with
mfg_sd_lookups as
( select
ml.lookup_code,
ml.meaning
from
msc_apps_instances&a2m_dblink mai,
msc_plans&a2m_dblink mp,
mfg_lookups&a2m_dblink ml
where
mp.sr_instance_id = mai.instance_id
and mp.compile_designator = :p_plan_name
and mai.instance_code = :p_instance_code
and (
( mp.plan_type != 4 -- ASCP Plan
and ml.lookup_type = 'MRP_HORIZONTAL_PLAN_TYPE_SC'
and ml.lookup_code not in (97,82,125,170,180,190)
and (( nvl(fnd_profile.value&a2m_dblink ('MSC_ASCP_IGNORE_CMRO_EAM_WO'),1) = 1
and ml.lookup_code not in (295,330,305,310,315,320,325,335,340,345,350)
)
or
(nvl(fnd_profile.value&a2m_dblink ('MSC_ASCP_IGNORE_CMRO_EAM_WO'),1) = 2
)
)
and (( fnd_profile.value&a2m_dblink ('MSC_HP_EXTENSION_PROGRAM') is null
and ml.lookup_code not in (500) -- custom user defined
)
or
(fnd_profile.value&a2m_dblink ('MSC_HP_EXTENSION_PROGRAM') is not null
)
)
and NVL(xxen_msc_horizplan.get_pref_option(mp.plan_type,:p_pref_name,ml.lookup_type,ml.lookup_code),'Y') = 'Y'
)
or
( mp.plan_type = 4 -- IO Plan
and ml.lookup_type = 'MSC_HORIZONTAL_PLAN_TYPE_IO'
and (( fnd_profile.value&a2m_dblink ('MSC_HP_EXTENSION_PROGRAM') is null
and ml.lookup_code not in (500) -- custom user defined
)
or
(fnd_profile.value&a2m_dblink ('MSC_HP_EXTENSION_PROGRAM') is not null
)
)
and NVL(xxen_msc_horizplan.get_pref_option(mp.plan_type,:p_pref_name,ml.lookup_type,ml.lookup_code),'Y') = 'Y'
)
)
union
select
900 lookup_code,
'PAB Days Stock Cover' meaning
from
dual&a2m_dblink
where
nvl(:p_metric,'Horizontal Plan') = 'PAB Days Stock Cover'
),
hp3 as
( select
hp2.planning_instance,
hp2.plan_name,
hp2.organization,
hp2.organization_name,
hp2.operating_unit,
hp2.item,
hp2.item_description,
hp2.category_set,
hp2.category,
hp2.item_uom,
hp2.report_uom,
hp2.uom_conversion,
hp2.make_buy "Make/Buy",
hp2.planner,
hp2.buyer,
hp2.dashboard_metric,
hp2.supply_demand_code,
hp2.supply_demand_type,
hp2.aggregation_level,
hp2.aggregation_label,
hp2.bucket_date,
hp2.pn period_num,
hp2.wn week_num,
hp2.dn day_num,
--
-- quantities
--
hp2.quantity,
--
case hp2.supply_demand_code
when 110 then hp2.week_qty_last
when 120 then hp2.week_qty_last
when 130 then hp2.week_qty_last
when 150 then hp2.week_qty_last
when 175 then hp2.week_qty_last
when 177 then hp2.week_qty_last
when 178 then hp2.week_qty_last
when 180 then hp2.week_qty_last
when 183 then hp2.week_qty_last
when 184 then hp2.week_qty_last
when 185 then hp2.week_qty_last
when 186 then hp2.week_qty_last
when 190 then hp2.week_qty_last
when 200 then hp2.week_qty_last
when 210 then hp2.week_qty_last
when 220 then hp2.week_qty_last
when 230 then hp2.week_qty_last
when 240 then hp2.week_qty_last
when 250 then hp2.week_qty_last
when 260 then hp2.week_qty_last
when 270 then hp2.week_qty_last
when 280 then hp2.week_qty_last
when 290 then hp2.week_qty_last
when 300 then hp2.week_qty_last
when 900 then hp2.week_qty_last
else hp2.week_qty_sum
end week_quantity,
--
case hp2.supply_demand_code
when 110 then hp2.period_qty_last
when 120 then hp2.period_qty_last
when 130 then hp2.period_qty_last
when 150 then hp2.period_qty_last
when 175 then hp2.period_qty_last
when 177 then hp2.period_qty_last
when 180 then hp2.period_qty_last
when 183 then hp2.period_qty_last
when 184 then hp2.period_qty_last
when 185 then hp2.period_qty_last
when 186 then hp2.period_qty_last
when 190 then hp2.period_qty_last
when 200 then hp2.period_qty_last
when 210 then hp2.period_qty_last
when 220 then hp2.period_qty_last
when 230 then hp2.period_qty_last
when 240 then hp2.period_qty_last
when 250 then hp2.period_qty_last
when 260 then hp2.period_qty_last
when 270 then hp2.period_qty_last
when 280 then hp2.period_qty_last
when 290 then hp2.period_qty_last
when 300 then hp2.period_qty_last
when 900 then hp2.period_qty_last
else hp2.period_qty_sum
end period_quantity,
--
case hp2.supply_demand_code
when 110 then hp2.year_qty_last
when 120 then hp2.year_qty_last
when 130 then hp2.year_qty_last
when 150 then hp2.year_qty_last
when 175 then hp2.year_qty_last
when 177 then hp2.year_qty_last
when 178 then hp2.year_qty_last
when 180 then hp2.year_qty_last
when 183 then hp2.year_qty_last
when 184 then hp2.year_qty_last
when 185 then hp2.year_qty_last
when 186 then hp2.year_qty_last
when 190 then hp2.year_qty_last
when 200 then hp2.year_qty_last
when 210 then hp2.year_qty_last
when 220 then hp2.year_qty_last
when 230 then hp2.year_qty_last
when 240 then hp2.year_qty_last
when 250 then hp2.year_qty_last
when 260 then hp2.year_qty_last
when 270 then hp2.year_qty_last
when 280 then hp2.year_qty_last
when 290 then hp2.year_qty_last
when 300 then hp2.year_qty_last
when 900 then hp2.year_qty_last
else hp2.year_qty_sum
end year_quantity,
--
-- values
--
hp2.standard_cost,
hp2.value,
--
case hp2.supply_demand_code
when 110 then hp2.week_value_last
when 120 then hp2.week_value_last
when 130 then hp2.week_value_last
when 150 then hp2.week_value_last
when 175 then hp2.week_value_last
when 177 then hp2.week_value_last
when 178 then hp2.week_value_last
when 180 then hp2.week_value_last
when 183 then hp2.week_value_last
when 184 then hp2.week_value_last
when 185 then hp2.week_value_last
when 186 then hp2.week_value_last
when 190 then hp2.week_value_last
when 200 then hp2.week_value_last
when 210 then hp2.week_value_last
when 220 then hp2.week_value_last
when 230 then hp2.week_value_last
when 240 then hp2.week_value_last
when 250 then hp2.week_value_last
when 260 then hp2.week_value_last
when 270 then hp2.week_value_last
when 280 then hp2.week_value_last
when 290 then hp2.week_value_last
when 300 then hp2.week_value_last
when 900 then hp2.week_value_last
else hp2.week_value_sum
end week_value,
--
case hp2.supply_demand_code
when 110 then hp2.period_value_last
when 120 then hp2.period_value_last
when 130 then hp2.period_value_last
when 150 then hp2.period_value_last
when 175 then hp2.period_value_last
when 177 then hp2.period_value_last
when 178 then hp2.period_value_last
when 180 then hp2.period_value_last
when 183 then hp2.period_value_last
when 184 then hp2.period_value_last
when 185 then hp2.period_value_last
when 186 then hp2.period_value_last
when 190 then hp2.period_value_last
when 200 then hp2.period_value_last
when 210 then hp2.period_value_last
when 220 then hp2.period_value_last
when 230 then hp2.period_value_last
when 240 then hp2.period_value_last
when 250 then hp2.period_value_last
when 260 then hp2.period_value_last
when 270 then hp2.period_value_last
when 280 then hp2.period_value_last
when 290 then hp2.period_value_last
when 300 then hp2.period_value_last
when 900 then hp2.period_value_last
else hp2.period_value_sum
end period_value,
--
case hp2.supply_demand_code
when 110 then hp2.year_value_last
when 120 then hp2.year_value_last
when 130 then hp2.year_value_last
when 150 then hp2.year_value_last
when 175 then hp2.year_value_last
when 177 then hp2.year_value_last
when 178 then hp2.year_value_last
when 180 then hp2.year_value_last
when 183 then hp2.year_value_last
when 184 then hp2.year_value_last
when 185 then hp2.year_value_last
when 186 then hp2.year_value_last
when 190 then hp2.year_value_last
when 200 then hp2.year_value_last
when 210 then hp2.year_value_last
when 220 then hp2.year_value_last
when 230 then hp2.year_value_last
when 240 then hp2.year_value_last
when 250 then hp2.year_value_last
when 260 then hp2.year_value_last
when 270 then hp2.year_value_last
when 280 then hp2.year_value_last
when 290 then hp2.year_value_last
when 300 then hp2.year_value_last
when 900 then hp2.year_value_last
else hp2.year_value_sum
end year_value,
--
--
hp2.using_assemblies,
-- item dff attributes
&lp_item_dff_cols
--
--
ltrim(to_char(hp2.supply_demand_code,'000')) || ' ' || hp2.supply_demand_type supply_demand_label,
--
case substr(:p_summary_level,1,1)
when 'A'
then case
when hp2.wn < xxen_msc_horizplan.get_week_bucket_cutoff_wn
then to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn || ' W:' || hp2.wn || ' D:' || hp2.dn
when hp2.wn >= xxen_msc_horizplan.get_week_bucket_cutoff_wn
and hp2.wn < xxen_msc_horizplan.get_period_bucket_cutoff_wn
then to_char(hp2.bucket_date,'YYYY/MM/DD') || ' W:' || hp2.wn || ' P:' || hp2.pn
else to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn
end
when 'P' then to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn
when 'W' then to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn || ' W:' || hp2.wn
else to_char(hp2.bucket_date,'YYYY/MM/DD') || ' P:' || hp2.pn || ' W:' || hp2.wn || ' D:' || hp2.dn
end bucket_label,
--
to_char(hp2.bucket_date,'YYYY') year_chart_label,
'P:' || ltrim(to_char(hp2.pn,'000')) || ' ' || to_char(hp2.period_date,'DD-MON-YY') period_chart_label,
case substr(:p_summary_level,1,1)
when 'A'
then case when hp2.wn >= xxen_msc_horizplan.get_period_bucket_cutoff_wn
then 'P:' || ltrim(to_char(hp2.pn,'000')) || ' ' || to_char(hp2.period_date,'DD-MON-YY')
else 'W:' || ltrim(to_char(hp2.wn,'000')) || ' ' || to_char(hp2.week_date,'DD-MON-YY')
end
else 'W:' || ltrim(to_char(hp2.wn,'000')) || ' ' || to_char(hp2.week_date,'DD-MON-YY')
end week_chart_label,
case substr(:p_summary_level,1,1)
when 'A'
then case
when hp2.wn < xxen_msc_horizplan.get_week_bucket_cutoff_wn
then 'D:' || ltrim(to_char(hp2.dn,'000')) || ' ' || to_char(hp2.bucket_date,'DD-MON-YY')
when hp2.wn >= xxen_msc_horizplan.get_week_bucket_cutoff_wn
and hp2.wn < xxen_msc_horizplan.get_period_bucket_cutoff_wn
then 'W:' || ltrim(to_char(hp2.wn,'000')) || ' ' || to_char(hp2.week_date,'DD-MON-YY')
else 'P:' || ltrim(to_char(hp2.pn,'000')) || ' ' || to_char(hp2.period_date,'DD-MON-YY')
end
else 'D:' || ltrim(to_char(hp2.dn,'000')) || ' ' || to_char(hp2.bucket_date,'DD-MON-YY')
end day_chart_label,
--
row_number() over (partition by hp2.planning_instance,hp2.plan_name,hp2.aggregation_level,hp2.dashboard_metric,hp2.aggregation_level,hp2.operating_unit,hp2.organization,hp2.category,hp2.item,hp2.supply_demand_code,to_char(hp2.bucket_date,'YYYY') order by hp2.bucket_date) yn_seq,
row_number() over (partition by hp2.planning_instance,hp2.plan_name,hp2.aggregation_level,hp2.dashboard_metric,hp2.aggregation_level,hp2.operating_unit,hp2.organization,hp2.category,hp2.item,hp2.supply_demand_code,hp2.pn order by hp2.bucket_date) pn_seq,
row_number() over (partition by hp2.planning_instance,hp2.plan_name,hp2.aggregation_level,hp2.dashboard_metric,hp2.aggregation_level,hp2.operating_unit,hp2.organization,hp2.category,hp2.item,hp2.supply_demand_code,hp2.wn order by hp2.bucket_date) wn_seq
from
(select
hp.planning_instance
,hp.plan_name
,hp.organization
,hp.organization_name
,hp.operating_unit
,hp.item
,hp.item_description
,hp.category_set
,hp.category
,hp.item_uom
,hp.report_uom
,hp.uom_conversion
,hp.make_buy
,hp.planner
,hp.buyer
,hp.standard_cost
,hp.using_assemblies
,hp.dashboard_metric
,hp.supply_demand_code
,hp.supply_demand_type
,hp.aggregation_level
,case hp.horizontal_plan_type_text
when 'IU' then hp.operating_unit || ' - ' || hp.item
when 'IA' then hp.item
when 'CO' then replace(hp.organization,hp.planning_instance || ':','') || ' - ' || hp.category
when 'CU' then hp.operating_unit || ' - ' || hp.category
when 'CA' then hp.category
else replace(hp.organization,hp.planning_instance || ':','') || ' - ' || hp.item
end aggregation_label
,hp.bucket_date
--
,hp.pn
,hp.wn
,hp.dn
,first_value(hp.bucket_date) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.pn order by hp.bucket_date range between unbounded preceding and unbounded following) period_date
,first_value(hp.bucket_date) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn order by hp.bucket_date range between unbounded preceding and unbounded following) week_date
--
,hp.quantity
,last_value(hp.quantity) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.pn order by hp.bucket_date range between unbounded preceding and unbounded following) period_qty_last
,last_value(hp.quantity) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn order by hp.bucket_date range between unbounded preceding and unbounded following) week_qty_last
,last_value(hp.quantity) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,to_char(hp.bucket_date,'YYYY') order by hp.bucket_date range between unbounded preceding and unbounded following) year_qty_last
,sum(hp.quantity) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.pn) period_qty_sum
,sum(hp.quantity) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn) week_qty_sum
,sum(hp.quantity) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,to_char(hp.bucket_date,'YYYY')) year_qty_sum
--
,hp.value
,last_value(hp.value) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.pn order by hp.bucket_date range between unbounded preceding and unbounded following) period_value_last
,last_value(hp.value) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn order by hp.bucket_date range between unbounded preceding and unbounded following) week_value_last
,last_value(hp.value) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,to_char(hp.bucket_date,'YYYY') order by hp.bucket_date range between unbounded preceding and unbounded following) year_value_last
,sum(hp.value) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.pn) period_value_sum
,sum(hp.value) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,hp.wn) week_value_sum
,sum(hp.value) over (partition by hp.planning_instance,hp.plan_name,hp.aggregation_level,hp.operating_unit,hp.organization,hp.category,hp.item,hp.supply_demand_code,to_char(hp.bucket_date,'YYYY')) year_value_sum
--
,hp.sr_instance_id
,hp.plan_id
,hp.organization_id
,hp.inventory_item_id
,hp.sr_inventory_item_id
from
(select /*+ ordered */
-- hp organization id would be -1 for all org aggregation, however not using this opton in this code as we always pass the list of Org IDs even for all orgs
-- hp inventory item id will be negative for product family details when display product family details = True
mai.instance_code planning_instance
,mp.compile_designator plan_name
,case when mmp.horizontal_plan_type_text in ('IU','IA','CU','CA') then '(Aggregated)' else decode(mmp.organization_id,-1,'All Orgs',mpo.organization_code) end organization
,case when mmp.horizontal_plan_type_text in ('IU','IA','CU','CA') then null else nvl(mtp.partner_name,'All Orgs') end organization_name
,case when mmp.horizontal_plan_type_text not in ('IA','CA') then mtp.operating_unit_name else '(Aggregated)' end operating_unit
,case when mmp.horizontal_plan_type_text not in ('CO','CU','CA')
then case sign(mmp.inventory_item_id)
when -1 then
(select mfq.char2
from msc_form_query&a2m_dblink mfq
where mfq.query_id = abs(mmp.query_id)
and mfq.number4 = mmp.plan_id
and mfq.number3 = mmp.sr_instance_id
and mfq.number6 = mmp.organization_id
and mfq.number5 = mmp.inventory_item_id
and rownum <= 1
)
else msi.item_name
end
else '(Aggregated)'
end item
,case when mmp.horizontal_plan_type_text not in ('CO','CU','CA') then msi.description end item_description
,:p_cat_set_name category_set
,mic.category_name category
,case when mmp.horizontal_plan_type_text not in ('CO','CU','CA') then msi.uom_code end item_uom
,nvl(:p_uom_code,'Primary') report_uom
,nvl2(:p_uom_code,xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code),null) uom_conversion
,case when mmp.horizontal_plan_type_text not in ('CO','CU','CA') then msc_get_name.lookup_meaning&a2m_dblink ('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) end make_buy
,case when mmp.horizontal_plan_type_text not in ('CO','CU','CA') then msi.planner_code end planner
,case when mmp.horizontal_plan_type_text not in ('CO','CU','CA') then msi.buyer_name end buyer
,case when mmp.horizontal_plan_type_text not in ('IA','IU','CO','CU','CA') then msi.standard_cost end standard_cost
,case when mmp.horizontal_plan_type_text not in ('IA','IU','CO','CU','CA') then mmp.item_segments end using_assemblies
,:p_metric dashboard_metric
,ml.lookup_code supply_demand_code
,ml.meaning supply_demand_type
,case mmp.horizontal_plan_type_text
when 'IU' then 'Item: Operating Unit'
when 'IA' then 'Item: All Organizations'
when 'CO' then 'Category: Organization'
when 'CU' then 'Category: Operating Unit'
when 'CA' then 'Category: All Organizations'
else 'Item: Organization'
end aggregation_level
,substr(mmp.organization_code,1,instr(mmp.organization_code,'|',1,1)-1) pn
,substr(mmp.organization_code,instr(mmp.organization_code,'|',1,1)+1,length(mmp.organization_code)) wn
,mmp.bucket_date - (decode(mp.plan_id,-1,trunc(sysdate),trunc(mp.curr_start_date))-1) dn
,mmp.bucket_date bucket_date
-- Quantity
,round(
case ml.lookup_code
-- common to SC and IO Horizontal Plans
when 10 then mmp.quantity1 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 20 then mmp.quantity2 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 25 then mmp.quantity3 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 30 then mmp.quantity4 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 40 then mmp.quantity5 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 45 then mmp.quantity6 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 50 then mmp.quantity7 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 70 then mmp.quantity8 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 81 then mmp.quantity9 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 83 then mmp.quantity10 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 85 then mmp.quantity11 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 87 then mmp.quantity12 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 89 then mmp.quantity13 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 90 then mmp.quantity14 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 95 then mmp.quantity15 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 100 then mmp.quantity16 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 105 then mmp.quantity17 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 110 then mmp.quantity18 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 120 then mmp.quantity19 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 130 then mmp.quantity20 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 140 then mmp.quantity21 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 150 then mmp.quantity22 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 160 then mmp.quantity23 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
-- SC HP CMRO_EAM_WO specific
when 295 then mmp.quantity46 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 305 then mmp.quantity48 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 310 then mmp.quantity49 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 315 then mmp.quantity50 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 320 then mmp.quantity51 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 325 then mmp.quantity52 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 330 then mmp.quantity47 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 335 then mmp.quantity53 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 340 then mmp.quantity54 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 345 then mmp.quantity55 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 350 then mmp.quantity56 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
-- IO HP Specific
when 175 then mmp.quantity25 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 177 then mmp.quantity26 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 178 then mmp.quantity36 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 180 then mmp.quantity19 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 183 then mmp.quantity37
when 184 then mmp.quantity38
when 185 then mmp.quantity39
when 186 then mmp.quantity40
when 190 then mmp.quantity27
when 200 then mmp.quantity28
when 210 then mmp.quantity24 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 220 then mmp.quantity29
when 230 then mmp.quantity30
when 240 then mmp.quantity31 * xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code)
when 250 then mmp.quantity32
when 260 then mmp.quantity33
when 270 then mmp.quantity34
when 280 then mmp.quantity35
when 290 then mmp.quantity41
when 300 then mmp.quantity42
-- common user defined
when 500 then mmp.quantity45 -- this could be a qty or some other unit, so we won't uom convert this
-- PAB Days Stock Cover is captured in quantity 43
-- However for higher level aggreagations it needs to be recalculated across the aggregation level
when 900 then mmp.quantity43
end
, nvl(to_number('&p_decimal_places'),1)
) quantity
-- Value
,case ml.lookup_code
when 10 then mmp.quantity1 * nvl(msi.standard_cost,0)
when 20 then mmp.quantity2 * nvl(msi.standard_cost,0)
when 25 then mmp.quantity3 * nvl(msi.standard_cost,0)
when 30 then mmp.quantity4 * nvl(msi.standard_cost,0)
when 40 then mmp.quantity5 * nvl(msi.standard_cost,0)
when 45 then mmp.quantity6 * nvl(msi.standard_cost,0)
when 50 then mmp.quantity7 * nvl(msi.standard_cost,0)
when 70 then mmp.quantity8 * nvl(msi.standard_cost,0)
when 81 then mmp.quantity9 * nvl(msi.standard_cost,0)
when 83 then mmp.quantity10 * nvl(msi.standard_cost,0)
when 85 then mmp.quantity11 * nvl(msi.standard_cost,0)
when 87 then mmp.quantity12 * nvl(msi.standard_cost,0)
when 89 then mmp.quantity13 * nvl(msi.standard_cost,0)
when 90 then mmp.quantity14 * nvl(msi.standard_cost,0)
when 95 then mmp.quantity15 * nvl(msi.standard_cost,0)
when 100 then mmp.quantity16 * nvl(msi.standard_cost,0)
when 105 then mmp.quantity17 * nvl(msi.standard_cost,0)
when 110 then mmp.quantity18 * nvl(msi.standard_cost,0)
when 120 then mmp.quantity19 * nvl(msi.standard_cost,0)
when 130 then mmp.quantity20 * nvl(msi.standard_cost,0)
when 140 then mmp.quantity21 * nvl(msi.standard_cost,0)
when 150 then mmp.quantity22 * nvl(msi.standard_cost,0)
when 160 then mmp.quantity23 * nvl(msi.standard_cost,0)
-- SC HP CMRO_EAM_WO specific
when 295 then mmp.quantity46 * nvl(msi.standard_cost,0)
when 305 then mmp.quantity48 * nvl(msi.standard_cost,0)
when 310 then mmp.quantity49 * nvl(msi.standard_cost,0)
when 315 then mmp.quantity50 * nvl(msi.standard_cost,0)
when 320 then mmp.quantity51 * nvl(msi.standard_cost,0)
when 325 then mmp.quantity52 * nvl(msi.standard_cost,0)
when 330 then mmp.quantity47 * nvl(msi.standard_cost,0)
when 335 then mmp.quantity53 * nvl(msi.standard_cost,0)
when 340 then mmp.quantity54 * nvl(msi.standard_cost,0)
when 345 then mmp.quantity55 * nvl(msi.standard_cost,0)
when 350 then mmp.quantity56 * nvl(msi.standard_cost,0)
-- IO HP Specific
when 175 then mmp.quantity25 * nvl(msi.standard_cost,0)
when 177 then mmp.quantity26 * nvl(msi.standard_cost,0)
when 178 then mmp.quantity36 * nvl(msi.standard_cost,0)
when 180 then mmp.quantity19 * nvl(msi.standard_cost,0)
when 183 then mmp.quantity37
when 184 then mmp.quantity38
when 185 then mmp.quantity39
when 186 then mmp.quantity40
when 190 then mmp.quantity27
when 200 then mmp.quantity28
when 210 then mmp.quantity24 * nvl(msi.standard_cost,0)
when 220 then mmp.quantity29
when 230 then mmp.quantity30
when 240 then mmp.quantity31 * nvl(msi.standard_cost,0)
when 250 then mmp.quantity32
when 260 then mmp.quantity33
when 270 then mmp.quantity34
when 280 then mmp.quantity35
when 290 then mmp.quantity41
when 300 then mmp.quantity42
-- common user defined
when 500 then mmp.quantity45 -- this could be a qty or some other unit, so we won't include in std cost
-- PAB Days Stock Cover
when 900 then null -- No value for the Days Stock Cover metric
end value
--
,mmp.sr_instance_id
,mmp.plan_id
,case when mmp.horizontal_plan_type_text in ('IU','IA','CU','CA') then null else mmp.organization_id end organization_id
,case when mmp.horizontal_plan_type_text not in ('CO','CU','CA') then msi.inventory_item_id end inventory_item_id
,case when mmp.horizontal_plan_type_text not in ('CO','CU','CA') then msi.sr_inventory_item_id end sr_inventory_item_id
,case when mmp.horizontal_plan_type_text not in ('IA','CA') then mtp.operating_unit end operating_unit_id
,mmp.horizontal_plan_type_text
from
msc_material_plans&a2m_dblink mmp,
msc_apps_instances&a2m_dblink mai,
msc_plans&a2m_dblink mp,
msc_plan_organizations&a2m_dblink mpo,
msc_system_items&a2m_dblink msi,
msc_item_categories&a2m_dblink mic,
msc_trading_partners&a2m_dblink mtp,
mfg_sd_lookups ml
where
mmp.query_id = :p_query_id
and mmp.sr_instance_id = mai.instance_id
and mmp.sr_instance_id = mp.sr_instance_id
and mmp.plan_id = mp.plan_id
--
and mmp.sr_instance_id = mpo.sr_instance_id
and mmp.plan_id = mpo.plan_id
and mmp.organization_id = mpo.organization_id
--
and mmp.sr_instance_id = msi.sr_instance_id
and mmp.plan_id = msi.plan_id
and mmp.organization_id = msi.organization_id
and abs(mmp.inventory_item_id)
= msi.inventory_item_id
and msi.new_plan_id is null
and msi.simulation_set_id is null
--
and msi.sr_instance_id = mic.sr_instance_id
and msi.organization_id = mic.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and mic.category_set_id = :p_cat_set_id
--
and mtp.sr_instance_id (+) = mmp.sr_instance_id
and mtp.sr_tp_id (+) = mmp.organization_id
and mtp.partner_type (+) = 3
--
and mmp.bucket_date <= :p_cutoff_date
--
and ( ( nvl('&p_disp_pf','N') = 'Y'
and mmp.inventory_item_id > 0
and msi.bom_item_type in (2,5)
and ml.lookup_code in (20,70,90,100,110)
)
or
( nvl('&p_disp_pf','N') != 'Y'
or mmp.inventory_item_id < 0
or msi.bom_item_type not in (2,5)
)
)
--
and (:p_uom_code is null or xxen_msc_horizplan.get_uom_conversion(msi.organization_id,msi.inventory_item_id,:p_uom_code,msi.uom_code) is not null)
and 1=1
) hp
) hp2
)
select
hp3.*,
--
case when hp3.yn_seq = max(hp3.yn_seq) over (partition by hp3.plan_name,hp3.dashboard_metric,hp3.aggregation_level,hp3.operating_unit,hp3.organization,hp3.category,hp3.item,hp3.supply_demand_code,to_char(hp3.bucket_date,'YYYY')) then hp3.year_quantity else null end year_qty_last,
case when hp3.pn_seq = max(hp3.pn_seq) over (partition by hp3.plan_name,hp3.dashboard_metric,hp3.aggregation_level,hp3.operating_unit,hp3.organization,hp3.category,hp3.item,hp3.supply_demand_code,hp3.period_num) then hp3.period_quantity else null end period_qty_last,
case when hp3.wn_seq = max(hp3.wn_seq) over (partition by hp3.plan_name,hp3.dashboard_metric,hp3.aggregation_level,hp3.operating_unit,hp3.organization,hp3.category,hp3.item,hp3.supply_demand_code,hp3.week_num) then hp3.week_quantity else null end week_qty_last,
case when hp3.yn_seq = max(hp3.yn_seq) over (partition by hp3.plan_name,hp3.dashboard_metric,hp3.aggregation_level,hp3.operating_unit,hp3.organization,hp3.category,hp3.item,hp3.supply_demand_code,to_char(hp3.bucket_date,'YYYY')) then hp3.year_value else null end year_value_last,
case when hp3.pn_seq = max(hp3.pn_seq) over (partition by hp3.plan_name,hp3.dashboard_metric,hp3.aggregation_level,hp3.operating_unit,hp3.organization,hp3.category,hp3.item,hp3.supply_demand_code,hp3.period_num) then hp3.period_value else null end period_value_last,
case when hp3.wn_seq = max(hp3.wn_seq) over (partition by hp3.plan_name,hp3.dashboard_metric,hp3.aggregation_level,hp3.operating_unit,hp3.organization,hp3.category,hp3.item,hp3.supply_demand_code,hp3.week_num) then hp3.week_value else null end week_value_last
from
hp3
where
2=2
and hp3.aggregation_level = nvl(:p_aggregation_level,hp3.aggregation_level)
order by
hp3.planning_instance
,hp3.plan_name
,hp3.dashboard_metric
,hp3.aggregation_level desc
,hp3.operating_unit
,hp3.organization
,hp3.item
,hp3.bucket_date
,hp3.supply_demand_code |
