Query to Find Purchase Order in Oracle Apps R12

SELECT
  O.NAME "OPERATING_UNIT_NAME",
  PH.SEGMENT1 "PO_REV_NUM",
  PH.PO_HEADER_ID,
  PH.TYPE_LOOKUP_CODE "TYPE",
  PH.CURRENCY_CODE,
  PH.AUTHORIZATION_STATUS "STATUS",
  PV.VENDOR_SITE_CODE,
  V.VENDOR_NAME "SUPPLIER_NAME",
  LOC1.LOCATION_CODE "SHIP_TO_LOC",
  LOC2.LOCATION_CODE "BILL_TO_LOC",
  (PL1.QUANTITY*PL1.UNIT_PRICE) "TOTAL",
--LINES INFORMATION
  PL1.LINE_NUM,
  PL1.PURCHASE_BASIS "TYPE",
  I.SEGMENT1 "ITEM",
  PL1.ITEM_REVISION,
  C.SEGMENT1||C.SEGMENT1 "CATEGORY",
  PL1.ITEM_DESCRIPTION,
  PL1.QUANTITY,
  PL.UNIT_MEAS_LOOKUP_CODE,
  PL1.UNIT_PRICE,
  PL.NEED_BY_DATE,
  PL.SHIPMENT_TYPE,
  PL.SHIPMENT_NUM,
  M.ORGANIZATION_CODE "SHP_ORG_CODE",
  LOC1.LOCATION_CODE "SHIPMENT_LOC",
  PL.UNIT_MEAS_LOOKUP_CODE "SHP_UOM",
  PL.QUANTITY "SHP_QUANTITY",
  (PL.QUANTITY*PL.PRICE_OVERRIDE) "SHP_AMOUNT",
  D.DISTRIBUTION_NUM,
  D.DESTINATION_TYPE_CODE,
  GL.CONCATENATED_SEGMENTS,
  D.QUANTITY_ORDERED "DIS_QUANTITY",
  P.FULL_NAME "REQUESTER NAME",
  I.INVENTORY_ITEM_ID,
  C.CATEGORY_ID
FROM
  --PO_HEADERS
  HR_ORGANIZATION_UNITS O,
  po_headers_all PH,
  PO_VENDORS V,
  PO_VENDOR_SITES_ALL PV,
  HR_LOCATIONS_ALL_TL LOC1,
  HR_LOCATIONS_ALL_TL LOC2,
  --PO_LINES
  po_line_locations_all PL,
  PO_LINES_ALL PL1,
  MTL_SYSTEM_ITEMS_B I,
  MTL_CATEGORIES_B C,
  MTL_PARAMETERS M,
  PO_DISTRIBUTIONS_ALL D,
  GL_CODE_COMBINATIONS_KFV GL,
  PER_ALL_PEOPLE_F P
WHERE PH.VENDOR_SITE_ID=PV.VENDOR_SITE_ID
  AND PH.SHIP_TO_LOCATION_ID=PV.SHIP_TO_LOCATION_ID
  AND PH.VENDOR_ID=V.VENDOR_ID
  AND LOC1.LOCATION_ID=PH.SHIP_TO_LOCATION_ID
  AND LOC2.LOCATION_ID=PV.BILL_TO_LOCATION_ID
  AND PH.PO_HEADER_ID=PL1.PO_HEADER_ID
    --LINES
  AND PL1.ITEM_ID=I.INVENTORY_ITEM_ID
  AND PL.PO_HEADER_ID=PL1.PO_HEADER_ID
  AND PL.PO_LINE_ID=PL1.PO_LINE_ID
  AND PL1.CATEGORY_ID=C.CATEGORY_ID
  --ORG_CODE
  AND M.ORGANIZATION_ID=PL.SHIP_TO_ORGANIZATION_ID
  --AND O.ORGANIZATION_ID=M.ORGANIZATION_ID
  --AND C.ORGANIZATION_ID=M.ORGANIZATION_ID
  AND O.ORGANIZATION_ID=PH.ORG_ID
  --DISTRIBUTION
  AND D.PO_HEADER_ID=PL.PO_HEADER_ID
  AND D.PO_LINE_ID=PL.PO_LINE_ID
  AND D.LINE_LOCATION_ID=PL.LINE_LOCATION_ID
  AND GL.CODE_COMBINATION_ID=D.CODE_COMBINATION_ID
  AND D.DELIVER_TO_PERSON_ID=P.PERSON_ID;

Order Management Sales Order Query

SELECT
oha.header_id,bill_ca.cust_account_id,
oha.order_number,
oha.ordered_date,
oha.cust_po_number customer_po,
oha.org_id,
ott.name order_type,
ola.line_number,
msi.segment1 item_code,
msi. description item_desc,
ola.ordered_quantity,
ola.order_quantity_uom,
oha.transactional_curr_code trx_curr,
ola.unit_selling_price,
(ola.ordered_quantity*ola.unit_selling_price) line_amt,
ola.shipping_quantity,
ola.shipping_quantity_uom,
(nvl(ola.shipping_quantity,0)*ola.unit_selling_price) shipped_amt,
ola.schedule_ship_date,
--oha.flow_status_code order_status,
ol.meaning order_status,
bill_p.party_name,
bill_su.LOCATION invoice_to_location,
bill_loc.address1 invoice_to_address1,
bill_loc.address2 invoice_to_address2,
bill_loc.address3 invoice_to_address3,
bill_loc.address4 invoice_to_address4,
DECODE (bill_loc.city,
NULL, NULL,
bill_loc.city || ', '
)
|| DECODE (bill_loc.state,
NULL, bill_loc.province || ', ',
bill_loc.state || ', '
)
|| DECODE (bill_loc.postal_code,
NULL, NULL,
bill_loc.postal_code || ', '
)
|| DECODE (bill_loc.country, NULL, NULL, bill_loc.country) invoice_to_address5,
ship_loc.city ship_city,
ship_loc.country ship_country_code,
ft.territory_short_name ship_country,
(select name
from ra_salesreps_all
where salesrep_id = oha.salesrep_id
and org_id = oha.org_id) sales_person,
--wd.date_scheduled,
oha.conversion_type_code,
bill_ca.account_number,
oha.global_attribute2

FROM oe_order_headers_all oha,
oe_order_lines_all ola,
oe_transaction_types_tl ott,
mtl_system_items_b msi,
hz_cust_site_uses_all bill_su,
hz_party_sites bill_ps,
hz_locations bill_loc,
hz_cust_acct_sites_all bill_cas,
hz_cust_accounts bill_ca,
hz_parties bill_p,
hz_cust_site_uses_all ship_su,
hz_party_sites ship_ps,
hz_locations ship_loc,
hz_cust_acct_sites_all ship_cas,
--    wsh_deliverables_v wd
fnd_territories_tl ft,
oe_lookups ol

WHERE
oha.org_id = nvl(:p_org_id,oha.org_id)
and trunc(oha.ordered_date) between nvl(:p_order_date_from,trunc(oha.ordered_date))
and nvl(:p_order_date_to,trunc(oha.ordered_date))
and bill_ca.cust_account_id between nvl(:p_cust_acc_id_from,trunc(bill_ca.cust_account_id))
and nvl(:p_cust_acc_id_to,trunc(bill_ca.cust_account_id))
and trunc(NVL(ola.schedule_ship_date,SYSDATE)) between nvl(:p_delvry_date_from,trunc(NVL(ola.schedule_ship_date,SYSDATE)))
and nvl(:p_delvry_date_to,trunc(NVL(ola.schedule_ship_date,SYSDATE)))
--and oha.flow_status_code between nvl(:p_order_status_from,oha.flow_status_code)
--and nvl(:p_order_status_to,oha.flow_status_code)
and ol.meaning between nvl(:p_order_status_from,ol.meaning)
and nvl(:p_order_status_to,ol.meaning)
and msi.inventory_item_id between nvl(:p_prod_desc_from,msi.inventory_item_id)
and nvl(:p_prod_desc_to,msi.inventory_item_id)
/*and msi.description between nvl(:p_prod_desc_from,msi.description)
and nvl(:p_prod_desc_to,msi.description) */
and ship_loc.country between nvl(:p_ship_country_from,ship_loc.country)
and nvl(:p_ship_country_to,ship_loc.country)
and oha.salesrep_id between nvl(:p_salesrep_id_from,oha.salesrep_id)
and nvl(:p_salesrep_id_to,oha.salesrep_id)
and oha.header_id = ola.header_id
and oha.ORDER_TYPE_ID = ott.transaction_type_id
and ott.language = userenv('LANG')
and ola.inventory_item_id = msi.inventory_item_id
and ola.ship_from_org_id = msi.organization_id
and oha.invoice_to_org_id = bill_su.site_use_id(+)
and bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
and bill_cas.party_site_id = bill_ps.party_site_id(+)
and bill_loc.location_id(+) = bill_ps.location_id
and bill_cas.cust_account_id = bill_ca.cust_account_id
and bill_ca.party_id = bill_p.party_id
and oha.ship_to_org_id = ship_su.site_use_id(+)
and ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
and ship_cas.party_site_id = ship_ps.party_site_id(+)
and ship_loc.location_id(+) = ship_ps.location_id
--   and oha.header_id = wd.source_header_id(+)
--   and ola.line_id = wd.source_line_id(+)
and ship_loc.country = ft.territory_code
and ft.language = userenv('LANG')
and ol.lookup_type = 'FLOW_STATUS'
and ol.lookup_code = oha.flow_status_code
and oha.order_number = '1821062' ;

Account Receivable TO General Ledger Join Query ( Order to Cash)

SELECT   ooha.order_number,
         ooha.org_id,
         hca.account_name,
         hp.party_name "Customer Name",
         hcasab.orig_system_reference BILL_TO_ORIG_REF,
         hpsb.status BILL_TO_STATUS,
            'ADDRESS1 - '
         || bill_loc.address1
         || ','
         || CHR (10)
         || 'ADDRESS2 - '
         || bill_loc.address2
         || ','
         || CHR (10)
         || 'ADDRESS3 - '
         || bill_loc.address3
         || ','
         || CHR (10)
         || 'CITY     - '
         || bill_loc.city
         || ','
         || CHR (10)
         || 'POSTAL CD- '
         || bill_loc.postal_code
         || ','
         || CHR (10)
         || 'COUNTRY  - '
         || bill_loc.country
            BILL_TO_ADDRESS,
         hcasas.orig_system_reference SHIP_TO_ORIG_REF,
         hpss.status SHIP_TO_STATUS,
            'ADDRESS1 - '
         || ship_loc.address1
         || ','
         || CHR (10)
         || 'ADDRESS2 - '
         || ship_loc.address2
         || ','
         || CHR (10)
         || 'ADDRESS3 - '
         || ship_loc.address3
         || ','
         || CHR (10)
         || 'CITY     - '
         || ship_loc.city
         || ','
         || CHR (10)
         || 'POSTAL CD- '
         || ship_loc.postal_code
         || ','
         || CHR (10)
         || 'COUNTRY  - '
         || ship_loc.country
            SHIP_TO_ADDRESS,
         oola.inventory_item_id,
         oola.ordered_item,
         msib.description item_description,
         wnd.name delivery_number,
         rct.trx_number "AR Invoice Number",
         acr.receipt_number "AR Receipt Number",
         gjh.ledger_id,
         gjh.name
  FROM  -- Enter the Sales Order:
         oe_order_headers_all ooha,
         oe_order_lines_all oola,
         -- Enter the Sales Order: --
         -- AR Customer Detail
         hz_parties hp,
         hz_cust_accounts hca,
         hz_party_sites hpss,
         hz_party_sites hpsb,
         hz_locations bill_loc,
         hz_locations ship_loc,
         hz_cust_acct_sites_all hcasab,
         hz_cust_acct_sites_all hcasas,
         hz_cust_site_uses_all hzsuab,
         hz_cust_site_uses_all hzsuas,
         -- AR Customer Detail: --
         mtl_system_items_b msib,
         -- Book the Sales Order:
         wsh_delivery_details wdd,
         wsh_new_deliveries wnd,
         wsh_delivery_assignments wda,
         -- Book the Sales Order: --
         -- Create ARInvoice:
         ra_customer_trx_all rct,
         ra_customer_trx_lines_all rctl,
         ra_cust_trx_line_gl_dist_all rctld,
         -- Create AR Invoice: --
         -- Create AR Receipt
         ar_cash_receipts_all acr,
         -- Create AR Receipt --
         -- subledger accounting
         xla.xla_transaction_entities xte,
         xla_events xe,
         xla_ae_headers xah,
         xla_ae_lines xal,
         xla_distribution_links xdl,
         -- subledger accounting : --
         -- GL Journal Import:
         gl_import_references gir,
         gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl
 -- GL Journal Import: --
 WHERE      1 = 1
         AND ooha.order_number = :SalesOrderNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
         AND ooha.org_id = 81
         AND ooha.header_id = oola.header_id
         AND hca.cust_account_id = ooha.sold_to_org_id
         AND hp.party_id = hca.party_id
         AND hpss.party_id = hca.party_id
         AND hpsb.party_id = hca.party_id
         AND bill_loc.location_id = hpss.location_id
         AND ship_loc.location_id = hpsb.location_id
         AND hcasas.cust_account_id = hca.cust_account_id
         AND hcasab.cust_account_id = hca.cust_account_id
         AND hcasas.party_site_id = hpss.party_site_id
         AND hcasab.party_site_id = hpsb.party_site_id
         AND hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
         AND hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
         AND hzsuas.site_use_id = ooha.ship_to_org_id
         AND hzsuab.site_use_id = ooha.invoice_to_org_id
         AND wda.delivery_id = wnd.delivery_id(+)
         AND wdd.delivery_detail_id = wda.delivery_detail_id
         AND wdd.source_header_id = ooha.header_id
         AND wdd.source_line_id = oola.line_id
         AND wdd.organization_id = msib.organization_id(+)
         AND wdd.inventory_item_id = msib.inventory_item_id(+)
         AND rct.interface_header_attribute1 = TO_CHAR (ooha.order_number)
         AND rct.org_id = ooha.org_id
         AND rctl.customer_trx_id = rct.customer_trx_id
         AND rctl.sales_order = TO_CHAR (ooha.order_number)
         AND rctld.customer_trx_id = rct.customer_trx_id
         AND rctld.customer_trx_line_id = rctl.customer_trx_line_id
         AND acr.receipt_number = '05037'
         AND acr.pay_from_customer = rct.sold_to_customer_id
         AND acr.org_id = ooha.org_id
         AND acr.customer_site_use_id = rct.bill_to_site_use_id
         AND xte.transaction_number = acr.receipt_number
         AND xte.entity_code = 'RECEIPTS'
         AND xe.entity_id = xte.entity_id
         AND xah.event_id = xe.event_id
         AND xal.ae_header_id = xah.ae_header_id
         AND xal.accounting_class_code = 'CASH'
         AND xdl.ae_header_id = xah.ae_header_id
         AND xdl.ae_line_num = xal.ae_line_num
         --and xdl.source_distribution_id_num_1
         AND gir.reference_5 = xte.entity_id                      -- Entity Id
         AND gir.reference_6 = TO_CHAR (xe.event_id)                --Event Id
         AND gir.reference_7 = TO_CHAR (xah.ae_header_id)      -- AE Header Id
         AND gir.gl_sl_link_id = xal.gl_sl_link_id
         -- AND gir.created_by = XXXXXX
         AND gjb.je_batch_id = gir.je_batch_id
         AND gjh.je_batch_id = gjb.je_batch_id
         AND gjh.je_header_id = gir.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gir.je_line_num;

Script to generate the FND LOAD script in Oracle Apps R12

    CREATE OR REPLACE VIEW xx_generate_fnd_scriptc_v (
   owner,
   object_name,
   object_description,
   last_update_date,
   object_type,
   source,
   download_fnd_script,
   upload_fnd_script
   )
AS
   ---------------------------------------------------
   --- ALL_OBJECTS
   ---------------------------------------------------
   SELECT owner,
          object_name,
          object_type || ' ' || owner || '.' || object_name object_description,
          last_ddl_time last_update_date,
          object_type,
          'ALL_OBJECTS' source,
             'sqlplus apps/$PASSWORD @admin/sql/'
          || LOWER (REPLACE (object_type, ' ', '_'))
          || ' '
          || object_name
             download,
          'sqlplus apps/$PASSWORD @admin/sql/' || object_name || '.sql' upload
   FROM all_objects
   UNION ALL
   ---------------------------------------------------
   --- PROGRAM
   ---------------------------------------------------
   SELECT fa.application_short_name,
          fcp.concurrent_program_name,
          fcpt.user_concurrent_program_name,
          fcp.last_update_date,
          'PROGRAM',
          'FND_CONCURRENT_PROGRAMS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct'
          || fcp.concurrent_program_name
          || '_CP.ldt PROGRAM APPLICATION_SHORT_NAME="'
          || fa.application_short_name
          || '" CONCURRENT_PROGRAM_NAME="'
          || fcp.concurrent_program_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct'
          || fcp.concurrent_program_name
          || '_CP.ldt'
             upload
   FROM fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpt,
        fnd_application fa
   WHERE     fcpt.concurrent_program_id = fcp.concurrent_program_id
         AND fcpt.language = 'US'
         AND fa.application_id = fcp.application_id
   UNION ALL
   ---------------------------------------------------
   --- FORM
   ---------------------------------------------------
   SELECT fa.application_short_name,
          ff.form_name,
          fft.user_form_name,
          ff.last_update_date,
          'FORM',
          'FND_FORM',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct '
          || ff.form_name
          || '_FRM.ldt FORM APPLICATION_SHORT_NAME="'
          || fa.application_short_name
          || '" FORM_NAME="'
          || ff.form_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct '
          || ff.form_name
          || '_FRM.ldt'
             upload
   FROM fnd_form ff, fnd_form_tl fft, fnd_application fa
   WHERE     fft.form_id = ff.form_id
         AND fft.language = 'US'
         AND fa.application_id = ff.application_id
   UNION ALL
   ---------------------------------------------------
   --- EXECUTABLES TYPES
   ---------------------------------------------------
   SELECT fa.application_short_name,
          fe.execution_file_name,
          fet.user_executable_name,
          fe.last_update_date,
          'EXECUTABLE '
          || DECODE (fe.execution_method_code,
                'H', 'HOST',
                'S', 'IMMEDIATE',
                'J', 'JAVA STORED PROC',
                'K', 'JAVA CONC PROG',
                'M', 'MULTI LANG FUNC',
                'I', 'PL/SQL',
                'B', 'REQ SET STAGE',
                'A', 'SPAWNED',
                'P', 'REPORT',
                'Q', 'SQL*PLUS',
                'L', 'SQL*LOADER',
                'E', 'PERL',
                '*' || fe.execution_method_code || ' ' || execution_file_name),
          'FND_EXECUTABLES',
          '' download,
          '' upload
   FROM fnd_executables fe, fnd_executables_tl fet, fnd_application fa
   WHERE     fet.executable_id = fe.executable_id
         AND fet.language = 'US'
         AND fa.application_id = fe.application_id
   UNION ALL
   ---------------------------------------------------
   --- EXECUTABLE
   ---------------------------------------------------
   SELECT fa.application_short_name,
          fe.executable_name,
          fet.user_executable_name,
          fe.last_update_date,
          'EXECUTABLE',
          'FND_EXECUTABLES',
          '' download,
          '' upload
   FROM fnd_executables fe, fnd_executables_tl fet, fnd_application fa
   WHERE     fet.executable_id = fe.executable_id
         AND fet.language = 'US'
         AND fa.application_id = fe.application_id
   UNION ALL
   ---------------------------------------------------
   --- WORKFLOW
   ---------------------------------------------------
   SELECT 'APPS',
          wit.name,
          witl.display_name,
          wfa.last_update_date,
          'WORKFLOW',
          'WF_ITEM_TYPES',
             'WFLOAD apps/$PASSWORD 0 Y DOWNLOAD wf/'
          || wit.name
          || '_'
          || TO_CHAR (wfa.last_update_date, 'ddmonyyyy')
          || '.wft '
          || wit.name
             download,
             'WFLOAD apps/$PASSWORD 0 Y UPLOAD wf/'
          || wit.name
          || '_'
          || TO_CHAR (wfa.last_update_date, 'ddmonyyyy')
          || '.wft '
             upload
   FROM (SELECT wpa.activity_item_type, MAX (wfa.begin_date) last_update_date
         FROM wf_process_activities wpa, wf_activities wfa
         WHERE wpa.activity_item_type = wfa.item_type
               AND wpa.activity_name = wfa.name
               AND wfa.version =
                     (SELECT MAX (version)
                      FROM wf_activities wfa1
                      WHERE wpa.activity_item_type = wfa1.item_type
                            AND wpa.activity_name = wfa1.name)
         GROUP BY wpa.activity_item_type) wfa,
        wf_item_types wit,
        wf_item_types_tl witl
   WHERE     witl.name = wit.name
         AND witl.language = 'US'
         AND wfa.activity_item_type(+) = wit.name
   UNION ALL
   ---------------------------------------------------
   --- MENU
   ---------------------------------------------------
   SELECT 'APPS',
          fm.menu_name,
          fmt.user_menu_name,
          fm.last_update_date,
          'MENU',
          'FND_MENUS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct '
          || 'fndload/'
          || REPLACE (fm.menu_name, ' ', '_')
          || '_fm.ldt '
          || 'MENU MENU_NAME="'
          || fm.menu_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct '
          || 'fndload/'
          || REPLACE (fm.menu_name, ' ', '_')
          || '.ldt '
             upload
   FROM fnd_menus fm, fnd_menus_tl fmt
   WHERE fmt.menu_id = fm.menu_id AND fmt.language = 'US'
   UNION ALL
   ---------------------------------------------------
   --- MENU ENTRY
   ---------------------------------------------------
   SELECT 'APPS',
          m2.menu_name || '/' || u.function_name || m.menu_name,
          e.entry_sequence || ' ' || u.user_function_name || m.user_menu_name,
          e.last_update_date,
          'MENU ENTRY',
          'FND_MENU_ENTRIES',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct '
          || 'fndload/'
          || REPLACE (m2.menu_name, ' ', '_')
          || '_'
          || REPLACE (u.function_name, ' ', '_')
          || '_'
          || REPLACE (m.menu_name, ' ', '_')
          || '.ldt '
          || 'MENU PARENT_MENU_NAME="'
          || m2.menu_name
          || DECODE (u.function_name,
                NULL, '',
                '" FUNCTION_NAME="' || u.function_name || '"')
          || DECODE (m.menu_name,
                NULL, '',
                '" SUB_MENU_NAME="' || m.menu_name || '"')
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct '
          || 'fndload/'
          || REPLACE (m2.menu_name, ' ', '_')
          || '_'
          || REPLACE (u.function_name, ' ', '_')
          || '_'
          || REPLACE (m.menu_name, ' ', '_')
          || '_fme.ldt '
             upload
   FROM fnd_menu_entries_vl e,
        fnd_menus_vl m,
        fnd_menus_vl m2,
        fnd_form_functions_vl u
   WHERE     e.function_id = u.function_id(+)
         AND e.sub_menu_id = m.menu_id(+)
         AND e.menu_id = m2.menu_id
   UNION ALL
   ---------------------------------------------------
   --- LOOKUP
   ---------------------------------------------------
   SELECT fa.application_short_name,
          flt.lookup_type,
          fltt.meaning,
          flt.last_update_date,
          'LOOKUP',
          'FND_LOOKUP_TYPES',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct '
          || 'fndload/'
          || REPLACE (flt.lookup_type, ' ', '_')
          || '_flt.ldt '
          || 'FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="'
          || fa.application_short_name
          || '" '
          || 'LOOKUP_TYPE="'
          || flt.lookup_type
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct '
          || 'fndload/'
          || REPLACE (flt.lookup_type, ' ', '_')
          || '_flt.ldt '
             upload
   FROM fnd_application fa, fnd_lookup_types flt, fnd_lookup_types_tl fltt
   WHERE     fltt.lookup_type = flt.lookup_type
         AND fltt.language = 'US'
         AND fa.application_id = flt.application_id
   UNION ALL
   ---------------------------------------------------
   --- FLEXVALUES
   ---------------------------------------------------
   SELECT 'APPS',
          ffvs.flex_value_set_name,
          ffvs.description,
          ffvs.last_update_date,
          'FLEXVALUE',
          'FND_FLEX_VALUE_SETS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct '
          || 'fndload/'
          || REPLACE (ffvs.flex_value_set_name, ' ', '_')
          || '_ffvs.ldt '
          || 'VALUE_SET FLEX_VALUE_SET_NAME="'
          || flex_value_set_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct '
          || 'fndload/'
          || REPLACE (ffvs.flex_value_set_name, ' ', '_')
          || '_ffvs.ldt '
             upload
   FROM fnd_flex_value_sets ffvs
   UNION ALL
   ---------------------------------------------------
   --- PERSONALIZATION
   ---------------------------------------------------
   SELECT 'APPS',
          form_name,
          function_name,
          MAX (last_update_date),
          'PERSONALIZATION',
          'FND_FORM_CUSTOM_RULES',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct '
          || 'fndload/'
          || REPLACE (form_name, ' ', '_')
          || '_'
          || REPLACE (function_name, ' ', '_')
          || '_ffcr.ldt FND_FORM_CUSTOM_RULES FORM_NAME="'
          || form_name
          || '" '
          || 'FUNCTION_NAME="'
          || function_name
          || '"'
             download,
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct '
          || form_name
          || '_ffcr.ldt'
             upload
   FROM fnd_form_custom_rules
   GROUP BY form_name, function_name
   UNION ALL
   ---------------------------------------------------
   --- PROFILE
   ---------------------------------------------------
   SELECT fa.application_short_name,
          fpo.profile_option_name,
          user_profile_option_name,
          fpo.last_update_date,
          'PROFILE',
          'FND_PROFILE_OPTIONS_VL',
          'FNDLOAD APPS/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct '
          || 'fndload/'
          || fpo.profile_option_name
          || '_fpo.ldt '
          || 'PROFILE FND_PROFILE_OPTION_VALUES PROFILE_NAME="'
          || fpo.profile_option_name
          || '" APPLICATION_SHORT_NAME="'
          || fa.application_short_name
          || '"',
          'FNDLOAD APPS/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct '
          || 'fndload/'
          || fpo.profile_option_name
          || '_fpo.ldt '
   FROM fnd_profile_options_vl fpo, fnd_application fa
   WHERE fpo.application_id = fa.application_id
   UNION ALL
   ---------------------------------------------------
   --- REQUEST GROUPS
   ---------------------------------------------------
   SELECT a.application_short_name,
          v.request_group_code,
          v.request_group_name,
          v.last_update_date,
          'REQUEST GROUP',
          'FND_REQUEST_GROUPS',
          'FNDLOAD APPS/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct '
          || 'fndload/'
          || v.request_group_code
          || '_rg.ldt '
          || 'REQUEST_GROUP REQUEST_GROUP_NAME="'
          || v.request_group_code
          || '"',
          'FNDLOAD APPS/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct '
          || 'fndload/'
          || v.request_group_code
          || '_rg.ldt '
   FROM fnd_request_groups v, fnd_application a
   WHERE 1 = 1 AND a.application_id = v.application_id
   UNION ALL
   ---------------------------------------------------
   --- REQUEST GROUP UNITS
   ---------------------------------------------------
   SELECT va.application_short_name,
          g.request_group_name,
          DECODE (v.request_unit_type,
                  'P',
                  p.concurrent_program_name,
                  'S',
                  s.request_set_name
          ),
          v.last_update_date,
          'REQUEST GROUP UNIT',
          'FND_REQUEST_GROUP_UNITS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct'
          || REPLACE (g.request_group_name || '_'
                      || DECODE (v.request_unit_type,
                                 'P',
                                 p.concurrent_program_name,
                                 'S',
                                 s.request_set_name
                         ),
                      ' ',
                      '_'
             )
          || '_frgu.ldt '
          || 'REQUEST_GROUP REQUEST_GROUP_NAME="'
          || g.request_group_name
          || '" UNIT_NAME="'
          || DECODE (v.request_unit_type,
                     'P',
                     p.concurrent_program_name,
                     'S',
                     s.request_set_name
             )
          || '"',
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct'
          || REPLACE (g.request_group_name || '_'
                      || DECODE (v.request_unit_type,
                                 'P',
                                 p.concurrent_program_name,
                                 'S',
                                 s.request_set_name
                         ),
                      ' ',
                      '_'
             )
          || '_frgu.ldt '
   FROM fnd_application va,
        fnd_application a,
        fnd_concurrent_programs p,
        fnd_request_sets s,
        fnd_request_group_units v,
        fnd_request_groups g
   WHERE     1 = 1
         AND g.application_id = va.application_id
         AND v.application_id = g.application_id
         AND v.request_group_id = g.request_group_id
         AND a.application_id = v.unit_application_id
         AND v.unit_application_id = p.application_id(+)
         AND v.unit_application_id = s.application_id(+)
         AND ( (v.request_unit_type = 'P'
                AND v.request_unit_id = p.concurrent_program_id)
              OR (v.request_unit_type = 'S'
                  AND v.request_unit_id = s.request_set_id)
              OR v.request_unit_type NOT IN ('S', 'P'))
         AND DECODE (v.request_unit_type, 'P', v.request_unit_id, NULL) =
               p.concurrent_program_id(+)
         AND DECODE (v.request_unit_type, 'S', v.request_unit_id, NULL) =
               s.request_set_id(+)
   UNION ALL ----------- REQUEST GROUP UNITS
   SELECT 'APPS' owner,
          plsql_type object_name,
          plsql_name object_description,
          last_update_date,
          'WEB PLSQL' object_type,
          'FND_ENABLED_PLSQL',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct'
          || plsql_type
          || '_'
          || plsql_name
          || '_fep.ldt '
          || 'FND_ENABLED_PLSQL PLSQL_TYPE="'
          || plsql_type
          || '" PLSQL_NAME="'
          || plsql_name
          || '"',
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct'
          || plsql_type
          || '_'
          || plsql_name
          || '_fep.ldt '
   FROM fnd_enabled_plsql
   UNION ALL
   ---------------------------------------------------
   --- ALERT
   ---------------------------------------------------
   SELECT a1.application_short_name,
          v.alert_name,
          v.description,
          v.last_update_date,
          'ALERT',
          'ALR_ALERTS',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct'
          || v.alert_name
          || '_alr.ldt '
          || 'ALR_ALERTS APPLICATION_SHORT_NAME="'
          || a1.application_short_name
          || '" ALERT_NAME="'
          || v.alert_name
          || '"',
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct'
          || v.alert_name
          || '_alr.ldt '
   FROM fnd_application a1, alr_alerts v, fnd_application a2
   WHERE a1.application_id = v.application_id
         AND v.table_application_id = a2.application_id(+)
   UNION ALL
   ---------------------------------------------------
   --- USER
   ---------------------------------------------------
   SELECT 'APPS',
          user_name,
          description,
          last_update_date,
          'USER',
          'FND_USER',
          'FNDLOAD apps/$PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct'
          || user_name
          || '_user.ldt FND_USER USER_NAME="'
          || user_name
          || '"',
          'FNDLOAD apps/$PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct '
          || user_name
          || '_user.ldt'
   FROM fnd_user
   ORDER BY 1, 2,  3;
   
OUTPUT :

   SELECT * FROM xx_generate_fnd_scriptc_v ORDER BY LAST_UPDATE_DATE;