Hold Details for an Order.sql

-- SQL Query - Obtain Hold Details for an Order and Hold Code and Release Reason Code Defintitions
;
-- Holds which have been released:
SELECT
	oh.Source_Order_Number
  , hdi.DOO_Header_ID
  , hdi.DOO_Line_id
  , hdi.Fulfill_Line_id
  , TRANSACTION_ENTITY_NAME1
  , hdi.TRANSACTION_ENTITY_ID1
  , dhcb.HOLD_CODE
  , dhct.HOLD_NAME
  , dhct.HOLD_DESCRIPTION
  , dhct.HOLD_CODE_ID
  , dhcb.EFFECTIVE_START_DATE
  , dhcb.EFFECTIVE_END_DATE
  , dhcb.GENERAL_HOLD_FLAG
  , dhcb.SYSTEM_FLAG
  , dhcb.APPLY_HOLD_ROLE_ELIGIBILITY
  , dhcB.RELEASE_HOLD_ROLE_ELIGIBILITY
  , hdi.pending_flag
  , hdi.deleted_flag
  , hdi.Hold_Instance_id
  , hdi.hold_code_id
  , hdi.APPLY_SYSTEM
  , hdi.apply_date
  , hdi.APPLY_USER_ID
  , hdi.RELEASE_USER_ID
  , hdi.RELEASE_DATE
  , hdi.HOLD_RUNNING_TASK_FLAG
  , hdi.HOLD_COMMENTS
  , hdi.HOLD_RELEASE_REASON_CODE
  , hdi.HOLD_RELEASE_COMMENTS
  , hdi.Active_Flag
  , hdi.TRANSACTION_ENTITY_ID1
  , hdi.HOLD_RELEASE_REASON_CODE
  , hdi.HOLD_RELEASE_COMMENTS
FROM
	Fusion.DOO_Hold_Instances    hdi
  , Fusion.DOO_Headers_all       oh
  , Fusion.DOO_Lines_all         ol
  , Fusion.DOO_Fulfill_Lines_all fl
  , Fusion.DOO_HOLD_CODES_B      dhcb
  , Fusion.DOO_HOLD_CODES_tl     dhct
  , fusion.FND_LOOKUP_VALUES_B   flvb
  , fusion.FND_LOOKUP_VALUES_tl  flvt
WHERE
	oh.Source_Order_Number = :SOURCE_ORDER_NUMBER
	AND hdi.hold_code_id   = dhcb.hold_code_id
	AND oh.Header_ID       = ol.Header_id
	AND oh.Header_ID       = fl.Header_id
	AND fl.line_id         =ol.line_id
	AND
	(
		(
			TRANSACTION_ENTITY_NAME1   = 'DOO_ORDER_LINES_V'
			AND TRANSACTION_ENTITY_ID1 = ol.Line_id
		)
		OR
		(
			TRANSACTION_ENTITY_NAME1   = 'DOO_ORDER_HEADERS_V'
			AND TRANSACTION_ENTITY_ID1 = oh.Header_id
		)
		OR
		(
			TRANSACTION_ENTITY_NAME1   = 'DOO_ORDER_FLINES_V'
			AND TRANSACTION_ENTITY_ID1 = fl.Fulfill_Line_id
		)
	)
	AND dhct.hold_code_id                      = dhcb.hold_code_id
	AND dhct.language                          = USERENV('LANG')
	AND hdi.HOLD_RELEASE_REASON_CODE IS NOT NULL
	AND hdi.HOLD_RELEASE_REASON_CODE           =flvb.LOOKUP_CODE
	AND flvt.lookup_code                       = flvb.lookup_code
	AND flvt.LOOKUP_TYPE                       = flvb.LOOKUP_TYPE
	AND flvt.LOOKUP_TYPE                       = 'DOO_HLD_RELEASE_REASON'
	AND flvt.language                          = USERENV('LANG')
	AND oh.submitted_flag                      = 'Y'
	and active_flag                           <> 'Y'
ORDER BY
	oh.creation_Date DESC
;

-- Active holds on an order:
SELECT
	oh.Source_Order_Number
  , hdi.DOO_Header_ID
  , hdi.DOO_Line_id
  , hdi.Fulfill_Line_id
  , TRANSACTION_ENTITY_NAME1
  , hdi.TRANSACTION_ENTITY_ID1
  , dhcb.HOLD_CODE
  , dhct.HOLD_NAME
  , dhct.HOLD_DESCRIPTION
  , dhct.HOLD_CODE_ID
  , dhcb.EFFECTIVE_START_DATE
  , dhcb.EFFECTIVE_END_DATE
  , dhcb.GENERAL_HOLD_FLAG
  , dhcb.SYSTEM_FLAG
  , dhcb.APPLY_HOLD_ROLE_ELIGIBILITY
  , dhcB.RELEASE_HOLD_ROLE_ELIGIBILITY
  , hdi.pending_flag
  , hdi.deleted_flag
  , hdi.Hold_Instance_id
  , hdi.hold_code_id
  , hdi.APPLY_SYSTEM
  , hdi.apply_date
  , hdi.APPLY_USER_ID
  , hdi.RELEASE_USER_ID
  , hdi.RELEASE_DATE
  , hdi.HOLD_RUNNING_TASK_FLAG
  , hdi.HOLD_COMMENTS
  , hdi.HOLD_RELEASE_REASON_CODE
  , hdi.HOLD_RELEASE_COMMENTS
  , hdi.Active_Flag
  , hdi.TRANSACTION_ENTITY_ID1
  , hdi.HOLD_RELEASE_REASON_CODE
  , hdi.HOLD_RELEASE_COMMENTS
FROM
	Fusion.DOO_Hold_Instances    hdi
  , Fusion.DOO_Headers_all       oh
  , Fusion.DOO_Lines_all         ol
  , Fusion.DOO_Fulfill_Lines_all fl
  , Fusion.DOO_HOLD_CODES_B      dhcb
  , Fusion.DOO_HOLD_CODES_tl     dhct
WHERE
	oh.Source_Order_Number = :SOURCE_ORDER_NUMBER
	AND hdi.hold_code_id   = dhcb.hold_code_id
	AND oh.Header_ID       = ol.Header_id
	AND oh.Header_ID       = fl.Header_id
	AND fl.line_id         =ol.line_id
	AND
	(
		(
			TRANSACTION_ENTITY_NAME1   = 'DOO_ORDER_LINES_V'
			AND TRANSACTION_ENTITY_ID1 = ol.Line_id
		)
		OR
		(
			TRANSACTION_ENTITY_NAME1   = 'DOO_ORDER_HEADERS_V'
			AND TRANSACTION_ENTITY_ID1 = oh.Header_id
		)
		OR
		(
			TRANSACTION_ENTITY_NAME1   = 'DOO_ORDER_FLINES_V'
			AND TRANSACTION_ENTITY_ID1 = fl.Fulfill_Line_id
		)
	)
	AND dhct.hold_code_id = dhcb.hold_code_id
	AND dhct.language     = USERENV('LANG')
	AND oh.submitted_flag = 'Y'
	and active_flag       = 'Y'
ORDER BY
	oh.creation_Date DESC
;

-- Hold Codes
SELECT
	dhcb.HOLD_CODE
  , dhct.HOLD_NAME
  , dhct.HOLD_DESCRIPTION
  , dhct.HOLD_CODE_ID
  , dhcb.EFFECTIVE_START_DATE
  , dhcb.EFFECTIVE_END_DATE
  , dhcb.GENERAL_HOLD_FLAG
  , dhcb.SYSTEM_FLAG
  , dhcb.APPLY_HOLD_ROLE_ELIGIBILITY
  , dhcB.RELEASE_HOLD_ROLE_ELIGIBILITY
FROM
	Fusion.DOO_HOLD_CODES_B  dhcb
  , Fusion.DOO_HOLD_CODES_tl dhct
WHERE
	dhct.hold_code_id = dhcb.hold_code_id
	AND dhct.language = USERENV('LANG')
ORDER BY
	dhcb.HOLD_CODE
;

-- Release Reason Codes
SELECT
	flvb.LOOKUP_CODE
  , flvt.MEANING
  , flvb.ENABLED_FLAG
  , flvb.START_DATE_ACTIVE
  , flvb.END_DATE_ACTIVE
FROM
	fusion.FND_LOOKUP_VALUES_B  flvb
  , fusion.FND_LOOKUP_VALUES_tl flvt
WHERE
	flvt.lookup_code     = flvb.lookup_code
	AND flvt.LOOKUP_TYPE = flvb.LOOKUP_TYPE
	AND flvt.LOOKUP_TYPE = 'DOO_HLD_RELEASE_REASON'
	AND flvt.language    = USERENV('LANG')
ORDER BY
	flvb.LOOKUP_CODE
;