[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.12010000.2 2009/04/14 07:08:20 rrajaman ship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE lock_rec IS RECORD (
6 archive_assact_id NUMBER
7 );
8
9 TYPE lock_table IS TABLE OF lock_rec
10 INDEX BY BINARY_INTEGER;
11
12 g_lock_table lock_table;
13 g_index NUMBER := -1;
14 g_index_assact NUMBER := -1;
15 g_index_bal NUMBER := -1;
16 g_package VARCHAR2 (33) := 'PAY_SE_FORA.';
17 g_payroll_action_id NUMBER;
18 g_arc_payroll_action_id NUMBER;
19 -- Globals to pick up all the parameter
20 g_business_group_id NUMBER;
21 g_effective_date DATE;
22
23
24 g_legal_employer_id NUMBER;
25 g_local_unit_id NUMBER;
26 g_LE_request VARCHAR2 (240);
27
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
560 hr_organization_units.organization_id%TYPE)
561 IS
562 SELECT hoi2.ORG_INFORMATION1 local_unit_id
563 FROM hr_organization_units o1
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
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)
680 IS
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 paaf.hourly_salaried_code,
716 /*nvl(substr(trim(hsck.SEGMENT3),1,4),'0000') ssyk_code,
717 nvl(substr(trim(hsck.SEGMENT3),5,2),'00') association_code,*/
718 hsck.SEGMENT3 ssyk_code,
719 hsck.SEGMENT3 association_code,
720 hsck.segment14 agreement_code,
721 paaf.payroll_id
722 FROM per_all_assignments_f paaf,
723 per_all_people_f papf,
724 hr_soft_coding_keyflex hsck
725 WHERE papf.business_group_id=csr_v_business_group_id -- 3133 --paaf.assignment_id = p_assignment_id
726 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
727 AND papf.person_id=paaf.person_id
728 and papf.person_id between p_start_person and p_end_person
729 AND paaf.primary_flag='Y'
730 AND hsck.segment2=to_char(csr_v_local_unit_id) --3268)
731 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
732 AND paaf.effective_end_date
733 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
734 AND papf.effective_end_date
735 AND months_between (csr_v_effective_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
736 AND months_between (csr_v_effective_date,DATE_OF_BIRTH) <= 780 /* Age less than and equal to 65 */
737 AND papf.CURRENT_EMPLOYEE_FLAG='Y'
738 AND paaf.payroll_id IS NOT NULL
739 AND hsck.segment14 IS NOT NULL /* need not archive the person if he doesnt have agreement code */
740 ORDER BY papf.person_id;
741
742 CURSOR csr_lookup_values(csr_v_lookup_type hr_lookups.lookup_type%TYPE,
743 csr_v_lookup_code hr_lookups.lookup_code%TYPE)
744 IS
745 SELECT meaning FROM hr_lookups WHERE
746 lookup_type =csr_v_lookup_type --'SE_AGREEMENT_CODE'
747 AND LOOKUP_CODE=csr_v_lookup_code ;
748
749
750
751 l_ovn NUMBER;
752 l_action_info_id NUMBER;
753 L_MONTH_START_DATE DATE;
754 L_MONTH_END_DATE DATE;
755 L_LOCAL_UNIT_NAME VARCHAR2(50);
756 l_sex CHAR(1);
757 l_local_unit_id NUMBER;
758 l_assignment_category VARCHAR2(5);
759 l_assignment_start_date DATE;
760 l_assignment_end_date DATE;
761 l_absence_count NUMBER;
762 l_employee_category per_all_assignments_f.employee_category%type;
763 l_person_number per_all_people_f.national_identifier%TYPE;
764 l_person_name VARCHAR2(350);
765
766 l_terminated VARCHAR2(50);
767
768 l_gross_salary number;
769 --l_start_date date;
770 --l_end_date date;
771 l_termination_date date;
772 lr_Get_Defined_Balance_Id pay_defined_balances.defined_balance_id%type;
773 l_value number;
774 L_CFAR_NUMBER NUMBER;
775 l_legal_employer_id hr_organization_units.organization_id%TYPE;
776 l_virtual_date DATE;
777 l_date_birth per_all_people_f.DATE_OF_BIRTH%TYPE;
778 l_counter NUMBER :=0;
779 l_total_salary NUMBER;
780 l_asg_start_date DATE;
781 l_asg_end_date date;
782 l_category per_all_assignments_f.employee_category%type;
783 l_prev_category per_all_assignments_f.employee_category%type;
784 l_working_percentage NUMBER;
785 l_asg_hour_sal per_all_assignments_f.hourly_salaried_code%type;
786 l_frequency per_all_assignments_f.frequency%type;
787 l_normal_hours per_all_assignments_f.normal_hours%type;
788 l_include_event char(1);
789 l_wrk_schd_return NUMBER;
790 l_wrk_duration NUMBER;
791 l_absence_start_date DATE;
792 l_absence_end_date DATE;
793 l_type varchar2(50);
794
795 l_valid_person number;
796 l_check_insert number;
797 l_worksite_number varchar2(10);
798 l_association_number varchar2(10);
799 l_legal_agreement_code varchar2(10);
800 l_agreement_code hr_lookups.meaning%TYPE;
801 l_agreement hr_lookups.lookup_code%TYPE;
802 l_asg_agreement_code hr_lookups.meaning%TYPE;
803 l_weekend_duty varchar2(10);
804 l_legal_employer hr_organization_units.name%TYPE;
805 l_organization_id varchar2(20);
806 l_membership_number varchar2(10);
807 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
808 l_person_id per_all_people_f.person_id%TYPE;
809 l_ssyk hr_lookups.lookup_code%TYPE;
810 l_ssyk_code hr_lookups.meaning%TYPE;
811 l_association_code varchar2(10);
812 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
813 l_work_type varchar2(250);
814 l_salary_type Number(1);
815 l_payroll_type pay_payrolls_f.payroll_type%TYPE;
816
817
818 l_001_steering_code NUMBER:=0;
819 l_002_steering_code NUMBER:=0;
820 l_003_steering_code NUMBER:=0;
821 l_004_steering_code NUMBER:=0;
822 l_051_steering_code NUMBER:=0;
823 l_052_steering_code NUMBER:=0;
824 l_053_steering_code NUMBER:=0;
825 l_054_steering_code NUMBER:=0;
826 l_055_steering_code NUMBER:=0;
827 l_056_steering_code NUMBER:=0;
828 l_058_steering_code NUMBER:=0;
829 l_600_steering_code NUMBER:=0;
830 l_601_steering_code NUMBER:=0;
831 l_810_steering_code NUMBER:=0;
832 l_800_steering_code NUMBER:=0;
833 l_801_steering_code NUMBER:=0;
834 l_802_steering_code NUMBER:=0;
835 l_803_steering_code NUMBER:=0;
836 l_804_steering_code NUMBER:=0;
837 l_805_steering_code NUMBER:=0;
838 l_806_steering_code NUMBER:=0;
839 l_808_steering_code NUMBER:=0;
840 l_hour_sal CHAR(1);
841
842
843 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
844 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
845
846 TYPE emp_cat_type
847 IS TABLE OF VARCHAR2(10)
848 INDEX BY BINARY_INTEGER;
849 emp_cat emp_cat_type;
850
851 TYPE emp_job_record IS RECORD
852 (
853 job VARCHAR2(5),
854 end_date date
855 );
856 TYPE emp_job_type
857 IS TABLE OF emp_job_record
858 INDEX BY BINARY_INTEGER;
859 emp_job emp_job_type;
860
861 TYPE emp_detail_record IS RECORD
862 (
863 l_start_date date,
864 l_end_date date,
865 l_category varchar2(5),
866 l_job varchar2(5),
867 l_gross_salary number(17,2),
868 l_termination varchar2(5),
869 l_white_from date
870 );
871 TYPE emp_record_type
872 IS TABLE OF emp_detail_record
873 INDEX BY BINARY_INTEGER;
874 emp_record emp_record_type;
875 --------------
876
877 BEGIN
878 IF g_debug
879 THEN
880 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
881 END IF;
882 --The codes used in the package
883 /*
884 001- Hours worked (September)
885 002-Paid Overtime (September)
886 003-Working Hours per week
887 004-Working Hours week-full
888 051-Monthly, weekly pay
889 052-Over-time allowance
890 053-Comp. for Over-time
891 054-Comp. for shift work
892 055-comp. duty, relief work
893 056-Comp. for danger,dirt
894 058-Incentive pay (bonus)
895 600-Holiday days Entitlement
896 601-Salary for hours worked
897 810-Job Status
898 800-Comp. type of shift
899 801-Comp. post and position
900 802-Comp. for calving
901 803-Comp. for delegation
902 804-Comp. County, Municipal
903 805-Comp. for Per Diem
904 806-Comp. for Travel Expenses
905 808-Comp. for Official Duty
906 */
907
908 g_payroll_action_id := p_payroll_action_id;
909 g_business_group_id := NULL;
910 g_effective_date := NULL;
911 g_LE_request :=null;
912 g_legal_employer_id := NULL;
913 get_all_parameters (p_payroll_action_id
914 , g_business_group_id
915 , g_effective_date
916 , g_legal_employer_id
917 , g_LE_request
918 , g_month
919 , g_report_year
920 );
921
922 -- g_start_date:=to_date('01-' || g_month || '-' || g_year, 'dd-mm-yyyy');
923 --g_end_date:=to_date('31-'|| g_month || '-' || g_year, 'dd-mm-yyyy');
924 g_end_date:=last_day(to_date('01-09'|| '-' || g_report_year, 'dd-mm-yyyy'));
925
926
927 IF g_LE_request ='LE_SELECTED' THEN
928 FOR csr_legal_employer IN csr_local_legal_employer(g_business_group_id,g_legal_employer_id) LOOP
929 l_local_unit_id:=csr_legal_employer.local_unit;
930 /* Salary Structure EIT Details */
931 OPEN csr_salary_structure_details(g_business_group_id,g_legal_employer_id);
932 FETCH csr_salary_structure_details INTO l_worksite_number,l_association_number,
933 l_legal_agreement_code,l_weekend_duty;
934 CLOSE csr_salary_structure_details;
935 FOR csr_person IN csr_person_local_unit(g_business_group_id,l_local_unit_id,g_end_date)LOOP
936 l_person_id:=csr_person.person_id;
937 l_hour_sal:=csr_person.hourly_salaried_code;
938 l_person_number:=csr_person.person_number;
939 l_assignment_id:=csr_person.assignment_id;
940 l_employee_category:=csr_person.employee_category;
941 l_ssyk:=csr_person.ssyk_code;
942 --l_association_code:=csr_person.association_code;
943 l_payroll_id:=csr_person.payroll_id;
944 l_agreement:=csr_person.agreement_code;
945 OPEN csr_lookup_values('SE_SKILLS_LEVEL_CODE',l_ssyk);
946 FETCH csr_lookup_values INTO l_ssyk_code;
947 l_association_code:=nvl(substr(trim(l_ssyk_code),5,2),'00');
948 l_ssyk_code:=nvl(substr(trim(l_ssyk_code),1,4),'0000');
949 CLOSE csr_lookup_values;
950 OPEN csr_lookup_values('SE_AGREEMENT_CODE',l_agreement);
951 FETCH csr_lookup_values INTO l_asg_agreement_code;
952 CLOSE csr_lookup_values;
953 l_agreement_code:=l_asg_agreement_code;
954 OPEN csr_payroll_period(l_payroll_id);
955 FETCH csr_payroll_period INTO l_payroll_type;
956 CLOSE csr_payroll_period;
957 /*IF l_payroll_type='Calendar Month' THEN
958 l_salary_type:=1;
959 ELSIF l_payroll_type='Week' THEN
960 l_salary_type:=2;
961 END IF;*/
962 IF l_payroll_type='Calendar Month' THEN
963 IF l_hour_sal='H' THEN
964 l_salary_type:=3;
965 ELSE
966 l_salary_type:=1;
967 END IF;
968 ELSIF l_payroll_type='Week' THEN
969 IF l_hour_sal='H' THEN
970 l_salary_type:=3;
971 ELSE
972 l_salary_type:=2;
973 END if;
974 ELSIF l_payroll_type='Bi-Week' THEN
975 IF l_hour_sal='H' THEN
976 l_salary_type:=3;
977 ELSE
978 l_salary_type:=1;
979 END IF;
980 END IF;
981
982
983 OPEN csr_extra_assignment(l_assignment_id);
984 FETCH csr_extra_assignment INTO l_work_type;
985 CLOSE csr_extra_assignment;
986 pay_balance_pkg.set_context('ASSIGNMENT_ID',l_assignment_id); --133942);
987 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id); --3621);
988
989 FOR csr_balance IN csr_balances(g_business_group_id,'001- Hours worked (September)') LOOP
990
991 l_balance_type_id:=csr_balance.balance_type_id;
992
993 OPEN csr_defined_balances(l_balance_type_id);
994 FETCH csr_defined_balances INTO l_defined_balance_id;
995 CLOSE csr_defined_balances;
996
997 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
998 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
999 P_VIRTUAL_DATE=>g_end_date),0);
1000
1001 l_001_steering_code:=l_001_steering_code+l_value;
1002
1003 END LOOP;
1004 FOR csr_balance IN csr_balances(g_business_group_id,'002-Paid Overtime (September)') LOOP
1005
1006 l_balance_type_id:=csr_balance.balance_type_id;
1007 OPEN csr_defined_balances(l_balance_type_id);
1008 FETCH csr_defined_balances INTO l_defined_balance_id;
1009 CLOSE csr_defined_balances;
1010 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1011 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1012 P_VIRTUAL_DATE=>g_end_date),0);
1013 l_002_steering_code:=l_002_steering_code+l_value;
1014 END LOOP;
1015 FOR csr_balance IN csr_balances(g_business_group_id,'003-Working Hours per week') LOOP
1016
1017 l_balance_type_id:=csr_balance.balance_type_id;
1018 OPEN csr_defined_balances(l_balance_type_id);
1019 FETCH csr_defined_balances INTO l_defined_balance_id;
1020 CLOSE csr_defined_balances;
1021 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1022 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1023 P_VIRTUAL_DATE=>g_end_date),0);
1024 l_003_steering_code:=l_003_steering_code+l_value;
1025 END LOOP;
1026 FOR csr_balance IN csr_balances(g_business_group_id,'004-Working Hours week-full') LOOP
1027
1028 l_balance_type_id:=csr_balance.balance_type_id;
1029 OPEN csr_defined_balances(l_balance_type_id);
1030 FETCH csr_defined_balances INTO l_defined_balance_id;
1031 CLOSE csr_defined_balances;
1032 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1033 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1034 P_VIRTUAL_DATE=>g_end_date),0);
1035 l_004_steering_code:=l_004_steering_code+l_value;
1036 END LOOP;
1037 FOR csr_balance IN csr_balances(g_business_group_id,'051-Monthly, weekly pay') LOOP
1038
1039 l_balance_type_id:=csr_balance.balance_type_id;
1040 OPEN csr_defined_balances(l_balance_type_id);
1041 FETCH csr_defined_balances INTO l_defined_balance_id;
1042 CLOSE csr_defined_balances;
1043 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1044 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1045 P_VIRTUAL_DATE=>g_end_date),0);
1046 l_051_steering_code:=l_051_steering_code+l_value;
1047 END LOOP;
1048 FOR csr_balance IN csr_balances(g_business_group_id,'052-Over-time allowance') LOOP
1049
1050 l_balance_type_id:=csr_balance.balance_type_id;
1051 OPEN csr_defined_balances(l_balance_type_id);
1052 FETCH csr_defined_balances INTO l_defined_balance_id;
1053 CLOSE csr_defined_balances;
1054 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1055 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1056 P_VIRTUAL_DATE=>g_end_date),0);
1057 l_052_steering_code:=l_052_steering_code+l_value;
1058 END LOOP;
1059 FOR csr_balance IN csr_balances(g_business_group_id,'053-Comp. for Over-time') LOOP
1060
1061 l_balance_type_id:=csr_balance.balance_type_id;
1062 OPEN csr_defined_balances(l_balance_type_id);
1063 FETCH csr_defined_balances INTO l_defined_balance_id;
1064 CLOSE csr_defined_balances;
1065 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1066 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1067 P_VIRTUAL_DATE=>g_end_date),0);
1068 l_053_steering_code:=l_053_steering_code+l_value;
1069 END LOOP;
1070 FOR csr_balance IN csr_balances(g_business_group_id,'054-Comp. for shift work') LOOP
1071
1072 l_balance_type_id:=csr_balance.balance_type_id;
1073 OPEN csr_defined_balances(l_balance_type_id);
1074 FETCH csr_defined_balances INTO l_defined_balance_id;
1075 CLOSE csr_defined_balances;
1076 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1077 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1078 P_VIRTUAL_DATE=>g_end_date),0);
1079 l_054_steering_code:=l_054_steering_code+l_value;
1080 END LOOP;
1081 FOR csr_balance IN csr_balances(g_business_group_id,'055-comp. duty, relief work') LOOP
1082
1083 l_balance_type_id:=csr_balance.balance_type_id;
1084 OPEN csr_defined_balances(l_balance_type_id);
1085 FETCH csr_defined_balances INTO l_defined_balance_id;
1086 CLOSE csr_defined_balances;
1087 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1088 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1089 P_VIRTUAL_DATE=>g_end_date),0);
1090 l_055_steering_code:=l_055_steering_code+l_value;
1091 END LOOP;
1092 FOR csr_balance IN csr_balances(g_business_group_id,'056-Comp. for danger,dirt') LOOP
1093
1094 l_balance_type_id:=csr_balance.balance_type_id;
1095 OPEN csr_defined_balances(l_balance_type_id);
1096 FETCH csr_defined_balances INTO l_defined_balance_id;
1097 CLOSE csr_defined_balances;
1098 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1099 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1100 P_VIRTUAL_DATE=>g_end_date),0);
1101 l_056_steering_code:=l_056_steering_code+l_value;
1102 END LOOP;
1103 FOR csr_balance IN csr_balances(g_business_group_id,'058-Incentive pay (bonus)') LOOP
1104
1105 l_balance_type_id:=csr_balance.balance_type_id;
1106 OPEN csr_defined_balances(l_balance_type_id);
1107 FETCH csr_defined_balances INTO l_defined_balance_id;
1108 CLOSE csr_defined_balances;
1109 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1110 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1111 P_VIRTUAL_DATE=>g_end_date),0);
1112 l_058_steering_code:=l_058_steering_code+l_value;
1113 END LOOP;
1114 FOR csr_balance IN csr_balances(g_business_group_id,'600-Holiday days Entitlement') LOOP
1115
1116 l_balance_type_id:=csr_balance.balance_type_id;
1117 OPEN csr_defined_balances(l_balance_type_id);
1118 FETCH csr_defined_balances INTO l_defined_balance_id;
1119 CLOSE csr_defined_balances;
1120 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1121 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1122 P_VIRTUAL_DATE=>g_end_date),0);
1123 l_600_steering_code:=l_600_steering_code+l_value;
1124 END LOOP;
1125 FOR csr_balance IN csr_balances(g_business_group_id,'601-Salary for hours worked') LOOP
1126
1127 l_balance_type_id:=csr_balance.balance_type_id;
1128 OPEN csr_defined_balances(l_balance_type_id);
1129 FETCH csr_defined_balances INTO l_defined_balance_id;
1130 CLOSE csr_defined_balances;
1131 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1132 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1133 P_VIRTUAL_DATE=>g_end_date),0);
1134 l_601_steering_code:=l_601_steering_code+l_value;
1135 END LOOP;
1136 FOR csr_balance IN csr_balances(g_business_group_id,'810-Job Status') LOOP
1137
1138 l_balance_type_id:=csr_balance.balance_type_id;
1139 OPEN csr_defined_balances(l_balance_type_id);
1140 FETCH csr_defined_balances INTO l_defined_balance_id;
1141 CLOSE csr_defined_balances;
1142 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1143 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1144 P_VIRTUAL_DATE=>g_end_date),0);
1145 l_810_steering_code:=l_810_steering_code+l_value;
1146 END LOOP;
1147 FOR csr_balance IN csr_balances(g_business_group_id,'800-Comp. type of shift') LOOP
1148
1149 l_balance_type_id:=csr_balance.balance_type_id;
1150 OPEN csr_defined_balances(l_balance_type_id);
1151 FETCH csr_defined_balances INTO l_defined_balance_id;
1152 CLOSE csr_defined_balances;
1153 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1154 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1155 P_VIRTUAL_DATE=>g_end_date),0);
1156 l_800_steering_code:=l_800_steering_code+l_value;
1157 END LOOP;
1158 FOR csr_balance IN csr_balances(g_business_group_id,'801-Comp. post and position') LOOP
1159
1160 l_balance_type_id:=csr_balance.balance_type_id;
1161 OPEN csr_defined_balances(l_balance_type_id);
1162 FETCH csr_defined_balances INTO l_defined_balance_id;
1163 CLOSE csr_defined_balances;
1164 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1165 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1166 P_VIRTUAL_DATE=>g_end_date),0);
1167 l_801_steering_code:=l_801_steering_code+l_value;
1168 END LOOP;
1169 FOR csr_balance IN csr_balances(g_business_group_id,'802-Comp. for calving') LOOP
1170
1171 l_balance_type_id:=csr_balance.balance_type_id;
1172 OPEN csr_defined_balances(l_balance_type_id);
1173 FETCH csr_defined_balances INTO l_defined_balance_id;
1174 CLOSE csr_defined_balances;
1175 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1176 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1177 P_VIRTUAL_DATE=>g_end_date),0);
1178 l_802_steering_code:=l_802_steering_code+l_value;
1179 END LOOP;
1180 FOR csr_balance IN csr_balances(g_business_group_id,'803-Comp. for delegation') LOOP
1181
1182 l_balance_type_id:=csr_balance.balance_type_id;
1183 OPEN csr_defined_balances(l_balance_type_id);
1184 FETCH csr_defined_balances INTO l_defined_balance_id;
1185 CLOSE csr_defined_balances;
1186 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1187 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1188 P_VIRTUAL_DATE=>g_end_date),0);
1189 l_803_steering_code:=l_803_steering_code+l_value;
1190 END LOOP;
1191 FOR csr_balance IN csr_balances(g_business_group_id,'804-Comp. County, Municipal') LOOP
1192
1193 l_balance_type_id:=csr_balance.balance_type_id;
1194 OPEN csr_defined_balances(l_balance_type_id);
1195 FETCH csr_defined_balances INTO l_defined_balance_id;
1196 CLOSE csr_defined_balances;
1197 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1198 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1199 P_VIRTUAL_DATE=>g_end_date),0);
1200 l_804_steering_code:=l_804_steering_code+l_value;
1201 END LOOP;
1202 FOR csr_balance IN csr_balances(g_business_group_id,'805-Comp. for Per Diem') LOOP
1203
1204 l_balance_type_id:=csr_balance.balance_type_id;
1205 OPEN csr_defined_balances(l_balance_type_id);
1206 FETCH csr_defined_balances INTO l_defined_balance_id;
1207 CLOSE csr_defined_balances;
1208 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1209 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1210 P_VIRTUAL_DATE=>g_end_date),0);
1211 l_805_steering_code:=l_805_steering_code+l_value;
1212 END LOOP;
1213 FOR csr_balance IN csr_balances(g_business_group_id,'806-Comp. for Travel Expenses') LOOP
1214
1215 l_balance_type_id:=csr_balance.balance_type_id;
1216 OPEN csr_defined_balances(l_balance_type_id);
1217 FETCH csr_defined_balances INTO l_defined_balance_id;
1218 CLOSE csr_defined_balances;
1219 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1220 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1221 P_VIRTUAL_DATE=>g_end_date),0);
1222 l_806_steering_code:=l_806_steering_code+l_value;
1223 END LOOP;
1224 FOR csr_balance IN csr_balances(g_business_group_id,'808-Comp. for Official Duty') LOOP
1225
1226 l_balance_type_id:=csr_balance.balance_type_id;
1227 OPEN csr_defined_balances(l_balance_type_id);
1228 FETCH csr_defined_balances INTO l_defined_balance_id;
1229 CLOSE csr_defined_balances;
1230 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1231 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1232 P_VIRTUAL_DATE=>g_end_date),0);
1233 l_808_steering_code:=l_808_steering_code+l_value;
1234 END LOOP;
1235
1236 pay_action_information_api.create_action_information
1237 (p_action_information_id => l_action_info_id
1238 , p_action_context_id => p_payroll_action_id
1239 , p_action_context_type => 'PA'
1240 , p_object_version_number => l_ovn
1241 , p_effective_date => g_effective_date
1242 , p_source_id => NULL
1243 , p_source_text => NULL
1244 , p_action_information_category => 'EMEA REPORT INFORMATION'
1245 , p_action_information1 => 'PYSESSSA'
1246 , p_action_information2 => 'PER1'
1247 , p_action_information3 => l_person_id
1248 , p_action_information4 => l_person_number
1249 , p_action_information5 => l_local_unit_id
1250 , p_action_information6 => l_cfar_number
1251 , p_action_information7 => l_employee_category
1252 , p_action_information8 => l_work_type
1253 , p_action_information9 => l_agreement_code
1254 , p_action_information10 => l_ssyk_code
1255 , p_action_information11 => l_association_code
1256 , p_action_information12 => l_salary_type
1257 , p_action_information13 => l_001_steering_code
1258 , p_action_information14 => l_002_steering_code
1259 , p_action_information15 => l_003_steering_code
1260 , p_action_information16 => l_004_steering_code
1261 , p_action_information17 => l_051_steering_code
1262 , p_action_information18 => l_052_steering_code
1263 , p_action_information19 => l_053_steering_code
1264 , p_action_information20 => l_054_steering_code
1265 , p_action_information21 => l_055_steering_code
1266 , p_action_information22 => l_056_steering_code
1267 , p_action_information23 => l_058_steering_code
1268 , p_action_information24 => l_600_steering_code
1269 , p_action_information25 => l_601_steering_code
1270 , p_action_information26 => l_810_steering_code
1271 );
1272
1273 pay_action_information_api.create_action_information
1274 (p_action_information_id => l_action_info_id
1275 , p_action_context_id => p_payroll_action_id
1276 , p_action_context_type => 'PA'
1277 , p_object_version_number => l_ovn
1278 , p_effective_date => g_effective_date
1279 , p_source_id => NULL
1280 , p_source_text => NULL
1281 , p_action_information_category => 'EMEA REPORT INFORMATION'
1282 , p_action_information1 => 'PYSESSSA'
1283 , p_action_information2 => 'PER2'
1284 , p_action_information3 => l_person_id
1285 , p_action_information4 => l_local_unit_id
1286 , p_action_information5 => l_800_steering_code
1287 , p_action_information6 => l_801_steering_code
1288 , p_action_information7 => l_802_steering_code
1289 , p_action_information8 => l_803_steering_code
1290 , p_action_information9 => l_804_steering_code
1291 , p_action_information10 => l_805_steering_code
1292 , p_action_information11 => l_806_steering_code
1293 , p_action_information12 => l_808_steering_code
1294 );
1295 l_001_steering_code:=0;
1296 l_002_steering_code:=0;
1297 l_003_steering_code:=0;
1298 l_004_steering_code:=0;
1299 l_051_steering_code:=0;
1300 l_052_steering_code:=0;
1301 l_053_steering_code:=0;
1302 l_054_steering_code:=0;
1303 l_055_steering_code:=0;
1304 l_056_steering_code:=0;
1305 l_058_steering_code:=0;
1306 l_600_steering_code:=0;
1307 l_601_steering_code:=0;
1308 l_810_steering_code:=0;
1309 l_800_steering_code:=0;
1310 l_801_steering_code:=0;
1311 l_802_steering_code:=0;
1312 l_803_steering_code:=0;
1313 l_804_steering_code:=0;
1314 l_805_steering_code:=0;
1315 l_806_steering_code:=0;
1316 l_808_steering_code:=0;
1317
1318 END LOOP;
1319
1320
1321 END LOOP;
1322 ELSE
1323 FOR csr_legal IN csr_legal_employer(g_business_group_id) LOOP
1324 l_legal_employer_id:=csr_legal.legal_employer_id;
1325 /* Salary Structure EIT Details */
1326 OPEN csr_salary_structure_details(g_business_group_id,g_legal_employer_id);
1327 FETCH csr_salary_structure_details INTO l_worksite_number,l_association_number,
1328 l_legal_agreement_code,l_weekend_duty;
1329 CLOSE csr_salary_structure_details;
1330 FOR csr_legal_employer IN csr_local_legal_employer(g_business_group_id,l_legal_employer_id) LOOP
1331 l_local_unit_id:=csr_legal_employer.local_unit;
1332 FOR csr_person IN csr_person_local_unit(g_business_group_id,l_local_unit_id,g_end_date)LOOP
1333 l_person_id:=csr_person.person_id;
1334 l_person_number:=csr_person.person_number;
1335 l_hour_sal:=csr_person.hourly_salaried_code;
1336 l_assignment_id:=csr_person.assignment_id;
1337 l_employee_category:=csr_person.employee_category;
1338 l_ssyk:=csr_person.ssyk_code;
1339 l_association_code:=csr_person.association_code;
1340 l_payroll_id:=csr_person.payroll_id;
1341 l_agreement:=csr_person.agreement_code;
1342 OPEN csr_lookup_values('SE_SKILLS_LEVEL_CODE',l_ssyk);
1343 FETCH csr_lookup_values INTO l_ssyk_code;
1344 l_association_code:=nvl(substr(trim(l_ssyk_code),5,2),'00');
1345 l_ssyk_code:=nvl(substr(trim(l_ssyk_code),1,4),'0000');
1346 CLOSE csr_lookup_values;
1347 OPEN csr_lookup_values('SE_AGREEMENT_CODE',l_agreement);
1348 FETCH csr_lookup_values INTO l_asg_agreement_code;
1349 CLOSE csr_lookup_values;
1350 l_agreement_code:=l_asg_agreement_code;
1351 OPEN csr_payroll_period(l_payroll_id);
1352 FETCH csr_payroll_period INTO l_payroll_type;
1353 CLOSE csr_payroll_period;
1354 IF l_payroll_type='Calendar Month' THEN
1355 IF l_hour_sal='H' THEN
1356 l_salary_type:=3;
1357 ELSE
1358 l_salary_type:=1;
1359 END IF;
1360 ELSIF l_payroll_type='Week' THEN
1361 IF l_hour_sal='H' THEN
1362 l_salary_type:=3;
1363 ELSE
1364 l_salary_type:=2;
1365 END if;
1366 ELSIF l_payroll_type='Bi-Week' THEN
1367 IF l_hour_sal='H' THEN
1368 l_salary_type:=3;
1369 ELSE
1370 l_salary_type:=1;
1371 END IF;
1372 END IF;
1373
1374 OPEN csr_extra_assignment(l_assignment_id);
1375 FETCH csr_extra_assignment INTO l_work_type;
1376 CLOSE csr_extra_assignment;
1377 pay_balance_pkg.set_context('ASSIGNMENT_ID',l_assignment_id); --133942);
1378 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id); --3621);
1379
1380 FOR csr_balance IN csr_balances(g_business_group_id,'001- Hours worked (September)') LOOP
1381
1382 l_balance_type_id:=csr_balance.balance_type_id;
1383
1384 OPEN csr_defined_balances(l_balance_type_id);
1385 FETCH csr_defined_balances INTO l_defined_balance_id;
1386 CLOSE csr_defined_balances;
1387
1388 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1389 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1390 P_VIRTUAL_DATE=>g_end_date),0);
1391
1392 l_001_steering_code:=l_001_steering_code+l_value;
1393
1394 END LOOP;
1395 FOR csr_balance IN csr_balances(g_business_group_id,'002-Paid Overtime (September)') LOOP
1396
1397 l_balance_type_id:=csr_balance.balance_type_id;
1398 OPEN csr_defined_balances(l_balance_type_id);
1399 FETCH csr_defined_balances INTO l_defined_balance_id;
1400 CLOSE csr_defined_balances;
1401 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1402 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1403 P_VIRTUAL_DATE=>g_end_date),0);
1404 l_002_steering_code:=l_002_steering_code+l_value;
1405 END LOOP;
1406 FOR csr_balance IN csr_balances(g_business_group_id,'003-Working Hours per week') LOOP
1407
1408 l_balance_type_id:=csr_balance.balance_type_id;
1409 OPEN csr_defined_balances(l_balance_type_id);
1410 FETCH csr_defined_balances INTO l_defined_balance_id;
1411 CLOSE csr_defined_balances;
1412 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1413 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1414 P_VIRTUAL_DATE=>g_end_date),0);
1415 l_003_steering_code:=l_003_steering_code+l_value;
1416 END LOOP;
1417 FOR csr_balance IN csr_balances(g_business_group_id,'004-Working Hours week-full') LOOP
1418
1419 l_balance_type_id:=csr_balance.balance_type_id;
1420 OPEN csr_defined_balances(l_balance_type_id);
1421 FETCH csr_defined_balances INTO l_defined_balance_id;
1422 CLOSE csr_defined_balances;
1423 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1424 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1425 P_VIRTUAL_DATE=>g_end_date),0);
1426 l_004_steering_code:=l_004_steering_code+l_value;
1427 END LOOP;
1428 FOR csr_balance IN csr_balances(g_business_group_id,'051-Monthly, weekly pay') LOOP
1429
1430 l_balance_type_id:=csr_balance.balance_type_id;
1431 OPEN csr_defined_balances(l_balance_type_id);
1432 FETCH csr_defined_balances INTO l_defined_balance_id;
1433 CLOSE csr_defined_balances;
1434 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1435 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1436 P_VIRTUAL_DATE=>g_end_date),0);
1437 l_051_steering_code:=l_051_steering_code+l_value;
1438 END LOOP;
1439 FOR csr_balance IN csr_balances(g_business_group_id,'052-Over-time allowance') LOOP
1440
1441 l_balance_type_id:=csr_balance.balance_type_id;
1442 OPEN csr_defined_balances(l_balance_type_id);
1443 FETCH csr_defined_balances INTO l_defined_balance_id;
1444 CLOSE csr_defined_balances;
1445 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1446 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1447 P_VIRTUAL_DATE=>g_end_date),0);
1448 l_052_steering_code:=l_052_steering_code+l_value;
1449 END LOOP;
1450 FOR csr_balance IN csr_balances(g_business_group_id,'053-Comp. for Over-time') LOOP
1451
1452 l_balance_type_id:=csr_balance.balance_type_id;
1453 OPEN csr_defined_balances(l_balance_type_id);
1454 FETCH csr_defined_balances INTO l_defined_balance_id;
1455 CLOSE csr_defined_balances;
1456 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1457 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1458 P_VIRTUAL_DATE=>g_end_date),0);
1459 l_053_steering_code:=l_053_steering_code+l_value;
1460 END LOOP;
1461 FOR csr_balance IN csr_balances(g_business_group_id,'054-Comp. for shift work') LOOP
1462
1463 l_balance_type_id:=csr_balance.balance_type_id;
1464 OPEN csr_defined_balances(l_balance_type_id);
1465 FETCH csr_defined_balances INTO l_defined_balance_id;
1466 CLOSE csr_defined_balances;
1467 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1468 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1469 P_VIRTUAL_DATE=>g_end_date),0);
1470 l_054_steering_code:=l_054_steering_code+l_value;
1471 END LOOP;
1472 FOR csr_balance IN csr_balances(g_business_group_id,'055-comp. duty, relief work') LOOP
1473
1474 l_balance_type_id:=csr_balance.balance_type_id;
1475 OPEN csr_defined_balances(l_balance_type_id);
1476 FETCH csr_defined_balances INTO l_defined_balance_id;
1477 CLOSE csr_defined_balances;
1478 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1479 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1480 P_VIRTUAL_DATE=>g_end_date),0);
1481 l_055_steering_code:=l_055_steering_code+l_value;
1482 END LOOP;
1483 FOR csr_balance IN csr_balances(g_business_group_id,'056-Comp. for danger,dirt') LOOP
1484
1485 l_balance_type_id:=csr_balance.balance_type_id;
1486 OPEN csr_defined_balances(l_balance_type_id);
1487 FETCH csr_defined_balances INTO l_defined_balance_id;
1488 CLOSE csr_defined_balances;
1489 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1490 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1491 P_VIRTUAL_DATE=>g_end_date),0);
1492 l_056_steering_code:=l_056_steering_code+l_value;
1493 END LOOP;
1494 FOR csr_balance IN csr_balances(g_business_group_id,'058-Incentive pay (bonus)') LOOP
1495
1496 l_balance_type_id:=csr_balance.balance_type_id;
1497 OPEN csr_defined_balances(l_balance_type_id);
1498 FETCH csr_defined_balances INTO l_defined_balance_id;
1499 CLOSE csr_defined_balances;
1500 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1501 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1502 P_VIRTUAL_DATE=>g_end_date),0);
1503 l_058_steering_code:=l_058_steering_code+l_value;
1504 END LOOP;
1505 FOR csr_balance IN csr_balances(g_business_group_id,'600-Holiday days Entitlement') LOOP
1506
1507 l_balance_type_id:=csr_balance.balance_type_id;
1508 OPEN csr_defined_balances(l_balance_type_id);
1509 FETCH csr_defined_balances INTO l_defined_balance_id;
1510 CLOSE csr_defined_balances;
1511 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1512 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1513 P_VIRTUAL_DATE=>g_end_date),0);
1514 l_600_steering_code:=l_600_steering_code+l_value;
1515 END LOOP;
1516 FOR csr_balance IN csr_balances(g_business_group_id,'601-Salary for hours worked') LOOP
1517
1518 l_balance_type_id:=csr_balance.balance_type_id;
1519 OPEN csr_defined_balances(l_balance_type_id);
1520 FETCH csr_defined_balances INTO l_defined_balance_id;
1521 CLOSE csr_defined_balances;
1522 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1523 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1524 P_VIRTUAL_DATE=>g_end_date),0);
1525 l_601_steering_code:=l_601_steering_code+l_value;
1526 END LOOP;
1527 FOR csr_balance IN csr_balances(g_business_group_id,'810-Job Status') LOOP
1528
1529 l_balance_type_id:=csr_balance.balance_type_id;
1530 OPEN csr_defined_balances(l_balance_type_id);
1531 FETCH csr_defined_balances INTO l_defined_balance_id;
1532 CLOSE csr_defined_balances;
1533 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1534 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1535 P_VIRTUAL_DATE=>g_end_date),0);
1536 l_810_steering_code:=l_810_steering_code+l_value;
1537 END LOOP;
1538 FOR csr_balance IN csr_balances(g_business_group_id,'800-Comp. type of shift') LOOP
1539
1540 l_balance_type_id:=csr_balance.balance_type_id;
1541 OPEN csr_defined_balances(l_balance_type_id);
1542 FETCH csr_defined_balances INTO l_defined_balance_id;
1543 CLOSE csr_defined_balances;
1544 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1545 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1546 P_VIRTUAL_DATE=>g_end_date),0);
1547 l_800_steering_code:=l_800_steering_code+l_value;
1548 END LOOP;
1549 FOR csr_balance IN csr_balances(g_business_group_id,'801-Comp. post and position') LOOP
1550
1551 l_balance_type_id:=csr_balance.balance_type_id;
1552 OPEN csr_defined_balances(l_balance_type_id);
1553 FETCH csr_defined_balances INTO l_defined_balance_id;
1554 CLOSE csr_defined_balances;
1555 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1556 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1557 P_VIRTUAL_DATE=>g_end_date),0);
1558 l_801_steering_code:=l_801_steering_code+l_value;
1559 END LOOP;
1560 FOR csr_balance IN csr_balances(g_business_group_id,'802-Comp. for calving') LOOP
1561
1562 l_balance_type_id:=csr_balance.balance_type_id;
1563 OPEN csr_defined_balances(l_balance_type_id);
1564 FETCH csr_defined_balances INTO l_defined_balance_id;
1565 CLOSE csr_defined_balances;
1566 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1567 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1568 P_VIRTUAL_DATE=>g_end_date),0);
1569 l_802_steering_code:=l_802_steering_code+l_value;
1570 END LOOP;
1571 FOR csr_balance IN csr_balances(g_business_group_id,'803-Comp. for delegation') LOOP
1572
1573 l_balance_type_id:=csr_balance.balance_type_id;
1574 OPEN csr_defined_balances(l_balance_type_id);
1575 FETCH csr_defined_balances INTO l_defined_balance_id;
1576 CLOSE csr_defined_balances;
1577 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1578 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1579 P_VIRTUAL_DATE=>g_end_date),0);
1580 l_803_steering_code:=l_803_steering_code+l_value;
1581 END LOOP;
1582 FOR csr_balance IN csr_balances(g_business_group_id,'804-Comp. County, Municipal') LOOP
1583
1584 l_balance_type_id:=csr_balance.balance_type_id;
1585 OPEN csr_defined_balances(l_balance_type_id);
1586 FETCH csr_defined_balances INTO l_defined_balance_id;
1587 CLOSE csr_defined_balances;
1588 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1589 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1590 P_VIRTUAL_DATE=>g_end_date),0);
1591 l_804_steering_code:=l_804_steering_code+l_value;
1592 END LOOP;
1593 FOR csr_balance IN csr_balances(g_business_group_id,'805-Comp. for Per Diem') LOOP
1594
1595 l_balance_type_id:=csr_balance.balance_type_id;
1596 OPEN csr_defined_balances(l_balance_type_id);
1597 FETCH csr_defined_balances INTO l_defined_balance_id;
1598 CLOSE csr_defined_balances;
1599 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1600 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1601 P_VIRTUAL_DATE=>g_end_date),0);
1602 l_805_steering_code:=l_805_steering_code+l_value;
1603 END LOOP;
1604 FOR csr_balance IN csr_balances(g_business_group_id,'806-Comp. for Travel Expenses') LOOP
1605
1606 l_balance_type_id:=csr_balance.balance_type_id;
1607 OPEN csr_defined_balances(l_balance_type_id);
1608 FETCH csr_defined_balances INTO l_defined_balance_id;
1609 CLOSE csr_defined_balances;
1610 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1611 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1612 P_VIRTUAL_DATE=>g_end_date),0);
1613 l_806_steering_code:=l_806_steering_code+l_value;
1614 END LOOP;
1615 FOR csr_balance IN csr_balances(g_business_group_id,'808-Comp. for Official Duty') LOOP
1616
1617 l_balance_type_id:=csr_balance.balance_type_id;
1618 OPEN csr_defined_balances(l_balance_type_id);
1619 FETCH csr_defined_balances INTO l_defined_balance_id;
1620 CLOSE csr_defined_balances;
1621 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>l_defined_balance_id,
1622 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1623 P_VIRTUAL_DATE=>g_end_date),0);
1624 l_808_steering_code:=l_808_steering_code+l_value;
1625 END LOOP;
1626
1627 pay_action_information_api.create_action_information
1628 (p_action_information_id => l_action_info_id
1629 , p_action_context_id => p_payroll_action_id
1630 , p_action_context_type => 'PA'
1631 , p_object_version_number => l_ovn
1632 , p_effective_date => g_effective_date
1633 , p_source_id => NULL
1634 , p_source_text => NULL
1635 , p_action_information_category => 'EMEA REPORT INFORMATION'
1636 , p_action_information1 => 'PYSESSSA'
1637 , p_action_information2 => 'PER1'
1638 , p_action_information3 => l_person_id
1639 , p_action_information4 => l_person_number
1640 , p_action_information5 => l_local_unit_id
1641 , p_action_information6 => l_cfar_number
1642 , p_action_information7 => l_employee_category
1643 , p_action_information8 => l_work_type
1644 , p_action_information9 => l_agreement_code
1645 , p_action_information10 => l_ssyk_code
1646 , p_action_information11 => l_association_code
1647 , p_action_information12 => l_salary_type
1648 , p_action_information13 => l_001_steering_code
1649 , p_action_information14 => l_002_steering_code
1650 , p_action_information15 => l_003_steering_code
1651 , p_action_information16 => l_004_steering_code
1652 , p_action_information17 => l_051_steering_code
1653 , p_action_information18 => l_052_steering_code
1654 , p_action_information19 => l_053_steering_code
1655 , p_action_information20 => l_054_steering_code
1656 , p_action_information21 => l_055_steering_code
1657 , p_action_information22 => l_056_steering_code
1658 , p_action_information23 => l_058_steering_code
1659 , p_action_information24 => l_600_steering_code
1660 , p_action_information25 => l_601_steering_code
1661 , p_action_information26 => l_810_steering_code
1662 );
1663
1664 pay_action_information_api.create_action_information
1665 (p_action_information_id => l_action_info_id
1666 , p_action_context_id => p_payroll_action_id
1667 , p_action_context_type => 'PA'
1668 , p_object_version_number => l_ovn
1669 , p_effective_date => g_effective_date
1670 , p_source_id => NULL
1671 , p_source_text => NULL
1672 , p_action_information_category => 'EMEA REPORT INFORMATION'
1673 , p_action_information1 => 'PYSESSSA'
1674 , p_action_information2 => 'PER2'
1675 , p_action_information3 => l_person_id
1676 , p_action_information4 => l_local_unit_id
1677 , p_action_information5 => l_800_steering_code
1678 , p_action_information6 => l_801_steering_code
1679 , p_action_information7 => l_802_steering_code
1680 , p_action_information8 => l_803_steering_code
1681 , p_action_information9 => l_804_steering_code
1682 , p_action_information10 => l_805_steering_code
1683 , p_action_information11 => l_806_steering_code
1684 , p_action_information12 => l_808_steering_code
1685 );
1686 l_001_steering_code:=0;
1687 l_002_steering_code:=0;
1688 l_003_steering_code:=0;
1689 l_004_steering_code:=0;
1690 l_051_steering_code:=0;
1691 l_052_steering_code:=0;
1692 l_053_steering_code:=0;
1693 l_054_steering_code:=0;
1694 l_055_steering_code:=0;
1695 l_056_steering_code:=0;
1696 l_058_steering_code:=0;
1697 l_600_steering_code:=0;
1698 l_601_steering_code:=0;
1699 l_810_steering_code:=0;
1700 l_800_steering_code:=0;
1701 l_801_steering_code:=0;
1702 l_802_steering_code:=0;
1703 l_803_steering_code:=0;
1704 l_804_steering_code:=0;
1705 l_805_steering_code:=0;
1706 l_806_steering_code:=0;
1707 l_808_steering_code:=0;
1708
1709 END LOOP;
1710
1711
1712 END LOOP;
1713
1714 END LOOP;
1715
1716 END IF;
1717
1718 /*EXCEPTION
1719 WHEN OTHERS
1720 THEN
1721 IF g_debug
1722 THEN
1723 hr_utility.set_location ('error raised assignment_action_code '
1724 , 5
1725 );
1726 END IF;
1727
1728 RAISE;*/
1729 END assignment_action_code;
1730
1731 /*fffffffffffffffffffffffffff*/
1732
1733 /* INITIALIZATION CODE */
1734 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
1735 IS
1736 l_action_info_id NUMBER;
1737 l_ovn NUMBER;
1738 l_count NUMBER := 0;
1739 l_business_group_id NUMBER;
1740 l_start_date VARCHAR2 (20);
1741 l_end_date VARCHAR2 (20);
1742 l_effective_date DATE;
1743 l_payroll_id NUMBER;
1744 l_consolidation_set NUMBER;
1745 l_prev_prepay NUMBER := 0;
1746 BEGIN
1747 IF g_debug
1748 THEN
1749 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE'
1750 , 80
1751 );
1752 END IF;
1753
1754
1755 g_payroll_action_id := p_payroll_action_id;
1756 g_business_group_id := NULL;
1757 g_effective_date := NULL;
1758 g_LE_request := NULL;
1759 g_legal_employer_id := NULL;
1760 g_local_unit_id := NULL;
1761 g_account_date :=null;
1762 g_posting_date :=null;
1763
1764
1765 IF g_debug
1766 THEN
1767 hr_utility.set_location (' Leaving Procedure INITIALIZATION_CODE'
1768 , 90
1769 );
1770 END IF;
1771 EXCEPTION
1772 WHEN OTHERS
1773 THEN
1774 g_err_num := SQLCODE;
1775
1776 IF g_debug
1777 THEN
1778 hr_utility.set_location ( 'ORA_ERR: '
1779 || g_err_num
1780 || 'In INITIALIZATION_CODE'
1781 , 180
1782 );
1783 END IF;
1784 END initialization_code;
1785
1786 /* GET DEFINED BALANCE ID */
1787 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
1788 RETURN NUMBER
1789 IS
1790 /* Cursor to retrieve Defined Balance Id */
1791 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1792 IS
1793 SELECT u.creator_id
1794 FROM ff_user_entities u, ff_database_items d
1795 WHERE d.user_name = p_user_name
1796 AND u.user_entity_id = d.user_entity_id
1797 AND (u.legislation_code = 'SE')
1798 AND (u.business_group_id IS NULL)
1799 AND u.creator_type = 'B';
1800
1801 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1802 BEGIN
1803 IF g_debug
1804 THEN
1805 hr_utility.set_location
1806 (' Entering Function GET_DEFINED_BALANCE_ID'
1807 , 240
1808 );
1809 END IF;
1810
1811 OPEN csr_def_bal_id (p_user_name);
1812
1813 FETCH csr_def_bal_id
1814 INTO l_defined_balance_id;
1815
1816 CLOSE csr_def_bal_id;
1817
1818 RETURN l_defined_balance_id;
1819
1820 IF g_debug
1821 THEN
1822 hr_utility.set_location (' Leaving Function GET_DEFINED_BALANCE_ID'
1823 , 250
1824 );
1825 END IF;
1826 END get_defined_balance_id;
1827
1828 FUNCTION get_defined_balance_value (
1829 p_user_name IN VARCHAR2
1830 , p_in_assignment_id IN NUMBER
1831 , p_in_virtual_date IN DATE
1832 )
1833 RETURN NUMBER
1834 IS
1835 /* Cursor to retrieve Defined Balance Id */
1836 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1837 IS
1838 SELECT u.creator_id
1839 FROM ff_user_entities u, ff_database_items d
1840 WHERE d.user_name = p_user_name
1841 AND u.user_entity_id = d.user_entity_id
1842 AND (u.legislation_code = 'SE')
1843 AND (u.business_group_id IS NULL)
1844 AND u.creator_type = 'B';
1845
1846 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1847 l_return_balance_value NUMBER;
1848 BEGIN
1849 IF g_debug
1850 THEN
1851 hr_utility.set_location
1852 (' Entering Function GET_DEFINED_BALANCE_VALUE'
1853 , 240
1854 );
1855 END IF;
1856
1857 OPEN csr_def_bal_id (p_user_name);
1858
1859 FETCH csr_def_bal_id
1860 INTO l_defined_balance_id;
1861
1862 CLOSE csr_def_bal_id;
1863
1864 l_return_balance_value :=
1865 TO_CHAR
1866 (pay_balance_pkg.get_value
1867 (p_defined_balance_id => l_defined_balance_id
1868 , p_assignment_id => p_in_assignment_id
1869 , p_virtual_date => p_in_virtual_date
1870 )
1871 , '999999999D99'
1872 );
1873 RETURN l_return_balance_value;
1874
1875 IF g_debug
1876 THEN
1877 hr_utility.set_location
1878 (' Leaving Function GET_DEFINED_BALANCE_VALUE'
1879 , 250
1880 );
1881 END IF;
1882 END get_defined_balance_value;
1883
1884 /* ARCHIVE CODE */
1885 PROCEDURE archive_code (
1886 p_assignment_action_id IN NUMBER
1887 , p_effective_date IN DATE
1888 )
1889 IS
1890 begin
1891
1892 IF g_debug
1893 THEN
1894 hr_utility.set_location (' Leaving Procedure ARCHIVE_CODE', 390);
1895 END IF;
1896 END archive_code;
1897
1898 --- Report XML generating code
1899 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
1900 IS
1901 l_xfdf_string CLOB;
1902 l_str1 VARCHAR2 (1000);
1903 l_str2 VARCHAR2 (20);
1904 l_str3 VARCHAR2 (20);
1905 l_str4 VARCHAR2 (20);
1906 l_str5 VARCHAR2 (20);
1907 l_str6 VARCHAR2 (30);
1908 l_str7 VARCHAR2 (1000);
1909 l_str8 VARCHAR2 (240);
1910 l_str9 VARCHAR2 (240);
1911 l_str10 VARCHAR2 (20);
1912 l_str11 VARCHAR2 (20);
1913 current_index PLS_INTEGER;
1914 l_iana_charset VARCHAR2 (50);
1915 BEGIN
1916 l_iana_charset := hr_se_utility.get_iana_charset;
1917
1918 -- hr_utility.set_location ('Entering WritetoCLOB ', 70);
1919 l_str1 :=
1920 '<?xml version="1.0" encoding="'
1921 || l_iana_charset
1922 || '"?> <ROOT><SSST>';
1923 l_str2 := '<';
1924 l_str3 := '>';
1925 l_str4 := '</';
1926 l_str5 := '>';
1927 l_str6 := '</SSST></ROOT>';
1928 l_str7 :=
1929 '<?xml version="1.0" encoding="'
1930 || l_iana_charset
1931 || '"?> <ROOT></ROOT>';
1932 l_str10 := '<SSST>';
1933 l_str11 := '</SSST>';
1934 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
1935 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
1936 current_index := 0;
1937
1938 IF xml_tab.COUNT > 0
1939 THEN
1940 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
1941
1942 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST
1943 LOOP
1944 l_str8 := xml_tab (table_counter).tagname;
1945 l_str9 := xml_tab (table_counter).tagvalue;
1946
1947
1948
1949 IF l_str9 IN
1950 (
1951 'SSST_DETAILS',
1952 'END_SSST_DETAILS'
1953 )
1954 THEN
1955 IF l_str9 IN
1956 ('SSST_DETAILS')
1957 THEN
1958 DBMS_LOB.writeappend (l_xfdf_string
1959 , LENGTH (l_str2)
1960 , l_str2
1961 );
1962 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1963 , l_str8);
1964 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1965 , l_str3);
1966 ELSE
1967 DBMS_LOB.writeappend (l_xfdf_string
1968 , LENGTH (l_str4)
1969 , l_str4
1970 );
1971 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1972 , l_str8);
1973 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1974 , l_str5);
1975 END IF;
1976 ELSE
1977 IF l_str9 IS NOT NULL
1978 THEN
1979 DBMS_LOB.writeappend (l_xfdf_string
1980 , LENGTH (l_str2)
1981 , l_str2
1982 );
1983 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1984 , l_str8);
1985 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1986 , l_str3);
1987 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9)
1988 , l_str9);
1989 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1990 , l_str4);
1991 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1992 , l_str8);
1993 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1994 , l_str5);
1995 ELSE
1996 DBMS_LOB.writeappend (l_xfdf_string
1997 , LENGTH (l_str2)
1998 , l_str2
1999 );
2000 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
2001 , l_str8);
2002 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
2003 , l_str3);
2004 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
2005 , l_str4);
2006 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
2007 , l_str8);
2008 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
2009 , l_str5);
2010 END IF;
2011 END IF;
2012 END LOOP;
2013
2014 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
2015 ELSE
2016 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
2017 END IF;
2018 p_xfdf_clob := l_xfdf_string;
2019 -- hr_utility.set_location ('Leaving WritetoCLOB ', 40);
2020 EXCEPTION
2021 WHEN OTHERS
2022 THEN
2023 hr_utility.TRACE ('sqlerrm ' || SQLERRM);
2024 hr_utility.raise_error;
2025 END writetoclob;
2026
2027 -----------------------------------------------------------------------------------
2028 -- POPULATE_DATA_DETAIL generates xml for the reports.
2029 -----------------------------------------------------------------------------------
2030 --
2031 --
2032 PROCEDURE POPULATE_DATA_DETAIL
2033 (p_business_group_id IN NUMBER,
2034 p_payroll_action_id IN VARCHAR2 ,
2035 p_template_name IN VARCHAR2,
2036 p_xml OUT NOCOPY CLOB)
2037 IS
2038
2039
2040 /* Cursor to fetch Header Information */
2041
2042 l_employee_category hr_lookups.meaning%type;
2043 l_weekend_pay_duty hr_lookups.meaning%type;
2044 l_working_hour_code hr_lookups.lookup_code%type;
2045 l_lu_salary NUMBER;
2046 l_salary NUMBER:=0;
2047 l_grand_salary NUMBER:=0;
2048 l_legal_employer hr_organization_units.name%type;
2049 l_local_unit hr_organization_units.name%type;
2050 l_action_information_id pay_action_information.action_information_id%TYPE;
2051 l_month varchar2(20);
2052 l_year NUMBER;
2053 l_retroactive_date_from DATE;
2054 l_retroactive_date_to DATE;
2055 l_bh_worked_calendar_month NUMBER;
2056 l_bh_worked_payment_period NUMBER;
2057 l_bh_pbt_value NUMBER;
2058 l_bh_pcow_value NUMBER;
2059 l_bh_nha_value NUMBER;
2060 l_bh_nho_value NUMBER;
2061 l_bh_retroactive_pay NUMBER;
2062 l_bh_ppo_value NUMBER;
2063 l_bh_sick_pay NUMBER;
2064 l_bh_total_employees NUMBER;
2065 l_bs_gross_pay NUMBER;
2066 l_bs_working_agreement NUMBER;
2067 l_bs_tcdp_value NUMBER;
2068 l_bs_tcow_value NUMBER;
2069 l_bs_nha_value NUMBER;
2070 l_bs_nho_value NUMBER;
2071 l_bs_retroactive_pay NUMBER;
2072 l_bs_ppo_value NUMBER;
2073 l_bs_sick_pay NUMBER;
2074 l_bs_total_employees NUMBER;
2075 l_ws_full_time_employee NUMBER;
2076 l_ws_gross_pay NUMBER;
2077 l_ws_working_agreement NUMBER;
2078 l_ws_tcdp_value NUMBER;
2079 l_ws_tcow_value NUMBER;
2080 l_ws_nha_value NUMBER;
2081 l_ws_nho_value NUMBER;
2082 l_ws_retroactive_pay NUMBER;
2083 l_ws_ppo_value NUMBER;
2084 l_ws_sick_pay NUMBER;
2085 l_ws_total_employees NUMBER;
2086 l_wh_full_time_employee NUMBER;
2087 l_wh_gross_pay NUMBER;
2088 l_wh_working_agreement NUMBER;
2089 l_wh_tcdp_value NUMBER;
2090 l_wh_retroactive_pay NUMBER;
2091 l_wh_ppo_value NUMBER;
2092 l_wh_sick_pay NUMBER;
2093 l_wh_total_employees NUMBER;
2094
2095
2096
2097
2098
2099 CURSOR csr_report_year(csr_v_payroll_action_id number)
2100 IS
2101 SELECT pai.action_information6 Report_Year
2102 FROM
2103 pay_action_information pai,
2104 pay_payroll_actions ppa
2105 WHERE
2106 ppa.payroll_action_id=csr_v_payroll_action_id--175110 --csr_v_payroll_action_id
2107 AND ppa.payroll_action_id=pai.action_context_id
2108 AND pai.action_context_type = 'PA'
2109 AND pai.action_information1 = 'PYSESSSA'
2110 AND pai.action_information_category = 'EMEA REPORT DETAILS';
2111
2112 CURSOR csr_salary_statistics(csr_v_payroll_action_id number)
2113 IS
2114 SELECT pai1.action_information3 legal_employer_id,
2115 pai2.action_information3 local_unit_id,
2116 pai3.action_information3 person_id,
2117 pai1.action_information6 Membership_Number,
2118 pai1.action_information7 Working_Site_Number,
2119 pai1.action_information5 Organization_Id,
2120 pai1.action_information8 Association_Number,
2121 pai3.action_information9 Agreement_Code,
2122 pai3.action_information4 Person_Number,
2123 pai3.action_information7 Employee_Category,
2124 pai3.action_information8 Working_Hours_Type,
2125 pai3.action_information10 SSYK_Code,
2126 pai3.action_information11 Association_Code,
2127 pai3.action_information12 Salary_Type,
2128 pai2.action_information5 CFAR_Number,
2129 pai1.action_information10 Weekend_Pay_Duty,
2130 decode(nvl(pai3.action_information13,0) ,0,'0000000',pai3.action_information13) Steering_Code_001,
2131 decode(nvl(pai3.action_information14,0) ,0,'0000000',pai3.action_information14) Steering_Code_002,
2132 decode(nvl(pai3.action_information15,0) ,0,'0000000',pai3.action_information15) Steering_Code_003,
2133 decode(nvl(pai3.action_information16,0) ,0,'0000000',pai3.action_information16) Steering_Code_004,
2134 decode(nvl(pai3.action_information17,0) ,0,'0000000',pai3.action_information17) Steering_Code_051,
2135 decode(nvl(pai3.action_information18,0) ,0,'0000000',pai3.action_information18) Steering_Code_052,
2136 decode(nvl(pai3.action_information19,0) ,0,'0000000',pai3.action_information19) Steering_Code_053,
2137 decode(nvl(pai3.action_information20,0) ,0,'0000000',pai3.action_information20) Steering_Code_054,
2138 decode(nvl(pai3.action_information21,0) ,0,'0000000',pai3.action_information21) Steering_Code_055,
2139 decode(nvl(pai3.action_information22,0) ,0,'0000000',pai3.action_information22) Steering_Code_056,
2140 decode(nvl(pai3.action_information23,0) ,0,'0000000',pai3.action_information23) Steering_Code_058,
2141 decode(nvl(pai3.action_information24,0) ,0,'0000000',pai3.action_information24) Steering_Code_600,
2142 decode(nvl(pai3.action_information25,0) ,0,'0000000',pai3.action_information25) Steering_Code_601,
2143 decode(nvl(pai3.action_information26,0) ,0,'0000000',pai3.action_information26) Steering_Code_810,
2144 decode(nvl(pai4.action_information5,0) ,0,'0000000',pai4.action_information5) Steering_Code_800,
2145 decode(nvl(pai4.action_information6,0) ,0,'0000000',pai4.action_information6) Steering_Code_801,
2146 decode(nvl(pai4.action_information7,0) ,0,'0000000',pai4.action_information7) Steering_Code_802,
2147 decode(nvl(pai4.action_information8,0) ,0,'0000000',pai4.action_information8) Steering_Code_803,
2148 decode(nvl(pai4.action_information9,0) ,0,'0000000',pai4.action_information9) Steering_Code_804,
2149 decode(nvl(pai4.action_information10,0) ,0,'0000000',pai4.action_information10) Steering_Code_805,
2150 decode(nvl(pai4.action_information11,0) ,0,'0000000',pai4.action_information11) Steering_Code_806,
2151 decode(nvl(pai4.action_information12,0) ,0,'0000000',pai4.action_information12) Steering_Code_808
2152 FROM
2153 pay_action_information pai1,
2154 pay_action_information pai2,
2155 pay_action_information pai3,
2156 pay_action_information pai4,
2157 pay_payroll_actions ppa
2158 WHERE
2159 ppa.payroll_action_id=csr_v_payroll_action_id --175110 --175079 --175068 --csr_v_payroll_action_id
2160 AND ppa.payroll_action_id=pai1.action_context_id
2161 AND pai1.action_context_id=pai2.action_context_id
2162 AND pai2.action_context_id=pai3.action_context_id
2163 and pai3.action_context_id=pai4.action_context_id
2164 and pai4.action_context_id=ppa.payroll_action_id
2165 --AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
2166 AND pai1.action_context_type='PA'
2167 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
2168 AND pai1.action_information1 = 'PYSESSSA'
2169 --AND pai1.action_information_id=csr_v_action_information_id
2170 AND pai1.action_information2='LE'
2171 AND pai1.action_information3=pai2.action_information4
2172 AND pai2.action_context_type='PA'
2173 AND pai2.action_information2 = 'LU'
2174 AND pai2.action_information1 = 'PYSESSSA'
2175 AND pai2.action_information_category = 'EMEA REPORT INFORMATION'
2176 AND pai2.action_information3=pai3.action_information5
2177 AND pai3.action_context_type='PA'
2178 AND pai3.action_information2 = 'PER1'
2179 AND pai3.action_information1 = 'PYSESSSA'
2180 AND pai3.action_information_category = 'EMEA REPORT INFORMATION'
2181 AND pai2.action_information3=pai4.action_information4
2182 AND pai3.action_information5=pai4.action_information4
2183 AND pai3.action_information3=pai4.action_information3
2184 /* Bug Fix 6209364 */
2185 AND pai3.action_information9 IN (SELECT
2186 hl.meaning Agreement_Code
2187 FROM hr_organization_units o1
2188 , hr_organization_information hoi1
2189 ,hr_lookups hl
2190 WHERE o1.business_group_id =g_business_group_id
2191 AND hoi1.organization_id = o1.organization_id
2192 AND hoi1.organization_id =pai1.action_information3
2193 AND hoi1.ORG_INFORMATION_CONTEXT='SE_SALARY_STRUCTURE'
2194 AND hl.lookup_type ='SE_AGREEMENT_CODE'
2195 AND hl.LOOKUP_CODE=hoi1.org_information3 )
2196 --=pai1.action_information9 /*linking the agreement code b/w assignment and Legal employer */
2197 AND pai4.action_context_type='PA'
2198 AND pai4.action_information2 = 'PER2'
2199 AND pai4.action_information1 = 'PYSESSSA'
2200 AND pai4.action_information_category = 'EMEA REPORT INFORMATION'
2201 ORDER BY pai1.action_information3,
2202 pai2.action_information3,
2203 pai3.action_information9,
2204 pai3.action_information3;
2205
2206 CURSOR csr_local_unit_count(csr_v_payroll_action_id number, csr_v_CFAR_Number VARCHAR2)
2207 IS
2208 SELECT nvl(COUNT(*),0) Local_Unit_Count
2209 FROM
2210 pay_action_information pai,
2211 pay_action_information pai1,
2212 pay_payroll_actions ppa
2213 WHERE
2214 ppa.payroll_action_id=csr_v_payroll_action_id --175110 --csr_v_payroll_action_id
2215 AND ppa.payroll_action_id=pai.action_context_id
2216 AND pai.action_context_id=pai1.action_context_id
2217 AND pai1.action_context_id=ppa.payroll_action_id
2218 AND pai.action_context_type = 'PA'
2219 AND pai.action_information1 = 'PYSESSSA'
2220 AND pai.action_information2 = 'LU'
2221 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
2222 AND pai.action_information5=csr_v_CFAR_Number --312
2223 AND pai.action_information3= pai1.action_information5
2224 AND pai1.action_context_type = 'PA'
2225 AND pai1.action_information1 = 'PYSESSSA'
2226 AND pai1.action_information2 = 'PER1'
2227 AND pai1.action_information_category = 'EMEA REPORT INFORMATION';
2228
2229 CURSOR csr_lookup_values(csr_v_lookup_type varchar2, csr_v_lookup_code varchar2)
2230 IS
2231 SELECT meaning FROM
2232 hr_lookups WHERE lookup_type =csr_v_lookup_type-- 'EMPLOYEE_CATG'
2233 AND lookup_code=csr_v_lookup_code; -- 'WC'
2234
2235 CURSOR csr_lookup_code(csr_v_lookup_type varchar2, csr_v_lookup_meaning varchar2)
2236 IS
2237 SELECT lookup_code FROM
2238 hr_lookups WHERE lookup_type =csr_v_lookup_type-- 'EMPLOYEE_CATG'
2239 AND meaning=csr_v_lookup_meaning; -- 'WC'
2240
2241 CURSOR csr_agreement_legal (csr_v_legal_employer NUMBER, csr_v_agreement VARCHAR2 )
2242 IS
2243 SELECT
2244 hoi1.ORG_INFORMATION1 Working_Site_Number,
2245 hoi1.ORG_INFORMATION2 Association_Number,
2246 hoi1.ORG_INFORMATION4 Weekend_Pay_Duty
2247 FROM hr_organization_units o1
2248 , hr_organization_information hoi1
2249 ,hr_lookups hl
2250 WHERE o1.business_group_id =g_business_group_id --3133 --3133 --csr_v_business_group_id --3133 --l_business_group_id
2251 AND hoi1.organization_id = o1.organization_id
2252 AND hoi1.organization_id =csr_v_legal_employer --3134 --pai1.action_information3 --csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
2253 AND hoi1.ORG_INFORMATION_CONTEXT='SE_SALARY_STRUCTURE'
2254 AND hl.lookup_type ='SE_AGREEMENT_CODE'
2255 AND hl.MEANING=csr_v_agreement --'333'
2256 AND hl.lookup_code=hoi1.ORG_INFORMATION3;
2257
2258
2259
2260 l_counter NUMBER:=0;
2261 l_total NUMBER;
2262 l_total_eft NUMBER;
2263 l_count NUMBER;
2264 l_payroll_action_id NUMBER;
2265 l_lu_counter_reset VARCHAR2(10);
2266 l_prev_local_unit VARCHAR2(15);
2267 l_report_date DATE;
2268 l_person_number VARCHAR2(50);
2269 l_local_unit_id hr_organization_units.organization_id%type;
2270 l_period varchar2(50);
2271 l_report_year number(4);
2272 l_local_count NUMBER;
2273 l_CFAR_Number NUMBER;
2274 l_legal_employer_id hr_organization_units.organization_id%TYPE;
2275 l_working_hour_meaning hr_lookups.meaning%type;
2276 l_association_number varchar2(10);
2277 l_worksite_number varchar2(10);
2278 l_agreement_code hr_lookups.meaning%TYPE;
2279
2280
2281 --l_local_unit hr_organization_units.name%TYPE;
2282
2283 BEGIN
2284
2285
2286 IF p_payroll_action_id IS NULL THEN
2287 BEGIN
2288 SELECT payroll_action_id
2289 INTO l_payroll_action_id
2290 FROM pay_payroll_actions ppa,
2291 fnd_conc_req_summary_v fcrs,
2292 fnd_conc_req_summary_v fcrs1
2293 WHERE fcrs.request_id = fnd_global.conc_request_id
2294 AND fcrs.priority_request_id = fcrs1.priority_request_id
2295 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
2296 AND ppa.request_id = fcrs1.request_id;
2297 EXCEPTION
2298 WHEN OTHERS THEN
2299 NULL;
2300 END ;
2301 ELSE
2302 l_payroll_action_id := p_payroll_action_id;
2303 END IF;
2304 g_payroll_action_id :=p_payroll_action_id;
2305 /* g_business_group_id := null;
2306 g_legal_employer_id := null;
2307 g_start_date := null;
2308 g_end_date := null;
2309 g_version := null;
2310 g_archive := null;*/
2311
2312 get_all_parameters (l_payroll_action_id
2313 , g_business_group_id
2314 , g_effective_date
2315 , g_legal_employer_id
2316 , g_LE_request
2317 , g_month
2318 , g_report_year
2319 );
2320
2321
2322 hr_utility.set_location('Entered Procedure GETDATA',10);
2323
2324 /* xml_tab(l_counter).TagName :='LU_DETAILS';
2325 xml_tab(l_counter).TagValue :='LU_DETAILS';*/
2326 /* l_counter:=l_counter+1;*/
2327
2328 /* Get the File Header Information */
2329 OPEN csr_report_year(l_payroll_action_id);
2330 FETCH csr_report_year INTO l_report_year;
2331 CLOSE csr_report_year;
2332
2333 hr_utility.set_location('Before populating pl/sql table',20);
2334 FOR csr_salary IN csr_salary_statistics(l_payroll_action_id) LOOP
2335
2336 xml_tab(l_counter).TagName :='SSST_DETAILS';
2337 xml_tab(l_counter).TagValue :='SSST_DETAILS';
2338 l_counter:=l_counter+1;
2339
2340 xml_tab(l_counter).TagName :='REPORT_YEAR';
2341 xml_tab(l_counter).TagValue :=l_report_year; --csr_cat.legal_employer;
2342 l_counter:=l_counter+1;
2343
2344 xml_tab(l_counter).TagName :='MEMBERSHIP_NUMBER';
2345 xml_tab(l_counter).TagValue :=csr_salary.Membership_Number; --csr_cat.local_unit;
2346 l_counter:=l_counter+1;
2347 l_legal_employer_id:=csr_salary.legal_employer_id;
2348 l_agreement_code:=csr_salary.Agreement_Code;
2349
2350 OPEN csr_agreement_legal (l_legal_employer_id , l_agreement_code);
2351 FETCH csr_agreement_legal INTO l_worksite_number,l_association_number,l_weekend_pay_duty;
2352 CLOSE csr_agreement_legal;
2353 xml_tab(l_counter).TagName :='WORKING_SITE_NUMBER';
2354 xml_tab(l_counter).TagValue :=l_worksite_number; --csr_salary.Working_Site_Number; --csr_cat.month;
2355 l_counter:=l_counter+1;
2356
2357 xml_tab(l_counter).TagName :='ORGANIZATION_ID';
2358 xml_tab(l_counter).TagValue :=csr_salary.Organization_Id; --csr_cat.retroactive_date_from;
2359 l_counter:=l_counter+1;
2360
2361 xml_tab(l_counter).TagName :='ASSOCIATION_NUMBER';
2362 xml_tab(l_counter).TagValue :=l_association_number; --csr_salary.Association_Number; --csr_cat.retroactive_date_to;
2363 l_counter:=l_counter+1;
2364
2365 xml_tab(l_counter).TagName :='AGREEMENT_CODE';
2366 xml_tab(l_counter).TagValue :=csr_salary.Agreement_Code; --csr_cat.bh_worked_calendar_month;
2367 l_counter:=l_counter+1;
2368
2369 xml_tab(l_counter).TagName :='PERSON_NUMBER';
2370 xml_tab(l_counter).TagValue :=csr_salary.Person_Number; --csr_cat.bh_worked_payment_period;
2371 l_counter:=l_counter+1;
2372
2373 l_employee_category:=csr_salary.Employee_Category;
2374 xml_tab(l_counter).TagName :='EMPLOYEE_CATG';
2375
2376 IF l_employee_category='BC' THEN
2377 xml_tab(l_counter).TagValue :=1; --csr_salary.Employee_Category; --csr_cat.bh_pbt_value;
2378 ELSIF l_employee_category='WC' THEN
2379 xml_tab(l_counter).TagValue :=2;
2380 END IF;
2381 l_counter:=l_counter+1;
2382 OPEN csr_lookup_values('EMPLOYEE_CATG',l_employee_category );
2383 FETCH csr_lookup_values INTO l_employee_category;
2384 CLOSE csr_lookup_values;
2385
2386 xml_tab(l_counter).TagName :='EMPLOYEE_CATEGORY';
2387 xml_tab(l_counter).TagValue :=l_employee_category; --csr_salary.Employee_Category; --csr_cat.bh_pbt_value;
2388 l_counter:=l_counter+1;
2389
2390 l_working_hour_code:=csr_salary.Working_Hours_Type;
2391
2392 OPEN csr_lookup_code('SE_WORKING_HOUR_TYPE', l_working_hour_code);
2393 FETCH csr_lookup_code INTO l_working_hour_code;
2394 CLOSE csr_lookup_code;
2395
2396 xml_tab(l_counter).TagName :='WORKING_HOURS';
2397 xml_tab(l_counter).TagValue :=l_working_hour_code;-- csr_salary.Working_Hours_Type; --csr_cat.bh_pcow_value;
2398 l_counter:=l_counter+1;
2399
2400 xml_tab(l_counter).TagName :='WORKING_HOURS_TYPE';
2401 xml_tab(l_counter).TagValue :=csr_salary.Working_Hours_Type; --csr_cat.bh_pcow_value;
2402 l_counter:=l_counter+1;
2403
2404 xml_tab(l_counter).TagName :='SSYK_CODE';
2405 xml_tab(l_counter).TagValue :=csr_salary.SSYK_Code; --csr_cat.bh_nha_value;
2406 l_counter:=l_counter+1;
2407
2408 xml_tab(l_counter).TagName :='ASSOCIATION_CODE';
2409 xml_tab(l_counter).TagValue :=csr_salary.Association_Code; --csr_cat.bh_nho_value;
2410 l_counter:=l_counter+1;
2411
2412 xml_tab(l_counter).TagName :='SALARY_TYPE';
2413 xml_tab(l_counter).TagValue :=csr_salary.Salary_Type; --csr_cat.bh_retroactive_pay;
2414 l_counter:=l_counter+1;
2415
2416 l_CFAR_Number:=csr_salary.CFAR_Number;
2417
2418 open csr_local_unit_count(l_payroll_action_id, l_CFAR_Number);
2419 FETCH csr_local_unit_count INTO l_local_count;
2420 CLOSE csr_local_unit_count;
2421
2422 xml_tab(l_counter).TagName :='LOCAL_COUNT';
2423 xml_tab(l_counter).TagValue :=l_local_count; --csr_cat.bh_ppo_value;
2424 l_counter:=l_counter+1;
2425
2426 xml_tab(l_counter).TagName :='CFAR_NUMBER';
2427 xml_tab(l_counter).TagValue :=csr_salary.CFAR_Number; --csr_cat.bh_sick_pay;
2428 l_counter:=l_counter+1;
2429
2430 xml_tab(l_counter).TagName :='WEEKEND_PAY';
2431 IF /*csr_salary.Weekend_Pay_Duty*/l_weekend_pay_duty='Y' THEN
2432 xml_tab(l_counter).TagValue :=1;-- csr_salary.Weekend_Pay_Duty; --csr_cat.bh_total_employees;
2433 ELSE
2434 xml_tab(l_counter).TagValue :=2;
2435 END IF;
2436 l_counter:=l_counter+1;
2437
2438 --l_weekend_pay_duty:=csr_salary.Weekend_Pay_Duty;
2439
2440 OPEN csr_lookup_values('YES_NO',l_weekend_pay_duty );
2441 FETCH csr_lookup_values INTO l_weekend_pay_duty;
2442 CLOSE csr_lookup_values;
2443
2444 xml_tab(l_counter).TagName :='WEEKEND_PAY_DUTY';
2445 xml_tab(l_counter).TagValue :=l_weekend_pay_duty; --csr_salary.Weekend_Pay_Duty; --csr_cat.bh_total_employees;
2446 l_counter:=l_counter+1;
2447
2448 xml_tab(l_counter).TagName :='ZERO_FILLING';
2449 xml_tab(l_counter).TagValue :='000000';
2450 l_counter:=l_counter+1;
2451
2452 xml_tab(l_counter).TagName :='STEERING_CODE_001';
2453 xml_tab(l_counter).TagValue :='001'; --csr_cat.bs_gross_pay;
2454 l_counter:=l_counter+1;
2455
2456 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_001';
2457 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_001,2); --csr_cat.bs_gross_pay;
2458 l_counter:=l_counter+1;
2459
2460 xml_tab(l_counter).TagName :='STEERING_VALUE_001';
2461 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_001,2); --csr_cat.bs_gross_pay;
2462 l_counter:=l_counter+1;
2463
2464 xml_tab(l_counter).TagName :='STEERING_CODE_002';
2465 xml_tab(l_counter).TagValue :='002'; --csr_cat.bs_working_agreement;
2466 l_counter:=l_counter+1;
2467
2468 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_002';
2469 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_002,2); --csr_cat.bs_working_agreement;
2470 l_counter:=l_counter+1;
2471
2472 xml_tab(l_counter).TagName :='STEERING_VALUE_002';
2473 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_002,2); --csr_cat.bs_working_agreement;
2474 l_counter:=l_counter+1;
2475
2476 xml_tab(l_counter).TagName :='STEERING_CODE_003';
2477 xml_tab(l_counter).TagValue :='003'; --csr_cat.bs_tcdp_value;
2478 l_counter:=l_counter+1;
2479
2480 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_003';
2481 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_003,2); --csr_cat.bs_tcdp_value;
2482 l_counter:=l_counter+1;
2483
2484 xml_tab(l_counter).TagName :='STEERING_VALUE_003';
2485 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_003,2); --csr_cat.bs_tcdp_value;
2486 l_counter:=l_counter+1;
2487
2488 xml_tab(l_counter).TagName :='STEERING_CODE_004';
2489 xml_tab(l_counter).TagValue :='004';
2490 l_counter:=l_counter+1;
2491
2492 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_004';
2493 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_004,2); --csr_cat.bs_tcow_value;
2494 l_counter:=l_counter+1;
2495
2496 xml_tab(l_counter).TagName :='STEERING_VALUE_004';
2497 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_004,2); --csr_cat.bs_tcow_value;
2498 l_counter:=l_counter+1;
2499
2500 xml_tab(l_counter).TagName :='STEERING_CODE_051';
2501 xml_tab(l_counter).TagValue :='051'; --csr_cat.bs_nha_value;
2502 l_counter:=l_counter+1;
2503
2504 IF csr_salary.Salary_Type=3 THEN
2505 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_051';
2506 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_051,2); --csr_cat.bs_nha_value;
2507 l_counter:=l_counter+1;
2508
2509 xml_tab(l_counter).TagName :='STEERING_VALUE_051';
2510 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_051,2); --csr_cat.bs_nha_value;
2511 l_counter:=l_counter+1;
2512 ELSE
2513 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_051';
2514 IF csr_salary.Steering_Code_051 > 0 THEN
2515 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_051); --csr_cat.bs_nha_value;
2516 ELSE
2517 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_051;
2518 END IF;
2519 l_counter:=l_counter+1;
2520
2521 xml_tab(l_counter).TagName :='STEERING_VALUE_051';
2522 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_051); --csr_cat.bs_nha_value;
2523 l_counter:=l_counter+1;
2524 END IF;
2525
2526 xml_tab(l_counter).TagName :='STEERING_CODE_052';
2527 xml_tab(l_counter).TagValue :='052'; --csr_cat.bs_nho_value;
2528 l_counter:=l_counter+1;
2529
2530 IF csr_salary.Salary_Type=3 THEN
2531
2532 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_052';
2533 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_052,2); --csr_cat.bs_nho_value;
2534 l_counter:=l_counter+1;
2535
2536 xml_tab(l_counter).TagName :='STEERING_VALUE_052';
2537 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_052,2); --csr_cat.bs_nho_value;
2538 l_counter:=l_counter+1;
2539
2540 ELSE
2541 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_052';
2542 IF csr_salary.Steering_Code_052 > 0 THEN
2543 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_052); --csr_cat.bs_nho_value;
2544 ELSE
2545 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_052; --csr_cat.bs_nho_value;
2546 END IF;
2547 l_counter:=l_counter+1;
2548
2549 xml_tab(l_counter).TagName :='STEERING_VALUE_052';
2550 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_052); --csr_cat.bs_nho_value;
2551 l_counter:=l_counter+1;
2552 END IF;
2553
2554 xml_tab(l_counter).TagName :='STEERING_CODE_053';
2555 xml_tab(l_counter).TagValue :='053'; --csr_cat.bs_retroactive_pay;
2556 l_counter:=l_counter+1;
2557
2558 IF csr_salary.Salary_Type=3 THEN
2559
2560 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_053';
2561 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_053,2); --csr_cat.bs_retroactive_pay;
2562 l_counter:=l_counter+1;
2563
2564 xml_tab(l_counter).TagName :='STEERING_VALUE_053';
2565 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_053,2); --csr_cat.bs_retroactive_pay;
2566 l_counter:=l_counter+1;
2567
2568 ELSE
2569
2570 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_053';
2571 IF csr_salary.Steering_Code_053 > 0 THEN
2572 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_053); --csr_cat.bs_retroactive_pay;
2573 ELSE
2574 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_053; --csr_cat.bs_retroactive_pay;
2575 END IF;
2576 l_counter:=l_counter+1;
2577
2578 xml_tab(l_counter).TagName :='STEERING_VALUE_053';
2579 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_053); --csr_cat.bs_retroactive_pay;
2580 l_counter:=l_counter+1;
2581
2582 END IF;
2583
2584 xml_tab(l_counter).TagName :='STEERING_CODE_054';
2585 xml_tab(l_counter).TagValue :='054'; --csr_cat.bs_ppo_value;
2586 l_counter:=l_counter+1;
2587
2588 IF csr_salary.Salary_Type=3 THEN
2589
2590 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_054';
2591 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_054,2); --csr_cat.bs_ppo_value;
2592 l_counter:=l_counter+1;
2593
2594 xml_tab(l_counter).TagName :='STEERING_VALUE_054';
2595 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_054,2); --csr_cat.bs_ppo_value;
2596 l_counter:=l_counter+1;
2597
2598 ELSE
2599
2600 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_054';
2601 IF csr_salary.Steering_Code_054 > 0 THEN
2602 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_054); --csr_cat.bs_ppo_value;
2603 ELSE
2604 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_054; --csr_cat.bs_ppo_value;
2605 END IF;
2606 l_counter:=l_counter+1;
2607
2608 xml_tab(l_counter).TagName :='STEERING_VALUE_054';
2609 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_054); --csr_cat.bs_ppo_value;
2610 l_counter:=l_counter+1;
2611
2612 END IF;
2613
2614 xml_tab(l_counter).TagName :='STEERING_CODE_055';
2615 xml_tab(l_counter).TagValue :='055'; --csr_cat.bs_sick_pay;
2616 l_counter:=l_counter+1;
2617 IF csr_salary.Salary_Type=3 THEN
2618
2619 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_055';
2620 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_055,2); --csr_cat.bs_sick_pay;
2621 l_counter:=l_counter+1;
2622
2623 xml_tab(l_counter).TagName :='STEERING_VALUE_055';
2624 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_055,2); --csr_cat.bs_sick_pay;
2625 l_counter:=l_counter+1;
2626
2627 ELSE
2628
2629 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_055';
2630 IF csr_salary.Steering_Code_055 > 0 THEN
2631 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_055); --csr_cat.bs_sick_pay;
2632 ELSE
2633 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_055; --csr_cat.bs_sick_pay;
2634 END IF;
2635 l_counter:=l_counter+1;
2636
2637 xml_tab(l_counter).TagName :='STEERING_VALUE_055';
2638 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_055); --csr_cat.bs_sick_pay;
2639 l_counter:=l_counter+1;
2640
2641 END IF;
2642
2643
2644 xml_tab(l_counter).TagName :='STEERING_CODE_056';
2645 xml_tab(l_counter).TagValue :='056'; --csr_cat.bs_total_employees;
2646 l_counter:=l_counter+1;
2647
2648 IF csr_salary.Salary_Type=3 THEN
2649
2650 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_056';
2651 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_056,2); --csr_cat.bs_total_employees;
2652 l_counter:=l_counter+1;
2653
2654 xml_tab(l_counter).TagName :='STEERING_VALUE_056';
2655 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_056,2); --csr_cat.bs_total_employees;
2656 l_counter:=l_counter+1;
2657
2658 ELSE
2659
2660 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_056';
2661 IF csr_salary.Steering_Code_056 > 0 THEN
2662 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_056); --csr_cat.bs_total_employees;
2663 ELSE
2664 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_056; --csr_cat.bs_total_employees;
2665 END IF;
2666 l_counter:=l_counter+1;
2667
2668 xml_tab(l_counter).TagName :='STEERING_VALUE_056';
2669 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_056); --csr_cat.bs_total_employees;
2670 l_counter:=l_counter+1;
2671
2672 END IF;
2673 xml_tab(l_counter).TagName :='STEERING_CODE_058';
2674 xml_tab(l_counter).TagValue :='058'; --round(csr_cat.ws_full_time_employee,2);
2675 l_counter:=l_counter+1;
2676
2677 IF csr_salary.Salary_Type=3 THEN
2678
2679 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_058';
2680 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_058,2); --round(csr_cat.ws_full_time_employee,2);
2681 l_counter:=l_counter+1;
2682
2683 xml_tab(l_counter).TagName :='STEERING_VALUE_058';
2684 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_058,2); --round(csr_cat.ws_full_time_employee,2);
2685 l_counter:=l_counter+1;
2686
2687 ELSE
2688
2689 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_058';
2690 IF csr_salary.Steering_Code_058 > 0 THEN
2691 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_058); --round(csr_cat.ws_full_time_employee,2);
2692 ELSE
2693 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_058; --round(csr_cat.ws_full_time_employee,2);
2694 END IF;
2695 l_counter:=l_counter+1;
2696
2697 xml_tab(l_counter).TagName :='STEERING_VALUE_058';
2698 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_058); --round(csr_cat.ws_full_time_employee,2);
2699 l_counter:=l_counter+1;
2700
2701 END IF;
2702
2703 xml_tab(l_counter).TagName :='STEERING_CODE_600';
2704 xml_tab(l_counter).TagValue :='600'; --csr_cat.ws_gross_pay;
2705 l_counter:=l_counter+1;
2706
2707 IF csr_salary.Salary_Type=3 THEN
2708
2709 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_600';
2710 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_600,2); --csr_cat.ws_gross_pay;
2711 l_counter:=l_counter+1;
2712
2713 xml_tab(l_counter).TagName :='STEERING_VALUE_600';
2714 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_600,2); --csr_cat.ws_gross_pay;
2715 l_counter:=l_counter+1;
2716
2717 ELSE
2718
2719 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_600';
2720 IF csr_salary.Steering_Code_600 > 0 THEN
2721 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_600); --csr_cat.ws_gross_pay;
2722 ELSE
2723 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_600; --csr_cat.ws_gross_pay;
2724 END IF;
2725 l_counter:=l_counter+1;
2726
2727 xml_tab(l_counter).TagName :='STEERING_VALUE_600';
2728 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_600); --csr_cat.ws_gross_pay;
2729 l_counter:=l_counter+1;
2730
2731 END IF;
2732
2733 xml_tab(l_counter).TagName :='STEERING_CODE_601';
2734 xml_tab(l_counter).TagValue :='601'; --csr_cat.ws_working_agreement;
2735 l_counter:=l_counter+1;
2736
2737 IF csr_salary.Salary_Type=3 THEN
2738
2739 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_601';
2740 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_601,2); --csr_cat.ws_working_agreement;
2741 l_counter:=l_counter+1;
2742
2743 xml_tab(l_counter).TagName :='STEERING_VALUE_601';
2744 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_601,2); --csr_cat.ws_working_agreement;
2745 l_counter:=l_counter+1;
2746
2747 ELSE
2748 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_601';
2749 IF csr_salary.Steering_Code_601 > 0 THEN
2750 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_601); --csr_cat.ws_working_agreement;
2751 ELSE
2752 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_601; --csr_cat.ws_working_agreement;
2753 END IF;
2754 l_counter:=l_counter+1;
2755
2756 xml_tab(l_counter).TagName :='STEERING_VALUE_601';
2757 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_601); --csr_cat.ws_working_agreement;
2758 l_counter:=l_counter+1;
2759 END IF;
2760 xml_tab(l_counter).TagName :='STEERING_CODE_810';
2761 xml_tab(l_counter).TagValue :='810'; --csr_cat.ws_tcdp_value;
2762 l_counter:=l_counter+1;
2763
2764 IF csr_salary.Salary_Type=3 THEN
2765
2766 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_810';
2767 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_810,2); --csr_cat.ws_tcdp_value;
2768 l_counter:=l_counter+1;
2769
2770 xml_tab(l_counter).TagName :='STEERING_VALUE_810';
2771 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_810,2); --csr_cat.ws_tcdp_value;
2772 l_counter:=l_counter+1;
2773
2774 ELSE
2775 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_810';
2776 IF csr_salary.Steering_Code_810 > 0 THEN
2777 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_810); --csr_cat.ws_tcdp_value;
2778 ELSE
2779 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_810; --csr_cat.ws_tcdp_value;
2780 END IF;
2781 l_counter:=l_counter+1;
2782
2783 xml_tab(l_counter).TagName :='STEERING_VALUE_810';
2784 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_810); --csr_cat.ws_tcdp_value;
2785 l_counter:=l_counter+1;
2786 END IF;
2787 xml_tab(l_counter).TagName :='STEERING_CODE_800';
2788 xml_tab(l_counter).TagValue :='800'; --csr_cat.ws_tcow_value;
2789 l_counter:=l_counter+1;
2790 IF csr_salary.Salary_Type=3 THEN
2791
2792 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_800';
2793 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_800,2); --csr_cat.ws_tcow_value;
2794 l_counter:=l_counter+1;
2795
2796 xml_tab(l_counter).TagName :='STEERING_VALUE_800';
2797 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_800,2); --csr_cat.ws_tcow_value;
2798 l_counter:=l_counter+1;
2799
2800 ELSE
2801
2802 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_800';
2803 IF csr_salary.Steering_Code_800 > 0 THEN
2804 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_800); --csr_cat.ws_tcow_value;
2805 ELSE
2806 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_800; --csr_cat.ws_tcow_value;
2807 END IF;
2808 l_counter:=l_counter+1;
2809
2810 xml_tab(l_counter).TagName :='STEERING_VALUE_800';
2811 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_800); --csr_cat.ws_tcow_value;
2812 l_counter:=l_counter+1;
2813
2814 END IF;
2815 xml_tab(l_counter).TagName :='STEERING_CODE_801';
2816 xml_tab(l_counter).TagValue :='801'; --csr_cat.ws_nha_value;
2817 l_counter:=l_counter+1;
2818 IF csr_salary.Salary_Type=3 THEN
2819
2820 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_801';
2821 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_801,2); --csr_cat.ws_nha_value;
2822 l_counter:=l_counter+1;
2823
2824 xml_tab(l_counter).TagName :='STEERING_VALUE_801';
2825 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_801,2); --csr_cat.ws_nha_value;
2826 l_counter:=l_counter+1;
2827
2828 ELSE
2829
2830 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_801';
2831 IF csr_salary.Steering_Code_801 > 0 THEN
2832 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_801); --csr_cat.ws_nha_value;
2833 ELSE
2834 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_801,2); --csr_cat.ws_nha_value;
2835 END IF;
2836 l_counter:=l_counter+1;
2837
2838 xml_tab(l_counter).TagName :='STEERING_VALUE_801';
2839 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_801); --csr_cat.ws_nha_value;
2840 l_counter:=l_counter+1;
2841
2842 END IF;
2843
2844 xml_tab(l_counter).TagName :='STEERING_CODE_802';
2845 xml_tab(l_counter).TagValue :='802'; --csr_cat.ws_nho_value;
2846 l_counter:=l_counter+1;
2847 IF csr_salary.Salary_Type=3 THEN
2848
2849 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_802';
2850 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_802,2); --csr_cat.ws_nho_value;
2851 l_counter:=l_counter+1;
2852
2853 xml_tab(l_counter).TagName :='STEERING_VALUE_802';
2854 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_802,2); --csr_cat.ws_nho_value;
2855 l_counter:=l_counter+1;
2856
2857 ELSE
2858 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_802';
2859 IF csr_salary.Steering_Code_802 > 0 THEN
2860 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_802); --csr_cat.ws_nho_value;
2861 ELSE
2862 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_802; --csr_cat.ws_nho_value;
2863 END IF;
2864 l_counter:=l_counter+1;
2865
2866 xml_tab(l_counter).TagName :='STEERING_VALUE_802';
2867 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_802); --csr_cat.ws_nho_value;
2868 l_counter:=l_counter+1;
2869
2870 END IF;
2871
2872 xml_tab(l_counter).TagName :='STEERING_CODE_803';
2873 xml_tab(l_counter).TagValue :='803'; --csr_cat.ws_retroactive_pay;
2874 l_counter:=l_counter+1;
2875 IF csr_salary.Salary_Type=3 THEN
2876
2877 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_803';
2878 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_803,2); --csr_cat.ws_retroactive_pay;
2879 l_counter:=l_counter+1;
2880
2881 xml_tab(l_counter).TagName :='STEERING_VALUE_803';
2882 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_803,2); --csr_cat.ws_retroactive_pay;
2883 l_counter:=l_counter+1;
2884
2885 ELSE
2886 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_803';
2887 IF csr_salary.Steering_Code_803 > 0 THEN
2888 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_803); --csr_cat.ws_retroactive_pay;
2889 ELSE
2890 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_803; --csr_cat.ws_retroactive_pay;
2891 END IF;
2892 l_counter:=l_counter+1;
2893
2894 xml_tab(l_counter).TagName :='STEERING_VALUE_803';
2895 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_803); --csr_cat.ws_retroactive_pay;
2896 l_counter:=l_counter+1;
2897
2898 END IF;
2899
2900 xml_tab(l_counter).TagName :='STEERING_CODE_804';
2901 xml_tab(l_counter).TagValue :='804'; --csr_cat.ws_ppo_value;
2902 l_counter:=l_counter+1;
2903
2904 IF csr_salary.Salary_Type=3 THEN
2905
2906 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_804';
2907 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_804,2); --csr_cat.ws_ppo_value;
2908 l_counter:=l_counter+1;
2909
2910 xml_tab(l_counter).TagName :='STEERING_VALUE_804';
2911 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_804,2); --csr_cat.ws_ppo_value;
2912 l_counter:=l_counter+1;
2913
2914 ELSE
2915 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_804';
2916 IF csr_salary.Steering_Code_804 > 0 THEN
2917 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_804); --csr_cat.ws_ppo_value;
2918 ELSE
2919 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_804; --csr_cat.ws_ppo_value;
2920 END IF;
2921 l_counter:=l_counter+1;
2922
2923 xml_tab(l_counter).TagName :='STEERING_VALUE_804';
2924 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_804); --csr_cat.ws_ppo_value;
2925 l_counter:=l_counter+1;
2926
2927 END IF;
2928
2929 xml_tab(l_counter).TagName :='STEERING_CODE_805';
2930 xml_tab(l_counter).TagValue :='805'; --csr_cat.ws_sick_pay;
2931 l_counter:=l_counter+1;
2932 IF csr_salary.Salary_Type=3 THEN
2933
2934 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_805';
2935 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_805,2); --csr_cat.ws_sick_pay;
2936 l_counter:=l_counter+1;
2937
2938 xml_tab(l_counter).TagName :='STEERING_VALUE_805';
2939 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_805,2); --csr_cat.ws_sick_pay;
2940 l_counter:=l_counter+1;
2941
2942 ELSE
2943
2944 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_805';
2945 IF csr_salary.Steering_Code_804 > 0 THEN
2946 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_805); --csr_cat.ws_sick_pay;
2947 ELSE
2948 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_805; --csr_cat.ws_sick_pay;
2949 END IF;
2950 l_counter:=l_counter+1;
2951
2952 xml_tab(l_counter).TagName :='STEERING_VALUE_805';
2953 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_805); --csr_cat.ws_sick_pay;
2954 l_counter:=l_counter+1;
2955
2956 END IF;
2957
2958 xml_tab(l_counter).TagName :='STEERING_CODE_806';
2959 xml_tab(l_counter).TagValue :='806'; --csr_cat.ws_total_employees;
2960 l_counter:=l_counter+1;
2961 IF csr_salary.Salary_Type=3 THEN
2962
2963 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_806';
2964 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_806,2); --csr_cat.ws_total_employees;
2965 l_counter:=l_counter+1;
2966
2967 xml_tab(l_counter).TagName :='STEERING_VALUE_806';
2968 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_806,2); --csr_cat.ws_total_employees;
2969 l_counter:=l_counter+1;
2970
2971 ELSE
2972
2973 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_806';
2974 IF csr_salary.Steering_Code_806 > 0 THEN
2975 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_806); --csr_cat.ws_total_employees;
2976 ELSE
2977 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_806; --csr_cat.ws_total_employees;
2978 END IF;
2979 l_counter:=l_counter+1;
2980
2981 xml_tab(l_counter).TagName :='STEERING_VALUE_806';
2982 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_806); --csr_cat.ws_total_employees;
2983 l_counter:=l_counter+1;
2984
2985 END IF;
2986
2987 xml_tab(l_counter).TagName :='STEERING_CODE_808';
2988 xml_tab(l_counter).TagValue :='808'; --round(csr_cat.wh_full_time_employee,2);
2989 l_counter:=l_counter+1;
2990 IF csr_salary.Salary_Type=3 THEN
2991
2992 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_808';
2993 xml_tab(l_counter).TagValue :=round(csr_salary.Steering_Code_808,2); --round(csr_cat.wh_full_time_employee,2);
2994 l_counter:=l_counter+1;
2995
2996 xml_tab(l_counter).TagName :='STEERING_VALUE_808';
2997 xml_tab(l_counter).TagValue :=100*round(csr_salary.Steering_Code_808,2); --round(csr_cat.wh_full_time_employee,2);
2998 l_counter:=l_counter+1;
2999
3000 ELSE
3001
3002 xml_tab(l_counter).TagName :='STEERING_CODE_VALUE_808';
3003 IF csr_salary.Steering_Code_808 > 0 THEN
3004 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_808); --round(csr_cat.wh_full_time_employee,2);
3005 ELSE
3006 xml_tab(l_counter).TagValue :=csr_salary.Steering_Code_808; --round(csr_cat.wh_full_time_employee,2);
3007 END IF;
3008 l_counter:=l_counter+1;
3009
3010 xml_tab(l_counter).TagName :='STEERING_VALUE_808';
3011 xml_tab(l_counter).TagValue :=ceil(csr_salary.Steering_Code_808); --round(csr_cat.wh_full_time_employee,2);
3012 l_counter:=l_counter+1;
3013
3014 END IF;
3015
3016 xml_tab(l_counter).TagName :='SSST_DETAILS';
3017 xml_tab(l_counter).TagValue :='END_SSST_DETAILS';
3018 l_counter := l_counter + 1;
3019
3020 l_worksite_number:=NULL;
3021 l_association_number:=NULL;
3022 l_weekend_pay_duty:=NULL;
3023
3024
3025 END LOOP;
3026
3027
3028
3029 WritetoCLOB (p_xml );
3030 --INSERT INTO raaj VALUES (p_xml);
3031 -- fnd_file.put_line(fnd_file.LOG,'p_xml'||p_xml);
3032
3033
3034 END POPULATE_DATA_DETAIL;
3035
3036 END PAY_SE_SALARY_STRUCTURE_STATS;
3037