Untitled

/* Extract data PR dari approval code */
SELECT
    x.*,
    (CASE 
        WHEN dbms_obfuscation_toolkit.md5(input => UTL_RAW.cast_to_raw(
            ID_PR || '/' || 'dept'
        )) = '$pHash' then 'dept'
        WHEN dbms_obfuscation_toolkit.md5(input => UTL_RAW.cast_to_raw(
            ID_PR || '/' || 'div'
        )) = '$pHash' THEN 'div'
        WHEN dbms_obfuscation_toolkit.md5(input => UTL_RAW.cast_to_raw(
            ID_PR || '/' || 'dic'
        )) = '$pHash' THEN 'dic'
    END) V_APV
FROM PAKO_CUSTOM.XPAKO_UPLOAD_PR x
WHERE 
(
    dbms_obfuscation_toolkit.md5(input => UTL_RAW.cast_to_raw(
            ID_PR || '/' || 'dept'
        )) = '$pHash'
    OR

    dbms_obfuscation_toolkit.md5(input => UTL_RAW.cast_to_raw(
            ID_PR || '/' || 'div'
        )) = '$pHash'
    OR

    dbms_obfuscation_toolkit.md5(input => UTL_RAW.cast_to_raw(
            ID_PR || '/' || 'dic'
        )) = '$pHash'
)




/* HEADER */
SELECT
    poha.REQUISITION_HEADER_ID,
    poha.SEGMENT1,
    TO_CHAR(poha.CREATION_DATE) CREATION_DATE,
    poha.CREATED_BY,
    (SELECT USER_NAME FROM FND_USER WHERE USER_ID = poha.CREATED_BY ) PEMBUAT,
    poha.DESCRIPTION,
    poha.ORG_ID,
    poha.AUTHORIZATION_STATUS,
    -- (CASE WHEN xpr.id_pr IS NOT NULL THEN 'Y' ELSE 'N' END) IS_PR_CUSTOM
    (CASE WHEN (SELECT SUM(ATTRIBUTE2) FROM PO_REQUISITION_lines_ALL WHERE REQUISITION_HEADER_ID = poha.REQUISITION_HEADER_ID) > 0 
    OR (
        (SELECT COUNT(1) FROM PO_REQUISITION_lines_ALL WHERE REQUISITION_HEADER_ID = poha.REQUISITION_HEADER_ID AND ATTRIBUTE3 IS NOT NULL ) > 0
            OR
        (SELECT COUNT(1) FROM PO_REQUISITION_lines_ALL WHERE REQUISITION_HEADER_ID = poha.REQUISITION_HEADER_ID AND ATTRIBUTE4 IS NOT NULL ) > 0
    ) 
    THEN
        'Y'
    ELSE
        'N'
    END) IS_PR_CUSTOM
FROM po_requisition_headers_all poha
LEFT JOIN PAKO_CUSTOM.XPAKO_UPLOAD_PR xpr ON xpr.id_pr = poha.segment1 
WHERE poha.SEGMENT1 = '$no_pr'




/* LINES */
SELECT
    PRH.SEGMENT1 NO_REQ,
    (
        case when MSI.segment1='AA00000703' THEN 
                'STANDARD'
            when PRH.attribute2 is not null then
                case when (select count(ATTRIBUTE4) from PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID)=0 then
                    'INVESTASI'
                else 'CUSTOM' end
            else 'STANDARD' END
    ) jenis_pr,
    /*APPS.XPAKO_UPLOAD_PR.GET_QOH(Prl.DESTINATION_ORGANIZATION_ID,Prl.ITEM_ID,Prl.DESTINATION_SUBINVENTORY)*/ NULL qoh,
    PRH.AUTHORIZATION_STATUS,
    PRL.REQUISITION_HEADER_ID,
    PRH.REQUISITION_HEADER_ID,
    substr(PRL.CREATION_DATE,1,10) AS date_req,
    FU.USER_NAME,
    case
    when Prl.attribute4 is not null then
        (
            SELECT
        DISTINCT ab.DESCRIPTION 
        FROM FND_FLEX_VALUES_VL ab, FND_FLEX_VALUE_SETS ac
        WHERE ab.FLEX_VALUE_SET_ID = ac.FLEX_VALUE_SET_ID
        AND ab.ENABLED_FLAG = 'Y'
        AND ac.FLEX_VALUE_SET_NAME in ('INK_GL_DEPARTMENT','PKO_GL_DEPARTMENT')
        AND NVL(LENGTH(TRIM(TRANSLATE(ab.FLEX_VALUE, ' +-.0123456789', ' '))),0) = 0
        AND ab.FLEX_VALUE = Prl.attribute4
        AND ab.FLEX_VALUE NOT IN ('215','615')
        AND DECODE(substr(ac.FLEX_VALUE_SET_NAME,1,3),'INK',81,82) = PRH.org_id )
    when Prl.attribute4 is null then
        HREMP.full_NAME
    end req_from,
    -- HREMP.FULL_NAME REQ_FROM,
    PRH.APPROVED_DATE,
    PRL.ITEM_DESCRIPTION,
    PRH.DESCRIPTION,
    MSI.SEGMENT1 ITEMCODE,
    MSI.DESCRIPTION,
    GLC.SEGMENT4,
    PRL.UNIT_MEAS_LOOKUP_CODE UOM,
    PRL.CURRENCY_CODE,
    PRL.rate,
    PRL .item_id,
    (CASE WHEN PRL.RATE IS NULL THEN Prl.UNIT_PRICE ELSE (Prl.CURRENCY_UNIT_PRICE * PRL.RATE) END) UNIT_PRICE,
    Prl.CURRENCY_UNIT_PRICE,
    Prl.QUANTITY,
    HRL.LOCATION_CODE LOCATION,
    Prl.NEED_BY_DATE,
    Prl.NOTE_TO_AGENT,
    Prl.NOTE_TO_RECEIVER,
    HOU.NAME as Org_name,
    substr(Prl.DESTINATION_TYPE_CODE,1,3),
    MPAR.ORGANIZATION_CODE as destination_Org_name,
    Prl.SUGGESTED_VENDOR_NAME ||' '||Prl.SUGGESTED_VENDOR_LOCATION as vendor_name,
    Prl.attribute2 QTY_ONHAND,
    Prl.line_num,
    mc.DESCRIPTION MESIN
FROM PO_REQUISITION_LINES_ALL PRL,
PO_REQUISITION_HEADERS_ALL PRH,
FND_USER FU,
hr_employees HREMP,
mtl_system_items_b msi,
PO_REQ_DISTRIBUTIONS_ALL PRDIST,
GL_CODE_COMBINATIONS GLC,
hr_locations_all HRL,
MTL_PARAMETERS MPAR,
hr_operating_units HOU,
PAKO_CUSTOM.PKG_MASTER_ASSET_MESIN_MC mc
WHERE
PRH.SEGMENT1 = '$no_pr'
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND FU.USER_ID = PRL.CREATED_BY
AND HREMP.EMPLOYEE_ID=PRL.TO_PERSON_ID
AND MSI.INVENTORY_ITEM_ID=PRL.ITEM_ID AND MSI.ORGANIZATION_ID=PRL.DESTINATION_ORGANIZATION_ID
AND PRDIST.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRDIST.CODE_COMBINATION_ID = GLC.CODE_COMBINATION_ID
AND HRL.LOCATION_ID = PRL.DELIVER_TO_LOCATION_ID
and MPAR.ORGANIZATION_ID=Prl.DESTINATION_ORGANIZATION_ID
AND HOU.ORGANIZATION_ID=Prl.ORG_ID
and Prl.ATTRIBUTE1 = mc.NO_ASSET(+)