Intransit Query

SELECT
      ABC.SEGMENT1,
      ABC.FROM_ORG                              FROM_ORG,
      ABC.TO_ORG                            TO_ORG,
      BCD.INVOICE_NO                        INVOICE_NO,
      BCD.INVOICE_DATE                        INVOICE_DATE,
      BCD.ORDERED_DATE,
      ABC.ITEM_CODE                            ITEM_CODE,
      ABC.ITEM_NAME                            ITEM_NAME,
      ABC.ITEM_CAT                            ITEM_CAT,
      ABC.SEGMENT1                            REQ_NO,
      ABC.QTY_RECEIVED                        QTY,
      BCD.UNIT_PRICE                        UNIT_PRICE,
      ABC.QTY_RECEIVED*BCD.UNIT_PRICE        BASIC,
      BCD.TAX                                TAX,
      (ABC.QTY_RECEIVED*BCD.UNIT_PRICE)+BCD.TAX TOTAL,
      BCD.ORDER_NUMBER,
      BCD.LINE_ID,
      ABC.OU_NAME,
     abc.prod_cat,
     abc.org_id,
     abc.FROM_ORG_ID,
     abc.TO_ORG_ID,
     abc.item_id,
     abc.category_id,
     abc.need_by_date,
     abc.shipment_header_id,
       abc.shipment_line_id
FROM
      (SELECT
              PRHA.SEGMENT1                                                     SEGMENT1,
              RSL.ITEM_ID                                                    ITEM_ID,
              RSL.FROM_ORGANIZATION_ID                                        FROM_ORG_ID,
              RSL.TO_ORGANIZATION_ID                                        TO_ORG_ID,
              HLA1.LOCATION_CODE                                               FROM_ORG,
              HLA2.LOCATION_CODE                                               TO_ORG,
              RTRIM(LTRIM(msi.segment1||'-'||msi.segment2||'-'||msi.segment3||'-'||msi.segment4||'-'||msi.segment5||'-'||msi.segment6||'-'||msi.segment7||'-'||msi.segment8||'-'||msi.segment9)) ITEM_CODE,
              MSI.DESCRIPTION                                                ITEM_NAME,
              (SELECT X.DESCRIPTION
                 FROM MTL_ITEM_CATEGORIES Z,
                            MTL_CATEGORIES_tl X
               WHERE Z.CATEGORY_ID=X.CATEGORY_ID
                     AND Z.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
                     AND Z.ORGANIZATION_ID=(SELECT ORGANIZATION_ID
                                                                         FROM MTL_PARAMETERS Y
                                                                      WHERE Y.ORGANIZATION_CODE='001'))  ITEM_CAT,
              SUM(RSL.QUANTITY_SHIPPED - RSL.QUANTITY_RECEIVED)            QTY_RECEIVED,
              (SELECT name FROM hr_operating_units hou WHERE hou.organization_id = prla.org_id) ou_name,
              (SELECT ffv.description
                FROM FND_FLEX_VALUES_VL FFV
               WHERE flex_value_set_id =  1014898
                 AND flex_value = msi.SEGMENT1) prod_cat,
              prla.org_id,
              rsl.category_id ,
              prla.need_by_date,
              rsl.shipment_header_id,
              rsl.shipment_line_id
       FROM
               RCV_SHIPMENT_LINES RSL,
               MTL_SYSTEM_ITEMS_B MSI,
               HR_LOCATIONS_ALL HLA1,
               HR_LOCATIONS_ALL HLA2,
               PO_REQUISITION_LINES_ALL PRLA,
               PO_REQUISITION_HEADERS_ALL PRHA
       WHERE
                NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED')!= 'FULLY RECEIVED'
                AND RSL.SOURCE_DOCUMENT_CODE = 'REQ'
                AND RSL.ITEM_ID = MSI.INVENTORY_ITEM_ID AND
                RSL.FROM_ORGANIZATION_ID = MSI.ORGANIZATION_ID AND
                RSL.FROM_ORGANIZATION_ID = HLA1.INVENTORY_ORGANIZATION_ID AND
                RSL.TO_ORGANIZATION_ID = HLA2.INVENTORY_ORGANIZATION_ID AND
                RSL.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID AND
                PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
                AND RSL.SHIP_TO_LOCATION_ID = HLA2.LOCATION_ID
    AND HLA1.INACTIVE_DATE IS NULL      ----------------------Added by Raju(HCL) on 25-OCT-2013
                AND HLA2.INACTIVE_DATE IS NULL       ----------------------Added by Raju(HCL) on 25-OCT-2013
       and HLA1.ship_to_site_flag = 'Y'
       and HLA2.ship_to_site_flag = 'Y'
       and hla1.OBJECT_VERSION_NUMBER not like '3'
       GROUP BY
                RSL.ITEM_ID,
                PRHA.SEGMENT1,
                RSL.FROM_ORGANIZATION_ID,
                RSL.TO_ORGANIZATION_ID,
                HLA1.LOCATION_CODE,
                HLA2.LOCATION_CODE,
                msi.segment1||'-'||msi.segment2||'-'||msi.segment3||'-'||msi.segment4||'-'||msi.segment5||'-'||msi.segment6||'-'||msi.segment7||'-'||msi.segment8||'-'||msi.segment9,
                MSI.DESCRIPTION,
                rsl.category_id,
               MSI.INVENTORY_ITEM_ID,
                prla.org_id,
                msi.SEGMENT1,
                prla.need_by_date,
                rsl.shipment_header_id,
              rsl.shipment_line_id
       ORDER BY 2,3,4,5) ABC,
       (SELECT                   OOHA.ORIG_SYS_DOCUMENT_REF                                SEGMENT1,
                 OOHA.ORDER_NUMBER,
                 OOHA.ORDERED_DATE,
                 OOLA.LINE_ID,
                JISPL.INVENTORY_ITEM_ID                                  ITEM_ID,
                JISPL.VAT_INVOICE_NO                                     INVOICE_NO,
                TO_CHAR(JISPL.VAT_INVOICE_DATE,'DD-MON-RRRR')            INVOICE_DATE,
                JISPL.SELLING_PRICE                                      UNIT_PRICE,
                SUM(NVL(JISPL.TAX_AMOUNT, 0))                              TAX
        FROM
                OE_ORDER_HEADERS_ALL OOHA,
                OE_ORDER_LINES_ALL OOLA,
                JAI_OM_WSH_LINES_ALL  JISPL
        WHERE
                OOHA.HEADER_ID = JISPL.ORDER_HEADER_ID
          AND   OOHA.HEADER_ID = OOLA.HEADER_ID
          AND   OOLA.LINE_ID = JISPL.ORDER_LINE_ID and JISPL.SPLIT_FROM_DELIVERY_DETAIL_ID is null
        GROUP BY
                OOHA.ORIG_SYS_DOCUMENT_REF,
                OOHA.ORDER_NUMBER,
                OOHA.ORDERED_DATE,
                 OOLA.LINE_ID,
                JISPL.INVENTORY_ITEM_ID,
                JISPL.VAT_INVOICE_NO,
                JISPL.VAT_INVOICE_DATE,
                JISPL.SELLING_PRICE) BCD
WHERE
     ABC.SEGMENT1 = BCD.SEGMENT1 AND
     ABC.ITEM_ID = BCD.ITEM_ID AND
      --    BCD.INVOICE_NO IS NOT NULL AND
  --   BCD.INVOICE_DATE <= :P_ST_DATE AND
     BCD.ORDERED_DATE  <= :P_ST_DATE AND
     abc.org_id        = nvl(:p_org_id,abc.org_id) AND
     abc.FROM_ORG_ID   = nvl(:P_FROM_ORG_ID,abc.FROM_ORG_ID) AND
     abc.TO_ORG_ID     = nvl(:P_TO_ORG_ID,abc.TO_ORG_ID) AND
     abc.item_id       = nvl(:P_item_id,abc.item_id) AND
     abc.category_id   = nvl(:P_category_id,abc.category_id)
--      and BCD.INVOICE_NO NOT IN ( SELECT attribute1 FROM Intransit_Temp)
ORDER BY 2,3,4;