[Home] [Help]
PACKAGE BODY: APPS.PER_PAYRPEMV_XMLP_PKG
Source
1 PACKAGE BODY PER_PAYRPEMV_XMLP_PKG AS
2 /* $Header: PAYRPEMVB.pls 120.1 2007/12/06 11:24:34 amakrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 declare
8 l_organization_structure_desc VARCHAR2(80);
9 l_org_version_desc NUMBER;
10 l_version_start_date DATE;
11 l_version_end_date DATE;
12 l_organization_desc VARCHAR2(240);
13 l_organization_type VARCHAR2(30);
14 l_payroll_period VARCHAR2(80);
15 l_payroll_period_start_date DATE;
16 l_payroll_period_end_date DATE;
17 l_emp_det_param VARCHAR2(80);
18
19
20 begin
21
22 --null;
23
24 --hr_standard.event('BEFORE REPORT');
25 P_DATE_FROM_T := to_char(P_DATE_FROM,'dd-mon-yyyy');
26 P_DATE_TO_T := to_char(P_DATE_TO,'dd-mon-yyyy');
27
28 c_business_group_name :=
29 hr_reports.get_business_group(p_business_group_id);
30
31 if p_org_structure_version_id is not null then
32 hr_reports.get_organization_hierarchy
33 (null
34 ,p_org_structure_version_id
35 ,l_organization_structure_desc
36 ,l_org_version_desc
37 ,l_version_start_date
38 ,l_version_end_date);
39
40 c_org_structure_name := l_organization_structure_desc;
41 c_version_number := l_org_version_desc;
42
43
44 end if;
45
46 if p_parent_organization_id is not null then
47
48 hr_reports.get_organization
49 (p_parent_organization_id
50 ,l_organization_desc
51 ,l_organization_type);
52
53 c_parent_organization_name := l_organization_desc;
54
55
56 end if;
57
58 if p_payroll_id is not null then
59 c_payroll_name :=
60 hr_reports.get_payroll_name(p_session_date,p_payroll_id);
61
62 p_payroll_matching :=
63 ' and paf.payroll_id +0 = '|| to_char(p_payroll_id);
64
65 p_payroll_matching2 :=
66 ' and paf2.payroll_id +0 = '|| to_char(p_payroll_id);
67
68 p_payroll_matching3 :=
69 ' and paf3.payroll_id +0 = '|| to_char(p_payroll_id);
70
71 end if;
72
73 if p_org_structure_version_id is not null
74 and p_parent_organization_id is not null then
75
76
77 p_org_matching :=
78 ' and paf.organization_id in '||
79 '(select to_char(pose.organization_id_child) '||
80 'from per_org_structure_elements pose '||
81 'connect by pose.organization_id_parent = '||
82 'prior pose.organization_id_child '||
83 'and pose.org_structure_version_id = '||
84 to_char(p_org_structure_version_id) ||
85 ' start with pose.organization_id_parent = '||
86 to_char(p_parent_organization_id) ||
87 ' and pose.org_structure_version_id = '||
88 to_char(p_org_structure_version_id) ||
89 ' union select ' ||''''||
90 to_char(p_parent_organization_id) ||''''||
91 ' from sys.dual) ';
92
93
94 p_org_matching2 :=
95 ' and paf2.organization_id in '||
96 '(select to_char(pose.organization_id_child) '||
97 'from per_org_structure_elements pose '||
98 'connect by pose.organization_id_parent = '||
99 'prior pose.organization_id_child '||
100 'and pose.org_structure_version_id = '||
101 to_char(p_org_structure_version_id) ||
102 ' start with pose.organization_id_parent = '||
103 to_char(p_parent_organization_id) ||
104 ' and pose.org_structure_version_id = '||
105 to_char(p_org_structure_version_id) ||
106 ' union select ' ||''''||
107 to_char(p_parent_organization_id) ||''''||
108 ' from sys.dual) ';
109
110 p_org_matching3 :=
111 ' and paf3.organization_id in '||
112 '(select to_char(pose.organization_id_child) '||
113 'from per_org_structure_elements pose '||
114 'connect by pose.organization_id_parent = '||
115 'prior pose.organization_id_child '||
116 'and pose.org_structure_version_id = '||
117 to_char(p_org_structure_version_id) ||
118 ' start with pose.organization_id_parent = '||
119 to_char(p_parent_organization_id) ||
120 ' and pose.org_structure_version_id = '||
121 to_char(p_org_structure_version_id) ||
122 ' union select ' ||''''||
123 to_char(p_parent_organization_id) ||''''||
124 ' from sys.dual) ';
125
126 elsif
127 p_parent_organization_id is not null then
128 p_org_matching :=
129 ' and paf.organization_id = ' ||
130 to_char(p_parent_organization_id);
131
132 p_org_matching2 :=
133 ' and paf2.organization_id = ' ||
134 to_char(p_parent_organization_id);
135
136 p_org_matching3 :=
137 ' and paf3.organization_id = ' ||
138 to_char(p_parent_organization_id);
139
140 elsif p_org_structure_version_id is not null then
141 p_org_matching :=
142 ' and paf.organization_id in '||
143 '( select organization_id_child '||
144 'from per_org_structure_elements '||
145 'where org_structure_version_id = '||
146 to_char(p_org_structure_version_id) ||
147 ' union '||
148 'select distinct organization_id_parent '||
149 'from per_org_structure_elements '||
150 'where org_structure_version_id = '||
151 to_char(p_org_structure_version_id) ||')';
152
153 p_org_matching2 :=
154 ' and paf.organization_id in '||
155 '( select organization_id_child '||
156 'from per_org_structure_elements '||
157 'where org_structure_version_id = '||
158 to_char(p_org_structure_version_id) ||
159 ' union '||
160 'select distinct organization_id_parent '||
161 'from per_org_structure_elements '||
162 'where org_structure_version_id = '||
163 to_char(p_org_structure_version_id) ||')';
164
165 p_org_matching3 :=
166 ' and paf.organization_id in '||
167 '( select organization_id_child '||
168 'from per_org_structure_elements '||
169 'where org_structure_version_id = '||
170 to_char(p_org_structure_version_id) ||
171 ' union '||
172 'select distinct organization_id_parent '||
173 'from per_org_structure_elements '||
174 'where org_structure_version_id = '||
175 to_char(p_org_structure_version_id) ||')';
176
177 end if;
178
179 if p_payroll_period_id is not null then
180 hr_reports.get_time_period(p_payroll_period_id,
181 l_payroll_period,
182 l_payroll_period_start_date,
183 l_payroll_period_end_date);
184
185 c_payroll_period := l_payroll_period;
186
187
188
189
190
191
192
193
194
195 p_dates_matching :=
196 ' and to_date(''' || to_char(l_payroll_period_start_date, 'MMDDYYYY') ||
197 ''', ''MMDDYYYY'') between paf.effective_start_date and paf.effective_end_date ';
198
199 p_dates_matching2 :=
200 ' and to_date(''' || to_char(l_payroll_period_end_date, 'MMDDYYYY') ||
201 ''', ''MMDDYYYY'') between paf.effective_start_date and paf.effective_end_date ';
202
203 p_dates_matching3 :=
204 ' between to_date(''' ||to_char(l_payroll_period_start_date, 'MMDDYYYY') ||
205 ''', ''MMDDYYYY'') and to_date(''' || to_char(l_payroll_period_end_date, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
206
207 p_dates_matching4 :=
208 'to_date(''' || to_char(l_payroll_period_start_date, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
209
210 p_dates_matching5 :=
211 'to_date(''' || to_char(l_payroll_period_end_date, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
212
213
214 /*srw.message(1, 'String ->'||p_dates_matching);*/null;
215
216 /*srw.message(2, 'String ->'||p_dates_matching2);*/null;
217
218 /*srw.message(3, 'String ->'||p_dates_matching3);*/null;
219
220 /*srw.message(4, 'String ->'||p_dates_matching4);*/null;
221
222 /*srw.message(5, 'String ->'||p_dates_matching5);*/null;
223
224
225
226 elsif p_date_from is not null and p_date_to is not null then
227
228
229
230
231
232
233
234 p_dates_matching :=
235 ' and to_date(''' || to_char(p_date_from, 'MMDDYYYY') ||
236 ''', ''MMDDYYYY'') between paf.effective_start_date and paf.effective_end_date ';
237
238 p_dates_matching2 :=
239 ' and to_date(''' || to_char(p_date_to, 'MMDDYYYY') ||
240 ''', ''MMDDYYYY'') between paf.effective_start_date and paf.effective_end_date ';
241
242 p_dates_matching3:=
243 ' between to_date(''' ||to_char(p_date_from, 'MMDDYYYY') ||
244 ''', ''MMDDYYYY'') and to_date(''' || to_char(p_date_to, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
245
246 p_dates_matching4 :=
247 'to_date(''' || to_char(p_date_from, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
248
249 p_dates_matching5 :=
250 'to_date(''' || to_char(p_date_to, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
251
252 /*srw.message(11, 'String ->'||p_dates_matching);*/null;
253
254 /*srw.message(22, 'String ->'||p_dates_matching2);*/null;
255
256 /*srw.message(33, 'String ->'||p_dates_matching3);*/null;
257
258 /*srw.message(44, 'String ->'||p_dates_matching4);*/null;
259
260 /*srw.message(55, 'String ->'||p_dates_matching5);*/null;
261
262
263
264 elsif p_date_from is not null then
265
266 p_dates_matching3 := ' >= to_date(''' ||to_char(p_date_from, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
267
268 p_dates_matching := 'and paf.effective_start_date >= to_date(''' ||to_char(p_date_from, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
269
270
271 elsif p_date_to is not null then
272
273 p_dates_matching2 :=
274 'and paf.effective_start_date <= to_date(''' || to_char(p_date_to, 'MMDDYYYY') ||''', ''MMDDYYYY'')';
275
276
277 p_dates_matching3 := ' <= to_date(''' ||to_char(p_date_to, 'MMDDYYYY') || ''', ''MMDDYYYY'')';
278
279 end if;
280
281 if upper(p_employee_detail) = 'S' then
282 p_emp_ord_clause := null;
283 elsif upper(p_employee_detail) = 'A' then
284 p_emp_ord_clause :='ORDER BY 1,3';
285
286 elsif upper(p_employee_detail) = 'E' then
287 p_emp_ord_clause := 'ORDER BY 1,2';
288
289 end if;
290 /*srw.message(77, 'Order clause: '||p_emp_ord_clause);*/null;
291
292
293 begin
294 select hrl.meaning
295 into l_emp_det_param
296 from hr_lookups hrl
297 where p_employee_detail = hrl.lookup_code
298 and hrl.lookup_type = 'PAYRPEMV_EMP_DET';
299
300 exception
301 when no_data_found then return null;
302 end;
303
304 c_emp_det_param_disp := l_emp_det_param ;
305 begin
306 select hrl.meaning
307 into CP_WORKER_TYPE_DESC
308 from hr_lookups hrl
309 where p_worker_type = hrl.lookup_code
310 and hrl.lookup_type = 'HR_HEADCOUNT_WORKER_TYPE';
311 exception
312 when no_data_found then return CP_WORKER_TYPE_DESC = 'N/A';
313 end;
314
315 ---Added for DT Fixes---
316 if P_ORG_MATCHING is null
317 then P_ORG_MATCHING := ' ';
318 end if;
319
320 if P_PAYROLL_MATCHING is null
321 then P_PAYROLL_MATCHING := ' ';
322 end if;
323
324 if P_ORG_MATCHING3 is null
325 then P_ORG_MATCHING3 := ' ';
326 end if;
327
328 if P_PAYROLL_MATCHING3 is null
329 then P_PAYROLL_MATCHING3 := ' '
330 ;
331 end if;
332
333 if P_DATES_MATCHING is null
334 then P_DATES_MATCHING := ' ';
335 end if;
336
337 if P_EMP_ORD_CLAUSE is null
338 then P_EMP_ORD_CLAUSE := ' ';
339 end if;
340
341 if P_DATES_MATCHING2 is null
342 then P_DATES_MATCHING2 :=' ';
343 end if;
344
345 ---End of DT Fixes------
346
347 end;
348 return (TRUE);
349 end;
350
351 function c_net_changeformula(c_new_hires_count in number, c_transfers_in in number, c_terminations_count in number, c_transfers_out in number) return varchar2 is
352 begin
353
354 return (to_char( (c_new_hires_count
355 + c_transfers_in)
356 - (c_terminations_count
357 + c_transfers_out) , 'FMS999990'));
358 end;
359
360 function C_sql_traceFormula return VARCHAR2 is
361 begin
362
363 /*srw.do_sql('Alter session set sql_trace=true');*/null;
364
365 RETURN NULL; end;
366
367 function TRACEFormula return VARCHAR2 is
368 begin
369
370 if p_trace='Y' then
371 /*SRW.DO_SQL('ALTER SESSION SET SQL_TRACE=TRUE');*/null;
372
373 end if;
377 begin
374 RETURN NULL; end;
375
376 function cf_control_total_newhireformul(new_hire_asg_type in varchar2, assignment_type in varchar2) return number is
378 if new_hire_asg_type = 'E' then
379 cp_total_emp_newhire := cp_total_emp_newhire + 1;
380 end if;
381 if assignment_type = 'C' then
382 cp_total_cwk_newhire := cp_total_cwk_newhire + 1;
383 end if;
384 return 1;
385 end;
386
387 function cf_control_total_termformula(term_asg_type in varchar2) return number is
388 begin
389 if term_asg_type = 'E' then
390 cp_total_emp_term := cp_total_emp_term + 1;
391 end if;
392 if term_asg_type = 'C' then
393 cp_total_cwk_term := cp_total_cwk_term + 1;
394 end if;
395 return 1;
396 end;
397
398 function cf_control_total_transinformul(trans_ex_asg_type in varchar2) return number is
399 begin
400 if trans_ex_asg_type = 'E' then
401 cp_total_emp_transin := cp_total_emp_transin + 1;
402 end if;
403 if trans_ex_asg_type = 'C' then
404 cp_total_cwk_transin := cp_total_cwk_transin + 1;
405 end if;
406 return 1;
407 end;
408
409 function cf_control_total_transoutformu(transout_ex_asg_type in varchar2) return number is
410 begin
411 if transout_ex_asg_type = 'E' then
412 cp_total_emp_transout := cp_total_emp_transout+ 1;
413 end if;
414 if transout_ex_asg_type = 'C' then
415 cp_total_cwk_transout := cp_total_cwk_transout + 1;
416 end if;
417 return 1;
418 end;
419
420 function AfterReport return boolean is
421 begin
422 --hr_standard.event('AFTER REPORT');
423 return (TRUE);
424 end;
425
426 --Functions to refer Oracle report placeholders--
427
428 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
429 Begin
430 return C_BUSINESS_GROUP_NAME;
431 END;
432 Function C_REPORT_SUBTITLE_p return varchar2 is
433 Begin
434 return C_REPORT_SUBTITLE;
435 END;
436 Function C_ORG_STRUCTURE_NAME_p return varchar2 is
437 Begin
438 return C_ORG_STRUCTURE_NAME;
439 END;
440 Function C_VERSION_NUMBER_p return number is
441 Begin
442 return C_VERSION_NUMBER;
443 END;
444 Function C_PARENT_ORGANIZATION_NAME_p return varchar2 is
445 Begin
446 return C_PARENT_ORGANIZATION_NAME;
447 END;
448 Function C_PAYROLL_NAME_p return varchar2 is
449 Begin
450 return C_PAYROLL_NAME;
451 END;
452 Function C_PAYROLL_PERIOD_p return varchar2 is
453 Begin
454 return C_PAYROLL_PERIOD;
455 END;
456 Function C_emp_det_param_disp_p return varchar2 is
457 Begin
458 return C_emp_det_param_disp;
459 END;
460 Function CP_worker_type_desc_p return varchar2 is
461 Begin
462 return CP_worker_type_desc;
463 END;
464 Function CP_total_emp_newhire_p return number is
465 Begin
466 return CP_total_emp_newhire;
467 END;
468 Function CP_total_cwk_newhire_p return number is
469 Begin
470 return CP_total_cwk_newhire;
471 END;
472 Function CP_total_emp_term_p return number is
473 Begin
474 return CP_total_emp_term;
475 END;
476 Function CP_total_cwk_term_p return number is
477 Begin
478 return CP_total_cwk_term;
479 END;
480 Function CP_total_emp_transin_p return number is
481 Begin
482 return CP_total_emp_transin;
483 END;
484 Function CP_total_cwk_transin_p return number is
485 Begin
486 return CP_total_cwk_transin;
487 END;
488 Function CP_total_emp_transout_p return number is
489 Begin
490 return CP_total_emp_transout;
491 END;
492 Function CP_total_cwk_transout_p return number is
493 Begin
494 return CP_total_cwk_transout;
495 END;
496 END PER_PAYRPEMV_XMLP_PKG ;