Sales Order SQL script in Oracle Fusion

 

Sales Order SQL script in Oracle Fusion

Two records with OPEN and DOO_REFERENCE is that Order Number 98561 has been revised (edited and resubmitted) at least once.

In Order Management, when an order is revised, Oracle does not overwrite the old data. Instead, it creates a new active version of the order and converts the old version into a historical audit trail.

DOO_REFERENCE: This is the old, inactive, read-only snapshot of the order before it was edited.

OPEN (or PROCESSING): This is the current, active, live version of your order that is moving through fulfillment

So, 

Active records will always have SUBMITTED_FLAG = 'Y' OR 

We can explicitly bypass "DOO_REFERENCE" in Where Clause




SELECT
    DHA.ORDER_NUMBER,
    DHA.ORDERED_DATE,
    DHA.STATUS_CODE AS ORDER_STATUS,
    DHA.ORDER_TYPE_CODE AS ORDER_TYPE,
    HP_SOLD.PARTY_NAME AS CUSTOMER,
    HP_BILL.PARTY_NAME AS BILL_TO_CUSTOMER,
    HP_SHIP.PARTY_NAME AS SHIP_TO_CUSTOMER,
    (
        SELECT CONT.PARTY_NAME
        FROM
            HZ_CUST_ACCOUNTS HCA,
            HZ_PARTIES       CUST,
            HZ_RELATIONSHIPS HR,
            HZ_PARTIES       CONT
        WHERE HCA.PARTY_ID     = CUST.PARTY_ID
          AND CUST.PARTY_ID    = HR.OBJECT_ID
          AND HR.SUBJECT_ID    = CONT.PARTY_ID
          AND HR.RELATIONSHIP_ID = DHA.SOLD_TO_PARTY_CONTACT_ID
          AND CONT.PARTY_NAME  = 'Garrett Collins'
          AND ROWNUM           = 1
    ) AS CONTACT_NAME,
    DLA.ORDERED_UOM,
    DLA.STATUS_CODE AS LINE_STATUS,
    DLA.ORDERED_QTY AS LINE_QTY,
    DLA.LINE_TYPE_CODE AS LINE_TYPE,
    DLA.EXTENDED_AMOUNT AS LINE_AMOUNT,
    DFL.PROCESS_INSTANCE_ID AS PROCESS_NUM,
    DFL.STATUS_CODE AS FULFILL_STA_CODE,
    DFL.SCHEDULE_SHIP_DATE AS SCHE_SHIP_DATE,
    DFL.FULFILL_INSTANCE_ID AS FULFILL_INSTANCE_ID,
    DFL.FULFILLMENT_SPLIT_REF_ID AS FULFILLMENT_SPLIT_REF_ID,
    DFL.ESTIMATE_FULFILLMENT_COST AS EST_FULFILL_COST,
    DFL.LATEST_EXT_FULFILL_LINE_NUMBER AS LATEST_FULFILLMENT_LINE_NUMBER,
    DFL.FULFILLMENT_MODE AS FULFILLMENT_MODE,
    DLA.INVENTORY_ITEM_ID AS INV_ITEM_CODE
FROM
    DOO_HEADERS_ALL          DHA,
    DOO_LINES_ALL            DLA,
    DOO_FULFILL_LINES_ALL    DFL,
    HZ_PARTIES               HP_SOLD,
    DOO_ORDER_ADDRESSES      DOA_BILL,
    HZ_CUST_ACCOUNTS         HCA_BILL,
    HZ_PARTIES               HP_BILL,
    DOO_ORDER_ADDRESSES      DOA_SHIP,
    HZ_CUST_ACCOUNTS         HCA_SHIP,
    HZ_PARTIES               HP_SHIP
WHERE 1=1
    AND DHA.HEADER_ID = DLA.HEADER_ID
    AND DLA.LINE_ID = DFL.LINE_ID
    AND DHA.SOLD_TO_PARTY_ID = HP_SOLD.PARTY_ID(+)
    AND DHA.HEADER_ID = DOA_BILL.HEADER_ID(+)
    AND DOA_BILL.ADDRESS_USE_TYPE(+) = 'BILL_TO'
    AND DOA_BILL.CUST_ACCT_ID = HCA_BILL.CUST_ACCOUNT_ID(+)
    AND HCA_BILL.PARTY_ID = HP_BILL.PARTY_ID(+)
    AND DHA.HEADER_ID = DOA_SHIP.HEADER_ID(+)
    AND DOA_SHIP.ADDRESS_USE_TYPE(+) = 'SHIP_TO'
    AND DOA_SHIP.CUST_ACCT_ID = HCA_SHIP.CUST_ACCOUNT_ID(+)
    AND HCA_SHIP.PARTY_ID = HP_SHIP.PARTY_ID(+)
    AND DHA.ORDER_NUMBER = '98561'
    AND DHA.SUBMITTED_FLAG = 'Y'
    AND DHA.STATUS_CODE <> 'DOO_REFERENCE'

------------------------------------------

Using with Clause 

WITH contact_cte AS (
    SELECT
        HR.RELATIONSHIP_ID,
        CONT.PARTY_NAME AS CONTACT_NAME
    FROM
        HZ_CUST_ACCOUNTS HCA,
        HZ_PARTIES       CUST,
        HZ_RELATIONSHIPS HR,
        HZ_PARTIES       CONT
    WHERE HCA.PARTY_ID     = CUST.PARTY_ID
      AND CUST.PARTY_ID    = HR.OBJECT_ID
      AND HR.SUBJECT_ID    = CONT.PARTY_ID
      AND CONT.PARTY_NAME  = 'Garrett Collins'
      AND ROWNUM           = 1
),

bill_to_cte AS (
    SELECT
        DOA_BILL.HEADER_ID,
        HP_BILL.PARTY_NAME AS BILL_TO_CUSTOMER
    FROM
        DOO_ORDER_ADDRESSES      DOA_BILL,
        HZ_CUST_ACCOUNTS         HCA_BILL,
        HZ_PARTIES               HP_BILL
    WHERE DOA_BILL.ADDRESS_USE_TYPE = 'BILL_TO'
      AND DOA_BILL.CUST_ACCT_ID    = HCA_BILL.CUST_ACCOUNT_ID
      AND HCA_BILL.PARTY_ID        = HP_BILL.PARTY_ID
),

ship_to_cte AS (
    SELECT
        DOA_SHIP.HEADER_ID,
        HP_SHIP.PARTY_NAME AS SHIP_TO_CUSTOMER
    FROM
        DOO_ORDER_ADDRESSES      DOA_SHIP,
        HZ_CUST_ACCOUNTS         HCA_SHIP,
        HZ_PARTIES               HP_SHIP
    WHERE DOA_SHIP.ADDRESS_USE_TYPE = 'SHIP_TO'
      AND DOA_SHIP.CUST_ACCT_ID    = HCA_SHIP.CUST_ACCOUNT_ID
      AND HCA_SHIP.PARTY_ID        = HP_SHIP.PARTY_ID
)

SELECT DISTINCT
    DHA.ORDER_NUMBER,
    DHA.ORDERED_DATE,
    DHA.STATUS_CODE AS ORDER_STATUS,
    DHA.ORDER_TYPE_CODE AS ORDER_TYPE,
    HP_SOLD.PARTY_NAME AS CUSTOMER,
    B.BILL_TO_CUSTOMER,
    S.SHIP_TO_CUSTOMER,
    C.CONTACT_NAME,
    DLA.ORDERED_UOM,
    DLA.STATUS_CODE AS LINE_STATUS,
    DLA.ORDERED_QTY AS LINE_QTY,
    DLA.LINE_TYPE_CODE AS LINE_TYPE,
    DLA.EXTENDED_AMOUNT AS LINE_AMOUNT,
    DFL.PROCESS_INSTANCE_ID AS PROCESS_NUM,
    DFL.STATUS_CODE AS FULFILL_STA_CODE,
    DFL.SCHEDULE_SHIP_DATE AS SCHE_SHIP_DATE,
    DFL.FULFILL_INSTANCE_ID AS FULFILL_INSTANCE_ID,
    DFL.FULFILLMENT_SPLIT_REF_ID AS FULFILLMENT_SPLIT_REF_ID,
    DFL.ESTIMATE_FULFILLMENT_COST AS EST_FULFILL_COST,
    DFL.LATEST_EXT_FULFILL_LINE_NUMBER AS LATEST_FULFILLMENT_LINE_NUMBER,
    DFL.FULFILLMENT_MODE AS FULFILLMENT_MODE,
    DLA.INVENTORY_ITEM_ID AS INV_ITEM_CODE
FROM
    DOO_HEADERS_ALL          DHA,
    DOO_LINES_ALL            DLA,
    DOO_FULFILL_LINES_ALL    DFL,
    HZ_PARTIES               HP_SOLD,
    bill_to_cte              B,
    ship_to_cte              S,
    contact_cte              C
WHERE 1=1
    AND DHA.HEADER_ID = DLA.HEADER_ID
    AND DLA.LINE_ID = DFL.LINE_ID
    AND DHA.SOLD_TO_PARTY_ID = HP_SOLD.PARTY_ID(+)
    AND DHA.HEADER_ID = B.HEADER_ID(+)
    AND DHA.HEADER_ID = S.HEADER_ID(+)
    AND DHA.SOLD_TO_PARTY_CONTACT_ID = C.RELATIONSHIP_ID(+)
    AND DHA.ORDER_NUMBER = '98561'
    AND DHA.SUBMITTED_FLAG = 'Y'
    AND DHA.STATUS_CODE <> 'DOO_REFERENCE'