[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE Update_Cursor IS REF CURSOR;
Update_Cur Update_Cursor ;
- to identify whether we need to insert the
- data into the staging tables or the
- fact tables.
- 3. Check for the Data Duplication, we should
- use the forecast_designator for this fact.
- 4. Insert the Data accordingly into the
- Staging or the Fact table based on the
- MSD_SR_MFG_FCST_V
- 5. Commit
****************************************************/
retcode :=0;
Savepoint Before_Delete ;
/* DWK Delete existing data from MSD_TIME before collection */
if (l_calendar_code is null) then
delete from msd_time
where calendar_type = p_calendar_type_id;
delete from msd_time
where calendar_code = l_calendar_code
and calendar_type = p_calendar_type_id;
v_sql_stmt := 'insert into ' || p_dest_table || ' ( ' ||
'instance, ' ||
'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, ' ||
'LAST_UPDATE_DATE, ' ||
'last_updated_by, ' ||
'creation_date, ' ||
'created_by, ' ||
'LAST_UPDATE_LOGIN ) ' ||
'select ''' ||
p_instance_id ||''', ' ||
p_calendar_type_id ||
', 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, ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
FND_GLOBAL.USER_ID || ' ' ||
'from ' ||
p_source_table ||
' where calendar_code = NVL(:l_calendar_code, calendar_code)' ;
v_sql_stmt := v_sql_stmt || ' and calendar_code in (SELECT distinct mod.calendar_code FROM mtl_parameters';
v_sql_stmt := 'select ' ||
' calendar_code, ' ||
' 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 ' ||
' from ' ||
p_source_table ||
' where calendar_code = NVL( :p_calendar_code ' ||
', calendar_code) ' || v_month_range_stmt;
rollback to Savepoint Before_Delete ;
insert into msd_time (
instance,
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,
DAY,
DAY_DESCRIPTION,
LAST_UPDATE_DATE,
last_updated_by,
creation_date,
created_by,
LAST_UPDATE_LOGIN )
values(
p_instance_id,
p_calendar_type_id,
p_calendar_code,
g_seq_num,
p_year,
p_year_description,
p_year_start_date,
p_year_end_date,
p_quarter,
p_quarter_description,
p_quarter_start_date,
p_quarter_end_date,
p_month,
p_month_description,
p_month_start_date,
p_month_end_date,
p_month_start_date+v_num_of_days,
p_month_start_date+v_num_of_days,
sysdate,
FND_GLOBAL.USER_ID ,
sysdate,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.USER_ID
) ;
insert into msd_st_time (
instance,
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,
DAY,
DAY_DESCRIPTION,
LAST_UPDATE_DATE,
last_updated_by,
creation_date,
created_by,
LAST_UPDATE_LOGIN )
values(
p_instance_id,
p_calendar_type_id,
p_calendar_code,
g_seq_num,
p_year,
p_year_description,
p_year_start_date,
p_year_end_date,
p_quarter,
p_quarter_description,
p_quarter_start_date,
p_quarter_end_date,
p_month,
p_month_description,
p_month_start_date,
p_month_end_date,
p_month_start_date+v_num_of_days,
p_month_start_date+v_num_of_days,
sysdate,
FND_GLOBAL.USER_ID ,
sysdate,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.USER_ID
) ;
insert into msd_time (
instance,
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,
DAY,
DAY_DESCRIPTION,
LAST_UPDATE_DATE,
last_updated_by,
creation_date,
created_by,
LAST_UPDATE_LOGIN )
values (
-1,
1,
p_calendar_code,
v_seq,
to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
fnd_date.string_to_date('01-JAN-'||to_char(p_from_date+v_num_of_days,'YYYY'),
'DD-MON-YYYY'),
fnd_date.string_to_date('31-DEC-'||to_char(p_from_date+v_num_of_days,'YYYY'),
'DD-MON-YYYY'),
decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
'01', 'Qtr 1',
'02', 'Qtr 1',
'03', 'Qtr 1',
'04', 'Qtr 2',
'05', 'Qtr 2',
'06', 'Qtr 2',
'07', 'Qtr 3',
'08', 'Qtr 3',
'09', 'Qtr 3',
'10', 'Qtr 4',
'11', 'Qtr 4',
'12', 'Qtr 4') || ' ' || to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
'01', 'Qtr 1',
'02', 'Qtr 1',
'03', 'Qtr 1',
'04', 'Qtr 2',
'05', 'Qtr 2',
'06', 'Qtr 2',
'07', 'Qtr 3',
'08', 'Qtr 3',
'09', 'Qtr 3',
'10', 'Qtr 4',
'11', 'Qtr 4',
'12', 'Qtr 4') || ' ' || to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
fnd_date.string_to_date(decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
'01', '01-JAN-',
'02', '01-JAN-',
'03', '01-JAN-',
'04', '01-APR-',
'05', '01-APR-',
'06', '01-APR-',
'07', '01-JUL-',
'08', '01-JUL-',
'09', '01-JUL-',
'10', '01-OCT-',
'11', '01-OCT-',
'12', '01-OCT-')||to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
'DD-MON-YYYY'),
fnd_date.string_to_date(decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
'01', '31-MAR-',
'02', '31-MAR-',
'03', '31-MAR-',
'04', '30-JUN-',
'05', '30-JUN-',
'06', '30-JUN-',
'07', '30-SEP-',
'08', '30-SEP-',
'09', '30-SEP-',
'10', '31-DEC-',
'11', '31-DEC-',
'12', '31-DEC-')||to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
'DD-MON-YYYY'),
to_char(p_from_date+v_num_of_days,'MON','nls_date_language = AMERICAN')||' '||
to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
to_char(p_from_date+v_num_of_days,'MON','nls_date_language = AMERICAN')||' '||
to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
fnd_date.string_to_date(decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
'01', '01-JAN-',
'02', '01-FEB-',
'03', '01-MAR-',
'04', '01-APR-',
'05', '01-MAY-',
'06', '01-JUN-',
'07', '01-JUL-',
'08', '01-AUG-',
'09', '01-SEP-',
'10', '01-OCT-',
'11', '01-NOV-',
'12', '01-DEC-')||to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
'DD-MON-YYYY'),
fnd_date.string_to_date(decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
'01', '31-JAN-',
'02', decode(mod(to_number(
to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN')),4),
0, '29-FEB-',
'28-FEB-'),
'03', '31-MAR-',
'04', '30-APR-',
'05', '31-MAY-',
'06', '30-JUN-',
'07', '31-JUL-',
'08', '31-AUG-',
'09', '30-SEP-',
'10', '31-OCT-',
'11', '30-NOV-',
'12', '31-DEC-')||to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
'DD-MON-YYYY'),
((p_from_date)+(v_num_of_days)),
to_char((p_from_date)+(v_num_of_days), 'DD-MON-YYYY','nls_date_language = AMERICAN'),
sysdate,
FND_GLOBAL.USER_ID ,
sysdate,
FND_GLOBAL.USER_ID ,
FND_GLOBAL.USER_ID
) ;
select distinct
week_start_date sd,
week_end_date ed,
week,
week_description,
month,
month_description,
calendar_code,
month_start_date,
month_end_date,
instance
from msd_time
where calendar_type = 2
and calendar_code = nvl(p_cal_code, calendar_code);
insert into msd_time(INSTANCE, CALENDAR_TYPE, CALENDAR_CODE, SEQ_NUM,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN,
MONTH, MONTH_DESCRIPTION,
MONTH_START_DATE, MONTH_END_DATE,
WEEK, WEEK_DESCRIPTION,
WEEK_START_DATE, WEEK_END_DATE,
DAY, DAY_DESCRIPTION,
WORKING_DAY)
select week.instance, 2, week.calendar_code, -1,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.user_id,
week.month, week.month_description,
week.month_start_date, week.month_end_date,
week.week, week.week_description,
week.sd, week.ed,
day, to_char(day),
'NO'
from
(
select week.sd+rownum-1 day
from msd_time
where rownum < week.ed-week.sd+2
MINUS
select day
from msd_time
where calendar_type = 2
and calendar_code = week.calendar_code
and week_start_date = week.sd
and week_end_date = week.ed
);