[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT se.effective_date
INTO l_session_date
FROM fnd_sessions se
WHERE se.session_id =USERENV('sessionid');
SELECT null
INTO l_dummy
FROM dual
WHERE EXISTS
(SELECT 1
FROM per_all_people_f per
WHERE l_session_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND per.national_identifier=p_national_identifier)
or EXISTS
(SELECT 1
from hz_parties hzp,hz_person_profiles pro
where UPPER(hzp.person_last_name_phonetic)=UPPER(p_last_name_phonetic)
AND hzp.party_id = pro.party_id
AND pro.effective_end_date is NULL
AND
( UPPER(pro.person_first_name)=UPPER(p_first_name)
OR pro.person_first_name is null
OR p_first_name IS NULL)
AND
( UPPER(pro.Person_first_name_phonetic)=UPPER(p_first_name_phonetic)
OR pro.person_first_name_phonetic is null
OR p_first_name_phonetic IS NULL)
AND
( UPPER(pro.person_last_name)=UPPER(p_last_name)
OR pro.person_last_name is null
OR p_last_name IS NULL)
AND
( pro.date_of_birth=p_date_of_birth
OR pro.date_of_birth IS NULL
OR p_date_of_birth IS NULL)
);
SELECT null
INTO l_dummy
FROM dual
WHERE EXISTS
(SELECT 1
FROM per_all_people_f per
WHERE l_session_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND per.national_identifier=p_national_identifier)
or EXISTS
(SELECT 1
from hz_person_profiles pro,
hz_parties pty
where UPPER(pty.person_last_name)=UPPER(p_last_name)
AND pty.party_id = pro.party_id
AND pro.effective_end_date is NULL
AND
( UPPER(pro.Person_first_name)=UPPER(p_first_name)
OR pro.person_first_name is null
OR p_first_name IS NULL)
AND
( pro.date_of_birth=p_date_of_birth
OR pro.date_of_birth IS NULL
OR p_date_of_birth IS NULL)
);
SELECT se.effective_date
INTO l_session_date
FROM fnd_sessions se
WHERE se.session_id =USERENV('sessionid');
SELECT null
INTO l_dummy
FROM dual
WHERE EXISTS
(SELECT 1
FROM per_all_people_f per
WHERE l_session_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND per.national_identifier=p_national_identifier)
or EXISTS
(SELECT 1
from hz_person_profiles pro,
hz_parties pty
where UPPER(pty.person_last_name)=UPPER(p_last_name)
AND pty.party_id = pro.party_id
AND pro.effective_end_date is NULL
AND
( UPPER(pro.Person_first_name)=UPPER(p_first_name)
OR pro.person_first_name is null
OR p_first_name IS NULL)
AND
( pro.date_of_birth=p_date_of_birth
OR pro.date_of_birth IS NULL
OR p_date_of_birth IS NULL)
);
SELECT system_person_type
INTO l_sys_pers_type
FROM per_person_type_usages_f pptu
,per_person_types ppt
WHERE pptu.person_id = p_person_id
AND pptu.person_type_id = ppt.person_type_id;
SELECT 1
INTO dummy_value
FROM dual
WHERE EXISTS (SELECT 1
FROM PER_ALL_PEOPLE_F per
WHERE per.party_id=p_party_id
AND per.business_group_id=Hr_General.get_business_Group_id);
SELECT per.party_id
INTO dummy_value
FROM PER_PEOPLE_F per
WHERE per.party_id=p_party_id;
select distinct per_system_status from per_assignment_status_Types
where active_flag ='Y';
p_new_row_updated_by in varchar2,
p_new_row_update_date in date,
p_Table_key_name in varchar2,
p_table_key_value in varchar2)
return boolean
is
l_result boolean;
p_new_row_updated_by => p_new_row_updated_by,
p_new_row_update_date => p_new_row_update_date,
p_Table_key_name => p_Table_key_name,
p_table_key_value => p_table_key_value);
select 'Y'
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id
and pbg.legislation_code = p_legislation_code;
select 'Y'
from hr_legislation_installations hli
where hli.application_short_name = p_application_short_name
and hli.legislation_code = p_legislation_code
and hli.status = 'I';
select distinct atc.table_name table_name
from dba_tab_columns atc
, user_synonyms usy
where atc.column_name = 'OBJECT_VERSION_NUMBER'
and atc.nullable = 'Y'
and ( ( substr(usy.synonym_name,1,3) in
('BEN','DT_','FF_','PER','PAY','HR_'
,'OTA', 'SSP', 'GHR', 'HXT')
and substr(usy.synonym_name,1,5) <> 'HR_S_'
and p_table = 'ALL' )
or ( usy.synonym_name = p_table and p_table <> 'ALL' )
)
and atc.owner = p_owner
and atc.table_name = usy.table_name
and atc.owner = usy.table_owner
and not exists
(select 1
from all_views uv
where uv.view_name = atc.table_name
and uv.owner = p_owner)
order by 1;
select alt.trigger_name
into l_ovn_trigger_name
from user_triggers alt
where alt.table_name = p_table_name
and alt.trigger_name like '%_OVN';
execute immediate 'update ' || trec.table_name || ' set ' ||
'object_version_number = 1 where ' ||
'object_version_number is null';
select 'Y'
from hr_locations_all loc
where loc.location_id = p_location_id
and nvl(loc.legal_address_flag,'N') = 'Y';
select vendor_name
from po_vendors
where vendor_id = p_vendor_id;
select 'TRUE' from sys.dual where
(column_name like '%AMOUNT%' or column_name like '%AMT%' or
column_name like '%VAL%' or column_name like '%COST%' or
column_name like '%PRICE%' or column_name like '%FEE%' or
column_name like '%MIN%' or column_name like '%MAX%' or
column_name like '%COMPENSATION%' or column_name like '%COMPNSTN%' or
column_name like '%CURRENCY%' or column_name like '%PAY%' or
column_name like '%SAL%' or column_name like '%RATE%' or
column_name like '%LIMIT') and
(column_name not like '%CD' and column_name not like '%ID' and
column_name not like '%ID_' and
column_name not like '%RL' and column_name not like '%FLAG' and
column_name not like '%APPROVAL%' and
column_name not like '%DATE%' and column_name not like '%NUMBER' and
column_name not like '%NAME' and
column_name not like '%REASON%' and column_name not like '%TERMINATION%' and
column_name not like '%FROM' and
column_name not like '%TO' and column_name not like '%DESCRIPTION%' and
column_name not like '%COMMENTS%' and
column_name not like '%FORMULA%' and column_name not like '%PERIOD' and
column_name not like '%PERIODS' and
column_name not like '%FREQUENCY' and column_name not like '%APPRAISAL%' and
column_name not like 'AGE%' and
column_name not like '%\_AGE%' ESCAPE '\' and column_name not like '% AGE' and
column_name not like '%TYPE' and
column_name not like '%STATUS%' and column_name not like '%INDICATOR' and
column_name not like '%IDENTIFIER' and
column_name not like '%DAYS' and column_name not like '%WEEKS' and
column_name not like '%WEEK' and
column_name not like '%VALUE_SET' and column_name not like '%VALUESET' and
column_name not like '%VALUE SET' and
column_name not like '%CATEGORY' and column_name not like '%KEY%' and
column_name not like '%PARAMETER%' and
column_name not like '%MULTIPLE' and column_name not like '%METHOD' and
column_name not like '%CENTER' and
column_name not like '%PLAN' and column_name not like '%DURATION%' and
column_name not like '%YEARS' and
column_name not like '%YEAR' and column_name not like '%IDENTIFICATION') and
((instr(column_name,'CODE')>0 and column_name like '%CURRENCY%CODE') or
(instr(column_name,'CODE')=0));
select 'TRUE' from sys.dual where
(column_name like '%AMOUNT%' or column_name like '%AMT%' or
column_name like '%VAL%' or column_name like '%COST%' or
column_name like '%PRICE%' or column_name like '%FEE%' or
column_name like '%MIN%' or column_name like '%MAX%' or
column_name like '%COMPENSATION%' or column_name like '%COMPNSTN%' or
column_name like '%CURRENCY%' or column_name like '%UOM%' or
column_name like '%CRN%' or column_name like '%PAY%' or
column_name like '%SAL%' or column_name like '%RATE%' or
column_name like 'MX%' or column_name like 'MN%' or
column_name like '%LIMIT') and
(column_name not like '%CD' and column_name not like '%ID' and
column_name not like '%ID_' and column_name not like '%RL' and
column_name not like '%FLAG' and column_name not like '%APPROVAL%' and
column_name not like '%DATE%' and column_name not like '%NUMBER' and
column_name not like '%NAME' and column_name not like '%REASON%' and
column_name not like '%TERMINATION%' and column_name not like '%FROM' and
column_name not like '%TO' and column_name not like '%DESCRIPTION%' and
column_name not like '%COMMENTS%' and column_name not like '%FORMULA%' and
column_name not like '%PERIOD' and column_name not like '%PERIODS' and
column_name not like '%FREQUENCY' and column_name not like '%APPRAISAL%' and
column_name not like 'AGE%' and column_name not like '%\_AGE%' ESCAPE '\' and
column_name not like '% AGE' and column_name not like '%TYPE' and
column_name not like '%STATUS%' and column_name not like '%INDICATOR' and
column_name not like '%IDENTIFIER' and column_name not like '%DAYS' and
column_name not like '%WEEKS' and column_name not like '%WEEK' and
column_name not like '%VALUE_SET' and column_name not like '%VALUESET' and
column_name not like '%VALUE SET' and column_name not like '%CATEGORY' and
column_name not like '%KEY%' and column_name not like '%PARAMETER%' and
column_name not like '%MULTIPLE' and column_name not like '%METHOD' and
column_name not like '%PLAN' and column_name not like '%CENTER'and
column_name not like '%DURATION%' and column_name not like '%YEARS' and
column_name not like '%YEAR' and column_name not like '%IDENTIFICATION' and
column_name not like '%\_NUM' ESCAPE '\') and
((instr(column_name,'CODE')>0 and column_name like '%CURRENCY%CODE')
or (instr(column_name,'CODE')=0));
SELECT per.party_id party_id
,per.person_id person_id
,hr_general2.get_dup_security_status ( per.party_id
,per.business_group_id
,per.person_id) security_status -- modified for Bug14664920
,per.global_name person_name
,nvl(bg.name,hr_general2.get_dup_external_name) bg_name
,loc.location_code location_code
,org.NAME org_name
,adr.postal_code postal_code
,SUBSTR(per.national_identifier,-4,4) national_identifier
,per.business_group_id bg_id
FROM per_all_people_f per
,per_all_assignments_f ass
,per_business_groups_perf bg
,hr_locations_all loc
,hr_all_organization_units org
,per_addresses adr
WHERE ass.person_id(+) = per.person_id
AND NVL(ass.primary_flag(+),'Y') = 'Y'
AND NVL(ass.assignment_type(+),'E') = 'E'
AND p_session_date BETWEEN NVL(ass.effective_start_date(+),p_session_date) AND NVL(ass.effective_end_date(+),p_session_date)
AND bg.business_group_id = per.business_Group_id
AND loc.location_id(+) = ass.location_id
AND org.organization_id(+) = ass.organization_id
AND adr.person_id(+) = per.person_id
AND NVL(adr.primary_flag(+),'Y') = 'Y'
AND p_session_date BETWEEN NVL(adr.date_from(+),p_session_date) AND NVL(adr.date_to(+),p_session_date)--fix for bug 6748256.
AND p_session_date BETWEEN NVL(per.effective_start_date,p_session_date) AND NVL(per.effective_end_date,p_session_date)
AND (
per.national_identifier = p_ni
OR
(
global_name = p_global_name
-- added conditions to use index
AND
(
global_name like l_ul_check OR
global_name like l_lu_check OR
global_name like l_uu_check OR
global_name like l_ll_check
)
AND
(
per.date_of_birth = p_dob
OR per.date_of_birth IS NULL OR p_dob IS NULL
)
)
)
UNION
SELECT pty.party_id party_id
,to_number(NULL) person_id
,NULL security_status
,hr_general2.get_dup_full_name
(pty.person_title
,pty.person_first_name
,pty.person_middle_name
,pty.person_last_name
,pty.person_name_suffix
) person_name
,hr_general2.get_dup_external_name bg_name
,NULL location_code
,NULL org_name
,NULL postal_code
,NULL national_identifier
,NULL bg_id
FROM hz_person_profiles pty,
hz_parties par
WHERE pty.party_id = par.party_id
AND par.orig_system_reference NOT LIKE 'PER%'
AND par.party_type = 'PERSON'
AND pty.effective_end_date is NULL
AND (
(
(
UPPER(par.person_last_name) = UPPER(p_last_name)
--OR par.person_last_name is null
)
AND
(
UPPER(par.person_first_name) = UPPER(p_first_name)
OR par.person_first_name IS NULL OR p_first_name IS NULL
)
AND
(
pty.date_of_birth = p_dob
OR pty.date_of_birth IS NULL OR p_dob IS NULL
)
)
);
SELECT se.effective_date
INTO l_session_date
FROM fnd_sessions se
WHERE se.session_id =USERENV('sessionid');
SELECT se.effective_date
INTO l_session_date
FROM fnd_sessions se
WHERE se.session_id = USERENV('sessionid');
select per.party_id "PartyId",
per.person_id "PersonId",
hr_general2.get_dup_security_status(per.party_id,per.business_group_id
,per.person_id) "SecurityStatus", -- modified for Bug14664920
hr_general2.get_dup_full_name(hr_general.decode_lookup('TITLE',per.title),per.first_name, per.middle_names,per.last_name,per.suffix,hr_api.return_legislation_code(per.business_group_id),per.per_information19,per.per_information18) "PersonName",
NVL(bg.name,hr_general2.get_dup_external_name) "BgName",
loc.location_code "LocationCode",
org.name "OrgName",
adr.postal_code "PostalCode",
SUBSTR(per.national_identifier,-4,4) "NationalIdentifier",
per.business_group_id "BusinessGroupId"
from per_all_people_f per
,per_all_assignments_f ass
,hr_all_organization_units_tl bg
,hr_locations_all_tl loc
,hr_all_organization_units_tl org
,per_addresses adr
where ass.person_id(+) = per.person_id
and ass.primary_flag(+) = 'Y'
and ass.assignment_type(+) = 'E'
and per.business_group_id = p_business_group_id
and l_session_date between nvl(ass.effective_start_date(+),l_session_date) and nvl(ass.effective_end_date(+),l_session_date)
and bg.organization_id = per.business_Group_id
and bg.language = userenv('LANG')
and loc.location_id (+) = ass.location_id
and loc.language (+) = userenv('LANG')
and org.organization_id(+) = ass.organization_id
and org.language(+) = userenv('LANG')
and adr.person_id(+) = per.person_id
and nvl(adr.primary_flag(+),'Y')='Y'
and l_session_date between nvl(adr.date_from(+),l_session_date) and nvl(adr.date_to(+),l_session_date)
and l_session_date between nvl(per.effective_start_date,l_session_date) and nvl(per.effective_end_date,l_session_date)
and ((per.national_identifier=p_national_identifier)
or ((per.last_name=p_last_name) and
(per.first_name=p_first_name or per.first_name is null or p_first_name is null)
and (per.date_of_birth=p_date_of_birth or per.date_of_birth is null or p_date_of_birth is null)));
select pty.party_id "PartyId"
,to_number(null) "PersonId"
,null "SecurityStatus"
,hr_general2.get_dup_full_name(pty.person_title
,pty.person_first_name,pty.person_middle_name ,pty.person_last_name,pty.person_name_suffix,null,null,null) "PersonName"
,hr_general2.get_dup_external_name "BgName"
,null "LocationCode"
,null "OrgName"
,null "PostalCode"
,null "NationalIdentifier",
to_number(null) "BusinessGroupId"
from hz_person_profiles pty
,hz_parties p
where p.party_id = pty.party_id
and p.person_last_name=p_last_name
and (p.person_first_name=p_first_name or p.person_first_name is null or p_first_name is null)
and (pty.date_of_birth=p_date_of_birth or pty.date_of_birth is null or p_date_of_birth is null)
and l_session_date between nvl(pty.effective_start_date,l_session_date) and nvl(pty.effective_end_date,l_session_date)
and not exists (select 'x' from per_all_people_f per
where per.party_id = p.party_id
and l_session_date between nvl(per.effective_start_date,l_session_date) and nvl(per.effective_end_date,l_session_date) );*/
SELECT per.party_id "PartyId"
,per.person_id "PersonId"
,hr_general2.get_dup_security_status ( per.party_id
,per.business_group_id
,per.person_id) "SecurityStatus" -- modified for Bug14664920
,per.global_name "PersonName"
,nvl(bg.name,hr_general2.get_dup_external_name) "BgName"
,loc.location_code "LocationCode"
,org.NAME "OrgName"
,adr.postal_code "PostalCode"
,SUBSTR(per.national_identifier,-4,4) "NationalIdentifier"
,per.business_group_id "BusinessGroupId"
FROM per_all_people_f per
,per_all_assignments_f ass
,per_business_groups_perf bg
,hr_locations_all loc
,hr_all_organization_units org
,per_addresses adr
WHERE ass.person_id(+) = per.person_id
AND NVL(ass.primary_flag(+),'Y') = 'Y'
AND NVL(ass.assignment_type(+),'E') = 'E'
AND l_session_date BETWEEN NVL(ass.effective_start_date(+),l_session_date) AND NVL(ass.effective_end_date(+),l_session_date)
AND bg.business_group_id = per.business_Group_id
AND loc.location_id(+) = ass.location_id
AND org.organization_id(+) = ass.organization_id
AND adr.person_id(+) = per.person_id
AND NVL(adr.primary_flag(+),'Y') = 'Y'
AND l_session_date BETWEEN NVL(adr.date_from(+),l_session_date) AND NVL(adr.date_to(+),l_session_date)--fix for bug6748256
AND l_session_date BETWEEN NVL(per.effective_start_date,l_session_date) AND NVL(per.effective_end_date,l_session_date)
AND (
per.national_identifier = p_national_identifier
OR
(
--global_name = l_global_name
upper(global_name) = upper(l_global_name) --Added for Bug 7609125
-- added conditions to use index
AND
(
global_name like l_ul_check OR
global_name like l_lu_check OR
global_name like l_uu_check OR
global_name like l_ll_check
)
AND
(
per.date_of_birth = p_date_of_birth
OR per.date_of_birth IS NULL OR p_date_of_birth IS NULL
)
)
)
UNION
SELECT pty.party_id "PartyId"
,to_number(NULL) "PersonId"
,NULL "SecurityStatus"
,hr_general2.get_dup_full_name
(pty.person_title
,pty.person_first_name
,pty.person_middle_name
,pty.person_last_name
,pty.person_name_suffix
) "PersonName"
,hr_general2.get_dup_external_name "BgName"
,NULL "LocationCode"
,NULL "OrgName"
,NULL "PostalCode"
,NULL "NationalIdentifier"
,NULL "BusinessGroupId"
FROM hz_person_profiles pty,
hz_parties par
WHERE pty.party_id = par.party_id
AND par.orig_system_reference NOT LIKE 'PER%'
AND par.party_type = 'PERSON'
AND pty.effective_end_date is NULL
AND (
(
(
UPPER(par.person_last_name) = UPPER(p_last_name)
--OR par.person_last_name is null
)
AND
(
UPPER(par.person_first_name) = UPPER(p_first_name)
OR par.person_first_name IS NULL OR p_first_name IS NULL
)
AND
(
pty.date_of_birth = p_date_of_birth
OR pty.date_of_birth IS NULL OR p_date_of_birth IS NULL
)
)
);
SELECT security_profile_id
FROM per_security_profiles
WHERE reporting_oracle_username = USER;