[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_PREM
Source
1 package body BEN_EXT_PREM as
2 /* $Header: benxprem.pkb 115.5 2003/02/10 11:23:46 rpgupta ship $*/
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ext_prem.'; -- Global package name
9 --
10 -- procedure to initialize the globals -
11 -- ----------------------------------------------------------------------------
12 -- |---------------------< initialize_globals >-------------------------------------|
13 -- ----------------------------------------------------------------------------
14 PROCEDURE initialize_globals IS
15 --
16 l_proc varchar2(72) := g_package||'initialize_globals';
17 --
18 BEGIN
19 --
20 hr_utility.set_location('Entering'||l_proc, 5);
21 --
22 ben_ext_person.g_prem_actl_prem_id := null;
23 ben_ext_person.g_prem_mn_amt := null;
24 ben_ext_person.g_prem_mn_uom := null;
25 ben_ext_person.g_prem_mn_mnl_adj := null;
26 ben_ext_person.g_prem_mn_cr_mnl_adj := null;
27 ben_ext_person.g_prem_mn_cramt := null;
28 ben_ext_person.g_prem_mn_costalloc_name := null;
29 ben_ext_person.g_prem_mn_costalloc_id := null;
30 ben_ext_person.g_prem_mn_costalloc_flex_01 := null;
31 ben_ext_person.g_prem_mn_costalloc_flex_02 := null;
32 ben_ext_person.g_prem_mn_costalloc_flex_03 := null;
33 ben_ext_person.g_prem_month := null;
34 ben_ext_person.g_prem_year := null;
35 ben_ext_person.g_prtt_prem_by_mo_id := null;
36 ben_ext_person.g_prem_last_upd_date := null ;
37
38 hr_utility.set_location('Exiting'||l_proc, 15);
39 --
40 End initialize_globals;
41
42 -- ----------------------------------------------------------------------------
43 -- |---------------------< premium_total >---------------------------------------------|
44 -- ----------------------------------------------------------------------------
45 PROCEDURE premium_total
46 ( p_person_id in number,
47 p_prtt_enrt_rslt_id in number,
48 p_ext_rslt_id in number,
49 p_ext_file_id in number,
50 p_data_typ_cd in varchar2,
51 p_ext_typ_cd in varchar2,
52 p_chg_evt_cd in varchar2,
53 p_business_group_id in number,
54 P_effective_date in date ) is
55 --
56 l_proc varchar2(72) := g_package||'premium_total';
57 --
58 cursor c_mn_prem(l_prtt_enrt_rslt_id number) is
59 select prm.val
60 ,prm.cr_val
61 ,prm.yr_num
62 ,prm.mo_num
63 ,prm.last_update_date
64 from ben_prtt_prem_by_mo_f prm
65 , ben_prtt_prem_f ppe
66 where ppe.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
67 and ppe.prtt_prem_id = prm.prtt_prem_id
68 and p_effective_date between prm.effective_start_date and prm.effective_end_date
69 and p_effective_date between ppe.effective_start_date and ppe.effective_end_date ;
70 l_include varchar2(1) ;
71 l_tot_val ben_prtt_prem_by_mo_f.val%type ;
72 l_tot_cr_val ben_prtt_prem_by_mo_f.cr_val%type ;
73 --
74 BEGIN
75 --
76 --
77 hr_utility.set_location('Entering'||l_proc, 5);
78 --
79 -- Monthly Premium Informations
80 ben_ext_person.g_enrt_mntot_prem_amt := null ;
81 ben_ext_person.g_enrt_mntot_prem_cramt := null ;
82
83 --
84 FOR prem IN c_mn_prem(p_prtt_enrt_rslt_id) LOOP
85 --
86
87 hr_utility.set_location('month'|| prem.mo_num,5381);
88 hr_utility.set_location('year' || prem.yr_num,5381);
89 ---validating the month and year and update_date
90 ben_ext_evaluate_inclusion.evaluate_prem_incl
91 (p_last_update_date => prem.last_update_date,
92 p_mo_num => prem.mo_num ,
93 p_yr_num => prem.yr_num ,
94 p_effective_date => p_effective_date,
95 p_include => l_include) ;
96
97 if l_include = 'Y' then
98 l_tot_val := nvl(l_tot_val,0)+nvl(prem.val,0) ;
99 l_tot_cr_val := nvl(l_tot_cr_val,0)+nvl(prem.cr_val,0) ;
100
101 end if ;
102 --
103 END LOOP;
104 ben_ext_person.g_enrt_mntot_prem_amt := l_tot_val ;
105 ben_ext_person.g_enrt_mntot_prem_cramt := l_tot_cr_val ;
106 --
107 hr_utility.set_location('Exiting'||l_proc, 15);
108
109 END; -- premium_total
110
111
112
113 --
114 -- ----------------------------------------------------------------------------
115 -- |---------------------< main >---------------------------------------------|
116 -- ----------------------------------------------------------------------------
117 PROCEDURE main
118 ( p_person_id in number,
119 p_prtt_enrt_rslt_id in number,
120 p_ext_rslt_id in number,
121 p_ext_file_id in number,
122 p_data_typ_cd in varchar2,
123 p_ext_typ_cd in varchar2,
124 p_chg_evt_cd in varchar2,
125 p_business_group_id in number,
126 P_effective_date in date ) is
127 --
128 l_proc varchar2(72) := g_package||'main';
129 --
130 cursor c_mn_prem(l_prtt_enrt_rslt_id number) is
131 select apr.actl_prem_id
132 ,prm.val
133 ,prm.uom
134 ,prm.cr_val
135 ,prm.mnl_adj_flag
136 ,prm.cr_mnl_adj_flag
137 ,prm.yr_num
138 ,prm.mo_num
139 ,prm.last_update_date
140 ,csk.concatenated_segments
141 ,csk.cost_allocation_keyflex_id
142 ,csk.segment1
143 ,csk.segment2
144 ,csk.segment3
145 ,apr.actl_prem_typ_cd
146 ,prm.prtt_prem_by_mo_id
147 from ben_prtt_prem_by_mo_f prm
148 , ben_prtt_prem_f ppe
149 , ben_actl_prem_f apr
150 , pay_cost_allocation_keyflex csk
151 where ppe.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
152 and ppe.prtt_prem_id = prm.prtt_prem_id
153 and ppe.actl_prem_id = apr.actl_prem_id
154 and prm.cost_allocation_keyflex_id = csk.cost_allocation_keyflex_id(+)
155 --and prm.yr_num = to_number(to_char(p_effective_date,'YYYY'))
156 --and prm.mo_num = to_number(to_char(p_effective_date,'MM'))
157 and p_effective_date between prm.effective_start_date and prm.effective_end_date
158 and p_effective_date between ppe.effective_start_date and ppe.effective_end_date
159 and p_effective_date between apr.effective_start_date and apr.effective_end_date ;
160 l_include varchar2(1) ;
161
162 --
163 BEGIN
164 --
165 hr_utility.set_location('Entering'||l_proc, 5);
166 --
167 -- Monthly Premium Informations
168 hr_utility.set_location('result idd' || p_prtt_enrt_rslt_id,5380);
169 --
170 FOR prem IN c_mn_prem(p_prtt_enrt_rslt_id) LOOP
171 --
172 hr_utility.set_location(' prtt prrem id' || prem.prtt_prem_by_mo_id,5380);
173 initialize_globals;
174 -- fetch premium information into globals
175 --
176 --l_include := 'Y';
177 --if p_ext_crit_prfl_id is not null then
178 ben_ext_evaluate_inclusion.evaluate_prem_incl
179 (p_last_update_date => prem.last_update_date,
180 p_mo_num => prem.mo_num ,
181 p_yr_num => prem.yr_num ,
182 p_effective_date => p_effective_date,
183 p_include => l_include) ;
184 --end if ;
185 if l_include = 'Y' then
186 --
187 hr_utility.set_location(' month ' || prem.mo_num , 119);
188 hr_utility.set_location(' year ' || prem.yr_num , 119);
189
190 ben_ext_person.g_prem_actl_prem_id := prem.actl_prem_id;
191 ben_ext_person.g_prem_mn_amt := prem.val;
192 ben_ext_person.g_prem_mn_uom := prem.uom;
193 ben_ext_person.g_prem_month := prem.mo_num;
194 ben_ext_person.g_prem_year := prem.yr_num;
195 ben_ext_person.g_prem_mn_cramt := prem.cr_val;
196 ben_ext_person.g_prem_mn_mnl_adj := prem.mnl_adj_flag;
197 ben_ext_person.g_prem_mn_cr_mnl_adj := prem.cr_mnl_adj_flag;
198 ben_ext_person.g_prem_mn_costalloc_name := prem.concatenated_segments;
199 ben_ext_person.g_prem_mn_costalloc_id := prem.cost_allocation_keyflex_id;
200 ben_ext_person.g_prem_mn_costalloc_flex_01 := prem.segment1;
201 ben_ext_person.g_prem_mn_costalloc_flex_02 := prem.segment2;
202 ben_ext_person.g_prem_mn_costalloc_flex_03 := prem.segment3;
203 ben_ext_person.g_prem_type := prem.actl_prem_typ_cd;
204 ben_ext_person.g_prtt_prem_by_mo_id := prem.prtt_prem_by_mo_id;
205 ben_ext_person.g_prem_last_upd_date := prem.last_update_date;
206 --
207 hr_utility.set_location(' month ' || ben_ext_person.g_prem_month , 119);
208 hr_utility.set_location(' year ' || ben_ext_person.g_prem_year , 119);
209 -- format and write
210 --
211 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
212 p_ext_file_id => p_ext_file_id,
213 p_data_typ_cd => p_data_typ_cd,
214 p_ext_typ_cd => p_ext_typ_cd,
215 p_rcd_typ_cd => 'D',
216 p_low_lvl_cd => 'PR',
217 p_person_id => p_person_id,
218 p_chg_evt_cd => p_chg_evt_cd,
219 p_business_group_id => p_business_group_id,
220 p_effective_date => p_effective_date
221 );
222 end if ;
223 --
224 END LOOP;
225 --
226 hr_utility.set_location('Exiting'||l_proc, 15);
227
228 END; -- main
229 --
230 END; -- package