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