[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_SOE_PKG
Source
1 PACKAGE BODY pay_in_soe_pkg AS
2 /* $Header: pyinsoer.pkb 120.1.12010000.2 2008/08/06 07:29:09 ubhat ship $ */
3
4 g_package CONSTANT VARCHAR2(100) := 'pay_in_soe_pkg.';
5 g_debug BOOLEAN;
6 g_sql LONG;
7
8 --------------------------------------------------------------------------
9 -- --
10 -- Name : GET_EMPLOYEE --
11 -- Type : FUNCTION --
12 -- Access : Public --
13 -- Description : Function to return SQL for Personal Information --
14 -- Region --
15 -- --
16 -- Parameters : --
17 -- IN : p_assignment_action_id NUMBER --
18 -- --
19 --------------------------------------------------------------------------
20 FUNCTION get_employee(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
21 RETURN LONG
22 IS
23 l_procedure VARCHAR2(50);
24 l_reg_legal_name VARCHAR2(150);
25
26 CURSOR csr_reg_legal_name
27 IS
28 SELECT hoi2.org_information4
29 FROM per_assignments_f peaf
30 ,hr_soft_coding_keyflex hrscf
31 ,hr_organization_information hoi
32 ,hr_organization_units hou
33 ,hr_organization_information hoi2
34 , pay_assignment_actions paa
35 , pay_payroll_actions ppa
36 WHERE
37 paa.assignment_action_id = p_assignment_action_id
38 AND paa.payroll_action_id = ppa.payroll_action_id
39 AND peaf.assignment_id = paa.assignment_id
40 AND ppa.effective_date BETWEEN peaf.effective_start_date
41 AND peaf.effective_end_date
42 AND peaf.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
43 AND hoi.organization_id=hrscf.segment1
44 AND hoi.org_information_context='PER_IN_INCOME_TAX_DF'
45 AND hou.organization_id=hoi.org_information4
46 AND hoi2.organization_id=hoi.org_information4
47 AND hoi2.org_information_context='PER_IN_COMPANY_DF';
48
49 BEGIN
50
51 l_procedure := g_package || 'get_employee';
52 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
53
54 OPEN csr_reg_legal_name;
55 FETCH csr_reg_legal_name INTO l_reg_legal_name;
56 CLOSE csr_reg_legal_name;
57
58 --
59 g_sql :=
60 'SELECT org.name COL01
61 ,job.name COL02
62 ,loc.location_code COL03
63 ,grd.name COL04
64 ,pay.payroll_name COL05
65 ,pos.name COL06
66 ,'''||l_reg_legal_name||''' COL07
67 ,pg.group_name COL08
68 ,peo.national_identifier COL09
69 ,employee_number COL10
70 ,hl.meaning COL11
71 ,assignment_number COL12
72 ,nvl(ppb1.salary,''0'') COL13
73 FROM per_all_people_f peo
74 ,per_all_assignments_f asg
75 ,hr_all_organization_units_vl org
76 ,per_jobs_vl job
77 ,per_all_positions pos
78 ,hr_locations loc
79 ,per_grades_vl grd
80 ,pay_payrolls_f pay
81 ,pay_people_groups pg
82 ,hr_lookups hl
83 ,(select ppb2.pay_basis_id
84 ,ppb2.business_group_id
85 ,ee.assignment_id
86 ,eev.screen_entry_value salary
87 from per_pay_bases ppb2
88 ,pay_element_entries_f ee
89 ,pay_element_entry_values_f eev
90 where ppb2.input_value_id = eev.input_value_id
91 and ee.element_entry_id = eev.element_entry_id
92 and :effective_date between ee.effective_start_date
93 and ee.effective_end_date
94 and :effective_date between eev.effective_start_date
95 and eev.effective_end_date
96 ) ppb1
97 WHERE asg.assignment_id = :assignment_id
98 AND :effective_date
99 BETWEEN asg.effective_start_date and asg.effective_end_date
100 AND asg.person_id = peo.person_id
101 AND :effective_date
102 BETWEEN peo.effective_start_date and peo.effective_end_date
103 AND asg.position_id = pos.position_id(+)
104 AND asg.job_id = job.job_id(+)
105 AND asg.location_id = loc.location_id(+)
106 AND asg.grade_id = grd.grade_id(+)
107 AND asg.people_group_id = pg.people_group_id(+)
108 AND asg.payroll_id = pay.payroll_id(+)
109 AND :effective_date
110 BETWEEN pay.effective_start_date(+) and pay.effective_end_date(+)
111 AND asg.organization_id = org.organization_id
112 AND :effective_date
113 BETWEEN org.date_from and nvl(org.date_to, :effective_date)
114 AND asg.pay_basis_id = ppb1.pay_basis_id(+)
115 AND asg.assignment_id = ppb1.assignment_id(+)
116 AND asg.business_group_id = ppb1.business_group_id(+)
117 AND hl.application_id (+) = 800
118 AND hl.lookup_type (+) =''NATIONALITY''
119 AND hl.lookup_code (+) =peo.nationality';
120 --
121
122 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
123
124 RETURN g_sql;
125
126 END get_employee;
127
128 --------------------------------------------------------------------------
129 -- --
130 -- Name : GET_FUR_PERSON_INFO --
131 -- Type : FUNCTION --
132 -- Access : Public --
133 -- Description : Function to return SQL for Further Person --
134 -- Information Region --
135 -- --
136 -- Parameters : --
137 -- IN : p_assignment_action_id NUMBER --
138 -- --
139 --------------------------------------------------------------------------
140 FUNCTION get_fur_person_info(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
141 RETURN LONG
142 IS
143 l_procedure VARCHAR2(50);
144
145 BEGIN
146
147 l_procedure := g_package || 'get_fur_person_info';
148 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
149
150 g_sql :=
151 'SELECT TO_CHAR(paf.date_of_birth,''DD-MON-YYYY'') COL01
152 ,TO_CHAR(paf.original_date_of_hire,''DD-MON-YYYY'') COL02
153 ,paf.per_information4 COL03
154 ,paf.per_information8 COL04
155 ,paf.per_information9 COL06
156 ,paf.per_information10 COL05
157 ,hr_general.decode_lookup(''IN_RESIDENTIAL_STATUS'',paf.per_information7) COL07
158 FROM per_all_people_f paf
159 ,per_all_assignments_f asg
160 WHERE
161 asg.assignment_id = '':assignment_id''
162 AND :effective_date
163 BETWEEN asg.effective_start_date AND asg.effective_end_date
164 AND asg.person_id = paf.person_id
165 AND :effective_date
166 BETWEEN paf.effective_start_date AND paf.effective_end_date';
167
168 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
169
170 RETURN g_sql;
171
172 END get_fur_person_info;
173
174 --------------------------------------------------------------------------
175 -- --
176 -- Name : GET_PERIOD --
177 -- Type : FUNCTION --
178 -- Access : Public --
179 -- Description : Function to return SQL for Payroll Processing --
180 -- Region --
181 -- --
182 -- Parameters : --
183 -- IN : p_assignment_action_id NUMBER --
184 -- --
185 --------------------------------------------------------------------------
186 FUNCTION get_period(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
187 RETURN LONG
188 IS
189 l_procedure VARCHAR2(50);
190 l_action_type VARCHAR2(2);
191
192 CURSOR periodDates IS
193 SELECT action_type FROM
194 pay_payroll_actions pa
195 ,per_time_periods tp
196 ,pay_assignment_actions aa
197 WHERE pa.payroll_action_id = aa.payroll_action_id
198 AND pa.effective_date = tp.regular_payment_date
199 AND pa.payroll_id = tp.payroll_id
200 AND aa.assignment_action_id = p_assignment_action_id;
201
202 BEGIN
203
204 l_procedure := g_package || 'get_period';
205 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
206
207 open periodDates;
208 fetch periodDates into l_action_type;
209 close periodDates;
210
211 if l_action_type is not null then
212 if l_action_type in ( 'P','U' ) then
213 g_sql :=
214 'select tp1.period_name || decode(tp2.period_name, tp1.period_name, null, '' - '' || tp2.period_name) COL01
215 ,fnd_date.date_to_displaydate(tp1.start_date) COL02
216 ,fnd_date.date_to_displaydate(pa2.effective_date) COL03
217 ,fnd_date.date_to_displaydate(tp2.end_date) COL04
218 ,fnd_date.date_to_displaydate(aa1.start_date) COL05
219 ,fnd_date.date_to_displaydate(aa2.end_date) COL06
220 ,tp1.period_type COL07
221 from pay_payroll_actions pa1
222 ,pay_payroll_actions pa2
223 ,per_time_periods tp1
224 ,per_time_periods tp2
225 ,pay_assignment_actions aa1
226 ,pay_assignment_actions aa2
227 where pa1.payroll_action_id = aa1.payroll_action_id
228 and pa1.effective_date = tp1.regular_payment_date
229 and pa1.payroll_id = tp1.payroll_id
230 and aa1.assignment_action_id = :PREPAY_MIN_ACTION
231 and pa2.payroll_action_id = aa2.payroll_action_id
232 and pa2.effective_date = tp2.regular_payment_date
233 and pa2.payroll_id = tp2.payroll_id
234 and aa2.assignment_action_id = :PREPAY_MAX_ACTION';
235 else
236 g_sql :=
237 'select tp.period_name COL01
238 ,fnd_date.date_to_displaydate(tp.end_date) COL04
239 ,fnd_date.date_to_displaydate(pa.effective_date) COL03
240 ,fnd_date.date_to_displaydate(aa.start_date) COL05
241 ,fnd_date.date_to_displaydate(aa.end_date) COL06
242 ,fnd_date.date_to_displaydate(tp.start_date) COL02
243 ,tp.period_type COL07
244 from pay_payroll_actions pa
245 ,per_time_periods tp
246 ,pay_assignment_actions aa
247 where pa.payroll_action_id = aa.payroll_action_id
248 and pa.effective_date = tp.regular_payment_date
249 and pa.payroll_id = tp.payroll_id
250 and aa.assignment_action_id = :assignment_action_id';
251 end if;
252 else
253 g_sql :=
254 'select tp.period_name COL01
255 ,fnd_date.date_to_displaydate(tp.end_date) COL04
256 ,fnd_date.date_to_displaydate(pa.effective_date) COL03
257 ,fnd_date.date_to_displaydate(aa.start_date) COL05
258 ,fnd_date.date_to_displaydate(aa.end_date) COL06
259 ,fnd_date.date_to_displaydate(tp.start_date) COL02
260 ,tp.period_type COL07
261 from pay_payroll_actions pa
262 ,per_time_periods tp
263 ,pay_assignment_actions aa
264 where pa.payroll_action_id = aa.payroll_action_id
265 and pa.time_period_id = tp.time_period_id
266 and aa.assignment_action_id = :assignment_action_id';
267 end if;
268 --
269
270 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
271
272 RETURN g_sql;
273
274 END get_period;
275
276
277 --------------------------------------------------------------------------
278 -- --
279 -- Name : GETELEMENTS --
280 -- Type : FUNCTION --
281 -- Access : Private --
282 -- Description : Function to return SQL for some regions in the SOE --
283 -- --
284 -- Parameters : --
285 -- IN : p_assignment_action_id NUMBER --
286 -- p_classification_name VARCHAR2 --
287 -- --
288 --------------------------------------------------------------------------
289 FUNCTION getelements(p_assignment_action_id IN NUMBER
290 ,p_classification_name IN VARCHAR2
291 ) RETURN LONG
292 IS
293 l_procedure VARCHAR2(50);
294
295 BEGIN
296
297 l_procedure := g_package || 'getelements';
298 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
299
300 IF (p_classification_name = 'Earnings') THEN
301 g_sql:=' SELECT DECODE(classification_name,
302 ''Earnings'', element_reporting_name,
303 ''Paid Monetary Perquisite'', SUBSTR(element_reporting_name, 0, LENGTH(element_reporting_name) - 8)) COL02
304 , TO_CHAR( DECODE(foreign_currency_code,NULL,amount,amount/exchange_rate)
305 , fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
306 FROM pay_in_asg_elements_v
307 WHERE assignment_action_id = ' || p_assignment_action_id || '
308 AND ( classification_name = ''' || p_classification_name || '''
309 OR classification_name = ''Paid Monetary Perquisite'')
310 AND amount <> 0
311 ORDER BY COL02';
312
313 ELSE
314 g_sql:='SELECT element_reporting_name COL02
315 , TO_CHAR( DECODE(foreign_currency_code,NULL,amount,amount/exchange_rate)
316 , fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
317 FROM pay_in_asg_elements_v
318 WHERE assignment_action_id = ' || p_assignment_action_id || '
319 AND classification_name = ''' || p_classification_name || '''
320 AND amount <> 0
321 ORDER BY element_reporting_name';
322 END IF;
323 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
324 RETURN g_sql;
325 --
326 END getelements;
327
328 --------------------------------------------------------------------------
329 -- --
330 -- Name : GET_EARNINGS --
331 -- Type : FUNCTION --
332 -- Access : Public --
333 -- Description : Function to return SQL for Earnings Region --
334 -- --
335 -- Parameters : --
336 -- IN : p_assignment_action_id NUMBER --
337 -- --
341 IS
338 --------------------------------------------------------------------------
339 FUNCTION get_earnings(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
340 RETURN LONG
342 l_procedure VARCHAR2(50);
343
344 BEGIN
345
346 l_procedure := g_package || 'get_earnings';
347 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
348
349 RETURN getElements(p_assignment_action_id => p_assignment_action_id
350 ,p_classification_name => 'Earnings'
351 );
352
353 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
354
355 END get_earnings;
356
357 --------------------------------------------------------------------------
358 -- --
359 -- Name : GET_DEDUCTIONS --
360 -- Type : FUNCTION --
361 -- Access : Public --
362 -- Description : Function to return SQL for Deductions Region --
363 -- --
364 -- Parameters : --
365 -- IN : p_assignment_action_id NUMBER --
366 -- --
367 --------------------------------------------------------------------------
368 FUNCTION get_deductions(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
369 RETURN LONG
370 IS
371 l_procedure VARCHAR2(50);
372
373 BEGIN
374
375 l_procedure := g_package || 'get_deductions';
376 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
377
378 RETURN getElements( p_assignment_action_id => p_assignment_action_id
379 , p_classification_name => 'Deductions'
380 );
381
382 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
383
384 END get_deductions;
385
386
387 --------------------------------------------------------------------------
388 -- --
389 -- Name : GET_ADVANCES --
390 -- Type : FUNCTION --
391 -- Access : Public --
392 -- Description : Function to return SQL for Advances Region --
393 -- --
394 -- Parameters : --
395 -- IN : p_assignment_action_id NUMBER --
396 -- --
397 --------------------------------------------------------------------------
398 FUNCTION get_advances(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
399 RETURN LONG
400 IS
401 l_procedure VARCHAR2(50);
402 BEGIN
403
404 l_procedure := g_package || 'get_advances';
405 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
406
407 RETURN getElements( p_assignment_action_id => p_assignment_action_id
408 , p_classification_name => 'Advances'
409 );
410
411 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
412
413 END get_advances;
414
415 --------------------------------------------------------------------------
416 -- --
417 -- Name : GET_FRINGE_BENEFITS --
418 -- Type : FUNCTION --
419 -- Access : Public --
420 -- Description : Function to return SQL for Fringe Benefits Region --
421 -- --
422 -- Parameters : --
423 -- IN : p_assignment_action_id NUMBER --
424 -- --
425 --------------------------------------------------------------------------
426 FUNCTION get_fringe_benefits(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
427 RETURN LONG
428 IS
429 l_procedure VARCHAR2(50);
430 BEGIN
431
432 l_procedure := g_package || 'get_fringe_benefits';
433 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
434
435 RETURN getElements( p_assignment_action_id => p_assignment_action_id
436 , p_classification_name => 'Fringe Benefits'
437 );
438
439 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
440
441 END get_fringe_benefits;
442
443
444 --------------------------------------------------------------------------
445 -- --
446 -- Name : GET_PERQUISITES --
447 -- Type : FUNCTION --
448 -- Access : Public --
449 -- Description : Function to return SQL for Perquisites Region --
453 -- --
450 -- --
451 -- Parameters : --
452 -- IN : p_assignment_action_id NUMBER --
454 --------------------------------------------------------------------------
455 FUNCTION get_perquisites(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
456 RETURN LONG
457 IS
458 l_procedure VARCHAR2(50);
459 BEGIN
460
461 l_procedure := g_package || 'get_perquisites';
462 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
463
464 RETURN getElements( p_assignment_action_id => p_assignment_action_id
465 , p_classification_name => 'Perquisites'
466 );
467
468 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
469
470 END get_perquisites;
471
472 --------------------------------------------------------------------------
473 -- --
474 -- Name : GET_EMPLOYER_CHARGES --
475 -- Type : FUNCTION --
476 -- Access : Public --
477 -- Description : Function to return SQL for Employer Charges --
478 -- Region --
479 -- --
480 -- Parameters : --
481 -- IN : p_assignment_action_id NUMBER --
482 -- --
483 --------------------------------------------------------------------------
484 FUNCTION get_employer_charges(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
485 RETURN LONG
486 IS
487 l_procedure VARCHAR2(50);
488 BEGIN
489
490 l_procedure := g_package || 'get_employer_charges';
491 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
492
493 RETURN getElements( p_assignment_action_id => p_assignment_action_id
494 , p_classification_name => 'Employer Charges'
495 );
496
497 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
498
499 END get_employer_charges;
500
501 --------------------------------------------------------------------------
502 -- --
503 -- Name : GET_TERM_PAYMENTS --
504 -- Type : FUNCTION --
505 -- Access : Public --
506 -- Description : Function to return SQL for Termination Payments --
507 -- Region --
508 -- --
509 -- Parameters : --
510 -- IN : p_assignment_action_id NUMBER --
511 -- --
512 --------------------------------------------------------------------------
513 FUNCTION get_term_payments(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
514 RETURN LONG
515 IS
516 l_procedure VARCHAR2(50);
517 BEGIN
518
519 l_procedure := g_package || 'get_term_payments';
520 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
521
522 RETURN getElements( p_assignment_action_id => p_assignment_action_id
523 , p_classification_name => 'Termination Payments'
524 );
525
526 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
527
528 END get_term_payments;
529
530 --------------------------------------------------------------------------
531 -- --
532 -- Name : GET_BALANCES --
533 -- Type : FUNCTION --
534 -- Access : Public --
535 -- Description : Function to return SQL for Balances Region --
536 -- --
537 -- Parameters : --
538 -- IN : p_assignment_action_id NUMBER --
539 -- --
540 --------------------------------------------------------------------------
541 FUNCTION get_balances( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
542 RETURN LONG
543 IS
544
545 CURSOR csr_get_tax_till_date
546 IS
547 SELECT SUM(pay_balance_pkg.get_value( pdb.defined_balance_id, p_assignment_action_id)) COL16
548 FROM pay_balance_types pbt
549 , pay_balance_dimensions pbd
550 , pay_defined_balances pdb
551 WHERE pbt.balance_name IN ( 'F16 Income Tax till Date'
552 , 'F16 Surcharge till Date'
553 , 'F16 Education Cess till Date'
554 , 'F16 Sec and HE Cess till Date')
558 AND pbd.balance_dimension_id = pdb.balance_dimension_id
555 AND pbd.dimension_name = '_ASG_PTD'
556 AND pbt.legislation_code = 'IN'
557 AND pbd.legislation_code = 'IN'
559 AND pbt.balance_type_id = pdb.balance_type_id;
560
561 l_procedure VARCHAR2(50);
562 l_tax_till_date NUMBER;
563
564 BEGIN
565
566 l_procedure := g_package || 'get_balances';
567 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
568
569 OPEN csr_get_tax_till_date;
570 FETCH csr_get_tax_till_date INTO l_tax_till_date;
571 CLOSE csr_get_tax_till_date;
572
573 g_sql := 'SELECT DECODE(pbt.balance_name,''F16 Salary Under Section 17'',''Salary (Section 17(1))'',
574 ''F16 Value of Perquisites'',''Value of Perquisites (Section 17(2))'',
575 ''F16 Profit in lieu of Salary'',''Profit in lieu of salary (Section 17(3))'',
576 ''F16 Allowances Exempt'',''Allowances under Sec 10''
577 ,SUBSTR(pbt.balance_name,5)) COL04
578 , DECODE(pbt.balance_name,''F16 Income Tax till Date'',
579 TO_CHAR('||l_tax_till_date||'
580 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)),
581 TO_CHAR(pay_balance_pkg.get_value( pdb.defined_balance_id,'|| p_assignment_action_id ||')
582 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40))) COL16
583 FROM pay_balance_types pbt
584 , pay_balance_dimensions pbd
585 , pay_defined_balances pdb
586 WHERE pbt.balance_name IN ( ''F16 Salary Under Section 17''
587 , ''F16 Value of Perquisites''
588 , ''F16 Profit in lieu of Salary''
589 , ''F16 Gross Salary''
590 , ''F16 Allowances Exempt''
591 , ''F16 Deductions under Sec 16''
592 , ''F16 Total Chapter VI A Deductions''
593 , ''F16 Total Income''
594 , ''F16 Tax on Total Income''
595 , ''F16 Total Tax payable''
596 , ''F16 Income Tax till Date''
597 , ''F16 Balance Tax'')
598 AND pbd.dimension_name = ''_ASG_PTD''
599 AND pbt.legislation_code = ''IN''
600 AND pbd.legislation_code = ''IN''
601 AND pbd.balance_dimension_id = pdb.balance_dimension_id
602 AND pbt.balance_type_id = pdb.balance_type_id
603 ORDER BY (DECODE (pbt.balance_name,''F16 Salary Under Section 17'',1
604 , ''F16 Value of Perquisites'',2
605 , ''F16 Profit in lieu of Salary'',3
606 , ''F16 Gross Salary'',4
607 , ''F16 Allowances Exempt'',5
608 , ''F16 Deductions under Sec 16'',6
609 , ''F16 Total Chapter VI A Deductions'',7
610 , ''F16 Total Income'',8
611 , ''F16 Tax on Total Income'',9
612 , ''F16 Total Tax payable'',10
613 , ''F16 Income Tax till Date'',11
614 , ''F16 Balance Tax'',12,999))';
615
616 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
617
618 RETURN g_sql;
619
620 END get_balances;
621
622 --------------------------------------------------------------------------
623 -- --
624 -- Name : GET_PAYMENT_DETAILS --
625 -- Type : FUNCTION --
626 -- Access : Public --
627 -- Description : Function to return SQL for Payment Details Region --
628 -- --
629 -- Parameters : --
630 -- IN : p_assignment_action_id NUMBER --
631 -- --
632 --------------------------------------------------------------------------
633 FUNCTION get_payment_details(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
634 RETURN LONG
635 IS
636
637 l_procedure VARCHAR2(50);
638
639 BEGIN
640
641 l_procedure := g_package || 'get_payment_details';
642 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
643
644 g_sql := 'SELECT org_payment_method_name COL01
645 , pay_soe_util.getBankDetails('':legislation_code''
646 ,NVL(ppm.external_account_id,opm.external_account_id)
647 ,''BANK_NAME''
648 ,NULL) || '','' ||
652 ,NULL) COL02
649 pay_soe_util.getBankDetails('':legislation_code''
650 ,NVL(ppm.external_account_id,opm.external_account_id)
651 ,''BANK_BRANCH''
653 , pay_soe_util.getBankDetails('':legislation_code''
654 ,NVL(ppm.external_account_id,opm.external_account_id)
655 ,''BANK_ACCOUNT_NUMBER''
656 ,NULL) COL03
657 , TO_CHAR(:G_CURRENCY_CODE) COL04
658 , pay_soe_util.getBankDetails('':legislation_code''
659 ,NVL(ppm.external_account_id,opm.external_account_id)
660 ,''BANK_BRANCH''
661 ,NULL) COL05
662 , to_char(pp.value
663 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE
664 ,40)
665 ) COL16
666 FROM pay_pre_payments pp
667 , pay_personal_payment_methods_f ppm
668 , pay_org_payment_methods_f opm
669 , pay_payment_types_tl pt
670 WHERE pp.assignment_action_id IN
671 (SELECT ai.locking_action_id
672 FROM pay_action_interlocks ai
673 WHERE ai.locked_action_id :action_clause)
674 AND pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
675 AND :effective_date BETWEEN ppm.effective_start_date(+)
676 AND ppm.effective_end_date(+)
677 AND pp.org_payment_method_id = opm.org_payment_method_id
678 AND :effective_date BETWEEN opm.effective_start_date
679 AND opm.effective_end_date
680 AND opm.payment_type_id = pt.payment_type_id
681 AND pt.language = USERENV(''LANG'')';
682 --
683 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
684
685 RETURN g_sql;
686
687 END get_payment_details;
688
689 --------------------------------------------------------------------------
690 -- --
691 -- Name : GET_OTHER_ELEMENT_INFORMATION --
692 -- Type : FUNCTION --
693 -- Access : Public --
694 -- Description : Function to return SQL for Other Element --
695 -- Information Region --
696 -- --
697 -- Parameters : --
698 -- IN : p_assignment_action_id NUMBER --
699 -- --
700 --------------------------------------------------------------------------
701 FUNCTION get_other_element_information(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
702 RETURN LONG
703 IS
704
705 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
706 l_business_group_id per_business_groups.business_group_id%TYPE;
707 l_procedure VARCHAR2(100);
708
709 CURSOR csr_prepayment
710 IS
711 SELECT MAX(locked_action_id)
712 FROM pay_action_interlocks
713 WHERE locking_action_id = p_assignment_action_id;
714
715 CURSOR csr_get_bg_id
716 IS
717 SELECT ppa.business_group_id
718 FROM pay_payroll_actions ppa
719 ,pay_assignment_actions paa
720 WHERE ppa.payroll_action_id = paa.payroll_action_id
721 AND paa.assignment_action_id = p_assignment_action_id;
722
723
724 BEGIN
725
726 l_procedure := g_package || 'get_other_element_information';
727 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
728
729 OPEN csr_prepayment;
730 FETCH csr_prepayment INTO l_assignment_action_id;
731 CLOSE csr_prepayment;
732
733 IF l_assignment_action_id IS NOT NULL THEN
734 p_assignment_action_id := l_assignment_action_id;
735 END IF;
736
737 OPEN csr_get_bg_id;
738 FETCH csr_get_bg_id INTO l_business_group_id;
739 CLOSE csr_get_bg_id;
740
741 g_sql :=
742 'SELECT org.org_information7 COL02
743 , prv.result_value COL16
744 FROM pay_run_result_values prv,
745 pay_run_results prr,
746 hr_organization_information_v org
747 WHERE prr.status IN (''P'',''PA'')
748 AND org.organization_id = ' || l_business_group_id || '
749 AND org.org_information_context = ''Business Group:SOE Detail''
750 AND org.org_information1 = ''ELEMENT''
751 AND prv.run_result_id = prr.run_result_id
752 AND prr.assignment_action_id = ' || p_assignment_action_id || '
753 AND prr.element_type_id = org.org_information2
754 AND prv.input_value_id = org.org_information3
758 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
755 AND prv.result_value IS NOT NULL
756 ORDER BY org.org_information7';
757
759
760 RETURN g_sql;
761
762 END get_other_element_information;
763
764 --------------------------------------------------------------------------
765 -- --
766 -- Name : GET_OTHER_BALANCE_INFORMATION --
767 -- Type : FUNCTION --
768 -- Access : Public --
769 -- Description : Function to return SQL for Other Balance --
770 -- Information Region --
771 -- --
772 -- Parameters : --
773 -- IN : p_assignment_action_id NUMBER --
774 -- --
775 --------------------------------------------------------------------------
776 FUNCTION get_other_balance_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
777 RETURN LONG
778 IS
779
780 l_date_earned DATE;
781 l_tax_unit_id NUMBER;
782 l_business_group_id per_business_groups.business_group_id%TYPE;
783 l_procedure VARCHAR2(100);
784
785 CURSOR csr_get_date_earned
786 IS
787 SELECT ppa.date_earned, ppa.business_group_id
788 FROM pay_payroll_actions ppa
789 , pay_assignment_actions paa
790 WHERE ppa.payroll_action_id = paa.payroll_action_id
791 AND paa.assignment_action_id = p_assignment_action_id;
792
793 CURSOR csr_get_tax_unit_id
794 IS
795 SELECT hsck.segment1
796 FROM hr_soft_coding_keyflex hsck
797 , per_assignments_f paf
798 , pay_assignment_actions paa
799 , pay_payroll_actions ppa
800 WHERE hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
801 AND paa.assignment_action_id = p_assignment_action_id
802 AND paa.payroll_action_id = ppa.payroll_action_id
803 AND paf.assignment_id = paa.assignment_id
804 AND ppa.effective_date BETWEEN paf.effective_start_date
805 AND paf.effective_end_date;
806
807 BEGIN
808
809 l_procedure := g_package || 'get_other_balance_information';
810 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
811
812 OPEN csr_get_date_earned;
813 FETCH csr_get_date_earned INTO l_date_earned, l_business_group_id;
814 CLOSE csr_get_date_earned;
815
816 OPEN csr_get_tax_unit_id;
817 FETCH csr_get_tax_unit_id INTO l_tax_unit_id;
818 CLOSE csr_get_tax_unit_id;
819
820 g_sql := 'SELECT org.org_information7 COL02
821 , TO_CHAR(pay_balance_pkg.get_value( pdb.defined_balance_id
822 , ' || p_assignment_action_id || ')
823 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
824 FROM pay_defined_balances pdb
825 , hr_organization_information_v org
826 WHERE org.organization_id = ' || l_business_group_id || '
827 AND org.org_information_context = ''Business Group:SOE Detail''
828 AND org.org_information1 = ''BALANCE''
829 AND pdb.balance_type_id = org.org_information4
830 AND pdb.balance_dimension_id = org.org_information5';
831
832 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
833
834 RETURN g_sql;
835
836 END get_other_balance_information;
837
838 --------------------------------------------------------------------------
839 -- --
840 -- Name : GET_ANNUAL_LEAVE_INFORMATION --
841 -- Type : FUNCTION --
842 -- Access : Public --
843 -- Description : Function to return SQL for Annual Leave --
844 -- Information Region --
845 -- --
846 -- Parameters : --
847 -- IN : p_assignment_action_id NUMBER --
848 -- --
849 --------------------------------------------------------------------------
850 FUNCTION get_annual_leave_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
851 RETURN LONG
852 IS
853
854 CURSOR csr_get_annual_leave_details(p_payroll_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
855 IS
856 SELECT pap.accrual_plan_name
857 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
858 ,hr_general.decode_lookup('HOURS_OR_DAYS',pap.accrual_units_of_measure)
859 ,ppa.payroll_id
860 ,pap.business_group_id
861 ,pap.accrual_plan_id
862 ,paa.assignment_id
863 FROM pay_accrual_plans pap
864 ,pay_element_types_f pet
868 ,pay_payroll_actions ppa
865 ,pay_element_links_f pel
866 ,pay_element_entries_f pee
867 ,pay_assignment_actions paa
869 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
870 AND pel.element_type_id = pet.element_type_id
871 AND pee.element_link_id = pel.element_link_id
872 AND paa.assignment_id = pee.assignment_id
873 AND ppa.payroll_action_id = paa.payroll_action_id
874 AND ppa.action_type IN ('R','Q')
875 AND ppa.action_status = 'C'
876 AND ppa.date_earned BETWEEN pet.effective_start_date
877 AND pet.effective_end_date
878 AND ppa.date_earned BETWEEN pel.effective_start_date
879 AND pel.effective_end_date
880 AND ppa.date_earned BETWEEN pee.effective_start_date
881 AND pee.effective_end_date
882 AND paa.assignment_action_id = p_payroll_assignment_action_id;
883
884 CURSOR csr_get_date_earned
885 IS
886 SELECT ppa.date_earned
887 FROM pay_payroll_actions ppa
888 , pay_assignment_actions paa
889 WHERE ppa.payroll_action_id = paa.payroll_action_id
890 AND paa.assignment_action_id = p_assignment_action_id;
891
892 CURSOR csr_prepayment
893 IS
894 SELECT MAX(locked_action_id)
895 FROM pay_action_interlocks
896 WHERE locking_action_id = p_assignment_action_id;
897
898 l_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
899 l_accrual_category pay_accrual_plans.accrual_category%TYPE;
900 l_uom pay_accrual_plans.accrual_units_of_measure%TYPE;
901 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
902 l_business_group_id per_business_groups.business_group_id%TYPE;
903 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE;
904 l_assignment_id per_assignments_f.assignment_id%TYPE;
905 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
906 l_annual_leave_balance NUMBER;
907 l_ovn NUMBER;
908 l_leave_taken NUMBER;
909 l_start_date DATE;
910 l_end_date DATE;
911 l_accrual_end_date DATE;
912 l_date_earned DATE;
913 l_accrual NUMBER;
914 l_total_leave_taken NUMBER;
915 l_procedure VARCHAR2(100);
916
917 BEGIN
918
919 l_procedure := g_package || 'get_annual_leave_information';
920 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
921
922 OPEN csr_prepayment;
923 FETCH csr_prepayment INTO l_assignment_action_id;
924 CLOSE csr_prepayment;
925
926 IF l_assignment_action_id IS NULL THEN
927 l_assignment_action_id := p_assignment_action_id;
928 END IF;
929
930 OPEN csr_get_annual_leave_details(l_assignment_action_id);
931 FETCH csr_get_annual_leave_details
932 INTO l_plan_name
933 , l_accrual_category
934 , l_uom
935 , l_payroll_id
936 , l_business_group_id
937 , l_accrual_plan_id
938 , l_assignment_id;
939
940 /* Bug 6914353 - Added check to call accrual calculations function
941 only if Accrual Plan ID is available */
942 IF csr_get_annual_leave_details%FOUND
943 THEN
944
945 CLOSE csr_get_annual_leave_details;
946
947 OPEN csr_get_date_earned;
948 FETCH csr_get_date_earned INTO l_date_earned;
949 CLOSE csr_get_date_earned;
950
951
952 per_accrual_calc_functions.get_net_accrual
953 ( p_assignment_id => l_assignment_id
954 , p_plan_id => l_accrual_plan_id
955 , p_payroll_id => l_payroll_id
956 , p_business_group_id => l_business_group_id
957 , p_calculation_date => l_date_earned
958 , p_start_date => l_start_date
959 , p_end_date => l_end_date
960 , p_accrual_end_date => l_accrual_end_date
961 , p_accrual => l_accrual
962 , p_net_entitlement => l_annual_leave_balance
963 );
964
965 g_sql := 'SELECT ''' || l_plan_name || ''' COL01
966 , ''' || l_uom || ''' COL02
967 , TO_CHAR(' || l_annual_leave_balance|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
968 FROM DUAL';
969
970 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
971 ELSE
972 CLOSE csr_get_annual_leave_details;
973 END IF;
974
975 RETURN g_sql;
976
977 END get_annual_leave_information;
978
979 --------------------------------------------------------------------------
980 -- --
981 -- Name : GET_LEAVE_TAKEN --
982 -- Type : FUNCTION --
983 -- Access : Public --
984 -- Description : Function to return SQL for Leave Taken Region --
985 -- --
986 -- Parameters : --
990 FUNCTION get_leave_taken(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
987 -- IN : p_assignment_action_id NUMBER --
988 -- --
989 --------------------------------------------------------------------------
991 RETURN LONG
992 IS
993
994 CURSOR csr_prepayment
995 IS
996 SELECT MAX(locked_action_id)
997 FROM pay_action_interlocks
998 WHERE locking_action_id = p_assignment_action_id;
999
1000 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1001 l_procedure VARCHAR2(50);
1002
1003 BEGIN
1004
1005 l_procedure := g_package || 'get_leave_taken';
1006 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
1007
1008 OPEN csr_prepayment;
1009 FETCH csr_prepayment INTO l_assignment_action_id;
1010 CLOSE csr_prepayment;
1011
1012 IF l_assignment_action_id IS NOT NULL THEN
1013 p_assignment_action_id := l_assignment_action_id;
1014 END IF;
1015
1016
1017 g_sql :=
1018 ' SELECT pet.reporting_name COL01
1019 ,TO_CHAR(decode(pet.processing_type,''R'',greatest(pab.date_start,PTP.START_DATE),pab.date_start),''DD-Mon-YYYY'') COL02
1020 ,TO_CHAR(decode(pet.processing_type,''R'',least(pab.date_end,PTP.END_DATE),pab.date_end),''DD-Mon-YYYY'') COL03
1021 ,TO_CHAR(decode(pet.processing_type,''R'',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours))
1022 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
1023 FROM pay_assignment_actions paa
1024 ,pay_payroll_actions ppa
1025 ,pay_run_results prr
1026 ,pay_run_result_values prrv
1027 ,per_time_periods ptp
1028 ,pay_element_types_f pet
1029 ,pay_input_values_f piv
1030 ,pay_element_entries_f pee
1031 ,per_absence_attendance_types pat
1032 ,per_absence_attendances pab
1033 WHERE paa.assignment_action_id = ' || p_assignment_action_id || '
1034 AND ppa.payroll_action_id = paa.payroll_action_id
1035 AND ppa.action_type IN (''Q'',''R'')
1036 AND ptp.time_period_id = ppa.time_period_id
1037 AND paa.assignment_action_id = prr.assignment_action_id
1038 AND pet.element_type_id = prr.element_type_id
1039 AND pet.element_type_id = piv.element_type_id
1040 AND piv.input_value_id = pat.input_value_id
1041 AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
1042 AND pab.absence_attendance_id = pee.creator_id
1043 AND pee.creator_type = ''A''
1044 AND pee.assignment_id = paa.assignment_id
1045 AND pee.element_entry_id = prr.source_id
1046 AND piv.input_value_id = prrv.input_value_id
1047 AND prr.run_result_id = prrv.run_result_id
1048 AND ppa.effective_date BETWEEN pet.effective_start_date
1049 AND pet.effective_end_date
1050 AND ppa.effective_date BETWEEN pee.effective_start_date
1051 AND pee.effective_end_date
1052 AND ppa.effective_date BETWEEN piv.effective_start_date
1053 AND piv.effective_end_date';
1054
1055 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
1056
1057 RETURN g_sql;
1058
1059 END get_leave_taken;
1060
1061 --------------------------------------------------------------------------
1062 -- --
1063 -- Name : GET_MESSAGES --
1064 -- Type : FUNCTION --
1065 -- Access : Public --
1066 -- Description : Function to return SQL for Messages Region --
1067 -- --
1068 -- Parameters : --
1069 -- IN : p_assignment_action_id NUMBER --
1070 -- --
1071 --------------------------------------------------------------------------
1072 FUNCTION get_messages(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1073 RETURN LONG
1074 IS
1075
1076 CURSOR csr_prepayment
1077 IS
1078 SELECT MAX(locked_action_id)
1079 FROM pay_action_interlocks
1080 WHERE locking_action_id = p_assignment_action_id;
1081
1082 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1083 l_procedure VARCHAR2(50);
1084
1085 BEGIN
1086
1087 l_procedure := g_package || 'get_messages';
1088
1089 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
1090
1091 OPEN csr_prepayment;
1092 FETCH csr_prepayment INTO l_assignment_action_id;
1093 CLOSE csr_prepayment;
1094
1095 IF l_assignment_action_id IS NULL THEN
1096 l_assignment_action_id := p_assignment_action_id;
1097 END IF;
1098
1099 g_sql := 'SELECT ppa.pay_advice_message COL01
1100 FROM pay_payroll_actions ppa
1101 , pay_assignment_actions paa
1102 WHERE ppa.payroll_action_id = paa.payroll_action_id
1103 AND paa.assignment_action_id = ' || l_assignment_action_id;
1104
1105 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
1106
1107 RETURN g_sql;
1108
1109 END get_messages;
1110
1111 END pay_in_soe_pkg;