OPM Batch Lot Cost Trends

➜ Back to Library
Description
Categories: Enginatics, R12 only
Repository: Github
OPM Batch Lot Costing Trends Report

This report shows the Batch Lot Costs for the specified Organization and Product over a specified date range.

The report shows the Lot Cost for the batch product and explodes the batch to display all the lowest level ingredient lot costs involved in producing the batch.

Where an ingredient is sourced from another (child) batch, the (child) b ...  OPM Batch Lot Costing Trends Report

This report shows the Batch Lot Costs for the specified Organization and Product over a specified date range.

The report shows the Lot Cost for the batch product and explodes the batch to display all the lowest level ingredient lot costs involved in producing the batch.

Where an ingredient is sourced from another (child) batch, the (child) batch ingredient quantities are apportioned based on the actual usage of the ingredient in the batch consuming that ingredient.

By default, intermediate ingredients are not displayed in the report, so as not to overstate the ingredient lot costs. To override this default behaviour set the ‘Show Intermediate Ingredients’ report parameter to Yes

The report allows the user to pull in several additional data points to allow further analysis of data based on the customer specific configuration. Specifically, for the both the batch product (same for all lines in the batch) and for the batch ingredients (are specific to each line in the batch), the following additional data can be pulled into the report.

- Item Catalog Descriptive Elements for a specific Item Catalog Group
- Item Category Segments for a specific Item Category Set
- Item Descriptive Flexfield Attributes
   more 
with opm_batch_lot_costs as
(
select
x.top_batch_id,
x.top_item_id,
x.batch_id,
x.material_detail_id,
x.organization_id,
x.product_item_id,
x.inventory_item_id,
x.primary_uom_code,
x.dtl_um,
x.dtl_prim_uom_conv_rate,
x.plan_qty,
x.actual_qty,
x.scaled_actual_qty,
x.ingredient_usage_factor,
x.lot_number,
x.lot_cost * x.dtl_prim_uom_conv_rate lot_cost,
x.lot_qty,
x.scaled_lot_qty,
x.intermediate_ingred,
x.uom_conv_error,
x.line_type,
x.lot_source,
x.step_level,
x.sort_order,
x.group_id
from
xmltable
( '/BATCHLOTCOST/ROW'
  passing xxen_opm.get_report_xml
  columns
    top_batch_id            number          path 'TOP_BATCH_ID'
  , top_item_id             number          path 'TOP_ITEM_ID'
  , batch_id                number          path 'BATCH_ID'
  , material_detail_id      number          path 'MATERIAL_DETAIL_ID'
  , organization_id         number          path 'ORGANIZATION_ID'
  , product_item_id         number          path 'PRODUCT_ITEM_ID'
  , inventory_item_id       number          path 'INVENTORY_ITEM_ID'
  , primary_uom_code        varchar2(3)     path 'PRIMARY_UOM_CODE'
  , dtl_um                  varchar2(3)     path 'DTL_UM'
  , dtl_prim_uom_conv_rate  number          path 'DTL_PRIM_UOM_CONV_RATE'
  , plan_qty                number          path 'PLAN_QTY'
  , actual_qty              number          path 'ACTUAL_QTY'
  , scaled_actual_qty       number          path 'SCALED_ACTUAL_QTY'
  , ingredient_usage_factor number          path 'SCALING_FACTOR'
  , lot_number              varchar2(150)   path 'LOT_NUMBER'
  , lot_cost                number          path 'LOT_COST'
  , lot_qty                 number          path 'LOT_QTY'
  , scaled_lot_qty          number          path 'SCALED_LOT_QTY'
  , intermediate_ingred     varchar2(1)     path 'INTERMEDIATE_INGRED'
  , uom_conv_error          varchar2(1)     path 'UOM_CONV_ERROR'
  , line_type               number          path 'LINE_TYPE'
  , lot_source              varchar2(150)   path 'LOT_SOURCE'
  , step_level              number          path 'STEP_LEVEL'
  , sort_order              varchar2(2000)  path 'SORT_ORDER'
  , group_id                number          path 'GROUP_ID'
) x
)
--
--
select
ood.organization_code organization,
gbh1.batch_no,
(select gfc.formula_class_desc from fm_form_mst ffm,gmd_formula_class gfc where ffm.formula_class = gfc.formula_class and ffm.formula_id = gbh1.formula_id) batch_type,
gbh1.actual_cmplt_date batch_completion_date,
(select gllv.currency_code from gl_ledger_le_v gllv where gllv.ledger_id = ood.set_of_books_id) currency_code,
-- product
msiv1.concatenated_segments product,
msiv1.description product_desc,
xxen_util.meaning(msiv1.item_type,'ITEM_TYPE',3) product_item_type,
case when oblc.step_level = 0 then gmd.plan_qty else null end batch_size,
case when oblc.step_level = 0 then gmd.actual_qty else null end batch_actual_output,
case when oblc.step_level = 0 then round(gmd.actual_qty/gmd.plan_qty*100,2) else null end batch_yield,
case when oblc.step_level = 0 then trunc(trunc(oblc.lot_cost,:p_precision) * oblc.actual_qty,:p_precision) else null end batch_cost,
case when oblc.step_level = 0 then trunc(oblc.lot_cost,:p_precision) else null end batch_unit_cost,
-- ingredients
oblc.step_level "Level",
xxen_util.meaning(oblc.line_type,'GMD_FORMULA_ITEM_TYPE',552) type,
xxen_util.meaning(decode(oblc.intermediate_ingred,'Y','Y',null),'YES_NO',0) intermediate_ingredient,
msiv2.concatenated_segments item,
msiv2.description item_desc,
xxen_util.meaning(msiv2.item_type,'ITEM_TYPE',3) item_type,
-- lot
oblc.lot_number,
trunc(oblc.lot_cost,:p_precision) lot_cost,
trunc(oblc.scaled_lot_qty,:p_precision) lot_quantity,
trunc(trunc(oblc.lot_cost,:p_precision) * trunc(oblc.scaled_lot_qty,:p_precision),:p_precision) lot_extended_cost,
gbh2.batch_no line_batch_no,
oblc.lot_source,
oblc.ingredient_usage_factor,
oblc.dtl_um detail_uom,
oblc.primary_uom_code,
oblc.dtl_prim_uom_conv_rate,
xxen_util.meaning(oblc.uom_conv_error,'YES_NO',0) uom_conv_error,
case when oblc.line_type = 1  then trunc(trunc(oblc.lot_cost,:p_precision) * trunc(oblc.scaled_lot_qty,:p_precision),:p_precision) else null end product_cost,
case when oblc.intermediate_ingred = 'N' and oblc.step_level > 0 then trunc(trunc(oblc.lot_cost,:p_precision) * trunc(oblc.scaled_lot_qty,:p_precision),:p_precision) else null end ingredient_cost,
case when oblc.intermediate_ingred = 'Y' and oblc.step_level > 0 then trunc(trunc(oblc.lot_cost,:p_precision) * trunc(oblc.scaled_lot_qty,:p_precision),:p_precision) else null end intemediate_ingredient_cost,
--
&lp_prod_category_segments
&lp_prod_catalog_elements
&lp_prod_item_dff
&lp_ingred_category_segments
&lp_ingred_catalog_elements
&lp_ingred_item_dff
--
oblc.sort_order,
oblc.top_batch_id,
oblc.batch_id,
oblc.material_detail_id
from
opm_batch_lot_costs oblc,
gme_batch_header gbh1,
gme_batch_header gbh2,
gme_material_details gmd,
org_organization_definitions ood,
mtl_system_items_vl msiv1,
mtl_system_items_vl msiv2
where
oblc.top_batch_id = gbh1.batch_id and
oblc.top_batch_id = gmd.batch_id and
oblc.top_item_id = gmd.inventory_item_id and
gmd.line_type = 1 and
oblc.batch_id = gbh2.batch_id and
gbh1.organization_id = ood.organization_id and
oblc.organization_id = msiv1.organization_id and
oblc.top_item_id = msiv1.inventory_item_id and
oblc.organization_id = msiv2.organization_id and
oblc.inventory_item_id = msiv2.inventory_item_id and
--
-- only the top batch product line to be selected
(oblc.step_level = 0 or oblc.line_type = -1) and
--
(:p_show_interm_ingred = 'Y' or oblc.intermediate_ingred = 'N') and
1=1
order by
gbh1.batch_no,
oblc.sort_order
Parameter NameSQL textValidation
Organization Code
 
LOV
From Date
 
Date
To Date
 
Date
Product
 
LOV
Formula Class
 
LOV
Batch
 
LOV
Cost Type
 
LOV
Product Category Set
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 msiv1.organization_id=mic.organization_id and msiv1.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substrb('Prd: ' || fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv,
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
mcsv.category_set_name=:p_prod_cat_set_name 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
LOV
Product Item Catalog
select distinct
'(select mdev.element_value from mtl_descr_element_values_v mdev where mdev.inventory_item_id = msiv1.inventory_item_id and mdev.item_catalog_group_id = msiv1.item_catalog_group_id and mdev.element_name = ''' || mdv.element_name || ''')' ||
' "' || substrb('Prd: ' || mdv.element_name,1,xxen_report.max_column_length) || '",' value
from
mtl_descr_element_values_v mdv
where
mdv.item_catalog_group_id = :p_prod_catlg_grp_id
order by
value
LOV
Show Product Item DFF
select xxen_util.dff_columns(p_table_name=>'mtl_system_items_b',p_table_alias=>'msiv1', p_row_id=>'row_id',p_column_name_prefix=>'Prd: ') sql_text from dual
LOV
Ingredient Category Set
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 oblc.step_level > 0 and mic.category_set_id=' || mcsv.category_set_id || ' and msiv2.organization_id=mic.organization_id and msiv2.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substrb('Ing: ' || fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv,
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
mcsv.category_set_name=:p_ingred_cat_set_name 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
LOV
Ingredient Item Catalog
select distinct
'(select mdev.element_value from mtl_descr_element_values_v mdev where oblc.step_level > 0 and mdev.inventory_item_id = msiv2.inventory_item_id and mdev.item_catalog_group_id = msiv2.item_catalog_group_id and mdev.element_name = ''' || mdv.element_name || ''')' ||
' "' || substrb('Ing: ' || mdv.element_name,1,xxen_report.max_column_length) || '",' value
from
mtl_descr_element_values_v mdv
where
mdv.item_catalog_group_id = :p_ingred_catlg_grp_id
order by
value
LOV
Show Ingredient Item DFF
select xxen_util.dff_columns(p_table_name=>'mtl_system_items_b',p_table_alias=>'msiv2', p_row_id=>'row_id',p_column_name_prefix=>'Ing: ') sql_text from dual
LOV
Include Intermediate Ingredients
 
LOV
Download
Related Blitz Reports
APL Lot Cost Trend OPM Batch Lot Cost Details ECC Genealogy and Trace, Process Batch ECC Process Genealogy and Trace, Process Batch CAC OPM Batch Material Summary GME Material Usage Variance CAC OPM Costed Formula OPM2 GME Batch Ticket
Blitz Report™