[Home] [Help]
PACKAGE BODY: APPS.BEN_BENCLAUD_XMLP_PKG
Source
1 PACKAGE BODY BEN_BENCLAUD_XMLP_PKG AS
2 /* $Header: BENCLAUDB.pls 120.1 2007/12/10 08:25:44 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 ;
12 l_plan ben_pl_f.name%type ;
13 l_enrollment_type_cycle varchar2(800);
14 l_plans_not_in_programs hr_lookups.meaning%type ;
15 l_just_programs hr_lookups.meaning%type ;
16 l_comp_object_selection_rule ff_formulas_f.formula_name%type ;
17 l_person_selection_rule ff_formulas_f.formula_name%type ;
18 l_life_event_reason ben_ler_f.name%type ;
19 l_organization hr_all_organization_units.name%type ;
20 l_postal_zip_range varchar2(80);
21 l_reporting_group ben_rptg_grp.name%type ;
22 l_plan_type ben_pl_typ_f.name%type ;
23 l_option ben_opt_f.name%type ;
24 l_eligibility_profile ben_eligy_prfl_f.name%type ;
25 l_variable_rate_profile ben_vrbl_rt_prfl_f.name%type ;
26 l_legal_entity hr_all_organization_units.name%type ;
27 l_payroll pay_payrolls_f.payroll_name%type ;
28 l_status fnd_lookups.meaning%type ;
29 l_all varchar2(80);
30 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_STATUS := l_status;
63 CP_BUSINESS_GROUP := l_business_group;
64 CP_VALIDATE := l_validate;
65 CP_PERSON := l_person;
66 CP_PROGRAM := l_program;
67 CP_PLAN := l_plan;
68 CP_PERSON_SELECTION_RULE := l_person_selection_rule;
69 CP_LIFE_EVENT_REASON := l_life_event_reason;
70 CP_STATUS := l_status;
71 fnd_message.set_name('BEN','BEN_91792_ALL_PROMPT');
72 l_all := substrb(fnd_message.get,1,80);
73 begin
74 select location_code into CP_LOCATION
75 from hr_locations a,
76 ben_benefit_actions b
77 where a.location_id = b.location_id
78 and b.request_id = P_CONCURRENT_REQUEST_ID;
79
80 exception when no_data_found then
81 CP_LOCATION := l_all;
82 end;
83 begin
84 select meaning into CP_AUDIT_LOG
85 from hr_lookups a,
86 ben_benefit_actions b
87 where a.lookup_type = 'YES_NO'
88 and a.lookup_code = b.AUDIT_LOG_FLAG
89 and b.request_id = P_CONCURRENT_REQUEST_ID;
90
91 exception when no_data_found then
92 null;
93 end;
94 begin
95 select meaning into CP_CLOSE_ACTION_ITEMS_FLAG
96 from hr_lookups a,
97 ben_benefit_actions b
98 where a.lookup_type = 'YES_NO'
99 and a.lookup_code = b.close_uneai_flag
100 and b.request_id = P_CONCURRENT_REQUEST_ID;
101
102 exception when no_data_found then
103 null;
104 end;
105 begin
106 select b.uneai_effective_date into CP_ACTION_ITEM_EFFECTIVE_DATE
107 from ben_benefit_actions b
108 where b.request_id = P_CONCURRENT_REQUEST_ID;
109
110 exception when no_data_found then
111 null;
112 end;
113 begin
114 select b.lf_evt_ocrd_dt into CP_ASSIGNED_LIFE_EVENT_DATE
115 from ben_benefit_actions b
116 where b.request_id = P_CONCURRENT_REQUEST_ID;
117
118 exception when no_data_found then
119 null;
120 end;
121 begin
122 select hl.meaning into CP_CLOSE_MODE
123 from ben_benefit_actions bft, hr_lookups hl
124 where bft.PTNL_LER_FOR_PER_STAT_CD = hl.lookup_code
125 and hl.lookup_type = 'BEN_BENCLENR_MD'
126 and bft.request_id = P_CONCURRENT_REQUEST_ID;
127
128 exception when no_data_found then
129 null;
130 end;
131 return 1;
132 end;
133
134 function CF_PROCESS_INFORMATIONFormula return Number is
135 l_start_date varchar2(30);
136 l_end_date varchar2(30);
137 l_start_time varchar2(30);
138 l_end_time varchar2(30);
139 l_elapsed_time varchar2(30);
140 l_persons_selected varchar2(30);
141 l_persons_processed varchar2(30);
142 l_persons_errored varchar2(30);
143 l_persons_processed_succ varchar2(30);
144 l_persons_unprocessed varchar2(30);
145 l_val number;
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 ben_batch_utils.summary_by_action
170 (p_concurrent_request_id => P_CONCURRENT_REQUEST_ID
171 ,p_cd_1 => 'CLSNNOACTN', p_val_1 => CP_CLSNNOACTN
172 ,p_cd_2 => 'CLSNODEF', p_val_2 => CP_CLSNODEF
173 ,p_cd_3 => 'CLSNDEFNOCHG', p_val_3 => CP_CLSNDEFNOCHG
174 ,p_cd_4 => 'CLSNDEFWCHG', p_val_4 => CP_CLSNDEFWCHG
175 ,p_cd_5 => 'CLSDEFNOCHG', p_val_5 => CP_CLSDEFNOCHG
176 ,p_cd_6 => 'CLSDEFWCHG', p_val_6 => CP_CLSDEFWCHG
177 ,p_cd_7 => 'XXXXXXXXXX', p_val_7 => l_val
178 ,p_cd_8 => 'XXXXXXXXXX', p_val_8 => l_val
179 ,p_cd_9 => 'XXXxxxxxxx', p_val_9 => l_val
180 ,p_cd_10 => 'XXxxxxxxxX', p_val_10 => l_val
181 );
182 CP_CLSNNOACTN := nvl(CP_CLSNNOACTN,0);
183 CP_CLSNODEF := nvl(CP_CLSNODEF,0);
184 CP_CLSNDEFNOCHG := NVL(CP_CLSNDEFNOCHG,0);
185 CP_CLSNDEFWCHG := NVL(CP_CLSNDEFWCHG,0);
186 CP_CLSDEFNOCHG := NVL(CP_CLSDEFNOCHG,0);
187 CP_CLSDEFWCHG := NVL(CP_CLSDEFWCHG,0);
188
189 return 1;
190 end;
191
192 function BeforeReport return boolean is
193 begin
194 /* hr_standard.event('BEFORE REPORT'); */
195 return (TRUE);
196 end;
197
198 function AfterReport return boolean is
199 begin
200 /* hr_standard.event('AFTER REPORT'); */
201 return (TRUE);
202 end;
203
204 function AfterPForm return boolean is
205 begin
206 P_CONC_REQUEST_ID := P_CONCURRENT_REQUEST_ID;
207 return (TRUE);
208 end;
209
210 --Functions to refer Oracle report placeholders--
211
212 Function CP_PROCESS_DATE_p return date is
213 Begin
214 return CP_PROCESS_DATE;
215 END;
216 Function CP_BUSINESS_GROUP_p return varchar2 is
217 Begin
218 return CP_BUSINESS_GROUP;
219 END;
220 Function CP_CONCURRENT_PROGRAM_NAME_p return varchar2 is
221 Begin
222 return CP_CONCURRENT_PROGRAM_NAME;
223 END;
224 Function CP_START_DATE_p return varchar2 is
225 Begin
226 return CP_START_DATE;
227 END;
228 Function CP_END_DATE_p return varchar2 is
229 Begin
230 return CP_END_DATE;
231 END;
232 Function CP_ELAPSED_TIME_p return varchar2 is
233 Begin
234 return CP_ELAPSED_TIME;
235 END;
236 Function CP_PERSONS_SELECTED_p return number is
237 Begin
238 return CP_PERSONS_SELECTED;
239 END;
240 Function CP_PERSONS_PROCESSED_p return number is
241 Begin
242 return CP_PERSONS_PROCESSED;
243 END;
244 Function CP_START_TIME_p return varchar2 is
245 Begin
246 return CP_START_TIME;
247 END;
248 Function CP_END_TIME_p return varchar2 is
249 Begin
250 return CP_END_TIME;
251 END;
252 Function CP_PERSONS_UNPROCESSED_p return number is
253 Begin
254 return CP_PERSONS_UNPROCESSED;
255 END;
256 Function CP_PERSONS_PROCESSED_SUCC_p return number is
257 Begin
258 return CP_PERSONS_PROCESSED_SUCC;
259 END;
260 Function CP_PERSONS_ERRORED_p return number is
261 Begin
262 return CP_PERSONS_ERRORED;
263 END;
264 Function CP_STATUS_p return varchar2 is
265 Begin
266 return CP_STATUS;
267 END;
268 Function CP_CLSNNOACTN_p return number is
269 Begin
270 return CP_CLSNNOACTN;
271 END;
272 Function CP_CLSNODEF_p return number is
273 Begin
274 return CP_CLSNODEF;
275 END;
276 Function CP_CLSNDEFNOCHG_p return number is
277 Begin
278 return CP_CLSNDEFNOCHG;
279 END;
280 Function CP_CLSNDEFWCHG_p return number is
281 Begin
282 return CP_CLSNDEFWCHG;
283 END;
284 Function CP_CLSDEFNOCHG_p return number is
285 Begin
286 return CP_CLSDEFNOCHG;
287 END;
288 Function CP_CLSDEFWCHG_p return number is
289 Begin
290 return CP_CLSDEFWCHG;
291 END;
292 Function CP_PERSON_p return varchar2 is
293 Begin
294 return CP_PERSON;
295 END;
296 Function CP_PROGRAM_p return varchar2 is
297 Begin
298 return CP_PROGRAM;
299 END;
300 Function CP_PLAN_p return varchar2 is
301 Begin
302 return CP_PLAN;
303 END;
304 Function CP_VALIDATE_p return varchar2 is
305 Begin
306 return CP_VALIDATE;
307 END;
308 Function CP_PERSON_SELECTION_RULE_p return varchar2 is
309 Begin
310 return CP_PERSON_SELECTION_RULE;
311 END;
312 Function CP_LOCATION_p return varchar2 is
313 Begin
314 return CP_LOCATION;
315 END;
316 Function CP_ASSIGNED_LIFE_EVENT_DATE_p return varchar2 is
317 Begin
318 return CP_ASSIGNED_LIFE_EVENT_DATE;
319 END;
320 Function CP_LIFE_EVENT_REASON_p return varchar2 is
321 Begin
322 return CP_LIFE_EVENT_REASON;
323 END;
324 Function CP_CLOSE_ACTION_ITEMS_FLAG_p return varchar2 is
325 Begin
326 return CP_CLOSE_ACTION_ITEMS_FLAG;
327 END;
328 Function CP_CLOSE_MODE_p return varchar2 is
329 Begin
330 return CP_CLOSE_MODE;
331 END;
332 Function CP_AUDIT_LOG_p return varchar2 is
333 Begin
334 return CP_AUDIT_LOG;
335 END;
336 Function CP_ACTION_ITEM_EFFECTIVE_DATE1 return varchar2 is
337 Begin
338 return CP_ACTION_ITEM_EFFECTIVE_DATE;
339 END;
340 END BEN_BENCLAUD_XMLP_PKG ;