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