[Home] [Help]
PACKAGE BODY: APPS.BEN_BENERRTY_XMLP_PKG
Source
1 PACKAGE BODY BEN_BENERRTY_XMLP_PKG AS
2 /* $Header: BENERRTYB.pls 120.1 2007/12/10 08:32:43 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 date;
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(800);
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
31 begin
32
33 ben_batch_reporting.standard_header
34 (p_concurrent_request_id => p_concurrent_request_id,
35 p_concurrent_program_name => L_CONCURRENT_PROGRAM_NAME,
36 p_process_date => L_PROCESS_DATE,
37 p_mode => L_MODE,
38 p_derivable_factors => L_DERIVABLE_FACTORS,
39 p_validate => L_VALIDATE,
40 p_person => L_PERSON,
41 p_person_type => L_PERSON_TYPE,
42 p_program => L_PROGRAM,
43 p_business_group => L_BUSINESS_GROUP,
44 p_plan => L_PLAN,
45 p_popl_enrt_typ_cycl => L_ENROLLMENT_TYPE_CYCLE,
46 p_plans_not_in_programs => L_PLANS_NOT_IN_PROGRAMS,
47 p_just_programs => L_JUST_PROGRAMS,
48 p_comp_object_selection_rule => L_COMP_OBJECT_SELECTION_RULE,
49 p_person_selection_rule => L_PERSON_SELECTION_RULE,
50 p_life_event_reason => L_LIFE_EVENT_REASON,
51 p_organization => L_ORGANIZATION,
52 p_postal_zip_range => L_POSTAL_ZIP_RANGE,
53 p_reporting_group => L_REPORTING_GROUP,
54 p_plan_type => L_PLAN_TYPE,
55 p_option => L_OPTION,
56 p_eligibility_profile => L_ELIGIBILITY_PROFILE,
57 p_variable_rate_profile => L_VARIABLE_RATE_PROFILE,
58 p_legal_entity => L_LEGAL_ENTITY,
59 p_payroll => L_PAYROLL,
60 p_status => L_STATUS);
61 CP_CONCURRENT_PROGRAM_NAME := l_concurrent_program_name;
62 CP_PROCESS_DATE := (l_process_date);
63 CP_MODE := l_mode;
64 CP_DERIVABLE_FACTORS := l_derivable_factors;
65 CP_VALIDATE := l_validate;
66 CP_PERSON := l_person;
67 CP_PERSON_TYPE := l_person_type;
68 CP_PROGRAM := l_program;
69 CP_BUSINESS_GROUP := l_business_group;
70 CP_PLAN := l_plan;
71 CP_ENROLLMENT_TYPE_CYCLE := l_enrollment_type_cycle;
72 CP_PLANS_NOT_IN_PROGRAMS := l_plans_not_in_programs;
73 CP_JUST_PROGRAMS := l_just_programs;
74 CP_COMP_OBJECT_SELECTION_RULE := l_comp_object_selection_rule;
75 CP_PERSON_SELECTION_RULE := l_person_selection_rule;
76 CP_LIFE_EVENT_REASON := l_life_event_reason;
77 CP_ORGANIZATION := l_organization;
78 CP_POSTAL_ZIP_RANGE := l_postal_zip_range;
79 CP_REPORTING_GROUP := l_reporting_group;
80 CP_PLAN_TYPE := l_plan_type;
81 CP_OPTION := l_option;
82 CP_ELIGIBILITY_PROFILE := l_eligibility_profile;
83 CP_VARIABLE_RATE_PROFILE := l_variable_rate_profile;
84 CP_LEGAL_ENTITY := l_legal_entity;
85 CP_PAYROLL := l_payroll;
86 CP_STATUS := l_status;
87 begin
88 select loc.location_code into CP_LOCATION
89 from hr_locations loc,
90 ben_benefit_actions bft
91 where bft.request_id = p_concurrent_request_id
92 and bft.location_id = loc.location_id;
93
94 exception
95 when no_data_found then
96 null;
97 end;
98
99 begin
100 select bng.name into CP_PERSON_BNFT_GRP
101 from ben_benfts_grp bng,
102 ben_benefit_actions bft
103 where bft.request_id = p_concurrent_request_id
104 and bft.benfts_grp_id = bng.benfts_grp_id;
105
106 exception
107 when no_data_found then
108 null;
109 end;
110
111 begin
112 select decode(audit_log_flag, 'Y', 'Yes', 'N', 'No') into CP_AUDIT_FLAG
113 from ben_benefit_actions bft
114 where bft.request_id = p_concurrent_request_id;
115
116
117 exception
118 when no_data_found then
119 null;
120 end;
121
122 begin
123 select decode(lmt_prpnip_by_org_flag, 'Y', 'Yes', 'N', 'No') into CP_LMT_BY_ORG_FLAG
124 from ben_benefit_actions bft
125 where bft.request_id = p_concurrent_request_id;
126
127
128 exception
129 when no_data_found then
130 null;
131 end;
132 return 1;
133 end;
134
135 function CF_1Formula return Number is
136 l_start_date varchar2(30);
137 l_end_date varchar2(30);
138 l_start_time varchar2(30);
139 l_end_time varchar2(30);
140 l_elapsed_time varchar2(30);
141 l_persons_selected varchar2(30);
142 l_persons_processed varchar2(30);
143 l_persons_errored varchar2(30);
144 l_persons_processed_succ varchar2(30);
145 l_persons_unprocessed varchar2(30);
146 begin
147 ben_batch_reporting.process_information
148 (p_concurrent_request_id => p_concurrent_request_id,
149 p_start_date => L_START_DATE,
150 p_end_date => L_END_DATE,
151 p_start_time => L_START_TIME,
152 p_end_time => L_END_TIME,
153 p_elapsed_time => L_ELAPSED_TIME,
154 p_persons_selected => L_PERSONS_SELECTED,
155 p_persons_processed => L_PERSONS_PROCESSED,
156 p_persons_unprocessed => L_PERSONS_UNPROCESSED,
157 p_persons_processed_succ => L_PERSONS_PROCESSED_SUCC,
158 p_persons_errored => L_PERSONS_ERRORED);
159 CP_START_DATE := (l_start_date);
160 CP_END_DATE := (l_end_date);
161 CP_START_TIME := l_start_time;
162 CP_END_TIME := l_end_time;
163 CP_ELAPSED_TIME := l_elapsed_time;
164 CP_PERSONS_SELECTED := l_persons_selected;
165 CP_PERSONS_PROCESSED := l_persons_processed;
166 CP_PERSONS_ERRORED := l_persons_errored;
167 CP_PERSONS_UNPROCESSED := l_persons_unprocessed;
168 CP_PERSONS_PROCESSED_SUCC := l_persons_processed_succ;
169 return 1;
170 end;
171
172 function BeforePForm return boolean is
173 begin
174 return (TRUE);
175 end;
176
177 function AfterReport return boolean is
178 begin
179 /*srw.user_exit('FND SRWEXIT');*/null;
180
181 return (TRUE);
182 end;
183
184 function BeforeReport return boolean is
185 l_date date;
186 begin
187 /*srw.user_exit('FND SRWINIT');*/null;
188
189 p_date_mask := fnd_profile.value('ICX_DATE_FORMAT_MASK');
190 select benefit_action_id, process_date
191 into p_benefit_action_id, l_date
192 from ben_benefit_actions
193 where request_id = p_concurrent_request_id ;
194
195 p_process_date := to_char(l_date, P_DATE_MASK) ;
196 return (TRUE);
197 end;
198
199 function AfterPForm return boolean is
200 begin
201 P_CONC_REQUEST_ID := P_CONCURRENT_REQUEST_ID;
202 return (TRUE);
203 end;
204
205 --Functions to refer Oracle report placeholders--
206
207 Function CP_PROCESS_DATE_p return date is
208 Begin
209 return CP_PROCESS_DATE;
210 END;
211 Function CP_DERIVABLE_FACTORS_p return varchar2 is
212 Begin
213 return CP_DERIVABLE_FACTORS;
214 END;
215 Function CP_VALIDATE_p return varchar2 is
216 Begin
217 return CP_VALIDATE;
218 END;
219 Function CP_PERSON_p return varchar2 is
220 Begin
221 return CP_PERSON;
222 END;
223 Function CP_PERSON_TYPE_p return varchar2 is
224 Begin
225 return CP_PERSON_TYPE;
226 END;
227 Function CP_PROGRAM_p return varchar2 is
228 Begin
229 return CP_PROGRAM;
230 END;
231 Function CP_BUSINESS_GROUP_p return varchar2 is
232 Begin
233 return CP_BUSINESS_GROUP;
234 END;
235 Function CP_PLAN_p return varchar2 is
236 Begin
237 return CP_PLAN;
238 END;
239 Function CP_ENROLLMENT_TYPE_CYCLE_p return varchar2 is
240 Begin
241 return CP_ENROLLMENT_TYPE_CYCLE;
242 END;
243 Function CP_PLANS_NOT_IN_PROGRAMS_p return varchar2 is
244 Begin
245 return CP_PLANS_NOT_IN_PROGRAMS;
246 END;
247 Function CP_JUST_PROGRAMS_p return varchar2 is
248 Begin
249 return CP_JUST_PROGRAMS;
250 END;
251 Function CP_COMP_OBJECT_SELECTION_RULE1 return varchar2 is
252 Begin
253 return CP_COMP_OBJECT_SELECTION_RULE;
254 END;
255 Function CP_PERSON_SELECTION_RULE_p return varchar2 is
256 Begin
257 return CP_PERSON_SELECTION_RULE;
258 END;
259 Function CP_LIFE_EVENT_REASON_p return varchar2 is
260 Begin
261 return CP_LIFE_EVENT_REASON;
262 END;
263 Function CP_ORGANIZATION_p return varchar2 is
264 Begin
265 return CP_ORGANIZATION;
266 END;
267 Function CP_POSTAL_ZIP_RANGE_p return varchar2 is
268 Begin
269 return CP_POSTAL_ZIP_RANGE;
270 END;
271 Function CP_REPORTING_GROUP_p return varchar2 is
272 Begin
273 return CP_REPORTING_GROUP;
274 END;
275 Function CP_PLAN_TYPE_p return varchar2 is
276 Begin
277 return CP_PLAN_TYPE;
278 END;
279 Function CP_OPTION_p return varchar2 is
280 Begin
281 return CP_OPTION;
282 END;
283 Function CP_ELIGIBILITY_PROFILE_p return varchar2 is
284 Begin
285 return CP_ELIGIBILITY_PROFILE;
286 END;
287 Function CP_VARIABLE_RATE_PROFILE_p return varchar2 is
288 Begin
289 return CP_VARIABLE_RATE_PROFILE;
290 END;
291 Function CP_LEGAL_ENTITY_p return varchar2 is
292 Begin
293 return CP_LEGAL_ENTITY;
294 END;
295 Function CP_PAYROLL_p return varchar2 is
296 Begin
297 return CP_PAYROLL;
298 END;
299 Function CP_CONCURRENT_PROGRAM_NAME_p return varchar2 is
300 Begin
301 return CP_CONCURRENT_PROGRAM_NAME;
302 END;
303 Function CP_MODE_p return varchar2 is
304 Begin
305 return CP_MODE;
306 END;
307 Function CP_STATUS_p return varchar2 is
308 Begin
309 return CP_STATUS;
310 END;
311 Function CP_START_DATE_p return date is
312 Begin
313 return CP_START_DATE;
314 END;
315 Function CP_END_DATE_p return date is
316 Begin
317 return CP_END_DATE;
318 END;
319 Function CP_START_TIME_p return varchar2 is
320 Begin
321 return CP_START_TIME;
322 END;
323 Function CP_END_TIME_p return varchar2 is
324 Begin
325 return CP_END_TIME;
326 END;
327 Function CP_ELAPSED_TIME_p return varchar2 is
328 Begin
329 return CP_ELAPSED_TIME;
330 END;
331 Function CP_PERSONS_SELECTED_p return number is
332 Begin
333 return CP_PERSONS_SELECTED;
334 END;
335 Function CP_PERSONS_PROCESSED_p return number is
336 Begin
337 return CP_PERSONS_PROCESSED;
338 END;
339 Function CP_PERSONS_ERRORED_p return number is
340 Begin
341 return CP_PERSONS_ERRORED;
342 END;
343 Function CP_PERSONS_UNPROCESSED_p return number is
344 Begin
345 return CP_PERSONS_UNPROCESSED;
346 END;
347 Function CP_PERSONS_PROCESSED_SUCC_p return number is
348 Begin
349 return CP_PERSONS_PROCESSED_SUCC;
350 END;
351 Function CP_LOCATION_p return varchar2 is
352 Begin
353 return CP_LOCATION;
354 END;
355 Function CP_PERSON_BNFT_GRP_p return varchar2 is
356 Begin
357 return CP_PERSON_BNFT_GRP;
358 END;
359 Function CP_AUDIT_FLAG_p return varchar2 is
360 Begin
361 return CP_AUDIT_FLAG;
362 END;
363 Function CP_LMT_BY_ORG_FLAG_p return varchar2 is
364 Begin
365 return CP_LMT_BY_ORG_FLAG;
366 END;
367 END BEN_BENERRTY_XMLP_PKG ;