[Home] [Help]
1: package body pay_us_tax_bals_pkg as
2: /* $Header: pyustxbl.pkb 120.9.12020000.2 2012/11/01 17:21:44 emunisek ship $ */
3: --
4: -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5: --
153: BEGIN
154: --
155: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
156: hr_utility.set_message_token('PROCEDURE',
157: 'pay_us_tax_bals_pkg.'||p_procedure);
158: hr_utility.set_message_token('STEP',p_step);
159: hr_utility.raise_error;
160: --
161: END local_error;
177: l_found boolean;
178: begin
179: -- Look to see if the group level balance is in our cache.
180: --
181: hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 10);
182: --
183: l_count := 0;
184: l_found := FALSE;
185: while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
183: l_count := 0;
184: l_found := FALSE;
185: while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
186: if (p_grp_dvl_dimension = g_dim_tbl_grp(l_count)) then
187: hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 20);
188: --
189: p_asg_lvl_dimension := g_dim_tbl_asg(l_count);
190: p_asg_cursor := g_dim_tbl_crs(l_count);
191: p_asg_vdate_cursor := g_dim_tbl_vtd(l_count);
202: end if;
203: l_count := l_count + 1;
204: end loop;
205: --
206: hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 30);
207: p_found := l_found;
208: --
209: end;
210: --
216: p_dimension_suffix varchar2) return number is
217: l_defined_balance_id number;
218: --
219: begin
220: hr_utility.set_location('pay_us_tax_bals_pkg.get_defined_balance', 10);
221: --
222:
223: SELECT creator_id
224: INTO l_defined_balance_id
226: WHERE user_entity_name like
227: translate(p_balance_name||'_'||p_dimension_suffix,' ','_')
228: AND legislation_code = 'US';
229: --
230: hr_utility.set_location('pay_us_tax_bals_pkg.get_defined_balance', 20);
231: return l_defined_balance_id;
232: end;
233: --
234: ------------------------------------------------------------------------------
248: begin
249: --
250: -- Is the assignment on a payroll.
251: --
252: hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date', 10);
253: select ''
254: into l_dummy
255: from per_assignments_f paf
256: where paf.assignment_id = p_assignment_id
258: and paf.effective_end_date
259: and paf.payroll_id is not null;
260:
261: --
262: hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date', 20);
263: return p_virtual_date;
264: exception
265: when no_data_found then
266: --
269: declare
270: sql_cursor number;
271: l_rows number;
272: begin
273: hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date',
274: 30);
275: sql_cursor := dbms_sql.open_cursor;
276: dbms_sql.parse(sql_cursor, p_asg_vdate_cursor, dbms_sql.v7);
277: dbms_sql.bind_variable (sql_cursor, 'ASSIGNMENT_ID',
284: l_rows := dbms_sql.execute(sql_cursor);
285: l_rows := dbms_sql.fetch_rows (sql_cursor);
286: if l_rows > 0 then
287: hr_utility.set_location(
288: 'pay_us_tax_bals_pkg.get_virtual_date', 40);
289: dbms_sql.column_value (sql_cursor, 1, l_virtual_date);
290: --
291: select max(ppf.effective_end_date)
292: into l_virtual_date2
302: --
303: if l_virtual_date is null then
304: if l_virtual_date2 is null then
305: hr_utility.set_location(
306: 'pay_us_tax_bals_pkg.get_virtual_date', 60);
307: local_error('get_virtual_date', 2);
308: else
309: hr_utility.set_location(
310: 'pay_us_tax_bals_pkg.get_virtual_date', 70);
306: 'pay_us_tax_bals_pkg.get_virtual_date', 60);
307: local_error('get_virtual_date', 2);
308: else
309: hr_utility.set_location(
310: 'pay_us_tax_bals_pkg.get_virtual_date', 70);
311: l_res_date := l_virtual_date2;
312: end if;
313: else
314: if l_virtual_date2 is null then
312: end if;
313: else
314: if l_virtual_date2 is null then
315: hr_utility.set_location(
316: 'pay_us_tax_bals_pkg.get_virtual_date', 80);
317: l_res_date := l_virtual_date;
318: else
319: hr_utility.set_location(
320: 'pay_us_tax_bals_pkg.get_virtual_date', 90);
316: 'pay_us_tax_bals_pkg.get_virtual_date', 80);
317: l_res_date := l_virtual_date;
318: else
319: hr_utility.set_location(
320: 'pay_us_tax_bals_pkg.get_virtual_date', 90);
321: l_res_date := least(l_virtual_date, l_virtual_date2);
322: end if;
323: end if;
324: --
323: end if;
324: --
325: else
326: hr_utility.set_location(
327: 'pay_us_tax_bals_pkg.get_virtual_date', 50);
328: local_error('get_virtual_date', 1);
329: end if;
330: --
331: dbms_sql.close_cursor(sql_cursor);
389: l_run_route_bool := true;
390: END IF;
391:
392: l_balance_value := 0;
393: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 10);
394: --
395: -- Get the assignment level version.
396: --
397: get_asg_for_grp_lvl(p_database_suffix,
405: if l_grp_lat_exist then
406: --
407: -- Are there latest balances available.
408: --
409: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 20);
410: l_defined_balance_id := get_defined_balance(p_balance_name,
411: l_asg_data_suffix);
412: --
413: begin
438: --
439: -- OK, we can sum the values of the assignment balances to get the
440: -- group balance.
441: --
442: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 30);
443: begin
444: --
445: sql_cursor := dbms_sql.open_cursor;
446: dbms_sql.parse(sql_cursor, l_asg_data_cursor, dbms_sql.v7);
474: l_rows := dbms_sql.fetch_rows (sql_cursor);
475: cnt := cnt + 1;
476: if l_rows > 0 then
477: hr_utility.set_location(
478: 'pay_us_tax_bals_pkg.get_grp_asg_value', 40);
479: dbms_sql.column_value (sql_cursor, 1, l_asg_id);
480: --
481: l_virtual_date := get_virtual_date(l_asg_id, p_virtual_date,
482: l_asg_balance_time,
498: else
499: --
500: -- No latets balances available. Run the route.
501: --
502: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 50);
503: l_defined_balance_id := get_defined_balance(p_balance_name,
504: p_database_suffix);
505: l_balance_value := pay_balance_pkg.get_value_lock
506: (l_defined_balance_id,
514: --
515: -- Can not sum the assignment level balances, thus run group
516: -- level route.
517: --
518: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 60);
519: l_defined_balance_id := get_defined_balance(p_balance_name,
520: p_database_suffix);
521: l_balance_value := pay_balance_pkg.get_value_lock
522: (l_defined_balance_id,
526: p_asg_lock
527: );
528: end if;
529: --
530: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 70);
531: return l_balance_value;
532: --
533: end;
534: --
570: l_run_route_bool := true;
571: END IF;
572:
573: l_balance_value := 0;
574: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_act_value', 10);
575: --
576: l_defined_balance_id := get_defined_balance(p_balance_name,
577: p_database_suffix);
578: l_balance_value := pay_balance_pkg.get_value (l_defined_balance_id,
580: l_run_route_bool,
581: FALSE
582: );
583: --
584: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_act_value', 20);
585: return l_balance_value;
586: --
587: end;
588: --
598: p_assignment_action_id number default null,
599: p_asg_lock varchar2)
600: return number is
601: begin
602: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 10);
603: if p_assignment_action_id is null then
604: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 20);
605: return get_grp_asg_value(p_assignment_id,
606: p_virtual_date,
600: return number is
601: begin
602: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 10);
603: if p_assignment_action_id is null then
604: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 20);
605: return get_grp_asg_value(p_assignment_id,
606: p_virtual_date,
607: p_balance_name,
608: p_database_suffix,
609: p_gre_id,
610: p_jurisdiction_code,
611: p_asg_lock);
612: else
613: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 30);
614: return get_grp_act_value(p_assignment_action_id,
615: p_virtual_date,
616: p_balance_name,
617: p_database_suffix,
644: --
645: BEGIN
646: --
647:
648: hr_utility.set_location('pay_us_tax_bals_pkg.balance_name'||p_balance_name, 9);
649: hr_utility.trace('p_dimension_suffix = '||p_dimension_suffix);
650: hr_utility.trace('p_balance_name = '||p_balance_name);
651: hr_utility.trace('p_asg_type = '||p_asg_type);
652: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 10);
648: hr_utility.set_location('pay_us_tax_bals_pkg.balance_name'||p_balance_name, 9);
649: hr_utility.trace('p_dimension_suffix = '||p_dimension_suffix);
650: hr_utility.trace('p_balance_name = '||p_balance_name);
651: hr_utility.trace('p_asg_type = '||p_asg_type);
652: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 10);
653:
654: -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
655: -- table to determine if we want to call the run_result route instead of
656: -- the run_balance route.
678: /* commenting of the following code. From now on we will be using
679: the Balance Reporting Arch */
680: /*
681: if p_asg_type = 'GRE' then
682: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
683: 20);
684: return get_grp_value(p_assignment_id,
685: p_virtual_date,
686: p_balance_name,
694: */
695: l_defined_balance_id := get_defined_balance(p_balance_name,
696: p_dimension_suffix);
697: IF p_dimension_suffix not like '%PAY%' THEN
698: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 30);
699: return pay_balance_pkg.get_value (l_defined_balance_id,
700: p_assignment_action_id,
701: l_run_route_bool,
702: FALSE
701: l_run_route_bool,
702: FALSE
703: );
704: ELSE /* If payments dimension then must execute DB item 395029 */
705: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 40);
706: return pay_balance_pkg.get_value (l_defined_balance_id,
707: p_assignment_action_id,
708: true );
709: -- END IF;
714: /* commenting of the following code. From now on we will be using
715: the Balance Reporting Arch */
716: /*
717: if p_asg_type = 'GRE' then
718: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
719: 50);
720: return get_grp_value(p_assignment_id,
721: p_virtual_date,
722: p_balance_name,
729: else
730: */
731: l_defined_balance_id := get_defined_balance(p_balance_name,
732: p_dimension_suffix);
733: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
734: 60);
735: return pay_balance_pkg.get_value_lock
736: (l_defined_balance_id,
737: p_assignment_id,
2476: g_nxt_free_dim := 4;
2477:
2478:
2479: -- hr_utility.trace_on(null,'tx');
2480: end pay_us_tax_bals_pkg;