[Home] [Help]
3: --PER_USE_TITLE_IN_FULL_NAME
4: g_package varchar2(30) := 'per_hrtca_merge.';
5: g_count number := 0;
6: --
7: procedure update_person(p_rec in out nocopy per_all_people_f%rowtype) is
8: --
9: l_effective_start_date date;
10: l_effective_end_date date;
11: l_full_name varchar2(240);
45: p_name_combination_warning => l_name_combination_warning,
46: p_assign_payroll_warning => l_assign_payroll_warning,
47: p_orig_hire_warning => l_orig_hire_warning);
48: */
49: update per_all_people_f
50: set party_id = p_rec.party_id
51: where person_id = p_rec.person_id
52: and p_rec.effective_start_date
53: between effective_start_date
56: hr_utility.set_location('Leaving '||l_proc,10);
57: --
58: end update_person;
59: --
60: procedure update_child_tables(p_rec in out nocopy per_all_people_f%rowtype) is
61: --
62: l_proc varchar2(80) := g_package||'update_child_tables';
63: --
64: cursor c_competences is
90: cursor c_phones is
91: select *
92: from per_phones
93: where parent_id = p_rec.person_id
94: and parent_table = 'PER_ALL_PEOPLE_F';
95: --
96: l_phones c_phones%rowtype;
97: --
98: cursor c_qualifications is
497: l_proc varchar2(80) := g_package||'migrate_all_hr_persons';
498: --
499: cursor c_person is
500: select *
501: from per_all_people_f
502: where party_id is null
503: and mod(person_id,p_number_of_workers) = p_current_worker-1
504: and effective_end_date = hr_api.g_eot;
505: --
504: and effective_end_date = hr_api.g_eot;
505: --
506: cursor c_old_person(p_person_id number) is
507: select *
508: from per_all_people_f
509: where party_id is null
510: and effective_end_date <> hr_api.g_eot
511: and person_id = p_person_id;
512: --
610: end;
611: --
612: -- modified the input parameter for bug 12933615.
613: procedure create_update_contact_point
614: (p_party_id in per_all_people_f.party_id%type,
615: p_email_address in per_all_people_f.email_address%type) is
616: --
617: l_proc varchar2(80) := g_package||'create_update_contact_point';
618: --
611: --
612: -- modified the input parameter for bug 12933615.
613: procedure create_update_contact_point
614: (p_party_id in per_all_people_f.party_id%type,
615: p_email_address in per_all_people_f.email_address%type) is
616: --
617: l_proc varchar2(80) := g_package||'create_update_contact_point';
618: --
619: -- Cursor to select existing email address from hz_parties
1060: p_party_id in number
1061: )
1062: IS
1063: SELECT typ.system_person_type
1064: FROM per_all_people_f ppf
1065: ,per_person_types typ
1066: ,per_person_type_usages_f ptu
1067: WHERE ppf.party_id = p_party_id
1068: AND ppf.effective_end_date = p_effective_date
1089: p_person_id in number
1090: )
1091: IS
1092: SELECT typ.system_person_type
1093: FROM per_all_people_f ppf
1094: ,per_person_types typ
1095: ,per_person_type_usages_f ptu
1096: WHERE ppf.person_id = p_person_id
1097: AND ppf.effective_end_date = p_effective_date
1324: raise;
1325: end;
1326:
1327: begin
1328: update PER_ALL_PEOPLE_F
1329: set party_id = null
1330: where party_id = p_party_id;
1331: exception
1332: when no_data_found then
1412: t_pemp_party_id g_party_id_type;
1413: --
1414: cursor c_people is
1415: select papf.party_id
1416: from per_all_people_f papf,
1417: hr_tca_party_unmerge punm
1418: where papf.party_id = punm.party_id
1419: and punm.status = 'PURGE';
1420: --
1446: -- 4) Phones
1447: -- 5) Qualifications
1448: -- 6) Establishment Attendances
1449: -- 7) Previous Employers
1450: -- 8) Per all people f
1451: --
1452: -- Loop through all of a persons competence records
1453: --
1454: hr_utility.set_location('FIRST ATTACK : OPEN COMPETENCES',10);
1646: exit;
1647: end if;
1648: --
1649: forall i in t_papf_party_id.first..t_papf_party_id.last
1650: update per_all_people_f
1651: set party_id = null
1652: where party_id = t_papf_party_id(i);
1653: --
1654: commit;
1763: set party_id = p_party_id
1764: -- There is no person_id column in per_phones table
1765: -- where person_id = p_person_id;
1766: where parent_id = p_person_id
1767: and parent_table = 'PER_ALL_PEOPLE_F';
1768: exception
1769: when no_data_found then
1770: null;
1771: when others then
1794: raise;
1795: end;
1796:
1797: begin
1798: update PER_ALL_PEOPLE_F
1799: set party_id = p_party_id
1800: where person_id = p_person_id;
1801: exception
1802: when no_data_found then
1980: t_party_id_notto_purge g_party_id_type;
1981:
1982: cursor csr_get_party_ids is
1983: select distinct p.person_id, p.party_id
1984: from per_all_people_f p
1985: where p.rowid
1986: between p_start_rowid and p_end_rowid
1987: and p.party_id is not null
1988: and not exists (select null
2000: p_party_id in number
2001: )
2002: IS
2003: SELECT typ.system_person_type
2004: FROM per_all_people_f ppf
2005: ,per_person_types typ
2006: ,per_person_type_usages_f ptu
2007: WHERE ppf.party_id = p_party_id
2008: AND ppf.effective_end_date = p_effective_date
2160: --
2161: cursor get_rowid_range is
2162: select min(rowid),
2163: max(rowid)
2164: from per_all_people_f;
2165: --
2166: l_release_name fnd_product_groups.release_name%type;
2167: --
2168: cursor csr_get_release_name is
2389: -- |----------------------------< create_tca_person >--------------------------|
2390: -- ------------------------------------------------------------------------------
2391: --
2392: procedure create_tca_person
2393: (p_rec in out nocopy per_all_people_f%rowtype) is
2394: --
2395: l_proc varchar2(80) := g_package||'create_tca_person';
2396: l_return_status varchar2(30);
2397: l_msg_count number;
2825: fnd_message.raise_error;
2826: --
2827: end;
2828: --
2829: -- Assign party_id back to party_id for per_all_people_f row
2830: --
2831: hr_utility.set_location('Party ID = '||l_party_id,10);
2832: --
2833: p_rec.party_id := l_party_id;
2857: -- |------------------------------< upate_tca_person >--------------------------|
2858: -- ------------------------------------------------------------------------------
2859: --
2860: procedure update_tca_person
2861: (p_rec in out nocopy per_all_people_f%rowtype,
2862: p_overwrite_data in varchar2 default 'Y') is
2863: --
2864: l_proc varchar2(80) := g_package||'.update_tca_person';
2865: l_return_status varchar2(30);
3262: -- |------------------------< replicate_person_across_bg >----------------------|
3263: -- ------------------------------------------------------------------------------
3264: --
3265: procedure replicate_person_across_bg
3266: (p_rec in out nocopy per_all_people_f%rowtype,
3267: p_overwrite_data in varchar2 default 'Y') is
3268: --
3269: l_proc varchar2(80) := g_package||'.replicate_person_across_bg';
3270: l_host_leg varchar2(30);
3271: l_target_leg varchar2(30);
3272: --
3273: cursor c_person is
3274: select *
3275: from per_all_people_f
3276: where effective_start_date <= p_rec.effective_end_date
3277: and effective_end_date >= p_rec.effective_start_date
3278: and person_id <> p_rec.person_id
3279: and party_id = p_rec.party_id
3284: --
3285: cursor csr_get_person_details(cp_person_id number,
3286: cp_effective_date date) is
3287: select *
3288: from per_all_people_f
3289: where person_id = cp_person_id
3290: and cp_effective_date between effective_start_date and effective_end_date;
3291: l_person_rec csr_get_person_details%rowtype;
3292: --
3306: l_orig_hire_warning boolean;
3307: l_validation_start_date date;
3308: l_validation_end_date date;
3309: l_dummy_lock_id number;
3310: l_copy_rec per_all_people_f%rowtype;
3311: l_ref_person_id number;
3312: l_ref_effective_start_date date;
3313: --
3314: l_local_name per_all_people_f.local_name%TYPE;
3310: l_copy_rec per_all_people_f%rowtype;
3311: l_ref_person_id number;
3312: l_ref_effective_start_date date;
3313: --
3314: l_local_name per_all_people_f.local_name%TYPE;
3315: l_global_name per_all_people_f.global_name%TYPE;
3316: l_order_name per_all_people_f.order_name%TYPE;
3317: --
3318: begin
3311: l_ref_person_id number;
3312: l_ref_effective_start_date date;
3313: --
3314: l_local_name per_all_people_f.local_name%TYPE;
3315: l_global_name per_all_people_f.global_name%TYPE;
3316: l_order_name per_all_people_f.order_name%TYPE;
3317: --
3318: begin
3319: --
3312: l_ref_effective_start_date date;
3313: --
3314: l_local_name per_all_people_f.local_name%TYPE;
3315: l_global_name per_all_people_f.global_name%TYPE;
3316: l_order_name per_all_people_f.order_name%TYPE;
3317: --
3318: begin
3319: --
3320: hr_utility.set_location('Entering '||l_proc,10);
3476: l_ref_person_id := l_person(l_count).person_id;
3477: --
3478: select person_id
3479: into l_dummy_lock_id
3480: from per_all_people_f
3481: where l_ref_effective_start_date
3482: between effective_start_date
3483: and effective_end_date
3484: and person_id = l_ref_person_id
3739:
3740: l_ref_effective_start_date := l_person(l_count).effective_start_date;
3741: l_ref_person_id := l_person(l_count).person_id;
3742: --
3743: update per_all_people_f
3744: set last_name = p_rec.last_name,
3745: full_name = l_full_name,
3746: date_of_birth = l_copy_rec.date_of_birth,
3747: first_name = l_copy_rec.first_name,
3785: --
3786: l_ref_effective_start_date := l_person(l_count).effective_start_date;
3787: l_ref_person_id := l_person(l_count).person_id;
3788: --
3789: update per_all_people_f
3790: set effective_end_date = p_rec.effective_start_date-1
3791: where person_id = l_ref_person_id
3792: and l_ref_effective_start_date
3793: between effective_start_date
3796: hr_utility.set_location('Getting max OVN in update mode',10);
3797: --
3798: l_person(l_count).object_version_number :=
3799: dt_api.get_object_version_number
3800: (p_base_table_name => 'per_all_people_f',
3801: p_base_key_column => 'person_id',
3802: p_base_key_value => l_person(l_count).person_id);
3803: --
3804: hr_utility.set_location('inserting new record in update mode',10);
4050: hr_utility.set_location('Before insert for update event',10);
4051: hr_utility.set_location('Person ID '||l_person(l_count).person_id,10);
4052: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
4053: --
4054: insert into per_all_people_f
4055: (person_id,
4056: effective_start_date,
4057: effective_end_date,
4058: business_group_id,
4364: elsif l_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
4365: --
4366: hr_utility.set_location('updating record in update change insert mode',10);
4367: --
4368: update per_all_people_f
4369: set effective_end_date = p_rec.effective_start_date-1
4370: where person_id = l_person(l_count).person_id
4371: and p_rec.effective_start_date
4372: between effective_start_date
4375: hr_utility.set_location('getting max ovn in update change insert mode',10);
4376: --
4377: l_person(l_count).object_version_number :=
4378: dt_api.get_object_version_number
4379: (p_base_table_name => 'per_all_people_f',
4380: p_base_key_column => 'person_id',
4381: p_base_key_value => l_person(l_count).person_id);
4382: --
4383: hr_utility.set_location('inserting record in update change insert mode',10);
4625: p_local_name => l_local_name,
4626: p_duplicate_flag => l_duplicate_flag);
4627:
4628:
4629: insert into per_all_people_f
4630: (person_id,
4631: effective_start_date,
4632: effective_end_date,
4633: business_group_id,
4988: hr_utility.set_location('finding dt delete modes',10);
4989: --
4990: dt_api.find_dt_upd_modes
4991: (p_effective_date => p_rec.effective_end_date,
4992: p_base_table_name => 'PER_ALL_PEOPLE_F',
4993: p_base_key_column => 'PERSON_ID',
4994: p_base_key_value => l_person(l_count).person_id,
4995: p_correction => l_correction,
4996: p_update => l_update,
5018: if l_datetrack_mode = 'UPDATE' then
5019: --
5020: hr_utility.set_location('updating person in update mode',10);
5021: --
5022: update per_all_people_f
5023: set effective_end_date = p_rec.effective_end_date
5024: where person_id = l_person(l_count).person_id
5025: and p_rec.effective_start_date
5026: between effective_start_date
5029: hr_utility.set_location('getting max ovn in update mode',10);
5030: --
5031: l_person(l_count).object_version_number :=
5032: dt_api.get_object_version_number
5033: (p_base_table_name => 'per_all_people_f',
5034: p_base_key_column => 'person_id',
5035: p_base_key_value => l_person(l_count).person_id);
5036: --
5037: hr_utility.set_location('inserting record in update mode',10);
5037: hr_utility.set_location('inserting record in update mode',10);
5038: --
5039: -- Now the insert
5040: --
5041: insert into per_all_people_f
5042: (person_id,
5043: effective_start_date,
5044: effective_end_date,
5045: business_group_id,
5337: fnd_global.user_id,
5338: fnd_global.login_id,
5339: global_name,
5340: local_name
5341: from per_all_people_f
5342: where person_id = l_person(l_count).person_id
5343: and p_rec.effective_start_date-1
5344: between effective_start_date
5345: and effective_end_date;
5347: elsif l_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
5348: --
5349: hr_utility.set_location('updating record in update change insert mode',10);
5350: --
5351: update per_all_people_f
5352: set effective_end_date = p_rec.effective_end_date
5353: where person_id = l_person(l_count).person_id
5354: and p_rec.effective_start_date
5355: between effective_start_date
5358: hr_utility.set_location('getting max ovn in update change insert mode',10);
5359: --
5360: l_person(l_count).object_version_number :=
5361: dt_api.get_object_version_number
5362: (p_base_table_name => 'per_all_people_f',
5363: p_base_key_column => 'person_id',
5364: p_base_key_value => l_person(l_count).person_id);
5365: --
5366: -- Now the insert
5366: -- Now the insert
5367: --
5368: hr_utility.set_location('inserting record in update change insert mode',10);
5369: --
5370: insert into per_all_people_f
5371: (person_id,
5372: effective_start_date,
5373: effective_end_date,
5374: business_group_id,
5666: fnd_global.user_id,
5667: fnd_global.login_id,
5668: global_name,
5669: local_name
5670: from per_all_people_f
5671: where person_id = l_person(l_count).person_id
5672: and p_rec.effective_start_date-1
5673: between effective_start_date
5674: and effective_end_date;
5749: l_proc varchar2(80) := g_package||'.per_party_merge';
5750: --
5751: cursor c_person is
5752: select ppf.*
5753: from per_all_people_f ppf
5754: where ppf.party_id = p_from_fk_id
5755: order by ppf.effective_start_date;
5756: --
5757: l_person c_person%rowtype;
5806: --
5807: -- Update all the child table records.
5808: --
5809: */
5810: update per_all_people_f
5811: set party_id = l_person.party_id
5812: where person_id = l_person.person_id
5813: and effective_start_date = l_person.effective_start_date;
5814: --
5840: p_effective_date in date) return per_per_shd.g_rec_type is
5841: --
5842: cursor c1 is
5843: select *
5844: from per_all_people_f
5845: where party_id = p_party_id
5846: and p_effective_date
5847: between effective_start_date
5848: and effective_end_date;
5912: l_proc varchar2(80) := g_package||'migrate_all_hr_email';
5913: --
5914: cursor c_person is
5915: select party_id,email_address
5916: from per_all_people_f ppf
5917: where ppf.email_address is not null
5918: and mod(ppf.person_id,p_number_of_workers) = p_current_worker-1
5919: and ppf.effective_end_date = hr_api.g_eot
5920: and ppf.party_id is not null
5982: l_proc varchar2(80) := g_package||'migrate_all_hr_gender';
5983: --
5984: cursor c_person is
5985: select *
5986: from per_all_people_f ppf
5987: where mod(ppf.person_id,p_number_of_workers) = p_current_worker-1
5988: and ppf.effective_end_date = hr_api.g_eot
5989: and ppf.party_id is not null
5990: and exists(select null
6042: l_proc varchar2(80) := g_package||'get_person_details';
6043: --
6044: cursor c1 is
6045: select *
6046: from per_all_people_f
6047: where party_id = p_party_id
6048: and person_id = p_person_id
6049: and p_effective_date
6050: between effective_start_date
6132: l_elig_person_cnt number;
6133: -- Cursor to fetch the person id in the range.
6134: cursor csr_person is
6135: select person_id
6136: from per_all_people_f
6137: where party_id is null
6138: and rowid between p_start_rowid and p_end_rowid
6139: and effective_end_date = hr_api.g_eot;
6140: --
6141: -- Cursor to fecth the details for a person.
6142: --
6143: cursor csr_per_details(p_person_id number) is
6144: select *
6145: from per_all_people_f
6146: where person_id = p_person_id
6147: and effective_end_date = hr_api.g_eot;
6148: --
6149: l_per_rec csr_per_details%rowtype;
6193: --
6194: if t_elig_person_id.count > 0 then
6195: --
6196: forall i in t_elig_person_id.first..t_elig_person_id.last
6197: update per_all_people_f
6198: set party_id = t_party_id(i)
6199: where person_id = t_elig_person_id(i);
6200: --
6201: -- Bulk update competence records
6227: forall i in t_elig_person_id.first..t_elig_person_id.last
6228: update per_phones
6229: set party_id = t_party_id(i)
6230: where parent_id = t_elig_person_id(i)
6231: and parent_table = 'PER_ALL_PEOPLE_F';
6232: --
6233: -- Bulk update qualification records
6234: --
6235: forall i in t_elig_person_id.first..t_elig_person_id.last
6288: l_proc varchar2(80) := g_package||'migrate_all_hr_email2';
6289: --
6290: cursor c_person is
6291: select party_id,email_address -- fix for bug 12933615
6292: from per_all_people_f ppf
6293: where ppf.email_address is not null
6294: and ppf.ROWID between p_start_rowid and p_end_rowid
6295: and ppf.effective_end_date = hr_api.g_eot
6296: and ppf.party_id is not null