MSC Vertical Plan
Description
select vp.* from ( select :p_instance_code planning_instance, :p_plan_name plan, mvpv.organization_code, nvl2(mvpv.project_id,msc_get_name.project&a2m_dblink (mvpv.project_id,mvpv.organization_id,mvpv.plan_id,mvpv.sr_instance_id),null) project_number, nvl2(mvpv.task_id,msc_get_name.task&a2m_dblink (mvpv.task_id,mvpv.project_id,mvpv.organization_id,mvpv.plan_id,mvpv.sr_instance_id),null) task_number, -- custom attributes &lp_custom_attributes -- mvpv.item_segments item, -- item dff attributes &lp_item_dff_cols decode(mvpv.dummy_sort,1,'1. Onhand',decode(mvpv.source_flag,1,'2. Supply','3. Demand')) source, mvpv.order_type_text order_type, mvpv.order_number, trunc(mvpv.new_due_date) new_due_date, mvpv.quantity_rate quantity, sum(mvpv.quantity_rate) over (partition by mvpv.sr_instance_id,mvpv.plan_id,mvpv.organization_id,mvpv.inventory_item_id order by mvpv.new_due_date, mvpv.dummy_sort,mvpv.source_flag,mvpv.source_flag,mvpv.order_type,mvpv.order_number,mvpv.transaction_id ) cumulative_quantity, mvpv.description, -- mcs.category_set_name category_set, mic.category_name category, mvpv.part_condition, mvpv.probability, mvpv.product_family_name, mvpv.using_requirement_quantity, mvpv.transaction_id, row_number() over (partition by mvpv.sr_instance_id,mvpv.plan_id,mvpv.organization_id,mvpv.inventory_item_id order by mvpv.new_due_date, mvpv.dummy_sort,mvpv.source_flag,mvpv.source_flag,mvpv.order_type,mvpv.order_number,mvpv.transaction_id ) seq, case when trunc(mvpv.new_due_date) <= trunc(mp.curr_start_date)-1 then to_char(mp.curr_start_date-1,'YYYY') else to_char(mvpv.new_due_date,'YYYY') end year_char, 'P:'|| case when trunc(mvpv.new_due_date) <= trunc(mp.curr_start_date)-1 then '00' else ltrim(to_char(xxen_msc_horizplan.get_period_num(mvpv.plan_id,mvpv.new_due_date),'00')) || ' ' || (select cal.period_name from msc_period_start_dates&a2m_dblink cal, msc_trading_partners&a2m_dblink mtp where mtp.sr_tp_id = mvpv.organization_id and mtp.sr_instance_id = mvpv.sr_instance_id and mtp.partner_type = 3 and mtp.calendar_code = cal.calendar_code and mtp.sr_instance_id = cal.sr_instance_id and mtp.calendar_exception_set_id = cal.exception_set_id and trunc(mvpv.new_due_date) between cal.period_start_date and cal.next_date-1 ) end period_char, 'W:'|| case when trunc(mvpv.new_due_date) <= trunc(mp.curr_start_date)-1 then '000' else ltrim(to_char(xxen_msc_horizplan.get_week_num(mvpv.plan_id,mvpv.new_due_date),'000')) || ' ' || (select to_char(cal.week_start_date,'DD-MON-YY') from msc_cal_week_start_dates&a2m_dblink cal, msc_trading_partners&a2m_dblink mtp where mtp.sr_tp_id = mvpv.organization_id and mtp.sr_instance_id = mvpv.sr_instance_id and mtp.partner_type = 3 and mtp.calendar_code = cal.calendar_code and mtp.sr_instance_id = cal.sr_instance_id and mtp.calendar_exception_set_id = cal.exception_set_id and trunc(mvpv.new_due_date) between cal.week_start_date and cal.next_date-1 ) end week_char, case when trunc(mvpv.new_due_date) <= trunc(mp.curr_start_date)-1 then trunc(mp.curr_start_date)-1 else trunc(mvpv.new_due_date) end bucket_date from msc_vertical_plan_v&a2m_dblink mvpv, msc_apps_instances&a2m_dblink mai, msc_plans&a2m_dblink mp, msc_system_items&a2m_dblink msi, msc_item_categories&a2m_dblink mic, msc_category_sets&a2m_dblink mcs where mvpv.sr_instance_id = mai.instance_id and mvpv.sr_instance_id = mp.sr_instance_id and mvpv.plan_id = mp.plan_id and mvpv.sr_instance_id = msi.sr_instance_id and mvpv.plan_id = msi.plan_id and mvpv.organization_id = msi.organization_id and mvpv.inventory_item_id = msi.inventory_item_id and mvpv.sr_instance_id = mic.sr_instance_id and mvpv.organization_id = mic.organization_id and mvpv.inventory_item_id = mic.inventory_item_id and mic.category_set_id = mcs.category_set_id and (mcs.category_set_name = :p_category_set_name or (:p_category_set_name is null and mcs.default_flag = 1) ) and ((mvpv.order_type <> 18) or (mvpv.order_type = 18 and mvpv.quantity_rate <> 0)) and 1=1 order by mvpv.organization_code, mvpv.item_segments, mvpv.dummy_sort, mvpv.new_due_date, mvpv.source_flag, mvpv.order_type, mvpv.order_number, mvpv.transaction_id ) vp |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Planning Instance |
|
LOV | |
Plan |
|
LOV | |
Organization |
|
LOV | |
Category Set |
|
LOV | |
Category |
|
LOV | |
Item |
|
LOV | |
Cutoff Date |
|
Date | |
Exclude Zero Quantity |
|
LOV | |
Show Item Descriptive Attributes |
|
LOV |