This OM Delivery Assignments query fetches order-level data along with associated delivery assignment details by joining the relevant order and delivery tables.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves delivery, customer, order, and item details from Oracle Shipping and Order Management module.
HOU.NAME AS OPERATING_UNIT,
HAOU.NAME AS WAREHOUSE,
WDD.ITEM_DESCRIPTION ,
WND.DELIVERED_DATE ,
WND.DELIVERY_TYPE ,
WND.STATUS_CODE AS DELIVERY_STATUS_CODE,
WDA.DELIVERY_ASSIGNMENT_ID ,
HCA.ACCOUNT_NUMBER AS CUSTOMER_NUMBER,
HCA.ACCOUNT_NAME AS CUSTOMER_NAME,
DHA.ORDER_NUMBER ,
DHA.ORDERED_DATE ,
HCA.CUSTOMER_TYPE ,
HCA.CUSTOMER_CLASS_CODE,
HCA.CUST_ACCOUNT_ID ,
WND.VOLUME ,
WND.VOLUME_UOM_CODE ,
WND.LOADING_SEQUENCE ,
WND.WAYBILL ,
WND.WEIGHT_UOM_CODE ,
WND.SHIP_METHOD_CODE ,
WND.CURRENCY_CODE ,
WDD.UNIT_PRICE ,
WND.GROSS_WEIGHT ,
WND.FOB_CODE ,
WND.FREIGHT_TERMS_CODE ,
WND.ASN_SEQ_NUMBER ,
DECODE(WDA.ACTIVE_FLAG, 'N', 'NO', 'Y', 'YES', NULL) AS ACTIVE_FLAG,
ESI.INVENTORY_ITEM_ID ,
HCA.TAX_ROUNDING_RULE
FROM
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
DOO_HEADERS_ALL DHA,
HZ_CUST_ACCOUNTS HCA,
EGP_SYSTEM_ITEMS ESI,
HR_OPERATING_UNITS HOU,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID(+)
AND WDD.SOURCE_HEADER_ID = DHA.HEADER_ID(+)
AND DHA.SOLD_TO_PARTY_ID = HCA.CUST_ACCOUNT_ID(+)
AND DHA.ORG_ID = HOU.ORGANIZATION_ID(+)
AND WDD.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID
AND WDD.ORGANIZATION_ID = ESI.ORGANIZATION_ID
AND WDD.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+);