The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nmf.legislation_code
,nmf.format_name
,nmf.user_format_choice
,nmf.format_mask
from HR_NAME_FORMATS nmf
where ((cp_legCode is not null and nmf.legislation_code = cp_legCode)
OR ( nmf.legislation_code is null))
and ((cp_formatName is not null and nmf.format_name = cp_formatName)
OR (cp_formatName is null))
and ((cp_scope is not null and nmf.user_format_choice = cp_scope)
OR (cp_scope is null));
select nmf.legislation_code
,nmf.format_name
,nmf.user_format_choice
,nmf.format_mask
from HR_NAME_FORMATS nmf
where nmf.legislation_code in
(select distinct legislation_code from per_business_groups_perf)
and ((cp_formatName is not null and nmf.format_name = cp_formatName)
OR (cp_formatName is null))
and ((cp_scope is not null and nmf.user_format_choice = cp_scope)
OR (cp_scope is null));
select legislation_code, business_group_id, name
from PER_BUSINESS_GROUPS_PERF bg
where ((cp_legCode is not null and bg.legislation_code = cp_legCode)
or (cp_legCode is null));
select leg.territory_code legislation_code
from fnd_territories_vl leg
where leg.territory_code in
(select distinct legislation_code from per_business_groups_perf);
Returns : nothing, it updates the table with newly constructed names.
-----------------------------------------------------------------------*/
procedure sync_person_names
(p_rowid in rowid
,p_person_id in number
,p_person_rec in hr_person_name.t_nameColumns_Rec
,p_legislation_code in varchar2
,p_format_name in varchar2
,p_user_format_choice in varchar2
,p_num_rows_processed out nocopy number) IS
--
l_format_mask hr_name_formats.format_mask%TYPE;
update per_all_people_f
set full_name = l_full_name
,order_name = l_order_name
,local_name = l_local_name
,global_name = l_global_name
where rowid = p_rowid;
select table_owner from user_triggers
where trigger_name = 'PER_ALL_PEOPLE_F_WHO';
select business_group_id
from PER_BUSINESS_GROUPS_PERF bg
where bg.legislation_code = cp_legCode;
select pay_core_utils.get_parameter('LEGISLATION_CODE',
ppa.legislative_parameters)
into l_leg_code
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
sqlstr := 'select distinct per.person_id
from per_all_people_f per
where :payroll_action_id > 0
order by per.person_id';
sqlstr := 'select distinct per.person_id
from per_all_people_f per
,per_business_groups_perf bg
where :payroll_action_id > 0
and bg.business_group_id = per.business_group_id
and bg.legislation_code = '||''''||l_leg_code||''''||
' order by per.person_id';
select distinct ppf.person_id
from per_all_people_f ppf
,per_business_groups_perf bg
,pay_payroll_actions ppa
where ppf.person_id between cp_stperson and cp_endperson
and ppa.payroll_action_id = cp_pactid
and ppf.business_group_id = bg.business_group_id
and bg.legislation_code = cp_leg_code;
select distinct ppf.person_id
from per_all_people_f ppf
,pay_payroll_actions ppa
where ppf.person_id between cp_stperson and cp_endperson
and ppa.payroll_action_id = cp_pactid;
select pay_core_utils.get_parameter('LEGISLATION_CODE',
ppa.legislative_parameters)
into l_legislation_code
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select pay_assignment_actions_s.nextval into l_lockingactid
from dual;
select pay_assignment_actions_s.nextval into l_lockingactid
from dual;
select pay_core_utils.get_parameter('LEGISLATION_CODE',
ppa.legislative_parameters)
,pay_core_utils.get_parameter('FORMAT_NAME_CHOICE',
ppa.legislative_parameters)
into g_prm_legislation_code, p_format_name_choice
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select ass.object_id
from pay_assignment_actions ass
where ass.assignment_action_id = p_assactid;
select rowid row_id
,FIRST_NAME
,MIDDLE_NAMES
,LAST_NAME
,SUFFIX
,PRE_NAME_ADJUNCT
,TITLE
,KNOWN_AS
,EMAIL_ADDRESS
,EMPLOYEE_NUMBER
,APPLICANT_NUMBER
,NPW_NUMBER
,PREVIOUS_LAST_NAME
,PER_INFORMATION1
,PER_INFORMATION2
,PER_INFORMATION3
,PER_INFORMATION4
,PER_INFORMATION5
,PER_INFORMATION6
,PER_INFORMATION7
,PER_INFORMATION8
,PER_INFORMATION9
,PER_INFORMATION10
,PER_INFORMATION11
,PER_INFORMATION12
,PER_INFORMATION13
,PER_INFORMATION14
,PER_INFORMATION15
,PER_INFORMATION16
,PER_INFORMATION17
,PER_INFORMATION18
,PER_INFORMATION19
,PER_INFORMATION20
,PER_INFORMATION21
,PER_INFORMATION22
,PER_INFORMATION23
,PER_INFORMATION24
,PER_INFORMATION25
,PER_INFORMATION26
,PER_INFORMATION27
,PER_INFORMATION28
,PER_INFORMATION29
,PER_INFORMATION30
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE16
,ATTRIBUTE17
,ATTRIBUTE18
,ATTRIBUTE19
,ATTRIBUTE20
,ATTRIBUTE21
,ATTRIBUTE22
,ATTRIBUTE23
,ATTRIBUTE24
,ATTRIBUTE25
,ATTRIBUTE26
,ATTRIBUTE27
,ATTRIBUTE28
,ATTRIBUTE29
,ATTRIBUTE30
,FULL_NAME
,ORDER_NAME
,LOCAL_NAME
,GLOBAL_NAME
,BUSINESS_GROUP_ID
from per_all_people_f
where person_id = cp_person_id
for update nowait;
select rowid row_id
,FIRST_NAME
,MIDDLE_NAMES
,LAST_NAME
,SUFFIX
,PRE_NAME_ADJUNCT
,TITLE
,KNOWN_AS
,EMAIL_ADDRESS
,EMPLOYEE_NUMBER
,APPLICANT_NUMBER
,NPW_NUMBER
,PREVIOUS_LAST_NAME
,PER_INFORMATION1
,PER_INFORMATION2
,PER_INFORMATION3
,PER_INFORMATION4
,PER_INFORMATION5
,PER_INFORMATION6
,PER_INFORMATION7
,PER_INFORMATION8
,PER_INFORMATION9
,PER_INFORMATION10
,PER_INFORMATION11
,PER_INFORMATION12
,PER_INFORMATION13
,PER_INFORMATION14
,PER_INFORMATION15
,PER_INFORMATION16
,PER_INFORMATION17
,PER_INFORMATION18
,PER_INFORMATION19
,PER_INFORMATION20
,PER_INFORMATION21
,PER_INFORMATION22
,PER_INFORMATION23
,PER_INFORMATION24
,PER_INFORMATION25
,PER_INFORMATION26
,PER_INFORMATION27
,PER_INFORMATION28
,PER_INFORMATION29
,PER_INFORMATION30
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE16
,ATTRIBUTE17
,ATTRIBUTE18
,ATTRIBUTE19
,ATTRIBUTE20
,ATTRIBUTE21
,ATTRIBUTE22
,ATTRIBUTE23
,ATTRIBUTE24
,ATTRIBUTE25
,ATTRIBUTE26
,ATTRIBUTE27
,ATTRIBUTE28
,ATTRIBUTE29
,ATTRIBUTE30
,FULL_NAME
,ORDER_NAME
,LOCAL_NAME
,GLOBAL_NAME
,BUSINESS_GROUP_ID
from per_all_people_f
where rowid = cp_row_id
for update nowait;
select peo.rowid, bg.legislation_code
from per_all_people_f peo
,per_business_groups_perf bg
where (peo.global_name is null or peo.local_name is null)
and peo.business_group_id = bg.business_group_id;
,p_user_format_choice => NULL -- this will update local and global
,p_num_rows_processed => l_num_rows_processed);
l_update_date date;
l_update_date := trunc(sysdate);
l_result := hr_update_utility.isUpdateComplete
(p_app_shortname => 'PER'
,p_function_name => null
,p_business_group_id => null
,p_update_name => 'PERNMFT');