OM - Order Hold and Release

/* A common war between Credit and Warehouse Dept is Delivery after Hold removal
Following query will put a focus on Order and hold till releasing a hold */

SELECT H.ORDER_NUMBER,
HO.NAME HOLD_NAME,
HS.HOLD_UNTIL_DATE,
HS.HOLD_COMMENT,
OH.HEADER_ID,
OH.LINE_ID,
OH.ORDER_HOLD_ID,
L.ITEM_IDENTIFIER_TYPE,
L.INVENTORY_ITEM_ID,
L.ORDERED_ITEM,
OHR.RELEASE_COMMENT
FROM OE_ORDER_HOLDS_ALL OH,
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS_ALL H,
OE_HOLD_DEFINITIONS HO,
OE_HOLD_SOURCES_ALL HS,
OE_HOLD_RELEASES OHR
WHERE OH.HEADER_ID = H.HEADER_ID
AND (H.CANCELLED_FLAG IS NULL OR H.CANCELLED_FLAG = 'N')
--AND OH.RELEASED_FLAG != 'Y'
AND H.OPEN_FLAG = 'Y'
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND HS.HOLD_ID = HO.HOLD_ID
AND H.HEADER_ID = L.HEADER_ID(+)
AND L.OPEN_FLAG = 'Y'
AND L.LINE_ID = NVL (OH.LINE_ID, L.LINE_ID)
AND L.SERVICE_REFERENCE_LINE_ID IS NULL
AND OH.HOLD_RELEASE_ID IS NULL
--AND NVL(H.ORG_ID,0) = 204
AND NVL (L.ORG_ID, 0) = NVL (H.ORG_ID, 0)
AND OH.HOLD_RELEASE_ID = OHR.HOLD_RELEASE_ID(+)
ORDER BY HO.NAME, H.ORDER_NUMBER

/* When - Why */

SELECT H.ORDER_NUMBER,
RELEASE_REASON_CODE,
RELEASE_COMMENT,
TO_CHAR (OH.CREATION_DATE, 'DD-MON-YYYY hh24:mm:ss') HOLD_APPLIED_DATE,
TO_CHAR (OHR.CREATION_DATE, 'DD-MON-YYYY hh24:mm:ss') HOLD_RELEASE_DATE
FROM OE_ORDER_HOLDS_ALL OH, OE_HOLD_RELEASES OHR, OE_ORDER_HEADERS_ALL H
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID = OHR.HOLD_RELEASE_ID
AND OHR.CREATION_DATE LIKE SYSDATE