AR to GL link Query [ O2C full flow technical reference ]

/* Formatted on 5/12/2017 4:23:28 PM (QP5 v5.114.809.3010) */
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