1 PACKAGE BODY BEN_BENLESUM_XMLP_PKG AS
2 /* $Header: BENLESUMB.pls 120.1 2007/12/10 08:36:35 vjaganat noship $ */
3
4 function cf_transfer_valuesformula(CS_detected_name_potnl_rep in number, CS_unprocessed_name_potnl_rep in number, CS_processed_name_potnl_rep in number,
5 CS_voided_name_potnl_rep in number,
6 CS_settoman_name_potnl_rep in number, CS_manover_name_potnl_rep in number, CS_total_name_potnl_rep in number,
7 CS_detected_name_potnl_comp in number, CS_unprocessed_name_potnl_comp in number, CS_processed_name_potnl_comp in number,
8 CS_voided_name_potnl_comp in number, CS_settoman_name_potnl_comp in number,
9 CS_manover_name_potnl_comp in number, CS_total_name_potnl_comp in number,
10 CS_started_name_processed_rep in number, CS_proc_name_processed_rep in number, CS_backedout_name_proc_rep in number,
11 CS_voided_name_proc_rep in number, CS_total_name_proc_rep in number,
12 CS_started_name_processed_comp in number, CS_proc_name_processed_comp in number,
13 CS_backedout_name_proc_comp in number, CS_voided_name_proc_comp in number, CS_total_name_proc_comp in number) return number is
14
15 cursor c_report_module is
16 Select meaning from hr_lookups where lookup_type = 'BEN_LESUM_REPT_MODL_CD' and lookup_code = p_report_module_cd;
17
18 cursor c_asg_type is
19 Select meaning from hr_lookups where lookup_type = 'BEN_LESUM_ASG_TYPE' and lookup_code = p_assignment_type;
20
21 cursor c_ler_type is
22 Select meaning from hr_lookups where lookup_type = 'BEN_LESUM_LER_TYPE' and lookup_code = p_ler_type;
23
24 cursor c_BG_info is
25 select hr_general.decode_organization(p_organization_id),hr_general.decode_location(p_location_id),hr_general.decode_organization(p_business_group_id) from dual;
26
27 cursor c_benefit_group_name is
28 Select name from BEN_BENFTS_GRP where benfts_grp_id=p_benefit_group_id and business_group_id=p_business_group_id;
29
30 cursor c_reporting_group_name is
31 Select tl.name from ben_rptg_grp rpt,ben_rptg_grp_tl tl where rpt.rptg_grp_id=p_reporting_group_id and business_group_id=p_business_group_id and rpt.rptg_grp_id=tl.rptg_grp_id
32 and tl.language=userenv('LANG');
33
34 cursor c_ler_name is
35 Select tl.name from ben_ler_f ler, ben_ler_f_tl tl
36 where
37 ler.ler_id = tl.ler_id and
38 ler.effective_start_date = tl.effective_start_date and
39 tl.language = userenv('LANG') and
40 ler.ler_id=p_ler_id and business_group_id=p_business_group_id;
41
42 cursor c_person_name is
43 Select full_name from per_all_people_f where person_id=p_person_id and business_group_id=p_business_group_id;
44
45 cursor c_displ_flex is
46 Select meaning from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_disp_flex_fields_flag;
47
48 begin
49
50 CP_detected_name_potnl_rep :=CS_detected_name_potnl_rep;
51 CP_unprocessed_name_potnl_rep :=CS_unprocessed_name_potnl_rep;
52 CP_processed_name_potnl_rep :=CS_processed_name_potnl_rep;
53 CP_voided_name_potnl_rep :=CS_voided_name_potnl_rep;
54 CP_settomanual_name_potnl_rep :=CS_settoman_name_potnl_rep;
55 CP_manover_name_potnl_rep :=CS_manover_name_potnl_rep;
56 CP_total_name_potnl_rep :=CS_total_name_potnl_rep;
57
58
59 CP_detected_name_potnl_comp :=CS_detected_name_potnl_comp;
60 CP_unprocessed_name_potnl_comp :=CS_unprocessed_name_potnl_comp;
61 CP_processed_name_potnl_comp :=CS_processed_name_potnl_comp;
62 CP_voided_name_potnl_comp :=CS_voided_name_potnl_comp;
63 CP_settomanual_name_potnl_comp :=CS_settoman_name_potnl_comp;
64 CP_manover_name_potnl_comp :=CS_manover_name_potnl_comp;
65 CP_total_name_potnl_comp :=CS_total_name_potnl_comp;
66
67
68 CP_started_name_proc_rep :=CS_started_name_processed_rep;
69 CP_processed_name_proc_rep :=CS_proc_name_processed_rep;
70 CP_backedout_name_proc_rep :=CS_backedout_name_proc_rep;
71 CP_voided_name_proc_rep :=CS_voided_name_proc_rep;
72 CP_total_name_proc_rep :=CS_total_name_proc_rep;
73
74 CP_started_name_proc_comp :=CS_started_name_processed_comp;
75 CP_processed_name_proc_comp :=CS_proc_name_processed_comp;
76 CP_backedout_name_proc_comp :=CS_backedout_name_proc_comp;
77 CP_voided_name_proc_comp :=CS_voided_name_proc_comp;
78 CP_total_name_proc_comp :=CS_total_name_proc_comp;
79
80 cp_rept_strt_end_dt := to_char(p_rept_perd_strt_dt,p_date_mask)|| ' - '||to_char(p_rept_perd_end_dt,p_date_mask);
81
82 if p_comp_perd_strt_dt is null and p_comp_perd_end_dt is null then
83 cp_comp_strt_end_dt :=null;
84 else
85 cp_comp_strt_end_dt := to_char(p_comp_perd_strt_dt,p_date_mask)|| ' - '||to_char(p_comp_perd_end_dt,p_date_mask);
86 end if;
87
88
89 open c_report_module;
90 fetch c_report_module into CP_report_module_name;
91
92 close c_report_module;
93
94 open c_asg_type ;
95 fetch c_asg_type into CP_asg_type;
96 if cp_asg_type is null then
97 cp_asg_type:='All';
98 end if;
99 close c_asg_type;
100
101 open c_ler_type;
102 fetch c_ler_type into CP_ler_type;
103 if cp_ler_type is null then
104 cp_ler_type:='All';
105 end if;
106 close c_ler_type;
107
108 open c_bg_info;
109 fetch c_bg_info into cp_organization_name,cp_location_name,cp_business_group_name;
110 if cp_organization_name is null then
111 cp_organization_name:='All';
112 end if;
113 if cp_location_name is null then
114 cp_location_name:='All';
115 end if;
116 close c_bg_info;
117
118
119 open c_reporting_group_name;
120 fetch c_reporting_group_name into cp_reporting_group_name;
121 if cp_reporting_group_name is null then
122 cp_reporting_group_name:='All';
123 end if;
124 close c_reporting_group_name;
125
126
127 open c_benefit_group_name;
128 fetch c_benefit_group_name into cp_benefit_group_name;
129 if cp_benefit_group_name is null then
130 cp_benefit_group_name:='All';
131 end if;
132 close c_benefit_group_name;
133
134
135 if p_pl_id is not null then
136 cp_pln_name:=ben_batch_utils.get_pl_name(p_pl_id, p_business_group_id,p_run_date);
137 if cp_pln_name='PLAN NOT FOUND' then
138 cp_pln_name:='All';
139 end if;
140 else
141 cp_pln_name:='All';
142 end if;
143
144
145 open c_person_name;
146 fetch c_person_name into cp_person_name;
147 if cp_person_name is null then
148 cp_person_name:='All';
149 end if;
150 close c_person_name;
151
152 open c_ler_name;
153 fetch c_ler_name into cp_ler_name;
154 if cp_ler_name is null then
155 cp_ler_name:='All';
156 end if;
157 close c_ler_name;
158
159 open c_displ_flex;
160 fetch c_displ_flex into cp_displ_flex;
161 close c_displ_flex;
162
163
164 if p_nat_ident is null then
165 cp_nat_ident:='All';
166 else
167 cp_nat_ident:=p_nat_ident;
168 end if;
169
170
171
172 return 1;
173 end;
174
175 function AfterPForm return boolean is
176 begin
177
178
179 if ((p_rept_perd_strt_dt is not null) and (p_rept_perd_end_dt is null))
180 or ((p_rept_perd_strt_dt is null) and (p_rept_perd_end_dt is not null)) then
181 fnd_message.set_name('BEN','BEN_93336_LES_STRT_END_DT_NULL');
182 fnd_message.set_token('PARAM','Reporting',TRUE);
183 p_error := fnd_message.get;
184 p_run_report:='N';
185 end if;
186
187 if ((p_comp_perd_strt_dt is not null) and (p_comp_perd_end_dt is null))
188 or ((p_comp_perd_strt_dt is null) and (p_comp_perd_end_dt is not null)) then
189
190 fnd_message.set_name('BEN','BEN_93336_LES_STRT_END_DT_NULL');
191 fnd_message.set_token('PARAM','Comparison',TRUE);
192 p_run_report:='N';
193 p_error := fnd_message.get;
194 end if;
195
196
197 if (p_pl_id is not null and p_reporting_group_id is not null) then
198 declare
199 l_dummy varchar2(1);
200 l_rpt_grp_name varchar2(300):=null;
201 cursor c_pl is select null from ben_popl_rptg_grp_f rgr
202 where rgr.rptg_grp_id = p_reporting_group_id
203 and rgr.pl_id=p_pl_id
204 and p_run_date between rgr.effective_start_date and rgr.effective_end_date;
205 cursor c_rpt_grp is Select tl.name from ben_rptg_grp rpt,ben_rptg_grp_tl tl
206 where rpt.rptg_grp_id=p_reporting_group_id and
207 business_group_id=p_business_group_id and rpt.rptg_grp_id=tl.rptg_grp_id
208 and tl.language=userenv('LANG');
209
210 begin
211 open c_rpt_grp;
212 fetch c_rpt_grp into l_rpt_grp_name;
213 close c_rpt_grp;
214 open c_pl;
215 fetch c_pl into l_dummy;
216 if c_pl%found then
217 close c_pl;
218 else
219 fnd_message.set_name('BEN','BEN_93334_PL_NOT_IN_RPTG_GRP');
220 fnd_message.set_token('PL_NAME',ben_batch_utils.get_pl_name(p_pl_id, p_business_group_id,p_run_date));
221 fnd_message.set_token('RPTG_GRP',l_rpt_grp_name);
222 p_run_report:='N';
223 p_error := fnd_message.get;
224 close c_pl;
225 end if;
226 end;
227
228 end if;
229
230
231
232 if ((p_rept_perd_strt_dt is not null) and (p_rept_perd_end_dt is not null)) then
233 if p_rept_perd_strt_dt > p_rept_perd_end_dt then
234 fnd_message.set_name('BEN','BEN_93335_LES_STRT_GRT_END_DT');
235 fnd_message.set_token('PARAM','Reporting',TRUE);
236 p_run_report:='N';
237 p_error := fnd_message.get;
238 end if;
239 end if;
240
241
242 if ((p_comp_perd_strt_dt is not null) and (p_comp_perd_end_dt is not null)) then
243 if p_comp_perd_strt_dt > p_comp_perd_end_dt then
244 fnd_message.set_name('BEN','BEN_93335_LES_STRT_GRT_END_DT');
245 fnd_message.set_token('PARAM','Comparison',TRUE);
246 p_run_report:='N';
247 p_error := fnd_message.get;
248 end if;
249 end if;
250
251
252
253
254
255
256 if p_sort_order_1 is not null then
257 append_order_by(p_sort_order_1);
258 end if;
259 if p_sort_order_2 is not null then
260 append_order_by(p_sort_order_2);
261 end if;
262 if p_sort_order_3 is not null then
263 append_order_by(p_sort_order_3);
264 end if;
265 if p_sort_order_4 is not null then
266 append_order_by(p_sort_order_4);
267 end if;
268
269
270 if p_sort is null then
271 p_sort :=' order by '||' PERNAME';
272 p_sort1 :=' order by '||' PERNAME'; --Added during DT Fixes
273 p_user_sort:=hr_general.decode_lookup('BEN_LESUM_SORT_ORDR', 'PERNAME');
274 else
275 p_sort:=' order by '||p_sort;
276 p_sort1:=' order by '||p_sort1; --Added during DT Fixes
277 end if;
278 p_sort_dff := p_sort|| ',PER_IN_LER_ID, COUNTER ';
279
280 p_sort_dff2 := ' order by '||p_sort_dff2|| ',PER_IN_LER_ID, COUNTER '; --Added during DT Fixes
281 p_sort_dff3 := ' order by '||p_sort_dff3|| ',PER_IN_LER_ID1, COUNTER1 '; --Added during DT Fixes
282
283
284
285 return (TRUE);
286 end;
287
288 PROCEDURE append_order_by(colname varchar2) IS
289 BEGIN
290
291 if colname is not null and p_sort is not null then
292 p_sort :=p_sort||' , '||colname;
293 p_user_sort:=p_user_sort||' , '||hr_general.decode_lookup('BEN_LESUM_SORT_ORDR',colname);
294 --Added for DT Fixes
295 p_sort1 :=p_sort1||' , '||colname||'1';
296 p_sort_dff2 := p_sort_dff2||' , '||colname||'2';
297 p_sort_dff3 := p_sort_dff3||' , '||colname||'3';
298 --End of add during DT Fixes
299 elsif colname is not null and p_sort is null then
300 p_sort:=colname;
301 --Added for DT Fixes
302 p_sort1 :=colname||'1';
303 p_sort_dff2 :=colname||'2';
304 p_sort_dff3 :=colname||'3';
305 --End of add during DT Fixes
306 p_user_sort:=hr_general.decode_lookup('BEN_LESUM_SORT_ORDR',colname);
307 end if;
308 END;
309
310 FUNCTION FIND_COL(col_name varchar2) RETURN varchar2 IS
311
312 BEGIN
313 if col_name='PERNAME' then
314 return('FULL_NAME_REP');
315 end if;
316 if col_name='PERSSN' then
317 return('SSN_rep');
318 end if;
319 if col_name='LERNAME' then
320 return('ler_name_plan_rep');
321 end if;
322 if col_name='LESTAT' then
323 return('ler_status_plan_rep');
324 end if;
325 if col_name='LERTYPE' then
326 return('le_type_plan_rep');
327 end if;
328 if col_name='PERLOC' then
329 return('loc_code_plan_rep');
330 end if;
331 if col_name='LEOCRDDT' then
332 return('lf_evt_dt_plan_rep');
333 end if;
334 return(null);
335 END;
336
337 function BeforeReport return boolean is
338 begin
339 /*srw.user_exit('FND SRWINIT');*/null;
340 p_date_mask := fnd_profile.value('ICX_DATE_FORMAT_MASK');
341
342 T_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
343 T_RUN_DATE := to_char(P_RUN_DATE,p_date_mask);
344 T_COMP_PERD_STRT_DT := to_char(P_COMP_PERD_STRT_DT,p_date_mask);
345 T_COMP_PERD_END_DT := to_char(P_COMP_PERD_END_DT,p_date_mask);
346 T_REPT_PERD_STRT_DT := to_char(P_REPT_PERD_STRT_DT,p_date_mask);
347 T_REPT_PERD_END_DT := to_char(P_REPT_PERD_END_DT,p_date_mask);
348
349
350
351 return (TRUE);
352 end;
353
354 function AfterReport return boolean is
355 begin
356 /*srw.user_exit('FND SRWEXIT');*/null;
357
358 return (TRUE);
359 end;
360
361 --Functions to refer Oracle report placeholders--
362
363 Function CP_detected_name_potnl_rep_p return number is
364 Begin
365 return CP_detected_name_potnl_rep;
366 END;
367 Function CP_unprocessed_name_potnl_rep1 return number is
368 Begin
369 return CP_unprocessed_name_potnl_rep;
370 END;
371 Function CP_processed_name_potnl_rep_p return number is
372 Begin
373 return CP_processed_name_potnl_rep;
374 END;
375 Function CP_total_name_potnl_rep_p return number is
376 Begin
377 return CP_total_name_potnl_rep;
378 END;
379 Function CP_voided_name_potnl_rep_p return number is
380 Begin
381 return CP_voided_name_potnl_rep;
382 END;
383 Function CP_settomanual_name_potnl_rep1 return number is
384 Begin
385 return CP_settomanual_name_potnl_rep;
386 END;
387 Function CP_manover_name_potnl_rep_p return number is
388 Begin
389 return CP_manover_name_potnl_rep;
390 END;
391 Function CP_detected_name_potnl_comp_p return number is
392 Begin
393 return CP_detected_name_potnl_comp;
394 END;
395 Function CP_unprocessed_name_potnl_com return number is
396 Begin
397 return CP_unprocessed_name_potnl_comp;
398 END;
399 Function CP_processed_name_potnl_comp_p return number is
400 Begin
401 return CP_processed_name_potnl_comp;
402 END;
403 Function CP_total_name_potnl_comp_p return number is
404 Begin
405 return CP_total_name_potnl_comp;
406 END;
407 Function CP_voided_name_potnl_comp_p return number is
408 Begin
409 return CP_voided_name_potnl_comp;
410 END;
411 Function CP_settomanual_name_potnl_com return number is
412 Begin
413 return CP_settomanual_name_potnl_comp;
414 END;
415 Function CP_manover_name_potnl_comp_p return number is
416 Begin
417 return CP_manover_name_potnl_comp;
418 END;
419 Function CP_started_name_proc_rep_p return number is
420 Begin
421 return CP_started_name_proc_rep;
422 END;
423 Function CP_processed_name_proc_rep_p return number is
424 Begin
425 return CP_processed_name_proc_rep;
426 END;
427 Function CP_backedout_name_proc_rep_p return number is
428 Begin
429 return CP_backedout_name_proc_rep;
430 END;
431 Function CP_voided_name_proc_rep_p return number is
432 Begin
433 return CP_voided_name_proc_rep;
434 END;
435 Function CP_total_name_proc_rep_p return number is
436 Begin
437 return CP_total_name_proc_rep;
438 END;
439 Function CP_started_name_proc_comp_p return number is
440 Begin
441 return CP_started_name_proc_comp;
442 END;
443 Function CP_processed_name_proc_comp_p return number is
444 Begin
445 return CP_processed_name_proc_comp;
446 END;
447 Function CP_backedout_name_proc_comp_p return number is
448 Begin
449 return CP_backedout_name_proc_comp;
450 END;
451 Function CP_voided_name_proc_comp_p return number is
452 Begin
453 return CP_voided_name_proc_comp;
454 END;
455 Function CP_total_name_proc_comp_p return number is
456 Begin
457 return CP_total_name_proc_comp;
458 END;
459 Function CP_report_module_name_p return varchar2 is
460 Begin
461 return CP_report_module_name;
462 END;
463 Function CP_asg_type_p return varchar2 is
464 Begin
465 return CP_asg_type;
466 END;
467 Function CP_ler_type_p return varchar2 is
468 Begin
469 return CP_ler_type;
470 END;
471 Function CP_business_group_name_p return varchar2 is
472 Begin
473 return CP_business_group_name;
474 END;
475 Function CP_location_name_p return varchar2 is
476 Begin
477 return CP_location_name;
478 END;
479 Function CP_organization_name_p return varchar2 is
480 Begin
481 return CP_organization_name;
482 END;
483 Function CP_reporting_group_name_p return varchar2 is
484 Begin
485 return CP_reporting_group_name;
486 END;
487 Function CP_benefit_group_name_p return varchar2 is
488 Begin
489 return CP_benefit_group_name;
490 END;
491 Function CP_pln_name_p return varchar2 is
492 Begin
493 return CP_pln_name;
494 END;
495 Function CP_ler_name_p return varchar2 is
496 Begin
497 return CP_ler_name;
498 END;
499 Function CP_person_name_p return varchar2 is
500 Begin
501 return CP_person_name;
502 END;
503 Function CP_nat_ident_p return varchar2 is
504 Begin
505 return CP_nat_ident;
506 END;
507 Function CP_displ_flex_p return varchar2 is
508 Begin
509 return CP_displ_flex;
510 END;
511 Function CP_rept_strt_end_dt_p return varchar2 is
512 Begin
513 return CP_rept_strt_end_dt;
514 END;
515 Function CP_comp_strt_end_dt_p return varchar2 is
516 Begin
517 return CP_comp_strt_end_dt;
518 END;
519 END BEN_BENLESUM_XMLP_PKG ;
[Home] [Help]