[Home] [Help]
237: 115.85 30-jul-02 hnarayan bug 1169240 - added the function
238: check_bnf_actn_item_dfnd to check for
239: any action items defined for beneficiaries
240: 115.86 26-DEC-02 kichoudh NOCOPY changes
241: 115.87 13-feb-03 hnarayan hr_utility.set_location - 'if g_debug' changes
242: 115.88 21-Mar-03 mmudigon Bug 2858700. Added dt condition and order
243: by clause to cursor c_dpnt_bnf_adrs.
244: 115.89 24-Mar-03 mmudigon Bug 2858700 continued. Added order by
245: clause to cursor c_prtt_adrs.
351: */
352: --
353: -- Package Variables
354: --
355: g_debug boolean := hr_utility.debug_enabled;
356: --
357: -- -----------------------------------------------------------------------------
358: -- |-----------------------< check_ctfns_defined >-----------------------------|
359: -- -----------------------------------------------------------------------------
483: begin
484: --
485: if g_debug then
486: l_proc := g_package || '.check_ctfns_defined';
487: hr_utility.set_location('Entering ' || l_proc, 10);
488: end if;
489: if g_debug then
490: hr_utility.set_location('Dpnt dsgn level code : ' || p_lvl_cd, 10);
491: end if;
486: l_proc := g_package || '.check_ctfns_defined';
487: hr_utility.set_location('Entering ' || l_proc, 10);
488: end if;
489: if g_debug then
490: hr_utility.set_location('Dpnt dsgn level code : ' || p_lvl_cd, 10);
491: end if;
492: --
493: l_susp_if_ctfn_not_prvd_flag := null;
494: --
566: p_susp_if_ctfn_not_prvd_flag := l_susp_if_ctfn_not_prvd_flag;
567: --
568: if l_return then
569: if g_debug then
570: hr_utility.set_location('CTFNS are defined', 99);
571: end if;
572: else
573: if g_debug then
574: hr_utility.set_location('CTFNS not defined', 99);
570: hr_utility.set_location('CTFNS are defined', 99);
571: end if;
572: else
573: if g_debug then
574: hr_utility.set_location('CTFNS not defined', 99);
575: end if;
576: end if;
577: --
578: if g_debug then
575: end if;
576: end if;
577: --
578: if g_debug then
579: hr_utility.set_location('Leaving ' || l_proc, 99);
580: end if;
581: --
582: return l_return;
583: --
659: --
660: begin
661: --
662: if g_debug then
663: hr_utility.set_location ('Entering '||l_proc,10);
664: end if;
665: --
666: if p_actn_typ_id is null then
667: --
715: --End Bug 4610971
716: end if;
717: --
718: if g_debug then
719: hr_utility.set_location ('Leaving '||l_proc,10);
720: end if;
721: --
722: return l_returned_date;
723: --
725: --
726: when pat_not_found then
727: --
728: if g_debug then
729: hr_utility.set_location('Leaving : ' || l_proc, 10);
730: end if;
731: l_returned_date := NULL;
732: return l_returned_date;
733: --
732: return l_returned_date;
733: --
734: when others then
735: if g_debug then
736: hr_utility.set_location('Exception Raised '||l_proc, 10);
737: end if;
738: raise;
739: --
740: end get_due_date;
763: and bat.business_group_id = p_business_group_id;
764: --
765: begin
766: --
767: g_debug := hr_utility.debug_enabled;
768: if g_debug then
769: l_proc := g_package||'.get_actn_typ_id';
770: hr_utility.set_location ('Entering '||l_proc,10);
771: end if;
766: --
767: g_debug := hr_utility.debug_enabled;
768: if g_debug then
769: l_proc := g_package||'.get_actn_typ_id';
770: hr_utility.set_location ('Entering '||l_proc,10);
771: end if;
772: --
773: open c_actn_typ_id;
774: fetch c_actn_typ_id into l_actn_typ_id;
779: --
780: close c_actn_typ_id;
781: --
782: if g_debug then
783: hr_utility.set_location ('Leaving '||l_proc, 10);
784: end if;
785: --
786: return l_actn_typ_id;
787: --
787: --
788: exception
789: when others then
790: if g_debug then
791: hr_utility.set_location('Exception Raised '||l_proc, 10);
792: end if;
793: raise;
794: --
795: end get_actn_typ_id;
882:
883: --
884: begin
885: --
886: g_debug := hr_utility.debug_enabled;
887: if g_debug then
888: l_proc := g_package||'.get_prtt_enrt_actn_id';
889: hr_utility.set_location ('Entering '||l_proc,10);
890: end if;
885: --
886: g_debug := hr_utility.debug_enabled;
887: if g_debug then
888: l_proc := g_package||'.get_prtt_enrt_actn_id';
889: hr_utility.set_location ('Entering '||l_proc,10);
890: end if;
891: l_object_version_number := p_object_version_number ;
892:
893: hr_utility.set_location ('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id,10);
889: hr_utility.set_location ('Entering '||l_proc,10);
890: end if;
891: l_object_version_number := p_object_version_number ;
892:
893: hr_utility.set_location ('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id,10);
894: hr_utility.set_location ('p_elig_cvrd_dpnt_id '|| p_elig_cvrd_dpnt_id,10);
895: hr_utility.set_location ('p_pl_bnf_id '|| p_pl_bnf_id,10);
896:
897: --
890: end if;
891: l_object_version_number := p_object_version_number ;
892:
893: hr_utility.set_location ('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id,10);
894: hr_utility.set_location ('p_elig_cvrd_dpnt_id '|| p_elig_cvrd_dpnt_id,10);
895: hr_utility.set_location ('p_pl_bnf_id '|| p_pl_bnf_id,10);
896:
897: --
898: if p_prtt_enrt_rslt_id is not null then
891: l_object_version_number := p_object_version_number ;
892:
893: hr_utility.set_location ('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id,10);
894: hr_utility.set_location ('p_elig_cvrd_dpnt_id '|| p_elig_cvrd_dpnt_id,10);
895: hr_utility.set_location ('p_pl_bnf_id '|| p_pl_bnf_id,10);
896:
897: --
898: if p_prtt_enrt_rslt_id is not null then
899: --
916: end if;
917: --
918: close c_enrt_actn;
919: --
920: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,11);
921: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,11);
922:
923:
924:
917: --
918: close c_enrt_actn;
919: --
920: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,11);
921: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,11);
922:
923:
924:
925: elsif p_elig_cvrd_dpnt_id is not null and
940: end if;
941: --
942: close c_enrt_actn_dpnt;
943: --
944: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,12);
945: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,12);
946:
947: elsif p_elig_cvrd_dpnt_id is null and
948: p_pl_bnf_id is not null then
941: --
942: close c_enrt_actn_dpnt;
943: --
944: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,12);
945: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,12);
946:
947: elsif p_elig_cvrd_dpnt_id is null and
948: p_pl_bnf_id is not null then
949: --
962: end if;
963: --
964: close c_enrt_actn_bnf;
965: --
966: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,13);
967: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,13);
968:
969: end if;
970: --
963: --
964: close c_enrt_actn_bnf;
965: --
966: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,13);
967: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,13);
968:
969: end if;
970: --
971: else
976: p_object_version_number := NULL;
977: end if;
978: --
979: if g_debug then
980: hr_utility.set_location ('Leaving '||l_proc, 10);
981: end if;
982: --
983: exception
984: when others then
988: p_cmpltd_dt :=null ;
989: p_object_version_number := l_object_version_number ;
990: --
991: if g_debug then
992: hr_utility.set_location('Exception Raised '||l_proc, 10);
993: end if;
994: raise;
995: --
996: end get_prtt_enrt_actn_id;
1012: begin
1013: --
1014: if g_debug then
1015: l_proc := g_package||'.date_track_verify';
1016: hr_utility.set_location ('Entering '||l_proc,10);
1017: end if;
1018: --
1019: -- Date track settings need some massaging right now.
1020: -- This routine is used to set the rules for mode settings depending
1054: l_datetrack_mode := hr_api.g_correction;
1055: end if;
1056: --
1057: if g_debug then
1058: hr_utility.set_location ('Leaving '||l_proc, 10);
1059: end if;
1060: --
1061: return l_datetrack_mode;
1062: --
1062: --
1063: exception
1064: when others then
1065: if g_debug then
1066: hr_utility.set_location('Exception Raised '||l_proc, 10);
1067: end if;
1068: raise;
1069: --
1070: end date_track_verify;
1123:
1124: --
1125: begin
1126: --
1127: g_debug := hr_utility.debug_enabled;
1128: if g_debug then
1129: l_proc := g_package||'.write_new_action_item';
1130: hr_utility.set_location ('Entering '||l_proc,10);
1131: end if;
1126: --
1127: g_debug := hr_utility.debug_enabled;
1128: if g_debug then
1129: l_proc := g_package||'.write_new_action_item';
1130: hr_utility.set_location ('Entering '||l_proc,10);
1131: end if;
1132: l_rslt_object_version_number := p_rslt_object_version_number ;
1133: --
1134: -- Get the due date for the action item
1178: --
1179: end if;
1180: --
1181: if g_debug then
1182: hr_utility.set_location ('Leaving '||l_proc, 10);
1183: end if;
1184: --
1185: exception
1186: when others then
1191: p_object_version_number :=null;
1192: p_prtt_enrt_actn_id :=null;
1193:
1194: if g_debug then
1195: hr_utility.set_location('Exception Raised '||l_proc, 10);
1196: end if;
1197: raise;
1198: --
1199: end write_new_action_item;
1222: begin
1223: --
1224: if g_debug then
1225: l_proc := g_package||'.write_new_dpnt_ctfn_item';
1226: hr_utility.set_location ('Entering '||l_proc,10);
1227: end if;
1228: --
1229: -- write a new record for a dependent certification item
1230: --
1244: ,p_request_id => fnd_global.conc_request_id
1245: ,p_program_update_date => sysdate);
1246: --
1247: if g_debug then
1248: hr_utility.set_location ('Leaving '||l_proc, 10);
1249: end if;
1250: --
1251: exception
1252: /* we could have duplicates already here from prior run
1256: -- Init Variables for NOCOPY
1257: p_object_version_number :=null ;
1258: p_cvrd_dpnt_ctfn_prvdd_id :=null ;
1259: if g_debug then
1260: hr_utility.set_location('Exception Raised '||l_proc, 10);
1261: end if;
1262: raise;
1263: --
1264: end write_new_dpnt_ctfn_item;
1309: begin
1310: --
1311: if g_debug then
1312: l_proc := g_package||'.write_new_prtt_ctfn_prvdd_item';
1313: hr_utility.set_location ('Entering '||l_proc,10);
1314: end if;
1315: --
1316: -- write a new record for a participant certification item
1317: --
1334: ,p_effective_end_date => l_effective_end_date
1335: ,p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
1336: );
1337: else
1338: hr_utility.set_location ('Already certification exists. No need to create new one', 10);
1339: end if;
1340: --
1341: if g_debug then
1342: hr_utility.set_location ('Leaving '||l_proc, 10);
1338: hr_utility.set_location ('Already certification exists. No need to create new one', 10);
1339: end if;
1340: --
1341: if g_debug then
1342: hr_utility.set_location ('Leaving '||l_proc, 10);
1343: end if;
1344: --
1345: exception
1346: when others then
1349: p_object_version_number := null ;
1350: p_prtt_enrt_ctfn_prvdd_id:= null;
1351:
1352: if g_debug then
1353: hr_utility.set_location('Exception Raised '||l_proc, 10);
1354: end if;
1355: raise;
1356: --
1357: end write_new_prtt_ctfn_prvdd_item;
1456: begin
1457: --
1458: if g_debug then
1459: l_proc := g_package||'.set_cmpltd_dt';
1460: hr_utility.set_location ('Entering '||l_proc,10);
1461: hr_utility.set_location('p_prtt_enrt_actn_id '||p_prtt_enrt_actn_id,119);
1462: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,119);
1463: --
1464: end if;
1457: --
1458: if g_debug then
1459: l_proc := g_package||'.set_cmpltd_dt';
1460: hr_utility.set_location ('Entering '||l_proc,10);
1461: hr_utility.set_location('p_prtt_enrt_actn_id '||p_prtt_enrt_actn_id,119);
1462: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,119);
1463: --
1464: end if;
1465: --
1458: if g_debug then
1459: l_proc := g_package||'.set_cmpltd_dt';
1460: hr_utility.set_location ('Entering '||l_proc,10);
1461: hr_utility.set_location('p_prtt_enrt_actn_id '||p_prtt_enrt_actn_id,119);
1462: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,119);
1463: --
1464: end if;
1465: --
1466:
1488: end if;
1489: --
1490: l_cmpltd_dt := l_prvdd_dt ;
1491: --
1492: hr_utility.set_location('FIRST l_cmpltd_dt '||l_cmpltd_dt,119);
1493: --
1494: open c_ccp(p_prtt_enrt_actn_id);
1495: fetch c_ccp into l_prvdd_dt ;
1496: close c_ccp ;
1498: if l_prvdd_dt > nvl(l_cmpltd_dt,l_prvdd_dt-1) then
1499: l_cmpltd_dt := l_prvdd_dt ;
1500: end if;
1501: --
1502: hr_utility.set_location('SECOND l_cmpltd_dt '||l_cmpltd_dt,119);
1503: --
1504: open c_pbc(p_prtt_enrt_actn_id);
1505: fetch c_pbc into l_prvdd_dt ;
1506: close c_pbc ;
1508: if l_prvdd_dt > nvl(l_cmpltd_dt,l_prvdd_dt-1) then
1509: l_cmpltd_dt := l_prvdd_dt ;
1510: end if;
1511: --
1512: hr_utility.set_location(' p_effective_date '||p_effective_date,119);
1513: hr_utility.set_location(' l_cmpltd_dt '||l_cmpltd_dt,119);
1514: l_cmpltd_dt := nvl(l_cmpltd_dt,p_effective_date) ;
1515: --
1516: end if;
1509: l_cmpltd_dt := l_prvdd_dt ;
1510: end if;
1511: --
1512: hr_utility.set_location(' p_effective_date '||p_effective_date,119);
1513: hr_utility.set_location(' l_cmpltd_dt '||l_cmpltd_dt,119);
1514: l_cmpltd_dt := nvl(l_cmpltd_dt,p_effective_date) ;
1515: --
1516: end if;
1517: --
1551: -- bug 6010780
1552: if l_object_version_number2 is not null then
1553: l_datetrack_mode2 := 'FUTURE_CHANGE';
1554:
1555: hr_utility.set_location(' Future change exists fo update or correction ', 121 );
1556: hr_utility.set_location(' p_object_version_number 1 ' || p_object_version_number , 121 );
1557:
1558: ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
1559: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
1552: if l_object_version_number2 is not null then
1553: l_datetrack_mode2 := 'FUTURE_CHANGE';
1554:
1555: hr_utility.set_location(' Future change exists fo update or correction ', 121 );
1556: hr_utility.set_location(' p_object_version_number 1 ' || p_object_version_number , 121 );
1557:
1558: ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
1559: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
1560: p_business_group_id => p_business_group_id,
1567: p_effective_start_date => l_effective_start_date,
1568: p_effective_end_date => l_effective_end_date
1569: );
1570: end if;
1571: hr_utility.set_location(' p_object_version_number 2 ' || p_object_version_number , 121 );
1572: open c_curr_ovn_of_actn(p_prtt_enrt_actn_id);
1573: fetch c_curr_ovn_of_actn into curr_ovn;
1574: close c_curr_ovn_of_actn;
1575: hr_utility.set_location('curr_ovn ' || curr_ovn , 121 );
1571: hr_utility.set_location(' p_object_version_number 2 ' || p_object_version_number , 121 );
1572: open c_curr_ovn_of_actn(p_prtt_enrt_actn_id);
1573: fetch c_curr_ovn_of_actn into curr_ovn;
1574: close c_curr_ovn_of_actn;
1575: hr_utility.set_location('curr_ovn ' || curr_ovn , 121 );
1576: if curr_ovn is not null then
1577: ben_prtt_enrt_actn_api.update_prtt_enrt_actn
1578: (p_cmpltd_dt => l_cmpltd_dt
1579: ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
1593: end if;
1594: end if;
1595: --
1596: if g_debug then
1597: hr_utility.set_location ('Leaving '||l_proc, 10);
1598: end if;
1599: --
1600: exception
1601: when others then
1600: exception
1601: when others then
1602:
1603: if g_debug then
1604: hr_utility.set_location('Exception Raised '||l_proc, 10);
1605: end if;
1606: -- Init Variables for NOCOPY
1607: p_rslt_object_version_number := l_rslt_object_version_number ;
1608: p_object_version_number := l_object_version_number ;
1648: ;
1649: --
1650: begin
1651: --
1652: g_debug := hr_utility.debug_enabled;
1653: if g_debug then
1654: l_proc := g_package||'.complete_this_action_item';
1655: hr_utility.set_location ('Entering '||l_proc,10);
1656: end if;
1651: --
1652: g_debug := hr_utility.debug_enabled;
1653: if g_debug then
1654: l_proc := g_package||'.complete_this_action_item';
1655: hr_utility.set_location ('Entering '||l_proc,10);
1656: end if;
1657: --
1658: -- Using table api update the record for an action item
1659: --
1673: --
1674: end loop;
1675: --
1676: if g_debug then
1677: hr_utility.set_location ('Leaving '||l_proc, 10);
1678: end if;
1679: --
1680: exception
1681: when others then
1679: --
1680: exception
1681: when others then
1682: if g_debug then
1683: hr_utility.set_location('Exception Raised '||l_proc, 10);
1684: end if;
1685: raise;
1686: --
1687: end complete_this_action_item;
1708: begin
1709: --
1710: if g_debug then
1711: l_proc := g_package||'.delete_prtt_ctfn_prvdd';
1712: hr_utility.set_location ('Entering '||l_proc,10);
1713: end if;
1714: --
1715: l_object_version_number := p_object_version_number ;
1716:
1724: ,p_effective_date => p_effective_date
1725: ,p_datetrack_mode => p_datetrack_mode);
1726: --
1727: if g_debug then
1728: hr_utility.set_location ('Leaving '||l_proc, 10);
1729: end if;
1730: --
1731: exception
1732: when others then
1731: exception
1732: when others then
1733:
1734: if g_debug then
1735: hr_utility.set_location('Exception Raised '||l_proc, 10);
1736: end if;
1737: p_object_version_number := l_object_version_number ;
1738:
1739: raise;
1763: begin
1764: --
1765: if g_debug then
1766: l_proc := g_package||'.delete_dpnt_ctfn_prvdd';
1767: hr_utility.set_location ('Entering '||l_proc,10);
1768: end if;
1769: --
1770: l_object_version_number := p_object_version_number ;
1771:
1781: ,p_datetrack_mode => p_datetrack_mode
1782: ,p_called_from => 'benactcm' );
1783: --
1784: if g_debug then
1785: hr_utility.set_location ('Leaving '||l_proc, 10);
1786: end if;
1787: --
1788: exception
1789: when others then
1787: --
1788: exception
1789: when others then
1790: if g_debug then
1791: hr_utility.set_location('Exception Raised '||l_proc, 10);
1792: end if;
1793: p_object_version_number := l_object_version_number ;
1794: raise;
1795: --
1834: begin
1835: --
1836: if g_debug then
1837: l_proc := g_package||'.remove_prtt_certifications';
1838: hr_utility.set_location ('Entering '||l_proc,10);
1839: end if;
1840: --
1841: -- Using table api write a new record for an action item
1842: --
1850: --
1851: end loop;
1852: --
1853: if g_debug then
1854: hr_utility.set_location ('Leaving '||l_proc, 10);
1855: end if;
1856: --
1857: exception
1858: when others then
1856: --
1857: exception
1858: when others then
1859: if g_debug then
1860: hr_utility.set_location('Exception Raised '||l_proc, 10);
1861: end if;
1862: raise;
1863: --
1864: end remove_prtt_certifications;
1905: begin
1906: --
1907: if g_debug then
1908: l_proc := g_package||'.remove_dpnt_certifications';
1909: hr_utility.set_location ('Entering '||l_proc,10);
1910: end if;
1911: --
1912: for l_dpnt_ctfn_prvdd in c_dpnt_ctfn_prvdd loop
1913: --
1920: --
1921: end loop;
1922: --
1923: if g_debug then
1924: hr_utility.set_location ('Leaving '||l_proc, 10);
1925: end if;
1926: --
1927: exception
1928: when others then
1929: p_effective_start_date := null;
1930: p_effective_end_date := null;
1931:
1932: if g_debug then
1933: hr_utility.set_location('Exception Raised '||l_proc, 10);
1934: end if;
1935: raise;
1936: --
1937: end remove_dpnt_certifications;
1960: begin
1961: --
1962: if g_debug then
1963: l_proc := g_package||'.delete_action_item';
1964: hr_utility.set_location ('Entering '||l_proc,10);
1965: end if;
1966: if g_debug then
1967: hr_utility.set_location(p_datetrack_mode || ' ' || l_proc, 10);
1968: end if;
1963: l_proc := g_package||'.delete_action_item';
1964: hr_utility.set_location ('Entering '||l_proc,10);
1965: end if;
1966: if g_debug then
1967: hr_utility.set_location(p_datetrack_mode || ' ' || l_proc, 10);
1968: end if;
1969: --
1970: l_object_version_number := p_object_version_number;
1971: l_rslt_object_version_number := p_rslt_object_version_number ;
1984: ,p_effective_start_date => l_effective_start_date
1985: ,p_effective_end_date => l_effective_end_date);
1986: --
1987: if g_debug then
1988: hr_utility.set_location ('Leaving '||l_proc, 10);
1989: end if;
1990: --
1991: exception
1992: when others then
1990: --
1991: exception
1992: when others then
1993: if g_debug then
1994: hr_utility.set_location('Exception Raised '||l_proc, 10);
1995: end if;
1996: p_object_version_number := l_object_version_number ;
1997: p_rslt_object_version_number := l_rslt_object_version_number ;
1998: raise;
2054: begin
2055: --
2056: if g_debug then
2057: l_proc := g_package || '.get_ctfn_count';
2058: hr_utility.set_location ('Entering '||l_proc,10);
2059: end if;
2060: --
2061: p_optional_count := 0;
2062: p_required_count := 0;
2064: p_open_optional_count := 0;
2065: --
2066: if p_prtt_enrt_actn_id is null then
2067: if g_debug then
2068: hr_utility.set_location ('Leaving '||l_proc,97);
2069: end if;
2070: return;
2071: end if;
2072: --
2089: close c_pcs;
2090: --
2091: else
2092: if g_debug then
2093: hr_utility.set_location ('Leaving '||l_proc,98);
2094: end if;
2095: return;
2096: --
2097: end if;
2100: p_optional_count := l_ctfn.tot_ctfn - l_ctfn.tot_rqd + 0;
2101: p_open_required_count := l_ctfn.tot_open_rqd + 0;
2102: p_open_optional_count := l_ctfn.tot_open_ctfn - p_open_required_count +0;
2103:
2104: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2105: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2106: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2107: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2108: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2101: p_open_required_count := l_ctfn.tot_open_rqd + 0;
2102: p_open_optional_count := l_ctfn.tot_open_ctfn - p_open_required_count +0;
2103:
2104: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2105: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2106: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2107: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2108: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2109:
2102: p_open_optional_count := l_ctfn.tot_open_ctfn - p_open_required_count +0;
2103:
2104: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2105: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2106: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2107: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2108: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2109:
2110: --
2103:
2104: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2105: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2106: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2107: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2108: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2109:
2110: --
2111: if g_debug then
2104: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2105: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2106: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2107: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2108: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2109:
2110: --
2111: if g_debug then
2112: hr_utility.set_location ('Leaving '||l_proc,99);
2108: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2109:
2110: --
2111: if g_debug then
2112: hr_utility.set_location ('Leaving '||l_proc,99);
2113: end if;
2114: --
2115: end get_ctfn_count;
2116: --
2144:
2145: --
2146: begin
2147: --
2148: g_debug := hr_utility.debug_enabled;
2149: if g_debug then
2150: l_proc := g_package||'.process_action_item';
2151: hr_utility.set_location ('Entering '||l_proc,10);
2152: end if;
2147: --
2148: g_debug := hr_utility.debug_enabled;
2149: if g_debug then
2150: l_proc := g_package||'.process_action_item';
2151: hr_utility.set_location ('Entering '||l_proc,10);
2152: end if;
2153: --
2154: l_prtt_enrt_actn_id := p_prtt_enrt_actn_id ;
2155: l_object_version_number := p_object_version_number ;
2226: NULL;
2227: end if;
2228: --
2229: if g_debug then
2230: hr_utility.set_location ('Leaving '||l_proc, 10);
2231: end if;
2232: --
2233: exception
2234: when others then
2233: exception
2234: when others then
2235:
2236: if g_debug then
2237: hr_utility.set_location('Exception Raised '||l_proc, 10);
2238: end if;
2239:
2240: p_prtt_enrt_actn_id := l_prtt_enrt_actn_id ;
2241: p_object_version_number := l_object_version_number ;
2357:
2358: --
2359: begin
2360: --
2361: g_debug := hr_utility.debug_enabled;
2362: if g_debug then
2363: l_proc := g_package||'.process_ctfn_action_item';
2364: hr_utility.set_location ('Entering '||l_proc, 10);
2365: end if;
2360: --
2361: g_debug := hr_utility.debug_enabled;
2362: if g_debug then
2363: l_proc := g_package||'.process_ctfn_action_item';
2364: hr_utility.set_location ('Entering '||l_proc, 10);
2365: end if;
2366: --
2367: p_prtt_enrt_actn_id := null;
2368: --
2413: elsif p_ctfn_rqd_flag = 'N' and p_ctfn_recd_dt is null then
2414: --
2415: -- Case 3: Optional Open Ctfn.
2416: --
2417: hr_utility.set_location ('p_ctfn_rqd_flag '|| p_ctfn_rqd_flag,10);
2418: hr_utility.set_location ('p_ctfn_recd_dt '|| p_ctfn_recd_dt,10);
2419:
2420: get_ctfn_count
2421: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2414: --
2415: -- Case 3: Optional Open Ctfn.
2416: --
2417: hr_utility.set_location ('p_ctfn_rqd_flag '|| p_ctfn_rqd_flag,10);
2418: hr_utility.set_location ('p_ctfn_recd_dt '|| p_ctfn_recd_dt,10);
2419:
2420: get_ctfn_count
2421: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2422: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2447: elsif p_ctfn_rqd_flag = 'N' and p_ctfn_recd_dt is not null then
2448: --
2449: -- Case 4: Optional Closed Ctfn.
2450: --
2451: hr_utility.set_location ('p_ctfn_rqd_flag '|| p_ctfn_rqd_flag,11);
2452: hr_utility.set_location ('p_ctfn_recd_dt '|| p_ctfn_recd_dt,11);
2453:
2454: get_ctfn_count
2455: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2448: --
2449: -- Case 4: Optional Closed Ctfn.
2450: --
2451: hr_utility.set_location ('p_ctfn_rqd_flag '|| p_ctfn_rqd_flag,11);
2452: hr_utility.set_location ('p_ctfn_recd_dt '|| p_ctfn_recd_dt,11);
2453:
2454: get_ctfn_count
2455: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2456: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2517: --
2518:
2519: IF g_debug
2520: THEN
2521: hr_utility.set_location ('Designation level code : ' || l_lvl_cd,14 );
2522: END IF;
2523:
2524: --
2525: -- check the level code for program, ptip or plan (default) and fetch the
2562: END IF;
2563:
2564: IF g_debug
2565: THEN
2566: hr_utility.set_location ('l_rqd_flag : ' || l_rqd_flag, 14);
2567: END IF;
2568: --End Bug 6353069
2569: --
2570: process_action_item
2583: p_datetrack_mode => hr_api.g_update,
2584: p_rslt_object_version_number => l_rslt.object_version_number);
2585: --
2586: if g_debug then
2587: hr_utility.set_location ('Leaving '||l_proc, 10);
2588: end if;
2589: --
2590: end process_new_ctfn_action;
2591: --
2613: l_date_of_birth date;
2614: --
2615: begin
2616: --
2617: g_debug := hr_utility.debug_enabled;
2618: if g_debug then
2619: l_proc := g_package||'.check_dob';
2620: hr_utility.set_location ('Entering '||l_proc,10);
2621: end if;
2616: --
2617: g_debug := hr_utility.debug_enabled;
2618: if g_debug then
2619: l_proc := g_package||'.check_dob';
2620: hr_utility.set_location ('Entering '||l_proc,10);
2621: end if;
2622: --
2623: open c_date_of_birth;
2624: fetch c_date_of_birth into l_date_of_birth;
2630: l_dob_found := FALSE;
2631: end if;
2632: --
2633: if g_debug then
2634: hr_utility.set_location ('Leaving ' ||l_proc,10);
2635: end if;
2636: --
2637: return l_dob_found;
2638: --
2638: --
2639: exception
2640: when others then
2641: if g_debug then
2642: hr_utility.set_location('Exception Raised '||l_proc, 10);
2643: end if;
2644: raise;
2645: --
2646: end check_dob;
2724: l_dpnt_bnf_adrs c_dpnt_bnf_adrs%rowtype;
2725: --
2726: begin
2727: --
2728: g_debug := hr_utility.debug_enabled;
2729: if g_debug then
2730: l_proc := g_package||'.check_adrs';
2731: hr_utility.set_location ('Entering '||l_proc,10);
2732: end if;
2727: --
2728: g_debug := hr_utility.debug_enabled;
2729: if g_debug then
2730: l_proc := g_package||'.check_adrs';
2731: hr_utility.set_location ('Entering '||l_proc,10);
2732: end if;
2733: --
2734: -- check if the person resides with the participant
2735: --
2772: --
2773: end if; -- l_rsds
2774: --
2775: if g_debug then
2776: hr_utility.set_location ('Leaving ' ||l_proc,10);
2777: end if;
2778: --
2779: return l_valid_adrs;
2780: --
2780: --
2781: exception
2782: when others then
2783: if g_debug then
2784: hr_utility.set_location('Exception Raised '||l_proc, 10);
2785: end if;
2786: raise;
2787: --
2788: end check_adrs;
2815: l_leg_id c_leg_id%rowtype;
2816: --
2817: begin
2818: --
2819: g_debug := hr_utility.debug_enabled;
2820: if g_debug then
2821: l_proc := g_package||'.check_legid';
2822: hr_utility.set_location ('Entering '||l_proc,10);
2823: end if;
2818: --
2819: g_debug := hr_utility.debug_enabled;
2820: if g_debug then
2821: l_proc := g_package||'.check_legid';
2822: hr_utility.set_location ('Entering '||l_proc,10);
2823: end if;
2824: --
2825: open c_leg_id;
2826: fetch c_leg_id into l_leg_id;
2832: l_legid_found := FALSE;
2833: end if;
2834: --
2835: if g_debug then
2836: hr_utility.set_location ('Leaving ' ||l_proc,10);
2837: end if;
2838: --
2839: return l_legid_found;
2840: --
2840: --
2841: exception
2842: when others then
2843: if g_debug then
2844: hr_utility.set_location('Exception Raised '||l_proc, 10);
2845: end if;
2846: raise;
2847: --
2848: end check_legid;
2862: begin
2863: --
2864: if g_debug then
2865: l_proc := g_package ||'.check_ctfn';
2866: hr_utility.set_location ('Entering '||l_proc,10);
2867: end if;
2868: --
2869: if (p_required_count + p_optional_count) = 0 then
2870: --
2869: if (p_required_count + p_optional_count) = 0 then
2870: --
2871: -- No certifications were found. Return FALSE.
2872: if g_debug then
2873: hr_utility.set_location ('Leaving '||l_proc,95);
2874: end if;
2875: return FALSE;
2876: --
2877: end if;
2880: if p_open_required_count = 0 and
2881: p_optional_count = 0 then
2882: -- all reqd provided and no optional ones found
2883: if g_debug then
2884: hr_utility.set_location ('Leaving '||l_proc,96);
2885: end if;
2886: return TRUE;
2887: --
2888: elsif p_open_required_count = 0 and
2888: elsif p_open_required_count = 0 and
2889: p_optional_count <> p_open_optional_count then
2890: -- all rqd prvdd and atleast one optional prvdd
2891: if g_debug then
2892: hr_utility.set_location ('Leaving '||l_proc,97);
2893: end if;
2894: return TRUE;
2895: --
2896: else
2895: --
2896: else
2897: -- certifications missing or no optional ones provided
2898: if g_debug then
2899: hr_utility.set_location ('Leaving '||l_proc,98);
2900: end if;
2901: return FALSE;
2902: --
2903: end if;
2902: --
2903: end if;
2904: --
2905: if g_debug then
2906: hr_utility.set_location ('Leaving '||l_proc,99);
2907: end if;
2908: --
2909: end check_ctfn;
2910: --
2935: l_proc varchar2(80) ;
2936: --
2937: begin
2938: --
2939: g_debug := hr_utility.debug_enabled;
2940: if g_debug then
2941: l_proc := g_package||'.check_enrt_ctfn';
2942: hr_utility.set_location ('Entering '||l_proc,10);
2943: end if;
2938: --
2939: g_debug := hr_utility.debug_enabled;
2940: if g_debug then
2941: l_proc := g_package||'.check_enrt_ctfn';
2942: hr_utility.set_location ('Entering '||l_proc,10);
2943: end if;
2944: --
2945: get_ctfn_count
2946: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2956: ,p_open_required_count => l_open_required
2957: ,p_open_optional_count => l_open_optional);
2958: --
2959: if g_debug then
2960: hr_utility.set_location ('Leaving '||l_proc,10);
2961: end if;
2962: --
2963: return l_return;
2964: --
2964: --
2965: exception
2966: when others then
2967: if g_debug then
2968: hr_utility.set_location('Exception Raised '||l_proc, 10);
2969: end if;
2970: raise;
2971: --
2972: end check_enrt_ctfn;
2998: l_proc varchar2(80) ;
2999: --
3000: begin
3001: --
3002: g_debug := hr_utility.debug_enabled;
3003: if g_debug then
3004: l_proc := g_package||'.check_dpnt_ctfn';
3005: hr_utility.set_location ('Entering '||l_proc,10);
3006: end if;
3001: --
3002: g_debug := hr_utility.debug_enabled;
3003: if g_debug then
3004: l_proc := g_package||'.check_dpnt_ctfn';
3005: hr_utility.set_location ('Entering '||l_proc,10);
3006: end if;
3007: --
3008: get_ctfn_count
3009: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
3019: ,p_open_required_count => l_open_required
3020: ,p_open_optional_count => l_open_optional);
3021: --
3022: if g_debug then
3023: hr_utility.set_location ('Leaving '||l_proc,99);
3024: end if;
3025: --
3026: return l_return;
3027: --
3027: --
3028: exception
3029: when others then
3030: if g_debug then
3031: hr_utility.set_location('Exception Raised '||l_proc, 999);
3032: end if;
3033: raise;
3034: --
3035: end check_dpnt_ctfn;
3071: begin
3072: --
3073: if g_debug then
3074: l_proc := g_package||'.check_write_ctfn';
3075: hr_utility.set_location ('Entering '||l_proc,10);
3076: end if;
3077: --
3078: if p_formula_id IS NOT NULL then
3079: --
3106: NULL; -- nothing to do at this time
3107: end if;
3108: --
3109: if g_debug then
3110: hr_utility.set_location ('Leaving ' ||l_proc,10);
3111: end if;
3112: --
3113: return l_write_ctfn;
3114: --
3114: --
3115: exception
3116: when others then
3117: if g_debug then
3118: hr_utility.set_location('Exception Raised '||l_proc, 10);
3119: end if;
3120: raise;
3121: --
3122: end check_write_ctfn;
3444: begin
3445: --
3446: if g_debug then
3447: l_proc := g_package||'.determine_dpnt_miss_actn_items';
3448: hr_utility.set_location ('Entering '||l_proc,10);
3449: end if;
3450:
3451: l_rslt_object_version_number := p_rslt_object_version_number ;
3452:
3456: fetch c_rslt into l_rslt;
3457: close c_rslt;
3458: else
3459: if g_debug then
3460: hr_utility.set_location('RESULT ID IS NULL!!' , 13);
3461: end if;
3462: end if;
3463: --
3464: if l_rslt.person_id is not null then
3495: close c_dpnt_lvl_cd;
3496: end if;
3497: --
3498: if g_debug then
3499: hr_utility.set_location('Designation level code : ' || l_lvl_cd, 14);
3500: end if;
3501: --
3502: -- check the level code for program, ptip or plan (default) and fetch the
3503: -- appropriate required-info-flags.
3533: --
3534: l_ff_ctfns_exists := FALSE ;
3535: --
3536: if g_debug then
3537: hr_utility.set_location('DOB action item ', 30);
3538: end if;
3539: --
3540: l_actn_typ_id := get_actn_typ_id
3541: (p_type_cd => 'DDDOB'
3555: --
3556: -- check if person has a date of birth entry
3557: --
3558: if g_debug then
3559: hr_utility.set_location('DOB is rqd', 35);
3560: end if;
3561: --
3562: if l_dpnt.susp_if_dpnt_dob_nt_prv_cd = 'RQDS' then
3563: l_rqd_flag := 'Y';
3595: -- if date_track mode is updating and designation at plan level
3596: -- delete action item of type DDDOB.
3597: --
3598: if g_debug then
3599: hr_utility.set_location('DOB is not required', 40);
3600: end if;
3601: --
3602: if (l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE) then
3603: --
3619: --
3620: -- Social security number/national identifier action item
3621: --
3622: if g_debug then
3623: hr_utility.set_location('SSN action item ', 45);
3624: end if;
3625: --
3626: l_actn_typ_id := get_actn_typ_id
3627: (p_type_cd => 'DDSSN'
3641: --
3642: -- check if person has a national identifier entry
3643: --
3644: if g_debug then
3645: hr_utility.set_location('SSN is rqd', 50);
3646: end if;
3647: --
3648: if l_dpnt.susp_if_dpnt_ssn_nt_prv_cd = 'RQDS' then
3649: l_rqd_flag := 'Y';
3678: --
3679: else
3680: --
3681: if g_debug then
3682: hr_utility.set_location('SSN is not rqd', 55);
3683: end if;
3684: --
3685: -- dpnt_leg_id_rqd_flag is 'N'
3686: -- if date_track mode is updating and designation at plan level
3707: --
3708: -- address action item
3709: --
3710: if g_debug then
3711: hr_utility.set_location('ADDR action item ' , 60);
3712: end if;
3713: --
3714: l_actn_typ_id := get_actn_typ_id
3715: (p_type_cd => 'DDADDR'
3729: --
3730: -- check if person has an address
3731: --
3732: if g_debug then
3733: hr_utility.set_location('ADDR is rqd', 65);
3734: end if;
3735: --
3736: if l_dpnt.susp_if_dpnt_adr_nt_prv_cd = 'RQDS' then
3737: l_rqd_flag := 'Y';
3767: --
3768: else
3769: --
3770: if g_debug then
3771: hr_utility.set_location('ADD is not rqd', 70);
3772: end if;
3773: --
3774: -- dpnt_adrs_rqd_flag is 'N'
3775: -- if date_track mode is updating and designation at plan level
3793: --
3794: -- certification action item
3795: --
3796: if g_debug then
3797: hr_utility.set_location('CTFN action item ' , 75);
3798: end if;
3799: --
3800: l_actn_typ_id := get_actn_typ_id
3801: (p_type_cd => 'DDCTFN'
3819: --
3820: if l_dpnt.dpnt_dsgn_no_ctfn_rqd_flag = 'N' then
3821: -- double negative....ctfn is required.
3822: if g_debug then
3823: hr_utility.set_location('DPNT CTFN is rqd', 80);
3824: end if;
3825: --
3826: --
3827: -- check if person has certification
3850: not(l_rqd_data_found) and
3851: (l_ctfns_defined) then
3852: --
3853: if g_debug then
3854: hr_utility.set_location('Ctfn defined and not provided', 85);
3855: end if;
3856: --
3857: -- Bug 3851427 : If certifications are considered at LER_CHG_DPNT level, then Suspension of
3858: -- enrollment ( SUSP_IF_CTFN_NOT_PRVD_FLAG ) should also be considered at LER_CHG_DPNT level
3906: --
3907: close c_mng_dpnts;
3908: --
3909: if g_debug then
3910: hr_utility.set_location('l_all_lvls:'||l_all_lvls|| 'l_lvl_cd:'||
3911: l_lvl_cd||' l_pgm_id:'||to_char(l_rslt.pgm_id), 87);
3912: end if;
3913: --
3914: -- now write out the appropriate certifications for each level
4062: ,p_cvrd_dpnt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id);
4063: --
4064: else
4065: if g_debug then
4066: hr_utility.set_location('Dpnt ctfns at ptip in else - false returned ', 111);
4067: end if;
4068: end if;
4069: --
4070: end loop;
4068: end if;
4069: --
4070: end loop;
4071: if g_debug then
4072: hr_utility.set_location('Dpnt ctfns at ptip level - false returned ', 990);
4073: end if;
4074: --
4075: else
4076: --
4123: if l_all_lvls = NOT_FOUND and
4124: (l_lvl_cd = 'PL' ) then
4125: --
4126: if g_debug then
4127: hr_utility.set_location('Dpnt ctfns at plan level', 90);
4128: end if;
4129: --
4130: if l_ctfn_at_ler_chg = TRUE then
4131: --
4207: -- presently we want to write the certifications always.
4208: -- unless the above rule indicates not to.
4209: --
4210: if g_debug then
4211: hr_utility.set_location('Writing Certification', 95);
4212: end if;
4213: --
4214: write_new_dpnt_ctfn_item
4215: (p_elig_cvrd_dpnt_id => l_cvrd_dpnt.elig_cvrd_dpnt_id
4222: ,p_cvrd_dpnt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id);
4223: --
4224: else
4225: if g_debug then
4226: hr_utility.set_location('Rule failed.. not writing ctfn', 100);
4227: end if;
4228: end if;
4229: --
4230: end loop;
4239: IF l_ff_ctfns_exists = FALSE
4240: THEN
4241: --
4242: if g_debug then
4243: hr_utility.set_location ('Entering l_ff_ctfns_exists',999);
4244: end if;
4245: --
4246: -- bug: 5513339
4247: open c_curr_ovn_of_actn (l_prtt_enrt_actn_id);
4257: ,p_datetrack_mode => hr_api.g_zap
4258: ,p_post_rslt_flag => p_post_rslt_flag);
4259: --
4260: if g_debug then
4261: hr_utility.set_location ('Leaving l_ff_ctfns_exists',999);
4262: end if;
4263: --Start Bug 5998009
4264: ELSE
4265: l_dpnt_ctfn_actn_warning := TRUE;
4283: end if;
4284: --
4285: -- Bug 6434143
4286: if g_debug then
4287: hr_utility.set_location('Existing actn item. Have rqd info', 110);
4288: end if;
4289: --
4290: -- Existing open action item. But we now have required info. Close the
4291: -- action item by setting the cmpltd_dt field.
4311: -- Existing closed action item. But required info is missing. Reopen the
4312: -- action item.
4313: --
4314: if g_debug then
4315: hr_utility.set_location('Existing actn item. Rqd info missing', 115);
4316: end if;
4317: --
4318: --Bug 6434143
4319: if l_ctfn_at_ler_chg = true
4347: --
4348: else
4349: --
4350: if g_debug then
4351: hr_utility.set_location('Dpnt ctfn rqd flag is N', 120);
4352: end if;
4353: --
4354: -- dpnt_ctfn_rqd_flag is 'N'
4355: -- if date_track mode is updating and designation at plan level
4375: --
4376: end loop; -- dpnt loop
4377: --
4378: if g_debug then
4379: hr_utility.set_location ('Leaving '||l_proc,999);
4380: end if;
4381: --
4382: p_dpnt_actn_warning := l_dpnt_actn_warning;
4383: --
4392: exception
4393: --
4394: when others then
4395: if g_debug then
4396: hr_utility.set_location('Exception Raised '||l_proc, 997);
4397: end if;
4398: p_rslt_object_version_number :=l_rslt_object_version_number;
4399: p_dpnt_actn_warning := null;
4400:
4447: --
4448: BEGIN
4449: --
4450: if g_debug then
4451: hr_utility.set_location ('Entering '||l_proc,10);
4452: end if;
4453: --
4454:
4455: l_rslt_object_version_number := p_rslt_object_version_number ;
4491:
4492: CLOSE c_future_sal_inc;
4493: -- new call to write to ben_reporting
4494: if g_debug then
4495: hr_utility.set_location ('Leaving '||l_proc, 10);
4496: end if;
4497: --
4498: EXCEPTION
4499: WHEN OTHERS THEN
4497: --
4498: EXCEPTION
4499: WHEN OTHERS THEN
4500: if g_debug then
4501: hr_utility.set_location('Exception Raised '||l_proc, 10);
4502: end if;
4503: fnd_file.put_line(fnd_file.log,'Exception Raised -'||l_proc);
4504: fnd_file.put_line(fnd_file.log,fnd_message.get);
4505: fnd_file.put_line(fnd_file.log,sqlerrm);
4531:
4532: --
4533: begin
4534: --
4535: g_debug := hr_utility.debug_enabled;
4536: if g_debug then
4537: l_proc := g_package||'.process_cwb_actn_items';
4538: hr_utility.set_location ('Entering '||l_proc,10);
4539: end if;
4534: --
4535: g_debug := hr_utility.debug_enabled;
4536: if g_debug then
4537: l_proc := g_package||'.process_cwb_actn_items';
4538: hr_utility.set_location ('Entering '||l_proc,10);
4539: end if;
4540: if g_debug then
4541: hr_utility.set_location ('Entering rslt id '||to_char(p_prtt_enrt_rslt_id),11);
4542: end if;
4537: l_proc := g_package||'.process_cwb_actn_items';
4538: hr_utility.set_location ('Entering '||l_proc,10);
4539: end if;
4540: if g_debug then
4541: hr_utility.set_location ('Entering rslt id '||to_char(p_prtt_enrt_rslt_id),11);
4542: end if;
4543: --
4544: l_rslt_object_version_number := p_rslt_object_version_number ;
4545: l_suspend_flag := p_suspend_flag ;
4572: raise hr_api.validate_enabled;
4573: end if;
4574: --
4575: if g_debug then
4576: hr_utility.set_location ('Leaving '||l_proc, 10);
4577: end if;
4578: --
4579: exception
4580: --
4582: then
4583: rollback to process_cwb_actn_items;
4584: --
4585: if g_debug then
4586: hr_utility.set_location(' Leaving:'||l_proc, 10);
4587: end if;
4588: --
4589: when others then
4590: if g_debug then
4587: end if;
4588: --
4589: when others then
4590: if g_debug then
4591: hr_utility.set_location('Exception Raised '||l_proc, 10);
4592: end if;
4593: p_rslt_object_version_number := l_rslt_object_version_number ;
4594: p_suspend_flag := l_suspend_flag ;
4595: raise;
4759: begin
4760: --
4761: if g_debug then
4762: l_proc := g_package||'.determine_dpnt_actn_items';
4763: hr_utility.set_location ('Entering '||l_proc,10);
4764: end if;
4765: --
4766: l_rslt_object_version_number :=p_rslt_object_version_number ;
4767:
4788:
4789: if ((p_allws_flag = 'Y') or (p_dsgn_cd = 'R')) then
4790: --
4791: if g_debug then
4792: hr_utility.set_location('Dpnt dsgn allowed', 10);
4793: end if;
4794: --
4795: -- Check if any dependents are designated
4796: --
4807: l_rqd_flag := 'N';
4808: end if;
4809: --
4810: if g_debug then
4811: hr_utility.set_location('Dpnt dsgn rqd flag : ' || l_rqd_flag || ' ' || l_proc, 20);
4812: end if;
4813: --
4814: if c_dpnt_dsgn%NOTFOUND then
4815: --
4813: --
4814: if c_dpnt_dsgn%NOTFOUND then
4815: --
4816: if g_debug then
4817: hr_utility.set_location('No dpnts designated', 10);
4818: end if;
4819: --
4820: -- there are no dependents designated
4821: --
4846: If c_elig_dpnts%FOUND then
4847: --
4848: l_open_act_item_flag := TRUE ;
4849: if g_debug then
4850: hr_utility.set_location('Eligible dpnts found: ', 10);
4851: end if;
4852: --
4853: Else
4854: l_open_act_item_flag := FALSE ;
4870:
4871: /* if c_not_emp_only%found then
4872: --
4873: if g_debug then
4874: hr_utility.set_location('c_not_emp_only found', 10);
4875: end if;
4876: --
4877: l_open_act_item_flag := TRUE ;
4878: --
4878: --
4879: else
4880: --
4881: if g_debug then
4882: hr_utility.set_location('c_not_emp_only not found: l_mx_dpnts_alwd_num: '||l_mx_dpnts_alwd_num, 10);
4883: end if;
4884: --
4885: Open c_elig_dpnts;
4886: Fetch c_elig_dpnts into l_Dummy;
4888: If c_elig_dpnts%FOUND then
4889: --
4890: l_open_act_item_flag := TRUE ;
4891: if g_debug then
4892: hr_utility.set_location('c_lig_dpnts found: ', 10);
4893: end if;
4894: --
4895: Else
4896: l_open_act_item_flag := FALSE ;
4931: -- Also, delete all other "dependent" action items that might exist for
4932: -- the prtt_enrt_rslt_id.
4933: --
4934: if g_debug then
4935: hr_utility.set_location('Deleting other dpnt actn items', 10);
4936: end if;
4937: --
4938: for actn_item_rec in c_other_dpnt_actn_items loop
4939: --
4964: --
4965: else
4966: --
4967: if g_debug then
4968: hr_utility.set_location('Dpnts designated', 10);
4969: end if;
4970: --
4971: -- Dependents found. Set the out parameter.
4972: --
4995: --
4996: end if;
4997: --
4998: if g_debug then
4999: hr_utility.set_location('Leaving ' ||l_proc,10);
5000: end if;
5001: --
5002: exception
5003: when others then
5001: --
5002: exception
5003: when others then
5004: if g_debug then
5005: hr_utility.set_location('Exception Raised '||l_proc, 10);
5006: end if;
5007: p_rslt_object_version_number := l_rslt_object_version_number ;
5008: p_dd_actn_item_open := null;
5009: p_hack := null;
5149: begin
5150: --
5151: if g_debug then
5152: l_proc := g_package||'.complete_dependent_designation';
5153: hr_utility.set_location ('Entering '||l_proc,10);
5154: end if;
5155: --
5156: l_rslt_object_version_number :=p_rslt_object_version_number;
5157: l_dpnt_actn_warning :=p_dpnt_actn_warning ;
5182: --
5183: close c_min_no_grp;
5184: --
5185: if g_debug then
5186: hr_utility.set_location ('After c_min_no_grp '||l_proc,20);
5187: end if;
5188: --
5189: -- Check the other dsgn rqmts. If there are any rows returned from
5190: -- this cursor, then the person must designate more dependents of
5200: --
5201: close c_min_typ;
5202: --
5203: if g_debug then
5204: hr_utility.set_location ('After c_min_typ '||l_proc,30);
5205: end if;
5206: --
5207: --
5208: -- find DDADDNL action item, if it exists.
5220: ,p_cmpltd_dt => l_cmpltd_dt
5221: ,p_object_version_number => l_object_version_number);
5222: --
5223: if g_debug then
5224: hr_utility.set_location ('After get_prtt_enrt_actn_id call '||l_proc,30);
5225: end if;
5226: --
5227: if p_dsgn_cd = 'R' then
5228: l_rqd_flag := 'Y';
5232: --
5233: if l_meets_rqmt = 'Y' then
5234: --
5235: if g_debug then
5236: hr_utility.set_location('Meets addnl dpnt rqmts', 10);
5237: end if;
5238: --
5239: -- they meet the minimum number of dpnts, call process-action-item with
5240: -- rqd-data 'true'
5255: --
5256: elsif l_meets_rqmt = 'N' then
5257: --
5258: if g_debug then
5259: hr_utility.set_location('Does not meet rqmts', 10);
5260: end if;
5261: --
5262: --
5263: -- Doesn't meet requirements. set rqd-data 'FALSE'
5285: p_dpnt_actn_warning := TRUE;
5286: end if;
5287: --
5288: if g_debug then
5289: hr_utility.set_location ('Leaving ' ||l_proc,90);
5290: end if;
5291: --
5292: exception
5293: when others then
5291: --
5292: exception
5293: when others then
5294: if g_debug then
5295: hr_utility.set_location('Exception Raised '||l_proc, 99);
5296: end if;
5297: p_rslt_object_version_number := l_rslt_object_version_number;
5298: p_dpnt_actn_warning := l_dpnt_actn_warning ;
5299: raise;
5357: and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
5358: --
5359: begin
5360: --
5361: g_debug := hr_utility.debug_enabled;
5362: if g_debug then
5363: l_proc := g_package||'.process_dpnt_actn_items';
5364: hr_utility.set_location ('Entering '||l_proc,10);
5365: end if;
5360: --
5361: g_debug := hr_utility.debug_enabled;
5362: if g_debug then
5363: l_proc := g_package||'.process_dpnt_actn_items';
5364: hr_utility.set_location ('Entering '||l_proc,10);
5365: end if;
5366: --
5367: l_rslt_object_version_number :=p_rslt_object_version_number;
5368: l_suspend_flag :=p_suspend_flag ;
5469: raise hr_api.validate_enabled;
5470: end if;
5471: --
5472: if g_debug then
5473: hr_utility.set_location ('Leaving '||l_proc, 10);
5474: end if;
5475: --
5476: exception
5477: --
5479: then
5480: rollback to process_dpnt_actn_items;
5481: --
5482: if g_debug then
5483: hr_utility.set_location(' Leaving:'||l_proc, 10);
5484: end if;
5485: --
5486: when others then
5487: if g_debug then
5484: end if;
5485: --
5486: when others then
5487: if g_debug then
5488: hr_utility.set_location('Exception Raised '||l_proc, 10);
5489: end if;
5490:
5491: p_rslt_object_version_number := l_rslt_object_version_number ;
5492: p_suspend_flag := l_suspend_flag ;
5846:
5847: --
5848: Begin
5849: --
5850: hr_utility.set_location('Entering - Check certification prvdd',10);
5851: --
5852: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5853: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5854: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5848: Begin
5849: --
5850: hr_utility.set_location('Entering - Check certification prvdd',10);
5851: --
5852: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5853: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5854: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5855: hr_utility.set_location('enrt_r_bnft_ctfn_cd'||p_enrt_r_bnft_ctfn_cd,11);
5856: --bug#3748133
5849: --
5850: hr_utility.set_location('Entering - Check certification prvdd',10);
5851: --
5852: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5853: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5854: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5855: hr_utility.set_location('enrt_r_bnft_ctfn_cd'||p_enrt_r_bnft_ctfn_cd,11);
5856: --bug#3748133
5857: /*
5850: hr_utility.set_location('Entering - Check certification prvdd',10);
5851: --
5852: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5853: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5854: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5855: hr_utility.set_location('enrt_r_bnft_ctfn_cd'||p_enrt_r_bnft_ctfn_cd,11);
5856: --bug#3748133
5857: /*
5858: if p_crntly_enrd_flag = 'Y' and p_ctfn_determine_cd = 'ENRFT' then
5851: --
5852: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5853: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5854: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5855: hr_utility.set_location('enrt_r_bnft_ctfn_cd'||p_enrt_r_bnft_ctfn_cd,11);
5856: --bug#3748133
5857: /*
5858: if p_crntly_enrd_flag = 'Y' and p_ctfn_determine_cd = 'ENRFT' then
5859: if p_bnft_amt = 0 then
5878: --If defined at the plan level, then need to see the previous enrollment in
5879: --any option in plan. If the ctfn is not defined at the plan level just check
5880: --for the currently enrolled
5881: --
5882: hr_utility.set_location(' p_ctfn_determine_cd = '||p_ctfn_determine_cd,122);
5883: --
5884: open c_pl_ctfn(p_pl_id,p_per_in_ler_id,p_effective_date,p_enrt_ctfn_typ_cd);
5885: fetch c_pl_ctfn into l_dummy;
5886: if c_pl_ctfn%found then
5884: open c_pl_ctfn(p_pl_id,p_per_in_ler_id,p_effective_date,p_enrt_ctfn_typ_cd);
5885: fetch c_pl_ctfn into l_dummy;
5886: if c_pl_ctfn%found then
5887: --
5888: hr_utility.set_location(' Plan Level Cert',122);
5889: --
5890: open c_current_pl_enrollment(p_prtt_enrt_rslt_id);
5891: --
5892: fetch c_current_pl_enrollment into l_dummy;
5890: open c_current_pl_enrollment(p_prtt_enrt_rslt_id);
5891: --
5892: fetch c_current_pl_enrollment into l_dummy;
5893: if c_current_pl_enrollment%found then
5894: hr_utility.set_location(' Plan Level - Currently Enrolled in',122);
5895: l_ctfn_prvdd := TRUE;
5896: end if;
5897: --
5898: close c_current_pl_enrollment;
5898: close c_current_pl_enrollment;
5899: --
5900: elsif p_crntly_enrd_flag = 'Y' and p_oipl_id is not null then
5901: --
5902: hr_utility.set_location(' Option Level -',123);
5903: --Now we need to check if the previous enrollment was unsuspeded or not
5904: open c_current_oipl_enrollment(p_prtt_enrt_rslt_id);
5905: --
5906: fetch c_current_oipl_enrollment into l_dummy;
5904: open c_current_oipl_enrollment(p_prtt_enrt_rslt_id);
5905: --
5906: fetch c_current_oipl_enrollment into l_dummy;
5907: if c_current_oipl_enrollment%found then
5908: hr_utility.set_location(' Option in Plan Level - Currently Enrolled in',122);
5909: l_ctfn_prvdd := TRUE;
5910: end if;
5911: --
5912: close c_current_oipl_enrollment;
5911: --
5912: close c_current_oipl_enrollment;
5913: --
5914: else
5915: hr_utility.set_location('Not Currently Enrolled in- why here ', 123);
5916: end if;
5917: close c_pl_ctfn ;
5918: end if;
5919: --
5942: --
5943: if not l_ctfn_prvdd and p_oipl_id is not null and
5944: p_ctfn_determine_cd = 'ENRNP'then
5945: --
5946: hr_utility.set_location('different opt',12);
5947: open c_prtt_enrt_rslt(p_prtt_enrt_rslt_id);
5948: fetch c_prtt_enrt_rslt into l_prtt_enrt_rslt_id;
5949: close c_prtt_enrt_rslt;
5950: --
5947: open c_prtt_enrt_rslt(p_prtt_enrt_rslt_id);
5948: fetch c_prtt_enrt_rslt into l_prtt_enrt_rslt_id;
5949: close c_prtt_enrt_rslt;
5950: --
5951: hr_utility.set_location('Prtt result Id'||l_prtt_enrt_rslt_id,13);
5952: if l_prtt_enrt_rslt_id is not null then
5953: --
5954: if p_rqd_flag = 'Y' then
5955: hr_utility.set_location('inside',11);
5951: hr_utility.set_location('Prtt result Id'||l_prtt_enrt_rslt_id,13);
5952: if l_prtt_enrt_rslt_id is not null then
5953: --
5954: if p_rqd_flag = 'Y' then
5955: hr_utility.set_location('inside',11);
5956: open c_ctfn_prvdd(l_prtt_enrt_rslt_id);
5957: fetch c_ctfn_prvdd into l_dummy;
5958: if c_ctfn_prvdd%found then
5959: l_ctfn_prvdd := TRUE;
5964: --
5965: open c_ctfn_prvdd2(l_prtt_enrt_rslt_id);
5966: fetch c_ctfn_prvdd2 into l_dummy;
5967: if c_ctfn_prvdd2%found then
5968: hr_utility.set_location('True',14);
5969: l_ctfn_prvdd := TRUE;
5970: end if;
5971: close c_ctfn_prvdd2;
5972: --
6034: end if;
6035: p_ctfn_prvdd := l_ctfn_prvdd;
6036: --
6037: if p_ctfn_prvdd then
6038: hr_utility.set_location('Leaving Check Ctfn Prvdd TRUE',13);
6039: else
6040: hr_utility.set_location('Leaving Check Ctfn Prvdd FALSE',14);
6041: end if;
6042: --
6036: --
6037: if p_ctfn_prvdd then
6038: hr_utility.set_location('Leaving Check Ctfn Prvdd TRUE',13);
6039: else
6040: hr_utility.set_location('Leaving Check Ctfn Prvdd FALSE',14);
6041: end if;
6042: --
6043: end;
6044:
6209: -- End Bug 5887665
6210: --
6211: begin
6212: --
6213: g_debug := hr_utility.debug_enabled;
6214: if g_debug then
6215: hr_utility.set_location ('Entering '||l_proc,10);
6216: end if;
6217: --
6211: begin
6212: --
6213: g_debug := hr_utility.debug_enabled;
6214: if g_debug then
6215: hr_utility.set_location ('Entering '||l_proc,10);
6216: end if;
6217: --
6218: l_rslt_object_version_number :=p_rslt_object_version_number ;
6219: l_suspend_flag :=p_suspend_flag ;
6240: -- fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
6241: -- fnd_message.set_token('BUSINESS_GROUP_ID',to_char(p_business_group_id));
6242: -- fnd_message.raise_error;
6243: if g_debug then
6244: hr_utility.set_location('Enrollment already ended/ZAPed by another process ',22);
6245: end if;
6246: return ;
6247: else
6248: close c_epe;
6260: p_prtt_enrt_actn_id => l_prtt_enrt_actn_id,
6261: p_cmpltd_dt => l_cmpltd_dt,
6262: p_object_version_number => l_object_version_number); */ -- Commented for 4241743
6263: --
6264: hr_utility.set_location('CTFN rqd flag '||l_epe.ctfn_rqd_flag,9);
6265: if l_epe.ctfn_rqd_flag = 'Y' then
6266: --
6267: if g_debug then
6268: hr_utility.set_location('CTFN rqd flag is Y', 10);
6264: hr_utility.set_location('CTFN rqd flag '||l_epe.ctfn_rqd_flag,9);
6265: if l_epe.ctfn_rqd_flag = 'Y' then
6266: --
6267: if g_debug then
6268: hr_utility.set_location('CTFN rqd flag is Y', 10);
6269: hr_utility.set_location('l_actn_exists '||l_actn_exists,10);
6270: end if;
6271: -- Bug No 4241743
6272: l_prtt_enrt_actn_id := null;
6265: if l_epe.ctfn_rqd_flag = 'Y' then
6266: --
6267: if g_debug then
6268: hr_utility.set_location('CTFN rqd flag is Y', 10);
6269: hr_utility.set_location('l_actn_exists '||l_actn_exists,10);
6270: end if;
6271: -- Bug No 4241743
6272: l_prtt_enrt_actn_id := null;
6273: open c_enrt_actn
6280: fetch c_enrt_actn into l_enrt_actn;
6281: exit when c_enrt_actn%notfound;
6282: l_prtt_enrt_actn_id := l_enrt_actn.prtt_enrt_actn_id;
6283: if l_epe.per_in_ler_id = l_enrt_actn.per_in_ler_id then
6284: hr_utility.set_location('Actn Id '||l_prtt_enrt_actn_id||'found in per_in_ler_id '||l_enrt_actn.per_in_ler_id,12);
6285: l_actn_exists := 'Y';
6286: exit;
6287: end if;
6288: end loop;
6286: exit;
6287: end if;
6288: end loop;
6289: close c_enrt_actn;
6290: hr_utility.set_location('After loop l_actn_exists '||l_actn_exists,12);
6291: --
6292: -- Loop through the certifications that might exist for the eltbl chc and
6293: -- write required certifications to the prtt_enrt_ctfn_prvdd table
6294: --
6313: --
6314: -- Bug No 4241743 Added l_actn_exists check in if condition
6315: if ((l_prtt_enrt_actn_id is null or l_actn_exists = 'N') and not l_ctfn_prvdd) then
6316: --
6317: hr_utility.set_location('write action ',12);
6318: write_new_action_item
6319: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
6320: ,p_rslt_object_version_number => p_rslt_object_version_number
6321: ,p_actn_typ_id => l_actn_typ_id
6368: fnd_message.raise_error;
6369: else
6370: close c_enrt_bnft;
6371: if g_debug then
6372: hr_utility.set_location('c_enrt_bnft found', 10);
6373: end if;
6374: end if;
6375: --
6376: -- Check if there are any ENRTCTFN action items and certifications that
6423: end if;
6424:
6425: -- write the ctfn's only if the flag is 'Y' AND if the cvg amt was
6426: -- enterable, the entered amt was more than the mx_wout_ctfn_val.Bug 1249901
6427: hr_utility.set_location('Benefit amount '||l_epe.bnft_amt,11);
6428: --
6429: open c_ler_rstrn (p_effective_date, l_epe.per_in_ler_id, l_epe.pl_id);
6430: fetch c_ler_rstrn into l_cvg_incr_code;
6431: close c_ler_rstrn;
6449: --
6450: -- Bug 3183266 : For flat range l_enrt_bnft.mx_wout_ctfn_val will be null
6451: --
6452: if g_debug then
6453: hr_utility.set_location('CTFN rqd flag is Y', 10);
6454: end if;
6455: --
6456: -- Loop through the certifications that might exist for the eltbl chc and
6457: -- write required certifications to the prtt_enrt_ctfn_prvdd table
6520: raise hr_api.validate_enabled;
6521: end if;
6522: --
6523: if g_debug then
6524: hr_utility.set_location ('Leaving ' ||l_proc,10);
6525: end if;
6526: --
6527: exception
6528: --
6529: when hr_api.validate_enabled
6530: then
6531: rollback to determine_other_actn_items;
6532: if g_debug then
6533: hr_utility.set_location ('Leaving ' ||l_proc,10);
6534: end if;
6535: --
6536: when others then
6537: if g_debug then
6534: end if;
6535: --
6536: when others then
6537: if g_debug then
6538: hr_utility.set_location('Exception Raised '||l_proc, 10);
6539: end if;
6540: raise;
6541: --
6542: end determine_other_actn_items;
6591: begin
6592: --
6593: if g_debug then
6594: l_proc := g_package||'.determine_pcp_dpnt_actn_items';
6595: hr_utility.set_location ('Entering '||l_proc,10);
6596: end if;
6597: --
6598: l_rslt_object_version_number := p_rslt_object_version_number ;
6599: l_suspend_flag := p_suspend_flag ;
6608: l_rqd_flag := 'N';
6609: end if;
6610: --
6611: if g_debug then
6612: hr_utility.set_location('pcp dpnt dsgn rqd flag : ' || l_rqd_flag || ' ' ||
6613: l_proc, 20);
6614: end if;
6615: --
6616: -- As the only modification happening in the code is for rqd_flag = 'Y',
6621: fetch c_prmry_care_actn into l_dummy;
6622: if c_prmry_care_actn%notfound then
6623: l_rqd_data_found := FALSE;
6624: if g_debug then
6625: hr_utility.set_location('c_prmry_care_actn notfound' , 90 );
6626: end if;
6627: elsif c_prmry_care_actn%found then
6628: l_rqd_data_found := TRUE;
6629: if g_debug then
6626: end if;
6627: elsif c_prmry_care_actn%found then
6628: l_rqd_data_found := TRUE;
6629: if g_debug then
6630: hr_utility.set_location('c_prmry_care_actn found' , 91 );
6631: end if;
6632: end if;
6633: close c_prmry_care_actn;
6634: --
6632: end if;
6633: close c_prmry_care_actn;
6634: --
6635: if g_debug then
6636: hr_utility.set_location('PCPDPNT action item ', 30);
6637: end if;
6638: --
6639: l_actn_typ_id := get_actn_typ_id
6640: (p_type_cd => 'PCPDPNT'
6640: (p_type_cd => 'PCPDPNT'
6641: ,p_business_group_id => p_business_group_id);
6642: --
6643: if g_debug then
6644: hr_utility.set_location ('Entering get_prtt_enrt_actn_id ' , 101);
6645: end if;
6646: get_prtt_enrt_actn_id
6647: (p_actn_typ_id => l_actn_typ_id,
6648: p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
6661: l_rqd_data_found = TRUE and
6662: l_cmpltd_dt IS NULL then
6663: --
6664: if g_debug then
6665: hr_utility.set_location ('Case 1 ',102);
6666: end if;
6667: -- Existing open action item but we now have required data. Close the open
6668: -- action item by setting cmpltd_dt field
6669: --
6674: --
6675: -- Found a closed action item. But required data is missing. Reopen item
6676: --
6677: if g_debug then
6678: hr_utility.set_location ('Case2 ',103);
6679: end if;
6680: l_pcp_dpnt_actn_warning := TRUE;
6681: else
6682: if g_debug then
6679: end if;
6680: l_pcp_dpnt_actn_warning := TRUE;
6681: else
6682: if g_debug then
6683: hr_utility.set_location ('Case3 ',104);
6684: end if;
6685: l_pcp_dpnt_actn_warning := FALSE;
6686: end if;
6687: -- process the action item
6708: raise hr_api.validate_enabled;
6709: end if;
6710: --
6711: if g_debug then
6712: hr_utility.set_location ('Leaving '||l_proc, 10);
6713: end if;
6714: --
6715:
6716: exception
6719: then
6720: rollback to determine_pcp_dpnt_actn_items;
6721: --
6722: if g_debug then
6723: hr_utility.set_location(' Leaving:'||l_proc, 10);
6724: end if;
6725: --
6726: when others then
6727: if g_debug then
6724: end if;
6725: --
6726: when others then
6727: if g_debug then
6728: hr_utility.set_location('Exception Raised '||l_proc, 10);
6729: end if;
6730: p_rslt_object_version_number := l_rslt_object_version_number;
6731: p_suspend_flag := l_suspend_flag ;
6732: p_pcp_dpnt_actn_warning := null ;
6777: begin
6778: --
6779: if g_debug then
6780: l_proc := g_package||'.determine_pcp_actn_items';
6781: hr_utility.set_location ('Entering '||l_proc,10);
6782: end if;
6783: --
6784:
6785: l_rslt_object_version_number := p_rslt_object_version_number ;
6795: l_rqd_flag := 'N';
6796: end if;
6797: --
6798: if g_debug then
6799: hr_utility.set_location('pcp dsgn rqd flag : ' || l_rqd_flag || ' ' ||
6800: l_proc, 20);
6801: end if;
6802: --
6803: -- As the only modification happening in the code is for rqd_flag = 'Y',
6813: end if;
6814: close c_prmry_care_actn;
6815: --
6816: if g_debug then
6817: hr_utility.set_location('PCPPRTT action item ', 30);
6818: end if;
6819: --
6820: l_actn_typ_id := get_actn_typ_id
6821: (p_type_cd => 'PCPPRTT'
6834: if (l_prtt_enrt_actn_id IS NULL and
6835: l_rqd_data_found = FALSE) then
6836: l_pcp_actn_warning := TRUE;
6837: if g_debug then
6838: hr_utility.set_location ('Leaving '||'TRUE', 11);
6839: end if;
6840: elsif l_prtt_enrt_actn_id IS NOT NULL and
6841: l_rqd_data_found = TRUE and
6842: l_cmpltd_dt IS NULL then
6844: -- Existing open action item but we now have required data. Close the open
6845: -- action item by setting cmpltd_dt field
6846: --
6847: if g_debug then
6848: hr_utility.set_location ('Leaving '||'FALSE', 12);
6849: end if;
6850: l_pcp_actn_warning := FALSE;
6851: elsif l_prtt_enrt_actn_id IS NOT NULL and
6852: l_rqd_data_found = FALSE and
6854: --
6855: -- Found a closed action item. But required data is missing. Reopen item
6856: --
6857: if g_debug then
6858: hr_utility.set_location ('Leaving '||'TRUE', 13);
6859: end if;
6860: l_pcp_actn_warning := TRUE;
6861: else
6862: if g_debug then
6859: end if;
6860: l_pcp_actn_warning := TRUE;
6861: else
6862: if g_debug then
6863: hr_utility.set_location ('Leaving '||'FALSE', 14);
6864: end if;
6865: l_pcp_actn_warning := FALSE;
6866: end if;
6867: -- process the action item
6887: raise hr_api.validate_enabled;
6888: end if;
6889: --
6890: if g_debug then
6891: hr_utility.set_location ('Leaving '||l_proc, 10);
6892: end if;
6893: --
6894:
6895: exception
6898: then
6899: rollback to determine_pcp_actn_items;
6900: --
6901: if g_debug then
6902: hr_utility.set_location(' Leaving:'||l_proc, 10);
6903: end if;
6904: --
6905: when others then
6906: if g_debug then
6903: end if;
6904: --
6905: when others then
6906: if g_debug then
6907: hr_utility.set_location('Exception Raised '||l_proc, 10);
6908: end if;
6909:
6910: p_rslt_object_version_number := l_rslt_object_version_number;
6911: p_suspend_flag := l_suspend_flag ;
6988: and pen.effective_end_date;
6989: --
6990: begin
6991: --
6992: g_debug := hr_utility.debug_enabled;
6993: if g_debug then
6994: l_proc := g_package||'.process_pcp_dpnt_actn_items';
6995: hr_utility.set_location ('Entering '||l_proc,10);
6996: end if;
6991: --
6992: g_debug := hr_utility.debug_enabled;
6993: if g_debug then
6994: l_proc := g_package||'.process_pcp_dpnt_actn_items';
6995: hr_utility.set_location ('Entering '||l_proc,10);
6996: end if;
6997: if g_debug then
6998: hr_utility.set_location ('Entering rslt id '||to_char(p_prtt_enrt_rslt_id),11);
6999: end if;
6994: l_proc := g_package||'.process_pcp_dpnt_actn_items';
6995: hr_utility.set_location ('Entering '||l_proc,10);
6996: end if;
6997: if g_debug then
6998: hr_utility.set_location ('Entering rslt id '||to_char(p_prtt_enrt_rslt_id),11);
6999: end if;
7000: --
7001: --
7002:
7018: fetch c_pcp_dpnt_cd into l_pcp_dpnt_dsgn_cd,l_elig_cvrd_dpnt_id;
7019: if c_pcp_dpnt_cd%notfound then
7020: close c_pcp_dpnt_cd;
7021: if g_debug then
7022: hr_utility.set_location('pcp not required ' , 298);
7023: end if;
7024: return;
7025: else
7026: loop
7024: return;
7025: else
7026: loop
7027: if g_debug then
7028: hr_utility.set_location('l_pcp_dpnt_dsgn_cd '||l_pcp_dpnt_dsgn_cd , 299);
7029: end if;
7030: if g_debug then
7031: hr_utility.set_location('l_elig_cvrd_dpnt_id '||l_elig_cvrd_dpnt_id, 299);
7032: end if;
7027: if g_debug then
7028: hr_utility.set_location('l_pcp_dpnt_dsgn_cd '||l_pcp_dpnt_dsgn_cd , 299);
7029: end if;
7030: if g_debug then
7031: hr_utility.set_location('l_elig_cvrd_dpnt_id '||l_elig_cvrd_dpnt_id, 299);
7032: end if;
7033: determine_pcp_dpnt_actn_items
7034: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
7035: ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
7050: --
7051: end if;
7052: else
7053: if g_debug then
7054: hr_utility.set_location('l_pcp_dpnt_dsgn_cd '||l_pcp_dpnt_dsgn_cd , 399);
7055: end if;
7056: loop
7057: --
7058: determine_pcp_dpnt_actn_items
7081: raise hr_api.validate_enabled;
7082: end if;
7083: --
7084: if g_debug then
7085: hr_utility.set_location ('Leaving '||l_proc, 10);
7086: end if;
7087: --
7088: exception
7089: --
7091: then
7092: rollback to process_pcp_dpnt_actn_items;
7093: --
7094: if g_debug then
7095: hr_utility.set_location(' Leaving:'||l_proc, 10);
7096: end if;
7097: --
7098: when others then
7099: if g_debug then
7096: end if;
7097: --
7098: when others then
7099: if g_debug then
7100: hr_utility.set_location('Exception Raised '||l_proc, 10);
7101: end if;
7102:
7103: p_rslt_object_version_number := l_rslt_object_version_number ;
7104: p_suspend_flag := l_suspend_flag ;
7160: and perf.effective_end_date;
7161: --
7162: begin
7163: --
7164: g_debug := hr_utility.debug_enabled;
7165: if g_debug then
7166: l_proc := g_package||'.process_pcp_actn_items';
7167: hr_utility.set_location ('Entering '||l_proc,10);
7168: end if;
7163: --
7164: g_debug := hr_utility.debug_enabled;
7165: if g_debug then
7166: l_proc := g_package||'.process_pcp_actn_items';
7167: hr_utility.set_location ('Entering '||l_proc,10);
7168: end if;
7169: --
7170: l_rslt_object_version_number := p_rslt_object_version_number ;
7171: l_suspend_flag := p_suspend_flag ;
7185: return;
7186: else
7187: close c_pcp_cd;
7188: if g_debug then
7189: hr_utility.set_location('l_pcp_dsgn_cd '||l_pcp_dsgn_cd , 198);
7190: end if;
7191: if g_debug then
7192: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,198);
7193: end if;
7188: if g_debug then
7189: hr_utility.set_location('l_pcp_dsgn_cd '||l_pcp_dsgn_cd , 198);
7190: end if;
7191: if g_debug then
7192: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,198);
7193: end if;
7194:
7195: determine_pcp_actn_items
7196: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
7205: end if;
7206: else
7207: close c_pcp_cd_oipl;
7208: if g_debug then
7209: hr_utility.set_location('l_pcp_dsgn_cd '||l_pcp_dsgn_cd , 199);
7210: end if;
7211: if g_debug then
7212: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,199);
7213: end if;
7208: if g_debug then
7209: hr_utility.set_location('l_pcp_dsgn_cd '||l_pcp_dsgn_cd , 199);
7210: end if;
7211: if g_debug then
7212: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,199);
7213: end if;
7214:
7215: determine_pcp_actn_items
7216: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
7230: raise hr_api.validate_enabled;
7231: end if;
7232: --
7233: if g_debug then
7234: hr_utility.set_location ('Leaving '||l_proc, 10);
7235: end if;
7236: --
7237: exception
7238: --
7240: then
7241: rollback to process_pcp_actn_items;
7242: --
7243: if g_debug then
7244: hr_utility.set_location(' Leaving:'||l_proc, 10);
7245: end if;
7246: --
7247: when others then
7248: if g_debug then
7245: end if;
7246: --
7247: when others then
7248: if g_debug then
7249: hr_utility.set_location('Exception Raised '||l_proc, 10);
7250: end if;
7251:
7252: p_rslt_object_version_number := l_rslt_object_version_number ;
7253: p_suspend_flag := l_suspend_flag ;
7292: begin
7293: --
7294: if g_debug then
7295: l_proc := g_package||'.check_bnf_ttee';
7296: hr_utility.set_location ('Entering '||l_proc,10);
7297: end if;
7298: --
7299: open c_bnf_age;
7300: fetch c_bnf_age into l_bnf_age;
7300: fetch c_bnf_age into l_bnf_age;
7301: close c_bnf_age;
7302: --
7303: if g_debug then
7304: hr_utility.set_location ('Leaving ' ||l_proc,10);
7305: end if;
7306: --
7307: if (l_bnf_age.bnf_age IS NOT NULL and
7308: l_bnf_age.bnf_age >= 18)
7319: --
7320: exception
7321: when others then
7322: if g_debug then
7323: hr_utility.set_location('Exception Raised '||l_proc, 10);
7324: end if;
7325: raise;
7326: --
7327: end check_bnf_ttee;
7351: l_proc varchar2(80) ;
7352: --
7353: begin
7354: --
7355: g_debug := hr_utility.debug_enabled;
7356: if g_debug then
7357: l_proc := g_package||'.check_bnf_ctfn';
7358: hr_utility.set_location ('Entering '||l_proc,10);
7359: end if;
7354: --
7355: g_debug := hr_utility.debug_enabled;
7356: if g_debug then
7357: l_proc := g_package||'.check_bnf_ctfn';
7358: hr_utility.set_location ('Entering '||l_proc,10);
7359: end if;
7360: --
7361: get_ctfn_count
7362: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
7372: ,p_open_required_count => l_open_required
7373: ,p_open_optional_count => l_open_optional);
7374: --
7375: if g_debug then
7376: hr_utility.set_location ('Leaving '||l_proc,10);
7377: end if;
7378: --
7379: return l_return;
7380: --
7381: exception
7382: --
7383: when others then
7384: if g_debug then
7385: hr_utility.set_location('Exception Raised '||l_proc, 10);
7386: end if;
7387: raise;
7388: --
7389: end check_bnf_ctfn;
7423: begin
7424: --
7425: if g_debug then
7426: l_proc := g_package||'.check_bnf_actn_item_dfnd' ;
7427: hr_utility.set_location ('Entering '||l_proc,10);
7428: end if;
7429: --
7430: open c_bnf_actn_item ;
7431: fetch c_bnf_actn_item into l_dummy;
7442: --
7443: close c_bnf_actn_item;
7444: --
7445: if g_debug then
7446: hr_utility.set_location ('Leaving '||l_proc,10);
7447: end if;
7448: --
7449: return l_exists;
7450: --
7474: begin
7475: --
7476: if g_debug then
7477: l_proc := g_package||'.write_new_bnf_ctfn_item';
7478: hr_utility.set_location ('Entering '||l_proc,10);
7479: end if;
7480: --
7481: -- write a new record for each beneficiary certification item
7482: --
7492: ,p_effective_end_date => l_effective_end_date
7493: ,p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id);
7494: --
7495: if g_debug then
7496: hr_utility.set_location ('Leaving '||l_proc, 10);
7497: end if;
7498: --
7499: exception
7500: /* we could have duplicates already here from prior run */
7499: exception
7500: /* we could have duplicates already here from prior run */
7501: when others then
7502: if g_debug then
7503: hr_utility.set_location('Exception Raised '||l_proc, 10);
7504: end if;
7505:
7506: p_object_version_number := null ;
7507: p_pl_bnf_ctfn_prvdd_id := null;
7699: --
7700:
7701: if g_debug then
7702: l_proc := g_package||'.determine_bnf_miss_actn_items';
7703: hr_utility.set_location ('Entering '||l_proc,10);
7704: end if;
7705: --
7706: l_rslt_object_version_number := p_rslt_object_version_number;
7707: --
7719: --
7720: -- date of birth action item
7721: --
7722: if g_debug then
7723: hr_utility.set_location('DOB action item ' || l_proc, 20);
7724: end if;
7725: --
7726: l_actn_typ_id := get_actn_typ_id
7727: (p_type_cd => 'BNFDOB'
7749: --
7750: if g_bnf_pl_rec.susp_if_bnf_dob_nt_prv_cd is not null or l_bnf_actn_typ_dfnd then --date of birth is required for plan
7751: --
7752: if g_debug then
7753: hr_utility.set_location('DOB rqd flag is Y', 10);
7754: end if;
7755: --
7756: --
7757: -- check if person has a date of birth entry
7791: --
7792: else
7793: --
7794: if g_debug then
7795: hr_utility.set_location('DOB rqd flag is N', 10);
7796: end if;
7797: --
7798: -- bnf_dob_rqd_flag is 'N';
7799: -- if date_track mode is updating and designation at plan level
7819: --
7820: -- legislative code (social security number/national identifier) action item
7821: --
7822: if g_debug then
7823: hr_utility.set_location('SSN action item ' || l_proc, 35);
7824: end if;
7825: --
7826: l_actn_typ_id := get_actn_typ_id
7827: (p_type_cd => 'BNFSSN'
7849: --
7850: if g_bnf_pl_rec.susp_if_bnf_ssn_nt_prv_cd is not null or l_bnf_actn_typ_dfnd then --legislative id is rqd for plan
7851: --
7852: if g_debug then
7853: hr_utility.set_location('SSN rqd flag is Y', 10);
7854: end if;
7855: --
7856: -- check if person has a national identifier entry
7857: --
7887: --
7888: else
7889: --
7890: if g_debug then
7891: hr_utility.set_location('SSN rqd flag is N', 10);
7892: end if;
7893: --
7894: -- bnf_legv_id_rqd_flag is 'N'
7895: -- if date_track mode is updating and designation at plan level
7941: --
7942: if g_bnf_pl_rec.susp_if_bnf_adr_nt_prv_cd is not null or l_bnf_actn_typ_dfnd then -- address is required for plan
7943: --
7944: if g_debug then
7945: hr_utility.set_location('ADDR rqd flag is Y', 10);
7946: end if;
7947: --
7948: -- check if person has an address
7949: --
7980: --
7981: else
7982: --
7983: if g_debug then
7984: hr_utility.set_location('ADDR rqd flag is N', 10);
7985: end if;
7986: --
7987: -- bnf_adrs_rqd_flag is 'N'
7988: -- if date_track mode is updating and designation at plan level
8037: if ( g_bnf_pl_rec.bnf_dsge_mnr_ttee_rqd_flag = 'Y' or l_bnf_actn_typ_dfnd ) and
8038: l_dob_found = TRUE then
8039: --
8040: if g_debug then
8041: hr_utility.set_location('TTEE rqd flag is Y and DOB found', 10);
8042: end if;
8043: --
8044: --
8045: -- check if the designated beneficiary is over age of 18
8072: --
8073: else
8074: --
8075: if g_debug then
8076: hr_utility.set_location('TTEE rqd flag is N or DOB not found', 10);
8077: end if;
8078: --
8079: -- bnf_dsge_mnr_ttee_rqd_flag is 'N'
8080: -- if date_track mode is updating and designation at plan level
8136: --
8137: if g_bnf_pl_rec.bnf_ctfn_rqd_flag = 'N' or l_bnf_actn_typ_dfnd then
8138: --
8139: if g_debug then
8140: hr_utility.set_location('Ctfn rqd flag is N', 10);
8141: end if;
8142: --
8143: -- Certifications are required for the plan.
8144: -- Check if there are any certifications defined for the plan that are
8160: --
8161: if l_ctfn_defined = TRUE then
8162: --
8163: if g_debug then
8164: hr_utility.set_location('Certifications defined', 10);
8165: end if;
8166: --
8167: l_rqd_data_found := check_bnf_ctfn
8168: (p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
8190: --
8191: if l_rqd_data_found = FALSE then
8192: --
8193: if g_debug then
8194: hr_utility.set_location('Ctfn rqmts not met', 10);
8195: end if;
8196: --
8197: -- Ctfn rqmts are not met or no ctfns were found. Check if ctfns
8198: -- exist for the person in ben_pl_bnf_ctfn_prvdd_f.
8202: --
8203: if c_ctfns_exist%notfound then
8204: --
8205: if g_debug then
8206: hr_utility.set_location('Person has no ctfns defined', 10);
8207: end if;
8208: --
8209: -- No ctfns exist for this person. Write new ctfns.
8210: --
8213: --
8214: -- start bug 5667528
8215: l_write_ctfn := TRUE;
8216: l_ff_ctfn_exits := FALSE;
8217: hr_utility.set_location (' l_ctfn_rec.ctrrl ' || l_ctfn_rec.ctrrl,
8218: 12.12
8219: );
8220:
8221: IF l_ctfn_rec.ctrrl IS NOT NULL
8253: p_assignment_id => asg_rec.assignment_id,
8254: p_organization_id => asg_rec.organization_id,
8255: p_effective_date => p_effective_date
8256: );
8257: hr_utility.set_location ( ' formula result '
8258: || l_outputs (l_outputs.FIRST).VALUE,
8259: 12.12
8260: );
8261:
8261:
8262: IF l_outputs (l_outputs.FIRST).VALUE <> 'Y'
8263: THEN
8264: l_write_ctfn := FALSE;
8265: hr_utility.set_location (' setting ctfn to false ', 12.12);
8266: END IF;
8267: END IF; -- end if l_ctfn_rec.ctrrl is not null
8268: IF l_write_ctfn = TRUE THEN
8269: l_ff_ctfn_exits := true;
8266: END IF;
8267: END IF; -- end if l_ctfn_rec.ctrrl is not null
8268: IF l_write_ctfn = TRUE THEN
8269: l_ff_ctfn_exits := true;
8270: hr_utility.set_location(' writing certification ',12.12);
8271:
8272: write_new_bnf_ctfn_item
8273: (p_pl_bnf_id => l_dsgn_bnf.pl_bnf_id
8274: ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
8290: end if; -- rqd_data_found
8291: --
8292: IF l_ff_ctfn_exits = FALSE
8293: THEN
8294: hr_utility.set_location
8295: ('Deleting enrollment action item when formula fails ',
8296: 12.12
8297: );
8298:
8364: --
8365: end loop; -- bnf loop
8366: --
8367: if g_debug then
8368: hr_utility.set_location ('Leaving '||l_proc,10);
8369: end if;
8370: --
8371: exception
8372: when others then
8370: --
8371: exception
8372: when others then
8373: if g_debug then
8374: hr_utility.set_location('Exception Raised '||l_proc, 10);
8375: end if;
8376:
8377: p_rslt_object_version_number :=l_rslt_object_version_number ;
8378: p_bnf_actn_warning := null;
8451: begin
8452: --
8453: if g_debug then
8454: l_proc := g_package||'.determine_bnf_actn_items';
8455: hr_utility.set_location ('Entering '||l_proc,10);
8456: end if;
8457: --
8458: l_rslt_object_version_number := p_rslt_object_version_number;
8459:
8500: --
8501: if l_bnf_found = FALSE then
8502: --
8503: if g_debug then
8504: hr_utility.set_location('Beneficiaries not designated', 10);
8505: end if;
8506: --
8507: --
8508: -- There are no beneficiaries designated
8551: --
8552: -- Beneficiaries found
8553: --
8554: if g_debug then
8555: hr_utility.set_location('Beneficiaries found', 10);
8556: end if;
8557: --
8558: --
8559: -- Set the p_rqd_data_found = TRUE so that the proc below closes an open
8578: --
8579: end if;
8580: --
8581: if g_debug then
8582: hr_utility.set_location ('Leaving ' ||l_proc,10);
8583: end if;
8584: --
8585: exception
8586: --
8595: p_bnf_actn_item_open := TRUE;
8596: p_hack := 'Y';
8597: --
8598: if g_debug then
8599: hr_utility.set_location('Bnf dsgn not specified.', 10);
8600: end if;
8601: if g_debug then
8602: hr_utility.set_location('Leaving : ' || l_proc, 10);
8603: end if;
8598: if g_debug then
8599: hr_utility.set_location('Bnf dsgn not specified.', 10);
8600: end if;
8601: if g_debug then
8602: hr_utility.set_location('Leaving : ' || l_proc, 10);
8603: end if;
8604: --
8605: when others then
8606: if g_debug then
8603: end if;
8604: --
8605: when others then
8606: if g_debug then
8607: hr_utility.set_location('Exception Raised '||l_proc, 10);
8608: end if;
8609:
8610: p_rslt_object_version_number :=l_rslt_object_version_number ;
8611: p_bnf_actn_item_open := null;
8826: begin
8827: --
8828: if g_debug then
8829: l_proc := g_package||'.determine_additional_bnf';
8830: hr_utility.set_location ('Entering '||l_proc,10);
8831: end if;
8832: --
8833:
8834: l_rslt_object_version_number := p_rslt_object_version_number;
8891: --
8892: if l_meets_rqmt = TRUE then
8893: --
8894: if g_debug then
8895: hr_utility.set_location('rqmts met for BNFADDNL', 10);
8896: end if;
8897: --
8898: -- Set the p_rqd_data_found = TRUE so that the procedure closes an open
8899: -- action item of type BNFADDNL, if found.
8914: --
8915: else
8916: --
8917: if g_debug then
8918: hr_utility.set_location('rqmts not met for BNFADDNL', 10);
8919: end if;
8920:
8921: --
8922: -- Requirements are not met. Set the p_rqd_data_found = FALSE so that the
8954: --
8955: -- The primary bnf is not a spouse.
8956: --
8957: if g_debug then
8958: hr_utility.set_location('Contact type ' || l_bnf_rec.contact_type ||
8959: ' is not a spouse', 10);
8960: end if;
8961: --
8962: -- Check if an BNFSCCTFN actn item already exists for the bnf.
8989: --
8990: -- no BNFSCCTFN action item yet so create a new entry
8991: --
8992: if g_debug then
8993: hr_utility.set_location('No BNFSCCTFN actn item.', 10);
8994: end if;
8995: --
8996: write_new_action_item
8997: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
9056: -- A BNFSCCTFN action item was written in an earlier run. Check if the
9057: -- NSC ctfn has been provided and if yes, close the action item.
9058: --
9059: if g_debug then
9060: hr_utility.set_location('BNFSCCTFN actn item found', 10);
9061: end if;
9062: --
9063: open c_nsc_prvdd(l_bnf_rec.pl_bnf_id);
9064: fetch c_nsc_prvdd into l_dummy;
9067: --
9068: -- Ctfn provided. Close action item.
9069: --
9070: if g_debug then
9071: hr_utility.set_location('SCCTFN provided. Close actn item.', 10);
9072: end if;
9073: --
9074: set_cmpltd_dt
9075: (p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
9088: --
9089: -- Ctfn not provided. Open action item if already closed.
9090: --
9091: if g_debug then
9092: hr_utility.set_location('CTFN not prvdd. Open actn item.', 10);
9093: end if;
9094: --
9095: if l_cmpltd_dt is null then
9096: set_cmpltd_dt
9118: --
9119: end loop; -- outer loop
9120: --
9121: if g_debug then
9122: hr_utility.set_location ('Leaving ' ||l_proc,10);
9123: end if;
9124: --
9125: exception
9126: when others then
9124: --
9125: exception
9126: when others then
9127: if g_debug then
9128: hr_utility.set_location('Exception Raised '||l_proc, 10);
9129: end if;
9130: p_rslt_object_version_number :=l_rslt_object_version_number ;
9131: p_bnf_actn_warning :=l_bnf_actn_warning ;
9132: raise;
9159:
9160: --
9161: begin
9162: --
9163: g_debug := hr_utility.debug_enabled;
9164: if g_debug then
9165: l_proc := g_package || '.process_bnf_actn_items';
9166: hr_utility.set_location ('Entering '||l_proc,10);
9167: end if;
9162: --
9163: g_debug := hr_utility.debug_enabled;
9164: if g_debug then
9165: l_proc := g_package || '.process_bnf_actn_items';
9166: hr_utility.set_location ('Entering '||l_proc,10);
9167: end if;
9168: --
9169:
9170: l_rslt_object_version_number:= p_rslt_object_version_number ;
9233: raise hr_api.validate_enabled;
9234: end if;
9235: --
9236: if g_debug then
9237: hr_utility.set_location ('Leaving '||l_proc, 10);
9238: end if;
9239: --
9240: exception
9241: --
9243: then
9244: --
9245: rollback to process_bnf_actn_items;
9246: if g_debug then
9247: hr_utility.set_location ('Leaving '||l_proc, 10);
9248: end if;
9249: --
9250: when others then
9251: if g_debug then
9248: end if;
9249: --
9250: when others then
9251: if g_debug then
9252: hr_utility.set_location('Exception Raised '||l_proc, 10);
9253: end if;
9254:
9255: p_rslt_object_version_number :=l_rslt_object_version_number ;
9256: p_suspend_flag :=l_suspend_flag ;
9377: l_interim boolean := false ;
9378: --
9379: begin
9380: --
9381: g_debug := hr_utility.debug_enabled;
9382: if g_debug then
9383: l_proc := g_package||'.determine_action_items';
9384: hr_utility.set_location ('Entering '||l_proc,10);
9385: end if;
9380: --
9381: g_debug := hr_utility.debug_enabled;
9382: if g_debug then
9383: l_proc := g_package||'.determine_action_items';
9384: hr_utility.set_location ('Entering '||l_proc,10);
9385: end if;
9386: --
9387: l_rslt_object_version_number :=p_rslt_object_version_number ;
9388:
9431: fetch c_waive_opt_flag into l_waive_flag;
9432: close c_waive_opt_flag;
9433:
9434: if g_debug then
9435: hr_utility.set_location ('waive_flag = '||l_waive_flag,9);
9436: end if;
9437:
9438: --
9439: -- un comment the following code 3771346 /*
9449: --end 3771346 */
9450: end if;
9451:
9452: if g_debug then
9453: hr_utility.set_location ('waive_flag = '||l_waive_flag,10);
9454: end if;
9455: --BUG 3042379 We don't need to process the action items for the
9456: --interim enrollment.
9457:
9559: --
9560: -- BUG 3042379 Don't create action items for the interim
9561: else
9562: if g_debug then
9563: hr_utility.set_location ('Interim Enrt- No action item created PEN '||p_prtt_enrt_rslt_id,110);
9564: end if;
9565: end if ;
9566: -- BUG 3042379 Don't create action items for the interim
9567: --
9613: --
9614: p_suspend_flag := l_suspend_flag;
9615: --
9616: if g_debug then
9617: hr_utility.set_location ('Leaving ' ||l_proc,10);
9618: end if;
9619: --
9620: exception
9621: --
9626: ROLLBACK TO determine_action_items;
9627: --
9628: when others then
9629: if g_debug then
9630: hr_utility.set_location('Exception Raised '||l_proc, 10);
9631: end if;
9632: p_rslt_object_version_number :=l_rslt_object_version_number ;
9633: p_suspend_flag :=l_suspend_flag ;
9634: p_ctfn_actn_warning := null ;