[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_ARCHIVE_CWCA
Source
1 PACKAGE BODY PAY_SE_ARCHIVE_CWCA AS
2 /* $Header: pysecwca.pkb 120.0.12010000.2 2008/08/06 08:17:14 ubhat ship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 g_index NUMBER := -1;
6 g_index_assact NUMBER := -1;
7 g_index_bal NUMBER := -1;
8 g_package VARCHAR2 (240) := 'PAY_SE_ARCHIVE_CWCA.';
9 g_payroll_action_id NUMBER;
10 g_arc_payroll_action_id NUMBER;
11
12 -- Globals to pick up all th parameter
13 g_business_group_id NUMBER;
14 g_effective_date DATE;
15 g_person_id NUMBER;
16 g_assignment_id NUMBER;
17 g_still_employed VARCHAR2 (10);
18 g_report_start_year VARCHAR2 (10);
19 g_report_start_month VARCHAR2 (10);
20
21 --End of Globals to pick up all the parameter
22 g_format_mask VARCHAR2 (50);
23 g_err_num NUMBER;
24 g_errm VARCHAR2 (150);
25
26 /* GET PARAMETER */
27 FUNCTION get_parameter (
28 p_parameter_string IN VARCHAR2
29 ,p_token IN VARCHAR2
30 ,p_segment_number IN NUMBER DEFAULT NULL
31 )
32 RETURN VARCHAR2
33 IS
34 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
35 l_start_pos NUMBER;
36 l_delimiter VARCHAR2 (1) := ' ';
37 l_proc VARCHAR2 (240) := g_package || ' get parameter ';
38 BEGIN
39 --
40 IF g_debug
41 THEN
42 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
43 END IF;
44
45 l_start_pos :=
46 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
47
48 --
49 IF l_start_pos = 0
50 THEN
51 l_delimiter := '|';
52 l_start_pos :=
53 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
54 END IF;
55
56 IF l_start_pos <> 0
57 THEN
58 l_start_pos := l_start_pos + LENGTH (p_token || '=');
59 l_parameter :=
60 SUBSTR (p_parameter_string
61 ,l_start_pos
62 , INSTR (p_parameter_string || ' '
63 ,l_delimiter
64 ,l_start_pos
65 )
66 - (l_start_pos)
67 );
68
69 IF p_segment_number IS NOT NULL
70 THEN
71 l_parameter := ':' || l_parameter || ':';
72 l_parameter :=
73 SUBSTR (l_parameter
74 , INSTR (l_parameter, ':', 1, p_segment_number) + 1
75 , INSTR (l_parameter, ':', 1, p_segment_number + 1)
76 - 1
77 - INSTR (l_parameter, ':', 1, p_segment_number)
78 );
79 END IF;
80 END IF;
81
82 --
83 IF g_debug
84 THEN
85 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
86 END IF;
87
88 RETURN l_parameter;
89 END;
90
91 /* GET ALL PARAMETERS */
92 PROCEDURE get_all_parameters (
93 p_payroll_action_id IN NUMBER -- In parameter
94 ,p_business_group_id OUT NOCOPY NUMBER -- Core parameter
95 ,p_effective_date OUT NOCOPY DATE -- Core parameter
96 ,p_person_id OUT NOCOPY NUMBER -- User parameter
97 ,p_assignment_id OUT NOCOPY VARCHAR2 -- User parameter
98 ,p_still_employed OUT NOCOPY VARCHAR2 -- User parameter
99 ,p_report_start_year OUT NOCOPY VARCHAR2 -- User parameter
100 ,p_report_start_month OUT NOCOPY VARCHAR2 -- User parameter
101 )
102 IS
103 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
104 IS
105 SELECT (PAY_SE_ARCHIVE_CWCA.get_parameter
106 (legislative_parameters
107 ,'PERSON_ID'
108 )
109 ) person_id
110 , (PAY_SE_ARCHIVE_CWCA.get_parameter
111 (legislative_parameters
112 ,'ASSIGNMENT_ID'
113 )
114 ) assignment_id
115 , (PAY_SE_ARCHIVE_CWCA.get_parameter
116 (legislative_parameters
117 ,'STILL_EMPLOYED'
118 )
119 ) still_employed
120 , (PAY_SE_ARCHIVE_CWCA.get_parameter
121 (legislative_parameters
122 ,'REPORT_YEAR'
123 )
124 ) report_year
125 , (PAY_SE_ARCHIVE_CWCA.get_parameter
126 (legislative_parameters
127 ,'REPORT_MONTH'
128 )
129 ) report_month
130 ,effective_date effective_date
131 ,business_group_id bg_id
132 FROM pay_payroll_actions
133 WHERE payroll_action_id = p_payroll_action_id;
134
135 lr_parameter_info csr_parameter_info%ROWTYPE;
136 l_proc VARCHAR2 (240)
137 := g_package || ' GET_ALL_PARAMETERS ';
138 BEGIN
139 --logger ('Entering ', l_proc);
140 --logger ('p_payroll_action_id ', p_payroll_action_id);
141
142 OPEN csr_parameter_info (p_payroll_action_id);
143
144 --FETCH csr_parameter_info into lr_parameter_info;
145 FETCH csr_parameter_info
146 INTO lr_parameter_info;
147
148 CLOSE csr_parameter_info;
149
150 fnd_file.put_line (fnd_file.LOG
151 , 'lr_parameter_info.STILL_EMPLOYED '
152 || lr_parameter_info.still_employed
153 );
154 --logger ('Entering ', l_proc);
155 p_person_id := lr_parameter_info.person_id;
156 --logger ('lr_parameter_info.PERSON_ID ', lr_parameter_info.person_id);
157 p_assignment_id := lr_parameter_info.assignment_id;
158 --logger ('lr_parameter_info.ASSIGNMENT_ID ' ,lr_parameter_info.assignment_id );
159 p_still_employed := lr_parameter_info.still_employed;
160 --logger ('lr_parameter_info.still_employed ' ,lr_parameter_info.still_employed );
161 p_report_start_year := lr_parameter_info.report_year;
162 --logger ('lr_parameter_info.report_year ' ,lr_parameter_info.report_year );
163 p_report_start_month := lr_parameter_info.report_month;
164 --logger ('lr_parameter_info.report_month ' ,lr_parameter_info.report_month );
165 p_effective_date := lr_parameter_info.effective_date;
166 --logger ('lr_parameter_info.effective_date ' ,lr_parameter_info.effective_date );
167 p_business_group_id := lr_parameter_info.bg_id;
168 --logger ('lr_parameter_info.bg_id ', lr_parameter_info.bg_id);
169 --logger ('LEAVING ', l_proc);
170
171 IF g_debug
172 THEN
173 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS'
174 ,30);
175 END IF;
176 END get_all_parameters;
177
178 -- *****************************************************************************
179 /* RANGE CODE */
180 -- *****************************************************************************
181 PROCEDURE range_code (
182 p_payroll_action_id IN NUMBER
183 ,p_sql OUT NOCOPY VARCHAR2
184 )
185 IS
186 l_action_info_id NUMBER;
187 l_ovn NUMBER;
188 l_business_group_id NUMBER;
189 l_start_date VARCHAR2 (30);
190 l_end_date VARCHAR2 (30);
191 l_assignment_id NUMBER;
192 -- *****************************************************************************
193 -- Variable Required
194 l_person_number VARCHAR2 (100);
195 l_last_name per_all_people_f.last_name%TYPE;
196 l_first_name per_all_people_f.first_name%TYPE;
197
198 l_local_unit_id NUMBER;
199 l_legal_employer_name VARCHAR2 (100);
200 l_org_number VARCHAR2 (100);
201 l_location_id VARCHAR2 (100);
202 l_phone_number VARCHAR2 (100);
203 l_location_code VARCHAR2 (100);
204 l_address_line_1 VARCHAR2 (100);
205 l_address_line_2 VARCHAR2 (100);
206 l_address_line_3 VARCHAR2 (100);
207 l_postal_code VARCHAR2 (100);
208 l_town_or_city VARCHAR2 (100);
209 l_region_1 VARCHAR2 (100);
210 l_region_2 VARCHAR2 (100);
211 l_territory_short_name VARCHAR2 (100);
212 l_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE;
213 l_oth_comp NUMBER;
214 bname VARCHAR2 (100);
215 l_month VARCHAR2 (2);
216 l_year VARCHAR2 (4);
217 l_reporting_date DATE;
218 l_tot_addl_time_hw NUMBER;
219 l_tot_relief_duty_hours NUMBER;
220 l_tot_relief_duty_hw NUMBER;
221 l_tot_overtime_hours NUMBER;
222 l_tot_overtime_hw NUMBER;
223 l_tot_addl_time_hours NUMBER;
224 l_addl_time_hw NUMBER;
225 l_relief_duty_hours NUMBER;
226 l_relief_duty_hw NUMBER;
227 l_overtime_hours NUMBER;
228 l_overtime_hw NUMBER;
229 l_addl_time_hours NUMBER;
230 l_legal_employer_id NUMBER;
231 l_dimension VARCHAR2 (100);
232 l_report_start_date DATE;
233 l_report_end_date DATE;
234 l_asg_effective_start_date DATE;
235 l_asg_effective_end_date DATE;
236
237
238 l_get_defined_balance_id NUMBER;
239 l_count NUMBER;
240 l_hourly_pay_variable VARCHAR(4); -- EOY 2008
241 l_other_tax_compensation VARCHAR(4); -- EOY 2008
242 l_overtime_mw NUMBER; -- EOY 2008
243 l_addl_time_mw NUMBER; -- EOY 2008
244 l_relief_duty_mw NUMBER; -- EOY 2008
245 l_tot_relief_duty_mw NUMBER; -- EOY 2008
246 count_months NUMBER; -- EOY 2008
247 l_days_worked VARCHAR(10); -- EOY 2008
248 l_artistic_work VARCHAR(10); -- EOY 2008
249
250 -- *****************************************************************************
251 -- CURSOR
252
253 /* Cursor to retrieve Balance Types having a particular Balance Category */
254
255 CURSOR csr_asg_effective_date (
256 p_asg_id number,
257 p_end_date date,
258 p_start_date date,
259 p_business_group_id number
260 ) IS
261 SELECT MAX (effective_end_date) effective_date
262 FROM per_all_assignments_f paa
263 WHERE assignment_id = p_asg_id
264 AND paa.effective_start_date <= p_end_date
265 AND paa.effective_end_date > = p_start_date
266 AND assignment_status_type_id
267 IN
268 (SELECT assignment_status_type_id
269 FROM per_assignment_status_types
270 WHERE per_system_status = 'ACTIVE_ASSIGN'
271 AND active_flag = 'Y'
272 AND ((legislation_code is null and business_group_id is null)
273 OR (business_group_id = p_business_group_id)
274 ));
275
276 lr_asg_effective_date csr_asg_effective_date%ROWTYPE;
277
278 CURSOR csr_balance
279 (p_balance_category_name VARCHAR2
280 ,p_business_group_id NUMBER)
281 IS
282 SELECT REPLACE(UPPER(pbt.balance_name),' ' ,'_') balance_name , pbt.balance_name bname
283 FROM pay_balance_types pbt , pay_balance_categories_f pbc
284 WHERE pbc.legislation_code='SE'
285 AND pbt.business_group_id =p_business_group_id
286 AND pbt.balance_category_id = pbc.balance_category_id
287 AND pbc.category_name = p_balance_category_name ;
288
289
290 /* Cursor to retrieve Defined Balance Id */
291 Cursor csr_bg_get_defined_balance_id
292 (csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE
293 ,p_business_group_id NUMBER)
294 IS
295 SELECT ue.creator_id
296 FROM ff_user_entities ue,
297 ff_database_items di
298 WHERE di.user_name = csr_v_Balance_Name
299 AND ue.user_entity_id = di.user_entity_id
300 AND ue.legislation_code is NULL
301 AND ue.business_group_id = p_business_group_id
302 AND ue.creator_type = 'B';
303
304 rg_csr_bg_get_defined_bal_id csr_bg_get_defined_balance_id%rowtype;
305
306 CURSOR csr_address_details (
307 csr_v_location_id hr_locations.location_id%TYPE
308 )
309 IS
310 SELECT hl.location_code
311 ,hl.description
312 ,hl.address_line_1
313 ,hl.address_line_2
314 ,hl.address_line_3
315 ,hl.postal_code
316 ,hl.town_or_city
317 ,hl.region_1
318 ,hl.region_2
319 ,ft.territory_short_name
320 FROM hr_organization_units hou
321 ,hr_locations hl
322 ,fnd_territories_vl ft
323 WHERE hl.location_id = csr_v_location_id
324 AND hl.country = ft.territory_code;
325
326 lr_address_details csr_address_details%ROWTYPE;
327
328 CURSOR csr_legal_employer_details (
329 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
330 )
331 IS
332 SELECT o.NAME
333 ,hoi3.org_information2 "ORG_NUMBER"
334 ,o.location_id
335 ,o.organization_id
336 FROM hr_all_organization_units o
337 ,hr_organization_information hoi1
338 ,hr_organization_information hoi2
339 ,hr_organization_information hoi3
340 WHERE o.business_group_id = g_business_group_id
341 AND hoi1.organization_id = o.organization_id
342 AND hoi1.org_information_context = 'CLASS'
343 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
344 AND hoi2.organization_id = hoi1.organization_id
345 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
346 AND hoi2.org_information1 = csr_v_local_unit_id
347 AND o.organization_id = hoi3.organization_id
348 AND hoi3.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
349
350 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
351
352 CURSOR csr_contact_details (
353 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
354 )
355 IS
356 SELECT hoi4.org_information3
357 FROM hr_organization_information hoi4
358 WHERE hoi4.organization_id = csr_v_legal_employer_id
359 AND hoi4.org_information_context = 'SE_ORG_CONTACT_DETAILS'
360 AND hoi4.org_information_id =
361 (SELECT MIN (org_information_id)
362 FROM hr_organization_information
363 WHERE organization_id = csr_v_legal_employer_id
364 AND org_information_context = 'SE_ORG_CONTACT_DETAILS'
365 AND org_information1 = 'PHONE');
366
367 lr_contact_details csr_contact_details%ROWTYPE;
368
369 CURSOR csr_person_info (
370 csr_v_person_id per_all_people_f.person_id%TYPE
371 ,csr_v_effective_date per_all_people_f.effective_start_date%TYPE
372 )
373 IS
374 SELECT *
375 FROM per_all_people_f p
376 WHERE p.business_group_id = g_business_group_id
377 AND p.person_id = csr_v_person_id
378 AND csr_v_effective_date BETWEEN p.effective_start_date
379 AND p.effective_end_date;
380
381 lr_person_info csr_person_info%ROWTYPE;
382
383 CURSOR csr_assignment_info (
384 csr_v_person_id per_all_people_f.person_id%TYPE
385 ,csr_v_assignment_id per_all_assignments_f.person_id%TYPE
386 ,csr_v_effective_date per_all_assignments_f.effective_start_date%TYPE
387 )
388 IS
389 SELECT *
390 FROM per_all_assignments_f p
391 WHERE p.business_group_id = g_business_group_id
392 AND p.assignment_id = csr_v_assignment_id
393 AND p.person_id = csr_v_person_id
394 AND csr_v_effective_date BETWEEN p.effective_start_date
395 AND p.effective_end_date;
396
397 lr_assignment_info csr_assignment_info%ROWTYPE;
398
399
400 CURSOR csr_soft_coded_keyflex_info (
401 csr_v_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE
402 )
403 IS
404 SELECT *
405 FROM hr_soft_coding_keyflex
406 WHERE soft_coding_keyflex_id = csr_v_soft_coding_keyflex_id;
407
408 lr_soft_coded_keyflex_info csr_soft_coded_keyflex_info%ROWTYPE;
409
410
411
412 -- Cursor to extract the hourly pay variables in the Completion Working report
413
414
415 CURSOR csr_extra_assignment_info (
416 csr_v_assignment_id per_all_assignments_f.person_id%TYPE
417 ,csr_v_information_type per_assignment_extra_info.information_type%TYPE
418 )
419 IS
420 SELECT *
421 FROM per_assignment_extra_info
422 WHERE assignment_id = csr_v_assignment_id
423 AND information_type = csr_v_information_type;
424
425 lr_extra_assignment_info csr_extra_assignment_info%ROWTYPE;
426
427
428 CURSOR csr_se_wtc_time_worked_info (
429 csr_v_assignment_id per_all_assignments_f.person_id%TYPE
430 ,csr_v_year per_assignment_extra_info.aei_information2%TYPE
431 ,csr_v_month per_assignment_extra_info.aei_information3%TYPE
432 )
433 IS
434 SELECT *
435 FROM per_assignment_extra_info
436 WHERE assignment_id = csr_v_assignment_id
437 AND information_type = 'SE_WTC_TIME_WORKED_INFO'
438 AND aei_information1 = csr_v_year
439 AND aei_information2 = csr_v_month;
440
441 lr_se_wtc_time_worked_info csr_se_wtc_time_worked_info%ROWTYPE;
442 -- Archiving the data , as this will fire once
443 -- *****************************************************************************
444 -- *****************************************************************************
445 BEGIN
446
447
448 -- *****************************************************************************
449 IF g_debug THEN
450 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
451 END IF;
452
453 -- *****************************************************************************
454
455
456 g_payroll_action_id := p_payroll_action_id;
457 g_business_group_id := NULL;
458 g_effective_date := NULL;
459 g_person_id := NULL;
460 g_assignment_id := NULL;
461
462 PAY_SE_ARCHIVE_CWCA.get_all_parameters (p_payroll_action_id
463 ,g_business_group_id
464 ,g_effective_date
465 ,g_person_id
466 ,g_assignment_id
467 ,g_still_employed
468 ,g_report_start_year
469 ,g_report_start_month
470 );
471 p_sql :=
472 'SELECT DISTINCT person_id
473 FROM per_people_f ppf
474 ,pay_payroll_actions ppa
475 WHERE ppa.payroll_action_id = :payroll_action_id
476 AND ppa.business_group_id = ppf.business_group_id
477 AND ppf.person_id = '''
478 || g_person_id
479 || '''
480 ORDER BY ppf.person_id';
481
482
483 -- *****************************************************************************
484 --START OF PICKING UP DATA
485
486 l_report_start_date := TO_DATE('01/'||g_report_start_month||'/'||g_report_start_year,'DD/MM/YYYY');
487
488 fnd_file.put_line (fnd_file.LOG, 'g_assignment_id'||g_assignment_id);
489 fnd_file.put_line (fnd_file.LOG, 'l_report_end_date'||to_char(l_report_end_date));
490 fnd_file.put_line (fnd_file.LOG, 'l_report_start_date'||to_char(l_report_start_date));
491 fnd_file.put_line (fnd_file.LOG, 'g_business_group_id'||to_char(g_business_group_id));
492
493
494 OPEN csr_asg_effective_date ( g_assignment_id, g_effective_date , l_report_start_date, g_business_group_id);
495 FETCH csr_asg_effective_date INTO lr_asg_effective_date;
496 CLOSE csr_asg_effective_date;
497
498 l_asg_effective_end_date := lr_asg_effective_date.effective_date;
499
500 IF l_asg_effective_end_date <= l_report_end_date THEN
501
502 SELECT LAST_DAY(l_asg_effective_end_date)
503 INTO l_report_end_date
504 FROM DUAL;
505 ELSE
506
507 SELECT LAST_DAY(g_effective_date)
508 INTO l_report_end_date
509 FROM DUAL;
510
511 END IF;
512
513 fnd_file.put_line (fnd_file.LOG, 'l_report_end_date'||to_char(l_report_end_date));
514
515 IF lr_asg_effective_date.effective_date IS NOT NULL THEN
516
517
518 l_tot_addl_time_hw :=0;
519 l_tot_relief_duty_hours :=0;
520 l_tot_relief_duty_hw :=0;
521 l_tot_overtime_hours :=0;
522 l_tot_overtime_hw :=0;
523 l_tot_addl_time_hours :=0;
524 l_addl_time_hw :=0;
525 l_relief_duty_hours :=0;
526 l_relief_duty_hw :=0;
527 l_overtime_hours :=0;
528 l_overtime_hw :=0;
529 l_addl_time_hours :=0;
530 l_relief_duty_mw :=0;
531 l_tot_relief_duty_mw :=0;
532
533
534
535 -- Insert the report Parameters
536 pay_action_information_api.create_action_information
537 (p_action_information_id => l_action_info_id
538 ,p_action_context_id => p_payroll_action_id
539 ,p_action_context_type => 'PA'
540 ,p_object_version_number => l_ovn
541 ,p_effective_date => g_effective_date
542 ,p_source_id => NULL
543 ,p_source_text => NULL
544 ,p_action_information_category => 'EMEA REPORT DETAILS'
545 ,p_action_information1 => 'PYSECWCA'
546 ,p_action_information2 => g_person_id
547 ,p_action_information3 => g_assignment_id
548 ,p_action_information4 => g_still_employed
549 ,p_action_information5 => g_business_group_id
550 ,p_action_information6 => g_report_start_year
551 ,p_action_information7 => g_report_start_month
552 ,p_action_information8 => NULL
553 ,p_action_information9 => NULL
554 ,p_action_information10 => NULL
555 );
556
557
558 OPEN csr_person_info (g_person_id, lr_asg_effective_date.effective_date);
559 FETCH csr_person_info INTO lr_person_info;
560 CLOSE csr_person_info;
561
562 l_person_number := lr_person_info.national_identifier;
563 l_last_name := lr_person_info.last_name;
564 l_first_name := lr_person_info.first_name;
565
566 OPEN csr_assignment_info (g_person_id, g_assignment_id, lr_asg_effective_date.effective_date);
567 FETCH csr_assignment_info INTO lr_assignment_info;
568 CLOSE csr_assignment_info;
569 l_soft_coding_keyflex_id := lr_assignment_info.soft_coding_keyflex_id;
570
571 l_asg_effective_start_date := lr_assignment_info.effective_start_date;
572
573 -- *****************************************************************************
574 -- SOFT CODED FLEX
575 OPEN csr_soft_coded_keyflex_info (l_soft_coding_keyflex_id);
576 FETCH csr_soft_coded_keyflex_info INTO lr_soft_coded_keyflex_info;
577 CLOSE csr_soft_coded_keyflex_info;
578
579 l_local_unit_id := lr_soft_coded_keyflex_info.segment2;
580 -- *****************************************************************************
581 -- *****************************************************************************
582 -- Legal Employer Details
583 OPEN csr_legal_employer_details (l_local_unit_id);
584 FETCH csr_legal_employer_details INTO lr_legal_employer_details;
585 CLOSE csr_legal_employer_details;
586
587 l_legal_employer_name := lr_legal_employer_details.NAME;
588 l_org_number := lr_legal_employer_details.org_number;
589 l_location_id := lr_legal_employer_details.location_id;
590 l_legal_employer_id := lr_legal_employer_details.organization_id;
591
592 -- Employer and Signature
593 OPEN csr_contact_details (lr_legal_employer_details.organization_id);
594 FETCH csr_contact_details INTO lr_contact_details;
595 CLOSE csr_contact_details;
596
597 l_phone_number := lr_contact_details.org_information3;
598
599 OPEN csr_address_details (l_location_id);
600 FETCH csr_address_details INTO lr_address_details;
601 CLOSE csr_address_details;
602
603 l_location_code := lr_address_details.location_code;
604 l_address_line_1 := lr_address_details.address_line_1;
605 l_address_line_2 := lr_address_details.address_line_2;
606 l_address_line_3 := lr_address_details.address_line_3;
607 l_postal_code := lr_address_details.postal_code;
608 l_town_or_city := lr_address_details.town_or_city;
609 l_region_1 := lr_address_details.region_1;
610 l_region_2 := lr_address_details.region_2;
611 l_territory_short_name := lr_address_details.territory_short_name;
612
613 pay_action_information_api.create_action_information
614 (p_action_information_id => l_action_info_id
615 ,p_action_context_id => p_payroll_action_id
616 ,p_action_context_type => 'PA'
617 ,p_object_version_number => l_ovn
618 ,p_effective_date => g_effective_date
619 ,p_source_id => NULL
620 ,p_source_text => NULL
621 ,p_action_information_category => 'EMEA REPORT INFORMATION'
622 ,p_action_information1 => 'PYSECWCA'
623 ,p_action_information2 => 'CWC1'
624 ,p_action_information3 => l_person_number
625 ,p_action_information4 => l_last_name
626 ,p_action_information5 => l_first_name
627 ,p_action_information6 => l_legal_employer_name
628 ,p_action_information7 => l_org_number
629 ,p_action_information8 => l_location_code
630 ,p_action_information9 => l_address_line_1
631 ,p_action_information10 => l_address_line_2
632 ,p_action_information11 => l_address_line_3
633 ,p_action_information12 => l_postal_code
634 ,p_action_information13 => l_town_or_city
635 ,p_action_information14 => l_region_1
636 ,p_action_information15 => l_region_2
637 ,p_action_information16 => l_territory_short_name
638 ,p_action_information17 => l_phone_number
639 ,p_action_information18 => NULL
640 ,p_action_information19 => NULL
641 ,p_action_information20 => NULL
642 ,p_action_information21 => NULL
643 ,p_action_information22 => NULL
644 ,p_action_information23 => NULL
645 ,p_action_information24 => NULL
646 ,p_action_information25 => NULL
647 ,p_action_information26 => NULL
648 ,p_action_information27 => NULL
649 ,p_action_information28 => NULL
650 ,p_action_information29 => NULL
651 ,p_action_information30 => g_person_id
652 ,p_assignment_id => g_assignment_id
653 );
654
655 l_dimension:='_ASG_LE_MONTH';
656
657
658 /* Setting Context */
659 BEGIN
660 pay_balance_pkg.set_context('ASSIGNMENT_ID',g_assignment_id);
661 pay_balance_pkg.set_context('TAX_UNIT_ID',l_legal_employer_id);
662 END;
663
664
665 --- Check for Artistic Work
666
667 lr_extra_assignment_info := NULL;
668
669 OPEN csr_extra_assignment_info (g_assignment_id
670 ,'SE_WTC_TIME_WORKED_HEADER'
671 );
672
673 FETCH csr_extra_assignment_info
674 INTO lr_extra_assignment_info;
675
676 CLOSE csr_extra_assignment_info;
677
678 l_artistic_work := lr_extra_assignment_info.aei_information6;
679
680
681 --- Display only 26 months
682
683 count_months := 1;
684
685 WHILE l_report_start_date <= l_report_end_date AND count_months < 27
686
687 --WHILE l_report_start_date <= l_report_end_date
688
689 --FOR i IN 1..12
690 LOOP
691
692 --SELECT last_day(l_report_start_date), LPAD(TO_CHAR(l_report_start_date,'MM'), 2,'0'),TO_CHAR(l_report_start_date,'YYYY')
693 --INTO l_reporting_date, l_month, l_year
694 --FROM DUAL;
695
696 SELECT last_day(l_report_end_date), LPAD(TO_CHAR(l_report_end_date,'MM'), 2,'0'),TO_CHAR(l_report_end_date,'YYYY')
697 INTO l_reporting_date, l_month, l_year
698 FROM DUAL;
699
700 IF l_artistic_work = 'Y'
701 THEN
702 OPEN csr_se_wtc_time_worked_info (g_assignment_id,l_year,l_month);
703 FETCH csr_se_wtc_time_worked_info INTO lr_se_wtc_time_worked_info;
704 CLOSE csr_se_wtc_time_worked_info;
705 l_days_worked := lr_se_wtc_time_worked_info.aei_information3;
706 END IF;
707
708 -- fnd_file.put_line (fnd_file.LOG, 'l_days_worked'||l_days_worked);
709
710 BEGIN
711 l_addl_time_hours :=0;
712 l_tot_addl_time_hours:=0;
713 FOR balance_rec IN csr_balance('Additional Time - Hours' , g_business_group_id)
714 LOOP
715
716 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
717 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
718 CLOSE csr_bg_Get_Defined_Balance_Id;
719
720
721
722 IF csr_balance%FOUND THEN
723
724 l_addl_time_hours :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
725 l_tot_addl_time_hours := l_tot_addl_time_hours + nvl(l_addl_time_hours,0);
726 END IF;
727 END LOOP ;
728
729 EXCEPTION
730 WHEN others THEN
731 fnd_file.put_line (fnd_file.LOG, 'Error'||substr(sqlerrm,1,30));
732 null;
733 END;
734 l_addl_time_hw :=0;
735 l_tot_addl_time_hw :=0;
736 BEGIN
737 FOR balance_rec IN csr_balance('Additional Time - Hourly Wages' , g_business_group_id)
738 LOOP
739 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
740 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
741 CLOSE csr_bg_Get_Defined_Balance_Id;
742 IF csr_balance%FOUND THEN
743 l_addl_time_hw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
744 l_tot_addl_time_hw := l_tot_addl_time_hw + nvl(l_addl_time_hw,0);
745 END IF;
746 END LOOP ;
747
748 EXCEPTION
749 WHEN others THEN
750 null;
751 END;
752 l_relief_duty_hours :=0;
753 l_tot_relief_duty_hours :=0;
754 BEGIN
755 FOR balance_rec IN csr_balance('Relief/ Duty - Hours' , g_business_group_id)
756 LOOP
757 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
758 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
759 CLOSE csr_bg_Get_Defined_Balance_Id;
760 IF csr_balance%FOUND THEN
761 l_relief_duty_hours :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
762 l_tot_relief_duty_hours := l_tot_relief_duty_hours + nvl(l_relief_duty_hours,0);
763 END IF;
764 END LOOP ;
765
766 -- fnd_file.put_line (fnd_file.LOG, 'l_tot_relief_duty_hours'||l_tot_relief_duty_hours);
767
768 EXCEPTION
769 WHEN others THEN
770 null;
771 END;
772
773 l_relief_duty_hw :=0;
774 l_tot_relief_duty_hw :=0;
775 BEGIN
776 FOR balance_rec IN csr_balance('Relief/ Duty - Hourly Wages' , g_business_group_id)
777 LOOP
778 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
779 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
780 CLOSE csr_bg_Get_Defined_Balance_Id;
781 IF csr_balance%FOUND THEN
782 l_relief_duty_hw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
783 l_tot_relief_duty_hw := l_tot_relief_duty_hw + nvl(l_relief_duty_hw,0);
784 END IF;
785 END LOOP ;
786
787 EXCEPTION
788 WHEN others THEN
789 null;
790 END;
791
792
793 l_relief_duty_mw :=0;
794 l_tot_relief_duty_mw :=0;
795 BEGIN
796 FOR balance_rec IN csr_balance('Relief/ Duty - Monthly Wages' , g_business_group_id)
797 LOOP
798 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
799 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
800 CLOSE csr_bg_Get_Defined_Balance_Id;
801 IF csr_balance%FOUND THEN
802 l_relief_duty_mw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
803 l_tot_relief_duty_mw := l_tot_relief_duty_mw + nvl(l_relief_duty_mw,0);
804 END IF;
805 END LOOP ;
806
807 EXCEPTION
808 WHEN others THEN
809 null;
810 END;
811
812 l_overtime_hours :=0;
813 l_tot_overtime_hours :=0;
814 BEGIN
815 FOR balance_rec IN csr_balance('Overtime - Hours' , g_business_group_id)
816 LOOP
817 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
818 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
819 CLOSE csr_bg_Get_Defined_Balance_Id;
820 IF csr_balance%FOUND THEN
821 l_overtime_hours :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
822 l_tot_overtime_hours := l_tot_overtime_hours + nvl(l_overtime_hours,0);
823 END IF;
824 END LOOP ;
825
826 EXCEPTION
827 WHEN others THEN
828 null;
829 END;
830 l_overtime_hw :=0;
831 l_tot_overtime_hw:=0;
832 BEGIN
833 FOR balance_rec IN csr_balance('Overtime - Hourly Wages' , g_business_group_id)
834 LOOP
835 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
836 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
837 CLOSE csr_bg_Get_Defined_Balance_Id;
838 IF csr_balance%FOUND THEN
839 l_overtime_hw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
840 l_tot_overtime_hw := l_tot_overtime_hw + nvl(l_overtime_hw,0);
841 END IF;
842 END LOOP ;
843
844 EXCEPTION
845 WHEN others THEN
846 null;
847 END;
848
849 pay_action_information_api.create_action_information
850 (p_action_information_id => l_action_info_id
851 ,p_action_context_id => p_payroll_action_id
852 ,p_action_context_type => 'PA'
853 ,p_object_version_number => l_ovn
854 ,p_effective_date => g_effective_date
855 ,p_source_id => NULL
856 ,p_source_text => NULL
857 ,p_action_information_category => 'EMEA REPORT INFORMATION'
858 ,p_action_information1 => 'PYSECWCA'
859 ,p_action_information2 => 'CWC2'
860 ,p_action_information3 => l_month
861 ,p_action_information4 => l_tot_addl_time_hw
862 ,p_action_information5 => l_tot_relief_duty_hours
863 ,p_action_information6 => l_tot_relief_duty_hw
864 ,p_action_information7 => l_tot_overtime_hours
865 ,p_action_information8 => l_tot_overtime_hw
866 ,p_action_information9 => l_tot_addl_time_hours
867 ,p_action_information10 => l_year
868 ,p_action_information11 => l_tot_relief_duty_mw -- EOY 2008
869 ,p_action_information12 => l_days_worked -- EOY 2008
870 ,p_action_information13 => NULL
871 ,p_action_information14 => NULL
872 ,p_action_information15 => NULL
873 ,p_action_information16 => NULL
874 ,p_action_information17 => NULL
875 ,p_action_information18 => NULL
876 ,p_action_information19 => NULL
877 ,p_action_information20 => NULL
878 ,p_action_information21 => NULL
879 ,p_action_information22 => NULL
880 ,p_action_information23 => NULL
881 ,p_action_information24 => NULL
882 ,p_action_information25 => NULL
883 ,p_action_information26 => NULL
884 ,p_action_information27 => NULL
885 ,p_action_information28 => NULL
886 ,p_action_information29 => NULL
887 ,p_action_information30 => g_person_id
888 ,p_assignment_id => g_assignment_id
889 );
890
891
892 l_overtime_hw :=0;
893
894 ---- Check for the Other Taxable Compensation Variable in the Working Time Certificate Report
895
896
897 -- Salary Information in Working Time Certificate of Section 12
898 -- has vaiable Other Compensation
899 -- Assignment EIT of Income Info has Other Compensation marked as 'YES'
900
901 OPEN csr_extra_assignment_info (g_assignment_id, 'SE_WTC_INCOME_INFO');
902
903
904
905 FETCH csr_extra_assignment_info
906 INTO lr_extra_assignment_info;
907
908 CLOSE csr_extra_assignment_info;
909
910 l_hourly_pay_variable := lr_extra_assignment_info.aei_information9;
911 l_other_tax_compensation := lr_extra_assignment_info.aei_information12;
912
913 -- fnd_file.put_line (fnd_file.LOG, 'l_other_tax_compensation'||l_other_tax_compensation);
914 --fnd_file.put_line (fnd_file.LOG, 'l_reporting_date'||l_reporting_date);
915
916
917 IF l_other_tax_compensation ='Y'
918 THEN
919 l_oth_comp :=0;
920
921 BEGIN
922 FOR balance_rec IN csr_balance('Other Compensation' , g_business_group_id)
923 LOOP
924
925 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
926 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
927 CLOSE csr_bg_Get_Defined_Balance_Id;
928
929 IF csr_balance%FOUND THEN
930
931 l_oth_comp :=0;
932 l_oth_comp :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
933 --fnd_file.put_line (fnd_file.LOG, 'l_oth_comp' ||l_oth_comp);
934
935 IF l_oth_comp > 0 THEN
936
937 pay_action_information_api.create_action_information
938 (p_action_information_id => l_action_info_id
939 ,p_action_context_id => p_payroll_action_id
940 ,p_action_context_type => 'PA'
941 ,p_object_version_number => l_ovn
942 ,p_effective_date => g_effective_date
943 ,p_source_id => NULL
944 ,p_source_text => NULL
945 ,p_action_information_category => 'EMEA REPORT INFORMATION'
946 ,p_action_information1 => 'PYSECWCA'
947 ,p_action_information2 => 'CWC3'
948 ,p_action_information3 => l_month
949 ,p_action_information4 => balance_rec.bname
950 ,p_action_information5 => l_oth_comp
951 ,p_action_information6 => NULL
952 ,p_action_information7 => NULL
953 ,p_action_information8 => NULL
954 ,p_action_information9 => NULL
955 ,p_action_information10 => l_year
956 ,p_action_information11 => NULL
957 ,p_action_information12 => NULL
958 ,p_action_information13 => NULL
959 ,p_action_information14 => NULL
960 ,p_action_information15 => NULL
961 ,p_action_information16 => NULL
962 ,p_action_information17 => NULL
963 ,p_action_information18 => NULL
964 ,p_action_information19 => NULL
965 ,p_action_information20 => NULL
966 ,p_action_information21 => NULL
967 ,p_action_information22 => NULL
968 ,p_action_information23 => NULL
969 ,p_action_information24 => NULL
970 ,p_action_information25 => NULL
971 ,p_action_information26 => NULL
972 ,p_action_information27 => NULL
973 ,p_action_information28 => NULL
974 ,p_action_information29 => NULL
975 ,p_action_information30 => g_person_id
976 ,p_assignment_id => g_assignment_id
977 );
978
979 END IF;
980
981 END IF;
982 END LOOP ;
983
984
985 EXCEPTION
986 WHEN others THEN
987 fnd_file.put_line (fnd_file.LOG, 'Error in Other Compensation'||substr(sqlerrm,1,30));
988 END;
989
990 END IF; --- End of Validation for Other Tax Compensation in Assignment EIT
991
992
993
994 ----- If the Over Time and Additional Pay are Variables then we insert the following cases...
995
996
997 -- Salary Information in Working Time Certificate of Section 12
998 -- has vaiable Additional and Overtime Pay
999 -- Assignment EIT of Income Info has Hourly Pay Variable marked as 'YES'
1000
1001 --fnd_file.put_line (fnd_file.LOG, 'l_hourly_pay_variable'||l_hourly_pay_variable);
1002
1003 IF l_hourly_pay_variable = 'Y'
1004 THEN
1005
1006 l_overtime_mw :=0;
1007
1008 BEGIN
1009
1010 FOR balance_rec IN csr_balance('Overtime - Monthly Wages' , g_business_group_id)
1011 LOOP
1012
1013 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1014 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1015 CLOSE csr_bg_Get_Defined_Balance_Id;
1016
1017 IF csr_balance%FOUND THEN
1018 l_overtime_mw := 0;
1019 l_overtime_mw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
1020 --fnd_file.put_line (fnd_file.LOG, 'l_overtime_mw' ||l_overtime_mw);
1021 IF l_overtime_mw > 0 THEN
1022
1023 pay_action_information_api.create_action_information
1024 (p_action_information_id => l_action_info_id
1025 ,p_action_context_id => p_payroll_action_id
1026 ,p_action_context_type => 'PA'
1027 ,p_object_version_number => l_ovn
1028 ,p_effective_date => g_effective_date
1029 ,p_source_id => NULL
1030 ,p_source_text => NULL
1031 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1032 ,p_action_information1 => 'PYSECWCA'
1033 ,p_action_information2 => 'CWC4'
1034 ,p_action_information3 => l_month
1035 ,p_action_information4 => balance_rec.bname
1036 ,p_action_information5 => l_overtime_mw
1037 ,p_action_information6 => NULL
1038 ,p_action_information7 => NULL
1039 ,p_action_information8 => NULL
1040 ,p_action_information9 => NULL
1041 ,p_action_information10 => l_year
1042 ,p_action_information11 => NULL
1043 ,p_action_information12 => NULL
1044 ,p_action_information13 => NULL
1045 ,p_action_information14 => NULL
1046 ,p_action_information15 => NULL
1047 ,p_action_information16 => NULL
1048 ,p_action_information17 => NULL
1049 ,p_action_information18 => NULL
1050 ,p_action_information19 => NULL
1051 ,p_action_information20 => NULL
1052 ,p_action_information21 => NULL
1053 ,p_action_information22 => NULL
1054 ,p_action_information23 => NULL
1055 ,p_action_information24 => NULL
1056 ,p_action_information25 => NULL
1057 ,p_action_information26 => NULL
1058 ,p_action_information27 => NULL
1059 ,p_action_information28 => NULL
1060 ,p_action_information29 => NULL
1061 ,p_action_information30 => g_person_id
1062 ,p_assignment_id => g_assignment_id
1063 );
1064
1065 END IF;
1066
1067 END IF;
1068 END LOOP ;
1069
1070
1071 EXCEPTION
1072 WHEN others THEN
1073 fnd_file.put_line (fnd_file.LOG, 'Error in Overtime'||substr(sqlerrm,1,30));
1074 END; -- End of Begin for Overtime Variable Pay
1075
1076
1077 ------------------------------------Additional/Supplementary hours - if variable ---
1078
1079
1080
1081
1082 BEGIN
1083 l_addl_time_mw :=0;
1084
1085 FOR balance_rec IN csr_balance('Additional Time -Monthly Wages' , g_business_group_id)
1086 LOOP
1087
1088 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1089 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1090 CLOSE csr_bg_Get_Defined_Balance_Id;
1091 --fnd_file.put_line (fnd_file.LOG, 'check_addtional' ||l_addl_time_mw);
1092
1093 IF csr_balance%FOUND THEN
1094
1095 l_addl_time_mw :=0;
1096 l_addl_time_mw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
1097 --fnd_file.put_line (fnd_file.LOG, 'l_addl_time_mw' ||l_addl_time_mw);
1098 IF l_addl_time_mw > 0 THEN
1099
1100 pay_action_information_api.create_action_information
1101 (p_action_information_id => l_action_info_id
1102 ,p_action_context_id => p_payroll_action_id
1103 ,p_action_context_type => 'PA'
1104 ,p_object_version_number => l_ovn
1105 ,p_effective_date => g_effective_date
1106 ,p_source_id => NULL
1107 ,p_source_text => NULL
1108 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1109 ,p_action_information1 => 'PYSECWCA'
1110 ,p_action_information2 => 'CWC5'
1111 ,p_action_information3 => l_month
1112 ,p_action_information4 => balance_rec.bname
1113 ,p_action_information5 => l_addl_time_mw
1114 ,p_action_information6 => NULL
1115 ,p_action_information7 => NULL
1116 ,p_action_information8 => NULL
1117 ,p_action_information9 => NULL
1118 ,p_action_information10 => l_year
1119 ,p_action_information11 => NULL
1120 ,p_action_information12 => NULL
1121 ,p_action_information13 => NULL
1122 ,p_action_information14 => NULL
1123 ,p_action_information15 => NULL
1124 ,p_action_information16 => NULL
1125 ,p_action_information17 => NULL
1126 ,p_action_information18 => NULL
1127 ,p_action_information19 => NULL
1128 ,p_action_information20 => NULL
1129 ,p_action_information21 => NULL
1130 ,p_action_information22 => NULL
1131 ,p_action_information23 => NULL
1132 ,p_action_information24 => NULL
1133 ,p_action_information25 => NULL
1134 ,p_action_information26 => NULL
1135 ,p_action_information27 => NULL
1136 ,p_action_information28 => NULL
1137 ,p_action_information29 => NULL
1138 ,p_action_information30 => g_person_id
1139 ,p_assignment_id => g_assignment_id
1140 );
1141
1142 END IF;
1143
1144 END IF;
1145 END LOOP ;
1146
1147
1148 EXCEPTION
1149 WHEN others THEN
1150 fnd_file.put_line (fnd_file.LOG, 'Error in Additional'||substr(sqlerrm,1,30));
1151 END; -- End of Begin for Additional Time monthly Wage
1152
1153 END IF; --- End of IF condition for hourly pay variable
1154
1155 --l_report_start_date := ADD_MONTHS(l_report_start_date,1);
1156 l_report_end_date := ADD_MONTHS(l_report_end_date,-1);
1157 count_months := count_months + 1;
1158
1159 END LOOP;
1160
1161 END IF;
1162
1163 IF g_debug THEN
1164 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
1165 END IF;
1166 EXCEPTION
1167 WHEN OTHERS THEN
1168 -- Return cursor that selects no rows
1169 p_sql :=
1170 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1171 END range_code;
1172
1173 /* ASSIGNMENT ACTION CODE */
1174 PROCEDURE assignment_action_code (
1175 p_payroll_action_id IN NUMBER
1176 ,p_start_person IN NUMBER
1177 ,p_end_person IN NUMBER
1178 ,p_chunk IN NUMBER
1179 )
1180 IS
1181 -- End of User pARAMETERS needed
1182 BEGIN
1183 NULL;
1184 END assignment_action_code;
1185
1186 /*fffffffffffffffffffffffffff*/
1187
1188 /* INITIALIZATION CODE */
1189 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
1190 IS
1191 l_action_info_id NUMBER;
1192 l_ovn NUMBER;
1193 l_count NUMBER := 0;
1194 l_business_group_id NUMBER;
1195 l_start_date VARCHAR2 (20);
1196 l_end_date VARCHAR2 (20);
1197 l_effective_date DATE;
1198 l_payroll_id NUMBER;
1199 l_consolidation_set NUMBER;
1200 l_prev_prepay NUMBER := 0;
1201 BEGIN
1202
1203 IF g_debug THEN
1204 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
1205 END IF;
1206
1207 IF g_debug THEN
1208 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
1209 END IF;
1210
1211 EXCEPTION
1212 WHEN OTHERS
1213 THEN
1214 g_err_num := SQLCODE;
1215
1216 IF g_debug
1217 THEN
1218 hr_utility.set_location ( 'ORA_ERR: '
1219 || g_err_num
1220 || 'In INITIALIZATION_CODE'
1221 ,180
1222 );
1223 END IF;
1224
1225
1226 END initialization_code;
1227
1228
1229 /* ARCHIVE CODE */
1230 PROCEDURE archive_code (
1231 p_assignment_action_id IN NUMBER
1232 ,p_effective_date IN DATE
1233 )
1234 IS
1235 -- End of place for Cursor which fetches the values to be archived
1236 BEGIN
1237 NULL;
1238 END archive_code;
1239
1240 PROCEDURE DEINITIALIZATION_CODE
1241 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
1242
1243 BEGIN
1244 IF g_debug THEN
1245 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',380);
1246 END IF;
1247
1248 IF g_debug THEN
1249 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
1250 END IF;
1251
1252 EXCEPTION
1253 WHEN others THEN
1254 IF g_debug THEN
1255 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
1256 END if;
1257 RAISE;
1258 END;
1259
1260 END PAY_SE_ARCHIVE_CWCA;