The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(earliest_start_date,TRUNC(SYSDATE))
, NVL(latest_end_date,TRUNC(SYSDATE))
INTO
g_earliest_start_date
, g_latest_end_date
FROM pji_time_cal_extr_info
WHERE calendar_id = -1;
INSERT INTO pji_time_week
(week_id,
period445_id,
SEQUENCE,
NAME,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
VALUES
(
l_year_num||LPAD(l_period_num,2,'0')||LPAD(l_week_num,2,'0'),
l_year_num||LPAD(l_period_num,2,'0'),
TO_CHAR(l_week-g_week_offset,'iw'),
TO_CHAR(l_week_end,'dd-Mon-rr'),
l_week,
l_week_end,
SYSDATE,
SYSDATE,
g_user_id,
g_user_id,
g_login_id
);
l_no_rows_inserted NUMBER := 0;
DELETE pji_time_extr_tmp;
INSERT INTO pji_time_extr_tmp(period_year
, quarter_num
, period_num
, period_name
, start_date
, end_date)
SELECT period_year
, quarter_num
, period_num
, period_name
, start_date
, end_date
FROM gl_periods
WHERE 1=1
AND period_set_name = g_period_set_name
AND period_type = g_period_type
AND adjustment_period_flag='N'
AND (start_date < g_earliest_start_date
OR end_date > g_latest_end_date);
INSERT INTO pji_time_ent_period(
ent_period_id
, ent_qtr_id
, ent_year_id
, sequence
, name
, start_date
, end_date
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT TO_NUMBER(period_year||quarter_num||DECODE(LENGTH(period_num),1,'0'||period_num, period_num))
, TO_NUMBER(period_year||quarter_num)
, TO_NUMBER(period_year)
, period_num
, period_name
, start_date
, end_date
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM pji_time_extr_tmp;
l_no_rows_inserted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_PERIOD table.');
IF l_no_rows_inserted > 0 THEN
SELECT MIN(start_date), MAX(end_date)
INTO l_min_date, l_max_date
FROM pji_time_ent_period
WHERE (start_date < g_earliest_start_date
OR end_date > g_latest_end_date);
pji_utils.write2log('Trying to insert record into PJI_TIME_EXTR_INFO table.');
INSERT INTO pji_time_cal_extr_info
(calendar_id, earliest_start_date,
latest_end_date, creation_date,
last_update_date, last_updated_by,
created_by, last_update_login)
VALUES (-1, l_min_date, l_max_date,
sysdate, sysdate, -1, -1, -1);
pji_utils.write2log('Duplicate records. Now trying to update the record...');
UPDATE pji_time_cal_extr_info
SET earliest_start_date = LEAST(l_min_date, earliest_start_date)
, latest_end_date = GREATEST(l_max_date, latest_end_date)
WHERE calendar_id = -1;
UPDATE pji_time_cal_extr_info
SET earliest_start_date = LEAST(l_min_date, earliest_start_date)
, latest_end_date = GREATEST(l_max_date, latest_end_date)
WHERE calendar_id = -1;
IF l_no_rows_inserted >0 THEN
RETURN TRUE;
pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_PERIOD table.');
l_no_rows_deleted NUMBER := 0;
l_no_rows_inserted NUMBER := 0;
SELECT
MIN(ent_qtr_id) start_qtr_id
, MAX(ent_qtr_id) end_qtr_id
, MIN(end_date) earliest_qtr_end_date
, MAX(start_date) latest_qtr_start_date
INTO
l_start_qtr_id
, l_end_qtr_id
, l_earliest_qtr_end_date
, l_latest_qtr_start_date
FROM pji_time_ent_qtr
WHERE 1=1
AND (g_earliest_start_date BETWEEN start_date AND end_date)
OR (g_latest_end_date BETWEEN start_date AND end_date);
DELETE FROM pji_time_ent_qtr
WHERE ent_qtr_id in (SELECT DISTINCT period_year||quarter_num FROM pji_time_extr_tmp);
l_no_rows_deleted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_QTR table.');
INSERT INTO pji_time_ent_qtr
(ent_qtr_id
, ent_year_id
, SEQUENCE
, NAME
, start_date
, end_date
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT period_year||quarter_num
, period_year
, quarter_num
, REPLACE(Fnd_Message.get_string('PJI','PJI_QUARTER_LABEL'),'&QUARTER_NUMBER',quarter_num)
||'-'||
TO_CHAR(TO_DATE(period_year,'yyyy'),'RR')
, DECODE(period_year||quarter_num,l_end_qtr_id,l_latest_qtr_start_date,MIN(start_date))
, DECODE(period_year||quarter_num,l_start_qtr_id,l_earliest_qtr_end_date,MAX(end_date))
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM pji_time_extr_tmp
GROUP BY
period_year||quarter_num
, period_year
, quarter_num
HAVING MAX(end_date)g_latest_end_date
OR g_cal_info_exists = 'N';
l_no_rows_inserted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_QTR table.');
pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_QTR table.');
l_no_rows_deleted NUMBER := 0;
l_no_rows_inserted NUMBER := 0;
SELECT
MIN(ent_year_id) start_yr_id
, MAX(ent_year_id) end_yr_id
, MIN(end_date) earliest_yr_end_date
, MAX(start_date) latest_yr_start_date
INTO
l_start_yr_id
, l_end_yr_id
, l_earliest_yr_end_date
, l_latest_yr_start_date
FROM pji_time_ent_year
WHERE 1=1
AND (g_earliest_start_date BETWEEN start_date AND end_date)
OR (g_latest_end_date BETWEEN start_date AND end_date);
DELETE FROM pji_time_ent_year
WHERE ent_year_id in (SELECT DISTINCT period_year from pji_time_extr_tmp);
l_no_rows_deleted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_YEAR table.');
INSERT INTO pji_time_ent_year
(ent_year_id
, period_set_name
, period_type
, SEQUENCE
, NAME
, start_date
, end_date
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT period_year
, g_period_set_name
, g_period_type
, period_year
, period_year
, DECODE(period_year,l_end_yr_id,l_latest_yr_start_date,MIN(start_date))
, DECODE(period_year,l_start_yr_id,l_earliest_yr_end_date,MAX(end_date))
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM pji_time_extr_tmp
GROUP BY period_year
HAVING MAX(end_date)g_latest_end_date
OR g_cal_info_exists = 'N';
l_no_rows_inserted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_YEAR table.');
pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_YEAR table.');
l_no_rows_inserted NUMBER := 0;
DELETE pji_time_extr_tmp;
INSERT INTO pji_time_extr_tmp(period_year
, quarter_num
, period_num
, period_name
, start_date
, end_date)
SELECT period_year
, quarter_num
, period_num
, period_name
, start_date
, end_date
FROM gl_periods
WHERE 1=1
AND period_set_name = p_period_set_name
AND period_type = p_period_type
AND adjustment_period_flag='N'
AND (start_date < p_earliest_start_date
OR end_date > p_latest_end_date);
INSERT INTO pji_time_cal_period(cal_period_id
, cal_qtr_id
, calendar_id
, SEQUENCE
, NAME
, start_date
, end_date
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT LPAD(p_calendar_id,3,'0')||period_year||quarter_num
||DECODE(LENGTH(period_num),1,'0'||period_num, period_num)
, LPAD(p_calendar_id,3,'0')||period_year||quarter_num
, LPAD(p_calendar_id,3,'0')
, period_num
, period_name
, start_date
, end_date
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM pji_time_extr_tmp;
l_no_rows_inserted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_PERIOD table.');
IF l_no_rows_inserted > 0 THEN
SELECT MIN(start_date), MAX(end_date)
INTO l_min_date, l_max_date
FROM pji_time_cal_period
WHERE (start_date < p_earliest_start_date
OR end_date > p_latest_end_date)
AND calendar_id = p_calendar_id;
pji_utils.write2log('Trying to insert record into PJI_TIME_EXTR_INFO table.');
INSERT INTO pji_time_cal_extr_info
(calendar_id, earliest_start_date,
latest_end_date, creation_date,
last_update_date, last_updated_by,
created_by, last_update_login)
VALUES (p_calendar_id, l_min_date, l_max_date,
sysdate, sysdate, -1, -1, -1);
pji_utils.write2log('Duplicate records. Now trying to update the record...');
UPDATE pji_time_cal_extr_info
SET earliest_start_date = LEAST(l_min_date, earliest_start_date)
, latest_end_date = GREATEST(l_max_date, latest_end_date)
WHERE calendar_id = p_calendar_id;
UPDATE pji_time_cal_extr_info
SET earliest_start_date = LEAST(l_min_date, earliest_start_date)
, latest_end_date = GREATEST(l_max_date, latest_end_date)
WHERE calendar_id = p_calendar_id;
IF l_no_rows_inserted >0 THEN
RETURN TRUE;
pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_PERIOD table.');
l_no_rows_deleted NUMBER := 0;
l_no_rows_inserted NUMBER := 0;
SELECT
MIN(cal_qtr_id) start_qtr_id
, MAX(cal_qtr_id) end_qtr_id
, MIN(end_date) earliest_qtr_end_date
, MAX(start_date) latest_qtr_start_date
INTO
l_start_qtr_id
, l_end_qtr_id
, l_earliest_qtr_end_date
, l_latest_qtr_start_date
FROM pji_time_cal_qtr
WHERE 1=1
AND calendar_id = p_calendar_id
AND (p_earliest_start_date BETWEEN start_date AND end_date)
OR (p_latest_end_date BETWEEN start_date AND end_date);
DELETE FROM pji_time_cal_qtr
WHERE cal_qtr_id in (SELECT DISTINCT LPAD(p_calendar_id,3,'0')||period_year||quarter_num FROM pji_time_extr_tmp);
l_no_rows_deleted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_CAL_QTR table.');
INSERT INTO pji_time_cal_qtr
(cal_qtr_id
, cal_year_id
, calendar_id
, SEQUENCE
, NAME
, start_date
, end_date
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT LPAD(p_calendar_id,3,'0')||period_year||quarter_num
, LPAD(p_calendar_id,3,'0')||period_year
, LPAD(p_calendar_id,3,'0')
, quarter_num
, TO_CHAR(quarter_num)||', '||TO_CHAR(period_year)
, DECODE(LPAD(p_calendar_id,3,'0')||period_year||quarter_num,l_end_qtr_id,l_latest_qtr_start_date,MIN(start_date))
, DECODE(LPAD(p_calendar_id,3,'0')||period_year||quarter_num,l_start_qtr_id,l_earliest_qtr_end_date,MAX(end_date))
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM pji_time_extr_tmp
GROUP BY period_year||quarter_num
, period_year
, quarter_num
HAVING MAX(end_date)p_latest_end_date
OR p_cal_info_exists = 'N';
l_no_rows_inserted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_QTR table.');
pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_QTR table.');
l_no_rows_deleted NUMBER := 0;
l_no_rows_inserted NUMBER := 0;
SELECT
MIN(cal_year_id) start_yr_id
, MAX(cal_year_id) end_yr_id
, MIN(end_date) earliest_yr_end_date
, MAX(start_date) latest_yr_start_date
INTO
l_start_yr_id
, l_end_yr_id
, l_earliest_yr_end_date
, l_latest_yr_start_date
FROM pji_time_cal_year
WHERE 1=1
AND calendar_id = p_calendar_id
AND (g_earliest_start_date BETWEEN start_date AND end_date)
OR (g_latest_end_date BETWEEN start_date AND end_date);
DELETE FROM pji_time_cal_year
WHERE cal_year_id in (SELECT DISTINCT LPAD(p_calendar_id,3,'0')||period_year from pji_time_extr_tmp);
l_no_rows_deleted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_CAL_YEAR table.');
INSERT INTO PJI_TIME_CAL_YEAR
(cal_year_id
, calendar_id
, SEQUENCE
, NAME
, start_date
, end_date
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT LPAD(p_calendar_id,3,'0')||period_year
, LPAD(p_calendar_id,3,'0')
, period_year
, period_year
, DECODE(LPAD(p_calendar_id,3,'0')||period_year,l_end_yr_id,l_latest_yr_start_date,MIN(start_date))
, DECODE(LPAD(p_calendar_id,3,'0')||period_year,l_start_yr_id,l_earliest_yr_end_date,MAX(end_date))
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM pji_time_extr_tmp
GROUP BY period_year
HAVING MAX(end_date)p_latest_end_date
OR p_cal_info_exists = 'N';
l_no_rows_inserted := SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_YEAR table.');
pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_YEAR table.');
l_no_rows_inserted NUMBER;
SELECT period.start_date period_start_date
, qtr.start_date qtr_start_date
, yr.start_date year_start_date
, period.end_date period_end_date
, qtr.end_date qtr_end_date
, yr.end_date year_end_date
INTO
l_period_start_date
, l_qtr_start_date
, l_year_start_date
, l_qtr_end_date
, l_period_end_date
, l_year_end_date
FROM pji_time_ent_period period
, pji_time_ent_qtr qtr
, pji_time_ent_year yr
WHERE 1=1
AND period.ent_qtr_id = qtr.ent_qtr_id
AND qtr.ent_year_id = yr.ent_year_id
AND period.start_date = g_earliest_start_date;
INSERT INTO PJI_TIME_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
-1
, 'E'
, period.start_date
, year.ent_year_id
, 128
, 1024
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_YEAR year
, PJI_TIME_ENT_PERIOD period
WHERE year.end_date < l_year_start_date
AND period.start_date >= g_earliest_start_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
INSERT INTO PJI_TIME_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
-1
, 'E'
, period.start_date
, qtr.ent_qtr_id
, 64
, 64
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_QTR qtr
, PJI_TIME_ENT_PERIOD period
WHERE 1=1
AND qtr.end_date < l_qtr_start_date
AND qtr.start_date >= l_year_start_date
AND period.start_date >= g_earliest_start_date
AND period.end_date <= l_year_end_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
INSERT INTO PJI_TIME_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
-1
, 'E'
, oldprd.start_date
, newprd.ent_period_id
, 32
, 32
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_PERIOD newprd
, PJI_TIME_ENT_PERIOD oldprd
WHERE 1=1
AND newprd.end_date < l_period_start_date
AND newprd.start_date >= l_qtr_start_date
AND oldprd.start_date >= g_earliest_start_date
AND oldprd.end_date <= l_qtr_end_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
FOR extr_rec IN (SELECT extr.start_date report_date
, period.start_date period_start_date
, qtr.start_date qtr_start_date
, YEAR.start_date year_start_date
, period.end_date period_end_date
, qtr.end_date qtr_end_date
, YEAR.end_date year_end_date
FROM pji_time_extr_tmp extr
, pji_time_ent_period period
, pji_time_ent_qtr qtr
, pji_time_ent_year YEAR
WHERE 1=1
AND TO_NUMBER(extr.period_year||extr.quarter_num||DECODE(LENGTH(extr.period_num),1,'0'||extr.period_num, extr.period_num)) = period.ent_period_id
AND period.ent_qtr_id = qtr.ent_qtr_id
AND qtr.ent_year_id = YEAR.ent_year_id)
LOOP
INSERT INTO PJI_TIME_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
-1
, 'E'
, extr_rec.report_date
, ent_period_id
, 32
, 32
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_PERIOD
WHERE start_date >= extr_rec.qtr_start_date
AND start_date <= extr_rec.period_start_date
AND end_date < extr_rec.report_date
UNION ALL
SELECT
-1
, 'E'
, extr_rec.report_date
, ent_period_id
, 32
, 256
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_PERIOD
WHERE start_date >= extr_rec.qtr_start_date
AND start_date <= extr_rec.period_start_date
AND end_date >= extr_rec.report_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
INSERT INTO PJI_TIME_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
-1
, 'E'
, extr_rec.report_date
, ent_qtr_id
, 64
, 64
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_QTR
WHERE start_date >= extr_rec.year_start_date
AND start_date <= extr_rec.qtr_start_date
AND end_date < extr_rec.report_date
UNION ALL
SELECT
-1
, 'E'
, extr_rec.report_date
, ent_qtr_id
, 64
, 512
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_QTR
WHERE start_date >= extr_rec.year_start_date
AND start_date <= extr_rec.qtr_start_date
AND end_date >= extr_rec.report_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
INSERT INTO PJI_TIME_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
-1
, 'E'
, extr_rec.report_date
, ent_year_id
, 128
, 128
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_YEAR
WHERE extr_rec.report_date BETWEEN start_date AND end_date
UNION ALL
SELECT
-1
, 'E'
, extr_rec.report_date
, ent_year_id
, 128
, 1024
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_ENT_YEAR
WHERE end_date < extr_rec.report_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_RPT_STRUCT table for date : '||Fnd_Date.date_to_displaydate(extr_rec.report_date));
l_no_rows_inserted NUMBER;
SELECT period.start_date period_start_date
, qtr.start_date qtr_start_date
, yr.start_date year_start_date
, period.end_date period_end_date
, qtr.end_date qtr_end_date
, yr.end_date year_end_date
INTO
l_period_start_date
, l_qtr_start_date
, l_year_start_date
, l_qtr_end_date
, l_period_end_date
, l_year_end_date
FROM pji_time_cal_period period
, pji_time_cal_qtr qtr
, pji_time_cal_year yr
WHERE 1=1
AND period.calendar_id = p_calendar_id
AND period.cal_qtr_id = qtr.cal_qtr_id
AND qtr.cal_year_id = yr.cal_year_id
AND period.start_date = p_earliest_start_date;
INSERT INTO PJI_TIME_CAL_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
p_calendar_id
, 'G'
, period.start_date
, year.cal_year_id
, 128
, 1024
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_YEAR year
, PJI_TIME_CAL_PERIOD period
WHERE year.end_date < l_year_start_date
AND year.calendar_id = p_calendar_id
AND period.calendar_id = p_calendar_id
AND period.start_date > p_earliest_start_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
INSERT INTO PJI_TIME_CAL_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
p_calendar_id
, 'G'
, period.start_date
, qtr.cal_qtr_id
, 64
, 64
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_QTR qtr
, PJI_TIME_CAL_PERIOD period
WHERE 1=1
AND qtr.end_date < l_qtr_start_date
AND qtr.start_date >= l_year_start_date
AND qtr.calendar_id = p_calendar_id
AND period.calendar_id = p_calendar_id
AND period.start_date > p_earliest_start_date
AND period.end_date <= l_year_end_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
INSERT INTO PJI_TIME_CAL_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
p_calendar_id
, 'G'
, oldprd.start_date
, newprd.cal_period_id
, 32
, 32
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_PERIOD newprd
, PJI_TIME_CAL_PERIOD oldprd
WHERE 1=1
AND newprd.end_date < l_period_start_date
AND newprd.start_date >= l_qtr_start_date
AND newprd.calendar_id = p_calendar_id
AND oldprd.calendar_id = p_calendar_id
AND oldprd.start_date > p_earliest_start_date
AND oldprd.end_date <= l_qtr_end_date;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
FOR extr_rec IN (SELECT extr.start_date report_date
, period.start_date period_start_date
, qtr.start_date qtr_start_date
, YEAR.start_date year_start_date
, period.end_date period_end_date
, qtr.end_date qtr_end_date
, YEAR.end_date year_end_date
FROM pji_time_extr_tmp extr
, pji_time_cal_period period
, pji_time_cal_qtr qtr
, pji_time_cal_year YEAR
WHERE 1=1
AND period.calendar_id = p_calendar_id
AND TO_NUMBER(LPAD(p_calendar_id,3,'0')||period_year||quarter_num||DECODE(LENGTH(period_num),1,'0'||period_num, period_num)) = period.cal_period_id
AND period.cal_qtr_id = qtr.cal_qtr_id
AND qtr.cal_year_id = YEAR.cal_year_id)
LOOP
INSERT INTO PJI_TIME_CAL_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
p_calendar_id
, 'G'
, extr_rec.report_date
, cal_period_id
, 32
, 32
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_PERIOD
WHERE start_date >= extr_rec.qtr_start_date
AND start_date <= extr_rec.period_start_date
AND end_date < extr_rec.report_date
AND calendar_id = p_calendar_id
UNION ALL
SELECT
p_calendar_id
, 'G'
, extr_rec.report_date
, cal_period_id
, 32
, 256
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_PERIOD
WHERE start_date >= extr_rec.qtr_start_date
AND start_date <= extr_rec.period_start_date
AND end_date >= extr_rec.report_date
AND calendar_id = p_calendar_id;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
INSERT INTO PJI_TIME_CAL_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
p_calendar_id
, 'G'
, extr_rec.report_date
, cal_qtr_id
, 64
, 64
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_QTR
WHERE start_date >= extr_rec.year_start_date
AND start_date <= extr_rec.qtr_start_date
AND end_date < extr_rec.report_date
AND calendar_id = p_calendar_id
UNION ALL
SELECT
p_calendar_id
, 'G'
, extr_rec.report_date
, cal_qtr_id
, 64
, 512
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_QTR
WHERE start_date >= extr_rec.year_start_date
AND start_date <= extr_rec.qtr_start_date
AND end_date >= extr_rec.report_date
AND calendar_id = p_calendar_id;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
INSERT INTO PJI_TIME_CAL_RPT_STRUCT
(calendar_id
, calendar_type
, report_date
, time_id
, period_type_id
, record_type_id
, creation_date
, last_update_date
, last_updated_by
, created_by
, last_update_login)
SELECT
p_calendar_id
, 'G'
, extr_rec.report_date
, cal_year_id
, 128
, 128
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_YEAR
WHERE extr_rec.report_date BETWEEN start_date AND end_date
AND calendar_id = p_calendar_id
UNION ALL
SELECT
p_calendar_id
, 'G'
, extr_rec.report_date
, cal_year_id
, 128
, 1024
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM PJI_TIME_CAL_YEAR
WHERE end_date < extr_rec.report_date
AND calendar_id = p_calendar_id;
l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_RPT_STRUCT table for date : '||Fnd_Date.date_to_displaydate(extr_rec.report_date));
SELECT calendar_id
INTO l_calendar_id
FROM pa_time_cal_name /* Modified for bug 12979524 */
WHERE period_set_name = p_period_set_name
AND period_type = p_period_type;
SELECT cal.calendar_id
, period_set_name
, period_type
, NVL(info.earliest_start_date,TRUNC(SYSDATE))
, NVL(info.latest_end_date,TRUNC(SYSDATE))
, DECODE(NVL(info.earliest_start_date,TRUNC(SYSDATE)),info.earliest_start_date,'Y','N')
BULK COLLECT INTO
l_calendar_ids_tbl
, l_period_set_name_tbl
, l_period_type_tbl
, l_earliest_start_dates_tbl
, l_latest_end_dates_tbl
, l_cal_info_exists_tbl
FROM pa_time_cal_name cal /* Modified for bug 12979524 */
, PJI_TIME_CAL_EXTR_INFO info
WHERE 1=1
AND cal.calendar_id = info.calendar_id (+)
AND cal.calendar_id = NVL(l_calendar_id,cal.calendar_id)
AND cal.period_set_name = NVL(p_period_set_name,cal.period_set_name)
AND cal.period_type = NVL(p_period_type,cal.period_type);
select distinct gl.period_set_name, gl.period_type from gl_periods gl
minus
select distinct cal.period_set_name, cal.period_type from pa_time_cal_name cal;
select nvl(max(calendar_id),0)
into l_max_cal_name
from pa_time_cal_name;
insert into pa_time_cal_name
(calendar_id,
period_set_name,
period_type,
name,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values(
l_max_cal_name+1,
new_cal_rec.period_set_name,
new_cal_rec.period_type,
new_cal_rec.period_set_name||' ('||new_cal_rec.period_type||')',
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id);