[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pur.rowid ROW_ID,
pur.user_row_id,
pur.display_sequence,
pur.effective_start_date
from pay_user_rows_f pur,
pay_user_tables put
where put.user_table_name='RO_LOCALITY_DETAILS'
and put.legislation_code =g_legislation_code
and put.business_group_id is null
and pur.user_table_id=put.user_table_id
and pur.legislation_code is null
and pur.business_group_id = p_business_group_id
and ROW_LOW_RANGE_OR_NAME = p_row_low_range_or_name
and p_effective_date
between pur.effective_start_date and pur.effective_end_date
for update of pur.user_row_id;
select nvl(max(DISPLAY_SEQUENCE),0)+1 into p_display_sequence from pay_user_rows_f pur,
pay_user_tables put
where put.user_table_name=p_user_table_name
and pur.user_table_id=put.user_table_id;
insert into pay_user_rows_f(
USER_ROW_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
USER_TABLE_ID,
ROW_LOW_RANGE_OR_NAME,
DISPLAY_SEQUENCE,
LEGISLATION_SUBGROUP,
ROW_HIGH_RANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
select pay_user_rows_s.nextval,
p_effective_date,
g_end_date,
p_business_group_id,
null,
put.user_table_id,
p_row_low_range_or_name,
p_display_sequence,
NULL,
NULL,
sysdate,
1,
-1,
1,
sysdate
from pay_user_tables put
where put.user_table_name=p_user_table_name
and put.legislation_code=g_legislation_code
and put.business_group_id is NULL;
-- update
hr_utility.trace('Updating record');
update pay_user_rows_f pur
set pur.row_low_range_or_name=p_row_low_range_or_name
where pur.rowid=l_rec.row_id;
-- update mode
hr_utility.trace('Update mode');
insert into pay_user_rows_f(
USER_ROW_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
USER_TABLE_ID,
ROW_LOW_RANGE_OR_NAME,
DISPLAY_SEQUENCE,
LEGISLATION_SUBGROUP,
ROW_HIGH_RANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
select l_rec.user_row_id ,
p_effective_date,
pur.effective_end_date,
pur.business_group_id,
pur.legislation_code,
pur.user_table_id,
p_row_low_range_or_name,
pur.display_sequence,
NULL,
NULL,
sysdate,
1,
-1,
1,
sysdate
from pay_user_rows_f pur
where pur.rowid = l_rec.row_id;
update pay_user_rows_f pur
set pur.effective_end_date=p_effective_date - 1
where pur.rowid=l_rec.row_id
and pur.effective_start_date <> p_effective_date;
select puci.rowid ROW_ID,
puci.user_column_instance_id,
puci.effective_start_date,
puci.value
from pay_user_column_instances_f puci,
pay_user_rows_f pur,
pay_user_columns puc,
pay_user_tables put
where put.user_table_name=p_user_table_name
and put.legislation_code=g_legislation_code
and put.business_group_id is null
and puc.user_table_id=put.user_table_id
and puc.user_column_name=p_user_column_name
and puc.legislation_code=g_legislation_code
and puc.business_group_id is null
and pur.user_table_id=put.user_table_id
and pur.display_sequence=p_display_sequence
and pur.legislation_code is null
and pur.business_group_id = p_business_group_id
and p_effective_date
between pur.effective_start_date and pur.effective_end_date
and puci.user_column_id=puc.user_column_id
and puci.user_row_id=pur.user_row_id
and p_effective_date
between puci.effective_start_date and puci.effective_end_date;
insert into pay_user_column_instances_f(
USER_COLUMN_INSTANCE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
USER_ROW_ID,
USER_COLUMN_ID,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
LEGISLATION_SUBGROUP,
VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
select pay_user_column_instances_s.nextval,
p_effective_date,
g_end_date,
pur.user_row_id,
puc.user_column_id,
p_business_group_id,
null,
NULL,
p_value,
sysdate,
1,
-1,
1,
sysdate
from pay_user_rows_f pur,
pay_user_columns puc,
pay_user_tables put
where put.user_table_name=p_user_table_name
and put.legislation_code=g_legislation_code
and put.business_group_id is NULL
and puc.user_table_id=put.user_table_id
and puc.user_column_name=p_user_column_name
and puc.legislation_code=g_legislation_code
and puc.business_group_id is NULL
and pur.user_table_id=put.user_table_id
and pur.display_sequence=p_display_sequence
and pur.legislation_code is null
and pur.business_group_id = p_business_group_id
and p_effective_date
between pur.effective_start_date and pur.effective_end_date;
-- update already present record
hr_utility.trace('Updaing record');
update pay_user_column_instances_f puci
set puci.value=p_value
where puci.rowid=l_rec.row_id;
-- update mode
-- entering new record
hr_utility.trace('Update mode');
insert into pay_user_column_instances_f(
USER_COLUMN_INSTANCE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
USER_ROW_ID,
USER_COLUMN_ID,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
LEGISLATION_SUBGROUP,
VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
select puci.user_column_instance_id,
p_effective_date,
puci.effective_end_date,
puci.user_row_id,
puci.user_column_id,
p_business_group_id,
null,
NULL,
p_value,
sysdate,
1,
-1,
1,
sysdate
from pay_user_column_instances_f puci
where puci.rowid=l_rec.row_id;
update pay_user_column_instances_f puci
set puci.effective_end_date = p_effective_date - 1
where puci.rowid=l_rec.row_id
and puci.effective_start_date <> p_effective_date;
-- set update mode as correction -if its correction, no changes to l_eff_date
-- if update mode is updation -- fetch effevtive date nad pass as l_eff_date
is
l_file_handler UTL_FILE.FILE_TYPE;
if (p_mode = 'UPDATE') then
l_eff_date := to_date(p_effective_date,'RRRR-MM-DD HH24:MI:SS');
PROCEDURE lookup_insert_row
( p_lookup_code IN fnd_lookup_values.lookup_code%type,
p_meaning IN fnd_lookup_values.meaning%type,
p_description IN fnd_lookup_values.description%type,
p_security_group_id IN NUMBER,
p_effective_date IN DATE,
p_lookup_type IN VARCHAR2)
IS
--l_lookup_type CONSTANT VARCHAR2(50):='RO_COR_VALUE_LIST';
FND_LOOKUP_VALUES_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_LOOKUP_TYPE => p_lookup_type,
X_SECURITY_GROUP_ID => p_security_group_id,
X_VIEW_APPLICATION_ID => 3,
X_LOOKUP_CODE => p_lookup_code,
X_TAG => '+RO',
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => null,
X_END_DATE_ACTIVE => null,
X_TERRITORY_CODE => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_MEANING => p_meaning,
X_DESCRIPTION => p_description,
X_CREATION_DATE => p_effective_date,
X_CREATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
X_LAST_UPDATE_DATE => p_effective_date,
X_LAST_UPDATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
X_LAST_UPDATE_LOGIN => TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
);
hr_utility.set_location ('insert row', 30);
end lookup_insert_row;
PROCEDURE lookup_update_row
( p_lookup_code IN fnd_lookup_values.lookup_code%type,
p_meaning IN fnd_lookup_values.meaning%type,
p_description IN fnd_lookup_values.description%type,
p_security_group_id IN NUMBER,
p_effective_date IN DATE,
p_lookup_type IN VARCHAR2)
IS
--l_lookup_type CONSTANT VARCHAR2(50):='RO_COR_VALUE_LIST';
FND_LOOKUP_VALUES_PKG.UPDATE_ROW(
X_LOOKUP_TYPE => p_lookup_type,
X_SECURITY_GROUP_ID => p_security_group_id,
X_VIEW_APPLICATION_ID => 3,
X_LOOKUP_CODE => p_lookup_code,
X_TAG => '+RO',
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => NULL,
X_END_DATE_ACTIVE => NULL,
X_TERRITORY_CODE => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_MEANING => p_meaning,
X_DESCRIPTION => p_description,
X_LAST_UPDATE_DATE => p_effective_date,
X_LAST_UPDATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
X_LAST_UPDATE_LOGIN => TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')));
hr_utility.set_location ('update row', 30);
end lookup_update_row;
l_update_flag VARCHAR2(1) := 'N';
select security_group_id
into l_security_group_id
from per_business_groups
where business_group_id = p_business_group_id;
select 'Y'
into l_update_flag
from fnd_lookup_values
where lookup_type= 'RO_COR_VALUE_LIST'
and lookup_code= l_lookup_code
and security_group_id = l_security_group_id
and language = userenv('LANG');
if l_update_flag='Y' then
lookup_update_row(l_lookup_code,l_lookup_code,l_description,l_security_group_id,l_effective_date,'RO_COR_VALUE_LIST');
lookup_insert_row(l_lookup_code,l_lookup_code,l_description,l_security_group_id,l_effective_date,'RO_COR_VALUE_LIST');
(select distinct pucif.VALUE ,pucif2.VALUE, hr.meaning
from pay_user_column_instances_f pucif,
pay_user_rows_f purf,
pay_user_rows_f purf1,
pay_user_column_instances_f pucif1,
pay_user_rows_f purf2,
pay_user_column_instances_f pucif2,
(select lookup_code,meaning from hr_lookups where lookup_type = 'RO_PER_COUNTIES' and enabled_flag='Y') hr
where purf.user_table_id = p_user_table_id
and pucif.user_column_id = p_user_column_id_loc
and pucif.user_row_id = purf.user_row_id
and pucif.business_group_id = p_business_group_id
and purf.business_group_id = p_business_group_id
and purf1.user_table_id = p_user_table_id
and pucif1.user_column_id = p_user_column_id_con
and pucif1.user_row_id = purf1.user_row_id
and pucif1.business_group_id = p_business_group_id
and purf1.business_group_id = p_business_group_id
and purf.row_low_range_or_name = purf1.row_low_range_or_name
and pucif1.value = hr.lookup_code
and purf2.user_table_id = p_user_table_id
and pucif2.user_column_id = p_user_column_id_pin
and pucif2.user_row_id = purf2.user_row_id
and pucif2.business_group_id = p_business_group_id
and purf2.business_group_id = p_business_group_id
and purf.row_low_range_or_name = purf2.row_low_range_or_name
and purf1.row_low_range_or_name = purf2.row_low_range_or_name
and purf.row_low_range_or_name in
(SELECT purf3.row_low_range_or_name
FROM pay_user_column_instances_f pucif3
,pay_user_columns puc3
,pay_user_rows_f purf3
WHERE purf3.user_table_id = p_user_table_id
AND puc3.user_column_name = 'LEVEL'
and puc3.legislation_code = 'RO'
AND puc3.user_table_id = p_user_table_id
AND pucif3.user_column_id = puc3.user_column_id
AND pucif3.value = '3'
AND purf3.user_row_id = pucif3.user_row_id
and pucif3.business_group_id = p_business_group_id
and purf3.business_group_id = p_business_group_id));
select security_group_id
into l_security_group_id
from per_business_groups
where business_group_id = p_business_group_id;
SELECT put.user_table_id
,puc.user_column_id
into
l_user_table_id
,l_user_column_id_loc
FROM pay_user_tables put
,pay_user_columns puc
WHERE put.user_table_name = 'RO_LOCALITY_DETAILS'
AND put.user_table_id = puc.user_table_id
AND puc.user_column_name = 'LOCALITY';
SELECT puc.user_column_id
into l_user_column_id_con
FROM pay_user_tables put
,pay_user_columns puc
WHERE put.user_table_name = 'RO_LOCALITY_DETAILS'
AND put.user_table_id = puc.user_table_id
AND puc.user_column_name = 'COUNTY CODE';
SELECT puc.user_column_id
into l_user_column_id_pin
FROM pay_user_tables put
,pay_user_columns puc
WHERE put.user_table_name = 'RO_LOCALITY_DETAILS'
AND put.user_table_id = puc.user_table_id
AND puc.user_column_name = 'POSTAL CODE';
select NVL(MAX(TO_NUMBER(SUBSTR(LOOKUP_CODE,4))),0) into l_code_postfix
FROM fnd_lookup_values
WHERE LOOKUP_TYPE = 'PER_RO_LOCALITY';
select 'Y'
into l_exist_flag
from fnd_lookup_values
where lookup_type= 'PER_RO_LOCALITY'
and meaning= l_meaning
and description = l_description
--and security_group_id = l_security_group_id
and language = userenv('LANG');
lookup_insert_row(l_code,l_meaning,l_description,l_security_group_id,l_eff_date,'PER_RO_LOCALITY');