1 package body pay_balance_upload as
2 /* $Header: pybalupl.pkb 120.8.12010000.2 2008/10/01 06:12:44 ankagarw ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1995 All rights reserved
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pybalupl.pkb
9 DESCRIPTION
10 Uploads initial balances from batch tables.
11 EXTERNAL
12 process
13 count_contexts
14 dim_expiry_date
15 dim_is_supported
16 get_batch_info
17 load_latest_balances
18 lock_batch_header
19 INTERNAL
20 apply_adjustments
21 cache_balances
22 calculate_adjustment
23 get_current_value
24 get_tax_unit_id
25 ins_latest_balance
26 load_latest_asg_balances
27 lock_batch
28 post_transfer_batch
29 purge_batch
30 set_batch_status
31 transfer_assignment
32 transfer_batch
33 undo_transfer_batch
34 valid_latest_balance_run
35 validate_assignment
36 validate_balance
37 validate_batch
38 validate_batch_header
39 validate_batch_lines
40 validate_dimension
41 validate_transfer_batch
42 which_contexts
43 write_message_line
44 MODIFIED (DD-MON-YYYY)
45 115.54 pgongada 08-MAY-2008 Bug # 6997838.
46 Giving exception to BEE action as it
47 doesn't contribute to the balance in
48 the cursor csr_assignment_action under
49 the procedure validate_assignment.
50 115.53 T.Habara 03-OCT-2006 Removed the dynamic sql for batch_type.
51 115.52 T.Habara 21-SEP-2006 Bug 5556876. Added g_batch_info and
52 get_batch_info(). Modified process() and
53 validate_batch() to set the global info.
54 Use of dynamic sql for batch_type.
55 115.51 T.Habara 31-MAY-2006 Exception handling in dim_expiry_date.
56 115.50 T.Habara 28-FEB-2006 Bug 5057885. Corrected index hints for
57 csr_assignment in transfer_batch and
58 csr_assignment in load_latest_balances.
59 115.49 T.Habara 22-DEC-2005 Nowait in locking batch header.
60 115.48 T.Habara 20-DEC-2005 Bug 4893251. Added external function
61 dim_is_supported.
62 115.47 T.Habara 09-DEC-2005 Bug 4872523. Modified the payroll action
63 creation to support different payrolls.
64 115.46 T.Habara 15-NOV-2005 Batch header table is shared for batch
65 adjustment usage. Added a validation for
66 batch_type in validate_batch_header.
67 115.45 T.Habara 16-FEB-2005 Modified validate_assignment().
68 Modified write_message_line() to
69 handle a token value. Bug 4179912.
70 115.44 T.Habara 02-SEP-2004 Modified apply_adjustments(). The check
71 for shared adjustments did not handle
72 the case of orig entry id being null.
73 115.43 T.Habara 11-MAY-2004 Modified transfer_assignment to set
74 balance conflict errors to correct
75 batch lines. (Bug 3604595)
76 115.42 T.Habara 20-APR-2004 Bug 3513653. Support for source_number
77 and source_text2.
78 Use of execute immediate statements for
79 the dynamic sqls.
80 Added generic current value calculation
81 with BAL_INIT_INCLUDE_ADJ leg rule.
82 115.41 T.Habara 05-APR-2004 Nocopy changes.
83 Removed create_adjustment that is
84 no longer used.
85 115.40 T.Habara 30-MAR-2004 Bug 3354765. Batch validation and
86 transfer processes have been moved in
87 validate_transfer_batch to handle
88 unexpected error as a batch level error.
89 Added post_transfer_batch.
90 Corrected to pass a proper batch mode
91 to init_batch.
92 Corrected validate_balance to use
93 element link validity cache.
94 115.39 A.Logue 22-DEC-2003 Use of unsecured base tables for performance.
95 115.38 T.Battoo 3-NOV-2003 support for sparse matrix and
96 pay_latest_balances table
97 115.37 T.Habara 18-SEP-2003 Modified the BF include_adjustment call
98 to pass Source ID and Source Text values.
99 115.36 N.Bristow 13-MAR-2003 Source ID and Source Text where being corrupted
100 on run results.
101 115.35 A.Logue 03-MAR-2003 Bug 2700291 - Performance fix introduce
102 use of csr_get_err_lines into purge_batch.
103 115.34 N.Bristow 17-FEB-2003 Added Dynamic option for TAX_UNIT.
104 115.33 A.Logue 20-JAN-2003 Remove times from dates.
105 115.32 N.Bristow 14-JAN-2003 Jurisdiction Input Value can now be
106 named by the legislation
107 115.31 A.Logue 23-DEC-2002 Bug 2628014 - Enhanced load_latest_asg_balances
108 to handle exceptions raised. Affected lines
109 are now set to Errored, and a message is
110 put into pay_message_lines. This was done
111 by passing batch_line_list and num_lines
112 to the procedure. This avoids the scenario
113 where some lines were being set as Valid, and
114 others as Transferred - and the batch has been
115 marked as Transferred.
116 115.30 A.Logue 17-DEC-2002 Bug 2700291 - Performance fixes in
117 cursor csr_assignment in transfer_batch
118 and cursor csr_assignment in
119 load_latest_balances.
120 115.29 T.Habara 21-NOV-2002 Bug 2676349 - Implementation of batch
121 adjustment.
122 Added the following data structures.
123 - g_payroll_action_rec_type
124 - g_payroll_action_tab_type
125 - g_payroll_actions
126 Modified apply_adjustments()
127 - added pay_bal_adjust.init_batch call
128 and logic to handle payroll actions
129 cache.
130 - create_adjustment call was replaced
131 with pay_bal_adjust.adjust_balance.
132 Added the logic to handle payroll
133 actions cache to transfer_assignment()
134 and process().
135 Modified transfer_batch() to update
136 multiple batch lines for a payroll
137 action.
138 Modified the select statement in
139 load_latest_asg_balances() to use
140 paa.action_sequence instead of ppa.
141 115.28 J.Hobbs 11-OCT-2002 Added logic to be able to process International
142 Payroll supported dimensions. Made changes to
143 dim_expiry_date()
144 validate_dimension()
145 get_current_value()
146 validate_batch()
147 115.27 N.Bristow 09-APR-2002 Changed code to allow balances to be loaded
148 historically.
149 115.26 M.Reid 04-FEB-2002 2211591: Corrected source_text parameter
150 115.25 D.Saxby 18-DEC-2001 GSCC standards fix.
151 115.24 D.Saxby 17-DEC-2001 Bug 2153245 - changes for Purge.
152 o New global data structure member:
153 purge_mode.
154 o Changed lock_batch to init purge_mode
155 as appropriate and reset the
156 BALANCE_ROLLUP to TRANSFER mode.
157 o Changed transfer_batch and
158 undo_transfer_batch to perform
159 commit based on new purge_mode.
160 o Don't error if upload detects an
161 assignment has been previously
162 processed and we are purging.
163 o Call bal_adjust_actions in purge
164 mode when appropriate.
165 o Added dbdrv line.
166 o Added commit at end of file.
167 115.23 A.Logue 25-JUN-2001 Performance changes to dim_expiry_date.
168 115.22 A.Logue 22-JUN-2001 Performance changes to
169 load_latest_asg_balances
170 including hints.
171 115.21 SuSivasu 20-JUN-2001 Re-arranged the parameter call
172 to the which_context function.
173 115.20 A.Logue 09-MAY-2001 Added some CBO hints. Bug 1763446.
174 115.19 SuSivasu 06-APR-2001 Added two SOURCE_ID and SOURCE_TEXT
175 contexts to the batch balance upload
176 tables.
177 115.18 JARTHURT 04-JAN-2001 Removed hard-coded calls to
178 pay_ca_bal_upload. These calls are now
179 performed dynamically using the new
180 functionality added in 115.17
181 115.17 N.Bristow 29-SEP-2000 Changes for Singapore, now passing
182 tax unit id to balance adjustments,
183 also passing batch_line_id to
184 include_adjustment.
185 115.16 A.Logue 13-JAN-2000 Ensure that error messages fetched from
186 hr_utility.get_message are of max length 240
187 to fit into pay_message_lines.
188 115.14 A.Logue 07-OCT-1999 Pass batch_line_status to dim_expiry_date so that
189 it does not call the legislative expiry_date
190 procedure if the line is to be discarded (ie not 'V').
191 This should give an improvement of performance.
192 115.13 A.Logue 06-OCT-1999 Put call to dim_expiry_date back into
193 csr_batch_line_transfer. Can do this as the
194 procedure does not have to be pragmatised in 11i
195 and hence can be called from the cursor (nb it
196 contains dynamic sql). Thus can remove the insert
197 sort implemented as part of 730491. This
198 should give an improvement of performance.
199 115.12 A.Logue 18-MAY-1999 Change dbms_output to hr_utility.trace.
200 115.11 A.Logue 14-MAY-1999 Order by line_id on line fetch.
201 115.9 T.Battoo 20-APR-1999 setting the previous value for
202 latest balances - this code had been
203 deleted for some reason.
204 115.8 A.Logue 15-APR-1999 Fix to support of canonical numbers.
205 110.10 A.Logue 30-NOV-1998 Bug 713456. Fix to legislation code
206 check in csr_initial_balance_feed
207 cursors.
208 110.8 A.Logue 24-NOV-1998 Bug 768805. Fix to include_adjustment dynamic
209 sql bind variables for new legislations.
210 110.7 A.Logue 23-NOV-1998 Bug 768805. Fix to is_supported dynamic sql
211 bind variables for new legislations.
212 110.6 A.Logue 17-NOV-1998 Bug 713456. Business group and legislation
213 code check on potential balance feeds in
214 csr_initial_balance_feed.
215 110.5 A.Logue 30-OCT-1998 Bug 730491. Changes to use dynamic sql to avoid
216 explicit legislative package references for
217 any new legislations. This has resulted in
218 a slighlty amended interface for any new
219 legislations where include_adjustment is now
220 passed the batch_line_id and returns a number,
221 and is_supported which now returns a number.
222 110.4 A.Logue 24-MAR-1998 Bug 485629. Fix for balance initialization
223 elements, check for
224 balance_initialization_flag AND
225 stops a thread attempting to process
226 a batch if it is already being
227 processed by another thread. Done by
228 batch header batch_status getting
229 L-ocked during processing.
230 110.3 N.Bristow 03-MAR-1998 Bug 630068. GRE name was not being
231 checked correctly.
232 110.2 N.Bristow 16-OCT-1997 Now setting the previous value for
233 latest balances.
234 40.26 A.Logue 02-JUL-1997 Bug 485629. Support for JP, CH and CA
235 legislations ie calls to legislative
236 routines.
237 40.25 A.Logue 26-JUN-1997 Bug 418064. Further fix for jurisdiction
238 clashes.
239 40.24 A.Logue 24-JUN-1997 Bug 418064. Now checks if invalid
240 combination of balance adjustments.
241 40.23 N.Bristow 18-FEB-1997 When validating the tax unit the name
242 as well as the id are now checked.
243 40.22 N.Bristow 04-FEB-1997 Now commits in chunks when performing
244 in undo mode.
245 40.21 N.Bristow 12-JUN-1996 Bug 373446. No longer performing
246 a full table scan when undoing
247 a batch.
248 40.20 N.Bristow 08-MAY-1996 Bug 359005. Now caching Tax Unit Id
249 when validating. Reinstated the
250 tax unit id column on
251 pay_balance_batch_lines. Tuned several
252 statements.
253 40.19 N.Bristow 18-MAR-1996 Now padding expired latest balance
257 a no_data_found error was produced
254 columns with -9999.
255 40.18 N.Bristow 18-MAR-1996 Bug 349583. Order by clause on
256 csr_bal_adj was wrong, as a result
258 later in the code.
259 40.17 N.Bristow 08-MAR-1996 Bug 346991. Upload not erroring
260 correctly when no defined balance id
261 is found for the balance to be loaded.
262 40.16 N.Bristow 14-DEC-1995 Error HR_7030_ELE_ENTRY_INV_ADJ was
263 not being raised correctly.
264 40.15 N.Bristow 27-Nov-1995 Now loads the latest balances when the
265 balance value is zero.
266 40.14 N.Bristow 22-Nov-1995 Added the loading of latest balances.
267 Latest balances are now loaded in the
268 transfer mode.
269 40.13 N.Bristow 11-Nov-1995 Now calling bal_adjust_actions to
270 perform the balance adjustment.
271 40.12 N.Bristow 02-Nov-1995 Statements that reference the
272 hr_tax_units_v view run very slow.
273 Changed to access base tables.
274 40.11 N.Bristow 23-Oct-1995 Now csr_batch_line_transfer ordering in
275 decending date order. Also reversed the
276 10.7 changes with regard to the BF
277 legislative functions.
278 40.10 N.Bristow 17-Oct-1995 Now using error tokens in fnd_messages.
279 Also changed the order by on
280 csr_batch_line_transfer.
281 40.8 N.Bristow 20-Sep_1995 Error status is now set when
282 an error is encountered.
283 40.7 M.Callaghan 11-Sep-1995 "whenever sqlerror" added.
284 Temp change: references to the package
285 pay_bf_bal_upload commented out for
286 prod 5 freeze.
287 40.6 N.Bristow 25-Aug-1995 Now picks up the correct
288 classifications.
289 40.5 N.Bristow 13-Jul-1995 Checking against wrong legislation
290 code.
291 40.4 N.Bristow 13-Jul-1995 Closing cursors on error.
292 40.3 N.Bristow 07-Jul-1995 Now uses the new rollback function.
293 40.2 N.Bristow 06-Jul-1995 General bugs discovered when testing.
294 40.1 J.S.Hobbs 16-May-1995 created.
295 */
296 --
297 -- Array data types.
298 --
299 type number_array is table of number index by binary_integer;
300 type varchar2_array is table of varchar2(80) index by binary_integer;
301 type boolean_array is table of boolean index by binary_integer;
302 --
303 -- Global data structure.
304 --
305 type glbl_data_rec_type is record
306 (upload_mode varchar2(30)
307 ,purge_mode boolean
308 ,upload_date pay_balance_batch_headers.upload_date%type
309 ,batch_id pay_balance_batch_headers.batch_id%type
310 ,business_group_id pay_balance_batch_headers.business_group_id%type
311 ,legislation_code varchar2(30)
312 ,payroll_id pay_balance_batch_headers.payroll_id%type
313 ,consolidation_set_id pay_consolidation_sets.consolidation_set_id%type
314 ,assignment_id pay_balance_batch_lines.assignment_id%type
315 ,batch_header_status pay_balance_batch_headers.batch_status%type
316 ,batch_line_status pay_balance_batch_lines.batch_line_status%type
317 ,chunk_size number(9)
318 ,jurisdiction_iv pay_input_values_f.name%type
319 ,include_adj_rule pay_legislation_rules.rule_mode%type
320 );
321
322 type g_payroll_action_rec_type is record
323 (payroll_action_id pay_payroll_actions.payroll_action_id%type
324 ,effective_date pay_payroll_actions.effective_date%type
325 ,payroll_id number
326 );
327
328 type g_payroll_action_tab_type is table of g_payroll_action_rec_type
329 index by binary_integer;
330
331 type t_pointer_rec is record
332 (
333 start_ptr number,
334 end_ptr number
335 );
336 type t_pointer_tab is table of t_pointer_rec
337 index by binary_integer;
338
339 type t_inpval_context_rec is record
340 (
341 context_name ff_contexts.context_name%type,
342 input_value_id pay_input_values_f.input_value_id%type
343 );
344 type t_inpval_context_tab is table of t_inpval_context_rec
345 index by binary_integer;
346
347 type t_balance_validation_rec is record
348 (balance_type_id number
349 ,balance_name pay_balance_types.balance_name%type
350 ,element_type_id number
351 ,element_link_id number
352 ,ibf_input_value_id number
353 ,jc_input_value_id number
354 ,jurisdiction_level number
355 ,bal_invld boolean -- does the balance exist ?
356 ,bal_invl_feed boolean -- does it have an intial balance feed ?
360 type t_balance_validation_tab is table of t_balance_validation_rec
357 ,bal_invl_link boolean -- does it have an element link ?
358 );
359
361 index by binary_integer;
362
363
364 type t_dimension_validation_rec is record
365 (balance_dimension_id pay_balance_dimensions.balance_dimension_id%type
366 ,dimension_name pay_balance_dimensions.dimension_name%type
367 ,invld boolean -- does the dimension exist ?
368 ,not_supp boolean -- is it supported ?
369 ,jc_cntxt boolean -- does it use JURISDICTION_CODE ?
370 ,gre_cntxt boolean -- does it use TAX_UNIT_ID ?
371 ,oee_cntxt boolean -- does it use ORIGINAL_ENTRY_ID ?
372 ,srcid_cntxt boolean -- does it use SOURCE_ID ?
373 ,srctxt_cntxt boolean -- does it use SOURCE_TEXT ?
374 ,runtyp_cntxt boolean -- does it use Run Type ?
375 ,sn_cntxt boolean -- does it use SOURCE_NUMBER ?
376 ,st2_cntxt boolean -- does it use SOURCE_TEST2 ?
377 ,other_cntxt boolean -- are any other contexts used ?
378 );
379
380 type t_dimension_validation_tab is table of t_dimension_validation_rec
381 index by binary_integer;
382
383
384 type t_balance_rec is record
385 (element_link_id number
386 ,ibf_input_value_id number
387 ,jc_input_value_id number
388 ,jurisdiction_level number
389 );
390
391 type t_balance_tab is table of t_balance_rec
392 index by binary_integer;
393
394 --
395 -- global cache to store the list of payroll actions for a batch
396 --
397 g_payroll_actions g_payroll_action_tab_type;
398
399 --
400 -- current batch info for the reference from the outside code
401 -- during the batch processing.
402 --
403 g_batch_info t_batch_info_rec;
404
405 --
406 -- Retrieves all the non transferred batch lines for a batch NB. it is
407 -- possible that a status has not been set for each line so the nvl ensures
408 -- a valid comparison. This is used to retrieve the batch lines during the
409 -- VALIDATION process.
410 --
411 cursor csr_batch_line_validate
412 (
413 p_batch_id number
414 ) is
415 select *
416 from pay_balance_batch_lines BL
417 where BL.batch_id = p_batch_id
418 and nvl(BL.batch_line_status, 'U') <> 'T'
419 order by BL.assignment_id,
420 BL.assignment_number
421 for update;
422 --
423 -- Retrieves the batch lines for an assignment within a batch NB. this
424 -- combines the batch header and line information, provides the date on which
425 -- the dimension expires and also how many contexts each uses. The batch lines
426 -- are ordered by status, then by balance, then by the expiry date of the
427 -- dimension, and then finally by the number of contexts the dimension uses.
428 -- This is used to retrieve the batch lines during the TRANSFER process.
429 --
430 cursor csr_batch_line_transfer
431 (
432 p_batch_id number
433 ,p_assignment_id number
434 ) is
435 select BL.batch_id
436 ,BL.batch_line_id
437 ,BL.batch_line_status
438 ,BL.assignment_id
439 ,BL.balance_type_id
440 ,BL.balance_dimension_id
441 ,BL.dimension_name
442 ,BL.balance_name
443 ,BL.assignment_number
444 ,BL.gre_name
445 ,BL.tax_unit_id
446 ,BL.jurisdiction_code
447 ,BL.original_entry_id
448 ,BL.source_id
449 ,BL.source_text
450 ,BL.source_number
451 ,BL.source_text2
452 ,BL.run_type_id
453 ,BL.value
454 ,trunc(nvl(BL.upload_date, BH.upload_date)) upload_date
455 ,pay_balance_upload.count_contexts
456 (BL.balance_dimension_id, BL.dimension_name) no_of_contexts
457 ,pay_balance_upload.dim_expiry_date
458 (BH.business_group_id
459 ,trunc(nvl(BL.upload_date, BH.upload_date))
460 ,BL.dimension_name
461 ,BL.assignment_id
462 ,BL.tax_unit_id
463 ,BL.jurisdiction_code
464 ,BL.original_entry_id
465 ,BL.batch_line_status) expiry_date
466 from pay_balance_batch_headers BH
467 ,pay_balance_batch_lines BL
468 where BH.batch_id = p_batch_id
469 and BL.batch_id = BH.batch_id
470 and BL.assignment_id = p_assignment_id
471 order by BL.assignment_id
472 ,decode(BL.batch_line_status,'T',1 ,'E',2 ,'V',3)
473 ,BL.balance_type_id
474 ,trunc(nvl(BL.upload_date, BH.upload_date))
475 ,pay_balance_upload.dim_expiry_date
476 (BH.business_group_id
477 ,trunc(nvl(BL.upload_date,BH.upload_date))
478 ,BL.dimension_name
479 ,BL.assignment_id
480 ,BL.tax_unit_id
481 ,BL.jurisdiction_code
482 ,BL.original_entry_id
483 ,BL.batch_line_status) desc
484 ,pay_balance_upload.count_contexts(
485 BL.balance_dimension_id, BL.dimension_name) desc
486 ,BL.batch_line_id;
487
488 --
492 --
489 -- Retrieves all the transferred batch lines for a batch line. The batch
490 -- lines are ordered by the payroll action. This is used to retrieve the batch
491 -- lines during the UNDO TRANSFER process.
493 cursor csr_batch_line_undo_transfer
494 (
495 p_batch_id number
496 ) is
497 select *
498 from pay_balance_batch_lines BL
499 where BL.batch_id = p_batch_id
500 and BL.batch_line_status = 'T'
501 order by BL.payroll_action_id;
502 --
503 -- SRS Constant Statuses
504 --
505 SRS_SUCCESS constant number := 0;
506 SRS_ERROR constant number := 2;
507 -- Constants identifying the level at which to report messages.
508 --
509 HEADER constant number := 1;
510 LINE constant number := 2;
511 --
512 -- Constant holding the default chunk size.
513 --
514 CHUNK_SIZE constant number := 10;
515 --
516 -- Constants holding the start and end of time.
517 --
518 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
519 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
520 --
521 -- Global Status Indicator
522 --
523 status_indicator number;
524 --
525 -- Global Cache.
526 --
527 g_gre_tbl_name varchar2_array;
528 g_gre_tbl_id number_array;
529 g_gre_tbl_nxt number;
530 g_runtyp_tbl_name varchar2_array;
531 g_runtyp_tbl_id number_array;
532 g_runtyp_tbl_nxt number;
533 g_legislation_code varchar2(30);
534 g_legislation_contexts pay_core_utils.t_contexts_tab;
535 g_element_link_contexts t_pointer_tab;
536 g_input_val_contexts t_inpval_context_tab;
537 --
538 g_bal_vald t_balance_validation_tab; -- balance validation cache.
539 g_dim_vald t_dimension_validation_tab; -- dimension validation cache.
540 -- balance cache indexed by balance type id.
541 g_balances t_balance_tab;
542 --
543 -----------------------------------------------------------------------------
544 -- NAME
545 -- remove_messages
546 -- PURPOSE
547 -- This removes the error messages created for a batch. This is used when
548 -- the batch is rerun or reversed.
549 -- ARGUMENTS
550 -- USES
551 -- NOTES
552 -----------------------------------------------------------------------------
553 procedure remove_messages(p_batch_id in number)
554 is
555 cursor pml (p_batch_id number) is
556 select batch_line_id
557 from pay_balance_batch_lines
558 where batch_id = p_batch_id;
559 --
560 begin
561 hr_utility.trace('Entering pay_balance_upload.remove_messages');
562 --
563 -- Remove the messages
564 --
565 for pmlrec in pml(p_batch_id) loop
566 delete from pay_message_lines
567 where source_type = 'L'
568 and source_id = pmlrec.batch_line_id;
569 end loop;
570 --
571 hr_utility.set_location('pay_balance_upload.remove_messages',10);
572 delete from pay_message_lines
573 where source_type = 'H'
574 and source_id = p_batch_id;
575 --
576 hr_utility.trace('Exiting pay_balance_upload.remove_messages');
577 end remove_messages;
578 --
579 -----------------------------------------------------------------------------
580 -- NAME
581 -- get_run_type_id
582 -- PURPOSE
583 -- This gets the run type name/id given the run type id/name. The run type
584 -- details are then stored in a cache.
585 -- ARGUMENTS
586 -- USES
587 -- NOTES
588 -- A global cache is used to store the run type details.
589 -----------------------------------------------------------------------------
590 procedure get_run_type_id (p_business_group in number,
591 p_run_type_name in out nocopy varchar2,
592 p_run_type_id in out nocopy number,
593 p_effective_date in date) is
594 cursor csr_run_type
595 (
596 p_business_group_id number
597 ,p_run_type_name varchar2
598 ,p_run_type_id number
599 ,p_effective_date date
600 ) is
601 select upper(prt.run_type_name) run_type_name,
602 prt.run_type_id
603 from pay_run_types_f prt,
604 per_business_groups_perf pbg
605 where pbg.business_group_id = p_business_group_id
606 and p_effective_date between prt.effective_start_date
607 and prt.effective_end_date
608 and (pbg.business_group_id = prt.business_group_id
609 or pbg.legislation_code = prt.legislation_code
610 or (prt.business_group_id is null
611 and prt.legislation_code is null))
612 and p_run_type_id = prt.run_type_id
613 and p_run_type_id is not null
614 union all
615 select upper(prt.run_type_name) run_type_name,
616 prt.run_type_id
617 from pay_run_types_f prt,
618 per_business_groups_perf pbg
619 where pbg.business_group_id = p_business_group_id
620 and p_effective_date between prt.effective_start_date
621 and prt.effective_end_date
622 and (pbg.business_group_id = prt.business_group_id
623 or pbg.legislation_code = prt.legislation_code
624 or (prt.business_group_id is null
628 --
625 and prt.legislation_code is null))
626 and upper(prt.run_type_name) = upper(p_run_type_name)
627 and p_run_type_id is null;
629 l_run_type_rec csr_run_type%rowtype;
630 l_run_type_name varchar2(80);
631 l_count NUMBER;
632 l_found BOOLEAN;
633 begin
634 hr_utility.trace('Entering pay_balance_upload.get_run_type_id');
635 --
636 -- Search for the defined balance in the Cache.
637 --
638 l_found := FALSE;
639 if (p_run_type_id is not null) then
640 hr_utility.set_location('pay_balance_upload.get_run_type_id',10);
641 l_count := 1;
642 while (l_count < g_runtyp_tbl_nxt and l_found = FALSE) loop
643 if (p_run_type_id = g_runtyp_tbl_id(l_count)) then
644 p_run_type_id := g_runtyp_tbl_id(l_count);
645 p_run_type_name := g_runtyp_tbl_name(l_count);
646 l_found := TRUE;
647 end if;
648 l_count := l_count + 1;
649 end loop;
650 else
651 if (p_run_type_name is not null) then
652 hr_utility.set_location('pay_balance_upload.get_run_type_id',20);
653 l_run_type_name := upper(p_run_type_name);
654 l_count := 1;
655 while (l_count < g_runtyp_tbl_nxt and l_found = FALSE) loop
656 if (l_run_type_name = g_runtyp_tbl_name(l_count)) then
657 p_run_type_id := g_runtyp_tbl_id(l_count);
658 p_run_type_name := g_runtyp_tbl_name(l_count);
659 l_found := TRUE;
660 end if;
661 l_count := l_count + 1;
662 end loop;
663 end if;
664 end if;
665 hr_utility.set_location('pay_balance_upload.get_run_type_id',30);
666 --
667 -- If the balance is not in the Cache get it from the database.
668 --
669 if (l_found = FALSE) then
670 hr_utility.set_location('pay_balance_upload.get_run_type_id',40);
671 --
672 open csr_run_type(p_business_group,
673 p_run_type_name,
674 p_run_type_id,
675 p_effective_date);
676 fetch csr_run_type into l_run_type_rec;
677 --
678 -- The GRE doesn't exist so raise error.
679 --
680 if csr_run_type%notfound then
681 close csr_run_type;
682 raise no_data_found;
683 end if;
684 --
685 p_run_type_name := l_run_type_rec.run_type_name;
686 p_run_type_id := l_run_type_rec.run_type_id;
687 close csr_run_type;
688 --
689 -- Place the defined balance in cache.
690 --
691 g_runtyp_tbl_name(g_runtyp_tbl_nxt) := l_run_type_rec.run_type_name;
692 g_runtyp_tbl_id(g_runtyp_tbl_nxt) := l_run_type_rec.run_type_id;
693 g_runtyp_tbl_nxt := g_runtyp_tbl_nxt + 1;
694 end if;
695 --
696 hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
697 exception
698 when no_data_found then
702 raise;
699 hr_utility.set_location('pay_balance_upload.get_run_type_id',50);
700 -- close csr_tax_unit;
701 hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
703 end;
704 --
705 -----------------------------------------------------------------------------
706 -- NAME
707 -- get_tax_unit_id
708 -- PURPOSE
709 -- This gets the tax unit name/id given the tax unit id/name. The tax unit
710 -- details are then stored in a cache.
711 -- ARGUMENTS
712 -- USES
713 -- NOTES
714 -- A global cache is used to store the tax unit details.
715 -----------------------------------------------------------------------------
716 procedure get_tax_unit_id (p_business_group in number,
717 p_gre_name in out nocopy varchar2,
718 p_tax_unit_id in out nocopy number) is
719 cursor csr_tax_unit
720 (
721 p_business_group_id number
722 ,p_gre_name varchar2
723 ,p_tax_unit_id number
724 ) is
725 select upper(name) tax_unit_name,
726 tax_unit_id
727 from hr_tax_units_v
728 where business_group_id = p_business_group_id
729 and p_tax_unit_id = tax_unit_id
730 and p_tax_unit_id is not null
731 union all
732 select upper(name) tax_unit_name,
733 tax_unit_id
734 from hr_tax_units_v
735 where business_group_id = p_business_group_id
736 and upper(name) = upper(p_gre_name)
737 and p_tax_unit_id is null;
738 --
739 cursor csr_establishment_unit
740 (
741 p_business_group_id number
742 ,p_name varchar2
743 ,p_establishment_id number
744 ) is
745 select upper(name) tax_unit_name,
746 ORGANIZATION_ID tax_unit_id
747 from hr_fr_establishments_v
748 where business_group_id = p_business_group_id
749 and p_establishment_id = ORGANIZATION_ID
750 and p_establishment_id is not null
751 union all
752 select upper(name) tax_unit_name,
753 ORGANIZATION_ID tax_unit_id
754 from hr_fr_establishments_v
755 where business_group_id = p_business_group_id
756 and upper(name) = upper(p_name)
757 and p_establishment_id is null;
758 --
759 g_leg_rule pay_legislation_rules.rule_mode%type;
760 --
761 l_gre_rec csr_tax_unit%rowtype;
762 l_gre_name varchar2(80);
763 l_count NUMBER;
764 l_found BOOLEAN;
765 begin
766 hr_utility.trace('Entering pay_balance_upload.get_tax_unit_id');
767 --
768 -- Search for the defined balance in the Cache.
769 --
770 l_found := FALSE;
771 if (p_tax_unit_id is not null) then
772 hr_utility.set_location('pay_balance_upload.get_tax_unit_id',10);
773 l_count := 1;
774 while (l_count < g_gre_tbl_nxt and l_found = FALSE) loop
775 if (p_tax_unit_id = g_gre_tbl_id(l_count)) then
776 p_tax_unit_id := g_gre_tbl_id(l_count);
777 p_gre_name := g_gre_tbl_name(l_count);
778 l_found := TRUE;
779 end if;
780 l_count := l_count + 1;
781 end loop;
782 else
783 if (p_gre_name is not null) then
784 hr_utility.set_location('pay_balance_upload.get_tax_unit_id',20);
785 l_gre_name := upper(p_gre_name);
786 l_count := 1;
787 while (l_count < g_gre_tbl_nxt and l_found = FALSE) loop
788 if (l_gre_name = g_gre_tbl_name(l_count)) then
789 p_tax_unit_id := g_gre_tbl_id(l_count);
790 p_gre_name := g_gre_tbl_name(l_count);
791 l_found := TRUE;
792 end if;
793 l_count := l_count + 1;
794 end loop;
795 end if;
796 end if;
797 hr_utility.set_location('pay_balance_upload.get_tax_unit_id',30);
798 --
799 -- If the balance is not in the Cache get it from the database.
800 --
801 if (l_found = FALSE) then
802 hr_utility.set_location('pay_balance_upload.get_tax_unit_id',40);
803 begin
804 --
805 select plr.rule_mode
806 into g_leg_rule
807 from pay_legislation_rules plr,
808 per_business_groups_perf pbg
809 where pbg.business_group_id = p_business_group
810 and plr.legislation_code = pbg.legislation_code
811 and plr.rule_type = 'TAX_UNIT';
812 --
813 --
814 exception
815 when no_data_found then
816 g_leg_rule := 'N';
817 end;
818 --
819 if (g_leg_rule in ('Y', 'D')) then
820 open csr_tax_unit(p_business_group,
821 p_gre_name,
822 p_tax_unit_id);
823 fetch csr_tax_unit into l_gre_rec;
824 --
825 -- The GRE doesn't exist so raise error.
826 --
827 if csr_tax_unit%notfound then
828 close csr_tax_unit;
829 raise no_data_found;
830 end if;
831 --
832 p_gre_name := l_gre_rec.tax_unit_name;
833 p_tax_unit_id := l_gre_rec.tax_unit_id;
834 close csr_tax_unit;
835 elsif (g_leg_rule = 'E') then
839 fetch csr_establishment_unit into l_gre_rec;
836 open csr_establishment_unit(p_business_group,
837 p_gre_name,
838 p_tax_unit_id);
840 --
841 -- The Establishment doesn't exist so raise error.
842 --
843 if csr_establishment_unit%notfound then
844 close csr_establishment_unit;
845 raise no_data_found;
846 end if;
847 --
848 p_gre_name := l_gre_rec.tax_unit_name;
849 p_tax_unit_id := l_gre_rec.tax_unit_id;
850 close csr_establishment_unit;
851 end if;
852 --
853 -- Place the defined balance in cache.
854 --
855 g_gre_tbl_name(g_gre_tbl_nxt) := l_gre_rec.tax_unit_name;
856 g_gre_tbl_id(g_gre_tbl_nxt) := l_gre_rec.tax_unit_id;
857 g_gre_tbl_nxt := g_gre_tbl_nxt + 1;
858 end if;
859 --
860 hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
861 exception
862 when no_data_found then
863 hr_utility.set_location('pay_balance_upload.get_tax_unit_id',50);
864 -- close csr_tax_unit;
865 hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
866 raise;
867 end;
868 -----------------------------------------------------------------------------
869 -- NAME
870 -- write_message_line
871 -- PURPOSE
872 -- Writes a message to the message lines table.
873 -- ARGUMENTS
874 -- p_meesage_level - either HEADER or LINE constants.
875 -- p_batch_id - the batch to report the error against (optional)
876 -- p_batch_line_id - the batch line to report the error against (optional)
877 -- p_meesage_text - the text explaining the error.
878 -- p_message_token - the token for the text explaining the error.
879 -- USES
880 -- NOTES
881 -----------------------------------------------------------------------------
882 --
883 procedure write_message_line
884 (
885 p_meesage_level number
886 ,p_batch_id number
887 ,p_batch_line_id number
888 ,p_message_text varchar2
889 ,p_message_token varchar2
890 ,p_token_name varchar2 default null
891 ,p_token_value varchar2 default null
892 ) is
893 --
894 -- The message text to be reported.
895 --
896 l_message_text varchar2(500);
897 --
898 begin
899 --
900 hr_utility.trace('Entering pay_balance_upload.write_message_line');
901 --
902 -- Set global error indicator.
903 --
904 status_indicator := SRS_ERROR;
905 --
906 -- The message text has been passed.
907 --
908 if p_message_text is not null then
909 l_message_text := p_message_text;
910 --
911 -- The message token for the message text has been passed so extract the
912 -- message text.
913 --
914 else
915 hr_utility.set_message(801, p_message_token);
916 if p_token_name is not null then
917 --
918 -- Set the token value if specified.
919 --
920 hr_utility.set_message_token
921 (p_token_name, p_token_value);
922 end if;
923 l_message_text := substrb(hr_utility.get_message, 1, 500);
924 end if;
925 --
926 -- Create new message line.
927 --
928 hr_utility.trace(l_message_text);
929 --
930 insert into pay_message_lines
931 (line_sequence
932 ,message_level
933 ,source_id
934 ,source_type
935 ,line_text)
936 values
937 (pay_message_lines_s.nextval
938 ,'F' -- 'F'atal
939 ,decode(p_meesage_level, HEADER, p_batch_id, LINE, p_batch_line_id)
940 ,decode(p_meesage_level, HEADER, 'H' , LINE, 'L')
941 ,substr(l_message_text, 1, 240));
942 --
943 hr_utility.trace('Exiting pay_balance_upload.write_message_line');
944 --
945 end write_message_line;
946 --
947 -----------------------------------------------------------------------------
948 -- NAME
949 -- dim_expiry_date
950 -- PURPOSE
951 -- Returns the boundary date of a dimension relative to a date ie. the date
952 -- returned for a QTD dimension would be the start date of the quarter in
953 -- which the date existed. For some dimensions the contexts can affect the
954 -- date returned ie. if the dimension is GRE within QTD then the date must
955 -- be set such that the assignment belongs to the particular GRE within the
956 -- quarter.
957 -- ARGUMENTS
958 -- USES
959 -- NOTES
960 -- This is used by the csr_batch_line_transfer cursor.
961 -----------------------------------------------------------------------------
962 --
963 function dim_expiry_date
964 (
965 p_business_group_id number
966 ,p_upload_date date
967 ,p_dimension_name varchar2
968 ,p_assignment_id number
969 ,p_tax_unit_id number
970 ,p_jurisdiction_code varchar2
971 ,p_original_entry_id number
972 ,p_batch_line_status varchar2 default 'V'
973 ) return date is
974 --
975 -- Retrieves the legislation ocde for the business group.
976 --
977 cursor csr_legislation_code
978 (
979 p_business_group_id number
980 ) is
984 --
981 select BG.legislation_code
982 from per_business_groups_perf BG
983 where BG.business_group_id = p_business_group_id;
985 -- Holds the expiry date of the dimension.
986 --
987 l_expiry_date date;
988 --
989 -- Holds the legislation code for the business group.
990 --
991 l_legislation_code varchar2(30);
992 --
993 -- Dynamic sql variables
994 --
995 sql_curs number;
996 rows_processed integer;
997 statem varchar2(512);
998 --
999 begin
1000 --
1001 hr_utility.trace('Entering pay_balance_upload.dim_expiry_date');
1002 --
1003 if g_legislation_code is null then
1004 --
1005 -- Get the legislation code for the business group.
1006 --
1007 open csr_legislation_code(p_business_group_id);
1008 fetch csr_legislation_code into g_legislation_code;
1009 close csr_legislation_code;
1010 end if;
1011 --
1012 l_legislation_code := g_legislation_code;
1013 --
1014 if (p_batch_line_status <> 'V') then
1015 --
1016 -- line is not valid and hence no point working
1017 -- out expiry date as it is not used anyway
1018 --
1019 hr_utility.trace('pay_balance_upload.dim_expiry_date invalid line');
1020 --
1021 l_expiry_date := p_upload_date;
1022 --
1023 --
1024 -- If the dimension uses one of the International Payroll supported routes
1025 -- then process it NB. this could be for any legislation. All other dimensions
1026 -- for a given legislation will be processed in the relevant legislation specific
1027 -- package.
1028 --
1029 elsif pay_ip_bal_upload.international_payroll(p_dimension_name ,l_legislation_code) then
1030 l_expiry_date := pay_ip_bal_upload.expiry_date
1031 (p_upload_date
1032 ,p_dimension_name
1033 ,p_assignment_id
1034 ,p_original_entry_id
1035 ,p_business_group_id
1036 ,l_legislation_code);
1037 elsif l_legislation_code = 'GB' then
1038 --
1039 -- GB dimensions.
1040 --
1041 hr_utility.trace('pay_balance_upload.dim_expiry_date UK dimensions');
1042 --
1043 statem := 'BEGIN
1044 :l_expiry_date := pay_uk_bal_upload.expiry_date
1045 (:p_upload_date
1046 ,:p_dimension_name
1047 ,:p_assignment_id
1048 ,:p_original_entry_id); END;';
1049 --
1050 execute immediate statem
1051 using out l_expiry_date
1052 ,p_upload_date
1053 ,p_dimension_name
1054 ,p_assignment_id
1055 ,p_original_entry_id
1056 ;
1057 --
1058 elsif (l_legislation_code = 'US') OR (l_legislation_code = 'BF') then
1059 --
1060 -- US + BF dimensions.
1061 --
1062 hr_utility.trace('pay_balance_upload.dim_expiry_date US or BF dimensions');
1063 --
1064 statem := 'BEGIN
1065 :l_expiry_date := pay_'||lower(l_legislation_code)||'_bal_upload.expiry_date
1066 (:p_upload_date
1067 ,:p_dimension_name
1068 ,:p_assignment_id
1069 ,:p_tax_unit_id
1070 ,:p_jurisdiction_code
1071 ,:p_original_entry_id); END;';
1072 --
1073 execute immediate statem
1074 using out l_expiry_date
1075 ,p_upload_date
1076 ,p_dimension_name
1077 ,p_assignment_id
1078 ,p_tax_unit_id
1079 ,p_jurisdiction_code
1080 ,p_original_entry_id
1081 ;
1082 --
1083 else
1084 --
1085 -- Other Legislations dimensions.
1086 --
1087 statem := 'BEGIN
1088 :l_expiry_date := pay_'||lower(l_legislation_code)||'_bal_upload.expiry_date
1089 (:p_upload_date
1090 ,:p_dimension_name
1091 ,:p_assignment_id
1092 ,:p_original_entry_id); END;';
1093
1094 execute immediate statem
1095 using out l_expiry_date
1096 ,p_upload_date
1097 ,p_dimension_name
1098 ,p_assignment_id
1099 ,p_original_entry_id
1100 ;
1101 --
1102 end if;
1103 --
1104 hr_utility.trace('Exiting pay_balance_upload.dim_expiry_date');
1105 --
1106 -- Return the expiry date for the dimension.
1107 --
1108 return (l_expiry_date);
1109 --
1110 exception
1111 when others then
1112 --
1113 -- Ensures not to raise any error since it causes
1114 -- csr_batch_line_transfer to fail, and therefore it
1115 -- cannot be trapped in transfer_assignment.
1116 --
1117 hr_utility.trace('Error in pay_balance_upload.dim_expiry_date');
1118 hr_utility.trace(sqlerrm);
1119 return null;
1120
1121 end dim_expiry_date;
1122 --
1123 -----------------------------------------------------------------------------
1124 -- NAME
1125 -- dim_is_supported
1126 -- PURPOSE
1130 -- ARGUMENTS
1127 -- Returns Y if the specified balance dimension is supported in the
1128 -- balance initialization. This is a wrapper function to call the
1129 -- legislative is_supported function.
1131 -- p_legislation_code
1132 -- p_dimension_name
1133 -- USES
1134 -- pay_xx_bal_upload.is_supported
1135 -- NOTES
1136 -- This function does not check the existence of the dimension.
1137 -----------------------------------------------------------------------------
1138 --
1139 function dim_is_supported
1140 (
1141 p_legislation_code in varchar2
1142 ,p_dimension_name in varchar2
1143 ) return varchar2 is
1144 --
1145 l_dim_name pay_balance_dimensions.dimension_name%type;
1146 l_is_supported boolean;
1147 statem varchar2(256);
1148 dim_not_supp number;
1149 --
1150 begin
1151 --
1152 l_dim_name := upper(p_dimension_name);
1153 --
1154 -- If the dimension uses one of the International Payroll supported routes
1155 -- then process it NB. this could be for any legislation. All other dimensions
1156 -- for a given legislation will be processed in the relevant legislation specific
1157 -- package.
1158 --
1159 if pay_ip_bal_upload.international_payroll(l_dim_name, p_legislation_code) then
1160 l_is_supported := true;
1161 elsif p_legislation_code = 'GB' then
1162 l_is_supported := pay_uk_bal_upload.is_supported(l_dim_name);
1163 elsif p_legislation_code = 'US' then
1164 l_is_supported := pay_us_bal_upload.is_supported(l_dim_name);
1165 elsif p_legislation_code = 'JP' then
1166 l_is_supported := pay_jp_bal_upload.is_supported(l_dim_name);
1167 elsif p_legislation_code = 'CH' then
1168 l_is_supported := pay_ch_bal_upload.is_supported(l_dim_name);
1169 elsif p_legislation_code = 'BF' then
1170 l_is_supported := pay_bf_bal_upload.is_supported(l_dim_name);
1171 else
1172 --
1173 -- Other Legislations dimensions.
1174 -- Note: can't pass booleans in dynamic sql, so new legislative packages
1175 -- should return a number with 0 denoting false, and 1 denoting true.
1176 --
1177 begin
1178 statem := 'BEGIN
1179 :dim_not_supp := pay_'||lower(p_legislation_code)||'_bal_upload.is_supported(:l_dim_name); END;';
1180 --
1181 execute immediate statem
1182 using out dim_not_supp
1183 ,l_dim_name
1184 ;
1185 --
1186 if dim_not_supp = 0 then
1187 l_is_supported := false;
1188 else
1189 l_is_supported := true;
1190 end if;
1191 exception
1192 when others then
1193 --
1194 -- Basically the process reaches here because the localization does
1195 -- not have the correct balance upload package or is using the
1196 -- international payroll but the dimension is not supported.
1197 -- In either case, the dimension is not available, hence marking it
1198 -- as not supported instead of raising a sql error.
1199 --
1200 l_is_supported := false;
1201 end;
1202 end if;
1203 --
1204 if l_is_supported then
1205 return 'Y';
1206 else
1207 return 'N';
1208 end if;
1209 --
1210 end dim_is_supported;
1211 --
1212 -----------------------------------------------------------------------------
1213 -- NAME
1214 -- get_batch_info
1215 -- PURPOSE
1216 -- Returns batch information that is currently running.
1217 -- ARGUMENTS
1218 -- USES
1219 -- NOTES
1220 -----------------------------------------------------------------------------
1221 --
1222 function get_batch_info return t_batch_info_rec
1223 is
1224 begin
1225 return g_batch_info;
1226 end get_batch_info;
1227 --
1228 -----------------------------------------------------------------------------
1229 -- NAME
1230 -- count_contexts
1231 -- PURPOSE
1232 -- Counts the number of contexts a balance dimension uses.
1233 -- ARGUMENTS
1234 -- p_balance_dimension_id - the balance dimeension for which the number of
1235 -- contexts is required for.
1236 -- USES
1237 -- NOTES
1238 -- This is used by the csr_batch_line_transfer cursor.
1239 -----------------------------------------------------------------------------
1240 --
1241 function count_contexts
1242 (
1243 p_balance_dimension_id number,
1244 p_dimension_name varchar2
1245 ) return number is
1246 --
1247 -- Holds the number of contexts a balance dimension uses.
1248 --
1249 l_no_contexts number;
1250 --
1251 begin
1252 --
1253 -- Count the number of contexts used by the balance dimension.
1254 --
1255 select count(CU.context_id)
1256 into l_no_contexts
1257 from pay_balance_dimensions BD
1258 ,ff_route_context_usages CU
1259 where CU.route_id = BD.route_id
1260 and BD.balance_dimension_id = p_balance_dimension_id;
1261 --
1262 --
1263 -- NBR Hard Coding for Korea.
1264 if (p_dimension_name like '%_BON'
1265 or p_dimension_name like '%_MTH') then
1266 l_no_contexts := l_no_contexts +1;
1267 end if;
1268 --
1269 return (l_no_contexts);
1270 --
1271 end count_contexts;
1275 -- lock_batch_header
1272 --
1273 -----------------------------------------------------------------------------
1274 -- NAME
1276 -- PURPOSE
1277 -- Locks the batch header.
1278 -- ARGUMENTS
1279 -- p_batch_id - the batch header to be locked.
1280 -- USES
1281 -- NOTES
1282 -- This is used by the insert, update and delete triggers for the table
1283 -- PAY_BALANCE_BATCH_LINES. This can be used to ensure that the batch lines
1284 -- cannot be changed once another user has a row level lock on the batch
1285 -- header. This is used by the process to freeze the batch definition while
1286 -- it is being processed.
1287 -----------------------------------------------------------------------------
1288 --
1289 procedure lock_batch_header
1290 (
1291 p_batch_id number
1292 ) is
1293 --
1294 -- Locks the batch header.
1295 --
1296 cursor csr_lock_batch_header
1297 (
1298 p_batch_id number
1299 ) is
1300 select BBH.batch_id
1301 from pay_balance_batch_headers BBH
1302 where BBH.batch_id = p_batch_id
1303 for update nowait;
1304 --
1305 -- Holds the batch_id of the locked batch header.
1306 --
1307 l_batch_id number;
1308 --
1309 begin
1310 --
1311 hr_utility.trace('Entering pay_balance_upload.lock_batch_header');
1312 --
1313 -- Lock the specified batch header.
1314 --
1315 begin
1316 open csr_lock_batch_header(p_batch_id);
1317 fetch csr_lock_batch_header into l_batch_id;
1318 close csr_lock_batch_header;
1319 exception
1320 when others then
1321 if csr_lock_batch_header%isopen then
1322 close csr_lock_batch_header;
1323 end if;
1324 raise;
1325 end;
1326 --
1327 hr_utility.trace('Exiting pay_balance_upload.lock_batch_header');
1328 --
1329 end lock_batch_header;
1330 --
1331 -----------------------------------------------------------------------------
1332 -- NAME
1333 -- get_current_value
1334 -- PURPOSE
1335 -- Calculates the current value for a balance based on the list of balance
1336 -- adjustments that have already been worked out ie. a YTD balance may be
1337 -- partially set already by a previous QTD balance.
1338 -- ARGUMENTS
1339 -- p_glbl_data_rec - global data structure.
1340 -- p_batch_line_rec - the current batch line
1341 -- p_current_amount - the current amount for the balance.
1342 -- p_min_expiry_date - the minimum expiry date of all the balances that
1343 -- contribute to the current amount.
1344 -- USES
1345 -- NOTES
1346 -- The list of balance adjustments for the assignment are held in a
1347 -- temporary DB table caled PAY_TEMP_BALANCE_ADJUSTMENTS.
1348 -----------------------------------------------------------------------------
1349 --
1350 procedure get_current_value
1351 (
1352 p_glbl_data_rec in glbl_data_rec_type
1353 ,p_batch_line_rec in csr_batch_line_transfer%rowtype
1354 ,p_current_value out nocopy number
1355 ,p_min_expiry_date out nocopy date
1356 ) is
1357 --
1358 -- Holds information about a balance adjustment held in the temporary table.
1359 --
1360 l_bal_adjustment_rec csr_balance_adjustment%rowtype;
1361 --
1362 -- Indicates whether the balance adjustment contributes to the new balance.
1363 --
1364 l_include boolean := FALSE;
1365 --
1366 -- The current value of the new balance as set by the balance adjustments.
1367 --
1368 l_current_value number := 0;
1369 --
1370 -- The minimum expiry date of all the existing adjustments that effect the
1371 -- new balance.
1372 --
1373 l_min_expiry_date date := p_batch_line_rec.upload_date;
1374 --
1375 -- Dynamic sql variables
1376 --
1377 sql_curs number;
1378 rows_processed integer;
1379 statem varchar2(512);
1380 p_include number;
1381 --
1382 l_jurisdiction_level number;
1383
1384 cursor csr_adjustment_value
1385 (p_balance_type_id in number
1386 ,p_expiry_date in date
1387 ,p_upload_date in date
1388 ,p_jurisdiction_level in number
1389 ) is
1390 select
1391 nvl(sum(BA.adjustment_amount), 0)
1392 ,nvl(min(BA.adjustment_date), p_upload_date)
1393 from pay_temp_balance_adjustments BA
1394 where BA.balance_type_id = p_balance_type_id
1395 and BA.adjustment_date between p_expiry_date
1396 and p_upload_date
1397 and ((p_batch_line_rec.jurisdiction_code is null) or
1398 substr(p_batch_line_rec.jurisdiction_code, 1, p_jurisdiction_level)
1399 = substr(BA.jurisdiction_code, 1, p_jurisdiction_level))
1400 and nvl(p_batch_line_rec.tax_unit_id, nvl(BA.tax_unit_id, -1))
1401 = nvl(BA.tax_unit_id, -1)
1402 and nvl(p_batch_line_rec.original_entry_id, nvl(BA.original_entry_id, -1))
1403 = nvl(BA.original_entry_id, -1)
1404 and nvl(p_batch_line_rec.source_id, nvl(BA.source_id, -1))
1405 = nvl(BA.source_id, -1)
1406 and nvl(p_batch_line_rec.source_text, nvl(BA.source_text, '~nvl~'))
1407 = nvl(BA.source_text, '~nvl~')
1411 = nvl(BA.source_number, -1)
1408 and nvl(p_batch_line_rec.run_type_id, nvl(BA.run_type_id, -1))
1409 = nvl(BA.run_type_id, -1)
1410 and nvl(p_batch_line_rec.source_number, nvl(BA.source_number, -1))
1412 and nvl(p_batch_line_rec.source_text2, nvl(BA.source_text2, '~nvl~'))
1413 = nvl(BA.source_text2, '~nvl~')
1414 ;
1415 begin
1416 --
1417 hr_utility.trace('Entering pay_balance_upload.get_current_value');
1418 --
1419 if p_glbl_data_rec.include_adj_rule = 'N' then
1420 --
1421 -- Generic calculation without include_adjustment.
1422 --
1423 l_jurisdiction_level
1424 := g_balances(p_batch_line_rec.balance_type_id).jurisdiction_level;
1425
1426 open csr_adjustment_value
1427 (p_batch_line_rec.balance_type_id
1428 ,p_batch_line_rec.expiry_date
1429 ,p_batch_line_rec.upload_date
1430 ,l_jurisdiction_level
1431 );
1432 fetch csr_adjustment_value into l_current_value, l_min_expiry_date;
1433 close csr_adjustment_value;
1434
1435 else
1436 --
1437 -- Localization specific calculation using include_adjustment.
1438 --
1439 open csr_balance_adjustment(p_batch_line_rec.balance_type_id
1440 ,p_batch_line_rec.expiry_date
1441 ,p_batch_line_rec.upload_date);
1442 --
1443 -- Loop for all the balance adjustments in the temporary table that are for
1444 -- the same balance as that being set and lie betwwen the expiry date of
1445 -- the new balance and the upload date.
1446 --
1447 loop
1448 --
1449 -- Get the next balance adjustment.
1450 --
1451 fetch csr_balance_adjustment into l_bal_adjustment_rec;
1452 exit when csr_balance_adjustment%notfound;
1453 --
1454 -- See if the balance adjustment contributes to the value of the balance
1455 -- being set NB. this is dependent on the dimension of the new balance.
1456 --
1457 --
1458 -- If the dimension uses one of the International Payroll supported routes
1459 -- then process it NB. this could be for any legislation. All other dimensions
1460 -- for a given legislation will be processed in the relevant legislation specific
1461 -- package.
1462 --
1463 if pay_ip_bal_upload.international_payroll
1464 (p_batch_line_rec.dimension_name
1465 ,p_glbl_data_rec.legislation_code) then
1466
1467 l_include := pay_ip_bal_upload.include_adjustment
1468 (p_batch_line_rec.balance_type_id
1469 ,p_batch_line_rec.dimension_name
1470 ,p_batch_line_rec.original_entry_id
1471 ,p_glbl_data_rec.upload_date
1472 ,p_batch_line_rec.batch_line_id
1473 ,l_bal_adjustment_rec.batch_line_id
1474 ,p_glbl_data_rec.legislation_code);
1475 --
1476 -- UK dimensions.
1477 --
1478 elsif p_glbl_data_rec.legislation_code = 'GB' then
1479 l_include := pay_uk_bal_upload.include_adjustment
1480 (p_batch_line_rec.balance_type_id
1481 ,p_batch_line_rec.dimension_name
1482 ,p_batch_line_rec.original_entry_id
1483 ,l_bal_adjustment_rec);
1484 --
1485 -- US dimensions.
1486 --
1487 elsif p_glbl_data_rec.legislation_code = 'US' then
1488 l_include := pay_us_bal_upload.include_adjustment
1489 (p_batch_line_rec.balance_type_id
1490 ,p_batch_line_rec.dimension_name
1491 ,p_batch_line_rec.jurisdiction_code
1492 ,p_batch_line_rec.original_entry_id
1493 ,p_batch_line_rec.tax_unit_id
1494 ,p_batch_line_rec.assignment_id
1495 ,p_glbl_data_rec.upload_date
1496 ,l_bal_adjustment_rec);
1497 --
1498 -- JP dimensions.
1499 --
1500 elsif p_glbl_data_rec.legislation_code = 'JP' then
1501 l_include := pay_jp_bal_upload.include_adjustment
1502 (p_batch_line_rec.balance_type_id
1503 ,p_batch_line_rec.dimension_name
1504 ,p_batch_line_rec.original_entry_id
1505 ,l_bal_adjustment_rec);
1506 --
1507 -- CH dimensions.
1508 --
1509 elsif p_glbl_data_rec.legislation_code = 'CH' then
1510 l_include := pay_ch_bal_upload.include_adjustment
1511 (p_batch_line_rec.balance_type_id
1512 ,p_batch_line_rec.dimension_name
1513 ,p_batch_line_rec.original_entry_id
1514 ,l_bal_adjustment_rec);
1515 --
1516 -- BF dimensions.
1517 --
1518 elsif p_glbl_data_rec.legislation_code = 'BF' then
1519 l_include := pay_bf_bal_upload.include_adjustment
1520 (p_batch_line_rec.balance_type_id
1521 ,p_batch_line_rec.dimension_name
1522 ,p_batch_line_rec.jurisdiction_code
1523 ,p_batch_line_rec.original_entry_id
1524 ,p_batch_line_rec.tax_unit_id
1525 ,p_batch_line_rec.assignment_id
1529 ,l_bal_adjustment_rec);
1526 ,p_glbl_data_rec.upload_date
1527 ,p_batch_line_rec.source_id
1528 ,p_batch_line_rec.source_text
1530 else
1531 --
1532 -- Other Legislations dimensions.
1533 -- Note: can't pass booleans or records in dynamic sql, so we pass
1534 -- in the batch_line_id (and thus the legislative packages have to
1535 -- fetch the line info themselves), and the legislative packages should
1536 -- return a number with 0 denoting false, and 1 denoting true.
1537 --
1538 statem := 'BEGIN
1539 :p_include := pay_'||lower(p_glbl_data_rec.legislation_code)||'_bal_upload.include_adjustment
1540 (:p_balance_type_id
1541 ,:p_dimension_name
1542 ,:p_original_entry_id
1543 ,:p_upload_date
1544 ,:p_batch_line_id
1545 ,:p_test_batch_line_id); END;';
1546 --
1547 execute immediate statem
1548 using out p_include
1549 ,p_batch_line_rec.balance_type_id
1550 ,p_batch_line_rec.dimension_name
1551 ,p_batch_line_rec.original_entry_id
1552 ,p_glbl_data_rec.upload_date
1553 ,p_batch_line_rec.batch_line_id
1554 ,l_bal_adjustment_rec.batch_line_id
1555 ;
1556 --
1557 if p_include = 0 then
1558 l_include := FALSE;
1559 else
1560 l_include := TRUE;
1561 end if;
1562 --
1563 end if;
1564 --
1565 -- The balance adjustment contributes to the new balance so add to the
1566 -- running total. Also keep track of the earliest expiry date of the
1567 -- balance adjustments.
1568 --
1569 if l_include then
1570 hr_utility.set_location('pay_balance_upload.get_current_value',10);
1571 l_current_value :=
1572 l_current_value + l_bal_adjustment_rec.adjustment_amount;
1573 l_min_expiry_date :=
1574 least(l_min_expiry_date, l_bal_adjustment_rec.expiry_date);
1575 end if;
1576 --
1577 end loop;
1578 --
1579 close csr_balance_adjustment;
1580 --
1581 end if;
1582 --
1583 -- Return the current value.
1584 --
1585 p_current_value := l_current_value;
1586 p_min_expiry_date := l_min_expiry_date;
1587 --
1588 hr_utility.trace('Current Value = '|| l_current_value);
1589 hr_utility.trace('Exiting pay_balance_upload.get_current_value');
1590 --
1591 end get_current_value;
1592 --
1593 -----------------------------------------------------------------------------
1594 -- NAME
1595 -- calculate_adjustment
1596 -- PURPOSE
1597 -- Calculates the balance adjustment required to set a balance to a
1598 -- particular value. It takes into account previous balance adjustments
1599 -- which may also contribute to the balance.
1600 -- ARGUMENTS
1601 -- p_glbl_data_rec - global data structure.
1602 -- p_batch_line_rec - the current batch line
1603 -- USES
1604 -- get_current_value
1605 -- NOTES
1606 -----------------------------------------------------------------------------
1607 --
1608 procedure calculate_adjustment
1609 (
1610 p_glbl_data_rec in glbl_data_rec_type
1611 ,p_batch_line_rec in csr_batch_line_transfer%rowtype
1612 ) is
1613 --
1614 -- Retrieves the payroll the assignment is on at a particular time.
1615 --
1616 cursor csr_payroll
1617 (
1618 p_assignment_id number
1619 ,p_effective_date date
1620 ) is
1621 select ASS.payroll_id
1622 from per_all_assignments_f ASS
1623 where ASS.assignment_id = p_assignment_id
1624 and p_effective_date between ASS.effective_start_date
1625 and ASS.effective_end_date;
1626 --
1627 -- Holds the payroll the assignment is on.
1628 --
1629 l_payroll_id number;
1630 --
1631 -- Holds the expiry date of the dimension.
1632 --
1633 l_expiry_date date;
1634 --
1635 -- Holds the current value of a balance as set by the planned balance
1636 -- adjustments.
1637 --
1638 l_curr_value number := 0;
1639 l_min_expiry_date date;
1640 --
1641 -- Holds the amount and date of the adjustment required to set the balance
1642 -- to the correct value.
1643 --
1644 l_adjustment_amount number;
1645 l_adjustment_date date;
1646 --
1647 -- Holds the tax unit
1648 --
1649 l_tax_unit_id number;
1650 begin
1651 --
1652 hr_utility.trace('Entering pay_balance_upload.calculate_adjustment');
1653 --
1654 -- Retrieve the current value for the initial balance as set by previous
1655 -- balance adjustments calculated for the assignment.
1656 --
1657 get_current_value(p_glbl_data_rec
1658 ,p_batch_line_rec
1659 ,l_curr_value
1660 ,l_min_expiry_date);
1661 --
1665 --
1662 -- Calculate the amount required to set the balance to it's initial value
1663 -- ie. the difference between what the value should be and the value it
1664 -- currently is (according to the existing balance adjustments).
1666 l_adjustment_amount := p_batch_line_rec.value - l_curr_value;
1667 --
1668 -- The balance adjustment is to be done on the expiry date of the dimension
1669 -- being set NB. the advantage of this is that it reduces the chances of
1670 -- the adjustment affecting other dimensions inadvertently and it also
1671 -- ensures that the dimension criteria is met ie. the assignment belongs to
1672 -- the correct tax unit, etc...
1673 --
1674 l_adjustment_date := p_batch_line_rec.expiry_date;
1675 --
1676 -- Ensure that the proposed balance adjustment is valid.
1677 --
1678 -- The expiry date could not be derived successfully NB. this is signified
1679 -- by an expiry date of the end of time.
1680 --
1681 hr_utility.trace(p_batch_line_rec.dimension_name||' '||
1682 p_batch_line_rec.jurisdiction_code||' '||
1683 p_batch_line_rec.gre_name);
1684 hr_utility.trace('Adjustment Date '||l_adjustment_date);
1685 hr_utility.trace('Adjustment Value '|| l_adjustment_amount);
1686 hr_utility.trace('Adjustment Run Type '|| p_batch_line_rec.run_type_id);
1687
1688 if nvl(l_adjustment_date, END_OF_TIME) = END_OF_TIME
1689 or l_adjustment_date > p_batch_line_rec.upload_date then
1690
1691 hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1692 hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1693 raise hr_utility.hr_error;
1694 -- null; -- need to set up message and raise hr_utility.hr_error.
1695 end if;
1696 --
1697 -- Amount cannot be set as the current value is greater than the amount to
1698 -- be set eg. QTD was 2000.00 while the YTD was 1500.00.
1699 --
1700 -- Negative ajustments of balances are valid.
1701 /*
1702 if l_adjustment_amount < 0 then
1703 hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1704 raise hr_utility.hr_error;
1705 -- null; -- need to set up message and raise hr_utility.hr_error.
1706 end if;
1707 */
1708 --
1709 -- Assignment is not to a payroll on the adjustment date NB. an optimisation
1710 -- here would be to cache the previously tested date for the assignment and
1711 -- then only carry out the test if the date was different.
1712 --
1713 l_payroll_id := null;
1714 open csr_payroll(p_glbl_data_rec.assignment_id
1715 ,l_adjustment_date);
1716 fetch csr_payroll into l_payroll_id;
1717 close csr_payroll;
1718 if l_payroll_id is null then
1719 hr_utility.set_message(801, 'HR_7789_SETUP_ASG_HAS_NO_PAYR');
1720 hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1721 raise hr_utility.hr_error;
1722 -- null; -- need to set up message and raise hr_utility.hr_error.
1723 end if;
1724 --
1725 -- In the US, each assignment always belongs to a legal company. If the
1726 -- legal compamy was not specified as a context then get the legal comapny
1727 -- the assignment belongs to as of the adjustment date. This could be used
1728 -- when calculating the current value for a new balance.
1729 --
1730 l_tax_unit_id := p_batch_line_rec.tax_unit_id;
1731 if (l_tax_unit_id is null) then
1732 --
1733 l_tax_unit_id := hr_dynsql.get_tax_unit(
1734 p_batch_line_rec.assignment_id,
1735 l_adjustment_date);
1736 --
1737 /* NBR no need to do this anymore need to call the
1738 core package.
1739 if p_glbl_data_rec.legislation_code = 'US' then
1740 l_tax_unit_id := pay_us_bal_upload.get_tax_unit
1741 (p_batch_line_rec.assignment_id
1742 ,l_adjustment_date);
1743 end if;
1744 -- As US
1745 if p_glbl_data_rec.legislation_code = 'BF' then
1746 l_tax_unit_id := pay_bf_bal_upload.get_tax_unit
1747 (p_batch_line_rec.assignment_id
1748 ,l_adjustment_date);
1749 end if;
1750 */
1751 end if;
1752 --
1753 -- Add new balance adjustment to the list NB. if the existing balance
1754 -- adjustments already add up to the correct amount for the initial balance
1755 -- then do not create a new balance adjustment.
1756 --
1757 insert into pay_temp_balance_adjustments
1758 (batch_line_id
1759 ,balance_type_id
1760 ,balance_dimension_id
1761 ,expiry_date
1762 ,element_link_id
1763 ,ibf_input_value_id
1764 ,jc_input_value_id
1765 ,adjustment_date
1766 ,adjustment_amount
1767 ,tax_unit_id
1768 ,jurisdiction_code
1769 ,source_id
1770 ,source_text
1771 ,source_number
1772 ,source_text2
1773 ,run_type_id
1774 ,original_entry_id)
1775 values
1776 (p_batch_line_rec.batch_line_id
1777 ,p_batch_line_rec.balance_type_id
1778 ,p_batch_line_rec.balance_dimension_id
1779 ,p_batch_line_rec.expiry_date
1780 ,g_balances(p_batch_line_rec.balance_type_id).element_link_id
1781 ,g_balances(p_batch_line_rec.balance_type_id).ibf_input_value_id
1785 ,l_tax_unit_id
1782 ,g_balances(p_batch_line_rec.balance_type_id).jc_input_value_id
1783 ,l_adjustment_date
1784 ,l_adjustment_amount
1786 ,p_batch_line_rec.jurisdiction_code
1787 ,p_batch_line_rec.source_id
1788 ,p_batch_line_rec.source_text
1789 ,p_batch_line_rec.source_number
1790 ,p_batch_line_rec.source_text2
1791 ,p_batch_line_rec.run_type_id
1792 ,p_batch_line_rec.original_entry_id);
1793 --
1794 hr_utility.trace('Exiting pay_balance_upload.calculate_adjustment');
1795 --
1796 end calculate_adjustment;
1797 --
1798 -----------------------------------------------------------------------------
1799 -- NAME
1800 -- load_element_contexts
1801 -- PURPOSE
1802 -- This procedure loads details of the element types context usage.
1803 -- ARGUMENTS
1804 -- USES
1805 -- NOTES
1806 -----------------------------------------------------------------------------
1807 --
1808 procedure load_element_contexts(p_element_link_id in number,
1809 p_start_ptr out nocopy number,
1810 p_end_ptr out nocopy number
1811 )
1812 is
1813 l_iv_id pay_input_values_f.input_value_id%type;
1814 l_start_ptr number;
1815 l_ptr number;
1816 begin
1817 --
1818 hr_utility.trace('Entering load_element_contexts');
1819 --
1820 p_start_ptr:= null;
1821 p_end_ptr := null;
1822 --
1823 if (g_element_link_contexts.exists(p_element_link_id)) then
1824 --
1825 p_start_ptr := g_element_link_contexts(p_element_link_id).start_ptr;
1826 p_end_ptr := g_element_link_contexts(p_element_link_id).end_ptr;
1827 --
1828 else
1829 --
1830 -- Initialise variables.
1831 --
1832 g_element_link_contexts(p_element_link_id).start_ptr := p_start_ptr;
1833 g_element_link_contexts(p_element_link_id).end_ptr := p_end_ptr;
1834 l_start_ptr := g_input_val_contexts.count + 1;
1835 --
1836 for i in 1..g_legislation_contexts.count loop
1837 --
1838 if (g_legislation_contexts(i).input_value_name is not null) then
1839 --
1840 -- OK does this element have this legislation context
1841 begin
1842 select piv.input_value_id
1843 into l_iv_id
1844 from pay_input_values_f piv,
1845 pay_element_links_f pel
1846 where pel.element_link_id= p_element_link_id
1847 and pel.element_type_id = piv.element_type_id
1848 and piv.name = g_legislation_contexts(i).input_value_name
1849 and piv.effective_start_date = START_OF_TIME
1850 and piv.effective_end_date = END_OF_TIME
1851 and pel.effective_start_date = START_OF_TIME
1852 and pel.effective_end_date = END_OF_TIME;
1853 --
1854 l_ptr := g_input_val_contexts.count + 1;
1855 g_input_val_contexts(l_ptr).context_name := g_legislation_contexts(i).context_name;
1856 g_input_val_contexts(l_ptr).input_value_id := l_iv_id;
1857 g_element_link_contexts(p_element_link_id).start_ptr := l_start_ptr;
1858 g_element_link_contexts(p_element_link_id).end_ptr := g_input_val_contexts.count;
1859 --
1860 exception
1861 when no_data_found then
1862 null;
1863 end ;
1864 --
1865 end if;
1866 --
1867 end loop;
1868 --
1869 p_start_ptr := g_element_link_contexts(p_element_link_id).start_ptr;
1870 p_end_ptr := g_element_link_contexts(p_element_link_id).end_ptr;
1871 --
1872 end if;
1873 --
1874 hr_utility.trace('Exitting load_element_contexts');
1875 --
1876 end load_element_contexts;
1877 --
1878
1879 -----------------------------------------------------------------------------
1880 -- NAME
1881 -- set_entry_context
1882 -- PURPOSE
1883 -- This procedure sets a specific context for an entry.
1884 -- ARGUMENTS
1885 -- USES
1886 -- NOTES
1887 -----------------------------------------------------------------------------
1888 --
1889 procedure set_entry_context
1890 (
1891 p_element_link_id in number,
1892 p_context_name in varchar2,
1893 p_context_value in varchar2,
1894 p_num_entry_values in out nocopy number,
1895 p_input_value_id_tbl in out nocopy hr_entry.number_table,
1896 p_entry_value_tbl in out nocopy hr_entry.varchar2_table
1897 )
1898 is
1899 --
1900 l_start_ptr number;
1901 l_end_ptr number;
1902 l_iv_id pay_input_values_f.input_value_id%type;
1903 cnt number;
1904 found boolean;
1905 --
1906 begin
1907 --
1908 hr_utility.trace('Entering set_entry_context');
1909 --
1910 load_element_contexts(p_element_link_id,
1911 l_start_ptr,
1912 l_end_ptr
1913 );
1914 --
1915 -- Need to get the input value to use.
1916 --
1917 l_iv_id := null;
1918 --
1919 -- Only need to do something if the element has contexts.
1920 --
1921 if (l_start_ptr is not null) then
1922 for i in l_start_ptr..l_end_ptr loop
1923 --
1927 --
1924 if (p_context_name = g_input_val_contexts(i).context_name) then
1925 --
1926 l_iv_id := g_input_val_contexts(i).input_value_id;
1928 end if;
1929 --
1930 end loop;
1931 --
1932 -- Now set the entry value.
1933 --
1934 if (l_iv_id is not null) then
1935 --
1936 found := FALSE;
1937 for cnt in 1..p_num_entry_values loop
1938 --
1939 if (l_iv_id = p_input_value_id_tbl(cnt)) then
1940 found := TRUE;
1941 exit;
1942 end if;
1943 --
1944 end loop;
1945 --
1946 -- Only set the context if needed.
1947 --
1948 if (found = FALSE) then
1949 --
1950 p_num_entry_values := p_num_entry_values + 1;
1951 p_input_value_id_tbl(p_num_entry_values) := l_iv_id;
1952 p_entry_value_tbl(p_num_entry_values) := p_context_value;
1953 --
1954 end if;
1955 --
1956 end if;
1957 end if;
1958 --
1959 hr_utility.trace('Exitting set_entry_context');
1960 --
1961 end set_entry_context;
1962 -----------------------------------------------------------------------------
1963 -- NAME
1964 -- create_entry_values
1965 -- PURPOSE
1966 -- This procedure creates the appropreate entry values for an adjustment.
1967 -- It ensures that the contexts for the entry are set correctly
1968 -- ARGUMENTS
1969 -- USES
1970 -- NOTES
1971 -----------------------------------------------------------------------------
1972 --
1973 procedure create_entry_values
1974 (
1975 p_element_link_id in number,
1976 p_jurisdiction_code in varchar2,
1977 p_source_id in number,
1978 p_source_text in varchar2,
1979 p_source_number in number,
1980 p_source_text2 in varchar2,
1981 p_adj_amount in number,
1982 p_adj_iv_id in number,
1983 p_num_entry_values in out nocopy number,
1984 p_input_value_id_tbl in out nocopy hr_entry.number_table,
1985 p_entry_value_tbl in out nocopy hr_entry.varchar2_table
1986 )
1987 is
1988 begin
1989 --
1990 hr_utility.trace('Entering create_entry_values');
1991 -- OK setup the contexts
1992 --
1993 if (p_jurisdiction_code is not null) then
1994 set_entry_context(
1995 p_element_link_id,
1996 'JURISDICTION_CODE',
1997 p_jurisdiction_code,
1998 p_num_entry_values,
1999 p_input_value_id_tbl,
2000 p_entry_value_tbl
2001 );
2002 end if;
2003 if (p_source_id is not null) then
2004 set_entry_context(
2005 p_element_link_id,
2006 'SOURCE_ID',
2007 to_char(p_source_id),
2008 p_num_entry_values,
2009 p_input_value_id_tbl,
2010 p_entry_value_tbl
2011 );
2012 end if;
2013 if (p_source_text is not null) then
2014 set_entry_context(
2015 p_element_link_id,
2016 'SOURCE_TEXT',
2017 p_source_text,
2018 p_num_entry_values,
2019 p_input_value_id_tbl,
2020 p_entry_value_tbl
2021 );
2022 end if;
2023 if (p_source_number is not null) then
2024 set_entry_context(
2025 p_element_link_id,
2026 'SOURCE_NUMBER',
2027 to_char(p_source_number),
2028 p_num_entry_values,
2029 p_input_value_id_tbl,
2030 p_entry_value_tbl
2031 );
2032 end if;
2033 if (p_source_text2 is not null) then
2034 set_entry_context(
2035 p_element_link_id,
2036 'SOURCE_TEXT2',
2037 p_source_text2,
2038 p_num_entry_values,
2039 p_input_value_id_tbl,
2040 p_entry_value_tbl
2041 );
2042 end if;
2043 --
2044 -- Now set the value
2045 p_num_entry_values := p_num_entry_values + 1;
2046 p_input_value_id_tbl(p_num_entry_values) := p_adj_iv_id;
2047 p_entry_value_tbl(p_num_entry_values) := to_char(p_adj_amount);
2048 --
2049 hr_utility.trace('Exitting create_entry_values');
2050 --
2051 end create_entry_values;
2052 -----------------------------------------------------------------------------
2053 -- NAME
2054 -- apply_adjustments
2055 -- PURPOSE
2056 -- Applies the list of balance adjustments held in the temporary table. The
2057 -- balance adjustments are combined where possible to reduce the number of
2058 -- balance adjustments to be made ie. if 2 adjustments use the same entry
2059 -- and they have to be done on the same day then they can share an
2060 -- adjustment.
2061 -- ARGUMENTS
2062 -- p_glbl_data_rec - global data structure.
2063 -- p_batch_line_list - the list of batch lines currently being processed
2064 -- p_num_lines - the number of batch lines in the list.
2068 -- pay_bal_adjust.adjust_balance
2065 -- p_message - the error message.
2066 -- USES
2067 -- pay_bal_adjust.init_batch
2069 -- NOTES
2070 -----------------------------------------------------------------------------
2071 --
2072 procedure apply_adjustments
2073 (
2074 p_glbl_data_rec in glbl_data_rec_type
2075 ,p_batch_line_list in out nocopy number_array
2076 ,p_num_lines in out nocopy number
2077 ) is
2078 --
2079 -- Retrieves the balance adjustments held in the temporary table.
2080 --
2081 -- Bug 4872523. Retrieving payroll id for the adjustment date.
2082 -- Note the assignment has already been checked in calculate_adjustment.
2083 --
2084 cursor csr_bal_adj is
2085 select TBA.*
2086 ,ASG.payroll_id
2087 from pay_temp_balance_adjustments TBA
2088 ,per_all_assignments_f ASG
2089 where
2090 ASG.assignment_id = p_glbl_data_rec.assignment_id
2091 and TBA.adjustment_date between ASG.effective_start_date
2092 and ASG.effective_end_date
2093 order by TBA.element_link_id
2094 ,TBA.adjustment_date
2095 ,TBA.jurisdiction_code
2096 ,TBA.original_entry_id
2097 ,TBA.tax_unit_id
2098 ,TBA.source_id
2099 ,TBA.source_text
2100 ,TBA.source_number
2101 ,TBA.source_text2
2102 ,TBA.run_type_id
2103 ,TBA.balance_type_id;
2104 --
2105 -- Holds information about a balance adjustment.
2106 --
2107 l_bal_adj_rec csr_bal_adj%rowtype;
2108 --
2109 -- Strutures to hold the values of element entry values to be used when
2110 -- creating an element entry.
2111 --
2112 l_num_entry_values number := 0;
2113 l_input_value_id_tbl hr_entry.number_table;
2114 l_entry_value_tbl hr_entry.varchar2_table;
2115 --
2116 -- Holds information about the balance adjustment.
2117 --
2118 l_ele_link_id number;
2119 l_bal_type_id number;
2120 l_tax_unit_id number;
2121 l_oee_id number;
2122 l_source_id number;
2123 l_run_type_id number;
2124 l_source_text pay_balance_batch_lines.source_text%type;
2125 l_source_number pay_balance_batch_lines.source_number%type;
2126 l_source_text2 pay_balance_batch_lines.source_text2%type;
2127 l_adj_date date;
2128 l_jurisdiction_code varchar2(30);
2129 l_payroll_id number;
2130 --
2131 -- Holds the payroll action used by the latest balance adjustment for an
2132 -- assignment.
2133 --
2134 l_payroll_action_id number;
2135 l_payroll_action_rec g_payroll_action_rec_type;
2136 --
2137 l_idx binary_integer;
2138 l_batch_mode varchar2(30):= 'STANDARD';
2139 --
2140 -- Dummy constants for handling null conditions.
2141 --
2142 c_number constant number := -987456321;
2143 c_varchar2 constant varchar2(10) := '~nvl~';
2144 --
2145 begin
2146 --
2147 hr_utility.trace('Entering pay_balance_upload.apply_adjustments');
2148 --
2149 open csr_bal_adj;
2150 --
2151 -- Get the first balance adjustment.
2152 --
2153 fetch csr_bal_adj into l_bal_adj_rec;
2154 --
2155 -- At least one balance adjustment exists.
2156 --
2157 hr_utility.set_location('pay_balance_upload.apply_adjustments', 10);
2158 if csr_bal_adj%found then
2159 --
2160 -- Keep track of balance adjustment information.
2161 --
2162 hr_utility.trace('Adding to the Adjustment List');
2163 hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2164 hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2165 hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2166 hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2167 hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2168 hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2169 hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2170 hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2171 hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2172 hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2173 hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2174 hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2175 hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2176 hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2177 l_ele_link_id := l_bal_adj_rec.element_link_id;
2178 l_oee_id := l_bal_adj_rec.original_entry_id;
2179 l_bal_type_id := l_bal_adj_rec.balance_type_id;
2180 l_adj_date := l_bal_adj_rec.adjustment_date;
2181 l_jurisdiction_code := l_bal_adj_rec.jurisdiction_code;
2182 l_tax_unit_id := l_bal_adj_rec.tax_unit_id;
2183 l_source_id := l_bal_adj_rec.source_id;
2184 l_source_text := l_bal_adj_rec.source_text;
2185 l_source_number := l_bal_adj_rec.source_number;
2186 l_source_text2 := l_bal_adj_rec.source_text2;
2187 l_run_type_id := l_bal_adj_rec.run_type_id;
2188 l_payroll_id := l_bal_adj_rec.payroll_id;
2189 --
2190 -- Initialise batch line structure.
2191 --
2192 p_num_lines := 1;
2196 -- Add to the list of entry values to be used with the next balance
2193 p_batch_line_list(p_num_lines) := l_bal_adj_rec.batch_line_id;
2194 --
2195 hr_utility.set_location('pay_balance_upload.apply_adjustments', 15);
2197 -- adjustment.
2198 --
2199 create_entry_values (
2200 l_ele_link_id,
2201 l_jurisdiction_code,
2202 l_source_id,
2203 l_source_text,
2204 l_source_number,
2205 l_source_text2,
2206 l_bal_adj_rec.adjustment_amount,
2207 l_bal_adj_rec.ibf_input_value_id,
2208 l_num_entry_values,
2209 l_input_value_id_tbl,
2210 l_entry_value_tbl
2211 );
2212 --
2213 -- Loop for all the balance adjustments in the list.
2214 --
2215 hr_utility.set_location('pay_balance_upload.apply_adjustments', 20);
2216 loop
2217 hr_utility.set_location('pay_balance_upload.apply_adjustments', 30);
2218 --
2219 -- Get the next balance adjustment.
2220 --
2221 fetch csr_bal_adj into l_bal_adj_rec;
2222 --
2223 -- The new balance adjustment cannot be shared with the previous
2224 -- adjustment so apply the previous adjustment. The rules for sharing
2225 -- are as follows :-
2226 --
2227 -- 1. must use the same element link and therefore the same element type.
2228 -- 2. adjustment is on the same day.
2229 -- 3. if both are dependent on context values then they must be the
2230 -- same as only one combination can be set for each adjustment.
2231 -- 4. each balance can only be set once per adjustment.
2232 --
2233 if not ( l_bal_adj_rec.element_link_id = l_ele_link_id
2234 and l_bal_adj_rec.adjustment_date = l_adj_date
2235 -- check context values
2236 and nvl(l_bal_adj_rec.original_entry_id, c_number)
2237 = nvl(l_oee_id, c_number)
2238 and nvl(l_bal_adj_rec.jurisdiction_code, c_varchar2)
2239 = nvl(l_jurisdiction_code, c_varchar2)
2240 and nvl(l_bal_adj_rec.tax_unit_id, c_number)
2241 = nvl(l_tax_unit_id, c_number)
2242 and nvl(l_bal_adj_rec.run_type_id, c_number)
2243 = nvl(l_run_type_id, c_number)
2244 and nvl(l_bal_adj_rec.source_id, c_number)
2245 = nvl(l_source_id, c_number)
2246 and nvl(l_bal_adj_rec.source_text, c_varchar2)
2247 = nvl(l_source_text, c_varchar2)
2248 and nvl(l_bal_adj_rec.source_number, c_number)
2249 = nvl(l_source_number, c_number)
2250 and nvl(l_bal_adj_rec.source_text2, c_varchar2)
2251 = nvl(l_source_text2, c_varchar2)
2252 --
2253 and l_bal_adj_rec.balance_type_id <> l_bal_type_id
2254 )
2255 or csr_bal_adj%notfound then
2256 --
2257 hr_utility.set_location('pay_balance_upload.apply_adjustments', 40);
2258
2259 l_payroll_action_id := null;
2260 --
2261 -- Firstly see if the last payroll action is available.
2262 --
2263 if l_payroll_action_rec.effective_date = l_adj_date and
2264 l_payroll_action_rec.payroll_id = l_payroll_id then
2265 --
2266 -- The previous payroll action is available.
2267 --
2268 l_payroll_action_id := l_payroll_action_rec.payroll_action_id;
2269 else
2270
2271 --
2272 -- Check to see if the payroll action is already prepared.
2273 --
2274 l_payroll_action_id := null;
2275 <<payroll_action_search_loop>>
2276 for l_idx in 1..g_payroll_actions.count loop
2277
2278 if g_payroll_actions(l_idx).effective_date = l_adj_date and
2279 g_payroll_actions(l_idx).payroll_id = l_payroll_id then
2280
2281 l_payroll_action_rec := g_payroll_actions(l_idx);
2282 l_payroll_action_id := l_payroll_action_rec.payroll_action_id;
2283 exit payroll_action_search_loop;
2284
2285 end if;
2286
2287 end loop;
2288
2289 end if;
2290
2291 --
2292 -- If the payroll action does not exist in cache, then prepare a
2293 -- new payroll action id.
2294 --
2295 if l_payroll_action_id is null then
2296
2297 --
2298 if p_glbl_data_rec.purge_mode then
2299 l_batch_mode := 'NO_COMMIT';
2300 else
2301 l_batch_mode := 'STANDARD';
2302 end if;
2303 --
2304
2305 l_payroll_action_id :=
2306 pay_bal_adjust.init_batch
2307 (p_batch_name => NULL
2308 ,p_effective_date => l_adj_date
2309 ,p_consolidation_set_id => p_glbl_data_rec.consolidation_set_id
2310 ,p_payroll_id => l_payroll_id
2311 ,p_action_type => 'I'
2312 ,p_batch_mode => l_batch_mode
2313 ,p_prepay_flag => null
2317
2314 );
2315
2316 l_idx := g_payroll_actions.count + 1;
2318 hr_utility.trace('Adding new payroll action information to cache.');
2319 hr_utility.trace(' payroll_action_id = '|| l_payroll_action_id);
2320 hr_utility.trace(' effective_date = '|| l_adj_date);
2321 hr_utility.trace(' payroll_id = '|| l_payroll_id);
2322
2323 l_payroll_action_rec.payroll_action_id := l_payroll_action_id;
2324 l_payroll_action_rec.effective_date := l_adj_date;
2325 l_payroll_action_rec.payroll_id := l_payroll_id;
2326
2327 g_payroll_actions(l_idx) := l_payroll_action_rec;
2328
2329 end if;
2330
2331 --
2332 -- Create the balance adjustment.
2333 -- Changed from create_adjustment to pay_bal_adjust.adjust_balance
2334 -- to perform batch adjustment for the batch.
2335 --
2336 pay_bal_adjust.adjust_balance
2337 (p_batch_id => l_payroll_action_id
2338 ,p_assignment_id => p_glbl_data_rec.assignment_id
2339 ,p_element_link_id => l_ele_link_id
2340 ,p_num_entry_values => l_num_entry_values
2341 ,p_input_value_id_tbl => l_input_value_id_tbl
2342 ,p_entry_value_tbl => l_entry_value_tbl
2343 ,p_run_type_id => l_run_type_id
2344 ,p_original_entry_id => l_oee_id
2345 ,p_tax_unit_id => l_tax_unit_id
2346 ,p_purge_mode => p_glbl_data_rec.purge_mode
2347 );
2348
2349 --
2350 -- Mark the batch lines that have just been transferred with a status
2351 -- of 'T'ransferred. Also set the payroll_action_id to that used by
2352 -- the balance adjustment. This ensures that the balance adjustment
2353 -- used to set each initial balance is held against the corresponding
2354 -- batch line.
2355 --
2356 for l_index in 1..p_num_lines loop
2357 hr_utility.trace('Updating Line '||p_batch_line_list(l_index)||
2358 ' With Payroll Action '||l_payroll_action_id);
2359 update pay_balance_batch_lines BL
2360 set BL.batch_line_status = 'T' -- Transferred
2361 ,BL.payroll_action_id = l_payroll_action_id
2362 where BL.batch_line_id = p_batch_line_list(l_index);
2363 end loop;
2364 --
2365 -- Reset in preparation for a new set of balance adjustments.
2366 --
2367 l_num_entry_values := 0;
2368 p_num_lines := 0;
2369 --
2370 end if;
2371 --
2372 -- Stop when there are no more balance adjustments.
2373 --
2374 exit when csr_bal_adj%notfound;
2375 --
2376 -- Keep track of balance adjustment information.
2377 --
2378 hr_utility.trace('Adding to the Adjustment List');
2379 hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2380 hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2381 hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2382 hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2383 hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2384 hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2385 hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2386 hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2387 hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2388 hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2389 hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2390 hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2391 l_ele_link_id := l_bal_adj_rec.element_link_id;
2392 l_oee_id := l_bal_adj_rec.original_entry_id;
2393 l_bal_type_id := l_bal_adj_rec.balance_type_id;
2394 l_adj_date := l_bal_adj_rec.adjustment_date;
2395 l_jurisdiction_code := l_bal_adj_rec.jurisdiction_code;
2396 l_tax_unit_id := l_bal_adj_rec.tax_unit_id;
2397 l_source_id := l_bal_adj_rec.source_id;
2398 l_source_text := l_bal_adj_rec.source_text;
2399 l_source_number := l_bal_adj_rec.source_number;
2400 l_source_text2 := l_bal_adj_rec.source_text2;
2401 l_run_type_id := l_bal_adj_rec.run_type_id;
2402 l_payroll_id := l_bal_adj_rec.payroll_id;
2403 --
2404 -- Add batch line to the list of batch lines currently being processed.
2405 --
2406 p_num_lines := p_num_lines + 1;
2407 p_batch_line_list(p_num_lines) := l_bal_adj_rec.batch_line_id;
2408 --
2409 -- Add to the list of entry values to be used with the next balance
2410 -- adjustment.
2411 --
2412 create_entry_values (
2413 l_ele_link_id,
2414 l_jurisdiction_code,
2415 l_source_id,
2416 l_source_text,
2417 l_source_number,
2418 l_source_text2,
2419 l_bal_adj_rec.adjustment_amount,
2423 l_entry_value_tbl
2420 l_bal_adj_rec.ibf_input_value_id,
2421 l_num_entry_values,
2422 l_input_value_id_tbl,
2424 );
2425 --
2426 end loop;
2427 hr_utility.set_location('pay_balance_upload.apply_adjustments', 50);
2428 --
2429 end if;
2430 --
2431 close csr_bal_adj;
2432 --
2433 hr_utility.trace('Exiting pay_balance_upload.apply_adjustments');
2434 --
2435 -- Trap any exceptions, put the error message into the message
2436 -- variable and raise an internal exception to indicate that there
2437 -- has been a failure. Close any open cursors.
2438 --
2439 exception
2440 when others then
2441 --
2442 -- Close the cursor if it is open.
2443 --
2444 if csr_bal_adj%isopen then
2445 close csr_bal_adj;
2446 end if;
2447 --
2448 -- reraise the exception to be caught at a higer level.
2449 --
2450 raise;
2451 --
2452 end apply_adjustments;
2453 --
2454 -----------------------------------------------------------------------------
2455 -- NAME
2456 -- cache_balances
2457 -- PURPOSE
2458 -- Caches initial balance feed information for each balance in a batch.
2459 -- ARGUMENTS
2460 -- USES
2461 -- NOTES
2462 -- The information is held in PLSQL tables indexed by the balance_type_id
2463 -- for fast access.
2464 -----------------------------------------------------------------------------
2465 --
2466 procedure cache_balances
2467 is
2468 --
2469 -- Record to hold initial balance feed information.
2470 --
2471 l_bal_id number;
2472 l_idx number;
2473 --
2474 begin
2475 --
2476 hr_utility.trace('Entering pay_balance_upload.cache_balances');
2477 --
2478 -- Clear the balance cache.
2479 --
2480 g_balances.delete;
2481
2482 --
2483 -- Copy valid balance info from the validation cache.
2484 --
2485 for l_idx in 1..g_bal_vald.count loop
2486
2487 --
2488 if (not g_bal_vald(l_idx).bal_invld) and
2489 (not g_bal_vald(l_idx).bal_invl_feed) and
2490 (not g_bal_vald(l_idx).bal_invl_link) then
2491 --
2492 l_bal_id := g_bal_vald(l_idx).balance_type_id;
2493 --
2494 g_balances(l_bal_id).element_link_id := g_bal_vald(l_idx).element_link_id;
2495 g_balances(l_bal_id).ibf_input_value_id := g_bal_vald(l_idx).ibf_input_value_id;
2496 g_balances(l_bal_id).jc_input_value_id := g_bal_vald(l_idx).jc_input_value_id;
2497 g_balances(l_bal_id).jurisdiction_level := g_bal_vald(l_idx).jurisdiction_level;
2498 end if;
2499 --
2500 end loop;
2501 --
2502 hr_utility.trace('Exiting pay_balance_upload.cache_balances');
2503 --
2504 end cache_balances;
2505 --
2506 --
2507 -----------------------------------------------------------------------------
2508 -- NAME
2509 -- lock_batch
2510 -- PURPOSE
2511 -- Places an exclusive row lock on the batch header.
2512 -- ARGUMENTS
2513 -- p_mode - the mode the process is running in ie. PURGE, VALLIDATE
2514 -- or TRANSFER.
2515 -- p_batch_id - identifies the batch being processed.
2516 -- p_glbl_data_rec - global data structure.
2517 -- USES
2518 -- NOTES
2519 -----------------------------------------------------------------------------
2520 --
2521 procedure lock_batch
2522 (
2523 p_mode in varchar2
2524 ,p_batch_id in number
2525 ,p_glbl_data_rec in out nocopy glbl_data_rec_type
2526 ) is
2527 --
2528 -- Locks the batch.
2529 --
2530 cursor csr_lock_batch
2531 (
2532 p_batch_id number
2533 ) is
2534 select BBH.batch_id
2535 ,trunc(BBH.upload_date) upload_date
2536 from pay_balance_batch_headers BBH
2537 where BBH.batch_id = p_batch_id
2538 for update nowait;
2539 --
2540 -- Retrieves the commit unit size held as an action parameter.
2541 --
2542 cursor csr_chunk_size is
2543 select fnd_number.canonical_to_number(AP.parameter_value)
2544 from pay_action_parameters AP
2545 where AP.parameter_name = 'CHUNK_SIZE';
2546 --
2547 -- Holds the batch_id of the locked batch.
2548 --
2549 l_batch_rec csr_lock_batch%rowtype;
2550 --
2551 -- Holds the size of each commit unit NB. this is measured in assignments.
2552 --
2553 l_chunk_size number;
2554 --
2555 begin
2556 --
2557 hr_utility.trace('Entering pay_balance_upload.lock_batch');
2558 --
2559 -- Lock the batch.
2560 --
2561 open csr_lock_batch(p_batch_id);
2562 fetch csr_lock_batch into l_batch_rec;
2563 close csr_lock_batch;
2564 --
2565 -- Fetch the commit unit.
2566 --
2567 open csr_chunk_size;
2568 fetch csr_chunk_size into l_chunk_size;
2569 if csr_chunk_size%notfound then
2570 l_chunk_size := CHUNK_SIZE;
2571 end if;
2572 close csr_chunk_size;
2573 --
2574 -- Set batch_status of batch header to 'L' to lock it.
2578 set BBH.batch_status = 'L'
2575 -- This is used to stop other threads attempting to process this batch.
2576 --
2577 update pay_balance_batch_headers BBH
2579 where BBH.batch_id = p_batch_id;
2580 --
2581 -- Initialise the global data structure.
2582 --
2583 -- There is a 'special' mode that is set when calling balance upload
2584 -- from Purge. This simply sets a purge_mode to be FALSE to prevent
2585 -- the upload process from performing commits.
2586 -- Otherwise, commits are allowed.
2587 p_glbl_data_rec.upload_mode := upper(p_mode);
2588 p_glbl_data_rec.purge_mode := FALSE; -- Default to allowing commit.
2589 --
2590 if p_glbl_data_rec.upload_mode = 'BALANCE_ROLLUP' then
2591 p_glbl_data_rec.upload_mode := 'TRANSFER'; -- Now perform a transfer.
2592 p_glbl_data_rec.purge_mode := TRUE;
2593 end if;
2594 --
2595 p_glbl_data_rec.batch_id := l_batch_rec.batch_id;
2596 p_glbl_data_rec.upload_date := l_batch_rec.upload_date;
2597 p_glbl_data_rec.chunk_size := l_chunk_size;
2598 --
2599 hr_utility.trace('Exiting pay_balance_upload.lock_batch');
2600 --
2601 end lock_batch;
2602 --
2603 -----------------------------------------------------------------------------
2604 -- NAME
2605 -- set_batch_status
2606 -- PURPOSE
2607 -- Sets the batch status based on the status of the batch lines.
2608 -- ARGUMENTS
2609 -- p_glbl_data_rec - global data structure.
2610 -- USES
2611 -- NOTES
2612 -----------------------------------------------------------------------------
2613 --
2614 procedure set_batch_status
2615 (
2616 p_glbl_data_rec in glbl_data_rec_type
2617 ) is
2618 --
2619 -- Retrieves the statuses of the batch lines for a batch
2620 --
2621 cursor csr_status
2622 (
2623 p_batch_id number
2624 ) is
2625 select distinct nvl(BL.batch_line_status, 'U')
2626 from pay_balance_batch_lines BL
2627 where BL.batch_id = p_batch_id
2628 order by decode(nvl(BL.batch_line_status, 'U'), 'U', 1
2629 , 'T', 2
2630 , 'E', 3
2631 , 'V', 4);
2632 --
2633 -- The status of a batch line.
2634 --
2635 l_status varchar2(30);
2636 --
2637 -- Indicators identifying which statuses are used.
2638 --
2639 l_unprocessed boolean := FALSE;
2640 l_transferred boolean := FALSE;
2641 l_error boolean := FALSE;
2642 l_valid boolean := FALSE;
2643 --
2644 -- Indicates that only one status was used by all the batch lines.
2645 --
2646 l_one_status boolean := FALSE;
2647 --
2648 begin
2649 --
2650 hr_utility.trace('Entering pay_balance_upload.set_batch_status');
2651 --
2652 open csr_status(p_glbl_data_rec.batch_id);
2653 --
2654 -- Loop for all the differnent statuses for the batch lines within a natch.
2655 --
2656 loop
2657 --
2658 -- Get the next status.
2659 --
2660 fetch csr_status into l_status;
2661 exit when csr_status%notfound;
2662 --
2663 -- Set an indicator for each status found.
2664 --
2665 if l_status = 'U' then
2666 l_unprocessed := TRUE;
2667 elsif l_status = 'T' then
2668 l_transferred := TRUE;
2669 elsif l_status = 'E' then
2670 l_error := TRUE;
2671 elsif l_status = 'V' then
2672 l_valid := TRUE;
2673 end if;
2674 --
2675 end loop;
2676 --
2677 -- Was only one status found ?
2678 --
2679 l_one_status := (csr_status%rowcount = 1);
2680 --
2681 --
2682 -- Only one status for all the batch lines so the batch status is the same
2683 -- NB. l_status will contain the status selected from the cursor.
2684 --
2685 if l_one_status then
2686 null;
2687 --
2688 -- More than one batch status exists so need to derive the batch status.
2689 --
2690 elsif l_unprocessed then
2691 l_status := 'U';
2692 elsif l_transferred then
2693 l_status := 'P';
2694 elsif l_error then
2695 l_status := 'E';
2696 elsif l_valid then
2697 l_status := 'V';
2698 end if;
2699 --
2700 -- Set the status on the batch.
2701 --
2702 if csr_status%rowcount > 0 then
2703 update pay_balance_batch_headers BH
2704 set BH.batch_status = l_status
2705 where BH.batch_id = p_glbl_data_rec.batch_id;
2706 end if;
2707 close csr_status;
2708 --
2709 hr_utility.trace('Exiting pay_balance_upload.set_batch_status');
2710 --
2711 end set_batch_status;
2712 --
2713 -----------------------------------------------------------------------------
2714 -- NAME
2715 -- validate_batch_header
2716 -- PURPOSE
2717 -- Ensures that the batch header for a batch are valid NB. it also populates
2718 -- the system IDs where necessary.
2719 -- ARGUMENTS
2720 -- p_glbl_data_rec - global data structure.
2721 -- USES
2722 -- write_message_line
2723 -- NOTES
2724 -- Ensures that
2725 -- 1. the business group exists
2726 -- 2. the payroll exists
2730 --
2727 -- 3. the payroll has a reasonable number of time periods prior to the
2728 -- upload date. TBD
2729 -----------------------------------------------------------------------------
2731 procedure validate_batch_header
2732 (
2733 p_glbl_data_rec in out nocopy glbl_data_rec_type
2734 ) is
2735 --
2736 -- Retrieves the batch header.
2737 --
2738 cursor csr_batch_header
2739 (
2740 p_batch_id number
2741 ) is
2742 select *
2743 from pay_balance_batch_headers BBH
2744 where BBH.batch_id = p_batch_id
2745 for update;
2746 --
2747 -- Retrieves business group information NB. either the business_group_id or
2748 -- name may have been specified. If both are specified then the
2749 -- business_group_id overrides the name. A constraint on the batch headers
2750 -- table ensures that at least one of them is set.
2751 --
2752 cursor csr_business_group
2753 (
2754 p_business_group_id number
2755 ,p_name varchar2
2756 ,p_upload_date date
2757 ) is
2758 select BG.business_group_id
2759 ,BG.name
2760 ,BG.legislation_code
2761 from per_business_groups_perf BG
2762 where p_business_group_id is not null
2763 and BG.business_group_id = p_business_group_id
2764 union all
2765 select BG.business_group_id
2766 ,BG.name
2767 ,BG.legislation_code
2768 from per_business_groups_perf BG
2769 where p_business_group_id is null
2770 and upper(BG.name) = upper(p_name);
2771 --
2772 -- Retrieves payroll information NB. either the payroll_id or payroll_name
2773 -- may have been specified. If both are specified then the payroll_id
2774 -- overrides the payroll_name. A constraint on the batch headers table
2775 -- ensures that at least one of them is set.
2776 --
2777 cursor csr_payroll
2778 (
2779 p_business_group_id number
2780 ,p_payroll_id number
2781 ,p_payroll_name varchar2
2782 ,p_upload_date date
2783 ) is
2784 select PL.payroll_id
2785 ,PL.payroll_name
2786 ,PL.consolidation_set_id
2787 from pay_all_payrolls_f PL
2788 where p_payroll_id is not null
2789 and PL.business_group_id + 0 = p_business_group_id
2790 and PL.payroll_id = p_payroll_id
2791 and p_upload_date between PL.effective_start_date
2792 and PL.effective_end_date
2793 union all
2794 select PL.payroll_id
2795 ,PL.payroll_name
2796 ,PL.consolidation_set_id
2797 from pay_all_payrolls_f PL
2798 where p_payroll_id is null
2799 and PL.business_group_id + 0 = p_business_group_id
2800 and upper(PL.payroll_name) = upper(p_payroll_name)
2801 and p_upload_date between PL.effective_start_date
2802 and PL.effective_end_date;
2803 --
2804 -- Record to hold the batch header.
2805 --
2806 l_batch_header_rec csr_batch_header%rowtype;
2807 --
2808 -- Record to hold business group information.
2809 --
2810 l_bg_rec csr_business_group%rowtype;
2811 --
2812 -- Record to hold payroll information.
2813 --
2814 l_payroll_rec csr_payroll%rowtype;
2815 --
2816 -- Flags to hold the results of validation checks.
2817 --
2818 l_bg_invld boolean := FALSE;
2819 l_pyrl_invld boolean := FALSE;
2820 l_batchtyp_invld boolean := FALSE;
2821 l_found boolean;
2822 l_ctx_idx number;
2823 --
2824 begin
2825 --
2826 hr_utility.trace('Entering pay_balance_upload.validate_batch_header');
2827 --
2828 -- Retrieve the batch header.
2829 --
2830 open csr_batch_header(p_glbl_data_rec.batch_id);
2831 fetch csr_batch_header into l_batch_header_rec;
2832 --
2833 -- See if the batch type is for balance initialization.
2834 -- NOTE: If batch type is null, we regard this as initialization.
2835 --
2836 if nvl(l_batch_header_rec.batch_type, 'I') <> 'I' then
2837 l_batchtyp_invld := TRUE;
2838 end if;
2839 --
2840 -- See if the business group exists.
2841 --
2842 hr_utility.set_location('pay_balance_upload.validate_batch_header',10);
2843 open csr_business_group(l_batch_header_rec.business_group_id
2844 ,l_batch_header_rec.business_group_name
2845 ,p_glbl_data_rec.upload_date);
2846 fetch csr_business_group into l_bg_rec;
2847 if csr_business_group%notfound then
2848 l_bg_invld := TRUE;
2849 l_pyrl_invld := FALSE;
2850 else
2851 l_batch_header_rec.business_group_id := l_bg_rec.business_group_id;
2852 l_batch_header_rec.business_group_name := l_bg_rec.name;
2853 l_bg_invld := FALSE;
2854 end if;
2855 close csr_business_group;
2856 --
2857 -- See if the payroll exists.
2858 --
2859 hr_utility.set_location('pay_balance_upload.validate_batch_header',20);
2860 open csr_payroll(l_batch_header_rec.business_group_id
2861 ,l_batch_header_rec.payroll_id
2862 ,l_batch_header_rec.payroll_name
2863 ,p_glbl_data_rec.upload_date);
2864 fetch csr_payroll into l_payroll_rec;
2865 if csr_payroll%notfound then
2869 l_batch_header_rec.payroll_name := l_payroll_rec.payroll_name;
2866 l_pyrl_invld := TRUE;
2867 else
2868 l_batch_header_rec.payroll_id := l_payroll_rec.payroll_id;
2870 l_pyrl_invld := FALSE;
2871 end if;
2872 close csr_payroll;
2873 --
2874 -- Check each error flag and write out message for each failure against the
2875 -- batch header being validated.
2876 --
2877 -- Batch Type is not valid.
2878 --
2879 hr_utility.set_location('pay_balance_upload.validate_batch_header',25);
2880 if l_batchtyp_invld then
2881 write_message_line
2882 (p_meesage_level => HEADER
2883 ,p_batch_id => l_batch_header_rec.batch_id
2884 ,p_batch_line_id => null
2885 ,p_message_text => null
2886 ,p_message_token => 'PAY_50387_BI_INV_BATCH_TYPE');
2887 end if;
2888 --
2889 -- Business group is not valid.
2890 --
2891 hr_utility.set_location('pay_balance_upload.validate_batch_header',30);
2892 if l_bg_invld then
2893 write_message_line
2894 (p_meesage_level => HEADER
2895 ,p_batch_id => l_batch_header_rec.batch_id
2896 ,p_batch_line_id => null
2897 ,p_message_text => null
2898 ,p_message_token => 'HR_6673_PO_EMP_NO_BG');
2899 end if;
2900 --
2901 -- Payroll is not valid.
2902 --
2903 hr_utility.set_location('pay_balance_upload.validate_batch_header',40);
2904 if l_pyrl_invld then
2905 write_message_line
2906 (p_meesage_level => HEADER
2907 ,p_batch_id => l_batch_header_rec.batch_id
2908 ,p_batch_line_id => null
2909 ,p_message_text => null
2910 ,p_message_token => 'HR_51043_PRL_DOES_NOT_EXIST');
2911 end if;
2912 --
2913 -- At least one of the tests has failed so mark the batch header as invalid.
2914 --
2915 if l_batchtyp_invld or
2916 l_bg_invld or
2917 l_pyrl_invld then
2918 hr_utility.set_location('pay_balance_upload.validate_batch_header',50);
2919 l_batch_header_rec.batch_status := 'E'; -- Error
2920 --
2921 -- All tests have succeeded so mark the batch header as valid.
2922 --
2923 else
2924 hr_utility.set_location('pay_balance_upload.validate_batch_header',60);
2925 l_batch_header_rec.batch_status := 'V'; -- Valid
2926 end if;
2927 --
2928 -- Update the batch header with information retrieved during validation
2929 -- ie. if the payroll_name was set on a batch header then the payroll_id is
2930 -- derived.
2931 --
2932 -- Only set batch_status if have Error as in the case of Valid we
2933 -- we need the batch_status to remain L-ocked until end of upload
2934 -- for this batch.
2935 --
2936 if l_batch_header_rec.batch_status = 'E' then
2937 update pay_balance_batch_headers BBH
2938 set BBH.business_group_id = l_batch_header_rec.business_group_id
2939 ,BBH.business_group_name = l_batch_header_rec.business_group_name
2940 ,BBH.payroll_id = l_batch_header_rec.payroll_id
2941 ,BBH.payroll_name = l_batch_header_rec.payroll_name
2942 ,BBH.batch_status = l_batch_header_rec.batch_status
2943 where current of csr_batch_header;
2944 else
2945 update pay_balance_batch_headers BBH
2946 set BBH.business_group_id = l_batch_header_rec.business_group_id
2947 ,BBH.business_group_name = l_batch_header_rec.business_group_name
2948 ,BBH.payroll_id = l_batch_header_rec.payroll_id
2949 ,BBH.payroll_name = l_batch_header_rec.payroll_name
2950 where current of csr_batch_header;
2951 end if;
2952 --
2953 close csr_batch_header;
2954 --
2955 -- Ensure that the global data structure has the current information.
2956 --
2957 p_glbl_data_rec.business_group_id := l_batch_header_rec.business_group_id;
2958 p_glbl_data_rec.legislation_code := l_bg_rec.legislation_code;
2959 p_glbl_data_rec.payroll_id := l_batch_header_rec.payroll_id;
2960 p_glbl_data_rec.consolidation_set_id := l_payroll_rec.consolidation_set_id;
2961 p_glbl_data_rec.batch_header_status := l_batch_header_rec.batch_status;
2962 --
2963 if p_glbl_data_rec.batch_header_status = 'V' then
2964 --
2965 -- Get the legislation contexts
2966 --
2967 pay_core_utils.get_dynamic_contexts(p_glbl_data_rec.business_group_id,
2968 g_legislation_contexts);
2969
2970 -- Get the jurisdiction iv name.
2971 for l_ctx_idx in 1..g_legislation_contexts.count loop
2972
2973 if (g_legislation_contexts(l_ctx_idx).context_name
2974 = 'JURISDICTION_CODE') then
2975 --
2976 p_glbl_data_rec.jurisdiction_iv
2977 := g_legislation_contexts(l_ctx_idx).input_value_name;
2978 end if;
2979
2980 end loop;
2981 --
2982 -- Get legislation rule BAL_INIT_INCLUDE_ADJ.
2983 --
2984 -- This rule is used to determine whether to use include_adjustment
2985 -- in calculating sum of adjusted value for a balance.
2986 --
2987 -- Rule mode = Y - Call localization include_adjustment.
2988 -- Rule mode = N - Use a generic routine.
2989 -- No rule mode - Call localization include_adjustment.
2990 --
2994 ,l_found);
2991 pay_core_utils.get_legislation_rule('BAL_INIT_INCLUDE_ADJ'
2992 ,p_glbl_data_rec.legislation_code
2993 ,p_glbl_data_rec.include_adj_rule
2995 if (l_found = FALSE) then
2996 p_glbl_data_rec.include_adj_rule := 'Y';
2997 end if;
2998 --
2999 end if;
3000 --
3001 hr_utility.trace('Exiting pay_balance_upload.validate_batch_header');
3002 --
3003 end validate_batch_header;
3004 --
3005 -----------------------------------------------------------------------------
3006 -- NAME
3007 -- which_contexts
3008 -- PURPOSE
3009 -- Sets up flags indicating the contexts used by the balance dimension.
3010 -- ARGUMENTS
3011 -- p_dim_vald_rec
3012 -- The context indicators are set to true if the balance dimension uses that
3013 -- context :-
3014 -- jc_cntxt - JURISDICTION_CODE
3015 -- gre_cntxt - TAX_UNIT_ID
3016 -- oee_cntxt - ORIGINAL_ENTRY_ID
3017 -- srcid_cntxt - SOURCE_ID
3018 -- srctxt_cntxt- SOURCE_TEXT
3019 -- sn_cntxt - SOURCE_NUMBER
3020 -- st2_cntxt - SOURCE_TEXT2
3021 -- other_cntxt - any other contexts except ASSIGNMENT_ACTION_ID which is
3022 -- common to all dimensions.
3023 -- USES
3024 -- NOTES
3025 -----------------------------------------------------------------------------
3026 --
3027 procedure which_contexts
3028 (
3029 p_dim_vald_rec in out nocopy t_dimension_validation_rec
3030 ) is
3031 --
3032 -- Retrieves all the contexts used by a balance dimension.
3033 --
3034 cursor csr_context
3035 (
3036 p_balance_dimension_id number
3037 ) is
3038 select CO.context_name,
3039 BD.database_item_suffix,
3040 BD.legislation_code
3041 from pay_balance_dimensions BD
3042 ,ff_route_context_usages CU
3043 ,ff_contexts CO
3044 where BD.balance_dimension_id = p_balance_dimension_id
3045 and CU.route_id = BD.route_id
3046 and CO.context_id = CU.context_id;
3047 --
3048 l_dim_rec t_dimension_validation_rec:= p_dim_vald_rec;
3049 --
3050 begin
3051 --
3052 hr_utility.trace('Entering pay_balance_upload.which_contexts');
3053 --
3054 -- Indicator variables showing which contexts a balance dimension has.
3055 --
3056 l_dim_rec.jc_cntxt := FALSE;
3057 l_dim_rec.gre_cntxt := FALSE;
3058 l_dim_rec.oee_cntxt := FALSE;
3059 l_dim_rec.other_cntxt := FALSE;
3060 l_dim_rec.srcid_cntxt := FALSE;
3061 l_dim_rec.srctxt_cntxt := FALSE;
3062 l_dim_rec.runtyp_cntxt := FALSE;
3063 --
3064 -- Retrieve all the contexts for the balance dimension.
3065 --
3066 for l_cntxt_rec in csr_context(l_dim_rec.balance_dimension_id) loop
3067 --
3068 -- Set the appropriate indicator depending on the context NB. the
3069 -- ASSIGNMENT_ACTION_ID context is common to all balance dimensions as is
3070 -- therefore ignored.
3071 --
3072 if l_cntxt_rec.context_name = 'JURISDICTION_CODE' then
3073 l_dim_rec.jc_cntxt := TRUE;
3074 elsif l_cntxt_rec.context_name = 'TAX_UNIT_ID' then
3075 l_dim_rec.gre_cntxt := TRUE;
3076 elsif l_cntxt_rec.context_name = 'ORIGINAL_ENTRY_ID' then
3077 l_dim_rec.oee_cntxt := TRUE;
3078 elsif l_cntxt_rec.context_name = 'SOURCE_ID' then
3079 l_dim_rec.srcid_cntxt := TRUE;
3080 elsif l_cntxt_rec.context_name = 'SOURCE_TEXT' then
3081 l_dim_rec.srctxt_cntxt := TRUE;
3082 elsif l_cntxt_rec.context_name = 'SOURCE_NUMBER' then
3083 l_dim_rec.sn_cntxt := TRUE;
3084 elsif l_cntxt_rec.context_name = 'SOURCE_TEXT2' then
3085 l_dim_rec.st2_cntxt := TRUE;
3086 elsif l_cntxt_rec.context_name = 'ASSIGNMENT_ACTION_ID' then
3087 null;
3088 else
3089 l_dim_rec.other_cntxt := TRUE;
3090 end if;
3091 --
3092 -- NBR.Hardcoded temporary change for Korean.
3093 if (l_cntxt_rec.legislation_code = 'KR'
3094 and (l_cntxt_rec.database_item_suffix like '%_BON'
3095 or l_cntxt_rec.database_item_suffix like '%_MTH')) then
3096 l_dim_rec.runtyp_cntxt := TRUE;
3097 end if;
3098 --
3099 end loop;
3100 --
3101 -- Set the indicator flags.
3102 --
3103 p_dim_vald_rec := l_dim_rec;
3104 --
3105 hr_utility.trace('Exiting pay_balance_upload.which_contexts');
3106 --
3107 end which_contexts;
3108 --
3109 -----------------------------------------------------------------------------
3110 -- NAME
3111 -- ins_latest_balance
3112 -- PURPOSE
3113 -- This inserts the latest balance and the context the balances requires
3114 -- onto the database.
3115 -- ARGUMENTS
3116 -- p_bal_type_id - the balance type of the latest balance.
3117 -- p_bal_dimension_id - the dimension of the latest balance.
3118 -- p_value - the value of the balance.
3119 -- p_assignment_id - the assignment the balance is for.
3120 -- p_asg_act_id - the assignment action that last effected the
3121 -- balance.
3125 -- p_source_text - the source text context.
3122 -- p_tax_unit_id - the tax unit context.
3123 -- p_jurisdiction_code - the jurisdiction context.
3124 -- p_source_id - the source id context.
3126 -- p_source_number - the source number context.
3127 -- p_source_text2 - the source text2 context.
3128 -- p_oee_id - the original entry context.
3129 -- USES
3130 -- NOTES
3131 -----------------------------------------------------------------------------
3132 --
3133 procedure ins_latest_balance
3134 (
3135 p_bal_type_id in pay_balance_batch_lines.balance_type_id%TYPE,
3136 p_bal_dimension_id in pay_balance_batch_lines.balance_dimension_id%TYPE,
3137 p_value in pay_balance_batch_lines.value%TYPE,
3138 p_assignment_id in pay_balance_batch_lines.assignment_id%TYPE,
3139 p_asg_act_id in pay_assignment_actions.assignment_action_id%TYPE,
3140 p_tax_unit_id in number,
3141 p_jurisdiction_code in pay_balance_batch_lines.jurisdiction_code%TYPE
3142 default NULL,
3143 p_source_id in number,
3144 p_source_text in varchar2,
3145 p_source_number in number,
3146 p_source_text2 in varchar2,
3147 p_run_type_id in number,
3148 p_oee_id in pay_balance_batch_lines.original_entry_id%TYPE
3149 default NULL
3150 ) is
3151 --
3152 cursor csr_get_def_bal (p_bal_type_id in number,
3153 p_bal_dimension_id in number)
3154 is
3155 select defined_balance_id
3156 from pay_defined_balances
3157 where balance_type_id = p_bal_type_id
3158 and balance_dimension_id = p_bal_dimension_id;
3159 --
3160 cursor csr_get_context_id (p_cxt_name in varchar)
3161 is
3162 select context_id
3163 from ff_contexts
3164 where context_name = p_cxt_name;
3165 --
3166 --
3167 l_defined_bal number;
3168 l_lat_bal_id number;
3169 l_bus_grp_id number;
3170 l_person_id number;
3171 l_status varchar2(30);
3172 ctx_id number;
3173 begin
3174 --
3175 hr_utility.trace('Entering pay_balance_upload.ins_latest_balance');
3176 open csr_get_def_bal (p_bal_type_id,
3177 p_bal_dimension_id);
3178 fetch csr_get_def_bal into l_defined_bal;
3179 close csr_get_def_bal;
3180 --
3181 hr_utility.set_location('pay_balance_upload.ins_latest_balance',10);
3182 select pay_latest_balances_s.nextval
3183 into l_lat_bal_id
3184 from sys.dual;
3185 --
3186 -- get bus grp id
3187 select distinct person_id,business_group_id
3188 into l_person_id,l_bus_grp_id
3189 from per_all_assignments_f
3190 where assignment_id = p_assignment_id;
3191
3192 pay_core_utils.get_upgrade_status(p_bus_grp_id=> l_bus_grp_id,
3193 p_short_name=> 'SINGLE_BAL_TABLE',
3194 p_status=>l_status);
3195
3196 if (l_status='Y')
3197 then
3198 hr_utility.trace('latest balances table');
3199 insert into pay_latest_balances
3200 (latest_balance_id,
3201 assignment_id,
3202 defined_balance_id,
3203 assignment_action_id,
3204 value,
3205 person_id,
3206 expired_assignment_action_id,
3207 expired_value,
3208 prev_assignment_action_id,
3209 prev_balance_value,
3210 tax_unit_id,
3211 jurisdiction_code,
3212 original_entry_id,
3213 source_id,
3214 source_text,
3215 source_number,
3216 source_text2
3217 )
3218 values (l_lat_bal_id,
3219 p_assignment_id,
3220 l_defined_bal,
3221 p_asg_act_id,
3222 p_value,
3223 l_person_id,
3224 -9999,
3225 -9999,
3226 -9999,
3227 -9999,
3228 p_tax_unit_id,
3229 p_jurisdiction_code,
3230 p_oee_id,
3231 p_source_id,
3232 p_source_text,
3233 p_source_number,
3234 p_source_text2
3235 );
3236 else
3237 insert into pay_assignment_latest_balances
3238 (latest_balance_id,
3239 assignment_id,
3240 defined_balance_id,
3241 assignment_action_id,
3242 value,
3243 expired_assignment_action_id,
3244 expired_value,
3245 prev_assignment_action_id,
3246 prev_balance_value)
3247 values (l_lat_bal_id,
3248 p_assignment_id,
3249 l_defined_bal,
3250 p_asg_act_id,
3251 p_value,
3252 -9999,
3253 -9999,
3254 -9999,
3255 -9999);
3256 --
3257 if p_tax_unit_id is not null then
3258 --
3259 hr_utility.set_location('pay_balance_upload.ins_latest_balance',20);
3260 open csr_get_context_id('TAX_UNIT_ID');
3261 fetch csr_get_context_id into ctx_id;
3265 (latest_balance_id,
3262 close csr_get_context_id;
3263 --
3264 insert into pay_balance_context_values
3266 context_id,
3267 value)
3268 values (l_lat_bal_id,
3269 ctx_id,
3270 p_tax_unit_id);
3271 end if;
3272 --
3273 if p_jurisdiction_code is not null then
3274 --
3275 hr_utility.set_location('pay_balance_upload.ins_latest_balance',30);
3276 open csr_get_context_id('JURISDICTION_CODE');
3277 fetch csr_get_context_id into ctx_id;
3278 close csr_get_context_id;
3279 --
3280 insert into pay_balance_context_values
3281 (latest_balance_id,
3282 context_id,
3283 value)
3284 values (l_lat_bal_id,
3285 ctx_id,
3286 p_jurisdiction_code);
3287 end if;
3288 if p_oee_id is not null then
3289 --
3290 hr_utility.set_location('pay_balance_upload.ins_latest_balance',40);
3291 open csr_get_context_id('ORIGINAL_ENTRY_ID');
3292 fetch csr_get_context_id into ctx_id;
3293 close csr_get_context_id;
3294 --
3295 insert into pay_balance_context_values
3296 (latest_balance_id,
3297 context_id,
3298 value)
3299 values (l_lat_bal_id,
3300 ctx_id,
3301 p_oee_id);
3302 end if;
3303 --
3304 --
3305 if p_source_id is not null then
3306 --
3307 hr_utility.set_location('pay_balance_upload.ins_latest_balance',50);
3308 open csr_get_context_id('SOURCE_ID');
3309 fetch csr_get_context_id into ctx_id;
3310 close csr_get_context_id;
3311 --
3312 insert into pay_balance_context_values
3313 (latest_balance_id,
3314 context_id,
3315 value)
3316 values (l_lat_bal_id,
3317 ctx_id,
3318 p_source_id);
3319 end if;
3320 --
3321 if p_source_text is not null then
3322 --
3323 hr_utility.set_location('pay_balance_upload.ins_latest_balance',50);
3324 open csr_get_context_id('SOURCE_TEXT');
3325 fetch csr_get_context_id into ctx_id;
3326 close csr_get_context_id;
3327 --
3328 insert into pay_balance_context_values
3329 (latest_balance_id,
3330 context_id,
3331 value)
3332 values (l_lat_bal_id,
3333 ctx_id,
3334 p_source_text);
3335 end if;
3336 --
3337 if p_source_number is not null then
3338 --
3339 hr_utility.set_location('pay_balance_upload.ins_latest_balance',60);
3340 open csr_get_context_id('SOURCE_NUMBER');
3341 fetch csr_get_context_id into ctx_id;
3342 close csr_get_context_id;
3343 --
3344 insert into pay_balance_context_values
3345 (latest_balance_id,
3346 context_id,
3347 value)
3348 values (l_lat_bal_id,
3349 ctx_id,
3350 p_source_number);
3351 end if;
3352 --
3353 if p_source_text2 is not null then
3354 --
3355 hr_utility.set_location('pay_balance_upload.ins_latest_balance',70);
3356 open csr_get_context_id('SOURCE_TEXT2');
3357 fetch csr_get_context_id into ctx_id;
3358 close csr_get_context_id;
3359 --
3360 insert into pay_balance_context_values
3361 (latest_balance_id,
3362 context_id,
3363 value)
3364 values (l_lat_bal_id,
3365 ctx_id,
3366 p_source_text2);
3367 end if;
3368 --
3369 end if;
3370 hr_utility.trace('Exiting pay_balance_upload.ins_latest_balance');
3371 end ins_latest_balance;
3372 --
3373 -----------------------------------------------------------------------------
3374 -- NAME
3375 -- load_latest_asg_balances
3376 -- PURPOSE
3377 -- This creates all the latest balances for an assignment.
3378 -- ARGUMENTS
3379 -- p_glbl_data_rec - global data structure.
3380 -- p_batch_line_list - the list of batch lines currently being processed
3381 -- p_num_lines - the number of batch lines in the list.
3382 -- USES
3383 -- ins_latest_balance
3384 -- NOTES
3385 -----------------------------------------------------------------------------
3386 --
3387 procedure load_latest_asg_balances
3388 (
3389 p_glbl_data_rec in glbl_data_rec_type,
3390 p_batch_line_list in out nocopy number_array,
3391 p_num_lines in out nocopy number
3392 ) is
3393 --
3394 --
3395 cursor csr_get_asg_act (p_act_id in number,
3396 p_asg_id in number)
3397 is
3398 select paa.assignment_action_id
3399 from pay_assignment_actions paa
3400 where paa.assignment_id = p_asg_id
3401 and paa.payroll_action_id = p_act_id;
3402 --
3403 --
3404 cursor csr_latest_asg_balances (p_batch_id number,
3405 p_assignment_id number)
3406 is
3407 select pbl.balance_type_id,
3408 pbl.balance_name,
3409 pbl.dimension_name,
3413 pbl.tax_unit_id,
3410 pbl.balance_dimension_id,
3411 pbl.batch_line_id,
3412 pbl.gre_name,
3414 pbl.jurisdiction_code,
3415 pbl.original_entry_id,
3416 pbl.source_id,
3417 pbl.source_text,
3418 pbl.source_number,
3419 pbl.source_text2,
3420 pbl.run_type_id,
3421 pbl.run_type_name,
3422 pbl.payroll_action_id,
3423 pbl.value
3424 from
3425 pay_balance_dimensions pbd,
3426 pay_balance_batch_lines pbl
3427 where pbl.batch_id = p_batch_id
3428 and pbl.assignment_id = p_assignment_id
3429 and pbl.balance_dimension_id = pbd.balance_dimension_id
3430 and pbl.upload_date is null -- Don't consider historical loads
3431 and pbd.dimension_type in ('A', 'P')
3432 order by pbl.batch_line_status;
3433 --
3434 -- Record to hold a batch line NB. the cursor is defined at package level.
3435 --
3436 l_lat_asg_balance csr_latest_asg_balances%rowtype;
3437 l_asg_act_id number;
3438 l_max_act_seq number;
3439 --
3440 begin
3441 --
3442 hr_utility.trace('Entering pay_balance_upload.load_latest_asg_balances');
3443 --
3444 open csr_latest_asg_balances(p_glbl_data_rec.batch_id
3445 ,p_glbl_data_rec.assignment_id);
3446 --
3447 loop
3448 --
3449 -- Get the next batch line for the assignment.
3450 --
3451 fetch csr_latest_asg_balances into l_lat_asg_balance;
3452 --
3453 exit when (csr_latest_asg_balances%notfound);
3454 --
3455 -- Store the details of linein case of error
3456 --
3457 p_num_lines := 1;
3458 p_batch_line_list(p_num_lines) := l_lat_asg_balance.batch_line_id;
3459 hr_utility.trace(p_num_lines||' '||p_batch_line_list(p_num_lines));
3460 --
3461 if (l_lat_asg_balance.value <> 0) then
3462 hr_utility.set_location('pay_balance_upload.load_latest_asg_balances',
3463 10);
3464 select /*+ ORDERED
3465 USE_NL(pbl ppa pbf paa rr rrv)
3466 INDEX(pbl PAY_BALANCE_BATCH_LINES_N51)
3467 INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
3468 INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
3469 INDEX(rr PAY_RUN_RESULTS_N50)
3470 INDEX(rrv PAY_RUN_RESULT_VALUES_N50)
3471 INDEX(pbf PAY_BALANCE_FEEDS_F_N2) */
3472 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3473 paa.assignment_action_id),16))
3474 into l_asg_act_id
3475 from
3476 pay_balance_batch_lines pbl,
3477 pay_payroll_actions ppa,
3478 pay_assignment_actions paa,
3479 pay_run_results rr,
3480 pay_run_result_values rrv,
3481 pay_balance_feeds_f pbf
3482 where pbl.batch_id = p_glbl_data_rec.batch_id
3483 and pbl.assignment_id = p_glbl_data_rec.assignment_id
3484 and pbl.balance_type_id = l_lat_asg_balance.balance_type_id
3485 and pbl.payroll_action_id = ppa.payroll_action_id
3486 and pbl.balance_type_id = pbf.balance_type_id + 0
3487 and ppa.effective_date between
3488 pbf.effective_start_date and pbf.effective_end_date
3489 and paa.payroll_action_id = ppa.payroll_action_id
3490 and paa.assignment_id = pbl.assignment_id
3491 and paa.assignment_action_id = rr.assignment_action_id
3492 and rr.run_result_id = rrv.run_result_id
3493 and rrv.input_value_id = pbf.input_value_id
3494 and nvl(rrv.result_value, '0') <> '0'
3495 and upper(nvl(nvl(pbl.gre_name, l_lat_asg_balance.gre_name),'-1')) =
3496 upper(nvl(nvl(l_lat_asg_balance.gre_name,
3497 pbl.gre_name), '-1'))
3498 and upper(nvl(nvl(pbl.run_type_name, l_lat_asg_balance.run_type_name),'-1')) =
3499 upper(nvl(nvl(l_lat_asg_balance.run_type_name,
3500 pbl.run_type_name), '-1'))
3501 and nvl(nvl(pbl.jurisdiction_code,
3502 l_lat_asg_balance.jurisdiction_code), -1) =
3503 nvl(nvl(l_lat_asg_balance.jurisdiction_code,
3504 pbl.jurisdiction_code), -1)
3505 and upper(nvl(nvl(pbl.source_text, l_lat_asg_balance.source_text),'-1')) =
3506 upper(nvl(nvl(l_lat_asg_balance.source_text, pbl.source_text), '-1'))
3507 and nvl(nvl(pbl.source_id, l_lat_asg_balance.source_id), -1) =
3508 nvl(nvl(l_lat_asg_balance.source_id, pbl.source_id), -1)
3509 and upper(nvl(nvl(pbl.source_text2, l_lat_asg_balance.source_text2),'-1')) =
3510 upper(nvl(nvl(l_lat_asg_balance.source_text2, pbl.source_text2), '-1'))
3511 and nvl(nvl(pbl.source_number, l_lat_asg_balance.source_number), -1) =
3512 nvl(nvl(l_lat_asg_balance.source_number, pbl.source_number), -1)
3513 and nvl(nvl(pbl.tax_unit_id, l_lat_asg_balance.tax_unit_id), -1) =
3514 nvl(nvl(l_lat_asg_balance.tax_unit_id, pbl.tax_unit_id), -1)
3515 and nvl(nvl(pbl.run_type_id, l_lat_asg_balance.run_type_id), -1) =
3516 nvl(nvl(l_lat_asg_balance.run_type_id, pbl.run_type_id), -1)
3517 and nvl(nvl(pbl.original_entry_id,
3518 l_lat_asg_balance.original_entry_id), -1) =
3519 nvl(nvl(l_lat_asg_balance.original_entry_id,
3523 20);
3520 pbl.original_entry_id), -1);
3521 else
3522 hr_utility.set_location('pay_balance_upload.load_latest_asg_balances',
3524 --
3525 open csr_get_asg_act (l_lat_asg_balance.payroll_action_id,
3526 p_glbl_data_rec.assignment_id);
3527 fetch csr_get_asg_act into l_asg_act_id;
3528 --
3529 if csr_get_asg_act%notfound then
3530 close csr_get_asg_act;
3531 raise no_data_found;
3532 end if;
3533 --
3534 close csr_get_asg_act;
3535 --
3536 end if;
3537 --
3538 hr_utility.set_location('pay_balance_upload.load_latest_asg_balances', 30);
3539 ins_latest_balance(l_lat_asg_balance.balance_type_id,
3540 l_lat_asg_balance.balance_dimension_id,
3541 l_lat_asg_balance.value,
3542 p_glbl_data_rec.assignment_id,
3543 l_asg_act_id,
3544 l_lat_asg_balance.tax_unit_id,
3545 l_lat_asg_balance.jurisdiction_code,
3546 l_lat_asg_balance.source_id,
3547 l_lat_asg_balance.source_text,
3548 l_lat_asg_balance.source_number,
3549 l_lat_asg_balance.source_text2,
3550 l_lat_asg_balance.run_type_id,
3551 l_lat_asg_balance.original_entry_id);
3552 --
3553 end loop;
3554 --
3555 close csr_latest_asg_balances;
3556 --
3557 p_num_lines := 0;
3558 --
3559 hr_utility.trace('Exiting pay_balance_upload.load_latest_asg_balances');
3560 --
3561 end load_latest_asg_balances;
3562 --
3563 -----------------------------------------------------------------------------
3564 -- NAME
3565 -- validate_dimension
3566 -- PURPOSE
3567 -- Ensures that the balance dimension on the batch line is valid.
3568 -- ARGUMENTS
3569 -- p_glbl_data_rec - global data structure.
3570 -- p_batch_line_rec - the current batch line.
3571 -- p_bal_vald_rec - Balance validation details.
3572 -- USES
3573 -- which_contexts
3574 -- write_message_line
3575 -- NOTES
3576 -- Ensures that
3577 -- 1. the balance dimension exists
3578 -- 2. the balance dimension is one of the supported dimensions
3579 -- 3. the JURISDICTION_CODE context is set when required.
3580 -- 4. the TAX_UNIT_ID context is set when required.
3581 -- 5. the TAX_UNIT_ID is valid.
3582 -- 6. the ORIGINAL_ENTRY_ID context is set when required.
3583 -- 7. the ORIGINAL_ENTRY_ID is valid.
3584 -- 8. the SOURCE_ID context is set when required.
3585 -- 9. the SOURCE_ID is valid.
3586 -- 10.the SOURCE_TEXT context is set when required.
3587 -- 11.the SOURCE_TEXT is valid.
3588 -- 12.the SOURCE_NUMBER context is set when required.
3589 -- 13.the SOURCE_NUMBER input value exists when required.
3590 -- 14.the SOURCE_TEXT2 context is set when required.
3591 -- 15.the SOURCE_TEXT2 input value exists when required.
3592 -----------------------------------------------------------------------------
3593 --
3594 procedure validate_dimension
3595 (
3596 p_glbl_data_rec in glbl_data_rec_type
3597 ,p_batch_line_rec in out nocopy csr_batch_line_validate%rowtype
3598 ,p_bal_vald_rec in t_balance_validation_rec
3599 ) is
3600 --
3601 -- Retrieves dimension information NB. either the balance_dimension_id or
3602 -- dimension_name may have been specified. If both are specified then
3603 -- the balance_dimension_id overrides the dimension_name. A constraint on
3604 -- the batch lines table ensures that at least one of them is set.
3605 --
3606 cursor csr_dimension
3607 (
3608 p_business_group_id number
3609 ,p_legislation_code varchar2
3610 ,p_balance_dimension_id number
3611 ,p_dimension_name varchar2
3612 ) is
3613 select BD.balance_dimension_id
3614 ,upper(BD.dimension_name) dimension_name
3615 from pay_balance_dimensions BD
3616 where p_balance_dimension_id is not null
3617 and BD.balance_dimension_id = p_balance_dimension_id
3618 and nvl(BD.business_group_id, nvl(p_business_group_id, -1)) =
3619 nvl(p_business_group_id, -1)
3620 and nvl(BD.legislation_code, nvl(p_legislation_code, ' ')) =
3621 nvl(p_legislation_code, ' ')
3622 union all
3623 select BD.balance_dimension_id
3624 ,upper(BD.dimension_name) dimension_name
3625 from pay_balance_dimensions BD
3626 where p_balance_dimension_id is null
3627 and upper(BD.dimension_name) = upper(p_dimension_name)
3628 and nvl(BD.business_group_id, nvl(p_business_group_id, -1)) =
3629 nvl(p_business_group_id, -1)
3630 and nvl(BD.legislation_code, nvl(p_legislation_code, ' ')) =
3631 nvl(p_legislation_code, ' ');
3632 --
3633 -- This cursor is used to check a defined balance exists.
3634 --
3635 cursor csr_get_defined_balance
3636 (
3637 p_dimension_id number
3638 ,p_balance_type_id number
3639 ) is
3640 select defined_balance_id
3641 from pay_defined_balances
3642 where balance_type_id = p_balance_type_id
3646 --
3643 and balance_dimension_id = p_dimension_id;
3644 --
3645 -- Retrieves element entry information.
3647 cursor csr_entry
3648 (
3649 p_upload_date date
3650 ,p_assignment_id number
3651 ,p_original_entry_id number
3652 ) is
3653 select EE.element_entry_id
3654 from pay_element_entries_f EE
3655 where EE.assignment_id = p_assignment_id
3656 and EE.entry_type = 'E'
3657 and EE.effective_start_date <= p_upload_date
3658 and EE.element_entry_id = p_original_entry_id
3659 and EE.original_entry_id is null
3660 union all
3661 select EE.element_entry_id
3662 from pay_element_entries_f EE
3663 where EE.assignment_id = p_assignment_id
3664 and EE.entry_type = 'E'
3665 and EE.effective_start_date <= p_upload_date
3666 and EE.original_entry_id = p_original_entry_id;
3667 --
3668 -- Record to hold dimension information.
3669 --
3670 l_dimension_rec csr_dimension%rowtype;
3671 --
3672 -- Record to hold tax unit information.
3673 --
3674 --
3675 -- Record for the defined balances.
3676 --
3677 l_def_bal_rec csr_get_defined_balance%rowtype;
3678 --
3679 -- holds the element entry an assignment has.
3680 --
3681 l_ele_entry_id number;
3682 --
3683 -- Variables to cache details of the current dimension being validated.
3684 --
3685 l_dim_rec t_dimension_validation_rec;
3686 --
3687 l_dim_is_supported varchar2(5);
3688 --
3689 l_jc_cntxt_not_set boolean := FALSE;
3690 l_gre_cntxt_not_set boolean := FALSE;
3691 l_gre_cntxt_invld boolean := FALSE;
3692 l_oee_cntxt_not_set boolean := FALSE;
3693 l_oee_cntxt_invld boolean := FALSE;
3694 l_srcid_cntxt_nset boolean := FALSE;
3695 l_srctxt_cntxt_nset boolean := FALSE;
3696 l_sn_cntxt_nset boolean := FALSE;
3697 l_st2_cntxt_nset boolean := FALSE;
3698 l_srcid_cntxt_noiv boolean := FALSE;
3699 l_srctxt_cntxt_noiv boolean := FALSE;
3700 l_sn_cntxt_noiv boolean := FALSE;
3701 l_st2_cntxt_noiv boolean := FALSE;
3702 l_def_bal_not_fnd boolean := FALSE;
3703 l_runtyp_cntxt_not_set boolean := FALSE;
3704 l_runtyp_cntxt_invld boolean := FALSE;
3705 --
3706 -- Variables used to control the search through list of previously
3707 -- validated balances.
3708 --
3709 l_index number := 0;
3710 l_dim_index number;
3711 l_dimension_found boolean := FALSE;
3712 --
3713 l_msgs varchar2_array;
3714 l_msg_idx number;
3715 --
3716 l_iv_start_ptr number;
3717 l_iv_end_ptr number;
3718 l_iv_idx number;
3719 l_context ff_contexts.context_name%type;
3720 --
3721 begin
3722 --
3723 hr_utility.trace('Entering pay_balance_upload.validate_dimension');
3724 --
3725 -- Search through list of dimensions that have already been validated NB
3726 -- the list of dimensions is held in a PLSQL table.
3727 --
3728 for l_index in 1..g_dim_vald.count loop
3729 --
3730 -- See if the dimension is in the list NB. the balance_dimension_id
3731 -- overrides the dimension_name. If the dimension is found then the flag
3732 -- is set and the search index points to the matching entry.
3733 --
3734 if ((p_batch_line_rec.balance_dimension_id
3735 = g_dim_vald(l_index).balance_dimension_id) or
3736 (p_batch_line_rec.balance_dimension_id is null and
3737 upper(p_batch_line_rec.dimension_name)
3738 = g_dim_vald(l_index).dimension_name)) then
3739 l_dimension_found := TRUE;
3740 l_dim_index := l_index;
3741 exit;
3742 end if;
3743 --
3744 end loop;
3745 --
3746 -- Dimension has already been validated.
3747 --
3748 if l_dimension_found then
3749 --
3750 -- Values must be the same as those for the dimension when it was
3751 -- validated.
3752 --
3753 l_dim_rec := g_dim_vald(l_dim_index);
3754
3755 --
3756 -- Dimension is new ie. has not been found already. The dimension must be
3757 -- validated and added to the list of validated dimensions.
3758 --
3759 else
3760 --
3761 -- See if the dimension exists. If it does not then cache the old values
3762 -- else cache the new values (for subsequent checks) NB. a dimension
3763 -- that does not exist cannot be validated any further.
3764 --
3765 open csr_dimension(p_glbl_data_rec.business_group_id
3766 ,p_glbl_data_rec.legislation_code
3767 ,p_batch_line_rec.balance_dimension_id
3768 ,p_batch_line_rec.dimension_name);
3769 fetch csr_dimension into l_dimension_rec;
3770 if csr_dimension%notfound then
3771 l_dim_rec.balance_dimension_id:= p_batch_line_rec.balance_dimension_id;
3772 l_dim_rec.dimension_name := p_batch_line_rec.dimension_name;
3773 l_dim_rec.invld := TRUE;
3774 else
3775 l_dim_rec.balance_dimension_id:= l_dimension_rec.balance_dimension_id;
3779 -- Find out which contexts the balance dimension uses.
3776 l_dim_rec.dimension_name := l_dimension_rec.dimension_name;
3777 l_dim_rec.invld := FALSE;
3778 --
3780 --
3781 which_contexts(l_dim_rec);
3782 --
3783 end if;
3784 close csr_dimension;
3785 --
3786 -- Balance dimension exists so continue validation. This validation
3787 -- only has to be done once for each new balance dimension eg. is the
3788 -- dimension one of the supported dimensions.
3789 --
3790 if not l_dim_rec.invld then
3791 --
3792 -- Ensure that the dimension is supported.
3793 --
3794 l_dim_is_supported := dim_is_supported
3795 (p_glbl_data_rec.legislation_code
3796 ,l_dim_rec.dimension_name
3797 );
3798 --
3799 if l_dim_is_supported = 'Y' then
3800 l_dim_rec.not_supp := FALSE;
3801 else
3802 l_dim_rec.not_supp := TRUE;
3803 end if;
3804 --
3805 end if;
3806 --
3807 -- A new dimension has been found so add it to the list of validated
3808 -- dimensions.
3809 --
3810 if not (l_dim_rec.balance_dimension_id is null and
3811 l_dim_rec.dimension_name is null) then
3812
3813 l_dim_index := g_dim_vald.count+1;
3814 g_dim_vald(l_dim_index) := l_dim_rec;
3815
3816 end if;
3817 --
3818 end if;
3819 --
3820 -- Balance dimension exists and is supported so continue validation NB.
3821 -- this validation has to be done for each new batch line even if the
3822 -- balance dimension has already been validated. This validation ensures
3823 -- that the relevant contexts have been set and are valid.
3824 --
3825 if not l_dim_rec.invld and not l_dim_rec.not_supp then
3826 --
3827 -- Does the balance have this dimension
3828 --
3829 open csr_get_defined_balance (l_dim_rec.balance_dimension_id,
3830 p_batch_line_rec.balance_type_id);
3831 fetch csr_get_defined_balance into l_def_bal_rec;
3832 if csr_get_defined_balance%notfound then
3833 l_def_bal_not_fnd := TRUE;
3834 end if;
3835 --
3836 close csr_get_defined_balance;
3837 --
3838 -- Dimension requires a JURISDICTION_CODE context.
3839 --
3840 if l_dim_rec.jc_cntxt then
3841 if p_batch_line_rec.jurisdiction_code is null then
3842 l_jc_cntxt_not_set := TRUE;
3843 end if;
3844 else
3845 p_batch_line_rec.jurisdiction_code := null;
3846 end if;
3847 --
3848 -- Dimension requires a TAX_UNIT_ID context.
3849 --
3850 if l_dim_rec.gre_cntxt then
3851 if (p_batch_line_rec.gre_name is null
3852 and p_batch_line_rec.tax_unit_id is null) then
3853 l_gre_cntxt_not_set := TRUE;
3854 else
3855 begin
3856 get_tax_unit_id( p_glbl_data_rec.business_group_id,
3857 p_batch_line_rec.gre_name,
3858 p_batch_line_rec.tax_unit_id);
3859 exception
3860 when no_data_found then
3861 l_gre_cntxt_invld := TRUE;
3862 end;
3863 --
3864 end if;
3865 else
3866 p_batch_line_rec.gre_name := null;
3867 p_batch_line_rec.tax_unit_id := null;
3868 end if;
3869 --
3870 -- Dimension requires a RUN_TYPE_ID context.
3871 --
3872 if l_dim_rec.runtyp_cntxt then
3873 if (p_batch_line_rec.run_type_name is null
3874 and p_batch_line_rec.run_type_id is null) then
3875 l_runtyp_cntxt_not_set := TRUE;
3876 else
3877 begin
3878 get_run_type_id( p_glbl_data_rec.business_group_id,
3879 p_batch_line_rec.run_type_name,
3880 p_batch_line_rec.run_type_id,
3881 p_glbl_data_rec.upload_date);
3882 exception
3883 when no_data_found then
3884 l_runtyp_cntxt_invld := TRUE;
3885 end;
3886 --
3887 end if;
3888 else
3889 p_batch_line_rec.run_type_name := null;
3890 p_batch_line_rec.run_type_id := null;
3891 end if;
3892 --
3893 -- Dimension requires an ORIGINAL_ENTRY_ID context.
3894 --
3895 if l_dim_rec.oee_cntxt then
3896 if p_batch_line_rec.original_entry_id is null then
3897 l_oee_cntxt_not_set := TRUE;
3898 else
3899 open csr_entry(p_glbl_data_rec.upload_date
3900 ,p_batch_line_rec.assignment_id
3901 ,p_batch_line_rec.original_entry_id);
3902 fetch csr_entry into l_ele_entry_id;
3903 if csr_entry%notfound then
3904 l_oee_cntxt_invld := TRUE;
3905 end if;
3906 close csr_entry;
3907 end if;
3908 else
3909 p_batch_line_rec.original_entry_id := null;
3910 end if;
3911 --
3912 -- Dimension requires an SOURCE_ID context.
3913 --
3914 if l_dim_rec.srcid_cntxt then
3915 if p_batch_line_rec.source_id is null then
3916 l_srcid_cntxt_nset := TRUE;
3920 end if;
3917 end if;
3918 else
3919 p_batch_line_rec.source_id := null;
3921 --
3922 -- Dimension requires an SOURCE_TEXT context.
3923 --
3924 if l_dim_rec.srctxt_cntxt then
3925 if p_batch_line_rec.source_text is null then
3926 l_srctxt_cntxt_nset := TRUE;
3927 end if;
3928 else
3929 p_batch_line_rec.source_text := null;
3930 end if;
3931 --
3932 -- Dimension requires an SOURCE_NUMBER context.
3933 --
3934 if l_dim_rec.sn_cntxt then
3935 if p_batch_line_rec.source_number is null then
3936 l_sn_cntxt_nset := TRUE;
3937 end if;
3938 else
3939 p_batch_line_rec.source_number := null;
3940 end if;
3941 --
3942 -- Dimension requires an SOURCE_TEXT2 context.
3943 --
3944 if l_dim_rec.st2_cntxt then
3945 if p_batch_line_rec.source_text2 is null then
3946 l_st2_cntxt_nset := TRUE;
3947 end if;
3948 else
3949 p_batch_line_rec.source_text2 := null;
3950 end if;
3951 --
3952 -- Check the existence of dynamic context input values
3953 -- NB. jurisdiction code is excluded since it has already
3954 -- been checked in validate_balance.
3955 --
3956 if (l_dim_rec.srcid_cntxt or
3957 l_dim_rec.srctxt_cntxt or
3958 l_dim_rec.sn_cntxt or
3959 l_dim_rec.st2_cntxt)
3960 and (p_bal_vald_rec.element_link_id is not null) then
3961 --
3962 -- Set the indicator of input value existence error.
3963 --
3964 if l_dim_rec.srcid_cntxt then
3965 l_srcid_cntxt_noiv := TRUE;
3966 end if;
3967 if l_dim_rec.srctxt_cntxt then
3968 l_srctxt_cntxt_noiv := TRUE;
3969 end if;
3970 if l_dim_rec.sn_cntxt then
3971 l_sn_cntxt_noiv := TRUE;
3972 end if;
3973 if l_dim_rec.st2_cntxt then
3974 l_st2_cntxt_noiv := TRUE;
3975 end if;
3976
3977 load_element_contexts(p_bal_vald_rec.element_link_id
3978 ,l_iv_start_ptr
3979 ,l_iv_end_ptr
3980 );
3981
3982 if l_iv_start_ptr is not null then
3983 for l_iv_idx in l_iv_start_ptr..l_iv_end_ptr loop
3984
3985 l_context := g_input_val_contexts(l_iv_idx).context_name;
3986
3987 if l_dim_rec.srcid_cntxt and l_context = 'SOURCE_ID' then
3988 -- Source ID input value exists.
3989 l_srcid_cntxt_noiv := FALSE;
3990
3991 elsif l_dim_rec.srctxt_cntxt and l_context = 'SOURCE_TEXT' then
3992 -- Source Text input value exists.
3993 l_srctxt_cntxt_noiv := FALSE;
3994
3995 elsif l_dim_rec.sn_cntxt and l_context = 'SOURCE_NUMBER' then
3996 -- Source Number input value exists.
3997 l_sn_cntxt_noiv := FALSE;
3998
3999 elsif l_dim_rec.st2_cntxt and l_context = 'SOURCE_TEXT2' then
4000 -- Source Text2 input value exists.
4001 l_st2_cntxt_noiv := FALSE;
4002
4003 end if;
4004
4005 end loop;
4006 end if;
4007
4008 end if;
4009 --
4010 end if;
4011 --
4012 -- Check each error flag and write out message for each failure against the
4013 -- batch line being validated.
4014 --
4015 -- Balance dimension does not exist.
4016 --
4017 if l_dim_rec.invld then
4018 l_msgs(l_msgs.count+1) := 'HR_51044_BLD_DOES_NOT_EXIST';
4019 end if;
4020 --
4021 -- Balance dimension is not supported.
4022 --
4023 if l_dim_rec.not_supp then
4024 l_msgs(l_msgs.count+1) := 'HR_51045_BLD_IS_NOT_SUPPORTED';
4025 end if;
4026 --
4027 -- The Defined Balance does not exist.
4028 --
4029 if l_def_bal_not_fnd then
4030 l_msgs(l_msgs.count+1) := 'HR_51105_BAL_DEF_NOT_EXIST';
4031 end if;
4032 --
4033 -- The JURISDICTION_CODE context must be specified.
4034 --
4035 if l_jc_cntxt_not_set then
4036 l_msgs(l_msgs.count+1) := 'HR_13131_BAL_JURIS_MANDATORY';
4037 end if;
4038 --
4039 -- The TAX_UNIT_ID context must be specified.
4040 --
4041 if l_gre_cntxt_not_set then
4042 l_msgs(l_msgs.count+1) := 'HR_13130_BAL_TAX_UNIT_MAND';
4043 end if;
4044 --
4045 -- The tax unit does not exist.
4046 --
4047 if l_gre_cntxt_invld then
4048 l_msgs(l_msgs.count+1) := 'HR_51046_ORU_TU_INVALID';
4049 end if;
4050 --
4051 -- The RUN_TYPE_ID context must be specified.
4052 --
4053 if l_runtyp_cntxt_not_set then
4054 l_msgs(l_msgs.count+1) := 'PAY_289146_RUN_TYP_MAND';
4055 end if;
4056 --
4057 -- The run type does not exist.
4058 --
4059 if l_runtyp_cntxt_invld then
4060 l_msgs(l_msgs.count+1) := 'PAY_289147_INV_RUN_TYP';
4061 end if;
4062 --
4063 -- The ORIGINAL_ENTRY_ID context must be specified.
4064 --
4065 if l_oee_cntxt_not_set then
4066 l_msgs(l_msgs.count+1) := 'HR_51047_ELE_ORIG_CXT_NEEDED';
4067 end if;
4068 --
4069 -- The element entry does not exist.
4070 --
4071 if l_oee_cntxt_invld then
4075 -- The SOURCE_ID context must be specified.
4072 l_msgs(l_msgs.count+1) := 'HR_51048_ELE_ORIG_DO_NOT_EXIST';
4073 end if;
4074 --
4076 --
4077 if l_srcid_cntxt_nset then
4078 l_msgs(l_msgs.count+1) := 'HR_51445_SRC_ID_CXT_NEEDED';
4079 end if;
4080 --
4081 -- The source id does not exist.
4082 --
4083 if l_srcid_cntxt_noiv then
4084 l_msgs(l_msgs.count+1) := 'HR_51446_SRC_ID_DO_NOT_EXIST';
4085 end if;
4086 --
4087 -- The SOURCE_TEXT context must be specified.
4088 --
4089 if l_srctxt_cntxt_nset then
4090 l_msgs(l_msgs.count+1) := 'HR_51447_SRC_TEXT_CXT_NEEDED';
4091 end if;
4092 --
4093 -- The source text does not exist.
4094 --
4095 if l_srctxt_cntxt_noiv then
4096 l_msgs(l_msgs.count+1) := 'HR_51448_SRC_TEXT_DO_NOT_EXIST';
4097 end if;
4098 --
4099 -- The SOURCE_NUMBER context must be specified.
4100 --
4101 if l_sn_cntxt_nset then
4102 l_msgs(l_msgs.count+1) := 'PAY_33250_BAL_SRC_NUM_MAND';
4103 end if;
4104 --
4105 -- Source Number input value does not exist.
4106 --
4107 if l_sn_cntxt_noiv then
4108 l_msgs(l_msgs.count+1) := 'PAY_33251_BAL_NO_SRC_NUM_IV';
4109 end if;
4110 --
4111 -- The SOURCE_TEXT2 context must be specified.
4112 --
4113 if l_st2_cntxt_nset then
4114 l_msgs(l_msgs.count+1) := 'PAY_33252_BAL_SRC_TXT2_MAND';
4115 end if;
4116 --
4117 -- Source Text2 input value does not exist.
4118 --
4119 if l_st2_cntxt_noiv then
4120 l_msgs(l_msgs.count+1) := 'PAY_33253_BAL_NO_SRC_TXT2_IV';
4121 end if;
4122 --
4123 -- Write all the messages
4124 --
4125 for l_msg_idx in 1..l_msgs.count loop
4126
4127 write_message_line
4128 (p_meesage_level => LINE
4129 ,p_batch_id => null
4130 ,p_batch_line_id => p_batch_line_rec.batch_line_id
4131 ,p_message_text => null
4132 ,p_message_token => l_msgs(l_msg_idx));
4133
4134 end loop;
4135 --
4136 -- Update the batch line with the balance information.
4137 --
4138 p_batch_line_rec.balance_dimension_id := l_dim_rec.balance_dimension_id;
4139 p_batch_line_rec.dimension_name := l_dim_rec.dimension_name;
4140 --
4141 -- At least one of the tests has failed so mark the batch line as invalid.
4142 --
4143 if l_dim_rec.invld or
4144 l_dim_rec.not_supp or
4145 l_def_bal_not_fnd or
4146 l_jc_cntxt_not_set or
4147 l_gre_cntxt_not_set or
4148 l_oee_cntxt_not_set or
4149 l_srctxt_cntxt_noiv or
4150 l_srcid_cntxt_noiv or
4151 l_srctxt_cntxt_nset or
4152 l_srcid_cntxt_nset or
4153 l_sn_cntxt_nset or
4154 l_sn_cntxt_noiv or
4155 l_st2_cntxt_nset or
4156 l_st2_cntxt_noiv or
4157 l_gre_cntxt_invld or
4158 l_runtyp_cntxt_not_set or
4159 l_runtyp_cntxt_invld or
4160 l_oee_cntxt_invld then
4161 p_batch_line_rec.batch_line_status := 'E'; -- Error
4162 end if;
4163 --
4164 hr_utility.trace('Exiting pay_balance_upload.validate_dimension');
4165 --
4166 end validate_dimension;
4167 --
4168 -----------------------------------------------------------------------------
4169 -- NAME
4170 -- validate_balance
4171 -- PURPOSE
4172 -- Ensures that the balance on the batch line is valid.
4173 -- ARGUMENTS
4174 -- p_glbl_data_rec - global data structure.
4175 -- p_batch_line_rec - the current batch line.
4176 -- p_bal_vald_rec - balance validation results
4177 -- USES
4178 -- write_message_line
4179 -- NOTES
4180 -- Ensures that
4181 -- 1. the balance exists
4182 -- 2. the balance has an initial balance feed
4183 -- 3. an element link exists which ensures the eligibility of the element
4184 -- used by initial balance feed.
4185 -----------------------------------------------------------------------------
4186 --
4187 procedure validate_balance
4188 (
4189 p_glbl_data_rec in glbl_data_rec_type
4190 ,p_batch_line_rec in out nocopy csr_batch_line_validate%rowtype
4191 ,p_bal_vald_rec out nocopy t_balance_validation_rec
4192 ) is
4193 --
4194 -- Retrieves balance information NB. either the balance_type_id or
4195 -- balance_name may have been specified. If both are specified then
4196 -- the balance_type_id overrides the balance_name. A constraint on the
4197 -- batch lines table ensures that at least one of them is set.
4198 --
4199 cursor csr_balance
4200 (
4201 p_business_group_id number
4202 ,p_legislation_code varchar2
4203 ,p_balance_type_id number
4204 ,p_balance_name varchar2
4205 ) is
4206 select BT.balance_type_id
4207 ,upper(BT.balance_name) balance_name
4208 from pay_balance_types BT
4209 where p_balance_type_id is not null
4210 and BT.balance_type_id = p_balance_type_id
4211 and nvl(BT.business_group_id, nvl(p_business_group_id, -1)) =
4212 nvl(p_business_group_id, -1)
4213 and nvl(BT.legislation_code, nvl(p_legislation_code, ' ')) =
4214 nvl(p_legislation_code, ' ')
4215 union all
4216 select BT.balance_type_id
4217 ,upper(BT.balance_name) balance_name
4221 and nvl(BT.business_group_id, nvl(p_business_group_id, -1)) =
4218 from pay_balance_types BT
4219 where p_balance_type_id is null
4220 and upper(BT.balance_name) = upper(p_balance_name)
4222 nvl(p_business_group_id, -1)
4223 and nvl(BT.legislation_code, nvl(p_legislation_code, ' ')) =
4224 nvl(p_legislation_code, ' ');
4225 --
4226 -- Retrieves the initial balance feed information for a balance NB. if the
4227 -- balance uses the JURISDICTION_CODE context then the element used for the
4228 -- initial balance feed must also have an input valu8e called 'Jurisdiction'.
4229 --
4230 cursor csr_initial_balance_feed
4231 (
4232 p_business_group_id number
4233 ,p_legislation_code varchar2
4234 ,p_balance_type_id number
4235 ) is
4236 select ET.element_type_id
4237 ,BT.jurisdiction_level
4238 ,IV.input_value_id ibf_input_value_id
4239 ,decode(nvl(BT.jurisdiction_level, 0),
4240 0, null,
4241 IV2.input_value_id) jc_input_value_id
4242 from pay_balance_types BT
4243 ,pay_balance_feeds_f BF
4244 ,pay_input_values_f IV
4245 ,pay_input_values_f IV2
4246 ,pay_element_types_f ET
4247 ,pay_element_classifications EC
4248 where BF.balance_type_id = p_balance_type_id
4249 and BT.balance_type_id = BF.balance_type_id
4250 and IV.input_value_id = BF.input_value_id
4251 and ET.element_type_id = IV.element_type_id
4252 and EC.classification_id = ET.classification_id
4253 and EC.balance_initialization_flag = 'Y'
4254 and ((nvl(BT.jurisdiction_level, 0) <> 0 and
4255 IV2.element_type_id = ET.element_type_id and
4256 IV2.name = p_glbl_data_rec.jurisdiction_iv) or
4257 (nvl(BT.jurisdiction_level, 0) = 0 and
4258 IV2.input_value_id = IV.input_value_id))
4259 and (ET.business_group_id +0 = p_business_group_id
4260 or (ET.business_group_id is null
4261 and ET.legislation_code = p_legislation_code)
4262 or (ET.business_group_id is null and ET.legislation_code is null))
4263 and BF.effective_start_date = START_OF_TIME
4264 and BF.effective_end_date = END_OF_TIME
4265 and IV.effective_start_date = START_OF_TIME
4266 and IV.effective_end_date = END_OF_TIME
4267 and IV2.effective_start_date = START_OF_TIME
4268 and IV2.effective_end_date = END_OF_TIME
4269 and ET.effective_start_date = START_OF_TIME
4270 and ET.effective_end_date = END_OF_TIME;
4271 --
4272 -- Retrieves the element link that makes the initial balance feed eligible.
4273 --
4274 cursor csr_element_link
4275 (
4276 p_business_group_id number
4277 ,p_element_type_id number
4278 ) is
4279 select EL.element_link_id
4280 from pay_element_links_f EL
4281 where EL.business_group_id = p_business_group_id
4282 and EL.element_type_id = p_element_type_id
4283 and EL.link_to_all_payrolls_flag = 'Y'
4284 and EL.payroll_id is null
4285 and EL.job_id is null
4286 and EL.position_id is null
4287 and EL.people_group_id is null
4288 and EL.organization_id is null
4289 and EL.grade_id is null
4290 and EL.pay_basis_id is null
4291 and EL.employment_category is null
4292 and EL.effective_start_date = START_OF_TIME
4293 and EL.effective_end_date = END_OF_TIME;
4294 --
4295 -- Record to hold balance information.
4296 --
4297 l_balance_rec csr_balance%rowtype;
4298 --
4299 -- Holds the element type used by the initial balance feed.
4300 --
4301 l_ele_type_id number;
4302 l_jurisdiction_level number;
4303 --
4304 -- Holds the element link that provides the eligibility for the element
4305 -- type used by the initial balance feed.
4306 --
4307 l_ele_link_id number;
4308 --
4309 -- Variables to cache details of the current balance being validated.
4310 --
4311 l_bal_id pay_balance_batch_lines.balance_type_id%type;
4312 l_bal_name pay_balance_batch_lines.balance_name%type;
4313 l_bal_invld boolean := FALSE;
4314 l_bal_invl_feed boolean := FALSE;
4315 l_bal_invl_link boolean := FALSE;
4316 l_bal_vald_rec t_balance_validation_rec;
4317 l_ibf_input_value_id number;
4318 l_jc_input_value_id number;
4319 --
4320 -- Variables used to control the search through list of previously
4321 -- validated balances.
4322 --
4323 l_index number := 0;
4324 l_bal_index number;
4325 l_balance_found boolean := FALSE;
4326 --
4327 begin
4328 --
4329 hr_utility.trace('Entering pay_balance_upload.validate_balance');
4330 --
4331 -- Search through list of balances that have already been validated NB. the
4332 -- list of balances is held in a PLSQL table.
4333 --
4334 for l_index in 1..g_bal_vald.count loop
4335 --
4339 --
4336 -- See if the balance is in the list NB. the balance_type_id overrides the
4337 -- balance_name. If the balance is found then the flag is set and the
4338 -- search index points to the matching entry.
4340 if ((p_batch_line_rec.balance_type_id
4341 = g_bal_vald(l_index).balance_type_id) or
4342 (p_batch_line_rec.balance_type_id is null and
4343 upper(p_batch_line_rec.balance_name)
4344 = g_bal_vald(l_index).balance_name)) then
4345 l_balance_found := TRUE;
4346 l_bal_index := l_index;
4347 exit;
4348 end if;
4349 --
4350 end loop;
4351 --
4352 -- Balance has already been validated.
4353 --
4354 if l_balance_found then
4355 --
4356 -- Values must be the same as those for the balance when it was validated.
4357 --
4358 l_bal_id := g_bal_vald(l_bal_index).balance_type_id;
4359 l_bal_name := g_bal_vald(l_bal_index).balance_name;
4360 l_bal_invld := g_bal_vald(l_bal_index).bal_invld;
4361 l_bal_invl_feed := g_bal_vald(l_bal_index).bal_invl_feed;
4362 l_bal_invl_link := g_bal_vald(l_bal_index).bal_invl_link;
4363
4364 p_bal_vald_rec := g_bal_vald(l_bal_index);
4365 --
4366 -- Balance is new ie. has not been found already. The balance must be
4367 -- validated and added to the list of validated balances.
4368 --
4369 else
4370 --
4371 -- See if the balance exists. If it does not then cache the old values
4372 -- else cache the new values (for subsequent checks) NB. a balance
4373 -- that does not exist cannot be validated any further.
4374 --
4375 open csr_balance(p_glbl_data_rec.business_group_id
4376 ,p_glbl_data_rec.legislation_code
4377 ,p_batch_line_rec.balance_type_id
4378 ,p_batch_line_rec.balance_name);
4379 fetch csr_balance into l_balance_rec;
4380 if csr_balance%notfound then
4381 l_bal_id := p_batch_line_rec.balance_type_id;
4382 l_bal_name := p_batch_line_rec.balance_name;
4383 l_bal_invld := TRUE;
4384 else
4385 l_bal_id := l_balance_rec.balance_type_id;
4386 l_bal_name := l_balance_rec.balance_name;
4387 end if;
4388 close csr_balance;
4389 --
4390 -- Balance exists so continue validation.
4391 --
4392 if not l_bal_invld then
4393 --
4394 -- See if the balance has an initial balance feed.
4395 --
4396 open csr_initial_balance_feed(p_glbl_data_rec.business_group_id
4397 ,p_glbl_data_rec.legislation_code
4398 ,l_bal_id);
4399 fetch csr_initial_balance_feed into l_ele_type_id
4400 ,l_jurisdiction_level
4401 ,l_ibf_input_value_id
4402 ,l_jc_input_value_id;
4403 if csr_initial_balance_feed%notfound then
4404 l_bal_invl_feed := TRUE;
4405 end if;
4406 close csr_initial_balance_feed;
4407 --
4408 -- Balance has an initial balance feed so continue validation.
4409 --
4410 if not l_bal_invl_feed then
4411 --
4412 -- See if an element link exists for the element used for the initial
4413 -- balance feed.
4414 --
4415 open csr_element_link(p_glbl_data_rec.business_group_id
4416 ,l_ele_type_id);
4417 fetch csr_element_link into l_ele_link_id;
4418 if csr_element_link%notfound then
4419 l_bal_invl_link := TRUE;
4420 end if;
4421 close csr_element_link;
4422 --
4423 end if;
4424 --
4425 end if;
4426 --
4427 -- A new balance has been found so add it to the list of validated
4428 -- balances along with the results of the validation.
4429 --
4430 l_bal_vald_rec.balance_type_id := l_bal_id;
4431 l_bal_vald_rec.balance_name := l_bal_name;
4432 l_bal_vald_rec.element_type_id := l_ele_type_id;
4433 l_bal_vald_rec.element_link_id := l_ele_link_id;
4434 l_bal_vald_rec.ibf_input_value_id := l_ibf_input_value_id;
4435 l_bal_vald_rec.jc_input_value_id := l_jc_input_value_id;
4436 l_bal_vald_rec.jurisdiction_level := l_jurisdiction_level;
4437 l_bal_vald_rec.bal_invld := l_bal_invld;
4438 l_bal_vald_rec.bal_invl_feed := l_bal_invl_feed;
4439 l_bal_vald_rec.bal_invl_link := l_bal_invl_link;
4440
4441 -- should avoid the case where both ID and name are null
4442 if not (l_bal_id is null and l_bal_name is null) then
4443 --
4444 l_bal_index := g_bal_vald.count+1;
4445 g_bal_vald(l_bal_index) := l_bal_vald_rec;
4446 end if;
4447 --
4448 p_bal_vald_rec := l_bal_vald_rec;
4449 --
4450 end if;
4451 --
4452 -- Check each error flag and write out message for each failure against the
4453 -- batch line being validated.
4454 --
4455 -- Balance does not exist.
4456 --
4457 if l_bal_invld then
4458 write_message_line
4459 (p_meesage_level => LINE
4460 ,p_batch_id => null
4461 ,p_batch_line_id => p_batch_line_rec.batch_line_id
4465 --
4462 ,p_message_text => null
4463 ,p_message_token => 'HR_51049_BLT_DOES_NOT_EXIST');
4464 end if;
4466 -- Balance does not have an initial balance feed.
4467 --
4468 if l_bal_invl_feed then
4469 write_message_line
4470 (p_meesage_level => LINE
4471 ,p_batch_id => null
4472 ,p_batch_line_id => p_batch_line_rec.batch_line_id
4473 ,p_message_text => null
4474 ,p_message_token => 'HR_51050_BLF_NO_INI_FEED');
4475 end if;
4476 --
4477 -- Balance does not have an element link for its initial balance feed.
4478 --
4479 if l_bal_invl_link then
4480 write_message_line
4481 (p_meesage_level => LINE
4482 ,p_batch_id => null
4483 ,p_batch_line_id => p_batch_line_rec.batch_line_id
4484 ,p_message_text => null
4485 ,p_message_token => 'HR_51051_ELI_NO_INI_BAL_LINK');
4486 end if;
4487 --
4488 -- Update the batch line with the balance information.
4489 --
4490 p_batch_line_rec.balance_type_id := l_bal_id;
4491 p_batch_line_rec.balance_name := l_bal_name;
4492 --
4493 -- At least one of the tests has failed so mark the batch line as invalid.
4494 --
4495 if l_bal_invld or
4496 l_bal_invl_feed or
4497 l_bal_invl_link then
4498 p_batch_line_rec.batch_line_status := 'E'; -- Error
4499 end if;
4500 --
4501 hr_utility.trace('Exiting pay_balance_upload.validate_balance');
4502 --
4503 end validate_balance;
4504 --
4505 -----------------------------------------------------------------------------
4506 -- NAME
4507 -- validate_assignment
4508 -- PURPOSE
4509 -- Ensures that the assignment on the batch line is valid.
4510 -- ARGUMENTS
4511 -- p_glbl_data_rec - global data structure.
4512 -- p_batch_line_rec - the current batch line.
4513 -- p_asg_id - assignment_id
4514 -- p_asg_number - assignment_number
4515 -- p_asg_invld - does the assignment exist ?
4516 -- p_asg_invld_type - is it an employee assignment ?
4517 -- p_asg_invld_pyrl - does it belong to the payroll specified in the batch ?
4518 -- p_asg_too_short - is it assigned to the payroll for sufficent time ?
4519 -- p_asg_processed - has it been processed before the upload date ?
4520 -- USES
4521 -- write_message_line
4522 -- NOTES
4523 -- Ensures that
4524 -- 1. the assignment exists
4525 -- 2. it is an employee assignment
4526 -- 3. it is assigned to the payroll
4527 -- 4. it has not been processed before the upload date
4528 -----------------------------------------------------------------------------
4529 --
4530 procedure validate_assignment
4531 (
4532 p_glbl_data_rec in glbl_data_rec_type
4533 ,p_batch_line_rec in out nocopy csr_batch_line_validate%rowtype
4534 ,p_asg_id in out nocopy pay_balance_batch_lines.assignment_id%type
4535 ,p_asg_number in out nocopy pay_balance_batch_lines.assignment_number%type
4536 ,p_asg_invld in out nocopy boolean
4537 ,p_asg_invld_type in out nocopy boolean
4538 ,p_asg_invld_pyrl in out nocopy boolean
4539 ,p_asg_too_short in out nocopy boolean
4540 ,p_asg_processed in out nocopy boolean
4541 ) is
4542 --
4543 -- Retrieves assignment information NB. either the assignment_id or
4544 -- assignment_number may have been specified. If both are specified then
4545 -- the assignment_id overrides the assignment_number. A constraint on the
4546 -- batch lines table ensures that at least one of them is set.
4547 --
4548 cursor csr_assignment
4549 (
4550 p_business_group_id number
4551 ,p_assignment_id number
4552 ,p_assignment_number varchar2
4553 ,p_upload_date date
4554 ) is
4555 select ASG.assignment_id
4556 ,upper(ASG.assignment_number) assignment_number
4557 ,ASG.assignment_type
4558 ,ASG.business_group_id
4559 ,ASG.payroll_id
4560 ,ASG.effective_start_date
4561 from per_all_assignments_f ASG
4562 where p_assignment_id is not null
4563 and ASG.business_group_id + 0 = p_business_group_id
4564 and ASG.assignment_id = p_assignment_id
4565 and p_upload_date between ASG.effective_start_date
4566 and ASG.effective_end_date
4567 union all
4568 select ASG.assignment_id
4569 ,upper(ASG.assignment_number) assignment_number
4570 ,ASG.assignment_type
4571 ,ASG.business_group_id
4572 ,ASG.payroll_id
4573 ,ASG.effective_start_date
4574 from per_all_assignments_f ASG
4575 where p_assignment_id is null
4576 and ASG.business_group_id + 0 = p_business_group_id
4577 and ASG.assignment_number = p_assignment_number
4578 and p_upload_date between ASG.effective_start_date
4579 and ASG.effective_end_date;
4580 --
4581 -- Retrieves the assignment actions for an assignment that exist before the
4582 -- upload date.
4583
4584 -- Bug # 6997838.
4585 -- If BEE is already run for this assignment, we can still consider that
4586 -- assignment for balance Initialization as it does not contribute to balance.
4587 -- This cursor needs to be changed in future to consider only SEQUENCED
4588 -- actions as only SEQUENCED actions contribute to balances.
4589
4590 cursor csr_assignment_action
4591 (
4595 from pay_assignment_actions AA, pay_payroll_actions PAA
4592 p_assignment_id number
4593 ) is
4594 select AA.payroll_action_id
4596 where AA.assignment_id = p_assignment_id
4597 and PAA.payroll_action_id = AA.payroll_action_id
4598 and PAA.action_type <> 'BEE';
4599 --
4600 -- Record to hold assignment information.
4601 --
4602 l_assignment_rec csr_assignment%rowtype;
4603 --
4604 -- Holds the payroll action the assignment was processed with.
4605 --
4606 l_pay_act_id number;
4607 --
4608 begin
4609 --
4610 hr_utility.trace('Entering pay_balance_upload.validate_assignment');
4611 --
4612 -- Assignment has already been validated, so there is no need to do the
4613 -- validation again NB. the assignment_id overrides the assignment_number.
4614 --
4615 if ((p_batch_line_rec.assignment_id is not null and
4616 p_batch_line_rec.assignment_id = p_asg_id) or
4617 (p_batch_line_rec.assignment_id is null and
4618 upper(p_batch_line_rec.assignment_number) = p_asg_number)) then
4619 --
4620 -- Do nothing.
4621 --
4622 null;
4623 --
4624 -- Assignment has not been validated yet, so validate it.
4625 --
4626 else
4627 --
4628 -- Reset the error flags.
4629 --
4630 p_asg_invld := FALSE;
4631 p_asg_invld_type := FALSE;
4632 p_asg_invld_pyrl := FALSE;
4633 p_asg_too_short := FALSE;
4634 p_asg_processed := FALSE;
4635 --
4636 -- See if the assignment exists. If it does not then cache the old values
4637 -- else cache the new values (for subsequent checks) NB. an assignment
4638 -- that does not exist cannot be validated any further.
4639 --
4640 open csr_assignment(p_glbl_data_rec.business_group_id
4641 ,p_batch_line_rec.assignment_id
4642 ,p_batch_line_rec.assignment_number
4643 ,p_glbl_data_rec.upload_date);
4644 fetch csr_assignment into l_assignment_rec;
4645 if csr_assignment%notfound then
4646 p_asg_id := p_batch_line_rec.assignment_id;
4647 p_asg_number := p_batch_line_rec.assignment_number;
4648 p_asg_invld := TRUE;
4649 else
4650 p_asg_id := l_assignment_rec.assignment_id;
4651 p_asg_number := l_assignment_rec.assignment_number;
4652 end if;
4653 close csr_assignment;
4654 --
4655 -- Assignment exists so continue validation.
4656 --
4657 if not p_asg_invld then
4658 --
4659 -- It is not an employee assignment.
4660 --
4661 if l_assignment_rec.assignment_type <> 'E' then
4662 p_asg_invld_type := TRUE;
4663 end if;
4664 --
4665 -- Assignment does not belong to the payroll for the batch NB. the
4666 -- assignment cannot be validated any further.
4667 --
4668 if l_assignment_rec.payroll_id = p_glbl_data_rec.payroll_id then
4669 -- OK.
4670 null;
4671 else
4672 p_asg_invld_pyrl := TRUE;
4673 end if;
4674 --
4675 -- Assignment has already been processed before the upload date.
4676 --
4677 open csr_assignment_action(p_asg_id);
4678 fetch csr_assignment_action into l_pay_act_id;
4679 if csr_assignment_action%found then
4680 p_asg_processed := TRUE;
4681 end if;
4682 close csr_assignment_action;
4683 --
4684 -- Assignment does not exist so stop validation.
4685 --
4686 else
4687 --
4688 -- Do nothing.
4689 --
4690 null;
4691 --
4692 end if;
4693 --
4694 end if;
4695 --
4696 -- Check each error flag and write out message for each failure against the
4697 -- batch line being validated.
4698 --
4699 -- Assignment does not exist.
4700 --
4701 if p_asg_invld then
4702 write_message_line
4703 (p_meesage_level => LINE
4704 ,p_batch_id => null
4705 ,p_batch_line_id => p_batch_line_rec.batch_line_id
4706 ,p_message_text => null
4707 ,p_message_token => 'PAY_7702_PDT_VALUE_NOT_FOUND');
4708 end if;
4709 --
4710 -- Assignment must be an employee assignment
4711 --
4712 if p_asg_invld_type then
4713 write_message_line
4714 (p_meesage_level => LINE
4715 ,p_batch_id => null
4716 ,p_batch_line_id => p_batch_line_rec.batch_line_id
4717 ,p_message_text => null
4718 ,p_message_token => 'HR_51052_ASG_MUST_BE_AN_EMP');
4719 end if;
4720 --
4721 -- Assignment does not belong to the payroll specified in the batch header.
4722 --
4723 if p_asg_invld_pyrl then
4724 write_message_line
4725 (p_meesage_level => LINE
4726 ,p_batch_id => null
4727 ,p_batch_line_id => p_batch_line_rec.batch_line_id
4728 ,p_message_text => null
4729 ,p_message_token => 'HR_7789_SETUP_ASG_HAS_NO_PAYR'
4730 ,p_token_name => 'ADJ_DATE'
4731 ,p_token_value => to_char(p_glbl_data_rec.upload_date)
4732 );
4733 end if;
4734 --
4735 -- Assignment has already been processed before the upload date.
4739 p_asg_processed := FALSE;
4736 --
4737 if p_asg_processed and p_glbl_data_rec.purge_mode then
4738 -- Processed error but we are purging, so ignore
4740 end if;
4741 --
4742 if p_asg_processed then
4743 write_message_line
4744 (p_meesage_level => LINE
4745 ,p_batch_id => null
4746 ,p_batch_line_id => p_batch_line_rec.batch_line_id
4747 ,p_message_text => null
4748 ,p_message_token => 'HR_51053_ASA_PREV_PROCESSED');
4749 end if;
4750 --
4751 -- Update the batch line with the assignment information.
4752 --
4753 p_batch_line_rec.assignment_id := p_asg_id;
4754 p_batch_line_rec.assignment_number := p_asg_number;
4755 --
4756 -- At least one of the tests has failed so mark the batch line as invalid.
4757 --
4758 if p_asg_invld or
4759 p_asg_invld_type or
4760 p_asg_invld_pyrl or
4761 p_asg_processed then
4762 p_batch_line_rec.batch_line_status := 'E'; -- Error
4763 end if;
4764 --
4765 hr_utility.trace('Exiting pay_balance_upload.validate_assignment');
4766 --
4767 end validate_assignment;
4768 --
4769 -----------------------------------------------------------------------------
4770 -- NAME
4771 -- validate_batch_lines
4772 -- PURPOSE
4773 -- Ensures that all the batch lines for a batch are valid NB. it also
4774 -- populates the system IDs where necessary.
4775 -- ARGUMENTS
4776 -- p_glbl_data_rec - global data structure.
4777 -- USES
4778 -- validate_assignment
4779 -- validate_balance
4780 -- validate_dimension
4781 -- NOTES
4782 -- All lines that have not been transferred are validated. Multiple errors
4783 -- may be reported against each line ie. the assignment and dimension may
4784 -- both be invalid.
4785 -----------------------------------------------------------------------------
4786 --
4787 procedure validate_batch_lines
4788 (
4789 p_glbl_data_rec in glbl_data_rec_type
4790 ) is
4791 --
4792 -- Record to hold a batch line NB. the cursor is defined at package level.
4793 --
4794 l_batch_line_rec csr_batch_line_validate%rowtype;
4795 --
4796 -- Variables to cache details of the previously validated assignment. This
4797 -- can be used bu the validate_assignment procedure when validating future
4798 -- assignments.
4799 --
4800 l_asg_id pay_balance_batch_lines.assignment_id%type;
4801 l_asg_number pay_balance_batch_lines.assignment_number%type;
4802 l_asg_invld boolean := FALSE;
4803 l_asg_invld_type boolean := FALSE;
4804 l_asg_invld_pyrl boolean := FALSE;
4805 l_asg_too_short boolean := FALSE;
4806 l_asg_processed boolean := FALSE;
4807 --
4808 -- Balance validation details returned from validate_balance.
4809 -- This will be reused in validate_dimension.
4810 --
4811 l_bal_vald_rec t_balance_validation_rec;
4812 --
4813 begin
4814 --
4815 hr_utility.trace('Entering pay_balance_upload.validate_batch_lines');
4816 --
4817 -- Clear the validation cache.
4818 --
4819 g_bal_vald.delete;
4820 g_dim_vald.delete;
4821 --
4822 open csr_batch_line_validate(p_glbl_data_rec.batch_id);
4823 --
4824 -- Loop for all the batch lines in the batch.
4825 --
4826 loop
4827 --
4828 -- Get the next batch line NB. exit the loop when all batch lines have
4829 -- been retrieved NB. the batch lines are ordered by assignment_id and
4830 -- then assignment_number. This ensures that all the batch lines for each
4831 -- assignment are contiguous which allows the validation to be optimised.
4832 --
4833 fetch csr_batch_line_validate into l_batch_line_rec;
4834 exit when csr_batch_line_validate%notfound;
4835 --
4836 -- Default the status to valid NB. this will be changed to invalid by
4837 -- any failures during validation.
4838 --
4839 l_batch_line_rec.batch_line_status := 'V';
4840 --
4841 -- Check the assignment on the batch line.
4842 --
4843 validate_assignment(p_glbl_data_rec
4844 ,l_batch_line_rec
4845 ,l_asg_id
4846 ,l_asg_number
4847 ,l_asg_invld
4848 ,l_asg_invld_type
4849 ,l_asg_invld_pyrl
4850 ,l_asg_too_short
4851 ,l_asg_processed);
4852 --
4853 -- Check the balance on the batch line.
4854 --
4855 validate_balance(p_glbl_data_rec
4856 ,l_batch_line_rec
4857 ,l_bal_vald_rec);
4858 --
4859 -- Check the dimension on the batch line.
4860 --
4861 validate_dimension(p_glbl_data_rec
4862 ,l_batch_line_rec
4863 ,l_bal_vald_rec);
4864 --
4865 -- Validate upload dates
4866 --
4867 if (l_batch_line_rec.upload_date is not null) then
4868 if (l_batch_line_rec.upload_date > p_glbl_data_rec.upload_date) then
4869 write_message_line
4870 (p_meesage_level => LINE
4871 ,p_batch_id => null
4872 ,p_batch_line_id => l_batch_line_rec.batch_line_id
4873 ,p_message_text => null
4877 end if;
4874 ,p_message_token => 'PAY_33254_BAL_INV_BL_UPL_DATE');
4875 l_batch_line_rec.batch_line_status := 'E'; -- Error
4876 end if;
4878 --
4879 -- Update the batch line with information retrieved during validation
4880 -- ie. if the assignment_number was set on a batch line then the
4881 -- assiugnment_id is derived etc...
4882 --
4883 update pay_balance_batch_lines BL
4884 set BL.assignment_number = l_batch_line_rec.assignment_number
4885 ,BL.assignment_id = l_batch_line_rec.assignment_id
4886 ,BL.balance_name = l_batch_line_rec.balance_name
4887 ,BL.balance_type_id = l_batch_line_rec.balance_type_id
4888 ,BL.dimension_name = l_batch_line_rec.dimension_name
4889 ,BL.balance_dimension_id = l_batch_line_rec.balance_dimension_id
4890 ,BL.gre_name = l_batch_line_rec.gre_name
4891 ,BL.tax_unit_id = l_batch_line_rec.tax_unit_id
4892 ,BL.jurisdiction_code = l_batch_line_rec.jurisdiction_code
4893 ,BL.original_entry_id = l_batch_line_rec.original_entry_id
4894 ,BL.source_id = l_batch_line_rec.source_id
4895 ,BL.source_text = l_batch_line_rec.source_text
4896 ,BL.source_number = l_batch_line_rec.source_number
4897 ,BL.source_text2 = l_batch_line_rec.source_text2
4898 ,BL.run_type_id = l_batch_line_rec.run_type_id
4899 ,BL.batch_line_status = l_batch_line_rec.batch_line_status
4900 where current of csr_batch_line_validate;
4901 --
4902 end loop;
4903 --
4904 close csr_batch_line_validate;
4905 --
4906 hr_utility.trace('Exiting pay_balance_upload.validate_batch_lines');
4907 --
4908 end validate_batch_lines;
4909 --
4910 -----------------------------------------------------------------------------
4911 -- NAME
4912 -- validate_batch
4913 -- PURPOSE
4914 -- Ensures that all the batch information is valid.
4915 -- ARGUMENTS
4916 -- p_glbl_data_rec - global data structure.
4917 -- USES
4918 -- validate_batch_header
4919 -- validate_batch_lines
4920 -- NOTES
4921 -- If the batch header is in error then there is no point in continuing
4922 -- with the validation of the batch lines.
4923 -----------------------------------------------------------------------------
4924 --
4925 procedure validate_batch
4926 (
4927 p_glbl_data_rec in out nocopy glbl_data_rec_type
4928 ) is
4929 --
4930 -- Dynamic sql variables
4931 --
4932 sql_curs number;
4933 rows_processed integer;
4934 statem varchar2(512);
4935 l_validation_supp varchar2(30);
4936 --
4937 begin
4938 --
4939 hr_utility.trace('Entering pay_balance_upload.validate_batch');
4940 --
4941 -- Ensure the batch header is valid.
4942 --
4943 validate_batch_header(p_glbl_data_rec);
4944 --
4945 -- Populate the batch info.
4946 --
4950 g_batch_info.purge_mode := p_glbl_data_rec.purge_mode;
4947 g_batch_info.batch_id := p_glbl_data_rec.batch_id;
4948 g_batch_info.business_group_id := p_glbl_data_rec.business_group_id;
4949 g_batch_info.legislation_code := p_glbl_data_rec.legislation_code;
4951
4952 --
4953 -- If the batch header is valid then ensure the batch lines are valid.
4954 --
4955 if p_glbl_data_rec.batch_header_status = 'V' then
4956 --
4957 -- General validation ie. is the data valid ?
4958 --
4959 validate_batch_lines(p_glbl_data_rec);
4960 --
4961 -- UK specific validation.
4962 --
4963 if p_glbl_data_rec.legislation_code = 'GB' then
4964 pay_uk_bal_upload.validate_batch_lines(p_glbl_data_rec.batch_id);
4965 --
4966 -- Other legislation validation.
4967 --
4968 else
4969 --
4970 --
4971 -- Check the legislation rule BAL_INIT_VALIDATION which determines if the legislation
4972 -- supports additional validation for the batches. The rule is interpreted as follows -
4973 --
4974 -- No rule - call legislation specific validation.
4975 -- Rule exists with NULL value - call legislation specific validation.
4976 -- Rule exists with Y value - call legislation specific validation.
4977 -- Rule exists with N value - do not call legislation specific validation.
4978 --
4979 -- Up until now every legislation has provided a package for this. This change is being
4980 -- introduced to support legislations setup using the International Payroll functionality
4981 -- where we have no pre-determined knowledge of which legislations will be used and
4982 -- therefore cannot guarantee that a package will exist.
4983 --
4984 begin
4985 select nvl(rule_mode, 'N')
4986 into l_validation_supp
4987 from pay_legislation_rules
4988 where legislation_code = p_glbl_data_rec.legislation_code
4989 and rule_type = 'BAL_INIT_VALIDATION';
4990 exception
4991 when no_data_found then
4992 l_validation_supp := 'Y';
4993 end;
4994 --
4995 --
4996 -- Batch validation is supported for the legislation so call relevant package.
4997 --
4998 if l_validation_supp = 'Y' then
4999 --
5000 statem := 'BEGIN
5001 pay_'||lower(p_glbl_data_rec.legislation_code)||'_bal_upload.validate_batch_lines
5002 (:p_batch_id); END;';
5003 --
5004 execute immediate statem
5005 using p_glbl_data_rec.batch_id;
5006 --
5007 end if;
5008 end if;
5009 end if;
5010 --
5011 hr_utility.trace('Exiting pay_balance_upload.validate_batch');
5012 --
5013 end validate_batch;
5014 --
5015 -----------------------------------------------------------------------------
5016 -- NAME
5017 -- transfer_assignment
5018 -- PURPOSE
5019 -- Transfers all the batch lines for an assiugnment onto the system ie.
5020 -- creates balance adjustments to produce the correct initial balances as
5021 -- specified by the batch lines for the assignment.
5022 -- ARGUMENTS
5023 -- p_glbl_data_rec - global data structure.
5024 -- USES
5025 -- calculate_adjustment
5026 -- apply_adjustments
5027 -- NOTES
5028 -----------------------------------------------------------------------------
5029 --
5030 procedure transfer_assignment
5031 (
5032 p_glbl_data_rec in glbl_data_rec_type
5033 ) is
5034 --
5035 -- Record to hold a batch line NB. the cursor is defined at package level.
5036 --
5037 l_batch_line_rec csr_batch_line_transfer%rowtype;
5038 --
5039 -- Holds information about which batch lines are currently being processed
5040 -- along with an error message to be reported agai9nst those batch lines.
5041 --
5042 l_batch_line_list number_array;
5043 l_num_lines number := 0;
5044 l_message varchar2(240);
5045 --
5046 -- An indicator that shows if the assignment is transferable ie. all its
5047 -- batch lines are valid.
5048 --
5049 l_asg_valid boolean := FALSE;
5050 --
5051 conflicts number := 0;
5052 --
5053 -- current number of cached payroll actions
5054 l_payroll_action_num number := g_payroll_actions.count;
5055 --
5056 cursor csr_conflicts
5057 is
5058 select distinct TBA1.batch_line_id
5059 from pay_temp_balance_adjustments TBA1,
5060 pay_temp_balance_adjustments TBA2,
5061 pay_balance_types BT
5062 where TBA1.batch_line_id <> TBA2.batch_line_id
5063 and TBA1.adjustment_date = TBA2.adjustment_date
5064 and TBA1.balance_type_id = TBA2.balance_type_id
5065 and TBA1.adjustment_amount <> 0
5066 and TBA2.adjustment_amount <> 0
5067 and nvl(TBA1.tax_unit_id, -1) = nvl(TBA2.tax_unit_id, -1)
5068 and nvl(TBA1.run_type_id, -1) = nvl(TBA2.run_type_id, -1)
5069 and nvl(TBA1.original_entry_id, -1) = nvl(TBA2.original_entry_id, -1)
5070 and nvl(TBA1.source_id, -1) = nvl(TBA2.source_id, -1)
5071 and nvl(TBA1.source_text, '~null~') = nvl(TBA2.source_text, '~null~')
5072 and nvl(TBA1.source_number, -1) = nvl(TBA2.source_number, -1)
5073 and nvl(TBA1.source_text2, '~null~') = nvl(TBA2.source_text2, '~null~')
5077 --
5074 and BT.balance_type_id = TBA1.balance_type_id
5075 and nvl(substr(TBA1.jurisdiction_code,1,BT.jurisdiction_level), -1) =
5076 nvl(substr(TBA2.jurisdiction_code,1,BT.jurisdiction_level), -1);
5078 begin
5079 --
5080 hr_utility.trace('Entering pay_balance_upload.transfer_assignment');
5081 --
5082 open csr_batch_line_transfer(p_glbl_data_rec.batch_id
5083 ,p_glbl_data_rec.assignment_id);
5084 --
5085 -- Set a savepoint indicating the start of processing for an assignment.
5086 --
5087 savepoint assignment_savepoint;
5088 --
5089 loop
5090 --
5091 -- Get the next batch line for the assignment.
5092 --
5093 fetch csr_batch_line_transfer into l_batch_line_rec;
5094 hr_utility.trace('BATCH_LINE_ID = '||l_batch_line_rec.batch_line_id);
5095 hr_utility.trace('EXPIRY_DATE = '||l_batch_line_rec.expiry_date);
5096 --
5097 -- Terminate the loop either if there are no more batch lines to process
5098 -- or the batch line has been transferred already or is invalid. The
5099 -- transfer of the batch lines for an assignment will only continue if all
5100 -- the batch lines are valid. The batch lines are retrieved in such an
5101 -- order that the transferred and invalid lines appear first. This means
5102 -- that the first batch line indicates whether the transfer can continue
5103 -- ie. if the first batch line is valid then all the batch lines are valid.
5104 --
5105 exit when (l_batch_line_rec.batch_line_status in ('E','T') or
5106 csr_batch_line_transfer%notfound);
5107 --
5108 -- Set an indicator to acknowledge that the assignment is valid ie. all
5109 -- the batch lines for the assignment are valid. This is confirmed by
5110 -- getting past the previous check used with the exit condition of the
5111 -- loop.
5112 --
5113 l_asg_valid := TRUE;
5114 --
5115 -- Calculate the balance adjustment required to set the initial balance
5116 -- as specified by the batch line. Store the details about the balance
5117 -- adjustment for later use.
5118 --
5119 l_num_lines := 1;
5120 l_batch_line_list(l_num_lines) := l_batch_line_rec.batch_line_id;
5121 calculate_adjustment(p_glbl_data_rec
5122 ,l_batch_line_rec);
5123 --
5124 end loop;
5125 --
5126 close csr_batch_line_transfer;
5127 --
5128 -- Check if adjustment dates are valid ie different dimensions of same
5129 -- balance adjusted on same day.
5130 --
5131 -- Bug 3604595. Reset the batch line list for balance conflict lines.
5132 --
5133 conflicts := 0;
5134 l_num_lines := 0;
5135 l_batch_line_list.delete;
5136
5137 for l_conflict_adj in csr_conflicts loop
5138
5139 conflicts := conflicts+1;
5140 l_num_lines := l_num_lines+1;
5141 l_batch_line_list(l_num_lines) := l_conflict_adj.batch_line_id;
5142
5143 end loop;
5144 --
5145 if conflicts <> 0 then
5146 hr_utility.set_message(801, 'PAY_52152_INV_ASS_BALS');
5147 raise hr_utility.hr_error;
5148 end if;
5149 --
5150 -- Create all the balance adjustments for the assignment which will set the
5151 -- initial balances as specified by the batch lines for that assignment.
5152 --
5153 if l_asg_valid then
5154 apply_adjustments(p_glbl_data_rec
5155 ,l_batch_line_list
5156 ,l_num_lines
5157 );
5158 --
5159 -- Now load the latest balances for this assignment.
5160 --
5161 if not p_glbl_data_rec.purge_mode then
5162 load_latest_asg_balances(p_glbl_data_rec
5163 ,l_batch_line_list
5164 ,l_num_lines);
5165 end if;
5166 end if;
5167 --
5168 -- The process is running in VALIDATION mode so the balance adjustments
5169 -- were created as a what if question. Must ensure that they are rolled
5170 -- back.
5171 --
5172 if p_glbl_data_rec.upload_mode = 'VALIDATE' then
5173 rollback to assignment_savepoint;
5174 -- remove the payroll actions cache
5175 g_payroll_actions.delete(l_payroll_action_num+1,g_payroll_actions.count);
5176 end if;
5177 --
5178 --
5179 delete from pay_temp_balance_adjustments;
5180 --
5181 hr_utility.trace('Exiting pay_balance_upload.transfer_assignment');
5182 --
5183 -- The transfer has failed.
5184 --
5185 exception
5186 when hr_utility.hr_error then
5187 --
5188 -- Close the batch line cursor if it is open.
5189 --
5190 if csr_batch_line_transfer%isopen then
5191 close csr_batch_line_transfer;
5192 end if;
5193 --
5194 -- Extract the error message.
5195 --
5196 l_message := substrb(nvl(hr_utility.get_message, sqlerrm), 1, 240);
5197 --
5198 -- Undo all the work relating to the assignment.
5199 --
5200 rollback to assignment_savepoint;
5201 -- remove the payroll actions cache
5202 g_payroll_actions.delete(l_payroll_action_num+1,g_payroll_actions.count);
5203 --
5207 --
5204 -- Write out the error message against all the batch lines that were
5205 -- being processed when the error occured NB. this could be more than one
5206 -- when several batch lines were being set on one balance adjustment.
5208 for l_index in 1..l_num_lines loop
5209 --
5210 -- Write the message against each line that has failed.
5211 --
5212 write_message_line
5213 (p_meesage_level => LINE
5214 ,p_batch_id => null
5215 ,p_batch_line_id => l_batch_line_list(l_index)
5216 ,p_message_text => l_message
5217 ,p_message_token => null);
5218 --
5219 -- Mark each batch line as invalid.
5220 --
5221 update pay_balance_batch_lines BL
5222 set BL.batch_line_status = 'E' -- Error
5223 where BL.batch_line_id = l_batch_line_list(l_index);
5224 --
5225 end loop;
5226 --
5227 delete from pay_temp_balance_adjustments;
5228 --
5229 when others then
5230 --
5231 hr_utility.trace(sqlerrm);
5232 --
5233 -- Extract the error message.
5234 --
5235 l_message := substrb(sqlerrm, 1, 240);
5236 -- Close the batch line cursor if it is open.
5237 --
5238 if csr_batch_line_transfer%isopen then
5239 close csr_batch_line_transfer;
5240 end if;
5241 --
5242 --
5243 -- Undo all the work relating to the assignment.
5244 --
5245 rollback to assignment_savepoint;
5246 -- remove the payroll actions cache
5247 g_payroll_actions.delete(l_payroll_action_num+1,g_payroll_actions.count);
5248 --
5249 for l_index in 1..l_num_lines loop
5250 --
5251 -- Write the message against each line that has failed.
5252 --
5253 write_message_line
5254 (p_meesage_level => LINE
5255 ,p_batch_id => null
5256 ,p_batch_line_id => l_batch_line_list(l_index)
5257 ,p_message_text => l_message
5258 ,p_message_token => null);
5259 --
5260 -- Mark each batch line as invalid.
5261 --
5262 update pay_balance_batch_lines BL
5263 set BL.batch_line_status = 'E' -- Error
5264 where BL.batch_line_id = l_batch_line_list(l_index);
5265 --
5266 end loop;
5267 --
5268 delete from pay_temp_balance_adjustments;
5269 --
5270 end transfer_assignment;
5271 --
5272 -----------------------------------------------------------------------------
5273 -- NAME
5274 -- transfer_batch
5275 -- PURPOSE
5276 -- Transfers a batch onto the system ie. creates balance adjustments to
5277 -- produce the correct initial balances as specified in the batch.
5278 -- ARGUMENTS
5279 -- p_glbl_data_rec - global data structure.
5280 -- USES
5281 -- cache_balances
5282 -- transfer_assignment
5283 -- NOTES
5284 -----------------------------------------------------------------------------
5285 --
5286 procedure transfer_batch
5287 (
5288 p_glbl_data_rec in out nocopy glbl_data_rec_type
5289 ) is
5290 --
5291 -- Retrieves all the assignments found in a batch NB. each batch line is for
5292 -- an assignment.
5293 --
5294 cursor csr_assignment
5295 (
5296 p_batch_id number
5297 ,p_effective_date date
5298 ,p_business_group_id number
5299 ,p_payroll_id number
5300 ) is
5301 select /*+ ORDERED
5302 INDEX(ASG PER_ASSIGNMENTS_F_PK)*/
5303 distinct ASG.assignment_id
5304 from pay_balance_batch_lines BL,
5305 per_all_assignments_f ASG
5306 where BL.batch_id = p_batch_id
5307 and BL.assignment_id = ASG.assignment_id
5308 and ASG.business_group_id = p_business_group_id
5309 and ASG.payroll_id + 0 = p_payroll_id
5310 and p_effective_date between ASG.effective_start_date
5311 and ASG.effective_end_date;
5312 --
5313 -- Keeps a count of the number of assignments that have been processed.
5314 --
5315 l_asg_count number := 0;
5316 --
5317 begin
5318 --
5319 hr_utility.trace('Entering pay_balance_upload.transfer_batch');
5320 --
5321 -- Cache the initial balance feed information for each balance found in
5322 -- the batch.
5323 --
5324 cache_balances;
5325 --
5326 open csr_assignment(p_glbl_data_rec.batch_id
5327 ,p_glbl_data_rec.upload_date
5328 ,p_glbl_data_rec.business_group_id
5329 ,p_glbl_data_rec.payroll_id);
5330 --
5331 -- Repeat for each assignment in the batch.
5332 --
5333 loop
5334 --
5335 -- Get the next assignment.
5336 --
5337 fetch csr_assignment into p_glbl_data_rec.assignment_id;
5338 exit when csr_assignment%notfound;
5339 hr_utility.trace('ASG = '||p_glbl_data_rec.assignment_id);
5340 --
5341 -- Keep a count of the number of assignments that have been processed.
5342 --
5343 l_asg_count := l_asg_count + 1;
5344 --
5345 -- Calculate the balance adjustments required to set the initial balances
5346 -- for the assignment and then create the balance adjustments.
5347 --
5351 -- Commit after every x assignments.
5348 transfer_assignment(p_glbl_data_rec);
5349
5350 --
5352 --
5353 if mod(l_asg_count, p_glbl_data_rec.chunk_size) = 0 then
5354 if not p_glbl_data_rec.purge_mode then
5355 commit;
5356 -- We need relock the batch header.
5357 lock_batch_header(p_glbl_data_rec.batch_id);
5358 end if;
5359 end if;
5360 --
5361 end loop;
5362 --
5363 close csr_assignment;
5364 --
5365 -- If there are any outstanding assignments that have not been committed
5366 -- then commit them.
5367 --
5368 if mod(l_asg_count, p_glbl_data_rec.chunk_size) <> 0 then
5369 if not p_glbl_data_rec.purge_mode then
5370 commit;
5371 -- We need relock the batch header.
5372 lock_batch_header(p_glbl_data_rec.batch_id);
5373 end if;
5374 end if;
5375 --
5376 hr_utility.trace('Exiting pay_balance_upload.transfer_batch');
5377 --
5378 end transfer_batch;
5379 --
5380 -----------------------------------------------------------------------------
5381 -- NAME
5382 -- undo_transfer_batch
5383 -- PURPOSE
5384 -- Rolls back all the balance adjustments made to set the initial values of
5385 -- the balances within the batch.
5386 -- ARGUMENTS
5387 -- p_glbl_data_rec - global data structure.
5388 -- USES
5389 -- py_rollback_pkg.rollback_payroll_action
5390 -- NOTES
5391 -----------------------------------------------------------------------------
5392 --
5393 procedure undo_transfer_batch
5394 (
5395 p_glbl_data_rec in glbl_data_rec_type
5396 ) is
5397 --
5398 -- Record to hold a batch line NB. the cursor is defined at package level.
5399 --
5400 l_batch_line_rec csr_batch_line_undo_transfer%rowtype;
5401 --
5402 -- Setup variables to hold the payroll action id and assignment id.
5403 --
5404 l_pyrl_act_id number;
5405 l_asg_id number;
5406 l_asg_count number;
5407 --
5408 begin
5409 --
5410 hr_utility.trace('Entering pay_balance_upload.undo_transfer_batch');
5411 --
5412 l_asg_count := 0;
5413 --
5414 open csr_batch_line_undo_transfer(p_glbl_data_rec.batch_id);
5415 --
5416 -- Get the first transferred batch line.
5417 --
5418 fetch csr_batch_line_undo_transfer into l_batch_line_rec;
5419 --
5420 -- At least one transferred batch line exists.
5421 --
5422 if csr_batch_line_undo_transfer%found then
5423 --
5424 -- Keep track of the payroll action used to set the balance for the
5425 -- batch line.
5426 --
5427 l_asg_id := l_batch_line_rec.assignment_id;
5428 l_pyrl_act_id := l_batch_line_rec.payroll_action_id;
5429 --
5430 loop
5431 --
5432 -- Get the next transferred batch line.
5433 --
5434 fetch csr_batch_line_undo_transfer into l_batch_line_rec;
5435 --
5436 -- New transferred batch line was not set using the same balance
5437 -- adjustment as the previous one.
5438 --
5439 if l_batch_line_rec.payroll_action_id <> l_pyrl_act_id or
5440 csr_batch_line_undo_transfer%notfound then
5441 --
5442 -- Rollback the balance adjustment.
5443 --
5444 py_rollback_pkg.rollback_payroll_action(l_pyrl_act_id);
5445 --
5446 -- Reset the status of the batch lines for which the payroll action
5447 -- has been rolled back.
5448 -- (#2676349) commented out the assignment condition in the
5449 -- where clause.
5450 update pay_balance_batch_lines BL
5451 set BL.batch_line_status = 'U'
5452 ,BL.payroll_action_id = null
5453 where BL.batch_id = p_glbl_data_rec.batch_id
5454 --and BL.assignment_id = l_asg_id
5455 and BL.payroll_action_id = l_pyrl_act_id;
5456 --
5457 end if;
5458 --
5459 -- Stop when there are no more transferred batch lines.
5460 --
5461 exit when csr_batch_line_undo_transfer%notfound;
5462 --
5463 -- Check the chunk size for the commit unit.
5464 --
5465 if l_asg_id <> l_batch_line_rec.assignment_id then
5466 l_asg_count := l_asg_count + 1;
5467 if mod(l_asg_count, p_glbl_data_rec.chunk_size) = 0 then
5468 if not p_glbl_data_rec.purge_mode then
5469 commit;
5470 end if;
5471 end if;
5472 end if;
5473 --
5474 -- Keep track of the payroll action used to set the balance for the
5475 -- batch line.
5476 --
5477 l_pyrl_act_id := l_batch_line_rec.payroll_action_id;
5478 l_asg_id := l_batch_line_rec.assignment_id;
5479 --
5480 end loop;
5481 --
5482 end if;
5483 --
5484 close csr_batch_line_undo_transfer;
5485 --
5486 if mod(l_asg_count, p_glbl_data_rec.chunk_size) <> 0 then
5487 if not p_glbl_data_rec.purge_mode then
5488 commit;
5489 end if;
5490 end if;
5491 --
5492 hr_utility.trace('Exiting pay_balance_upload.undo_transfer_batch');
5493 --
5494 end undo_transfer_batch;
5495 --
5496 -----------------------------------------------------------------------------
5497 -- NAME
5498 -- purge_batch
5499 -- PURPOSE
5500 -- Removes all data associated with a batch.
5504 -- NOTES
5501 -- ARGUMENTS
5502 -- p_glbl_data_rec - global data structure.
5503 -- USES
5505 -----------------------------------------------------------------------------
5506 --
5507 procedure purge_batch
5508 (
5509 p_glbl_data_rec in glbl_data_rec_type
5510 ) is
5511 --
5512 cursor csr_get_err_lines (p_batch_id in number)
5513 is
5514 select BL.batch_line_id
5515 from pay_balance_batch_lines BL
5516 where BL.batch_id = p_batch_id
5517 and BL.batch_line_status = 'E';
5518 --
5519 begin
5520 --
5521 hr_utility.trace('Entering pay_balance_upload.purge_batch');
5522 --
5523 -- Delete batch line messages.
5524 --
5525 for errline in csr_get_err_lines(p_glbl_data_rec.batch_id) loop
5526 delete from pay_message_lines ML
5527 where ML.message_level = 'F'
5528 and ML.source_type = 'L'
5529 and ML.source_id = errline.batch_line_id;
5530 end loop;
5531 --
5532 -- Delete batch header messages.
5533 --
5534 delete from pay_message_lines ML
5535 where ML.message_level = 'F'
5536 and ML.source_type = 'H'
5537 and ML.source_id = p_glbl_data_rec.batch_id;
5538 --
5539 -- Delete the batch lines for the batch.
5540 --
5541 delete from pay_balance_batch_lines BBL
5542 where BBL.batch_id = p_glbl_data_rec.batch_id;
5543 --
5544 -- Delete the batch header.
5545 --
5546 delete from pay_balance_batch_headers BBH
5547 where BBH.batch_id = p_glbl_data_rec.batch_id;
5548 --
5549 hr_utility.trace('Exiting pay_balance_upload.purge_batch');
5550 --
5551 end purge_batch;
5552 --
5553 -----------------------------------------------------------------------------
5554 -- NAME
5555 -- valid_latest_balance_run
5556 -- PURPOSE
5557 -- This ensures that only balance adjustments are performed for the
5558 -- assignments that the latest balances are to be created.
5559 -- ARGUMENTS
5560 -- p_glbl_data_rec - global data structure.
5561 -- USES
5562 -- NOTES
5563 -----------------------------------------------------------------------------
5564 --
5565 procedure valid_latest_balance_run
5566 (
5567 p_glbl_data_rec in glbl_data_rec_type
5568 ) is
5569 --
5570 cursor csr_get_payroll_actions (p_payroll_id in number,
5571 p_assignment_id in number)
5572 is
5573 select ppa.payroll_action_id
5574 from pay_payroll_actions ppa,
5575 pay_assignment_actions paa
5576 where ppa.payroll_id = p_payroll_id
5577 and ppa.action_type <> 'I'
5578 and paa.payroll_action_id = ppa.payroll_action_id
5579 and paa.assignment_id = p_assignment_id;
5580 --
5581 tmp_payroll_act number;
5582 --
5583 begin
5584 open csr_get_payroll_actions(p_glbl_data_rec.payroll_id,
5585 p_glbl_data_rec.assignment_id);
5586 fetch csr_get_payroll_actions into tmp_payroll_act;
5587 if csr_get_payroll_actions%notfound then
5588 close csr_get_payroll_actions;
5589 return;
5590 end if;
5591 --
5592 close csr_get_payroll_actions;
5593 hr_utility.set_message(801, 'HR_51053_ASA_PREV_PROCESSED');
5594 raise hr_utility.hr_error;
5595 end valid_latest_balance_run;
5596 --
5597 --
5598 -----------------------------------------------------------------------------
5599 -- NAME
5600 -- load_latest_balances
5601 -- PURPOSE
5602 -- Processes a batch of initial balances and will create the latest balances
5603 -- for the assignments.
5604 -- ARGUMENTS
5605 -- p_batch_id - identifies batch being processed.
5606 -- USES
5607 -- lock_batch
5608 -- valid_latest_balance_run
5609 -- load_latest_asg_balances
5610 -- NOTES
5611 -- This should only be used if the latest balances where not loaded by the
5612 -- balance loading process.
5613 -----------------------------------------------------------------------------
5614 --
5615 procedure load_latest_balances
5616 (
5617 p_batch_id in number
5618 ) is
5619 --
5620 -- Declare the global data structure.
5621 --
5622 l_glbl_data_rec glbl_data_rec_type;
5623 l_asg_count number := 0;
5624 l_batch_line_list number_array;
5625 l_num_lines number := 0;
5626 --
5627 -- Retrieves all the assignments found in a batch NB. each batch line is for
5628 -- an assignment.
5629 --
5630 cursor csr_assignment
5631 (
5632 p_batch_id number
5633 ,p_effective_date date
5634 ,p_business_group_id number
5635 ,p_payroll_id number
5636 ) is
5637 select /*+ ORDERED
5638 INDEX(ASG PER_ASSIGNMENTS_F_PK)*/
5639 distinct ASG.assignment_id
5640 from pay_balance_batch_lines BL,
5641 per_all_assignments_f ASG
5642 where BL.batch_id = p_batch_id
5643 and BL.assignment_id = ASG.assignment_id
5644 and BL.batch_line_status = 'T'
5645 and ASG.business_group_id = p_business_group_id
5646 and ASG.payroll_id + 0 = p_payroll_id
5647 and p_effective_date between ASG.effective_start_date
5648 and ASG.effective_end_date;
5649 --
5650 cursor csr_batch_header
5651 (
5652 p_batch_id number
5653 ) is
5657 from per_business_groups_perf pbg,
5654 select pbh.business_group_id,
5655 pbg.legislation_code,
5656 pbh.payroll_id
5658 pay_balance_batch_headers pbh
5659 where pbh.batch_id = p_batch_id
5660 and pbh.business_group_id = pbg.business_group_id;
5661 begin
5662 hr_utility.trace('Entering pay_balance_upload.load_latest_balances');
5663 --
5664 -- Freeze the batch while processing it and initialise the global data
5665 -- structure.
5666 --
5667 lock_batch('TRANSFER'
5668 ,p_batch_id
5669 ,l_glbl_data_rec);
5670 --
5671 open csr_batch_header(p_batch_id);
5672 fetch csr_batch_header into l_glbl_data_rec.business_group_id,
5673 l_glbl_data_rec.legislation_code,
5674 l_glbl_data_rec.payroll_id;
5675 close csr_batch_header;
5676 --
5677 open csr_assignment(l_glbl_data_rec.batch_id
5678 ,l_glbl_data_rec.upload_date
5679 ,l_glbl_data_rec.business_group_id
5680 ,l_glbl_data_rec.payroll_id);
5681 --
5682 -- Repeat for each assignment in the batch.
5683 --
5684 loop
5685 --
5686 -- Get the next assignment.
5687 --
5688 fetch csr_assignment into l_glbl_data_rec.assignment_id;
5689 exit when csr_assignment%notfound;
5690 --
5691 -- Keep a count of the number of assignments that have been processed.
5692 --
5693 l_asg_count := l_asg_count + 1;
5694 --
5695 -- Calculate the balance adjustments required to set the initial balances
5696 -- for the assignment and then create the balance adjustments.
5697 --
5698 valid_latest_balance_run(l_glbl_data_rec);
5699 load_latest_asg_balances(l_glbl_data_rec
5700 ,l_batch_line_list
5701 ,l_num_lines);
5702 end loop;
5703 --
5704 close csr_assignment;
5705 --
5706 -- If there are any outstanding assignments that have not been committed
5707 -- then commit them.
5708 --
5709 commit;
5710 --
5711 hr_utility.trace('Exiting pay_balance_upload.load_latest_balances');
5712 end load_latest_balances;
5713 -----------------------------------------------------------------------------
5714 -- NAME
5715 -- post_transfer_batch
5716 -- PURPOSE
5717 -- Performs the rest of tasks to complete the batch transfer.
5718 --
5719 -- ARGUMENTS
5720 -- p_glbl_data_rec - global data structure.
5721 -- USES
5722 -- pay_bal_adjust.process_batch
5723 -- NOTES
5724 --
5725 -----------------------------------------------------------------------------
5726 --
5727 procedure post_transfer_batch
5728 (p_glbl_data_rec in glbl_data_rec_type
5729 )
5730 is
5731 l_proc varchar2(80) := ' pay_balance_upload.post_transfer_batch';
5732
5733 --
5734 -- Retrieves incomplete payroll actions.
5735 --
5736 cursor csr_batch
5737 is
5738 select
5739 distinct ppa.payroll_action_id
5740 from
5741 pay_payroll_actions ppa
5742 ,pay_balance_batch_lines pbbl
5743 where
5744 ppa.action_status <> 'C'
5745 and ppa.payroll_action_id = pbbl.payroll_action_id
5746 and pbbl.batch_line_status = 'T'
5747 and pbbl.batch_id = p_glbl_data_rec.batch_id
5748 ;
5749 begin
5750 --
5751 hr_utility.set_location('Entering:'||l_proc, 5);
5752
5753 if p_glbl_data_rec.upload_mode = 'TRANSFER' then
5754
5755 for l_batch in csr_batch loop
5756 --
5757 hr_utility.trace(' payroll_action_id=:'||l_batch.payroll_action_id);
5758 --
5759 -- Completes the payroll action.
5760 --
5761 pay_bal_adjust.process_batch(l_batch.payroll_action_id);
5762 --
5763 end loop;
5764
5765 end if;
5766
5767 hr_utility.set_location('Leaving:'||l_proc, 50);
5768
5769 end post_transfer_batch;
5770
5771 -----------------------------------------------------------------------------
5772 -- NAME
5773 -- validate_transfer_batch
5774 -- PURPOSE
5775 -- Performs validation and transfer of the batch.
5776 --
5777 -- ARGUMENTS
5778 -- p_glbl_data_rec - global data structure.
5779 -- USES
5780 -- validate_batch
5781 -- transfer_batch
5782 -- post_transfer_batch
5783 -- NOTES
5784 --
5785 -----------------------------------------------------------------------------
5786 --
5787 procedure validate_transfer_batch
5788 (p_glbl_data_rec in glbl_data_rec_type
5789 )
5790 is
5791 l_proc varchar2(80) := ' pay_balance_upload.validate_transfer_batch';
5792 l_message varchar2(240);
5793 l_glbl_data_rec glbl_data_rec_type:= p_glbl_data_rec;
5794 begin
5795 --
5796 hr_utility.set_location('Entering:'||l_proc, 5);
5797
5798 --
5799 -- Validate the batch.
5800 --
5801 validate_batch(l_glbl_data_rec);
5802
5803 --
5804 -- Reset the payroll actions cache.
5805 --
5806 g_payroll_actions.delete;
5807
5808 --
5809 -- Transfer the batch NB. in 'VALIDATE' mode the transfer is done
5810 -- to see if the transfer would work. The transfer can only continue
5811 -- if the batch header is valid.
5812 --
5816 post_transfer_batch(l_glbl_data_rec);
5813 if l_glbl_data_rec.batch_header_status = 'V' then
5814 transfer_batch(l_glbl_data_rec);
5815
5817 end if;
5818
5819 --
5820 -- Set the status of the batch.
5821 --
5822 set_batch_status(l_glbl_data_rec);
5823
5824 hr_utility.set_location('Leaving:'||l_proc, 50);
5825 --
5826 exception
5827 --
5828 -- Treats the exception trapped here as a batch header error.
5829 --
5830 when hr_utility.hr_error then
5831 hr_utility.set_location(l_proc, 55);
5832
5833 l_message := substrb(nvl(hr_utility.get_message, sqlerrm), 1, 240);
5834
5835 write_message_line
5836 (p_meesage_level => HEADER
5837 ,p_batch_id => l_glbl_data_rec.batch_id
5838 ,p_batch_line_id => null
5839 ,p_message_text => l_message
5840 ,p_message_token => null
5841 );
5842
5843 update pay_balance_batch_headers
5844 set batch_status = 'E'
5845 where batch_id = l_glbl_data_rec.batch_id;
5846
5847 when others then
5848 hr_utility.set_location(l_proc, 60);
5849
5850 l_message := substrb(sqlerrm, 1, 240);
5851
5852 write_message_line
5853 (p_meesage_level => HEADER
5854 ,p_batch_id => l_glbl_data_rec.batch_id
5855 ,p_batch_line_id => null
5856 ,p_message_text => l_message
5857 ,p_message_token => null
5858 );
5859
5860 update pay_balance_batch_headers
5861 set batch_status = 'E'
5862 where batch_id = l_glbl_data_rec.batch_id;
5863
5864 end validate_transfer_batch;
5865 -----------------------------------------------------------------------------
5866 -- NAME
5867 -- process
5868 -- PURPOSE
5869 -- Processes a batch of initial balances and will either validate the batch,
5870 -- transfer the initial balances to the system ,purge the batch or undo the
5871 -- transfer of a batch.
5872 -- ARGUMENTS
5873 -- errbuf - error message string used by SRS.
5874 -- retcode - return code for SRS, 0 - Success, 1 - Warning, 2 - Error.
5875 -- p_mode - can be 'VALIDATE', 'TRANSFER', 'PURGE', or 'UNDO'.
5876 -- p_batch_id - identifies batch being processed.
5877 -- USES
5878 -- lock_batch
5879 -- validate_batch
5880 -- transfer_batch
5881 -- purge_batch
5882 -- undo_transfer_batch
5883 -- NOTES
5884 -- Can be run from SRS.
5885 -----------------------------------------------------------------------------
5886 --
5887 procedure process
5888 (
5889 errbuf out nocopy varchar2
5890 ,retcode out nocopy number
5891 ,p_mode in varchar2
5892 ,p_batch_id in number
5893 ) is
5894 --
5895 -- Declare the global data structure.
5896 --
5897 l_glbl_data_rec glbl_data_rec_type;
5898 --
5899 -- Indicates that an unsupported mode was passed.
5900 --
5901 l_invalid_mode boolean := FALSE;
5902 --
5903 -- Status of Batch
5904 --
5905 l_batch_status varchar2(30);
5906 --
5907 -- Null batch record to initialize.
5908 --
5909 l_null_batch_info t_batch_info_rec;
5910 --
5911 begin
5912 --
5913 hr_utility.trace('Entering pay_balance_upload.process');
5914 status_indicator := SRS_SUCCESS; -- Success
5915 g_legislation_code := null;
5916 --
5917 -- Reset the batch info.
5918 --
5919 g_batch_info := l_null_batch_info;
5920 --
5921 -- Check whether batch is currently being processed by another process
5922 -- by checking if batch header batch_status = 'L'.
5923 --
5924 select BBH.batch_status
5925 into l_batch_status
5926 from pay_balance_batch_headers BBH
5927 where BBH.batch_id = p_batch_id;
5928 --
5929 if l_batch_status = 'L' then
5930 --
5931 -- Set the return code and message for SRS.
5932 --
5933 hr_utility.trace('pay_balance_upload.process: batch locked');
5934 status_indicator := SRS_ERROR; -- Error
5935 errbuf := 'Batch currently being processed by another process';
5936 else
5937 --
5938 -- Remove previous messages
5939 --
5940 remove_messages(p_batch_id);
5941 --
5942 -- Freeze the batch while processing it and initialise the global data
5943 -- structure.
5944 --
5945 lock_batch(p_mode
5946 ,p_batch_id
5947 ,l_glbl_data_rec);
5948 --
5949 -- The batch is being purged.
5950 --
5951 if l_glbl_data_rec.upload_mode = 'PURGE' then
5952 --
5953 -- Remove all the data for the batch.
5954 --
5955 purge_batch(l_glbl_data_rec);
5956 --
5957 -- The transfer of a batch is being undone ie. the balance adjustments are
5958 -- to be rolled back.
5959 --
5960 elsif l_glbl_data_rec.upload_mode = 'UNDO' then
5961 --
5962 -- Rollback any balance adjustments made during the transfer of the batch.
5963 --
5964 undo_transfer_batch(l_glbl_data_rec);
5965 --
5966 -- Set the status of the batch.
5967 --
5968 set_batch_status(l_glbl_data_rec);
5969 --
5970 -- The batch is either being validated or transferred.
5971 --
5972 elsif l_glbl_data_rec.upload_mode in ('VALIDATE','TRANSFER') then
5973 --
5974 -- Validate and transfer the batch.
5975 --
5976 validate_transfer_batch(l_glbl_data_rec);
5977 --
5978 -- An invalid mode has been specified.
5979 --
5980 else
5981 l_invalid_mode := TRUE;
5982 end if;
5983 end if;
5984 --
5985 -- Reset the batch info.
5986 --
5987 g_batch_info := l_null_batch_info;
5988 --
5989 --
5990 -- Set the return code and message for SRS.
5991 --
5992 retcode := status_indicator;
5993 if l_invalid_mode then
5994 retcode := SRS_ERROR; -- Error
5995 errbuf := 'Invalid mode';
5996 end if;
5997 --
5998 hr_utility.trace('Exiting pay_balance_upload.process');
5999 --
6003 g_gre_tbl_nxt := 1;
6000 end process;
6001 --
6002 begin
6004 g_runtyp_tbl_nxt := 1;
6005 end pay_balance_upload;
[Home] [Help]