[Home] [Help]
PACKAGE BODY: APPS.PER_PERRPFP4_XMLP_PKG
Source
1 PACKAGE BODY PER_PERRPFP4_XMLP_PKG AS
2 /* $Header: PERRPFP4B.pls 120.1 2007/12/06 11:29:23 amakrish noship $ */
3 function BeforeReport return boolean is
4 l_date_format varchar2(20):='DD-MON-YYYY';
5 begin
6 P_SESSION_DATE1:=TO_CHAR(P_SESSION_DATE,'DD-MON-YYYY');
7 --c_end_of_time := hr_general.end_of_time;
8 c_end_of_time :=to_char(to_date(hr_general.end_of_time,l_date_format),'YYYY-MM-DD');
9 declare
10 v_name varchar2(382);
11 v_title varchar2(400);
12 v_label_expr varchar2(32000);
13 v_column_expr varchar2(32000);
14 v_legislation_code varchar2(30);
15 begin
16
17
18 /*srw.message('001','start');*/null;
19
20 --hr_standard.event('BEFORE REPORT');
21
22 c_business_group_name :=
23 hr_reports.get_business_group(p_business_group_id);
24
25 /*srw.message('002','bg');*/null;
26
27 select peo.first_name ||
28 decode(peo.first_name,null,null,' ') ||
29 peo.last_name
30 into v_name
31 from per_all_people_f peo
32 where peo.person_id = p_person_id
33 and p_session_date between peo.effective_start_date
34 and peo.effective_end_date;
35 c_header_name := v_name;
36 /*srw.message('003','name');*/null;
37
38
39
40
41 hr_reports.get_desc_flex('PER','PER_ASSIGNMENT_STATUSES',
42 'sst',v_title,v_label_expr,v_column_expr);
43 if v_column_expr is not null then
44 c_sec_ass_df_details := v_column_expr;
45 c_sec_ass_df_label := v_label_expr;
46 else
47 c_sec_ass_df_details := 'sst.attribute1';
48 end if;
49
50
51
52 hr_reports.get_desc_flex('PER','PER_PEOPLE',
53 'peo',v_title,v_label_expr,v_column_expr);
54 if v_column_expr is not null then
55 c_emp_df_details := v_column_expr;
56 c_emp_df_label := v_label_expr;
57 else
58 c_emp_df_details := 'peo.attribute1';
59 end if;
60
61 hr_reports.get_desc_flex('PER','PER_ASSIGNMENT_STATUSES',
62 'sst',v_title,v_label_expr,v_column_expr);
63 if v_column_expr is not null then
64 c_app_sec_status_details := v_column_expr;
65 c_app_sec_status_label := v_label_expr;
66 else
67 c_app_sec_status_details := 'sst.attribute1';
68 end if;
69
70 hr_reports.get_desc_flex('PER','PER_EVENTS',
71 'a',v_title,v_label_expr,v_column_expr);
72 if v_column_expr is not null then
73 c_inter_df_details := v_column_expr;
74 c_inter_df_label := v_label_expr;
75 else
76 c_inter_df_details := 'a.attribute1';
77 end if;
78
79 hr_reports.get_desc_flex('PER','PER_ASSIGNMENTS',
80 'asg',v_title,v_label_expr,v_column_expr);
81 if v_column_expr is not null then
82 c_app_ass_df_details := v_column_expr;
83 c_app_ass_df_label := v_label_expr;
84 else
85 c_app_ass_df_details := 'as'||'g.ass_attribute1';
86 end if;
87
88 hr_reports.get_desc_flex('PER','PER_APPLICATIONS',
89 'app',v_title,v_label_expr,v_column_expr);
90 if v_column_expr is not null then
91 c_app_df_details := v_column_expr;
92 c_app_df_label := v_label_expr;
93 else
94 c_app_df_details := 'app.appl_attribute1';
95 end if;
96
97 hr_reports.get_desc_flex('PER','PER_CONTACTS',
98 'con',v_title,v_label_expr,v_column_expr);
99 if v_column_expr is not null then
100 c_cont_df_details := v_column_expr;
101 c_cont_df_label := v_label_expr;
102 else
103 c_cont_df_details := 'peo.cont_attribute1';
104 end if;
105
106 hr_reports.get_desc_flex('PER','PER_ADDRESSES',
107 'addr',v_title,v_label_expr,v_column_expr);
108 if v_column_expr is not null then
109 c_add_df_details := v_column_expr;
110 c_add_df_label := v_label_expr;
111 else
112 c_add_df_details := 'addr.addr_attribute1';
113 end if;
114
115 hr_reports.get_desc_flex('PER','PER_ASSIGNMENTS',
116 'asg',v_title,v_label_expr,v_column_expr);
117 if v_column_expr is not null then
118 c_ass_df_details := v_column_expr;
119 c_ass_df_label := v_label_expr;
120 else
121 c_ass_df_details := 'as'||'g.ass_attribute1';
122 end if;
123
124 hr_reports.get_desc_flex('PER','PER_ABSENCE_ATTENDANCES',
125 'a',v_title,v_label_expr,v_column_expr);
126 if v_column_expr is not null then
127 c_absence_df_details := v_column_expr;
128 c_absence_df_label := v_label_expr;
129 else
130 c_absence_df_details := 'a.attribute1';
131 end if;
132
133 hr_reports.get_desc_flex('PER','PER_PERIODS_OF_SERVICE',
134 'a',v_title,v_label_expr,v_column_expr);
135 if v_column_expr is not null then
136 c_per_serv_df_details := v_column_expr;
137 c_per_serv_df_label := v_label_expr;
138 else
139 c_per_serv_df_details := 'a.attribute1';
140 end if;
141
142 hr_reports.get_desc_flex('PER','Assignment Developer DF',
143 'f',v_title,v_label_expr,v_column_expr);
144 if v_column_expr is not null then
145 c_fur_info_df_details := v_column_expr;
146 c_fur_info_df_label := v_label_expr;
147 else
148 c_fur_info_df_details := 'f.aei_attribute1';
149 end if;
150
151 hr_reports.get_desc_flex('PAY','PAY_PERSONAL_PAYMENT_METHODS',
152 'ppm',v_title,v_label_expr,v_column_expr);
153 if v_column_expr is not null then
154 c_ppm_df_details := v_column_expr;
155 c_ppm_df_label := v_label_expr;
156 else
157 c_ppm_df_details := 'ppm.attribute1';
158 end if;
159
160 hr_reports.get_desc_flex('PAY','PAY_ELEMENT_ENTRIES',
161 'ee',v_title,v_label_expr,v_column_expr);
162 if v_column_expr is not null then
163 c_ele_df_details := v_column_expr;
164 c_ele_df_label := v_label_expr;
165 else
166 c_ele_df_details := 'ee.attribute1';
167 end if;
168
169 hr_reports.get_desc_flex('PER','PER_EVENTS',
170 'a',v_title,v_label_expr,v_column_expr);
171 if v_column_expr is not null then
172 c_event_df_details := v_column_expr;
173 c_event_df_label := v_label_expr;
174 else
175 c_event_df_details := 'a.attribute1';
176 end if;
177
178 hr_reports.get_desc_flex('PER','PER_BOOKINGS',
179 'b',v_title,v_label_expr,v_column_expr);
180 if v_column_expr is not null then
181 c_book_df_details := v_column_expr;
182 c_book_df_label := v_label_expr;
183 else
184 c_book_df_details := 'b.attribute1';
185 end if;
186
187 hr_reports.get_desc_flex('PER','PER_PERSON_ANALYSES',
188 'jr',v_title,v_label_expr,v_column_expr);
189 if v_column_expr is not null then
190 c_special_df_details := v_column_expr;
191 c_special_df_label := v_label_expr;
192 else
193 c_special_df_details := 'jr.attribute1';
194 end if;
195
196 select PBG.LEGISLATION_CODE
197 into v_legislation_code
198 from PER_BUSINESS_GROUPS PBG
199 where PBG.BUSINESS_GROUP_ID = p_business_group_id;
200
201 declare
202 v_id_flex_num varchar2(150);
203 begin
204 select PBG.COST_ALLOCATION_STRUCTURE
205 into v_id_flex_num
206 from PER_BUSINESS_GROUPS PBG
207 where PBG.BUSINESS_GROUP_ID = p_business_group_id;
208 if v_id_flex_num is not null then
209 c_cost_id_flex_num := v_id_flex_num;
210
211 null;
212 end if;
213 exception
214 when others then null;
215 end;
216
217 declare
218 v_id_flex_num number;
219 begin
220 select rule_mode
221 into v_id_flex_num
222 from pay_legislation_rules
223 where legislation_code = v_legislation_code
224 and rule_type = 'S'
225 and exists
226 (select null
227 from FND_SEGMENT_ATTRIBUTE_VALUES
228 where ID_FLEX_NUM = rule_mode
229 and APPLICATION_ID = 800
230 and ID_FLEX_CODE = 'SCL'
231 and SEGMENT_ATTRIBUTE_TYPE = 'ASSIGNMENT'
232 and ATTRIBUTE_VALUE = 'Y');
233
234 c_scl_id_flex_num := v_id_flex_num;
235
236 exception
237 when others then null;
238 end;
239
240
241 end;
242 return (TRUE);
243 end;
244
245 function c_get_auth_repformula(authorising_person_id in number, replacement_person_id in number) return number is
246 begin
247
248 declare
249 v_authorizer_name varchar2(600);
250 v_replacement_name varchar2(600);
251 begin
252 if authorising_person_id is not null then
253 select hr1.meaning || decode(hr1.meaning,null,null,' ')
254 || peo.first_name
255 || decode(peo.first_name,null,null,' ')
256 || peo.last_name
257 into v_authorizer_name
258 from per_all_people_f peo
259 , hr_lookups hr1
260 where AUTHORISING_PERSON_ID = peo.person_id
261 and peo.effective_start_date = (select min(peo2.effective_start_date)
262 from per_all_people_f peo2
263 where peo2.person_id = peo.person_id)
264 and hr1.lookup_type (+) = 'TITLE'
265 and hr1.lookup_code (+) = peo.title;
266
267 c_authorized_by := v_authorizer_name;
268 end if;
269
270 if replacement_person_id is not null then
271 select hr1.meaning || decode(hr1.meaning,null,null,' ')
272 || peo.first_name
273 || decode(peo.first_name,null,null,' ')
274 || peo.last_name
275 into v_replacement_name
276 from per_all_people_f peo
277 , hr_lookups hr1
278 where replacement_PERSON_ID = peo.person_id
279 and peo.effective_start_date = (select min(peo2.effective_start_date)
280 from per_all_people_f peo2
281 where peo2.person_id = peo.person_id )
282 and hr1.lookup_type (+) = 'TITLE'
283 and hr1.lookup_code (+) = peo.title;
284
285 c_replaced_by := v_replacement_name;
286 end if;
287 return('');
288 end;
289
290 RETURN NULL; end;
291
292 function AfterReport return boolean is
293 begin
294
295
296 --hr_standard.event('AFTER REPORT');
297
298 return (TRUE);
299 end;
300
301 --Functions to refer Oracle report placeholders--
302
303 Function c_req_value_p return varchar2 is
304 Begin
305 return c_req_value;
306 END;
307 Function C_req_descript_p return varchar2 is
308 Begin
309 return C_req_descript;
310 END;
311 Function C_authorized_by_p return varchar2 is
312 Begin
313 return C_authorized_by;
314 END;
315 Function C_replaced_by_p return varchar2 is
316 Begin
317 return C_replaced_by;
318 END;
319 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
320 Begin
321 return C_BUSINESS_GROUP_NAME;
322 END;
323 Function C_details_p return varchar2 is
324 Begin
325 return C_details;
326 END;
327 Function C_cont_details_p return varchar2 is
328 Begin
329 return C_cont_details;
330 END;
331 Function C_requirement_desc_p return varchar2 is
332 Begin
333 return C_requirement_desc;
334 END;
335 Function C_requirement_value_p return varchar2 is
336 Begin
337 return C_requirement_value;
338 END;
339 Function C_header_name_p return varchar2 is
340 Begin
341 return C_header_name;
342 END;
343 Function C_pay_meth_count_p return number is
344 Begin
345 return C_pay_meth_count;
346 END;
347 Function C_emp_df_details_p return varchar2 is
348 Begin
349 return C_emp_df_details;
350 END;
351 Function C_emp_leg_df_details_p return varchar2 is
352 Begin
353 return C_emp_leg_df_details;
354 END;
355 Function C_add_df_details_p return varchar2 is
356 Begin
357 return C_add_df_details;
358 END;
359 Function C_cont_df_details_p return varchar2 is
360 Begin
361 return C_cont_df_details;
362 END;
363 Function C_app_df_details_p return varchar2 is
364 Begin
365 return C_app_df_details;
366 END;
367 Function c_app_ass_df_details_p return varchar2 is
368 Begin
369 return c_app_ass_df_details;
370 END;
371 Function C_sec_ass_df_details_p return varchar2 is
372 Begin
373 return C_sec_ass_df_details;
374 END;
375 Function C_per_serv_df_details_p return varchar2 is
376 Begin
377 return C_per_serv_df_details;
378 END;
379 Function C_temp_p return varchar2 is
380 Begin
381 return C_temp;
382 END;
383 Function C_add_df_label_p return varchar2 is
384 Begin
385 return C_add_df_label;
386 END;
387 Function C_emp_df_label_p return varchar2 is
388 Begin
389 return C_emp_df_label;
390 END;
391 Function C_emp_leg_df_label_p return varchar2 is
392 Begin
393 return C_emp_leg_df_label;
394 END;
395 Function C_cont_df_label_p return varchar2 is
396 Begin
397 return C_cont_df_label;
398 END;
399 Function C_app_df_label_p return varchar2 is
400 Begin
401 return C_app_df_label;
402 END;
403 Function C_app_ass_df_label_p return varchar2 is
404 Begin
405 return C_app_ass_df_label;
406 END;
407 Function C_app_sec_status_details_p return varchar2 is
408 Begin
409 return C_app_sec_status_details;
410 END;
411 Function C_app_sec_status_label_p return varchar2 is
412 Begin
413 return C_app_sec_status_label;
414 END;
415 Function C_inter_df_details_p return varchar2 is
416 Begin
417 return C_inter_df_details;
418 END;
419 Function C_inter_df_label_p return varchar2 is
420 Begin
421 return C_inter_df_label;
422 END;
423 Function C_sec_ass_df_label_p return varchar2 is
424 Begin
425 return C_sec_ass_df_label;
426 END;
427 Function C_ass_df_details_p return varchar2 is
428 Begin
429 return C_ass_df_details;
430 END;
431 Function C_ass_df_label_p return varchar2 is
432 Begin
433 return C_ass_df_label;
434 END;
435 Function C_fur_info_df_details_p return varchar2 is
436 Begin
437 return C_fur_info_df_details;
438 END;
439 Function C_fur_info_df_label_p return varchar2 is
440 Begin
441 return C_fur_info_df_label;
442 END;
443 Function C_fur_info_ddf_details_p return varchar2 is
444 Begin
445 return C_fur_info_ddf_details;
446 END;
447 Function C_fur_info_ddf_label_p return varchar2 is
448 Begin
449 return C_fur_info_ddf_label;
450 END;
451 Function C_cost_id_flex_num_p return varchar2 is
452 Begin
453 return C_cost_id_flex_num;
454 END;
455 Function C_scl_id_flex_num_p return number is
456 Begin
457 return C_scl_id_flex_num;
458 END;
459 Function C_scl_desc_p return varchar2 is
460 Begin
461 return C_scl_desc;
462 END;
463 Function C_scl_value_p return varchar2 is
464 Begin
465 return C_scl_value;
466 END;
467 Function C_cost_desc_p return varchar2 is
468 Begin
469 return C_cost_desc;
470 END;
471 Function C_cost_values_p return varchar2 is
472 Begin
473 return C_cost_values;
474 END;
475 Function C_ppm_df_details_p return varchar2 is
476 Begin
477 return C_ppm_df_details;
478 END;
479 Function C_ppm_df_label_p return varchar2 is
480 Begin
481 return C_ppm_df_label;
482 END;
483 Function C_ext_act_desc_p return varchar2 is
484 Begin
485 return C_ext_act_desc;
486 END;
487 Function C_ext_act_values_p return varchar2 is
488 Begin
489 return C_ext_act_values;
490 END;
491 Function C_ele_df_details_p return varchar2 is
492 Begin
493 return C_ele_df_details;
494 END;
495 Function C_ele_df_label_p return varchar2 is
496 Begin
497 return C_ele_df_label;
498 END;
499 Function C_ele_cost_desc_p return varchar2 is
500 Begin
501 return C_ele_cost_desc;
502 END;
503 Function C_ele_cost_values_p return varchar2 is
504 Begin
505 return C_ele_cost_values;
506 END;
507 Function C_event_df_details_p return varchar2 is
508 Begin
509 return C_event_df_details;
510 END;
511 Function C_event_df_label_p return varchar2 is
512 Begin
513 return C_event_df_label;
514 END;
515 Function C_book_df_details_p return varchar2 is
516 Begin
517 return C_book_df_details;
518 END;
519 Function C_book_df_label_p return varchar2 is
520 Begin
521 return C_book_df_label;
522 END;
523 Function C_special_df_details_p return varchar2 is
524 Begin
525 return C_special_df_details;
526 END;
527 Function C_special_df_label_p return varchar2 is
528 Begin
529 return C_special_df_label;
530 END;
531 Function C_per_serv_df_label_p return varchar2 is
532 Begin
533 return C_per_serv_df_label;
534 END;
535 Function C_absence_df_details_p return varchar2 is
536 Begin
537 return C_absence_df_details;
538 END;
539 Function C_absence_df_label_p return varchar2 is
540 Begin
541 return C_absence_df_label;
542 END;
543 Function C_subtitle_p return varchar2 is
544 Begin
545 return C_subtitle;
546 END;
547 Function C_END_OF_TIME_p return varchar2 is
548 Begin
549 return C_END_OF_TIME;
550 END;
551 END PER_PERRPFP4_XMLP_PKG ;