[Home] [Help]
PACKAGE BODY: APPS.PAY_CN_SOE
Source
1 PACKAGE BODY pay_cn_soe AS
2 /* $Header: pycnsoe.pkb 120.10.12020000.3 2013/02/11 06:30:44 mdubasi ship $ */
3
4 g_package CONSTANT VARCHAR2(100) := 'pay_cn_soe.';
5 g_debug BOOLEAN;
6 g_sql LONG;
7
8 --------------------------------------------------------------------------
9 -- --
10 -- Name : GETELEMENTS --
11 -- Type : FUNCTION --
12 -- Access : Private --
13 -- Description : Function to return SQL for for regions of SOE --
14 -- --
15 -- Parameters : --
16 -- IN : p_assignment_action_id NUMBER --
17 -- p_classification_name VARCHAR2 --
18 -- p_desc_column VARCHAR2 --
19 -- p_pay_column VARCHAR2 --
20 -- --
21 --------------------------------------------------------------------------
22 FUNCTION getelements(p_assignment_action_id IN NUMBER
23 ,p_classification_name IN VARCHAR2
24 ) RETURN LONG
25 IS
26 l_procedure VARCHAR2(50);
27 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
28
29 CURSOR csr_prepayments_action_id
30 IS
31 SELECT locking_action_id
32 FROM pay_action_interlocks
33 WHERE locked_action_id = p_assignment_action_id;
34 BEGIN
35
36 l_procedure := 'getelements';
37 hr_cn_api.set_location (g_debug,'Entering ' || l_procedure,10);
38
39 OPEN csr_prepayments_action_id;
40 FETCH csr_prepayments_action_id INTO l_assignment_action_id;
41 CLOSE csr_prepayments_action_id;
42
43 IF l_assignment_action_id IS NULL THEN
44 l_assignment_action_id := p_assignment_action_id;
45 END IF;
46
47 g_sql:='SELECT element_reporting_name COL02
48 , TO_CHAR( DECODE(foreign_currency_code,NULL,amount,amount/exchange_rate)
49 , fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
50 FROM pay_cn_asg_elements_v
51 WHERE assignment_action_id = ' || l_assignment_action_id || '
52 AND classification_name = ''' || p_classification_name || '''';
53
54 hr_cn_api.set_location (g_debug,'Leaving ' || l_procedure,10);
55
56 RETURN g_sql;
57
58 END getelements;
59
60 --------------------------------------------------------------------------
61 -- --
62 -- Name : GET_TAXABLE_EARNINGS --
63 -- Type : FUNCTION --
64 -- Access : Public --
65 -- Description : Function to return SQL for Earnings Region --
66 -- --
67 -- Parameters : --
68 -- IN : p_assignment_action_id NUMBER --
69 -- --
70 --------------------------------------------------------------------------
71 FUNCTION get_taxable_earnings(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
72 RETURN LONG
73 IS
74 l_procedure VARCHAR2(50);
75 BEGIN
76
77 l_procedure := 'get_taxable_earnings';
78 hr_cn_api.set_location (g_debug,'Entering ' || l_procedure,10);
79
80 RETURN getElements(p_assignment_action_id => p_assignment_action_id
81 ,p_classification_name => 'Taxable Earnings'
82 );
83
84 END get_taxable_earnings;
85
86 --------------------------------------------------------------------------
87 -- --
88 -- Name : GET_NON_TAXABLE_EARNINGS --
89 -- Type : FUNCTION --
90 -- Access : Public --
91 -- Description : Function to return SQL for Non Taxable Earnings --
92 -- Region --
93 -- --
94 -- Parameters : --
95 -- IN : p_assignment_action_id NUMBER --
96 -- --
97 --------------------------------------------------------------------------
98 FUNCTION get_non_taxable_earnings(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
99 RETURN LONG
100 IS
101 l_procedure VARCHAR2(50);
102 BEGIN
103
104 l_procedure := 'get_non_taxable_earnings';
105 hr_cn_api.set_location (g_debug,'Entering ' || l_procedure,10);
106
107 RETURN getElements( p_assignment_action_id => p_assignment_action_id
108 , p_classification_name => 'Non Taxable Earnings'
109 );
110
111 END get_non_taxable_earnings;
112
113 --------------------------------------------------------------------------
114 -- --
115 -- Name : get_statutory_deductions --
116 -- Type : FUNCTION --
117 -- Access : Public --
118 -- Description : Function to return SQL for Statutory Deductions --
119 -- Region --
120 -- --
121 -- Parameters : --
122 -- IN : p_assignment_action_id NUMBER --
123 -- --
124 --------------------------------------------------------------------------
125 FUNCTION get_statutory_deductions(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
126 RETURN LONG
127 IS
128 l_procedure VARCHAR2(50);
129 BEGIN
130
131 l_procedure := 'get_statutory_deductions';
132 hr_cn_api.set_location (g_debug,'Entering ' || l_procedure,10);
133
134 RETURN getElements( p_assignment_action_id => p_assignment_action_id
135 , p_classification_name => 'Statutory Deductions'
136 );
137
138 END get_statutory_deductions;
139
140 --------------------------------------------------------------------------
141 -- --
142 -- Name : get_non_statutory_deductions --
143 -- Type : FUNCTION --
144 -- Access : Public --
145 -- Description : Function to return SQL for Statutory Deductions --
146 -- Region --
147 -- --
148 -- Parameters : --
149 -- IN : p_assignment_action_id NUMBER --
150 -- --
151 --------------------------------------------------------------------------
152 FUNCTION get_non_statutory_deductions(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
153 RETURN LONG
154 IS
155 l_procedure VARCHAR2(50);
156 BEGIN
157
158 l_procedure := 'get_non_statutory_deductions';
159 hr_cn_api.set_location (g_debug,'Entering ' || l_procedure,10);
160
161 RETURN getElements( p_assignment_action_id => p_assignment_action_id
162 , p_classification_name => 'Pre Tax Non Statutory Deductions'
163 );
164
165 END get_non_statutory_deductions;
166
167 --------------------------------------------------------------------------
168 -- --
169 -- Name : GET_VOLUNTARY_DEDUCTIONS --
170 -- Type : FUNCTION --
171 -- Access : Public --
172 -- Description : Function to return SQL for Voluntary Deductions --
173 -- Region --
174 -- --
175 -- Parameters : --
176 -- IN : p_assignment_action_id NUMBER --
177 -- --
178 --------------------------------------------------------------------------
179 FUNCTION get_voluntary_deductions(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
180 RETURN LONG
181 IS
182 l_procedure VARCHAR2(50);
183 BEGIN
184
185 l_procedure := 'get_voluntary_deductions';
186 hr_cn_api.set_location (g_debug,'Entering ' || l_procedure,10);
187
188 RETURN getElements( p_assignment_action_id => p_assignment_action_id
189 , p_classification_name => 'Voluntary Dedn'
190 );
191
192 END get_voluntary_deductions;
193
194 --------------------------------------------------------------------------
195 -- --
196 -- Name : GET_BALANCES --
197 -- Type : FUNCTION --
198 -- Access : Public --
199 -- Description : Function to return SQL for Balances Region --
200 -- --
201 -- Parameters : --
202 -- IN : p_assignment_action_id NUMBER --
203 -- --
204 --------------------------------------------------------------------------
205 FUNCTION get_balances( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
206 RETURN LONG
207 IS
208
209 l_sql LONG;
210 l_date_earned DATE;
211 l_tax_unit_id NUMBER;
212
213 CURSOR csr_get_date_earned
214 IS
215 SELECT ppa.date_earned
216 FROM pay_payroll_actions ppa
217 , pay_assignment_actions paa
218 WHERE ppa.payroll_action_id = paa.payroll_action_id
219 AND paa.assignment_action_id = p_assignment_action_id;
220
221 CURSOR csr_get_tax_unit_id
222 IS
223 SELECT hsck.segment1
224 FROM hr_soft_coding_keyflex hsck
225 , per_assignments_f paf
226 , pay_assignment_actions paa
227 , pay_payroll_actions ppa
228 WHERE hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
229 AND paa.assignment_action_id = p_assignment_action_id
230 AND paa.payroll_action_id = ppa.payroll_action_id
231 AND paf.assignment_id = paa.assignment_id
232 AND ppa.effective_date BETWEEN paf.effective_start_date
233 AND paf.effective_end_date;
234
235 BEGIN
236
237 OPEN csr_get_date_earned;
238 FETCH csr_get_date_earned INTO l_date_earned;
239 CLOSE csr_get_date_earned;
240
241 OPEN csr_get_tax_unit_id;
242 FETCH csr_get_tax_unit_id INTO l_tax_unit_id;
243 CLOSE csr_get_tax_unit_id;
244
245 g_sql := 'SELECT NVL(pbt.reporting_name, pbt.balance_name) COL04
246 , TO_CHAR(pay_balance_pkg.get_value( pdb_ptd.defined_balance_id
247 , ' || p_assignment_action_id || '
248 , ' || l_tax_unit_id || '
249 , NULL
250 , NULL
251 , NULL
252 , NULL
253 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
254 , NULL
255 , NULL
256 )
257 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
258 , TO_CHAR(pay_balance_pkg.get_value( pdb_ytd.defined_balance_id
259 , ' || p_assignment_action_id || '
260 , ' || l_tax_unit_id || '
261 , NULL
262 , NULL
263 , NULL
264 , NULL
265 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
266 , NULL
267 , NULL
268 )
269 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL18
270 FROM pay_balance_types pbt
271 , pay_balance_dimensions pbd_ptd
272 , pay_balance_dimensions pbd_ytd
273 , pay_defined_balances pdb_ptd
274 , pay_defined_balances pdb_ytd
275 WHERE pbt.balance_name IN ( ''Taxable Earnings''
276 , ''Non Taxable Earnings''
277 , ''Statutory Deductions''
278 , ''Voluntary Deductions''
279 , ''Special Payments''
280 , ''Employer Liabilities''
281 , ''Pre Tax Non Statutory Deductions'')
282 AND pbd_ptd.dimension_name = ''_ASG_PTD''
283 AND pbd_ytd.dimension_name = ''_ASG_YTD''
284 AND pbt.legislation_code = ''CN''
285 AND pbd_ptd.legislation_code = ''CN''
286 AND pbd_ytd.legislation_code = ''CN''
287 AND pbd_ptd.balance_dimension_id = pdb_ptd.balance_dimension_id
288 AND pbt.balance_type_id = pdb_ptd.balance_type_id
289 AND pbd_ytd.balance_dimension_id = pdb_ytd.balance_dimension_id
290 AND pbt.balance_type_id = pdb_ytd.balance_type_id
291 ORDER BY DECODE(pbt.balance_name,''Taxable Earnings'',''Taxable Earnings'')
292 , DECODE(pbt.balance_name,''Non Taxable Earnings'',''Non Taxable Earnings'')
293 , DECODE(pbt.balance_name,''Pre Tax Non Statutory Deductions'',''Pre Tax Non Statutory Deductions'')
294 , DECODE(pbt.balance_name,''Statutory Deductions'',''Statutory Deductions'')
295 , DECODE(pbt.balance_name,''Voluntary Deductions'',''Voluntary Deductions'')';
296
297 RETURN g_sql;
298
299 END get_balances;
300
301 --------------------------------------------------------------------------
302 -- --
303 -- Name : GET_PAYMENT_METHODS --
304 -- Type : FUNCTION --
305 -- Access : Public --
306 -- Description : Function to return SQL for Payments Method Region --
307 -- --
308 -- Parameters : --
309 -- IN : p_assignment_action_id NUMBER --
310 -- --
311 --------------------------------------------------------------------------
312 FUNCTION get_payment_methods(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
313 RETURN LONG
314 IS
315 BEGIN
316
317 g_sql := 'SELECT org_payment_method_name COL01
318 , TO_CHAR(:G_CURRENCY_CODE) COL04
319 , pay_soe_util.getBankDetails('':legislation_code''
320 ,ppm.external_account_id
321 ,''BANK_NAME''
322 ,NULL) COL02
323 , pay_soe_util.getBankDetails('':legislation_code''
324 ,ppm.external_account_id
325 ,''BANK_BRANCH''
326 ,NULL) COL05
327 , pay_soe_util.getBankDetails('':legislation_code''
328 ,ppm.external_account_id
329 ,''BANK_ACCOUNT_NUMBER''
330 ,NULL) COL03
331 , to_char(pp.value
332 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE
333 ,40)
334 ) COL16
335 FROM pay_pre_payments pp
336 , pay_personal_payment_methods_f ppm
337 , pay_org_payment_methods_f opm
338 , pay_payment_types_tl pt
339 WHERE pp.assignment_action_id IN
340 (SELECT ai.locking_action_id
341 FROM pay_action_interlocks ai
342 WHERE ai.locked_action_id :action_clause)
343 AND pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
344 AND :effective_date BETWEEN ppm.effective_start_date(+)
345 AND ppm.effective_end_date(+)
346 AND pp.org_payment_method_id = opm.org_payment_method_id
347 AND :effective_date BETWEEN opm.effective_start_date
348 AND opm.effective_end_date
349 AND opm.payment_type_id = pt.payment_type_id
350 AND pt.language = USERENV(''LANG'')';
351 --
352 RETURN g_sql;
353
354 END get_payment_methods;
355
356 --------------------------------------------------------------------------
357 -- --
358 -- Name : GET_OTHER_ELEMENT_INFORMATION --
359 -- Type : FUNCTION --
360 -- Access : Public --
361 -- Description : Function to return SQL for Other Element --
362 -- Information Region --
363 -- --
364 -- Parameters : --
365 -- IN : p_assignment_action_id NUMBER --
366 -- --
367 --------------------------------------------------------------------------
368 FUNCTION get_other_element_information(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
369 RETURN LONG
370 IS
371
372 CURSOR csr_prepayment
373 IS
374 SELECT MAX(locked_action_id)
375 FROM pay_action_interlocks
376 WHERE locking_action_id = p_assignment_action_id;
377
378 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
379
380
381 BEGIN
382
383 OPEN csr_prepayment;
384 FETCH csr_prepayment INTO l_assignment_action_id;
385 CLOSE csr_prepayment;
386
387 IF l_assignment_action_id IS NOT NULL THEN
388 p_assignment_action_id := l_assignment_action_id;
389 END IF;
390
391 g_sql :=
392 'SELECT org.org_information7 COL02
393 , prv.result_value COL16
394 FROM pay_run_result_values prv,
395 pay_run_results prr,
396 hr_organization_information_v org
397 WHERE prr.status IN (''P'',''PA'')
398 AND org.org_information_context = ''Business Group:SOE Detail''
399 AND org.org_information1 = ''ELEMENT''
400 AND prv.run_result_id = prr.run_result_id
401 AND prr.assignment_action_id = ' || p_assignment_action_id || '
402 AND prr.element_type_id = org.org_information2
403 AND prv.input_value_id = org.org_information3
404 AND prv.result_value IS NOT NULL';
405
406 RETURN g_sql;
407
408
409 END get_other_element_information;
410
411 --------------------------------------------------------------------------
412 -- --
413 -- Name : GET_OTHER_BALANCE_INFORMATION --
414 -- Type : FUNCTION --
415 -- Access : Public --
416 -- Description : Function to return SQL for Other Balance --
417 -- Information Region --
418 -- --
419 -- Parameters : --
420 -- IN : p_assignment_action_id NUMBER --
421 -- --
422 --------------------------------------------------------------------------
423 FUNCTION get_other_balance_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
424 RETURN LONG
425 IS
426
427 l_date_earned DATE;
428 l_tax_unit_id NUMBER;
429 l_business_group_id per_business_groups.business_group_id%TYPE;
430
431 CURSOR csr_get_date_earned
432 IS
433 SELECT ppa.date_earned, ppa.business_group_id
434 FROM pay_payroll_actions ppa
435 , pay_assignment_actions paa
436 WHERE ppa.payroll_action_id = paa.payroll_action_id
437 AND paa.assignment_action_id = p_assignment_action_id;
438
439 CURSOR csr_get_tax_unit_id
440 IS
441 SELECT hsck.segment1
442 FROM hr_soft_coding_keyflex hsck
443 , per_assignments_f paf
444 , pay_assignment_actions paa
445 , pay_payroll_actions ppa
446 WHERE hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
447 AND paa.assignment_action_id = p_assignment_action_id
448 AND paa.payroll_action_id = ppa.payroll_action_id
449 AND paf.assignment_id = paa.assignment_id
450 AND ppa.effective_date BETWEEN paf.effective_start_date
451 AND paf.effective_end_date;
452
453 BEGIN
454
455 OPEN csr_get_date_earned;
456 FETCH csr_get_date_earned INTO l_date_earned, l_business_group_id;
457 CLOSE csr_get_date_earned;
458
459 OPEN csr_get_tax_unit_id;
460 FETCH csr_get_tax_unit_id INTO l_tax_unit_id;
461 CLOSE csr_get_tax_unit_id;
462
463 g_sql := 'SELECT org.org_information7 COL02
464 , TO_CHAR(pay_balance_pkg.get_value( pdb.defined_balance_id
465 , ' || p_assignment_action_id || '
466 , ' || l_tax_unit_id || '
467 , NULL
468 , NULL
469 , NULL
470 , NULL
471 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
472 , NULL
473 , NULL
474 )
475 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
476 FROM pay_defined_balances pdb
477 , hr_organization_information_v org
478 WHERE org.organization_id = ' || l_business_group_id || '
479 AND org.org_information_context = ''Business Group:SOE Detail''
480 AND org.org_information1 = ''BALANCE''
481 AND pdb.balance_type_id = org.org_information4
482 AND pdb.balance_dimension_id = org.org_information5';
483
484 RETURN g_sql;
485
486 END get_other_balance_information;
487
488 --------------------------------------------------------------------------
489 -- --
490 -- Name : GET_ANNUAL_LEAVE_INFORMATION --
491 -- Type : FUNCTION --
492 -- Access : Public --
493 -- Description : Function to return SQL for Annual Leave --
494 -- Information Region --
495 -- --
496 -- Parameters : --
497 -- IN : p_assignment_action_id NUMBER --
498 -- --
499 --------------------------------------------------------------------------
500 FUNCTION get_annual_leave_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
501 RETURN LONG
502 IS
503
504 CURSOR csr_get_annual_leave_details(p_payroll_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
505 IS
506 SELECT pap.accrual_plan_name
507 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
508 ,hr_general.decode_lookup('HOURS_OR_DAYS',pap.accrual_units_of_measure)
509 ,ppa.payroll_id
510 ,pap.business_group_id
511 ,pap.accrual_plan_id
512 ,paa.assignment_id
513 FROM pay_accrual_plans pap
514 ,pay_element_types_f pet
515 ,pay_element_links_f pel
516 ,pay_element_entries_f pee
517 ,pay_assignment_actions paa
518 ,pay_payroll_actions ppa
519 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
520 AND pel.element_type_id = pet.element_type_id
521 AND pee.element_link_id = pel.element_link_id
522 AND paa.assignment_id = pee.assignment_id
523 AND ppa.payroll_action_id = paa.payroll_action_id
524 AND ppa.action_type IN ('R','Q')
525 AND ppa.action_status = 'C'
526 AND ppa.date_earned BETWEEN pet.effective_start_date
527 AND pet.effective_end_date
528 AND ppa.date_earned BETWEEN pel.effective_start_date
529 AND pel.effective_end_date
530 AND ppa.date_earned BETWEEN pee.effective_start_date
531 AND pee.effective_end_date
532 AND paa.assignment_action_id = p_payroll_assignment_action_id;
533
534 CURSOR csr_get_annual_leave_R12(p_payroll_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
535 IS
536 SELECT paptl.accrual_plan_name
537 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
538 ,hr_general.decode_lookup('HOURS_OR_DAYS',pap.accrual_units_of_measure)
539 ,ppa.payroll_id
540 ,pap.business_group_id
541 ,pap.accrual_plan_id
542 ,paa.assignment_id
543 FROM pay_accrual_plans pap
544 ,pay_accrual_plans_tl paptl
545 ,pay_element_types_f pet
546 ,pay_element_links_f pel
547 ,pay_element_entries_f pee
548 ,pay_assignment_actions paa
549 ,pay_payroll_actions ppa
550 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
551 AND pel.element_type_id = pet.element_type_id
552 AND pee.element_link_id = pel.element_link_id
553 AND paa.assignment_id = pee.assignment_id
554 AND ppa.payroll_action_id = paa.payroll_action_id
555 AND ppa.action_type IN ('R','Q')
556 AND ppa.action_status = 'C'
557 AND ppa.date_earned BETWEEN pet.effective_start_date
558 AND pet.effective_end_date
559 AND ppa.date_earned BETWEEN pel.effective_start_date
560 AND pel.effective_end_date
561 AND ppa.date_earned BETWEEN pee.effective_start_date
562 AND pee.effective_end_date
563 AND paa.assignment_action_id = p_payroll_assignment_action_id
564 AND pap.ACCRUAL_PLAN_ID = paptl.ACCRUAL_PLAN_ID
565 AND paptl.LANGUAGE = USERENV('LANG');
566
567 CURSOR csr_get_date_earned
568 IS
569 SELECT ppa.date_earned
570 FROM pay_payroll_actions ppa
571 , pay_assignment_actions paa
572 WHERE ppa.payroll_action_id = paa.payroll_action_id
573 AND paa.assignment_action_id = p_assignment_action_id;
574
575 CURSOR csr_prepayment
576 IS
577 SELECT MAX(locked_action_id)
578 FROM pay_action_interlocks
579 WHERE locking_action_id = p_assignment_action_id;
580
581 l_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
582 l_accrual_category pay_accrual_plans.accrual_category%TYPE;
583 l_uom pay_accrual_plans.accrual_units_of_measure%TYPE;
584 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
585 l_business_group_id per_business_groups.business_group_id%TYPE;
586 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE;
587 l_assignment_id per_assignments_f.assignment_id%TYPE;
588 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
589 l_annual_leave_balance NUMBER;
590 l_ovn NUMBER;
591 l_leave_taken NUMBER;
592 l_start_date DATE;
593 l_end_date DATE;
594 l_accrual_end_date DATE;
595 l_date_earned DATE;
596 l_accrual NUMBER;
597 l_total_leave_taken NUMBER;
598 l_procedure VARCHAR2(100);
599 l_product_release VARCHAR2(50);
600
601 BEGIN
602
603 OPEN csr_prepayment;
604 FETCH csr_prepayment INTO l_assignment_action_id;
605 CLOSE csr_prepayment;
606
607 IF l_assignment_action_id IS NULL THEN
608 l_assignment_action_id := p_assignment_action_id;
609 END IF;
610
611 SELECT substr(p.product_version,1,2) INTO l_product_release
612 FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
613 WHERE a.application_id = p.application_id
614 AND a.application_id = t.application_id
615 AND t.language = Userenv ('LANG')
616 AND Substr (a.application_short_name, 1, 5) = 'PAY';
617
618 IF TO_NUMBER(l_product_release) = 11 THEN
619 OPEN csr_get_annual_leave_details(l_assignment_action_id);
620 FETCH csr_get_annual_leave_details
621 INTO l_plan_name
622 , l_accrual_category
623 , l_uom
624 , l_payroll_id
625 , l_business_group_id
626 , l_accrual_plan_id
627 , l_assignment_id;
628 CLOSE csr_get_annual_leave_details;
629 ELSE
630 OPEN csr_get_annual_leave_R12(l_assignment_action_id);
631 FETCH csr_get_annual_leave_R12
632 INTO l_plan_name
633 , l_accrual_category
634 , l_uom
635 , l_payroll_id
636 , l_business_group_id
637 , l_accrual_plan_id
638 , l_assignment_id;
639 CLOSE csr_get_annual_leave_R12;
640 END IF;
641
642 OPEN csr_get_date_earned;
643 FETCH csr_get_date_earned INTO l_date_earned;
644 CLOSE csr_get_date_earned;
645
646
647 per_accrual_calc_functions.get_net_accrual
648 ( p_assignment_id => l_assignment_id
649 , p_plan_id => l_accrual_plan_id
650 , p_payroll_id => l_payroll_id
651 , p_business_group_id => l_business_group_id
652 , p_calculation_date => l_date_earned
653 , p_start_date => l_start_date
654 , p_end_date => l_end_date
655 , p_accrual_end_date => l_accrual_end_date
656 , p_accrual => l_accrual
657 , p_net_entitlement => l_annual_leave_balance
658 );
659
660 g_sql := 'SELECT ''' || l_plan_name || ''' COL01
661 , ''' || l_uom || ''' COL02
662 , TO_CHAR(' || l_annual_leave_balance|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
663 FROM DUAL';
664
665 RETURN g_sql;
666
667 END get_annual_leave_information;
668
669 --------------------------------------------------------------------------
670 -- --
671 -- Name : GET_LEAVE_TAKEN --
672 -- Type : FUNCTION --
673 -- Access : Public --
674 -- Description : Function to return SQL for Leave Taken Region --
675 -- --
676 -- Parameters : --
677 -- IN : p_assignment_action_id NUMBER --
678 -- --
679 --------------------------------------------------------------------------
680 FUNCTION get_leave_taken(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
681 RETURN LONG
682 IS
683
684 CURSOR csr_prepayment
685 IS
686 SELECT MAX(locked_action_id)
687 FROM pay_action_interlocks
688 WHERE locking_action_id = p_assignment_action_id;
689
690 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
691
692 BEGIN
693
694 OPEN csr_prepayment;
695 FETCH csr_prepayment INTO l_assignment_action_id;
696 CLOSE csr_prepayment;
697
698 IF l_assignment_action_id IS NOT NULL THEN
699 p_assignment_action_id := l_assignment_action_id;
700 END IF;
701
702
703 g_sql :=
704 ' SELECT pet.reporting_name COL01
705 ,TO_CHAR(decode(pet.processing_type,''R'',greatest(pab.date_start,PTP.START_DATE),pab.date_start),''DD-Mon-YYYY'') COL02
706 ,TO_CHAR(decode(pet.processing_type,''R'',least(pab.date_end,PTP.END_DATE),pab.date_end),''DD-Mon-YYYY'') COL03
707 ,TO_CHAR(decode(pet.processing_type,''R'',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours))
708 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
709 FROM pay_assignment_actions paa
710 ,pay_payroll_actions ppa
711 ,pay_run_results prr
712 ,pay_run_result_values prrv
713 ,per_time_periods ptp
714 ,pay_element_types_f pet
715 ,pay_input_values_f piv
716 ,pay_element_entries_f pee
717 ,per_absence_attendance_types pat
718 ,per_absence_attendances pab
719 WHERE paa.assignment_action_id = ' || p_assignment_action_id || '
720 AND ppa.payroll_action_id = paa.payroll_action_id
721 AND ppa.action_type IN (''Q'',''R'')
722 AND ptp.time_period_id = ppa.time_period_id
723 AND paa.assignment_action_id = prr.assignment_action_id
724 AND pet.element_type_id = prr.element_type_id
725 AND pet.element_type_id = piv.element_type_id
726 AND piv.input_value_id = pat.input_value_id
727 AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
728 AND pab.absence_attendance_id = pee.creator_id
729 AND pee.creator_type = ''A''
730 AND pee.assignment_id = paa.assignment_id
731 AND pee.element_entry_id = prr.source_id
732 AND piv.input_value_id = prrv.input_value_id
733 AND prr.run_result_id = prrv.run_result_id
734 AND prr.status IN (''P'',''PA'')
735 AND ppa.effective_date BETWEEN pet.effective_start_date
736 AND pet.effective_end_date
737 AND ppa.effective_date BETWEEN pee.effective_start_date
738 AND pee.effective_end_date
739 AND ppa.effective_date BETWEEN piv.effective_start_date
740 AND piv.effective_end_date';
741
742
743 RETURN g_sql;
744
745 END get_leave_taken;
746
747 --------------------------------------------------------------------------
748 -- --
749 -- Name : GET_MESSAGES --
750 -- Type : FUNCTION --
751 -- Access : Public --
752 -- Description : Function to return SQL for Messages Region --
753 -- --
754 -- Parameters : --
755 -- IN : p_assignment_action_id NUMBER --
756 -- --
757 --------------------------------------------------------------------------
758 FUNCTION get_messages(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
759 RETURN LONG
760 IS
761
762 CURSOR csr_prepayment
763 IS
764 SELECT MAX(locked_action_id)
765 FROM pay_action_interlocks
766 WHERE locking_action_id = p_assignment_action_id;
767
768 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
769
770 BEGIN
771
772
773 OPEN csr_prepayment;
774 FETCH csr_prepayment INTO l_assignment_action_id;
775 CLOSE csr_prepayment;
776
777 IF l_assignment_action_id IS NULL THEN
778 l_assignment_action_id := p_assignment_action_id;
779 END IF;
780
781 g_sql := 'SELECT ppa.pay_advice_message COL01
782 FROM pay_payroll_actions ppa
783 , pay_assignment_actions paa
784 WHERE ppa.payroll_action_id = paa.payroll_action_id
785 AND paa.assignment_action_id = ' || l_assignment_action_id;
786
787 RETURN g_sql;
788
789 END get_messages;
790
791 END pay_cn_soe;