MSC Horizontal Plan
Description
Categories: Enginatics
Repository: Github
Repository: Github
ASCP: Horizontal Plan from the Planners Workbench.
Note:
The number of Items included in the HP is restricted by the parameter ‘Item Restriction Limit’. This parameter defaults from the profile option ‘MSC: HP Maximum Displayed Item Count’ in the ASCP Planning Instance. The value set in the Item Restriction Limit parameter will override the value specified in profile option.
... more
Note:
The number of Items included in the HP is restricted by the parameter ‘Item Restriction Limit’. This parameter defaults from the profile option ‘MSC: HP Maximum Displayed Item Count’ in the ASCP Planning Instance. The value set in the Item Restriction Limit parameter will override the value specified in profile option.
... more
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 -- 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 |