[Home] [Help]
72: l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
73: --
74: begin
75: --
76: hr_utility.set_location('Entering:'|| l_proc, 10);
77: --
78: -- Issue a savepoint if operating in validation only mode
79: --
80: savepoint create_ELIG_DPNT;
78: -- Issue a savepoint if operating in validation only mode
79: --
80: savepoint create_ELIG_DPNT;
81: --
82: hr_utility.set_location(l_proc, 20);
83: --
84: -- Process Logic
85: --
86: begin
277: -- End of API User Hook for the after hook of create_ELIG_DPNT
278: --
279: end;
280: --
281: hr_utility.set_location(l_proc, 60);
282: --
283: -- When in validation only mode raise the Validate_Enabled exception
284: --
285: if p_validate then
290: --
291: p_elig_dpnt_id := l_elig_dpnt_id;
292: p_object_version_number := l_object_version_number;
293: --
294: hr_utility.set_location(' Leaving:'||l_proc, 70);
295: --
296: exception
297: --
298: when hr_api.validate_enabled then
307: -- when validation only mode is being used.)
308: --
309: p_elig_dpnt_id := null;
310: p_object_version_number := null;
311: hr_utility.set_location(' Leaving:'||l_proc, 80);
312: --
313: when others then
314: --
315: -- A validation or unexpected error has occured
399: Cursor C_Sel1 is select ben_elig_dpnt_s.nextval from sys.dual;
400: --
401: begin
402: --
403: hr_utility.set_location('Entering:'|| l_proc, 10);
404: --
405: -- Issue a savepoint if operating in validation only mode
406: --
407: savepoint create_perf_ELIG_DPNT;
413: l_object_version_number := 1;
414: --
415: ben_egd_shd.g_api_dml := true; -- Set the api dml status
416: --
417: hr_utility.set_location('Insert EGD: '||l_proc, 5);
418: insert into ben_elig_dpnt
419: ( elig_dpnt_id,
420: create_dt,
421: elig_strt_dt,
520: p_program_id,
521: p_program_update_date,
522: l_object_version_number
523: ) RETURNING elig_dpnt_id into l_elig_dpnt_id;
524: hr_utility.set_location('Dn Insert: '||l_proc, 5);
525: --
526: -- Call life event trigger
527: --
528: l_old_rec.business_group_id := null;
557: --
558: p_elig_dpnt_id := l_elig_dpnt_id;
559: p_object_version_number := l_object_version_number;
560: --
561: hr_utility.set_location(' Leaving:'||l_proc, 70);
562: --
563: exception
564: --
565: when hr_api.validate_enabled then
574: -- when validation only mode is being used.)
575: --
576: p_elig_dpnt_id := null;
577: p_object_version_number := null;
578: hr_utility.set_location(' Leaving:'||l_proc, 80);
579: --
580: when others then
581: --
582: -- A validation or unexpected error has occured
652: l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
653: --
654: begin
655: --
656: hr_utility.set_location('Entering:'|| l_proc, 10);
657: --
658: -- Issue a savepoint if operating in validation only mode
659: --
660: savepoint update_ELIG_DPNT;
658: -- Issue a savepoint if operating in validation only mode
659: --
660: savepoint update_ELIG_DPNT;
661: --
662: hr_utility.set_location(l_proc, 20);
663: --
664: -- Process Logic
665: --
666: l_object_version_number := p_object_version_number;
860: -- End of API User Hook for the after hook of update_ELIG_DPNT
861: --
862: end;
863: --
864: hr_utility.set_location(l_proc, 60);
865: --
866: -- When in validation only mode raise the Validate_Enabled exception
867: --
868: if p_validate then
872: -- Set all output arguments
873: --
874: p_object_version_number := l_object_version_number;
875: --
876: hr_utility.set_location(' Leaving:'||l_proc, 70);
877: --
878: exception
879: --
880: when hr_api.validate_enabled then
887: -- Only set output warning arguments
888: -- (Any key or derived arguments must be set to null
889: -- when validation only mode is being used.)
890: --
891: hr_utility.set_location(' Leaving:'||l_proc, 80);
892: --
893: when others then
894: --
895: -- A validation or unexpected error has occured
974: l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
975: --
976: begin
977: --
978: hr_utility.set_location('Entering:'|| l_proc, 10);
979: --
980: -- Issue a savepoint if operating in validation only mode
981: --
982: savepoint update_perf_ELIG_DPNT;
1130: l_new_rec.ovrdn_flag := p_ovrdn_flag;
1131: l_new_rec.create_dt := p_create_dt;
1132: l_new_rec.per_in_ler_id := p_per_in_ler_id; --Bug 5630251
1133: --
1134: hr_utility.set_location(' Old per_in_ler_id from api ' || l_old_rec.per_in_ler_id , 9876 );
1135:
1136: ben_egd_ler.ler_chk(l_old_rec,l_new_rec,p_effective_date);
1137: --
1138: ben_egd_shd.g_api_dml := false; -- Unset the api dml status
1146: -- Set all output arguments
1147: --
1148: p_object_version_number := l_object_version_number;
1149: --
1150: hr_utility.set_location(' Leaving:'||l_proc, 70);
1151: --
1152: exception
1153: --
1154: when hr_api.validate_enabled then
1161: -- Only set output warning arguments
1162: -- (Any key or derived arguments must be set to null
1163: -- when validation only mode is being used.)
1164: --
1165: hr_utility.set_location(' Leaving:'||l_proc, 80);
1166: --
1167: when others then
1168: --
1169: -- A validation or unexpected error has occured
1191: l_object_version_number ben_elig_dpnt.object_version_number%TYPE;
1192: --
1193: begin
1194: --
1195: hr_utility.set_location('Entering:'|| l_proc, 10);
1196: --
1197: -- Issue a savepoint if operating in validation only mode
1198: --
1199: savepoint delete_ELIG_DPNT;
1197: -- Issue a savepoint if operating in validation only mode
1198: --
1199: savepoint delete_ELIG_DPNT;
1200: --
1201: hr_utility.set_location(l_proc, 20);
1202: --
1203: -- Process Logic
1204: --
1205: l_object_version_number := p_object_version_number;
1253: -- End of API User Hook for the after hook of delete_ELIG_DPNT
1254: --
1255: end;
1256: --
1257: hr_utility.set_location(l_proc, 60);
1258: --
1259: -- When in validation only mode raise the Validate_Enabled exception
1260: --
1261: if p_validate then
1261: if p_validate then
1262: raise hr_api.validate_enabled;
1263: end if;
1264: --
1265: hr_utility.set_location(' Leaving:'||l_proc, 70);
1266: --
1267: exception
1268: --
1269: when hr_api.validate_enabled then
1305: l_proc varchar2(72) := g_package||'lck';
1306: --
1307: begin
1308: --
1309: hr_utility.set_location('Entering:'|| l_proc, 10);
1310: --
1311: ben_egd_shd.lck
1312: (
1313: p_elig_dpnt_id => p_elig_dpnt_id
1313: p_elig_dpnt_id => p_elig_dpnt_id
1314: ,p_object_version_number => p_object_version_number
1315: );
1316: --
1317: hr_utility.set_location(' Leaving:'||l_proc, 70);
1318: --
1319: end lck;
1320: --
1321: -- ----------------------------------------------------------------------------
1353: begin
1354: --
1355: -- Similar code exists in BENEDSGB.fmb >> EPE.POST-QUERY. Make changes
1356: -- at both the places
1357: hr_utility.set_location('Entering return_sspnd_if_interim', 5);
1358: open c_is_pen_interim (p_interim_pen_id, p_effective_date);
1359: --
1360: fetch c_is_pen_interim into l_sspndd_pen_id;
1361: --
1371: end if;
1372: --
1373: close c_is_pen_interim;
1374: --
1375: hr_utility.set_location('l_sspndd_pen_id = ' || l_sspndd_pen_id, 15);
1376: hr_utility.set_location('Leaving return_sspnd_if_interim', 10);
1377: return l_sspndd_pen_id;
1378: --
1379: end return_sspnd_if_interim;
1372: --
1373: close c_is_pen_interim;
1374: --
1375: hr_utility.set_location('l_sspndd_pen_id = ' || l_sspndd_pen_id, 15);
1376: hr_utility.set_location('Leaving return_sspnd_if_interim', 10);
1377: return l_sspndd_pen_id;
1378: --
1379: end return_sspnd_if_interim;
1380: --
1470: l_proc varchar2(72) := g_package||'.process_dependent';
1471: --
1472: begin
1473: --
1474: hr_utility.set_location('Entering:'|| l_proc, 10);
1475: --
1476: -- Issue a savepoint if operating in validation only mode
1477: --
1478: savepoint process_dependent;
1488: p_benefit_action_id => null);
1489: --
1490: end if;
1491: --
1492: hr_utility.set_location(l_proc, 15);
1493: --
1494: open c_egd;
1495: fetch c_egd into l_egd_rec;
1496: if c_egd%found then
1500: --
1501: -- Bug 5486397
1502: l_egd_rec.prtt_enrt_rslt_id := return_sspnd_if_interim (l_egd_rec.prtt_enrt_rslt_id, p_effective_date);
1503: --
1504: hr_utility.set_location(l_proc, 20);
1505: --
1506: if l_egd_rec.elig_cvrd_dpnt_id is not null then
1507: --
1508: hr_utility.set_location(l_proc, 25);
1504: hr_utility.set_location(l_proc, 20);
1505: --
1506: if l_egd_rec.elig_cvrd_dpnt_id is not null then
1507: --
1508: hr_utility.set_location(l_proc, 25);
1509: --
1510: open c_pdp;
1511: fetch c_pdp into l_pdp_rec;
1512: if c_pdp%found then
1510: open c_pdp;
1511: fetch c_pdp into l_pdp_rec;
1512: if c_pdp%found then
1513: --
1514: hr_utility.set_location(l_proc, 30);
1515: --
1516: l_pdp_found := true;
1517: --
1518: if l_pdp_rec.effective_start_date = p_effective_date then
1528: end if;
1529: --
1530: close c_pdp;
1531: --
1532: hr_utility.set_location(l_proc, 35);
1533: --
1534: end if;
1535: --
1536: -- Note: We are making coverge through date as EOT, if it is null.
1545: l_cvg_thru_dt := p_cvg_thru_dt;
1546: --
1547: end if;
1548: --
1549: hr_utility.set_location(l_proc, 40);
1550: --
1551: -- Cases start. Each case has their own if statement.
1552: --
1553: if not l_egd_found then
1554: --
1555: -- Eligible dependent record not found.
1556: -- ERROR out.
1557: --
1558: hr_utility.set_location(l_proc, 45);
1559: --
1560: fnd_message.set_name('BEN','BEN_92322_EGD_NOT_FOUND');
1561: fnd_message.raise_error;
1562: --
1566: -- user is trying to end coverage. But here the covered dependent
1567: -- record is not found, so we cannot end coverage.
1568: -- ERROR out.
1569: --
1570: hr_utility.set_location(l_proc, 50);
1571: --
1572: fnd_message.set_name('BEN','BEN_92323_CVG_CANNOT_BE_ENDED');
1573: fnd_message.raise_error;
1574: --
1579: -- is null. In both the case, the user is trying to end the coverage
1580: -- for the dependent. As the covered dependent record is found,
1581: -- we continue with the processing.
1582: --
1583: hr_utility.set_location(l_proc, 55);
1584: --
1585: if p_effective_date > l_pdp_rec.cvg_strt_dt then
1586: --
1587: -- Coverage has started, so we cannot delete it. Just update
1586: --
1587: -- Coverage has started, so we cannot delete it. Just update
1588: -- the record with the coverage through date and the per_in_ler_id.
1589: --
1590: hr_utility.set_location(l_proc, 60);
1591: --
1592: ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1593: p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1594: p_effective_start_date => p_effective_start_date,
1643: --
1644: -- Coverage has not started, so purge the record.
1645: -- Remove the link from eligible dependent record.
1646: --
1647: hr_utility.set_location(l_proc, 65);
1648: --
1649: ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt(
1650: p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1651: p_effective_start_date => l_effective_start_date,
1705: -- we can re-open the covered dependent record and use it.
1706: -- Update the record the coverage through date as EOT and the
1707: -- new per in ler id.
1708: --
1709: hr_utility.set_location(l_proc, 70);
1710: --
1711: ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1712: p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1713: p_effective_start_date => p_effective_start_date,
1767: -- Create the covered dependent record with the coverage start date
1768: -- and coverge through date.
1769: -- Update the eligible dependent record with the covered dependent id.
1770: --
1771: hr_utility.set_location(l_proc, 75);
1772: --
1773: ben_elig_cvrd_dpnt_api.create_elig_cvrd_dpnt
1774: (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
1775: p_effective_start_date => p_effective_start_date,
1833: end if;
1834: --
1835: -- End of cases.
1836: --
1837: hr_utility.set_location('Leaving '||l_proc, 75);
1838: --
1839: -- When in validation only mode raise the Validate_Enabled exception
1840: --
1841: if p_validate then
1895: --
1896: begin
1897: --
1898: fnd_msg_pub.initialize;
1899: hr_utility.set_location('Entering:'|| l_proc, 10);
1900: --
1901: -- Issue a savepoint if operating in validation only mode
1902: --
1903: savepoint process_dependent_w;
1913: p_benefit_action_id => null);
1914: --
1915: end if;
1916: --
1917: hr_utility.set_location(l_proc, 20);
1918: --
1919: if upper(p_validate) = 'TRUE'
1920: then
1921: l_validate := TRUE;
1929: else
1930: l_multi_row_actn := FALSE;
1931: end if;
1932: --
1933: hr_utility.set_location(l_proc, 30);
1934: --
1935: process_dependent(
1936: p_validate => l_validate
1937: ,p_elig_dpnt_id => p_elig_dpnt_id
1956: p_effective_start_date := l_effective_start_date;
1957: p_effective_end_date := l_effective_end_date;
1958: p_object_version_number := l_object_version_number;
1959: --
1960: hr_utility.set_location(' Leaving:'||l_proc, 40);
1961:
1962: exception
1963: --
1964: when hr_api.validate_enabled then
1979: when others then
1980: --
1981: -- A validation or unexpected error has occured
1982: --
1983: hr_utility.set_location(' Exception in :'||l_proc, 100);
1984: --Bug 4387247
1985: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
1986: fnd_message.set_token('2',substr(sqlerrm,1,200));
1987: fnd_msg_pub.add; -- bug fix 4111762
2002: procedure store_crt_ord_warning_ss(p_person_id in number,
2003: p_crt_ord_warning in varchar2) is
2004: l_proc varchar2(72) := g_package||'.store_crt_ord_warning_ss';
2005: begin
2006: hr_utility.set_location('Entering:'|| l_proc, 10);
2007: --
2008: if p_person_id is not null and p_crt_ord_warning is not null then
2009: if p_crt_ord_warning = 'CLEAR' then
2010: DELETE ben_online_warnings
2014: VALUES (p_person_id, p_crt_ord_warning);
2015: end if;
2016: end if;
2017: --
2018: hr_utility.set_location('Leaving'|| l_proc, 20);
2019: exception
2020: when others then
2021: --
2022: -- An unexpected error has occured
2020: when others then
2021: --
2022: -- An unexpected error has occured
2023: --
2024: hr_utility.set_location(' Exception in :'||l_proc, 100);
2025: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2026: fnd_message.set_token('2',substr(sqlerrm,1,200));
2027: fnd_msg_pub.add;
2028: end store_crt_ord_warning_ss;
2097: --
2098: l_comp_obj_list varchar2(2000) := null;
2099: l_proc varchar2(72) := g_package||'.store_crt_ord_warng_DDNA';
2100: begin
2101: hr_utility.set_location('Entering:'|| l_proc, 10);
2102: --
2103: for l_results in c_crt_ordr_DDNA loop
2104: if l_comp_obj_list is null then
2105: l_comp_obj_list := l_results.comp_obj_name;
2115: INSERT INTO ben_online_warnings(session_id, message_text)
2116: VALUES (p_person_id, fnd_message.get);
2117: end if;
2118: --
2119: hr_utility.set_location('Leaving'|| l_proc, 20);
2120: exception
2121: when others then
2122: --
2123: -- An unexpected error has occured
2121: when others then
2122: --
2123: -- An unexpected error has occured
2124: --
2125: hr_utility.set_location(' Exception in :'||l_proc, 100);
2126: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2127: fnd_message.set_token('2',substr(sqlerrm,1,200));
2128: fnd_msg_pub.add;
2129: end store_crt_ord_warng_DDNA;
2143: l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_1';
2144: --
2145: begin
2146: --
2147: hr_utility.set_location('Entering '||l_proc, 10);
2148: --
2149: open c_egd;
2150: fetch c_egd into p_elig_dpnt_rec;
2151: --
2190: l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_2';
2191: --
2192: begin
2193: --
2194: hr_utility.set_location('Entering '||l_proc, 10);
2195: --
2196: open c_pdp;
2197: fetch c_pdp into p_elig_dpnt_rec;
2198: --
2243: l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_3';
2244: --
2245: begin
2246: --
2247: hr_utility.set_location('Entering '||l_proc, 10);
2248: --
2249: open c_pen;
2250: fetch c_pen into p_elig_dpnt_rec;
2251: --
2400: l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_4';
2401: --
2402: begin
2403: --
2404: hr_utility.set_location('Entering '||l_proc, 10);
2405: --
2406: -- Check if the elig dependent exists
2407: --
2408: open c_eligdpnt_exists;
2439: -- performant code. However if for some special cases no current
2440: -- eligibility rows are found for the comp object then revert to the
2441: -- original unperformant cursor.
2442: --
2443: hr_utility.set_location(l_proc, 20);
2444: open c_pilepodets
2445: (c_effective_date => p_effective_date
2446: ,c_per_in_ler_id => p_per_in_ler_id
2447: ,c_pl_id => p_pl_id
2486: close c_oipl_max_create_dt;
2487: --
2488: end if;
2489: --
2490: hr_utility.set_location(l_proc, 25);
2491: --
2492: else
2493: --
2494: hr_utility.set_location(l_proc, 21);
2490: hr_utility.set_location(l_proc, 25);
2491: --
2492: else
2493: --
2494: hr_utility.set_location(l_proc, 21);
2495: open c_oipl_max_create_dt
2496: (c_dpnt_person_id => p_dpnt_person_id
2497: ,c_effective_date => p_effective_date
2498: ,c_oipl_id => p_oipl_id
2499: ,c_pgm_id => nvl(p_pgm_id, -1)
2500: );
2501: fetch c_oipl_max_create_dt into l_elig_dpnt_id;
2502: close c_oipl_max_create_dt;
2503: hr_utility.set_location(l_proc, 26);
2504: --
2505: end if;
2506: --
2507: else
2531: fetch c_pl_max_create_dt into l_elig_dpnt_id;
2532: close c_pl_max_create_dt;
2533: --
2534: end if;
2535: hr_utility.set_location(l_proc, 30);
2536: --
2537: end if;
2538: --
2539: end if;
2541: close c_eligdpnt_exists;
2542: --
2543: if l_elig_dpnt_id is not null then
2544: --
2545: hr_utility.set_location(l_proc, 40);
2546: --
2547: l_return := get_elig_dpnt_rec(p_elig_dpnt_id => l_elig_dpnt_id,
2548: p_elig_dpnt_rec => p_elig_dpnt_rec);
2549: --
2548: p_elig_dpnt_rec => p_elig_dpnt_rec);
2549: --
2550: end if;
2551: --
2552: hr_utility.set_location('Leaving '||l_proc, 10);
2553: --
2554: return l_return;
2555: --
2556: end get_elig_dpnt_rec;
2603: l_proc varchar2(72) := g_package||'get_elig_per_id';
2604: --
2605: begin
2606: --
2607: hr_utility.set_location('Entering '||l_proc, 10);
2608: --
2609: p_elig_per_id := null;
2610: p_elig_per_opt_id := null;
2611: --
2610: p_elig_per_opt_id := null;
2611: --
2612: if p_oipl_id is not null then
2613: --
2614: hr_utility.set_location(l_proc, 20);
2615: --
2616: open c_oipl;
2617: fetch c_oipl into p_elig_per_id, p_elig_per_opt_id;
2618: close c_oipl;
2618: close c_oipl;
2619: --
2620: else
2621: --
2622: hr_utility.set_location(l_proc, 30);
2623: --
2624: open c_pl;
2625: fetch c_pl into p_elig_per_id;
2626: close c_pl;
2626: close c_pl;
2627: --
2628: end if;
2629: --
2630: hr_utility.set_location('Leaving '||l_proc, 10);
2631: --
2632: end get_elig_per_id;
2633: --
2634: -- Bug No 4931912
2694: l_flag varchar2(2);
2695: l_rltp_start_date date;
2696: l_rltp_end_date date;
2697: begin
2698: hr_utility.set_location('Entering '||l_proc, 10);
2699: open c_rltp;
2700: fetch c_rltp into l_dpnt_rltp_row;
2701: close c_rltp;
2702:
2700: fetch c_rltp into l_dpnt_rltp_row;
2701: close c_rltp;
2702:
2703: if(l_dpnt_rltp_row.date_start is not null and l_dpnt_rltp_row.personal_flag = 'Y' and (p_rltp_type <> l_dpnt_rltp_row.contact_type) ) then
2704: hr_utility.set_location('Date not null ', 10);
2705: open c_chk_enrt(l_dpnt_rltp_row.date_start,nvl(l_dpnt_rltp_row.date_end,hr_api.g_eot));
2706: fetch c_chk_enrt into l_flag;
2707: if(c_chk_enrt%found) then
2708: hr_utility.set_location('Enrollment Rec exists for Dep ', 10);
2704: hr_utility.set_location('Date not null ', 10);
2705: open c_chk_enrt(l_dpnt_rltp_row.date_start,nvl(l_dpnt_rltp_row.date_end,hr_api.g_eot));
2706: fetch c_chk_enrt into l_flag;
2707: if(c_chk_enrt%found) then
2708: hr_utility.set_location('Enrollment Rec exists for Dep ', 10);
2709: fnd_message.set_name('BEN','BEN_94714_RELATION_TYP_UPD');
2710: fnd_message.raise_error;
2711: --raise error message;
2712: end if;
2711: --raise error message;
2712: end if;
2713: close c_chk_enrt;
2714: end if;
2715: hr_utility.set_location('Leaving '||l_proc, 10);
2716: end chk_enrt_for_dpnt;
2717:
2718: end ben_ELIG_DPNT_api;