[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER(15) := -1;
g_last_update_date DATE := sysdate;
g_last_update_login NUMBER(15) := -1;
SELECT yr_strt_dt, yr_end_dt
FROM DDR_R_BSNS_YR
WHERE yr_nbr = (
SELECT MAX(yr_nbr)
FROM DDR_R_BSNS_YR
WHERE clndr_cd = (
SELECT clndr_cd
FROM DDR_R_CLNDR
WHERE org_cd = p_org_code
AND clndr_typ = 'BSNS'
)
)
-- Bug# 6866605 change start
AND clndr_cd = (
SELECT clndr_cd
FROM DDR_R_CLNDR
WHERE org_cd = p_org_code
AND clndr_typ = 'BSNS'
)
-- Bug# 6866605 change end
*/
l_SQL_str := 'select YR_NBR, YR_STRT_DT, YR_END_DT from ' || l_table_name || ' where YR_NBR =';
l_SQL_str := l_SQL_str || ' (select max(YR_NBR) from ' || l_table_name;
l_SQL_str := l_SQL_str || ' where CLNDR_CD = (select CLNDR_CD from DDR_R_CLNDR';
l_SQL_str := l_SQL_str || ' AND clndr_cd = (SELECT clndr_cd FROM DDR_R_CLNDR WHERE org_cd = ''' || p_org_code || ''' AND clndr_typ = ''' || p_clndr_type || ''')';
select WK_NBR, WK_STRT_DT, WK_END_DT
from DDR_R_CLNDR_WK
where WK_NBR = (
select max(WK_NBR)
from DDR_R_CLNDR_WK
);
select CLNDR_WK_ID, WK_CD
from DDR_R_CLNDR_WK
where p_date between TRUNC(WK_STRT_DT)
and TRUNC(WK_END_DT) + .99999;
select ORG_TYP
from DDR_R_ORG
where ORG_CD = p_org_code;
select ORG_CD
from DDR_R_ORG
where ORG_TYP = 'MFG';
select 1
from DDR_R_WKDAY;
insert into DDR_R_WKDAY (
WKDAY_ID,
WKDAY_CD,
WKDAY_DESC,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
day_idx,
day_idx,
TO_CHAR(l_sunday_date+day_idx-1,'DAY'),
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
select CLNDR_CD
from DDR_R_CLNDR
where CLNDR_TYP = p_clndr_type
and ORG_CD = p_org_code
and MFG_ORG_CD = p_mfg_org_code;
insert into DDR_R_CLNDR (
CLNDR_ID,
MFG_ORG_CD,
CLNDR_CD,
ORG_CD,
CLNDR_TYP,
CLNDR_DESC,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
DDR_R_CLNDR_SEQ.NEXTVAL,
p_mfg_org_code,
l_clndr_cd,
p_org_code,
p_clndr_type,
p_org_code || ' - ' ||
decode(p_clndr_type,
'BSNS','Business',
'FSCL','Fiscal',
'ADVR','Advertising',
'PLNG','Planning'
),
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
select 1
from DDR_R_CLNDR_YR
where YR_NBR = p_year;
SELECT DDR_R_CLNDR_YR_SEQ.NEXTVAL
INTO l_curr_year_id
FROM DUAL;
insert into DDR_R_CLNDR_YR (
CLNDR_YR_ID,
YR_CD,
YR_NBR,
YR_DESC,
YR_STRT_DT,
YR_END_DT,
YR_TIMESPN,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_year_id,
l_curr_year,
l_curr_year,
l_curr_year_desc,
l_curr_year_start_date,
l_curr_year_end_date,
l_no_year_days,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_CLNDR_QTR_SEQ.NEXTVAL
INTO l_curr_qtr_id
FROM DUAL;
insert into DDR_R_CLNDR_QTR (
CLNDR_QTR_ID,
QTR_CD,
QTR_NBR,
QTR_DESC,
QTR_STRT_DT,
QTR_END_DT,
QTR_TIMESPN,
CLNDR_YR_ID,
YR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_qtr_id,
l_curr_qtr,
l_curr_qtr,
l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
l_curr_qtr_start_date,
l_curr_qtr_end_date,
l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
l_curr_year_id,
l_curr_year,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_CLNDR_MNTH_SEQ.NEXTVAL
INTO l_curr_month_id
FROM DUAL;
insert into DDR_R_CLNDR_MNTH (
CLNDR_MNTH_ID,
MNTH_CD,
MNTH_NBR,
MNTH_DESC,
MNTH_STRT_DT,
MNTH_END_DT,
MNTH_TIMESPN,
CLNDR_QTR_ID,
QTR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_month_id,
l_curr_month,
l_curr_month,
l_curr_year_desc || ' M' || TO_CHAR(l_month_no),
l_curr_month_start_date,
l_curr_month_end_date,
l_curr_month_end_date - l_curr_month_start_date + 1,
l_curr_qtr_id,
l_curr_qtr,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_CLNDR_WK_SEQ.NEXTVAL
INTO l_curr_week_id
FROM DUAL;
insert into DDR_R_CLNDR_WK (
CLNDR_WK_ID,
WK_CD,
WK_NBR,
WK_DESC,
WK_STRT_DT,
WK_END_DT,
WK_TIMESPN,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_week_id,
l_curr_week,
l_curr_week,
l_curr_year_desc || ' W' || TO_CHAR(l_week_no),
l_curr_week_start_date,
l_curr_week_end_date,
l_curr_week_end_date - l_curr_week_start_date + 1,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
/* Insert Day Record */
l_curr_day_id := TO_NUMBER(TO_CHAR(l_curr_date,g_DAY_ID_format));
insert into DDR_R_DAY (
DAY_CD,
CLNDR_DT,
CLNDR_DT_DESC,
JULIAN_DAY,
WKDAY_ID,
WK_DAY,
CLNDR_WK_ID,
WK_CD,
CLNDR_MNTH_ID,
MNTH_CD,
DAY_OF_YR,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_day_id,
l_curr_date,
l_curr_year_desc || ' ' || TO_CHAR(l_curr_date),
TO_CHAR(l_curr_date,'J'),
l_wkday_id,
l_wkday_id,
l_curr_week_id,
l_curr_week,
l_curr_month_id,
l_curr_month,
day_idx,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='BSNS';
SELECT DDR_R_BSNS_YR_SEQ.NEXTVAL
INTO l_curr_year_id
FROM DUAL;
insert into DDR_R_BSNS_YR (
BSNS_YR_ID,
MFG_ORG_CD,
CLNDR_CD,
YR_CD,
YR_NBR,
YR_DESC,
YR_STRT_DT,
YR_END_DT,
YR_TIMESPN,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_year_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_year,
l_curr_year,
l_curr_year_desc,
l_curr_year_start_date,
l_curr_year_end_date,
l_no_year_days,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_BSNS_QTR_SEQ.NEXTVAL
INTO l_curr_qtr_id
FROM DUAL;
insert into DDR_R_BSNS_QTR (
BSNS_QTR_ID,
MFG_ORG_CD,
CLNDR_CD,
QTR_CD,
QTR_NBR,
QTR_DESC,
QTR_STRT_DT,
QTR_END_DT,
QTR_TIMESPN,
BSNS_YR_ID,
YR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_qtr_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_qtr,
l_curr_qtr,
l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
l_curr_qtr_start_date,
l_curr_qtr_end_date,
l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
l_curr_year_id,
l_curr_year,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_BSNS_MNTH_SEQ.NEXTVAL
INTO l_curr_month_id
FROM DUAL;
insert into DDR_R_BSNS_MNTH (
BSNS_MNTH_ID,
MFG_ORG_CD,
CLNDR_CD,
MNTH_CD,
MNTH_NBR,
MNTH_DESC,
MNTH_STRT_DT,
MNTH_END_DT,
MNTH_TIMESPN,
BSNS_QTR_ID,
QTR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_month_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_month,
l_curr_month,
l_curr_year_desc || ' M' || TO_CHAR(l_month_no),
l_curr_month_start_date,
l_curr_month_end_date,
l_curr_month_end_date - l_curr_month_start_date + 1,
l_curr_qtr_id,
l_curr_qtr,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
/* Insert Record into Week table */
l_week_no := l_week_no + 1;
SELECT DDR_R_BSNS_WK_SEQ.NEXTVAL
INTO l_curr_week_id
FROM DUAL;
insert into DDR_R_BSNS_WK (
BSNS_WK_ID,
MFG_ORG_CD,
CLNDR_CD,
WK_CD,
WK_NBR,
WK_DESC,
WK_STRT_DT,
WK_END_DT,
WK_TIMESPN,
BSNS_MNTH_ID,
MNTH_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_week_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_week,
l_curr_week,
l_curr_year_desc || ' W' || TO_CHAR(l_week_no),
l_curr_week_start_date,
l_curr_week_end_date,
l_curr_week_end_date - l_curr_week_start_date + 1,
l_curr_month_id,
l_curr_month,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
/* Insert Records into Base Day table */
l_curr_date := l_curr_date - 1;
insert into DDR_R_BASE_DAY (
BASE_DAY_ID,
MFG_ORG_CD,
CLNDR_CD,
DAY_CD,
CLNDR_TYP,
WK_ID,
WK_CD,
MNTH_ID,
MNTH_CD,
DAY_OF_YR,
WKEND_IND,
CLNDR_STRT_DT,
CLNDR_END_DT,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
DDR_R_BASE_DAY_SEQ.NEXTVAL,
l_mfg_org_cd,
l_clndr_cd,
l_curr_day_id,
'BSNS',
l_curr_week_id,
l_curr_week,
l_curr_month_id,
l_curr_month,
l_day_no,
decode(TRIM(TO_CHAR(l_curr_date,'DAY')),'SATURDAY','Y','SUNDAY','Y','N'),
l_curr_date,
l_curr_date,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='FSCL';
SELECT DDR_R_FSCL_YR_SEQ.NEXTVAL
INTO l_curr_year_id
FROM DUAL;
insert into DDR_R_FSCL_YR (
FSCL_YR_ID,
MFG_ORG_CD,
CLNDR_CD,
YR_CD,
YR_NBR,
YR_DESC,
YR_STRT_DT,
YR_END_DT,
YR_TIMESPN,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_year_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_year,
l_curr_year,
l_curr_year_desc,
l_curr_year_start_date,
l_curr_year_end_date,
l_no_year_days,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_FSCL_QTR_SEQ.NEXTVAL
INTO l_curr_qtr_id
FROM DUAL;
insert into DDR_R_FSCL_QTR (
FSCL_QTR_ID,
MFG_ORG_CD,
CLNDR_CD,
QTR_CD,
QTR_NBR,
QTR_DESC,
QTR_STRT_DT,
QTR_END_DT,
QTR_TIMESPN,
FSCL_YR_ID,
YR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_qtr_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_qtr,
l_curr_qtr,
l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
l_curr_qtr_start_date,
l_curr_qtr_end_date,
l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
l_curr_year_id,
l_curr_year,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_FSCL_MNTH_SEQ.NEXTVAL
INTO l_curr_month_id
FROM DUAL;
insert into DDR_R_FSCL_MNTH (
FSCL_MNTH_ID,
MFG_ORG_CD,
CLNDR_CD,
MNTH_CD,
MNTH_NBR,
MNTH_DESC,
MNTH_STRT_DT,
MNTH_END_DT,
MNTH_TIMESPN,
FSCL_QTR_ID,
QTR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_month_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_month,
l_curr_month,
l_curr_year_desc || ' M' || TO_CHAR(l_month_no),
l_curr_month_start_date,
l_curr_month_end_date,
l_curr_month_end_date - l_curr_month_start_date + 1,
l_curr_qtr_id,
l_curr_qtr,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
/* Insert Day Record */
l_day_no := l_day_no + 1;
insert into DDR_R_BASE_DAY (
BASE_DAY_ID,
MFG_ORG_CD,
CLNDR_CD,
DAY_CD,
CLNDR_TYP,
WK_ID,
WK_CD,
MNTH_ID,
MNTH_CD,
DAY_OF_YR,
WKEND_IND,
CLNDR_STRT_DT,
CLNDR_END_DT,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
DDR_R_BASE_DAY_SEQ.NEXTVAL,
l_mfg_org_cd,
l_clndr_cd,
l_curr_day_id,
'FSCL',
null,
null,
l_curr_month_id,
l_curr_month,
l_day_no,
decode(TRIM(TO_CHAR(l_curr_date,'DAY')),'SATURDAY','Y','SUNDAY','Y','N'),
l_curr_date,
l_curr_date,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='ADVR';
SELECT DDR_R_ADVR_YR_SEQ.NEXTVAL
INTO l_curr_year_id
FROM DUAL;
insert into DDR_R_ADVR_YR (
ADVR_YR_ID,
MFG_ORG_CD,
CLNDR_CD,
YR_CD,
YR_NBR,
YR_DESC,
YR_STRT_DT,
YR_END_DT,
YR_TIMESPN,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_year_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_year,
l_curr_year,
l_curr_year_desc,
l_curr_year_start_date,
l_curr_year_end_date,
l_no_year_days,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_ADVR_QTR_SEQ.NEXTVAL
INTO l_curr_qtr_id
FROM DUAL;
insert into DDR_R_ADVR_QTR (
ADVR_QTR_ID,
MFG_ORG_CD,
CLNDR_CD,
QTR_CD,
QTR_NBR,
QTR_DESC,
QTR_STRT_DT,
QTR_END_DT,
QTR_TIMESPN,
ADVR_YR_ID,
YR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_qtr_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_qtr,
l_curr_qtr,
l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
l_curr_qtr_start_date,
l_curr_qtr_end_date,
l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
l_curr_year_id,
l_curr_year,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_ADVR_PRD_SEQ.NEXTVAL
INTO l_curr_period_id
FROM DUAL;
insert into DDR_R_ADVR_PRD (
ADVR_PRD_ID,
MFG_ORG_CD,
CLNDR_CD,
PRD_CD,
PRD_NBR,
PRD_DESC,
PRD_STRT_DT,
PRD_END_DT,
PRD_TIMESPN,
ADVR_QTR_ID,
QTR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_period_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_period,
l_curr_period,
l_curr_year_desc || ' P' || TO_CHAR(l_period_no),
l_curr_period_start_date,
l_curr_period_end_date,
l_curr_period_end_date - l_curr_period_start_date + 1,
l_curr_qtr_id,
l_curr_qtr,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
/* Insert Record into Week table */
l_week_no := l_week_no + 1;
SELECT DDR_R_ADVR_WK_SEQ.NEXTVAL
INTO l_curr_week_id
FROM DUAL;
insert into DDR_R_ADVR_WK (
ADVR_WK_ID,
MFG_ORG_CD,
CLNDR_CD,
WK_CD,
WK_NBR,
WK_DESC,
WK_STRT_DT,
WK_END_DT,
WK_TIMESPN,
ADVR_PRD_ID,
PRD_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_week_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_week,
l_curr_week,
l_curr_year_desc || ' W' || TO_CHAR(l_week_no),
l_curr_week_start_date,
l_curr_week_end_date,
l_curr_week_end_date - l_curr_week_start_date + 1,
l_curr_period_id,
l_curr_period,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
/* Insert Records into Base Day table */
l_curr_date := l_curr_date - 1;
insert into DDR_R_BASE_DAY (
BASE_DAY_ID,
MFG_ORG_CD,
CLNDR_CD,
DAY_CD,
CLNDR_TYP,
WK_ID,
WK_CD,
MNTH_ID,
MNTH_CD,
DAY_OF_YR,
WKEND_IND,
CLNDR_STRT_DT,
CLNDR_END_DT,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
DDR_R_BASE_DAY_SEQ.NEXTVAL,
l_mfg_org_cd,
l_clndr_cd,
l_curr_day_id,
'ADVR',
l_curr_week_id,
l_curr_week,
null,
null,
l_day_no,
decode(TRIM(TO_CHAR(l_curr_date,'DAY')),'SATURDAY','Y','SUNDAY','Y','N'),
l_curr_date,
l_curr_date,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='PLNG';
SELECT DDR_R_PLNG_YR_SEQ.NEXTVAL
INTO l_curr_year_id
FROM DUAL;
insert into DDR_R_PLNG_YR (
PLNG_YR_ID,
MFG_ORG_CD,
CLNDR_CD,
YR_CD,
YR_NBR,
YR_DESC,
YR_STRT_DT,
YR_END_DT,
YR_TIMESPN,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_year_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_year,
l_curr_year,
l_curr_year_desc,
l_curr_year_start_date,
l_curr_year_end_date,
l_no_year_days,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_PLNG_QTR_SEQ.NEXTVAL
INTO l_curr_qtr_id
FROM DUAL;
insert into DDR_R_PLNG_QTR (
PLNG_QTR_ID,
MFG_ORG_CD,
CLNDR_CD,
QTR_CD,
QTR_NBR,
QTR_DESC,
QTR_STRT_DT,
QTR_END_DT,
QTR_TIMESPN,
PLNG_YR_ID,
YR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_qtr_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_qtr,
l_curr_qtr,
l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
l_curr_qtr_start_date,
l_curr_qtr_end_date,
l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
l_curr_year_id,
l_curr_year,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
SELECT DDR_R_PLNG_PRD_SEQ.NEXTVAL
INTO l_curr_period_id
FROM DUAL;
insert into DDR_R_PLNG_PRD (
PLNG_PRD_ID,
MFG_ORG_CD,
CLNDR_CD,
PRD_CD,
PRD_NBR,
PRD_DESC,
PRD_STRT_DT,
PRD_END_DT,
PRD_TIMESPN,
PLNG_QTR_ID,
QTR_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_period_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_period,
l_curr_period,
l_curr_year_desc || ' P' || TO_CHAR(l_period_no),
l_curr_period_start_date,
l_curr_period_end_date,
l_curr_period_end_date - l_curr_period_start_date + 1,
l_curr_qtr_id,
l_curr_qtr,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
/* Insert Record into Week table */
l_week_no := l_week_no + 1;
SELECT DDR_R_PLNG_WK_SEQ.NEXTVAL
INTO l_curr_week_id
FROM DUAL;
insert into DDR_R_PLNG_WK (
PLNG_WK_ID,
MFG_ORG_CD,
CLNDR_CD,
WK_CD,
WK_NBR,
WK_DESC,
WK_STRT_DT,
WK_END_DT,
WK_TIMESPN,
PLNG_PRD_ID,
PRD_CD,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_curr_week_id,
l_mfg_org_cd,
l_clndr_cd,
l_curr_week,
l_curr_week,
l_curr_year_desc || ' W' || TO_CHAR(l_week_no),
l_curr_week_start_date,
l_curr_week_end_date,
l_curr_week_end_date - l_curr_week_start_date + 1,
l_curr_period_id,
l_curr_period,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);
/* Insert Records into Base Day table */
l_curr_date := l_curr_date - 1;
insert into DDR_R_BASE_DAY (
BASE_DAY_ID,
MFG_ORG_CD,
CLNDR_CD,
DAY_CD,
CLNDR_TYP,
WK_ID,
WK_CD,
MNTH_ID,
MNTH_CD,
DAY_OF_YR,
WKEND_IND,
CLNDR_STRT_DT,
CLNDR_END_DT,
SRC_SYS_IDNT,
SRC_SYS_DT,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
DDR_R_BASE_DAY_SEQ.NEXTVAL,
l_mfg_org_cd,
l_clndr_cd,
l_curr_day_id,
'PLNG',
l_curr_week_id,
l_curr_week,
null,
null,
l_day_no,
decode(TRIM(TO_CHAR(l_curr_date,'DAY')),'SATURDAY','Y','SUNDAY','Y','N'),
l_curr_date,
l_curr_date,
g_src_sys_idnt,
g_src_sys_dt,
g_crtd_by_DSR,
g_last_updt_by_DSR,
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login
);