DBA Data[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