PR TO PO AND GRN REPORT QUERY Oracle EBS

CREATE OR REPLACE VIEW XX_RCV_TRANSACTIONS_V AS

/*

Created by m.Faisal

*/

SELECT PO_HEADER_ID ,PO_LINE_ID ,SHIPMENT_NUM,SHIPPED_DATE, SUM(RECEIVE)RECEIVE ,

SUM(DELIVER)DELIVER ,

SUM(RETURN_TO_VENDOR)RETURN_TO_VENDOR ,

SUM(ACCEPT)ACCEPT ,

SUM(REJECT)REJECT

FROM

(SELECT PO_HEADER_ID,

       PO_LINE_ID, rsh.SHIPMENT_NUM , rsh.SHIPPED_DATE,

       DECODE (transaction_type, 'RECEIVE', quantity, 0) RECEIVE,

       DECODE (transaction_type, 'DELIVER', quantity, 0) DELIVER,

       DECODE (transaction_type, 'RETURN TO VENDOR', quantity, 0)RETURN_TO_VENDOR,

       DECODE (transaction_type, 'ACCEPT', quantity, 0)ACCEPT,

       DECODE (transaction_type, 'REJECT', quantity, 0) REJECT

  FROM rcv_transactions rt , RCV_SHIPMENT_HEADERS_V rsh

  where rt.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID

  )

GROUP BY PO_HEADER_ID ,PO_LINE_ID ,SHIPMENT_NUM,SHIPPED_DATE;

/* Formatted on 1/29/2020 5:58:33 PM (QP5 v5.336) */

  SELECT PRHA.SEGMENT1                            REQUISTION_NO,

         POH.SEGMENT1                             PO_NO,

         MSI.CONCATENATED_SEGMENTS                ITEM_CODE,

         MSI.DESCRIPTION                          ITEM_DESCRIPTION,

         NVL (SUM (PLA.QUANTITY), 0)              PO_QUANTITY,

         rcv.SHIPMENT_NUM                         IGP_Number,

         rcv.SHIPPED_DATE                         IGP_Date,

         SUM (rcv.RECEIVE)                        IGP_Quantity,

         SUM (rcv.ACCEPT)                         Inspected_quantity,

         SUM (rcv.DELIVER)                        GRN_quantity,

         SUM (rcv.REJECT)                         B_Grade_quantity,

         SUM (rcv.RECEIVE) - SUM (rcv.ACCEPT)     Balance_to_Inspect,

         PLA.ATTRIBUTE_CATEGORY                   PO_TYPE,

         PLA.ATTRIBUTE1                           PO_ITEM_CATEGORY,

         PLA.ATTRIBUTE5                           PO_CATEGORY,

         PLA.ATTRIBUTE6                           PO_SUB_CATEGORY,

         POH.PO_HEADER_ID,

         PDA.PO_LINE_ID,

         MSI.INVENTORY_ITEM_ID

    FROM PO_HEADERS_ALL            POH,

         PO_LINES_ALL              PLA,

         PO_DISTRIBUTIONS_ALL      PDA,

         PO_REQ_DISTRIBUTIONS_ALL  PRDA,

         PO_REQUISITION_LINES_ALL  PRLA,

         PO_REQUISITION_HEADERS_ALL PRHA,

         APPS.MTL_SYSTEM_ITEMS_KFV MSI,

         xx_rcv_transactions_V    rcv

   WHERE     POH.PO_HEADER_ID = PDA.PO_HEADER_ID

         AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

         AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

         AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID

         AND PLA.PO_HEADER_ID = PDA.PO_HEADER_ID

         AND PLA.PO_HEADER_ID = POH.PO_HEADER_ID

         AND MSI.ORGANIZATION_ID = 87

         AND NVL (PLA.CANCEL_FLAG, 'N') <> 'Y'

         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID

         AND rcv.PO_HEADER_ID = POH.PO_HEADER_ID

         AND rcv.PO_LINE_ID = PLA.PO_LINE_ID

        -- AND PRHA.SEGMENT1 = 124                        --- requisition number

GROUP BY PRHA.SEGMENT1,

         POH.SEGMENT1,

         MSI.CONCATENATED_SEGMENTS,

         MSI.DESCRIPTION,

         rcv.SHIPMENT_NUM,

         rcv.SHIPPED_DATE,

         PLA.ATTRIBUTE_CATEGORY                  ,

         PLA.ATTRIBUTE1                          ,

         PLA.ATTRIBUTE5                          ,

         PLA.ATTRIBUTE6                          ,

         POH.PO_HEADER_ID,

         PDA.PO_LINE_ID,

         MSI.INVENTORY_ITEM_ID