[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: --
609: --
610: end;
611: --
612: procedure create_update_contact_point
613: (p_rec in out nocopy per_all_people_f%rowtype) is
614: --
615: l_proc varchar2(80) := g_package||'create_update_contact_point';
616: --
617: -- Cursor to select existing email address from hz_parties
1057: p_party_id in number
1058: )
1059: IS
1060: SELECT typ.system_person_type
1061: FROM per_all_people_f ppf
1062: ,per_person_types typ
1063: ,per_person_type_usages_f ptu
1064: WHERE ppf.party_id = p_party_id
1065: AND ppf.effective_end_date = p_effective_date
1086: p_person_id in number
1087: )
1088: IS
1089: SELECT typ.system_person_type
1090: FROM per_all_people_f ppf
1091: ,per_person_types typ
1092: ,per_person_type_usages_f ptu
1093: WHERE ppf.person_id = p_person_id
1094: AND ppf.effective_end_date = p_effective_date
1321: raise;
1322: end;
1323:
1324: begin
1325: update PER_ALL_PEOPLE_F
1326: set party_id = null
1327: where party_id = p_party_id;
1328: exception
1329: when no_data_found then
1409: t_pemp_party_id g_party_id_type;
1410: --
1411: cursor c_people is
1412: select papf.party_id
1413: from per_all_people_f papf,
1414: hr_tca_party_unmerge punm
1415: where papf.party_id = punm.party_id
1416: and punm.status = 'PURGE';
1417: --
1443: -- 4) Phones
1444: -- 5) Qualifications
1445: -- 6) Establishment Attendances
1446: -- 7) Previous Employers
1447: -- 8) Per all people f
1448: --
1449: -- Loop through all of a persons competence records
1450: --
1451: hr_utility.set_location('FIRST ATTACK : OPEN COMPETENCES',10);
1643: exit;
1644: end if;
1645: --
1646: forall i in t_papf_party_id.first..t_papf_party_id.last
1647: update per_all_people_f
1648: set party_id = null
1649: where party_id = t_papf_party_id(i);
1650: --
1651: commit;
1760: set party_id = p_party_id
1761: -- There is no person_id column in per_phones table
1762: -- where person_id = p_person_id;
1763: where parent_id = p_person_id
1764: and parent_table = 'PER_ALL_PEOPLE_F';
1765: exception
1766: when no_data_found then
1767: null;
1768: when others then
1791: raise;
1792: end;
1793:
1794: begin
1795: update PER_ALL_PEOPLE_F
1796: set party_id = p_party_id
1797: where person_id = p_person_id;
1798: exception
1799: when no_data_found then
1977: t_party_id_notto_purge g_party_id_type;
1978:
1979: cursor csr_get_party_ids is
1980: select distinct p.person_id, p.party_id
1981: from per_all_people_f p
1982: where p.rowid
1983: between p_start_rowid and p_end_rowid
1984: and p.party_id is not null
1985: and not exists (select null
1997: p_party_id in number
1998: )
1999: IS
2000: SELECT typ.system_person_type
2001: FROM per_all_people_f ppf
2002: ,per_person_types typ
2003: ,per_person_type_usages_f ptu
2004: WHERE ppf.party_id = p_party_id
2005: AND ppf.effective_end_date = p_effective_date
2157: --
2158: cursor get_rowid_range is
2159: select min(rowid),
2160: max(rowid)
2161: from per_all_people_f;
2162: --
2163: l_release_name fnd_product_groups.release_name%type;
2164: --
2165: cursor csr_get_release_name is
2386: -- |----------------------------< create_tca_person >--------------------------|
2387: -- ------------------------------------------------------------------------------
2388: --
2389: procedure create_tca_person
2390: (p_rec in out nocopy per_all_people_f%rowtype) is
2391: --
2392: l_proc varchar2(80) := g_package||'create_tca_person';
2393: l_return_status varchar2(30);
2394: l_msg_count number;
2820: fnd_message.raise_error;
2821: --
2822: end;
2823: --
2824: -- Assign party_id back to party_id for per_all_people_f row
2825: --
2826: hr_utility.set_location('Party ID = '||l_party_id,10);
2827: --
2828: p_rec.party_id := l_party_id;
2851: -- |------------------------------< upate_tca_person >--------------------------|
2852: -- ------------------------------------------------------------------------------
2853: --
2854: procedure update_tca_person
2855: (p_rec in out nocopy per_all_people_f%rowtype,
2856: p_overwrite_data in varchar2 default 'Y') is
2857: --
2858: l_proc varchar2(80) := g_package||'.update_tca_person';
2859: l_return_status varchar2(30);
3247: -- |------------------------< replicate_person_across_bg >----------------------|
3248: -- ------------------------------------------------------------------------------
3249: --
3250: procedure replicate_person_across_bg
3251: (p_rec in out nocopy per_all_people_f%rowtype,
3252: p_overwrite_data in varchar2 default 'Y') is
3253: --
3254: l_proc varchar2(80) := g_package||'.replicate_person_across_bg';
3255: l_host_leg varchar2(30);
3256: l_target_leg varchar2(30);
3257: --
3258: cursor c_person is
3259: select *
3260: from per_all_people_f
3261: where effective_start_date <= p_rec.effective_end_date
3262: and effective_end_date >= p_rec.effective_start_date
3263: and person_id <> p_rec.person_id
3264: and party_id = p_rec.party_id
3269: --
3270: cursor csr_get_person_details(cp_person_id number,
3271: cp_effective_date date) is
3272: select *
3273: from per_all_people_f
3274: where person_id = cp_person_id
3275: and cp_effective_date between effective_start_date and effective_end_date;
3276: l_person_rec csr_get_person_details%rowtype;
3277: --
3291: l_orig_hire_warning boolean;
3292: l_validation_start_date date;
3293: l_validation_end_date date;
3294: l_dummy_lock_id number;
3295: l_copy_rec per_all_people_f%rowtype;
3296: l_ref_person_id number;
3297: l_ref_effective_start_date date;
3298: --
3299: l_local_name per_all_people_f.local_name%TYPE;
3295: l_copy_rec per_all_people_f%rowtype;
3296: l_ref_person_id number;
3297: l_ref_effective_start_date date;
3298: --
3299: l_local_name per_all_people_f.local_name%TYPE;
3300: l_global_name per_all_people_f.global_name%TYPE;
3301: l_order_name per_all_people_f.order_name%TYPE;
3302: --
3303: begin
3296: l_ref_person_id number;
3297: l_ref_effective_start_date date;
3298: --
3299: l_local_name per_all_people_f.local_name%TYPE;
3300: l_global_name per_all_people_f.global_name%TYPE;
3301: l_order_name per_all_people_f.order_name%TYPE;
3302: --
3303: begin
3304: --
3297: l_ref_effective_start_date date;
3298: --
3299: l_local_name per_all_people_f.local_name%TYPE;
3300: l_global_name per_all_people_f.global_name%TYPE;
3301: l_order_name per_all_people_f.order_name%TYPE;
3302: --
3303: begin
3304: --
3305: hr_utility.set_location('Entering '||l_proc,10);
3461: l_ref_person_id := l_person(l_count).person_id;
3462: --
3463: select person_id
3464: into l_dummy_lock_id
3465: from per_all_people_f
3466: where l_ref_effective_start_date
3467: between effective_start_date
3468: and effective_end_date
3469: and person_id = l_ref_person_id
3724:
3725: l_ref_effective_start_date := l_person(l_count).effective_start_date;
3726: l_ref_person_id := l_person(l_count).person_id;
3727: --
3728: update per_all_people_f
3729: set last_name = p_rec.last_name,
3730: full_name = l_full_name,
3731: date_of_birth = l_copy_rec.date_of_birth,
3732: first_name = l_copy_rec.first_name,
3770: --
3771: l_ref_effective_start_date := l_person(l_count).effective_start_date;
3772: l_ref_person_id := l_person(l_count).person_id;
3773: --
3774: update per_all_people_f
3775: set effective_end_date = p_rec.effective_start_date-1
3776: where person_id = l_ref_person_id
3777: and l_ref_effective_start_date
3778: between effective_start_date
3781: hr_utility.set_location('Getting max OVN in update mode',10);
3782: --
3783: l_person(l_count).object_version_number :=
3784: dt_api.get_object_version_number
3785: (p_base_table_name => 'per_all_people_f',
3786: p_base_key_column => 'person_id',
3787: p_base_key_value => l_person(l_count).person_id);
3788: --
3789: hr_utility.set_location('inserting new record in update mode',10);
4035: hr_utility.set_location('Before insert for update event',10);
4036: hr_utility.set_location('Person ID '||l_person(l_count).person_id,10);
4037: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
4038: --
4039: insert into per_all_people_f
4040: (person_id,
4041: effective_start_date,
4042: effective_end_date,
4043: business_group_id,
4349: elsif l_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
4350: --
4351: hr_utility.set_location('updating record in update change insert mode',10);
4352: --
4353: update per_all_people_f
4354: set effective_end_date = p_rec.effective_start_date-1
4355: where person_id = l_person(l_count).person_id
4356: and p_rec.effective_start_date
4357: between effective_start_date
4360: hr_utility.set_location('getting max ovn in update change insert mode',10);
4361: --
4362: l_person(l_count).object_version_number :=
4363: dt_api.get_object_version_number
4364: (p_base_table_name => 'per_all_people_f',
4365: p_base_key_column => 'person_id',
4366: p_base_key_value => l_person(l_count).person_id);
4367: --
4368: hr_utility.set_location('inserting record in update change insert mode',10);
4610: p_local_name => l_local_name,
4611: p_duplicate_flag => l_duplicate_flag);
4612:
4613:
4614: insert into per_all_people_f
4615: (person_id,
4616: effective_start_date,
4617: effective_end_date,
4618: business_group_id,
4969: hr_utility.set_location('finding dt delete modes',10);
4970: --
4971: dt_api.find_dt_upd_modes
4972: (p_effective_date => p_rec.effective_end_date,
4973: p_base_table_name => 'PER_ALL_PEOPLE_F',
4974: p_base_key_column => 'PERSON_ID',
4975: p_base_key_value => l_person(l_count).person_id,
4976: p_correction => l_correction,
4977: p_update => l_update,
4999: if l_datetrack_mode = 'UPDATE' then
5000: --
5001: hr_utility.set_location('updating person in update mode',10);
5002: --
5003: update per_all_people_f
5004: set effective_end_date = p_rec.effective_end_date
5005: where person_id = l_person(l_count).person_id
5006: and p_rec.effective_start_date
5007: between effective_start_date
5010: hr_utility.set_location('getting max ovn in update mode',10);
5011: --
5012: l_person(l_count).object_version_number :=
5013: dt_api.get_object_version_number
5014: (p_base_table_name => 'per_all_people_f',
5015: p_base_key_column => 'person_id',
5016: p_base_key_value => l_person(l_count).person_id);
5017: --
5018: hr_utility.set_location('inserting record in update mode',10);
5018: hr_utility.set_location('inserting record in update mode',10);
5019: --
5020: -- Now the insert
5021: --
5022: insert into per_all_people_f
5023: (person_id,
5024: effective_start_date,
5025: effective_end_date,
5026: business_group_id,
5318: fnd_global.user_id,
5319: fnd_global.login_id,
5320: global_name,
5321: local_name
5322: from per_all_people_f
5323: where person_id = l_person(l_count).person_id
5324: and p_rec.effective_start_date-1
5325: between effective_start_date
5326: and effective_end_date;
5328: elsif l_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
5329: --
5330: hr_utility.set_location('updating record in update change insert mode',10);
5331: --
5332: update per_all_people_f
5333: set effective_end_date = p_rec.effective_end_date
5334: where person_id = l_person(l_count).person_id
5335: and p_rec.effective_start_date
5336: between effective_start_date
5339: hr_utility.set_location('getting max ovn in update change insert mode',10);
5340: --
5341: l_person(l_count).object_version_number :=
5342: dt_api.get_object_version_number
5343: (p_base_table_name => 'per_all_people_f',
5344: p_base_key_column => 'person_id',
5345: p_base_key_value => l_person(l_count).person_id);
5346: --
5347: -- Now the insert
5347: -- Now the insert
5348: --
5349: hr_utility.set_location('inserting record in update change insert mode',10);
5350: --
5351: insert into per_all_people_f
5352: (person_id,
5353: effective_start_date,
5354: effective_end_date,
5355: business_group_id,
5647: fnd_global.user_id,
5648: fnd_global.login_id,
5649: global_name,
5650: local_name
5651: from per_all_people_f
5652: where person_id = l_person(l_count).person_id
5653: and p_rec.effective_start_date-1
5654: between effective_start_date
5655: and effective_end_date;
5730: l_proc varchar2(80) := g_package||'.per_party_merge';
5731: --
5732: cursor c_person is
5733: select ppf.*
5734: from per_all_people_f ppf
5735: where ppf.party_id = p_from_fk_id
5736: order by ppf.effective_start_date;
5737: --
5738: l_person c_person%rowtype;
5787: --
5788: -- Update all the child table records.
5789: --
5790: */
5791: update per_all_people_f
5792: set party_id = l_person.party_id
5793: where person_id = l_person.person_id
5794: and effective_start_date = l_person.effective_start_date;
5795: --
5821: p_effective_date in date) return per_per_shd.g_rec_type is
5822: --
5823: cursor c1 is
5824: select *
5825: from per_all_people_f
5826: where party_id = p_party_id
5827: and p_effective_date
5828: between effective_start_date
5829: and effective_end_date;
5893: l_proc varchar2(80) := g_package||'migrate_all_hr_email';
5894: --
5895: cursor c_person is
5896: select *
5897: from per_all_people_f ppf
5898: where ppf.email_address is not null
5899: and mod(ppf.person_id,p_number_of_workers) = p_current_worker-1
5900: and ppf.effective_end_date = hr_api.g_eot
5901: and ppf.party_id is not null
5962: l_proc varchar2(80) := g_package||'migrate_all_hr_gender';
5963: --
5964: cursor c_person is
5965: select *
5966: from per_all_people_f ppf
5967: where mod(ppf.person_id,p_number_of_workers) = p_current_worker-1
5968: and ppf.effective_end_date = hr_api.g_eot
5969: and ppf.party_id is not null
5970: and exists(select null
6022: l_proc varchar2(80) := g_package||'get_person_details';
6023: --
6024: cursor c1 is
6025: select *
6026: from per_all_people_f
6027: where party_id = p_party_id
6028: and person_id = p_person_id
6029: and p_effective_date
6030: between effective_start_date
6112: l_elig_person_cnt number;
6113: -- Cursor to fetch the person id in the range.
6114: cursor csr_person is
6115: select person_id
6116: from per_all_people_f
6117: where party_id is null
6118: and rowid between p_start_rowid and p_end_rowid
6119: and effective_end_date = hr_api.g_eot;
6120: --
6121: -- Cursor to fecth the details for a person.
6122: --
6123: cursor csr_per_details(p_person_id number) is
6124: select *
6125: from per_all_people_f
6126: where person_id = p_person_id
6127: and effective_end_date = hr_api.g_eot;
6128: --
6129: l_per_rec csr_per_details%rowtype;
6173: --
6174: if t_elig_person_id.count > 0 then
6175: --
6176: forall i in t_elig_person_id.first..t_elig_person_id.last
6177: update per_all_people_f
6178: set party_id = t_party_id(i)
6179: where person_id = t_elig_person_id(i);
6180: --
6181: -- Bulk update competence records
6207: forall i in t_elig_person_id.first..t_elig_person_id.last
6208: update per_phones
6209: set party_id = t_party_id(i)
6210: where parent_id = t_elig_person_id(i)
6211: and parent_table = 'PER_ALL_PEOPLE_F';
6212: --
6213: -- Bulk update qualification records
6214: --
6215: forall i in t_elig_person_id.first..t_elig_person_id.last
6268: l_proc varchar2(80) := g_package||'migrate_all_hr_email2';
6269: --
6270: cursor c_person is
6271: select /*+ rowid(ppf) */ *
6272: from per_all_people_f ppf
6273: where ppf.email_address is not null
6274: and ppf.ROWID between p_start_rowid and p_end_rowid
6275: and ppf.effective_end_date = hr_api.g_eot
6276: and ppf.party_id is not null