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