CAC WIP Material Relief

➜ Back to Library
Description
Categories: Cost Accounting - Transactions, Enginatics
Repository: Github
Report to show WIP material variances on closed jobs for discrete manufacturing, in summary by inventory organization, with WIP class, job status, name and other details. Including any profit in inventory or PII amounts. But unlike the more recent CAC WIP Manufacturing Variance and CAC WIP Material Usage Variance reports, this report uses the latest material issue quantities as stored on the WIP ...  Report to show WIP material variances on closed jobs for discrete manufacturing, in summary by inventory organization, with WIP class, job status, name and other details. Including any profit in inventory or PII amounts. But unlike the more recent CAC WIP Manufacturing Variance and CAC WIP Material Usage Variance reports, this report uses the latest material issue quantities as stored on the WIP job definition, even if you run it for a prior period. It does not rollback the component issue quantities for a prior accounting period.

Parameters:
==========
Period Name: the accounting period you wish to report (mandatory).
Cost Type: defaults to your Costing Method. If blank the report uses your Costing Method Cost Type (mandatory).
PII Cost Type: enter the cost type for your profit in inventory item costs to report (optional).
PII Sub-Element: the sub-element or resource for profit in inventory, such as PII or ICP (optional).
Category Set 1: any item category you wish (optional).
Category Set 2: any item category you wish (optional).
Class Code: specific type of WIP class to report (optional).
Job Status: specific WIP job status (optional).
WIP Job: specific WIP job (optional).
Assembly Number: specific assembly number you wish to report (optional)
Component Number: specific component item you wish to report (optional)
Show Phantom Components: show the material usage variances for phantom components (optional).
Organization Code: any inventory organization, defaults to your session's inventory organization (optional).
Operating Unit: specific operating unit (optional).
Ledger: specific ledger (optional).

/* +=============================================================================+
-- | Copyright 2009-24 Douglas Volz Consulting, Inc.
-- | All rights reserved.
-- | Permission to use this code is granted provided the original author is acknowledged.
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_wip_relief_rept.sql
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 11 Jan 2010 Douglas Volz Initial Coding based on XXX_ICP_WIP_COMPONENT_VAL_REPT.sql,
-- | with this report analyzing the WIP variances on
-- | closed jobs. Added org_acct_periods to limit to
-- | jobs closed within the accounting period.
-- | 1.9 26 Sep 2010 Douglas Volz Added Component Item Number to report sort.
-- | 1.10 09 Feb 2012 Douglas Volz Included component yield in required quantities;
-- | decided to not exclude WIP expense jobs as they
-- | could have components with ICP issued to the job
-- | and the cost accountants would need the visibility.
-- | 1.11 23 Feb 2012 Douglas Volz Added component UOM code, to be consistent with
-- | other reports.
-- | 1.12 07 Oct 2020 Andy Haack Modify for Blitz Report with Blitz lookup functions
-- | (xxen_util) and re-format sql code.
-- | 1.13 11 Jul 2022 Douglas Volz Multi-language changes; added back PII parameters. Changed
-- | wro.component_yield_factor to nvl(wro.component_yield_factor,1).
-- | 1.14 23 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and inventory org access controls.
-- | Added category set parameters.
-- +=============================================================================+*/   more

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select  gl.name Ledger,
        haouv2.name Operating_Unit,
        mp.organization_code Org_Code,
        oap.period_name,
        &company_segment
        &segment_columns
        wac.class_code WIP_Class,
        xxen_util.meaning(wac.class_type,'WIP_CLASS_TYPE',700) Class_Type,
        we.wip_entity_name WIP_Job,
        xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status,
        wdj.date_completed,
        wdj.date_closed,
        wdj.start_quantity,
        wdj.quantity_completed Assembly_Quantity_Completed,
        wdj.quantity_scrapped assembly_Quantity_Scrapped,
        wdj.quantity_completed + wdj.quantity_scrapped Total_assembly_Quantity,
        msiv.concatenated_segments Assembly_Number,
        msiv.description Assembly_Description,
-- Revision for version 1.14
&category_columns
        msiv2.concatenated_segments Component_Number,
        msiv2.description Component_Description,
        gl.currency_code Currency_Code,
        sum(cic.item_cost) Gross_Item_Cost,
        nvl(cicd.item_cost,0) PII_Item_Cost,
        -- Revision for version 1.13
        -- msi2.primary_uom_code UOM_Code,
        muomv.uom_code UOM_Code,
        -- +=====================================================
        -- need to include component yield in the icp calculation
        -- +=====================================================
        -- a basis of 2 indicates the component is issued per lot not per assembly, and the component yield factor is ignored
        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)
                   )
            )
           ) Quantity_Per_Assembly,
        -- a basis of 2 indicates the component is issued per lot not per assembly, and the component yield factor is ignored
        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))
                   )
            )
           ) Total_Required_Quantity,
        sum(nvl(wro.quantity_issued,0)) Quantity_Issued,
        -- need to include component yield in the calculation
        sum((nvl(wro.quantity_issued,0) - 
                    -- a basis of 2 indicates the component is issued per lot not per assembly, and 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))
                          )
            )
           ) Quantity_Left_in_WIP,
        sum((nvl(wro.quantity_issued,0) - 
                    -- a basis of 2 indicates the component is issued per lot not per assembly, and 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)) Gross_Material_Usage_Var,
        sum((nvl(wro.quantity_issued,0) - 
                    -- a basis of 2 indicates the component is issued per lot not per assembly, and 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(cicd.item_cost,0) PII_in_WIP,
        sum((nvl(wro.quantity_issued,0) - 
                    -- a basis of 2 indicates the component is issued per lot not per assembly, and 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(cicd.item_cost,0)) Net_Material_Usage_Var
from    org_acct_periods oap,
        wip_entities we,
        wip_discrete_jobs wdj,
        wip_accounting_classes wac,
        wip_requirement_operations wro,
        mtl_parameters mp,
        mtl_system_items_vl msiv,
        mtl_system_items_vl msiv2,
        cst_item_costs cic,
        -- Revision for version 1.13
        mtl_units_of_measure_vl muomv,
        cst_cost_types cct,
        gl_code_combinations gcc1,  -- wip class accounts
        gl_code_combinations gcc2,  -- product group info from cogs account
        hr_organization_information hoi,
        hr_all_organization_units_vl haouv,
        hr_all_organization_units_vl haouv2,
        gl_ledgers gl,
        (select cicd.inventory_item_id,
                cicd.organization_id,
                sum(cicd.item_cost) item_cost
         from   cst_item_cost_details cicd,
                bom_resources br,
                cst_cost_types cct
         where  cicd.resource_id=br.resource_id
         and    br.resource_code= :p_pii_sub_element
         and    cicd.cost_type_id=cct.cost_type_id
         and    cct.cost_type=:p_pii_cost_type
         group by
                cicd.inventory_item_id,
                cicd.organization_id
        ) cicd
where   1=1
and     mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and     oap.organization_id        = we.organization_id
and     wdj.date_closed           >= oap.period_start_date
and     wdj.date_closed           <  oap.schedule_close_date+1
and     we.wip_entity_id           = wdj.wip_entity_id
and     wdj.class_code             = wac.class_code
and     wdj.organization_id        = wac.organization_id
and     wdj.wip_entity_id          = wro.wip_entity_id
-- =================================================================
-- mtl parameter, item master and period close snapshot joins
-- =================================================================
and    mp.organization_id          = msiv.organization_id
and    msiv.organization_id        = we.organization_id
and    msiv.inventory_item_id      = we.primary_item_id    -- fg assembly item
and    msiv.organization_id        = msiv2.organization_id
and    msiv2.inventory_item_id     = wro.inventory_item_id -- component item
and    msiv2.organization_id       = cic.organization_id
and    msiv2.inventory_item_id     = cic.inventory_item_id  -- component item
-- Revision for version 1.13
and    msiv2.primary_uom_code      = muomv.uom_code
and    cct.cost_type               = :p_cost_type
-- If you do not specify a cost type, gets the frozen or average cost type based on your costing method
and    cic.cost_type_id            = nvl(cct.cost_type_id, mp.primary_cost_method)
-- End of revision for version 1.13
and    wac.material_account        = gcc1.code_combination_id
and    msiv.cost_of_sales_account  = gcc2.code_combination_id
and    hoi.org_information_context = 'Accounting Information'
and    hoi.organization_id         = mp.organization_id
and    hoi.organization_id         = haouv.organization_id   -- this gets the organization name
and    haouv2.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
and    msiv2.inventory_item_id     = cicd.inventory_item_id(+)
and    msiv2.organization_id       = cicd.organization_id(+)
group by
        gl.name,
        haouv2.name,
        mp.organization_code,
        oap.period_name,
        &group_by_company_seg
        &group_by_segment_columns
        wac.class_code,
        we.wip_entity_name,
        xxen_util.meaning(wac.class_type,'WIP_CLASS_TYPE',700),
        xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700),
        wdj.date_completed,
        wdj.date_closed,
        wdj.start_quantity,
        wdj.quantity_completed,
        wdj.quantity_scrapped,
        wdj.net_quantity,
        msiv.concatenated_segments,
        msiv.description,
        -- Revision for version 1.14
        -- For Category columns
        msiv.inventory_item_id,
        msiv.organization_id,
        -- End revision for version 1.14
        msiv2.concatenated_segments,
        msiv2.description,
        gl.currency_code,
        -- Revision for version 1.13 
        -- msi2.primary_uom_code,
        muomv.uom_code,
        msiv2.inventory_item_id,
        msiv2.organization_id,
        cicd.item_cost
order by 1,2,3,4,5,6,7,9,21
Parameter Name SQL text Validation
Period Name 
oap.period_name=:p_period_name
 LOV
Cost Type 
 
 LOV
PII Cost Type 
 
 LOV
PII Sub-Element 
 
 LOV
Category Set 1 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
 LOV
Category Set 2 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name2 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name2
 LOV
Class Code 
wdj.class_code=:p_class_code
 LOV
Job Status 
wdj.status_type=xxen_util.lookup_code(:job_status,'WIP_JOB_STATUS',700)
 LOV
WIP Job 
crp.source_transaction_id in (
select
mr.transaction_id
from
wip_entities we,
mrp_recommendations mr
where
we.wip_entity_name=:job_name and
we.wip_entity_id=mr.disposition_id and
mr.order_type in (3,7,14,15,27,28)
)
 LOV
Assembly Number 
msiv.concatenated_segments = :p_item_number
 LOV
Component Number 
msiv2.concatenated_segments = :p_item_number
 LOV
Show Phantom Components 
wro.wip_supply_type<>6
 LOV Oracle
Organization Code 
mp.organization_code=:p_organization_code
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
   
Blitz Report™

Blitz Report™ provides multiple benefits: