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