DBA Data[Home] [Help]

APPS.PJI_TIME_C SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 166

		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;
Line: 221

		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
		);
Line: 265

l_no_rows_inserted NUMBER := 0;
Line: 270

	DELETE pji_time_extr_tmp;
Line: 272

	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);
Line: 292

	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;
Line: 319

	l_no_rows_inserted := SQL%rowcount;
Line: 322

		pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_PERIOD table.');
Line: 325

	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);
Line: 337

					pji_utils.write2log('Trying to insert record into PJI_TIME_EXTR_INFO table.');
Line: 339

				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);
Line: 349

					pji_utils.write2log('Duplicate records. Now trying to update the record...');
Line: 351

				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;
Line: 358

			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;
Line: 375

	IF l_no_rows_inserted >0 THEN
		RETURN TRUE;
Line: 383

		pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_PERIOD table.');
Line: 394

l_no_rows_deleted NUMBER := 0;
Line: 395

l_no_rows_inserted NUMBER := 0;
Line: 402

		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);
Line: 424

	DELETE FROM pji_time_ent_qtr
	WHERE ent_qtr_id in (SELECT DISTINCT period_year||quarter_num FROM pji_time_extr_tmp);
Line: 427

	l_no_rows_deleted := SQL%rowcount;
Line: 431

		pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_QTR table.');
Line: 434

	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';
Line: 468

	l_no_rows_inserted := SQL%rowcount;
Line: 471

		pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_QTR table.');
Line: 477

		pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_QTR table.');
Line: 487

l_no_rows_deleted NUMBER := 0;
Line: 488

l_no_rows_inserted NUMBER := 0;
Line: 495

		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);
Line: 517

	DELETE FROM pji_time_ent_year
	WHERE ent_year_id in (SELECT DISTINCT period_year from pji_time_extr_tmp);
Line: 520

	l_no_rows_deleted := SQL%rowcount;
Line: 524

		pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_YEAR table.');
Line: 527

	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';
Line: 558

	l_no_rows_inserted := SQL%rowcount;
Line: 561

		pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_YEAR table.');
Line: 567

		pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_YEAR table.');
Line: 578

l_no_rows_inserted NUMBER := 0;
Line: 582

	DELETE pji_time_extr_tmp;
Line: 584

	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);
Line: 604

	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;
Line: 631

	l_no_rows_inserted := SQL%rowcount;
Line: 634

		pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_PERIOD table.');
Line: 637

	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;
Line: 650

					pji_utils.write2log('Trying to insert record into PJI_TIME_EXTR_INFO table.');
Line: 652

				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);
Line: 662

					pji_utils.write2log('Duplicate records. Now trying to update the record...');
Line: 664

				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;
Line: 670

			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;
Line: 683

	IF l_no_rows_inserted >0 THEN
		RETURN TRUE;
Line: 691

		pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_PERIOD table.');
Line: 706

l_no_rows_deleted NUMBER := 0;
Line: 707

l_no_rows_inserted NUMBER := 0;
Line: 714

		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);
Line: 737

	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);
Line: 740

	l_no_rows_deleted := SQL%rowcount;
Line: 744

		pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_CAL_QTR table.');
Line: 747

	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';
Line: 781

	l_no_rows_inserted := SQL%rowcount;
Line: 784

		pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_QTR table.');
Line: 790

		pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_QTR table.');
Line: 803

l_no_rows_deleted NUMBER := 0;
Line: 804

l_no_rows_inserted NUMBER := 0;
Line: 811

		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);
Line: 835

	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);
Line: 838

	l_no_rows_deleted := SQL%rowcount;
Line: 842

		pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_CAL_YEAR table.');
Line: 845

	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';
Line: 874

	l_no_rows_inserted := SQL%rowcount;
Line: 877

		pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_YEAR table.');
Line: 883

		pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_YEAR table.');
Line: 893

l_no_rows_inserted NUMBER;
Line: 906

			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;
Line: 931

			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;
Line: 960

			l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 966

			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;
Line: 998

			l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1004

			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;
Line: 1036

			l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1049

	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;
Line: 1111

		l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1113

		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;
Line: 1159

		l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1161

		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;
Line: 1203

		l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1206

			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));
Line: 1222

l_no_rows_inserted NUMBER;
Line: 1235

			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;
Line: 1261

			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;
Line: 1292

			l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1298

			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;
Line: 1332

			l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1338

			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;
Line: 1372

			l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1385

	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;
Line: 1450

		l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1452

		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;
Line: 1500

		l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1502

		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;
Line: 1546

		l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
Line: 1549

			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));
Line: 1593

			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;
Line: 1605

	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);
Line: 1775

     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;
Line: 1786

   select nvl(max(calendar_id),0)
   into l_max_cal_name
   from pa_time_cal_name;
Line: 1795

      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);