[Home] [Help]
PACKAGE BODY: APPS.BEN_RECN_REP
Source
1 Package body ben_recn_rep as
2 /* $Header: bercnrep.pkb 120.0 2005/05/28 11:36:03 appldev noship $ */
3 /* ===========================================================================
4 * Name:
5 * ben_recn_rep
6 * Purpose:
7 * This package writes the Reconciliation of Premium to Element Entries
8 * Report in CSV format. There are procedures in this package which
9 * can be used to write to a file.
10 * History:
11 * Date Who Version What?
12 * ----------- --------- ------- -----------------------------------------
13 * 20-Jan-2003 vsethi 115.0 Created.
14 * 22-Jan-2003 vsethi 115.1 Modified after unit testing
15 * 23-Jan-2003 vsethi 115.3 Plan Disc 'Total' not displayed
16 * 28-Jan-2003 vsethi 115.4 Payroll Name not displayed
17 * Person Selection rule failing as seesion
18 * info is not present in fnd_sessions
19 * 28-Jan-2003 vsethi 115.5 In reconciliation and discrepancy sections
20 * added a clause to restrict enrt records
21 * whose cvg begins before the report end date.
22 * 30-Jan-2003 vsethi 115.6 Life event name not getting diplayed
23 * 07-Feb-2003 vsethi 115.7 Removed the p_run_date and p_mon_year parameters
24 * 19-Feb-2003 vsethi 115.8 2791345 - For person enrolled in multiple options
25 * premium is not displayed for the second record.
26 * 30-Jun-03 vsethi 115.9 Changed reference for table ben_rptg_grp
27 * MLS compliant view ben_rptg_grp_v
28 * 18-May-04 rpgupta 115.10 3608119 - picks up sum of all rates
29 * when an unerstricted enrollment is
30 * done, rates are changed and recalculate
31 * participant rates is run
32 * 08-Jun-04 rpgupta 115.11 3608119 - added date check in FUNCTION
33 * get_prtt_rate_val
34 * 20-Jul-04 nhunur 115.12 3775260 - Report should not be restricted to Employees.
35 * and also look at ERPYC acty typ rates in get_rate_val and get_element_val.
36 * 5-apr-05 nhunur 115.13 Use pds.end_date for queries to pickup rate / element values properly
37 * if rate changes midway thru pay period.
38 * 19-May-05 rbingi 115.15 Bug-4383835 Removed to_date for p_report_start_date because its
39 * already of type date. part of GSCC FireDrill
40 * ===========================================================================
41 */
42 --
43 -- Package Variables
44 --
45 f_out utl_file.file_type ;
46
47 --
48 -- ============================================================================
49 -- <<open_log_file>>
50 -- ============================================================================
51 --
52 procedure open_log_file(p_log_file_name in out nocopy varchar2 )
53 as
54 l_output_log_file varchar2(250) ;
55 l_audit_log_dir varchar2(250);
56 l_temp varchar2(5) ;
57 l_temp_num number(5) ;
58 --
59 begin
60 -- Get log file name using fnd routines
61 if p_log_file_name is null then
62 --
63 fnd_file.get_names(P_LOG => g_log_file_name,
64 P_OUT => l_output_log_file);
65 --g_log_file_name := replace(g_log_file_name,'.tmp','.csv');
66 --
67 else
68 g_log_file_name := p_log_file_name;
69 end if;
70 --
71 -- Get utl file dir from v$parameters change this query for only one dir
72 -- in utl_file_dir
73 --
74 select decode(instr(value,','),0,value,
75 substrb(translate(ltrim(value),',',' '),
76 1,
77 instr(translate(ltrim(value),',',' '),' ') - 1))
78 into l_audit_log_dir
79 from v$parameter
80 where name = 'utl_file_dir';
81
82 -- Open the log File using FND routines
83 f_out := utl_file.fopen(l_audit_log_dir,g_log_file_name,'w',32767);
84 --
85
86 -- Form the Audit Log File Name
87 -- Get the file separator for the platform
88
89 -- Check for a / in audit log dir
90 -- If a / is found then use / as file seperator (unix)
91 -- else use \ ( for win nt
92 l_temp_num := instr(l_audit_log_dir,'/');
93
94 if l_temp_num <=0 then
95 l_temp :='\' ;
96 else
97 l_temp :='/' ;
98 end if;
99
100
101 g_log_file_name := l_audit_log_dir || l_temp || g_log_file_name ;
102 p_log_file_name := g_log_file_name ;
103 --
104 exception
105 when others then
106 raise;
107 end open_log_file ;
108 --
109 -- ============================================================================
110 -- <<put_line>>
111 -- ============================================================================
112 --
113 procedure put_line(p_message in varchar2) as
114 begin
115 -- do logging only if log flag is true and severity level is
116 -- greater than current level
117 /*fnd_file.put_line(
118 WHICH =>FND_FILE.LOG ,
119 BUFF =>p_message );*/
120 utl_file.put_line(f_out,p_message);
121 utl_file.fflush(f_out);
122 end put_line ;
123 --
124 -- ============================================================================
125 -- <<close_log_file>>
126 -- ============================================================================
127 --
128 procedure close_log_file as
129 begin
130 utl_file.fclose(f_out) ;
131 g_log_file_name := null;
132 end close_log_file;
133 --
134 -- ============================================================================
135 -- <<print_report>>
136 -- ============================================================================
137 --
138 procedure print_report(p_log_file_name IN OUT nocopy varchar2,
139 p_report_array IN ben_recn_rep.g_report_array ,
140 p_close_file IN boolean default TRUE )
141 as
142 l_record varchar2(10000);
143 --
144 begin
145 -- open the file
146 if g_log_file_name is null then
147 open_log_file(p_log_file_name);
148 else
149 p_log_file_name := g_log_file_name;
150 end if;
151 --
152 for l_array_count in p_report_array.first..p_report_array.last loop
153 -- loop through the array and print the output
154
155 l_record := '"'||p_report_array(l_array_count).col1||'",'||
156 '"'||p_report_array(l_array_count).col2||'",'||
157 '"'||p_report_array(l_array_count).col3||'",'||
158 '"'||p_report_array(l_array_count).col4||'",'||
159 '"'||p_report_array(l_array_count).col5||'",'||
160 '"'||p_report_array(l_array_count).col6||'",'||
161 '"'||p_report_array(l_array_count).col7||'",'||
162 '"'||p_report_array(l_array_count).col8||'",'||
163 '"'||p_report_array(l_array_count).col9||'",'||
164 '"'||p_report_array(l_array_count).col10||'",'||
165 '"'||p_report_array(l_array_count).col11||'",'||
166 '"'||p_report_array(l_array_count).col12||'",'||
167 '"'||p_report_array(l_array_count).col13||'",'||
168 '"'||p_report_array(l_array_count).col14||'",'||
169 '"'||p_report_array(l_array_count).col15||'",'||
170 '"'||p_report_array(l_array_count).col16||'",'||
171 '"'||p_report_array(l_array_count).col17||'",'||
172 '"'||p_report_array(l_array_count).col18||'",'||
173 '"'||p_report_array(l_array_count).col19||'",'||
174 '"'||p_report_array(l_array_count).col20 ||'"';
175 --
176 put_line(l_record);
177 l_record := null;
178 end loop;
179 put_line('End Of Section');
180 --
181 -- close the file
182 if p_close_file then
183 close_log_file;
184 end if;
185 --
186 exception
187 when others then
188 close_log_file;
189 raise;
190 end print_report;
191 --
192 -- ============================================================================
193 -- <<recon_report>>
194 -- ============================================================================
195 --
196 procedure recon_report
197 (p_pl_id number,
198 p_pgm_id number,
199 p_person_id number,
200 p_per_sel_rule number,
201 p_business_group_id number,
202 p_benefit_action_id number,
203 p_organization_id number,
204 p_location_id number,
205 p_ntl_identifier varchar2,
206 p_rptg_grp_id number,
207 p_benfts_grp_id number,
208 p_run_date date,
209 p_report_start_date date,
210 p_report_end_date date,
211 p_prem_type varchar2,
212 p_payroll_id number,
213 p_dsply_pl_disc_rep varchar2,
214 p_dsply_pl_recn_rep varchar2,
215 p_dsply_pl_prtt_rep varchar2,
216 p_dsply_prtt_reps varchar2,
217 p_dsply_lfe_rep varchar2,
218 p_emp_name_format varchar2,
219 p_conc_request_id number,
220 p_rep_st_dt date, -- original rep start date as submitted in the concurrent request
221 p_rep_end_dt date, -- original rep start date as submitted in the concurrent request
222 p_dsply_recn varchar2,
223 p_dsply_disc varchar2,
224 p_dsply_lfe varchar2,
225 p_dsply_pl_prtt varchar2,
226 p_output_typ varchar2,
227 p_op_file_name IN OUT nocopy varchar2
228 ) is
229
230 --
231
232 cursor c_plan_recn is
233 select pl_oraganization_name pl_org_name,
234 pl_location_name pl_loc_name,
235 pl_payroll_name pl_pay_name,
236 pl_full_name pl_per_name,
237 pl_national_identifier pl_ntnl_id,
238 pl_bnft_amount pl_bnft_amt,
239 pl_prem_val pl_prem_val,
240 pl_sql_uom pl_uom,
241 pl_period_type pl_perd_typ,
242 sum(ee_ptax_rt_val) ee_ptax_rt_val_tot,
243 sum(ee_atax_rt_val) ee_atax_rt_val_tot,
244 sum(er_rt_val ) er_rt_val_tot,
245 sum(ee_ptax_rt_val + ee_atax_rt_val + er_rt_val) pay_perd_total,
246 sum(ee_ptax_elem_val) ee_ptax_elem_val_tot,
247 sum(ee_atax_elem_val) ee_atax_elem_val_tot,
248 sum(er_elem_val ) er_elem_val_tot ,
249 sum(ee_ptax_elem_val + ee_atax_elem_val + er_elem_val) actual_total
250 from (select distinct hr_general.decode_organization(asg.organization_id) pl_oraganization_name
251 ,hr_general.decode_location(asg.location_id) pl_location_name
252 ,pay.payroll_name pl_payroll_name
253 ,decode(p_emp_name_format,'JP',( per.last_name || ' ' || per.first_name || ' / ' || -- the japanese name format should be kept in sync with hrjputil.pkb
254 per.per_information18 || ' ' || per.per_information19)
255 , per.full_name) pl_full_name
256 ,per.national_identifier pl_national_identifier
257 ,pen.bnft_amt pl_bnft_amount
258 ,mpr.val pl_prem_val
259 ,nvl(pen.uom, mpr.uom) pl_sql_uom
260 ,pay.period_type pl_period_type
261 ,pds.period_name pl_period_name
262 ,to_char(pds.start_date,'MM/DD')|| ' - '|| to_char(pds.end_date,'MM/DD') pl_pay_prd
263 ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,'PRETAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_ptax_rt_val
264 ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,'AFTERTAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_atax_rt_val
265 ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,null,'ERC',pen.per_in_ler_id, pds.end_date) er_rt_val
266 ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,'PRETAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_ptax_elem_val
267 ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,'AFTERTAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_atax_elem_val
268 ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,null,'ERC',pen.per_in_ler_id, pds.end_date) er_elem_val
269 from ben_prtt_enrt_rslt_f pen
270 ,ben_actl_prem_f acp
271 ,ben_prtt_prem_f prm
272 ,ben_prtt_prem_by_mo_f mpr
273 ,ben_per_in_ler pil
274 ,per_all_people_f per
275 ,per_person_types ptp
276 ,pay_all_payrolls_f pay
277 ,per_time_periods pds
278 ,per_all_assignments_f asg
279 where pen.pl_id = p_pl_id
280 and (pen.pgm_id = p_pgm_id or p_pgm_id is null)
281 and pen.prtt_enrt_rslt_stat_cd is null
282 and pen.business_group_id = p_business_group_id
283 and pen.enrt_cvg_thru_dt >= pen.effective_end_date
284 and (p_report_start_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
285 or p_report_end_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
286 or (p_report_start_date >= pen.enrt_cvg_strt_dt and p_report_end_date <= pen.enrt_cvg_thru_dt)
287 or (p_report_start_date <= pen.enrt_cvg_strt_dt and p_report_end_date >= pen.enrt_cvg_thru_dt))
288 and ((pen.effective_end_date < pen.enrt_cvg_thru_dt
289 and (p_report_start_date between pen.effective_start_date and pen.effective_end_date
290 or p_report_end_date between pen.effective_start_date and pen.effective_end_date
291 or (p_report_start_date >= pen.effective_start_date and p_report_end_date <= pen.effective_end_date)
292 or (p_report_start_date <= pen.effective_start_date and p_report_end_date >= pen.effective_end_date)))
293 or pen.effective_end_date >= pen.enrt_cvg_thru_dt )
294 and pen.sspndd_flag = 'N'
295 and pen.per_in_ler_id = pil.per_in_ler_id
296 and pil.per_in_ler_stat_cd not in ( 'VOIDD' , 'BCKDT')
297 and pil.person_id = per.person_id
298 and ptp.person_type_id = per.person_type_id
299 and ptp.system_person_type in ( 'EMP' , 'EX_EMP' , 'EX_EMP_APL' , 'EMP_APL' , 'PRTN' )
300 and (p_person_id is null or pen.person_id = p_person_id)
301 and (p_per_sel_rule is null or pen.person_id in (select person_id
302 from ben_person_actions pac
303 where pac.benefit_action_id = p_benefit_action_id) )
304 and (p_ntl_identifier is null or per.national_identifier = p_ntl_identifier)
305 and pen.prtt_enrt_rslt_id = prm.prtt_enrt_rslt_id (+)
306 and mpr.prtt_prem_id(+) = prm.prtt_prem_id
307 and mpr.yr_num(+) = to_number(to_char(p_report_start_date,'YYYY'))
308 and mpr.mo_num(+) = to_number(to_char(p_report_start_date,'MM'))
309 and prm.per_in_ler_id (+) = pen.per_in_ler_id
310 and acp.actl_prem_id (+) = prm.actl_prem_id
311 and (p_prem_type is null or acp.prsptv_r_rtsptv_cd = p_prem_type )
312 and pen.person_id = asg.person_id
313 and pen.business_group_id = p_business_group_id
314 and asg.business_group_id = p_business_group_id
315 /* and asg.assignment_type = 'E' */
316 and asg.primary_flag = 'Y'
317 and (p_payroll_id is null or asg.payroll_id = p_payroll_id )
318 and asg.payroll_id = pay.payroll_id
319 and pds.payroll_id = pay.payroll_id
320 and pds.start_date >= p_report_start_date
321 and pds.end_date <= p_report_end_date
322 and per.business_group_id = p_business_group_id
323 and p_run_date between per.effective_start_date and per.effective_end_date
324 and p_run_date between asg.effective_start_date and asg.effective_end_date
325 and p_run_date between pay.effective_start_date and pay.effective_end_date
326 and p_run_date between acp.effective_start_date (+) and acp.effective_end_date (+)
327 and p_run_date >= prm.effective_start_date (+)
328 and p_run_date <= prm.effective_end_date (+)
329 and (p_organization_id is null
330 or asg.organization_id = p_organization_id )
331 and (p_location_id is null
332 or asg.location_id = p_location_id)
333 and (p_benfts_grp_id is null
334 or per.benefit_group_id = p_benfts_grp_id)
335 and (p_rptg_grp_id is null
336 or exists (select null
337 from ben_popl_rptg_grp_f prpg
338 where (pen.pl_id = prpg.pl_id
339 or pen.pgm_id = prpg.pgm_id )
340 and prpg.rptg_grp_id = p_rptg_grp_id))
341 ) pl
342 group by pl_oraganization_name, pl_location_name, pl_payroll_name ,
343 pl_full_name, pl_national_identifier, pl_bnft_amount , pl_prem_val, pl_sql_uom, pl_period_type
344 order by 1,2,3,4;
345 --
346 cursor c_plan_disc is
347 select pl.*
348 ,ee_ptax_rt_val + ee_atax_rt_val + er_rt_val pay_perd_total
349 ,ee_ptax_elem_val + ee_atax_elem_val + er_elem_val actual_total
350 ,((ee_ptax_elem_val + ee_atax_elem_val + er_elem_val) -
351 (ee_ptax_rt_val + ee_atax_rt_val + er_rt_val)) std_rt_dis
352 ,nvl(((ee_ptax_elem_val + ee_atax_elem_val + er_elem_val ) - pl_prem_val),0) prem_dis
353 from (select distinct hr_general.decode_organization(asg.organization_id) pl_oraganization_name
354 ,hr_general.decode_location(asg.location_id) pl_location_name
355 ,pay.payroll_name pl_payroll_name
356 ,decode(p_emp_name_format,'JP',( per.last_name || ' ' || per.first_name || ' / ' || -- the japanese name format should be kept in sync with hrjputil.pkb
357 per.per_information18 || ' ' || per.per_information19)
358 , per.full_name) pl_full_name
359 ,per.person_id pl_person_id
360 ,per.national_identifier pl_national_id
361 ,pen.bnft_amt pl_bnft_amount
362 ,mpr.val pl_prem_val
363 ,nvl(pen.uom,mpr.uom) pl_sql_uom
364 ,pay.period_type pl_period_type
365 ,pds.period_name pl_period_name
366 ,to_char(pds.start_date,'MM/DD')|| ' - '|| to_char(pds.end_date,'MM/DD') pl_pay_prd
367 ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,'PRETAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_ptax_rt_val
368 ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,'AFTERTAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_atax_rt_val
369 ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,null,'ERC',pen.per_in_ler_id, pds.end_date) er_rt_val
370 ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,'PRETAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_ptax_elem_val
371 ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,'AFTERTAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_atax_elem_val
372 ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,p_business_group_id,null,'ERC',pen.per_in_ler_id, pds.end_date) er_elem_val
373 from ben_prtt_enrt_rslt_f pen
374 ,ben_actl_prem_f acp
375 ,ben_prtt_prem_f prm
376 ,ben_prtt_prem_by_mo_f mpr
377 ,ben_per_in_ler pil
378 ,per_all_people_f per
379 ,per_person_types ptp
380 ,pay_all_payrolls_f pay
381 ,per_time_periods pds
382 ,per_all_assignments_f asg
383 where pen.pl_id = p_pl_id
384 and (pen.pgm_id = p_pgm_id or p_pgm_id is null)
385 and pen.prtt_enrt_rslt_stat_cd is null
386 and pen.business_group_id = p_business_group_id
387 and pen.enrt_cvg_thru_dt >= pen.effective_end_date
388 and (p_report_start_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
389 or p_report_end_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
390 or (p_report_start_date >= pen.enrt_cvg_strt_dt and p_report_end_date <= pen.enrt_cvg_thru_dt)
391 or (p_report_start_date <= pen.enrt_cvg_strt_dt and p_report_end_date >= pen.enrt_cvg_thru_dt))
392 and ((pen.effective_end_date < pen.enrt_cvg_thru_dt
393 and (p_report_start_date between pen.effective_start_date and pen.effective_end_date
394 or p_report_end_date between pen.effective_start_date and pen.effective_end_date
395 or (p_report_start_date >= pen.effective_start_date and p_report_end_date <= pen.effective_end_date)
396 or (p_report_start_date <= pen.effective_start_date and p_report_end_date >= pen.effective_end_date)))
397 or pen.effective_end_date >= pen.enrt_cvg_thru_dt )
398 and pen.sspndd_flag = 'N'
399 and pen.per_in_ler_id = pil.per_in_ler_id
400 and pil.per_in_ler_stat_cd not in ( 'VOIDD' , 'BCKDT')
401 and pil.person_id = per.person_id
402 and ptp.person_type_id = per.person_type_id
403 and ptp.system_person_type in ( 'EMP', 'EX_EMP' , 'EX_EMP_APL' , 'EMP_APL' , 'PRTN' )
404 and (p_person_id is null or pen.person_id = p_person_id)
405 and (p_per_sel_rule is null or pen.person_id in (select person_id
406 from ben_person_actions pac
407 where pac.benefit_action_id = p_benefit_action_id) )
408 and (p_ntl_identifier is null or per.national_identifier = p_ntl_identifier)
409 and pen.prtt_enrt_rslt_id = prm.prtt_enrt_rslt_id (+)
410 and mpr.prtt_prem_id(+) = prm.prtt_prem_id
411 and mpr.yr_num(+) = to_number(to_char(p_report_start_date,'YYYY'))
412 and mpr.mo_num(+) = to_number(to_char(p_report_start_date,'MM'))
413 and prm.per_in_ler_id (+) = pen.per_in_ler_id
414 and acp.actl_prem_id (+) = prm.actl_prem_id
415 and (p_prem_type is null or acp.prsptv_r_rtsptv_cd = p_prem_type )
416 and pen.person_id = asg.person_id
417 and pen.business_group_id = p_business_group_id
418 and asg.business_group_id = p_business_group_id
419 /* and asg.assignment_type = 'E' */
420 and asg.primary_flag = 'Y'
421 and (p_payroll_id is null or asg.payroll_id = p_payroll_id )
422 and asg.payroll_id = pay.payroll_id
423 and pds.payroll_id = pay.payroll_id
424 and pds.start_date >= p_report_start_date
425 and pds.end_date <= p_report_end_date
426 and per.business_group_id = p_business_group_id
427 and p_run_date between per.effective_start_date and per.effective_end_date
428 and p_run_date between asg.effective_start_date and asg.effective_end_date
429 and p_run_date between pay.effective_start_date and pay.effective_end_date
430 and p_run_date between acp.effective_start_date (+) and acp.effective_end_date (+)
431 and p_run_date >= prm.effective_start_date (+)
432 and p_run_date <= prm.effective_end_date (+)
433 and (p_organization_id is null
434 or asg.organization_id = p_organization_id )
435 and (p_location_id is null
436 or asg.location_id = p_location_id)
437 and (p_benfts_grp_id is null
438 or per.benefit_group_id = p_benfts_grp_id)
439 and (p_rptg_grp_id is null
440 or exists (select null
441 from ben_popl_rptg_grp_f prpg
442 where (pen.pl_id = prpg.pl_id
443 or pen.pgm_id = prpg.pgm_id )
444 and prpg.rptg_grp_id = p_rptg_grp_id))
445 ) pl
446 where ((ee_ptax_elem_val + ee_atax_elem_val + er_elem_val) -
447 (ee_ptax_rt_val + ee_atax_rt_val + er_rt_val)) <> 0
448 or decode(((ee_ptax_elem_val + ee_atax_elem_val + er_elem_val ) - pl_prem_val),null,0,1) = 1
449 order by 1,2,3,4;
450 --
451 cursor c_lf_prem is
452 select * from (
453 select hr_general.decode_organization(asg.organization_id) lf_org_name,
454 hr_general.decode_location(asg.location_id) lf_location_name,
455 hr_general.decode_payroll(asg.payroll_id) lf_payroll_name,
456 decode(p_emp_name_format,'JP',( per.last_name || ' ' || per.first_name || ' / ' ||
457 per.per_information18 || ' ' || per.per_information19)
458 , per.full_name) lf_full_name,
459 per.national_identifier lf_national_identifier ,
460 ler.name lf_ler_name ,
461 nvl(pen.uom,popl.uom) lf_uom,
462 pil.lf_evt_ocrd_dt,
463 ben_recn_rep.get_change_eff_dt(pen.prtt_enrt_rslt_id, p_report_start_date,p_run_date) lf_chng_eff_dt,
464 ben_recn_rep.old_premium_val(pen.person_id,p_pl_id ,pen.pgm_id, pen.oipl_id, p_report_start_date, p_run_date, p_business_group_id, 'PREMIUM') lf_old_prem,
465 popl.val lf_val,
466 ben_recn_rep.old_premium_val(pen.person_id,p_pl_id ,pen.pgm_id, pen.oipl_id, p_report_start_date, p_run_date, p_business_group_id, 'RATE') lf_old_rate,
467 ben_recn_rep.get_new_rates(pen.prtt_enrt_rslt_id,p_report_start_date,p_run_date,p_business_group_id,'RATE',pen.per_in_ler_id) lf_rate,
468 ben_recn_rep.get_new_rates(pen.prtt_enrt_rslt_id,p_report_start_date,p_run_date,p_business_group_id,'ELEMENT',pen.per_in_ler_id ) lf_elem_val
469 /*,
470 ( select sum(cmcd_rt_val)
471 from ben_prtt_rt_val
472 where prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id) lf_rate ,
473 ( select sum(screen_entry_value)
474 from ben_prtt_rt_val prv
475 ,pay_element_entry_values_f env
476 where prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
477 and env.element_entry_value_id = prv.element_entry_value_id) lf_elem_val */
478 from ben_prtt_enrt_rslt_f pen,
479 ben_per_in_ler pil,
480 ben_ler_f ler ,
481 ben_prtt_prem_f prm ,
482 ben_prtt_prem_by_mo_f popl,
483 per_all_people_f per,
484 per_all_assignments_f asg,
485 per_person_types ptp
486 where pen.pl_id = p_pl_id
487 and (p_pgm_id is null or pen.pgm_id = p_pgm_id )
488 and pen.prtt_enrt_rslt_stat_cd is null
489 and pen.business_group_id = p_business_group_id
490 and pen.enrt_cvg_thru_dt <= pen.effective_end_date
491 and p_run_date between pen.enrt_cvg_strt_dt and enrt_cvg_thru_dt
492 and pen.sspndd_flag = 'N'
493 and (p_person_id is null or pen.person_id = p_person_id)
494 and (p_per_sel_rule is null or pen.person_id in (select person_id
495 from ben_person_actions pac
496 where pac.benefit_action_id = p_benefit_action_id) )
497 and pil.per_in_ler_id = pen.per_in_ler_id
498 and pil.per_in_ler_stat_cd not in ( 'VOIDD' , 'BCKDT')
499 and pil.lf_evt_ocrd_dt = ( select max(pil2.LF_EVT_OCRD_DT)
500 from ben_per_in_ler pil2
501 where pil2.per_in_ler_id = pen.per_in_ler_id
502 and pil2.per_in_ler_stat_cd in ( 'STRTD','PROCD')
503 and pil2.lf_evt_ocrd_dt
504 between p_report_start_date and p_report_end_date )
505 and pil.ler_id = ler.ler_id
506 and per.person_id = pil.person_id
507 and (p_ntl_identifier is null or per.national_identifier = p_ntl_identifier)
508 and p_run_date between per.effective_start_date and per.effective_end_date
509 and ptp.person_type_id = per.person_type_id
510 and ptp.system_person_type in ( 'EMP' , 'EX_EMP' , 'EX_EMP_APL' , 'EMP_APL' , 'PRTN' )
511 and asg.person_id = pen.person_id
512 and asg.business_group_id = p_business_group_id
513 /* and asg.assignment_type = 'E' */
514 and asg.primary_flag = 'Y'
515 and p_run_date between asg.effective_start_date and asg.effective_end_date
516 and prm.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
517 and p_run_date between prm.effective_start_date and prm.effective_end_date
518 and p_run_date between ler.effective_start_date and ler.effective_end_date
519 and prm.prtt_prem_id = popl.prtt_prem_id
520 and popl.yr_num = to_number(to_char(p_report_start_date,'YYYY'))
521 and popl.mo_num = to_number(to_char(p_report_start_date,'MM'))
522 and popl.business_group_id = p_business_group_id
523 and (p_organization_id is null
524 or asg.organization_id = p_organization_id )
525 and (p_location_id is null
526 or asg.location_id = p_location_id)
527 and (p_benfts_grp_id is null
528 or per.benefit_group_id = p_benfts_grp_id)
529 and (p_rptg_grp_id is null
530 or exists (select null
531 from ben_popl_rptg_grp_f prpg
532 where (pen.pl_id = prpg.pl_id
533 or pen.pgm_id = prpg.pgm_id )
534 and prpg.rptg_grp_id = p_rptg_grp_id))
535 order by 1,2,3,4) pl
536 where lf_old_prem <> lf_val ;
537 --
538 cursor c_pl_oipl is
539 select distinct decode(apr.pl_id,null,'OIPL','PL') levels ,
540 hr_general.decode_organization(asg.organization_id) prtt_org_name,
541 hr_general.decode_location(asg.location_id) prtt_location_name,
542 hr_general.decode_payroll(asg.payroll_id) prtt_payroll_name,
543 decode(p_emp_name_format,'JP',( per.last_name || ' ' || per.first_name || ' / ' ||
544 per.per_information18 || ' ' || per.per_information19)
545 , per.full_name) full_name ,
546 per.national_identifier,
547 pen.enrt_cvg_strt_dt,
548 decode(pen.enrt_cvg_thru_dt,to_date('31-12-4712','dd-mm-yyyy'),null,pen.enrt_cvg_thru_dt) enrt_cvg_thru_dt ,
549 popl.val,
550 nvl(pen.uom, popl.uom) uom,
551 decode(apr.pl_id, null, ben_batch_utils.get_opt_name(apr.oipl_id, p_business_group_id, p_run_date)) option_name,
552 apr.oipl_id,
553 apr.pl_id ,
554 ben_recn_rep.get_prtt_rate_val(pen.prtt_enrt_rslt_id , p_report_start_date, pen.per_in_ler_id, p_report_end_date) rate-- 3608119
555 from ben_prtt_enrt_rslt_f pen
556 ,ben_per_in_ler pil
557 ,per_all_people_f per
558 ,per_person_types ptp
559 ,per_all_assignments_f asg
560 ,ben_actl_prem_f apr
561 ,ben_prtt_prem_f prm
562 ,ben_prtt_prem_by_mo_f popl
563 where pen.pl_id = p_pl_id
564 and (p_pgm_id is null or pen.pgm_id = p_pgm_id )
565 and pen.prtt_enrt_rslt_stat_cd is null
566 and pen.business_group_id = p_business_group_id
567 and pen.enrt_cvg_thru_dt >= pen.effective_end_date
568 and (p_report_start_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
569 or p_report_end_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
570 or (p_report_start_date >= pen.enrt_cvg_strt_dt and p_report_end_date <= pen.enrt_cvg_thru_dt)
571 or (p_report_start_date <= pen.enrt_cvg_strt_dt and p_report_end_date >= pen.enrt_cvg_thru_dt))
572 and pen.sspndd_flag = 'N'
573 and (p_person_id is null or pen.person_id = p_person_id)
574 and (p_per_sel_rule is null or pen.person_id in (select person_id
575 from ben_person_actions pac
576 where pac.benefit_action_id = p_benefit_action_id) )
577 and pil.per_in_ler_id = pen.per_in_ler_id
578 and pil.per_in_ler_stat_cd not in ( 'VOIDD' , 'BCKDT')
579 and per.person_id = pil.person_id
580 and (p_ntl_identifier is null or per.national_identifier = p_ntl_identifier)
581 and p_run_date between per.effective_start_date and per.effective_end_date
582 and ptp.person_type_id = per.person_type_id
583 and ptp.system_person_type in ( 'EMP' , 'EX_EMP' , 'EX_EMP_APL' , 'EMP_APL' , 'PRTN' )
584 and asg.person_id = pen.person_id
585 and asg.business_group_id = p_business_group_id
586 /* and asg.assignment_type = 'E' */
587 and asg.primary_flag = 'Y'
588 and p_run_date between asg.effective_start_date and asg.effective_end_date
589 and prm.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
590 and prm.per_in_ler_id = pen.per_in_ler_id
591 and apr.actl_prem_id = prm.actl_prem_id
592 and prm.prtt_prem_id = popl.prtt_prem_id
593 and popl.yr_num||lpad(popl.mo_num,2,0) between to_number(to_char(p_report_start_date,'YYYYMM'))
594 and to_number(to_char(p_report_end_date,'YYYYMM'))
595 and apr.business_group_id = p_business_group_id
596 and (p_prem_type is null or apr.prsptv_r_rtsptv_cd = p_prem_type )
597 and p_run_date between apr.effective_start_date and apr.effective_end_date
598 and popl.business_group_id = p_business_group_id
599 and p_run_date between prm.effective_start_date and prm.effective_end_date
600 and ( ( apr.oipl_id is null and apr.pl_id = p_pl_id )
601 or ( apr.pl_id is null
602 and apr.oipl_id in ( select oipl_id from ben_oipl_f oipl
603 where oipl.pl_id = p_pl_id
604 and oipl.business_group_id = p_business_group_id
605 and p_run_date between oipl.effective_start_date and oipl.effective_end_date )))
606 and (p_payroll_id is null or asg.payroll_id = p_payroll_id )
607 and (p_organization_id is null
608 or asg.organization_id = p_organization_id )
609 and (p_location_id is null
610 or asg.location_id = p_location_id)
611 and (p_benfts_grp_id is null
612 or per.benefit_group_id = p_benfts_grp_id)
613 and (p_rptg_grp_id is null
614 or exists (select null
615 from ben_popl_rptg_grp_f prpg
616 where (pen.pl_id = prpg.pl_id
617 or pen.pgm_id = prpg.pgm_id )
618 and prpg.rptg_grp_id = p_rptg_grp_id))
619 order by 1,2,3,4,5;
620 --
621 l_grp_header_pos number := 5;
622 l_rec_pointer number := 0;
623 l_report ben_recn_rep.g_report_array := ben_recn_rep.g_report_array(); -- Plan Reconciliation Report Array
624 l_disc_report ben_recn_rep.g_report_array := ben_recn_rep.g_report_array(); -- Plan Discrepency Report Array
625 l_disc_rec_pointer number := 0;
626 l_person_id varchar2(200) := -1;
627 l_total_record g_report_cols_rec;
628 l_subtotal_record g_report_cols_rec;
629 l_prtt_count number := 0;
630 l_level varchar2(10);
631 l_uom varchar2(30);
632 l_format_mask varchar2(100);
633 l_pgm_name ben_pgm_f.name%type;
634 l_pl_name varchar2(1000);
635 l_date varchar2(50);
636 l_plan_recn_rep_header varchar2(2000);
637 l_plan_disc_rep_header varchar2(2000);
638 l_plan_prtt_rep_header varchar2(2000);
639 l_lfe_rep_header varchar2(2000);
640 --
641 l_person per_all_people_f.full_name%type;
642 l_emp_name_format varchar2(80);
643 l_pgm ben_pgm_f.name%type;
644 l_pl ben_pl_f.name%type;
645 l_per_sel_rule ff_formulas_f.formula_name%type;
646 l_business_group varchar2(250);
647 l_organization varchar2(250);
648 l_location varchar2(250);
649 l_benfts_grp varchar2(250);
650 l_rptg_grp varchar2(250);
651 l_prem_type varchar2(80);
652 l_payroll varchar2(250);
653 l_output_typ varchar2(80);
654 l_dsply_pl_disc_rep varchar2(80);
655 l_dsply_pl_recn_rep varchar2(80);
656 l_dsply_pl_prtt_rep varchar2(80);
657 l_dsply_prtt_reps varchar2(80);
658 l_dsply_lfe_rep varchar2(80);
659 begin
660
661 -- Report Headers
662 if p_pgm_id is not null then
663 l_pgm_name := ben_batch_utils.get_pgm_name(p_pgm_id, p_business_group_id,p_run_date);
664 end if;
665 --
666 l_pl_name := ben_batch_utils.get_pl_name(p_pl_id,p_business_group_id,p_run_date);
667 l_date := ' ' || to_char(p_report_start_date,'DD Month YYYY')
668 || ' - ' || to_char(p_report_end_date,'DD Month YYYY');
669 l_plan_recn_rep_header := l_pl_name ||' Reconciliations for'|| l_date;
670 l_plan_disc_rep_header := l_pl_name ||' Discrepancies for'|| l_date;
671 l_plan_prtt_rep_header := l_pl_name ||' Plan Participant Details for'|| l_date;
672 l_lfe_rep_header := 'Life Events Affecting Premiums for'|| l_date;
673 --
674
675 -- csv file name
676 p_op_file_name := to_char(p_conc_request_id)||'.csv';
677 --
678
679 -------------------- Report Parameters ----------------------
680 l_report.extend(29);
681 l_report(2).col2 := 'ORACLE ADVANCED BENEFITS';
682 l_report(4).col2 := 'Reconciliation of Premium Contributions to Element Entries Report';
683 l_report(5).col1 := 'Execution ID '|| p_conc_request_id;
684 l_report(5).col3 := 'Start Date '|| trunc(sysdate);
685 --
686
687 l_report(7).col1 := 'Reporting Start Date';
688 l_report(8).col1 := 'Reporting End Date';
689 l_report(9).col1 := 'Employee Name';
690 l_report(10).col1 := 'National Identifier';
691 l_report(11).col1 := 'Employee Name Format';
692 l_report(12).col1 := 'Program';
693 l_report(13).col1 := 'Plan';
694 l_report(14).col1 := 'Person Selection Rule';
695 l_report(15).col1 := 'Organization';
696 l_report(16).col1 := 'Location';
697 l_report(17).col1 := 'Benefits Group';
698 l_report(18).col1 := 'Reporting Group';
699 l_report(19).col1 := 'Premium Type';
700 l_report(20).col1 := 'Payroll';
701 l_report(21).col1 := 'Output Type';
702 l_report(22).col1 := 'Display Plan Reconciliation Report';
703 l_report(23).col1 := 'Display Plan Discrepancy Report';
704 l_report(24).col1 := 'Display Life Event Report';
705 l_report(25).col1 := 'Display Plan Plan Participant Details Report';
706 l_report(26).col1 := 'Display Participant Details Report';
707 --
708 ben_recn_rep.report_header
709 (p_run_date => p_run_date
710 ,p_person_id => p_person_id
711 ,p_emp_name_format => p_emp_name_format
712 ,p_pgm_id => p_pgm_id
713 ,p_pl_id => p_pl_id
714 ,p_per_sel_rule_id => p_per_sel_rule
715 ,p_business_group_id => p_business_group_id
716 ,p_organization_id => p_organization_id
717 ,p_location_id => p_location_id
718 ,p_benfts_grp_id => p_benfts_grp_id
719 ,p_rptg_grp_id => p_rptg_grp_id
720 ,p_prem_type => p_prem_type
721 ,p_payroll_id => p_payroll_id
722 ,p_output_typ => p_output_typ
723 ,p_dsply_pl_disc_rep => p_dsply_pl_disc_rep
724 ,p_dsply_pl_recn_rep => p_dsply_pl_recn_rep
725 ,p_dsply_pl_prtt_rep => p_dsply_pl_prtt_rep
726 ,p_dsply_prtt_reps => p_dsply_prtt_reps
727 ,p_dsply_lfe_rep => p_dsply_lfe_rep
728 ,p_ret_person => l_person
729 ,p_ret_emp_name_format => l_emp_name_format
730 ,p_ret_pgm => l_pgm
731 ,p_ret_pl => l_pl
732 ,p_ret_per_sel_rule => l_per_sel_rule
733 ,p_ret_business_group => l_business_group
734 ,p_ret_organization => l_organization
735 ,p_ret_location => l_location
736 ,p_ret_benfts_grp => l_benfts_grp
737 ,p_ret_rptg_grp => l_rptg_grp
738 ,p_ret_prem_type => l_prem_type
739 ,p_ret_payroll => l_payroll
740 ,p_ret_output_typ => l_output_typ
741 ,p_ret_dsply_pl_disc_rep => l_dsply_pl_disc_rep
742 ,p_ret_dsply_pl_recn_rep => l_dsply_pl_recn_rep
743 ,p_ret_dsply_pl_prtt_rep => l_dsply_pl_prtt_rep
744 ,p_ret_dsply_prtt_reps => l_dsply_prtt_reps
745 ,p_ret_dsply_lfe_rep => l_dsply_lfe_rep );
746 --
747 l_report(7).col2 := p_rep_st_dt;
748 l_report(8).col2 := p_rep_end_dt;
749 l_report(9).col2 := l_person;
750 l_report(10).col2 := nvl(p_ntl_identifier,'All');
751 l_report(11).col2 := l_emp_name_format;
752 l_report(12).col2 := l_pgm;
753 l_report(13).col2 := l_pl;
754 l_report(14).col2 := l_per_sel_rule;
755 l_report(15).col2 := l_organization;
756 l_report(16).col2 := l_location;
757 l_report(17).col2 := l_benfts_grp;
758 l_report(18).col2 := l_rptg_grp;
759 l_report(19).col2 := l_prem_type;
760 l_report(20).col2 := l_payroll;
761 l_report(21).col2 := l_output_typ;
762 l_report(22).col2 := l_dsply_pl_recn_rep;
763 l_report(23).col2 := l_dsply_pl_disc_rep;
764 l_report(24).col2 := l_dsply_lfe_rep;
765 l_report(25).col2 := l_dsply_pl_prtt_rep;
766 l_report(26).col2 := l_dsply_prtt_reps;
767
768 --
769 l_report.extend;
770 print_report(p_log_file_name => p_op_file_name,
771 p_report_array => l_report,
772 p_close_file => FALSE);
773 --
774
775
776 l_report.delete;
777 l_rec_pointer := 0;
778 ---------------------Plan Reconciliation Section---------------
779 if p_dsply_recn = 'Y' then
780 l_report.extend(l_grp_header_pos);
781 l_report(2).col1 := l_pgm_name;
782 l_report(3).col1 := l_plan_recn_rep_header;
783 --
784 l_report(l_grp_header_pos).col1 := 'Participant';
785 l_report(l_grp_header_pos).col2 := 'National Id';
786 l_report(l_grp_header_pos).col3 := 'Payroll';
787 l_report(l_grp_header_pos).col4 := 'Coverage';
788 l_report(l_grp_header_pos).col5 := 'Participant Monthly Premium';
789 l_report(l_grp_header_pos).col6 := 'Payroll Frequency';
790 l_report(l_grp_header_pos).col7 := 'Standard Rate Amounts Employee Pre-Tax Contribution';
791 l_report(l_grp_header_pos).col8 := 'Standard Rate Amounts Employee Post-Tax Contribution';
792 l_report(l_grp_header_pos).col9 := 'Standard Rate Amounts Employer Contribution';
793 l_report(l_grp_header_pos).col10 := 'Standard Rate Amounts Pay Period Total';
794 l_report(l_grp_header_pos).col11 := 'Element Entry(per pay period) Employee Pre-Tax Contribution';
795 l_report(l_grp_header_pos).col12 := 'Element Entry(per pay period) Employee Post-Tax Contribution';
796 l_report(l_grp_header_pos).col13 := 'Element Entry(per pay period) Employer Contribtion';
797 l_report(l_grp_header_pos).col14 := 'Element Entry(per pay period) Actual Total Contributions';
798 l_report(l_grp_header_pos).col15 := 'Discrepancy';
799 --
800 l_rec_pointer := l_grp_header_pos ;
801 for l_plan_recn in c_plan_recn loop
802 if l_uom is null then
803 l_uom := l_plan_recn.pl_uom ;
804 end if;
805 --
806 l_prtt_count := c_plan_recn%rowcount;
807 l_report.extend;
808 l_rec_pointer := l_rec_pointer + 1;
809
810
811 l_report(l_rec_pointer).col1 := l_plan_recn.pl_per_name ; -- 'Participant'
812 l_report(l_rec_pointer).col2 := l_plan_recn.pl_ntnl_id ; -- 'National Id'
813 l_report(l_rec_pointer).col3 := l_plan_recn.pl_pay_name ; -- 'Payroll'
814 l_report(l_rec_pointer).col4 := l_plan_recn.pl_bnft_amt ; -- 'Coverage'
815 l_report(l_rec_pointer).col5 := l_plan_recn.pl_prem_val ; -- 'Participant Monthly Premium'
816 l_report(l_rec_pointer).col6 := l_plan_recn.pl_perd_typ ; -- 'Payroll Frequency'
817 l_report(l_rec_pointer).col7 := l_plan_recn.ee_ptax_rt_val_tot ; -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
818 l_report(l_rec_pointer).col8 := l_plan_recn.ee_atax_rt_val_tot ; -- 'Standard Rate Amounts Employee Post-Tax Contribution'
819 l_report(l_rec_pointer).col9 := l_plan_recn.er_rt_val_tot ; -- 'Standard Rate Amounts Employer Contribution'
820 l_report(l_rec_pointer).col10 := l_plan_recn.pay_perd_total ; -- 'Standard Rate Amounts Pay Period Total'
821 l_report(l_rec_pointer).col11 := l_plan_recn.ee_ptax_elem_val_tot ; -- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
822 l_report(l_rec_pointer).col12 := l_plan_recn.ee_atax_elem_val_tot ; -- 'Element Entry(per pay period) Employee Post-Tax Contribution'
823 l_report(l_rec_pointer).col13 := l_plan_recn.er_elem_val_tot; -- 'Element Entry(per pay period) Employer Contribtion'
824 l_report(l_rec_pointer).col14 := l_plan_recn.actual_total ; -- 'Element Entry(per pay period) Actual Total Contributions'
825
826 -- discrepency
827 if l_report(l_rec_pointer).col10 <> l_report(l_rec_pointer).col14 and
828 l_report(l_rec_pointer).col14 <> l_report(l_rec_pointer).col5 then
829 l_report(l_rec_pointer).col15 := 'Standard Rate and Premium';
830 elsif l_report(l_rec_pointer).col10 <> l_report(l_rec_pointer).col14 then
831 l_report(l_rec_pointer).col15 := 'Standard Rate';
832 elsif l_report(l_rec_pointer).col14 <> l_report(l_rec_pointer).col5 then
833 l_report(l_rec_pointer).col15 := 'Premium';
834 end if;
835 --
836 l_total_record.col1 := 'Total:'; -- 'Total'
837 l_total_record.col5 := nvl(l_total_record.col5 ,0) + nvl(l_plan_recn.pl_prem_val,0) ; -- 'Participant Monthly Premium'
838 l_total_record.col7 := nvl(l_total_record.col7 ,0) + nvl(l_plan_recn.ee_ptax_rt_val_tot,0) ; -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
839 l_total_record.col8 := nvl(l_total_record.col8 ,0) + nvl(l_plan_recn.ee_atax_rt_val_tot,0) ; -- 'Standard Rate Amounts Employee Post-Tax Contribution'
840 l_total_record.col9 := nvl(l_total_record.col9 ,0) + nvl(l_plan_recn.er_rt_val_tot,0) ; -- 'Standard Rate Amounts Employer Contribution'
841 l_total_record.col10 := nvl(l_total_record.col10,0) + nvl(l_plan_recn.pay_perd_total,0) ; -- 'Standard Rate Amounts Pay Period Total'
842 l_total_record.col11 := nvl(l_total_record.col11,0) + nvl(l_plan_recn.ee_ptax_elem_val_tot,0) ; -- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
843 l_total_record.col12 := nvl(l_total_record.col12,0) + nvl(l_plan_recn.ee_atax_elem_val_tot,0) ; -- 'Element Entry(per pay period) Employee Post-Tax Contribution'
844 l_total_record.col13 := nvl(l_total_record.col13,0) + nvl(l_plan_recn.er_elem_val_tot,0); -- 'Element Entry(per pay period) Employer Contribtion'
845 l_total_record.col14 := nvl(l_total_record.col14,0) + nvl(l_plan_recn.actual_total,0) ; -- 'Element Entry(per pay period) Actual Total Contribu
846
847
848 end loop;
849 if l_grp_header_pos <> l_rec_pointer then
850 l_report.extend;
851 l_rec_pointer := l_rec_pointer + 1;
852 l_report(l_rec_pointer) := l_total_record;
853 end if;
854 --
855 l_report.extend;
856 l_rec_pointer := l_rec_pointer + 1;
857 l_report(l_rec_pointer).col1 := 'Participant Count:' || l_prtt_count;
858 --
859 if l_prtt_count = 0 then
860 l_report(l_grp_header_pos) := null;
861 end if;
862 --
863 l_format_mask := fnd_currency.get_format_mask(l_uom, 30);
864 if l_format_mask is not null then
865 for l_loop_count in (l_grp_header_pos + 1)..l_report.last loop
866 l_report(l_loop_count).col4 := to_char(to_number(l_report(l_loop_count).col4 ) ,l_format_mask); -- 'Coverage'
867 l_report(l_loop_count).col5 := to_char(to_number(l_report(l_loop_count).col5 ) ,l_format_mask); -- 'Participant Monthly Premium'
868 l_report(l_loop_count).col7 := to_char(to_number(l_report(l_loop_count).col7 ) ,l_format_mask); -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
869 l_report(l_loop_count).col8 := to_char(to_number(l_report(l_loop_count).col8 ) ,l_format_mask); -- 'Standard Rate Amounts Employee Post-Tax Contribution'
870 l_report(l_loop_count).col9 := to_char(to_number(l_report(l_loop_count).col9 ) ,l_format_mask); -- 'Standard Rate Amounts Employer Contribution'
871 l_report(l_loop_count).col10 := to_char(to_number(l_report(l_loop_count).col10) ,l_format_mask); -- 'Standard Rate Amounts Pay Period Total'
872 l_report(l_loop_count).col11 := to_char(to_number(l_report(l_loop_count).col11) ,l_format_mask); -- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
873 l_report(l_loop_count).col12 := to_char(to_number(l_report(l_loop_count).col12) ,l_format_mask); -- 'Element Entry(per pay period) Employee Post-Tax Contribution'
874 l_report(l_loop_count).col13 := to_char(to_number(l_report(l_loop_count).col13) ,l_format_mask); -- 'Element Entry(per pay period) Employer Contribtion'
875 l_report(l_loop_count).col14 := to_char(to_number(l_report(l_loop_count).col14) ,l_format_mask); -- 'Element Entry(per pay period) Actual Total Contributions'
876 end loop;
877 --
878 end if;
879 --
880 l_report.extend;
881 print_report(p_log_file_name => p_op_file_name,
882 p_report_array => l_report,
883 p_close_file => FALSE);
884 --
885 end if;
886 --
887
888 l_report.delete;
889 l_rec_pointer := 0;
890 l_prtt_count := 0;
891 l_total_record := null;
892
893 if p_dsply_disc = 'Y' then
894 l_disc_report.extend(l_grp_header_pos);
895 l_disc_report(2).col1 := l_pgm_name;
896 l_disc_report(3).col1 := l_plan_disc_rep_header;
897 l_disc_report(l_grp_header_pos).col1 := 'Participant';
898 l_disc_report(l_grp_header_pos).col2 := 'National Id';
899 l_disc_report(l_grp_header_pos).col3 := 'Payroll';
900 l_disc_report(l_grp_header_pos).col4 := 'Coverage';
901 l_disc_report(l_grp_header_pos).col5 := 'Participant Monthly Premium';
902 l_disc_report(l_grp_header_pos).col6 := 'Pay Period';
903 l_disc_report(l_grp_header_pos).col7 := 'Standard Rate Amounts Employee Pre-Tax Contribution';
904 l_disc_report(l_grp_header_pos).col8 := 'Standard Rate Amounts Employee Post-Tax Contribution';
905 l_disc_report(l_grp_header_pos).col9 := 'Standard Rate Amounts Employer Contribution';
906 l_disc_report(l_grp_header_pos).col10 := 'Standard Rate Amounts Pay Period Total';
907 l_disc_report(l_grp_header_pos).col11 := 'Element Entry(per pay period) Employee Pre-Tax Contribution';
908 l_disc_report(l_grp_header_pos).col12 := 'Element Entry(per pay period) Employee Post-Tax Contribution';
909 l_disc_report(l_grp_header_pos).col13 := 'Element Entry(per pay period) Employer Contribtion';
910 l_disc_report(l_grp_header_pos).col14 := 'Element Entry(per pay period) Actual Total Contributions';
911 l_disc_report(l_grp_header_pos).col15 := 'Premium Discrepancy';
912 l_disc_report(l_grp_header_pos).col16 := 'Standard Rate Discrepancy';
913
914 l_disc_rec_pointer := l_grp_header_pos;
915
916 for l_plan_recn in c_plan_disc loop
917 --
918 if l_uom is null then
919 l_uom := l_plan_recn.pl_sql_uom;
920 end if;
921 --
922 if l_plan_recn.pl_person_id||l_plan_recn.pl_bnft_amount||l_plan_recn.pl_prem_val <> l_person_id then
923 l_person_id := l_plan_recn.pl_person_id||l_plan_recn.pl_bnft_amount||l_plan_recn.pl_prem_val ;
924 l_prtt_count := l_prtt_count + 1;
925 if l_disc_rec_pointer <> l_grp_header_pos then
926 -- Fill the Subtotals for the previous person
927 l_disc_report.extend(1);
928 l_disc_rec_pointer := l_disc_rec_pointer + 1;
929
930 l_disc_report(l_disc_rec_pointer) := l_subtotal_record;
931
932 -- Report Total
933 l_total_record.col1 := 'Total:';
934 l_total_record.col7 := nvl(l_total_record.col7,0) + l_subtotal_record.col7 ; -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
935 l_total_record.col8 := nvl(l_total_record.col8,0) + l_subtotal_record.col8 ; -- 'Standard Rate Amounts Employee Post-Tax Contribution'
936 l_total_record.col9 := nvl(l_total_record.col9,0) + l_subtotal_record.col9 ; -- 'Standard Rate Amounts Employer Contribution'
937 l_total_record.col10 := nvl(l_total_record.col10,0) + l_subtotal_record.col10; -- 'Standard Rate Amounts Pay Period Total'
938 l_total_record.col11 := nvl(l_total_record.col11,0) + l_subtotal_record.col11; -- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
939 l_total_record.col12 := nvl(l_total_record.col12,0) + l_subtotal_record.col12; -- 'Element Entry(per pay period) Employee Post-Tax Contribution'
940 l_total_record.col13 := nvl(l_total_record.col13,0) + l_subtotal_record.col13; -- 'Element Entry(per pay period) Employer Contribtion'
941 l_total_record.col14 := nvl(l_total_record.col14,0) + l_subtotal_record.col14; -- 'Element Entry(per pay period) Actual Total Contributions'
942 l_total_record.col15 := nvl(l_total_record.col15,0) + l_subtotal_record.col15; -- 'Premium Discrepency'
943 l_total_record.col16 := nvl(l_total_record.col16,0) + l_subtotal_record.col16; -- 'Standard Rate Discrepancy'
944
945 l_subtotal_record := null;
946 --
947 --
948 end if;
949 --
950 l_subtotal_record.col7 := l_plan_recn.ee_ptax_rt_val ; -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
951 l_subtotal_record.col8 := l_plan_recn.ee_atax_rt_val ; -- 'Standard Rate Amounts Employee Post-Tax Contribution'
952 l_subtotal_record.col9 := l_plan_recn.er_rt_val ; -- 'Standard Rate Amounts Employer Contribution'
953 l_subtotal_record.col10 := l_plan_recn.pay_perd_total ; -- 'Standard Rate Amounts Pay Period Total'
954 l_subtotal_record.col11 := l_plan_recn.ee_ptax_elem_val;-- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
955 l_subtotal_record.col12 := l_plan_recn.ee_atax_elem_val;-- 'Element Entry(per pay period) Employee Post-Tax Contribution'
956 l_subtotal_record.col13 := l_plan_recn.er_elem_val; -- 'Element Entry(per pay period) Employer Contribtion'
957 l_subtotal_record.col14 := l_plan_recn.actual_total ; -- 'Element Entry(per pay period) Actual Total Contributions'
958 l_subtotal_record.col15 := l_plan_recn.prem_dis; -- 'Premium Discrepency'
959 l_subtotal_record.col16 := l_plan_recn.std_rt_dis; -- 'Standard Rate Discrepancy'
960
961 l_disc_report.extend(1);
962 l_disc_rec_pointer := l_disc_rec_pointer + 1;
963
964 l_disc_report(l_disc_rec_pointer).col1 := l_plan_recn.pl_full_name ; -- 'Participant'
965 l_disc_report(l_disc_rec_pointer).col2 := l_plan_recn.pl_national_id ; -- 'National Id'
966 l_disc_report(l_disc_rec_pointer).col3 := l_plan_recn.pl_payroll_name; -- 'Payroll'
967 l_disc_report(l_disc_rec_pointer).col4 := l_plan_recn.pl_bnft_amount ; -- 'Coverage'
968 l_disc_report(l_disc_rec_pointer).col5 := l_plan_recn.pl_prem_val ; -- 'Participant Monthly Premium'
969 l_disc_report(l_disc_rec_pointer).col6 := l_plan_recn.pl_pay_prd ; -- 'Payroll Period'
970 l_disc_report(l_disc_rec_pointer).col7 := l_plan_recn.ee_ptax_rt_val ; -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
971 l_disc_report(l_disc_rec_pointer).col8 := l_plan_recn.ee_atax_rt_val ; -- 'Standard Rate Amounts Employee Post-Tax Contribution'
972 l_disc_report(l_disc_rec_pointer).col9 := l_plan_recn.er_rt_val ; -- 'Standard Rate Amounts Employer Contribution'
973 l_disc_report(l_disc_rec_pointer).col10 := l_plan_recn.pay_perd_total ; -- 'Standard Rate Amounts Pay Period Total'
974 l_disc_report(l_disc_rec_pointer).col11 := l_plan_recn.ee_ptax_elem_val;-- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
975 l_disc_report(l_disc_rec_pointer).col12 := l_plan_recn.ee_atax_elem_val;-- 'Element Entry(per pay period) Employee Post-Tax Contribution'
976 l_disc_report(l_disc_rec_pointer).col13 := l_plan_recn.er_elem_val; -- 'Element Entry(per pay period) Employer Contribtion'
977 l_disc_report(l_disc_rec_pointer).col14 := l_plan_recn.actual_total ; -- 'Element Entry(per pay period) Actual Total Contributions'
978 l_disc_report(l_disc_rec_pointer).col15 := l_plan_recn.prem_dis; -- 'Premium Discrepency'
979 l_disc_report(l_disc_rec_pointer).col16 := l_plan_recn.std_rt_dis; -- 'Standard Rate Discrepancy'
980 else
981 l_subtotal_record.col7 := l_subtotal_record.col7 + l_plan_recn.ee_ptax_rt_val; -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
982 l_subtotal_record.col8 := l_subtotal_record.col8 + l_plan_recn.ee_atax_rt_val; -- 'Standard Rate Amounts Employee Post-tax Contribution'
983 l_subtotal_record.col9 := l_subtotal_record.col9 + l_plan_recn.er_rt_val; -- 'Standard Rate Amounts Employer Contribution'
984 l_subtotal_record.col10 := l_subtotal_record.col10 + l_plan_recn.pay_perd_total; -- 'Standard Rate Amounts Pay Period Total'
985 l_subtotal_record.col11 := l_subtotal_record.col11 + l_plan_recn.ee_ptax_elem_val; -- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
986 l_subtotal_record.col12 := l_subtotal_record.col12 + l_plan_recn.ee_atax_elem_val; -- 'Element Entry(per pay period) Employee Post-Tax Contribution'
987 l_subtotal_record.col13 := l_subtotal_record.col13 + l_plan_recn.er_elem_val; -- 'Element Entry(per pay period) Employer Contribtion'
988 l_subtotal_record.col14 := l_subtotal_record.col14 + l_plan_recn.actual_total; -- 'Element Entry(per pay period) Actual Total Contributions'
989 l_subtotal_record.col15 := l_subtotal_record.col15 + l_plan_recn.prem_dis ; -- 'Premium Discrepency'
990 l_subtotal_record.col16 := l_subtotal_record.col16 + l_plan_recn.std_rt_dis; -- 'Standard Rate Discrepancy'
991 --
992 l_disc_report.extend(1);
993 l_disc_rec_pointer := l_disc_rec_pointer + 1;
994 l_disc_report(l_disc_rec_pointer).col6 := l_plan_recn.pl_pay_prd ; -- 'Payroll Period'
995 l_disc_report(l_disc_rec_pointer).col7 := l_plan_recn.ee_ptax_rt_val ; -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
996 l_disc_report(l_disc_rec_pointer).col8 := l_plan_recn.ee_atax_rt_val ; -- 'Standard Rate Amounts Employee Post-Tax Contribution'
997 l_disc_report(l_disc_rec_pointer).col9 := l_plan_recn.er_rt_val ; -- 'Standard Rate Amounts Employer Contribution'
998 l_disc_report(l_disc_rec_pointer).col10 := l_plan_recn.pay_perd_total ; -- 'Standard Rate Amounts Pay Period Total'
999 l_disc_report(l_disc_rec_pointer).col11 := l_plan_recn.ee_ptax_elem_val;-- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
1000 l_disc_report(l_disc_rec_pointer).col12 := l_plan_recn.ee_atax_elem_val;-- 'Element Entry(per pay period) Employee Post-Tax Contribution'
1001 l_disc_report(l_disc_rec_pointer).col13 := l_plan_recn.er_elem_val; -- 'Element Entry(per pay period) Employer Contribtion'
1002 l_disc_report(l_disc_rec_pointer).col14 := l_plan_recn.actual_total ; -- 'Element Entry(per pay period) Actual Total Contributions'
1003 l_disc_report(l_disc_rec_pointer).col15 := l_plan_recn.prem_dis; -- 'Premium Discrepency'
1004 l_disc_report(l_disc_rec_pointer).col16 := l_plan_recn.std_rt_dis; -- 'Standard Rate Discrepancy'
1005 end if;
1006 end loop;
1007 --
1008 -- subtotal for the last record
1009 if l_person_id <> -1 then
1010
1011 l_disc_report.extend(1);
1012 l_disc_rec_pointer := l_disc_rec_pointer + 1;
1013
1014 l_disc_report(l_disc_rec_pointer) := l_subtotal_record;
1015 -- Report Total
1016 l_total_record.col7 := nvl(l_total_record.col7,0) + l_subtotal_record.col7 ; -- 'Standard Rate Amounts Employee Pre-Tax Contribution'
1017 l_total_record.col8 := nvl(l_total_record.col8,0) + l_subtotal_record.col8 ; -- 'Standard Rate Amounts Employee Post-Tax Contribution'
1018 l_total_record.col9 := nvl(l_total_record.col9,0) + l_subtotal_record.col9 ; -- 'Standard Rate Amounts Employer Contribution'
1019 l_total_record.col10 := nvl(l_total_record.col10,0) + l_subtotal_record.col10; -- 'Standard Rate Amounts Pay Period Total'
1020 l_total_record.col11 := nvl(l_total_record.col11,0) + l_subtotal_record.col11; -- 'Element Entry(per pay period) Employee Pre-Tax Contribution'
1021 l_total_record.col12 := nvl(l_total_record.col12,0) + l_subtotal_record.col12; -- 'Element Entry(per pay period) Employee Post-Tax Contribution'
1022 l_total_record.col13 := nvl(l_total_record.col13,0) + l_subtotal_record.col13; -- 'Element Entry(per pay period) Employer Contribtion'
1023 l_total_record.col14 := nvl(l_total_record.col14,0) + l_subtotal_record.col14; -- 'Element Entry(per pay period) Actual Total Contributions'
1024 l_total_record.col15 := nvl(l_total_record.col15,0) + l_subtotal_record.col15; -- 'Premium Discrepency'
1025 l_total_record.col16 := nvl(l_total_record.col16,0) + l_subtotal_record.col16; -- 'Standard Rate Discrepancy'
1026
1027 --
1028 l_disc_report.extend(1);
1029 l_disc_rec_pointer := l_disc_rec_pointer + 1;
1030 l_disc_report(l_disc_rec_pointer) := l_total_record;
1031
1032 l_disc_report.extend(1);
1033 l_disc_rec_pointer := l_disc_rec_pointer + 1;
1034 l_disc_report(l_disc_rec_pointer).col1 := 'Participant Count:' || l_prtt_count;
1035 --
1036 end if;
1037 --
1038 -- apply the format mask for number columns
1039 l_format_mask := fnd_currency.get_format_mask(l_uom, 30);
1040 if l_format_mask is not null then
1041 --
1042 for l_loop_count in (l_grp_header_pos + 1)..l_disc_report.last loop
1043 l_disc_report(l_loop_count).col4 := to_char(to_number(l_disc_report(l_loop_count).col4) ,l_format_mask);-- Coverage'
1044 l_disc_report(l_loop_count).col5 := to_char(to_number(l_disc_report(l_loop_count).col5) ,l_format_mask);-- Participant Monthly Premium'
1045 l_disc_report(l_loop_count).col7 := to_char(to_number(l_disc_report(l_loop_count).col7) ,l_format_mask);-- Standard Rate Amounts Employee Pre-Tax Contribution'
1046 l_disc_report(l_loop_count).col8 := to_char(to_number(l_disc_report(l_loop_count).col8) ,l_format_mask);-- Standard Rate Amounts Employee Post-Tax Contribution'
1047 l_disc_report(l_loop_count).col9 := to_char(to_number(l_disc_report(l_loop_count).col9) ,l_format_mask);-- Standard Rate Amounts Employer Contribution'
1048 l_disc_report(l_loop_count).col10 := to_char(to_number(l_disc_report(l_loop_count).col10),l_format_mask);-- Standard Rate Amounts Pay Period Total'
1049 l_disc_report(l_loop_count).col11 := to_char(to_number(l_disc_report(l_loop_count).col11),l_format_mask);-- Element Entry(per pay period) Employee Pre-Tax Contribution'
1050 l_disc_report(l_loop_count).col12 := to_char(to_number(l_disc_report(l_loop_count).col12),l_format_mask);-- Element Entry(per pay period) Employee Post-Tax Contribution'
1051 l_disc_report(l_loop_count).col13 := to_char(to_number(l_disc_report(l_loop_count).col13),l_format_mask);-- Element Entry(per pay period) Employer Contribtion'
1052 l_disc_report(l_loop_count).col14 := to_char(to_number(l_disc_report(l_loop_count).col14),l_format_mask);-- Element Entry(per pay period) Actual Total Contributions'
1053 l_disc_report(l_loop_count).col15 := to_char(to_number(l_disc_report(l_loop_count).col15),l_format_mask);-- Premium Discrepency'
1054 l_disc_report(l_loop_count).col16 := to_char(to_number(l_disc_report(l_loop_count).col16),l_format_mask);-- Standard Rate Discrepancy'
1055 end loop;
1056 --
1057 end if;
1058 --
1059
1060 --
1061 if l_prtt_count = 0 then
1062 l_disc_report(l_grp_header_pos) := null;
1063 end if;
1064 --
1065 l_disc_report.extend;
1066 print_report(p_log_file_name => p_op_file_name,
1067 p_report_array => l_disc_report,
1068 p_close_file => FALSE);
1069 --
1070 end if;
1071 --
1072 l_report.delete;
1073 l_disc_report.delete;
1074 l_rec_pointer := 0;
1075 l_disc_rec_pointer := 0;
1076 l_prtt_count := 0;
1077 l_total_record := null;
1078 l_subtotal_record := null;
1079
1080 ---------------------Life Event Section---------------
1081 if p_dsply_lfe = 'Y' then
1082 l_report.extend(l_grp_header_pos);
1083 l_report(2).col1 := l_pgm_name;
1084 l_report(3).col1 := l_lfe_rep_header;
1085 --
1086 l_report(l_grp_header_pos).col1 := 'Participant';
1087 l_report(l_grp_header_pos).col2 := 'National Id';
1088 l_report(l_grp_header_pos).col3 := 'Life Event Name';
1089 l_report(l_grp_header_pos).col4 := 'Date of Life Event';
1090 l_report(l_grp_header_pos).col5 := 'Change Effective Date';
1091 l_report(l_grp_header_pos).col6 := 'Old Monthly Premium';
1092 l_report(l_grp_header_pos).col7 := 'New Monthly Premium';
1093 l_report(l_grp_header_pos).col8 := 'Old Rate';
1094 l_report(l_grp_header_pos).col9 := 'New Rate';
1095 l_report(l_grp_header_pos).col10 := 'Total Element Entries';
1096 --
1097 l_rec_pointer := l_grp_header_pos ;
1098 for l_lf_prem in c_lf_prem loop
1099 if l_uom is null then
1100 l_uom := l_lf_prem.lf_uom;
1101 end if;
1102 --
1103 l_prtt_count := c_lf_prem%rowcount;
1104 l_report.extend;
1105 l_rec_pointer := l_rec_pointer + 1;
1106
1107 l_report(l_rec_pointer).col1 := l_lf_prem.lf_full_name ; -- 'Participant';
1108 l_report(l_rec_pointer).col2 := l_lf_prem.lf_national_identifier ; -- 'National Id';
1109 l_report(l_rec_pointer).col3 := l_lf_prem.lf_ler_name ; -- 'Life Event Name';
1110 l_report(l_rec_pointer).col4 := l_lf_prem.lf_evt_ocrd_dt ; -- 'Date of Life Event';
1111 l_report(l_rec_pointer).col5 := l_lf_prem.lf_chng_eff_dt ; -- 'Change Effective Date';
1112 l_report(l_rec_pointer).col6 := l_lf_prem.lf_old_prem ; -- 'Old Monthly Premium';
1113 l_report(l_rec_pointer).col7 := l_lf_prem.lf_val ; -- 'New Monthly Premium';
1114 l_report(l_rec_pointer).col8 := l_lf_prem.lf_old_rate ; -- 'Old Rate';
1115 l_report(l_rec_pointer).col9 := l_lf_prem.lf_rate ; -- 'New Rate';
1116 l_report(l_rec_pointer).col10 := l_lf_prem.lf_elem_val ; -- 'Total Element Entries';
1117 end loop;
1118 --
1119 l_report.extend;
1120 l_rec_pointer := l_rec_pointer + 1;
1121 l_report(l_rec_pointer).col1 := 'Participant Count:' || l_prtt_count;
1122 --
1123 if l_prtt_count = 0 then
1124 l_report(l_grp_header_pos) := null;
1125 end if;
1126 --
1127 l_format_mask := fnd_currency.get_format_mask(l_uom, 30);
1128 if l_format_mask is not null then
1129 for l_loop_count in (l_grp_header_pos + 1)..l_report.last loop
1130 l_report(l_loop_count).col6 := to_char(to_number(l_report(l_loop_count).col6 ),l_format_mask);-- 'Old Monthly Premium';
1131 l_report(l_loop_count).col7 := to_char(to_number(l_report(l_loop_count).col7 ),l_format_mask);-- 'New Monthly Premium';
1132 l_report(l_loop_count).col8 := to_char(to_number(l_report(l_loop_count).col8 ),l_format_mask);-- 'Old Rate';
1133 l_report(l_loop_count).col9 := to_char(to_number(l_report(l_loop_count).col9 ),l_format_mask);-- 'New Rate';
1134 l_report(l_loop_count).col10 := to_char(to_number(l_report(l_loop_count).col10),l_format_mask);-- 'Total Element Entries';
1135 end loop;
1136 --
1137 end if;
1138 --
1139 l_report.extend;
1140 print_report(p_log_file_name => p_op_file_name,
1141 p_report_array => l_report,
1142 p_close_file => FALSE);
1143 --
1144 end if;
1145 --
1146
1147 l_report.delete;
1148 l_rec_pointer := 0;
1149 l_prtt_count := 0;
1150 l_total_record := null;
1151
1152 ---------------------Plan Participant Details Section---------------
1153 if p_dsply_pl_prtt = 'Y' then
1154 l_report.extend(l_grp_header_pos);
1155 l_report(2).col1 := l_pgm_name;
1156 l_report(3).col1 := l_plan_prtt_rep_header;
1157 --
1158 l_report(l_grp_header_pos).col1 := 'Participant';
1159 l_report(l_grp_header_pos).col2 := 'National Id';
1160 l_report(l_grp_header_pos).col3 := 'Coverage Start Date';
1161 l_report(l_grp_header_pos).col4 := 'Coverage End Date';
1162 l_report(l_grp_header_pos).col5 := 'Option';
1163 l_report(l_grp_header_pos).col6 := 'Participant Monthly Premium';
1164 l_report(l_grp_header_pos).col7 := 'Defined Amount';
1165 --
1166 l_rec_pointer := l_grp_header_pos ;
1167 for l_pl_oipl in c_pl_oipl loop
1168 if l_uom is null then
1169 l_uom := l_pl_oipl.uom;
1170 end if;
1171 --
1172 if l_pl_oipl.levels <> nvl(l_level,'STRANGE LEVEL') then
1173 -- For the First record in the sub group
1174 if l_grp_header_pos <> l_rec_pointer then
1175 --
1176 if l_level = 'PL' then
1177 l_subtotal_record.col1 := 'Total for Plan :';
1178 elsif l_level = 'OIPL' then
1179 l_subtotal_record.col1 := 'Total for Option in Plan :';
1180 end if;
1181 --
1182 l_report.extend;
1183 l_rec_pointer := l_rec_pointer + 1;
1184 l_report(l_rec_pointer) := l_subtotal_record;
1185 l_subtotal_record := null;
1186 end if;
1187 --
1188 l_total_record.col1 := 'Total:';
1189 l_total_record.col6 := nvl(l_total_record.col6,0) + nvl(l_pl_oipl.val,0);
1190
1191 l_subtotal_record.col6 := l_pl_oipl.val;
1192
1193 l_level := l_pl_oipl.levels;
1194 else
1195 l_subtotal_record.col6 := nvl(l_subtotal_record.col6,0) + nvl(l_pl_oipl.val,0);
1196 l_total_record.col6 := nvl(l_total_record.col6,0) + nvl(l_pl_oipl.val,0);
1197 end if;
1198 --
1199 l_prtt_count := c_pl_oipl%rowcount;
1200 l_report.extend;
1201 l_rec_pointer := l_rec_pointer + 1;
1202
1203 l_report(l_rec_pointer).col1 := l_pl_oipl.full_name ; -- 'Participant';
1204 l_report(l_rec_pointer).col2 := l_pl_oipl.national_identifier ;-- 'National Id';
1205 l_report(l_rec_pointer).col3 := l_pl_oipl.enrt_cvg_strt_dt ; -- 'Coverage Start Date';
1206 l_report(l_rec_pointer).col4 := l_pl_oipl.enrt_cvg_thru_dt ; -- 'Coverage End Date';
1207 l_report(l_rec_pointer).col5 := l_pl_oipl.option_name ; -- 'Option';
1208 l_report(l_rec_pointer).col6 := l_pl_oipl.val ; -- 'Participant Monthly Premium';
1209 l_report(l_rec_pointer).col7 := l_pl_oipl.rate; -- 'Defined Amount';
1210 --
1211 end loop;
1212 --
1213 if l_grp_header_pos <> l_rec_pointer then
1214 l_report.extend;
1215 l_rec_pointer := l_rec_pointer + 1;
1216 if l_level = 'PL' then
1217 l_subtotal_record.col1 := 'Total for Plan :';
1218 elsif l_level = 'OIPL' then
1219 l_subtotal_record.col1 := 'Total for Option in Plan :';
1220 end if;
1221
1222 l_report(l_rec_pointer) := l_subtotal_record;
1223
1224 l_report.extend;
1225 l_rec_pointer := l_rec_pointer + 1;
1226 l_report(l_rec_pointer) := l_total_record;
1227 end if;
1228 --
1229 l_report.extend;
1230 l_rec_pointer := l_rec_pointer + 1;
1231 l_report(l_rec_pointer).col1 := 'Participant Count:' || l_prtt_count;
1232 --
1233 if l_prtt_count = 0 then
1234 l_report(l_grp_header_pos) := null;
1235 end if;
1236 --
1237 l_format_mask := fnd_currency.get_format_mask(l_uom, 30);
1238 if l_format_mask is not null then
1239 for l_loop_count in (l_grp_header_pos + 1)..l_report.last loop
1240 l_report(l_loop_count).col6 := to_char(to_number(l_report(l_loop_count).col6) ,l_format_mask);-- 'Participant Monthly Premium';
1241 l_report(l_loop_count).col7 := to_char(to_number(l_report(l_loop_count).col7) ,l_format_mask);-- 'Defined Amount';
1242 end loop;
1243 --
1244 end if;
1245 --
1246 l_report.extend;
1247 print_report(p_log_file_name => p_op_file_name,
1248 p_report_array => l_report,
1249 p_close_file => TRUE);
1250 --
1251 end if;
1252 --
1253 if g_log_file_name is not null then
1254 close_log_file;
1255 end if;
1256 --
1257 if p_op_file_name is null then
1258 p_op_file_name := 'no output';
1259 end if;
1260 /*
1261 exception
1262 when others then
1263 raise;
1264 */
1265 end recon_report;
1266
1267 --
1268 -- ============================================================================
1269 -- << exec_per_selection_rule >>
1270 -- This procedure creates a person action for people who pass the person
1271 -- selection rule and returns the benefit action item (for a set).
1272 -- ============================================================================
1273 --
1274 procedure exec_per_selection_rule
1275 (p_pl_id number,
1276 p_pgm_id number,
1277 p_business_group_id number,
1278 p_run_date date,
1279 p_report_start_date date,
1280 p_prem_type varchar2,
1281 p_payroll_id number,
1282 p_organization_id number,
1283 p_location_id number,
1284 p_benfts_grp_id number,
1285 p_rptg_grp_id number,
1286 p_person_selection_rule_id number,
1287 p_benefit_action_id out nocopy number
1288 ) as
1289 --
1290 cursor c_person is
1291 select distinct per.person_id, pil.ler_id
1292 from ben_prtt_enrt_rslt_f pen
1293 ,ben_actl_prem_f acp
1294 ,ben_prtt_prem_f prm
1295 ,ben_prtt_prem_by_mo_f mpr
1296 ,ben_per_in_ler pil
1297 ,per_all_people_f per
1298 ,per_all_assignments_f asg
1299 where pen.pl_id = p_pl_id
1300 and (p_pgm_id is null or pen.pgm_id = p_pgm_id)
1301 and pen.prtt_enrt_rslt_stat_cd is null
1302 and pen.business_group_id = p_business_group_id
1303 and pen.enrt_cvg_thru_dt <= pen.effective_end_date
1304 and p_run_date between pen.enrt_cvg_strt_dt and enrt_cvg_thru_dt
1305 and pen.sspndd_flag = 'N'
1306 and pen.per_in_ler_id = pil.per_in_ler_id
1307 and pil.per_in_ler_stat_cd not in ( 'VOIDD' , 'BCKDT')
1308 and pil.person_id = per.person_id
1309 and pen.prtt_enrt_rslt_id = prm.prtt_enrt_rslt_id (+)
1310 and mpr.prtt_prem_id(+) = prm.prtt_prem_id
1311 and mpr.yr_num(+) = to_number(to_char(p_report_start_date,'YYYY'))
1312 and mpr.mo_num(+) = to_number(to_char(p_report_start_date,'MM'))
1313 and prm.per_in_ler_id (+) = pen.per_in_ler_id
1314 and acp.actl_prem_id (+) = prm.actl_prem_id
1315 and (p_prem_type is null or acp.prsptv_r_rtsptv_cd = p_prem_type )
1316 and pen.person_id = asg.person_id
1317 and pen.business_group_id = p_business_group_id
1318 and asg.business_group_id = p_business_group_id
1319 /* and asg.assignment_type = 'E' */
1320 and asg.primary_flag = 'Y'
1321 and (p_payroll_id is null or asg.payroll_id = p_payroll_id )
1322 and per.business_group_id = p_business_group_id
1323 and p_run_date between per.effective_start_date and per.effective_end_date
1324 and p_run_date between asg.effective_start_date and asg.effective_end_date
1325 and p_run_date between acp.effective_start_date (+) and acp.effective_end_date (+)
1326 and p_run_date >= prm.effective_start_date (+)
1327 and p_run_date <= prm.effective_end_date (+)
1328 and (p_organization_id is null
1329 or asg.organization_id = p_organization_id )
1330 and (p_location_id is null
1331 or asg.location_id = p_location_id)
1332 and (p_benfts_grp_id is null
1333 or per.benefit_group_id = p_benfts_grp_id)
1334 and (p_rptg_grp_id is null
1335 or exists (select null
1336 from ben_popl_rptg_grp_f prpg
1337 where pen.pl_id = prpg.pl_id(+)
1338 and pen.pgm_id = prpg.pgm_id(+)
1339 and prpg.rptg_grp_id = p_rptg_grp_id));
1340 --
1341 l_object_version_number number;
1342 l_benefit_action_id number;
1343 skip boolean;
1344 l_err_message varchar2(4000);
1345 l_rl_ret varchar2(100);
1346 l_person_action_id number;
1347 l_commit number;
1348 --
1349 begin
1350 -- Put row in fnd_sessions
1351 --
1352 dt_fndate.change_ses_date
1353 (p_ses_date => p_run_date,
1354 p_commit => l_commit);
1355
1356 -- create a benefit action
1357 ben_benefit_actions_api.create_perf_benefit_actions
1358 ( p_benefit_action_id => l_benefit_action_id
1359 ,p_process_date => p_run_date
1360 ,p_pgm_id => p_pgm_id
1361 ,p_business_group_id => p_business_group_id
1362 ,p_pl_id => p_pl_id
1363 ,p_person_selection_rl => p_person_selection_rule_id
1364 ,p_organization_id => p_organization_id
1365 ,p_location_id => p_location_id
1366 ,p_request_id => fnd_global.conc_request_id
1367 ,p_program_application_id => fnd_global.prog_appl_id
1368 ,p_program_id => fnd_global.conc_program_id
1369 ,p_program_update_date => sysdate
1370 ,p_object_version_number => l_object_version_number
1371 ,p_effective_date => p_run_date
1372 ,p_benfts_grp_id => p_benfts_grp_id
1373 ,p_payroll_id => p_payroll_id
1374 ,p_rptg_grp_id => p_rptg_grp_id
1375 ,p_mode_cd => 'U'
1376 ,p_derivable_factors_flag => 'N'
1377 ,p_validate_flag => 'N'
1378 ,p_debug_messages_flag => 'Y'
1379 ,p_audit_log_flag => 'N'
1380 ,p_no_plans_flag => 'N'
1381 ,p_no_programs_flag => 'N'
1382 );
1383 --
1384 p_benefit_action_id := l_benefit_action_id ;
1385 -- execute the formula for each person
1386 for l_person in c_person loop
1387 --
1388 ben_conc_reports.rep_person_selection_rule
1389 (p_person_id => l_person.person_id
1390 ,p_business_group_id => p_business_group_id
1391 ,p_person_selection_rule_id => p_person_selection_rule_id
1392 ,p_effective_date => p_run_date
1393 ,p_return => l_rl_ret
1394 ,p_err_message => l_err_message ) ;
1395 --
1396
1397 if l_rl_ret = 'Y' then
1398 -- person has passed the rule, so create a person action
1399 ben_person_actions_api.create_person_actions(
1400 p_validate => false
1401 ,p_person_action_id => l_person_action_id
1402 ,p_person_id => l_person.person_id
1403 ,p_ler_id => l_person.ler_id
1404 ,p_benefit_action_id => l_benefit_action_id
1405 ,p_action_status_cd => 'U'
1406 ,p_object_version_number => l_object_version_number
1407 ,p_effective_date => p_run_date);
1408 --
1409 end if;
1410 --
1411 end loop;
1412 --
1413 commit;
1414 exception
1415 when others then
1416 -- fnd_file.put_line(fnd_file.log, sqlerrm || ' ' || sqlcode);
1417 rollback;
1418 p_benefit_action_id := null;
1419 raise;
1420
1421 end exec_per_selection_rule;
1422 --
1423 -- ============================================================================
1424 -- <<old_premium_val>>
1425 -- ============================================================================
1426 --
1427 FUNCTION old_premium_val
1428 (p_person_id number,
1429 p_pl_id number,
1430 p_pgm_id number,
1431 p_oipl_id number,
1432 p_report_start_date date,
1433 p_run_date date,
1434 p_business_group_id number,
1435 p_return_type varchar2 -- ('PREMIUM','RATE')
1436 ) RETURN NUMBER is
1437 --
1438 cursor c_premium(c_pgm_id number, c_oipl_id number) is
1439 select popl.val,
1440 pen.prtt_enrt_rslt_id
1441 from ben_prtt_enrt_rslt_f pen
1442 ,ben_per_in_ler pil
1443 ,ben_prtt_prem_f prm
1444 ,ben_prtt_prem_by_mo_f popl
1445 where pen.pl_id = p_pl_id
1446 and (c_pgm_id is null or pen.pgm_id = c_pgm_id )
1447 and (c_oipl_id is null or pen.oipl_id = c_oipl_id )
1448 and pen.person_id = p_person_id
1449 and pen.prtt_enrt_rslt_stat_cd is null
1450 and pen.enrt_cvg_thru_dt >= pen.effective_end_date
1451 --and enrt_cvg_thru_dt < p_report_start_date --p_run_date
1452 and pen.sspndd_flag = 'N'
1453 and pil.per_in_ler_id = pen.per_in_ler_id
1454 and pil.per_in_ler_stat_cd in ('PROCD' , 'STRTD')
1455 and pil.lf_evt_ocrd_dt = ( select max(pil2.LF_EVT_OCRD_DT)
1456 from ben_per_in_ler pil2
1457 where pil2.per_in_ler_stat_cd in ( 'PROCD' , 'STRTD')
1458 and pil2.lf_evt_ocrd_dt < p_report_start_date
1459 and pil2.person_id = p_person_id )
1460 and prm.effective_start_date = (select max(prm2.effective_start_date)
1461 from ben_prtt_prem_f prm2
1462 where prm2.prtt_prem_id = prm.prtt_prem_id
1463 and prm2.effective_start_date <= p_report_start_date )
1464 and prm.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1465 --and p_report_start_date >= prm.effective_end_date
1466 and prm.prtt_prem_id = popl.prtt_prem_id
1467 and popl.yr_num||lpad(popl.mo_num,2,0) = to_char(add_months(last_day(p_report_start_date),-1),'YYYYMM')
1468 and popl.business_group_id = p_business_group_id;
1469 --
1470 cursor c_rate(c_prtt_enrt_rslt_id number) is
1471 select sum(prv.cmcd_rt_val)
1472 from ben_prtt_rt_val prv
1473 where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1474 and tx_typ_cd in ('PRETAX' , 'AFTERTAX' , 'NOTAPPLICABLE')
1475 and acty_typ_cd in ('EEPYC','ERC', 'ERPYC')
1476 and add_months(last_day(p_report_start_date),-1) between rt_strt_dt and rt_end_dt;
1477 --
1478 cursor c_mult_enrt is
1479 select count(*)
1480 from ben_prtt_enrt_rslt_f pen
1481 where pen.pl_id = p_pl_id
1482 and pen.person_id = p_person_id
1483 and pen.prtt_enrt_rslt_stat_cd is null
1484 and pen.business_group_id = p_business_group_id
1485 and pen.enrt_cvg_thru_dt <= pen.effective_end_date
1486 and p_run_date between pen.enrt_cvg_strt_dt and enrt_cvg_thru_dt
1487 and pen.sspndd_flag = 'N' ;
1488 --
1489 l_premium number := 0;
1490 l_rate number := 0;
1491 l_dummy number;
1492 l_pgm_id number;
1493 l_oipl_id number;
1494 l_prtt_enrt_rslt_id number;
1495 --
1496 BEGIN
1497 --
1498 /*
1499 open c_mult_enrt;
1500 fetch c_mult_enrt into l_dummy;
1501 close c_mult_enrt;
1502 --
1503 if l_dummy > 1 then
1504 -- person enrolled in multiple comp objects having the same plan
1505 l_pgm_id := p_pgm_id;
1506 l_oipl_id := p_oipl_id;
1507 end if;
1508 --
1509 */
1510 open c_premium(l_pgm_id , l_oipl_id );
1511 fetch c_premium into l_premium, l_prtt_enrt_rslt_id;
1512 close c_premium;
1513 --
1514 if p_return_type = 'PREMIUM' then
1515 return l_premium;
1516 elsif p_return_type = 'RATE' then
1517 --
1518 open c_rate(l_prtt_enrt_rslt_id);
1519 fetch c_rate into l_rate;
1520 close c_rate;
1521 --
1522 return nvl(l_rate,0);
1523 end if;
1524 --
1525 END old_premium_val;
1526 --
1527 -- ============================================================================
1528 -- <<get_new_rates>>
1529 -- ============================================================================
1530 --
1531 FUNCTION get_new_rates
1532 (p_prtt_enrt_rslt_id number,
1533 p_report_start_date date,
1534 p_run_date date,
1535 p_business_group_id number,
1536 p_return_type varchar2, -- ('ELEMENT','RATE')
1537 p_per_in_ler_id number
1538 ) RETURN NUMBER is
1539 --
1540 cursor c_rate(c_prtt_enrt_rslt_id number) is
1541 select sum(prv.cmcd_rt_val)
1542 from ben_prtt_rt_val prv
1543 where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1544 and tx_typ_cd in ('PRETAX' , 'AFTERTAX' , 'NOTAPPLICABLE' )
1545 and acty_typ_cd in ('EEPYC','ERC' , 'ERPYC' )
1546 and prv.per_in_ler_id = p_per_in_ler_id;
1547
1548 --and p_run_date between rt_strt_dt and rt_end_dt;
1549 --
1550 cursor c_total_elem_entry(c_prtt_enrt_rslt_id number) is
1551 select sum(screen_entry_value)
1552 from ben_prtt_rt_val prv,
1553 pay_element_entry_values_f env
1554 where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1555 and env.element_entry_value_id = prv.element_entry_value_id
1556 and tx_typ_cd in ('PRETAX' , 'AFTERTAX' , 'NOTAPPLICABLE' )
1557 and acty_typ_cd in ('EEPYC','ERC' , 'ERPYC')
1558 and prv.per_in_ler_id = p_per_in_ler_id
1559 and p_run_date between env.effective_start_date and env.effective_end_date;
1560 --
1561
1562 --
1563 l_element number := 0;
1564 l_rate number := 0;
1565 l_dummy number;
1566 l_pgm_id number;
1567 l_oipl_id number;
1568 l_prtt_enrt_rslt_id number;
1569 --
1570 BEGIN
1571 --
1572 --
1573 if p_return_type = 'ELEMENT' then
1574 open c_total_elem_entry(p_prtt_enrt_rslt_id);
1575 fetch c_total_elem_entry into l_element;
1576 close c_total_elem_entry;
1577
1578 return nvl(l_element,0);
1579 elsif p_return_type = 'RATE' then
1580 --
1581 open c_rate(p_prtt_enrt_rslt_id);
1582 fetch c_rate into l_rate;
1583 close c_rate;
1584 --
1585 return nvl(l_rate,0);
1586 end if;
1587 --
1588 END get_new_rates;
1589 --
1590 -- ============================================================================
1591 -- <<get_change_eff_dt>>
1592 -- ============================================================================
1593 --
1594 FUNCTION get_change_eff_dt
1595 (p_prtt_enrt_rslt_id number,
1596 p_report_start_date date,
1597 p_run_date date
1598 ) RETURN date is
1599 --
1600 cursor c_rate(c_prtt_enrt_rslt_id number) is
1601 select min(prv.rt_strt_dt)
1602 from ben_prtt_rt_val prv
1603 where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1604 and tx_typ_cd in ('PRETAX' , 'AFTERTAX')
1605 and acty_typ_cd in ('EEPYC','ERC') ;
1606 --and p_report_start_date <= rate_strt_dt;
1607 --
1608
1609 --
1610 l_change_eff_date date;
1611 --
1612 BEGIN
1613 --
1614 --
1615 open c_rate(p_prtt_enrt_rslt_id);
1616 fetch c_rate into l_change_eff_date;
1617 close c_rate;
1618
1619 return l_change_eff_date;
1620 --
1621 END GET_CHANGE_EFF_DT;
1622 --
1623 -- ============================================================================
1624 -- <<get_element_val>>
1625 -- ============================================================================
1626 --
1627 FUNCTION get_element_val
1628 (p_prtt_enrt_rslt_id number,
1629 p_run_date date,
1630 p_business_group_id number,
1631 p_tx_typ_cd varchar2,
1632 p_acty_typ_cd varchar2,
1633 p_per_in_ler_id number,
1634 p_run_date_end date default null -- 3608119
1635 ) RETURN NUMBER is
1636 --
1637 cursor c_element_val is
1638 select sum(env.screen_entry_value)
1639 from ben_prtt_rt_val prv,
1640 pay_element_entry_values_f env
1641 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1642 and (p_tx_typ_cd is null or prv.tx_typ_cd = p_tx_typ_cd)
1643 and prv.acty_typ_cd = p_acty_typ_cd
1644 and prv.prtt_rt_val_stat_cd is null
1645 --and p_run_date between prv.rt_strt_dt and prv.rt_end_dt
1646 and prv.per_in_ler_id = p_per_in_ler_id
1647 and env.element_entry_value_id = prv.element_entry_value_id
1648 and prv.rt_strt_dt between env.effective_start_date and env.effective_end_date
1649 and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
1650 or
1651 nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt
1652 );-- 3608119
1653 --
1654 cursor c_er_element_val is
1655 select sum(env.screen_entry_value)
1656 from ben_prtt_rt_val prv,
1657 pay_element_entry_values_f env
1658 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1659 and (p_tx_typ_cd is null or prv.tx_typ_cd = p_tx_typ_cd)
1660 and prv.acty_typ_cd in ('ERC' , 'ERPYC' )
1661 and prv.prtt_rt_val_stat_cd is null
1662 --and p_run_date between prv.rt_strt_dt and prv.rt_end_dt
1663 and prv.per_in_ler_id = p_per_in_ler_id
1664 and env.element_entry_value_id = prv.element_entry_value_id
1665 and prv.rt_strt_dt between env.effective_start_date and env.effective_end_date
1666 and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
1667 or
1668 nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt
1669 );
1670 --
1671 l_element_value number;
1672 --
1673 BEGIN
1674 --
1675 if p_acty_typ_cd = 'ERC'
1676 then
1677 open c_er_element_val;
1678 fetch c_er_element_val into l_element_value;
1679 close c_er_element_val;
1680 else
1681 open c_element_val;
1682 fetch c_element_val into l_element_value;
1683 close c_element_val;
1684 end if;
1685
1686 return nvl(l_element_value , 0);
1687 --
1688 end GET_ELEMENT_VAL;
1689 --
1690 -- ============================================================================
1691 -- <<get_rate_val>>
1692 -- ============================================================================
1693 --
1694 FUNCTION get_rate_val
1695 (p_prtt_enrt_rslt_id number,
1696 p_run_date date,
1697 p_business_group_id number,
1698 p_tx_typ_cd varchar2,
1699 p_acty_typ_cd varchar2,
1700 p_per_in_ler_id number,
1701 p_run_date_end date default null-- 3608119
1702 ) RETURN NUMBER is
1703 --
1704 cursor c_rate_val is
1705 select sum(prv.cmcd_rt_val)
1706 from ben_prtt_rt_val prv
1707 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1708 and (p_tx_typ_cd is null or prv.tx_typ_cd = p_tx_typ_cd)
1709 and prv.acty_typ_cd = p_acty_typ_cd
1710 and prv.per_in_ler_id = p_per_in_ler_id
1711 and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
1712 or
1713 nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt
1714 )-- 3608119
1715 ;
1716 cursor c_er_rate_val is
1717 select sum(prv.cmcd_rt_val)
1718 from ben_prtt_rt_val prv
1719 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1720 and (p_tx_typ_cd is null or prv.tx_typ_cd = p_tx_typ_cd)
1721 and prv.acty_typ_cd in ('ERC' , 'ERPYC' )
1722 and prv.per_in_ler_id = p_per_in_ler_id
1723 and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
1724 or
1725 nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt
1726 );
1727 --
1728 l_rate_value number;
1729 --
1730 BEGIN
1731 --
1732 if p_acty_typ_cd = 'ERC' then
1733 open c_er_rate_val;
1734 fetch c_er_rate_val into l_rate_value ;
1735 close c_er_rate_val;
1736 else
1737 open c_rate_val;
1738 fetch c_rate_val into l_rate_value ;
1739 close c_rate_val;
1740 end if;
1741
1742 return nvl(l_rate_value , 0);
1743 --
1744 end get_rate_val;
1745 --
1746 -- ============================================================================
1747 -- <<get_prtt_rate_val>>
1748 -- ============================================================================
1749 --
1750 FUNCTION get_prtt_rate_val
1751 (p_prtt_enrt_rslt_id number,
1752 p_run_date date,
1753 p_per_in_ler_id number,
1754 p_run_date_end date default null -- 3608119
1755 ) RETURN NUMBER is
1756 --
1757 cursor c_prtt_defn_amt is
1758 select sum(rt_val) rate
1759 from ben_prtt_rt_val prv
1760 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1761 and tx_typ_cd in ('PRETAX' , 'AFTERTAX', 'NOTAPPLICABLE')
1762 and acty_typ_cd in ('EEPYC','ERC')
1763 and prv.per_in_ler_id = p_per_in_ler_id
1764 and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
1765 or
1766 nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt)--3608119
1767 ;
1768 --
1769 l_rate_value number;
1770 --
1771 BEGIN
1772 --
1773 open c_prtt_defn_amt;
1774 fetch c_prtt_defn_amt into l_rate_value ;
1775 close c_prtt_defn_amt;
1776
1777 return nvl(l_rate_value , 0);
1778 --
1779 end get_prtt_rate_val;
1780
1781 --
1782 -- ============================================================================
1783 -- <<report_header>>
1784 -- ============================================================================
1785 --
1786 procedure report_header
1787 (p_run_date IN date,
1788 p_person_id IN number,
1789 p_emp_name_format IN varchar2,
1790 p_pgm_id IN number,
1791 p_pl_id IN number,
1792 p_per_sel_rule_id IN number,
1793 p_business_group_id IN number,
1794 p_organization_id IN number,
1795 p_location_id IN number,
1796 p_benfts_grp_id IN number,
1797 p_rptg_grp_id IN number,
1798 p_prem_type IN varchar2,
1799 p_payroll_id IN number,
1800 p_output_typ IN varchar2,
1801 p_dsply_pl_disc_rep IN varchar2,
1802 p_dsply_pl_recn_rep IN varchar2,
1803 p_dsply_pl_prtt_rep IN varchar2,
1804 p_dsply_prtt_reps IN varchar2,
1805 p_dsply_lfe_rep IN varchar2,
1806 p_ret_person OUT NOCOPY varchar2,
1807 p_ret_emp_name_format OUT NOCOPY varchar2,
1808 p_ret_pgm OUT NOCOPY varchar2,
1809 p_ret_pl OUT NOCOPY varchar2,
1810 p_ret_per_sel_rule OUT NOCOPY varchar2,
1811 p_ret_business_group OUT NOCOPY varchar2,
1812 p_ret_organization OUT NOCOPY varchar2,
1813 p_ret_location OUT NOCOPY varchar2,
1814 p_ret_benfts_grp OUT NOCOPY varchar2,
1815 p_ret_rptg_grp OUT NOCOPY varchar2,
1816 p_ret_prem_type OUT NOCOPY varchar2,
1817 p_ret_payroll OUT NOCOPY varchar2,
1818 p_ret_output_typ OUT NOCOPY varchar2,
1819 p_ret_dsply_pl_disc_rep OUT NOCOPY varchar2,
1820 p_ret_dsply_pl_recn_rep OUT NOCOPY varchar2,
1821 p_ret_dsply_pl_prtt_rep OUT NOCOPY varchar2,
1822 p_ret_dsply_prtt_reps OUT NOCOPY varchar2,
1823 p_ret_dsply_lfe_rep OUT NOCOPY varchar2)
1824 as
1825 --
1826 cursor c_person is
1827 select per.full_name
1828 from per_all_people_f per
1829 where per.person_id = p_person_id
1830 and p_run_date between nvl(per.effective_start_date,p_run_date)
1831 and nvl(per.effective_end_date,p_run_date);
1832 --
1833
1834 cursor c_formula is
1835 select formula_name
1836 from ff_formulas_f ff
1837 where ff.formula_id(+) = p_per_sel_rule_id
1838 and p_run_date between nvl(ff.effective_start_date,p_run_date)
1839 and nvl(ff.effective_end_date,p_run_date);
1840 --
1841 cursor c_benefits_group is
1842 select name
1843 from ben_benfts_grp
1844 where benfts_grp_id = p_benfts_grp_id;
1845 --
1846 cursor c_reporting_group is
1847 select name
1848 from ben_rptg_grp_v
1849 where rptg_grp_id = p_rptg_grp_id;
1850 --
1851 cursor c_payroll is
1852 select pay.payroll_name
1853 from pay_all_payrolls_f pay
1854 where pay.payroll_id = p_payroll_id
1855 and p_run_date between pay.effective_start_date and pay.effective_end_date ;
1856
1857 --
1858 l_all varchar2(80) := 'All';
1859 l_none varchar2(80) := 'None';
1860 --
1861 BEGIN
1862 --
1863 if p_person_id is not null then
1864 open c_person;
1865 fetch c_person into p_ret_person;
1866 close c_person;
1867 else
1868 p_ret_person := l_all;
1869 end if;
1870 --
1871 p_ret_emp_name_format := hr_general.decode_lookup('BEN_PER_NAME_FMT',p_emp_name_format );
1872 --
1873 if p_pgm_id is not null then
1874 p_ret_pgm := ben_batch_utils.get_pgm_name(p_pgm_id, p_business_group_id,p_run_date);
1875 else
1876 p_ret_pgm := l_all;
1877 end if;
1878 --
1879 p_ret_pl := ben_batch_utils.get_pl_name(p_pl_id,p_business_group_id,p_run_date);
1880 --
1881 if p_per_sel_rule_id is not null then
1882 open c_formula;
1883 fetch c_formula into p_ret_per_sel_rule;
1884 close c_formula;
1885 else
1886 p_ret_per_sel_rule := l_none;
1887 end if;
1888 --
1889 p_ret_business_group := hr_general.decode_organization(p_business_group_id);
1890 --
1891 if p_organization_id is not null then
1892 p_ret_organization := hr_general.decode_organization(p_organization_id);
1893 else
1894 p_ret_organization := l_all;
1895 end if;
1896 --
1897 if p_location_id is not null then
1898 p_ret_location := hr_general.decode_location(p_location_id);
1899 else
1900 p_ret_location := l_all;
1901 end if;
1902 --
1903 if p_benfts_grp_id is not null then
1904 open c_benefits_group;
1905 fetch c_benefits_group into p_ret_benfts_grp;
1906 close c_benefits_group;
1907 else
1908 p_ret_benfts_grp := l_all;
1909 end if;
1910 --
1911 if p_rptg_grp_id is not null then
1912 open c_reporting_group;
1913 fetch c_reporting_group into p_ret_rptg_grp;
1914 close c_reporting_group;
1915 else
1916 p_ret_rptg_grp := l_all;
1917 end if;
1918 --
1919 if p_payroll_id is not null then
1920 open c_payroll;
1921 fetch c_payroll into p_ret_payroll;
1922 close c_payroll;
1923 else
1924 p_ret_payroll := l_all;
1925 end if;
1926 --
1927 if p_prem_type is not null then
1928 p_ret_prem_type := hr_general.decode_lookup('BEN_PRSPCTV_R_RTSPCTV',p_prem_type);
1929 else
1930 p_ret_prem_type := l_all;
1931 end if;
1932 --
1933 p_ret_output_typ := hr_general.decode_lookup('BEN_FILE_OP_TYP',p_output_typ);
1934 p_ret_dsply_pl_disc_rep := hr_general.decode_lookup('YES_NO',p_dsply_pl_disc_rep);
1935 p_ret_dsply_pl_recn_rep := hr_general.decode_lookup('YES_NO',p_dsply_pl_recn_rep);
1936 p_ret_dsply_pl_prtt_rep := hr_general.decode_lookup('YES_NO',p_dsply_pl_prtt_rep);
1937 p_ret_dsply_prtt_reps := hr_general.decode_lookup('YES_NO',p_dsply_prtt_reps);
1938 p_ret_dsply_lfe_rep := hr_general.decode_lookup('YES_NO',p_dsply_lfe_rep);
1939 --
1940 END report_header;
1941 --
1942 end ben_recn_rep;