APPS.GL_TRANSACTION_DATES_PKG SQL Statements

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

Line: 13

			x_LAST_UPDATE_DATE	DATE,
			x_LAST_UPDATED_BY	NUMBER,
			x_LAST_UPDATE_LOGIN	NUMBER
			)  IS
  CURSOR check_new_year IS
	SELECT '1' FROM sys.dual
	WHERE EXISTS
		(SELECT 'Existing Year'
		FROM	gl_periods
		WHERE
			    period_set_name = x_period_set_name
			AND end_date BETWEEN
			        TO_DATE(x_entered_year || '/01/01', 'YYYY/MM/DD')
			    AND TO_DATE(x_entered_year || '/12/31', 'YYYY/MM/DD')
		);

Line: 47

    SELECT concurrency_class
    INTO   dummy
    FROM  gl_concurrency_control
    WHERE concurrency_class = 'EXTEND_TRANSACTION_CALENDAR'
    FOR UPDATE OF concurrency_class;

Line: 58

    INSERT INTO gl_transaction_dates
       ( TRANSACTION_CALENDAR_ID,
	TRANSACTION_DATE,
	DAY_OF_WEEK,
	BUSINESS_DAY_FLAG,
	CREATION_DATE,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
       )
       SELECT
	tcal.transaction_calendar_id,
	TO_DATE(new_entered_year || '/01/01', 'YYYY/MM/DD')+ cnt.multiplier-1,
	DECODE
	(
                TO_CHAR(TO_DATE( new_entered_year || '/01/01', 'YYYY/MM/DD')+cnt.multiplier-1,'DY'),
		TO_CHAR(TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'DY'), 'MON',
		TO_CHAR(TO_DATE('1996/01/02', 'YYYY/MM/DD'), 'DY'), 'TUE',
		TO_CHAR(TO_DATE('1996/01/03', 'YYYY/MM/DD'), 'DY'), 'WED',
		TO_CHAR(TO_DATE('1996/01/04', 'YYYY/MM/DD'), 'DY'), 'THU',
		TO_CHAR(TO_DATE('1996/01/05', 'YYYY/MM/DD'), 'DY'), 'FRI',
		TO_CHAR(TO_DATE('1996/01/06', 'YYYY/MM/DD'), 'DY'), 'SAT',
		TO_CHAR(TO_DATE('1996/01/07', 'YYYY/MM/DD'), 'DY'), 'SUN',
		'NONE'
	),
        DECODE
	(
	     	DECODE
		(
			TO_CHAR(TO_DATE( new_entered_year || '/01/01', 'YYYY/MM/DD')
									+ cnt.multiplier-1,'DY'),
                	TO_CHAR(TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'DY'), 'MON',
                	TO_CHAR(TO_DATE('1996/01/02', 'YYYY/MM/DD'), 'DY'), 'TUE',
                	TO_CHAR(TO_DATE('1996/01/03', 'YYYY/MM/DD'), 'DY'), 'WED',
                	TO_CHAR(TO_DATE('1996/01/04', 'YYYY/MM/DD'), 'DY'), 'THU',
                	TO_CHAR(TO_DATE('1996/01/05', 'YYYY/MM/DD'), 'DY'), 'FRI',
                	TO_CHAR(TO_DATE('1996/01/06', 'YYYY/MM/DD'), 'DY'), 'SAT',
                	TO_CHAR(TO_DATE('1996/01/07', 'YYYY/MM/DD'), 'DY'), 'SUN',
                	'NONE'
		),
		'MON', tcal.mon_business_day_flag,
		'TUE', tcal.tue_business_day_flag,
		'WED', tcal.wed_business_day_flag,
		'THU', tcal.thu_business_day_flag,
		'FRI', tcal.fri_business_day_flag,
		'SAT', tcal.sat_business_day_flag,
		'SUN', tcal.sun_business_day_flag,
		'Y'
	),
	x_CREATION_DATE,
	x_CREATED_BY,
	x_LAST_UPDATE_DATE,
	x_LAST_UPDATED_BY,
	x_LAST_UPDATE_LOGIN
       FROM gl_transaction_calendar tcal, gl_row_multipliers cnt
       WHERE
            cnt.multiplier <= TO_DATE(new_entered_year || '/12/31', 'YYYY/MM/DD') -
    	        TO_DATE(new_entered_year || '/01/01', 'YYYY/MM/DD')+1
       AND NOT EXISTS
         (SELECT 'duplicate'
          FROM   gl_transaction_dates tdates
          WHERE  tdates.transaction_date =
                 to_date(new_entered_year || '/01/01',
				'YYYY/MM/DD')+cnt.multiplier-1
                 AND    tdates.transaction_calendar_id =
					tcal.transaction_calendar_id);

Line: 136

  PROCEDURE insert_all_years_for_calendar
			(
			x_transaction_calendar_id	NUMBER,
			x_CREATION_DATE			DATE,
			x_CREATED_BY			NUMBER,
			x_LAST_UPDATE_DATE		DATE,
			x_LAST_UPDATED_BY		NUMBER,
			x_LAST_UPDATE_LOGIN		NUMBER
			)  IS

        dummy			VARCHAR2(1000);

Line: 151

    SELECT TO_NUMBER(TO_CHAR(MIN(start_date), 'YYYY'))
    INTO earliest_year
    FROM gl_periods;

Line: 156

    SELECT TO_NUMBER(TO_CHAR(MAX(end_date), 'YYYY'))
    INTO latest_year
    FROM gl_periods;

Line: 164

    SELECT concurrency_class
    INTO   dummy
    FROM  gl_concurrency_control
    WHERE concurrency_class = 'EXTEND_TRANSACTION_CALENDAR'
    FOR UPDATE OF concurrency_class;

Line: 172

   INSERT INTO gl_transaction_dates
       (TRANSACTION_CALENDAR_ID,
	TRANSACTION_DATE,
	DAY_OF_WEEK,
	BUSINESS_DAY_FLAG,
	CREATION_DATE,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
       )
	SELECT
	x_transaction_calendar_id,
	TO_DATE(TO_CHAR(yr.multiplier+1900) || '/01/01', 'YYYY/MM/DD')+ cnt.multiplier-1,
	DECODE
	(
		TO_CHAR(TO_DATE( TO_CHAR(yr.multiplier+1900) || '/01/01', 'YYYY/MM/DD')
									+ cnt.multiplier-1,'DY'),
		TO_CHAR(TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'DY'), 'MON',
		TO_CHAR(TO_DATE('1996/01/02', 'YYYY/MM/DD'), 'DY'), 'TUE',
		TO_CHAR(TO_DATE('1996/01/03', 'YYYY/MM/DD'), 'DY'), 'WED',
		TO_CHAR(TO_DATE('1996/01/04', 'YYYY/MM/DD'), 'DY'), 'THU',
		TO_CHAR(TO_DATE('1996/01/05', 'YYYY/MM/DD'), 'DY'), 'FRI',
                TO_CHAR(TO_DATE('1996/01/06', 'YYYY/MM/DD'), 'DY'), 'SAT',
                TO_CHAR(TO_DATE('1996/01/07', 'YYYY/MM/DD'), 'DY'), 'SUN',
                'NONE'

	),
        DECODE
	(
	     	DECODE
		(
			TO_CHAR(TO_DATE( TO_CHAR(yr.multiplier+1900) || '/01/01', 'YYYY/MM/DD')
									+ cnt.multiplier-1,'DY'),
                	TO_CHAR(TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'DY'), 'MON',
                	TO_CHAR(TO_DATE('1996/01/02', 'YYYY/MM/DD'), 'DY'), 'TUE',
                	TO_CHAR(TO_DATE('1996/01/03', 'YYYY/MM/DD'), 'DY'), 'WED',
                	TO_CHAR(TO_DATE('1996/01/04', 'YYYY/MM/DD'), 'DY'), 'THU',
                	TO_CHAR(TO_DATE('1996/01/05', 'YYYY/MM/DD'), 'DY'), 'FRI',
                	TO_CHAR(TO_DATE('1996/01/06', 'YYYY/MM/DD'), 'DY'), 'SAT',
                	TO_CHAR(TO_DATE('1996/01/07', 'YYYY/MM/DD'), 'DY'), 'SUN',
                	'NONE'
		),
		'MON', cal.mon_business_day_flag,
		'TUE', cal.tue_business_day_flag,
		'WED', cal.wed_business_day_flag,
		'THU', cal.thu_business_day_flag,
		'FRI', cal.fri_business_day_flag,
		'SAT', cal.sat_business_day_flag,
		'SUN', cal.sun_business_day_flag,
		'Y'
	),
	x_CREATION_DATE,
	x_CREATED_BY,
	x_LAST_UPDATE_DATE,
	x_LAST_UPDATED_BY,
	x_LAST_UPDATE_LOGIN
       FROM  gl_transaction_calendar cal, gl_row_multipliers yr, gl_row_multipliers cnt
       WHERE
            cal.transaction_calendar_id = x_transaction_calendar_id
        AND cnt.multiplier <= TO_DATE(TO_CHAR(yr.multiplier+1900) || '/12/31', 'YYYY/MM/DD') -
    	        TO_DATE(TO_CHAR(yr.multiplier+1900) || '/01/01', 'YYYY/MM/DD')+1
	AND yr.multiplier >= earliest_year-1900
	AND yr.multiplier <= latest_year-1900
       AND NOT EXISTS
         (SELECT 'duplicate'
          FROM   gl_transaction_dates tdates
          WHERE  tdates.transaction_date =
                 to_date(TO_CHAR(yr.multiplier+1900) || '/01/01',
				'YYYY/MM/DD')+cnt.multiplier-1
                 AND    tdates.transaction_calendar_id = x_transaction_calendar_id);

Line: 247

                            'gl_periods_pkg.insert_all_years_for_calendar');

Line: 249

  END insert_all_years_for_calendar;