[Home] [Help]
PACKAGE BODY: APPS.BEN_BENCLSUM_XMLP_PKG
Source
1 PACKAGE BODY BEN_BENCLSUM_XMLP_PKG AS
2 /* $Header: BENCLSUMB.pls 120.1 2007/12/10 08:26:36 vjaganat noship $ */
3
4 function CF_STANDARD_HEADERFormula return Number is
5 l_concurrent_program_name fnd_concurrent_programs_tl.user_concurrent_program_name%type ; l_process_date varchar2(30);
6 l_mode hr_lookups.meaning%type ;
7 l_derivable_factors hr_lookups.meaning%type ;
8 l_validate hr_lookups.meaning%type ;
9 l_person per_people_f.full_name%type ;
10 l_person_type per_person_types.user_person_type%type ;
11 l_program ben_pgm_f.name%type ;
12 l_business_group per_business_groups.name%type ;
13 l_plan ben_pl_f.name%type ;
14 l_enrollment_type_cycle varchar2(800);
15 l_plans_not_in_programs hr_lookups.meaning%type ;
16 l_just_programs hr_lookups.meaning%type ;
17 l_comp_object_selection_rule ff_formulas_f.formula_name%type ;
18 l_person_selection_rule ff_formulas_f.formula_name%type ;
19 l_life_event_reason ben_ler_f.name%type ;
20 l_organization hr_all_organization_units.name%type ;
21 l_postal_zip_range varchar2(80);
22 l_reporting_group ben_rptg_grp.name%type ;
23 l_plan_type ben_pl_typ_f.name%type ;
24 l_option ben_opt_f.name%type ;
25 l_eligibility_profile ben_eligy_prfl_f.name%type ;
26 l_variable_rate_profile ben_vrbl_rt_prfl_f.name%type ;
27 l_legal_entity hr_all_organization_units.name%type ;
28 l_payroll pay_payrolls_f.payroll_name%type ;
29 l_status fnd_lookups.meaning%type ;
30 l_all varchar2(80); begin
31
32 ben_batch_reporting.standard_header
33 (p_concurrent_request_id => P_CONCURRENT_REQUEST_ID,
34 p_concurrent_program_name => L_CONCURRENT_PROGRAM_NAME,
35 p_process_date => L_PROCESS_DATE,
36 p_mode => L_MODE,
37 p_derivable_factors => L_DERIVABLE_FACTORS,
38 p_validate => L_VALIDATE,
39 p_person => L_PERSON,
40 p_person_type => L_PERSON_TYPE,
41 p_program => L_PROGRAM,
42 p_business_group => L_BUSINESS_GROUP,
43 p_plan => L_PLAN,
44 p_popl_enrt_typ_cycl => L_ENROLLMENT_TYPE_CYCLE,
45 p_plans_not_in_programs => L_PLANS_NOT_IN_PROGRAMS,
46 p_just_programs => L_JUST_PROGRAMS,
47 p_comp_object_selection_rule => L_COMP_OBJECT_SELECTION_RULE,
48 p_person_selection_rule => L_PERSON_SELECTION_RULE,
49 p_life_event_reason => L_LIFE_EVENT_REASON,
50 p_organization => L_ORGANIZATION,
51 p_postal_zip_range => L_POSTAL_ZIP_RANGE,
52 p_reporting_group => L_REPORTING_GROUP,
53 p_plan_type => L_PLAN_TYPE,
54 p_option => L_OPTION,
55 p_eligibility_profile => L_ELIGIBILITY_PROFILE,
56 p_variable_rate_profile => L_VARIABLE_RATE_PROFILE,
57 p_legal_entity => L_LEGAL_ENTITY,
58 p_payroll => L_PAYROLL,
59 p_status => L_STATUS);
60 CP_CONCURRENT_PROGRAM_NAME := l_concurrent_program_name;
61 CP_PROCESS_DATE := l_process_date;
62 CP_MODE := l_mode;
63 CP_DERIVABLE_FACTORS := l_derivable_factors;
64 CP_VALIDATE := l_validate;
65 CP_PERSON := l_person;
66 CP_PERSON_TYPE := l_person_type;
67 CP_PROGRAM := l_program;
68 CP_BUSINESS_GROUP := l_business_group;
69 CP_PLAN := l_plan;
70 CP_ENROLLMENT_TYPE_CYCLE := l_enrollment_type_cycle;
71 CP_PLANS_NOT_IN_PROGRAMS := l_plans_not_in_programs;
72 CP_JUST_PROGRAMS := l_just_programs;
73 CP_COMP_OBJECT_SELECTION_RULE := l_comp_object_selection_rule;
74 CP_PERSON_SELECTION_RULE := l_person_selection_rule;
75 CP_LIFE_EVENT_REASON := l_life_event_reason;
76 CP_ORGANIZATION := l_organization;
77 CP_POSTAL_ZIP_RANGE := l_postal_zip_range;
78 CP_REPORTING_GROUP := l_reporting_group;
79 CP_PLAN_TYPE := l_plan_type;
80 CP_OPTION := l_option;
81 CP_ELIGIBILITY_PROFILE := l_eligibility_profile;
82 CP_VARIABLE_RATE_PROFILE := l_variable_rate_profile;
83 CP_LEGAL_ENTITY := l_legal_entity;
84 CP_PAYROLL := l_payroll;
85 CP_STATUS := l_status;
86 fnd_message.set_name('BEN','BEN_91792_ALL_PROMPT');
87 l_all := substrb(fnd_message.get,1,80);
88 begin
89 select location_code into CP_LOCATION
90 from hr_locations a,
91 ben_benefit_actions b
92 where a.location_id = b.location_id
93 and b.request_id = P_CONCURRENT_REQUEST_ID;
94
95 exception when no_data_found then
96 CP_LOCATION := l_all;
97 end;
98 begin
99 select meaning into CP_AUDIT_LOG_FLAG
100 from hr_lookups a,
101 ben_benefit_actions b
102 where a.lookup_type = 'YES_NO'
103 and a.lookup_code = b.AUDIT_LOG_FLAG
104 and b.request_id = P_CONCURRENT_REQUEST_ID;
105
106 exception when no_data_found then
107 null;
108 end;
109 begin
110 select meaning into CP_CLOSE_ACTION_ITEMS_FLAG
111 from hr_lookups a,
112 ben_benefit_actions b
113 where a.lookup_type = 'YES_NO'
114 and a.lookup_code = b.close_uneai_flag
115 and b.request_id = P_CONCURRENT_REQUEST_ID;
116
117 exception when no_data_found then
118 null;
119 end;
120 begin
121 select b.uneai_effective_date into CP_ACTION_ITEM_EFFECTIVE_DATE
122 from ben_benefit_actions b
123 where b.request_id = P_CONCURRENT_REQUEST_ID;
124
125 exception when no_data_found then
126 null;
127 end;
128 begin
129 select b.lf_evt_ocrd_dt into CP_ASSIGNED_LIFE_EVENT_DATE
130 from ben_benefit_actions b
131 where b.request_id = P_CONCURRENT_REQUEST_ID;
132
133 exception when no_data_found then
134 null;
135 end;
136 begin
137 select hl.meaning into CP_CLOSE_MODE
138 from ben_benefit_actions bft, hr_lookups hl
139 where bft.PTNL_LER_FOR_PER_STAT_CD = hl.lookup_code
140 and hl.lookup_type = 'BEN_BENCLENR_MD'
141 and bft.request_id = P_CONCURRENT_REQUEST_ID;
142
143 exception when no_data_found then
144 null;
145 end;
146 return 1;
147 end;
148
149 function CF_SUMMARY_EVENTFormula return Number is
150 l_val number;
151 begin
152 ben_batch_utils.summary_by_action
153 (p_concurrent_request_id => P_CONCURRENT_REQUEST_ID
154 ,p_cd_1 => 'CLSNNOACTN', p_val_1 => CP_CLSNNOACTN
155 ,p_cd_2 => 'CLSNODEF', p_val_2 => CP_CLSNODEF
156 ,p_cd_3 => 'CLSNDEFNOCHG', p_val_3 => CP_CLSNDEFNOCHG
157 ,p_cd_4 => 'CLSNDEFWCHG', p_val_4 => CP_CLSNDEFWCHG
158 ,p_cd_5 => 'CLSDEFNOCHG', p_val_5 => CP_CLSDEFNOCHG
159 ,p_cd_6 => 'CLSDEFWCHG', p_val_6 => CP_CLSDEFWCHG
160 ,p_cd_7 => 'XXXXXXXXXX', p_val_7 => l_val
161 ,p_cd_8 => 'XXXXXXXXXX', p_val_8 => l_val
162 ,p_cd_9 => 'XXXxxxxxxx', p_val_9 => l_val
163 ,p_cd_10 => 'XXxxxxxxxX', p_val_10 => l_val
164 );
165 CP_CLSNNOACTN := nvl(CP_CLSNNOACTN,0);
166 CP_CLSNODEF := nvl(CP_CLSNODEF,0);
167 CP_CLSNDEFNOCHG := NVL(CP_CLSNDEFNOCHG,0);
168 CP_CLSNDEFWCHG := NVL(CP_CLSNDEFWCHG,0);
169 CP_CLSDEFNOCHG := NVL(CP_CLSDEFNOCHG,0);
170 CP_CLSDEFWCHG := NVL(CP_CLSDEFWCHG,0);
171 CP_TOTCLSDEF := CP_CLSDEFNOCHG + CP_CLSDEFWCHG;
172 CP_TOTCLSNDEF := CP_CLSNDEFNOCHG + CP_CLSNDEFWCHG;
173 CP_TOT := CP_CLSNNOACTN + CP_CLSNODEF + CP_TOTCLSDEF + CP_TOTCLSNDEF;
174 return 1;
175 end;
176
177 function CF_1Formula return Number is
178 l_start_date varchar2(30);
179 l_end_date varchar2(30);
180 l_start_time varchar2(30);
181 l_end_time varchar2(30);
182 l_elapsed_time varchar2(30);
183 l_persons_selected varchar2(30);
184 l_persons_processed varchar2(30);
185 l_persons_errored varchar2(30);
186 l_persons_processed_succ varchar2(30);
187 l_persons_unprocessed varchar2(30);
188 begin
189 ben_batch_reporting.process_information
190 (p_concurrent_request_id => P_CONCURRENT_REQUEST_ID,
191 p_start_date => L_START_DATE,
192 p_end_date => L_END_DATE,
193 p_start_time => L_START_TIME,
194 p_end_time => L_END_TIME,
195 p_elapsed_time => L_ELAPSED_TIME,
196 p_persons_selected => L_PERSONS_SELECTED,
197 p_persons_processed => L_PERSONS_PROCESSED,
198 p_persons_unprocessed => L_PERSONS_UNPROCESSED,
199 p_persons_processed_succ => L_PERSONS_PROCESSED_SUCC,
200 p_persons_errored => L_PERSONS_ERRORED);
201 CP_START_DATE := l_start_date;
202 CP_END_DATE := l_end_date;
203 CP_START_TIME := l_start_time;
204 CP_END_TIME := l_end_time;
205 CP_ELAPSED_TIME := l_elapsed_time;
206 CP_PERSONS_SELECTED := l_persons_selected;
207 CP_PERSONS_PROCESSED := l_persons_processed;
208 CP_PERSONS_ERRORED := l_persons_errored;
209 CP_PERSONS_UNPROCESSED := l_persons_unprocessed;
210 CP_PERSONS_PROCESSED_SUCC := l_persons_processed_succ;
211 return 1;
212 end;
213
214 function AfterReport return boolean is
215 begin
216 /* hr_standard.event('AFTER REPORT'); */
217 return (TRUE);
218 end;
219
220 function BeforeReport return boolean is
221 begin
222 /* hr_standard.event('BEFORE REPORT'); */
223 return (TRUE);
224 end;
225
226 function AfterPForm return boolean is
227 begin
228 P_CONC_REQUEST_ID := P_CONCURRENT_REQUEST_ID;
229 return (TRUE);
230 end;
231
232 --Functions to refer Oracle report placeholders--
233
234 Function CP_PROCESS_DATE_p return varchar2 is
235 Begin
236 return CP_PROCESS_DATE;
237 END;
238 Function CP_DERIVABLE_FACTORS_p return varchar2 is
239 Begin
240 return CP_DERIVABLE_FACTORS;
241 END;
242 Function CP_VALIDATE_p return varchar2 is
243 Begin
244 return CP_VALIDATE;
245 END;
246 Function CP_PERSON_p return varchar2 is
247 Begin
248 return CP_PERSON;
249 END;
250 Function CP_PERSON_TYPE_p return varchar2 is
251 Begin
252 return CP_PERSON_TYPE;
253 END;
254 Function CP_PROGRAM_p return varchar2 is
255 Begin
256 return CP_PROGRAM;
257 END;
258 Function CP_BUSINESS_GROUP_p return varchar2 is
259 Begin
260 return CP_BUSINESS_GROUP;
261 END;
262 Function CP_PLAN_p return varchar2 is
263 Begin
264 return CP_PLAN;
265 END;
266 Function CP_ENROLLMENT_TYPE_CYCLE_p return varchar2 is
267 Begin
268 return CP_ENROLLMENT_TYPE_CYCLE;
269 END;
270 Function CP_PLANS_NOT_IN_PROGRAMS_p return varchar2 is
271 Begin
272 return CP_PLANS_NOT_IN_PROGRAMS;
273 END;
274 Function CP_JUST_PROGRAMS_p return varchar2 is
275 Begin
276 return CP_JUST_PROGRAMS;
277 END;
278 Function CP_COMP_OBJECT_SELECTION_RULE1 return varchar2 is
279 Begin
280 return CP_COMP_OBJECT_SELECTION_RULE;
281 END;
282 Function CP_PERSON_SELECTION_RULE_p return varchar2 is
283 Begin
284 return CP_PERSON_SELECTION_RULE;
285 END;
286 Function CP_LIFE_EVENT_REASON_p return varchar2 is
287 Begin
288 return CP_LIFE_EVENT_REASON;
289 END;
290 Function CP_ORGANIZATION_p return varchar2 is
291 Begin
292 return CP_ORGANIZATION;
293 END;
294 Function CP_POSTAL_ZIP_RANGE_p return varchar2 is
295 Begin
296 return CP_POSTAL_ZIP_RANGE;
297 END;
298 Function CP_REPORTING_GROUP_p return varchar2 is
299 Begin
300 return CP_REPORTING_GROUP;
301 END;
302 Function CP_PLAN_TYPE_p return varchar2 is
303 Begin
304 return CP_PLAN_TYPE;
305 END;
306 Function CP_OPTION_p return varchar2 is
307 Begin
308 return CP_OPTION;
309 END;
310 Function CP_ELIGIBILITY_PROFILE_p return varchar2 is
311 Begin
312 return CP_ELIGIBILITY_PROFILE;
313 END;
314 Function CP_VARIABLE_RATE_PROFILE_p return varchar2 is
315 Begin
316 return CP_VARIABLE_RATE_PROFILE;
317 END;
318 Function CP_LEGAL_ENTITY_p return varchar2 is
319 Begin
320 return CP_LEGAL_ENTITY;
321 END;
322 Function CP_PAYROLL_p return varchar2 is
323 Begin
324 return CP_PAYROLL;
325 END;
326 Function CP_CONCURRENT_PROGRAM_NAME_p return varchar2 is
327 Begin
328 return CP_CONCURRENT_PROGRAM_NAME;
329 END;
330 Function CP_START_DATE_p return varchar2 is
331 Begin
332 return CP_START_DATE;
333 END;
334 Function CP_END_DATE_p return varchar2 is
335 Begin
336 return CP_END_DATE;
337 END;
338 Function CP_ELAPSED_TIME_p return varchar2 is
339 Begin
340 return CP_ELAPSED_TIME;
341 END;
342 Function CP_PERSONS_SELECTED_p return number is
343 Begin
344 return CP_PERSONS_SELECTED;
345 END;
346 Function CP_PERSONS_PROCESSED_p return number is
347 Begin
348 return CP_PERSONS_PROCESSED;
349 END;
350 Function CP_MODE_p return varchar2 is
351 Begin
352 return CP_MODE;
353 END;
354 Function CP_CLSNDEFNOCHG_p return number is
355 Begin
356 return CP_CLSNDEFNOCHG;
357 END;
358 Function CP_CLSNDEFWCHG_p return number is
359 Begin
360 return CP_CLSNDEFWCHG;
361 END;
362 Function CP_END_TIME_p return varchar2 is
363 Begin
364 return CP_END_TIME;
365 END;
366 Function CP_START_TIME_p return varchar2 is
367 Begin
368 return CP_START_TIME;
369 END;
370 Function CP_STATUS_p return varchar2 is
371 Begin
372 return CP_STATUS;
373 END;
374 Function CP_PERSONS_ERRORED_p return number is
375 Begin
376 return CP_PERSONS_ERRORED;
377 END;
378 Function CP_PERSONS_PROCESSED_SUCC_p return number is
379 Begin
380 return CP_PERSONS_PROCESSED_SUCC;
381 END;
382 Function CP_PERSONS_UNPROCESSED_p return number is
383 Begin
384 return CP_PERSONS_UNPROCESSED;
385 END;
386 Function CP_CLSDEFNOCHG_p return number is
387 Begin
388 return CP_CLSDEFNOCHG;
389 END;
390 Function CP_CLSNNOACTN_p return number is
391 Begin
392 return CP_CLSNNOACTN;
393 END;
394 Function CP_CLSDEFWCHG_p return number is
395 Begin
396 return CP_CLSDEFWCHG;
397 END;
398 Function CP_CLSNODEF_p return number is
399 Begin
400 return CP_CLSNODEF;
401 END;
402 Function CP_TOTCLSDEF_p return number is
403 Begin
404 return CP_TOTCLSDEF;
405 END;
406 Function CP_TOTCLSNDEF_p return number is
407 Begin
408 return CP_TOTCLSNDEF;
409 END;
410 Function CP_tot_p return number is
411 Begin
412 return CP_tot;
413 END;
414 Function CP_LOCATION_p return varchar2 is
415 Begin
416 return CP_LOCATION;
417 END;
418 Function CP_ASSIGNED_LIFE_EVENT_DATE_p return varchar2 is
419 Begin
420 return CP_ASSIGNED_LIFE_EVENT_DATE;
421 END;
422 Function CP_ACTION_ITEM_EFFECTIVE_DATE1 return date is
423 Begin
424 return CP_ACTION_ITEM_EFFECTIVE_DATE;
425 END;
426 Function CP_AUDIT_LOG_FLAG_p return varchar2 is
427 Begin
428 return CP_AUDIT_LOG_FLAG;
429 END;
430 Function CP_CLOSE_ACTION_ITEMS_FLAG_p return varchar2 is
431 Begin
432 return CP_CLOSE_ACTION_ITEMS_FLAG;
433 END;
434 Function CP_CLOSE_MODE_p return varchar2 is
435 Begin
436 return CP_CLOSE_MODE;
437 END;
438 END BEN_BENCLSUM_XMLP_PKG ;