[Home] [Help]
1: package body pay_us_tax_bals_pkg as
2: /* $Header: pyustxbl.pkb 120.3 2005/09/26 23:59:57 rdhingra noship $ */
3: --
4: -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5: --
140: BEGIN
141: --
142: hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
143: hr_utility.set_message_token('PROCEDURE',
144: 'pay_us_tax_bals_pkg.'||p_procedure);
145: hr_utility.set_message_token('STEP',p_step);
146: hr_utility.raise_error;
147: --
148: END local_error;
164: l_found boolean;
165: begin
166: -- Look to see if the group level balance is in our cache.
167: --
168: hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 10);
169: --
170: l_count := 0;
171: l_found := FALSE;
172: while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
170: l_count := 0;
171: l_found := FALSE;
172: while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
173: if (p_grp_dvl_dimension = g_dim_tbl_grp(l_count)) then
174: hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 20);
175: --
176: p_asg_lvl_dimension := g_dim_tbl_asg(l_count);
177: p_asg_cursor := g_dim_tbl_crs(l_count);
178: p_asg_vdate_cursor := g_dim_tbl_vtd(l_count);
189: end if;
190: l_count := l_count + 1;
191: end loop;
192: --
193: hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 30);
194: p_found := l_found;
195: --
196: end;
197: --
203: p_dimension_suffix varchar2) return number is
204: l_defined_balance_id number;
205: --
206: begin
207: hr_utility.set_location('pay_us_tax_bals_pkg.get_defined_balance', 10);
208: --
209:
210: SELECT creator_id
211: INTO l_defined_balance_id
213: WHERE user_entity_name like
214: translate(p_balance_name||'_'||p_dimension_suffix,' ','_')
215: AND legislation_code = 'US';
216: --
217: hr_utility.set_location('pay_us_tax_bals_pkg.get_defined_balance', 20);
218: return l_defined_balance_id;
219: end;
220: --
221: ------------------------------------------------------------------------------
235: begin
236: --
237: -- Is the assignment on a payroll.
238: --
239: hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date', 10);
240: select ''
241: into l_dummy
242: from per_assignments_f paf
243: where paf.assignment_id = p_assignment_id
245: and paf.effective_end_date
246: and paf.payroll_id is not null;
247:
248: --
249: hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date', 20);
250: return p_virtual_date;
251: exception
252: when no_data_found then
253: --
256: declare
257: sql_cursor number;
258: l_rows number;
259: begin
260: hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date',
261: 30);
262: sql_cursor := dbms_sql.open_cursor;
263: dbms_sql.parse(sql_cursor, p_asg_vdate_cursor, dbms_sql.v7);
264: dbms_sql.bind_variable (sql_cursor, 'ASSIGNMENT_ID',
271: l_rows := dbms_sql.execute(sql_cursor);
272: l_rows := dbms_sql.fetch_rows (sql_cursor);
273: if l_rows > 0 then
274: hr_utility.set_location(
275: 'pay_us_tax_bals_pkg.get_virtual_date', 40);
276: dbms_sql.column_value (sql_cursor, 1, l_virtual_date);
277: --
278: select max(ppf.effective_end_date)
279: into l_virtual_date2
289: --
290: if l_virtual_date is null then
291: if l_virtual_date2 is null then
292: hr_utility.set_location(
293: 'pay_us_tax_bals_pkg.get_virtual_date', 60);
294: local_error('get_virtual_date', 2);
295: else
296: hr_utility.set_location(
297: 'pay_us_tax_bals_pkg.get_virtual_date', 70);
293: 'pay_us_tax_bals_pkg.get_virtual_date', 60);
294: local_error('get_virtual_date', 2);
295: else
296: hr_utility.set_location(
297: 'pay_us_tax_bals_pkg.get_virtual_date', 70);
298: l_res_date := l_virtual_date2;
299: end if;
300: else
301: if l_virtual_date2 is null then
299: end if;
300: else
301: if l_virtual_date2 is null then
302: hr_utility.set_location(
303: 'pay_us_tax_bals_pkg.get_virtual_date', 80);
304: l_res_date := l_virtual_date;
305: else
306: hr_utility.set_location(
307: 'pay_us_tax_bals_pkg.get_virtual_date', 90);
303: 'pay_us_tax_bals_pkg.get_virtual_date', 80);
304: l_res_date := l_virtual_date;
305: else
306: hr_utility.set_location(
307: 'pay_us_tax_bals_pkg.get_virtual_date', 90);
308: l_res_date := least(l_virtual_date, l_virtual_date2);
309: end if;
310: end if;
311: --
310: end if;
311: --
312: else
313: hr_utility.set_location(
314: 'pay_us_tax_bals_pkg.get_virtual_date', 50);
315: local_error('get_virtual_date', 1);
316: end if;
317: --
318: dbms_sql.close_cursor(sql_cursor);
376: l_run_route_bool := true;
377: END IF;
378:
379: l_balance_value := 0;
380: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 10);
381: --
382: -- Get the assignment level version.
383: --
384: get_asg_for_grp_lvl(p_database_suffix,
392: if l_grp_lat_exist then
393: --
394: -- Are there latest balances available.
395: --
396: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 20);
397: l_defined_balance_id := get_defined_balance(p_balance_name,
398: l_asg_data_suffix);
399: --
400: begin
425: --
426: -- OK, we can sum the values of the assignment balances to get the
427: -- group balance.
428: --
429: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 30);
430: begin
431: --
432: sql_cursor := dbms_sql.open_cursor;
433: dbms_sql.parse(sql_cursor, l_asg_data_cursor, dbms_sql.v7);
461: l_rows := dbms_sql.fetch_rows (sql_cursor);
462: cnt := cnt + 1;
463: if l_rows > 0 then
464: hr_utility.set_location(
465: 'pay_us_tax_bals_pkg.get_grp_asg_value', 40);
466: dbms_sql.column_value (sql_cursor, 1, l_asg_id);
467: --
468: l_virtual_date := get_virtual_date(l_asg_id, p_virtual_date,
469: l_asg_balance_time,
485: else
486: --
487: -- No latets balances available. Run the route.
488: --
489: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 50);
490: l_defined_balance_id := get_defined_balance(p_balance_name,
491: p_database_suffix);
492: l_balance_value := pay_balance_pkg.get_value_lock
493: (l_defined_balance_id,
501: --
502: -- Can not sum the assignment level balances, thus run group
503: -- level route.
504: --
505: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 60);
506: l_defined_balance_id := get_defined_balance(p_balance_name,
507: p_database_suffix);
508: l_balance_value := pay_balance_pkg.get_value_lock
509: (l_defined_balance_id,
513: p_asg_lock
514: );
515: end if;
516: --
517: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 70);
518: return l_balance_value;
519: --
520: end;
521: --
557: l_run_route_bool := true;
558: END IF;
559:
560: l_balance_value := 0;
561: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_act_value', 10);
562: --
563: l_defined_balance_id := get_defined_balance(p_balance_name,
564: p_database_suffix);
565: l_balance_value := pay_balance_pkg.get_value (l_defined_balance_id,
567: l_run_route_bool,
568: FALSE
569: );
570: --
571: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_act_value', 20);
572: return l_balance_value;
573: --
574: end;
575: --
585: p_assignment_action_id number default null,
586: p_asg_lock varchar2)
587: return number is
588: begin
589: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 10);
590: if p_assignment_action_id is null then
591: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 20);
592: return get_grp_asg_value(p_assignment_id,
593: p_virtual_date,
587: return number is
588: begin
589: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 10);
590: if p_assignment_action_id is null then
591: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 20);
592: return get_grp_asg_value(p_assignment_id,
593: p_virtual_date,
594: p_balance_name,
595: p_database_suffix,
596: p_gre_id,
597: p_jurisdiction_code,
598: p_asg_lock);
599: else
600: hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 30);
601: return get_grp_act_value(p_assignment_action_id,
602: p_virtual_date,
603: p_balance_name,
604: p_database_suffix,
631: --
632: BEGIN
633: --
634:
635: hr_utility.set_location('pay_us_tax_bals_pkg.balance_name'||p_balance_name, 9);
636: hr_utility.trace('p_dimension_suffix = '||p_dimension_suffix);
637: hr_utility.trace('p_balance_name = '||p_balance_name);
638: hr_utility.trace('p_asg_type = '||p_asg_type);
639: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 10);
635: hr_utility.set_location('pay_us_tax_bals_pkg.balance_name'||p_balance_name, 9);
636: hr_utility.trace('p_dimension_suffix = '||p_dimension_suffix);
637: hr_utility.trace('p_balance_name = '||p_balance_name);
638: hr_utility.trace('p_asg_type = '||p_asg_type);
639: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 10);
640:
641: -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
642: -- table to determine if we want to call the run_result route instead of
643: -- the run_balance route.
665: /* commenting of the following code. From now on we will be using
666: the Balance Reporting Arch */
667: /*
668: if p_asg_type = 'GRE' then
669: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
670: 20);
671: return get_grp_value(p_assignment_id,
672: p_virtual_date,
673: p_balance_name,
681: */
682: l_defined_balance_id := get_defined_balance(p_balance_name,
683: p_dimension_suffix);
684: IF p_dimension_suffix not like '%PAY%' THEN
685: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 30);
686: return pay_balance_pkg.get_value (l_defined_balance_id,
687: p_assignment_action_id,
688: l_run_route_bool,
689: FALSE
688: l_run_route_bool,
689: FALSE
690: );
691: ELSE /* If payments dimension then must execute DB item 395029 */
692: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 40);
693: return pay_balance_pkg.get_value (l_defined_balance_id,
694: p_assignment_action_id,
695: true );
696: -- END IF;
701: /* commenting of the following code. From now on we will be using
702: the Balance Reporting Arch */
703: /*
704: if p_asg_type = 'GRE' then
705: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
706: 50);
707: return get_grp_value(p_assignment_id,
708: p_virtual_date,
709: p_balance_name,
716: else
717: */
718: l_defined_balance_id := get_defined_balance(p_balance_name,
719: p_dimension_suffix);
720: hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
721: 60);
722: return pay_balance_pkg.get_value_lock
723: (l_defined_balance_id,
724: p_assignment_id,
2177: g_nxt_free_dim := 4;
2178:
2179:
2180: -- hr_utility.trace_on(null,'tx');
2181: end pay_us_tax_bals_pkg;