[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_lvl_table IN NUMBER
);
/* This procedure deletes duplicate level Values in the Level Association Table*/
Procedure Delete_duplicate_lvl_assoc( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_plan_id in number);
/* This procedure deletes duplicate level Values in the Level Value Table*/
Procedure Delete_duplicate(p_plan_id in number, p_dest_table in varchar2);
/* This procedure does the actual insert of Level values into fact and association table */
Procedure translate_level_parent_values(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_source_table IN VARCHAR2,
p_dest_table IN VARCHAR2,
p_plan_id IN NUMBER,
p_level_id IN NUMBER,
p_level_value_column IN VARCHAR2,
p_level_value_pk_column IN VARCHAR2,
p_level_value_desc_column IN VARCHAR2,
p_parent_level_id IN NUMBER,
p_parent_value_column IN VARCHAR2,
p_parent_value_pk_column IN VARCHAR2,
p_parent_value_desc_column IN VARCHAR2,
p_update_lvl_table IN NUMBER,
p_delete_flag IN VARCHAR2,
p_seq_num IN NUMBER);
p_delete_flag IN VARCHAR2);
select calculate_liability
from msc_plans
where plan_id = p_plan_id;
update msd_demand_plans
set valid_flag = 1
where demand_plan_id = p_liability_plan_id ;
update msd_demand_plans
set valid_flag = 0
where demand_plan_id = p_liability_plan_id ;
/* or updates an existing Plan */
/*2. Validates the demand Plan*/
/*3. Collects Liability Level Values */
/*4. Checks if a Gregoria Calendar exist for plan Start Date and end Date */
/*and generates if not available */
/* 5. Calls the demand Plan buildApi to buid the Demand Plan Cube in Olap */
Procedure run_liability_flow(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_plan_id IN NUMBER
) IS
x_liab_plan_id NUMBER ;
/* This preprocessor updates the Agreement details for the forecast liability views */
MSD_COMMON_UTILITIES_LB. liability_preprocessor ( p_plan_id ) ;
select mdsol.LEVEL_ID , mds.scenario_id into x_prev_liab_calc_level , x_scenario_id from
MSD_DP_SCENARIO_OUTPUT_LEVELS mdsol ,
msd_dp_scenarios mds
where
mds.demand_plan_id = mdsol.demand_plan_id and
mds.scenario_id = mdsol.scenario_id
and mds.demand_plan_id =x_liab_plan_id
and mds.scenario_designator = 'TOTAL_LIABILITY'
and mdsol.level_id in ( 1,2) ;
/* update the output level of the scenario */
update MSD_DP_SCENARIO_OUTPUT_LEVELS
set level_id = x_liab_calc_level
where level_id = x_prev_liab_calc_level
and demand_plan_id = x_liab_plan_id
and scenario_id = x_scenario_id ;
display_message( 'The the output level of the scenario will be updated ' ,WARNING );
display_message( 'The the output level of the scenario will be updated ' ,WARNING );
select mdsol.LEVEL_ID , mds.scenario_id into x_prev_liab_calc_level , x_scenario_id from
MSD_DP_SCENARIO_OUTPUT_LEVELS mdsol ,
msd_dp_scenarios mds
where
mds.demand_plan_id = mdsol.demand_plan_id and
mds.scenario_id = mdsol.scenario_id
and mds.demand_plan_id =x_liab_plan_id
and mds.scenario_designator = 'TOTAL_LIABILITY'
and mdsol.level_id in ( 1,2) ;
/* update the output level of the scenario */
update MSD_DP_SCENARIO_OUTPUT_LEVELS
set level_id = x_liab_calc_level
where level_id = x_prev_liab_calc_level
and demand_plan_id = x_liab_plan_id
and scenario_id = x_scenario_id ;
update msd_demand_plans set plan_start_date = x_plan_start_date , plan_end_date = x_plan_end_date
where demand_plan_id = x_liab_plan_id ;
UPDATE msd_dp_parameters set start_date = x_plan_start_date , end_date = x_plan_end_date
WHERE parameter_type in ( 'MSD_LIAB_OPEN_PO' , 'MSD_LIAB_FORECAST' ,'MSD_LIAB_FCST_DEMAND')
and demand_plan_id = x_liab_plan_id ;
/*UPDATE msd_dp_parameters SET PARAMETER_NAME = x_liab_plan_name
WHERE demand_plan_id = x_liab_plan_id ; */
update msd_demand_plans set valid_flag = 1 where liab_plan_id = p_plan_id ;
update msd_demand_plans set valid_flag = 1 where liab_plan_id = p_plan_id ;
retcode := MSD_COMMON_UTILITIES_LB.liability_plan_update( x_liab_plan_id ) ;
UPDATE msd_dp_parameters set start_date = x_plan_start_date , end_date = x_plan_end_date
WHERE parameter_type in ( 'MSD_LIAB_OPEN_PO' , 'MSD_LIAB_FORECAST' ,'MSD_LIAB_FCST_DEMAND')
and demand_plan_id = x_liab_plan_id ;
Select count(*) into x_cal_no_of_days
from msd_time
where calendar_type = 1 and day between
x_plan_start_date and x_plan_end_date
;
display_message( 'Deleteing Gergorian Calendar between '||x_plan_start_date||'---'||x_plan_end_date , DEBUG);
delete from msd_time where calendar_type = 1 and trunc( day, 'DD') between trunc( x_plan_start_date, 'DD') and trunc( x_plan_end_date , 'DD') ;
update msd_demand_plans set valid_flag = 1 where liab_plan_id = p_plan_id ;
update msd_demand_plans set valid_flag = 0 where liab_plan_id = p_plan_id ;
Select DP_BUILD_ERROR_FLAG into x_DP_BUILD_ERROR_FLAG
from msd_demand_plans
where demand_plan_id = MSD_COMMON_UTILITIES_LB.get_demand_plan_id(p_plan_id) ;
Delete_duplicate(p_plan_id , MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE);
Delete_duplicate_lvl_assoc(errbuf, retcode, p_plan_id);
SELECT DISTINCT dp_dimension_code,
hl.hierarchy_name,
hl.level_name,
hl.relationship_view
FROM msd_dp_hierarchies dh,
msd_hierarchy_levels_lb_v hl
WHERE demand_plan_id = p_demand_plan_id
AND dp_dimension_code <> 'TIM'
AND dh.hierarchy_id = hl.hierarchy_id
AND level_id NOT IN
(select distinct level_id
from msd_level_values_lb lv
where lv.plan_id = p_plan_id
);
SELECT calendar_type, calendar_code, decode(calendar_type,
1, initcap(calendar_code),
calendar_code) op_cal_code
FROM msd_dp_calendars
WHERE demand_plan_id = p_demand_plan_id
and calendar_type <> 1; -- To Prevent the validation of Gregorian calendar because validation of Gregorian Calendar is not required
SELECT
min(start_date), max(end_date)
FROM msd_dp_parameters_cs_v
WHERE demand_plan_id = p_demand_plan_id;
SELECT MIN(day) min_date, MAX(day) max_date
FROM msd_time_lb_v dp
WHERE dp.calendar_type = p_calendar_type
AND dp.calendar_code = p_calendar_code ;
SELECT
distinct
mdp.parameter_type ,
mcd.planning_server_view_name ,
mcd.description
FROM msd_dp_parameters mdp , msd_cs_definitions mcd
where mdp.demand_plan_id =p_demand_plan_id
and parameter_type =mcd.name
and nvl( mcd.planning_server_view_name, 'NA') <> 'NA'
and nvl(mcd.liability_user_flag , 'N') <> 'Y' ;
v_sql_stmt := 'select count(1) from dual where exists (select 1 from '
||x_input_param_rec.planning_server_view_name
||' where cs_name = '
||''''||x_plan_name||''''||' ) ' ;
p_update_lvl_table IN NUMBER) IS
x_source_table VARCHAR2(50) ;
x_delete_flag varchar2(1) := 'Y' ;
select distinct
hierarchy_id,
relationship_view,
level_value_column,
level_value_pk_column,
nvl(level_value_desc_column,level_value_column) level_value_desc_column,
parent_value_column,
parent_value_pk_column,
nvl(parent_value_desc_column, parent_value_column) parent_value_desc_column
from msd_hierarchy_levels
where level_id = p_level_id
and parent_level_id = p_parent_level_id
and plan_type = 'LIABILITY';
SELECT hierarchy_name INTO p_hierarchy_name
FROM msd_hierarchies
WHERE hierarchy_id = Relationship_Rec.hierarchy_id;
p_update_lvl_table => p_update_lvl_table,
p_delete_flag => x_delete_flag,
p_seq_num => p_seq_num);
select distinct level_id, parent_level_id, level_type_code
from msd_hierarchy_levels_lb_v
where owning_dimension_code = p_dimension_code
order by level_type_code, level_id;
select distinct level_id, level_type_code
from msd_hierarchy_levels_lb_v
where owning_dimension_code = p_dimension_code
order by level_type_code, level_id;
p_update_lvl_table => 0);
p_update_lvl_table => 1);
select lookup_code
from fnd_lookup_values_vl
where lookup_type = 'MSD_DIMENSIONS_LB' ;
/* delete duplicate data */
Delete_duplicate(p_plan_id, MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE);
/* Delete duplicate level association from staging table */
Delete_duplicate_lvl_assoc(errbuf, retcode, p_plan_id);
Procedure Delete_duplicate(p_plan_id in number, p_dest_table in varchar2) is
Begin
/* This procedure deletes duplicate records from staging level_values
Key - Plan_id + Level_Id + SR_LEVEL_PK
*/
if p_dest_table = MSD_COMMON_UTILITIES_LB.LEVEL_VALUES_STAGING_TABLE then
delete from msd_st_level_values_lb a where
a.plan_id = p_plan_id and
rowid <> (select max(rowid) from msd_st_level_values_lb b
where a.plan_id = b.plan_id and a.level_id = b.level_id and a.sr_level_pk = b.sr_level_pk);
Procedure Delete_duplicate_lvl_assoc( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_plan_id in number) is
cursor c_duplicate is
select level_id, sr_level_pk, parent_level_id
from msd_st_level_associations_lb
where plan_id = p_plan_id
group by level_id, sr_level_pk, parent_level_id
having count(*) > 1;
/* This procedure deletes duplicate records from staging level association
Key - Plan_id + Child_Level_Id + SR_LEVEL_PK + Parent_Level_ID
*/
OPEN c_duplicate;
delete from msd_st_level_associations_lb a where
a.plan_id = p_plan_id and
a.level_id = a_child_level_id(i) and
a.sr_level_pk = a_sr_level_pk(i) and
a.parent_level_id = a_parent_level_id(i) and
rowid <> (select rowid from msd_st_level_associations_lb b
where b.plan_id = p_plan_id and
b.level_id = a_child_level_id(i) and
b.sr_level_pk = a_sr_level_pk(i) and
b.parent_level_id = a_parent_level_id(i) and
rownum < 2);
END Delete_duplicate_lvl_assoc;
p_update_lvl_table IN NUMBER,
p_delete_flag IN VARCHAR2,
p_seq_num IN NUMBER ) IS
v_plan_id varchar2(40);
v_up := p_update_lvl_table;
Savepoint Before_Delete ;
IF (p_update_lvl_table = 1) THEN
/* Insert deleted level values into deleted_level_value table and delete it
from the fact level value table */
/* For Incremental Level Value Collection, p_delete_flag = 'N'
So, we don't delete existing level values */
/* Process row by row from staging level values table */
-- fnd_file.put_line(fnd_file.log,'3:translate Level Values ' );
/* Delete Staging Table only if delete flag = Yes */
IF (p_delete_flag = 'Y') THEN
/* First time to process this level_id */
IF (p_update_lvl_table = 1) THEN
DELETE FROM msd_st_level_values_lb
WHERE plan_id = p_plan_id AND level_id = p_level_id;
DELETE FROM msd_st_level_associations_lb
WHERE plan_id = p_plan_id AND
level_id = p_level_id
AND parent_level_id = p_parent_level_id;
/* Insert Level Values into staging table */
v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || p_dest_table || ' ( '
||'plan_id, ' ||
'level_id, ' ||
'level_value, ' ||
'sr_level_pk, ' ||
'level_value_desc, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'creation_date, ' ||
'created_by ) ' ||
'select ' ||
p_plan_id ||', ' ||
p_level_id || ', ' ||
p_level_value_column||', ' ||
p_level_value_pk_column||', ' ||
p_level_value_desc_column||', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ' ' ||
'from ' ||
p_source_table||
' where '||
'plan_id = '||
p_plan_id ;
/* Insert Level Associations into staging table */
v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || v_dest_ass_table || ' ( ' ||
'plan_id, ' ||
'level_id, ' ||
'sr_level_pk, ' ||
'parent_level_id, ' ||
'sr_parent_level_pk, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'creation_date, ' ||
'created_by ) ' ||
'select ' ||
p_plan_id||', ' ||
p_level_id || ', ' ||
p_level_value_pk_column||', ' ||
p_parent_level_id || ', ' ||
p_parent_value_pk_column ||', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ' ' ||
' from ' ||
p_source_table||
' where '||
'plan_id = '||
p_plan_id ;
select level_type_code into v_parent_lvl_type
from msd_levels
where level_id = p_parent_level_id
and plan_type = 'LIABILITY' ;
IF (v_parent_lvl_type = '1' AND p_update_lvl_table = 1) THEN
--fnd_file.put_line(fnd_file.log,'1: Before PROCESS_TOP_LEVEL_VALUES ');
p_delete_flag);
/* New Level values will be inserted into fact table
and will get deleted from the staging */
CURSOR c_insert IS
select sr_level_pk
from msd_st_level_values_lb
where plan_id = p_plan_id and level_id = p_level_id
MINUS
select sr_level_pk
from msd_level_values_lb
where plan_id = p_plan_id and level_id = p_level_id;
new level values are deleted from the staging table
*/
CURSOR c_update IS
(select sr_level_pk, level_value,
level_value_desc
from msd_st_level_values_lb
where plan_id = p_plan_id and level_id = p_level_id
MINUS
select sr_level_pk, level_value,
level_value_desc
from msd_level_values_lb
where plan_id = p_plan_id and level_id = p_level_id);
OPEN c_insert;
FETCH c_insert BULK COLLECT INTO a_sr_level_pk;
CLOSE c_insert;
/* First Delete fetched rows from staging, and then
Insert them into Fact Table.
*/
---fnd_file.put_line(fnd_file.log,'**In 2 PROCESS_LEVEL_VALUE_PER_ROW********');
DELETE FROM msd_st_level_values_lb
WHERE plan_id = p_plan_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i)
RETURNING level_value, level_value_desc
BULK COLLECT INTO a_level_value,
a_level_value_desc;
/* Insert new rows into fact table */
FORALL j IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
INSERT INTO msd_level_values_lb(
plan_id, level_id, level_value,
sr_level_pk, level_pk, level_value_desc,
action_code, created_by_refresh_num, last_refresh_num,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login)
VALUES(
p_plan_id,
p_level_id,
a_level_value(j),
a_sr_level_pk(j),
a_level_pk(j),
a_level_value_desc(j),
'I', p_seq_num, p_seq_num,
sysdate, FND_GLOBAL.USER_ID,
sysdate, FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID );
/* Fetch updated rows from staging */
OPEN c_update;
FETCH c_update BULK COLLECT INTO a_sr_level_pk, a_level_value,
a_level_value_desc;
CLOSE c_update;
UPDATE msd_level_values_lb
SET level_value = a_level_value(i),
level_value_desc = a_level_value_desc(i),
action_code = 'U',
last_refresh_num = p_seq_num,
last_update_date = sysdate
WHERE plan_id = p_plan_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i);
/* This cursur will select only new level associations */
CURSOR c_new_rows IS
(select sr_level_pk
from msd_st_level_associations_lb
where plan_id = p_plan_id and level_id = p_level_id and
parent_level_id = p_parent_level_id
MINUS
select sr_level_pk
from msd_level_associations_lb
where plan_id = p_plan_id and level_id = p_level_id and
parent_level_id = p_parent_level_id);
/* Cursor for updated level association */
/* This cursor need to be opened only after
new associations are deleted from the staging table */
CURSOR c_update_rows IS
(select sr_level_pk, sr_parent_level_pk
from msd_st_level_associations_lb
where plan_id = p_plan_id and level_id = p_level_id and
parent_level_id = p_parent_level_id
MINUS
select sr_level_pk, sr_parent_level_pk
from msd_level_associations_lb
where plan_id = p_plan_id and level_id = p_level_id and
parent_level_id = p_parent_level_id);
/* First Delete fetched rows(new level associations) from staging,
and then Insert them into Fact Table.
*/
FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
DELETE FROM msd_st_level_associations_lb
WHERE plan_id = p_plan_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i) and
parent_level_id = p_parent_level_id
RETURNING sr_parent_level_pk
BULK COLLECT INTO a_sr_parent_level_pk;
/* Insert new rows into fact table */
IF (a_sr_parent_level_pk.exists(1)) THEN
FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
INSERT INTO msd_level_associations_lb(
plan_id, level_id, sr_level_pk,
parent_level_id, sr_parent_level_pk,
last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
created_by_refresh_num, last_refresh_num, action_code)
VALUES( p_plan_id, p_level_id, a_sr_level_pk(i),
p_parent_level_id, a_sr_parent_level_pk(i),
sysdate, FND_GLOBAL.USER_ID,
sysdate,FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID,
p_seq_num, p_seq_num, 'I');
OPEN c_update_rows;
FETCH c_update_rows BULK COLLECT INTO a_sr_level_pk, a_sr_parent_level_pk;
CLOSE c_update_rows;
/* For updated level association */
IF (a_sr_level_pk.exists(1) and a_sr_parent_level_pk.exists(1)) THEN
FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
UPDATE msd_level_associations_lb
SET
sr_parent_level_pk = a_sr_parent_level_pk(i),
action_code = 'U',
last_refresh_num = p_seq_num,
last_update_date = sysdate
WHERE plan_id = p_plan_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i) and
parent_level_id = p_parent_level_id;
p_delete_flag IN VARCHAR2) IS
v_sql_stmt varchar2(4000);
delete from msd_st_level_values_lb
where plan_id = p_plan_id
and level_id = p_parent_level_id ;
v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || p_dest_table || ' ( ' ||
'plan_id, ' ||
'level_value, ' ||
'sr_level_pk, ' ||
'level_id, ' ||
'level_value_desc, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'creation_date, ' ||
'created_by ) ' ||
'SELECT ''' ||
p_plan_id ||''', ' ||
p_parent_value_column || ', ' ||
p_parent_value_pk_column ||', ' ||
p_parent_level_id || ', ' ||
'parent_desc_alias' ||', ' ||
'sysdate, ' || FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' || FND_GLOBAL.USER_ID || ' ' ||
'FROM ' ||
'(select distinct ' || p_parent_value_column || ', ' ||
p_parent_value_pk_column || ', ' ||
p_parent_level_id || ', '||
p_parent_value_desc_column || ' parent_desc_alias ' || ' from ' ||
p_source_table||' where plan_id = '||p_plan_id|| ') src ';
x_delete_flag VARCHAR2(1);
select distinct
mla.plan_id,
ml.level_type_code,
mla.level_id,
mla.parent_level_id
from msd_st_level_associations_lb mla, msd_levels ml
where mla.level_id = ml.level_id
and ml.plan_type = 'LIABILITY'
and mla.plan_id = p_plan_id ;
x_delete_flag := 'N';
/* Fetch new seq number for deleted level values */
SELECT msd.msd_last_refresh_number_s.nextval
INTO l_seq_num from dual;
p_update_lvl_table => 0,
p_delete_flag => x_delete_flag,
p_seq_num => l_seq_num
) ;
Delete from msd_st_level_associations_lb
where plan_id = Relationship_Rec.plan_id
and level_id = Relationship_Rec.level_id
and parent_level_id = Relationship_Rec.parent_level_id ;
p_update_lvl_table => 1,
p_delete_flag => x_delete_flag,
p_seq_num => l_seq_num
) ;
Delete from msd_st_level_values_lb
where plan_id = Relationship_Rec.plan_id
and level_id = Relationship_Rec.level_id ;
Delete from msd_st_level_associations_lb
where plan_id = Relationship_Rec.plan_id
and level_id = Relationship_Rec.level_id
and parent_level_id = Relationship_Rec.parent_level_id ;
Delete from msd_st_level_values_lb
where level_id in (
select level_id
from msd_levels
where level_type_code = '1'
and plan_type = 'LIABILITY') ;
SELECT demand_plan_name
FROM msd_demand_plans
WHERE demand_plan_id = p_demand_plan_id
FOR UPDATE of demand_plan_name NOWAIT;
select
plan_id plan_id
from msd_level_values_lb
minus
Select
liab_plan_id plan_id
from msd_demand_plans ;
Delete from msd_level_values_lb where plan_id = to_number (a_plan_id(i) );
Delete from msd_level_associations_lb where plan_id = to_number (a_plan_id(i) );
select
count(*) no_of_agreements
from
msc_item_suppliers mis ,
MSC_ASL_AUTH_DETAILS maad
where
maad.sr_instance_id = mis.sr_instance_id
and maad.organization_id = mis.organization_id
and maad.supplier_id = mis.supplier_id
and maad.inventory_item_id = mis.inventory_item_id
and mis.plan_id = p_plan_id
and maad.plan_id = -1 ;
update msd_demand_plans set base_uom = x_base_uom where liab_plan_id = p_plan_id ;
update msd_demand_plans set valid_flag = 1 where liab_plan_id = p_plan_id ;
SELECT scen.scenario_name,ml.dimension_code,count(*)
FROM
msd_dp_scenario_output_levels a,
msd_levels ml,
msd_dp_scenarios scen,
msd_demand_plans mdp
WHERE a.level_id = ml.level_id
AND a.scenario_id = scen.scenario_id
AND scen.enable_flag = 'Y'
and scen.demand_plan_id = mdp.demand_plan_id
and a.demand_plan_id = mdp.demand_plan_id
and mdp.liab_plan_id = p_plan_id
and ml.plan_type = 'LIABILITY'
group by scen.scenario_name,ml.dimension_code
having count(*) >1 ;
select m_min_tim_lvl_id from
msd_demand_plans mdp
where
mdp.liab_plan_id =p_plan_id ;
select mdc.calendar_type
from
msd_dp_calendars mdc ,
msd_demand_plans mdp
where
mdc.demand_plan_id = mdp.demand_plan_id
and mdp.liab_plan_id = p_plan_id
and mdc.calendar_type = 2 ;
display_message('Scenario '||get_dup_dim_output_levels_rec.scenario_name||' has more than one Output Level selected ' , ERROR) ;
delete from msd_time_lb where CALENDAR_CODE in
( select CALENDAR_CODE from msd_dp_calendars where demand_plan_id = p_demand_plan_id)
;
insert into msd_time_lb (
CALENDAR_TYPE,
CALENDAR_CODE,
SEQ_NUM,
YEAR,
YEAR_DESCRIPTION,
YEAR_START_DATE,
YEAR_END_DATE,
QUARTER,
QUARTER_DESCRIPTION,
QUARTER_START_DATE,
QUARTER_END_DATE,
MONTH,
MONTH_DESCRIPTION,
MONTH_START_DATE,
MONTH_END_DATE,
WEEK,
WEEK_DESCRIPTION,
WEEK_START_DATE,
WEEK_END_DATE,
DAY,
DAY_DESCRIPTION,
WORKING_DAY )
select * from msd_sr_time_lb_v
where CALENDAR_CODE
in ( select CALENDAR_CODE from msd_dp_calendars where demand_plan_id = p_demand_plan_id and calendar_type <> 1 ) ;