[Home] [Help]
PACKAGE BODY: APPS.BEN_BENBOSUM_XMLP_PKG
Source
1 PACKAGE BODY BEN_BENBOSUM_XMLP_PKG AS
2 /* $Header: BENBOSUMB.pls 120.1 2007/12/10 08:24:51 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 ; l_validate hr_lookups.meaning%type ;
8 l_person per_people_f.full_name%type ;
9 l_person_type per_person_types.user_person_type%type ;
10 l_program ben_pgm_f.name%type ;
11 l_business_group per_business_groups.name%type ; l_plan ben_pl_f.name%type ;
12 l_enrollment_type_cycle varchar2(800); l_plans_not_in_programs hr_lookups.meaning%type ;
13 l_just_programs hr_lookups.meaning%type ; l_comp_object_selection_rule ff_formulas_f.formula_name%type ;
14 l_person_selection_rule ff_formulas_f.formula_name%type ;
15 l_life_event_reason ben_ler_f.name%type ;
16 l_organization hr_all_organization_units.name%type ; l_postal_zip_range varchar2(80);
17 l_reporting_group ben_rptg_grp.name%type ;
18 l_plan_type ben_pl_typ_f.name%type ;
19 l_option ben_opt_f.name%type ;
20 l_eligibility_profile ben_eligy_prfl_f.name%type ;
21 l_variable_rate_profile ben_vrbl_rt_prfl_f.name%type ;
22 l_legal_entity hr_all_organization_units.name%type ;
23 l_payroll pay_payrolls_f.payroll_name%type ;
24 l_status fnd_lookups.meaning%type ; l_all varchar2(80); begin
25
26 ben_batch_reporting.standard_header
27 (p_concurrent_request_id => P_CONCURRENT_REQUEST_ID,
28 p_concurrent_program_name => L_CONCURRENT_PROGRAM_NAME,
29 p_process_date => L_PROCESS_DATE,
30 p_mode => L_MODE,
31 p_derivable_factors => L_DERIVABLE_FACTORS,
32 p_validate => L_VALIDATE,
33 p_person => L_PERSON,
34 p_person_type => L_PERSON_TYPE,
35 p_program => L_PROGRAM,
36 p_business_group => L_BUSINESS_GROUP,
37 p_plan => L_PLAN,
38 p_popl_enrt_typ_cycl => L_ENROLLMENT_TYPE_CYCLE,
39 p_plans_not_in_programs => L_PLANS_NOT_IN_PROGRAMS,
40 p_just_programs => L_JUST_PROGRAMS,
41 p_comp_object_selection_rule => L_COMP_OBJECT_SELECTION_RULE,
42 p_person_selection_rule => L_PERSON_SELECTION_RULE,
43 p_life_event_reason => L_LIFE_EVENT_REASON,
44 p_organization => L_ORGANIZATION,
45 p_postal_zip_range => L_POSTAL_ZIP_RANGE,
46 p_reporting_group => L_REPORTING_GROUP,
47 p_plan_type => L_PLAN_TYPE,
48 p_option => L_OPTION,
49 p_eligibility_profile => L_ELIGIBILITY_PROFILE,
50 p_variable_rate_profile => L_VARIABLE_RATE_PROFILE,
51 p_legal_entity => L_LEGAL_ENTITY,
52 p_payroll => L_PAYROLL,
53 p_status => L_STATUS);
54 CP_CONCURRENT_PROGRAM_NAME := l_concurrent_program_name;
55 CP_PROCESS_DATE := l_process_date;
56 CP_VALIDATE := l_validate;
57 CP_BUSINESS_GROUP := l_business_group;
58 CP_LIFE_EVENT_REASON := l_life_event_reason;
59 CP_ORGANIZATION := l_organization;
60 CP_LEGAL_ENTITY := l_legal_entity;
61 begin
62 fnd_message.set_name('BEN','BEN_91792_ALL_PROMPT');
63 l_all := substrb(fnd_message.get,1,80);
64 select name into CP_PERSON_BNFT_GRP
65 from ben_benfts_grp a,
66 ben_benefit_actions b
67 where a.benfts_grp_id = b.benfts_grp_id
68 and b.request_id = P_CONCURRENT_REQUEST_ID;
69
70 exception when no_data_found then
71 CP_PERSON_BNFT_GRP := l_all;
72 end;
73
74 begin
75 select location_code into CP_LOCATION
76 from hr_locations a,
77 ben_benefit_actions b
78 where a.location_id = b.location_id
79 and b.request_id = P_CONCURRENT_REQUEST_ID;
80
81 exception when no_data_found then
82 CP_LOCATION := l_all;
83 end;
84
85 begin
86 select date_from, uneai_effective_date into CP_FROM_OCRD_DT, CP_TO_OCRD_DT
87 from ben_benefit_actions
88 where request_id = P_CONCURRENT_REQUEST_ID;
89
90 exception when no_data_found then
91 null;
92 end;
93 begin
94 select hl.meaning into CP_RESULTING_STATUS
95 from ben_benefit_actions bft, hr_lookups hl
96 where bft.PTNL_LER_FOR_PER_STAT_CD = hl.lookup_code
97 and hl.lookup_type = 'BEN_PTNL_LER_FOR_PER_STAT'
98 and bft.request_id = P_CONCURRENT_REQUEST_ID;
99
100 exception when no_data_found then
101 null;
102 end;
103
104 return 1;
105 end;
106
107 function CF_SUMMARY_EVENTFormula return Number is
108 l_val number;
109 begin
110 select count(distinct rpt.person_id)
111 into CP_PEOPLE_LF_EVT_BO
112 from ben_reporting rpt,
113 ben_benefit_actions ba
114 where ba.request_id=P_CONCURRENT_REQUEST_ID and
115 ba.benefit_action_id=rpt.benefit_action_id and
116 rpt.rep_typ_cd='LFBO';
117
118 select count(rpt.person_id)
119 into CP_LF_EVT_BO
120 from ben_reporting rpt,
121 ben_benefit_actions ba
122 where ba.request_id=P_CONCURRENT_REQUEST_ID and
123 ba.benefit_action_id=rpt.benefit_action_id and
124 rpt.rep_typ_cd='LFBO';
125
126 select count(rpt.person_id)
127 into CP_LF_EVT_BO_CLS
128 from ben_reporting rpt,
129 ben_benefit_actions ba
130 where ba.request_id=P_CONCURRENT_REQUEST_ID and
131 ba.benefit_action_id=rpt.benefit_action_id and
132 rpt.rep_typ_cd='LFBO' and
133 rpt.text like 'PROCD_';
134
135 select count(rpt.person_id)
136 into CP_LF_EVT_BO_IP_WE
137 from ben_reporting rpt,
138 ben_benefit_actions ba
139 where ba.request_id=P_CONCURRENT_REQUEST_ID and
140 ba.benefit_action_id=rpt.benefit_action_id and
141 rpt.rep_typ_cd='LFBO' and
142 rpt.text = 'STRTDY';
143
144 select count(rpt.person_id)
145 into CP_LF_EVT_BO_IP_WOE
146 from ben_reporting rpt,
147 ben_benefit_actions ba
148 where ba.request_id=P_CONCURRENT_REQUEST_ID and
149 ba.benefit_action_id=rpt.benefit_action_id and
150 rpt.rep_typ_cd='LFBO' and
151 rpt.text = 'STRTDN';
152
153 return 1;
154 end;
155
156 function CF_1Formula return Number is
157 l_start_date varchar2(30);
158 l_end_date varchar2(30);
159 l_start_time varchar2(30);
160 l_end_time varchar2(30);
161 l_elapsed_time varchar2(30);
162 l_persons_selected varchar2(30);
163 l_persons_processed varchar2(30);
164 l_persons_errored varchar2(30);
165 l_persons_processed_succ varchar2(30);
166 l_persons_unprocessed varchar2(30);
167 begin
168 ben_batch_reporting.process_information
169 (p_concurrent_request_id => P_CONCURRENT_REQUEST_ID,
170 p_start_date => L_START_DATE,
171 p_end_date => L_END_DATE,
172 p_start_time => L_START_TIME,
173 p_end_time => L_END_TIME,
174 p_elapsed_time => L_ELAPSED_TIME,
175 p_persons_selected => L_PERSONS_SELECTED,
176 p_persons_processed => L_PERSONS_PROCESSED,
177 p_persons_unprocessed => L_PERSONS_UNPROCESSED,
178 p_persons_processed_succ => L_PERSONS_PROCESSED_SUCC,
179 p_persons_errored => L_PERSONS_ERRORED);
180 CP_START_DATE := l_start_date;
181 CP_END_DATE := l_end_date;
182 CP_START_TIME := l_start_time;
183 CP_END_TIME := l_end_time;
184 CP_ELAPSED_TIME := l_elapsed_time;
185 CP_PERSONS_SELECTED := l_persons_selected;
186 CP_PERSONS_PROCESSED := l_persons_processed;
187 CP_PERSONS_ERRORED := l_persons_errored;
188 CP_PERSONS_UNPROCESSED := l_persons_unprocessed;
189 CP_PERSONS_PROCESSED_SUCC := l_persons_processed_succ;
190
191
192 return 1;
193 end;
194
195 function G_benefit_action_idGroupFilter return boolean is
196 begin
197
198 return (TRUE);
199 end;
200
201 function BeforeReport return boolean is
202 begin
203 /* hr_standard.event('BEFORE REPORT'); */
204 return (TRUE);
205 end;
206
207 function AfterReport return boolean is
208 begin
209 /* hr_standard.event('AFTER REPORT'); */
210 return (TRUE);
211 end;
212
213 function AfterPForm return boolean is
214 begin
215 P_CONC_REQUEST_ID := P_CONCURRENT_REQUEST_ID;
216 return (TRUE);
217 end;
218
219 --Functions to refer Oracle report placeholders--
220
221 Function CP_PROCESS_DATE_p return date is
222 Begin
223 return CP_PROCESS_DATE;
224 END;
225 Function CP_VALIDATE_p return varchar2 is
226 Begin
227 return CP_VALIDATE;
228 END;
229 Function CP_PERSON_SELECTION_RULE_p return varchar2 is
230 Begin
231 return CP_PERSON_SELECTION_RULE;
232 END;
233 Function CP_LIFE_EVENT_REASON_p return varchar2 is
234 Begin
235 return CP_LIFE_EVENT_REASON;
236 END;
237 Function CP_ORGANIZATION_p return varchar2 is
238 Begin
239 return CP_ORGANIZATION;
240 END;
241 Function CP_LEGAL_ENTITY_p return varchar2 is
242 Begin
243 return CP_LEGAL_ENTITY;
244 END;
245 Function CP_TO_OCRD_DT_p return date is
246 Begin
247 return CP_TO_OCRD_DT;
248 END;
249 Function CP_START_DATE_p return varchar2 is
250 Begin
251 return CP_START_DATE;
252 END;
253 Function CP_END_DATE_p return varchar2 is
254 Begin
255 return CP_END_DATE;
256 END;
257 Function CP_ELAPSED_TIME_p return varchar2 is
258 Begin
259 return CP_ELAPSED_TIME;
260 END;
261 Function CP_PERSONS_SELECTED_p return number is
262 Begin
263 return CP_PERSONS_SELECTED;
264 END;
265 Function CP_PERSONS_PROCESSED_p return number is
266 Begin
267 return CP_PERSONS_PROCESSED;
268 END;
269 Function CP_FROM_OCRD_DT_p return date is
270 Begin
271 return CP_FROM_OCRD_DT;
272 END;
273 Function CP_END_TIME_p return varchar2 is
274 Begin
275 return CP_END_TIME;
276 END;
277 Function CP_START_TIME_p return varchar2 is
278 Begin
279 return CP_START_TIME;
280 END;
281 Function CP_LOCATION_p return varchar2 is
282 Begin
283 return CP_LOCATION;
284 END;
285 Function CP_PERSONS_ERRORED_p return number is
286 Begin
287 return CP_PERSONS_ERRORED;
288 END;
289 Function CP_PERSONS_PROCESSED_SUCC_p return number is
290 Begin
291 return CP_PERSONS_PROCESSED_SUCC;
292 END;
293 Function CP_PERSONS_UNPROCESSED_p return number is
294 Begin
295 return CP_PERSONS_UNPROCESSED;
296 END;
297 Function CP_LF_EVT_BO_p return number is
298 Begin
299 return CP_LF_EVT_BO;
300 END;
301 Function CP_PERSON_BNFT_GRP_p return varchar2 is
302 Begin
303 return CP_PERSON_BNFT_GRP;
304 END;
305 Function CP_PEOPLE_LF_EVT_BO_p return number is
306 Begin
307 return CP_PEOPLE_LF_EVT_BO;
308 END;
309 Function CP_concurrent_program_name_p return varchar2 is
310 Begin
311 return CP_concurrent_program_name;
312 END;
313 Function CP_BUSINESS_GROUP_p return varchar2 is
314 Begin
315 return CP_BUSINESS_GROUP;
316 END;
317 Function CP_LF_EVT_BO_CLS_p return number is
318 Begin
319 return CP_LF_EVT_BO_CLS;
320 END;
321 Function CP_LF_EVT_BO_IP_WE_p return number is
322 Begin
323 return CP_LF_EVT_BO_IP_WE;
324 END;
325 Function CP_LF_EVT_BO_IP_WOE_p return number is
326 Begin
327 return CP_LF_EVT_BO_IP_WOE;
328 END;
329 Function CP_RESULTING_STATUS_p return varchar2 is
330 Begin
331 return CP_RESULTING_STATUS;
332 END;
333 END BEN_BENBOSUM_XMLP_PKG ;