Oracle Cloud ERP (Fusion SaaS) – Sales Order Queries

Below are the main tables of Distributed Order Orchestration – DOO

--For Sales Order Header
SELECT * FROM doo_headers_all where order_number='754553'
--For Sales Order Lines
SELECT     dha.order_number,     dla.line_id,      dha.header_id
FROM
    fusion.doo_lines_all    dla,
    fusion.doo_headers_all          dha
WHERE         1 = 1
    AND dla.header_id = dha.header_id
    AND dha.order_number = '754547'

--For Fulfillment Lines
SELECT
    dla.source_line_id,
    dha.order_number,
    dfl.fulfill_line_id,
    dfl.header_id,
    dla.line_number,
    dfl.source_line_number
FROM
    fusion.doo_fulfill_lines_all    dfl,
    fusion.doo_headers_all          dha,
    fusion.doo_lines_all            dla
WHERE
        1 = 1
    AND dfl.header_id = dha.header_id
    AND dla.header_id = dha.header_id
    AND dla.line_id = dfl.line_id
    AND dha.header_id = 300000772162683

--For Hold Code Details
select DHCT.HOLD_NAME, DHCT.HOLD_DESCRIPTION, DHCT.HOLD_CODE_ID , DHCB.HOLD_CODE
from DOO_HOLD_CODES_TL DHCT , DOO_HOLD_CODES_B  DHCB
WHERE 1=1
AND DHCT.LANGUAGE='US'
AND DHCB.HOLD_CODE_ID = DHCT.HOLD_CODE_ID

--For Sales Order Header EFF Extract
SELECT
    dha.order_number,
    dheb.eff_line_id,
    dheb.header_id,
    dheb.context_code,
    dheb.attribute_char1    ,
    dha.creation_date,
    dha.created_by
FROM
    fusion.doo_headers_eff_b    dheb,
    fusion.doo_headers_all      dha
WHERE
        1 = 1
    AND dha.header_id = dheb.header_id
    AND dha.order_number = '754933'
    AND dha.object_version_number = (
        SELECT
            MAX(object_version_number)
        FROM
            fusion.doo_headers_all
        WHERE
            order_number = '754933'
            and open_flag='Y'
    )