[Home] [Help]
353: l_outputs ff_exec.outputs_t;
354: l_jurisdiction_code varchar2(30);
355:
356: begin
357: hr_utility.set_location('Entering:'|| l_proc, 10);
358: open c_pool_rule_info;
359: fetch c_pool_rule_info into l_pool_rule_info;
360: if c_pool_rule_info%NOTFOUND or c_pool_rule_info%NOTFOUND is null then
361: close c_pool_rule_info;
361: close c_pool_rule_info;
362: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
363: fnd_message.set_token('PROC',l_proc);
364: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
365: hr_utility.set_location(l_proc,82);
366: fnd_message.raise_error;
367: end if;
368: close c_pool_rule_info;
369:
373: close c_person_rule_info;
374: fnd_message.set_name('BEN','BEN_91708_PERSON_NOT_FOUND');
375: fnd_message.set_token('PROC',l_proc);
376: fnd_message.set_token('ID', p_person_id);
377: hr_utility.set_location(l_proc,82);
378: fnd_message.raise_error;
379: end if;
380: close c_person_rule_info;
381:
418: p_jurisdiction_code => l_jurisdiction_code);
419: p_dflt_excs_trtmt_cd := null;
420: p_mx_val := l_outputs(l_outputs.first).value;
421: end if;
422: hr_utility.set_location('Leaving:'|| l_proc, 999);
423:
424: end run_rule;
425: --------------------------------------------------------------------------------
426: -- This procedure returns the update mode appropriate
439: l_update_override boolean := FALSE;
440: l_update_change_insert boolean := FALSE;
441: --
442: begin
443: hr_utility.set_location(' Entering: Get_DT_Upd_Mode' , 10);
444: --
445: -- Get the appropriate update mode.
446: --
447: DT_Api.Find_DT_Upd_Modes(p_effective_date => p_effective_date,
460: elsif l_correction then
461: p_mode := 'CORRECTION';
462: end if;
463: --
464: hr_utility.set_location(' Leaving: Get_DT_Upd_Mode' , 10);
465: end;
466:
467: --------------------------------------------------------------------------------
468: -- accumulate_pools
543: l_element_type_id number;
544: --
545: begin
546: --
547: hr_utility.set_location('Entering:'|| l_proc, 10);
548: hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,12);
549: --
550: open c_choice;
551: loop
544: --
545: begin
546: --
547: hr_utility.set_location('Entering:'|| l_proc, 10);
548: hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,12);
549: --
550: open c_choice;
551: loop
552: --
577: --
578: end loop;
579: close c_choice;
580: --
581: hr_utility.set_location(' Leaving:'||l_proc, 999);
582: --
583: end accumulate_pools;
584: --------------------------------------------------------------------------------
585: -- accumulate_pools_for_choice
600: l_dummy_number number;
601: --
602: begin
603:
604: hr_utility.set_location('Entering:'|| l_proc, 10);
605: --
606: -- determine if the person is enrolled in the comp object or a component of it.
607: --
608: l_person_enrolled:=person_enrolled_in_choice(
614: -- If enrolled do the credit stuff
615: --
616: if (l_person_enrolled and
617: p_epe_rec.bnft_prvdr_pool_id is not null) then
618: hr_utility.set_location(l_proc, 20);
619: create_credit_ledger_entry
620: (p_person_id => p_person_id
621: ,p_epe_rec => p_epe_rec
622: ,p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
626: ,p_bpl_prvdd_val => l_dummy_number
627: );
628: end if;
629: --
630: hr_utility.set_location(' Leaving:'||l_proc, 999);
631: --
632: end accumulate_pools_for_choice;
633: --------------------------------------------------------------------------------
634: -- person_enrolled_in_choice
848: l_enrolled varchar2(1);
849:
850: begin
851:
852: hr_utility.set_location('Entering:'|| l_proc, 10);
853:
854: if p_epe_rec.cmbn_ptip_id is not null then
855: hr_utility.set_location(l_proc, 170);
856: open c_cmbn_ptip_enrolment;
851:
852: hr_utility.set_location('Entering:'|| l_proc, 10);
853:
854: if p_epe_rec.cmbn_ptip_id is not null then
855: hr_utility.set_location(l_proc, 170);
856: open c_cmbn_ptip_enrolment;
857: fetch c_cmbn_ptip_enrolment into l_enrolled;
858: if c_cmbn_ptip_enrolment%notfound then
859: close c_cmbn_ptip_enrolment;
856: open c_cmbn_ptip_enrolment;
857: fetch c_cmbn_ptip_enrolment into l_enrolled;
858: if c_cmbn_ptip_enrolment%notfound then
859: close c_cmbn_ptip_enrolment;
860: hr_utility.set_location(l_proc, 180);
861: return false;
862: else
863: close c_cmbn_ptip_enrolment;
864: hr_utility.set_location(l_proc, 190);
860: hr_utility.set_location(l_proc, 180);
861: return false;
862: else
863: close c_cmbn_ptip_enrolment;
864: hr_utility.set_location(l_proc, 190);
865: return true;
866: end if;
867:
868: elsif p_epe_rec.cmbn_plip_id is not null then
865: return true;
866: end if;
867:
868: elsif p_epe_rec.cmbn_plip_id is not null then
869: hr_utility.set_location(l_proc, 240);
870: open c_cmbn_plip_enrolment;
871: fetch c_cmbn_plip_enrolment into l_enrolled;
872: if c_cmbn_plip_enrolment%notfound then
873: close c_cmbn_plip_enrolment;
870: open c_cmbn_plip_enrolment;
871: fetch c_cmbn_plip_enrolment into l_enrolled;
872: if c_cmbn_plip_enrolment%notfound then
873: close c_cmbn_plip_enrolment;
874: hr_utility.set_location(l_proc, 250);
875: return false;
876: else
877: close c_cmbn_plip_enrolment;
878: hr_utility.set_location(l_proc, 260);
874: hr_utility.set_location(l_proc, 250);
875: return false;
876: else
877: close c_cmbn_plip_enrolment;
878: hr_utility.set_location(l_proc, 260);
879: return true;
880: end if;
881:
882: elsif p_epe_rec.cmbn_ptip_opt_id is not null then
879: return true;
880: end if;
881:
882: elsif p_epe_rec.cmbn_ptip_opt_id is not null then
883: hr_utility.set_location(l_proc, 200);
884: open c_cmbn_ptip_opt_enrolment;
885: fetch c_cmbn_ptip_opt_enrolment into l_enrolled;
886: if c_cmbn_ptip_opt_enrolment%notfound then
887: close c_cmbn_ptip_opt_enrolment;
884: open c_cmbn_ptip_opt_enrolment;
885: fetch c_cmbn_ptip_opt_enrolment into l_enrolled;
886: if c_cmbn_ptip_opt_enrolment%notfound then
887: close c_cmbn_ptip_opt_enrolment;
888: hr_utility.set_location(l_proc, 210);
889: return false;
890: else
891: close c_cmbn_ptip_opt_enrolment;
892: hr_utility.set_location(l_proc, 220);
888: hr_utility.set_location(l_proc, 210);
889: return false;
890: else
891: close c_cmbn_ptip_opt_enrolment;
892: hr_utility.set_location(l_proc, 220);
893: return true;
894: end if;
895: hr_utility.set_location(l_proc, 230);
896:
891: close c_cmbn_ptip_opt_enrolment;
892: hr_utility.set_location(l_proc, 220);
893: return true;
894: end if;
895: hr_utility.set_location(l_proc, 230);
896:
897: elsif p_epe_rec.oiplip_id is not null then
898: hr_utility.set_location(l_proc, 201);
899: open c_oiplip_enrolment;
894: end if;
895: hr_utility.set_location(l_proc, 230);
896:
897: elsif p_epe_rec.oiplip_id is not null then
898: hr_utility.set_location(l_proc, 201);
899: open c_oiplip_enrolment;
900: fetch c_oiplip_enrolment into l_enrolled;
901: if c_oiplip_enrolment%notfound then
902: close c_oiplip_enrolment;
899: open c_oiplip_enrolment;
900: fetch c_oiplip_enrolment into l_enrolled;
901: if c_oiplip_enrolment%notfound then
902: close c_oiplip_enrolment;
903: hr_utility.set_location(l_proc, 301);
904: return false;
905: else
906: close c_oiplip_enrolment;
907: hr_utility.set_location(l_proc, 401);
903: hr_utility.set_location(l_proc, 301);
904: return false;
905: else
906: close c_oiplip_enrolment;
907: hr_utility.set_location(l_proc, 401);
908: return true;
909: end if;
910: hr_utility.set_location(l_proc, 501);
911:
906: close c_oiplip_enrolment;
907: hr_utility.set_location(l_proc, 401);
908: return true;
909: end if;
910: hr_utility.set_location(l_proc, 501);
911:
912: elsif p_epe_rec.oipl_id is not null then
913: hr_utility.set_location(l_proc, 20);
914: open c_oipl_enrolment;
909: end if;
910: hr_utility.set_location(l_proc, 501);
911:
912: elsif p_epe_rec.oipl_id is not null then
913: hr_utility.set_location(l_proc, 20);
914: open c_oipl_enrolment;
915: fetch c_oipl_enrolment into l_enrolled;
916: if c_oipl_enrolment%notfound then
917: close c_oipl_enrolment;
914: open c_oipl_enrolment;
915: fetch c_oipl_enrolment into l_enrolled;
916: if c_oipl_enrolment%notfound then
917: close c_oipl_enrolment;
918: hr_utility.set_location(l_proc, 30);
919: return false;
920: else
921: close c_oipl_enrolment;
922: hr_utility.set_location(l_proc, 40);
918: hr_utility.set_location(l_proc, 30);
919: return false;
920: else
921: close c_oipl_enrolment;
922: hr_utility.set_location(l_proc, 40);
923: return true;
924: end if;
925: hr_utility.set_location(l_proc, 50);
926:
921: close c_oipl_enrolment;
922: hr_utility.set_location(l_proc, 40);
923: return true;
924: end if;
925: hr_utility.set_location(l_proc, 50);
926:
927: elsif p_epe_rec.pl_id is not null then
928: hr_utility.set_location(l_proc, 60);
929: open c_plan_enrolment;
924: end if;
925: hr_utility.set_location(l_proc, 50);
926:
927: elsif p_epe_rec.pl_id is not null then
928: hr_utility.set_location(l_proc, 60);
929: open c_plan_enrolment;
930: fetch c_plan_enrolment into l_enrolled;
931: if c_plan_enrolment%notfound then
932: close c_plan_enrolment;
929: open c_plan_enrolment;
930: fetch c_plan_enrolment into l_enrolled;
931: if c_plan_enrolment%notfound then
932: close c_plan_enrolment;
933: hr_utility.set_location(l_proc, 70);
934: return false;
935: else
936: close c_plan_enrolment;
937: return true;
935: else
936: close c_plan_enrolment;
937: return true;
938: end if;
939: hr_utility.set_location(l_proc, 80);
940:
941: elsif p_epe_rec.ptip_id is not null then
942: hr_utility.set_location(l_proc, 90);
943: open c_ptip_enrolment;
938: end if;
939: hr_utility.set_location(l_proc, 80);
940:
941: elsif p_epe_rec.ptip_id is not null then
942: hr_utility.set_location(l_proc, 90);
943: open c_ptip_enrolment;
944: fetch c_ptip_enrolment into l_enrolled;
945: if c_ptip_enrolment%notfound then
946: close c_ptip_enrolment;
943: open c_ptip_enrolment;
944: fetch c_ptip_enrolment into l_enrolled;
945: if c_ptip_enrolment%notfound then
946: close c_ptip_enrolment;
947: hr_utility.set_location(l_proc, 100);
948: return false;
949: else
950: close c_ptip_enrolment;
951: hr_utility.set_location(l_proc, 110);
947: hr_utility.set_location(l_proc, 100);
948: return false;
949: else
950: close c_ptip_enrolment;
951: hr_utility.set_location(l_proc, 110);
952: return true;
953: end if;
954: hr_utility.set_location(l_proc, 120);
955:
950: close c_ptip_enrolment;
951: hr_utility.set_location(l_proc, 110);
952: return true;
953: end if;
954: hr_utility.set_location(l_proc, 120);
955:
956: elsif p_epe_rec.pgm_id is not null then
957: hr_utility.set_location(l_proc, 130);
958: open c_pgm_enrolment;
953: end if;
954: hr_utility.set_location(l_proc, 120);
955:
956: elsif p_epe_rec.pgm_id is not null then
957: hr_utility.set_location(l_proc, 130);
958: open c_pgm_enrolment;
959: fetch c_pgm_enrolment into l_enrolled;
960: if c_pgm_enrolment%notfound then
961: close c_pgm_enrolment;
958: open c_pgm_enrolment;
959: fetch c_pgm_enrolment into l_enrolled;
960: if c_pgm_enrolment%notfound then
961: close c_pgm_enrolment;
962: hr_utility.set_location(l_proc, 140);
963: return false;
964: else
965: close c_pgm_enrolment;
966: hr_utility.set_location(l_proc, 150);
962: hr_utility.set_location(l_proc, 140);
963: return false;
964: else
965: close c_pgm_enrolment;
966: hr_utility.set_location(l_proc, 150);
967: return true;
968: end if;
969: hr_utility.set_location(l_proc, 160);
970:
965: close c_pgm_enrolment;
966: hr_utility.set_location(l_proc, 150);
967: return true;
968: end if;
969: hr_utility.set_location(l_proc, 160);
970:
971: else
972: hr_utility.set_location(l_proc, 270);
973: return false;
968: end if;
969: hr_utility.set_location(l_proc, 160);
970:
971: else
972: hr_utility.set_location(l_proc, 270);
973: return false;
974: end if;
975: --
976: hr_utility.set_location(' Leaving:'||l_proc, 999);
972: hr_utility.set_location(l_proc, 270);
973: return false;
974: end if;
975: --
976: hr_utility.set_location(' Leaving:'||l_proc, 999);
977: --
978: end person_enrolled_in_choice;
979: --------------------------------------------------------------------------------
980: -- create_credit_ledger_entry
1007: --
1008:
1009: begin
1010:
1011: hr_utility.set_location('Entering:'|| l_proc, 10);
1012: hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,13);
1013: --
1014: open c_bnft_pool;
1015: fetch c_bnft_pool into l_pgm_id;
1008:
1009: begin
1010:
1011: hr_utility.set_location('Entering:'|| l_proc, 10);
1012: hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,13);
1013: --
1014: open c_bnft_pool;
1015: fetch c_bnft_pool into l_pgm_id;
1016: close c_bnft_pool;
1033: --
1034: ,p_bpl_prvdd_val => l_dummy_number
1035: );
1036: --
1037: hr_utility.set_location(' Leaving:'||l_proc, 999);
1038: --
1039:
1040: end create_credit_ledger_entry;
1041: --------------------------------------------------------------------------------
1129: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1130: ;
1131:
1132: begin
1133: hr_utility.set_location('Entering:'|| l_proc, 15);
1134: --
1135: -- Get the flex credit result id and it's per-in-ler. Both are needed
1136: -- for when we create ledger rows.
1137: --
1149: ,p_pgm_id => p_epe_rec.pgm_id
1150: );
1151: --
1152: end if;
1153: hr_utility.set_location(l_proc, 40);
1154:
1155: hr_utility.set_location(l_proc, 50);
1156:
1157: hr_utility.set_location('starting create', 10);
1151: --
1152: end if;
1153: hr_utility.set_location(l_proc, 40);
1154:
1155: hr_utility.set_location(l_proc, 50);
1156:
1157: hr_utility.set_location('starting create', 10);
1158: hr_utility.set_location('business_group_id='||to_char(p_epe_rec.business_group_id), 10);
1159: open c_enrt_rt
1153: hr_utility.set_location(l_proc, 40);
1154:
1155: hr_utility.set_location(l_proc, 50);
1156:
1157: hr_utility.set_location('starting create', 10);
1158: hr_utility.set_location('business_group_id='||to_char(p_epe_rec.business_group_id), 10);
1159: open c_enrt_rt
1160: (c_epe_id => p_epe_rec.elig_per_elctbl_chc_id
1161: );
1154:
1155: hr_utility.set_location(l_proc, 50);
1156:
1157: hr_utility.set_location('starting create', 10);
1158: hr_utility.set_location('business_group_id='||to_char(p_epe_rec.business_group_id), 10);
1159: open c_enrt_rt
1160: (c_epe_id => p_epe_rec.elig_per_elctbl_chc_id
1161: );
1162: fetch c_enrt_rt into
1166: ;
1167: if c_enrt_rt%notfound
1168: then
1169: -- error
1170: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 51);
1171: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
1172: fnd_message.set_token('PROC',l_proc);
1173: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1174: hr_utility.set_location(' choice_id='''||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1170: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 51);
1171: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
1172: fnd_message.set_token('PROC',l_proc);
1173: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1174: hr_utility.set_location(' choice_id='''||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1175: hr_utility.set_location(' rslt_id='''||to_char(g_credit_pool_result_id),51);
1176: close c_enrt_rt;
1177: fnd_message.raise_error;
1178: elsif not p_calculate_only_mode
1171: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
1172: fnd_message.set_token('PROC',l_proc);
1173: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1174: hr_utility.set_location(' choice_id='''||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1175: hr_utility.set_location(' rslt_id='''||to_char(g_credit_pool_result_id),51);
1176: close c_enrt_rt;
1177: fnd_message.raise_error;
1178: elsif not p_calculate_only_mode
1179: then
1176: close c_enrt_rt;
1177: fnd_message.raise_error;
1178: elsif not p_calculate_only_mode
1179: then
1180: hr_utility.set_location(l_proc, 20);
1181: if l_acty_base_rt_id is null then
1182: hr_utility.set_location('BEN_91725_NO_FLX_CR_ABR_FOUND', 52);
1183: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
1184: fnd_message.set_token('PROC',l_proc);
1178: elsif not p_calculate_only_mode
1179: then
1180: hr_utility.set_location(l_proc, 20);
1181: if l_acty_base_rt_id is null then
1182: hr_utility.set_location('BEN_91725_NO_FLX_CR_ABR_FOUND', 52);
1183: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
1184: fnd_message.set_token('PROC',l_proc);
1185: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1186: hr_utility.set_location(' enrt_rt_id='''||to_char(l_enrt_rt_id),52);
1182: hr_utility.set_location('BEN_91725_NO_FLX_CR_ABR_FOUND', 52);
1183: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
1184: fnd_message.set_token('PROC',l_proc);
1185: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1186: hr_utility.set_location(' enrt_rt_id='''||to_char(l_enrt_rt_id),52);
1187: close c_enrt_rt;
1188: fnd_message.raise_error;
1189: end if;
1190:
1187: close c_enrt_rt;
1188: fnd_message.raise_error;
1189: end if;
1190:
1191: hr_utility.set_location(' choice_id='||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1192: hr_utility.set_location('looking for POOL p_epe_rec.bnft_prvdr_pool_id'||
1193: TO_CHAR(p_epe_rec.bnft_prvdr_pool_id),60);
1194: hr_utility.set_location(' l_acty_base_rt_id'||to_char(l_acty_base_rt_id),60);
1195: hr_utility.set_location(' g_credit_pool_result_id'||
1188: fnd_message.raise_error;
1189: end if;
1190:
1191: hr_utility.set_location(' choice_id='||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1192: hr_utility.set_location('looking for POOL p_epe_rec.bnft_prvdr_pool_id'||
1193: TO_CHAR(p_epe_rec.bnft_prvdr_pool_id),60);
1194: hr_utility.set_location(' l_acty_base_rt_id'||to_char(l_acty_base_rt_id),60);
1195: hr_utility.set_location(' g_credit_pool_result_id'||
1196: to_char(g_credit_pool_result_id), 60);
1190:
1191: hr_utility.set_location(' choice_id='||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1192: hr_utility.set_location('looking for POOL p_epe_rec.bnft_prvdr_pool_id'||
1193: TO_CHAR(p_epe_rec.bnft_prvdr_pool_id),60);
1194: hr_utility.set_location(' l_acty_base_rt_id'||to_char(l_acty_base_rt_id),60);
1195: hr_utility.set_location(' g_credit_pool_result_id'||
1196: to_char(g_credit_pool_result_id), 60);
1197:
1198: -- if a row is already there update it
1191: hr_utility.set_location(' choice_id='||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1192: hr_utility.set_location('looking for POOL p_epe_rec.bnft_prvdr_pool_id'||
1193: TO_CHAR(p_epe_rec.bnft_prvdr_pool_id),60);
1194: hr_utility.set_location(' l_acty_base_rt_id'||to_char(l_acty_base_rt_id),60);
1195: hr_utility.set_location(' g_credit_pool_result_id'||
1196: to_char(g_credit_pool_result_id), 60);
1197:
1198: -- if a row is already there update it
1199: open c_old_ledger;
1202: l_prvdd_val,
1203: l_object_version_number,
1204: l_effective_start_date
1205: ;
1206: hr_utility.set_location(l_proc, 60);
1207: if c_old_ledger%notfound then
1208: hr_utility.set_location('val is'||to_char(l_val),12);
1209: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
1210: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
1204: l_effective_start_date
1205: ;
1206: hr_utility.set_location(l_proc, 60);
1207: if c_old_ledger%notfound then
1208: hr_utility.set_location('val is'||to_char(l_val),12);
1209: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
1210: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
1211: ,p_effective_start_date => l_effective_start_date
1212: ,p_effective_end_date => l_effective_end_date
1224: ,p_object_version_number => l_object_version_number
1225: ,p_cash_recd_val => null
1226: ,p_effective_date => p_effective_date
1227: );
1228: hr_utility.set_location('CREATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),60);
1229: -- Bug 2200139 Override if user changes the provided value from the
1230: -- Override Enrollment form don't reset it again to the enrt_rt value
1231: --
1232: elsif l_val<>l_prvdd_val and nvl(p_enrt_mthd_cd,'E') <> 'O' then
1242: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
1243: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
1244: p_base_key_value => l_bnft_prvdd_ldgr_id,
1245: p_mode => l_datetrack_mode);
1246: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),70);
1247: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
1248: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
1249: ,p_effective_start_date => l_effective_start_date
1250: ,p_effective_end_date => l_effective_end_date
1261: ,p_cash_recd_val => null
1262: ,p_effective_date => p_effective_date
1263: ,p_datetrack_mode => l_datetrack_mode
1264: );
1265: hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),80);
1266: end if;
1267: p_bnft_prvdd_ldgr_id:=l_bnft_prvdd_ldgr_id;
1268: close c_old_ledger;
1269: end if;
1270: close c_enrt_rt;
1271: --
1272: p_bpl_prvdd_val := l_val;
1273: --
1274: hr_utility.set_location('p_bnft_prvdd_ldgr_id-' || p_bnft_prvdd_ldgr_id,123);
1275: hr_utility.set_location(' Leaving:'||l_proc, 998);
1276:
1277: end create_credit_ledger_entry;
1278: --------------------------------------------------------------------------------
1271: --
1272: p_bpl_prvdd_val := l_val;
1273: --
1274: hr_utility.set_location('p_bnft_prvdd_ldgr_id-' || p_bnft_prvdd_ldgr_id,123);
1275: hr_utility.set_location(' Leaving:'||l_proc, 998);
1276:
1277: end create_credit_ledger_entry;
1278: --------------------------------------------------------------------------------
1279: -- create_debit_ledger_entry
1315: --
1316:
1317: begin
1318:
1319: hr_utility.set_location('Entering:'|| l_proc, 10);
1320: --
1321: open c_epe;
1322: fetch c_epe into l_pgm_id;
1323: close c_epe;
1340: ,p_pgm_id => l_pgm_id
1341: );
1342: --
1343: end if;
1344: hr_utility.set_location(l_proc, 40);
1345: --
1346: l_epe_rec.elig_per_elctbl_chc_id:= p_elig_per_elctbl_chc_id;
1347: l_epe_rec.prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
1348: l_epe_rec.business_group_id := p_business_group_id;
1365: ,p_effective_date => p_effective_date
1366: --
1367: ,p_bpl_used_val => p_bpl_used_val
1368: );
1369: hr_utility.set_location('Leaving:'|| l_proc, 999);
1370:
1371: end create_debit_ledger_entry;
1372: --------------------------------------------------------------------------------
1373: -- create_debit_ledger_entry
1427: where prtt_rt_val_id=p_enrt_rt_rec.prtt_rt_val_id;
1428:
1429: begin
1430:
1431: hr_utility.set_location('Entering:'|| l_proc, 15);
1432:
1433: -- see if a prtt_enrt_rt exists, is so use it's val
1434: if p_enrt_rt_rec.prtt_rt_val_id is null then
1435: l_val:=p_enrt_rt_rec.val;
1476: ,p_object_version_number => l_object_version_number
1477: ,p_cash_recd_val => null
1478: ,p_effective_date => p_effective_date
1479: );
1480: hr_utility.set_location('CREATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),41);
1481: else
1482: /*if l_effective_start_date=p_effective_date then
1483: l_datetrack_mode:=hr_api.g_correction;
1484: else
1494: -- or condition to take care of unrestricted life event
1495: if l_per_in_ler_id <> p_epe_rec.per_in_ler_id or
1496: l_used_val <> l_val then
1497:
1498: hr_utility.set_location('UPDATING LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),51);
1499:
1500: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
1501: p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1502: ,p_effective_start_date => l_effective_start_date
1516: ,p_cash_recd_val => null
1517: ,p_effective_date => p_effective_date
1518: ,p_datetrack_mode => l_datetrack_mode
1519: );
1520: hr_utility.set_location('UPDATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),55);
1521: end if;
1522: end if;
1523: close c_old_ledger;
1524: end if;
1521: end if;
1522: end if;
1523: close c_old_ledger;
1524: end if;
1525: hr_utility.set_location(' Leaving:'||l_proc, 998);
1526: --
1527: -- Set OUT parameters
1528: --
1529: p_bpl_used_val := l_val;
1808: l_datetrack_mode varchar2(30);
1809: l_effective_date date;
1810: --
1811: begin
1812: hr_utility.set_location('Entering:'|| l_proc, 10);
1813: open c_ledger;
1814:
1815: loop
1816: fetch c_ledger into
1830: l_bnft_prvdd_ldgr_id,
1831: l_object_version_number,
1832: l_rt_usg_cd
1833: ;
1834: hr_utility.set_location(l_proc, 20);
1835:
1836: exit when c_ledger%notfound;
1837:
1838: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1834: hr_utility.set_location(l_proc, 20);
1835:
1836: exit when c_ledger%notfound;
1837:
1838: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1839: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1840: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1841: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1842: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1835:
1836: exit when c_ledger%notfound;
1837:
1838: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1839: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1840: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1841: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1842: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1843: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1836: exit when c_ledger%notfound;
1837:
1838: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1839: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1840: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1841: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1842: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1843: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1844: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1837:
1838: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1839: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1840: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1841: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1842: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1843: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1844: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1845:
1838: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1839: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1840: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1841: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1842: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1843: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1844: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1845:
1846:
1839: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1840: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1841: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1842: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1843: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1844: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1845:
1846:
1847: l_person_enrolled:=person_enrolled_in_choice(
1840: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1841: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1842: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1843: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1844: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1845:
1846:
1847: l_person_enrolled:=person_enrolled_in_choice(
1848: p_person_id => p_person_id,
1849: p_epe_rec => l_epe,
1850: p_old_result_id => p_prtt_enrt_rslt_id,
1851: p_effective_date => p_effective_date );
1852:
1853: hr_utility.set_location(l_proc, 40);
1854: --
1855: -- if the person is not enrolled or
1856: -- rt_usg_cd<>'FLXCR' and not directly enrolled
1857: -- then remove the ledger entry
1855: -- if the person is not enrolled or
1856: -- rt_usg_cd<>'FLXCR' and not directly enrolled
1857: -- then remove the ledger entry
1858: --
1859: hr_utility.set_location('Checking ledger='||to_char(l_bnft_prvdd_ldgr_id), 45);
1860: if (not l_person_enrolled) then
1861: open c_ldgr_exist(l_bnft_prvdd_ldgr_id,l_per_in_ler_id);
1862: fetch c_ldgr_exist into l_ldgr_exist;
1863: if c_ldgr_exist%notfound then
1864: close c_ldgr_exist;
1865: else
1866:
1867: --nvl(l_rt_usg_cd,hr_api.g_varchar2)<>'FLXCR') then
1868: hr_utility.set_location('Deleting ledger='||to_char(l_bnft_prvdd_ldgr_id), 50);
1869: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
1870: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
1871: p_effective_start_date => l_effective_start_date,
1872: p_effective_end_date => l_effective_end_date,
1878: close c_ldgr_exist;
1879: end if;
1880: --check if there are any ledger entry only corresponding to this Activity base rt ID,Bug 8504085
1881: if l_epe.oipl_id is not null then
1882: hr_utility.set_location('oipl ID not null ,l_epe.oipl_id : '||l_epe.oipl_id, 50);
1883: open c_get_credit_ledger_epe(l_per_in_ler_id,l_epe.oipl_id);
1884: fetch c_get_credit_ledger_epe into l_get_credit_ledger_epe;
1885: if c_get_credit_ledger_epe%found then
1886: open c_acty_base_rt1(l_get_credit_ledger_epe.elig_per_elctbl_chc_id);
1885: if c_get_credit_ledger_epe%found then
1886: open c_acty_base_rt1(l_get_credit_ledger_epe.elig_per_elctbl_chc_id);
1887: fetch c_acty_base_rt1 into l_acty_base_rt_id;
1888: if c_acty_base_rt1%found then
1889: hr_utility.set_location('l_acty_base_rt_id1 : '|| l_acty_base_rt_id, 50);
1890: open c_flx_credit_plan;
1891: fetch c_flx_credit_plan into l_prtt_enrt_rslt_id;
1892: close c_flx_credit_plan;
1893: if l_prtt_enrt_rslt_id is not null then
1895: fetch c_bnft_prvdd_ldgr into l_bnft_prvdd_ldgr_id,
1896: l_prtt_ro_of_unusd_amt_flag,
1897: l_object_version_number,
1898: l_bpl_esd;
1899: hr_utility.set_location('ldgr id is'||l_bnft_prvdd_ldgr_id, 51);
1900: if c_bnft_prvdd_ldgr%found then
1901: if p_effective_date = l_bpl_esd
1902: then
1903: l_datetrack_mode := hr_api.g_zap;
1931: l_object_version_number := null;
1932: l_bpl_esd := null;
1933: l_datetrack_mode := null;
1934: l_effective_date := null;
1935: hr_utility.set_location(l_proc, 70);
1936: end loop;
1937: --
1938: -- bug # 1708166
1939: --
1939: --
1940: open c_rslt;
1941: fetch c_rslt into l_rslt;
1942: close c_rslt;
1943: hr_utility.set_location('Thru dt ' ||l_rslt.enrt_cvg_thru_dt, 70);
1944: hr_utility.set_location('stat Cd '||l_rslt.prtt_enrt_rslt_stat_cd, 70);
1945: hr_utility.set_location('sspndd '||l_rslt.sspndd_flag, 70);
1946: if l_rslt.enrt_cvg_thru_dt <>hr_api.g_eot or
1947: l_rslt.prtt_enrt_rslt_stat_cd in ('VOIDD','BCKDT') or
1940: open c_rslt;
1941: fetch c_rslt into l_rslt;
1942: close c_rslt;
1943: hr_utility.set_location('Thru dt ' ||l_rslt.enrt_cvg_thru_dt, 70);
1944: hr_utility.set_location('stat Cd '||l_rslt.prtt_enrt_rslt_stat_cd, 70);
1945: hr_utility.set_location('sspndd '||l_rslt.sspndd_flag, 70);
1946: if l_rslt.enrt_cvg_thru_dt <>hr_api.g_eot or
1947: l_rslt.prtt_enrt_rslt_stat_cd in ('VOIDD','BCKDT') or
1948: nvl(l_rslt.sspndd_flag,'N') = 'Y' then -- Bug 5185351
1941: fetch c_rslt into l_rslt;
1942: close c_rslt;
1943: hr_utility.set_location('Thru dt ' ||l_rslt.enrt_cvg_thru_dt, 70);
1944: hr_utility.set_location('stat Cd '||l_rslt.prtt_enrt_rslt_stat_cd, 70);
1945: hr_utility.set_location('sspndd '||l_rslt.sspndd_flag, 70);
1946: if l_rslt.enrt_cvg_thru_dt <>hr_api.g_eot or
1947: l_rslt.prtt_enrt_rslt_stat_cd in ('VOIDD','BCKDT') or
1948: nvl(l_rslt.sspndd_flag,'N') = 'Y' then -- Bug 5185351
1949: l_purge := 'Y';
1955:
1956: open c_flx_credit_plan_deenrt;
1957: fetch c_flx_credit_plan_deenrt into l_prtt_enrt_rslt_id,l_per_in_ler_id_1;
1958: close c_flx_credit_plan_deenrt;
1959: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id, 70);
1960: hr_utility.set_location('l_prtt_enrt_rslt_id '||l_prtt_enrt_rslt_id, 70);
1961: hr_utility.set_location('l_per_in_ler_id_1 '||l_per_in_ler_id_1, 70);
1962: else*/
1963: open c_flx_credit_plan;
1956: open c_flx_credit_plan_deenrt;
1957: fetch c_flx_credit_plan_deenrt into l_prtt_enrt_rslt_id,l_per_in_ler_id_1;
1958: close c_flx_credit_plan_deenrt;
1959: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id, 70);
1960: hr_utility.set_location('l_prtt_enrt_rslt_id '||l_prtt_enrt_rslt_id, 70);
1961: hr_utility.set_location('l_per_in_ler_id_1 '||l_per_in_ler_id_1, 70);
1962: else*/
1963: open c_flx_credit_plan;
1964: fetch c_flx_credit_plan into l_prtt_enrt_rslt_id;
1957: fetch c_flx_credit_plan_deenrt into l_prtt_enrt_rslt_id,l_per_in_ler_id_1;
1958: close c_flx_credit_plan_deenrt;
1959: hr_utility.set_location('p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id, 70);
1960: hr_utility.set_location('l_prtt_enrt_rslt_id '||l_prtt_enrt_rslt_id, 70);
1961: hr_utility.set_location('l_per_in_ler_id_1 '||l_per_in_ler_id_1, 70);
1962: else*/
1963: open c_flx_credit_plan;
1964: fetch c_flx_credit_plan into l_prtt_enrt_rslt_id;
1965: close c_flx_credit_plan;
1976: fetch c_bnft_prvdd_ldgr into l_bnft_prvdd_ldgr_id,
1977: l_prtt_ro_of_unusd_amt_flag,
1978: l_object_version_number,
1979: l_bpl_esd;
1980: hr_utility.set_location('ldgr id is'||l_bnft_prvdd_ldgr_id, 51);
1981: if c_bnft_prvdd_ldgr%found then
1982: --
1983: -- Delete only if there are no current enrollment in
1984: -- this comp object for the person. For an FSA plan, when
1995: if l_prtt_ro_of_unusd_amt_flag = 'Y' then
1996: open c_person_enrolled;
1997: fetch c_person_enrolled into l_exists;
1998: if c_person_enrolled%found then
1999: hr_utility.set_location('person_enrolled ', 51);
2000: l_delete_bpl := false;
2001: else
2002: l_delete_bpl := true;
2003: end if;
2074: /* bug#3365290
2075: if c_enrt_rt%notfound
2076: then
2077: -- error
2078: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 51);
2079: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
2080: fnd_message.set_token('PROC',l_proc);
2081: close c_enrt_rt;
2082: fnd_message.raise_error;
2082: fnd_message.raise_error;
2083: end if;
2084: close c_enrt_rt;
2085: if l_enrt_rt.acty_base_rt_id is null then
2086: hr_utility.set_location('BEN_91725_NO_FLX_CR_ABR_FOUND', 52);
2087: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
2088: fnd_message.set_token('PROC',l_proc);
2089: fnd_message.raise_error;
2090: end if;
2094: --
2095:
2096: open c_bnft_prvdd_ldgr_2 (l_enrt_rt.acty_base_rt_id);
2097: fetch c_bnft_prvdd_ldgr_2 into l_bnft_prvdd_ldgr_id,l_object_version_number,l_bpl_esd;
2098: hr_utility.set_location('ldgr id provided is'||l_bnft_prvdd_ldgr_id, 52);
2099: if c_bnft_prvdd_ldgr_2%found then
2100: --
2101: -- Bug 5500864
2102: if p_effective_date = l_bpl_esd
2126: end if;
2127: close c_ledger;
2128: --
2129:
2130: hr_utility.set_location(' Leaving:'||l_proc, 999);
2131: --
2132: end cleanup_invalid_ledger_entries;
2133: --------------------------------------------------------------------------------
2134: -- cleanup_invalid_ledger_entries
2211: ;
2212:
2213: begin
2214:
2215: hr_utility.set_location('Entering:'|| l_proc, 15);
2216:
2217: open c_ledger;
2218:
2219: loop
2232: l_bnft_prvdd_ldgr_id,
2233: l_object_version_number,
2234: l_rt_usg_cd
2235: ;
2236: hr_utility.set_location(l_proc, 20);
2237:
2238: exit when c_ledger%notfound;
2239:
2240: hr_utility.set_location(l_proc, 30);
2236: hr_utility.set_location(l_proc, 20);
2237:
2238: exit when c_ledger%notfound;
2239:
2240: hr_utility.set_location(l_proc, 30);
2241:
2242: l_person_enrolled:=person_enrolled_in_choice(
2243: p_person_id => p_person_id,
2244: p_epe_rec => l_epe,
2243: p_person_id => p_person_id,
2244: p_epe_rec => l_epe,
2245: p_effective_date => p_effective_date
2246: );
2247: hr_utility.set_location(l_proc, 40);
2248: --
2249: -- if the person is not enrolled or
2250: -- rt_usg_cd<>'FLXCR' and not directly enrolled
2251: -- then remove the ledger entry
2252: --
2253: if ((not l_person_enrolled) or
2254: (nvl(l_rt_usg_cd,hr_api.g_varchar2)<>'FLXCR' and
2255: l_epe.prtt_enrt_rslt_id is null)) then
2256: hr_utility.set_location('Deleting ledger='||to_char(l_bnft_prvdd_ldgr_id), 50);
2257: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
2258: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
2259: p_effective_start_date => l_effective_start_date,
2260: p_effective_end_date => l_effective_end_date,
2262: p_effective_date => p_effective_date,
2263: p_datetrack_mode => hr_api.g_zap,
2264: p_business_group_id => p_business_group_id
2265: );
2266: hr_utility.set_location(l_proc, 60);
2267: end if;
2268: hr_utility.set_location(l_proc, 70);
2269: end loop;
2270: close c_ledger;
2264: p_business_group_id => p_business_group_id
2265: );
2266: hr_utility.set_location(l_proc, 60);
2267: end if;
2268: hr_utility.set_location(l_proc, 70);
2269: end loop;
2270: close c_ledger;
2271: --
2272: hr_utility.set_location(' Leaving:'||l_proc, 998);
2268: hr_utility.set_location(l_proc, 70);
2269: end loop;
2270: close c_ledger;
2271: --
2272: hr_utility.set_location(' Leaving:'||l_proc, 998);
2273: --
2274: end cleanup_invalid_ledger_entries;
2275: --------------------------------------------------------------------------------
2276: -- create_flex_credit_enrolment
2394: l_acty_typ_cd2 varchar2(300);
2395: --
2396: begin
2397:
2398: hr_utility.set_location('Entering:'|| l_proc, 10);
2399:
2400: -- 2988218 chk if its a flex pgm
2401: open c_pgm_type;
2402: fetch c_pgm_type into l_pgm_type;
2410: fetch c_flex_credit_choice into l_flex_credit_choice;
2411: --
2412: if (c_flex_credit_choice%notfound and
2413: nvl(p_rt_val,hr_api.g_number) <> hr_api.g_number ) then --Bug 3864152
2414: hr_utility.set_location('BEN_91726_NO_FLX_CR_CHOICE', 20);
2415: fnd_message.set_name('BEN','BEN_91726_NO_FLX_CR_CHOICE');
2416: fnd_message.set_token('PROC',l_proc);
2417: fnd_message.set_token('PERSON_ID', to_char(p_person_id));
2418: fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
2458: else
2459: l_acty_typ_cd := 'NCRUDED';
2460: l_acty_typ_cd2 := 'NCRDSTR';
2461: end if;
2462: hr_utility.set_location('l_acty_typ_cd:'|| l_acty_typ_cd, 10);
2463: --
2464: open c_net_credits_rate(l_acty_typ_cd);
2465: fetch c_net_credits_rate into l_prnt_prtt_rt_val_id
2466: ,l_prnt_enrt_rt_id
2465: fetch c_net_credits_rate into l_prnt_prtt_rt_val_id
2466: ,l_prnt_enrt_rt_id
2467: ,l_prnt_acty_base_rt_id;
2468: if c_net_credits_rate%notfound then
2469: hr_utility.set_location('not found:'|| l_acty_typ_cd, 10);
2470: close c_net_credits_rate;
2471: fnd_message.set_name('BEN','BEN_92622_NET_CRED_RT_NOT_FND');
2472: fnd_message.raise_error;
2473: else
2506: -- Get the calculated value for the child rate.
2507: --
2508: l_prnt_rt_val := abs(p_net_credit_val);
2509: --
2510: hr_utility.set_location('l_prnt_rt_val:'|| l_prnt_rt_val, 10);
2511: hr_utility.set_location('l_child_rt_val:'|| l_child_rt_val, 10);
2512: ben_determine_activity_base_rt.main
2513: (p_person_id => p_person_id
2514: ,p_elig_per_elctbl_chc_id => l_flex_credit_choice.elig_per_elctbl_chc_id
2507: --
2508: l_prnt_rt_val := abs(p_net_credit_val);
2509: --
2510: hr_utility.set_location('l_prnt_rt_val:'|| l_prnt_rt_val, 10);
2511: hr_utility.set_location('l_child_rt_val:'|| l_child_rt_val, 10);
2512: ben_determine_activity_base_rt.main
2513: (p_person_id => p_person_id
2514: ,p_elig_per_elctbl_chc_id => l_flex_credit_choice.elig_per_elctbl_chc_id
2515: ,p_acty_base_rt_id => l_child_acty_base_rt_id
2559: ,p_pp_in_yr_used_num => l_dummy_num
2560: ,p_ordr_num => l_dummy_num
2561: ,p_iss_val => l_dummy_num
2562: );
2563: hr_utility.set_location(l_proc, 70);
2564: hr_utility.set_location('l_child_rt_val:'|| l_child_rt_val, 10);
2565: end if;
2566: end if;
2567: --
2560: ,p_ordr_num => l_dummy_num
2561: ,p_iss_val => l_dummy_num
2562: );
2563: hr_utility.set_location(l_proc, 70);
2564: hr_utility.set_location('l_child_rt_val:'|| l_child_rt_val, 10);
2565: end if;
2566: end if;
2567: --
2568: -- call election_information api to create prtt enrt result
2577: (p_rt_val<>0 or
2578: (p_rt_val is null and
2579: p_net_credit_val is null))) then
2580: begin
2581: hr_utility.set_location(l_proc, 70);
2582: if l_flex_credit_choice.elig_per_elctbl_chc_id is not null then --Bug 2645624
2583: ben_election_information.election_information(
2584: p_elig_per_elctbl_chc_id => l_flex_credit_choice.elig_per_elctbl_chc_id,
2585: p_prtt_enrt_rslt_id => l_flex_credit_choice.prtt_enrt_rslt_id,
2615: end;
2616: elsif (l_flex_credit_choice.prtt_enrt_rslt_id is not null and
2617: (p_rt_val is not null
2618: or p_net_credit_val is not null)) then
2619: hr_utility.set_location(l_proc, 80);
2620: if p_rt_val is not null and l_flex_credit_choice.elig_per_elctbl_chc_id is not null then --Bug 2645624
2621: ben_election_information.election_information(
2622: p_elig_per_elctbl_chc_id => l_flex_credit_choice.elig_per_elctbl_chc_id,
2623: p_prtt_enrt_rslt_id => l_flex_credit_choice.prtt_enrt_rslt_id,
2699: -- store the created id in the global
2700: g_credit_pool_result_id:=l_flex_credit_choice.prtt_enrt_rslt_id;
2701: g_credit_pool_person_id:=p_person_id;
2702:
2703: hr_utility.set_location(' Leaving:'||l_proc, 999);
2704:
2705: end create_flex_credit_enrolment;
2706: --
2707: --------------------------------------------------------------------------------
2728: p_business_group_id=res.business_group_id ;
2729:
2730: begin
2731: --
2732: hr_utility.set_location('Entering:'|| l_proc, 10);
2733: --
2734: -- get the flex result from the ledger row(s)
2735: -- so don't have to do it first.
2736: --
2753: );
2754: --
2755: -- Don't update the flex credit row if necessary done in total_pools
2756: --
2757: hr_utility.set_location('Leaving:'||l_proc, 999);
2758: --
2759: end remove_bnft_prvdd_ldgr;
2760: --------------------------------------------------------------------------------
2761: -- forfeit_credits
2828: and pil.business_group_id=bpl.business_group_id
2829: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
2830: --
2831: begin
2832: hr_utility.set_location('Entering:'|| l_proc, 10);
2833: l_balance:=nvl(p_prvdd_val,0)
2834: +nvl(p_rlld_up_val,0)
2835: -nvl(p_used_val,0)
2836: -nvl(p_rollover_val,0)
2837: -nvl(p_cash_val,0);
2838: --
2839: l_balance_for_cr := l_balance ;
2840: --
2841: hr_utility.set_location('balance='||to_char(l_balance),15);
2842: open c_forfeit;
2843: fetch c_forfeit into
2844: l_bnft_prvdd_ldgr_id,
2845: l_frftd_val,
2846: l_object_version_number,
2847: l_effective_start_date;
2848: if c_forfeit%found then
2849: if l_balance<0 then
2850: hr_utility.set_location(l_proc, 16);
2851: l_balance:=0;
2852: end if;
2853: end if;
2854: --
2853: end if;
2854: --
2855: p_frftd_val:=l_balance;
2856: --
2857: hr_utility.set_location('frftd_val='||to_char(l_frftd_val),17);
2858: hr_utility.set_location('l_balance='||to_char(l_balance),17);
2859: --
2860: if l_frftd_val<>l_balance then
2861: hr_utility.set_location(l_proc, 18);
2854: --
2855: p_frftd_val:=l_balance;
2856: --
2857: hr_utility.set_location('frftd_val='||to_char(l_frftd_val),17);
2858: hr_utility.set_location('l_balance='||to_char(l_balance),17);
2859: --
2860: if l_frftd_val<>l_balance then
2861: hr_utility.set_location(l_proc, 18);
2862: --
2857: hr_utility.set_location('frftd_val='||to_char(l_frftd_val),17);
2858: hr_utility.set_location('l_balance='||to_char(l_balance),17);
2859: --
2860: if l_frftd_val<>l_balance then
2861: hr_utility.set_location(l_proc, 18);
2862: --
2863: -- update ledger row
2864: --
2865: /*
2874: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
2875: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
2876: p_base_key_value => l_bnft_prvdd_ldgr_id,
2877: p_mode => l_datetrack_mode);
2878: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),20);
2879: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
2880: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
2881: ,p_effective_start_date => l_effective_start_date
2882: ,p_effective_end_date => l_effective_end_date
2893: ,p_cash_recd_val => null
2894: ,p_effective_date => p_effective_date
2895: ,p_datetrack_mode => l_datetrack_mode
2896: );
2897: hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),30);
2898: else
2899: if l_balance>0 and l_frftd_val is null then
2900: --
2901: -- create a forfeit row
2899: if l_balance>0 and l_frftd_val is null then
2900: --
2901: -- create a forfeit row
2902: --
2903: hr_utility.set_location('result_id='||to_char(p_prtt_enrt_rslt_id),35);
2904: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
2905: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
2906: ,p_effective_start_date => l_effective_start_date
2907: ,p_effective_end_date => l_effective_end_date
2923: end if ;
2924: end if;
2925: --
2926: -- Bug 2645993
2927: hr_utility.set_location('CREATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),40);
2928: if l_balance_for_cr < 0 then
2929: --
2930: hr_utility.set_location('More Used than Provided case '||l_balance_for_cr ,30);
2931: open c_cash_rcvd;
2926: -- Bug 2645993
2927: hr_utility.set_location('CREATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),40);
2928: if l_balance_for_cr < 0 then
2929: --
2930: hr_utility.set_location('More Used than Provided case '||l_balance_for_cr ,30);
2931: open c_cash_rcvd;
2932: fetch c_cash_rcvd into
2933: l_bnft_prvdd_ldgr_id,
2934: l_cash_recd_val,
2935: l_object_version_number,
2936: l_effective_start_date;
2937: --
2938: -- When Used Value is more than the forefeited value
2939: hr_utility.set_location(' l_cash_recd_val '||l_cash_recd_val,31);
2940: if l_cash_recd_val is not null then
2941: --
2942: --if l_cash_recd_val > 0 then
2943: -- l_balance := l_cash_recd_val + l_balance ;
2948: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
2949: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
2950: p_base_key_value => l_bnft_prvdd_ldgr_id,
2951: p_mode => l_datetrack_mode);
2952: hr_utility.set_location('l_cash_recd_val is not null '||to_char(l_bnft_prvdd_ldgr_id),20);
2953: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
2954: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
2955: ,p_effective_start_date => l_effective_start_date
2956: ,p_effective_end_date => l_effective_end_date
2969: ,p_datetrack_mode => l_datetrack_mode
2970: );
2971: --
2972: else
2973: hr_utility.set_location('CR NULL ='||to_char(p_prtt_enrt_rslt_id),35);
2974: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
2975: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
2976: ,p_effective_start_date => l_effective_start_date
2977: ,p_effective_end_date => l_effective_end_date
3001: if l_balance_for_cr >= 0
3002: ----Bug 9754353
3003: and nvl(p_prvdd_val,0) >= nvl(p_used_val,0) then
3004: --Bug 9754353
3005: hr_utility.set_location('Provided more than used case' ,30.1);
3006: open c_cash_rcvd;
3007: fetch c_cash_rcvd into
3008: l_bnft_prvdd_ldgr_id,
3009: l_cash_recd_val,
3010: l_object_version_number,
3011: l_effective_start_date;
3012: --
3013: -- When provided is more than used and cash received value is found then reset it.
3014: hr_utility.set_location(' l_cash_recd_val '||l_cash_recd_val,31.1);
3015: if l_cash_recd_val is not null and l_cash_recd_val < 0 then
3016: --
3017: Get_DT_Upd_Mode
3018: (p_effective_date => p_effective_date,
3019: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
3020: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
3021: p_base_key_value => l_bnft_prvdd_ldgr_id,
3022: p_mode => l_datetrack_mode);
3023: hr_utility.set_location('l_cash_recd_val is not null '||to_char(l_bnft_prvdd_ldgr_id),20.1);
3024: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
3025: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
3026: ,p_effective_start_date => l_effective_start_date
3027: ,p_effective_end_date => l_effective_end_date
3045: --Bug 9388229
3046: end if;
3047: close c_forfeit;
3048: -- p_frftd_val:=l_balance;
3049: hr_utility.set_location('Leaving:'||l_proc||' p_frftd_val '||p_frftd_val , 999);
3050:
3051: end forfeit_credits;
3052: --------------------------------------------------------------------------------
3053: -- distribute_credits
3165: and p_effective_date between bpp.effective_start_date
3166: and bpp.effective_end_date;
3167:
3168: begin
3169: hr_utility.set_location('Entering:'|| l_proc, 10);
3170: hr_utility.set_location('p_prvdd_val:'||to_char(p_prvdd_val)||
3171: 'p_rlld_up_val:'||to_char(p_rlld_up_val),11);
3172: hr_utility.set_location('p_used_val:'||to_char(p_used_val),12);
3173: hr_utility.set_location('p_rollover_val:'||to_char(p_rollover_val),13);
3166: and bpp.effective_end_date;
3167:
3168: begin
3169: hr_utility.set_location('Entering:'|| l_proc, 10);
3170: hr_utility.set_location('p_prvdd_val:'||to_char(p_prvdd_val)||
3171: 'p_rlld_up_val:'||to_char(p_rlld_up_val),11);
3172: hr_utility.set_location('p_used_val:'||to_char(p_used_val),12);
3173: hr_utility.set_location('p_rollover_val:'||to_char(p_rollover_val),13);
3174: --
3168: begin
3169: hr_utility.set_location('Entering:'|| l_proc, 10);
3170: hr_utility.set_location('p_prvdd_val:'||to_char(p_prvdd_val)||
3171: 'p_rlld_up_val:'||to_char(p_rlld_up_val),11);
3172: hr_utility.set_location('p_used_val:'||to_char(p_used_val),12);
3173: hr_utility.set_location('p_rollover_val:'||to_char(p_rollover_val),13);
3174: --
3175: -- balance used depends on dflt_excs_trtmt_cd
3176: --
3169: hr_utility.set_location('Entering:'|| l_proc, 10);
3170: hr_utility.set_location('p_prvdd_val:'||to_char(p_prvdd_val)||
3171: 'p_rlld_up_val:'||to_char(p_rlld_up_val),11);
3172: hr_utility.set_location('p_used_val:'||to_char(p_used_val),12);
3173: hr_utility.set_location('p_rollover_val:'||to_char(p_rollover_val),13);
3174: --
3175: -- balance used depends on dflt_excs_trtmt_cd
3176: --
3177: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
3174: --
3175: -- balance used depends on dflt_excs_trtmt_cd
3176: --
3177: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
3178: hr_utility.set_location(l_proc,20 );
3179: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val;
3180: elsif p_dflt_excs_trtmt_cd = 'RLOVR_DSTRBT_FRFT' then
3181: hr_utility.set_location(l_proc,30 );
3182: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val-p_rollover_val;
3177: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
3178: hr_utility.set_location(l_proc,20 );
3179: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val;
3180: elsif p_dflt_excs_trtmt_cd = 'RLOVR_DSTRBT_FRFT' then
3181: hr_utility.set_location(l_proc,30 );
3182: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val-p_rollover_val;
3183: else
3184: hr_utility.set_location(l_proc,40 );
3185: fnd_message.set_name('BEN','BEN_DFLT_TRTMT_NOT_HNDLD');
3180: elsif p_dflt_excs_trtmt_cd = 'RLOVR_DSTRBT_FRFT' then
3181: hr_utility.set_location(l_proc,30 );
3182: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val-p_rollover_val;
3183: else
3184: hr_utility.set_location(l_proc,40 );
3185: fnd_message.set_name('BEN','BEN_DFLT_TRTMT_NOT_HNDLD');
3186: fnd_message.set_token('PROC',l_proc);
3187: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3188: fnd_message.set_token('DFLT_EXCS_TRTMT_CD',p_dflt_excs_trtmt_cd);
3187: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3188: fnd_message.set_token('DFLT_EXCS_TRTMT_CD',p_dflt_excs_trtmt_cd);
3189: fnd_message.raise_error;
3190: end if;
3191: hr_utility.set_location(l_proc,50 );
3192: --
3193: -- balance is rounded
3194: --
3195: l_acty_base_rt_id := p_acty_base_rt_id;
3193: -- balance is rounded
3194: --
3195: l_acty_base_rt_id := p_acty_base_rt_id;
3196: --
3197: hr_utility.set_location('['||p_val_rndg_cd||']',55);
3198: if p_val_rndg_cd is not null then
3199: l_balance:=benutils.do_rounding(
3200: p_rounding_cd => p_val_rndg_cd,
3201: p_rounding_rl => p_val_rndg_rl,
3201: p_rounding_rl => p_val_rndg_rl,
3202: p_value => l_balance,
3203: p_effective_date => p_effective_date);
3204: end if;
3205: hr_utility.set_location(l_proc,60 );
3206: --
3207: -- if the rounded balance is less than the minimum val or pct, then nothing to do
3208: --
3209: -- do min val first
3209: -- do min val first
3210: --
3211: if p_mn_dstrbl_val is not null then
3212: if p_mn_dstrbl_val > l_balance then
3213: hr_utility.set_location('Leaving with zero distributed:'||l_proc,97);
3214: p_dstrbtd_val:=0;
3215: return;
3216: end if;
3217: end if;
3214: p_dstrbtd_val:=0;
3215: return;
3216: end if;
3217: end if;
3218: hr_utility.set_location(l_proc,80 );
3219: --
3220: -- do min pct second
3221: --
3222: if p_mn_dstrbl_pct_num is not null then
3219: --
3220: -- do min pct second
3221: --
3222: if p_mn_dstrbl_pct_num is not null then
3223: hr_utility.set_location(l_proc,90 );
3224: l_mn_dstrbl_val:=(p_mn_dstrbl_pct_num/100)*(p_prvdd_val+p_rlld_up_val-p_used_val);
3225: if p_pct_rndg_cd is not null then
3226: l_mn_dstrbl_val:=benutils.do_rounding(
3227: p_rounding_cd => p_pct_rndg_cd,
3229: p_value => l_mn_dstrbl_val,
3230: p_effective_date => p_effective_date);
3231: end if;
3232: if l_mn_dstrbl_val > l_balance then
3233: hr_utility.set_location(l_proc,100 );
3234: p_dstrbtd_val:=0;
3235: return;
3236: end if;
3237: hr_utility.set_location(l_proc,110 );
3233: hr_utility.set_location(l_proc,100 );
3234: p_dstrbtd_val:=0;
3235: return;
3236: end if;
3237: hr_utility.set_location(l_proc,110 );
3238: end if;
3239: hr_utility.set_location(l_proc,120 );
3240: --
3241: -- if the rounded balance is more than the maximum val or pct, then reduce it to the maximum
3235: return;
3236: end if;
3237: hr_utility.set_location(l_proc,110 );
3238: end if;
3239: hr_utility.set_location(l_proc,120 );
3240: --
3241: -- if the rounded balance is more than the maximum val or pct, then reduce it to the maximum
3242: --
3243: l_cash_val:=l_balance;
3245: -- do max val next
3246: --
3247: if p_mx_dstrbl_val is not null then
3248: if p_mx_dstrbl_val < l_cash_val then
3249: hr_utility.set_location(l_proc,130 );
3250: l_cash_val:=p_mx_dstrbl_val;
3251: end if;
3252: end if;
3253: hr_utility.set_location(l_proc,140 );
3249: hr_utility.set_location(l_proc,130 );
3250: l_cash_val:=p_mx_dstrbl_val;
3251: end if;
3252: end if;
3253: hr_utility.set_location(l_proc,140 );
3254: --
3255: -- do max pct second
3256: --
3257: if p_mx_pct is not null then
3254: --
3255: -- do max pct second
3256: --
3257: if p_mx_pct is not null then
3258: hr_utility.set_location(l_proc,150 );
3259: l_mx_dstrbl_val:=(p_mx_pct/100)*(p_prvdd_val+p_rlld_up_val-p_used_val);
3260: if p_pct_rndg_cd is not null then
3261: l_mx_dstrbl_val:=benutils.do_rounding(
3262: p_rounding_cd => p_pct_rndg_cd,
3264: p_value => l_mx_dstrbl_val,
3265: p_effective_date => p_effective_date);
3266: end if;
3267: if l_mx_dstrbl_val < l_cash_val then
3268: hr_utility.set_location(l_proc,160 );
3269: l_cash_val:=l_mx_dstrbl_val;
3270: end if;
3271: hr_utility.set_location(l_proc,170 );
3272: end if;
3267: if l_mx_dstrbl_val < l_cash_val then
3268: hr_utility.set_location(l_proc,160 );
3269: l_cash_val:=l_mx_dstrbl_val;
3270: end if;
3271: hr_utility.set_location(l_proc,170 );
3272: end if;
3273: hr_utility.set_location(l_proc,180 );
3274: --
3275: -- create the cash ledger entry
3269: l_cash_val:=l_mx_dstrbl_val;
3270: end if;
3271: hr_utility.set_location(l_proc,170 );
3272: end if;
3273: hr_utility.set_location(l_proc,180 );
3274: --
3275: -- create the cash ledger entry
3276: --
3277: p_dstrbtd_val:=l_cash_val;
3278: --
3279: if l_cash_val<>p_cash_recd_total
3280: and not p_calculate_only_mode
3281: then
3282: hr_utility.set_location(l_proc,190 );
3283: hr_utility.set_location('ACE l_cash_val = ' || l_cash_val, 9999);
3284: hr_utility.set_location('ACE p_cash_recd_total = ' || p_cash_recd_total, 9999);
3285: --
3286: -- get cash abr
3279: if l_cash_val<>p_cash_recd_total
3280: and not p_calculate_only_mode
3281: then
3282: hr_utility.set_location(l_proc,190 );
3283: hr_utility.set_location('ACE l_cash_val = ' || l_cash_val, 9999);
3284: hr_utility.set_location('ACE p_cash_recd_total = ' || p_cash_recd_total, 9999);
3285: --
3286: -- get cash abr
3287: --
3280: and not p_calculate_only_mode
3281: then
3282: hr_utility.set_location(l_proc,190 );
3283: hr_utility.set_location('ACE l_cash_val = ' || l_cash_val, 9999);
3284: hr_utility.set_location('ACE p_cash_recd_total = ' || p_cash_recd_total, 9999);
3285: --
3286: -- get cash abr
3287: --
3288: open c_cash_abr;
3304: then
3305: close c_cash_abr_from_epe;
3306: -- Bug 5447507
3307: --
3308: hr_utility.set_location(l_proc,200 );
3309: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
3310: fnd_message.set_token('PROC',l_proc);
3311: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3312: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 220);
3308: hr_utility.set_location(l_proc,200 );
3309: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
3310: fnd_message.set_token('PROC',l_proc);
3311: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3312: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 220);
3313: hr_utility.set_location('pool_id='||to_char(p_bnft_prvdr_pool_id), 230);
3314: fnd_message.raise_error;
3315: --
3316: end if;
3309: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
3310: fnd_message.set_token('PROC',l_proc);
3311: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3312: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 220);
3313: hr_utility.set_location('pool_id='||to_char(p_bnft_prvdr_pool_id), 230);
3314: fnd_message.raise_error;
3315: --
3316: end if;
3317: --
3317: --
3318: close c_cash_abr_from_epe;
3319: --
3320: else
3321: hr_utility.set_location(l_proc,205 );
3322: close c_cash_abr;
3323: end if;
3324: hr_utility.set_location('ACE l_acty_base_rt_id = ' || l_acty_base_rt_id, 99);
3325: hr_utility.set_location(l_proc,210 );
3320: else
3321: hr_utility.set_location(l_proc,205 );
3322: close c_cash_abr;
3323: end if;
3324: hr_utility.set_location('ACE l_acty_base_rt_id = ' || l_acty_base_rt_id, 99);
3325: hr_utility.set_location(l_proc,210 );
3326: --
3327: hr_utility.set_location(l_proc, 220);
3328: --
3321: hr_utility.set_location(l_proc,205 );
3322: close c_cash_abr;
3323: end if;
3324: hr_utility.set_location('ACE l_acty_base_rt_id = ' || l_acty_base_rt_id, 99);
3325: hr_utility.set_location(l_proc,210 );
3326: --
3327: hr_utility.set_location(l_proc, 220);
3328: --
3329: if p_process_enrt_flag = 'Y'
3323: end if;
3324: hr_utility.set_location('ACE l_acty_base_rt_id = ' || l_acty_base_rt_id, 99);
3325: hr_utility.set_location(l_proc,210 );
3326: --
3327: hr_utility.set_location(l_proc, 220);
3328: --
3329: if p_process_enrt_flag = 'Y'
3330: and not p_calculate_only_mode
3331: then
3334: --
3335: if (g_credit_pool_result_id is null or
3336: g_credit_pool_person_id is null or
3337: g_credit_pool_person_id<>p_person_id) then
3338: hr_utility.set_location(l_proc, 250);
3339: --
3340: open c_bnft_pool;
3341: fetch c_bnft_pool into l_pgm_id;
3342: close c_bnft_pool;
3351: p_per_in_ler_id => p_per_in_ler_id,
3352: p_rt_val => null,
3353: p_pgm_id => l_pgm_id
3354: );
3355: hr_utility.set_location(l_proc, 260);
3356: end if;
3357: --
3358: end if;
3359: hr_utility.set_location(l_proc,270 );
3355: hr_utility.set_location(l_proc, 260);
3356: end if;
3357: --
3358: end if;
3359: hr_utility.set_location(l_proc,270 );
3360: --
3361: open c_old_ledger
3362: (c_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
3363: ,c_prtt_enrt_rslt_id => g_credit_pool_result_id
3369: l_effective_start_date;
3370: --
3371: -- insert cash row
3372: --
3373: hr_utility.set_location(l_proc, 280);
3374: if c_old_ledger%notfound
3375: and not p_calculate_only_mode
3376: then
3377: hr_utility.set_location('insert cash row', 290);
3373: hr_utility.set_location(l_proc, 280);
3374: if c_old_ledger%notfound
3375: and not p_calculate_only_mode
3376: then
3377: hr_utility.set_location('insert cash row', 290);
3378: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
3379: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
3380: ,p_effective_start_date => l_effective_start_date
3381: ,p_effective_end_date => l_effective_end_date
3393: ,p_object_version_number => l_object_version_number
3394: ,p_cash_recd_val => l_cash_val
3395: ,p_effective_date => p_effective_date
3396: );
3397: hr_utility.set_location('CREATED CASH LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),300);
3398: elsif l_old_cash_val<>l_cash_val
3399: and not p_calculate_only_mode
3400: then
3401: hr_utility.set_location(l_proc, 310);
3397: hr_utility.set_location('CREATED CASH LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),300);
3398: elsif l_old_cash_val<>l_cash_val
3399: and not p_calculate_only_mode
3400: then
3401: hr_utility.set_location(l_proc, 310);
3402: /* if l_effective_start_date=p_effective_date then
3403: hr_utility.set_location(l_proc,320 );
3404: l_datetrack_mode:=hr_api.g_correction;
3405: else
3399: and not p_calculate_only_mode
3400: then
3401: hr_utility.set_location(l_proc, 310);
3402: /* if l_effective_start_date=p_effective_date then
3403: hr_utility.set_location(l_proc,320 );
3404: l_datetrack_mode:=hr_api.g_correction;
3405: else
3406: hr_utility.set_location(l_proc,330 );
3407: l_datetrack_mode:=hr_api.g_update;
3402: /* if l_effective_start_date=p_effective_date then
3403: hr_utility.set_location(l_proc,320 );
3404: l_datetrack_mode:=hr_api.g_correction;
3405: else
3406: hr_utility.set_location(l_proc,330 );
3407: l_datetrack_mode:=hr_api.g_update;
3408: end if; */
3409: Get_DT_Upd_Mode
3410: (p_effective_date => p_effective_date,
3411: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
3412: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
3413: p_base_key_value => l_bnft_prvdd_ldgr_id,
3414: p_mode => l_datetrack_mode);
3415: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),340);
3416: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
3417: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
3418: ,p_effective_start_date => l_effective_start_date
3419: ,p_effective_end_date => l_effective_end_date
3430: ,p_cash_recd_val => l_cash_val
3431: ,p_effective_date => p_effective_date
3432: ,p_datetrack_mode => l_datetrack_mode
3433: );
3434: hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),350);
3435: end if;
3436: hr_utility.set_location(l_proc,360 );
3437: close c_old_ledger;
3438: --
3432: ,p_datetrack_mode => l_datetrack_mode
3433: );
3434: hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),350);
3435: end if;
3436: hr_utility.set_location(l_proc,360 );
3437: close c_old_ledger;
3438: --
3439: end if;
3440: hr_utility.set_location('Leaving:'||l_proc, 999);
3436: hr_utility.set_location(l_proc,360 );
3437: close c_old_ledger;
3438: --
3439: end if;
3440: hr_utility.set_location('Leaving:'||l_proc, 999);
3441: --
3442: -- Set out param
3443: --
3444: p_bpl_cash_recd_val := l_cash_val;
3734: pil_flex1.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
3735: pil_flex1.business_group_id=p_business_group_id and
3736: pil_flex.per_in_ler_id=p_per_in_ler_id ;
3737: begin
3738: hr_utility.set_location('Entering:'|| l_proc, 10);
3739: l_rollover_diff_total:=0;
3740: for l_rollover in c_rollovers loop
3741: --
3742: -- If rule exists run if and see if qualifies for rollover
3775: if l_qualify_flag='Y' then
3776: --
3777: -- Process this rollover
3778: --
3779: hr_utility.set_location(l_proc, 20);
3780: l_acty_base_rt_id:=l_rollover.acty_base_rt_id;
3781: --
3782: -- Get the ledger totals with exclusions (see cursor)
3783: --
3791: --
3792: -- depending on the excess treatment code compute the balance
3793: --
3794: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
3795: hr_utility.set_location(l_proc, 30);
3796: l_balance:=l_prvdd_val-l_used_val-l_recd_val-l_rld_ovr_val;
3797: else
3798: hr_utility.set_location(l_proc, 40);
3799: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
3794: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
3795: hr_utility.set_location(l_proc, 30);
3796: l_balance:=l_prvdd_val-l_used_val-l_recd_val-l_rld_ovr_val;
3797: else
3798: hr_utility.set_location(l_proc, 40);
3799: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
3800: end if;
3801: hr_utility.set_location(l_proc, 50);
3802: --
3797: else
3798: hr_utility.set_location(l_proc, 40);
3799: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
3800: end if;
3801: hr_utility.set_location(l_proc, 50);
3802: --
3803: -- round the balance
3804: --
3805: if l_rollover.val_rndg_cd is not null then
3920: l_balance < l_rollover.mn_rlovr_val or
3921: l_balance < l_mn_dstrbl_pct_num or
3922: l_balance <=0 )
3923: then
3924: hr_utility.set_location('Balance less than minimum or zero',60);
3925: hr_utility.set_location('l_balance : '||l_balance,60);
3926:
3927: --Bug 7118730
3928: open c_old_ledger;
3921: l_balance < l_mn_dstrbl_pct_num or
3922: l_balance <=0 )
3923: then
3924: hr_utility.set_location('Balance less than minimum or zero',60);
3925: hr_utility.set_location('l_balance : '||l_balance,60);
3926:
3927: --Bug 7118730
3928: open c_old_ledger;
3929: fetch c_old_ledger into
3964: --
3965: -- nothing to do
3966: --
3967: else
3968: hr_utility.set_location(l_proc, 70);
3969: --
3970: -- Find out amount of rollover
3971: --
3972: open c_old_ledger;
3978: ;
3979: --
3980: -- perform rollover
3981: --
3982: hr_utility.set_location(l_proc, 80);
3983: if c_old_ledger%notfound then
3984: hr_utility.set_location(l_proc, 90);
3985: l_rollover_diff_total:=l_rollover_diff_total+l_balance;
3986: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
3980: -- perform rollover
3981: --
3982: hr_utility.set_location(l_proc, 80);
3983: if c_old_ledger%notfound then
3984: hr_utility.set_location(l_proc, 90);
3985: l_rollover_diff_total:=l_rollover_diff_total+l_balance;
3986: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
3987: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
3988: ,p_effective_start_date => l_effective_start_date
4001: ,p_object_version_number => l_object_version_number
4002: ,p_cash_recd_val => null
4003: ,p_effective_date => p_effective_date
4004: );
4005: hr_utility.set_location('CREATED LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),100);
4006: else --if l_old_rlovr_val<>l_balance then-------Bug 7363185
4007: hr_utility.set_location(l_proc, 110);
4008: /*
4009: if l_effective_start_date=p_effective_date then
4003: ,p_effective_date => p_effective_date
4004: );
4005: hr_utility.set_location('CREATED LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),100);
4006: else --if l_old_rlovr_val<>l_balance then-------Bug 7363185
4007: hr_utility.set_location(l_proc, 110);
4008: /*
4009: if l_effective_start_date=p_effective_date then
4010: l_datetrack_mode:=hr_api.g_correction;
4011: else
4017: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
4018: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
4019: p_base_key_value => l_bnft_prvdd_ldgr_id,
4020: p_mode => l_datetrack_mode);
4021: hr_utility.set_location('UPDATING LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),120);
4022: l_rollover_diff_total:=l_rollover_diff_total+l_balance-l_old_rlovr_val;
4023: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
4024: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
4025: ,p_effective_start_date => l_effective_start_date
4037: ,p_cash_recd_val => 0
4038: ,p_effective_date => p_effective_date
4039: ,p_datetrack_mode => l_datetrack_mode
4040: );
4041: hr_utility.set_location('UPDATED LEDGER (ID='||to_char(l_bnft_prvdd_ldgr_id),130);
4042: end if;
4043: close c_old_ledger;
4044: end if;
4045: end if;
4043: close c_old_ledger;
4044: end if;
4045: end if;
4046: end if; -- rule returned 'Y'
4047: hr_utility.set_location(l_proc, 140);
4048: end loop;
4049: if l_rollover_diff_total is null then
4050: p_rollover_val := 0;
4051: else p_rollover_val:=l_rollover_diff_total;
4049: if l_rollover_diff_total is null then
4050: p_rollover_val := 0;
4051: else p_rollover_val:=l_rollover_diff_total;
4052: end if;
4053: hr_utility.set_location('We rolled over $'||to_char(l_rollover_diff_total), 998);
4054: hr_utility.set_location('Leaving:'||l_proc, 999);
4055: end default_rollovers;
4056: --
4057: -- Bug 2185478 Added procedure to validate the rollover value entered by the user on
4050: p_rollover_val := 0;
4051: else p_rollover_val:=l_rollover_diff_total;
4052: end if;
4053: hr_utility.set_location('We rolled over $'||to_char(l_rollover_diff_total), 998);
4054: hr_utility.set_location('Leaving:'||l_proc, 999);
4055: end default_rollovers;
4056: --
4057: -- Bug 2185478 Added procedure to validate the rollover value entered by the user on
4058: -- flex enrollment form and show proper message immediately
4092: l_rollover_val number;
4093: --
4094: begin
4095: --
4096: hr_utility.set_location('Entering:'||l_proc, 10);
4097: --
4098: -- Initialise globals.
4099: --
4100: g_balance := null;
4118: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4119: fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
4120: fnd_message.set_token('BNFT_PRVDR_POOL_ID',
4121: to_char(p_bnft_prvdr_pool_id));
4122: hr_utility.set_location(l_proc,20);
4123: fnd_message.raise_error;
4124: end if;
4125: hr_utility.set_location(l_proc,30 );
4126: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_RLOVR_FRFT','RLOVR_DSTRBT_FRFT') then
4121: to_char(p_bnft_prvdr_pool_id));
4122: hr_utility.set_location(l_proc,20);
4123: fnd_message.raise_error;
4124: end if;
4125: hr_utility.set_location(l_proc,30 );
4126: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_RLOVR_FRFT','RLOVR_DSTRBT_FRFT') then
4127: --
4128: default_rollovers(
4129: p_calculate_only_mode => true,
4141: p_enrt_mthd_cd => p_enrt_mthd_cd);
4142: --
4143: end if;
4144: --
4145: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
4146: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
4147: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
4148: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4142: --
4143: end if;
4144: --
4145: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
4146: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
4147: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
4148: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4143: end if;
4144: --
4145: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
4146: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
4147: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
4148: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4151: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
4144: --
4145: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
4146: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
4147: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
4148: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4151: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
4152: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
4145: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
4146: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
4147: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
4148: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4151: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
4152: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
4153: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
4146: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
4147: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
4148: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4151: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
4152: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
4153: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
4154: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
4147: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
4148: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4151: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
4152: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
4153: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
4154: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
4155: --
4148: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4151: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
4152: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
4153: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
4154: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
4155: --
4156: -- Raise errors if entered rollover value exceeds any limits set
4149: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4151: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
4152: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
4153: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
4154: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
4155: --
4156: -- Raise errors if entered rollover value exceeds any limits set
4157: --
4150: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
4151: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
4152: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
4153: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
4154: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
4155: --
4156: -- Raise errors if entered rollover value exceeds any limits set
4157: --
4158: if p_new_rollover_val /* g_balance */ > g_mx_dstrbl_pct_num then
4157: --
4158: if p_new_rollover_val /* g_balance */ > g_mx_dstrbl_pct_num then
4159: --
4160: fnd_message.set_name('BEN','BEN_92960_RLOVR_VAL_GT_MXDPN');
4161: hr_utility.set_location(l_proc,40);
4162: fnd_message.raise_error;
4163: --
4164: end if;
4165: --
4165: --
4166: if p_new_rollover_val /* g_balance */ > g_mx_rlovr_val then
4167: --
4168: fnd_message.set_name('BEN','BEN_92961_RLOVR_VAL_GT_MXRV');
4169: hr_utility.set_location(l_proc,41);
4170: fnd_message.raise_error;
4171: --
4172: end if;
4173: if p_new_rollover_val /* g_balance */ > g_mx_elcn_val then
4172: end if;
4173: if p_new_rollover_val /* g_balance */ > g_mx_elcn_val then
4174: --
4175: fnd_message.set_name('BEN','BEN_92962_RLOVR_VAL_GT_MXEV');
4176: hr_utility.set_location(l_proc,42);
4177: fnd_message.raise_error;
4178: --
4179: end if;
4180: --
4180: --
4181: if p_new_rollover_val /* g_balance */ > g_mx_rlovr_rl_val then
4182: --
4183: fnd_message.set_name('BEN','BEN_92963_RLOVR_VAL_GT_MXRRV');
4184: hr_utility.set_location(l_proc,43);
4185: fnd_message.raise_error;
4186: --
4187: end if;
4188: --
4189: --
4190: if p_new_rollover_val /* g_balance */ < g_mn_dstrbl_pct_num then
4191: --
4192: fnd_message.set_name('BEN','BEN_92964_RLOVR_VAL_GT_MNDPN');
4193: hr_utility.set_location(l_proc,44);
4194: fnd_message.raise_error;
4195: --
4196: end if;
4197: --
4197: --
4198: if p_new_rollover_val /* g_balance */ < g_mn_rlovr_val then
4199: --
4200: fnd_message.set_name('BEN','BEN_92965_RLOVR_VAL_GT_MNRV');
4201: hr_utility.set_location(l_proc,45);
4202: fnd_message.raise_error;
4203: --
4204: end if;
4205: if p_new_rollover_val /* g_balance */ < g_mn_elcn_val then
4204: end if;
4205: if p_new_rollover_val /* g_balance */ < g_mn_elcn_val then
4206: --
4207: fnd_message.set_name('BEN','BEN_92966_RLOVR_VAL_GT_MNEV');
4208: hr_utility.set_location(l_proc,46);
4209: fnd_message.raise_error;
4210: --
4211: end if;
4212: --
4220: g_mn_dstrbl_pct_num := null;
4221: g_mn_rlovr_val := null;
4222: g_mn_elcn_val := null;
4223: --
4224: hr_utility.set_location('Leaving:'||l_proc, 40);
4225: --
4226: end validate_rollover_val;
4227: --------------------------------------------------------------------------------
4228: -- create_rollover_enrollment
4336: l_payroll_id per_all_assignments_f.payroll_id%type;
4337: l_organization_id per_all_assignments_f.organization_id%type;
4338: --END GEVITY
4339: begin
4340: hr_utility.set_location('Entering:'|| l_proc, 10);
4341: hr_utility.set_location('crdt_pool_rslt_id='||to_char(g_credit_pool_result_id),10);
4342: --
4343: -- Get choice id of what you are trying to roll into
4344: open c_rlovr_chc;
4337: l_organization_id per_all_assignments_f.organization_id%type;
4338: --END GEVITY
4339: begin
4340: hr_utility.set_location('Entering:'|| l_proc, 10);
4341: hr_utility.set_location('crdt_pool_rslt_id='||to_char(g_credit_pool_result_id),10);
4342: --
4343: -- Get choice id of what you are trying to roll into
4344: open c_rlovr_chc;
4345: fetch c_rlovr_chc into l_rlovr_chc;
4343: -- Get choice id of what you are trying to roll into
4344: open c_rlovr_chc;
4345: fetch c_rlovr_chc into l_rlovr_chc;
4346: if c_rlovr_chc%notfound then
4347: hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND ACTY_id:'||
4348: to_char(p_acty_base_rt_id), 20); hr_utility.set_location('p_person_id:'||to_char(p_person_id), 20);
4349: hr_utility.set_location('p_business_group_id:'||to_char(p_business_group_id), 20);
4350: close c_rlovr_chc;
4351: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4344: open c_rlovr_chc;
4345: fetch c_rlovr_chc into l_rlovr_chc;
4346: if c_rlovr_chc%notfound then
4347: hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND ACTY_id:'||
4348: to_char(p_acty_base_rt_id), 20); hr_utility.set_location('p_person_id:'||to_char(p_person_id), 20);
4349: hr_utility.set_location('p_business_group_id:'||to_char(p_business_group_id), 20);
4350: close c_rlovr_chc;
4351: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4352: fnd_message.set_token('ID', 'NA');
4345: fetch c_rlovr_chc into l_rlovr_chc;
4346: if c_rlovr_chc%notfound then
4347: hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND ACTY_id:'||
4348: to_char(p_acty_base_rt_id), 20); hr_utility.set_location('p_person_id:'||to_char(p_person_id), 20);
4349: hr_utility.set_location('p_business_group_id:'||to_char(p_business_group_id), 20);
4350: close c_rlovr_chc;
4351: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4352: fnd_message.set_token('ID', 'NA');
4353: fnd_message.set_token('PROC',l_proc);
4368: end if;
4369: --
4370: -- compute the new prtt_rt_val
4371: -- start bug 5608160
4372: hr_utility.set_location('Entering: SSARKAR ', 10);
4373:
4374: if nvl(l_rlovr_chc.entr_val_at_enrt_flag,'N') = 'N' then
4375: l_new_prtt_rt_val:=l_old_prtt_rt_val_amt+p_rlovr_amt;
4376: else
4378: end if ;
4379:
4380: -- end bug 5608160
4381:
4382: hr_utility.set_location('l_new_prtt_rt_val='||l_new_prtt_rt_val,10);
4383: hr_utility.set_location('l_old_prtt_rt_val_amt='||l_old_prtt_rt_val_amt,10);
4384: hr_utility.set_location('p_old_rlovr_amt='||p_old_rlovr_amt,10);
4385: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4386: --
4379:
4380: -- end bug 5608160
4381:
4382: hr_utility.set_location('l_new_prtt_rt_val='||l_new_prtt_rt_val,10);
4383: hr_utility.set_location('l_old_prtt_rt_val_amt='||l_old_prtt_rt_val_amt,10);
4384: hr_utility.set_location('p_old_rlovr_amt='||p_old_rlovr_amt,10);
4385: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4386: --
4387: -- create the result
4380: -- end bug 5608160
4381:
4382: hr_utility.set_location('l_new_prtt_rt_val='||l_new_prtt_rt_val,10);
4383: hr_utility.set_location('l_old_prtt_rt_val_amt='||l_old_prtt_rt_val_amt,10);
4384: hr_utility.set_location('p_old_rlovr_amt='||p_old_rlovr_amt,10);
4385: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4386: --
4387: -- create the result
4388: --
4381:
4382: hr_utility.set_location('l_new_prtt_rt_val='||l_new_prtt_rt_val,10);
4383: hr_utility.set_location('l_old_prtt_rt_val_amt='||l_old_prtt_rt_val_amt,10);
4384: hr_utility.set_location('p_old_rlovr_amt='||p_old_rlovr_amt,10);
4385: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4386: --
4387: -- create the result
4388: --
4389: hr_utility.set_location('enrt_rt_id='||to_char(l_rlovr_chc.enrt_rt_id),40);
4385: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4386: --
4387: -- create the result
4388: --
4389: hr_utility.set_location('enrt_rt_id='||to_char(l_rlovr_chc.enrt_rt_id),40);
4390: hr_utility.set_location('choice_id='||to_char(l_rlovr_chc.elig_per_elctbl_chc_id),50);
4391: hr_utility.set_location('result_id='||to_char(l_rlovr_chc.prtt_enrt_rslt_id),60);
4392: --
4393: -- Calculate the annual value.
4386: --
4387: -- create the result
4388: --
4389: hr_utility.set_location('enrt_rt_id='||to_char(l_rlovr_chc.enrt_rt_id),40);
4390: hr_utility.set_location('choice_id='||to_char(l_rlovr_chc.elig_per_elctbl_chc_id),50);
4391: hr_utility.set_location('result_id='||to_char(l_rlovr_chc.prtt_enrt_rslt_id),60);
4392: --
4393: -- Calculate the annual value.
4394: --
4387: -- create the result
4388: --
4389: hr_utility.set_location('enrt_rt_id='||to_char(l_rlovr_chc.enrt_rt_id),40);
4390: hr_utility.set_location('choice_id='||to_char(l_rlovr_chc.elig_per_elctbl_chc_id),50);
4391: hr_utility.set_location('result_id='||to_char(l_rlovr_chc.prtt_enrt_rslt_id),60);
4392: --
4393: -- Calculate the annual value.
4394: --
4395: if l_new_prtt_rt_val > 0 and l_rlovr_chc.enrt_rt_id is not null then
4458: else
4459: l_datetrack_mode:=hr_api.g_insert;
4460: end if;
4461: --
4462: hr_utility.set_location(l_proc, 70);
4463: ben_election_information.election_information(
4464: p_elig_per_elctbl_chc_id => l_rlovr_chc.elig_per_elctbl_chc_id,
4465: p_prtt_enrt_rslt_id => l_rlovr_chc.prtt_enrt_rslt_id,
4466: p_effective_date => p_effective_date,
4490: p_dpnt_actn_warning => l_dpnt_actn_warning,
4491: p_bnf_actn_warning => l_bnf_actn_warning,
4492: p_ctfn_actn_warning => l_ctfn_actn_warning
4493: );
4494: hr_utility.set_location('Leaving:'||l_proc, 999);
4495: end create_rollover_enrollment;
4496: --------------------------------------------------------------------------------
4497: -- total_pools
4498: --------------------------------------------------------------------------------
4689:
4690: l_default_enrt_flag varchar2(1) := 'N' ;
4691: l_dummy_var varchar2(1) ;
4692: begin
4693: hr_utility.set_location('Entering:'|| l_proc, 10);
4694:
4695: -- Get the flex credit result id and it's per-in-ler. Both are needed
4696: -- for when we create ledger rows.
4697: create_flex_credit_enrolment(
4719: end if ;
4720: close c_get_mthd ;
4721:
4722: open c_ledger_totals;
4723: hr_utility.set_location(l_proc, 20);
4724:
4725: loop
4726: hr_utility.set_location(l_proc, 25);
4727: --
4722: open c_ledger_totals;
4723: hr_utility.set_location(l_proc, 20);
4724:
4725: loop
4726: hr_utility.set_location(l_proc, 25);
4727: --
4728: fetch c_ledger_totals into l_ledger_totals;
4729: hr_utility.set_location(l_proc, 30);
4730: --
4725: loop
4726: hr_utility.set_location(l_proc, 25);
4727: --
4728: fetch c_ledger_totals into l_ledger_totals;
4729: hr_utility.set_location(l_proc, 30);
4730: --
4731: exit when c_ledger_totals%notfound;
4732: hr_utility.set_location(l_proc, 55);
4733: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4728: fetch c_ledger_totals into l_ledger_totals;
4729: hr_utility.set_location(l_proc, 30);
4730: --
4731: exit when c_ledger_totals%notfound;
4732: hr_utility.set_location(l_proc, 55);
4733: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4734: 'used_total = ' || l_ledger_totals.used_total ||
4735: 'frftd_total = ' || l_ledger_totals.frftd_total ||
4736: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
4729: hr_utility.set_location(l_proc, 30);
4730: --
4731: exit when c_ledger_totals%notfound;
4732: hr_utility.set_location(l_proc, 55);
4733: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4734: 'used_total = ' || l_ledger_totals.used_total ||
4735: 'frftd_total = ' || l_ledger_totals.frftd_total ||
4736: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
4737: --
4737: --
4738: -- Total up credits provided
4739: --
4740: l_total_credits:=l_total_credits+l_ledger_totals.prvdd_total;
4741: hr_utility.set_location( 'l_total_credits = ' || l_total_credits, 55);
4742: --
4743: -- get the generic ABR to use for cash and forfeitures.
4744: --
4745: l_acty_base_rt_id := null;
4759: fetch c_get_pool_abr into l_acty_base_rt_id;
4760: if c_get_pool_abr%notfound then
4761: close c_cash_abr;
4762: close c_get_pool_abr;
4763: hr_utility.set_location(l_proc,15);
4764: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
4765: fnd_message.set_token('PROC',l_proc);
4766: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4767: fnd_message.raise_error;
4782: null;
4783: else
4784: --
4785: open c_pool_info;
4786: hr_utility.set_location(l_proc, 60);
4787: fetch c_pool_info into l_pool_info;
4788: if c_pool_info%notfound then
4789: --
4790: -- error
4795: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4796: fnd_message.set_token('ACTY_BASE_RT_ID',to_char(l_acty_base_rt_id));
4797: fnd_message.set_token('BNFT_PRVDR_POOL_ID',
4798: to_char(l_ledger_totals.bnft_prvdr_pool_id));
4799: hr_utility.set_location(l_proc,70);
4800: fnd_message.raise_error;
4801: end if;
4802: close c_pool_info;
4803: --
4811: -- Check if there is a deficit amount.
4812: --
4813: l_def_exc_amt :=
4814: l_ledger_totals.prvdd_total - l_ledger_totals.used_total;
4815: hr_utility.set_location('l_def_exc_amt: '||l_def_exc_amt,70);
4816: --
4817: if l_def_exc_amt <> 0 then
4818: if l_def_exc_amt < 0 then
4819: --
4823: --
4824: if l_pool_info.mx_dfcit_pct_pool_crs_num is not null then
4825: l_deficit_limit :=
4826: l_pool_info.mx_dfcit_pct_pool_crs_num/100 * l_ledger_totals.prvdd_total;
4827: hr_utility.set_location('l_deficit_limit: '||l_deficit_limit,70);
4828: if abs(l_def_exc_amt) > l_deficit_limit then
4829: fnd_message.set_name('BEN', 'BEN_92620_EXCEED_TOT_DFCIT_PCT');
4830: fnd_message.raise_error;
4831: end if;
4849: );
4850: --
4851: l_deficit_limit :=
4852: l_pool_info.mx_dfcit_pct_comp_num/100 * l_compensation_value;
4853: hr_utility.set_location('l_deficit_limit_c: '||l_deficit_limit,70);
4854: if abs(l_def_exc_amt) > l_deficit_limit then
4855: fnd_message.set_name('BEN', 'BEN_92621_DFCIT_EXC_COMP_PCT');
4856: fnd_message.raise_error;
4857: end if;
4859: end if;
4860: end if;
4861: end if;
4862:
4863: hr_utility.set_location(' auto_alct_excs_flag ' || l_pool_info.auto_alct_excs_flag , 998 );
4864: --if l_pool_info.auto_alct_excs_flag='Y' then
4865: -- to avoid bleeding of the output parameter l_dstrbtd_val it is initialised
4866: l_dstrbtd_val := 0;
4867:
4864: --if l_pool_info.auto_alct_excs_flag='Y' then
4865: -- to avoid bleeding of the output parameter l_dstrbtd_val it is initialised
4866: l_dstrbtd_val := 0;
4867:
4868: hr_utility.set_location(l_proc,80 );
4869: --
4870: -- get the dflt trtmt cd by executing the rule if necessary
4871: if l_pool_info.dflt_excs_trtmt_rl is not null then
4872: run_rule
4884:
4885: -- execute the appropriate sequence based on the dflt_excs_trtmt_cd
4886: --
4887: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
4888: hr_utility.set_location(l_proc,90 );
4889: distribute_credits
4890: (p_validate => p_validate
4891: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4892: ,p_bnft_prvdr_pool_id => l_ledger_totals.bnft_prvdr_pool_id
4913: ,p_dstrbtd_val => l_dstrbtd_val
4914: ,p_bpl_cash_recd_val => l_dummy_number
4915: ,p_bnft_prvdd_ldgr_id => l_dummy_number
4916: );
4917: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 90);
4918: end if;
4919:
4920: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_RLOVR_FRFT','RLOVR_DSTRBT_FRFT') then
4921: hr_utility.set_location(l_proc,100 );
4917: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 90);
4918: end if;
4919:
4920: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_RLOVR_FRFT','RLOVR_DSTRBT_FRFT') then
4921: hr_utility.set_location(l_proc,100 );
4922: if l_pool_info.auto_alct_excs_flag='Y' or l_default_enrt_flag = 'Y' then
4923: default_rollovers(
4924: p_bnft_prvdr_pool_id => l_ledger_totals.bnft_prvdr_pool_id,
4925: p_person_id => p_person_id,
4931: p_dflt_excs_trtmt_cd => l_pool_info.dflt_excs_trtmt_cd,
4932: p_rollover_val => l_rollover_val, -- returns the rollover change
4933: p_per_in_ler_id => p_per_in_ler_id,
4934: p_enrt_mthd_cd => p_enrt_mthd_cd);
4935: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 100);
4936: end if;
4937: end if ;
4938:
4939: if l_pool_info.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
4936: end if;
4937: end if ;
4938:
4939: if l_pool_info.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
4940: hr_utility.set_location(l_proc,90 );
4941: ------Bug 7118730,set this flag to recalculate the ledger,
4942: -----if used amount is more than provided amt.
4943: if l_ledger_totals.used_total > l_ledger_totals.prvdd_total
4944: and l_rollover_val = 0 then
4942: -----if used amount is more than provided amt.
4943: if l_ledger_totals.used_total > l_ledger_totals.prvdd_total
4944: and l_rollover_val = 0 then
4945: l_redis_credits := true;
4946: hr_utility.set_location('re-distribute the credits',90 );
4947: end if;
4948:
4949: ------Bug 7118730
4950:
4975: ,p_per_in_ler_id => p_per_in_ler_id
4976: ,p_bpl_cash_recd_val => l_dummy_number
4977: ,p_bnft_prvdd_ldgr_id => l_dummy_number
4978: );
4979: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 101);
4980: end if;
4981: --
4982: if l_acty_base_rt_id is not null and l_pool_info.dflt_excs_trtmt_cd is not null then
4983: -- Bug#4473573 - added net credit method condition
5002: p_person_id => p_person_id,
5003: p_enrt_mthd_cd => p_enrt_mthd_cd
5004: );
5005: end if;
5006: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
5007: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
5008: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
5009: --
5010: -- If some are forfeited subtract from credit total
5003: p_enrt_mthd_cd => p_enrt_mthd_cd
5004: );
5005: end if;
5006: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
5007: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
5008: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
5009: --
5010: -- If some are forfeited subtract from credit total
5011: --
5004: );
5005: end if;
5006: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
5007: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
5008: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
5009: --
5010: -- If some are forfeited subtract from credit total
5011: --
5012: l_total_credits:=l_total_credits-l_ledger_totals.frftd_total;
5010: -- If some are forfeited subtract from credit total
5011: --
5012: l_total_credits:=l_total_credits-l_ledger_totals.frftd_total;
5013: --
5014: hr_utility.set_location(l_proc,110 );
5015: --end if; -- if auto_alct_excs_flag='Y'
5016: --close c_pool_info;
5017: --
5018: if l_pool_info.uses_net_crs_mthd_flag = 'Y' then
5026: else
5027: l_def_exc_amount := l_def_exc_amount +
5028: (l_ledger_totals.prvdd_total - l_ledger_totals.used_total);
5029: end if;
5030: hr_utility.set_location('l_def_exc_amount: '||l_def_exc_amount,110 );
5031: end if;
5032: --
5033: -- Check if there is a total pool restriction.
5034: --
5043: end loop;
5044: --
5045: end loop;
5046: close c_ledger_totals;
5047: hr_utility.set_location(l_proc,120 );
5048:
5049: ------Bug 7118730,recalculate the ledger and distribute the credits.
5050: if l_redis_credits then
5051: open c_ledger_totals;
5051: open c_ledger_totals;
5052: --
5053: fetch c_ledger_totals into l_ledger_totals;
5054: if c_ledger_totals%found then
5055: hr_utility.set_location(l_proc, 30);
5056: hr_utility.set_location(l_proc, 55);
5057: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
5058: 'used_total = ' || l_ledger_totals.used_total ||
5059: 'frftd_total = ' || l_ledger_totals.frftd_total ||
5052: --
5053: fetch c_ledger_totals into l_ledger_totals;
5054: if c_ledger_totals%found then
5055: hr_utility.set_location(l_proc, 30);
5056: hr_utility.set_location(l_proc, 55);
5057: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
5058: 'used_total = ' || l_ledger_totals.used_total ||
5059: 'frftd_total = ' || l_ledger_totals.frftd_total ||
5060: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
5053: fetch c_ledger_totals into l_ledger_totals;
5054: if c_ledger_totals%found then
5055: hr_utility.set_location(l_proc, 30);
5056: hr_utility.set_location(l_proc, 55);
5057: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
5058: 'used_total = ' || l_ledger_totals.used_total ||
5059: 'frftd_total = ' || l_ledger_totals.frftd_total ||
5060: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
5061: if l_pool_info.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
5058: 'used_total = ' || l_ledger_totals.used_total ||
5059: 'frftd_total = ' || l_ledger_totals.frftd_total ||
5060: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
5061: if l_pool_info.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
5062: hr_utility.set_location(l_proc,90 );
5063: distribute_credits
5064: (p_validate => p_validate
5065: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
5066: ,p_bnft_prvdr_pool_id => l_ledger_totals.bnft_prvdr_pool_id
5087: ,p_per_in_ler_id => p_per_in_ler_id
5088: ,p_bpl_cash_recd_val => l_dummy_number
5089: ,p_bnft_prvdd_ldgr_id => l_dummy_number
5090: );
5091: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 101);
5092: end if;
5093: --
5094: if l_acty_base_rt_id is not null and l_pool_info.dflt_excs_trtmt_cd is not null then
5095: -- Bug#4473573 - added net credit method condition
5114: p_person_id => p_person_id,
5115: p_enrt_mthd_cd => p_enrt_mthd_cd
5116: );
5117: end if;
5118: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
5119: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
5120: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
5121: hr_utility.set_location( 'Re-distributed credits', 102);
5122: end if;
5115: p_enrt_mthd_cd => p_enrt_mthd_cd
5116: );
5117: end if;
5118: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
5119: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
5120: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
5121: hr_utility.set_location( 'Re-distributed credits', 102);
5122: end if;
5123: close c_ledger_totals;
5116: );
5117: end if;
5118: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
5119: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
5120: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
5121: hr_utility.set_location( 'Re-distributed credits', 102);
5122: end if;
5123: close c_ledger_totals;
5124: end if;
5117: end if;
5118: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
5119: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
5120: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
5121: hr_utility.set_location( 'Re-distributed credits', 102);
5122: end if;
5123: close c_ledger_totals;
5124: end if;
5125: ------Bug 7118730
5133: if l_uses_net_crs_mthd then
5134: --
5135: -- Write an enrollment result for the excess/deficit amount.
5136: --
5137: hr_utility.set_location('l_def_exc_amount'||l_def_exc_amount,110 );
5138: hr_utility.set_location('prvdd_total'||l_ledger_totals.prvdd_total,110 );
5139: hr_utility.set_location('used_total'||l_ledger_totals.used_total,110 );
5140: create_flex_credit_enrolment
5141: (p_person_id => p_person_id,
5134: --
5135: -- Write an enrollment result for the excess/deficit amount.
5136: --
5137: hr_utility.set_location('l_def_exc_amount'||l_def_exc_amount,110 );
5138: hr_utility.set_location('prvdd_total'||l_ledger_totals.prvdd_total,110 );
5139: hr_utility.set_location('used_total'||l_ledger_totals.used_total,110 );
5140: create_flex_credit_enrolment
5141: (p_person_id => p_person_id,
5142: p_enrt_mthd_cd => p_enrt_mthd_cd,
5135: -- Write an enrollment result for the excess/deficit amount.
5136: --
5137: hr_utility.set_location('l_def_exc_amount'||l_def_exc_amount,110 );
5138: hr_utility.set_location('prvdd_total'||l_ledger_totals.prvdd_total,110 );
5139: hr_utility.set_location('used_total'||l_ledger_totals.used_total,110 );
5140: create_flex_credit_enrolment
5141: (p_person_id => p_person_id,
5142: p_enrt_mthd_cd => p_enrt_mthd_cd,
5143: p_business_group_id => p_business_group_id,
5150: p_pgm_id => p_pgm_id
5151: );
5152: end if;
5153: --
5154: hr_utility.set_location('calling create_flex,total_credits='||to_char(l_total_credits),130);
5155: create_flex_credit_enrolment(
5156: p_person_id => p_person_id,
5157: p_enrt_mthd_cd => p_enrt_mthd_cd,
5158: p_business_group_id => p_business_group_id,
5162: p_per_in_ler_id => p_per_in_ler_id,
5163: p_rt_val => l_total_credits,
5164: p_pgm_id => p_pgm_id
5165: );
5166: hr_utility.set_location(l_proc, 140);
5167: open c_element_details;
5168: fetch c_element_details into
5169: p_acty_ref_perd_cd,
5170: p_acty_base_rt_id,
5174: ;
5175: close c_element_details;
5176: end if;
5177: --
5178: hr_utility.set_location('Leaving:'||l_proc, 999);
5179: --
5180: end total_pools;
5181: --
5182: --------------------------------------------------------------------------------
5292: l_proc varchar2(80) := g_package||'.update_rate';
5293: --
5294: begin
5295: --
5296: hr_utility.set_location('Entering '||l_proc,5);
5297: --
5298: open c_prv;
5299: fetch c_prv into l_prv_rec;
5300: close c_prv;
5328: fetch c_lee_rsn_for_program into l_lee_rsn_id ;
5329: close c_lee_rsn_for_program ;
5330: end if;
5331: --
5332: hr_utility.set_location(l_proc,10);
5333: --
5334: ben_determine_date.rate_and_coverage_dates(
5335: p_which_dates_cd => 'R',
5336: p_pgm_id => l_pen_rec.pgm_id,
5354: p_rt_end_dt_rl => l_rt_end_dt_rl,
5355: p_effective_date => p_effective_date
5356: );
5357: --
5358: hr_utility.set_location(l_proc,15);
5359: --
5360: if l_prv_rec.rt_strt_dt >= l_rt_strt_dt then
5361: --
5362: ben_prtt_rt_val_api.delete_prtt_rt_val(
5383: ,p_effective_date => p_effective_date
5384: );
5385: end if;
5386: --
5387: hr_utility.set_location(l_proc,20);
5388: --
5389: -- Bug#2441871
5390: if l_enrt_rt_id is not null then
5391: --
5422: );
5423: --
5424: End if;
5425: --
5426: hr_utility.set_location('Leaving '||l_proc,5);
5427: --
5428: end update_rate;
5429: --
5430: --------------------------------------------------------------------------------
5503: l_proc varchar2(80) := g_package||'.delete_all_ledgers';
5504: --
5505: begin
5506: --
5507: hr_utility.set_location('Enetering '||l_proc, 5);
5508: for l_bpl_rec in c_bpl loop
5509: --
5510: hr_utility.set_location(l_proc,10);
5511: if p_effective_date = l_bpl_rec.effective_start_date then
5506: --
5507: hr_utility.set_location('Enetering '||l_proc, 5);
5508: for l_bpl_rec in c_bpl loop
5509: --
5510: hr_utility.set_location(l_proc,10);
5511: if p_effective_date = l_bpl_rec.effective_start_date then
5512: --
5513: l_datetrack_mode := hr_api.g_zap;
5514: l_effective_date := p_effective_date;
5532: p_process_enrt_flag => 'N');
5533: --
5534: -- Check whether rollovers are attached.
5535: --
5536: hr_utility.set_location(l_proc,15);
5537: if l_bpl_rec.prtt_ro_of_unusd_amt_flag = 'Y' then
5538: --
5539: hr_utility.set_location(l_proc,20);
5540: open c_pen(l_bpl_rec.acty_base_rt_id);
5535: --
5536: hr_utility.set_location(l_proc,15);
5537: if l_bpl_rec.prtt_ro_of_unusd_amt_flag = 'Y' then
5538: --
5539: hr_utility.set_location(l_proc,20);
5540: open c_pen(l_bpl_rec.acty_base_rt_id);
5541: fetch c_pen into l_prtt_enrt_rslt_id,
5542: l_pen_object_version_number,
5543: l_pen_effective_start_date,
5589: end if;
5590: --
5591: end loop;
5592: --
5593: hr_utility.set_location('Leaving '||l_proc,5);
5594: --
5595: end delete_all_ledgers;
5596: --
5597: --------------------------------------------------------------------------------
5773: l_dummy varchar2(80);
5774: l_rt_strt_dt date;
5775: --
5776: begin
5777: hr_utility.set_location('Entering:'|| l_proc, 5);
5778: --
5779: -- Running total for the rollover.
5780: --
5781: l_rollover_diff_total:=0;
5824: if l_qualify_flag='Y' then
5825: --
5826: -- Process this rollover
5827: --
5828: hr_utility.set_location(l_proc, 20);
5829: l_acty_base_rt_id:=l_rollover.acty_base_rt_id;
5830: --
5831: -- Get the ledger totals with exclusions (see cursor)
5832: --
5840: --
5841: -- depending on the excess treatment code compute the balance
5842: --
5843: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
5844: hr_utility.set_location(l_proc, 30);
5845: l_balance:=l_prvdd_val-l_used_val-l_recd_val-l_rld_ovr_val;
5846: else
5847: hr_utility.set_location(l_proc, 40);
5848: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
5843: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
5844: hr_utility.set_location(l_proc, 30);
5845: l_balance:=l_prvdd_val-l_used_val-l_recd_val-l_rld_ovr_val;
5846: else
5847: hr_utility.set_location(l_proc, 40);
5848: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
5849: end if;
5850: hr_utility.set_location(l_proc, 50);
5851: --
5846: else
5847: hr_utility.set_location(l_proc, 40);
5848: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
5849: end if;
5850: hr_utility.set_location(l_proc, 50);
5851: --
5852: -- round the balance
5853: --
5854: if l_rollover.val_rndg_cd is not null then
5919: --
5920: if (l_balance < l_rollover.mn_rlovr_val or
5921: l_balance < l_mn_dstrbl_pct_num or
5922: l_balance <=0 ) then
5923: hr_utility.set_location('Balance less than minimum or zero',60);
5924: --
5925: -- nothing to do
5926: --
5927: null;
5926: --
5927: null;
5928: --
5929: else
5930: hr_utility.set_location(l_proc, 70);
5931: --
5932: open c_old_ledger;
5933: fetch c_old_ledger into
5934: l_bnft_prvdd_ldgr_id,
5938: ;
5939: --
5940: -- perform rollover
5941: --
5942: hr_utility.set_location(l_proc, 80);
5943: if c_old_ledger%notfound
5944: and not p_calculate_only_mode
5945: then
5946: hr_utility.set_location(l_proc, 90);
5942: hr_utility.set_location(l_proc, 80);
5943: if c_old_ledger%notfound
5944: and not p_calculate_only_mode
5945: then
5946: hr_utility.set_location(l_proc, 90);
5947: --
5948: -- Check the rollover plan is already enrolled.
5949: -- If not, no rollovers to be attached to this plan.
5950: --
5974: ,p_cash_recd_val => null
5975: ,p_effective_date => p_effective_date
5976: ,p_process_enrt_flag => 'N'
5977: );
5978: hr_utility.set_location('CREATED LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),100);
5979: --
5980: -- Update the rollover plan rate to reflect rollovers.
5981: --
5982: update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
5995: then
5996: --
5997: -- Update the ledger if the amount differs.
5998: --
5999: hr_utility.set_location(l_proc, 110);
6000: /*
6001: if l_effective_start_date=p_effective_date then
6002: l_datetrack_mode:=hr_api.g_correction;
6003: else
6009: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
6010: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
6011: p_base_key_value => l_bnft_prvdd_ldgr_id,
6012: p_mode => l_datetrack_mode);
6013: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),120);
6014: l_rollover_diff_total:=l_rollover_diff_total+l_balance-l_old_rlovr_val;
6015: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
6016: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
6017: ,p_effective_start_date => l_effective_start_date
6030: ,p_effective_date => p_effective_date
6031: ,p_datetrack_mode => l_datetrack_mode
6032: ,p_process_enrt_flag => 'N'
6033: );
6034: hr_utility.set_location('UPDATED LEDGER ID='||To_char(l_bnft_prvdd_ldgr_id),130);
6035: --
6036: open c_prv(-1);
6037: fetch c_prv into l_prtt_rt_val_id,
6038: l_rt_val, l_rt_strt_dt;
6050: end if;
6051: close c_old_ledger;
6052: end if;
6053: end if; -- rule returned 'Y'
6054: hr_utility.set_location(l_proc, 140);
6055: end loop;
6056: --
6057: p_rollover_val:=l_rollover_diff_total;
6058: hr_utility.set_location('Leaving:'||l_proc, 999);
6054: hr_utility.set_location(l_proc, 140);
6055: end loop;
6056: --
6057: p_rollover_val:=l_rollover_diff_total;
6058: hr_utility.set_location('Leaving:'||l_proc, 999);
6059: --
6060: end compute_rollovers;
6061: --
6062: --------------------------------------------------------------------------------
6157: l_dummy_number number;
6158: --
6159: begin
6160: --
6161: hr_utility.set_location('Entering '||l_proc,5);
6162: --
6163: -- Get all the totals for the pool.
6164: --
6165: p_frftd_val := 0;
6185: close c_pool;
6186: --
6187: -- If auto allocate flag is ON, then do the allocation.
6188: --
6189: hr_utility.set_location(l_proc,10);
6190: --
6191: if l_pool_rec.auto_alct_excs_flag = 'Y' then
6192: --
6193: g_credit_pool_result_id := p_flex_rslt_id;
6191: if l_pool_rec.auto_alct_excs_flag = 'Y' then
6192: --
6193: g_credit_pool_result_id := p_flex_rslt_id;
6194: --
6195: hr_utility.set_location(l_proc,15);
6196: -- get the dflt trtmt cd by executing the rule if necessary
6197: if l_pool_rec.dflt_excs_trtmt_rl is not null then
6198: run_rule
6199: (p_effective_date => p_effective_date
6209: if l_pool_rec.dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
6210: --
6211: -- Distribute the cash.
6212: --
6213: hr_utility.set_location(l_proc,20);
6214: --
6215: distribute_credits
6216: (p_calculate_only_mode => p_calculate_only_mode
6217: ,p_per_in_ler_id => p_per_in_ler_id
6248: 'RLOVR_DSTRBT_FRFT') then
6249: --
6250: -- Rollover the excess, if anything left.
6251: --
6252: hr_utility.set_location(l_proc,25);
6253: compute_rollovers
6254: (p_calculate_only_mode => p_calculate_only_mode
6255: ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
6256: ,p_person_id => p_person_id
6267: --
6268: end if;
6269: --
6270: if l_pool_rec.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
6271: hr_utility.set_location(l_proc,90 );
6272: --
6273: -- Distribute as cash, if anything still left.
6274: --
6275: hr_utility.set_location(l_proc,30);
6271: hr_utility.set_location(l_proc,90 );
6272: --
6273: -- Distribute as cash, if anything still left.
6274: --
6275: hr_utility.set_location(l_proc,30);
6276: distribute_credits
6277: (p_calculate_only_mode => p_calculate_only_mode
6278: ,p_per_in_ler_id => p_per_in_ler_id
6279: ,p_prtt_enrt_rslt_id => p_flex_rslt_id
6307: --
6308: -- All possible rollovers and cash distribution done. If anything is
6309: -- still left, it has to be forfeited.
6310: --
6311: hr_utility.set_location(l_proc,35);
6312: forfeit_credits
6313: (p_calculate_only_mode => p_calculate_only_mode
6314: ,p_prtt_enrt_rslt_id => p_flex_rslt_id
6315: ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
6342: end if; -- end net credit method.
6343: --
6344: p_def_exc_amount := l_def_exc_amount;
6345: --
6346: hr_utility.set_location('Leaving '||l_proc,999);
6347:
6348: end compute_excess;
6349: --
6350: --------------------------------------------------------------------------------
6478: l_proc varchar2(80) := g_package||'.update_net_credit_rate';
6479: --
6480: begin
6481: --
6482: hr_utility.set_location('Entering '||l_proc,5);
6483: open c_pen;
6484: fetch c_pen into l_pen_rec;
6485: close c_pen;
6486: --
6487: if p_prtt_rt_val_id is not null then
6488: --
6489: l_prtt_rt_val_id := p_prtt_rt_val_id;
6490: --
6491: hr_utility.set_location('update_net_credit amt '||p_def_exc_amount,5);
6492: update_rate
6493: (p_prtt_rt_val_id => l_prtt_rt_val_id
6494: ,p_val => p_def_exc_amount
6495: ,p_prtt_enrt_rslt_id => p_flex_rslt_id
6741: ,p_ordr_num => l_abr_rec.ordr_num
6742: );
6743: end if;
6744: --
6745: hr_utility.set_location('Leaving '||l_proc,999);
6746: --
6747: end update_net_credit_rate;
6748: --------------------------------------------------------------------------------
6749: -- recompute_flex_credits
6950: l_proc varchar2(80) := g_package||'.recompute_flex_credits';
6951: --
6952: begin
6953: --
6954: hr_utility.set_location('Entering '||l_proc,5);
6955: --
6956: if p_pgm_id is null or
6957: p_person_id is null or
6958: p_prtt_enrt_rslt_id is null then
6960: return;
6961: --
6962: end if;
6963: --
6964: hr_utility.set_location(l_proc, 10);
6965: --
6966: open c_pgm;
6967: fetch c_pgm into l_flex_pgm;
6968: --
7035: p_datetrack_mode => l_datetrack_mode,
7036: p_business_group_id => p_business_group_id,
7037: p_process_enrt_flag => 'N');
7038: --
7039: hr_utility.set_location(l_proc,15);
7040: --
7041: end if;
7042: --
7043: close c_dbt_bpl;
7041: end if;
7042: --
7043: close c_dbt_bpl;
7044: --
7045: hr_utility.set_location(l_proc,20);
7046: --
7047: -- Loop through all the pools for the program, to compute the total
7048: -- credits available after the de-enrollment.
7049: --
7056: if l_crdt_bpp_rec.uses_net_crs_mthd_flag = 'Y' then
7057: l_uses_net_crs_mthd := true;
7058: end if;
7059: --
7060: hr_utility.set_location(l_proc,25);
7061: --
7062: l_flex_rslt_id := l_crdt_bpp_rec.prtt_enrt_rslt_id;
7063: --
7064: l_epe_rec.pgm_id := l_crdt_bpp_rec.pgm_id;
7078: p_effective_date => p_effective_date);
7079: --
7080: if l_person_enrolled then
7081: --
7082: hr_utility.set_location(l_proc,30);
7083: --
7084: l_total_credits := l_total_credits + l_crdt_bpp_rec.prvdd_val;
7085: --
7086: if l_crdt_bpp_rec.bnft_prvdr_pool_id <> l_debit_pool_id then
7102: --
7103: -- Person is not eligible for the pool, so we need to delete
7104: -- all ledgers for the pool.
7105: --
7106: hr_utility.set_location(l_proc,35);
7107: --
7108: l_credit_ledger_deleted := true;
7109: --
7110: if l_crdt_bpp_rec.bnft_prvdr_pool_id = l_debit_pool_id then
7127: end if;
7128: --
7129: end loop;
7130: --
7131: hr_utility.set_location(l_proc,40);
7132: --
7133: if not(l_credit_ledger_deleted) and not(l_debit_ledger_deleted) then
7134: --
7135: -- No ledger deletion took place, it means everything is in order.
7133: if not(l_credit_ledger_deleted) and not(l_debit_ledger_deleted) then
7134: --
7135: -- No ledger deletion took place, it means everything is in order.
7136: --
7137: hr_utility.set_location(l_proc,45);
7138: return;
7139: --
7140: elsif l_recompute_excess then
7141: --
7141: --
7142: -- Re-compute the excess amounts as a debit ledger is deleted,
7143: -- so we have more excess amount available.
7144: --
7145: hr_utility.set_location(l_proc,50);
7146: compute_excess
7147: (p_bnft_prvdr_pool_id => l_debit_pool_id
7148: ,p_flex_rslt_id => l_flex_rslt_id
7149: ,p_effective_date => p_effective_date
7155: ,p_frftd_val => l_frftd_val
7156: ,p_def_exc_amount => l_def_exc_amount
7157: ,p_bpl_cash_recd_val => l_dummy_number
7158: );
7159: hr_utility.set_location('l_def_exc_amount:1 '||l_def_exc_amount ,50);
7160: --
7161: -- Reduce forfeited credits for the pool from which the
7162: -- debit ledger was deleted.
7163: --
7281: --
7282: -- Update the flex credit rate with the total credits,
7283: -- if the value has changed.
7284: --
7285: hr_utility.set_location ('Total Credits'||l_total_credits,111);
7286: hr_utility.set_location ('Rate Value '||l_rt_val,111);
7287: if c_prv%found and l_total_credits <> l_rt_val then
7288: --
7289: hr_utility.set_location(l_proc,55);
7282: -- Update the flex credit rate with the total credits,
7283: -- if the value has changed.
7284: --
7285: hr_utility.set_location ('Total Credits'||l_total_credits,111);
7286: hr_utility.set_location ('Rate Value '||l_rt_val,111);
7287: if c_prv%found and l_total_credits <> l_rt_val then
7288: --
7289: hr_utility.set_location(l_proc,55);
7290: update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
7285: hr_utility.set_location ('Total Credits'||l_total_credits,111);
7286: hr_utility.set_location ('Rate Value '||l_rt_val,111);
7287: if c_prv%found and l_total_credits <> l_rt_val then
7288: --
7289: hr_utility.set_location(l_proc,55);
7290: update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
7291: p_val => l_total_credits,
7292: p_prtt_enrt_rslt_id => l_flex_rslt_id,
7293: p_ended_per_in_ler_id => p_per_in_ler_id,
7297: end if;
7298:
7299: close c_prv;
7300:
7301: hr_utility.set_location('Leaving '||l_proc,999);
7302:
7303: end recompute_flex_credits;
7304: --
7305: --