[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_EMPLOYMENT_STATISTICS
Source
1 PACKAGE BODY PAY_SE_EMPLOYMENT_STATISTICS AS
2 /* $Header: pysestsr.pkb 120.0.12000000.1 2007/04/20 09:27:37 abhgangu noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE lock_rec IS RECORD (
6 archive_assact_id NUMBER
7 );
8
9 TYPE lock_table IS TABLE OF lock_rec
10 INDEX BY BINARY_INTEGER;
11
12 g_lock_table lock_table;
13 g_index NUMBER := -1;
14 g_index_assact NUMBER := -1;
15 g_index_bal NUMBER := -1;
16 g_package VARCHAR2 (33) := 'PAY_SE_EMPLOYMENT_STATISTICS.';
17 g_payroll_action_id NUMBER;
18 g_arc_payroll_action_id NUMBER;
19 -- Globals to pick up all the parameter
20 g_business_group_id NUMBER;
21 g_effective_date DATE;
22
23
24 g_legal_employer_id NUMBER;
25 g_local_unit_id NUMBER;
26 g_LE_request VARCHAR2 (240);
27 g_LU_request VARCHAR2 (240);
28
29 g_posting_date DATE;
30 g_account_date DATE;
31 g_reporting_date DATE;
32 --End of Globals to pick up all the parameter
33 g_format_mask VARCHAR2 (50);
34 g_err_num NUMBER;
35 g_errm VARCHAR2 (150);
36
37 /* GET PARAMETER */
38
39
40 /* GET PARAMETER */
41 FUNCTION get_parameter (
42 p_parameter_string IN VARCHAR2
43 , p_token IN VARCHAR2
44 , p_segment_number IN NUMBER DEFAULT NULL
45 )
46 RETURN VARCHAR2
47 IS
48 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
49 l_start_pos NUMBER;
50 l_delimiter VARCHAR2 (1) := ' ';
51 l_proc VARCHAR2 (240) := g_package || ' get parameter ';
52 BEGIN
53 --
54 IF g_debug
55 THEN
56 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
57 END IF;
58
59 l_start_pos :=
60 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
61
62 --
63 IF l_start_pos = 0
64 THEN
65 l_delimiter := '|';
66 l_start_pos :=
67 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
68 END IF;
69
70 IF l_start_pos <> 0
71 THEN
72 l_start_pos := l_start_pos + LENGTH (p_token || '=');
73 l_parameter :=
74 SUBSTR (p_parameter_string
75 , l_start_pos
76 , INSTR (p_parameter_string || ' '
77 , l_delimiter
78 , l_start_pos
79 )
80 - (l_start_pos)
81 );
82
83 IF p_segment_number IS NOT NULL
84 THEN
85 l_parameter := ':' || l_parameter || ':';
86 l_parameter :=
87 SUBSTR (l_parameter
88 , INSTR (l_parameter, ':', 1, p_segment_number) + 1
89 , INSTR (l_parameter, ':', 1, p_segment_number + 1)
90 - 1
91 - INSTR (l_parameter, ':', 1, p_segment_number)
92 );
93 END IF;
94 END IF;
95
96 --
97 IF g_debug
98 THEN
99 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
100 END IF;
101
102 RETURN l_parameter;
103 END;
104
105 /* GET ALL PARAMETERS */
106 PROCEDURE get_all_parameters (
107 p_payroll_action_id IN NUMBER -- In parameter
108 , p_business_group_id OUT NOCOPY NUMBER -- Core parameter
109 , p_effective_date OUT NOCOPY DATE -- Core parameter
110 , p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
111 , p_LE_request OUT NOCOPY VARCHAR2 -- User parameter
112 , p_LU_request OUT NOCOPY VARCHAR2 -- User parameter
113 , p_LOCAL_UNIT_id OUT NOCOPY NUMBER -- User parameter
114 , p_ACCOUNT_date OUT NOCOPY DATE -- User parameter
115 , p_POSTING_date OUT NOCOPY DATE -- User parameter
116 , p_REPORTING_date OUT nocopy DATE -- user parameter
117 )
118 IS
119 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
120 IS
121 SELECT (PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
122 (legislative_parameters
123 , 'LE_REQUEST'
124 )
125 ) LE_REQUEST
126 ,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
127 (legislative_parameters
128 , 'LEGAL_EMPLOYER_ID'
129 )
130 ) LEGAL_EMPLOYER_ID
131 , (PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
132 (legislative_parameters
133 , 'LU_REQUEST'
134 )
135 ) LU_REQUEST
136 ,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
137 (legislative_parameters
138 , 'LOCAL_UNIT_ID'
139 )
140 ) LOCAL_UNIT_ID
141 ,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
142 (legislative_parameters
143 , 'ACCOUNT_DATE'
144 )
145 ) ACCOUNT_DATE
146 ,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
147 (legislative_parameters
148 , 'POSTING_DATE'
149 )
150 ) POSTING_DATE
151 ,(PAY_SE_EMPLOYMENT_STATISTICS.get_parameter
152 (legislative_parameters
153 , 'REPORTING_DATE'
154 )
155 )REPORTING_DATE
156 , effective_date effective_date, business_group_id bg_id
157 FROM pay_payroll_actions
158 WHERE payroll_action_id = p_payroll_action_id;
159
160 lr_parameter_info csr_parameter_info%ROWTYPE;
161 l_proc VARCHAR2 (240)
162 := g_package || ' GET_ALL_PARAMETERS ';
163 BEGIN
164
165
166 OPEN csr_parameter_info (p_payroll_action_id);
167
168 --FETCH csr_parameter_info into lr_parameter_info;
169 FETCH csr_parameter_info
170 INTO lr_parameter_info;
171
172 CLOSE csr_parameter_info;
173
174
175 p_legal_employer_id := lr_parameter_info.legal_employer_id;
176
177
178 p_LE_request := lr_parameter_info.LE_REQUEST;
179
180
181 p_LU_request := lr_parameter_info.LU_REQUEST;
182
183
184 p_local_unit_id := lr_parameter_info.LOCAL_UNIT_ID;
185
186
187 p_ACCOUNT_DATE :=
188 fnd_date.canonical_to_date (lr_parameter_info.ACCOUNT_DATE);
189
190
191 P_POSTING_DATE :=
192 fnd_date.canonical_to_date (lr_parameter_info.POSTING_DATE);
193 P_REPORTING_DATE :=
194 fnd_date.canonical_to_date (lr_parameter_info.REPORTING_DATE);
195 p_effective_date := lr_parameter_info.effective_date;
196 p_business_group_id := lr_parameter_info.bg_id;
197
198
199 IF g_debug
200 THEN
201 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS'
202 , 30);
203 END IF;
204 END get_all_parameters;
205
206 /* RANGE CODE */
207 PROCEDURE range_code (
208 p_payroll_action_id IN NUMBER
209 , p_sql OUT NOCOPY VARCHAR2
210 )
211 IS
212 l_action_info_id NUMBER;
213 l_ovn NUMBER;
214 l_business_group_id NUMBER;
215 l_start_date VARCHAR2 (30);
216 l_end_date VARCHAR2 (30);
217 l_effective_date DATE;
218 l_consolidation_set NUMBER;
219 l_defined_balance_id NUMBER := 0;
220 l_count NUMBER := 0;
221 l_prev_prepay NUMBER := 0;
222 l_canonical_start_date DATE;
223 l_canonical_end_date DATE;
224 l_payroll_id NUMBER;
225 l_prepay_action_id NUMBER;
226 l_actid NUMBER;
227 l_assignment_id NUMBER;
228 l_action_sequence NUMBER;
229 l_assact_id NUMBER;
230 l_pact_id NUMBER;
231 l_flag NUMBER := 0;
232 l_element_context VARCHAR2 (5);
233
234 -- Archiving the data , as this will fire once
235 CURSOR csr_legal_employer_details (
236 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
237 )
238 IS
239 SELECT o1.NAME legal_employer_name
240 , hoi2.org_information2 org_number
241 , hoi1.organization_id legal_id
242 FROM hr_organization_units o1
243 , hr_organization_information hoi1
244 , hr_organization_information hoi2
245 WHERE o1.business_group_id = g_business_group_id
246 AND hoi1.organization_id = o1.organization_id
247 AND hoi1.organization_id = csr_v_legal_employer_id
248 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
249 AND hoi1.org_information_context = 'CLASS'
250 AND o1.organization_id = hoi2.organization_id
251 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
252
253 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
254 L_LEGAL_EMPLOYER_NAME VARCHAR2(240);
255
256 CURSOR csr_check_empty_le (
257 csr_v_legal_employer_id NUMBER
258 , csr_v_canonical_start_date DATE
259 , csr_v_canonical_end_date DATE
260 )
261 IS
262 SELECT '1'
263 FROM pay_payroll_actions appa
264 , pay_assignment_actions act
265 , per_all_assignments_f as1
266 , pay_payroll_actions ppa
267 WHERE ppa.payroll_action_id = p_payroll_action_id
268 AND appa.effective_date BETWEEN csr_v_canonical_start_date
269 AND csr_v_canonical_end_date
270 AND appa.action_type IN ('R', 'Q')
271 -- Payroll Run or Quickpay Run
272 AND act.payroll_action_id = appa.payroll_action_id
273 AND act.source_action_id IS NULL -- Master Action
274 AND as1.assignment_id = act.assignment_id
275 AND as1.business_group_id = g_business_group_id
276 AND act.action_status = 'C' -- Completed
277 AND act.tax_unit_id = csr_v_legal_employer_id
278 AND appa.effective_date BETWEEN as1.effective_start_date
279 AND as1.effective_end_date
280 AND ppa.effective_date BETWEEN as1.effective_start_date
281 AND as1.effective_end_date
282 ORDER BY as1.person_id, act.assignment_id;
283
284 l_le_has_employee VARCHAR2 (2);
285 -- Archiving the data , as this will fire once
286 -- ********************* for cfar from lU ***********************
287 CURSOR csr_CFAR_FROM_LU (
288 csr_local_unit_ID NUMBER
289 )
290 is
291 select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
292 from HR_ORGANIZATION_UNITS o1
293 , HR_ORGANIZATION_INFORMATION hoi1
294 , HR_ORGANIZATION_INFORMATION hoi2
295 WHERE o1.business_group_id = g_business_group_id
296 and hoi1.organization_id = o1.organization_id
297 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
298 and hoi1.org_information_context = 'CLASS'
299 and o1.organization_id = hoi2.organization_id
300 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
301 and o1.organization_id = csr_local_unit_ID;
302
303 lr_CFAR_FROM_LU csr_CFAR_FROM_LU%ROWTYPE;
304 -- ********************* for cfar from lU ***********************
305 -- ********************* for ASIGNMENT fOR GIVEN LU ***********************
306 -- ********************* With group by FT,PT,FR,PR types ****************
307 CURSOR CSR_PERSON_TYPES_COUNT (
308 csr_local_unit_ID NUMBER
309 )
310 is
311 SELECT
312 count(paa.assignment_id) TOTAL
313 ,paa.employment_category EMP_CAT
314 ,pap.SEX
315 FROM
316 PER_ALL_ASSIGNMENTS_F paa
317 ,HR_SOFT_CODING_KEYFLEX scl1
318 ,PER_ALL_PEOPLE_F pap
319 WHERE
320 paa.person_id = pap.person_id
321 and paa.business_group_id = g_business_group_id
322 and scl1.segment2 = TO_CHAR(csr_local_unit_ID)
323 AND scl1.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
324 and paa.employment_category in ('FR','FT')
325 and paa.ASSIGNMENT_STATUS_TYPE_ID = 1
326 and pap.SEX in('F','M')
327 AND paa.PRIMARY_FLAG='Y'
328 and g_account_date between paa.EFFECTIVE_START_DATE and paa.EFFECTIVE_END_DATE
329 and g_account_date between pap.EFFECTIVE_START_DATE and pap.EFFECTIVE_END_DATE
330 group by paa.employment_category,pap.SEX
331 order by paa.employment_category,pap.SEX;
332
333 LR_PERSON_TYPES_COUNT CSR_PERSON_TYPES_COUNT%ROWTYPE;
334 -- ********************* for ASIGNMENT fOR GIVEN LU ***********************
335 -- ********************* With group by FT,PT,FR,PR types ****************
336 -- ********************* for ALL LU for GIVEN LE***********************
337 CURSOR csr_ALL_LU_FOR_LE (
338 csr_legal_employer_ID NUMBER
339 ) is
340 SELECT hoi_le.org_information1 local_unit_id,
341 hou_lu.NAME local_unit_name,
342 hoi_lu.org_information2 CFAR
343 FROM hr_organization_units hou_le,
344 hr_organization_information hoi_le,
345 hr_organization_units hou_lu,
346 hr_organization_information hoi_lu
347 WHERE hoi_le.organization_id = hou_le.organization_id
348 AND hou_le.organization_id = csr_legal_employer_ID
349 AND hoi_le.org_information_context = 'SE_LOCAL_UNITS'
350 AND hou_lu.organization_id = hoi_le.org_information1
351 AND hou_lu.organization_id = hoi_lu.organization_id
352 AND hoi_lu.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
353
354 -- ********************* for ALL LU for GIVEN LE***********************
355 -- ********************* ALL LE for GIVEN Business group id***********************
356 CURSOR csr_ALL_LE_FOR_BG is
357 select o1.name legal_employer_name,hoi1.organization_id
358 from hr_organization_units o1,
359 hr_organization_information hoi1,
360 hr_organization_information hoi2
361 where o1.business_group_id = g_business_group_id
362 and hoi1.organization_id = o1.organization_id
363 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
364 and hoi1.org_information_context = 'CLASS'
365 and o1.organization_id = hoi2.organization_id
366 and hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
367 -- ********************* ALL LE for GIVEN Business group id***********************
368 /* To Get All the Persons, sex under the Local unit within the period */
369 CURSOR csr_person_local_unit(p_local_unit_id number) is
370 SELECT DISTINCT paaf.person_id,sex
371 FROM per_all_assignments_f paaf,
372 per_all_people_f papf,
373 hr_soft_coding_keyflex hsck
374 WHERE paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
375 AND hsck.segment2=to_char(p_local_unit_id)
376 AND papf.person_id=paaf.person_id
377 and papf.SEX in('F','M')
378 AND paaf.PRIMARY_FLAG='Y'
379 AND g_account_date BETWEEN paaf.effective_start_date
380 AND paaf.effective_end_date
381 AND g_account_date BETWEEN papf.effective_start_date
382 AND papf.effective_end_date;
383 /* To Get count of the absences under the person, either vacation or sickness */
384 CURSOR csr_person_absence(p_person_id number, p_month_start_date date, p_month_end_date date, p_absence_type_id varchar2) IS
385 SELECT count(*)
386 FROM per_absence_attendances paa,
387 per_absence_attendance_types pat
388 WHERE paa.person_id = p_person_id
389 AND g_account_date BETWEEN
390 paa.date_start AND paa.date_end
391 /*AND paa.date_start >=p_month_start_date
392 -- AND least(nvl(paa.date_end,p_abs_end_date),p_abs_end_date)<=p_abs_end_date
393 AND paa.date_end<=p_month_end_date */
394 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
395 AND pat.absence_category = p_absence_type_id --'S'
396 ORDER BY paa.date_end ;
397
398 /* To Get count of the absences under the person, other than vacation and sickness */
399 CURSOR csr_person_other_absence(p_person_id number, p_month_start_date date, p_month_end_date date) IS
400 SELECT count(*)
401 FROM per_absence_attendances paa,
402 per_absence_attendance_types pat
403 WHERE paa.person_id = p_person_id
404 AND g_account_date BETWEEN
405 paa.date_start AND paa.date_end
406 /*AND paa.date_start >=p_month_start_date
407 -- AND least(nvl(paa.date_end,p_abs_end_date),p_abs_end_date)<=p_abs_end_date
408 AND paa.date_end<=p_month_end_date*/
409 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
410 AND pat.absence_category NOT IN ('S','V')
411 ORDER BY paa.date_end ;
412
413 /* To Get the assignments within the accounting month period, which are new hire*/
414 CURSOR csr_new_assignments(p_month_start_date date, p_month_end_date date ) IS
415 SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
416 FROM PER_ALL_ASSIGNMENTS_F paaf,
417 hr_soft_coding_keyflex hsck
418 WHERE paaf.EFFECTIVE_START_DATE BETWEEN p_month_start_date
419 AND p_month_end_date
420 AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
421 AND hsck.segment2=to_char(g_local_unit_id)
422 and paaf.employment_category in ('FR','FT')
423 AND paaf.PRIMARY_FLAG='Y'
424 and paaf.ASSIGNMENT_STATUS_TYPE_ID = 1;
425
426 /* To Get the previous assignments for the current assignment*/
427 CURSOR csr_prev_assignments(p_assignment_id number, p_effecive_start_date date) IS
428 SELECT hsck.segment2
429 FROM per_all_assignments_f paaf,
430 hr_soft_coding_keyflex hsck
431 WHERE hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
432 AND paaf.assignment_id=p_assignment_id
433 AND paaf.EFFECTIVE_START_DATE=
434 (SELECT max(EFFECTIVE_START_DATE)
435 FROM per_all_assignments_f
436 WHERE assignment_id=p_assignment_id
437 AND EFFECTIVE_START_DATE < p_effecive_start_date);
438
439 /* To get the sex of the person, from the assignment */
440 CURSOR csr_person_sex(p_assignment_id number) IS
441 SELECT papf.sex FROM
442 per_all_assignments_f paaf,
443 per_all_people_f papf
444 WHERE paaf.person_id=papf.person_id
445 AND paaf.assignment_id=p_assignment_id
446 AND g_account_date BETWEEN paaf.effective_start_date
447 AND paaf.effective_end_date
448 AND g_account_date BETWEEN papf.effective_start_date
449 AND papf.effective_end_date;
450
451 /* To Get the assignments within the accounting month period, which are ended in one Local Unit*/
452 CURSOR csr_end_assignments(p_month_start_date date, p_month_end_date date ) IS
453 SELECT paaf.assignment_id,paaf.EFFECTIVE_end_DATE,paaf.employment_category
454 FROM PER_ALL_ASSIGNMENTS_F paaf,
455 hr_soft_coding_keyflex hsck
456 WHERE paaf.effective_end_date BETWEEN p_month_start_date
457 AND p_month_end_date
458 AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
459 AND hsck.segment2=to_char(g_local_unit_id)
460 and paaf.employment_category in ('FR','FT')
461 AND paaf.PRIMARY_FLAG='Y'
462 and paaf.ASSIGNMENT_STATUS_TYPE_ID = 1;
463
464 /* To Get the assignments within the accounting month period, which are terminated */
465 CURSOR csr_ter_assignments(p_month_start_date date, p_month_end_date date ) IS
466 /*SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
467 FROM PER_ALL_ASSIGNMENTS_F paaf,
468 hr_soft_coding_keyflex hsck
469 WHERE paaf.effective_end_date BETWEEN p_month_start_date
470 AND p_month_end_date
471 AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
472 AND hsck.segment2=to_char(g_local_unit_id)
473 and paaf.employment_category in ('FR','FT')
474 and paaf.ASSIGNMENT_STATUS_TYPE_ID = 3;*/
475 SELECT paaf.assignment_id,paaf.EFFECTIVE_START_DATE,paaf.employment_category
476 FROM
477 PER_ALL_ASSIGNMENTS_F paaf,
478 hr_soft_coding_keyflex hsck
479 WHERE
480 paaf.effective_start_date BETWEEN p_month_start_date
481 AND p_month_end_date
482 AND hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
483 AND hsck.segment2=to_char(g_local_unit_id)
484 and paaf.employment_category in ('FR','FT')
485 AND paaf.PRIMARY_FLAG='Y'
486 and paaf.ASSIGNMENT_STATUS_TYPE_ID = 3;
487
488
489 /* To Get the next assignments for the current assignment*/
490 CURSOR csr_next_assignments(p_assignment_id number, p_effecive_end_date date) IS
491 SELECT hsck.segment2
492 FROM per_all_assignments_f paaf,
493 hr_soft_coding_keyflex hsck
494 WHERE hsck.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
495 AND paaf.assignment_id=p_assignment_id
496 AND paaf.effective_end_date=
497 (SELECT min(effective_end_date)
498 FROM per_all_assignments_f
499 WHERE assignment_id=p_assignment_id
500 AND effective_end_date > p_effecive_end_date);
501
502 CURSOR csr_local_unit_details(p_local_unit_id number) IS
503 SELECT NAME FROM
504 hr_organization_units WHERE
505 organization_id=p_local_unit_id;
506
507 -- VARIABLE FOR THIS REPORET
508 L_DATE_OF_REPORT VARCHAR2(6);
509
510 L_FISCAL_YEAR VARCHAR2(4);
511 L_FISCAL_QUARTER VARCHAR2(1);
512 L_FISCAL_MONTH VARCHAR2(1);
513
514 L_CFAR_NUMBER VARCHAR2(8);
515
516 L_REGULAR_MEN NUMBER := 0;
517 L_REGULAR_WOMEN NUMBER := 0;
518
519 L_TEMPORARY_MEN NUMBER := 0;
520 L_TEMPORARY_WOMEN NUMBER := 0;
521
522 L_SICK_LEAVE_MEN NUMBER := 0;
523 L_SICK_LEAVE_WOMEN NUMBER := 0;
524
525 L_HOLIDAY_LEAVE_MEN NUMBER := 0;
526 L_HOLIDAY_LEAVE_WOMEN NUMBER := 0;
527
528 L_OTHER_ABSENCE_MEN NUMBER := 0;
529 L_OTHER_ABSENCE_WOMEN NUMBER := 0;
530
531 L_NEW_HIRES_REGULAR_MEN NUMBER := 0;
532 L_NEW_HIRES_REGULAR_WOMEN NUMBER := 0;
533
534 L_NEW_HIRES_TEMPORARY_MEN NUMBER := 0;
535 L_NEW_HIRES_TEMPORARY_WOMEN NUMBER := 0;
536
537 L_TERMINATION_REGULAR_MEN NUMBER := 0;
538 L_TERMINATION_REGULAR_WOMEN NUMBER := 0;
539
540 L_TERMINATION_TEMPORARY_MEN NUMBER := 0;
541 L_TERMINATION_TEMPORARY_WOMEN NUMBER := 0;
542
543 L_MONTH_START_DATE DATE;
544 L_MONTH_END_DATE DATE;
545 L_LOCAL_UNIT_NAME VARCHAR2(50);
546 l_person_id NUMBER;
547 l_sex CHAR(1);
548 l_local_unit_id NUMBER;
549 l_assignment_category VARCHAR2(5);
550 l_assignment_start_date DATE;
551 l_assignment_end_date DATE;
552 l_absence_count NUMBER;
553
554 -- VARIABLE FOR THIS REPORET
555 BEGIN
556
557
558 IF g_debug
559 THEN
560 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
561 END IF;
562
563 p_sql :=
564 'SELECT DISTINCT person_id
565 FROM per_people_f ppf
566 ,pay_payroll_actions ppa
567 WHERE ppa.payroll_action_id = :payroll_action_id
568 AND ppa.business_group_id = ppf.business_group_id
569 ORDER BY ppf.person_id';
570 g_payroll_action_id := p_payroll_action_id;
571 g_business_group_id := NULL;
572 g_effective_date := NULL;
573 g_LE_request := NULL;
574 g_LU_request :=null;
575 g_legal_employer_id := NULL;
576 g_local_unit_id := NULL;
577 g_account_date :=null;
578 g_posting_date :=null;
579 PAY_SE_EMPLOYMENT_STATISTICS.get_all_parameters (p_payroll_action_id
580 , g_business_group_id
581 , g_effective_date
582 , g_legal_employer_id
583 , g_LE_request
584 , g_LU_request
585 , g_local_unit_id
586 , g_account_date
587 , g_posting_date
588 , g_reporting_date
589 );
590
591 IF g_legal_employer_id IS NOT NULL
592 THEN
593 -- TO pick up the Name of the LE
594 OPEN csr_legal_employer_details (g_legal_employer_id);
595 FETCH csr_legal_employer_details INTO lr_legal_employer_details;
596 CLOSE csr_legal_employer_details;
597 L_LEGAL_EMPLOYER_NAME := lr_legal_employer_details.legal_employer_name;
598 ELSE
599 L_LEGAL_EMPLOYER_NAME :=null;
600 END IF;
601 OPEN csr_local_unit_details(g_local_unit_id);
602 fetch csr_local_unit_details into L_LOCAL_UNIT_NAME;
603 CLOSE csr_local_unit_details;
604 -- Insert the report Parameters
605 pay_action_information_api.create_action_information
606 (p_action_information_id => l_action_info_id
607 , p_action_context_id => p_payroll_action_id
608 , p_action_context_type => 'PA'
609 , p_object_version_number => l_ovn
610 , p_effective_date => g_effective_date
611 , p_source_id => NULL
612 , p_source_text => NULL
613 , p_action_information_category => 'EMEA REPORT DETAILS'
614 , p_action_information1 => 'PYSESTEA'
615 , p_action_information2 => hr_general.decode_lookup('SE_TAX_CARD_REQUEST_LEVEL',g_LE_request)
616 , p_action_information3 => g_legal_employer_id
617 , p_action_information4 => L_LEGAL_EMPLOYER_NAME
618 , p_action_information5 => hr_general.decode_lookup('SE_REQUEST_LEVEL',g_LU_request)
619 , p_action_information6 => g_local_unit_id
620 , p_action_information7 => L_LOCAL_UNIT_NAME
621 , p_action_information8 => fnd_date.date_to_canonical
622 (g_account_date)
623 , p_action_information9 => fnd_date.date_to_canonical
624 (g_posting_date)
625 , p_action_information10 => fnd_date.date_to_canonical(g_reporting_date)
626 );
627 -- *****************************************************************************
628
629
630
631 L_FISCAL_YEAR := TO_CHAR(g_account_date,'YYYY');
632
633
634 L_FISCAL_QUARTER := TO_CHAR(g_account_date,'Q');
635
636
637 L_FISCAL_MONTH := MOD((TO_NUMBER(TO_CHAR(g_account_date,'MM'))),3);
638 IF L_FISCAL_MONTH ='0'
639 THEN
640 L_FISCAL_MONTH := 3;
641 END IF;
642
643
644 IF g_LE_request ='REQUESTING_ORG'
645 THEN
646 /* THis is for Given LEGAL EMOPLYER */
647
648 IF g_LU_request ='LU_SELECTED'
649 THEN
650 /* THis is for Given LOCAL UNIT */
651
652
653
654 OPEN csr_CFAR_FROM_LU (g_local_unit_id);
655 FETCH csr_CFAR_FROM_LU INTO lr_CFAR_FROM_LU;
656 CLOSE csr_CFAR_FROM_LU;
657
658
659 L_CFAR_NUMBER :=lr_CFAR_FROM_LU.CFAR;
660
661 FOR REC_PERSON_TYPES_COUNT IN CSR_PERSON_TYPES_COUNT (g_local_unit_id)
662 LOOP
663 IF REC_PERSON_TYPES_COUNT.SEX ='M'
664 THEN
665 IF REC_PERSON_TYPES_COUNT.EMP_CAT ='FR'
666 THEN
667 L_REGULAR_MEN := REC_PERSON_TYPES_COUNT.TOTAL;
668
669 ELSIF REC_PERSON_TYPES_COUNT.EMP_CAT ='FT'
670 THEN
671 L_TEMPORARY_MEN := REC_PERSON_TYPES_COUNT.TOTAL;
672 END IF;
673
674
675 ELSIF REC_PERSON_TYPES_COUNT.SEX ='F'
676 THEN
677 IF REC_PERSON_TYPES_COUNT.EMP_CAT ='FR'
678 THEN
679 L_REGULAR_WOMEN := REC_PERSON_TYPES_COUNT.TOTAL;
680 ELSIF REC_PERSON_TYPES_COUNT.EMP_CAT ='FT'
681 THEN
682 L_TEMPORARY_WOMEN := REC_PERSON_TYPES_COUNT.TOTAL;
683 END IF;
684
685 END IF;
686
687 END LOOP;
688
689 /* getting start of the month and end of the month */
690 L_MONTH_START_DATE:= trunc(g_account_date,'MM');
691 L_MONTH_END_DATE:=last_day(trunc(g_account_date,'MM'));
692
693
694 /* getting the person_ids who have assignments within the period under the local unit*/
695 /* OPEN csr_person_local_unit(g_local_unit_id);
696 FETCH csr_person_local_unit INTO l_person_id,l_sex
697 CLOSE csr_person_local_unit; */
698
699 FOR csr_person IN csr_person_local_unit(g_local_unit_id) LOOP
700 l_person_id:=csr_person.person_id;
701 l_sex:=csr_person.sex;
702 /* Getting the count of the Sickness Absence */
703
704 OPEN csr_person_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE, 'S');
705 FETCH csr_person_absence INTO l_absence_count;
706 CLOSE csr_person_absence;
707 IF l_sex='M' THEN
708 L_SICK_LEAVE_MEN:=nvl(l_absence_count,0);
709 ELSIF l_sex='F' THEN
710 L_SICK_LEAVE_WOMEN:=nvl(l_absence_count,0);
711 END IF;
712
713 /* Getting the count of the Vacation Absence */
714
715 OPEN csr_person_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE, 'V');
716 FETCH csr_person_absence INTO l_absence_count;
717 CLOSE csr_person_absence;
718 IF l_sex='M' THEN
719 L_HOLIDAY_LEAVE_MEN:=nvl(l_absence_count,0);
720 ELSIF l_sex='F' THEN
721 L_HOLIDAY_LEAVE_WOMEN:=nvl(l_absence_count,0);
722 END IF;
723
724 /* Getting the count of the Other Absence */
725
726 OPEN csr_person_other_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE);
727 FETCH csr_person_other_absence INTO l_absence_count;
728 CLOSE csr_person_other_absence;
729 IF l_sex='M' THEN
730 L_OTHER_ABSENCE_MEN:=nvl(l_absence_count,0);
731 ELSIF l_sex='F' THEN
732 L_OTHER_ABSENCE_WOMEN:=nvl(l_absence_count,0);
733 END IF;
734
735 END LOOP;
736 /* need to check the absences which falls on the date of accounting */
737 /* Get the start date and the assignments that fall within the accounting month period */
738 FOR csr_assignment IN csr_new_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE) LOOP
739 l_assignment_id:=csr_assignment.assignment_id;
740 l_assignment_start_date:=csr_assignment.effective_start_date;
741 l_assignment_category:=csr_assignment.employment_category;
742 /*Get the sex of the person */
743 OPEN csr_person_sex(l_assignment_id);
744 FETCH csr_person_sex INTO l_sex;
745 CLOSE csr_person_sex;
746 /* Check for the previous assignment*/
747 OPEN csr_prev_assignments(l_assignment_id, l_assignment_start_date);
748 FETCH csr_prev_assignments INTO l_local_unit_id;
749 CLOSE csr_prev_assignments;
750 /* check for the sex */
751 IF l_sex='M' THEN
752 /* check for the category */
753 IF l_assignment_category='FR' THEN
754 IF l_local_unit_id IS NULL THEN
755 L_NEW_HIRES_REGULAR_MEN:=L_NEW_HIRES_REGULAR_MEN+1;
756 ELSE
757 IF l_local_unit_id<>g_local_unit_id THEN
758 L_NEW_HIRES_REGULAR_MEN:=L_NEW_HIRES_REGULAR_MEN+1;
759 END IF;
760 END if;
761 ELSIF l_assignment_category='FT' THEN
762 IF l_local_unit_id IS NULL THEN
763 L_NEW_HIRES_TEMPORARY_MEN:=L_NEW_HIRES_TEMPORARY_MEN+1;
764 ELSE
765 IF l_local_unit_id<>g_local_unit_id THEN
766 L_NEW_HIRES_TEMPORARY_MEN:=L_NEW_HIRES_TEMPORARY_MEN+1;
767 END IF;
768 END if;
769 END if;
770 ELSIF l_sex='F' THEN
771 /* If there is no previous record, then only one record, so increase the count by 1 */
772 /* check for the category */
773 IF l_assignment_category='FR' THEN
774 IF l_local_unit_id IS NULL THEN
775 L_NEW_HIRES_REGULAR_WOMEN:=L_NEW_HIRES_REGULAR_WOMEN+1;
776 ELSE
777 IF l_local_unit_id<>g_local_unit_id THEN
778 L_NEW_HIRES_REGULAR_WOMEN:=L_NEW_HIRES_REGULAR_WOMEN+1;
779 END IF;
780 END if;
781 ELSIF l_assignment_category='FT' THEN
782 IF l_local_unit_id IS NULL THEN
783 L_NEW_HIRES_TEMPORARY_WOMEN:=L_NEW_HIRES_TEMPORARY_WOMEN+1;
784 ELSE
785 IF l_local_unit_id<>g_local_unit_id THEN
786 L_NEW_HIRES_TEMPORARY_WOMEN:=L_NEW_HIRES_TEMPORARY_WOMEN+1;
787 END IF;
788 END IF;
789 END IF;
790 END IF;
791 l_local_unit_id:=NULL;
792 END LOOP;
793 /*cursor to get the end date, assignment id for getting the assignment ended employees */
794 FOR csr_assignment IN csr_end_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE ) LOOP
795 l_assignment_id:=csr_assignment.assignment_id;
796 l_assignment_end_date:=csr_assignment.effective_end_date;
797 l_assignment_category:=csr_assignment.employment_category;
798 /*Get the sex of the person */
799 OPEN csr_person_sex(l_assignment_id);
800 FETCH csr_person_sex INTO l_sex;
801 CLOSE csr_person_sex;
802 /* Check for the previous assignment*/
803 OPEN csr_next_assignments(l_assignment_id, l_assignment_end_date);
804 FETCH csr_next_assignments INTO l_local_unit_id;
805 CLOSE csr_next_assignments;
806 /* check for the sex */
807 IF l_sex='M' THEN
808 /* check for the category */
809 IF l_assignment_category='FR' THEN
810 IF l_local_unit_id IS NULL THEN
811 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
812 ELSE
813 IF l_local_unit_id<>g_local_unit_id THEN
814 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
815 END IF;
816 END if;
817 ELSIF l_assignment_category='FT' THEN
818 IF l_local_unit_id IS NULL THEN
819 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
820 ELSE
821 IF l_local_unit_id<>g_local_unit_id THEN
822 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
823 END IF;
824 END if;
825 END if;
826 ELSIF l_sex='F' THEN
827 /* If there is no previous record, then only one record, so increase the count by 1 */
828 /* check for the category */
829 IF l_assignment_category='FR' THEN
830 IF l_local_unit_id IS NULL THEN
831 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
832 ELSE
833 IF l_local_unit_id<>g_local_unit_id THEN
834 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
835 END IF;
836 END if;
837 ELSIF l_assignment_category='FT' THEN
838 IF l_local_unit_id IS NULL THEN
839 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
840 ELSE
841 IF l_local_unit_id<>g_local_unit_id THEN
842 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
843 END IF;
844 END IF;
845 END IF;
846 END IF;
847 l_local_unit_id:=NULL;
848 END LOOP;
849
850 /*cursor to get the Terminated, assignment */
851 FOR csr_assignment IN csr_ter_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE ) LOOP
852 l_assignment_id:=csr_assignment.assignment_id;
853 l_assignment_category:=csr_assignment.employment_category;
854 /*Get the sex of the person */
855 OPEN csr_person_sex(l_assignment_id);
856 FETCH csr_person_sex INTO l_sex;
857 CLOSE csr_person_sex;
858 /* check for the sex */
859 IF l_sex='M' THEN
860 /* check for the category */
861 IF l_assignment_category='FR' THEN
862 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
863 ELSIF l_assignment_category='FT' THEN
864
865 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
866 END if;
867 ELSIF l_sex='F' THEN
868 /* If there is no previous record, then only one record, so increase the count by 1 */
869 /* check for the category */
870 IF l_assignment_category='FR' THEN
871 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
872 ELSIF l_assignment_category='FT' THEN
873 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
874 END IF;
875 END IF;
876
877 END LOOP;
878
879 /*cursor to get the start date, assignment id for getting the new hires */
880
881
882
883 pay_action_information_api.create_action_information
884 (p_action_information_id => l_action_info_id
885 , p_action_context_id => p_payroll_action_id
886 , p_action_context_type => 'PA'
887 , p_object_version_number => l_ovn
888 , p_effective_date => g_effective_date
889 , p_source_id => NULL
890 , p_source_text => NULL
891 , p_action_information_category => 'EMEA REPORT INFORMATION'
892 , p_action_information1 => 'PYSESTEA'
893 , p_action_information2 => 'LU'
894 , p_action_information3 => L_LEGAL_EMPLOYER_NAME
895 , p_action_information4 => lr_CFAR_FROM_LU.LU_NAME
896 , p_action_information5 => L_CFAR_NUMBER
897 , p_action_information6 => L_FISCAL_YEAR
898 , p_action_information7 => L_FISCAL_QUARTER
899 , p_action_information8 => L_FISCAL_MONTH
900 , p_action_information9 => L_REGULAR_MEN
901 , p_action_information10 => L_REGULAR_WOMEN
902 , p_action_information11 => L_TEMPORARY_MEN
903 , p_action_information12 => L_TEMPORARY_WOMEN
904 , p_action_information13 => L_SICK_LEAVE_MEN
905 , p_action_information14 => L_SICK_LEAVE_WOMEN
906 , p_action_information15 => L_HOLIDAY_LEAVE_MEN
907 , p_action_information16 => L_HOLIDAY_LEAVE_WOMEN
908 , p_action_information17 => L_OTHER_ABSENCE_MEN
909 , p_action_information18 => L_OTHER_ABSENCE_WOMEN
910 , p_action_information19 => L_NEW_HIRES_REGULAR_MEN
911 , p_action_information20 => L_NEW_HIRES_REGULAR_WOMEN
912 , p_action_information21 => L_NEW_HIRES_TEMPORARY_MEN
913 , p_action_information22 => L_NEW_HIRES_TEMPORARY_WOMEN
914 , p_action_information23 => L_TERMINATION_REGULAR_MEN
915 , p_action_information24 => L_TERMINATION_REGULAR_WOMEN
916 , p_action_information25 => L_TERMINATION_TEMPORARY_MEN
917 , p_action_information26 => L_TERMINATION_TEMPORARY_WOMEN
918 );
919
920
921 ELSE
922 /* THis is for ALL LOCAL UNIT */
923
924 FOR rec_ALL_LU_FOR_LE IN csr_ALL_LU_FOR_LE (g_LEGAL_EMPLOYER_id)
925 LOOP
926 L_CFAR_NUMBER :=rec_ALL_LU_FOR_LE.CFAR;
927 g_local_unit_id:=rec_ALL_LU_FOR_LE.local_unit_id;
928 FOR REC_PERSON_TYPES_COUNT IN CSR_PERSON_TYPES_COUNT (rec_ALL_LU_FOR_LE.local_unit_id)
929 LOOP
930 IF REC_PERSON_TYPES_COUNT.SEX ='M'
931 THEN
932 IF REC_PERSON_TYPES_COUNT.EMP_CAT ='FR'
933 THEN
934 L_REGULAR_MEN := REC_PERSON_TYPES_COUNT.TOTAL;
935
936 ELSIF REC_PERSON_TYPES_COUNT.EMP_CAT ='FT'
937 THEN
938 L_TEMPORARY_MEN := REC_PERSON_TYPES_COUNT.TOTAL;
939 END IF;
940 ELSIF REC_PERSON_TYPES_COUNT.SEX ='F'
941 THEN
942 IF REC_PERSON_TYPES_COUNT.EMP_CAT ='FR'
943 THEN
944 L_REGULAR_WOMEN := REC_PERSON_TYPES_COUNT.TOTAL;
945 ELSIF REC_PERSON_TYPES_COUNT.EMP_CAT ='FT'
946 THEN
947 L_TEMPORARY_WOMEN := REC_PERSON_TYPES_COUNT.TOTAL;
948 END IF;
949 END IF;
950 END LOOP;
951
952 /* getting start of the month and end of the month */
953 L_MONTH_START_DATE:= trunc(g_account_date,'MM');
954 L_MONTH_END_DATE:=last_day(trunc(g_account_date,'MM'));
955
956
957 /* getting the person_ids who have assignments within the period under the local unit*/
958 /* OPEN csr_person_local_unit(g_local_unit_id);
959 FETCH csr_person_local_unit INTO l_person_id,l_sex
960 CLOSE csr_person_local_unit; */
961
962 FOR csr_person IN csr_person_local_unit(g_local_unit_id) LOOP
963 l_person_id:=csr_person.person_id;
964 l_sex:=csr_person.sex;
965 /* Getting the count of the Sickness Absence */
966
967 OPEN csr_person_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE, 'S');
968 FETCH csr_person_absence INTO l_absence_count;
969 CLOSE csr_person_absence;
970 IF l_sex='M' THEN
971 L_SICK_LEAVE_MEN:=nvl(l_absence_count,0);
972 ELSIF l_sex='F' THEN
973 L_SICK_LEAVE_WOMEN:=nvl(l_absence_count,0);
974 END IF;
975
976 /* Getting the count of the Vacation Absence */
977
978 OPEN csr_person_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE, 'V');
979 FETCH csr_person_absence INTO l_absence_count;
980 CLOSE csr_person_absence;
981 IF l_sex='M' THEN
982 L_HOLIDAY_LEAVE_MEN:=nvl(l_absence_count,0);
983 ELSIF l_sex='F' THEN
984 L_HOLIDAY_LEAVE_WOMEN:=nvl(l_absence_count,0);
985 END IF;
986
987 /* Getting the count of the Other Absence */
988
989 OPEN csr_person_other_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE);
990 FETCH csr_person_other_absence INTO l_absence_count;
991 CLOSE csr_person_other_absence;
992 IF l_sex='M' THEN
993 L_OTHER_ABSENCE_MEN:=nvl(l_absence_count,0);
994 ELSIF l_sex='F' THEN
995 L_OTHER_ABSENCE_WOMEN:=nvl(l_absence_count,0);
996 END IF;
997
998 END LOOP;
999 /* need to check the absences which falls on the date of accounting */
1000 /* Get the start date and the assignments that fall within the accounting month period */
1001 FOR csr_assignment IN csr_new_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE) LOOP
1002 l_assignment_id:=csr_assignment.assignment_id;
1003 l_assignment_start_date:=csr_assignment.effective_start_date;
1004 l_assignment_category:=csr_assignment.employment_category;
1005 /*Get the sex of the person */
1006 OPEN csr_person_sex(l_assignment_id);
1007 FETCH csr_person_sex INTO l_sex;
1008 CLOSE csr_person_sex;
1009 /* Check for the previous assignment*/
1010 OPEN csr_prev_assignments(l_assignment_id, l_assignment_start_date);
1011 FETCH csr_prev_assignments INTO l_local_unit_id;
1012 CLOSE csr_prev_assignments;
1013 /* check for the sex */
1014 IF l_sex='M' THEN
1015 /* check for the category */
1016 IF l_assignment_category='FR' THEN
1017 IF l_local_unit_id IS NULL THEN
1018 L_NEW_HIRES_REGULAR_MEN:=L_NEW_HIRES_REGULAR_MEN+1;
1019 ELSE
1020 IF l_local_unit_id<>g_local_unit_id THEN
1021 L_NEW_HIRES_REGULAR_MEN:=L_NEW_HIRES_REGULAR_MEN+1;
1022 END IF;
1023 END if;
1024 ELSIF l_assignment_category='FT' THEN
1025 IF l_local_unit_id IS NULL THEN
1026 L_NEW_HIRES_TEMPORARY_MEN:=L_NEW_HIRES_TEMPORARY_MEN+1;
1027 ELSE
1028 IF l_local_unit_id<>g_local_unit_id THEN
1029 L_NEW_HIRES_TEMPORARY_MEN:=L_NEW_HIRES_TEMPORARY_MEN+1;
1030 END IF;
1031 END if;
1032 END if;
1033 ELSIF l_sex='F' THEN
1034 /* If there is no previous record, then only one record, so increase the count by 1 */
1035 /* check for the category */
1036 IF l_assignment_category='FR' THEN
1037 IF l_local_unit_id IS NULL THEN
1038 L_NEW_HIRES_REGULAR_WOMEN:=L_NEW_HIRES_REGULAR_WOMEN+1;
1039 ELSE
1040 IF l_local_unit_id<>g_local_unit_id THEN
1041 L_NEW_HIRES_REGULAR_WOMEN:=L_NEW_HIRES_REGULAR_WOMEN+1;
1042 END IF;
1043 END if;
1044 ELSIF l_assignment_category='FT' THEN
1045 IF l_local_unit_id IS NULL THEN
1046 L_NEW_HIRES_TEMPORARY_WOMEN:=L_NEW_HIRES_TEMPORARY_WOMEN+1;
1047 ELSE
1048 IF l_local_unit_id<>g_local_unit_id THEN
1049 L_NEW_HIRES_TEMPORARY_WOMEN:=L_NEW_HIRES_TEMPORARY_WOMEN+1;
1050 END IF;
1051 END IF;
1052 END IF;
1053 END IF;
1054 l_local_unit_id:=NULL;
1055 END LOOP;
1056 /*cursor to get the end date, assignment id for getting the assignment ended employees */
1057 FOR csr_assignment IN csr_end_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE ) LOOP
1058 l_assignment_id:=csr_assignment.assignment_id;
1059 l_assignment_end_date:=csr_assignment.effective_end_date;
1060 l_assignment_category:=csr_assignment.employment_category;
1061 /*Get the sex of the person */
1062 OPEN csr_person_sex(l_assignment_id);
1063 FETCH csr_person_sex INTO l_sex;
1064 CLOSE csr_person_sex;
1065 /* Check for the previous assignment*/
1066 OPEN csr_next_assignments(l_assignment_id, l_assignment_end_date);
1067 FETCH csr_next_assignments INTO l_local_unit_id;
1068 CLOSE csr_next_assignments;
1069 /* check for the sex */
1070 IF l_sex='M' THEN
1071 /* check for the category */
1072 IF l_assignment_category='FR' THEN
1073 IF l_local_unit_id IS NULL THEN
1074 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
1075 ELSE
1076 IF l_local_unit_id<>g_local_unit_id THEN
1077 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
1078 END IF;
1079 END if;
1080 ELSIF l_assignment_category='FT' THEN
1081 IF l_local_unit_id IS NULL THEN
1082 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
1083 ELSE
1084 IF l_local_unit_id<>g_local_unit_id THEN
1085 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
1086 END IF;
1087 END if;
1088 END if;
1089 ELSIF l_sex='F' THEN
1090 /* If there is no previous record, then only one record, so increase the count by 1 */
1091 /* check for the category */
1092 IF l_assignment_category='FR' THEN
1093 IF l_local_unit_id IS NULL THEN
1094 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
1095 ELSE
1096 IF l_local_unit_id<>g_local_unit_id THEN
1097 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
1098 END IF;
1099 END if;
1100 ELSIF l_assignment_category='FT' THEN
1101 IF l_local_unit_id IS NULL THEN
1102 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
1103 ELSE
1104 IF l_local_unit_id<>g_local_unit_id THEN
1105 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
1106 END IF;
1107 END IF;
1108 END IF;
1109 END IF;
1110 l_local_unit_id:=NULL;
1111 END LOOP;
1112 /*cursor to get the Terminated, assignment */
1113 FOR csr_assignment IN csr_ter_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE ) LOOP
1114 l_assignment_id:=csr_assignment.assignment_id;
1115 -- l_assignment_end_date:=csr_assignment.effective_end_date;
1116 l_assignment_category:=csr_assignment.employment_category;
1117 /*Get the sex of the person */
1118 OPEN csr_person_sex(l_assignment_id);
1119 FETCH csr_person_sex INTO l_sex;
1120 CLOSE csr_person_sex;
1121 /* check for the sex */
1122 IF l_sex='M' THEN
1123 /* check for the category */
1124 IF l_assignment_category='FR' THEN
1125 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
1126 ELSIF l_assignment_category='FT' THEN
1127
1128 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
1129 END if;
1130 ELSIF l_sex='F' THEN
1131 /* If there is no previous record, then only one record, so increase the count by 1 */
1132 /* check for the category */
1133 IF l_assignment_category='FR' THEN
1134 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
1135 ELSIF l_assignment_category='FT' THEN
1136 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
1137 END IF;
1138 END IF;
1139
1140 END LOOP;
1141
1142 pay_action_information_api.create_action_information
1143 (p_action_information_id => l_action_info_id
1144 , p_action_context_id => p_payroll_action_id
1145 , p_action_context_type => 'PA'
1146 , p_object_version_number => l_ovn
1147 , p_effective_date => g_effective_date
1148 , p_source_id => NULL
1149 , p_source_text => NULL
1150 , p_action_information_category => 'EMEA REPORT INFORMATION'
1151 , p_action_information1 => 'PYSESTEA'
1152 , p_action_information2 => 'LU'
1153 , p_action_information3 => L_LEGAL_EMPLOYER_NAME
1154 , p_action_information4 => rec_ALL_LU_FOR_LE.local_unit_name
1155 , p_action_information5 => L_CFAR_NUMBER
1156 , p_action_information6 => L_FISCAL_YEAR
1157 , p_action_information7 => L_FISCAL_QUARTER
1158 , p_action_information8 => L_FISCAL_MONTH
1159 , p_action_information9 => L_REGULAR_MEN
1160 , p_action_information10 => L_REGULAR_WOMEN
1161 , p_action_information11 => L_TEMPORARY_MEN
1162 , p_action_information12 => L_TEMPORARY_WOMEN
1163 , p_action_information13 => L_SICK_LEAVE_MEN
1164 , p_action_information14 => L_SICK_LEAVE_WOMEN
1165 , p_action_information15 => L_HOLIDAY_LEAVE_MEN
1166 , p_action_information16 => L_HOLIDAY_LEAVE_WOMEN
1167 , p_action_information17 => L_OTHER_ABSENCE_MEN
1168 , p_action_information18 => L_OTHER_ABSENCE_WOMEN
1169 , p_action_information19 => L_NEW_HIRES_REGULAR_MEN
1170 , p_action_information20 => L_NEW_HIRES_REGULAR_WOMEN
1171 , p_action_information21 => L_NEW_HIRES_TEMPORARY_MEN
1172 , p_action_information22 => L_NEW_HIRES_TEMPORARY_WOMEN
1173 , p_action_information23 => L_TERMINATION_REGULAR_MEN
1174 , p_action_information24 => L_TERMINATION_REGULAR_WOMEN
1175 , p_action_information25 => L_TERMINATION_TEMPORARY_MEN
1176 , p_action_information26 => L_TERMINATION_TEMPORARY_WOMEN
1177 );
1178 L_REGULAR_MEN:=0;
1179 L_REGULAR_WOMEN:=0;
1180 L_TEMPORARY_MEN:=0;
1181 L_TEMPORARY_WOMEN:=0;
1182 L_SICK_LEAVE_MEN:=0;
1183 L_SICK_LEAVE_WOMEN:=0;
1184 L_HOLIDAY_LEAVE_MEN:=0;
1185 L_HOLIDAY_LEAVE_WOMEN:=0;
1186 L_OTHER_ABSENCE_MEN:=0;
1187 L_OTHER_ABSENCE_WOMEN:=0;
1188 L_NEW_HIRES_REGULAR_MEN:=0;
1189 L_NEW_HIRES_REGULAR_WOMEN:=0;
1190 L_NEW_HIRES_TEMPORARY_MEN:=0;
1191 L_NEW_HIRES_TEMPORARY_WOMEN:=0;
1192 L_TERMINATION_REGULAR_MEN:=0;
1193 L_TERMINATION_REGULAR_WOMEN:=0;
1194 L_TERMINATION_TEMPORARY_MEN:=0;
1195 L_TERMINATION_TEMPORARY_WOMEN:=0;
1196 g_local_unit_id:=NULL;
1197 END LOOP;
1198
1199
1200 END IF;
1201
1202
1203 ELSE
1204 /* THis is for ALL LEGAL EMOPLYER */
1205
1206
1207
1208 FOR REC_ALL_LE_FOR_BG IN csr_ALL_LE_FOR_BG ()
1209 LOOP
1210 L_LEGAL_EMPLOYER_NAME :=REC_ALL_LE_FOR_BG.LEGAL_EMPLOYER_NAME;
1211
1212 FOR rec_ALL_LU_FOR_LE IN csr_ALL_LU_FOR_LE (REC_ALL_LE_FOR_BG.ORGANIZATION_ID)
1213 LOOP
1214 L_CFAR_NUMBER :=rec_ALL_LU_FOR_LE.CFAR;
1215 g_local_unit_id:=rec_ALL_LU_FOR_LE.local_unit_id;
1216 FOR REC_PERSON_TYPES_COUNT IN CSR_PERSON_TYPES_COUNT (rec_ALL_LU_FOR_LE.local_unit_id)
1217 LOOP
1218 IF REC_PERSON_TYPES_COUNT.SEX ='M'
1219 THEN
1220 IF REC_PERSON_TYPES_COUNT.EMP_CAT ='FR'
1221 THEN
1222 L_REGULAR_MEN := REC_PERSON_TYPES_COUNT.TOTAL;
1223
1224 ELSIF REC_PERSON_TYPES_COUNT.EMP_CAT ='FT'
1225 THEN
1226 L_TEMPORARY_MEN := REC_PERSON_TYPES_COUNT.TOTAL;
1227 END IF;
1228 ELSIF REC_PERSON_TYPES_COUNT.SEX ='F'
1229 THEN
1230 IF REC_PERSON_TYPES_COUNT.EMP_CAT ='FR'
1231 THEN
1232 L_REGULAR_WOMEN := REC_PERSON_TYPES_COUNT.TOTAL;
1233 ELSIF REC_PERSON_TYPES_COUNT.EMP_CAT ='FT'
1234 THEN
1235 L_TEMPORARY_WOMEN := REC_PERSON_TYPES_COUNT.TOTAL;
1236 END IF;
1237 END IF;
1238 END LOOP;
1239
1240 /* getting start of the month and end of the month */
1241 L_MONTH_START_DATE:= trunc(g_account_date,'MM');
1242 L_MONTH_END_DATE:=last_day(trunc(g_account_date,'MM'));
1243
1244
1245 /* getting the person_ids who have assignments within the period under the local unit*/
1246 /* OPEN csr_person_local_unit(g_local_unit_id);
1247 FETCH csr_person_local_unit INTO l_person_id,l_sex
1248 CLOSE csr_person_local_unit; */
1249
1250 FOR csr_person IN csr_person_local_unit(g_local_unit_id) LOOP
1251 l_person_id:=csr_person.person_id;
1252 l_sex:=csr_person.sex;
1253 /* Getting the count of the Sickness Absence */
1254
1255 OPEN csr_person_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE, 'S');
1256 FETCH csr_person_absence INTO l_absence_count;
1257 CLOSE csr_person_absence;
1258 IF l_sex='M' THEN
1259 L_SICK_LEAVE_MEN:=nvl(l_absence_count,0);
1260 ELSIF l_sex='F' THEN
1261 L_SICK_LEAVE_WOMEN:=nvl(l_absence_count,0);
1262 END IF;
1263
1264 /* Getting the count of the Vacation Absence */
1265
1266 OPEN csr_person_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE, 'V');
1267 FETCH csr_person_absence INTO l_absence_count;
1268 CLOSE csr_person_absence;
1269 IF l_sex='M' THEN
1270 L_HOLIDAY_LEAVE_MEN:=nvl(l_absence_count,0);
1271 ELSIF l_sex='F' THEN
1272 L_HOLIDAY_LEAVE_WOMEN:=nvl(l_absence_count,0);
1273 END IF;
1274
1275 /* Getting the count of the Other Absence */
1276
1277 OPEN csr_person_other_absence(l_person_id, L_MONTH_START_DATE, L_MONTH_END_DATE);
1278 FETCH csr_person_other_absence INTO l_absence_count;
1279 CLOSE csr_person_other_absence;
1280 IF l_sex='M' THEN
1281 L_OTHER_ABSENCE_MEN:=nvl(l_absence_count,0);
1282 ELSIF l_sex='F' THEN
1283 L_OTHER_ABSENCE_WOMEN:=nvl(l_absence_count,0);
1284 END IF;
1285
1286 END LOOP;
1287 /* need to check the absences which falls on the date of accounting */
1288 /* Get the start date and the assignments that fall within the accounting month period */
1289 FOR csr_assignment IN csr_new_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE) LOOP
1290 l_assignment_id:=csr_assignment.assignment_id;
1291 l_assignment_start_date:=csr_assignment.effective_start_date;
1292 l_assignment_category:=csr_assignment.employment_category;
1293 /*Get the sex of the person */
1294 OPEN csr_person_sex(l_assignment_id);
1295 FETCH csr_person_sex INTO l_sex;
1296 CLOSE csr_person_sex;
1297 /* Check for the previous assignment*/
1298 OPEN csr_prev_assignments(l_assignment_id, l_assignment_start_date);
1299 FETCH csr_prev_assignments INTO l_local_unit_id;
1300 CLOSE csr_prev_assignments;
1301 /* check for the sex */
1302 IF l_sex='M' THEN
1303 /* check for the category */
1304 IF l_assignment_category='FR' THEN
1305 IF l_local_unit_id IS NULL THEN
1306 L_NEW_HIRES_REGULAR_MEN:=L_NEW_HIRES_REGULAR_MEN+1;
1307 ELSE
1308 IF l_local_unit_id<>g_local_unit_id THEN
1309 L_NEW_HIRES_REGULAR_MEN:=L_NEW_HIRES_REGULAR_MEN+1;
1310 END IF;
1311 END if;
1312 ELSIF l_assignment_category='FT' THEN
1313 IF l_local_unit_id IS NULL THEN
1314 L_NEW_HIRES_TEMPORARY_MEN:=L_NEW_HIRES_TEMPORARY_MEN+1;
1315 ELSE
1316 IF l_local_unit_id<>g_local_unit_id THEN
1317 L_NEW_HIRES_TEMPORARY_MEN:=L_NEW_HIRES_TEMPORARY_MEN+1;
1318 END IF;
1319 END if;
1320 END if;
1321 ELSIF l_sex='F' THEN
1322 /* If there is no previous record, then only one record, so increase the count by 1 */
1323 /* check for the category */
1324 IF l_assignment_category='FR' THEN
1325 IF l_local_unit_id IS NULL THEN
1326 L_NEW_HIRES_REGULAR_WOMEN:=L_NEW_HIRES_REGULAR_WOMEN+1;
1327 ELSE
1328 IF l_local_unit_id<>g_local_unit_id THEN
1329 L_NEW_HIRES_REGULAR_WOMEN:=L_NEW_HIRES_REGULAR_WOMEN+1;
1330 END IF;
1331 END if;
1332 ELSIF l_assignment_category='FT' THEN
1333 IF l_local_unit_id IS NULL THEN
1334 L_NEW_HIRES_TEMPORARY_WOMEN:=L_NEW_HIRES_TEMPORARY_WOMEN+1;
1335 ELSE
1336 IF l_local_unit_id<>g_local_unit_id THEN
1337 L_NEW_HIRES_TEMPORARY_WOMEN:=L_NEW_HIRES_TEMPORARY_WOMEN+1;
1338 END IF;
1339 END IF;
1340 END IF;
1341 END IF;
1342 l_local_unit_id:=NULL;
1343 END LOOP;
1344 /*cursor to get the end date, assignment id for getting the assignment ended employees */
1345 FOR csr_assignment IN csr_end_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE ) LOOP
1346 l_assignment_id:=csr_assignment.assignment_id;
1347 l_assignment_end_date:=csr_assignment.effective_end_date;
1348 l_assignment_category:=csr_assignment.employment_category;
1349 /*Get the sex of the person */
1350 OPEN csr_person_sex(l_assignment_id);
1351 FETCH csr_person_sex INTO l_sex;
1352 CLOSE csr_person_sex;
1353 /* Check for the previous assignment*/
1354 OPEN csr_next_assignments(l_assignment_id, l_assignment_end_date);
1355 FETCH csr_next_assignments INTO l_local_unit_id;
1356 CLOSE csr_next_assignments;
1357 /* check for the sex */
1358 IF l_sex='M' THEN
1359 /* check for the category */
1360 IF l_assignment_category='FR' THEN
1361 IF l_local_unit_id IS NULL THEN
1362 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
1363 ELSE
1364 IF l_local_unit_id<>g_local_unit_id THEN
1365 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
1366 END IF;
1367 END if;
1368 ELSIF l_assignment_category='FT' THEN
1369 IF l_local_unit_id IS NULL THEN
1370 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
1371 ELSE
1372 IF l_local_unit_id<>g_local_unit_id THEN
1373 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
1374 END IF;
1375 END if;
1376 END if;
1377 ELSIF l_sex='F' THEN
1378 /* If there is no previous record, then only one record, so increase the count by 1 */
1379 /* check for the category */
1380 IF l_assignment_category='FR' THEN
1381 IF l_local_unit_id IS NULL THEN
1382 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
1383 ELSE
1384 IF l_local_unit_id<>g_local_unit_id THEN
1385 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
1386 END IF;
1387 END if;
1388 ELSIF l_assignment_category='FT' THEN
1389 IF l_local_unit_id IS NULL THEN
1390 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
1391 ELSE
1392 IF l_local_unit_id<>g_local_unit_id THEN
1393 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
1394 END IF;
1395 END IF;
1396 END IF;
1397 END IF;
1398 l_local_unit_id:=NULL;
1399 END LOOP;
1400
1401 /*cursor to get the Terminated, assignment */
1402 FOR csr_assignment IN csr_ter_assignments(L_MONTH_START_DATE, L_MONTH_END_DATE ) LOOP
1403 l_assignment_id:=csr_assignment.assignment_id;
1404 -- l_assignment_end_date:=csr_assignment.effective_end_date;
1405 l_assignment_category:=csr_assignment.employment_category;
1406 /*Get the sex of the person */
1407 OPEN csr_person_sex(l_assignment_id);
1408 FETCH csr_person_sex INTO l_sex;
1409 CLOSE csr_person_sex;
1410 /* check for the sex */
1411 IF l_sex='M' THEN
1412 /* check for the category */
1413 IF l_assignment_category='FR' THEN
1414 L_TERMINATION_REGULAR_MEN:=L_TERMINATION_REGULAR_MEN+1;
1415 ELSIF l_assignment_category='FT' THEN
1416
1417 L_TERMINATION_TEMPORARY_MEN:=L_TERMINATION_TEMPORARY_MEN+1;
1418 END if;
1419 ELSIF l_sex='F' THEN
1420 /* If there is no previous record, then only one record, so increase the count by 1 */
1421 /* check for the category */
1422 IF l_assignment_category='FR' THEN
1423 L_TERMINATION_REGULAR_WOMEN:=L_TERMINATION_REGULAR_WOMEN+1;
1424 ELSIF l_assignment_category='FT' THEN
1425 L_TERMINATION_TEMPORARY_WOMEN:=L_TERMINATION_TEMPORARY_WOMEN+1;
1426 END IF;
1427 END IF;
1428
1429 END LOOP;
1430
1431 pay_action_information_api.create_action_information
1432 (p_action_information_id => l_action_info_id
1433 , p_action_context_id => p_payroll_action_id
1434 , p_action_context_type => 'PA'
1435 , p_object_version_number => l_ovn
1436 , p_effective_date => g_effective_date
1437 , p_source_id => NULL
1438 , p_source_text => NULL
1439 , p_action_information_category => 'EMEA REPORT INFORMATION'
1440 , p_action_information1 => 'PYSESTEA'
1441 , p_action_information2 => 'LU'
1442 , p_action_information3 => L_LEGAL_EMPLOYER_NAME
1443 , p_action_information4 => rec_ALL_LU_FOR_LE.local_unit_name
1444 , p_action_information5 => L_CFAR_NUMBER
1445 , p_action_information6 => L_FISCAL_YEAR
1446 , p_action_information7 => L_FISCAL_QUARTER
1447 , p_action_information8 => L_FISCAL_MONTH
1448 , p_action_information9 => L_REGULAR_MEN
1449 , p_action_information10 => L_REGULAR_WOMEN
1450 , p_action_information11 => L_TEMPORARY_MEN
1451 , p_action_information12 => L_TEMPORARY_WOMEN
1452 , p_action_information13 => L_SICK_LEAVE_MEN
1453 , p_action_information14 => L_SICK_LEAVE_WOMEN
1454 , p_action_information15 => L_HOLIDAY_LEAVE_MEN
1455 , p_action_information16 => L_HOLIDAY_LEAVE_WOMEN
1456 , p_action_information17 => L_OTHER_ABSENCE_MEN
1457 , p_action_information18 => L_OTHER_ABSENCE_WOMEN
1458 , p_action_information19 => L_NEW_HIRES_REGULAR_MEN
1459 , p_action_information20 => L_NEW_HIRES_REGULAR_WOMEN
1460 , p_action_information21 => L_NEW_HIRES_TEMPORARY_MEN
1461 , p_action_information22 => L_NEW_HIRES_TEMPORARY_WOMEN
1462 , p_action_information23 => L_TERMINATION_REGULAR_MEN
1463 , p_action_information24 => L_TERMINATION_REGULAR_WOMEN
1464 , p_action_information25 => L_TERMINATION_TEMPORARY_MEN
1465 , p_action_information26 => L_TERMINATION_TEMPORARY_WOMEN
1466 );
1467 L_REGULAR_MEN:=0;
1468 L_REGULAR_WOMEN:=0;
1469 L_TEMPORARY_MEN:=0;
1470 L_TEMPORARY_WOMEN:=0;
1471 L_SICK_LEAVE_MEN:=0;
1472 L_SICK_LEAVE_WOMEN:=0;
1473 L_HOLIDAY_LEAVE_MEN:=0;
1474 L_HOLIDAY_LEAVE_WOMEN:=0;
1475 L_OTHER_ABSENCE_MEN:=0;
1476 L_OTHER_ABSENCE_WOMEN:=0;
1477 L_NEW_HIRES_REGULAR_MEN:=0;
1478 L_NEW_HIRES_REGULAR_WOMEN:=0;
1479 L_NEW_HIRES_TEMPORARY_MEN:=0;
1480 L_NEW_HIRES_TEMPORARY_WOMEN:=0;
1481 L_TERMINATION_REGULAR_MEN:=0;
1482 L_TERMINATION_REGULAR_WOMEN:=0;
1483 L_TERMINATION_TEMPORARY_MEN:=0;
1484 L_TERMINATION_TEMPORARY_WOMEN:=0;
1485
1486 END LOOP;
1487 g_local_unit_id:=NULL;
1488 END LOOP;
1489
1490
1491 END IF;
1492 /*
1493 -- *****************************************************************************
1494 -- TO pick up the Name of the LE
1495 OPEN csr_legal_employer_details (g_legal_employer_id);
1496
1497 FETCH csr_legal_employer_details
1498 INTO l_legal_employer_details;
1499
1500 CLOSE csr_legal_employer_details;
1501
1502 -- *****************************************************************************
1503 fnd_file.put_line (fnd_file.LOG
1504 , 'After CURSOR Legal Emp DETAILS ==> '
1505 || g_legal_employer_id
1506 );
1507 -- *****************************************************************************
1508 -- Insert the report Parameters
1509 pay_action_information_api.create_action_information
1510 (p_action_information_id => l_action_info_id
1511 , p_action_context_id => p_payroll_action_id
1512 , p_action_context_type => 'PA'
1513 , p_object_version_number => l_ovn
1514 , p_effective_date => g_effective_date
1515 , p_source_id => NULL
1516 , p_source_text => NULL
1517 , p_action_information_category => 'EMEA REPORT DETAILS'
1518 , p_action_information1 => 'PYSEHPDA'
1519 , p_action_information2 => l_legal_employer_details.legal_employer_name
1520 , p_action_information3 => g_legal_employer_id
1521 , p_action_information4 => g_LE_request
1522 , p_action_information5 => fnd_date.date_to_canonical
1523 (g_posting_date)
1524 , p_action_information6 => fnd_date.date_to_canonical
1525 (g_account_date)
1526 , p_action_information7 => NULL
1527 , p_action_information8 => NULL
1528 , p_action_information9 => NULL
1529 , p_action_information10 => NULL
1530 );
1531 -- *****************************************************************************
1532 fnd_file.put_line (fnd_file.LOG
1533 , ' ================ ALL ================ '
1534 );
1535 --fnd_file.put_line(fnd_file.log,'PENSION provider name ==> '||lr_pension_provider_details.NAME );
1536 --fnd_file.put_line(fnd_file.log,'PENSION provider ID ==> '||g_pension_provider_id);
1537 fnd_file.put_line (fnd_file.LOG
1538 , 'Legal Emp Name ==> '
1539 || l_legal_employer_details.legal_employer_name
1540 );
1541 fnd_file.put_line (fnd_file.LOG
1542 , 'Legal Emp ID ==> ' || g_legal_employer_id
1543 );
1544 fnd_file.put_line (fnd_file.LOG
1545 , 'g_request_for ==> ' || g_LE_request
1546 );
1547 --fnd_file.put_line(fnd_file.log,'Local Unit ID ==> '||g_local_unit_id);
1548 --fnd_file.put_line(fnd_file.log,'acti_info_id ==> '||l_action_info_id );
1549 fnd_file.put_line (fnd_file.LOG, ' ================================ ');
1550
1551 -- *****************************************************************************
1552 IF g_LE_request = 'REQUESTING_ORG'
1553 THEN
1554 -- Information regarding the Legal Employer
1555 OPEN csr_legal_employer_details (g_legal_employer_id);
1556
1557 FETCH csr_legal_employer_details
1558 INTO l_legal_employer_details;
1559
1560 CLOSE csr_legal_employer_details;
1561
1562 pay_action_information_api.create_action_information
1563 (p_action_information_id => l_action_info_id
1564 , p_action_context_id => p_payroll_action_id
1565 , p_action_context_type => 'PA'
1566 , p_object_version_number => l_ovn
1567 , p_effective_date => g_effective_date
1568 , p_source_id => NULL
1569 , p_source_text => NULL
1570 , p_action_information_category => 'EMEA REPORT INFORMATION'
1571 , p_action_information1 => 'PYSEHPDA'
1572 , p_action_information2 => 'LE'
1573 , p_action_information3 => g_legal_employer_id
1574 , p_action_information4 => l_legal_employer_details.legal_employer_name
1575 , p_action_information5 => l_legal_employer_details.org_number
1576 , p_action_information6 => NULL
1577 , p_action_information7 => NULL
1578 , p_action_information8 => NULL
1579 , p_action_information9 => NULL
1580 , p_action_information10 => NULL
1581 );
1582 -- *****************************************************************************
1583 ELSE
1584 -- *****************************************************************************
1585 FOR rec_legal_employer_details IN csr_legal_employer_details (NULL)
1586 LOOP
1587 OPEN csr_check_empty_le (rec_legal_employer_details.legal_id
1588 , g_start_date
1589 , g_end_date
1590 );
1591
1592 FETCH csr_check_empty_le
1593 INTO l_le_has_employee;
1594
1595 CLOSE csr_check_empty_le;
1596
1597 IF l_le_has_employee = '1'
1598 THEN
1599 pay_action_information_api.create_action_information
1600 (p_action_information_id => l_action_info_id
1601 , p_action_context_id => p_payroll_action_id
1602 , p_action_context_type => 'PA'
1603 , p_object_version_number => l_ovn
1604 , p_effective_date => g_effective_date
1605 , p_source_id => NULL
1606 , p_source_text => NULL
1607 , p_action_information_category => 'EMEA REPORT INFORMATION'
1608 , p_action_information1 => 'PYSEHPDA'
1609 , p_action_information2 => 'LE'
1610 , p_action_information3 => rec_legal_employer_details.legal_id
1611 , p_action_information4 => rec_legal_employer_details.legal_employer_name
1612 , p_action_information5 => rec_legal_employer_details.org_number
1613 , p_action_information6 => NULL
1614 , p_action_information7 => NULL
1615 , p_action_information8 => NULL
1616 , p_action_information9 => NULL
1617 , p_action_information10 => NULL
1618 );
1619 END IF;
1620 END LOOP;
1621 END IF; -- FOR G_LEGAL_EMPLOYER
1622
1623 */
1624 IF g_debug
1625 THEN
1626 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
1627 END IF;
1628 EXCEPTION
1629 WHEN OTHERS
1630 THEN
1631 -- Return cursor that selects no rows
1632 p_sql :=
1633 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1634 END range_code;
1635
1636 /* ASSIGNMENT ACTION CODE */
1637 PROCEDURE assignment_action_code (
1638 p_payroll_action_id IN NUMBER
1639 , p_start_person IN NUMBER
1640 , p_end_person IN NUMBER
1641 , p_chunk IN NUMBER
1642 )
1643 IS
1644 BEGIN
1645 IF g_debug
1646 THEN
1647 hr_utility.set_location
1648 (' Entering Procedure ASSIGNMENT_ACTION_CODE'
1649 , 60
1650 );
1651 END IF;
1652
1653
1654
1655 IF g_debug
1656 THEN
1657 hr_utility.set_location
1658 (' Leaving Procedure ASSIGNMENT_ACTION_CODE'
1659 , 70
1660 );
1661 END IF;
1662 EXCEPTION
1663 WHEN OTHERS
1664 THEN
1665 IF g_debug
1666 THEN
1667 hr_utility.set_location ('error raised assignment_action_code '
1668 , 5
1669 );
1670 END IF;
1671
1672 RAISE;
1673 END assignment_action_code;
1674
1675 /*fffffffffffffffffffffffffff*/
1676
1677 /* INITIALIZATION CODE */
1678 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
1679 IS
1680 l_action_info_id NUMBER;
1681 l_ovn NUMBER;
1682 l_count NUMBER := 0;
1683 l_business_group_id NUMBER;
1684 l_start_date VARCHAR2 (20);
1685 l_end_date VARCHAR2 (20);
1686 l_effective_date DATE;
1687 l_payroll_id NUMBER;
1688 l_consolidation_set NUMBER;
1689 l_prev_prepay NUMBER := 0;
1690 BEGIN
1691 IF g_debug
1692 THEN
1693 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE'
1694 , 80
1695 );
1696 END IF;
1697
1698
1699 g_payroll_action_id := p_payroll_action_id;
1700 g_business_group_id := NULL;
1701 g_effective_date := NULL;
1702 g_LE_request := NULL;
1703 g_LU_request :=null;
1704 g_legal_employer_id := NULL;
1705 g_local_unit_id := NULL;
1706 g_account_date :=null;
1707 g_posting_date :=null;
1708 PAY_SE_EMPLOYMENT_STATISTICS.get_all_parameters (p_payroll_action_id
1709 , g_business_group_id
1710 , g_effective_date
1711 , g_legal_employer_id
1712 , g_LE_request
1713 , g_LU_request
1714 , g_local_unit_id
1715 , g_account_date
1716 , g_posting_date
1717 , g_reporting_date
1718 );
1719
1720 IF g_debug
1721 THEN
1722 hr_utility.set_location (' Leaving Procedure INITIALIZATION_CODE'
1723 , 90
1724 );
1725 END IF;
1726 EXCEPTION
1727 WHEN OTHERS
1728 THEN
1729 g_err_num := SQLCODE;
1730
1731 IF g_debug
1732 THEN
1733 hr_utility.set_location ( 'ORA_ERR: '
1734 || g_err_num
1735 || 'In INITIALIZATION_CODE'
1736 , 180
1737 );
1738 END IF;
1739 END initialization_code;
1740
1741 /* GET DEFINED BALANCE ID */
1742 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
1743 RETURN NUMBER
1744 IS
1745 /* Cursor to retrieve Defined Balance Id */
1746 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1747 IS
1748 SELECT u.creator_id
1749 FROM ff_user_entities u, ff_database_items d
1750 WHERE d.user_name = p_user_name
1751 AND u.user_entity_id = d.user_entity_id
1752 AND (u.legislation_code = 'SE')
1753 AND (u.business_group_id IS NULL)
1754 AND u.creator_type = 'B';
1755
1756 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1757 BEGIN
1758 IF g_debug
1759 THEN
1760 hr_utility.set_location
1761 (' Entering Function GET_DEFINED_BALANCE_ID'
1762 , 240
1763 );
1764 END IF;
1765
1766 OPEN csr_def_bal_id (p_user_name);
1767
1768 FETCH csr_def_bal_id
1769 INTO l_defined_balance_id;
1770
1771 CLOSE csr_def_bal_id;
1772
1773 RETURN l_defined_balance_id;
1774
1775 IF g_debug
1776 THEN
1777 hr_utility.set_location (' Leaving Function GET_DEFINED_BALANCE_ID'
1778 , 250
1779 );
1780 END IF;
1781 END get_defined_balance_id;
1782
1783 FUNCTION get_defined_balance_value (
1784 p_user_name IN VARCHAR2
1785 , p_in_assignment_id IN NUMBER
1786 , p_in_virtual_date IN DATE
1787 )
1788 RETURN NUMBER
1789 IS
1790 /* Cursor to retrieve Defined Balance Id */
1791 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1792 IS
1793 SELECT u.creator_id
1794 FROM ff_user_entities u, ff_database_items d
1795 WHERE d.user_name = p_user_name
1796 AND u.user_entity_id = d.user_entity_id
1797 AND (u.legislation_code = 'SE')
1798 AND (u.business_group_id IS NULL)
1799 AND u.creator_type = 'B';
1800
1801 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1802 l_return_balance_value NUMBER;
1803 BEGIN
1804 IF g_debug
1805 THEN
1806 hr_utility.set_location
1807 (' Entering Function GET_DEFINED_BALANCE_VALUE'
1808 , 240
1809 );
1810 END IF;
1811
1812 OPEN csr_def_bal_id (p_user_name);
1813
1814 FETCH csr_def_bal_id
1815 INTO l_defined_balance_id;
1816
1817 CLOSE csr_def_bal_id;
1818
1819 l_return_balance_value :=
1820 TO_CHAR
1821 (pay_balance_pkg.get_value
1822 (p_defined_balance_id => l_defined_balance_id
1823 , p_assignment_id => p_in_assignment_id
1824 , p_virtual_date => p_in_virtual_date
1825 )
1826 , '999999999D99'
1827 );
1828 RETURN l_return_balance_value;
1829
1830 IF g_debug
1831 THEN
1832 hr_utility.set_location
1833 (' Leaving Function GET_DEFINED_BALANCE_VALUE'
1834 , 250
1835 );
1836 END IF;
1837 END get_defined_balance_value;
1838
1839 /* ARCHIVE CODE */
1840 PROCEDURE archive_code (
1841 p_assignment_action_id IN NUMBER
1842 , p_effective_date IN DATE
1843 )
1844 IS
1845 BEGIN
1846 IF g_debug
1847 THEN
1848 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
1849 END IF;
1850
1851
1852
1853 IF g_debug
1854 THEN
1855 hr_utility.set_location (' Leaving Procedure ARCHIVE_CODE', 390);
1856 END IF;
1857 END archive_code;
1858
1859 --- Report XML generating code
1860 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
1861 IS
1862 l_xfdf_string CLOB;
1863 l_str1 VARCHAR2 (1000);
1864 l_str2 VARCHAR2 (20);
1865 l_str3 VARCHAR2 (20);
1866 l_str4 VARCHAR2 (20);
1867 l_str5 VARCHAR2 (20);
1868 l_str6 VARCHAR2 (30);
1869 l_str7 VARCHAR2 (1000);
1870 l_str8 VARCHAR2 (240);
1871 l_str9 VARCHAR2 (240);
1872 l_str10 VARCHAR2 (20);
1873 l_str11 VARCHAR2 (20);
1874 current_index PLS_INTEGER;
1875 l_iana_charset VARCHAR2 (50);
1876 BEGIN
1877 l_iana_charset := hr_se_utility.get_iana_charset;
1878
1879 -- hr_utility.set_location ('Entering WritetoCLOB ', 70);
1880 l_str1 :=
1881 '<?xml version="1.0" encoding="'
1882 || l_iana_charset
1883 || '"?> <ROOT><HPDR>';
1884 l_str2 := '<';
1885 l_str3 := '>';
1886 l_str4 := '</';
1887 l_str5 := '>';
1888 l_str6 := '</HPDR></ROOT>';
1889 l_str7 :=
1890 '<?xml version="1.0" encoding="'
1891 || l_iana_charset
1892 || '"?> <ROOT></ROOT>';
1893 l_str10 := '<HPDR>';
1894 l_str11 := '</HPDR>';
1895 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
1896 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
1897 current_index := 0;
1898
1899 IF xml_tab.COUNT > 0
1900 THEN
1901 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
1902
1903 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST
1904 LOOP
1905 l_str8 := xml_tab (table_counter).tagname;
1906 l_str9 := xml_tab (table_counter).tagvalue;
1907
1908 IF l_str9 IN
1909 (
1910 'LU_DETAILS',
1911 'END_LU_DETAILS'
1912 )
1913 THEN
1914 IF l_str9 IN
1915 ('LU_DETAILS')
1916 THEN
1917 DBMS_LOB.writeappend (l_xfdf_string
1918 , LENGTH (l_str2)
1919 , l_str2
1920 );
1921 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1922 , l_str8);
1923 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1924 , l_str3);
1925 ELSE
1926 DBMS_LOB.writeappend (l_xfdf_string
1927 , LENGTH (l_str4)
1928 , l_str4
1929 );
1930 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1931 , l_str8);
1932 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1933 , l_str5);
1934 END IF;
1935 ELSE
1936 IF l_str9 IS NOT NULL
1937 THEN
1938 DBMS_LOB.writeappend (l_xfdf_string
1939 , LENGTH (l_str2)
1940 , l_str2
1941 );
1942 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1943 , l_str8);
1944 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1945 , l_str3);
1946 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9)
1947 , l_str9);
1948 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1949 , l_str4);
1950 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1951 , l_str8);
1952 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1953 , l_str5);
1954 ELSE
1955 DBMS_LOB.writeappend (l_xfdf_string
1956 , LENGTH (l_str2)
1957 , l_str2
1958 );
1959 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1960 , l_str8);
1961 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1962 , l_str3);
1963 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1964 , l_str4);
1965 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1966 , l_str8);
1967 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1968 , l_str5);
1969 END IF;
1970 END IF;
1971
1972 END LOOP;
1973
1974 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
1975 ELSE
1976 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
1977 END IF;
1978
1979 p_xfdf_clob := l_xfdf_string;
1980 -- hr_utility.set_location ('Leaving WritetoCLOB ', 40);
1981 EXCEPTION
1982 WHEN OTHERS
1983 THEN
1984 hr_utility.TRACE ('sqlerrm ' || SQLERRM);
1985 hr_utility.raise_error;
1986 END writetoclob;
1987
1988 PROCEDURE get_xml_for_report (
1989 p_business_group_id IN NUMBER
1990 , p_payroll_action_id IN VARCHAR2
1991 , p_template_name IN VARCHAR2
1992 , p_xml OUT NOCOPY CLOB
1993 )
1994 IS
1995 --Variables needed for the report
1996 l_counter NUMBER := 0;
1997 l_payroll_action_id pay_action_information.action_information1%TYPE;
1998
1999 --Cursors needed for report
2000 CURSOR csr_all_legal_employer (
2001 csr_v_pa_id pay_action_information.action_context_id%TYPE
2002 )
2003 IS
2004 SELECT action_information3, action_information4
2005 , action_information5
2006 FROM pay_action_information
2007 WHERE action_context_type = 'PA'
2008 AND action_context_id = csr_v_pa_id
2009 AND action_information_category = 'EMEA REPORT INFORMATION'
2010 AND action_information1 = 'PYSEHPDA'
2011 AND action_information2 = 'LE';
2012
2013 CURSOR csr_report_details (
2014 csr_v_pa_id pay_action_information.action_context_id%TYPE
2015 )
2016 IS
2017 SELECT TO_CHAR
2018 (fnd_date.canonical_to_date (action_information5)
2019 , 'YYYYMMDD'
2020 ) period_from
2021 , TO_CHAR
2022 (fnd_date.canonical_to_date (action_information6)
2023 , 'YYYYMMDD'
2024 ) period_to
2025 FROM pay_action_information
2026 WHERE action_context_type = 'PA'
2027 AND action_context_id = csr_v_pa_id
2028 AND action_information_category = 'EMEA REPORT DETAILS'
2029 AND action_information1 = 'PYSEHPDA';
2030
2031 lr_report_details csr_report_details%ROWTYPE;
2032
2033 CURSOR csr_all_employees_under_le (
2034 csr_v_pa_id pay_action_information.action_information3%TYPE
2035 , csr_v_le_id pay_action_information.action_information15%TYPE
2036 )
2037 IS
2038 SELECT *
2039 FROM pay_action_information
2040 WHERE action_context_type = 'AAP'
2041 AND action_information_category = 'EMEA REPORT INFORMATION'
2042 AND action_information1 = 'PYSEHPDA'
2043 AND action_information3 = csr_v_pa_id
2044 AND action_information2 = 'PER'
2045 AND action_information15 = csr_v_le_id
2046 ORDER BY action_information30;
2047
2048 /* End of declaration*/
2049 /* Proc to Add the tag value and Name */
2050 PROCEDURE add_tag_value (p_tag_name IN VARCHAR2, p_tag_value IN VARCHAR2)
2051 IS
2052 BEGIN
2053 ghpd_data (l_counter).tagname := p_tag_name;
2054 ghpd_data (l_counter).tagvalue := p_tag_value;
2055 l_counter := l_counter + 1;
2056 END add_tag_value;
2057 /* End of Proc to Add the tag value and Name */
2058 /* Start of GET_HPD_XML */
2059 BEGIN
2060 IF p_payroll_action_id IS NULL
2061 THEN
2062 BEGIN
2063 SELECT payroll_action_id
2064 INTO l_payroll_action_id
2065 FROM pay_payroll_actions ppa
2066 , fnd_conc_req_summary_v fcrs
2067 , fnd_conc_req_summary_v fcrs1
2068 WHERE fcrs.request_id = fnd_global.conc_request_id
2069 AND fcrs.priority_request_id = fcrs1.priority_request_id
2070 AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
2071 AND ppa.request_id = fcrs1.request_id;
2072 EXCEPTION
2073 WHEN OTHERS
2074 THEN
2075 NULL;
2076 END;
2077 ELSE
2078 l_payroll_action_id := p_payroll_action_id;
2079
2080 /* Structure of Xml should look like this
2081 <LE>
2082 <DETAILS>
2083 </DETAILS>
2084 <EMPLOYEES>
2085 <PERSON>
2086 </PERSON>
2087 </EMPLOYEES>
2088 </LE>
2089 */
2090 OPEN csr_report_details (l_payroll_action_id);
2091
2092 FETCH csr_report_details
2093 INTO lr_report_details;
2094
2095 CLOSE csr_report_details;
2096
2097 add_tag_value ('PERIOD_FROM', lr_report_details.period_from);
2098 add_tag_value ('PERIOD_TO', lr_report_details.period_to);
2099
2100 FOR rec_all_le IN csr_all_legal_employer (l_payroll_action_id)
2101 LOOP
2102 add_tag_value ('LEGAL_EMPLOYER', 'LEGAL_EMPLOYER');
2103 add_tag_value ('LE_DETAILS', 'LE_DETAILS');
2104 add_tag_value ('LE_NAME', rec_all_le.action_information4);
2105 add_tag_value ('LE_ORG_NUM', rec_all_le.action_information5);
2106 add_tag_value ('LE_DETAILS', 'LE_DETAILS_END');
2107 add_tag_value ('EMPLOYEES', 'EMPLOYEES');
2108
2109 FOR rec_all_emp_under_le IN
2110 csr_all_employees_under_le (l_payroll_action_id
2111 , rec_all_le.action_information3
2112 )
2113 LOOP
2114
2115 add_tag_value ('PERSON', 'PERSON');
2116 add_tag_value ('EMPLOYEE_CODE'
2117 , rec_all_emp_under_le.action_information4
2118 );
2119 add_tag_value ('EMPLOYEE_NUMBER'
2120 , rec_all_emp_under_le.action_information5
2121 );
2122 add_tag_value ('EMPLOYEE_NAME'
2123 , rec_all_emp_under_le.action_information6
2124 );
2125 add_tag_value
2126 ('HOLIDAY_PAY_PER_DAY'
2127 , TO_CHAR
2128 (fnd_number.canonical_to_number
2129 (rec_all_emp_under_le.action_information7)
2130 , '999999990D99'
2131 )
2132 );
2133 add_tag_value ('TOTAL_PAID_DAYS'
2134 , rec_all_emp_under_le.action_information8
2135 );
2136 add_tag_value
2137 ('TOTAL_PAID_DAYS_AMOUNT'
2138 , TO_CHAR
2139 (fnd_number.canonical_to_number
2140 (rec_all_emp_under_le.action_information9)
2141 , '999999990D99'
2142 )
2143 );
2144 add_tag_value ('TOTAL_SAVED_DAYS'
2145 , rec_all_emp_under_le.action_information10
2146 );
2147 add_tag_value
2148 ('TOTAL_SAVED_DAYS_AMOUNT'
2149 , TO_CHAR
2150 (fnd_number.canonical_to_number
2151 (rec_all_emp_under_le.action_information11)
2152 , '999999990D99'
2153 )
2154 );
2155 add_tag_value ('TOTAL_EARNED_DAYS'
2156 , rec_all_emp_under_le.action_information12
2157 );
2158 add_tag_value
2159 ('TOTAL_EARNED_DAYS_AMOUNT'
2160 , TO_CHAR
2161 (fnd_number.canonical_to_number
2162 (rec_all_emp_under_le.action_information13)
2163 , '999999990D99'
2164 )
2165 );
2166 add_tag_value ('PERSON', 'PERSON_END');
2167 END LOOP; /* For all EMPLOYEES */
2168
2169
2170 add_tag_value ('EMPLOYEES', 'EMPLOYEES_END');
2171 add_tag_value ('LEGAL_EMPLOYER', 'LEGAL_EMPLOYER_END');
2172 END LOOP; /* For all LEGAL_EMPLYER */
2173 END IF; /* for p_payroll_action_id IS NULL */
2174
2175 writetoclob (p_xml);
2176 END get_xml_for_report;
2177
2178 -----------------------------------------------------------------------------------
2179 -- POPULATE_DATA_DETAIL generates xml for the reports.
2180 -----------------------------------------------------------------------------------
2181 --
2182 --
2183 PROCEDURE POPULATE_DATA_DETAIL
2184 (p_business_group_id IN NUMBER,
2185 p_payroll_action_id IN VARCHAR2 ,
2186 p_template_name IN VARCHAR2,
2187 p_xml OUT NOCOPY CLOB)
2188 IS
2189
2190
2191 /* Cursor to fetch Header Information */
2192
2193
2194 CURSOR csr_local_unit_details (p_payroll_action_id NUMBER)
2195 IS
2196 SELECT
2197 pai1.action_information3 Legal_Employer,
2198 pai1.action_information4 Local_Unit,
2199 pai.ACTION_INFORMATION9 Report_Date,
2200 --'0000000SCB',
2201 pai1.action_information6 Fiscal_Year,
2202 pai1.action_information7 Fiscal_Quarter,
2203 pai1.action_information8 Fiscal_Month,
2204 pai1.ACTION_INFORMATION5 CFAR_Number,
2205 pai1.action_information9 Regular_Men,
2206 pai1.action_information10 Regular_Women,
2207 pai1.action_information11 Temporary_Men,
2208 pai1.action_information12 Temporary_Women,
2209 pai1.action_information13 Sick_Men,
2210 pai1.action_information14 Sick_Women,
2211 pai1.action_information15 Holiday_Men,
2212 pai1.action_information16 Holiday_Women,
2213 pai1.action_information17 Other_Men,
2214 pai1.action_information18 Other_Women,
2215 pai1.action_information19 New_Regular_Men,
2216 pai1.action_information20 New_Regular_Women,
2217 pai1.action_information21 New_Temporary_Men,
2218 pai1.action_information22 New_Temporary_women,
2219 pai1.action_information23 Terminate_Regular_Men,
2220 pai1.action_information24 Terminate_Regular_Women,
2221 pai1.action_information25 Terminate_Temporary_Men,
2222 pai1.action_information26 Terminate_Temporary_Women
2223 FROM
2224 pay_action_information pai,
2225 pay_payroll_actions ppa,
2226 pay_action_information pai1
2227 WHERE
2228 pai.action_context_id = ppa.payroll_action_id
2229 AND ppa.payroll_action_id =p_payroll_action_id --20162 --20264 --20165
2230 AND pai.action_context_id = pai1.action_context_id
2231 AND pai1.action_context_id= ppa.payroll_action_id
2232 AND pai1.action_context_type='PA'
2233 AND pai1.action_information2 = 'LU'
2234 AND pai1.action_information1 = 'PYSESTEA'
2235 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
2236 AND pai.action_context_type = 'PA'
2237 AND pai.action_information1 = 'PYSESTEA'
2238 AND pai.action_information_category = 'EMEA REPORT DETAILS'
2239 order BY pai1.ACTION_INFORMATION3,pai1.ACTION_INFORMATION2;
2240
2241 /*CURSOR csr_legal_employer_details (p_payroll_action_id NUMBER, p_legal_employer varchar2 )
2242 IS
2243 SELECT SUM(pai1.action_information9) Regular_Men,
2244 sum(pai1.action_information10) Regular_Women,
2245 sum(pai1.action_information11) Temporary_Men,
2246 sum(pai1.action_information12) Temporary_Women
2247 FROM
2248 pay_payroll_actions ppa,
2249 pay_action_information pai1
2250 WHERE
2251 pai1.action_context_id = ppa.payroll_action_id
2252 AND ppa.payroll_action_id =p_payroll_action_id
2253 AND pai1.action_context_type='PA'
2254 AND pai1.action_information2 = 'LU'
2255 AND pai1.action_information1 = 'PYSESTEA'
2256 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
2257 AND pai1.action_information3=p_legal_employer;*/
2258
2259 CURSOR csr_legal_employer /*(p_payroll_action_id NUMBER, */(p_legal_employer varchar2 )
2260 IS
2261 SELECT
2262 count(paa.assignment_id) TOTAL
2263 ,paa.employment_category EMP_CAT
2264 ,pap.SEX
2265 FROM
2266 hr_all_organization_units hou_le,
2267 hr_organization_information hoi_le,
2268 hr_all_organization_units hou_lu,
2269 hr_organization_information hoi_lu,
2270 PER_ALL_ASSIGNMENTS_F paa
2271 ,HR_SOFT_CODING_KEYFLEX scl1
2272 ,PER_ALL_PEOPLE_F pap
2273 WHERE
2274 hoi_le.organization_id = hou_le.organization_id
2275 AND hou_le.name =p_legal_employer
2276 AND hoi_le.org_information_context = 'SE_LOCAL_UNITS'
2277 AND hou_lu.organization_id = hoi_le.org_information1
2278 AND hou_lu.organization_id = hoi_lu.organization_id
2279 AND hoi_lu.org_information_context = 'SE_LOCAL_UNIT_DETAILS'
2280 AND paa.person_id = pap.person_id
2281 and paa.business_group_id = g_business_group_id
2282 and scl1.segment2 = TO_CHAR(hoi_le.org_information1)
2283 AND scl1.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
2284 and paa.employment_category in ('FR','FT')
2285 and paa.ASSIGNMENT_STATUS_TYPE_ID = 1
2286 and pap.SEX in('F','M')
2287 and g_account_date between paa.EFFECTIVE_START_DATE and paa.EFFECTIVE_END_DATE
2288 and g_account_date between pap.EFFECTIVE_START_DATE and pap.EFFECTIVE_END_DATE
2289 group by paa.employment_category,pap.SEX
2290 order by paa.employment_category,pap.SEX;
2291
2292
2293 l_local_unit_details_rec csr_local_unit_details%rowtype;
2294
2295
2296
2297 l_counter NUMBER;
2298 l_total NUMBER;
2299 l_total_eft NUMBER;
2300 l_count NUMBER;
2301 l_payroll_action_id NUMBER;
2302 l_lu_counter_reset VARCHAR2(10);
2303 l_prev_local_unit VARCHAR2(15);
2304 l_report_date DATE;
2305 l_total_termination NUMBER;
2306 l_total_hire NUMBER;
2307 l_total_absence NUMBER;
2308 l_total_sick NUMBER;
2309 l_total_lu_emp NUMBER;
2310 l_total_le_emp NUMBER;
2311 l_legal_employer VARCHAR2(80);
2312 l_regular_men NUMBER;
2313 l_regular_women NUMBER;
2314 l_temp_men NUMBER;
2315 l_temp_women NUMBER;
2316
2317 BEGIN
2318
2319 l_counter:=0;
2320
2321 IF p_payroll_action_id IS NULL THEN
2322 BEGIN
2323 SELECT payroll_action_id
2324 INTO l_payroll_action_id
2325 FROM pay_payroll_actions ppa,
2326 fnd_conc_req_summary_v fcrs,
2327 fnd_conc_req_summary_v fcrs1
2328 WHERE fcrs.request_id = fnd_global.conc_request_id
2329 AND fcrs.priority_request_id = fcrs1.priority_request_id
2330 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
2331 AND ppa.request_id = fcrs1.request_id;
2332 EXCEPTION
2333 WHEN OTHERS THEN
2334 NULL;
2335 END ;
2336 ELSE
2337 l_payroll_action_id := p_payroll_action_id;
2338 END IF;
2339 g_payroll_action_id :=p_payroll_action_id;
2340 /* g_business_group_id := null;
2341 g_legal_employer_id := null;
2342 g_start_date := null;
2343 g_end_date := null;
2344 g_version := null;
2345 g_archive := null;*/
2346
2347 get_all_parameters (p_payroll_action_id
2348 , g_business_group_id
2349 , g_effective_date
2350 , g_legal_employer_id
2351 , g_LE_request
2352 , g_LU_request
2353 , g_local_unit_id
2354 , g_account_date
2355 , g_posting_date
2356 , g_reporting_date
2357 );
2358
2359 hr_utility.set_location('Entered Procedure GETDATA',10);
2360
2361 /* xml_tab(l_counter).TagName :='LU_DETAILS';
2362 xml_tab(l_counter).TagValue :='LU_DETAILS';*/
2363 /* l_counter:=l_counter+1;*/
2364
2365 /* Get the File Header Information */
2366 hr_utility.set_location('Before populating pl/sql table',20);
2367 FOR csr_local IN csr_local_unit_details(p_payroll_action_id) loop
2368
2369 xml_tab(l_counter).TagName :='LU_DETAILS';
2370 xml_tab(l_counter).TagValue :='LU_DETAILS';
2371 l_counter:=l_counter+1;
2372
2373
2374 hr_utility.set_location('Entered Procedure GETDATA',10);
2375
2376 xml_tab(l_counter).TagName :='LEGAL_EMPLOYER';
2377 xml_tab(l_counter).TagValue := csr_local.Legal_Employer;
2378 l_counter:=l_counter+1;
2379
2380 l_legal_employer:=csr_local.Legal_Employer;
2381
2382
2383 xml_tab(l_counter).TagName :='LOCAL_UNIT';
2384 xml_tab(l_counter).TagValue := csr_local.Local_Unit;
2385 l_counter:=l_counter+1;
2386
2387 /*xml_tab(l_counter).TagName :='LOCAL_UNIT';
2388 xml_tab(l_counter).TagValue := csr_local.Local_Unit;
2389 l_counter:=l_counter+1;*/
2390
2391 xml_tab(l_counter).TagName :='REP_DATE';
2392 xml_tab(l_counter).TagValue := to_char(g_posting_date,'YYYY-MM-DD');
2393 l_counter:=l_counter+1;
2394
2395 xml_tab(l_counter).TagName :='ACC_DATE';
2396 xml_tab(l_counter).TagValue := to_char(g_account_date,'YYYY-MM-DD');
2397 l_counter:=l_counter+1;
2398
2399 xml_tab(l_counter).TagName :='MONTH';
2400 xml_tab(l_counter).TagValue := to_char(g_account_date,'MONTH');
2401 l_counter:=l_counter+1;
2402
2403 xml_tab(l_counter).TagName :='YEAR';
2404 xml_tab(l_counter).TagValue := to_char(g_account_date,'YYYY');
2405 l_counter:=l_counter+1;
2406
2407 xml_tab(l_counter).TagName :='REPORT_DATE';
2408 --l_report_date:=csr_local.Report_Date;
2409
2410 xml_tab(l_counter).TagValue :=to_char(g_reporting_date,'YYMMDD'); --to_char(l_local_unit_details_rec.Report_Date,'YYYYMMDD');
2411 l_counter:=l_counter+1;
2412
2413
2414
2415 xml_tab(l_counter).TagName :='SCB';
2416 xml_tab(l_counter).TagValue := 0;
2417 l_counter:=l_counter+1;
2418
2419 xml_tab(l_counter).TagName :='FISCAL_YEAR';
2420 xml_tab(l_counter).TagValue := csr_local.Fiscal_Year;
2421 l_counter:=l_counter+1;
2422
2423 xml_tab(l_counter).TagName :='FISCAL_MONTH';
2424 xml_tab(l_counter).TagValue := csr_local.Fiscal_Month;
2425 l_counter:=l_counter+1;
2426
2427 xml_tab(l_counter).TagName :='FISCAL_QUARTER';
2428 xml_tab(l_counter).TagValue := csr_local.Fiscal_Quarter;
2429 l_counter:=l_counter+1;
2430
2431 xml_tab(l_counter).TagName :='CFAR_NUMBER';
2432 xml_tab(l_counter).TagValue := csr_local.CFAR_Number;
2433 l_counter:=l_counter+1;
2434
2435 xml_tab(l_counter).TagName :='ACCOUNT_NUM';
2436 xml_tab(l_counter).TagValue := 0;
2437 l_counter:=l_counter+1;
2438
2439 xml_tab(l_counter).TagName :='ACTIVE_MEN';
2440 xml_tab(l_counter).TagValue := 0;
2441 l_counter:=l_counter+1;
2442
2443 xml_tab(l_counter).TagName :='ACTIVE_WOMEN';
2444 xml_tab(l_counter).TagValue := 0;
2445 l_counter:=l_counter+1;
2446
2447 xml_tab(l_counter).TagName :='REG_MEN';
2448 xml_tab(l_counter).TagValue := csr_local.Regular_Men;
2449 l_counter:=l_counter+1;
2450
2451 xml_tab(l_counter).TagName :='REG_WOMEN';
2452 xml_tab(l_counter).TagValue := csr_local.Regular_Women;
2453 l_counter:=l_counter+1;
2454
2455 xml_tab(l_counter).TagName :='TEMP_MEN';
2456 xml_tab(l_counter).TagValue := csr_local.Temporary_Men;
2457 l_counter:=l_counter+1;
2458
2459 xml_tab(l_counter).TagName :='TEMP_WOMEN';
2460 xml_tab(l_counter).TagValue := csr_local.Temporary_Women;
2461 l_counter:=l_counter+1;
2462
2463 l_total_lu_emp:=csr_local.Regular_Men+csr_local.Regular_Women+csr_local.Temporary_Men+csr_local.Temporary_Women;
2464
2465 xml_tab(l_counter).TagName :='TOTAL_LU_EMP';
2466 xml_tab(l_counter).TagValue := l_total_lu_emp;
2467 l_counter:=l_counter+1;
2468
2469 xml_tab(l_counter).TagName :='SICK_MEN';
2470 xml_tab(l_counter).TagValue := csr_local.Sick_Men;
2471 l_counter:=l_counter+1;
2472
2473 xml_tab(l_counter).TagName :='SICK_WOMEN';
2474 xml_tab(l_counter).TagValue := csr_local.Sick_Women;
2475 l_counter:=l_counter+1;
2476
2477 xml_tab(l_counter).TagName :='FILLER';
2478 xml_tab(l_counter).TagValue := 0;
2479 l_counter:=l_counter+1;
2480
2481 xml_tab(l_counter).TagName :='HOLI_MEN';
2482 xml_tab(l_counter).TagValue := csr_local.Holiday_Men;
2483 l_counter:=l_counter+1;
2484
2485 xml_tab(l_counter).TagName :='HOLI_WOMEN';
2486 xml_tab(l_counter).TagValue := csr_local.Holiday_Women;
2487 l_counter:=l_counter+1;
2488
2489 xml_tab(l_counter).TagName :='OTH_MEN';
2490 xml_tab(l_counter).TagValue := csr_local.Other_Men;
2491 l_counter:=l_counter+1;
2492
2493 xml_tab(l_counter).TagName :='OTH_WOMEN';
2494 xml_tab(l_counter).TagValue := csr_local.Other_Women;
2495 l_counter:=l_counter+1;
2496
2497 l_total_absence:=csr_local.Sick_Men+csr_local.Sick_Women+csr_local.Holiday_Men
2498 +csr_local.Holiday_Women+csr_local.Other_Men+csr_local.Other_Women;
2499
2500 xml_tab(l_counter).TagName :='TOTAL_ABSENCE';
2501 xml_tab(l_counter).TagValue := l_total_absence;
2502 l_counter:=l_counter+1;
2503
2504 l_total_sick:=csr_local.Sick_Men+csr_local.Sick_Women;
2505
2506 xml_tab(l_counter).TagName :='TOTAL_SICK';
2507 xml_tab(l_counter).TagValue := l_total_sick;
2508 l_counter:=l_counter+1;
2509
2510 xml_tab(l_counter).TagName :='NEW_REGULAR_MEN';
2511 xml_tab(l_counter).TagValue := csr_local.New_Regular_Men;
2512 l_counter:=l_counter+1;
2513
2514 xml_tab(l_counter).TagName :='NEW_REGULAR_WOMEN';
2515 xml_tab(l_counter).TagValue := csr_local.New_Regular_Women;
2516 l_counter:=l_counter+1;
2517
2518 xml_tab(l_counter).TagName :='NEW_TEMP_MEN';
2519 xml_tab(l_counter).TagValue := csr_local.New_Temporary_Men;
2520 l_counter:=l_counter+1;
2521
2522
2523 xml_tab(l_counter).TagName :='NEW_TEMP_WOMEN';
2524 xml_tab(l_counter).TagValue := csr_local.New_Temporary_Women;
2525 l_counter:=l_counter+1;
2526
2527 l_total_hire:=csr_local.New_Regular_Men+csr_local.New_Regular_Women+
2528 csr_local.New_Temporary_Men+csr_local.New_Temporary_Women;
2529
2530 xml_tab(l_counter).TagName :='NEW_HIRE';
2531 xml_tab(l_counter).TagValue := l_total_hire;
2532 l_counter:=l_counter+1;
2533
2534 xml_tab(l_counter).TagName :='TER_REG_MEN';
2535 xml_tab(l_counter).TagValue := csr_local.Terminate_Regular_Men;
2536 l_counter:=l_counter+1;
2537
2538
2539 xml_tab(l_counter).TagName :='TER_REG_WOMEN';
2540 xml_tab(l_counter).TagValue := csr_local.Terminate_Regular_Women;
2541 l_counter:=l_counter+1;
2542
2543 xml_tab(l_counter).TagName :='TER_TEMP_MEN';
2544 xml_tab(l_counter).TagValue := csr_local.Terminate_Temporary_Men;
2545 l_counter:=l_counter+1;
2546
2547
2548 xml_tab(l_counter).TagName :='TER_TEMP_WOMEN';
2549 xml_tab(l_counter).TagValue := csr_local.Terminate_Temporary_Women;
2550 l_counter:=l_counter+1;
2551
2552 l_total_termination:=csr_local.Terminate_Regular_Men+csr_local.Terminate_Regular_Women+
2553 csr_local.Terminate_Temporary_Men+csr_local.Terminate_Temporary_Women;
2554
2555 xml_tab(l_counter).TagName :='TOTAL_TERM';
2556 xml_tab(l_counter).TagValue := l_total_termination;
2557 l_counter:=l_counter+1;
2558
2559 /*OPEN csr_legal_employer_details(p_payroll_action_id,l_legal_employer);
2560 FETCH csr_legal_employer_details INTO l_regular_men,l_regular_women,l_temp_men,l_temp_women;
2561 CLOSE csr_legal_employer_details;*/
2562
2563 FOR csr_legal IN csr_legal_employer(l_legal_employer) --(g_local_unit_id)
2564 LOOP
2565 IF csr_legal.SEX ='M'
2566 THEN
2567 IF csr_legal.EMP_CAT ='FR'
2568 THEN
2569 l_regular_men := csr_legal.TOTAL;
2570
2571 ELSIF csr_legal.EMP_CAT ='FT'
2572 THEN
2573 l_temp_men := csr_legal.TOTAL;
2574 END IF;
2575
2576
2577 ELSIF csr_legal.SEX ='F'
2578 THEN
2579 IF csr_legal.EMP_CAT ='FR'
2580 THEN
2581 l_regular_women := csr_legal.TOTAL;
2582 ELSIF csr_legal.EMP_CAT ='FT'
2583 THEN
2584 l_temp_women := csr_legal.TOTAL;
2585 END IF;
2586
2587 END IF;
2588
2589 END LOOP;
2590
2591 l_regular_men:=NVL(l_regular_men,0);
2592 l_regular_women:=NVL(l_regular_women,0);
2593 l_temp_men:=NVL(l_temp_men,0);
2594 l_temp_women:=NVL(l_temp_women,0);
2595
2596 xml_tab(l_counter).TagName :='LE_REG_MEN';
2597 xml_tab(l_counter).TagValue := l_regular_men;
2598 l_counter:=l_counter+1;
2599
2600 xml_tab(l_counter).TagName :='LE_REG_WOMEN';
2601 xml_tab(l_counter).TagValue := l_regular_women;
2602 l_counter:=l_counter+1;
2603
2604 xml_tab(l_counter).TagName :='LE_TEMP_MEN';
2605 xml_tab(l_counter).TagValue := l_temp_men;
2606 l_counter:=l_counter+1;
2607
2608 xml_tab(l_counter).TagName :='LE_TEMP_WOMEN';
2609 xml_tab(l_counter).TagValue := l_temp_women;
2610 l_counter:=l_counter+1;
2611
2612 xml_tab(l_counter).TagName :='TOTAL_LE_EMP';
2613 xml_tab(l_counter).TagValue := l_regular_men+l_regular_women+l_temp_men+l_temp_women;
2614 l_counter:=l_counter+1;
2615
2616 hr_utility.set_location('After populating pl/sql table',30);
2617 hr_utility.set_location('Entered Procedure GETDATA',10);
2618
2619 xml_tab(l_counter).TagName :='LU_DETAILS';
2620 xml_tab(l_counter).TagValue :='END_LU_DETAILS';
2621 l_counter := l_counter + 1;
2622 END LOOP;
2623 /*xml_tab(l_counter).TagName :='LU_DETAILS';
2624 xml_tab(l_counter).TagValue :='END_LU_DETAILS';*/
2625 /*l_counter := l_counter + 1;*/
2626
2627 WritetoCLOB (p_xml );
2628
2629
2630
2631 END POPULATE_DATA_DETAIL;
2632
2633 END PAY_SE_EMPLOYMENT_STATISTICS;
2634