[Home] [Help]
PACKAGE BODY: APPS.BEN_BENRECON_XMLP_PKG
Source
1 PACKAGE BODY BEN_BENRECON_XMLP_PKG AS
2 /* $Header: BENRECONB.pls 120.1 2007/12/10 08:38:02 vjaganat noship $ */
3
4 function Format_Mask(p_uom varchar2) return varchar2 is
5 v_format_mask varchar2(50) := null;
6 v_field_length number(3) := 22;
7 begin
8 v_format_mask := fnd_currency.get_format_mask(p_uom, v_field_length);
9
10 return v_format_mask;
11 end;
12
13 function AfterPForm return boolean is
14 l_plip_name varchar2(1000);
15 l_date varchar2(50);
16 l_date_dsply boolean;
17
18 l_rep_start_date date;
19 l_rep_end_date date;
20 l_op_file_name varchar2(200);
21 begin
22
23 T_DSPLY_PL_RECN_REP := P_DSPLY_PL_RECN_REP;
24 T_DSPLY_PL_DISC_REP := P_DSPLY_PL_DISC_REP;
25 T_DSPLY_PL_PRTT_REP := P_DSPLY_PL_PRTT_REP;
26 T_DSPLY_LFE_REP := P_DSPLY_LFE_REP;
27
28
29 p_run_date := p_report_end_date;
30 if (p_person_id is not null ) and
31 (p_per_sel_rule is not null) then
32 fnd_message.set_name('BEN','BEN_91745_RULE_AND_PERSON');
33 fnd_message.set_token('PROC','BEN_RECN_REP');
34 fnd_message.set_token('PERSON_ID',p_person_id);
35 fnd_message.set_token('PER_SELECT_RL',p_per_sel_rule);
36 p_error := fnd_message.get;
37 p_dsply_disc := 'N';
38 p_dsply_recn := 'N';
39 p_dsply_lfe := 'N';
40 p_dsply_pl_prtt := 'N';
41 return (true);
42 end if;
43
44 if p_per_sel_rule is not null then
45 ben_recn_rep.exec_per_selection_rule
46 (p_pl_id => p_pl_id,
47 p_pgm_id => p_pgm_id,
48 p_business_group_id => p_business_group_id,
49 p_run_date => p_run_date,
50 p_report_start_date => p_report_start_date,
51 p_prem_type => p_prem_type,
52 p_payroll_id => p_payroll_id,
53 p_organization_id => p_organization_id,
54 p_location_id => p_location_id,
55 p_benfts_grp_id => p_benfts_grp_id,
56 p_rptg_grp_id => p_rptg_grp_id,
57 p_person_selection_rule_id => p_per_sel_rule,
58 p_benefit_action_id => p_benefit_action_id);
59 end if;
60
61 p_rep_st_dt := p_report_start_date;
62 p_rep_end_dt := p_report_end_date;
63
64 if ceil(months_between(p_report_end_date,p_report_start_date)) = 1 then
65 l_date_dsply := TRUE;
66 else
67 l_date_dsply := FALSE;
68 end if;
69 if not l_date_dsply or p_dsply_prtt_reps = 'N' then
70 p_dsply_disc := 'N';
71 p_dsply_recn := 'N';
72 p_dsply_lfe := 'N';
73 p_dsply_pl_prtt := 'Y';
74 else
75 p_dsply_disc := p_dsply_pl_disc_rep;
76 p_dsply_recn := p_dsply_pl_recn_rep;
77 p_dsply_lfe := p_dsply_lfe_rep;
78 p_dsply_pl_prtt := p_dsply_pl_prtt_rep;
79 end if;
80 if p_output_typ = 'CSV' then
81 ben_recn_rep.recon_report
82 (p_pl_id => p_pl_id ,
83 p_pgm_id => p_pgm_id ,
84 p_person_id => p_person_id ,
85 p_per_sel_rule => p_per_sel_rule ,
86 p_business_group_id => p_business_group_id ,
87 p_benefit_action_id => p_benefit_action_id ,
88 p_organization_id => p_organization_id ,
89 p_location_id => p_location_id ,
90 p_ntl_identifier => p_ntl_identifier ,
91 p_rptg_grp_id => p_rptg_grp_id ,
92 p_benfts_grp_id => p_benfts_grp_id ,
93 p_run_date => p_run_date ,
94 p_report_start_date => p_report_start_date ,
95 p_report_end_date => p_report_end_date ,
96 p_prem_type => p_prem_type ,
97 p_payroll_id => p_payroll_id ,
98 p_dsply_pl_disc_rep => p_dsply_pl_disc_rep ,
99 p_dsply_pl_recn_rep => p_dsply_pl_recn_rep ,
100 p_dsply_pl_prtt_rep => p_dsply_pl_prtt_rep ,
101 p_dsply_prtt_reps => p_dsply_prtt_reps ,
102 p_dsply_lfe_rep => p_dsply_lfe_rep ,
103 p_emp_name_format => p_emp_name_format ,
104 p_conc_request_id => p_conc_request_id ,
105 p_rep_st_dt => p_rep_st_dt ,
106 p_rep_end_dt => p_rep_end_dt ,
107 p_dsply_recn => p_dsply_recn ,
108 p_dsply_disc => p_dsply_disc ,
109 p_dsply_lfe => p_dsply_lfe ,
110 p_dsply_pl_prtt => p_dsply_pl_prtt ,
111 p_output_typ => p_output_typ ,
112 p_op_file_name => l_op_file_name );
113 p_op_file_name := l_op_file_name;
114 p_dsply_disc := 'N';
115 p_dsply_recn := 'N';
116 p_dsply_lfe := 'N';
117 p_dsply_pl_prtt := 'N';
118
119 end if;
120
121
122
123 return (TRUE);
124 end;
125
126 function cf_levelformula(levels in varchar2) return number is
127 l_return number;
128 begin
129 if levels = 'OIPL' then
130 p_plan_prtt_subtotal_name := 'Premium for Option in Plan :';
131 l_return := 1;
132 else
133 p_plan_prtt_subtotal_name := 'Premium for Plan :';
134 l_return := 2;
135 end if;
136 return l_return;
137 end;
138
139 function cf_uomformula(pl_sql_uom in varchar2) return number is
140 begin
141 if pl_uom is null then
142 pl_uom := pl_sql_uom;
143 end if;
144 return 1;
145 end;
146
147 function CF_LFE_PRTT_COUNTFormula return Number is
148 begin
149 cp_lfe_prtt_count := cp_lfe_prtt_count + 1;
150 return 0;
151 end;
152
153 function cf_1formula(uom2 in varchar2) return number is
154 begin
155 cp_pl_prtt_count := cp_pl_prtt_count + 1;
156 if pl_uom is null then
157 pl_uom := uom2;
158 end if;
159 return 1;
160 end;
161
162 function cf_dsicrepencyformula(pay_perd_total1 in number, actual_total1 in number, pl_prem_val1 in number, pl_sql_uom1 in varchar2) return char is
163 l_output varchar2(100);
164 begin
165 if pay_perd_total1 <> actual_total1 and
166 actual_total1 <> pl_prem_val1 then
167 l_output := 'SP' ; elsif pay_perd_total1 <> actual_total1 then
168 l_output := 'S'; elsif actual_total1 <> pl_prem_val1 then
169 l_output := 'P'; end if;
170 cp_pl_recn_prtt_count := cp_pl_recn_prtt_count + 1;
171 if pl_uom is null then
172 pl_uom := pl_sql_uom1;
173 end if;
174 return l_output;
175 end;
176
177 function CF_DISC_PRTT_COUNTFormula return Number is
178 begin
179 cp_disc_prtt_count := cp_disc_prtt_count + 1;
180 return 1;
181 end;
182
183 function CF_headerFormula return Number is
184 l_person per_all_people_f.full_name%type;
185 l_emp_name_format varchar2(80);
186 l_pgm ben_pgm_f.name%type;
187 l_pl ben_pl_f.name%type;
188 l_per_sel_rule ff_formulas_f.formula_name%type;
189 l_business_group varchar2(250);
190 l_organization varchar2(250);
191 l_location varchar2(250);
192 l_benfts_grp varchar2(250);
193 l_rptg_grp varchar2(250);
194 l_prem_type varchar2(80);
195 l_payroll varchar2(250);
196 l_output_typ varchar2(80);
197 l_dsply_pl_disc_rep varchar2(80);
198 l_dsply_pl_recn_rep varchar2(80);
199 l_dsply_pl_prtt_rep varchar2(80);
200 l_dsply_prtt_reps varchar2(80);
201 l_dsply_lfe_rep varchar2(80);
202 begin
203 ben_recn_rep.report_header
204 (p_run_date => p_run_date
205 ,p_person_id => p_person_id
206 ,p_emp_name_format => p_emp_name_format
207 ,p_pgm_id => p_pgm_id
208 ,p_pl_id => p_pl_id
209 ,p_per_sel_rule_id => p_per_sel_rule
210 ,p_business_group_id => p_business_group_id
211 ,p_organization_id => p_organization_id
212 ,p_location_id => p_location_id
213 ,p_benfts_grp_id => p_benfts_grp_id
214 ,p_rptg_grp_id => p_rptg_grp_id
215 ,p_prem_type => p_prem_type
216 ,p_payroll_id => p_payroll_id
217 ,p_output_typ => p_output_typ
218 ,p_dsply_pl_disc_rep => p_dsply_pl_disc_rep
219 ,p_dsply_pl_recn_rep => p_dsply_pl_recn_rep
220 ,p_dsply_pl_prtt_rep => p_dsply_pl_prtt_rep
221 ,p_dsply_prtt_reps => p_dsply_prtt_reps
222 ,p_dsply_lfe_rep => p_dsply_lfe_rep
223 ,p_ret_person => l_person
224 ,p_ret_emp_name_format => l_emp_name_format
225 ,p_ret_pgm => l_pgm
226 ,p_ret_pl => l_pl
227 ,p_ret_per_sel_rule => l_per_sel_rule
228 ,p_ret_business_group => l_business_group
229 ,p_ret_organization => l_organization
230 ,p_ret_location => l_location
231 ,p_ret_benfts_grp => l_benfts_grp
232 ,p_ret_rptg_grp => l_rptg_grp
233 ,p_ret_prem_type => l_prem_type
234 ,p_ret_payroll => l_payroll
235 ,p_ret_output_typ => l_output_typ
236 ,p_ret_dsply_pl_disc_rep => l_dsply_pl_disc_rep
237 ,p_ret_dsply_pl_recn_rep => l_dsply_pl_recn_rep
238 ,p_ret_dsply_pl_prtt_rep => l_dsply_pl_prtt_rep
239 ,p_ret_dsply_prtt_reps => l_dsply_prtt_reps
240 ,p_ret_dsply_lfe_rep => l_dsply_lfe_rep );
241 cp_person := l_person;
242 cp_emp_name_format := l_emp_name_format;
243 cp_pgm := l_pgm;
244 cp_pl := l_pl;
245 cp_per_sel_rule := l_per_sel_rule;
246 cp_business_group := l_business_group;
247 cp_organization := l_organization;
248 cp_location := l_location;
249 cp_benfts_grp := l_benfts_grp;
250 cp_rptg_grp := l_rptg_grp;
251 cp_prem_type := l_prem_type;
252 cp_payroll := l_payroll;
253 cp_output_typ := l_output_typ ;
254 p_dsply_pl_disc_rep := l_dsply_pl_disc_rep;
255 p_dsply_pl_recn_rep := l_dsply_pl_recn_rep;
256 p_dsply_pl_prtt_rep := l_dsply_pl_prtt_rep;
257 p_dsply_prtt_reps := l_dsply_prtt_reps;
258 p_dsply_lfe_rep := l_dsply_lfe_rep;
259 cp_ntl_identifier := nvl(p_ntl_identifier,'All');
260 return 1;
261 end;
262
263 function BeforeReport return boolean is
264 l_plip_name varchar2(1000);
265 l_date varchar2(50);
266 l_date_dsply boolean;
267 begin
268
269
270
271
272 /*srw.user_exit('FND SRWINIT');*/null;
273
274 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
275 p_date_format := fnd_profile.value('ICX_DATE_FORMAT_MASK');
276
277 if p_pgm_id is not null then
278 l_plip_name := ben_batch_utils.get_pgm_name(p_pgm_id, p_business_group_id,p_run_date) || ' - ';
279 end if;
280 l_plip_name := l_plip_name || ben_batch_utils.get_pl_name(p_pl_id, p_business_group_id,p_run_date);
281 l_date := ' ' || to_char(p_report_start_date,p_date_format)
282 || ' to ' || to_char(p_report_end_date,p_date_format);
283 p_plan_recn_rep_header := l_plip_name ||' '||p_plan_recn_rep_header|| l_date;
284 p_plan_disc_rep_header := l_plip_name ||' '||p_plan_disc_rep_header|| l_date;
285 p_plan_prtt_rep_header := l_plip_name ||' '||p_plan_prtt_rep_header|| l_date;
286 p_lfe_rep_header := p_lfe_rep_header|| l_date;
287
288 return (TRUE);
289 end;
290
291 function AfterReport return boolean is
292 begin
293 /*srw.user_exit('FND SRWEXIT');*/null;
294
295 return (TRUE);
296 end;
297
298 --Functions to refer Oracle report placeholders--
299
300 Function CP_PL_RECN_PRTT_COUNT_p return number is
301 Begin
302 return CP_PL_RECN_PRTT_COUNT;
303 END;
304 Function CP_PL_PRTT_COUNT_p return number is
305 Begin
306 return CP_PL_PRTT_COUNT;
307 END;
308 Function CP_LFE_PRTT_COUNT_p return number is
309 Begin
310 return CP_LFE_PRTT_COUNT;
311 END;
312 Function CP_DISC_PRTT_COUNT_p return number is
313 Begin
314 return CP_DISC_PRTT_COUNT;
315 END;
316 Function CP_DSPLY_RECN_p return varchar2 is
317 Begin
318 return CP_DSPLY_RECN;
319 END;
320 Function CP_DSPLY_DISC_p return varchar2 is
321 Begin
322 return CP_DSPLY_DISC;
323 END;
324 Function CP_DSPLY_LFE_p return varchar2 is
325 Begin
326 return CP_DSPLY_LFE;
327 END;
328 Function CP_DSPLY_PL_PRTT_p return varchar2 is
329 Begin
330 return CP_DSPLY_PL_PRTT;
331 END;
332 Function CP_PERSON_p return varchar2 is
333 Begin
334 return CP_PERSON;
335 END;
336 Function CP_LOCATION_p return varchar2 is
337 Begin
338 return CP_LOCATION;
339 END;
340 Function CP_BENFTS_GRP_p return varchar2 is
341 Begin
342 return CP_BENFTS_GRP;
343 END;
344 Function CP_RPTG_GRP_p return varchar2 is
345 Begin
346 return CP_RPTG_GRP;
347 END;
348 Function CP_PREM_TYPE_p return varchar2 is
349 Begin
350 return CP_PREM_TYPE;
351 END;
352 Function CP_OUTPUT_TYP_p return varchar2 is
353 Begin
354 return CP_OUTPUT_TYP;
355 END;
356 Function CP_EMP_NAME_FORMAT_p return varchar2 is
357 Begin
358 return CP_EMP_NAME_FORMAT;
359 END;
360 Function CP_PGM_p return varchar2 is
361 Begin
362 return CP_PGM;
363 END;
364 Function CP_PL_p return varchar2 is
365 Begin
366 return CP_PL;
367 END;
368 Function CP_PER_SEL_RULE_p return varchar2 is
369 Begin
370 return CP_PER_SEL_RULE;
371 END;
372 Function CP_BUSINESS_GROUP_p return varchar2 is
373 Begin
374 return CP_BUSINESS_GROUP;
375 END;
376 Function CP_ORGANIZATION_p return varchar2 is
377 Begin
378 return CP_ORGANIZATION;
379 END;
380 Function CP_PAYROLL_p return varchar2 is
381 Begin
382 return CP_PAYROLL;
383 END;
384 Function CP_NTL_IDENTIFIER_p return varchar2 is
385 Begin
386 return CP_NTL_IDENTIFIER;
387 END;
388 END BEN_BENRECON_XMLP_PKG ;