O2C Flow SQL Query Link

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 --Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
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;