[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_SOE_PKG
Source
1 PACKAGE BODY pay_in_soe_pkg AS
2 /* $Header: pyinsoer.pkb 120.3.12020000.4 2013/02/07 18:32:20 pthummal 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 -- --
338 --------------------------------------------------------------------------
339 FUNCTION get_earnings(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
340 RETURN LONG
341 IS
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 --
450 -- --
451 -- Parameters : --
452 -- IN : p_assignment_action_id NUMBER --
453 -- --
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')
555 AND pbd.dimension_name = '_ASG_DE_PTD'
556 AND pbt.legislation_code = 'IN'
557 AND pbd.legislation_code = 'IN'
558 AND pbd.balance_dimension_id = pdb.balance_dimension_id
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_DE_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) || '','' ||
649 pay_soe_util.getBankDetails('':legislation_code''
650 ,NVL(ppm.external_account_id,opm.external_account_id)
651 ,''BANK_BRANCH''
652 ,NULL) COL02
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
755 AND prv.result_value IS NOT NULL
756 ORDER BY org.org_information7';
757
758 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
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 /* Bug 13475048 - Added ORDER BY clause */
821
822 g_sql := 'SELECT org.org_information7 COL02
823 , TO_CHAR(pay_balance_pkg.get_value( pdb.defined_balance_id
824 , ' || p_assignment_action_id || ')
825 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
826 FROM pay_defined_balances pdb
827 , hr_organization_information_v org
828 WHERE org.organization_id = ' || l_business_group_id || '
829 AND org.org_information_context = ''Business Group:SOE Detail''
830 AND org.org_information1 = ''BALANCE''
831 AND pdb.balance_type_id = org.org_information4
832 AND pdb.balance_dimension_id = org.org_information5
833 ORDER BY lpad(org.org_information8,4,0)'; /* Bug 13638866 - Added lpad in ORDER BY clause */
834
835 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
836
837 RETURN g_sql;
838
839 END get_other_balance_information;
840
841 --------------------------------------------------------------------------
842 -- --
843 -- Name : GET_ANNUAL_LEAVE_INFORMATION --
844 -- Type : FUNCTION --
845 -- Access : Public --
846 -- Description : Function to return SQL for Annual Leave --
847 -- Information Region --
848 -- --
849 -- Parameters : --
850 -- IN : p_assignment_action_id NUMBER --
851 -- --
852 --------------------------------------------------------------------------
853 FUNCTION get_annual_leave_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
854 RETURN LONG
855 IS
856
857 CURSOR csr_get_annual_leave_details(p_payroll_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
858 IS
859 SELECT pap.accrual_plan_name
860 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
861 ,hr_general.decode_lookup('HOURS_OR_DAYS',pap.accrual_units_of_measure)
862 ,ppa.payroll_id
863 ,pap.business_group_id
864 ,pap.accrual_plan_id
865 ,paa.assignment_id
866 FROM pay_accrual_plans pap
867 ,pay_element_types_f pet
868 ,pay_element_links_f pel
869 ,pay_element_entries_f pee
870 ,pay_assignment_actions paa
871 ,pay_payroll_actions ppa
872 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
873 AND pel.element_type_id = pet.element_type_id
874 AND pee.element_link_id = pel.element_link_id
875 AND paa.assignment_id = pee.assignment_id
876 AND ppa.payroll_action_id = paa.payroll_action_id
877 AND ppa.action_type IN ('R','Q')
878 AND ppa.action_status = 'C'
879 AND ppa.date_earned BETWEEN pet.effective_start_date
880 AND pet.effective_end_date
881 AND ppa.date_earned BETWEEN pel.effective_start_date
882 AND pel.effective_end_date
883 AND ppa.date_earned BETWEEN pee.effective_start_date
884 AND pee.effective_end_date
885 AND paa.assignment_action_id = p_payroll_assignment_action_id;
886
887 CURSOR csr_get_date_earned
888 IS
889 SELECT ppa.date_earned
890 FROM pay_payroll_actions ppa
891 , pay_assignment_actions paa
892 WHERE ppa.payroll_action_id = paa.payroll_action_id
893 AND paa.assignment_action_id = p_assignment_action_id;
894
895 CURSOR csr_prepayment
896 IS
897 SELECT MAX(locked_action_id)
898 FROM pay_action_interlocks
899 WHERE locking_action_id = p_assignment_action_id;
900
901 l_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
902 l_accrual_category pay_accrual_plans.accrual_category%TYPE;
903 l_uom pay_accrual_plans.accrual_units_of_measure%TYPE;
904 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
905 l_business_group_id per_business_groups.business_group_id%TYPE;
906 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE;
907 l_assignment_id per_assignments_f.assignment_id%TYPE;
908 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
909 l_annual_leave_balance NUMBER;
910 l_ovn NUMBER;
911 l_leave_taken NUMBER;
912 l_start_date DATE;
913 l_end_date DATE;
914 l_accrual_end_date DATE;
915 l_date_earned DATE;
916 l_accrual NUMBER;
917 l_total_leave_taken NUMBER;
918 l_procedure VARCHAR2(100);
919
920 BEGIN
921
922 l_procedure := g_package || 'get_annual_leave_information';
923 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
924
925 OPEN csr_prepayment;
926 FETCH csr_prepayment INTO l_assignment_action_id;
927 CLOSE csr_prepayment;
928
929 IF l_assignment_action_id IS NULL THEN
930 l_assignment_action_id := p_assignment_action_id;
931 END IF;
932
933 OPEN csr_get_date_earned;
934 FETCH csr_get_date_earned INTO l_date_earned;
935 CLOSE csr_get_date_earned;
936
937 g_sql := NULL;
938 OPEN csr_get_annual_leave_details(l_assignment_action_id);
939 LOOP
940 FETCH csr_get_annual_leave_details
941 INTO l_plan_name
942 , l_accrual_category
943 , l_uom
944 , l_payroll_id
945 , l_business_group_id
946 , l_accrual_plan_id
947 , l_assignment_id;
948
949 /* Bug 6914353 - Added check to call accrual calculations function
950 only if Accrual Plan ID is available */
951
952
953
954 EXIT WHEN csr_get_annual_leave_details%NOTFOUND;
955
956 per_accrual_calc_functions.get_net_accrual
957 ( p_assignment_id => l_assignment_id
958 , p_plan_id => l_accrual_plan_id
959 , p_payroll_id => l_payroll_id
960 , p_business_group_id => l_business_group_id
961 , p_calculation_date => l_date_earned
962 , p_start_date => l_start_date
963 , p_end_date => l_end_date
964 , p_accrual_end_date => l_accrual_end_date
965 , p_accrual => l_accrual
966 , p_net_entitlement => l_annual_leave_balance
967 );
968
969 /* Bug 13021890 - To display multiple accrual plans */
970
971 IF g_sql IS NOT NULL THEN
972
973 g_sql := g_sql ||' '||'UNION' ||' '|| 'SELECT ''' || l_plan_name || ''' COL01
974 , ''' || l_uom || ''' COL02
975 , TO_CHAR(' || l_annual_leave_balance|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
976 FROM DUAL';
977
978 ELSE
979 g_sql := 'SELECT ''' || l_plan_name || ''' COL01
980 , ''' || l_uom || ''' COL02
981 , TO_CHAR(' || l_annual_leave_balance|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
982 FROM DUAL';
983
984
985 END IF;
986
987 /* End of Bug 13021890 - To display multiple accrual plans */
988
989 pay_in_utils.set_location (g_debug, 'Leaving ' || l_procedure,20);
990
991 END LOOP;
992
993 CLOSE csr_get_annual_leave_details;
994
995 RETURN g_sql;
996
997 END get_annual_leave_information;
998
999 --------------------------------------------------------------------------
1000 -- --
1001 -- Name : GET_LEAVE_TAKEN --
1002 -- Type : FUNCTION --
1003 -- Access : Public --
1004 -- Description : Function to return SQL for Leave Taken Region --
1005 -- --
1006 -- Parameters : --
1007 -- IN : p_assignment_action_id NUMBER --
1008 -- --
1009 --------------------------------------------------------------------------
1010 FUNCTION get_leave_taken(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
1011 RETURN LONG
1012 IS
1013
1014 CURSOR csr_prepayment
1015 IS
1016 SELECT MAX(locked_action_id)
1017 FROM pay_action_interlocks
1018 WHERE locking_action_id = p_assignment_action_id;
1019
1020 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1021 l_procedure VARCHAR2(50);
1022
1023 BEGIN
1024
1025 l_procedure := g_package || 'get_leave_taken';
1026 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
1027
1028 OPEN csr_prepayment;
1029 FETCH csr_prepayment INTO l_assignment_action_id;
1030 CLOSE csr_prepayment;
1031
1032 IF l_assignment_action_id IS NOT NULL THEN
1033 p_assignment_action_id := l_assignment_action_id;
1034 END IF;
1035
1036
1037 g_sql :=
1038 ' SELECT pet.reporting_name COL01
1039 ,TO_CHAR(decode(pet.processing_type,''R'',greatest(pab.date_start,PTP.START_DATE),pab.date_start),''DD-Mon-YYYY'') COL02
1040 ,TO_CHAR(decode(pet.processing_type,''R'',least(pab.date_end,PTP.END_DATE),pab.date_end),''DD-Mon-YYYY'') COL03
1041 ,TO_CHAR(decode(pet.processing_type,''R'',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours))
1042 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
1043 FROM pay_assignment_actions paa
1044 ,pay_payroll_actions ppa
1045 ,pay_run_results prr
1046 ,pay_run_result_values prrv
1047 ,per_time_periods ptp
1048 ,pay_element_types_f pet
1049 ,pay_input_values_f piv
1050 ,pay_element_entries_f pee
1051 ,per_absence_attendance_types pat
1052 ,per_absence_attendances pab
1053 WHERE paa.assignment_action_id = ' || p_assignment_action_id || '
1054 AND ppa.payroll_action_id = paa.payroll_action_id
1055 AND ppa.action_type IN (''Q'',''R'')
1056 AND ptp.time_period_id = ppa.time_period_id
1057 AND paa.assignment_action_id = prr.assignment_action_id
1058 AND pet.element_type_id = prr.element_type_id
1059 AND pet.element_type_id = piv.element_type_id
1060 AND piv.input_value_id = pat.input_value_id
1061 AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
1062 AND pab.absence_attendance_id = pee.creator_id
1063 AND pee.creator_type = ''A''
1064 AND pee.assignment_id = paa.assignment_id
1065 AND pee.element_entry_id = prr.source_id
1066 AND piv.input_value_id = prrv.input_value_id
1067 AND prr.run_result_id = prrv.run_result_id
1068 AND ppa.effective_date BETWEEN pet.effective_start_date
1069 AND pet.effective_end_date
1070 AND ppa.effective_date BETWEEN pee.effective_start_date
1071 AND pee.effective_end_date
1072 AND ppa.effective_date BETWEEN piv.effective_start_date
1073 AND piv.effective_end_date';
1074
1075 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
1076
1077 RETURN g_sql;
1078
1079 END get_leave_taken;
1080
1081 --------------------------------------------------------------------------
1082 -- --
1083 -- Name : GET_MESSAGES --
1084 -- Type : FUNCTION --
1085 -- Access : Public --
1086 -- Description : Function to return SQL for Messages Region --
1087 -- --
1088 -- Parameters : --
1089 -- IN : p_assignment_action_id NUMBER --
1090 -- --
1091 --------------------------------------------------------------------------
1092 FUNCTION get_messages(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1093 RETURN LONG
1094 IS
1095
1096 CURSOR csr_prepayment
1097 IS
1098 SELECT MAX(locked_action_id)
1099 FROM pay_action_interlocks
1100 WHERE locking_action_id = p_assignment_action_id;
1101
1102 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1103 l_procedure VARCHAR2(50);
1104
1105 BEGIN
1106
1107 l_procedure := g_package || 'get_messages';
1108
1109 pay_in_utils.set_location (g_debug,'Entering ' || l_procedure,10);
1110
1111 OPEN csr_prepayment;
1112 FETCH csr_prepayment INTO l_assignment_action_id;
1113 CLOSE csr_prepayment;
1114
1115 IF l_assignment_action_id IS NULL THEN
1116 l_assignment_action_id := p_assignment_action_id;
1117 END IF;
1118
1119 g_sql := 'SELECT ppa.pay_advice_message COL01
1120 FROM pay_payroll_actions ppa
1121 , pay_assignment_actions paa
1122 WHERE ppa.payroll_action_id = paa.payroll_action_id
1123 AND paa.assignment_action_id = ' || l_assignment_action_id;
1124
1125 pay_in_utils.set_location (g_debug,'Leaving ' || l_procedure,20);
1126
1127 RETURN g_sql;
1128
1129 END get_messages;
1130
1131 END pay_in_soe_pkg;