[Home] [Help]
PACKAGE BODY: APPS.PAY_HK_SOE_PKG
Source
1 package body pay_hk_soe_pkg as
2 /* $Header: pyhksoe.pkb 120.12 2011/05/05 10:34:02 jmarupil noship $ */
3
4 g_debug boolean;
5 g_package constant varchar2(100) := 'pay_hk_soe_pkg.';
6 g_sql long;
7
8 ------------------------------------------------------------------------
9 -- Define global variable to store defined_balance_id's and the
10 -- corresponding balance values for BBR.
11 ------------------------------------------------------------------------
12 p_balance_value_tab_payment pay_balance_pkg.t_balance_value_tab;
13 p_balance_value_tab_ytd pay_balance_pkg.t_balance_value_tab;
14 p_context_table pay_balance_pkg.t_context_tab;
15 p_result_table pay_balance_pkg.t_detailed_bal_out_tab;
16
17 ------------------------------------------------------------------------
18 -- Returns the Currency Code for the Business Group.
19 ------------------------------------------------------------------------
20 function business_currency_code
21 (p_business_group_id in hr_organization_units.business_group_id%type)
22 return fnd_currencies.currency_code%type is
23
24 v_currency_code fnd_currencies.currency_code%type;
25
26 cursor currency_code
27 (c_business_group_id hr_organization_units.business_group_id%type) is
28 select fcu.currency_code
29 from hr_organization_information hoi,
30 hr_organization_units hou,
31 fnd_currencies fcu
32 where hou.business_group_id = c_business_group_id
33 and hou.organization_id = hoi.organization_id
34 and hoi.org_information_context = 'Business Group Information'
35 and fcu.issuing_territory_code = hoi.org_information9;
36
37 begin
38 open currency_code (p_business_group_id);
39 fetch currency_code into v_currency_code;
40 close currency_code;
41
42 return v_currency_code;
43
44 end business_currency_code;
45
46 ------------------------------------------------------------------------
47 -- Sums the Balances for This Pay and YTD, according to the parameters.
48 ------------------------------------------------------------------------
49 function get_balance_id
50 (p_balance_name in pay_balance_types.balance_name%type,
51 p_dimension_name in pay_balance_dimensions.dimension_name%type)
52 return pay_defined_balances.defined_balance_id%type
53 is
54
55 p_defined_balance_id pay_defined_balances.defined_balance_id%type;
56
57 cursor balance_id
58 (c_balance_name pay_balance_types.balance_name%type,
59 c_dimension_name pay_balance_dimensions.dimension_name%type) is
60 select
61 pdb.defined_balance_id
62 from pay_balance_types pbt,
63 pay_defined_balances pdb,
64 pay_balance_dimensions pbd
65 where pbt.balance_type_id = pdb.balance_type_id
66 and pdb.balance_dimension_id = pbd.balance_dimension_id
67 and pbd.dimension_name = c_dimension_name
68 and pbt.balance_name = c_balance_name
69 and pbt.legislation_code = 'HK' ;
70
71 begin
72
73 open balance_id (p_balance_name,
74 p_dimension_name);
75
76 fetch balance_id into p_defined_balance_id;
77
78 close balance_id;
79
80 return p_defined_balance_id;
81
82 end get_balance_id;
83
84 ------------------------------------------------------------------------
85 -- Procedure to merely pass all the balance results back in one hit,
86 -- rather than separate calls for each balance.
87 ------------------------------------------------------------------------
88 procedure balance_totals
89 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
90 p_tax_unit_id in pay_assignment_actions.tax_unit_id%type,
91 p_total_earnings_this_pay out nocopy number,
92 p_total_earnings_ytd out nocopy number,
93 p_total_deductions_this_pay out nocopy number,
94 p_total_deductions_ytd out nocopy number,
95 p_net_pay_this_pay out nocopy number,
96 p_net_pay_ytd out nocopy number,
97 p_direct_payments_this_pay out nocopy number,
98 p_direct_payments_ytd out nocopy number,
99 p_total_payment_this_pay out nocopy number,
100 p_total_payment_ytd out nocopy number)
101 is
102
103 ------------------------------------------------------------------------
104 -- cursor to get the defined balance ids for the various balances --3609072
105 ------------------------------------------------------------------------
106 CURSOR c_get_defined_balance_id
107 (c_dimension_name pay_balance_dimensions.dimension_name%type)
108 IS
109 SELECT decode(pbt.balance_name, 'GROSS_PAY',1
110 , 'MANDATORY_DEDUCTIONS',2
111 , 'INVOLUNTARY_DEDUCTIONS',3
112 , 'VOLUNTARY_DEDUCTIONS',4
113 , 'NET',5
114 , 'DIRECT_PAYMENTS',6
115 , 'TOTAL_PAYMENTS',7) sort_index,
116 pdb.defined_balance_id defined_balance_id
117 FROM pay_balance_types pbt,
118 pay_balance_dimensions pbd,
119 pay_defined_balances pdb
120 WHERE pbt.balance_name IN ('GROSS_PAY'
121 , 'MANDATORY_DEDUCTIONS'
122 , 'INVOLUNTARY_DEDUCTIONS'
123 , 'VOLUNTARY_DEDUCTIONS'
124 , 'NET'
125 , 'DIRECT_PAYMENTS'
126 , 'TOTAL_PAYMENTS')
127 AND pbd.dimension_name = c_dimension_name
128 AND pbt.balance_type_id = pdb.balance_type_id
129 AND pbd.balance_dimension_id = pdb.balance_dimension_id
130 AND pbt.legislation_code = 'HK'
131 ORDER BY sort_index;
132
133 v_defined_balance_id number;
134 v_gross_pay_this_run number;
135 v_gross_pay_ytd number;
136 v_mandatory_ded_this_run number;
137 v_mandatory_ded_ytd number;
138 v_involuntary_ded_this_run number;
139 v_involuntary_ded_ytd number;
140 v_voluntary_ded_this_run number;
141 v_voluntary_ded_ytd number;
142 v_net_pay_this_run number;
143 v_net_pay_ytd number;
144 v_direct_pay_this_run number;
145 v_direct_pay_ytd number;
146 v_total_pay_this_run number;
147 v_total_pay_ytd number;
148
149 begin
150
151 IF g_debug THEN
152 hr_utility.trace('Entering:' || 'pay_hk_soe.total_balances');
153 END IF;
154
155
156 /*------------- for payment values -----------------*/
157
158 /* populate a table for defined balance ids of PAYMENT balances.Bug 3609072*/
159
160 if not g_def_bal_id_populated_payment then /*Bug 4210525 */
161
162 FOR csr_rec IN c_get_defined_balance_id('_PAYMENTS')
163 LOOP
164 p_balance_value_tab_payment(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
165
166 END LOOP;
167 g_def_bal_id_populated_payment := TRUE; /*Bug 4210525 */
168
169
170 end if;
171 -- Set the TAX_UNIT_ID context. Needed for LE_YTD balances.
172
173 p_context_table(1).tax_unit_id := p_tax_unit_id;
174
175 /* get the balances using BBR. Bug 3609072 */
176
177 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id
178 ,p_defined_balance_lst=>p_balance_value_tab_payment
179 ,p_context_lst => p_context_table
180 ,p_output_table=> p_result_table);
181
182 v_gross_pay_this_run := p_result_table(1).balance_value;
183 v_mandatory_ded_this_run := p_result_table(2).balance_value;
184 v_involuntary_ded_this_run := p_result_table(3).balance_value;
185 v_voluntary_ded_this_run := p_result_table(4).balance_value;
186 v_net_pay_this_run := p_result_table(5).balance_value;
187 v_direct_pay_this_run := p_result_table(6).balance_value;
188 v_total_pay_this_run := p_result_table(7).balance_value;
189
190 if g_debug THEN
191 hr_utility.trace('_PAYMENTS balances');
192 hr_utility.trace('GROSS_PAY-->' || p_result_table(1).balance_value);
193 hr_utility.trace('MANDATORY_DEDUCTIONS-->' || p_result_table(2).balance_value);
194 hr_utility.trace('INVOLUNTARY_DEDUCTIONS-->' || p_result_table(3).balance_value);
195 hr_utility.trace('VOLUNTARY_DEDUCTIONS-->' || p_result_table(4).balance_value);
196 hr_utility.trace('NET-->' || p_result_table(5).balance_value);
197
198 hr_utility.trace('DIRECT_PAYMENTS-->' || p_result_table(6).balance_value);
199 hr_utility.trace('TOTAL_PAYMENTS-->' || p_result_table(7).balance_value);
200 end if;
201
202
203 /*----------------------------------- for YTD values --------------------*/
204
205 /* populate a table for defined balance ids of ytd balances */
206
207 if not g_def_bal_id_populated_ytd then /* Bug 4210525 */
208 FOR csr_rec IN c_get_defined_balance_id('_ASG_LE_YTD')
209 LOOP
210 p_balance_value_tab_ytd(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
211
212 END LOOP;
213 g_def_bal_id_populated_ytd := TRUE; /* Bug 4210525 */
214
215 end if;
216
217 /* get the balances using BBR. Bug 3609072 */
218
219 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
220 p_defined_balance_lst=>p_balance_value_tab_ytd,
221 p_context_lst =>p_context_table,
222 p_output_table=>p_result_table);
223
224 v_gross_pay_ytd := p_result_table(1).balance_value;
225 v_mandatory_ded_ytd := p_result_table(2).balance_value;
226 v_involuntary_ded_ytd := p_result_table(3).balance_value;
227 v_voluntary_ded_ytd := p_result_table(4).balance_value;
228 v_net_pay_ytd := p_result_table(5).balance_value;
229 v_direct_pay_ytd := p_result_table(6).balance_value;
230 v_total_pay_ytd := p_result_table(7).balance_value;
231
232 if g_debug THEN
233 hr_utility.trace('_ASG_LE_YTD balances');
234 hr_utility.trace('GROSS_PAY-->' || p_result_table(1).balance_value);
235 hr_utility.trace('MANDATORY_DEDUCTIONS-->' || p_result_table(2).balance_value);
236 hr_utility.trace('INVOLUNTARY_DEDUCTIONS-->' || p_result_table(3).balance_value);
237 hr_utility.trace('VOLUNTARY_DEDUCTIONS-->' || p_result_table(4).balance_value);
238 hr_utility.trace('NET-->' || p_result_table(5).balance_value);
239 hr_utility.trace('DIRECT_PAYMENTS-->' || p_result_table(6).balance_value);
240 hr_utility.trace('TOTAL_PAYMENTS-->' || p_result_table(7).balance_value);
241 end if;
242
243
244 -- Set the output balance amounts.
245
246 p_total_earnings_this_pay := v_gross_pay_this_run;
247 p_total_deductions_this_pay := v_mandatory_ded_this_run +
248 v_involuntary_ded_this_run +
249 v_voluntary_ded_this_run;
250 p_net_pay_this_pay := v_net_pay_this_run;
251 p_direct_payments_this_pay := v_direct_pay_this_run;
252 p_total_payment_this_pay := v_total_pay_this_run;
253
254 p_total_earnings_ytd := v_gross_pay_ytd;
255 p_total_deductions_ytd := v_mandatory_ded_ytd +
256 v_involuntary_ded_ytd +
257 v_voluntary_ded_ytd;
258 p_net_pay_ytd := v_net_pay_ytd;
259 p_direct_payments_ytd := v_direct_pay_ytd;
260 p_total_payment_ytd := v_total_pay_ytd;
261
262
263 if g_debug then
264 hr_utility.trace('Leaving:' || 'pay_hk_soe.total_balances');
265 end if;
266
267 end balance_totals;
268
269 --------------------------------------------------------------------------
270 -- --
271 -- Name : GETELEMENTS --
272 -- Type : FUNCTION --
273 -- Access : Private --
274 -- Description : Function to return SQL for some regions in the SOE --
275 -- --
276 -- Parameters : --
277 -- IN : p_assignment_action_id NUMBER --
278 -- p_classification_name VARCHAR2 --
279 -- Bug 5396046 --
280 --------------------------------------------------------------------------
281 FUNCTION getelements(p_assignment_action_id IN NUMBER
282 ,p_classification_name IN VARCHAR2
283 ) RETURN LONG
284 IS
285 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
286
287 CURSOR csr_locked_action_id
288 IS
289 SELECT max(locked_action_id)
290 FROM pay_action_interlocks
291 WHERE locking_action_id = p_assignment_action_id;
292
293 BEGIN
294
295 hr_utility.trace('Entering: ' || 'pay_hk_soe.getelements');
296
297 OPEN csr_locked_action_id;
298 FETCH csr_locked_action_id INTO l_assignment_action_id;
299 CLOSE csr_locked_action_id;
300
301 IF l_assignment_action_id IS NULL THEN
302
303 g_sql:=' SELECT element_reporting_name COL02
304 , TO_CHAR(amount, fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
305 FROM pay_hk_asg_elements_v
306 WHERE assignment_action_id = ' || p_assignment_action_id || '
307 AND (classification_name like ''%''||''' || p_classification_name || ''' ||''%'')
308 ORDER BY element_reporting_name';
309
310 ELSE
311
312 g_sql:=' SELECT phaev.element_reporting_name COL02
313 , TO_CHAR(SUM(phaev.amount), fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
314 FROM pay_hk_asg_elements_v phaev,
315 pay_action_interlocks pai
316 WHERE pai.locked_action_id = phaev.assignment_action_id
317 AND pai.locking_action_id = ' || p_assignment_action_id || '
318 AND (phaev.classification_name like ''%''||''' || p_classification_name || ''' ||''%'')
319 GROUP BY phaev.element_reporting_name
320 ORDER BY phaev.element_reporting_name';
321
322 END IF;
323
324 hr_utility.trace ('Leaving: ' || 'pay_hk_soe.getelements');
325 RETURN g_sql;
326 --
327 END getelements;
328
329
330 --------------------------------------------------------------------------
331 -- --
332 -- Name : GET_EARNINGS --
333 -- Type : FUNCTION --
334 -- Access : Public --
335 -- Description : Function to return SQL for Earnings Region --
336 -- --
337 -- Parameters : --
338 -- IN : p_assignment_action_id NUMBER --
339 -- Bug 5396046 --
340 --------------------------------------------------------------------------
341 FUNCTION get_earnings(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
342 RETURN LONG
343 IS
344
345 BEGIN
346
347 hr_utility.trace ('Entering: ' || 'pay_hk_soe.get_earnings');
348 RETURN getElements(p_assignment_action_id => p_assignment_action_id
349 ,p_classification_name => 'Earnings'
350 );
351
352 END get_earnings;
353
354 --------------------------------------------------------------------------
355 -- --
356 -- Name : GET_DEDUCTIONS --
357 -- Type : FUNCTION --
358 -- Access : Public --
359 -- Description : Function to return SQL for Earnings Region --
360 -- --
361 -- Parameters : --
362 -- IN : p_assignment_action_id NUMBER --
363 -- Bug 5396046 --
364 --------------------------------------------------------------------------
365 FUNCTION get_deductions(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
366 RETURN LONG
367 IS
368
369 BEGIN
370
371 hr_utility.trace ('Entering: ' || 'pay_hk_soe.get_deductions');
372 RETURN getElements(p_assignment_action_id => p_assignment_action_id
373 ,p_classification_name => 'Deductions'
374 );
375
376 END get_deductions;
377
378 --------------------------------------------------------------------------
379 -- --
380 -- Name : GET_EMPLOYER_LIABILITIES --
381 -- Type : FUNCTION --
382 -- Access : Public --
383 -- Description : Function to return SQL for Employer Liabilities --
384 -- Region --
385 -- --
386 -- Parameters : --
387 -- IN : p_assignment_action_id NUMBER --
388 -- Bug 5396046 --
389 --------------------------------------------------------------------------
390 FUNCTION get_employer_liabilities(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
391 RETURN LONG
392 IS
393
394 BEGIN
395
396 hr_utility.trace ('Entering: ' || 'pay_hk_soe.get_employer_liabilities');
397 RETURN getElements(p_assignment_action_id => p_assignment_action_id
398 ,p_classification_name => 'Employer Liabilities'
399 );
400
401 END get_employer_liabilities;
402
403 --------------------------------------------------------------------------
404 -- --
405 -- Name : GET_BALANCES --
406 -- Type : FUNCTION --
407 -- Access : Public --
408 -- Description : Function to return SQL for Balances Region --
409 -- --
410 -- Parameters : --
411 -- IN : p_assignment_action_id NUMBER --
412 -- Bug 5396046 --
413 --------------------------------------------------------------------------
414 FUNCTION get_balances( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
415 RETURN LONG
416 IS
417
418 l_date_earned DATE;
419 l_tax_unit_id NUMBER;
420 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
421 l_dimension pay_balance_dimensions.dimension_name%type;
422
423 CURSOR csr_locked_action_id
424 IS
425 SELECT max(locked_action_id)
426 FROM pay_action_interlocks
427 WHERE locking_action_id = p_assignment_action_id;
428
429 CURSOR csr_get_date_earned
430 IS
431 SELECT ppa.date_earned, paa.tax_unit_id
432 FROM pay_payroll_actions ppa
433 , pay_assignment_actions paa
434 WHERE ppa.payroll_action_id = paa.payroll_action_id
435 AND paa.assignment_action_id = p_assignment_action_id;
436
437 BEGIN
438
439 hr_utility.trace('Entering: '|| 'pay_hk_soe_pkg.get_balances');
440
441 OPEN csr_locked_action_id;
442 FETCH csr_locked_action_id INTO l_assignment_action_id;
443 CLOSE csr_locked_action_id;
444
445 IF l_assignment_action_id IS NULL THEN
446 l_dimension := '_ASG_LE_RUN';
447 l_assignment_action_id := p_assignment_action_id;
448 ELSE
449 l_dimension := '_ASG_LE_PTD';
450 END IF;
451
452 OPEN csr_get_date_earned;
453 FETCH csr_get_date_earned INTO l_date_earned, l_tax_unit_id;
454 CLOSE csr_get_date_earned;
455
456 g_sql := 'SELECT /*+ USE_NL(pbt) */ NVL(pbt.reporting_name, pbt.balance_name) COL04
457 , TO_CHAR(pay_balance_pkg.get_value( pdb_ptd.defined_balance_id
458 , ' || l_assignment_action_id || '
459 , ' || l_tax_unit_id || '
460 , NULL
461 , NULL
462 , NULL
463 , NULL
464 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
465 , NULL
466 , NULL)
467 , fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
468 , TO_CHAR(pay_balance_pkg.get_value( pdb_ytd.defined_balance_id
469 , ' || l_assignment_action_id || '
470 , ' || l_tax_unit_id || '
471 , NULL
472 , NULL
473 , NULL
474 , NULL
475 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
476 , NULL
477 , NULL)
478 , fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL18
479 FROM pay_balance_types pbt
480 , pay_balance_dimensions pbd_ptd
481 , pay_balance_dimensions pbd_ytd
482 , pay_defined_balances pdb_ptd
483 , pay_defined_balances pdb_ytd
484 WHERE pbt.balance_name IN (''GROSS_PAY''
485 , ''MANDATORY_DEDUCTIONS''
486 , ''INVOLUNTARY_DEDUCTIONS''
487 , ''VOLUNTARY_DEDUCTIONS''
488 , ''NET''
489 , ''DIRECT_PAYMENTS''
490 , ''TOTAL_PAYMENTS'')
491 AND pbd_ptd.dimension_name = '''|| l_dimension ||'''
492 AND pbd_ytd.dimension_name = ''_ASG_LE_YTD''
493 AND pbt.legislation_code = ''HK''
494 AND pbd_ptd.legislation_code = ''HK''
495 AND pbd_ytd.legislation_code = ''HK''
496 AND pbd_ptd.balance_dimension_id = pdb_ptd.balance_dimension_id
497 AND pbt.balance_type_id = pdb_ptd.balance_type_id
498 AND pbd_ytd.balance_dimension_id = pdb_ytd.balance_dimension_id
499 AND pbt.balance_type_id = pdb_ytd.balance_type_id
500 ORDER BY decode(pbt.balance_name, ''GROSS_PAY'',1
501 , ''MANDATORY_DEDUCTIONS'',2
502 , ''INVOLUNTARY_DEDUCTIONS'',3
503 , ''VOLUNTARY_DEDUCTIONS'',4
504 , ''NET'',5
505 , ''DIRECT_PAYMENTS'',6
506 , ''TOTAL_PAYMENTS'',7)';
507
508 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_balances');
509
510 RETURN g_sql;
511
512 END get_balances;
513
514 --------------------------------------------------------------------------
515 -- --
516 -- Name : GET_PAYMENT_METHODS --
517 -- Type : FUNCTION --
518 -- Access : Public --
519 -- Description : Function to return SQL for Payments Method Region --
520 -- --
521 -- Parameters : --
522 -- IN : p_assignment_action_id NUMBER --
523 -- Bug 5396046 --
524 --------------------------------------------------------------------------
525 FUNCTION get_payment_methods(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
526 RETURN LONG
527 IS
528 BEGIN
529
530 hr_utility.trace('Entering: '|| 'pay_hk_soe_pkg.get_payment_methods');
531
532 g_sql := 'SELECT /*+
533 INDEX(opm PAY_ORG_PAYMENT_METHODS_F_PK) */
534 org_payment_method_name COL01
535 , pay_soe_util.getBankDetails('':legislation_code''
536 ,ppm.external_account_id
537 ,''BANK_NAME''
538 ,NULL) COL02
539 , pay_soe_util.getBankDetails('':legislation_code''
540 ,ppm.external_account_id
541 ,''BANK_ACCOUNT_NUMBER''
542 ,NULL) COL03
543 , TO_CHAR(:G_CURRENCY_CODE) COL04
544 , to_char(pp.value
545 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
546 FROM pay_pre_payments pp
547 , pay_personal_payment_methods_f ppm
548 , pay_org_payment_methods_f opm
549 , pay_payment_types_tl pt
550 WHERE pp.assignment_action_id IN
551 (SELECT ai.locking_action_id
552 FROM pay_action_interlocks ai
553 WHERE ai.locked_action_id :action_clause)
554 AND pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
555 AND :effective_date BETWEEN ppm.effective_start_date(+)
556 AND ppm.effective_end_date(+)
557 AND pp.org_payment_method_id = opm.org_payment_method_id
558 AND :effective_date BETWEEN opm.effective_start_date
559 AND opm.effective_end_date
560 AND opm.payment_type_id = pt.payment_type_id
561 AND pt.language = USERENV(''LANG'')';
562
563 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_payment_methods');
564
565 RETURN g_sql;
566
567 END get_payment_methods;
568
569 --------------------------------------------------------------------------
570 -- --
571 -- Name : GET_OTHER_ELEMENT_INFORMATION --
572 -- Type : FUNCTION --
573 -- Access : Public --
574 -- Description : Function to return SQL for Other Element --
575 -- Information Region --
576 -- --
577 -- Parameters : --
578 -- IN : p_assignment_action_id NUMBER --
579 -- Bug 5396046 --
580 --------------------------------------------------------------------------
581 FUNCTION get_other_element_information(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
582 RETURN LONG
583 IS
584
585 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
586 l_business_group_id per_business_groups.business_group_id%TYPE;
587 l_effective_date pay_payroll_actions.effective_date%TYPE;
588
589 CURSOR csr_prepayment
590 IS
591 SELECT MAX(locked_action_id)
592 FROM pay_action_interlocks
593 WHERE locking_action_id = p_assignment_action_id;
594
595 CURSOR csr_get_bg_id
596 IS
597 SELECT ppa.business_group_id,ppa.effective_date
598 FROM pay_payroll_actions ppa
599 ,pay_assignment_actions paa
600 WHERE ppa.payroll_action_id = paa.payroll_action_id
601 AND paa.assignment_action_id = p_assignment_action_id;
602
603 BEGIN
604
605 hr_utility.trace('Entering: ' || 'pay_hk_soe_pkg.get_other_element_information');
606
607 OPEN csr_prepayment;
608 FETCH csr_prepayment INTO l_assignment_action_id;
609 CLOSE csr_prepayment;
610
611 IF l_assignment_action_id IS NOT NULL THEN
612 p_assignment_action_id := l_assignment_action_id;
613 END IF;
614
615 OPEN csr_get_bg_id;
616 FETCH csr_get_bg_id INTO l_business_group_id,l_effective_date;
617 CLOSE csr_get_bg_id;
618
619 g_sql :=
620 'SELECT org.org_information7 COL02
621 , to_char(sum(prv.result_value)) COL16 /* BUG 5972299 */
622 FROM pay_run_result_values prv,
623 pay_run_results prr,
624 hr_organization_information_v org,
625 pay_input_values_f pivf
626 WHERE prr.status IN (''P'',''PA'')
627 AND org.organization_id = ' || l_business_group_id || '
628 AND org.org_information_context = ''Business Group:SOE Detail''
629 AND org.org_information1 = ''ELEMENT''
630 AND prv.run_result_id = prr.run_result_id
631 AND pivf.input_value_id = prv.input_value_id
632 AND ''' || l_effective_date || ''' between pivf.effective_start_date and pivf.effective_end_date
633 AND substr(pivf.uom,1,1) IN (''M'',''I'',''H'')
634 AND prr.assignment_action_id = ' || p_assignment_action_id || '
635 AND prr.element_type_id = org.org_information2
636 AND prv.input_value_id = org.org_information3
637 AND prv.result_value IS NOT NULL
638 group by prr.element_type_id,
639 org.org_information7
640 union all
641 SELECT org.org_information7 COL02
642 , to_char(prv.result_value) COL16
643 FROM pay_run_result_values prv,
644 pay_run_results prr,
645 hr_organization_information_v org,
646 pay_input_values_f pivf
647 WHERE prr.status IN (''P'',''PA'')
648 AND org.organization_id = ' || l_business_group_id || '
649 AND org.org_information_context = ''Business Group:SOE Detail''
650 AND org.org_information1 = ''ELEMENT''
651 AND prv.run_result_id = prr.run_result_id
652 AND pivf.input_value_id = prv.input_value_id
653 AND ''' || l_effective_date || ''' between pivf.effective_start_date and pivf.effective_end_date
654 AND substr(pivf.uom,1,1) NOT IN (''M'',''I'',''H'')
655 AND prr.assignment_action_id = ' || p_assignment_action_id || '
656 AND prr.element_type_id = org.org_information2
657 AND prv.input_value_id = org.org_information3
658 AND prv.result_value IS NOT NULL';
659
660 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_other_element_information');
661
662 RETURN g_sql;
663
664 END get_other_element_information;
665
666 --------------------------------------------------------------------------
667 -- --
668 -- Name : GET_OTHER_BALANCE_INFORMATION --
669 -- Type : FUNCTION --
670 -- Access : Public --
671 -- Description : Function to return SQL for Other Balance --
672 -- Information Region --
673 -- --
674 -- Parameters : --
675 -- IN : p_assignment_action_id NUMBER --
676 -- Bug 5396046 --
677 --------------------------------------------------------------------------
678 FUNCTION get_other_balance_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
679 RETURN LONG
680 IS
681
682 l_date_earned DATE;
683 l_tax_unit_id NUMBER;
684 l_business_group_id per_business_groups.business_group_id%TYPE;
685 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
686
687 CURSOR csr_locked_action_id
688 IS
689 SELECT max(locked_action_id)
690 FROM pay_action_interlocks
691 WHERE locking_action_id = p_assignment_action_id;
692
693 CURSOR csr_get_date_earned
694 IS
695 SELECT ppa.date_earned, ppa.business_group_id, paa.tax_unit_id
696 FROM pay_payroll_actions ppa
697 , pay_assignment_actions paa
698 WHERE ppa.payroll_action_id = paa.payroll_action_id
699 AND paa.assignment_action_id = p_assignment_action_id;
700
701 BEGIN
702
703
704 hr_utility.trace('Entering: ' || 'pay_hk_soe_pkg.get_other_balance_information');
705
706 OPEN csr_locked_action_id;
707 FETCH csr_locked_action_id INTO l_assignment_action_id;
708 CLOSE csr_locked_action_id;
709
710 IF l_assignment_action_id IS NULL THEN
711 l_assignment_action_id := p_assignment_action_id;
712 END IF;
713
714 OPEN csr_get_date_earned;
715 FETCH csr_get_date_earned INTO l_date_earned, l_business_group_id, l_tax_unit_id;
716 CLOSE csr_get_date_earned;
717
718 g_sql := 'SELECT org.org_information7 COL02
719 , TO_CHAR(pay_balance_pkg.get_value( pdb.defined_balance_id
720 , ' || l_assignment_action_id || '
721 , ' || l_tax_unit_id || '
722 , NULL
723 , NULL
724 , NULL
725 , NULL
726 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
727 , NULL
728 , NULL)
729 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
730 FROM pay_defined_balances pdb
731 , hr_organization_information_v org
732 WHERE org.organization_id = ' || l_business_group_id || '
733 AND org.org_information_context = ''Business Group:SOE Detail''
734 AND org.org_information1 = ''BALANCE''
735 AND pdb.balance_type_id = org.org_information4
736 AND pdb.balance_dimension_id = org.org_information5
737 AND pay_balance_pkg.get_value( pdb.defined_balance_id
738 , ' || l_assignment_action_id || '
739 , ' || l_tax_unit_id || '
740 , NULL
741 , NULL
742 , NULL
743 , NULL
744 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
745 , NULL
746 , NULL) <> 0';
747
748 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_other_balance_information');
749
750 RETURN g_sql;
751
752 END get_other_balance_information;
753
754 --------------------------------------------------------------------------
755 -- --
756 -- Name : GET_ANNUAL_LEAVE_INFORMATION --
757 -- Type : FUNCTION --
758 -- Access : Public --
759 -- Description : Function to return SQL for Annual Leave --
760 -- Information Region --
761 -- --
762 -- Parameters : --
763 -- IN : p_assignment_action_id NUMBER --
764 -- Bug 5396046 --
765 --------------------------------------------------------------------------
766 FUNCTION get_annual_leave_info(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
767 RETURN LONG
768 IS
769
770 CURSOR csr_get_annual_leave_details(p_payroll_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
771 IS
772 SELECT pap.accrual_plan_name
773 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
774 ,hr_general.decode_lookup('HOURS_OR_DAYS',pap.accrual_units_of_measure)
775 ,ppa.payroll_id
776 ,pap.business_group_id
777 ,pap.accrual_plan_id
778 ,paa.assignment_id
779 FROM pay_accrual_plans pap
780 ,pay_element_types_f pet
781 ,pay_element_links_f pel
782 ,pay_element_entries_f pee
783 ,pay_assignment_actions paa
784 ,pay_payroll_actions ppa
785 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
786 AND pel.element_type_id = pet.element_type_id
787 AND pee.element_link_id = pel.element_link_id
788 AND paa.assignment_id = pee.assignment_id
789 AND ppa.payroll_action_id = paa.payroll_action_id
790 AND pap.accrual_category = 'HKAL'
791 AND ppa.action_type IN ('R','Q')
792 AND ppa.action_status = 'C'
793 AND ppa.date_earned BETWEEN pet.effective_start_date
794 AND pet.effective_end_date
795 AND ppa.date_earned BETWEEN pel.effective_start_date
796 AND pel.effective_end_date
797 AND ppa.date_earned BETWEEN pee.effective_start_date
798 AND pee.effective_end_date
799 AND paa.assignment_action_id = p_payroll_assignment_action_id;
800
801 /*Bug 6074090 Added parameter c_assignment_action_id as date earned is not populated for pre payment action id,
802 Date earned of Payroll needs to be fetched */
803
804 CURSOR csr_get_date_earned(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
805 IS
806 SELECT ppa.date_earned
807 FROM pay_payroll_actions ppa
808 , pay_assignment_actions paa
809 WHERE ppa.payroll_action_id = paa.payroll_action_id
810 AND paa.assignment_action_id = c_assignment_action_id;
811
812 CURSOR csr_prepayment
813 IS
814 SELECT MAX(locked_action_id)
815 FROM pay_action_interlocks
816 WHERE locking_action_id = p_assignment_action_id;
817
818 l_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
819 l_accrual_category pay_accrual_plans.accrual_category%TYPE;
820 l_uom pay_accrual_plans.accrual_units_of_measure%TYPE;
821 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
822 l_business_group_id per_business_groups.business_group_id%TYPE;
823 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE;
824 l_assignment_id per_assignments_f.assignment_id%TYPE;
825 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
826 l_annual_leave_balance NUMBER;
827 l_ovn NUMBER;
828 l_leave_taken NUMBER;
829 l_start_date DATE;
830 l_end_date DATE;
831 l_accrual_end_date DATE;
832 l_date_earned DATE;
833 l_accrual NUMBER;
834 l_total_leave_taken NUMBER;
835
836 BEGIN
837
838
839 hr_utility.trace('Entering: ' || 'pay_hk_soe_pkg.get_annual_leave_information');
840
841 OPEN csr_prepayment;
842 FETCH csr_prepayment INTO l_assignment_action_id;
843 CLOSE csr_prepayment;
844
845 IF l_assignment_action_id IS NULL THEN
846 l_assignment_action_id := p_assignment_action_id;
847 END IF;
848
849 OPEN csr_get_annual_leave_details(l_assignment_action_id);
850 FETCH csr_get_annual_leave_details
851 INTO l_plan_name
852 , l_accrual_category
853 , l_uom
854 , l_payroll_id
855 , l_business_group_id
856 , l_accrual_plan_id
857 , l_assignment_id;
858
859 /* Bug 6928705 - Added check to call accrual calculations function
860 only if Accrual Plan ID is available */
861 IF csr_get_annual_leave_details%FOUND
862 THEN
863
864 OPEN csr_get_date_earned(l_assignment_action_id); /*Bug 6074090 */
865 FETCH csr_get_date_earned INTO l_date_earned;
866 CLOSE csr_get_date_earned;
867
868 per_accrual_calc_functions.get_net_accrual
869 ( p_assignment_id => l_assignment_id
870 , p_plan_id => l_accrual_plan_id
871 , p_payroll_id => l_payroll_id
872 , p_business_group_id => l_business_group_id
873 , p_calculation_date => l_date_earned
874 , p_start_date => l_start_date
875 , p_end_date => l_end_date
876 , p_accrual_end_date => l_accrual_end_date
877 , p_accrual => l_accrual
878 , p_net_entitlement => l_annual_leave_balance
879 );
880
881 END IF; /* Bug 10276550 */
882 CLOSE csr_get_annual_leave_details;
883
884 /* Bug 10276550 Add check to return SQL only if l_annual_leave_balance is not null */
885 IF l_annual_leave_balance IS NOT NULL
886 THEN
887 g_sql := 'SELECT ''' || l_plan_name || ''' COL01
888 , TO_CHAR(' || l_annual_leave_balance|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
889 , ''' || l_uom || ''' COL03
890 FROM DUAL';
891 END IF;
892
893 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_annual_leave_information');
894
895 RETURN g_sql;
896
897 END get_annual_leave_info;
898
899 --------------------------------------------------------------------------
900 -- --
901 -- Name : GET_LEAVE_TAKEN --
902 -- Type : FUNCTION --
903 -- Access : Public --
904 -- Description : Function to return SQL for Leave Taken Region --
905 -- --
906 -- Parameters : --
907 -- IN : p_assignment_action_id NUMBER --
908 -- Bug 5396046 --
909 --------------------------------------------------------------------------
910 FUNCTION get_leave_taken(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
911 RETURN LONG
912 IS
913
914 CURSOR csr_prepayment
915 IS
916 SELECT MAX(locked_action_id)
917 FROM pay_action_interlocks
918 WHERE locking_action_id = p_assignment_action_id;
919
920 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
921
922 BEGIN
923
924
925 hr_utility.trace('Entering: ' || 'pay_hk_soe_pkg.get_leave_taken');
926
927 OPEN csr_prepayment;
928 FETCH csr_prepayment INTO l_assignment_action_id;
929 CLOSE csr_prepayment;
930
931 IF l_assignment_action_id IS NOT NULL THEN
932 p_assignment_action_id := l_assignment_action_id;
933 END IF;
934
935 g_sql :=
936 ' SELECT pet.reporting_name COL01
937 ,TO_CHAR(decode(pet.processing_type,''R'',greatest(pab.date_start,PTP.START_DATE),pab.date_start),''DD-Mon-YYYY'') COL02
938 ,TO_CHAR(decode(pet.processing_type,''R'',least(pab.date_end,PTP.END_DATE),pab.date_end),''DD-Mon-YYYY'') COL03
939 ,hr_general.decode_lookup(''HOURS_OR_DAYS'',pat.HOURS_OR_DAYS) COL04
940 ,TO_CHAR(decode(pet.processing_type,''R'',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours))) COL16
941 FROM pay_assignment_actions paa
942 ,pay_payroll_actions ppa
943 ,pay_run_results prr
944 ,pay_run_result_values prrv
945 ,per_time_periods ptp
946 ,pay_element_types_f pet
947 ,pay_input_values_f piv
948 ,pay_element_entries_f pee
949 ,per_absence_attendance_types pat
950 ,per_absence_attendances pab
951 WHERE paa.assignment_action_id = ' || p_assignment_action_id || '
952 AND ppa.payroll_action_id = paa.payroll_action_id
953 AND ppa.action_type IN (''Q'',''R'')
954 AND ptp.time_period_id = ppa.time_period_id
955 AND paa.assignment_action_id = prr.assignment_action_id
956 AND pet.element_type_id = prr.element_type_id
957 AND pet.element_type_id = piv.element_type_id
958 AND piv.input_value_id = pat.input_value_id
959 AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
960 AND pab.absence_attendance_id = pee.creator_id
961 AND pee.creator_type = ''A''
962 AND pee.assignment_id = paa.assignment_id
963 AND pee.element_entry_id = prr.source_id
964 AND piv.input_value_id = prrv.input_value_id
965 AND prr.run_result_id = prrv.run_result_id
966 AND ppa.effective_date BETWEEN pet.effective_start_date
967 AND pet.effective_end_date
968 AND ppa.effective_date BETWEEN pee.effective_start_date
969 AND pee.effective_end_date
970 AND ppa.effective_date BETWEEN piv.effective_start_date
971 AND piv.effective_end_date';
972
973
974 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_leave_taken');
975
976 RETURN g_sql;
977
978 END get_leave_taken;
979
980 begin
981 g_def_bal_id_populated_payment := FALSE; /* Bug 4210525 */
982 g_def_bal_id_populated_ytd := FALSE; /* Bug 4210525 */
983 g_debug := hr_utility.debug_enabled;
984
985 end pay_hk_soe_pkg;