1 package pay_balance_pkg as
2 /* $Header: pybaluex.pkh 120.4.12010000.1 2008/07/27 22:08:22 appldev ship $ */
3 --
4 /*
5 --
6 -- Copyright (c) Oracle Corporation 1995 All rights reserved
7 /*
8 PRODUCT
9 Oracle*Payroll
10 --
11 NAME
12 pybaluex.pkh - PL/SQL Balance User Exit
13 --
14 DESCRIPTION
15 Procedure and Function headers for retrieving balance and database item
16 values using dynamic pl/sql
17 --
18 MODIFIED (DD-MON-YYYY)
19 nbristow 16-JAN-2006 - Added maintain_balances_for_action.
20 RThirlby 20-NOV-2006 - 5619425 - Altered following procedures to take
21 p_eff_date and p_delta parameters to enable
22 Generate Run Balances in DELTA mode to use balance
23 attributes, and thus process more quickly:
24 create_asg_balance, create_all_asg_balances
25 create_group_balance, create_all_group_balances
26 RThirlby 24-OCT-2006 - Altered code so that Generate Run Balances
27 SINGLE mode will utilise balance_attributes
28 to improve performance. Added new paramter
29 p_bal_att to create_asg_balance, and
30 create_group_balance.
31 nbristow 06-JUN-2005 - Added new contexts.
32 RThirlby 06-MAY-2005 - Added Original Entry Id to get value call.
33 nbristow 04-MAY-2005 - Added Payroll ID to get_value call.
34 nbristow 03-MAY-2005 - Added Time Definition ID and Balance Date
35 for generate Run Balances.
36 nbristow 15-JUN-2004 - Made the set run balance functions
37 visable.
38 thabara 30-MAR-2004 - Added create_rr_asg_balances.
39 ALogue 22-MAR-2004 - Added set_check_latest_balances and
40 unset_check_latest_balances procedures.
41 Also removed last change.
42 ALogue 19-MAR-2004 - Added CHECK_RUN_BALANCES global.
43 nbristow 09-DEC-2003 - Added get_context_internal.
44 nbristow 01-DEC-2003 - Changes to improve the performance of
45 the assignment level balance creation.
46 ALogue 05-JUN-2003 - Bug 2960902 - Added new overloaded procedure
47 invalidate_run_balances passed only balance_type_id
48 and trash_date.
49 RThirlby 20-May-2003 - Removed defaults of 'FALSE' on parameters
50 p_get_rr_route and p_get_rb_route in get_value.
51 RThirlby 19-MAY-2003 - Bug 2898484 - 2 new parameters, p_source_text2
52 and p_source_number added to get_value with null
53 defaults. Also defaults of FALSE added to
54 parameters p_get_rr_route and p_get_rb_route.
55 RThirlby 15-MAY-2003 - Bug 2959584 - New procedure initialise_run_balance,
56 will be called from insert trigger for
57 pay_defined_balances.
58 SuSivasu 11-APR-2003 - Overloading of get_value to include the
59 ORIGINAL_ENTRY_ID context.
60 nbristow 21-FEB-2003 - Added new PAYMENT period type.
61 nbristow 07-FEB-2003 - Added new set_contexts
62 nbristow 05-FEB-2003 - Added new contexts.
63 nbristow 05-DEC-2002 - Performance changes for run balances.
64 nbristow 16-OCT-2002 - Added get_period_type_start procedure.
65 nbristow 10-OCT-2002 - Changes for Run Balances phase 2, including
66 batch balance retrieval.
67 RThirlby 03-OCT-2002 - Bug 2552864 - Added new parameter to
68 invalidate_run_balances.
69 RThirlby 02-MAY-2002 - Added following procedures and supporting functions
70 for maintaining assignment and group level run
71 balances - used in reversal and balance
72 adjustments:
73 create_asg_balance
74 create_all_asg_balances
75 create_group_balance
76 create_all_group_balances
77 find_context
78 split_jurisdiction
79 ins_run_balance
80 Added support for rollback of run balances, with
81 new procedure remove_balance_contribs.
82 RThirlby 07-MAR-2002 - Added procedure remove_asg_contribs_from_grp_bals.
83 RThirlby 01-MAR-2002 - 2 more overloaded versions of get_value to use run
84 balance architecture. 2 overloaded versions of
85 get_run_balance (the run balance equivalent of
86 get_db_item).
87 Also added procedure invalidate_run_balances -
88 which is called from the balance_feeds triggers.
89 SuSivasu 28-Feb-2002 - Added chk_contex function.
90 RThirlby 06-APR-2001 - Overloaded version of run_db_item, for performance
91 improvements.
92 SuSivasu 06-APR-2001 - Overloading of get_value to include the
93 SOURCE_TEXT context.
94 nbristow 14-SEP-2000 - Changes for the new expiry checking
95 types for complex balances.
96 nbristow 30-JUN-2000 - Added p_tax_group to get_value call.
97 nbristow 31-MAY-2000 - Added new get_value that supplies context values.
98 dzshanno 30-JUN-1998 - add function get_context, called by core archiver
99 nbristow 18-DEC-1996 - Fixed previous change by reverting the get_value
100 call to its original format and adding a new
101 function get_value_lock.
102 nbristow 20-NOV-1996 - Changed get_value (date mode) to be called
103 with a flag indicating whether the assignment
104 rows are to be locked.
105 mwcallag 20-FEB-1995 - Overloading of get_value rather than using a
106 default parameter added since currently calls
107 from forms do not support default parameters.
108 mwcallag 08-FEB-1995 - Support for latest balances added.
109 mwcallag 18-JAN-1995 - Created.
110 */
111 --
112 -- Setup the types
113 --
114 type t_balance_value_rec is record
115 (defined_balance_id pay_defined_balances.defined_balance_id%type,
116 balance_value number
117 );
118 type t_balance_value_tab is table of t_balance_value_rec
119 index by binary_integer;
120 --
121 type t_detailed_bal_out_rec is record
122 (defined_balance_id pay_defined_balances.defined_balance_id%type,
123 tax_unit_id pay_assignment_actions.tax_unit_id%type,
124 jurisdiction_code pay_run_results.jurisdiction_code%type,
125 source_id pay_run_result_values.result_value%type,
126 source_text pay_run_result_values.result_value%type,
127 source_number pay_run_result_values.result_value%type,
128 source_text2 pay_run_result_values.result_value%type,
129 time_def_id pay_run_results.time_definition_id%type,
130 balance_date pay_run_results.end_date%type,
131 local_unit_id pay_run_results.local_unit_id%type,
132 source_number2 pay_run_result_values.result_value%type,
133 organization_id pay_run_result_values.result_value%type,
134 balance_value number,
135 --
136 /* These are internal values set by the procedures */
137 balance_found boolean,
138 jurisdiction_lvl pay_balance_types.jurisdiction_level%type
139 );
140 type t_detailed_bal_out_tab is table of t_detailed_bal_out_rec
141 index by binary_integer;
142 --
143 -- Context combination cache.
144 --
145 type t_context_rec is record
146 (
147 tax_unit_id pay_assignment_actions.tax_unit_id%type,
148 jurisdiction_code pay_run_results.jurisdiction_code%type,
149 source_id number,
150 source_text pay_run_result_values.result_value%type,
151 source_number number,
152 source_text2 pay_run_result_values.result_value%type,
153 time_def_id pay_run_results.time_definition_id%type,
154 balance_date pay_run_results.end_date%type,
155 local_unit_id pay_run_results.local_unit_id%type,
156 source_number2 number,
157 organization_id number
158 );
159 --
160 type t_context_tab is table of t_context_rec index by binary_integer;
161 --
162 -- balance expiry checking constants
163 --
164 BALANCE_NOT_EXPIRED constant number := 0 ;
165 BALANCE_EXPIRED constant number := 1 ;
166 --
167 --------------------------- get_period_type_start -------------------------------
168 /* Name : get_period_type_start
169 Purpose : This returns the start date of a period type given an
170 effective_date.
171 Arguments :
172 p_period_type is mandatory
173 p_effective_date is mandatory
174 p_start_date_code is only required for period_type DYNAMIC
175 p_payroll_id is only required for period_type PERIOD
176 p_bus_grp is only needed for period_type TYEAR, TQUARTER, FYEAR and
177 FQUARTER
178 p_action_type is only needed for period_type PAYMENT
179 p_asg_action is only needed for period_type PAYMENT
180
181 Notes :
182 */
183 procedure get_period_type_start(p_period_type in varchar2,
184 p_effective_date in date,
185 p_start_date out nocopy date,
186 p_start_date_code in varchar2 default null,
187 p_payroll_id in number default null,
188 p_bus_grp in number default null,
189 p_action_type in varchar2 default null,
190 p_asg_action in number default null);
191 --
192 ------------------------------- chk_context -------------------------------
193 --
194 function chk_context
195 (
196 p_context_id in number,
197 p_route_id in number
198 ) return varchar2;
199 --
200 ------------------------------- get_context -------------------------------
201 --
202 function get_context
203 (
204 p_context_name in varchar2
205 ) return varchar2;
206 function get_context_internal
207 (
208 p_context_name in varchar2
209 ) return varchar2;
210 --
211 ------------------------------- set_context -------------------------------
212 --
213 procedure set_context
214 (
215 p_context_name in varchar2,
216 p_context_value in varchar2
217 );
218 --
219 procedure set_context
220 (
221 p_legislation_code in varchar2,
222 p_context_name in varchar2,
223 p_context_value in varchar2
224 );
225 --
226 ------------------------------- run_db_item -------------------------------
227 --
228 function run_db_item
229 (
230 p_database_name in varchar2,
231 p_bus_group_id in number,
232 p_legislation_code in varchar2
233 ) return varchar2;
234 --
235 ------------------------------- run_db_item -------------------------------
236 --
237 function run_db_item
238 (p_def_bal_id in number) return varchar2;
239 --
240 -------------------------- get_run_balance -------------------------------
241 --
242 --function get_run_balance
243 --(p_def_bal_id in number
244 --,p_priority in number
245 --,p_route_type in varchar2) return varchar2;
246 --
247 ------------------------- get_run_balance --------------------------------
248 --
249 --function get_run_balance
250 --(p_user_name in varchar2
251 --,p_business_group_id in number
252 --,p_legislation_code in varchar2
253 --,p_route_type in varchar2
254 --) return varchar2;
255 ------------------------- check_bal_expiry -------------------------------
256 --
257 function check_bal_expiry
261 p_dimension_name in varchar2, -- balance dimension name.
258 (
259 p_bal_owner_asg_action in number, -- assact created balance.
260 p_assignment_action_id in number, -- current assact..
262 p_expiry_checking_level in varchar2,
263 p_expiry_checking_code in varchar2,
264 p_bal_context_str in varchar2 -- list of context values.
265 ) return boolean;
266 --
267 ------------------------------- get_value -------------------------------
268 --
269 -- ---- Assignment action mode -----
270 --
271 procedure get_value (p_assignment_action_id in number,
272 p_defined_balance_lst in t_balance_value_tab,
273 p_context_lst in t_context_tab,
274 p_get_rr_route in boolean default FALSE,
275 p_get_rb_route in boolean default FALSE,
276 p_output_table out nocopy t_detailed_bal_out_tab);
277 procedure get_value (p_assignment_action_id in number,
278 p_defined_balance_lst in out nocopy t_balance_value_tab,
279 p_get_rr_route in boolean default FALSE,
280 p_get_rb_route in boolean default FALSE);
281 function get_value
282 (
283 p_defined_balance_id in number,
284 p_assignment_action_id in number,
285 p_tax_unit_id in number,
286 p_jurisdiction_code in varchar2,
287 p_source_id in number,
288 p_tax_group in varchar2,
289 p_date_earned in date
290 ) return number;
291 --
292 function get_value
293 (
294 p_defined_balance_id in number,
295 p_assignment_action_id in number
296 ) return number;
297 --
298 function get_value
299 (
300 p_defined_balance_id in number,
301 p_assignment_action_id in number,
302 p_always_get_db_item in boolean
303 ) return number;
304 --
305 function get_value
306 (
307 p_defined_balance_id in number,
308 p_assignment_action_id in number,
309 p_tax_unit_id in number,
310 p_jurisdiction_code in varchar2,
311 p_source_id in number,
312 p_source_text in varchar2,
313 p_tax_group in varchar2,
314 p_date_earned in date
315 ) return number;
316 --
317 -- Added to support original_entry_id.
318 --
319 function get_value
320 (
321 p_defined_balance_id in number,
322 p_assignment_action_id in number,
323 p_tax_unit_id in number,
324 p_jurisdiction_code in varchar2,
325 p_source_id in number,
326 p_source_text in varchar2,
327 p_tax_group in varchar2,
328 p_original_entry_id in number,
329 p_date_earned in date
330 ) return number;
331 --
332 function get_value
333 (p_defined_balance_id in number
334 ,p_assignment_action_id in number
335 ,p_tax_unit_id in number
336 ,p_jurisdiction_code in varchar2
337 ,p_source_id in number
338 ,p_source_text in varchar2
339 ,p_tax_group in varchar2
340 ,p_date_earned in date
341 ,p_get_rr_route in varchar2
342 ,p_get_rb_route in varchar2
343 ,p_source_text2 in varchar2 default null
344 ,p_source_number in number default null
345 ,p_time_def_id in number default null
346 ,p_balance_date in date default null
347 ,p_payroll_id in number default null
348 ,p_original_entry_id in number default null
349 ,p_local_unit_id in number default null
350 ,p_source_number2 in number default null
351 ,p_organization_id in number default null
352 ) return number;
353 --
354 function get_value
355 ( p_defined_balance_id in number
356 , p_assignment_action_id in number
357 , p_get_rr_route in boolean
358 , p_get_rb_route in boolean
359 ) return number;
360 --
361 ------------------------------- get_value -------------------------------
362 --
363 -- ---- Date mode ----
364 --
365 function get_value
366 (
367 p_defined_balance_id in number,
368 p_assignment_id in number,
369 p_virtual_date in date
370 ) return number;
371 --
372 function get_value
373 (
374 p_defined_balance_id in number,
375 p_assignment_id in number,
376 p_virtual_date in date,
377 p_always_get_db_item in boolean
378 ) return number;
379 --
380 function get_value_lock
381 (
382 p_defined_balance_id in number,
383 p_assignment_id in number,
384 p_virtual_date in date,
385 p_asg_lock in varchar2
386 ) return number;
387 --
388 function get_value_lock
389 (
390 p_defined_balance_id in number,
391 p_assignment_id in number,
392 p_virtual_date in date,
393 p_always_get_db_item in boolean,
394 p_asg_lock in varchar2
395 ) return number;
396 --------------------------------------------------------------------------
397 -- procedure invalidate_run_balances
398 --------------------------------------------------------------------------
399 procedure invalidate_run_balances(p_balance_type_id in number,
400 p_input_value_id in number,
401 p_invalid_date in date);
402 --
403 --------------------------------------------------------------------------
404 -- procedure invalidate_run_balances
405 --------------------------------------------------------------------------
406 procedure invalidate_run_balances(p_balance_type_id in number,
407 p_invalid_date in date);
408 --
409 --------------------------------------------------------------------------
410 -- function find_context
411 --------------------------------------------------------------------------
412 function find_context(p_context_name in varchar2,
413 p_context_id in number) return varchar2;
414 --------------------------------------------------------------------------
415 -- remove_asg_contribs
416 -- Description: Removes assignment contributions to a run balance group balance
417 -- from the run balance group balance, i.e. when an assignment is
418 -- rolled back, the group balance needs to redueced by the
419 -- amount contributed by that assignment.
420 --
421 --------------------------------------------------------------------------
422 procedure remove_asg_contribs
423 (p_payroll_action_id in number
424 ,p_assignment_action_id in number
425 ,p_multi_thread in boolean default false
426 );
427 --------------------------------------------------------------------------
428 -- procedure create_asg_balance
429 --------------------------------------------------------------------------
430 procedure create_asg_balance(p_def_bal_id in number,
431 p_asgact_id in number,
432 p_load_type in varchar2 default 'NORMAL'
433 ,p_bal_att in varchar2 default NULL
434 ,p_eff_date in date default NULL
435 ,p_delta in varchar2 default NULL);
436 --------------------------------------------------------------------------
437 -- procedure create_rr_asg_balances
438 --------------------------------------------------------------------------
439 -- Description: This procedure creates assignment level run balances
440 -- based on the specified run result id.
441 --
442 procedure create_rr_asg_balances
443 (p_run_result_id in number
444 );
445 --------------------------------------------------------------------------
446 -- procedure create_set_asg_balance
447 --------------------------------------------------------------------------
448 procedure create_set_asg_balance(
449 p_defined_balance_lst in out nocopy t_balance_value_tab,
450 p_asgact_id in number,
451 p_load_type in varchar2 default 'NORMAL');
452 --------------------------------------------------------------------------
453 -- procedure create_all_asg_balances
454 --------------------------------------------------------------------------
455 procedure create_all_asg_balances(p_asgact_id in number
456 ,p_bal_list in varchar2 default 'ALL'
460 --------------------------------------------------------------------------
457 ,p_load_type in varchar2 default 'NORMAL'
458 ,p_eff_date in date default null
459 ,p_delta in varchar2 default null);
461 -- procedure create_group_balance
462 --------------------------------------------------------------------------
463 procedure create_group_balance(p_def_bal_id in number
464 ,p_pact_id in number
465 ,p_load_type in varchar2 default 'NORMAL'
466 ,p_bal_att in varchar2 default NULL
467 ,p_eff_date in date default NULL
468 ,p_delta in varchar2 default null);
469 --------------------------------------------------------------------------
470 -- procedure create_set_group_balance
471 --------------------------------------------------------------------------
472 procedure create_set_group_balance(
473 p_defined_balance_lst in out nocopy t_balance_value_tab,
474 p_pact_id in number,
475 p_load_type in varchar2 default 'NORMAL');
476 --------------------------------------------------------------------------
477 -- procedure create_all_group_balances
478 --------------------------------------------------------------------------
479 procedure create_all_group_balances(p_pact_id in number
480 ,p_bal_list in varchar2 default 'ALL'
481 ,p_load_type in varchar2 default 'NORMAL'
482 ,p_eff_date in date default NULL
483 ,p_delta in varchar2 default NULL);
484 --------------------------------------------------------------------------
485 procedure initialise_run_balance(p_defbal_id in number
486 ,p_baldim_id in number
487 ,p_bal_type_id in number
488 ,p_legislation_code in varchar2
492 --------------------------------------------------------------------------
489 ,p_business_group_id in number);
490 --------------------------------------------------------------------------
491 -- procedure set_check_latest_balances
493 procedure set_check_latest_balances;
494 --------------------------------------------------------------------------
495 -- procedure unset_check_latest_balances
496 --------------------------------------------------------------------------
497 procedure unset_check_latest_balances;
498 --------------------------------------------------------------------------
499 procedure maintain_balances_for_action(p_asg_action in number
500 );
501 end pay_balance_pkg;
[Home] [Help]