CAC OPM WIP Account Value

Description
Categories: Cost Accounting - Inventory Value
Report to show WIP values for process manufacturing (OPM), in summary by inventory, organization, with batch status, name and other details. The valuation accounts come from the cumulative WIP Valuation accounting entries, as processed by Create Accounting.

Parameters:
===========
Period Name: the inventory accounting period you wish to report (mandatory).
Include Lab Batches: e ... 
Report to show WIP values for process manufacturing (OPM), in summary by inventory, organization, with batch status, name and other details. The valuation accounts come from the cumulative WIP Valuation accounting entries, as processed by Create Accounting.

Parameters:
===========
Period Name: the inventory accounting period you wish to report (mandatory).
Include Lab Batches: enter Yes to include laboratory batches. Enter No to exclude them. Defaults to No (mandatory).
Category Set 1: the first item category set to report, typically the Cost or Product Line Category Set (optional).
Category Set 2: The second item category set to report, typically the Inventory Category Set (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 2014 - 2024 Douglas Volz Consulting, Inc.
-- | All rights reserved.
-- | Permission to use this code is granted provided the original author is
-- | acknowledged. No warranties, express or otherwise is included in this permission.
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_opm_wip_acct_value_rept.sql
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 22-Oct-2014 Douglas Volz Initial version.
-- | 1.1 06-Jul-2024 Douglas Volz Cumulative changes plus format for Blitz Report.
-- +=============================================================================+*/
   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,
        haou2.name Operating_Unit,
        wip.organization_code Org_Code,
        wip.period_name Period_Name,
        &segment_columns
        gem_l1.meaning Batch_Type,
        wip.batch_no Batch_Number,
        gem_l2.meaning Batch_Status,
        wip.formula_no Formula_Number,
        wip.formula_vers Formula_Version,
        wip.routing_no Routing_Number,
        wip.routing_vers Routing_Version,
        wip.recipe_no Recipe_Number,
        wip.recipe_version Recipe_Version,
        wip.creation_date Creation_Date,
        wip.plan_start_date Planned_Start_Date,
        wip.actual_start_date Actual_Start_Date,
        wip.due_date Due_Date,
        wip.actual_cmplt_date Actual_Completion_Date,
        wip.batch_close_date Batch_Close_Date,
        case 
           when (wip.schedule_close_date - wip.creation_date) < 31  then '30 days'
           when (wip.schedule_close_date - wip.creation_date) < 61  then '60 days'
           when (wip.schedule_close_date - wip.creation_date) < 91  then '90 days'
           when (wip.schedule_close_date - wip.creation_date) < 121 then '120 days'
           when (wip.schedule_close_date - wip.creation_date) < 151 then '150 days'
           when (wip.schedule_close_date - wip.creation_date) < 181 then '180 days'
           else 'Over 180 days'
        end Aged_Creation_Date,
        case 
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 31  then '30 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 61  then '60 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 91  then '90 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 121 then '120 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 151 then '150 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 181 then '180 days'
           else 'Over 180 days'
        end Aged_Compln_vs_Release_Date,
        case 
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 31  then '30 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 61  then '60 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 91  then '90 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 121 then '120 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 151 then '150 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 181 then '180 days'
           else 'Over 180 days'
        end Aged_Compln_vs_Creation_Date,
        msiv.concatenated_segments Product_Number,
        msiv.description Product_Description,
        muomv.uom_code UOM_Code,
        fcl.meaning Item_Type,
&category_columns
        xl.meaning Accounting_Class,
        gl.currency_code Currency_Code,
        sum(wip.wip_costs_in) WIP_Costs_In,
        sum(wip.wip_costs_out) WIP_Costs_Out,
        sum(wip.wip_relief) WIP_Relief,
        sum(wip.wip_value) WIP_Value
from    mtl_system_items_vl msiv,
        mtl_units_of_measure_vl muomv,
        gl_code_combinations gcc,
        gl_ledgers gl,
        hr_organization_information hoi,
        hr_all_organization_units haou,
        hr_all_organization_units haou2,
        fnd_common_lookups fcl,
        xla_lookups xl,
        gem_lookups gem_l1,
        gem_lookups gem_l2,
        -- ==========================================================
        -- This first select gets the OPM Batch Resource
        -- Transactions based on the view gmf_subledger_rep_v.
        -- See the section called Q6 batch Resource transactions
        -- ==========================================================
        (select xah.ledger_id,
                mp.organization_code,
                mp.organization_id,
                oap.period_name,
                xal.code_combination_id,
                oap.schedule_close_date,
                gbh.laboratory_ind, -- Batch Type
                gbh.batch_no,
                gbh.batch_id,
                gbh.batch_status,
                ffmv.formula_no,
                ffmv.formula_vers,
                frh.routing_no,
                frh.routing_vers,
                gr.recipe_no,
                gr.recipe_version,
                gbh.creation_date,
                gxeh.inventory_item_id,
                gbh.plan_start_date,
                gbh.actual_start_date,
                gbh.due_date,
                gbh.actual_cmplt_date,
                gbh.batch_close_date,
                xal.accounting_class_code,
                gxeh.event_class_code,
                gxeh.transaction_type_id,
                sum(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)) wip_costs_in,
                sum(0) wip_costs_out,
                sum(0) wip_relief,
                sum(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)) wip_value        
         from   gme_batch_header gbh,
                fm_form_mst_vl ffmv,
                fm_rout_hdr frh,
                gmd_recipes gr,
                gmd_recipe_validity_rules grvr, 
                gmf_xla_extract_headers gxeh,
                gme_resource_txns grt,
                org_acct_periods oap,
                xla_ae_headers xah,
                xla_ae_lines xal,
                mtl_parameters mp,
                gmf_fiscal_policies gfp
         where  gxeh.event_id                   = xah.event_id
         and    gxeh.organization_id            = mp.organization_id
         and    gxeh.transaction_id             = grt.poc_trans_id
         and    gxeh.source_document_id         = grt.doc_id
         and    gxeh.source_line_id             = grt.line_id
         and    gxeh.resources                  = grt.resources
         and    gbh.batch_id                    = grt.doc_id
         and    gbh.formula_id                  = ffmv.formula_id (+)  
         and    gbh.routing_id                  = frh.routing_id (+)
         and    gbh.recipe_validity_rule_id     = grvr.recipe_validity_rule_id (+)
         and    grvr.recipe_id                  = gr.recipe_id (+)
         and    mp.organization_id              = gbh.organization_id
         and    gxeh.txn_source                 = 'PM'
         and    xal.accounting_class_code       = 'WIP_VALUATION'
         and    mp.process_enabled_flag         = 'Y'
         -- ===========================================
         -- Limit to just WIP Resource Txns
         -- ===========================================
         and    gxeh.entity_code                = 'PRODUCTION'
         and    gxeh.event_class_code           = 'BATCH_RESOURCE'
         -- ===========================================
         -- Inventory accounting period joins to limit
         -- to wip activity within the accounting period.
         -- ===========================================
         and    oap.period_name                 = xah.period_name
         and    oap.organization_id             = mp.organization_id
         -- Limit to jobs closed after the period start date
         and    nvl(trunc(gbh.batch_close_date), oap.period_start_date) >= oap.period_start_date
         -- ===========================================
         -- Only get entries which go to the G/L
         -- ===========================================
         and    gfp.ledger_id                   = xah.ledger_id
         and    gfp.cost_type_id                = gxeh.valuation_cost_type_id
         and    gfp.legal_entity_id             = (select to_number(hoi.org_information2) 
                                                   from   hr_organization_information hoi
                                                   where  hoi.org_information_context     = 'Accounting Information'
                                                   and    hoi.organization_id             = mp.organization_id)
         -- ===========================================
         -- Subledger Accounting joins
         -- ===========================================
         and    xah.ae_header_id                = xal.ae_header_id
         and    xah.ledger_id                   = xal.ledger_id
         and    xah.application_id              = 555
         and    xah.application_id              = xal.application_id
         and    2=2                             -- p_org_code, p_period_name, p_include_lab_batches
         group by
                xah.ledger_id,
                mp.organization_code,
                mp.organization_id,
                oap.period_name,
                xal.code_combination_id,
                oap.schedule_close_date,
                gbh.laboratory_ind, -- Batch Type
                gbh.batch_no,
                gbh.batch_id,
                gbh.batch_status,
                ffmv.formula_no,
                ffmv.formula_vers,
                frh.routing_no,
                frh.routing_vers,
                gr.recipe_no,
                gr.recipe_version,
                gbh.creation_date,
                gxeh.inventory_item_id,
                gbh.plan_start_date,
                gbh.actual_start_date,
                gbh.due_date,
                gbh.actual_cmplt_date,
                gbh.batch_close_date,
                xal.accounting_class_code,
                gxeh.event_class_code,
                gxeh.transaction_type_id
         union all
         -- ==========================================================
         -- This second select gets the OPM Batch Close Transactions
         -- based on the view gmf_subledger_rep_v.
         -- See the section called Q7 Batch Close Variances
         -- ==========================================================
         select xah.ledger_id,
                mp.organization_code,
                mp.organization_id,
                oap.period_name,
                xal.code_combination_id,
                oap.schedule_close_date,
                gbh.laboratory_ind, -- Batch Type
                gbh.batch_no,
                gbh.batch_id,
                gbh.batch_status,
                ffmv.formula_no,
                ffmv.formula_vers,
                frh.routing_no,
                frh.routing_vers,
                gr.recipe_no,
                gr.recipe_version,
                gbh.creation_date,
                we.primary_item_id inventory_item_id,
                gbh.plan_start_date,
                gbh.actual_start_date,
                gbh.due_date,
                gbh.actual_cmplt_date,
                gbh.batch_close_date,
                xal.accounting_class_code,
                gxeh.event_class_code,
                gxeh.transaction_type_id,
                sum(0) wip_costs_in,
                sum(0) wip_costs_out,
                -- Revision, change SIGN of WIP Cost Relief to match Oracle (Discrete) WIP Value Report
                decode(xal.accounting_class_code,
                                'WIP_VALUATION',   -1 * sum(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)),
                                sum(0)
                      ) wip_relief,
                decode(xal.accounting_class_code,
                                'WIP_VALUATION', sum(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)),
                                sum(0)
                      ) wip_value
         from   gme_batch_header gbh,
                fm_form_mst_vl ffmv,
                fm_rout_hdr frh,
                gmd_recipes gr,
                gmd_recipe_validity_rules grvr, 
                gmf_xla_extract_headers gxeh,
                org_acct_periods oap,
                -- Revision, show Product not Ingredient
                wip_entities we,
                xla_ae_headers xah,
                xla_ae_lines xal,
                gmf_fiscal_policies gfp,
                mtl_parameters mp
         -- ===========================================
         -- Transaction, Batch and item master joins
         -- ===========================================
         where  gxeh.event_id                   = xah.event_id
         and    gxeh.transaction_id             = gbh.batch_id
         and    gxeh.source_document_id         = gbh.batch_id
         and    gxeh.organization_id            = mp.organization_id
         and    gbh.formula_id                  = ffmv.formula_id (+)  
         and    gbh.routing_id                  = frh.routing_id (+)
         and    gbh.recipe_validity_rule_id     = grvr.recipe_validity_rule_id (+)
         and    grvr.recipe_id                  = gr.recipe_id (+)
         and    mp.organization_id              = gbh.organization_id
         and    gbh.batch_status                = 4
         and    we.wip_entity_id                = gbh.batch_id
         and    we.organization_id              = mp.organization_id
         and    gxeh.txn_source                 = 'PM'
         and    gxeh.event_type_code            = 'CLOS'
         and    xal.accounting_class_code       = 'WIP_VALUATION'
         and    mp.process_enabled_flag         = 'Y'
         -- ===========================================
         -- Limit to just WIP Close Txns
         -- ===========================================
         and     gxeh.entity_code                = 'PRODUCTION'
         and     gxeh.event_class_code           = 'BATCH_CLOSE'
         -- ===========================================
         -- Inventory accounting period joins to limit
         -- to wip activity within the accounting period.
         -- ===========================================
         and    oap.period_name                 = xah.period_name
         and    oap.organization_id             = mp.organization_id
         -- Limit to jobs closed after the period start date
         and    nvl(trunc(gbh.batch_close_date), oap.period_start_date) >= oap.period_start_date
         -- ===========================================
         -- Only get entries with go to the G/L
         -- ===========================================
         and    gfp.ledger_id                   = xah.ledger_id
         and    gfp.legal_entity_id             = (select to_number(hoi.org_information2) 
                                                   from   hr_organization_information hoi
                                                   where  hoi.org_information_context     = 'Accounting Information'
                                                   and    hoi.organization_id             = mp.organization_id)
         and    gxeh.valuation_cost_type_id     = gfp.cost_type_id
         -- ===========================================
         -- Subledger Accounting joins
         -- ===========================================
         and    xah.ae_header_id                = xal.ae_header_id
         and    xah.ledger_id                   = xal.ledger_id
         and    xah.application_id              = 555
         and    xah.application_id              = xal.application_id
         and    2=2                             -- p_org_code, p_period_name, p_include_lab_batches
         group by
                xah.ledger_id,
                mp.organization_code,
                mp.organization_id,
                oap.period_name,
                xal.code_combination_id,
                oap.schedule_close_date,
                xal.code_combination_id,
                gbh.laboratory_ind, -- Batch Type
                gbh.batch_no,
                gbh.batch_id,
                gbh.batch_status,
                ffmv.formula_no,
                ffmv.formula_vers,
                frh.routing_no,
                frh.routing_vers,
                gr.recipe_no,
                gr.recipe_version,
                gbh.creation_date,
                we.primary_item_id, -- inventory_item_id
                gbh.plan_start_date,
                gbh.actual_start_date,
                gbh.due_date,
                gbh.actual_cmplt_date,
                xal.accounting_class_code,
                gbh.batch_close_date,
                gxeh.event_class_code,
                gxeh.transaction_type_id
         union all
         -- ==========================================================
         -- This third select gets the OPM Batch Material Transactions
         -- based on the BIP Material Account Summary Report
         -- ==========================================================
         select xah.ledger_id,
                mp.organization_code,
                mp.organization_id,
                oap.period_name,
                xal.code_combination_id,
                oap.schedule_close_date,
                gbh.laboratory_ind, -- Batch Type
                gbh.batch_no,
                gbh.batch_id,
                gbh.batch_status,
                ffmv.formula_no,
                ffmv.formula_vers,
                frh.routing_no,
                frh.routing_vers,
                gr.recipe_no,
                gr.recipe_version,
                gbh.creation_date,
                we.primary_item_id inventory_item_id,
                gbh.plan_start_date,
                gbh.actual_start_date,
                gbh.due_date,
                gbh.actual_cmplt_date,
                gbh.batch_close_date,
                xal.accounting_class_code,
                gxeh.event_class_code,
                gxeh.transaction_type_id,
                decode(mmt.transaction_action_id,
                                31, 0,
                                32, 0,
                                sum(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0))
                      ) wip_costs_in,
                -- Revision, change SIGN of Costs Out to match Oracle (Discrete) WIP Value Report
                decode(mmt.transaction_action_id,
                                31, sum(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)),
                                32, sum(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)),
                                sum(0)
                      ) * -1 wip_costs_out,
                sum(0) wip_relief,
                sum(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)) wip_value
         from   mtl_material_transactions mmt,
                gme_batch_header gbh,
                fm_form_mst_vl ffmv,
                fm_rout_hdr frh,
                gmd_recipes gr,
                gmd_recipe_validity_rules grvr, 
                gmf_xla_extract_headers gxeh,
                org_acct_periods oap,
                -- Revision, show Product not Ingredient
                wip_entities we,
                xla_ae_headers xah,
                xla_ae_lines xal,
                gmf_fiscal_policies gfp,
                mtl_parameters mp
         where  mmt.transaction_id              = gxeh.transaction_id
         and    mmt.transaction_source_type_id  = 5
         and    we.wip_entity_id                = gbh.batch_id
         and    gxeh.organization_id            = mp.organization_id
         and    mmt.transaction_source_id       = gbh.batch_id
         and    mmt.organization_id             = mp.organization_id
         and    gbh.formula_id                  = ffmv.formula_id (+)  
         and    gbh.routing_id                  = frh.routing_id (+)
         and    gbh.recipe_validity_rule_id     = grvr.recipe_validity_rule_id (+)
         and    grvr.recipe_id                  = gr.recipe_id (+)
         and    gxeh.event_id                   = xah.event_id
         and    gxeh.txn_source                 = 'PM'
         and    xal.accounting_class_code       = 'WIP_VALUATION'
         and    mp.process_enabled_flag         = 'Y'
         -- ===========================================
         -- Limit to just WIP Batch Material
         -- ===========================================
         and    gxeh.entity_code                = 'PRODUCTION'
         and    gxeh.event_class_code           = 'BATCH_MATERIAL'
         -- ===========================================
         -- Inventory accounting period joins to limit
         -- to wip activity within the accounting period.
         -- ===========================================
         and    oap.period_name                 = xah.period_name
         and    oap.organization_id             = mp.organization_id
         -- Limit to jobs closed after the period start date
         and    nvl(trunc(gbh.batch_close_date), oap.period_start_date) >= oap.period_start_date
         -- ===========================================
         -- Only get entries with go to the G/L
         -- ===========================================
         and    gfp.ledger_id                   = xah.ledger_id
         and    gxeh.valuation_cost_type_id     = gfp.cost_type_id
         and    gfp.legal_entity_id             = (select to_number(hoi.org_information2) 
                                                   from   hr_organization_information hoi
                                                   where  hoi.org_information_context     = 'Accounting Information'
                                                   and    hoi.organization_id             = mp.organization_id)
         -- ===========================================
         -- Subledger Accounting joins
         -- ===========================================
         and    xah.ae_header_id                = xal.ae_header_id
         and    xah.ledger_id                   = xal.ledger_id
         and    xah.application_id              = 555
         and    xah.application_id              = xal.application_id
         and    2=2                             -- p_org_code, p_period_name, p_include_lab_batches
         group by
                xah.ledger_id,
                mp.organization_code,
                mp.organization_id,
                oap.period_name,
                xal.code_combination_id,
                oap.schedule_close_date,
                xal.code_combination_id,
                gbh.laboratory_ind, -- Batch Type
                gbh.batch_no,
                gbh.batch_id,
                gbh.batch_status,
                ffmv.formula_no,
                ffmv.formula_vers,
                frh.routing_no,
                frh.routing_vers,
                gr.recipe_no,
                gr.recipe_version,
                gbh.creation_date,
                we.primary_item_id, -- inventory_item_id
                gbh.plan_start_date,
                gbh.actual_start_date,
                gbh.due_date,
                gbh.actual_cmplt_date,
                xal.accounting_class_code,
                gbh.batch_close_date,
                gxeh.event_class_code,
                gxeh.transaction_type_id,
                mmt.transaction_action_id
        ) wip
where   msiv.inventory_item_id          = wip.inventory_item_id
and     msiv.organization_id            = wip.organization_id
and     msiv.primary_uom_code           = muomv.uom_code
-- ===========================================
-- Accounts (CCID) join
-- ===========================================
-- Outer join in case Create Accounting fails
and     gcc.code_combination_id(+)      = wip.code_combination_id
-- ===========================================
-- Lookup Codes
-- ===========================================
and     xl.lookup_code (+)              = wip.accounting_class_code
and     xl.lookup_type (+)              = 'XLA_ACCOUNTING_CLASS'
and     fcl.lookup_type (+)             = 'ITEM_TYPE'
and     fcl.lookup_code (+)             = msiv.item_type
and     gem_l1.lookup_type (+)          = 'GME_BATCH_STATUS'
and     gem_l1.lookup_code (+)          = wip.batch_status
and     gem_l2.lookup_type (+)          = 'GME_LABORATORY_IND'
and     gem_l2.lookup_code (+)          = wip.laboratory_ind
-- ===========================================
-- Organization joins
-- ===========================================
and     hoi.org_information_context     = 'Accounting Information'
and     hoi.organization_id             = wip.organization_id
and     hoi.organization_id             = haou.organization_id
and     haou2.organization_id           = to_number(hoi.org_information3)
and     gl.ledger_id                    = to_number(hoi.org_information1)
and     1=1                             -- p_operating_unit, p_ledger
group by
        gl.name,
        haou2.name,
        wip.organization_code,
        wip.period_name,
        &segment_columns_grp
        gem_l1.meaning, -- Batch Type
        wip.batch_no,
        gem_l2.meaning, -- Batch Status
        wip.formula_no,
        wip.formula_vers,
        wip.routing_no,
        wip.routing_vers,
        wip.recipe_no,
        wip.recipe_version,
        wip.creation_date,
        wip.plan_start_date,
        wip.actual_start_date,
        wip.due_date,
        wip.actual_cmplt_date,
        wip.batch_close_date,
        case 
           when (wip.schedule_close_date - wip.creation_date) < 31  then '30 days'
           when (wip.schedule_close_date - wip.creation_date) < 61  then '60 days'
           when (wip.schedule_close_date - wip.creation_date) < 91  then '90 days'
           when (wip.schedule_close_date - wip.creation_date) < 121 then '120 days'
           when (wip.schedule_close_date - wip.creation_date) < 151 then '150 days'
           when (wip.schedule_close_date - wip.creation_date) < 181 then '180 days'
           else 'Over 180 days'
        end, -- Aged_Creation_Date
        case 
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 31  then '30 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 61  then '60 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 91  then '90 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 121 then '120 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 151 then '150 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - nvl(wip.actual_start_date,(nvl(wip.actual_cmplt_date,sysdate)))) < 181 then '180 days'
           else 'Over 180 days'
        end, -- Aged_Compln_vs_Release_Date
        case 
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 31  then '30 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 61  then '60 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 91  then '90 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 121 then '120 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 151 then '150 days'
           when (nvl(wip.actual_cmplt_date, wip.schedule_close_date) - wip.creation_date) < 181 then '180 days'
           else 'Over 180 days'
        end, -- Aged_Compln_vs_Creation_Date
        msiv.concatenated_segments, -- Product Number
        msiv.description,
        muomv.uom_code,
        fcl.meaning, -- Item Type
        xl.meaning, -- Accounting Class
        wip.batch_close_date,
        wip.accounting_class_code,
        gl.currency_code,
        -- added for inline select
        msiv.inventory_item_id,
        msiv.organization_id
-- Order by Ledger, Operating Unit, Org Code, Period Name, Accounts, Item and Batch
order by 1,2,3,4,5,6,7,8,9,10,11,12,14,21
Parameter Name SQL text Validation
Period Name
oap.period_name = :p_period_name
LOV
Include Lab Batches
nvl(gbh.laboratory_ind,0)       = decode(:p_include_lab_batches, 'Y', gbh.laboratory_ind, 'N', 0)
LOV Oracle
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
Organization Code
mp.organization_code = :p_org_code
LOV
Operating Unit
haou2.name = :p_operating_unit
LOV
Ledger
gl.name = :p_ledger
LOV
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: