Related Queries in O2C Cycle
SELECT HEADER_ID,
ORDER_NUMBER ,
FLOW_STATUS_CODE ,
ORDER_CATEGORY_CODE ,
BOOKED_FLAG ,
ORG_ID
FROM oe_order_headers_all
WHERE order_number = 66435
-----------------------------
SELECT HEADER_ID ,
LINE_ID ,
LINE_TYPE_ID ,
FLOW_STATUS_CODE ,
LINE_NUMBER ,
ORDERED_ITEM ,
ORDERED_QUANTITY ,
UNIT_LIST_PRICE_PER_PQTY
FROM OE_ORDER_LINES_ALL WHERE 1=1
AND HEADER_ID = 192535
------------------------------------
SELECT DELIVERY_DETAIL_ID ,
SOURCE_HEADER_ID ,
SOURCE_LINE_ID ,
CUSTOMER_ID ,
INVENTORY_ITEM_ID ,
RELEASED_STATUS
FROM WSH_DELIVERY_DETAILS WHERE 1=1
AND SOURCE_HEADER_ID = 192535
-------------------------------------------
SELECT *
FROM WSH_NEW_DELIVERIES WHERE 1=1
AND DELIVERY_ID = 3773405
ORDER BY CREATION_DATE DESC
------------------------------------
SELECT *
FROM WSH_DELIVERY_ASSIGNMENTS
WHERE 1=1
AND DELIVERY_DETAIL_ID = 3962503
ORDER BY CREATION_DATE DESC
--------------------------------------------
SELECT *
FROM RA_INTERFACE_LINES_ALL
WHERE 1=1
AND INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
AND INTERFACE_LINE_ATTRIBUTE1 = 66435
AND INTERFACE_LINE_ATTRIBUTE3 = 3773405
SELECT CUSTOMER_TRX_ID ,
TRX_NUMBER ,
INTERFACE_HEADER_ATTRIBUTE1 ,
INTERFACE_HEADER_ATTRIBUTE2 ,
INTERFACE_HEADER_ATTRIBUTE3 ,
INTERFACE_HEADER_CONTEXT ,
STATUS_TRX
FROM RA_CUSTOMER_TRX_ALL WHERE 1=1
AND INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY'
AND INTERFACE_HEADER_ATTRIBUTE1 = '66435'
AND INTERFACE_HEADER_ATTRIBUTE3 = 3773405
ORDER BY CREATION_DATE DESC
-----------------------------------------------
SELECT *
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE 1=1
AND INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
AND INTERFACE_LINE_ATTRIBUTE1 = 66435
AND INTERFACE_LINE_ATTRIBUTE3 = 3773405
ORDER BY CREATION_DATE DESC
------------------------------------------
SELECT *
FROM AR_CASH_RECEIPTS_ALL
WHERE 1=1
AND RECEIPT_NUMBER = 'R10037546'
ORDER BY CREATION_DATE DESC
-----------------------------------------
SELECT *
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE 1=1
AND CASH_RECEIPT_ID = 113993
ORDER BY CREATION_DATE DESC
----------------------------------------
SELECT *
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE 1=1
AND CASH_RECEIPT_ID = 113993
ORDER BY CREATION_DATE DESC **************** O2C Complete Query ****************
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
SELECT HEADER_ID,
ORDER_NUMBER ,
FLOW_STATUS_CODE ,
ORDER_CATEGORY_CODE ,
BOOKED_FLAG ,
ORG_ID
FROM oe_order_headers_all
WHERE order_number = 66435
-----------------------------
SELECT HEADER_ID ,
LINE_ID ,
LINE_TYPE_ID ,
FLOW_STATUS_CODE ,
LINE_NUMBER ,
ORDERED_ITEM ,
ORDERED_QUANTITY ,
UNIT_LIST_PRICE_PER_PQTY
FROM OE_ORDER_LINES_ALL WHERE 1=1
AND HEADER_ID = 192535
------------------------------------
SELECT DELIVERY_DETAIL_ID ,
SOURCE_HEADER_ID ,
SOURCE_LINE_ID ,
CUSTOMER_ID ,
INVENTORY_ITEM_ID ,
RELEASED_STATUS
FROM WSH_DELIVERY_DETAILS WHERE 1=1
AND SOURCE_HEADER_ID = 192535
-------------------------------------------
SELECT *
FROM WSH_NEW_DELIVERIES WHERE 1=1
AND DELIVERY_ID = 3773405
ORDER BY CREATION_DATE DESC
------------------------------------
SELECT *
FROM WSH_DELIVERY_ASSIGNMENTS
WHERE 1=1
AND DELIVERY_DETAIL_ID = 3962503
ORDER BY CREATION_DATE DESC
--------------------------------------------
SELECT *
FROM RA_INTERFACE_LINES_ALL
WHERE 1=1
AND INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
AND INTERFACE_LINE_ATTRIBUTE1 = 66435
AND INTERFACE_LINE_ATTRIBUTE3 = 3773405
SELECT CUSTOMER_TRX_ID ,
TRX_NUMBER ,
INTERFACE_HEADER_ATTRIBUTE1 ,
INTERFACE_HEADER_ATTRIBUTE2 ,
INTERFACE_HEADER_ATTRIBUTE3 ,
INTERFACE_HEADER_CONTEXT ,
STATUS_TRX
FROM RA_CUSTOMER_TRX_ALL WHERE 1=1
AND INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY'
AND INTERFACE_HEADER_ATTRIBUTE1 = '66435'
AND INTERFACE_HEADER_ATTRIBUTE3 = 3773405
ORDER BY CREATION_DATE DESC
-----------------------------------------------
SELECT *
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE 1=1
AND INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
AND INTERFACE_LINE_ATTRIBUTE1 = 66435
AND INTERFACE_LINE_ATTRIBUTE3 = 3773405
ORDER BY CREATION_DATE DESC
------------------------------------------
SELECT *
FROM AR_CASH_RECEIPTS_ALL
WHERE 1=1
AND RECEIPT_NUMBER = 'R10037546'
ORDER BY CREATION_DATE DESC
-----------------------------------------
SELECT *
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE 1=1
AND CASH_RECEIPT_ID = 113993
ORDER BY CREATION_DATE DESC
----------------------------------------
SELECT *
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE 1=1
AND CASH_RECEIPT_ID = 113993
ORDER BY CREATION_DATE DESC **************** O2C Complete Query ****************
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
