[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_NES_REPORT_PKG
Source
1 PACKAGE BODY PAY_IE_NES_REPORT_PKG AS
2 /* $Header: pyienes.pkb 120.10 2010/09/30 09:07:01 knadhan noship $ */
3
4 g_package VARCHAR2(50) := 'PAY_IE_nes_REPORT_PKG.';
5 EOL VARCHAR2(5) := fnd_global.local_chr(10);
6 l_errflag VARCHAR2(1) := 'N';
7 error_message boolean;
8 l_str_Common VARCHAR2(2000);
9 l_employee_categories VARCHAR(200);
10 l_nes_exception exception;
11
12 /* 9734490 */
13 --
14 FUNCTION test_XML(P_STRING VARCHAR2) RETURN VARCHAR2 AS
15 l_string varchar2(1000);
16
17 FUNCTION replace_xml_symbols(pp_string IN VARCHAR2)
18 RETURN VARCHAR2
19 AS
20
21 ll_string VARCHAR2(1000);
22
23 BEGIN
24
25
26 ll_string := pp_string;
27
28 ll_string := replace(ll_string, '&', '&');
29 ll_string := replace(ll_string, '<', '<');
30 ll_string := replace(ll_string, '>', '>');
31 ll_string := replace(ll_string, '''',''');
32 ll_string := replace(ll_string, '"', '"');
33
34 RETURN ll_string;
35 EXCEPTION when no_data_found then
36 null;
37 END replace_xml_symbols;
38
39 begin
40 l_string := p_string;
41 l_string := replace_xml_symbols(l_string);
42
43 l_string := pay_ie_p35_magtape.test_XML(l_string);
44
45 RETURN l_string;
46 END ;
47 --
48
49
50 -----------------------------------------------------------------------
51 -- setup_balance_table
52 -----------------------------------------------------------------------
53
54 PROCEDURE setup_balance_table
55 IS
56
57 CURSOR csr_balance_dimension(p_balance IN CHAR,
58 p_dimension IN CHAR) IS
59 SELECT pdb.defined_balance_id
60 FROM pay_balance_types pbt,
61 pay_balance_dimensions pbd,
62 pay_defined_balances pdb
63 WHERE pdb.balance_type_id = pbt.balance_type_id
64 AND pdb.balance_dimension_id = pbd.balance_dimension_id
65 AND pbt.balance_name = p_balance
66 AND pbd.database_item_suffix = p_dimension
67 AND pbd.legislation_code = 'IE'
68 AND pbd.business_group_id is NULL
69 AND pbt.legislation_code = 'IE'
70 AND pbt.business_group_id is NULL
71 AND pdb.legislation_code = 'IE'
72 AND pdb.business_group_id is NULL;
73
74 l_archive_index NUMBER := 0;
75 l_dimension VARCHAR2(100) := '_ASG_QTD';
76 l_max_stat_balance NUMBER := 24;
77 l_index_id NUMBER := 0;
78 l_index_id1 NUMBER := 0;
79
80 l_proc VARCHAR2(120) := g_package || 'setup_balance_table';
81 l_no_bal NUMBER :=26; -- 9768428
82 BEGIN
83
84 hr_utility.set_location('Entering ' || l_proc,10);
85 hr_utility.set_location('Step ' || l_proc,20);
86
87 g_balance_name(1).balance_name := 'Regular Earnings';
88 g_balance_name(2).balance_name := 'Irregular Earnings';
89 g_balance_name(3).balance_name := 'Overtime Payments';
90 g_balance_name(4).balance_name := 'Paid Overtime Hours';
91 g_balance_name(5).balance_name := 'Paid Maternity Hours';
92 g_balance_name(6).balance_name := 'Paid Sick Leave Hours';
93 g_balance_name(7).balance_name := 'Paid Other Leave Hours';
94
95 g_balance_name(8).balance_name := 'Voluntary Sickness Insurance';
96 g_balance_name(9).balance_name := 'Staff Housing';
97 g_balance_name(10).balance_name := 'Other Benefits';
98 g_balance_name(11).balance_name := 'Other Subsidies';
99
100 g_balance_name(12).balance_name := 'IE BIK Company Vehicle';
101 g_balance_name(13).balance_name := 'Normal Working Hours';
102 g_balance_name(14).balance_name := 'Paid Maternity Days';
103 g_balance_name(15).balance_name := 'Paid Other Leave Days';
104 g_balance_name(16).balance_name := 'Paid Sick Leave Days';
105
106
107 g_balance_name(17).balance_name := 'IE PRSI Insurable Weeks';
108 g_balance_name(18).balance_name := 'IE PRSI K Term Insurable Weeks';
109 g_balance_name(19).balance_name := 'IE PRSI M Term Insurable Weeks';
110
111 g_balance_name(20).balance_name := 'Regular EarningsMONTH';
112 g_balance_name(21).balance_name := 'Overtime PaymentsMONTH';
113
114 g_balance_name(22).balance_name := 'Paid Holiday Hours';
115 g_balance_name(23).balance_name := 'Paid Holiday Days';
116 g_balance_name(24).balance_name := 'Regular Shift Allowance';
117 g_balance_name(25).balance_name := 'Irregular Shift Allowance';
118 g_balance_name(26).balance_name := 'Total Commission';
119
120 hr_utility.set_location('Step = ' || l_proc,30);
121
122 FOR l_index IN 1 .. g_balance_name.COUNT
123 LOOP
124
125 IF g_balance_name(l_index).balance_name in ('Regular Shift Allowance','Irregular Shift Allowance','Total Commission'
126 ,'Paid Overtime Hours','Normal Working Hours') THEN
127 l_dimension := '_PER_PAYE_REF_MONTH';
128 ELSE
129 l_dimension := '_PER_PAYE_REF_YTD';
130 END IF;
131
132 l_index_id := l_index_id +1 ;
133 hr_utility.set_location('l_index = ' || l_index,30);
134 hr_utility.set_location('balance_name = ' || g_balance_name(l_index).balance_name,30);
135 hr_utility.set_location('l_dimension = ' || l_dimension,30);
136
137
138
139 IF g_balance_name(l_index).balance_name ='Regular EarningsMONTH' THEN
140 OPEN csr_balance_dimension('Regular Earnings',
141 '_PER_PAYE_REF_MONTH');
142 ELSIF g_balance_name(l_index).balance_name ='Overtime PaymentsMONTH' THEN
143 OPEN csr_balance_dimension('Overtime Payments',
144 '_PER_PAYE_REF_MONTH');
145
146 ELSE
147 OPEN csr_balance_dimension(g_balance_name(l_index).balance_name,
148 l_dimension);
149 END IF;
150
151 FETCH csr_balance_dimension
152 INTO g_def_bal_id(l_index_id).defined_balance_id;
153
154 g_def_bal_id(l_index_id).balance_name := g_balance_name(l_index).balance_name;
155
156 IF csr_balance_dimension%NOTFOUND
157 THEN
158 g_def_bal_id(l_index_id).defined_balance_id := 0;
159 END IF;
160
161 CLOSE csr_balance_dimension;
162
163 hr_utility.set_location('Balance Name = ' || g_def_bal_id(l_index_id).balance_name,30);
164 hr_utility.set_location('defined_balance_id = ' || g_def_bal_id(l_index_id).defined_balance_id,30);
165
166 END LOOP;
167
168 hr_utility.set_location('Step ' || l_proc,50);
169 /* 9768428 start */
170 l_index_id1:=l_index_id;
171 l_index_id :=0;
172
173 FOR l_index IN 1 .. g_balance_name.COUNT
174 LOOP
175
176 IF g_balance_name(l_index).balance_name in ('Regular Shift Allowance','Irregular Shift Allowance','Total Commission'
177 ,'Paid Overtime Hours','Normal Working Hours') THEN
178 l_dimension := '_PER_PAYE_REF_PPSN_REF_MONTH';
179 ELSE
180 l_dimension := '_PER_PAYE_REF_PPSN_YTD';
181 END IF;
182
183 l_index_id1 := l_index_id1 + 1 ;
184 l_index_id := l_index_id +1 ;
185
186 hr_utility.set_location('l_index = ' || l_index,30);
187 hr_utility.set_location('l_index_id = ' || l_index_id,30);
188 hr_utility.set_location('l_index_id1 = ' || l_index_id1,30);
189 hr_utility.set_location('balance_name = ' || g_balance_name(l_index).balance_name,30);
190 hr_utility.set_location('l_dimension = ' || l_dimension,30);
191
192
193
194 IF g_balance_name(l_index_id).balance_name ='Regular EarningsMONTH' THEN
195 OPEN csr_balance_dimension('Regular Earnings',
196 '_PER_PAYE_REF_PPSN_REF_MONTH');
197 ELSIF g_balance_name(l_index_id).balance_name ='Overtime PaymentsMONTH' THEN
198 OPEN csr_balance_dimension('Overtime Payments',
199 '_PER_PAYE_REF_PPSN_REF_MONTH');
200
201 ELSE
202 OPEN csr_balance_dimension(g_balance_name(l_index_id).balance_name,
203 l_dimension);
204 END IF;
205
206 FETCH csr_balance_dimension
207 INTO g_def_bal_id(l_index_id1).defined_balance_id;
208
209 g_def_bal_id(l_index_id1).balance_name := g_balance_name(l_index).balance_name||'PPSN';
210
211 IF csr_balance_dimension%NOTFOUND
212 THEN
213 g_def_bal_id(l_index_id1).defined_balance_id := 0;
214 END IF;
215
216 CLOSE csr_balance_dimension;
217
218 hr_utility.set_location('Balance Name = ' || g_def_bal_id(l_index_id1).balance_name,30);
219 hr_utility.set_location('defined_balance_id = ' || g_def_bal_id(l_index_id1).defined_balance_id,30);
220
221 END LOOP;
222 /* 9768428 end */
223 hr_utility.set_location('Leaving ' || l_proc,60);
224
225 END setup_balance_table;
226
227 -----------------------------------------------------------------------
228 -- GET_PARAMETERS
229 -----------------------------------------------------------------------
230 PROCEDURE get_parameters
231 (
232 p_payroll_action_id IN NUMBER,
233 p_token_name IN VARCHAR2,
234 p_token_value out nocopy VARCHAR2
235 ) IS
236
237 CURSOR csr_parameter_info
238 (
239 p_pact_id NUMBER,
240 p_token CHAR
241 ) IS
242
243 SELECT TRIM(SUBSTR
244 (
245 legislative_parameters,
246 DECODE(INSTR
247 (
248 legislative_parameters,
249 p_token
250 ),0,LENGTH(legislative_parameters),INSTR
251 (
252 legislative_parameters,
253 p_token
254 )) + (LENGTH(p_token) + 1),
255 DECODE(INSTR
256 (
257 legislative_parameters,
258 ' ',
259 INSTR
260 (
261 legislative_parameters,
262 p_token
263 )),0,LENGTH(legislative_parameters),INSTR
264 (
265 legislative_parameters,
266 ' ',
267 INSTR
268 (
269 legislative_parameters,
270 p_token
271 )))
272 -
273 (
274 INSTR
275 (
276 legislative_parameters,
277 p_token
278 ) + LENGTH(p_token)
279 )
280 )),
281 TRIM(business_group_id)
282 FROM pay_payroll_actions
283 WHERE payroll_action_id = p_pact_id;
284
285 l_business_group_id VARCHAR2(300);
286 l_token_value VARCHAR2(300);
287 l_proc VARCHAR2(50) := g_package ||'get_parameters';
288 l_token_name VARCHAR2(300); /* 7367314QA */
289
290 /*6856473 */
291
292 CURSOR csr_comments (
293 p_pact_id NUMBER,
294 p_token CHAR
295 ) IS
296 SELECT TRIM(SUBSTR
297 (
298 legislative_parameters,
299 DECODE(
300 INSTR(
301 legislative_parameters,
302 p_token
303 )--INSTR
304 ,0,LENGTH(legislative_parameters),
305 INSTR
306 (
307 legislative_parameters,
308 p_token
309 )--INSTR 2 DEFAULT FOR DECODE
310 )--CLOSE DECODE
311 + (LENGTH(p_token) ),--END OF SECOND PARAMETER FOR SUBSTR
312 LENGTH(legislative_parameters)
313 )
314 )
315
316 FROM pay_payroll_actions
317 WHERE payroll_action_id = p_pact_id;
318
319
320 BEGIN
321
322 hr_utility.set_location('Entering ' || l_proc, 100);
323 hr_utility.set_location('p_token_name ' || TO_CHAR(p_token_name), 110);
324
325 OPEN csr_parameter_info
326 (
327 p_payroll_action_id,
328 p_token_name
329 );
330 FETCH csr_parameter_info INTO l_token_value, l_business_group_id;
331 CLOSE csr_parameter_info;
332
333 hr_utility.set_location('l_token_value ' || TO_CHAR(l_token_value), 115);
334 hr_utility.set_location('l_business_group_id ' || TO_CHAR(l_business_group_id), 120);
335
336 IF p_token_name = 'BG_ID' THEN
337 p_token_value := l_business_group_id;
338 hr_utility.set_location('p_token_name '||p_token_name,125);
339 /*6856473 */
340
341 ELSIF p_token_name= 'COMMENTS' THEN
342 hr_utility.set_location('comments before replace ' || TO_CHAR(l_token_value), 120);
343 l_token_name:=p_token_name||'=';
344 OPEN csr_comments
345 (
346 p_payroll_action_id,
347 l_token_name /* 7367314QA */
348 );
349 FETCH csr_comments INTO l_token_value;
350 CLOSE csr_comments;
351 hr_utility.set_location('comments after replace ' || TO_CHAR(l_token_value), 120);
352 p_token_value := l_token_value;
353 ELSE
354 p_token_value := l_token_value;
355 --7367314
356 IF p_token_name= 'ADD_CHANGE' THEN
357 IF p_token_value = 'Y' THEN
358 p_token_value := '1' ;
359 ELSE
360 p_token_value := '0' ;
361 END IF;
362 END IF;
363 --7367314
364 hr_utility.set_location('p_token_name '||p_token_name,130);
365 END IF;
366
367 hr_utility.set_location('Leaving ' || l_proc, 135);
368 --
369 EXCEPTION
370 WHEN others THEN
371 hr_utility.set_location('Leaving' || l_proc,140);
372 p_token_value := NULL;
373 --
374 END get_parameters;
375 -----------------------------------------------------------------------
376 -- GET_ALL_PARAMETERS
377 -----------------------------------------------------------------------
378 PROCEDURE get_all_parameters(p_payroll_action_id IN NUMBER
379 ,p_rep_group OUT NOCOPY VARCHAR2
380 ,p_payroll_id OUT NOCOPY VARCHAR2
381 ,p_year OUT NOCOPY VARCHAR2
382 ,p_quarter OUT NOCOPY VARCHAR2
383 ,p_month OUT NOCOPY VARCHAR2
384 ,p_sample_fraction OUT NOCOPY VARCHAR2
385 ,p_business_Group_id OUT NOCOPY VARCHAR2
386 ,p_assignment_set_id OUT NOCOPY VARCHAR2
387 ,p_occupational_category OUT NOCOPY VARCHAR2
388 ,p_employer_id OUT NOCOPY VARCHAR2
389 ,p_report_type OUT NOCOPY VARCHAR2
390 ,p_declare_date OUT NOCOPY VARCHAR2
391 ,p_change_add OUT NOCOPY VARCHAR2
392 ,p_send_emp OUT NOCOPY VARCHAR2
393 ,p_comments OUT NOCOPY VARCHAR2
394 )
395 IS
396
397 CURSOR cur_nat_min_wg
398 IS
399 SELECT fnd_number.canonical_to_number(global_value)
400 FROM ff_globals_f
401 WHERE GLOBAL_NAME = 'IE_NAT_MIN_WAGE_RATE'
402 AND legislation_code = 'IE'
403 AND g_archive_effective_date BETWEEN effective_start_date AND effective_end_date;
404
405 CURSOR cur_inc_exc_flag
406 IS
407 SELECT DISTINCT hasa.include_or_exclude inc_or_exc
408 FROM
409 hr_assignment_set_amendments hasa,
410 hr_assignment_sets has
411 WHERE hasa.assignment_set_id = has.assignment_set_id
412 AND has.business_group_id = p_business_Group_id
413 AND has.assignment_set_id = p_assignment_set_id;
414
415 l_occupational_catg VARCHAR2(50);
416 l_cur_inc_exc_flag_rec cur_inc_exc_flag%rowtype;
417
418 BEGIN
419 hr_utility.set_location(' Entering PAY_IE_NES_REPORT.get_all_parameters ', 200);
420
421 get_parameters(p_payroll_action_id,'REP_GROUP',p_rep_group);
422 get_parameters(p_payroll_action_id,'PAYROLL',p_payroll_id);
423 get_parameters(p_payroll_action_id,'YEAR',p_year);
424 get_parameters(p_payroll_action_id,'MONTH',p_month);
425 get_parameters(p_payroll_action_id,'SAMPLE',p_sample_fraction);
426 get_parameters(p_payroll_action_id,'BG_ID',p_business_Group_id);
427 get_parameters(p_payroll_action_id,'ASSIGNMENT_SET_ID',p_assignment_set_id);
428 get_parameters(p_payroll_action_id,'OCCUPATION',p_occupational_category);
429 get_parameters(p_payroll_action_id,'EMPLOYER',p_employer_id);
430 get_parameters(p_payroll_action_id,'REPTYPE',p_report_type);
431 get_parameters(p_payroll_action_id,'DDATE',p_declare_date);
432 get_parameters(p_payroll_action_id,'ADD_CHANGE',p_change_add);
433 get_parameters(p_payroll_action_id,'SEND_EMP',p_send_emp);
434 get_parameters(p_payroll_action_id,'COMMENTS',p_comments);
435
436 hr_utility.set_location(' After last get_parameters call ', 210);
437
438 IF p_month in ('01','02','03') THEN
439 g_qtr_start_date := to_date('01/01/' || p_year,'DD/MM/RRRR');
440 g_qtr_end_date := to_date('31/03/' || p_year,'DD/MM/RRRR');
441 p_quarter:=1;
442 ELSIF p_month in ('04','05','06') THEN
443 g_qtr_start_date := to_date('01/04/' || p_year,'DD/MM/RRRR');
444 g_qtr_end_date := to_date('30/06/' || p_year,'DD/MM/RRRR');
445 p_quarter:=2;
446 ELSIF p_month in ('07','08','09') THEN
447 g_qtr_start_date := to_date('01/07/' || p_year,'DD/MM/RRRR');
448 g_qtr_end_date := to_date('30/09/' || p_year,'DD/MM/RRRR');
449 p_quarter:=3;
450 ELSIF p_month in ('10','11','12') THEN
451 g_qtr_start_date := to_date('01/10/' || p_year,'DD/MM/RRRR');
452 g_qtr_end_date := to_date('31/12/' || p_year,'DD/MM/RRRR');
453 p_quarter:=4;
454 END IF;
455
456 hr_utility.set_location(' After populating the Quarter dates. ', 220);
457
458
459
460 OPEN cur_inc_exc_flag;
461 FETCH cur_inc_exc_flag INTO l_cur_inc_exc_flag_rec;
462 CLOSE cur_inc_exc_flag;
463
464 g_exc_inc := l_cur_inc_exc_flag_rec.inc_or_exc;
465
466 hr_utility.set_location(' After cur_inc_exc_flag Cursor ', 235);
467 hr_utility.set_location(' l_cur_inc_exc_flag_rec.inc_or_exc '||l_cur_inc_exc_flag_rec.inc_or_exc, 235);
468 hr_utility.set_location(' p_business_Group_id = '||p_business_Group_id,240);
469 hr_utility.set_location(' REP_GROUP = '||p_rep_group,240);
470 hr_utility.set_location(' PAYROLL = '||p_payroll_id,240);
471 hr_utility.set_location(' YEAR = '||p_year,240);
472 hr_utility.set_location(' MONTH = '||p_month,240);
473 hr_utility.set_location(' SAMPLE = '||p_sample_fraction,240);
474 hr_utility.set_location(' QUARTER = '||p_quarter,240);
475 hr_utility.set_location(' ASSIGNMENT_SET_ID = '||p_assignment_set_id,240);
476 hr_utility.set_location(' OCCUPATION CATEGORY = '||p_occupational_category,240);
477 hr_utility.set_location(' EMPLOYER = '||p_employer_id,240);
478 hr_utility.set_location(' REPORT TYPE = '||p_report_type,240);
479 hr_utility.set_location(' DDATE = '||p_declare_date,240);
480 hr_utility.set_location(' ADD_CHANGE = '||p_change_add,240);
481 hr_utility.set_location(' SEND_EMP = '|| p_send_emp,240);
482 hr_utility.set_location(' COMMENTS = '||p_comments,240);
483 hr_utility.set_location(' g_qtr_start_date = '||g_qtr_start_date,240);
484 hr_utility.set_location(' g_qtr_end_date = '||g_qtr_end_date,240);
485
486 IF p_occupational_category IS NOT NULL THEN
487
488 SELECT NVL(hruserdt.get_table_value(p_business_Group_id,'EHECS_CATG_TAB','Managers',p_occupational_category,g_qtr_start_date),
489 NVL(hruserdt.get_table_value(p_business_Group_id,'EHECS_CATG_TAB','Clerical Workers',p_occupational_category,g_qtr_start_date),
490 hruserdt.get_table_value(p_business_Group_id,'EHECS_CATG_TAB','Production Workers',p_occupational_category,g_qtr_start_date))) ff
491 into g_occupational_category_M_C_P
492 FROM dual;
493
494
495 END IF;
496
497 hr_utility.set_location(' g_occupational_category_M_C_P = '||g_occupational_category_M_C_P,245);
498
499 IF p_payroll_id IS NOT NULL THEN
500 g_where_clause :=
501 ' and papf.payroll_id = '||to_char(p_payroll_id);
502 ELSE
503 g_where_clause :=' and 1=1 ';
504 END IF;
505
506 IF p_occupational_category IS NOT NULL THEN
507 g_where_clause1 :=
508 ' and paaf.employee_category = '||to_char(g_occupational_category);
509
510 ELSE
511 g_where_clause1 :=' and 1=1 ';
512 END IF;
513
514 IF p_assignment_set_id IS NOT NULL THEN
515 IF l_cur_inc_exc_flag_rec.inc_or_exc = 'I' THEN
516 g_where_clause_asg_set := ' AND EXISTS(SELECT 1
517 FROM hr_assignment_set_amendments hasa
518 , hr_assignment_sets has
519 , per_assignments_f paf
520 WHERE has.assignment_set_id = hasa.assignment_set_id
521 AND has.business_group_id = paaf.business_group_id
522 AND has.assignment_set_id = '|| p_assignment_set_id
523 ||' AND hasa.assignment_id = paf.assignment_id
524 AND paf.person_id = ppf.person_id) ';
525 ELSIF l_cur_inc_exc_flag_rec.inc_or_exc = 'E' THEN
526 g_where_clause_asg_set := ' AND NOT EXISTS(SELECT 1
527 FROM hr_assignment_set_amendments hasa
528 , hr_assignment_sets has
529 , per_assignments_f paf
530 WHERE has.assignment_set_id = hasa.assignment_set_id
531 AND has.business_group_id = paaf.business_group_id
532 AND has.assignment_set_id = '|| p_assignment_set_id
533 ||' AND hasa.assignment_id = paf.assignment_id
534 AND paf.person_id = ppf.person_id) ';
535 ELSIF l_cur_inc_exc_flag_rec.inc_or_exc IS NULL THEN
536 g_where_clause_asg_set := ' and 1=2 ';
537 END IF;
538 ELSE
539 g_where_clause_asg_set := ' and 1=1 ';
540 END IF;
541
542
543 hr_utility.set_location(' Inside get_all_parameters:g_where_clause: '||g_where_clause,250);
544 hr_utility.set_location(' Inside get_all_parameters:g_where_clause1: '||g_where_clause1,260);
545 hr_utility.set_location(' Inside get_all_parameters:g_where_clause1: '||g_where_clause_asg_set,265);
546
547 hr_utility.set_location(' Leaving: PAY_IE_NES_REPORT.get_all_parameters: ', 270);
548
549 EXCEPTION
550 WHEN Others THEN
551 hr_utility.set_location(' Leaving: PAY_IE_nes_REPORT.get_all_parameters with errors: ', 280);
552 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1215);
553 END get_all_parameters;
554
555 -----------------------------------------------------------------------
556 -- RANGE_CODE
557 -----------------------------------------------------------------------
558 PROCEDURE range_code(pactid IN NUMBER,
559 sqlstr OUT nocopy VARCHAR2)
560 IS
561 l_procedure_name VARCHAR2(100);
562
563 l_year varchar2(50);
564 l_month varchar2(50);
565 l_quarter varchar2(50);
566 l_assignment_set_id varchar2(50);
567 l_occupational_category varchar2(50);
568 l_report_type varchar2(50);
569 l_declare_date varchar2(50):=' ';
570 l_change_indicator varchar2(50):=' ';
571 l_comments varchar2(300);
572
573 CURSOR csr_archive_effective_date(pactid NUMBER) IS
574 SELECT effective_date
575 FROM pay_payroll_actions
576 WHERE payroll_action_id = pactid;
577
578 CURSOR csr_employer_details(c_org_id hr_organization_information.organization_id%type
579 ,c_bg_id hr_organization_units.business_group_id%type) IS
580 select hou.organization_id org_id
581 ,hou.name employer_name
582 ,hla.address_line_1 addr1
583 ,hla.address_line_2 addr2
584 ,hla.address_line_3 addr3
585 ,null addr4
586 ,null addr5
587 ,hla.TOWN_OR_CITY City
588 ,flv.meaning County
589 ,hla.COUNTRY Country_Name
590 ,hla.REGION_1
591 from hr_organization_units hou
592 ,hr_organization_information hoi
593 ,hr_locations_all hla
594 ,fnd_lookup_values flv
595 where hoi.org_information_context='IE_EMPLOYER_INFO'
596 and hoi.organization_id=c_org_id
597 and hoi.organization_id=hou.organization_id
598 and hou.business_group_id= c_bg_id
599 and hou.location_id=hla.location_id(+)
600 and flv.lookup_type(+) = 'IE_COUNTY'
601 and flv.language(+) = 'US'
602 and flv.lookup_code(+) = hla.REGION_1;
603
604
605 CURSOR csr_declarant(c_org_id hr_organization_information.organization_id%type
606 ,c_bg_id hr_organization_units.business_group_id%type
607 ,p_year varchar2
608 ,p_qtr varchar2) IS
609 select hoi.org_information3 cbr_no
610 ,hoi.org_information13 person_id
611 ,hoi.org_information17 position
612 ,hoi.org_information19 email
613 ,hoi.org_information20 phone
614 from hr_organization_units hou
615 ,hr_organization_information hoi
616 where hoi.org_information_context='IE_EHECS'
617 and hoi.organization_id=c_org_id
618 and hoi.organization_id=hou.organization_id
619 and hou.business_group_id= c_bg_id
620 and hoi.ORG_INFORMATION1 = p_year
621 and hoi.ORG_INFORMATION2 = p_qtr;
622
623 CURSOR csr_declarant_details(c_person_id per_all_people_f.person_id%type) is
624 select papf.full_name declarant_name
625 from per_all_people_f papf
626 where papf.person_id=c_person_id ;
627
628
629 l_employer_details csr_employer_details%rowtype;
630 l_declarant csr_declarant%rowtype;
631 l_declarant_details csr_declarant_details%rowtype;
632 l_action_info_id NUMBER;
633 l_ovn NUMBER;
634 l_org_id hr_organization_units.organization_id%type;
635 l_employer_name hr_organization_units.name%type;
636
637 l_addr1 hr_locations_all.address_line_1%type;
638 l_addr2 hr_locations_all.address_line_2%type;
639 l_addr3 hr_locations_all.address_line_3%type;
640 l_addr4 hr_locations_all.address_line_3%type;
641 l_addr5 hr_locations_all.address_line_3%type;
642 l_cbr_no hr_organization_information.org_information3%type;
643 l_person_id hr_organization_information.org_information13%type;
644 l_declarant_name per_all_people_f.full_name%type;
645 l_declarant_phone per_all_people_f.office_number%type ;
646 l_declarant_email_add per_all_people_f.email_address%type;
647 l_declarant_position per_all_positions.name%type;
648
649 Type tab_address is table of per_addresses.ADDRESS_LINE1%type index by binary_integer;
650 pl_address tab_address;
651 pl_address_final tab_address;
652 k NUMBER(3) := 0;
653
654
655 BEGIN
656
657 l_procedure_name := g_package||'range_code';
658
659 hr_utility.set_location('Entering '||l_procedure_name, 300);
660 hr_utility.set_location('pactid '||TO_CHAR(pactid), 300);
661
662 sqlstr := ' select distinct p.person_id' ||
663 ' from per_people_f p,' ||
664 ' pay_payroll_actions pa' ||
665 ' where pa.payroll_action_id = :payroll_action_id' ||
666 ' and p.business_group_id = pa.business_group_id' ||
667 ' order by p.person_id';
668
669 -------------
670
671 OPEN csr_archive_effective_date(pactid);
672 FETCH csr_archive_effective_date
673 INTO g_archive_effective_date;
674 CLOSE csr_archive_effective_date;
675
676 hr_utility.set_location('After fetching the g_archive_effective_date '||g_archive_effective_date, 310);
677
678 get_all_parameters(pactid
679 ,g_rep_group
680 ,g_payroll_id
681 ,l_year
682 ,l_quarter
683 ,l_month
684 ,g_sample_fraction
685 ,g_business_group_id
686 ,l_assignment_set_id
687 ,l_occupational_category
688 ,g_employer_id
689 ,l_report_type
690 ,l_declare_date
691 ,l_change_indicator
692 ,g_send_emp
693 ,l_comments);
694
695 hr_utility.set_location(' g_rep_group '|| TO_CHAR(g_rep_group) , 300);
696 hr_utility.set_location(' g_payroll_id '|| TO_CHAR(g_payroll_id) , 300);
697 hr_utility.set_location(' l_year '|| TO_CHAR(l_year) , 300);
698 hr_utility.set_location(' l_month '|| TO_CHAR(l_month) , 300);
699 hr_utility.set_location(' g_sample_fraction '|| TO_CHAR(g_sample_fraction) , 300);
700 hr_utility.set_location(' l_quarter '|| TO_CHAR(l_quarter) , 300);
701 hr_utility.set_location(' g_business_group_id '|| TO_CHAR(g_business_group_id) , 300);
702 hr_utility.set_location(' l_assignment_set_id '|| TO_CHAR(l_assignment_set_id) , 300);
703 hr_utility.set_location(' l_occupational_category '|| TO_CHAR(l_occupational_category) , 300);
704 hr_utility.set_location(' g_employer_id '|| TO_CHAR(g_employer_id) , 300);
705 hr_utility.set_location(' l_report_type '|| TO_CHAR(l_report_type) , 300);
706 hr_utility.set_location(' l_declare_date '|| TO_CHAR(l_declare_date) , 300);
707 hr_utility.set_location(' l_change_indicator '|| TO_CHAR(l_change_indicator) , 300);
708 hr_utility.set_location(' g_send_emp '|| TO_CHAR(g_send_emp) , 300);
709 hr_utility.set_location(' l_comments '|| l_comments , 300);
710
711 g_year := l_year;
712 g_quarter := l_quarter;
713 g_month:=l_month;
714 g_occupational_category := l_occupational_category;
715 g_assignment_set_id := l_assignment_set_id;
716
717 hr_utility.set_location('before the call of setup_balance_table in '||l_procedure_name, 320);
718 setup_balance_table;
719
720 hr_utility.set_location('After the call of setup_balance_table in '||l_procedure_name, 320);
721
722 OPEN csr_employer_details(g_employer_id, g_business_group_id);
723 FETCH csr_employer_details INTO l_employer_details;
724 CLOSE csr_employer_details;
725
726 hr_utility.set_location('After fetching the csr_employer_details ', 330);
727
728 l_org_id := l_employer_details.org_id;
729 l_employer_name := l_employer_details.employer_name;
730 /*
731 l_addr1 := l_employer_details.addr1;
732 l_addr2 := l_employer_details.addr2;
733 l_addr3 := l_employer_details.addr3;
734 l_addr4 := ' ';
735 l_addr5 := ' '; */
736
737 hr_utility.set_location(' Before deleting the PL table pl_address. ',1100);
738 pl_address.delete;
739
740 pl_address(1) := l_employer_details.addr1;
741 pl_address(2) := l_employer_details.addr2;
742 pl_address(3) := l_employer_details.addr3;
743 pl_address(4) := l_employer_details.City;
744 pl_address(5) := l_employer_details.COUNTY;
745 pl_address(6) := l_employer_details.Country_Name;
746
747
748 hr_utility.set_location(' pl_address.COUNT: '||pl_address.COUNT,1100);
749
750 hr_utility.set_location(' pl_address(1): '||pl_address(1),1100);
751 hr_utility.set_location(' pl_address(2): '||pl_address(2),1100);
752 hr_utility.set_location(' pl_address(3): '||pl_address(3),1100);
753 hr_utility.set_location(' pl_address(4): '||pl_address(4),1100);
754 hr_utility.set_location(' pl_address(5): '||pl_address(5),1100);
755 hr_utility.set_location(' pl_address(6): '||pl_address(6),1100);
756
757 hr_utility.set_location(' Before deleting the PL table pl_address_final. ',1100);
758 pl_address_final.delete;
759 hr_utility.set_location(' Initializing the PL table pl_address_final. ',1100);
760
761 FOR j in 1..pl_address.LAST
762 LOOP
763 IF pl_address(j) IS NOT NULL THEN
764 k:=k+1;
765 pl_address_final(k) := pl_address(j);
766 hr_utility.set_location('pl_address_final'||k||'--'||pl_address_final(k),1100);
767 END IF;
768 END LOOP;
769
770 hr_utility.set_location(' Re Initializing the record l_person_details. ',1100);
771
772
773
774 hr_utility.set_location(' Re Initializing the cursor record l_person_details with actual values. ',1100);
775 hr_utility.set_location(' pl_address_final.COUNT: '||pl_address_final.COUNT,1100);
776
777 IF pl_address_final.COUNT > 0 THEN
778
779 /* l_employer_details.addr1 := NULL;
780 l_employer_details.addr2 := NULL;
781 l_employer_details.addr3 := NULL;
782 l_employer_details.addr4 := NULL;
783 l_employer_details.addr5 := NULL; */
784
785 FOR l in 1..pl_address_final.LAST
786 LOOP
787 hr_utility.set_location(' Inside the loop of PL table pl_address_final',1100);
788 BEGIN
789 IF l = 1 THEN
790 hr_utility.set_location(' employer address .addr1 ',1100);
791 l_addr1 := pl_address_final(1);
792 hr_utility.set_location('employer address .addr1 ',1101);
793 END IF;
794 --
795 IF l = 2 THEN
796 hr_utility.set_location(' employer address.addr2 ',1102);
797 l_addr2 := pl_address_final(2);
798 hr_utility.set_location(' employer address .addr2 ',1103);
799 END IF;
800 --
801 IF l = 3 THEN
802 hr_utility.set_location(' employer address.addr3 ',1104);
803 l_addr3 := pl_address_final(3);
804 hr_utility.set_location(' employer address .addr3 ',1105);
805 END IF;
806 --
807 IF l = 4 THEN
808 hr_utility.set_location(' l_person_details.addr4 ',1106);
809 l_addr4 := pl_address_final(4);
810 hr_utility.set_location(' employer address .addr4 ',1107);
811 END IF;
812 IF l = 5 THEN
813 hr_utility.set_location(' l_person_details.addr5 ',1106);
814 l_addr5 := pl_address_final(5);
815 hr_utility.set_location(' employer address .addr5 ',1107);
816 END IF;
817 EXCEPTION
818 WHEN NO_DATA_FOUND THEN
819 NULL;
820 END;
821 END LOOP;
822 END IF;
823 hr_utility.set_location(' After Re Initializing the cursor record l_person_details with actual values. ',1100);
824
825 hr_utility.set_location('After fetching the csr_employer_details l_org_id'||l_org_id, 330);
826 hr_utility.set_location('After fetching the csr_employer_details l_employer_name'||l_employer_name, 330);
827 hr_utility.set_location('After fetching the csr_employer_details l_addr1'||l_addr1, 330);
828 hr_utility.set_location('After fetching the csr_employer_details l_addr2'||l_addr2, 330);
829 hr_utility.set_location('After fetching the csr_employer_details l_addr3'||l_addr3, 330);
830
831 OPEN csr_declarant(g_employer_id, g_business_group_id, l_year, l_quarter);
832 FETCH csr_declarant INTO l_declarant;
833 CLOSE csr_declarant;
834 l_cbr_no := l_declarant.cbr_no;
835 l_person_id := l_declarant.person_id;
836 l_declarant_position := l_declarant.position; -- bug 6850742
837 l_declarant_phone := l_declarant.phone; -- bug 6850742
838 l_declarant_email_add := l_declarant.email; -- bug 6850742
839
840 hr_utility.set_location('After fetching the csr_declarant ', 340);
841 hr_utility.set_location('After fetching the l_cbr_no '||l_cbr_no, 340);
842 hr_utility.set_location('After fetching the l_person_id '||l_person_id, 340);
843 hr_utility.set_location('l_declarant_position '||l_declarant_position, 340);
844 hr_utility.set_location('l_declarant_phone '||l_declarant_phone, 340);
845 hr_utility.set_location('l_declarant_email_add '||l_declarant_email_add, 340);
846
847 OPEN csr_declarant_details(l_person_id);
848 FETCH csr_declarant_details into l_declarant_details;
849 CLOSE csr_declarant_details;
850
851 hr_utility.set_location('After fetching the csr_declarant_details ', 350);
852
853 l_declarant_name := l_declarant_details.declarant_name;
854
855
856 hr_utility.set_location('l_declarant_name '||l_declarant_name, 350);
857
858 IF l_employer_name IS NULL THEN
859 l_errflag := 'Y';
860 --Fnd_file.put_line(FND_FILE.LOG,'Employer name is missing. Please enter it first.' );
861 Fnd_file.put_line(FND_FILE.LOG,'You have not entered the employer name. Enter a valid employer name.' );
862 END IF;
863 IF l_addr1 IS NULL OR l_addr2 IS NULL THEN
864 l_errflag := 'Y';
865 --Fnd_file.put_line(FND_FILE.LOG,'Employer Address line 1/2 is missing. Please enter it first.' );
866 Fnd_file.put_line(FND_FILE.LOG,'You have not entered a complete address for the employer. Enter a valid address.' );
867 END IF;
868 IF l_cbr_no IS NULL THEN
869 l_errflag := 'Y';
870 --Fnd_file.put_line(FND_FILE.LOG,'CBR Number is missing. Please enter it first.' );
871 Fnd_file.put_line(FND_FILE.LOG,'You have not entered the CBR Number. Enter a valid CBR Number.' );
872 END IF;
873 IF l_declarant_name IS NULL OR l_declarant_phone IS NULL OR
874 l_declarant_email_add IS NULL OR l_declarant_position IS NULL THEN
875 l_errflag := 'Y';
876 --Fnd_file.put_line(FND_FILE.LOG,'Declarant details are missing. Please check for Name, Phone, Email, Position.' );
877 Fnd_file.put_line(FND_FILE.LOG,'You have not entered the declaration contact details. Enter the Name, Phone, Email and Position of the declaration contact.' );
878 END IF;
879
880 hr_utility.set_location('l_errflag '||l_errflag, 370);
881
882 IF l_errflag = 'Y' THEN
883 Fnd_file.put_line(FND_FILE.LOG,'Some mandatory data is misssing.' );
884 Raise l_nes_exception;
885 END IF;
886
887 hr_utility.set_location('Before entering record for IE_NES_HEADER ', 380);
888
889 pay_action_information_api.create_action_information
890 ( p_action_information_id => l_action_info_id
891 ,p_action_context_id => pactid
892 ,p_action_context_type => 'PA'
893 ,p_object_version_number => l_ovn
894 ,p_effective_date => g_archive_effective_date --g_end_date
895 ,p_source_id => NULL
896 ,p_source_text => NULL
897 ,p_action_information_category => 'IE_NES_HEADER'
898 ,p_action_information6 => l_year
899 ,p_action_information7 => l_month
900 ,p_action_information8 => l_report_type
901 ,p_action_information9 => 'Oracle HRMS' --SOFTWARE NAME (HARD CODED)
902 ,p_action_information10 => '1.0' --SOFTWARE VERSION(HARD CODED)
903 ,p_action_information11 => 'Oracle Corporation' --VENDOR NAME
904 ,p_action_information12 => '870-4000-900' --'870.4000.900' --VENDOR PHONE --7367314
905 ,p_action_information13 => l_org_id
906 ,p_action_information14 => l_employer_name
907 ,p_action_information15 => l_addr1
908 ,p_action_information16 => l_addr2
909 ,p_action_information17 => l_addr3
910 ,p_action_information18 => l_addr4
911 ,p_action_information19 => l_addr5
912 ,p_action_information20 => l_change_indicator --parameter
913 ,p_action_information21 => l_cbr_no
914 ,p_action_information22 => l_declarant_name
915 ,p_action_information23 => l_declarant_phone
916 ,p_action_information24 => l_declarant_email_add
917 ,p_action_information25 => l_declare_date
918 ,p_action_information26 => l_declarant_position
919 );
920
921 hr_utility.set_location('After entering record for IE_NES_HEADER ', 390);
922
923 hr_utility.set_location('Leaving '||l_procedure_name, 400);
924
925 EXCEPTION
926 WHEN l_nes_exception THEN
927 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,410);
928 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','NES Report errors out. Some mandatory values are missing.');
929 WHEN Others THEN
930 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,410);
931 END range_code;
932 -----------------------------------------------------------------------
933 -- ASSIGNMENT_ACTION_CODE
934 -----------------------------------------------------------------------
935
936 PROCEDURE assignment_action_code(pactid in number,
937 stperson in number,
938 endperson in number,
939 chunk in number)
940 IS
941 l_assg_id per_assignments_f.assignment_id%TYPE;
942 l_person_id Number;
943 l_assignment_number per_all_assignments_f.assignment_number%type;
944 l_period_of_service_id per_all_assignments_f.period_of_service_id%type;
945
946 l_prev_person_id Number := 0;
947 l_prev_period_of_service_id per_all_assignments_f.period_of_service_id%type := 0;
948
949 l_start_date VARCHAR2(50);
950 l_end_date VARCHAR2(50);
951 l_select_str VARCHAR2(3000);
952 l_select_str1 VARCHAR2(3000);
953 lockingactid NUMBER;
954 l_valid_assg boolean := False;
955 l_file_type pay_element_entry_values_f.screen_entry_value%TYPE;
956 l_submitted pay_element_entry_values_f.screen_entry_value%TYPE;
957 l_element_name varchar2(50);
958
959 l_total_count NUMBER;
960 l_total_sample NUMBER;
961 TYPE asg_ref IS REF CURSOR;
962 csr_get_asg asg_ref;
963 csr_get_asg1 asg_ref;
964
965 l_ass_check varchar2(1);
966 l_csr_already_archived pay_element_entry_values_f.screen_entry_value%TYPE := 'N';
967 BEGIN
968
969 hr_utility.set_location('Entering PAY_IE_NES_REPORT_PKG.assignment_action_code',500);
970
971 -- Get all the parameters
972 /*6978389 */
973 hr_utility.set_location('Before get_all_parameters',501);
974 get_all_parameters(pactid
975 ,g_rep_group
976 ,g_payroll_id
977 ,g_year
978 ,g_quarter
979 ,g_month
980 ,g_sample_fraction
981 ,g_business_group_id
982 ,g_assignment_set_id
983 ,g_occupational_category
984 ,g_employer_id
985 ,g_report_type
986 ,g_declare_date
987 ,g_change_indicator
988 ,g_send_emp
989 ,g_comments);
990 hr_utility.set_location('after get_all_parameters',502);
991 hr_utility.set_location(' g_qtr_start_date = '||g_qtr_start_date,505);
992 hr_utility.set_location(' g_qtr_end_date = '||g_qtr_end_date,505);
993 hr_utility.set_location(' g_business_group_id = '||g_business_group_id,505);
994 hr_utility.set_location(' g_employer_id = '||g_employer_id,505);
995
996 g_reference_start_date:=to_date('01'||'/'||g_month||'/'||g_year,'dd/mm/yyyy');
997 g_reference_end_date:=add_months(to_date('01'||'/'||g_month||'/'||g_year,'dd/mm/yyyy'),1)-1;
998
999 hr_utility.set_location(' g_reference_start_date = '|| g_reference_start_date,505);
1000 hr_utility.set_location(' g_reference_end_date = '|| g_reference_end_date,505);
1001
1002 l_select_str1 :='select count (distinct paaf.assignment_id )
1003 from per_all_assignments_f paaf,
1004 per_all_people_f ppf,
1005 pay_all_payrolls_f papf,
1006 pay_payroll_actions ppa,
1007 pay_assignment_actions paa,
1008 hr_soft_coding_keyflex scl
1009 where paaf.business_group_id = '|| g_business_group_id
1010 ||' and papf.business_group_id = paaf.business_group_id and '
1011 ||''''|| g_reference_end_date||''''||' between paaf.effective_start_date and paaf.effective_end_date '
1012 ||' and paaf.person_id = ppf.person_id '
1013 ||' and paaf.assignment_status_type_id in (SELECT assignment_status_type_id '
1014 ||' FROM per_assignment_status_types '
1015 ||' WHERE per_system_status = '||'''ACTIVE_ASSIGN'''
1016 ||' AND active_flag = '||'''Y'''||') '
1017 ||' and paaf.primary_flag= '||'''Y'''
1018 ||' and paaf.employment_category = '
1019 -- ||' IN ('||'''FT'''||','||'''FR'''||','||'''PT'''||','||'''PR'''||','||'''AT'''||') '
1020 ||'nvl(hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Full_Time'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
1021 || ',nvl(hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Part_Time'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
1022 || ', hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Apprentice_Trainee'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
1023 || ' ))'
1024
1025 ||' and ppf.person_id between '|| stperson || ' and ' || endperson
1026 ||g_where_clause1
1027 ||' and ppa.payroll_action_id = paa.payroll_action_id '
1028 ||' and paa.assignment_id=paaf.assignment_id'
1029 ||' and (paa.source_action_id is not null or ppa.action_type in ('||'''I'''||','||'''V'''||','||'''B'''||'))'
1030 ||' and ppa.effective_date <='||''''||g_reference_end_date||''''
1031 ||' and ppa.effective_date >='||''''||g_reference_start_date||''''
1032 ||' and paa.action_status = '||'''C'''
1033 ||' and ppa.action_type in ('||'''R'''||','||'''Q'''||','||'''I'''||','
1034 ||'''V'''||','||'''B'''||')'
1035 ||' and papf.payroll_id = paaf.payroll_id '
1036 ||' and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id '
1037 ||' and scl.segment4 = to_char('||g_employer_id||') '
1038 ||g_where_clause
1039 ||g_where_clause_asg_set
1040 ||' Order by paaf.assignment_id';
1041
1042
1043 hr_utility.set_location(' before cursor l_total_count' || l_total_count,530);
1044 OPEN csr_get_asg1 FOR l_select_str1;
1045 FETCH csr_get_asg1 INTO l_total_count;
1046 CLOSE csr_get_asg1;
1047 hr_utility.set_location(' after fetch l_total_count' || l_total_count,530);
1048 hr_utility.set_location(' g_sample_fraction' || g_sample_fraction,530);
1049 l_total_sample:= round(l_total_count /g_sample_fraction);
1050 hr_utility.set_location(' l_total_sample' || l_total_sample,530);
1051
1052
1053 hr_utility.set_location('Before building the dynamic query.',510);
1054 /* 6856486 modified the employment_category 's IN condition to a value fetch from USER TABLE EHECS_ASG_CATG_TAB */
1055 l_select_str :='select asgid from (
1056 select asgid,round(mod(dbms_random.value*'||g_sample_fraction||','||g_sample_fraction||')) serial '
1057 || 'from ( '
1058 || 'select distinct paaf.assignment_id asgid
1059 from per_all_assignments_f paaf,
1060 per_all_people_f ppf,
1061 pay_all_payrolls_f papf,
1062 pay_payroll_actions ppa,
1063 pay_assignment_actions paa,
1064 hr_soft_coding_keyflex scl
1065 where paaf.business_group_id = '|| g_business_group_id
1066 ||' and papf.business_group_id = paaf.business_group_id and '
1067 ||''''|| g_reference_end_date||''''||' between paaf.effective_start_date and paaf.effective_end_date '
1068 ||' and paaf.person_id = ppf.person_id '
1069 ||' and paaf.assignment_status_type_id in (SELECT assignment_status_type_id '
1070 ||' FROM per_assignment_status_types '
1071 ||' WHERE per_system_status = '||'''ACTIVE_ASSIGN'''
1072 ||' AND active_flag = '||'''Y'''||') '
1073 ||' and paaf.primary_flag= '||'''Y'''
1074 ||' and paaf.employment_category = '
1075 -- ||' IN ('||'''FT'''||','||'''FR'''||','||'''PT'''||','||'''PR'''||','||'''AT'''||') '
1076 ||'nvl(hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Full_Time'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
1077 || ',nvl(hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Part_Time'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
1078 || ', hruserdt.get_table_value(paaf.business_group_id,'||''''||'EHECS_ASG_CATG_TAB'||''''||','||''''||'Apprentice_Trainee'||''''||',paaf.EMPLOYMENT_CATEGORY,'||''''||g_qtr_start_date||''''||')'
1079 || ' ))'
1080
1081 ||' and ppf.person_id between '|| stperson || ' and ' || endperson
1082 ||g_where_clause1
1083 ||' and ppa.payroll_action_id = paa.payroll_action_id '
1084 ||' and paa.assignment_id=paaf.assignment_id'
1085 ||' and (paa.source_action_id is not null or ppa.action_type in ('||'''I'''||','||'''V'''||','||'''B'''||'))'
1086 ||' and ppa.effective_date <='||''''||g_reference_end_date||''''
1087 ||' and ppa.effective_date >='||''''||g_reference_start_date||''''
1088 ||' and paa.action_status = '||'''C'''
1089 ||' and ppa.action_type in ('||'''R'''||','||'''Q'''||','||'''I'''||','
1090 ||'''V'''||','||'''B'''||')'
1091 ||' and papf.payroll_id = paaf.payroll_id '
1092 ||' and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id '
1093 ||' and scl.segment4 = to_char('||g_employer_id||') '
1094 ||g_where_clause
1095 ||g_where_clause_asg_set
1096 ||' Order by paaf.assignment_id'
1097 ||' ))'
1098 ||' where rownum<='||l_total_sample
1099 ||' order by serial';
1100
1101
1102 hr_utility.set_location('After building the dynamic query.',520);
1103
1104
1105
1106 /*6978389 */
1107 Fnd_file.put_line(FND_FILE.LOG,'Dynamic Query:'||l_select_str );
1108
1109
1110 OPEN csr_get_asg FOR l_select_str; -- ref cursor
1111 LOOP
1112 hr_utility.set_location(' Inside ass action code, inside loop for ref cursor',530);
1113 FETCH csr_get_asg INTO l_assg_id;
1114 EXIT WHEN csr_get_asg%NOTFOUND;
1115 SELECT pay_assignment_actions_s.nextval
1116 INTO lockingactid
1117 FROM dual;
1118
1119 hr_utility.set_location('assignment_action_code, the assignment id finally picked up: '||l_assg_id, 540);
1120
1121 hr_utility.set_location(' Before hr_nonrun_asact.insact call',550);
1122 hr_nonrun_asact.insact(lockingactid => lockingactid
1123 ,assignid => l_assg_id
1124 ,pactid => pactid
1125 ,chunk => chunk
1126 ,greid => NULL);
1127 hr_utility.set_location(' After hr_nonrun_asact.insact call',560);
1128
1129 END LOOP;-- ref cursor
1130
1131 END assignment_action_code;
1132 -----------------------------------------------------------------------
1133 -- ARCHIVE_INIT
1134 -----------------------------------------------------------------------
1135
1136 PROCEDURE archive_init(p_payroll_action_id IN NUMBER)
1137 IS
1138 l_start_date VARCHAR2(50);
1139 l_end_date VARCHAR2(50);
1140
1141
1142 CURSOR csr_archive_effective_date(pactid NUMBER) IS
1143 SELECT effective_date
1144 FROM pay_payroll_actions
1145 WHERE payroll_action_id = pactid;
1146
1147 BEGIN
1148
1149 hr_utility.set_location('Entering: PAY_IE_nes_REPORT_PKG.archive_init: ',600);
1150
1151 OPEN csr_archive_effective_date(p_payroll_action_id);
1152 FETCH csr_archive_effective_date
1153 INTO g_archive_effective_date;
1154 CLOSE csr_archive_effective_date;
1155
1156 hr_utility.set_location('Before calling get_all_parameters ',610);
1157
1158 get_all_parameters(p_payroll_action_id
1159 ,g_rep_group
1160 ,g_payroll_id
1161 ,g_year
1162 ,g_quarter
1163 ,g_month
1164 ,g_sample_fraction
1165 ,g_business_group_id
1166 ,g_assignment_set_id
1167 ,g_occupational_category
1168 ,g_employer_id
1169 ,g_report_type
1170 ,g_declare_date
1171 ,g_change_indicator
1172 ,g_send_emp
1173 ,g_comments);
1174
1175 hr_utility.set_location('After calling get_all_parameters ',620);
1176
1177 setup_balance_table;
1178
1179 hr_utility.set_location('After calling setup_balance_table ',630);
1180
1181 hr_utility.set_location(' Leaving PAY_IE_NESS_REPORT_PKG.archive_init', 640);
1182
1183 EXCEPTION
1184 WHEN Others THEN
1185 hr_utility.set_location(' Leaving PAY_IE_nes_REPORT_PKG.archive_init with errors', 650);
1186 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1211);
1187
1188 END archive_init;
1189 -----------------------------------------------------------------------
1190 -- ARCHIVE_DATA
1191 -----------------------------------------------------------------------
1192 PROCEDURE archive_data(p_assactid in number,
1193 p_effective_date in date)
1194 IS
1195
1196 CURSOR cur_get_pactid(p_cess_aact pay_assignment_actions.assignment_action_id%TYPE) IS
1197 SELECT distinct paa.payroll_action_id,paa.assignment_id,paaf.person_id,papf.employee_number
1198 FROM pay_assignment_actions paa
1199 ,per_all_assignments_f paaf
1200 ,per_all_people_f papf -- 9795571
1201 WHERE paa.assignment_action_id = p_cess_aact
1202 and paa.assignment_id=paaf.assignment_id
1203 and papf.person_id=paaf.person_id; -- 9795571
1204
1205
1206 CURSOR cur_assignment_action(c_person_id number,
1207 c_till_date date)is
1208 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
1209 paa.assignment_action_id),16))
1210 FROM pay_assignment_actions paa
1211 ,pay_payroll_actions ppa
1212 ,per_all_assignments_f paaf
1213 ,pay_all_payrolls_f papf
1214 ,hr_soft_coding_keyflex scl
1215 WHERE paaf.person_id=c_person_id
1216 AND paa.assignment_id=paaf.assignment_id
1217 AND paa.payroll_action_id=ppa.payroll_action_id
1218 AND ppa.action_type in ('Q','B','R','I','V')
1219 AND paa.action_status ='C'
1220 AND paa.source_action_id is not null
1221 AND ppa.effective_date<= c_till_date
1222 AND papf.payroll_id=paaf.payroll_id
1223 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1224 AND scl.segment4 = to_char(g_employer_id);
1225
1226 CURSOR cur_assignment_action_ytd(c_person_id number)is
1227 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
1228 paa.assignment_action_id),16))
1229 FROM pay_assignment_actions paa
1230 ,pay_payroll_actions ppa
1231 ,per_all_assignments_f paaf
1232 ,pay_all_payrolls_f papf
1233 ,hr_soft_coding_keyflex scl
1234 WHERE paaf.person_id=c_person_id
1235 AND paa.assignment_id=paaf.assignment_id
1236 AND paa.payroll_action_id=ppa.payroll_action_id
1237 AND ppa.action_type in ('Q','B','R','I','V')
1238 AND paa.action_status ='C'
1239 AND paa.source_action_id is not null
1240 AND papf.payroll_id=paaf.payroll_id
1241 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1242 AND scl.segment4 = to_char(g_employer_id)
1243 AND to_char(ppa.effective_date,'yyyy')=to_char(g_year); -- 9734490
1244
1245
1246 CURSOR csr_hours_per_day(c_org_id hr_organization_information.organization_id%type
1247 ,c_bg_id hr_organization_units.business_group_id%type
1248 ) IS
1249 select hoi.org_information18 hrs_per_day
1250 from hr_organization_units hou
1251 ,hr_organization_information hoi
1252 where hoi.org_information_context='IE_EHECS'
1253 and hoi.organization_id=c_org_id
1254 and hoi.organization_id=hou.organization_id
1255 and hou.business_group_id= c_bg_id;
1256
1257 CURSOR cur_valid_asg(c_assignment_id NUMBER, c_person_id NUMBER)
1258 IS
1259 SELECT distinct paaf.assignment_id, paaf.person_id, paaf.payroll_id,
1260 --decode(paaf.EMPLOYMENT_CATEGORY,'FT','F','FR','F','PR','P','PT','P',paaf.EMPLOYMENT_CATEGORY) EMP_CATG,
1261 /* 6856486 */
1262 decode(paaf.EMPLOYMENT_CATEGORY
1263 ,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Full_Time',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'F'
1264 ,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Part_Time',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'P'
1265 ,hruserdt.get_table_value(paaf.business_group_id,'EHECS_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'AT'
1266 ) EMP_CATG,
1267
1268 NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Managers',paaf.EMPLOYEE_CATEGORY,g_reference_start_date),
1269 NVL(hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Clerical Workers',paaf.EMPLOYEE_CATEGORY,g_reference_start_date),
1270 hruserdt.get_table_value(paaf.business_group_id,'EHECS_CATG_TAB','Production Workers',paaf.EMPLOYEE_CATEGORY,g_reference_start_date)
1271 )
1272 ) EHECS_CATG
1273 , paaf.effective_start_date
1274 ,normal_hours normal_hours /*6856473*/
1275 ,frequency frequency /*6856473*/
1276 ,hourly_salaried_code hourly_or_salaried /*6856473*/
1277 ,substr(paaf.EMPLOYEE_CATEGORY,-2,length(paaf.EMPLOYEE_CATEGORY)) status_code
1278 ,paaf.assignment_number assignment_number
1279 ,decode(paaf.EMPLOYMENT_CATEGORY
1280 ,hruserdt.get_table_value(paaf.business_group_id,'NES_ASG_CATG_TAB','Indefinite',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'INDEF'
1281 ,hruserdt.get_table_value(paaf.business_group_id,'NES_ASG_CATG_TAB','Definite',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'DEF'
1282 ,hruserdt.get_table_value(paaf.business_group_id,'NES_ASG_CATG_TAB','Apprentice_Trainee',paaf.EMPLOYMENT_CATEGORY,g_reference_start_date),'AT'
1283 ) NES_EMPLOYEMENT_CATG /* 10081528 */
1284 FROM
1285 per_all_assignments_f paaf
1286 WHERE paaf.assignment_id = c_assignment_id
1287 and paaf.person_id = c_person_id
1288 /* 9795685 */
1289 --
1290 /*and paaf.effective_start_date <= g_reference_start_date
1291 and paaf.effective_end_date >= g_reference_end_date */
1292 and paaf.effective_start_date <= g_reference_end_date
1293 and paaf.effective_end_date >= g_reference_start_date
1294 --
1295 and assignment_status_type_id in (SELECT assignment_status_type_id
1296 FROM per_assignment_status_types
1297 WHERE per_system_status = 'ACTIVE_ASSIGN'
1298 AND active_flag = 'Y')/*6856473 to filter the terminated assingment*/
1299 ORDER BY paaf.effective_start_date desc;
1300
1301
1302 CURSOR csr_employee_details(cp_person_id in number,
1303 cp_effective_date in date) is
1304 select papf.full_name full_name,
1305 papf.national_identifier PPS,
1306 pa.address_line1 address_line1,
1307 pa.address_line2 address_line2,
1308 pa.address_line3 address_line3,
1309 null address_line4,
1310 null address_line5,
1311 pa.town_or_city city ,
1312 flv.meaning County,
1313 pa.country Country
1314 from per_addresses pa,
1315 per_all_people_f papf,
1316 fnd_lookup_values flv
1317 where papf.person_id = cp_person_id
1318 and papf.person_id=pa.person_id(+)
1319 and pa.primary_flag (+)= 'Y' --is address primary ?
1320 and pa.date_from(+) <= cp_effective_date
1321 and nvl(pa.date_to, cp_effective_date) >= cp_effective_date
1322 and flv.lookup_type(+) = 'IE_COUNTY'
1323 and flv.language(+) = 'US'
1324 and flv.lookup_code(+) = pa.REGION_1;
1325
1326 CURSOR csr_input_value_id(p_element_name CHAR,
1327 p_value_name CHAR) IS
1328 SELECT piv.input_value_id
1329 FROM pay_input_values_f piv,
1330 pay_element_types_f pet
1331 WHERE piv.element_type_id = pet.element_type_id
1332 AND pet.legislation_code = 'IE'
1333 AND pet.element_name = p_element_name
1334 AND piv.name = p_value_name;
1335
1336 CURSOR csr_ness_action_details(c_assignment_action_id NUMBER) is
1337 SELECT ppa.payroll_action_id pact_id,
1338 ppa.date_earned date_earned
1339 FROM pay_payroll_actions ppa,
1340 pay_assignment_actions paa
1341 WHERE paa.assignment_action_id=c_assignment_action_id
1342 AND paa.payroll_action_id=ppa.payroll_action_id;
1343
1344 cursor csr_get_periods(c_start_date in date,
1345 c_end_date in date,
1346 c_payroll_action_id in number) is
1347 select ptp.period_type period_type,
1348 count(ptp.period_num) pay_periods
1349 from
1350 pay_payroll_actions ppa
1351 , per_time_periods ptp
1352 , per_time_period_types tptype
1353 WHERE ppa.payroll_action_id=c_payroll_action_id
1354 AND ptp.payroll_id=ppa.payroll_id
1355 AND ptp.period_type = tptype.period_type
1356 AND ptp.end_date between
1357 c_start_date and c_end_date
1358 Group by ptp.period_type ;
1359
1360 /* 9768428 start */
1361 cursor csr_ppsn_override(p_asg_id number)
1362 is
1363 select aei_information1 PPSN_OVERRIDE
1364 from per_assignment_extra_info
1365 where assignment_id = p_asg_id
1366 and aei_information_category = 'IE_ASG_OVERRIDE';
1367
1368 CURSOR cur_assignment_action_ppsn(c_person_id number,
1369 c_till_date date,
1370 c_ppsn_override per_assignment_extra_info.aei_information1%type)is
1371 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
1372 paa.assignment_action_id),16))
1373 FROM pay_assignment_actions paa
1374 ,pay_payroll_actions ppa
1375 ,per_all_assignments_f paaf
1376 ,pay_all_payrolls_f papf
1377 ,hr_soft_coding_keyflex scl
1378 WHERE paaf.person_id=c_person_id
1379 AND paa.assignment_id in (select paaf.assignment_id
1380 from per_all_assignments_f paaf, per_assignment_extra_info paei
1381 where paaf.person_id = c_person_id
1382 and paaf.assignment_id=paei.assignment_id
1383 and paei.information_type = 'IE_ASG_OVERRIDE'
1384 and paei.aei_information1 = c_ppsn_override --'314678745T'
1385 )
1386 AND paa.payroll_action_id=ppa.payroll_action_id
1387 AND ppa.action_type in ('Q','B','R','I','V')
1388 AND paa.action_status ='C'
1389 AND paa.source_action_id is not null
1390 AND ppa.effective_date<= c_till_date
1391 AND papf.payroll_id=paaf.payroll_id
1392 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1393 AND scl.segment4 = to_char(g_employer_id);
1394
1395 CURSOR cur_assignment_action_ytd_ppsn(c_person_id number,
1396 c_ppsn_override per_assignment_extra_info.aei_information1%type)is
1397 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
1398 paa.assignment_action_id),16))
1399 FROM pay_assignment_actions paa
1400 ,pay_payroll_actions ppa
1401 ,per_all_assignments_f paaf
1402 ,pay_all_payrolls_f papf
1403 ,hr_soft_coding_keyflex scl
1404 WHERE paaf.person_id=c_person_id
1405 AND paa.assignment_id in (select paaf.assignment_id
1406 from per_all_assignments_f paaf, per_assignment_extra_info paei
1407 where paaf.person_id = c_person_id
1408 and paaf.assignment_id=paei.assignment_id
1409 and paei.information_type = 'IE_ASG_OVERRIDE'
1410 and paei.aei_information1 = c_ppsn_override --'314678745T'
1411 )
1412 AND paa.payroll_action_id=ppa.payroll_action_id
1413 AND ppa.action_type in ('Q','B','R','I','V')
1414 AND paa.action_status ='C'
1415 AND paa.source_action_id is not null
1416 AND papf.payroll_id=paaf.payroll_id
1417 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1418 AND scl.segment4 = to_char(g_employer_id)
1419 AND to_char(ppa.effective_date,'yyyy')=to_char(g_year);
1420
1421
1422 l_ppsn_override per_assignment_extra_info.aei_information1%type;
1423 /* 9768428 end */
1424
1425 l_ness_action_details csr_ness_action_details%ROWTYPE;
1426 l_input_value_id NUMBER;
1427 l_input_value_id1 NUMBER;
1428 l_employee_details csr_employee_details%ROWTYPE;
1429
1430 l_address_line1 hr_locations_all.address_line_1%type;
1431 l_address_line2 hr_locations_all.address_line_2%type;
1432 l_address_line3 hr_locations_all.address_line_3%type;
1433 l_address_line4 hr_locations_all.address_line_3%type;
1434 l_address_line5 hr_locations_all.address_line_3%type;
1435
1436 l_id_reference per_all_assignments_f.assignment_number%TYPE:=NULL;
1437
1438 l_valid_asg_rec cur_valid_asg%ROWTYPE;
1439 l_hours_per_day varchar2(10);
1440 l_org_id hr_organization_units.organization_id%type;
1441 l_normal_hours per_all_assignments_f.normal_hours%type;
1442 l_frequency per_all_assignments_f.frequency%type;
1443
1444 Type tab_address is table of per_addresses.ADDRESS_LINE1%type index by binary_integer;
1445 pl_address tab_address;
1446 pl_address_final tab_address;
1447 k NUMBER(3) := 0;
1448
1449 --------------------------- Variables which will hold the Balance Values.
1450 l_regwg_bal_val Number := 0;
1451 l_irrb_bal_val Number := 0;
1452 l_ovrt_bal_val Number := 0;
1453 l_regwg_bal_val_ptd Number := 0;
1454
1455 l_ovrt_bal_val_ptd Number := 0;
1456
1457 l_reg_shft_allnce_bal_val NUMBER :=0;
1458 l_ireg_shft_allnce_bal_val NUMBER :=0;
1459 l_tot_shft_allnce_bal_val NUMBER :=0;
1460 l_holi_bal_val NUMBER :=0;
1461 l_tot_comm_bal_val NUMBER:=0;
1462
1463 l_othr_bal_val Number := 0;
1464 l_chrs_bal_val number := 0;
1465 l_mat_bal_val Number := 0;
1466 l_sic_bal_val Number := 0;
1467 l_otl_bal_val Number := 0;
1468 l_vhi_bal_val Number := 0;
1469 l_hse_bal_val Number := 0;
1470 l_otben_bal_val Number := 0;
1471 l_ot_sub_bal_val Number := 0;
1472 l_bik_veh_bal_val Number := 0;
1473 l_pen_bal_val_tot Number := 0;
1474 l_lap_bal_val_tot Number := 0;
1475 l_app_wg_bal_val_tot Number := 0;
1476 l_ssec_bal_val_tot Number := 0;
1477 l_nmw_count Number := 0;
1478 l_total_weeks Number:=0;
1479
1480 /* 9768428 start */
1481 l_regwg_bal_val_ppsn Number := 0;
1482 l_irrb_bal_val_ppsn Number := 0;
1483 l_ovrt_bal_val_ppsn Number := 0;
1484 l_regwg_bal_val_ptd_ppsn Number := 0;
1485
1486 l_ovrt_bal_val_ptd_ppsn Number := 0;
1487
1488 l_reg_shft_allnce_bal_val_ppsn NUMBER :=0;
1489 l_ireg_shft_val_ppsn NUMBER :=0;
1490 l_holi_bal_val_ppsn NUMBER :=0;
1491 l_tot_comm_bal_val_ppsn NUMBER:=0;
1492
1493 l_othr_bal_val_ppsn Number := 0;
1494 l_chrs_bal_val_ppsn number := 0;
1495 l_mat_bal_val_ppsn Number := 0;
1496 l_sic_bal_val_ppsn Number := 0;
1497 l_otl_bal_val_ppsn Number := 0;
1498 l_vhi_bal_val_ppsn Number := 0;
1499 l_hse_bal_val_ppsn Number := 0;
1500 l_otben_bal_val_ppsn Number := 0;
1501 l_ot_sub_bal_val_ppsn Number := 0;
1502 l_bik_veh_bal_val_ppsn Number := 0;
1503 l_total_weeks_ppsn Number:=0;
1504
1505 /* 9768428 end */
1506
1507 l_payroll_action_id number;
1508 l_assignment_id number;
1509 l_person_id number;
1510 l_employee_number per_all_people_f.employee_number%TYPE;
1511 l_reference_date date;
1512 l_ness_assignment_action number;
1513 l_ness_assignment_action_ytd number;
1514 l_action_info_id NUMBER;
1515 l_ovn NUMBER;
1516
1517 l_ann_earning Number;
1518 l_ann_bik NUMBER;
1519 l_other_absence NUMBER;
1520 l_gross_earning NUMBER:=0; /* 9795571 */
1521 l_prsi_class VARCHAR2(10);
1522 l_prsi_subclass VARCHAR2(10);
1523 l_period_type per_time_periods.period_type%type;
1524 l_pay_periods number;
1525 l_freq_pay NUMBER;
1526 l_employement_type NUMBER:=1; /* 9795685 */
1527 l_ref_period_pay NUMBER;
1528 l_ref_period_hours NUMBER;
1529
1530 l_str_Common VARCHAR2(2000);
1531 l_errflag VARCHAR2(1) := 'N';
1532 --------------------------- Variables which will hold the Balance Values.
1533
1534 BEGIN
1535
1536 hr_utility.set_location(' Entering PAY_IE_nes_REPORT_PKG.ARCHIVE_CODE: ',700);
1537
1538 hr_utility.set_location('p_assignment_action_id '||TO_CHAR(p_assactid),700);
1539 OPEN cur_get_pactid(p_assactid);
1540 FETCH cur_get_pactid INTO l_payroll_action_id,l_assignment_id,l_person_id,l_employee_number; -- 9795571
1541 CLOSE cur_get_pactid;
1542
1543 /* 9768428 start */
1544 open csr_ppsn_override(l_assignment_id);
1545 fetch csr_ppsn_override into l_ppsn_override;
1546 close csr_ppsn_override;
1547 hr_utility.set_location('PPSN Override value = ' || l_ppsn_override,20);
1548 /* 9768428 end */
1549
1550 hr_utility.set_location('l_payroll_action_id '||TO_CHAR(l_payroll_action_id),700);
1551 hr_utility.set_location('l_payroll_action_id '||TO_CHAR(l_assignment_id),700);
1552 l_reference_date:=add_months(to_date('01'||'/'||g_month||'/'||g_year,'dd/mm/yyyy'),1)-1;
1553
1554 hr_utility.set_location('l_reference_date '|| l_reference_date,700);
1555
1556
1557 /*6856473*/
1558 hr_utility.set_location(' before calling get parameters ', 800);
1559 hr_utility.set_location('Before get_all_parameters',501);
1560 get_all_parameters(l_payroll_action_id
1561 ,g_rep_group
1562 ,g_payroll_id
1563 ,g_year
1564 ,g_quarter
1565 ,g_month
1566 ,g_sample_fraction
1567 ,g_business_group_id
1568 ,g_assignment_set_id
1569 ,g_occupational_category
1570 ,g_employer_id
1571 ,g_report_type
1572 ,g_declare_date
1573 ,g_change_indicator
1574 ,g_send_emp
1575 ,g_comments);
1576 hr_utility.set_location('after get_all_parameters',502);
1577 IF l_ppsn_override is NOT NULL THEN /* 9768428 */
1578 OPEN cur_assignment_action_ppsn(l_person_id,l_reference_date,l_ppsn_override);
1579 FETCH cur_assignment_action_ppsn INTO l_ness_assignment_action;
1580 CLOSE cur_assignment_action_ppsn;
1581
1582 OPEN cur_assignment_action_ytd_ppsn(l_person_id,l_ppsn_override);
1583 FETCH cur_assignment_action_ytd_ppsn INTO l_ness_assignment_action_ytd;
1584 CLOSE cur_assignment_action_ytd_ppsn;
1585
1586 ELSE
1587 OPEN cur_assignment_action(l_person_id,l_reference_date);
1588 FETCH cur_assignment_action INTO l_ness_assignment_action;
1589 CLOSE cur_assignment_action;
1590
1591 OPEN cur_assignment_action_ytd(l_person_id);
1592 FETCH cur_assignment_action_ytd INTO l_ness_assignment_action_ytd;
1593 CLOSE cur_assignment_action_ytd;
1594 END IF;
1595 hr_utility.set_location(' l_ness_assignment_action '|| l_ness_assignment_action, 801);
1596 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 801);
1597
1598 OPEN csr_hours_per_day(g_employer_id,g_business_group_id);
1599 FETCH csr_hours_per_day INTO l_hours_per_day;
1600 CLOSE csr_hours_per_day;
1601
1602 hr_utility.set_location(' AFTER cursor csr_hours_per_day '||l_hours_per_day, 801);
1603
1604 hr_utility.set_location(' Before cursor cur_valid_asg ', 801);
1605 OPEN cur_valid_asg(l_assignment_id, l_person_id);
1606 FETCH cur_valid_asg INTO l_valid_asg_rec;
1607 CLOSE cur_valid_asg;
1608 hr_utility.set_location(' after cursor cur_valid_asg ', 801);
1609
1610
1611 IF l_ness_assignment_action IS NOT NULL
1612 THEN
1613 hr_utility.set_location(' Entered if condition ', 801);
1614
1615 hr_utility.set_location(' Before cursor csr_input_value_id', 890);
1616
1617 OPEN csr_input_value_id('IE PRSI Detail','Contribution Class');
1618 FETCH csr_input_value_id INTO l_input_value_id;
1619 CLOSE csr_input_value_id;
1620
1621 OPEN csr_input_value_id('IE PRSI Detail','Subclass');
1622 FETCH csr_input_value_id INTO l_input_value_id1;
1623 CLOSE csr_input_value_id;
1624
1625 hr_utility.set_location(' after cursor csr_input_value_id', 890);
1626 hr_utility.set_location(' l_input_value_id'|| l_input_value_id , 890);
1627 hr_utility.set_location(' l_input_value_id1'|| l_input_value_id1 , 890);
1628
1629 hr_utility.set_location(' Before cursor csr_ness_action_details', 890);
1630 OPEN csr_ness_action_details(l_ness_assignment_action);
1631 FETCH csr_ness_action_details INTO l_ness_action_details;
1632 CLOSE csr_ness_action_details;
1633 hr_utility.set_location(' l_ness_action_details.date_earned'|| l_ness_action_details.date_earned, 890);
1634 hr_utility.set_location(' after cursor csr_date_earned', 890);
1635
1636
1637 l_prsi_class:= pay_ie_archive_detail_pkg.get_tax_details (
1638 p_run_assignment_action_id => l_ness_assignment_action
1639 ,p_input_value_id => l_input_value_id
1640 ,p_date_earned => to_char(l_ness_action_details.date_earned, 'rrrr/mm/dd'));
1641
1642 l_prsi_subclass:= pay_ie_archive_detail_pkg.get_tax_details (
1643 p_run_assignment_action_id => l_ness_assignment_action
1644 ,p_input_value_id => l_input_value_id1
1645 ,p_date_earned => to_char(l_ness_action_details.date_earned, 'rrrr/mm/dd'));
1646
1647 hr_utility.set_location(' l_prsi_class' || l_prsi_class, 890);
1648 hr_utility.set_location(' l_prsi_subclass' || l_prsi_subclass, 890);
1649 l_prsi_class := l_prsi_class||l_prsi_subclass;
1650 hr_utility.set_location(' l_prsi_class' || l_prsi_class, 890);
1651
1652 OPEN csr_get_periods(g_reference_start_date,g_reference_end_date,l_ness_action_details.pact_id);
1653 FETCH csr_get_periods INTO l_period_type,l_pay_periods;
1654 CLOSE csr_get_periods;
1655
1656 hr_utility.set_location(' l_period_type' || l_period_type, 890);
1657 hr_utility.set_location(' l_pay_periods' || l_pay_periods, 890);
1658
1659 IF l_period_type='Week' THEN
1660 l_freq_pay:=1;
1661 IF l_pay_periods<=4 THEN
1662 l_ref_period_pay:=1;
1663 ELSIF l_pay_periods=5 THEN
1664 l_ref_period_pay:=3;
1665 END IF;
1666
1667 ELSIF l_period_type='Bi-Week' THEN
1668 l_freq_pay:=2;
1669 IF l_pay_periods<=2 THEN
1670 l_ref_period_pay:=1;
1671 ELSIF l_pay_periods=3 THEN
1672 l_ref_period_pay:=4;
1673 END IF;
1674 ELSIF l_period_type='Lunar Month' THEN
1675 l_freq_pay:=3;
1676 l_ref_period_pay:=1;
1677 ELSIF l_period_type='Calendar Month' THEN
1678 l_freq_pay:=4;
1679 l_ref_period_pay:=2;
1680 ELSIF l_period_type='Lunar Month' and l_pay_periods=5 THEN
1681 l_freq_pay:=5;
1682 l_ref_period_pay:=3;
1683 END IF;
1684
1685 /*
1686 IF l_ref_period_pay NOT in(1,2,3,4) THEN
1687 l_ref_period_pay:=5;
1688 END IF;
1689 */
1690 FOR bal_index IN 1..g_def_bal_id.COUNT
1691 LOOP
1692 hr_utility.set_location(' Enteredfor loop '||bal_index, 801);
1693 hr_utility.set_location(' before gloabnl tableand count '|| to_char(g_def_bal_id.COUNT) , 801);
1694 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '|| to_char(g_def_bal_id(bal_index).balance_name), 801);
1695 hr_utility.set_location(' g_def_bal_id(bal_index).defined_balance_id '||to_char( g_def_bal_id(bal_index).defined_balance_id), 801);
1696 hr_utility.set_location(' before gloabnl table ', 801);
1697
1698 IF g_def_bal_id(bal_index).balance_name = 'Regular Earnings' and l_ppsn_override IS NULL THEN
1699
1700 hr_utility.set_location(' Inside balance Loop ', 850);
1701 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1702 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1703
1704 l_regwg_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1705 l_ness_assignment_action_ytd,
1706 g_employer_id,
1707 null,
1708 null,
1709 null,
1710 null,
1711 null);
1712 hr_utility.set_location(' l_regwg_bal_val '|| l_regwg_bal_val, 850);
1713 ELSIF g_def_bal_id(bal_index).balance_name = 'Irregular Earnings' and l_ppsn_override IS NULL THEN
1714
1715 hr_utility.set_location(' Inside balance Loop ', 850);
1716 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1717 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1718
1719 l_irrb_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1720 l_ness_assignment_action_ytd,
1721 g_employer_id,
1722 null,
1723 null,
1724 null,
1725 null,
1726 null);
1727 hr_utility.set_location(' l_irrb_bal_val '|| l_irrb_bal_val, 850);
1728 ELSIF g_def_bal_id(bal_index).balance_name = 'Overtime Payments' and l_ppsn_override IS NULL THEN
1729
1730 hr_utility.set_location(' Inside balance Loop ', 850);
1731 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1732 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1733
1734 l_ovrt_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1735 l_ness_assignment_action_ytd,
1736 g_employer_id,
1737 null,
1738 null,
1739 null,
1740 null,
1741 null);
1742 hr_utility.set_location(' l_ovrt_bal_val '|| l_ovrt_bal_val, 850);
1743 ELSIF g_def_bal_id(bal_index).balance_name = 'Paid Overtime Hours' and l_ppsn_override IS NULL THEN
1744
1745 hr_utility.set_location(' Inside balance Loop ', 850);
1746 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1747 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
1748
1749 l_othr_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1750 l_ness_assignment_action,
1751 g_employer_id,
1752 null,
1753 null,
1754 null,
1755 null,
1756 null);
1757 hr_utility.set_location(' l_othr_bal_val '|| l_othr_bal_val, 850);
1758 ELSIF g_def_bal_id(bal_index).balance_name = 'Normal Working Hours' THEN
1759
1760 hr_utility.set_location(' Inside balance Loop ', 850);
1761 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1762 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
1763
1764
1765
1766
1767
1768 IF(l_valid_asg_rec.hourly_or_salaried='S')
1769 THEN
1770
1771 IF(l_valid_asg_rec.frequency='D')
1772 THEN
1773 IF l_period_type='Calendar Month' THEN
1774 l_ref_period_hours:=1;
1775 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0)*((g_reference_end_date - g_reference_start_date)+1);
1776
1777 hr_utility.set_location(' ((g_reference_end_date - g_reference_start_date)+1) '|| ((g_reference_end_date - g_reference_start_date)+1), 850);
1778 ELSIF l_period_type='Week' THEN
1779 l_ref_period_hours:=1;
1780 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0)*7;
1781 ELSIF l_period_type='Bi-Week' THEN
1782 l_ref_period_hours:=1;
1783 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0)*14;
1784 ELSIF l_period_type='Lunar Month' THEN
1785 l_ref_period_hours:=1;
1786 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0)*7*l_pay_periods;
1787 END IF;
1788
1789 ELSIF(l_valid_asg_rec.frequency='M')
1790 THEN
1791 l_ref_period_hours:=2;
1792 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0);
1793
1794 ELSIF(l_valid_asg_rec.frequency='W')
1795 THEN
1796 IF l_period_type='Calendar Month' THEN
1797 l_ref_period_hours:=1;
1798 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0)*4;
1799 ELSIF l_period_type='Week' THEN
1800 l_ref_period_hours:=1;
1801 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0)*1;
1802 ELSIF l_period_type='Bi-Week' THEN
1803 l_ref_period_hours:=1;
1804 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0)*2;
1805 ELSIF l_period_type='Lunar Month' THEN
1806 IF l_pay_periods=4 THEN
1807 l_ref_period_hours:=1;
1808 ELSIF l_pay_periods=5 THEN
1809 l_ref_period_hours:=3;
1810 END IF;
1811 l_chrs_bal_val:=nvl(l_valid_asg_rec.normal_hours,0)*l_pay_periods;
1812 END IF;
1813
1814 ELSIF(l_valid_asg_rec.frequency='Y')
1815 THEN
1816 l_chrs_bal_val:=(nvl(l_valid_asg_rec.normal_hours,0))/12;
1817 END IF;
1818 /* ELSE
1819 l_errflag := 'Y';
1820 Fnd_file.put_line(FND_FILE.LOG,'Ensure that Normal hours value is not null at the assignment level of person'||p_person_id );
1821 Raise l_nes_exception;
1822 END IF;
1823 */
1824 ELSE
1825 l_chrs_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1826 l_ness_assignment_action,
1827 g_employer_id,
1828 null,
1829 null,
1830 null,
1831 null,
1832 null);
1833 l_ref_period_hours:=l_ref_period_pay;
1834 END IF;
1835 hr_utility.set_location(' l_chrs_bal_val '|| l_chrs_bal_val, 850);
1836 --Bug # 6774024
1837 ELSIF (g_def_bal_id(bal_index).balance_name = 'Paid Maternity Hours') and l_ppsn_override IS NULL THEN
1838
1839 hr_utility.set_location(' Inside balance Loop ', 850);
1840 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1841 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1842
1843 l_mat_bal_val := nvl(l_mat_bal_val,0)
1844 +
1845 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1846 l_ness_assignment_action_ytd,
1847 g_employer_id,
1848 null,
1849 null,
1850 null,
1851 null,
1852 null),0);
1853
1854 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1855
1856 hr_utility.set_location(' l_mat_bal_val '|| l_mat_bal_val, 850);
1857 ELSIF (g_def_bal_id(bal_index).balance_name = 'Paid Sick Leave Hours' ) and l_ppsn_override IS NULL THEN
1858
1859 hr_utility.set_location(' Inside balance Loop ', 850);
1860 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1861 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1862
1863 l_sic_bal_val :=
1864 nvl(l_sic_bal_val,0)
1865 +
1866 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1867 l_ness_assignment_action_ytd,
1868 g_employer_id,
1869 null,
1870 null,
1871 null,
1872 null,
1873 null),0);
1874
1875 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1876 hr_utility.set_location(' l_sic_bal_val '|| l_sic_bal_val, 850);
1877 ELSIF (g_def_bal_id(bal_index).balance_name = 'Paid Other Leave Hours' ) and l_ppsn_override IS NULL THEN
1878
1879 hr_utility.set_location(' Inside balance Loop ', 850);
1880 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1881 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1882
1883 l_otl_bal_val :=
1884 NVl(l_otl_bal_val,0)
1885 +
1886 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1887 l_ness_assignment_action_ytd,
1888 g_employer_id,
1889 null,
1890 null,
1891 null,
1892 null,
1893 null),0);
1894
1895 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1896 hr_utility.set_location(' l_otl_bal_val '|| l_otl_bal_val, 850);
1897
1898 /*6856473 added checks for balances Paid Maternity Days, Paid Sick Leave Days and Paid Other Leave Days*/
1899
1900 ELSIF (g_def_bal_id(bal_index).balance_name = 'Paid Maternity Days') and l_ppsn_override IS NULL THEN
1901
1902 hr_utility.set_location(' Inside balance Loop ', 850);
1903 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1904 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1905
1906 l_mat_bal_val :=
1907 nvl(l_mat_bal_val,0)
1908 +
1909 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1910 l_ness_assignment_action_ytd,
1911 g_employer_id,
1912 null,
1913 null,
1914 null,
1915 null,
1916 null),0)*l_hours_per_day;
1917
1918 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1919
1920 hr_utility.set_location(' l_mat_bal_val '|| l_mat_bal_val, 850);
1921 ELSIF ( g_def_bal_id(bal_index).balance_name = 'Paid Sick Leave Days') and l_ppsn_override IS NULL THEN
1922
1923 hr_utility.set_location(' Inside balance Loop ', 850);
1924 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1925 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1926
1927 l_sic_bal_val :=
1928 nvl(l_sic_bal_val,0)
1929 +
1930 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1931 l_ness_assignment_action_ytd,
1932 g_employer_id,
1933 null,
1934 null,
1935 null,
1936 null,
1937 null),0)*l_hours_per_day;
1938 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1939 hr_utility.set_location(' l_sic_bal_val '|| l_sic_bal_val, 850);
1940 ELSIF ( g_def_bal_id(bal_index).balance_name = 'Paid Other Leave Days') and l_ppsn_override IS NULL THEN
1941
1942 hr_utility.set_location(' Inside balance Loop ', 850);
1943 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1944 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1945
1946 l_otl_bal_val :=
1947 nvl(l_otl_bal_val,0)
1948 +
1949 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1950 l_ness_assignment_action_ytd,
1951 g_employer_id,
1952 null,
1953 null,
1954 null,
1955 null,
1956 null),0)*l_hours_per_day
1957 ;
1958 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1959
1960 hr_utility.set_location(' l_otl_bal_val '|| l_otl_bal_val, 850);
1961
1962
1963 ELSIF g_def_bal_id(bal_index).balance_name = 'Voluntary Sickness Insurance' and l_ppsn_override IS NULL THEN
1964
1965 hr_utility.set_location(' Inside balance Loop ', 850);
1966 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1967 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1968
1969 l_vhi_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1970 l_ness_assignment_action_ytd,
1971 g_employer_id,
1972 null,
1973 null,
1974 null,
1975 null,
1976 null);
1977 hr_utility.set_location(' l_vhi_bal_val '|| l_vhi_bal_val, 850);
1978 ELSIF g_def_bal_id(bal_index).balance_name = 'Staff Housing' and l_ppsn_override IS NULL THEN
1979
1980 hr_utility.set_location(' Inside balance Loop ', 850);
1981 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1982 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1983
1984 l_hse_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
1985 l_ness_assignment_action_ytd,
1986 g_employer_id,
1987 null,
1988 null,
1989 null,
1990 null,
1991 null);
1992 hr_utility.set_location(' l_hse_bal_val '|| l_hse_bal_val, 850);
1993 ELSIF g_def_bal_id(bal_index).balance_name = 'Other Benefits' and l_ppsn_override IS NULL THEN
1994
1995 hr_utility.set_location(' Inside balance Loop ', 850);
1996 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
1997 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
1998
1999 l_otben_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2000 l_ness_assignment_action_ytd,
2001 g_employer_id,
2002 null,
2003 null,
2004 null,
2005 null,
2006 null);
2007 hr_utility.set_location(' l_otben_bal_val '|| l_otben_bal_val, 850);
2008 ELSIF g_def_bal_id(bal_index).balance_name = 'Other Subsidies' and l_ppsn_override IS NULL THEN
2009
2010 hr_utility.set_location(' Inside balance Loop ', 850);
2011 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2012 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2013
2014 l_ot_sub_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2015 l_ness_assignment_action_ytd,
2016 g_employer_id,
2017 null,
2018 null,
2019 null,
2020 null,
2021 null);
2022 hr_utility.set_location(' l_ot_sub_bal_val '|| l_ot_sub_bal_val, 850);
2023
2024
2025
2026 ELSIF g_def_bal_id(bal_index).balance_name = 'IE BIK Company Vehicle' and l_ppsn_override IS NULL THEN
2027
2028 hr_utility.set_location(' Inside balance Loop ', 850);
2029 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2030 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2031
2032 l_bik_veh_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2033 l_ness_assignment_action_ytd,
2034 g_employer_id,
2035 null,
2036 null,
2037 null,
2038 null,
2039 null);
2040
2041 hr_utility.set_location(' l_bik_veh_bal_val '|| l_bik_veh_bal_val, 850);
2042
2043
2044
2045 ELSIF g_def_bal_id(bal_index).balance_name = 'IE PRSI Insurable Weeks' and l_ppsn_override IS NULL THEN
2046
2047 hr_utility.set_location(' Inside balance Loop ', 850);
2048 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2049 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2050
2051 l_total_weeks :=
2052 nvl(l_total_weeks,0)
2053 +
2054 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2055 l_ness_assignment_action_ytd,
2056 g_employer_id,
2057 null,
2058 null,
2059 null,
2060 null,
2061 null),0);
2062 hr_utility.set_location(' l_total_weeks '|| l_total_weeks, 850);
2063 ELSIF g_def_bal_id(bal_index).balance_name = 'IE PRSI K Term Insurable Weeks' and l_ppsn_override IS NULL THEN
2064
2065 hr_utility.set_location(' Inside balance Loop ', 850);
2066 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2067 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2068
2069 l_total_weeks :=
2070 nvl(l_total_weeks,0)
2071 +
2072 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2073 l_ness_assignment_action_ytd,
2074 g_employer_id,
2075 null,
2076 null,
2077 null,
2078 null,
2079 null),0);
2080 hr_utility.set_location(' l_total_weeks '|| l_total_weeks, 850);
2081 ELSIF g_def_bal_id(bal_index).balance_name = 'IE PRSI M Term Insurable Weeks' and l_ppsn_override IS NULL THEN
2082
2083 hr_utility.set_location(' Inside balance Loop ', 850);
2084 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2085 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2086
2087 l_total_weeks :=
2088 nvl(l_total_weeks,0)
2089 +
2090 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2091 l_ness_assignment_action_ytd,
2092 g_employer_id,
2093 null,
2094 null,
2095 null,
2096 null,
2097 null),0);
2098 hr_utility.set_location(' l_total_weeks '|| l_total_weeks, 850);
2099 ELSIF g_def_bal_id(bal_index).balance_name = 'Regular EarningsMONTH' and l_ppsn_override IS NULL THEN
2100
2101 hr_utility.set_location(' Inside balance Loop ', 850);
2102 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2103 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2104
2105 l_regwg_bal_val_ptd := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2106 l_ness_assignment_action,
2107 g_employer_id,
2108 null,
2109 null,
2110 null,
2111 null,
2112 null);
2113 hr_utility.set_location(' l_regwg_bal_val_ptd '|| l_regwg_bal_val_ptd, 850);
2114
2115 ELSIF g_def_bal_id(bal_index).balance_name = 'Overtime PaymentsMONTH' and l_ppsn_override IS NULL THEN
2116
2117 hr_utility.set_location(' Inside balance Loop ', 850);
2118 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2119 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2120
2121 l_ovrt_bal_val_ptd := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2122 l_ness_assignment_action,
2123 g_employer_id,
2124 null,
2125 null,
2126 null,
2127 null,
2128 null);
2129 hr_utility.set_location(' l_ovrt_bal_val_ptd '|| l_ovrt_bal_val_ptd, 850);
2130 ELSIF g_def_bal_id(bal_index).balance_name = 'Regular Shift Allowance' and l_ppsn_override IS NULL THEN
2131
2132 hr_utility.set_location(' Inside balance Loop ', 850);
2133 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2134 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2135
2136 l_reg_shft_allnce_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2137 l_ness_assignment_action,
2138 g_employer_id,
2139 null,
2140 null,
2141 null,
2142 null,
2143 null);
2144 hr_utility.set_location(' l_reg_shft_allnce_bal_val '|| l_reg_shft_allnce_bal_val, 850);
2145 ELSIF g_def_bal_id(bal_index).balance_name = 'Irregular Shift Allowance' and l_ppsn_override IS NULL THEN
2146
2147 hr_utility.set_location(' Inside balance Loop ', 850);
2148 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2149 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2150
2151 l_ireg_shft_allnce_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2152 l_ness_assignment_action,
2153 g_employer_id,
2154 null,
2155 null,
2156 null,
2157 null,
2158 null);
2159 hr_utility.set_location(' l_ireg_shft_allnce_bal_val '|| l_ireg_shft_allnce_bal_val, 850);
2160 ELSIF g_def_bal_id(bal_index).balance_name = 'Paid Holiday Hours' and l_ppsn_override IS NULL THEN
2161
2162 hr_utility.set_location(' Inside balance Loop ', 850);
2163 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2164 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2165
2166 l_holi_bal_val :=
2167 NVL(l_holi_bal_val,0)
2168 +
2169 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2170 l_ness_assignment_action_ytd,
2171 g_employer_id,
2172 null,
2173 null,
2174 null,
2175 null,
2176 null),0);
2177 hr_utility.set_location(' l_holi_bal_val '|| l_holi_bal_val, 850);
2178 ELSIF g_def_bal_id(bal_index).balance_name = 'Paid Holiday Days' and l_ppsn_override IS NULL THEN
2179
2180 hr_utility.set_location(' Inside balance Loop ', 850);
2181 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2182 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2183
2184 l_holi_bal_val :=
2185 NVL(l_holi_bal_val,0)
2186 +
2187 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2188 l_ness_assignment_action_ytd,
2189 g_employer_id,
2190 null,
2191 null,
2192 null,
2193 null,
2194 null),0)*l_hours_per_day;
2195 hr_utility.set_location(' l_holi_bal_val '|| l_holi_bal_val, 850);
2196 ELSIF g_def_bal_id(bal_index).balance_name = 'Total Commission' and l_ppsn_override IS NULL THEN
2197
2198 hr_utility.set_location(' Inside balance Loop ', 850);
2199 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2200 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2201
2202 l_tot_comm_bal_val := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2203 l_ness_assignment_action,
2204 g_employer_id,
2205 null,
2206 null,
2207 null,
2208 null,
2209 null);
2210 hr_utility.set_location(' l_tot_comm_bal_val '|| l_tot_comm_bal_val, 850);
2211
2212 END IF;
2213 /* 9768428 start */
2214 IF g_def_bal_id(bal_index).balance_name = 'Regular EarningsPPSN' and l_ppsn_override IS NOT NULL THEN
2215
2216 hr_utility.set_location(' Inside balance Loop ', 850);
2217 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2218 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2219
2220 l_regwg_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2221 l_ness_assignment_action_ytd,
2222 g_employer_id,
2223 null,
2224 null,
2225 null,
2226 null,
2227 null);
2228 hr_utility.set_location(' l_regwg_bal_val_ppsn '|| l_regwg_bal_val_ppsn, 850);
2229 ELSIF g_def_bal_id(bal_index).balance_name = 'Irregular EarningsPPSN' and l_ppsn_override IS NOT NULL THEN
2230
2231 hr_utility.set_location(' Inside balance Loop ', 850);
2232 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2233 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2234
2235 l_irrb_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2236 l_ness_assignment_action_ytd,
2237 g_employer_id,
2238 null,
2239 null,
2240 null,
2241 null,
2242 null);
2243 hr_utility.set_location(' l_irrb_bal_val_ppsn '|| l_irrb_bal_val_ppsn, 850);
2244 ELSIF g_def_bal_id(bal_index).balance_name = 'Overtime PaymentsPPSN' and l_ppsn_override IS NOT NULL THEN
2245
2246 hr_utility.set_location(' Inside balance Loop ', 850);
2247 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2248 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2249
2250 l_ovrt_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2251 l_ness_assignment_action_ytd,
2252 g_employer_id,
2253 null,
2254 null,
2255 null,
2256 null,
2257 null);
2258 hr_utility.set_location(' l_ovrt_bal_val_ppsn '|| l_ovrt_bal_val_ppsn, 850);
2259 ELSIF g_def_bal_id(bal_index).balance_name = 'Paid Overtime HoursPPSN' and l_ppsn_override IS NOT NULL THEN
2260
2261 hr_utility.set_location(' Inside balance Loop ', 850);
2262 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2263 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2264
2265 l_othr_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2266 l_ness_assignment_action,
2267 g_employer_id,
2268 null,
2269 null,
2270 null,
2271 null,
2272 null);
2273 hr_utility.set_location(' l_othr_bal_val_ppsn '|| l_othr_bal_val_ppsn, 850);
2274 ELSIF g_def_bal_id(bal_index).balance_name = 'Normal Working HoursPPSN' and l_ppsn_override IS NOT NULL THEN
2275
2276 hr_utility.set_location(' Inside balance Loop ', 850);
2277 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2278 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2279
2280
2281
2282
2283
2284 IF(l_valid_asg_rec.hourly_or_salaried='H')
2285 THEN
2286 l_chrs_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2287 l_ness_assignment_action,
2288 g_employer_id,
2289 null,
2290 null,
2291 null,
2292 null,
2293 null);
2294 l_ref_period_hours:=l_ref_period_pay;
2295 END IF;
2296 hr_utility.set_location(' l_chrs_bal_val_ppsn '|| l_chrs_bal_val_ppsn, 850);
2297 --Bug # 6774024
2298 ELSIF (g_def_bal_id(bal_index).balance_name = 'Paid Maternity HoursPPSN') and l_ppsn_override IS NOT NULL THEN
2299
2300 hr_utility.set_location(' Inside balance Loop ', 850);
2301 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2302 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2303
2304 l_mat_bal_val_ppsn := nvl(l_mat_bal_val_ppsn,0)
2305 +
2306 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2307 l_ness_assignment_action_ytd,
2308 g_employer_id,
2309 null,
2310 null,
2311 null,
2312 null,
2313 null),0);
2314
2315 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2316
2317 hr_utility.set_location(' l_mat_bal_val_ppsn '|| l_mat_bal_val_ppsn, 850);
2318 ELSIF (g_def_bal_id(bal_index).balance_name = 'Paid Sick Leave HoursPPSN' ) and l_ppsn_override IS NOT NULL THEN
2319
2320 hr_utility.set_location(' Inside balance Loop ', 850);
2321 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2322 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2323
2324 l_sic_bal_val_ppsn :=
2325 nvl(l_sic_bal_val_ppsn,0)
2326 +
2327 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2328 l_ness_assignment_action_ytd,
2329 g_employer_id,
2330 null,
2331 null,
2332 null,
2333 null,
2334 null),0);
2335
2336 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2337 hr_utility.set_location(' l_sic_bal_val_ppsn '|| l_sic_bal_val_ppsn, 850);
2338 ELSIF (g_def_bal_id(bal_index).balance_name = 'Paid Other Leave HoursPPSN' ) and l_ppsn_override IS NOT NULL THEN
2339
2340 hr_utility.set_location(' Inside balance Loop ', 850);
2341 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2342 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2343
2344 l_otl_bal_val_ppsn :=
2345 NVl(l_otl_bal_val_ppsn,0)
2346 +
2347 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2348 l_ness_assignment_action_ytd,
2349 g_employer_id,
2350 null,
2351 null,
2352 null,
2353 null,
2354 null),0);
2355
2356 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2357 hr_utility.set_location(' l_otl_bal_val_ppsn '|| l_otl_bal_val_ppsn, 850);
2358
2359 /*6856473 added checks for balances Paid Maternity Days, Paid Sick Leave Days and Paid Other Leave Days*/
2360
2361 ELSIF (g_def_bal_id(bal_index).balance_name = 'Paid Maternity DaysPPSN') and l_ppsn_override IS NOT NULL THEN
2362
2363 hr_utility.set_location(' Inside balance Loop ', 850);
2364 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2365 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2366
2367 l_mat_bal_val_ppsn :=
2368 nvl(l_mat_bal_val_ppsn,0)
2369 +
2370 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2371 l_ness_assignment_action_ytd,
2372 g_employer_id,
2373 null,
2374 null,
2375 null,
2376 null,
2377 null),0)*l_hours_per_day;
2378
2379 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2380
2381 hr_utility.set_location(' l_mat_bal_val_ppsn '|| l_mat_bal_val_ppsn, 850);
2382 ELSIF ( g_def_bal_id(bal_index).balance_name = 'Paid Sick Leave DaysPPSN') and l_ppsn_override IS NOT NULL THEN
2383
2384 hr_utility.set_location(' Inside balance Loop ', 850);
2385 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2386 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2387
2388 l_sic_bal_val_ppsn :=
2389 nvl(l_sic_bal_val_ppsn,0)
2390 +
2391 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2392 l_ness_assignment_action_ytd,
2393 g_employer_id,
2394 null,
2395 null,
2396 null,
2397 null,
2398 null),0)*l_hours_per_day;
2399 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2400 hr_utility.set_location(' l_sic_bal_val_ppsn '|| l_sic_bal_val_ppsn, 850);
2401 ELSIF ( g_def_bal_id(bal_index).balance_name = 'Paid Other Leave DaysPPSN') and l_ppsn_override IS NOT NULL THEN
2402
2403 hr_utility.set_location(' Inside balance Loop ', 850);
2404 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2405 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2406
2407 l_otl_bal_val_ppsn :=
2408 nvl(l_otl_bal_val_ppsn,0)
2409 +
2410 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2411 l_ness_assignment_action_ytd,
2412 g_employer_id,
2413 null,
2414 null,
2415 null,
2416 null,
2417 null),0)*l_hours_per_day
2418 ;
2419 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2420
2421 hr_utility.set_location(' l_otl_bal_val_ppsn '|| l_otl_bal_val, 850);
2422
2423
2424 ELSIF g_def_bal_id(bal_index).balance_name = 'Voluntary Sickness InsurancePPSN' and l_ppsn_override IS NOT NULL THEN
2425
2426 hr_utility.set_location(' Inside balance Loop ', 850);
2427 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2428 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2429
2430 l_vhi_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2431 l_ness_assignment_action_ytd,
2432 g_employer_id,
2433 null,
2434 null,
2435 null,
2436 null,
2437 null);
2438 hr_utility.set_location(' l_vhi_bal_val_ppsn '|| l_vhi_bal_val_ppsn, 850);
2439 ELSIF g_def_bal_id(bal_index).balance_name = 'Staff HousingPPSN' and l_ppsn_override IS NOT NULL THEN
2440
2441 hr_utility.set_location(' Inside balance Loop ', 850);
2442 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2443 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2444
2445 l_hse_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2446 l_ness_assignment_action_ytd,
2447 g_employer_id,
2448 null,
2449 null,
2450 null,
2451 null,
2452 null);
2453 hr_utility.set_location(' l_hse_bal_val_ppsn '|| l_hse_bal_val_ppsn, 850);
2454 ELSIF g_def_bal_id(bal_index).balance_name = 'Other BenefitsPPSN' and l_ppsn_override IS NOT NULL THEN
2455
2456 hr_utility.set_location(' Inside balance Loop ', 850);
2457 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2458 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2459
2460 l_otben_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2461 l_ness_assignment_action_ytd,
2462 g_employer_id,
2463 null,
2464 null,
2465 null,
2466 null,
2467 null);
2468 hr_utility.set_location(' l_otben_bal_val_ppsn '|| l_otben_bal_val_ppsn, 850);
2469 ELSIF g_def_bal_id(bal_index).balance_name = 'Other SubsidiesPPSN' and l_ppsn_override IS NOT NULL THEN
2470
2471 hr_utility.set_location(' Inside balance Loop ', 850);
2472 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2473 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2474
2475 l_ot_sub_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2476 l_ness_assignment_action_ytd,
2477 g_employer_id,
2478 null,
2479 null,
2480 null,
2481 null,
2482 null);
2483 hr_utility.set_location(' l_ot_sub_bal_val_ppsn '|| l_ot_sub_bal_val_ppsn, 850);
2484
2485
2486
2487 ELSIF g_def_bal_id(bal_index).balance_name = 'IE BIK Company VehiclePPSN' and l_ppsn_override IS NOT NULL THEN
2488
2489 hr_utility.set_location(' Inside balance Loop ', 850);
2490 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2491 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2492
2493 l_bik_veh_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2494 l_ness_assignment_action_ytd,
2495 g_employer_id,
2496 null,
2497 null,
2498 null,
2499 null,
2500 null);
2501
2502 hr_utility.set_location(' l_bik_veh_bal_val_ppsn '|| l_bik_veh_bal_val_ppsn, 850);
2503
2504
2505
2506 ELSIF g_def_bal_id(bal_index).balance_name = 'IE PRSI Insurable WeeksPPSN' and l_ppsn_override IS NOT NULL THEN
2507
2508 hr_utility.set_location(' Inside balance Loop ', 850);
2509 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2510 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2511
2512 l_total_weeks_ppsn :=
2513 nvl(l_total_weeks_ppsn,0)
2514 +
2515 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2516 l_ness_assignment_action_ytd,
2517 g_employer_id,
2518 null,
2519 null,
2520 null,
2521 null,
2522 null),0);
2523 hr_utility.set_location(' l_total_weeks_ppsn '|| l_total_weeks_ppsn, 850);
2524 ELSIF g_def_bal_id(bal_index).balance_name = 'IE PRSI K Term Insurable WeeksPPSN' and l_ppsn_override IS NOT NULL THEN
2525
2526 hr_utility.set_location(' Inside balance Loop ', 850);
2527 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2528 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2529
2530 l_total_weeks_ppsn :=
2531 nvl(l_total_weeks_ppsn,0)
2532 +
2533 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2534 l_ness_assignment_action_ytd,
2535 g_employer_id,
2536 null,
2537 null,
2538 null,
2539 null,
2540 null),0);
2541 hr_utility.set_location(' l_total_weeks_ppsn '|| l_total_weeks_ppsn, 850);
2542 ELSIF g_def_bal_id(bal_index).balance_name = 'IE PRSI M Term Insurable WeeksPPSN' and l_ppsn_override IS NOT NULL THEN
2543
2544 hr_utility.set_location(' Inside balance Loop ', 850);
2545 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2546 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2547
2548 l_total_weeks_ppsn :=
2549 nvl(l_total_weeks_ppsn,0)
2550 +
2551 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2552 l_ness_assignment_action_ytd,
2553 g_employer_id,
2554 null,
2555 null,
2556 null,
2557 null,
2558 null),0);
2559 hr_utility.set_location(' l_total_weeks_ppsn '|| l_total_weeks_ppsn, 850);
2560 ELSIF g_def_bal_id(bal_index).balance_name = 'Regular EarningsMONTHPPSN' and l_ppsn_override IS NOT NULL THEN
2561
2562 hr_utility.set_location(' Inside balance Loop ', 850);
2563 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2564 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2565
2566 l_regwg_bal_val_ptd_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2567 l_ness_assignment_action,
2568 g_employer_id,
2569 null,
2570 null,
2571 null,
2572 null,
2573 null);
2574 hr_utility.set_location(' l_regwg_bal_val_ptd_ppsn '|| l_regwg_bal_val_ptd_ppsn, 850);
2575
2576 ELSIF g_def_bal_id(bal_index).balance_name = 'Overtime PaymentsMONTHPPSN' and l_ppsn_override IS NOT NULL THEN
2577
2578 hr_utility.set_location(' Inside balance Loop ', 850);
2579 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2580 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2581
2582 l_ovrt_bal_val_ptd_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2583 l_ness_assignment_action,
2584 g_employer_id,
2585 null,
2586 null,
2587 null,
2588 null,
2589 null);
2590 hr_utility.set_location(' l_ovrt_bal_val_ptd_ppsn '|| l_ovrt_bal_val_ptd_ppsn, 850);
2591 ELSIF g_def_bal_id(bal_index).balance_name = 'Regular Shift AllowancePPSN' and l_ppsn_override IS NOT NULL THEN
2592
2593 hr_utility.set_location(' Inside balance Loop ', 850);
2594 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2595 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2596
2597 l_reg_shft_allnce_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2598 l_ness_assignment_action,
2599 g_employer_id,
2600 null,
2601 null,
2602 null,
2603 null,
2604 null);
2605 hr_utility.set_location(' l_reg_shft_allnce_bal_val_ppsn '|| l_reg_shft_allnce_bal_val_ppsn, 850);
2606 ELSIF g_def_bal_id(bal_index).balance_name = 'Irregular Shift AllowancePPSN' and l_ppsn_override IS NOT NULL THEN
2607
2608 hr_utility.set_location(' Inside balance Loop ', 850);
2609 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2610 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2611
2612 l_ireg_shft_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2613 l_ness_assignment_action,
2614 g_employer_id,
2615 null,
2616 null,
2617 null,
2618 null,
2619 null);
2620 hr_utility.set_location(' l_ireg_shft_val_ppsn '|| l_ireg_shft_val_ppsn, 850);
2621 ELSIF g_def_bal_id(bal_index).balance_name = 'Paid Holiday HoursPPSN' and l_ppsn_override IS NOT NULL THEN
2622
2623 hr_utility.set_location(' Inside balance Loop ', 850);
2624 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2625 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2626
2627 l_holi_bal_val_ppsn :=
2628 NVL(l_holi_bal_val_ppsn,0)
2629 +
2630 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2631 l_ness_assignment_action_ytd,
2632 g_employer_id,
2633 null,
2634 null,
2635 null,
2636 null,
2637 null),0);
2638 hr_utility.set_location(' l_holi_bal_val_ppsn '|| l_holi_bal_val_ppsn, 850);
2639 ELSIF g_def_bal_id(bal_index).balance_name = 'Paid Holiday DaysPPSN' and l_ppsn_override IS NOT NULL THEN
2640
2641 hr_utility.set_location(' Inside balance Loop ', 850);
2642 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2643 hr_utility.set_location(' l_ness_assignment_action_ytd '|| l_ness_assignment_action_ytd, 850);
2644
2645 l_holi_bal_val_ppsn :=
2646 NVL(l_holi_bal_val_ppsn,0)
2647 +
2648 NVL(PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2649 l_ness_assignment_action_ytd,
2650 g_employer_id,
2651 null,
2652 null,
2653 null,
2654 null,
2655 null),0)*l_hours_per_day;
2656 hr_utility.set_location(' l_holi_bal_val_ppsn '|| l_holi_bal_val_ppsn, 850);
2657 ELSIF g_def_bal_id(bal_index).balance_name = 'Total CommissionPPSN' and l_ppsn_override IS NOT NULL THEN
2658
2659 hr_utility.set_location(' Inside balance Loop ', 850);
2660 hr_utility.set_location(' g_def_bal_id(bal_index).balance_name '||g_def_bal_id(bal_index).balance_name, 850);
2661 hr_utility.set_location(' l_ness_assignment_action '||l_ness_assignment_action, 850);
2662
2663 l_tot_comm_bal_val_ppsn := PAY_BALANCE_PKG.GET_VALUE(g_def_bal_id(bal_index).defined_balance_id,
2664 l_ness_assignment_action,
2665 g_employer_id,
2666 null,
2667 null,
2668 null,
2669 null,
2670 null);
2671 hr_utility.set_location(' l_tot_comm_bal_val_ppsn '|| l_tot_comm_bal_val_ppsn, 850);
2672
2673 END IF;
2674 /* 9768428 end */
2675 END LOOP;
2676 /* 9768428 start */
2677 hr_utility.set_location(' l_regwg_bal_val '|| l_regwg_bal_val, 850);
2678 hr_utility.set_location(' l_irrb_bal_val '|| l_irrb_bal_val, 850);
2679 hr_utility.set_location(' l_ovrt_bal_val '|| l_ovrt_bal_val, 850);
2680 hr_utility.set_location(' l_othr_bal_val '|| l_othr_bal_val, 850);
2681 hr_utility.set_location(' l_mat_bal_val '|| l_mat_bal_val, 850);
2682 hr_utility.set_location(' l_sic_bal_val '|| l_sic_bal_val, 850);
2683 hr_utility.set_location(' l_otl_bal_val '|| l_otl_bal_val, 850);
2684 hr_utility.set_location(' l_mat_bal_val '|| l_mat_bal_val, 850);
2685 hr_utility.set_location(' l_sic_bal_val '|| l_sic_bal_val, 850);
2686 hr_utility.set_location(' l_otl_bal_val '|| l_otl_bal_val, 850);
2687 hr_utility.set_location(' l_vhi_bal_val '|| l_vhi_bal_val, 850);
2688 hr_utility.set_location(' l_hse_bal_val '|| l_hse_bal_val, 850);
2689 hr_utility.set_location(' l_otben_bal_val '|| l_otben_bal_val, 850);
2690 hr_utility.set_location(' l_ot_sub_bal_val '|| l_ot_sub_bal_val, 850);
2691 hr_utility.set_location(' l_bik_veh_bal_val '|| l_bik_veh_bal_val, 850);
2692 hr_utility.set_location(' l_total_weeks '|| l_total_weeks, 850);
2693 hr_utility.set_location(' l_regwg_bal_val_ptd '|| l_regwg_bal_val_ptd, 850);
2694 hr_utility.set_location(' l_ovrt_bal_val_ptd '|| l_ovrt_bal_val_ptd, 850);
2695 hr_utility.set_location(' l_reg_shft_allnce_bal_val '|| l_reg_shft_allnce_bal_val, 850);
2696 hr_utility.set_location(' l_ireg_shft_allnce_bal_val '|| l_ireg_shft_allnce_bal_val, 850);
2697 hr_utility.set_location(' l_holi_bal_val '|| l_holi_bal_val, 850);
2698 hr_utility.set_location(' l_tot_comm_bal_val '|| l_tot_comm_bal_val, 850);
2699 hr_utility.set_location(' l_chrs_bal_val '|| l_chrs_bal_val, 850);
2700
2701 hr_utility.set_location(' PPSN Balances ', 850);
2702
2703 hr_utility.set_location(' l_regwg_bal_val_ppsn '|| l_regwg_bal_val_ppsn, 850);
2704 hr_utility.set_location(' l_irrb_bal_val_ppsn '|| l_irrb_bal_val_ppsn, 850);
2705 hr_utility.set_location(' l_ovrt_bal_val_ppsn '|| l_ovrt_bal_val_ppsn, 850);
2706 hr_utility.set_location(' l_othr_bal_val_ppsn '|| l_othr_bal_val_ppsn, 850);
2707 hr_utility.set_location(' l_mat_bal_val_ppsn '|| l_mat_bal_val_ppsn, 850);
2708 hr_utility.set_location(' l_sic_bal_val_ppsn '|| l_sic_bal_val_ppsn, 850);
2709 hr_utility.set_location(' l_otl_bal_val_ppsn '|| l_otl_bal_val_ppsn, 850);
2710 hr_utility.set_location(' l_mat_bal_val_ppsn '|| l_mat_bal_val_ppsn, 850);
2711 hr_utility.set_location(' l_sic_bal_val_ppsn '|| l_sic_bal_val_ppsn, 850);
2712 hr_utility.set_location(' l_otl_bal_val_ppsn '|| l_otl_bal_val_ppsn, 850);
2713 hr_utility.set_location(' l_vhi_bal_val_ppsn '|| l_vhi_bal_val_ppsn, 850);
2714 hr_utility.set_location(' l_hse_bal_val_ppsn '|| l_hse_bal_val_ppsn, 850);
2715 hr_utility.set_location(' l_otben_bal_val_ppsn '|| l_otben_bal_val_ppsn, 850);
2716 hr_utility.set_location(' l_ot_sub_bal_val_ppsn '|| l_ot_sub_bal_val_ppsn, 850);
2717 hr_utility.set_location(' l_bik_veh_bal_val_ppsn '|| l_bik_veh_bal_val_ppsn, 850);
2718 hr_utility.set_location(' l_total_weeks_ppsn '|| l_total_weeks_ppsn, 850);
2719 hr_utility.set_location(' l_regwg_bal_val_ptd_ppsn '|| l_regwg_bal_val_ptd_ppsn, 850);
2720 hr_utility.set_location(' l_ovrt_bal_val_ptd_ppsn '|| l_ovrt_bal_val_ptd_ppsn, 850);
2721 hr_utility.set_location(' l_reg_shft_allnce_bal_val_ppsn '|| l_reg_shft_allnce_bal_val_ppsn, 850);
2722 hr_utility.set_location(' l_ireg_shft_val_ppsn '|| l_ireg_shft_val_ppsn, 850);
2723 hr_utility.set_location(' l_holi_bal_val_ppsn '|| l_holi_bal_val_ppsn, 850);
2724 hr_utility.set_location(' l_tot_comm_bal_val_ppsn '|| l_tot_comm_bal_val_ppsn, 850);
2725 hr_utility.set_location(' l_chrs_bal_val_ppsn '|| l_chrs_bal_val_ppsn, 850);
2726
2727 IF l_ppsn_override IS NOT NULL THEN
2728 l_regwg_bal_val := l_regwg_bal_val_ppsn;
2729 l_irrb_bal_val := l_irrb_bal_val_ppsn;
2730 l_ovrt_bal_val := l_ovrt_bal_val_ppsn;
2731 l_othr_bal_val := l_othr_bal_val_ppsn;
2732 IF(l_valid_asg_rec.hourly_or_salaried='H') THEN
2733 l_chrs_bal_val:=l_chrs_bal_val_ppsn;
2734 END IF;
2735 l_mat_bal_val := l_mat_bal_val_ppsn;
2736 l_sic_bal_val := l_sic_bal_val_ppsn;
2737 l_otl_bal_val := l_otl_bal_val_ppsn;
2738 l_mat_bal_val := l_mat_bal_val_ppsn;
2739 l_sic_bal_val := l_sic_bal_val_ppsn;
2740 l_otl_bal_val := l_otl_bal_val_ppsn;
2741 l_vhi_bal_val := l_vhi_bal_val_ppsn;
2742 l_hse_bal_val := l_hse_bal_val_ppsn;
2743 l_otben_bal_val := l_otben_bal_val_ppsn;
2744 l_ot_sub_bal_val := l_ot_sub_bal_val_ppsn;
2745 l_bik_veh_bal_val := l_bik_veh_bal_val_ppsn;
2746 l_total_weeks := l_total_weeks_ppsn;
2747 l_regwg_bal_val_ptd := l_regwg_bal_val_ptd_ppsn;
2748 l_ovrt_bal_val_ptd := l_ovrt_bal_val_ptd_ppsn;
2749 l_reg_shft_allnce_bal_val := l_reg_shft_allnce_bal_val_ppsn;
2750 l_ireg_shft_allnce_bal_val := l_ireg_shft_val_ppsn;
2751 l_holi_bal_val := l_holi_bal_val_ppsn;
2752 l_tot_comm_bal_val := l_tot_comm_bal_val_ppsn;
2753 END IF;
2754 /* 9768428 end */
2755 l_tot_shft_allnce_bal_val := NVL(l_reg_shft_allnce_bal_val,0) + NVL(l_ireg_shft_allnce_bal_val,0);
2756
2757
2758 l_ann_earning := NVL(l_regwg_bal_val,0) + NVL(l_irrb_bal_val,0) + NVL(l_ovrt_bal_val,0)
2759 + NVL(l_bik_veh_bal_val,0) + NVL(l_vhi_bal_val,0) + NVL(l_hse_bal_val,0)
2760 + NVL(l_otben_bal_val,0) + NVL(l_ot_sub_bal_val,0);
2761
2762 l_ann_bik := NVL(l_bik_veh_bal_val,0) + NVL(l_vhi_bal_val,0) + NVL(l_hse_bal_val,0)
2763 + NVL(l_otben_bal_val,0) + NVL(l_ot_sub_bal_val,0);
2764
2765
2766 l_other_absence:= NVL(l_mat_bal_val,0) + NVL(l_sic_bal_val,0) + NVL(l_otl_bal_val,0);
2767
2768 l_gross_earning:= NVL(l_regwg_bal_val_ptd,0) + NVL(l_ovrt_bal_val_ptd,0) + NVL(l_ireg_shft_allnce_bal_val,0);
2769 -- + NVL(l_tot_shft_allnce_bal_val,0) + NVL(l_tot_comm_bal_val,0) ; /* 9803428 */
2770
2771
2772
2773 END IF;
2774
2775
2776
2777 hr_utility.set_location(' VALUE OF EMP_CATG'||l_valid_asg_rec.EMP_CATG, 890);
2778
2779
2780 OPEN csr_employee_details(l_person_id,g_archive_effective_date);
2781 FETCH csr_employee_details INTO l_employee_details;
2782 CLOSE csr_employee_details;
2783
2784 IF g_send_emp='Y' THEN
2785
2786 hr_utility.set_location(' Before deleting the PL table pl_address. ',1100);
2787 pl_address.delete;
2788
2789 pl_address(1) := l_employee_details.address_line1;
2790 pl_address(2) := l_employee_details.address_line2;
2791 pl_address(3) := l_employee_details.address_line3;
2792 pl_address(4) := l_employee_details.City;
2793 pl_address(5) := l_employee_details.COUNTY;
2794 pl_address(6) := l_employee_details.Country;
2795
2796
2797 hr_utility.set_location(' pl_address.COUNT: '||pl_address.COUNT,1100);
2798
2799 hr_utility.set_location(' pl_address(1): '||pl_address(1),1100);
2800 hr_utility.set_location(' pl_address(2): '||pl_address(2),1100);
2801 hr_utility.set_location(' pl_address(3): '||pl_address(3),1100);
2802 hr_utility.set_location(' pl_address(4): '||pl_address(4),1100);
2803 hr_utility.set_location(' pl_address(5): '||pl_address(5),1100);
2804 hr_utility.set_location(' pl_address(6): '||pl_address(6),1100);
2805
2806 hr_utility.set_location(' Before deleting the PL table pl_address_final. ',1100);
2807 pl_address_final.delete;
2808 hr_utility.set_location(' Initializing the PL table pl_address_final. ',1100);
2809
2810 FOR j in 1..pl_address.LAST
2811 LOOP
2812 IF pl_address(j) IS NOT NULL THEN
2813 k:=k+1;
2814 pl_address_final(k) := pl_address(j);
2815 hr_utility.set_location('pl_address_final'||k||'--'||pl_address_final(k),1100);
2816 END IF;
2817 END LOOP;
2818
2819 hr_utility.set_location(' Re Initializing the record l_person_details. ',1100);
2820
2821
2822
2823 hr_utility.set_location(' Re Initializing the cursor record l_person_details with actual values. ',1100);
2824 hr_utility.set_location(' pl_address_final.COUNT: '||pl_address_final.COUNT,1100);
2825
2826 IF pl_address_final.COUNT > 0 THEN
2827
2828 l_employee_details.address_line1:=NULL;
2829 l_employee_details.address_line2:=NULL;
2830 l_employee_details.address_line3:=NULL;
2831 l_employee_details.address_line4:=NULL;
2832 l_employee_details.address_line5:=NULL;
2833
2834 FOR l in 1..pl_address_final.LAST
2835 LOOP
2836 hr_utility.set_location(' Inside the loop of PL table pl_address_final',1100);
2837 BEGIN
2838 IF l = 1 THEN
2839 hr_utility.set_location(' employee address .addr1 ',1100);
2840 l_employee_details.address_line1 := pl_address_final(1);
2841 hr_utility.set_location('employee address .addr1 ',1101);
2842 END IF;
2843 --
2844 IF l = 2 THEN
2845 hr_utility.set_location(' employee address.addr2 ',1102);
2846 l_employee_details.address_line2 := pl_address_final(2);
2847 hr_utility.set_location(' employee address .addr2 ',1103);
2848 END IF;
2849 --
2850 IF l = 3 THEN
2851 hr_utility.set_location(' employee address.addr3 ',1104);
2852 l_employee_details.address_line3 := pl_address_final(3);
2853 hr_utility.set_location(' employee address .addr3 ',1105);
2854 END IF;
2855 --
2856 IF l = 4 THEN
2857 hr_utility.set_location(' employee address.addr4 ',1106);
2858 l_employee_details.address_line4 := pl_address_final(4);
2859 hr_utility.set_location(' employee address.addr5 ',1107);
2860 END IF;
2861 IF l = 5 THEN
2862 hr_utility.set_location(' employee address.addr5 ',1106);
2863 l_employee_details.address_line5 := pl_address_final(5);
2864 hr_utility.set_location(' eemployee address.addr5',1107);
2865 END IF;
2866 EXCEPTION
2867 WHEN NO_DATA_FOUND THEN
2868 NULL;
2869 END;
2870 END LOOP;
2871 END IF;
2872 hr_utility.set_location(' After Re Initializing the cursor record l_person_details with actual values. ',1100);
2873 ELSE
2874 l_employee_details.address_line1:=NULL;
2875 l_employee_details.address_line2:=NULL;
2876 l_employee_details.address_line3:=NULL;
2877 l_employee_details.address_line4:=NULL;
2878 l_employee_details.address_line5:=NULL;
2879
2880 END IF;
2881
2882 IF (nvl(l_ppsn_override,l_employee_details.PPS) IS NULL) THEN -- 9768428
2883 l_id_reference:=l_valid_asg_rec.assignment_number;
2884 ELSE
2885 l_id_reference:=NULL;
2886 END IF;
2887
2888 hr_utility.set_location(' l_employement_type '|| l_employement_type,1100);
2889 hr_utility.set_location(' l_valid_asg_rec.NES_EMPLOYEMENT_CATG '|| l_valid_asg_rec.NES_EMPLOYEMENT_CATG,1100);
2890 /* 10081528 start */
2891 IF l_valid_asg_rec.NES_EMPLOYEMENT_CATG = 'INDEF' THEN
2892 l_employement_type:=1;
2893 ELSIF l_valid_asg_rec.NES_EMPLOYEMENT_CATG = 'DEF' THEN
2894 l_employement_type:=2;
2895 ELSIF l_valid_asg_rec.NES_EMPLOYEMENT_CATG = 'AT' THEN
2896 l_employement_type:=3;
2897 ELSE
2898 l_employement_type:=4;
2899 END IF;
2900
2901 hr_utility.set_location(' aft l_employement_type '|| l_employement_type,1100);
2902 /* 10081528 end */
2903
2904 hr_utility.set_location(' l_id_reference '|| l_id_reference,1100);
2905 hr_utility.set_location(' Before Inserting in to IE_NES_EMPLOYEE_INFORMATION ', 890);
2906 pay_action_information_api.create_action_information
2907 ( p_action_information_id => l_action_info_id
2908 ,p_action_context_id => p_assactid
2909 ,p_action_context_type => 'AAP'
2910 ,p_object_version_number => l_ovn
2911 ,p_effective_date => g_archive_effective_date --g_end_date
2912 ,p_source_id => NULL
2913 ,p_source_text => NULL
2914 ,p_action_information_category => 'IE_NES_EMPLOYEE_INFORMATION'
2915 ,p_action_information1 => l_employee_details.full_name -- Full name
2916 ,p_action_information2 => nvl(l_ppsn_override,l_employee_details.PPS) -- PPS 9768428
2917 ,p_action_information3 => substr(l_id_reference,1,9) -- Reference id
2918 ,p_action_information4 => l_employee_details.address_line1 -- addressline_1
2919 ,p_action_information5 => l_employee_details.address_line2 -- addressline_2
2920 ,p_action_information6 => l_employee_details.address_line3 -- addressline_3
2921 ,p_action_information7 => l_employee_details.address_line4 -- addressline_4
2922 ,p_action_information8 => l_employee_details.address_line5 -- addressline_5
2923
2924 );
2925
2926 hr_utility.set_location(' After Inserting in to IE_NES_EMPLOYEE_INFORMATION ', 890);
2927
2928
2929 IF l_ann_earning < NVL(l_irrb_bal_val,0) + NVL(l_ann_bik,0) THEN
2930 l_errflag := 'Y';
2931 Fnd_file.put_line(FND_FILE.LOG,'The Annual Earnings is less than the sum of Irregular Bonuses and Annual Benefit In Kind for Assignment : ' || l_employee_number);
2932 END IF;
2933
2934 IF l_gross_earning>0 and NVL(l_chrs_bal_val,0)<=0 THEN
2935 l_errflag := 'Y';
2936 Fnd_file.put_line(FND_FILE.LOG,'Paid Hours should be greater than Zero for Gross Earnings greater than Zero : ' || l_employee_number);
2937 END IF;
2938
2939 IF l_gross_earning=0 and NVL(l_chrs_bal_val,0)<>0 THEN
2940 l_errflag := 'Y';
2941 Fnd_file.put_line(FND_FILE.LOG,'Paid Hours should be Zero for Gross Earnings equal than Zero : ' || l_employee_number);
2942 END IF;
2943
2944 IF NVL(l_chrs_bal_val,0) < NVL(l_othr_bal_val,0) THEN
2945 l_errflag := 'Y';
2946 Fnd_file.put_line(FND_FILE.LOG,'Paid Hours should be greater than or equal to Overtime Hours : ' || l_employee_number);
2947 END IF;
2948
2949
2950 IF l_gross_earning< (NVL(l_tot_comm_bal_val,0) + NVL(l_tot_shft_allnce_bal_val,0) + NVL(l_ovrt_bal_val_ptd,0)) THEN /* 9795571 replaced l_ovrt_bal by l_ovrt_bal_val_ptd */
2951 l_errflag := 'Y';
2952 Fnd_file.put_line(FND_FILE.LOG,'Gross Earnings must be greater than the sum of Total Commission, Shift Allowance and Overtime Earnings : ' || l_employee_number);
2953 END IF;
2954
2955 -----------------------------------------------------
2956
2957 IF NVL(l_ovrt_bal_val_ptd,0)=0 and NVL(l_othr_bal_val,0)<>0 THEN
2958 l_errflag := 'Y';
2959 Fnd_file.put_line(FND_FILE.LOG,'Overtime Hours should be zero for Overtime Earnings equal to 0 : ' || l_employee_number);
2960 END IF;
2961
2962 IF NVL(l_ovrt_bal_val_ptd,0)>0 and NVL(l_othr_bal_val,0)<=0 THEN
2963 l_errflag := 'Y';
2964 Fnd_file.put_line(FND_FILE.LOG,'Overtime Hours should be greater than zero for Overtime Earnings greater than 0 : ' || l_employee_number);
2965 END IF;
2966
2967 IF NVL(l_ovrt_bal_val_ptd,0)<=0 and NVL(l_othr_bal_val,0)>0 THEN
2968 l_errflag := 'Y';
2969 Fnd_file.put_line(FND_FILE.LOG,'Overtime Earnings should be greater than zero for Overtime Hours greater than 0 : ' || l_employee_number);
2970 END IF;
2971
2972
2973 hr_utility.set_location(' Before Inserting in to IE_NES_PART1 ', 890);
2974 pay_action_information_api.create_action_information
2975 ( p_action_information_id => l_action_info_id
2976 ,p_action_context_id => p_assactid
2977 ,p_action_context_type => 'AAP'
2978 ,p_object_version_number => l_ovn
2979 ,p_effective_date => g_archive_effective_date --g_end_date
2980 ,p_source_id => NULL
2981 ,p_source_text => NULL
2982 ,p_action_information_category => 'IE_NES_PART1'
2983 ,p_action_information1 => lpad(round(l_ann_earning),7,0) -- Annual Earnings (l_regwg_bal_val+l_irrb_bal_val+l_ovrt_bal_val+l_bik_veh_bal_val+l_vhi_bal_val+l_hse_bal_val+l_otben_bal_val+l_ot_sub_bal_val)
2984 ,p_action_information2 => lpad(round(l_irrb_bal_val),7,0) -- irregular bonuses (l_irrb_bal_val)
2985 ,p_action_information3 => lpad(round(l_ann_bik),7,0) -- Annual Benefit in Kind( l_bik_veh_bal_val+l_vhi_bal_val+l_hse_bal_val+l_otben_bal_val+l_ot_sub_bal_val)
2986 ,p_action_information4 => lpad(l_total_weeks,2,0) -- NO of Weeks ( sum of IE PRSI Insurable Weeks + IE PRSI K Term Insurable Weeks + IE PRSI M Term Insurable Weeks)
2987 ,p_action_information5 => lpad(to_char(to_number(l_holi_bal_val),'FM999D0'),5,0) -- Paid Holidays
2988 ,p_action_information6 => lpad(to_char(to_number(l_other_absence),'FM999D0'),5,0) -- Other Absence (l_mat_bal_val + l_sic_bal_val + l_otl_bal_val)
2989 ,p_action_information7 => l_employement_type -- Employment Type
2990 ,p_action_information8 => l_freq_pay -- Frequency Pay
2991 ,p_action_information9 => l_ref_period_pay -- Ref Period pay
2992 ,p_action_information10 => l_valid_asg_rec.status_code -- Status Code (l_valid_asg_rec.status_code)
2993 ,p_action_information11 => lpad(round(l_gross_earning),6,0) -- Gross Earnings( l_regwg_bal_val_ptd + l_ovrt_bal_val_ptd + l_reg_shft_allnce_bal_val + l_ireg_shft_allnce_bal_val)
2994 ,p_action_information12 => lpad(round(l_ovrt_bal_val_ptd),6,0) -- Overtime Earnings( l_ovrt_bal_val )
2995 ,p_action_information13 => lpad(round(l_tot_shft_allnce_bal_val),6,0) -- Shift allowance (l_reg_shft_allnce_bal_val + l_ireg_shft_allnce_bal_val)
2996 ,p_action_information14 => lpad(round(l_tot_comm_bal_val),6,0) -- Total Commission (l_regwg_bal_val + l_reg_shft_allnce_bal_val)
2997 ,p_action_information15 => l_prsi_class -- PRSI Class
2998 ,p_action_information16 => l_ref_period_hours -- Ref Period Hours
2999 ,p_action_information17 => lpad(to_char(to_number(l_chrs_bal_val),'FM999D0'),5,0) -- Contracted Hours (l_chrs_bal_val)
3000 ,p_action_information18 => lpad(to_char(to_number(l_othr_bal_val),'FM999D0'),5,0) -- Overtime Hours (l_othr_bal_val)
3001 );
3002 hr_utility.set_location(' After Inserting in to IE_NES_PART1 ', 890);
3003
3004
3005
3006
3007 l_str_common := 'The report completed with validation warning(s). Do not submit the generated XML file '
3008 ||'as it may not be in the correct format. You can, however, modify and use the template output.';
3009
3010 IF l_errflag = 'Y' THEN
3011 Fnd_file.put_line(FND_FILE.LOG,l_str_common);
3012 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',
3013 'NES Report completed with validation warning(s).');
3014 END IF;
3015
3016
3017 END archive_data;
3018
3019
3020
3021
3022 -----------------------------------------------------------------------
3023 --C2B
3024 -----------------------------------------------------------------------
3025
3026 FUNCTION c2b( c IN CLOB ) RETURN BLOB
3027 -- typecasts CLOB to BLOB (binary conversion)
3028 IS
3029 pos PLS_INTEGER := 1;
3030 buffer RAW( 32767 );
3031 res BLOB;
3032 lob_len PLS_INTEGER := DBMS_LOB.getLength( c );
3033 BEGIN
3034 Hr_Utility.set_location('Entering: PAY_IE_NES_REPORT_PKG.c2b',1000);
3035 DBMS_LOB.createTemporary( res, TRUE );
3036 DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
3037
3038
3039 LOOP
3040 buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( c, 16000, pos ) );
3041
3042 IF UTL_RAW.LENGTH( buffer ) > 0 THEN
3043 DBMS_LOB.writeAppend( res, UTL_RAW.LENGTH( buffer ), buffer );
3044 END IF;
3045
3046 pos := pos + 16000;
3047 EXIT WHEN pos > lob_len;
3048 END LOOP;
3049
3050 Hr_Utility.set_location('Leaving: PAY_IE_NES_REPORT_PKG.c2b',1010);
3051 RETURN res; -- res is OPEN here
3052 END c2b;
3053 -----------------------------------------------------------------------
3054 -- GEN_BODY_XML
3055 -----------------------------------------------------------------------
3056
3057 PROCEDURE gen_body_xml
3058 IS
3059 l_string varchar2(32767) := NULL;
3060 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
3061 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
3062
3063 l_buf VARCHAR2(2000);
3064 l_proc VARCHAR2(100);
3065
3066 l_person_id per_all_people_f.person_id%TYPE;
3067 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3068 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
3069 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
3070
3071 cursor csr_nes_emp_info (c_pact_id NUMBER) IS
3072 SELECT
3073 action_information1 full_name,
3074 action_information2 ppsn,
3075 action_information3 reference_id,
3076 action_information4 address_line1,
3077 action_information5 address_line2,
3078 action_information6 address_line3,
3079 action_information7 address_line4,
3080 action_information8 address_line5
3081
3082 FROM pay_action_information
3083 WHERE action_context_id = c_pact_id
3084 AND action_context_type = 'AAP'
3085 AND action_information_category ='IE_NES_EMPLOYEE_INFORMATION';
3086
3087
3088 cursor csr_nes_part1 (c_pact_id NUMBER) IS
3089 SELECT
3090 NVL(action_information1,0) annual_earning,
3091 NVL(action_information2,0) irreg_earning,
3092 NVL(action_information3,0) annual_bik,
3093 NVL(action_information4,0) no_of_weeks,
3094 NVL(action_information5,0) paid_holiday,
3095 NVL(action_information6,0) other_absence,
3096 NVL(action_information7,0) employment_type,
3097 NVL(action_information8,0) freq_pay,
3098 NVL(action_information9,0) ref_period_pay,
3099 NVL(action_information10,0) status_code,
3100 NVL(action_information11,0) gross_earning,
3101 NVL(action_information12,0) overtime_earning,
3102 NVL(action_information13,0) shift_allowance,
3103 NVL(action_information14,0) total_commission,
3104 NVL(action_information15,0) prsi_class,
3105 NVL(action_information16,0) ref_period_hours,
3106 NVL(action_information17,0) contracted_hours,
3107 NVL(action_information18,0) overtime_hours
3108 FROM pay_action_information
3109 WHERE action_context_id = c_pact_id
3110 AND action_context_type = 'AAP'
3111 AND action_information_category ='IE_NES_PART1';
3112
3113 l_csr_nes_emp_info csr_nes_emp_info%ROWTYPE;
3114 l_csr_nes_part1 csr_nes_part1%ROWTYPE;
3115
3116
3117 BEGIN
3118 hr_utility.set_location(' Entering: PAY_IE_NES_REPORT_PKG.gen_body_xml: ', 2000);
3119
3120 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
3121 l_assignment_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
3122
3123 OPEN csr_nes_emp_info(l_assignment_action_id);
3124 FETCH csr_nes_emp_info INTO l_csr_nes_emp_info;
3125 CLOSE csr_nes_emp_info;
3126
3127 OPEN csr_nes_part1(l_assignment_action_id);
3128 FETCH csr_nes_part1 INTO l_csr_nes_part1;
3129 CLOSE csr_nes_part1;
3130
3131 l_string := l_string || '<Data>'||EOL ;
3132 l_string := l_string || ' <Full_Name>'|| l_csr_nes_emp_info.full_name ||'</Full_Name>' ;
3133 l_string := l_string || ' <PPS>'|| l_csr_nes_emp_info.ppsn ||'</PPS>' ;
3134 l_string := l_string || ' <Id_Reference>'|| l_csr_nes_emp_info.reference_id ||'</Id_Reference>' ;
3135 l_string := l_string || ' <Annual_Earnings>'|| l_csr_nes_part1.annual_earning ||'</Annual_Earnings>' ;
3136 l_string := l_string || ' <Irregular_Bonuses>'|| l_csr_nes_part1.irreg_earning ||'</Irregular_Bonuses>' ;
3137 l_string := l_string || ' <Annual_Benefit_In_Kind>'|| l_csr_nes_part1.annual_bik ||'</Annual_Benefit_In_Kind>' ;
3138 l_string := l_string || ' <No_Of_Weeks>'|| l_csr_nes_part1.no_of_weeks ||'</No_Of_Weeks>' ;
3139 l_string := l_string || ' <Paid_Holidays>'|| l_csr_nes_part1.paid_holiday ||'</Paid_Holidays>' ;
3140 l_string := l_string || ' <Other_Absence>'|| l_csr_nes_part1.other_absence ||'</Other_Absence>' ;
3141 l_string := l_string || ' <Employment_Type>'|| l_csr_nes_part1.employment_type ||'</Employment_Type>' ;
3142 l_string := l_string || ' <Frequency_Pay>'|| l_csr_nes_part1.freq_pay ||'</Frequency_Pay>' ;
3143 l_string := l_string || ' <Ref_Period_Pay>'|| l_csr_nes_part1.ref_period_pay ||'</Ref_Period_Pay>' ;
3144 l_string := l_string || ' <Status_Code>'|| l_csr_nes_part1.status_code ||'</Status_Code>' ;
3145 l_string := l_string || ' <Gross_Earnings>'|| l_csr_nes_part1.gross_earning ||'</Gross_Earnings>' ;
3146 l_string := l_string || ' <Overtime_Earnings>'|| l_csr_nes_part1.overtime_earning ||'</Overtime_Earnings>' ;
3147 l_string := l_string || ' <Shift_Allowance>'|| l_csr_nes_part1.shift_allowance ||'</Shift_Allowance>' ;
3148 l_string := l_string || ' <Total_Commission>'|| l_csr_nes_part1.total_commission ||'</Total_Commission>' ;
3149 l_string := l_string || ' <PRSI_Class>'|| l_csr_nes_part1.prsi_class ||'</PRSI_Class>' ;
3150 l_string := l_string || ' <Ref_Period_Hours>'|| l_csr_nes_part1.ref_period_hours ||'</Ref_Period_Hours>' ;
3151 l_string := l_string || ' <Contracted_Hours>'|| l_csr_nes_part1.contracted_hours ||'</Contracted_Hours>' ;
3152 l_string := l_string || ' <Overtime_Hours>'|| l_csr_nes_part1.overtime_hours ||'</Overtime_Hours>' ;
3153 l_string := l_string || ' <Addr1_Employee >'|| test_XML(l_csr_nes_emp_info.address_line1) ||'</Addr1_Employee >' ; -- 9734490
3154 l_string := l_string || ' <Addr2_Employee >'|| test_XML(l_csr_nes_emp_info.address_line2) ||'</Addr2_Employee >' ; -- 9734490
3155 l_string := l_string || ' <Addr3_Employee >'|| test_XML(l_csr_nes_emp_info.address_line3) ||'</Addr3_Employee >' ; -- 9734490
3156 l_string := l_string || ' <Addr4_Employee >'|| test_XML(l_csr_nes_emp_info.address_line4) ||'</Addr4_Employee >' ; -- 9734490
3157 l_string := l_string || ' <Addr5_Employee >'|| test_XML(l_csr_nes_emp_info.address_line5) ||'</Addr5_Employee >' ; -- 9734490
3158 l_string := l_string ||'</Data>'||EOL ;
3159
3160 l_clob := l_clob||l_string;
3161 IF l_clob IS NOT NULL THEN
3162 l_blob := c2b(l_clob);
3163 pay_core_files.write_to_magtape_lob(l_blob);
3164 END IF;
3165
3166 hr_utility.set_location(' Leaving: PAY_IE_NES_REPORT_PKG.gen_body_xml: ', 2040);
3167
3168 EXCEPTION
3169 WHEN Others THEN
3170 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,2050);
3171 END gen_body_xml;
3172 -----------------------------------------------------------------------
3173 -- GEN_HEADER_XML
3174 -----------------------------------------------------------------------
3175 PROCEDURE gen_header_xml
3176 IS
3177 l_string varchar2(32767) := NULL;
3178 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
3179 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
3180
3181 l_proc VARCHAR2(100);
3182 l_buf VARCHAR2(2000);
3183
3184 CURSOR c_get_header(c_pact_id NUMBER) IS
3185 SELECT
3186 action_information6 year,
3187 action_information7 month,
3188 action_information8 report_type,
3189 action_information9 software_name,
3190 action_information10 software_version,
3191 action_information11 vendor_name,
3192 action_information12 Vendor_phone,
3193 action_information13 org_id,
3194 action_information14 employer_name,
3195 action_information15 addr1,
3196 action_information16 addr2,
3197 action_information17 addr3,
3198 action_information18 addr4,
3199 action_information19 addr5,
3200 action_information20 change_indicator,
3201 action_information21 cbr_no,
3202 action_information22 declarant_name,
3203 action_information23 declarant_phone,
3204 action_information24 declarant_email,
3205 action_information25 declare_date,
3206 action_information26 declarant_position
3207 FROM pay_action_information
3208 WHERE action_context_id = c_pact_id
3209 AND action_context_type = 'PA'
3210 AND action_information_category ='IE_NES_HEADER';
3211
3212 CURSOR c_total_sample(c_pact_id NUMBER) IS
3213 SELECT count(*)
3214 FROM pay_assignment_actions paa,
3215 pay_action_information pai
3216 WHERE paa.payroll_action_id=c_pact_id
3217 AND paa.source_action_id is null
3218 AND pai.action_context_id=paa.assignment_action_id
3219 AND pai.action_information_category='IE_NES_PART1';
3220
3221 l_header c_get_header%rowtype;
3222 l_payroll_action_id number;
3223 l_total_sample NUMBER(10);
3224
3225 BEGIN
3226 l_proc := g_package || 'gen_header_xml';
3227 hr_utility.set_location ('Entering '||l_proc,1500);
3228
3229 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
3230 hr_utility.set_location('Inside PAY_IE_NES_REPORT_PKG.gen_header_xml,l_payroll_action_id: '||l_payroll_action_id,300);
3231
3232 OPEN c_get_header(l_payroll_action_id);
3233 FETCH c_get_header into l_header;
3234 CLOSE c_get_header;
3235
3236 OPEN c_total_sample(l_payroll_action_id);
3237 FETCH c_total_sample INTO l_total_sample;
3238 CLOSE c_total_sample;
3239 l_string := l_string || '<NES' ;
3240 l_string := l_string || ' Yr="'|| l_header.year ||'"';
3241 l_string := l_string || ' TypRt="'|| l_header.report_type||'"';
3242 l_string := l_string || ' SoftwareName="'|| l_header.software_name||'"';
3243 l_string := l_string || ' SoftwareVersion="'|| l_header.software_version||'"';
3244 l_string := l_string || ' VendorName="'|| l_header.vendor_name||'"';
3245 l_string := l_string || ' VendorPhone="'|| l_header.vendor_phone ||'">'||EOL ;
3246
3247 l_string := l_string || '<Company>'||EOL ;
3248
3249 l_string := l_string || ' <Name>'|| substr(l_header.employer_name,1,80)||'</Name>' ;
3250 l_string := l_string || ' <Addr1>'|| substr(test_XML(l_header.addr1),1,80)||'</Addr1>'; -- 9734490
3251 l_string := l_string || ' <Addr2>'|| substr(test_XML(l_header.addr2),1,80)||'</Addr2>'; -- 9734490
3252 l_string := l_string || ' <Addr3>'|| substr(test_XML(l_header.addr3),1,80)||'</Addr3>'; -- 9734490
3253 l_string := l_string || ' <Addr4>'|| substr(test_XML(l_header.addr4),1,80)||'</Addr4>'; -- 9734490
3254 l_string := l_string || ' <Addr5>'|| substr(test_XML(l_header.addr5),1,80)||'</Addr5>'; -- 9734490
3255 l_string := l_string || ' <ChgAd>'|| substr(l_header.change_indicator,1,20)||'</ChgAd>';
3256 l_string := l_string || ' <CBR>'||substr(l_header.cbr_no,1,12)||'</CBR>' ;
3257 l_string := l_string || ' <Total_Sample>'|| l_total_sample ||'</Total_Sample>' ;
3258 l_string := l_string ||'</Company>'||EOL ;
3259
3260 l_string := l_string || '<Declaration>'||EOL ;
3261 l_string := l_string || ' <Contact>'|| substr(l_header.declarant_name,1,40) ||'</Contact>';
3262 l_string := l_string || ' <Phone>'|| substr(l_header.declarant_phone,1,14) ||'</Phone>' ;
3263 l_string := l_string || ' <Email>'|| substr(l_header.declarant_email,1,80) ||'</Email>';
3264 l_string := l_string || ' <Date>'|| to_char(fnd_date.canonical_to_date(l_header.declare_date),'DDMMYYYY') ||'</Date>';
3265 l_string := l_string || ' <Position>'||l_header.declarant_position||'</Position>';
3266
3267 l_string := l_string ||'</Declaration>'||EOL ;
3268 l_clob := l_clob||l_string;
3269 IF l_clob IS NOT NULL THEN
3270 l_blob := c2b(l_clob);
3271 pay_core_files.write_to_magtape_lob(l_blob);
3272 END IF;
3273
3274 EXCEPTION
3275 WHEN Others THEN
3276 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1214);
3277
3278 END gen_header_xml;
3279 -----------------------------------------------------------------------
3280 -- GEN_FOOTER_XML
3281 -----------------------------------------------------------------------
3282 PROCEDURE gen_footer_xml
3283 IS
3284 l_string varchar2(32767) := NULL;
3285 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
3286 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
3287 l_buf VARCHAR2(2000);
3288 l_proc VARCHAR2(100);
3289
3290 begin
3291 l_string:=l_string || '<Comment>' || '<![CDATA[' || g_comments || ']]>' || '</Comment>';
3292 l_string := l_string ||'</NES>'||EOL ;
3293
3294 l_clob := l_clob||l_string;
3295
3296 IF l_clob IS NOT NULL THEN
3297 l_blob := c2b(l_clob);
3298 pay_core_files.write_to_magtape_lob(l_blob);
3299 END IF;
3300
3301 EXCEPTION
3302 WHEN Others THEN
3303 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1214);
3304 l_string := l_string ||'</NES>'||EOL ;
3305 l_clob := l_clob||l_string;
3306 IF l_clob IS NOT NULL THEN
3307 l_blob := c2b(l_clob);
3308 pay_core_files.write_to_magtape_lob(l_blob);
3309 END IF;
3310
3311 end gen_footer_xml;
3312
3313 procedure archive_deinit(pactid IN NUMBER)
3314 IS
3315 l_action_info_id NUMBER;
3316 l_ovn NUMBER;
3317 l_procedure_name VARCHAR2(100):='denit';
3318
3319 begin
3320
3321 hr_utility.set_location('before the call of setup_balance_table in '||l_procedure_name, 320);
3322 setup_balance_table;
3323
3324 hr_utility.set_location('After the call of setup_balance_table in '||l_procedure_name, 320);
3325 end archive_deinit;
3326 END PAY_IE_NES_REPORT_PKG;