[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_ARCHIVE_EIN
Source
1 PACKAGE BODY PAY_DK_ARCHIVE_EIN AS
2 /* $Header: pydkeina.pkb 120.49.12020000.5 2012/12/18 12:40:40 rsahai ship $ */
3
4 g_debug BOOLEAN := hr_utility.debug_enabled;
5 g_package VARCHAR2(33) := ' PAY_DK_ARCHIVE_EIN.';
6 g_payroll_action_id NUMBER ;
7 g_le_assignment_action_id NUMBER ;
8 g_business_group_id NUMBER;
9 g_legal_employer_id NUMBER;
10 --Create globals
11 g_effective_date DATE;
12 g_start_date VARCHAR2(30); /* 9489806 */
13 g_end_date VARCHAR2(30); /* 9489806 */
14 g_payroll_type VARCHAR2(30); /* 9489806 */
15 g_payroll_id NUMBER;
16 g_payroll_period NUMBER;
17 g_test_submission VARCHAR2(1);
18 g_company_terminating VARCHAR2(1);
19 g_sender_id VARCHAR2(8);
20 g_flag NUMBER:=0;
21 l_bincome_exception exception;
22
23 --13463885
24 FUNCTION get_defined_balance_id (
25 p_balance_name VARCHAR2,
26 p_dimension_suffix VARCHAR2
27 )
28 RETURN NUMBER
29 AS
30 CURSOR csr_defined_balance_id
31 IS
32 SELECT pdb.defined_balance_id
33 FROM pay_balance_dimensions pbd,
34 pay_balance_types pbt,
35 pay_defined_balances pdb
36 WHERE pbd.database_item_suffix = p_dimension_suffix
37 AND pbd.business_group_id IS NULL
38 AND pbd.legislation_code = 'DK'
39 AND pbt.balance_name = p_balance_name
40 AND pbt.business_group_id IS NULL
41 AND pbt.legislation_code = 'DK'
42 AND pdb.balance_type_id = pbt.balance_type_id
43 AND pdb.balance_dimension_id = pbd.balance_dimension_id
44 AND pdb.business_group_id IS NULL
45 AND pdb.legislation_code = 'DK';
46
47 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
48 BEGIN
49 OPEN csr_defined_balance_id;
50 FETCH csr_defined_balance_id INTO l_defined_balance_id;
51 CLOSE csr_defined_balance_id;
52 RETURN l_defined_balance_id;
53 END;
54 --13463885
55
56
57 FUNCTION GET_DEFINED_BALANCE_VALUE
58 (p_assignment_id IN NUMBER
59 ,p_balance_name IN VARCHAR2
60 ,p_balance_dim IN VARCHAR2
61 ,p_virtual_date IN DATE) RETURN NUMBER IS
62
63 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
64 l_value NUMBER;
65
66
67 CURSOR get_dbal_id(p_balance_name VARCHAR2 , p_balance_dim VARCHAR2) IS
68 SELECT pdb.defined_balance_id
69 FROM pay_defined_balances pdb
70 ,pay_balance_types pbt
71 ,pay_balance_dimensions pbd
72 WHERE pbt.legislation_code='DK'
73 AND pbt.balance_name = p_balance_name
74 AND pbd.legislation_code = 'DK'
75 AND pbd.database_item_suffix = p_balance_dim
76 AND pdb.balance_type_id = pbt.balance_type_id
77 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
78
79
80 BEGIN
81
82 OPEN get_dbal_id(p_balance_name, p_balance_dim);
83 FETCH get_dbal_id INTO l_context1;
84 CLOSE get_dbal_id;
85
86 l_value := nvl(pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date), 0);
87
88 RETURN l_value;
89
90 END GET_DEFINED_BALANCE_VALUE ;
91
92
93 /* GET PARAMETER */
94 FUNCTION GET_PARAMETER(
95 p_parameter_string IN VARCHAR2
96 ,p_token IN VARCHAR2
97 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
98 IS
99 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
100 l_start_pos NUMBER;
101 l_delimiter VARCHAR2(1):=' ';
102 l_proc VARCHAR2(40):= g_package||' get parameter ';
103 BEGIN
104 --
105 IF g_debug THEN
106 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
107 END IF;
108 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
109 --
110 IF l_start_pos = 0 THEN
111 l_delimiter := '|';
112 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
113 END IF;
114
115 IF l_start_pos <> 0 THEN
116 l_start_pos := l_start_pos + length(p_token||'=');
117 l_parameter := substr(p_parameter_string,
118 l_start_pos,
119 instr(p_parameter_string||' ',
120 l_delimiter,l_start_pos)
121 - l_start_pos);
122 IF p_segment_number IS NOT NULL THEN
123 l_parameter := ':'||l_parameter||':';
124 l_parameter := substr(l_parameter,
125 instr(l_parameter,':',1,p_segment_number)+1,
126 instr(l_parameter,':',1,p_segment_number+1) -1
127 - instr(l_parameter,':',1,p_segment_number));
128 END IF;
129 END IF;
130 --
131 IF g_debug THEN
132 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
133 END IF;
134
135 RETURN l_parameter;
136
137 END;
138
139 /* GET ALL PARAMETERS */
140 PROCEDURE GET_ALL_PARAMETERS(
141 p_payroll_action_id IN NUMBER
142 ,p_business_group_id OUT NOCOPY NUMBER
143 ,p_legal_employer_id OUT NOCOPY NUMBER
144 ,p_effective_date OUT NOCOPY DATE
145 ,p_payroll OUT NOCOPY NUMBER
146 -- ,p_payroll_period OUT NOCOPY NUMBER /* 9489806 */
147 ,p_payroll_type OUT NOCOPY VARCHAR2 /* 9489806 */
148 ,p_start_date OUT NOCOPY VARCHAR2 /* 9489806 */
149 ,p_end_date OUT NOCOPY VARCHAR2 /* 9489806 */
150 ,p_test_submission OUT NOCOPY VARCHAR2
151 ,p_company_terminating OUT NOCOPY VARCHAR2
152 ,p_sender_id OUT NOCOPY VARCHAR2
153 ) IS
154
155 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
156 SELECT PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
157 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL')
158 -- ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL_PERIOD')
159 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL_TYPE') /* 9489806 */
160 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'START_DATE') /* 9489806 */
161 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'END_DATE') /* 9489806 */
162 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'TEST_SUBMISSION')
163 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'COMPANY_TERMINATING')
164 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'SENDER_ID')
165 ,effective_date
166 ,business_group_id
167 FROM pay_payroll_actions
168 WHERE payroll_action_id = p_payroll_action_id;
169
170 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
171 --
172 BEGIN
173
174 OPEN csr_parameter_info (p_payroll_action_id);
175
176 FETCH csr_parameter_info
177 INTO p_legal_employer_id
178 ,p_payroll
179 -- ,p_payroll_period
180 ,p_payroll_type
181 ,p_start_date
182 ,p_end_date
183 ,p_test_submission
184 ,p_company_terminating
185 ,p_sender_id
186 ,p_effective_date
187 ,p_business_group_id;
188 CLOSE csr_parameter_info;
189 --
190 hr_utility.set_location(' get paramerters:p_start_date ' || p_start_date,30);
191 hr_utility.set_location(' get paramerters:p_end_date ' || p_end_date,30);
192 hr_utility.set_location(' get paramerters:p_payroll_type ' || p_payroll_type,30);
193 IF g_debug THEN
194 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
195 END IF;
196
197 END GET_ALL_PARAMETERS;
198
199 /* RANGE CODE */
200 PROCEDURE RANGE_CODE
201 (p_payroll_action_id IN NUMBER
202 ,p_sql OUT NOCOPY VARCHAR2)
203 IS
204 l_action_info_id NUMBER;
205 l_ovn NUMBER;
206 l_count NUMBER := 0;
207 l_business_group_id NUMBER;
208 l_test_submission varchar2(1);
209 -- l_company_terminating varchar2(1); bug 10367494
210 l_emp_id hr_organization_units.organization_id%TYPE ;
211 l_le_name hr_organization_units.name%TYPE ;
212 l_business_id hr_organization_information.org_information1%TYPE ;
213 l_cvr_number hr_organization_information.org_information1%TYPE ;
214 l_sp_cvr_number hr_organization_information.org_information1%TYPE ;
215 l_org_type hr_organization_information.org_information1%TYPE ;
216 l_date VARCHAR2(100);
217 l_time VARCHAR2(10);
218 l_lb_num VARCHAR2(10);
219 l_unique_id VARCHAR2(16);
220 l_unique_id1 VARCHAR2(16); /* 9489806 */
221
222 l_canonical_start_date DATE; /* 9489806 */
223 l_canonical_end_date DATE; /* 9489806 */
224
225 /*Cursors */
226
227 /*Legal Employer Information*/
228 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
229 IS
230 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
231 FROM hr_organization_units o1
232 , hr_organization_information hoi1
233 , hr_organization_information hoi2
234 WHERE o1.business_group_id =l_business_group_id
235 AND hoi1.organization_id = o1.organization_id
236 AND hoi1.organization_id = csr_v_legal_emp_id
237 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
238 AND hoi1.org_information_context = 'CLASS'
239 AND o1.organization_id =hoi2.organization_id
240 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
241
242 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
243
244 -- Service Provider information.
245 cursor service_provider_details
246 is
247 select * from hr_organization_information
248 where org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
249 and organization_id in (
250 select organization_id from hr_organization_units
251 where business_group_id= l_business_group_id);
252
253 sp service_provider_details%rowtype;
254
255 /* Payroll Time period */
256 CURSOR csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
257 IS
258 SELECT ptp.start_date, ptp.end_date, ptp.period_name,
259 default_dd_date,
260 decode(PERIOD_TYPE
261 ,'Calendar Month','1'
262 ,'Bi-Week' ,'2'
263 ,'Week' ,'3'
264 ,'Lunar Month' ,'4') PAYROLL_PERIOD
265 from per_time_periods ptp
266 WHERE payroll_id = p_payroll_id
267 AND time_period_id = p_payroll_period;
268
269 rg_csr_pay_periods csr_pay_periods%rowtype;
270
271 /* Payroll Name */
272 CURSOR csr_payroll_name(id pay_all_payrolls_f.payroll_id%type)
273 IS
274 select payroll_name from pay_payrolls_f
275 where payroll_id=id;
276
277 -- rg_csr_payroll_name csr_payroll_name%rowtype;
278 l_payroll_name VARCHAR2(100):=NULL;
279
280 CURSOR csr_le_payrolls is /* 9489806 */
281 SELECT DISTINCT
282 ppf.payroll_name,
283 ppf.payroll_id,
284 ptp.start_date,
285 ptp.end_date,
286 ptp.period_name,
287 ptp.default_dd_date,
288 decode(ptp.PERIOD_TYPE
289 ,'Calendar Month','1'
290 ,'Bi-Week' ,'2'
291 ,'Week' ,'3'
292 ,'Lunar Month' ,'4') PAYROLL_PERIOD,
293 ptp.TIME_PERIOD_ID
294 FROM
295 pay_payrolls_f ppf,
296 per_assignments_f paf,
297 hr_organization_units hou,
298 hr_organization_information hoi,
299 hr_soft_coding_keyflex scl,
300 per_time_periods ptp
301 WHERE hou.business_group_id = l_business_group_id
302 AND hou.organization_id = g_legal_employer_id
303 AND hou.organization_id = hoi.organization_id
304 AND hoi.org_information_context = 'CLASS'
305 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
306 AND paf.payroll_id = ppf.payroll_id
307 AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
308 AND ppf.effective_start_date <= l_canonical_start_date and ppf.effective_end_date >= l_canonical_end_date
309 AND ptp.payroll_id=ppf.payroll_id
310 AND ptp.end_date BETWEEN l_canonical_start_date AND l_canonical_end_date
311 AND paf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
312 AND scl.segment1 = to_char(hou.organization_id)
313 AND ppf.business_group_id = l_business_group_id;
314
315 /* End of Cursors */
316 BEGIN
317
318 hr_utility.set_location(' Entering Procedure RANGE_CODE',1);
319
320 IF g_debug THEN
321 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
322 END IF;
323
324 p_sql := 'SELECT DISTINCT person_id FROM per_people_f ppf,pay_payroll_actions ppa WHERE ppa.payroll_action_id = :payroll_action_id AND ppa.business_group_id = ppf.business_group_id ORDER BY ppf.person_id';
325
326 g_legal_employer_id := NULL ;
327 g_effective_date := NULL ;
328 g_start_date := NULL; /* 9489806 */
329 g_end_date :=NULL;
330 g_payroll_action_id := p_payroll_action_id ;
331 g_le_assignment_action_id := NULL ;
332 g_payroll_id:=NULL;
333 g_payroll_period:=NULL;
334 g_test_submission:=NULL;
335 g_company_terminating:=NULL;
336 g_sender_id:=NULL;
337
338 hr_utility.set_location('RANGE_CODE:Before Calling all parameters',2);
339
340 PAY_DK_ARCHIVE_EIN.GET_ALL_PARAMETERS(
341 p_payroll_action_id
342 ,l_business_group_id
343 ,g_legal_employer_id
344 ,g_effective_date
345 ,g_payroll_id
346 -- ,g_payroll_period
347 ,g_payroll_type
348 ,g_start_date
349 ,g_end_date
350 ,g_test_submission
351 ,g_company_terminating
352 ,g_sender_id) ;
353
354 hr_utility.set_location('RANGE_CODE:After Calling all parameters',2);
355
356 l_canonical_start_date := fnd_date.canonical_to_date(g_start_date);
357 l_canonical_end_date := fnd_date.canonical_to_date(g_end_date);
358 SELECT count(*)
359 INTO l_count
360 FROM pay_action_information
361 WHERE action_information_category = 'EMEA REPORT DETAILS'
362 AND action_information1 = 'PYDKEINA'
363 AND action_context_id = p_payroll_action_id;
364
365 -- fnd_file.put_line(fnd_file.log,'Range Code 6');
366
367 IF l_count < 1 then
368
369 hr_utility.set_location('Entered Procedure GETDATA',10);
370
371
372 -- fnd_file.put_line(fnd_file.log,'g_legal_employer_id : '||g_legal_employer_id);
373 -- fnd_file.put_line(fnd_file.log,'l_business_group_id : '||l_business_group_id);
374 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
375 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
376
377 /* if(csr_Legal_Emp_Details%notfound) then
378 fnd_file.put_line(fnd_file.log,'No data found for the legal emp');
379 end if;*/
380
381 l_le_name := rg_Legal_Emp_Details.name ;
382 l_cvr_number := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
383
384 -- fnd_file.put_line(fnd_file.log,'l_cvr_number : '||l_cvr_number);
385
386 CLOSE csr_Legal_Emp_Details;
387
388 /* Pick up the details belonging to Legal Employer
389
390 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
391 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
392 CLOSE csr_Legal_Emp_Details;
393
394 l_le_name := rg_Legal_Emp_Details.name ;
395 l_business_id := rg_Legal_Emp_Details.ORG_INFORMATION1 ; */
396
397 -- date and time
398 SELECT to_char(sysdate,'yyyymmdd') INTO l_date FROM dual;
399 SELECT to_char(sysdate,'hhmiss') INTO l_time FROM dual;
400
401 if(g_test_submission='Y') then
402 l_test_submission:='T';
403 else
404 l_test_submission:='P';
405 end if;
406
407 /* if(g_company_terminating='Y') then
408 l_company_terminating:='A';
409 else
410 l_company_terminating:=null;
411 end if; */ -- bug 10367494
412
413 --Employer Level Information
414 if(rg_Legal_Emp_Details.ORG_INFORMATION3 = 'Y') then -- if the legal emp has data supplier set to 'Y'
415
416 -- l_unique_id:=l_cvr_number; /* 9489806 */
417
418 pay_action_information_api.create_action_information (
419 p_action_information_id => l_action_info_id
420 ,p_action_context_id => p_payroll_action_id
421 ,p_action_context_type => 'PA'
422 ,p_object_version_number => l_ovn
423 ,p_effective_date => g_effective_date
424 ,p_source_id => NULL
425 ,p_source_text => NULL
426 ,p_action_information_category => 'EMEA REPORT INFORMATION'
427 ,p_action_information1 => 'PYDKEINA'
428 ,p_action_information2 => p_payroll_action_id
429 ,p_action_information3 => '1000'
430 ,p_action_information4 => l_date
431 ,p_action_information5 => l_time
432 ,p_action_information6 => rg_Legal_Emp_Details.ORG_INFORMATION5 -- legal emp SE number
433 ,p_action_information7 => l_cvr_number -- legal emp CVR number
434 ,p_action_information8 => '01' -- for Legal emp
435 ,p_action_information9 => '0'
436 ,p_action_information10 => 'Oracle Payroll'
437 ,p_action_information11 => '1'
438 ,p_action_information12 => g_sender_id -- main sender ID
439 ,p_action_information13 => '2.0'
440 ,p_action_information14 => l_test_submission
441 ,p_action_information15 => 'E'
442 ,p_action_information16 => null
443 ,p_action_information17 => null
444 ,p_action_information18 => null
445 ,p_action_information19 => null
446 ,p_action_information20 => null
447 ,p_action_information21 => null
448 ,p_action_information22 => null
449 ,p_action_information23 => null
450 ,p_action_information24 => null
451 ,p_action_information25 => null
452 ,p_action_information26 => null
453 ,p_action_information27 => null
454 ,p_action_information28 => null
455 ,p_action_information29 => null
456 ,p_action_information30 => null);
457
458 else
459
460 open service_provider_details;
461 fetch service_provider_details into sp;
462 l_sp_cvr_number := sp.org_information1;
463
464 -- check the value of e-income data supplier
465 if(sp.org_information3<>'Y') then
466 fnd_file.put_line(fnd_file.log,HR_DK_UTILITY.GET_MESSAGE('PAY','HR_377103_DK_EINCOME_STATUS'));
467 g_flag:=1;
468 else
469 -- l_unique_id:=l_sp_cvr_number; /* 9489806 */
470
471 pay_action_information_api.create_action_information (
472 p_action_information_id => l_action_info_id
473 ,p_action_context_id => p_payroll_action_id
474 ,p_action_context_type => 'PA'
475 ,p_object_version_number => l_ovn
476 ,p_effective_date => g_effective_date
477 ,p_source_id => NULL
478 ,p_source_text => NULL
479 ,p_action_information_category => 'EMEA REPORT INFORMATION'
480 ,p_action_information1 => 'PYDKEINA'
481 ,p_action_information2 => p_payroll_action_id
482 ,p_action_information3 => '1000'
483 ,p_action_information4 => l_date
484 ,p_action_information5 => l_time -- time
485 ,p_action_information6 => l_sp_cvr_number -- service provider CVR number
486 ,p_action_information7 => l_sp_cvr_number -- service provider CVR number
487 ,p_action_information8 => '02' -- 02 for Service provider
488 ,p_action_information9 => '0'
489 ,p_action_information10 => 'Oracle Payroll'
490 ,p_action_information11 => '1'
491 ,p_action_information12 => g_sender_id -- main sender ID (service provider CVR number)
492 ,p_action_information13 => '2.0'
493 ,p_action_information14 => l_test_submission
494 ,p_action_information15 => 'E'
495 ,p_action_information16 => null
496 ,p_action_information17 => null
497 ,p_action_information18 => null
498 ,p_action_information19 => null
499 ,p_action_information20 => null
500 ,p_action_information21 => null
501 ,p_action_information22 => null
502 ,p_action_information23 => null
503 ,p_action_information24 => null
504 ,p_action_information25 => null
505 ,p_action_information26 => null
506 ,p_action_information27 => null
507 ,p_action_information28 => null
508 ,p_action_information29 => null
509 ,p_action_information30 => null);
510 end if;
511 close service_provider_details;
512 end if;
513
514 if(g_flag=0) then
515 -- Record 2001
516 /* pay_action_information_api.create_action_information (
517 p_action_information_id => l_action_info_id
518 ,p_action_context_id => p_payroll_action_id
519 ,p_action_context_type => 'PA'
520 ,p_object_version_number => l_ovn
521 ,p_effective_date => g_effective_date
522 ,p_source_id => NULL
523 ,p_source_text => NULL
524 ,p_action_information_category => 'EMEA REPORT INFORMATION'
525 ,p_action_information1 => 'PYDKEINA'
526 ,p_action_information2 => p_payroll_action_id
527 ,p_action_information3 => '2001'
528 ,p_action_information4 => l_cvr_number -- legal emp CVR number
529 ,p_action_information5 => l_company_terminating -- is null if company termiating is 'N'
530 ,p_action_information6 => 'DKK'
531 ,p_action_information7 => null
532 ,p_action_information8 => null
533 ,p_action_information9 => null
534 ,p_action_information10 => null
535 ,p_action_information11 => null
536 ,p_action_information12 => null
537 ,p_action_information13 => null
538 ,p_action_information14 => null
539 ,p_action_information15 => null
540 ,p_action_information16 => null
541 ,p_action_information17 => null
542 ,p_action_information18 => null
543 ,p_action_information19 => null
544 ,p_action_information20 => null
545 ,p_action_information21 => null
546 ,p_action_information22 => null
547 ,p_action_information23 => null
548 ,p_action_information24 => null
549 ,p_action_information25 => null
550 ,p_action_information26 => null
551 ,p_action_information27 => null
552 ,p_action_information28 => null
553 ,p_action_information29 => null
554 ,p_action_information30 => null); */ -- bug 10367494
555
556
557
558 /* Bug fix 7579265 */
559 /* 8861878 p_payroll_action_id */
560 -- l_unique_id:=l_unique_id||lpad(nvl(substr(g_payroll_id,-4),g_payroll_id),4,'0')||to_char(rg_csr_pay_periods.start_date,'mmyy');
561 l_unique_id:=lpad(substr(to_char(p_payroll_action_id), -least(length(p_payroll_action_id),8)),8,0);
562 hr_utility.set_location('RANGE_CODE: Before loop l_unique_id '|| l_unique_id,50);
563 FOR csr_rec in csr_le_payrolls /* 9489806 */
564 LOOP
565 l_unique_id1:=l_unique_id|| lpad( substr(to_char(csr_rec.TIME_PERIOD_ID),-least(length(csr_rec.TIME_PERIOD_ID),5)),5,0) || '00'||'0'; /* 5 digits of time period id 2 digits of employemtn code, 1 digit for correcctin record , */
566 if(g_company_terminating='N') then
567 -- Record 5000
568 pay_action_information_api.create_action_information (
569 p_action_information_id => l_action_info_id
570 ,p_action_context_id => p_payroll_action_id
571 ,p_action_context_type => 'PA'
572 ,p_object_version_number => l_ovn
573 ,p_effective_date => g_effective_date
574 ,p_source_id => NULL
575 ,p_source_text => NULL
576 ,p_action_information_category => 'EMEA REPORT INFORMATION'
577 ,p_action_information1 => 'PYDKEINA'
578 ,p_action_information2 => p_payroll_action_id
579 ,p_action_information3 => '5000'
580 ,p_action_information4 => to_char(csr_rec.start_date,'yyyymmdd')
581 ,p_action_information5 => to_char(csr_rec.end_date,'yyyymmdd')
582 ,p_action_information6 => to_char(csr_rec.default_dd_date,'yyyymmdd')
583 ,p_action_information7 => 'B'
584 ,p_action_information8 => '000' --Greenland code
585 ,p_action_information9 => '00' --Normal employee code
586 ,p_action_information10 => l_unique_id1 -- should be reported in field 4 /* Bug fix 7579265 */
587 ,p_action_information11 => l_cvr_number -- 10367494
588 ,p_action_information12 => null
589 ,p_action_information13 => null
590 ,p_action_information14 => null
591 ,p_action_information15 => null
592 ,p_action_information16 => null
593 ,p_action_information17 => null
594 ,p_action_information18 => null
595 ,p_action_information19 => null
596 ,p_action_information20 => null
597 ,p_action_information21 => null
598 ,p_action_information22 => null
599 ,p_action_information23 => null
600 ,p_action_information24 => csr_rec.TIME_PERIOD_ID /* 9489806 */
601 ,p_action_information25 => csr_rec.payroll_id /* 9489806 */
602 ,p_action_information26 => null
603 ,p_action_information27 => null
604 ,p_action_information28 => null
605 ,p_action_information29 => null
606 ,p_action_information30 => null);
607 end if;
608 END LOOP;
609
610 /* 9489806QA */
611 IF g_payroll_id IS NOT NULL THEN
612 open csr_payroll_name(g_payroll_id);
613 fetch csr_payroll_name into l_payroll_name;
614 close csr_payroll_name;
615 END IF;
616
617 -- fnd_file.put_line(fnd_file.log,'Range Code 14');
618 --Report Level Information
619 pay_action_information_api.create_action_information (
620 p_action_information_id => l_action_info_id
621 ,p_action_context_id => p_payroll_action_id
622 ,p_action_context_type => 'PA'
623 ,p_object_version_number => l_ovn
624 ,p_effective_date => g_effective_date
625 ,p_source_id => NULL
626 ,p_source_text => NULL
627 ,p_action_information_category => 'EMEA REPORT DETAILS'
628 ,p_action_information1 => 'PYDKEINA'
629 ,p_action_information2 => l_le_name
630 ,p_action_information3 => l_payroll_name -- PAYROLL NAME if given in parameter 9489806
631 ,p_action_information4 => NULL -- earlier PAYROLL PERIOD NAME now NULL
632 ,p_action_information5 => HR_GENERAL.DECODE_LOOKUP('YES_NO',g_test_submission) -- TEST SUBMISSION
633 ,p_action_information6 => HR_GENERAL.DECODE_LOOKUP('YES_NO',g_company_terminating) -- COMPANY TERMINATING
634 ,p_action_information7 => null
635 ,p_action_information8 => g_start_date /* 9489806 */
636 ,p_action_information9 => g_end_date /* 9489806 */
637 ,p_action_information10 => null
638 ,p_action_information11 => null
639 ,p_action_information12 => null
640 ,p_action_information13 => null
641 ,p_action_information14 => null
642 ,p_action_information15 => null
643 ,p_action_information16 => null
644 ,p_action_information17 => null
645 ,p_action_information18 => null
646 ,p_action_information19 => null
647 ,p_action_information20 => null
648 ,p_action_information21 => null
649 ,p_action_information22 => null
650 ,p_action_information23 => null
651 ,p_action_information24 => null
652 ,p_action_information25 => null
653 ,p_action_information26 => null
654 ,p_action_information27 => null
655 ,p_action_information28 => null
656 ,p_action_information29 => null
657 ,p_action_information30 => null );
658
659 -- fnd_file.put_line(fnd_file.log,'Range Code 15');
660
661 end if; -- end g_flag condition
662
663 END IF;
664
665 IF g_debug THEN
666 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
667 END IF;
668
669 EXCEPTION
670 WHEN others THEN
671 IF g_debug THEN
672 hr_utility.set_location('error raised assignment_action_code ',5);
673 END if;
674 RAISE;
675 END RANGE_CODE;
676
677 /* ASSIGNMENT ACTION CODE */
678 PROCEDURE ASSIGNMENT_ACTION_CODE
679 (p_payroll_action_id IN NUMBER
680 ,p_start_person IN NUMBER
681 ,p_end_person IN NUMBER
682 ,p_chunk IN NUMBER )
683 IS
684
685 l_canonical_start_date DATE;
686 l_canonical_end_date DATE;
687 l_prepay_action_id NUMBER;
688 l_prev_person_id NUMBER;
689 l_prev_assg_id NUMBER;
690 l_prev_local_unit_id NUMBER;
691 l_actid NUMBER;
692
693
694 CURSOR csr_prepaid_assignments_le(p_payroll_action_id NUMBER,
695 p_start_person NUMBER,
696 p_end_person NUMBER,
697 p_legal_employer_id NUMBER,
698 p_payroll_id NUMBER,
699 p_payroll_period NUMBER,
700 l_canonical_start_date DATE,
701 l_canonical_end_date DATE)
702 IS
703 SELECT as1.person_id person_id,
704 act.assignment_id assignment_id,
705 act.assignment_action_id run_action_id,
706 act1.assignment_action_id prepaid_action_id
707 FROM pay_payroll_actions ppa
708 ,pay_payroll_actions appa
709 ,pay_payroll_actions appa2
710 ,pay_assignment_actions act
711 ,pay_assignment_actions act1
712 ,pay_action_interlocks pai
713 ,per_all_assignments_f as1
714 WHERE ppa.payroll_action_id = p_payroll_action_id
715 AND appa.effective_date BETWEEN l_canonical_start_date
716 AND l_canonical_end_date
717 AND as1.person_id BETWEEN p_start_person
718 AND p_end_person
719 AND appa.action_type IN ('R','Q')
720 -- Payroll Run or Quickpay Run
721 AND act.payroll_action_id = appa.payroll_action_id
722 --aapa table add time period check
723 -- AND appa.time_period_id = p_payroll_period
724 AND appa.effective_date between l_canonical_start_date and l_canonical_end_date
725 AND act.source_action_id IS NULL -- Master Action
726 AND as1.assignment_id = act.assignment_id
727 -- Add payroll id
728 AND as1.payroll_id = nvl(p_payroll_id,as1.payroll_id) /* 9489806 */
729 -- Commenting Code to Include Terminated Assignments
730 -- AND ppa.effective_date BETWEEN as1.effective_start_date
731 -- AND as1.effective_end_date
732 AND act.action_status IN ('C','S') -- 10229494
733 AND act.assignment_action_id = pai.locked_action_id
734 AND act1.assignment_action_id = pai.locking_action_id
735 AND act1.action_status IN ('C','S') -- 10229494
736 AND act1.payroll_action_id = appa2.payroll_action_id
737 AND appa2.action_type IN ('P','U')
738 AND appa2.effective_date BETWEEN l_canonical_start_date
739 AND l_canonical_end_date
740 -- Prepayments or Quickpay Prepayments
741 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
742 AND act.TAX_UNIT_ID = p_legal_employer_id
743 ORDER BY as1.person_id , act.assignment_id;
744
745 cursor csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
746 is
747 select start_date, end_date from per_time_periods
748 where payroll_id = p_payroll_id
749 and time_period_id = p_payroll_period;
750
751 pp csr_pay_periods%rowtype;
752
753 BEGIN
754 if(g_flag=0) then
755 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 1');
756
757 IF g_debug THEN
758 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
759 END IF;
760
761 PAY_DK_ARCHIVE_EIN.GET_ALL_PARAMETERS(
762 p_payroll_action_id
763 ,g_business_group_id
764 ,g_legal_employer_id
765 ,g_effective_date
766 ,g_payroll_id
767 --,g_payroll_period
768 ,g_payroll_type
769 ,g_start_date
770 ,g_end_date
771 ,g_test_submission
772 ,g_company_terminating
773 ,g_sender_id) ;
774
775 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 2');
776
777 g_payroll_action_id :=p_payroll_action_id;
778
779 /*
780 open csr_pay_periods(g_payroll_id, g_payroll_period);
781 fetch csr_pay_periods into pp;
782 l_canonical_start_date := pp.start_date;
783 l_canonical_end_date := pp.end_date;
784 */
785 l_canonical_start_date := fnd_date.canonical_to_date(g_start_date);
786 l_canonical_end_date := fnd_date.canonical_to_date(g_end_date);
787
788 l_prepay_action_id := 0;
789 l_prev_person_id := 0;
790 l_prev_assg_id := 0;
791
792 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 3');
793
794 FOR rec_prepaid_assignments IN csr_prepaid_assignments_le(p_payroll_action_id
795 ,p_start_person
796 ,p_end_person
797 ,g_legal_employer_id
798 ,g_payroll_id
799 ,g_payroll_period
800 ,l_canonical_start_date
801 ,l_canonical_end_date)
802 LOOP
803 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id
804 -- 13630274 -- AND l_prev_person_id <> rec_prepaid_assignments.person_id THEN
805 AND l_prev_assg_id <> rec_prepaid_assignments.assignment_id THEN
806
807 SELECT pay_assignment_actions_s.NEXTVAL
808 INTO l_actid
809 FROM dual;
810
811 -- Create the archive assignment action
812 hr_nonrun_asact.insact(l_actid
813 ,rec_prepaid_assignments.assignment_id
814 ,p_payroll_action_id
815 ,p_chunk
816 ,NULL);
817
818 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 7');
819
820 END IF;
821 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
822 l_prev_person_id := rec_prepaid_assignments.person_id;
823 l_prev_assg_id := rec_prepaid_assignments.assignment_id;
824 END LOOP;
825
826 IF g_debug THEN
827 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
828 END IF;
829 end if; -- end g_flag condition.
830
831 EXCEPTION
832 WHEN others THEN
833 IF g_debug THEN
834 hr_utility.set_location('error raised assignment_action_code ',5);
835 END if;
836 RAISE;
837 END ASSIGNMENT_ACTION_CODE;
838
839
840 /* INITIALIZATION CODE */
841 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
842 IS
843
844 BEGIN
845 IF g_debug THEN
846 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
847 END IF;
848 -- fnd_file.put_line(fnd_file.log,'INITIALIZATION_CODE 1');
849 IF g_debug THEN
850 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
851 END IF;
852
853 EXCEPTION
854 WHEN others THEN
855 IF g_debug THEN
856 hr_utility.set_location('error raised initialization code ',5);
857 END if;
858 RAISE;
859 END INITIALIZATION_CODE;
860
861 /* ARCHIVE CODE */
862 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
863 ,p_effective_date IN DATE)
864 IS
865 /* Cursor to retrieve Person Details */
866 CURSOR csr_get_person_details(p_asg_act_id NUMBER , p_asg_effective_date DATE ) IS
867 SELECT pap.national_identifier cpr , pap.person_id , pac.assignment_id, pap.full_name, to_char(pap.date_of_birth,'yyyymmdd') dob,
868 pap.first_name||' '||pap.middle_names||' '||pap.last_name pname, pap.sex, pap.start_date, pap.business_group_id,
869 assign.hourly_salaried_code HOURLY_SALARIED_CODE, assign.assignment_number, assign.organization_id, assign.primary_flag,
870 assign.location_id
871 FROM
872 pay_assignment_actions pac,
873 per_all_assignments_f assign,
874 per_all_people_f pap
875 WHERE pac.assignment_action_id = p_asg_act_id
876 AND assign.assignment_id = pac.assignment_id
877 AND assign.person_id = pap.person_id
878 AND pap.per_information_category = 'DK'
879 AND p_asg_effective_date BETWEEN assign.effective_start_date
880 AND assign.effective_end_date
881 AND p_asg_effective_date BETWEEN pap.effective_start_date
882 AND pap.effective_end_date;
883
884 rg_csr_get_person_details csr_get_person_details%rowtype;
885
886 /* Getting the latest hire date */
887 /* For Bug 9011035 */
888 CURSOR csr_latest_hire_date(pid per_all_people_f.person_id%type
889 ,p_end_date DATE) IS
890 SELECT MAX(date_start) lhd FROM per_periods_of_service
891 WHERE person_id=pid
892 AND date_start <= p_end_date;
893
894 rg_csr_latest_hire_date csr_latest_hire_date%rowtype;
895
896 /* Cursor to get the extra person info - Foreigner (yes/no) */ -- bug 13880339
897 CURSOR csr_get_extra_person_info(pid per_all_people_f.person_id%type) IS
898 SELECT PEI_INFORMATION1 yes_no ,PEI_INFORMATION2 stationed_abroad FROM per_people_extra_info
899 WHERE person_id=pid
900 AND information_type='DK_EINCOME_FORIEGN_IND';
901
902 rg_csr_get_extra_person_info csr_get_extra_person_info%rowtype;
903
904 /* Get the territory */
905 CURSOR csr_get_territory(pid per_all_people_f.person_id%type) IS
906 SELECT *
907 FROM per_addresses_v
908 WHERE person_id =pid
909 and primary_flag='Y';
910
911 rg_csr_get_territory csr_get_territory%rowtype;
912
913 /* Cursor to get SE Number, PUCODE at HR Org level */
914
915 CURSOR csr_get_hr_org_info(
916 bg_id hr_organization_units.business_group_id%type
917 ,hr_org_id hr_organization_information.organization_id%type) IS
918 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4,
919 hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
920 FROM hr_organization_units o1
921 , hr_organization_information hoi1
922 , hr_organization_information hoi2
923 WHERE o1.business_group_id =bg_id
924 AND hoi1.organization_id = o1.organization_id
925 AND hoi1.organization_id = hr_org_id
926 AND hoi1.org_information1 = 'HR_ORG'
927 AND hoi1.org_information_context = 'CLASS'
928 AND o1.organization_id =hoi2.organization_id
929 AND hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS' ;
930
931 rg_csr_get_hr_org_info csr_get_hr_org_info%rowtype;
932
933 --bug 10367494
934 CURSOR csr_chk_2001 (p_se_no hr_organization_information.ORG_INFORMATION5%type) IS
935
936 SELECT 1
937 FROM pay_action_information pai
938 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
939 AND pai.action_information1 = 'PYDKEINA'
940 AND pai.action_information2 = to_char(g_payroll_action_id)
941 AND pai.action_information3 IN ('2001')
942 AND pai.action_information4 = p_se_no;
943
944 /* Payroll Time period */
945 CURSOR csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
946 IS
947 SELECT ptp.start_date, ptp.end_date,
948 default_dd_date,
949 decode(PERIOD_TYPE
950 ,'Calendar Month','1'
951 ,'Bi-Week' ,'2'
952 ,'Week' ,'3'
953 ,'Lunar Month' ,'4') PAYROLL_PERIOD
954 from per_time_periods ptp
955 WHERE payroll_id = p_payroll_id
956 AND time_period_id = p_payroll_period;
957
958 rg_csr_pay_periods csr_pay_periods%rowtype;
959
960 /* Cursor to get the details of the legal employer */
961 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
962 IS
963 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
964 FROM hr_organization_units o1
965 , hr_organization_information hoi1
966 , hr_organization_information hoi2
967 WHERE o1.business_group_id =g_business_group_id
968 AND hoi1.organization_id = o1.organization_id
969 AND hoi1.organization_id = csr_v_legal_emp_id
970 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
971 AND hoi1.org_information_context = 'CLASS'
972 AND o1.organization_id =hoi2.organization_id
973 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
974
975 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
976
977 CURSOR csr_location_info (p_location_id hr_location_extra_info.location_id%TYPE) IS
978 SELECT lei_information1
979 FROM hr_location_extra_info
980 WHERE location_id = p_location_id
981 AND information_type='DK_LOCATION_INFO';
982 /* 9489806 */
983 CURSOR csr_asg_payroll
984 (c_canonical_start_date DATE,
985 c_canonical_end_date DATE,
986 c_assignment_id NUMBER
987 ) IS
988 SELECT DISTINCT
989 ppf.payroll_id,
990 ptp.time_period_id,
991 ppf.payroll_name,
992 ptp.period_name
993
994 FROM
995 pay_payrolls_f ppf,
996 per_assignments_f paf,
997 per_time_periods ptp
998 WHERE paf.payroll_id = ppf.payroll_id
999 AND paf.assignment_id=c_assignment_id
1000 AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
1001 AND ppf.effective_start_date <= c_canonical_start_date and ppf.effective_end_date >= c_canonical_end_date
1002 AND ptp.payroll_id=ppf.payroll_id
1003 AND ptp.end_date BETWEEN c_canonical_start_date AND c_canonical_end_date
1004 AND ppf.business_group_id = g_business_group_id
1005 AND ptp.end_date between paf.effective_start_date and paf.effective_end_date;
1006
1007 rg_csr_location_info csr_location_info%ROWTYPE;
1008
1009 /* Cursor to retrieve Element - Employee ATP*/
1010 CURSOR csr_get_atp_table_value(p_assignment_id NUMBER , p_start_date DATE , p_end_date DATE ) IS
1011 SELECT eev1.screen_entry_value screen_entry_value
1012 FROM per_all_assignments_f asg1
1013 ,per_all_people_f per
1014 ,pay_element_links_f el
1015 ,pay_element_types_f et
1016 ,pay_input_values_f iv1
1017 ,pay_element_entries_f ee
1018 ,pay_element_entry_values_f eev1
1019 WHERE asg1.assignment_id = p_assignment_id
1020 AND per.person_id = asg1.person_id
1021 AND et.element_name = 'Employee ATP'
1022 AND et.legislation_code = 'DK'
1023 AND iv1.element_type_id = et.element_type_id
1024 AND iv1.name = 'ATP Table'
1025 AND el.business_group_id = per.business_group_id
1026 AND el.element_type_id = et.element_type_id
1027 AND ee.element_link_id = el.element_link_id
1028 AND ee.assignment_id = asg1.assignment_id
1029 AND eev1.element_entry_id = ee.element_entry_id
1030 AND eev1.input_value_id = iv1.input_value_id
1031 AND asg1.effective_end_date >= p_start_date
1032 AND asg1.effective_start_date <= p_end_date
1033 AND per.effective_end_date >= p_start_date
1034 AND per.effective_start_date <= p_end_date
1035 AND ee.effective_end_date >= p_start_date
1036 AND ee.effective_start_date <= p_end_date
1037 AND ((eev1.effective_start_date <= p_start_date
1038 AND eev1.effective_end_date >= p_start_date )
1039 OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
1040 AND eev1.effective_end_date >= p_end_date ));
1041
1042 rg_csr_get_atp_table_value csr_get_atp_table_value%rowtype;
1043
1044 /* Cursor to retrieve Element - Tax Card*/
1045 CURSOR csr_get_tax_card_details(p_assignment_id NUMBER , p_start_date DATE , p_end_date DATE ) IS
1046 SELECT ee.effective_start_date, eev1.screen_entry_value
1047 FROM per_all_assignments_f asg1
1048 ,per_all_people_f per
1049 ,pay_element_links_f el
1050 ,pay_element_types_f et
1051 ,pay_input_values_f iv1
1052 ,pay_element_entries_f ee
1053 ,pay_element_entry_values_f eev1
1054 WHERE asg1.assignment_id = p_assignment_id
1055 AND per.person_id = asg1.person_id
1056 AND et.element_name = 'Tax Card'
1057 AND et.legislation_code = 'DK'
1058 AND iv1.element_type_id = et.element_type_id
1059 AND iv1.name = 'Tax Card Type'
1060 AND el.business_group_id = per.business_group_id
1061 AND el.element_type_id = et.element_type_id
1062 AND ee.element_link_id = el.element_link_id
1063 AND ee.assignment_id = asg1.assignment_id
1064 AND eev1.element_entry_id = ee.element_entry_id
1065 AND eev1.input_value_id = iv1.input_value_id
1066 AND asg1.effective_end_date >= p_start_date
1067 AND asg1.effective_start_date <= p_end_date
1068 AND per.effective_end_date >= p_start_date
1069 AND per.effective_start_date <= p_end_date
1070 AND ee.effective_end_date >= p_start_date
1071 AND ee.effective_start_date <= p_end_date
1072 AND ((eev1.effective_start_date <= p_start_date
1073 AND eev1.effective_end_date >= p_start_date )
1074 OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
1075 AND eev1.effective_end_date >= p_end_date ));
1076
1077 rg_csr_get_tax_card_details csr_get_tax_card_details%rowtype;
1078
1079 /* Cursor to know the terminator in payroll period */
1080 CURSOR csr_asg_terminator
1081 (p_asg_act_id NUMBER
1082 ,p_start_date DATE
1083 ,p_end_date DATE
1084 ,p_business_group_id NUMBER) IS
1085 SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
1086 FROM per_all_assignments_f paa
1087 ,pay_assignment_actions pac
1088 WHERE pac.assignment_action_id = p_asg_act_id
1089 AND paa.assignment_id = pac.assignment_id
1090 -- AND paa.EFFECTIVE_START_DATE <= p_end_date 10322958
1091 AND paa.EFFECTIVE_END_DATE > = p_start_date
1092 AND assignment_status_type_id IN
1093 (select assignment_status_type_id
1094 from per_assignment_status_types
1095 where per_system_status = 'ACTIVE_ASSIGN'
1096 and active_flag = 'Y'
1097 and (( legislation_code is null
1098 and business_group_id is null)
1099 OR (BUSINESS_GROUP_ID = p_business_group_id)));
1100
1101 rg_csr_asg_terminator csr_asg_terminator%rowtype;
1102
1103 CURSOR csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE) IS
1104 SELECT ue.creator_id
1105 FROM ff_user_entities ue, ff_database_items di
1106 WHERE di.user_name = csr_v_Balance_Name
1107 AND ue.user_entity_id = di.user_entity_id
1108 AND ue.legislation_code = 'DK'
1109 AND ue.business_group_id is NULL
1110 AND ue.creator_type = 'B';
1111 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
1112
1113 CURSOR csr_asg_action_id(assg_id NUMBER, pid NUMBER, tid NUMBER, le_id NUMBER) IS
1114 select MAX(pac.ASSIGNMENT_ACTION_ID) id
1115 from pay_assignment_actions pac, pay_payroll_actions ppa
1116 where ppa.action_type IN ('R','Q')
1117 and pac.payroll_action_id = ppa.payroll_action_id
1118 and pac.assignment_id= assg_id
1119 and ppa.payroll_id=pid
1120 and ppa.time_period_id=tid
1121 and pac.tax_unit_id=le_id;
1122 rg_csr_asg_action_id csr_asg_action_id%rowtype;
1123
1124 --Bug 8522052 record 6003
1125 CURSOR csr_asg_extra_info (p_assgt_id NUMBER) IS
1126 SELECT *
1127 FROM per_assignment_extra_info
1128 WHERE information_type = 'DK_EINCOME_INFO'
1129 AND assignment_id = p_assgt_id;
1130
1131 rg_csr_asg_extra_info csr_asg_extra_info%ROWTYPE;
1132
1133 CURSOR csr_prepaid_actions_present(p_payroll_action_id NUMBER,
1134 p_legal_employer_id NUMBER,
1135 p_payroll_id NUMBER,
1136 p_payroll_period NUMBER,
1137 l_canonical_start_date DATE,
1138 l_canonical_end_date DATE,
1139 l_assignment_id NUMBER)
1140 IS
1141 SELECT 1
1142 FROM pay_payroll_actions ppa
1143 ,pay_payroll_actions appa
1144 ,pay_payroll_actions appa2
1145 ,pay_assignment_actions act
1146 ,pay_assignment_actions act1
1147 ,pay_action_interlocks pai
1148 ,per_all_assignments_f as1
1149
1150 WHERE ppa.payroll_action_id = p_payroll_action_id
1151 AND appa.effective_date BETWEEN l_canonical_start_date
1152 AND l_canonical_end_date
1153 AND appa.action_type IN ('R','Q')
1154 AND act.payroll_action_id = appa.payroll_action_id
1155 AND appa.time_period_id = p_payroll_period
1156 AND appa.effective_date between l_canonical_start_date and l_canonical_end_date
1157 AND act.source_action_id IS NULL -- Master Action
1158 AND as1.assignment_id = act.assignment_id
1159 AND as1.payroll_id = p_payroll_id
1160 AND act.action_status IN ('C','S') -- 10229494
1161 AND act.assignment_action_id = pai.locked_action_id
1162 AND act1.assignment_action_id = pai.locking_action_id
1163 AND act1.action_status IN ('C','S') -- 10229494
1164 AND act1.payroll_action_id = appa2.payroll_action_id
1165 AND appa2.action_type IN ('P','U')
1166 AND appa2.effective_date BETWEEN l_canonical_start_date
1167 AND l_canonical_end_date
1168 -- Prepayments or Quickpay Prepayments
1169 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
1170 AND act.TAX_UNIT_ID = p_legal_employer_id
1171 AND as1.assignment_id = l_assignment_id;
1172
1173 --13463885
1174 CURSOR csr_use_hol_card(p_org_id NUMBER) is
1175 SELECT NVL(org_information5,'N')
1176 FROM hr_organization_information
1177 WHERE organization_id = p_org_id
1178 and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
1179
1180 CURSOR get_value_from_ddf(p_payroll_id NUMBER , p_date_earned DATE) IS
1181 SELECT PRL_INFORMATION1
1182 FROM pay_payrolls_f ppf
1183 WHERE PAYROLL_ID = p_payroll_id
1184 AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
1185
1186 CURSOR cur_max_asg_act_id(p_assignment_id NUMBER, p_start_date DATE, p_end_date DATE) IS
1187 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) asg_action_id
1188 FROM pay_assignment_actions paa,
1189 pay_payroll_actions ppa
1190 WHERE ppa.payroll_action_id = paa.payroll_action_id
1191 and paa.assignment_id = p_assignment_id
1192 and ppa.action_type in ('R','Q','I','B','V')
1193 and paa.source_action_id is null
1194 AND ppa.effective_date between p_start_date AND p_end_date;
1195
1196 CURSOR cur_table_val_exist_400(p_payroll_action_id number) IS
1197 SELECT 'Y'
1198 FROM pay_payroll_actions ppa
1199 , pay_user_tables put
1200 , pay_user_columns puc
1201 , pay_user_column_instances_f puci
1202 , pay_user_rows_f pur
1203 , pay_user_columns puc1
1204 , pay_user_column_instances_f puci1
1205 , pay_user_rows_f pur1
1206 WHERE ppa.payroll_action_id = p_payroll_action_id
1207 AND put.user_table_name = 'DK_PBS_DATA'
1208 AND put.LEGISLATION_CODE ='DK'
1209 AND puc.user_table_id = put.user_table_id
1210 AND puc.user_column_name = 'Information Type'
1211 AND puci.user_column_id = puc.user_column_id
1212 AND ppa.effective_date between puci.effective_start_date and puci.effective_end_date
1213 AND pur.user_row_id = puci.user_row_id
1214 AND ppa.effective_date between pur.effective_start_date and pur.effective_end_date
1215 and puc1.user_table_id = put.user_table_id
1216 and puc1.user_column_name = 'PBS Number'
1217 and puci1.user_column_id = puc1.user_column_id
1218 and puci1.business_group_id =ppa.business_group_id
1219 and ppa.effective_date between puci1.effective_start_date and puci1.effective_end_date
1220 and pur1.user_row_id = puci1.user_row_id
1221 and pur1.user_row_id = pur.user_row_id
1222 and ppa.effective_date between pur1.effective_start_date and pur1.effective_end_date
1223 and puci.value = '400';
1224
1225 l_usr_tbl_exist_400 VARCHAR2(1) := 'N';
1226
1227 --13463885
1228
1229 l_present_flag NUMBER:=0;
1230 l_assignment_action_id NUMBER;
1231 l_payroll_action_id NUMBER;
1232 l_ass_id NUMBER; /* 9489806 */
1233 l_payroll_id NUMBER;
1234 l_employment_type VARCHAR2(10):='00';
1235 l_green_land_code VARCHAR2(10):='000'; /* 8847591 */
1236 l_period_id NUMBER;
1237 l_action_context_id NUMBER;
1238 l_flag NUMBER := 0;
1239 l_action_info_id NUMBER;
1240 l_ovn NUMBER;
1241 l_dob VARCHAR2(8);
1242 l_sex VARCHAR2(10);
1243 l_tax_card_type VARCHAR2(5);
1244 l_payment_type VARCHAR2(5);
1245 l_source_text VARCHAR2(10);
1246 l_source_text2 VARCHAR2(10);
1247 l_org_type VARCHAR2(5);
1248 l_country_code varchar2(50);
1249 l_age_category varchar2(1);
1250 l_hourly_salaried varchar2(1):=null;
1251 l_start_date DATE;
1252 l_end_date DATE;
1253 l_assignment NUMBER:=null;
1254 l_primary VARCHAR2(2):=null;
1255 l_bal_date DATE;
1256 l_assignment_id NUMBER:=0;
1257 l_yes_no VARCHAR2(10);
1258 l_stationed_abroad VARCHAR2(10);
1259 l_style VARCHAR2(30);
1260 l_town_or_city VARCHAR2(30);
1261 l_canonical_start_date DATE; /* 9489806 */
1262 l_canonical_end_date DATE;
1263
1264 l_sp_bonus NUMBER(12,2);
1265 l_free_phone NUMBER(12,2);
1266 l_multimedia NUMBER(12,2); -- bug# 9079593 fix
1267 l_trivial_matter NUMBER(12,2); -- bug# 9169583 fix
1268 l_mileage NUMBER(12,2);
1269 -- l_b_income NUMBER(12,2);
1270 l_total_atp NUMBER(12,2);
1271 l_employer_atp NUMBER(12,2);
1272 l_board_lodge NUMBER(12,2);
1273 l_car NUMBER(12,2);
1274 /* 8861878 */
1275 --
1276 l_non_taxable_travel NUMBER(12,2);
1277 l_b_income_amb NUMBER(12,2);
1278 l_b_income_non_amb NUMBER(12,2);
1279 l_a_non_amb_income NUMBER(12,2);
1280 l_pension_sev_pay NUMBER(12,2);
1281 l_physical_gift_sev_pay NUMBER(12,2);
1282 --
1283 l_car_adj NUMBER(12,2);
1284 l_amb_pay_adj NUMBER(12,2);
1285 l_emp_atp_adj NUMBER(12,2);
1286 l_sp_bonus_adj NUMBER(12,2);
1287 l_free_phone_adj NUMBER(12,2);
1288 l_multimedia_adj NUMBER(12,2); -- bug# 9079593 fix
1289 l_trivial_matter_adj NUMBER(12,2); -- bug# 9169583 fix
1290 l_mileage_adj NUMBER(12,2);
1291 l_empr_atp_adj NUMBER(12,2);
1292 l_board_lodge_adj NUMBER(12,2);
1293 l_a_income_adj NUMBER(12,2);
1294 -- l_b_income_adj NUMBER(12,2);
1295 l_hourly_holiday_pay_adj NUMBER(12,2);
1296 l_monthly_holiday_pay_adj NUMBER(12,2);
1297 /* 8861878 */
1298 --
1299 l_non_taxable_travel_adj NUMBER(12,2);
1300 l_b_income_amb_adj NUMBER(12,2);
1301 l_b_income_non_amb_adj NUMBER(12,2);
1302 l_a_income_non_amb_adj NUMBER(12,2);
1303 l_pension_sev_pay_adj NUMBER(12,2);
1304 l_physical_gift_sev_pay_adj NUMBER(12,2);
1305 --
1306 l_tot_free_res_pay_adj NUMBER(12,2);
1307 l_tot_free_res_pay NUMBER(12,2); -- bug 12943072
1308
1309 l_total_amb NUMBER(12,2);
1310 l_hol_amb NUMBER(12,2);
1311 l_hol_amb_rep NUMBER(12,2);
1312 l_emp_amb NUMBER(12,2);
1313 l_tax NUMBER(12,2);
1314 l_emp_tax NUMBER(12,2);
1315 l_holiday_tax NUMBER(12,2);
1316 l_holiday_tax_pay NUMBER(12,2);
1317 l_a_income NUMBER(12,2);
1318
1319 /* 9945754-1 */
1320 --
1321 l_hourly_hol_income NUMBER(12,2);
1322 l_hourly_hol_income_adj NUMBER(12,2);
1323 l_sal_hol_income NUMBER(12,2);
1324 --
1325
1326 l_monthly_holiday_pay NUMBER(12,2);
1327 l_hourly_holiday_pay NUMBER(12,2);
1328 l_gross_income NUMBER(12,2);
1329 l_emp_atp NUMBER(12,2);
1330 l_amb_pay NUMBER(12,2);
1331 l_amb NUMBER(12,2);
1332 l_cvr_number hr_organization_information.org_information1%TYPE ;
1333 l_cpr_number VARCHAR2(15);
1334 l_pu_code hr_organization_information.ORG_INFORMATION6%type;
1335 l_total_atp_hours NUMBER;
1336 l_se_no hr_organization_information.ORG_INFORMATION5%type; -- bug 10367494
1337 l_company_terminating varchar2(1); -- bug 10367494
1338 l_flag1 NUMBER :=0 ;
1339 l_atp NUMBER;
1340 L_6005_SIGN VARCHAR2(1);
1341 l_bg_id NUMBER;
1342 l_org_id NUMBER;
1343 l_hd VARCHAR2(100);
1344 l_loc_id NUMBER := NULL;
1345 l_pension NUMBER := 0;
1346 l_g_dage_pay NUMBER :=0; -- 10269552
1347 l_corr_flag varchar(2) :=NULL; -- 12944028
1348
1349 --13463885
1350 l_no_of_hol_6005_0203 NUMBER(12,2);
1351
1352 l_net_hol_pay_0202 NUMBER(12,2);
1353 l_hol_pay_0202 NUMBER(12,2);
1354 l_hol_pay_0202_tax NUMBER(12,2);
1355 l_hol_pay_0202_amb NUMBER(12,2);
1356 --l_hol_pay_0202_sp NUMBER(12,2);
1357
1358 l_use_holiday_card VARCHAR2(10) := 'N';
1359 l_uhc_payroll VARCHAR2(10);
1360
1361 l_sal_hol_acr_amt NUMBER(12,2);
1362 l_sal_hol_acr_tax NUMBER(12,2);
1363 l_sal_hol_acr_amb NUMBER(12,2);
1364 l_sal_hol_acr_days NUMBER(12,2);
1365 l_net_hol_pay_6102_1 NUMBER(12,2);
1366 l_no_of_hol_6102_1 NUMBER(12,2);
1367
1368 l_sal_hol_cur_ent_amt NUMBER(12,2);
1369 l_sal_hol_cur_ent_tax NUMBER(12,2);
1370 l_sal_hol_cur_ent_amb NUMBER(12,2);
1371 l_sal_hol_cur_ent_days NUMBER(12,2);
1372 l_net_hol_pay_6102_2 NUMBER(12,2);
1373 l_no_of_hol_6102_2 NUMBER(12,2);
1374
1375 l_sal_hol_nxt_ent_amt NUMBER(12,2);
1376 l_sal_hol_nxt_ent_tax NUMBER(12,2);
1377 l_sal_hol_nxt_ent_amb NUMBER(12,2);
1378 l_sal_hol_nxt_ent_days NUMBER(12,2);
1379 l_net_hol_pay_6102_3 NUMBER(12,2);
1380 l_no_of_hol_6102_3 NUMBER(12,2);
1381
1382 l_sign_net_hol_pay_6102_1 VARCHAR2(1);
1383 l_sign_net_hol_pay_6102_2 VARCHAR2(1);
1384 l_sign_net_hol_pay_6102_3 VARCHAR2(1);
1385
1386 l_accr_yr VARCHAR2(10);
1387 l_last_yr VARCHAR2(10);
1388 l_curr_entit_yr VARCHAR2(10);
1389 l_nxt_entit_yr VARCHAR2(10);
1390
1391 l_max_asg_act_id NUMBER;
1392 --13463885
1393
1394
1395 PROCEDURE rec_6001(
1396 pid pay_action_information.action_information1%type
1397 ,aid pay_action_information.action_information1%type
1398 ,code pay_action_information.action_information1%type
1399 ,amt pay_action_information.action_information1%type
1400 ,sgn pay_action_information.action_information1%type
1401 ,corr pay_action_information.action_information1%type
1402 ,p_payroll_id pay_action_information.action_information1%type
1403 ,p_employment_type pay_action_information.action_information1%type
1404 ,p_period_id pay_action_information.action_information1%type
1405 ,p_green_land_code pay_action_information.action_information1%type /* 8847591 */
1406 ,p_se_no varchar2 )
1407 IS
1408 l_sgn pay_action_information.action_information1%type:=NULL;
1409 amt1 pay_action_information.action_information1%type:=NULL;
1410 BEGIN
1411
1412 if(sgn='-1') then
1413 l_sgn:='-';
1414 else
1415 l_sgn:='+';
1416 end if;
1417
1418 amt1:=lpad(amt,16,'0');
1419 --Record - 6001
1420 pay_action_information_api.create_action_information (
1421 p_action_information_id => l_action_info_id
1422 ,p_action_context_id => p_assignment_action_id
1423 ,p_action_context_type => 'AAP'
1424 ,p_object_version_number => l_ovn
1425 ,p_effective_date => g_effective_date
1426 ,p_source_id => NULL
1427 ,p_source_text => NULL
1428 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1429 ,p_action_information1 => 'PYDKEINA'
1430 ,p_action_information2 => l_payroll_action_id
1431 ,p_action_information3 => '6001'
1432 ,p_action_information4 => pid --rg_csr_get_person_details.PERSON_ID
1433 ,p_action_information5 => aid --rg_csr_get_person_details.assignment_ID
1434 ,p_action_information6 => code -- code according to the element entry reported
1435 ,p_action_information7 => amt1 -- element value
1436 ,p_action_information8 => l_sgn -- (+/-)
1437 ,p_action_information9 => null
1438 ,p_action_information10 => null
1439 ,p_action_information11 => p_se_no
1440 ,p_action_information12 => null
1441 ,p_action_information13 => null
1442 ,p_action_information14 => null
1443 ,p_action_information15 => null
1444 ,p_action_information16 => null
1445 ,p_action_information17 => null
1446 ,p_action_information18 => null
1447 ,p_action_information19 => null
1448 ,p_action_information20 => null
1449 ,p_action_information21 => null
1450 ,p_action_information22 => null
1451 ,p_action_information23 => null
1452 ,p_action_information24 => p_period_id /* 9489806 */
1453 ,p_action_information25 => p_payroll_id /* 9489806 */
1454 ,p_action_information26 => p_employment_type /* 9489806 to link respective 6000 with its 5000 rec */
1455 ,p_action_information27 => p_green_land_code /* green land code 8847591 */
1456 ,p_action_information28 => null
1457 ,p_action_information29 => corr
1458 ,p_action_information30 => null);
1459 END;
1460
1461 /* 9489806 */
1462 PROCEDURE rec_5000(
1463 p_payroll_action_id NUMBER
1464 ,p_payroll_id pay_action_information.action_information1%type
1465 ,p_employement_type pay_action_information.action_information1%type
1466 ,p_period_id pay_action_information.action_information1%type
1467 ,p_green_land_code pay_action_information.action_information1%type /* 8847591 */
1468 ,p_se_no varchar2 )
1469 IS
1470 CURSOR csr_chk_5000 IS
1471 SELECT *
1472 FROM pay_action_information pai
1473 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1474 AND pai.action_information1 = 'PYDKEINA'
1475 AND pai.action_information2 = to_char(p_payroll_action_id)
1476 AND pai.action_information3 IN ('5000')
1477 AND pai.action_information25 =p_payroll_id
1478 AND pai.action_information24 =p_period_id
1479 AND pai.action_information9='00'; /* 9587046 */
1480 rec_csr_chk_5000 csr_chk_5000%ROWTYPE;
1481
1482 CURSOR csr_chk_5000_emp IS
1483 SELECT 1
1484 FROM pay_action_information pai
1485 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1486 AND pai.action_information1 = 'PYDKEINA'
1487 AND pai.action_information2 = to_char(p_payroll_action_id)
1488 AND pai.action_information3 IN ('5000')
1489 AND pai.action_information25 =p_payroll_id
1490 AND pai.action_information9 =p_employement_type
1491 AND pai.action_information24 =p_period_id
1492 AND pai.action_information8 =p_green_land_code
1493 and pai.action_information11 =p_se_no; /* 8847591 */
1494
1495 l_flag NUMBER:=0;
1496 l_action_info_id NUMBER;
1497 l_ovn NUMBER;
1498 l_unique_id VARCHAR2(16);
1499 BEGIN
1500 hr_utility.set_location(' Entered Procedure rec_5000',500);
1501 hr_utility.set_location('rec_5000:p_payroll_action_id'|| p_payroll_action_id ,501);
1502 hr_utility.set_location('rec_5000:p_payroll_id'|| p_payroll_id ,501);
1503 hr_utility.set_location('rec_5000:p_employement_type'|| p_employement_type ,501);
1504 hr_utility.set_location('rec_5000:p_green_land_code'|| p_green_land_code ,501); /* 8847591 */
1505 OPEN csr_chk_5000_emp;
1506 FETCH csr_chk_5000_emp into l_flag;
1507 CLOSE csr_chk_5000_emp;
1508 hr_utility.set_location('rec_5000:After fetch l_flag'|| l_flag ,501);
1509
1510 IF l_flag<>1 THEN
1511 hr_utility.set_location('rec_5000:Entered l_flag IF'|| l_flag ,501);
1512 OPEN csr_chk_5000;
1513 FETCH csr_chk_5000 INTO rec_csr_chk_5000;
1514 CLOSE csr_chk_5000;
1515 IF p_green_land_code='000' THEN /* 8847591 */
1516 l_unique_id:=substr(rec_csr_chk_5000.action_information10,1,8);
1517 ELSE
1518 l_unique_id:=substr(p_green_land_code,2,2)||substr(rec_csr_chk_5000.action_information10,3,6); /* in case of green land code firts 2 digits green land code..next 6 digits of payroll action id */
1519 END IF;
1520 l_unique_id:=l_unique_id || lpad(substr(p_period_id,-least(length(p_period_id),5)),5,0) || p_employement_type||'0';
1521
1522 pay_action_information_api.create_action_information (
1523 p_action_information_id => l_action_info_id
1524 ,p_action_context_id => p_payroll_action_id
1525 ,p_action_context_type => 'PA'
1526 ,p_object_version_number => l_ovn
1527 ,p_effective_date => g_effective_date
1528 ,p_source_id => NULL
1529 ,p_source_text => NULL
1530 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1531 ,p_action_information1 => 'PYDKEINA'
1532 ,p_action_information2 => rec_csr_chk_5000.action_information2
1533 ,p_action_information3 => '5000'
1534 ,p_action_information4 => rec_csr_chk_5000.action_information4 -- to_char(csr_rec.start_date,'yyyymmdd')
1535 ,p_action_information5 => rec_csr_chk_5000.action_information5 --to_char(csr_rec.end_date,'yyyymmdd')
1536 ,p_action_information6 => rec_csr_chk_5000.action_information6 --to_char(csr_rec.default_dd_date,'yyyymmdd')
1537 ,p_action_information7 => rec_csr_chk_5000.action_information7 --'B'
1538 ,p_action_information8 => p_green_land_code -- '000' --Greenland code /* 8847591 */
1539 ,p_action_information9 => p_employement_type -- employee code
1540 ,p_action_information10 => l_unique_id
1541 ,p_action_information11 => p_se_no -- 10367494
1542 ,p_action_information12 => null
1543 ,p_action_information13 => null
1544 ,p_action_information14 => null
1545 ,p_action_information15 => null
1546 ,p_action_information16 => null
1547 ,p_action_information17 => null
1548 ,p_action_information18 => null
1549 ,p_action_information19 => null
1550 ,p_action_information20 => null
1551 ,p_action_information21 => null
1552 ,p_action_information22 => null
1553 ,p_action_information23 => null
1554 ,p_action_information24 => p_period_id /* 9489806 */
1555 ,p_action_information25 => rec_csr_chk_5000.action_information25 /* 9489806 */
1556 ,p_action_information26 => null
1557 ,p_action_information27 => null
1558 ,p_action_information28 => null
1559 ,p_action_information29 => null
1560 ,p_action_information30 => null);
1561 hr_utility.set_location('rec_5000:Last statement l_flag IF'|| l_flag ,501);
1562
1563
1564 END IF;
1565 hr_utility.set_location(' Leaving Procedure rec_5000',500);
1566
1567 END;
1568
1569 PROCEDURE rec_5000R(
1570 p_payroll_action_id NUMBER
1571 ,p_payroll_id pay_action_information.action_information1%type
1572 ,p_employement_type pay_action_information.action_information1%type
1573 ,p_period_id pay_action_information.action_information1%type
1574 ,p_green_land_code pay_action_information.action_information1%type /* 8847591 */
1575 ,p_se_no varchar2 )
1576 IS
1577 CURSOR csr_chk_5000 IS
1578 SELECT *
1579 FROM pay_action_information pai
1580 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1581 AND pai.action_information1 = 'PYDKEINA'
1582 AND pai.action_information2 = to_char(p_payroll_action_id)
1583 AND pai.action_information3 IN ('5000')
1584 AND pai.action_information25 =p_payroll_id
1585 AND pai.action_information24 =p_period_id
1586 AND pai.action_information9='00'; /* 9587046 */
1587 rec_csr_chk_5000 csr_chk_5000%ROWTYPE;
1588
1589 CURSOR csr_chk_5000_emp IS
1590 SELECT 1
1591 FROM pay_action_information pai
1592 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1593 AND pai.action_information1 = 'PYDKEINA'
1594 AND pai.action_information2 = to_char(p_payroll_action_id)
1595 AND pai.action_information3 IN ('5000R')
1596 AND pai.action_information25 =p_payroll_id
1597 AND pai.action_information9 =p_employement_type
1598 AND pai.action_information24 =p_period_id
1599 AND pai.action_information8 =p_green_land_code
1600 and pai.action_information11 =p_se_no; /* 8847591 */
1601
1602 l_flag NUMBER:=0;
1603 l_action_info_id NUMBER;
1604 l_ovn NUMBER;
1605 l_unique_id VARCHAR2(16);
1606 BEGIN
1607 hr_utility.set_location(' Entered Procedure rec_5000R',500);
1608 hr_utility.set_location('rec_5000R:p_payroll_action_id'|| p_payroll_action_id ,501);
1609 hr_utility.set_location('rec_5000R:p_payroll_id'|| p_payroll_id ,501);
1610 hr_utility.set_location('rec_5000R:p_employement_type'|| p_employement_type ,501);
1611 hr_utility.set_location('rec_5000R:p_green_land_code'|| p_green_land_code ,501); /* 8847591 */
1612 OPEN csr_chk_5000_emp;
1613 FETCH csr_chk_5000_emp into l_flag;
1614 CLOSE csr_chk_5000_emp;
1615 hr_utility.set_location('rec_5000R:After fetch l_flag'|| l_flag ,501);
1616
1617 IF l_flag<>1 THEN
1618 hr_utility.set_location('rec_5000:Entered l_flag IF'|| l_flag ,501);
1619 OPEN csr_chk_5000;
1620 FETCH csr_chk_5000 INTO rec_csr_chk_5000;
1621 CLOSE csr_chk_5000;
1622 IF p_green_land_code='000' THEN /* 8847591 */
1623 l_unique_id:=substr(rec_csr_chk_5000.action_information10,1,8);
1624 ELSE
1625 l_unique_id:=substr(p_green_land_code,2,2)||substr(rec_csr_chk_5000.action_information10,3,6); /* in case of green land code firts 2 digits green land code..next 6 digits of payroll action id */
1626 END IF;
1627
1628 l_unique_id:=l_unique_id || lpad(substr(p_period_id,-least(length(p_period_id),5)),5,0) || p_employement_type||'1';
1629 pay_action_information_api.create_action_information (
1630 p_action_information_id => l_action_info_id
1631 ,p_action_context_id => p_payroll_action_id
1632 ,p_action_context_type => 'PA'
1633 ,p_object_version_number => l_ovn
1634 ,p_effective_date => g_effective_date
1635 ,p_source_id => NULL
1636 ,p_source_text => NULL
1637 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1638 ,p_action_information1 => 'PYDKEINA'
1639 ,p_action_information2 => rec_csr_chk_5000.action_information2
1640 ,p_action_information3 => '5000R'
1641 ,p_action_information4 => rec_csr_chk_5000.action_information4 -- to_char(csr_rec.start_date,'yyyymmdd')
1642 ,p_action_information5 => rec_csr_chk_5000.action_information5 --to_char(csr_rec.end_date,'yyyymmdd')
1643 ,p_action_information6 => rec_csr_chk_5000.action_information6 --to_char(csr_rec.default_dd_date,'yyyymmdd')
1644 ,p_action_information7 => rec_csr_chk_5000.action_information7 --'B'
1645 ,p_action_information8 => p_green_land_code -- '000' --Greenland code /* 8847591 */
1646 ,p_action_information9 => p_employement_type -- employee code
1647 ,p_action_information10 => l_unique_id -- should be reported in field 4 /* Bug fix 7579265 */
1648 ,p_action_information11 => p_se_no
1649 ,p_action_information12 => null
1650 ,p_action_information13 => null
1651 ,p_action_information14 => null
1652 ,p_action_information15 => null
1653 ,p_action_information16 => null
1654 ,p_action_information17 => null
1655 ,p_action_information18 => null
1656 ,p_action_information19 => null
1657 ,p_action_information20 => null
1658 ,p_action_information21 => null
1659 ,p_action_information22 => null
1660 ,p_action_information23 => null
1661 ,p_action_information24 => p_period_id /* 9489806 */
1662 ,p_action_information25 => rec_csr_chk_5000.action_information25 /* 9489806 */
1663 ,p_action_information26 => null
1664 ,p_action_information27 => null
1665 ,p_action_information28 => null
1666 ,p_action_information29 => null
1667 ,p_action_information30 => null);
1668 hr_utility.set_location('rec_5000R:Last statement l_flag IF'|| l_flag ,501);
1669
1670
1671 END IF;
1672 hr_utility.set_location(' Leaving Procedure rec_5000R',500);
1673
1674 END;
1675 PROCEDURE rec_6000R(
1676 p_assignment_action_id NUMBER
1677 ,p_payroll_id pay_action_information.action_information1%type
1678 ,p_employement_type pay_action_information.action_information1%type
1679 ,p_period_id pay_action_information.action_information1%type
1680 ,p_assignment_id pay_action_information.action_information1%type
1681 ,p_green_land_code pay_action_information.action_information1%type /* 8847591 */
1682 ,p_se_no varchar2 )
1683 IS
1684 CURSOR csr_chk_6000 IS
1685 SELECT *
1686 FROM pay_action_information pai
1687 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1688 AND pai.action_information1 = 'PYDKEINA'
1689 AND pai.action_context_id = to_char(p_assignment_action_id)
1690 AND pai.action_information3 IN ('6000')
1691 AND pai.action_information25 =p_payroll_id
1692 AND pai.action_information24 =p_period_id;
1693 rec_csr_chk_6000 csr_chk_6000%ROWTYPE;
1694
1695 CURSOR csr_chk_6000_emp IS
1696 SELECT 1
1697 FROM pay_action_information pai
1698 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1699 AND pai.action_information1 = 'PYDKEINA'
1700 AND pai.action_information2 = to_char(p_assignment_action_id)
1701 AND pai.action_information3 IN ('6000R')
1702 AND pai.action_information25 =p_payroll_id
1703 AND pai.action_information26 =p_employement_type /* 8847591check */
1704 AND pai.action_information24 =p_period_id
1705 AND pai.action_information27 =p_green_land_code
1706 and pai.action_information11 =p_se_no ; /* 8847591 */
1707
1708 l_flag NUMBER:=0;
1709 l_action_info_id NUMBER;
1710 l_ovn NUMBER;
1711 BEGIN
1712 hr_utility.set_location(' Entered Procedure rec_6000R',500);
1713 hr_utility.set_location('rec_6000R:p_assignment_action_id'|| p_assignment_action_id ,501);
1714 hr_utility.set_location('rec_6000R:p_payroll_id'|| p_payroll_id ,501);
1715 hr_utility.set_location('rec_6000R:p_employement_type'|| p_employement_type ,501);
1716 hr_utility.set_location('rec_6000R:p_green_land_code'|| p_green_land_code ,501);
1717 OPEN csr_chk_6000_emp;
1718 FETCH csr_chk_6000_emp into l_flag;
1719 CLOSE csr_chk_6000_emp;
1720 hr_utility.set_location('rec_6000R:After fetch l_flag'|| l_flag ,501);
1721
1722 IF l_flag<>1 THEN
1723 hr_utility.set_location('rec_6000:Entered l_flag IF'|| l_flag ,501);
1724 OPEN csr_chk_6000;
1725 FETCH csr_chk_6000 INTO rec_csr_chk_6000;
1726 CLOSE csr_chk_6000;
1727 pay_action_information_api.create_action_information (
1728 p_action_information_id => l_action_info_id
1729 ,p_action_context_id => p_assignment_action_id
1730 ,p_action_context_type => 'AAP'
1731 ,p_object_version_number => l_ovn
1732 ,p_effective_date => g_effective_date
1733 ,p_source_id => NULL
1734 ,p_source_text => NULL
1735 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1736 ,p_action_information1 => 'PYDKEINA'
1737 ,p_action_information2 => rec_csr_chk_6000.action_information2
1738 ,p_action_information3 => '6000R'
1739 ,p_action_information4 => rec_csr_chk_6000.action_information4 -- to_char(csr_rec.start_date,'yyyymmdd')
1740 ,p_action_information5 => rec_csr_chk_6000.action_information5 --to_char(csr_rec.end_date,'yyyymmdd')
1741 ,p_action_information6 => rec_csr_chk_6000.action_information6 --to_char(csr_rec.default_dd_date,'yyyymmdd')
1742 ,p_action_information7 => rec_csr_chk_6000.action_information7 --'B'
1743 ,p_action_information8 => rec_csr_chk_6000.action_information8 -- '000' --Greenland code
1744 ,p_action_information9 => rec_csr_chk_6000.action_information9 -- employee code
1745 ,p_action_information10 => rec_csr_chk_6000.action_information10 -- should be reported in field 4 /* Bug fix 7579265 */
1746 ,p_action_information11 => rec_csr_chk_6000.action_information11
1747 ,p_action_information12 => null
1748 ,p_action_information13 => null
1749 ,p_action_information14 => null
1750 ,p_action_information15 => null
1751 ,p_action_information16 => null
1752 ,p_action_information17 => null
1753 ,p_action_information18 => null
1754 ,p_action_information19 => null
1755 ,p_action_information20 => null
1756 ,p_action_information21 => null
1757 ,p_action_information22 => null
1758 ,p_action_information23 => null
1759 ,p_action_information24 => rec_csr_chk_6000.action_information24 /* 9489806 */
1760 ,p_action_information25 => rec_csr_chk_6000.action_information25 /* 9489806 */
1761 ,p_action_information26 => p_employement_type /* 8847591check */
1762 ,p_action_information27 => p_green_land_code /* 8847591 */
1763 ,p_action_information28 => null
1764 ,p_action_information29 => null
1765 ,p_action_information30 => null);
1766 hr_utility.set_location('rec_6000R:Last statement l_flag IF'|| l_flag ,501);
1767
1768
1769 END IF;
1770 hr_utility.set_location(' Leaving Procedure rec_6000R',500);
1771
1772 END;
1773 PROCEDURE rec_8001R(
1774 p_assignment_action_id NUMBER
1775 ,p_payroll_id pay_action_information.action_information1%type
1776 ,p_employement_type pay_action_information.action_information1%type
1777 ,p_period_id pay_action_information.action_information1%type
1778 ,p_person_id pay_action_information.action_information1%type
1779 ,p_green_land_code pay_action_information.action_information1%type /* 8847591 */
1780 )
1781 IS
1782 CURSOR csr_chk_8001 IS
1783 SELECT *
1784 FROM pay_action_information pai
1785 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1786 AND pai.action_information1 = 'PYDKEINA'
1787 AND pai.action_context_id = to_char(p_assignment_action_id)
1788 AND pai.action_information4=to_char(p_person_id)
1789 AND pai.action_information3 IN ('8001')
1790 AND pai.action_information25 =p_payroll_id
1791 AND pai.action_information24 =p_period_id;
1792 rec_csr_chk_8001 csr_chk_8001%ROWTYPE;
1793
1794 CURSOR csr_chk_8001_emp IS
1795 SELECT 1
1796 FROM pay_action_information pai
1797 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1798 AND pai.action_information1 = 'PYDKEINA'
1799 AND pai.action_information2 = to_char(p_assignment_action_id)
1800 AND pai.action_information3 IN ('8001R')
1801 AND pai.action_information25 =p_payroll_id
1802 AND pai.action_information26 =p_employement_type /* 8847591check */
1803 AND pai.action_information24 =p_period_id
1804 AND pai.action_information27 =p_green_land_code; /* 8847591 */
1805
1806 l_flag NUMBER:=0;
1807 l_action_info_id NUMBER;
1808 l_ovn NUMBER;
1809 BEGIN
1810 hr_utility.set_location(' Entered Procedure rec_8001R',500);
1811 hr_utility.set_location('rec_8001R:p_assignment_action_id'|| p_assignment_action_id ,501);
1812 hr_utility.set_location('rec_8001R:p_payroll_id'|| p_payroll_id ,501);
1813 hr_utility.set_location('rec_8001R:p_employement_type'|| p_employement_type ,501);
1814 hr_utility.set_location('rec_8001R:p_green_land_code'|| p_green_land_code ,501);
1815 OPEN csr_chk_8001_emp;
1816 FETCH csr_chk_8001_emp into l_flag;
1817 CLOSE csr_chk_8001_emp;
1818 hr_utility.set_location('rec_8001R:After fetch l_flag'|| l_flag ,501);
1819
1820 IF l_flag<>1 THEN
1821 hr_utility.set_location('rec_8001:Entered l_flag IF'|| l_flag ,501);
1822 OPEN csr_chk_8001;
1823 FETCH csr_chk_8001 INTO rec_csr_chk_8001;
1824 CLOSE csr_chk_8001;
1825 pay_action_information_api.create_action_information (
1826 p_action_information_id => l_action_info_id
1827 ,p_action_context_id => p_assignment_action_id
1828 ,p_action_context_type => 'AAP'
1829 ,p_object_version_number => l_ovn
1830 ,p_effective_date => g_effective_date
1831 ,p_source_id => NULL
1832 ,p_source_text => NULL
1833 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1834 ,p_action_information1 => 'PYDKEINA'
1835 ,p_action_information2 => rec_csr_chk_8001.action_information2
1836 ,p_action_information3 => '8001R'
1837 ,p_action_information4 => rec_csr_chk_8001.action_information4 -- to_char(csr_rec.start_date,'yyyymmdd')
1838 ,p_action_information5 => rec_csr_chk_8001.action_information5 --to_char(csr_rec.end_date,'yyyymmdd')
1839 ,p_action_information6 => rec_csr_chk_8001.action_information6 --to_char(csr_rec.default_dd_date,'yyyymmdd')
1840 ,p_action_information7 => rec_csr_chk_8001.action_information7 --'B'
1841 ,p_action_information8 => rec_csr_chk_8001.action_information8 -- '000' --Greenland code
1842 ,p_action_information9 => rec_csr_chk_8001.action_information9 -- employee code
1843 ,p_action_information10 => rec_csr_chk_8001.action_information10 -- should be reported in field 4 /* Bug fix 7579265 */
1844 ,p_action_information11 => rec_csr_chk_8001.action_information11 --15921349
1845 ,p_action_information12 => rec_csr_chk_8001.action_information12 --15921349
1846 ,p_action_information13 => null
1847 ,p_action_information14 => null
1848 ,p_action_information15 => null
1849 ,p_action_information16 => null
1850 ,p_action_information17 => null
1851 ,p_action_information18 => null
1852 ,p_action_information19 => null
1853 ,p_action_information20 => null
1854 ,p_action_information21 => null
1855 ,p_action_information22 => null
1856 ,p_action_information23 => null
1857 ,p_action_information24 => rec_csr_chk_8001.action_information24 /* 9489806 */
1858 ,p_action_information25 => rec_csr_chk_8001.action_information25 /* 9489806 */
1859 ,p_action_information26 => p_employement_type /* 8847591check */
1860 ,p_action_information27 => p_green_land_code /* 8847591 */
1861 ,p_action_information28 => null
1862 ,p_action_information29 => null
1863 ,p_action_information30 => null);
1864 hr_utility.set_location('rec_8001R:Last statement l_flag IF'|| l_flag ,501);
1865
1866
1867 END IF;
1868 hr_utility.set_location(' Leaving Procedure rec_8001R',500);
1869
1870 END;
1871
1872
1873
1874
1875
1876
1877 BEGIN
1878 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 1');
1879 /*Initializing all balance variables*/
1880 l_sp_bonus :=0;
1881 l_free_phone :=0;
1882 l_multimedia := 0; -- bug# 9079593 fix
1883 l_trivial_matter :=0; -- bug# 9169583 fix
1884 l_mileage :=0;
1885 -- l_b_income :=0;
1886 l_total_atp :=0;
1887 l_employer_atp :=0;
1888 l_board_lodge :=0;
1889 l_car :=0;
1890 l_car_adj := 0;
1891 l_amb_pay_adj := 0;
1892 l_emp_atp_adj := 0;
1893 l_sp_bonus_adj := 0;
1894 l_free_phone_adj := 0;
1895 l_multimedia_adj := 0; -- bug# 9079593 fix
1896 l_trivial_matter_adj := 0; -- bug# 9169583 fix
1897 l_mileage_adj := 0;
1898 l_empr_atp_adj := 0;
1899 l_board_lodge_adj := 0;
1900 l_a_income_adj := 0;
1901 -- l_b_income_adj := 0;
1902 l_hourly_holiday_pay_adj := 0;
1903 l_monthly_holiday_pay_adj := 0;
1904 l_total_amb :=0;
1905 l_hol_amb :=0;
1906 l_emp_amb :=0;
1907 l_tax :=0;
1908 l_emp_tax :=0;
1909 l_holiday_tax :=0;
1910 l_a_income :=0;
1911
1912 /* 9945754-1 */
1913 --
1914 l_hourly_hol_income :=0;
1915 l_hourly_hol_income_adj :=0;
1916 l_sal_hol_income :=0;
1917 --
1918
1919 l_monthly_holiday_pay :=0;
1920 l_hourly_holiday_pay :=0;
1921 l_gross_income :=0;
1922 l_emp_atp :=0;
1923 l_amb_pay :=0;
1924 l_amb :=0;
1925 l_bg_id :=0;
1926 l_org_id :=0;
1927 l_pension := 0;
1928 l_g_dage_pay :=0; -- 10269552
1929
1930 BEGIN
1931
1932 SELECT payroll_action_id, assignment_id
1933 INTO l_payroll_action_id,l_ass_id /* 9489806 */
1934 FROM pay_assignment_actions
1935 WHERE assignment_action_id=p_assignment_action_id;
1936 END;
1937
1938 IF g_debug THEN
1939 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
1940 END IF;
1941
1942 /* Fetching report parameters */
1943 PAY_DK_ARCHIVE_EIN.GET_ALL_PARAMETERS(
1944 l_payroll_action_id
1945 ,g_business_group_id
1946 ,g_legal_employer_id
1947 ,g_effective_date
1948 ,g_payroll_id
1949 -- ,g_payroll_period
1950 ,g_payroll_type
1951 ,g_start_date
1952 ,g_end_date
1953 ,g_test_submission
1954 ,g_company_terminating
1955 ,g_sender_id) ;
1956 l_canonical_start_date := fnd_date.canonical_to_date(g_start_date);
1957 l_canonical_end_date := fnd_date.canonical_to_date(g_end_date);
1958
1959 --13463885
1960 OPEN csr_use_hol_card(g_legal_employer_id);
1961 FETCH csr_use_hol_card INTO l_use_holiday_card;
1962 CLOSE csr_use_hol_card;
1963
1964 OPEN cur_table_val_exist_400(l_payroll_action_id);
1965 FETCH cur_table_val_exist_400 INTO l_usr_tbl_exist_400;
1966 CLOSE cur_table_val_exist_400;
1967
1968 fnd_file.put_line(fnd_file.log,'l_usr_tbl_exist_400:'||l_usr_tbl_exist_400);
1969 --13463885
1970
1971
1972 if(g_flag=0) then
1973 if(g_company_terminating='N') then
1974
1975 fnd_file.put_line(fnd_file.log,'Fetched report parameters');
1976
1977 hr_utility.set_location('ARCHIVE_CODE: l_assignment_id '|| l_ass_id,380);
1978 hr_utility.set_location('ARCHIVE_CODE: Before For loop csr_asg_payroll ',380);
1979
1980 For csr_asg_payroll_rec IN csr_asg_payroll(l_canonical_start_date,l_canonical_end_date,l_ass_id)
1981 LOOP
1982 --FETCH csr_asg_payroll INTO l_payroll_id,l_period_id;
1983 --CLOSE csr_asg_payroll;
1984 hr_utility.set_location('ARCHIVE_CODE: Payroll Name ' || csr_asg_payroll_rec.payroll_name,380);
1985 hr_utility.set_location('ARCHIVE_CODE: l_payroll_id ' || csr_asg_payroll_rec.payroll_id,380);
1986 hr_utility.set_location('ARCHIVE_CODE: Period Name ' || csr_asg_payroll_rec.period_name,380);
1987 hr_utility.set_location('ARCHIVE_CODE: time_period_id ' || csr_asg_payroll_rec.time_period_id,380);
1988 l_employment_type :='00';
1989 l_green_land_code :='000';
1990 l_payroll_id:= csr_asg_payroll_rec.payroll_id;
1991 l_period_id:= csr_asg_payroll_rec.time_period_id;
1992
1993 hr_utility.set_location('ARCHIVE_CODE:After assign l_payroll_id ' || l_payroll_id,380);
1994 hr_utility.set_location('ARCHIVE_CODE:After assign l_period_id ' || l_period_id,380);
1995 hr_utility.set_location('ARCHIVE_CODE:p_effective_date ' || p_effective_date,380);
1996
1997 OPEN csr_pay_periods(l_payroll_id,l_period_id);
1998 FETCH csr_pay_periods into rg_csr_pay_periods;
1999 l_bal_date:= rg_csr_pay_periods.end_date;
2000 CLOSE csr_pay_periods;
2001 l_start_date:=rg_csr_pay_periods.start_date;
2002 l_end_date:=rg_csr_pay_periods.end_date;
2003
2004 hr_utility.set_location('ARCHIVE_CODE: l_bal_date ' || l_bal_date,380);
2005 hr_utility.set_location('ARCHIVE_CODE: l_start_date ' || l_start_date,380);
2006 hr_utility.set_location('ARCHIVE_CODE: l_end_date ' || l_end_date,380);
2007
2008
2009 OPEN csr_get_person_details(p_assignment_action_id, l_end_date ); -- p_effective_date 9489806
2010 FETCH csr_get_person_details into rg_csr_get_person_details;
2011 CLOSE csr_get_person_details;
2012 l_assignment_id:=rg_csr_get_person_details.assignment_id;
2013 l_cpr_number:= rg_csr_get_person_details.cpr;
2014 l_dob:=rg_csr_get_person_details.dob;
2015 l_sex:=rg_csr_get_person_details.sex;
2016 l_bg_id:=rg_csr_get_person_details.business_group_id;
2017 l_org_id:=rg_csr_get_person_details.organization_id;
2018 l_loc_id:=rg_csr_get_person_details.location_id;
2019
2020 fnd_file.put_line(fnd_file.log,'Fetched person details');
2021
2022
2023
2024
2025
2026
2027 OPEN csr_get_extra_person_info(rg_csr_get_person_details.person_id);
2028 FETCH csr_get_extra_person_info into rg_csr_get_extra_person_info;
2029 IF(csr_get_extra_person_info%notfound) THEN
2030 l_yes_no:='N';
2031 l_stationed_abroad := 'N'; -- bug 13880339
2032 ELSIF(csr_get_extra_person_info%found) THEN
2033 l_yes_no:=rg_csr_get_extra_person_info.yes_no;
2034 l_stationed_abroad := rg_csr_get_extra_person_info.stationed_abroad ;
2035 END IF;
2036 CLOSE csr_get_extra_person_info;
2037
2038 IF (l_yes_no='Y' AND l_stationed_abroad = 'N')
2039 OR (l_yes_no='Y' AND l_stationed_abroad = 'Y') THEN /* This employee is a Foreigner */ --15921349
2040 --l_cpr_number:=NULL;
2041 l_cpr_number := '00000000000'; -- Bug 8552112
2042 END IF;
2043
2044 fnd_file.put_line(fnd_file.log,'Fetched payroll period details');
2045
2046 OPEN csr_get_atp_table_value(l_assignment_id, l_start_date, l_end_date);
2047 FETCH csr_get_atp_table_value into rg_csr_get_atp_table_value;
2048 CLOSE csr_get_atp_table_value;
2049
2050 fnd_file.put_line(fnd_file.log,'Fetched atp table value');
2051 IF l_loc_id IS NOT NULL THEN
2052 OPEN csr_location_info (l_loc_id);
2053 FETCH csr_location_info INTO rg_csr_location_info;
2054 CLOSE csr_location_info;
2055 END IF;
2056 fnd_file.put_line(fnd_file.log,'bg id:'||l_bg_id);
2057 fnd_file.put_line(fnd_file.log,'org id:'||l_org_id);
2058 OPEN csr_get_hr_org_info(l_bg_id,l_org_id);
2059 FETCH csr_get_hr_org_info into rg_csr_get_hr_org_info;
2060 CLOSE csr_get_hr_org_info;
2061
2062
2063 fnd_file.put_line(fnd_file.log,'Fetched hr org info');
2064
2065 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
2066 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
2067 CLOSE csr_Legal_Emp_Details;
2068
2069 fnd_file.put_line(fnd_file.log,'Fetched legal emp details');
2070
2071 OPEN csr_get_tax_card_details(l_assignment_id, l_start_date, l_end_date);
2072 FETCH csr_get_tax_card_details INTO rg_csr_get_tax_card_details;
2073 CLOSE csr_get_tax_card_details;
2074
2075 if(rg_csr_get_tax_card_details.screen_entry_value='H') then
2076 l_tax_card_type:='1';
2077 else
2078 l_tax_card_type:='2';
2079 end if;
2080 l_present_flag:=0;
2081 hr_utility.set_location('ARCHIVE_CODE:Before fetch l_present_flag: ' || l_present_flag,380);
2082 OPEN csr_prepaid_actions_present(l_payroll_action_id,
2083 g_legal_employer_id,
2084 l_payroll_id,
2085 l_period_id,
2086 l_start_date,
2087 l_end_date,
2088 l_ass_id);
2089 FETCH csr_prepaid_actions_present INTO l_present_flag;
2090 CLOSE csr_prepaid_actions_present;
2091 hr_utility.set_location('ARCHIVE_CODE:After fetch l_present_flag: ' || l_present_flag,380);
2092 IF (l_present_flag=1) THEN
2093 -- Comment For Bug 9011035
2094 /* OPEN csr_latest_hire_date(rg_csr_get_person_details.person_id, l_end_date);
2095 FETCH csr_latest_hire_date into rg_csr_latest_hire_date;
2096 CLOSE csr_latest_hire_date; */
2097
2098
2099 /* Code for getting the hire date from PER_PEOPLE_V*/
2100 /* begin
2101
2102 select to_char(HIRE_DATE) into l_hd from PER_PEOPLE_V where NATIONAL_IDENTIFIER=rg_csr_get_person_details.cpr ;
2103 fnd_file.put_line(fnd_file.log,'hire date from per_people_v : '||l_hd);
2104
2105 exception
2106 when others then
2107 null;
2108 end; */
2109
2110 /* OPEN csr_latest_hire_date(rg_csr_get_person_details.person_id);
2111 FETCH csr_latest_hire_date into rg_csr_latest_hire_date;
2112 CLOSE csr_latest_hire_date;
2113
2114 l_cpr_number:= rg_csr_get_person_details.cpr;
2115 if((rg_csr_latest_hire_date.lhd between l_start_date and l_end_date) and to_char(l_end_date,'mm')<'06' and to_char(l_end_date,'yyyy')<='2008') then
2116 -- fnd_file.put_line(fnd_file.log,'Inserting starters');
2117 --Record - 2101
2118 pay_action_information_api.create_action_information (
2119 p_action_information_id => l_action_info_id
2120 ,p_action_context_id => p_assignment_action_id
2121 ,p_action_context_type => 'AAP'
2122 ,p_object_version_number => l_ovn
2123 ,p_effective_date => g_effective_date
2124 ,p_source_id => NULL
2125 ,p_source_text => NULL
2126 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2127 ,p_action_information1 => 'PYDKEINA'
2128 ,p_action_information2 => l_payroll_action_id
2129 ,p_action_information3 => '2101'
2130 ,p_action_information4 => rg_csr_get_person_details.person_id
2131 ,p_action_information5 => rg_csr_get_person_details.assignment_id
2132 ,p_action_information6 => substr(l_cpr_number,1,6)||substr(l_cpr_number,8,4)
2133 ,p_action_information7 => to_char(rg_csr_latest_hire_date.lhd,'yyyymmdd') --latest hire date
2134 ,p_action_information8 => null -- no end date
2135 ,p_action_information9 => l_tax_card_type -- tax card type
2136 ,p_action_information10 => to_char(rg_csr_get_tax_card_details.effective_start_date,'yyyymmdd') -- effective start date
2137 ,p_action_information11 => null
2138 ,p_action_information12 => null
2139 ,p_action_information13 => null
2140 ,p_action_information14 => null
2141 ,p_action_information15 => null
2142 ,p_action_information16 => null
2143 ,p_action_information17 => null
2144 ,p_action_information18 => null
2145 ,p_action_information19 => null
2146 ,p_action_information20 => null
2147 ,p_action_information21 => null
2148 ,p_action_information22 => null
2149 ,p_action_information23 => null
2150 ,p_action_information24 => null
2151 ,p_action_information25 => null
2152 ,p_action_information26 => null
2153 ,p_action_information27 => null
2154 ,p_action_information28 => null
2155 ,p_action_information29 => null
2156 ,p_action_information30 => null);
2157 end if; */
2158
2159 -- TERMINATOR RECORD
2160 OPEN csr_asg_terminator(p_assignment_action_id, l_start_date, l_end_date, g_business_group_id);
2161 FETCH csr_asg_terminator INTO rg_csr_asg_terminator;
2162 CLOSE csr_asg_terminator; -- For bug 9011035
2163
2164 -- For Bug 9011035. fetch the hire date for the coresponding termination date
2165 OPEN csr_latest_hire_date(rg_csr_get_person_details.person_id, rg_csr_asg_terminator.effective_end_date);
2166 FETCH csr_latest_hire_date into rg_csr_latest_hire_date;
2167 CLOSE csr_latest_hire_date;
2168
2169 -- bug 10367494
2170 l_se_no :=rg_csr_get_hr_org_info.ORG_INFORMATION5; -- HR-ORG SE Number
2171 if(l_se_no IS NULL) then
2172 l_se_no:=rg_Legal_Emp_Details.ORG_INFORMATION5;
2173 end if;
2174 if(l_se_no IS NULL) then
2175 l_se_no:=rg_Legal_Emp_Details.ORG_INFORMATION1;
2176 end if;
2177
2178 if(g_company_terminating='Y') then
2179 l_company_terminating:='A';
2180 else
2181 l_company_terminating:=null;
2182 end if;
2183 --end bug 10367494
2184
2185 /* bug 10367494 archiving p_action_information11 => l_se_no */
2186 if(rg_csr_asg_terminator.effective_end_date <= l_end_date) then
2187 fnd_file.put_line(fnd_file.log,'Inserting terminators');
2188 --Record - 2101
2189 pay_action_information_api.create_action_information (
2190 p_action_information_id => l_action_info_id
2191 ,p_action_context_id => p_assignment_action_id
2192 ,p_action_context_type => 'AAP'
2193 ,p_object_version_number => l_ovn
2194 ,p_effective_date => g_effective_date
2195 ,p_source_id => NULL
2196 ,p_source_text => NULL
2197 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2198 ,p_action_information1 => 'PYDKEINA'
2199 ,p_action_information2 => l_payroll_action_id
2200 ,p_action_information3 => '2101'
2201 ,p_action_information4 => rg_csr_get_person_details.person_id
2202 ,p_action_information5 => rg_csr_get_person_details.assignment_id
2203 ,p_action_information6 => substr(l_cpr_number,1,6)||substr(l_cpr_number,8,4)
2204 ,p_action_information7 => to_char(rg_csr_latest_hire_date.lhd,'yyyymmdd') --latest hire date
2205 ,p_action_information8 => to_char(rg_csr_asg_terminator.EFFECTIVE_END_DATE,'yyyymmdd') -- end date
2206 ,p_action_information9 => l_tax_card_type -- tax card type
2207 ,p_action_information10 => to_char(l_start_date,'yyyymmdd') -- effective start date
2208 ,p_action_information11 => l_se_no /* bug 10367494 */
2209 ,p_action_information12 => null
2210 ,p_action_information13 => null
2211 ,p_action_information14 => null
2212 ,p_action_information15 => null
2213 ,p_action_information16 => null
2214 ,p_action_information17 => null
2215 ,p_action_information18 => null
2216 ,p_action_information19 => null
2217 ,p_action_information20 => null
2218 ,p_action_information21 => null
2219 ,p_action_information22 => null
2220 ,p_action_information23 => null
2221 ,p_action_information24 => l_period_id /* 9489806 */
2222 ,p_action_information25 => null
2223 ,p_action_information26 => null
2224 ,p_action_information27 => null
2225 ,p_action_information28 => null
2226 ,p_action_information29 => null
2227 ,p_action_information30 => null);
2228 end if;
2229
2230
2231 -- CLOSE csr_asg_terminator;
2232
2233 OPEN csr_asg_extra_info (l_assignment_id);
2234 FETCH csr_asg_extra_info INTO rg_csr_asg_extra_info;
2235 CLOSE csr_asg_extra_info;
2236 /* 9945754-1 */
2237 --
2238 OPEN csr_asg_action_id(rg_csr_get_person_details.assignment_id,l_payroll_id,l_period_id,g_legal_employer_id);
2239 FETCH csr_asg_action_id into rg_csr_asg_action_id;
2240 CLOSE csr_asg_action_id;
2241
2242 OPEN csr_Get_Defined_Balance_Id('HOURLY_HOLIDAY_TAX_ASG_PTD');
2243 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2244 CLOSE csr_Get_Defined_Balance_Id;
2245
2246 l_emp_tax := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee Tax','_ASG_PTD' ,l_bal_date);
2247 l_holiday_tax := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Tax','_ASG_PTD' ,l_bal_date);
2248 l_holiday_tax_pay := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id, rg_csr_asg_action_id.id);
2249 /* 10269552 start */
2250 l_g_dage_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'G_Dage Pay Reporting','_ASG_PTD' ,l_bal_date);
2251 /* 10269552 end */
2252
2253 OPEN csr_Get_Defined_Balance_Id('HOLIDAY_AMB_REPORTING_ASG_PTD'); --8858949
2254 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2255 CLOSE csr_Get_Defined_Balance_Id;
2256
2257 l_emp_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee AMB Deduction','_ASG_PTD' ,l_bal_date);
2258 l_hol_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday AMB','_ASG_PTD' ,l_bal_date);
2259 l_hol_amb_rep := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id, rg_csr_asg_action_id.id);
2260 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 2');
2261
2262 l_hourly_salaried :=rg_csr_get_person_details.HOURLY_SALARIED_CODE;
2263 if(l_hourly_salaried IS null) then
2264 if (rg_csr_pay_periods.PAYROLL_PERIOD=1) then
2265 l_hourly_salaried:='S';
2266 else
2267 l_hourly_salaried := 'H';
2268 end if;
2269 end if;
2270
2271 IF l_hourly_salaried='H' THEN
2272 l_hourly_hol_income_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Accrual Pay Adjustment','_ASG_PTD' ,l_bal_date);
2273 l_hourly_hol_income := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Accrual Pay','_ASG_PTD' ,l_bal_date)- l_hourly_hol_income_adj;
2274 ELSE
2275 l_sal_hol_income := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Salaried Hol Accrual Amount','_ASG_PTD' ,l_bal_date)
2276 +
2277 GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Salaried Hol Curr Entit Amount','_ASG_PTD' ,l_bal_date)
2278 +
2279 GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Salaried Hol Next Entit Amount','_ASG_PTD' ,l_bal_date);
2280
2281 END IF;
2282
2283 --
2284 IF l_loc_id IS NOT NULL AND rg_csr_location_info.lei_information1 IS NOT NULL THEN
2285 l_pu_code :=rg_csr_location_info.lei_information1; -- Location EIT Prod Unit Code
2286 ELSE
2287 l_pu_code :=rg_csr_get_hr_org_info.ORG_INFORMATION6; -- HR-ORG Production Unit Code
2288 if(l_pu_code IS NULL) then
2289 l_pu_code:=rg_Legal_Emp_Details.ORG_INFORMATION6;
2290 end if;
2291 END IF;
2292
2293
2294
2295 -- fnd_file.put_line(fnd_file.log,'PU code:'||l_pu_code);
2296
2297
2298
2299 /* bug fix 7613211 */
2300 -- Record - 8001
2301
2302
2303 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 4');
2304
2305 fnd_file.put_line(fnd_file.log,'p_assignment_action_id:'||p_assignment_action_id);
2306 fnd_file.put_line(fnd_file.log,'l_bal_date:'||l_bal_date);
2307
2308 -- Record 6001
2309 l_amb_pay_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'AMBable Pay Adjustment','_ASG_PTD',l_bal_date);
2310 l_amb_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'AMBable Pay','_ASG_PTD',l_bal_date)
2311 - l_amb_pay_adj;
2312
2313 /* 8861878 */
2314 --
2315 l_b_income_amb_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total B Income Subject to AMB Adjustment','_ASG_PTD' ,l_bal_date);
2316 l_b_income_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total B Income Subject to AMB','_ASG_PTD' ,l_bal_date)
2317 - l_b_income_amb_adj;
2318 /* 9289006 */
2319 IF(l_b_income_amb_adj<>0 and l_amb_pay_adj<>0 ) THEN
2320 l_amb_pay_adj:= l_amb_pay_adj - l_b_income_amb_adj;
2321 END IF;
2322 IF(l_b_income_amb<>0 and l_amb_pay<>0) THEN
2323 l_amb_pay := l_amb_pay - l_b_income_amb;
2324 END IF;
2325 --
2326
2327 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 4.5');
2328 l_emp_atp := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee ATP Deductions','_ASG_PTD' ,l_bal_date);
2329 l_pension := nvl(GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee Pension Deductions','_ASG_PTD' ,l_bal_date),0)
2330 + nvl(GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Rate Pension Deduction','_ASG_PTD' ,l_bal_date),0);
2331 /* 9136987 */
2332 --
2333 l_a_income_non_amb_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total A Income Not AMBable Adjustment','_ASG_PTD' ,l_bal_date);
2334 l_a_non_amb_income := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total A Income Not AMBable','_ASG_PTD' ,l_bal_date)
2335 - l_a_income_non_amb_adj;
2336 --
2337 l_gross_income := l_amb_pay + l_emp_atp + l_a_non_amb_income + l_pension; /* 9136987 */
2338
2339 /* 9945754-1 */
2340
2341 /* 10184280 start */
2342 IF l_hourly_salaried='H' THEN
2343 l_total_amb := FLOOR(l_emp_amb)+FLOOR(l_hol_amb_rep);
2344 ELSE
2345 l_total_amb := FLOOR(l_emp_amb)+FLOOR(l_hol_amb);
2346 END IF;
2347 /* 10184280 end */
2348 --
2349 IF l_hourly_salaried='H' THEN
2350 /* 9958079 Removed Hourly Holiday Tax and Holiday AMB Reporting as Holiday Accrual Pay already includes */
2351 -- l_gross_income := l_gross_income + l_hourly_hol_income + l_holiday_tax_pay + l_hol_amb_rep;
2352 l_gross_income := l_gross_income + l_hourly_hol_income;
2353 ELSE
2354 l_gross_income := l_gross_income + l_sal_hol_income + l_holiday_tax + l_hol_amb;
2355 END IF;
2356 --
2357
2358
2359 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 5');
2360 l_hourly_holiday_pay_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Accrual Pay Adjustment','_ASG_PTD' ,l_bal_date);
2361 l_hourly_holiday_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Accrual Pay','_ASG_PTD' ,l_bal_date)
2362 - l_hourly_holiday_pay_adj;
2363 /* bug 12794710 commented below l_monthly_holiday_pay */
2364 /* l_monthly_holiday_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Allowance Paid','_ASG_PTD' ,l_bal_date)
2365 +
2366 GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Allowance Advance Paid','_ASG_PTD' ,l_bal_date); */ /* 9945754 */
2367 /* 9958079 added Holiday Bank Information balances to 0201 */
2368 l_monthly_holiday_pay := l_monthly_holiday_pay + l_sal_hol_income + l_holiday_tax + l_hol_amb;
2369
2370 /* 9945754-1 moved to top
2371 l_hourly_salaried :=rg_csr_get_person_details.HOURLY_SALARIED_CODE;
2372
2373 if(l_hourly_salaried IS null) then
2374 if (rg_csr_pay_periods.PAYROLL_PERIOD=1) then
2375 l_hourly_salaried:='S';
2376 else
2377 l_hourly_salaried := 'H';
2378 end if;
2379 end if;
2380 */
2381
2382
2383 l_a_income_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'AMBable Pay Adjustment','_ASG_PTD' ,l_bal_date);
2384 l_a_income := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'AMBable Pay','_ASG_PTD' ,l_bal_date)
2385 - l_a_income_adj;
2386
2387 /* 9945754-1 */
2388 --
2389 IF l_hourly_salaried='H' THEN
2390 /* 9958079 Removed Hourly Holiday Tax and Holiday AMB Reporting as Holiday Accrual Pay already includes */
2391 --l_a_income := l_a_income + l_hourly_hol_income + l_holiday_tax_pay + l_hol_amb_rep;
2392 l_a_income := l_a_income + l_hourly_hol_income;
2393 l_a_income_adj := l_a_income_adj + l_hourly_hol_income_adj;
2394 ELSE
2395 l_a_income := l_a_income + l_sal_hol_income + l_holiday_tax + l_hol_amb;
2396 END IF;
2397 --
2398
2399 /* 10184280 start */
2400 /*
2401 AMBable pay has negative feed of employee ATP so for
2402 ZERO Employee AMB, employee atp shuld be reported only in 0014, so adding atp to l_a_income
2403 to remove atp portion from it and added that value to l_a_non_amb_income to get reported in the
2404 0014
2405 */
2406 IF l_total_amb = 0 THEN
2407 l_a_income := l_a_income + l_emp_atp;
2408 l_a_non_amb_income := l_a_non_amb_income - l_emp_atp;
2409
2410 END IF;
2411
2412 /* 10184280 end */
2413
2414 /* 8861878 */
2415 --
2416 /* 10269552 start G_Dage pay need to be reported under 0014 code for record 6001 with employement code as 04 in 5000*/
2417 /* commented below 2 lines for bug 12365396 */
2418 -- l_a_non_amb_income := l_a_non_amb_income + l_g_dage_pay;
2419 -- l_gross_income := l_gross_income + l_g_dage_pay;
2420
2421 /* 10269552 end */
2422 /* 9289006 */
2423 IF(l_b_income_amb_adj<>0 and l_a_income_adj<>0 ) THEN
2424 l_a_income_adj := l_a_income_adj - l_b_income_amb_adj;
2425 END IF;
2426 IF(l_b_income_amb_adj<>0 and l_a_income<>0 ) THEN
2427 l_a_income := l_a_income - l_b_income_amb;
2428 END IF;
2429 --
2430 /* 10180980 start */
2431 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 12');
2432 l_sp_bonus_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Special Pay Adjustment','_ASG_PTD' ,l_bal_date);
2433 l_sp_bonus := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Special Pay','_ASG_PTD' ,l_bal_date);
2434 l_a_income := l_a_income - l_sp_bonus; /* only Special pay balance need to be substracted as this is the amount
2435 that got fed in to AMBable Pay via Special Pay Classification feed */
2436 l_gross_income := l_gross_income - l_sp_bonus; /* added for bug 12393673 */
2437 l_sp_bonus := l_sp_bonus - l_sp_bonus_adj;
2438 /* 10180980 end */
2439
2440 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 6');
2441 /* 8861878 */
2442 --
2443 /* moved these balance fetches to top as these figures shoudl be included in gros income 9136987 */
2444 /*
2445 l_a_income_non_amb_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total A Income Not AMBable Adjustment','_ASG_PTD' ,l_bal_date);
2446 l_a_non_amb_income := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total A Income Not AMBable','_ASG_PTD' ,l_bal_date)
2447 - l_a_income_non_amb_adj; */
2448
2449
2450 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 6.5');
2451 --
2452 /* 9945754-1 Moving this statements to the top
2453
2454 OPEN csr_asg_action_id(rg_csr_get_person_details.assignment_id,l_payroll_id,l_period_id,g_legal_employer_id);
2455 FETCH csr_asg_action_id into rg_csr_asg_action_id;
2456 CLOSE csr_asg_action_id;
2457 */
2458
2459 --OPEN csr_Get_Defined_Balance_Id('HOURLY_HOLIDAY_TAX_PAYMENTS'); --9014232
2460 /* 9945754-1 */
2461 /* moved these balance fetches
2462 OPEN csr_Get_Defined_Balance_Id('HOURLY_HOLIDAY_TAX_ASG_PTD'); --9014232
2463 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2464 CLOSE csr_Get_Defined_Balance_Id;
2465
2466 l_emp_tax := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee Tax','_ASG_PTD' ,l_bal_date);
2467
2468 l_holiday_tax := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Tax','_ASG_PTD' ,l_bal_date);
2469 l_holiday_tax_pay := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id, rg_csr_asg_action_id.id);
2470 */
2471
2472 -- fnd_file.put_line(fnd_file.log,'Hourly Holiday Tax_Payments : '||l_holiday_tax_pay);
2473 -- /* 9945754 */
2474 IF l_hourly_salaried='H' THEN
2475 l_tax := FLOOR(l_emp_tax)+FLOOR(l_holiday_tax_pay);
2476 ELSE
2477 l_tax := FLOOR(l_emp_tax)+FLOOR(l_holiday_tax);
2478 END IF;
2479
2480 /* 9945754-1 */
2481 /* moved these balance fetches
2482 OPEN csr_Get_Defined_Balance_Id('HOLIDAY_AMB_REPORTING_ASG_PTD'); --8858949
2483 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
2484 CLOSE csr_Get_Defined_Balance_Id;
2485
2486 l_emp_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee AMB Deduction','_ASG_PTD' ,l_bal_date);
2487
2488 l_hol_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday AMB','_ASG_PTD' ,l_bal_date);
2489 l_hol_amb_rep := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id, rg_csr_asg_action_id.id);
2490 */
2491 -- fnd_file.put_line(fnd_file.log,'Holiday AMB Reporting (ASG RUN) : '||l_hol_amb_rep);
2492 /* 9945754 */
2493 /* 101842801 moved to top */
2494 /*
2495 IF l_hourly_salaried='H' THEN
2496 l_total_amb := FLOOR(l_emp_amb)+FLOOR(l_hol_amb_rep);
2497 ELSE
2498 l_total_amb := FLOOR(l_emp_amb)+FLOOR(l_hol_amb);
2499 END IF;
2500 */
2501 /* 10184280 end */
2502
2503
2504
2505 l_car_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Taxable Car Adjustment','_ASG_PTD' ,l_bal_date);
2506 l_car := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Taxable Car Amount','_ASG_PTD' ,l_bal_date)
2507 - l_car_adj;
2508
2509
2510 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 8');
2511 l_board_lodge_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Board and Lodge Adjustment','_ASG_PTD' ,l_bal_date);
2512 l_board_lodge := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Board and Lodge Amount','_ASG_PTD' ,l_bal_date)
2513 - l_board_lodge_adj;
2514
2515
2516 /* 8861878 */
2517 --
2518 -- l_b_income_amb_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total B Income Subject to AMB Adjustment','_ASG_PTD' ,l_bal_date);
2519 -- l_b_income_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total B Income Subject to AMB','_ASG_PTD' ,l_bal_date)
2520 -- - l_b_income_amb_adj;
2521
2522
2523 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 8.2');
2524 -- B-Income
2525 l_b_income_non_amb_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total B Income Not Subject to AMB Adjustment','_ASG_PTD' ,l_bal_date);
2526 l_b_income_non_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total B Income Not Subject to AMB','_ASG_PTD' ,l_bal_date)
2527 - l_b_income_non_amb_adj;
2528
2529 --
2530
2531 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 9');
2532
2533 l_employer_atp := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employer ATP Deductions','_ASG_PTD' ,l_bal_date);
2534 l_total_atp := l_emp_atp+l_employer_atp;
2535
2536
2537 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 10');
2538 /* 8861878 */
2539 --
2540 l_non_taxable_travel_adj:= GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Non Taxable Travel and Per Diem Adjustment','_ASG_PTD' ,l_bal_date);
2541 l_non_taxable_travel:= GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Non Taxable Travel and Per Diem','_ASG_PTD' ,l_bal_date)
2542 - l_non_taxable_travel_adj;
2543 --
2544 l_mileage_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Mileage Claimed Adjustment','_ASG_PTD' ,l_bal_date);
2545 l_mileage := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Mileage Claimed Paid','_ASG_PTD' ,l_bal_date)
2546 - l_mileage_adj;
2547 /* 8861878 */
2548 --
2549 l_mileage_adj := l_mileage_adj + l_non_taxable_travel_adj;
2550 l_mileage := l_mileage + l_non_taxable_travel;
2551
2552 --
2553
2554
2555
2556 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 11');
2557 l_free_phone_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Taxable Phone Adjustment','_ASG_PTD' ,l_bal_date);
2558 l_free_phone := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Taxable Phone Amount','_ASG_PTD' ,l_bal_date)
2559 - l_free_phone_adj;
2560
2561
2562 /* Bug# 9079593 fix starts */
2563 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 11.1 Multimedia');
2564 l_multimedia_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Multimedia Tax Adjustment','_ASG_PTD' ,l_bal_date);
2565 l_multimedia := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Multimedia Tax Amount','_ASG_PTD' ,l_bal_date)
2566 - l_multimedia_adj;
2567
2568 /* Bug# 9079593 fix ends */
2569
2570 /* Bug# 9169583 fix starts */
2571 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 11.2 BIK Trivial');
2572 l_trivial_matter_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Trivial Matter BIK Adjustment','_ASG_PTD' ,l_bal_date);
2573 l_trivial_matter := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Trivial Matter BIK Amount','_ASG_PTD' ,l_bal_date)
2574 - l_trivial_matter_adj;
2575
2576 /* Bug# 9169583 fix ends */
2577 /* moved this to above as a part of bug 10180980
2578 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 12');
2579 l_sp_bonus_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Special Pay Adjustment','_ASG_PTD' ,l_bal_date);
2580 l_sp_bonus := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Special Pay','_ASG_PTD' ,l_bal_date)
2581 - l_sp_bonus_adj;
2582 */
2583
2584 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 13');
2585
2586 /* 8861878 */
2587 --
2588 l_pension_sev_pay_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Pension Special Bonus Severance Pay Adjustment','_ASG_PTD' ,l_bal_date);
2589 l_pension_sev_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Pension Special Bonus Severance Pay','_ASG_PTD' ,l_bal_date)
2590 - l_pension_sev_pay_adj;
2591
2592 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 14');
2593
2594 l_physical_gift_sev_pay_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Physical Gifts and Severance Pay Adjustment','_ASG_PTD' ,l_bal_date);
2595 l_physical_gift_sev_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Physical Gifts and Severance Pay','_ASG_PTD' ,l_bal_date)
2596 - l_physical_gift_sev_pay_adj;
2597 /* bug 12943072 */
2598 l_tot_free_res_pay_adj := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Free Permanent Residence Adjustment','_ASG_PTD' ,l_bal_date);
2599 l_tot_free_res_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Free Permanent Residence Amount','_ASG_PTD' ,l_bal_date)
2600 - l_tot_free_res_pay_adj;
2601 /*
2602 For Normal Employement
2603 l_employment_type ='00'
2604 For B Incoem Employee
2605 l_employment_type ='05'
2606 For Foriegn Employees
2607 l_employment_type ='09'
2608 */
2609
2610 /* 10115425 start */
2611 --IF (l_yes_no='Y') OR l_stationed_abroad = 'Y' THEN --15921349
2612 --15921349
2613 IF (l_yes_no='Y' AND l_stationed_abroad = 'Y')
2614 OR (l_yes_no='N' AND l_stationed_abroad = 'Y') THEN
2615 --15921349
2616 l_employment_type :='09'; /* This employee is a Foreigner */
2617 END IF;
2618 /* 10115425 end */
2619
2620 --15921349
2621 IF (l_yes_no='N' AND l_stationed_abroad = 'N')
2622 OR (l_yes_no='Y' AND l_stationed_abroad = 'N') THEN
2623 l_employment_type :='00';
2624 END IF;
2625 --15921349
2626
2627 IF( l_b_income_amb<>0 OR l_b_income_amb_adj<>0 OR l_b_income_non_amb<>0 OR l_b_income_non_amb_adj<>0) THEN
2628 l_employment_type :='05';
2629 END IF;
2630
2631 /* moved this to above as a part of bug 10115425 B income takes precedence over
2632 foriegn employees
2633 IF(l_yes_no='Y') THEN
2634 l_employment_type :='09';
2635 END IF;
2636 */
2637
2638 IF( rg_csr_asg_extra_info.aei_information2 is not null ) THEN
2639 l_employment_type :='03';
2640 l_green_land_code :=rg_csr_asg_extra_info.aei_information2;
2641 END IF;
2642 /* 10269552 start */
2643 IF( l_g_dage_pay <>0 ) THEN
2644 /* 12365396 start */ -- bug 13335391
2645 -- l_employment_type :='04';
2646 if l_a_non_amb_income <> 0 or l_a_income <> 0 or l_tot_free_res_pay <> 0 then
2647 l_employment_type :='00';
2648
2649 else
2650 l_employment_type :='04';
2651 l_pu_code := NULL; /* bug 12994694 */
2652 --15921349
2653 IF (l_yes_no='N' AND l_stationed_abroad = 'N')
2654 OR (l_yes_no='Y' AND l_stationed_abroad = 'N') THEN
2655 l_employment_type :='00';
2656 END IF;
2657 --15921349
2658 end if;
2659
2660 END IF;
2661
2662 l_a_non_amb_income := l_a_non_amb_income + l_g_dage_pay;
2663 l_gross_income := l_gross_income + l_g_dage_pay;
2664
2665 /* 12365396 end */
2666 /* 10269552 end */
2667
2668 l_cvr_number:= rg_Legal_Emp_Details.ORG_INFORMATION1;
2669 -- bug 10367494
2670 l_flag1 :=0;
2671 OPEN csr_chk_2001(l_se_no);
2672 FETCH csr_chk_2001 into l_flag1;
2673 CLOSE csr_chk_2001;
2674
2675 IF l_flag1<>1 THEN
2676 pay_action_information_api.create_action_information (
2677 p_action_information_id => l_action_info_id
2678 ,p_action_context_id => g_payroll_action_id
2679 ,p_action_context_type => 'PA'
2680 ,p_object_version_number => l_ovn
2681 ,p_effective_date => g_effective_date
2682 ,p_source_id => NULL
2683 ,p_source_text => NULL
2684 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2685 ,p_action_information1 => 'PYDKEINA'
2686 ,p_action_information2 => g_payroll_action_id
2687 ,p_action_information3 => '2001'
2688 ,p_action_information4 => l_se_no -- legal emp CVR number
2689 ,p_action_information5 => l_company_terminating -- is null if company termiating is 'N'
2690 ,p_action_information6 => 'DKK'
2691 ,p_action_information7 => null
2692 ,p_action_information8 => null
2693 ,p_action_information9 => null
2694 ,p_action_information10 => null
2695 ,p_action_information11 => null
2696 ,p_action_information12 => null
2697 ,p_action_information13 => null
2698 ,p_action_information14 => null
2699 ,p_action_information15 => null
2700 ,p_action_information16 => null
2701 ,p_action_information17 => null
2702 ,p_action_information18 => null
2703 ,p_action_information19 => null
2704 ,p_action_information20 => null
2705 ,p_action_information21 => null
2706 ,p_action_information22 => null
2707 ,p_action_information23 => null
2708 ,p_action_information24 => null
2709 ,p_action_information25 => null
2710 ,p_action_information26 => null
2711 ,p_action_information27 => null
2712 ,p_action_information28 => null
2713 ,p_action_information29 => null
2714 ,p_action_information30 => null);
2715 END IF;
2716
2717 -- fnd_file.put_line(fnd_file.log,'Person name : '||rg_csr_get_person_details.full_name);
2718
2719 --Record - 6000
2720 /* bug 10367494 archiving p_action_information11 => l_se_no */
2721 pay_action_information_api.create_action_information (
2722 p_action_information_id => l_action_info_id
2723 ,p_action_context_id => p_assignment_action_id
2724 ,p_action_context_type => 'AAP'
2725 ,p_object_version_number => l_ovn
2726 ,p_effective_date => g_effective_date
2727 ,p_source_id => NULL
2728 ,p_source_text => NULL
2729 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2730 ,p_action_information1 => 'PYDKEINA'
2731 ,p_action_information2 => l_payroll_action_id
2732 ,p_action_information3 => '6000'
2733 ,p_action_information4 => rg_csr_get_person_details.person_id
2734 ,p_action_information5 => rg_csr_get_person_details.assignment_id
2735 ,p_action_information6 => l_cvr_number --cvr number
2736 ,p_action_information7 => substr(l_cpr_number,1,6)||substr(l_cpr_number,8,4)
2737 ,p_action_information8 => rg_csr_get_person_details.assignment_number -- assignment number
2738 ,p_action_information9 => '0000'
2739 ,p_action_information10 => l_pu_code --DK production unit code
2740 ,p_action_information11 => l_se_no /* bug 10367494 */
2741 ,p_action_information12 => null
2742 ,p_action_information13 => null
2743 ,p_action_information14 => null
2744 ,p_action_information15 => null
2745 ,p_action_information16 => null
2746 ,p_action_information17 => null
2747 ,p_action_information18 => null
2748 ,p_action_information19 => null
2749 ,p_action_information20 => null
2750 ,p_action_information21 => null
2751 ,p_action_information22 => null
2752 ,p_action_information23 => null
2753 ,p_action_information24 => l_period_id /* 9489806 */
2754 ,p_action_information25 => l_payroll_id -- 9489806
2755 ,p_action_information26 => l_employment_type
2756 ,p_action_information27 => l_green_land_code /* 8847591 */
2757 ,p_action_information28 => null
2758 ,p_action_information29 => null
2759 ,p_action_information30 => null);
2760
2761
2762 --15921349
2763 --IF(l_yes_no='Y') OR l_stationed_abroad = 'Y' THEN /* This employee is a Foreigner */
2764 IF (l_yes_no='Y' AND l_stationed_abroad = 'Y')
2765 OR (l_yes_no='Y' AND l_stationed_abroad = 'N') THEN
2766 --15921349
2767
2768 OPEN csr_get_territory(rg_csr_get_person_details.person_id);
2769 FETCH csr_get_territory INTO rg_csr_get_territory;
2770 CLOSE csr_get_territory;
2771
2772 l_style:=rg_csr_get_territory.style;
2773 if(l_style = 'DK') then
2774 l_town_or_city := rg_csr_get_territory.postal_code;
2775 elsif(l_style = 'DK_GLB') then
2776 l_town_or_city := rg_csr_get_territory.town_or_city;
2777 end if;
2778
2779 select DECODE(l_sex,'M','1','F','2','3') into l_sex from dual;
2780
2781 pay_action_information_api.create_action_information (
2782 p_action_information_id => l_action_info_id
2783 ,p_action_context_id => p_assignment_action_id
2784 ,p_action_context_type => 'AAP'
2785 ,p_object_version_number => l_ovn
2786 ,p_effective_date => g_effective_date
2787 ,p_source_id => NULL
2788 ,p_source_text => NULL
2789 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2790 ,p_action_information1 => 'PYDKEINA'
2791 ,p_action_information2 => l_payroll_action_id
2792 ,p_action_information3 => '8001'
2793 ,p_action_information4 => rg_csr_get_person_details.person_id
2794 ,p_action_information5 => rg_csr_get_person_details.assignment_id
2795 ,p_action_information6 => NVL(l_dob,'00000000') --date of birth
2796 ,p_action_information7 => l_sex -- gender
2797 ,p_action_information8 => rg_csr_get_territory.country -- Territory code
2798 ,p_action_information9 => rg_csr_get_person_details.pname -- name
2799 ,p_action_information10 => rg_csr_get_territory.address_line1 --address
2800 ,p_action_information11 => rg_csr_get_territory.postal_code -- postal code
2801 ,p_action_information12 => l_town_or_city -- town or city
2802 ,p_action_information13 => null
2803 ,p_action_information14 => null
2804 ,p_action_information15 => null
2805 ,p_action_information16 => null
2806 ,p_action_information17 => null
2807 ,p_action_information18 => null
2808 ,p_action_information19 => null
2809 ,p_action_information20 => null
2810 ,p_action_information21 => null
2811 ,p_action_information22 => null
2812 ,p_action_information23 => null
2813 ,p_action_information24 => l_period_id /* 9489806 */
2814 ,p_action_information25 => l_payroll_id -- 9489806
2815 ,p_action_information26 => l_employment_type -- 9489806
2816 ,p_action_information27 => l_green_land_code /* 8847591 */
2817 ,p_action_information28 => null
2818 ,p_action_information29 => null
2819 ,p_action_information30 => null);
2820
2821 END IF;
2822
2823 rec_5000(l_payroll_action_id,l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
2824 --
2825 l_corr_flag := 'N'; -- bug 12944028
2826 if(l_gross_income>0) then
2827 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0200',abs(l_gross_income)*1000000,SIGN(l_gross_income),
2828 'N',l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
2829 /* 9945754 */
2830 --
2831 elsif(l_hourly_salaried='H' AND l_gross_income<0 AND (l_gross_income+l_amb_pay_adj + l_a_income_non_amb_adj + l_hourly_hol_income_adj) <> 0) then
2832 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0200',abs(l_gross_income+l_amb_pay_adj + l_a_income_non_amb_adj+l_hourly_hol_income_adj)*1000000
2833 ,SIGN(l_gross_income+l_amb_pay_adj + l_a_income_non_amb_adj+l_hourly_hol_income_adj), 'Y',l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2834 l_corr_flag := 'Y';
2835 --
2836 elsif(l_gross_income<0 AND (l_gross_income+l_amb_pay_adj + l_a_income_non_amb_adj) <> 0) then
2837 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0200',abs(l_gross_income+l_amb_pay_adj + l_a_income_non_amb_adj)*1000000
2838 ,SIGN(l_gross_income+l_amb_pay_adj + l_a_income_non_amb_adj), 'Y',l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2839 l_corr_flag := 'Y';
2840 end if;
2841 --If gross income < 0 then we bring the -ve amount to the correction section. While doing this we also add any adjustment amounts to the same record
2842 -- So gross income < 0 case already handles AMB adjustment. The following is for remaining cases only
2843 /* 9945754 start */
2844 /*
2845 IF (l_amb_pay_adj + l_a_income_non_amb_adj <> 0 AND l_gross_income>=0) THEN
2846 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0200',abs(l_amb_pay_adj + l_a_income_non_amb_adj)*1000000,
2847 SIGN(l_amb_pay_adj + l_a_income_non_amb_adj), 'Y',l_payroll_id,l_employment_type,l_period_id,l_green_land_code);
2848 END IF;
2849 */
2850
2851 IF l_hourly_salaried='H' THEN
2852 IF (l_amb_pay_adj + l_a_income_non_amb_adj + l_hourly_hol_income_adj <> 0 AND l_gross_income>=0 ) THEN
2853 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0200',abs(l_amb_pay_adj + l_a_income_non_amb_adj+l_hourly_hol_income_adj)*1000000,
2854 SIGN(l_amb_pay_adj + l_a_income_non_amb_adj + l_hourly_hol_income_adj), 'Y',l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2855 l_corr_flag := 'Y';
2856 END IF;
2857
2858 ELSE
2859 IF (l_amb_pay_adj + l_a_income_non_amb_adj <> 0 AND l_gross_income>=0 ) THEN
2860 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0200',abs(l_amb_pay_adj + l_a_income_non_amb_adj)*1000000,
2861 SIGN(l_amb_pay_adj + l_a_income_non_amb_adj), 'Y',l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2862 l_corr_flag := 'Y';
2863 END IF;
2864 END IF;
2865 /* 9945754 end */
2866
2867
2868 --
2869 if(l_hourly_salaried='S' and l_monthly_holiday_pay>0) then
2870 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_monthly_holiday_pay)*1000000,SIGN(l_monthly_holiday_pay), 'N'
2871 ,l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
2872 elsif(l_hourly_salaried='S' and l_monthly_holiday_pay<0) then
2873 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_monthly_holiday_pay)*1000000,SIGN(l_monthly_holiday_pay), 'Y',
2874 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
2875 l_corr_flag := 'Y';
2876 /* bug 11808484 */
2877 /* elsif(l_hourly_salaried='H' and l_hourly_holiday_pay<>0) then
2878 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_hourly_holiday_pay)*1000000,SIGN(l_hourly_holiday_pay), 'N',
2879 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
2880 end if;
2881 IF (l_hourly_salaried='H' AND l_hourly_holiday_pay_adj<>0) THEN
2882 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_hourly_holiday_pay_adj)*1000000,SIGN(l_hourly_holiday_pay_adj), 'Y',
2883 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
2884 END IF; */
2885 --
2886 elsif(l_hourly_salaried='H' and l_hourly_holiday_pay>0) then
2887 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_hourly_holiday_pay)*1000000,SIGN(l_hourly_holiday_pay), 'N',
2888 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2889 elsif (l_hourly_salaried='H' and l_hourly_holiday_pay<0 and (l_hourly_holiday_pay+ l_hourly_holiday_pay_adj)<>0)then
2890 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_hourly_holiday_pay+l_hourly_holiday_pay_adj)*1000000,SIGN(l_hourly_holiday_pay+l_hourly_holiday_pay_adj), 'Y',
2891 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2892 l_corr_flag := 'Y';
2893 end if;
2894 IF (l_hourly_salaried='H' AND l_hourly_holiday_pay>=0 and l_hourly_holiday_pay_adj<>0) THEN
2895 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_hourly_holiday_pay_adj)*1000000,SIGN(l_hourly_holiday_pay_adj), 'Y',
2896 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2897 l_corr_flag := 'Y';
2898 END IF;
2899 /* end 11808484 */
2900 --
2901 /* 10293651 start */
2902 /*
2903 if(l_a_income<>0) then
2904 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0013',abs(l_a_income)*1000000,SIGN(l_a_income), 'N',
2905 l_payroll_id,l_employment_type,l_period_id,l_green_land_code);
2906 end if;
2907 if(l_a_income_adj<>0) then
2908 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0013',abs(l_a_income_adj)*1000000,SIGN(l_a_income_adj), 'Y',
2909 l_payroll_id,l_employment_type,l_period_id,l_green_land_code);
2910 end if;
2911 */
2912
2913 if(l_a_income>0) then
2914 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0013',abs(l_a_income)*1000000,SIGN(l_a_income), 'N',
2915 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2916
2917 ELSIF (l_a_income<0 AND (l_a_income+l_a_income_adj) <> 0) then
2918 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0013',abs(l_a_income+l_a_income_adj)*1000000,SIGN(l_a_income+l_a_income_adj), 'Y',
2919 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2920 l_corr_flag := 'Y';
2921
2922 end if;
2923
2924 if(l_a_income_adj<>0 and l_a_income>=0) then
2925 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0013',abs(l_a_income_adj)*1000000,SIGN(l_a_income_adj), 'Y',
2926 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2927 l_corr_flag := 'Y';
2928 end if;
2929 /* 10293651 end */
2930
2931 --
2932 /* bug 11808484 start */
2933
2934 /* if(l_a_non_amb_income<>0) then
2935 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0014',abs(l_a_non_amb_income)*1000000,SIGN(l_a_non_amb_income), 'N',
2936 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
2937 end if;
2938 if(l_a_income_non_amb_adj<>0) then
2939 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0014',abs(l_a_income_non_amb_adj)*1000000,SIGN(l_a_income_non_amb_adj), 'Y',
2940 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
2941 end if; */
2942 --
2943 if(l_a_non_amb_income>0) then
2944 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0014',abs(l_a_non_amb_income)*1000000,SIGN(l_a_non_amb_income), 'N',
2945 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2946 ELSIF (l_a_non_amb_income <0 and (l_a_non_amb_income+l_a_income_non_amb_adj)<>0) Then
2947 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0014',abs(l_a_non_amb_income+l_a_income_non_amb_adj)*1000000,SIGN(l_a_non_amb_income+l_a_income_non_amb_adj), 'Y',
2948 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2949 l_corr_flag := 'Y';
2950 end if;
2951 if(l_a_non_amb_income>=0 AND l_a_income_non_amb_adj<>0) then
2952 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0014',abs(l_a_income_non_amb_adj)*1000000,SIGN(l_a_income_non_amb_adj), 'Y',
2953 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2954 l_corr_flag := 'Y';
2955 end if;
2956 --
2957 if(l_tax>0) then
2958 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0015',abs(l_tax)*1000000,SIGN(l_tax), 'N',
2959 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
2960 elsif(l_tax<0) then
2961 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0015',abs(l_tax)*1000000,SIGN(l_tax), 'Y',
2962 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
2963 l_corr_flag := 'Y';
2964 end if;
2965 --
2966 if(l_total_amb>0) then
2967 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0016',abs(l_total_amb)*1000000,SIGN(l_total_amb), 'N',
2968 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
2969 elsif(l_total_amb<0) then
2970 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0016',abs(l_total_amb)*1000000,SIGN(l_total_amb), 'Y',
2971 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
2972 l_corr_flag := 'Y';
2973 end if;
2974 --
2975
2976 --
2977 /* if(l_car<>0) then
2978 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0019',abs(l_car)*1000000,SIGN(l_car), 'N',
2979 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
2980 end if;
2981 if(l_car_adj<>0) then
2982 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0019',abs(l_car_adj)*1000000,SIGN(l_car_adj), 'Y',
2983 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
2984 end if; */
2985 --
2986 if(l_car >0) then
2987 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0019',abs(l_car)*1000000,SIGN(l_car), 'N',
2988 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2989 ELSIF (l_car<0 and (l_car +l_car_adj)<>0) then
2990 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0019',abs(l_car+l_car_adj)*1000000,SIGN(l_car+l_car_adj), 'Y',
2991 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2992 l_corr_flag := 'Y';
2993 end if;
2994 if (l_car >=0 and l_car_adj <>0) then
2995 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0019',abs(l_car_adj)*1000000,SIGN(l_car_adj), 'Y',
2996 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
2997 l_corr_flag := 'Y';
2998 end if;
2999 ---
3000 /* if(l_board_lodge<>0) then
3001 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0021',abs(l_board_lodge)*1000000,SIGN(l_board_lodge), 'N',
3002 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3003 end if;
3004 if(l_board_lodge_adj<>0) then
3005 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0021',abs(l_board_lodge_adj)*1000000,SIGN(l_board_lodge_adj), 'Y',
3006 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3007 end if; */
3008 --
3009 if(l_board_lodge>0) then
3010 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0021',abs(l_board_lodge)*1000000,SIGN(l_board_lodge), 'N',
3011 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3012
3013 elsif (l_board_lodge <0 and (l_board_lodge+l_board_lodge_adj)<>0)then
3014 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0021',abs(l_board_lodge+l_board_lodge_adj)*1000000,SIGN(l_board_lodge+l_board_lodge_adj), 'Y',
3015 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3016 l_corr_flag := 'Y';
3017 end if;
3018 if(l_board_lodge>=0 and l_board_lodge_adj<>0) then
3019 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0021',abs(l_board_lodge_adj)*1000000,SIGN(l_board_lodge_adj), 'Y',
3020 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3021 l_corr_flag := 'Y';
3022 end if;
3023 --
3024 /* if(l_b_income_amb<>0) then
3025 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0036',abs(l_b_income_amb)*1000000,SIGN(l_b_income_amb), 'N',
3026 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3027 end if;
3028 if(l_b_income_amb_adj<>0) then
3029 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0036',abs(l_b_income_amb_adj)*1000000,SIGN(l_b_income_amb_adj), 'Y',
3030 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3031 end if; */
3032 --
3033 if(l_b_income_amb>0) then
3034 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0036',abs(l_b_income_amb)*1000000,SIGN(l_b_income_amb), 'N',
3035 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3036
3037 elsif (l_b_income_amb<0 and (l_b_income_amb+l_b_income_amb_adj)<>0) then
3038 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0036',abs(l_b_income_amb+l_b_income_amb_adj)*1000000,SIGN(l_b_income_amb+l_b_income_amb_adj), 'Y',
3039 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3040 l_corr_flag := 'Y';
3041 end if;
3042 if(l_b_income_amb >=0 and l_b_income_amb_adj<>0) then
3043 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0036',abs(l_b_income_amb_adj)*1000000,SIGN(l_b_income_amb_adj), 'Y',
3044 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3045 l_corr_flag := 'Y';
3046 end if;
3047 --
3048 /* if(l_b_income_non_amb<>0) then
3049 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0038',abs(l_b_income_non_amb)*1000000,SIGN(l_b_income_non_amb), 'N',
3050 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3051 end if;
3052 if(l_b_income_non_amb_adj<>0) then
3053 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0038',abs(l_b_income_non_amb_adj)*1000000,SIGN(l_b_income_non_amb_adj), 'Y',
3054 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3055 end if; */
3056 --
3057 if(l_b_income_non_amb>0) then
3058 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0038',abs(l_b_income_non_amb)*1000000,SIGN(l_b_income_non_amb), 'N',
3059 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3060 elsif (l_b_income_non_amb<0 and (l_b_income_non_amb+l_b_income_non_amb_adj)<>0) then
3061 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0038',abs(l_b_income_non_amb+l_b_income_non_amb_adj)*1000000,SIGN(l_b_income_non_amb+l_b_income_non_amb_adj), 'Y',
3062 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3063 l_corr_flag := 'Y';
3064 end if;
3065 if(l_b_income_non_amb >=0 and l_b_income_non_amb_adj<>0) then
3066 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0038',abs(l_b_income_non_amb_adj)*1000000,SIGN(l_b_income_non_amb_adj), 'Y',
3067 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3068 l_corr_flag := 'Y';
3069 end if;
3070 --
3071 if(l_total_atp>0) then
3072 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0046',abs(l_total_atp)*1000000,SIGN(l_total_atp), 'N',
3073 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
3074 elsif(l_total_atp<0) then
3075 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0046',abs(l_total_atp)*1000000,SIGN(l_total_atp), 'Y',
3076 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
3077 l_corr_flag := 'Y';
3078 end if;
3079 --
3080
3081 --
3082 /* if(l_mileage<>0) then
3083 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0048',abs(l_mileage)*1000000,SIGN(l_mileage),'N',
3084 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3085 end if;
3086 if(l_mileage_adj<>0) then
3087 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0048',abs(l_mileage_adj)*1000000,SIGN(l_mileage_adj),'Y',
3088 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3089 end if; */
3090 --
3091 if(l_mileage>0) then
3092 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0048',abs(l_mileage)*1000000,SIGN(l_mileage),'N',
3093 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3094 elsif(l_mileage<0 and (l_mileage+l_mileage_adj)<>0) then
3095 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0048',abs(l_mileage+l_mileage_adj)*1000000,SIGN(l_mileage+l_mileage_adj),'Y',
3096 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3097 l_corr_flag := 'Y';
3098 end if;
3099 if(l_mileage>=0 and l_mileage_adj<>0) then
3100 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0048',abs(l_mileage_adj)*1000000,SIGN(l_mileage_adj),'Y',
3101 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3102 l_corr_flag := 'Y';
3103 end if;
3104 --
3105 /* if(l_free_phone<>0) then
3106 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0054',abs(l_free_phone)*1000000,SIGN(l_free_phone),'N',
3107 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3108 end if;
3109 if(l_free_phone_adj<>0) then
3110 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0054',abs(l_free_phone_adj)*1000000,SIGN(l_free_phone_adj),'Y',
3111 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3112 end if; */
3113 --
3114 if(l_free_phone>0) then
3115 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0054',abs(l_free_phone)*1000000,SIGN(l_free_phone),'N',
3116 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3117
3118 elsif(l_free_phone <0 and (l_free_phone+l_free_phone_adj)<>0) then
3119 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0054',abs(l_free_phone+l_free_phone_adj)*1000000,SIGN(l_free_phone+l_free_phone_adj),'Y',
3120 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3121 l_corr_flag := 'Y';
3122 end if;
3123 if(l_free_phone>=0 and l_free_phone_adj<>0) then
3124 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0054',abs(l_free_phone_adj)*1000000,SIGN(l_free_phone_adj),'Y',
3125 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3126 l_corr_flag := 'Y';
3127 end if;
3128 --
3129 /* if(l_multimedia<>0) then
3130 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0020',abs(l_multimedia)*1000000,SIGN(l_multimedia),'N',
3131 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3132 end if;
3133 if(l_multimedia_adj<>0) then
3134 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0020',abs(l_multimedia_adj)*1000000,SIGN(l_multimedia_adj),'Y',
3135 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3136 end if; */
3137 --
3138 if(l_multimedia>0) then
3139 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0020',abs(l_multimedia)*1000000,SIGN(l_multimedia),'N',
3140 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3141 elsif(l_multimedia<0 and (l_multimedia+l_multimedia_adj)<>0) then
3142 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0020',abs(l_multimedia+l_multimedia_adj)*1000000,SIGN(l_multimedia+l_multimedia_adj),'Y',
3143 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3144 l_corr_flag := 'Y';
3145 end if;
3146 if(l_multimedia>=0 and l_multimedia_adj<>0) then
3147 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0020',abs(l_multimedia_adj)*1000000,SIGN(l_multimedia_adj),'Y',
3148 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3149 l_corr_flag := 'Y';
3150 end if;
3151 --
3152 /* if(l_trivial_matter<>0) then
3153 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0055',abs(l_trivial_matter)*1000000,SIGN(l_trivial_matter),'N',
3154 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3155 end if;
3156 if(l_trivial_matter_adj<>0) then
3157 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0055',abs(l_trivial_matter_adj)*1000000,SIGN(l_trivial_matter_adj),'Y',
3158 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3159 end if; */
3160 --
3161 if(l_trivial_matter>0) then
3162 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0055',abs(l_trivial_matter)*1000000,SIGN(l_trivial_matter),'N',
3163 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3164 elsif(l_trivial_matter<0 and (l_trivial_matter+l_trivial_matter_adj)<>0) then
3165 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0055',abs(l_trivial_matter+l_trivial_matter_adj)*1000000,SIGN(l_trivial_matter+l_trivial_matter_adj),'Y',
3166 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3167 l_corr_flag := 'Y';
3168 end if;
3169 if(l_trivial_matter>=0 and l_trivial_matter_adj<>0) then
3170 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0055',abs(l_trivial_matter_adj)*1000000,SIGN(l_trivial_matter_adj),'Y',
3171 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3172 l_corr_flag := 'Y';
3173 end if;
3174 --
3175 /* if(l_sp_bonus<>0) then
3176 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0069',abs(l_sp_bonus)*1000000,SIGN(l_sp_bonus),'N',
3177 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3178 end if;
3179 if(l_sp_bonus_adj<>0) then
3180 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0069',abs(l_sp_bonus_adj)*1000000,SIGN(l_sp_bonus_adj),'Y',
3181 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3182 end if; */
3183 --
3184 if(l_sp_bonus>0) then
3185 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0069',abs(l_sp_bonus)*1000000,SIGN(l_sp_bonus),'N',
3186 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3187 elsif(l_sp_bonus<0 and (l_sp_bonus+l_sp_bonus_adj)<>0) then
3188 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0069',abs(l_sp_bonus+l_sp_bonus_adj)*1000000,SIGN(l_sp_bonus+l_sp_bonus_adj),'Y',
3189 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3190 l_corr_flag := 'Y';
3191 end if;
3192 if(l_sp_bonus>=0 and l_sp_bonus_adj<>0) then
3193 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0069',abs(l_sp_bonus_adj)*1000000,SIGN(l_sp_bonus_adj),'Y',
3194 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3195 l_corr_flag := 'Y';
3196 end if;
3197 --
3198 /* if(l_pension_sev_pay<>0) then
3199 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0070',abs(l_pension_sev_pay)*1000000,SIGN(l_pension_sev_pay),'N',
3200 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3201 end if;
3202 if(l_pension_sev_pay_adj<>0) then
3203 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0070',abs(l_pension_sev_pay_adj)*1000000,SIGN(l_pension_sev_pay_adj),'Y',
3204 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3205 end if; */
3206 --
3207 if(l_pension_sev_pay>0) then
3208 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0070',abs(l_pension_sev_pay)*1000000,SIGN(l_pension_sev_pay),'N',
3209 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3210 elsif(l_pension_sev_pay<0 and (l_pension_sev_pay+l_pension_sev_pay_adj)<>0) then
3211 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0070',abs(l_pension_sev_pay+l_pension_sev_pay_adj)*1000000,SIGN(l_pension_sev_pay+l_pension_sev_pay_adj),'Y',
3212 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3213 l_corr_flag := 'Y';
3214 end if;
3215 if(l_pension_sev_pay>=0 and l_pension_sev_pay_adj<>0) then
3216 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0070',abs(l_pension_sev_pay_adj)*1000000,SIGN(l_pension_sev_pay_adj),'Y',
3217 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3218 l_corr_flag := 'Y';
3219 end if;
3220 --
3221 /* if(l_physical_gift_sev_pay<>0) then
3222 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0071',abs(l_physical_gift_sev_pay)*1000000,SIGN(l_physical_gift_sev_pay),'N',
3223 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3224 end if;
3225 if(l_physical_gift_sev_pay_adj<>0) then
3226 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0071',abs(l_physical_gift_sev_pay_adj)*1000000,SIGN(l_physical_gift_sev_pay_adj),'Y',
3227 l_payroll_id,l_employment_type,l_period_id,l_green_land_code); 8847591
3228 end if; */
3229 if(l_physical_gift_sev_pay>0) then
3230 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0071',abs(l_physical_gift_sev_pay)*1000000,SIGN(l_physical_gift_sev_pay),'N',
3231 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3232 elsif(l_physical_gift_sev_pay<0 and (l_physical_gift_sev_pay+l_physical_gift_sev_pay_adj)<>0)then
3233 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0071',abs(l_physical_gift_sev_pay+l_physical_gift_sev_pay_adj)*1000000,
3234 SIGN(l_physical_gift_sev_pay+l_physical_gift_sev_pay_adj),'Y',
3235 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3236 l_corr_flag := 'Y';
3237 end if;
3238 if(l_physical_gift_sev_pay>=0 and l_physical_gift_sev_pay_adj<>0) then
3239 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0071',abs(l_physical_gift_sev_pay_adj)*1000000,SIGN(l_physical_gift_sev_pay_adj),'Y',
3240 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3241 l_corr_flag := 'Y';
3242 end if;
3243 /* end of bug 11808484 */
3244 /* bug 12943072 */
3245 if(l_tot_free_res_pay>0) then
3246 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0050',abs(l_tot_free_res_pay)*1000000,SIGN(l_tot_free_res_pay),'N',
3247 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3248 elsif(l_tot_free_res_pay<0 and (l_tot_free_res_pay+l_tot_free_res_pay_adj)<>0)then
3249 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0050',abs(l_tot_free_res_pay+l_tot_free_res_pay_adj)*1000000,
3250 SIGN(l_tot_free_res_pay+l_tot_free_res_pay_adj),'Y',
3251 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3252 l_corr_flag := 'Y';
3253 end if;
3254 if(l_tot_free_res_pay>=0 and l_tot_free_res_pay_adj<>0) then
3255 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0050',abs(l_tot_free_res_pay_adj)*1000000,SIGN(l_tot_free_res_pay_adj),'Y',
3256 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3257 l_corr_flag := 'Y';
3258 end if;
3259
3260 --13463885
3261 OPEN get_value_from_ddf(l_payroll_id, l_canonical_end_date);
3262 FETCH get_value_from_ddf INTO l_uhc_payroll;
3263 CLOSE get_value_from_ddf;
3264
3265 fnd_file.put_line(fnd_file.log,'l_uhc_payroll:'||l_uhc_payroll);
3266 fnd_file.put_line(fnd_file.log,'g_end_date:'||g_end_date||'--l_canonical_end_date--'||l_canonical_end_date);
3267
3268 l_uhc_payroll := NVL(l_uhc_payroll,l_use_holiday_card);
3269
3270 fnd_file.put_line(fnd_file.log,'l_use_holiday_card:'||l_use_holiday_card);
3271
3272 IF (l_use_holiday_card ='N' OR (l_uhc_payroll ='N' AND l_use_holiday_card = 'Y' )) AND l_usr_tbl_exist_400 = 'N' THEN
3273
3274 l_accr_yr := to_char(l_canonical_end_date,'RRRR');
3275 l_last_yr := to_char(TRUNC(l_canonical_end_date,'YEAR') - 1, 'RRRR');
3276
3277 IF to_char(l_canonical_end_date,'MM') <='04' THEN
3278 l_curr_entit_yr := to_char(to_number(l_last_yr)- 1);
3279 l_nxt_entit_yr := l_last_yr;
3280 ELSE
3281 l_curr_entit_yr := l_last_yr;
3282 l_nxt_entit_yr := '0000';
3283 END IF;
3284
3285 fnd_file.put_line(fnd_file.log,'l_accr_yr:'||l_accr_yr);
3286 fnd_file.put_line(fnd_file.log,'l_last_yr:'||l_last_yr);
3287 fnd_file.put_line(fnd_file.log,'l_curr_entit_yr:'||l_curr_entit_yr);
3288 fnd_file.put_line(fnd_file.log,'l_nxt_entit_yr:'||l_nxt_entit_yr);
3289
3290 OPEN cur_max_asg_act_id(l_assignment_id, l_canonical_start_date, l_canonical_end_date);
3291 FETCH cur_max_asg_act_id INTO l_max_asg_act_id;
3292 CLOSE cur_max_asg_act_id;
3293
3294 fnd_file.put_line(fnd_file.log,'l_max_asg_act_id:'||l_max_asg_act_id);
3295
3296 IF l_hourly_salaried='H' THEN
3297
3298 fnd_file.put_line(fnd_file.log,'Entered l_hourly_salaried=H:');
3299
3300 l_hol_pay_0202 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Pay','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3301 fnd_file.put_line(fnd_file.log,'l_hol_pay_0202:'||l_hol_pay_0202);
3302
3303 l_hol_pay_0202_tax := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Tax','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3304 fnd_file.put_line(fnd_file.log,'l_hol_pay_0202_tax:'||l_hol_pay_0202_tax);
3305
3306 l_hol_pay_0202_amb := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday AMB','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3307 fnd_file.put_line(fnd_file.log,'l_hol_pay_0202_amb:'||l_hol_pay_0202_amb);
3308
3309 --l_hol_pay_0202_sp := NVL(GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Hourly Holiday SP','_PAYMENTS' ,l_bal_date),0);
3310
3311 l_net_hol_pay_0202 := l_hol_pay_0202 - (l_hol_pay_0202_tax + l_hol_pay_0202_amb );
3312 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_0202:'||l_net_hol_pay_0202);
3313
3314 IF l_net_hol_pay_0202 <> 0 THEN
3315 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0202',abs(l_net_hol_pay_0202)*1000000,SIGN(l_net_hol_pay_0202),'N',
3316 l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no);
3317 END IF;
3318 ELSIF l_hourly_salaried='S' THEN
3319
3320 fnd_file.put_line(fnd_file.log,'Entered l_hourly_salaried=S:');
3321
3322 l_sal_hol_acr_amt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Accrual Amount','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3323 fnd_file.put_line(fnd_file.log,'l_sal_hol_acr_amt:'||l_sal_hol_acr_amt);
3324
3325 l_sal_hol_acr_tax := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Accrual Tax','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3326 fnd_file.put_line(fnd_file.log,'l_sal_hol_acr_tax:'||l_sal_hol_acr_tax);
3327
3328 l_sal_hol_acr_amb := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Accrual AMB','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3329 fnd_file.put_line(fnd_file.log,'l_sal_hol_acr_amb:'||l_sal_hol_acr_amb);
3330
3331 l_sal_hol_acr_days := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Accrual Days','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3332 fnd_file.put_line(fnd_file.log,'l_sal_hol_acr_days:'||l_sal_hol_acr_days);
3333
3334 -- l_net_hol_pay_6102_1 := l_sal_hol_acr_amt - (l_sal_hol_acr_tax + l_sal_hol_acr_amb); --13630274
3335 l_net_hol_pay_6102_1 := l_sal_hol_acr_amt ;
3336 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_6102_1:'||l_net_hol_pay_6102_1);
3337 IF l_net_hol_pay_6102_1 < 0 THEN
3338 l_sign_net_hol_pay_6102_1 := '-';
3339 ELSE
3340 l_sign_net_hol_pay_6102_1 := '+';
3341 END IF;
3342
3343 l_no_of_hol_6102_1 := l_sal_hol_acr_days;
3344
3345 l_sal_hol_cur_ent_amt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit Amount','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3346 fnd_file.put_line(fnd_file.log,'l_sal_hol_cur_ent_amt:'||l_sal_hol_cur_ent_amt);
3347
3348 l_sal_hol_cur_ent_tax := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit Tax','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3349 fnd_file.put_line(fnd_file.log,'l_sal_hol_cur_ent_tax:'||l_sal_hol_cur_ent_tax);
3350
3351 l_sal_hol_cur_ent_amb := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit AMB','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3352 fnd_file.put_line(fnd_file.log,'l_sal_hol_cur_ent_amb:'||l_sal_hol_cur_ent_amb);
3353
3354 l_sal_hol_cur_ent_days := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit Days','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3355 fnd_file.put_line(fnd_file.log,'l_sal_hol_cur_ent_days:'||l_sal_hol_cur_ent_days);
3356
3357 -- l_net_hol_pay_6102_2 := l_sal_hol_cur_ent_amt - (l_sal_hol_cur_ent_tax + l_sal_hol_cur_ent_amb); --13630274
3358 l_net_hol_pay_6102_2 := l_sal_hol_cur_ent_amt;
3359 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_6102_2:'||l_net_hol_pay_6102_2);
3360 IF l_net_hol_pay_6102_2 < 0 THEN
3361 l_sign_net_hol_pay_6102_2 := '-';
3362 ELSE
3363 l_sign_net_hol_pay_6102_2 := '+';
3364 END IF;
3365
3366 l_no_of_hol_6102_2 := l_sal_hol_cur_ent_days;
3367
3368 l_sal_hol_nxt_ent_amt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Next Entit Amount','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3369 fnd_file.put_line(fnd_file.log,'l_sal_hol_nxt_ent_amt:'||l_sal_hol_nxt_ent_amt);
3370
3371 l_sal_hol_nxt_ent_tax := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Next Entit Tax','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3372 fnd_file.put_line(fnd_file.log,'l_sal_hol_nxt_ent_tax:'||l_sal_hol_nxt_ent_tax);
3373
3374 l_sal_hol_nxt_ent_amb := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Next Entit AMB','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3375 fnd_file.put_line(fnd_file.log,'l_sal_hol_nxt_ent_amb:'||l_sal_hol_nxt_ent_amb);
3376
3377 l_sal_hol_nxt_ent_days := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Next Entit Days','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3378 fnd_file.put_line(fnd_file.log,'l_sal_hol_nxt_ent_days:'||l_sal_hol_nxt_ent_days);
3379
3380 -- l_net_hol_pay_6102_3 := l_sal_hol_nxt_ent_amt - (l_sal_hol_nxt_ent_tax + l_sal_hol_nxt_ent_amb); --13630274
3381 l_net_hol_pay_6102_3 := l_sal_hol_nxt_ent_amt;
3382 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_6102_3:'||l_net_hol_pay_6102_3);
3383 IF l_net_hol_pay_6102_3 < 0 THEN
3384 l_sign_net_hol_pay_6102_3 := '-';
3385 ELSE
3386 l_sign_net_hol_pay_6102_3 := '+';
3387 END IF;
3388
3389 l_no_of_hol_6102_3 := l_sal_hol_nxt_ent_days;
3390
3391 END IF;
3392 END IF;
3393 --13463885
3394
3395 /* if any of the adjustment balances are not null go create a 6000 record for this employee */
3396 /* IF ((l_physical_gift_sev_pay_adj<>0) OR
3397 (l_pension_sev_pay_adj<>0) OR
3398 (l_sp_bonus_adj<>0) OR
3399 (l_trivial_matter_adj<>0) OR
3400 (l_multimedia_adj<>0) OR
3401 (l_free_phone_adj<>0) OR
3402 (l_mileage_adj<>0) OR
3403 (l_total_atp<0) OR
3404 (l_b_income_non_amb_adj<>0) OR
3405 (l_b_income_amb_adj<>0) OR
3406 (l_board_lodge_adj<>0) OR
3407 (l_car_adj<>0) OR
3408 (l_total_amb<0) OR
3409 (l_tax<0) OR
3410 (l_a_income_non_amb_adj<>0) OR
3411 (l_a_income_adj<>0) OR
3412 ( (l_hourly_salaried='H' AND l_hourly_holiday_pay_adj<>0) ) OR
3413 ((l_hourly_salaried='S' and l_monthly_holiday_pay<0) ) OR
3414 ( (l_gross_income<0 AND (l_gross_income+l_amb_pay_adj + l_a_income_non_amb_adj) <> 0))
3415 ) THEN */
3416 IF l_corr_flag = 'Y' THEN
3417
3418 rec_5000R(l_payroll_action_id,l_payroll_id,l_employment_type,l_period_id,l_green_land_code,l_se_no); /* 8847591 */
3419 rec_6000R(p_assignment_action_id,l_payroll_id,l_employment_type,l_period_id,l_ass_id,l_green_land_code,l_se_no); /* 8847591 */
3420 rec_8001R(p_assignment_action_id,l_payroll_id,l_employment_type,l_period_id,rg_csr_get_person_details.PERSON_ID,l_green_land_code);
3421
3422 END IF;
3423
3424
3425
3426
3427
3428 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15');
3429 --
3430
3431
3432 IF (l_yes_no='N' and l_stationed_abroad = 'N') THEN /* Employee is not a foreigner */
3433 --Record - 6002
3434 IF (l_total_atp <> 0) THEN -- 11075581
3435 pay_action_information_api.create_action_information (
3436 p_action_information_id => l_action_info_id
3437 ,p_action_context_id => p_assignment_action_id
3438 ,p_action_context_type => 'AAP'
3439 ,p_object_version_number => l_ovn
3440 ,p_effective_date => g_effective_date
3441 ,p_source_id => NULL
3442 ,p_source_text => NULL
3443 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3444 ,p_action_information1 => 'PYDKEINA'
3445 ,p_action_information2 => l_payroll_action_id
3446 ,p_action_information3 => '6002'
3447 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
3448 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
3449 ,p_action_information6 => '0500'
3450 ,p_action_information7 => '6750000005'
3451 ,p_action_information8 => null
3452 ,p_action_information9 => null
3453 ,p_action_information10 => null
3454 ,p_action_information11 => null
3455 ,p_action_information12 => null
3456 ,p_action_information13 => null
3457 ,p_action_information14 => null
3458 ,p_action_information15 => null
3459 ,p_action_information16 => null
3460 ,p_action_information17 => null
3461 ,p_action_information18 => null
3462 ,p_action_information19 => null
3463 ,p_action_information20 => null
3464 ,p_action_information21 => null
3465 ,p_action_information22 => null
3466 ,p_action_information23 => null
3467 ,p_action_information24 => l_period_id /* 9489806 */
3468 ,p_action_information25 => l_payroll_id -- 9489806
3469 ,p_action_information26 => l_employment_type -- 9489806
3470 ,p_action_information27 => l_green_land_code /* 8847591 */
3471 ,p_action_information28 => null
3472 ,p_action_information29 => null
3473 ,p_action_information30 => null);
3474 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 16: Inserted 6002');
3475 END IF; -- kknadhan end
3476
3477 IF rg_csr_asg_extra_info.aei_information1='Y' THEN
3478 pay_action_information_api.create_action_information (
3479 p_action_information_id => l_action_info_id
3480 ,p_action_context_id => p_assignment_action_id
3481 ,p_action_context_type => 'AAP'
3482 ,p_object_version_number => l_ovn
3483 ,p_effective_date => g_effective_date
3484 ,p_source_id => NULL
3485 ,p_source_text => NULL
3486 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3487 ,p_action_information1 => 'PYDKEINA'
3488 ,p_action_information2 => l_payroll_action_id
3489 ,p_action_information3 => '6003'
3490 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
3491 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
3492 ,p_action_information6 => '0011'
3493 ,p_action_information7 => 'X'
3494 ,p_action_information8 => null
3495 ,p_action_information9 => null
3496 ,p_action_information10 => null
3497 ,p_action_information11 => null
3498 ,p_action_information12 => null
3499 ,p_action_information13 => null
3500 ,p_action_information14 => null
3501 ,p_action_information15 => null
3502 ,p_action_information16 => null
3503 ,p_action_information17 => null
3504 ,p_action_information18 => null
3505 ,p_action_information19 => null
3506 ,p_action_information20 => null
3507 ,p_action_information21 => null
3508 ,p_action_information22 => null
3509 ,p_action_information23 => null
3510 ,p_action_information24 => l_period_id /* 9489806 */
3511 ,p_action_information25 => l_payroll_id -- 9489806
3512 ,p_action_information26 => l_employment_type -- 9489806
3513 ,p_action_information27 => l_green_land_code /* 8847591 */
3514 ,p_action_information28 => null
3515 ,p_action_information29 => null
3516 ,p_action_information30 => null);
3517 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 17: Inserted 6003');
3518 END IF;
3519
3520
3521 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 14');
3522 --Record - 6004
3523 IF (l_total_atp <> 0) THEN
3524 pay_action_information_api.create_action_information (
3525 p_action_information_id => l_action_info_id
3526 ,p_action_context_id => p_assignment_action_id
3527 ,p_action_context_type => 'AAP'
3528 ,p_object_version_number => l_ovn
3529 ,p_effective_date => g_effective_date
3530 ,p_source_id => NULL
3531 ,p_source_text => NULL
3532 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3533 ,p_action_information1 => 'PYDKEINA'
3534 ,p_action_information2 => l_payroll_action_id
3535 ,p_action_information3 => '6004'
3536 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
3537 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
3538 ,p_action_information6 => '0045'
3539 ,p_action_information7 => rg_csr_get_atp_table_value.screen_entry_value -- Input Value(atp table) of Employee ATP
3540 ,p_action_information8 => null
3541 ,p_action_information9 => null
3542 ,p_action_information10 => null
3543 ,p_action_information11 => null
3544 ,p_action_information12 => null
3545 ,p_action_information13 => null
3546 ,p_action_information14 => null
3547 ,p_action_information15 => null
3548 ,p_action_information16 => null
3549 ,p_action_information17 => null
3550 ,p_action_information18 => null
3551 ,p_action_information19 => null
3552 ,p_action_information20 => null
3553 ,p_action_information21 => null
3554 ,p_action_information22 => null
3555 ,p_action_information23 => null
3556 ,p_action_information24 => l_period_id /* 9489806 */
3557 ,p_action_information25 => l_payroll_id -- 9489806
3558 ,p_action_information26 => l_employment_type -- 9489806
3559 ,p_action_information27 => l_green_land_code /* 8847591 */
3560 ,p_action_information28 => null
3561 ,p_action_information29 => null
3562 ,p_action_information30 => null);
3563 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 18: Inserted 6004');
3564 END IF; --15965781
3565
3566 l_total_atp_hours := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total ATP Hours','_ASG_PTD' ,l_bal_date);
3567 l_total_atp_hours := ROUND(l_total_atp_hours,2); -- rounding to 2 decimals
3568
3569 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15');
3570 --Record - 6005
3571 pay_action_information_api.create_action_information (
3572 p_action_information_id => l_action_info_id
3573 ,p_action_context_id => p_assignment_action_id
3574 ,p_action_context_type => 'AAP'
3575 ,p_object_version_number => l_ovn
3576 ,p_effective_date => g_effective_date
3577 ,p_source_id => NULL
3578 ,p_source_text => NULL
3579 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3580 ,p_action_information1 => 'PYDKEINA'
3581 ,p_action_information2 => l_payroll_action_id
3582 ,p_action_information3 => '6005'
3583 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
3584 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
3585 ,p_action_information6 => '0200'
3586 ,p_action_information7 => lpad((l_total_atp_hours*100),8,0)
3587 ,p_action_information8 => '+'
3588 ,p_action_information9 => null
3589 ,p_action_information10 => null
3590 ,p_action_information11 => null
3591 ,p_action_information12 => null
3592 ,p_action_information13 => null
3593 ,p_action_information14 => null
3594 ,p_action_information15 => null
3595 ,p_action_information16 => null
3596 ,p_action_information17 => null
3597 ,p_action_information18 => null
3598 ,p_action_information19 => null
3599 ,p_action_information20 => null
3600 ,p_action_information21 => null
3601 ,p_action_information22 => null
3602 ,p_action_information23 => null
3603 ,p_action_information24 => l_period_id /* 9489806 */
3604 ,p_action_information25 => l_payroll_id -- 9489806
3605 ,p_action_information26 => l_employment_type -- 9489806
3606 ,p_action_information27 => l_green_land_code /* 8847591 */
3607 ,p_action_information28 => null
3608 ,p_action_information29 => null
3609 ,p_action_information30 => null);
3610 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 19: Inserted 6005');
3611 --END IF; --15965781
3612 --13463885
3613 IF (l_use_holiday_card ='N' OR (l_uhc_payroll ='N' AND l_use_holiday_card = 'Y' )) AND l_usr_tbl_exist_400 = 'N' THEN
3614 IF l_hourly_salaried='H' THEN
3615
3616 l_no_of_hol_6005_0203 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Days','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3617 fnd_file.put_line(fnd_file.log,'l_no_of_hol_6005_0203 :'||l_no_of_hol_6005_0203);
3618
3619 IF l_no_of_hol_6005_0203 <> 0 THEN
3620
3621
3622 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15-1');
3623 --Record - 6005
3624 pay_action_information_api.create_action_information (
3625 p_action_information_id => l_action_info_id
3626 ,p_action_context_id => p_assignment_action_id
3627 ,p_action_context_type => 'AAP'
3628 ,p_object_version_number => l_ovn
3629 ,p_effective_date => g_effective_date
3630 ,p_source_id => NULL
3631 ,p_source_text => NULL
3632 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3633 ,p_action_information1 => 'PYDKEINA'
3634 ,p_action_information2 => l_payroll_action_id
3635 ,p_action_information3 => '6005'
3636 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
3637 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
3638 ,p_action_information6 => '0203'
3639 ,p_action_information7 => lpad((l_no_of_hol_6005_0203*100),8,0) --No of earned holidays
3640 ,p_action_information8 => '+'
3641 ,p_action_information9 => null
3642 ,p_action_information10 => null
3643 ,p_action_information11 => null
3644 ,p_action_information12 => null
3645 ,p_action_information13 => null
3646 ,p_action_information14 => null
3647 ,p_action_information15 => null
3648 ,p_action_information16 => null
3649 ,p_action_information17 => null
3650 ,p_action_information18 => null
3651 ,p_action_information19 => null
3652 ,p_action_information20 => null
3653 ,p_action_information21 => null
3654 ,p_action_information22 => null
3655 ,p_action_information23 => null
3656 ,p_action_information24 => l_period_id /* 9489806 */
3657 ,p_action_information25 => l_payroll_id -- 9489806
3658 ,p_action_information26 => l_employment_type -- 9489806
3659 ,p_action_information27 => l_green_land_code /* 8847591 */
3660 ,p_action_information28 => null
3661 ,p_action_information29 => null
3662 ,p_action_information30 => null);
3663
3664 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 19-1: Inserted 6005:0203');
3665
3666 END IF;
3667 END IF;
3668 END IF;
3669 --13463885
3670 END IF; -- end l_yes_no is 'Y'
3671 END IF; --l_present_flag
3672 --13463885
3673 IF (l_use_holiday_card ='N' OR (l_uhc_payroll ='N' AND l_use_holiday_card = 'Y' )) AND l_usr_tbl_exist_400 = 'N' THEN
3674 IF l_hourly_salaried='S' AND l_net_hol_pay_6102_1 <> 0 THEN
3675 --fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15-1');
3676
3677 --Record - 6102
3678 pay_action_information_api.create_action_information (
3679 p_action_information_id => l_action_info_id
3680 ,p_action_context_id => p_assignment_action_id
3681 ,p_action_context_type => 'AAP'
3682 ,p_object_version_number => l_ovn
3683 ,p_effective_date => g_effective_date
3684 ,p_source_id => NULL
3685 ,p_source_text => NULL
3686 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3687 ,p_action_information1 => 'PYDKEINA'
3688 ,p_action_information2 => l_payroll_action_id
3689 ,p_action_information3 => '6102'
3690 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
3691 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
3692 ,p_action_information6 => l_accr_yr --Holiday Earning Year
3693 ,p_action_information7 => lpad((l_net_hol_pay_6102_1*1000000),16,0) --Net Holiday Pay
3694 ,p_action_information8 => l_sign_net_hol_pay_6102_1
3695 ,p_action_information9 => lpad(to_number(trunc(l_no_of_hol_6102_1)||lpad(mod(l_no_of_hol_6102_1*100,100),2,0)),4,0) --13630274 Number of Holidays
3696 ,p_action_information10 => '+'
3697 ,p_action_information11 => to_char(rg_csr_asg_terminator.EFFECTIVE_END_DATE,'yyyymmdd') --Termination date YYYYMMDD
3698 ,p_action_information12 => null
3699 ,p_action_information13 => null
3700 ,p_action_information14 => null
3701 ,p_action_information15 => null
3702 ,p_action_information16 => null
3703 ,p_action_information17 => null
3704 ,p_action_information18 => null
3705 ,p_action_information19 => null
3706 ,p_action_information20 => null
3707 ,p_action_information21 => null
3708 ,p_action_information22 => null
3709 ,p_action_information23 => null
3710 ,p_action_information24 => l_period_id /* 9489806 */
3711 ,p_action_information25 => l_payroll_id -- 9489806
3712 ,p_action_information26 => l_employment_type -- 9489806
3713 ,p_action_information27 => l_green_land_code /* 8847591 */
3714 ,p_action_information28 => null
3715 ,p_action_information29 => null
3716 ,p_action_information30 => null);
3717 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted 6102-1');
3718 END IF;
3719
3720 IF l_hourly_salaried='S' AND l_net_hol_pay_6102_2 <> 0 THEN
3721 --fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15-2');
3722
3723 --Record - 6102
3724 pay_action_information_api.create_action_information (
3725 p_action_information_id => l_action_info_id
3726 ,p_action_context_id => p_assignment_action_id
3727 ,p_action_context_type => 'AAP'
3728 ,p_object_version_number => l_ovn
3729 ,p_effective_date => g_effective_date
3730 ,p_source_id => NULL
3731 ,p_source_text => NULL
3732 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3733 ,p_action_information1 => 'PYDKEINA'
3734 ,p_action_information2 => l_payroll_action_id
3735 ,p_action_information3 => '6102'
3736 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
3737 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
3738 ,p_action_information6 => l_curr_entit_yr --Holiday Earning Year
3739 ,p_action_information7 => lpad((l_net_hol_pay_6102_2*1000000),16,0) --Net Holiday Pay
3740 ,p_action_information8 => l_sign_net_hol_pay_6102_2
3741 ,p_action_information9 => lpad(to_number(trunc(l_no_of_hol_6102_2)||lpad(mod(l_no_of_hol_6102_2*100,100),2,0)),4,0) -- 13630274 Number of Holidays
3742 ,p_action_information10 => '+'
3743 ,p_action_information11 => to_char(rg_csr_asg_terminator.EFFECTIVE_END_DATE,'yyyymmdd') --Termination date YYYYMMDD
3744 ,p_action_information12 => null
3745 ,p_action_information13 => null
3746 ,p_action_information14 => null
3747 ,p_action_information15 => null
3748 ,p_action_information16 => null
3749 ,p_action_information17 => null
3750 ,p_action_information18 => null
3751 ,p_action_information19 => null
3752 ,p_action_information20 => null
3753 ,p_action_information21 => null
3754 ,p_action_information22 => null
3755 ,p_action_information23 => null
3756 ,p_action_information24 => l_period_id /* 9489806 */
3757 ,p_action_information25 => l_payroll_id -- 9489806
3758 ,p_action_information26 => l_employment_type -- 9489806
3759 ,p_action_information27 => l_green_land_code /* 8847591 */
3760 ,p_action_information28 => null
3761 ,p_action_information29 => null
3762 ,p_action_information30 => null);
3763 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted 6102-2');
3764 END IF;
3765
3766 IF l_hourly_salaried='S' AND l_net_hol_pay_6102_3 <> 0 THEN
3767 --fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15-3');
3768
3769 --Record - 6102
3770 pay_action_information_api.create_action_information (
3771 p_action_information_id => l_action_info_id
3772 ,p_action_context_id => p_assignment_action_id
3773 ,p_action_context_type => 'AAP'
3774 ,p_object_version_number => l_ovn
3775 ,p_effective_date => g_effective_date
3776 ,p_source_id => NULL
3777 ,p_source_text => NULL
3778 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3779 ,p_action_information1 => 'PYDKEINA'
3780 ,p_action_information2 => l_payroll_action_id
3781 ,p_action_information3 => '6102'
3782 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
3783 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
3784 ,p_action_information6 => l_nxt_entit_yr --Holiday Earning Year
3785 ,p_action_information7 => lpad((l_net_hol_pay_6102_3*1000000),16,0) --Net Holiday Pay
3786 ,p_action_information8 => l_sign_net_hol_pay_6102_3
3787 ,p_action_information9 => lpad(to_number(trunc(l_no_of_hol_6102_3)||lpad(mod(l_no_of_hol_6102_3*100,100),2,0)),4,0) --13630274 Number of Holidays
3788 ,p_action_information10 => '+'
3789 ,p_action_information11 => to_char(rg_csr_asg_terminator.EFFECTIVE_END_DATE,'yyyymmdd') --Termination date YYYYMMDD
3790 ,p_action_information12 => null
3791 ,p_action_information13 => null
3792 ,p_action_information14 => null
3793 ,p_action_information15 => null
3794 ,p_action_information16 => null
3795 ,p_action_information17 => null
3796 ,p_action_information18 => null
3797 ,p_action_information19 => null
3798 ,p_action_information20 => null
3799 ,p_action_information21 => null
3800 ,p_action_information22 => null
3801 ,p_action_information23 => null
3802 ,p_action_information24 => l_period_id /* 9489806 */
3803 ,p_action_information25 => l_payroll_id -- 9489806
3804 ,p_action_information26 => l_employment_type -- 9489806
3805 ,p_action_information27 => l_green_land_code /* 8847591 */
3806 ,p_action_information28 => null
3807 ,p_action_information29 => null
3808 ,p_action_information30 => null);
3809 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted 6102-3');
3810 END IF;
3811
3812 END IF;
3813 --13463885
3814 END LOOP;
3815 end if; -- end company terminating check condition.
3816 end if; -- g_flag
3817
3818 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 16');
3819 IF g_debug THEN
3820 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
3821 END IF;
3822
3823 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE END');
3824
3825 EXCEPTION
3826 WHEN others THEN
3827 IF g_debug THEN
3828 hr_utility.set_location('error raised in archive code ',5);
3829 END if;
3830 RAISE;
3831 END ARCHIVE_CODE;
3832
3833 PROCEDURE DEINITIALIZATION_CODE
3834 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
3835
3836 /* Cursors to fetch data for record numbering*/
3837
3838 CURSOR csr_all_rec_2001 ( p_payroll_action_id NUMBER) IS
3839 SELECT *
3840 FROM pay_action_information pai
3841 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3842 AND pai.action_information1 = 'PYDKEINA'
3843 AND pai.action_information2 = to_char(p_payroll_action_id)
3844 -- AND pai.action_information3 IN ('1000','2001','2101','5000','6000')
3845 AND pai.action_information3 IN ('1000','2001') -- 9489806
3846 ORDER BY pai.action_information3,action_context_id DESC
3847 FOR UPDATE;
3848
3849 CURSOR csr_all_rec ( p_payroll_action_id NUMBER,p_se_no varchar2) IS
3850 SELECT *
3851 FROM pay_action_information pai
3852 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3853 AND pai.action_information1 = 'PYDKEINA'
3854 AND pai.action_information2 = to_char(p_payroll_action_id)
3855 -- AND pai.action_information3 IN ('1000','2001','2101','5000','6000')
3856 AND pai.action_information3 IN ('5000') -- 9489806
3857 AND pai.action_information11=p_se_no
3858 ORDER BY pai.action_information3,action_context_id DESC
3859 FOR UPDATE;
3860
3861 --13063867
3862 CURSOR csr_all_rec_2101 ( p_payroll_action_id NUMBER,p_se_no varchar2) IS
3863 SELECT *
3864 FROM pay_action_information pai
3865 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3866 AND pai.ACTION_CONTEXT_TYPE = 'AAP'
3867 AND pai.action_information1 = 'PYDKEINA'
3868 AND pai.action_information2 = to_char(p_payroll_action_id)
3869 AND pai.action_information3 IN ('2101') --13063867
3870 AND pai.action_information11= p_se_no
3871 ORDER BY pai.action_information3,action_context_id DESC
3872 FOR UPDATE;
3873 --13063867
3874
3875 /* 9489806 */
3876 CURSOR csr_all_rec_6000 ( p_payroll_action_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER,p_se_no varchar2 ) IS
3877 SELECT *
3878 FROM pay_action_information pai
3879 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3880 AND pai.action_information1 = 'PYDKEINA'
3881 AND pai.action_information2 = to_char(p_payroll_action_id)
3882 AND pai.action_information3 IN ('6000')
3883 AND pai.action_information25=to_char(p_payroll_id)
3884 AND pai.action_information26=p_employement_type
3885 AND pai.action_information24=p_time_period_id
3886 AND pai.action_information27=p_green_land_code /* 8847591 */
3887 AND pai.action_information11=p_se_no
3888 ORDER BY pai.action_information3,action_context_id DESC
3889 FOR UPDATE;
3890
3891 /* 9587046 */
3892 CURSOR csr_all_rec_6000R ( p_payroll_action_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER,p_se_no varchar2 ) IS
3893 SELECT *
3894 FROM pay_action_information pai
3895 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3896 AND pai.action_information1 = 'PYDKEINA'
3897 AND pai.action_information2 = to_char(p_payroll_action_id)
3898 AND pai.action_information3 IN ('6000R')
3899 AND pai.action_information25=to_char(p_payroll_id)
3900 AND pai.action_information26=p_employement_type
3901 AND pai.action_information24=p_time_period_id
3902 AND pai.action_information27=p_green_land_code /* 8847591 */
3903 AND pai.action_information11=p_se_no
3904 ORDER BY pai.action_information3,action_context_id DESC
3905 FOR UPDATE;
3906
3907 CURSOR csr_asg_all_rec ( p_payroll_action_id NUMBER,p_person_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS
3908 SELECT *
3909 FROM pay_action_information pai
3910 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3911 AND pai.action_information1 = 'PYDKEINA'
3912 AND pai.action_information2 = to_char(p_payroll_action_id)
3913 AND pai.action_information4 = to_char(p_person_id)
3914 AND pai.action_information3 IN ('6001','6002','6003','6004','6005','6102') --13463885
3915 AND nvl(pai.action_information29, 'N') <> 'Y'
3916 AND pai.action_information25=to_char(p_payroll_id)
3917 AND pai.action_information26=p_employement_type
3918 AND pai.action_information24=p_time_period_id
3919 AND pai.action_information27=p_green_land_code /* 8847591 */
3920 ORDER BY pai.action_information3,action_context_id,pai.action_information6 DESC
3921 FOR UPDATE;
3922
3923 /* 8616718 */
3924
3925 CURSOR csr_asg_all_rec_corr ( p_payroll_action_id NUMBER,p_person_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS
3926 SELECT *
3927 FROM pay_action_information pai
3928 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3929 AND pai.action_information1 = 'PYDKEINA'
3930 AND pai.action_information2 = to_char(p_payroll_action_id)
3931 AND pai.action_information4 = to_char(p_person_id)
3932 AND pai.action_information3 IN ('6001')
3933 AND nvl(pai.action_information29, 'N') = 'Y'
3934 AND pai.action_information25=to_char(p_payroll_id)
3935 AND pai.action_information26=p_employement_type
3936 AND pai.action_information24=p_time_period_id
3937 AND pai.action_information27=p_green_land_code /* 8847591 */
3938 ORDER BY pai.action_information3,action_context_id,pai.action_information6 DESC
3939 FOR UPDATE;
3940
3941 /* bug 7613211*/
3942 CURSOR csr_asg_8001 (p_payroll_action_id NUMBER,p_person_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS
3943 SELECT *
3944 FROM pay_action_information pai
3945 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3946 AND pai.action_information1 = 'PYDKEINA'
3947 AND pai.action_information2 = to_char(p_payroll_action_id)
3948 AND pai.action_information4 = to_char(p_person_id)
3949 AND pai.action_information3 = '8001'
3950 AND pai.action_information25=to_char(p_payroll_id)
3951 AND pai.action_information26=p_employement_type
3952 AND pai.action_information24=p_time_period_id
3953 AND pai.action_information27=p_green_land_code /* 8847591 */
3954 FOR UPDATE;
3955
3956 /* 9587046R */
3957 CURSOR csr_asg_8001R (p_payroll_action_id NUMBER,p_person_id NUMBER, p_payroll_id NUMBER,p_employement_type NUMBER,p_time_period_id NUMBER,p_green_land_code NUMBER) IS
3958 SELECT *
3959 FROM pay_action_information pai
3960 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
3961 AND pai.action_information1 = 'PYDKEINA'
3962 AND pai.action_information2 = to_char(p_payroll_action_id)
3963 AND pai.action_information4 = to_char(p_person_id)
3964 AND pai.action_information3 = '8001R'
3965 AND pai.action_information25=to_char(p_payroll_id)
3966 AND pai.action_information26=p_employement_type
3967 AND pai.action_information24=p_time_period_id
3968 AND pai.action_information27=p_green_land_code /* 8847591 */
3969 FOR UPDATE;
3970
3971
3972 l_action_info_id NUMBER;
3973 l_ovn NUMBER;
3974 l_end_code NUMBER;
3975 /* 8616718 */
3976 l_bincome_flag varchar2(1):='N';
3977 l_other_flag varchar2(1):='N';
3978
3979 /* 9001660 */
3980 l_foreign_flag varchar2(1):='N';
3981 l_foreign_count NUMBER:=0;
3982 l_6000_count NUMBER:=0;
3983 l_5000_updated VARCHAR2(1):='N';
3984 l_5000R_updated VARCHAR2(1):='N';
3985 l_6000R_updated VARCHAR2(1):='N'; /* 9587046R */
3986
3987 BEGIN
3988 l_end_code:=0 ;
3989 if(g_flag=0) then
3990 fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 1');
3991
3992 FOR rg_csr_all_rec_2001 IN csr_all_rec_2001( p_payroll_action_id)
3993 LOOP
3994 IF rg_csr_all_rec_2001.action_information3 in ('1000') THEN
3995 l_end_code:=l_end_code + 1 ;
3996 fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 2');
3997
3998 UPDATE pay_action_information pai
3999 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4000 WHERE CURRENT OF csr_all_rec_2001;
4001
4002 END IF;
4003
4004 fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 3');
4005 fnd_file.put_line(fnd_file.log,'payroll action id:'||p_payroll_action_id);
4006 fnd_file.put_line(fnd_file.log,'action info 4:'||rg_csr_all_rec_2001.action_information4);
4007 hr_utility.set_location('DEINIT:rg_csr_all_rec.action_information3: ' || rg_csr_all_rec_2001.action_information3,390);
4008 hr_utility.set_location('DEINIT:rg_csr_all_rec.action_information25: ' || rg_csr_all_rec_2001.action_information25,390);
4009 IF rg_csr_all_rec_2001.action_information3='2001' THEN
4010 l_end_code:=l_end_code + 1 ;
4011 UPDATE pay_action_information pai
4012 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4013 WHERE CURRENT OF csr_all_rec_2001;
4014
4015 --13063867
4016 FOR rg_csr_all_rec_2101 IN csr_all_rec_2101 ( p_payroll_action_id, rg_csr_all_rec_2001.action_information4 )
4017 LOOP
4018 l_end_code:=l_end_code + 1 ;
4019 UPDATE pay_action_information pai
4020 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4021 WHERE CURRENT OF csr_all_rec_2101;
4022 END LOOP;
4023 --13063867
4024
4025 -- IF rg_csr_all_rec.action_information3='5000' THEN
4026 FOR rg_csr_all_rec IN csr_all_rec( p_payroll_action_id,rg_csr_all_rec_2001.action_information4)
4027 loop
4028 l_5000_updated:='N';
4029 FOR rg_csr_all_rec_6000 IN csr_all_rec_6000(p_payroll_action_id,rg_csr_all_rec.action_information25,rg_csr_all_rec.action_information9,rg_csr_all_rec.action_information24,rg_csr_all_rec.action_information8,rg_csr_all_rec_2001.action_information4)
4030 LOOP
4031 IF l_5000_updated='N' THEN
4032 l_end_code:=l_end_code + 1 ;
4033 UPDATE pay_action_information pai
4034 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4035 WHERE CURRENT OF csr_all_rec;
4036 l_5000_updated:='Y';
4037 END IF;
4038
4039 l_end_code:=l_end_code + 1 ;
4040 UPDATE pay_action_information pai
4041 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4042 WHERE CURRENT OF csr_all_rec_6000;
4043 hr_utility.set_location('DEINIT:entered csr_all_rec_6000: ' || rg_csr_all_rec_6000.action_information4,390);
4044 /* bug 7613211 start*/
4045 /* 9001660 */
4046 l_6000_count:=l_6000_count+1;
4047 fnd_file.put_line(fnd_file.log,'l_foreign_flag :'|| l_foreign_flag);
4048 FOR rg_csr_asg_8001 IN csr_asg_8001( p_payroll_action_id,to_number(rg_csr_all_rec_6000.action_information4),rg_csr_all_rec.action_information25
4049 ,rg_csr_all_rec.action_information9,rg_csr_all_rec.action_information24,rg_csr_all_rec.action_information8)
4050 LOOP
4051 l_end_code:=l_end_code + 1 ;
4052 UPDATE pay_action_information pai
4053 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4054 WHERE CURRENT OF csr_asg_8001;
4055 fnd_file.put_line(fnd_file.log,'rg_csr_all_rec_6000.action_information7:'|| rg_csr_all_rec_6000.action_information7);
4056
4057 /* 9001660 */
4058 l_foreign_count:=l_foreign_count+1;
4059 IF rg_csr_all_rec_6000.action_information7='0000000000' THEN
4060 l_foreign_flag:='Y';
4061 END IF;
4062 fnd_file.put_line(fnd_file.log,'l_foreign_flag :'|| l_foreign_flag);
4063 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 4');
4064 END LOOP;
4065 /* bug 7613211 end*/
4066
4067 FOR rg_csr_asg_all_rec IN csr_asg_all_rec( p_payroll_action_id,to_number(rg_csr_all_rec_6000.action_information4),rg_csr_all_rec.action_information25
4068 ,rg_csr_all_rec.action_information9,rg_csr_all_rec.action_information24,rg_csr_all_rec.action_information8) /* 8847591 */
4069 LOOP
4070 l_end_code:=l_end_code + 1 ;
4071 UPDATE pay_action_information pai
4072 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4073 WHERE CURRENT OF csr_asg_all_rec;
4074 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 4');
4075
4076 IF (rg_csr_asg_all_rec.action_information6='0038' or rg_csr_asg_all_rec.action_information6='0036') and rg_csr_asg_all_rec.action_information3='6001' THEN /* 8616718 */
4077 l_bincome_flag:='Y';
4078 ELSIF rg_csr_asg_all_rec.action_information3='6001' and rg_csr_asg_all_rec.action_information6 <> '0016' THEN /* 8861878 */
4079 l_other_flag:='Y';
4080 END IF;
4081 fnd_file.put_line(fnd_file.log,'Pay Type code :'|| rg_csr_asg_all_rec.action_information6);
4082 fnd_file.put_line(fnd_file.log,'l_bincome_flag'|| l_bincome_flag);
4083 fnd_file.put_line(fnd_file.log,'l_other_flag'|| l_other_flag);
4084
4085
4086 END LOOP;
4087 END LOOP; -- record 6000 loop
4088 /* 8616718 */
4089 fnd_file.put_line(fnd_file.log,'before correctl_bincome_flag'|| l_bincome_flag);
4090 fnd_file.put_line(fnd_file.log,'before correct l_other_flag'|| l_other_flag);
4091 l_5000R_updated:='N';
4092 FOR rg_csr_all_rec_6000 IN csr_all_rec_6000R(p_payroll_action_id,rg_csr_all_rec.action_information25,rg_csr_all_rec.action_information9,rg_csr_all_rec.action_information24,rg_csr_all_rec.action_information8,rg_csr_all_rec_2001.action_information4)
4093 LOOP
4094 l_6000R_updated:='N'; /* 9587046R */
4095 FOR rg_csr_asg_all_rec IN csr_asg_all_rec_corr( p_payroll_action_id,to_number(rg_csr_all_rec_6000.action_information4),rg_csr_all_rec.action_information25
4096 ,rg_csr_all_rec.action_information9,rg_csr_all_rec.action_information24,rg_csr_all_rec.action_information8) /* 8847591 */
4097 LOOP
4098 fnd_file.put_line(fnd_file.log,'before payroll_id'|| rg_csr_all_rec.action_information25);
4099 fnd_file.put_line(fnd_file.log,'before time period id '|| rg_csr_all_rec.action_information24);
4100 IF l_5000R_updated='N' THEN
4101 fnd_file.put_line(fnd_file.log,'entered iff l_5000R_updated');
4102 l_end_code:=l_end_code + 1 ;
4103 UPDATE pay_action_information pai
4104 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4105 WHERE pai.action_information3 = '5000R'
4106 AND pai.action_information2= to_char(p_payroll_action_id)
4107 AND pai.action_information9=rg_csr_all_rec.action_information9
4108 AND pai.action_information8=rg_csr_all_rec.action_information8 /* 9587046 */
4109 AND pai.action_information25=rg_csr_all_rec.action_information25
4110 AND pai.action_information24=rg_csr_all_rec.action_information24
4111 and pai.action_information11=rg_csr_all_rec_2001.action_information4;
4112
4113 l_5000R_updated:='Y';
4114 END IF; /* 9587046R */
4115 IF l_6000R_updated='N' THEN
4116 l_end_code:=l_end_code + 1 ;
4117 UPDATE pay_action_information pai
4118 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4119 WHERE pai.action_information3 = '6000R'
4120 AND pai.action_information2 = to_char(p_payroll_action_id)
4121 AND pai.action_information4 = to_char(rg_csr_all_rec_6000.action_information4)
4122 AND pai.action_information25=rg_csr_all_rec_6000.action_information25
4123 AND pai.action_information27=rg_csr_all_rec_6000.action_information27 /* 9587046 */
4124 AND pai.action_information26=rg_csr_all_rec_6000.action_information26
4125 AND pai.action_information24=rg_csr_all_rec_6000.action_information24
4126 and pai.action_information11=rg_csr_all_rec_2001.action_information4;
4127 l_6000R_updated:='Y';
4128 /* 9587046R */
4129 FOR rg_csr_asg_8001 IN csr_asg_8001R( p_payroll_action_id,to_number(rg_csr_all_rec_6000.action_information4),rg_csr_all_rec.action_information25
4130 ,rg_csr_all_rec.action_information9,rg_csr_all_rec.action_information24,rg_csr_all_rec.action_information8)
4131 LOOP
4132 l_end_code:=l_end_code + 1 ;
4133 UPDATE pay_action_information pai
4134 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4135 WHERE CURRENT OF csr_asg_8001R;
4136 END LOOP;
4137
4138 END IF;
4139 fnd_file.put_line(fnd_file.log,'l_5000R_updated'|| l_5000R_updated);
4140 l_end_code:=l_end_code + 1 ;
4141 UPDATE pay_action_information pai
4142 SET pai.action_information30 =LPAD(l_end_code,7,'0')
4143 WHERE CURRENT OF csr_asg_all_rec_corr;
4144
4145 IF (rg_csr_asg_all_rec.action_information6='0038' or rg_csr_asg_all_rec.action_information6='0036') and rg_csr_asg_all_rec.action_information3='6001' THEN /* 8616718 */
4146 l_bincome_flag:='Y';
4147 ELSIF rg_csr_asg_all_rec.action_information3='6001' and rg_csr_asg_all_rec.action_information6 <> '0016' THEN /* 8861878 */
4148 l_other_flag:='Y';
4149 END IF;
4150 fnd_file.put_line(fnd_file.log,'Pay Type code in corrected record :'|| rg_csr_asg_all_rec.action_information6);
4151
4152 END LOOP;
4153 fnd_file.put_line(fnd_file.log,'after correctl_bincome_flag'|| l_bincome_flag);
4154 fnd_file.put_line(fnd_file.log,'after correct l_other_flag'|| l_other_flag);
4155 END LOOP; -- for 5000
4156 -- END IF;
4157 end loop ;
4158 end if; /* 10367494 */
4159 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 5');
4160 END LOOP;
4161 l_end_code:=l_end_code + 1 ;
4162 fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 6');
4163 /* 9001660 */
4164 IF l_foreign_flag='Y' THEN
4165 /*
4166 UPDATE pay_action_information pai
4167 SET pai.action_information9='09'
4168 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
4169 AND pai.action_information1 = 'PYDKEINA'
4170 AND pai.action_information2 = to_char(p_payroll_action_id)
4171 AND pai.action_information3 ='5000'; */
4172
4173 --
4174 fnd_file.put_line(fnd_file.log,'l_foreign_count :'||l_foreign_count);
4175 fnd_file.put_line(fnd_file.log,'l_6000_count :'|| l_6000_count);
4176 IF l_foreign_count<>l_6000_count THEN
4177 -- Raise l_bincome_exception;
4178 NULL;
4179 END IF;
4180 END IF;
4181 /* 8616718 */
4182 IF l_bincome_flag='Y' THEN
4183 /* UPDATE pay_action_information pai
4184 SET pai.action_information9='05'
4185 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
4186 AND pai.action_information1 = 'PYDKEINA'
4187 AND pai.action_information2 = to_char(p_payroll_action_id)
4188 AND pai.action_information3 ='5000'; */
4189 NULL;
4190 IF l_other_flag='Y' THEN
4191 -- Raise l_bincome_exception;
4192 NULL;
4193 END IF;
4194 END IF;
4195
4196
4197 pay_action_information_api.create_action_information (
4198 p_action_information_id=> l_action_info_id,
4199 p_action_context_id=> p_payroll_action_id,
4200 p_action_context_type=> 'PA',
4201 p_object_version_number=> l_ovn,
4202 p_effective_date=> g_effective_date,
4203 p_source_id=> NULL,
4204 p_source_text=> NULL,
4205 p_action_information_category=> 'EMEA REPORT INFORMATION',
4206 p_action_information1=> 'PYDKEINA',
4207 p_action_information2=> p_payroll_action_id,
4208 p_action_information3=> '9999',
4209 p_action_information4=> l_end_code,
4210 p_action_information5=> NULL,
4211 p_action_information6=> NULL,
4212 p_action_information7=> NULL,
4213 p_action_information8=> NULL,
4214 p_action_information9=> NULL,
4215 p_action_information10=> NULL,
4216 p_action_information11=> NULL,
4217 p_action_information12=> NULL,
4218 p_action_information13=> NULL,
4219 p_action_information14=> NULL,
4220 p_action_information15=> NULL,
4221 p_action_information16=> NULL,
4222 p_action_information17=> NULL,
4223 p_action_information18=> NULL,
4224 p_action_information19=> NULL,
4225 p_action_information20=> NULL,
4226 p_action_information21=> NULL,
4227 p_action_information22=> NULL,
4228 p_action_information23=> NULL,
4229 p_action_information24=> NULL,
4230 p_action_information25=> NULL,
4231 p_action_information26=> NULL,
4232 p_action_information27=> NULL,
4233 p_action_information28=> NULL,
4234 p_action_information29=> NULL,
4235 p_action_information30=> LPAD(l_end_code,7,'0')
4236 );
4237
4238 end if; -- g_flag
4239 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 7');
4240 IF g_debug THEN
4241 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
4242 END IF;
4243
4244 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 8');
4245 EXCEPTION
4246 WHEN l_bincome_exception THEN
4247 /* 9001660 */
4248 /* fnd_file.put_line(fnd_file.log,'Error raised in DEINITIALIZATION_CODE : The Payroll used for processing normal employees must not be used for processing employees with only B income.
4249 Please process the employees with only B Income on a different Payroll.');
4250 */
4251 fnd_file.put_line(fnd_file.log,'Error raised in DEINITIALIZATION_CODE :'|| fnd_message.get_string('PER','HR_377071_DK_FORIEGN_BINCOME'));
4252 RAISE;
4253 WHEN others THEN
4254 IF g_debug THEN
4255 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
4256 END if;
4257 RAISE;
4258 END;
4259
4260 BEGIN
4261
4262 g_payroll_action_id :=NULL;
4263 g_le_assignment_action_id :=NULL;
4264 g_business_group_id :=NULL;
4265 g_legal_employer_id :=NULL;
4266 g_effective_date :=NULL;
4267 g_payroll_id :=NULL;
4268 g_payroll_period :=NULL;
4269 g_test_submission :=NULL;
4270 g_company_terminating :=NULL;
4271 g_sender_id :=NULL;
4272
4273 END PAY_DK_ARCHIVE_EIN;