OPM WIP Value - V2

SELECT                            
         (CASE
             WHEN     GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE
                  AND TRUNC (GBH.BATCH_CLOSE_DATE) < oap.schedule_close_date + 1
             THEN
                'Variance'
             WHEN     gbh.BATCH_CLOSE_DATE IS NULL          -- the job is OPEN
                  AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date
             THEN
                'Valuation'
             WHEN     GBH.BATCH_CLOSE_DATE IS NOT NULL -- the job is closed and ...the job was closed after the accounting period
                  AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE
             THEN
                'Valuation'
          END)
            "Report Type",
         -- End of revision 1.5 from Doug Volz  13-Jan-2015
         GL.NAME "Ledger",
         HAOU2.NAME "Operating Unit",
         MP.ORGANIZATION_CODE "Org Code",
         OAP.PERIOD_NAME "Period Name",
         GCC1.SEGMENT1 "Co",
         GCC2.SEGMENT2 "Cost Ctr",
         (SELECT MEANING
            FROM apps.MFG_LOOKUPS
           WHERE     LOOKUP_TYPE = 'CELG_CST_GLINV_RECON_ACCOUNTS'
                 AND description = 'WIP')
            "Acct",
         GCC2.SEGMENT4 "Sub-Acct",
         GCC1.SEGMENT5 "Prod Grp",
         GCC2.SEGMENT6 "Loc",
         GCC2.SEGMENT7 "InterCo",
         GCC2.SEGMENT8 "Future1",
         GCC2.SEGMENT9 "Future2",
         NULL "WIP Class",
         NULL "Class Type",
         GBH.BATCH_NO "Batch or WIP Job",
         FLV1.MEANING "Batch or Job Status",
         TO_CHAR (GBH.ACTUAL_CMPLT_DATE, 'DD-MON-YYYY') "Date Completed",
         TO_CHAR (GBH.BATCH_CLOSE_DATE, 'DD-MON-YYYY') "Date Closed",
         TO_CHAR (GBH.LAST_UPDATE_DATE, 'DD-MON-YYYY') "Last Updated",
         NVL (ITEM.STQTY, 0) "Start Quantity",
         NVL (ITEM.CQTY, 0) "FG Quantity Completed",
         NVL (ITEM.SCRAPPED, 0) "FG Quantity Scrapped",
         NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0) "Total FG Quantity",
         ITEM.FG_ITEM "FG Item Number",
         ITEM.FG_DESC "FG Description",
         ITEM.COMP_ITEM "Component or Ingredient",
         ITEM.COMP_DESC "Component or Ingredient Desc",
         GL.CURRENCY_CODE "Curr Code",
         -- Revision for version 1.2, get OPM costs from transactions
         /*round((SELECT NVL (SUM (CCD.CMPNT_COST), 0)
            FROM APPS.CM_CMPT_DTL CCD
           WHERE MSI.ORGANIZATION_ID = CCD.ORGANIZATION_ID
                 AND MSI.INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID
                 AND GPS.PERIOD_ID = CCD.PERIOD_ID
                 AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID),5)
            "Gross Item Cost",*/
         --Commented by Apps on 09Dec14
         (CASE
             WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0
             THEN
                (ROUND (
                    (SELECT NVL (SUM (CCD.CMPNT_COST), 0)
                       FROM APPS.CM_CMPT_DTL CCD
                      WHERE ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID
                            AND ITEM.COMP_INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID
                            AND GPS.PERIOD_ID = CCD.PERIOD_ID
                            AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID),
                    5))
             ELSE
                ROUND (GXEH_SUB.ITEM_COST, 5)
          END)
            "Gross Item Cost",                      --Added by Apps on 09Dec14
         0 "ICP Item Cost",
         item.uom "UOM Code",
         -- Revision for version 1.3
         NVL (ITEM.REQTY / ITEM.STQTY, 0) "Quantity Per FG", --Modified by Apps on 22Dec14
         ITEM.REQTY "Total Req Quantity",
         ITEM.QTYISS "Quantity Issued",
         -- Revision for version 1.4
         --NVL (ITEM.REQTY, 0) - NVL (ITEM.QTYISS, 0) "Est. Quantity Left in WIP", --Commented by Apps on 1/7/15
         (  NVL (ITEM.QTYISS, 0)
          - (  (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0))
             * NVL (ITEM.REQTY / ITEM.STQTY, 0)))
            "Est. Quantity Left in WIP",
         ROUND (
              (  NVL (ITEM.QTYISS, 0)
               - (  (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0))
                  * NVL (ITEM.REQTY / ITEM.STQTY, 0)))
            * (CASE
                  WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0
                  THEN
                     (ROUND (
                         (SELECT NVL (SUM (CCD.CMPNT_COST), 0)
                            FROM APPS.CM_CMPT_DTL CCD
                           WHERE  ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID
                                 AND ITEM.COMP_INVENTORY_ITEM_ID =
                                        CCD.INVENTORY_ITEM_ID
                                 AND GPS.PERIOD_ID = CCD.PERIOD_ID
                                 AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID),
                         5))
                  ELSE
                     ROUND (GXEH_SUB.ITEM_COST, 5)
               END),
            2)
            "Gross Matl Usage Var.",                 --Modified by Apps 1/7/14
         0 "ICP in WIP Variances",
         ROUND (
              (  NVL (ITEM.QTYISS, 0)
               - (  (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0))
                  * NVL (ITEM.REQTY / ITEM.STQTY, 0)))
            * (CASE
                  WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0
                  THEN
                     (ROUND (
                         (SELECT NVL (SUM (CCD.CMPNT_COST), 0)
                            FROM APPS.CM_CMPT_DTL CCD
                           WHERE  ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID
                                 AND ITEM.COMP_INVENTORY_ITEM_ID =
                                        CCD.INVENTORY_ITEM_ID
                                 AND GPS.PERIOD_ID = CCD.PERIOD_ID
                                 AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID),
                         5))
                  ELSE
                     ROUND (GXEH_SUB.ITEM_COST, 5)
               END),
            2)
            "Net Matl Usage Var."                 --Modified by Apps on 1/7/14
    FROM APPS.GL_LEDGERS GL,
         APPS.HR_ORGANIZATION_INFORMATION HOI,
         APPS.HR_ALL_ORGANIZATION_UNITS HAOU2,
         APPS.MTL_PARAMETERS MP,
         APPS.HR_ALL_ORGANIZATION_UNITS HAOU,
         APPS.MTL_SYSTEM_ITEMS_B MSI,
         APPS.GL_CODE_COMBINATIONS GCC1,
         APPS.MTL_SECONDARY_INVENTORIES MSUB,
         APPS.GL_CODE_COMBINATIONS GCC2,
         APPS.ORG_ACCT_PERIODS OAP,
         APPS.GMF_PERIOD_STATUSES GPS,
         APPS.GMF_FISCAL_POLICIES GFP,
         APPS.GMF_CALENDAR_ASSIGNMENTS GCA,
         APPS.GME_BATCH_HEADER GBH,
         APPS.GME_MATERIAL_DETAILS GMD,
         APPS.FND_LOOKUP_VALUES FLV1                  --, APPS.CM_CMPT_DTL CCD
                                    ,
         (SELECT A.FG_ITEM,
                 A.FG_DESC,
                 B.COMP_ITEM,
                 B.COMP_DESC,
                 A.BATCH_ID,
                 A.ORGANIZATION_ID,
                 A.INVENTORY_ITEM_ID,
                 A.PLAN_QTY STQTY,
                 A.ACTUAL_QTY cqty,
                 C.SCRAPPED,
                 b.reqty,
                 B.QTYISS,
                 b.uom,
                 b.inventory_item_id comp_inventory_item_id, --Added by Apps on 09Dec14
                 b.organization_id comp_organization_id --Added by Apps on 09Dec14
            FROM (SELECT msi.segment1 FG_Item,
                         MSI.DESCRIPTION FG_DESC,
                         GMD.BATCH_ID,
                         GMD.ORGANIZATION_ID,
                         GMD.INVENTORY_ITEM_ID,
                         GMD.ACTUAL_QTY,
                         gmd.PLAN_QTY
                    FROM APPS.GME_MATERIAL_DETAILS GMD,
                         apps.MTL_SYSTEM_ITEMS_B MSI
                   WHERE     GMD.LINE_TYPE = 1                       --product
                         AND MSI.ORGANIZATION_ID = GMD.ORGANIZATION_ID
                         AND msi.inventory_item_id = gmd.inventory_item_id) A,
                 (SELECT msi.segment1 comp_item,
                         MSI.DESCRIPTION COMP_DESC,
                         GMD.BATCH_ID,
                         GMD.ORGANIZATION_ID,
                         GMD.INVENTORY_ITEM_ID,
                         GMD.PLAN_QTY REQTY,
                         GMD.ACTUAL_QTY QTYISS,
                         msi.primary_uom_code uom
                    FROM APPS.GME_MATERIAL_DETAILS GMD,
                         apps.MTL_SYSTEM_ITEMS_B MSI
                   WHERE     gmd.LINE_TYPE = -1                   --ingredient
                         AND NVL (GMD.BY_PRODUCT_TYPE, 'N') != 'Y' --Added by Apps on 12Dec14
                         AND MSI.ORGANIZATION_ID = GMD.ORGANIZATION_ID
                         AND MSI.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID) B,
                 (  SELECT SUM (ACTUAL_QTY) SCRAPPED, BATCH_ID
                      FROM APPS.GME_MATERIAL_DETAILS
                     WHERE  NVL (BY_PRODUCT_TYPE, 'N') != 'Y' --Added by Apps on 12Dec14
                           AND LINE_TYPE = 2
                  GROUP BY BATCH_ID) c
           WHERE A.BATCH_ID = B.BATCH_ID AND A.BATCH_ID = C.BATCH_ID(+)) ITEM,
         -- Revision 1.8: Added below sub query to improve performance
         (  SELECT GXEH.SOURCE_DOCUMENT_ID,
                   GXEH.INVENTORY_ITEM_ID,
                   GXEH.ORGANIZATION_ID,
                   NVL (SUM (GXEH.TRANSACTION_VALUE / TRANSACTION_QUANTITY), 0)
                      ITEM_COST
              FROM APPS.GMF_XLA_EXTRACT_HEADERS GXEH
             WHERE GXEH.event_class_code = 'BATCH_MATERIAL'
          GROUP BY GXEH.SOURCE_DOCUMENT_ID,
                   GXEH.INVENTORY_ITEM_ID,
                   GXEH.ORGANIZATION_ID) GXEH_SUB
   WHERE  1=1 and  GL.LEDGER_ID = TO_NUMBER (HOI.ORG_INFORMATION1)
         -- ===========================================
         -- Organization joins to the HR org model
         -- ===========================================
         AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
        AND GL.NAME = 'OPM US'
         AND HAOU2.ORGANIZATION_ID = TO_NUMBER (HOI.ORG_INFORMATION3)
         AND HOI.ORGANIZATION_ID = MP.ORGANIZATION_ID
         AND MP.PROCESS_ENABLED_FLAG = 'Y'
         AND SUBSTR (MP.ORGANIZATION_CODE, 1, 1) <> '9'
         AND HOI.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
         AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
         -- Comment this out, can be inconsistent. D.Volz 08-Jan-2015
         -- AND MSI.PROCESS_COSTING_ENABLED_FLAG = 'Y'
         -- ===========================================
         -- Accounting code combination joins
         -- ===========================================
         AND MSI.COST_OF_SALES_ACCOUNT = GCC1.CODE_COMBINATION_ID
         AND MSUB.ORGANIZATION_ID = MSI.ORGANIZATION_ID
         AND MSUB.MATERIAL_ACCOUNT = GCC2.CODE_COMBINATION_ID
         -- ===========================================
         -- Period name joins
         -- ===========================================
         AND OAP.ORGANIZATION_ID = MP.ORGANIZATION_ID
         AND OAP.PERIOD_NAME = GPS.PERIOD_CODE
         AND OAP.SCHEDULE_CLOSE_DATE = TRUNC (GPS.END_DATE)
         AND OAP.PERIOD_START_DATE = GPS.START_DATE
         AND GPS.LEGAL_ENTITY_ID = TO_NUMBER (HOI.ORG_INFORMATION2)
         AND GPS.LEGAL_ENTITY_ID = GFP.LEGAL_ENTITY_ID
         AND GPS.COST_TYPE_ID = GFP.COST_TYPE_ID
         AND GPS.LEGAL_ENTITY_ID = GCA.LEGAL_ENTITY_ID
         AND GPS.CALENDAR_CODE = GCA.CALENDAR_CODE
         AND GPS.COST_TYPE_ID = GCA.COST_TYPE_ID
         -- ===========================================
         -- Batch joins
         -- ===========================================
         AND GBH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
         AND GBH.BATCH_ID = GMD.BATCH_ID
         AND GMD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
         AND GMD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
         -- ===========================================
         -- lookup type joins
         -- ===========================================
         AND FLV1.LOOKUP_TYPE = 'GME_BATCH_STATUS'
         AND FLV1.LOOKUP_CODE = GBH.BATCH_STATUS
         AND FLV1.SOURCE_LANG = USERENV ('LANG')
         AND FLV1.LANGUAGE = USERENV ('LANG')
         -- ===========================================
         -- Batch date validation
         -- ===========================================
         -- Revision version 1.5 from Doug Volz 13-Jan-2015
         -- Incorrect column used
         -- AND ( (  (    gbh.ACTUAL_CMPLT_DATE IS NULL
         --          AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date)
         --      OR (    GBH.ACTUAL_CMPLT_DATE IS NOT NULL
         --          and TRUNC (GBH.ACTUAL_CMPLT_DATE) > OAP.SCHEDULE_CLOSE_DATE))
         --          or (GBH.ACTUAL_CMPLT_DATE >= OAP.PERIOD_START_DATE
         --          and TRUNC (GBH.ACTUAL_CMPLT_DATE) <  OAP.SCHEDULE_CLOSE_DATE + 1))
         AND (   (   (    gbh.BATCH_CLOSE_DATE IS NULL
                      AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date)
                  OR (    GBH.BATCH_CLOSE_DATE IS NOT NULL
                      AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE))
              OR (    GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE
                  AND TRUNC (GBH.BATCH_CLOSE_DATE) < OAP.SCHEDULE_CLOSE_DATE + 1))
         -- End of revision 1.5 from Doug Volz
         -- ===========================================
         -- Ingredients joins
         -- ===========================================
         AND GMD.BATCH_ID = ITEM.BATCH_ID
         AND GMD.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
         AND MSI.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
        AND OAP.PERIOD_NAME = 'JUN-08'
         AND MSUB.SECONDARY_INVENTORY_NAME = GMD.SUBINVENTORY
         AND MSUB.ORGANIZATION_ID = GMD.ORGANIZATION_ID
         AND ITEM.BATCH_ID = GXEH_SUB.SOURCE_DOCUMENT_ID(+)
         AND ITEM.COMP_INVENTORY_ITEM_ID = GXEH_SUB.INVENTORY_ITEM_ID(+)
         AND ITEM.COMP_ORGANIZATION_ID = GXEH_SUB.ORGANIZATION_ID(+)
GROUP BY (CASE
             WHEN     GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE
                  AND TRUNC (GBH.BATCH_CLOSE_DATE) <
                         oap.schedule_close_date + 1
             THEN
                'Variance'
             WHEN     gbh.BATCH_CLOSE_DATE IS NULL          -- the job is OPEN
                  AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date
             THEN
                'Valuation'
             WHEN     GBH.BATCH_CLOSE_DATE IS NOT NULL -- the job is closed and ...the job was closed after the accounting period
                  AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE
             THEN
                'Valuation'
          END),
         -- End revision 1.5 from Doug Volz
         GL.NAME,
         haou2.name,
         mp.organization_code,
         oap.period_name,
         GCC1.SEGMENT1,
         GCC2.SEGMENT2,
         gcc2.segment4,
         gcc1.segment5,
         gcc2.segment6,
         gcc2.segment7,
         gcc2.segment8,
         GCC2.SEGMENT9,
         GBH.BATCH_NO,
         FLV1.MEANING,
         TO_CHAR (GBH.ACTUAL_CMPLT_DATE, 'DD-MON-YYYY'),
         TO_CHAR (GBH.BATCH_CLOSE_DATE, 'DD-MON-YYYY'),
         TO_CHAR (GBH.LAST_UPDATE_DATE, 'DD-MON-YYYY'),
         ITEM.STQTY,
         ITEM.CQTY,
         ITEM.SCRAPPED,
         ITEM.FG_ITEM,
         ITEM.FG_DESC,
         ITEM.COMP_ITEM,
         ITEM.COMP_DESC,
         GL.CURRENCY_CODE,
         item.uom,
         DECODE (ITEM.QTYISS,
                 NVL (ITEM.QTYISS, 0), NVL (ITEM.QTYISS, 0),
                 NVL (ITEM.QTYISS, 0) / NVL (item.STQTY, 0)),
         ITEM.REQTY,
         ITEM.QTYISS,
         NVL (ITEM.REQTY, 0) - NVL (ITEM.QTYISS, 0),
         MSI.ORGANIZATION_ID,
         MSI.INVENTORY_ITEM_ID,
         GPS.PERIOD_ID,
         GPS.COST_TYPE_ID,
         -- Revision for version 1.9, needed for column select statements
         ITEM.COMP_INVENTORY_ITEM_ID,               --Added by Apps on 09Dec14
         ITEM.COMP_ORGANIZATION_ID,                --Added by Apps on 09Dec14,
         GBH.BATCH_ID,                              --Added by Apps on 09Dec14
         GXEH_SUB.ITEM_COST
ORDER BY 2,
         3,
         4,
         5,
         6,
         7,
         8,
         15,
         17,
         28