The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_new_year
(
x_period_set_name VARCHAR2,
x_period_type VARCHAR2,
x_entered_year VARCHAR2,
x_period_name VARCHAR2,
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(100);
SELECT '1' INTO dummy FROM sys.dual
WHERE EXISTS
(SELECT 'Existing Year'
FROM gl_date_period_map
WHERE
period_set_name = x_period_set_name
AND period_type = x_period_type
AND accounting_date BETWEEN
TO_DATE(x_entered_year || '/01/01', 'YYYY/MM/DD')
AND TO_DATE(x_entered_year || '/12/31', 'YYYY/MM/DD')
);
INSERT INTO gl_date_period_map
(
period_set_name,
period_type,
accounting_date,
period_name,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT
x_period_set_name,
x_period_type,
TO_DATE(new_entered_year || '/01/01',
'YYYY/MM/DD')+(cnt.multiplier-1),
'NOT ASSIGNED', -- placeholder
x_CREATION_DATE,
x_CREATED_BY,
x_LAST_UPDATE_DATE,
x_LAST_UPDATED_BY,
x_LAST_UPDATE_LOGIN
FROM 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;
PROCEDURE select_row( recinfo IN OUT NOCOPY gl_date_period_map%ROWTYPE) IS
BEGIN
SELECT *
INTO recinfo
FROM gl_date_period_map
WHERE period_set_name = recinfo.period_set_name
AND period_type = recinfo.period_type
AND accounting_date = recinfo.accounting_date;
END select_row;
x_LAST_UPDATE_DATE DATE,
x_LAST_UPDATED_BY NUMBER,
x_LAST_UPDATE_LOGIN NUMBER
) IS
dummy VARCHAR2(100);
insert_new_year (
x_period_set_name,
x_period_type,
start_entered_year,
x_period_name,
x_CREATION_DATE,
x_CREATED_BY,
x_LAST_UPDATE_DATE,
x_LAST_UPDATED_BY,
x_LAST_UPDATE_LOGIN
);
-- insert new year for end_date if needed
insert_new_year (
x_period_set_name,
x_period_type,
end_entered_year,
x_period_name,
x_CREATION_DATE,
x_CREATED_BY,
x_LAST_UPDATE_DATE,
x_LAST_UPDATED_BY,
x_LAST_UPDATE_LOGIN
);
IF (x_operation = 'INSERT') THEN
-- For new periods being inserted:
UPDATE gl_date_period_map
SET period_name = x_period_name
WHERE
accounting_date between
x_start_date
AND x_end_date
AND period_set_name = x_period_set_name
AND period_type = x_period_type;
ELSIF(x_operation = 'DELETE') THEN
-- For existing periods being updated:
UPDATE gl_date_period_map
SET period_name = 'NOT ASSIGNED'
WHERE
( accounting_date between x_start_date AND x_end_date
OR period_name = x_period_name)
AND period_set_name = x_period_set_name
AND period_type = x_period_type;
-- For existing periods being updated:
UPDATE gl_date_period_map
SET period_name = DECODE(LEAST(accounting_date, x_start_date-1),
accounting_date, 'NOT ASSIGNED',
DECODE(GREATEST(accounting_date, x_end_date+1),
accounting_date, 'NOT ASSIGNED',
x_period_name))
WHERE
( accounting_date between x_start_date AND x_end_date
OR period_name = x_period_name)
AND period_set_name = x_period_set_name
AND period_type = x_period_type;
PROCEDURE select_columns
(
x_period_set_name VARCHAR2,
x_period_type VARCHAR2,
x_accounting_date DATE,
x_period_name IN OUT NOCOPY VARCHAR2) IS
recinfo gl_date_period_map%ROWTYPE;
select_row(recinfo);
END select_columns;