[Home] [Help]
1: package body ben_generate_communications as
2: /* $Header: bencommu.pkb 120.22.12020000.6 2012/07/03 12:17:35 amnaraya ship $ */
3: --
4: g_package varchar2(30) := 'ben_generate_communications.';
5: -- bwharton bug 1619271 added 5 globals below.
1: package body ben_generate_communications as
2: /* $Header: bencommu.pkb 120.22.12020000.6 2012/07/03 12:17:35 amnaraya ship $ */
3: --
4: g_package varchar2(30) := 'ben_generate_communications.';
5: -- bwharton bug 1619271 added 5 globals below.
6: g_p_date_cd varchar2(30);
7: g_p_lf_evt_ocrd_dt date;
8: g_p_effective_date date;
433: -- rather than c_per_cm
434: --
435: hr_utility.set_location ('p_lf_evt_ocrd_dt 1'||p_lf_evt_ocrd_dt,101);
436: hr_utility.set_location ('p_effective_date 1'||p_effective_date,101);
437: hr_utility.set_location ('Condition'||ben_generate_communications.g_comm_start_date,101);
438: if p_per_in_ler_id is not null then
439: --
440: hr_utility.set_location ('Condition 1',101);
441: open c_pil_per_cm
447: ,c_cm_typ_id => p_cm_typ_id
448: ,c_pil_id => p_per_in_ler_id
449: ,c_leo_dt => p_lf_evt_ocrd_dt
450: ,c_eff_dt => p_effective_date
451: ,c_comm_sdt => ben_generate_communications.g_comm_start_date
452: );
453: fetch c_pil_per_cm into p_per_cm_id;
454: if c_pil_per_cm%notfound then
455: l_notfound := TRUE;
470: ,c_dpntper_id => p_dpnt_person_id
471: ,c_cm_typ_id => p_cm_typ_id
472: ,c_leo_dt => p_lf_evt_ocrd_dt
473: ,c_eff_dt => p_effective_date
474: ,c_comm_sdt => ben_generate_communications.g_comm_start_date
475: );
476: fetch c_perid_per_cm into p_per_cm_id;
477: if c_perid_per_cm%notfound then
478: l_notfound := TRUE;
494: ,c_pil_id => p_per_in_ler_id
495: ,c_leo_dt => p_lf_evt_ocrd_dt
496: ,c_eff_dt => p_effective_date
497: ,c_bgp_id => p_business_group_id
498: ,c_comm_sdt => ben_generate_communications.g_comm_start_date
499: );
500: --
501: fetch c_per_cm into p_per_cm_id;
502: if c_per_cm%notfound then
509: end if;
510: --
511: if l_notfound then
512: --
513: open c_elig_rec(ben_generate_communications.g_comm_start_date);
514: fetch c_elig_rec into l_elig_rec;
515: if(c_elig_rec%found) then
516: close c_elig_rec;
517: l_dummy_val := 'Y';
541: l_continue_eval := 'Y' ;
542: elsif (l_eval = 'N' and l_dummy_val = 'Y') then
543: ben_eval_comm_ler_elig_profl.check_eligible
544: (p_person_id => p_person_id
545: ,p_effective_date => nvl(ben_generate_communications.g_comm_start_date,p_effective_date)
546: --,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
547: ,p_business_group_id => p_business_group_id
548: ,p_per_in_ler_id => p_per_in_ler_id
549: --,p_ler_id => p_ler_id
609: ,p_pcm_attribute28 => null
610: ,p_pcm_attribute29 => null
611: ,p_pcm_attribute30 => null
612: ,p_object_version_number => l_object_version_number
613: ,p_effective_date => ben_generate_communications.
614: g_comm_start_date
615: ,p_request_id => fnd_global.conc_request_id
616: ,p_program_application_id => fnd_global.prog_appl_id
617: ,p_program_id => fnd_global.conc_program_id
646: from ben_per_cm_trgr_f pcr
647: where pcr.cm_trgr_id = p_cm_trgr_id
648: and pcr.per_cm_id = p_per_cm_id
649: and pcr.business_group_id = p_business_group_id
650: and ben_generate_communications.g_comm_start_date
651: between pcr.effective_start_date
652: and pcr.effective_end_date;
653: --
654: begin
699: ,p_pcr_attribute28 => null
700: ,p_pcr_attribute29 => null
701: ,p_pcr_attribute30 => null
702: ,p_object_version_number => l_object_version_number
703: ,p_effective_date => ben_generate_communications.
704: g_comm_start_date);
705: --
706: end if;
707: --
738: from ben_per_cm_prvdd_f pcd
739: where pcd.per_cm_id = p_per_cm_id
740: and pcd.sent_dt is null
741: and pcd.business_group_id = p_business_group_id
742: and ben_generate_communications.g_comm_start_date
743: between pcd.effective_start_date
744: and pcd.effective_end_date;
745: --
746: cursor c_get_instnc_num is
747: select max(pcd.instnc_num)
748: from ben_per_cm_prvdd_f pcd
749: where pcd.per_cm_id = p_per_cm_id
750: and pcd.business_group_id = p_business_group_id
751: and ben_generate_communications.g_comm_start_date
752: between pcd.effective_start_date
753: and pcd.effective_end_date;
754: --
755: l_instnc_num number;
828: ,p_pcd_attribute28 => null
829: ,p_pcd_attribute29 => null
830: ,p_pcd_attribute30 => null
831: ,p_object_version_number => l_object_version_number
832: ,p_effective_date => ben_generate_communications.
833: g_comm_start_date);
834: --
835: else
836: close c_per_cm_prvdd;
861: from ben_per_cm_usg_f pcu
862: where pcu.per_cm_id = p_per_cm_id
863: and pcu.cm_typ_usg_id = p_cm_typ_usg_id
864: and pcu.business_group_id = p_business_group_id
865: and ben_generate_communications.g_comm_start_date between
866: pcu.effective_start_date and pcu.effective_end_date;
867: --
868: begin
869: --
916: ,p_pcu_attribute28 => null
917: ,p_pcu_attribute29 => null
918: ,p_pcu_attribute30 => null
919: ,p_object_version_number => l_object_version_number
920: ,p_effective_date => ben_generate_communications.
921: g_comm_start_date);
922: --
923: end if;
924: --
1161:
1162: )
1163: )
1164: /* Use nvl here as only pgm pl can be populated */
1165: and ben_generate_communications.g_comm_start_date
1166: between ctu.effective_start_date
1167: and ctu.effective_end_date
1168: and ctu.cm_typ_id = p_cm_typ_id
1169: and ctu.all_r_any_cd = 'ALL'
1394: hr_utility.set_location(' p_assignment_id ' || p_assignment_id , 1999);
1395: hr_utility.set_location(' p_per_cm_id ' || p_per_cm_id , 1999);
1396: hr_utility.set_location(' p_actn_typ_id ' || p_actn_typ_id , 1999);
1397: hr_utility.set_location(' p_cm_typ_id ' || p_cm_typ_id , 1999);
1398: hr_utility.set_location(' comm_start_date'||ben_generate_communications.g_comm_start_date , 1999);
1399: hr_utility.set_location(' p_ler_id ' || p_ler_id , 1999);
1400: hr_utility.set_location(' p_pgm_id ' || p_pgm_id , 1999);
1401: hr_utility.set_location(' p_pl_id ' || p_pl_id , 1999);
1402:
1582: ) */
1583: )
1584: and (p_pl_typ_id is null or
1585: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
1586: and ben_generate_communications.g_comm_start_date
1587: between ctu.effective_start_date
1588: and ctu.effective_end_date
1589: and ctu.cm_typ_id = p_cm_typ_id
1590: and ctu.all_r_any_cd = 'ALL'
1847: (c_pil_id => p_per_in_ler_id
1848: ,c_bgp_id => p_business_group_id
1849: ,c_pl_typ_id => p_pl_typ_id
1850: ,c_asnd_lf_evt_dt => p_asnd_lf_evt_dt
1851: ,c_comm_st_date => ben_generate_communications.g_comm_start_date
1852: ,c_cm_typ_id => p_cm_typ_id
1853: );
1854: fetch c1 BULK COLLECT INTO l_ctu_cm_typ_usg_id_va,
1855: l_ctu_cm_usg_rl_va,
2014: from ben_elig_per_elctbl_chc epe
2015: where epe.business_group_id = p_business_group_id
2016: and epe.elctbl_flag = 'Y'
2017: and epe.per_in_ler_id = p_per_in_ler_id)
2018: and ben_generate_communications.g_comm_start_date
2019: between ctu.effective_start_date
2020: and ctu.effective_end_date
2021: /* This process code can only apply to ALL since one context only
2022: can be set, we code for both though just in case */
2189: enp_m.business_group_id=ctu.business_group_id and
2190: enp_m.strt_dt=enp_c.strt_dt
2191: ) */
2192: )
2193: and ben_generate_communications.g_comm_start_date
2194: between ctu.effective_start_date
2195: and ctu.effective_end_date
2196: and ctu.cm_typ_id = p_cm_typ_id
2197: and ctu.all_r_any_cd = 'ALL'
2369: and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2370: and pel.pgm_id is null)
2371: or nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)))
2372: or (ctu.enrt_perd_id is null))
2373: and ben_generate_communications.g_comm_start_date
2374: between ctu.effective_start_date
2375: and ctu.effective_end_date
2376: and ctu.cm_typ_id = p_cm_typ_id
2377: and ctu.all_r_any_cd = 'ALL'
2524: and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2525: and pel.pgm_id is null)
2526: or nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)))
2527: or (ctu.enrt_perd_id is null))
2528: and ben_generate_communications.g_comm_start_date
2529: between ctu.effective_start_date
2530: and ctu.effective_end_date
2531: and ctu.cm_typ_id = p_cm_typ_id
2532: and ctu.all_r_any_cd = 'ALL'
2665: and (p_pl_typ_id is null or
2666: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2667: and (p_actn_typ_id is null or
2668: nvl(ctu.actn_typ_id,p_actn_typ_id) = p_actn_typ_id)
2669: and ben_generate_communications.g_comm_start_date
2670: between ctu.effective_start_date
2671: and ctu.effective_end_date
2672: and ctu.cm_typ_id = p_cm_typ_id
2673: and ctu.all_r_any_cd = 'ALL';
2782: and (p_pl_id is null or
2783: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2784: and (p_pl_typ_id is null or
2785: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2786: and ben_generate_communications.g_comm_start_date
2787: between ctu.effective_start_date
2788: and ctu.effective_end_date
2789: and ctu.cm_typ_id = p_cm_typ_id
2790: and ctu.all_r_any_cd = 'ALL';
2808: hr_utility.set_location('PL: '||p_pl_id,10);
2809: hr_utility.set_location('ler : '||p_ler_id,10);
2810: hr_utility.set_location('pgm: '|| p_pgm_id,10);
2811: hr_utility.set_location('pl_typ: '||p_pl_typ_id,10);
2812: hr_utility.set_location('comm date : '||ben_generate_communications.g_comm_start_date,10);
2813: hr_utility.set_location('comm type : '||p_cm_typ_id,10);
2814:
2815: --
2816: l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2903: and (p_pl_id is null or
2904: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2905: and (p_pl_typ_id is null or
2906: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2907: and ben_generate_communications.g_comm_start_date
2908: between ctu.effective_start_date
2909: and ctu.effective_end_date
2910: and ctu.cm_typ_id = p_cm_typ_id
2911: and ctu.all_r_any_cd = 'ALL';
3017: and (p_pl_id is null or
3018: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
3019: and (p_pl_typ_id is null or
3020: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3021: and ben_generate_communications.g_comm_start_date
3022: between ctu.effective_start_date
3023: and ctu.effective_end_date
3024: and ctu.cm_typ_id = p_cm_typ_id
3025: and ctu.all_r_any_cd = 'ALL';
3155: and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
3156: = nvl(pel.enrt_perd_id, -1)
3157: and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
3158: and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
3159: and ben_generate_communications.g_comm_start_date
3160: between ctu.effective_start_date
3161: and ctu.effective_end_date
3162: and ctu.cm_typ_id = p_cm_typ_id
3163: and ctu.all_r_any_cd = 'ALL'
3290: and (p_pgm_id is null or
3291: nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3292: and (p_pl_typ_id is null or
3293: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3294: and ben_generate_communications.g_comm_start_date
3295: between ctu.effective_start_date
3296: and ctu.effective_end_date
3297: and ctu.cm_typ_id = p_cm_typ_id
3298: and ctu.all_r_any_cd = 'ALL';
3443: enp_m.business_group_id=ctu.business_group_id and
3444: enp_m.strt_dt=enp_c.strt_dt
3445: ) */
3446: )
3447: and ben_generate_communications.g_comm_start_date
3448: between ctu.effective_start_date
3449: and ctu.effective_end_date
3450: and ctu.cm_typ_id = p_cm_typ_id
3451: and ctu.all_r_any_cd = 'ALL'
3611: enp_m.business_group_id=ctu.business_group_id and
3612: enp_m.strt_dt=enp_c.strt_dt
3613: ) */
3614: )
3615: and ben_generate_communications.g_comm_start_date
3616: between ctu.effective_start_date
3617: and ctu.effective_end_date
3618: and ctu.cm_typ_id = p_cm_typ_id
3619: and ctu.all_r_any_cd = 'ALL';
3890: enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
3891: )
3892: )
3893: and ctu.pl_typ_id is not null
3894: and ben_generate_communications.g_comm_start_date
3895: between ctu.effective_start_date
3896: and ctu.effective_end_date
3897: and ctu.cm_typ_id = p_cm_typ_id
3898: and ctu.pl_typ_id is not null
4973: --
4974: hr_utility.set_location('Displaying stats ',10);
4975: end main;
4976: --
4977: end ben_generate_communications;
4978: --