[Home] [Help]
PACKAGE BODY: APPS.PAY_CN_SOE
Source
1 PACKAGE BODY pay_cn_soe AS
2 /* $Header: pycnsoe.pkb 120.7 2006/11/30 06:32:45 rpalli noship $ */
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_VOLUNTARY_DEDUCTIONS --
143 -- Type : FUNCTION --
144 -- Access : Public --
145 -- Description : Function to return SQL for Voluntary Deductions --
146 -- Region --
147 -- --
148 -- Parameters : --
149 -- IN : p_assignment_action_id NUMBER --
150 -- --
151 --------------------------------------------------------------------------
152 FUNCTION get_voluntary_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_voluntary_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 => 'Voluntary Dedn'
163 );
164
165 END get_voluntary_deductions;
166
167 --------------------------------------------------------------------------
168 -- --
169 -- Name : GET_BALANCES --
170 -- Type : FUNCTION --
171 -- Access : Public --
172 -- Description : Function to return SQL for Balances Region --
173 -- --
174 -- Parameters : --
175 -- IN : p_assignment_action_id NUMBER --
176 -- --
177 --------------------------------------------------------------------------
178 FUNCTION get_balances( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
179 RETURN LONG
180 IS
181
182 l_sql LONG;
183 l_date_earned DATE;
184 l_tax_unit_id NUMBER;
185
186 CURSOR csr_get_date_earned
187 IS
188 SELECT ppa.date_earned
189 FROM pay_payroll_actions ppa
190 , pay_assignment_actions paa
191 WHERE ppa.payroll_action_id = paa.payroll_action_id
192 AND paa.assignment_action_id = p_assignment_action_id;
193
194 CURSOR csr_get_tax_unit_id
195 IS
196 SELECT hsck.segment1
197 FROM hr_soft_coding_keyflex hsck
198 , per_assignments_f paf
199 , pay_assignment_actions paa
200 , pay_payroll_actions ppa
201 WHERE hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
202 AND paa.assignment_action_id = p_assignment_action_id
203 AND paa.payroll_action_id = ppa.payroll_action_id
204 AND paf.assignment_id = paa.assignment_id
205 AND ppa.effective_date BETWEEN paf.effective_start_date
206 AND paf.effective_end_date;
207
208 BEGIN
209
210 OPEN csr_get_date_earned;
211 FETCH csr_get_date_earned INTO l_date_earned;
212 CLOSE csr_get_date_earned;
213
214 OPEN csr_get_tax_unit_id;
215 FETCH csr_get_tax_unit_id INTO l_tax_unit_id;
216 CLOSE csr_get_tax_unit_id;
217
218 g_sql := 'SELECT NVL(pbt.reporting_name, pbt.balance_name) COL04
219 , TO_CHAR(pay_balance_pkg.get_value( pdb_ptd.defined_balance_id
220 , ' || p_assignment_action_id || '
221 , ' || l_tax_unit_id || '
222 , NULL
223 , NULL
224 , NULL
225 , NULL
226 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
227 , NULL
228 , NULL
229 )
230 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
231 , TO_CHAR(pay_balance_pkg.get_value( pdb_ytd.defined_balance_id
232 , ' || p_assignment_action_id || '
233 , ' || l_tax_unit_id || '
234 , NULL
235 , NULL
236 , NULL
237 , NULL
238 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
239 , NULL
240 , NULL
241 )
242 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL18
243 FROM pay_balance_types pbt
244 , pay_balance_dimensions pbd_ptd
245 , pay_balance_dimensions pbd_ytd
246 , pay_defined_balances pdb_ptd
247 , pay_defined_balances pdb_ytd
248 WHERE pbt.balance_name IN ( ''Taxable Earnings''
249 , ''Non Taxable Earnings''
250 , ''Statutory Deductions''
251 , ''Voluntary Deductions''
252 , ''Special Payments''
253 , ''Employer Liabilities'')
254 AND pbd_ptd.dimension_name = ''_ASG_PTD''
255 AND pbd_ytd.dimension_name = ''_ASG_YTD''
256 AND pbt.legislation_code = ''CN''
257 AND pbd_ptd.legislation_code = ''CN''
258 AND pbd_ytd.legislation_code = ''CN''
259 AND pbd_ptd.balance_dimension_id = pdb_ptd.balance_dimension_id
260 AND pbt.balance_type_id = pdb_ptd.balance_type_id
261 AND pbd_ytd.balance_dimension_id = pdb_ytd.balance_dimension_id
262 AND pbt.balance_type_id = pdb_ytd.balance_type_id
263 ORDER BY DECODE(pbt.balance_name,''Taxable Earnings'',''Taxable Earnings'')
264 , DECODE(pbt.balance_name,''Non Taxable Earnings'',''Non Taxable Earnings'')
265 , DECODE(pbt.balance_name,''Statutory Deductions'',''Statutory Deductions'')
266 , DECODE(pbt.balance_name,''Voluntary Deductions'',''Voluntary Deductions'')';
267
268 RETURN g_sql;
269
270 END get_balances;
271
272 --------------------------------------------------------------------------
273 -- --
274 -- Name : GET_PAYMENT_METHODS --
275 -- Type : FUNCTION --
276 -- Access : Public --
277 -- Description : Function to return SQL for Payments Method Region --
278 -- --
279 -- Parameters : --
280 -- IN : p_assignment_action_id NUMBER --
281 -- --
282 --------------------------------------------------------------------------
283 FUNCTION get_payment_methods(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
284 RETURN LONG
285 IS
286 BEGIN
287
288 g_sql := 'SELECT org_payment_method_name COL01
289 , TO_CHAR(:G_CURRENCY_CODE) COL04
290 , pay_soe_util.getBankDetails('':legislation_code''
291 ,ppm.external_account_id
292 ,''BANK_NAME''
293 ,NULL) COL02
294 , pay_soe_util.getBankDetails('':legislation_code''
295 ,ppm.external_account_id
296 ,''BANK_BRANCH''
297 ,NULL) COL05
298 , pay_soe_util.getBankDetails('':legislation_code''
299 ,ppm.external_account_id
300 ,''BANK_ACCOUNT_NUMBER''
301 ,NULL) COL03
302 , to_char(pp.value
303 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE
304 ,40)
305 ) COL16
306 FROM pay_pre_payments pp
307 , pay_personal_payment_methods_f ppm
308 , pay_org_payment_methods_f opm
309 , pay_payment_types_tl pt
310 WHERE pp.assignment_action_id IN
311 (SELECT ai.locking_action_id
312 FROM pay_action_interlocks ai
313 WHERE ai.locked_action_id :action_clause)
314 AND pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
315 AND :effective_date BETWEEN ppm.effective_start_date(+)
316 AND ppm.effective_end_date(+)
320 AND opm.payment_type_id = pt.payment_type_id
317 AND pp.org_payment_method_id = opm.org_payment_method_id
318 AND :effective_date BETWEEN opm.effective_start_date
319 AND opm.effective_end_date
321 AND pt.language = USERENV(''LANG'')';
322 --
323 RETURN g_sql;
324
325 END get_payment_methods;
326
327 --------------------------------------------------------------------------
328 -- --
329 -- Name : GET_OTHER_ELEMENT_INFORMATION --
330 -- Type : FUNCTION --
331 -- Access : Public --
332 -- Description : Function to return SQL for Other Element --
333 -- Information Region --
334 -- --
335 -- Parameters : --
336 -- IN : p_assignment_action_id NUMBER --
337 -- --
338 --------------------------------------------------------------------------
339 FUNCTION get_other_element_information(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
340 RETURN LONG
341 IS
342
343 CURSOR csr_prepayment
344 IS
345 SELECT MAX(locked_action_id)
346 FROM pay_action_interlocks
347 WHERE locking_action_id = p_assignment_action_id;
348
349 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
350
351
352 BEGIN
353
354 OPEN csr_prepayment;
355 FETCH csr_prepayment INTO l_assignment_action_id;
356 CLOSE csr_prepayment;
357
358 IF l_assignment_action_id IS NOT NULL THEN
359 p_assignment_action_id := l_assignment_action_id;
360 END IF;
361
362 g_sql :=
363 'SELECT org.org_information7 COL02
364 , prv.result_value COL16
365 FROM pay_run_result_values prv,
366 pay_run_results prr,
367 hr_organization_information_v org
368 WHERE prr.status IN (''P'',''PA'')
369 AND org.org_information_context = ''Business Group:SOE Detail''
370 AND org.org_information1 = ''ELEMENT''
371 AND prv.run_result_id = prr.run_result_id
372 AND prr.assignment_action_id = ' || p_assignment_action_id || '
373 AND prr.element_type_id = org.org_information2
374 AND prv.input_value_id = org.org_information3
375 AND prv.result_value IS NOT NULL';
376
377 RETURN g_sql;
378
379
380 END get_other_element_information;
381
382 --------------------------------------------------------------------------
383 -- --
384 -- Name : GET_OTHER_BALANCE_INFORMATION --
385 -- Type : FUNCTION --
386 -- Access : Public --
387 -- Description : Function to return SQL for Other Balance --
388 -- Information Region --
389 -- --
390 -- Parameters : --
391 -- IN : p_assignment_action_id NUMBER --
392 -- --
393 --------------------------------------------------------------------------
394 FUNCTION get_other_balance_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
395 RETURN LONG
396 IS
397
398 l_date_earned DATE;
399 l_tax_unit_id NUMBER;
400 l_business_group_id per_business_groups.business_group_id%TYPE;
401
402 CURSOR csr_get_date_earned
403 IS
404 SELECT ppa.date_earned, ppa.business_group_id
405 FROM pay_payroll_actions ppa
406 , pay_assignment_actions paa
407 WHERE ppa.payroll_action_id = paa.payroll_action_id
408 AND paa.assignment_action_id = p_assignment_action_id;
409
410 CURSOR csr_get_tax_unit_id
411 IS
412 SELECT hsck.segment1
413 FROM hr_soft_coding_keyflex hsck
414 , per_assignments_f paf
415 , pay_assignment_actions paa
416 , pay_payroll_actions ppa
417 WHERE hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
418 AND paa.assignment_action_id = p_assignment_action_id
419 AND paa.payroll_action_id = ppa.payroll_action_id
420 AND paf.assignment_id = paa.assignment_id
421 AND ppa.effective_date BETWEEN paf.effective_start_date
422 AND paf.effective_end_date;
423
424 BEGIN
425
426 OPEN csr_get_date_earned;
427 FETCH csr_get_date_earned INTO l_date_earned, l_business_group_id;
428 CLOSE csr_get_date_earned;
429
430 OPEN csr_get_tax_unit_id;
431 FETCH csr_get_tax_unit_id INTO l_tax_unit_id;
432 CLOSE csr_get_tax_unit_id;
433
434 g_sql := 'SELECT org.org_information7 COL02
435 , TO_CHAR(pay_balance_pkg.get_value( pdb.defined_balance_id
436 , ' || p_assignment_action_id || '
437 , ' || l_tax_unit_id || '
438 , NULL
439 , NULL
440 , NULL
441 , NULL
442 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
443 , NULL
444 , NULL
445 )
449 WHERE org.organization_id = ' || l_business_group_id || '
446 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
447 FROM pay_defined_balances pdb
448 , hr_organization_information_v org
450 AND org.org_information_context = ''Business Group:SOE Detail''
451 AND org.org_information1 = ''BALANCE''
452 AND pdb.balance_type_id = org.org_information4
453 AND pdb.balance_dimension_id = org.org_information5';
454
455 RETURN g_sql;
456
457 END get_other_balance_information;
458
459 --------------------------------------------------------------------------
460 -- --
461 -- Name : GET_ANNUAL_LEAVE_INFORMATION --
462 -- Type : FUNCTION --
463 -- Access : Public --
464 -- Description : Function to return SQL for Annual Leave --
465 -- Information Region --
466 -- --
467 -- Parameters : --
468 -- IN : p_assignment_action_id NUMBER --
469 -- --
470 --------------------------------------------------------------------------
471 FUNCTION get_annual_leave_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
472 RETURN LONG
473 IS
474
475 CURSOR csr_get_annual_leave_details(p_payroll_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
476 IS
477 SELECT pap.accrual_plan_name
478 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
479 ,hr_general.decode_lookup('HOURS_OR_DAYS',pap.accrual_units_of_measure)
480 ,ppa.payroll_id
481 ,pap.business_group_id
482 ,pap.accrual_plan_id
483 ,paa.assignment_id
484 FROM pay_accrual_plans pap
485 ,pay_element_types_f pet
486 ,pay_element_links_f pel
487 ,pay_element_entries_f pee
488 ,pay_assignment_actions paa
489 ,pay_payroll_actions ppa
490 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
491 AND pel.element_type_id = pet.element_type_id
492 AND pee.element_link_id = pel.element_link_id
493 AND paa.assignment_id = pee.assignment_id
494 AND ppa.payroll_action_id = paa.payroll_action_id
495 AND ppa.action_type IN ('R','Q')
496 AND ppa.action_status = 'C'
497 AND ppa.date_earned BETWEEN pet.effective_start_date
498 AND pet.effective_end_date
499 AND ppa.date_earned BETWEEN pel.effective_start_date
500 AND pel.effective_end_date
501 AND ppa.date_earned BETWEEN pee.effective_start_date
502 AND pee.effective_end_date
503 AND paa.assignment_action_id = p_payroll_assignment_action_id;
504
505 CURSOR csr_get_date_earned
506 IS
507 SELECT ppa.date_earned
508 FROM pay_payroll_actions ppa
509 , pay_assignment_actions paa
510 WHERE ppa.payroll_action_id = paa.payroll_action_id
511 AND paa.assignment_action_id = p_assignment_action_id;
512
513 CURSOR csr_prepayment
514 IS
515 SELECT MAX(locked_action_id)
516 FROM pay_action_interlocks
517 WHERE locking_action_id = p_assignment_action_id;
518
519 l_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
520 l_accrual_category pay_accrual_plans.accrual_category%TYPE;
521 l_uom pay_accrual_plans.accrual_units_of_measure%TYPE;
522 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
523 l_business_group_id per_business_groups.business_group_id%TYPE;
524 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE;
525 l_assignment_id per_assignments_f.assignment_id%TYPE;
526 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
527 l_annual_leave_balance NUMBER;
528 l_ovn NUMBER;
529 l_leave_taken NUMBER;
530 l_start_date DATE;
531 l_end_date DATE;
532 l_accrual_end_date DATE;
533 l_date_earned DATE;
534 l_accrual NUMBER;
535 l_total_leave_taken NUMBER;
536 l_procedure VARCHAR2(100);
537
538 BEGIN
539
540 OPEN csr_prepayment;
541 FETCH csr_prepayment INTO l_assignment_action_id;
542 CLOSE csr_prepayment;
543
544 IF l_assignment_action_id IS NULL THEN
545 l_assignment_action_id := p_assignment_action_id;
546 END IF;
547
548 OPEN csr_get_annual_leave_details(l_assignment_action_id);
549 FETCH csr_get_annual_leave_details
550 INTO l_plan_name
551 , l_accrual_category
552 , l_uom
553 , l_payroll_id
554 , l_business_group_id
555 , l_accrual_plan_id
556 , l_assignment_id;
557 CLOSE csr_get_annual_leave_details;
558
559 OPEN csr_get_date_earned;
560 FETCH csr_get_date_earned INTO l_date_earned;
561 CLOSE csr_get_date_earned;
562
563
564 per_accrual_calc_functions.get_net_accrual
565 ( p_assignment_id => l_assignment_id
566 , p_plan_id => l_accrual_plan_id
567 , p_payroll_id => l_payroll_id
568 , p_business_group_id => l_business_group_id
572 , p_accrual_end_date => l_accrual_end_date
569 , p_calculation_date => l_date_earned
570 , p_start_date => l_start_date
571 , p_end_date => l_end_date
573 , p_accrual => l_accrual
574 , p_net_entitlement => l_annual_leave_balance
575 );
576
577 g_sql := 'SELECT ''' || l_plan_name || ''' COL01
578 , ''' || l_uom || ''' COL02
579 , TO_CHAR(' || l_annual_leave_balance|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
580 FROM DUAL';
581
582 RETURN g_sql;
583
584 END get_annual_leave_information;
585
586 --------------------------------------------------------------------------
587 -- --
588 -- Name : GET_LEAVE_TAKEN --
589 -- Type : FUNCTION --
590 -- Access : Public --
591 -- Description : Function to return SQL for Leave Taken Region --
592 -- --
593 -- Parameters : --
594 -- IN : p_assignment_action_id NUMBER --
595 -- --
596 --------------------------------------------------------------------------
597 FUNCTION get_leave_taken(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
598 RETURN LONG
599 IS
600
601 CURSOR csr_prepayment
602 IS
603 SELECT MAX(locked_action_id)
604 FROM pay_action_interlocks
605 WHERE locking_action_id = p_assignment_action_id;
606
607 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
608
609 BEGIN
610
611 OPEN csr_prepayment;
612 FETCH csr_prepayment INTO l_assignment_action_id;
613 CLOSE csr_prepayment;
614
615 IF l_assignment_action_id IS NOT NULL THEN
616 p_assignment_action_id := l_assignment_action_id;
617 END IF;
618
619
620 g_sql :=
621 ' SELECT pet.reporting_name COL01
622 ,TO_CHAR(decode(pet.processing_type,''R'',greatest(pab.date_start,PTP.START_DATE),pab.date_start),''DD-Mon-YYYY'') COL02
623 ,TO_CHAR(decode(pet.processing_type,''R'',least(pab.date_end,PTP.END_DATE),pab.date_end),''DD-Mon-YYYY'') COL03
624 ,TO_CHAR(decode(pet.processing_type,''R'',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours))
625 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
626 FROM pay_assignment_actions paa
627 ,pay_payroll_actions ppa
628 ,pay_run_results prr
629 ,pay_run_result_values prrv
630 ,per_time_periods ptp
631 ,pay_element_types_f pet
632 ,pay_input_values_f piv
633 ,pay_element_entries_f pee
634 ,per_absence_attendance_types pat
635 ,per_absence_attendances pab
636 WHERE paa.assignment_action_id = ' || p_assignment_action_id || '
637 AND ppa.payroll_action_id = paa.payroll_action_id
638 AND ppa.action_type IN (''Q'',''R'')
639 AND ptp.time_period_id = ppa.time_period_id
640 AND paa.assignment_action_id = prr.assignment_action_id
641 AND pet.element_type_id = prr.element_type_id
642 AND pet.element_type_id = piv.element_type_id
643 AND piv.input_value_id = pat.input_value_id
644 AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
645 AND pab.absence_attendance_id = pee.creator_id
646 AND pee.creator_type = ''A''
647 AND pee.assignment_id = paa.assignment_id
648 AND pee.element_entry_id = prr.source_id
649 AND piv.input_value_id = prrv.input_value_id
650 AND prr.run_result_id = prrv.run_result_id
651 AND ppa.effective_date BETWEEN pet.effective_start_date
652 AND pet.effective_end_date
653 AND ppa.effective_date BETWEEN pee.effective_start_date
654 AND pee.effective_end_date
655 AND ppa.effective_date BETWEEN piv.effective_start_date
656 AND piv.effective_end_date';
657
658
659 RETURN g_sql;
660
661 END get_leave_taken;
662
663 --------------------------------------------------------------------------
664 -- --
665 -- Name : GET_MESSAGES --
666 -- Type : FUNCTION --
667 -- Access : Public --
668 -- Description : Function to return SQL for Messages Region --
669 -- --
670 -- Parameters : --
671 -- IN : p_assignment_action_id NUMBER --
672 -- --
673 --------------------------------------------------------------------------
674 FUNCTION get_messages(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
675 RETURN LONG
676 IS
677
678 CURSOR csr_prepayment
679 IS
680 SELECT MAX(locked_action_id)
681 FROM pay_action_interlocks
682 WHERE locking_action_id = p_assignment_action_id;
683
684 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
685
686 BEGIN
687
688
689 OPEN csr_prepayment;
690 FETCH csr_prepayment INTO l_assignment_action_id;
691 CLOSE csr_prepayment;
692
693 IF l_assignment_action_id IS NULL THEN
694 l_assignment_action_id := p_assignment_action_id;
695 END IF;
696
697 g_sql := 'SELECT ppa.pay_advice_message COL01
698 FROM pay_payroll_actions ppa
699 , pay_assignment_actions paa
700 WHERE ppa.payroll_action_id = paa.payroll_action_id
701 AND paa.assignment_action_id = ' || l_assignment_action_id;
702
703 RETURN g_sql;
704
705 END get_messages;
706
707 END pay_cn_soe;