[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.
364: */
365: --
366: -- Package Variables
367: --
368: g_debug boolean := hr_utility.debug_enabled;
369: --
370: -- -----------------------------------------------------------------------------
371: -- |-----------------------< check_ctfns_defined >-----------------------------|
372: -- -----------------------------------------------------------------------------
496: begin
497: --
498: if g_debug then
499: l_proc := g_package || '.check_ctfns_defined';
500: hr_utility.set_location('Entering ' || l_proc, 10);
501: end if;
502: if g_debug then
503: hr_utility.set_location('Dpnt dsgn level code : ' || p_lvl_cd, 10);
504: end if;
499: l_proc := g_package || '.check_ctfns_defined';
500: hr_utility.set_location('Entering ' || l_proc, 10);
501: end if;
502: if g_debug then
503: hr_utility.set_location('Dpnt dsgn level code : ' || p_lvl_cd, 10);
504: end if;
505: --
506: l_susp_if_ctfn_not_prvd_flag := null;
507: --
579: p_susp_if_ctfn_not_prvd_flag := l_susp_if_ctfn_not_prvd_flag;
580: --
581: if l_return then
582: if g_debug then
583: hr_utility.set_location('CTFNS are defined', 99);
584: end if;
585: else
586: if g_debug then
587: hr_utility.set_location('CTFNS not defined', 99);
583: hr_utility.set_location('CTFNS are defined', 99);
584: end if;
585: else
586: if g_debug then
587: hr_utility.set_location('CTFNS not defined', 99);
588: end if;
589: end if;
590: --
591: if g_debug then
588: end if;
589: end if;
590: --
591: if g_debug then
592: hr_utility.set_location('Leaving ' || l_proc, 99);
593: end if;
594: --
595: return l_return;
596: --
672: --
673: begin
674: --
675: if g_debug then
676: hr_utility.set_location ('Entering '||l_proc,10);
677: end if;
678: --
679: if p_actn_typ_id is null then
680: --
728: --End Bug 4610971
729: end if;
730: --
731: if g_debug then
732: hr_utility.set_location ('Leaving '||l_proc,10);
733: end if;
734: --
735: return l_returned_date;
736: --
738: --
739: when pat_not_found then
740: --
741: if g_debug then
742: hr_utility.set_location('Leaving : ' || l_proc, 10);
743: end if;
744: l_returned_date := NULL;
745: return l_returned_date;
746: --
745: return l_returned_date;
746: --
747: when others then
748: if g_debug then
749: hr_utility.set_location('Exception Raised '||l_proc, 10);
750: end if;
751: raise;
752: --
753: end get_due_date;
776: and bat.business_group_id = p_business_group_id;
777: --
778: begin
779: --
780: g_debug := hr_utility.debug_enabled;
781: if g_debug then
782: l_proc := g_package||'.get_actn_typ_id';
783: hr_utility.set_location ('Entering '||l_proc,10);
784: end if;
779: --
780: g_debug := hr_utility.debug_enabled;
781: if g_debug then
782: l_proc := g_package||'.get_actn_typ_id';
783: hr_utility.set_location ('Entering '||l_proc,10);
784: end if;
785: --
786: open c_actn_typ_id;
787: fetch c_actn_typ_id into l_actn_typ_id;
792: --
793: close c_actn_typ_id;
794: --
795: if g_debug then
796: hr_utility.set_location ('Leaving '||l_proc, 10);
797: end if;
798: --
799: return l_actn_typ_id;
800: --
800: --
801: exception
802: when others then
803: if g_debug then
804: hr_utility.set_location('Exception Raised '||l_proc, 10);
805: end if;
806: raise;
807: --
808: end get_actn_typ_id;
895:
896: --
897: begin
898: --
899: g_debug := hr_utility.debug_enabled;
900: if g_debug then
901: l_proc := g_package||'.get_prtt_enrt_actn_id';
902: hr_utility.set_location ('Entering '||l_proc,10);
903: end if;
898: --
899: g_debug := hr_utility.debug_enabled;
900: if g_debug then
901: l_proc := g_package||'.get_prtt_enrt_actn_id';
902: hr_utility.set_location ('Entering '||l_proc,10);
903: end if;
904: l_object_version_number := p_object_version_number ;
905:
906: hr_utility.set_location ('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id,10);
902: hr_utility.set_location ('Entering '||l_proc,10);
903: end if;
904: l_object_version_number := p_object_version_number ;
905:
906: hr_utility.set_location ('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id,10);
907: hr_utility.set_location ('p_elig_cvrd_dpnt_id '|| p_elig_cvrd_dpnt_id,10);
908: hr_utility.set_location ('p_pl_bnf_id '|| p_pl_bnf_id,10);
909:
910: --
903: end if;
904: l_object_version_number := p_object_version_number ;
905:
906: hr_utility.set_location ('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id,10);
907: hr_utility.set_location ('p_elig_cvrd_dpnt_id '|| p_elig_cvrd_dpnt_id,10);
908: hr_utility.set_location ('p_pl_bnf_id '|| p_pl_bnf_id,10);
909:
910: --
911: if p_prtt_enrt_rslt_id is not null then
904: l_object_version_number := p_object_version_number ;
905:
906: hr_utility.set_location ('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id,10);
907: hr_utility.set_location ('p_elig_cvrd_dpnt_id '|| p_elig_cvrd_dpnt_id,10);
908: hr_utility.set_location ('p_pl_bnf_id '|| p_pl_bnf_id,10);
909:
910: --
911: if p_prtt_enrt_rslt_id is not null then
912: --
929: end if;
930: --
931: close c_enrt_actn;
932: --
933: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,11);
934: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,11);
935:
936:
937:
930: --
931: close c_enrt_actn;
932: --
933: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,11);
934: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,11);
935:
936:
937:
938: elsif p_elig_cvrd_dpnt_id is not null and
953: end if;
954: --
955: close c_enrt_actn_dpnt;
956: --
957: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,12);
958: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,12);
959:
960: elsif p_elig_cvrd_dpnt_id is null and
961: p_pl_bnf_id is not null then
954: --
955: close c_enrt_actn_dpnt;
956: --
957: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,12);
958: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,12);
959:
960: elsif p_elig_cvrd_dpnt_id is null and
961: p_pl_bnf_id is not null then
962: --
975: end if;
976: --
977: close c_enrt_actn_bnf;
978: --
979: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,13);
980: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,13);
981:
982: end if;
983: --
976: --
977: close c_enrt_actn_bnf;
978: --
979: hr_utility.set_location ('p_prtt_enrt_actn_id '|| p_prtt_enrt_actn_id,13);
980: hr_utility.set_location ('p_cmpltd_dt '|| p_cmpltd_dt,13);
981:
982: end if;
983: --
984: else
989: p_object_version_number := NULL;
990: end if;
991: --
992: if g_debug then
993: hr_utility.set_location ('Leaving '||l_proc, 10);
994: end if;
995: --
996: exception
997: when others then
1001: p_cmpltd_dt :=null ;
1002: p_object_version_number := l_object_version_number ;
1003: --
1004: if g_debug then
1005: hr_utility.set_location('Exception Raised '||l_proc, 10);
1006: end if;
1007: raise;
1008: --
1009: end get_prtt_enrt_actn_id;
1025: begin
1026: --
1027: if g_debug then
1028: l_proc := g_package||'.date_track_verify';
1029: hr_utility.set_location ('Entering '||l_proc,10);
1030: end if;
1031: --
1032: -- Date track settings need some massaging right now.
1033: -- This routine is used to set the rules for mode settings depending
1067: l_datetrack_mode := hr_api.g_correction;
1068: end if;
1069: --
1070: if g_debug then
1071: hr_utility.set_location ('Leaving '||l_proc, 10);
1072: end if;
1073: --
1074: return l_datetrack_mode;
1075: --
1075: --
1076: exception
1077: when others then
1078: if g_debug then
1079: hr_utility.set_location('Exception Raised '||l_proc, 10);
1080: end if;
1081: raise;
1082: --
1083: end date_track_verify;
1136:
1137: --
1138: begin
1139: --
1140: g_debug := hr_utility.debug_enabled;
1141: if g_debug then
1142: l_proc := g_package||'.write_new_action_item';
1143: hr_utility.set_location ('Entering '||l_proc,10);
1144: end if;
1139: --
1140: g_debug := hr_utility.debug_enabled;
1141: if g_debug then
1142: l_proc := g_package||'.write_new_action_item';
1143: hr_utility.set_location ('Entering '||l_proc,10);
1144: end if;
1145: l_rslt_object_version_number := p_rslt_object_version_number ;
1146: --
1147: -- Get the due date for the action item
1191: --
1192: end if;
1193: --
1194: if g_debug then
1195: hr_utility.set_location ('Leaving '||l_proc, 10);
1196: end if;
1197: --
1198: exception
1199: when others then
1204: p_object_version_number :=null;
1205: p_prtt_enrt_actn_id :=null;
1206:
1207: if g_debug then
1208: hr_utility.set_location('Exception Raised '||l_proc, 10);
1209: end if;
1210: raise;
1211: --
1212: end write_new_action_item;
1235: begin
1236: --
1237: if g_debug then
1238: l_proc := g_package||'.write_new_dpnt_ctfn_item';
1239: hr_utility.set_location ('Entering '||l_proc,10);
1240: end if;
1241: --
1242: -- write a new record for a dependent certification item
1243: --
1257: ,p_request_id => fnd_global.conc_request_id
1258: ,p_program_update_date => sysdate);
1259: --
1260: if g_debug then
1261: hr_utility.set_location ('Leaving '||l_proc, 10);
1262: end if;
1263: --
1264: exception
1265: /* we could have duplicates already here from prior run
1269: -- Init Variables for NOCOPY
1270: p_object_version_number :=null ;
1271: p_cvrd_dpnt_ctfn_prvdd_id :=null ;
1272: if g_debug then
1273: hr_utility.set_location('Exception Raised '||l_proc, 10);
1274: end if;
1275: raise;
1276: --
1277: end write_new_dpnt_ctfn_item;
1322: begin
1323: --
1324: if g_debug then
1325: l_proc := g_package||'.write_new_prtt_ctfn_prvdd_item';
1326: hr_utility.set_location ('Entering '||l_proc,10);
1327: end if;
1328: --
1329: -- write a new record for a participant certification item
1330: --
1347: ,p_effective_end_date => l_effective_end_date
1348: ,p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
1349: );
1350: else
1351: hr_utility.set_location ('Already certification exists. No need to create new one', 10);
1352: end if;
1353: --
1354: if g_debug then
1355: hr_utility.set_location ('Leaving '||l_proc, 10);
1351: hr_utility.set_location ('Already certification exists. No need to create new one', 10);
1352: end if;
1353: --
1354: if g_debug then
1355: hr_utility.set_location ('Leaving '||l_proc, 10);
1356: end if;
1357: --
1358: exception
1359: when others then
1362: p_object_version_number := null ;
1363: p_prtt_enrt_ctfn_prvdd_id:= null;
1364:
1365: if g_debug then
1366: hr_utility.set_location('Exception Raised '||l_proc, 10);
1367: end if;
1368: raise;
1369: --
1370: end write_new_prtt_ctfn_prvdd_item;
1469: begin
1470: --
1471: if g_debug then
1472: l_proc := g_package||'.set_cmpltd_dt';
1473: hr_utility.set_location ('Entering '||l_proc,10);
1474: hr_utility.set_location('p_prtt_enrt_actn_id '||p_prtt_enrt_actn_id,119);
1475: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,119);
1476: --
1477: end if;
1470: --
1471: if g_debug then
1472: l_proc := g_package||'.set_cmpltd_dt';
1473: hr_utility.set_location ('Entering '||l_proc,10);
1474: hr_utility.set_location('p_prtt_enrt_actn_id '||p_prtt_enrt_actn_id,119);
1475: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,119);
1476: --
1477: end if;
1478: --
1471: if g_debug then
1472: l_proc := g_package||'.set_cmpltd_dt';
1473: hr_utility.set_location ('Entering '||l_proc,10);
1474: hr_utility.set_location('p_prtt_enrt_actn_id '||p_prtt_enrt_actn_id,119);
1475: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,119);
1476: --
1477: end if;
1478: --
1479:
1501: end if;
1502: --
1503: l_cmpltd_dt := l_prvdd_dt ;
1504: --
1505: hr_utility.set_location('FIRST l_cmpltd_dt '||l_cmpltd_dt,119);
1506: --
1507: open c_ccp(p_prtt_enrt_actn_id);
1508: fetch c_ccp into l_prvdd_dt ;
1509: close c_ccp ;
1511: if l_prvdd_dt > nvl(l_cmpltd_dt,l_prvdd_dt-1) then
1512: l_cmpltd_dt := l_prvdd_dt ;
1513: end if;
1514: --
1515: hr_utility.set_location('SECOND l_cmpltd_dt '||l_cmpltd_dt,119);
1516: --
1517: open c_pbc(p_prtt_enrt_actn_id);
1518: fetch c_pbc into l_prvdd_dt ;
1519: close c_pbc ;
1521: if l_prvdd_dt > nvl(l_cmpltd_dt,l_prvdd_dt-1) then
1522: l_cmpltd_dt := l_prvdd_dt ;
1523: end if;
1524: --
1525: hr_utility.set_location(' p_effective_date '||p_effective_date,119);
1526: hr_utility.set_location(' l_cmpltd_dt '||l_cmpltd_dt,119);
1527: l_cmpltd_dt := nvl(l_cmpltd_dt,p_effective_date) ;
1528: --
1529: end if;
1522: l_cmpltd_dt := l_prvdd_dt ;
1523: end if;
1524: --
1525: hr_utility.set_location(' p_effective_date '||p_effective_date,119);
1526: hr_utility.set_location(' l_cmpltd_dt '||l_cmpltd_dt,119);
1527: l_cmpltd_dt := nvl(l_cmpltd_dt,p_effective_date) ;
1528: --
1529: end if;
1530: --
1564: -- bug 6010780
1565: if l_object_version_number2 is not null then
1566: l_datetrack_mode2 := 'FUTURE_CHANGE';
1567:
1568: hr_utility.set_location(' Future change exists fo update or correction ', 121 );
1569: hr_utility.set_location(' p_object_version_number 1 ' || p_object_version_number , 121 );
1570:
1571: ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
1572: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
1565: if l_object_version_number2 is not null then
1566: l_datetrack_mode2 := 'FUTURE_CHANGE';
1567:
1568: hr_utility.set_location(' Future change exists fo update or correction ', 121 );
1569: hr_utility.set_location(' p_object_version_number 1 ' || p_object_version_number , 121 );
1570:
1571: ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
1572: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
1573: p_business_group_id => p_business_group_id,
1580: p_effective_start_date => l_effective_start_date,
1581: p_effective_end_date => l_effective_end_date
1582: );
1583: end if;
1584: hr_utility.set_location(' p_object_version_number 2 ' || p_object_version_number , 121 );
1585: open c_curr_ovn_of_actn(p_prtt_enrt_actn_id);
1586: fetch c_curr_ovn_of_actn into curr_ovn;
1587: close c_curr_ovn_of_actn;
1588: hr_utility.set_location('curr_ovn ' || curr_ovn , 121 );
1584: hr_utility.set_location(' p_object_version_number 2 ' || p_object_version_number , 121 );
1585: open c_curr_ovn_of_actn(p_prtt_enrt_actn_id);
1586: fetch c_curr_ovn_of_actn into curr_ovn;
1587: close c_curr_ovn_of_actn;
1588: hr_utility.set_location('curr_ovn ' || curr_ovn , 121 );
1589: if curr_ovn is not null then
1590: ben_prtt_enrt_actn_api.update_prtt_enrt_actn
1591: (p_cmpltd_dt => l_cmpltd_dt
1592: ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
1606: end if;
1607: end if;
1608: --
1609: if g_debug then
1610: hr_utility.set_location ('Leaving '||l_proc, 10);
1611: end if;
1612: --
1613: exception
1614: when others then
1613: exception
1614: when others then
1615:
1616: if g_debug then
1617: hr_utility.set_location('Exception Raised '||l_proc, 10);
1618: end if;
1619: -- Init Variables for NOCOPY
1620: p_rslt_object_version_number := l_rslt_object_version_number ;
1621: p_object_version_number := l_object_version_number ;
1661: ;
1662: --
1663: begin
1664: --
1665: g_debug := hr_utility.debug_enabled;
1666: if g_debug then
1667: l_proc := g_package||'.complete_this_action_item';
1668: hr_utility.set_location ('Entering '||l_proc,10);
1669: end if;
1664: --
1665: g_debug := hr_utility.debug_enabled;
1666: if g_debug then
1667: l_proc := g_package||'.complete_this_action_item';
1668: hr_utility.set_location ('Entering '||l_proc,10);
1669: end if;
1670: --
1671: -- Using table api update the record for an action item
1672: --
1686: --
1687: end loop;
1688: --
1689: if g_debug then
1690: hr_utility.set_location ('Leaving '||l_proc, 10);
1691: end if;
1692: --
1693: exception
1694: when others then
1692: --
1693: exception
1694: when others then
1695: if g_debug then
1696: hr_utility.set_location('Exception Raised '||l_proc, 10);
1697: end if;
1698: raise;
1699: --
1700: end complete_this_action_item;
1721: begin
1722: --
1723: if g_debug then
1724: l_proc := g_package||'.delete_prtt_ctfn_prvdd';
1725: hr_utility.set_location ('Entering '||l_proc,10);
1726: end if;
1727: --
1728: l_object_version_number := p_object_version_number ;
1729:
1737: ,p_effective_date => p_effective_date
1738: ,p_datetrack_mode => p_datetrack_mode);
1739: --
1740: if g_debug then
1741: hr_utility.set_location ('Leaving '||l_proc, 10);
1742: end if;
1743: --
1744: exception
1745: when others then
1744: exception
1745: when others then
1746:
1747: if g_debug then
1748: hr_utility.set_location('Exception Raised '||l_proc, 10);
1749: end if;
1750: p_object_version_number := l_object_version_number ;
1751:
1752: raise;
1776: begin
1777: --
1778: if g_debug then
1779: l_proc := g_package||'.delete_dpnt_ctfn_prvdd';
1780: hr_utility.set_location ('Entering '||l_proc,10);
1781: end if;
1782: --
1783: l_object_version_number := p_object_version_number ;
1784:
1794: ,p_datetrack_mode => p_datetrack_mode
1795: ,p_called_from => 'benactcm' );
1796: --
1797: if g_debug then
1798: hr_utility.set_location ('Leaving '||l_proc, 10);
1799: end if;
1800: --
1801: exception
1802: when others then
1800: --
1801: exception
1802: when others then
1803: if g_debug then
1804: hr_utility.set_location('Exception Raised '||l_proc, 10);
1805: end if;
1806: p_object_version_number := l_object_version_number ;
1807: raise;
1808: --
1847: begin
1848: --
1849: if g_debug then
1850: l_proc := g_package||'.remove_prtt_certifications';
1851: hr_utility.set_location ('Entering '||l_proc,10);
1852: end if;
1853: --
1854: -- Using table api write a new record for an action item
1855: --
1863: --
1864: end loop;
1865: --
1866: if g_debug then
1867: hr_utility.set_location ('Leaving '||l_proc, 10);
1868: end if;
1869: --
1870: exception
1871: when others then
1869: --
1870: exception
1871: when others then
1872: if g_debug then
1873: hr_utility.set_location('Exception Raised '||l_proc, 10);
1874: end if;
1875: raise;
1876: --
1877: end remove_prtt_certifications;
1918: begin
1919: --
1920: if g_debug then
1921: l_proc := g_package||'.remove_dpnt_certifications';
1922: hr_utility.set_location ('Entering '||l_proc,10);
1923: end if;
1924: --
1925: for l_dpnt_ctfn_prvdd in c_dpnt_ctfn_prvdd loop
1926: --
1933: --
1934: end loop;
1935: --
1936: if g_debug then
1937: hr_utility.set_location ('Leaving '||l_proc, 10);
1938: end if;
1939: --
1940: exception
1941: when others then
1942: p_effective_start_date := null;
1943: p_effective_end_date := null;
1944:
1945: if g_debug then
1946: hr_utility.set_location('Exception Raised '||l_proc, 10);
1947: end if;
1948: raise;
1949: --
1950: end remove_dpnt_certifications;
1973: begin
1974: --
1975: if g_debug then
1976: l_proc := g_package||'.delete_action_item';
1977: hr_utility.set_location ('Entering '||l_proc,10);
1978: end if;
1979: if g_debug then
1980: hr_utility.set_location(p_datetrack_mode || ' ' || l_proc, 10);
1981: end if;
1976: l_proc := g_package||'.delete_action_item';
1977: hr_utility.set_location ('Entering '||l_proc,10);
1978: end if;
1979: if g_debug then
1980: hr_utility.set_location(p_datetrack_mode || ' ' || l_proc, 10);
1981: end if;
1982: --
1983: l_object_version_number := p_object_version_number;
1984: l_rslt_object_version_number := p_rslt_object_version_number ;
1997: ,p_effective_start_date => l_effective_start_date
1998: ,p_effective_end_date => l_effective_end_date);
1999: --
2000: if g_debug then
2001: hr_utility.set_location ('Leaving '||l_proc, 10);
2002: end if;
2003: --
2004: exception
2005: when others then
2003: --
2004: exception
2005: when others then
2006: if g_debug then
2007: hr_utility.set_location('Exception Raised '||l_proc, 10);
2008: end if;
2009: p_object_version_number := l_object_version_number ;
2010: p_rslt_object_version_number := l_rslt_object_version_number ;
2011: raise;
2067: begin
2068: --
2069: if g_debug then
2070: l_proc := g_package || '.get_ctfn_count';
2071: hr_utility.set_location ('Entering '||l_proc,10);
2072: end if;
2073: --
2074: p_optional_count := 0;
2075: p_required_count := 0;
2077: p_open_optional_count := 0;
2078: --
2079: if p_prtt_enrt_actn_id is null then
2080: if g_debug then
2081: hr_utility.set_location ('Leaving '||l_proc,97);
2082: end if;
2083: return;
2084: end if;
2085: --
2102: close c_pcs;
2103: --
2104: else
2105: if g_debug then
2106: hr_utility.set_location ('Leaving '||l_proc,98);
2107: end if;
2108: return;
2109: --
2110: end if;
2113: p_optional_count := l_ctfn.tot_ctfn - l_ctfn.tot_rqd + 0;
2114: p_open_required_count := l_ctfn.tot_open_rqd + 0;
2115: p_open_optional_count := l_ctfn.tot_open_ctfn - p_open_required_count +0;
2116:
2117: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2118: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2119: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2120: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2121: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2114: p_open_required_count := l_ctfn.tot_open_rqd + 0;
2115: p_open_optional_count := l_ctfn.tot_open_ctfn - p_open_required_count +0;
2116:
2117: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2118: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2119: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2120: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2121: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2122:
2115: p_open_optional_count := l_ctfn.tot_open_ctfn - p_open_required_count +0;
2116:
2117: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2118: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2119: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2120: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2121: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2122:
2123: --
2116:
2117: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2118: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2119: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2120: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2121: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2122:
2123: --
2124: if g_debug then
2117: hr_utility.set_location ('l_ctfn.tot_rqd '|| l_ctfn.tot_rqd,10);
2118: hr_utility.set_location ('l_ctfn.tot_ctfn '|| l_ctfn.tot_ctfn,10);
2119: hr_utility.set_location ('l_ctfn.tot_open_rqd '|| l_ctfn.tot_open_rqd,10);
2120: hr_utility.set_location ('l_ctfn.tot_open_ctfn '|| l_ctfn.tot_open_ctfn,10);
2121: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2122:
2123: --
2124: if g_debug then
2125: hr_utility.set_location ('Leaving '||l_proc,99);
2121: hr_utility.set_location ('p_open_required_count '|| p_open_required_count,10);
2122:
2123: --
2124: if g_debug then
2125: hr_utility.set_location ('Leaving '||l_proc,99);
2126: end if;
2127: --
2128: end get_ctfn_count;
2129: --
2157:
2158: --
2159: begin
2160: --
2161: g_debug := hr_utility.debug_enabled;
2162: if g_debug then
2163: l_proc := g_package||'.process_action_item';
2164: hr_utility.set_location ('Entering '||l_proc,10);
2165: end if;
2160: --
2161: g_debug := hr_utility.debug_enabled;
2162: if g_debug then
2163: l_proc := g_package||'.process_action_item';
2164: hr_utility.set_location ('Entering '||l_proc,10);
2165: end if;
2166: --
2167: l_prtt_enrt_actn_id := p_prtt_enrt_actn_id ;
2168: l_object_version_number := p_object_version_number ;
2239: NULL;
2240: end if;
2241: --
2242: if g_debug then
2243: hr_utility.set_location ('Leaving '||l_proc, 10);
2244: end if;
2245: --
2246: exception
2247: when others then
2246: exception
2247: when others then
2248:
2249: if g_debug then
2250: hr_utility.set_location('Exception Raised '||l_proc, 10);
2251: end if;
2252:
2253: p_prtt_enrt_actn_id := l_prtt_enrt_actn_id ;
2254: p_object_version_number := l_object_version_number ;
2370:
2371: --
2372: begin
2373: --
2374: g_debug := hr_utility.debug_enabled;
2375: if g_debug then
2376: l_proc := g_package||'.process_ctfn_action_item';
2377: hr_utility.set_location ('Entering '||l_proc, 10);
2378: end if;
2373: --
2374: g_debug := hr_utility.debug_enabled;
2375: if g_debug then
2376: l_proc := g_package||'.process_ctfn_action_item';
2377: hr_utility.set_location ('Entering '||l_proc, 10);
2378: end if;
2379: --
2380: p_prtt_enrt_actn_id := null;
2381: --
2426: elsif p_ctfn_rqd_flag = 'N' and p_ctfn_recd_dt is null then
2427: --
2428: -- Case 3: Optional Open Ctfn.
2429: --
2430: hr_utility.set_location ('p_ctfn_rqd_flag '|| p_ctfn_rqd_flag,10);
2431: hr_utility.set_location ('p_ctfn_recd_dt '|| p_ctfn_recd_dt,10);
2432:
2433: get_ctfn_count
2434: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2427: --
2428: -- Case 3: Optional Open Ctfn.
2429: --
2430: hr_utility.set_location ('p_ctfn_rqd_flag '|| p_ctfn_rqd_flag,10);
2431: hr_utility.set_location ('p_ctfn_recd_dt '|| p_ctfn_recd_dt,10);
2432:
2433: get_ctfn_count
2434: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2435: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2460: elsif p_ctfn_rqd_flag = 'N' and p_ctfn_recd_dt is not null then
2461: --
2462: -- Case 4: Optional Closed Ctfn.
2463: --
2464: hr_utility.set_location ('p_ctfn_rqd_flag '|| p_ctfn_rqd_flag,11);
2465: hr_utility.set_location ('p_ctfn_recd_dt '|| p_ctfn_recd_dt,11);
2466:
2467: get_ctfn_count
2468: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2461: --
2462: -- Case 4: Optional Closed Ctfn.
2463: --
2464: hr_utility.set_location ('p_ctfn_rqd_flag '|| p_ctfn_rqd_flag,11);
2465: hr_utility.set_location ('p_ctfn_recd_dt '|| p_ctfn_recd_dt,11);
2466:
2467: get_ctfn_count
2468: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2469: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2530: --
2531:
2532: IF g_debug
2533: THEN
2534: hr_utility.set_location ('Designation level code : ' || l_lvl_cd,14 );
2535: END IF;
2536:
2537: --
2538: -- check the level code for program, ptip or plan (default) and fetch the
2575: END IF;
2576:
2577: IF g_debug
2578: THEN
2579: hr_utility.set_location ('l_rqd_flag : ' || l_rqd_flag, 14);
2580: END IF;
2581: --End Bug 6353069
2582: --
2583: process_action_item
2596: p_datetrack_mode => hr_api.g_update,
2597: p_rslt_object_version_number => l_rslt.object_version_number);
2598: --
2599: if g_debug then
2600: hr_utility.set_location ('Leaving '||l_proc, 10);
2601: end if;
2602: --
2603: end process_new_ctfn_action;
2604: --
2626: l_date_of_birth date;
2627: --
2628: begin
2629: --
2630: g_debug := hr_utility.debug_enabled;
2631: if g_debug then
2632: l_proc := g_package||'.check_dob';
2633: hr_utility.set_location ('Entering '||l_proc,10);
2634: end if;
2629: --
2630: g_debug := hr_utility.debug_enabled;
2631: if g_debug then
2632: l_proc := g_package||'.check_dob';
2633: hr_utility.set_location ('Entering '||l_proc,10);
2634: end if;
2635: --
2636: open c_date_of_birth;
2637: fetch c_date_of_birth into l_date_of_birth;
2643: l_dob_found := FALSE;
2644: end if;
2645: --
2646: if g_debug then
2647: hr_utility.set_location ('Leaving ' ||l_proc,10);
2648: end if;
2649: --
2650: return l_dob_found;
2651: --
2651: --
2652: exception
2653: when others then
2654: if g_debug then
2655: hr_utility.set_location('Exception Raised '||l_proc, 10);
2656: end if;
2657: raise;
2658: --
2659: end check_dob;
2737: l_dpnt_bnf_adrs c_dpnt_bnf_adrs%rowtype;
2738: --
2739: begin
2740: --
2741: g_debug := hr_utility.debug_enabled;
2742: if g_debug then
2743: l_proc := g_package||'.check_adrs';
2744: hr_utility.set_location ('Entering '||l_proc,10);
2745: end if;
2740: --
2741: g_debug := hr_utility.debug_enabled;
2742: if g_debug then
2743: l_proc := g_package||'.check_adrs';
2744: hr_utility.set_location ('Entering '||l_proc,10);
2745: end if;
2746: --
2747: -- check if the person resides with the participant
2748: --
2785: --
2786: end if; -- l_rsds
2787: --
2788: if g_debug then
2789: hr_utility.set_location ('Leaving ' ||l_proc,10);
2790: end if;
2791: --
2792: return l_valid_adrs;
2793: --
2793: --
2794: exception
2795: when others then
2796: if g_debug then
2797: hr_utility.set_location('Exception Raised '||l_proc, 10);
2798: end if;
2799: raise;
2800: --
2801: end check_adrs;
2828: l_leg_id c_leg_id%rowtype;
2829: --
2830: begin
2831: --
2832: g_debug := hr_utility.debug_enabled;
2833: if g_debug then
2834: l_proc := g_package||'.check_legid';
2835: hr_utility.set_location ('Entering '||l_proc,10);
2836: end if;
2831: --
2832: g_debug := hr_utility.debug_enabled;
2833: if g_debug then
2834: l_proc := g_package||'.check_legid';
2835: hr_utility.set_location ('Entering '||l_proc,10);
2836: end if;
2837: --
2838: open c_leg_id;
2839: fetch c_leg_id into l_leg_id;
2845: l_legid_found := FALSE;
2846: end if;
2847: --
2848: if g_debug then
2849: hr_utility.set_location ('Leaving ' ||l_proc,10);
2850: end if;
2851: --
2852: return l_legid_found;
2853: --
2853: --
2854: exception
2855: when others then
2856: if g_debug then
2857: hr_utility.set_location('Exception Raised '||l_proc, 10);
2858: end if;
2859: raise;
2860: --
2861: end check_legid;
2875: begin
2876: --
2877: if g_debug then
2878: l_proc := g_package ||'.check_ctfn';
2879: hr_utility.set_location ('Entering '||l_proc,10);
2880: end if;
2881: --
2882: if (p_required_count + p_optional_count) = 0 then
2883: --
2882: if (p_required_count + p_optional_count) = 0 then
2883: --
2884: -- No certifications were found. Return FALSE.
2885: if g_debug then
2886: hr_utility.set_location ('Leaving '||l_proc,95);
2887: end if;
2888: return FALSE;
2889: --
2890: end if;
2893: if p_open_required_count = 0 and
2894: p_optional_count = 0 then
2895: -- all reqd provided and no optional ones found
2896: if g_debug then
2897: hr_utility.set_location ('Leaving '||l_proc,96);
2898: end if;
2899: return TRUE;
2900: --
2901: elsif p_open_required_count = 0 and
2901: elsif p_open_required_count = 0 and
2902: p_optional_count <> p_open_optional_count then
2903: -- all rqd prvdd and atleast one optional prvdd
2904: if g_debug then
2905: hr_utility.set_location ('Leaving '||l_proc,97);
2906: end if;
2907: return TRUE;
2908: --
2909: else
2908: --
2909: else
2910: -- certifications missing or no optional ones provided
2911: if g_debug then
2912: hr_utility.set_location ('Leaving '||l_proc,98);
2913: end if;
2914: return FALSE;
2915: --
2916: end if;
2915: --
2916: end if;
2917: --
2918: if g_debug then
2919: hr_utility.set_location ('Leaving '||l_proc,99);
2920: end if;
2921: --
2922: end check_ctfn;
2923: --
2948: l_proc varchar2(80) ;
2949: --
2950: begin
2951: --
2952: g_debug := hr_utility.debug_enabled;
2953: if g_debug then
2954: l_proc := g_package||'.check_enrt_ctfn';
2955: hr_utility.set_location ('Entering '||l_proc,10);
2956: end if;
2951: --
2952: g_debug := hr_utility.debug_enabled;
2953: if g_debug then
2954: l_proc := g_package||'.check_enrt_ctfn';
2955: hr_utility.set_location ('Entering '||l_proc,10);
2956: end if;
2957: --
2958: get_ctfn_count
2959: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
2969: ,p_open_required_count => l_open_required
2970: ,p_open_optional_count => l_open_optional);
2971: --
2972: if g_debug then
2973: hr_utility.set_location ('Leaving '||l_proc,10);
2974: end if;
2975: --
2976: return l_return;
2977: --
2977: --
2978: exception
2979: when others then
2980: if g_debug then
2981: hr_utility.set_location('Exception Raised '||l_proc, 10);
2982: end if;
2983: raise;
2984: --
2985: end check_enrt_ctfn;
3011: l_proc varchar2(80) ;
3012: --
3013: begin
3014: --
3015: g_debug := hr_utility.debug_enabled;
3016: if g_debug then
3017: l_proc := g_package||'.check_dpnt_ctfn';
3018: hr_utility.set_location ('Entering '||l_proc,10);
3019: end if;
3014: --
3015: g_debug := hr_utility.debug_enabled;
3016: if g_debug then
3017: l_proc := g_package||'.check_dpnt_ctfn';
3018: hr_utility.set_location ('Entering '||l_proc,10);
3019: end if;
3020: --
3021: get_ctfn_count
3022: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
3032: ,p_open_required_count => l_open_required
3033: ,p_open_optional_count => l_open_optional);
3034: --
3035: if g_debug then
3036: hr_utility.set_location ('Leaving '||l_proc,99);
3037: end if;
3038: --
3039: return l_return;
3040: --
3040: --
3041: exception
3042: when others then
3043: if g_debug then
3044: hr_utility.set_location('Exception Raised '||l_proc, 999);
3045: end if;
3046: raise;
3047: --
3048: end check_dpnt_ctfn;
3084: begin
3085: --
3086: if g_debug then
3087: l_proc := g_package||'.check_write_ctfn';
3088: hr_utility.set_location ('Entering '||l_proc,10);
3089: end if;
3090: --
3091: if p_formula_id IS NOT NULL then
3092: --
3119: NULL; -- nothing to do at this time
3120: end if;
3121: --
3122: if g_debug then
3123: hr_utility.set_location ('Leaving ' ||l_proc,10);
3124: end if;
3125: --
3126: return l_write_ctfn;
3127: --
3127: --
3128: exception
3129: when others then
3130: if g_debug then
3131: hr_utility.set_location('Exception Raised '||l_proc, 10);
3132: end if;
3133: raise;
3134: --
3135: end check_write_ctfn;
3457: begin
3458: --
3459: if g_debug then
3460: l_proc := g_package||'.determine_dpnt_miss_actn_items';
3461: hr_utility.set_location ('Entering '||l_proc,10);
3462: end if;
3463:
3464: l_rslt_object_version_number := p_rslt_object_version_number ;
3465:
3469: fetch c_rslt into l_rslt;
3470: close c_rslt;
3471: else
3472: if g_debug then
3473: hr_utility.set_location('RESULT ID IS NULL!!' , 13);
3474: end if;
3475: end if;
3476: --
3477: if l_rslt.person_id is not null then
3508: close c_dpnt_lvl_cd;
3509: end if;
3510: --
3511: if g_debug then
3512: hr_utility.set_location('Designation level code : ' || l_lvl_cd, 14);
3513: end if;
3514: --
3515: -- check the level code for program, ptip or plan (default) and fetch the
3516: -- appropriate required-info-flags.
3546: --
3547: l_ff_ctfns_exists := FALSE ;
3548: --
3549: if g_debug then
3550: hr_utility.set_location('DOB action item ', 30);
3551: end if;
3552: --
3553: l_actn_typ_id := get_actn_typ_id
3554: (p_type_cd => 'DDDOB'
3568: --
3569: -- check if person has a date of birth entry
3570: --
3571: if g_debug then
3572: hr_utility.set_location('DOB is rqd', 35);
3573: end if;
3574: --
3575: if l_dpnt.susp_if_dpnt_dob_nt_prv_cd = 'RQDS' then
3576: l_rqd_flag := 'Y';
3608: -- if date_track mode is updating and designation at plan level
3609: -- delete action item of type DDDOB.
3610: --
3611: if g_debug then
3612: hr_utility.set_location('DOB is not required', 40);
3613: end if;
3614: --
3615: if (l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE) then
3616: --
3632: --
3633: -- Social security number/national identifier action item
3634: --
3635: if g_debug then
3636: hr_utility.set_location('SSN action item ', 45);
3637: end if;
3638: --
3639: l_actn_typ_id := get_actn_typ_id
3640: (p_type_cd => 'DDSSN'
3654: --
3655: -- check if person has a national identifier entry
3656: --
3657: if g_debug then
3658: hr_utility.set_location('SSN is rqd', 50);
3659: end if;
3660: --
3661: if l_dpnt.susp_if_dpnt_ssn_nt_prv_cd = 'RQDS' then
3662: l_rqd_flag := 'Y';
3691: --
3692: else
3693: --
3694: if g_debug then
3695: hr_utility.set_location('SSN is not rqd', 55);
3696: end if;
3697: --
3698: -- dpnt_leg_id_rqd_flag is 'N'
3699: -- if date_track mode is updating and designation at plan level
3720: --
3721: -- address action item
3722: --
3723: if g_debug then
3724: hr_utility.set_location('ADDR action item ' , 60);
3725: end if;
3726: --
3727: l_actn_typ_id := get_actn_typ_id
3728: (p_type_cd => 'DDADDR'
3742: --
3743: -- check if person has an address
3744: --
3745: if g_debug then
3746: hr_utility.set_location('ADDR is rqd', 65);
3747: end if;
3748: --
3749: if l_dpnt.susp_if_dpnt_adr_nt_prv_cd = 'RQDS' then
3750: l_rqd_flag := 'Y';
3780: --
3781: else
3782: --
3783: if g_debug then
3784: hr_utility.set_location('ADD is not rqd', 70);
3785: end if;
3786: --
3787: -- dpnt_adrs_rqd_flag is 'N'
3788: -- if date_track mode is updating and designation at plan level
3806: --
3807: -- certification action item
3808: --
3809: if g_debug then
3810: hr_utility.set_location('CTFN action item ' , 75);
3811: end if;
3812: --
3813: l_actn_typ_id := get_actn_typ_id
3814: (p_type_cd => 'DDCTFN'
3832: --
3833: if l_dpnt.dpnt_dsgn_no_ctfn_rqd_flag = 'N' then
3834: -- double negative....ctfn is required.
3835: if g_debug then
3836: hr_utility.set_location('DPNT CTFN is rqd', 80);
3837: end if;
3838: --
3839: --
3840: -- check if person has certification
3863: not(l_rqd_data_found) and
3864: (l_ctfns_defined) then
3865: --
3866: if g_debug then
3867: hr_utility.set_location('Ctfn defined and not provided', 85);
3868: end if;
3869: --
3870: -- Bug 3851427 : If certifications are considered at LER_CHG_DPNT level, then Suspension of
3871: -- enrollment ( SUSP_IF_CTFN_NOT_PRVD_FLAG ) should also be considered at LER_CHG_DPNT level
3919: --
3920: close c_mng_dpnts;
3921: --
3922: if g_debug then
3923: hr_utility.set_location('l_all_lvls:'||l_all_lvls|| 'l_lvl_cd:'||
3924: l_lvl_cd||' l_pgm_id:'||to_char(l_rslt.pgm_id), 87);
3925: end if;
3926: --
3927: -- now write out the appropriate certifications for each level
4075: ,p_cvrd_dpnt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id);
4076: --
4077: else
4078: if g_debug then
4079: hr_utility.set_location('Dpnt ctfns at ptip in else - false returned ', 111);
4080: end if;
4081: end if;
4082: --
4083: end loop;
4081: end if;
4082: --
4083: end loop;
4084: if g_debug then
4085: hr_utility.set_location('Dpnt ctfns at ptip level - false returned ', 990);
4086: end if;
4087: --
4088: else
4089: --
4136: if l_all_lvls = NOT_FOUND and
4137: (l_lvl_cd = 'PL' ) then
4138: --
4139: if g_debug then
4140: hr_utility.set_location('Dpnt ctfns at plan level', 90);
4141: end if;
4142: --
4143: if l_ctfn_at_ler_chg = TRUE then
4144: --
4220: -- presently we want to write the certifications always.
4221: -- unless the above rule indicates not to.
4222: --
4223: if g_debug then
4224: hr_utility.set_location('Writing Certification', 95);
4225: end if;
4226: --
4227: write_new_dpnt_ctfn_item
4228: (p_elig_cvrd_dpnt_id => l_cvrd_dpnt.elig_cvrd_dpnt_id
4235: ,p_cvrd_dpnt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id);
4236: --
4237: else
4238: if g_debug then
4239: hr_utility.set_location('Rule failed.. not writing ctfn', 100);
4240: end if;
4241: end if;
4242: --
4243: end loop;
4252: IF l_ff_ctfns_exists = FALSE
4253: THEN
4254: --
4255: if g_debug then
4256: hr_utility.set_location ('Entering l_ff_ctfns_exists',999);
4257: end if;
4258: --
4259: -- bug: 5513339
4260: open c_curr_ovn_of_actn (l_prtt_enrt_actn_id);
4270: ,p_datetrack_mode => hr_api.g_zap
4271: ,p_post_rslt_flag => p_post_rslt_flag);
4272: --
4273: if g_debug then
4274: hr_utility.set_location ('Leaving l_ff_ctfns_exists',999);
4275: end if;
4276: --Start Bug 5998009
4277: ELSE
4278: l_dpnt_ctfn_actn_warning := TRUE;
4296: end if;
4297: --
4298: -- Bug 6434143
4299: if g_debug then
4300: hr_utility.set_location('Existing actn item. Have rqd info', 110);
4301: end if;
4302: --
4303: -- Existing open action item. But we now have required info. Close the
4304: -- action item by setting the cmpltd_dt field.
4324: -- Existing closed action item. But required info is missing. Reopen the
4325: -- action item.
4326: --
4327: if g_debug then
4328: hr_utility.set_location('Existing actn item. Rqd info missing', 115);
4329: end if;
4330: --
4331: --Bug 6434143
4332: if l_ctfn_at_ler_chg = true
4360: --
4361: else
4362: --
4363: if g_debug then
4364: hr_utility.set_location('Dpnt ctfn rqd flag is N', 120);
4365: end if;
4366: --
4367: -- dpnt_ctfn_rqd_flag is 'N'
4368: -- if date_track mode is updating and designation at plan level
4388: --
4389: end loop; -- dpnt loop
4390: --
4391: if g_debug then
4392: hr_utility.set_location ('Leaving '||l_proc,999);
4393: end if;
4394: --
4395: p_dpnt_actn_warning := l_dpnt_actn_warning;
4396: --
4405: exception
4406: --
4407: when others then
4408: if g_debug then
4409: hr_utility.set_location('Exception Raised '||l_proc, 997);
4410: end if;
4411: p_rslt_object_version_number :=l_rslt_object_version_number;
4412: p_dpnt_actn_warning := null;
4413:
4460: --
4461: BEGIN
4462: --
4463: if g_debug then
4464: hr_utility.set_location ('Entering '||l_proc,10);
4465: end if;
4466: --
4467:
4468: l_rslt_object_version_number := p_rslt_object_version_number ;
4504:
4505: CLOSE c_future_sal_inc;
4506: -- new call to write to ben_reporting
4507: if g_debug then
4508: hr_utility.set_location ('Leaving '||l_proc, 10);
4509: end if;
4510: --
4511: EXCEPTION
4512: WHEN OTHERS THEN
4510: --
4511: EXCEPTION
4512: WHEN OTHERS THEN
4513: if g_debug then
4514: hr_utility.set_location('Exception Raised '||l_proc, 10);
4515: end if;
4516: fnd_file.put_line(fnd_file.log,'Exception Raised -'||l_proc);
4517: fnd_file.put_line(fnd_file.log,fnd_message.get);
4518: fnd_file.put_line(fnd_file.log,sqlerrm);
4544:
4545: --
4546: begin
4547: --
4548: g_debug := hr_utility.debug_enabled;
4549: if g_debug then
4550: l_proc := g_package||'.process_cwb_actn_items';
4551: hr_utility.set_location ('Entering '||l_proc,10);
4552: end if;
4547: --
4548: g_debug := hr_utility.debug_enabled;
4549: if g_debug then
4550: l_proc := g_package||'.process_cwb_actn_items';
4551: hr_utility.set_location ('Entering '||l_proc,10);
4552: end if;
4553: if g_debug then
4554: hr_utility.set_location ('Entering rslt id '||to_char(p_prtt_enrt_rslt_id),11);
4555: end if;
4550: l_proc := g_package||'.process_cwb_actn_items';
4551: hr_utility.set_location ('Entering '||l_proc,10);
4552: end if;
4553: if g_debug then
4554: hr_utility.set_location ('Entering rslt id '||to_char(p_prtt_enrt_rslt_id),11);
4555: end if;
4556: --
4557: l_rslt_object_version_number := p_rslt_object_version_number ;
4558: l_suspend_flag := p_suspend_flag ;
4585: raise hr_api.validate_enabled;
4586: end if;
4587: --
4588: if g_debug then
4589: hr_utility.set_location ('Leaving '||l_proc, 10);
4590: end if;
4591: --
4592: exception
4593: --
4595: then
4596: rollback to process_cwb_actn_items;
4597: --
4598: if g_debug then
4599: hr_utility.set_location(' Leaving:'||l_proc, 10);
4600: end if;
4601: --
4602: when others then
4603: if g_debug then
4600: end if;
4601: --
4602: when others then
4603: if g_debug then
4604: hr_utility.set_location('Exception Raised '||l_proc, 10);
4605: end if;
4606: p_rslt_object_version_number := l_rslt_object_version_number ;
4607: p_suspend_flag := l_suspend_flag ;
4608: raise;
4772: begin
4773: --
4774: if g_debug then
4775: l_proc := g_package||'.determine_dpnt_actn_items';
4776: hr_utility.set_location ('Entering '||l_proc,10);
4777: end if;
4778: --
4779: l_rslt_object_version_number :=p_rslt_object_version_number ;
4780:
4801:
4802: if ((p_allws_flag = 'Y') or (p_dsgn_cd = 'R')) then
4803: --
4804: if g_debug then
4805: hr_utility.set_location('Dpnt dsgn allowed', 10);
4806: end if;
4807: --
4808: -- Check if any dependents are designated
4809: --
4820: l_rqd_flag := 'N';
4821: end if;
4822: --
4823: if g_debug then
4824: hr_utility.set_location('Dpnt dsgn rqd flag : ' || l_rqd_flag || ' ' || l_proc, 20);
4825: end if;
4826: --
4827: if c_dpnt_dsgn%NOTFOUND then
4828: --
4826: --
4827: if c_dpnt_dsgn%NOTFOUND then
4828: --
4829: if g_debug then
4830: hr_utility.set_location('No dpnts designated', 10);
4831: end if;
4832: --
4833: -- there are no dependents designated
4834: --
4859: If c_elig_dpnts%FOUND then
4860: --
4861: l_open_act_item_flag := TRUE ;
4862: if g_debug then
4863: hr_utility.set_location('Eligible dpnts found: ', 10);
4864: end if;
4865: --
4866: Else
4867: l_open_act_item_flag := FALSE ;
4883:
4884: /* if c_not_emp_only%found then
4885: --
4886: if g_debug then
4887: hr_utility.set_location('c_not_emp_only found', 10);
4888: end if;
4889: --
4890: l_open_act_item_flag := TRUE ;
4891: --
4891: --
4892: else
4893: --
4894: if g_debug then
4895: hr_utility.set_location('c_not_emp_only not found: l_mx_dpnts_alwd_num: '||l_mx_dpnts_alwd_num, 10);
4896: end if;
4897: --
4898: Open c_elig_dpnts;
4899: Fetch c_elig_dpnts into l_Dummy;
4901: If c_elig_dpnts%FOUND then
4902: --
4903: l_open_act_item_flag := TRUE ;
4904: if g_debug then
4905: hr_utility.set_location('c_lig_dpnts found: ', 10);
4906: end if;
4907: --
4908: Else
4909: l_open_act_item_flag := FALSE ;
4944: -- Also, delete all other "dependent" action items that might exist for
4945: -- the prtt_enrt_rslt_id.
4946: --
4947: if g_debug then
4948: hr_utility.set_location('Deleting other dpnt actn items', 10);
4949: end if;
4950: --
4951: for actn_item_rec in c_other_dpnt_actn_items loop
4952: --
4977: --
4978: else
4979: --
4980: if g_debug then
4981: hr_utility.set_location('Dpnts designated', 10);
4982: end if;
4983: --
4984: -- Dependents found. Set the out parameter.
4985: --
5008: --
5009: end if;
5010: --
5011: if g_debug then
5012: hr_utility.set_location('Leaving ' ||l_proc,10);
5013: end if;
5014: --
5015: exception
5016: when others then
5014: --
5015: exception
5016: when others then
5017: if g_debug then
5018: hr_utility.set_location('Exception Raised '||l_proc, 10);
5019: end if;
5020: p_rslt_object_version_number := l_rslt_object_version_number ;
5021: p_dd_actn_item_open := null;
5022: p_hack := null;
5183: begin
5184: --
5185: if g_debug then
5186: l_proc := g_package||'.complete_dependent_designation';
5187: hr_utility.set_location ('Entering '||l_proc,10);
5188: end if;
5189: --
5190: l_rslt_object_version_number :=p_rslt_object_version_number;
5191: l_dpnt_actn_warning :=p_dpnt_actn_warning ;
5195: fetch c_get_ler_typ into l_get_ler_typ;
5196: close c_get_ler_typ;
5197: -- Bug 7516987
5198:
5199: hr_utility.set_location ('l_get_ler_typ.typ_cd '||to_char(l_get_ler_typ.typ_cd),20);
5200: hr_utility.set_location ('l_get_ler_typ.lf_evt_ocrd_dt '||to_char(l_get_ler_typ.lf_evt_ocrd_dt),20);
5201: hr_utility.set_location ('p_effective_date '||p_effective_date,20);
5202: hr_utility.set_location ('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,20);
5203: hr_utility.set_location ('l_get_ler_typ '||l_get_ler_typ.typ_cd,20);
5196: close c_get_ler_typ;
5197: -- Bug 7516987
5198:
5199: hr_utility.set_location ('l_get_ler_typ.typ_cd '||to_char(l_get_ler_typ.typ_cd),20);
5200: hr_utility.set_location ('l_get_ler_typ.lf_evt_ocrd_dt '||to_char(l_get_ler_typ.lf_evt_ocrd_dt),20);
5201: hr_utility.set_location ('p_effective_date '||p_effective_date,20);
5202: hr_utility.set_location ('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,20);
5203: hr_utility.set_location ('l_get_ler_typ '||l_get_ler_typ.typ_cd,20);
5204:
5197: -- Bug 7516987
5198:
5199: hr_utility.set_location ('l_get_ler_typ.typ_cd '||to_char(l_get_ler_typ.typ_cd),20);
5200: hr_utility.set_location ('l_get_ler_typ.lf_evt_ocrd_dt '||to_char(l_get_ler_typ.lf_evt_ocrd_dt),20);
5201: hr_utility.set_location ('p_effective_date '||p_effective_date,20);
5202: hr_utility.set_location ('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,20);
5203: hr_utility.set_location ('l_get_ler_typ '||l_get_ler_typ.typ_cd,20);
5204:
5205: --
5198:
5199: hr_utility.set_location ('l_get_ler_typ.typ_cd '||to_char(l_get_ler_typ.typ_cd),20);
5200: hr_utility.set_location ('l_get_ler_typ.lf_evt_ocrd_dt '||to_char(l_get_ler_typ.lf_evt_ocrd_dt),20);
5201: hr_utility.set_location ('p_effective_date '||p_effective_date,20);
5202: hr_utility.set_location ('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,20);
5203: hr_utility.set_location ('l_get_ler_typ '||l_get_ler_typ.typ_cd,20);
5204:
5205: --
5206: -- Check the total number of dpnts designated. See if it's less
5199: hr_utility.set_location ('l_get_ler_typ.typ_cd '||to_char(l_get_ler_typ.typ_cd),20);
5200: hr_utility.set_location ('l_get_ler_typ.lf_evt_ocrd_dt '||to_char(l_get_ler_typ.lf_evt_ocrd_dt),20);
5201: hr_utility.set_location ('p_effective_date '||p_effective_date,20);
5202: hr_utility.set_location ('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id,20);
5203: hr_utility.set_location ('l_get_ler_typ '||l_get_ler_typ.typ_cd,20);
5204:
5205: --
5206: -- Check the total number of dpnts designated. See if it's less
5207: -- than the minimum number of dpnts of any contact type. There
5245: --
5246: close c_min_no_grp;
5247: --
5248: if g_debug then
5249: hr_utility.set_location ('After c_min_no_grp '||l_proc,20);
5250: end if;
5251: --
5252: -- Check the other dsgn rqmts. If there are any rows returned from
5253: -- this cursor, then the person must designate more dependents of
5278: --
5279: close c_min_typ;
5280: --
5281: if g_debug then
5282: hr_utility.set_location ('After c_min_typ '||l_proc,30);
5283: end if;
5284: --
5285: --
5286: -- find DDADDNL action item, if it exists.
5298: ,p_cmpltd_dt => l_cmpltd_dt
5299: ,p_object_version_number => l_object_version_number);
5300: --
5301: if g_debug then
5302: hr_utility.set_location ('After get_prtt_enrt_actn_id call '||l_proc,30);
5303: end if;
5304: --
5305: if p_dsgn_cd = 'R' then
5306: l_rqd_flag := 'Y';
5310: --
5311: if l_meets_rqmt = 'Y' then
5312: --
5313: if g_debug then
5314: hr_utility.set_location('Meets addnl dpnt rqmts', 10);
5315: end if;
5316: --
5317: -- they meet the minimum number of dpnts, call process-action-item with
5318: -- rqd-data 'true'
5333: --
5334: elsif l_meets_rqmt = 'N' then
5335: --
5336: if g_debug then
5337: hr_utility.set_location('Does not meet rqmts', 10);
5338: end if;
5339: --
5340: --
5341: -- Doesn't meet requirements. set rqd-data 'FALSE'
5363: p_dpnt_actn_warning := TRUE;
5364: end if;
5365: --
5366: if g_debug then
5367: hr_utility.set_location ('Leaving ' ||l_proc,90);
5368: end if;
5369: --
5370: exception
5371: when others then
5369: --
5370: exception
5371: when others then
5372: if g_debug then
5373: hr_utility.set_location('Exception Raised '||l_proc, 99);
5374: end if;
5375: p_rslt_object_version_number := l_rslt_object_version_number;
5376: p_dpnt_actn_warning := l_dpnt_actn_warning ;
5377: raise;
5435: and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
5436: --
5437: begin
5438: --
5439: g_debug := hr_utility.debug_enabled;
5440: if g_debug then
5441: l_proc := g_package||'.process_dpnt_actn_items';
5442: hr_utility.set_location ('Entering '||l_proc,10);
5443: end if;
5438: --
5439: g_debug := hr_utility.debug_enabled;
5440: if g_debug then
5441: l_proc := g_package||'.process_dpnt_actn_items';
5442: hr_utility.set_location ('Entering '||l_proc,10);
5443: end if;
5444: --
5445: l_rslt_object_version_number :=p_rslt_object_version_number;
5446: l_suspend_flag :=p_suspend_flag ;
5547: raise hr_api.validate_enabled;
5548: end if;
5549: --
5550: if g_debug then
5551: hr_utility.set_location ('Leaving '||l_proc, 10);
5552: end if;
5553: --
5554: exception
5555: --
5557: then
5558: rollback to process_dpnt_actn_items;
5559: --
5560: if g_debug then
5561: hr_utility.set_location(' Leaving:'||l_proc, 10);
5562: end if;
5563: --
5564: when others then
5565: if g_debug then
5562: end if;
5563: --
5564: when others then
5565: if g_debug then
5566: hr_utility.set_location('Exception Raised '||l_proc, 10);
5567: end if;
5568:
5569: p_rslt_object_version_number := l_rslt_object_version_number ;
5570: p_suspend_flag := l_suspend_flag ;
5924:
5925: --
5926: Begin
5927: --
5928: hr_utility.set_location('Entering - Check certification prvdd',10);
5929: --
5930: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5931: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5932: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5926: Begin
5927: --
5928: hr_utility.set_location('Entering - Check certification prvdd',10);
5929: --
5930: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5931: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5932: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5933: hr_utility.set_location('enrt_r_bnft_ctfn_cd'||p_enrt_r_bnft_ctfn_cd,11);
5934: --bug#3748133
5927: --
5928: hr_utility.set_location('Entering - Check certification prvdd',10);
5929: --
5930: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5931: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5932: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5933: hr_utility.set_location('enrt_r_bnft_ctfn_cd'||p_enrt_r_bnft_ctfn_cd,11);
5934: --bug#3748133
5935: /*
5928: hr_utility.set_location('Entering - Check certification prvdd',10);
5929: --
5930: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5931: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5932: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5933: hr_utility.set_location('enrt_r_bnft_ctfn_cd'||p_enrt_r_bnft_ctfn_cd,11);
5934: --bug#3748133
5935: /*
5936: if p_crntly_enrd_flag = 'Y' and p_ctfn_determine_cd = 'ENRFT' then
5929: --
5930: hr_utility.set_location('ctfn_determine_cd'||p_ctfn_determine_cd,11);
5931: hr_utility.set_location('enrt_ctfn_typ_cd'||p_enrt_ctfn_typ_cd,11);
5932: hr_utility.set_location('prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,11);
5933: hr_utility.set_location('enrt_r_bnft_ctfn_cd'||p_enrt_r_bnft_ctfn_cd,11);
5934: --bug#3748133
5935: /*
5936: if p_crntly_enrd_flag = 'Y' and p_ctfn_determine_cd = 'ENRFT' then
5937: if p_bnft_amt = 0 then
5956: --If defined at the plan level, then need to see the previous enrollment in
5957: --any option in plan. If the ctfn is not defined at the plan level just check
5958: --for the currently enrolled
5959: --
5960: hr_utility.set_location(' p_ctfn_determine_cd = '||p_ctfn_determine_cd,122);
5961: --
5962: open c_pl_ctfn(p_pl_id,p_per_in_ler_id,p_effective_date,p_enrt_ctfn_typ_cd);
5963: fetch c_pl_ctfn into l_dummy;
5964: if c_pl_ctfn%found then
5962: open c_pl_ctfn(p_pl_id,p_per_in_ler_id,p_effective_date,p_enrt_ctfn_typ_cd);
5963: fetch c_pl_ctfn into l_dummy;
5964: if c_pl_ctfn%found then
5965: --
5966: hr_utility.set_location(' Plan Level Cert',122);
5967: --
5968: open c_current_pl_enrollment(p_prtt_enrt_rslt_id);
5969: --
5970: fetch c_current_pl_enrollment into l_dummy;
5968: open c_current_pl_enrollment(p_prtt_enrt_rslt_id);
5969: --
5970: fetch c_current_pl_enrollment into l_dummy;
5971: if c_current_pl_enrollment%found then
5972: hr_utility.set_location(' Plan Level - Currently Enrolled in',122);
5973: l_ctfn_prvdd := TRUE;
5974: end if;
5975: --
5976: close c_current_pl_enrollment;
5976: close c_current_pl_enrollment;
5977: --
5978: elsif p_crntly_enrd_flag = 'Y' and p_oipl_id is not null then
5979: --
5980: hr_utility.set_location(' Option Level -',123);
5981: --Now we need to check if the previous enrollment was unsuspeded or not
5982: open c_current_oipl_enrollment(p_prtt_enrt_rslt_id);
5983: --
5984: fetch c_current_oipl_enrollment into l_dummy;
5982: open c_current_oipl_enrollment(p_prtt_enrt_rslt_id);
5983: --
5984: fetch c_current_oipl_enrollment into l_dummy;
5985: if c_current_oipl_enrollment%found then
5986: hr_utility.set_location(' Option in Plan Level - Currently Enrolled in',122);
5987: l_ctfn_prvdd := TRUE;
5988: end if;
5989: --
5990: close c_current_oipl_enrollment;
5989: --
5990: close c_current_oipl_enrollment;
5991: --
5992: else
5993: hr_utility.set_location('Not Currently Enrolled in- why here ', 123);
5994: end if;
5995: close c_pl_ctfn ;
5996: end if;
5997: --
6020: --
6021: if not l_ctfn_prvdd and p_oipl_id is not null and
6022: p_ctfn_determine_cd = 'ENRNP'then
6023: --
6024: hr_utility.set_location('different opt',12);
6025: open c_prtt_enrt_rslt(p_prtt_enrt_rslt_id);
6026: fetch c_prtt_enrt_rslt into l_prtt_enrt_rslt_id;
6027: close c_prtt_enrt_rslt;
6028: --
6025: open c_prtt_enrt_rslt(p_prtt_enrt_rslt_id);
6026: fetch c_prtt_enrt_rslt into l_prtt_enrt_rslt_id;
6027: close c_prtt_enrt_rslt;
6028: --
6029: hr_utility.set_location('Prtt result Id'||l_prtt_enrt_rslt_id,13);
6030: if l_prtt_enrt_rslt_id is not null then
6031: --
6032: if p_rqd_flag = 'Y' then
6033: hr_utility.set_location('inside',11);
6029: hr_utility.set_location('Prtt result Id'||l_prtt_enrt_rslt_id,13);
6030: if l_prtt_enrt_rslt_id is not null then
6031: --
6032: if p_rqd_flag = 'Y' then
6033: hr_utility.set_location('inside',11);
6034: open c_ctfn_prvdd(l_prtt_enrt_rslt_id);
6035: fetch c_ctfn_prvdd into l_dummy;
6036: if c_ctfn_prvdd%found then
6037: l_ctfn_prvdd := TRUE;
6042: --
6043: open c_ctfn_prvdd2(l_prtt_enrt_rslt_id);
6044: fetch c_ctfn_prvdd2 into l_dummy;
6045: if c_ctfn_prvdd2%found then
6046: hr_utility.set_location('True',14);
6047: l_ctfn_prvdd := TRUE;
6048: end if;
6049: close c_ctfn_prvdd2;
6050: --
6112: end if;
6113: p_ctfn_prvdd := l_ctfn_prvdd;
6114: --
6115: if p_ctfn_prvdd then
6116: hr_utility.set_location('Leaving Check Ctfn Prvdd TRUE',13);
6117: else
6118: hr_utility.set_location('Leaving Check Ctfn Prvdd FALSE',14);
6119: end if;
6120: --
6114: --
6115: if p_ctfn_prvdd then
6116: hr_utility.set_location('Leaving Check Ctfn Prvdd TRUE',13);
6117: else
6118: hr_utility.set_location('Leaving Check Ctfn Prvdd FALSE',14);
6119: end if;
6120: --
6121: end;
6122:
6287: -- End Bug 5887665
6288: --
6289: begin
6290: --
6291: g_debug := hr_utility.debug_enabled;
6292: if g_debug then
6293: hr_utility.set_location ('Entering '||l_proc,10);
6294: end if;
6295: --
6289: begin
6290: --
6291: g_debug := hr_utility.debug_enabled;
6292: if g_debug then
6293: hr_utility.set_location ('Entering '||l_proc,10);
6294: end if;
6295: --
6296: l_rslt_object_version_number :=p_rslt_object_version_number ;
6297: l_suspend_flag :=p_suspend_flag ;
6318: -- fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
6319: -- fnd_message.set_token('BUSINESS_GROUP_ID',to_char(p_business_group_id));
6320: -- fnd_message.raise_error;
6321: if g_debug then
6322: hr_utility.set_location('Enrollment already ended/ZAPed by another process ',22);
6323: end if;
6324: return ;
6325: else
6326: close c_epe;
6338: p_prtt_enrt_actn_id => l_prtt_enrt_actn_id,
6339: p_cmpltd_dt => l_cmpltd_dt,
6340: p_object_version_number => l_object_version_number); */ -- Commented for 4241743
6341: --
6342: hr_utility.set_location('CTFN rqd flag '||l_epe.ctfn_rqd_flag,9);
6343: /*Bug 9758763: When a backed out result is reinstated on reprocessing a Life Event,
6344: and the reinstated result is an interim enrollment and the
6345: interim result became effective enrollment because of deleted suspended enrollment
6346: (Action item denied) before backing out the LE and interim and suspended corresponds to same plan(ie only
6349: This is continuation of Bug 9538592 */
6350: if(l_epe.ctfn_rqd_flag = 'Y' and ben_lf_evt_clps_restore.g_create_new_result = 'N') then
6351: --
6352: if g_debug then
6353: hr_utility.set_location('CTFN rqd flag is Y', 10);
6354: hr_utility.set_location('l_actn_exists '||l_actn_exists,10);
6355: end if;
6356: -- Bug No 4241743
6357: l_prtt_enrt_actn_id := null;
6350: if(l_epe.ctfn_rqd_flag = 'Y' and ben_lf_evt_clps_restore.g_create_new_result = 'N') then
6351: --
6352: if g_debug then
6353: hr_utility.set_location('CTFN rqd flag is Y', 10);
6354: hr_utility.set_location('l_actn_exists '||l_actn_exists,10);
6355: end if;
6356: -- Bug No 4241743
6357: l_prtt_enrt_actn_id := null;
6358: open c_enrt_actn
6365: fetch c_enrt_actn into l_enrt_actn;
6366: exit when c_enrt_actn%notfound;
6367: l_prtt_enrt_actn_id := l_enrt_actn.prtt_enrt_actn_id;
6368: if l_epe.per_in_ler_id = l_enrt_actn.per_in_ler_id then
6369: 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);
6370: l_actn_exists := 'Y';
6371: exit;
6372: end if;
6373: end loop;
6371: exit;
6372: end if;
6373: end loop;
6374: close c_enrt_actn;
6375: hr_utility.set_location('After loop l_actn_exists '||l_actn_exists,12);
6376: --
6377: -- Loop through the certifications that might exist for the eltbl chc and
6378: -- write required certifications to the prtt_enrt_ctfn_prvdd table
6379: --
6398: --
6399: -- Bug No 4241743 Added l_actn_exists check in if condition
6400: if ((l_prtt_enrt_actn_id is null or l_actn_exists = 'N') and not l_ctfn_prvdd) then
6401: --
6402: hr_utility.set_location('write action ',12);
6403: write_new_action_item
6404: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
6405: ,p_rslt_object_version_number => p_rslt_object_version_number
6406: ,p_actn_typ_id => l_actn_typ_id
6453: fnd_message.raise_error;
6454: else
6455: close c_enrt_bnft;
6456: if g_debug then
6457: hr_utility.set_location('c_enrt_bnft found', 10);
6458: end if;
6459: end if;
6460: --
6461: -- Check if there are any ENRTCTFN action items and certifications that
6508: end if;
6509:
6510: -- write the ctfn's only if the flag is 'Y' AND if the cvg amt was
6511: -- enterable, the entered amt was more than the mx_wout_ctfn_val.Bug 1249901
6512: hr_utility.set_location('Benefit amount '||l_epe.bnft_amt,11);
6513: --
6514: open c_ler_rstrn (p_effective_date, l_epe.per_in_ler_id, l_epe.pl_id);
6515: fetch c_ler_rstrn into l_cvg_incr_code;
6516: close c_ler_rstrn;
6534: --
6535: -- Bug 3183266 : For flat range l_enrt_bnft.mx_wout_ctfn_val will be null
6536: --
6537: if g_debug then
6538: hr_utility.set_location('CTFN rqd flag is Y', 10);
6539: end if;
6540: --
6541: -- Loop through the certifications that might exist for the eltbl chc and
6542: -- write required certifications to the prtt_enrt_ctfn_prvdd table
6605: raise hr_api.validate_enabled;
6606: end if;
6607: --
6608: if g_debug then
6609: hr_utility.set_location ('Leaving ' ||l_proc,10);
6610: end if;
6611: --
6612: exception
6613: --
6614: when hr_api.validate_enabled
6615: then
6616: rollback to determine_other_actn_items;
6617: if g_debug then
6618: hr_utility.set_location ('Leaving ' ||l_proc,10);
6619: end if;
6620: --
6621: when others then
6622: if g_debug then
6619: end if;
6620: --
6621: when others then
6622: if g_debug then
6623: hr_utility.set_location('Exception Raised '||l_proc, 10);
6624: end if;
6625: raise;
6626: --
6627: end determine_other_actn_items;
6676: begin
6677: --
6678: if g_debug then
6679: l_proc := g_package||'.determine_pcp_dpnt_actn_items';
6680: hr_utility.set_location ('Entering '||l_proc,10);
6681: end if;
6682: --
6683: l_rslt_object_version_number := p_rslt_object_version_number ;
6684: l_suspend_flag := p_suspend_flag ;
6693: l_rqd_flag := 'N';
6694: end if;
6695: --
6696: if g_debug then
6697: hr_utility.set_location('pcp dpnt dsgn rqd flag : ' || l_rqd_flag || ' ' ||
6698: l_proc, 20);
6699: end if;
6700: --
6701: -- As the only modification happening in the code is for rqd_flag = 'Y',
6706: fetch c_prmry_care_actn into l_dummy;
6707: if c_prmry_care_actn%notfound then
6708: l_rqd_data_found := FALSE;
6709: if g_debug then
6710: hr_utility.set_location('c_prmry_care_actn notfound' , 90 );
6711: end if;
6712: elsif c_prmry_care_actn%found then
6713: l_rqd_data_found := TRUE;
6714: if g_debug then
6711: end if;
6712: elsif c_prmry_care_actn%found then
6713: l_rqd_data_found := TRUE;
6714: if g_debug then
6715: hr_utility.set_location('c_prmry_care_actn found' , 91 );
6716: end if;
6717: end if;
6718: close c_prmry_care_actn;
6719: --
6717: end if;
6718: close c_prmry_care_actn;
6719: --
6720: if g_debug then
6721: hr_utility.set_location('PCPDPNT action item ', 30);
6722: end if;
6723: --
6724: l_actn_typ_id := get_actn_typ_id
6725: (p_type_cd => 'PCPDPNT'
6725: (p_type_cd => 'PCPDPNT'
6726: ,p_business_group_id => p_business_group_id);
6727: --
6728: if g_debug then
6729: hr_utility.set_location ('Entering get_prtt_enrt_actn_id ' , 101);
6730: end if;
6731: get_prtt_enrt_actn_id
6732: (p_actn_typ_id => l_actn_typ_id,
6733: p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
6746: l_rqd_data_found = TRUE and
6747: l_cmpltd_dt IS NULL then
6748: --
6749: if g_debug then
6750: hr_utility.set_location ('Case 1 ',102);
6751: end if;
6752: -- Existing open action item but we now have required data. Close the open
6753: -- action item by setting cmpltd_dt field
6754: --
6759: --
6760: -- Found a closed action item. But required data is missing. Reopen item
6761: --
6762: if g_debug then
6763: hr_utility.set_location ('Case2 ',103);
6764: end if;
6765: l_pcp_dpnt_actn_warning := TRUE;
6766: else
6767: if g_debug then
6764: end if;
6765: l_pcp_dpnt_actn_warning := TRUE;
6766: else
6767: if g_debug then
6768: hr_utility.set_location ('Case3 ',104);
6769: end if;
6770: l_pcp_dpnt_actn_warning := FALSE;
6771: end if;
6772: -- process the action item
6793: raise hr_api.validate_enabled;
6794: end if;
6795: --
6796: if g_debug then
6797: hr_utility.set_location ('Leaving '||l_proc, 10);
6798: end if;
6799: --
6800:
6801: exception
6804: then
6805: rollback to determine_pcp_dpnt_actn_items;
6806: --
6807: if g_debug then
6808: hr_utility.set_location(' Leaving:'||l_proc, 10);
6809: end if;
6810: --
6811: when others then
6812: if g_debug then
6809: end if;
6810: --
6811: when others then
6812: if g_debug then
6813: hr_utility.set_location('Exception Raised '||l_proc, 10);
6814: end if;
6815: p_rslt_object_version_number := l_rslt_object_version_number;
6816: p_suspend_flag := l_suspend_flag ;
6817: p_pcp_dpnt_actn_warning := null ;
6862: begin
6863: --
6864: if g_debug then
6865: l_proc := g_package||'.determine_pcp_actn_items';
6866: hr_utility.set_location ('Entering '||l_proc,10);
6867: end if;
6868: --
6869:
6870: l_rslt_object_version_number := p_rslt_object_version_number ;
6880: l_rqd_flag := 'N';
6881: end if;
6882: --
6883: if g_debug then
6884: hr_utility.set_location('pcp dsgn rqd flag : ' || l_rqd_flag || ' ' ||
6885: l_proc, 20);
6886: end if;
6887: --
6888: -- As the only modification happening in the code is for rqd_flag = 'Y',
6898: end if;
6899: close c_prmry_care_actn;
6900: --
6901: if g_debug then
6902: hr_utility.set_location('PCPPRTT action item ', 30);
6903: end if;
6904: --
6905: l_actn_typ_id := get_actn_typ_id
6906: (p_type_cd => 'PCPPRTT'
6919: if (l_prtt_enrt_actn_id IS NULL and
6920: l_rqd_data_found = FALSE) then
6921: l_pcp_actn_warning := TRUE;
6922: if g_debug then
6923: hr_utility.set_location ('Leaving '||'TRUE', 11);
6924: end if;
6925: elsif l_prtt_enrt_actn_id IS NOT NULL and
6926: l_rqd_data_found = TRUE and
6927: l_cmpltd_dt IS NULL then
6929: -- Existing open action item but we now have required data. Close the open
6930: -- action item by setting cmpltd_dt field
6931: --
6932: if g_debug then
6933: hr_utility.set_location ('Leaving '||'FALSE', 12);
6934: end if;
6935: l_pcp_actn_warning := FALSE;
6936: elsif l_prtt_enrt_actn_id IS NOT NULL and
6937: l_rqd_data_found = FALSE and
6939: --
6940: -- Found a closed action item. But required data is missing. Reopen item
6941: --
6942: if g_debug then
6943: hr_utility.set_location ('Leaving '||'TRUE', 13);
6944: end if;
6945: l_pcp_actn_warning := TRUE;
6946: else
6947: if g_debug then
6944: end if;
6945: l_pcp_actn_warning := TRUE;
6946: else
6947: if g_debug then
6948: hr_utility.set_location ('Leaving '||'FALSE', 14);
6949: end if;
6950: l_pcp_actn_warning := FALSE;
6951: end if;
6952: -- process the action item
6972: raise hr_api.validate_enabled;
6973: end if;
6974: --
6975: if g_debug then
6976: hr_utility.set_location ('Leaving '||l_proc, 10);
6977: end if;
6978: --
6979:
6980: exception
6983: then
6984: rollback to determine_pcp_actn_items;
6985: --
6986: if g_debug then
6987: hr_utility.set_location(' Leaving:'||l_proc, 10);
6988: end if;
6989: --
6990: when others then
6991: if g_debug then
6988: end if;
6989: --
6990: when others then
6991: if g_debug then
6992: hr_utility.set_location('Exception Raised '||l_proc, 10);
6993: end if;
6994:
6995: p_rslt_object_version_number := l_rslt_object_version_number;
6996: p_suspend_flag := l_suspend_flag ;
7073: and pen.effective_end_date;
7074: --
7075: begin
7076: --
7077: g_debug := hr_utility.debug_enabled;
7078: if g_debug then
7079: l_proc := g_package||'.process_pcp_dpnt_actn_items';
7080: hr_utility.set_location ('Entering '||l_proc,10);
7081: end if;
7076: --
7077: g_debug := hr_utility.debug_enabled;
7078: if g_debug then
7079: l_proc := g_package||'.process_pcp_dpnt_actn_items';
7080: hr_utility.set_location ('Entering '||l_proc,10);
7081: end if;
7082: if g_debug then
7083: hr_utility.set_location ('Entering rslt id '||to_char(p_prtt_enrt_rslt_id),11);
7084: end if;
7079: l_proc := g_package||'.process_pcp_dpnt_actn_items';
7080: hr_utility.set_location ('Entering '||l_proc,10);
7081: end if;
7082: if g_debug then
7083: hr_utility.set_location ('Entering rslt id '||to_char(p_prtt_enrt_rslt_id),11);
7084: end if;
7085: --
7086: --
7087:
7103: fetch c_pcp_dpnt_cd into l_pcp_dpnt_dsgn_cd,l_elig_cvrd_dpnt_id;
7104: if c_pcp_dpnt_cd%notfound then
7105: close c_pcp_dpnt_cd;
7106: if g_debug then
7107: hr_utility.set_location('pcp not required ' , 298);
7108: end if;
7109: return;
7110: else
7111: loop
7109: return;
7110: else
7111: loop
7112: if g_debug then
7113: hr_utility.set_location('l_pcp_dpnt_dsgn_cd '||l_pcp_dpnt_dsgn_cd , 299);
7114: end if;
7115: if g_debug then
7116: hr_utility.set_location('l_elig_cvrd_dpnt_id '||l_elig_cvrd_dpnt_id, 299);
7117: end if;
7112: if g_debug then
7113: hr_utility.set_location('l_pcp_dpnt_dsgn_cd '||l_pcp_dpnt_dsgn_cd , 299);
7114: end if;
7115: if g_debug then
7116: hr_utility.set_location('l_elig_cvrd_dpnt_id '||l_elig_cvrd_dpnt_id, 299);
7117: end if;
7118: determine_pcp_dpnt_actn_items
7119: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
7120: ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
7135: --
7136: end if;
7137: else
7138: if g_debug then
7139: hr_utility.set_location('l_pcp_dpnt_dsgn_cd '||l_pcp_dpnt_dsgn_cd , 399);
7140: end if;
7141: loop
7142: --
7143: determine_pcp_dpnt_actn_items
7166: raise hr_api.validate_enabled;
7167: end if;
7168: --
7169: if g_debug then
7170: hr_utility.set_location ('Leaving '||l_proc, 10);
7171: end if;
7172: --
7173: exception
7174: --
7176: then
7177: rollback to process_pcp_dpnt_actn_items;
7178: --
7179: if g_debug then
7180: hr_utility.set_location(' Leaving:'||l_proc, 10);
7181: end if;
7182: --
7183: when others then
7184: if g_debug then
7181: end if;
7182: --
7183: when others then
7184: if g_debug then
7185: hr_utility.set_location('Exception Raised '||l_proc, 10);
7186: end if;
7187:
7188: p_rslt_object_version_number := l_rslt_object_version_number ;
7189: p_suspend_flag := l_suspend_flag ;
7245: and perf.effective_end_date;
7246: --
7247: begin
7248: --
7249: g_debug := hr_utility.debug_enabled;
7250: if g_debug then
7251: l_proc := g_package||'.process_pcp_actn_items';
7252: hr_utility.set_location ('Entering '||l_proc,10);
7253: end if;
7248: --
7249: g_debug := hr_utility.debug_enabled;
7250: if g_debug then
7251: l_proc := g_package||'.process_pcp_actn_items';
7252: hr_utility.set_location ('Entering '||l_proc,10);
7253: end if;
7254: --
7255: l_rslt_object_version_number := p_rslt_object_version_number ;
7256: l_suspend_flag := p_suspend_flag ;
7270: return;
7271: else
7272: close c_pcp_cd;
7273: if g_debug then
7274: hr_utility.set_location('l_pcp_dsgn_cd '||l_pcp_dsgn_cd , 198);
7275: end if;
7276: if g_debug then
7277: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,198);
7278: end if;
7273: if g_debug then
7274: hr_utility.set_location('l_pcp_dsgn_cd '||l_pcp_dsgn_cd , 198);
7275: end if;
7276: if g_debug then
7277: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,198);
7278: end if;
7279:
7280: determine_pcp_actn_items
7281: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
7290: end if;
7291: else
7292: close c_pcp_cd_oipl;
7293: if g_debug then
7294: hr_utility.set_location('l_pcp_dsgn_cd '||l_pcp_dsgn_cd , 199);
7295: end if;
7296: if g_debug then
7297: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,199);
7298: end if;
7293: if g_debug then
7294: hr_utility.set_location('l_pcp_dsgn_cd '||l_pcp_dsgn_cd , 199);
7295: end if;
7296: if g_debug then
7297: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,199);
7298: end if;
7299:
7300: determine_pcp_actn_items
7301: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
7315: raise hr_api.validate_enabled;
7316: end if;
7317: --
7318: if g_debug then
7319: hr_utility.set_location ('Leaving '||l_proc, 10);
7320: end if;
7321: --
7322: exception
7323: --
7325: then
7326: rollback to process_pcp_actn_items;
7327: --
7328: if g_debug then
7329: hr_utility.set_location(' Leaving:'||l_proc, 10);
7330: end if;
7331: --
7332: when others then
7333: if g_debug then
7330: end if;
7331: --
7332: when others then
7333: if g_debug then
7334: hr_utility.set_location('Exception Raised '||l_proc, 10);
7335: end if;
7336:
7337: p_rslt_object_version_number := l_rslt_object_version_number ;
7338: p_suspend_flag := l_suspend_flag ;
7377: begin
7378: --
7379: if g_debug then
7380: l_proc := g_package||'.check_bnf_ttee';
7381: hr_utility.set_location ('Entering '||l_proc,10);
7382: end if;
7383: --
7384: open c_bnf_age;
7385: fetch c_bnf_age into l_bnf_age;
7385: fetch c_bnf_age into l_bnf_age;
7386: close c_bnf_age;
7387: --
7388: if g_debug then
7389: hr_utility.set_location ('Leaving ' ||l_proc,10);
7390: end if;
7391: --
7392: if (l_bnf_age.bnf_age IS NOT NULL and
7393: l_bnf_age.bnf_age >= 18)
7404: --
7405: exception
7406: when others then
7407: if g_debug then
7408: hr_utility.set_location('Exception Raised '||l_proc, 10);
7409: end if;
7410: raise;
7411: --
7412: end check_bnf_ttee;
7436: l_proc varchar2(80) ;
7437: --
7438: begin
7439: --
7440: g_debug := hr_utility.debug_enabled;
7441: if g_debug then
7442: l_proc := g_package||'.check_bnf_ctfn';
7443: hr_utility.set_location ('Entering '||l_proc,10);
7444: end if;
7439: --
7440: g_debug := hr_utility.debug_enabled;
7441: if g_debug then
7442: l_proc := g_package||'.check_bnf_ctfn';
7443: hr_utility.set_location ('Entering '||l_proc,10);
7444: end if;
7445: --
7446: get_ctfn_count
7447: (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
7457: ,p_open_required_count => l_open_required
7458: ,p_open_optional_count => l_open_optional);
7459: --
7460: if g_debug then
7461: hr_utility.set_location ('Leaving '||l_proc,10);
7462: end if;
7463: --
7464: return l_return;
7465: --
7466: exception
7467: --
7468: when others then
7469: if g_debug then
7470: hr_utility.set_location('Exception Raised '||l_proc, 10);
7471: end if;
7472: raise;
7473: --
7474: end check_bnf_ctfn;
7508: begin
7509: --
7510: if g_debug then
7511: l_proc := g_package||'.check_bnf_actn_item_dfnd' ;
7512: hr_utility.set_location ('Entering '||l_proc,10);
7513: end if;
7514: --
7515: open c_bnf_actn_item ;
7516: fetch c_bnf_actn_item into l_dummy;
7527: --
7528: close c_bnf_actn_item;
7529: --
7530: if g_debug then
7531: hr_utility.set_location ('Leaving '||l_proc,10);
7532: end if;
7533: --
7534: return l_exists;
7535: --
7559: begin
7560: --
7561: if g_debug then
7562: l_proc := g_package||'.write_new_bnf_ctfn_item';
7563: hr_utility.set_location ('Entering '||l_proc,10);
7564: end if;
7565: --
7566: -- write a new record for each beneficiary certification item
7567: --
7577: ,p_effective_end_date => l_effective_end_date
7578: ,p_pl_bnf_ctfn_prvdd_id => p_pl_bnf_ctfn_prvdd_id);
7579: --
7580: if g_debug then
7581: hr_utility.set_location ('Leaving '||l_proc, 10);
7582: end if;
7583: --
7584: exception
7585: /* we could have duplicates already here from prior run */
7584: exception
7585: /* we could have duplicates already here from prior run */
7586: when others then
7587: if g_debug then
7588: hr_utility.set_location('Exception Raised '||l_proc, 10);
7589: end if;
7590:
7591: p_object_version_number := null ;
7592: p_pl_bnf_ctfn_prvdd_id := null;
7784: --
7785:
7786: if g_debug then
7787: l_proc := g_package||'.determine_bnf_miss_actn_items';
7788: hr_utility.set_location ('Entering '||l_proc,10);
7789: end if;
7790: --
7791: l_rslt_object_version_number := p_rslt_object_version_number;
7792: --
7804: --
7805: -- date of birth action item
7806: --
7807: if g_debug then
7808: hr_utility.set_location('DOB action item ' || l_proc, 20);
7809: end if;
7810: --
7811: l_actn_typ_id := get_actn_typ_id
7812: (p_type_cd => 'BNFDOB'
7834: --
7835: 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
7836: --
7837: if g_debug then
7838: hr_utility.set_location('DOB rqd flag is Y', 10);
7839: end if;
7840: --
7841: --
7842: -- check if person has a date of birth entry
7876: --
7877: else
7878: --
7879: if g_debug then
7880: hr_utility.set_location('DOB rqd flag is N', 10);
7881: end if;
7882: --
7883: -- bnf_dob_rqd_flag is 'N';
7884: -- if date_track mode is updating and designation at plan level
7904: --
7905: -- legislative code (social security number/national identifier) action item
7906: --
7907: if g_debug then
7908: hr_utility.set_location('SSN action item ' || l_proc, 35);
7909: end if;
7910: --
7911: l_actn_typ_id := get_actn_typ_id
7912: (p_type_cd => 'BNFSSN'
7934: --
7935: 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
7936: --
7937: if g_debug then
7938: hr_utility.set_location('SSN rqd flag is Y', 10);
7939: end if;
7940: --
7941: -- check if person has a national identifier entry
7942: --
7972: --
7973: else
7974: --
7975: if g_debug then
7976: hr_utility.set_location('SSN rqd flag is N', 10);
7977: end if;
7978: --
7979: -- bnf_legv_id_rqd_flag is 'N'
7980: -- if date_track mode is updating and designation at plan level
8026: --
8027: 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
8028: --
8029: if g_debug then
8030: hr_utility.set_location('ADDR rqd flag is Y', 10);
8031: end if;
8032: --
8033: -- check if person has an address
8034: --
8065: --
8066: else
8067: --
8068: if g_debug then
8069: hr_utility.set_location('ADDR rqd flag is N', 10);
8070: end if;
8071: --
8072: -- bnf_adrs_rqd_flag is 'N'
8073: -- if date_track mode is updating and designation at plan level
8122: if ( g_bnf_pl_rec.bnf_dsge_mnr_ttee_rqd_flag = 'Y' or l_bnf_actn_typ_dfnd ) and
8123: l_dob_found = TRUE then
8124: --
8125: if g_debug then
8126: hr_utility.set_location('TTEE rqd flag is Y and DOB found', 10);
8127: end if;
8128: --
8129: --
8130: -- check if the designated beneficiary is over age of 18
8157: --
8158: else
8159: --
8160: if g_debug then
8161: hr_utility.set_location('TTEE rqd flag is N or DOB not found', 10);
8162: end if;
8163: --
8164: -- bnf_dsge_mnr_ttee_rqd_flag is 'N'
8165: -- if date_track mode is updating and designation at plan level
8221: --
8222: if g_bnf_pl_rec.bnf_ctfn_rqd_flag = 'N' or l_bnf_actn_typ_dfnd then
8223: --
8224: if g_debug then
8225: hr_utility.set_location('Ctfn rqd flag is N', 10);
8226: end if;
8227: --
8228: -- Certifications are required for the plan.
8229: -- Check if there are any certifications defined for the plan that are
8245: --
8246: if l_ctfn_defined = TRUE then
8247: --
8248: if g_debug then
8249: hr_utility.set_location('Certifications defined', 10);
8250: end if;
8251: --
8252: l_rqd_data_found := check_bnf_ctfn
8253: (p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
8275: --
8276: if l_rqd_data_found = FALSE then
8277: --
8278: if g_debug then
8279: hr_utility.set_location('Ctfn rqmts not met', 10);
8280: end if;
8281: --
8282: -- Ctfn rqmts are not met or no ctfns were found. Check if ctfns
8283: -- exist for the person in ben_pl_bnf_ctfn_prvdd_f.
8287: --
8288: if c_ctfns_exist%notfound then
8289: --
8290: if g_debug then
8291: hr_utility.set_location('Person has no ctfns defined', 10);
8292: end if;
8293: --
8294: -- No ctfns exist for this person. Write new ctfns.
8295: --
8298: --
8299: -- start bug 5667528
8300: l_write_ctfn := TRUE;
8301: l_ff_ctfn_exits := FALSE;
8302: hr_utility.set_location (' l_ctfn_rec.ctrrl ' || l_ctfn_rec.ctrrl,
8303: 12.12
8304: );
8305:
8306: IF l_ctfn_rec.ctrrl IS NOT NULL
8338: p_assignment_id => asg_rec.assignment_id,
8339: p_organization_id => asg_rec.organization_id,
8340: p_effective_date => p_effective_date
8341: );
8342: hr_utility.set_location ( ' formula result '
8343: || l_outputs (l_outputs.FIRST).VALUE,
8344: 12.12
8345: );
8346:
8346:
8347: IF l_outputs (l_outputs.FIRST).VALUE <> 'Y'
8348: THEN
8349: l_write_ctfn := FALSE;
8350: hr_utility.set_location (' setting ctfn to false ', 12.12);
8351: END IF;
8352: END IF; -- end if l_ctfn_rec.ctrrl is not null
8353: IF l_write_ctfn = TRUE THEN
8354: l_ff_ctfn_exits := true;
8351: END IF;
8352: END IF; -- end if l_ctfn_rec.ctrrl is not null
8353: IF l_write_ctfn = TRUE THEN
8354: l_ff_ctfn_exits := true;
8355: hr_utility.set_location(' writing certification ',12.12);
8356:
8357: write_new_bnf_ctfn_item
8358: (p_pl_bnf_id => l_dsgn_bnf.pl_bnf_id
8359: ,p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
8375: end if; -- rqd_data_found
8376: --
8377: IF l_ff_ctfn_exits = FALSE
8378: THEN
8379: hr_utility.set_location
8380: ('Deleting enrollment action item when formula fails ',
8381: 12.12
8382: );
8383:
8449: --
8450: end loop; -- bnf loop
8451: --
8452: if g_debug then
8453: hr_utility.set_location ('Leaving '||l_proc,10);
8454: end if;
8455: --
8456: exception
8457: when others then
8455: --
8456: exception
8457: when others then
8458: if g_debug then
8459: hr_utility.set_location('Exception Raised '||l_proc, 10);
8460: end if;
8461:
8462: p_rslt_object_version_number :=l_rslt_object_version_number ;
8463: p_bnf_actn_warning := null;
8536: begin
8537: --
8538: if g_debug then
8539: l_proc := g_package||'.determine_bnf_actn_items';
8540: hr_utility.set_location ('Entering '||l_proc,10);
8541: end if;
8542: --
8543: l_rslt_object_version_number := p_rslt_object_version_number;
8544:
8585: --
8586: if l_bnf_found = FALSE then
8587: --
8588: if g_debug then
8589: hr_utility.set_location('Beneficiaries not designated', 10);
8590: end if;
8591: --
8592: --
8593: -- There are no beneficiaries designated
8636: --
8637: -- Beneficiaries found
8638: --
8639: if g_debug then
8640: hr_utility.set_location('Beneficiaries found', 10);
8641: end if;
8642: --
8643: --
8644: -- Set the p_rqd_data_found = TRUE so that the proc below closes an open
8663: --
8664: end if;
8665: --
8666: if g_debug then
8667: hr_utility.set_location ('Leaving ' ||l_proc,10);
8668: end if;
8669: --
8670: exception
8671: --
8680: p_bnf_actn_item_open := TRUE;
8681: p_hack := 'Y';
8682: --
8683: if g_debug then
8684: hr_utility.set_location('Bnf dsgn not specified.', 10);
8685: end if;
8686: if g_debug then
8687: hr_utility.set_location('Leaving : ' || l_proc, 10);
8688: end if;
8683: if g_debug then
8684: hr_utility.set_location('Bnf dsgn not specified.', 10);
8685: end if;
8686: if g_debug then
8687: hr_utility.set_location('Leaving : ' || l_proc, 10);
8688: end if;
8689: --
8690: when others then
8691: if g_debug then
8688: end if;
8689: --
8690: when others then
8691: if g_debug then
8692: hr_utility.set_location('Exception Raised '||l_proc, 10);
8693: end if;
8694:
8695: p_rslt_object_version_number :=l_rslt_object_version_number ;
8696: p_bnf_actn_item_open := null;
8911: begin
8912: --
8913: if g_debug then
8914: l_proc := g_package||'.determine_additional_bnf';
8915: hr_utility.set_location ('Entering '||l_proc,10);
8916: end if;
8917: --
8918:
8919: l_rslt_object_version_number := p_rslt_object_version_number;
8976: --
8977: if l_meets_rqmt = TRUE then
8978: --
8979: if g_debug then
8980: hr_utility.set_location('rqmts met for BNFADDNL', 10);
8981: end if;
8982: --
8983: -- Set the p_rqd_data_found = TRUE so that the procedure closes an open
8984: -- action item of type BNFADDNL, if found.
8999: --
9000: else
9001: --
9002: if g_debug then
9003: hr_utility.set_location('rqmts not met for BNFADDNL', 10);
9004: end if;
9005:
9006: --
9007: -- Requirements are not met. Set the p_rqd_data_found = FALSE so that the
9039: --
9040: -- The primary bnf is not a spouse.
9041: --
9042: if g_debug then
9043: hr_utility.set_location('Contact type ' || l_bnf_rec.contact_type ||
9044: ' is not a spouse', 10);
9045: end if;
9046: --
9047: -- Check if an BNFSCCTFN actn item already exists for the bnf.
9074: --
9075: -- no BNFSCCTFN action item yet so create a new entry
9076: --
9077: if g_debug then
9078: hr_utility.set_location('No BNFSCCTFN actn item.', 10);
9079: end if;
9080: --
9081: write_new_action_item
9082: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
9141: -- A BNFSCCTFN action item was written in an earlier run. Check if the
9142: -- NSC ctfn has been provided and if yes, close the action item.
9143: --
9144: if g_debug then
9145: hr_utility.set_location('BNFSCCTFN actn item found', 10);
9146: end if;
9147: --
9148: open c_nsc_prvdd(l_bnf_rec.pl_bnf_id);
9149: fetch c_nsc_prvdd into l_dummy;
9152: --
9153: -- Ctfn provided. Close action item.
9154: --
9155: if g_debug then
9156: hr_utility.set_location('SCCTFN provided. Close actn item.', 10);
9157: end if;
9158: --
9159: set_cmpltd_dt
9160: (p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
9173: --
9174: -- Ctfn not provided. Open action item if already closed.
9175: --
9176: if g_debug then
9177: hr_utility.set_location('CTFN not prvdd. Open actn item.', 10);
9178: end if;
9179: --
9180: if l_cmpltd_dt is null then
9181: set_cmpltd_dt
9203: --
9204: end loop; -- outer loop
9205: --
9206: if g_debug then
9207: hr_utility.set_location ('Leaving ' ||l_proc,10);
9208: end if;
9209: --
9210: exception
9211: when others then
9209: --
9210: exception
9211: when others then
9212: if g_debug then
9213: hr_utility.set_location('Exception Raised '||l_proc, 10);
9214: end if;
9215: p_rslt_object_version_number :=l_rslt_object_version_number ;
9216: p_bnf_actn_warning :=l_bnf_actn_warning ;
9217: raise;
9244:
9245: --
9246: begin
9247: --
9248: g_debug := hr_utility.debug_enabled;
9249: if g_debug then
9250: l_proc := g_package || '.process_bnf_actn_items';
9251: hr_utility.set_location ('Entering '||l_proc,10);
9252: end if;
9247: --
9248: g_debug := hr_utility.debug_enabled;
9249: if g_debug then
9250: l_proc := g_package || '.process_bnf_actn_items';
9251: hr_utility.set_location ('Entering '||l_proc,10);
9252: end if;
9253: --
9254:
9255: l_rslt_object_version_number:= p_rslt_object_version_number ;
9318: raise hr_api.validate_enabled;
9319: end if;
9320: --
9321: if g_debug then
9322: hr_utility.set_location ('Leaving '||l_proc, 10);
9323: end if;
9324: --
9325: exception
9326: --
9328: then
9329: --
9330: rollback to process_bnf_actn_items;
9331: if g_debug then
9332: hr_utility.set_location ('Leaving '||l_proc, 10);
9333: end if;
9334: --
9335: when others then
9336: if g_debug then
9333: end if;
9334: --
9335: when others then
9336: if g_debug then
9337: hr_utility.set_location('Exception Raised '||l_proc, 10);
9338: end if;
9339:
9340: p_rslt_object_version_number :=l_rslt_object_version_number ;
9341: p_suspend_flag :=l_suspend_flag ;
9462: l_interim boolean := false ;
9463: --
9464: begin
9465: --
9466: g_debug := hr_utility.debug_enabled;
9467: if g_debug then
9468: l_proc := g_package||'.determine_action_items';
9469: hr_utility.set_location ('Entering '||l_proc,10);
9470: end if;
9465: --
9466: g_debug := hr_utility.debug_enabled;
9467: if g_debug then
9468: l_proc := g_package||'.determine_action_items';
9469: hr_utility.set_location ('Entering '||l_proc,10);
9470: end if;
9471: --
9472: l_rslt_object_version_number :=p_rslt_object_version_number ;
9473:
9516: fetch c_waive_opt_flag into l_waive_flag;
9517: close c_waive_opt_flag;
9518:
9519: if g_debug then
9520: hr_utility.set_location ('waive_flag = '||l_waive_flag,9);
9521: end if;
9522:
9523: --
9524: -- un comment the following code 3771346 /*
9534: --end 3771346 */
9535: end if;
9536:
9537: if g_debug then
9538: hr_utility.set_location ('waive_flag = '||l_waive_flag,10);
9539: end if;
9540: --BUG 3042379 We don't need to process the action items for the
9541: --interim enrollment.
9542:
9644: --
9645: -- BUG 3042379 Don't create action items for the interim
9646: else
9647: if g_debug then
9648: hr_utility.set_location ('Interim Enrt- No action item created PEN '||p_prtt_enrt_rslt_id,110);
9649: end if;
9650: end if ;
9651: -- BUG 3042379 Don't create action items for the interim
9652: --
9698: --
9699: p_suspend_flag := l_suspend_flag;
9700: --
9701: if g_debug then
9702: hr_utility.set_location ('Leaving ' ||l_proc,10);
9703: end if;
9704: --
9705: exception
9706: --
9711: ROLLBACK TO determine_action_items;
9712: --
9713: when others then
9714: if g_debug then
9715: hr_utility.set_location('Exception Raised '||l_proc, 10);
9716: end if;
9717: p_rslt_object_version_number :=l_rslt_object_version_number ;
9718: p_suspend_flag :=l_suspend_flag ;
9719: p_ctfn_actn_warning := null ;