1 package body pay_au_soe_pkg as
2 /* $Header: pyausoe.pkb 120.19.12020000.2 2012/12/21 08:03:54 ruihuang ship $ */
3
4
5 /* changes start here */
6 g_debug boolean;
7 p_balance_value_tab_run pay_balance_pkg.t_balance_value_tab;
8 p_balance_value_tab_ytd pay_balance_pkg.t_balance_value_tab;
9 p_balance_value_tab_le_ytd pay_balance_pkg.t_balance_value_tab; /* Bug 4169557 */
10 p_context_table_run pay_balance_pkg.t_context_tab;
11 p_result_table_run pay_balance_pkg.t_detailed_bal_out_tab;
12
13
14
15 ------------------------------------------------------------------------
16 -- Selects the Home Address for the Person.
17 ------------------------------------------------------------------------
18 procedure get_home_address
19 (p_person_id in per_addresses.person_id%type,
20 p_address_line1 out NOCOPY per_addresses.address_line1%type,
21 p_address_line2 out NOCOPY per_addresses.address_line2%type,
22 p_address_line3 out NOCOPY per_addresses.address_line3%type,
23 p_town_city out NOCOPY per_addresses.town_or_city%type,
24 p_postal_code out NOCOPY per_addresses.postal_code%type,
25 p_country_name out NOCOPY fnd_territories_tl.territory_short_name%type) is
26
27 cursor home_address
28 (c_person_id per_addresses.person_id%type) is
29 select pad.address_line1,
30 pad.address_line2,
31 pad.address_line3,
32 pad.town_or_city,
33 pad.postal_code,
34 ftt.territory_short_name
35 from per_addresses pad,
36 fnd_territories_tl ftt
37 where pad.person_id = c_person_id
38 and ftt.language = userenv('LANG')
39 and ftt.territory_code = pad.country
40 and sysdate between nvl(pad.date_from, sysdate) and nvl(pad.date_to, sysdate);
41
42 begin
43 open home_address(p_person_id);
44 fetch home_address into p_address_line1,
45 p_address_line2,
46 p_address_line3,
47 p_town_city,
48 p_postal_code,
49 p_country_name;
50 close home_address;
51 end;
52
53 ------------------------------------------------------------------------
54 -- Selects the Work Address for the Person.
55 ------------------------------------------------------------------------
56 procedure get_work_address
57 (p_location_id in hr_locations.location_id%type,
58 p_address_line1 out NOCOPY hr_locations.address_line_1%type,
59 p_address_line2 out NOCOPY hr_locations.address_line_2%type,
60 p_address_line3 out NOCOPY hr_locations.address_line_3%type,
61 p_town_city out NOCOPY hr_locations.town_or_city%type,
62 p_postal_code out NOCOPY hr_locations.postal_code%type,
63 p_country_name out NOCOPY fnd_territories_tl.territory_short_name%type) is
64
65 cursor c_get_work_address
66 (c_location_id hr_locations.location_id%type) is
67 select hrl.address_line_1,
68 hrl.address_line_2,
69 hrl.address_line_3,
70 hrl.town_or_city,
71 hrl.postal_code,
72 ftt.territory_short_name
73 from hr_locations hrl,
74 fnd_territories_tl ftt
75 where hrl.location_id = c_location_id
76 and ftt.language = userenv('LANG')
77 and ftt.territory_code = hrl.country;
78
79 begin
80 open c_get_work_address(p_location_id);
81 fetch c_get_work_address into p_address_line1,
82 p_address_line2,
83 p_address_line3,
84 p_town_city,
85 p_postal_code,
86 p_country_name;
87 close c_get_work_address;
88 end;
89
90 ------------------------------------------------------------------------
91 -- Selects the Salary for the Person.
92 --
93 -- clone of hr_general.get_salary but fetch At a given date
94 -- This cursor gets the screen_entry_value from pay_element_entry_values_f.
95 -- This is the salary amount obtained when the pay basis isn't null.
96 -- The pay basis and assignment_id are passed in by the view.
97 -- A check is made on the effective date of pay_element_entry_values_f
98 -- and pay_element_entries_f as they're datetracked.
99 ------------------------------------------------------------------------
100 function get_salary
101 (p_pay_basis_id in per_pay_bases.pay_basis_id%type,
102 p_assignment_id in pay_element_entries_f.assignment_id%type,
103 p_effective_date in date)
104 return varchar2 is
105
106 cursor salary
107 (c_pay_basis_id per_pay_bases.pay_basis_id%type,
108 c_assignment_id pay_element_entries_f.assignment_id%type,
109 c_effective_date date) is
110 select pev.screen_entry_value
111 from per_pay_bases ppb,
112 pay_element_entries_f pee,
113 pay_element_entry_values_f pev
114 where pee.assignment_id = c_assignment_id
115 and ppb.pay_basis_id = c_pay_basis_id
116 and pee.element_entry_id = pev.element_entry_id
117 and ppb.input_value_id = pev.input_value_id
118 and c_effective_date between pev.effective_start_date
119 and pev.effective_end_date
120 and c_effective_date between pee.effective_start_date
121 and pee.effective_end_date;
122
123 v_salary pay_element_entry_values_f.screen_entry_value%type := null;
124 begin
125
126 -- Only open the cursor if the parameter may retrieve anything
127 -- In practice, p_assignment_id is always going to be non null;
128 -- p_pay_basis_id may be null, though. If it is, don't bother trying
129 -- to fetch a salary.
130 -- If we do have a pay basis, try and get a salary. There may not be one,
131 -- in which case no problem: just return null.
132
133 if p_pay_basis_id is not null and p_assignment_id is not null then
134 open salary (p_pay_basis_id,
135 p_assignment_id,
136 p_effective_date) ;
137 fetch salary into v_salary;
138 close salary;
139 end if;
140
141 return v_salary;
142 end get_salary;
143 ------------------------------------------------------------------------
144 -- Returns the Currency Code for the Business Group.
145 ------------------------------------------------------------------------
146 function business_currency_code
147 (p_business_group_id in hr_organization_units.business_group_id%type)
148 return fnd_currencies.currency_code%type is
149
150 v_currency_code fnd_currencies.currency_code%type;
151
152 cursor currency_code
153 (c_business_group_id hr_organization_units.business_group_id%type) is
154 select fcu.currency_code
155 from hr_organization_information hoi,
156 hr_organization_units hou,
157 fnd_currencies fcu
158 where hou.business_group_id = c_business_group_id
159 and hou.organization_id = hoi.organization_id
160 and hoi.org_information_context = 'Business Group Information'
161 and fcu.issuing_territory_code = hoi.org_information9;
162
163 begin
164 open currency_code (p_business_group_id);
168 return v_currency_code;
165 fetch currency_code into v_currency_code;
166 close currency_code;
167
169 end business_currency_code;
170 ------------------------------------------------------------------------
171 -- Sums the Balances for This Pay and YTD, according to the parameters.
172 ------------------------------------------------------------------------
173 /* --------------------------------------------------------------------------
174 Bug 4169557
175 Procedure : populate_defined_balances
176 This procedure populates 2 PL/SQL table with Defined Balance ID's of
177 10 Balances for LE Dimensions _ASG_LE_RUN and _ASG_LE_YTD
178 --------------------------------------------------------------------------
179 */
180
181 procedure populate_defined_balances
182 is
183 CURSOR c_get_defined_balance_id
184 (c_dimension_name pay_balance_dimensions.dimension_name%type)
185 IS
186 SELECT decode(pbt.balance_name, 'Earnings_Total',1
187 , 'Direct Payments',2
188 , 'Termination_Payments',3
189 , 'Involuntary Deductions',4
190 , 'Pre Tax Deductions',5
191 , 'Termination Deductions',6
192 , 'Voluntary Deductions',7
193 , 'Employer Superannuation Contribution',8
194 , 'Earnings_Non_Taxable',9
195 , 'Total_Tax_Deductions',10) sort_index,
196 pdb.defined_balance_id defined_balance_id
197 FROM pay_balance_types pbt,
198 pay_balance_dimensions pbd,
199 pay_defined_balances pdb
200 WHERE pbt.balance_name IN ('Earnings_Total'
201 , 'Direct Payments'
202 , 'Termination_Payments'
203 , 'Involuntary Deductions'
204 , 'Pre Tax Deductions'
205 , 'Termination Deductions'
206 , 'Voluntary Deductions'
207 , 'Employer Superannuation Contribution'
208 , 'Earnings_Non_Taxable'
209 , 'Total_Tax_Deductions')
210
211 AND pbd.dimension_name = c_dimension_name
212 AND pbt.balance_type_id = pdb.balance_type_id
213 AND pbd.balance_dimension_id = pdb.balance_dimension_id
214 AND pbt.legislation_code = 'AU'
215 ORDER BY sort_index;
216 begin
217
218 /* populate a table for defined balance ids of LE run balances
219 p_balance_value_tab_run */
220
221 FOR csr_rec IN c_get_defined_balance_id('_ASG_LE_RUN')
222 LOOP
223 p_balance_value_tab_run(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
224
225 END LOOP;
226
227 /* populate a table for defined balance ids of LE YTD balances
228 p_balance_value_tab_le_ytd */
229
230 FOR csr_rec IN c_get_defined_balance_id('_ASG_LE_YTD')
231 LOOP
232 p_balance_value_tab_le_ytd(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
233
234 END LOOP;
235
236 end populate_defined_balances;
237
238 ------------------------------------------------------------------------
239 -- Procedure to merely pass all the balance results back in one hit,
240 -- rather than 6 separate calls.
241 ------------------------------------------------------------------------
242 procedure balance_totals
243 (p_assignment_id in per_all_assignments_f.assignment_id%type,
244 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
245 p_effective_date in date,
246 p_gross_this_pay out NOCOPY number,
247 p_other_deductions_this_pay out NOCOPY number,
248 p_tax_deductions_this_pay out NOCOPY number,
249 p_gross_ytd out NOCOPY number,
250 p_other_deductions_ytd out NOCOPY number,
251 p_tax_deductions_ytd out NOCOPY number,
252 p_non_tax_allowances_run out NOCOPY number,
253 p_non_tax_allowances_ytd out NOCOPY number,
254 p_pre_tax_deductions_run out NOCOPY number,
255 p_pre_tax_deductions_ytd out NOCOPY number,
256 p_super_run out NOCOPY number,
257 p_super_ytd out NOCOPY number,
258 p_taxable_income_this_pay out NOCOPY number,
259 p_taxable_income_ytd out NOCOPY number,
260 p_direct_payments_run out NOCOPY number,
261 p_direct_payments_ytd out NOCOPY number,
262 p_get_le_level_bal in varchar2, --3935483
263 p_fetch_only_ytd_value in varchar2) --3935483
264
265 /* bug 3935483 2 new parameters introduced p_get_le_level_bal when Y the le level balances,run and ytd, would be fetched
266 p_fetch_only_ytd_value when Y ytd balances would be fetched and run balances would not be fetched*/
267
268 is
269
270
271 /* Bug 2610141 */
272 CURSOR tax_unit_id IS
273 SELECT tax_unit_id
274 from pay_assignment_actions paa
275 where paa.assignment_action_id = p_assignment_action_id;
276
277 /*Bug 3935483 Changes for BBR start here */
278
279 /* cursor to get the defined balance ids for the various balances */
280
281 CURSOR c_get_defined_balance_id
282 (c_dimension_name pay_balance_dimensions.dimension_name%type)
283 IS
284 SELECT decode(pbt.balance_name, 'Earnings_Total',1
285 , 'Direct Payments',2
286 , 'Termination_Payments',3
287 , 'Involuntary Deductions',4
288 , 'Pre Tax Deductions',5
289 , 'Termination Deductions',6
290 , 'Voluntary Deductions',7
291 , 'Employer Superannuation Contribution',8
292 , 'Earnings_Non_Taxable',9
293 , 'Total_Tax_Deductions',10) sort_index,
294 pdb.defined_balance_id defined_balance_id
295 FROM pay_balance_types pbt,
296 pay_balance_dimensions pbd,
297 pay_defined_balances pdb
298 WHERE pbt.balance_name IN ('Earnings_Total'
299 , 'Direct Payments'
300 , 'Termination_Payments'
301 , 'Involuntary Deductions'
302 , 'Pre Tax Deductions'
303 , 'Termination Deductions'
304 , 'Voluntary Deductions'
305 , 'Employer Superannuation Contribution'
306 , 'Earnings_Non_Taxable'
307 , 'Total_Tax_Deductions')
308
309 AND pbd.dimension_name = c_dimension_name
310 AND pbt.balance_type_id = pdb.balance_type_id
311 AND pbd.balance_dimension_id = pdb.balance_dimension_id
312 AND pbt.legislation_code = 'AU'
313 ORDER BY sort_index;
314
315
316 v_tax_unit_id number;
317 v_earnings_run number;
318 v_earnings_ytd number;
319 v_direct_payments_run number;
320 v_direct_payments_ytd number;
321 v_involuntary_deductions_run number;
322 v_involuntary_deductions_ytd number;
323 v_pre_tax_deductions_run number;
324 v_pre_tax_deductions_ytd number;
325 v_voluntary_deductions_run number;
326 v_voluntary_deductions_ytd number;
327 v_tax_deductions_run number;
328 v_tax_deductions_ytd number;
329 v_termination_payments_run number;
330 v_termination_payments_ytd number;
331 v_termination_deductions_run number;
332 v_termination_deductions_ytd number;
333 v_super_run number;
334 v_super_ytd number;
335 v_non_tax_allow_run number;
336 v_non_tax_allow_ytd number;
337
338
339 l_bal_dimen_ytd pay_balance_dimensions.dimension_name%type ; --3935483
340 l_bal_dimen_run pay_balance_dimensions.dimension_name%type ; --3935483
341
342
343
347 /*Bug 2610141 */
344 begin
345
346
348
349
350 /*Fetch the value of v_tax_unit_id if the flag is Y else assign it null Bug 3935483*/
351
352 if p_get_le_level_bal='Y' then
353 open tax_unit_id;
354 fetch tax_unit_id into v_tax_unit_id;
355 close tax_unit_id;
356 else
357 v_tax_unit_id:=NULL;
358 end if;
359
360 /* The ytd balance dimension would be set according to the flag to get the value of balances*/
361
362 if (p_get_le_level_bal ='Y')then
363 l_bal_dimen_ytd :='_ASG_LE_YTD';
364 else
365 l_bal_dimen_ytd :='_ASG_YTD';
366
367 end if;
368
369 p_context_table_run(1).tax_unit_id:=v_tax_unit_id;
370
371 /*---------------for run values-------------------- */
372
373
374 /* if the flag p_fetch_only_ytd_value is Y then only the YTD balances would be fetched and the run balances would be set to null
375 else the LE level run balances would be fetched This is done to ensure that
376 the run balances are not fetched twice for le level and ytd balances in the SOE form */
377
378 if p_fetch_only_ytd_value = 'Y' then
379
380 p_gross_this_pay := null;
381 p_non_tax_allowances_run := null;
382 p_direct_payments_run := null;
383 p_taxable_income_this_pay := null;
384 p_other_deductions_this_pay := null;
385 p_tax_deductions_this_pay := null;
386 p_pre_tax_deductions_run := null;
387 p_super_run := null;
388
389 else
390
391 /* populate a table for defined balance ids of LE run balances */
392 /* Bug 4169557 - Removed calls to populate _ASG_LE_RUN Defined balances
393 */
394
395 /* get the balances using BBR */
396
397 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
398 p_defined_balance_lst=>p_balance_value_tab_run,
399 p_context_lst =>p_context_table_run,
400 p_output_table=>p_result_table_run);
401
402 /* assign the values of the balances to the variables and calculate other run balances */
403
404 v_earnings_run := p_result_table_run(1).balance_value;
405 v_direct_payments_run :=p_result_table_run(2).balance_value;
406 v_termination_payments_run := p_result_table_run(3).balance_value;
407 v_involuntary_deductions_run := p_result_table_run(4).balance_value;
408 v_pre_tax_deductions_run := p_result_table_run(5).balance_value;
409 v_termination_deductions_run := p_result_table_run(6).balance_value;
410 v_voluntary_deductions_run := p_result_table_run(7).balance_value;
411 v_super_run := p_result_table_run(8).balance_value;
412 v_non_tax_allow_run := p_result_table_run(9).balance_value;
413 v_tax_deductions_run := p_result_table_run(10).balance_value;
414
415
416 p_gross_this_pay := v_earnings_run +
417 v_termination_payments_run +
418 v_pre_tax_deductions_run ;
419
420 p_non_tax_allowances_run := v_non_tax_allow_run;
421
422 p_direct_payments_run := v_direct_payments_run;
423
424 p_taxable_income_this_pay := p_gross_this_pay -
425 v_non_tax_allow_run -
426 v_pre_tax_deductions_run;
427
428 p_other_deductions_this_pay := v_involuntary_deductions_run +
429 v_voluntary_deductions_run;
430
431 p_tax_deductions_this_pay := v_tax_deductions_run +
432 v_termination_deductions_run;
433
434 p_pre_tax_deductions_run := v_pre_tax_deductions_run;
435
436 p_super_run := v_super_run;
437
438
439
440 end if;
441
442 /*------------------------------------- for YTD values -----------------------*/
443
444 /* Bug 4169557 - Removed calls to populate defined balance ID's for _ASG_LE_YTD
445 IF Dimension Level = LE (ASG_LE_YTD)
446 fetch balance values
447 else (_ASG_YTD)
448 populate defined balance ID's for YTD
449 fetch balance values
450 END IF
451 */
452 IF ( p_get_le_level_bal ='Y')
453 THEN
454 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
455 p_defined_balance_lst=> p_balance_value_tab_le_ytd,
456 p_context_lst =>p_context_table_run,
457 p_output_table=>p_result_table_run);
458
459 ELSE
460 p_balance_value_tab_ytd.delete;
461
462 FOR csr_rec IN c_get_defined_balance_id(l_bal_dimen_ytd)
463 LOOP
464 p_balance_value_tab_ytd(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
465 END LOOP;
466
467 /* fetch the ytd balances */
468 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
469 p_defined_balance_lst=>p_balance_value_tab_ytd,
470 p_context_lst =>p_context_table_run,
471 p_output_table=>p_result_table_run);
472
473 END IF;
474
475 v_earnings_ytd :=p_result_table_run(1).balance_value;
476 v_direct_payments_ytd := p_result_table_run(2).balance_value;
477 v_termination_payments_ytd := p_result_table_run(3).balance_value;
478 v_involuntary_deductions_ytd := p_result_table_run(4).balance_value;
479 v_pre_tax_deductions_ytd := p_result_table_run(5).balance_value;
480 v_termination_deductions_ytd := p_result_table_run(6).balance_value;
481 v_voluntary_deductions_ytd := p_result_table_run(7).balance_value;
482 v_super_ytd := p_result_table_run(8).balance_value;
483 v_non_tax_allow_ytd := p_result_table_run(9).balance_value;
484 v_tax_deductions_ytd := p_result_table_run(10).balance_value;
485
486
487
488 /* Bug 3953706 - Modfied the calculatioon of earnings and deductions
489 p_gross_this_pay = Earnings Total + Termination Payments + Pre Tax Deductions
490 p_non_tax_allowances_run = Non Taxable Earnings
491 p_direct_payments_run = Direct Payments
492 p_pre_tax_deductions_run = Pre Tax Deductions
493 p_taxable_income_this_pay = p_gross_this_pay - p_pre_tax_deductions_run - p_non_tax_allowances_run
494 p_super_run = Employer charges
495 p_other_deductions_this_pay = Involuntary Deductions + Voluntary Deductions
496 p_tax_deductions_this_pay = Tax deductions + Termination deductions*/
497
498
499
500 -- Gross Earnings
501
502
503 p_gross_ytd := v_earnings_ytd +
504 v_termination_payments_ytd +
505 v_pre_tax_deductions_ytd ;
506 -- Earnings Non Taxable
507
508 p_non_tax_allowances_ytd := v_non_tax_allow_ytd;
509
510 -- Direct Payments
511
512 p_direct_payments_ytd := v_direct_payments_ytd;
513
514 -- Taxable Gross
515
516 p_taxable_income_ytd := p_gross_ytd -
517 p_non_tax_allowances_ytd -
518 v_pre_tax_deductions_ytd;
519
520 -- Post Tax Deduction
521
522
523 p_other_deductions_ytd := v_involuntary_deductions_ytd +
524 v_voluntary_deductions_ytd;
525
526 -- Tax Deductions
527
528 p_tax_deductions_ytd := v_tax_deductions_ytd +
529 v_termination_deductions_ytd;
530
531 -- Pre Tax Deductions
532
533 p_pre_tax_deductions_ytd := v_pre_tax_deductions_ytd;
534
535 p_super_ytd := v_super_ytd;
536
537
538 end balance_totals;
539 ------------------------------------------------------------------------
540
541 procedure get_asg_latest_pay(p_session_date in date,
542 p_payroll_exists in out NOCOPY varchar2,
543 p_assignment_action_id in out NOCOPY number,
544 p_run_assignment_action_id in out NOCOPY number,
545 p_assignment_id in number,
546 p_payroll_id out NOCOPY number,
547 p_payroll_action_id in out NOCOPY number,
548 p_date_earned in out NOCOPY varchar2,
549 p_time_period_id out NOCOPY number,
550 p_period_name out NOCOPY varchar2,
551 p_pay_advice_date out NOCOPY date,
552 p_pay_advice_message out NOCOPY varchar2)
553 is
554
555 -- get the latest prepayments action for this individual and get the
556 -- details of the last run that that action locked
557 cursor asg_latest_pay is
558 select
559 rppa.date_earned,
560 rpaa.payroll_action_id,
561 rpaa.assignment_action_id,
562 paa.assignment_action_id,
563 ptp.time_period_id,
564 ptp.period_name,
565 rppa.payroll_id,
566 nvl(rppa.pay_advice_date,ptp.pay_advice_date),
567 rppa.pay_advice_message
568 from pay_assignment_actions paa,
569 pay_payroll_actions ppa,
570 pay_assignment_actions rpaa,
571 per_time_periods ptp,
572 pay_payroll_actions rppa
573 where paa.payroll_action_id = ppa.payroll_action_id
574 and rppa.payroll_action_id = rpaa.payroll_action_id
575 and rppa.time_period_id = ptp.time_period_id
576 and paa.assignment_action_id =
577 (select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
578 from pay_payroll_actions pa,
579 pay_assignment_actions aa
580 where pa.action_type in ('U','P') /* Bug No : 2674887 */
581 and aa.action_status = 'C'
582 and pa.payroll_action_id = aa.payroll_action_id
583 and aa.assignment_id = p_assignment_id
584 and pa.effective_date <= p_session_date)
585 and ppa.action_type in ('P', 'U') /* Bug No : 2674887 */
586 and rpaa.assignment_id = p_assignment_id
587 and rpaa.action_sequence =
588 (select max(aa.action_sequence)
589 from pay_assignment_actions aa,
590 pay_action_interlocks loc
591 where loc.locked_action_id = aa.assignment_action_id
592 and loc.locking_action_id = paa.assignment_action_id);
593 begin
594 open asg_latest_pay;
595 fetch asg_latest_pay into p_date_earned,
596 p_payroll_action_id,
597 p_run_assignment_action_id,
598 p_assignment_action_id,
599 p_time_period_id,
600 p_period_name,
601 p_payroll_id,
602 p_pay_advice_date,
603 p_pay_advice_message;
604 if asg_latest_pay%FOUND then
605 p_payroll_exists := 'TRUE';
606 end if;
607 close asg_latest_pay;
608 end get_asg_latest_pay;
609
610 ------------------------------------------------------------------
611 procedure get_details (p_assignment_action_id in out NOCOPY number,
612 p_run_assignment_action_id in out NOCOPY number,
613 p_assignment_id in out NOCOPY number,
614 p_payroll_id out NOCOPY number,
615 p_payroll_action_id in out NOCOPY number,
616 p_date_earned in out NOCOPY date,
617 p_time_period_id out NOCOPY number,
618 p_period_name out NOCOPY varchar2,
619 p_pay_advice_date out NOCOPY date,
620 p_pay_advice_message out NOCOPY varchar2) is
621
622 -- if the assignment action is a run then return the run details
623 -- if the assignment action is a prepayment return the latest run
624 --locked by the prepayment
625
626 cursor get_action_type is
627 -- find type of action this is
628 select pact.action_type , assact.assignment_id
629 from pay_assignment_actions assact,
630 pay_payroll_actions pact
631 where assact.assignment_action_id = p_assignment_action_id
632 and pact.payroll_action_id =
633 assact.payroll_action_id
634 ;
635 cursor get_run is
636 -- for prepayment action find the latest interlocked run
637 select assact.assignment_action_id
638 from pay_assignment_actions assact,
639 pay_action_interlocks loc
640 where loc.locking_action_id = p_assignment_action_id
641 and assact.assignment_action_id = loc.locked_action_id
642 order by assact.action_sequence desc ;
643
644 cursor get_prepay is
645 -- for run action check if its been prepaid
646 select assact.assignment_action_id
647 from pay_assignment_actions assact,
648 pay_payroll_actions pact,
649 pay_action_interlocks loc
650 where loc.locked_action_id = p_assignment_action_id
651 and assact.assignment_action_id = loc.locking_action_id
652 and pact.payroll_action_id = assact.payroll_action_id
653 and pact.action_type in ('P','U') -- Removed check for costing (2846119)
654 /* prepayments
655 only */
656 order by assact.action_sequence desc
657 ;
658 cursor get_run_details is
659 -- now find the date earned and payroll action of the run action
660 select pact.payroll_id,
661 pact.payroll_action_id,
662 pact.date_earned,
663 ptp.time_period_id,
664 ptp.period_name,
665 nvl(pact.pay_advice_date,ptp.pay_advice_date),
666 pay_advice_message
667 from pay_assignment_actions assact,
668 pay_payroll_actions pact,
669 per_time_periods ptp
670 where assact.assignment_action_id = p_run_assignment_action_id
671 and pact.payroll_action_id = assact.payroll_action_id
672 and pact.payroll_id = ptp.payroll_id
673 and pact.date_earned between ptp.start_date and ptp.end_date ;
674 --
675 -- Bug-2595888: Changed the variable type from varchar2(1)
676 l_action_type pay_payroll_actions.action_type%type;
677 --
678 begin
679 --
680 open get_action_type;
681 fetch get_action_type into l_action_type, p_assignment_id;
682 close get_action_type;
683 --
684 if l_action_type in ('P', 'U') then -- Removed check for costing(2846119)
685 open get_run;
686 fetch get_run into p_run_assignment_action_id;
687 close get_run;
688 -- if its a run action it may or may not have been prepaid
689 else
690 p_run_assignment_action_id := p_assignment_action_id;
691 begin
692 open get_prepay;
693 fetch get_prepay into p_assignment_action_id;
694 if get_prepay%NOTFOUND then
695 p_assignment_action_id := p_run_assignment_action_id;
696 end if;
697 close get_prepay;
698 end;
699 end if;
700 -- fetch payroll details
701 open get_run_details;
702 fetch get_run_details into p_payroll_id,
703 p_payroll_action_id,
704 p_date_earned,
705 p_time_period_id,
706 p_period_name,
707 p_pay_advice_date,
708 p_pay_advice_message;
709 close get_run_details;
710 end get_details;
711
712 /* bug 3935483 2 new parameters added in balance_totals and final_balance_totals , p_get_le_level_bal to fetch le level balances when it is Y and
713 p_fetch_only_ytd_value to fetch only ytd balances and not run balances */
714
715 procedure final_balance_totals
716 (p_assignment_id in per_all_assignments_f.assignment_id%type,
717 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
718 p_effective_date in date,
719 p_gross_this_pay out NOCOPY number,
720 p_other_deductions_this_pay out NOCOPY number,
721 p_tax_deductions_this_pay out NOCOPY number,
722 p_gross_ytd out NOCOPY number,
723 p_other_deductions_ytd out NOCOPY number,
724 p_tax_deductions_ytd out NOCOPY number,
725 p_non_tax_allow_this_pay out NOCOPY number,
726 p_non_tax_allow_ytd out NOCOPY number,
727 p_pre_tax_deductions_this_pay out NOCOPY number,
728 p_pre_tax_deductions_ytd out NOCOPY number,
729 p_super_this_pay out NOCOPY number,
730 p_super_ytd out NOCOPY number,
731 p_taxable_income_this_pay out NOCOPY number,
732 p_taxable_income_ytd out NOCOPY number,
733 p_direct_payments_this_pay out NOCOPY number,
734 p_direct_payments_ytd out NOCOPY number,
735 p_get_le_level_bal in varchar2,
736 p_fetch_only_ytd_value in varchar2)
737 is
738
739 CURSOR run_ids IS
740 SELECT pai.locked_action_id
741 FROM pay_assignment_actions paa,
742 pay_action_interlocks pai
743 WHERE pai.LOCKING_ACTION_ID = p_assignment_action_id
744 AND pai.locked_action_id = paa.assignment_action_id
745 AND paa.assignment_action_id not in (select bpaa.source_action_id
746 from pay_assignment_actions bpaa
747 where bpaa.source_action_id =pai.locked_action_id)
748 ORDER BY locked_action_id ASC;
749
750 /*SELECT locked_action_id Bug 3245909 To fetch Master locked action_id only */
751 /* FROM pay_assignment_actions paa,
752 pay_action_interlocks pai
753 WHERE LOCKING_ACTION_ID = p_assignment_action_id
754 AND pai.locked_action_id = paa.assignment_action_id
755 AND paa.source_action_id IS NULL
756 ORDER BY locked_action_id ASC;*/
757
758 pre_pay number := 1;
759 cur_run_id number;
760 l_ASSIGNMENT_ID number ;
761 l_RUN_ASSIGNMENT_ACTION_ID number ;
762 l_DATE_EARNED date ;
763 l_GROSS_INCOME_TP number;
764 l_DED_TP number;
765 l_TAX_DED_TP number;
766 l_GROSS_INCOME_YTD number;
767 l_DED_YTD number;
768 l_TAX_DED_YTD number;
769 l_NON_TAX_TP number;
770 l_NON_TAX_YTD number;
771 l_PRE_TAX_DED_TP number;
772 l_PRE_TAX_DED_YTD number;
773 l_super_TP number;
774 l_super_YTD number;
775 l_TAXABLE_INCOME_TP number;
776 l_TAXABLE_INCOME_YTD number;
777 l_direct_payments_tp number;
778 l_direct_payments_ytd number;
779
780 begin
781 open run_ids;
782 loop
783 fetch run_ids into cur_run_id;
784 exit when run_ids%NOTFOUND;
785 l_RUN_ASSIGNMENT_ACTION_ID := cur_run_id;
786 pre_pay:= 0;
787 pay_au_soe_pkg.balance_totals(
788 p_assignment_id ,
789 l_RUN_ASSIGNMENT_ACTION_ID ,
790 p_effective_date ,
791 l_GROSS_INCOME_TP,
792 l_DED_TP,
793 l_TAX_DED_TP,
794 l_GROSS_INCOME_YTD,
795 l_DED_YTD,
796 l_TAX_DED_YTD,
797 l_NON_TAX_TP,
798 l_NON_TAX_YTD,
799 l_PRE_TAX_DED_TP,
800 l_PRE_TAX_DED_YTD,
801 l_super_TP,
802 l_super_YTD,
803 l_TAXABLE_INCOME_TP,
804 l_TAXABLE_INCOME_YTD,
805 l_direct_payments_tp,
806 l_direct_payments_ytd,
807 p_get_le_level_bal,
808 p_fetch_only_ytd_value); /*3935483*/
809
810 p_gross_this_pay := NVL(p_gross_this_pay,0) + l_GROSS_INCOME_TP;
811 p_other_deductions_this_pay := NVL(p_other_deductions_this_pay,0) + l_DED_TP;
812 p_tax_deductions_this_pay := NVL(p_tax_deductions_this_pay,0) + l_TAX_DED_TP;
813 p_non_tax_allow_this_pay := NVL(p_non_tax_allow_this_pay,0) + l_NON_TAX_TP;
814 p_pre_tax_deductions_this_pay := NVL(p_pre_tax_deductions_this_pay,0) +
815 l_PRE_TAX_DED_TP;
816 p_super_this_pay := NVL(p_super_this_pay,0) + l_super_TP;
817 p_taxable_income_this_pay := NVL(p_taxable_income_this_pay,0) + l_TAXABLE_INCOME_TP; /* Bug 3953706 */
818 p_direct_payments_this_pay := NVL(p_direct_payments_this_pay,0) + l_direct_payments_tp; /* Bug 3953706 */
819 end loop;
820 p_gross_ytd := l_GROSS_INCOME_YTD;
821 p_other_deductions_ytd := l_DED_YTD;
822 p_tax_deductions_ytd := l_TAX_DED_YTD;
823 p_non_tax_allow_ytd := l_NON_TAX_YTD;
824 p_pre_tax_deductions_ytd := l_PRE_TAX_DED_YTD;
825 p_super_ytd := l_super_ytd;
826 p_taxable_income_ytd := l_TAXABLE_INCOME_YTD; /* Bug 3953706 */
827 p_direct_payments_ytd := l_direct_payments_ytd;/* Bug 3953706 */
828
829 close run_ids;
830 if Pre_pay <> 0 then
831 pay_au_soe_pkg.balance_totals(
832 p_assignment_id ,
833 p_assignment_action_id ,
834 p_effective_date ,
835 p_gross_this_pay ,
836 p_other_deductions_this_pay ,
837 p_tax_deductions_this_pay ,
838 p_gross_ytd ,
839 p_other_deductions_ytd ,
840 p_tax_deductions_ytd ,
841 p_non_tax_allow_this_pay ,
842 p_non_tax_allow_ytd ,
843 p_pre_tax_deductions_this_pay ,
844 p_pre_tax_deductions_ytd ,
845 p_super_this_pay,
846 p_super_ytd,
847 p_taxable_income_this_pay,
848 p_taxable_income_ytd,
849 p_direct_payments_this_pay,
850 p_direct_payments_ytd,
851 p_get_le_level_bal, --3935483
852 p_fetch_only_ytd_value); --3935483
853 end if;
854 end final_balance_totals;
855
856 /* Bug 5461557 - Added function super_fund_name to get superannnuation fund name
857 This function will return Superannuation Fund Name.
858 If Superannuation Fund Name is null,then element reporting name will be returned*/
859
860 function super_fund_name
861 (p_source_id in number,
862 p_element_reporting_name in pay_element_types_f.reporting_name%type,
863 p_date_earned in pay_payroll_actions.date_earned%type,
864 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
865 p_assignment_id in per_all_assignments_f.assignment_id%type,
866 p_element_entry_id in pay_element_entries_f.PERSONAL_PAYMENT_METHOD_ID%TYPE,
867 p_business_group_id per_all_assignments_f.business_group_id%TYPE)
868 return varchar2
869
870 is
871
872 cursor c_get_super_fund_name (p_assignment_action_id number,p_date_earned date,p_source_id number)
873 is
874 select distinct prrv.result_value ,prr.element_entry_id
875 from
876 pay_run_results prr,
877 pay_run_result_values prrv,
878 pay_input_values_f piv,
879 pay_element_types_f pet,
880 pay_element_classifications pec
881 where prr.source_id= p_source_id
882 and piv.input_value_id=prrv.input_value_id
883 and piv.name like '%Fund%Name%'
884 and pet.element_type_id=piv.element_type_id
885 and pet.classification_id=pec.classification_id
886 and pec.classification_name='Information'
887 and prr.element_type_id=pet.element_type_id
888 and prrv.result_value is not null
889 and prr.run_result_id=prrv.run_result_id
890 and prr.assignment_action_id=p_assignment_action_id
891 and p_date_earned between pet.effective_start_date and pet.effective_end_date
892 and p_date_earned between piv.effective_start_date and piv.effective_end_date
893 AND (pec.legislation_code is null or pec.legislation_code = 'AU');
894
895
896 /*for bug 5983711 for advance entries */
897 cursor c_get_ae_super_fund_name (p_assignment_action_id number,p_date_earned date,p_source_id number)
898 is
899 select distinct prrv.result_value ,prr.element_entry_id
900 from
901 pay_run_results prr,
902 pay_run_result_values prrv,
903 pay_input_values_f piv,
904 pay_element_types_f pet,
905 pay_element_classifications pec,
906 pay_element_entries_f pee,
907 pay_element_entries_f pee1
908 where pee.element_entry_id=p_source_id
909 and pee.source_id=pee1.element_entry_id
910 and prr.element_entry_id=pee1.element_entry_id
911 and piv.input_value_id=prrv.input_value_id
912 and piv.name like '%Fund%Name%'
913 and pet.element_type_id=piv.element_type_id
914 and pet.classification_id=pec.classification_id
915 and pec.classification_name='Information'
916 and prr.element_type_id=pet.element_type_id
917 and prrv.result_value is not null
921 and p_date_earned between piv.effective_start_date and piv.effective_end_date
918 and prr.run_result_id=prrv.run_result_id
919 and prr.assignment_action_id=p_assignment_action_id
920 and p_date_earned between pet.effective_start_date and pet.effective_end_date
922 and p_date_earned between pee.effective_start_date and pee.effective_end_date
923 and p_date_earned between pee1.effective_start_date and pee1.effective_end_date
924 AND (pec.legislation_code is null or pec.legislation_code = 'AU');
925
926
927 /* for bug 5983711 for retro entries */
928 cursor c_get_rr_super_fund_name(p_assignment_action_id number,p_date_earned date,p_source_id number)
929 is
930 select distinct prrv.result_value ,prr.element_entry_id
931 from pay_element_entries_f pee,
932 pay_element_entries_f pee1,
933 pay_run_results prr,
934 pay_run_results prr1,
935 pay_run_result_values prrv,
936 pay_input_values_f piv,
937 pay_element_types_f pet,
938 pay_element_classifications pec
939 where pee.element_entry_id=p_source_id
940 and pee.source_id =prr.run_result_id
941 and prr.source_id= pee1.element_entry_id
942 and prr1.element_entry_id=pee1.element_entry_id
943 and piv.input_value_id=prrv.input_value_id
944 and piv.name like '%Fund%Name%'
945 and pet.element_type_id=piv.element_type_id
946 and pet.classification_id=pec.classification_id
947 and pec.classification_name='Information'
948 and prr1.element_type_id=pet.element_type_id
949 and prrv.result_value is not null
950 and prr1.run_result_id=prrv.run_result_id
951 and prr1.assignment_action_id=p_assignment_action_id
952 and p_date_earned between pet.effective_start_date and pet.effective_end_date
953 and p_date_earned between piv.effective_start_date and piv.effective_end_date
954 and p_date_earned between pee.effective_start_date and pee.effective_end_date
955 and p_date_earned between pee1.effective_start_date and pee1.effective_end_date
956 AND (pec.legislation_code is null or pec.legislation_code = 'AU');
957
958 cursor c_get_third_party_payment_id(p_element_entry_id number,p_assignment_id number,p_date_earned date)
959 is
960 select PERSONAL_PAYMENT_METHOD_ID
961 from pay_element_entries_f pee
962 where element_entry_id=p_element_entry_id
963 and assignment_id=p_assignment_id
964 and p_date_earned between pee.effective_start_date and pee.effective_end_date;
965
966
967
968 cursor c_get_third_party_fund_name(p_third_party_id number,p_date_earned date,p_assignment_id number
969 ,p_business_group_id number)
970 is
971 select hoi.org_information2
972 from
973 hr_organization_information hoi,
974 hr_organization_units hou,
975 pay_personal_payment_methods_f pppm
976 where
977 hoi.org_information_context='AU_SUPER_FUND'
978 and hoi.organization_id=hou.organization_id
979 and pppm.payee_id=hoi.organization_id
980 and p_date_earned between pppm.effective_start_date and last_day(pppm.effective_end_date)
981 and (p_date_earned between to_date(hoi.org_information9,'yyyy/mm/dd hh24:mi:ss') and
982 nvl(to_date(hoi.org_information10,'yyyy/mm/dd hh24:mi:ss'),
983 to_date('4712/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')))
984 and hou.business_group_id=p_business_group_id
985 and pppm.assignment_id=p_assignment_id
986 and pppm.personal_payment_method_id=p_third_party_id
987 order by 1 ;
988
989 /* for bug 5983711 */
990 cursor c_get_creator_type(p_source_id number)
991 is
992 select creator_type
993 from pay_element_entries_f
994 where element_entry_id=p_source_id;
995
996 l_fund_name varchar2(100);
997 l_element_entry_id number ;
998 l_payee_id number;
999 l_third_party_id number;
1000 l_creator_type varchar2(20);
1001
1002 begin
1003
1004 if g_debug then
1005 hr_utility.trace('Entering Function super_fund_name');
1006 hr_utility.trace('Value of p_source_id is '||p_source_id);
1007 hr_utility.trace('Value of p_element_reporting_name is '||p_element_reporting_name);
1008 hr_utility.trace('Value of p_date_earned is '||p_date_earned);
1009 hr_utility.trace('Value of p_assignment_action_id is '||p_assignment_action_id);
1010 hr_utility.trace('Value of p_assignment_id is '||p_assignment_id);
1011 hr_utility.trace('Value of p_element_entry_id is '||p_element_entry_id);
1012 end if;
1013
1014 /* Get value of payee id attached to the element */
1015
1016
1017 open c_get_third_party_payment_id(p_element_entry_id,p_assignment_id,p_date_earned);
1018 fetch c_get_third_party_payment_id into l_payee_id;
1019 close c_get_third_party_payment_id ;
1020
1021 if g_debug then
1022 hr_utility.trace(' l_payee_id is is '||l_payee_id);
1023 end if;
1024
1025 /* Get value of Superannuation Fund from Payee Organization Super Fund for the element
1026 This may either be the Seeded Superannuation Element or Employer Charge Element with
1027 third party Payments */
1028
1029 if l_payee_id is not null then
1030
1031 open c_get_third_party_fund_name(l_payee_id,p_date_earned,p_assignment_id,p_business_group_id);
1032 fetch c_get_third_party_fund_name into l_fund_name ;
1033 close c_get_third_party_fund_name ;
1034
1035 if g_debug then
1036 hr_utility.trace('Fund Name is '||l_fund_name);
1037 end if;
1038
1039 end if;
1040
1044 the employee */
1041 if l_fund_name is null then
1042
1043 /* Get value of Superannuation Fund from Superannuation Information Element Attached to
1045 /* Bug 5983711 for Retro entries call c_get_rr_super_fund_name else c_get_super_fund_name */
1046 open c_get_creator_type(p_source_id);
1047 fetch c_get_creator_type into l_creator_type ;
1048 close c_get_creator_type;
1049
1050 If l_creator_type='RR' then
1051 open c_get_rr_super_fund_name(p_assignment_action_id,p_date_earned,p_source_id);
1052 fetch c_get_rr_super_fund_name into l_fund_name,l_element_entry_id;
1053 close c_get_rr_super_fund_name;
1054
1055 elsif l_creator_type in ('AE','EE','AD') then
1056
1057 open c_get_ae_super_fund_name(p_assignment_action_id,p_date_earned,p_source_id);
1058 fetch c_get_ae_super_fund_name into l_fund_name,l_element_entry_id;
1059 close c_get_ae_super_fund_name;
1060
1061 else
1062
1063 open c_get_super_fund_name(p_assignment_action_id,p_date_earned,p_source_id);
1064 fetch c_get_super_fund_name into l_fund_name,l_element_entry_id;
1065 close c_get_super_fund_name;
1066 end if;
1067 if g_debug then
1068 hr_utility.trace('Fund Name is '||l_fund_name);
1069 end if;
1070
1071 if l_element_entry_id is not null then
1072
1073 open c_get_third_party_payment_id(l_element_entry_id,p_assignment_id,p_date_earned);
1074 fetch c_get_third_party_payment_id into l_payee_id;
1075 close c_get_third_party_payment_id ;
1076
1077 if g_debug then
1078 hr_utility.trace(' l_payee_id is is '||l_payee_id);
1079 end if;
1080
1081 /* Get value of Superannuation Fund from Payee Organization Super Fund
1082 for the Superannuation Information Element */
1083
1084 if l_payee_id is not null then
1085
1086 open c_get_third_party_fund_name(l_payee_id,p_date_earned,p_assignment_id,p_business_group_id);
1087 fetch c_get_third_party_fund_name into l_fund_name ;
1088 close c_get_third_party_fund_name ;
1089
1090 if g_debug then
1091 hr_utility.trace('Fund Name is '||l_fund_name);
1092 end if;
1093
1094 end if;
1095
1096 end if;
1097
1098 end if;
1099 /* Return element reporting name if superannuation Fund is not attached */
1100
1101 if l_fund_name is null then
1102
1103 l_fund_name := p_element_reporting_name ;
1104
1105 end if;
1106 if g_debug then
1107 hr_utility.trace('Fund Name RETURNED is '||l_fund_name);
1108 end if;
1109
1110 return l_fund_name;
1111
1112 end super_fund_name;
1113
1114
1115 /* Bug 5591333 - Function is used to compute Hours for Elements.
1116 Function : get_element_payment_hours
1117 Description : This function is to be used for getting the Hours component paid in run.
1118 If Element is a salary basis element, hours will be fetched from
1119 "Normal Hours" seeded element.
1120 Inputs : p_assignment_action_id - Assignment Action ID
1121 p_element_type_id - Element Type ID
1122 p_pay_bases_id - Pay Basis ID
1123 p_run_result_id - Run Result ID
1124 p_effective_date - Effective Date of Run
1125 */
1126
1127 FUNCTION get_element_payment_hours
1128 (
1129 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
1130 p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
1131 p_pay_bases_id IN per_all_assignments_f.pay_basis_id%TYPE,
1132 p_run_result_id IN pay_run_results.run_result_ID%TYPE,
1133 p_effective_date IN pay_payroll_actions.effective_date%TYPE
1134 )
1135 RETURN NUMBER
1136 IS
1137
1138 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1139 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1140
1141 l_result NUMBER := NULL;
1142 l_temp NUMBER := NULL;
1143
1144 CURSOR Cr_value IS (
1145 SELECT prv.result_value
1146 from pay_run_results prr,
1147 pay_run_result_values prv,
1148 pay_element_types_f pet,
1149 pay_input_values_f piv
1150 where prr.assignment_action_id = p_assignment_action_id
1151 and prv.run_result_id = prr.run_result_id
1152 and prv.input_value_id = piv.input_value_id
1153 and prr.element_type_id = pet.element_type_id
1154 and piv.uom like 'H_%'
1155 and piv.element_type_id= pet.element_type_id
1156 and pet.element_name= 'Normal Hours');
1157
1158 CURSOR Cr_element_type_id IS (
1159 SELECT pivf.element_type_id
1160 FROM pay_input_values_f pivf, per_pay_bases ppb
1161 WHERE pivf.input_value_id = ppb.input_value_id
1162 AND ppb.pay_basis_id = p_pay_bases_id);
1163
1164 /* Bug 5967108 - Added Check for Input with Name - Hours */
1165 CURSOR get_hours_input_value
1166 (c_element_type_id pay_element_types_f.element_type_id%TYPE
1167 ,c_effective_date date)
1168 IS
1169 SELECT pivf.input_value_id
1170 ,pivf.name
1171 ,decode(pivf.name,'Hours',1,2) sort_index
1172 FROM pay_input_values_f pivf
1173 WHERE pivf.element_type_id = c_element_type_id
1174 AND substr(pivf.uom,1,1) = 'H'
1175 AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date
1176 ORDER BY sort_index;
1177
1178 CURSOR get_hours_result_value
1179 (c_run_result_id pay_run_result_values.run_result_id%TYPE
1180 ,c_input_value_id pay_run_result_values.input_value_id%TYPE)
1181 IS
1182 SELECT prrv.result_value
1183 FROM pay_run_result_values prrv
1184 WHERE prrv.run_result_id = c_run_result_id
1185 AND prrv.input_value_id = c_input_value_id;
1186
1187
1188 BEGIN
1189
1190 g_debug := hr_utility.debug_enabled;
1191
1192 /* Bug 5967108 - Added Check for Multiple Hours Input
1193 If Input Name = "Hours", return run result for it
1194 else sum the run results for all "H_" UOM type.
1195 */
1196 FOR csr_rec IN get_hours_input_value(p_element_type_id,p_effective_date)
1197 LOOP
1198 OPEN get_hours_result_value(p_run_result_id,csr_rec.input_value_id);
1199 FETCH get_hours_result_value INTO l_temp;
1200 CLOSE get_hours_result_value;
1201 IF csr_rec.sort_index = 1
1202 THEN
1203 l_result := l_temp;
1204 EXIT;
1205 ELSE
1206 l_result := NVL(l_result,0) + NVL(l_temp,0);
1207 END IF;
1208 END LOOP;
1209
1210 IF ( l_result IS NULL OR l_result = 0)
1211 THEN
1212 OPEN Cr_element_type_id;
1213 FETCH Cr_element_type_id INTO l_element_type_id;
1214 CLOSE Cr_element_type_id;
1215
1216 IF p_element_type_id = l_element_type_id THEN
1217 OPEN Cr_value;
1218 FETCH Cr_value INTO l_result;
1219 CLOSE Cr_value;
1220 END IF;
1221 END IF;
1222
1223 /* Avoid Divide by Zero Errors when used for computing Rate, Report Hours and Rate as Null */
1224 IF l_result = 0
1225 THEN
1226 l_result := NULL;
1227 END IF;
1228
1229 RETURN l_result;
1230 END get_element_payment_hours;
1231
1232
1233 /* Bug 5599310 - Function is used to get Rate for Elements.
1234 Function : get_element_payment_rate
1235 Description : This function is to be used for getting the rate if entered for an Earnings element.
1236 Inputs : p_assignment_action_id - Assignment Action ID
1237 p_element_type_id - Element Type ID
1238 p_run_result_id - Run Result ID
1239 p_effective_date - Effective Date of Run
1240 */
1241
1242 FUNCTION get_element_payment_rate
1243 (
1244 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
1245 p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
1246 p_run_result_id IN pay_run_results.run_result_ID%TYPE,
1247 p_effective_date IN pay_payroll_actions.effective_date%TYPE
1248 )
1249 RETURN NUMBER
1250 IS
1251
1252 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1253 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1254
1255 l_result number := NULL;
1256
1257 CURSOR get_rate_input_value
1258 (c_element_type_id pay_element_types_f.element_type_id%TYPE
1259 ,c_effective_date date)
1260 IS
1261 SELECT pivf.input_value_id
1262 FROM pay_input_values_f pivf
1263 WHERE pivf.element_type_id = c_element_type_id
1264 AND upper(pivf.name) like 'RATE%'
1265 AND pivf.uom in ('N','M','I') /*bug 6109668 */
1266 AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date;
1267
1268 CURSOR get_rate_result_value
1269 (c_run_result_id pay_run_result_values.run_result_id%TYPE
1270 ,c_input_value_id pay_run_result_values.input_value_id%TYPE)
1271 IS
1272 SELECT prrv.result_value
1273 FROM pay_run_result_values prrv
1274 WHERE prrv.run_result_id = c_run_result_id
1275 AND prrv.input_value_id = c_input_value_id;
1276
1277 BEGIN
1278
1279 g_debug := hr_utility.debug_enabled;
1280
1281 if g_debug then
1282 hr_utility.trace('Entering get_element_payment_rate');
1283 end if;
1284
1285 OPEN get_rate_input_value(p_element_type_id,p_effective_date);
1289 IF l_input_value_id IS NOT NULL
1286 FETCH get_rate_input_value INTO l_input_value_id;
1287 CLOSE get_rate_input_value;
1288
1290 THEN
1291 OPEN get_rate_result_value(p_run_result_id,l_input_value_id);
1292 FETCH get_rate_result_value INTO l_result;
1293 CLOSE get_rate_result_value;
1294 END IF;
1295
1296 /* Avoid Divide by Zero Errors when used for computing Rate, Report Rate as Null */
1297 IF l_result = 0
1298 THEN
1299 l_result := NULL;
1300 END IF;
1301
1302 if g_debug then
1303 hr_utility.trace('l_result is ' || l_result);
1304 end if;
1305
1306 if g_debug then
1307 hr_utility.trace('Leaving get_element_payment_rate');
1308 end if;
1309
1310 RETURN l_result;
1311 END get_element_payment_rate;
1312
1313 /* Bug 5597052 - Function is used to compute Hours for Leave Taken.
1314 Function : get_leave_taken_hours
1315 Description : This function is to be used for getting the Hours component paid in run.
1316 Inputs : p_assignment_action_id - Assignment Action ID
1317 p_element_type_id - Element Type ID
1318 p_pay_bases_id - Pay Basis ID
1319 p_run_result_id - Run Result ID
1320 p_effective_date - Effective Date of Run
1321 */
1322
1323
1324 FUNCTION get_leave_taken_hours
1325 (
1326 p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
1327 p_run_result_id IN pay_run_results.run_result_ID%TYPE,
1328 p_effective_date IN pay_payroll_actions.effective_date%TYPE
1329 )
1330 RETURN NUMBER
1331 IS
1332
1333 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1334 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1335
1336 l_result number := NULL;
1337
1338 CURSOR get_hours_input_value
1339 (c_element_type_id pay_element_types_f.element_type_id%TYPE
1340 ,c_effective_date date)
1341 IS
1342 SELECT pivf.input_value_id
1343 FROM pay_input_values_f pivf
1344 WHERE pivf.element_type_id = c_element_type_id
1345 and pivf.name IN ('Hours','Days')
1346 AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date;
1347
1348 CURSOR get_hours_result_value
1349 (c_run_result_id pay_run_result_values.run_result_id%TYPE
1350 ,c_input_value_id pay_run_result_values.input_value_id%TYPE)
1351 IS
1352 SELECT prrv.result_value
1353 FROM pay_run_result_values prrv
1354 WHERE prrv.run_result_id = c_run_result_id
1355 AND prrv.input_value_id = c_input_value_id;
1356
1357 BEGIN
1358
1359 g_debug := hr_utility.debug_enabled;
1360
1361 OPEN get_hours_input_value(p_element_type_id,p_effective_date);
1362 FETCH get_hours_input_value INTO l_input_value_id;
1363 CLOSE get_hours_input_value;
1364
1365 IF l_input_value_id IS NOT NULL
1366 THEN
1367 OPEN get_hours_result_value(p_run_result_id,l_input_value_id);
1368 FETCH get_hours_result_value INTO l_result;
1369 CLOSE get_hours_result_value;
1370 END IF;
1371
1372 /* Avoid Divide by Zero Errors when used for computing Rate, Report Hours and Rate as Null */
1373 IF l_result = 0
1374 THEN
1375 l_result := NULL;
1376 END IF;
1377
1378 RETURN l_result;
1379 END get_leave_taken_hours;
1380
1381
1382 /* Bug 5689508 - Function is used to get currency code.
1383 Function : get_currency_code
1384 Description : This function checks for payroll's
1385 default currency code and Business Group Default Currency Code
1386 Inputs : p_business_group_id - Business Group Id
1387 p_payroll_id - Payroll Id
1388 */
1389 FUNCTION get_currency_code
1390 (p_business_group_id in hr_organization_units.business_group_id%type,
1391 p_payroll_id in pay_payrolls_f.payroll_id%type,
1392 p_effective_date in date)
1393 return fnd_currencies.currency_code%type is
1394
1395 lv_currency_code fnd_currencies.currency_code%type;
1396
1397 Cursor payroll_currency_code(c_payroll_id pay_payrolls_f.payroll_id%type) is
1398 select popm.currency_code
1399 from pay_payrolls_f ppf,
1400 pay_org_payment_methods_f popm
1401 where ppf.default_payment_method_id = popm.org_payment_method_id
1402 and ppf.payroll_id = c_payroll_id
1403 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
1404 and p_effective_date between popm.effective_start_date and popm.effective_end_date;
1405
1406 Cursor org_currency_code(c_business_group_id hr_organization_units.business_group_id%type) is
1407 select hoi.org_information10
1408 from hr_organization_information hoi,
1409 hr_organization_units hou
1410 where hou.business_group_id = c_business_group_id
1411 and hou.organization_id = hoi.organization_id
1412 and hoi.org_information_context = 'Business Group Information';
1413
1414 Begin
1415
1416 open payroll_currency_code(p_payroll_id);
1417 fetch payroll_currency_code into lv_currency_code;
1418
1419 if payroll_currency_code%NOTFOUND then
1420 open org_currency_code(p_business_group_id);
1421 fetch org_currency_code into lv_currency_code;
1422 close org_currency_code;
1423 end if;
1424 close payroll_currency_code;
1425
1426 return lv_currency_code;
1427
1428 End get_currency_code;
1429
1433 c_get_prepay_eff_date - Cursor fetches effective date of pre-payments */
1430 /* Bug 9221420 - Added function to retrieve payments effective date if exists
1431 else fetch the pre-payments effective date.
1432 c_get_payments_eff_date - Cursor fetches the effective date of payments
1434
1435 FUNCTION get_effective_date
1436 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE)
1437 RETURN pay_payroll_actions.effective_date%TYPE
1438 IS
1439
1440 CURSOR c_get_payments_eff_date(c_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE)
1441 IS
1442 SELECT max(ppa.effective_date)
1443 FROM pay_payroll_actions ppa ,
1444 pay_assignment_actions paa ,
1445 pay_action_interlocks pai
1446 WHERE pai.locked_action_id = c_assignment_action_id
1447 AND pai.locking_action_id = paa.assignment_action_id
1448 AND paa.pre_payment_id is not null
1449 AND ppa.action_type IN ('H','E','M','A')
1450 AND ppa.payroll_action_id = paa.payroll_action_id;
1451
1452 CURSOR c_get_prepay_eff_date(c_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE)
1453 is
1454 SELECT ppa.effective_date
1455 FROM pay_payroll_actions ppa ,
1456 pay_assignment_actions paa
1457 WHERE paa.assignment_action_id = c_assignment_action_id
1458 AND ppa.payroll_action_id = paa.payroll_action_id;
1459
1460 l_effective_date pay_payroll_actions.effective_date%TYPE;
1461
1462 BEGIN
1463
1464 OPEN c_get_payments_eff_date(p_assignment_action_id);
1465 FETCH c_get_payments_eff_date INTO l_effective_date ;
1466
1467 IF l_effective_date IS NULL THEN
1468
1469 OPEN c_get_prepay_eff_date(p_assignment_action_id);
1470 FETCH c_get_prepay_eff_date INTO l_effective_date ;
1471 CLOSE c_get_prepay_eff_date;
1472
1473 END IF;
1474
1475 CLOSE c_get_payments_eff_date;
1476
1477 return l_effective_date ;
1478
1479 END get_effective_date;
1480
1481
1482 /* Bug : 8344279
1483 * Name : get_doc_eit
1484 * Purpuse : returns flag indicating whether Pay advice should be printed
1485 *
1486 * Arguments : p_assignment_id = assignment_id
1487 * p_payroll_action_id = run_payroll_action_id
1488 *
1489 * Notes : Priority for levels (high to low)
1490 * Person
1491 * Location
1492 * Organization
1493 * Business Group
1494 **/
1495 function get_doc_eit (p_assignment_id in number,
1496 p_payroll_action_id in number)
1497 return varchar2 is
1498
1499 cursor get_assign_info (p_assignment_id number, p_payroll_action_id number) is
1500 select paf.person_id, paf.location_id, paf.organization_id, paf.business_group_id
1501 from per_all_assignments_f paf, pay_payroll_actions ppa
1502 where paf.assignment_id = p_assignment_id
1503 and ppa.payroll_action_id = p_payroll_action_id
1504 and (ppa.effective_date between paf.effective_start_date and paf.effective_end_date);
1505
1506 cursor get_person_eit (p_person_id number) is
1507 select pei_information3
1508 from per_people_extra_info
1509 where person_id = p_person_id
1510 and information_type = 'HR_SELF_SERVICE_PER_PREFERENCE'
1511 and pei_information1 = 'PAYSLIP';
1512
1513 cursor get_loc_eit (p_location_id number) is
1514 select lei_information3
1515 from hr_location_extra_info
1516 where location_id = p_location_id
1517 and information_type = 'HR_SELF_SERVICE_LOC_PREFERENCE'
1518 and lei_information1 = 'PAYSLIP';
1519
1520 cursor get_org_eit (p_organization_id number) is
1521 select org_information3
1522 from hr_organization_information
1523 where organization_id = p_organization_id
1524 and org_information_context = 'HR_SELF_SERVICE_ORG_PREFERENCE'
1525 and org_information1 = 'PAYSLIP';
1526
1527 cursor get_bg_eit (p_business_group_id number) is
1528 select org_information3
1529 from hr_organization_information
1530 where organization_id = p_business_group_id
1531 and org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
1532 and org_information1 = 'PAYSLIP';
1533
1534 l_person_id number;
1535 l_loc_id number;
1536 l_org_id number;
1537 l_bg_id number;
1538
1539 l_value varchar2(1);
1540 l_result varchar2(1);
1541
1542 begin
1543
1544 open get_assign_info(p_assignment_id, p_payroll_action_id);
1545 fetch get_assign_info into l_person_id, l_loc_id, l_org_id, l_bg_id;
1546 close get_assign_info;
1547
1548 /* person level eit */
1549 open get_person_eit(l_person_id);
1550 fetch get_person_eit into l_value;
1551
1552 if get_person_eit%found then
1553 l_result := l_value;
1554 else
1555 /* location level eit */
1556 open get_loc_eit(l_loc_id);
1557 fetch get_loc_eit into l_value;
1558 if get_loc_eit%found then
1559 l_result := l_value;
1560 else
1561 /* organization level eit*/
1562 open get_org_eit(l_org_id);
1563 fetch get_org_eit into l_value;
1564 if get_org_eit%found then
1565 l_result := l_value;
1566 else
1567 /* business group level eit */
1568 open get_bg_eit(l_bg_id);
1569 fetch get_bg_eit into l_value;
1570 if get_bg_eit%found then
1571 l_result := l_value;
1572 else
1573 l_result := 'Y';
1574 end if;
1575 close get_bg_eit;
1576 end if;
1577 close get_org_eit;
1578 end if;
1579 close get_loc_eit;
1580 end if;
1581 close get_person_eit;
1582
1583 return l_result;
1584
1585 end get_doc_eit;
1586
1587
1588 end pay_au_soe_pkg;
[Home] [Help]