SELECT DISTINCT DOFL.SOURCE_ORDER_NUMBER
,DOFL.SOURCE_LINE_NUMBER
, DHCT.HOLD_DESCRIPTION
,DHCB.hold_code
,DHCT.HOLD_NAME
,DHA.ORDER_NUMBER
,EGT.DESCRIPTION ITEM_DESC
,EGB.ITEM_NUMBER ITEM
,HP.PARTY_NAME
,HCA.ATTRIBUTE1
,DHI.APPLY_DATE
,NVL(PPNF.FULL_NAME,PU.USERNAME) HOLD_CREATED_BY
, hou.name OU
, hca.account_number
,DHI.HOLD_COMMENTS
, (SELECT HCP.CREDIT_HOLD
FROM HZ_CUSTOMER_PROFILES_F HCP
WHERE HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND ROWNUM = 1
) ACC_CREDIT_HOLD
from DOO_HOLD_CODES_TL DHCT
, DOO_HOLD_CODES_B DHCB
, DOO_HOLD_INSTANCES DHI
, DOO_ORDER_FLINES_V DOFL
, DOO_HEADERS_ALL DHA
, egp_system_items_b egb
, egp_system_items_TL egt
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, PER_USERS PU
, PER_PERSON_NAMES_F PPNF
, HR_OPERATING_UNITS hou
WHERE 1=1
AND DHCT.LANGUAGE = USERENV('LANG')
AND DHCB.HOLD_CODE_ID = DHCT.HOLD_CODE_ID
AND DHCB.HOLD_CODE_ID = DHI.HOLD_CODE_ID
AND DHI.TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_FLINES_V'
AND DHI.TRANSACTION_ENTITY_ID1 = DOFL.FULFILL_LINE_ID
AND DOFL.HEADER_ID = DHA.HEADER_ID
AND DOFL.INVENTORY_ITEM_ID = EGB.INVENTORY_ITEM_ID
AND DOFL.INVENTORY_ORGANIZATION_ID = EGB.ORGANIZATION_ID
AND EGB.INVENTORY_ITEM_ID = EGT.INVENTORY_ITEM_ID
AND EGB.ORGANIZATION_ID = EGT.ORGANIZATION_ID
AND DHA.SOLD_TO_PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND DHI.RELEASE_DATE IS NULL
AND HCA.ACCOUNT_TERMINATION_DATE >= TRUNC(SYSDATE)
AND DHI.CREATED_BY = PU.USERNAME
AND PU.PERSON_ID = PPNF.PERSON_ID(+)
AND PPNF.NAME_TYPE (+) ='GLOBAL'
AND dha.org_id = hou.organization_id
AND hou.name = :P_Business_Unit
AND (hou.name IN (:p_bu) OR COALESCE (:p_bu,NULL) IS NULL)
UNION ALL
SELECT DISTINCT DHA.SOURCE_ORDER_NUMBER
,DLA.SOURCE_LINE_NUMBER
, DHCT.HOLD_DESCRIPTION
,DHCB.hold_code
,DHCT.HOLD_NAME
,DHA.ORDER_NUMBER
,EGT.DESCRIPTION ITEM_DESC
,EGB.ITEM_NUMBER ITEM
,HP.PARTY_NAME
,HCA.ATTRIBUTE1
,DHI.APPLY_DATE
,NVL(PPNF.FULL_NAME,PU.USERNAME) HOLD_CREATED_BY
, hou.name OU
, hca.account_number
,DHI.HOLD_COMMENTS
, (SELECT HCP.CREDIT_HOLD
FROM HZ_CUSTOMER_PROFILES_F HCP
WHERE HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND ROWNUM = 1
) ACC_CREDIT_HOLD
from DOO_HOLD_CODES_TL DHCT
, DOO_HOLD_CODES_B DHCB
, DOO_HOLD_INSTANCES DHI
, DOO_HEADERS_ALL DHA
, DOO_LINES_ALL DLA
, egp_system_items_b egb
, egp_system_items_TL egt
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, PER_USERS PU
, PER_PERSON_NAMES_F PPNF
, HR_OPERATING_UNITS hou
WHERE 1=1
AND DHCT.LANGUAGE = USERENV('LANG')
AND DHCB.HOLD_CODE_ID = DHCT.HOLD_CODE_ID
AND DHCB.HOLD_CODE_ID = DHI.HOLD_CODE_ID
AND DHI.TRANSACTION_ENTITY_NAME1 = 'DOO_ORDER_HEADERS_V'
AND DHI.TRANSACTION_ENTITY_ID1 = DHA.HEADER_ID
AND DHA.HEADER_ID = DLA.HEADER_ID
AND DLA.INVENTORY_ITEM_ID = EGB.INVENTORY_ITEM_ID
AND DLA.INVENTORY_ORGANIZATION_ID = EGB.ORGANIZATION_ID
AND EGB.INVENTORY_ITEM_ID = EGT.INVENTORY_ITEM_ID
AND EGB.ORGANIZATION_ID = EGT.ORGANIZATION_ID
AND DHA.SOLD_TO_PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND DHI.RELEASE_DATE IS NULL
AND DHI.CREATED_BY = PU.USERNAME
AND PU.PERSON_ID = PPNF.PERSON_ID(+)
AND PPNF.NAME_TYPE (+) ='GLOBAL'
AND dha.org_id = hou.organization_id
AND HCA.ACCOUNT_TERMINATION_DATE >= TRUNC(SYSDATE)
AND (hou.name IN (:p_bu) OR COALESCE (:p_bu,NULL) IS NULL)
ORDER BY 1,6
