[Home] [Help]
209: ,c_comm_sdt date
210: )
211: is
212: select pcm.per_cm_id
213: from ben_per_cm_f pcm,ben_per_cm_prvdd_f pcpf
214: where pcm.per_in_ler_id = c_pil_id
215: -- if commu table has no ler id dont compare , pil_id take care
216: -- of validation # 3296015
217: and (pcm.ler_id is null or pcm.ler_id = c_ler_id)
222: and nvl(pcm.cm_typ_id,-1) = nvl(c_cm_typ_id,-1)
223: and pcm.per_cm_id = pcpf.per_cm_id
224: and pcpf.per_cm_prvdd_stat_cd <> 'VOID'
225: -- Bug :7629124, Check to see if Communication is VOIDed.If VOIDed, then create a new Communication for the same LE
226: -- Added ben_per_cm_prvdd_f table to the cursor for verification
227: and nvl(pcm.lf_evt_ocrd_dt,nvl(c_leo_dt,c_eff_dt)) =
228: nvl(c_leo_dt,c_eff_dt)
229: and c_comm_sdt
230: between pcm.effective_start_date and pcm.effective_end_date;
710: hr_utility.set_location('Leaving: '||l_proc,10);
711: --
712: end pop_ben_per_cm_trgr_f;
713: --
714: procedure pop_ben_per_cm_prvdd_f(p_per_cm_id in number,
715: p_effective_date in date,
716: p_rqstd_flag in varchar2,
717: p_inspn_rqd_flag in varchar2,
718: p_per_cm_prvdd_stat_cd in varchar2,
724: p_address_id in number,
725: p_business_group_id in number,
726: p_per_cm_prvdd_id out nocopy number) is
727: --
728: l_proc varchar2(80) := g_package||'pop_ben_per_cm_prvdd_f';
729: --
730: -- Output variables
731: --
732: l_object_version_number number;
734: l_effective_end_date date;
735: --
736: cursor c_per_cm_prvdd is
737: select pcd.*
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
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
837: end if;
838: --
839: hr_utility.set_location('Leaving: '||l_proc,10);
840: --
841: end pop_ben_per_cm_prvdd_f;
842: --
843: procedure pop_ben_per_cm_usg_f
844: (p_per_cm_id in number,
845: p_cm_typ_usg_id in number,
955: p_business_group_id => p_business_group_id,
956: p_effective_date => p_effective_date,
957: p_per_cm_trgr_id => l_per_cm_trgr_id);
958: --
959: pop_ben_per_cm_prvdd_f
960: (p_per_cm_id => p_per_cm_id,
961: p_rqstd_flag => 'N',
962: p_inspn_rqd_flag => p_inspn_rqd_flag,
963: p_per_cm_prvdd_stat_cd => 'ACTIVE',
3851: -- 3717297 the hipaa communication called from benmngle and close enrollment
3852: -- to avoid the dups
3853: cursor c_pcd is
3854: select 'x'
3855: from ben_per_cm_prvdd_f
3856: where per_cm_id = p_per_cm_id
3857: --
3858: -- Bug No: 3752029
3859: -- Commented out this condition since it was only allowing selecting records for which we
4740: -- 4. If rule exists and fails then look at next record
4741: -- 5. If whnvr_trgrd_flag = 'Y' then do for all comp objects
4742: -- 4a. Populate ben_cm_trgr_f
4743: -- 4b. Populate ben_per_cm_f
4744: -- 4b. Populate ben_per_cm_prvdd_f
4745: -- 6. If whnvr_trgrd_flag = 'N' then join to cm_typ_usg_f
4746: -- 7. If rule exists or no rule and a usage exists
4747: -- 6a. Populate ben_per_cm_trgr_f
4748: -- 6b. Populate ben_per_cm_usg_f
4746: -- 7. If rule exists or no rule and a usage exists
4747: -- 6a. Populate ben_per_cm_trgr_f
4748: -- 6b. Populate ben_per_cm_usg_f
4749: -- 6c. Populate ben_per_cm_f
4750: -- 6d. Populate ben_per_cm_prvdd_f
4751: -- 8. Go to 2.
4752: --
4753: -- Step 1.
4754: --