OPM WIP Value

Description
Categories: Draft
Columns: Report Type, Ledger, Operating Unit, Org Code, Period Name, Co, Cost Ctr, Acct, Sub-Acct, Prod Grp ...
draft WIP value from Doug Voltz - unable to edit
  SELECT (CASE
             WHEN     wdj.DATE_CLOSED >= OAP.PERIOD_START_DATE
                  AND TRUNC (wdj.DATE_CLOSED) < OAP.SCHEDULE_CLOSE_DATE + 1
             THEN
                'Variance'
             WHEN     wdj.date_closed IS NULL               -- the job is OPEN
                  AND TRUNC (wdj.CREATION_DATE) <= OAP.SCHEDULE_CLOSE_DATE
             THEN
                'Valuation'
             WHEN     wdj.DATE_CLOSED IS NOT NULL -- the job is closed and ...the job was closed after the accounting period
                  AND TRUNC (wdj.DATE_CLOSED) > OAP.SCHEDULE_CLOSE_DATE
             THEN
                'Valuation'
          END)
            "Report Type",
         gl.name "Ledger",
         haou2.name "Operating Unit",
         mp.organization_code "Org Code",
         oap.period_name "Period Name",
         gcc2.segment1 "Co",
         gcc1.segment2 "Cost Ctr",
         gcc1.segment3 "Acct",
         gcc1.segment4 "Sub-Acct",
         gcc2.segment5 "Prod Grp",
         gcc1.segment6 "Loc",
         gcc1.segment7 "InterCo",
         gcc1.segment8 "Future1",
         gcc1.segment9 "Future2",
         wac.class_code "WIP Class",
         ml2.meaning "Class Type",
         we.wip_entity_name "Batch or WIP Job",
         ml.meaning "Batch or Job Status",
         TO_CHAR (wdj.date_completed, 'DD-MON-YYYY') "Date Completed",
         TO_CHAR (wdj.date_closed, 'DD-MON-YYYY') "Date Closed",
         TO_CHAR (wdj.last_update_date, 'DD-MON-YYYY') "Last Updated",
         NVL (wdj.START_QUANTITY, 0) "Start Quantity",
         NVL (wdj.quantity_completed, 0) "FG Quantity Completed",
         NVL (wdj.quantity_scrapped, 0) "FG Quantity Scrapped",
         NVL (wdj.quantity_completed, 0) + NVL (wdj.quantity_scrapped, 0)
            "Total FG Quantity",
         msi.segment1 "FG Item Number",
         msi.description "FG Description",
         msi2.segment1 "Component or Ingredient",
         msi2.description "Component or Ingredient Desc",
         gl.currency_code "Curr Code",
         SUM (cic.item_cost) "Gross Item Cost",
         NVL (
            (SELECT SUM (NVL (cicd.ITEM_COST, 0))
               FROM apps.cst_item_cost_details cicd,
                    apps.cst_cost_types cct,
                    apps.bom_resources br
              WHERE    2=2 and cicd.inventory_item_id = msi2.inventory_item_id
                    AND cicd.organization_id = msi2.organization_id
                    AND br.resource_id = cicd.resource_id
                    AND br.resource_code = 'ICP'
                    AND CCT.COST_TYPE_ID = CICD.COST_TYPE_ID),
--                    AND cct.cost_type = :P_ICP_COST_TYPE),
            0)
            "ICP Item Cost",                                    -- P_COST_TYPE
         msi2.primary_uom_code "UOM Code",
         ROUND (
            SUM (
               (DECODE (
                   wro.basis_type,
                   NULL,   NVL (wro.quantity_per_assembly, 0)
                         * 1
                         / NVL (wro.component_yield_factor, 1),
                   1,   NVL (wro.quantity_per_assembly, 0)
                      * 1
                      / NVL (wro.component_yield_factor, 1),
                   2, NVL (wro.REQUIRED_QUANTITY, 1),
                     NVL (wro.quantity_per_assembly, 0)
                   * 1
                   / NVL (wro.component_yield_factor, 1)))),
            5)
            "Quantity Per FG",
         -- A basis of 2 indicates the component is issued per lot not per assembly
         -- For a basis of 2 the component yield factor is ignored
         ROUND (
            SUM (
               (DECODE (
                   wro.basis_type,
                   NULL,   NVL (wro.quantity_per_assembly, 0)
                         * 1
                         / NVL (wro.component_yield_factor, 1)
                         * (  NVL (wdj.quantity_completed, 0)
                            + NVL (wdj.quantity_scrapped, 0)),
                   1,   NVL (wro.quantity_per_assembly, 0)
                      * 1
                      / NVL (wro.component_yield_factor, 1)
                      * (  NVL (wdj.quantity_completed, 0)
                         + NVL (wdj.quantity_scrapped, 0)),
                   2, NVL (wro.REQUIRED_QUANTITY, 1),
                     NVL (wro.quantity_per_assembly, 0)
                   * 1
                   / NVL (wro.component_yield_factor, 1)
                   * (  NVL (wdj.quantity_completed, 0)
                      + NVL (wdj.quantity_scrapped, 0))))),
            5)
            "Total Req Quantity",
         SUM (NVL (wro.quantity_issued, 0)) "Quantity Issued",
         -- Need to include component yield in the ICP calculation
         ROUND (
            SUM (
               (  NVL (wro.quantity_issued, 0)
                - -- A basis of 2 indicates the component is issued per lot not per assembly
                  -- For a basis of 2 the component yield factor is ignored
                  DECODE (
                     wro.basis_type,
                     NULL,   NVL (wro.quantity_per_assembly, 0)
                           * 1
                           / NVL (wro.component_yield_factor, 1)
                           * (  NVL (wdj.quantity_completed, 0)
                              + NVL (wdj.quantity_scrapped, 0)),
                     1,   NVL (wro.quantity_per_assembly, 0)
                        * 1
                        / NVL (wro.component_yield_factor, 1)
                        * (  NVL (wdj.quantity_completed, 0)
                           + NVL (wdj.quantity_scrapped, 0)),
                     2, NVL (wro.REQUIRED_QUANTITY, 1),
                       NVL (wro.quantity_per_assembly, 0)
                     * 1
                     / NVL (wro.component_yield_factor, 1)
                     * (  NVL (wdj.quantity_completed, 0)
                        + NVL (wdj.quantity_scrapped, 0))))),
            5)
            "Est. Quantity Left in WIP",
         ROUND (
              SUM (
                 (  NVL (wro.quantity_issued, 0)
                  - -- A basis of 2 indicates the component is issued per lot not per assembly
                    -- For a basis of 2 the component yield factor is ignored
                    DECODE (
                       wro.basis_type,
                       NULL,   NVL (wro.quantity_per_assembly, 0)
                             * 1
                             / NVL (wro.component_yield_factor, 1)
                             * (  NVL (wdj.quantity_completed, 0)
                                + NVL (wdj.quantity_scrapped, 0)),
                       1,   NVL (wro.quantity_per_assembly, 0)
                          * 1
                          / NVL (wro.component_yield_factor, 1)
                          * (  NVL (wdj.quantity_completed, 0)
                             + NVL (wdj.quantity_scrapped, 0)),
                       2, NVL (wro.REQUIRED_QUANTITY, 1),
                         NVL (wro.quantity_per_assembly, 0)
                       * 1
                       / NVL (wro.component_yield_factor, 1)
                       * (  NVL (wdj.quantity_completed, 0)
                          + NVL (wdj.quantity_scrapped, 0)))))
            * SUM (NVL (cic.ITEM_COST, 0)),
            2)
            "Gross Matl Usage Var.",
         ROUND (
              SUM (
                 (  NVL (wro.quantity_issued, 0)
                  - -- A basis of 2 indicates the component is issued per lot not per assembly
                    -- For a basis of 2 the component yield factor is ignored
                    DECODE (
                       wro.basis_type,
                       NULL,   NVL (wro.quantity_per_assembly, 0)
                             * 1
                             / NVL (wro.component_yield_factor, 1)
                             * (  NVL (wdj.quantity_completed, 0)
                                + NVL (wdj.quantity_scrapped, 0)),
                       1,   NVL (wro.quantity_per_assembly, 0)
                          * 1
                          / NVL (wro.component_yield_factor, 1)
                          * (  NVL (wdj.quantity_completed, 0)
                             + NVL (wdj.quantity_scrapped, 0)),
                       2, NVL (wro.REQUIRED_QUANTITY, 1),
                         NVL (wro.quantity_per_assembly, 0)
                       * 1
                       / NVL (wro.component_yield_factor, 1)
                       * (  NVL (wdj.quantity_completed, 0)
                          + NVL (wdj.quantity_scrapped, 0)))))
            * NVL (
                 (SELECT SUM (NVL (cicd.ITEM_COST, 0))
                    FROM apps.cst_item_cost_details cicd,
                         apps.cst_cost_types cct,
                         apps.bom_resources br
                   WHERE    2=2 and cicd.inventory_item_id = msi2.inventory_item_id
                         AND cicd.organization_id = msi2.organization_id
                         AND br.resource_id = cicd.resource_id
                         AND br.resource_code = 'ICP'
                         AND CCT.COST_TYPE_ID = CICD.COST_TYPE_ID),
--                         AND cct.cost_type = :P_ICP_COST_TYPE),
                 0),
            2)
            "ICP in WIP Variances",                             -- P_COST_TYPE
         ROUND (
              SUM (
                 (  NVL (wro.quantity_issued, 0)
                  - -- A basis of 2 indicates the component is issued per lot not per assembly
                    -- For a basis of 2 the component yield factor is ignored
                    DECODE (
                       wro.basis_type,
                       NULL,   NVL (wro.quantity_per_assembly, 0)
                             * 1
                             / NVL (wro.component_yield_factor, 1)
                             * (  NVL (wdj.quantity_completed, 0)
                                + NVL (wdj.quantity_scrapped, 0)),
                       1,   NVL (wro.quantity_per_assembly, 0)
                          * 1
                          / NVL (wro.component_yield_factor, 1)
                          * (  NVL (wdj.quantity_completed, 0)
                             + NVL (wdj.quantity_scrapped, 0)),
                       2, NVL (wro.REQUIRED_QUANTITY, 1),
                         NVL (wro.quantity_per_assembly, 0)
                       * 1
                       / NVL (wro.component_yield_factor, 1)
                       * (  NVL (wdj.quantity_completed, 0)
                          + NVL (wdj.quantity_scrapped, 0)))))
            * (  SUM (NVL (cic.ITEM_COST, 0))
               + NVL (
                    (SELECT SUM (NVL (cicd.ITEM_COST, 0))
                       FROM apps.cst_item_cost_details cicd,
                            apps.cst_cost_types cct,
                            apps.bom_resources br
                      WHERE 2=2 and     cicd.inventory_item_id = msi2.inventory_item_id
                            AND cicd.organization_id = msi2.organization_id
                            AND br.resource_id = cicd.resource_id
                            AND br.resource_code = 'ICP'
                            AND cct.cost_type_id = cicd.cost_type_id),
                          --  AND cct.cost_type = :P_ICP_COST_TYPE),
                    0)),
            2)
            "Net Matl Usage Var."                               -- P_COST_TYPE
    FROM apps.wip_accounting_classes wac,
         apps.wip_entities we,
         -- Revision for version 1.7
         -- Get the corrected WIP Qty Completed and Qty Scrapped
         (  SELECT wdjsum.wip_entity_id,
                   wdjsum.organization_id,
                   wdjsum.class_code,
                   wdjsum.date_closed,
                   wdjsum.date_completed,
                   wdjsum.last_update_date,
                   wdjsum.creation_date,
                   wdjsum.primary_item_id,
                   wdjsum.status_type,
                   wdjsum.start_quantity,
                   SUM (wdjsum.quantity_completed) quantity_completed,
                   SUM (wdjsum.quantity_scrapped) quantity_scrapped
              FROM (SELECT wdj.wip_entity_id,
                           wdj.organization_id,
                           wdj.class_code,
                           wdj.date_closed,
                           wdj.date_completed,
                           wdj.last_update_date,
                           wdj.creation_date,
                           wdj.primary_item_id,
                           wdj.status_type,
                           wdj.start_quantity,
                           wdj.quantity_completed,
                           wdj.quantity_scrapped
                      FROM apps.wip_discrete_jobs wdj, apps.org_acct_periods oap
                     WHERE    1=1 and oap.organization_id = wdj.organization_id
                           --AND oap.period_name =
                            --      NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME
                           -- =================================================
                           -- Omit jobs created after the period close date.
                           -- =================================================
                           -- Find jobs that were open during the report period
                           AND (   (   (    wdj.date_closed IS NULL -- the job is OPEN
                                        AND TRUNC (wdj.creation_date) <=
                                               oap.schedule_close_date -- the job is OPENED before the period close date ... or ...
                                                                      )
                                    OR (    wdj.date_closed IS NOT NULL -- the job is closed and ...the job was closed after the accounting period
                                        AND TRUNC (wdj.date_closed) >
                                               oap.schedule_close_date))
                                OR -- Find jobs that were closed during the report period
                                   (    wdj.DATE_CLOSED >= OAP.PERIOD_START_DATE
                                    AND TRUNC (wdj.date_closed) <
                                           oap.schedule_close_date + 1))
                    UNION ALL
                    SELECT mmt.transaction_source_id,         -- wip_entity_id
                           mmt.organization_id,
                           wdj.class_code,
                           wdj.date_closed,
                           wdj.date_completed,
                           wdj.last_update_date,
                           wdj.creation_date,
                           wdj.primary_item_id,
                           wdj.status_type,
                           wdj.start_quantity,
                           DECODE (mmt.transaction_type_id,
                                   90, 0,         -- Scrap assemblies from WIP
                                   91, 0, -- Return assemblies scrapped from WIP
                                   44, -1 * mmt.primary_quantity, -- WIP Completion
                                   17, mmt.primary_quantity -- WIP Completion Return
                                                           )
                              quantity_completed,
                           DECODE (mmt.transaction_type_id,
                                   90, mmt.primary_quantity, -- Scrap assemblies from WIP
                                   91, -1 * mmt.primary_quantity, -- Return assemblies scrapped from WIP
                                   44, 0,                    -- WIP Completion
                                   17, 0              -- WIP Completion Return
                                        )
                              quantity_scrapped
                      FROM apps.mtl_material_transactions mmt,
                           apps.wip_discrete_jobs wdj,
                           apps.org_acct_periods oap
                     WHERE    1=1 and mmt.transaction_source_type_id = 5
                           AND mmt.transaction_source_id = wdj.wip_entity_id
                           AND mmt.transaction_date >=
                                  oap.schedule_close_date + 1
                         --  AND oap.period_name =
                          --        NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME
                           AND oap.organization_id = mmt.organization_id
                           -- Disable an index to increase performance
                           AND oap.acct_period_id + 0 = mmt.acct_period_id) wdjsum
          GROUP BY wdjsum.wip_entity_id,
                   wdjsum.organization_id,
                   wdjsum.class_code,
                   wdjsum.date_closed,
                   wdjsum.date_completed,
                   wdjsum.last_update_date,
                   wdjsum.creation_date,
                   wdjsum.primary_item_id,
                   wdjsum.status_type,
                   wdjsum.start_quantity) wdj,
         -- Revision for version 1.7
         -- Get the corrected WIP component issue quantities
         (  SELECT wrosum.wip_entity_id,
                   wrosum.organization_id,
                   wrosum.inventory_item_id,
                   wrosum.quantity_per_assembly,
                   SUM (wrosum.REQUIRED_QUANTITY) REQUIRED_QUANTITY, -- Revision 1.8 Added SUM
                   wrosum.component_yield_factor,
                   SUM (wrosum.quantity_issued) quantity_issued,
                   wrosum.basis_type basis_type
              FROM (SELECT wro.wip_entity_id,
                           wro.organization_id,
                           wro.inventory_item_id,
                           wro.quantity_per_assembly,
                           wro.REQUIRED_QUANTITY,
                           wro.component_yield_factor,
                           wro.quantity_issued,
                           wro.basis_type
                      FROM apps.wip_requirement_operations wro,
                           apps.wip_discrete_jobs wdj,
                           apps.org_acct_periods oap
                     WHERE   1=1 and  wdj.wip_entity_id = wro.wip_entity_id
                           AND wdj.organization_id = wro.organization_id
                           AND wdj.organization_id = oap.organization_id
                       --    AND oap.period_name =
                        --          NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME
                           -- =================================================
                           -- Omit jobs created after the period close date.
                           -- =================================================
                           -- Find jobs that were open during the report period
                           AND (   (   (    wdj.date_closed IS NULL -- the job is OPEN
                                        AND TRUNC (wdj.creation_date) <=
                                               oap.schedule_close_date -- the job is OPENED before the period close date ... or ...
                                                                      )
                                    OR (    wdj.date_closed IS NOT NULL -- the job is closed and ...the job was closed after the accounting period
                                        AND TRUNC (wdj.date_closed) >
                                               oap.schedule_close_date))
                                OR -- Find jobs that were closed during the report period
                                   (    wdj.DATE_CLOSED >= OAP.PERIOD_START_DATE
                                    AND TRUNC (wdj.date_closed) <
                                           oap.schedule_close_date + 1))
                    UNION ALL
                    SELECT mmt.transaction_source_id,
                           wro.organization_id,
                           mmt.inventory_item_id,
                           wro.quantity_per_assembly,
                           wro.REQUIRED_QUANTITY,
                           wro.component_yield_factor,
                           DECODE (mmt.transaction_type_id,
                                   35, mmt.primary_quantity, -- WIP Component Issue
                                   43, -1 * mmt.primary_quantity -- WIP Component Return
                                                                )
                              quantity_issued,
                           wro.basis_type
                      FROM apps.mtl_material_transactions mmt,
                           apps.wip_discrete_jobs wdj,
                           apps.wip_requirement_operations wro,
                           apps.org_acct_periods oap
                     WHERE   1=1 and  mmt.transaction_source_type_id = 5
                           AND mmt.transaction_source_id = wdj.wip_entity_id
                           AND wro.wip_entity_id = mmt.transaction_source_id
                           AND wro.organization_id = mmt.organization_id
                           AND mmt.transaction_date >=
                                  oap.schedule_close_date + 1
                           AND oap.organization_id = mmt.organization_id
                          -- AND oap.period_name =
                          --        NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME
                           -- Disable an index to increase performance
                           AND oap.acct_period_id + 0 = mmt.acct_period_id) wrosum
          GROUP BY wrosum.wip_entity_id,
                   wrosum.organization_id,
                   wrosum.inventory_item_id,
                   wrosum.quantity_per_assembly,
                   wrosum.component_yield_factor,
                   wrosum.basis_type) wro,
         -- End revision for version 1.7
         apps.mtl_parameters mp,
         apps.mtl_system_items_b msi,
         apps.mtl_system_items_b msi2,
         -- Fix for version 1.9
         apps.cst_item_costs cic,
         apps.org_acct_periods oap,
         apps.mfg_lookups ml,
         apps.mfg_lookups ml2,
         apps.gl_code_combinations gcc1,                 -- wip class accounts
         apps.gl_code_combinations gcc2, -- product group info from COGS account
         apps.hr_organization_information hoi,
         apps.hr_all_organization_units haou,
         apps.hr_all_organization_units haou2,
         apps.gl_ledgers gl
   -- ===========================================
   -- WIP Job Entity and Class joins
   -- ===========================================
   WHERE  1=1 and   we.wip_entity_id = wdj.wip_entity_id
         AND wro.wip_entity_id = wdj.wip_entity_id
         AND wac.class_code = wdj.class_code
         AND wac.organization_id = we.organization_id
         AND oap.organization_id = mp.organization_id
		 AND  MP.ORGANIZATION_CODE = 'PR1'
    --     AND oap.period_name = NVL (:P_PERIOD_NAME, oap.period_name) -- P_PERIOD_NAME
         -- ========================================================================
         -- Mtl parameter, item master and WIP assembly and WIP component joins
         -- ========================================================================
         AND mp.organization_id = msi.organization_id
         AND msi.organization_id = we.organization_id
         AND msi.inventory_item_id = we.primary_item_id    -- FG assembly item
         AND msi.organization_id = msi2.organization_id
         AND msi2.inventory_item_id = wro.inventory_item_id  -- Component item
         -- ========================================================================
         -- Item Cost joins for the components
         -- ========================================================================
         AND msi2.organization_id = cic.organization_id
         AND msi2.inventory_item_id = cic.inventory_item_id  -- Component item
         AND cic.cost_type_id = mp.primary_cost_method -- gets the Frozen or Average cost type based on your costing method
         -- ===========================================
         -- Lookup Code joins
         -- ===========================================
         AND ml.lookup_type = 'WIP_JOB_STATUS'
         AND ml.lookup_code = wdj.status_type
         AND ml2.lookup_type = 'WIP_CLASS_TYPE'
         AND ml2.lookup_code = wac.class_type
         -- ===========================================
         -- Accounting code combination joins
         -- ===========================================
         AND wac.material_account = gcc1.code_combination_id
         AND msi.cost_of_sales_account = gcc2.code_combination_id
         -- ===========================================
         -- Organization joins to the HR org model
         -- ===========================================
         AND hoi.org_information_context = 'Accounting Information'
         AND hoi.organization_id = mp.organization_id
         AND hoi.organization_id = haou.organization_id -- this gets the organization name
         AND haou2.organization_id = TO_NUMBER (hoi.org_information3) -- this gets the operating unit id
         AND gl.ledger_id = TO_NUMBER (hoi.org_information1) -- get the ledger_id
 
GROUP BY gl.name,
         haou2.name,
         mp.organization_code,
         oap.period_name,
         gcc2.segment1,
         gcc1.segment2,
         gcc1.segment3,
         gcc1.segment4,
         gcc2.segment5,
         gcc1.segment6,
         gcc1.segment7,
         gcc1.segment8,
         gcc1.segment9,
         wac.class_code,
         ml2.meaning,
         we.wip_entity_name,
         ml.meaning,
         TO_CHAR (wdj.date_completed, 'DD-MON-YYYY'),
         TO_CHAR (wdj.date_closed, 'DD-MON-YYYY'),
         TO_CHAR (wdj.last_update_date, 'DD-MON-YYYY'),
         wdj.START_QUANTITY,
         wdj.quantity_completed,
         wdj.quantity_scrapped,
         msi.segment1,
         msi.description,
         msi2.segment1,
         msi2.description,
         gl.currency_code,
         msi2.primary_uom_code,
         MSI2.INVENTORY_ITEM_ID,
         MSI2.ORGANIZATION_ID,
         (CASE
             WHEN     wdj.DATE_CLOSED >= OAP.PERIOD_START_DATE
                  AND TRUNC (wdj.DATE_CLOSED) < OAP.SCHEDULE_CLOSE_DATE + 1
             THEN
                'Variance'
             WHEN     wdj.date_closed IS NULL               -- the job is OPEN
                  AND TRUNC (wdj.CREATION_DATE) <= OAP.SCHEDULE_CLOSE_DATE
             THEN
                'Valuation'
             WHEN     wdj.DATE_CLOSED IS NOT NULL -- the job is closed and ...the job was closed after the accounting period
                  AND TRUNC (wdj.DATE_CLOSED) > OAP.SCHEDULE_CLOSE_DATE
             THEN
                'Valuation'
          END)
UNION ALL
  --====================================
  --OPM WIP Ingredient Query
  --====================================
  SELECT                            -- Revision 1.5 from Doug Volz 13-Jan-2015
                    -- Replace logic, used wrong column to indicate close date
                   -- (case when GBH.ACTUAL_CMPLT_DATE>= OAP.PERIOD_START_DATE
             -- and TRUNC (GBH.ACTUAL_CMPLT_DATE)<  oap.schedule_close_date +1
                                                            -- then 'Variance'
                 -- when gbh.ACTUAL_CMPLT_DATE is null      -- the job is OPEN
                  -- and  TRUNC (gbh.creation_DATE) <= oap.schedule_close_date
                                                           -- then 'Valuation'
 -- when GBH.ACTUAL_CMPLT_DATE is not null   -- the job is closed and ...the job was closed after the accounting period
                -- and TRUNC (GBH.ACTUAL_CMPLT_DATE) > OAP.SCHEDULE_CLOSE_DATE
         (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 /