APL Lot Cost Trend

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,
ood.organization_code mfg_unit,
ood.organization_name warehouse,
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 "FP Code",
msiv1.concatenated_segments product_code,
msiv1.description product,
case when oblc.step_level = 0 then gmd1.plan_qty else null end batch_size,
case when oblc.step_level = 0 then gmd1.actual_qty else null end batch_actual_output,
case when oblc.step_level = 0 then gmd1.plan_qty else null end "Batch Size Qty in Nos",
case when oblc.step_level = 0 then gmd1.actual_qty else null end "Actuals Output Qty in Nos",
case when oblc.step_level = 0 and nvl((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 = 'PACK SIZE'),0) > 0
then trunc(gmd1.actual_qty/(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 = 'PACK SIZE'),:p_precision)
else null
end batch_pack_count,
case when oblc.step_level = 0 then round(gmd1.actual_qty/gmd1.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,
(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 = 'SIZE') product_size,
(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 = 'REGION') country,
(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 = 'PACK STYLE') pack_style,
(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 = 'PACK TYPE') pack_type,
(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 = 'PACK SIZE') pack_size,
(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 = 'PACK SIZE') pack_count,
(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 = 'GENERIC NAME') generic_name,
(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 = 'BRAND NAME') brand_name,
(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 = 'STRENGTH') strength,
(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 = 'FORM') dosage_form,
-- ingredients
xxen_util.meaning(oblc.line_type,'GMD_FORMULA_ITEM_TYPE',552) type,
msiv2.concatenated_segments item,
msiv2.description item_desc,
-- lot
oblc.lot_number,
gbh2.batch_no lot_batch_no,
xxapl_batch_lot_costing_pkg.get_lot_source_name(gmd2.organization_id,gmd2.inventory_item_id,msiv2.concatenated_segments,oblc.lot_number) lot_source,
trunc(oblc.lot_cost,:p_precision) lot_cost,
trunc(oblc.scaled_lot_qty,:p_precision) lot_quantity,
trunc(trunc(oblc.lot_cost,:p_precision) * oblc.scaled_lot_qty,:p_precision) lot_extended_cost,
--
case when oblc.line_type = 1 then trunc(trunc(oblc.lot_cost,:p_precision) * oblc.scaled_lot_qty,: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) * oblc.scaled_lot_qty,: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) * oblc.scaled_lot_qty,:p_precision) else null end intermediate_ingredient_cost,
--
case when substr(msiv2.concatenated_segments,1,1) = 'C' and substr(msiv2.concatenated_segments,2,1) != '5' then trunc(trunc(oblc.lot_cost,:p_precision) * oblc.scaled_lot_qty,:p_precision) else null end "API Cost",
case when substr(msiv2.concatenated_segments,1,2) = 'C5' then trunc(trunc(oblc.lot_cost,:p_precision) * oblc.scaled_lot_qty,:p_precision) else null end "Excipient Cost",
case when substr(msiv2.concatenated_segments,1,1) = 'P' then trunc(trunc(oblc.lot_cost,:p_precision) * oblc.scaled_lot_qty,:p_precision) else null end "PM Cost",
case when oblc.step_level > 0 and substr(msiv2.concatenated_segments,1,1) in ('C','P') then trunc(trunc(oblc.lot_cost,:p_precision) * oblc.scaled_lot_qty,:p_precision) else null end total_material_cost,
case when substr(msiv2.concatenated_segments,1,1) in ('C','P','F') then to_number(null) else trunc(trunc(oblc.lot_cost,:p_precision) * oblc.scaled_lot_qty,:p_precision) end other_item_cost,
case when substr(msiv2.concatenated_segments,1,1) = 'F' then trunc(trunc(oblc.lot_cost,:p_precision) * oblc.scaled_lot_qty,:p_precision) else null end finished_product_cost,
--
oblc.ingredient_usage_factor,
oblc.primary_uom_code,
oblc.dtl_um detail_uom_code,
oblc.dtl_prim_uom_conv_rate,
xxen_util.meaning(oblc.uom_conv_error,'YES_NO',0) uom_conv_error,
xxen_util.meaning(decode(oblc.intermediate_ingred,'Y','Y',null),'YES_NO',0) intermediate_ingredient,
oblc.step_level "Level",
oblc.sort_order,
oblc.group_id,
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 gmd1,
gme_material_details gmd2,
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 = gmd1.batch_id and
oblc.top_item_id = gmd1.inventory_item_id and
gmd1.line_type = 1 and
oblc.batch_id = gbh2.batch_id and
oblc.material_detail_id = gmd2.material_detail_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
--
oblc.intermediate_ingred = 'N' and
1=1
order by
gbh1.batch_no,
oblc.sort_order