Query to get the Oracle Order to Cash (O2C) Details

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 oe_order_headers_all ooha,

       oe_order_lines_all oola,

       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,

       mtl_system_items_b msib,

       wsh_delivery_details wdd,

       wsh_new_deliveries wnd,

       wsh_delivery_assignments wda,

       ra_customer_trx_all rct,

       ra_customer_trx_lines_all rctl,

       ra_cust_trx_line_gl_dist_all rctld,      

       ar_cash_receipts_all acr,

       xla.xla_transaction_entities xte,

       xla_events xe,

       xla_ae_headers xah,

       xla_ae_lines xal,

       xla_distribution_links xdl,

       gl_import_references gir,

       gl_je_batches gjb,

       gl_je_headers gjh,

       gl_je_lines gjl

 where ooha.order_number = :SalesOrderNumber

   and ooha.org_id = 204

   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 = 'G-1001'

   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 = 1318

   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