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