[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: procedure create_update_contact_point
645: l_email_changed boolean:=false;
646: --bug no 5546586 ends here
647: BEGIN
648: --
649: hr_utility.set_location('Entering '||l_proc,10);
650: --
651: --Added for bug 2648797
652: l_contact_point_rec.created_by_module := 'HR API';
653: --
670: OPEN c1;
671: FETCH c1 INTO l_c1;
672: IF c1%found THEN
673: --
674: hr_utility.set_location(l_proc,20);
675: --
676: --Added for bug 2648797
677: --Get the object version number
678: select max(object_version_number)
685: --bug no 5546586 ends here
686: IF (l_c1.email_address is null or l_c1.email_address = 'NULL')
687: and (p_rec.email_address is not null) THEN
688: --
689: hr_utility.set_location('Entering '||l_proc,30);
690: --
691: l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
692: l_email_rec.email_address := p_rec.email_address;
693: --
714: p_init_msg_list => l_init_msg_list,
715: p_object_version_number => l_object_version_number
716: );
717: --
718: hr_utility.set_location(l_proc,40);
719: --
720: if l_return_status in ('E','U') then
721: --
722: -- bug 4632157 Starts
727: end loop;
728: end if;
729: -- bug 4632157 ends
730: --
731: hr_utility.set_location(l_msg_data,10);
732: fnd_message.raise_error;
733:
734: end if;
735:
736:
737: ELSIF (l_c1.email_address is not null and p_rec.email_address is not null)
738: and (l_c1.email_address <> p_rec.email_address) THEN
739: --
740: hr_utility.set_location(l_proc,50);
741: --
742: l_contact_point_rec.contact_point_type := 'EMAIL';
743: l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
744: l_contact_point_rec.owner_table_id := p_rec.party_id;
767: p_init_msg_list => l_init_msg_list,
768: x_contact_point_id => l_contact_point_id
769: );
770: --
771: hr_utility.set_location(l_proc,60);
772: --
773: if l_return_status in ('E','U') then
774: --
775:
772: --
773: if l_return_status in ('E','U') then
774: --
775:
776: hr_utility.set_location(l_msg_count,10);
777: -- bug 4632157 Starts
778: if l_msg_count > 1 then
779: for i in 1..l_msg_count
780: loop
781: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
782: end loop;
783: end if;
784: -- bug 4632157 ends
785: hr_utility.set_location(l_msg_data,10);
786: fnd_message.raise_error;
787: --
788: end if;
789: --
787: --
788: end if;
789: --
790:
791: hr_utility.set_location(l_proc,70);
792: --
793: l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
794: l_contact_point_rec.status := 'I';
795: l_contact_point_rec.primary_flag := 'N';
828: p_init_msg_list => l_init_msg_list,
829: p_object_version_number => l_object_version_number
830: );
831: --
832: hr_utility.set_location(l_proc,80);
833: --
834:
835: if l_return_status in ('E','U') then
836: --
842: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
843: end loop;
844: end if;
845: -- bug 4632157 ends
846: hr_utility.set_location(l_msg_data,10);
847: fnd_message.raise_error;
848: --
849: end if;
850: -- added <> 'NULL' condition below for bug 4694355
850: -- added <> 'NULL' condition below for bug 4694355
851: ELSIF ( l_c1.email_address is not null and l_c1.email_address <> 'NULL' )
852: and (p_rec.email_address is null) then
853: --
854: hr_utility.set_location(l_proc,90);
855: --
856:
857: l_contact_point_rec.contact_point_id := l_c1.contact_point_id;
858: l_contact_point_rec.status := 'I';
879: p_init_msg_list => l_init_msg_list,
880: p_object_version_number => l_object_version_number
881: );
882: --
883: hr_utility.set_location(l_proc,100);
884: --
885: if l_return_status in ('E','U') then
886: --
887:
892: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
893: end loop;
894: end if;
895: -- bug 4632157 ends
896: hr_utility.set_location(l_msg_data,10);
897: fnd_message.raise_error;
898: --
899: end if;
900:
903: end if;
904: --bug no 5546586 ends here
905: ELSE
906: --
907: hr_utility.set_location(l_proc,110);
908: --
909: IF (p_rec.email_address is not null) THEN
910: --
911: hr_utility.set_location(l_proc,120);
907: hr_utility.set_location(l_proc,110);
908: --
909: IF (p_rec.email_address is not null) THEN
910: --
911: hr_utility.set_location(l_proc,120);
912: --
913: l_contact_point_rec.contact_point_type := 'EMAIL';
914: l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
915: l_contact_point_rec.owner_table_id := p_rec.party_id;
939: p_init_msg_list => l_init_msg_list,
940: x_contact_point_id => l_contact_point_id
941: );
942: --
943: hr_utility.set_location(l_proc,130);
944: --
945: if l_return_status in ('E','U') then
946: --
947:
944: --
945: if l_return_status in ('E','U') then
946: --
947:
948: hr_utility.set_location(l_msg_count,10);
949: -- bug 4632157 Starts
950: if l_msg_count > 1 then
951: for i in 1..l_msg_count
952: loop
953: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
954: end loop;
955: end if;
956: -- bug 4632157 ends
957: hr_utility.set_location(l_msg_data,10);
958: fnd_message.raise_error;
959: --
960: end if;
961:
962: END IF;
963: END IF;
964:
965: --
966: hr_utility.set_location('Leaving '||l_proc,140);
967: --
968:
969: end create_update_contact_point;
970: --
1003: l_separator varchar2(1) :='_';
1004: l_proc varchar2(80) := g_package||'.get_system_person_type';
1005: BEGIN
1006: --
1007: hr_utility.set_location('Entering '||l_proc,10);
1008: --
1009: FOR l_person_type IN csr_person_types
1010: (p_effective_date => p_effective_date
1011: ,p_person_id => p_person_id
1020: || l_person_type.system_person_type;
1021: END IF;
1022: END LOOP;
1023: --
1024: hr_utility.set_location('Leaving '||l_proc,10);
1025: --
1026: RETURN l_system_person_type;
1027: --
1028: END get_system_person_type;
1109:
1110: --
1111: Begin
1112: --
1113: hr_utility.set_location('Entering '||l_proc,10);
1114: --
1115: l_system_person_type := per_hrtca_merge.get_system_person_type(
1116: l_effective_date,
1117: p_person_id);
1132: --
1133: for person_type in person_type_person_cur(l_effective_date,p_person_id)
1134: loop
1135: --
1136: hr_utility.set_location('Person Type '||person_type.system_person_type,20);
1137: --
1138: --
1139: -- 4120469 Added the Ex-Emp/Ex-Cwk/Ex-Apl
1140: --
1171: -- with a valid person_type exists.
1172: --
1173: for person_type in person_type_party_cur(l_effective_date,p_party_id) loop
1174: --
1175: hr_utility.set_location('Person Type '||person_type.system_person_type,30);
1176: --
1177: --
1178: -- 4120469 Added the Ex-Emp/Ex-Cwk/Ex-Apl
1179: --
1213: l_return_value := 'CREATE PARTY';
1214: end if;
1215: --
1216: -- End of fix for IRC Bug 3202002
1217: hr_utility.set_location('Leaving '||l_proc,10);
1218: --
1219: return (l_return_value);
1220: --
1221: end get_tca_merge_actions;
1236: l_proc varchar2(80) := g_package||'clear_party_from_hrms';
1237:
1238: begin
1239:
1240: hr_utility.set_location('Entering : '||l_proc,10);
1241: /*
1242: ** Clear party_id information from all records relating to the current
1243: ** party_id across all business groups.
1244: **
1331: when others then
1332: raise;
1333: end;
1334:
1335: hr_utility.set_location('Leaving : '||l_proc, 100);
1336:
1337: end clear_party_from_hrms;
1338: --
1339: -- ------------------------------------------------------------------------------
1423: hr_general.g_data_migrator_mode := 'Y';
1424: --
1425: --
1426: /*
1427: hr_utility.set_location('Entering : '||l_proc,10);
1428: ** Clear party_id information from all records relating to the current
1429: ** party_id across all business groups.
1430: **
1431: ** For performance reasons just use direct SQL and not the APIs
1430: **
1431: ** For performance reasons just use direct SQL and not the APIs
1432: */
1433:
1434: hr_utility.set_location('Entering '||l_proc,10);
1435: --
1436: -- This package repeatedly calls the child table related API's for a
1437: -- particular party and updates the party_id column to null for the
1438: -- party_id that has been passed in.
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);
1452: --
1453: open c_competences;
1454: --
1455: loop
1473: close c_competences;
1474: --
1475: -- Loop through all of a persons event records
1476: --
1477: hr_utility.set_location('FIRST ATTACK : OPEN EVENTS',10);
1478: open c_events;
1479: --
1480: loop
1481: --
1496: --
1497: end loop;
1498: --
1499: close c_events;
1500: --hr_utility.set_location('FIRST ATTACK : CLOSE EVENTS',10);
1501: --
1502: -- Loop through all of a persons address records
1503: --
1504: hr_utility.set_location('FIRST ATTACK : OPEN ADDRESSES',10);
1500: --hr_utility.set_location('FIRST ATTACK : CLOSE EVENTS',10);
1501: --
1502: -- Loop through all of a persons address records
1503: --
1504: hr_utility.set_location('FIRST ATTACK : OPEN ADDRESSES',10);
1505: open c_addresses;
1506: --
1507: loop
1508: --
1523: --
1524: end loop;
1525: --
1526: close c_addresses;
1527: --hr_utility.set_location('FIRST ATTACK : CLOSE ADDRESSES',10);
1528: --
1529: -- Loop through all of a persons phone records
1530: --
1531: hr_utility.set_location('FIRST ATTACK : OPEN PHONES',10);
1527: --hr_utility.set_location('FIRST ATTACK : CLOSE ADDRESSES',10);
1528: --
1529: -- Loop through all of a persons phone records
1530: --
1531: hr_utility.set_location('FIRST ATTACK : OPEN PHONES',10);
1532: open c_phones;
1533: --
1534: loop
1535: --
1550: --
1551: end loop;
1552: --
1553: close c_phones;
1554: --hr_utility.set_location('FIRST ATTACK : CLOSE PHONES',10);
1555: --
1556: -- Loop through all of a persons qualification records
1557: --
1558: open c_qualifications;
1655: end loop;
1656: --
1657: close c_people;
1658: --
1659: hr_utility.set_location('Leaving '||l_proc,10);
1660: --
1661: --
1662: hr_general.g_data_migrator_mode := l_data_migrator_mode;
1663: --
1685: -- Bug fix 3632535 ends here
1686:
1687: begin
1688:
1689: hr_utility.set_location('Entering : '||l_proc,10);
1690: /*
1691: ** Clear party_id information from all records relating to the current
1692: ** party_id across all business groups.
1693: **
1801: when others then
1802: raise;
1803: end;
1804:
1805: hr_utility.set_location('Leaving : '||l_proc, 100);
1806:
1807: end set_party_in_hrms;
1808:
1809: --
1816: procedure clear_purge_parties_temp
1817: is
1818: l_proc varchar2(100) := g_package||'.clear_purge_parties_temp';
1819: begin
1820: hr_utility.set_location('Entering :'||l_proc,10);
1821: delete from hr_purge_parties_gt;
1822: hr_utility.set_location('Leaving :'||l_proc,20);
1823: end;
1824:
1818: l_proc varchar2(100) := g_package||'.clear_purge_parties_temp';
1819: begin
1820: hr_utility.set_location('Entering :'||l_proc,10);
1821: delete from hr_purge_parties_gt;
1822: hr_utility.set_location('Leaving :'||l_proc,20);
1823: end;
1824:
1825: --
1826: -- ------------------------------------------------------------------------------
1834: procedure add_party_for_purge(p_party_id number)
1835: is
1836: l_proc varchar2(100) := g_package||'add_party_for_purge';
1837: begin
1838: hr_utility.set_location('Entering : '||l_proc,10);
1839:
1840: begin
1841: insert into hr_purge_parties_gt (party_id) values (p_party_id);
1842: exception
1843: when others then
1844: raise;
1845: end;
1846:
1847: hr_utility.set_location('Leaving : '||l_proc,20);
1848: end add_party_for_purge;
1849:
1850: --
1851: -- ------------------------------------------------------------------------------
1880: l_msg_count number;
1881: l_msg_data varchar2(1000);
1882: begin
1883:
1884: hr_utility.set_location('Entering : '||l_proc,10);
1885:
1886: /* Insert the details into HZ_PURGE_BATCHES.
1887: */
1888: hr_utility.set_location(l_proc,30);
1884: hr_utility.set_location('Entering : '||l_proc,10);
1885:
1886: /* Insert the details into HZ_PURGE_BATCHES.
1887: */
1888: hr_utility.set_location(l_proc,30);
1889: select hz_purge_batches_s.nextval
1890: into l_batchid
1891: from sys.dual;
1892:
1889: select hz_purge_batches_s.nextval
1890: into l_batchid
1891: from sys.dual;
1892:
1893: hr_utility.set_location(l_proc,40);
1894: insert into hz_purge_batches (batch_id,
1895: batch_name,
1896: subset_sql,
1897: creation_date,
1907: fnd_global.login_id);
1908:
1909: /* Process the batch and determine the candidate parties for the purge..
1910: */
1911: hr_utility.set_location(l_proc,50);
1912: hz_purge.identify_purge_parties(l_errbuf, l_retcode,
1913: to_char(l_batchid), 'N');
1914:
1915: if l_retcode = 2 then
1928: ** the purge of those parties.
1929: */
1930: l_errbuf := null;
1931: l_retcode := null;
1932: hr_utility.set_location(l_proc,60);
1933: hz_purge.purge_parties(l_errbuf, l_retcode, to_char(l_batchid), 'N');
1934:
1935: if l_retcode = 2 then
1936: /*
1948: /* Clear the temp table.
1949: */
1950: clear_purge_parties_temp;
1951:
1952: hr_utility.set_location('Leaving : '||l_proc,100);
1953: end purge_parties;
1954: --
1955: --
1956: -- ------------------------------------------------------------------------------
2437: --
2438: -- This routine will create a person in TCA. It calls the TCA API passing
2439: -- in the correct column values based on the legislation of the HRMS person.
2440: --
2441: hr_utility.set_location('Entering '||l_proc,10);
2442: --
2443: -- Begin New code added for PARTY UNMERGE
2444: --
2445: -- function get_tca_merge_actions returns four possible values
2462: --
2463: -- if the function returns PARTY_VALID
2464: -- Regular processing continues normally
2465: --
2466: hr_utility.set_location('Party unmerge '||l_proc,10);
2467: --
2468: --Added for bug 2648797
2469: l_person_rec.created_by_module := 'HR API';
2470: --
2472: (p_person_id => p_rec.person_id
2473: ,p_party_id => p_rec.party_id
2474: );
2475: --
2476: hr_utility.set_location('ACTION = :'||l_tca_merge_action,11);
2477: --
2478: if l_tca_merge_action = 'AVOID CREATE PARTY' then
2479: --
2480: -- This person is not eligible to be migrated. Do nothing and return
2520:
2521: if csr_get_release_name%found and
2522: l_release_name not in ('11.5.1','11.5.2','11.5.3','11.5.4',
2523: '11.5.5') then
2524: hr_utility.set_location(l_proc,12);
2525: --
2526: -- We've found the release details and are at 11.5.6 or above
2527: -- we have all the TCA infrastructure therefore we can continue
2528: -- with the purge.
2542: p_rec.party_id := null;
2543: return;
2544: --
2545: end if;
2546: hr_utility.set_location('END Party unmerge '||l_proc,20);
2547: --
2548: --
2549: -- End New code added for PARTY UNMERGE
2550: --
2569: -- Get the length of person_previous_last_name and assign it to
2570: -- per_hrtca_merge.g_prev_last_name, if not assigned already
2571: --
2572: -- Modiifed to fix 4201545 starts here
2573: hr_utility.set_location('Before prev_last_name loop ',99);
2574: per_hrtca_merge.g_prev_last_name_len := get_column_length('HZ_PERSON_PROFILES','PERSON_PREVIOUS_LAST_NAME',222); --for bug 6331673
2575: --
2576: if per_hrtca_merge.g_prev_last_name_len is null then
2577: begin
2575: --
2576: if per_hrtca_merge.g_prev_last_name_len is null then
2577: begin
2578: --
2579: hr_utility.set_location('Before prev_last_name loop ',99);
2580: loop
2581: l_prev_last_name := l_prev_last_name||'x';
2582: l_prev_last_name_len := l_prev_last_name_len + 1;
2583: end loop;
2595: end;
2596: end if;
2597: -- Modiifed to fix 4201545 ends here
2598: l_prev_last_name_len := per_hrtca_merge.g_prev_last_name_len;
2599: hr_utility.set_location('Before prev_last_name '||l_prev_last_name_len,99);
2600: -- End WWBUG 2735866
2601: --
2602: -- Assign variables to TCA structure based on legislation
2603: --
2747: --
2748: -- Bug 4149356 Start of Fix
2749: -- Write only the first 80 characters of first_name to the trace file
2750: --
2751: hr_utility.set_location(substr(l_person_rec.person_first_name,1,70),10);
2752: --
2753: -- Bug 4149356 End of Fix
2754: --Modified Created by Module Code to 'HR API'
2755: fnd_profile.put('HZ_CREATED_BY_MODULE','HR API');
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;
2829: --
2830: -- We've now got a party_id, this may be for a new person but
2841: create_update_contact_point(p_rec => p_rec);
2842: --
2843: end if;
2844: --
2845: hr_utility.set_location('Leaving '||l_proc,10);
2846: --
2847: end;
2848: --
2849: --
2895: and language = userenv('LANG');
2896: --
2897: begin
2898: --
2899: hr_utility.set_location('Entering '||l_proc,10);
2900: --
2901: --
2902: -- Begin New code added for PARTY UNMERGE
2903: --
2941: -- Get the length of person_previous_last_name and assign it to
2942: -- per_hrtca_merge.g_prev_last_name, if not assigned already
2943: --
2944: -- Modiifed to fix 4201545 starts here
2945: hr_utility.set_location('Before prev_last_name loop ',99);
2946: per_hrtca_merge.g_prev_last_name_len := get_column_length('HZ_PERSON_PROFILES','PERSON_PREVIOUS_LAST_NAME',222); --for bug 6331673
2947: --
2948: if per_hrtca_merge.g_prev_last_name_len is null then
2949: begin
2947: --
2948: if per_hrtca_merge.g_prev_last_name_len is null then
2949: begin
2950: --
2951: hr_utility.set_location('Before prev_last_name loop ',99);
2952: loop
2953: l_prev_last_name := l_prev_last_name||'x';
2954: l_prev_last_name_len := l_prev_last_name_len + 1;
2955: end loop;
2968: --
2969: end if;
2970: -- Modiifed to fix 4201545 ends here
2971: l_prev_last_name_len := per_hrtca_merge.g_prev_last_name_len;
2972: hr_utility.set_location('Before prev_last_name '||l_prev_last_name_len,99);
2973: -- End WWBUG 2735866
2974: --
2975: -- Assign variables to TCA structure based on legislation
2976: --
3011: end if; */
3012: --
3013: -- Assign all other variables
3014: --
3015: hr_utility.set_location('UPDATE_TCA_PERSON Before middle Name ###'||p_rec.middle_names||'***',99);
3016: l_person_rec.person_middle_name := nvl(p_rec.middle_names,FND_API.G_MISS_CHAR); -- for bug 6609549.
3017: l_person_rec.person_name_suffix := nvl(p_rec.suffix,FND_API.G_MISS_CHAR);--fix for bug7411512.
3018: --l_person_rec.previous_last_name := substr(p_rec.previous_last_name,1,40);
3019: -- This column length is changed to 150 in one of the latest HZ FP.
3167: );
3168: --
3169: if l_return_status in ('E','U') then
3170: --
3171: hr_utility.set_location(substr(l_msg_data,1,80),10);
3172: fnd_message.set_token('POO',p_rec.party_id);
3173: fnd_message.raise_error;
3174: --
3175: end if;
3230: end if;
3231: --
3232: create_update_contact_point(p_rec => p_rec);
3233: --
3234: hr_utility.set_location('Leaving '||l_proc,10);
3235: --
3236: exception
3237: --
3238: when others then
3301: l_order_name per_all_people_f.order_name%TYPE;
3302: --
3303: begin
3304: --
3305: hr_utility.set_location('Entering '||l_proc,10);
3306: --
3307: -- Bug fix 3598173. NVL added to if condition.
3308:
3309: if nvl(fnd_profile.value('HR_PROPAGATE_DATA_CHANGES'),'N') <> 'Y' then
3441: --
3442: -- It works fine through API's but not when called from the forms
3443: -- interface. Correct code will be left to make transistion easier.
3444: --
3445: hr_utility.set_location('Locking Record',10);
3446: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3447: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3448: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3449: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3442: -- It works fine through API's but not when called from the forms
3443: -- interface. Correct code will be left to make transistion easier.
3444: --
3445: hr_utility.set_location('Locking Record',10);
3446: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3447: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3448: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3449: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3450: --
3443: -- interface. Correct code will be left to make transistion easier.
3444: --
3445: hr_utility.set_location('Locking Record',10);
3446: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3447: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3448: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3449: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3450: --
3451: -- We have to lock ourselves as the row handler locks per_people_f
3444: --
3445: hr_utility.set_location('Locking Record',10);
3446: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3447: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3448: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3449: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3450: --
3451: -- We have to lock ourselves as the row handler locks per_people_f
3452: -- which could mean that we can't lock the other records due to
3445: hr_utility.set_location('Locking Record',10);
3446: hr_utility.set_location('Person_ID '||l_person(l_count).person_id,10);
3447: hr_utility.set_location('Effective Date '||to_char(p_rec.effective_start_date,'DD/MM/YYYY'),10);
3448: hr_utility.set_location('OVN '||l_person(l_count).object_version_number,10);
3449: hr_utility.set_location('DT mode '||l_datetrack_mode,10);
3450: --
3451: -- We have to lock ourselves as the row handler locks per_people_f
3452: -- which could mean that we can't lock the other records due to
3453: -- security issues. Additionally we have to use a loop to go through
3479: */
3480: --
3481: if l_datetrack_mode = 'CORRECTION' then
3482: --
3483: hr_utility.set_location('Updating Record in Correction mode',10);
3484: --
3485: -- Set the values of the columns.
3486: --
3487: l_copy_rec.date_of_birth :=
3765: and effective_end_date;
3766: --
3767: elsif l_datetrack_mode = 'UPDATE' then
3768: --
3769: hr_utility.set_location('Updating Record in update mode',10);
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: --
3777: and l_ref_effective_start_date
3778: between effective_start_date
3779: and effective_end_date;
3780: --
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',
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);
3790: --
3791: l_copy_rec.date_of_birth :=
3792: propagate_value
3793: (l_person(l_count).date_of_birth,
4031: p_global_name => l_global_name,
4032: p_local_name => l_local_name,
4033: p_duplicate_flag => l_duplicate_flag);
4034:
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
4032: p_local_name => l_local_name,
4033: p_duplicate_flag => l_duplicate_flag);
4034:
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,
4033: p_duplicate_flag => l_duplicate_flag);
4034:
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,
4347: end if;
4348: --
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
4358: and effective_end_date;
4359: --
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',
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);
4369: --
4370: l_copy_rec.date_of_birth :=
4371: propagate_value
4372: (l_person(l_count).date_of_birth,
4965: l_person(l_count).effective_end_date > p_rec.effective_end_date or
4966: l_person(l_count).effective_start_date < p_rec.effective_start_date and
4967: l_person(l_count).effective_end_date > p_rec.effective_end_date then
4968: --
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',
4997: -- Now call the API with the appropriate calling mode.
4998: --
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
5008: and effective_end_date;
5009: --
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',
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);
5019: --
5020: -- Now the insert
5021: --
5022: insert into per_all_people_f
5326: and effective_end_date;
5327: --
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
5337: and effective_end_date;
5338: --
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',
5345: p_base_key_value => l_person(l_count).person_id);
5346: --
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,
5706: l_last_bg_id := l_person(l_count).business_group_id;
5707: --
5708: end loop;
5709: --
5710: hr_utility.set_location('Leaving '||l_proc,10);
5711: --
5712: end;
5713: --
5714: --
5745: l_orig_hire_warning boolean;
5746: --
5747: begin
5748: --
5749: hr_utility.set_location('Entering '||l_proc,10);
5750: --
5751: p_return_status := FND_API.G_RET_STS_SUCCESS;
5752: --
5753: -- This routine must select all the person record information and
5804: close c_person;
5805: --
5806: g_count := 0;
5807: --
5808: hr_utility.set_location('Leaving '||l_proc,10);
5809: --
5810: exception
5811: when others then
5812: p_return_status := 'F';
5910: l_data_migrator_mode varchar2(30);
5911: --
5912: begin
5913: --
5914: hr_utility.set_location('Entering '||l_proc,10);
5915: --
5916: -- This routine will create contact point records for all person
5917: -- records in HRMS which have an email address.
5918: --
5946: -- Get the last set of records in the chunk.
5947: --
5948: commit;
5949: --
5950: hr_utility.set_location('Entering '||l_proc,10);
5951: --
5952: end;
5953: --
5954: --
5976: l_count number := 0;
5977: --
5978: begin
5979: --
5980: hr_utility.set_location('Entering '||l_proc,10);
5981: --
5982: open c_person;
5983: --
5984: loop
6008: close c_person;
6009: --
6010: commit;
6011: --
6012: hr_utility.set_location('Leaving '||l_proc,10);
6013: --
6014: end migrate_all_hr_gender;
6015: --
6016: function get_person_details
6035: --
6036: begin
6037: --
6038:
6039: hr_utility.set_location('Entering '||l_proc,10);
6040:
6041: if nvl(fnd_profile.value('HR_PROPAGATE_DATA_CHANGES'),'N') <> 'Y' then
6042: --
6043: return l_rec;
6045: end if;
6046: --
6047: -- Just get the first record regardless.
6048: --
6049: hr_utility.set_location(l_proc,20);
6050: --
6051: open c1;
6052:
6053: fetch c1 into l_c1;
6086: end if;
6087: --
6088: close c1;
6089: --
6090: hr_utility.set_location('Leaving '||l_proc,30);
6091: --
6092: return l_rec;
6093: --
6094: end get_person_details;
6287: --
6288:
6289: begin
6290:
6291: hr_utility.set_location('Entering '||l_proc,10);
6292:
6293: l_data_migrator_mode := hr_general.g_data_migrator_mode;
6294: hr_general.g_data_migrator_mode := 'Y';
6295:
6337:
6338: --
6339: hr_general.g_data_migrator_mode := l_data_migrator_mode;
6340: --
6341: hr_utility.set_location('Leaving '||l_proc,20);
6342: --
6343: end migrate_all_hr_email;
6344: --
6345: -- Bug fix 5395601 starts here --
6349: --
6350: procedure purge_person (p_person_id number,p_party_id number ) is
6351: begin
6352:
6353: hr_utility.set_location('purge_person ', 12);
6354: --fix for bug 6620368 starts here.
6355: -- Call to purge_parties is commented to improve performance.
6356: -- Party id is inserted into table HR_TCA_PARTY_UNMERGE
6357: -- so that party id will be purged when the user run the
6360: INSERT INTO hr_tca_party_unmerge (party_id,status)
6361: VALUES (p_party_id,'PURGE');
6362:
6363:
6364: /* hr_utility.set_location('before calling add_party_for_purge ', 12);
6365: per_hrtca_merge.add_party_for_purge (p_party_id => p_party_id);
6366: hr_utility.set_location('before calling purge_parties ', 13);
6367: per_hrtca_merge.purge_parties;
6368: hr_utility.set_location('After call ', 14);*/
6362:
6363:
6364: /* hr_utility.set_location('before calling add_party_for_purge ', 12);
6365: per_hrtca_merge.add_party_for_purge (p_party_id => p_party_id);
6366: hr_utility.set_location('before calling purge_parties ', 13);
6367: per_hrtca_merge.purge_parties;
6368: hr_utility.set_location('After call ', 14);*/
6369:
6370: hr_utility.set_location('purge_person ', 13);
6364: /* hr_utility.set_location('before calling add_party_for_purge ', 12);
6365: per_hrtca_merge.add_party_for_purge (p_party_id => p_party_id);
6366: hr_utility.set_location('before calling purge_parties ', 13);
6367: per_hrtca_merge.purge_parties;
6368: hr_utility.set_location('After call ', 14);*/
6369:
6370: hr_utility.set_location('purge_person ', 13);
6371: --fix for bug 6620368 ends here.
6372: exception
6366: hr_utility.set_location('before calling purge_parties ', 13);
6367: per_hrtca_merge.purge_parties;
6368: hr_utility.set_location('After call ', 14);*/
6369:
6370: hr_utility.set_location('purge_person ', 13);
6371: --fix for bug 6620368 ends here.
6372: exception
6373: when others then
6374: ROLLBACK TO hr_delete_person;