[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;
delete from DDR_R_DAY_TRANS
where (DAY_CD,CLNDR_CD) in (
select DAY_CD,CLNDR_CD
from DDR_TIME_BSNS_DAY_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
insert into DDR_R_DAY_TRANS (
MFG_ORG_CD,
DAY_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_DAY_THIS_YR_CD,
LAST_WK_THIS_DAY_CD,
LAST_MNTH_THIS_DAY_CD,
LAST_PRD_THIS_DAY_CD,
LAST_QTR_THIS_DAY_CD,
LAST_YR_THIS_DAY_CD,
NXT_DAY_THIS_YR_CD,
NXT_WK_THIS_DAY_CD,
NXT_MNTH_THIS_DAY_CD,
NXT_PRD_THIS_DAY_CD,
NXT_QTR_THIS_DAY_CD,
NXT_YR_THIS_DAY_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
)
select
BDAY.MFG_ORG_CD,
DDR_R_DAY_TRANS_SEQ.NEXTVAL,
BDAY.DAY_CD,
BDAY.CLNDR_CD,
'BSNS',
PDAY.DAY_CD,
PWK.DAY_CD,
PMNTH.DAY_CD,
null,
PQTR.DAY_CD,
PYR.DAY_CD,
NDAY.DAY_CD,
NWK.DAY_CD,
NMNTH.DAY_CD,
null,
NQTR.DAY_CD,
NYR.DAY_CD,
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
from
DDR_TIME_BSNS_DAY_V BDAY,
DDR_TIME_BSNS_DAY_V PDAY,
DDR_TIME_BSNS_DAY_V PWK,
DDR_TIME_BSNS_DAY_V PMNTH,
DDR_TIME_BSNS_DAY_V PQTR,
DDR_TIME_BSNS_DAY_V PYR,
DDR_TIME_BSNS_DAY_V NDAY,
DDR_TIME_BSNS_DAY_V NWK,
DDR_TIME_BSNS_DAY_V NMNTH,
DDR_TIME_BSNS_DAY_V NQTR,
DDR_TIME_BSNS_DAY_V NYR
where BDAY.ORG_CD = p_org_cd
and BDAY.YR_CD between to_char(p_start_year) and to_char(p_end_year)
and PDAY.ORG_CD(+) = BDAY.ORG_CD
and PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
and PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
and PWK.ORG_CD(+) = BDAY.ORG_CD
and PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
and PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
and PMNTH.ORG_CD(+) = BDAY.ORG_CD
and PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
and PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
and PQTR.ORG_CD(+) = BDAY.ORG_CD
and PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
and PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
and PYR.ORG_CD(+) = BDAY.ORG_CD
and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and PYR.DAY_CD(+) = add_day(BDAY.DAY_CD,-364)
and NDAY.ORG_CD(+) = BDAY.ORG_CD
and NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
and NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
and NWK.ORG_CD(+) = BDAY.ORG_CD
and NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
and NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
and NMNTH.ORG_CD(+) = BDAY.ORG_CD
and NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
and NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
and NQTR.ORG_CD(+) = BDAY.ORG_CD
and NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
and NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
and NYR.ORG_CD(+) = BDAY.ORG_CD
and NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and NYR.DAY_CD(+) = add_day(BDAY.DAY_CD,364)
;
delete from DDR_R_WK_TRANS
where (WK_CD,CLNDR_CD) in (
select WK_CD,CLNDR_CD
from DDR_TIME_BSNS_WK_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
insert into DDR_R_WK_TRANS (
MFG_ORG_CD,
WK_TRANS_ID,
WK_ID,
WK_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_WK_THIS_YR_ID,
LAST_WK_THIS_YR_CD,
LAST_MNTH_THIS_WK_ID,
LAST_MNTH_THIS_WK_CD,
LAST_PRD_THIS_WK_ID,
LAST_PRD_THIS_WK_CD,
LAST_QTR_THIS_WK_ID,
LAST_QTR_THIS_WK_CD,
LAST_YR_THIS_WK_ID,
LAST_YR_THIS_WK_CD,
NXT_WK_THIS_YR_WK_ID,
NXT_WK_THIS_YR_WK_CD,
NXT_MNTH_THIS_WK_ID,
NXT_MNTH_THIS_WK_CD,
NXT_PRD_THIS_WK_ID,
NXT_PRD_THIS_WK_CD,
NXT_QTR_THIS_WK_ID,
NXT_QTR_THIS_WK_CD,
NXT_YR_THIS_WK_ID,
NXT_YR_THIS_WK_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
)
select
WK.MFG_ORG_CD,
DDR_R_WK_TRANS_SEQ.NEXTVAL,
WK.WK_ID,
WK.WK_CD,
WK.CLNDR_CD,
'BSNS',
PWK.WK_ID,
PWK.WK_CD,
PMNTH.WK_ID,
PMNTH.WK_CD,
null,
null,
PQTR.WK_ID,
PQTR.WK_CD,
PYR.WK_ID,
PYR.WK_CD,
NWK.WK_ID,
NWK.WK_CD,
NMNTH.WK_ID,
NMNTH.WK_CD,
null,
null,
NQTR.WK_ID,
NQTR.WK_CD,
NYR.WK_ID,
NYR.WK_CD,
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
from
DDR_TIME_BSNS_WK_V WK,
DDR_TIME_BSNS_WK_V PWK,
DDR_TIME_BSNS_WK_V PMNTH,
DDR_TIME_BSNS_WK_V PQTR,
DDR_TIME_BSNS_WK_V PYR,
DDR_TIME_BSNS_WK_V NWK,
DDR_TIME_BSNS_WK_V NMNTH,
DDR_TIME_BSNS_WK_V NQTR,
DDR_TIME_BSNS_WK_V NYR
where WK.ORG_CD = p_org_cd
and WK.YR_CD between to_char(p_start_year) and to_char(p_end_year)
and PWK.ORG_CD(+) = WK.ORG_CD
and PWK.CLNDR_CD(+) = WK.CLNDR_CD
and PWK.WK_CD(+) = add_week(WK.WK_CD,-1)
and PMNTH.ORG_CD(+) = WK.ORG_CD
and PMNTH.CLNDR_CD(+) = WK.CLNDR_CD
and PMNTH.WK_CD(+) = add_week(WK.WK_CD,-4)
and PQTR.ORG_CD(+) = WK.ORG_CD
and PQTR.CLNDR_CD(+) = WK.CLNDR_CD
and PQTR.WK_CD(+) = add_week(WK.WK_CD,-13)
and PYR.ORG_CD(+) = WK.ORG_CD
and PYR.CLNDR_CD(+) = WK.CLNDR_CD
and PYR.WK_CD(+) = add_week(WK.WK_CD,-52)
and NWK.ORG_CD(+) = WK.ORG_CD
and NWK.CLNDR_CD(+) = WK.CLNDR_CD
and NWK.WK_CD(+) = add_week(WK.WK_CD,1)
and NMNTH.ORG_CD(+) = WK.ORG_CD
and NMNTH.CLNDR_CD(+) = WK.CLNDR_CD
and NMNTH.WK_CD(+) = add_week(WK.WK_CD,4)
and NQTR.ORG_CD(+) = WK.ORG_CD
and NQTR.CLNDR_CD(+) = WK.CLNDR_CD
and NQTR.WK_CD(+) = add_week(WK.WK_CD,13)
and NYR.ORG_CD(+) = WK.ORG_CD
and NYR.CLNDR_CD(+) = WK.CLNDR_CD
and NYR.WK_CD(+) = add_week(WK.WK_CD,52)
;
delete from DDR_R_MNTH_TRANS
where (MNTH_CD,CLNDR_CD) in (
select MNTH_CD,CLNDR_CD
from DDR_TIME_BSNS_MNTH_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
insert into DDR_R_MNTH_TRANS (
MFG_ORG_CD,
MNTH_TRANS_ID,
MNTH_ID,
MNTH_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_MNTH_THIS_YR_ID,
LAST_MNTH_THIS_YR_CD,
LAST_QTR_THIS_MNTH_ID,
LAST_QTR_THIS_MNTH_CD,
LAST_YR_THIS_MNTH_ID,
LAST_YR_THIS_MNTH_CD,
NXT_MNTH_THIS_YR_ID,
NXT_MNTH_THIS_YR_CD,
NXT_QTR_THIS_MNTH_ID,
NXT_QTR_THIS_MNTH_CD,
NXT_YR_THIS_MNTH_ID,
NXT_YR_THIS_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
)
select
MNTH.MFG_ORG_CD,
DDR_R_MNTH_TRANS_SEQ.NEXTVAL,
MNTH.MNTH_ID,
MNTH.MNTH_CD,
MNTH.CLNDR_CD,
'BSNS',
PMNTH.MNTH_ID,
PMNTH.MNTH_CD,
PQTR.MNTH_ID,
PQTR.MNTH_CD,
PYR.MNTH_ID,
PYR.MNTH_CD,
NMNTH.MNTH_ID,
NMNTH.MNTH_CD,
NQTR.MNTH_ID,
NQTR.MNTH_CD,
NYR.MNTH_ID,
NYR.MNTH_CD,
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
from
DDR_TIME_BSNS_MNTH_V MNTH,
DDR_TIME_BSNS_MNTH_V PMNTH,
DDR_TIME_BSNS_MNTH_V PQTR,
DDR_TIME_BSNS_MNTH_V PYR,
DDR_TIME_BSNS_MNTH_V NMNTH,
DDR_TIME_BSNS_MNTH_V NQTR,
DDR_TIME_BSNS_MNTH_V NYR
where MNTH.ORG_CD = p_org_cd
and MNTH.YR_CD between to_char(p_start_year) and to_char(p_end_year)
and PMNTH.ORG_CD(+) = MNTH.ORG_CD
and PMNTH.CLNDR_CD(+) = MNTH.CLNDR_CD
and PMNTH.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-1)
and PQTR.ORG_CD(+) = MNTH.ORG_CD
and PQTR.CLNDR_CD(+) = MNTH.CLNDR_CD
and PQTR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-3)
and PYR.ORG_CD(+) = MNTH.ORG_CD
and PYR.CLNDR_CD(+) = MNTH.CLNDR_CD
and PYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-12)
and NMNTH.ORG_CD(+) = MNTH.ORG_CD
and NMNTH.CLNDR_CD(+) = MNTH.CLNDR_CD
and NMNTH.MNTH_CD(+) = add_month(MNTH.MNTH_CD,1)
and NQTR.ORG_CD(+) = MNTH.ORG_CD
and NQTR.CLNDR_CD(+) = MNTH.CLNDR_CD
and NQTR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,3)
and NYR.ORG_CD(+) = MNTH.ORG_CD
and NYR.CLNDR_CD(+) = MNTH.CLNDR_CD
and NYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,12)
;
delete from DDR_R_QTR_TRANS
where (QTR_CD,CLNDR_CD) in (
select QTR_CD,CLNDR_CD
from DDR_TIME_BSNS_QTR_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
insert into DDR_R_QTR_TRANS (
MFG_ORG_CD,
QTR_TRANS_ID,
QTR_ID,
QTR_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_QTR_THIS_YR_ID,
LAST_QTR_THIS_YR_CD,
LAST_YR_THIS_QTR_ID,
LAST_YR_THIS_QTR_CD,
NXT_QTR_THIS_YR_ID,
NXT_QTR_THIS_YR_CD,
NXT_YR_THIS_QTR_ID,
NXT_YR_THIS_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
)
select
QTR.MFG_ORG_CD,
DDR_R_QTR_TRANS_SEQ.NEXTVAL,
QTR.QTR_ID,
QTR.QTR_CD,
QTR.CLNDR_CD,
'BSNS',
PQTR.QTR_ID,
PQTR.QTR_CD,
PYR.QTR_ID,
PYR.QTR_CD,
NQTR.QTR_ID,
NQTR.QTR_CD,
NYR.QTR_ID,
NYR.QTR_CD,
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
from
DDR_TIME_BSNS_QTR_V QTR,
DDR_TIME_BSNS_QTR_V PQTR,
DDR_TIME_BSNS_QTR_V PYR,
DDR_TIME_BSNS_QTR_V NQTR,
DDR_TIME_BSNS_QTR_V NYR
where QTR.ORG_CD = p_org_cd
and QTR.YR_CD between to_char(p_start_year) and to_char(p_end_year)
and PQTR.ORG_CD(+) = QTR.ORG_CD
and PQTR.CLNDR_CD(+) = QTR.CLNDR_CD
and PQTR.QTR_CD(+) = add_quarter(QTR.QTR_CD,-1)
and PYR.ORG_CD(+) = QTR.ORG_CD
and PYR.CLNDR_CD(+) = QTR.CLNDR_CD
and PYR.QTR_CD(+) = add_quarter(QTR.QTR_CD,-4)
and NQTR.ORG_CD(+) = QTR.ORG_CD
and NQTR.CLNDR_CD(+) = QTR.CLNDR_CD
and NQTR.QTR_CD(+) = add_quarter(QTR.QTR_CD,1)
and NYR.ORG_CD(+) = QTR.ORG_CD
and NYR.CLNDR_CD(+) = QTR.CLNDR_CD
and NYR.QTR_CD(+) = add_quarter(QTR.QTR_CD,4)
;
delete from DDR_R_YR_TRANS
where (YR_CD,CLNDR_CD) in (
select YR_CD,CLNDR_CD
from DDR_TIME_BSNS_YR_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
insert into DDR_R_YR_TRANS (
MFG_ORG_CD,
YR_TRANS_ID,
YR_ID,
YR_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_YR_ID,
LAST_YR_CD,
NXT_YR_ID,
NXT_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
)
select
YR.MFG_ORG_CD,
DDR_R_YR_TRANS_SEQ.NEXTVAL,
YR.YR_ID,
YR.YR_CD,
YR.CLNDR_CD,
'BSNS',
PYR.YR_ID,
PYR.YR_CD,
NYR.YR_ID,
NYR.YR_CD,
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
from
DDR_TIME_BSNS_YR_V YR,
DDR_TIME_BSNS_YR_V PYR,
DDR_TIME_BSNS_YR_V NYR
where YR.ORG_CD = p_org_cd
and YR.YR_CD between to_char(p_start_year) and to_char(p_end_year)
and PYR.ORG_CD(+) = YR.ORG_CD
and PYR.CLNDR_CD(+) = YR.CLNDR_CD
and PYR.YR_CD(+) = YR.YR_CD - 1
and NYR.ORG_CD(+) = YR.ORG_CD
and NYR.CLNDR_CD(+) = YR.CLNDR_CD
and NYR.YR_CD(+) = YR.YR_CD + 1
;
delete from DDR_R_DAY_TODATE_TRANS
where (DAY_CD,CLNDR_CD) in (
select DAY_CD,CLNDR_CD
from DDR_TIME_BSNS_DAY_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
INSERT INTO DDR_R_DAY_TODATE_TRANS(
MFG_ORG_CD,
DAY_TODATE_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
YR_DAY_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
)
select
YTD.MFG_ORG_CD,
DDR_R_DAY_TODATE_TRANS_SEQ.NEXTVAL,
YTD.DAY_CD,
YTD.CLNDR_CD,
'BSNS',
YTD.YR_DAY_CD,
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
FROM
(SELECT
A.MFG_ORG_CD MFG_ORG_CD,
A.DAY_CD DAY_CD,
A.CLNDR_CD CLNDR_CD,
B.DAY_CD YR_DAY_CD
FROM DDR_TIME_BSNS_DAY_V A,
DDR_TIME_BSNS_DAY_V B
WHERE A.YR_CD = B.YR_CD
AND A.DAY_CD >= B.DAY_CD
AND A.ORG_CD = B.ORG_CD
AND A.ORG_CD = p_org_cd
AND A.YR_CD between to_char(p_start_year) and to_char(p_end_year)
ORDER BY A.DAY_CD,
B.DAY_CD) YTD;
delete from DDR_R_WK_TODATE_TRANS
where (WK_CD,CLNDR_CD) in (
select WK_CD,CLNDR_CD
from DDR_TIME_BSNS_WK_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
INSERT INTO DDR_R_WK_TODATE_TRANS(
MFG_ORG_CD,
WK_TODATE_TRANS_ID,
WK_ID,
WK_CD,
CLNDR_CD,
CLNDR_TYP,
YR_WK_ID,
YR_WK_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
)
select
YTD.MFG_ORG_CD,
DDR_R_WK_TODATE_TRANS_SEQ.NEXTVAL,
YTD.WK_ID,
YTD.WK_CD,
YTD.CLNDR_CD,
'BSNS',
YTD.YR_WK_ID,
YTD.YR_WK_CD,
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
FROM
(SELECT
A.MFG_ORG_CD MFG_ORG_CD,
A.WK_CD WK_CD,
A.WK_ID WK_ID,
A.CLNDR_CD CLNDR_CD,
B.WK_CD YR_WK_CD,
B.WK_ID YR_WK_ID
FROM DDR_TIME_BSNS_WK_V A,
DDR_TIME_BSNS_WK_V B
WHERE A.YR_CD = B.YR_CD
AND A.WK_CD >= B.WK_CD
AND A.ORG_CD = B.ORG_CD
AND A.ORG_CD = p_org_cd
AND A.YR_CD between to_char(p_start_year) and to_char(p_end_year)
ORDER BY A.WK_CD,
B.WK_CD) YTD;
delete from DDR_R_MNTH_TODATE_TRANS
where (MNTH_CD,CLNDR_CD) in (
select MNTH_CD,CLNDR_CD
from DDR_TIME_BSNS_MNTH_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
INSERT INTO DDR_R_MNTH_TODATE_TRANS(
MFG_ORG_CD,
MNTH_TODATE_TRANS_ID,
MNTH_ID,
MNTH_CD,
CLNDR_CD,
CLNDR_TYP,
YR_MNTH_ID,
YR_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
)
select
YTD.MFG_ORG_CD,
DDR_R_MNTH_TODATE_TRANS_SEQ.NEXTVAL,
YTD.MNTH_ID,
YTD.MNTH_CD,
YTD.CLNDR_CD,
'BSNS',
YTD.YR_MNTH_ID,
YTD.YR_MNTH_CD,
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
FROM
(SELECT
A.MFG_ORG_CD MFG_ORG_CD,
A.MNTH_CD MNTH_CD,
A.MNTH_ID MNTH_ID,
A.CLNDR_CD CLNDR_CD,
B.MNTH_CD YR_MNTH_CD,
B.MNTH_ID YR_MNTH_ID
FROM DDR_TIME_BSNS_MNTH_V A,
DDR_TIME_BSNS_MNTH_V B
WHERE A.YR_CD = B.YR_CD
AND A.MNTH_CD >= B.MNTH_CD
AND A.ORG_CD = B.ORG_CD
AND A.ORG_CD = p_org_cd
AND A.YR_CD between to_char(p_start_year) and to_char(p_end_year)
ORDER BY A.MNTH_CD,
B.MNTH_CD) YTD;
delete from DDR_R_QTR_TODATE_TRANS
where (QTR_CD,CLNDR_CD) in (
select QTR_CD,CLNDR_CD
from DDR_TIME_BSNS_QTR_V
where ORG_CD = p_org_cd
and YR_CD between to_char(p_start_year) and to_char(p_end_year)
);
INSERT INTO DDR_R_QTR_TODATE_TRANS(
MFG_ORG_CD,
QTR_TODATE_TRANS_ID,
QTR_ID,
QTR_CD,
CLNDR_CD,
CLNDR_TYP,
YR_QTR_ID,
YR_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
)
select
YTD.MFG_ORG_CD,
DDR_R_QTR_TODATE_TRANS_SEQ.NEXTVAL,
YTD.QTR_ID,
YTD.QTR_CD,
YTD.CLNDR_CD,
'BSNS',
YTD.YR_QTR_ID,
YTD.YR_QTR_CD,
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
FROM
(SELECT
A.MFG_ORG_CD MFG_ORG_CD,
A.QTR_CD QTR_CD,
A.QTR_ID QTR_ID,
A.CLNDR_CD CLNDR_CD,
B.QTR_CD YR_QTR_CD,
B.QTR_ID YR_QTR_ID
FROM DDR_TIME_BSNS_QTR_V A,
DDR_TIME_BSNS_QTR_V B
WHERE A.YR_CD = B.YR_CD
AND A.QTR_CD >= B.QTR_CD
AND A.ORG_CD = B.ORG_CD
AND A.ORG_CD = p_org_cd
AND A.YR_CD between to_char(p_start_year) and to_char(p_end_year)
ORDER BY A.QTR_CD,
B.QTR_CD) YTD;
DELETE
FROM DDR_R_DAY_TRANS
WHERE (DAY_CD,CLNDR_CD) IN
(SELECT DAY_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_DAY_V
WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
);
select YR_TIMESPN
into V_YR_TIMESPN
from DDR_R_CLNDR_YR
where YR_CD = V_START_YEAR;
INSERT
INTO DDR_R_DAY_TRANS
(
MFG_ORG_CD,
DAY_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_DAY_THIS_YR_CD,
LAST_WK_THIS_DAY_CD,
LAST_MNTH_THIS_DAY_CD,
LAST_PRD_THIS_DAY_CD,
LAST_QTR_THIS_DAY_CD,
LAST_YR_THIS_DAY_CD,
NXT_DAY_THIS_YR_CD,
NXT_WK_THIS_DAY_CD,
NXT_MNTH_THIS_DAY_CD,
NXT_PRD_THIS_DAY_CD,
NXT_QTR_THIS_DAY_CD,
NXT_YR_THIS_DAY_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
)
SELECT BDAY.ORG_CD,
DDR_R_DAY_TRANS_SEQ.NEXTVAL,
BDAY.DAY_CD,
BDAY.CLNDR_CD,
BDAY.CLNDR_TYP,
PDAY.DAY_CD,
PWK.DAY_CD,
PMNTH.DAY_CD,
NULL,
PQTR.DAY_CD,
PYR.DAY_CD,
NDAY.DAY_CD,
NWK.DAY_CD,
NMNTH.DAY_CD,
NULL,
NQTR.DAY_CD,
NYR.DAY_CD,
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
FROM DDR_TIME_CLNDR_DAY_V BDAY,
DDR_TIME_CLNDR_DAY_V PDAY,
DDR_TIME_CLNDR_DAY_V PWK,
DDR_TIME_CLNDR_DAY_V PMNTH,
DDR_TIME_CLNDR_DAY_V PQTR,
DDR_TIME_CLNDR_DAY_V PYR,
DDR_TIME_CLNDR_DAY_V NDAY,
DDR_TIME_CLNDR_DAY_V NWK,
DDR_TIME_CLNDR_DAY_V NMNTH,
DDR_TIME_CLNDR_DAY_V NQTR,
DDR_TIME_CLNDR_DAY_V NYR
where BDAY.YR_CD = TO_CHAR(v_start_year)
AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN-1))
AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
and BDAY.DAY_CD < V_START_YEAR||'0229';
INSERT
INTO DDR_R_DAY_TRANS
(
MFG_ORG_CD,
DAY_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_DAY_THIS_YR_CD,
LAST_WK_THIS_DAY_CD,
LAST_MNTH_THIS_DAY_CD,
LAST_PRD_THIS_DAY_CD,
LAST_QTR_THIS_DAY_CD,
LAST_YR_THIS_DAY_CD,
NXT_DAY_THIS_YR_CD,
NXT_WK_THIS_DAY_CD,
NXT_MNTH_THIS_DAY_CD,
NXT_PRD_THIS_DAY_CD,
NXT_QTR_THIS_DAY_CD,
NXT_YR_THIS_DAY_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
)
SELECT BDAY.ORG_CD,
DDR_R_DAY_TRANS_SEQ.NEXTVAL,
BDAY.DAY_CD,
BDAY.CLNDR_CD,
BDAY.CLNDR_TYP,
PDAY.DAY_CD,
PWK.DAY_CD,
PMNTH.DAY_CD,
NULL,
PQTR.DAY_CD,
NULL,
NDAY.DAY_CD,
NWK.DAY_CD,
NMNTH.DAY_CD,
NULL,
NQTR.DAY_CD,
NULL,
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
FROM DDR_TIME_CLNDR_DAY_V BDAY,
DDR_TIME_CLNDR_DAY_V PDAY,
DDR_TIME_CLNDR_DAY_V PWK,
DDR_TIME_CLNDR_DAY_V PMNTH,
DDR_TIME_CLNDR_DAY_V PQTR,
DDR_TIME_CLNDR_DAY_V PYR,
DDR_TIME_CLNDR_DAY_V NDAY,
DDR_TIME_CLNDR_DAY_V NWK,
DDR_TIME_CLNDR_DAY_V NMNTH,
DDR_TIME_CLNDR_DAY_V NQTR,
DDR_TIME_CLNDR_DAY_V NYR
where BDAY.YR_CD = TO_CHAR(v_start_year)
AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN-1))
AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
and BDAY.DAY_CD = V_START_YEAR||'0229';
INSERT
INTO DDR_R_DAY_TRANS
(
MFG_ORG_CD,
DAY_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_DAY_THIS_YR_CD,
LAST_WK_THIS_DAY_CD,
LAST_MNTH_THIS_DAY_CD,
LAST_PRD_THIS_DAY_CD,
LAST_QTR_THIS_DAY_CD,
LAST_YR_THIS_DAY_CD,
NXT_DAY_THIS_YR_CD,
NXT_WK_THIS_DAY_CD,
NXT_MNTH_THIS_DAY_CD,
NXT_PRD_THIS_DAY_CD,
NXT_QTR_THIS_DAY_CD,
NXT_YR_THIS_DAY_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
)
SELECT BDAY.ORG_CD,
DDR_R_DAY_TRANS_SEQ.NEXTVAL,
BDAY.DAY_CD,
BDAY.CLNDR_CD,
BDAY.CLNDR_TYP,
PDAY.DAY_CD,
PWK.DAY_CD,
PMNTH.DAY_CD,
NULL,
PQTR.DAY_CD,
PYR.DAY_CD,
NDAY.DAY_CD,
NWK.DAY_CD,
NMNTH.DAY_CD,
NULL,
NQTR.DAY_CD,
NYR.DAY_CD,
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
FROM DDR_TIME_CLNDR_DAY_V BDAY,
DDR_TIME_CLNDR_DAY_V PDAY,
DDR_TIME_CLNDR_DAY_V PWK,
DDR_TIME_CLNDR_DAY_V PMNTH,
DDR_TIME_CLNDR_DAY_V PQTR,
DDR_TIME_CLNDR_DAY_V PYR,
DDR_TIME_CLNDR_DAY_V NDAY,
DDR_TIME_CLNDR_DAY_V NWK,
DDR_TIME_CLNDR_DAY_V NMNTH,
DDR_TIME_CLNDR_DAY_V NQTR,
DDR_TIME_CLNDR_DAY_V NYR
where BDAY.YR_CD = TO_CHAR(v_start_year)
AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN))
AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
and BDAY.DAY_CD > V_START_YEAR||'0229';
INSERT
INTO DDR_R_DAY_TRANS
(
MFG_ORG_CD,
DAY_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_DAY_THIS_YR_CD,
LAST_WK_THIS_DAY_CD,
LAST_MNTH_THIS_DAY_CD,
LAST_PRD_THIS_DAY_CD,
LAST_QTR_THIS_DAY_CD,
LAST_YR_THIS_DAY_CD,
NXT_DAY_THIS_YR_CD,
NXT_WK_THIS_DAY_CD,
NXT_MNTH_THIS_DAY_CD,
NXT_PRD_THIS_DAY_CD,
NXT_QTR_THIS_DAY_CD,
NXT_YR_THIS_DAY_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
)
SELECT BDAY.ORG_CD,
DDR_R_DAY_TRANS_SEQ.NEXTVAL,
BDAY.DAY_CD,
BDAY.CLNDR_CD,
BDAY.CLNDR_TYP,
PDAY.DAY_CD,
PWK.DAY_CD,
PMNTH.DAY_CD,
NULL,
PQTR.DAY_CD,
PYR.DAY_CD,
NDAY.DAY_CD,
NWK.DAY_CD,
NMNTH.DAY_CD,
NULL,
NQTR.DAY_CD,
NYR.DAY_CD,
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
FROM DDR_TIME_CLNDR_DAY_V BDAY,
DDR_TIME_CLNDR_DAY_V PDAY,
DDR_TIME_CLNDR_DAY_V PWK,
DDR_TIME_CLNDR_DAY_V PMNTH,
DDR_TIME_CLNDR_DAY_V PQTR,
DDR_TIME_CLNDR_DAY_V PYR,
DDR_TIME_CLNDR_DAY_V NDAY,
DDR_TIME_CLNDR_DAY_V NWK,
DDR_TIME_CLNDR_DAY_V NMNTH,
DDR_TIME_CLNDR_DAY_V NQTR,
DDR_TIME_CLNDR_DAY_V NYR
where BDAY.YR_CD = TO_CHAR(v_start_year)
AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN+1))
AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
and BDAY.DAY_CD <= V_START_YEAR||'0228';
INSERT
INTO DDR_R_DAY_TRANS
(
MFG_ORG_CD,
DAY_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_DAY_THIS_YR_CD,
LAST_WK_THIS_DAY_CD,
LAST_MNTH_THIS_DAY_CD,
LAST_PRD_THIS_DAY_CD,
LAST_QTR_THIS_DAY_CD,
LAST_YR_THIS_DAY_CD,
NXT_DAY_THIS_YR_CD,
NXT_WK_THIS_DAY_CD,
NXT_MNTH_THIS_DAY_CD,
NXT_PRD_THIS_DAY_CD,
NXT_QTR_THIS_DAY_CD,
NXT_YR_THIS_DAY_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
)
SELECT BDAY.ORG_CD,
DDR_R_DAY_TRANS_SEQ.NEXTVAL,
BDAY.DAY_CD,
BDAY.CLNDR_CD,
BDAY.CLNDR_TYP,
PDAY.DAY_CD,
PWK.DAY_CD,
PMNTH.DAY_CD,
NULL,
PQTR.DAY_CD,
PYR.DAY_CD,
NDAY.DAY_CD,
NWK.DAY_CD,
NMNTH.DAY_CD,
NULL,
NQTR.DAY_CD,
NYR.DAY_CD,
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
FROM DDR_TIME_CLNDR_DAY_V BDAY,
DDR_TIME_CLNDR_DAY_V PDAY,
DDR_TIME_CLNDR_DAY_V PWK,
DDR_TIME_CLNDR_DAY_V PMNTH,
DDR_TIME_CLNDR_DAY_V PQTR,
DDR_TIME_CLNDR_DAY_V PYR,
DDR_TIME_CLNDR_DAY_V NDAY,
DDR_TIME_CLNDR_DAY_V NWK,
DDR_TIME_CLNDR_DAY_V NMNTH,
DDR_TIME_CLNDR_DAY_V NQTR,
DDR_TIME_CLNDR_DAY_V NYR
where BDAY.YR_CD = TO_CHAR(v_start_year)
AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN))
AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
and BDAY.DAY_CD > V_START_YEAR||'0228';
INSERT
INTO DDR_R_DAY_TRANS
(
MFG_ORG_CD,
DAY_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_DAY_THIS_YR_CD,
LAST_WK_THIS_DAY_CD,
LAST_MNTH_THIS_DAY_CD,
LAST_PRD_THIS_DAY_CD,
LAST_QTR_THIS_DAY_CD,
LAST_YR_THIS_DAY_CD,
NXT_DAY_THIS_YR_CD,
NXT_WK_THIS_DAY_CD,
NXT_MNTH_THIS_DAY_CD,
NXT_PRD_THIS_DAY_CD,
NXT_QTR_THIS_DAY_CD,
NXT_YR_THIS_DAY_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
)
SELECT BDAY.ORG_CD,
DDR_R_DAY_TRANS_SEQ.NEXTVAL,
BDAY.DAY_CD,
BDAY.CLNDR_CD,
BDAY.CLNDR_TYP,
PDAY.DAY_CD,
PWK.DAY_CD,
PMNTH.DAY_CD,
NULL,
PQTR.DAY_CD,
PYR.DAY_CD,
NDAY.DAY_CD,
NWK.DAY_CD,
NMNTH.DAY_CD,
NULL,
NQTR.DAY_CD,
NYR.DAY_CD,
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
FROM DDR_TIME_CLNDR_DAY_V BDAY,
DDR_TIME_CLNDR_DAY_V PDAY,
DDR_TIME_CLNDR_DAY_V PWK,
DDR_TIME_CLNDR_DAY_V PMNTH,
DDR_TIME_CLNDR_DAY_V PQTR,
DDR_TIME_CLNDR_DAY_V PYR,
DDR_TIME_CLNDR_DAY_V NDAY,
DDR_TIME_CLNDR_DAY_V NWK,
DDR_TIME_CLNDR_DAY_V NMNTH,
DDR_TIME_CLNDR_DAY_V NQTR,
DDR_TIME_CLNDR_DAY_V NYR
where BDAY.YR_CD = TO_CHAR(v_start_year)
AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN))
AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
and NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN) ;
DELETE
FROM DDR_R_WK_TRANS
WHERE (WK_CD,CLNDR_CD) IN
(SELECT WK_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_WK_V
WHERE SUBSTR(WK_CD,1,4) BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
);
INSERT
INTO DDR_R_WK_TRANS
(
MFG_ORG_CD,
WK_TRANS_ID,
WK_ID,
WK_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_WK_THIS_YR_ID,
LAST_WK_THIS_YR_CD,
/* LAST_MNTH_THIS_WK_ID,
LAST_MNTH_THIS_WK_CD,
LAST_PRD_THIS_WK_ID,
LAST_PRD_THIS_WK_CD,
LAST_QTR_THIS_WK_ID,
LAST_QTR_THIS_WK_CD,*/
LAST_YR_THIS_WK_ID,
LAST_YR_THIS_WK_CD,
NXT_WK_THIS_YR_WK_ID,
NXT_WK_THIS_YR_WK_CD,
/* NXT_MNTH_THIS_WK_ID,
NXT_MNTH_THIS_WK_CD,
NXT_PRD_THIS_WK_ID,
NXT_PRD_THIS_WK_CD,
NXT_QTR_THIS_WK_ID,
NXT_QTR_THIS_WK_CD, */
NXT_YR_THIS_WK_ID,
NXT_YR_THIS_WK_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
)
SELECT WK.ORG_CD,
DDR_R_WK_TRANS_SEQ.NEXTVAL,
WK.CLNDR_WK_ID,
WK.WK_CD,
WK.CLNDR_CD,
WK.CLNDR_TYP,
PWK.CLNDR_WK_ID,
PWK.WK_CD,
/* PMNTH.CLNDR_WK_ID,
PMNTH.WK_CD,
null,
null,
PQTR.CLNDR_WK_ID,
PQTR.WK_CD, */
PYR.CLNDR_WK_ID,
PYR.WK_CD,
NWK.CLNDR_WK_ID,
NWK.WK_CD,
/* NMNTH.CLNDR_WK_ID,
NMNTH.WK_CD,
null,
null,
NQTR.CLNDR_WK_ID,
NQTR.WK_CD, */
NYR.CLNDR_WK_ID,
NYR.WK_CD,
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
FROM DDR_TIME_CLNDR_WK_V WK,
DDR_TIME_CLNDR_WK_V PWK,
DDR_TIME_CLNDR_WK_V PMNTH,
DDR_TIME_CLNDR_WK_V PQTR,
DDR_TIME_CLNDR_WK_V PYR,
DDR_TIME_CLNDR_WK_V NWK,
DDR_TIME_CLNDR_WK_V NMNTH,
DDR_TIME_CLNDR_WK_V NQTR,
DDR_TIME_CLNDR_WK_V NYR
WHERE PWK.CLNDR_CD(+) = WK.CLNDR_CD
AND PWK.WK_CD(+) = add_week(WK.WK_CD,-1)
AND PMNTH.CLNDR_CD(+) = WK.CLNDR_CD
AND PMNTH.WK_CD(+) = add_week(WK.WK_CD,-4)
AND PQTR.CLNDR_CD(+) = WK.CLNDR_CD
AND PQTR.WK_CD(+) = add_week(WK.WK_CD,-13)
AND PYR.CLNDR_CD(+) = WK.CLNDR_CD
AND PYR.WK_CD(+) = add_week(WK.WK_CD,-52)
AND NWK.CLNDR_CD(+) = WK.CLNDR_CD
AND NWK.WK_CD(+) = add_week(WK.WK_CD,1)
AND NMNTH.CLNDR_CD(+) = WK.CLNDR_CD
AND NMNTH.WK_CD(+) = add_week(WK.WK_CD,4)
AND NQTR.CLNDR_CD(+) = WK.CLNDR_CD
AND NQTR.WK_CD(+) = add_week(WK.WK_CD,13)
AND NYR.CLNDR_CD(+) = WK.CLNDR_CD
AND NYR.WK_CD(+) = ADD_WEEK(WK.WK_CD,52)
AND SUBSTR(WK.WK_CD,1,4) BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
;
DELETE
FROM DDR_R_MNTH_TRANS
WHERE (MNTH_CD,CLNDR_CD) IN
(SELECT MNTH_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_MNTH_V
WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
);
INSERT
INTO DDR_R_MNTH_TRANS
(
MFG_ORG_CD,
MNTH_TRANS_ID,
MNTH_ID,
MNTH_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_MNTH_THIS_YR_ID,
LAST_MNTH_THIS_YR_CD,
LAST_QTR_THIS_MNTH_ID,
LAST_QTR_THIS_MNTH_CD,
LAST_YR_THIS_MNTH_ID,
LAST_YR_THIS_MNTH_CD,
NXT_MNTH_THIS_YR_ID,
NXT_MNTH_THIS_YR_CD,
NXT_QTR_THIS_MNTH_ID,
NXT_QTR_THIS_MNTH_CD,
NXT_YR_THIS_MNTH_ID,
NXT_YR_THIS_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
)
SELECT MNTH.ORG_CD,
DDR_R_MNTH_TRANS_SEQ.NEXTVAL,
MNTH.CLNDR_MNTH_ID,
MNTH.MNTH_CD,
MNTH.CLNDR_CD,
MNTH.CLNDR_TYP,
PMNTH.CLNDR_MNTH_ID,
PMNTH.MNTH_CD,
PQTR.CLNDR_MNTH_ID,
PQTR.MNTH_CD,
PYR.CLNDR_MNTH_ID,
PYR.MNTH_CD,
NMNTH.CLNDR_MNTH_ID,
NMNTH.MNTH_CD,
NQTR.CLNDR_MNTH_ID,
NQTR.MNTH_CD,
NYR.CLNDR_MNTH_ID,
NYR.MNTH_CD,
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
FROM DDR_TIME_CLNDR_MNTH_V MNTH,
DDR_TIME_CLNDR_MNTH_V PMNTH,
DDR_TIME_CLNDR_MNTH_V PQTR,
DDR_TIME_CLNDR_MNTH_V PYR,
DDR_TIME_CLNDR_MNTH_V NMNTH,
DDR_TIME_CLNDR_MNTH_V NQTR,
DDR_TIME_CLNDR_MNTH_V NYR
WHERE MNTH.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
AND PMNTH.CLNDR_CD(+) = MNTH.CLNDR_CD
AND PMNTH.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-1)
AND PQTR.CLNDR_CD(+) = MNTH.CLNDR_CD
AND PQTR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-3)
AND PYR.CLNDR_CD(+) = MNTH.CLNDR_CD
AND PYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-12)
AND NMNTH.CLNDR_CD(+) = MNTH.CLNDR_CD
AND NMNTH.MNTH_CD(+) = add_month(MNTH.MNTH_CD,1)
AND NQTR.CLNDR_CD(+) = MNTH.CLNDR_CD
AND NQTR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,3)
AND NYR.CLNDR_CD(+) = MNTH.CLNDR_CD
AND NYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,12) ;
DELETE
FROM DDR_R_QTR_TRANS
WHERE (QTR_CD,CLNDR_CD) IN
(SELECT QTR_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_QTR_V
WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
);
INSERT
INTO DDR_R_QTR_TRANS
(
MFG_ORG_CD,
QTR_TRANS_ID,
QTR_ID,
QTR_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_QTR_THIS_YR_ID,
LAST_QTR_THIS_YR_CD,
LAST_YR_THIS_QTR_ID,
LAST_YR_THIS_QTR_CD,
NXT_QTR_THIS_YR_ID,
NXT_QTR_THIS_YR_CD,
NXT_YR_THIS_QTR_ID,
NXT_YR_THIS_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
)
SELECT QTR.ORG_CD,
DDR_R_QTR_TRANS_SEQ.NEXTVAL,
QTR.CLNDR_QTR_ID,
QTR.QTR_CD,
QTR.CLNDR_CD,
QTR.CLNDR_TYP,
PQTR.CLNDR_QTR_ID,
PQTR.QTR_CD,
PYR.CLNDR_QTR_ID,
PYR.QTR_CD,
NQTR.CLNDR_QTR_ID,
NQTR.QTR_CD,
NYR.CLNDR_QTR_ID,
NYR.QTR_CD,
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
FROM DDR_TIME_CLNDR_QTR_V QTR,
DDR_TIME_CLNDR_QTR_V PQTR,
DDR_TIME_CLNDR_QTR_V PYR,
DDR_TIME_CLNDR_QTR_V NQTR,
DDR_TIME_CLNDR_QTR_V NYR
WHERE QTR.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
AND PQTR.ORG_CD(+) = QTR.ORG_CD
AND PQTR.CLNDR_CD(+) = QTR.CLNDR_CD
AND PQTR.QTR_CD(+) = add_quarter(QTR.QTR_CD,-1)
AND PYR.ORG_CD(+) = QTR.ORG_CD
AND PYR.CLNDR_CD(+) = QTR.CLNDR_CD
AND PYR.QTR_CD(+) = add_quarter(QTR.QTR_CD,-4)
AND NQTR.ORG_CD(+) = QTR.ORG_CD
AND NQTR.CLNDR_CD(+) = QTR.CLNDR_CD
AND NQTR.QTR_CD(+) = add_quarter(QTR.QTR_CD,1)
AND NYR.ORG_CD(+) = QTR.ORG_CD
AND NYR.CLNDR_CD(+) = QTR.CLNDR_CD
AND NYR.QTR_CD(+) = add_quarter(QTR.QTR_CD,4) ;
DELETE
FROM DDR_R_YR_TRANS
WHERE (YR_CD,CLNDR_CD) IN
(SELECT YR_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_YR_V
WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
);
INSERT
INTO DDR_R_YR_TRANS
(
MFG_ORG_CD,
YR_TRANS_ID,
YR_ID,
YR_CD,
CLNDR_CD,
CLNDR_TYP,
LAST_YR_ID,
LAST_YR_CD,
NXT_YR_ID,
NXT_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
)
SELECT YR.ORG_CD,
DDR_R_YR_TRANS_SEQ.NEXTVAL,
YR.CLNDR_YR_ID,
YR.YR_CD,
YR.CLNDR_CD,
YR.CLNDR_TYP,
PYR.CLNDR_YR_ID,
PYR.YR_CD,
NYR.CLNDR_YR_ID,
NYR.YR_CD,
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
FROM DDR_TIME_CLNDR_YR_V YR,
DDR_TIME_CLNDR_YR_V PYR,
DDR_TIME_CLNDR_YR_V NYR
WHERE YR.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
AND PYR.CLNDR_CD(+) = YR.CLNDR_CD
AND PYR.YR_CD(+) = YR.YR_CD - 1
AND NYR.CLNDR_CD(+) = YR.CLNDR_CD
AND NYR.YR_CD(+) = YR.YR_CD + 1 ;
DELETE
FROM DDR_R_DAY_TODATE_TRANS
WHERE (DAY_CD,CLNDR_CD) IN
(SELECT DAY_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_DAY_V
WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
);
INSERT
INTO DDR_R_DAY_TODATE_TRANS
(
MFG_ORG_CD,
DAY_TODATE_TRANS_ID,
DAY_CD,
CLNDR_CD,
CLNDR_TYP,
YR_DAY_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
)
SELECT YTD.ORG_CD,
DDR_R_DAY_TODATE_TRANS_SEQ.NEXTVAL,
YTD.DAY_CD,
YTD.CLNDR_CD,
YTD.CLNDR_TYP,
YTD.YR_DAY_CD,
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
FROM
(SELECT A.ORG_CD ORG_CD,
A.DAY_CD DAY_CD,
A.CLNDR_CD CLNDR_CD,
A.CLNDR_TYP CLNDR_TYP,
B.DAY_CD YR_DAY_CD
FROM DDR_TIME_CLNDR_DAY_V A,
DDR_TIME_CLNDR_DAY_V B
WHERE A.YR_CD = B.YR_CD
AND A.DAY_CD >= B.DAY_CD
AND A.ORG_CD = B.ORG_CD
AND A.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
ORDER BY A.DAY_CD, B.DAY_CD) YTD;
DELETE
FROM DDR_R_WK_TODATE_TRANS
WHERE (WK_CD,CLNDR_CD) IN
(SELECT WK_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_WK_V
WHERE SUBSTR(WK_CD,1,4) BETWEEN TO_CHAR(p_start_year) AND
TO_CHAR(p_end_year)
);
INSERT
INTO DDR_R_WK_TODATE_TRANS
(
MFG_ORG_CD,
WK_TODATE_TRANS_ID,
WK_ID,
WK_CD,
CLNDR_CD,
CLNDR_TYP,
YR_WK_ID,
YR_WK_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
)
SELECT YTD.ORG_CD,
DDR_R_WK_TODATE_TRANS_SEQ.NEXTVAL,
YTD.WK_ID,
YTD.WK_CD,
YTD.CLNDR_CD,
YTD.CLNDR_TYP,
YTD.YR_WK_ID,
YTD.YR_WK_CD,
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
FROM
(SELECT A.ORG_CD ORG_CD,
A.WK_CD WK_CD,
A.CLNDR_WK_ID WK_ID,
A.CLNDR_CD CLNDR_CD,
A.CLNDR_TYP CLNDR_TYP,
B.WK_CD YR_WK_CD,
B.CLNDR_WK_ID YR_WK_ID
FROM DDR_TIME_CLNDR_WK_V A,
DDR_TIME_CLNDR_WK_V B
WHERE A.WK_CD >= B.WK_CD
AND A.ORG_CD = B.ORG_CD
AND SUBSTR(A.WK_CD,1,4) = SUBSTR(B.WK_CD,1,4)
AND SUBSTR(A.WK_CD,1,4) BETWEEN TO_CHAR(P_START_YEAR) AND
TO_CHAR(P_END_YEAR)
ORDER BY A.WK_CD, B.WK_CD) YTD;
DELETE
FROM DDR_R_MNTH_TODATE_TRANS
WHERE (MNTH_CD,CLNDR_CD) IN
(SELECT MNTH_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_MNTH_V
WHERE YR_CD BETWEEN TO_CHAR(P_START_YEAR) AND TO_CHAR(P_END_YEAR)
);
INSERT
INTO DDR_R_MNTH_TODATE_TRANS
(
MFG_ORG_CD,
MNTH_TODATE_TRANS_ID,
MNTH_ID,
MNTH_CD,
CLNDR_CD,
CLNDR_TYP,
YR_MNTH_ID,
YR_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
)
SELECT YTD.ORG_CD,
DDR_R_MNTH_TODATE_TRANS_SEQ.NEXTVAL,
YTD.MNTH_ID,
YTD.MNTH_CD,
YTD.CLNDR_CD,
YTD.CLNDR_TYP,
YTD.YR_MNTH_ID,
YTD.YR_MNTH_CD,
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
FROM
(SELECT A.ORG_CD ORG_CD,
A.MNTH_CD MNTH_CD,
A.CLNDR_MNTH_ID MNTH_ID,
A.CLNDR_CD CLNDR_CD,
A.CLNDR_TYP CLNDR_TYP,
B.MNTH_CD YR_MNTH_CD,
B.CLNDR_MNTH_ID YR_MNTH_ID
FROM DDR_TIME_CLNDR_MNTH_V A,
DDR_TIME_CLNDR_MNTH_V B
WHERE A.YR_CD = B.YR_CD
AND A.MNTH_CD >= B.MNTH_CD
AND A.ORG_CD = B.ORG_CD
AND A.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
ORDER BY A.MNTH_CD, B.MNTH_CD) YTD;
DELETE
FROM DDR_R_QTR_TODATE_TRANS
WHERE (QTR_CD,CLNDR_CD) IN
(SELECT QTR_CD,
CLNDR_CD
FROM DDR_TIME_CLNDR_QTR_V
WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
);
INSERT
INTO DDR_R_QTR_TODATE_TRANS
(
MFG_ORG_CD,
QTR_TODATE_TRANS_ID,
QTR_ID,
QTR_CD,
CLNDR_CD,
CLNDR_TYP,
YR_QTR_ID,
YR_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
)
SELECT YTD.ORG_CD,
DDR_R_QTR_TODATE_TRANS_SEQ.NEXTVAL,
YTD.QTR_ID,
YTD.QTR_CD,
YTD.CLNDR_CD,
YTD.CLNDR_TYP,
YTD.YR_QTR_ID,
YTD.YR_QTR_CD,
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
FROM
(SELECT A.ORG_CD ORG_CD,
A.QTR_CD QTR_CD,
A.CLNDR_QTR_ID QTR_ID,
A.CLNDR_CD CLNDR_CD,
A.CLNDR_CD CLNDR_TYP,
B.QTR_CD YR_QTR_CD,
B.CLNDR_QTR_ID YR_QTR_ID
FROM DDR_TIME_CLNDR_QTR_V A,
DDR_TIME_CLNDR_QTR_V B
WHERE A.YR_CD = B.YR_CD
AND A.QTR_CD >= B.QTR_CD
AND A.ORG_CD = B.ORG_CD
AND A.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
ORDER BY A.QTR_CD, B.QTR_CD) YTD;