OM Active Holds of an Order (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Order Management (OM-Fusion-SQL's)
1

This OM Active Holds of an Order query displays hold information for submitted orders as of the current date, helping users identify and resolve fulfillment blockers in a timely manner.

#sqlquery

select  
-- SQL4FUSION (AN ORBIT ANALYTICS PROJECT) 
-- This query display hold details for the submitted orders.
   DHA.SOURCE_ORDER_NUMBER,
   DLO.STATUS_CODE,
   DLO.SOURCE_ORDER_SYSTEM,
   DHCT.HOLD_NAME,
   DHCT.HOLD_DESCRIPTION,
	DLO.ORDERED_QTY ORDERED_QUANTITY,
	DLO.CANCELED_QTY CANCELED_QUANTITY,
  DHI.DOO_HEADER_ID HOLD_INSTANCE_HEADER_ID,
  DHI.DOO_LINE_ID HOLD_INSTANCE_LINE_ID,
  DHI.PENDING_FLAG,
  DHI.DELETED_FLAG,
    DHI.APPLY_SYSTEM,
  DHI.APPLY_DATE,
    DHI.RELEASE_DATE,
  DHI.HOLD_RUNNING_TASK_FLAG,
  DHI.HOLD_COMMENTS,
  DHI.HOLD_RELEASE_REASON_CODE,
  DHI.HOLD_RELEASE_COMMENTS,
  DHI.ACTIVE_FLAG,
   DHCB.SYSTEM_FLAG,
   DHCB.APPLY_HOLD_ROLE_ELIGIBILITY,
  DHCB.RELEASE_HOLD_ROLE_ELIGIBILITY,
  DHCB.SEED_DATA_SOURCE,
  DHA.SUBMITTED_FLAG
FROM
  DOO_HOLD_INSTANCES DHI,
  DOO_HOLD_CODES_B DHCB,
  DOO_HEADERS_ALL DHA,
  DOO_LINES_ALL DLO,
  DOO_HOLD_CODES_TL DHCT,
  DOO_FULFILL_LINES_ALL DFLA
WHERE
  1 = 1
  AND DHI.HOLD_CODE_ID = DHCB.HOLD_CODE_ID
  AND DHA.HEADER_ID = DHI.DOO_HEADER_ID
  AND DHA.HEADER_ID = DLO.HEADER_ID
  AND DHA.HEADER_ID = DFLA.HEADER_ID
  AND DFLA.LINE_ID = DLO.LINE_ID
  AND DHI.DOO_LINE_ID = DLO.LINE_ID
  AND DHCT.HOLD_CODE_ID = DHCB.HOLD_CODE_ID
  AND DHCT.LANGUAGE = USERENV('LANG')
 AND DHA.SUBMITTED_FLAG = 'Y'