[Home] [Help]
335: l_outputs ff_exec.outputs_t;
336: l_jurisdiction_code varchar2(30);
337:
338: begin
339: hr_utility.set_location('Entering:'|| l_proc, 10);
340: open c_pool_rule_info;
341: fetch c_pool_rule_info into l_pool_rule_info;
342: if c_pool_rule_info%NOTFOUND or c_pool_rule_info%NOTFOUND is null then
343: close c_pool_rule_info;
343: close c_pool_rule_info;
344: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
345: fnd_message.set_token('PROC',l_proc);
346: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
347: hr_utility.set_location(l_proc,82);
348: fnd_message.raise_error;
349: end if;
350: close c_pool_rule_info;
351:
355: close c_person_rule_info;
356: fnd_message.set_name('BEN','BEN_91708_PERSON_NOT_FOUND');
357: fnd_message.set_token('PROC',l_proc);
358: fnd_message.set_token('ID', p_person_id);
359: hr_utility.set_location(l_proc,82);
360: fnd_message.raise_error;
361: end if;
362: close c_person_rule_info;
363:
400: p_jurisdiction_code => l_jurisdiction_code);
401: p_dflt_excs_trtmt_cd := null;
402: p_mx_val := l_outputs(l_outputs.first).value;
403: end if;
404: hr_utility.set_location('Leaving:'|| l_proc, 999);
405:
406: end run_rule;
407: --------------------------------------------------------------------------------
408: -- This procedure returns the update mode appropriate
421: l_update_override boolean := FALSE;
422: l_update_change_insert boolean := FALSE;
423: --
424: begin
425: hr_utility.set_location(' Entering: Get_DT_Upd_Mode' , 10);
426: --
427: -- Get the appropriate update mode.
428: --
429: DT_Api.Find_DT_Upd_Modes(p_effective_date => p_effective_date,
442: elsif l_correction then
443: p_mode := 'CORRECTION';
444: end if;
445: --
446: hr_utility.set_location(' Leaving: Get_DT_Upd_Mode' , 10);
447: end;
448:
449: --------------------------------------------------------------------------------
450: -- accumulate_pools
525: l_element_type_id number;
526: --
527: begin
528: --
529: hr_utility.set_location('Entering:'|| l_proc, 10);
530: hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,12);
531: --
532: open c_choice;
533: loop
526: --
527: begin
528: --
529: hr_utility.set_location('Entering:'|| l_proc, 10);
530: hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,12);
531: --
532: open c_choice;
533: loop
534: --
559: --
560: end loop;
561: close c_choice;
562: --
563: hr_utility.set_location(' Leaving:'||l_proc, 999);
564: --
565: end accumulate_pools;
566: --------------------------------------------------------------------------------
567: -- accumulate_pools_for_choice
582: l_dummy_number number;
583: --
584: begin
585:
586: hr_utility.set_location('Entering:'|| l_proc, 10);
587: --
588: -- determine if the person is enrolled in the comp object or a component of it.
589: --
590: l_person_enrolled:=person_enrolled_in_choice(
596: -- If enrolled do the credit stuff
597: --
598: if (l_person_enrolled and
599: p_epe_rec.bnft_prvdr_pool_id is not null) then
600: hr_utility.set_location(l_proc, 20);
601: create_credit_ledger_entry
602: (p_person_id => p_person_id
603: ,p_epe_rec => p_epe_rec
604: ,p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
608: ,p_bpl_prvdd_val => l_dummy_number
609: );
610: end if;
611: --
612: hr_utility.set_location(' Leaving:'||l_proc, 999);
613: --
614: end accumulate_pools_for_choice;
615: --------------------------------------------------------------------------------
616: -- person_enrolled_in_choice
830: l_enrolled varchar2(1);
831:
832: begin
833:
834: hr_utility.set_location('Entering:'|| l_proc, 10);
835:
836: if p_epe_rec.cmbn_ptip_id is not null then
837: hr_utility.set_location(l_proc, 170);
838: open c_cmbn_ptip_enrolment;
833:
834: hr_utility.set_location('Entering:'|| l_proc, 10);
835:
836: if p_epe_rec.cmbn_ptip_id is not null then
837: hr_utility.set_location(l_proc, 170);
838: open c_cmbn_ptip_enrolment;
839: fetch c_cmbn_ptip_enrolment into l_enrolled;
840: if c_cmbn_ptip_enrolment%notfound then
841: close c_cmbn_ptip_enrolment;
838: open c_cmbn_ptip_enrolment;
839: fetch c_cmbn_ptip_enrolment into l_enrolled;
840: if c_cmbn_ptip_enrolment%notfound then
841: close c_cmbn_ptip_enrolment;
842: hr_utility.set_location(l_proc, 180);
843: return false;
844: else
845: close c_cmbn_ptip_enrolment;
846: hr_utility.set_location(l_proc, 190);
842: hr_utility.set_location(l_proc, 180);
843: return false;
844: else
845: close c_cmbn_ptip_enrolment;
846: hr_utility.set_location(l_proc, 190);
847: return true;
848: end if;
849:
850: elsif p_epe_rec.cmbn_plip_id is not null then
847: return true;
848: end if;
849:
850: elsif p_epe_rec.cmbn_plip_id is not null then
851: hr_utility.set_location(l_proc, 240);
852: open c_cmbn_plip_enrolment;
853: fetch c_cmbn_plip_enrolment into l_enrolled;
854: if c_cmbn_plip_enrolment%notfound then
855: close c_cmbn_plip_enrolment;
852: open c_cmbn_plip_enrolment;
853: fetch c_cmbn_plip_enrolment into l_enrolled;
854: if c_cmbn_plip_enrolment%notfound then
855: close c_cmbn_plip_enrolment;
856: hr_utility.set_location(l_proc, 250);
857: return false;
858: else
859: close c_cmbn_plip_enrolment;
860: hr_utility.set_location(l_proc, 260);
856: hr_utility.set_location(l_proc, 250);
857: return false;
858: else
859: close c_cmbn_plip_enrolment;
860: hr_utility.set_location(l_proc, 260);
861: return true;
862: end if;
863:
864: elsif p_epe_rec.cmbn_ptip_opt_id is not null then
861: return true;
862: end if;
863:
864: elsif p_epe_rec.cmbn_ptip_opt_id is not null then
865: hr_utility.set_location(l_proc, 200);
866: open c_cmbn_ptip_opt_enrolment;
867: fetch c_cmbn_ptip_opt_enrolment into l_enrolled;
868: if c_cmbn_ptip_opt_enrolment%notfound then
869: close c_cmbn_ptip_opt_enrolment;
866: open c_cmbn_ptip_opt_enrolment;
867: fetch c_cmbn_ptip_opt_enrolment into l_enrolled;
868: if c_cmbn_ptip_opt_enrolment%notfound then
869: close c_cmbn_ptip_opt_enrolment;
870: hr_utility.set_location(l_proc, 210);
871: return false;
872: else
873: close c_cmbn_ptip_opt_enrolment;
874: hr_utility.set_location(l_proc, 220);
870: hr_utility.set_location(l_proc, 210);
871: return false;
872: else
873: close c_cmbn_ptip_opt_enrolment;
874: hr_utility.set_location(l_proc, 220);
875: return true;
876: end if;
877: hr_utility.set_location(l_proc, 230);
878:
873: close c_cmbn_ptip_opt_enrolment;
874: hr_utility.set_location(l_proc, 220);
875: return true;
876: end if;
877: hr_utility.set_location(l_proc, 230);
878:
879: elsif p_epe_rec.oiplip_id is not null then
880: hr_utility.set_location(l_proc, 201);
881: open c_oiplip_enrolment;
876: end if;
877: hr_utility.set_location(l_proc, 230);
878:
879: elsif p_epe_rec.oiplip_id is not null then
880: hr_utility.set_location(l_proc, 201);
881: open c_oiplip_enrolment;
882: fetch c_oiplip_enrolment into l_enrolled;
883: if c_oiplip_enrolment%notfound then
884: close c_oiplip_enrolment;
881: open c_oiplip_enrolment;
882: fetch c_oiplip_enrolment into l_enrolled;
883: if c_oiplip_enrolment%notfound then
884: close c_oiplip_enrolment;
885: hr_utility.set_location(l_proc, 301);
886: return false;
887: else
888: close c_oiplip_enrolment;
889: hr_utility.set_location(l_proc, 401);
885: hr_utility.set_location(l_proc, 301);
886: return false;
887: else
888: close c_oiplip_enrolment;
889: hr_utility.set_location(l_proc, 401);
890: return true;
891: end if;
892: hr_utility.set_location(l_proc, 501);
893:
888: close c_oiplip_enrolment;
889: hr_utility.set_location(l_proc, 401);
890: return true;
891: end if;
892: hr_utility.set_location(l_proc, 501);
893:
894: elsif p_epe_rec.oipl_id is not null then
895: hr_utility.set_location(l_proc, 20);
896: open c_oipl_enrolment;
891: end if;
892: hr_utility.set_location(l_proc, 501);
893:
894: elsif p_epe_rec.oipl_id is not null then
895: hr_utility.set_location(l_proc, 20);
896: open c_oipl_enrolment;
897: fetch c_oipl_enrolment into l_enrolled;
898: if c_oipl_enrolment%notfound then
899: close c_oipl_enrolment;
896: open c_oipl_enrolment;
897: fetch c_oipl_enrolment into l_enrolled;
898: if c_oipl_enrolment%notfound then
899: close c_oipl_enrolment;
900: hr_utility.set_location(l_proc, 30);
901: return false;
902: else
903: close c_oipl_enrolment;
904: hr_utility.set_location(l_proc, 40);
900: hr_utility.set_location(l_proc, 30);
901: return false;
902: else
903: close c_oipl_enrolment;
904: hr_utility.set_location(l_proc, 40);
905: return true;
906: end if;
907: hr_utility.set_location(l_proc, 50);
908:
903: close c_oipl_enrolment;
904: hr_utility.set_location(l_proc, 40);
905: return true;
906: end if;
907: hr_utility.set_location(l_proc, 50);
908:
909: elsif p_epe_rec.pl_id is not null then
910: hr_utility.set_location(l_proc, 60);
911: open c_plan_enrolment;
906: end if;
907: hr_utility.set_location(l_proc, 50);
908:
909: elsif p_epe_rec.pl_id is not null then
910: hr_utility.set_location(l_proc, 60);
911: open c_plan_enrolment;
912: fetch c_plan_enrolment into l_enrolled;
913: if c_plan_enrolment%notfound then
914: close c_plan_enrolment;
911: open c_plan_enrolment;
912: fetch c_plan_enrolment into l_enrolled;
913: if c_plan_enrolment%notfound then
914: close c_plan_enrolment;
915: hr_utility.set_location(l_proc, 70);
916: return false;
917: else
918: close c_plan_enrolment;
919: return true;
917: else
918: close c_plan_enrolment;
919: return true;
920: end if;
921: hr_utility.set_location(l_proc, 80);
922:
923: elsif p_epe_rec.ptip_id is not null then
924: hr_utility.set_location(l_proc, 90);
925: open c_ptip_enrolment;
920: end if;
921: hr_utility.set_location(l_proc, 80);
922:
923: elsif p_epe_rec.ptip_id is not null then
924: hr_utility.set_location(l_proc, 90);
925: open c_ptip_enrolment;
926: fetch c_ptip_enrolment into l_enrolled;
927: if c_ptip_enrolment%notfound then
928: close c_ptip_enrolment;
925: open c_ptip_enrolment;
926: fetch c_ptip_enrolment into l_enrolled;
927: if c_ptip_enrolment%notfound then
928: close c_ptip_enrolment;
929: hr_utility.set_location(l_proc, 100);
930: return false;
931: else
932: close c_ptip_enrolment;
933: hr_utility.set_location(l_proc, 110);
929: hr_utility.set_location(l_proc, 100);
930: return false;
931: else
932: close c_ptip_enrolment;
933: hr_utility.set_location(l_proc, 110);
934: return true;
935: end if;
936: hr_utility.set_location(l_proc, 120);
937:
932: close c_ptip_enrolment;
933: hr_utility.set_location(l_proc, 110);
934: return true;
935: end if;
936: hr_utility.set_location(l_proc, 120);
937:
938: elsif p_epe_rec.pgm_id is not null then
939: hr_utility.set_location(l_proc, 130);
940: open c_pgm_enrolment;
935: end if;
936: hr_utility.set_location(l_proc, 120);
937:
938: elsif p_epe_rec.pgm_id is not null then
939: hr_utility.set_location(l_proc, 130);
940: open c_pgm_enrolment;
941: fetch c_pgm_enrolment into l_enrolled;
942: if c_pgm_enrolment%notfound then
943: close c_pgm_enrolment;
940: open c_pgm_enrolment;
941: fetch c_pgm_enrolment into l_enrolled;
942: if c_pgm_enrolment%notfound then
943: close c_pgm_enrolment;
944: hr_utility.set_location(l_proc, 140);
945: return false;
946: else
947: close c_pgm_enrolment;
948: hr_utility.set_location(l_proc, 150);
944: hr_utility.set_location(l_proc, 140);
945: return false;
946: else
947: close c_pgm_enrolment;
948: hr_utility.set_location(l_proc, 150);
949: return true;
950: end if;
951: hr_utility.set_location(l_proc, 160);
952:
947: close c_pgm_enrolment;
948: hr_utility.set_location(l_proc, 150);
949: return true;
950: end if;
951: hr_utility.set_location(l_proc, 160);
952:
953: else
954: hr_utility.set_location(l_proc, 270);
955: return false;
950: end if;
951: hr_utility.set_location(l_proc, 160);
952:
953: else
954: hr_utility.set_location(l_proc, 270);
955: return false;
956: end if;
957: --
958: hr_utility.set_location(' Leaving:'||l_proc, 999);
954: hr_utility.set_location(l_proc, 270);
955: return false;
956: end if;
957: --
958: hr_utility.set_location(' Leaving:'||l_proc, 999);
959: --
960: end person_enrolled_in_choice;
961: --------------------------------------------------------------------------------
962: -- create_credit_ledger_entry
989: --
990:
991: begin
992:
993: hr_utility.set_location('Entering:'|| l_proc, 10);
994: hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,13);
995: --
996: open c_bnft_pool;
997: fetch c_bnft_pool into l_pgm_id;
990:
991: begin
992:
993: hr_utility.set_location('Entering:'|| l_proc, 10);
994: hr_utility.set_location(' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,13);
995: --
996: open c_bnft_pool;
997: fetch c_bnft_pool into l_pgm_id;
998: close c_bnft_pool;
1015: --
1016: ,p_bpl_prvdd_val => l_dummy_number
1017: );
1018: --
1019: hr_utility.set_location(' Leaving:'||l_proc, 999);
1020: --
1021:
1022: end create_credit_ledger_entry;
1023: --------------------------------------------------------------------------------
1111: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1112: ;
1113:
1114: begin
1115: hr_utility.set_location('Entering:'|| l_proc, 15);
1116: --
1117: -- Get the flex credit result id and it's per-in-ler. Both are needed
1118: -- for when we create ledger rows.
1119: --
1131: ,p_pgm_id => p_epe_rec.pgm_id
1132: );
1133: --
1134: end if;
1135: hr_utility.set_location(l_proc, 40);
1136:
1137: hr_utility.set_location(l_proc, 50);
1138:
1139: hr_utility.set_location('starting create', 10);
1133: --
1134: end if;
1135: hr_utility.set_location(l_proc, 40);
1136:
1137: hr_utility.set_location(l_proc, 50);
1138:
1139: hr_utility.set_location('starting create', 10);
1140: hr_utility.set_location('business_group_id='||to_char(p_epe_rec.business_group_id), 10);
1141: open c_enrt_rt
1135: hr_utility.set_location(l_proc, 40);
1136:
1137: hr_utility.set_location(l_proc, 50);
1138:
1139: hr_utility.set_location('starting create', 10);
1140: hr_utility.set_location('business_group_id='||to_char(p_epe_rec.business_group_id), 10);
1141: open c_enrt_rt
1142: (c_epe_id => p_epe_rec.elig_per_elctbl_chc_id
1143: );
1136:
1137: hr_utility.set_location(l_proc, 50);
1138:
1139: hr_utility.set_location('starting create', 10);
1140: hr_utility.set_location('business_group_id='||to_char(p_epe_rec.business_group_id), 10);
1141: open c_enrt_rt
1142: (c_epe_id => p_epe_rec.elig_per_elctbl_chc_id
1143: );
1144: fetch c_enrt_rt into
1148: ;
1149: if c_enrt_rt%notfound
1150: then
1151: -- error
1152: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 51);
1153: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
1154: fnd_message.set_token('PROC',l_proc);
1155: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1156: hr_utility.set_location(' choice_id='''||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1152: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 51);
1153: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
1154: fnd_message.set_token('PROC',l_proc);
1155: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1156: hr_utility.set_location(' choice_id='''||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1157: hr_utility.set_location(' rslt_id='''||to_char(g_credit_pool_result_id),51);
1158: close c_enrt_rt;
1159: fnd_message.raise_error;
1160: elsif not p_calculate_only_mode
1153: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
1154: fnd_message.set_token('PROC',l_proc);
1155: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1156: hr_utility.set_location(' choice_id='''||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1157: hr_utility.set_location(' rslt_id='''||to_char(g_credit_pool_result_id),51);
1158: close c_enrt_rt;
1159: fnd_message.raise_error;
1160: elsif not p_calculate_only_mode
1161: then
1158: close c_enrt_rt;
1159: fnd_message.raise_error;
1160: elsif not p_calculate_only_mode
1161: then
1162: hr_utility.set_location(l_proc, 20);
1163: if l_acty_base_rt_id is null then
1164: hr_utility.set_location('BEN_91725_NO_FLX_CR_ABR_FOUND', 52);
1165: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
1166: fnd_message.set_token('PROC',l_proc);
1160: elsif not p_calculate_only_mode
1161: then
1162: hr_utility.set_location(l_proc, 20);
1163: if l_acty_base_rt_id is null then
1164: hr_utility.set_location('BEN_91725_NO_FLX_CR_ABR_FOUND', 52);
1165: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
1166: fnd_message.set_token('PROC',l_proc);
1167: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1168: hr_utility.set_location(' enrt_rt_id='''||to_char(l_enrt_rt_id),52);
1164: hr_utility.set_location('BEN_91725_NO_FLX_CR_ABR_FOUND', 52);
1165: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
1166: fnd_message.set_token('PROC',l_proc);
1167: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1168: hr_utility.set_location(' enrt_rt_id='''||to_char(l_enrt_rt_id),52);
1169: close c_enrt_rt;
1170: fnd_message.raise_error;
1171: end if;
1172:
1169: close c_enrt_rt;
1170: fnd_message.raise_error;
1171: end if;
1172:
1173: hr_utility.set_location(' choice_id='||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1174: hr_utility.set_location('looking for POOL p_epe_rec.bnft_prvdr_pool_id'||
1175: TO_CHAR(p_epe_rec.bnft_prvdr_pool_id),60);
1176: hr_utility.set_location(' l_acty_base_rt_id'||to_char(l_acty_base_rt_id),60);
1177: hr_utility.set_location(' g_credit_pool_result_id'||
1170: fnd_message.raise_error;
1171: end if;
1172:
1173: hr_utility.set_location(' choice_id='||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1174: hr_utility.set_location('looking for POOL p_epe_rec.bnft_prvdr_pool_id'||
1175: TO_CHAR(p_epe_rec.bnft_prvdr_pool_id),60);
1176: hr_utility.set_location(' l_acty_base_rt_id'||to_char(l_acty_base_rt_id),60);
1177: hr_utility.set_location(' g_credit_pool_result_id'||
1178: to_char(g_credit_pool_result_id), 60);
1172:
1173: hr_utility.set_location(' choice_id='||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1174: hr_utility.set_location('looking for POOL p_epe_rec.bnft_prvdr_pool_id'||
1175: TO_CHAR(p_epe_rec.bnft_prvdr_pool_id),60);
1176: hr_utility.set_location(' l_acty_base_rt_id'||to_char(l_acty_base_rt_id),60);
1177: hr_utility.set_location(' g_credit_pool_result_id'||
1178: to_char(g_credit_pool_result_id), 60);
1179:
1180: -- if a row is already there update it
1173: hr_utility.set_location(' choice_id='||to_char(p_epe_rec.elig_per_elctbl_chc_id),51);
1174: hr_utility.set_location('looking for POOL p_epe_rec.bnft_prvdr_pool_id'||
1175: TO_CHAR(p_epe_rec.bnft_prvdr_pool_id),60);
1176: hr_utility.set_location(' l_acty_base_rt_id'||to_char(l_acty_base_rt_id),60);
1177: hr_utility.set_location(' g_credit_pool_result_id'||
1178: to_char(g_credit_pool_result_id), 60);
1179:
1180: -- if a row is already there update it
1181: open c_old_ledger;
1184: l_prvdd_val,
1185: l_object_version_number,
1186: l_effective_start_date
1187: ;
1188: hr_utility.set_location(l_proc, 60);
1189: if c_old_ledger%notfound then
1190: hr_utility.set_location('val is'||to_char(l_val),12);
1191: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
1192: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
1186: l_effective_start_date
1187: ;
1188: hr_utility.set_location(l_proc, 60);
1189: if c_old_ledger%notfound then
1190: hr_utility.set_location('val is'||to_char(l_val),12);
1191: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
1192: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
1193: ,p_effective_start_date => l_effective_start_date
1194: ,p_effective_end_date => l_effective_end_date
1206: ,p_object_version_number => l_object_version_number
1207: ,p_cash_recd_val => null
1208: ,p_effective_date => p_effective_date
1209: );
1210: hr_utility.set_location('CREATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),60);
1211: -- Bug 2200139 Override if user changes the provided value from the
1212: -- Override Enrollment form don't reset it again to the enrt_rt value
1213: --
1214: elsif l_val<>l_prvdd_val and nvl(p_enrt_mthd_cd,'E') <> 'O' then
1224: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
1225: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
1226: p_base_key_value => l_bnft_prvdd_ldgr_id,
1227: p_mode => l_datetrack_mode);
1228: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),70);
1229: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
1230: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
1231: ,p_effective_start_date => l_effective_start_date
1232: ,p_effective_end_date => l_effective_end_date
1243: ,p_cash_recd_val => null
1244: ,p_effective_date => p_effective_date
1245: ,p_datetrack_mode => l_datetrack_mode
1246: );
1247: hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),80);
1248: end if;
1249: p_bnft_prvdd_ldgr_id:=l_bnft_prvdd_ldgr_id;
1250: close c_old_ledger;
1251: end if;
1252: close c_enrt_rt;
1253: --
1254: p_bpl_prvdd_val := l_val;
1255: --
1256: hr_utility.set_location(' Leaving:'||l_proc, 998);
1257:
1258: end create_credit_ledger_entry;
1259: --------------------------------------------------------------------------------
1260: -- create_debit_ledger_entry
1296: --
1297:
1298: begin
1299:
1300: hr_utility.set_location('Entering:'|| l_proc, 10);
1301: --
1302: open c_epe;
1303: fetch c_epe into l_pgm_id;
1304: close c_epe;
1321: ,p_pgm_id => l_pgm_id
1322: );
1323: --
1324: end if;
1325: hr_utility.set_location(l_proc, 40);
1326: --
1327: l_epe_rec.elig_per_elctbl_chc_id:= p_elig_per_elctbl_chc_id;
1328: l_epe_rec.prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
1329: l_epe_rec.business_group_id := p_business_group_id;
1346: ,p_effective_date => p_effective_date
1347: --
1348: ,p_bpl_used_val => p_bpl_used_val
1349: );
1350: hr_utility.set_location('Leaving:'|| l_proc, 999);
1351:
1352: end create_debit_ledger_entry;
1353: --------------------------------------------------------------------------------
1354: -- create_debit_ledger_entry
1408: where prtt_rt_val_id=p_enrt_rt_rec.prtt_rt_val_id;
1409:
1410: begin
1411:
1412: hr_utility.set_location('Entering:'|| l_proc, 15);
1413:
1414: -- see if a prtt_enrt_rt exists, is so use it's val
1415: if p_enrt_rt_rec.prtt_rt_val_id is null then
1416: l_val:=p_enrt_rt_rec.val;
1457: ,p_object_version_number => l_object_version_number
1458: ,p_cash_recd_val => null
1459: ,p_effective_date => p_effective_date
1460: );
1461: hr_utility.set_location('CREATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),41);
1462: else
1463: /*if l_effective_start_date=p_effective_date then
1464: l_datetrack_mode:=hr_api.g_correction;
1465: else
1475: -- or condition to take care of unrestricted life event
1476: if l_per_in_ler_id <> p_epe_rec.per_in_ler_id or
1477: l_used_val <> l_val then
1478:
1479: hr_utility.set_location('UPDATING LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),51);
1480:
1481: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
1482: p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
1483: ,p_effective_start_date => l_effective_start_date
1497: ,p_cash_recd_val => null
1498: ,p_effective_date => p_effective_date
1499: ,p_datetrack_mode => l_datetrack_mode
1500: );
1501: hr_utility.set_location('UPDATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),55);
1502: end if;
1503: end if;
1504: close c_old_ledger;
1505: end if;
1502: end if;
1503: end if;
1504: close c_old_ledger;
1505: end if;
1506: hr_utility.set_location(' Leaving:'||l_proc, 998);
1507: --
1508: -- Set OUT parameters
1509: --
1510: p_bpl_used_val := l_val;
1748: l_datetrack_mode varchar2(30);
1749: l_effective_date date;
1750: --
1751: begin
1752: hr_utility.set_location('Entering:'|| l_proc, 10);
1753: open c_ledger;
1754:
1755: loop
1756: fetch c_ledger into
1770: l_bnft_prvdd_ldgr_id,
1771: l_object_version_number,
1772: l_rt_usg_cd
1773: ;
1774: hr_utility.set_location(l_proc, 20);
1775:
1776: exit when c_ledger%notfound;
1777:
1778: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1774: hr_utility.set_location(l_proc, 20);
1775:
1776: exit when c_ledger%notfound;
1777:
1778: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1779: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1780: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1781: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1782: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1775:
1776: exit when c_ledger%notfound;
1777:
1778: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1779: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1780: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1781: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1782: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1783: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1776: exit when c_ledger%notfound;
1777:
1778: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1779: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1780: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1781: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1782: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1783: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1784: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1777:
1778: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1779: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1780: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1781: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1782: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1783: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1784: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1785:
1778: hr_utility.set_location('ldgr is '||l_bnft_prvdd_ldgr_id, 30);
1779: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1780: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1781: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1782: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1783: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1784: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1785:
1786:
1779: hr_utility.set_location('Pool is '||l_epe.bnft_prvdr_pool_id, 30);
1780: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1781: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1782: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1783: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1784: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1785:
1786:
1787: l_person_enrolled:=person_enrolled_in_choice(
1780: hr_utility.set_location('PGM is '||l_epe.pgm_id, 30);
1781: hr_utility.set_location('PLIP is '||l_epe.plip_id, 30);
1782: hr_utility.set_location('PL is '||l_epe.pl_id, 30);
1783: hr_utility.set_location('OIPL is '||l_epe.oipl_id, 30);
1784: hr_utility.set_location('PTIP is '||l_epe.ptip_id, 30);
1785:
1786:
1787: l_person_enrolled:=person_enrolled_in_choice(
1788: p_person_id => p_person_id,
1789: p_epe_rec => l_epe,
1790: p_old_result_id => p_prtt_enrt_rslt_id,
1791: p_effective_date => p_effective_date );
1792:
1793: hr_utility.set_location(l_proc, 40);
1794: --
1795: -- if the person is not enrolled or
1796: -- rt_usg_cd<>'FLXCR' and not directly enrolled
1797: -- then remove the ledger entry
1795: -- if the person is not enrolled or
1796: -- rt_usg_cd<>'FLXCR' and not directly enrolled
1797: -- then remove the ledger entry
1798: --
1799: hr_utility.set_location('Checking ledger='||to_char(l_bnft_prvdd_ldgr_id), 45);
1800: if (not l_person_enrolled) then
1801: open c_ldgr_exist(l_bnft_prvdd_ldgr_id,l_per_in_ler_id);
1802: fetch c_ldgr_exist into l_ldgr_exist;
1803: if c_ldgr_exist%notfound then
1804: close c_ldgr_exist;
1805: else
1806:
1807: --nvl(l_rt_usg_cd,hr_api.g_varchar2)<>'FLXCR') then
1808: hr_utility.set_location('Deleting ledger='||to_char(l_bnft_prvdd_ldgr_id), 50);
1809: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
1810: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
1811: p_effective_start_date => l_effective_start_date,
1812: p_effective_end_date => l_effective_end_date,
1817: );
1818: close c_ldgr_exist;
1819: end if;
1820: end if;
1821: hr_utility.set_location(l_proc, 70);
1822: end loop;
1823: --
1824: -- bug # 1708166
1825: --
1825: --
1826: open c_rslt;
1827: fetch c_rslt into l_rslt;
1828: close c_rslt;
1829: hr_utility.set_location('Thru dt ' ||l_rslt.enrt_cvg_thru_dt, 70);
1830: hr_utility.set_location('stat Cd '||l_rslt.prtt_enrt_rslt_stat_cd, 70);
1831: hr_utility.set_location('sspndd '||l_rslt.sspndd_flag, 70);
1832: if l_rslt.enrt_cvg_thru_dt <>hr_api.g_eot or
1833: l_rslt.prtt_enrt_rslt_stat_cd in ('VOIDD','BCKDT') or
1826: open c_rslt;
1827: fetch c_rslt into l_rslt;
1828: close c_rslt;
1829: hr_utility.set_location('Thru dt ' ||l_rslt.enrt_cvg_thru_dt, 70);
1830: hr_utility.set_location('stat Cd '||l_rslt.prtt_enrt_rslt_stat_cd, 70);
1831: hr_utility.set_location('sspndd '||l_rslt.sspndd_flag, 70);
1832: if l_rslt.enrt_cvg_thru_dt <>hr_api.g_eot or
1833: l_rslt.prtt_enrt_rslt_stat_cd in ('VOIDD','BCKDT') or
1834: nvl(l_rslt.sspndd_flag,'N') = 'Y' then -- Bug 5185351
1827: fetch c_rslt into l_rslt;
1828: close c_rslt;
1829: hr_utility.set_location('Thru dt ' ||l_rslt.enrt_cvg_thru_dt, 70);
1830: hr_utility.set_location('stat Cd '||l_rslt.prtt_enrt_rslt_stat_cd, 70);
1831: hr_utility.set_location('sspndd '||l_rslt.sspndd_flag, 70);
1832: if l_rslt.enrt_cvg_thru_dt <>hr_api.g_eot or
1833: l_rslt.prtt_enrt_rslt_stat_cd in ('VOIDD','BCKDT') or
1834: nvl(l_rslt.sspndd_flag,'N') = 'Y' then -- Bug 5185351
1835: l_purge := 'Y';
1850: fetch c_bnft_prvdd_ldgr into l_bnft_prvdd_ldgr_id,
1851: l_prtt_ro_of_unusd_amt_flag,
1852: l_object_version_number,
1853: l_bpl_esd;
1854: hr_utility.set_location('ldgr id is'||l_bnft_prvdd_ldgr_id, 51);
1855: if c_bnft_prvdd_ldgr%found then
1856: --
1857: -- Delete only if there are no current enrollment in
1858: -- this comp object for the person. For an FSA plan, when
1865: if l_prtt_ro_of_unusd_amt_flag = 'Y' then
1866: open c_person_enrolled;
1867: fetch c_person_enrolled into l_exists;
1868: if c_person_enrolled%found then
1869: hr_utility.set_location('person_enrolled ', 51);
1870: l_delete_bpl := false;
1871: else
1872: l_delete_bpl := true;
1873: end if;
1943: /* bug#3365290
1944: if c_enrt_rt%notfound
1945: then
1946: -- error
1947: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 51);
1948: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
1949: fnd_message.set_token('PROC',l_proc);
1950: close c_enrt_rt;
1951: fnd_message.raise_error;
1951: fnd_message.raise_error;
1952: end if;
1953: close c_enrt_rt;
1954: if l_enrt_rt.acty_base_rt_id is null then
1955: hr_utility.set_location('BEN_91725_NO_FLX_CR_ABR_FOUND', 52);
1956: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
1957: fnd_message.set_token('PROC',l_proc);
1958: fnd_message.raise_error;
1959: end if;
1963: --
1964:
1965: open c_bnft_prvdd_ldgr_2 (l_enrt_rt.acty_base_rt_id);
1966: fetch c_bnft_prvdd_ldgr_2 into l_bnft_prvdd_ldgr_id,l_object_version_number,l_bpl_esd;
1967: hr_utility.set_location('ldgr id provided is'||l_bnft_prvdd_ldgr_id, 52);
1968: if c_bnft_prvdd_ldgr_2%found then
1969: --
1970: -- Bug 5500864
1971: if p_effective_date = l_bpl_esd
1995: end if;
1996: close c_ledger;
1997: --
1998:
1999: hr_utility.set_location(' Leaving:'||l_proc, 999);
2000: --
2001: end cleanup_invalid_ledger_entries;
2002: --------------------------------------------------------------------------------
2003: -- cleanup_invalid_ledger_entries
2080: ;
2081:
2082: begin
2083:
2084: hr_utility.set_location('Entering:'|| l_proc, 15);
2085:
2086: open c_ledger;
2087:
2088: loop
2101: l_bnft_prvdd_ldgr_id,
2102: l_object_version_number,
2103: l_rt_usg_cd
2104: ;
2105: hr_utility.set_location(l_proc, 20);
2106:
2107: exit when c_ledger%notfound;
2108:
2109: hr_utility.set_location(l_proc, 30);
2105: hr_utility.set_location(l_proc, 20);
2106:
2107: exit when c_ledger%notfound;
2108:
2109: hr_utility.set_location(l_proc, 30);
2110:
2111: l_person_enrolled:=person_enrolled_in_choice(
2112: p_person_id => p_person_id,
2113: p_epe_rec => l_epe,
2112: p_person_id => p_person_id,
2113: p_epe_rec => l_epe,
2114: p_effective_date => p_effective_date
2115: );
2116: hr_utility.set_location(l_proc, 40);
2117: --
2118: -- if the person is not enrolled or
2119: -- rt_usg_cd<>'FLXCR' and not directly enrolled
2120: -- then remove the ledger entry
2121: --
2122: if ((not l_person_enrolled) or
2123: (nvl(l_rt_usg_cd,hr_api.g_varchar2)<>'FLXCR' and
2124: l_epe.prtt_enrt_rslt_id is null)) then
2125: hr_utility.set_location('Deleting ledger='||to_char(l_bnft_prvdd_ldgr_id), 50);
2126: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
2127: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
2128: p_effective_start_date => l_effective_start_date,
2129: p_effective_end_date => l_effective_end_date,
2131: p_effective_date => p_effective_date,
2132: p_datetrack_mode => hr_api.g_zap,
2133: p_business_group_id => p_business_group_id
2134: );
2135: hr_utility.set_location(l_proc, 60);
2136: end if;
2137: hr_utility.set_location(l_proc, 70);
2138: end loop;
2139: close c_ledger;
2133: p_business_group_id => p_business_group_id
2134: );
2135: hr_utility.set_location(l_proc, 60);
2136: end if;
2137: hr_utility.set_location(l_proc, 70);
2138: end loop;
2139: close c_ledger;
2140: --
2141: hr_utility.set_location(' Leaving:'||l_proc, 998);
2137: hr_utility.set_location(l_proc, 70);
2138: end loop;
2139: close c_ledger;
2140: --
2141: hr_utility.set_location(' Leaving:'||l_proc, 998);
2142: --
2143: end cleanup_invalid_ledger_entries;
2144: --------------------------------------------------------------------------------
2145: -- create_flex_credit_enrolment
2263: l_acty_typ_cd2 varchar2(300);
2264: --
2265: begin
2266:
2267: hr_utility.set_location('Entering:'|| l_proc, 10);
2268:
2269: -- 2988218 chk if its a flex pgm
2270: open c_pgm_type;
2271: fetch c_pgm_type into l_pgm_type;
2279: fetch c_flex_credit_choice into l_flex_credit_choice;
2280: --
2281: if (c_flex_credit_choice%notfound and
2282: nvl(p_rt_val,hr_api.g_number) <> hr_api.g_number ) then --Bug 3864152
2283: hr_utility.set_location('BEN_91726_NO_FLX_CR_CHOICE', 20);
2284: fnd_message.set_name('BEN','BEN_91726_NO_FLX_CR_CHOICE');
2285: fnd_message.set_token('PROC',l_proc);
2286: fnd_message.set_token('PERSON_ID', to_char(p_person_id));
2287: fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
2327: else
2328: l_acty_typ_cd := 'NCRUDED';
2329: l_acty_typ_cd2 := 'NCRDSTR';
2330: end if;
2331: hr_utility.set_location('l_acty_typ_cd:'|| l_acty_typ_cd, 10);
2332: --
2333: open c_net_credits_rate(l_acty_typ_cd);
2334: fetch c_net_credits_rate into l_prnt_prtt_rt_val_id
2335: ,l_prnt_enrt_rt_id
2334: fetch c_net_credits_rate into l_prnt_prtt_rt_val_id
2335: ,l_prnt_enrt_rt_id
2336: ,l_prnt_acty_base_rt_id;
2337: if c_net_credits_rate%notfound then
2338: hr_utility.set_location('not found:'|| l_acty_typ_cd, 10);
2339: close c_net_credits_rate;
2340: fnd_message.set_name('BEN','BEN_92622_NET_CRED_RT_NOT_FND');
2341: fnd_message.raise_error;
2342: else
2375: -- Get the calculated value for the child rate.
2376: --
2377: l_prnt_rt_val := abs(p_net_credit_val);
2378: --
2379: hr_utility.set_location('l_prnt_rt_val:'|| l_prnt_rt_val, 10);
2380: hr_utility.set_location('l_child_rt_val:'|| l_child_rt_val, 10);
2381: ben_determine_activity_base_rt.main
2382: (p_person_id => p_person_id
2383: ,p_elig_per_elctbl_chc_id => l_flex_credit_choice.elig_per_elctbl_chc_id
2376: --
2377: l_prnt_rt_val := abs(p_net_credit_val);
2378: --
2379: hr_utility.set_location('l_prnt_rt_val:'|| l_prnt_rt_val, 10);
2380: hr_utility.set_location('l_child_rt_val:'|| l_child_rt_val, 10);
2381: ben_determine_activity_base_rt.main
2382: (p_person_id => p_person_id
2383: ,p_elig_per_elctbl_chc_id => l_flex_credit_choice.elig_per_elctbl_chc_id
2384: ,p_acty_base_rt_id => l_child_acty_base_rt_id
2428: ,p_pp_in_yr_used_num => l_dummy_num
2429: ,p_ordr_num => l_dummy_num
2430: ,p_iss_val => l_dummy_num
2431: );
2432: hr_utility.set_location(l_proc, 70);
2433: hr_utility.set_location('l_child_rt_val:'|| l_child_rt_val, 10);
2434: end if;
2435: end if;
2436: --
2429: ,p_ordr_num => l_dummy_num
2430: ,p_iss_val => l_dummy_num
2431: );
2432: hr_utility.set_location(l_proc, 70);
2433: hr_utility.set_location('l_child_rt_val:'|| l_child_rt_val, 10);
2434: end if;
2435: end if;
2436: --
2437: -- call election_information api to create prtt enrt result
2446: (p_rt_val<>0 or
2447: (p_rt_val is null and
2448: p_net_credit_val is null))) then
2449: begin
2450: hr_utility.set_location(l_proc, 70);
2451: if l_flex_credit_choice.elig_per_elctbl_chc_id is not null then --Bug 2645624
2452: ben_election_information.election_information(
2453: p_elig_per_elctbl_chc_id => l_flex_credit_choice.elig_per_elctbl_chc_id,
2454: p_prtt_enrt_rslt_id => l_flex_credit_choice.prtt_enrt_rslt_id,
2484: end;
2485: elsif (l_flex_credit_choice.prtt_enrt_rslt_id is not null and
2486: (p_rt_val is not null
2487: or p_net_credit_val is not null)) then
2488: hr_utility.set_location(l_proc, 80);
2489: if p_rt_val is not null and l_flex_credit_choice.elig_per_elctbl_chc_id is not null then --Bug 2645624
2490: ben_election_information.election_information(
2491: p_elig_per_elctbl_chc_id => l_flex_credit_choice.elig_per_elctbl_chc_id,
2492: p_prtt_enrt_rslt_id => l_flex_credit_choice.prtt_enrt_rslt_id,
2568: -- store the created id in the global
2569: g_credit_pool_result_id:=l_flex_credit_choice.prtt_enrt_rslt_id;
2570: g_credit_pool_person_id:=p_person_id;
2571:
2572: hr_utility.set_location(' Leaving:'||l_proc, 999);
2573:
2574: end create_flex_credit_enrolment;
2575: --
2576: --------------------------------------------------------------------------------
2597: p_business_group_id=res.business_group_id ;
2598:
2599: begin
2600: --
2601: hr_utility.set_location('Entering:'|| l_proc, 10);
2602: --
2603: -- get the flex result from the ledger row(s)
2604: -- so don't have to do it first.
2605: --
2622: );
2623: --
2624: -- Don't update the flex credit row if necessary done in total_pools
2625: --
2626: hr_utility.set_location('Leaving:'||l_proc, 999);
2627: --
2628: end remove_bnft_prvdd_ldgr;
2629: --------------------------------------------------------------------------------
2630: -- forfeit_credits
2697: and pil.business_group_id=bpl.business_group_id
2698: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
2699: --
2700: begin
2701: hr_utility.set_location('Entering:'|| l_proc, 10);
2702: l_balance:=nvl(p_prvdd_val,0)
2703: +nvl(p_rlld_up_val,0)
2704: -nvl(p_used_val,0)
2705: -nvl(p_rollover_val,0)
2706: -nvl(p_cash_val,0);
2707: --
2708: l_balance_for_cr := l_balance ;
2709: --
2710: hr_utility.set_location('balance='||to_char(l_balance),15);
2711: open c_forfeit;
2712: fetch c_forfeit into
2713: l_bnft_prvdd_ldgr_id,
2714: l_frftd_val,
2715: l_object_version_number,
2716: l_effective_start_date;
2717: if c_forfeit%found then
2718: if l_balance<0 then
2719: hr_utility.set_location(l_proc, 16);
2720: l_balance:=0;
2721: end if;
2722: end if;
2723: --
2722: end if;
2723: --
2724: p_frftd_val:=l_balance;
2725: --
2726: hr_utility.set_location('frftd_val='||to_char(l_frftd_val),17);
2727: hr_utility.set_location('l_balance='||to_char(l_balance),17);
2728: --
2729: if l_frftd_val<>l_balance then
2730: hr_utility.set_location(l_proc, 18);
2723: --
2724: p_frftd_val:=l_balance;
2725: --
2726: hr_utility.set_location('frftd_val='||to_char(l_frftd_val),17);
2727: hr_utility.set_location('l_balance='||to_char(l_balance),17);
2728: --
2729: if l_frftd_val<>l_balance then
2730: hr_utility.set_location(l_proc, 18);
2731: --
2726: hr_utility.set_location('frftd_val='||to_char(l_frftd_val),17);
2727: hr_utility.set_location('l_balance='||to_char(l_balance),17);
2728: --
2729: if l_frftd_val<>l_balance then
2730: hr_utility.set_location(l_proc, 18);
2731: --
2732: -- update ledger row
2733: --
2734: /*
2743: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
2744: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
2745: p_base_key_value => l_bnft_prvdd_ldgr_id,
2746: p_mode => l_datetrack_mode);
2747: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),20);
2748: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
2749: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
2750: ,p_effective_start_date => l_effective_start_date
2751: ,p_effective_end_date => l_effective_end_date
2762: ,p_cash_recd_val => null
2763: ,p_effective_date => p_effective_date
2764: ,p_datetrack_mode => l_datetrack_mode
2765: );
2766: hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),30);
2767: else
2768: if l_balance>0 and l_frftd_val is null then
2769: --
2770: -- create a forfeit row
2768: if l_balance>0 and l_frftd_val is null then
2769: --
2770: -- create a forfeit row
2771: --
2772: hr_utility.set_location('result_id='||to_char(p_prtt_enrt_rslt_id),35);
2773: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
2774: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
2775: ,p_effective_start_date => l_effective_start_date
2776: ,p_effective_end_date => l_effective_end_date
2792: end if ;
2793: end if;
2794: --
2795: -- Bug 2645993
2796: hr_utility.set_location('CREATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),40);
2797: if l_balance_for_cr < 0 then
2798: --
2799: hr_utility.set_location('More Used than Provided case '||l_balance_for_cr ,30);
2800: open c_cash_rcvd;
2795: -- Bug 2645993
2796: hr_utility.set_location('CREATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),40);
2797: if l_balance_for_cr < 0 then
2798: --
2799: hr_utility.set_location('More Used than Provided case '||l_balance_for_cr ,30);
2800: open c_cash_rcvd;
2801: fetch c_cash_rcvd into
2802: l_bnft_prvdd_ldgr_id,
2803: l_cash_recd_val,
2804: l_object_version_number,
2805: l_effective_start_date;
2806: --
2807: -- When Used Value is more than the forefeited value
2808: hr_utility.set_location(' l_cash_recd_val '||l_cash_recd_val,31);
2809: if l_cash_recd_val is not null then
2810: --
2811: --if l_cash_recd_val > 0 then
2812: -- l_balance := l_cash_recd_val + l_balance ;
2817: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
2818: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
2819: p_base_key_value => l_bnft_prvdd_ldgr_id,
2820: p_mode => l_datetrack_mode);
2821: hr_utility.set_location('l_cash_recd_val is not null '||to_char(l_bnft_prvdd_ldgr_id),20);
2822: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
2823: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
2824: ,p_effective_start_date => l_effective_start_date
2825: ,p_effective_end_date => l_effective_end_date
2838: ,p_datetrack_mode => l_datetrack_mode
2839: );
2840: --
2841: else
2842: hr_utility.set_location('CR NULL ='||to_char(p_prtt_enrt_rslt_id),35);
2843: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
2844: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
2845: ,p_effective_start_date => l_effective_start_date
2846: ,p_effective_end_date => l_effective_end_date
2866: --
2867: end if;
2868: close c_forfeit;
2869: -- p_frftd_val:=l_balance;
2870: hr_utility.set_location('Leaving:'||l_proc||' p_frftd_val '||p_frftd_val , 999);
2871:
2872: end forfeit_credits;
2873: --------------------------------------------------------------------------------
2874: -- distribute_credits
2986: and p_effective_date between bpp.effective_start_date
2987: and bpp.effective_end_date;
2988:
2989: begin
2990: hr_utility.set_location('Entering:'|| l_proc, 10);
2991: hr_utility.set_location('p_prvdd_val:'||to_char(p_prvdd_val)||
2992: 'p_rlld_up_val:'||to_char(p_rlld_up_val),11);
2993: hr_utility.set_location('p_used_val:'||to_char(p_used_val),12);
2994: hr_utility.set_location('p_rollover_val:'||to_char(p_rollover_val),13);
2987: and bpp.effective_end_date;
2988:
2989: begin
2990: hr_utility.set_location('Entering:'|| l_proc, 10);
2991: hr_utility.set_location('p_prvdd_val:'||to_char(p_prvdd_val)||
2992: 'p_rlld_up_val:'||to_char(p_rlld_up_val),11);
2993: hr_utility.set_location('p_used_val:'||to_char(p_used_val),12);
2994: hr_utility.set_location('p_rollover_val:'||to_char(p_rollover_val),13);
2995: --
2989: begin
2990: hr_utility.set_location('Entering:'|| l_proc, 10);
2991: hr_utility.set_location('p_prvdd_val:'||to_char(p_prvdd_val)||
2992: 'p_rlld_up_val:'||to_char(p_rlld_up_val),11);
2993: hr_utility.set_location('p_used_val:'||to_char(p_used_val),12);
2994: hr_utility.set_location('p_rollover_val:'||to_char(p_rollover_val),13);
2995: --
2996: -- balance used depends on dflt_excs_trtmt_cd
2997: --
2990: hr_utility.set_location('Entering:'|| l_proc, 10);
2991: hr_utility.set_location('p_prvdd_val:'||to_char(p_prvdd_val)||
2992: 'p_rlld_up_val:'||to_char(p_rlld_up_val),11);
2993: hr_utility.set_location('p_used_val:'||to_char(p_used_val),12);
2994: hr_utility.set_location('p_rollover_val:'||to_char(p_rollover_val),13);
2995: --
2996: -- balance used depends on dflt_excs_trtmt_cd
2997: --
2998: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
2995: --
2996: -- balance used depends on dflt_excs_trtmt_cd
2997: --
2998: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
2999: hr_utility.set_location(l_proc,20 );
3000: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val;
3001: elsif p_dflt_excs_trtmt_cd = 'RLOVR_DSTRBT_FRFT' then
3002: hr_utility.set_location(l_proc,30 );
3003: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val-p_rollover_val;
2998: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
2999: hr_utility.set_location(l_proc,20 );
3000: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val;
3001: elsif p_dflt_excs_trtmt_cd = 'RLOVR_DSTRBT_FRFT' then
3002: hr_utility.set_location(l_proc,30 );
3003: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val-p_rollover_val;
3004: else
3005: hr_utility.set_location(l_proc,40 );
3006: fnd_message.set_name('BEN','BEN_DFLT_TRTMT_NOT_HNDLD');
3001: elsif p_dflt_excs_trtmt_cd = 'RLOVR_DSTRBT_FRFT' then
3002: hr_utility.set_location(l_proc,30 );
3003: l_balance:=p_prvdd_val+p_rlld_up_val-p_used_val-p_rollover_val;
3004: else
3005: hr_utility.set_location(l_proc,40 );
3006: fnd_message.set_name('BEN','BEN_DFLT_TRTMT_NOT_HNDLD');
3007: fnd_message.set_token('PROC',l_proc);
3008: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3009: fnd_message.set_token('DFLT_EXCS_TRTMT_CD',p_dflt_excs_trtmt_cd);
3008: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3009: fnd_message.set_token('DFLT_EXCS_TRTMT_CD',p_dflt_excs_trtmt_cd);
3010: fnd_message.raise_error;
3011: end if;
3012: hr_utility.set_location(l_proc,50 );
3013: --
3014: -- balance is rounded
3015: --
3016: l_acty_base_rt_id := p_acty_base_rt_id;
3014: -- balance is rounded
3015: --
3016: l_acty_base_rt_id := p_acty_base_rt_id;
3017: --
3018: hr_utility.set_location('['||p_val_rndg_cd||']',55);
3019: if p_val_rndg_cd is not null then
3020: l_balance:=benutils.do_rounding(
3021: p_rounding_cd => p_val_rndg_cd,
3022: p_rounding_rl => p_val_rndg_rl,
3022: p_rounding_rl => p_val_rndg_rl,
3023: p_value => l_balance,
3024: p_effective_date => p_effective_date);
3025: end if;
3026: hr_utility.set_location(l_proc,60 );
3027: --
3028: -- if the rounded balance is less than the minimum val or pct, then nothing to do
3029: --
3030: -- do min val first
3030: -- do min val first
3031: --
3032: if p_mn_dstrbl_val is not null then
3033: if p_mn_dstrbl_val > l_balance then
3034: hr_utility.set_location('Leaving with zero distributed:'||l_proc,97);
3035: p_dstrbtd_val:=0;
3036: return;
3037: end if;
3038: end if;
3035: p_dstrbtd_val:=0;
3036: return;
3037: end if;
3038: end if;
3039: hr_utility.set_location(l_proc,80 );
3040: --
3041: -- do min pct second
3042: --
3043: if p_mn_dstrbl_pct_num is not null then
3040: --
3041: -- do min pct second
3042: --
3043: if p_mn_dstrbl_pct_num is not null then
3044: hr_utility.set_location(l_proc,90 );
3045: l_mn_dstrbl_val:=(p_mn_dstrbl_pct_num/100)*(p_prvdd_val+p_rlld_up_val-p_used_val);
3046: if p_pct_rndg_cd is not null then
3047: l_mn_dstrbl_val:=benutils.do_rounding(
3048: p_rounding_cd => p_pct_rndg_cd,
3050: p_value => l_mn_dstrbl_val,
3051: p_effective_date => p_effective_date);
3052: end if;
3053: if l_mn_dstrbl_val > l_balance then
3054: hr_utility.set_location(l_proc,100 );
3055: p_dstrbtd_val:=0;
3056: return;
3057: end if;
3058: hr_utility.set_location(l_proc,110 );
3054: hr_utility.set_location(l_proc,100 );
3055: p_dstrbtd_val:=0;
3056: return;
3057: end if;
3058: hr_utility.set_location(l_proc,110 );
3059: end if;
3060: hr_utility.set_location(l_proc,120 );
3061: --
3062: -- if the rounded balance is more than the maximum val or pct, then reduce it to the maximum
3056: return;
3057: end if;
3058: hr_utility.set_location(l_proc,110 );
3059: end if;
3060: hr_utility.set_location(l_proc,120 );
3061: --
3062: -- if the rounded balance is more than the maximum val or pct, then reduce it to the maximum
3063: --
3064: l_cash_val:=l_balance;
3066: -- do max val next
3067: --
3068: if p_mx_dstrbl_val is not null then
3069: if p_mx_dstrbl_val < l_cash_val then
3070: hr_utility.set_location(l_proc,130 );
3071: l_cash_val:=p_mx_dstrbl_val;
3072: end if;
3073: end if;
3074: hr_utility.set_location(l_proc,140 );
3070: hr_utility.set_location(l_proc,130 );
3071: l_cash_val:=p_mx_dstrbl_val;
3072: end if;
3073: end if;
3074: hr_utility.set_location(l_proc,140 );
3075: --
3076: -- do max pct second
3077: --
3078: if p_mx_pct is not null then
3075: --
3076: -- do max pct second
3077: --
3078: if p_mx_pct is not null then
3079: hr_utility.set_location(l_proc,150 );
3080: l_mx_dstrbl_val:=(p_mx_pct/100)*(p_prvdd_val+p_rlld_up_val-p_used_val);
3081: if p_pct_rndg_cd is not null then
3082: l_mx_dstrbl_val:=benutils.do_rounding(
3083: p_rounding_cd => p_pct_rndg_cd,
3085: p_value => l_mx_dstrbl_val,
3086: p_effective_date => p_effective_date);
3087: end if;
3088: if l_mx_dstrbl_val < l_cash_val then
3089: hr_utility.set_location(l_proc,160 );
3090: l_cash_val:=l_mx_dstrbl_val;
3091: end if;
3092: hr_utility.set_location(l_proc,170 );
3093: end if;
3088: if l_mx_dstrbl_val < l_cash_val then
3089: hr_utility.set_location(l_proc,160 );
3090: l_cash_val:=l_mx_dstrbl_val;
3091: end if;
3092: hr_utility.set_location(l_proc,170 );
3093: end if;
3094: hr_utility.set_location(l_proc,180 );
3095: --
3096: -- create the cash ledger entry
3090: l_cash_val:=l_mx_dstrbl_val;
3091: end if;
3092: hr_utility.set_location(l_proc,170 );
3093: end if;
3094: hr_utility.set_location(l_proc,180 );
3095: --
3096: -- create the cash ledger entry
3097: --
3098: p_dstrbtd_val:=l_cash_val;
3099: --
3100: if l_cash_val<>p_cash_recd_total
3101: and not p_calculate_only_mode
3102: then
3103: hr_utility.set_location(l_proc,190 );
3104: hr_utility.set_location('ACE l_cash_val = ' || l_cash_val, 9999);
3105: hr_utility.set_location('ACE p_cash_recd_total = ' || p_cash_recd_total, 9999);
3106: --
3107: -- get cash abr
3100: if l_cash_val<>p_cash_recd_total
3101: and not p_calculate_only_mode
3102: then
3103: hr_utility.set_location(l_proc,190 );
3104: hr_utility.set_location('ACE l_cash_val = ' || l_cash_val, 9999);
3105: hr_utility.set_location('ACE p_cash_recd_total = ' || p_cash_recd_total, 9999);
3106: --
3107: -- get cash abr
3108: --
3101: and not p_calculate_only_mode
3102: then
3103: hr_utility.set_location(l_proc,190 );
3104: hr_utility.set_location('ACE l_cash_val = ' || l_cash_val, 9999);
3105: hr_utility.set_location('ACE p_cash_recd_total = ' || p_cash_recd_total, 9999);
3106: --
3107: -- get cash abr
3108: --
3109: open c_cash_abr;
3125: then
3126: close c_cash_abr_from_epe;
3127: -- Bug 5447507
3128: --
3129: hr_utility.set_location(l_proc,200 );
3130: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
3131: fnd_message.set_token('PROC',l_proc);
3132: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3133: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 220);
3129: hr_utility.set_location(l_proc,200 );
3130: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
3131: fnd_message.set_token('PROC',l_proc);
3132: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3133: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 220);
3134: hr_utility.set_location('pool_id='||to_char(p_bnft_prvdr_pool_id), 230);
3135: fnd_message.raise_error;
3136: --
3137: end if;
3130: fnd_message.set_name('BEN','BEN_91724_NO_FLX_CR_RT_FOUND');
3131: fnd_message.set_token('PROC',l_proc);
3132: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3133: hr_utility.set_location('BEN_91724_NO_FLX_CR_RT_FOUND', 220);
3134: hr_utility.set_location('pool_id='||to_char(p_bnft_prvdr_pool_id), 230);
3135: fnd_message.raise_error;
3136: --
3137: end if;
3138: --
3138: --
3139: close c_cash_abr_from_epe;
3140: --
3141: else
3142: hr_utility.set_location(l_proc,205 );
3143: close c_cash_abr;
3144: end if;
3145: hr_utility.set_location('ACE l_acty_base_rt_id = ' || l_acty_base_rt_id, 99);
3146: hr_utility.set_location(l_proc,210 );
3141: else
3142: hr_utility.set_location(l_proc,205 );
3143: close c_cash_abr;
3144: end if;
3145: hr_utility.set_location('ACE l_acty_base_rt_id = ' || l_acty_base_rt_id, 99);
3146: hr_utility.set_location(l_proc,210 );
3147: --
3148: hr_utility.set_location(l_proc, 220);
3149: --
3142: hr_utility.set_location(l_proc,205 );
3143: close c_cash_abr;
3144: end if;
3145: hr_utility.set_location('ACE l_acty_base_rt_id = ' || l_acty_base_rt_id, 99);
3146: hr_utility.set_location(l_proc,210 );
3147: --
3148: hr_utility.set_location(l_proc, 220);
3149: --
3150: if p_process_enrt_flag = 'Y'
3144: end if;
3145: hr_utility.set_location('ACE l_acty_base_rt_id = ' || l_acty_base_rt_id, 99);
3146: hr_utility.set_location(l_proc,210 );
3147: --
3148: hr_utility.set_location(l_proc, 220);
3149: --
3150: if p_process_enrt_flag = 'Y'
3151: and not p_calculate_only_mode
3152: then
3155: --
3156: if (g_credit_pool_result_id is null or
3157: g_credit_pool_person_id is null or
3158: g_credit_pool_person_id<>p_person_id) then
3159: hr_utility.set_location(l_proc, 250);
3160: --
3161: open c_bnft_pool;
3162: fetch c_bnft_pool into l_pgm_id;
3163: close c_bnft_pool;
3172: p_per_in_ler_id => p_per_in_ler_id,
3173: p_rt_val => null,
3174: p_pgm_id => l_pgm_id
3175: );
3176: hr_utility.set_location(l_proc, 260);
3177: end if;
3178: --
3179: end if;
3180: hr_utility.set_location(l_proc,270 );
3176: hr_utility.set_location(l_proc, 260);
3177: end if;
3178: --
3179: end if;
3180: hr_utility.set_location(l_proc,270 );
3181: --
3182: open c_old_ledger
3183: (c_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
3184: ,c_prtt_enrt_rslt_id => g_credit_pool_result_id
3190: l_effective_start_date;
3191: --
3192: -- insert cash row
3193: --
3194: hr_utility.set_location(l_proc, 280);
3195: if c_old_ledger%notfound
3196: and not p_calculate_only_mode
3197: then
3198: hr_utility.set_location('insert cash row', 290);
3194: hr_utility.set_location(l_proc, 280);
3195: if c_old_ledger%notfound
3196: and not p_calculate_only_mode
3197: then
3198: hr_utility.set_location('insert cash row', 290);
3199: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
3200: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
3201: ,p_effective_start_date => l_effective_start_date
3202: ,p_effective_end_date => l_effective_end_date
3214: ,p_object_version_number => l_object_version_number
3215: ,p_cash_recd_val => l_cash_val
3216: ,p_effective_date => p_effective_date
3217: );
3218: hr_utility.set_location('CREATED CASH LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),300);
3219: elsif l_old_cash_val<>l_cash_val
3220: and not p_calculate_only_mode
3221: then
3222: hr_utility.set_location(l_proc, 310);
3218: hr_utility.set_location('CREATED CASH LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),300);
3219: elsif l_old_cash_val<>l_cash_val
3220: and not p_calculate_only_mode
3221: then
3222: hr_utility.set_location(l_proc, 310);
3223: /* if l_effective_start_date=p_effective_date then
3224: hr_utility.set_location(l_proc,320 );
3225: l_datetrack_mode:=hr_api.g_correction;
3226: else
3220: and not p_calculate_only_mode
3221: then
3222: hr_utility.set_location(l_proc, 310);
3223: /* if l_effective_start_date=p_effective_date then
3224: hr_utility.set_location(l_proc,320 );
3225: l_datetrack_mode:=hr_api.g_correction;
3226: else
3227: hr_utility.set_location(l_proc,330 );
3228: l_datetrack_mode:=hr_api.g_update;
3223: /* if l_effective_start_date=p_effective_date then
3224: hr_utility.set_location(l_proc,320 );
3225: l_datetrack_mode:=hr_api.g_correction;
3226: else
3227: hr_utility.set_location(l_proc,330 );
3228: l_datetrack_mode:=hr_api.g_update;
3229: end if; */
3230: Get_DT_Upd_Mode
3231: (p_effective_date => p_effective_date,
3232: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
3233: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
3234: p_base_key_value => l_bnft_prvdd_ldgr_id,
3235: p_mode => l_datetrack_mode);
3236: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),340);
3237: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
3238: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
3239: ,p_effective_start_date => l_effective_start_date
3240: ,p_effective_end_date => l_effective_end_date
3251: ,p_cash_recd_val => l_cash_val
3252: ,p_effective_date => p_effective_date
3253: ,p_datetrack_mode => l_datetrack_mode
3254: );
3255: hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),350);
3256: end if;
3257: hr_utility.set_location(l_proc,360 );
3258: close c_old_ledger;
3259: --
3253: ,p_datetrack_mode => l_datetrack_mode
3254: );
3255: hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),350);
3256: end if;
3257: hr_utility.set_location(l_proc,360 );
3258: close c_old_ledger;
3259: --
3260: end if;
3261: hr_utility.set_location('Leaving:'||l_proc, 999);
3257: hr_utility.set_location(l_proc,360 );
3258: close c_old_ledger;
3259: --
3260: end if;
3261: hr_utility.set_location('Leaving:'||l_proc, 999);
3262: --
3263: -- Set out param
3264: --
3265: p_bpl_cash_recd_val := l_cash_val;
3555: pil_flex1.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
3556: pil_flex1.business_group_id=p_business_group_id and
3557: pil_flex.per_in_ler_id=p_per_in_ler_id ;
3558: begin
3559: hr_utility.set_location('Entering:'|| l_proc, 10);
3560: l_rollover_diff_total:=0;
3561: for l_rollover in c_rollovers loop
3562: --
3563: -- If rule exists run if and see if qualifies for rollover
3596: if l_qualify_flag='Y' then
3597: --
3598: -- Process this rollover
3599: --
3600: hr_utility.set_location(l_proc, 20);
3601: l_acty_base_rt_id:=l_rollover.acty_base_rt_id;
3602: --
3603: -- Get the ledger totals with exclusions (see cursor)
3604: --
3612: --
3613: -- depending on the excess treatment code compute the balance
3614: --
3615: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
3616: hr_utility.set_location(l_proc, 30);
3617: l_balance:=l_prvdd_val-l_used_val-l_recd_val-l_rld_ovr_val;
3618: else
3619: hr_utility.set_location(l_proc, 40);
3620: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
3615: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
3616: hr_utility.set_location(l_proc, 30);
3617: l_balance:=l_prvdd_val-l_used_val-l_recd_val-l_rld_ovr_val;
3618: else
3619: hr_utility.set_location(l_proc, 40);
3620: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
3621: end if;
3622: hr_utility.set_location(l_proc, 50);
3623: --
3618: else
3619: hr_utility.set_location(l_proc, 40);
3620: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
3621: end if;
3622: hr_utility.set_location(l_proc, 50);
3623: --
3624: -- round the balance
3625: --
3626: if l_rollover.val_rndg_cd is not null then
3741: l_balance < l_rollover.mn_rlovr_val or
3742: l_balance < l_mn_dstrbl_pct_num or
3743: l_balance <=0 )
3744: then
3745: hr_utility.set_location('Balance less than minimum or zero',60);
3746: hr_utility.set_location('l_balance : '||l_balance,60);
3747:
3748: --Bug 7118730
3749: open c_old_ledger;
3742: l_balance < l_mn_dstrbl_pct_num or
3743: l_balance <=0 )
3744: then
3745: hr_utility.set_location('Balance less than minimum or zero',60);
3746: hr_utility.set_location('l_balance : '||l_balance,60);
3747:
3748: --Bug 7118730
3749: open c_old_ledger;
3750: fetch c_old_ledger into
3785: --
3786: -- nothing to do
3787: --
3788: else
3789: hr_utility.set_location(l_proc, 70);
3790: --
3791: -- Find out amount of rollover
3792: --
3793: open c_old_ledger;
3799: ;
3800: --
3801: -- perform rollover
3802: --
3803: hr_utility.set_location(l_proc, 80);
3804: if c_old_ledger%notfound then
3805: hr_utility.set_location(l_proc, 90);
3806: l_rollover_diff_total:=l_rollover_diff_total+l_balance;
3807: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
3801: -- perform rollover
3802: --
3803: hr_utility.set_location(l_proc, 80);
3804: if c_old_ledger%notfound then
3805: hr_utility.set_location(l_proc, 90);
3806: l_rollover_diff_total:=l_rollover_diff_total+l_balance;
3807: ben_Benefit_Prvdd_Ledger_api.create_Benefit_Prvdd_Ledger (
3808: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
3809: ,p_effective_start_date => l_effective_start_date
3822: ,p_object_version_number => l_object_version_number
3823: ,p_cash_recd_val => null
3824: ,p_effective_date => p_effective_date
3825: );
3826: hr_utility.set_location('CREATED LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),100);
3827: elsif l_old_rlovr_val<>l_balance then
3828: hr_utility.set_location(l_proc, 110);
3829: /*
3830: if l_effective_start_date=p_effective_date then
3824: ,p_effective_date => p_effective_date
3825: );
3826: hr_utility.set_location('CREATED LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),100);
3827: elsif l_old_rlovr_val<>l_balance then
3828: hr_utility.set_location(l_proc, 110);
3829: /*
3830: if l_effective_start_date=p_effective_date then
3831: l_datetrack_mode:=hr_api.g_correction;
3832: else
3838: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
3839: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
3840: p_base_key_value => l_bnft_prvdd_ldgr_id,
3841: p_mode => l_datetrack_mode);
3842: hr_utility.set_location('UPDATING LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),120);
3843: l_rollover_diff_total:=l_rollover_diff_total+l_balance-l_old_rlovr_val;
3844: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
3845: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
3846: ,p_effective_start_date => l_effective_start_date
3858: ,p_cash_recd_val => 0
3859: ,p_effective_date => p_effective_date
3860: ,p_datetrack_mode => l_datetrack_mode
3861: );
3862: hr_utility.set_location('UPDATED LEDGER (ID='||to_char(l_bnft_prvdd_ldgr_id),130);
3863: end if;
3864: close c_old_ledger;
3865: end if;
3866: end if;
3864: close c_old_ledger;
3865: end if;
3866: end if;
3867: end if; -- rule returned 'Y'
3868: hr_utility.set_location(l_proc, 140);
3869: end loop;
3870: if l_rollover_diff_total is null then
3871: p_rollover_val := 0;
3872: else p_rollover_val:=l_rollover_diff_total;
3870: if l_rollover_diff_total is null then
3871: p_rollover_val := 0;
3872: else p_rollover_val:=l_rollover_diff_total;
3873: end if;
3874: hr_utility.set_location('We rolled over $'||to_char(l_rollover_diff_total), 998);
3875: hr_utility.set_location('Leaving:'||l_proc, 999);
3876: end default_rollovers;
3877: --
3878: -- Bug 2185478 Added procedure to validate the rollover value entered by the user on
3871: p_rollover_val := 0;
3872: else p_rollover_val:=l_rollover_diff_total;
3873: end if;
3874: hr_utility.set_location('We rolled over $'||to_char(l_rollover_diff_total), 998);
3875: hr_utility.set_location('Leaving:'||l_proc, 999);
3876: end default_rollovers;
3877: --
3878: -- Bug 2185478 Added procedure to validate the rollover value entered by the user on
3879: -- flex enrollment form and show proper message immediately
3913: l_rollover_val number;
3914: --
3915: begin
3916: --
3917: hr_utility.set_location('Entering:'||l_proc, 10);
3918: --
3919: -- Initialise globals.
3920: --
3921: g_balance := null;
3939: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
3940: fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
3941: fnd_message.set_token('BNFT_PRVDR_POOL_ID',
3942: to_char(p_bnft_prvdr_pool_id));
3943: hr_utility.set_location(l_proc,20);
3944: fnd_message.raise_error;
3945: end if;
3946: hr_utility.set_location(l_proc,30 );
3947: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_RLOVR_FRFT','RLOVR_DSTRBT_FRFT') then
3942: to_char(p_bnft_prvdr_pool_id));
3943: hr_utility.set_location(l_proc,20);
3944: fnd_message.raise_error;
3945: end if;
3946: hr_utility.set_location(l_proc,30 );
3947: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_RLOVR_FRFT','RLOVR_DSTRBT_FRFT') then
3948: --
3949: default_rollovers(
3950: p_calculate_only_mode => true,
3962: p_enrt_mthd_cd => p_enrt_mthd_cd);
3963: --
3964: end if;
3965: --
3966: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
3967: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
3968: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
3969: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3963: --
3964: end if;
3965: --
3966: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
3967: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
3968: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
3969: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3964: end if;
3965: --
3966: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
3967: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
3968: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
3969: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3972: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
3965: --
3966: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
3967: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
3968: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
3969: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3972: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
3973: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
3966: hr_utility.set_location( 'g_rollover_val = ' || g_balance, 30);
3967: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
3968: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
3969: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3972: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
3973: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
3974: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
3967: hr_utility.set_location( 'g_mx_dstrbl_pct_num = ' || g_mx_dstrbl_pct_num, 30);
3968: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
3969: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3972: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
3973: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
3974: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
3975: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
3968: hr_utility.set_location( 'g_mx_rlovr_val = ' || g_mx_rlovr_val, 30);
3969: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3972: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
3973: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
3974: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
3975: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
3976: --
3969: hr_utility.set_location( 'g_mx_elcn_val = ' || g_mx_elcn_val, 30);
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3972: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
3973: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
3974: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
3975: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
3976: --
3977: -- Raise errors if entered rollover value exceeds any limits set
3970: hr_utility.set_location( 'g_mn_dstrbl_pct_num = ' || g_mn_dstrbl_pct_num, 30);
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3972: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
3973: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
3974: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
3975: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
3976: --
3977: -- Raise errors if entered rollover value exceeds any limits set
3978: --
3971: hr_utility.set_location( 'g_mn_rlovr_val = ' || g_mn_rlovr_val, 30);
3972: hr_utility.set_location( 'g_mn_elcn_val = ' || g_mn_elcn_val, 30);
3973: hr_utility.set_location( 'g_mx_rlovr_rl_val = ' || g_mx_rlovr_rl_val, 30);
3974: hr_utility.set_location( 'l_rollover_val = ' || g_balance, 30);
3975: hr_utility.set_location( 'p_new_rollover_val = ' || p_new_rollover_val, 30);
3976: --
3977: -- Raise errors if entered rollover value exceeds any limits set
3978: --
3979: if p_new_rollover_val /* g_balance */ > g_mx_dstrbl_pct_num then
3978: --
3979: if p_new_rollover_val /* g_balance */ > g_mx_dstrbl_pct_num then
3980: --
3981: fnd_message.set_name('BEN','BEN_92960_RLOVR_VAL_GT_MXDPN');
3982: hr_utility.set_location(l_proc,40);
3983: fnd_message.raise_error;
3984: --
3985: end if;
3986: --
3986: --
3987: if p_new_rollover_val /* g_balance */ > g_mx_rlovr_val then
3988: --
3989: fnd_message.set_name('BEN','BEN_92961_RLOVR_VAL_GT_MXRV');
3990: hr_utility.set_location(l_proc,41);
3991: fnd_message.raise_error;
3992: --
3993: end if;
3994: if p_new_rollover_val /* g_balance */ > g_mx_elcn_val then
3993: end if;
3994: if p_new_rollover_val /* g_balance */ > g_mx_elcn_val then
3995: --
3996: fnd_message.set_name('BEN','BEN_92962_RLOVR_VAL_GT_MXEV');
3997: hr_utility.set_location(l_proc,42);
3998: fnd_message.raise_error;
3999: --
4000: end if;
4001: --
4001: --
4002: if p_new_rollover_val /* g_balance */ > g_mx_rlovr_rl_val then
4003: --
4004: fnd_message.set_name('BEN','BEN_92963_RLOVR_VAL_GT_MXRRV');
4005: hr_utility.set_location(l_proc,43);
4006: fnd_message.raise_error;
4007: --
4008: end if;
4009: --
4010: --
4011: if p_new_rollover_val /* g_balance */ < g_mn_dstrbl_pct_num then
4012: --
4013: fnd_message.set_name('BEN','BEN_92964_RLOVR_VAL_GT_MNDPN');
4014: hr_utility.set_location(l_proc,44);
4015: fnd_message.raise_error;
4016: --
4017: end if;
4018: --
4018: --
4019: if p_new_rollover_val /* g_balance */ < g_mn_rlovr_val then
4020: --
4021: fnd_message.set_name('BEN','BEN_92965_RLOVR_VAL_GT_MNRV');
4022: hr_utility.set_location(l_proc,45);
4023: fnd_message.raise_error;
4024: --
4025: end if;
4026: if p_new_rollover_val /* g_balance */ < g_mn_elcn_val then
4025: end if;
4026: if p_new_rollover_val /* g_balance */ < g_mn_elcn_val then
4027: --
4028: fnd_message.set_name('BEN','BEN_92966_RLOVR_VAL_GT_MNEV');
4029: hr_utility.set_location(l_proc,46);
4030: fnd_message.raise_error;
4031: --
4032: end if;
4033: --
4041: g_mn_dstrbl_pct_num := null;
4042: g_mn_rlovr_val := null;
4043: g_mn_elcn_val := null;
4044: --
4045: hr_utility.set_location('Leaving:'||l_proc, 40);
4046: --
4047: end validate_rollover_val;
4048: --------------------------------------------------------------------------------
4049: -- create_rollover_enrollment
4157: l_payroll_id per_all_assignments_f.payroll_id%type;
4158: l_organization_id per_all_assignments_f.organization_id%type;
4159: --END GEVITY
4160: begin
4161: hr_utility.set_location('Entering:'|| l_proc, 10);
4162: hr_utility.set_location('crdt_pool_rslt_id='||to_char(g_credit_pool_result_id),10);
4163: --
4164: -- Get choice id of what you are trying to roll into
4165: open c_rlovr_chc;
4158: l_organization_id per_all_assignments_f.organization_id%type;
4159: --END GEVITY
4160: begin
4161: hr_utility.set_location('Entering:'|| l_proc, 10);
4162: hr_utility.set_location('crdt_pool_rslt_id='||to_char(g_credit_pool_result_id),10);
4163: --
4164: -- Get choice id of what you are trying to roll into
4165: open c_rlovr_chc;
4166: fetch c_rlovr_chc into l_rlovr_chc;
4164: -- Get choice id of what you are trying to roll into
4165: open c_rlovr_chc;
4166: fetch c_rlovr_chc into l_rlovr_chc;
4167: if c_rlovr_chc%notfound then
4168: hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND ACTY_id:'||
4169: to_char(p_acty_base_rt_id), 20); hr_utility.set_location('p_person_id:'||to_char(p_person_id), 20);
4170: hr_utility.set_location('p_business_group_id:'||to_char(p_business_group_id), 20);
4171: close c_rlovr_chc;
4172: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4165: open c_rlovr_chc;
4166: fetch c_rlovr_chc into l_rlovr_chc;
4167: if c_rlovr_chc%notfound then
4168: hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND ACTY_id:'||
4169: to_char(p_acty_base_rt_id), 20); hr_utility.set_location('p_person_id:'||to_char(p_person_id), 20);
4170: hr_utility.set_location('p_business_group_id:'||to_char(p_business_group_id), 20);
4171: close c_rlovr_chc;
4172: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4173: fnd_message.set_token('ID', 'NA');
4166: fetch c_rlovr_chc into l_rlovr_chc;
4167: if c_rlovr_chc%notfound then
4168: hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND ACTY_id:'||
4169: to_char(p_acty_base_rt_id), 20); hr_utility.set_location('p_person_id:'||to_char(p_person_id), 20);
4170: hr_utility.set_location('p_business_group_id:'||to_char(p_business_group_id), 20);
4171: close c_rlovr_chc;
4172: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4173: fnd_message.set_token('ID', 'NA');
4174: fnd_message.set_token('PROC',l_proc);
4189: end if;
4190: --
4191: -- compute the new prtt_rt_val
4192: -- start bug 5608160
4193: hr_utility.set_location('Entering: SSARKAR ', 10);
4194:
4195: if nvl(l_rlovr_chc.entr_val_at_enrt_flag,'N') = 'N' then
4196: l_new_prtt_rt_val:=l_old_prtt_rt_val_amt+p_rlovr_amt;
4197: else
4199: end if ;
4200:
4201: -- end bug 5608160
4202:
4203: hr_utility.set_location('l_new_prtt_rt_val='||l_new_prtt_rt_val,10);
4204: hr_utility.set_location('l_old_prtt_rt_val_amt='||l_old_prtt_rt_val_amt,10);
4205: hr_utility.set_location('p_old_rlovr_amt='||p_old_rlovr_amt,10);
4206: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4207: --
4200:
4201: -- end bug 5608160
4202:
4203: hr_utility.set_location('l_new_prtt_rt_val='||l_new_prtt_rt_val,10);
4204: hr_utility.set_location('l_old_prtt_rt_val_amt='||l_old_prtt_rt_val_amt,10);
4205: hr_utility.set_location('p_old_rlovr_amt='||p_old_rlovr_amt,10);
4206: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4207: --
4208: -- create the result
4201: -- end bug 5608160
4202:
4203: hr_utility.set_location('l_new_prtt_rt_val='||l_new_prtt_rt_val,10);
4204: hr_utility.set_location('l_old_prtt_rt_val_amt='||l_old_prtt_rt_val_amt,10);
4205: hr_utility.set_location('p_old_rlovr_amt='||p_old_rlovr_amt,10);
4206: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4207: --
4208: -- create the result
4209: --
4202:
4203: hr_utility.set_location('l_new_prtt_rt_val='||l_new_prtt_rt_val,10);
4204: hr_utility.set_location('l_old_prtt_rt_val_amt='||l_old_prtt_rt_val_amt,10);
4205: hr_utility.set_location('p_old_rlovr_amt='||p_old_rlovr_amt,10);
4206: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4207: --
4208: -- create the result
4209: --
4210: hr_utility.set_location('enrt_rt_id='||to_char(l_rlovr_chc.enrt_rt_id),40);
4206: hr_utility.set_location('p_rlovr_amt='||p_rlovr_amt,10);
4207: --
4208: -- create the result
4209: --
4210: hr_utility.set_location('enrt_rt_id='||to_char(l_rlovr_chc.enrt_rt_id),40);
4211: hr_utility.set_location('choice_id='||to_char(l_rlovr_chc.elig_per_elctbl_chc_id),50);
4212: hr_utility.set_location('result_id='||to_char(l_rlovr_chc.prtt_enrt_rslt_id),60);
4213: --
4214: -- Calculate the annual value.
4207: --
4208: -- create the result
4209: --
4210: hr_utility.set_location('enrt_rt_id='||to_char(l_rlovr_chc.enrt_rt_id),40);
4211: hr_utility.set_location('choice_id='||to_char(l_rlovr_chc.elig_per_elctbl_chc_id),50);
4212: hr_utility.set_location('result_id='||to_char(l_rlovr_chc.prtt_enrt_rslt_id),60);
4213: --
4214: -- Calculate the annual value.
4215: --
4208: -- create the result
4209: --
4210: hr_utility.set_location('enrt_rt_id='||to_char(l_rlovr_chc.enrt_rt_id),40);
4211: hr_utility.set_location('choice_id='||to_char(l_rlovr_chc.elig_per_elctbl_chc_id),50);
4212: hr_utility.set_location('result_id='||to_char(l_rlovr_chc.prtt_enrt_rslt_id),60);
4213: --
4214: -- Calculate the annual value.
4215: --
4216: if l_new_prtt_rt_val > 0 and l_rlovr_chc.enrt_rt_id is not null then
4279: else
4280: l_datetrack_mode:=hr_api.g_insert;
4281: end if;
4282: --
4283: hr_utility.set_location(l_proc, 70);
4284: ben_election_information.election_information(
4285: p_elig_per_elctbl_chc_id => l_rlovr_chc.elig_per_elctbl_chc_id,
4286: p_prtt_enrt_rslt_id => l_rlovr_chc.prtt_enrt_rslt_id,
4287: p_effective_date => p_effective_date,
4311: p_dpnt_actn_warning => l_dpnt_actn_warning,
4312: p_bnf_actn_warning => l_bnf_actn_warning,
4313: p_ctfn_actn_warning => l_ctfn_actn_warning
4314: );
4315: hr_utility.set_location('Leaving:'||l_proc, 999);
4316: end create_rollover_enrollment;
4317: --------------------------------------------------------------------------------
4318: -- total_pools
4319: --------------------------------------------------------------------------------
4511: l_default_enrt_flag varchar2(1) := 'N' ;
4512: l_dummy_var varchar2(1) ;
4513: begin
4514:
4515: hr_utility.set_location('Entering:'|| l_proc, 10);
4516:
4517: -- Get the flex credit result id and it's per-in-ler. Both are needed
4518: -- for when we create ledger rows.
4519: create_flex_credit_enrolment(
4542: end if ;
4543: close c_get_mthd ;
4544:
4545: open c_ledger_totals;
4546: hr_utility.set_location(l_proc, 20);
4547:
4548: loop
4549: hr_utility.set_location(l_proc, 25);
4550: --
4545: open c_ledger_totals;
4546: hr_utility.set_location(l_proc, 20);
4547:
4548: loop
4549: hr_utility.set_location(l_proc, 25);
4550: --
4551: fetch c_ledger_totals into l_ledger_totals;
4552: hr_utility.set_location(l_proc, 30);
4553: --
4548: loop
4549: hr_utility.set_location(l_proc, 25);
4550: --
4551: fetch c_ledger_totals into l_ledger_totals;
4552: hr_utility.set_location(l_proc, 30);
4553: --
4554: exit when c_ledger_totals%notfound;
4555: hr_utility.set_location(l_proc, 55);
4556: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4551: fetch c_ledger_totals into l_ledger_totals;
4552: hr_utility.set_location(l_proc, 30);
4553: --
4554: exit when c_ledger_totals%notfound;
4555: hr_utility.set_location(l_proc, 55);
4556: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4557: 'used_total = ' || l_ledger_totals.used_total ||
4558: 'frftd_total = ' || l_ledger_totals.frftd_total ||
4559: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
4552: hr_utility.set_location(l_proc, 30);
4553: --
4554: exit when c_ledger_totals%notfound;
4555: hr_utility.set_location(l_proc, 55);
4556: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4557: 'used_total = ' || l_ledger_totals.used_total ||
4558: 'frftd_total = ' || l_ledger_totals.frftd_total ||
4559: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
4560: --
4560: --
4561: -- Total up credits provided
4562: --
4563: l_total_credits:=l_total_credits+l_ledger_totals.prvdd_total;
4564: hr_utility.set_location( 'l_total_credits = ' || l_total_credits, 55);
4565: --
4566: -- get the generic ABR to use for cash and forfeitures.
4567: --
4568: l_acty_base_rt_id := null;
4582: fetch c_get_pool_abr into l_acty_base_rt_id;
4583: if c_get_pool_abr%notfound then
4584: close c_cash_abr;
4585: close c_get_pool_abr;
4586: hr_utility.set_location(l_proc,15);
4587: fnd_message.set_name('BEN','BEN_91725_NO_FLX_CR_ABR_FOUND');
4588: fnd_message.set_token('PROC',l_proc);
4589: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4590: fnd_message.raise_error;
4605: null;
4606: else
4607: --
4608: open c_pool_info;
4609: hr_utility.set_location(l_proc, 60);
4610: fetch c_pool_info into l_pool_info;
4611: if c_pool_info%notfound then
4612: --
4613: -- error
4618: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4619: fnd_message.set_token('ACTY_BASE_RT_ID',to_char(l_acty_base_rt_id));
4620: fnd_message.set_token('BNFT_PRVDR_POOL_ID',
4621: to_char(l_ledger_totals.bnft_prvdr_pool_id));
4622: hr_utility.set_location(l_proc,70);
4623: fnd_message.raise_error;
4624: end if;
4625: close c_pool_info;
4626: --
4634: -- Check if there is a deficit amount.
4635: --
4636: l_def_exc_amt :=
4637: l_ledger_totals.prvdd_total - l_ledger_totals.used_total;
4638: hr_utility.set_location('l_def_exc_amt: '||l_def_exc_amt,70);
4639: --
4640: if l_def_exc_amt <> 0 then
4641: if l_def_exc_amt < 0 then
4642: --
4646: --
4647: if l_pool_info.mx_dfcit_pct_pool_crs_num is not null then
4648: l_deficit_limit :=
4649: l_pool_info.mx_dfcit_pct_pool_crs_num/100 * l_ledger_totals.prvdd_total;
4650: hr_utility.set_location('l_deficit_limit: '||l_deficit_limit,70);
4651: if abs(l_def_exc_amt) > l_deficit_limit then
4652: fnd_message.set_name('BEN', 'BEN_92620_EXCEED_TOT_DFCIT_PCT');
4653: fnd_message.raise_error;
4654: end if;
4672: );
4673: --
4674: l_deficit_limit :=
4675: l_pool_info.mx_dfcit_pct_comp_num/100 * l_compensation_value;
4676: hr_utility.set_location('l_deficit_limit_c: '||l_deficit_limit,70);
4677: if abs(l_def_exc_amt) > l_deficit_limit then
4678: fnd_message.set_name('BEN', 'BEN_92621_DFCIT_EXC_COMP_PCT');
4679: fnd_message.raise_error;
4680: end if;
4682: end if;
4683: end if;
4684: end if;
4685:
4686: hr_utility.set_location(' auto_alct_excs_flag ' || l_pool_info.auto_alct_excs_flag , 998 );
4687: --if l_pool_info.auto_alct_excs_flag='Y' then
4688: -- to avoid bleeding of the output parameter l_dstrbtd_val it is initialised
4689: l_dstrbtd_val := 0;
4690:
4687: --if l_pool_info.auto_alct_excs_flag='Y' then
4688: -- to avoid bleeding of the output parameter l_dstrbtd_val it is initialised
4689: l_dstrbtd_val := 0;
4690:
4691: hr_utility.set_location(l_proc,80 );
4692: --
4693: -- get the dflt trtmt cd by executing the rule if necessary
4694: if l_pool_info.dflt_excs_trtmt_rl is not null then
4695: run_rule
4707:
4708: -- execute the appropriate sequence based on the dflt_excs_trtmt_cd
4709: --
4710: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
4711: hr_utility.set_location(l_proc,90 );
4712: distribute_credits
4713: (p_validate => p_validate
4714: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4715: ,p_bnft_prvdr_pool_id => l_ledger_totals.bnft_prvdr_pool_id
4736: ,p_dstrbtd_val => l_dstrbtd_val
4737: ,p_bpl_cash_recd_val => l_dummy_number
4738: ,p_bnft_prvdd_ldgr_id => l_dummy_number
4739: );
4740: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 90);
4741: end if;
4742:
4743: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_RLOVR_FRFT','RLOVR_DSTRBT_FRFT') then
4744: hr_utility.set_location(l_proc,100 );
4740: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 90);
4741: end if;
4742:
4743: if l_pool_info.dflt_excs_trtmt_cd in ('DSTRBT_RLOVR_FRFT','RLOVR_DSTRBT_FRFT') then
4744: hr_utility.set_location(l_proc,100 );
4745: if l_pool_info.auto_alct_excs_flag='Y' or l_default_enrt_flag = 'Y' then
4746: default_rollovers(
4747: p_bnft_prvdr_pool_id => l_ledger_totals.bnft_prvdr_pool_id,
4748: p_person_id => p_person_id,
4754: p_dflt_excs_trtmt_cd => l_pool_info.dflt_excs_trtmt_cd,
4755: p_rollover_val => l_rollover_val, -- returns the rollover change
4756: p_per_in_ler_id => p_per_in_ler_id,
4757: p_enrt_mthd_cd => p_enrt_mthd_cd);
4758: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 100);
4759: end if;
4760: end if ;
4761:
4762: if l_pool_info.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
4759: end if;
4760: end if ;
4761:
4762: if l_pool_info.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
4763: hr_utility.set_location(l_proc,90 );
4764: ------Bug 7118730,set this flag to recalculate the ledger,
4765: -----if used amount is more than provided amt.
4766: if l_ledger_totals.used_total > l_ledger_totals.prvdd_total
4767: and l_rollover_val = 0 then
4765: -----if used amount is more than provided amt.
4766: if l_ledger_totals.used_total > l_ledger_totals.prvdd_total
4767: and l_rollover_val = 0 then
4768: l_redis_credits := true;
4769: hr_utility.set_location('re-distribute the credits',90 );
4770: end if;
4771:
4772: ------Bug 7118730
4773:
4798: ,p_per_in_ler_id => p_per_in_ler_id
4799: ,p_bpl_cash_recd_val => l_dummy_number
4800: ,p_bnft_prvdd_ldgr_id => l_dummy_number
4801: );
4802: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 101);
4803: end if;
4804: --
4805: if l_acty_base_rt_id is not null and l_pool_info.dflt_excs_trtmt_cd is not null then
4806: -- Bug#4473573 - added net credit method condition
4825: p_person_id => p_person_id,
4826: p_enrt_mthd_cd => p_enrt_mthd_cd
4827: );
4828: end if;
4829: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
4830: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
4831: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
4832: --
4833: -- If some are forfeited subtract from credit total
4826: p_enrt_mthd_cd => p_enrt_mthd_cd
4827: );
4828: end if;
4829: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
4830: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
4831: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
4832: --
4833: -- If some are forfeited subtract from credit total
4834: --
4827: );
4828: end if;
4829: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
4830: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
4831: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
4832: --
4833: -- If some are forfeited subtract from credit total
4834: --
4835: l_total_credits:=l_total_credits-l_ledger_totals.frftd_total;
4833: -- If some are forfeited subtract from credit total
4834: --
4835: l_total_credits:=l_total_credits-l_ledger_totals.frftd_total;
4836: --
4837: hr_utility.set_location(l_proc,110 );
4838: --end if; -- if auto_alct_excs_flag='Y'
4839: --close c_pool_info;
4840: --
4841: if l_pool_info.uses_net_crs_mthd_flag = 'Y' then
4849: else
4850: l_def_exc_amount := l_def_exc_amount +
4851: (l_ledger_totals.prvdd_total - l_ledger_totals.used_total);
4852: end if;
4853: hr_utility.set_location('l_def_exc_amount: '||l_def_exc_amount,110 );
4854: end if;
4855: --
4856: -- Check if there is a total pool restriction.
4857: --
4866: end loop;
4867: --
4868: end loop;
4869: close c_ledger_totals;
4870: hr_utility.set_location(l_proc,120 );
4871:
4872: ------Bug 7118730,recalculate the ledger and distribute the credits.
4873: if l_redis_credits then
4874: open c_ledger_totals;
4874: open c_ledger_totals;
4875: --
4876: fetch c_ledger_totals into l_ledger_totals;
4877: if c_ledger_totals%found then
4878: hr_utility.set_location(l_proc, 30);
4879: hr_utility.set_location(l_proc, 55);
4880: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4881: 'used_total = ' || l_ledger_totals.used_total ||
4882: 'frftd_total = ' || l_ledger_totals.frftd_total ||
4875: --
4876: fetch c_ledger_totals into l_ledger_totals;
4877: if c_ledger_totals%found then
4878: hr_utility.set_location(l_proc, 30);
4879: hr_utility.set_location(l_proc, 55);
4880: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4881: 'used_total = ' || l_ledger_totals.used_total ||
4882: 'frftd_total = ' || l_ledger_totals.frftd_total ||
4883: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
4876: fetch c_ledger_totals into l_ledger_totals;
4877: if c_ledger_totals%found then
4878: hr_utility.set_location(l_proc, 30);
4879: hr_utility.set_location(l_proc, 55);
4880: hr_utility.set_location( 'prvdd_total = ' || l_ledger_totals.prvdd_total ||
4881: 'used_total = ' || l_ledger_totals.used_total ||
4882: 'frftd_total = ' || l_ledger_totals.frftd_total ||
4883: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
4884: if l_pool_info.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
4881: 'used_total = ' || l_ledger_totals.used_total ||
4882: 'frftd_total = ' || l_ledger_totals.frftd_total ||
4883: 'cash_recd_total = ' || l_ledger_totals.cash_recd_total, 55);
4884: if l_pool_info.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
4885: hr_utility.set_location(l_proc,90 );
4886: distribute_credits
4887: (p_validate => p_validate
4888: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4889: ,p_bnft_prvdr_pool_id => l_ledger_totals.bnft_prvdr_pool_id
4910: ,p_per_in_ler_id => p_per_in_ler_id
4911: ,p_bpl_cash_recd_val => l_dummy_number
4912: ,p_bnft_prvdd_ldgr_id => l_dummy_number
4913: );
4914: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 101);
4915: end if;
4916: --
4917: if l_acty_base_rt_id is not null and l_pool_info.dflt_excs_trtmt_cd is not null then
4918: -- Bug#4473573 - added net credit method condition
4937: p_person_id => p_person_id,
4938: p_enrt_mthd_cd => p_enrt_mthd_cd
4939: );
4940: end if;
4941: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
4942: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
4943: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
4944: hr_utility.set_location( 'Re-distributed credits', 102);
4945: end if;
4938: p_enrt_mthd_cd => p_enrt_mthd_cd
4939: );
4940: end if;
4941: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
4942: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
4943: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
4944: hr_utility.set_location( 'Re-distributed credits', 102);
4945: end if;
4946: close c_ledger_totals;
4939: );
4940: end if;
4941: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
4942: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
4943: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
4944: hr_utility.set_location( 'Re-distributed credits', 102);
4945: end if;
4946: close c_ledger_totals;
4947: end if;
4940: end if;
4941: hr_utility.set_location( 'l_dstrbtd_val = ' || l_dstrbtd_val, 102);
4942: hr_utility.set_location( 'l_rollover_val = ' || l_rollover_val, 102);
4943: hr_utility.set_location( 'frftd_total = ' || l_ledger_totals.frftd_total, 102);
4944: hr_utility.set_location( 'Re-distributed credits', 102);
4945: end if;
4946: close c_ledger_totals;
4947: end if;
4948: ------Bug 7118730
4956: if l_uses_net_crs_mthd then
4957: --
4958: -- Write an enrollment result for the excess/deficit amount.
4959: --
4960: hr_utility.set_location('l_def_exc_amount'||l_def_exc_amount,110 );
4961: hr_utility.set_location('prvdd_total'||l_ledger_totals.prvdd_total,110 );
4962: hr_utility.set_location('used_total'||l_ledger_totals.used_total,110 );
4963: create_flex_credit_enrolment
4964: (p_person_id => p_person_id,
4957: --
4958: -- Write an enrollment result for the excess/deficit amount.
4959: --
4960: hr_utility.set_location('l_def_exc_amount'||l_def_exc_amount,110 );
4961: hr_utility.set_location('prvdd_total'||l_ledger_totals.prvdd_total,110 );
4962: hr_utility.set_location('used_total'||l_ledger_totals.used_total,110 );
4963: create_flex_credit_enrolment
4964: (p_person_id => p_person_id,
4965: p_enrt_mthd_cd => p_enrt_mthd_cd,
4958: -- Write an enrollment result for the excess/deficit amount.
4959: --
4960: hr_utility.set_location('l_def_exc_amount'||l_def_exc_amount,110 );
4961: hr_utility.set_location('prvdd_total'||l_ledger_totals.prvdd_total,110 );
4962: hr_utility.set_location('used_total'||l_ledger_totals.used_total,110 );
4963: create_flex_credit_enrolment
4964: (p_person_id => p_person_id,
4965: p_enrt_mthd_cd => p_enrt_mthd_cd,
4966: p_business_group_id => p_business_group_id,
4973: p_pgm_id => p_pgm_id
4974: );
4975: end if;
4976: --
4977: hr_utility.set_location('calling create_flex,total_credits='||to_char(l_total_credits),130);
4978: create_flex_credit_enrolment(
4979: p_person_id => p_person_id,
4980: p_enrt_mthd_cd => p_enrt_mthd_cd,
4981: p_business_group_id => p_business_group_id,
4985: p_per_in_ler_id => p_per_in_ler_id,
4986: p_rt_val => l_total_credits,
4987: p_pgm_id => p_pgm_id
4988: );
4989: hr_utility.set_location(l_proc, 140);
4990: open c_element_details;
4991: fetch c_element_details into
4992: p_acty_ref_perd_cd,
4993: p_acty_base_rt_id,
4997: ;
4998: close c_element_details;
4999: end if;
5000: --
5001: hr_utility.set_location('Leaving:'||l_proc, 999);
5002: --
5003: end total_pools;
5004: --
5005: --------------------------------------------------------------------------------
5115: l_proc varchar2(80) := g_package||'.update_rate';
5116: --
5117: begin
5118: --
5119: hr_utility.set_location('Entering '||l_proc,5);
5120: --
5121: open c_prv;
5122: fetch c_prv into l_prv_rec;
5123: close c_prv;
5151: fetch c_lee_rsn_for_program into l_lee_rsn_id ;
5152: close c_lee_rsn_for_program ;
5153: end if;
5154: --
5155: hr_utility.set_location(l_proc,10);
5156: --
5157: ben_determine_date.rate_and_coverage_dates(
5158: p_which_dates_cd => 'R',
5159: p_pgm_id => l_pen_rec.pgm_id,
5177: p_rt_end_dt_rl => l_rt_end_dt_rl,
5178: p_effective_date => p_effective_date
5179: );
5180: --
5181: hr_utility.set_location(l_proc,15);
5182: --
5183: if l_prv_rec.rt_strt_dt >= l_rt_strt_dt then
5184: --
5185: ben_prtt_rt_val_api.delete_prtt_rt_val(
5206: ,p_effective_date => p_effective_date
5207: );
5208: end if;
5209: --
5210: hr_utility.set_location(l_proc,20);
5211: --
5212: -- Bug#2441871
5213: if l_enrt_rt_id is not null then
5214: --
5245: );
5246: --
5247: End if;
5248: --
5249: hr_utility.set_location('Leaving '||l_proc,5);
5250: --
5251: end update_rate;
5252: --
5253: --------------------------------------------------------------------------------
5326: l_proc varchar2(80) := g_package||'.delete_all_ledgers';
5327: --
5328: begin
5329: --
5330: hr_utility.set_location('Enetering '||l_proc, 5);
5331: for l_bpl_rec in c_bpl loop
5332: --
5333: hr_utility.set_location(l_proc,10);
5334: if p_effective_date = l_bpl_rec.effective_start_date then
5329: --
5330: hr_utility.set_location('Enetering '||l_proc, 5);
5331: for l_bpl_rec in c_bpl loop
5332: --
5333: hr_utility.set_location(l_proc,10);
5334: if p_effective_date = l_bpl_rec.effective_start_date then
5335: --
5336: l_datetrack_mode := hr_api.g_zap;
5337: l_effective_date := p_effective_date;
5355: p_process_enrt_flag => 'N');
5356: --
5357: -- Check whether rollovers are attached.
5358: --
5359: hr_utility.set_location(l_proc,15);
5360: if l_bpl_rec.prtt_ro_of_unusd_amt_flag = 'Y' then
5361: --
5362: hr_utility.set_location(l_proc,20);
5363: open c_pen(l_bpl_rec.acty_base_rt_id);
5358: --
5359: hr_utility.set_location(l_proc,15);
5360: if l_bpl_rec.prtt_ro_of_unusd_amt_flag = 'Y' then
5361: --
5362: hr_utility.set_location(l_proc,20);
5363: open c_pen(l_bpl_rec.acty_base_rt_id);
5364: fetch c_pen into l_prtt_enrt_rslt_id,
5365: l_pen_object_version_number,
5366: l_pen_effective_start_date,
5412: end if;
5413: --
5414: end loop;
5415: --
5416: hr_utility.set_location('Leaving '||l_proc,5);
5417: --
5418: end delete_all_ledgers;
5419: --
5420: --------------------------------------------------------------------------------
5596: l_dummy varchar2(80);
5597: l_rt_strt_dt date;
5598: --
5599: begin
5600: hr_utility.set_location('Entering:'|| l_proc, 5);
5601: --
5602: -- Running total for the rollover.
5603: --
5604: l_rollover_diff_total:=0;
5647: if l_qualify_flag='Y' then
5648: --
5649: -- Process this rollover
5650: --
5651: hr_utility.set_location(l_proc, 20);
5652: l_acty_base_rt_id:=l_rollover.acty_base_rt_id;
5653: --
5654: -- Get the ledger totals with exclusions (see cursor)
5655: --
5663: --
5664: -- depending on the excess treatment code compute the balance
5665: --
5666: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
5667: hr_utility.set_location(l_proc, 30);
5668: l_balance:=l_prvdd_val-l_used_val-l_recd_val-l_rld_ovr_val;
5669: else
5670: hr_utility.set_location(l_proc, 40);
5671: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
5666: if p_dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
5667: hr_utility.set_location(l_proc, 30);
5668: l_balance:=l_prvdd_val-l_used_val-l_recd_val-l_rld_ovr_val;
5669: else
5670: hr_utility.set_location(l_proc, 40);
5671: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
5672: end if;
5673: hr_utility.set_location(l_proc, 50);
5674: --
5669: else
5670: hr_utility.set_location(l_proc, 40);
5671: l_balance:=l_prvdd_val-l_used_val-l_rld_ovr_val;
5672: end if;
5673: hr_utility.set_location(l_proc, 50);
5674: --
5675: -- round the balance
5676: --
5677: if l_rollover.val_rndg_cd is not null then
5742: --
5743: if (l_balance < l_rollover.mn_rlovr_val or
5744: l_balance < l_mn_dstrbl_pct_num or
5745: l_balance <=0 ) then
5746: hr_utility.set_location('Balance less than minimum or zero',60);
5747: --
5748: -- nothing to do
5749: --
5750: null;
5749: --
5750: null;
5751: --
5752: else
5753: hr_utility.set_location(l_proc, 70);
5754: --
5755: open c_old_ledger;
5756: fetch c_old_ledger into
5757: l_bnft_prvdd_ldgr_id,
5761: ;
5762: --
5763: -- perform rollover
5764: --
5765: hr_utility.set_location(l_proc, 80);
5766: if c_old_ledger%notfound
5767: and not p_calculate_only_mode
5768: then
5769: hr_utility.set_location(l_proc, 90);
5765: hr_utility.set_location(l_proc, 80);
5766: if c_old_ledger%notfound
5767: and not p_calculate_only_mode
5768: then
5769: hr_utility.set_location(l_proc, 90);
5770: --
5771: -- Check the rollover plan is already enrolled.
5772: -- If not, no rollovers to be attached to this plan.
5773: --
5797: ,p_cash_recd_val => null
5798: ,p_effective_date => p_effective_date
5799: ,p_process_enrt_flag => 'N'
5800: );
5801: hr_utility.set_location('CREATED LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),100);
5802: --
5803: -- Update the rollover plan rate to reflect rollovers.
5804: --
5805: update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
5818: then
5819: --
5820: -- Update the ledger if the amount differs.
5821: --
5822: hr_utility.set_location(l_proc, 110);
5823: /*
5824: if l_effective_start_date=p_effective_date then
5825: l_datetrack_mode:=hr_api.g_correction;
5826: else
5832: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
5833: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
5834: p_base_key_value => l_bnft_prvdd_ldgr_id,
5835: p_mode => l_datetrack_mode);
5836: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),120);
5837: l_rollover_diff_total:=l_rollover_diff_total+l_balance-l_old_rlovr_val;
5838: ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
5839: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
5840: ,p_effective_start_date => l_effective_start_date
5853: ,p_effective_date => p_effective_date
5854: ,p_datetrack_mode => l_datetrack_mode
5855: ,p_process_enrt_flag => 'N'
5856: );
5857: hr_utility.set_location('UPDATED LEDGER ID='||To_char(l_bnft_prvdd_ldgr_id),130);
5858: --
5859: open c_prv(-1);
5860: fetch c_prv into l_prtt_rt_val_id,
5861: l_rt_val, l_rt_strt_dt;
5873: end if;
5874: close c_old_ledger;
5875: end if;
5876: end if; -- rule returned 'Y'
5877: hr_utility.set_location(l_proc, 140);
5878: end loop;
5879: --
5880: p_rollover_val:=l_rollover_diff_total;
5881: hr_utility.set_location('Leaving:'||l_proc, 999);
5877: hr_utility.set_location(l_proc, 140);
5878: end loop;
5879: --
5880: p_rollover_val:=l_rollover_diff_total;
5881: hr_utility.set_location('Leaving:'||l_proc, 999);
5882: --
5883: end compute_rollovers;
5884: --
5885: --------------------------------------------------------------------------------
5980: l_dummy_number number;
5981: --
5982: begin
5983: --
5984: hr_utility.set_location('Entering '||l_proc,5);
5985: --
5986: -- Get all the totals for the pool.
5987: --
5988: p_frftd_val := 0;
6008: close c_pool;
6009: --
6010: -- If auto allocate flag is ON, then do the allocation.
6011: --
6012: hr_utility.set_location(l_proc,10);
6013: --
6014: if l_pool_rec.auto_alct_excs_flag = 'Y' then
6015: --
6016: g_credit_pool_result_id := p_flex_rslt_id;
6014: if l_pool_rec.auto_alct_excs_flag = 'Y' then
6015: --
6016: g_credit_pool_result_id := p_flex_rslt_id;
6017: --
6018: hr_utility.set_location(l_proc,15);
6019: -- get the dflt trtmt cd by executing the rule if necessary
6020: if l_pool_rec.dflt_excs_trtmt_rl is not null then
6021: run_rule
6022: (p_effective_date => p_effective_date
6032: if l_pool_rec.dflt_excs_trtmt_cd in ('DSTRBT_ALL','DSTRBT_RLOVR_FRFT') then
6033: --
6034: -- Distribute the cash.
6035: --
6036: hr_utility.set_location(l_proc,20);
6037: --
6038: distribute_credits
6039: (p_calculate_only_mode => p_calculate_only_mode
6040: ,p_per_in_ler_id => p_per_in_ler_id
6071: 'RLOVR_DSTRBT_FRFT') then
6072: --
6073: -- Rollover the excess, if anything left.
6074: --
6075: hr_utility.set_location(l_proc,25);
6076: compute_rollovers
6077: (p_calculate_only_mode => p_calculate_only_mode
6078: ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
6079: ,p_person_id => p_person_id
6090: --
6091: end if;
6092: --
6093: if l_pool_rec.dflt_excs_trtmt_cd ='RLOVR_DSTRBT_FRFT' then
6094: hr_utility.set_location(l_proc,90 );
6095: --
6096: -- Distribute as cash, if anything still left.
6097: --
6098: hr_utility.set_location(l_proc,30);
6094: hr_utility.set_location(l_proc,90 );
6095: --
6096: -- Distribute as cash, if anything still left.
6097: --
6098: hr_utility.set_location(l_proc,30);
6099: distribute_credits
6100: (p_calculate_only_mode => p_calculate_only_mode
6101: ,p_per_in_ler_id => p_per_in_ler_id
6102: ,p_prtt_enrt_rslt_id => p_flex_rslt_id
6130: --
6131: -- All possible rollovers and cash distribution done. If anything is
6132: -- still left, it has to be forfeited.
6133: --
6134: hr_utility.set_location(l_proc,35);
6135: forfeit_credits
6136: (p_calculate_only_mode => p_calculate_only_mode
6137: ,p_prtt_enrt_rslt_id => p_flex_rslt_id
6138: ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
6165: end if; -- end net credit method.
6166: --
6167: p_def_exc_amount := l_def_exc_amount;
6168: --
6169: hr_utility.set_location('Leaving '||l_proc,999);
6170:
6171: end compute_excess;
6172: --
6173: --------------------------------------------------------------------------------
6301: l_proc varchar2(80) := g_package||'.update_net_credit_rate';
6302: --
6303: begin
6304: --
6305: hr_utility.set_location('Entering '||l_proc,5);
6306: open c_pen;
6307: fetch c_pen into l_pen_rec;
6308: close c_pen;
6309: --
6310: if p_prtt_rt_val_id is not null then
6311: --
6312: l_prtt_rt_val_id := p_prtt_rt_val_id;
6313: --
6314: hr_utility.set_location('update_net_credit amt '||p_def_exc_amount,5);
6315: update_rate
6316: (p_prtt_rt_val_id => l_prtt_rt_val_id
6317: ,p_val => p_def_exc_amount
6318: ,p_prtt_enrt_rslt_id => p_flex_rslt_id
6564: ,p_ordr_num => l_abr_rec.ordr_num
6565: );
6566: end if;
6567: --
6568: hr_utility.set_location('Leaving '||l_proc,999);
6569: --
6570: end update_net_credit_rate;
6571: --------------------------------------------------------------------------------
6572: -- recompute_flex_credits
6766: l_proc varchar2(80) := g_package||'.recompute_flex_credits';
6767: --
6768: begin
6769: --
6770: hr_utility.set_location('Entering '||l_proc,5);
6771: --
6772: if p_pgm_id is null or
6773: p_person_id is null or
6774: p_prtt_enrt_rslt_id is null then
6776: return;
6777: --
6778: end if;
6779: --
6780: hr_utility.set_location(l_proc, 10);
6781: --
6782: open c_pgm;
6783: fetch c_pgm into l_flex_pgm;
6784: --
6851: p_datetrack_mode => l_datetrack_mode,
6852: p_business_group_id => p_business_group_id,
6853: p_process_enrt_flag => 'N');
6854: --
6855: hr_utility.set_location(l_proc,15);
6856: --
6857: end if;
6858: --
6859: close c_dbt_bpl;
6857: end if;
6858: --
6859: close c_dbt_bpl;
6860: --
6861: hr_utility.set_location(l_proc,20);
6862: --
6863: -- Loop through all the pools for the program, to compute the total
6864: -- credits available after the de-enrollment.
6865: --
6872: if l_crdt_bpp_rec.uses_net_crs_mthd_flag = 'Y' then
6873: l_uses_net_crs_mthd := true;
6874: end if;
6875: --
6876: hr_utility.set_location(l_proc,25);
6877: --
6878: l_flex_rslt_id := l_crdt_bpp_rec.prtt_enrt_rslt_id;
6879: --
6880: l_epe_rec.pgm_id := l_crdt_bpp_rec.pgm_id;
6894: p_effective_date => p_effective_date);
6895: --
6896: if l_person_enrolled then
6897: --
6898: hr_utility.set_location(l_proc,30);
6899: --
6900: l_total_credits := l_total_credits + l_crdt_bpp_rec.prvdd_val;
6901: --
6902: if l_crdt_bpp_rec.bnft_prvdr_pool_id <> l_debit_pool_id then
6918: --
6919: -- Person is not eligible for the pool, so we need to delete
6920: -- all ledgers for the pool.
6921: --
6922: hr_utility.set_location(l_proc,35);
6923: --
6924: l_credit_ledger_deleted := true;
6925: --
6926: if l_crdt_bpp_rec.bnft_prvdr_pool_id = l_debit_pool_id then
6943: end if;
6944: --
6945: end loop;
6946: --
6947: hr_utility.set_location(l_proc,40);
6948: --
6949: if not(l_credit_ledger_deleted) and not(l_debit_ledger_deleted) then
6950: --
6951: -- No ledger deletion took place, it means everything is in order.
6949: if not(l_credit_ledger_deleted) and not(l_debit_ledger_deleted) then
6950: --
6951: -- No ledger deletion took place, it means everything is in order.
6952: --
6953: hr_utility.set_location(l_proc,45);
6954: return;
6955: --
6956: elsif l_recompute_excess then
6957: --
6957: --
6958: -- Re-compute the excess amounts as a debit ledger is deleted,
6959: -- so we have more excess amount available.
6960: --
6961: hr_utility.set_location(l_proc,50);
6962: compute_excess
6963: (p_bnft_prvdr_pool_id => l_debit_pool_id
6964: ,p_flex_rslt_id => l_flex_rslt_id
6965: ,p_effective_date => p_effective_date
6971: ,p_frftd_val => l_frftd_val
6972: ,p_def_exc_amount => l_def_exc_amount
6973: ,p_bpl_cash_recd_val => l_dummy_number
6974: );
6975: hr_utility.set_location('l_def_exc_amount:1 '||l_def_exc_amount ,50);
6976: --
6977: -- Reduce forfeited credits for the pool from which the
6978: -- debit ledger was deleted.
6979: --
7097: --
7098: -- Update the flex credit rate with the total credits,
7099: -- if the value has changed.
7100: --
7101: hr_utility.set_location ('Total Credits'||l_total_credits,111);
7102: hr_utility.set_location ('Rate Value '||l_rt_val,111);
7103: if c_prv%found and l_total_credits <> l_rt_val then
7104: --
7105: hr_utility.set_location(l_proc,55);
7098: -- Update the flex credit rate with the total credits,
7099: -- if the value has changed.
7100: --
7101: hr_utility.set_location ('Total Credits'||l_total_credits,111);
7102: hr_utility.set_location ('Rate Value '||l_rt_val,111);
7103: if c_prv%found and l_total_credits <> l_rt_val then
7104: --
7105: hr_utility.set_location(l_proc,55);
7106: update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
7101: hr_utility.set_location ('Total Credits'||l_total_credits,111);
7102: hr_utility.set_location ('Rate Value '||l_rt_val,111);
7103: if c_prv%found and l_total_credits <> l_rt_val then
7104: --
7105: hr_utility.set_location(l_proc,55);
7106: update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
7107: p_val => l_total_credits,
7108: p_prtt_enrt_rslt_id => l_flex_rslt_id,
7109: p_ended_per_in_ler_id => p_per_in_ler_id,
7113: end if;
7114:
7115: close c_prv;
7116:
7117: hr_utility.set_location('Leaving '||l_proc,999);
7118:
7119: end recompute_flex_credits;
7120: --
7121: --