Untitled
unknown
sql
2 months ago
5.0 kB
3
Indexable
/* 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(+)
Editor is loading...
Leave a Comment