Sales Order Hold Details

SELECT
    DHA.ORDER_NUMBER                                         AS Sales_Order_Number,
    DHCT.HOLD_NAME                                           AS Hold_Name,
    (
        SELECT
            PU.USERNAME
        FROM
            PER_USERS                PU
        WHERE
            PU.USER_GUID             = DHI.APPLY_USER_ID
    )                                                        AS Hold_Created_By,
    TO_CHAR(DHI.CREATION_DATE, 'DD-MON-YYYY')                AS Hold_Creation_Date,
    DHI.HOLD_COMMENTS                                        AS Hold_Comments,
    DLA.DISPLAY_LINE_NUMBER                                  AS Line_Number,
    (
        SELECT
            PU.USERNAME
        FROM
            PER_USERS                PU
        WHERE
            PU.USER_GUID             = DHI.RELEASE_USER_ID
    )                                                        AS Hold_Released_By,
    DHI.HOLD_RELEASE_REASON_CODE                             AS Hold_Release_Reason_Code,
    FLV.MEANING                                              AS Hold_Release_Reason,
    DHI.HOLD_RELEASE_COMMENTS                                AS Hold_Release_Comments,
    DFLA.LINE_ID                                             AS Line_Id,
    DHA.HEADER_ID                                            AS Header_Id,
    DECODE(DHI.ACTIVE_FLAG, 'Y', 'N', 'N', 'Y')              AS Hold_Released_Flag,
    (
        SELECT
            HP.PARTY_NAME
        FROM
            HZ_PARTIES               HP
        WHERE
            HP.PARTY_ID              = DHA.SOLD_TO_PARTY_ID
    )                                                        AS Customer_Name,
    (
        SELECT
            FLV1.MEANING
        FROM
            FND_LOOKUP_VALUES        FLV1
        WHERE
                FLV1.LOOKUP_TYPE     = 'ORA_DOO_ORDER_TYPES'
            AND FLV1.LOOKUP_CODE     = DHA.ORDER_TYPE_CODE
            AND FLV1.LANGUAGE        = USERENV('LANG')
    )                                                        AS Order_Type,
    TO_CHAR(DHA.ORDERED_DATE, 'DD-MON-YYYY')                 AS Order_Date,
    DHA.STATUS_CODE                                          AS Order_Status,
    DECODE(
        DHI.TRANSACTION_ENTITY_NAME1,
        'DOO_ORDER_HEADERS_V', 'ORDER',
        'DOO_ORDER_FLINES_V',  'LINE',
        DHI.TRANSACTION_ENTITY_NAME1
    )                                                        AS Hold_Level
FROM
    DOO_HEADERS_ALL              DHA,
    DOO_LINES_ALL                DLA,
    DOO_FULFILL_LINES_ALL        DFLA,
    DOO_HOLD_INSTANCES           DHI,
    DOO_HOLD_CODES_TL            DHCT,
    FND_LOOKUP_VALUES            FLV
WHERE
        DHA.HEADER_ID            = DFLA.HEADER_ID
    AND DHA.HEADER_ID            = DLA.HEADER_ID
    AND DLA.LINE_ID              = DFLA.LINE_ID
    AND (
            DHA.HEADER_ID        = DHI.TRANSACTION_ENTITY_ID1
         OR DFLA.FULFILL_LINE_ID = DHI.TRANSACTION_ENTITY_ID1
        )
    AND DHI.HOLD_CODE_ID         = DHCT.HOLD_CODE_ID
    AND FLV.LOOKUP_TYPE (+)      = 'DOO_HLD_RELEASE_REASON'
    AND FLV.LOOKUP_CODE (+)      = DHI.HOLD_RELEASE_REASON_CODE
    AND FLV.LANGUAGE (+)         = USERENV('LANG')
ORDER BY
    DHA.ORDER_NUMBER,
    DLA.DISPLAY_LINE_NUMBER