[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_Formula_Names(p_new_dp_id in number);
Procedure Parse_Dimension_Select_List(p_new_dp_id in number);
Procedure update_ascp_related_data(p_new_dp_id in number);
Procedure validate_doc_dim_selections(p_new_dp_id in number);
Procedure update_parameter_dates(p_demand_plan_id number);
select demand_plan_id
from msd_demand_plans
where plan_type = p_plan_type
and template_flag = 'Y'
and default_template = 'Y';
-- Update the Liability Specific Columns
update msd_demand_plans
set plan_start_date = p_plan_start_date,
plan_end_date = p_plan_end_date,
liab_plan_id = p_supply_plan_id,
liab_plan_name = p_supply_plan_name
where demand_plan_id = p_new_dp_id;
-- Update Dates of the input parameters
-- set the start date and end date for input parameters having time data
update msd_dp_parameters
set start_date = p_plan_start_date,
end_date = p_plan_end_date
where demand_plan_id = p_new_dp_id
and forecast_date_used is not null
and deleteable_flag = 'N';
update msd_dp_parameters
set parameter_name = p_supply_plan_name
where demand_plan_id = p_new_dp_id;
-- set the supply plan name to supply plan name for doc dim selection
update msd_dp_doc_dim_selections
set supply_plan_name = p_supply_plan_name
where demand_plan_id = p_new_dp_id
and selection_type = 'I';
update msd_dp_formula_parameters
set supply_plan_name = p_supply_plan_name
where demand_plan_id = p_new_dp_id
and parameter_type = 'I';
update msd_dp_formulas mdf
set upload_formula_id = (select formula_id from msd_dp_formulas mdf1
where mdf1.demand_plan_id = p_new_dp_id
and mdf1.formula_name = mdf.upload_formula_id)
where demand_plan_id = p_new_dp_id
and upload_formula_id is not null;
Calls refresh_document_dimensions, refresh_formulas, Parse_Dimension_Select_List,
Replace_formula_tokens, Replace_dimension_tokens, Validate_formula_parameters,
validate_formulas, validate_doc_dim_selections, validate_doc_dimensions
and validate_documents
********************************************************/
Procedure create_seeded_definitions(p_demand_plan_id in number,
p_errcode in out nocopy varchar2
)
is
cursor get_template_id is
select template_id
from msd_demand_plans
where demand_plan_id=p_demand_plan_id;
-- parse the selection script for dimensions with selection type as list
-- Parse_Dimension_Select_List(p_demand_plan_id); commented out as only one set of ascp specific measures needs to be added to a doc
-- validate dimension selections
validate_doc_dim_selections(p_demand_plan_id);
select template_id
from msd_demand_plans
where demand_plan_id = p_demand_plan_id;
select document_name,document_id
from msd_dp_seeded_documents
where demand_plan_id = p_demand_plan_id;
select document_id
from msd_dp_seeded_documents
where demand_plan_id = p_template_id
and document_name = p_document_name;
select dimension_code
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_demand_plan_id
and document_id = p_document_id;
select selection_script
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_template_id
and document_id = p_document_id
and dimension_code = p_dimension_code;
l_selection_script varchar2(4000);
select document_id
from msd_dp_seeded_documents
where demand_plan_id=p_demand_plan_id
and document_name = 'MSD_SD_EOL_LWF';
-- get the selection script for the corresponding dimension in template
open c5(l_template_id, l_document_id, c4_cur.dimension_code);
fetch c5 into l_selection_script;
-- update the selection script for the dimension in the plan with the selection script for the corresponding dimension in template
if c2_cur.document_id=waterfall_doc_id and c4_cur.dimension_code = 'MEAS' then
null;
update msd_dp_seeded_doc_dimensions
set selection_script = l_selection_script
where demand_plan_id = p_demand_plan_id
and document_id = c2_cur.document_id
and dimension_code = c4_cur.dimension_code;
select template_id
from msd_demand_plans
where demand_plan_id = p_demand_plan_id;
select formula_name,formula_id
from msd_dp_formulas
where demand_plan_id = p_demand_plan_id;
select equation, custom_field1, custom_field2, isby, numerator, denominator
from msd_dp_formulas
where demand_plan_id = p_template_id
and formula_name = p_formula_name;
-- update equation, custom_field1, isby, numerator and denominator for the formula in plan with the corresponding formula in template
update msd_dp_formulas
set equation = l_equation,
custom_field1 = l_custom_field1,
custom_field2 = l_custom_field2,
isby = l_isby,
numerator = l_numerator,
denominator = l_denominator
where demand_plan_id = p_demand_plan_id
and formula_name = c2_cur.formula_name;
select scenario_id, scenario_name, description
from msd_dp_scenarios_cs_v
where demand_plan_id = p_new_dp_id;
update msd_demand_plans
set template_flag = 'N',
default_template = 'N'
where demand_plan_id = p_new_dp_id;
update msd_dp_scenarios
set scenario_name = fnd_message.get
where demand_plan_id = p_new_dp_id
and scenario_id = c1_cur.scenario_id;
update msd_dp_scenarios
set description = fnd_message.get
where demand_plan_id = p_new_dp_id
and scenario_id = c1_cur.scenario_id;
update msd_dp_scenarios_tl
set description = fnd_message.get
where demand_plan_id = p_new_dp_id
and scenario_id = c1_cur.scenario_id;
select parameter_id
from msd_dp_parameters
where demand_plan_id=p_demand_plan_id
and stream_type='CALCULATED';
select parameter_sequence,parameter_type,parameter_component,parameter_value,supply_plan_name
from msd_dp_formula_parameters
where demand_plan_id=p_demand_plan_id
and formula_id=p_parameter_id
order by parameter_sequence;
select parameter_type,parameter_id
from msd_dp_parameters
where demand_plan_id=p_demand_plan_id;
select parameter_id
from msd_dp_parameters
where demand_plan_id=p_demand_plan_id
and post_calculation is not null;
update msd_dp_parameters
set equation = replace(equation,'%'||c2_rec.parameter_sequence||'%', l_parameter_value)
where demand_plan_id = p_demand_plan_id
and parameter_id = c1_rec.parameter_id;
update msd_dp_parameters
set post_calculation=replace(post_calculation,c3_rec.parameter_type,c3_rec.parameter_id)
where demand_plan_id=p_demand_plan_id
and parameter_id=c4_rec.parameter_id;
Procedure update_parameter_dates(p_demand_plan_id number)
is
cursor c1 is
select plan_type
from msd_demand_plans
where demand_plan_id=p_demand_plan_id;
update msd_dp_parameters
set start_date=msd_common_utilities.get_bucket_start_date(sysdate,1,6,'GREGORIAN'),
end_date=msd_common_utilities.get_bucket_end_date(sysdate,1,6,'GREGORIAN')
where parameter_type in ('MSD_ON_HAND')
and demand_plan_id=p_demand_plan_id;
update msd_dp_parameters
set start_date=msd_common_utilities.get_bucket_start_date(sysdate,1,6,'GREGORIAN'),
end_date=msd_common_utilities.get_bucket_end_date(sysdate,24,6,'GREGORIAN')
where parameter_type in ('MSD_GROSS_REQ_EXCESS_HORIZON','MSD_INTRANSIT','MSD_ONORDER',
'MSD_GROSS_REQ_OBS_DATE','MSD_TOTAL_SUPPLY','MSD_ORDER_FORECAST',
'MSD_FORECAST_BASIS_LIAB','MSD_AUTHORIZATION','MSD_SUPPLY_COMMIT',
'MSD_SHORTAGE','MSD_FORECAST_LIABILITY','MSD_ESTIMATED_FORECAST_LIAB',
'MSD_SIM_END_ITEM_DEMAND','MSD_ESTIMATED_GROSS_REQ')
and demand_plan_id=p_demand_plan_id;
end update_parameter_dates;
select plan_type
from msd_demand_plans
where demand_plan_id = p_new_dp_id;
-- insert ASCP specific data
update_ascp_related_data(p_new_dp_id);
-- Update the start and end dates for the parameters
update_parameter_dates(p_new_dp_id);
/*update msd_dp_parameters set allo_agg_basis_stream_id = (select parameter_id
from msd_dp_parameters
where demand_plan_id=p_new_dp_id
and parameter_type='MSD_FORECAST_BASIS_LIAB')
where demand_plan_id=p_new_dp_id
and parameter_type='MSD_SIM_END_ITEM_DEMAND'; */
update msd_dp_parameters
set dependent_demand_desc = fnd_message.get
where demand_plan_id=p_new_dp_id
and parameter_type='MSD_SIM_END_ITEM_DEMAND';
select parameter_id
from msd_dp_parameters
where demand_plan_id=p_new_dp_id
and parameter_type=p_parameter_type;
select scenario_id
from msd_dp_scenarios
where demand_plan_id=p_new_dp_id;
select associate_parameter
from msd_dp_scenarios
where demand_plan_id=p_new_dp_id
and scenario_id=p_scenario_id;
update msd_dp_scenarios
set associate_parameter=p_parameter_id
where demand_plan_id=p_new_dp_id
and scenario_id=c2_rec.scenario_id;
select plan_type
from msd_demand_plans
where demand_plan_id = p_new_dp_id;
update msd_dp_parameters
set start_date = msd_common_utilities.get_bucket_start_date(sysdate,-18,6,'GREGORIAN'),
end_date = msd_common_utilities.get_bucket_end_date(sysdate,1,6,'GREGORIAN')
where demand_plan_id = p_new_dp_id
and (supply_plan_flag <> 'Y'
or supply_plan_flag is null);
update msd_dp_scenarios
set history_start_date = msd_common_utilities.get_bucket_start_date(sysdate,-18,6,'GREGORIAN'),
history_end_date = msd_common_utilities.get_bucket_end_date(sysdate,1,6,'GREGORIAN'),
horizon_start_date = msd_common_utilities.get_bucket_start_date(sysdate,2,6,'GREGORIAN'),
horizon_end_date = msd_common_utilities.get_bucket_end_date(sysdate,19,6,'GREGORIAN')
where demand_plan_id = p_new_dp_id;
-- insert ASCP specific data
update_ascp_related_data(p_new_dp_id);
select liab_plan_name, plan_type, organization_id, sr_instance_id
from msd_demand_plans
where demand_plan_id = p_new_dp_id;
-- insert default manufacturing calendar for PDS base liability plans
if l_org_id <> -1 then
insert into msd_dp_calendars
(
DEMAND_PLAN_ID
,CALENDAR_TYPE
,CALENDAR_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,DELETEABLE_FLAG
,ENABLE_NONSEED_FLAG
)
values
(
p_new_dp_id
,2
,MSD_COMMON_UTILITIES_LB.get_default_mfg_cal ( l_org_id,l_instance_id)
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
,null
,'Y'
);
update msd_demand_plans
set m_min_tim_lvl_id = 1
where demand_plan_id = p_new_dp_id;
Calls Update_Formula_Names.
********************************************************/
Procedure common_all_post_process(p_new_dp_id in number)
is
cursor c2 is
select parameter_id, price_list_name
from msd_dp_parameters
where demand_plan_id = p_new_dp_id
and price_list_name is not null;
select scenario_id, price_list_name
from msd_dp_scenarios
where demand_plan_id = p_new_dp_id
and price_list_name is not null;
select dp_price_list_id, price_list_name
from msd_dp_price_lists
where demand_plan_id = p_new_dp_id;
update msd_dp_parameters
set price_list_name = fnd_message.get_string('MSD',c2_cur.price_list_name)
where demand_plan_id = p_new_dp_id
and parameter_id = c2_cur.parameter_id;
update msd_dp_scenarios
set price_list_name = fnd_message.get_string('MSD',c3_cur.price_list_name)
where demand_plan_id = p_new_dp_id
and scenario_id = c3_cur.scenario_id;
update msd_dp_price_lists
set price_list_name = fnd_message.get_string('MSD',c4_cur.price_list_name)
where demand_plan_id = p_new_dp_id
and dp_price_list_id = c4_cur.dp_price_list_id;
Update_Formula_Names(p_new_dp_id);
Procedure Update_Formula_Names(p_new_dp_id in number)
is
cursor c1 is
select formula_name, formula_desc, formula_id
from msd_dp_formulas
where demand_plan_id = p_new_dp_id
order by creation_sequence;
select document_id, description
from msd_dp_seeded_documents
where demand_plan_id = p_new_dp_id;
-- update the description seeded as message with message text
update msd_dp_formulas
--set formula_desc = fnd_message.get
set formula_desc = fnd_message.get_string('MSD',c1_cur.formula_desc)
where formula_id = c1_cur.formula_id
and demand_plan_id = p_new_dp_id;
-- update the description seeded as message with message text
update msd_dp_seeded_documents
--set description = fnd_message.get
set description = fnd_message.get_string('MSD',c2_cur.description)
where document_id = c2_cur.document_id
and demand_plan_id = p_new_dp_id;
END Update_Formula_Names;
select formula_id
from msd_dp_formulas
where demand_plan_id = p_new_dp_id
order by creation_sequence;
select where_used, parameter_sequence, parameter_type, parameter_component, parameter_value, supply_plan_name
from msd_dp_formula_parameters
where demand_plan_id = p_new_dp_id
and formula_id = p_formula_id
and enabled_flag = 'Y'
order by parameter_sequence;
-- update the names with IDS
update msd_dp_formulas
set custom_field1 = replace(custom_field1,'%'||c2_cur.parameter_sequence||'%', l_parameter_value),
custom_field2 = replace(custom_field2,'%'||c2_cur.parameter_sequence||'%', l_parameter_value),
equation = replace(equation,'%'||c2_cur.parameter_sequence||'%', l_parameter_value)
where demand_plan_id = p_new_dp_id
and formula_id = c1_cur.formula_id;
This Procedure parses the select list for dimensions with selection type as List. e.g. measures .
The list contains values seperated by '\n'
Called by create_seeded_definitions
NO MORE USED
********************************************************/
Procedure Parse_Dimension_Select_List(p_new_dp_id in number)
is
cursor c1 is
select document_id, dimension_code, selection_sequence
from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and (dimension_code, document_id) in
(select dimension_code, document_id from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_new_dp_id
and selection_type = 'L')
order by document_id,dimension_code,selection_sequence;
update msd_dp_seeded_doc_dimensions
set selection_script = ''
where demand_plan_id = p_new_dp_id
and selection_type = 'L';
update msd_dp_seeded_doc_dimensions
set selection_script = selection_script||'%'||c1_cur.selection_sequence||'%\n'
where demand_plan_id = p_new_dp_id
and document_id = c1_cur.document_id
and dimension_code = c1_cur.dimension_code
and selection_type = 'L';
END Parse_Dimension_Select_List;
This Procedure replaces the tokens in selection script with values of document selections.
Called by create_seeded_definitions.
********************************************************/
Procedure Replace_dimension_tokens(p_new_dp_id in number)
is
cursor c1 is
select document_id, dimension_code
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_new_dp_id
order by sequence_number;
select selection_sequence, selection_type, selection_component, selection_value, supply_plan_name
from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and document_id = p_document_id
and dimension_code = p_dimension_code
and enabled_flag = 'Y'
order by selection_sequence;
l_selection_value varchar2(4000);
if c2_cur.selection_type = 'I' then
l_parameter_id := get_parameter_id(p_new_dp_id,c2_cur.selection_value, c2_cur.supply_plan_name, c2_cur.selection_component);
l_selection_value := 'V.'||c2_cur.selection_component||l_parameter_id;
l_selection_value := null;
elsif c2_cur.selection_type = 'F' then
l_formula_id := get_formula_id(p_new_dp_id,c2_cur.selection_value, c2_cur.supply_plan_name);
l_selection_value := 'SYSF'||l_formula_id;
l_selection_value := null;
elsif c2_cur.selection_type = 'H' then
l_hierarchy_id := get_hierarchy_id(p_new_dp_id,c2_cur.selection_value);
l_selection_value := 'H'||c2_cur.selection_value;
l_selection_value := null;
elsif c2_cur.selection_type = 'L' then
l_level_id := get_level_id(p_new_dp_id,c2_cur.selection_value);
l_selection_value := 'L'||c2_cur.selection_value;
l_selection_value := null;
elsif c2_cur.selection_type = 'D' then
l_dimension_code := get_dimension_code(p_new_dp_id,c2_cur.selection_value);
l_selection_value := l_dimension_code;
elsif c2_cur.selection_type = 'DS' then
l_dimension_script := get_dimension_script(p_new_dp_id,c2_cur.selection_component, c2_cur.selection_value);
l_selection_value := l_dimension_script;
l_selection_value := c2_cur.selection_value;
update msd_dp_seeded_doc_dimensions
set selection_script = replace(selection_script,'%'||c2_cur.selection_sequence||'%', l_selection_value)
where demand_plan_id = p_new_dp_id
and document_id = c1_cur.document_id
and dimension_code = c1_cur.dimension_code;
/*update msd_dp_seeded_doc_dimensions
set enabled_flag = decode(nvl(selection_script,'NOT_POSS'), 'NOT_POSS', 'N', enabled_flag)
where document_id = c1_cur.document_id
and dimension_code = c1_cur.dimension_code
and demand_plan_id=p_new_dp_id
and dimension_code = 'MEAS';*/
Procedure update_ascp_related_data(p_new_dp_id in number)
is
cursor c1 is
select demand_plan_name
from msd_demand_plans
where demand_plan_id = p_new_dp_id;
select distinct supply_plan_id, supply_plan_name -- Bug 4729854
from msd_dp_supply_plans
where demand_plan_name = p_demand_plan_name;
select count(*)
from msd_dp_supply_plans
where demand_plan_name = p_demand_plan_name;
select plan_type
from msd_demand_plans
where demand_plan_id=p_new_dp_id;
select max(supply_plan_id) into l_liab_plan_id from msd_dp_supply_plans where demand_plan_name = l_demand_plan_name;
update msd_demand_plans set liab_plan_id=l_liab_plan_id where demand_plan_id=p_new_dp_id;
-- for each suuply plan selected in Template window
for c2_cur in c2(l_demand_plan_name) loop
if p_plan_type='EOL' then
add_ascp_scenario_for_eol(p_new_dp_id, c2_cur.supply_plan_id, c2_cur.supply_plan_name);
-- delete data as not required after this
delete from msd_dp_supply_plans
where demand_plan_name = l_demand_plan_name;
END update_ascp_related_data;
select distinct formula_id
from msd_dp_formula_parameters
where demand_plan_id = p_new_dp_id
and mandatory_flag = 'Y'
and enabled_flag = 'N';
update msd_dp_formulas
set valid_flag = 'Y'
where demand_plan_id = p_new_dp_id;
update msd_dp_formulas
set valid_flag = 'N'
where demand_plan_id = p_new_dp_id
and formula_id = c1_cur.formula_id;
Procedure validate_doc_dim_selections(p_new_dp_id in number)
is
cursor c1 is
select plan_type
from msd_demand_plans
where demand_plan_id = p_new_dp_id;
select distinct selection_value, dimension_code
from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and selection_type = 'L';
select distinct selection_value, dimension_code
from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and selection_type = 'H';
select level_id
from msd_dp_scenario_output_levels
where demand_plan_id = p_new_dp_id
and level_id in
(select level_id from msd_levels
where dimension_code = p_dimension_code
and nvl(plan_type,'DP') = decode(p_plan_type,null,'DP','SOP','DP','EOL','DP',p_plan_type))
and rownum < 2;
select hierarchy_id
from msd_dp_hierarchies
where demand_plan_id = p_new_dp_id
and hierarchy_id in
(select hierarchy_id from msd_hierarchies
where dimension_code = p_dimension_code
and nvl(plan_type,'DP') = decode(p_plan_type,null,'DP','SOP','DP','EOL','DP',p_plan_type))
and rownum < 2;
select count(*) into l_count
from msd_levels
where level_id = c2_cur.selection_value
and nvl(plan_type,'DP') = decode(l_plan_type,null,'DP','SOP','DP','EOL','DP',l_plan_type);
-- disable the selection
update msd_dp_doc_dim_selections
set enabled_flag = 'N'
where demand_plan_id = p_new_dp_id
and selection_type = 'L'
and selection_value = c2_cur.selection_value
and dimension_code = c2_cur.dimension_code;
update msd_dp_doc_dim_selections
set selection_value = l_level_id
where demand_plan_id = p_new_dp_id
and selection_type = 'L'
and selection_value = c2_cur.selection_value
and dimension_code = c2_cur.dimension_code;
select count(*) into l_count
from msd_hierarchies
where hierarchy_id = c3_cur.selection_value
and nvl(plan_type,'DP') = decode(l_plan_type,null,'DP','SOP','DP','EOL','DP',l_plan_type);
-- disable the selection
update msd_dp_doc_dim_selections
set enabled_flag = 'N'
where demand_plan_id = p_new_dp_id
and selection_type = 'H'
and selection_value = c3_cur.selection_value
and dimension_code = c3_cur.dimension_code;
update msd_dp_doc_dim_selections
set selection_value = l_hierarchy_id
where demand_plan_id = p_new_dp_id
and selection_type = 'H'
and selection_value = c3_cur.selection_value
and dimension_code = c3_cur.dimension_code;
END validate_doc_dim_selections;
This Procedure checks if any of mandatory selections for the dimension is disabled and enables the dimensions.
Called by create_seeded_definitions.
********************************************************/
Procedure validate_doc_dimensions(p_new_dp_id in number)
is
cursor c1 is
select distinct document_id, dimension_code
from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and mandatory_flag = 'Y'
and enabled_flag = 'N'
and dimension_code <> 'MEAS'
order by document_id, dimension_code;
select distinct document_id
from msd_dp_doc_dim_selections mdds,
msd_dp_parameters mdp
where mdds.demand_plan_id = p_new_dp_id
and mdp.demand_plan_id=p_new_dp_id
and mdp.parameter_type=mdds.selection_value
and nvl(mdp.parameter_name,'ABCD')=nvl(mdds.supply_plan_name,'ABCD')
and dimension_code = 'MEAS';
update msd_dp_seeded_doc_dimensions
set enabled_flag = 'Y'
where (document_id, dimension_code)
in
/*------Fix for bug 4550732--------*/
(select document_id, dimension_code from msd_dp_doc_dim_selections where demand_plan_id=p_new_dp_id)
and dimension_code <> 'MEAS';
update msd_dp_seeded_doc_dimensions dpdim
set enabled_flag = decode(dpdim.selection_type, 'S', 'Y', 'N')
where demand_plan_id = p_new_dp_id
and dimension_code = 'MEAS'
and document_id <> (select document_id from msd_dp_seeded_documents where demand_plan_id=p_new_dp_id and
document_name='MSD_EOL_WHEREUSED_RE');
-- disable dimensions except measure which have any of the mandatory selection disabled
update msd_dp_seeded_doc_dimensions
set enabled_flag = 'N'
where demand_plan_id = p_new_dp_id
and document_id = c1_cur.document_id
and dimension_code = c1_cur.dimension_code;
update msd_dp_seeded_doc_dimensions
set enabled_flag = 'Y'
where demand_plan_id = p_new_dp_id
and document_id = c2_cur.document_id
and dimension_code = 'MEAS';
select distinct document_id
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_new_dp_id
and mandatory_flag = 'Y'
and enabled_flag = 'N';
update msd_dp_seeded_documents
set valid_flag = 'Y'
where demand_plan_id = p_new_dp_id;
update msd_dp_seeded_documents
set valid_flag = 'N'
where demand_plan_id = p_new_dp_id
and document_id = c1_cur.document_id;
Called from form whenever user deletes a dimension.
********************************************************/
procedure remove_dimension(
p_demand_plan_id in number,
p_dimension_code in varchar2,
p_dp_dimension_code in varchar2)
is
BEGIN
savepoint sp;
update msd_dp_seeded_doc_dimensions
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and dimension_code = p_dp_dimension_code;
-- disable all dimension selections which use related hierarchies and levels
update msd_dp_doc_dim_selections
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and ((selection_type = 'H'
and selection_value in
(select distinct hierarchy_id from msd_hierarchies
where dimension_code = p_dp_dimension_code))
or (selection_type = 'L'
and selection_value in
(select distinct level_id from msd_levels
where dimension_code = p_dp_dimension_code)));
update msd_dp_formula_parameters
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and parameter_type = 'D'
and parameter_value = p_dp_dimension_code;
Called from form whenever user deletes an input parameter.
********************************************************/
procedure remove_parameter(
p_demand_plan_id in number,
p_parameter_id in number)
is
cursor c1 is
select parameter_type, parameter_name
from msd_dp_parameters
where demand_plan_id = p_demand_plan_id
and parameter_id = p_parameter_id;
-- disable doc dim selections
update msd_dp_doc_dim_selections
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and selection_value = l_parameter_type
and nvl(supply_plan_name,'~!#$%^&*') = nvl(l_parameter_name,'~!#$%^&*')
and selection_type = 'I';
update msd_dp_formula_parameters
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and parameter_type = 'I'
and nvl(supply_plan_name,'~!#$%^&*') = nvl(l_parameter_name,'~!#$%^&*')
and parameter_value = l_parameter_type;
Called from form whenever user deletes a scenario.
FOR FUTURE USE
********************************************************/
procedure remove_scenario(
p_demand_plan_id in number,
p_scenario_id in number)
is
cursor c1 is
select supply_plan_name, forecast_based_on, parameter_name
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id;
update msd_dp_formula_parameters
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and parameter_component = 'SN'
and parameter_value = l_forecast_based_on
and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
update msd_dp_doc_dim_selections
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and selection_component = 'SN'
and selection_value = l_forecast_based_on
and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
delete from msd_dp_parameters
where demand_plan_id = p_demand_plan_id
and parameter_name = l_supply_plan_name;
Called from form whenever user deletes a scenario output level.
********************************************************/
procedure remove_scenario_output_lvl(
p_demand_plan_id in number,
p_scenario_id in number,
p_level_id in number)
is
cursor c1 is
select enable_nonseed_flag
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id;
-- disable doc dim selections
update msd_dp_doc_dim_selections
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and selection_type = 'L'
and selection_value = p_level_id
and enabled_flag = 'Y';
Called from form whenever user deletes a scenario event.
FOR FUTURE USE
********************************************************/
procedure remove_event(
p_demand_plan_id in number,
p_dp_event_id in number)
is
BEGIN
null;
Called from form whenever user deletes a calendar.
FOR FUTURE USE
********************************************************/
procedure remove_calendar(
p_demand_plan_id in number,
p_calendar_type in varchar2,
p_calendar_code in varchar2)
is
BEGIN
null;
Called from form whenever user deletes a hierarchy.
********************************************************/
procedure remove_hierarchy(
p_demand_plan_id in number,
p_dp_dimension_code in varchar2,
p_hierarchy_id in number)
is
BEGIN
savepoint sp;
-- disable doc dim selections
update msd_dp_doc_dim_selections
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and selection_type = 'H'
and selection_value = p_hierarchy_id;
update msd_dp_formula_parameters
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and parameter_type = 'H'
and parameter_value = p_hierarchy_id;
update msd_dp_seeded_doc_dimensions
set enabled_flag = 'Y'
where demand_plan_id = p_Demand_plan_id
and dimension_code = p_dp_dimension_code
and enabled_flag = 'N';
-- enable document selections that use related hierarchies and levels
update msd_dp_doc_dim_selections
set enabled_flag = 'Y'
where demand_plan_id = p_Demand_plan_id
and ((selection_type = 'H'
and selection_value in
(select distinct hierarchy_id from msd_hierarchies
where dimension_code = p_dp_dimension_code))
or (selection_type = 'L'
and selection_value in
(select distinct level_id from msd_levels
where dimension_code = p_dp_dimension_code)));
update msd_dp_formula_parameters
set enabled_flag = 'Y'
where demand_plan_id = p_Demand_plan_id
and parameter_type = 'D'
and parameter_value = p_dp_dimension_code
and enabled_flag = 'N';
-- enable doc dim selections
update msd_dp_doc_dim_selections
set enabled_flag = 'Y'
where demand_plan_id = p_Demand_plan_id and selection_type = 'I'
and selection_value = p_parameter_type
and nvl(supply_plan_name,'~!#$%^&*') = nvl(p_parameter_name,'~!#$%^&*')
and enabled_flag = 'N';
update msd_dp_formula_parameters
set enabled_flag = 'Y'
where demand_plan_id = p_Demand_plan_id
and parameter_type = 'I'
and parameter_value = p_parameter_type
and nvl(supply_plan_name,'~!#$%^&*') = nvl(p_parameter_name,'~!#$%^&*')
and enabled_flag = 'N';
select supply_plan_name, forecast_based_on, parameter_name
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and scenario_name = p_scenario_name;
update msd_dp_formula_parameters
set enabled_flag = 'Y'
where demand_plan_id = p_demand_plan_id
and parameter_component = 'SN'
and parameter_value = l_forecast_based_on
and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
update msd_dp_doc_dim_selections
set enabled_flag = 'Y'
where demand_plan_id = p_demand_plan_id
and selection_component = 'SN'
and selection_value = l_forecast_based_on
and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
/*----Bug 4550732----If a dimension is added first time, it will be inserted into seeded dimensions----*/
cursor c1(p_document_id in number)is
select max(sequence_number)+1
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_demand_plan_id
and document_id = p_document_id
and axis = 'Z';
select distinct document_id
from msd_dp_seeded_documents
where demand_plan_id = p_demand_plan_id;
-- enable doc dim selections
update msd_dp_doc_dim_selections
set enabled_flag = 'Y'
where demand_plan_id = p_demand_plan_id
and selection_type = 'H'
and selection_value = p_hierarchy_id
and enabled_flag = 'N';
/*----Bug 4550732----If a dimension is added first time, it will be inserted into seeded dimensions----*/
l_dimension_code:=p_dp_dimension_code;
select count(*) into l_count
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_demand_plan_id
and dimension_code = l_dimension_code;
select count(*) into l_coll_dim
from msd_dp_dimensions
where demand_plan_id=p_demand_plan_id
and dimension_code = l_dimension_code
and dp_dimension_code=l_dimension_code;
insert into msd_dp_seeded_doc_dimensions
(
DEMAND_PLAN_ID
,DOCUMENT_ID
,DIMENSION_CODE
,SEQUENCE_NUMBER
,AXIS
,HIERARCHY_ID
,SELECTION_TYPE
,SELECTION_SCRIPT
,ENABLED_FLAG
,MANDATORY_FLAG
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LAST_UPDATE_DATE
)
VALUES
(
p_demand_plan_id
,c2_cur.document_id
,l_dimension_code
,l_sequence_number
,'Z'
,p_hierarchy_id
,'S'
,'limit '||l_dimension_code||' to '||l_dimension_code||'.L.REL eq 1'
,'Y'
,'N'
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
,SYSDATE
);
select enable_nonseed_flag
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id;
select dimension_code
from msd_levels
where level_id = p_level_id;
select distinct hierarchy_id
from msd_dp_hierarchies
where demand_plan_id = p_demand_plan_id
and dp_dimension_code = p_dimension_code;
select max(sequence_number)+1
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_demand_plan_id
and document_id = p_document_id
and axis = 'Z';
select distinct document_id
from msd_dp_seeded_documents
where demand_plan_id = p_demand_plan_id;
-- enable doc dim selections
update msd_dp_doc_dim_selections
set enabled_flag = 'Y'
where demand_plan_id = p_demand_plan_id
and selection_type = 'L'
and selection_value = p_level_id
and enabled_flag = 'N';
select count(*) into l_count
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_demand_plan_id
and dimension_code = l_dimension_code;
insert into msd_dp_seeded_doc_dimensions
(
DEMAND_PLAN_ID
,DOCUMENT_ID
,DIMENSION_CODE
,SEQUENCE_NUMBER
,AXIS
,HIERARCHY_ID
,SELECTION_TYPE
,SELECTION_SCRIPT
,ENABLED_FLAG
,MANDATORY_FLAG
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LAST_UPDATE_DATE
)
VALUES
(
p_demand_plan_id
,c5_cur.document_id
,l_dimension_code
,l_sequence_number
,'Z'
,l_hierarchy_id
,'S'
,'limit '||l_dimension_code||' to '||l_dimension_code||'.L.REL eq 1'
,'Y'
,'N'
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
,SYSDATE
);
select enable_nonseed_flag
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id;
select distinct document_id
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_demand_plan_id
and dimension_code = 'TIM'
and upper(selection_script) like 'CALL SL.LIMIT.ROLLTIM(%'||p_old_output_period_type_id||')';
select selection_value, selection_sequence
from msd_dp_doc_dim_selections
where demand_plan_id = p_demand_plan_id
and dimension_code = 'TIM'
and selection_sequence in (1,2)
and document_id = p_document_id;
l_new_selection_value number;
if c3_cur.selection_sequence = 1 then
l_start_date := msd_common_utilities.get_bucket_start_date(sysdate,c3_cur.selection_value,p_old_output_period_type_id,l_calendar_code);
l_new_selection_value := msd_common_utilities.get_age_in_buckets(l_start_date,sysdate,p_output_period_type_id,l_calendar_code);
elsif c3_cur.selection_sequence = 2 then
l_end_date := msd_common_utilities.get_bucket_end_date(sysdate,c3_cur.selection_value,p_old_output_period_type_id,l_calendar_code);
l_new_selection_value := msd_common_utilities.get_age_in_buckets(sysdate,l_end_date,p_output_period_type_id,l_calendar_code);
-- update with new values
update msd_dp_doc_dim_selections
set selection_value = l_new_selection_value*sign(c3_cur.selection_value)
where demand_plan_id = p_demand_plan_id
and document_id = c2_cur.document_id
and dimension_code = 'TIM'
and selection_type = 'TL'
and selection_value = c3_cur.selection_value
and selection_sequence = c3_cur.selection_sequence;
-- update the selection value with new data
update msd_dp_doc_dim_selections
set selection_value = p_output_period_type_id
where demand_plan_id = p_demand_plan_id
and selection_type = 'TL'
and selection_value = p_old_output_period_type_id;
select enable_nonseed_flag
from msd_dp_hierarchies
where demand_plan_id = p_demand_plan_id
and hierarchy_id = p_old_hierarchy_id;
update msd_dp_doc_dim_selections
set selection_value = p_hierarchy_id
where demand_plan_id = p_demand_plan_id
and selection_type = 'H'
and selection_value = p_old_hierarchy_id;
select enable_nonseed_flag
from msd_dp_scenario_output_levels
where demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id
and level_id = p_old_level_id;
update msd_dp_doc_dim_selections
set selection_value = p_level_id
where demand_plan_id = p_demand_plan_id
and selection_type = 'L'
and selection_value = p_old_level_id;
select enable_nonseed_flag
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id;
-- disable the doc dim selections
update msd_dp_doc_dim_selections
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and selection_component = 'SN'
and selection_value = p_old_stream_type;
update msd_dp_formula_parameters
set enabled_flag = 'N'
where demand_plan_id = p_demand_plan_id
and parameter_component = 'SN'
and parameter_value = p_old_stream_type;
-- change the doc dim selections
update msd_dp_doc_dim_selections
set selection_value = p_stream_type
where demand_plan_id = p_demand_plan_id
and selection_component = 'SN'
and selection_value = p_old_stream_type;
update msd_dp_formula_parameters
set parameter_value = p_stream_type
where demand_plan_id = p_demand_plan_id
and parameter_component = 'SN'
and parameter_value = p_old_stream_type;
-- enable the doc dim selections
update msd_dp_doc_dim_selections
set enabled_flag = 'Y'
where demand_plan_id = p_demand_plan_id
and selection_component = 'SN'
and selection_value = p_old_stream_type;
update msd_dp_formula_parameters
set enabled_flag = 'Y'
where demand_plan_id = p_demand_plan_id
and parameter_component = 'SN'
and parameter_value = p_old_stream_type;
select plan_type
from msd_demand_plans
where demand_plan_id=p_new_dp_id;
select template_id
from msd_demand_plans
where demand_plan_id=p_new_dp_id;
update msd_demand_plans
set liab_plan_id=p_supply_plan_id
where demand_plan_id=p_new_dp_id;
Called from update_ascp_related_data
********************************************************/
Procedure add_ascp_scenario(p_new_dp_id in number,p_supply_plan_id in number, p_supply_plan_name in varchar2) -- Bug 4729854
is
cursor c1 is
select count(*) from
msd_dp_scenarios
where demand_plan_id = p_new_dp_id
and supply_plan_flag = 'Y';
select scenario_name, description, demand_plan_id, scenario_id
from msd_dp_scenarios
where supply_plan_flag = 'Y'
and demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
and template_flag = 'Y'
and default_template = 'Y'
);
select msd_dp_scenarios_s.nextval into l_scenario_id from dual;
insert into msd_dp_scenarios
(demand_plan_id
,scenario_id
,scenario_name
,description
,output_period_type
,horizon_start_date
,horizon_end_date
,forecast_date_used
,forecast_based_on
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,scenario_type
,status
,history_start_date
,history_end_date
,publish_flag
,enable_flag
,price_list_name
,last_revision
,parameter_name
,consume_flag
,error_type
,supply_plan_id
,deleteable_flag
,supply_plan_flag
,supply_plan_name
,dmd_priority_scenario_id -- Bug 4710963
,scenario_designator
,associate_parameter
,sc_type)
(select
p_new_dp_id
,l_scenario_id
,l_name
,l_description
,output_period_type
,decode(p_type.plan_type, 'SOP', msd_common_utilities.get_bucket_start_date(sysdate,2,6,'GREGORIAN'), null)
,decode(p_type.plan_type, 'SOP', msd_common_utilities.get_bucket_end_date(sysdate,19,6,'GREGORIAN'), null)
,forecast_date_used
,forecast_based_on
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,scenario_type
,status
,decode(p_type.plan_type, 'SOP', msd_common_utilities.get_bucket_start_date(sysdate,-18,6,'GREGORIAN'), null)
,decode(p_type.plan_type, 'SOP', msd_common_utilities.get_bucket_end_date(sysdate,1,6,'GREGORIAN'), null)
,publish_flag
,enable_flag
,price_list_name
,last_revision
,parameter_name
,consume_flag
,error_type
,p_supply_plan_id
,deleteable_flag
,supply_plan_flag
,p_supply_plan_name -- Bug 4729854
,dmd_priority_scenario_id -- Bug 4710963
,scenario_designator
,associate_parameter
,sc_type
from msd_dp_scenarios,
(select nvl(plan_type,'DP') plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id) p_type
where scenario_id = c2_cur.scenario_id
and demand_plan_id = c2_cur.demand_plan_id);
insert into msd_dp_scenarios_tl
( demand_plan_id
,scenario_id
,description
,language
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
select
p_new_dp_id
,l_scenario_id
,l_description
,USERENV('LANG')
,USERENV('LANG')
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
from dual;
insert into msd_dp_scenario_events
(
demand_plan_id
,scenario_id
,event_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,event_association_priority)
(select
p_new_dp_id
,l_scenario_id
,event_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,event_association_priority
from msd_dp_scenario_events
where scenario_id = c2_cur.scenario_id
and demand_plan_id = c2_cur.demand_plan_id);
insert into msd_dp_scenario_output_levels
(
demand_plan_id
,scenario_id
,level_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date)
(select
p_new_dp_id
,l_scenario_id
,level_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
from msd_dp_scenario_output_levels
where scenario_id = c2_cur.scenario_id
and demand_plan_id = c2_cur.demand_plan_id);
Called from update_ascp_related_data, attach_supply_plan.
********************************************************/
Procedure add_ascp_input_parameter(p_new_dp_id in number,p_supply_plan_id in number,p_supply_plan_name in varchar2,
p_old_supply_plan_id in number default null, p_old_supply_plan_name in varchar2 default null) -- Bug 4729854
is
BEGIN
if p_old_supply_plan_id is not null then
-- change the parameters if change in ascp plan attahced
if p_supply_plan_id is not null then
update msd_dp_parameters
set parameter_name = p_supply_plan_name, -- Bug 4729854
--Bug 4549059
capacity_usage_ratio = decode(parameter_type,'MSD_SUPPLY_PLANS',p_supply_plan_name,
'MSD_SIM_END_ITEM_DEMAND',p_supply_plan_name,
capacity_usage_ratio) -- Bug 4729854
where demand_plan_id = p_new_dp_id
and parameter_name = p_old_supply_plan_name; -- Bug 4729854
-- delete if ascp plan is detached
else
delete from msd_dp_parameters
where demand_plan_id = p_new_dp_id
and parameter_name = p_old_supply_plan_name; -- Bug 4729854
insert into msd_dp_parameters
(
demand_plan_id
,parameter_id
,parameter_type
,parameter_name
,start_date
,end_date
,output_scenario_id
,input_scenario_id
,input_demand_plan_id
,forecast_date_used
,forecast_based_on
,quantity_used
,amount_used
,forecast_used
,period_type
,fact_type
,view_name
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,revision
,allo_agg_basis_stream_id
,number_of_period
,exclude_from_rolling_cycle
,scn_build_refresh_num
,rounding_flag
,deleteable_flag
,capacity_usage_ratio
,supply_plan_flag
, equation
,stream_type
,calculated_order
,post_calculation
,price_list_name)
(select
p_new_dp_id
,msd_dp_parameters_s.nextval
,parameter_type
,p_supply_plan_name -- Bug 4729854
,decode(p_type.plan_type, 'SOP', get_supply_plan_start_date(p_supply_plan_id), null)
,decode(p_type.plan_type, 'SOP', get_supply_plan_end_date(p_supply_plan_id), null)
,output_scenario_id
,input_scenario_id
,input_demand_plan_id
,forecast_date_used
,forecast_based_on
,quantity_used
,amount_used
,forecast_used
,period_type
,fact_type
,view_name
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
,revision
,allo_agg_basis_stream_id
,number_of_period
,exclude_from_rolling_cycle
,scn_build_refresh_num
,rounding_flag
,deleteable_flag
,decode(p_type.plan_type, 'SOP', decode(parameter_type,'MSD_SUPPLY_PLANS',p_supply_plan_name,capacity_usage_ratio)
, 'EOL', decode(parameter_type,'MSD_SIM_END_ITEM_DEMAND',p_supply_plan_name,capacity_usage_ratio)) -- Bug 4729854
,supply_plan_flag
,equation
,stream_type
,calculated_order
,post_calculation
,price_list_name
from msd_dp_parameters,
(select nvl(plan_type,'DP') plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id) p_type
where supply_plan_flag = 'Y'
and nvl(stream_type,'ABCD') not in ('ARCHIVED','ARCHIVED_TIM')
and demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = p_type.plan_type
and template_flag = 'Y'
and default_template = 'Y'
));
Called from update_ascp_related_data, attach_supply_plan.
********************************************************/
Procedure add_ascp_formula(p_new_dp_id in number,p_supply_plan_id in number,p_supply_plan_name in varchar2,
p_old_supply_plan_id in number default null,p_old_supply_plan_name in varchar2 default null) -- Bug 4729854
is
l_formula_id number;
select
formula_id
,creation_sequence
,formula_name
,formula_desc
,custom_type
,equation
,custom_field1
,custom_field2
,custom_subtype
,custom_addtlcalc
,isby
,valid_flag
,numerator
,denominator
,supply_plan_flag
,p_supply_plan_name -- Bug 4729854
,FORMAT /* ADDED NEW COLUMN FOR THE BUG#4373422 */
,START_PERIOD /* ADDED NEW COLUMN FOR THE BUG#4744717 */
from msd_dp_formulas
where demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
and template_flag = 'Y'
and default_template = 'Y'
)
and supply_plan_flag = 'Y';
select plan_type
from msd_demand_plans
where demand_plan_id=p_new_dp_id;
update msd_dp_formulas
set supply_plan_name = p_supply_plan_name -- Bug 4729854
where demand_plan_id = p_new_dp_id
and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
update msd_dp_formula_parameters
set supply_plan_name = p_supply_plan_name -- Bug 4729854
where demand_plan_id = p_new_dp_id
and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
-- delete the formula if ascp plan is detached
else
delete from msd_dp_formulas
where demand_plan_id = p_new_dp_id
and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
delete from msd_dp_formula_parameters
where demand_plan_id = p_new_dp_id
and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
select msd_dp_parameters_s.nextval into l_formula_id from dual;
insert into msd_dp_formulas
(
demand_plan_id
,formula_id
,creation_sequence
,formula_name
,formula_desc
,custom_type
,equation
,custom_field1
,custom_field2
,custom_subtype
,custom_addtlcalc
,isby
,valid_flag
,numerator
,denominator
,supply_plan_flag
,supply_plan_name
,last_update_date
,FORMAT /*----NEW COLUMN ADDED FOR THE BUG# 4373422-----*/
,START_PERIOD /* ADDED NEW COLUMN FOR THE BUG#4744717 */
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
values
(
p_new_dp_id
,l_formula_id
,c1_cur.creation_sequence
,c1_cur.formula_name
,c1_cur.formula_desc
,c1_cur.custom_type
,c1_cur.equation
,c1_cur.custom_field1
,c1_cur.custom_field2
,c1_cur.custom_subtype
,c1_cur.custom_addtlcalc
,c1_cur.isby
,c1_cur.valid_flag
,c1_cur.numerator
,c1_cur.denominator
,c1_cur.supply_plan_flag
,p_supply_plan_name -- Bug 4729854
,SYSDATE
,c1_cur.FORMAT /*----NEW COLUMN ADDED FOR THE BUG# 4373422-----*/
,c1_cur.START_PERIOD /* ADDED NEW COLUMN FOR THE BUG#4744717 */
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE);
insert into msd_dp_formula_parameters
(
demand_plan_id
,formula_id
,where_used
,parameter_sequence
,enabled_flag
,mandatory_flag
,parameter_type
,parameter_component
,parameter_value
,supply_plan_flag
,supply_plan_name
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
(select
p_new_dp_id
,l_formula_id
,where_used
,parameter_sequence
,enabled_flag
,mandatory_flag
,parameter_type
,parameter_component
,parameter_value
,supply_plan_flag
,decode(supply_plan_flag,'Y',p_supply_plan_name,null) -- Bug 4729854
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
from msd_dp_formula_parameters
where demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
and template_flag = 'Y'
and default_template = 'Y'
)
and formula_id = c1_cur.formula_id);
insert into msd_dp_formula_parameters
(
demand_plan_id
,formula_id
,where_used
,parameter_sequence
,enabled_flag
,mandatory_flag
,parameter_type
,parameter_component
,parameter_value
,supply_plan_flag
,supply_plan_name
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
(select
p_new_dp_id
,mdp1.parameter_id
,mdfp.where_used
,mdfp.parameter_sequence
,mdfp.enabled_flag
,mdfp.mandatory_flag
,mdfp.parameter_type
,mdfp.parameter_component
,mdfp.parameter_value
,mdfp.supply_plan_flag
,decode(mdfp.supply_plan_flag,'Y',p_supply_plan_name,null) -- Bug 4729854
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
from msd_dp_formula_parameters mdfp,
msd_dp_parameters mdp,
msd_dp_parameters mdp1
where mdfp.demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
and template_flag = 'Y'
and default_template = 'Y')
and mdp.demand_plan_id=mdfp.demand_plan_id
and mdp.parameter_id=mdfp.formula_id
and mdp1.demand_plan_id=p_new_dp_id
and mdp1.parameter_type=mdp.parameter_type);
* This procedure 'update_formula_names' should always be called whenever a
* supply plan is attached to a scenario.
* Note: This procedure 'update_formula_names' will not be called whenever a
* supply plan attached to a scenario is changed/deleted.
*/
-- if not g_call then
update_formula_names(p_new_dp_id);
Called from update_ascp_related_data, attach_supply_plan.
********************************************************/
Procedure add_ascp_measure(p_new_dp_id in number,p_supply_plan_id in number,p_supply_plan_name in varchar2,
p_old_supply_plan_id in number default null,p_old_supply_plan_name in varchar2 default null) -- Bug 4729854
is
cursor c1 is
select msd.document_id, msd.document_name
from msd_dp_seeded_documents msd
where msd.demand_plan_id = p_new_dp_id;
select document_id
from msd_dp_seeded_documents
where document_name = p_document_name
and demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
and template_flag = 'Y'
and default_template = 'Y');
select count(*)
from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and supply_plan_flag = 'Y';
select distinct document_id, dimension_code
from msd_dp_seeded_doc_dimensions
where demand_plan_id = p_new_dp_id
order by document_id, dimension_code;
select selection_sequence
from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and supply_plan_name = p_old_supply_plan_name -- Bug 4729854
and document_id = p_document_id
and dimension_code = p_dimension_code
order by selection_sequence;
cursor c6(p_document_id in number, p_dimension_code in varchar2, p_selection_sequence in number) is
select supply_plan_name, min(selection_sequence)
from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and supply_plan_name is not null
and supply_plan_name <> p_old_supply_plan_name -- Bug 4729854
and document_id = p_document_id
and dimension_code = p_dimension_code
and selection_sequence > p_selection_sequence
group by supply_plan_name
order by min(selection_sequence);
l_max_selection_sequence number;
l_selection_sequence number;
l_selection_count number;
update msd_dp_doc_dim_selections
set supply_plan_name = p_supply_plan_name -- Bug 4729854
where demand_plan_id = p_new_dp_id
and supply_plan_name = p_old_supply_plan_name; -- Bug 4729854
-- delete the measure if ascp plan is detached and update any other ascp specific measure to be first measure if this was the first measure
else
-- for each document, dimension in plan
for c4_cur in c4 loop
l_selection_count := 0;
-- for each selection sequence for the detached plan
for c5_cur in c5(c4_cur.document_id, c4_cur.dimension_code) loop
-- get the next supply plan name if any and difference in selection sequences for the document and dimension.
-- do this only once for a document and dimension
if l_selection_count = 0 then
open c6(c4_cur.document_id, c4_cur.dimension_code,c5_cur.selection_sequence);
fetch c6 into l_supply_plan_name, l_selection_sequence;
l_seq_diff := l_selection_sequence - c5_cur.selection_sequence;
-- delete the selection first
delete from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id
and supply_plan_name = p_old_supply_plan_name -- Bug 4729854
and selection_sequence = c5_cur.selection_sequence
and document_id = c4_cur.document_id
and dimension_code = c4_cur.dimension_code;
-- set the selection sequence of the next ascp measure to the selection sequence of the measure being deleted and increase by 1000 to avoid uinque constraint violation
update msd_dp_doc_dim_selections
set selection_sequence = c5_cur.selection_sequence
where demand_plan_id = p_new_dp_id
and supply_plan_name = l_supply_plan_name
and selection_sequence = l_seq_diff + c5_cur.selection_sequence
and document_id = c4_cur.document_id
and dimension_code = c4_cur.dimension_code;
l_selection_count := l_selection_count +1;
-- increase selection sequence if not first first plan attached
if l_count > 0 then
-- for each document for the plan
for c1_cur in c1 loop
-- get the document id of the same documnet in template
open c2(c1_cur.document_name);
select max(selection_sequence) into l_max_selection_sequence from msd_dp_doc_dim_selections
where demand_plan_id = p_new_dp_id;
insert into msd_dp_doc_dim_selections
(
demand_plan_id
,document_id
,dimension_code
,enabled_flag
,mandatory_flag
,selection_sequence
,selection_type
,selection_component
,selection_value
,supply_plan_flag
,supply_plan_name
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
(select
p_new_dp_id
,c1_cur.document_id
,dimension_code
,enabled_flag
,mandatory_flag
,l_max_selection_sequence + selection_sequence
,selection_type
,selection_component
,selection_value
,supply_plan_flag
,p_supply_plan_name -- Bug 4729854
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
from msd_dp_doc_dim_selections
where supply_plan_flag = 'Y'
and demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
and template_flag = 'Y'
and default_template = 'Y'
)
and document_id = l_document_id);
-- use seeded selection sequence if first plan attached
else /* l_count >0 */
-- for each document for the plan
for c1_cur in c1 loop
-- get the document id of the same documnet in template
open c2(c1_cur.document_name);
insert into msd_dp_doc_dim_selections
(
demand_plan_id
,document_id
,dimension_code
,enabled_flag
,mandatory_flag
,selection_sequence
,selection_type
,selection_component
,selection_value
,supply_plan_flag
,supply_plan_name
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
(select
p_new_dp_id
,c1_cur.document_id
,dimension_code
,enabled_flag
,mandatory_flag
,selection_sequence
,selection_type
,selection_component
,selection_value
,supply_plan_flag
,p_supply_plan_name -- Bug 4729854
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
from msd_dp_doc_dim_selections
where supply_plan_flag = 'Y'
and demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = (select plan_type from msd_demand_plans where demand_plan_id=p_new_dp_id)
and template_flag = 'Y'
and default_template = 'Y'
)
and document_id = l_document_id);
select fnd_profile.value('MSC_LIABILITY_CALC_LEVEL') into l_level from dual;
update msd_dp_doc_dim_selections
set selection_value = nvl(fnd_profile.value('MSC_LIABILITY_CALC_LEVEL'),1)
where demand_plan_id = p_demand_plan_id
and document_id in
(select document_id
from msd_dp_seeded_documents
where demand_plan_id = p_demand_plan_id
and document_name in ('MSD_LB_DETAILED_REPORT','MSD_LB_SUMMARY_REPORT'))
and dimension_code = 'PRD'
and selection_type = 'L'
and selection_value in ('1','2');
select compile_designator
from msc_plans
where plan_id = p_supply_plan_id;
select curr_start_date
from msc_plans
where plan_id = p_supply_plan_id;
select curr_cutoff_date
from msc_plans
where plan_id = p_supply_plan_id;
select parameter_id
from msd_dp_parameters
where demand_plan_id = p_demand_plan_id
and parameter_type = p_parameter_type
and nvl(parameter_name,'123456789') = nvl(p_parameter_name,'123456789');
select scenario_id
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and forecast_based_on = p_parameter_type
and nvl(parameter_name,'123456789') = nvl(p_parameter_name,'123456789');
select formula_id
from msd_dp_formulas
where demand_plan_id = p_demand_plan_id
and formula_name = p_formula_name
and nvl(supply_plan_name,'123456789') = nvl(p_supply_plan_name,'123456789');
select calendar_code
from msd_dp_calendars
where demand_plan_id = p_demand_plan_id
and calendar_type = decode(p_old_output_period_type_id,1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4);
select min(mdh.hierarchy_id)
from msd_dp_dimensions mdd,
msd_dp_hierarchies mdh,
msd_hierarchies mh
where mdd.dimension_code =
(select dimension_code
from msd_hierarchies
where hierarchy_id = p_hierarchy_id) and
mdh.dp_dimension_code=mdd.dp_dimension_code and
mdh.demand_plan_id=p_demand_plan_id and
mdd.demand_plan_id=p_demand_plan_id and
mdd.dimension_code=mh.dimension_code and
mh.hierarchy_id=mdh.hierarchy_id;
select count(mdh.hierarchy_id)
from msd_dp_hierarchies mdh
where
mdh.demand_plan_id=p_demand_plan_id and
mdh.hierarchy_id=p_hierarchy_id;
select min(ml.level_id)
from msd_dp_dimensions mdd,
msd_dp_hierarchies mdh,
msd_hierarchies mh,
msd_hierarchy_levels mhl,
msd_levels ml
where mdd.dimension_code =
(select dimension_code
from msd_hierarchies
where hierarchy_id = mh.hierarchy_id) and
mdh.dp_dimension_code=mdd.dp_dimension_code and
mdh.demand_plan_id=p_level_id and
mdd.demand_plan_id=p_level_id and
mdd.dimension_code=mh.dimension_code and
mh.hierarchy_id=mdh.hierarchy_id and
mhl.hierarchy_id=mh.hierarchy_id and
(mhl.level_id=ml.level_id or mhl.parent_level_id=ml.level_id) and
ml.level_type_code = (select distinct level_type_code from msd_levels where level_id=p_level_id);
select count(mdh.hierarchy_id)
from msd_dp_hierarchies mdh,
msd_hierarchy_levels mhl
where
mdh.demand_plan_id=p_demand_plan_id and
mhl.hierarchy_id=mdh.hierarchy_id and
(mhl.level_id=p_level_id or mhl.parent_level_id=p_level_id);
select count(*)
from msd_dp_dimensions
where dimension_code=p_dimension_code and
demand_plan_id=p_demand_plan_id;
select count(*)
from msd_dp_dimensions
where dimension_code=p_dimension_code and
demand_plan_id=p_demand_plan_id;
select count(*) from
msd_dp_scenarios
where demand_plan_id = p_new_dp_id
and supply_plan_flag = 'Y'
and associate_parameter is not null;
select scenario_name, description, demand_plan_id, scenario_id
from msd_dp_scenarios
where supply_plan_flag = 'Y'
and demand_plan_id =
(select demand_plan_id
from msd_demand_plans
where plan_type = 'EOL'
and template_flag = 'Y'
and default_template = 'Y'
);
update msd_dp_scenarios set supply_plan_name=p_supply_plan_name, supply_plan_id=p_supply_plan_id
where demand_plan_id=p_new_dp_id
and supply_plan_flag='Y'
and associate_parameter is not null
and supply_plan_id<>p_supply_plan_id;
select msd_dp_scenarios_s.nextval into l_scenario_id from dual;
insert into msd_dp_scenarios
( demand_plan_id
,scenario_id
,scenario_name
,description
,output_period_type
,horizon_start_date
,horizon_end_date
,forecast_date_used
,forecast_based_on
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,scenario_type
,status
,history_start_date
,history_end_date
,publish_flag
,enable_flag
,price_list_name
,last_revision
,parameter_name
,consume_flag
,error_type
,supply_plan_id
,deleteable_flag
,supply_plan_flag
,supply_plan_name
,dmd_priority_scenario_id -- Bug 4710963
,associate_parameter
,sc_type)
(select
p_new_dp_id
,l_scenario_id
,l_name
,l_description
,output_period_type
,null
,null
,forecast_date_used
,forecast_based_on
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,scenario_type
,status
,null
,null
,publish_flag
,enable_flag
,price_list_name
,last_revision
,parameter_name
,consume_flag
,error_type
,p_supply_plan_id
,deleteable_flag
,supply_plan_flag
,p_supply_plan_name -- Bug 4729854
,dmd_priority_scenario_id -- Bug 4710963
,associate_parameter
,sc_type
from msd_dp_scenarios
where scenario_id = c2_rec.scenario_id
and demand_plan_id = c2_rec.demand_plan_id);
insert into msd_dp_scenarios_tl
( demand_plan_id
,scenario_id
,description
,language
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
select
p_new_dp_id
,l_scenario_id
,l_description
,USERENV('LANG')
,USERENV('LANG')
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
from dual;
insert into msd_dp_scenario_events
(
demand_plan_id
,scenario_id
,event_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,event_association_priority)
(select
p_new_dp_id
,l_scenario_id
,event_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,event_association_priority
from msd_dp_scenario_events
where scenario_id = c2_rec.scenario_id
and demand_plan_id = c2_rec.demand_plan_id);
insert into msd_dp_scenario_output_levels
(
demand_plan_id
,scenario_id
,level_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date)
(select
p_new_dp_id
,l_scenario_id
,level_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,NULL
,NULL
,NULL
,SYSDATE
from msd_dp_scenario_output_levels
where scenario_id = c2_rec.scenario_id
and demand_plan_id = c2_rec.demand_plan_id);