Find the Product Installed on your EBS Environment

SELECT a.application_id,
  a.application_short_name,
  a.application_name,
  b.oracle_id,
  b.last_update_date,
  b.product_version,
  b.patch_level
FROM Fnd_application_vl a,
            Fnd_PRODUCT_INSTALLATIONS b
WHERE a.application_id = b.application_id;

Find List of Responsibilities For a Request Set in Oracle Apps R12

SELECT frt.responsibility_name,
  frg.request_group_name,
  frgu.request_unit_type,
  frgu.request_unit_id,
  fcpt.user_request_set_name,
  fr.responsibility_id,
  fr.application_id
FROM apps.fnd_Responsibility fr,
  apps.fnd_responsibility_tl frt,
  apps.fnd_request_groups frg,
  apps.fnd_request_group_units frgu,
  apps.fnd_request_Sets_tl fcpt
WHERE frt.responsibility_id    = fr.responsibility_id
AND frg.request_group_id       = fr.request_group_id
AND frgu.request_group_id      = frg.request_group_id
AND fcpt.request_set_id        = frgu.request_unit_id
AND fcpt.user_request_set_name = 'Import Item Organization Assignment';

Sales Order Type Name in Oracle Apps R12

SELECT  distinct ooha.order_type_id, ott.NAME   "Order Type Name"
FROM    oe_transaction_types_tl ott, 
              oe_order_headers_all ooha
 WHERE  ooha.order_type_id = ott.transaction_type_id  ;

OUTPUT :
1162 Delivery - On Account APO
1166 MM APO Charitable Donation
1170 MM APO Trade Shows
1241 MM US Samples - Apollo
1261 MM US Samples - Langley
1143 Will Call - Cash
1384 E-commerce Parcel
1101 Internal Order MT
1168 MM APO Return
1092 Will Call - On Account APO

Query for Supplier Details with Bank in Oracle Apps R12

SELECT       
                aps.vendor_name "vendor_name",
                ass.vendor_site_code "Payee_Identifier"
                , accts.ext_bank_account_id "Payee_Bank_Account_Identifier"
                , bank.party_name "Bank_Name"
                , branch.bank_branch_name "Branch_Name"
                , accts.country_code "Account_Country_Code"
                , accts.bank_account_name "Account_Name"
                , accts.bank_account_num "Account_Number"
                , accts.currency_code "Account_Currency_Code"
                , NULL "Allow_International_Payments"
                , uses.start_date "Account_start_date"
                , uses.end_date "Account_end_date"
                , accts.iban "IBAN"
                ,accts.check_digits "check_digits"
                , accts.bank_account_name_alt "Account_Alternate_Name"
                , accts.bank_account_type "Bank_account_type"
                , accts.account_suffix "Account_Suffix"
                , accts. description "DESCRIPTION"
                , accts.agency_location_code "Agency_Location_Code"
                , accts.exchange_rate_agreement_num   "Exchange_Rate_Agreement_Number"
                , accts.exchange_rate_agreement_type "Exchange_Rate_Agreement_Type"
                , accts.exchange_rate "EXCHANGE_RATE"
                , accts.secondary_account_reference "SECONDARY_ACCOUNT_REFERENCE"
           FROM  ap_suppliers aps
                ,ap_supplier_sites_all ass
                ,iby_pmt_instr_uses_all uses
                ,iby_external_payees_all payee
                ,iby_ext_bank_accounts accts
                ,hz_parties bank
                ,ce_bank_branches_v branch
          WHERE uses.instrument_type   = 'BANKACCOUNT'
            AND aps.vendor_id          = ass.vendor_id
            AND aps.party_id           = payee.payee_party_id
            AND payee.ext_payee_id     = uses.ext_pmt_party_id
            AND payee.payment_function = 'PAYABLES_DISB'
            AND uses.instrument_id     = accts.ext_bank_account_id
            AND accts.bank_id          = bank.party_id
            --AND accts.bank_id          = bankprofile.party_id
            AND accts.branch_id         = branch.branch_party_id
            AND aps.end_date_active  IS NULL
            AND ass.inactive_date    IS NULL;

Key SQL Queries for Purchase Requisition, PO, and Receipt in Oracle Apps R12

SELECT prha.segment1 requisition_number,
       prha.type_lookup_code,
       prha.authorization_status,
       prla.item_description,
       prla.quantity,
       pha.type_lookup_code,
       pha.segment1 po_number,
       pha.document_creation_method,
       pla.item_id,pla.list_price_per_unit,rsh.receipt_num
FROM  po_requisition_headers_all prha,--REQUISITION HEADERS TABLE
      po_requisition_lines_all  prla,--REQUISITIONS LINES TABLE
      po_req_distributions_all prda,--REQUISITIONS DISTRIBUTION TABLE
      po_headers_all pha, --PURCHASE ORDER HEADER TABLE
      po_lines_all pla,--PURCHASE ORDER LINE TABLE
      po_distributions_all pda,--PURCHASE ORDER DISTRIBUTIONS TABLE
      rcv_shipment_headers rsh,--RECEIPT HEADER TABLE
      rcv_shipment_lines rsl--RECEIPT LINE TABLE
WHERE prha.requisition_header_id = prla.requisition_header_id
and  prla.requisition_line_id = prda.requisition_line_id
and  pha.po_header_id = pla.po_header_id
and pla.po_line_id = pda.po_line_id
and prda.distribution_id = pda.req_distribution_id
and rsh.shipment_header_id = rsl.shipment_header_id
and pha.po_header_id = rsl.po_header_id
--and   prha.segment1 = '336276';
and rsh.shipment_header_id = '23163104';

Query to Find Order Management Join Query in Oracle Apps R12

SELECT   ooh.order_number,
         msib.segment1 item_number,
         ool.line_id,
         mr.reservation_quantity,
         mr.reservation_id
FROM     oe_order_headers_all ooh,
         oe_order_lines_all ool,
         mtl_reservations mr,
         mtl_system_items_b msib
WHERE    ooh.header_id = ool.header_id
         AND mr.demand_source_line_id = ool.line_id
         AND ool.ship_from_org_id = msib.organization_id
         AND mr.inventory_item_id = msib.inventory_item_id
         AND ORDER_NUMBER = 2076061;

Find Table and Column Name with table and column description

SELECT fa.application_id,
  fa.application_short_name ,
  fat.application_name ,
  table_name ,
  column_name ,
  ft.description table_description ,
  fc.description column_description
FROM fnd_tables ft ,
  fnd_columns fc ,
  fnd_application_tl fat ,
  fnd_application fa
WHERE ft.table_id      = fc.table_id
AND fat.application_id = ft.application_id
AND fa.application_id  = fat.application_id
AND fc.column_name =:column_name;

Complex Sql Queries - Part 2

1. Display the names of the employees who are working in the company for The past 5 years.
      SELECT ename
      FROM emp
      WHERE TO_CHAR (sysdate,'YYYY')-TO_CHAR(hiredate,'YYYY')>=5;

2. Display your age in days.
      SELECT to_date (sysdate)-to_date ('01-apr-96')
      FROM dual;

3. Display your age in months.
      SELECT months_between (sysdate,'01-apr-96')
      FROM dual;

4. Display the 10th record of emp table without using group by and rowid?
      SELECT *
      FROM EMP
      WHERE ROWNUM<11
      MINUS
      SELECT * FROM EMP WHERE ROWNUM <10;

5. Display the various jobs and total number of employees within each job Group.
      SELECT job,
      COUNT (job)
     FROM EMP
     GROUP BY job;

6. Display the depart numbers and max salary for each department.
      SELECT deptno,
      MAX (sal)
     FROM EMP
     GROUP BY deptno;

7. Display the depart numbers with more than three employees in each dept.
      SELECT deptno,
     COUNT (deptno)
     FROM EMP
     GROUP BY deptno
     HAVING COUNT (*)>3;

8. Display the name of the employee who earns the highest salary.
      SELECT ename
     FROM emp
     WHERE sal=
     (SELECT MAX (sal) FROM emp);

9. Display the names of the employees who earn the highest salary in their respective departments.
     SELECT ename,
                    sal,
                    deptno
     FROM EMP
     WHERE sal IN
     (SELECT MAX (sal) FROM EMP GROUP BY deptno);

10. Find out the top 5 earners of the company?
     SELECT DISTINCT SAL
     FROM EMP E
    WHERE 5>=
    (SELECT COUNT (DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL)
    ORDER BY SAL DESC;

     

Complex SQL Queries

1. Display Salary in Words.
    SELECT ename,
                   sal,
               TO_CHAR(to_date(sal,'jsp'),'jsp')"in words"
    FROM emp;

2. Find the 3rd MAX salary in the emp table.
    SELECT DISTINCT sal
    FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e1.sal <= e2.sal);

3. Find the 3rd MIN salary in the emp table.
     SELECT DISTINCT sal
     FROM emp e1
    WHERE 3 =
   (SELECT COUNT(DISTINCT sal) FROM emp e2 where e1.sal >= e2.sal);

4. Select FIRST n records from a table.
     SELECT * FROM emp WHERE rownum <= &n;

5. Select LAST n records from a table.
     SELECT * FROM emp
     MINUS
    SELECT * FROM emp WHERE rownum <=
   (SELECT COUNT(*) - &n FROM emp);

6. How to get 3 Max salaries?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
   (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal <= b.sal  )
   ORDER BY a.sal DESC;

7. How to get 3 Min salaries ?.
    SELECT DISTINCT sal
    FROM emp a
   WHERE 3 >=
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal);

8. How to get nth max salaries?
   SELECT DISTINCT hire_date
   FROM emp a
   WHERE
   &n =
  (SELECT COUNT(DISTINCT sal) FROM emp b WHERE a.sal >= b.sal );

Query to Get the Account Flex field Structure

SELECT DISTINCT sob.name Ledger_Name ,
  sob.set_of_books_id,
  sob.chart_of_accounts_id coa_id ,
  fifst.id_flex_structure_name struct_name ,
  ifs.segment_name ,
  ifs.application_column_name column_name ,
  sav1.attribute_value BALANCING ,
  sav2.attribute_value COST_CENTER ,
  sav3.attribute_value NATURAL_ACCOUNT ,
  sav4.attribute_value INTERCOMPANY ,
  sav5.attribute_value SECONDARY_TRACKING ,
  sav6.attribute_value GLOBAL ,
  ffvs.flex_value_set_name ,
  ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs ,
  fnd_id_flex_structures_tl fifst ,
  fnd_segment_attribute_values sav1 ,
  fnd_segment_attribute_values sav2 ,
  fnd_segment_attribute_values sav3 ,
  fnd_segment_attribute_values sav4 ,
  fnd_segment_attribute_values sav5 ,
  fnd_segment_attribute_values sav6 ,
  fnd_id_flex_segments ifs ,
  fnd_flex_value_sets ffvs ,
  gl_sets_of_books sob
WHERE 1                          =1
AND fifs.id_flex_code            = 'GL#'
AND fifs.application_id          = fifst.application_id
AND fifs.id_flex_code            = fifst.id_flex_code
AND fifs.id_flex_num             = fifst.id_flex_num
AND fifs.application_id          = ifs.application_id
AND fifs.id_flex_code            = ifs.id_flex_code
AND fifs.id_flex_num             = ifs.id_flex_num
AND sav1.application_id          = ifs.application_id
AND sav1.id_flex_code            = ifs.id_flex_code
AND sav1.id_flex_num             = ifs.id_flex_num
AND sav1.application_column_name = ifs.application_column_name
AND sav2.application_id          = ifs.application_id
AND sav2.id_flex_code            = ifs.id_flex_code
AND sav2.id_flex_num             = ifs.id_flex_num
AND sav2.application_column_name = ifs.application_column_name
AND sav3.application_id          = ifs.application_id
AND sav3.id_flex_code            = ifs.id_flex_code
AND sav3.id_flex_num             = ifs.id_flex_num
AND sav3.application_column_name = ifs.application_column_name
AND sav4.application_id          = ifs.application_id
AND sav4.id_flex_code            = ifs.id_flex_code
AND sav4.id_flex_num             = ifs.id_flex_num
AND sav4.application_column_name = ifs.application_column_name
AND sav5.application_id          = ifs.application_id
AND sav5.id_flex_code            = ifs.id_flex_code
AND sav5.id_flex_num             = ifs.id_flex_num
AND sav5.application_column_name = ifs.application_column_name
AND sav6.application_id          = ifs.application_id
AND sav6.id_flex_code            = ifs.id_flex_code
AND sav6.id_flex_num             = ifs.id_flex_num
AND sav6.application_column_name = ifs.application_column_name
AND sav1.segment_attribute_type  = 'GL_BALANCING'
AND sav2.segment_attribute_type  = 'FA_COST_CTR'
AND sav3.segment_attribute_type  = 'GL_ACCOUNT'
AND sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
AND sav5.segment_attribute_type  = 'GL_SECONDARY_TRACKING'
AND sav6.segment_attribute_type  = 'GL_GLOBAL'
AND ifs.id_flex_num              = sob.chart_of_accounts_id
AND ifs.flex_value_set_id        = ffvs.flex_value_set_id;

Query to get details of all Concurrent programs used by a Request Set

SELECT rs.user_request_set_name ,
  rs.request_set_name,
  rs.description ,
  rss.display_sequence Seq,
  cp.user_concurrent_program_name ,
  ap.application_name ,
  e.executable_name ,
  e.execution_file_name ,
  lv. meaning
FROM apps.fnd_request_sets_vl rs,
  apps.fnd_req_set_stages_form_v rss,
  applsys.fnd_request_set_programs rsp,
  apps.fnd_concurrent_programs_vl cp,
  apps.fnd_executables e,
  apps.fnd_lookup_values lv,
  apps.fnd_application_vl ap
WHERE rs.application_id          = rss.set_application_id
AND rs.request_set_id            = rss.request_set_id
AND rss.set_application_id       = rsp.set_application_id
AND rss.request_set_id           = rsp.request_set_id
AND rss.request_set_stage_id     = rsp.request_set_stage_id
AND rsp.program_application_id   = cp.application_id
AND rsp.concurrent_program_id    = cp.concurrent_program_id
AND cp.executable_id             = e.executable_id
AND cp.executable_application_id = e.application_id
AND e.application_id             =ap.application_id
AND lv.lookup_code = e.execution_method_code
AND lv.language         ='US'
AND ap.application_name = 'Order Management';

Query to get DFF and Segment Values

SELECT ffv.descriptive_flexfield_name,
  ffv.application_table_name,
  ffv.title,
  ap.application_name ,
  ffc.descriptive_flex_context_code ,
  ffc.descriptive_flex_context_name,
  ffc.description ,
  ffc.enabled_flag ,
  att.column_seq_num ,
  att.form_left_prompt ,
  att.application_column_name ,
  fvs.flex_value_set_name ,
  att.display_flag ,
  att.enabled_flag ,
  att.required_flag
FROM fnd_descriptive_flexs_vl ffv,
  fnd_descr_flex_contexts_vl ffc,
  fnd_descr_flex_col_usage_vl att,
  fnd_flex_value_sets fvs,
  fnd_application_vl ap
WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id                =ffv.application_id
AND ffv.descriptive_flexfield_name   = ffc.descriptive_flexfield_name
AND ffv.application_id               = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id            =att.flex_value_set_id
AND ap.application_name              = 'Order Management'
AND ffv.application_table_name       ='OE_ORDER_HEADERS_ALL';

Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name,
  fcp.concurrent_program_name,
  fcp.concurrent_program_id,
  fav.application_short_name,
  fav.application_name,
  fav.application_id,
  fdfcuv.end_user_column_name,
  fdfcuv.form_left_prompt prompt ,
  fdfcuv.enabled_flag,
  fdfcuv.required_flag,
  fdfcuv.display_flag
FROM fnd_concurrent_programs fcp ,
  fnd_concurrent_programs_tl fcpl ,
  fnd_descr_flex_col_usage_vl fdfcuv ,
  fnd_application_vl fav
WHERE fcp.concurrent_program_id       = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fav.application_id                =fcp.application_id
AND fcpl.language                     = 'US'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'|| fcp.concurrent_program_name;

Query To Find Concurrent Program Attached To a Responsibility

SELECT fcpt.user_concurrent_program_name,
  frt.responsibility_name,
  frg.request_group_name
FROM fnd_Responsibility fr,
  fnd_responsibility_tl frt,
  fnd_request_groups frg,
  fnd_request_group_units frgu,
  fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id           = fr.responsibility_id
AND frg.request_group_id              = fr.request_group_id
AND frgu.request_group_id             = frg.request_group_id
AND fcpt.concurrent_program_id        = frgu.request_unit_id
AND fcpt.user_concurrent_program_name = '&conc_program_name';

Query to list all the responsibilities attached to a user

SELECT fu.user_name,
  fr.responsibility_name,
  furg.start_date,
  furg.end_date
FROM fnd_user_resp_groups_direct furg,
  fnd_user fu,
  fnd_responsibility_tl fr
WHERE upper(fu.user_name)  =upper(:user_name)
AND furg.user_id           = fu.user_id
AND furg.responsibility_id = fr.responsibility_id
AND fr.language            = userenv('lang');

Query to find request group and application name for a concurrent program

SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
  DECODE(rgu.request_unit_type, 'P', 'Program', 'S', 'Set', rgu.request_unit_type) "Unit Type",
  cp.concurrent_program_name "Concurrent Program Short Name",
  rg.application_id "Application ID",
  rg.request_group_name "Request Group Name",
  fat.application_name "Application Name",
  fa.application_short_name "Application Short Name",
  fa. basepath "Basepath"
FROM fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  fnd_concurrent_programs_tl cpt,
  fnd_application fa,
  fnd_application_tl fat
WHERE rg.request_group_id    = rgu.request_group_id
AND rgu.request_unit_id      = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id        = fat.application_id
AND fa.application_id        = fat.application_id
AND cpt.language             = USERENV('LANG')
AND fat.language             = USERENV('LANG')
AND cpt.user_concurrent_program_name LIKE 'Reservations Interface Manager';

Oracle Concurrent Request Error Script

SELECT a.request_id "Req Id" ,
  a.phase_code,
  a.status_code ,
  actual_start_date ,
  actual_completion_date ,
  c.concurrent_program_name
  || ': '
  || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,
  APPLSYS.fnd_concurrent_processes b,
  applsys.fnd_concurrent_queues q ,
  APPLSYS.fnd_concurrent_programs c ,
  APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager   = b.concurrent_process_id
AND a.concurrent_program_id   = c.concurrent_program_id
AND a.program_application_id  = c.application_id
AND a.status_code             = 'E'
AND a.phase_code              = 'C'
AND b.queue_application_id    = q.application_id
AND b.concurrent_queue_id     = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE              = 'US'
ORDER BY 5 DESC;

Concurrent Program Queries

SELECT request_id,
  DECODE(phase_code,'C','Completed',phase_code)phase_code,
  DECODE(status_code, 'D', 'Cancelled' , 'E', 'Error', 'G', 'Warning', 'H', 'On Hold', 'T', 'Terminating', 'M', 'No Manager', 'X', 'Terminated', 'C', 'Normal', status_code)status_code,
  TO_CHAR(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date,
  TO_CHAR(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
  program,
  user_concurrent_program_name,
  completion_text,
  requestor,
  request_date
FROM fnd_conc_req_summary_v
ORDER BY request_date DESC;

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;