Order Management Tables in Oracle Fusion are the core table of SCM distributed order orchestration module. Oracle Fusion Order Management is used to manage the order lifecycle from Creation, Scheduling, Reservations, Shipping, Invoicing and Billing, and Receipts.
If you are from Oracle EBS background there are many tables that are similar. But the most common ones which you would need to query during your day-to-day Order management activities related to BIP Reporting, querying , Integration are listed below.
Frequently Used
Table of Contents
DOO Orchestration Order Management Tables in Oracle Fusion Cloud
DOO stands for Distributed Order Orchestration. Before Oracle providing a full-fledged order management application DOO was released which could be used to orchestrate orders originating from different source systems and fulfillment from any other target system. From there comes the name DOO and it’s prefixed with many table names.
Many of the Order Management Tables in Oracle Fusion have a prefix of DOO.
- DOO_HEADERS_ALL – For Order Headers
- DOO_LINES_ALL- For Order Lines
- DOO_FULFILL_LINES_ALL- Fulfillment details
- DOO_FULFILL_LINE_DETAILS- Fulfillment Line Details
Order Management Shipping tables in Oracle Fusion ( Like in EBS)
- WSH_DELIVERY_DETAILS
- WSH_DELIVERY_ASSIGNMENTS
- WSH_NEW_DELIVERIES
Order Management Shipping Interface tables in Oracle Fusion
- WSH_DEL_DETAILS_INTERFACE
- WSH_INTERFACE_ERRORS
select * from wsh_interface_errors where interface_error_group_id IN (select delivery_interface_id from wsh_new_del_interface where delivery_name = '<>');
Order Management Carriers tables in Oracle Fusion
- WSH_CARRIERS
Order Management Price List Tables in Oracle Fusion
- QP_PRICE_LISTS_VL
- QP_PRICE_LIST_ITEMS
- QP_MATRIX_RULES_VL
Useful SQL queries on Order Management Tables in linking doo_headers_all
Select * from DOO_HEADERS_ALL where Order_number = 97532
Select * from DOO_LINES_ALL_V where HEADER_ID = 300000220067866
Select * From DOO_FULFILL_LINES_ALL where SOURCE_ORDER_NUMBER =’ 97532′
select * from wsh_interface_errors order by creation_date desc
select * from WSH_DEL_DETAILS_INTERFACE order by creation_date desc
To get shipment details of fulfillment lines of an order
SELECT fl.source_order_number ,
fl.fulfill_line_id ,
dd.delivery_detail_id ,
fl.fulfillment_split_ref_id,
fl.ordered_qty ,
fl.shipped_qty ,
fl.status_code ,
dd.lot_number ,
dd.released_status ,
dd.shipped_quantity
FROM fusion.doo_fulfill_lines_all fl,
fusion.wsh_delivery_details dd
WHERE fl.source_order_number LIKE '97540'
AND fl.status_code IN ( 'BACKORDERED', 'WAITING', 'AWAIT_SHIP' )
AND dd.released_status IN ( 'B', 'R' )
AND dd.released_status NOT IN ( 'C', 'D' )
AND NVL(fl.fulfillment_split_ref_id, fl.fulfill_line_id) = dd.source_shipment_idGet Order and Customer name
SELECT dha.ORDER_NUMBER ,
dha.source_order_number,
dha.SOLD_TO_PARTY_ID ,
dha.STATUS_CODE ,
hz.PARTY_ID ,
hz.PARTY_NUMBER ,
hz.PARTY_NAME
FROM fusion.doo_headers_all dha,
fusion.HZ_PARTIES HZ
WHERE dha.SOURCE_ORDER_NUMBER = ('&SOURCE_ORDER_NUMBER')
-- AND status_code <> 'DOO_REFERENCE'
-- AND Submitted_Flag = 'Y' -- is this the active/submitted version
and hz.PARTY_ID =dha.SOLD_TO_PARTY_IDFind SHIP TO information on Order Header
SELECT SOURCE_ORDER_NUMBER,
SOLD_TO_CUSTOMER_ID,
SOLD_TO_PARTY_ID ,
HZP.PARTy_name
||
' '
||
HZP.PARTY_NUMBER "Sold to Customer",
DOA.ADDRESS_USE_TYPE ,
hza.account_number ,
hzp_ship_to.party_name ,
hza.account_name ,
doa.PARTY_SITE_ID ,
hzl.ADDRESS1 ,
hzl.ADDRESS2 ,
hzl.ADDRESS3 ,
hzl.ADDRESS4 ,
hzl.CITY ,
hzl.POSTAL_CODE ,
hzl.STATE ,
hzl.COUNTRY
FROM FUSION.HZ_PARTIES HZP ,
FUSION.HZ_PARTIES HZP_SHIP_TO ,
FUSION.DOO_HEADERS_aLL DHA ,
fusion.DOO_ORDER_ADDRESSES DOA ,
fusion.HZ_CUST_ACCOUNTS HZA ,
fusion.HZ_CUST_ACCT_SITES_ALL hzcasa,
fusion.HZ_PARTY_SITES hzps ,
fusion.hz_locations HZL
WHERE HZP.PARTY_ID = DHA.SOLD_TO_PARTY_ID
AND dha.header_id = doa.header_id (+)
AND
(
doa.ADDRESS_USE_TYPE = 'SHIP_TO'
OR doa.ADDRESS_USE_TYPE IS NULL
)
AND doa.party_site_id = hzps.party_site_id (+)
AND hzcasa.PARTY_SITE_ID (+) = hzps.PARTY_SITE_ID
AND hzps.party_id = hzp_ship_to.party_id (+)
AND HZcasa.CUST_ACCounT_ID = hza.CUST_ACCOUNT_ID (+)
AND hzps.location_id = hzl.location_id (+)
AND DHA.SOURCE_ORDER_NUMBER = ('&SOURCE_ORDER_NUMBER')
AND DHA.status_code <> 'DOO_REFERENCE'
AND DHA.Submitted_Flag = 'Y' -- this the active/submitted version
Find BILL TO information on Order Header
SELECT SOURCE_ORDER_NUMBER,
SOLD_TO_CUSTOMER_ID,
SOLD_TO_PARTY_ID ,
HZP.PARTy_name
||
' '
||
HZP.PARTY_NUMBER "Sold to Customer",
DOA.ADDRESS_USE_TYPE ,
hza.account_number ,
hza.account_name ,
doa.CUST_ACCT_ID ,
doa.CUST_ACCT_SITE_USE_ID ,
hzl.ADDRESS1 ,
hzl.ADDRESS2 ,
hzl.ADDRESS3 ,
hzl.ADDRESS4 ,
hzl.CITY ,
hzl.POSTAL_CODE ,
hzl.STATE ,
hzl.COUNTRY
FROM FUSION.HZ_PARTIES HZP ,
FUSION.DOO_HEADERS_aLL DHA ,
fusion.DOO_ORDER_ADDRESSES DOA ,
fusion.HZ_CUST_ACCOUNTS HZA ,
fusion.HZ_CUST_SITE_USES_ALL hzcsua ,
fusion.HZ_CUST_ACCT_SITES_ALL hzcasa,
fusion.HZ_PARTY_SITES hzps ,
fusion.hz_locations HZL
WHERE HZP.PARTY_ID = DHA.SOLD_TO_PARTY_ID
AND dha.header_id = doa.header_id (+)
AND
(
doa.ADDRESS_USE_TYPE = 'BILL_TO'
OR doa.ADDRESS_USE_TYPE IS NULL
)
AND DOA.CUST_ACCT_ID = hza.CUST_ACCOUNT_ID (+)
AND DOA.CUST_ACCT_SITE_USE_ID = hzcsua.SITE_USE_ID(+)
AND hzcsua.CUST_ACCT_SITE_ID = hzcasa.CUST_ACCT_SITE_ID (+)
AND hzcasa.PARTY_SITE_ID = hzps.PARTY_SITE_ID (+)
AND hzps.location_id = hzl.location_id (+)
AND DHA.SOURCE_ORDER_NUMBER = ('&SOURCE_ORDER_NUMBER')
-- AND DHA.status_code <> 'DOO_REFERENCE'
-- AND DHA.Submitted_Flag = 'Y' -- is this the active/submitted versionFind BILL TO and SHIP TO information on Order Line
SELECT dha.Source_order_number ,
dha.order_number ,
dha.submitted_Flag ,
dfla.SHIP_TO_PARTY_ID ,
dfla.SHIP_TO_PARTY_SITE_ID,
dfla.BILL_TO_CUSTOMER_ID ,
dfla.BILL_TO_SITE_USE_ID
FROM Fusion.DOO_headers_all dha,
Fusion.DOO_fulfill_lines_all dfla
WHERE dha.header_id = dfla.header_id
AND dha.source_order_number = '&ENTER SOURCE_ORDER_NUMBER'For more details on oracle SCM cloud documentations refer
OM Cloud Complete list of Tables and Views
Order to Cash Cycle in OM cloud
