[Home] [Help]
PACKAGE BODY: APPS.PAY_HK_SOE_PKG
Source
1 package body pay_hk_soe_pkg as
2 /* $Header: pyhksoe.pkb 120.9.12010000.2 2008/08/06 07:22:54 ubhat ship $ */
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(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
320
317 AND pai.locking_action_id = ' || p_assignment_action_id || '
318 AND (phaev.classification_name like ''%''||''' || p_classification_name || ''' ||''%'')
319 ORDER BY phaev.element_reporting_name';
321 END IF;
322
323 hr_utility.trace ('Leaving: ' || 'pay_hk_soe.getelements');
324 RETURN g_sql;
325 --
326 END getelements;
327
328
329 --------------------------------------------------------------------------
330 -- --
331 -- Name : GET_EARNINGS --
332 -- Type : FUNCTION --
333 -- Access : Public --
334 -- Description : Function to return SQL for Earnings Region --
335 -- --
336 -- Parameters : --
337 -- IN : p_assignment_action_id NUMBER --
338 -- Bug 5396046 --
339 --------------------------------------------------------------------------
340 FUNCTION get_earnings(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
341 RETURN LONG
342 IS
343
344 BEGIN
345
346 hr_utility.trace ('Entering: ' || 'pay_hk_soe.get_earnings');
347 RETURN getElements(p_assignment_action_id => p_assignment_action_id
348 ,p_classification_name => 'Earnings'
349 );
350
351 END get_earnings;
352
353 --------------------------------------------------------------------------
354 -- --
355 -- Name : GET_DEDUCTIONS --
356 -- Type : FUNCTION --
357 -- Access : Public --
358 -- Description : Function to return SQL for Earnings Region --
359 -- --
360 -- Parameters : --
361 -- IN : p_assignment_action_id NUMBER --
362 -- Bug 5396046 --
363 --------------------------------------------------------------------------
364 FUNCTION get_deductions(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
365 RETURN LONG
366 IS
367
368 BEGIN
369
370 hr_utility.trace ('Entering: ' || 'pay_hk_soe.get_deductions');
371 RETURN getElements(p_assignment_action_id => p_assignment_action_id
372 ,p_classification_name => 'Deductions'
373 );
374
375 END get_deductions;
376
377 --------------------------------------------------------------------------
378 -- --
379 -- Name : GET_EMPLOYER_LIABILITIES --
380 -- Type : FUNCTION --
381 -- Access : Public --
382 -- Description : Function to return SQL for Employer Liabilities --
383 -- Region --
384 -- --
385 -- Parameters : --
386 -- IN : p_assignment_action_id NUMBER --
387 -- Bug 5396046 --
388 --------------------------------------------------------------------------
389 FUNCTION get_employer_liabilities(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
390 RETURN LONG
391 IS
392
393 BEGIN
394
395 hr_utility.trace ('Entering: ' || 'pay_hk_soe.get_employer_liabilities');
396 RETURN getElements(p_assignment_action_id => p_assignment_action_id
397 ,p_classification_name => 'Employer Liabilities'
398 );
399
400 END get_employer_liabilities;
401
402 --------------------------------------------------------------------------
403 -- --
404 -- Name : GET_BALANCES --
405 -- Type : FUNCTION --
406 -- Access : Public --
407 -- Description : Function to return SQL for Balances Region --
411 -- Bug 5396046 --
408 -- --
409 -- Parameters : --
410 -- IN : p_assignment_action_id NUMBER --
412 --------------------------------------------------------------------------
413 FUNCTION get_balances( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
414 RETURN LONG
415 IS
416
417 l_date_earned DATE;
418 l_tax_unit_id NUMBER;
419 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
420 l_dimension pay_balance_dimensions.dimension_name%type;
421
422 CURSOR csr_locked_action_id
423 IS
424 SELECT max(locked_action_id)
425 FROM pay_action_interlocks
426 WHERE locking_action_id = p_assignment_action_id;
427
428 CURSOR csr_get_date_earned
429 IS
430 SELECT ppa.date_earned, paa.tax_unit_id
431 FROM pay_payroll_actions ppa
432 , pay_assignment_actions paa
433 WHERE ppa.payroll_action_id = paa.payroll_action_id
434 AND paa.assignment_action_id = p_assignment_action_id;
435
436 BEGIN
437
438 hr_utility.trace('Entering: '|| 'pay_hk_soe_pkg.get_balances');
439
440 OPEN csr_locked_action_id;
441 FETCH csr_locked_action_id INTO l_assignment_action_id;
442 CLOSE csr_locked_action_id;
443
444 IF l_assignment_action_id IS NULL THEN
445 l_dimension := '_ASG_LE_RUN';
446 l_assignment_action_id := p_assignment_action_id;
447 ELSE
448 l_dimension := '_ASG_LE_PTD';
449 END IF;
450
451 OPEN csr_get_date_earned;
452 FETCH csr_get_date_earned INTO l_date_earned, l_tax_unit_id;
453 CLOSE csr_get_date_earned;
454
455 g_sql := 'SELECT /*+ USE_NL(pbt) */ NVL(pbt.reporting_name, pbt.balance_name) COL04
456 , TO_CHAR(pay_balance_pkg.get_value( pdb_ptd.defined_balance_id
457 , ' || l_assignment_action_id || '
458 , ' || l_tax_unit_id || '
459 , NULL
460 , NULL
461 , NULL
462 , NULL
463 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
464 , NULL
465 , NULL)
466 , fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
467 , TO_CHAR(pay_balance_pkg.get_value( pdb_ytd.defined_balance_id
468 , ' || l_assignment_action_id || '
469 , ' || l_tax_unit_id || '
470 , NULL
471 , NULL
472 , NULL
473 , NULL
474 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
475 , NULL
476 , NULL)
477 , fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL18
478 FROM pay_balance_types pbt
479 , pay_balance_dimensions pbd_ptd
480 , pay_balance_dimensions pbd_ytd
481 , pay_defined_balances pdb_ptd
482 , pay_defined_balances pdb_ytd
483 WHERE pbt.balance_name IN (''GROSS_PAY''
484 , ''MANDATORY_DEDUCTIONS''
485 , ''INVOLUNTARY_DEDUCTIONS''
486 , ''VOLUNTARY_DEDUCTIONS''
487 , ''NET''
488 , ''DIRECT_PAYMENTS''
489 , ''TOTAL_PAYMENTS'')
490 AND pbd_ptd.dimension_name = '''|| l_dimension ||'''
491 AND pbd_ytd.dimension_name = ''_ASG_LE_YTD''
492 AND pbt.legislation_code = ''HK''
493 AND pbd_ptd.legislation_code = ''HK''
494 AND pbd_ytd.legislation_code = ''HK''
495 AND pbd_ptd.balance_dimension_id = pdb_ptd.balance_dimension_id
496 AND pbt.balance_type_id = pdb_ptd.balance_type_id
497 AND pbd_ytd.balance_dimension_id = pdb_ytd.balance_dimension_id
498 AND pbt.balance_type_id = pdb_ytd.balance_type_id
499 ORDER BY decode(pbt.balance_name, ''GROSS_PAY'',1
500 , ''MANDATORY_DEDUCTIONS'',2
501 , ''INVOLUNTARY_DEDUCTIONS'',3
502 , ''VOLUNTARY_DEDUCTIONS'',4
503 , ''NET'',5
504 , ''DIRECT_PAYMENTS'',6
505 , ''TOTAL_PAYMENTS'',7)';
506
507 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_balances');
508
509 RETURN g_sql;
510
511 END get_balances;
512
513 --------------------------------------------------------------------------
514 -- --
515 -- Name : GET_PAYMENT_METHODS --
516 -- Type : FUNCTION --
517 -- Access : Public --
521 -- IN : p_assignment_action_id NUMBER --
518 -- Description : Function to return SQL for Payments Method Region --
519 -- --
520 -- Parameters : --
522 -- Bug 5396046 --
523 --------------------------------------------------------------------------
524 FUNCTION get_payment_methods(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
525 RETURN LONG
526 IS
527 BEGIN
528
529 hr_utility.trace('Entering: '|| 'pay_hk_soe_pkg.get_payment_methods');
530
531 g_sql := 'SELECT /*+
532 INDEX(opm PAY_ORG_PAYMENT_METHODS_F_PK) */
533 org_payment_method_name COL01
534 , pay_soe_util.getBankDetails('':legislation_code''
535 ,ppm.external_account_id
536 ,''BANK_NAME''
537 ,NULL) COL02
538 , pay_soe_util.getBankDetails('':legislation_code''
539 ,ppm.external_account_id
540 ,''BANK_ACCOUNT_NUMBER''
541 ,NULL) COL03
542 , TO_CHAR(:G_CURRENCY_CODE) COL04
543 , to_char(pp.value
544 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
545 FROM pay_pre_payments pp
546 , pay_personal_payment_methods_f ppm
547 , pay_org_payment_methods_f opm
548 , pay_payment_types_tl pt
549 WHERE pp.assignment_action_id IN
550 (SELECT ai.locking_action_id
551 FROM pay_action_interlocks ai
552 WHERE ai.locked_action_id :action_clause)
553 AND pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
554 AND :effective_date BETWEEN ppm.effective_start_date(+)
555 AND ppm.effective_end_date(+)
556 AND pp.org_payment_method_id = opm.org_payment_method_id
557 AND :effective_date BETWEEN opm.effective_start_date
558 AND opm.effective_end_date
559 AND opm.payment_type_id = pt.payment_type_id
560 AND pt.language = USERENV(''LANG'')';
561
562 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_payment_methods');
563
564 RETURN g_sql;
565
566 END get_payment_methods;
567
568 --------------------------------------------------------------------------
569 -- --
570 -- Name : GET_OTHER_ELEMENT_INFORMATION --
571 -- Type : FUNCTION --
572 -- Access : Public --
573 -- Description : Function to return SQL for Other Element --
574 -- Information Region --
575 -- --
576 -- Parameters : --
577 -- IN : p_assignment_action_id NUMBER --
578 -- Bug 5396046 --
579 --------------------------------------------------------------------------
580 FUNCTION get_other_element_information(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
581 RETURN LONG
582 IS
583
584 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
585 l_business_group_id per_business_groups.business_group_id%TYPE;
586 l_effective_date pay_payroll_actions.effective_date%TYPE;
587
588 CURSOR csr_prepayment
589 IS
590 SELECT MAX(locked_action_id)
591 FROM pay_action_interlocks
592 WHERE locking_action_id = p_assignment_action_id;
593
594 CURSOR csr_get_bg_id
595 IS
596 SELECT ppa.business_group_id,ppa.effective_date
597 FROM pay_payroll_actions ppa
598 ,pay_assignment_actions paa
599 WHERE ppa.payroll_action_id = paa.payroll_action_id
600 AND paa.assignment_action_id = p_assignment_action_id;
601
602 BEGIN
603
604 hr_utility.trace('Entering: ' || 'pay_hk_soe_pkg.get_other_element_information');
605
606 OPEN csr_prepayment;
607 FETCH csr_prepayment INTO l_assignment_action_id;
608 CLOSE csr_prepayment;
609
610 IF l_assignment_action_id IS NOT NULL THEN
611 p_assignment_action_id := l_assignment_action_id;
612 END IF;
613
614 OPEN csr_get_bg_id;
615 FETCH csr_get_bg_id INTO l_business_group_id,l_effective_date;
616 CLOSE csr_get_bg_id;
617
618 g_sql :=
619 'SELECT org.org_information7 COL02
620 , to_char(sum(prv.result_value)) COL16 /* BUG 5972299 */
621 FROM pay_run_result_values prv,
622 pay_run_results prr,
623 hr_organization_information_v org,
624 pay_input_values_f pivf
625 WHERE prr.status IN (''P'',''PA'')
629 AND prv.run_result_id = prr.run_result_id
626 AND org.organization_id = ' || l_business_group_id || '
627 AND org.org_information_context = ''Business Group:SOE Detail''
628 AND org.org_information1 = ''ELEMENT''
630 AND pivf.input_value_id = prv.input_value_id
631 AND ''' || l_effective_date || ''' between pivf.effective_start_date and pivf.effective_end_date
632 AND substr(pivf.uom,1,1) IN (''M'',''I'',''H'')
633 AND prr.assignment_action_id = ' || p_assignment_action_id || '
634 AND prr.element_type_id = org.org_information2
635 AND prv.input_value_id = org.org_information3
636 AND prv.result_value IS NOT NULL
637 group by prr.element_type_id,
638 org.org_information7
639 union all
640 SELECT org.org_information7 COL02
641 , to_char(prv.result_value) COL16
642 FROM pay_run_result_values prv,
643 pay_run_results prr,
644 hr_organization_information_v org,
645 pay_input_values_f pivf
646 WHERE prr.status IN (''P'',''PA'')
647 AND org.organization_id = ' || l_business_group_id || '
648 AND org.org_information_context = ''Business Group:SOE Detail''
649 AND org.org_information1 = ''ELEMENT''
650 AND prv.run_result_id = prr.run_result_id
651 AND pivf.input_value_id = prv.input_value_id
652 AND ''' || l_effective_date || ''' between pivf.effective_start_date and pivf.effective_end_date
653 AND substr(pivf.uom,1,1) NOT IN (''M'',''I'',''H'')
654 AND prr.assignment_action_id = ' || p_assignment_action_id || '
655 AND prr.element_type_id = org.org_information2
656 AND prv.input_value_id = org.org_information3
657 AND prv.result_value IS NOT NULL';
658
659 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_other_element_information');
660
661 RETURN g_sql;
662
663 END get_other_element_information;
664
665 --------------------------------------------------------------------------
666 -- --
667 -- Name : GET_OTHER_BALANCE_INFORMATION --
668 -- Type : FUNCTION --
669 -- Access : Public --
670 -- Description : Function to return SQL for Other Balance --
671 -- Information Region --
672 -- --
673 -- Parameters : --
674 -- IN : p_assignment_action_id NUMBER --
675 -- Bug 5396046 --
676 --------------------------------------------------------------------------
677 FUNCTION get_other_balance_information(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
678 RETURN LONG
679 IS
680
681 l_date_earned DATE;
682 l_tax_unit_id NUMBER;
683 l_business_group_id per_business_groups.business_group_id%TYPE;
684 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
685
686 CURSOR csr_locked_action_id
687 IS
688 SELECT max(locked_action_id)
689 FROM pay_action_interlocks
690 WHERE locking_action_id = p_assignment_action_id;
691
692 CURSOR csr_get_date_earned
693 IS
694 SELECT ppa.date_earned, ppa.business_group_id, paa.tax_unit_id
695 FROM pay_payroll_actions ppa
696 , pay_assignment_actions paa
697 WHERE ppa.payroll_action_id = paa.payroll_action_id
698 AND paa.assignment_action_id = p_assignment_action_id;
699
700 BEGIN
701
702
703 hr_utility.trace('Entering: ' || 'pay_hk_soe_pkg.get_other_balance_information');
704
705 OPEN csr_locked_action_id;
706 FETCH csr_locked_action_id INTO l_assignment_action_id;
707 CLOSE csr_locked_action_id;
708
709 IF l_assignment_action_id IS NULL THEN
710 l_assignment_action_id := p_assignment_action_id;
711 END IF;
712
713 OPEN csr_get_date_earned;
714 FETCH csr_get_date_earned INTO l_date_earned, l_business_group_id, l_tax_unit_id;
715 CLOSE csr_get_date_earned;
716
717 g_sql := 'SELECT org.org_information7 COL02
718 , TO_CHAR(pay_balance_pkg.get_value( pdb.defined_balance_id
719 , ' || l_assignment_action_id || '
720 , ' || l_tax_unit_id || '
721 , NULL
722 , NULL
723 , NULL
724 , NULL
725 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
726 , NULL
727 , NULL)
728 ,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
729 FROM pay_defined_balances pdb
730 , hr_organization_information_v org
731 WHERE org.organization_id = ' || l_business_group_id || '
732 AND org.org_information_context = ''Business Group:SOE Detail''
733 AND org.org_information1 = ''BALANCE''
734 AND pdb.balance_type_id = org.org_information4
738 , ' || l_tax_unit_id || '
735 AND pdb.balance_dimension_id = org.org_information5
736 AND pay_balance_pkg.get_value( pdb.defined_balance_id
737 , ' || l_assignment_action_id || '
739 , NULL
740 , NULL
741 , NULL
742 , NULL
743 , fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
744 , NULL
745 , NULL) <> 0';
746
747 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_other_balance_information');
748
749 RETURN g_sql;
750
751 END get_other_balance_information;
752
753 --------------------------------------------------------------------------
754 -- --
755 -- Name : GET_ANNUAL_LEAVE_INFORMATION --
756 -- Type : FUNCTION --
757 -- Access : Public --
758 -- Description : Function to return SQL for Annual Leave --
759 -- Information Region --
760 -- --
761 -- Parameters : --
762 -- IN : p_assignment_action_id NUMBER --
763 -- Bug 5396046 --
764 --------------------------------------------------------------------------
765 FUNCTION get_annual_leave_info(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
766 RETURN LONG
767 IS
768
769 CURSOR csr_get_annual_leave_details(p_payroll_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
770 IS
771 SELECT pap.accrual_plan_name
772 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
773 ,hr_general.decode_lookup('HOURS_OR_DAYS',pap.accrual_units_of_measure)
774 ,ppa.payroll_id
775 ,pap.business_group_id
776 ,pap.accrual_plan_id
777 ,paa.assignment_id
778 FROM pay_accrual_plans pap
779 ,pay_element_types_f pet
780 ,pay_element_links_f pel
781 ,pay_element_entries_f pee
782 ,pay_assignment_actions paa
783 ,pay_payroll_actions ppa
784 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
785 AND pel.element_type_id = pet.element_type_id
786 AND pee.element_link_id = pel.element_link_id
787 AND paa.assignment_id = pee.assignment_id
788 AND ppa.payroll_action_id = paa.payroll_action_id
789 AND pap.accrual_category = 'HKAL'
790 AND ppa.action_type IN ('R','Q')
791 AND ppa.action_status = 'C'
792 AND ppa.date_earned BETWEEN pet.effective_start_date
793 AND pet.effective_end_date
794 AND ppa.date_earned BETWEEN pel.effective_start_date
795 AND pel.effective_end_date
796 AND ppa.date_earned BETWEEN pee.effective_start_date
797 AND pee.effective_end_date
798 AND paa.assignment_action_id = p_payroll_assignment_action_id;
799
800 /*Bug 6074090 Added parameter c_assignment_action_id as date earned is not populated for pre payment action id,
801 Date earned of Payroll needs to be fetched */
802
803 CURSOR csr_get_date_earned(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
804 IS
805 SELECT ppa.date_earned
806 FROM pay_payroll_actions ppa
807 , pay_assignment_actions paa
808 WHERE ppa.payroll_action_id = paa.payroll_action_id
809 AND paa.assignment_action_id = c_assignment_action_id;
810
811 CURSOR csr_prepayment
812 IS
813 SELECT MAX(locked_action_id)
814 FROM pay_action_interlocks
815 WHERE locking_action_id = p_assignment_action_id;
816
817 l_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
818 l_accrual_category pay_accrual_plans.accrual_category%TYPE;
819 l_uom pay_accrual_plans.accrual_units_of_measure%TYPE;
820 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
821 l_business_group_id per_business_groups.business_group_id%TYPE;
822 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%TYPE;
823 l_assignment_id per_assignments_f.assignment_id%TYPE;
824 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
825 l_annual_leave_balance NUMBER;
826 l_ovn NUMBER;
827 l_leave_taken NUMBER;
828 l_start_date DATE;
829 l_end_date DATE;
830 l_accrual_end_date DATE;
831 l_date_earned DATE;
832 l_accrual NUMBER;
833 l_total_leave_taken NUMBER;
834
835 BEGIN
836
837
838 hr_utility.trace('Entering: ' || 'pay_hk_soe_pkg.get_annual_leave_information');
839
840 OPEN csr_prepayment;
844 IF l_assignment_action_id IS NULL THEN
841 FETCH csr_prepayment INTO l_assignment_action_id;
842 CLOSE csr_prepayment;
843
845 l_assignment_action_id := p_assignment_action_id;
846 END IF;
847
848 OPEN csr_get_annual_leave_details(l_assignment_action_id);
849 FETCH csr_get_annual_leave_details
850 INTO l_plan_name
851 , l_accrual_category
852 , l_uom
853 , l_payroll_id
854 , l_business_group_id
855 , l_accrual_plan_id
856 , l_assignment_id;
857
858 /* Bug 6928705 - Added check to call accrual calculations function
859 only if Accrual Plan ID is available */
860 IF csr_get_annual_leave_details%FOUND
861 THEN
862
863 CLOSE csr_get_annual_leave_details;
864
865 OPEN csr_get_date_earned(l_assignment_action_id); /*Bug 6074090 */
866 FETCH csr_get_date_earned INTO l_date_earned;
867 CLOSE csr_get_date_earned;
868
869
870 per_accrual_calc_functions.get_net_accrual
871 ( p_assignment_id => l_assignment_id
872 , p_plan_id => l_accrual_plan_id
873 , p_payroll_id => l_payroll_id
874 , p_business_group_id => l_business_group_id
875 , p_calculation_date => l_date_earned
876 , p_start_date => l_start_date
877 , p_end_date => l_end_date
878 , p_accrual_end_date => l_accrual_end_date
879 , p_accrual => l_accrual
880 , p_net_entitlement => l_annual_leave_balance
881 );
882
883 g_sql := 'SELECT ''' || l_plan_name || ''' COL01
884 , TO_CHAR(' || l_annual_leave_balance|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
885 , ''' || l_uom || ''' COL03
886 FROM DUAL';
887
888 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_annual_leave_information');
889
890 ELSE
891 CLOSE csr_get_annual_leave_details;
892 END IF;
893
894 RETURN g_sql;
895
896
897
898 END get_annual_leave_info;
899
900 --------------------------------------------------------------------------
901 -- --
902 -- Name : GET_LEAVE_TAKEN --
903 -- Type : FUNCTION --
904 -- Access : Public --
905 -- Description : Function to return SQL for Leave Taken Region --
906 -- --
907 -- Parameters : --
908 -- IN : p_assignment_action_id NUMBER --
909 -- Bug 5396046 --
910 --------------------------------------------------------------------------
911 FUNCTION get_leave_taken(p_assignment_action_id IN OUT NOCOPY pay_assignment_actions.assignment_action_id%TYPE)
912 RETURN LONG
913 IS
914
915 CURSOR csr_prepayment
916 IS
917 SELECT MAX(locked_action_id)
918 FROM pay_action_interlocks
919 WHERE locking_action_id = p_assignment_action_id;
920
921 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
922
923 BEGIN
924
925
926 hr_utility.trace('Entering: ' || 'pay_hk_soe_pkg.get_leave_taken');
927
928 OPEN csr_prepayment;
929 FETCH csr_prepayment INTO l_assignment_action_id;
930 CLOSE csr_prepayment;
931
932 IF l_assignment_action_id IS NOT NULL THEN
933 p_assignment_action_id := l_assignment_action_id;
934 END IF;
935
936 g_sql :=
937 ' SELECT pet.reporting_name COL01
938 ,TO_CHAR(decode(pet.processing_type,''R'',greatest(pab.date_start,PTP.START_DATE),pab.date_start),''DD-Mon-YYYY'') COL02
939 ,TO_CHAR(decode(pet.processing_type,''R'',least(pab.date_end,PTP.END_DATE),pab.date_end),''DD-Mon-YYYY'') COL03
940 ,hr_general.decode_lookup(''HOURS_OR_DAYS'',pat.HOURS_OR_DAYS) COL04
941 ,TO_CHAR(decode(pet.processing_type,''R'',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours))) COL16
942 FROM pay_assignment_actions paa
943 ,pay_payroll_actions ppa
944 ,pay_run_results prr
945 ,pay_run_result_values prrv
946 ,per_time_periods ptp
947 ,pay_element_types_f pet
948 ,pay_input_values_f piv
949 ,pay_element_entries_f pee
950 ,per_absence_attendance_types pat
951 ,per_absence_attendances pab
952 WHERE paa.assignment_action_id = ' || p_assignment_action_id || '
953 AND ppa.payroll_action_id = paa.payroll_action_id
954 AND ppa.action_type IN (''Q'',''R'')
955 AND ptp.time_period_id = ppa.time_period_id
956 AND paa.assignment_action_id = prr.assignment_action_id
957 AND pet.element_type_id = prr.element_type_id
958 AND pet.element_type_id = piv.element_type_id
959 AND piv.input_value_id = pat.input_value_id
960 AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
961 AND pab.absence_attendance_id = pee.creator_id
962 AND pee.creator_type = ''A''
963 AND pee.assignment_id = paa.assignment_id
964 AND pee.element_entry_id = prr.source_id
965 AND piv.input_value_id = prrv.input_value_id
966 AND prr.run_result_id = prrv.run_result_id
967 AND ppa.effective_date BETWEEN pet.effective_start_date
968 AND pet.effective_end_date
969 AND ppa.effective_date BETWEEN pee.effective_start_date
970 AND pee.effective_end_date
971 AND ppa.effective_date BETWEEN piv.effective_start_date
972 AND piv.effective_end_date';
973
974
975 hr_utility.trace('Leaving: ' || 'pay_hk_soe_pkg.get_leave_taken');
976
977 RETURN g_sql;
978
979 END get_leave_taken;
980
981 begin
982 g_def_bal_id_populated_payment := FALSE; /* Bug 4210525 */
983 g_def_bal_id_populated_ytd := FALSE; /* Bug 4210525 */
984 g_debug := hr_utility.debug_enabled;
985
986 end pay_hk_soe_pkg;