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
   -- ================