[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: --
1469: l_proc varchar2(72) := g_package||'.process_dependent';
1470: --
1471: begin
1472: --
1473: hr_utility.set_location('Entering:'|| l_proc, 10);
1474: --
1475: -- Issue a savepoint if operating in validation only mode
1476: --
1477: savepoint process_dependent;
1487: p_benefit_action_id => null);
1488: --
1489: end if;
1490: --
1491: hr_utility.set_location(l_proc, 15);
1492: --
1493: open c_egd;
1494: fetch c_egd into l_egd_rec;
1495: if c_egd%found then
1499: --
1500: -- Bug 5486397
1501: l_egd_rec.prtt_enrt_rslt_id := return_sspnd_if_interim (l_egd_rec.prtt_enrt_rslt_id, p_effective_date);
1502: --
1503: hr_utility.set_location(l_proc, 20);
1504: --
1505: if l_egd_rec.elig_cvrd_dpnt_id is not null then
1506: --
1507: hr_utility.set_location(l_proc, 25);
1503: hr_utility.set_location(l_proc, 20);
1504: --
1505: if l_egd_rec.elig_cvrd_dpnt_id is not null then
1506: --
1507: hr_utility.set_location(l_proc, 25);
1508: --
1509: open c_pdp;
1510: fetch c_pdp into l_pdp_rec;
1511: if c_pdp%found then
1509: open c_pdp;
1510: fetch c_pdp into l_pdp_rec;
1511: if c_pdp%found then
1512: --
1513: hr_utility.set_location(l_proc, 30);
1514: --
1515: l_pdp_found := true;
1516: --
1517: if l_pdp_rec.effective_start_date = p_effective_date then
1527: end if;
1528: --
1529: close c_pdp;
1530: --
1531: hr_utility.set_location(l_proc, 35);
1532: --
1533: end if;
1534: --
1535: -- Note: We are making coverge through date as EOT, if it is null.
1544: l_cvg_thru_dt := p_cvg_thru_dt;
1545: --
1546: end if;
1547: --
1548: hr_utility.set_location(l_proc, 40);
1549: --
1550: -- Cases start. Each case has their own if statement.
1551: --
1552: if not l_egd_found then
1553: --
1554: -- Eligible dependent record not found.
1555: -- ERROR out.
1556: --
1557: hr_utility.set_location(l_proc, 45);
1558: --
1559: fnd_message.set_name('BEN','BEN_92322_EGD_NOT_FOUND');
1560: fnd_message.raise_error;
1561: --
1565: -- user is trying to end coverage. But here the covered dependent
1566: -- record is not found, so we cannot end coverage.
1567: -- ERROR out.
1568: --
1569: hr_utility.set_location(l_proc, 50);
1570: --
1571: fnd_message.set_name('BEN','BEN_92323_CVG_CANNOT_BE_ENDED');
1572: fnd_message.raise_error;
1573: --
1578: -- is null. In both the case, the user is trying to end the coverage
1579: -- for the dependent. As the covered dependent record is found,
1580: -- we continue with the processing.
1581: --
1582: hr_utility.set_location(l_proc, 55);
1583: --
1584: if p_effective_date > l_pdp_rec.cvg_strt_dt then
1585: --
1586: -- Coverage has started, so we cannot delete it. Just update
1585: --
1586: -- Coverage has started, so we cannot delete it. Just update
1587: -- the record with the coverage through date and the per_in_ler_id.
1588: --
1589: hr_utility.set_location(l_proc, 60);
1590: --
1591: ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1592: p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1593: p_effective_start_date => p_effective_start_date,
1642: --
1643: -- Coverage has not started, so purge the record.
1644: -- Remove the link from eligible dependent record.
1645: --
1646: hr_utility.set_location(l_proc, 65);
1647: --
1648: ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt(
1649: p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1650: p_effective_start_date => l_effective_start_date,
1698: -- we can re-open the covered dependent record and use it.
1699: -- Update the record the coverage through date as EOT and the
1700: -- new per in ler id.
1701: --
1702: hr_utility.set_location(l_proc, 70);
1703: --
1704: ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
1705: p_elig_cvrd_dpnt_id => l_egd_rec.elig_cvrd_dpnt_id,
1706: p_effective_start_date => p_effective_start_date,
1760: -- Create the covered dependent record with the coverage start date
1761: -- and coverge through date.
1762: -- Update the eligible dependent record with the covered dependent id.
1763: --
1764: hr_utility.set_location(l_proc, 75);
1765: --
1766: ben_elig_cvrd_dpnt_api.create_elig_cvrd_dpnt
1767: (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
1768: p_effective_start_date => p_effective_start_date,
1826: end if;
1827: --
1828: -- End of cases.
1829: --
1830: hr_utility.set_location('Leaving '||l_proc, 75);
1831: --
1832: -- When in validation only mode raise the Validate_Enabled exception
1833: --
1834: if p_validate then
1888: --
1889: begin
1890: --
1891: fnd_msg_pub.initialize;
1892: hr_utility.set_location('Entering:'|| l_proc, 10);
1893: --
1894: -- Issue a savepoint if operating in validation only mode
1895: --
1896: savepoint process_dependent_w;
1906: p_benefit_action_id => null);
1907: --
1908: end if;
1909: --
1910: hr_utility.set_location(l_proc, 20);
1911: --
1912: if upper(p_validate) = 'TRUE'
1913: then
1914: l_validate := TRUE;
1922: else
1923: l_multi_row_actn := FALSE;
1924: end if;
1925: --
1926: hr_utility.set_location(l_proc, 30);
1927: --
1928: process_dependent(
1929: p_validate => l_validate
1930: ,p_elig_dpnt_id => p_elig_dpnt_id
1949: p_effective_start_date := l_effective_start_date;
1950: p_effective_end_date := l_effective_end_date;
1951: p_object_version_number := l_object_version_number;
1952: --
1953: hr_utility.set_location(' Leaving:'||l_proc, 40);
1954:
1955: exception
1956: --
1957: when hr_api.validate_enabled then
1972: when others then
1973: --
1974: -- A validation or unexpected error has occured
1975: --
1976: hr_utility.set_location(' Exception in :'||l_proc, 100);
1977: --Bug 4387247
1978: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
1979: fnd_message.set_token('2',substr(sqlerrm,1,200));
1980: fnd_msg_pub.add; -- bug fix 4111762
1995: procedure store_crt_ord_warning_ss(p_person_id in number,
1996: p_crt_ord_warning in varchar2) is
1997: l_proc varchar2(72) := g_package||'.store_crt_ord_warning_ss';
1998: begin
1999: hr_utility.set_location('Entering:'|| l_proc, 10);
2000: --
2001: if p_person_id is not null and p_crt_ord_warning is not null then
2002: if p_crt_ord_warning = 'CLEAR' then
2003: DELETE ben_online_warnings
2007: VALUES (p_person_id, p_crt_ord_warning);
2008: end if;
2009: end if;
2010: --
2011: hr_utility.set_location('Leaving'|| l_proc, 20);
2012: exception
2013: when others then
2014: --
2015: -- An unexpected error has occured
2013: when others then
2014: --
2015: -- An unexpected error has occured
2016: --
2017: hr_utility.set_location(' Exception in :'||l_proc, 100);
2018: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2019: fnd_message.set_token('2',substr(sqlerrm,1,200));
2020: fnd_msg_pub.add;
2021: end store_crt_ord_warning_ss;
2090: --
2091: l_comp_obj_list varchar2(2000) := null;
2092: l_proc varchar2(72) := g_package||'.store_crt_ord_warng_DDNA';
2093: begin
2094: hr_utility.set_location('Entering:'|| l_proc, 10);
2095: --
2096: for l_results in c_crt_ordr_DDNA loop
2097: if l_comp_obj_list is null then
2098: l_comp_obj_list := l_results.comp_obj_name;
2108: INSERT INTO ben_online_warnings(session_id, message_text)
2109: VALUES (p_person_id, fnd_message.get);
2110: end if;
2111: --
2112: hr_utility.set_location('Leaving'|| l_proc, 20);
2113: exception
2114: when others then
2115: --
2116: -- An unexpected error has occured
2114: when others then
2115: --
2116: -- An unexpected error has occured
2117: --
2118: hr_utility.set_location(' Exception in :'||l_proc, 100);
2119: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2120: fnd_message.set_token('2',substr(sqlerrm,1,200));
2121: fnd_msg_pub.add;
2122: end store_crt_ord_warng_DDNA;
2136: l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_1';
2137: --
2138: begin
2139: --
2140: hr_utility.set_location('Entering '||l_proc, 10);
2141: --
2142: open c_egd;
2143: fetch c_egd into p_elig_dpnt_rec;
2144: --
2183: l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_2';
2184: --
2185: begin
2186: --
2187: hr_utility.set_location('Entering '||l_proc, 10);
2188: --
2189: open c_pdp;
2190: fetch c_pdp into p_elig_dpnt_rec;
2191: --
2236: l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_3';
2237: --
2238: begin
2239: --
2240: hr_utility.set_location('Entering '||l_proc, 10);
2241: --
2242: open c_pen;
2243: fetch c_pen into p_elig_dpnt_rec;
2244: --
2393: l_proc varchar2(72) := g_package||'get_elig_dpnt_rec_4';
2394: --
2395: begin
2396: --
2397: hr_utility.set_location('Entering '||l_proc, 10);
2398: --
2399: -- Check if the elig dependent exists
2400: --
2401: open c_eligdpnt_exists;
2432: -- performant code. However if for some special cases no current
2433: -- eligibility rows are found for the comp object then revert to the
2434: -- original unperformant cursor.
2435: --
2436: hr_utility.set_location(l_proc, 20);
2437: open c_pilepodets
2438: (c_effective_date => p_effective_date
2439: ,c_per_in_ler_id => p_per_in_ler_id
2440: ,c_pl_id => p_pl_id
2479: close c_oipl_max_create_dt;
2480: --
2481: end if;
2482: --
2483: hr_utility.set_location(l_proc, 25);
2484: --
2485: else
2486: --
2487: hr_utility.set_location(l_proc, 21);
2483: hr_utility.set_location(l_proc, 25);
2484: --
2485: else
2486: --
2487: hr_utility.set_location(l_proc, 21);
2488: open c_oipl_max_create_dt
2489: (c_dpnt_person_id => p_dpnt_person_id
2490: ,c_effective_date => p_effective_date
2491: ,c_oipl_id => p_oipl_id
2492: ,c_pgm_id => nvl(p_pgm_id, -1)
2493: );
2494: fetch c_oipl_max_create_dt into l_elig_dpnt_id;
2495: close c_oipl_max_create_dt;
2496: hr_utility.set_location(l_proc, 26);
2497: --
2498: end if;
2499: --
2500: else
2524: fetch c_pl_max_create_dt into l_elig_dpnt_id;
2525: close c_pl_max_create_dt;
2526: --
2527: end if;
2528: hr_utility.set_location(l_proc, 30);
2529: --
2530: end if;
2531: --
2532: end if;
2534: close c_eligdpnt_exists;
2535: --
2536: if l_elig_dpnt_id is not null then
2537: --
2538: hr_utility.set_location(l_proc, 40);
2539: --
2540: l_return := get_elig_dpnt_rec(p_elig_dpnt_id => l_elig_dpnt_id,
2541: p_elig_dpnt_rec => p_elig_dpnt_rec);
2542: --
2541: p_elig_dpnt_rec => p_elig_dpnt_rec);
2542: --
2543: end if;
2544: --
2545: hr_utility.set_location('Leaving '||l_proc, 10);
2546: --
2547: return l_return;
2548: --
2549: end get_elig_dpnt_rec;
2596: l_proc varchar2(72) := g_package||'get_elig_per_id';
2597: --
2598: begin
2599: --
2600: hr_utility.set_location('Entering '||l_proc, 10);
2601: --
2602: p_elig_per_id := null;
2603: p_elig_per_opt_id := null;
2604: --
2603: p_elig_per_opt_id := null;
2604: --
2605: if p_oipl_id is not null then
2606: --
2607: hr_utility.set_location(l_proc, 20);
2608: --
2609: open c_oipl;
2610: fetch c_oipl into p_elig_per_id, p_elig_per_opt_id;
2611: close c_oipl;
2611: close c_oipl;
2612: --
2613: else
2614: --
2615: hr_utility.set_location(l_proc, 30);
2616: --
2617: open c_pl;
2618: fetch c_pl into p_elig_per_id;
2619: close c_pl;
2619: close c_pl;
2620: --
2621: end if;
2622: --
2623: hr_utility.set_location('Leaving '||l_proc, 10);
2624: --
2625: end get_elig_per_id;
2626: --
2627: -- Bug No 4931912