13 Important Order Management Tables in Oracle Fusion

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.

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.

Order Management Tables in Oracle Fusion
Image Source : docs.oracle.com (https://docs.oracle.com/en/cloud/saas/supply-chain-management/21b/fauom/hello.html#FAUOM4012087)
  • 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 based on Order Management Tables in Oracle Fusion

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_id

Get 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_ID

Find 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 version

Find 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

OM cloud SCM Documentation

Order to Cash Cycle in OM cloud

FAQ

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Logo
      Enable registration in settings - general
      Compare items
      • Total (0)
      Compare
      0
      Shopping cart