Query for Credit Hold Report Oracle Erp Cloud

 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