[Home] [Help]
1: package body ben_generate_communications as
2: /* $Header: bencommu.pkb 120.4.12000000.4 2007/10/17 11:46:44 rtagarra 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.4.12000000.4 2007/10/17 11:46:44 rtagarra 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;
369: ,c_cm_typ_id => p_cm_typ_id
370: ,c_pil_id => p_per_in_ler_id
371: ,c_leo_dt => p_lf_evt_ocrd_dt
372: ,c_eff_dt => p_effective_date
373: ,c_comm_sdt => ben_generate_communications.g_comm_start_date
374: );
375: fetch c_pil_per_cm into p_per_cm_id;
376: if c_pil_per_cm%notfound then
377: l_notfound := TRUE;
391: ,c_dpntper_id => p_dpnt_person_id
392: ,c_cm_typ_id => p_cm_typ_id
393: ,c_leo_dt => p_lf_evt_ocrd_dt
394: ,c_eff_dt => p_effective_date
395: ,c_comm_sdt => ben_generate_communications.g_comm_start_date
396: );
397: fetch c_perid_per_cm into p_per_cm_id;
398: if c_perid_per_cm%notfound then
399: l_notfound := TRUE;
414: ,c_pil_id => p_per_in_ler_id
415: ,c_leo_dt => p_lf_evt_ocrd_dt
416: ,c_eff_dt => p_effective_date
417: ,c_bgp_id => p_business_group_id
418: ,c_comm_sdt => ben_generate_communications.g_comm_start_date
419: );
420: --
421: fetch c_per_cm into p_per_cm_id;
422: if c_per_cm%notfound then
476: ,p_pcm_attribute28 => null
477: ,p_pcm_attribute29 => null
478: ,p_pcm_attribute30 => null
479: ,p_object_version_number => l_object_version_number
480: ,p_effective_date => ben_generate_communications.
481: g_comm_start_date
482: ,p_request_id => fnd_global.conc_request_id
483: ,p_program_application_id => fnd_global.prog_appl_id
484: ,p_program_id => fnd_global.conc_program_id
510: from ben_per_cm_trgr_f pcr
511: where pcr.cm_trgr_id = p_cm_trgr_id
512: and pcr.per_cm_id = p_per_cm_id
513: and pcr.business_group_id = p_business_group_id
514: and ben_generate_communications.g_comm_start_date
515: between pcr.effective_start_date
516: and pcr.effective_end_date;
517: --
518: begin
563: ,p_pcr_attribute28 => null
564: ,p_pcr_attribute29 => null
565: ,p_pcr_attribute30 => null
566: ,p_object_version_number => l_object_version_number
567: ,p_effective_date => ben_generate_communications.
568: g_comm_start_date);
569: --
570: end if;
571: --
602: from ben_per_cm_prvdd_f pcd
603: where pcd.per_cm_id = p_per_cm_id
604: and pcd.sent_dt is null
605: and pcd.business_group_id = p_business_group_id
606: and ben_generate_communications.g_comm_start_date
607: between pcd.effective_start_date
608: and pcd.effective_end_date;
609: --
610: cursor c_get_instnc_num is
611: select max(pcd.instnc_num)
612: from ben_per_cm_prvdd_f pcd
613: where pcd.per_cm_id = p_per_cm_id
614: and pcd.business_group_id = p_business_group_id
615: and ben_generate_communications.g_comm_start_date
616: between pcd.effective_start_date
617: and pcd.effective_end_date;
618: --
619: l_instnc_num number;
692: ,p_pcd_attribute28 => null
693: ,p_pcd_attribute29 => null
694: ,p_pcd_attribute30 => null
695: ,p_object_version_number => l_object_version_number
696: ,p_effective_date => ben_generate_communications.
697: g_comm_start_date);
698: --
699: else
700: close c_per_cm_prvdd;
725: from ben_per_cm_usg_f pcu
726: where pcu.per_cm_id = p_per_cm_id
727: and pcu.cm_typ_usg_id = p_cm_typ_usg_id
728: and pcu.business_group_id = p_business_group_id
729: and ben_generate_communications.g_comm_start_date between
730: pcu.effective_start_date and pcu.effective_end_date;
731: --
732: begin
733: --
780: ,p_pcu_attribute28 => null
781: ,p_pcu_attribute29 => null
782: ,p_pcu_attribute30 => null
783: ,p_object_version_number => l_object_version_number
784: ,p_effective_date => ben_generate_communications.
785: g_comm_start_date);
786: --
787: end if;
788: --
973:
974: )
975: )
976: /* Use nvl here as only pgm pl can be populated */
977: and ben_generate_communications.g_comm_start_date
978: between ctu.effective_start_date
979: and ctu.effective_end_date
980: and ctu.cm_typ_id = p_cm_typ_id
981: and ctu.all_r_any_cd = 'ALL'
1166: hr_utility.set_location(' p_assignment_id ' || p_assignment_id , 1999);
1167: hr_utility.set_location(' p_per_cm_id ' || p_per_cm_id , 1999);
1168: hr_utility.set_location(' p_actn_typ_id ' || p_actn_typ_id , 1999);
1169: hr_utility.set_location(' p_cm_typ_id ' || p_cm_typ_id , 1999);
1170: hr_utility.set_location(' comm_start_date'||ben_generate_communications.g_comm_start_date , 1999);
1171: hr_utility.set_location(' p_ler_id ' || p_ler_id , 1999);
1172: hr_utility.set_location(' p_pgm_id ' || p_pgm_id , 1999);
1173: hr_utility.set_location(' p_pl_id ' || p_pl_id , 1999);
1174:
1343: ) */
1344: )
1345: and (p_pl_typ_id is null or
1346: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
1347: and ben_generate_communications.g_comm_start_date
1348: between ctu.effective_start_date
1349: and ctu.effective_end_date
1350: and ctu.cm_typ_id = p_cm_typ_id
1351: and ctu.all_r_any_cd = 'ALL'
1608: (c_pil_id => p_per_in_ler_id
1609: ,c_bgp_id => p_business_group_id
1610: ,c_pl_typ_id => p_pl_typ_id
1611: ,c_asnd_lf_evt_dt => p_asnd_lf_evt_dt
1612: ,c_comm_st_date => ben_generate_communications.g_comm_start_date
1613: ,c_cm_typ_id => p_cm_typ_id
1614: );
1615: fetch c1 BULK COLLECT INTO l_ctu_cm_typ_usg_id_va,
1616: l_ctu_cm_usg_rl_va,
1775: from ben_elig_per_elctbl_chc epe
1776: where epe.business_group_id = p_business_group_id
1777: and epe.elctbl_flag = 'Y'
1778: and epe.per_in_ler_id = p_per_in_ler_id)
1779: and ben_generate_communications.g_comm_start_date
1780: between ctu.effective_start_date
1781: and ctu.effective_end_date
1782: /* This process code can only apply to ALL since one context only
1783: can be set, we code for both though just in case */
1950: enp_m.business_group_id=ctu.business_group_id and
1951: enp_m.strt_dt=enp_c.strt_dt
1952: ) */
1953: )
1954: and ben_generate_communications.g_comm_start_date
1955: between ctu.effective_start_date
1956: and ctu.effective_end_date
1957: and ctu.cm_typ_id = p_cm_typ_id
1958: and ctu.all_r_any_cd = 'ALL'
2130: and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2131: and pel.pgm_id is null)
2132: or nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)))
2133: or (ctu.enrt_perd_id is null))
2134: and ben_generate_communications.g_comm_start_date
2135: between ctu.effective_start_date
2136: and ctu.effective_end_date
2137: and ctu.cm_typ_id = p_cm_typ_id
2138: and ctu.all_r_any_cd = 'ALL'
2285: and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2286: and pel.pgm_id is null)
2287: or nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)))
2288: or (ctu.enrt_perd_id is null))
2289: and ben_generate_communications.g_comm_start_date
2290: between ctu.effective_start_date
2291: and ctu.effective_end_date
2292: and ctu.cm_typ_id = p_cm_typ_id
2293: and ctu.all_r_any_cd = 'ALL'
2426: and (p_pl_typ_id is null or
2427: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2428: and (p_actn_typ_id is null or
2429: nvl(ctu.actn_typ_id,p_actn_typ_id) = p_actn_typ_id)
2430: and ben_generate_communications.g_comm_start_date
2431: between ctu.effective_start_date
2432: and ctu.effective_end_date
2433: and ctu.cm_typ_id = p_cm_typ_id
2434: and ctu.all_r_any_cd = 'ALL';
2543: and (p_pl_id is null or
2544: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2545: and (p_pl_typ_id is null or
2546: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2547: and ben_generate_communications.g_comm_start_date
2548: between ctu.effective_start_date
2549: and ctu.effective_end_date
2550: and ctu.cm_typ_id = p_cm_typ_id
2551: and ctu.all_r_any_cd = 'ALL';
2569: hr_utility.set_location('PL: '||p_pl_id,10);
2570: hr_utility.set_location('ler : '||p_ler_id,10);
2571: hr_utility.set_location('pgm: '|| p_pgm_id,10);
2572: hr_utility.set_location('pl_typ: '||p_pl_typ_id,10);
2573: hr_utility.set_location('comm date : '||ben_generate_communications.g_comm_start_date,10);
2574: hr_utility.set_location('comm type : '||p_cm_typ_id,10);
2575:
2576: --
2577: l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2664: and (p_pl_id is null or
2665: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2666: and (p_pl_typ_id is null or
2667: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2668: and ben_generate_communications.g_comm_start_date
2669: between ctu.effective_start_date
2670: and ctu.effective_end_date
2671: and ctu.cm_typ_id = p_cm_typ_id
2672: and ctu.all_r_any_cd = 'ALL';
2778: and (p_pl_id is null or
2779: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2780: and (p_pl_typ_id is null or
2781: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2782: and ben_generate_communications.g_comm_start_date
2783: between ctu.effective_start_date
2784: and ctu.effective_end_date
2785: and ctu.cm_typ_id = p_cm_typ_id
2786: and ctu.all_r_any_cd = 'ALL';
2916: and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
2917: = nvl(pel.enrt_perd_id, -1)
2918: and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2919: and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
2920: and ben_generate_communications.g_comm_start_date
2921: between ctu.effective_start_date
2922: and ctu.effective_end_date
2923: and ctu.cm_typ_id = p_cm_typ_id
2924: and ctu.all_r_any_cd = 'ALL'
3051: and (p_pgm_id is null or
3052: nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3053: and (p_pl_typ_id is null or
3054: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3055: and ben_generate_communications.g_comm_start_date
3056: between ctu.effective_start_date
3057: and ctu.effective_end_date
3058: and ctu.cm_typ_id = p_cm_typ_id
3059: and ctu.all_r_any_cd = 'ALL';
3204: enp_m.business_group_id=ctu.business_group_id and
3205: enp_m.strt_dt=enp_c.strt_dt
3206: ) */
3207: )
3208: and ben_generate_communications.g_comm_start_date
3209: between ctu.effective_start_date
3210: and ctu.effective_end_date
3211: and ctu.cm_typ_id = p_cm_typ_id
3212: and ctu.all_r_any_cd = 'ALL'
3372: enp_m.business_group_id=ctu.business_group_id and
3373: enp_m.strt_dt=enp_c.strt_dt
3374: ) */
3375: )
3376: and ben_generate_communications.g_comm_start_date
3377: between ctu.effective_start_date
3378: and ctu.effective_end_date
3379: and ctu.cm_typ_id = p_cm_typ_id
3380: and ctu.all_r_any_cd = 'ALL';
4634: --
4635: hr_utility.set_location('Displaying stats ',10);
4636: end main;
4637: --
4638: end ben_generate_communications;
4639: --