[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_WAGES_SALARIES
Source
1 PACKAGE BODY PAY_SE_WAGES_SALARIES AS
2 /* $Header: pysewssa.pkb 120.0.12000000.1 2007/04/24 06:44:17 rsahai noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE lock_rec IS RECORD (
6 archive_assact_id NUMBER
7 );
8
9 TYPE lock_table IS TABLE OF lock_rec
10 INDEX BY BINARY_INTEGER;
11
12 g_lock_table lock_table;
13 g_index NUMBER := -1;
14 g_index_assact NUMBER := -1;
15 g_index_bal NUMBER := -1;
16 g_package VARCHAR2 (33) := 'PAY_SE_FORA.';
17 g_payroll_action_id NUMBER;
18 g_arc_payroll_action_id NUMBER;
19 -- Globals to pick up all the parameter
20 g_business_group_id NUMBER;
21 g_effective_date DATE;
22
23
24 g_legal_employer_id NUMBER;
25 g_local_unit_id NUMBER;
26 g_LE_request VARCHAR2 (240);
27 g_LU_request VARCHAR2 (240);
28
29 g_posting_date DATE;
30 g_account_date DATE;
31 g_reporting_date DATE;
32 g_year NUMBER;
33 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_LU_request OUT NOCOPY VARCHAR2 -- User parameter
118 , p_LOCAL_UNIT_id OUT NOCOPY NUMBER -- User parameter
119 , p_MONTH OUT NOCOPY NUMBER -- User parameter
120 , p_YEAR OUT NOCOPY NUMBER -- User parameter
121 , p_RETROACTIVE_PAYMENT_FROM OUT NOCOPY DATE
122 , p_RETROACTIVE_PAYMENT_TO OUT NOCOPY DATE
123 )
124 IS
125 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
126 IS
127 SELECT (get_parameter
128 (legislative_parameters
129 , 'LEGAL_EMPLOYER'
130 )
131 ) LEGAL_EMPLOYER_ID
132 , (get_parameter
133 (legislative_parameters
134 , 'LU_REQUEST'
135 )
136 ) LU_REQUEST
137 ,(get_parameter
138 (legislative_parameters
139 , 'LOCAL_UNIT'
140 )
141 ) LOCAL_UNIT_ID
142 ,(get_parameter
143 (legislative_parameters
144 , 'MONTH'
145 )
146 ) L_MONTH
147 ,(get_parameter
148 (legislative_parameters
149 , 'YEAR'
150 )
151 ) L_YEAR
152 ,FND_DATE.canonical_to_date((get_parameter
153 (legislative_parameters
154 , 'RETROACTIVE_PAYMENT_FROM'
155 )
156 )) L_RETROACTIVE_PAYMENT_FROM
157 ,FND_DATE.canonical_to_date((get_parameter
158 (legislative_parameters
159 , 'RETROACTIVE_PAYMENT_TO'
160 )
161 )) L_RETROACTIVE_PAYMENT_TO
162 , effective_date, business_group_id bg_id
163 FROM pay_payroll_actions
164 WHERE payroll_action_id = p_payroll_action_id;
165
166 lr_parameter_info csr_parameter_info%ROWTYPE;
167 l_proc VARCHAR2 (240)
168 := g_package || ' GET_ALL_PARAMETERS ';
169 BEGIN
170
171 OPEN csr_parameter_info (p_payroll_action_id);
172
173 --FETCH csr_parameter_info into lr_parameter_info;
174 FETCH csr_parameter_info
175 INTO lr_parameter_info;
176
177 CLOSE csr_parameter_info;
178
179 p_legal_employer_id := lr_parameter_info.legal_employer_id;
180
181
182 p_LU_request := lr_parameter_info.LU_REQUEST;
183
184
185 p_local_unit_id := lr_parameter_info.LOCAL_UNIT_ID;
186
187 p_month:=lr_parameter_info.l_month;
188 p_year:=lr_parameter_info.l_year;
189 p_retroactive_payment_from:=lr_parameter_info.l_retroactive_payment_from;
190 p_retroactive_payment_to:=lr_parameter_info.l_retroactive_payment_to;
191 p_effective_date := lr_parameter_info.effective_date;
192 p_business_group_id := lr_parameter_info.bg_id;
193
194
195 IF g_debug
196 THEN
197 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS'
198 , 30);
199 END IF;
200 END get_all_parameters;
201
202 /* RANGE CODE */
203 PROCEDURE range_code (
204 p_payroll_action_id IN NUMBER
205 , p_sql OUT NOCOPY VARCHAR2
206 )
207 IS
208 l_action_info_id NUMBER;
209 l_ovn NUMBER;
210 l_business_group_id NUMBER;
211 --l_start_date VARCHAR2 (30);
212 --l_end_date VARCHAR2 (30);
213 l_effective_date DATE;
214 l_consolidation_set NUMBER;
215 l_defined_balance_id NUMBER := 0;
216 l_count NUMBER := 0;
217 l_prev_prepay NUMBER := 0;
218 l_canonical_start_date DATE;
219 l_canonical_end_date DATE;
220 l_payroll_id NUMBER;
221 l_prepay_action_id NUMBER;
222 l_actid NUMBER;
223 -- l_assignment_id NUMBER;
224 l_action_sequence NUMBER;
225 l_assact_id NUMBER;
226 l_pact_id NUMBER;
227 l_flag NUMBER := 0;
228 l_element_context VARCHAR2 (5);
229
230 -- Archiving the data , as this will fire once
231
232 -- ********************* for cfar from lU ***********************
233 CURSOR csr_CFAR_FROM_LU (
234 csr_local_unit_ID NUMBER
235 )
236 is
237 select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
238 from HR_ORGANIZATION_UNITS o1
239 , HR_ORGANIZATION_INFORMATION hoi1
240 , HR_ORGANIZATION_INFORMATION hoi2
241 WHERE o1.business_group_id = g_business_group_id
242 and hoi1.organization_id = o1.organization_id
243 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
244 and hoi1.org_information_context = 'CLASS'
245 and o1.organization_id = hoi2.organization_id
246 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
247 and o1.organization_id = csr_local_unit_ID;
248
249 lr_CFAR_FROM_LU csr_CFAR_FROM_LU%ROWTYPE;
250
251 CURSOR csr_employee_details(csr_v_person_id number, csr_v_end_date date)
252 IS
253 SELECT national_identifier, last_name || ' ' || first_name name ,DATE_OF_BIRTH
254 FROM
255 per_all_people_f WHERE
256 BUSINESS_GROUP_ID=g_business_group_id
257 AND person_id=csr_v_person_id
258 AND csr_v_end_date
259 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
260 AND months_between (csr_v_end_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
261 AND months_between (csr_v_end_date,DATE_OF_BIRTH) < 768 /* and age less than 64 */
262 ORDER BY last_name || ' ' || first_name;
263
264
265 CURSOR csr_white_collar(csr_v_person_id number, csr_v_end_date date)
266 IS
267 SELECT effective_start_date FROM per_all_assignments_f
268 WHERE person_id=csr_v_person_id --21233
269 AND csr_v_end_date
270 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
271 AND primary_flag='Y'
272 AND employee_category='WC' ;
273
274 CURSOR csr_termination(csr_v_person_id number, csr_v_start_date date, csr_v_end_date date)
275 IS
276 SELECT MAX(effective_start_date) FROM per_all_people_f papf WHERE
277 CURRENT_EMPLOYEE_FLAG IS NULL
278 AND person_id=csr_v_person_id--21257
279 AND EFFECTIVE_END_DATE --'31-dec-2000'
280 BETWEEN csr_v_start_date AND csr_v_end_date /*'01-jan-2000' AND csr_v_end_date '31-dec-2000'*/
281 AND NOT EXISTS
282 (SELECT 1 FROM per_all_people_f WHERE
283 CURRENT_EMPLOYEE_FLAG='Y'
284 AND person_id=papf.person_id --21257
285 AND effective_start_date >papf.effective_start_date
286 );
287 CURSOR csr_assignment_details(csr_v_person_id NUMBER,csr_v_start_date date, csr_v_end_date date)
288 IS
289 /*SELECT effective_start_date,effective_end_date,pj.JOB_INFORMATION1,employee_category
290 FROM
291 per_all_people_f papf,
292 per_jobs pj
293 WHERE person_id=csr_v_person_id --21257 --21233
294 AND csr_v_start_date<=EFFECTIVE_END_DATE AND
295 csr_v_end_date>=EFFECTIVE_START_DATE
296 AND primary_flag='Y'
297 AND pj.job_id=papf.job_id
298 AND papf.job_id IS NOT NULL
299 AND papf.emloyee_category IS NOT NULL*/
300
301 SELECT paaf.effective_start_date,paaf.effective_end_date,
302 decode(pj.JOB_INFORMATION1,'Y','M',null) job,paaf.employee_category,
303 payroll_id
304 FROM
305 per_all_assignments_f paaf,
306 per_jobs pj
307 WHERE person_id=csr_v_person_id --21197 --21257 --21233
308 AND csr_v_start_date <=paaf.EFFECTIVE_END_DATE AND
309 csr_v_end_date >=paaf.EFFECTIVE_START_DATE
310 AND primary_flag='Y'
311 AND pj.job_id(+)=paaf.job_id
312 -- AND paaf.employee_category IN ('BC','WC')
313 AND (paaf.job_id IS NOT NULL
314 OR paaf.employee_category IS NOT NULL);
315
316
317 /*CURSOR csr_painter(csr_v_person_id NUMBER,csr_v_start_date date, csr_v_end_date date)
318 IS
319 /*SELECT JOB_INFORMATION1 FROM per_jobs pj, per_roles pr
320 WHERE pj.job_id=pr.job_id
321 AND pj.JOB_INFORMATION_CATEGORY='SE'
322 AND pr.person_id=csr_v_person_id; --21257 */
323 /*SELECT JOB_INFORMATION1,start_date, start_date+(e_date-start_date-1) end_date
324 FROM
325 (
326 SELECT JOB_INFORMATION1,start_date,lead( start_date, 1, to_date('31-12-4713','dd-mm-yyyy') )
327 over (order by start_date ASC) e_date
328 FROM per_jobs pj, per_roles pr
329 WHERE pj.job_id=pr.job_id
330 AND pj.JOB_INFORMATION_CATEGORY='SE'
331 AND pr.person_id=csr_v_person_id /*21197*/--)
332 /*WHERE start_date<=csr_v_end_date --'31-dec-2005'
333 AND start_date+(e_date-start_date-1)>=csr_v_start_date; /*'01-jan-2005'*/
334
335 CURSOR csr_employee_category(csr_v_person_id number, csr_v_start_date DATE, csr_v_end_date date)
336 IS
337 SELECT DISTINCT employee_category ,EFFECTIVE_START_DATE
338 FROM per_all_assignments_f
339 WHERE person_id=csr_v_person_id --21257 --21233
340 AND csr_v_start_date<=EFFECTIVE_END_DATE AND
341 csr_v_end_date>=EFFECTIVE_START_DATE
342 AND primary_flag='Y'
343 ORDER BY EFFECTIVE_START_DATE;
344
345 CURSOR csr_employee_blue_max_date(csr_v_person_id number, csr_v_start_date DATE, csr_v_end_date date)
346 IS
347 SELECT MAX(EFFECTIVE_end_DATE) FROM
348 per_all_assignments_f
349 WHERE person_id=csr_v_person_id --21257 --21233
350 AND csr_v_start_date<=EFFECTIVE_END_DATE AND
351 csr_v_end_date>=EFFECTIVE_START_DATE
352 AND employee_category='BC'
353 AND primary_flag='Y';
354
355 /*SELECT employee_category FROM per_all_assignments_f
356 WHERE person_id=csr_v_person_id --21233
357 AND csr_v_end_date
358 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
359 AND primary_flag='Y';*/
360
361 CURSOR csr_legal_employer_details (
362 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
363 )
364 IS
365 SELECT o1.NAME legal_employer_name
366 -- , hoi2.org_information2 org_number
367 -- , hoi1.organization_id legal_id
368 FROM hr_organization_units o1
369 , hr_organization_information hoi1
370 , hr_organization_information hoi2
371 WHERE o1.business_group_id = g_business_group_id
372 AND hoi1.organization_id = o1.organization_id
373 AND hoi1.organization_id = csr_v_legal_employer_id
374 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
375 AND hoi1.org_information_context = 'CLASS'
376 AND o1.organization_id = hoi2.organization_id
377 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
378
379 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
380 L_LEGAL_EMPLOYER_NAME VARCHAR2(240);
381
382 CURSOR csr_person_local_unit(csr_v_business_group_id number, csr_v_local_unit_id number, csr_v_effective_date date)
383 IS
384 SELECT papf.person_id --,paaf.assignment_id
385 FROM per_all_assignments_f paaf,
386 per_all_people_f papf,
387 hr_soft_coding_keyflex hsck
388 WHERE papf.business_group_id=csr_v_business_group_id -- 3133 --paaf.assignment_id = p_assignment_id
389 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
390 AND papf.person_id=paaf.person_id
391 --AND paaf.primary_flag='Y'
392 AND hsck.segment2=to_char(csr_v_local_unit_id) --3268)
393 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
394 AND paaf.effective_end_date
395 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
396 AND papf.effective_end_date
397 AND months_between (csr_v_effective_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
398 AND months_between (csr_v_effective_date,DATE_OF_BIRTH) < 768
399 AND papf.CURRENT_EMPLOYEE_FLAG='Y'
400 AND paaf.employee_category IN ('WC','BC')
401 AND paaf.employment_category IN ('SE_VTR','SE_HW') --add one more type
402 ORDER BY papf.person_id;
403
404 CURSOR csr_assignment_person(csr_v_person_id number, csr_v_effective_date date)
405 IS
406 SELECT paaf.assignment_id,
407 paaf.employee_category,
408 paaf.hourly_salaried_code,
409 hsck.SEGMENT9 working_percentage,
410 paaf.frequency,
411 paaf.normal_hours
412 FROM per_all_assignments_f paaf,
413 hr_soft_coding_keyflex hsck
414 WHERE paaf.person_id=csr_v_person_id
415 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
416 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
417 AND paaf.effective_end_date
418 AND paaf.employee_category IN ('WC','BC')
419 AND paaf.employment_category IN ('SE_VTR','SE_HW') --add one more type
420 ORDER BY paaf.assignment_id;
421
422 CURSOR csr_assignment_absence (csr_v_assignment_id number, csr_v_start_date date, csr_v_end_date date)
423 is
424 SELECT greatest(fnd_Date.canonical_to_date(eev1.screen_entry_value),csr_v_start_date) start_date,
425 least(fnd_Date.canonical_to_date(eev2.screen_entry_value),csr_v_end_date) end_date
426 FROM per_all_assignments_f asg1
427 ,per_all_assignments_f asg2
428 ,per_all_people_f per
429 ,pay_element_links_f el
430 ,pay_element_types_f et
431 ,pay_input_values_f iv1
432 ,pay_input_values_f iv2
433 ,pay_element_entries_f ee
434 ,pay_element_entry_values_f eev1
435 ,pay_element_entry_values_f eev2
436 WHERE asg1.assignment_id = csr_v_assignment_id
437 AND csr_v_end_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
438 AND csr_v_end_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
439 AND per.person_id = asg1.person_id
440 AND asg2.person_id = per.person_id
441 --AND asg2.primary_flag = 'Y'
442 AND asg1.assignment_id=asg2.assignment_id
443 AND et.element_name = 'Sickness Details'
444 AND et.legislation_code = 'SE'
445 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
446 AND iv1.element_type_id = et.element_type_id
447 AND iv1.name = 'Start Date'
448 AND iv2.element_type_id = et.element_type_id
449 AND iv2.name = 'End Date'
450 AND el.business_group_id = per.business_group_id
451 AND el.element_type_id = et.element_type_id
452 AND ee.assignment_id = asg2.assignment_id
453 AND ee.element_link_id = el.element_link_id
454 AND eev1.element_entry_id = ee.element_entry_id
455 AND eev1.input_value_id = iv1.input_value_id
456 AND eev2.element_entry_id = ee.element_entry_id
457 AND eev2.input_value_id = iv2.input_value_id
458 AND ee.effective_start_date <= csr_v_end_date
459 AND ee.effective_end_date >= csr_v_start_date
460 AND eev1.effective_start_date <= csr_v_end_date
461 AND eev1.effective_end_date >= csr_v_start_date
462 AND eev2.effective_start_date <= csr_v_end_date
463 AND eev2.effective_end_date >= csr_v_start_date;
464
465
466
467 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
468 IS
469 SELECT ue.creator_id
470 FROM ff_user_entities ue,
471 ff_database_items di
472 WHERE di.user_name = csr_v_Balance_Name
473 AND ue.user_entity_id = di.user_entity_id
474 AND ue.legislation_code = 'SE'
475 AND ue.business_group_id is NULL
476 AND ue.creator_type = 'B';
477
478 CURSOR csr_assignment_action (csr_v_payroll_action_id
479 pay_payroll_actions.payroll_action_id%type)
480 IS
481 SELECT MAX(assignment_action_id)
482 FROM pay_Assignment_actions WHERE
483 payroll_action_id=csr_v_payroll_action_id; --23
484
485 CURSOR csr_Local_unit_Legal(csr_v_legal_unit_id
486 hr_organization_units.organization_id%TYPE)
487 IS
488 SELECT hoi2.ORG_INFORMATION1 local_unit_id
489 FROM hr_organization_units o1
490 , hr_organization_information hoi1
491 , hr_organization_information hoi2
492 WHERE o1.business_group_id =g_business_group_id
493 AND hoi1.organization_id = o1.organization_id
494 AND hoi1.organization_id = csr_v_legal_unit_id
495 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
496 AND hoi1.org_information_context = 'CLASS'
497 AND o1.organization_id =hoi2.organization_id
498 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';
499
500 CURSOR csr_local_unit_details (
501 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
502 )
503 IS
504 SELECT o1.NAME local_unit_name
505 -- , hoi2.org_information2 org_number
506 -- , hoi1.organization_id legal_id
507 FROM hr_organization_units o1
508 , hr_organization_information hoi1
509 , hr_organization_information hoi2
510 WHERE o1.business_group_id = g_business_group_id
511 AND hoi1.organization_id = o1.organization_id
512 AND hoi1.organization_id = csr_v_local_unit_id
513 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
514 AND hoi1.org_information_context = 'CLASS'
515 AND o1.organization_id = hoi2.organization_id
516 AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
517
518 CURSOR csr_payroll_periods(csr_v_effective_date date,csr_v_payroll_id number)
519 IS
520 SELECT START_DATE,end_date FROM per_time_periods WHERE payroll_id=csr_v_payroll_id --3469
521 AND /*'15-jan-2005'*/ csr_v_effective_date BETWEEN START_DATE AND end_date;
522
523 CURSOR csr_month (csr_v_month NUMBER)
524 IS
525 SELECT MEANING
526 FROM hr_lookups
527 WHERE LOOKUP_TYPE = 'HR_SE_CALENDAR_MONTH'
528 AND ENABLED_FLAG = 'Y'
529 AND LOOKUP_CODE = csr_v_month; -- 01;
530
531 L_MONTH_START_DATE DATE;
532 L_MONTH_END_DATE DATE;
533 L_LOCAL_UNIT_NAME VARCHAR2(50);
534 l_person_id NUMBER;
535 l_sex CHAR(1);
536 l_local_unit_id NUMBER;
537 l_assignment_category VARCHAR2(5);
538 l_assignment_start_date DATE;
539 l_assignment_end_date DATE;
540 l_absence_count NUMBER;
541 l_employee_category per_all_assignments_f.employee_category%type;
542 l_person_number per_all_people_f.national_identifier%TYPE;
543 l_person_name VARCHAR2(350);
544
545 l_terminated VARCHAR2(50);
546
547 l_gross_salary number;
548 l_start_date date;
549 l_end_date date;
550 l_termination_date date;
551 lr_Get_Defined_Balance_Id pay_defined_balances.defined_balance_id%type;
552 l_value number;
553 l_assignment_id pay_Assignment_actions.assignment_id%type;
554 l_assignment_action_id pay_Assignment_actions.assignment_action_id%type;
555 L_CFAR_NUMBER NUMBER;
556 l_legal_employer_id NUMBER;
557 l_virtual_date DATE;
558 l_date_birth per_all_people_f.DATE_OF_BIRTH%TYPE;
559 l_counter NUMBER :=0;
560 l_total_salary NUMBER;
561 l_asg_start_date DATE;
562 l_asg_end_date date;
563 l_category per_all_assignments_f.employee_category%type;
564 l_prev_category per_all_assignments_f.employee_category%type;
565 l_working_percentage NUMBER;
566 l_asg_hour_sal per_all_assignments_f.hourly_salaried_code%type;
567 l_frequency per_all_assignments_f.frequency%type;
568 l_normal_hours per_all_assignments_f.normal_hours%type;
569 l_include_event char(1);
570 l_wrk_schd_return NUMBER;
571 l_wrk_duration NUMBER;
572 l_absence_start_date DATE;
573 l_absence_end_date DATE;
574 l_bh_worked_calendar_month NUMBER:=0;
575 l_bh_absence_days NUMBER:=0;
576 l_bh_worked_payment_period NUMBER:=0;
577 l_bh_total_employees NUMBER:=0;
578 l_bs_total_employees NUMBER:=0;
579 l_ws_total_employees NUMBER:=0;
580 l_wh_total_employees NUMBER:=0;
581 l_ws_full_time_employee NUMBER:=0;
582 l_wh_full_time_employee NUMBER:=0;
583 l_bs_gross_pay NUMBER:=0;
584 l_ws_gross_pay NUMBER:=0;
585 l_wh_gross_pay NUMBER:=0;
586 l_bh_retroactive_pay NUMBER:=0;
587 l_bs_retroactive_pay NUMBER:=0;
588 l_ws_retroactive_pay NUMBER:=0;
589 l_wh_retroactive_pay NUMBER:=0;
590 l_bh_sick_pay NUMBER:=0;
591 l_bs_sick_pay NUMBER:=0;
592 l_ws_sick_pay NUMBER:=0;
593 l_wh_sick_pay NUMBER:=0;
594 l_start_time_char Varchar2(10) :=null; -- '0';
595 l_end_time_char Varchar2(10) :=null; -- '23.59';
596 l_bs_working_agreement NUMBER:=0;
597 l_ws_working_agreement NUMBER:=0;
598 l_wh_working_agreement NUMBER:=0;
599 l_month varchar2(50);
600 TYPE emp_cat_type
601 IS TABLE OF VARCHAR2(10)
602 INDEX BY BINARY_INTEGER;
603 emp_cat emp_cat_type;
604
605 TYPE emp_job_record IS RECORD
606 (
607 job VARCHAR2(5),
608 end_date date
609 );
610 TYPE emp_job_type
611 IS TABLE OF emp_job_record
612 INDEX BY BINARY_INTEGER;
613 emp_job emp_job_type;
614
615 TYPE emp_detail_record IS RECORD
616 (
617 l_start_date date,
618 l_end_date date,
619 l_category varchar2(5),
620 l_job varchar2(5),
621 l_gross_salary number(17,2),
622 l_termination varchar2(5),
623 l_white_from date
624 );
625 TYPE emp_record_type
626 IS TABLE OF emp_detail_record
627 INDEX BY BINARY_INTEGER;
628 emp_record emp_record_type;
629
630 -- VARIABLE FOR THIS REPORET
631 BEGIN
632
633
634 IF g_debug
635 THEN
636 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
637 END IF;
638
639 p_sql :=
640 'SELECT DISTINCT person_id
641 FROM per_people_f ppf
642 ,pay_payroll_actions ppa
643 WHERE ppa.payroll_action_id = :payroll_action_id
644 AND ppa.business_group_id = ppf.business_group_id
645 ORDER BY ppf.person_id';
646 g_payroll_action_id := p_payroll_action_id;
647 g_business_group_id := NULL;
648 g_effective_date := NULL;
649 g_LU_request :=null;
650 g_legal_employer_id := NULL;
651 g_local_unit_id := NULL;
652 g_account_date :=null;
653 g_posting_date :=null;
654 get_all_parameters (p_payroll_action_id
655 , g_business_group_id
656 , g_effective_date
657 , g_legal_employer_id
658 , g_LU_request
659 , g_local_unit_id
660 , g_month
661 , g_year
662 , g_retroactive_payment_from
663 , g_retroactive_payment_to
664 );
665
666 OPEN csr_legal_employer_details(g_legal_employer_id);
667 FETCH csr_legal_employer_details INTO l_legal_employer_name;
668 CLOSE csr_legal_employer_details;
669
670 IF g_local_unit_id IS NOT NULL THEN
671
672 OPEN csr_local_unit_details(g_local_unit_id);
673 FETCH csr_local_unit_details INTO L_LOCAL_UNIT_NAME;
674 CLOSE csr_local_unit_details;
675
676 END IF;
677 l_local_unit_id:=g_local_unit_id;
678
679
680 g_start_date:=to_date('01-' || g_month || '-' || g_year, 'dd-mm-yyyy');
681 g_end_date:=last_day(to_date('01-'|| g_month || '-' || g_year, 'dd-mm-yyyy'));
682
683 /* OPEN csr_local_unit_details(g_local_unit_id);
684 fetch csr_local_unit_details into L_LOCAL_UNIT_NAME;
685 CLOSE csr_local_unit_details;*/
686 OPEN csr_month(g_month);
687 FETCH csr_month INTO l_month;
688 CLOSE csr_month;
689 -- Insert the report Parameters
690 pay_action_information_api.create_action_information
691 (p_action_information_id => l_action_info_id
692 , p_action_context_id => p_payroll_action_id
693 , p_action_context_type => 'PA'
694 , p_object_version_number => l_ovn
695 , p_effective_date => g_effective_date
696 , p_source_id => NULL
697 , p_source_text => NULL
698 , p_action_information_category => 'EMEA REPORT DETAILS'
699 , p_action_information1 => 'PYSEWSSA'
700 , p_action_information2 => g_legal_employer_id
701 , p_action_information3 => L_LEGAL_EMPLOYER_NAME
702 , p_action_information4 => hr_general.decode_lookup('SE_REQUEST_LEVEL',g_LU_request)
703 , p_action_information5 => g_local_unit_id
704 , p_action_information6 => L_LOCAL_UNIT_NAME
705 , p_action_information7 => l_month --TO_CHAR(TO_DATE(g_month,'MM'),'MONTH')
706 , p_action_information8 => g_year
707 , p_action_information9 => FND_DATE.DATE_TO_CANONICAL(g_retroactive_payment_from)
708 , p_action_information10 => FND_DATE.DATE_TO_CANONICAL(g_retroactive_payment_to)
709 );
710 -- *****************************************************************************
711
712
713
714
715
716
717 -- END IF;
718 --END IF;
719 IF g_debug
720 THEN
721 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
722 END IF;
723 EXCEPTION
724 WHEN OTHERS
725 THEN
726 -- Return cursor that selects no rows
727 p_sql :=
728 'select 1 from dual where to_char(:payroll_action_id) = dummy';
729 END range_code;
730
731 /* ASSIGNMENT ACTION CODE */
732 PROCEDURE assignment_action_code (
733 p_payroll_action_id IN NUMBER
734 , p_start_person IN NUMBER
735 , p_end_person IN NUMBER
736 , p_chunk IN NUMBER
737 )
738 IS
739 CURSOR csr_CFAR_FROM_LU (
740 csr_local_unit_ID NUMBER
741 )
742 is
743 select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
744 from HR_ORGANIZATION_UNITS o1
745 , HR_ORGANIZATION_INFORMATION hoi1
746 , HR_ORGANIZATION_INFORMATION hoi2
747 WHERE o1.business_group_id = g_business_group_id
748 and hoi1.organization_id = o1.organization_id
749 and hoi1.org_information1 = 'SE_LOCAL_UNIT'
750 and hoi1.org_information_context = 'CLASS'
751 and o1.organization_id = hoi2.organization_id
752 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
753 and o1.organization_id = csr_local_unit_ID;
754
755 lr_CFAR_FROM_LU csr_CFAR_FROM_LU%ROWTYPE;
756
757 CURSOR csr_employee_details(csr_v_person_id number, csr_v_end_date date)
758 IS
759 SELECT national_identifier, last_name || ' ' || first_name name ,DATE_OF_BIRTH
760 FROM
761 per_all_people_f WHERE
762 BUSINESS_GROUP_ID=g_business_group_id
763 AND person_id=csr_v_person_id
764 AND csr_v_end_date
765 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
766 AND months_between (csr_v_end_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
767 AND months_between (csr_v_end_date,DATE_OF_BIRTH) < 768 /* and age less than 64 */
768 ORDER BY last_name || ' ' || first_name;
769
770
771 CURSOR csr_white_collar(csr_v_person_id number, csr_v_end_date date)
772 IS
773 SELECT effective_start_date FROM per_all_assignments_f
774 WHERE person_id=csr_v_person_id --21233
775 AND csr_v_end_date
776 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
777 AND primary_flag='Y'
778 AND employee_category='WC' ;
779
780 CURSOR csr_termination(csr_v_person_id number, csr_v_start_date date, csr_v_end_date date)
781 IS
782 SELECT MAX(effective_start_date) FROM per_all_people_f papf WHERE
783 CURRENT_EMPLOYEE_FLAG IS NULL
784 AND person_id=csr_v_person_id--21257
785 AND EFFECTIVE_END_DATE --'31-dec-2000'
786 BETWEEN csr_v_start_date AND csr_v_end_date /*'01-jan-2000' AND csr_v_end_date '31-dec-2000'*/
787 AND NOT EXISTS
788 (SELECT 1 FROM per_all_people_f WHERE
789 CURRENT_EMPLOYEE_FLAG='Y'
790 AND person_id=papf.person_id --21257
791 AND effective_start_date >papf.effective_start_date
792 );
793 CURSOR csr_assignment_details(csr_v_person_id NUMBER,csr_v_start_date date, csr_v_end_date date)
794 IS
795 /*SELECT effective_start_date,effective_end_date,pj.JOB_INFORMATION1,employee_category
796 FROM
797 per_all_people_f papf,
798 per_jobs pj
799 WHERE person_id=csr_v_person_id --21257 --21233
800 AND csr_v_start_date<=EFFECTIVE_END_DATE AND
801 csr_v_end_date>=EFFECTIVE_START_DATE
802 AND primary_flag='Y'
803 AND pj.job_id=papf.job_id
804 AND papf.job_id IS NOT NULL
805 AND papf.emloyee_category IS NOT NULL*/
806
807 SELECT paaf.effective_start_date,paaf.effective_end_date,
808 decode(pj.JOB_INFORMATION1,'Y','M',null) job,paaf.employee_category,
809 payroll_id
810 FROM
811 per_all_assignments_f paaf,
812 per_jobs pj
813 WHERE person_id=csr_v_person_id --21197 --21257 --21233
814 AND csr_v_start_date <=paaf.EFFECTIVE_END_DATE AND
815 csr_v_end_date >=paaf.EFFECTIVE_START_DATE
816 AND primary_flag='Y'
817 AND pj.job_id(+)=paaf.job_id
818 -- AND paaf.employee_category IN ('BC','WC')
819 AND (paaf.job_id IS NOT NULL
820 OR paaf.employee_category IS NOT NULL);
821
822
823 CURSOR csr_painter(csr_v_person_id NUMBER,csr_v_start_date date, csr_v_end_date date)
824 IS
825 /*SELECT JOB_INFORMATION1 FROM per_jobs pj, per_roles pr
826 WHERE pj.job_id=pr.job_id
827 AND pj.JOB_INFORMATION_CATEGORY='SE'
828 AND pr.person_id=csr_v_person_id; --21257 */
829 SELECT JOB_INFORMATION1,start_date, start_date+(e_date-start_date-1) end_date
830 FROM
831 (
832 SELECT JOB_INFORMATION1,start_date,lead( start_date, 1, to_date('31-12-4713','dd-mm-yyyy') )
833 over (order by start_date ASC) e_date
834 FROM per_jobs pj, per_roles pr
835 WHERE pj.job_id=pr.job_id
836 AND pj.JOB_INFORMATION_CATEGORY='SE'
837 AND pr.person_id=csr_v_person_id /*21197*/)
838 WHERE start_date<=csr_v_end_date --'31-dec-2005'
839 AND start_date+(e_date-start_date-1)>=csr_v_start_date; /*'01-jan-2005'*/
840
841 CURSOR csr_employee_category(csr_v_person_id number, csr_v_start_date DATE, csr_v_end_date date)
842 IS
843 SELECT DISTINCT employee_category ,EFFECTIVE_START_DATE
844 FROM per_all_assignments_f
845 WHERE person_id=csr_v_person_id --21257 --21233
846 AND csr_v_start_date<=EFFECTIVE_END_DATE AND
847 csr_v_end_date>=EFFECTIVE_START_DATE
848 AND primary_flag='Y'
849 ORDER BY EFFECTIVE_START_DATE;
850
851 CURSOR csr_employee_blue_max_date(csr_v_person_id number, csr_v_start_date DATE, csr_v_end_date date)
852 IS
853 SELECT MAX(EFFECTIVE_end_DATE) FROM
854 per_all_assignments_f
855 WHERE person_id=csr_v_person_id --21257 --21233
856 AND csr_v_start_date<=EFFECTIVE_END_DATE AND
857 csr_v_end_date>=EFFECTIVE_START_DATE
858 AND employee_category='BC'
859 AND primary_flag='Y';
860
861 /*SELECT employee_category FROM per_all_assignments_f
862 WHERE person_id=csr_v_person_id --21233
863 AND csr_v_end_date
864 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
865 AND primary_flag='Y';*/
866
867 CURSOR csr_legal_employer_details (
868 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
869 )
870 IS
871 SELECT o1.NAME legal_employer_name
872 -- , hoi2.org_information2 org_number
873 -- , hoi1.organization_id legal_id
874 FROM hr_organization_units o1
875 , hr_organization_information hoi1
876 , hr_organization_information hoi2
877 WHERE o1.business_group_id = g_business_group_id
878 AND hoi1.organization_id = o1.organization_id
879 AND hoi1.organization_id = csr_v_legal_employer_id
880 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
881 AND hoi1.org_information_context = 'CLASS'
882 AND o1.organization_id = hoi2.organization_id
883 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
884
885 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
886 L_LEGAL_EMPLOYER_NAME VARCHAR2(240);
887
888 CURSOR csr_person_local_unit(csr_v_business_group_id number, csr_v_local_unit_id number, csr_v_effective_date date)
889 IS
890 SELECT distinct papf.person_id --,paaf.assignment_id
891 FROM per_all_assignments_f paaf,
892 per_all_people_f papf,
893 hr_soft_coding_keyflex hsck
894 WHERE papf.business_group_id=csr_v_business_group_id -- 3133 --paaf.assignment_id = p_assignment_id
895 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
896 AND papf.person_id=paaf.person_id
897 and papf.person_id between p_start_person and p_end_person
898 --AND paaf.primary_flag='Y'
899 AND hsck.segment2=to_char(csr_v_local_unit_id) --3268)
900 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
901 AND paaf.effective_end_date
902 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
903 AND papf.effective_end_date
904 AND months_between (csr_v_effective_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
905 AND months_between (csr_v_effective_date,DATE_OF_BIRTH) < 768
906 AND papf.CURRENT_EMPLOYEE_FLAG='Y'
907 AND paaf.employee_category IN ('WC','BC')
908 AND paaf.employment_category IN ('SE_VTR','SE_HW','SE_PE') --add one more type
909 ORDER BY papf.person_id;
910
911 CURSOR csr_assignment_person(csr_v_person_id number, csr_v_effective_date date)
912 IS
913 SELECT paaf.assignment_id,
914 paaf.employee_category,
915 paaf.hourly_salaried_code,
916 fnd_number.canonical_to_number(hsck.SEGMENT9) working_percentage,
917 paaf.frequency,
918 paaf.normal_hours
919 FROM per_all_assignments_f paaf,
920 hr_soft_coding_keyflex hsck
921 WHERE paaf.person_id=csr_v_person_id
922 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
923 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
924 AND paaf.effective_end_date
925 AND paaf.employee_category IN ('WC','BC')
926 AND paaf.employment_category IN ('SE_VTR','SE_HW','SE_PE') --add one more type
927 ORDER BY paaf.assignment_id;
928
929 CURSOR csr_wage_assignment(csr_v_business_group_id number, csr_v_local_unit_id number, csr_v_effective_date date)
930 IS
931 SELECT paaf.assignment_id,
932 paaf.employee_category,
933 paaf.hourly_salaried_code,
934 fnd_number.canonical_to_number(hsck.SEGMENT9) working_percentage,
935 paaf.frequency,
936 paaf.normal_hours,
937 paaf.payroll_id
938 FROM per_all_assignments_f paaf,
939 hr_soft_coding_keyflex hsck --,
940 -- per_all_people_f papf
941 WHERE paaf.business_group_id=csr_v_business_group_id
942 -- papf.person_id=paaf.person_id
943 and paaf.person_id between p_start_person and p_end_person
944 --AND paaf.primary_flag='Y'
945 AND hsck.segment2=to_char(csr_v_local_unit_id)
946 -- and csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
947 -- AND papf.effective_end_date
948 AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
949 AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
950 AND paaf.effective_end_date
951 AND paaf.employee_category IN ('WC','BC')
952 AND paaf.employment_category IN ('SE_VTR','SE_HW','SE_PE') --add one more type
953 /* AND months_between (csr_v_end_date,papf.DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
954 /* AND months_between (csr_v_end_date,papf.DATE_OF_BIRTH) < 768
955 AND papf.CURRENT_EMPLOYEE_FLAG='Y' ;*/
956
957 ORDER BY paaf.assignment_id;
958
959 CURSOR csr_assignment_absence (csr_v_assignment_id number, csr_v_start_date date, csr_v_end_date date)
960 is
961 SELECT greatest(fnd_Date.canonical_to_date(eev1.screen_entry_value),csr_v_start_date) start_date,
962 least(fnd_Date.canonical_to_date(eev2.screen_entry_value),csr_v_end_date) end_date
963 FROM per_all_assignments_f asg1
964 ,per_all_assignments_f asg2
965 ,per_all_people_f per
966 ,pay_element_links_f el
967 ,pay_element_types_f et
968 ,pay_input_values_f iv1
969 ,pay_input_values_f iv2
970 ,pay_element_entries_f ee
971 ,pay_element_entry_values_f eev1
972 ,pay_element_entry_values_f eev2
973 WHERE asg1.assignment_id = csr_v_assignment_id
974 AND csr_v_end_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
975 AND csr_v_end_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
976 AND per.person_id = asg1.person_id
977 AND asg2.person_id = per.person_id
978 --AND asg2.primary_flag = 'Y'
979 AND asg1.assignment_id=asg2.assignment_id
980 AND et.element_name = 'Sickness Details'
981 AND et.legislation_code = 'SE'
982 --OR et.business_group_id=3261 ) --checking for the business group, it should be removed
983 AND iv1.element_type_id = et.element_type_id
984 AND iv1.name = 'Start Date'
985 AND iv2.element_type_id = et.element_type_id
986 AND iv2.name = 'End Date'
987 AND el.business_group_id = per.business_group_id
988 AND el.element_type_id = et.element_type_id
989 AND ee.assignment_id = asg2.assignment_id
990 AND ee.element_link_id = el.element_link_id
991 AND eev1.element_entry_id = ee.element_entry_id
992 AND eev1.input_value_id = iv1.input_value_id
993 AND eev2.element_entry_id = ee.element_entry_id
994 AND eev2.input_value_id = iv2.input_value_id
995 AND ee.effective_start_date <= csr_v_end_date
996 AND ee.effective_end_date >= csr_v_start_date
997 AND eev1.effective_start_date <= csr_v_end_date
998 AND eev1.effective_end_date >= csr_v_start_date
999 AND eev2.effective_start_date <= csr_v_end_date
1000 AND eev2.effective_end_date >= csr_v_start_date;
1001
1002
1003
1004 Cursor csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE)
1005 IS
1006 SELECT ue.creator_id
1007 FROM ff_user_entities ue,
1008 ff_database_items di
1009 WHERE di.user_name = csr_v_Balance_Name
1010 AND ue.user_entity_id = di.user_entity_id
1011 AND ue.legislation_code = 'SE'
1012 AND ue.business_group_id is NULL
1013 AND ue.creator_type = 'B';
1014
1015 CURSOR csr_assignment_action (csr_v_payroll_action_id
1016 pay_payroll_actions.payroll_action_id%type)
1017 IS
1018 SELECT MAX(assignment_action_id)
1019 FROM pay_Assignment_actions WHERE
1020 payroll_action_id=csr_v_payroll_action_id; --23
1021
1022 CURSOR csr_Local_unit_Legal(csr_v_legal_unit_id
1023 hr_organization_units.organization_id%TYPE)
1024 IS
1025 SELECT hoi2.ORG_INFORMATION1 local_unit_id
1026 FROM hr_organization_units o1
1027 , hr_organization_information hoi1
1028 , hr_organization_information hoi2
1029 WHERE o1.business_group_id =g_business_group_id
1030 AND hoi1.organization_id = o1.organization_id
1031 AND hoi1.organization_id = csr_v_legal_unit_id
1032 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1033 AND hoi1.org_information_context = 'CLASS'
1034 AND o1.organization_id =hoi2.organization_id
1035 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';
1036
1037 CURSOR csr_local_unit_details (
1038 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
1039 )
1040 IS
1041 SELECT o1.NAME local_unit_name
1042 -- , hoi2.org_information2 org_number
1043 -- , hoi1.organization_id legal_id
1044 FROM hr_organization_units o1
1045 , hr_organization_information hoi1
1046 , hr_organization_information hoi2
1047 WHERE o1.business_group_id = g_business_group_id
1048 AND hoi1.organization_id = o1.organization_id
1049 AND hoi1.organization_id = csr_v_local_unit_id
1050 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1051 AND hoi1.org_information_context = 'CLASS'
1052 AND o1.organization_id = hoi2.organization_id
1053 AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
1054
1055 CURSOR csr_payroll_periods(csr_v_effective_date date,csr_v_payroll_id number)
1056 IS
1057 SELECT START_DATE,end_date FROM per_time_periods WHERE payroll_id=csr_v_payroll_id --3469
1058 AND /*'15-jan-2005'*/ csr_v_effective_date BETWEEN START_DATE AND end_date;
1059
1060 CURSOR csr_element_types(csr_v_assignment_id number, csr_v_start_date date, csr_v_end_date date,
1061 csr_v_element_type_id number, csr_v_input_value_id number)
1062 IS
1063 SELECT SUM(RESULT_VALUE) total --prrv1.* ,paa.assignment_id
1064 FROM pay_assignment_actions paa,
1065 pay_payroll_actions ppa,
1066 pay_run_results prr,
1067 pay_run_result_values prrv1,
1068 pay_input_values_f pivf,
1069 pay_element_types_f petf
1070 /* pay_run_result_values prrv2,
1071 pay_run_result_values prrv3*/
1072 WHERE ppa.effective_date BETWEEN csr_v_start_date --'01-feb-2000' --p_group_start_date --'01-jun-1999' --p_report_start_date
1073 AND csr_v_end_date --'28-feb-2000'
1074 /* AND p_group_end_date /*'01-jun-2000' */--p_report_end_date
1075 AND ppa.payroll_action_id = paa.payroll_action_id
1076 AND paa.assignment_id =csr_v_assignment_id --32488 --p_assignment_id --21035 --p_assignment_id
1077 AND paa.assignment_action_id = prr.assignment_action_id
1078 AND prr.element_type_id = petf.element_type_id --62358 -- p_element_type_id
1079 AND petf.element_type_id=csr_v_element_type_id --'Sick Pay 1 to 14 days' --p_element_name --'Sick Pay 1 to 14 days'
1080 AND petf.element_type_id=pivf.element_type_id
1081 AND pivf.element_type_id=prr.element_type_id
1082 AND prr.run_result_id = prrv1.run_result_id
1083 AND prrv1.input_value_id =pivf.input_value_id --139722 --p_input_value_id;
1084 AND pivf.Input_value_id=csr_v_input_value_id; --'Sick Hours' --p_input_name; --'Waiting Day'*/
1085
1086 cursor csr_wages_details (csr_v_organization_id NUMBER, csr_v_category VARCHAR2, csr_v_display_name VARCHAR2)
1087 IS
1088 SELECT org_information3 Type, org_information4 Element_Type_Id, org_information5 Input_value_Id,
1089 org_information6 Balance_Type_Id,org_information7 Balance_Dimension_Id
1090 FROM hr_organization_information hoi
1091 WHERE hoi.organization_id=csr_v_organization_id --3134
1092 AND hoi.org_information_context='SE_WAGES_SALARY_DETAILS'
1093 AND hoi.org_information1=csr_v_category --'BH'
1094 AND hoi.org_information2=csr_v_display_name;--'CCD'
1095
1096 cursor csr_get_defined_balance(csr_v_balance_type_id NUMBER, csr_v_balance_dimension_id NUMBER)
1097 is
1098 SELECT defined_balance_id FROM pay_defined_balances
1099 WHERE
1100 balance_type_id=csr_v_balance_type_id --10504412
1101 AND balance_dimension_id=csr_v_balance_dimension_id; --5525498
1102
1103 cursor csr_person_assignment(csr_v_assignment_id number, csr_v_start_date date,csr_v_end_date date)
1104 is
1105 select 1 from
1106 per_all_people_f papf,
1107 per_all_assignments_f paaf
1108 where papf.person_id=paaf.person_id
1109 and paaf.assignment_id=csr_v_assignment_id
1110 and csr_v_end_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
1111 AND papf.effective_end_date
1112 and csr_v_end_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
1113 AND papf.effective_end_date
1114 AND months_between (csr_v_end_date,DATE_OF_BIRTH) >= 216 /* Age greater than 18 */
1115 AND months_between (csr_v_end_date,DATE_OF_BIRTH) < 768
1116 AND papf.CURRENT_EMPLOYEE_FLAG='Y' ;
1117
1118 cursor csr_category_insert(csr_v_payroll_action_id number, csr_v_category varchar2, csr_v_local_unit_id number )
1119 is
1120 select 1 from pay_action_information
1121 where action_context_id=csr_v_payroll_action_id --45446
1122 and action_information2=csr_v_category --'BH'
1123 AND action_information3=csr_v_local_unit_id;
1124
1125 cursor csr_local_unit_insert(csr_v_payroll_action_id number, csr_v_category varchar2, csr_v_local_unit_id number )
1126 is
1127 select 1 from pay_action_information
1128 where action_context_id=csr_v_payroll_action_id --45446
1129 and action_information2=csr_v_category --'BH'
1130 and action_information3=csr_v_local_unit_id;
1131
1132 CURSOR csr_payroll_period(csr_v_payroll_id number, csr_v_start_date DATE, csr_v_end_date DATE)
1133 IS
1134 SELECT papf.period_type, min(ptp.start_date),min(ptp.end_date)
1135 FROM per_time_periods ptp,
1136 pay_all_payrolls_f papf
1137 WHERE ptp.payroll_id=csr_v_payroll_id --4337 --3469
1138 AND ptp.payroll_id=papf.payroll_id
1139 AND /*'15-jan-2005'*/ ptp.START_DATE >=csr_v_start_date
1140 AND ptp.end_date <=csr_v_end_date
1141 AND csr_v_end_date between papf.EFFECTIVE_START_DATE
1142 AND papf.EFFECTIVE_end_DATE
1143 GROUP BY papf.period_type;
1144
1145 /*CURSOR csr_payroll(csr_v_assignment_id NUMBER, csr_v_start_date DATE, csr_v_end_date DATE)
1146 IS
1147 SELECT payroll_id
1148 FROM per_all_assignments_f
1149 WHERE assignment_id=csr_v_assignment_id
1150 AND csr_v_end_date BETWEEN effective_start_date AND
1151 effective_end_date*/
1152
1153 l_ovn NUMBER;
1154 l_action_info_id NUMBER;
1155 L_MONTH_START_DATE DATE;
1156 L_MONTH_END_DATE DATE;
1157 L_LOCAL_UNIT_NAME VARCHAR2(50);
1158 l_person_id NUMBER;
1159 l_sex CHAR(1);
1160 l_local_unit_id NUMBER;
1161 l_assignment_category VARCHAR2(5);
1162 l_assignment_start_date DATE;
1163 l_assignment_end_date DATE;
1164 l_absence_count NUMBER;
1165 l_employee_category per_all_assignments_f.employee_category%type;
1166 l_person_number per_all_people_f.national_identifier%TYPE;
1167 l_person_name VARCHAR2(350);
1168
1169 l_terminated VARCHAR2(50);
1170
1171 l_gross_salary number;
1172 --l_start_date date;
1173 --l_end_date date;
1174 l_termination_date date;
1175 lr_Get_Defined_Balance_Id pay_defined_balances.defined_balance_id%type;
1176 l_value number;
1177 l_assignment_id pay_Assignment_actions.assignment_id%type;
1178 l_assignment_action_id pay_Assignment_actions.assignment_action_id%type;
1179 L_CFAR_NUMBER NUMBER;
1180 l_legal_employer_id NUMBER;
1181 l_virtual_date DATE;
1182 l_date_birth per_all_people_f.DATE_OF_BIRTH%TYPE;
1183 l_counter NUMBER :=0;
1184 l_total_salary NUMBER;
1185 l_asg_start_date DATE;
1186 l_asg_end_date date;
1187 l_category per_all_assignments_f.employee_category%type;
1188 l_prev_category per_all_assignments_f.employee_category%type;
1189 l_working_percentage NUMBER;
1190 l_asg_hour_sal per_all_assignments_f.hourly_salaried_code%type;
1191 l_frequency per_all_assignments_f.frequency%type;
1192 l_normal_hours per_all_assignments_f.normal_hours%type;
1193 l_include_event char(1);
1194 l_wrk_schd_return NUMBER;
1195 l_wrk_duration NUMBER;
1196 l_absence_start_date DATE;
1197 l_absence_end_date DATE;
1198 l_bh_worked_calendar_month NUMBER:=0;
1199 l_bh_absence_days NUMBER:=0;
1200 l_bh_worked_payment_period NUMBER:=0;
1201 l_bh_total_employees NUMBER:=0;
1202 l_bs_total_employees NUMBER:=0;
1203 l_ws_total_employees NUMBER:=0;
1204 l_wh_total_employees NUMBER:=0;
1205 l_ws_full_time_employee NUMBER:=0;
1206 l_wh_full_time_employee NUMBER:=0;
1207 l_bs_gross_pay NUMBER:=0;
1208 l_ws_gross_pay NUMBER:=0;
1209 l_wh_gross_pay NUMBER:=0;
1210 l_bh_retroactive_pay NUMBER:=0;
1211 l_bs_retroactive_pay NUMBER:=0;
1212 l_ws_retroactive_pay NUMBER:=0;
1213 l_wh_retroactive_pay NUMBER:=0;
1214 l_bh_sick_pay NUMBER:=0;
1215 l_bs_sick_pay NUMBER:=0;
1216 l_ws_sick_pay NUMBER:=0;
1217 l_wh_sick_pay NUMBER:=0;
1218 l_start_time_char Varchar2(10) :=NULL; -- '0';
1219 l_end_time_char Varchar2(10) :=NULL; -- '23.59';
1220 l_bs_working_agreement NUMBER:=0;
1221 l_ws_working_agreement NUMBER:=0;
1222 l_wh_working_agreement NUMBER:=0;
1223 l_type varchar2(50);
1224
1225 l_bh_pbt_value NUMBER:=0;
1226 l_bh_pcow_value NUMBER:=0;
1227 l_bh_nha_value NUMBER:=0;
1228 l_bh_nho_value NUMBER:=0;
1229 l_bh_ppo_value NUMBER:=0;
1230
1231
1232 l_bs_tcdp_value NUMBER:=0;
1233 l_bs_tcow_value NUMBER:=0;
1234 l_bs_nha_value NUMBER:=0;
1235 l_bs_nho_value NUMBER:=0;
1236 l_bs_ppo_value NUMBER:=0;
1237
1238 l_ws_tcdp_value NUMBER:=0;
1239 l_ws_tcow_value NUMBER:=0;
1240 l_ws_nha_value NUMBER:=0;
1241 l_ws_nho_value NUMBER:=0;
1242 l_ws_ppo_value NUMBER:=0;
1243
1244 l_wh_tcdp_value NUMBER:=0;
1245 l_wh_ppo_value NUMBER:=0;
1246 l_valid_person number;
1247 l_check_insert number;
1248
1249 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1250 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1251 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
1252 l_balance_dimension_id pay_balance_dimensions.balance_dimension_id%TYPE;
1253 l_period per_time_period_types.period_type%TYPE;
1254 l_period_start per_time_periods.start_date%TYPE;
1255 l_period_end per_time_periods.end_date%TYPE;
1256 l_bh_worked_period NUMBER:=0;
1257 l_work_hours_days char(1):='D';
1258 l_payroll_id per_all_assignments_f.payroll_id%TYPE;
1259
1260 TYPE emp_cat_type
1261 IS TABLE OF VARCHAR2(10)
1262 INDEX BY BINARY_INTEGER;
1263 emp_cat emp_cat_type;
1264
1265 TYPE emp_job_record IS RECORD
1266 (
1267 job VARCHAR2(5),
1268 end_date date
1269 );
1270 TYPE emp_job_type
1271 IS TABLE OF emp_job_record
1272 INDEX BY BINARY_INTEGER;
1273 emp_job emp_job_type;
1274
1275 TYPE emp_detail_record IS RECORD
1276 (
1277 l_start_date date,
1278 l_end_date date,
1279 l_category varchar2(5),
1280 l_job varchar2(5),
1281 l_gross_salary number(17,2),
1282 l_termination varchar2(5),
1283 l_white_from date
1284 );
1285 TYPE emp_record_type
1286 IS TABLE OF emp_detail_record
1287 INDEX BY BINARY_INTEGER;
1288 emp_record emp_record_type;
1289 --------------
1290
1291 BEGIN
1292 IF g_debug
1293 THEN
1294 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
1295 END IF;
1296
1297 /* TCDP --> Total of variable add.pay excl. of comp for on-call duties and payment in kind (SEK)
1298 TCOW --> Total variable, compensation for overtime worked (SEK)
1299 CCD --> Compensation for on-call duties and payment in kind (SEK)
1300 PBT --> Paid out salaries (for hours worked) before tax deduction (gross pay)
1301 PCOW --> Paid out compensation for overtime worked (SEK)
1302 NHA --> Number of Hours worked in the actual payment period
1303 NHO --> Number of Hours, number of hours overtime worked(hours)
1304 PPO --> Varaiable add. payments from previous payments periods (SEK)*/
1305
1306 g_payroll_action_id := p_payroll_action_id;
1307 g_business_group_id := NULL;
1308 g_effective_date := NULL;
1309 g_LU_request :=null;
1310 g_legal_employer_id := NULL;
1311 g_local_unit_id := NULL;
1312 g_account_date :=null;
1313 g_posting_date :=null;
1314 get_all_parameters (p_payroll_action_id
1315 , g_business_group_id
1316 , g_effective_date
1317 , g_legal_employer_id
1318 , g_LU_request
1319 , g_local_unit_id
1320 , g_month
1321 , g_year
1322 , g_retroactive_payment_from
1323 , g_retroactive_payment_to
1324 );
1325 g_start_date:=to_date('01-' || g_month || '-' || g_year, 'dd-mm-yyyy');
1326 --g_end_date:=to_date('31-'|| g_month || '-' || g_year, 'dd-mm-yyyy');
1327 g_end_date:=last_day(to_date('01-'|| g_month || '-' || g_year, 'dd-mm-yyyy'));
1328
1329
1330
1331 OPEN csr_legal_employer_details(g_legal_employer_id);
1332 FETCH csr_legal_employer_details INTO l_legal_employer_name;
1333 CLOSE csr_legal_employer_details;
1334
1335 IF g_local_unit_id IS NOT NULL THEN
1336
1337 OPEN csr_local_unit_details(g_local_unit_id);
1338 FETCH csr_local_unit_details INTO L_LOCAL_UNIT_NAME;
1339 CLOSE csr_local_unit_details;
1340
1341 END IF;
1342 l_local_unit_id:=g_local_unit_id;
1343
1344 IF g_LU_request ='LU_SELECTED' THEN
1345 /* THis is for Given LOCAL UNIT */
1346
1347
1348 OPEN csr_CFAR_FROM_LU (g_local_unit_id);
1349 FETCH csr_CFAR_FROM_LU INTO lr_CFAR_FROM_LU;
1350 CLOSE csr_CFAR_FROM_LU;
1351
1352 L_CFAR_NUMBER :=lr_CFAR_FROM_LU.CFAR;
1353 l_local_unit_name:=lr_CFAR_FROM_LU.LU_NAME;
1354
1355 /* check whether record has been inserted for White Collar Hourly Employee */
1356 open csr_local_unit_insert(p_payroll_action_id,'LU',g_local_unit_id);
1357 fetch csr_local_unit_insert into l_check_insert;
1358 close csr_local_unit_insert;
1359 if l_check_insert is null then
1360 pay_action_information_api.create_action_information
1361 (p_action_information_id => l_action_info_id
1362 , p_action_context_id => p_payroll_action_id
1363 , p_action_context_type => 'PA'
1364 , p_object_version_number => l_ovn
1365 , p_effective_date => g_effective_date
1366 , p_source_id => NULL
1367 , p_source_text => NULL
1368 , p_action_information_category => 'EMEA REPORT INFORMATION'
1369 , p_action_information1 => 'PYSEWSSA'
1370 , p_action_information2 => 'LU'
1371 , p_action_information3 => g_local_unit_id
1372 , p_action_information4 => l_local_unit_name --lr_CFAR_FROM_LU.LU_NAME
1373 , p_action_information5 => null --L_CFAR_NUMBER
1374 , p_action_information6 => NULL
1375 , p_action_information7 => NULL
1376 );
1377 end if;
1378 -- FOR csr_person IN csr_person_local_unit(g_business_group_id, g_local_unit_id, g_end_date /*g_effective_date*/) LOOP
1379 --l_person_id:=csr_person.person_id;
1380 --fnd_file.put_line(fnd_file.LOG,'l_person_id'||l_person_id);
1381 pay_balance_pkg.set_context('ASSIGNMENT_ID',l_assignment_id); --133942);
1382 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_local_unit_id); --3621);
1383 FOR csr_assignment IN csr_wage_assignment(g_business_group_id, g_local_unit_id, g_end_date) LOOP
1384 l_assignment_id:=csr_assignment.assignment_id;
1385 l_working_percentage:=csr_assignment.working_percentage;
1386 l_asg_hour_sal:=csr_assignment.hourly_salaried_code;
1387 l_employee_category:=csr_assignment.employee_category;
1388 l_frequency:=csr_assignment.frequency;
1389 l_normal_hours:=csr_assignment.normal_hours;
1390 l_payroll_id:=csr_assignment.payroll_id;
1391 /* Calculating the number of days actually worked */
1392 /* Blue Collar Hourly Employee */
1393 open csr_person_assignment(l_assignment_id,g_start_date,g_end_date);
1394 fetch csr_person_assignment into l_valid_person;
1395 close csr_person_assignment;
1396 IF l_valid_person is not null THEN
1397 /* Getting the payroll period and payroll details */
1398 OPEN csr_payroll_period(l_payroll_id,g_start_Date, g_end_date);
1399 FETCH csr_payroll_period INTO l_period,l_period_start,l_period_end;
1400 CLOSE csr_payroll_period;
1401
1402 IF l_employee_category='BC' AND l_asg_hour_sal='H' THEN
1403 l_include_event:='Y';
1404 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1405 ( l_assignment_id, l_work_hours_days, l_include_event,
1406 g_start_Date, g_end_date,l_start_time_char,
1407 l_end_time_char, l_wrk_duration
1408 );
1409 l_bh_worked_calendar_month:=l_wrk_duration;
1410 --l_bh_worked_calendar_month:=l_bh_worked_calendar_month+l_wrk_duration;
1411 l_wrk_duration:=0;
1412 IF l_period_end IS NOT NULL THEN
1413 FOR csr_absence IN csr_assignment_absence(l_assignment_id, l_period_start, l_period_end) LOOP
1414 l_absence_start_date:= csr_absence.start_date;
1415 l_absence_end_date:= csr_absence.end_date;
1416 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1417 ( l_assignment_id, l_work_hours_days, l_include_event,
1418 l_absence_start_date, l_absence_end_date,l_start_time_char,
1419 l_end_time_char, l_wrk_duration
1420 );
1421 l_bh_absence_days:= l_bh_absence_days+l_wrk_duration;
1422 l_wrk_duration:=0;
1423 END LOOP;
1424 /* To get the working days within the period */
1425 l_include_event:='Y';
1426 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1427 ( l_assignment_id, l_work_hours_days, l_include_event,
1428 l_period_start, l_period_end,l_start_time_char,
1429 l_end_time_char, l_wrk_duration
1430 );
1431 l_bh_worked_period:=l_wrk_duration;
1432 l_bh_worked_period:=l_bh_worked_period-l_bh_absence_days;
1433 IF l_period='Week' THEN
1434 l_bh_worked_payment_period:=l_bh_worked_payment_period+round(l_bh_worked_period*2);
1435 ELSIF l_period='Calendar Month' THEN
1436 l_bh_worked_payment_period:=l_bh_worked_payment_period+ round(l_bh_worked_period/2);
1437 END IF;
1438 --l_bh_absence_days:=0;
1439 /* PBT Value */
1440 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'PBT') LOOP
1441 l_type:=csr_wages.Type;
1442 l_element_type_id:=csr_wages.Element_Type_Id;
1443 l_input_value_id:=csr_wages.Input_value_Id;
1444 l_balance_type_id:=csr_wages.Balance_Type_Id;
1445 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1446
1447 /* check whether value has been entered in EIT*/
1448 IF l_type IS NOT NULL THEN
1449 /* If element is selected */
1450 IF l_type='ELEMENT' THEN
1451 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
1452 FETCH csr_element_types INTO l_value;
1453 CLOSE csr_element_types;
1454
1455 ELSE
1456 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1457 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1458 CLOSE csr_get_defined_balance;
1459
1460 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1461 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1462 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
1463 END IF;
1464 IF l_period='Week' THEN
1465 l_value:=l_value*2;
1466 ELSIF l_period='Calendar Month' THEN
1467 l_value:=l_value/2;
1468 END IF;
1469 --l_bh_pbt_value:=l_value;
1470 l_bh_pbt_value:=l_bh_pbt_value+round(nvl(l_value,0));
1471 l_value:=NULL;
1472 l_type:=NULL;
1473
1474 END IF;
1475 END LOOP;
1476 /* PCOW Value */
1477 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'PCOW') LOOP
1478 l_type:=csr_wages.Type;
1479 l_element_type_id:=csr_wages.Element_Type_Id;
1480 l_input_value_id:=csr_wages.Input_value_Id;
1481 l_balance_type_id:=csr_wages.Balance_Type_Id;
1482 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1483
1484 /* check whether value has been entered in EIT*/
1485 IF l_type IS NOT NULL THEN
1486 /* If element is selected */
1487 IF l_type='ELEMENT' THEN
1488 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
1489 FETCH csr_element_types INTO l_value;
1490 CLOSE csr_element_types;
1491
1492 ELSE
1493 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1494 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1495 CLOSE csr_get_defined_balance;
1496
1497 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1498 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1499 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
1500 END IF;
1501 IF l_period='Week' THEN
1502 l_value:=l_value*2;
1503 ELSIF l_period='Calendar Month' THEN
1504 l_value:=l_value/2;
1505 END IF;
1506 --l_bh_pcow_value:=l_value;
1507 l_bh_pcow_value:=l_bh_pcow_value+round(nvl(l_value,0));
1508 l_value:=NULL;
1509 l_type:=NULL;
1510 END IF;
1511 END LOOP;
1512 /* NHA Value */
1513 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'NHA') LOOP
1514 l_type:=csr_wages.Type;
1515 l_element_type_id:=csr_wages.Element_Type_Id;
1516 l_input_value_id:=csr_wages.Input_value_Id;
1517 l_balance_type_id:=csr_wages.Balance_Type_Id;
1518 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1519
1520 /* check whether value has been entered in EIT*/
1521 IF l_type IS NOT NULL THEN
1522 /* If element is selected */
1523 IF l_type='ELEMENT' THEN
1524 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
1525 FETCH csr_element_types INTO l_value;
1526 CLOSE csr_element_types;
1527
1528 ELSE
1529 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1530 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1531 CLOSE csr_get_defined_balance;
1532
1533 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1534 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1535 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
1536 END IF;
1537 IF l_period='Week' THEN
1538 l_value:=l_value*2;
1539 ELSIF l_period='Calendar Month' THEN
1540 l_value:=l_value/2;
1541 END IF;
1542 --l_bh_nha_value:=l_value;
1543 l_bh_nha_value:=l_bh_nha_value+round(nvl(l_value,0));
1544 l_value:=NULL;
1545 l_type:=NULL;
1546 END IF;
1547 END LOOP;
1548 /* NHO Value */
1549 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'NHO') LOOP
1550 l_type:=csr_wages.Type;
1551 l_element_type_id:=csr_wages.Element_Type_Id;
1552 l_input_value_id:=csr_wages.Input_value_Id;
1553 l_balance_type_id:=csr_wages.Balance_Type_Id;
1554 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1555
1556 /* check whether value has been entered in EIT*/
1557 IF l_type IS NOT NULL THEN
1558 /* If element is selected */
1559 IF l_type='ELEMENT' THEN
1560 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
1561 FETCH csr_element_types INTO l_value;
1562 CLOSE csr_element_types;
1563
1564 ELSE
1565 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1566 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1567 CLOSE csr_get_defined_balance;
1568
1569 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1570 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1571 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
1572 END IF;
1573 IF l_period='Week' THEN
1574 l_value:=l_value*2;
1575 ELSIF l_period='Calendar Month' THEN
1576 l_value:=l_value/2;
1577 END IF;
1578 --l_bh_nho_value:=l_value;
1579 l_bh_nho_value:=l_bh_nho_value+round(nvl(l_value,0));
1580 l_value:=NULL;
1581 l_type:=NULL;
1582 END IF;
1583 END LOOP;
1584 /*Retroactive payment _ASG_LU_PTD */
1585 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
1586 OPEN csr_Get_Defined_Balance_Id( 'RETROSPECTIVE_PAYMENTS_ASG_LU_PTD');
1587 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1588 CLOSE csr_Get_Defined_Balance_Id;
1589 l_value:=nvl(pay_balance_pkg.get_value
1590 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1591 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
1592 P_VIRTUAL_DATE =>l_period_end-- '31-jan-2000'
1593 ),0);
1594 --l_bh_retroactive_pay:=l_value;
1595 IF l_period='Week' THEN
1596 l_value:=l_value*2;
1597 ELSIF l_period='Calendar Month' THEN
1598 l_value:=l_value/2;
1599 END IF;
1600 l_bh_retroactive_pay:=l_bh_retroactive_pay+round(nvl(l_value,0));
1601 l_value:=NULL;
1602 l_type:=NULL;
1603 /* PPO Value */
1604 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'PPO') LOOP
1605 l_type:=csr_wages.Type;
1606 l_element_type_id:=csr_wages.Element_Type_Id;
1607 l_input_value_id:=csr_wages.Input_value_Id;
1608 l_balance_type_id:=csr_wages.Balance_Type_Id;
1609 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1610
1611 /* check whether value has been entered in EIT*/
1612 IF l_type IS NOT NULL THEN
1613 /* If element is selected */
1614 IF l_type='ELEMENT' THEN
1615 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
1616 FETCH csr_element_types INTO l_value;
1617 CLOSE csr_element_types;
1618
1619 ELSE
1620 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1621 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1622 CLOSE csr_get_defined_balance;
1623
1624 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1625 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1626 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
1627 END IF;
1628 IF l_period='Week' THEN
1629 l_value:=l_value*2;
1630 ELSIF l_period='Calendar Month' THEN
1631 l_value:=l_value/2;
1632 END IF;
1633 --l_bh_ppo_value:=l_value;
1634 l_bh_ppo_value:=l_bh_ppo_value+round(nvl(l_value,0));
1635 l_value:=NULL;
1636 l_type:=NULL;
1637 END IF;
1638 END LOOP;
1639
1640 /*Total Sick Pay _ASG_LU_PTD */
1641 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
1642 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SICK_PAY_ASG_LU_PTD');
1643 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1644 CLOSE csr_Get_Defined_Balance_Id;
1645 l_value:=nvl(pay_balance_pkg.get_value
1646 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1647 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
1648 P_VIRTUAL_DATE =>l_period_end-- '31-jan-2000'
1649 ),0);
1650 --l_bh_sick_pay:=l_value;
1651 IF l_period='Week' THEN
1652 l_value:=l_value*2;
1653 ELSIF l_period='Calendar Month' THEN
1654 l_value:=l_value/2;
1655 END IF;
1656 l_bh_sick_pay:=l_bh_sick_pay+round(nvl(l_value,0));
1657 l_value:=NULL;
1658 l_type:=NULL;
1659 END IF;
1660 /*Total count of the employees*/
1661 l_bh_total_employees:=l_bh_total_employees+1;
1662
1663 /* Blue Collar Salaried Employee */
1664 ELSIF l_employee_category='BC' AND l_asg_hour_sal='S' THEN
1665 /*Gross Pay _ASG_LU_MONTH */
1666 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
1667 OPEN csr_Get_Defined_Balance_Id( 'GROSS_PAY_ASG_LU_MONTH');
1668 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1669 CLOSE csr_Get_Defined_Balance_Id;
1670 l_value:=nvl(pay_balance_pkg.get_value
1671 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1672 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
1673 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
1674 ),0);
1675 --l_bs_gross_pay:=l_value;
1676 l_bs_gross_pay:=l_bs_gross_pay+round(nvl(l_value,0));
1677 l_value:=NULL;
1678 l_type:=NULL;
1679 /*Total Working hours agreement */
1680 /*IF l_frequency='M' THEN
1681 l_bs_working_agreement:=l_bs_working_agreement+l_normal_hours;
1682 END IF;*/
1683 IF l_frequency='W' THEN
1684 l_normal_hours:=l_normal_hours*4.3;
1685 END IF;
1686 l_bs_working_agreement:=l_bs_working_agreement+round(l_normal_hours);
1687 /* TCDP Value */
1688 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'TCDP') LOOP
1689 l_type:=csr_wages.Type;
1690 l_element_type_id:=csr_wages.Element_Type_Id;
1691 l_input_value_id:=csr_wages.Input_value_Id;
1692 l_balance_type_id:=csr_wages.Balance_Type_Id;
1693 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1694 /* check whether value has been entered in EIT*/
1695 IF l_type IS NOT NULL THEN
1696 /* If element is selected */
1697 IF l_type='ELEMENT' THEN
1698 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
1699 FETCH csr_element_types INTO l_value;
1700 CLOSE csr_element_types;
1701
1702 ELSE
1703 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1704 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1705 CLOSE csr_get_defined_balance;
1706
1707 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1708 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1709 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
1710 END IF;
1711 --l_bs_tcdp_value:=l_value;
1712 l_bs_tcdp_value:=l_bs_tcdp_value+round(nvl(l_value,0));
1713 l_value:=NULL;
1714 l_type:=NULL;
1715 END IF;
1716 END LOOP;
1717 /* TCOW Value */
1718 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'TCOW') LOOP
1719 l_type:=csr_wages.Type;
1720 l_element_type_id:=csr_wages.Element_Type_Id;
1721 l_input_value_id:=csr_wages.Input_value_Id;
1722 l_balance_type_id:=csr_wages.Balance_Type_Id;
1723 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1724
1725 /* check whether value has been entered in EIT*/
1726 IF l_type IS NOT NULL THEN
1727 /* If element is selected */
1728 IF l_type='ELEMENT' THEN
1729 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
1730 FETCH csr_element_types INTO l_value;
1731 CLOSE csr_element_types;
1732
1733 ELSE
1734 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1735 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1736 CLOSE csr_get_defined_balance;
1737
1738 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1739 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1740 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
1741 END IF;
1742 --l_bs_tcow_value:=l_value;
1743 l_bs_tcow_value:=l_bs_tcow_value+round(nvl(l_value,0));
1744 l_value:=NULL;
1745 l_type:=NULL;
1746 END IF;
1747 END LOOP;
1748 /* NHA Value */
1749 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'NHA') LOOP
1750 l_type:=csr_wages.Type;
1751 l_element_type_id:=csr_wages.Element_Type_Id;
1752 l_input_value_id:=csr_wages.Input_value_Id;
1753 l_balance_type_id:=csr_wages.Balance_Type_Id;
1754 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1755 /* check whether value has been entered in EIT*/
1756 IF l_type IS NOT NULL THEN
1757 /* If element is selected */
1758 IF l_type='ELEMENT' THEN
1759 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
1760 FETCH csr_element_types INTO l_value;
1761 CLOSE csr_element_types;
1762 ELSE
1763 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1764 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1765 CLOSE csr_get_defined_balance;
1766 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1767 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1768 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
1769 END IF;
1770 --l_bs_nha_value:=l_value;
1771 l_bs_nha_value:=l_bs_nha_value+round(nvl(l_value,0));
1772 l_value:=NULL;
1773 l_type:=NULL;
1774 END IF;
1775 END LOOP;
1776 /* NHO Value */
1777 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'NHO') LOOP
1778 l_type:=csr_wages.Type;
1779 l_element_type_id:=csr_wages.Element_Type_Id;
1780 l_input_value_id:=csr_wages.Input_value_Id;
1781 l_balance_type_id:=csr_wages.Balance_Type_Id;
1782 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1783 /* check whether value has been entered in EIT*/
1784 IF l_type IS NOT NULL THEN
1785 /* If element is selected */
1786 IF l_type='ELEMENT' THEN
1787 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
1788 FETCH csr_element_types INTO l_value;
1789 CLOSE csr_element_types;
1790 ELSE
1791 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1792 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1793 CLOSE csr_get_defined_balance;
1794
1795 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1796 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1797 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
1798 END IF;
1799 --l_bs_nho_value:=l_value;
1800 l_bs_nho_value:=l_bs_nho_value+round(nvl(l_value,0));
1801 l_value:=NULL;
1802 l_type:=NULL;
1803 lr_Get_Defined_Balance_Id:=NULL;
1804
1805 END IF;
1806 END LOOP;
1807 /*Retroactive payment _ASG_LU_MONTH */
1808 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
1809 OPEN csr_Get_Defined_Balance_Id( 'RETROSPECTIVE_PAYMENTS_ASG_LU_MONTH');
1810 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1811 CLOSE csr_Get_Defined_Balance_Id;
1812 l_value:=nvl(pay_balance_pkg.get_value
1813 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1814 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
1815 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
1816 ),0);
1817 --l_bs_retroactive_pay:=l_value;
1818 l_bs_retroactive_pay:=l_bs_retroactive_pay+round(nvl(l_value,0));
1819 l_value:=NULL;
1820 l_type:=NULL;
1821 /* PPO Value */
1822 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'PPO') LOOP
1823 l_type:=csr_wages.Type;
1824 l_element_type_id:=csr_wages.Element_Type_Id;
1825 l_input_value_id:=csr_wages.Input_value_Id;
1826 l_balance_type_id:=csr_wages.Balance_Type_Id;
1827 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1828 /* check whether value has been entered in EIT*/
1829 IF l_type IS NOT NULL THEN
1830 /* If element is selected */
1831 IF l_type='ELEMENT' THEN
1832 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
1833 FETCH csr_element_types INTO l_value;
1834 CLOSE csr_element_types;
1835
1836 ELSE
1837 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1838 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1839 CLOSE csr_get_defined_balance;
1840
1841 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1842 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1843 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
1844 END IF;
1845 --l_bs_ppo_value:=l_value;
1846 l_bs_ppo_value:=l_bs_ppo_value+round(nvl(l_value,0));
1847 l_value:=NULL;
1848 l_type:=NULL;
1849 END IF;
1850 END LOOP;
1851 /*Total Sick Pay _ASG_LU_MONTH */
1852 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
1853 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SICK_PAY_ASG_LU_MONTH');
1854 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1855 CLOSE csr_Get_Defined_Balance_Id;
1856 l_value:=nvl(pay_balance_pkg.get_value
1857 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1858 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
1859 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
1860 ),0);
1861 --l_bs_sick_pay:=l_value;
1862 l_bs_sick_pay:=l_bs_sick_pay+round(nvl(l_value,0));
1863 l_value:=NULL;
1864 l_type:=NULL;
1865 /*Total count of the employees*/
1866 l_bs_total_employees:=l_bs_total_employees+1;
1867
1868 /*White Collar Salaried Employee */
1869 ELSIF l_employee_category='WC' AND l_asg_hour_sal='S' THEN
1870 /* Number of Full Time Employees */
1871 --IF l_working_percentage=100 THEN
1872
1873 l_ws_full_time_employee:=l_ws_full_time_employee+round(nvl(l_working_percentage,100)/100,2);
1874 --END IF;
1875
1876 /*Gross Pay _ASG_LU_MONTH */
1877 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
1878 OPEN csr_Get_Defined_Balance_Id( 'GROSS_PAY_ASG_LU_MONTH');
1879 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1880 CLOSE csr_Get_Defined_Balance_Id;
1881 l_value:=nvl(pay_balance_pkg.get_value
1882 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1883 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
1884 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
1885 ),0);
1886 --l_ws_gross_pay:=l_value;
1887 l_ws_gross_pay:=l_ws_gross_pay+round(nvl(l_value,0));
1888 l_value:=NULL;
1889 l_type:=NULL;
1890 /*Total Working hours agreement */
1891 IF l_frequency='W' THEN
1892 l_normal_hours:=l_normal_hours*4.3;
1893 END IF;
1894 l_ws_working_agreement:=l_ws_working_agreement+round(l_normal_hours);
1895
1896
1897 /* TCDP Value */
1898 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'TCDP') LOOP
1899 l_type:=csr_wages.Type;
1900 l_element_type_id:=csr_wages.Element_Type_Id;
1901 l_input_value_id:=csr_wages.Input_value_Id;
1902 l_balance_type_id:=csr_wages.Balance_Type_Id;
1903 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1904
1905 /* check whether value has been entered in EIT*/
1906 IF l_type IS NOT NULL THEN
1907 /* If element is selected */
1908 IF l_type='ELEMENT' THEN
1909 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
1910 FETCH csr_element_types INTO l_value;
1911 CLOSE csr_element_types;
1912
1913 ELSE
1914 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1915 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1916 CLOSE csr_get_defined_balance;
1917
1918 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1919 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1920 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
1921 END IF;
1922 --l_ws_tcdp_value:=l_value;
1923 l_ws_tcdp_value:=l_ws_tcdp_value+round(nvl(l_value,0));
1924 l_value:=NULL;
1925 l_type:=NULL;
1926 END IF;
1927 END LOOP;
1928 /* TCOW Value */
1929 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'TCOW') LOOP
1930 l_type:=csr_wages.Type;
1931 l_element_type_id:=csr_wages.Element_Type_Id;
1932 l_input_value_id:=csr_wages.Input_value_Id;
1933 l_balance_type_id:=csr_wages.Balance_Type_Id;
1934 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1935
1936 /* check whether value has been entered in EIT*/
1937 IF l_type IS NOT NULL THEN
1938 /* If element is selected */
1939 IF l_type='ELEMENT' THEN
1940 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
1941 FETCH csr_element_types INTO l_value;
1942 CLOSE csr_element_types;
1943
1944 ELSE
1945 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1946 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1947 CLOSE csr_get_defined_balance;
1948
1949 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1950 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1951 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
1952 END IF;
1953 --l_ws_tcow_value:=l_value;
1954 l_ws_tcow_value:=l_ws_tcow_value+round(nvl(l_value,0));
1955 l_value:=NULL;
1956 l_type:=NULL;
1957 END IF;
1958 END LOOP;
1959 /* NHA Value */
1960 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'NHA') LOOP
1961 l_type:=csr_wages.Type;
1962 l_element_type_id:=csr_wages.Element_Type_Id;
1963 l_input_value_id:=csr_wages.Input_value_Id;
1964 l_balance_type_id:=csr_wages.Balance_Type_Id;
1965 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1966
1967 /* check whether value has been entered in EIT*/
1968 IF l_type IS NOT NULL THEN
1969 /* If element is selected */
1970 IF l_type='ELEMENT' THEN
1971 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
1972 FETCH csr_element_types INTO l_value;
1973 CLOSE csr_element_types;
1974
1975 ELSE
1976 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
1977 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
1978 CLOSE csr_get_defined_balance;
1979
1980 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
1981 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
1982 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
1983 END IF;
1984 --l_ws_nha_value:=l_value;
1985 l_ws_nha_value:=l_ws_nha_value+round(nvl(l_value,0));
1986 l_value:=NULL;
1987 l_type:=NULL;
1988 END IF;
1989 END LOOP;
1990 /* NHO Value */
1991 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'NHO') LOOP
1992 l_type:=csr_wages.Type;
1993 l_element_type_id:=csr_wages.Element_Type_Id;
1994 l_input_value_id:=csr_wages.Input_value_Id;
1995 l_balance_type_id:=csr_wages.Balance_Type_Id;
1996 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
1997
1998 /* check whether value has been entered in EIT*/
1999 IF l_type IS NOT NULL THEN
2000 /* If element is selected */
2001 IF l_type='ELEMENT' THEN
2002 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2003 FETCH csr_element_types INTO l_value;
2004 CLOSE csr_element_types;
2005
2006 ELSE
2007 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2008 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2009 CLOSE csr_get_defined_balance;
2010
2011 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2012 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2013 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2014 END IF;
2015 --l_ws_nho_value:=l_value;
2016 l_ws_nho_value:=l_ws_nho_value+round(nvl(l_value,0));
2017 l_value:=NULL;
2018 l_type:=NULL;
2019 END IF;
2020 END LOOP;
2021 /*Retroactive payment _ASG_LU_MONTH */
2022 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
2023 OPEN csr_Get_Defined_Balance_Id( 'RETROSPECTIVE_PAYMENTS_ASG_LU_MONTH');
2024 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2025 CLOSE csr_Get_Defined_Balance_Id;
2026 l_value:=nvl(pay_balance_pkg.get_value
2027 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2028 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2029 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
2030 ),0);
2031 --l_ws_retroactive_pay:=l_value;
2032 l_ws_retroactive_pay:=l_ws_retroactive_pay+round(nvl(l_value,0));
2033 l_value:=NULL;
2034 l_type:=NULL;
2035
2036 /* PPO Value */
2037 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'PPO') LOOP
2038 l_type:=csr_wages.Type;
2039 l_element_type_id:=csr_wages.Element_Type_Id;
2040 l_input_value_id:=csr_wages.Input_value_Id;
2041 l_balance_type_id:=csr_wages.Balance_Type_Id;
2042 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2043
2044 /* check whether value has been entered in EIT*/
2045 IF l_type IS NOT NULL THEN
2046 /* If element is selected */
2047 IF l_type='ELEMENT' THEN
2048 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2049 FETCH csr_element_types INTO l_value;
2050 CLOSE csr_element_types;
2051
2052 ELSE
2053 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2054 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2055 CLOSE csr_get_defined_balance;
2056
2057 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2058 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2059 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2060 --l_ws_ppo_value:=l_value;
2061 l_ws_ppo_value:=l_ws_ppo_value+round(nvl(l_value,0));
2062 l_value:=NULL;
2063 l_type:=NULL;
2064 END IF;
2065 END IF;
2066 END LOOP;
2067 /*Total Sick Pay _ASG_LU_MONTH */
2068 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
2069 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SICK_PAY_ASG_LU_MONTH');
2070 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2071 CLOSE csr_Get_Defined_Balance_Id;
2072 l_value:=nvl(pay_balance_pkg.get_value
2073 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2074 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2075 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
2076 ),0);
2077 --l_ws_sick_pay:=l_value;
2078 l_ws_sick_pay:=l_ws_sick_pay+round(nvl(l_value,0));
2079 l_value:=NULL;
2080 l_type:=NULL;
2081
2082 /*Total count of the employees*/
2083 l_ws_total_employees:=l_ws_total_employees+1;
2084 END IF;
2085 l_type:=NULL;
2086 l_element_type_id:=NULL;
2087 l_input_value_id:=NULL;
2088 l_balance_type_id:=NULL;
2089 l_balance_dimension_id:=NULL;
2090 l_period:=NULL;
2091 l_period_start:=NULL;
2092 l_period_end:=NULL;
2093 END IF;
2094 l_valid_person:=null;
2095 END LOOP;
2096 /* check whether there are Blue collar Hourly Employees on current Local unit*/
2097 IF l_bh_total_employees<>0 THEN
2098 /* check whether record has been inserted for Blue Collar Hourly Employee */
2099 open csr_category_insert(p_payroll_action_id,'BH',l_local_unit_id);
2100 fetch csr_category_insert into l_check_insert;
2101 --close csr_category_insert;
2102 --if l_check_insert is null then
2103 IF csr_category_insert%NOTFOUND THEN
2104 /*Insert the record*/
2105 pay_action_information_api.create_action_information
2106 (p_action_information_id => l_action_info_id
2107 , p_action_context_id => p_payroll_action_id
2108 , p_action_context_type => 'PA'
2109 , p_object_version_number => l_ovn
2110 , p_effective_date => g_effective_date
2111 , p_source_id => NULL
2112 , p_source_text => NULL
2113 , p_action_information_category => 'EMEA REPORT INFORMATION'
2114 , p_action_information1 => 'PYSEWSSA'
2115 , p_action_information2 => 'BH'
2116 , p_action_information3 => l_local_unit_id
2117 , p_action_information4 => l_bh_worked_calendar_month
2118 , p_action_information5 => l_bh_worked_payment_period
2119 , p_action_information6 => NULL
2120 , p_action_information7 => NULL
2121 , p_action_information8 => NULL
2122 , p_action_information9 => NULL
2123 , p_action_information10 => NULL
2124 , p_action_information11 => NULL
2125 , p_action_information12 => l_bh_pbt_value
2126 , p_action_information13 => l_bh_pcow_value
2127 , p_action_information14 => l_bh_nha_value
2128 , p_action_information15 => l_bh_nho_value
2129 , p_action_information16 => l_bh_retroactive_pay
2130 , p_action_information17 => l_bh_ppo_value
2131 , p_action_information18 => l_bh_sick_pay
2132 , p_action_information19 => l_bh_total_employees
2133 );
2134 else
2135 /*update the record*/
2136 update pay_action_information set
2137 --action_information4=action_information4+l_bh_worked_calendar_month,
2138 action_information5=action_information5+l_bh_worked_payment_period,
2139 action_information12=action_information12+l_bh_pbt_value,
2140 action_information13=action_information13+l_bh_pcow_value,
2141 action_information14=action_information14+l_bh_nha_value,
2142 action_information15=action_information15+l_bh_nho_value,
2143 action_information16=action_information16+l_bh_retroactive_pay,
2144 action_information17=action_information17+l_bh_ppo_value,
2145 action_information18=action_information18+l_bh_sick_pay,
2146 action_information19=action_information19+l_bh_total_employees
2147 where action_context_id=p_payroll_action_id
2148 and action_information2='BH'
2149 AND action_information3=l_local_unit_id;
2150
2151 end if;
2152 close csr_category_insert;
2153 l_check_insert:=null;
2154 END IF;
2155 /* check whether there are Blue collar Salaried Employees on current Local unit*/
2156 IF l_bs_total_employees<>0 THEN
2157 /* check whether record has been inserted for Blue Collar Salaried Employee */
2158 open csr_category_insert(p_payroll_action_id,'BS',l_local_unit_id);
2159 fetch csr_category_insert into l_check_insert;
2160 --close csr_category_insert;
2161 --if l_check_insert is null then
2162 IF csr_category_insert%NOTFOUND THEN
2163 /*Insert the record*/
2164 pay_action_information_api.create_action_information
2165 (p_action_information_id => l_action_info_id
2166 , p_action_context_id => p_payroll_action_id
2167 , p_action_context_type => 'PA'
2168 , p_object_version_number => l_ovn
2169 , p_effective_date => g_effective_date
2170 , p_source_id => NULL
2171 , p_source_text => NULL
2172 , p_action_information_category => 'EMEA REPORT INFORMATION'
2173 , p_action_information1 => 'PYSEWSSA'
2174 , p_action_information2 => 'BS'
2175 , p_action_information3 => l_local_unit_id
2176 , p_action_information4 => null
2177 , p_action_information5 => null
2178 , p_action_information6 => null
2179 , p_action_information7 => l_bs_gross_pay
2180 , p_action_information8 => l_bs_working_agreement
2181 , p_action_information9 => l_bs_tcdp_value
2182 , p_action_information10 => l_bs_tcow_value
2183 , p_action_information11 => NULL
2184 , p_action_information12 => NULL
2185 , p_action_information13 => NULL
2186 , p_action_information14 => l_bs_nha_value
2187 , p_action_information15 => l_bs_nho_value
2188 , p_action_information16 => l_bs_retroactive_pay
2189 , p_action_information17 => l_bs_ppo_value
2190 , p_action_information18 => l_bs_sick_pay
2191 , p_action_information19 => l_bs_total_employees
2192 );
2193 else
2194 /*update the record*/
2195 update pay_action_information set
2196 action_information7=action_information7+l_bs_gross_pay,
2197 action_information8=action_information8+l_bs_working_agreement,
2198 action_information9=action_information9+l_bs_tcdp_value,
2199 action_information10=action_information10+l_bs_tcow_value,
2200 action_information14=action_information14+l_bs_nha_value,
2201 action_information15=action_information15+l_bs_nho_value,
2202 action_information16=action_information16+l_bs_retroactive_pay,
2203 action_information17=action_information17+l_bs_ppo_value,
2204 action_information18=action_information18+l_bs_sick_pay,
2205 action_information19=action_information19+l_bs_total_employees
2206 where action_context_id=p_payroll_action_id
2207 and action_information2='BS'
2208 AND action_information3=l_local_unit_id;
2209
2210 end if;
2211 close csr_category_insert;
2212 l_check_insert:=null;
2213 END IF;
2214 /* check whether there are White collar Salaried Employees on current Local unit*/
2215 IF l_ws_total_employees<>0 THEN
2216 /* check whether record has been inserted for White Collar Salaried Employee */
2217 open csr_category_insert(p_payroll_action_id,'WS',l_local_unit_id);
2218 fetch csr_category_insert into l_check_insert;
2219 --close csr_category_insert;
2220 --if l_check_insert is null then
2221 IF csr_category_insert%NOTFOUND THEN
2222 /*Insert the record*/
2223 pay_action_information_api.create_action_information
2224 (p_action_information_id => l_action_info_id
2225 , p_action_context_id => p_payroll_action_id
2226 , p_action_context_type => 'PA'
2227 , p_object_version_number => l_ovn
2228 , p_effective_date => g_effective_date
2229 , p_source_id => NULL
2230 , p_source_text => NULL
2231 , p_action_information_category => 'EMEA REPORT INFORMATION'
2232 , p_action_information1 => 'PYSEWSSA'
2233 , p_action_information2 => 'WS'
2234 , p_action_information3 => l_local_unit_id
2235 , p_action_information4 => null
2236 , p_action_information5 => null
2237 , p_action_information6 => l_ws_full_time_employee
2238 , p_action_information7 => l_ws_gross_pay
2239 , p_action_information8 => l_ws_working_agreement
2240 , p_action_information9 => l_ws_tcdp_value
2241 , p_action_information10 => l_ws_tcow_value
2242 , p_action_information11 => NULL
2243 , p_action_information12 => NULL
2244 , p_action_information13 => NULL
2245 , p_action_information14 => l_ws_nha_value
2246 , p_action_information15 => l_ws_nho_value
2247 , p_action_information16 => l_ws_retroactive_pay
2248 , p_action_information17 => l_ws_ppo_value
2249 , p_action_information18 => l_ws_sick_pay
2250 , p_action_information19 => l_ws_total_employees
2251 );
2252 else
2253 /*update the record*/
2254 update pay_action_information set
2255 action_information6=action_information6+l_ws_full_time_employee,
2256 action_information7=action_information7+l_ws_gross_pay,
2257 action_information8=action_information8+l_ws_working_agreement,
2258 action_information9=action_information9+l_ws_tcdp_value,
2259 action_information10=action_information10+l_ws_tcow_value,
2260 action_information14=action_information14+l_ws_nha_value,
2261 action_information15=action_information15+l_ws_nho_value,
2262 action_information16=action_information16+l_ws_retroactive_pay,
2263 action_information17=action_information17+l_ws_ppo_value,
2264 action_information18=action_information18+l_ws_sick_pay,
2265 action_information19=action_information19+l_ws_total_employees
2266 where action_context_id=p_payroll_action_id
2267 and action_information2='WS'
2268 AND action_information3=l_local_unit_id;
2269
2270 end if;
2271 close csr_category_insert;
2272 l_check_insert:=null;
2273 END IF;
2274 ELSE
2275 /* if all the local units under the legal employer is selected */
2276 for csr_local in csr_Local_unit_Legal(g_legal_employer_id ) loop
2277 l_local_unit_id:=csr_local.local_unit_id;
2278
2279 OPEN csr_CFAR_FROM_LU (l_local_unit_id);
2280 FETCH csr_CFAR_FROM_LU INTO lr_CFAR_FROM_LU;
2281 CLOSE csr_CFAR_FROM_LU;
2282
2283 L_CFAR_NUMBER :=lr_CFAR_FROM_LU.CFAR;
2284 l_local_unit_name:=lr_CFAR_FROM_LU.LU_NAME;
2285
2286 /* check whether record has been inserted for White Collar Hourly Employee */
2287 open csr_local_unit_insert(p_payroll_action_id,'LU',l_local_unit_id);
2288 fetch csr_local_unit_insert into l_check_insert;
2289 close csr_local_unit_insert;
2290 if l_check_insert is null then
2291 pay_action_information_api.create_action_information
2292 (p_action_information_id => l_action_info_id
2293 , p_action_context_id => p_payroll_action_id
2294 , p_action_context_type => 'PA'
2295 , p_object_version_number => l_ovn
2296 , p_effective_date => g_effective_date
2297 , p_source_id => NULL
2298 , p_source_text => NULL
2299 , p_action_information_category => 'EMEA REPORT INFORMATION'
2300 , p_action_information1 => 'PYSEWSSA'
2301 , p_action_information2 => 'LU'
2302 , p_action_information3 => l_local_unit_id
2303 , p_action_information4 => l_local_unit_name --lr_CFAR_FROM_LU.LU_NAME
2304 , p_action_information5 => null --L_CFAR_NUMBER
2305 , p_action_information6 => NULL
2306 , p_action_information7 => NULL
2307 );
2308 end if;
2309 -- FOR csr_person IN csr_person_local_unit(g_business_group_id, g_local_unit_id, g_end_date /*g_effective_date*/) LOOP
2310 --l_person_id:=csr_person.person_id;
2311 --fnd_file.put_line(fnd_file.LOG,'l_person_id'||l_person_id);
2312 pay_balance_pkg.set_context('ASSIGNMENT_ID',l_assignment_id); --133942);
2313 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id); --3621);
2314 FOR csr_assignment IN csr_wage_assignment(g_business_group_id, l_local_unit_id, g_end_date) LOOP
2315 l_assignment_id:=csr_assignment.assignment_id;
2316 l_working_percentage:=csr_assignment.working_percentage;
2317 l_asg_hour_sal:=csr_assignment.hourly_salaried_code;
2318 l_employee_category:=csr_assignment.employee_category;
2319 l_frequency:=csr_assignment.frequency;
2320 l_normal_hours:=csr_assignment.normal_hours;
2321 l_payroll_id:=csr_assignment.payroll_id;
2322 /* Calculating the number of days actually worked */
2323 /* Blue Collar Hourly Employee */
2324 open csr_person_assignment(l_assignment_id,g_start_date,g_end_date);
2325 fetch csr_person_assignment into l_valid_person;
2326 close csr_person_assignment;
2327 IF l_valid_person IS NOT NULL THEN
2328 /* Getting the payroll period and payroll details */
2329 OPEN csr_payroll_period(l_payroll_id,g_start_Date, g_end_date);
2330 FETCH csr_payroll_period INTO l_period,l_period_start,l_period_end;
2331 CLOSE csr_payroll_period;
2332
2333 IF l_employee_category='BC' AND l_asg_hour_sal='H' THEN
2334 l_include_event:='Y';
2335 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2336 ( l_assignment_id, l_work_hours_days, l_include_event,
2337 g_start_Date, g_end_date,l_start_time_char,
2338 l_end_time_char, l_wrk_duration
2339 );
2340 l_bh_worked_calendar_month:=l_wrk_duration;
2341 --l_bh_worked_calendar_month:=l_bh_worked_calendar_month+l_wrk_duration;
2342 l_wrk_duration:=0;
2343 IF l_period_end IS NOT NULL THEN
2344 FOR csr_absence IN csr_assignment_absence(l_assignment_id, l_period_start, l_period_end) LOOP
2345 l_absence_start_date:= csr_absence.start_date;
2346 l_absence_end_date:= csr_absence.end_date;
2347 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2348 ( l_assignment_id, l_work_hours_days, l_include_event,
2349 l_absence_start_date, l_absence_end_date,l_start_time_char,
2350 l_end_time_char, l_wrk_duration
2351 );
2352 l_bh_absence_days:= l_bh_absence_days+l_wrk_duration;
2353 l_wrk_duration:=0;
2354 END LOOP;
2355 /* To get the working days within the period */
2356 l_include_event:='Y';
2357 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
2358 ( l_assignment_id, l_work_hours_days, l_include_event,
2359 l_period_start, l_period_end,l_start_time_char,
2360 l_end_time_char, l_wrk_duration
2361 );
2362 l_bh_worked_period:=l_wrk_duration;
2363 l_bh_worked_period:=l_bh_worked_period-l_bh_absence_days;
2364 IF l_period='Week' THEN
2365 l_bh_worked_payment_period:=l_bh_worked_payment_period+round(l_bh_worked_period*2);
2366 ELSIF l_period='Calendar Month' THEN
2367 l_bh_worked_payment_period:=l_bh_worked_payment_period+round(l_bh_worked_period/2);
2368 END IF;
2369 --l_bh_absence_days:=0;
2370 /* PBT Value */
2371 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'PBT') LOOP
2372 l_type:=csr_wages.Type;
2373 l_element_type_id:=csr_wages.Element_Type_Id;
2374 l_input_value_id:=csr_wages.Input_value_Id;
2375 l_balance_type_id:=csr_wages.Balance_Type_Id;
2376 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2377
2378 /* check whether value has been entered in EIT*/
2379 IF l_type IS NOT NULL THEN
2380 /* If element is selected */
2381 IF l_type='ELEMENT' THEN
2382 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
2383 FETCH csr_element_types INTO l_value;
2384 CLOSE csr_element_types;
2385
2386 ELSE
2387 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2388 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2389 CLOSE csr_get_defined_balance;
2390
2391 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2392 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2393 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
2394 END IF;
2395 IF l_period='Week' THEN
2396 l_value:=l_value*2;
2397 ELSIF l_period='Calendar Month' THEN
2398 l_value:=l_value/2;
2399 END IF;
2400 --l_bh_pbt_value:=l_value;
2401 l_bh_pbt_value:=l_bh_pbt_value+round(nvl(l_value,0));
2402 l_value:=NULL;
2403 l_type:=NULL;
2404
2405 END IF;
2406 END LOOP;
2407 /* PCOW Value */
2408 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'PCOW') LOOP
2409 l_type:=csr_wages.Type;
2410 l_element_type_id:=csr_wages.Element_Type_Id;
2411 l_input_value_id:=csr_wages.Input_value_Id;
2412 l_balance_type_id:=csr_wages.Balance_Type_Id;
2413 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2414
2415 /* check whether value has been entered in EIT*/
2416 IF l_type IS NOT NULL THEN
2417 /* If element is selected */
2418 IF l_type='ELEMENT' THEN
2419 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
2420 FETCH csr_element_types INTO l_value;
2421 CLOSE csr_element_types;
2422
2423 ELSE
2424 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2425 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2426 CLOSE csr_get_defined_balance;
2427
2428 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2429 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2430 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
2431 END IF;
2432 IF l_period='Week' THEN
2433 l_value:=l_value*2;
2434 ELSIF l_period='Calendar Month' THEN
2435 l_value:=l_value/2;
2436 END IF;
2437 --l_bh_pcow_value:=l_value;
2438 l_bh_pcow_value:=l_bh_pcow_value+round(nvl(l_value,0));
2439 l_value:=NULL;
2440 l_type:=NULL;
2441 END IF;
2442 END LOOP;
2443 /* NHA Value */
2444 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'NHA') LOOP
2445 l_type:=csr_wages.Type;
2446 l_element_type_id:=csr_wages.Element_Type_Id;
2447 l_input_value_id:=csr_wages.Input_value_Id;
2448 l_balance_type_id:=csr_wages.Balance_Type_Id;
2449 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2450
2451 /* check whether value has been entered in EIT*/
2452 IF l_type IS NOT NULL THEN
2453 /* If element is selected */
2454 IF l_type='ELEMENT' THEN
2455 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
2456 FETCH csr_element_types INTO l_value;
2457 CLOSE csr_element_types;
2458
2459 ELSE
2460 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2461 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2462 CLOSE csr_get_defined_balance;
2463
2464 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2465 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2466 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
2467 END IF;
2468 IF l_period='Week' THEN
2469 l_value:=l_value*2;
2470 ELSIF l_period='Calendar Month' THEN
2471 l_value:=l_value/2;
2472 END IF;
2473 --l_bh_nha_value:=l_value;
2474 l_bh_nha_value:=l_bh_nha_value+round(nvl(l_value,0));
2475 l_value:=NULL;
2476 l_type:=NULL;
2477 END IF;
2478 END LOOP;
2479 /* NHO Value */
2480 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'NHO') LOOP
2481 l_type:=csr_wages.Type;
2482 l_element_type_id:=csr_wages.Element_Type_Id;
2483 l_input_value_id:=csr_wages.Input_value_Id;
2484 l_balance_type_id:=csr_wages.Balance_Type_Id;
2485 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2486
2487 /* check whether value has been entered in EIT*/
2488 IF l_type IS NOT NULL THEN
2489 /* If element is selected */
2490 IF l_type='ELEMENT' THEN
2491 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
2492 FETCH csr_element_types INTO l_value;
2493 CLOSE csr_element_types;
2494
2495 ELSE
2496 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2497 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2498 CLOSE csr_get_defined_balance;
2499
2500 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2501 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2502 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
2503 END IF;
2504 IF l_period='Week' THEN
2505 l_value:=l_value*2;
2506 ELSIF l_period='Calendar Month' THEN
2507 l_value:=l_value/2;
2508 END IF;
2509 --l_bh_nho_value:=l_value;
2510 l_bh_nho_value:=l_bh_nho_value+round(nvl(l_value,0));
2511 l_value:=NULL;
2512 l_type:=NULL;
2513 END IF;
2514 END LOOP;
2515 /*Retroactive payment _ASG_LU_PTD */
2516 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
2517 OPEN csr_Get_Defined_Balance_Id( 'RETROSPECTIVE_PAYMENTS_ASG_LU_PTD');
2518 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2519 CLOSE csr_Get_Defined_Balance_Id;
2520 l_value:=nvl(pay_balance_pkg.get_value
2521 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2522 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2523 P_VIRTUAL_DATE =>l_period_end-- '31-jan-2000'
2524 ),0);
2525 --l_bh_retroactive_pay:=l_value;
2526 IF l_period='Week' THEN
2527 l_value:=l_value*2;
2528 ELSIF l_period='Calendar Month' THEN
2529 l_value:=l_value/2;
2530 END IF;
2531 l_bh_retroactive_pay:=l_bh_retroactive_pay+round(nvl(l_value,0));
2532 l_value:=NULL;
2533 l_type:=NULL;
2534 /* PPO Value */
2535 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BH', 'PPO') LOOP
2536 l_type:=csr_wages.Type;
2537 l_element_type_id:=csr_wages.Element_Type_Id;
2538 l_input_value_id:=csr_wages.Input_value_Id;
2539 l_balance_type_id:=csr_wages.Balance_Type_Id;
2540 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2541
2542 /* check whether value has been entered in EIT*/
2543 IF l_type IS NOT NULL THEN
2544 /* If element is selected */
2545 IF l_type='ELEMENT' THEN
2546 OPEN csr_element_types(l_assignment_id, l_period_start, l_period_end, l_element_type_id, l_input_value_id);
2547 FETCH csr_element_types INTO l_value;
2548 CLOSE csr_element_types;
2549
2550 ELSE
2551 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2552 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2553 CLOSE csr_get_defined_balance;
2554
2555 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2556 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2557 P_VIRTUAL_DATE=>l_period_end/*TO_DATE('31-jan-2001')*/),0);
2558 END IF;
2559 IF l_period='Week' THEN
2560 l_value:=l_value*2;
2561 ELSIF l_period='Calendar Month' THEN
2562 l_value:=l_value/2;
2563 END IF;
2564 --l_bh_ppo_value:=l_value;
2565 l_bh_ppo_value:=l_bh_ppo_value+round(nvl(l_value,0));
2566 l_value:=NULL;
2567 l_type:=NULL;
2568 END IF;
2569 END LOOP;
2570
2571 /*Total Sick Pay _ASG_LU_PTD */
2572 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
2573 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SICK_PAY_ASG_LU_PTD');
2574 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2575 CLOSE csr_Get_Defined_Balance_Id;
2576 l_value:=nvl(pay_balance_pkg.get_value
2577 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2578 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2579 P_VIRTUAL_DATE =>l_period_end-- '31-jan-2000'
2580 ),0);
2581 --l_bh_sick_pay:=l_value;
2582 IF l_period='Week' THEN
2583 l_value:=l_value*2;
2584 ELSIF l_period='Calendar Month' THEN
2585 l_value:=l_value/2;
2586 END IF;
2587 l_bh_sick_pay:=l_bh_sick_pay+round(nvl(l_value,0));
2588 l_value:=NULL;
2589 l_type:=NULL;
2590 END IF;
2591 /*Total count of the employees*/
2592 l_bh_total_employees:=l_bh_total_employees+1;
2593
2594 /* Blue Collar Salaried Employee */
2595 ELSIF l_employee_category='BC' AND l_asg_hour_sal='S' THEN
2596 /*Gross Pay _ASG_LU_MONTH */
2597 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',l_local_unit_id);
2598 OPEN csr_Get_Defined_Balance_Id( 'GROSS_PAY_ASG_LU_MONTH');
2599 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2600 CLOSE csr_Get_Defined_Balance_Id;
2601 l_value:=nvl(pay_balance_pkg.get_value
2602 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2603 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2604 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
2605 ),0);
2606 --l_bs_gross_pay:=l_value;
2607 l_bs_gross_pay:=l_bs_gross_pay+round(nvl(l_value,0));
2608 l_value:=NULL;
2609 l_type:=NULL;
2610 /*Total Working hours agreement */
2611 IF l_frequency='W' THEN
2612 l_normal_hours:=l_normal_hours*4.3;
2613 END IF;
2614 l_bs_working_agreement:=l_bs_working_agreement+round(l_normal_hours);
2615 /* TCDP Value */
2616 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'TCDP') LOOP
2617 l_type:=csr_wages.Type;
2618 l_element_type_id:=csr_wages.Element_Type_Id;
2619 l_input_value_id:=csr_wages.Input_value_Id;
2620 l_balance_type_id:=csr_wages.Balance_Type_Id;
2621 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2622
2623 /* check whether value has been entered in EIT*/
2624 IF l_type IS NOT NULL THEN
2625 /* If element is selected */
2626 IF l_type='ELEMENT' THEN
2627 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2628 FETCH csr_element_types INTO l_value;
2629 CLOSE csr_element_types;
2630
2631 ELSE
2632 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2633 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2634 CLOSE csr_get_defined_balance;
2635
2636 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2637 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2638 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2639 END IF;
2640 --l_bs_tcdp_value:=l_value;
2641 l_bs_tcdp_value:=l_bs_tcdp_value+round(nvl(l_value,0));
2642 l_value:=NULL;
2643 l_type:=NULL;
2644 END IF;
2645 END LOOP;
2646 /* TCOW Value */
2647 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'TCOW') LOOP
2648 l_type:=csr_wages.Type;
2649 l_element_type_id:=csr_wages.Element_Type_Id;
2650 l_input_value_id:=csr_wages.Input_value_Id;
2651 l_balance_type_id:=csr_wages.Balance_Type_Id;
2652 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2653
2654 /* check whether value has been entered in EIT*/
2655 IF l_type IS NOT NULL THEN
2656 /* If element is selected */
2657 IF l_type='ELEMENT' THEN
2658 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2659 FETCH csr_element_types INTO l_value;
2660 CLOSE csr_element_types;
2661
2662 ELSE
2663 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2664 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2665 CLOSE csr_get_defined_balance;
2666
2667 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2668 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2669 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2670 END IF;
2671 --l_bs_tcow_value:=l_value;
2672 l_bs_tcow_value:=l_bs_tcow_value+round(nvl(l_value,0));
2673 l_value:=NULL;
2674 l_type:=NULL;
2675 END IF;
2676 END LOOP;
2677 /* NHA Value */
2678 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'NHA') LOOP
2679 l_type:=csr_wages.Type;
2680 l_element_type_id:=csr_wages.Element_Type_Id;
2681 l_input_value_id:=csr_wages.Input_value_Id;
2682 l_balance_type_id:=csr_wages.Balance_Type_Id;
2683 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2684 /* check whether value has been entered in EIT*/
2685 IF l_type IS NOT NULL THEN
2686 /* If element is selected */
2687 IF l_type='ELEMENT' THEN
2688 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2689 FETCH csr_element_types INTO l_value;
2690 CLOSE csr_element_types;
2691 ELSE
2692 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2693 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2694 CLOSE csr_get_defined_balance;
2695 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2696 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2697 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2698 END IF;
2699 --l_bs_nha_value:=l_value;
2700 l_bs_nha_value:=l_bs_nha_value+round(nvl(l_value,0));
2701 l_value:=NULL;
2702 l_type:=NULL;
2703 END IF;
2704 END LOOP;
2705 /* NHO Value */
2706 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'NHO') LOOP
2707 l_type:=csr_wages.Type;
2708 l_element_type_id:=csr_wages.Element_Type_Id;
2709 l_input_value_id:=csr_wages.Input_value_Id;
2710 l_balance_type_id:=csr_wages.Balance_Type_Id;
2711 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2712 /* check whether value has been entered in EIT*/
2713 IF l_type IS NOT NULL THEN
2714 /* If element is selected */
2715 IF l_type='ELEMENT' THEN
2716 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2717 FETCH csr_element_types INTO l_value;
2718 CLOSE csr_element_types;
2719 ELSE
2720 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2721 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2722 CLOSE csr_get_defined_balance;
2723
2724 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2725 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2726 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2727 --l_bs_nho_value:=l_value;
2728 l_bs_nho_value:=l_bs_nho_value+round(nvl(l_value,0));
2729 l_value:=NULL;
2730 l_type:=NULL;
2731 lr_Get_Defined_Balance_Id:=NULL;
2732 END IF;
2733 END IF;
2734 END LOOP;
2735 /*Retroactive payment _ASG_LU_MONTH */
2736 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',l_local_unit_id);
2737 OPEN csr_Get_Defined_Balance_Id( 'RETROSPECTIVE_PAYMENTS_ASG_LU_MONTH');
2738 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2739 CLOSE csr_Get_Defined_Balance_Id;
2740 l_value:=nvl(pay_balance_pkg.get_value
2741 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2742 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2743 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
2744 ),0);
2745 --l_bs_retroactive_pay:=l_value;
2746 l_bs_retroactive_pay:=l_bs_retroactive_pay+round(nvl(l_value,0));
2747 l_value:=NULL;
2748 l_type:=NULL;
2749 /* PPO Value */
2750 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'BS', 'PPO') LOOP
2751 l_type:=csr_wages.Type;
2752 l_element_type_id:=csr_wages.Element_Type_Id;
2753 l_input_value_id:=csr_wages.Input_value_Id;
2754 l_balance_type_id:=csr_wages.Balance_Type_Id;
2755 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2756
2757 /* check whether value has been entered in EIT*/
2758 IF l_type IS NOT NULL THEN
2759 /* If element is selected */
2760 IF l_type='ELEMENT' THEN
2761 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2762 FETCH csr_element_types INTO l_value;
2763 CLOSE csr_element_types;
2764
2765 ELSE
2766 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2767 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2768 CLOSE csr_get_defined_balance;
2769
2770 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2771 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2772 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2773 END IF;
2774 --l_bs_ppo_value:=l_value;
2775 l_bs_ppo_value:=l_bs_ppo_value+round(nvl(l_value,0));
2776 l_value:=NULL;
2777 l_type:=NULL;
2778 END IF;
2779 END LOOP;
2780 /*Total Sick Pay _ASG_LU_MONTH */
2781 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',l_local_unit_id);
2782 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SICK_PAY_ASG_LU_MONTH');
2783 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2784 CLOSE csr_Get_Defined_Balance_Id;
2785 l_value:=nvl(pay_balance_pkg.get_value
2786 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2787 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2788 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
2789 ),0);
2790 --l_bs_sick_pay:=l_value;
2791 l_bs_sick_pay:=l_bs_sick_pay+round(nvl(l_value,0));
2792 l_value:=NULL;
2793 l_type:=NULL;
2794 /*Total count of the employees*/
2795 l_bs_total_employees:=l_bs_total_employees+1;
2796
2797 /*White Collar Salaried Employee */
2798 ELSIF l_employee_category='WC' AND l_asg_hour_sal='S' THEN
2799 /* Number of Full Time Employees */
2800 l_ws_full_time_employee:=l_ws_full_time_employee+round(nvl(l_working_percentage,100)/100,2);
2801 --IF l_working_percentage=100 THEN
2802 --l_ws_full_time_employee:=l_ws_full_time_employee+round(nvl(l_working_percentage,100)* nvl(l_normal_hours,0)/100,2);
2803 --END IF;
2804
2805 /*Gross Pay _ASG_LU_MONTH */
2806 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',l_local_unit_id);
2807 OPEN csr_Get_Defined_Balance_Id( 'GROSS_PAY_ASG_LU_MONTH');
2808 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2809 CLOSE csr_Get_Defined_Balance_Id;
2810 l_value:=nvl(pay_balance_pkg.get_value
2811 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2812 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2813 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
2814 ),0);
2815 --l_ws_gross_pay:=l_value;
2816 l_ws_gross_pay:=l_ws_gross_pay+round(nvl(l_value,0));
2817 l_value:=NULL;
2818 l_type:=NULL;
2819 /*Total Working hours agreement */
2820 IF l_frequency='W' THEN
2821 l_normal_hours:=l_normal_hours*4.3;
2822 END IF;
2823 l_ws_working_agreement:=l_ws_working_agreement+round(l_normal_hours);
2824 /*IF l_frequency='M' THEN
2825 l_ws_working_agreement:=l_ws_working_agreement+round(l_normal_hours);
2826 END IF; */
2827
2828 /* TCDP Value */
2829 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'TCDP') LOOP
2830 l_type:=csr_wages.Type;
2831 l_element_type_id:=csr_wages.Element_Type_Id;
2832 l_input_value_id:=csr_wages.Input_value_Id;
2833 l_balance_type_id:=csr_wages.Balance_Type_Id;
2834 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2835
2836 /* check whether value has been entered in EIT*/
2837 IF l_type IS NOT NULL THEN
2838 /* If element is selected */
2839 IF l_type='ELEMENT' THEN
2840 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2841 FETCH csr_element_types INTO l_value;
2842 CLOSE csr_element_types;
2843
2844 ELSE
2845 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2846 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2847 CLOSE csr_get_defined_balance;
2848
2849 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2850 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2851 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2852 END IF;
2853 --l_ws_tcdp_value:=l_value;
2854 l_ws_tcdp_value:=l_ws_tcdp_value+round(nvl(l_value,0));
2855 l_value:=NULL;
2856 l_type:=NULL;
2857 END IF;
2858 END LOOP;
2859 /* TCOW Value */
2860 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'TCOW') LOOP
2861 l_type:=csr_wages.Type;
2862 l_element_type_id:=csr_wages.Element_Type_Id;
2863 l_input_value_id:=csr_wages.Input_value_Id;
2864 l_balance_type_id:=csr_wages.Balance_Type_Id;
2865 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2866
2867 /* check whether value has been entered in EIT*/
2868 IF l_type IS NOT NULL THEN
2869 /* If element is selected */
2870 IF l_type='ELEMENT' THEN
2871 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2872 FETCH csr_element_types INTO l_value;
2873 CLOSE csr_element_types;
2874
2875 ELSE
2876 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2877 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2878 CLOSE csr_get_defined_balance;
2879
2880 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2881 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2882 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2883 END IF;
2884 --l_ws_tcow_value:=l_value;
2885 l_ws_tcow_value:=l_ws_tcow_value+round(nvl(l_value,0));
2886 l_value:=NULL;
2887 l_type:=NULL;
2888 END IF;
2889 END LOOP;
2890 /* NHA Value */
2891 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'NHA') LOOP
2892 l_type:=csr_wages.Type;
2893 l_element_type_id:=csr_wages.Element_Type_Id;
2894 l_input_value_id:=csr_wages.Input_value_Id;
2895 l_balance_type_id:=csr_wages.Balance_Type_Id;
2896 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2897
2898 /* check whether value has been entered in EIT*/
2899 IF l_type IS NOT NULL THEN
2900 /* If element is selected */
2901 IF l_type='ELEMENT' THEN
2902 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2903 FETCH csr_element_types INTO l_value;
2904 CLOSE csr_element_types;
2905
2906 ELSE
2907 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2908 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2909 CLOSE csr_get_defined_balance;
2910
2911 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2912 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2913 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2914 END IF;
2915 --l_ws_nha_value:=l_value;
2916 l_ws_nha_value:=l_ws_nha_value+round(nvl(l_value,0));
2917 l_value:=NULL;
2918 l_type:=NULL;
2919 END IF;
2920 END LOOP;
2921 /* NHO Value */
2922 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'NHO') LOOP
2923 l_type:=csr_wages.Type;
2924 l_element_type_id:=csr_wages.Element_Type_Id;
2925 l_input_value_id:=csr_wages.Input_value_Id;
2926 l_balance_type_id:=csr_wages.Balance_Type_Id;
2927 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2928
2929 /* check whether value has been entered in EIT*/
2930 IF l_type IS NOT NULL THEN
2931 /* If element is selected */
2932 IF l_type='ELEMENT' THEN
2933 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2934 FETCH csr_element_types INTO l_value;
2935 CLOSE csr_element_types;
2936
2937 ELSE
2938 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2939 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2940 CLOSE csr_get_defined_balance;
2941
2942 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2943 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2944 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2945 END IF;
2946 --l_ws_nho_value:=l_value;
2947 l_ws_nho_value:=l_ws_nho_value+round(nvl(l_value,0));
2948 l_value:=NULL;
2949 l_type:=NULL;
2950 END IF;
2951 END LOOP;
2952 /*Retroactive payment _ASG_LU_MONTH */
2953 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',l_local_unit_id);
2954 OPEN csr_Get_Defined_Balance_Id( 'RETROSPECTIVE_PAYMENTS_ASG_LU_MONTH');
2955 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2956 CLOSE csr_Get_Defined_Balance_Id;
2957 l_value:=nvl(pay_balance_pkg.get_value
2958 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2959 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
2960 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
2961 ),0);
2962 --l_ws_retroactive_pay:=l_value;
2963 l_ws_retroactive_pay:=l_ws_retroactive_pay+round(nvl(l_value,0));
2964 l_value:=NULL;
2965 l_type:=NULL;
2966
2967 /* PPO Value */
2968 FOR csr_wages IN csr_wages_details (g_legal_employer_id, 'WS', 'PPO') LOOP
2969 l_type:=csr_wages.Type;
2970 l_element_type_id:=csr_wages.Element_Type_Id;
2971 l_input_value_id:=csr_wages.Input_value_Id;
2972 l_balance_type_id:=csr_wages.Balance_Type_Id;
2973 l_balance_dimension_id:=csr_wages.Balance_Dimension_Id;
2974
2975 /* check whether value has been entered in EIT*/
2976 IF l_type IS NOT NULL THEN
2977 /* If element is selected */
2978 IF l_type='ELEMENT' THEN
2979 OPEN csr_element_types(l_assignment_id, g_start_date, g_end_date, l_element_type_id, l_input_value_id);
2980 FETCH csr_element_types INTO l_value;
2981 CLOSE csr_element_types;
2982
2983 ELSE
2984 OPEN csr_get_defined_balance(l_balance_type_id, l_balance_dimension_id);
2985 FETCH csr_get_defined_balance INTO lr_Get_Defined_Balance_Id;
2986 CLOSE csr_get_defined_balance;
2987
2988 l_value:=nvl(pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
2989 P_ASSIGNMENT_ID =>l_assignment_id, --21348,
2990 P_VIRTUAL_DATE=>g_end_date/*TO_DATE('31-jan-2001')*/),0);
2991 --l_ws_ppo_value:=l_value;
2992 l_ws_ppo_value:=l_ws_ppo_value+round(nvl(l_value,0));
2993 l_value:=NULL;
2994 l_type:=NULL;
2995 END IF;
2996 END IF;
2997 END LOOP;
2998 /*Total Sick Pay _ASG_LU_MONTH */
2999 pay_balance_pkg.set_context ('LOCAL_UNIT_ID',g_local_unit_id);
3000 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SICK_PAY_ASG_LU_MONTH');
3001 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
3002 CLOSE csr_Get_Defined_Balance_Id;
3003 l_value:=nvl(pay_balance_pkg.get_value
3004 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
3005 P_ASSIGNMENT_ID =>l_assignment_id, --32488,
3006 P_VIRTUAL_DATE =>g_end_date-- '31-jan-2000'
3007 ),0);
3008 --l_ws_sick_pay:=l_value;
3009 l_ws_sick_pay:=l_ws_sick_pay+round(nvl(l_value,0));
3010 l_value:=NULL;
3011 l_type:=NULL;
3012
3013 /*Total count of the employees*/
3014 l_ws_total_employees:=l_ws_total_employees+1;
3015 END IF;
3016 l_type:=NULL;
3017 l_element_type_id:=NULL;
3018 l_input_value_id:=NULL;
3019 l_balance_type_id:=NULL;
3020 l_balance_dimension_id:=NULL;
3021 l_period:=NULL;
3022 l_period_start:=NULL;
3023 l_period_end:=NULL;
3024 END IF;
3025 l_valid_person:=NULL;
3026 END LOOP;
3027
3028 /* check whether there are Blue collar Hourly Employees on current Local unit*/
3029 IF l_bh_total_employees<>0 THEN
3030 /* check whether record has been inserted for Blue Collar Hourly Employee */
3031 open csr_category_insert(p_payroll_action_id,'BH',l_local_unit_id);
3032 fetch csr_category_insert into l_check_insert;
3033 -- close csr_category_insert;
3034 --if l_check_insert is null then
3035 IF csr_category_insert%NOTFOUND THEN
3036 /*Insert the record*/
3037 pay_action_information_api.create_action_information
3038 (p_action_information_id => l_action_info_id
3039 , p_action_context_id => p_payroll_action_id
3040 , p_action_context_type => 'PA'
3041 , p_object_version_number => l_ovn
3042 , p_effective_date => g_effective_date
3043 , p_source_id => NULL
3044 , p_source_text => NULL
3045 , p_action_information_category => 'EMEA REPORT INFORMATION'
3046 , p_action_information1 => 'PYSEWSSA'
3047 , p_action_information2 => 'BH'
3048 , p_action_information3 => l_local_unit_id
3049 , p_action_information4 => l_bh_worked_calendar_month
3050 , p_action_information5 => l_bh_worked_payment_period
3051 , p_action_information6 => NULL
3052 , p_action_information7 => NULL
3053 , p_action_information8 => NULL
3054 , p_action_information9 => NULL
3055 , p_action_information10 => NULL
3056 , p_action_information11 => NULL
3057 , p_action_information12 => l_bh_pbt_value
3058 , p_action_information13 => l_bh_pcow_value
3059 , p_action_information14 => l_bh_nha_value
3060 , p_action_information15 => l_bh_nho_value
3061 , p_action_information16 => l_bh_retroactive_pay
3062 , p_action_information17 => l_bh_ppo_value
3063 , p_action_information18 => l_bh_sick_pay
3064 , p_action_information19 => l_bh_total_employees
3065 );
3066 else
3067 /*update the record*/
3068 update pay_action_information set
3069 --action_information4=action_information4+l_bh_worked_calendar_month,
3070 action_information5=action_information5+l_bh_worked_payment_period,
3071 action_information12=action_information12+l_bh_pbt_value,
3072 action_information13=action_information13+l_bh_pcow_value,
3073 action_information14=action_information14+l_bh_nha_value,
3074 action_information15=action_information15+l_bh_nho_value,
3075 action_information16=action_information16+l_bh_retroactive_pay,
3076 action_information17=action_information17+l_bh_ppo_value,
3077 action_information18=action_information18+l_bh_sick_pay,
3078 action_information19=action_information19+l_bh_total_employees
3079 where action_context_id=p_payroll_action_id
3080 and action_information2='BH'
3081 AND action_information3=l_local_unit_id;
3082
3083 end if;
3084 close csr_category_insert;
3085 l_check_insert:=null;
3086 END IF;
3087 /* check whether there are Blue collar Salaried Employees on current Local unit*/
3088 IF l_bs_total_employees<>0 THEN
3089 /* check whether record has been inserted for Blue Collar Salaried Employee */
3090 open csr_category_insert(p_payroll_action_id,'BS',l_local_unit_id);
3091 fetch csr_category_insert into l_check_insert;
3092 --close csr_category_insert;
3093 --if l_check_insert is null then
3094 IF csr_category_insert%NOTFOUND THEN
3095 /*Insert the record*/
3096 pay_action_information_api.create_action_information
3097 (p_action_information_id => l_action_info_id
3098 , p_action_context_id => p_payroll_action_id
3099 , p_action_context_type => 'PA'
3100 , p_object_version_number => l_ovn
3101 , p_effective_date => g_effective_date
3102 , p_source_id => NULL
3103 , p_source_text => NULL
3104 , p_action_information_category => 'EMEA REPORT INFORMATION'
3105 , p_action_information1 => 'PYSEWSSA'
3106 , p_action_information2 => 'BS'
3107 , p_action_information3 => l_local_unit_id
3108 , p_action_information4 => null
3109 , p_action_information5 => null
3110 , p_action_information6 => null
3111 , p_action_information7 => l_bs_gross_pay
3112 , p_action_information8 => l_bs_working_agreement
3113 , p_action_information9 => l_bs_tcdp_value
3114 , p_action_information10 => l_bs_tcow_value
3115 , p_action_information11 => NULL
3116 , p_action_information12 => NULL
3117 , p_action_information13 => NULL
3118 , p_action_information14 => l_bs_nha_value
3119 , p_action_information15 => l_bs_nho_value
3120 , p_action_information16 => l_bs_retroactive_pay
3121 , p_action_information17 => l_bs_ppo_value
3122 , p_action_information18 => l_bs_sick_pay
3123 , p_action_information19 => l_bs_total_employees
3124 );
3125 else
3126 /*update the record*/
3127 update pay_action_information set
3128 action_information7=action_information7+l_bs_gross_pay,
3129 action_information8=action_information8+l_bs_working_agreement,
3130 action_information9=action_information9+l_bs_tcdp_value,
3131 action_information10=action_information10+l_bs_tcow_value,
3132 action_information14=action_information14+l_bs_nha_value,
3133 action_information15=action_information15+l_bs_nho_value,
3134 action_information16=action_information16+l_bs_retroactive_pay,
3135 action_information17=action_information17+l_bs_ppo_value,
3136 action_information18=action_information18+l_bs_sick_pay,
3137 action_information19=action_information19+l_bs_total_employees
3138 where action_context_id=p_payroll_action_id
3139 and action_information2='BS'
3140 AND action_information3=l_local_unit_id;
3141
3142 end if;
3143 close csr_category_insert;
3144 l_check_insert:=null;
3145 END IF;
3146 /* check whether there are White collar Salaried Employees on current Local unit*/
3147 IF l_ws_total_employees<>0 THEN
3148 /* check whether record has been inserted for White Collar Salaried Employee */
3149 open csr_category_insert(p_payroll_action_id,'WS',l_local_unit_id);
3150 fetch csr_category_insert into l_check_insert;
3151 --close csr_category_insert;
3152 --if l_check_insert is null then
3153 IF csr_category_insert%NOTFOUND THEN
3154 /*Insert the record*/
3155 pay_action_information_api.create_action_information
3156 (p_action_information_id => l_action_info_id
3157 , p_action_context_id => p_payroll_action_id
3158 , p_action_context_type => 'PA'
3159 , p_object_version_number => l_ovn
3160 , p_effective_date => g_effective_date
3161 , p_source_id => NULL
3162 , p_source_text => NULL
3163 , p_action_information_category => 'EMEA REPORT INFORMATION'
3164 , p_action_information1 => 'PYSEWSSA'
3165 , p_action_information2 => 'WS'
3166 , p_action_information3 => l_local_unit_id
3167 , p_action_information4 => null
3168 , p_action_information5 => null
3169 , p_action_information6 => l_ws_full_time_employee
3170 , p_action_information7 => l_ws_gross_pay
3171 , p_action_information8 => l_ws_working_agreement
3172 , p_action_information9 => l_ws_tcdp_value
3173 , p_action_information10 => l_ws_tcow_value
3174 , p_action_information11 => NULL
3175 , p_action_information12 => NULL
3176 , p_action_information13 => NULL
3177 , p_action_information14 => l_ws_nha_value
3178 , p_action_information15 => l_ws_nho_value
3179 , p_action_information16 => l_ws_retroactive_pay
3180 , p_action_information17 => l_ws_ppo_value
3181 , p_action_information18 => l_ws_sick_pay
3182 , p_action_information19 => l_ws_total_employees
3183 );
3184 else
3185 /*update the record*/
3186 update pay_action_information set
3187 action_information6=action_information6+l_ws_full_time_employee,
3188 action_information7=action_information7+l_ws_gross_pay,
3189 action_information8=action_information8+l_ws_working_agreement,
3190 action_information9=action_information9+l_ws_tcdp_value,
3191 action_information10=action_information10+l_ws_tcow_value,
3192 action_information14=action_information14+l_ws_nha_value,
3193 action_information15=action_information15+l_ws_nho_value,
3194 action_information16=action_information16+l_ws_retroactive_pay,
3195 action_information17=action_information17+l_ws_ppo_value,
3196 action_information18=action_information18+l_ws_sick_pay,
3197 action_information19=action_information19+l_ws_total_employees
3198 where action_context_id=p_payroll_action_id
3199 and action_information2='WS'
3200 AND action_information3=l_local_unit_id;
3201
3202 end if;
3203 close csr_category_insert;
3204 l_check_insert:=null;
3205 END IF;
3206 /* Initializing all the variables for next local unit */
3207 l_bh_worked_calendar_month:=0;
3208 l_bh_worked_payment_period:=0;
3209 l_bh_pbt_value:=0;
3210 l_bh_pcow_value:=0;
3211 l_bh_nha_value:=0;
3212 l_bh_nho_value:=0;
3213 l_bh_retroactive_pay:=0;
3214 l_bh_ppo_value:=0;
3215 l_bh_sick_pay:=0;
3216 l_bh_total_employees:=0;
3217 l_bs_gross_pay:=0;
3218 l_bs_working_agreement:=0;
3219 l_bs_tcdp_value:=0;
3220 l_bs_tcow_value:=0;
3221 l_bs_nha_value:=0;
3222 l_bs_nho_value:=0;
3223 l_bs_retroactive_pay:=0;
3224 l_bs_ppo_value:=0;
3225 l_bs_sick_pay:=0;
3226 l_bs_total_employees:=0;
3227 l_ws_full_time_employee:=0;
3228 l_ws_gross_pay:=0;
3229 l_ws_working_agreement:=0;
3230 l_ws_tcdp_value:=0;
3231 l_ws_tcow_value:=0;
3232 l_ws_nha_value:=0;
3233 l_ws_nho_value:=0;
3234 l_ws_retroactive_pay:=0;
3235 l_ws_ppo_value:=0;
3236 l_ws_sick_pay:=0;
3237 l_ws_total_employees:=0;
3238 l_wh_full_time_employee:=0;
3239 l_wh_gross_pay:=0;
3240 l_wh_working_agreement:=0;
3241 l_wh_tcdp_value:=0;
3242 l_wh_retroactive_pay:=0;
3243 l_wh_ppo_value:=0;
3244 l_wh_sick_pay:=0;
3245 l_wh_total_employees:=0;
3246
3247 END LOOP;
3248 END IF;
3249
3250 /* BEGIN
3251 IF g_debug
3252 THEN
3253 hr_utility.set_location
3254 (' Entering Procedure ASSIGNMENT_ACTION_CODE'
3255 , 60
3256 );
3257 END IF;
3258
3259 fnd_file.put_line(fnd_file.LOG,'I am assignment here');
3260
3261 IF g_debug
3262 THEN
3263 hr_utility.set_location
3264 (' Leaving Procedure ASSIGNMENT_ACTION_CODE'
3265 , 70
3266 );
3267 END IF;*/
3268 EXCEPTION
3269 WHEN OTHERS
3270 THEN
3271 IF g_debug
3272 THEN
3273 hr_utility.set_location ('error raised assignment_action_code '
3274 , 5
3275 );
3276 END IF;
3277
3278 RAISE;
3279 END assignment_action_code;
3280
3281 /*fffffffffffffffffffffffffff*/
3282
3283 /* INITIALIZATION CODE */
3284 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
3285 IS
3286 l_action_info_id NUMBER;
3287 l_ovn NUMBER;
3288 l_count NUMBER := 0;
3289 l_business_group_id NUMBER;
3290 l_start_date VARCHAR2 (20);
3291 l_end_date VARCHAR2 (20);
3292 l_effective_date DATE;
3293 l_payroll_id NUMBER;
3294 l_consolidation_set NUMBER;
3295 l_prev_prepay NUMBER := 0;
3296 BEGIN
3297 IF g_debug
3298 THEN
3299 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE'
3300 , 80
3301 );
3302 END IF;
3303
3304
3305 g_payroll_action_id := p_payroll_action_id;
3306 g_business_group_id := NULL;
3307 g_effective_date := NULL;
3308 g_LE_request := NULL;
3309 g_LU_request :=null;
3310 g_legal_employer_id := NULL;
3311 g_local_unit_id := NULL;
3312 g_account_date :=null;
3313 g_posting_date :=null;
3314
3315
3316 IF g_debug
3317 THEN
3318 hr_utility.set_location (' Leaving Procedure INITIALIZATION_CODE'
3319 , 90
3320 );
3321 END IF;
3322 EXCEPTION
3323 WHEN OTHERS
3324 THEN
3325 g_err_num := SQLCODE;
3326
3327 IF g_debug
3328 THEN
3329 hr_utility.set_location ( 'ORA_ERR: '
3330 || g_err_num
3331 || 'In INITIALIZATION_CODE'
3332 , 180
3333 );
3334 END IF;
3335 END initialization_code;
3336
3337 /* GET DEFINED BALANCE ID */
3338 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
3339 RETURN NUMBER
3340 IS
3341 /* Cursor to retrieve Defined Balance Id */
3342 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
3343 IS
3344 SELECT u.creator_id
3345 FROM ff_user_entities u, ff_database_items d
3346 WHERE d.user_name = p_user_name
3347 AND u.user_entity_id = d.user_entity_id
3348 AND (u.legislation_code = 'SE')
3349 AND (u.business_group_id IS NULL)
3350 AND u.creator_type = 'B';
3351
3352 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
3353 BEGIN
3354 IF g_debug
3355 THEN
3356 hr_utility.set_location
3357 (' Entering Function GET_DEFINED_BALANCE_ID'
3358 , 240
3359 );
3360 END IF;
3361
3362 OPEN csr_def_bal_id (p_user_name);
3363
3364 FETCH csr_def_bal_id
3365 INTO l_defined_balance_id;
3366
3367 CLOSE csr_def_bal_id;
3368
3369 RETURN l_defined_balance_id;
3370
3371 IF g_debug
3372 THEN
3373 hr_utility.set_location (' Leaving Function GET_DEFINED_BALANCE_ID'
3374 , 250
3375 );
3376 END IF;
3377 END get_defined_balance_id;
3378
3379 FUNCTION get_defined_balance_value (
3380 p_user_name IN VARCHAR2
3381 , p_in_assignment_id IN NUMBER
3382 , p_in_virtual_date IN DATE
3383 )
3384 RETURN NUMBER
3385 IS
3386 /* Cursor to retrieve Defined Balance Id */
3387 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
3388 IS
3389 SELECT u.creator_id
3390 FROM ff_user_entities u, ff_database_items d
3391 WHERE d.user_name = p_user_name
3392 AND u.user_entity_id = d.user_entity_id
3393 AND (u.legislation_code = 'SE')
3394 AND (u.business_group_id IS NULL)
3395 AND u.creator_type = 'B';
3396
3397 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
3398 l_return_balance_value NUMBER;
3399 BEGIN
3400 IF g_debug
3401 THEN
3402 hr_utility.set_location
3403 (' Entering Function GET_DEFINED_BALANCE_VALUE'
3404 , 240
3405 );
3406 END IF;
3407
3408 OPEN csr_def_bal_id (p_user_name);
3409
3410 FETCH csr_def_bal_id
3411 INTO l_defined_balance_id;
3412
3413 CLOSE csr_def_bal_id;
3414
3415 l_return_balance_value :=
3416 TO_CHAR
3417 (pay_balance_pkg.get_value
3418 (p_defined_balance_id => l_defined_balance_id
3419 , p_assignment_id => p_in_assignment_id
3420 , p_virtual_date => p_in_virtual_date
3421 )
3422 , '999999999D99'
3423 );
3424 RETURN l_return_balance_value;
3425
3426 IF g_debug
3427 THEN
3428 hr_utility.set_location
3429 (' Leaving Function GET_DEFINED_BALANCE_VALUE'
3430 , 250
3431 );
3432 END IF;
3433 END get_defined_balance_value;
3434
3435 /* ARCHIVE CODE */
3436 PROCEDURE archive_code (
3437 p_assignment_action_id IN NUMBER
3438 , p_effective_date IN DATE
3439 )
3440 IS
3441 begin
3442
3443 IF g_debug
3444 THEN
3445 hr_utility.set_location (' Leaving Procedure ARCHIVE_CODE', 390);
3446 END IF;
3447 END archive_code;
3448
3449 --- Report XML generating code
3450 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
3451 IS
3452 l_xfdf_string CLOB;
3453 l_str1 VARCHAR2 (1000);
3454 l_str2 VARCHAR2 (20);
3455 l_str3 VARCHAR2 (20);
3456 l_str4 VARCHAR2 (20);
3457 l_str5 VARCHAR2 (20);
3458 l_str6 VARCHAR2 (30);
3459 l_str7 VARCHAR2 (1000);
3460 l_str8 VARCHAR2 (240);
3461 l_str9 VARCHAR2 (240);
3462 l_str10 VARCHAR2 (20);
3463 l_str11 VARCHAR2 (20);
3464 current_index PLS_INTEGER;
3465 l_iana_charset VARCHAR2 (50);
3466 BEGIN
3467 l_iana_charset := hr_se_utility.get_iana_charset;
3468
3469 -- hr_utility.set_location ('Entering WritetoCLOB ', 70);
3470 l_str1 :=
3471 '<?xml version="1.0" encoding="'
3472 || l_iana_charset
3473 || '"?> <ROOT><HPDR>';
3474 l_str2 := '<';
3475 l_str3 := '>';
3476 l_str4 := '</';
3477 l_str5 := '>';
3478 l_str6 := '</HPDR></ROOT>';
3479 l_str7 :=
3480 '<?xml version="1.0" encoding="'
3481 || l_iana_charset
3482 || '"?> <ROOT></ROOT>';
3483 l_str10 := '<HPDR>';
3484 l_str11 := '</HPDR>';
3485 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
3486 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
3487 current_index := 0;
3488
3489 IF xml_tab.COUNT > 0
3490 THEN
3491 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
3492
3493 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST
3494 LOOP
3495 l_str8 := xml_tab (table_counter).tagname;
3496 l_str9 := xml_tab (table_counter).tagvalue;
3497
3498
3499
3500 IF l_str9 IN
3501 (
3502 'CAT_DETAILS',
3503 'END_CAT_DETAILS'
3504 )
3505 THEN
3506 IF l_str9 IN
3507 ('CAT_DETAILS')
3508 THEN
3509 DBMS_LOB.writeappend (l_xfdf_string
3510 , LENGTH (l_str2)
3511 , l_str2
3512 );
3513 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
3514 , l_str8);
3515 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
3516 , l_str3);
3517 ELSE
3518 DBMS_LOB.writeappend (l_xfdf_string
3519 , LENGTH (l_str4)
3520 , l_str4
3521 );
3522 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
3523 , l_str8);
3524 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
3525 , l_str5);
3526 END IF;
3527 ELSE
3528 IF l_str9 IS NOT NULL
3529 THEN
3530 DBMS_LOB.writeappend (l_xfdf_string
3531 , LENGTH (l_str2)
3532 , l_str2
3533 );
3534 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
3535 , l_str8);
3536 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
3537 , l_str3);
3538 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9)
3539 , l_str9);
3540 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
3541 , l_str4);
3542 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
3543 , l_str8);
3544 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
3545 , l_str5);
3546 ELSE
3547 DBMS_LOB.writeappend (l_xfdf_string
3548 , LENGTH (l_str2)
3549 , l_str2
3550 );
3551 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
3552 , l_str8);
3553 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
3554 , l_str3);
3555 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
3556 , l_str4);
3557 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
3558 , l_str8);
3559 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
3560 , l_str5);
3561 END IF;
3562 END IF;
3563 END LOOP;
3564
3565 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
3566 ELSE
3567 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
3568 END IF;
3569 p_xfdf_clob := l_xfdf_string;
3570 -- hr_utility.set_location ('Leaving WritetoCLOB ', 40);
3571 EXCEPTION
3572 WHEN OTHERS
3573 THEN
3574 hr_utility.TRACE ('sqlerrm ' || SQLERRM);
3575 hr_utility.raise_error;
3576 END writetoclob;
3577
3578 -----------------------------------------------------------------------------------
3579 -- POPULATE_DATA_DETAIL generates xml for the reports.
3580 -----------------------------------------------------------------------------------
3581 --
3582 --
3583 PROCEDURE POPULATE_DATA_DETAIL
3584 (p_business_group_id IN NUMBER,
3585 p_payroll_action_id IN VARCHAR2 ,
3586 p_template_name IN VARCHAR2,
3587 p_xml OUT NOCOPY CLOB)
3588 IS
3589
3590
3591 /* Cursor to fetch Header Information */
3592
3593 l_employee_category per_all_assignments_f.employee_category%type;
3594 l_lu_salary NUMBER;
3595 l_salary NUMBER:=0;
3596 l_grand_salary NUMBER:=0;
3597 l_legal_employer hr_organization_units.name%type;
3598 l_local_unit hr_organization_units.name%type;
3599 l_action_information_id pay_action_information.action_information_id%TYPE;
3600 l_month varchar2(20);
3601 l_year NUMBER;
3602 l_retroactive_date_from DATE;
3603 l_retroactive_date_to DATE;
3604 l_bh_worked_calendar_month NUMBER;
3605 l_bh_worked_payment_period NUMBER;
3606 l_bh_pbt_value NUMBER;
3607 l_bh_pcow_value NUMBER;
3608 l_bh_nha_value NUMBER;
3609 l_bh_nho_value NUMBER;
3610 l_bh_retroactive_pay NUMBER;
3611 l_bh_ppo_value NUMBER;
3612 l_bh_sick_pay NUMBER;
3613 l_bh_total_employees NUMBER;
3614 l_bs_gross_pay NUMBER;
3615 l_bs_working_agreement NUMBER;
3616 l_bs_tcdp_value NUMBER;
3617 l_bs_tcow_value NUMBER;
3618 l_bs_nha_value NUMBER;
3619 l_bs_nho_value NUMBER;
3620 l_bs_retroactive_pay NUMBER;
3621 l_bs_ppo_value NUMBER;
3622 l_bs_sick_pay NUMBER;
3623 l_bs_total_employees NUMBER;
3624 l_ws_full_time_employee NUMBER;
3625 l_ws_gross_pay NUMBER;
3626 l_ws_working_agreement NUMBER;
3627 l_ws_tcdp_value NUMBER;
3628 l_ws_tcow_value NUMBER;
3629 l_ws_nha_value NUMBER;
3630 l_ws_nho_value NUMBER;
3631 l_ws_retroactive_pay NUMBER;
3632 l_ws_ppo_value NUMBER;
3633 l_ws_sick_pay NUMBER;
3634 l_ws_total_employees NUMBER;
3635 l_wh_full_time_employee NUMBER;
3636 l_wh_gross_pay NUMBER;
3637 l_wh_working_agreement NUMBER;
3638 l_wh_tcdp_value NUMBER;
3639 l_wh_retroactive_pay NUMBER;
3640 l_wh_ppo_value NUMBER;
3641 l_wh_sick_pay NUMBER;
3642 l_wh_total_employees NUMBER;
3643
3644
3645
3646 /*CURSOR csr_local_unit_level_details (p_payroll_action_id NUMBER)
3647 IS
3648 SELECT distinct
3649 pai.ACTION_INFORMATION7 Reporting_Year,
3650 pai1.ACTION_INFORMATION3 Legal_Employer,
3651 pai1.ACTION_INFORMATION4 Local_unit_id,
3652 pai1.ACTION_INFORMATION5 Local_unit,
3653 pai.ACTION_INFORMATION8 Insurance_Number--,
3654 /*pai1.ACTION_INFORMATION6 Cfar_Number,
3655 pai1.ACTION_INFORMATION7 Name,
3656 pai1.ACTION_INFORMATION8 Employee_Category,
3657 pai1.ACTION_INFORMATION9 White_Collar,
3658 pai1.ACTION_INFORMATION10 Gross_Salary,
3659 pai1.ACTION_INFORMATION11 Terminated,
3660 pai1.ACTION_INFORMATION12 Painter*/
3661 /*FROM
3662 pay_action_information pai,
3663 pay_payroll_actions ppa,
3664 pay_action_information pai1
3665 WHERE
3666 pai.action_context_id = ppa.payroll_action_id
3667 AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
3668 AND pai.action_context_id = pai1.action_context_id
3669 AND pai1.action_context_id= ppa.payroll_action_id
3670 AND pai1.action_context_type='PA'
3671 AND pai1.action_information2 = 'PER'
3672 AND pai1.action_information1 = 'PYSEFORA'
3673 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
3674 AND pai1.ACTION_INFORMATION9=l_employee_category
3675 AND pai.action_context_type = 'PA'
3676 AND pai.action_information1 = 'PYSEFORA'
3677 AND pai.action_information_category = 'EMEA REPORT DETAILS'
3678 ORDER BY --pai1.ACTION_INFORMATION3,
3679 pai1.ACTION_INFORMATION4 ;--pai1.ACTION_INFORMATION8 ;
3680
3681 CURSOR csr_all_local_unit_details (p_payroll_action_id NUMBER)
3682 IS
3683 SELECT distinct
3684 pai.ACTION_INFORMATION7 Reporting_Year,
3685 pai1.ACTION_INFORMATION3 Legal_Employer,
3686 pai1.ACTION_INFORMATION4 Local_unit_id,
3687 pai1.ACTION_INFORMATION5 Local_unit,
3688 pai.ACTION_INFORMATION8 Insurance_Number,
3689 pai1.ACTION_INFORMATION9 Employee_Category
3690 /*pai1.ACTION_INFORMATION6 Cfar_Number,
3691 pai1.ACTION_INFORMATION7 Name,
3692 pai1.ACTION_INFORMATION8 Employee_Category,
3693 pai1.ACTION_INFORMATION9 White_Collar,
3694 pai1.ACTION_INFORMATION10 Gross_Salary,
3695 pai1.ACTION_INFORMATION11 Terminated,
3696 pai1.ACTION_INFORMATION12 Painter*/
3697 /*FROM
3698 pay_action_information pai,
3699 pay_payroll_actions ppa,
3700 pay_action_information pai1
3701 WHERE
3702 pai.action_context_id = ppa.payroll_action_id
3703 AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
3704 AND pai.action_context_id = pai1.action_context_id
3705 AND pai1.action_context_id= ppa.payroll_action_id
3706 AND pai1.action_context_type='PA'
3707 AND pai1.action_information2 = 'PER'
3708 AND pai1.action_information1 = 'PYSEFORA'
3709 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
3710 AND pai1.ACTION_INFORMATION9 IN ('BC','WC')
3711 AND pai.action_context_type = 'PA'
3712 AND pai.action_information1 = 'PYSEFORA'
3713 AND pai.action_information_category = 'EMEA REPORT DETAILS'
3714 ORDER BY --pai1.ACTION_INFORMATION3,
3715 pai1.ACTION_INFORMATION9, pai1.ACTION_INFORMATION4 ;--pai1.ACTION_INFORMATION8 ;
3716
3717
3718 CURSOR csr_person_level_details (p_payroll_action_id NUMBER,local_unit_id varchar2)
3719 IS
3720 SELECT
3721 pai1.ACTION_INFORMATION6 Cfar_Number,
3722 pai1.ACTION_INFORMATION7 Person_Number,
3723 pai1.ACTION_INFORMATION8 Name,
3724 pai1.ACTION_INFORMATION9 Employee_Category,
3725 pai1.ACTION_INFORMATION10 White_Collar,
3726 nvl(pai1.ACTION_INFORMATION11,0) Gross_Salary,
3727 pai1.ACTION_INFORMATION12 Terminated,
3728 pai1.ACTION_INFORMATION13 Painter
3729 FROM
3730 --pay_action_information pai,
3731 pay_payroll_actions ppa,
3732 pay_action_information pai1
3733 WHERE
3734 pai1.action_context_id = ppa.payroll_action_id
3735 AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
3736 /*AND pai.action_context_id = pai1.action_context_id*/
3737 /*AND pai1.action_context_id= ppa.payroll_action_id
3738 AND pai1.action_context_type='PA'
3739 AND pai1.action_information2 = 'PER'
3740 AND pai1.action_information1 = 'PYSEFORA'
3741 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
3742 AND pai1.ACTION_INFORMATION9=l_employee_category
3743 AND pai1.ACTION_INFORMATION4=local_unit_id
3744 /*AND pai.action_context_type = 'PA'
3745 AND pai.action_information1 = 'PYSEFORA'
3746 AND pai.action_information_category = 'EMEA REPORT DETAILS'*/
3747 /*ORDER BY pai1.ACTION_INFORMATION8;
3748 --pai1.ACTION_INFORMATION4,--pai1.ACTION_INFORMATION8 ; */
3749
3750 CURSOR csr_local_unit(csr_v_payroll_action_id number)
3751 IS
3752 SELECT action_information3 local_unit_id
3753 FROM pay_action_information pai
3754 WHERE pai.action_context_id= csr_v_payroll_action_id
3755 AND pai.action_context_type='PA'
3756 AND pai.action_information2 = 'LU'
3757 AND pai.action_information1 = 'PYSEWSSA'
3758 AND pai.action_information_category = 'EMEA REPORT INFORMATION'
3759 GROUP BY action_information3;
3760
3761
3762
3763 /*CURSOR csr_emp_cat(csr_v_payroll_action_id number, csr_v_local_unit_id number )
3764 IS
3765 SELECT pai.action_information3 legal_employer,
3766 pai1.action_information4 local_unit,
3767 pai.action_information7 month,
3768 pai.action_information8 year,
3769 fnd_date.canonical_to_date(pai.action_information9) retroactive_date_from,
3770 fnd_date.canonical_to_date(pai.action_information10) retroactive_date_to,
3771 decode(pai2.action_information4,0,NULL,pai2.action_information4) bh_worked_calendar_month,
3772 decode(pai2.action_information5,0,NULL,pai2.action_information5) bh_worked_payment_period,
3773 decode(pai2.action_information12,0,NULL,pai2.action_information12) bh_pbt_value,
3774 decode(pai2.action_information13,0,NULL,pai2.action_information13) bh_pcow_value,
3775 decode(pai2.action_information14,0,NULL,pai2.action_information14) bh_nha_value,
3776 decode(pai2.action_information15,0,NULL,pai2.action_information15) bh_nho_value,
3777 decode(pai2.action_information16,0,NULL,pai2.action_information16) bh_retroactive_pay,
3778 decode(pai2.action_information17,0,NULL,pai2.action_information17) bh_ppo_value,
3779 decode(pai2.action_information18,0,NULL,pai2.action_information18) bh_sick_pay,
3780 decode(pai2.action_information19,0,NULL,pai2.action_information19) bh_total_employees,
3781 decode(pai3.action_information7,0,NULL,pai3.action_information7) bs_gross_pay,
3782 decode(pai3.action_information8,0,NULL,pai3.action_information8) bs_working_agreement,
3783 decode(pai3.action_information9,0,NULL,pai3.action_information9) bs_tcdp_value,
3784 decode(pai3.action_information10,0,NULL,pai3.action_information10) bs_tcow_value,
3785 decode(pai3.action_information14,0,NULL,pai3.action_information14) bs_nha_value,
3786 decode(pai3.action_information15,0,NULL,pai3.action_information15) bs_nho_value,
3787 decode(pai3.action_information16,0,NULL,pai3.action_information16) bs_retroactive_pay,
3788 decode(pai3.action_information17,0,NULL,pai3.action_information17) bs_ppo_value,
3789 decode(pai3.action_information18,0,NULL,pai3.action_information18) bs_sick_pay,
3790 decode(pai3.action_information19,0,NULL,pai3.action_information19) bs_total_employees,
3791 decode(pai4.action_information6,0,NULL,pai4.action_information6) ws_full_time_employee,
3792 decode(pai4.action_information7,0,NULL,pai4.action_information7) ws_gross_pay,
3793 decode(pai4.action_information8,0,NULL,pai4.action_information8) ws_working_agreement,
3794 decode(pai4.action_information9,0,NULL,pai4.action_information9) ws_tcdp_value,
3795 decode(pai4.action_information10,0,NULL,pai4.action_information10) ws_tcow_value,
3796 decode(pai4.action_information14,0,NULL,pai4.action_information14) ws_nha_value,
3797 decode(pai4.action_information15,0,NULL,pai4.action_information15) ws_nho_value,
3798 decode(pai4.action_information16,0,NULL,pai4.action_information16) ws_retroactive_pay,
3799 decode(pai4.action_information17,0,NULL,pai4.action_information17) ws_ppo_value,
3800 decode(pai4.action_information18,0,NULL,pai4.action_information18) ws_sick_pay,
3801 decode(pai4.action_information19,0,NULL,pai4.action_information19) ws_total_employees,
3802 decode(pai5.action_information6,0,NULL,pai5.action_information6) wh_full_time_employee,
3803 decode(pai5.action_information7,0,NULL,pai5.action_information7) wh_gross_pay,
3804 decode(pai5.action_information8,0,NULL,pai5.action_information8) wh_working_agreement,
3805 decode(pai5.action_information9,0,NULL,pai5.action_information9) wh_tcdp_value,
3806 decode(pai5.action_information16,0,NULL,pai5.action_information16) wh_retroactive_pay,
3807 decode(pai5.action_information17,0,NULL,pai5.action_information17) wh_ppo_value,
3808 decode(pai5.action_information18,0,NULL,pai5.action_information18) wh_sick_pay,
3809 decode(pai5.action_information19,0,NULL,pai5.action_information19) wh_total_employees
3810 FROM
3811 pay_action_information pai,
3812 pay_payroll_actions ppa,
3813 pay_action_information pai1,
3814 pay_action_information pai2,
3815 pay_action_information pai3,
3816 pay_action_information pai4,
3817 pay_action_information pai5
3818 WHERE
3819 ppa.payroll_action_id=csr_v_payroll_action_id --45660 --p_payroll_action_id
3820 AND ppa.payroll_action_id=pai.action_context_id
3821 AND pai.action_context_id=pai1.action_context_id
3822 AND pai1.action_context_id=pai2.action_context_id
3823 AND pai2.action_context_id=pai3.action_context_id
3824 AND pai3.action_context_id=pai4.action_context_id
3825 AND pai4.action_context_id=pai5.action_context_id
3826 and pai5.action_context_id=ppa.payroll_action_id
3827 AND pai.action_context_type = 'PA'
3828 AND pai.action_information1 = 'PYSEWSSA'
3829 AND pai.action_information_category = 'EMEA REPORT DETAILS'
3830 AND pai1.action_context_type='PA'
3831 AND pai1.action_information2 = 'LU'
3832 AND pai1.action_information1 = 'PYSEWSSA'
3833 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
3834 AND pai2.action_context_type='PA'
3835 AND pai2.action_information2 = 'BH'
3836 AND pai2.action_information1 = 'PYSEWSSA'
3837 AND pai2.action_information_category = 'EMEA REPORT INFORMATION'
3838 AND pai3.action_context_type='PA'
3839 AND pai3.action_information2 = 'BH'
3840 AND pai3.action_information1 = 'PYSEWSSA'
3841 AND pai3.action_information_category = 'EMEA REPORT INFORMATION'
3842 AND pai4.action_context_type='PA'
3843 AND pai4.action_information2 = 'BS'
3844 AND pai4.action_information1 = 'PYSEWSSA'
3845 AND pai4.action_information_category = 'EMEA REPORT INFORMATION'
3846 AND pai5.action_context_type='PA'
3847 AND pai5.action_information2 = 'WH'
3848 AND pai5.action_information1 = 'PYSEWSSA'
3849 AND pai5.action_information_category = 'EMEA REPORT INFORMATION'
3850 AND pai1.action_information3=csr_v_local_unit_id --3135 --csr_v_local_unit_id
3851 AND pai1.action_information3=pai2.action_information3
3852 AND pai2.action_information3=pai3.action_information3
3853 AND pai3.action_information3=pai4.action_information3
3854 AND pai4.action_information3=pai5.action_information3
3855 AND pai5.action_information3=pai1.action_information3
3856 ORDER BY pai1.action_information3; */
3857 CURSOR csr_emp_cat(csr_v_payroll_action_id number, csr_v_local_unit_id number )
3858 IS
3859 SELECT pai.action_information3 legal_employer,
3860 pai1.action_information4 local_unit,
3861 pai.action_information7 || ' ' || pai.action_information8 period,-- month,
3862 --pai.action_information8 year,
3863 fnd_date.canonical_to_date(pai.action_information9) retroactive_date_from,
3864 fnd_date.canonical_to_date(pai.action_information10) retroactive_date_to
3865 FROM
3866 pay_action_information pai,
3867 pay_action_information pai1,
3868 pay_payroll_actions ppa
3869 WHERE
3870 ppa.payroll_action_id=csr_v_payroll_action_id
3871 AND ppa.payroll_action_id=pai.action_context_id
3872 AND pai.action_context_id=pai1.action_context_id
3873 AND pai1.action_context_id=ppa.payroll_action_id
3874 AND pai.action_context_type = 'PA'
3875 AND pai.action_information1 = 'PYSEWSSA'
3876 AND pai.action_information_category = 'EMEA REPORT DETAILS'
3877 AND pai1.action_context_type='PA'
3878 AND pai1.action_information2 = 'LU'
3879 AND pai1.action_information1 = 'PYSEWSSA'
3880 AND pai1.action_information3=csr_v_local_unit_id;
3881
3882 CURSOR csr_blue_hour(csr_v_payroll_action_id number, csr_v_local_unit_id number, csr_v_action_information_id number )
3883 IS
3884 SELECT
3885 --decode(sum(pai2.action_information4),0,NULL,sum(pai2.action_information4)) bh_worked_calendar_month,
3886 decode(sum(pai2.action_information5),0,NULL,sum(pai2.action_information5)) bh_worked_payment_period,
3887 decode(sum(pai2.action_information12),0,NULL,sum(pai2.action_information12)) bh_pbt_value,
3888 decode(sum(pai2.action_information13),0,NULL,sum(pai2.action_information13)) bh_pcow_value,
3889 decode(sum(pai2.action_information14),0,NULL,sum(pai2.action_information14)) bh_nha_value,
3890 decode(sum(pai2.action_information15),0,NULL,sum(pai2.action_information15)) bh_nho_value,
3891 decode(sum(pai2.action_information16),0,NULL,sum(pai2.action_information16)) bh_retroactive_pay,
3892 decode(sum(pai2.action_information17),0,NULL,sum(pai2.action_information17)) bh_ppo_value,
3893 decode(sum(pai2.action_information18),0,NULL,sum(pai2.action_information18)) bh_sick_pay,
3894 decode(sum(pai2.action_information19),0,NULL,sum(pai2.action_information19)) bh_total_employees
3895 FROM
3896 pay_action_information pai1,
3897 pay_action_information pai2,
3898 pay_payroll_actions ppa
3899 WHERE
3900 ppa.payroll_action_id=csr_v_payroll_action_id
3901 AND ppa.payroll_action_id=pai1.action_context_id
3902 AND pai1.action_context_id=pai2.action_context_id
3903 AND pai2.action_context_id=ppa.payroll_action_id
3904 AND pai1.action_information3=to_char(csr_v_local_unit_id /*3135*/) --csr_v_local_unit_id
3905 AND pai1.action_context_type='PA'
3906 AND pai1.action_information2 = 'LU'
3907 AND pai1.action_information1 = 'PYSEWSSA'
3908 AND pai1.action_information_id=csr_v_action_information_id
3909 AND pai1.action_information3=pai2.action_information3
3910 AND pai2.action_context_type='PA'
3911 AND pai2.action_information2 = 'BH'
3912 AND pai2.action_information1 = 'PYSEWSSA'
3913 AND pai2.action_information_category = 'EMEA REPORT INFORMATION';
3914
3915 CURSOR csr_blue_hour_calendar(csr_v_payroll_action_id number, csr_v_local_unit_id number, csr_v_action_information_id number )
3916 IS SELECT pai2.action_information4 bh_worked_calendar_month
3917 FROM
3918 pay_action_information pai1,
3919 pay_action_information pai2,
3920 pay_payroll_actions ppa
3921 WHERE
3922 ppa.payroll_action_id=csr_v_payroll_action_id
3923 AND ppa.payroll_action_id=pai1.action_context_id
3924 AND pai1.action_context_id=pai2.action_context_id
3925 AND pai2.action_context_id=ppa.payroll_action_id
3926 AND pai1.action_information3=to_char(csr_v_local_unit_id /*3135*/) --csr_v_local_unit_id
3927 AND pai1.action_context_type='PA'
3928 AND pai1.action_information2 = 'LU'
3929 AND pai1.action_information1 = 'PYSEWSSA'
3930 AND pai1.action_information_id=csr_v_action_information_id
3931 AND pai1.action_information3=pai2.action_information3
3932 AND pai2.action_context_type='PA'
3933 AND pai2.action_information2 = 'BH'
3934 AND pai2.action_information1 = 'PYSEWSSA'
3935 AND pai2.action_information_category = 'EMEA REPORT INFORMATION'
3936 AND ROWNUM <2;
3937
3938 CURSOR csr_blue_salary(csr_v_payroll_action_id number, csr_v_local_unit_id number, csr_v_action_information_id number )
3939 IS
3940 SELECT
3941 decode(sum(pai3.action_information7),0,NULL,sum(pai3.action_information7)) bs_gross_pay,
3942 decode(sum(pai3.action_information8),0,NULL,sum(pai3.action_information8)) bs_working_agreement,
3943 decode(sum(pai3.action_information9),0,NULL,sum(pai3.action_information9)) bs_tcdp_value,
3944 decode(sum(pai3.action_information10),0,NULL,sum(pai3.action_information10)) bs_tcow_value,
3945 decode(sum(pai3.action_information14),0,NULL,sum(pai3.action_information14)) bs_nha_value,
3946 decode(sum(pai3.action_information15),0,NULL,sum(pai3.action_information15)) bs_nho_value,
3947 decode(sum(pai3.action_information16),0,NULL,sum(pai3.action_information16)) bs_retroactive_pay,
3948 decode(sum(pai3.action_information17),0,NULL,sum(pai3.action_information17)) bs_ppo_value,
3949 decode(sum(pai3.action_information18),0,NULL,sum(pai3.action_information18)) bs_sick_pay,
3950 decode(sum(pai3.action_information19),0,NULL,sum(pai3.action_information19)) bs_total_employees
3951 FROM
3952 pay_action_information pai1,
3953 pay_action_information pai3,
3954 pay_payroll_actions ppa
3955 WHERE
3956 ppa.payroll_action_id=csr_v_payroll_action_id
3957 AND ppa.payroll_action_id=pai1.action_context_id
3958 AND pai1.action_context_id=pai3.action_context_id
3959 AND pai3.action_context_id=ppa.payroll_action_id
3960 AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
3961 AND pai1.action_context_type='PA'
3962 AND pai1.action_information2 = 'LU'
3963 AND pai1.action_information1 = 'PYSEWSSA'
3964 AND pai1.action_information_id=csr_v_action_information_id
3965 AND pai1.action_information3=pai3.action_information3
3966 AND pai3.action_context_type='PA'
3967 AND pai3.action_information2 = 'BS'
3968 AND pai3.action_information1 = 'PYSEWSSA'
3969 AND pai3.action_information_category = 'EMEA REPORT INFORMATION';
3970
3971 CURSOR csr_white_salary(csr_v_payroll_action_id number, csr_v_local_unit_id number, csr_v_action_information_id number )
3972 IS
3973 SELECT
3974 decode(sum(pai4.action_information6),0,NULL,sum(pai4.action_information6)) ws_full_time_employee,
3975 decode(sum(pai4.action_information7),0,NULL,sum(pai4.action_information7)) ws_gross_pay,
3976 decode(sum(pai4.action_information8),0,NULL,sum(pai4.action_information8)) ws_working_agreement,
3977 decode(sum(pai4.action_information9),0,NULL,sum(pai4.action_information9)) ws_tcdp_value,
3978 decode(sum(pai4.action_information10),0,NULL,sum(pai4.action_information10)) ws_tcow_value,
3979 decode(sum(pai4.action_information14),0,NULL,sum(pai4.action_information14)) ws_nha_value,
3980 decode(sum(pai4.action_information15),0,NULL,sum(pai4.action_information15)) ws_nho_value,
3981 decode(sum(pai4.action_information16),0,NULL,sum(pai4.action_information16)) ws_retroactive_pay,
3982 decode(sum(pai4.action_information17),0,NULL,sum(pai4.action_information17)) ws_ppo_value,
3983 decode(sum(pai4.action_information18),0,NULL,sum(pai4.action_information18)) ws_sick_pay,
3984 decode(sum(pai4.action_information19),0,NULL,sum(pai4.action_information19)) ws_total_employees
3985 FROM
3986 pay_action_information pai1,
3987 pay_action_information pai4,
3988 pay_payroll_actions ppa
3989 WHERE
3990 ppa.payroll_action_id=csr_v_payroll_action_id
3991 AND ppa.payroll_action_id=pai1.action_context_id
3992 AND pai1.action_context_id=pai4.action_context_id
3993 AND pai4.action_context_id=ppa.payroll_action_id
3994 AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
3995 AND pai1.action_context_type='PA'
3996 AND pai1.action_information2 = 'LU'
3997 AND pai1.action_information3=pai4.action_information3
3998 AND pai1.action_information1 = 'PYSEWSSA'
3999 AND pai1.action_information_id=csr_v_action_information_id
4000 AND pai4.action_context_type='PA'
4001 AND pai4.action_information2 = 'WS'
4002 AND pai4.action_information1 = 'PYSEWSSA'
4003 AND pai4.action_information_category = 'EMEA REPORT INFORMATION';
4004
4005 CURSOR csr_white_hour(csr_v_payroll_action_id number, csr_v_local_unit_id number, csr_v_action_information_id number )
4006 IS
4007 SELECT
4008 decode(sum(pai5.action_information6),0,NULL,sum(pai5.action_information6)) wh_full_time_employee,
4009 decode(sum(pai5.action_information7),0,NULL,sum(pai5.action_information7)) wh_gross_pay,
4010 decode(sum(pai5.action_information8),0,NULL,sum(pai5.action_information8)) wh_working_agreement,
4011 decode(sum(pai5.action_information9),0,NULL,sum(pai5.action_information9)) wh_tcdp_value,
4012 decode(sum(pai5.action_information16),0,NULL,sum(pai5.action_information16)) wh_retroactive_pay,
4013 decode(sum(pai5.action_information17),0,NULL,sum(pai5.action_information17)) wh_ppo_value,
4014 decode(sum(pai5.action_information18),0,NULL,sum(pai5.action_information18)) wh_sick_pay,
4015 decode(sum(pai5.action_information19),0,NULL,sum(pai5.action_information19)) wh_total_employees
4016 FROM
4017 pay_action_information pai1,
4018 pay_action_information pai5,
4019 pay_payroll_actions ppa
4020 WHERE
4021 ppa.payroll_action_id=csr_v_payroll_action_id
4022 AND ppa.payroll_action_id=pai1.action_context_id
4023 AND pai1.action_context_id=pai5.action_context_id
4024 AND pai5.action_context_id=ppa.payroll_action_id
4025 AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
4026 AND pai1.action_context_type='PA'
4027 AND pai1.action_information2 = 'LU'
4028 AND pai1.action_information1 = 'PYSEWSSA'
4029 AND pai1.action_information_id=csr_v_action_information_id
4030 AND pai1.action_information3=pai5.action_information3
4031 AND pai5.action_context_type='PA'
4032 AND pai5.action_information2 = 'WH'
4033 AND pai5.action_information1 = 'PYSEWSSA'
4034 AND pai5.action_information_category = 'EMEA REPORT INFORMATION';
4035
4036
4037
4038 CURSOR csr_unique_local_unit(csr_v_payroll_action_id number, csr_v_local_unit_id number )
4039 IS
4040 SELECT MIN(action_information_id)
4041 FROM pay_action_information pai
4042 WHERE pai.action_context_id= csr_v_payroll_action_id
4043 AND pai.action_context_type='PA'
4044 AND pai.action_information2 = 'LU'
4045 AND pai.action_information1 = 'PYSEWSSA'
4046 AND pai.action_information3=to_char(csr_v_local_unit_id)
4047 AND pai.action_information_category = 'EMEA REPORT INFORMATION';
4048
4049
4050 --l_local_unit_details_rec csr_local_unit_level_details%rowtype;
4051
4052
4053
4054 l_counter NUMBER:=0;
4055 l_total NUMBER;
4056 l_total_eft NUMBER;
4057 l_count NUMBER;
4058 l_payroll_action_id NUMBER;
4059 l_lu_counter_reset VARCHAR2(10);
4060 l_prev_local_unit VARCHAR2(15);
4061 l_report_date DATE;
4062 /*l_total_termination NUMBER;
4063 l_total_hire NUMBER;
4064 l_total_absence NUMBER;
4065 l_total_sick NUMBER;
4066 l_total_lu_emp NUMBER;
4067 l_total_le_emp NUMBER;
4068 l_legal_employer VARCHAR2(80);
4069 l_regular_men NUMBER;
4070 l_regular_women NUMBER;
4071 l_temp_men NUMBER;
4072 l_temp_women NUMBER;*/
4073 l_person_number VARCHAR2(50);
4074 l_local_unit_id hr_organization_units.organization_id%type;
4075 l_period varchar2(50);
4076
4077 --l_local_unit hr_organization_units.name%TYPE;
4078
4079 BEGIN
4080
4081
4082 IF p_payroll_action_id IS NULL THEN
4083 BEGIN
4084 SELECT payroll_action_id
4085 INTO l_payroll_action_id
4086 FROM pay_payroll_actions ppa,
4087 fnd_conc_req_summary_v fcrs,
4088 fnd_conc_req_summary_v fcrs1
4089 WHERE fcrs.request_id = fnd_global.conc_request_id
4090 AND fcrs.priority_request_id = fcrs1.priority_request_id
4091 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
4092 AND ppa.request_id = fcrs1.request_id;
4093 EXCEPTION
4094 WHEN OTHERS THEN
4095 NULL;
4096 END ;
4097 ELSE
4098 l_payroll_action_id := p_payroll_action_id;
4099 END IF;
4100 g_payroll_action_id :=p_payroll_action_id;
4101 /* g_business_group_id := null;
4102 g_legal_employer_id := null;
4103 g_start_date := null;
4104 g_end_date := null;
4105 g_version := null;
4106 g_archive := null;*/
4107
4108 get_all_parameters (p_payroll_action_id
4109 , g_business_group_id
4110 , g_effective_date
4111 , g_legal_employer_id
4112 , g_LU_request
4113 , g_local_unit_id
4114 , g_month
4115 , g_year
4116 , g_retroactive_payment_from
4117 , g_retroactive_payment_to
4118 );
4119
4120
4121 hr_utility.set_location('Entered Procedure GETDATA',10);
4122
4123 /* xml_tab(l_counter).TagName :='LU_DETAILS';
4124 xml_tab(l_counter).TagValue :='LU_DETAILS';*/
4125 /* l_counter:=l_counter+1;*/
4126
4127 /* Get the File Header Information */
4128 hr_utility.set_location('Before populating pl/sql table',20);
4129 l_lu_salary:=0;
4130 FOR csr_local IN csr_local_unit(p_payroll_action_id) LOOP
4131
4132 l_local_unit_id:=csr_local.local_unit_id;
4133 OPEN csr_unique_local_unit(p_payroll_action_id,l_local_unit_id);
4134 FETCH csr_unique_local_unit INTO l_action_information_id;
4135 CLOSE csr_unique_local_unit;
4136 -- FOR csr_cat IN csr_emp_cat(p_payroll_action_id, l_local_unit_id) LOOP
4137 OPEN csr_emp_cat(p_payroll_action_id, l_local_unit_id);
4138 FETCH csr_emp_cat INTO l_legal_employer,l_local_unit,l_period,
4139 l_retroactive_date_from,l_retroactive_date_to;
4140 CLOSE csr_emp_cat;
4141 xml_tab(l_counter).TagName :='CAT_DETAILS';
4142 xml_tab(l_counter).TagValue :='CAT_DETAILS';
4143 l_counter:=l_counter+1;
4144
4145 xml_tab(l_counter).TagName :='legal_employer';
4146 xml_tab(l_counter).TagValue :=l_legal_employer; --csr_cat.legal_employer;
4147 l_counter:=l_counter+1;
4148
4149 xml_tab(l_counter).TagName :='local_unit';
4150 xml_tab(l_counter).TagValue :=l_local_unit; --csr_cat.local_unit;
4151 l_counter:=l_counter+1;
4152
4153 /*xml_tab(l_counter).TagName :='local_unit';
4154 xml_tab(l_counter).TagValue :=csr_cat.local_unit;
4155 l_counter:=l_counter+1;*/
4156
4157 xml_tab(l_counter).TagName :='period';
4158 xml_tab(l_counter).TagValue :=l_period; --csr_cat.month;
4159 l_counter:=l_counter+1;
4160
4161 /*xml_tab(l_counter).TagName :='month';
4162 xml_tab(l_counter).TagValue :=l_month; --csr_cat.month;
4163 l_counter:=l_counter+1;
4164
4165 xml_tab(l_counter).TagName :='year';
4166 xml_tab(l_counter).TagValue :=l_year; --csr_cat.year;
4167 l_counter:=l_counter+1;*/
4168
4169 xml_tab(l_counter).TagName :='retroactive_date_from';
4170 xml_tab(l_counter).TagValue :=l_retroactive_date_from; --csr_cat.retroactive_date_from;
4171 l_counter:=l_counter+1;
4172
4173 xml_tab(l_counter).TagName :='retroactive_date_to';
4174 xml_tab(l_counter).TagValue :=l_retroactive_date_to; --csr_cat.retroactive_date_to;
4175 l_counter:=l_counter+1;
4176 OPEN csr_blue_hour(p_payroll_action_id, l_local_unit_id,l_action_information_id);
4177 FETCH csr_blue_hour INTO /*l_bh_worked_calendar_month,*/l_bh_worked_payment_period,
4178 l_bh_pbt_value,l_bh_pcow_value,l_bh_nha_value,l_bh_nho_value,l_bh_retroactive_pay,
4179 l_bh_ppo_value,l_bh_sick_pay,l_bh_total_employees;
4180 CLOSE csr_blue_hour;
4181 OPEN csr_blue_hour_calendar(p_payroll_action_id, l_local_unit_id,l_action_information_id);
4182 FETCH csr_blue_hour_calendar INTO l_bh_worked_calendar_month;
4183 CLOSE csr_blue_hour_calendar;
4184 xml_tab(l_counter).TagName :='bh_worked_calendar_month';
4185 xml_tab(l_counter).TagValue :=l_bh_worked_calendar_month; --csr_cat.bh_worked_calendar_month;
4186 l_counter:=l_counter+1;
4187
4188 xml_tab(l_counter).TagName :='bh_worked_payment_period';
4189 xml_tab(l_counter).TagValue :=l_bh_worked_payment_period; --csr_cat.bh_worked_payment_period;
4190 l_counter:=l_counter+1;
4191
4192 xml_tab(l_counter).TagName :='bh_pbt_value';
4193 xml_tab(l_counter).TagValue :=l_bh_pbt_value; --csr_cat.bh_pbt_value;
4194 l_counter:=l_counter+1;
4195
4196 xml_tab(l_counter).TagName :='bh_pcow_value';
4197 xml_tab(l_counter).TagValue :=l_bh_pcow_value; --csr_cat.bh_pcow_value;
4198 l_counter:=l_counter+1;
4199
4200 xml_tab(l_counter).TagName :='bh_nha_value';
4201 xml_tab(l_counter).TagValue :=l_bh_nha_value; --csr_cat.bh_nha_value;
4202 l_counter:=l_counter+1;
4203
4204 xml_tab(l_counter).TagName :='bh_nho_value';
4205 xml_tab(l_counter).TagValue :=l_bh_nho_value; --csr_cat.bh_nho_value;
4206 l_counter:=l_counter+1;
4207
4208 xml_tab(l_counter).TagName :='bh_retroactive_pay';
4209 xml_tab(l_counter).TagValue :=l_bh_retroactive_pay; --csr_cat.bh_retroactive_pay;
4210 l_counter:=l_counter+1;
4211
4212 xml_tab(l_counter).TagName :='bh_ppo_value';
4213 xml_tab(l_counter).TagValue :=l_bh_ppo_value; --csr_cat.bh_ppo_value;
4214 l_counter:=l_counter+1;
4215
4216 xml_tab(l_counter).TagName :='bh_sick_pay';
4217 xml_tab(l_counter).TagValue :=l_bh_sick_pay; --csr_cat.bh_sick_pay;
4218 l_counter:=l_counter+1;
4219
4220 xml_tab(l_counter).TagName :='bh_total_employees';
4221 xml_tab(l_counter).TagValue :=l_bh_total_employees; --csr_cat.bh_total_employees;
4222 l_counter:=l_counter+1;
4223
4224 OPEN csr_blue_salary(p_payroll_action_id, l_local_unit_id,l_action_information_id);
4225 FETCH csr_blue_salary INTO l_bs_gross_pay,l_bs_working_agreement,l_bs_tcdp_value,
4226 l_bs_tcow_value,l_bs_nha_value,l_bs_nho_value,l_bs_retroactive_pay,l_bs_ppo_value,
4227 l_bs_sick_pay,l_bs_total_employees;
4228 CLOSE csr_blue_salary;
4229
4230 xml_tab(l_counter).TagName :='bs_gross_pay';
4231 xml_tab(l_counter).TagValue :=l_bs_gross_pay; --csr_cat.bs_gross_pay;
4232 l_counter:=l_counter+1;
4233
4234 xml_tab(l_counter).TagName :='bs_working_agreement';
4235 xml_tab(l_counter).TagValue :=l_bs_working_agreement; --csr_cat.bs_working_agreement;
4236 l_counter:=l_counter+1;
4237
4238 xml_tab(l_counter).TagName :='bs_tcdp_value';
4239 xml_tab(l_counter).TagValue :=l_bs_tcdp_value; --csr_cat.bs_tcdp_value;
4240 l_counter:=l_counter+1;
4241
4242 xml_tab(l_counter).TagName :='bs_tcow_value';
4243 xml_tab(l_counter).TagValue :=l_bs_tcow_value; --csr_cat.bs_tcow_value;
4244 l_counter:=l_counter+1;
4245
4246 xml_tab(l_counter).TagName :='bs_nha_value';
4247 xml_tab(l_counter).TagValue :=l_bs_nha_value; --csr_cat.bs_nha_value;
4248 l_counter:=l_counter+1;
4249
4250 xml_tab(l_counter).TagName :='bs_nho_value';
4251 xml_tab(l_counter).TagValue :=l_bs_nho_value; --csr_cat.bs_nho_value;
4252 l_counter:=l_counter+1;
4253
4254 xml_tab(l_counter).TagName :='bs_retroactive_pay';
4255 xml_tab(l_counter).TagValue :=l_bs_retroactive_pay; --csr_cat.bs_retroactive_pay;
4256 l_counter:=l_counter+1;
4257
4258 xml_tab(l_counter).TagName :='bs_ppo_value';
4259 xml_tab(l_counter).TagValue :=l_bs_ppo_value; --csr_cat.bs_ppo_value;
4260 l_counter:=l_counter+1;
4261
4262 xml_tab(l_counter).TagName :='bs_sick_pay';
4263 xml_tab(l_counter).TagValue :=l_bs_sick_pay; --csr_cat.bs_sick_pay;
4264 l_counter:=l_counter+1;
4265
4266 xml_tab(l_counter).TagName :='bs_total_employees';
4267 xml_tab(l_counter).TagValue :=l_bs_total_employees; --csr_cat.bs_total_employees;
4268 l_counter:=l_counter+1;
4269
4270 OPEN csr_white_salary(p_payroll_action_id, l_local_unit_id,l_action_information_id);
4271 FETCH csr_white_salary INTO l_ws_full_time_employee,l_ws_gross_pay,l_ws_working_agreement,
4272 l_ws_tcdp_value,l_ws_tcow_value,l_ws_nha_value,l_ws_nho_value,l_ws_retroactive_pay,
4273 l_ws_ppo_value,l_ws_sick_pay,l_ws_total_employees;
4274 CLOSE csr_white_salary;
4275
4276 xml_tab(l_counter).TagName :='ws_full_time_employee';
4277 xml_tab(l_counter).TagValue :=l_ws_full_time_employee; --round(csr_cat.ws_full_time_employee,2);
4278 l_counter:=l_counter+1;
4279
4280 xml_tab(l_counter).TagName :='ws_gross_pay';
4281 xml_tab(l_counter).TagValue :=l_ws_gross_pay; --csr_cat.ws_gross_pay;
4282 l_counter:=l_counter+1;
4283
4284 xml_tab(l_counter).TagName :='ws_working_agreement';
4285 xml_tab(l_counter).TagValue :=l_ws_working_agreement; --csr_cat.ws_working_agreement;
4286 l_counter:=l_counter+1;
4287
4288 xml_tab(l_counter).TagName :='ws_tcdp_value';
4289 xml_tab(l_counter).TagValue :=l_ws_tcdp_value; --csr_cat.ws_tcdp_value;
4290 l_counter:=l_counter+1;
4291
4292 xml_tab(l_counter).TagName :='ws_tcow_value';
4293 xml_tab(l_counter).TagValue :=l_ws_tcow_value; --csr_cat.ws_tcow_value;
4294 l_counter:=l_counter+1;
4295
4296 xml_tab(l_counter).TagName :='ws_nha_value';
4297 xml_tab(l_counter).TagValue :=l_ws_nha_value; --csr_cat.ws_nha_value;
4298 l_counter:=l_counter+1;
4299
4300 xml_tab(l_counter).TagName :='ws_nho_value';
4301 xml_tab(l_counter).TagValue :=l_ws_nho_value; --csr_cat.ws_nho_value;
4302 l_counter:=l_counter+1;
4303
4304 xml_tab(l_counter).TagName :='ws_retroactive_pay';
4305 xml_tab(l_counter).TagValue :=l_ws_retroactive_pay; --csr_cat.ws_retroactive_pay;
4306 l_counter:=l_counter+1;
4307
4308 xml_tab(l_counter).TagName :='ws_ppo_value';
4309 xml_tab(l_counter).TagValue :=l_ws_ppo_value; --csr_cat.ws_ppo_value;
4310 l_counter:=l_counter+1;
4311
4312 xml_tab(l_counter).TagName :='ws_sick_pay';
4313 xml_tab(l_counter).TagValue :=l_ws_sick_pay; --csr_cat.ws_sick_pay;
4314 l_counter:=l_counter+1;
4315
4316 xml_tab(l_counter).TagName :='ws_total_employees';
4317 xml_tab(l_counter).TagValue :=l_ws_total_employees; --csr_cat.ws_total_employees;
4318 l_counter:=l_counter+1;
4319
4320 OPEN csr_white_hour(p_payroll_action_id, l_local_unit_id,l_action_information_id);
4321 FETCH csr_white_hour INTO l_wh_full_time_employee,l_wh_gross_pay,l_wh_working_agreement,l_wh_tcdp_value,
4322 l_wh_retroactive_pay,l_wh_ppo_value,l_wh_sick_pay,l_wh_total_employees;
4323 CLOSE csr_white_hour;
4324
4325 xml_tab(l_counter).TagName :='wh_full_time_employee';
4326 xml_tab(l_counter).TagValue :=l_wh_full_time_employee; --round(csr_cat.wh_full_time_employee,2);
4327 l_counter:=l_counter+1;
4328
4329 xml_tab(l_counter).TagName :='wh_gross_pay';
4330 xml_tab(l_counter).TagValue :=l_wh_gross_pay; --csr_cat.wh_gross_pay;
4331 l_counter:=l_counter+1;
4332
4333 xml_tab(l_counter).TagName :='wh_working_agreement';
4334 xml_tab(l_counter).TagValue :=l_wh_working_agreement; --csr_cat.wh_working_agreement;
4335 l_counter:=l_counter+1;
4336
4337 xml_tab(l_counter).TagName :='wh_tcdp_value';
4338 xml_tab(l_counter).TagValue :=l_wh_tcdp_value; --csr_cat.wh_tcdp_value;
4339 l_counter:=l_counter+1;
4340
4341 xml_tab(l_counter).TagName :='wh_retroactive_pay';
4342 xml_tab(l_counter).TagValue :=l_wh_retroactive_pay; --csr_cat.wh_retroactive_pay;
4343 l_counter:=l_counter+1;
4344
4345 xml_tab(l_counter).TagName :='wh_ppo_value';
4346 xml_tab(l_counter).TagValue :=l_wh_ppo_value; --csr_cat.wh_ppo_value;
4347 l_counter:=l_counter+1;
4348
4349 xml_tab(l_counter).TagName :='wh_sick_pay';
4350 xml_tab(l_counter).TagValue :=l_wh_sick_pay; --csr_cat.wh_sick_pay;
4351 l_counter:=l_counter+1;
4352
4353 xml_tab(l_counter).TagName :='wh_total_employees';
4354 xml_tab(l_counter).TagValue :=l_wh_total_employees; --csr_cat.wh_total_employees;
4355 l_counter:=l_counter+1;
4356
4357 xml_tab(l_counter).TagName :='CAT_DETAILS';
4358 xml_tab(l_counter).TagValue :='END_CAT_DETAILS';
4359 l_counter := l_counter + 1;
4360
4361
4362 l_legal_employer:=NULL;
4363 l_local_unit:=NULL;
4364 l_month:=NULL;
4365 l_year:=NULL;
4366 l_retroactive_date_from:=NULL;
4367 l_retroactive_date_to:=NULL;
4368 l_bh_worked_calendar_month:=NULL;
4369 l_bh_worked_payment_period:=NULL;
4370 l_bh_pbt_value:=NULL;
4371 l_bh_pcow_value:=NULL;
4372 l_bh_nha_value:=NULL;
4373 l_bh_nho_value:=NULL;
4374 l_bh_retroactive_pay:=NULL;
4375 l_bh_ppo_value:=NULL;
4376 l_bh_sick_pay:=NULL;
4377 l_bh_total_employees:=NULL;
4378 l_bs_gross_pay:=NULL;
4379 l_bs_working_agreement:=NULL;
4380 l_bs_tcdp_value:=NULL;
4381 l_bs_tcow_value:=NULL;
4382 l_bs_nha_value:=NULL;
4383 l_bs_nho_value:=NULL;
4384 l_bs_retroactive_pay:=NULL;
4385 l_bs_ppo_value:=NULL;
4386 l_bs_sick_pay:=NULL;
4387 l_bs_total_employees:=NULL;
4388 l_ws_full_time_employee:=NULL;
4389 l_ws_gross_pay:=NULL;
4390 l_ws_working_agreement:=NULL;
4391 l_ws_tcdp_value:=NULL;
4392 l_ws_tcow_value:=NULL;
4393 l_ws_nha_value:=NULL;
4394 l_ws_nho_value:=NULL;
4395 l_ws_retroactive_pay:=NULL;
4396 l_ws_ppo_value:=NULL;
4397 l_ws_sick_pay:=NULL;
4398 l_ws_total_employees:=NULL;
4399 l_wh_full_time_employee:=NULL;
4400 l_wh_gross_pay:=NULL;
4401 l_wh_working_agreement:=NULL;
4402 l_wh_tcdp_value:=NULL;
4403 l_wh_retroactive_pay:=NULL;
4404 l_wh_ppo_value:=NULL;
4405 l_wh_sick_pay:=NULL;
4406 l_wh_total_employees:=NULL;
4407 l_action_information_id:=NULL;
4408 --END LOOP;
4409 END LOOP;
4410
4411
4412 -- INSERT INTO raaj VALUES (p_xml);
4413 WritetoCLOB (p_xml );
4414
4415
4416
4417 END POPULATE_DATA_DETAIL;
4418
4419 END PAY_SE_WAGES_SALARIES;
4420