[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_SALARY_STRUCTURE_STATS
Source
1 PACKAGE BODY PAY_SE_SALARY_STRUCTURE_STATS AS
2 /* $Header: pysessst.pkb 120.1 2007/07/19 09:49:49 rravi noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE lock_rec IS RECORD (
6 archive_assact_id NUMBER
7 );
8
9 TYPE lock_table IS TABLE OF lock_rec
10 INDEX BY BINARY_INTEGER;
11
12 g_lock_table lock_table;
13 g_index NUMBER := -1;
14 g_index_assact NUMBER := -1;
15 g_index_bal NUMBER := -1;
16 g_package VARCHAR2 (33) := 'PAY_SE_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
28
29 g_posting_date DATE;
30 g_account_date DATE;
31 g_reporting_date DATE;
32 g_report_year NUMBER;
33 g_month NUMBER;
34 g_retroactive_payment_from DATE;
35 g_retroactive_payment_to DATE;
36 g_start_date DATE;
37 g_end_date DATE;
38 --End of Globals to pick up all the parameter
39 g_format_mask VARCHAR2 (50);
40 g_err_num NUMBER;
41 g_errm VARCHAR2 (150);
42
43 /* GET PARAMETER */
44
45
46 /* GET PARAMETER */
47 FUNCTION get_parameter (
48 p_parameter_string IN VARCHAR2
49 , p_token IN VARCHAR2
50 , p_segment_number IN NUMBER DEFAULT NULL
51 )
52 RETURN VARCHAR2
53 IS
54 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
55 l_start_pos NUMBER;
56 l_delimiter VARCHAR2 (1) := ' ';
57 l_proc VARCHAR2 (240) := g_package || ' get parameter ';
58 BEGIN
59 --
60 IF g_debug
61 THEN
62 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
63 END IF;
64
65 l_start_pos :=
66 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
67
68 --
69 IF l_start_pos = 0
70 THEN
71 l_delimiter := '|';
72 l_start_pos :=
73 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
74 END IF;
75
76 IF l_start_pos <> 0
77 THEN
78 l_start_pos := l_start_pos + LENGTH (p_token || '=');
79 l_parameter :=
80 SUBSTR (p_parameter_string
81 , l_start_pos
82 , INSTR (p_parameter_string || ' '
83 , l_delimiter
84 , l_start_pos
85 )
86 - (l_start_pos)
87 );
88
89 IF p_segment_number IS NOT NULL
90 THEN
91 l_parameter := ':' || l_parameter || ':';
92 l_parameter :=
93 SUBSTR (l_parameter
94 , INSTR (l_parameter, ':', 1, p_segment_number) + 1
95 , INSTR (l_parameter, ':', 1, p_segment_number + 1)
96 - 1
97 - INSTR (l_parameter, ':', 1, p_segment_number)
98 );
99 END IF;
100 END IF;
101
102 --
103 IF g_debug
104 THEN
105 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
106 END IF;
107
108 RETURN l_parameter;
109 END;
110
111 /* GET ALL PARAMETERS */
112 PROCEDURE get_all_parameters (
113 p_payroll_action_id IN NUMBER -- In parameter
114 , p_business_group_id OUT NOCOPY NUMBER -- Core parameter
115 , p_effective_date OUT NOCOPY DATE -- Core parameter
116 , p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
117 , p_LE_request OUT NOCOPY VARCHAR2 -- User parameter
118 , p_month OUT NOCOPY NUMBER -- User parameter
119 , p_report_year OUT NOCOPY NUMBER -- User parameter
120 )
121 IS
122 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
123 IS
124 SELECT (get_parameter
125 (legislative_parameters
126 , 'LEGAL_EMPLOYER'
127 )
128 ) LEGAL_EMPLOYER_ID
129 , (get_parameter
130 (legislative_parameters
131 , 'LE_REQUEST'
132 )
133 ) LE_REQUEST
134 ,(get_parameter
135 (legislative_parameters
136 , 'MONTH'
137 )
138 ) L_MONTH
139 ,(get_parameter
140 (legislative_parameters
141 , 'REPORT_YEAR'
142 )
143 ) L_REPORT_YEAR
144 , effective_date, business_group_id bg_id
145 FROM pay_payroll_actions
146 WHERE payroll_action_id = p_payroll_action_id;
147
148 lr_parameter_info csr_parameter_info%ROWTYPE;
149 l_proc VARCHAR2 (240)
150 := g_package || ' GET_ALL_PARAMETERS ';
151 BEGIN
152
153 OPEN csr_parameter_info (p_payroll_action_id);
154
155 --FETCH csr_parameter_info into lr_parameter_info;
156 FETCH csr_parameter_info
157 INTO lr_parameter_info;
158
159 CLOSE csr_parameter_info;
160
161 p_legal_employer_id := lr_parameter_info.legal_employer_id;
162
163
164 p_LE_request := lr_parameter_info.LE_REQUEST;
165
166
167
168
169 p_month:=lr_parameter_info.l_month;
170 p_report_year:=lr_parameter_info.l_report_year;
171 p_effective_date := lr_parameter_info.effective_date;
172 p_business_group_id := lr_parameter_info.bg_id;
173
174
175 IF g_debug
176 THEN
177 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS'
178 , 30);
179 END IF;
180 END get_all_parameters;
181
182 /* RANGE CODE */
183 PROCEDURE range_code (
184 p_payroll_action_id IN NUMBER
185 , p_sql OUT NOCOPY VARCHAR2
186 )
187 IS
188
189 /* Local Unit Details */
190 CURSOR csr_local_unit_details (csr_v_local_unit_id hr_organization_information.organization_id%TYPE)
191 IS
192 SELECT --o1.NAME local_unit_name,
193 hoi2.org_information2 cfar_number
194 FROM hr_organization_units o1
195 , hr_organization_information hoi1
196 , hr_organization_information hoi2
197 WHERE o1.business_group_id = g_business_group_id
198 AND hoi1.organization_id = o1.organization_id
199 AND hoi1.organization_id = csr_v_local_unit_id
200 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
201 AND hoi1.org_information_context = 'CLASS'
202 AND o1.organization_id = hoi2.organization_id
203 AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
204
205 /*Salary Structure EIT Details */
206 CURSOR csr_salary_structure_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
207 csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
208 IS
209 SELECT hoi2.org_information1 Worksite_Number,
210 hoi2.org_information2 Association_Number,
211 hl.meaning Agreement_Code,
212 hoi2.org_information4 Weekend_duty_pay
213 FROM hr_organization_units o1
214 , hr_organization_information hoi1
215 , hr_organization_information hoi2
216 ,hr_lookups hl
217 WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
218 AND hoi1.organization_id = o1.organization_id
219 AND hoi1.organization_id = csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
220 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
221 AND hoi1.org_information_context = 'CLASS'
222 AND o1.organization_id =hoi2.organization_id
223 AND hoi2.ORG_INFORMATION_CONTEXT='SE_SALARY_STRUCTURE'
224 AND hl.lookup_type(+) ='SE_AGREEMENT_CODE'
225 AND hl.LOOKUP_CODE(+)=hoi2.org_information3 ;
226
227 /* Legal Employers under the Business Group */
228 CURSOR csr_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE)
229 IS
230 SELECT o1.organization_id legal_employer_id
231 FROM hr_organization_units o1
232 , hr_organization_information hoi1
233 WHERE o1.business_group_id =csr_v_business_group_id --3133
234 AND hoi1.organization_id = o1.organization_id
235 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
236 AND hoi1.org_information_context = 'CLASS';
237
238 /* Legal Employer Details */
239 CURSOR csr_legal_employer_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
240 csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
241 IS
242 SELECT o1.name legal_employer,
243 hoi2.org_information2 Organization_Id,
244 hoi2.org_information9 Membership_Number
245 FROM hr_organization_units o1
246 , hr_organization_information hoi1
247 , hr_organization_information hoi2
248 WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
249 AND hoi1.organization_id = o1.organization_id
250 AND hoi1.organization_id = csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
251 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
252 AND hoi1.org_information_context = 'CLASS'
253 AND o1.organization_id =hoi2.organization_id
254 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS';
255
256
257 /*Local Units under the Legal Employer*/
258 CURSOR csr_local_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
259 csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
260 IS
261 SELECT hoi2.ORG_INFORMATION1 local_unit
262 FROM hr_organization_units o1
263 , hr_organization_information hoi1
264 , hr_organization_information hoi2
265 WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
266 AND hoi1.organization_id = o1.organization_id
267 AND hoi1.organization_id = csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
268 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
269 AND hoi1.org_information_context = 'CLASS'
270 AND o1.organization_id =hoi2.organization_id
271 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';
272
273 l_action_info_id NUMBER;
274 l_ovn NUMBER;
275 l_business_group_id NUMBER;
276 --l_start_date VARCHAR2 (30);
277 --l_end_date VARCHAR2 (30);
278 l_effective_date DATE;
279 l_consolidation_set NUMBER;
280 l_defined_balance_id NUMBER := 0;
281 l_count NUMBER := 0;
282 l_prev_prepay NUMBER := 0;
283 l_canonical_start_date DATE;
284 l_canonical_end_date DATE;
285 l_payroll_id NUMBER;
286 l_prepay_action_id NUMBER;
287 l_actid NUMBER;
288 -- l_assignment_id NUMBER;
289 l_action_sequence NUMBER;
290 l_assact_id NUMBER;
291 l_pact_id NUMBER;
292 l_flag NUMBER := 0;
293 l_element_context VARCHAR2 (5);
294
295 -- Archiving the data , as this will fire once
296
297 CURSOR csr_sep_month (csr_v_month NUMBER)
298 IS
299 SELECT MEANING
300 FROM hr_lookups
301 WHERE LOOKUP_TYPE = 'HR_SE_SEPTEMBER'
302 AND ENABLED_FLAG = 'Y'
303 AND LOOKUP_CODE = csr_v_month; -- 01;
304
305 l_month varchar2(50);
306 l_worksite_number varchar2(10);
307 l_association_number varchar2(10);
308 l_legal_agreement_code varchar2(10);
309 l_agreement_code varchar2(10);
310 l_weekend_duty varchar2(10);
311 l_legal_employer hr_organization_units.name%TYPE;
312 l_organization_id varchar2(20);
313 l_membership_number varchar2(10);
314 L_CFAR_NUMBER NUMBER;
315 l_local_unit_id NUMBER;
316 l_legal_employer_id hr_organization_units.organization_id%TYPE;
317
318 TYPE emp_cat_type
319 IS TABLE OF VARCHAR2(10)
320 INDEX BY BINARY_INTEGER;
321 emp_cat emp_cat_type;
322
323 TYPE emp_job_record IS RECORD
324 (
325 job VARCHAR2(5),
326 end_date date
327 );
328 TYPE emp_job_type
329 IS TABLE OF emp_job_record
330 INDEX BY BINARY_INTEGER;
331 emp_job emp_job_type;
332
333 TYPE emp_detail_record IS RECORD
334 (
335 l_start_date date,
336 l_end_date date,
337 l_category varchar2(5),
338 l_job varchar2(5),
339 l_gross_salary number(17,2),
340 l_termination varchar2(5),
341 l_white_from date
342 );
343 TYPE emp_record_type
344 IS TABLE OF emp_detail_record
345 INDEX BY BINARY_INTEGER;
346 emp_record emp_record_type;
347
348 -- VARIABLE FOR THIS REPORET
349 BEGIN
350
351
352 IF g_debug
353 THEN
354 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
355 END IF;
356
357 p_sql :=
358 'SELECT DISTINCT person_id
359 FROM per_people_f ppf
360 ,pay_payroll_actions ppa
361 WHERE ppa.payroll_action_id = :payroll_action_id
362 AND ppa.business_group_id = ppf.business_group_id
363 ORDER BY ppf.person_id';
364 g_payroll_action_id := p_payroll_action_id;
365 g_business_group_id := NULL;
366 g_effective_date := NULL;
367 g_LE_request :=null;
368 g_legal_employer_id := NULL;
369 g_local_unit_id := NULL;
370 g_account_date :=null;
371 g_posting_date :=null;
372 get_all_parameters (p_payroll_action_id
373 , g_business_group_id
374 , g_effective_date
375 , g_legal_employer_id
376 , g_LE_request
377 , g_month
378 , g_report_year
379 );
380
381
382
383
384 /*OPEN csr_sep_month(g_month);
385 FETCH csr_sep_month INTO l_month;
386 CLOSE csr_sep_month;*/
387 -- Insert the report Parameters
388 OPEN csr_legal_employer_details(g_business_group_id,g_legal_employer_id);
389 FETCH csr_legal_employer_details INTO l_legal_employer,l_Organization_Id,l_Membership_Number;
390 CLOSE csr_legal_employer_details;
391
392 pay_action_information_api.create_action_information
393 (p_action_information_id => l_action_info_id
394 , p_action_context_id => p_payroll_action_id
395 , p_action_context_type => 'PA'
396 , p_object_version_number => l_ovn
397 , p_effective_date => g_effective_date
398 , p_source_id => NULL
399 , p_source_text => NULL
400 , p_action_information_category => 'EMEA REPORT DETAILS'
401 , p_action_information1 => 'PYSESSSA'
402 , p_action_information2 => hr_general.decode_lookup('SE_REQUEST_LEVEL',g_LE_request)
403 , p_action_information3 => g_legal_employer_id
404 , p_action_information4 => l_legal_employer
405 , p_action_information5 => l_month
406 , p_action_information6 => g_report_year
407 );
408 -- *****************************************************************************
409
410 IF g_LE_request ='LE_SELECTED' THEN
411 /*Legal Emplooyer Details*/
412 OPEN csr_legal_employer_details(g_business_group_id,g_legal_employer_id);
413 FETCH csr_legal_employer_details INTO l_legal_employer,l_organization_id,
414 l_membership_number;
415 CLOSE csr_legal_employer_details;
416 /* Salary Structure EIT Details */
417 OPEN csr_salary_structure_details(g_business_group_id,g_legal_employer_id);
418 FETCH csr_salary_structure_details INTO l_worksite_number,l_association_number,
419 l_legal_agreement_code,l_weekend_duty;
420 CLOSE csr_salary_structure_details;
421 pay_action_information_api.create_action_information
422 (p_action_information_id => l_action_info_id
423 , p_action_context_id => p_payroll_action_id
424 , p_action_context_type => 'PA'
425 , p_object_version_number => l_ovn
426 , p_effective_date => g_effective_date
427 , p_source_id => NULL
428 , p_source_text => NULL
429 , p_action_information_category => 'EMEA REPORT INFORMATION'
430 , p_action_information1 => 'PYSESSSA'
431 , p_action_information2 => 'LE'
432 , p_action_information3 => g_legal_employer_id
433 , p_action_information4 => l_legal_employer
434 , p_action_information5 => l_organization_id
435 , p_action_information6 => l_membership_number
436 , p_action_information7 => l_worksite_number
437 , p_action_information8 => l_association_number
438 , p_action_information9 => l_legal_agreement_code
439 , p_action_information10 => l_weekend_duty
440 );
441
442 FOR csr_legal_employer IN csr_local_legal_employer(g_business_group_id,g_legal_employer_id) LOOP
443 l_local_unit_id:=csr_legal_employer.local_unit;
444 OPEN csr_local_unit_details (l_local_unit_id);
445 FETCH csr_local_unit_details INTO l_cfar_number;
446 CLOSE csr_local_unit_details;
447 pay_action_information_api.create_action_information
448 (p_action_information_id => l_action_info_id
449 , p_action_context_id => p_payroll_action_id
450 , p_action_context_type => 'PA'
451 , p_object_version_number => l_ovn
452 , p_effective_date => g_effective_date
453 , p_source_id => NULL
454 , p_source_text => NULL
455 , p_action_information_category => 'EMEA REPORT INFORMATION'
456 , p_action_information1 => 'PYSESSSA'
457 , p_action_information2 => 'LU'
458 , p_action_information3 => l_local_unit_id
459 , p_action_information4 => g_legal_employer_id
460 , p_action_information5 => l_cfar_number
461 );
462 END LOOP;
463 ELSE
464 FOR csr_legal IN csr_legal_employer(g_business_group_id) LOOP
465 l_legal_employer_id:=csr_legal.legal_employer_id;
466 /*Legal Emplooyer Details*/
467 OPEN csr_legal_employer_details(g_business_group_id,l_legal_employer_id);
468 FETCH csr_legal_employer_details INTO l_legal_employer,l_organization_id,
469 l_membership_number;
470 CLOSE csr_legal_employer_details;
471 /* Salary Structure EIT Details */
472 OPEN csr_salary_structure_details(g_business_group_id,g_legal_employer_id);
473 FETCH csr_salary_structure_details INTO l_worksite_number,l_association_number,
474 l_legal_agreement_code,l_weekend_duty;
475 CLOSE csr_salary_structure_details;
476 pay_action_information_api.create_action_information
477 (p_action_information_id => l_action_info_id
478 , p_action_context_id => p_payroll_action_id
479 , p_action_context_type => 'PA'
480 , p_object_version_number => l_ovn
481 , p_effective_date => g_effective_date
482 , p_source_id => NULL
483 , p_source_text => NULL
484 , p_action_information_category => 'EMEA REPORT INFORMATION'
485 , p_action_information1 => 'PYSESSSA'
486 , p_action_information2 => 'LE'
487 , p_action_information3 => l_legal_employer_id
488 , p_action_information4 => l_legal_employer
489 , p_action_information5 => l_organization_id
490 , p_action_information6 => l_membership_number
491 , p_action_information7 => l_worksite_number
492 , p_action_information8 => l_association_number
493 , p_action_information9 => l_legal_agreement_code
494 , p_action_information10 => l_weekend_duty
495 );
496
497 FOR csr_legal_employer IN csr_local_legal_employer(g_business_group_id,l_legal_employer_id) LOOP
498 l_local_unit_id:=csr_legal_employer.local_unit;
499 OPEN csr_local_unit_details (l_local_unit_id);
500 FETCH csr_local_unit_details INTO l_cfar_number;
501 CLOSE csr_local_unit_details;
502 pay_action_information_api.create_action_information
503 (p_action_information_id => l_action_info_id
504 , p_action_context_id => p_payroll_action_id
505 , p_action_context_type => 'PA'
506 , p_object_version_number => l_ovn
507 , p_effective_date => g_effective_date
508 , p_source_id => NULL
509 , p_source_text => NULL
510 , p_action_information_category => 'EMEA REPORT INFORMATION'
511 , p_action_information1 => 'PYSESSSA'
512 , p_action_information2 => 'LU'
513 , p_action_information3 => l_local_unit_id
514 , p_action_information4 => l_legal_employer_id
515 , p_action_information5 => l_cfar_number
516 );
517 END LOOP;
518 END LOOP;
519 END IF;
520
521
522
523 -- END IF;
524 --END IF;
525 IF g_debug
526 THEN
527 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
528 END IF;
529 EXCEPTION
530 WHEN OTHERS
531 THEN
532 -- Return cursor that selects no rows
533 p_sql :=
534 'select 1 from dual where to_char(:payroll_action_id) = dummy';
535 END range_code;
536
537 /* ASSIGNMENT ACTION CODE */
538 PROCEDURE assignment_action_code (
539 p_payroll_action_id IN NUMBER
540 , p_start_person IN NUMBER
541 , p_end_person IN NUMBER
542 , p_chunk IN NUMBER
543 )
544 IS
545
546
547 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
548 IS
549 SELECT ue.creator_id
550 FROM ff_user_entities ue,
551 ff_database_items di
552 WHERE di.user_name = csr_v_Balance_Name
553 AND ue.user_entity_id = di.user_entity_id
554 AND ue.legislation_code = 'SE'
555 AND ue.business_group_id is NULL
556 AND ue.creator_type = 'B';
557
558
559 CURSOR csr_Local_unit_Legal(csr_v_legal_unit_id
563 FROM hr_organization_units o1
560 hr_organization_units.organization_id%TYPE)
561 IS
562 SELECT hoi2.ORG_INFORMATION1 local_unit_id
564 , hr_organization_information hoi1
565 , hr_organization_information hoi2
566 WHERE o1.business_group_id =g_business_group_id
567 AND hoi1.organization_id = o1.organization_id
568 AND hoi1.organization_id = csr_v_legal_unit_id
569 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
570 AND hoi1.org_information_context = 'CLASS'
571 AND o1.organization_id =hoi2.organization_id
572 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';
573
574
575 /* Local Unit Details */
576 CURSOR csr_local_unit_details (csr_v_local_unit_id hr_organization_information.organization_id%TYPE)
577 IS
578 SELECT --o1.NAME local_unit_name,
579 hoi2.org_information2 cfar_number
580 FROM hr_organization_units o1
581 , hr_organization_information hoi1
582 , hr_organization_information hoi2
583 WHERE o1.business_group_id = g_business_group_id
584 AND hoi1.organization_id = o1.organization_id
585 AND hoi1.organization_id = csr_v_local_unit_id
586 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
587 AND hoi1.org_information_context = 'CLASS'
588 AND o1.organization_id = hoi2.organization_id
589 AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
590
591 /*Salary Structure EIT Details */
592 CURSOR csr_salary_structure_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
593 csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
594 IS
595 SELECT hoi2.org_information1 Worksite_Number,
596 hoi2.org_information2 Association_Number,
597 hl.meaning Agreement_Code,
598 hoi2.org_information4 Weekend_duty_pay
599 FROM hr_organization_units o1
600 , hr_organization_information hoi1
601 , hr_organization_information hoi2
602 ,hr_lookups hl
603 WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
604 AND hoi1.organization_id = o1.organization_id
605 AND hoi1.organization_id = csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
606 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
607 AND hoi1.org_information_context = 'CLASS'
608 AND o1.organization_id =hoi2.organization_id
609 AND hoi2.ORG_INFORMATION_CONTEXT='SE_SALARY_STRUCTURE'
610 AND hl.lookup_type(+) ='SE_AGREEMENT_CODE'
611 AND hl.LOOKUP_CODE(+)=hoi2.org_information3 ;
612
613 /* Legal Employers under the Business Group */
614 CURSOR csr_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE)
615 IS
616 SELECT o1.organization_id legal_employer_id
617 FROM hr_organization_units o1
618 , hr_organization_information hoi1
619 WHERE o1.business_group_id =csr_v_business_group_id --3133
620 AND hoi1.organization_id = o1.organization_id
621 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
622 AND hoi1.org_information_context = 'CLASS';
623
624 /* Legal Employer Details */
625 CURSOR csr_legal_employer_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
626 csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
627 IS
628 SELECT o1.name legal_employer,
629 hoi2.org_information2 Organization_Id,
630 hoi2.org_information9 Membership_Number
631 FROM hr_organization_units o1
632 , hr_organization_information hoi1
633 , hr_organization_information hoi2
634 WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
635 AND hoi1.organization_id = o1.organization_id
636 AND hoi1.organization_id = csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
637 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
638 AND hoi1.org_information_context = 'CLASS'
639 AND o1.organization_id =hoi2.organization_id
640 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS';
641
642
643 /*Local Units under the Legal Employer*/
644 CURSOR csr_local_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
645 csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
646 IS
647 SELECT hoi2.ORG_INFORMATION1 local_unit
648 FROM hr_organization_units o1
649 , hr_organization_information hoi1
650 , hr_organization_information hoi2
651 WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
652 AND hoi1.organization_id = o1.organization_id
653 AND hoi1.organization_id = csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
654 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
655 AND hoi1.org_information_context = 'CLASS'
656 AND o1.organization_id =hoi2.organization_id
657 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';
658
659 /* Assignment level Extra information EIT Details */
660 CURSOR csr_extra_assignment(csr_v_assignment_id per_all_assignments_f.assignment_id%type)
661 IS
662 SELECT hl.meaning
663 FROM per_assignment_extra_info,
664 hr_lookups hl
665 WHERE assignment_id = csr_v_assignment_id --32516 --p_assignment_id
666 AND information_type = 'SE_SALARY_STRUCTURE'
667 AND hl.LOOKUP_TYPE='SE_WORKING_HOUR_TYPE'
668 AND hl.lookup_code=aei_information1;
669
670 /* Payroll period type */
671 CURSOR csr_payroll_period(csr_v_payroll_id pay_payrolls_f.payroll_id%type)
672 IS
673 SELECT period_type
674 FROM pay_payrolls_f
675 WHERE payroll_id=csr_v_payroll_id;
676
680 IS
677 /*Balances under the Balance Category*/
678 CURSOR csr_balances(csr_v_business_group_id hr_organization_units.business_group_id%type,
679 csr_v_category_name pay_balance_categories.category_name%type)
681 SELECT balance_type_id FRoM
682 pay_balance_types pbt
683 WHERE (legislation_code='SE'
684 OR business_group_id=csr_v_business_group_id)
685 --AND pbt.balance_name LIKE 'Steering%'
686 AND pbt.BALANCE_CATEGORY_ID=(SELECT
687 BALANCE_CATEGORY_ID FROM
688 PAY_BALANCE_CATEGORIES_F WHERE
689 legislation_code='SE'
690 AND category_name=csr_v_category_name/*'001- Hours worked (September)'*/);
691
692 /*Defined Balances for the balances with peson level YTD dimension */
693 CURSOR csr_defined_balances(csr_v_balance_type_id pay_balance_types.balance_type_id%TYPE)
694 IS
695 SELECT pdb.defined_balance_id
696 FROM pay_balance_types pbt,
697 pay_defined_balances pdb
698 WHERE pbt.balance_type_id=pdb.balance_type_id
699 AND pbt.balance_type_id=csr_v_balance_type_id --10506678
700 AND pdb.BALANCE_DIMENSION_ID=(SELECT
701 balance_dimension_id FROM
702 pay_balance_dimensions WHERE
703 legislation_code='SE'
704 AND DATABASE_ITEM_SUFFIX='_PER_MONTH' );
705 /* changing _PER_YTD to _PER_MONTH for bug fix 6209364 */
706
707 /*Assignment level details for the report */
708 CURSOR csr_person_local_unit(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
709 csr_v_local_unit_id hr_organization_units.organization_id%TYPE, csr_v_effective_date date)
710 IS
711 SELECT papf.person_id person_id,
712 papf.national_identifier person_number,
713 paaf.assignment_id,
714 paaf.employee_category,
715 /*nvl(substr(trim(hsck.SEGMENT3),1,4),'0000') ssyk_code,
716 nvl(substr(trim(hsck.SEGMENT3),5,2),'00') association_code,*/
717 hsck.SEGMENT3 ssyk_code,
718 hsck.SEGMENT3 association_code,
719 hsck.segment14 agreement_code,
720 paaf.payroll_id
721 FROM per_all_assignments_f paaf,
722 per_all_people_f papf,
723 hr_soft_coding_keyflex hsck
724 WHERE papf.business_group_id=csr_v_business_group_id -- 3133 --paaf.assignment_id = p_assignment_id
725 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
726 AND papf.person_id=paaf.person_id
727 and papf.person_id between p_start_person and p_end_person
728 AND paaf.primary_flag='Y'
729 AND hsck.segment2=to_char(csr_v_local_unit_id) --3268)
730 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
731 AND paaf.effective_end_date
732 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
733 AND papf.effective_end_date
734 AND months_between (csr_v_effective_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
735 AND months_between (csr_v_effective_date,DATE_OF_BIRTH) < 780 /* Age less than 65 */
736 AND papf.CURRENT_EMPLOYEE_FLAG='Y'
737 AND paaf.payroll_id IS NOT NULL
738 ORDER BY papf.person_id;
739
740 CURSOR csr_lookup_values(csr_v_lookup_type hr_lookups.lookup_type%TYPE,
741 csr_v_lookup_code hr_lookups.lookup_code%TYPE)
742 IS
743 SELECT meaning FROM hr_lookups WHERE
744 lookup_type =csr_v_lookup_type --'SE_AGREEMENT_CODE'
745 AND LOOKUP_CODE=csr_v_lookup_code ;
746
747
748
749 l_ovn NUMBER;
750 l_action_info_id NUMBER;
751 L_MONTH_START_DATE DATE;
752 L_MONTH_END_DATE DATE;
753 L_LOCAL_UNIT_NAME VARCHAR2(50);
754 l_sex CHAR(1);
755 l_local_unit_id NUMBER;
756 l_assignment_category VARCHAR2(5);
757 l_assignment_start_date DATE;
758 l_assignment_end_date DATE;
759 l_absence_count NUMBER;
760 l_employee_category per_all_assignments_f.employee_category%type;
761 l_person_number per_all_people_f.national_identifier%TYPE;
762 l_person_name VARCHAR2(350);
763
764 l_terminated VARCHAR2(50);
765
766 l_gross_salary number;
767 --l_start_date date;
768 --l_end_date date;
769 l_termination_date date;
770 lr_Get_Defined_Balance_Id pay_defined_balances.defined_balance_id%type;
771 l_value number;
772 L_CFAR_NUMBER NUMBER;
773 l_legal_employer_id hr_organization_units.organization_id%TYPE;
774 l_virtual_date DATE;
775 l_date_birth per_all_people_f.DATE_OF_BIRTH%TYPE;
776 l_counter NUMBER :=0;
777 l_total_salary NUMBER;
778 l_asg_start_date DATE;
779 l_asg_end_date date;
780 l_category per_all_assignments_f.employee_category%type;
781 l_prev_category per_all_assignments_f.employee_category%type;
782 l_working_percentage NUMBER;
783 l_asg_hour_sal per_all_assignments_f.hourly_salaried_code%type;
784 l_frequency per_all_assignments_f.frequency%type;
785 l_normal_hours per_all_assignments_f.normal_hours%type;
786 l_include_event char(1);
787 l_wrk_schd_return NUMBER;
788 l_wrk_duration NUMBER;
789 l_absence_start_date DATE;
790 l_absence_end_date DATE;
791 l_type varchar2(50);
792
793 l_valid_person number;
794 l_check_insert number;
795 l_worksite_number varchar2(10);
796 l_association_number varchar2(10);
797 l_legal_agreement_code varchar2(10);
798 l_agreement_code hr_lookups.meaning%TYPE;
799 l_agreement hr_lookups.lookup_code%TYPE;
800 l_asg_agreement_code hr_lookups.meaning%TYPE;
801 l_weekend_duty varchar2(10);
802 l_legal_employer hr_organization_units.name%TYPE;
803 l_organization_id varchar2(20);
804 l_membership_number varchar2(10);
805 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
806 l_person_id per_all_people_f.person_id%TYPE;
807 l_ssyk hr_lookups.lookup_code%TYPE;
808 l_ssyk_code hr_lookups.meaning%TYPE;
809 l_association_code varchar2(10);
810 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
811 l_work_type varchar2(250);
812 l_salary_type Number(1);
813 l_payroll_type pay_payrolls_f.payroll_type%TYPE;
814
815
816 l_001_steering_code NUMBER:=0;
817 l_002_steering_code NUMBER:=0;
818 l_003_steering_code NUMBER:=0;
819 l_004_steering_code NUMBER:=0;
820 l_051_steering_code NUMBER:=0;
821 l_052_steering_code NUMBER:=0;
822 l_053_steering_code NUMBER:=0;
823 l_054_steering_code NUMBER:=0;
824 l_055_steering_code NUMBER:=0;
825 l_056_steering_code NUMBER:=0;
826 l_058_steering_code NUMBER:=0;
827 l_600_steering_code NUMBER:=0;
828 l_601_steering_code NUMBER:=0;
829 l_810_steering_code NUMBER:=0;
830 l_800_steering_code NUMBER:=0;
831 l_801_steering_code NUMBER:=0;
832 l_802_steering_code NUMBER:=0;
833 l_803_steering_code NUMBER:=0;
834 l_804_steering_code NUMBER:=0;
835 l_805_steering_code NUMBER:=0;
836 l_806_steering_code NUMBER:=0;
837 l_808_steering_code NUMBER:=0;
838
839
840
841 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
842 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
843
844 TYPE emp_cat_type
845 IS TABLE OF VARCHAR2(10)
846 INDEX BY BINARY_INTEGER;
847 emp_cat emp_cat_type;
848
849 TYPE emp_job_record IS RECORD
850 (
851 job VARCHAR2(5),
852 end_date date
853 );
854 TYPE emp_job_type
855 IS TABLE OF emp_job_record
856 INDEX BY BINARY_INTEGER;
857 emp_job emp_job_type;
858
859 TYPE emp_detail_record IS RECORD
860 (
861 l_start_date date,
862 l_end_date date,
863 l_category varchar2(5),
864 l_job varchar2(5),
865 l_gross_salary number(17,2),
866 l_termination varchar2(5),
867 l_white_from date
868 );
869 TYPE emp_record_type
870 IS TABLE OF emp_detail_record
871 INDEX BY BINARY_INTEGER;
872 emp_record emp_record_type;
873 --------------
874
875 BEGIN
876 IF g_debug
877 THEN
878 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
879 END IF;
880 --The codes used in the package
881 /*
882 001- Hours worked (September)
883 002-Paid Overtime (September)
884 003-Working Hours per week
885 004-Working Hours week-full
886 051-Monthly, weekly pay
887 052-Over-time allowance
888 053-Comp. for Over-time
889 054-Comp. for shift work
890 055-comp. duty, relief work
891 056-Comp. for danger,dirt
892 058-Incentive pay (bonus)
893 600-Holiday days Entitlement
894 601-Salary for hours worked
895 810-Job Status
896 800-Comp. type of shift
897 801-Comp. post and position
898 802-Comp. for calving
899 803-Comp. for delegation
900 804-Comp. County, Municipal
901 805-Comp. for Per Diem
902 806-Comp. for Travel Expenses
903 808-Comp. for Official Duty
904 */
905
906 g_payroll_action_id := p_payroll_action_id;
907 g_business_group_id := NULL;
908 g_effective_date := NULL;
909 g_LE_request :=null;
910 g_legal_employer_id := NULL;
911 get_all_parameters (p_payroll_action_id
912 , g_business_group_id
913 , g_effective_date
914 , g_legal_employer_id
915 , g_LE_request
916 , g_month
917 , g_report_year
918 );
919 fnd_file.put_line(fnd_file.LOG,'g_business_group_id'||g_business_group_id);
920 fnd_file.put_line(fnd_file.LOG,'g_effective_date'||g_effective_date);
921 fnd_file.put_line(fnd_file.LOG,'g_legal_employer_id'||g_legal_employer_id);
922 fnd_file.put_line(fnd_file.LOG,'g_LE_request'||g_LE_request);
923 fnd_file.put_line(fnd_file.LOG,'g_month'||g_month);
924 fnd_file.put_line(fnd_file.LOG,'g_report_year'||g_report_year);
925 -- g_start_date:=to_date('01-' || g_month || '-' || g_year, 'dd-mm-yyyy');
926 --g_end_date:=to_date('31-'|| g_month || '-' || g_year, 'dd-mm-yyyy');
927 g_end_date:=last_day(to_date('01-09'|| '-' || g_report_year, 'dd-mm-yyyy'));
928 fnd_file.put_line(fnd_file.LOG,'g_end_date'||g_end_date);
929
930 IF g_LE_request ='LE_SELECTED' THEN
931 FOR csr_legal_employer IN csr_local_legal_employer(g_business_group_id,g_legal_employer_id) LOOP
932 l_local_unit_id:=csr_legal_employer.local_unit;
933 /* Salary Structure EIT Details */
934 OPEN csr_salary_structure_details(g_business_group_id,g_legal_employer_id);
935 FETCH csr_salary_structure_details INTO l_worksite_number,l_association_number,
936 l_legal_agreement_code,l_weekend_duty;
937 CLOSE csr_salary_structure_details;
938 FOR csr_person IN csr_person_local_unit(g_business_group_id,l_local_unit_id,g_end_date)LOOP
939 l_person_id:=csr_person.person_id;
940 l_person_number:=csr_person.person_number;
941 l_assignment_id:=csr_person.assignment_id;
942 l_employee_category:=csr_person.employee_category;
943 l_ssyk:=csr_person.ssyk_code;
944 --l_association_code:=csr_person.association_code;
945 l_payroll_id:=csr_person.payroll_id;
946 l_agreement:=csr_person.agreement_code;
947 OPEN csr_lookup_values('SE_SKILLS_LEVEL_CODE',l_ssyk);
948 FETCH csr_lookup_values INTO l_ssyk_code;
949 l_association_code:=nvl(substr(trim(l_ssyk_code),5,2),'00');
950 l_ssyk_code:=nvl(substr(trim(l_ssyk_code),1,4),'0000');
951 CLOSE csr_lookup_values;
952 OPEN csr_lookup_values('SE_AGREEMENT_CODE',l_agreement);
953 FETCH csr_lookup_values INTO l_asg_agreement_code;
954 CLOSE csr_lookup_values;
955 l_agreement_code:=nvl(l_asg_agreement_code,l_legal_agreement_code);
956 OPEN csr_payroll_period(l_payroll_id);
957 FETCH csr_payroll_period INTO l_payroll_type;
958 CLOSE csr_payroll_period;
959 IF l_payroll_type='Calendar Month' THEN
960 l_salary_type:=1;
961 ELSIF l_payroll_type='Week' THEN
962 l_salary_type:=2;
963 END IF;
964 fnd_file.put_line(fnd_file.LOG,'l_person_number'||l_person_number);
965 fnd_file.put_line(fnd_file.LOG,'l_assignment_id'||l_assignment_id);
966 fnd_file.put_line(fnd_file.LOG,'l_local_unit_id'||l_local_unit_id);
967 OPEN csr_extra_assignment(l_assignment_id);
968 FETCH csr_extra_assignment INTO l_work_type;
969 CLOSE csr_extra_assignment;
970 pay_balance_pkg.set_context('ASSIGNMENT_ID',l_assignment_id); --133942);
971 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id); --3621);
972
973 FOR csr_balance IN csr_balances(g_business_group_id,'001- Hours worked (September)') LOOP
974 fnd_file.put_line(fnd_file.LOG,'steering code 001');
975 l_balance_type_id:=csr_balance.balance_type_id;
976 fnd_file.put_line(fnd_file.LOG,'l_balance_type_id'||l_balance_type_id);
977 OPEN csr_defined_balances(l_balance_type_id);
978 FETCH csr_defined_balances INTO l_defined_balance_id;
979 CLOSE csr_defined_balances;
980 fnd_file.put_line(fnd_file.LOG,'l_defined_balance_id'||l_defined_balance_id);
981 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
982 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
983 P_VIRTUAL_DATE=>g_end_date),0);
984 fnd_file.put_line(fnd_file.LOG,'l_value'||l_value);
985 l_001_steering_code:=l_001_steering_code+l_value;
986 fnd_file.put_line(fnd_file.LOG,'l_001_steering_code'||l_001_steering_code);
987 END LOOP;
988 FOR csr_balance IN csr_balances(g_business_group_id,'002-Paid Overtime (September)') LOOP
989 fnd_file.put_line(fnd_file.LOG,'steering code 002');
990 l_balance_type_id:=csr_balance.balance_type_id;
991 OPEN csr_defined_balances(l_balance_type_id);
992 FETCH csr_defined_balances INTO l_defined_balance_id;
993 CLOSE csr_defined_balances;
994 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
995 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
996 P_VIRTUAL_DATE=>g_end_date),0);
997 l_002_steering_code:=l_002_steering_code+l_value;
998 END LOOP;
999 FOR csr_balance IN csr_balances(g_business_group_id,'003-Working Hours per week') LOOP
1000 fnd_file.put_line(fnd_file.LOG,'steering code 003');
1001 l_balance_type_id:=csr_balance.balance_type_id;
1002 OPEN csr_defined_balances(l_balance_type_id);
1003 FETCH csr_defined_balances INTO l_defined_balance_id;
1004 CLOSE csr_defined_balances;
1005 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1006 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1007 P_VIRTUAL_DATE=>g_end_date),0);
1008 l_003_steering_code:=l_003_steering_code+l_value;
1009 END LOOP;
1010 FOR csr_balance IN csr_balances(g_business_group_id,'004-Working Hours week-full') LOOP
1011 fnd_file.put_line(fnd_file.LOG,'steering code 004');
1012 l_balance_type_id:=csr_balance.balance_type_id;
1013 OPEN csr_defined_balances(l_balance_type_id);
1014 FETCH csr_defined_balances INTO l_defined_balance_id;
1015 CLOSE csr_defined_balances;
1016 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1017 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1018 P_VIRTUAL_DATE=>g_end_date),0);
1019 l_004_steering_code:=l_004_steering_code+l_value;
1020 END LOOP;
1021 FOR csr_balance IN csr_balances(g_business_group_id,'051-Monthly, weekly pay') LOOP
1022 fnd_file.put_line(fnd_file.LOG,'steering code 051');
1023 l_balance_type_id:=csr_balance.balance_type_id;
1024 OPEN csr_defined_balances(l_balance_type_id);
1025 FETCH csr_defined_balances INTO l_defined_balance_id;
1026 CLOSE csr_defined_balances;
1027 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1028 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1029 P_VIRTUAL_DATE=>g_end_date),0);
1030 l_051_steering_code:=l_051_steering_code+l_value;
1031 END LOOP;
1032 FOR csr_balance IN csr_balances(g_business_group_id,'052-Over-time allowance') LOOP
1033 fnd_file.put_line(fnd_file.LOG,'steering code 052');
1034 l_balance_type_id:=csr_balance.balance_type_id;
1035 OPEN csr_defined_balances(l_balance_type_id);
1036 FETCH csr_defined_balances INTO l_defined_balance_id;
1037 CLOSE csr_defined_balances;
1038 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1039 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1040 P_VIRTUAL_DATE=>g_end_date),0);
1041 l_052_steering_code:=l_052_steering_code+l_value;
1042 END LOOP;
1043 FOR csr_balance IN csr_balances(g_business_group_id,'053-Comp. for Over-time') LOOP
1044 fnd_file.put_line(fnd_file.LOG,'steering code 053');
1045 l_balance_type_id:=csr_balance.balance_type_id;
1046 OPEN csr_defined_balances(l_balance_type_id);
1047 FETCH csr_defined_balances INTO l_defined_balance_id;
1048 CLOSE csr_defined_balances;
1049 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1050 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1051 P_VIRTUAL_DATE=>g_end_date),0);
1052 l_053_steering_code:=l_053_steering_code+l_value;
1053 END LOOP;
1054 FOR csr_balance IN csr_balances(g_business_group_id,'054-Comp. for shift work') LOOP
1055 fnd_file.put_line(fnd_file.LOG,'steering code 054');
1056 l_balance_type_id:=csr_balance.balance_type_id;
1057 OPEN csr_defined_balances(l_balance_type_id);
1058 FETCH csr_defined_balances INTO l_defined_balance_id;
1059 CLOSE csr_defined_balances;
1060 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1061 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1062 P_VIRTUAL_DATE=>g_end_date),0);
1063 l_054_steering_code:=l_054_steering_code+l_value;
1064 END LOOP;
1065 FOR csr_balance IN csr_balances(g_business_group_id,'055-comp. duty, relief work') LOOP
1066 fnd_file.put_line(fnd_file.LOG,'steering code 055');
1067 l_balance_type_id:=csr_balance.balance_type_id;
1068 OPEN csr_defined_balances(l_balance_type_id);
1069 FETCH csr_defined_balances INTO l_defined_balance_id;
1070 CLOSE csr_defined_balances;
1071 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1072 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1073 P_VIRTUAL_DATE=>g_end_date),0);
1074 l_055_steering_code:=l_055_steering_code+l_value;
1075 END LOOP;
1076 FOR csr_balance IN csr_balances(g_business_group_id,'056-Comp. for danger,dirt') LOOP
1077 fnd_file.put_line(fnd_file.LOG,'steering code 056');
1078 l_balance_type_id:=csr_balance.balance_type_id;
1079 OPEN csr_defined_balances(l_balance_type_id);
1080 FETCH csr_defined_balances INTO l_defined_balance_id;
1081 CLOSE csr_defined_balances;
1082 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1083 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1084 P_VIRTUAL_DATE=>g_end_date),0);
1085 l_056_steering_code:=l_056_steering_code+l_value;
1086 END LOOP;
1087 FOR csr_balance IN csr_balances(g_business_group_id,'058-Incentive pay (bonus)') LOOP
1088 fnd_file.put_line(fnd_file.LOG,'steering code 058');
1089 l_balance_type_id:=csr_balance.balance_type_id;
1090 OPEN csr_defined_balances(l_balance_type_id);
1091 FETCH csr_defined_balances INTO l_defined_balance_id;
1092 CLOSE csr_defined_balances;
1093 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1094 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1095 P_VIRTUAL_DATE=>g_end_date),0);
1096 l_058_steering_code:=l_058_steering_code+l_value;
1097 END LOOP;
1098 FOR csr_balance IN csr_balances(g_business_group_id,'600-Holiday days Entitlement') LOOP
1099 fnd_file.put_line(fnd_file.LOG,'steering code 600');
1100 l_balance_type_id:=csr_balance.balance_type_id;
1101 OPEN csr_defined_balances(l_balance_type_id);
1102 FETCH csr_defined_balances INTO l_defined_balance_id;
1103 CLOSE csr_defined_balances;
1104 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1105 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1106 P_VIRTUAL_DATE=>g_end_date),0);
1107 l_600_steering_code:=l_600_steering_code+l_value;
1108 END LOOP;
1109 FOR csr_balance IN csr_balances(g_business_group_id,'601-Salary for hours worked') LOOP
1110 fnd_file.put_line(fnd_file.LOG,'steering code 601');
1111 l_balance_type_id:=csr_balance.balance_type_id;
1112 OPEN csr_defined_balances(l_balance_type_id);
1113 FETCH csr_defined_balances INTO l_defined_balance_id;
1114 CLOSE csr_defined_balances;
1115 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1116 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1117 P_VIRTUAL_DATE=>g_end_date),0);
1118 l_601_steering_code:=l_601_steering_code+l_value;
1119 END LOOP;
1120 FOR csr_balance IN csr_balances(g_business_group_id,'810-Job Status') LOOP
1121 fnd_file.put_line(fnd_file.LOG,'steering code 810');
1122 l_balance_type_id:=csr_balance.balance_type_id;
1123 OPEN csr_defined_balances(l_balance_type_id);
1124 FETCH csr_defined_balances INTO l_defined_balance_id;
1125 CLOSE csr_defined_balances;
1126 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1127 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1128 P_VIRTUAL_DATE=>g_end_date),0);
1129 l_810_steering_code:=l_810_steering_code+l_value;
1130 END LOOP;
1131 FOR csr_balance IN csr_balances(g_business_group_id,'800-Comp. type of shift') LOOP
1132 fnd_file.put_line(fnd_file.LOG,'steering code 800');
1133 l_balance_type_id:=csr_balance.balance_type_id;
1134 OPEN csr_defined_balances(l_balance_type_id);
1135 FETCH csr_defined_balances INTO l_defined_balance_id;
1136 CLOSE csr_defined_balances;
1137 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1138 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1139 P_VIRTUAL_DATE=>g_end_date),0);
1140 l_800_steering_code:=l_800_steering_code+l_value;
1141 END LOOP;
1142 FOR csr_balance IN csr_balances(g_business_group_id,'801-Comp. post and position') LOOP
1143 fnd_file.put_line(fnd_file.LOG,'steering code 801');
1144 l_balance_type_id:=csr_balance.balance_type_id;
1145 OPEN csr_defined_balances(l_balance_type_id);
1146 FETCH csr_defined_balances INTO l_defined_balance_id;
1147 CLOSE csr_defined_balances;
1148 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1149 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1150 P_VIRTUAL_DATE=>g_end_date),0);
1151 l_801_steering_code:=l_801_steering_code+l_value;
1152 END LOOP;
1153 FOR csr_balance IN csr_balances(g_business_group_id,'802-Comp. for calving') LOOP
1154 fnd_file.put_line(fnd_file.LOG,'steering code 802');
1155 l_balance_type_id:=csr_balance.balance_type_id;
1156 OPEN csr_defined_balances(l_balance_type_id);
1157 FETCH csr_defined_balances INTO l_defined_balance_id;
1158 CLOSE csr_defined_balances;
1159 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1160 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1161 P_VIRTUAL_DATE=>g_end_date),0);
1162 l_802_steering_code:=l_802_steering_code+l_value;
1163 END LOOP;
1164 FOR csr_balance IN csr_balances(g_business_group_id,'803-Comp. for delegation') LOOP
1165 fnd_file.put_line(fnd_file.LOG,'steering code 803');
1166 l_balance_type_id:=csr_balance.balance_type_id;
1167 OPEN csr_defined_balances(l_balance_type_id);
1168 FETCH csr_defined_balances INTO l_defined_balance_id;
1169 CLOSE csr_defined_balances;
1170 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1171 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1172 P_VIRTUAL_DATE=>g_end_date),0);
1173 l_803_steering_code:=l_803_steering_code+l_value;
1174 END LOOP;
1175 FOR csr_balance IN csr_balances(g_business_group_id,'804-Comp. County, Municipal') LOOP
1176 fnd_file.put_line(fnd_file.LOG,'steering code 804');
1177 l_balance_type_id:=csr_balance.balance_type_id;
1178 OPEN csr_defined_balances(l_balance_type_id);
1179 FETCH csr_defined_balances INTO l_defined_balance_id;
1180 CLOSE csr_defined_balances;
1181 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1182 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1183 P_VIRTUAL_DATE=>g_end_date),0);
1184 l_804_steering_code:=l_804_steering_code+l_value;
1185 END LOOP;
1186 FOR csr_balance IN csr_balances(g_business_group_id,'805-Comp. for Per Diem') LOOP
1187 fnd_file.put_line(fnd_file.LOG,'steering code 805');
1188 l_balance_type_id:=csr_balance.balance_type_id;
1189 OPEN csr_defined_balances(l_balance_type_id);
1190 FETCH csr_defined_balances INTO l_defined_balance_id;
1191 CLOSE csr_defined_balances;
1192 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1193 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1194 P_VIRTUAL_DATE=>g_end_date),0);
1195 l_805_steering_code:=l_805_steering_code+l_value;
1196 END LOOP;
1197 FOR csr_balance IN csr_balances(g_business_group_id,'806-Comp. for Travel Expenses') LOOP
1198 fnd_file.put_line(fnd_file.LOG,'steering code 806');
1199 l_balance_type_id:=csr_balance.balance_type_id;
1200 OPEN csr_defined_balances(l_balance_type_id);
1201 FETCH csr_defined_balances INTO l_defined_balance_id;
1202 CLOSE csr_defined_balances;
1203 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1204 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1205 P_VIRTUAL_DATE=>g_end_date),0);
1206 l_806_steering_code:=l_806_steering_code+l_value;
1207 END LOOP;
1208 FOR csr_balance IN csr_balances(g_business_group_id,'808-Comp. for Official Duty') LOOP
1209 fnd_file.put_line(fnd_file.LOG,'steering code 808');
1210 l_balance_type_id:=csr_balance.balance_type_id;
1211 OPEN csr_defined_balances(l_balance_type_id);
1212 FETCH csr_defined_balances INTO l_defined_balance_id;
1213 CLOSE csr_defined_balances;
1214 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1215 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1216 P_VIRTUAL_DATE=>g_end_date),0);
1217 l_808_steering_code:=l_808_steering_code+l_value;
1218 END LOOP;
1219 fnd_file.put_line(fnd_file.LOG,'Before first insertion');
1220 pay_action_information_api.create_action_information
1221 (p_action_information_id => l_action_info_id
1222 , p_action_context_id => p_payroll_action_id
1223 , p_action_context_type => 'PA'
1224 , p_object_version_number => l_ovn
1225 , p_effective_date => g_effective_date
1226 , p_source_id => NULL
1227 , p_source_text => NULL
1228 , p_action_information_category => 'EMEA REPORT INFORMATION'
1229 , p_action_information1 => 'PYSESSSA'
1230 , p_action_information2 => 'PER1'
1231 , p_action_information3 => l_person_id
1232 , p_action_information4 => l_person_number
1233 , p_action_information5 => l_local_unit_id
1234 , p_action_information6 => l_cfar_number
1235 , p_action_information7 => l_employee_category
1236 , p_action_information8 => l_work_type
1237 , p_action_information9 => l_agreement_code
1238 , p_action_information10 => l_ssyk_code
1239 , p_action_information11 => l_association_code
1240 , p_action_information12 => l_salary_type
1241 , p_action_information13 => l_001_steering_code
1242 , p_action_information14 => l_002_steering_code
1243 , p_action_information15 => l_003_steering_code
1244 , p_action_information16 => l_004_steering_code
1245 , p_action_information17 => l_051_steering_code
1246 , p_action_information18 => l_052_steering_code
1247 , p_action_information19 => l_053_steering_code
1248 , p_action_information20 => l_054_steering_code
1249 , p_action_information21 => l_055_steering_code
1250 , p_action_information22 => l_056_steering_code
1251 , p_action_information23 => l_058_steering_code
1252 , p_action_information24 => l_600_steering_code
1253 , p_action_information25 => l_601_steering_code
1254 , p_action_information26 => l_810_steering_code
1255 );
1256 fnd_file.put_line(fnd_file.LOG,'Before second insertion');
1257 pay_action_information_api.create_action_information
1258 (p_action_information_id => l_action_info_id
1259 , p_action_context_id => p_payroll_action_id
1260 , p_action_context_type => 'PA'
1261 , p_object_version_number => l_ovn
1262 , p_effective_date => g_effective_date
1263 , p_source_id => NULL
1264 , p_source_text => NULL
1265 , p_action_information_category => 'EMEA REPORT INFORMATION'
1266 , p_action_information1 => 'PYSESSSA'
1267 , p_action_information2 => 'PER2'
1268 , p_action_information3 => l_person_id
1269 , p_action_information4 => l_local_unit_id
1270 , p_action_information5 => l_800_steering_code
1271 , p_action_information6 => l_801_steering_code
1272 , p_action_information7 => l_802_steering_code
1273 , p_action_information8 => l_803_steering_code
1274 , p_action_information9 => l_804_steering_code
1275 , p_action_information10 => l_805_steering_code
1276 , p_action_information11 => l_806_steering_code
1277 , p_action_information12 => l_808_steering_code
1278 );
1279 l_001_steering_code:=0;
1280 l_002_steering_code:=0;
1281 l_003_steering_code:=0;
1282 l_004_steering_code:=0;
1283 l_051_steering_code:=0;
1284 l_052_steering_code:=0;
1285 l_053_steering_code:=0;
1286 l_054_steering_code:=0;
1287 l_055_steering_code:=0;
1288 l_056_steering_code:=0;
1289 l_058_steering_code:=0;
1290 l_600_steering_code:=0;
1291 l_601_steering_code:=0;
1292 l_810_steering_code:=0;
1293 l_800_steering_code:=0;
1294 l_801_steering_code:=0;
1295 l_802_steering_code:=0;
1296 l_803_steering_code:=0;
1297 l_804_steering_code:=0;
1298 l_805_steering_code:=0;
1299 l_806_steering_code:=0;
1300 l_808_steering_code:=0;
1301
1302 END LOOP;
1303
1304
1305 END LOOP;
1306 ELSE
1307 FOR csr_legal IN csr_legal_employer(g_business_group_id) LOOP
1308 l_legal_employer_id:=csr_legal.legal_employer_id;
1309 /* Salary Structure EIT Details */
1310 OPEN csr_salary_structure_details(g_business_group_id,g_legal_employer_id);
1311 FETCH csr_salary_structure_details INTO l_worksite_number,l_association_number,
1312 l_legal_agreement_code,l_weekend_duty;
1313 CLOSE csr_salary_structure_details;
1314 FOR csr_legal_employer IN csr_local_legal_employer(g_business_group_id,l_legal_employer_id) LOOP
1315 l_local_unit_id:=csr_legal_employer.local_unit;
1316 FOR csr_person IN csr_person_local_unit(g_business_group_id,l_local_unit_id,g_end_date)LOOP
1317 l_person_id:=csr_person.person_id;
1318 l_person_number:=csr_person.person_number;
1319 l_assignment_id:=csr_person.assignment_id;
1320 l_employee_category:=csr_person.employee_category;
1321 l_ssyk:=csr_person.ssyk_code;
1322 l_association_code:=csr_person.association_code;
1323 l_payroll_id:=csr_person.payroll_id;
1324 l_agreement:=csr_person.agreement_code;
1325 OPEN csr_lookup_values('SE_SKILLS_LEVEL_CODE',l_ssyk);
1326 FETCH csr_lookup_values INTO l_ssyk_code;
1327 l_association_code:=nvl(substr(trim(l_ssyk_code),5,2),'00');
1328 l_ssyk_code:=nvl(substr(trim(l_ssyk_code),1,4),'0000');
1329 CLOSE csr_lookup_values;
1330 OPEN csr_lookup_values('SE_AGREEMENT_CODE',l_agreement);
1331 FETCH csr_lookup_values INTO l_asg_agreement_code;
1332 CLOSE csr_lookup_values;
1333 l_agreement_code:=nvl(csr_person.agreement_code,l_legal_agreement_code);
1334 OPEN csr_payroll_period(l_payroll_id);
1335 FETCH csr_payroll_period INTO l_payroll_type;
1336 CLOSE csr_payroll_period;
1337 IF l_payroll_type='Calendar Month' THEN
1338 l_salary_type:=1;
1339 ELSIF l_payroll_type='Week' THEN
1340 l_salary_type:=2;
1341 END IF;
1342 fnd_file.put_line(fnd_file.LOG,'l_person_number'||l_person_number);
1343 fnd_file.put_line(fnd_file.LOG,'l_assignment_id'||l_assignment_id);
1344 fnd_file.put_line(fnd_file.LOG,'l_local_unit_id'||l_local_unit_id);
1345 OPEN csr_extra_assignment(l_assignment_id);
1346 FETCH csr_extra_assignment INTO l_work_type;
1347 CLOSE csr_extra_assignment;
1348 pay_balance_pkg.set_context('ASSIGNMENT_ID',l_assignment_id); --133942);
1349 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id); --3621);
1350
1351 FOR csr_balance IN csr_balances(g_business_group_id,'001- Hours worked (September)') LOOP
1352 fnd_file.put_line(fnd_file.LOG,'steering code 001');
1353 l_balance_type_id:=csr_balance.balance_type_id;
1354 fnd_file.put_line(fnd_file.LOG,'l_balance_type_id'||l_balance_type_id);
1355 OPEN csr_defined_balances(l_balance_type_id);
1356 FETCH csr_defined_balances INTO l_defined_balance_id;
1357 CLOSE csr_defined_balances;
1358 fnd_file.put_line(fnd_file.LOG,'l_defined_balance_id'||l_defined_balance_id);
1359 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1360 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1361 P_VIRTUAL_DATE=>g_end_date),0);
1362 fnd_file.put_line(fnd_file.LOG,'l_value'||l_value);
1363 l_001_steering_code:=l_001_steering_code+l_value;
1364 fnd_file.put_line(fnd_file.LOG,'l_001_steering_code'||l_001_steering_code);
1365 END LOOP;
1366 FOR csr_balance IN csr_balances(g_business_group_id,'002-Paid Overtime (September)') LOOP
1367 fnd_file.put_line(fnd_file.LOG,'steering code 002');
1368 l_balance_type_id:=csr_balance.balance_type_id;
1369 OPEN csr_defined_balances(l_balance_type_id);
1370 FETCH csr_defined_balances INTO l_defined_balance_id;
1371 CLOSE csr_defined_balances;
1372 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1373 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1374 P_VIRTUAL_DATE=>g_end_date),0);
1375 l_002_steering_code:=l_002_steering_code+l_value;
1376 END LOOP;
1377 FOR csr_balance IN csr_balances(g_business_group_id,'003-Working Hours per week') LOOP
1378 fnd_file.put_line(fnd_file.LOG,'steering code 003');
1379 l_balance_type_id:=csr_balance.balance_type_id;
1380 OPEN csr_defined_balances(l_balance_type_id);
1381 FETCH csr_defined_balances INTO l_defined_balance_id;
1382 CLOSE csr_defined_balances;
1383 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1384 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1385 P_VIRTUAL_DATE=>g_end_date),0);
1386 l_003_steering_code:=l_003_steering_code+l_value;
1387 END LOOP;
1388 FOR csr_balance IN csr_balances(g_business_group_id,'004-Working Hours week-full') LOOP
1389 fnd_file.put_line(fnd_file.LOG,'steering code 004');
1390 l_balance_type_id:=csr_balance.balance_type_id;
1391 OPEN csr_defined_balances(l_balance_type_id);
1392 FETCH csr_defined_balances INTO l_defined_balance_id;
1393 CLOSE csr_defined_balances;
1394 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1395 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1396 P_VIRTUAL_DATE=>g_end_date),0);
1397 l_004_steering_code:=l_004_steering_code+l_value;
1398 END LOOP;
1399 FOR csr_balance IN csr_balances(g_business_group_id,'051-Monthly, weekly pay') LOOP
1400 fnd_file.put_line(fnd_file.LOG,'steering code 051');
1401 l_balance_type_id:=csr_balance.balance_type_id;
1402 OPEN csr_defined_balances(l_balance_type_id);
1403 FETCH csr_defined_balances INTO l_defined_balance_id;
1404 CLOSE csr_defined_balances;
1405 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1406 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1407 P_VIRTUAL_DATE=>g_end_date),0);
1408 l_051_steering_code:=l_051_steering_code+l_value;
1409 END LOOP;
1410 FOR csr_balance IN csr_balances(g_business_group_id,'052-Over-time allowance') LOOP
1411 fnd_file.put_line(fnd_file.LOG,'steering code 052');
1412 l_balance_type_id:=csr_balance.balance_type_id;
1413 OPEN csr_defined_balances(l_balance_type_id);
1414 FETCH csr_defined_balances INTO l_defined_balance_id;
1415 CLOSE csr_defined_balances;
1416 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1417 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1418 P_VIRTUAL_DATE=>g_end_date),0);
1419 l_052_steering_code:=l_052_steering_code+l_value;
1420 END LOOP;
1421 FOR csr_balance IN csr_balances(g_business_group_id,'053-Comp. for Over-time') LOOP
1422 fnd_file.put_line(fnd_file.LOG,'steering code 053');
1423 l_balance_type_id:=csr_balance.balance_type_id;
1424 OPEN csr_defined_balances(l_balance_type_id);
1425 FETCH csr_defined_balances INTO l_defined_balance_id;
1426 CLOSE csr_defined_balances;
1427 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1428 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1429 P_VIRTUAL_DATE=>g_end_date),0);
1430 l_053_steering_code:=l_053_steering_code+l_value;
1431 END LOOP;
1432 FOR csr_balance IN csr_balances(g_business_group_id,'054-Comp. for shift work') LOOP
1433 fnd_file.put_line(fnd_file.LOG,'steering code 054');
1434 l_balance_type_id:=csr_balance.balance_type_id;
1435 OPEN csr_defined_balances(l_balance_type_id);
1436 FETCH csr_defined_balances INTO l_defined_balance_id;
1437 CLOSE csr_defined_balances;
1438 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1439 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1440 P_VIRTUAL_DATE=>g_end_date),0);
1441 l_054_steering_code:=l_054_steering_code+l_value;
1442 END LOOP;
1443 FOR csr_balance IN csr_balances(g_business_group_id,'055-comp. duty, relief work') LOOP
1444 fnd_file.put_line(fnd_file.LOG,'steering code 055');
1445 l_balance_type_id:=csr_balance.balance_type_id;
1446 OPEN csr_defined_balances(l_balance_type_id);
1447 FETCH csr_defined_balances INTO l_defined_balance_id;
1448 CLOSE csr_defined_balances;
1449 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1450 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1451 P_VIRTUAL_DATE=>g_end_date),0);
1452 l_055_steering_code:=l_055_steering_code+l_value;
1453 END LOOP;
1454 FOR csr_balance IN csr_balances(g_business_group_id,'056-Comp. for danger,dirt') LOOP
1455 fnd_file.put_line(fnd_file.LOG,'steering code 056');
1456 l_balance_type_id:=csr_balance.balance_type_id;
1457 OPEN csr_defined_balances(l_balance_type_id);
1458 FETCH csr_defined_balances INTO l_defined_balance_id;
1459 CLOSE csr_defined_balances;
1460 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1461 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1462 P_VIRTUAL_DATE=>g_end_date),0);
1463 l_056_steering_code:=l_056_steering_code+l_value;
1464 END LOOP;
1465 FOR csr_balance IN csr_balances(g_business_group_id,'058-Incentive pay (bonus)') LOOP
1466 fnd_file.put_line(fnd_file.LOG,'steering code 058');
1467 l_balance_type_id:=csr_balance.balance_type_id;
1468 OPEN csr_defined_balances(l_balance_type_id);
1469 FETCH csr_defined_balances INTO l_defined_balance_id;
1470 CLOSE csr_defined_balances;
1471 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1472 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1473 P_VIRTUAL_DATE=>g_end_date),0);
1474 l_058_steering_code:=l_058_steering_code+l_value;
1475 END LOOP;
1476 FOR csr_balance IN csr_balances(g_business_group_id,'600-Holiday days Entitlement') LOOP
1477 fnd_file.put_line(fnd_file.LOG,'steering code 600');
1478 l_balance_type_id:=csr_balance.balance_type_id;
1479 OPEN csr_defined_balances(l_balance_type_id);
1480 FETCH csr_defined_balances INTO l_defined_balance_id;
1481 CLOSE csr_defined_balances;
1482 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1483 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1484 P_VIRTUAL_DATE=>g_end_date),0);
1485 l_600_steering_code:=l_600_steering_code+l_value;
1486 END LOOP;
1487 FOR csr_balance IN csr_balances(g_business_group_id,'601-Salary for hours worked') LOOP
1488 fnd_file.put_line(fnd_file.LOG,'steering code 601');
1489 l_balance_type_id:=csr_balance.balance_type_id;
1490 OPEN csr_defined_balances(l_balance_type_id);
1491 FETCH csr_defined_balances INTO l_defined_balance_id;
1492 CLOSE csr_defined_balances;
1493 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1494 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1495 P_VIRTUAL_DATE=>g_end_date),0);
1496 l_601_steering_code:=l_601_steering_code+l_value;
1497 END LOOP;
1498 FOR csr_balance IN csr_balances(g_business_group_id,'810-Job Status') LOOP
1499 fnd_file.put_line(fnd_file.LOG,'steering code 810');
1500 l_balance_type_id:=csr_balance.balance_type_id;
1501 OPEN csr_defined_balances(l_balance_type_id);
1502 FETCH csr_defined_balances INTO l_defined_balance_id;
1503 CLOSE csr_defined_balances;
1504 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1505 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1506 P_VIRTUAL_DATE=>g_end_date),0);
1507 l_810_steering_code:=l_810_steering_code+l_value;
1508 END LOOP;
1509 FOR csr_balance IN csr_balances(g_business_group_id,'800-Comp. type of shift') LOOP
1510 fnd_file.put_line(fnd_file.LOG,'steering code 800');
1511 l_balance_type_id:=csr_balance.balance_type_id;
1512 OPEN csr_defined_balances(l_balance_type_id);
1513 FETCH csr_defined_balances INTO l_defined_balance_id;
1514 CLOSE csr_defined_balances;
1515 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1516 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1517 P_VIRTUAL_DATE=>g_end_date),0);
1518 l_800_steering_code:=l_800_steering_code+l_value;
1519 END LOOP;
1520 FOR csr_balance IN csr_balances(g_business_group_id,'801-Comp. post and position') LOOP
1521 fnd_file.put_line(fnd_file.LOG,'steering code 801');
1522 l_balance_type_id:=csr_balance.balance_type_id;
1523 OPEN csr_defined_balances(l_balance_type_id);
1524 FETCH csr_defined_balances INTO l_defined_balance_id;
1525 CLOSE csr_defined_balances;
1526 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1527 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1528 P_VIRTUAL_DATE=>g_end_date),0);
1529 l_801_steering_code:=l_801_steering_code+l_value;
1530 END LOOP;
1531 FOR csr_balance IN csr_balances(g_business_group_id,'802-Comp. for calving') LOOP
1532 fnd_file.put_line(fnd_file.LOG,'steering code 802');
1533 l_balance_type_id:=csr_balance.balance_type_id;
1534 OPEN csr_defined_balances(l_balance_type_id);
1535 FETCH csr_defined_balances INTO l_defined_balance_id;
1536 CLOSE csr_defined_balances;
1537 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1538 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1539 P_VIRTUAL_DATE=>g_end_date),0);
1540 l_802_steering_code:=l_802_steering_code+l_value;
1541 END LOOP;
1542 FOR csr_balance IN csr_balances(g_business_group_id,'803-Comp. for delegation') LOOP
1543 fnd_file.put_line(fnd_file.LOG,'steering code 803');
1544 l_balance_type_id:=csr_balance.balance_type_id;
1545 OPEN csr_defined_balances(l_balance_type_id);
1546 FETCH csr_defined_balances INTO l_defined_balance_id;
1547 CLOSE csr_defined_balances;
1548 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1549 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1550 P_VIRTUAL_DATE=>g_end_date),0);
1551 l_803_steering_code:=l_803_steering_code+l_value;
1552 END LOOP;
1553 FOR csr_balance IN csr_balances(g_business_group_id,'804-Comp. County, Municipal') LOOP
1554 fnd_file.put_line(fnd_file.LOG,'steering code 804');
1555 l_balance_type_id:=csr_balance.balance_type_id;
1556 OPEN csr_defined_balances(l_balance_type_id);
1557 FETCH csr_defined_balances INTO l_defined_balance_id;
1558 CLOSE csr_defined_balances;
1559 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1560 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1561 P_VIRTUAL_DATE=>g_end_date),0);
1562 l_804_steering_code:=l_804_steering_code+l_value;
1563 END LOOP;
1564 FOR csr_balance IN csr_balances(g_business_group_id,'805-Comp. for Per Diem') LOOP
1565 fnd_file.put_line(fnd_file.LOG,'steering code 805');
1566 l_balance_type_id:=csr_balance.balance_type_id;
1567 OPEN csr_defined_balances(l_balance_type_id);
1568 FETCH csr_defined_balances INTO l_defined_balance_id;
1569 CLOSE csr_defined_balances;
1570 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1571 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1572 P_VIRTUAL_DATE=>g_end_date),0);
1573 l_805_steering_code:=l_805_steering_code+l_value;
1574 END LOOP;
1575 FOR csr_balance IN csr_balances(g_business_group_id,'806-Comp. for Travel Expenses') LOOP
1576 fnd_file.put_line(fnd_file.LOG,'steering code 806');
1577 l_balance_type_id:=csr_balance.balance_type_id;
1578 OPEN csr_defined_balances(l_balance_type_id);
1579 FETCH csr_defined_balances INTO l_defined_balance_id;
1580 CLOSE csr_defined_balances;
1581 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1582 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1583 P_VIRTUAL_DATE=>g_end_date),0);
1584 l_806_steering_code:=l_806_steering_code+l_value;
1585 END LOOP;
1586 FOR csr_balance IN csr_balances(g_business_group_id,'808-Comp. for Official Duty') LOOP
1587 fnd_file.put_line(fnd_file.LOG,'steering code 808');
1588 l_balance_type_id:=csr_balance.balance_type_id;
1589 OPEN csr_defined_balances(l_balance_type_id);
1590 FETCH csr_defined_balances INTO l_defined_balance_id;
1591 CLOSE csr_defined_balances;
1592 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1593 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1594 P_VIRTUAL_DATE=>g_end_date),0);
1595 l_808_steering_code:=l_808_steering_code+l_value;
1596 END LOOP;
1597 fnd_file.put_line(fnd_file.LOG,'Before first insertion');
1598 pay_action_information_api.create_action_information
1599 (p_action_information_id => l_action_info_id
1600 , p_action_context_id => p_payroll_action_id
1601 , p_action_context_type => 'PA'
1602 , p_object_version_number => l_ovn
1603 , p_effective_date => g_effective_date
1604 , p_source_id => NULL
1605 , p_source_text => NULL
1606 , p_action_information_category => 'EMEA REPORT INFORMATION'
1607 , p_action_information1 => 'PYSESSSA'
1608 , p_action_information2 => 'PER1'
1609 , p_action_information3 => l_person_id
1610 , p_action_information4 => l_person_number
1611 , p_action_information5 => l_local_unit_id
1612 , p_action_information6 => l_cfar_number
1613 , p_action_information7 => l_employee_category
1614 , p_action_information8 => l_work_type
1615 , p_action_information9 => l_agreement_code
1616 , p_action_information10 => l_ssyk_code
1617 , p_action_information11 => l_association_code
1618 , p_action_information12 => l_salary_type
1619 , p_action_information13 => l_001_steering_code
1620 , p_action_information14 => l_002_steering_code
1621 , p_action_information15 => l_003_steering_code
1622 , p_action_information16 => l_004_steering_code
1623 , p_action_information17 => l_051_steering_code
1624 , p_action_information18 => l_052_steering_code
1625 , p_action_information19 => l_053_steering_code
1626 , p_action_information20 => l_054_steering_code
1627 , p_action_information21 => l_055_steering_code
1628 , p_action_information22 => l_056_steering_code
1629 , p_action_information23 => l_058_steering_code
1630 , p_action_information24 => l_600_steering_code
1631 , p_action_information25 => l_601_steering_code
1632 , p_action_information26 => l_810_steering_code
1633 );
1634 fnd_file.put_line(fnd_file.LOG,'Before second insertion');
1635 pay_action_information_api.create_action_information
1636 (p_action_information_id => l_action_info_id
1637 , p_action_context_id => p_payroll_action_id
1638 , p_action_context_type => 'PA'
1639 , p_object_version_number => l_ovn
1640 , p_effective_date => g_effective_date
1641 , p_source_id => NULL
1642 , p_source_text => NULL
1643 , p_action_information_category => 'EMEA REPORT INFORMATION'
1644 , p_action_information1 => 'PYSESSSA'
1645 , p_action_information2 => 'PER2'
1646 , p_action_information3 => l_person_id
1647 , p_action_information4 => l_local_unit_id
1648 , p_action_information5 => l_800_steering_code
1649 , p_action_information6 => l_801_steering_code
1650 , p_action_information7 => l_802_steering_code
1651 , p_action_information8 => l_803_steering_code
1652 , p_action_information9 => l_804_steering_code
1653 , p_action_information10 => l_805_steering_code
1654 , p_action_information11 => l_806_steering_code
1655 , p_action_information12 => l_808_steering_code
1656 );
1657 l_001_steering_code:=0;
1658 l_002_steering_code:=0;
1659 l_003_steering_code:=0;
1660 l_004_steering_code:=0;
1661 l_051_steering_code:=0;
1662 l_052_steering_code:=0;
1663 l_053_steering_code:=0;
1664 l_054_steering_code:=0;
1665 l_055_steering_code:=0;
1666 l_056_steering_code:=0;
1667 l_058_steering_code:=0;
1668 l_600_steering_code:=0;
1669 l_601_steering_code:=0;
1670 l_810_steering_code:=0;
1671 l_800_steering_code:=0;
1672 l_801_steering_code:=0;
1673 l_802_steering_code:=0;
1674 l_803_steering_code:=0;
1675 l_804_steering_code:=0;
1676 l_805_steering_code:=0;
1677 l_806_steering_code:=0;
1678 l_808_steering_code:=0;
1679
1680 END LOOP;
1681
1682
1683 END LOOP;
1684
1685 END LOOP;
1686
1687 END IF;
1688
1689 /*EXCEPTION
1690 WHEN OTHERS
1691 THEN
1692 IF g_debug
1693 THEN
1694 hr_utility.set_location ('error raised assignment_action_code '
1695 , 5
1696 );
1697 END IF;
1698
1699 RAISE;*/
1700 END assignment_action_code;
1701
1702 /*fffffffffffffffffffffffffff*/
1703
1704 /* INITIALIZATION CODE */
1705 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
1706 IS
1707 l_action_info_id NUMBER;
1708 l_ovn NUMBER;
1709 l_count NUMBER := 0;
1710 l_business_group_id NUMBER;
1711 l_start_date VARCHAR2 (20);
1712 l_end_date VARCHAR2 (20);
1713 l_effective_date DATE;
1714 l_payroll_id NUMBER;
1715 l_consolidation_set NUMBER;
1716 l_prev_prepay NUMBER := 0;
1717 BEGIN
1718 IF g_debug
1719 THEN
1720 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE'
1721 , 80
1722 );
1723 END IF;
1724
1725
1726 g_payroll_action_id := p_payroll_action_id;
1727 g_business_group_id := NULL;
1728 g_effective_date := NULL;
1729 g_LE_request := NULL;
1730 g_legal_employer_id := NULL;
1731 g_local_unit_id := NULL;
1732 g_account_date :=null;
1733 g_posting_date :=null;
1734
1735
1736 IF g_debug
1737 THEN
1738 hr_utility.set_location (' Leaving Procedure INITIALIZATION_CODE'
1739 , 90
1740 );
1741 END IF;
1742 EXCEPTION
1743 WHEN OTHERS
1744 THEN
1745 g_err_num := SQLCODE;
1746
1747 IF g_debug
1748 THEN
1749 hr_utility.set_location ( 'ORA_ERR: '
1750 || g_err_num
1751 || 'In INITIALIZATION_CODE'
1752 , 180
1753 );
1754 END IF;
1755 END initialization_code;
1756
1757 /* GET DEFINED BALANCE ID */
1758 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
1759 RETURN NUMBER
1760 IS
1761 /* Cursor to retrieve Defined Balance Id */
1762 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1763 IS
1764 SELECT u.creator_id
1765 FROM ff_user_entities u, ff_database_items d
1766 WHERE d.user_name = p_user_name
1767 AND u.user_entity_id = d.user_entity_id
1768 AND (u.legislation_code = 'SE')
1769 AND (u.business_group_id IS NULL)
1770 AND u.creator_type = 'B';
1771
1772 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1773 BEGIN
1774 IF g_debug
1775 THEN
1776 hr_utility.set_location
1777 (' Entering Function GET_DEFINED_BALANCE_ID'
1778 , 240
1779 );
1780 END IF;
1781
1782 OPEN csr_def_bal_id (p_user_name);
1783
1784 FETCH csr_def_bal_id
1785 INTO l_defined_balance_id;
1786
1787 CLOSE csr_def_bal_id;
1788
1789 RETURN l_defined_balance_id;
1790
1791 IF g_debug
1792 THEN
1793 hr_utility.set_location (' Leaving Function GET_DEFINED_BALANCE_ID'
1794 , 250
1795 );
1796 END IF;
1797 END get_defined_balance_id;
1798
1799 FUNCTION get_defined_balance_value (
1800 p_user_name IN VARCHAR2
1801 , p_in_assignment_id IN NUMBER
1802 , p_in_virtual_date IN DATE
1803 )
1804 RETURN NUMBER
1805 IS
1806 /* Cursor to retrieve Defined Balance Id */
1807 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1808 IS
1809 SELECT u.creator_id
1810 FROM ff_user_entities u, ff_database_items d
1811 WHERE d.user_name = p_user_name
1812 AND u.user_entity_id = d.user_entity_id
1813 AND (u.legislation_code = 'SE')
1814 AND (u.business_group_id IS NULL)
1815 AND u.creator_type = 'B';
1816
1817 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1818 l_return_balance_value NUMBER;
1819 BEGIN
1820 IF g_debug
1821 THEN
1822 hr_utility.set_location
1823 (' Entering Function GET_DEFINED_BALANCE_VALUE'
1824 , 240
1825 );
1826 END IF;
1827
1828 OPEN csr_def_bal_id (p_user_name);
1829
1830 FETCH csr_def_bal_id
1831 INTO l_defined_balance_id;
1832
1833 CLOSE csr_def_bal_id;
1834
1835 l_return_balance_value :=
1836 TO_CHAR
1837 (pay_balance_pkg.get_value
1838 (p_defined_balance_id => l_defined_balance_id
1839 , p_assignment_id => p_in_assignment_id
1840 , p_virtual_date => p_in_virtual_date
1841 )
1842 , '999999999D99'
1843 );
1844 RETURN l_return_balance_value;
1845
1846 IF g_debug
1847 THEN
1848 hr_utility.set_location
1849 (' Leaving Function GET_DEFINED_BALANCE_VALUE'
1850 , 250
1851 );
1852 END IF;
1853 END get_defined_balance_value;
1854
1855 /* ARCHIVE CODE */
1856 PROCEDURE archive_code (
1857 p_assignment_action_id IN NUMBER
1858 , p_effective_date IN DATE
1859 )
1860 IS
1861 begin
1862
1863 IF g_debug
1864 THEN
1865 hr_utility.set_location (' Leaving Procedure ARCHIVE_CODE', 390);
1866 END IF;
1867 END archive_code;
1868
1869 --- Report XML generating code
1870 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
1871 IS
1872 l_xfdf_string CLOB;
1873 l_str1 VARCHAR2 (1000);
1874 l_str2 VARCHAR2 (20);
1875 l_str3 VARCHAR2 (20);
1876 l_str4 VARCHAR2 (20);
1877 l_str5 VARCHAR2 (20);
1878 l_str6 VARCHAR2 (30);
1879 l_str7 VARCHAR2 (1000);
1880 l_str8 VARCHAR2 (240);
1881 l_str9 VARCHAR2 (240);
1882 l_str10 VARCHAR2 (20);
1883 l_str11 VARCHAR2 (20);
1884 current_index PLS_INTEGER;
1885 l_iana_charset VARCHAR2 (50);
1886 BEGIN
1887 l_iana_charset := hr_se_utility.get_iana_charset;
1888
1889 -- hr_utility.set_location ('Entering WritetoCLOB ', 70);
1890 l_str1 :=
1891 '<?xml version="1.0" encoding="'
1892 || l_iana_charset
1893 || '"?> <ROOT><SSST>';
1894 l_str2 := '<';
1895 l_str3 := '>';
1896 l_str4 := '</';
1897 l_str5 := '>';
1898 l_str6 := '</SSST></ROOT>';
1899 l_str7 :=
1900 '<?xml version="1.0" encoding="'
1901 || l_iana_charset
1902 || '"?> <ROOT></ROOT>';
1903 l_str10 := '<SSST>';
1904 l_str11 := '</SSST>';
1905 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
1906 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
1907 current_index := 0;
1908
1909 IF xml_tab.COUNT > 0
1910 THEN
1911 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
1912
1913 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST
1914 LOOP
1915 l_str8 := xml_tab (table_counter).tagname;
1916 l_str9 := xml_tab (table_counter).tagvalue;
1917
1918
1919
1920 IF l_str9 IN
1921 (
1922 'SSST_DETAILS',
1923 'END_SSST_DETAILS'
1924 )
1925 THEN
1926 IF l_str9 IN
1927 ('SSST_DETAILS')
1928 THEN
1929 DBMS_LOB.writeappend (l_xfdf_string
1930 , LENGTH (l_str2)
1931 , l_str2
1932 );
1933 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1934 , l_str8);
1935 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1936 , l_str3);
1937 ELSE
1938 DBMS_LOB.writeappend (l_xfdf_string
1939 , LENGTH (l_str4)
1940 , l_str4
1941 );
1942 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1943 , l_str8);
1944 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1945 , l_str5);
1946 END IF;
1947 ELSE
1948 IF l_str9 IS NOT NULL
1949 THEN
1950 DBMS_LOB.writeappend (l_xfdf_string
1951 , LENGTH (l_str2)
1952 , l_str2
1953 );
1954 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1955 , l_str8);
1956 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1957 , l_str3);
1958 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9)
1959 , l_str9);
1960 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1961 , l_str4);
1962 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1963 , l_str8);
1964 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1965 , l_str5);
1966 ELSE
1967 DBMS_LOB.writeappend (l_xfdf_string
1968 , LENGTH (l_str2)
1969 , l_str2
1970 );
1971 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1972 , l_str8);
1973 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1974 , l_str3);
1975 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1976 , l_str4);
1977 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1978 , l_str8);
1979 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1980 , l_str5);
1981 END IF;
1982 END IF;
1983 END LOOP;
1984
1985 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
1986 ELSE
1987 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
1988 END IF;
1989 p_xfdf_clob := l_xfdf_string;
1990 -- hr_utility.set_location ('Leaving WritetoCLOB ', 40);
1991 EXCEPTION
1992 WHEN OTHERS
1993 THEN
1994 hr_utility.TRACE ('sqlerrm ' || SQLERRM);
1995 hr_utility.raise_error;
1996 END writetoclob;
1997
1998 -----------------------------------------------------------------------------------
1999 -- POPULATE_DATA_DETAIL generates xml for the reports.
2000 -----------------------------------------------------------------------------------
2001 --
2002 --
2003 PROCEDURE POPULATE_DATA_DETAIL
2004 (p_business_group_id IN NUMBER,
2005 p_payroll_action_id IN VARCHAR2 ,
2006 p_template_name IN VARCHAR2,
2007 p_xml OUT NOCOPY CLOB)
2008 IS
2009
2010
2011 /* Cursor to fetch Header Information */
2012
2013 l_employee_category hr_lookups.meaning%type;
2014 l_weekend_pay_duty hr_lookups.meaning%type;
2015 l_working_hour_code hr_lookups.lookup_code%type;
2016 l_lu_salary NUMBER;
2017 l_salary NUMBER:=0;
2018 l_grand_salary NUMBER:=0;
2019 l_legal_employer hr_organization_units.name%type;
2020 l_local_unit hr_organization_units.name%type;
2021 l_action_information_id pay_action_information.action_information_id%TYPE;
2022 l_month varchar2(20);
2023 l_year NUMBER;
2024 l_retroactive_date_from DATE;
2025 l_retroactive_date_to DATE;
2026 l_bh_worked_calendar_month NUMBER;
2027 l_bh_worked_payment_period NUMBER;
2028 l_bh_pbt_value NUMBER;
2029 l_bh_pcow_value NUMBER;
2030 l_bh_nha_value NUMBER;
2031 l_bh_nho_value NUMBER;
2032 l_bh_retroactive_pay NUMBER;
2033 l_bh_ppo_value NUMBER;
2034 l_bh_sick_pay NUMBER;
2035 l_bh_total_employees NUMBER;
2036 l_bs_gross_pay NUMBER;
2037 l_bs_working_agreement NUMBER;
2038 l_bs_tcdp_value NUMBER;
2039 l_bs_tcow_value NUMBER;
2040 l_bs_nha_value NUMBER;
2041 l_bs_nho_value NUMBER;
2042 l_bs_retroactive_pay NUMBER;
2043 l_bs_ppo_value NUMBER;
2044 l_bs_sick_pay NUMBER;
2045 l_bs_total_employees NUMBER;
2046 l_ws_full_time_employee NUMBER;
2047 l_ws_gross_pay NUMBER;
2048 l_ws_working_agreement NUMBER;
2049 l_ws_tcdp_value NUMBER;
2050 l_ws_tcow_value NUMBER;
2051 l_ws_nha_value NUMBER;
2052 l_ws_nho_value NUMBER;
2053 l_ws_retroactive_pay NUMBER;
2054 l_ws_ppo_value NUMBER;
2055 l_ws_sick_pay NUMBER;
2056 l_ws_total_employees NUMBER;
2057 l_wh_full_time_employee NUMBER;
2058 l_wh_gross_pay NUMBER;
2059 l_wh_working_agreement NUMBER;
2060 l_wh_tcdp_value NUMBER;
2061 l_wh_retroactive_pay NUMBER;
2062 l_wh_ppo_value NUMBER;
2063 l_wh_sick_pay NUMBER;
2064 l_wh_total_employees NUMBER;
2065
2066
2067
2068
2069
2070 CURSOR csr_report_year(csr_v_payroll_action_id number)
2071 IS
2072 SELECT pai.action_information6 Report_Year
2073 FROM
2074 pay_action_information pai,
2075 pay_payroll_actions ppa
2076 WHERE
2077 ppa.payroll_action_id=csr_v_payroll_action_id--175110 --csr_v_payroll_action_id
2078 AND ppa.payroll_action_id=pai.action_context_id
2079 AND pai.action_context_type = 'PA'
2080 AND pai.action_information1 = 'PYSESSSA'
2081 AND pai.action_information_category = 'EMEA REPORT DETAILS';
2082
2083 CURSOR csr_salary_statistics(csr_v_payroll_action_id number)
2084 IS
2085 SELECT pai1.action_information3 legal_employer_id,
2086 pai2.action_information3 local_unit_id,
2087 pai3.action_information3 person_id,
2088 pai1.action_information6 Membership_Number,
2089 pai1.action_information7 Working_Site_Number,
2090 pai1.action_information5 Organization_Id,
2091 pai1.action_information8 Association_Number,
2092 pai3.action_information9 Agreement_Code,
2093 pai3.action_information4 Person_Number,
2094 pai3.action_information7 Employee_Category,
2095 pai3.action_information8 Working_Hours_Type,
2096 pai3.action_information10 SSYK_Code,
2097 pai3.action_information11 Association_Code,
2098 pai3.action_information12 Salary_Type,
2099 pai2.action_information5 CFAR_Number,
2100 pai1.action_information10 Weekend_Pay_Duty,
2101 decode(nvl(pai3.action_information13,0) ,0,'0000000',pai3.action_information13) Steering_Code_001,
2102 decode(nvl(pai3.action_information14,0) ,0,'0000000',pai3.action_information14) Steering_Code_002,
2103 decode(nvl(pai3.action_information15,0) ,0,'0000000',pai3.action_information15) Steering_Code_003,
2104 decode(nvl(pai3.action_information16,0) ,0,'0000000',pai3.action_information16) Steering_Code_004,
2105 decode(nvl(pai3.action_information17,0) ,0,'0000000',pai3.action_information17) Steering_Code_051,
2106 decode(nvl(pai3.action_information18,0) ,0,'0000000',pai3.action_information18) Steering_Code_052,
2107 decode(nvl(pai3.action_information19,0) ,0,'0000000',pai3.action_information19) Steering_Code_053,
2108 decode(nvl(pai3.action_information20,0) ,0,'0000000',pai3.action_information20) Steering_Code_054,
2109 decode(nvl(pai3.action_information21,0) ,0,'0000000',pai3.action_information21) Steering_Code_055,
2110 decode(nvl(pai3.action_information22,0) ,0,'0000000',pai3.action_information22) Steering_Code_056,
2111 decode(nvl(pai3.action_information23,0) ,0,'0000000',pai3.action_information23) Steering_Code_058,
2112 decode(nvl(pai3.action_information24,0) ,0,'0000000',pai3.action_information24) Steering_Code_600,
2113 decode(nvl(pai3.action_information25,0) ,0,'0000000',pai3.action_information25) Steering_Code_601,
2114 decode(nvl(pai3.action_information26,0) ,0,'0000000',pai3.action_information26) Steering_Code_810,
2115 decode(nvl(pai4.action_information5,0) ,0,'0000000',pai4.action_information5) Steering_Code_800,
2116 decode(nvl(pai4.action_information6,0) ,0,'0000000',pai4.action_information6) Steering_Code_801,
2117 decode(nvl(pai4.action_information7,0) ,0,'0000000',pai4.action_information7) Steering_Code_802,
2118 decode(nvl(pai4.action_information8,0) ,0,'0000000',pai4.action_information8) Steering_Code_803,
2119 decode(nvl(pai4.action_information9,0) ,0,'0000000',pai4.action_information9) Steering_Code_804,
2120 decode(nvl(pai4.action_information10,0) ,0,'0000000',pai4.action_information10) Steering_Code_805,
2121 decode(nvl(pai4.action_information11,0) ,0,'0000000',pai4.action_information11) Steering_Code_806,
2122 decode(nvl(pai4.action_information12,0) ,0,'0000000',pai4.action_information12) Steering_Code_808
2123 FROM
2124 pay_action_information pai1,
2125 pay_action_information pai2,
2126 pay_action_information pai3,
2127 pay_action_information pai4,
2128 pay_payroll_actions ppa
2129 WHERE
2130 ppa.payroll_action_id=csr_v_payroll_action_id --175110 --175079 --175068 --csr_v_payroll_action_id
2131 AND ppa.payroll_action_id=pai1.action_context_id
2132 AND pai1.action_context_id=pai2.action_context_id
2133 AND pai2.action_context_id=pai3.action_context_id
2134 and pai3.action_context_id=pai4.action_context_id
2135 and pai4.action_context_id=ppa.payroll_action_id
2136 --AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
2137 AND pai1.action_context_type='PA'
2138 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
2139 AND pai1.action_information1 = 'PYSESSSA'
2140 --AND pai1.action_information_id=csr_v_action_information_id
2141 AND pai1.action_information2='LE'
2142 AND pai1.action_information3=pai2.action_information4
2143 AND pai2.action_context_type='PA'
2144 AND pai2.action_information2 = 'LU'
2145 AND pai2.action_information1 = 'PYSESSSA'
2146 AND pai2.action_information_category = 'EMEA REPORT INFORMATION'
2147 AND pai2.action_information3=pai3.action_information5
2148 AND pai3.action_context_type='PA'
2149 AND pai3.action_information2 = 'PER1'
2150 AND pai3.action_information1 = 'PYSESSSA'
2151 AND pai3.action_information_category = 'EMEA REPORT INFORMATION'
2152 AND pai2.action_information3=pai4.action_information4
2153 AND pai3.action_information5=pai4.action_information4
2154 AND pai3.action_information3=pai4.action_information3
2155 AND pai4.action_context_type='PA'
2156 AND pai4.action_information2 = 'PER2'
2157 AND pai4.action_information1 = 'PYSESSSA'
2158 AND pai4.action_information_category = 'EMEA REPORT INFORMATION'
2159 ORDER BY pai1.action_information3,
2160 pai2.action_information3,
2161 pai3.action_information3;
2162
2163 CURSOR csr_local_unit_count(csr_v_payroll_action_id number, csr_v_CFAR_Number VARCHAR2)
2164 IS
2165 SELECT nvl(COUNT(*),0) Local_Unit_Count
2166 FROM
2167 pay_action_information pai,
2168 pay_action_information pai1,
2169 pay_payroll_actions ppa
2170 WHERE
2171 ppa.payroll_action_id=csr_v_payroll_action_id --175110 --csr_v_payroll_action_id
2172 AND ppa.payroll_action_id=pai.action_context_id
2173 AND pai.action_context_id=pai1.action_context_id
2174 AND pai1.action_context_id=ppa.payroll_action_id
2175 AND pai.action_context_type = 'PA'
2176 AND pai.action_information1 = 'PYSESSSA'
2177 AND pai.action_information2 = 'LU'
2178 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
2179 AND pai.action_information5=csr_v_CFAR_Number --312
2180 AND pai.action_information3= pai1.action_information5
2181 AND pai1.action_context_type = 'PA'
2182 AND pai1.action_information1 = 'PYSESSSA'
2183 AND pai1.action_information2 = 'PER1'
2184 AND pai1.action_information_category = 'EMEA REPORT INFORMATION';
2185
2186 CURSOR csr_lookup_values(csr_v_lookup_type varchar2, csr_v_lookup_code varchar2)
2187 IS
2188 SELECT meaning FROM
2189 hr_lookups WHERE lookup_type =csr_v_lookup_type-- 'EMPLOYEE_CATG'
2190 AND lookup_code=csr_v_lookup_code; -- 'WC'
2191
2192 CURSOR csr_lookup_code(csr_v_lookup_type varchar2, csr_v_lookup_meaning varchar2)
2193 IS
2194 SELECT lookup_code FROM
2195 hr_lookups WHERE lookup_type =csr_v_lookup_type-- 'EMPLOYEE_CATG'
2196 AND meaning=csr_v_lookup_meaning; -- 'WC'
2197
2198
2199 l_counter NUMBER:=0;
2200 l_total NUMBER;
2201 l_total_eft NUMBER;
2202 l_count NUMBER;
2203 l_payroll_action_id NUMBER;
2204 l_lu_counter_reset VARCHAR2(10);
2205 l_prev_local_unit VARCHAR2(15);
2206 l_report_date DATE;
2207 l_person_number VARCHAR2(50);
2208 l_local_unit_id hr_organization_units.organization_id%type;
2209 l_period varchar2(50);
2210 l_report_year number(4);
2211 l_local_count NUMBER;
2212 l_CFAR_Number NUMBER;
2213
2214
2215 --l_local_unit hr_organization_units.name%TYPE;
2216
2217 BEGIN
2218
2219
2220 IF p_payroll_action_id IS NULL THEN
2221 BEGIN
2222 SELECT payroll_action_id
2223 INTO l_payroll_action_id
2224 FROM pay_payroll_actions ppa,
2225 fnd_conc_req_summary_v fcrs,
2226 fnd_conc_req_summary_v fcrs1
2227 WHERE fcrs.request_id = fnd_global.conc_request_id
2228 AND fcrs.priority_request_id = fcrs1.priority_request_id
2229 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
2230 AND ppa.request_id = fcrs1.request_id;
2231 EXCEPTION
2232 WHEN OTHERS THEN
2233 NULL;
2234 END ;
2235 ELSE
2236 l_payroll_action_id := p_payroll_action_id;
2237 END IF;
2238 g_payroll_action_id :=p_payroll_action_id;
2239 /* g_business_group_id := null;
2240 g_legal_employer_id := null;
2241 g_start_date := null;
2242 g_end_date := null;
2243 g_version := null;
2244 g_archive := null;*/
2245
2246 get_all_parameters (l_payroll_action_id
2247 , g_business_group_id
2248 , g_effective_date
2249 , g_legal_employer_id
2250 , g_LE_request
2251 , g_month
2252 , g_report_year
2253 );
2254
2255
2256 hr_utility.set_location('Entered Procedure GETDATA',10);
2257
2258 /* xml_tab(l_counter).TagName :='LU_DETAILS';
2259 xml_tab(l_counter).TagValue :='LU_DETAILS';*/
2260 /* l_counter:=l_counter+1;*/
2261
2262 /* Get the File Header Information */
2263 OPEN csr_report_year(l_payroll_action_id);
2264 FETCH csr_report_year INTO l_report_year;
2265 CLOSE csr_report_year;
2266
2267 hr_utility.set_location('Before populating pl/sql table',20);
2268 FOR csr_salary IN csr_salary_statistics(l_payroll_action_id) LOOP
2269
2270 xml_tab(l_counter).TagName :='SSST_DETAILS';
2271 xml_tab(l_counter).TagValue :='SSST_DETAILS';
2272 l_counter:=l_counter+1;
2273
2274 xml_tab(l_counter).TagName :='REPORT_YEAR';
2275 xml_tab(l_counter).TagValue :=l_report_year; --csr_cat.legal_employer;
2276 l_counter:=l_counter+1;
2277
2278 xml_tab(l_counter).TagName :='MEMBERSHIP_NUMBER';
2279 xml_tab(l_counter).TagValue :=csr_salary.Membership_Number; --csr_cat.local_unit;
2280 l_counter:=l_counter+1;
2281
2282 xml_tab(l_counter).TagName :='WORKING_SITE_NUMBER';
2283 xml_tab(l_counter).TagValue :=csr_salary.Working_Site_Number; --csr_cat.month;
2284 l_counter:=l_counter+1;
2285
2286 xml_tab(l_counter).TagName :='ORGANIZATION_ID';
2287 xml_tab(l_counter).TagValue :=csr_salary.Organization_Id; --csr_cat.retroactive_date_from;
2288 l_counter:=l_counter+1;
2289
2290 xml_tab(l_counter).TagName :='ASSOCIATION_NUMBER';
2291 xml_tab(l_counter).TagValue :=csr_salary.Association_Number; --csr_cat.retroactive_date_to;
2292 l_counter:=l_counter+1;
2293
2294 xml_tab(l_counter).TagName :='AGREEMENT_CODE';
2295 xml_tab(l_counter).TagValue :=csr_salary.Agreement_Code; --csr_cat.bh_worked_calendar_month;
2296 l_counter:=l_counter+1;
2297
2298 xml_tab(l_counter).TagName :='PERSON_NUMBER';
2299 xml_tab(l_counter).TagValue :=csr_salary.Person_Number; --csr_cat.bh_worked_payment_period;
2300 l_counter:=l_counter+1;
2301
2302 l_employee_category:=csr_salary.Employee_Category;
2303 xml_tab(l_counter).TagName :='EMPLOYEE_CATG';
2304
2305 IF l_employee_category='BC' THEN
2306 xml_tab(l_counter).TagValue :=1; --csr_salary.Employee_Category; --csr_cat.bh_pbt_value;
2307 ELSIF l_employee_category='WC' THEN
2308 xml_tab(l_counter).TagValue :=2;
2309 END IF;
2310 l_counter:=l_counter+1;
2311 OPEN csr_lookup_values('EMPLOYEE_CATG',l_employee_category );
2312 FETCH csr_lookup_values INTO l_employee_category;
2313 CLOSE csr_lookup_values;
2314
2315 xml_tab(l_counter).TagName :='EMPLOYEE_CATEGORY';
2316 xml_tab(l_counter).TagValue :=l_employee_category; --csr_salary.Employee_Category; --csr_cat.bh_pbt_value;
2317 l_counter:=l_counter+1;
2318
2319 l_working_hour_code:=csr_salary.Working_Hours_Type;
2320
2321 OPEN csr_lookup_code('SE_WORKING_HOUR_TYPE', l_working_hour_code);
2322 FETCH csr_lookup_code INTO l_working_hour_code;
2323 CLOSE csr_lookup_code;
2324
2325 xml_tab(l_counter).TagName :='WORKING_HOURS';
2326 xml_tab(l_counter).TagValue :=l_working_hour_code;-- csr_salary.Working_Hours_Type; --csr_cat.bh_pcow_value;
2327 l_counter:=l_counter+1;
2328
2329 xml_tab(l_counter).TagName :='WORKING_HOURS_TYPE';
2330 xml_tab(l_counter).TagValue :=csr_salary.Working_Hours_Type; --csr_cat.bh_pcow_value;
2331 l_counter:=l_counter+1;
2332
2333 xml_tab(l_counter).TagName :='SSYK_CODE';
2334 xml_tab(l_counter).TagValue :=csr_salary.SSYK_Code; --csr_cat.bh_nha_value;
2335 l_counter:=l_counter+1;
2336
2337 xml_tab(l_counter).TagName :='ASSOCIATION_CODE';
2338 xml_tab(l_counter).TagValue :=csr_salary.Association_Code; --csr_cat.bh_nho_value;
2339 l_counter:=l_counter+1;
2340
2341 xml_tab(l_counter).TagName :='SALARY_TYPE';
2342 xml_tab(l_counter).TagValue :=csr_salary.Salary_Type; --csr_cat.bh_retroactive_pay;
2343 l_counter:=l_counter+1;
2344
2345 l_CFAR_Number:=csr_salary.CFAR_Number;
2346
2347 open csr_local_unit_count(l_payroll_action_id, l_CFAR_Number);
2348 FETCH csr_local_unit_count INTO l_local_count;
2349 CLOSE csr_local_unit_count;
2350
2351 xml_tab(l_counter).TagName :='LOCAL_COUNT';
2352 xml_tab(l_counter).TagValue :=l_local_count; --csr_cat.bh_ppo_value;
2353 l_counter:=l_counter+1;
2354
2355 xml_tab(l_counter).TagName :='CFAR_NUMBER';
2356 xml_tab(l_counter).TagValue :=csr_salary.CFAR_Number; --csr_cat.bh_sick_pay;
2357 l_counter:=l_counter+1;
2358
2359 xml_tab(l_counter).TagName :='WEEKEND_PAY';
2360 IF csr_salary.Weekend_Pay_Duty='Y' THEN
2361 xml_tab(l_counter).TagValue :=1;-- csr_salary.Weekend_Pay_Duty; --csr_cat.bh_total_employees;
2362 ELSE
2363 xml_tab(l_counter).TagValue :=2;
2364 END IF;
2365 l_counter:=l_counter+1;
2366
2367 l_weekend_pay_duty:=csr_salary.Weekend_Pay_Duty;
2368
2369 OPEN csr_lookup_values('YES_NO',l_weekend_pay_duty );
2370 FETCH csr_lookup_values INTO l_weekend_pay_duty;
2371 CLOSE csr_lookup_values;
2372
2373 xml_tab(l_counter).TagName :='WEEKEND_PAY_DUTY';
2374 xml_tab(l_counter).TagValue :=l_weekend_pay_duty; --csr_salary.Weekend_Pay_Duty; --csr_cat.bh_total_employees;
2375 l_counter:=l_counter+1;
2376
2377 xml_tab(l_counter).TagName :='ZERO_FILLING';
2378 xml_tab(l_counter).TagValue :='000000';
2379 l_counter:=l_counter+1;
2380
2381 xml_tab(l_counter).TagName :='STEERING_CODE_001';
2382 xml_tab(l_counter).TagValue :='001'; --csr_cat.bs_gross_pay;
2383 l_counter:=l_counter+1;
2384
2385 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_001';
2386 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_001; --csr_cat.bs_gross_pay;
2387 l_counter:=l_counter+1;
2388
2389 xml_tab(l_counter).TagName :='STEERING_VALUE_001';
2390 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_001; --csr_cat.bs_gross_pay;
2391 l_counter:=l_counter+1;
2392
2393 xml_tab(l_counter).TagName :='STEERING_CODE_002';
2394 xml_tab(l_counter).TagValue :='002'; --csr_cat.bs_working_agreement;
2395 l_counter:=l_counter+1;
2396
2397 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_002';
2398 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_002; --csr_cat.bs_working_agreement;
2399 l_counter:=l_counter+1;
2400
2401 xml_tab(l_counter).TagName :='STEERING_VALUE_002';
2402 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_002; --csr_cat.bs_working_agreement;
2403 l_counter:=l_counter+1;
2404
2405 xml_tab(l_counter).TagName :='STEERING_CODE_003';
2406 xml_tab(l_counter).TagValue :='003'; --csr_cat.bs_tcdp_value;
2407 l_counter:=l_counter+1;
2408
2409 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_003';
2410 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_003; --csr_cat.bs_tcdp_value;
2411 l_counter:=l_counter+1;
2412
2413 xml_tab(l_counter).TagName :='STEERING_VALUE_003';
2414 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_003; --csr_cat.bs_tcdp_value;
2415 l_counter:=l_counter+1;
2416
2417 xml_tab(l_counter).TagName :='STEERING_CODE_004';
2418 xml_tab(l_counter).TagValue :='004';
2419 l_counter:=l_counter+1;
2420
2421 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_004';
2422 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_004; --csr_cat.bs_tcow_value;
2423 l_counter:=l_counter+1;
2424
2425 xml_tab(l_counter).TagName :='STEERING_VALUE_004';
2426 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_004; --csr_cat.bs_tcow_value;
2427 l_counter:=l_counter+1;
2428
2429 xml_tab(l_counter).TagName :='STEERING_CODE_051';
2430 xml_tab(l_counter).TagValue :='051'; --csr_cat.bs_nha_value;
2431 l_counter:=l_counter+1;
2432
2433 IF csr_salary.Salary_Type=3 THEN
2434 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_051';
2435 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_051; --csr_cat.bs_nha_value;
2436 l_counter:=l_counter+1;
2437
2438 xml_tab(l_counter).TagName :='STEERING_VALUE_051';
2439 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_051; --csr_cat.bs_nha_value;
2440 l_counter:=l_counter+1;
2441 ELSE
2442 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_051';
2443 IF csr_salary.Steering_Code_051 > 0 THEN
2444 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_051); --csr_cat.bs_nha_value;
2445 ELSE
2446 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_051;
2447 END IF;
2448 l_counter:=l_counter+1;
2449
2450 xml_tab(l_counter).TagName :='STEERING_VALUE_051';
2451 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_051); --csr_cat.bs_nha_value;
2452 l_counter:=l_counter+1;
2453 END IF;
2454
2455 xml_tab(l_counter).TagName :='STEERING_CODE_052';
2456 xml_tab(l_counter).TagValue :='052'; --csr_cat.bs_nho_value;
2457 l_counter:=l_counter+1;
2458
2459 IF csr_salary.Salary_Type=3 THEN
2460
2461 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_052';
2462 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_052; --csr_cat.bs_nho_value;
2463 l_counter:=l_counter+1;
2464
2465 xml_tab(l_counter).TagName :='STEERING_VALUE_052';
2466 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_052; --csr_cat.bs_nho_value;
2467 l_counter:=l_counter+1;
2468
2469 ELSE
2470 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_052';
2471 IF csr_salary.Steering_Code_052 > 0 THEN
2472 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_052); --csr_cat.bs_nho_value;
2473 ELSE
2474 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_052; --csr_cat.bs_nho_value;
2475 END IF;
2476 l_counter:=l_counter+1;
2477
2478 xml_tab(l_counter).TagName :='STEERING_VALUE_052';
2479 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_052); --csr_cat.bs_nho_value;
2480 l_counter:=l_counter+1;
2481 END IF;
2482
2483 xml_tab(l_counter).TagName :='STEERING_CODE_053';
2484 xml_tab(l_counter).TagValue :='053'; --csr_cat.bs_retroactive_pay;
2485 l_counter:=l_counter+1;
2486
2487 IF csr_salary.Salary_Type=3 THEN
2488
2489 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_053';
2490 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_053; --csr_cat.bs_retroactive_pay;
2491 l_counter:=l_counter+1;
2492
2493 xml_tab(l_counter).TagName :='STEERING_VALUE_053';
2494 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_053; --csr_cat.bs_retroactive_pay;
2495 l_counter:=l_counter+1;
2496
2497 ELSE
2498
2499 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_053';
2500 IF csr_salary.Steering_Code_053 > 0 THEN
2501 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_053); --csr_cat.bs_retroactive_pay;
2502 ELSE
2503 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_053; --csr_cat.bs_retroactive_pay;
2504 END IF;
2505 l_counter:=l_counter+1;
2506
2507 xml_tab(l_counter).TagName :='STEERING_VALUE_053';
2508 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_053); --csr_cat.bs_retroactive_pay;
2509 l_counter:=l_counter+1;
2510
2511 END IF;
2512
2513 xml_tab(l_counter).TagName :='STEERING_CODE_054';
2514 xml_tab(l_counter).TagValue :='054'; --csr_cat.bs_ppo_value;
2515 l_counter:=l_counter+1;
2516
2517 IF csr_salary.Salary_Type=3 THEN
2518
2519 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_054';
2520 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_054; --csr_cat.bs_ppo_value;
2521 l_counter:=l_counter+1;
2522
2523 xml_tab(l_counter).TagName :='STEERING_VALUE_054';
2524 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_054; --csr_cat.bs_ppo_value;
2525 l_counter:=l_counter+1;
2526
2527 ELSE
2528
2529 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_054';
2530 IF csr_salary.Steering_Code_054 > 0 THEN
2531 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_054); --csr_cat.bs_ppo_value;
2532 ELSE
2533 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_054; --csr_cat.bs_ppo_value;
2534 END IF;
2535 l_counter:=l_counter+1;
2536
2537 xml_tab(l_counter).TagName :='STEERING_VALUE_054';
2538 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_054); --csr_cat.bs_ppo_value;
2539 l_counter:=l_counter+1;
2540
2541 END IF;
2542
2543 xml_tab(l_counter).TagName :='STEERING_CODE_055';
2544 xml_tab(l_counter).TagValue :='055'; --csr_cat.bs_sick_pay;
2545 l_counter:=l_counter+1;
2546 IF csr_salary.Salary_Type=3 THEN
2547
2548 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_055';
2549 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_055; --csr_cat.bs_sick_pay;
2550 l_counter:=l_counter+1;
2551
2552 xml_tab(l_counter).TagName :='STEERING_VALUE_055';
2553 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_055; --csr_cat.bs_sick_pay;
2554 l_counter:=l_counter+1;
2555
2556 ELSE
2557
2558 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_055';
2559 IF csr_salary.Steering_Code_055 > 0 THEN
2560 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_055); --csr_cat.bs_sick_pay;
2561 ELSE
2562 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_055; --csr_cat.bs_sick_pay;
2563 END IF;
2564 l_counter:=l_counter+1;
2565
2566 xml_tab(l_counter).TagName :='STEERING_VALUE_055';
2567 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_055); --csr_cat.bs_sick_pay;
2568 l_counter:=l_counter+1;
2569
2570 END IF;
2571
2572
2573 xml_tab(l_counter).TagName :='STEERING_CODE_056';
2574 xml_tab(l_counter).TagValue :='056'; --csr_cat.bs_total_employees;
2575 l_counter:=l_counter+1;
2576
2577 IF csr_salary.Salary_Type=3 THEN
2578
2579 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_056';
2580 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_056; --csr_cat.bs_total_employees;
2581 l_counter:=l_counter+1;
2582
2583 xml_tab(l_counter).TagName :='STEERING_VALUE_056';
2584 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_056; --csr_cat.bs_total_employees;
2585 l_counter:=l_counter+1;
2586
2587 ELSE
2588
2589 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_056';
2590 IF csr_salary.Steering_Code_056 > 0 THEN
2591 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_056); --csr_cat.bs_total_employees;
2592 ELSE
2593 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_056; --csr_cat.bs_total_employees;
2594 END IF;
2595 l_counter:=l_counter+1;
2596
2597 xml_tab(l_counter).TagName :='STEERING_VALUE_056';
2598 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_056); --csr_cat.bs_total_employees;
2599 l_counter:=l_counter+1;
2600
2601 END IF;
2602 xml_tab(l_counter).TagName :='STEERING_CODE_058';
2603 xml_tab(l_counter).TagValue :='058'; --round(csr_cat.ws_full_time_employee,2);
2604 l_counter:=l_counter+1;
2605
2606 IF csr_salary.Salary_Type=3 THEN
2607
2608 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_058';
2609 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_058; --round(csr_cat.ws_full_time_employee,2);
2610 l_counter:=l_counter+1;
2611
2612 xml_tab(l_counter).TagName :='STEERING_VALUE_058';
2613 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_058; --round(csr_cat.ws_full_time_employee,2);
2614 l_counter:=l_counter+1;
2615
2616 ELSE
2617
2618 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_058';
2619 IF csr_salary.Steering_Code_058 > 0 THEN
2620 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_058); --round(csr_cat.ws_full_time_employee,2);
2621 ELSE
2622 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_058; --round(csr_cat.ws_full_time_employee,2);
2623 END IF;
2624 l_counter:=l_counter+1;
2625
2626 xml_tab(l_counter).TagName :='STEERING_VALUE_058';
2627 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_058); --round(csr_cat.ws_full_time_employee,2);
2628 l_counter:=l_counter+1;
2629
2630 END IF;
2631
2632 xml_tab(l_counter).TagName :='STEERING_CODE_600';
2633 xml_tab(l_counter).TagValue :='600'; --csr_cat.ws_gross_pay;
2634 l_counter:=l_counter+1;
2635
2636 IF csr_salary.Salary_Type=3 THEN
2637
2638 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_600';
2639 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_600; --csr_cat.ws_gross_pay;
2640 l_counter:=l_counter+1;
2641
2642 xml_tab(l_counter).TagName :='STEERING_VALUE_600';
2643 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_600; --csr_cat.ws_gross_pay;
2644 l_counter:=l_counter+1;
2645
2646 ELSE
2647
2648 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_600';
2649 IF csr_salary.Steering_Code_600 > 0 THEN
2650 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_600); --csr_cat.ws_gross_pay;
2651 ELSE
2652 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_600; --csr_cat.ws_gross_pay;
2653 END IF;
2654 l_counter:=l_counter+1;
2655
2656 xml_tab(l_counter).TagName :='STEERING_VALUE_600';
2657 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_600); --csr_cat.ws_gross_pay;
2658 l_counter:=l_counter+1;
2659
2660 END IF;
2661
2662 xml_tab(l_counter).TagName :='STEERING_CODE_601';
2663 xml_tab(l_counter).TagValue :='601'; --csr_cat.ws_working_agreement;
2664 l_counter:=l_counter+1;
2665
2666 IF csr_salary.Salary_Type=3 THEN
2667
2668 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_601';
2669 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_601; --csr_cat.ws_working_agreement;
2670 l_counter:=l_counter+1;
2671
2672 xml_tab(l_counter).TagName :='STEERING_VALUE_601';
2673 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_601; --csr_cat.ws_working_agreement;
2674 l_counter:=l_counter+1;
2675
2676 ELSE
2677 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_601';
2678 IF csr_salary.Steering_Code_601 > 0 THEN
2679 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_601); --csr_cat.ws_working_agreement;
2680 ELSE
2681 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_601; --csr_cat.ws_working_agreement;
2682 END IF;
2683 l_counter:=l_counter+1;
2684
2685 xml_tab(l_counter).TagName :='STEERING_VALUE_601';
2686 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_601); --csr_cat.ws_working_agreement;
2687 l_counter:=l_counter+1;
2688 END IF;
2689 xml_tab(l_counter).TagName :='STEERING_CODE_810';
2690 xml_tab(l_counter).TagValue :='810'; --csr_cat.ws_tcdp_value;
2691 l_counter:=l_counter+1;
2692
2693 IF csr_salary.Salary_Type=3 THEN
2694
2695 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_810';
2696 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_810; --csr_cat.ws_tcdp_value;
2697 l_counter:=l_counter+1;
2698
2699 xml_tab(l_counter).TagName :='STEERING_VALUE_810';
2700 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_810; --csr_cat.ws_tcdp_value;
2701 l_counter:=l_counter+1;
2702
2703 ELSE
2704 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_810';
2705 IF csr_salary.Steering_Code_810 > 0 THEN
2706 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_810); --csr_cat.ws_tcdp_value;
2707 ELSE
2708 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_810; --csr_cat.ws_tcdp_value;
2709 END IF;
2710 l_counter:=l_counter+1;
2711
2712 xml_tab(l_counter).TagName :='STEERING_VALUE_810';
2713 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_810); --csr_cat.ws_tcdp_value;
2714 l_counter:=l_counter+1;
2715 END IF;
2716 xml_tab(l_counter).TagName :='STEERING_CODE_800';
2717 xml_tab(l_counter).TagValue :='800'; --csr_cat.ws_tcow_value;
2718 l_counter:=l_counter+1;
2719 IF csr_salary.Salary_Type=3 THEN
2720
2721 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_800';
2722 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_800; --csr_cat.ws_tcow_value;
2723 l_counter:=l_counter+1;
2724
2725 xml_tab(l_counter).TagName :='STEERING_VALUE_800';
2726 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_800; --csr_cat.ws_tcow_value;
2727 l_counter:=l_counter+1;
2728
2729 ELSE
2730
2731 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_800';
2732 IF csr_salary.Steering_Code_800 > 0 THEN
2733 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_800); --csr_cat.ws_tcow_value;
2734 ELSE
2735 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_800; --csr_cat.ws_tcow_value;
2736 END IF;
2737 l_counter:=l_counter+1;
2738
2739 xml_tab(l_counter).TagName :='STEERING_VALUE_800';
2740 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_800); --csr_cat.ws_tcow_value;
2741 l_counter:=l_counter+1;
2742
2743 END IF;
2744 xml_tab(l_counter).TagName :='STEERING_CODE_801';
2745 xml_tab(l_counter).TagValue :='801'; --csr_cat.ws_nha_value;
2746 l_counter:=l_counter+1;
2747 IF csr_salary.Salary_Type=3 THEN
2748
2749 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_801';
2750 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_801; --csr_cat.ws_nha_value;
2751 l_counter:=l_counter+1;
2752
2753 xml_tab(l_counter).TagName :='STEERING_VALUE_801';
2754 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_801; --csr_cat.ws_nha_value;
2755 l_counter:=l_counter+1;
2756
2757 ELSE
2758
2759 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_801';
2760 IF csr_salary.Steering_Code_801 > 0 THEN
2761 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_801); --csr_cat.ws_nha_value;
2762 ELSE
2763 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_801; --csr_cat.ws_nha_value;
2764 END IF;
2765 l_counter:=l_counter+1;
2766
2767 xml_tab(l_counter).TagName :='STEERING_VALUE_801';
2768 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_801); --csr_cat.ws_nha_value;
2769 l_counter:=l_counter+1;
2770
2771 END IF;
2772
2773 xml_tab(l_counter).TagName :='STEERING_CODE_802';
2774 xml_tab(l_counter).TagValue :='802'; --csr_cat.ws_nho_value;
2775 l_counter:=l_counter+1;
2776 IF csr_salary.Salary_Type=3 THEN
2777
2778 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_802';
2779 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_802; --csr_cat.ws_nho_value;
2780 l_counter:=l_counter+1;
2781
2782 xml_tab(l_counter).TagName :='STEERING_VALUE_802';
2783 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_802; --csr_cat.ws_nho_value;
2784 l_counter:=l_counter+1;
2785
2786 ELSE
2787 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_802';
2788 IF csr_salary.Steering_Code_802 > 0 THEN
2789 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_802); --csr_cat.ws_nho_value;
2790 ELSE
2791 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_802; --csr_cat.ws_nho_value;
2792 END IF;
2793 l_counter:=l_counter+1;
2794
2795 xml_tab(l_counter).TagName :='STEERING_VALUE_802';
2796 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_802); --csr_cat.ws_nho_value;
2797 l_counter:=l_counter+1;
2798
2799 END IF;
2800
2801 xml_tab(l_counter).TagName :='STEERING_CODE_803';
2802 xml_tab(l_counter).TagValue :='803'; --csr_cat.ws_retroactive_pay;
2803 l_counter:=l_counter+1;
2804 IF csr_salary.Salary_Type=3 THEN
2805
2806 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_803';
2807 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_803; --csr_cat.ws_retroactive_pay;
2808 l_counter:=l_counter+1;
2809
2810 xml_tab(l_counter).TagName :='STEERING_VALUE_803';
2811 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_803; --csr_cat.ws_retroactive_pay;
2812 l_counter:=l_counter+1;
2813
2814 ELSE
2815 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_803';
2816 IF csr_salary.Steering_Code_803 > 0 THEN
2817 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_803); --csr_cat.ws_retroactive_pay;
2818 ELSE
2819 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_803; --csr_cat.ws_retroactive_pay;
2820 END IF;
2821 l_counter:=l_counter+1;
2822
2823 xml_tab(l_counter).TagName :='STEERING_VALUE_803';
2824 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_803); --csr_cat.ws_retroactive_pay;
2825 l_counter:=l_counter+1;
2826
2827 END IF;
2828
2829 xml_tab(l_counter).TagName :='STEERING_CODE_804';
2830 xml_tab(l_counter).TagValue :='804'; --csr_cat.ws_ppo_value;
2831 l_counter:=l_counter+1;
2832
2833 IF csr_salary.Salary_Type=3 THEN
2834
2835 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_804';
2836 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_804; --csr_cat.ws_ppo_value;
2837 l_counter:=l_counter+1;
2838
2839 xml_tab(l_counter).TagName :='STEERING_VALUE_804';
2840 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_804; --csr_cat.ws_ppo_value;
2841 l_counter:=l_counter+1;
2842
2843 ELSE
2844 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_804';
2845 IF csr_salary.Steering_Code_804 > 0 THEN
2846 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_804); --csr_cat.ws_ppo_value;
2847 ELSE
2848 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_804; --csr_cat.ws_ppo_value;
2849 END IF;
2850 l_counter:=l_counter+1;
2851
2852 xml_tab(l_counter).TagName :='STEERING_VALUE_804';
2853 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_804); --csr_cat.ws_ppo_value;
2854 l_counter:=l_counter+1;
2855
2856 END IF;
2857
2858 xml_tab(l_counter).TagName :='STEERING_CODE_805';
2859 xml_tab(l_counter).TagValue :='805'; --csr_cat.ws_sick_pay;
2860 l_counter:=l_counter+1;
2861 IF csr_salary.Salary_Type=3 THEN
2862
2863 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_805';
2864 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_805; --csr_cat.ws_sick_pay;
2865 l_counter:=l_counter+1;
2866
2867 xml_tab(l_counter).TagName :='STEERING_VALUE_805';
2868 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_805; --csr_cat.ws_sick_pay;
2869 l_counter:=l_counter+1;
2870
2871 ELSE
2872
2873 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_805';
2874 IF csr_salary.Steering_Code_804 > 0 THEN
2875 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_805); --csr_cat.ws_sick_pay;
2876 ELSE
2877 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_805; --csr_cat.ws_sick_pay;
2878 END IF;
2879 l_counter:=l_counter+1;
2880
2881 xml_tab(l_counter).TagName :='STEERING_VALUE_805';
2882 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_805); --csr_cat.ws_sick_pay;
2883 l_counter:=l_counter+1;
2884
2885 END IF;
2886
2887 xml_tab(l_counter).TagName :='STEERING_CODE_806';
2888 xml_tab(l_counter).TagValue :='806'; --csr_cat.ws_total_employees;
2889 l_counter:=l_counter+1;
2890 IF csr_salary.Salary_Type=3 THEN
2891
2892 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_806';
2893 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_806; --csr_cat.ws_total_employees;
2894 l_counter:=l_counter+1;
2895
2896 xml_tab(l_counter).TagName :='STEERING_VALUE_806';
2897 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_806; --csr_cat.ws_total_employees;
2898 l_counter:=l_counter+1;
2899
2900 ELSE
2901
2902 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_806';
2903 IF csr_salary.Steering_Code_806 > 0 THEN
2904 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_806); --csr_cat.ws_total_employees;
2905 ELSE
2906 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_806; --csr_cat.ws_total_employees;
2907 END IF;
2908 l_counter:=l_counter+1;
2909
2910 xml_tab(l_counter).TagName :='STEERING_VALUE_806';
2911 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_806); --csr_cat.ws_total_employees;
2912 l_counter:=l_counter+1;
2913
2914 END IF;
2915
2916 xml_tab(l_counter).TagName :='STEERING_CODE_808';
2917 xml_tab(l_counter).TagValue :='808'; --round(csr_cat.wh_full_time_employee,2);
2918 l_counter:=l_counter+1;
2919 IF csr_salary.Salary_Type=3 THEN
2920
2921 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_808';
2922 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_808; --round(csr_cat.wh_full_time_employee,2);
2923 l_counter:=l_counter+1;
2924
2925 xml_tab(l_counter).TagName :='STEERING_VALUE_808';
2926 xml_tab(l_counter).TagValue :=100*csr_salary.Steering_Code_808; --round(csr_cat.wh_full_time_employee,2);
2927 l_counter:=l_counter+1;
2928
2929 ELSE
2930
2931 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_808';
2932 IF csr_salary.Steering_Code_808 > 0 THEN
2933 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_808); --round(csr_cat.wh_full_time_employee,2);
2934 ELSE
2935 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_808; --round(csr_cat.wh_full_time_employee,2);
2936 END IF;
2937 l_counter:=l_counter+1;
2938
2939 xml_tab(l_counter).TagName :='STEERING_VALUE_808';
2940 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_808); --round(csr_cat.wh_full_time_employee,2);
2941 l_counter:=l_counter+1;
2942
2943 END IF;
2944
2945 xml_tab(l_counter).TagName :='SSST_DETAILS';
2946 xml_tab(l_counter).TagValue :='END_SSST_DETAILS';
2947 l_counter := l_counter + 1;
2948
2949
2950 END LOOP;
2951
2952
2953
2954 WritetoCLOB (p_xml );
2955 --INSERT INTO raaj VALUES (p_xml);
2956 -- fnd_file.put_line(fnd_file.LOG,'p_xml'||p_xml);
2957
2958
2959 END POPULATE_DATA_DETAIL;
2960
2961 END PAY_SE_SALARY_STRUCTURE_STATS;
2962