Fixed Assets few useful Queries in Oracle APPS

SELECT *

   FROM fa_additions_b

  WHERE asset_number = p_asset_number;

SELECT *

   FROM fa_additions_tl

  WHERE asset_id = p_asset_id

    AND LANGUAGE = USERENV('LANG');

  SELECT *

    FROM fa_transaction_headers

   WHERE asset_id = p_asset_id

     AND book_type_code = p_book_type_code

ORDER BY book_type_code, date_effective;

SELECT *

  FROM fa_asset_history

 WHERE asset_id = p_asset_id;

 SELECT *

   FROM fa_adjustments

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_books

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_deprn_summary

   WHERE asset_id = p_asset_id

     AND book_type_code = p_book_type_code

  ORDER BY deprn_run_date;

SELECT *

    FROM fa_deprn_summary_h

   WHERE asset_id = p_asset_id;

SELECT *

    FROM fa_deprn_detail

   WHERE asset_id = p_asset_id

  ORDER BY book_type_code, deprn_run_date;

SELECT *

    FROM fa_deprn_detail_h

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_deprn_events

   WHERE asset_id = p_asset_id

ORDER BY book_type_code, deprn_run_date;

SELECT *

  FROM fa_asset_invoices

 WHERE asset_id = p_asset_id;

SELECT *

  FROM fa_invoice_transactions

 WHERE invoice_transaction_id IN (SELECT DISTINCT invoice_transaction_id_in

                                    FROM fa_asset_invoices

                                   WHERE asset_id = p_asset_id);

  SELECT *

    FROM fa_books_summary

   WHERE asset_id = p_asset_id

ORDER BY book_type_code, period_counter;

  SELECT *

    FROM fa_deprn_periods

   WHERE book_type_code = (SELECT DISTINCT book_type_code

                             FROM fa_deprn_detail_h

                            WHERE asset_id = p_asset_id)

ORDER BY period_counter;

SELECT *

  FROM fa_mass_additions

 WHERE asset_number = p_asset_number;

SELECT *

  FROM fa_massadd_distributions

 WHERE mass_addition_id IN (SELECT DISTINCT mass_addition_id

                              FROM FA_MASS_ADDITIONS

                             WHERE asset_number = p_asset_number);

SELECT *

  FROM fa_book_controls

 WHERE book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id);

SELECT *

  FROM fa_book_controls_history

 WHERE book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                          );

SELECT *

  FROM fa_categories_b

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number);

SELECT *

  FROM fa_categories_tl

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                        );

SELECT *

  FROM fa_category_books

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                            )

   AND book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                          );

SELECT *

  FROM fa_category_book_defaults

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                      )

   AND book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                        );

SELECT *

  FROM fa_calendar_periods

 WHERE calendar_type =

          (SELECT deprn_calendar

             FROM fa_book_controls

            WHERE book_type_code = (SELECT DISTINCT book_type_code

                                      FROM fa_deprn_detail_h

                                     WHERE asset_id = p_asset_id

                                     )

          );

SELECT *

  FROM fa_conventions

 WHERE prorate_convention_code =

          (SELECT DISTINCT prorate_convention_code

             FROM fa_category_books

            WHERE category_id IN (SELECT DISTINCT asset_category_id

                                    FROM fa_additions_b

                                   WHERE asset_number = p_asset_number

                                     )

              AND book_type_code = (SELECT DISTINCT book_type_code

                                      FROM fa_deprn_detail_h

                                     WHERE asset_id = p_asset_id

                                    )

          );

SELECT *

  FROM fa_methods

 WHERE     method_code =

              (SELECT DISTINCT deprn_method

                 FROM fa_category_book_defaults

                WHERE     category_id IN (SELECT DISTINCT asset_category_id

                                            FROM fa_additions_b

                                           WHERE asset_number =p_asset_number

                                         )

                  AND book_type_code = (SELECT DISTINCT book_type_code

                                              FROM fa_deprn_detail_h

                                             WHERE asset_id = p_asset_id

                                             )

              )

       AND life_in_months =

              (SELECT DISTINCT life_in_months

                 FROM fa_category_book_defaults

                WHERE     category_id IN (SELECT DISTINCT asset_category_id

                                            FROM fa_additions_b

                                           WHERE asset_number =

                                                    p_asset_number)

                      AND book_type_code = (SELECT DISTINCT book_type_code

                                              FROM fa_deprn_detail_h

                                             WHERE asset_id = p_asset_id

                                             )

            );

SELECT *

    FROM xla_events

   WHERE event_id IN (SELECT DISTINCT event_id

                        FROM fa_transaction_headers

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary_h

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code)

ORDER BY event_date;

SELECT *

    FROM ( (SELECT *

              FROM xla_transaction_entities

             WHERE source_id_int_1 IN (SELECT DISTINCT

                                                  transaction_header_id

                                         FROM fa_transaction_headers

                                        WHERE asset_id = p_asset_id

                                          AND book_type_code = p_book_type_code

                                          AND event_id IS NOT NULL

                                       )

               AND source_id_char_1 = p_book_type_code

             )

          UNION ALL

          (SELECT *

             FROM xla_transaction_entities

            WHERE     source_id_int_3 IN (SELECT DISTINCT deprn_run_id

                                            FROM fa_deprn_summary

                                           WHERE asset_id = p_asset_id

                                             AND book_type_code = p_book_type_code

                                             AND event_id IS NOT NULL

                                             )

                  AND source_id_char_1 = p_book_type_code

                  AND source_id_int_1 = p_asset_id

           )

          UNION ALL

          (SELECT *

             FROM xla_transaction_entities

            WHERE source_id_int_3 IN (SELECT DISTINCT deprn_run_id

                                            FROM fa_deprn_summary_h

                                           WHERE asset_id = p_asset_id

                                             AND book_type_code = p_book_type_code

                                             )

             AND source_id_char_1 = p_book_type_code

             AND source_id_int_1 = p_asset_id

                  )

       )

ORDER BY creation_date;

SELECT *

    FROM xla_ae_headers

   WHERE event_id IN (SELECT DISTINCT event_id

                        FROM fa_transaction_headers

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary_h

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                       )

ORDER BY accounting_date;

Oracle EBS + Query to Find List of XML Publisher Reports

 SELECT fe.executable_name,'Data Template (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'K'

AND fe.executable_name = 'XDODTEXE'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%')

UNION

SELECT fe.executable_name,'PLSQL (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'I'

AND fcp.OUTPUT_FILE_TYPE = 'XML'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%')

UNION

SELECT fe.executable_name,'RDF (XML) Reports' object_type,fe.EXECUTION_FILE_NAME,

fcp.CONCURRENT_PROGRAM_NAME concurrent_program_short_name,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.enabled_flag,DECODE (NVL(fcp.ENABLED_FLAG,'N'),'Y','Active','Inactive') active_status,

(select max(fcr.actual_start_date) from fnd_concurrent_requests fcr where fcr.concurrent_program_id = fcp.concurrent_program_id) last_execuation_date

FROM FND_EXECUTABLES FE,

FND_CONCURRENT_PROGRAMS FCP,

FND_CONCURRENT_PROGRAMS_TL FCPT

WHERE fe.execution_method_code = 'P'

AND fcp.OUTPUT_FILE_TYPE = 'XML'

AND FE.executable_id = FCP.executable_id

AND FCP.concurrent_program_id = FCPT.concurrent_program_id

AND FCPT.language = 'US'

AND (fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'LIN%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'XX%' OR fcp.CONCURRENT_PROGRAM_NAME LIKE 'LIN%');

Regards,

Amar Alam

Oracle Fusion + Sales Order Audit Report Query + Sales Order Fulfillment Lines EFF Query

 SELECT * FROM 

(SELECT --dla.line_id,

       --dha.header_id,

   dha.order_number "Order Number",

       hp.party_name "customer",

       hp.country "country",

   (NVL(dla.ordered_qty,0) * NVL(dla.unit_selling_price,0)) amount,

  (SELECT attribute_char3

   FROM doo_headers_eff_b dheb

   WHERE dheb.header_id = dha.header_id

     AND CONTEXT_CODE='Subscription')  region,

       Revenue_Management.attribute_char8 "workday contract name",

       Subscription.attribute_char1 "billing_offset",

       ACCOUNTS_RECEIVABLES.attribute_char1 "invoice group by number",

       ACCOUNTS_RECEIVABLES.attribute_char2 "invoice group by description",

       ACCOUNTS_RECEIVABLES.attribute_char3 "management group",

        SF_SUBSCRIPTION.ATTRIBUTE_CHAR10 "align billing from",

            SF_SUBSCRIPTION.attribute_char11 "custom billing",

                 Conversion.ATTRIBUTE_CHAR4 "Misc info",

                 Conversion.attribute_char2 "PS billed amount",

                 Conversion.attribute_char3 "PS unbilled amount",

                 Conversion.attribute_char5 "supplier purchase order",

                 TO_CHAR(SNOW.attribute_timestamp1,'MM/DD/YY HH:MI') "cloud provision date",

                 TO_CHAR(SNOW.attribute_timestamp2,'MM/DD/YY HH:MI') "on-perm provision date",

                 IB.attribute_char2 "asset line identifer",

                 IB.attribute_char3 "life cycle pid ",

                 SFDC.attribute_char1 "PO requied",

 SFDC.attribute_char2 "PO Number",

 SFDC.attribute_char3 "PO Line Number",

                 SFDC.attribute_char4 "invoice trigger event",

                 SFDC.attribute_char5 "site id",

                 SFDC.attribute_char6 "bundle id",

                 SFDC.attribute_char7 "advanced billing flag",

                 SFDC.attribute_char10 "used inventory flag",

                 SFDC.attribute_char11 "GSS validation flag",

                 SFDC.attribute_char16 "suppress fulfillment",

                 Procurement.attribute_char1 "cabinet position",

                 Procurement.attribute_char2 "PO vendor cost/transfer price",

                 Procurement.attribute_char3 "related manual order",

                 Tax.attribute_char2 "0% tax  rate flag",

                 BRAZIL.attribute_char1 "CFOP-line order",

                 CSP.attribute_char1 "CSP flag",

                 CSP.attribute_char3 "private order flag",

     esib.item_number,

 esib.description item_description,

 bill_to_party.party_name bill_to_customer,

 ship_to_party.party_name ship_to_customer,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR5 sf_subscription_number,

 SF_SUBSCRIPTION.ATTRIBUTE_CHAR2  "SF Line Identifier (Model)",

 revenue_management.attribute_char6 "Rev Contract Grouping" ,

 (SELECT header_curr_duration_ext_amt

 FROM doo_order_pricing_details_v dopdv

 WHERE dopdv.fulfill_line_id = dfla.fulfill_line_id

                 AND dopdv.PRICE_ELEMENT_CODE='QP_NET_PRICE'

                 AND dopdv.ROLLUP_FLAG ='Y' ) Total_Amount_for_Duration

FROM doo_headers_all dha,

     doo_lines_all dla,

     doo_fulfill_lines_all dfla,

     doo_fulfill_lines_eff_b ACCOUNTS_RECEIVABLES,

     doo_fulfill_lines_eff_b Revenue_Management,

     doo_fulfill_lines_eff_b Subscription,

     doo_fulfill_lines_eff_b CONVERSION,

     doo_fulfill_lines_eff_b SFDC,

 doo_fulfill_lines_eff_b SNOW,

     doo_fulfill_lines_eff_b IB,

     doo_fulfill_lines_eff_b Procurement,

     doo_fulfill_lines_eff_b Tax,

     doo_fulfill_lines_eff_b BRAZIL,

     doo_fulfill_lines_eff_b CSP,

 doo_fulfill_lines_eff_b SF_SUBSCRIPTION,

     hz_parties hp,

 egp_system_items esib,

     doo_order_addresses bill_to,

 doo_order_addresses ship_to,

 hz_cust_accounts bill_to_cust,

 hz_parties bill_to_party,

 hz_parties ship_to_party

WHERE 1=1

  ---AND dha.order_number='00026264'

  AND dha.header_id=dla.header_id

  AND dla.line_id= dfla.line_id

  AND dfla.fulfill_line_id = accounts_receivables.fulfill_line_id (+)

  AND accounts_receivables.context_code (+)='ACCOUNTS RECEIVABLES'

  AND dfla.fulfill_line_id = Revenue_Management.fulfill_line_id (+)

  AND Revenue_Management.context_code (+)='Revenue_Management_Information_Line'

  AND dfla.fulfill_line_id = Subscription.fulfill_line_id (+)

  AND Subscription.context_code (+)='Subscription'

  AND dfla.fulfill_line_id = Conversion.fulfill_line_id (+)

  AND Conversion.context_code (+)='Conversion'

  AND dfla.fulfill_line_id = SFDC.fulfill_line_id (+)

  AND SFDC.context_code (+)='SFDC'

  AND dfla.fulfill_line_id = SNOW.fulfill_line_id (+)

  AND SNOW.context_code (+)='SNOW'

  AND dfla.fulfill_line_id = IB.fulfill_line_id (+)

  AND IB.context_code (+)='IB - Asset Line Identifier Model'

  AND dfla.fulfill_line_id = Procurement.fulfill_line_id (+)

  AND Procurement.context_code (+)='Procurement'

  AND dfla.fulfill_line_id = Tax.fulfill_line_id (+)

  AND Tax.context_code (+)='Tax'

  AND dfla.fulfill_line_id = BRAZIL.fulfill_line_id (+)

  AND BRAZIL.context_code (+)='BRAZIL-Localization'

  AND dfla.fulfill_line_id = CSP.fulfill_line_id (+)

  AND CSP.context_code (+)='CSP Details'

  AND dfla.fulfill_line_id = SF_SUBSCRIPTION.fulfill_line_id (+)

  AND SF_SUBSCRIPTION.context_code (+)='SF Subscription - SF Line Identifier Model'

  AND dha.sold_to_party_id = hp.party_id 

  AND dla.inventory_organization_id =esib.organization_id

  and dla.inventory_item_id = esib.inventory_item_id

  AND dha.header_id = bill_to.header_id (+)

  AND bill_to.address_use_type (+)= 'BILL_TO'

  AND bill_to.cust_acct_id = bill_to_cust.cust_account_id (+)

  AND bill_to_cust.party_id = bill_to_party.party_id

  AND dha.header_id = ship_to.header_id (+)

  AND ship_to.address_use_type (+) = 'SHIP_TO'

  AND ship_to.party_id = ship_to_party.party_id (+)

 ) 

  WHERE region = NVL(:p_region,region);

Regards,

Amar Alam

Oracle SQL + How to split a string in Oracle using substr/instr

 Table Name: GLCC

Column Name: criteria

Column Value: ALAM-**-98000-10301990-***-***************-*****-OPR**

SELECT

   criteria,INSTR(criteria,'-',1,1) AS first_comma,

   INSTR(criteria,'-',1,2) AS second_comma,

   INSTR(criteria,'-',1,3) AS third_comma

   ,INSTR(criteria,'-',1,4) AS fourth_comma,

   INSTR(criteria,'-',1,5) AS fifth_comma,

   SUBSTR(criteria,1,INSTR(criteria,'-',1,1)-1) column1

   ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,1) + 1

             ,INSTR(criteria,'-',1,2)

              - INSTR(criteria,'-',1,1)

              - 1)

       AS column2

         ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,2) + 1

             ,INSTR(criteria,'-',1,3)

              - INSTR(criteria,'-',1,2)

              - 1)

       AS column3       

                ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,3) + 1

             ,INSTR(criteria,'-',1,4)

              - INSTR(criteria,'-',1,3)

              - 1)

       AS column4  

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,4) + 1

             ,INSTR(criteria,'-',1,5)

              - INSTR(criteria,'-',1,4)

              - 1)

       AS column5 

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,5) + 1

             ,INSTR(criteria,'-',1,6)

              - INSTR(criteria,'-',1,5)

              - 1)

       AS column6

                       ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,6) + 1

             ,INSTR(criteria,'-',1,7)

              - INSTR(criteria,'-',1,6)

              - 1)

       AS column7

             ,SUBSTR(criteria

             ,INSTR(criteria,'-',1,7) + 1)

       AS column8

              FROM

    glcc;  

Oracle Fusion + Query to fetch Inventory On-hand Available to Reserve quantity

 select 

  NVL(TR_QTY, 0) - (

    NVL(TR_QTY_B, 0) + NVL(TR_QTY_C, 0)

  ) res_qty 

from 

  (

    SELECT 

      (

        SELECT 

          SUM(transaction_quantity) 

        FROM 

          INV_ONHAND_QUANTITIES_DETAIL 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348 

          AND subinventory_code IN (

            SELECT 

              SECONDARY_INVENTORY_NAME 

            FROM 

              INV_SECONDARY_INVENTORIES 

            WHERE 

              organization_id = 300000002138348 

              AND reservable_type = '2'

          )

      ) AS TR_QTY_C, 

      (

        SELECT 

          SUM(RESERVATION_QUANTITY) 

        FROM 

          INV_RESERVATIONS 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348

      ) AS TR_QTY_B, 

      (

        SELECT 

          SUM(transaction_quantity) 

        FROM 

          INV_ONHAND_QUANTITIES_DETAIL 

        WHERE 

          inventory_item_id = 100000000615408 

          AND organization_id = 300000002138348

      ) AS TR_QTY 

    from 

      egp_system_items_b MB1 

    where 

      1 = 1 

      AND (

        mb1.organization_id = 300000002138348

      ) 

      AND (

        mb1.inventory_item_id = 100000000615408

      )

  );

Query to fetch Purchase Receipt and its related Purchase order, Vendor details in Oracle Apps R12

 Key Tables:

RCV_TRANSACTIONS

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

PO_DISTRIBUTIONS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

PO_HEADERS_ALL

AP_SUPPLIERS

AP_SUPPLIER_SITES_ALL

Query:

SELECT hou.name ou_name, ph.segment1 po_num, ph.po_header_id, pol.po_line_id, pol.line_num, order_type.displayed_field line_type, COALESCE(pd.quantity_ordered,pll.quantity,pol.quantity) quantity, (pol.unit_price * (pd.quantity_ordered - pd.quantity_cancelled)) line_amount, rsh.receipt_source_code, flv_ship_source.meaning receipt_source_code, papf.full_name received_by, rsh.vendor_id, aps.vendor_name, aps.segment1 vendor_number, assa.vendor_site_id, assa.vendor_site_code, ph.org_id, rsh.shipment_num, rsh.receipt_num, rsh.ship_to_location_id, hl.location_code deliver_to, rsl.item_description, rsl.shipment_line_id, rsl.quantity_shipped, rsl.quantity_received, rsl.unit_of_measure, rsl.vendor_item_num, flv_shipment.meaning shipment_line_status_code, flv_inspection.meaning inspection_status_code, rsl.shipment_line_status_code, rct.inspection_status_code, flv_tran_type.meaning transaction_type, rct.transaction_type transaction_type_lookup_code, rct.transaction_id, nvl(rct.source_doc_quantity,0) transaction_qty, rct.transaction_date, assa.attribute1 vendor_global_code, assa.attribute2 entity_supplier_code, assa.attribute3 vendor_communication_language, pd.attribute1 company_specific_gl_code, pol.attribute2 sanction_number, pol.attribute3 inspection_required, pol.attribute4 end_user_details, rsh.attribute1 Invoice_number, rct.comments gre_comments, rsh.attribute2 exchange_rate_information, rsh.attribute3 gst_invoice_amt, rsh.attribute4 gst_exchange_rate, rsh.attribute5 custom_form_no, rct.attribute1 receipt_line_level_tax_rate from rcv_transactions rct, rcv_shipment_headers rsh, per_all_people_f papf, rcv_shipment_lines rsl, po_distributions_all pd, po_lines_all pol, po_line_locations_all pll, po_headers_all ph, ap_suppliers aps, ap_supplier_sites_all assa, hr_all_organization_units hou, hr_locations hl, fnd_lookup_values flv_shipment, fnd_lookup_values flv_inspection, fnd_lookup_values flv_tran_type, fnd_lookup_values flv_ship_source, po_lookup_codes order_type, po_line_types_b plt WHERE 1 = 1 and rct.po_header_id = ph.po_header_id and rct.po_line_location_id = pll.line_location_id AND rct.po_line_id = pol.po_line_id AND pol.po_line_id = pd.po_line_id and rct.shipment_line_id = rsl.shipment_line_id and rsl.shipment_header_id = rsh.shipment_header_id AND rct.po_distribution_id = pd.po_distribution_id AND rct.employee_id = papf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date and rsh.vendor_id = aps.vendor_id and ph.vendor_site_id = assa.vendor_site_id AND hou.organization_id = ph.org_id AND rct.deliver_to_location_id = hl.location_id AND rsl.shipment_line_status_code = flv_shipment.lookup_code AND flv_shipment.lookup_type = 'SHIPMENT LINE STATUS' AND flv_shipment.language = USERENV('LANG') AND rct.inspection_status_code = flv_inspection.lookup_code AND flv_inspection.lookup_type = 'INSPECTION STATUS' AND flv_inspection.language = USERENV('LANG') AND rct.transaction_type = flv_tran_type.lookup_code AND flv_tran_type.lookup_type = 'RCV TRANSACTION TYPE' AND flv_tran_type.language = USERENV('LANG') AND flv_ship_source.lookup_type = 'SHIPMENT SOURCE TYPE' AND flv_ship_source.language = USERENV('LANG') AND flv_ship_source.lookup_code = rsh.receipt_source_code AND order_type.lookup_type = 'ORDER TYPE' AND order_type.lookup_code = plt.order_type_lookup_code AND pol.line_type_id = plt.line_type_id;

Regards,

Amar Alam