1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pr ,rfq and po queries

Discussion in 'Oracle SCM & Manufacturing' started by 7elmy, Apr 9, 2019.

  1. 7elmy

    7elmy Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    Egypt
    ------------PR &FRQ & PO----------------------

    SELECT PRL.REQUISITION_HEADER_ID Header_id,
    prl.REQUISITION_LINE_ID,
    PRL.ITEM_ID MSR_ITEM_ID,
    ABCD_LOGISTICS_PKG.GET_ITEM_CODE (prl.ITEM_ID) REQ_ITEM_CODE,
    PRL.ITEM_DESCRIPTION MSR_ITEM_DESC,
    PRL.QUANTITY,
    PRL.LINE_NUM,
    PRL.UNIT_MEAS_LOOKUP_CODE MSR_UOM,
    PRL.UNIT_PRICE,
    XX_ABCD_HCM_PKG.GET_EMP_NAME (PRL.TO_PERSON_ID, SYSDATE) REQUESTOR,
    PRH.SEGMENT1 REQ_NO,
    pRH.AUTHORIZATION_STATUS REQ_STATUS,
    ABCD_PROJECT_PKG.GET_TASK_NAME (prd.TASK_ID) msr_TASK,
    ABCD_PROJECT_PKG.GET_PROJECT_NAME (prd.project_id) msr_proj,
    pha.SEGMENT1 PO_NUMBER,
    RFQ.SEGMENT1 RFQ_NUM,
    QUOT.SEGMENT1 quot,
    pha.PO_HEADER_ID,
    pha.AUTHORIZATION_STATUS PO_STATUS,
    TO_CHAR (pha.APPROVED_DATE, 'DD/MM/YYYY') PO_APPROVED_DATE,
    PLA.UNIT_MEAS_LOOKUP_CODE PO_UOM,
    pla.PO_LINE_ID,
    pla.LINE_NUM LINE_NUMBER,
    pla.ITEM_ID PO_ITEM_ID,
    ABCD_LOGISTICS_PKG.GET_ITEM_CODE (pla.ITEM_ID) PO_ITEM_CODE,
    Pla.ITEM_DESCRIPTION PO_ITEM_DESC,
    pda.PROJECT_ID po_PROJECT_ID,
    ABCD_PROJECT_PKG.GET_PROJECT_NAME (pda.PROJECT_ID) PO_PROJECT,
    -- ABCD_PROJECT_PKG.GET_PROJECT_NAME_BYPO (pha.PO_HEADER_ID) PO_PROJECT2,
    ABCD_PROJECT_PKG.GET_TASK_NAME (pda.TASK_ID) PO_TASK,
    plla.QUANTITY_RECEIVED,
    plla.QUANTITY_BILLED,
    CASE
    WHEN pha.APPROVED_DATE IS NOT NULL
    AND pha.AUTHORIZATION_STATUS = 'INPROCESS'
    THEN
    (SELECT xx_values_tmp.XX_GET_EMP_AR_NAME (EMPLOYEE_ID)
    FROM PO_ACTION_HISTORY pah
    WHERE OBJECT_TYPE_CODE = 'PO'
    AND OBJECT_ID = pha.PO_HEADER_ID
    AND SEQUENCE_NUM IN
    (SELECT MAX (SEQUENCE_NUM)
    FROM PO_ACTION_HISTORY hist
    WHERE OBJECT_TYPE_CODE = 'PO'
    AND hist.OBJECT_ID = pah.OBJECT_ID))
    WHEN pha.AUTHORIZATION_STATUS IN ('REJECTED', 'INCOMPLETE')
    THEN
    xx_values_tmp.XX_GET_EMP_AR_NAME (pha.AGENT_ID) --AGENT_ID
    ELSE
    NULL
    END
    PO_approval_Pending,
    CASE
    WHEN pha.APPROVED_DATE IS NULL
    AND pha.AUTHORIZATION_STATUS <> 'APPROVED'
    THEN
    TRUNC (SYSDATE - prh.APPROVED_DATE)
    END
    PO_Approve_Time_Lag,
    'MSR & RFQ & PO' remarks
    FROM PO_REQUISITION_HEADERS_ALL PRH,
    PO_REQUISITION_LINES_ALL PRL,
    po_req_distributions_all PRD,
    po_headers_all pha,
    PO_LINES_ALL PLA,
    PO_LINE_LOCATIONS_ALL plla,
    po_distributions_all pda,
    PO_HEADERS_ALL RFQ,
    PO_LINES_ALL RFQL,
    PO_HEADERS_ALL QUOT
    WHERE prh.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
    AND prd.requisition_line_id = prl.requisition_line_id
    AND pha.po_header_id = pda.po_header_id
    AND pha.PO_HEADER_ID = pla.PO_HEADER_ID
    AND pda.PO_LINE_ID = pla.PO_LINE_ID
    AND plla.PO_LINE_ID = pla.PO_LINE_ID
    AND plla.Line_Location_Id = pda.line_location_id
    AND pda.req_distribution_id = prd.distribution_id
    AND RFQ.PO_HEADER_ID = QUOT.FROM_HEADER_ID
    AND QUOT.FROM_HEADER_ID = RFQL.PO_HEADER_ID
    AND quot.type_lookup_code = 'QUOTATION'
    AND RFQL.PO_HEADER_ID = RFQ.PO_HEADER_ID
    AND PRl.ITEM_ID = RFQL.ITEM_ID
    AND PRl.ON_RFQ_FLAG = 'Y'
    -- AND prh.segment1 = :p_REQ_NO
    AND prh.creation_date BETWEEN :p_from_date AND :p_to_date
    AND (prl.ORG_ID = :p_ORG_id OR :p_ORG_id IS NULL)
    --------------------------PR & RFQ without PO----------------

    SELECT PRL.REQUISITION_HEADER_ID Header_id,
    prl.REQUISITION_LINE_ID,
    PRL.ITEM_ID MSR_ITEM_ID,
    ABCD_LOGISTICS_PKG.GET_ITEM_CODE (prl.ITEM_ID) REQ_ITEM_CODE,
    PRL.ITEM_DESCRIPTION MSR_ITEM_DESC,
    PRL.QUANTITY,
    PRL.LINE_NUM,
    PRL.UNIT_MEAS_LOOKUP_CODE MSR_UOM,
    PRL.UNIT_PRICE,
    XX_ABCD_HCM_PKG.GET_EMP_NAME (PRL.TO_PERSON_ID, SYSDATE) REQUESTOR,
    PRH.SEGMENT1 REQ_NO,
    pRH.AUTHORIZATION_STATUS REQ_STATUS,
    ABCD_PROJECT_PKG.GET_TASK_NAME (prd.TASK_ID) msr_TASK,
    ABCD_PROJECT_PKG.GET_PROJECT_NAME (prd.project_id) msr_proj,
    '' PO_NUMBER,
    RFQ.SEGMENT1 RFQ_NUM,
    QUOT.SEGMENT1 quot,
    0 PO_HEADER_ID,
    '' PO_STATUS,
    '' PO_APPROVED_DATE,
    '' PO_UOM,
    0 PO_LINE_ID,
    0 LINE_NUMBER,
    0 PO_ITEM_ID,
    '' PO_ITEM_CODE,
    '' PO_ITEM_DESC,
    0 po_PROJECT_ID,
    '' PO_PROJECT,
    -- '' PO_PROJECT2,
    '' PO_TASK,
    0 QUANTITY_RECEIVED,
    0 QUANTITY_BILLED,
    '' PO_approval_Pending,
    0 PO_Approve_Time_Lag,
    'MSR & RFQ without PO' remarks
    FROM PO_REQUISITION_HEADERS_ALL PRH,
    PO_REQUISITION_LINES_ALL PRL,
    po_req_distributions_all PRD,
    PO_HEADERS_ALL RFQ,
    PO_LINES_ALL RFQL,
    PO_HEADERS_ALL QUOT
    WHERE prh.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
    AND prd.requisition_line_id = prl.requisition_line_id
    AND RFQ.PO_HEADER_ID = QUOT.FROM_HEADER_ID
    AND QUOT.FROM_HEADER_ID = RFQL.PO_HEADER_ID
    AND quot.type_lookup_code = 'QUOTATION'
    AND RFQL.PO_HEADER_ID = RFQ.PO_HEADER_ID
    AND PRl.ITEM_ID = RFQL.ITEM_ID
    -- AND prh.segment1 = :p_REQ_NO
    -- AND PRl.ON_RFQ_FLAG ='Y'
    AND (SELECT DISTINCT pda.req_distribution_id
    FROM po_distributions_all pda
    WHERE pda.req_distribution_id = prd.distribution_id)
    IS NULL
    AND prh.creation_date BETWEEN :p_from_date AND :p_to_date
    AND (prl.ORG_ID = :p_ORG_id OR :p_ORG_id IS NULL)
    ----------------PR Only---------------
    SELECT PRL.REQUISITION_HEADER_ID Header_id,
    prl.REQUISITION_LINE_ID,
    PRL.ITEM_ID MSR_ITEM_ID,
    ABCD_LOGISTICS_PKG.GET_ITEM_CODE (prl.ITEM_ID) REQ_ITEM_CODE,
    PRL.ITEM_DESCRIPTION MSR_ITEM_DESC,
    PRL.QUANTITY,
    PRL.LINE_NUM,
    PRL.UNIT_MEAS_LOOKUP_CODE MSR_UOM,
    PRL.UNIT_PRICE,
    XX_ABCD_HCM_PKG.GET_EMP_NAME (PRL.TO_PERSON_ID, SYSDATE) REQUESTOR,
    PRH.SEGMENT1 REQ_NO,
    pRH.AUTHORIZATION_STATUS REQ_STATUS,
    ABCD_PROJECT_PKG.GET_TASK_NAME (prd.TASK_ID) msr_TASK,
    ABCD_PROJECT_PKG.GET_PROJECT_NAME (prd.project_id) msr_proj,
    '' PO_NUMBER,
    '' RFQ_NUM,
    '' quot,
    0 PO_HEADER_ID,
    '' PO_STATUS,
    '' PO_APPROVED_DATE,
    '' PO_UOM,
    0 PO_LINE_ID,
    0 LINE_NUMBER,
    0 PO_ITEM_ID,
    '' PO_ITEM_CODE,
    '' PO_ITEM_DESC,
    0 po_PROJECT_ID,
    '' PO_PROJECT,
    -- '' PO_PROJECT2,
    '' PO_TASK,
    0 QUANTITY_RECEIVED,
    0 QUANTITY_BILLED,
    '' PO_approval_Pending,
    0 PO_Approve_Time_Lag,
    'MSR Only' remarks
    FROM PO_REQUISITION_HEADERS_ALL PRH,
    PO_REQUISITION_LINES_ALL PRL,
    po_req_distributions_all PRD
    -- PO_HEADERS_ALL RFQ,
    -- PO_LINES_ALL RFQL,
    -- PO_HEADERS_ALL QUOT
    WHERE prh.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
    AND prd.requisition_line_id = prl.requisition_line_id
    -- AND prh.segment1 = :p_REQ_NO
    -- AND PRl.ON_RFQ_FLAG <>'Y'
    AND (SELECT DISTINCT pda.req_distribution_id
    FROM po_distributions_all pda
    WHERE pda.req_distribution_id = prd.distribution_id)
    IS NULL
    AND prh.creation_date BETWEEN :p_from_date AND :p_to_date
    AND (prl.ORG_ID = :p_ORG_id OR :p_ORG_id IS NULL)
    --------------PR & PO without RFQ----------------------
    SELECT PRL.REQUISITION_HEADER_ID Header_id,
    prl.REQUISITION_LINE_ID,
    PRL.ITEM_ID MSR_ITEM_ID,
    ABCD_LOGISTICS_PKG.GET_ITEM_CODE (prl.ITEM_ID) REQ_ITEM_CODE,
    PRL.ITEM_DESCRIPTION MSR_ITEM_DESC,
    PRL.QUANTITY,
    PRL.LINE_NUM,
    PRL.UNIT_MEAS_LOOKUP_CODE MSR_UOM,
    PRL.UNIT_PRICE,
    XX_ABCD_HCM_PKG.GET_EMP_NAME (PRL.TO_PERSON_ID, SYSDATE) REQUESTOR,
    PRH.SEGMENT1 REQ_NO,
    pRH.AUTHORIZATION_STATUS REQ_STATUS,
    ABCD_PROJECT_PKG.GET_TASK_NAME (prd.TASK_ID) msr_TASK,
    ABCD_PROJECT_PKG.GET_PROJECT_NAME (prd.project_id) msr_proj,
    pha.SEGMENT1 PO_NUMBER,
    '' RFQ_NUM,
    '' quot,
    pha.PO_HEADER_ID,
    pha.AUTHORIZATION_STATUS PO_STATUS,
    TO_CHAR (pha.APPROVED_DATE, 'DD/MM/YYYY') PO_APPROVED_DATE,
    PLA.UNIT_MEAS_LOOKUP_CODE PO_UOM,
    pla.PO_LINE_ID,
    pla.LINE_NUM LINE_NUMBER,
    pla.ITEM_ID PO_ITEM_ID,
    ABCD_LOGISTICS_PKG.GET_ITEM_CODE (pla.ITEM_ID) PO_ITEM_CODE,
    Pla.ITEM_DESCRIPTION PO_ITEM_DESC,
    pda.PROJECT_ID po_PROJECT_ID,
    ABCD_PROJECT_PKG.GET_PROJECT_NAME (pda.PROJECT_ID) PO_PROJECT,
    -- ABCD_PROJECT_PKG.GET_PROJECT_NAME_BYPO (pha.PO_HEADER_ID) PO_PROJECT2,
    ABCD_PROJECT_PKG.GET_TASK_NAME (pda.TASK_ID) PO_TASK,
    plla.QUANTITY_RECEIVED,
    plla.QUANTITY_BILLED,
    CASE
    WHEN pha.APPROVED_DATE IS NOT NULL
    AND pha.AUTHORIZATION_STATUS = 'INPROCESS'
    THEN
    (SELECT xx_values_tmp.XX_GET_EMP_AR_NAME (EMPLOYEE_ID)
    FROM PO_ACTION_HISTORY pah
    WHERE OBJECT_TYPE_CODE = 'PO'
    AND OBJECT_ID = pha.PO_HEADER_ID
    AND SEQUENCE_NUM IN
    (SELECT MAX (SEQUENCE_NUM)
    FROM PO_ACTION_HISTORY hist
    WHERE OBJECT_TYPE_CODE = 'PO'
    AND hist.OBJECT_ID = pah.OBJECT_ID))
    WHEN pha.AUTHORIZATION_STATUS IN ('REJECTED', 'INCOMPLETE')
    THEN
    xx_values_tmp.XX_GET_EMP_AR_NAME (pha.AGENT_ID) --AGENT_ID
    ELSE
    NULL
    END
    PO_approval_Pending,
    CASE
    WHEN pha.APPROVED_DATE IS NULL
    AND pha.AUTHORIZATION_STATUS <> 'APPROVED'
    THEN
    (SYSDATE - prh.APPROVED_DATE)
    END
    PO_Approve_Time_Lag,
    'MSR & PO without RFQ' remarks
    FROM PO_REQUISITION_HEADERS_ALL PRH,
    PO_REQUISITION_LINES_ALL PRL,
    po_req_distributions_all PRD,
    po_headers_all pha,
    PO_LINES_ALL PLA,
    PO_LINE_LOCATIONS_ALL plla,
    po_distributions_all pda
    -- PO_HEADERS_ALL RFQ,
    -- PO_LINES_ALL RFQL,
    -- PO_HEADERS_ALL QUOT
    WHERE prh.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
    AND prd.requisition_line_id = prl.requisition_line_id
    AND pha.po_header_id = pda.po_header_id
    AND pha.PO_HEADER_ID = pla.PO_HEADER_ID
    AND pda.PO_LINE_ID = pla.PO_LINE_ID
    AND plla.PO_LINE_ID = pla.PO_LINE_ID
    AND plla.Line_Location_Id = pda.line_location_id
    AND pda.req_distribution_id = prd.distribution_id
    -- AND RFQ.PO_HEADER_ID = QUOT.FROM_HEADER_ID
    -- AND QUOT.FROM_HEADER_ID = RFQL.PO_HEADER_ID
    -- AND quot.type_lookup_code='QUOTATION'
    -- AND RFQL.PO_HEADER_ID = RFQ.PO_HEADER_ID
    -- AND PRl.ITEM_ID = RFQL.ITEM_ID
    AND PRl.ON_RFQ_FLAG <> 'Y'
    -- AND prh.segment1 = :p_REQ_NO
    AND prh.creation_date BETWEEN :p_from_date AND :p_to_date
    AND (prl.ORG_ID = :p_ORG_id OR :p_ORG_id IS NULL)

    -----------------PO only -------------------------------

    SELECT 0 Header_id,
    0 REQUISITION_LINE_ID,
    0 MSR_ITEM_ID,
    '' REQ_ITEM_CODE,
    '' MSR_ITEM_DESC,
    0 QUANTITY,
    0 LINE_NUM,
    '' MSR_UOM,
    0 UNIT_PRICE,
    '' REQUESTOR,
    '' REQ_NO,
    '' REQ_STATUS,
    '' msr_TASK,
    '' msr_proj,
    pha.SEGMENT1 PO_NUMBER,
    '' RFQ_NUM,
    '' quot,
    pha.PO_HEADER_ID,
    pha.AUTHORIZATION_STATUS PO_STATUS,
    TO_CHAR (pha.APPROVED_DATE, 'DD/MM/YYYY') PO_APPROVED_DATE,
    PLA.UNIT_MEAS_LOOKUP_CODE PO_UOM,
    pla.PO_LINE_ID,
    pla.LINE_NUM LINE_NUMBER,
    pla.ITEM_ID PO_ITEM_ID,
    ABCD_LOGISTICS_PKG.GET_ITEM_CODE (pla.ITEM_ID) PO_ITEM_CODE,
    Pla.ITEM_DESCRIPTION PO_ITEM_DESC,
    pda.PROJECT_ID po_PROJECT_ID,
    ABCD_PROJECT_PKG.GET_PROJECT_NAME (pda.PROJECT_ID) PO_PROJECT,
    ABCD_PROJECT_PKG.GET_TASK_NAME (pda.TASK_ID) PO_TASK,
    plla.QUANTITY_RECEIVED,
    plla.QUANTITY_BILLED,
    CASE
    WHEN pha.APPROVED_DATE IS NOT NULL
    AND pha.AUTHORIZATION_STATUS = 'INPROCESS'
    THEN
    (SELECT xx_values_tmp.XX_GET_EMP_AR_NAME (EMPLOYEE_ID)
    FROM PO_ACTION_HISTORY pah
    WHERE OBJECT_TYPE_CODE = 'PO'
    AND OBJECT_ID = pha.PO_HEADER_ID
    AND SEQUENCE_NUM IN
    (SELECT MAX (SEQUENCE_NUM)
    FROM PO_ACTION_HISTORY hist
    WHERE OBJECT_TYPE_CODE = 'PO'
    AND hist.OBJECT_ID = pah.OBJECT_ID))
    WHEN pha.AUTHORIZATION_STATUS IN ('REJECTED', 'INCOMPLETE')
    THEN
    xx_values_tmp.XX_GET_EMP_AR_NAME (pha.AGENT_ID) --AGENT_ID
    ELSE
    NULL
    END
    PO_approval_Pending,
    CASE
    WHEN pha.APPROVED_DATE IS NULL
    AND pha.AUTHORIZATION_STATUS <> 'APPROVED'
    THEN
    TRUNC (SYSDATE - pha.Creation_DATE)
    END
    PO_Approve_Time_Lag,
    'PO only' remarks
    FROM po_headers_all pha,
    PO_LINES_ALL PLA,
    PO_LINE_LOCATIONS_ALL plla,
    po_distributions_all pda
    WHERE pha.po_header_id = pda.po_header_id
    AND pha.PO_HEADER_ID = pla.PO_HEADER_ID
    AND pda.PO_LINE_ID = pla.PO_LINE_ID
    AND plla.PO_LINE_ID = pla.PO_LINE_ID
    AND plla.Line_Location_Id = pda.line_location_id
    AND (SELECT DISTINCT prd.distribution_id
    FROM po_req_distributions_all prd
    WHERE prd.distribution_id = pda.req_distribution_id)
    IS NULL
    AND pha.creation_date BETWEEN :p_from_date AND :p_to_date
    -- and pha.SEGMENT1='15175/P1150/A/15'
    AND (pla.ORG_ID = :p_ORG_id OR :p_ORG_id IS NULL)[/code]
     

    Attached Files: