[Home] [Help]
5: -- | Private Global Definitions |
6: -- --------------------------------------------------------------------------
7: --
8: g_package varchar2(33):=' ben_cwb_summary_pkg.'; --Global package name
9: g_debug boolean := hr_utility.debug_enabled;
10: --
11: type g_summary_type is table of ben_cwb_summary%rowtype
12: index by binary_integer;
13: g_summary_rec g_summary_type;
202: --
203: begin
204: --
205: if g_debug then
206: hr_utility.set_location('Entering:'|| l_proc, 10);
207: end if;
208: --
209:
210: --
231:
232: if (csr_summary_id%notfound) then
233: --
234: if g_debug then
235: hr_utility.set_location(l_proc, 20);
236: end if;
237: --
238: -- No records are found. So set the flag to No records.
239: l_found := 'N'; -- no records;
239: l_found := 'N'; -- no records;
240: else
241: --
242: if g_debug then
243: hr_utility.set_location(l_proc, 30);
244: end if;
245: -- try to get summary Id of one unlocked record
246: loop
247: begin
272:
273: if (l_found = 'F') then
274: --
275: if g_debug then
276: hr_utility.set_location(l_proc, 40);
277: end if;
278: --
279: -- Found one unlocked record. So update the summary.
280: update ben_cwb_summary summ
409: where summ.summary_id = l_summary_id;
410: else
411: --
412: if g_debug then
413: hr_utility.set_location(l_proc, 50);
414: end if;
415: --
416: if (l_found = 'L') then
417: -- All the records are locked. So insert a 'P' record.
504: );
505: end if;
506: --
507: if g_debug then
508: hr_utility.set_location(' Leaving:'|| l_proc, 99);
509: end if;
510: --
511: end; -- of procedure update_or_insert
512: --
529: --
530: begin
531: --
532: if g_debug then
533: hr_utility.set_location('Entering:'|| l_proc, 10);
534: end if;
535: --
536: -- for each pending record
537: for pending_rec in csr_pending_recs(p_person_id)
537: for pending_rec in csr_pending_recs(p_person_id)
538: loop
539: --
540: if g_debug then
541: hr_utility.set_location(l_proc, 20);
542: end if;
543: --
544: -- These will be executed only for the first iteration of the loop
545: if l_sum_rec.group_per_in_ler_id is null then
544: -- These will be executed only for the first iteration of the loop
545: if l_sum_rec.group_per_in_ler_id is null then
546: --
547: if g_debug then
548: hr_utility.set_location(l_proc, 30);
549: end if;
550: --
551: l_sum_rec.group_per_in_ler_id := pending_rec.group_per_in_ler_id;
552: l_sum_rec.group_pl_id := pending_rec.group_pl_id;
560: or l_sum_rec.group_oipl_id <>
561: pending_rec.group_oipl_id) then
562: --
563: if g_debug then
564: hr_utility.set_location(l_proc, 40);
565: end if;
566: --
567: -- combination changed. So call update_or_insert
568: -- This procedure tries to update the main rec, if it is already
609: l_sum_rec.misc3_val_all :=null;
610: end if; -- if change in combination
611: --
612: if g_debug then
613: hr_utility.set_location(l_proc, 50);
614: end if;
615: --
616: --Now, add the values
617: l_sum_rec.elig_count_direct := ben_cwb_utils.add_number_with_null_check
685: where summary_id = pending_rec.summary_id;
686: end loop; -- of get_pening_recs
687: --
688: if g_debug then
689: hr_utility.set_location(l_proc, 60);
690: end if;
691: --
692: -- if the for loop fetches atleast one record then the last summary record
693: -- values will not get updated in ben_cwb_summary. So call
695:
696: if (l_sum_rec.group_per_in_ler_id is not null) then
697: --
698: if g_debug then
699: hr_utility.set_location(l_proc, 50);
700: end if;
701: --
702: update_or_insert(l_sum_rec);
703: end if;
702: update_or_insert(l_sum_rec);
703: end if;
704: --
705: if g_debug then
706: hr_utility.set_location(' Leaving:'|| l_proc, 99);
707: end if;
708: --
709: end; -- end of procedure consolidate_summary_rec
710: --
725: --
726: begin
727: --
728: if g_debug then
729: hr_utility.set_location('Entering:'|| l_proc, 10);
730: end if;
731: --
732: -- for each person from csr_get_per_id
733: for person in csr_get_person_ids
733: for person in csr_get_person_ids
734: loop
735: --
736: if g_debug then
737: hr_utility.set_location(l_proc, 20);
738: end if;
739: --
740: -- call the process that consolidates the summary records
741: -- for this person
742: consolidate_summary_rec(p_person_id => person.person_id);
743: end loop;
744: --
745: if g_debug then
746: hr_utility.set_location(' Leaving:'|| l_proc, 99);
747: end if;
748: --
749: end;
750: --
780: --
781: begin
782: --
783: if g_debug then
784: hr_utility.set_location('Entering:'|| l_proc, 10);
785: end if;
786: --
787: ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
788: 'update_person_info';
821: l_group_per_in_ler_id_tab.delete;
822: l_person_id_tab.delete;
823: --
824: if g_debug then
825: hr_utility.set_location(' Leaving:'|| l_proc, 999);
826: end if;
827: --
828: end update_person_info;
829: --
1020: --
1021: begin
1022: --
1023: if g_debug then
1024: hr_utility.set_location('Entering:'|| l_proc, 10);
1025: end if;
1026: --
1027: -- get the prsrv_bdgt_cd from pl_dsgn
1028: select prsrv_bdgt_cd, uses_bdgt_flag
1032: and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1033: and oipl_id = -1;
1034: --
1035: if g_debug then
1036: hr_utility.set_location(l_proc, 20);
1037: end if;
1038: --
1039: if l_uses_bdgt_flag = 'Y' then
1040: -- process the budgets summary
1043: -- and ws_bdgt_iss_val_direct.
1044: if l_prsrv_bdgt_cd = 'P' then
1045: --
1046: if g_debug then
1047: hr_utility.set_location(l_proc, 30);
1048: end if;
1049: --
1050: -- bulk collect the data
1051: open csr_directs_ws_bdgt_in_percnt(p_group_pl_id, p_lf_evt_ocrd_dt);
1057: ,l_ws_bdgt_iss_val_direct_tab;
1058: close csr_directs_ws_bdgt_in_percnt;
1059: --
1060: if g_debug then
1061: hr_utility.set_location(l_proc, 40);
1062: end if;
1063: --
1064: if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
1065: -- bulk update the table
1073: and group_oipl_id = l_group_oipl_id_tab(i);
1074: end if;
1075: --
1076: if g_debug then
1077: hr_utility.set_location(l_proc, 50);
1078: end if;
1079: --
1080: l_group_per_in_ler_id_tab.delete;
1081: l_group_pl_id_tab.delete;
1085:
1086: else
1087: --
1088: if g_debug then
1089: hr_utility.set_location(l_proc, 60);
1090: end if;
1091: --
1092: -- the code is 'A', compute bdgt_val_direct, bdgt_iss_va_direct
1093: -- ws_bdgt_val_direct, ws_bdgt_iss_val_direct
1102: ,l_bdgt_iss_val_direct_tab;
1103: close csr_directs_bdgt_in_amt;
1104: --
1105: if g_debug then
1106: hr_utility.set_location(l_proc, 70);
1107: end if;
1108: --
1109: if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
1110: -- bulk update the table
1120: and group_oipl_id = l_group_oipl_id_tab(i);
1121: end if;
1122: --
1123: if g_debug then
1124: hr_utility.set_location(l_proc, 80);
1125: end if;
1126: --
1127: l_group_per_in_ler_id_tab.delete;
1128: l_group_pl_id_tab.delete;
1134: end if; -- of prsrv_bdgt_cd
1135: end if; -- of uses_bdgts_flag
1136: --
1137: if g_debug then
1138: hr_utility.set_location(l_proc, 90);
1139: end if;
1140:
1141: -- Now compute the _all information
1142: open csr_all_info(p_group_pl_id
1161: ,l_misc3_val_all_tab;
1162: close csr_all_info;
1163: --
1164: if g_debug then
1165: hr_utility.set_location(l_proc, 100);
1166: end if;
1167: --
1168: if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
1169: -- bulk update the data into ben_cwb_summary
1190: and group_oipl_id = l_group_oipl_id_tab(i);
1191: end if;
1192: --
1193: if g_debug then
1194: hr_utility.set_location(l_proc, 110);
1195: end if;
1196: --
1197: l_group_per_in_ler_id_tab.delete;
1198: l_group_pl_id_tab.delete;
1213: l_misc2_val_all_tab.delete;
1214: l_misc3_val_all_tab.delete;
1215: --
1216: if g_debug then
1217: hr_utility.set_location(l_proc, 120);
1218: end if;
1219: --
1220: if l_uses_bdgt_flag = 'Y' then
1221: -- process the budgets info
1222: --
1223: if l_prsrv_bdgt_cd = 'P' then
1224: --
1225: if g_debug then
1226: hr_utility.set_location(l_proc, 130);
1227: end if;
1228: --
1229: -- bulk collect the data
1230: open csr_directs_bdgt_in_prcnt(p_group_pl_id, p_lf_evt_ocrd_dt);
1236: ,l_bdgt_iss_val_direct_tab;
1237: close csr_directs_bdgt_in_prcnt;
1238: --
1239: if g_debug then
1240: hr_utility.set_location(l_proc, 140);
1241: end if;
1242: --
1243: -- bulk update the table
1244: if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
1252: and group_oipl_id = l_group_oipl_id_tab(i);
1253: end if;
1254: --
1255: if g_debug then
1256: hr_utility.set_location(l_proc, 150);
1257: end if;
1258: --
1259: l_group_per_in_ler_id_tab.delete;
1260: l_group_pl_id_tab.delete;
1264: end if; -- of prsrv_bdgt_cd
1265: end if; -- of uses_bdgt_flag
1266: --
1267: if g_debug then
1268: hr_utility.set_location(l_proc, 160);
1269: end if;
1270: --
1271: if g_debug then
1272: hr_utility.set_location(' Leaving:'|| l_proc, 999);
1268: hr_utility.set_location(l_proc, 160);
1269: end if;
1270: --
1271: if g_debug then
1272: hr_utility.set_location(' Leaving:'|| l_proc, 999);
1273: end if;
1274: --
1275: end; -- of compute_bdgts_and_all
1276: --
1376: --
1377: begin
1378: --
1379: if g_debug then
1380: hr_utility.set_location('Entering:'|| l_proc, 10);
1381: end if;
1382: --
1383: ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
1384: 'refresh_summary_group_pl';
1399: and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1400: and group_per_in_ler_id <> -1;
1401: --
1402: if g_debug then
1403: hr_utility.set_location(l_proc, 20);
1404: end if;
1405: --
1406: -- Insert an empty summary row for each managers. This summary row
1407: -- will contain only the group_per_in_ler_id, group_pl_id, group_oipl_id,
1430: and pil.per_in_ler_stat_cd in ('PROCD','STRTD');
1431:
1432: --
1433: if g_debug then
1434: hr_utility.set_location(l_proc, 30);
1435: end if;
1436: --
1437: -- get the directs info
1438: open csr_directs_info;
1454: ,l_misc3_val_direct_tab;
1455: close csr_directs_info;
1456: --
1457: if g_debug then
1458: hr_utility.set_location(l_proc, 40);
1459: end if;
1460: --
1461: if (nvl(l_group_per_in_ler_id_tab.count,0) > 0)then
1462: forall i in l_group_per_in_ler_id_tab.first..
1510: delete_refresh_job_marker(p_group_pl_id => p_group_pl_id
1511: ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
1512: --
1513: if g_debug then
1514: hr_utility.set_location(' Leaving:'|| l_proc, 99);
1515: end if;
1516: --
1517: exception
1518: when others then
1663: --
1664: begin
1665: --
1666: if g_debug then
1667: hr_utility.set_location('Entering:'|| l_proc, 10);
1668: end if;
1669: --
1670: -- get the Ws Bdgt Direct and Dist Bdgt Direct
1671: if p_prsrv_bdgt_cd = 'P' then
1670: -- get the Ws Bdgt Direct and Dist Bdgt Direct
1671: if p_prsrv_bdgt_cd = 'P' then
1672: --
1673: if g_debug then
1674: hr_utility.set_location(l_proc, 20);
1675: end if;
1676: --
1677: if not (p_only_all) then
1678: open csr_prcnt_bdgts_direct(p_group_per_in_ler_id);
1686: close csr_prcnt_bdgts_direct;
1687: end if;
1688: --
1689: if g_debug then
1690: hr_utility.set_location(l_proc, 30);
1691: end if;
1692: --
1693: open csr_prcnt_ws_bdgts_all(p_group_per_in_ler_id);
1694: fetch csr_prcnt_ws_bdgts_all bulk collect into
1699: close csr_prcnt_ws_bdgts_all;
1700: else
1701: --
1702: if g_debug then
1703: hr_utility.set_location(l_proc, 40);
1704: end if;
1705: --
1706: if not (p_only_all) then
1707: open csr_amt_bdgts_direct(p_group_per_in_ler_id);
1715: close csr_amt_bdgts_direct;
1716: end if;
1717: --
1718: if g_debug then
1719: hr_utility.set_location(l_proc, 50);
1720: end if;
1721: --
1722: open csr_amt_ws_bdgts_all(p_group_per_in_ler_id);
1723: fetch csr_amt_ws_bdgts_all bulk collect into
1728: close csr_amt_ws_bdgts_all;
1729: end if;
1730: --
1731: if g_debug then
1732: hr_utility.set_location(l_proc, 60);
1733: end if;
1734: --
1735: -- get the old bdgts summary
1736: open csr_old_bdgts_summary(p_group_per_in_ler_id);
1743: ,l_old_bdgt_iss_val_direct_tab;
1744: close csr_old_bdgts_summary;
1745: --
1746: if g_debug then
1747: hr_utility.set_location(l_proc, 70);
1748: end if;
1749: --
1750: if nvl(l_group_pl_id_tab.count,0) = 0 then
1751: --
1749: --
1750: if nvl(l_group_pl_id_tab.count,0) = 0 then
1751: --
1752: if g_debug then
1753: hr_utility.set_location(' Leaving:'|| l_proc, 79);
1754: end if;
1755: -- no records to process
1756: return;
1757: end if;
1759: for i in l_group_pl_id_tab.first..l_group_pl_id_tab.last
1760: loop
1761: --
1762: if g_debug then
1763: hr_utility.set_location(l_proc, 80);
1764: hr_utility.set_location('grp pil :'||p_group_per_in_ler_id,85);
1765: hr_utility.set_location('Ws bdgt All :'||l_ws_bdgt_val_all_tab(i),85);
1766: hr_utility.set_location('Ws bdgt Iss All :'||l_ws_bdgt_iss_val_all_tab(i),85);
1767: end if;
1760: loop
1761: --
1762: if g_debug then
1763: hr_utility.set_location(l_proc, 80);
1764: hr_utility.set_location('grp pil :'||p_group_per_in_ler_id,85);
1765: hr_utility.set_location('Ws bdgt All :'||l_ws_bdgt_val_all_tab(i),85);
1766: hr_utility.set_location('Ws bdgt Iss All :'||l_ws_bdgt_iss_val_all_tab(i),85);
1767: end if;
1768: --
1761: --
1762: if g_debug then
1763: hr_utility.set_location(l_proc, 80);
1764: hr_utility.set_location('grp pil :'||p_group_per_in_ler_id,85);
1765: hr_utility.set_location('Ws bdgt All :'||l_ws_bdgt_val_all_tab(i),85);
1766: hr_utility.set_location('Ws bdgt Iss All :'||l_ws_bdgt_iss_val_all_tab(i),85);
1767: end if;
1768: --
1769: -- call the update_or_insert_pl_sql_tab with the diff
1762: if g_debug then
1763: hr_utility.set_location(l_proc, 80);
1764: hr_utility.set_location('grp pil :'||p_group_per_in_ler_id,85);
1765: hr_utility.set_location('Ws bdgt All :'||l_ws_bdgt_val_all_tab(i),85);
1766: hr_utility.set_location('Ws bdgt Iss All :'||l_ws_bdgt_iss_val_all_tab(i),85);
1767: end if;
1768: --
1769: -- call the update_or_insert_pl_sql_tab with the diff
1770: if not (p_only_all) then
1808: save_pl_sql_tab;
1809: --
1810: --
1811: if g_debug then
1812: hr_utility.set_location(l_proc, 90);
1813: end if;
1814: --
1815: if g_debug then
1816: hr_utility.set_location(' Leaving:'|| l_proc, 99);
1812: hr_utility.set_location(l_proc, 90);
1813: end if;
1814: --
1815: if g_debug then
1816: hr_utility.set_location(' Leaving:'|| l_proc, 99);
1817: end if;
1818: --
1819: end; -- update_budgets_summary
1820: --
1951: --
1952: begin
1953: --
1954: if g_debug then
1955: hr_utility.set_location('Entering:'|| l_proc, 10);
1956: end if;
1957: --
1958: -- Need to execute this procedure only when the number of persons with
1959: -- person_id as -1 is less. If records are more, call the procedure
1970: return;
1971: elsif l_count > 2 then
1972: --
1973: if g_debug then
1974: hr_utility.set_location(l_proc, 15);
1975: end if;
1976: -- call the refresh_summary_group_pl to improve performance
1977: ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
1978: 'refresh_summary_group_pl';
1984: --
1985: end if;
1986: --
1987: if g_debug then
1988: hr_utility.set_location(l_proc, 20);
1989: end if;
1990: --
1991: ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
1992: 'refresh_summary_persons';
2013: --
2014: end loop;
2015: --
2016: if g_debug then
2017: hr_utility.set_location(l_proc, 30);
2018: end if;
2019: --
2020: for l_emps in csr_emps loop
2021: --
2157: and oipl_id = -1;
2158: --
2159: --
2160: if g_debug then
2161: hr_utility.set_location(l_proc, 90);
2162: end if;
2163:
2164: -- check if the uses_bdgt_flag is set
2165: if l_uses_bdgt_flag = 'Y' then
2179: end loop;
2180: end if; -- of uses_bdgt_flag
2181: --
2182: if g_debug then
2183: hr_utility.set_location(l_proc, 160);
2184: end if;
2185: --
2186: save_pl_sql_tab;
2187: --
2190: -- to correct values from -1.
2191: update_person_info(p_group_pl_id,p_lf_evt_ocrd_dt);
2192: --
2193: if g_debug then
2194: hr_utility.set_location(' Leaving:'|| l_proc, 999);
2195: end if;
2196: --
2197: end; -- of refresh_summary_persons
2198: --
2246: --
2247: begin
2248: --
2249: if g_debug then
2250: hr_utility.set_location('Entering:'|| l_proc, 10);
2251: end if;
2252: --
2253: hr_utility.set_location('Count :'||g_summary_rec.count,20);
2254: --
2249: if g_debug then
2250: hr_utility.set_location('Entering:'|| l_proc, 10);
2251: end if;
2252: --
2253: hr_utility.set_location('Count :'||g_summary_rec.count,20);
2254: --
2255: if nvl(g_summary_rec.count,0) > 0 then
2256: for i in g_summary_rec.first..g_summary_rec.last
2257: loop
2268: --
2269: if (l_found) then
2270: --
2271: if g_debug then
2272: hr_utility.set_location(l_proc, 30);
2273: end if;
2274: --
2275: -- record alredy exists. So update the summary
2276: g_summary_rec(j).elig_count_direct := ben_cwb_utils.add_number_with_null_check
2341: g_summary_rec(j).lf_evt_ocrd_dt:=nvl(p_lf_evt_ocrd_dt,g_summary_rec(j).lf_evt_ocrd_dt);
2342: else
2343: --
2344: if g_debug then
2345: hr_utility.set_location(l_proc, 40);
2346: end if;
2347: --
2348: if nvl(g_summary_rec.count,0) > 0 then
2349: j := g_summary_rec.last + 1;
2390: g_summary_rec(j).lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
2391: end if; -- of l_found
2392: --
2393: if g_debug then
2394: hr_utility.set_location(' Leaving:'|| l_proc, 99);
2395: end if;
2396: --
2397: end; -- update_or_insert_pl_sql_tab
2398: --
2405: --
2406: begin
2407: --
2408: if g_debug then
2409: hr_utility.set_location('Entering:'|| l_proc, 10);
2410: end if;
2411: --
2412: if nvl(g_summary_rec.count,0) = 0 then
2413: return;
2418: update_or_insert(g_summary_rec(i));
2419: end loop;
2420: --
2421: if g_debug then
2422: hr_utility.set_location(l_proc, 20);
2423: end if;
2424: --
2425: g_summary_rec.delete;
2426: --
2424: --
2425: g_summary_rec.delete;
2426: --
2427: if g_debug then
2428: hr_utility.set_location(' Leaving:'|| l_proc, 99);
2429: end if;
2430: --
2431: end; -- save_pl_sql_tab
2432: --
2439: --
2440: begin
2441: --
2442: if g_debug then
2443: hr_utility.set_location('Entering:'|| l_proc, 10);
2444: end if;
2445: --
2446: g_summary_rec.delete;
2447: --
2445: --
2446: g_summary_rec.delete;
2447: --
2448: if g_debug then
2449: hr_utility.set_location(' Leaving:'|| l_proc, 99);
2450: end if;
2451: --
2452: end; -- delete_pl_sql_tab
2453: --
2507: --
2508: begin
2509: --
2510: if g_debug then
2511: hr_utility.set_location('Entering:'|| l_proc, 10);
2512: end if;
2513: --
2514:
2515: if p_lvl_up is not null then
2524: return;
2525: end if;
2526: --
2527: if g_debug then
2528: hr_utility.set_location(l_proc, 20);
2529: end if;
2530: --
2531: open c_summary;
2532: fetch c_summary into l_summary;
2542: return;
2543: end if;
2544: --
2545: if g_debug then
2546: hr_utility.set_location(l_proc, 30);
2547: end if;
2548: --
2549: for l_grps in c_grps loop
2550:
2571: l_grps.dist_bdgt_iss_val is not null) then
2572: -- Is LLM and has distribution budget.
2573: -- Null them.
2574: if g_debug then
2575: hr_utility.set_location(l_proc, 40);
2576: end if;
2577: --
2578: ben_cwb_person_groups_api.update_group_budget(
2579: p_group_per_in_ler_id => l_per_in_ler_id
2595: -- Check if they are allowed to budget
2596: -- A person is allowed to Budget only when they are HLM and the
2597: -- manager above them have a budgeting population of "Direct Managers"
2598: if g_debug then
2599: hr_utility.set_location(l_proc, 50);
2600: end if;
2601: --
2602: open c_mgr_pop_cd;
2603: fetch c_mgr_pop_cd into l_mgr_pop_cd;
2621:
2622: end loop; --c_grps
2623:
2624: if g_debug then
2625: hr_utility.set_location('Leaving:'|| l_proc, 999);
2626: end if;
2627: --
2628:
2629: end clean_budget_data;
2737: --
2738: begin
2739: --
2740: if g_debug then
2741: hr_utility.set_location('Entering:'|| l_proc, 10);
2742: end if;
2743: --
2744: open csr_rates(p_emp_per_in_ler_id);
2745: fetch csr_rates bulk collect into l_rts_group_pl_id_tab
2802: --
2803: if nvl(l_rts_group_pl_id_tab.count,0) = 0 then
2804: --
2805: if g_debug then
2806: hr_utility.set_location(' Leaving:'|| l_proc, 99);
2807: end if;
2808: -- no records to process. return
2809: return;
2810: end if;
2809: return;
2810: end if;
2811: --
2812: if g_debug then
2813: hr_utility.set_location(l_proc, 20);
2814: end if;
2815: --
2816: l_immd_mgr := 1;
2817: for mgr in csr_mgr_ids(p_old_mgr_per_in_ler_id
2817: for mgr in csr_mgr_ids(p_old_mgr_per_in_ler_id
2818: ,p_new_mgr_per_in_ler_id) loop
2819: --
2820: if g_debug then
2821: hr_utility.set_location(l_proc, 30);
2822: end if;
2823: --
2824: if g_debug then
2825: hr_utility.set_location(l_proc, 40);
2821: hr_utility.set_location(l_proc, 30);
2822: end if;
2823: --
2824: if g_debug then
2825: hr_utility.set_location(l_proc, 40);
2826: end if;
2827: --
2828: for i in l_rts_group_pl_id_tab.first..l_rts_group_pl_id_tab.last
2829: loop
2897: );
2898: end loop;
2899: --
2900: if g_debug then
2901: hr_utility.set_location(l_proc, 50);
2902: end if;
2903: --
2904: -- Now change the l_immd_mgr value to false
2905: l_immd_mgr := 0;
2905: l_immd_mgr := 0;
2906: end loop; -- of csr_mgr_ids cursor
2907: --
2908: if g_debug then
2909: hr_utility.set_location(l_proc, 60);
2910: end if;
2911: --
2912:
2913: -- Now add the values to new manager hierarchy
2915: for mgr in csr_mgr_ids(p_new_mgr_per_in_ler_id
2916: ,p_old_mgr_per_in_ler_id) loop
2917: --
2918: if g_debug then
2919: hr_utility.set_location(l_proc, 70);
2920: end if;
2921: for i in l_rts_group_pl_id_tab.first..l_rts_group_pl_id_tab.last
2922: loop
2923: update_or_insert_pl_sql_tab
2990: );
2991: end loop;
2992: --
2993: if g_debug then
2994: hr_utility.set_location(l_proc, 80);
2995: end if;
2996: --
2997: --
2998: -- Now change the l_immd_mgr value to false
2999: l_immd_mgr := 0;
3000: end loop; -- of csr_mgr_ids cursor
3001: --
3002: if g_debug then
3003: hr_utility.set_location(l_proc, 90);
3004: end if;
3005: --
3006: -- Bug 3812624. Added nvl to p_new_mgr_per_in_ler_id. Atleast one of them
3007: -- is always not null.
3022: and pl.lf_evt_ocrd_dt = grp.lf_evt_ocrd_dt;
3023: --
3024: --
3025: if g_debug then
3026: hr_utility.set_location(l_proc, 100);
3027: end if;
3028: --
3029: if l_uses_bdgt_flag = 'N' then
3030: --
3028: --
3029: if l_uses_bdgt_flag = 'N' then
3030: --
3031: if g_debug then
3032: hr_utility.set_location(l_proc, 109);
3033: end if;
3034: --
3035: -- Budgets are not used. So no processing required.
3036: return;
3041: -- correctly.
3042: save_pl_sql_tab;
3043: --
3044: if g_debug then
3045: hr_utility.set_location(l_proc, 110);
3046: end if;
3047: --
3048: -- update the budgets in the old mgr hiearchy
3049: --
3051: ,p_new_mgr_per_in_ler_id)
3052: loop
3053: --
3054: if g_debug then
3055: hr_utility.set_location(l_proc, 120);
3056: end if;
3057: --
3058: -- If lvl_num = 0,1 , do everything
3059: -- For lvl_num > 1, only all.
3065: ,(mgr.lvl_num > 1));
3066: end loop;
3067: --
3068: if g_debug then
3069: hr_utility.set_location(l_proc, 130);
3070: end if;
3071: --
3072: -- update the budgets in the new mgr hiearchy
3073: --
3076: ,p_old_mgr_per_in_ler_id)
3077: loop
3078: --
3079: if g_debug then
3080: hr_utility.set_location(l_proc, 140);
3081: end if;
3082: --
3083: -- If lvl_num = 0,1 , do everything
3084: -- For lvl_num > 1, only all.
3092: l_last_mgr_id := mgr.mgr_per_in_ler_id;
3093: end loop;
3094: --
3095: if g_debug then
3096: hr_utility.set_location(l_proc, 150);
3097: end if;
3098: --
3099: -- when budgets are stored as % we need to update the ws_bdgt_val_all
3100: -- till the top
3100: -- till the top
3101: if l_prsrv_bdgt_cd = 'P' then
3102: --
3103: if g_debug then
3104: hr_utility.set_location(l_proc, 160);
3105: end if;
3106: --
3107: for l_mgrs in csr_mgrs(l_last_mgr_id) loop
3108: --
3106: --
3107: for l_mgrs in csr_mgrs(l_last_mgr_id) loop
3108: --
3109: if g_debug then
3110: hr_utility.set_location(l_proc, 170);
3111: end if;
3112: --
3113: -- lvl_num = 1, do everything.
3114: -- when lvl_num > 1, then only update budgets for all.
3123: -- Clean Budget data for old and new managers. Their role may
3124: -- have changed from LLM to HLM or HLM to LLM.
3125: --
3126: if g_debug then
3127: hr_utility.set_location(l_proc, 180);
3128: end if;
3129: --
3130: clean_budget_data(p_per_in_ler_id => p_old_mgr_per_in_ler_id);
3131: clean_budget_data(p_per_in_ler_id => p_new_mgr_per_in_ler_id);
3131: clean_budget_data(p_per_in_ler_id => p_new_mgr_per_in_ler_id);
3132: save_pl_sql_tab;
3133: --
3134: if g_debug then
3135: hr_utility.set_location(' Leaving:'|| l_proc, 999);
3136: end if;
3137: --
3138: end; -- end of reassign_mgr
3139: --
3210: --
3211: begin
3212: --
3213: if g_debug then
3214: hr_utility.set_location('Entering:'|| l_proc, 10);
3215: end if;
3216: --
3217: open csr_rates(p_emp_per_in_ler_id);
3218: fetch csr_rates bulk collect into l_rts_group_pl_id_tab
3234: --
3235: if nvl(l_rts_group_pl_id_tab.count,0) = 0 then
3236: --
3237: if g_debug then
3238: hr_utility.set_location(' Leaving:'|| l_proc, 99);
3239: end if;
3240: --
3241: return;
3242: end if;
3241: return;
3242: end if;
3243: --
3244: if g_debug then
3245: hr_utility.set_location(l_proc, 20);
3246: end if;
3247: --
3248: l_immd_mgr := 1;
3249: for mgr in csr_mgr_ids(p_old_mgr_per_in_ler_id
3250: ,p_new_mgr_per_in_ler_id)
3251: loop
3252: --
3253: if g_debug then
3254: hr_utility.set_location(l_proc, 30);
3255: end if;
3256: --
3257: --
3258: for i in l_rts_group_pl_id_tab.first..l_rts_group_pl_id_tab.last
3303: );
3304: end loop;
3305: --
3306: if g_debug then
3307: hr_utility.set_location(l_proc, 40);
3308: end if;
3309: --
3310: -- Now change the l_immd_mgr value to false
3311: l_immd_mgr := 0;
3311: l_immd_mgr := 0;
3312: end loop; -- of csr_mgr_ids cursor
3313: --
3314: if g_debug then
3315: hr_utility.set_location(l_proc, 50);
3316: end if;
3317: --
3318:
3319: -- Now add the values to new manager hierarchy
3322: ,p_old_mgr_per_in_ler_id)
3323: loop
3324: --
3325: if g_debug then
3326: hr_utility.set_location(l_proc, 60);
3327: end if;
3328: --
3329: for i in l_rts_group_pl_id_tab.first..l_rts_group_pl_id_tab.last
3330: loop
3374: );
3375: end loop;
3376: --
3377: if g_debug then
3378: hr_utility.set_location(l_proc, 70);
3379: end if;
3380: --
3381: --
3382: -- Now change the l_immd_mgr value to false
3383: l_immd_mgr := 0;
3384: end loop; -- of csr_mgr_ids cursor
3385: --
3386: if g_debug then
3387: hr_utility.set_location(l_proc, 80);
3388: end if;
3389: --
3390: -- if the Prsrv Bdgt Cd is P then, the managers budget also need to
3391: -- updated.
3401: and pl.oipl_id = grp.group_oipl_id
3402: and pl.lf_evt_ocrd_dt = grp.lf_evt_ocrd_dt;
3403: --
3404: if g_debug then
3405: hr_utility.set_location(l_proc, 90);
3406: end if;
3407: --
3408: if l_uses_bdgt_flag = 'N' then
3409: --
3407: --
3408: if l_uses_bdgt_flag = 'N' then
3409: --
3410: if g_debug then
3411: hr_utility.set_location(l_proc, 99);
3412: end if;
3413: --
3414: -- Budgets are not used. So no process required.
3415: return;
3423: --
3424: if l_prsrv_bdgt_cd = 'P' then
3425: --
3426: if g_debug then
3427: hr_utility.set_location(l_proc, 100);
3428: end if;
3429: --
3430: for mgr in csr_mgr_ids(p_old_mgr_per_in_ler_id
3431: ,p_new_mgr_per_in_ler_id)
3431: ,p_new_mgr_per_in_ler_id)
3432: loop
3433: --
3434: if g_debug then
3435: hr_utility.set_location(l_proc, 110);
3436: end if;
3437: --
3438: -- lvl_num = 0, ignore that row as emps do not have budget.
3439: -- lvl_num = 1, do everything.
3449: end if;
3450: end loop;
3451: --
3452: if g_debug then
3453: hr_utility.set_location(l_proc, 120);
3454: end if;
3455: --
3456: -- update the budgets in the new mgr hiearchy
3457: --
3460: ,p_old_mgr_per_in_ler_id)
3461: loop
3462: --
3463: if g_debug then
3464: hr_utility.set_location(l_proc, 130);
3465: end if;
3466: --
3467: -- lvl_num = 0, ignore that row as emps do not have budget.
3468: -- lvl_num = 1, do everything.
3480: l_last_mgr_id := mgr.mgr_per_in_ler_id;
3481: end loop;
3482: --
3483: if g_debug then
3484: hr_utility.set_location(l_proc, 140);
3485: end if;
3486: --
3487: -- when budgets are stored as % the ws_bdgt_val_all will be affected
3488: -- till top
3488: -- till top
3489: for l_mgrs in csr_mgrs(l_last_mgr_id) loop
3490: --
3491: if g_debug then
3492: hr_utility.set_location(l_proc, 150);
3493: end if;
3494: --
3495: -- lvl_num = 1, do everything.
3496: -- when lvl_num > 1, then only update budgets for all.
3507: -- then the budget cleanup is needed for old and
3508: -- new manager's manager
3509: --
3510: if g_debug then
3511: hr_utility.set_location(l_proc, 160);
3512: end if;
3513: --
3514: clean_budget_data(p_per_in_ler_id => p_old_mgr_per_in_ler_id);
3515: clean_budget_data(p_per_in_ler_id => p_new_mgr_per_in_ler_id);
3521: ,p_lvl_up => 1);
3522: save_pl_sql_tab;
3523: --
3524: if g_debug then
3525: hr_utility.set_location(' Leaving:'|| l_proc, 999);
3526: end if;
3527: --
3528: end; -- end of reassign_emp
3529: --
3542: --
3543: begin
3544: --
3545: if g_debug then
3546: hr_utility.set_location('Entering:'|| l_proc, 10);
3547: end if;
3548: --
3549: --check if the emp was a manager earlier
3550: begin
3559: l_is_mgr := 'N';
3560: end;
3561: --
3562: if g_debug then
3563: hr_utility.set_location(l_proc, 20);
3564: end if;
3565:
3566: --
3567: -- if the old_mgr_pil is already deleted from group_hrchy then insert
3575: where emp_per_in_ler_id = p_old_mgr_per_in_ler_id
3576: and lvl_num = 0;
3577: --
3578: if g_debug then
3579: hr_utility.set_location(l_proc, 30);
3580: end if;
3581: --
3582: exception
3583: when no_data_found then
3591: -- set the flag
3592: l_insert_old_mgr :=true;
3593: --
3594: if g_debug then
3595: hr_utility.set_location(l_proc, 40);
3596: end if;
3597: --
3598: end;
3599: end if;
3641: -- inserted in this procedure
3642: if l_insert_old_mgr then
3643: --
3644: if g_debug then
3645: hr_utility.set_location(l_proc, 50);
3646: end if;
3647: --
3648: delete from ben_cwb_group_hrchy
3649: where mgr_per_in_ler_id = p_old_mgr_per_in_ler_id
3650: and lvl_num = 0;
3651: end if;
3652:
3653: if g_debug then
3654: hr_utility.set_location(' Leaving:'|| l_proc, 99);
3655: end if;
3656: --
3657: end;
3658: --
3667: --
3668: begin
3669: --
3670: if g_debug then
3671: hr_utility.set_location('Entering:'|| l_proc, 10);
3672: end if;
3673: --
3674: delete from ben_cwb_summary
3675: where group_pl_id = p_group_pl_id
3675: where group_pl_id = p_group_pl_id
3676: and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
3677: --
3678: if g_debug then
3679: hr_utility.set_location(l_proc, 170);
3680: end if;
3681: --
3682: end;
3683: --
3742: --
3743: begin
3744: --
3745: if g_debug then
3746: hr_utility.set_location('Entering:'|| l_proc, 10);
3747: end if;
3748: --
3749: select pl.prsrv_bdgt_cd
3750: ,pl.uses_bdgt_flag
3761: if l_prsrv_bdgt_cd = 'A' or l_uses_bdgt_flag = 'N'
3762: or p_elig_sal_change = 0 then
3763: --
3764: if g_debug then
3765: hr_utility.set_location(' Leaving:'|| l_proc, 19);
3766: end if;
3767: -- no changes are required
3768: return;
3769: end if;
3776: close csr_ws_bdgts;
3777:
3778: --
3779: if g_debug then
3780: hr_utility.set_location(l_proc, 20);
3781: end if;
3782: --
3783: --
3784: l_immd_mgr := 1;
3786: for mgr in mgr_ids(p_group_per_in_ler_id)
3787: loop
3788: --
3789: if g_debug then
3790: hr_utility.set_location(l_proc, 30);
3791: end if;
3792: --
3793: for i in l_group_pl_id_tab.first..l_group_pl_id_tab.last
3794: loop
3793: for i in l_group_pl_id_tab.first..l_group_pl_id_tab.last
3794: loop
3795: --
3796: if g_debug then
3797: hr_utility.set_location(l_proc, 40);
3798: end if;
3799:
3800: -- for Dist Bdgts, get the current mgr bdgt
3801: select decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
3809: and grp.group_pl_id = l_group_pl_id_tab(i)
3810: and grp.group_oipl_id = l_group_oipl_id_tab(i);
3811: --
3812: if g_debug then
3813: hr_utility.set_location(l_proc, 50);
3814: end if;
3815:
3816: -- this is the change in bdgts of current mgr. so his/her immd
3817: -- mgr summary will need to be updated.
3817: -- mgr summary will need to be updated.
3818: if mgr.next_mgr_id is not null then
3819: --
3820: if g_debug then
3821: hr_utility.set_location(l_proc, 60);
3822: end if;
3823: --
3824: update_or_insert_pl_sql_tab
3825: (p_group_per_in_ler_id => mgr.next_mgr_id
3837: --
3838: l_immd_mgr := 0;
3839: --
3840: if g_debug then
3841: hr_utility.set_location(l_proc, 70);
3842: end if;
3843: --
3844: end loop; -- of mgr_ids cursor
3845: --
3843: --
3844: end loop; -- of mgr_ids cursor
3845: --
3846: if g_debug then
3847: hr_utility.set_location(' Leaving:'|| l_proc, 99);
3848: end if;
3849: --
3850: end; -- end of update_summary_on_elig_sal_change
3851: --