The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from per_time_period_types ptpt
where upper(p_period_type) = upper(ptpt.period_type)
and (p_row_id is null
or (p_row_id is not null
and chartorowid(p_row_id) <> ptpt.rowid));
procedure check_delete_period_type (
p_period_type in varchar2,
p_number_per_fiscal_year in number) is
--
g_dummy_number number;
v_no_delete boolean := FALSE;
l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.delete_period_type';
select null
from pay_calendars
where upper(p_period_type) = upper(actual_period_type);
select null
from per_time_periods
where upper(p_period_type) = upper(period_type);
select null
from per_cobra_cov_enrollments
where upper(p_period_type) = upper(period_type);
select null
from per_time_period_types
where number_per_fiscal_year = p_number_per_fiscal_year
and exists
(select null
from per_time_period_types
where number_per_fiscal_year = p_number_per_fiscal_year
and number_per_fiscal_year = 1
having count(*) = 1);
select null
from per_time_period_types
where number_per_fiscal_year = p_number_per_fiscal_year
and exists
(select null
from per_time_period_types
where number_per_fiscal_year = p_number_per_fiscal_year
and number_per_fiscal_year = 4
having count(*) = 1);
v_no_delete := csr_calendar%found;
if v_no_delete then
hr_utility.set_message (801,'HR_7660_DEF_DELETE_PERIODS');
v_no_delete := csr_periods%found;
if v_no_delete then
hr_utility.set_message (801,'HR_6058_TIME_DELETE_PERIOD');
v_no_delete := csr_cobra%found;
if v_no_delete then
hr_utility.set_message (801,'HR_6974_TIME_DELETE_COBRA');
v_no_delete := csr_year%found;
if v_no_delete then
hr_utility.set_message (801,'HR_7662_DEF_DELETE_YEAR_OR_QTR');
v_no_delete := csr_quarter%found;
if v_no_delete then
hr_utility.set_message (801,'HR_7662_DEF_DELETE_YEAR_OR_QTR');
end check_delete_period_type;
SELECT 1
FROM per_time_period_types_tl ptptl
WHERE upper(ptptl.display_period_type) = upper(x_display_period_type)
AND ptptl.language = x_language
AND ( x_period_type is null or ( x_period_type is not null
and ptptl.period_type <> x_period_type ) );
procedure INSERT_ROW (
X_ROWID in out nocopy VARCHAR2,
X_PERIOD_TYPE in VARCHAR2,
X_NUMBER_PER_FISCAL_YEAR in NUMBER,
X_YEAR_TYPE_IN_NAME in VARCHAR2,
X_SYSTEM_FLAG in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_DISPLAY_PERIOD_TYPE in VARCHAR2,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_ATTRIBUTE16 in VARCHAR2,
X_ATTRIBUTE17 in VARCHAR2,
X_ATTRIBUTE18 in VARCHAR2,
X_ATTRIBUTE19 in VARCHAR2,
X_ATTRIBUTE20 in VARCHAR2,
X_REQUEST_ID in NUMBER,
X_PROGRAM_APPLICATION_ID in NUMBER,
X_PROGRAM_ID in NUMBER,
X_PROGRAM_UPDATE_DATE in DATE,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from PER_TIME_PERIOD_TYPES
where PERIOD_TYPE = X_PERIOD_TYPE;
l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.insert_row';
insert into PER_TIME_PERIOD_TYPES (
PERIOD_TYPE,
NUMBER_PER_FISCAL_YEAR,
YEAR_TYPE_IN_NAME,
SYSTEM_FLAG,
DESCRIPTION,
DISPLAY_PERIOD_TYPE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_PERIOD_TYPE,
X_NUMBER_PER_FISCAL_YEAR,
X_YEAR_TYPE_IN_NAME,
X_SYSTEM_FLAG,
X_DESCRIPTION,
X_DISPLAY_PERIOD_TYPE,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
X_REQUEST_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_PROGRAM_UPDATE_DATE,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into PER_TIME_PERIOD_TYPES_TL (
PERIOD_TYPE,
DISPLAY_PERIOD_TYPE,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
X_PERIOD_TYPE,
X_DISPLAY_PERIOD_TYPE,
X_DESCRIPTION,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_CREATED_BY,
X_CREATION_DATE,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from PER_TIME_PERIOD_TYPES_TL T
where T.PERIOD_TYPE = X_PERIOD_TYPE
and T.LANGUAGE = L.LANGUAGE_CODE);
End INSERT_ROW;
cursor c is select
ATTRIBUTE20,
NUMBER_PER_FISCAL_YEAR,
YEAR_TYPE_IN_NAME,
SYSTEM_FLAG,
REQUEST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19
from PER_TIME_PERIOD_TYPES
where PERIOD_TYPE = X_PERIOD_TYPE
for update of PERIOD_TYPE nowait;
cursor c1 is select
DISPLAY_PERIOD_TYPE,
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from PER_TIME_PERIOD_TYPES_TL
where PERIOD_TYPE = X_PERIOD_TYPE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of PERIOD_TYPE nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_PERIOD_TYPE in VARCHAR2,
X_NUMBER_PER_FISCAL_YEAR in NUMBER,
X_YEAR_TYPE_IN_NAME in VARCHAR2,
X_SYSTEM_FLAG in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_DISPLAY_PERIOD_TYPE in VARCHAR2,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_ATTRIBUTE16 in VARCHAR2,
X_ATTRIBUTE17 in VARCHAR2,
X_ATTRIBUTE18 in VARCHAR2,
X_ATTRIBUTE19 in VARCHAR2,
X_ATTRIBUTE20 in VARCHAR2,
X_REQUEST_ID in NUMBER,
X_PROGRAM_APPLICATION_ID in NUMBER,
X_PROGRAM_ID in NUMBER,
X_PROGRAM_UPDATE_DATE in DATE,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor chk_source_lang is
select 1
from per_time_period_types_tl
where period_type = x_period_type
and source_lang <> userenv('LANG');
l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.update_row';
update PER_TIME_PERIOD_TYPES set
NUMBER_PER_FISCAL_YEAR = X_NUMBER_PER_FISCAL_YEAR,
YEAR_TYPE_IN_NAME = X_YEAR_TYPE_IN_NAME,
SYSTEM_FLAG = X_SYSTEM_FLAG,
ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = X_ATTRIBUTE1,
ATTRIBUTE2 = X_ATTRIBUTE2,
ATTRIBUTE3 = X_ATTRIBUTE3,
ATTRIBUTE4 = X_ATTRIBUTE4,
ATTRIBUTE5 = X_ATTRIBUTE5,
ATTRIBUTE6 = X_ATTRIBUTE6,
ATTRIBUTE7 = X_ATTRIBUTE7,
ATTRIBUTE8 = X_ATTRIBUTE8,
ATTRIBUTE9 = X_ATTRIBUTE9,
ATTRIBUTE10 = X_ATTRIBUTE10,
ATTRIBUTE11 = X_ATTRIBUTE11,
ATTRIBUTE12 = X_ATTRIBUTE12,
ATTRIBUTE13 = X_ATTRIBUTE13,
ATTRIBUTE14 = X_ATTRIBUTE14,
ATTRIBUTE15 = X_ATTRIBUTE15,
ATTRIBUTE16 = X_ATTRIBUTE16,
ATTRIBUTE17 = X_ATTRIBUTE17,
ATTRIBUTE18 = X_ATTRIBUTE18,
ATTRIBUTE19 = X_ATTRIBUTE19,
ATTRIBUTE20 = X_ATTRIBUTE20,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where PERIOD_TYPE = X_PERIOD_TYPE;
update PER_TIME_PERIOD_TYPES set
DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
DESCRIPTION = X_DESCRIPTION
where PERIOD_TYPE = X_PERIOD_TYPE;
update PER_TIME_PERIOD_TYPES_TL set
DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
DESCRIPTION = X_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where PERIOD_TYPE = X_PERIOD_TYPE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
insert into PER_TIME_PERIOD_TYPES_TL (
PERIOD_TYPE,
DISPLAY_PERIOD_TYPE,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
X_PERIOD_TYPE,
X_DISPLAY_PERIOD_TYPE,
X_DESCRIPTION,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from PER_TIME_PERIOD_TYPES_TL T
where T.PERIOD_TYPE = X_PERIOD_TYPE
and T.LANGUAGE = L.LANGUAGE_CODE);
end UPDATE_ROW;
procedure DELETE_ROW (
X_PERIOD_TYPE in VARCHAR2
) is
l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.delete_row';
delete from PER_TIME_PERIOD_TYPES_TL
where PERIOD_TYPE = X_PERIOD_TYPE;
delete from PER_TIME_PERIOD_TYPES
where PERIOD_TYPE = X_PERIOD_TYPE;
end DELETE_ROW;
delete from PER_TIME_PERIOD_TYPES_TL T
where not exists
(select NULL
from PER_TIME_PERIOD_TYPES B
where B.PERIOD_TYPE = T.PERIOD_TYPE
);
update PER_TIME_PERIOD_TYPES_TL T set (
DISPLAY_PERIOD_TYPE,
DESCRIPTION
) = (select
B.DISPLAY_PERIOD_TYPE,
B.DESCRIPTION
from PER_TIME_PERIOD_TYPES_TL B
where B.PERIOD_TYPE = T.PERIOD_TYPE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.PERIOD_TYPE,
T.LANGUAGE
) in (select
SUBT.PERIOD_TYPE,
SUBT.LANGUAGE
from PER_TIME_PERIOD_TYPES_TL SUBB, PER_TIME_PERIOD_TYPES_TL SUBT
where SUBB.PERIOD_TYPE = SUBT.PERIOD_TYPE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.DISPLAY_PERIOD_TYPE <> SUBT.DISPLAY_PERIOD_TYPE
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into PER_TIME_PERIOD_TYPES_TL (
PERIOD_TYPE,
DISPLAY_PERIOD_TYPE,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.PERIOD_TYPE,
B.DISPLAY_PERIOD_TYPE,
B.DESCRIPTION,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATED_BY,
B.CREATION_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from PER_TIME_PERIOD_TYPES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PER_TIME_PERIOD_TYPES_TL T
where T.PERIOD_TYPE = B.PERIOD_TYPE
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE out nocopy DATE,
X_LAST_UPDATED_BY out nocopy NUMBER,
X_LAST_UPDATE_LOGIN out nocopy NUMBER
) is
l_proc CONSTANT varchar2(100) := 'per_time_period_types_pkg.owner_to_who';
X_LAST_UPDATE_DATE := sysdate;
X_LAST_UPDATED_BY := fnd_global.user_id;
X_LAST_UPDATE_LOGIN := fnd_global.login_id;
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_DATE => l_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => l_LAST_UPDATE_LOGIN );
UPDATE_ROW (
X_PERIOD_TYPE,
X_NUMBER_PER_FISCAL_YEAR,
X_YEAR_TYPE_IN_NAME,
X_SYSTEM_FLAG,
X_DESCRIPTION,
X_DISPLAY_PERIOD_TYPE,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
NULL,
NULL,
NULL,
NULL,
l_LAST_UPDATE_DATE,
l_LAST_UPDATED_BY,
l_LAST_UPDATE_LOGIN
);
INSERT_ROW (
l_ROWID,
X_PERIOD_TYPE,
X_NUMBER_PER_FISCAL_YEAR,
X_YEAR_TYPE_IN_NAME,
X_SYSTEM_FLAG,
X_DESCRIPTION,
X_DISPLAY_PERIOD_TYPE,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
NULL,
NULL,
NULL,
NULL,
l_CREATION_DATE,
l_CREATED_BY,
l_LAST_UPDATE_DATE,
l_LAST_UPDATED_BY,
l_LAST_UPDATE_LOGIN
);
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
select 1
from per_time_period_types_tl
where period_type = x_period_type
and source_lang <> userenv('LANG');
X_LAST_UPDATE_DATE => l_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => l_LAST_UPDATE_LOGIN );
update PER_TIME_PERIOD_TYPES_TL
set DESCRIPTION = X_DESCRIPTION,
DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
LAST_UPDATED_BY = l_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where PERIOD_TYPE = X_PERIOD_TYPE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update PER_TIME_PERIOD_TYPES set
DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
DESCRIPTION = X_DESCRIPTION,
LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
LAST_UPDATED_BY = l_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN
where PERIOD_TYPE = X_PERIOD_TYPE;
select 1
from per_time_period_types_tl
where period_type = x_period_type
and source_lang <> userenv('LANG');
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
l_LAST_UPDATE_DATE := sysdate;
l_LAST_UPDATED_BY := fnd_global.user_id;
l_LAST_UPDATE_LOGIN := fnd_global.login_id;
update PER_TIME_PERIOD_TYPES set
DISPLAY_PERIOD_TYPE = X_DISPLAY_PERIOD_TYPE,
DESCRIPTION = X_DESCRIPTION,
LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
LAST_UPDATED_BY = l_LAST_UPDATED_BY
where PERIOD_TYPE = X_PERIOD_TYPE;