O2C - Query to Get Order to Cash (OM, WSH, AR, XLA, GL) Details

SELECT

    ooha.org_id

    ,hou.name ORG_NAME

    ,mp.organization_code WARE_HOUSE

    ,ott.NAME ORDER_TYPE

    ,ooha.header_id

    ,ooha.order_number

    ,hca.account_name

    ,hca.account_number CUSTOMER_NUMBER

    ,ooha.ordered_date DATE_ORDERED

    ,qh.NAME PRICE_LIST

    ,rsa.NAME SALES_PERSON

    ,rtt.NAME PAYMENT_TERMS

    ,ooha.transactional_curr_code CURRENCY

    ,ooha.freight_carrier_code SHIPPING_METHOD

    ,ooha.flow_status_code STATUS

    ,rtt.NAME PAYMENT_TERMS

    ,hp.party_name

    ,hpsb.status bill_to_status

    ,hzsuab.location || ','|| bill_loc.address2|| ','|| bill_loc.city|| ','|| bill_loc.state|| ','|| bill_loc.postal_code|| ','|| bill_loc.county  BILL_TO

    ,hpss.status ship_to_status

    ,hzsuas.location || ','|| ship_loc.address2|| ','|| ship_loc.city|| ','|| ship_loc.state|| ','|| ship_loc.postal_code|| ','|| ship_loc.county  SHIP_TO

    ,oola.line_id

    ,oola.line_number LINE_NUMBER

    ,ottl.NAME LINE_TYPE

    ,oola.flow_status_code Line_Status

    ,oola.ordered_item ORDERED_ITEM

    ,oola.order_quantity_uom UOM

    ,oola.ordered_quantity QTY

    ,oola.unit_selling_price UNIT_SELLING_PRICE

    ,NVL(oola.tax_value,0) LINE_TAX

    ,((NVL(oola.ordered_quantity,0)) * (NVL(oola.unit_selling_price,0)) + NVL(oola.tax_value,0)) LINE_TOTAL   

    ,msib.description item_description

    ,wnd.name delivery_number

    ,rct.trx_number

    ,acr.receipt_number

    ,gjh.ledger_id

    ,gjb.name batch_name

    ,gjh.je_source

    ,gjh.period_name

    ,gjh.status

    ,gjh.name journal_name

    ,gjh.description

FROM

    hr_operating_units hou

    ,mtl_parameters mp

    ,oe_transaction_types_tl ott

    ,qp_list_headers qh

    ,ra_terms_tl rtt

    ,ra_salesreps_all rsa

    ,oe_order_headers_all ooha

    ,oe_transaction_types_tl ottl

    ,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_receivable_applications_all app

    ,ar_payment_schedules_all ps_inv

    ,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     1 = 1

    AND hou.organization_id = ooha.org_id

    AND mp.organization_id = ooha.ship_from_org_id

    AND hca.cust_account_id = ooha.sold_to_org_id

    AND ooha.order_type_id = ott.transaction_type_id

    AND ooha.salesrep_id = rsa.salesrep_id

    AND qh.list_header_id = ooha.price_list_id

    AND ott.LANGUAGE = USERENV ('LANG')   

    AND rtt.LANGUAGE = USERENV ('LANG')

    AND rtt.term_id = ooha.payment_term_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 oola.line_type_id = ottl.transaction_type_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.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 app.cash_receipt_id = acr.cash_receipt_id

    AND rct.customer_trx_id(+) = ps_inv.customer_trx_id

    AND app.applied_payment_schedule_id = ps_inv.payment_schedule_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 gir.reference_5 = to_char (xte.entity_id)

    AND gir.reference_6 = to_char (xe.event_id)

    AND gir.reference_7 = to_char (xah.ae_header_id)

    AND gir.gl_sl_link_id = xal.gl_sl_link_id

    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

    AND acr.receipt_number = 'APPS88-343452'

    AND ooha.order_number = 45782