[Home] [Help]
21: -- column to a value that has been passed in.
22: -- This is called during the initial migration of all persons and
23: -- for when a manual party merge takes place.
24: --
25: hr_utility.set_location('Entering '||l_proc,10);
26: --
27: -- Call the hr_person_api.update_person. This causes problems as there are
28: -- rules within the APi that are not enforced properly by the form hence
29: -- many people will experience rows that will not be migrated. We can't
52: and p_rec.effective_start_date
53: between effective_start_date
54: and effective_end_date;
55: --
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
124: l_prev_employers c_prev_employers%rowtype;
125: --
126: begin
127: --
128: hr_utility.set_location('Entering '||l_proc,10);
129: --
130: -- This package repeatedly calls the child table related API's for a
131: -- particular person and update the party_id column to the party_id
132: -- that has been passed in.
140: -- 7) Previous Employers
141: --
142: -- Loop through all of a persons competence records
143: --
144: hr_utility.set_location('FIRST ATTACK : OPEN COMPETENCES',10);
145: --
146: open c_competences;
147: --
148: loop
165: end loop;
166: --
167: close c_competences;
168: --
169: hr_utility.set_location('FIRST ATTACK : CLOSE COMPETENCES',10);
170: --
171: -- Loop through all of a persons event records
172: --
173: hr_utility.set_location('FIRST ATTACK : OPEN EVENTS',10);
169: hr_utility.set_location('FIRST ATTACK : CLOSE COMPETENCES',10);
170: --
171: -- Loop through all of a persons event records
172: --
173: hr_utility.set_location('FIRST ATTACK : OPEN EVENTS',10);
174: open c_events;
175: --
176: loop
177: --
190: --
191: end loop;
192: --
193: close c_events;
194: hr_utility.set_location('FIRST ATTACK : CLOSE EVENTS',10);
195: --
196: -- Loop through all of a persons address records
197: --
198: hr_utility.set_location('FIRST ATTACK : OPEN ADDRESSES',10);
194: hr_utility.set_location('FIRST ATTACK : CLOSE EVENTS',10);
195: --
196: -- Loop through all of a persons address records
197: --
198: hr_utility.set_location('FIRST ATTACK : OPEN ADDRESSES',10);
199: open c_addresses;
200: --
201: loop
202: --
216: --
217: end loop;
218: --
219: close c_addresses;
220: hr_utility.set_location('FIRST ATTACK : CLOSE ADDRESSES',10);
221: --
222: -- Loop through all of a persons phone records
223: --
224: hr_utility.set_location('FIRST ATTACK : OPEN PHONES',10);
220: hr_utility.set_location('FIRST ATTACK : CLOSE ADDRESSES',10);
221: --
222: -- Loop through all of a persons phone records
223: --
224: hr_utility.set_location('FIRST ATTACK : OPEN PHONES',10);
225: open c_phones;
226: --
227: loop
228: --
242: --
243: end loop;
244: --
245: close c_phones;
246: hr_utility.set_location('FIRST ATTACK : CLOSE PHONES',10);
247: --
248: -- Loop through all of a persons qualification records
249: --
250: open c_qualifications;
318: end loop;
319: --
320: close c_prev_employers;
321: --
322: hr_utility.set_location('Leaving '||l_proc,10);
323: --
324: end update_child_tables;
325: --
326: function propagate_value
341: -- else
342: -- only overwrite old value if new value is not null
343: -- end if;
344: --
345: hr_utility.set_location('Entering '||l_proc,10);
346: --
347: -- Bug fix 4146782
348: -- If condition added to check whether the value is not
349: -- null. Values are propogated across BG only if it is
366: end if;
367: --
368: end if;
369: --
370: hr_utility.set_location('Leaving '||l_proc,10);
371: --
372: end propagate_value;
373: --
374: function propagate_value
389: -- else
390: -- only overwrite old value if new value is not null
391: -- end if;
392: --
393: hr_utility.set_location('Entering '||l_proc,10);
394: --
395: -- Bug fix 4146782
396: -- If condition added to check whether the value is not
397: -- null. Values are propogated across BG only if it is
414: end if;
415: --
416: end if;
417: --
418: hr_utility.set_location('Leaving '||l_proc,10);
419: --
420: end propagate_value;
421: --
422: function propagate_value
437: -- else
438: -- only overwrite old value if new value is not null
439: -- end if;
440: --
441: hr_utility.set_location('Entering '||l_proc,10);
442: --
443: -- Bug fix 4146782
444: -- If condition added to check whether the value is not
445: -- null. Values are propogated across BG only if it is
462: end if;
463: --
464: end if;
465: --
466: hr_utility.set_location('Leaving '||l_proc,10);
467: --
468: end propagate_value;
469: --
470: function get_legislation_code
477: --
478: -- This procedure returns the legislation code for a particular business
479: -- group.
480: --
481: hr_utility.set_location('Entering '||l_proc,10);
482: --
483: select legislation_code
484: into l_legislation_code
485: from per_business_groups
484: into l_legislation_code
485: from per_business_groups
486: where business_group_id = p_business_group_id;
487: --
488: hr_utility.set_location('Leaving '||l_proc,10);
489: --
490: return l_legislation_code;
491: --
492: end get_legislation_code;
516: l_data_migrator_mode varchar2(30);
517: --
518: begin
519: --
520: hr_utility.set_location('Entering '||l_proc,10);
521: --
522: -- This routine will create party_records for all person
523: -- records in HRMS. It will then link the created party
524: -- to the child tables of person and these include
604: g_count := 0;
605: --
606: hr_general.g_data_migrator_mode := l_data_migrator_mode;
607: --
608: hr_utility.set_location('Entering '||l_proc,10);
609: --
610: end;
611: --
612: -- modified the input parameter for bug 12933615.
647: l_email_changed boolean:=false;
648: --bug no 5546586 ends here
649: BEGIN
650: --
651: hr_utility.set_location('Entering '||l_proc,10);
652: --
653: --Added for bug 2648797
654: l_contact_point_rec.created_by_module := 'HR API';
655: --
672: OPEN c1;
673: FETCH c1 INTO l_c1;
674: IF c1%found THEN
675: --
676: hr_utility.set_location(l_proc,20);
677: --
678: --Added for bug 2648797
679: --Get the object version number
680: select max(object_version_number)
687: --bug no 5546586 ends here
688: IF (l_c1.email_address is null or l_c1.email_address = 'NULL')
689: and (p_email_address is not null) THEN
690: --
691: hr_utility.set_location('Entering '||l_proc,30);
692: --
693: l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
694: l_email_rec.email_address := p_email_address;
695: --
716: p_init_msg_list => l_init_msg_list,
717: p_object_version_number => l_object_version_number
718: );
719: --
720: hr_utility.set_location(l_proc,40);
721: --
722: if l_return_status in ('E','U') then
723: --
724: -- bug 4632157 Starts
729: end loop;
730: end if;
731: -- bug 4632157 ends
732: --
733: hr_utility.set_location(l_msg_data,10);
734: fnd_message.raise_error;
735:
736: end if;
737:
738:
739: ELSIF (l_c1.email_address is not null and p_email_address is not null)
740: and (l_c1.email_address <> p_email_address) THEN
741: --
742: hr_utility.set_location(l_proc,50);
743: --
744: l_contact_point_rec.contact_point_type := 'EMAIL';
745: l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
746: l_contact_point_rec.owner_table_id := p_party_id;
769: p_init_msg_list => l_init_msg_list,
770: x_contact_point_id => l_contact_point_id
771: );
772: --
773: hr_utility.set_location(l_proc,60);
774: --
775: if l_return_status in ('E','U') then
776: --
777:
774: --
775: if l_return_status in ('E','U') then
776: --
777:
778: hr_utility.set_location(l_msg_count,10);
779: -- bug 4632157 Starts
780: if l_msg_count > 1 then
781: for i in 1..l_msg_count
782: loop
783: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
784: end loop;
785: end if;
786: -- bug 4632157 ends
787: hr_utility.set_location(l_msg_data,10);
788: fnd_message.raise_error;
789: --
790: end if;
791: --
789: --
790: end if;
791: --
792:
793: hr_utility.set_location(l_proc,70);
794: --
795: l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
796: l_contact_point_rec.status := 'I';
797: l_contact_point_rec.primary_flag := 'N';
830: p_init_msg_list => l_init_msg_list,
831: p_object_version_number => l_object_version_number
832: );
833: --
834: hr_utility.set_location(l_proc,80);
835: --
836:
837: if l_return_status in ('E','U') then
838: --
844: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
845: end loop;
846: end if;
847: -- bug 4632157 ends
848: hr_utility.set_location(l_msg_data,10);
849: fnd_message.raise_error;
850: --
851: end if;
852: -- added <> 'NULL' condition below for bug 4694355
852: -- added <> 'NULL' condition below for bug 4694355
853: ELSIF ( l_c1.email_address is not null and l_c1.email_address <> 'NULL' )
854: and (p_email_address is null) then
855: --
856: hr_utility.set_location(l_proc,90);
857: --
858:
859: l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
860: l_contact_point_rec.status := 'I';
881: p_init_msg_list => l_init_msg_list,
882: p_object_version_number => l_object_version_number
883: );
884: --
885: hr_utility.set_location(l_proc,100);
886: --
887: if l_return_status in ('E','U') then
888: --
889:
894: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
895: end loop;
896: end if;
897: -- bug 4632157 ends
898: hr_utility.set_location(l_msg_data,10);
899: fnd_message.raise_error;
900: --
901: end if;
902:
905: end if;
906: --bug no 5546586 ends here
907: ELSE
908: --
909: hr_utility.set_location(l_proc,110);
910: --
911: IF (p_email_address is not null) THEN
912: --
913: hr_utility.set_location(l_proc,120);
909: hr_utility.set_location(l_proc,110);
910: --
911: IF (p_email_address is not null) THEN
912: --
913: hr_utility.set_location(l_proc,120);
914: --
915: l_contact_point_rec.contact_point_type := 'EMAIL';
916: l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
917: l_contact_point_rec.owner_table_id := p_party_id;
941: p_init_msg_list => l_init_msg_list,
942: x_contact_point_id => l_contact_point_id
943: );
944: --
945: hr_utility.set_location(l_proc,130);
946: --
947: if l_return_status in ('E','U') then
948: --
949:
946: --
947: if l_return_status in ('E','U') then
948: --
949:
950: hr_utility.set_location(l_msg_count,10);
951: -- bug 4632157 Starts
952: if l_msg_count > 1 then
953: for i in 1..l_msg_count
954: loop
955: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
956: end loop;
957: end if;
958: -- bug 4632157 ends
959: hr_utility.set_location(l_msg_data,10);
960: fnd_message.raise_error;
961: --
962: end if;
963:
964: END IF;
965: END IF;
966:
967: --
968: hr_utility.set_location('Leaving '||l_proc,140);
969: --
970:
971: end create_update_contact_point;
972:
1006: l_separator varchar2(1) :='_';
1007: l_proc varchar2(80) := g_package||'.get_system_person_type';
1008: BEGIN
1009: --
1010: hr_utility.set_location('Entering '||l_proc,10);
1011: --
1012: FOR l_person_type IN csr_person_types
1013: (p_effective_date => p_effective_date
1014: ,p_person_id => p_person_id
1023: || l_person_type.system_person_type;
1024: END IF;
1025: END LOOP;
1026: --
1027: hr_utility.set_location('Leaving '||l_proc,10);
1028: --
1029: RETURN l_system_person_type;
1030: --
1031: END get_system_person_type;
1112:
1113: --
1114: Begin
1115: --
1116: hr_utility.set_location('Entering '||l_proc,10);
1117: --
1118: l_system_person_type := per_hrtca_merge.get_system_person_type(
1119: l_effective_date,
1120: p_person_id);
1135: --
1136: for person_type in person_type_person_cur(l_effective_date,p_person_id)
1137: loop
1138: --
1139: hr_utility.set_location('Person Type '||person_type.system_person_type,20);
1140: --
1141: --
1142: -- 4120469 Added the Ex-Emp/Ex-Cwk/Ex-Apl
1143: --
1174: -- with a valid person_type exists.
1175: --
1176: for person_type in person_type_party_cur(l_effective_date,p_party_id) loop
1177: --
1178: hr_utility.set_location('Person Type '||person_type.system_person_type,30);
1179: --
1180: --
1181: -- 4120469 Added the Ex-Emp/Ex-Cwk/Ex-Apl
1182: --
1216: l_return_value := 'CREATE PARTY';
1217: end if;
1218: --
1219: -- End of fix for IRC Bug 3202002
1220: hr_utility.set_location('Leaving '||l_proc,10);
1221: --
1222: return (l_return_value);
1223: --
1224: end get_tca_merge_actions;
1239: l_proc varchar2(80) := g_package||'clear_party_from_hrms';
1240:
1241: begin
1242:
1243: hr_utility.set_location('Entering : '||l_proc,10);
1244: /*
1245: ** Clear party_id information from all records relating to the current
1246: ** party_id across all business groups.
1247: **
1334: when others then
1335: raise;
1336: end;
1337:
1338: hr_utility.set_location('Leaving : '||l_proc, 100);
1339:
1340: end clear_party_from_hrms;
1341: --
1342: -- ------------------------------------------------------------------------------
1426: hr_general.g_data_migrator_mode := 'Y';
1427: --
1428: --
1429: /*
1430: hr_utility.set_location('Entering : '||l_proc,10);
1431: ** Clear party_id information from all records relating to the current
1432: ** party_id across all business groups.
1433: **
1434: ** For performance reasons just use direct SQL and not the APIs
1433: **
1434: ** For performance reasons just use direct SQL and not the APIs
1435: */
1436:
1437: hr_utility.set_location('Entering '||l_proc,10);
1438: --
1439: -- This package repeatedly calls the child table related API's for a
1440: -- particular party and updates the party_id column to null for the
1441: -- party_id that has been passed in.
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);
1455: --
1456: open c_competences;
1457: --
1458: loop
1476: close c_competences;
1477: --
1478: -- Loop through all of a persons event records
1479: --
1480: hr_utility.set_location('FIRST ATTACK : OPEN EVENTS',10);
1481: open c_events;
1482: --
1483: loop
1484: --
1499: --
1500: end loop;
1501: --
1502: close c_events;
1503: --hr_utility.set_location('FIRST ATTACK : CLOSE EVENTS',10);
1504: --
1505: -- Loop through all of a persons address records
1506: --
1507: hr_utility.set_location('FIRST ATTACK : OPEN ADDRESSES',10);
1503: --hr_utility.set_location('FIRST ATTACK : CLOSE EVENTS',10);
1504: --
1505: -- Loop through all of a persons address records
1506: --
1507: hr_utility.set_location('FIRST ATTACK : OPEN ADDRESSES',10);
1508: open c_addresses;
1509: --
1510: loop
1511: --
1526: --
1527: end loop;
1528: --
1529: close c_addresses;
1530: --hr_utility.set_location('FIRST ATTACK : CLOSE ADDRESSES',10);
1531: --
1532: -- Loop through all of a persons phone records
1533: --
1534: hr_utility.set_location('FIRST ATTACK : OPEN PHONES',10);
1530: --hr_utility.set_location('FIRST ATTACK : CLOSE ADDRESSES',10);
1531: --
1532: -- Loop through all of a persons phone records
1533: --
1534: hr_utility.set_location('FIRST ATTACK : OPEN PHONES',10);
1535: open c_phones;
1536: --
1537: loop
1538: --
1553: --
1554: end loop;
1555: --
1556: close c_phones;
1557: --hr_utility.set_location('FIRST ATTACK : CLOSE PHONES',10);
1558: --
1559: -- Loop through all of a persons qualification records
1560: --
1561: open c_qualifications;
1658: end loop;
1659: --
1660: close c_people;
1661: --
1662: hr_utility.set_location('Leaving '||l_proc,10);
1663: --
1664: --
1665: hr_general.g_data_migrator_mode := l_data_migrator_mode;
1666: --
1688: -- Bug fix 3632535 ends here
1689:
1690: begin
1691:
1692: hr_utility.set_location('Entering : '||l_proc,10);
1693: /*
1694: ** Clear party_id information from all records relating to the current
1695: ** party_id across all business groups.
1696: **
1804: when others then
1805: raise;
1806: end;
1807:
1808: hr_utility.set_location('Leaving : '||l_proc, 100);
1809:
1810: end set_party_in_hrms;
1811:
1812: --
1819: procedure clear_purge_parties_temp
1820: is
1821: l_proc varchar2(100) := g_package||'.clear_purge_parties_temp';
1822: begin
1823: hr_utility.set_location('Entering :'||l_proc,10);
1824: delete from hr_purge_parties_gt;
1825: hr_utility.set_location('Leaving :'||l_proc,20);
1826: end;
1827:
1821: l_proc varchar2(100) := g_package||'.clear_purge_parties_temp';
1822: begin
1823: hr_utility.set_location('Entering :'||l_proc,10);
1824: delete from hr_purge_parties_gt;
1825: hr_utility.set_location('Leaving :'||l_proc,20);
1826: end;
1827:
1828: --
1829: -- ------------------------------------------------------------------------------
1837: procedure add_party_for_purge(p_party_id number)
1838: is
1839: l_proc varchar2(100) := g_package||'add_party_for_purge';
1840: begin
1841: hr_utility.set_location('Entering : '||l_proc,10);
1842:
1843: begin
1844: insert into hr_purge_parties_gt (party_id) values (p_party_id);
1845: exception
1846: when others then
1847: raise;
1848: end;
1849:
1850: hr_utility.set_location('Leaving : '||l_proc,20);
1851: end add_party_for_purge;
1852:
1853: --
1854: -- ------------------------------------------------------------------------------
1883: l_msg_count number;
1884: l_msg_data varchar2(1000);
1885: begin
1886:
1887: hr_utility.set_location('Entering : '||l_proc,10);
1888:
1889: /* Insert the details into HZ_PURGE_BATCHES.
1890: */
1891: hr_utility.set_location(l_proc,30);
1887: hr_utility.set_location('Entering : '||l_proc,10);
1888:
1889: /* Insert the details into HZ_PURGE_BATCHES.
1890: */
1891: hr_utility.set_location(l_proc,30);
1892: select hz_purge_batches_s.nextval
1893: into l_batchid
1894: from sys.dual;
1895:
1892: select hz_purge_batches_s.nextval
1893: into l_batchid
1894: from sys.dual;
1895:
1896: hr_utility.set_location(l_proc,40);
1897: insert into hz_purge_batches (batch_id,
1898: batch_name,
1899: subset_sql,
1900: creation_date,
1910: fnd_global.login_id);
1911:
1912: /* Process the batch and determine the candidate parties for the purge..
1913: */
1914: hr_utility.set_location(l_proc,50);
1915: hz_purge.identify_purge_parties(l_errbuf, l_retcode,
1916: to_char(l_batchid), 'N');
1917:
1918: if l_retcode = 2 then
1931: ** the purge of those parties.
1932: */
1933: l_errbuf := null;
1934: l_retcode := null;
1935: hr_utility.set_location(l_proc,60);
1936: hz_purge.purge_parties(l_errbuf, l_retcode, to_char(l_batchid), 'N');
1937:
1938: if l_retcode = 2 then
1939: /*
1951: /* Clear the temp table.
1952: */
1953: clear_purge_parties_temp;
1954:
1955: hr_utility.set_location('Leaving : '||l_proc,100);
1956: end purge_parties;
1957: --
1958: --
1959: -- ------------------------------------------------------------------------------
2440: --
2441: -- This routine will create a person in TCA. It calls the TCA API passing
2442: -- in the correct column values based on the legislation of the HRMS person.
2443: --
2444: hr_utility.set_location('Entering '||l_proc,10);
2445: --
2446: -- Begin New code added for PARTY UNMERGE
2447: --
2448: -- function get_tca_merge_actions returns four possible values
2465: --
2466: -- if the function returns PARTY_VALID
2467: -- Regular processing continues normally
2468: --
2469: hr_utility.set_location('Party unmerge '||l_proc,10);
2470: --
2471: --Added for bug 2648797
2472: l_person_rec.created_by_module := 'HR API';
2473: --
2475: (p_person_id => p_rec.person_id
2476: ,p_party_id => p_rec.party_id
2477: );
2478: --
2479: hr_utility.set_location('ACTION = :'||l_tca_merge_action,11);
2480: --
2481: if l_tca_merge_action = 'AVOID CREATE PARTY' then
2482: --
2483: -- This person is not eligible to be migrated. Do nothing and return
2523:
2524: if csr_get_release_name%found and
2525: l_release_name not in ('11.5.1','11.5.2','11.5.3','11.5.4',
2526: '11.5.5') then
2527: hr_utility.set_location(l_proc,12);
2528: --
2529: -- We've found the release details and are at 11.5.6 or above
2530: -- we have all the TCA infrastructure therefore we can continue
2531: -- with the purge.
2545: p_rec.party_id := null;
2546: return;
2547: --
2548: end if;
2549: hr_utility.set_location('END Party unmerge '||l_proc,20);
2550: --
2551: --
2552: -- End New code added for PARTY UNMERGE
2553: --
2572: -- Get the length of person_previous_last_name and assign it to
2573: -- per_hrtca_merge.g_prev_last_name, if not assigned already
2574: --
2575: -- Modiifed to fix 4201545 starts here
2576: hr_utility.set_location('Before prev_last_name loop ',99);
2577: per_hrtca_merge.g_prev_last_name_len := get_column_length('HZ_PERSON_PROFILES','PERSON_PREVIOUS_LAST_NAME',222); --for bug 6331673
2578: --
2579: if per_hrtca_merge.g_prev_last_name_len is null then
2580: begin
2578: --
2579: if per_hrtca_merge.g_prev_last_name_len is null then
2580: begin
2581: --
2582: hr_utility.set_location('Before prev_last_name loop ',99);
2583: loop
2584: l_prev_last_name := l_prev_last_name||'x';
2585: l_prev_last_name_len := l_prev_last_name_len + 1;
2586: end loop;
2598: end;
2599: end if;
2600: -- Modiifed to fix 4201545 ends here
2601: l_prev_last_name_len := per_hrtca_merge.g_prev_last_name_len;
2602: hr_utility.set_location('Before prev_last_name '||l_prev_last_name_len,99);
2603: -- End WWBUG 2735866
2604: --
2605: -- Assign variables to TCA structure based on legislation
2606: --
2752: --
2753: -- Bug 4149356 Start of Fix
2754: -- Write only the first 80 characters of first_name to the trace file
2755: --
2756: hr_utility.set_location(substr(l_person_rec.person_first_name,1,70),10);
2757: --
2758: -- Bug 4149356 End of Fix
2759: --Modified Created by Module Code to 'HR API'
2760: fnd_profile.put('HZ_CREATED_BY_MODULE','HR API');
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;
2834: --
2835: -- We've now got a party_id, this may be for a new person but
2847: ,p_email_address=>p_rec.email_address);
2848: --
2849: end if;
2850: --
2851: hr_utility.set_location('Leaving '||l_proc,10);
2852: --
2853: end;
2854: --
2855: --
2901: and language = userenv('LANG');
2902: --
2903: begin
2904: --
2905: hr_utility.set_location('Entering '||l_proc,10);
2906: --
2907: --
2908: -- Begin New code added for PARTY UNMERGE
2909: --
2947: -- Get the length of person_previous_last_name and assign it to
2948: -- per_hrtca_merge.g_prev_last_name, if not assigned already
2949: --
2950: -- Modiifed to fix 4201545 starts here
2951: hr_utility.set_location('Before prev_last_name loop ',99);
2952: per_hrtca_merge.g_prev_last_name_len := get_column_length('HZ_PERSON_PROFILES','PERSON_PREVIOUS_LAST_NAME',222); --for bug 6331673
2953: --
2954: if per_hrtca_merge.g_prev_last_name_len is null then
2955: begin
2953: --
2954: if per_hrtca_merge.g_prev_last_name_len is null then
2955: begin
2956: --
2957: hr_utility.set_location('Before prev_last_name loop ',99);
2958: loop
2959: l_prev_last_name := l_prev_last_name||'x';
2960: l_prev_last_name_len := l_prev_last_name_len + 1;
2961: end loop;
2974: --
2975: end if;
2976: -- Modiifed to fix 4201545 ends here
2977: l_prev_last_name_len := per_hrtca_merge.g_prev_last_name_len;
2978: hr_utility.set_location('Before prev_last_name '||l_prev_last_name_len,99);
2979: -- End WWBUG 2735866
2980: --
2981: -- Assign variables to TCA structure based on legislation
2982: --
3023: end if; */
3024: --
3025: -- Assign all other variables
3026: --
3027: hr_utility.set_location('UPDATE_TCA_PERSON Before middle Name ###'||p_rec.middle_names||'***',99);
3028: l_person_rec.person_middle_name := nvl(p_rec.middle_names,FND_API.G_MISS_CHAR); -- for bug 6609549.
3029: l_person_rec.person_name_suffix := nvl(p_rec.suffix,FND_API.G_MISS_CHAR);--fix for bug7411512.
3030: --l_person_rec.previous_last_name := substr(p_rec.previous_last_name,1,40);
3031: -- This column length is changed to 150 in one of the latest HZ FP.
3181: );
3182: --
3183: if l_return_status in ('E','U') then
3184: --
3185: hr_utility.set_location(substr(l_msg_data,1,80),10);
3186: fnd_message.set_token('POO',p_rec.party_id);
3187: fnd_message.raise_error;
3188: --
3189: end if;
3245: --
3246: create_update_contact_point(p_party_id => p_rec.party_id,
3247: p_email_address=>p_rec.email_address);
3248: --
3249: hr_utility.set_location('Leaving '||l_proc,10);
3250: --
3251: exception
3252: --
3253: when others then
3316: l_order_name per_all_people_f.order_name%TYPE;
3317: --
3318: begin
3319: --
3320: hr_utility.set_location('Entering '||l_proc,10);
3321: --
3322: -- Bug fix 3598173. NVL added to if condition.
3323:
3324: if nvl(fnd_profile.value('HR_PROPAGATE_DATA_CHANGES'),'N') <> 'Y' then
3456: --
3457: -- It works fine through API's but not when called from the forms
3458: -- interface. Correct code will be left to make transistion easier.
3459: --
3460: hr_utility.set_location('Locking Record',10);
3461: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3462: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3463: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3464: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3457: -- It works fine through API's but not when called from the forms
3458: -- interface. Correct code will be left to make transistion easier.
3459: --
3460: hr_utility.set_location('Locking Record',10);
3461: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3462: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3463: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3464: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3465: --
3458: -- interface. Correct code will be left to make transistion easier.
3459: --
3460: hr_utility.set_location('Locking Record',10);
3461: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3462: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3463: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3464: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3465: --
3466: -- We have to lock ourselves as the row handler locks per_people_f
3459: --
3460: hr_utility.set_location('Locking Record',10);
3461: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3462: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3463: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3464: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3465: --
3466: -- We have to lock ourselves as the row handler locks per_people_f
3467: -- which could mean that we can't lock the other records due to
3460: hr_utility.set_location('Locking Record',10);
3461: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3462: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3463: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3464: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3465: --
3466: -- We have to lock ourselves as the row handler locks per_people_f
3467: -- which could mean that we can't lock the other records due to
3468: -- security issues. Additionally we have to use a loop to go through
3494: */
3495: --
3496: if l_datetrack_mode = 'CORRECTION' then
3497: --
3498: hr_utility.set_location('Updating Record in Correction mode',10);
3499: --
3500: -- Set the values of the columns.
3501: --
3502: l_copy_rec.date_of_birth :=
3780: and effective_end_date;
3781: --
3782: elsif l_datetrack_mode = 'UPDATE' then
3783: --
3784: hr_utility.set_location('Updating Record in update mode',10);
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: --
3792: and l_ref_effective_start_date
3793: between effective_start_date
3794: and effective_end_date;
3795: --
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',
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);
3805: --
3806: l_copy_rec.date_of_birth :=
3807: propagate_value
3808: (l_person(l_count).date_of_birth,
4046: p_global_name => l_global_name,
4047: p_local_name => l_local_name,
4048: p_duplicate_flag => l_duplicate_flag);
4049:
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
4047: p_local_name => l_local_name,
4048: p_duplicate_flag => l_duplicate_flag);
4049:
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,
4048: p_duplicate_flag => l_duplicate_flag);
4049:
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,
4362: end if;
4363: --
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
4373: and effective_end_date;
4374: --
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',
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);
4384: --
4385: l_copy_rec.date_of_birth :=
4386: propagate_value
4387: (l_person(l_count).date_of_birth,
4984: l_person(l_count).effective_end_date > p_rec.effective_end_date or
4985: l_person(l_count).effective_start_date < p_rec.effective_start_date and
4986: l_person(l_count).effective_end_date > p_rec.effective_end_date then
4987: --
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',
5016: -- Now call the API with the appropriate calling mode.
5017: --
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
5027: and effective_end_date;
5028: --
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',
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);
5038: --
5039: -- Now the insert
5040: --
5041: insert into per_all_people_f
5345: and effective_end_date;
5346: --
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
5356: and effective_end_date;
5357: --
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',
5364: p_base_key_value => l_person(l_count).person_id);
5365: --
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,
5725: l_last_bg_id := l_person(l_count).business_group_id;
5726: --
5727: end loop;
5728: --
5729: hr_utility.set_location('Leaving '||l_proc,10);
5730: --
5731: end;
5732: --
5733: --
5764: l_orig_hire_warning boolean;
5765: --
5766: begin
5767: --
5768: hr_utility.set_location('Entering '||l_proc,10);
5769: --
5770: p_return_status := FND_API.G_RET_STS_SUCCESS;
5771: --
5772: -- This routine must select all the person record information and
5823: close c_person;
5824: --
5825: g_count := 0;
5826: --
5827: hr_utility.set_location('Leaving '||l_proc,10);
5828: --
5829: exception
5830: when others then
5831: p_return_status := 'F';
5929: l_data_migrator_mode varchar2(30);
5930: --
5931: begin
5932: --
5933: hr_utility.set_location('Entering '||l_proc,10);
5934: --
5935: -- This routine will create contact point records for all person
5936: -- records in HRMS which have an email address.
5937: --
5966: -- Get the last set of records in the chunk.
5967: --
5968: commit;
5969: --
5970: hr_utility.set_location('Entering '||l_proc,10);
5971: --
5972: end;
5973: --
5974: --
5996: l_count number := 0;
5997: --
5998: begin
5999: --
6000: hr_utility.set_location('Entering '||l_proc,10);
6001: --
6002: open c_person;
6003: --
6004: loop
6028: close c_person;
6029: --
6030: commit;
6031: --
6032: hr_utility.set_location('Leaving '||l_proc,10);
6033: --
6034: end migrate_all_hr_gender;
6035: --
6036: function get_person_details
6055: --
6056: begin
6057: --
6058:
6059: hr_utility.set_location('Entering '||l_proc,10);
6060:
6061: if nvl(fnd_profile.value('HR_PROPAGATE_DATA_CHANGES'),'N') <> 'Y' then
6062: --
6063: return l_rec;
6065: end if;
6066: --
6067: -- Just get the first record regardless.
6068: --
6069: hr_utility.set_location(l_proc,20);
6070: --
6071: open c1;
6072:
6073: fetch c1 into l_c1;
6106: end if;
6107: --
6108: close c1;
6109: --
6110: hr_utility.set_location('Leaving '||l_proc,30);
6111: --
6112: return l_rec;
6113: --
6114: end get_person_details;
6306: --
6307:
6308: begin
6309:
6310: hr_utility.set_location('Entering '||l_proc,10);
6311:
6312: l_data_migrator_mode := hr_general.g_data_migrator_mode;
6313: hr_general.g_data_migrator_mode := 'Y';
6314:
6357:
6358: --
6359: hr_general.g_data_migrator_mode := l_data_migrator_mode;
6360: --
6361: hr_utility.set_location('Leaving '||l_proc,20);
6362: --
6363: end migrate_all_hr_email;
6364: --
6365: -- Bug fix 5395601 starts here --
6369: --
6370: procedure purge_person (p_person_id number,p_party_id number ) is
6371: begin
6372:
6373: hr_utility.set_location('purge_person ', 12);
6374: --fix for bug 6620368 starts here.
6375: -- Call to purge_parties is commented to improve performance.
6376: -- Party id is inserted into table HR_TCA_PARTY_UNMERGE
6377: -- so that party id will be purged when the user run the
6380: INSERT INTO hr_tca_party_unmerge (party_id,status)
6381: VALUES (p_party_id,'PURGE');
6382:
6383:
6384: /* hr_utility.set_location('before calling add_party_for_purge ', 12);
6385: per_hrtca_merge.add_party_for_purge (p_party_id => p_party_id);
6386: hr_utility.set_location('before calling purge_parties ', 13);
6387: per_hrtca_merge.purge_parties;
6388: hr_utility.set_location('After call ', 14);*/
6382:
6383:
6384: /* hr_utility.set_location('before calling add_party_for_purge ', 12);
6385: per_hrtca_merge.add_party_for_purge (p_party_id => p_party_id);
6386: hr_utility.set_location('before calling purge_parties ', 13);
6387: per_hrtca_merge.purge_parties;
6388: hr_utility.set_location('After call ', 14);*/
6389:
6390: hr_utility.set_location('purge_person ', 13);
6384: /* hr_utility.set_location('before calling add_party_for_purge ', 12);
6385: per_hrtca_merge.add_party_for_purge (p_party_id => p_party_id);
6386: hr_utility.set_location('before calling purge_parties ', 13);
6387: per_hrtca_merge.purge_parties;
6388: hr_utility.set_location('After call ', 14);*/
6389:
6390: hr_utility.set_location('purge_person ', 13);
6391: --fix for bug 6620368 ends here.
6392: exception
6386: hr_utility.set_location('before calling purge_parties ', 13);
6387: per_hrtca_merge.purge_parties;
6388: hr_utility.set_location('After call ', 14);*/
6389:
6390: hr_utility.set_location('purge_person ', 13);
6391: --fix for bug 6620368 ends here.
6392: exception
6393: when others then
6394: ROLLBACK TO hr_delete_person;