[Home] [Help]
PACKAGE BODY: APPS.PAYPLNK
Source
1 package body payplnk as
2 /* $Header: paylink.pkb 120.7.12020000.2 2012/07/06 11:27:34 vmaripal ship $ */
3 --
4 --
5 -- GLOBAL DECLARATIONS
6 --
7 type varchar2_table2 is table of varchar2(240)
8 index by binary_integer;
9 --
10 type varchar2_table80 is table of varchar2(80)
11 index by binary_integer;
12 --
13 g_header_error boolean;
14 g_control_error boolean;
15 g_line_error boolean;
16 g_header_processing boolean := FALSE;
17 g_process_mode varchar2(50);
18 g_user_status pay_batch_headers.batch_status%TYPE;
19 g_count binary_integer := 0;
20 --
21 g_line_id_tbl hr_entry.number_table;
22 g_status_tbl hr_entry.varchar2_table;
23 g_message_tbl varchar2_table2;
24 --
25 g_control_count number :=0;
26 g_ctl_id_tbl hr_entry.number_table;
27 g_ctl_stat_tbl hr_entry.varchar2_table;
28 g_ctl_mess_tbl varchar2_table2;
29 --
30 g_head_err_msg pay_message_lines.line_text%TYPE := null;
31 g_head_err_stat varchar(1) := null;
32 --
33 error_occurred exception;
34 --
35 cursor csr_all_lines (l_batch_id in number) is
36 select *
37 from pay_batch_lines bal
38 where bal.batch_id = l_batch_id
39 order by bal.batch_line_id
40 for update;
41 --
42 cursor csr_asg_lines (l_batch_id in number, l_asg_id in number) is
43 select *
44 from pay_batch_lines bal
45 where bal.assignment_id = l_asg_id
46 and bal.batch_id = l_batch_id
47 and bal.batch_line_status not in ('T','E') /* Bug 13814081 */
48 order by bal.batch_sequence,bal.batch_line_id
49 for update;
50 --
51 cursor csr_all_controls (l_batch_id in number) is
52 select *
53 from pay_batch_control_totals bac
54 where bac.batch_id = l_batch_id
55 and bac.control_status <> 'T'
56 for update;
57 --
58 cursor csr_header (l_batch_id in number) is
59 select *
60 from pay_batch_headers bah
61 where bah.batch_id = l_batch_id
62 for update;
63 --
64 -- This was introduced to overcome the performace within the
65 -- validate_lines procedure.
66 --
67 cursor csr_bl_header (l_batch_id in number) is
68 select *
69 from pay_batch_headers bah
70 where bah.batch_id = l_batch_id;
71 --
72 cursor csr_status_chk (l_user_status in varchar2) is
73 select 'x'
74 from hr_lookups hlk
75 where hlk.lookup_type = 'BATCH_STATUS'
76 and sysdate between nvl(hlk.start_date_active,sysdate)
77 and nvl(hlk.end_date_active,
78 hr_general.end_of_time)
79 and hlk.enabled_flag = 'Y'
80 and hlk.lookup_code = upper(l_user_status);
81 --
82 g_header_record csr_header%ROWTYPE;
83 g_control_record csr_all_controls%ROWTYPE;
84 g_line_record csr_all_lines%ROWTYPE;
85 --
86 -- FORWARD DECLARATIONS
87 --
88 procedure validate_header
89 (
90 p_batch_id in number,
91 p_business_group_id in number,
92 p_leg_header_check in boolean
93 );
94 --
95 procedure validate_controls
96 (
97 p_batch_id in number
98 );
99 --
100 --This is now included within the package header.
101 -- procedure validate_lines
102 -- (
103 -- p_process_mode in varchar,
104 -- p_line_id_tbl in out hr_entry.number_table,
105 -- p_status_tbl in out hr_entry.varchar2_table,
106 -- p_message_tbl in out varchar2_table2,
107 -- p_batch_id in number,
108 -- p_business_group_id in number
109 -- p_leg_line_check in boolean
110 -- );
111 --
112 procedure purge_messages
113 (
114 p_batch_id in number,
115 p_mode in varchar2
116 );
117 --
118 procedure insert_element_entry
119 (
120 p_link_id in number,
121 l_line_record in out nocopy csr_all_lines%ROWTYPE,
122 p_asg_act_id in number,
123 p_creator_type in varchar2,
124 p_absence_attendance_id in number,
125 p_entry_values_count in number,
126 p_passed_inp_tbl in hr_entry.number_table,
127 p_passed_val_tbl in hr_entry.varchar2_table
128 );
129 --
130 procedure update_element_entry
131 (
132 p_update_mode in varchar2,
133 p_element_entry_id in number,
134 p_creator_type in varchar2,
135 p_creator_id in number,
136 p_allow_rollback in boolean,
137 p_asg_act_id in number,
138 l_line_record in csr_all_lines%ROWTYPE,
139 p_entry_values_count in number,
140 p_passed_inp_tbl in hr_entry.number_table,
141 p_passed_val_tbl in hr_entry.varchar2_table
142 );
143 --
144 -- -------------------------------------------------------------------------
145 -- Procedure to convert input values from internal format to display format.
146 -- -------------------------------------------------------------------------
147 function convert_internal_to_display
148 (p_input_value varchar2,
149 p_uom_value varchar2,
150 p_lookup_type varchar2,
151 p_value_set_id number,
152 p_currency_code varchar2)
153 return varchar2 is
154 --
155 /*Changed size from 80 to 240, Modified for 9350651*/
156 l_display_value varchar2(240) := p_input_value;
157 l_internal_value varchar2(240) := p_input_value;
158 l_dummy varchar2(100);
159 --
160 cursor csr_valid_lookup
161 (p_lookup_type varchar2,
162 p_lookup_code varchar2) is
163 select HL.meaning
164 from hr_lookups HL
165 where HL.lookup_type = p_lookup_type
166 and HL.lookup_code = p_lookup_code;
167 --
168 begin
169 --
170 hr_utility.set_location('payplnk.convert_internal_to_display',10);
171 if (p_lookup_type is not null and
172 l_internal_value is not null) then
173 --
174 open csr_valid_lookup(p_lookup_type, l_internal_value);
175 fetch csr_valid_lookup into l_display_value ;
176 close csr_valid_lookup;
177 hr_utility.set_location('payplnk.convert_internal_to_display',15);
178 --
179 elsif (p_value_set_id is not null and
180 l_internal_value is not null) then
181 --
182 l_display_value := pay_input_values_pkg.decode_vset_value(
183 p_value_set_id, l_internal_value);
184 hr_utility.set_location('payplnk.convert_internal_to_display',20);
185 --
186 else
187 --
188 hr_utility.set_location('payplnk.convert_internal_to_display',21);
189 hr_chkfmt.changeformat (
190 l_internal_value, /* the value to be formatted (out - display) */
191 l_display_value, /* the formatted value on output (out - canonical) */
192 p_uom_value, /* the format to check */
193 p_currency_code );
194 --
195 hr_utility.set_location('payplnk.convert_internal_to_display',25);
196 end if;
197 hr_utility.set_location('payplnk.convert_internal_to_display',30);
198 --
199 return l_display_value;
200 --
201 exception
202 when others then
203 hr_utility.set_message ('PAY','PAY_6306_INPUT_VALUE_FORMAT');
204 hr_utility.set_message_token ('UNIT_OF_MEASURE', hr_general.decode_lookup ('UNITS', p_uom_value ));
205 hr_utility.raise_error;
206 --
207 end convert_internal_to_display;
208 --
209 --
210 function get_upgrade_status
211 (p_business_group_id number
212 ,p_short_name varchar2
213 )return varchar2 is
214 --
215 l_status pay_upgrade_status.status%type;
216 --
217 begin
218 --
219 pay_core_utils.get_upgrade_status(p_business_group_id,p_short_name,l_status);
220 --
221 return l_status;
222 --
223 exception
224 when others then
225 --
226 return 'E';
227 --
228 end;
229 --
230 --
231 -----------------------------------------------------------------------
232 -- NAME --
233 -- payplnk.run_process --
234 -- --
235 -- DESCRIPTION --
236 -- The main procedure caled from the SRS screen, from which the --
237 -- paylink process is triggered for a particular batch. --
238 -- The success status of the process, or not, as the case maybe, --
239 -- is returned back to the SRS screen. --
240 -----------------------------------------------------------------------
241 --
242 procedure run_process
243 (
244 errbuf out nocopy varchar2,
245 retcode out nocopy number,
246 p_business_group_id in number,
247 p_batch_operation in varchar2,
248 p_batch_id in number
249 ) is
250 --
251
252 --
253 -- LOCAL DECLARATIONS
254 --
255
256 l_dummy varchar2(1) := null;
257 l_row_not_exists boolean := FALSE;
258 --
259 l_bee_iv_upgrade varchar2(1);
260 --
261 begin
262 --
263 -- Initialise variables.
264 --
265 g_header_error := false;
266 g_control_error := false;
267 g_line_error := false;
268 g_count := 0;
269 g_process_mode := p_batch_operation;
270 g_header_processing := false;
271 l_bee_iv_upgrade := get_upgrade_status(p_business_group_id,'BEE_IV_UPG');
272 --
273 --
274 SAVEPOINT RP;
275 --
276 --
277 --
278 -- Check whether the upgrade process is in progress.
279 --
280 if l_bee_iv_upgrade = 'E' then
281 hr_utility.set_message(800, 'HR_449106_BEE_UPGRADING');
282 hr_utility.raise_error;
283 end if;
284 --
285 -- Need a row in fnd_sessions if it does not exist (bug 372339).
286 -- It may exist because next run_process may use same concurrent manager session
287 -- (this will not be neccessary when hr_utility.fnd_insert will do the check for
288 -- existing row before its insert).
289 --
290 begin
291 select null
292 into l_dummy
293 from fnd_sessions
294 where session_id = userenv('sessionid');
295 exception
296 when no_data_found then
297 l_row_not_exists := TRUE;
298 end;
299 if l_row_not_exists then
300 hr_utility.fnd_insert(sysdate);
301 end if;
302 --
303 --
304 -- Depending on the mode of operation requested the particular batch
305 -- will be processed accordingly.
306 --
307 hr_utility.set_location('payplnk.run_process',5);
308 --
309 if (p_batch_operation = 'VALIDATE' OR p_batch_operation = 'TRANSFER') then
310 --
311 -- validate the batch.
312 --
313 hr_utility.set_location('payplnk.run_process',6);
314 --
315 g_header_processing := TRUE;
316 --
317 payplnk.validate
318 (
319 p_business_group_id,
320 p_batch_operation,
321 p_batch_id
322 );
323 --
324 set_header_status(p_business_group_id,p_batch_operation,p_batch_id);
325 --
326 g_header_processing := FALSE;
327 --
328 payplnk.g_payplnk_call := TRUE;
329 --
330 hr_utility.set_location('payplnk.run_process',7);
331 --
332 -- elsif p_batch_operation = 'TRANSFER' then
333 -- --
334 -- -- transfer the batch. NB The transfer procedure calls the
335 -- -- validate procedure to ensure the batch is valid before
336 -- -- tansferring the element entries into the appropriate
337 -- -- base tables.
338 -- --
339 -- hr_utility.set_location('payplnk.run_process',8);
340 -- payplnk.transfer
341 -- (
342 -- p_business_group_id,
343 -- p_batch_operation,
344 -- p_batch_id
345 -- );
346 -- hr_utility.set_location('payplnk.run_process',9);
347 --
348 elsif p_batch_operation = 'PURGE' then
349 --
350 -- purge the batch. All records associated with the batch i.e.
351 -- batch messages, controls, lines and header will be deleted
352 -- from the temporary batch tables.
353 --
354 hr_utility.set_location('payplnk.run_process',10);
355 payplnk.purge
356 (
357 p_batch_id
358 );
359 hr_utility.set_location('payplnk.run_process',11);
360 --
361 end if;
362 --
363 hr_utility.set_location('payplnk.run_process',15);
364 --
365 -- commit;
366 --
367 errbuf := null;
368 retcode := 0;
369 --
370 --
371 exception
372 when others then
373 rollback to RP;
374 errbuf := sqlerrm; /* return the unhandled error message. */
375 retcode := 2; /* process failed, as an unhandled error
376 occurred. */
377 if p_batch_operation <> 'PURGE' then
378 hr_utility.set_message(800, 'HR_289718_BEE_HEADER_ERROR');
379 end if;
380 hr_utility.raise_error;
381 --
382 end run_process;
383 --
384 ------------------------------------------------------------------------
385 -- NAME --
386 -- payplnk.validate --
387 -- --
388 -- DESCRIPTION --
389 -- Given a batch id it will validate the batch and insert its --
390 -- element entries into the necessary base tables. Then depending on --
391 -- the mode of operation passed in i.e. VALIDATE or TRANSFER the --
392 -- inserted element entries will either be ROLLED BACK or not. --
393 ------------------------------------------------------------------------
394 --
395 procedure validate
396 (
397 p_business_group_id in number,
398 p_batch_operation in varchar2,
399 p_batch_id in number
400 ) is
401 --
402 -- LOCAL DECLARATIONS
403 --
404 l_error_count binary_integer :=0;
405 l_line_match boolean := false;
406 --
407 l_line_id_tbl hr_entry.number_table;
408 l_status_tbl hr_entry.varchar2_table;
409 l_message_tbl varchar2_table2;
410 --
411 sql_curs number;
412 rows_processed integer;
413 statem varchar2(256);
414 l_header_check number;
415 l_leg_header_check boolean := false;
416 l_line_check number;
417 l_leg_line_check boolean := false;
418 l_legislation_code per_business_groups_perf.legislation_code%TYPE := null;
419 --
420 l_assignment_id pay_batch_lines.assignment_id%TYPE := null;
421 l_error_text pay_message_lines.line_text%TYPE := null;
422 l_assignment_exists varchar2(1) := null;
423 not_upper boolean := false;
424 --
425 begin
426 --
427 hr_utility.set_location('payplnk.validate',1);
428 --
429 SAVEPOINT VL;
430 --
431 hr_utility.set_location('payplnk.validate',5);
432 --
433 select legislation_code
434 into l_legislation_code
435 from per_business_groups_perf
436 where business_group_id = p_business_group_id;
437 --
438 --
439 -- Find out of legislative checks at header and lines to
440 -- be performed.
441 --
442 -- (i) legislative header check
443 --
444 begin
445 statem := 'BEGIN
446 :header_check := pay_'||lower(l_legislation_code)||'_bee.header_check_supported; END;';
447 --
448 sql_curs := dbms_sql.open_cursor;
449 --
450 dbms_sql.parse(sql_curs,
451 statem,
452 dbms_sql.v7);
453 --
454 dbms_sql.bind_variable(sql_curs, 'header_check', l_header_check);
455 --
456 rows_processed := dbms_sql.execute(sql_curs);
457 --
458 dbms_sql.variable_value(sql_curs, 'header_check', l_header_check);
459 --
460 dbms_sql.close_cursor(sql_curs);
461 --
462 if l_header_check = 0 then
463 l_leg_header_check := TRUE;
464 else
465 l_leg_header_check := FALSE;
466 end if;
467 --
468 --
469 exception
470 when others then
471 l_leg_header_check := FALSE;
472 --
473 if dbms_sql.is_open(sql_curs) then
474 dbms_sql.close_cursor(sql_curs);
475 end if;
476 --
477 end;
478 --
479 -- This has been moved into the validate_lines.
480 -- -- (ii) legislative line check
481 -- --
482 -- begin
483 -- statem := 'BEGIN
484 -- :line_check := pay_'||lower(l_legislation_code)||'_bee.line_check_supported; END;';
485 -- --
486 -- sql_curs := dbms_sql.open_cursor;
487 -- --
488 -- dbms_sql.parse(sql_curs,
489 -- statem,
490 -- dbms_sql.v7);
491 -- --
492 -- dbms_sql.bind_variable(sql_curs, 'line_check', l_line_check);
493 -- --
494 -- rows_processed := dbms_sql.execute(sql_curs);
495 -- --
496 -- dbms_sql.variable_value(sql_curs, 'line_check', l_line_check);
497 -- --
498 -- dbms_sql.close_cursor(sql_curs);
499 -- --
500 -- if l_line_check = 0 then
501 -- l_leg_line_check := TRUE;
502 -- else
503 -- l_leg_line_check := FALSE;
504 -- end if;
505 -- --
506 -- exception
507 -- when others then
508 -- l_leg_line_check := FALSE;
509 -- --
510 -- if dbms_sql.is_open(sql_curs) then
511 -- dbms_sql.close_cursor(sql_curs);
512 -- end if;
513 -- --
514 -- end;
515 --
516 -- Validates the batch header details held in pay_batch_headers.
517 --
518 validate_header (
519 p_batch_id,
520 p_business_group_id,
521 l_leg_header_check
522 );
523 --
524 -- Only when the batch header details have been validated successfully
525 -- are the associated controls validated and checked against any
526 -- user defined control checks for the particular batch.
527 --
528 if g_header_error = true then
529 raise error_occurred;
530 else
531 open csr_all_controls(p_batch_id);
532 fetch csr_all_controls into g_control_record;
533 if csr_all_controls%FOUND then
534 close csr_all_controls;
535 validate_controls (
536 p_batch_id
537 );
538 elsif csr_all_controls%NOTFOUND then
539 close csr_all_controls;
540 end if;
541 end if;
542 --
543 hr_utility.set_location('payplnk.validate',10);
544 --
545 -- Any updates made to the batch header and control statuses, and
546 -- and messages resulting from the header and control checks will be
547 -- committed.
548 --
549 -- commit;
550 --
551 -- Only if the header and control(s) have been validated successfully will
552 -- the associated lines be validated.
553 --
554 if g_control_error = true then
555 raise error_occurred;
556 else
557 open csr_all_lines(p_batch_id);
558 fetch csr_all_lines into g_line_record;
559 if csr_all_lines%FOUND then
560 close csr_all_lines;
561 -- Validate Batch lines will be called further below as a
562 -- multithreaded process.
563 -- validate_lines (
564 -- l_line_id_tbl,
565 -- l_status_tbl,
566 -- l_message_tbl,
567 -- p_batch_id,
568 -- p_business_group_id,
569 -- l_leg_line_check );
570 else
571 close csr_all_lines;
572 -- Bug 3186708: No longer raises error if no lines exists.
573 -- raise error_occurred;
574 end if;
575 end if;
576 --
577 hr_utility.set_location('payplnk.validate',15);
578 -- The following is comment out due to multithreading.
579 -- --
580 -- -- Successfully validated line(s) may as a result have created
581 -- -- element entrie(s) in the base tables, therefore the entries must be
582 -- -- ROLLED BACK as the requested batch operation was 'VALIDATE'.
583 -- --
584 -- if p_batch_operation = 'VALIDATE' then
585 -- rollback;
586 -- --
587 -- -- Else the batch operation was 'TRANSFER'. Check if atleast one
588 -- -- error occurred during lines validation.
589 -- --
590 -- elsif (p_batch_operation = 'TRANSFER') and (g_line_error = true) then
591 -- rollback;
592 -- -- Safe now to reset the batch status from 'P' to original.
593 -- update pay_batch_headers bah
594 -- set bah.batch_status = decode(upper(g_user_status),
595 -- 'W','V',
596 -- upper(g_user_status))
597 -- where bah.batch_id = p_batch_id;
598 -- else
599 -- commit;
600 -- end if;
601 -- --
602 -- hr_utility.set_location('payplnk.validate',20);
603 -- --
604 -- -- Any messages accumulated during batch lines validation are now
605 -- -- inserted into the pay_message_lines table.
606 -- --
607 -- begin
608 -- for l_error_count in 1..g_count loop
609 -- if l_message_tbl(l_error_count) is not null then
610 -- --
611 -- insert into pay_message_lines
612 -- (LINE_SEQUENCE,
613 -- PAYROLL_ID,
614 -- MESSAGE_LEVEL,
615 -- SOURCE_ID,
616 -- SOURCE_TYPE,
617 -- LINE_TEXT)
618 -- values (
619 -- pay_message_lines_s.nextval,
620 -- null,
621 -- l_status_tbl(l_error_count),
622 -- l_line_id_tbl(l_error_count),
623 -- 'L',
624 -- l_message_tbl(l_error_count));
625 -- end if;
626 -- end loop;
627 -- --
628 -- exception
629 -- when no_data_found then
630 -- null;
631 -- end;
632 -- --
633 -- hr_utility.set_location('payplnk.validate',25);
634 -- --
635 -- -- The status of each batch line is set according to the outcome
636 -- -- of the validation carried out for the particular line.
637 -- --
638 -- for g_line_record in csr_all_lines(p_batch_id) loop
639 -- begin
640 -- for l_error_count in 1..g_count loop
641 -- if (g_line_record.batch_line_id = l_line_id_tbl(l_error_count)) then
642 -- --
643 -- update pay_batch_lines bal
644 -- set bal.batch_line_status =
645 -- decode(l_status_tbl(l_error_count),'F','E','V')
646 -- where current of csr_all_lines;
647 -- --
648 -- l_line_match := true;
649 -- --
650 -- if (l_status_tbl(l_error_count) = 'F') then
651 -- exit;
652 -- end if;
653 -- end if;
654 -- end loop;
655 -- --
656 -- exception
657 -- when no_data_found then
658 -- null;
659 -- end;
660 -- --
661 -- hr_utility.set_location('payplnk.validate',30);
662 -- --
663 -- if l_line_match = false then
664 -- --
665 -- update pay_batch_lines bal
666 -- set bal.batch_line_status = 'V'
667 -- where current of csr_all_lines;
668 -- else
669 -- l_line_match := false;
670 -- end if;
671 -- end loop;
672 -- --
673 -- End of the code blocked for multithreading.
674 hr_utility.set_location('payplnk.validate',35);
675 --
676 for g_line_record in csr_all_lines(p_batch_id) loop
677 --
678 if g_line_record.effective_date is null then
679 hr_utility.set_message(801,'HR_7449_PLK_COLUMN_NULL');
680 hr_utility.set_message_token('COLUMN_NAME','EFFECTIVE DATE');
681 l_error_text := substrb(hr_utility.get_message, 1, 240);
682 --
683 insert into pay_message_lines
684 (LINE_SEQUENCE,
685 PAYROLL_ID,
686 MESSAGE_LEVEL,
687 SOURCE_ID,
688 SOURCE_TYPE,
689 LINE_TEXT)
690 values (
691 pay_message_lines_s.nextval,
692 null,
693 'F',
694 g_line_record.batch_line_id,
695 'L',
696 l_error_text);
697 --
698 update pay_batch_lines
699 set batch_line_status = 'E'
700 where current of csr_all_lines;
701 else
702 --
703 -- If only an assignment number has been entered, ensure it is valid.
704 --
705 if (g_line_record.assignment_id is null) and
706 (g_line_record.assignment_number is not null) then
707 begin
708 select asg.assignment_id
709 into l_assignment_id
710 from per_assignments_f asg
711 where asg.assignment_number =
712 g_line_record.assignment_number
713 and g_line_record.effective_date between asg.effective_start_date
714 and asg.effective_end_date
715 and asg.business_group_id + 0 = p_business_group_id;
716 --
717 g_line_record.assignment_id := l_assignment_id;
718 not_upper := false;
719 --
720 update pay_batch_lines
721 set assignment_id = l_assignment_id
722 where current of csr_all_lines;
723 --
724 exception
725 when no_data_found then
726 not_upper := true;
727 --
728 when too_many_rows then
729 --
730 hr_utility.set_message(801,'HR_7449_PLK_COLUMN_NULL');
731 hr_utility.set_message_token('COLUMN_NAME','ASSIGNMENT ID');
732 l_error_text := substrb(hr_utility.get_message, 1, 240);
733 --
734 insert into pay_message_lines
735 (LINE_SEQUENCE,
736 PAYROLL_ID,
737 MESSAGE_LEVEL,
738 SOURCE_ID,
739 SOURCE_TYPE,
740 LINE_TEXT)
741 values (
742 pay_message_lines_s.nextval,
743 null,
744 'F',
745 g_line_record.batch_line_id,
746 'L',
747 l_error_text);
748 --
749 update pay_batch_lines
750 set batch_line_status = 'E'
751 where current of csr_all_lines;
752 --
753 end;
754 -- If upper case is not found, try again for mixed case
755 --
756 begin
757 if(not_upper = true) then
758 select asg.assignment_id
759 into l_assignment_id
760 from per_assignments_f asg
761 where upper(asg.assignment_number) =
762 upper(g_line_record.assignment_number)
763 and g_line_record.effective_date between
764 asg.effective_start_date
765 and asg.effective_end_date
766 and asg.business_group_id = p_business_group_id;
767 --
768 update pay_batch_lines
769 set assignment_id = l_assignment_id
770 where current of csr_all_lines;
771 --
772 end if;
773 exception
774 when no_data_found then
775 --
776 hr_utility.set_message(801,'HR_7466_PLK_NOT_ELGBLE_ASS_NUM');
777 hr_utility.set_message_token('ASSIGNMENT_NUMBER',
778 g_line_record.assignment_number);
779 l_error_text := substrb(hr_utility.get_message, 1, 240);
780 --
781 insert into pay_message_lines
782 (LINE_SEQUENCE,
783 PAYROLL_ID,
784 MESSAGE_LEVEL,
785 SOURCE_ID,
786 SOURCE_TYPE,
787 LINE_TEXT)
788 values (
789 pay_message_lines_s.nextval,
790 null,
791 'F',
792 g_line_record.batch_line_id,
793 'L',
794 l_error_text);
795 --
796 update pay_batch_lines
797 set batch_line_status = 'E'
798 where current of csr_all_lines;
799 --
800 --
801 when too_many_rows then
802 --
803 hr_utility.set_message(801,'HR_7449_PLK_COLUMN_NULL');
804 hr_utility.set_message_token('COLUMN_NAME','ASSIGNMENT ID');
805 l_error_text := substrb(hr_utility.get_message, 1, 240);
806 --
807 insert into pay_message_lines
808 (LINE_SEQUENCE,
809 PAYROLL_ID,
810 MESSAGE_LEVEL,
811 SOURCE_ID,
812 SOURCE_TYPE,
813 LINE_TEXT)
814 values (
815 pay_message_lines_s.nextval,
816 null,
817 'F',
818 g_line_record.batch_line_id,
819 'L',
820 l_error_text);
821 --
822 update pay_batch_lines
823 set batch_line_status = 'E'
824 where current of csr_all_lines;
825 --
826 end;
827 --
828 hr_utility.set_location('payplnk.validate',40);
829 --
830 elsif (g_line_record.assignment_id is null) and
831 (g_line_record.assignment_number is null) then
832 --
833 hr_utility.set_message(801,'HR_7482_PLK_BOTH_COLUMNS_NULL');
834 hr_utility.set_message_token('COL1','ASSIGNMENT NUMBER');
835 hr_utility.set_message_token('COL2','ASSIGNMENT ID');
836 l_error_text := substrb(hr_utility.get_message, 1, 240);
837 --
838 insert into pay_message_lines
839 (LINE_SEQUENCE,
840 PAYROLL_ID,
841 MESSAGE_LEVEL,
842 SOURCE_ID,
843 SOURCE_TYPE,
844 LINE_TEXT)
845 values (
846 pay_message_lines_s.nextval,
847 null,
848 'F',
849 g_line_record.batch_line_id,
850 'L',
851 l_error_text);
852 --
853 update pay_batch_lines
854 set batch_line_status = 'E'
855 where current of csr_all_lines;
856 --
857 --
858 hr_utility.set_location('payplnk.validate',45);
859 --
860 elsif (g_line_record.assignment_id is not null) and
861 (g_line_record.assignment_number is not null) then
862 begin
863 select 'x'
864 into l_assignment_exists
865 from per_assignments_f asg
866 where asg.assignment_number =
867 g_line_record.assignment_number
868 and asg.assignment_id = g_line_record.assignment_id
869 and g_line_record.effective_date between asg.effective_start_date
870 and asg.effective_end_date
871 and asg.business_group_id + 0 = p_business_group_id;
872 --
873 not_upper := false;
874 --
875 exception
876 when no_data_found then
877 not_upper := true;
878 end;
879 --
880 --
881 --
882 begin
883 if(not_upper = true) then
884 select 'x'
885 into l_assignment_exists
886 from per_assignments_f asg
887 where upper(asg.assignment_number) =
888 upper(g_line_record.assignment_number)
889 and asg.assignment_id = g_line_record.assignment_id
890 and g_line_record.effective_date between asg.effective_start_date
891 and asg.effective_end_date
892 and asg.business_group_id +0 = p_business_group_id;
893 end if;
894 exception
895 when no_data_found then
896 --
897 hr_utility.set_message(801,'HR_7479_PLK_INCONSISTENT_ASS');
898 hr_utility.set_message_token('ASSIGNMENT_ID',
899 g_line_record.assignment_id);
900 hr_utility.set_message_token('ASSIGNMENT_NUMBER',
901 g_line_record.assignment_number);
902 l_error_text := substrb(hr_utility.get_message, 1, 240);
903 --
904 insert into pay_message_lines
905 (LINE_SEQUENCE,
906 PAYROLL_ID,
907 MESSAGE_LEVEL,
908 SOURCE_ID,
909 SOURCE_TYPE,
910 LINE_TEXT)
911 values (
912 pay_message_lines_s.nextval,
913 null,
914 'F',
915 g_line_record.batch_line_id,
916 'L',
917 l_error_text);
918 --
919 update pay_batch_lines
920 set batch_line_status = 'E'
921 where current of csr_all_lines;
922 --
923 end;
924 --
925 hr_utility.set_location('payplnk.validate',50);
926 --
927 -- If only an assignment id has been entered, ensure that it is valid.
928 --
929 else
930 begin
931 select 'x'
932 into l_assignment_exists
933 from per_assignments_f asg
934 where asg.assignment_id = g_line_record.assignment_id
935 and g_line_record.effective_date between asg.effective_start_date
936 and asg.effective_end_date
937 and asg.business_group_id +0 = p_business_group_id;
938 --
939 exception
940 when no_data_found then
941 --
942 hr_utility.set_message(801,'HR_7467_PLK_NOT_ELGBLE_ASS_ID');
943 hr_utility.set_message_token('ASSIGNMENT_ID',
944 g_line_record.assignment_id);
945 l_error_text := substrb(hr_utility.get_message, 1, 240);
946 --
947 insert into pay_message_lines
948 (LINE_SEQUENCE,
949 PAYROLL_ID,
950 MESSAGE_LEVEL,
951 SOURCE_ID,
952 SOURCE_TYPE,
953 LINE_TEXT)
954 values (
955 pay_message_lines_s.nextval,
956 null,
957 'F',
958 g_line_record.batch_line_id,
959 'L',
960 l_error_text);
961 --
962 update pay_batch_lines
963 set batch_line_status = 'E'
964 where current of csr_all_lines;
965 --
966 end;
967 --
968 end if;
969 --
970 hr_utility.set_location('payplnk.validate',55);
971 --
972 end if;
973 --
974 end loop;
975 --
976 hr_utility.set_location('payplnk.validate',60);
977 --
978 -- COMMIT;
979 --
980 --
981 hr_utility.set_location('payplnk.validate',85);
982 --
983 exception
984 when error_occurred then
985 rollback to VL;
986 raise;
987 --
988 --
989 when others then
990 rollback to VL;
991 raise;
992 --
993 end validate;
994 --
995 -----------------------------------------------------------------------
996 -- NAME --
997 -- payplnk.set_status --
998 -- --
999 -- DESCRIPTION --
1000 -- Given a batch id and the mode of operation it will set the status --
1001 -- of the batch and control totals. --
1002 -----------------------------------------------------------------------
1003 --
1004 procedure set_status
1005 (
1006 p_business_group_id in number,
1007 p_batch_operation in varchar2,
1008 p_batch_id in number
1009 ) is
1010 --
1011 l_errors_exists varchar2(1) := null;
1012 l_batch_status pay_batch_headers.batch_status%TYPE := null;
1013 l_status pay_batch_headers.batch_status%TYPE := null;
1014 l_purge_after_transfer pay_batch_headers.purge_after_transfer%TYPE := null;
1015 --
1016 cursor csr_error_lines is
1017 select 'Y'
1018 from pay_batch_lines pbl
1019 where pbl.batch_line_status = 'E'
1020 and pbl.batch_id = p_batch_id;
1021 --
1022 cursor csr_error_totals is
1023 select 'Y'
1024 from pay_batch_control_totals pct
1025 where pct.control_status = 'E'
1026 and pct.batch_id = p_batch_id;
1027 --
1028 cursor csr_error_header is
1029 select 'Y'
1030 from pay_batch_headers pbh
1031 where pbh.batch_status = 'E'
1032 and pbh.batch_id = p_batch_id;
1033 --
1034 cursor csr_header_status is
1035 select pbh.batch_status,
1036 pbh.purge_after_transfer
1037 from pay_batch_headers pbh
1038 where pbh.batch_id = p_batch_id;
1039 --
1040 begin
1041 --
1042 hr_utility.set_location('payplnk.set_status',5);
1043 --
1044 SAVEPOINT SS;
1045 --
1046 g_line_error := false;
1047 g_control_error := false;
1048 g_header_error := false;
1049 --
1050 open csr_header_status;
1051 fetch csr_header_status into l_batch_status,l_purge_after_transfer;
1052 close csr_header_status;
1053 --
1054 -- Continue with this section only if the batch status is 'P'.
1055 if (l_batch_status <> 'P') then
1056 return;
1057 end if;
1058 --
1059 if (p_batch_operation = 'TRANSFER') then
1060 l_status := 'T';
1061 else
1062 l_status := 'V';
1063 end if;
1064 --
1065 open csr_error_lines;
1066 fetch csr_error_lines into l_errors_exists;
1067 if csr_error_lines%FOUND then
1068 g_line_error := true;
1069 end if;
1070 close csr_error_lines;
1071 --
1072 open csr_error_totals;
1073 fetch csr_error_totals into l_errors_exists;
1074 if csr_error_totals%FOUND then
1075 g_control_error := true;
1076 end if;
1077 close csr_error_totals;
1078 --
1079 open csr_error_header;
1080 fetch csr_error_header into l_errors_exists;
1081 if csr_error_header%FOUND then
1082 g_header_error := true;
1083 end if;
1084 close csr_error_header;
1085 --
1086
1087 if (g_header_error = true) then
1088 hr_utility.trace('payplnk.set_status g_header_error = TRUE');
1089 else
1090 hr_utility.trace('payplnk.set_status g_header_error = FALSE');
1091 end if;
1092
1093 if (g_control_error = true) then
1094 hr_utility.trace('payplnk.set_status g_control_error = TRUE');
1095 else
1096 hr_utility.trace('payplnk.set_status g_control_error = FALSE');
1097 end if;
1098
1099 if (g_line_error = true) then
1100 hr_utility.trace('payplnk.set_status g_line_error = TRUE');
1101 else
1102 hr_utility.trace('payplnk.set_status g_line_error = FALSE');
1103 end if;
1104 --
1105 if (p_batch_operation = 'VALIDATE' and g_header_error = false) then
1106 --
1107 update pay_batch_headers
1108 set batch_status = l_status
1109 where batch_id = p_batch_id;
1110 else
1111 --
1112 -- If no errors were detected then continue i.e. purge/transfer records.
1113 --
1114 if (g_header_error = false) and (g_control_error = false) then
1115 -- Once the element entries have been committed check if the
1116 -- PURGE_AFTER_TRANSFER flag is 'Y'. If so delete all records associated
1117 -- with the batch from the temporary batch tables.
1118 --
1119 hr_utility.set_location('payplnk.set_status',11);
1120 --
1121 if (g_line_error = false) and
1122 upper(l_purge_after_transfer) = 'Y' then
1123 hr_utility.set_location('payplnk.set_status',30);
1124 purge (
1125 p_batch_id
1126 );
1127 --
1128 else
1129 --
1130 hr_utility.set_location('payplnk.set_status',15);
1131 --
1132 if g_control_error = false then
1133 for g_control_record in csr_all_controls(p_batch_id) loop
1134 update pay_batch_control_totals
1135 set control_status = l_status
1136 where current of csr_all_controls;
1137 --
1138 end loop;
1139 end if;
1140 --
1141 hr_utility.set_location('payplnk.set_status',20);
1142 --
1143 update pay_batch_headers
1144 set batch_status = l_status
1145 where batch_id = p_batch_id;
1146 --
1147 hr_utility.set_location('payplnk.set_status',25);
1148 --
1149 end if;
1150 --
1151 hr_utility.set_location('payplnk.set_status',30);
1152 end if;
1153 --
1154 end if;
1155 --
1156 hr_utility.set_location('payplnk.set_status',35);
1157 --
1158 exception
1159 when others then
1160 rollback to SS;
1161 --
1162 end set_status;
1163 --
1164 -----------------------------------------------------------------------
1165 -- NAME --
1166 -- payplnk.set_line_status --
1167 -- --
1168 -- DESCRIPTION --
1169 -- Given a batch id and the mode of operation it will set the status --
1170 -- of the batch lines. --
1171 -----------------------------------------------------------------------
1172 --
1173 procedure set_line_status
1174 (
1175 p_business_group_id in number,
1176 p_batch_operation in varchar2,
1177 p_batch_id in number,
1178 p_asg_id in number
1179 ) is
1180 --
1181 l_batch_line_status pay_batch_lines.batch_line_status%TYPE := null;
1182 l_error_count binary_integer :=0;
1183 l_line_match boolean := false;
1184 --
1185 --
1186 begin
1187 --
1188 hr_utility.set_location('payplnk.set_line_status',5);
1189 --
1190 -- Set the global parameter to disable the triggers.
1191 payplnk.g_payplnk_call := true;
1192 --
1193 SAVEPOINT SLS;
1194 --
1195 --
1196 if p_batch_operation = 'VALIDATE' then
1197 l_batch_line_status := 'V';
1198 elsif (p_batch_operation = 'TRANSFER') and (g_line_error = true) then
1199 l_batch_line_status := 'V';
1200 else
1201 l_batch_line_status := 'T';
1202 end if;
1203 --
1204 hr_utility.set_location('payplnk.set_line_status',10);
1205 --
1206 -- Any messages accumulated during batch lines validation are now
1207 -- inserted into the pay_message_lines table.
1208 --
1209 begin
1210 for l_error_count in 1..g_count loop
1211 if g_message_tbl(l_error_count) is not null then
1212 --
1213 insert into pay_message_lines
1214 (LINE_SEQUENCE,
1215 PAYROLL_ID,
1216 MESSAGE_LEVEL,
1217 SOURCE_ID,
1218 SOURCE_TYPE,
1219 LINE_TEXT)
1220 values (
1221 pay_message_lines_s.nextval,
1222 null,
1223 g_status_tbl(l_error_count),
1224 g_line_id_tbl(l_error_count),
1225 'L',
1226 g_message_tbl(l_error_count));
1227 end if;
1228 end loop;
1229 --
1230 exception
1231 when no_data_found then
1232 null;
1233 end;
1234 --
1235 hr_utility.set_location('payplnk.set_line_status',20);
1236 --
1237 -- The status of each batch line is set according to the outcome
1238 -- of the validation carried out for the particular line.
1239 --
1240 for g_line_record in csr_asg_lines(p_batch_id, p_asg_id) loop
1241 begin
1242 for l_error_count in 1..g_count loop
1243 if (g_line_record.batch_line_id = g_line_id_tbl(l_error_count)) then
1244 --
1245 update pay_batch_lines bal
1246 set bal.batch_line_status =
1247 decode(l_batch_line_status,
1248 'T',decode(g_status_tbl(l_error_count),'F','E','T'),
1249 decode(g_status_tbl(l_error_count),'F','E','V'))
1250 where current of csr_asg_lines;
1251 --
1252 l_line_match := true;
1253 --
1254 -- Bug 2854485
1255 -- There could be more than one message exists for a batch line.
1256 -- Hence, only skip the check if the status is fatal.
1257 --
1258 if g_status_tbl(l_error_count) = 'F' then
1259 exit;
1260 end if;
1261 --
1262 end if;
1263 end loop;
1264 --
1265 exception
1266 when no_data_found then
1267 null;
1268 end;
1269 --
1270 hr_utility.set_location('payplnk.set_line_status',30);
1271 --
1272 if l_line_match = false then
1273 --
1274 update pay_batch_lines bal
1275 set bal.batch_line_status = l_batch_line_status
1276 where current of csr_asg_lines;
1277 else
1278 l_line_match := false;
1279 end if;
1280 end loop;
1281 --
1282 -- Empty global messages.
1283 g_count := 0;
1284 g_line_error := false;
1285 g_line_id_tbl.delete;
1286 g_status_tbl.delete;
1287 g_message_tbl.delete;
1288 --
1289 -- Set the global parameter to enable the triggers.
1290 payplnk.g_payplnk_call := false;
1291 --
1292 --
1293 hr_utility.set_location('payplnk.set_line_status',35);
1294 --
1295 exception
1296 when others then
1297 rollback to SLS;
1298 --
1299 end set_line_status;
1300 --
1301 -----------------------------------------------------------------------
1302 -- NAME --
1303 -- payplnk.set_header_status --
1304 -- --
1305 -- DESCRIPTION --
1306 -- Given a batch id and the mode of operation it will set the status --
1307 -- of the batch lines. --
1308 -----------------------------------------------------------------------
1309 --
1310 procedure set_header_status
1311 (
1312 p_business_group_id in number,
1313 p_batch_operation in varchar2,
1314 p_batch_id in number
1315 ) is
1316 --
1317 l_error_count binary_integer :=0;
1318 l_line_match boolean := false;
1319 --
1320 --
1321 begin
1322 --
1323 hr_utility.set_location('payplnk.set_header_status',5);
1324 --
1325 -- Set the global parameter to disable the triggers.
1326 payplnk.g_payplnk_call := true;
1327 --
1328 SAVEPOINT SHS;
1329 --
1330 if g_header_processing then
1331 --
1332 if g_head_err_msg is not null then
1333 insert into pay_message_lines
1334 (LINE_SEQUENCE,
1335 PAYROLL_ID,
1336 MESSAGE_LEVEL,
1337 SOURCE_ID,
1338 SOURCE_TYPE,
1339 LINE_TEXT)
1340 values (
1341 pay_message_lines_s.nextval,
1342 null,
1343 g_head_err_stat,
1344 p_batch_id,
1345 'H',
1346 g_head_err_msg);
1347
1348 if g_head_err_stat = 'F' then
1349 update pay_batch_headers
1350 set batch_status = 'E'
1351 where batch_id = p_batch_id;
1352 end if;
1353 end if;
1354 --
1355 --
1356 hr_utility.set_location('payplnk.set_header_status',10);
1357 --
1358 -- Only update the status of the cotnrol totals if the batc his not failed.
1359 if g_head_err_stat <> 'F' or g_head_err_stat is null then
1360 --
1361 -- Any messages accumulated during batch lines validation are now
1362 -- inserted into the pay_message_lines table.
1363 --
1364 begin
1365 for l_error_count in 1..g_control_count loop
1366 if g_ctl_mess_tbl(l_error_count) is not null then
1367 --
1368 insert into pay_message_lines
1369 (LINE_SEQUENCE,
1370 PAYROLL_ID,
1371 MESSAGE_LEVEL,
1372 SOURCE_ID,
1373 SOURCE_TYPE,
1374 LINE_TEXT)
1375 values (
1376 pay_message_lines_s.nextval,
1377 null,
1378 g_ctl_stat_tbl(l_error_count),
1379 g_ctl_id_tbl(l_error_count),
1380 'C',
1381 g_ctl_mess_tbl(l_error_count));
1382 end if;
1383 end loop;
1384 --
1385 exception
1386 when no_data_found then
1387 null;
1388 end;
1389 --
1390 hr_utility.set_location('payplnk.set_header_status',20);
1391 --
1392 -- The status of each batch total is set according to the outcome
1393 -- of the validation carried out for the particular total.
1394 --
1395 for g_control_record in csr_all_controls(p_batch_id) loop
1396 begin
1397 for l_error_count in 1..g_control_count loop
1398 if (g_control_record.batch_control_id = g_ctl_id_tbl(l_error_count)) then
1399 --
1400 update pay_batch_control_totals ctl
1401 set ctl.control_status =
1402 decode(g_ctl_stat_tbl(l_error_count),'F','E','V')
1403 where current of csr_all_controls;
1404 --
1405 l_line_match := true;
1406 --
1407 exit;
1408 --
1409 end if;
1410 end loop;
1411 --
1412 exception
1413 when no_data_found then
1414 null;
1415 end;
1416 --
1417 hr_utility.set_location('payplnk.set_header_status',30);
1418 --
1419 if l_line_match = false then
1420 --
1421 update pay_batch_control_totals ctl
1422 set ctl.control_status = 'V'
1423 where current of csr_all_controls;
1424 else
1425 l_line_match := false;
1426 end if;
1427 end loop;
1428 --
1429 end if;
1430 --
1431 else
1432 --
1433 -- This will be called if the payroll action status being set. This will be
1434 -- either at the end or when there are too many errors while
1435 -- validating lines.
1436 --
1437 update pay_batch_control_totals
1438 set control_status = decode(p_batch_operation,'TRANSFER','T','VALIDATE','V','P')
1439 where batch_id = p_batch_id
1440 and control_status = 'V'
1441 and exists
1442 ( select null
1443 from pay_batch_headers pbh
1444 where pbh.batch_id = p_batch_id
1445 and pbh.batch_status = 'P');
1446 --
1447 update pay_batch_headers
1448 set batch_status = decode(p_batch_operation,'TRANSFER','T','VALIDATE','V','P')
1449 where batch_id = p_batch_id
1450 and batch_status = 'P';
1451 --
1452 end if;
1453 --
1454 -- Empty global messages.
1455 g_control_count := 0;
1456 g_control_error := false;
1457 g_ctl_id_tbl.delete;
1458 g_ctl_stat_tbl.delete;
1459 g_ctl_mess_tbl.delete;
1460 g_head_err_stat := null;
1461 g_head_err_msg := null;
1462 g_header_processing := false;
1463 --
1464 -- Set the global parameter to enable the triggers.
1465 payplnk.g_payplnk_call := false;
1466 --
1467 --
1468 hr_utility.set_location('payplnk.set_header_status',35);
1469 --
1470 exception
1471 when others then
1472 rollback to SHS;
1473 --
1474 end set_header_status;
1475 --
1476 --
1477 -----------------------------------------------------------------------
1478 -- NAME --
1479 -- payplnk.purge --
1480 -- --
1481 -- DESCRIPTION --
1482 -- Given a batch id it will delete all records associated with the --
1483 -- batch from all the temporary batch tables. Any messages --
1484 -- associated with the batch will also be deleted from the --
1485 -- PAY_MESSAGE_LINES table. --
1486 -----------------------------------------------------------------------
1487 --
1488 procedure purge
1489 (
1490 p_batch_id in number
1491 ) is
1492 --
1493 l_ovn number;
1494 --
1495 cursor csr_ovn is
1496 select pbh.object_version_number
1497 from pay_batch_headers pbh
1498 where pbh.batch_id = p_batch_id;
1499 --
1500 begin
1501 --
1502 SAVEPOINT PU;
1503 hr_utility.set_location('payplnk.purge',5);
1504 --
1505 open csr_ovn;
1506 fetch csr_ovn into l_ovn;
1507 close csr_ovn;
1508 --
1509 -- Set the global parameter to enable the triggers.
1510 payplnk.g_payplnk_call := true;
1511 --
1512 --
1513 --
1514 -- Calls the BEE API to delete the batch.
1515 --
1516 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_header
1517 (p_batch_id => p_batch_id
1518 ,p_object_version_number => l_ovn
1519 );
1520 --
1521 -- Set the global parameter to enable the triggers.
1522 payplnk.g_payplnk_call := false;
1523 --
1524 --
1525 hr_utility.set_location('payplnk.purge',8);
1526 --
1527 --
1528 /*
1529 purge_messages(p_batch_id,'Y');
1530 --
1531 hr_utility.set_location('payplnk.purge',10);
1532 --
1533 delete from pay_batch_control_totals
1534 where batch_id = p_batch_id;
1535 --
1536 hr_utility.set_location('payplnk.purge',15);
1537 --
1538 delete from pay_batch_lines
1539 where batch_id = p_batch_id;
1540 --
1541 hr_utility.set_location('payplnk.purge',20);
1542 --
1543 delete from pay_batch_headers
1544 where batch_id = p_batch_id;
1545 --
1546 hr_utility.set_location('payplnk.purge',25);
1547 --
1548 */
1549 --
1550 exception
1551 when others then
1552 rollback to PU;
1553 raise;
1554 --
1555 end purge;
1556 --
1557 ----------------------------------------------------------------------
1558 -- NAME --
1559 -- payplnk.validate_header PRIVATE PROCEDURE --
1560 -- --
1561 -- DESCRIPTION --
1562 -- Validates batch header details. This includes the user defined --
1563 -- checks for the header as well as the core validation checks --
1564 -- carried out by the process. If and only if the header has been --
1565 -- validated successfully do we continue onto the next stage of --
1566 -- validation i.e. VALIDATE_CONTROLS. --
1567 ----------------------------------------------------------------------
1568 --
1569 procedure validate_header
1570 (
1571 p_batch_id in number,
1572 p_business_group_id in number,
1573 p_leg_header_check in boolean
1574 ) is
1575 --
1576 -- LOCAL DECLARATIONS
1577 --
1578 l_transfer_recs varchar2(1) := null;
1579 l_process_recs varchar2(1) := null;
1580 l_column_name1 varchar2(30):= null;
1581 l_usr_status_chk varchar2(1) := null;
1582 l_error_text pay_message_lines.line_text%TYPE := null;
1583 l_user_status pay_batch_headers.batch_status%TYPE := null;
1584 l_user_message pay_message_lines.line_text%TYPE := null;
1585 sql_curs number;
1586 rows_processed integer;
1587 statem varchar2(256);
1588 l_valid number;
1589 l_leg_message pay_message_lines.line_text%TYPE := null;
1590 l_legislation_code per_business_groups_perf.legislation_code%TYPE := null;
1591 --
1592 column_is_null exception;
1593 invalid_value exception;
1594 user_error exception;
1595 core_error exception;
1596 nopackbody exception;
1597 pragma exception_init(nopackbody,-6508);
1598 --
1599 begin
1600 --
1601 hr_utility.set_location('payplnk.validate_header',5);
1602 --
1603 select legislation_code
1604 into l_legislation_code
1605 from per_business_groups_perf
1606 where business_group_id = p_business_group_id;
1607 --
1608 -- Delete any messages that may have resulted from an OLD operation
1609 -- carried out on the batch.
1610 --
1611 purge_messages(p_batch_id,'N');
1612 --
1613 -- Initialize the global error message parameters.
1614 g_head_err_msg := null;
1615 g_head_err_stat := null;
1616 g_header_error := false;
1617 --
1618 SAVEPOINT BH;
1619 --
1620 -- commit;
1621 --
1622 hr_utility.set_location('payplnk.validate_header',10);
1623 --
1624 -- Check if transferred record(s) exist in the batch, if so, terminate
1625 -- validation with an appropriate message.
1626 --
1627 begin
1628 select 'x'
1629 into l_transfer_recs
1630 from sys.dual
1631 where exists
1632 (select null
1633 from pay_batch_headers bah
1634 , pay_batch_lines bal
1635 , pay_batch_control_totals bac
1636 where bah.batch_id = bal.batch_id(+)
1637 and bah.batch_id = bac.batch_id(+)
1638 and bah.batch_id = p_batch_id
1639 and ((bah.batch_status not in ('T','P')
1640 and (bal.batch_line_status = 'T'
1641 or bac.control_status = 'T'))));
1642 exception
1643 when no_data_found then
1644 null;
1645 end;
1646 --
1647 hr_utility.set_location('payplnk.validate_header',15);
1648 --
1649 if l_transfer_recs = 'x' then
1650 --
1651 -- Atleast one transferred record has been detected in the batch,
1652 -- therefore the batch will not be validated. An appropriate error
1653 -- message will be inserted into the PAY_MESSAGE_LINES table.
1654 --
1655 --
1656 hr_utility.set_message(801,'HR_7472_PLK_TRANSFERRED_RECS');
1657 hr_utility.set_message_token('BATCH_ID',p_batch_id);
1658 l_error_text := substrb(hr_utility.get_message, 1, 240);
1659 --
1660 -- insert into pay_message_lines
1661 -- (LINE_SEQUENCE,
1662 -- PAYROLL_ID,
1663 -- MESSAGE_LEVEL,
1664 -- SOURCE_ID,
1665 -- SOURCE_TYPE,
1666 -- LINE_TEXT)
1667 -- values (
1668 -- pay_message_lines_s.nextval,
1669 -- null,
1670 -- 'F',
1671 -- p_batch_id,
1672 -- 'H',
1673 -- l_error_text);
1674 --
1675 g_head_err_msg := l_error_text;
1676 g_head_err_stat := 'F';
1677 --
1678 raise core_error;
1679 end if;
1680 --
1681 hr_utility.set_location('payplnk.validate_header',20);
1682 --
1683 -- The core validation checks will be carried out first.
1684 -- Check the batch status is not already PROCESSING.
1685 --
1686 savepoint A;
1687 select batch_status into l_process_recs
1688 from pay_batch_headers
1689 where batch_id = p_batch_id
1690 for update;
1691 hr_utility.trace('batch_status ='||l_process_recs);
1692 hr_utility.trace('processing mode = '||g_process_mode);
1693 --
1694 -- IF batch is in processing state, lock out others processing.
1695 -- if(g_process_mode <> 'VALIDATE') then
1696 if(l_process_recs = 'P') then
1697 rollback to A; -- release lock
1698 hr_utility.trace('batch is in processing state');
1699 hr_utility.set_message(801,'HR_MIX_289133_PROCESS_STATE');
1700 hr_utility.set_message_token('BATCH_ID',p_batch_id);
1701 l_error_text := substrb(hr_utility.get_message, 1, 240);
1702 --
1703 -- insert into pay_message_lines
1704 -- (LINE_SEQUENCE,
1705 -- PAYROLL_ID,
1706 -- MESSAGE_LEVEL,
1707 -- SOURCE_ID,
1708 -- SOURCE_TYPE,
1709 -- LINE_TEXT)
1710 -- values (
1711 -- pay_message_lines_s.nextval,
1712 -- null,
1713 -- 'F',
1714 -- p_batch_id,
1715 -- 'H',
1716 -- l_error_text);
1717 --
1718 g_head_err_msg := l_error_text;
1719 g_head_err_stat := 'F';
1720 --
1721 raise core_error;
1722 end if;
1723 update pay_batch_headers bah
1724 set bah.batch_status = 'P'
1725 where bah.batch_id = p_batch_id;
1726 -- commit;
1727 -- else
1728 -- update pay_batch_headers bah
1729 -- set bah.batch_status = 'U'
1730 -- where bah.batch_id = p_batch_id;
1731 -- commit;
1732 -- end if;
1733 --
1734 hr_utility.set_location('payplnk.validate_header',25);
1735 --
1736 update pay_batch_control_totals bac
1737 set bac.control_status = 'U'
1738 where bac.batch_id = p_batch_id
1739 and bac.control_status <> 'T';
1740 --
1741 hr_utility.set_location('payplnk.validate_header',30);
1742 --
1743 update pay_batch_lines bal
1744 set bal.batch_line_status = 'U'
1745 where bal.batch_id = p_batch_id
1746 and bal.batch_line_status <> 'T';
1747 --
1748 -- commit;
1749 --
1750 hr_utility.set_location('payplnk.validate_header',35);
1751 --
1752 if l_process_recs <> 'T' then
1753 --
1754 open csr_header(p_batch_id);
1755 fetch csr_header into g_header_record;
1756 --
1757 -- Check that the correct business group id has be entered.
1758 --
1759 if g_header_record.business_group_id <> p_business_group_id then
1760 l_column_name1 := 'BUSINESS GROUP ID';
1761 --
1762 raise invalid_value;
1763 --
1764 -- Check that a value has been entered for the PURGE_AFTER_TRANSFER flag
1765 --
1766 elsif g_header_record.purge_after_transfer is null then
1767 l_column_name1 := 'PURGE AFTER TRANSFER';
1768 --
1769 raise column_is_null;
1770 end if;
1771 --
1772 -- Core checks have been validated successfully, hence
1773 -- legislative hook checks (if any) and then user defined
1774 -- checks for the header can now be carried out.
1775 --
1776 --
1777 -- call legislative header check hook if required
1778 --
1779 hr_utility.set_location('payplnk.validate_header',36);
1780 if p_leg_header_check = TRUE then
1781 --
1782 begin
1783 statem := 'BEGIN
1784 pay_'||lower(l_legislation_code)||'_bee.validate_header(:batch_id, :valid, :leg_message); END;';
1785 --
1786 sql_curs := dbms_sql.open_cursor;
1787 --
1788 dbms_sql.parse(sql_curs,
1789 statem,
1790 dbms_sql.v7);
1791 --
1792 dbms_sql.bind_variable(sql_curs, 'batch_id', p_batch_id);
1793 dbms_sql.bind_variable(sql_curs, 'valid', l_valid);
1794 dbms_sql.bind_variable(sql_curs, 'leg_message', l_leg_message, 240);
1795 --
1796 rows_processed := dbms_sql.execute(sql_curs);
1797 --
1798 dbms_sql.variable_value(sql_curs, 'valid', l_valid);
1799 dbms_sql.variable_value(sql_curs, 'leg_message', l_leg_message);
1800 --
1801 dbms_sql.close_cursor(sql_curs);
1802 --
1803 exception
1804 when others then
1805 --
1806 if dbms_sql.is_open(sql_curs) then
1807 dbms_sql.close_cursor(sql_curs);
1808 end if;
1809 --
1810 -- update pay_batch_headers bah
1811 -- set bah.batch_status = 'E'
1812 -- where current of csr_header;
1813 --
1814 hr_utility.set_message(801,'HR_7481_PLK_USR_CHECK_ERROR');
1815 hr_utility.set_message_token('USER_PROCEDURE',
1816 'the legislative batch header procedure');
1817 l_error_text := substrb((hr_utility.get_message||' '||sqlerrm),1,240);
1818 --
1819 hr_utility.trace(sqlerrm);
1820 -- insert into pay_message_lines
1821 -- (LINE_SEQUENCE,
1822 -- PAYROLL_ID,
1823 -- MESSAGE_LEVEL,
1824 -- SOURCE_ID,
1825 -- SOURCE_TYPE,
1826 -- LINE_TEXT)
1827 -- values (
1828 -- pay_message_lines_s.nextval,
1829 -- null,
1830 -- 'F',
1831 -- p_batch_id,
1832 -- 'H',
1833 -- l_error_text);
1834 --
1835 g_head_err_msg := l_error_text;
1836 g_head_err_stat := 'F';
1837 --
1838 raise user_error;
1839 end;
1840 --
1841 if l_valid = 0 then
1842 -- update pay_batch_headers bah
1843 -- set bah.batch_status = 'E'
1844 -- where current of csr_header;
1845 --
1846 hr_utility.set_message(801,'HR_7469_PLK_USR_STATUS_INVLD');
1847 hr_utility.set_message_token('USER_PROCEDURE',
1848 'the legislative batch header procedure');
1849 l_error_text := substrb((hr_utility.get_message||' '||l_leg_message),1,240);
1850 --
1851 -- insert into pay_message_lines
1852 -- (LINE_SEQUENCE,
1853 -- PAYROLL_ID,
1854 -- MESSAGE_LEVEL,
1855 -- SOURCE_ID,
1856 -- SOURCE_TYPE,
1857 -- LINE_TEXT)
1858 -- values (
1859 -- pay_message_lines_s.nextval,
1860 -- null,
1861 -- 'F',
1862 -- p_batch_id,
1863 -- 'H',
1864 -- l_error_text);
1865 --
1866 g_head_err_msg := l_error_text;
1867 g_head_err_stat := 'F';
1868 --
1869 raise user_error;
1870 end if;
1871 --
1872 end if;
1873 --
1874 -- call user defined checks
1875 --
1876 l_user_status := 'V';
1877 g_user_status := l_user_status;
1878 --
1879 hr_utility.set_location('payplnk.validate_header',40);
1880 --
1881 begin
1882 pay_user_check.validate_header (
1883 p_batch_id,
1884 l_user_status,
1885 l_user_message);
1886 --
1887 hr_utility.set_location('payplnk.validate_header',45);
1888 --
1889 exception
1890 when nopackbody then
1891 --
1892 -- The user defined checks could not be found.
1893 --
1894 g_header_error := true;
1895 --
1896 -- update pay_batch_headers bah
1897 -- set bah.batch_status = 'E'
1898 -- where current of csr_header;
1899 --
1900 hr_utility.set_message(801,'HR_7450_PLK_PACK_BODY_NOT_EXST');
1901 l_error_text := substrb(hr_utility.get_message, 1, 240);
1902 --
1903 -- insert into pay_message_lines
1904 -- (LINE_SEQUENCE,
1905 -- PAYROLL_ID,
1906 -- MESSAGE_LEVEL,
1907 -- SOURCE_ID,
1908 -- SOURCE_TYPE,
1909 -- LINE_TEXT)
1910 -- values (
1911 -- pay_message_lines_s.nextval,
1912 -- null,
1913 -- 'F',
1914 -- p_batch_id,
1915 -- 'H',
1916 -- l_error_text);
1917 --
1918 g_head_err_msg := l_error_text;
1919 g_head_err_stat := 'F';
1920 --
1921 raise user_error;
1922 --
1923 --
1924 when others then
1925 --
1926 -- update pay_batch_headers bah
1927 -- set bah.batch_status = 'E'
1928 -- where current of csr_header;
1929 --
1930 hr_utility.set_message(801,'HR_7481_PLK_USR_CHECK_ERROR');
1931 hr_utility.set_message_token('USER_PROCEDURE',
1932 'the user batch header procedure');
1933 l_error_text := substrb((hr_utility.get_message||' '||sqlerrm),1,240);
1934 --
1935 -- insert into pay_message_lines
1936 -- (LINE_SEQUENCE,
1937 -- PAYROLL_ID,
1938 -- MESSAGE_LEVEL,
1939 -- SOURCE_ID,
1940 -- SOURCE_TYPE,
1941 -- LINE_TEXT)
1942 -- values (
1943 -- pay_message_lines_s.nextval,
1944 -- null,
1945 -- 'F',
1946 -- p_batch_id,
1947 -- 'H',
1948 -- l_error_text);
1949 --
1950 g_head_err_msg := l_error_text;
1951 g_head_err_stat := 'F';
1952 --
1953 raise user_error;
1954 end;
1955 --
1956 hr_utility.set_location('payplnk.validate_header',50);
1957 --
1958 -- If a status has not been returned raise an error.
1959 --
1960 if l_user_status is null then
1961 --
1962 -- update pay_batch_headers bah
1963 -- set bah.batch_status = 'E'
1964 -- where current of csr_header;
1965 --
1966 hr_utility.set_message(801,'HR_7468_PLK_USR_STATUS_NULL');
1967 hr_utility.set_message_token('USER_PROCEDURE',
1968 'the user batch header procedure');
1969 l_error_text := substrb(hr_utility.get_message, 1, 240);
1970 --
1971 -- insert into pay_message_lines
1972 -- (LINE_SEQUENCE,
1973 -- PAYROLL_ID,
1974 -- MESSAGE_LEVEL,
1975 -- SOURCE_ID,
1976 -- SOURCE_TYPE,
1977 -- LINE_TEXT)
1978 -- values (
1979 -- pay_message_lines_s.nextval,
1980 -- null,
1981 -- 'F',
1982 -- p_batch_id,
1983 -- 'H',
1984 -- l_error_text);
1985 --
1986 g_head_err_msg := l_error_text;
1987 g_head_err_stat := 'F';
1988 --
1989 raise user_error;
1990 --
1991 else
1992 open csr_status_chk(l_user_status);
1993 fetch csr_status_chk into l_usr_status_chk;
1994 --
1995 if csr_status_chk%NOTFOUND then
1996 --
1997 -- An invalid status has been returned, raise an error.
1998 --
1999 -- update pay_batch_headers bah
2000 -- set bah.batch_status = 'E'
2001 -- where current of csr_header;
2002 hr_utility.set_message(801,'HR_7469_PLK_USR_STATUS_INVLD');
2003 hr_utility.set_message_token('USER_PROCEDURE',
2004 'the user batch header procedure');
2005 l_error_text := substrb(hr_utility.get_message, 1, 240);
2006 --
2007 -- insert into pay_message_lines
2008 -- (LINE_SEQUENCE,
2009 -- PAYROLL_ID,
2010 -- MESSAGE_LEVEL,
2011 -- SOURCE_ID,
2012 -- SOURCE_TYPE,
2013 -- LINE_TEXT)
2014 -- values (
2015 -- pay_message_lines_s.nextval,
2016 -- null,
2017 -- 'F',
2018 -- p_batch_id,
2019 -- 'H',
2020 -- l_error_text);
2021 --
2022 g_head_err_msg := l_error_text;
2023 g_head_err_stat := 'F';
2024 --
2025 close csr_status_chk;
2026 --
2027 raise user_error;
2028 --
2029 elsif upper(l_user_status) in ('U','T') then
2030 --
2031 -- A status has been returned that is not valid in this context, raise
2032 -- an error.
2033 --
2034 -- update pay_batch_headers bah
2035 -- set bah.batch_status = 'E'
2036 -- where current of csr_header;
2037 --
2038 hr_utility.set_message(801,'HR_7470_PLK_USR_STATUS_INVLD_C');
2039 hr_utility.set_message_token('USER_PROCEDURE',
2040 'the user batch header procedure');
2041 l_error_text := substrb(hr_utility.get_message, 1, 240);
2042 --
2043 -- insert into pay_message_lines
2044 -- (LINE_SEQUENCE,
2045 -- PAYROLL_ID,
2046 -- MESSAGE_LEVEL,
2047 -- SOURCE_ID,
2048 -- SOURCE_TYPE,
2049 -- LINE_TEXT)
2050 -- values (
2051 -- pay_message_lines_s.nextval,
2052 -- null,
2053 -- 'F',
2054 -- p_batch_id,
2055 -- 'H',
2056 -- l_error_text);
2057 --
2058 g_head_err_msg := l_error_text;
2059 g_head_err_stat := 'F';
2060 --
2061 close csr_status_chk;
2062 --
2063 raise user_error;
2064 --
2065 else
2066 --
2067 -- A valid status has been returned from the user header procedure,
2068 -- therefore set the header to the appropriate status and insert a message
2069 -- into the PAY_MESSAGE_LINES table if one has been returned.
2070 --
2071 close csr_status_chk;
2072 --
2073 -- REMOVED THE FOLLOWING. This is logic is done as set_status procedure.
2074 -- If the process is in transfer mode, we really want to
2075 -- retain the batch status for now to 'P' to lock another
2076 -- submission of the batch submission inadvertantly.
2077 -- if(g_process_mode = 'VALIDATE') then
2078 -- update pay_batch_headers bah
2079 -- set bah.batch_status = decode(upper(l_user_status),
2080 -- 'W','V',
2081 -- upper(l_user_status))
2082 -- where current of csr_header;
2083 -- end if;
2084 --
2085 -- store user status globally.
2086 g_user_status := l_user_status;
2087 --
2088 if l_user_message is not null then
2089 --
2090 -- insert into pay_message_lines
2091 -- (LINE_SEQUENCE,
2092 -- PAYROLL_ID,
2093 -- MESSAGE_LEVEL,
2094 -- SOURCE_ID,
2095 -- SOURCE_TYPE,
2096 -- LINE_TEXT)
2097 -- values (
2098 -- pay_message_lines_s.nextval,
2099 -- null,
2100 -- decode(upper(l_user_status),'W','W','E','F','I'),
2101 -- p_batch_id,
2102 -- 'H',
2103 -- l_user_message);
2104 g_head_err_msg := l_user_message;
2105 if upper(l_user_status) = 'W' then
2106 g_head_err_stat := 'W';
2107 elsif upper(l_user_status) = 'E' then
2108 g_head_err_stat := 'F';
2109 else
2110 g_head_err_stat := 'I';
2111 end if;
2112 end if;
2113 --
2114 if upper(l_user_status) = 'E' then
2115 raise user_error;
2116 end if;
2117 end if;
2118 end if;
2119 --
2120 close csr_header;
2121 --
2122 end if;
2123 hr_utility.set_location('payplnk.validate_header',55);
2124 --
2125 exception
2126 when column_is_null then
2127 --
2128 -- A value was required.
2129 --
2130 g_header_error := true;
2131 --
2132 -- update pay_batch_headers bah
2133 -- set bah.batch_status = 'E'
2134 -- where current of csr_header;
2135 --
2136 hr_utility.set_message(801,'HR_7449_PLK_COLUMN_NULL');
2137 hr_utility.set_message_token('COLUMN_NAME',l_column_name1);
2138 l_error_text := substrb(hr_utility.get_message, 1, 240);
2139 --
2140 -- insert into pay_message_lines
2141 -- (LINE_SEQUENCE,
2142 -- PAYROLL_ID,
2143 -- MESSAGE_LEVEL,
2144 -- SOURCE_ID,
2145 -- SOURCE_TYPE,
2146 -- LINE_TEXT)
2147 -- values (
2148 -- pay_message_lines_s.nextval,
2149 -- null,
2150 -- 'F',
2151 -- p_batch_id,
2152 -- 'H',
2153 -- l_error_text);
2154 --
2155 g_head_err_msg := l_error_text;
2156 g_head_err_stat := 'F';
2157 --
2158 close csr_header;
2159 --
2160 --
2161 when invalid_value then
2162 --
2163 -- An invalid value was entered.
2164 --
2165 g_header_error := true;
2166 --
2167 -- update pay_batch_headers bah
2168 -- set bah.batch_status = 'E'
2169 -- where current of csr_header;
2170 --
2171 hr_utility.set_message(801,'HR_7462_PLK_INVLD_VALUE');
2172 hr_utility.set_message_token('COLUMN_NAME',l_column_name1);
2173 l_error_text := substrb(hr_utility.get_message, 1, 240);
2174 --
2175 -- insert into pay_message_lines
2176 -- (LINE_SEQUENCE,
2177 -- PAYROLL_ID,
2178 -- MESSAGE_LEVEL,
2179 -- SOURCE_ID,
2180 -- SOURCE_TYPE,
2181 -- LINE_TEXT)
2182 -- values (
2183 -- pay_message_lines_s.nextval,
2184 -- null,
2185 -- 'F',
2186 -- p_batch_id,
2187 -- 'H',
2188 -- l_error_text);
2189 --
2190 g_head_err_msg := l_error_text;
2191 g_head_err_stat := 'F';
2192 --
2193 close csr_header;
2194 --
2195 --
2196 when core_error or user_error then
2197 --
2198 -- A handled error occurred during header validation.
2199 --
2200 g_header_error := true;
2201 --
2202 if csr_header%ISOPEN then
2203 close csr_header;
2204 end if;
2205 --
2206 --
2207 when others then
2208 if csr_header%ISOPEN then
2209 close csr_header;
2210 end if;
2211 rollback to BH;
2212 raise;
2213 --
2214 end validate_header;
2215 --
2216 -----------------------------------------------------------------------
2217 -- NAME --
2218 -- payplnk.validate_controls PRIVATE PROCEDURE --
2219 -- --
2220 -- DESCRIPTION --
2221 -- Validate batch control(s) details. This includes the user defined--
2222 -- checks for the control(s) as well as the core validation checks --
2223 -- carried out by the process. If and only if the control(s) have --
2224 -- been validated successfully do we continue onto the next stage of --
2225 -- validation i.e. VALIDATE_LINES. --
2226 -----------------------------------------------------------------------
2227 --
2228 procedure validate_controls (
2229 p_batch_id in number
2230 ) is
2231 --
2232 -- LOCAL DECLARATIONS
2233 --
2234 l_exists varchar2(1) :=null;
2235 l_usr_status_chk varchar2(1) :=null;
2236 l_column_name1 varchar2(30) :=null;
2237 l_error_text pay_message_lines.line_text%TYPE := null;
2238 l_user_status pay_batch_control_totals.control_status%TYPE :=null;
2239 l_user_message pay_message_lines.line_text%TYPE :=null;
2240 -- Additions for standard totalling functionality
2241 l_std_status pay_batch_control_totals.control_status%TYPE :=null;
2242 l_std_message pay_message_lines.line_text%TYPE :=null;
2243 --
2244 column_is_null exception;
2245 invalid_value exception;
2246 user_control_error exception;
2247 control_types_not_defined exception;
2248 --
2249 begin
2250 --
2251 SAVEPOINT BC;
2252 --
2253 g_control_error := false;
2254 g_control_count := 0;
2255 g_ctl_id_tbl.delete;
2256 g_ctl_stat_tbl.delete;
2257 g_ctl_mess_tbl.delete;
2258 --
2259 hr_utility.set_location('payplnk.validate_controls',5);
2260 --
2261 -- Check to see if user defined control types exist.
2262 --
2263 begin
2264 select 'x'
2265 into l_exists
2266 from sys.dual
2267 where exists
2268 (select null
2269 from hr_lookups hlk
2270 where upper( hlk.lookup_type) = 'CONTROL_TYPE'
2271 and sysdate between nvl(hlk.start_date_active,sysdate)
2272 and nvl(hlk.end_date_active,
2273 hr_general.end_of_time)
2274 and hlk.enabled_flag = 'Y');
2275 --
2276 exception
2277 when no_data_found then
2278 --
2279 -- If control type(s) have not been defined then terminate batch control
2280 -- validation with an appropriate error message.
2281 --
2282 raise control_types_not_defined;
2283 --
2284 end;
2285 --
2286 hr_utility.set_location('payplnk.validate_controls',10);
2287 --
2288 -- Otherwise validate the batch control details.
2289 --
2290 for g_control_record in csr_all_controls(p_batch_id) loop
2291 -- Ensure that a valid control type has been entered.
2292 --
2293 begin
2294 l_column_name1 := 'CONTROL TYPE';
2295 --
2296 if g_control_record.control_type is null then
2297 raise column_is_null;
2298 else
2299 begin
2300 select 'x'
2301 into l_exists
2302 from hr_lookups hlk
2303 where hlk.lookup_type = 'CONTROL_TYPE'
2304 and sysdate between nvl(hlk.start_date_active,
2305 sysdate)
2306 and nvl(hlk.end_date_active,
2307 hr_general.end_of_time)
2308 and hlk.enabled_flag = 'Y'
2309 and upper(g_control_record.control_type) in (hlk.lookup_code);
2310 --
2311 hr_utility.set_location('payplnk.validate_controls',15);
2312 --
2313 exception
2314 when no_data_found then
2315 raise invalid_value;
2316 end;
2317 -- The core batch control checks were validated successfully, therefore
2318 -- carry out the control checks for the particular control.
2319 -- See if its a standard control check first. If not, pass on to be dealt
2320 -- with by user check routine
2321
2322
2323 -- next block carries out standard totalling checks. l_std_status is returned as
2324 -- Warning, Error or Custom (W,E,C). A status of custom is returned when
2325 -- control_type is not a standard control total and is therefore assumed
2326 -- to have been added as a part of customization
2327
2328 begin
2329
2330 pay_standard_check.check_control(p_batch_id,
2331 g_control_record.control_type,
2332 g_control_record.control_total,
2333 l_std_status,l_std_message);
2334
2335
2336 exception
2337 when others then
2338 -- update pay_batch_control_totals bac
2339 -- set bac.control_status = 'E'
2340 -- where current of csr_all_controls;
2341
2342 select hlk.meaning into l_std_message -- we already checked to see if this
2343 from hr_lookups hlk -- control type exists so we can select meaning
2344 where hlk.lookup_type = 'CONTROL_TYPE'
2345 and hlk.lookup_code = g_control_record.control_type;
2346
2347 hr_utility.set_message(801,'HR_34854_ERROR_IN_STD_TOTALS');
2348 hr_utility.set_message_token('NAME_OF_ENTITY_TO_SUM',l_std_message);
2349 l_error_text:=rpad(hr_utility.get_message||' '||sqlerrm,255);
2350
2351 -- insert into pay_message_lines
2352 -- (LINE_SEQUENCE,
2353 -- PAYROLL_ID,
2354 -- MESSAGE_LEVEL,
2355 -- SOURCE_ID,
2356 -- SOURCE_TYPE,
2357 -- LINE_TEXT)
2358 -- values (
2359 -- pay_message_lines_s.nextval,
2360 -- null,
2361 -- 'F',
2362 -- g_control_record.batch_control_id,
2363 -- 'C',
2364 -- l_error_text);
2365 g_control_count := g_control_count +1;
2366 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2367 g_ctl_stat_tbl(g_control_count) := 'F';
2368 g_ctl_mess_tbl(g_control_count) := l_error_text;
2369
2370
2371 raise user_control_error;
2372 end;
2373
2374
2375 if(l_std_status <> 'C') then --control was a standard one and has been
2376 -- processed by the above routine
2377
2378 -- update control status. Both V and W -> V in pay_batch_control_totals
2379
2380 -- update pay_batch_control_totals bac
2381 -- set bac.control_status = decode(l_std_status,'W','V',l_std_status)
2382 -- where current of csr_all_controls;
2383
2384 if(l_std_status <> 'V') then -- control not valid. Could be 'E' or 'W'
2385 -- insert message and level of error
2386
2387 -- insert into pay_message_lines
2388 -- (LINE_SEQUENCE,
2389 -- PAYROLL_ID,
2390 -- MESSAGE_LEVEL,
2391 -- SOURCE_ID,
2392 -- SOURCE_TYPE,
2393 -- LINE_TEXT)
2394 -- values (
2395 -- pay_message_lines_s.nextval,
2396 -- null,
2397 -- decode(l_std_status,'E','F',l_std_status),
2398 -- g_control_record.batch_control_id,
2399 -- 'C',
2400 -- l_std_message);
2401 g_control_count := g_control_count +1;
2402 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2403 g_ctl_mess_tbl(g_control_count) := l_std_message;
2404 if l_std_status = 'E' then
2405 g_ctl_stat_tbl(g_control_count) := 'F';
2406 else
2407 g_ctl_stat_tbl(g_control_count) := l_std_status;
2408 end if;
2409
2410 if(l_std_status = 'E') then
2411 raise user_control_error;
2412 end if;
2413
2414 end if;
2415
2416
2417 else -- pass control onto user control routine.
2418
2419 --
2420 l_user_status := 'V';
2421 --
2422 begin
2423 --
2424 pay_user_check.check_control (
2425 p_batch_id,
2426 g_control_record.control_type,
2427 g_control_record.control_total,
2428 l_user_status,
2429 l_user_message);
2430 --
2431 hr_utility.set_location('payplnk.validate_controls',20);
2432 --
2433 exception
2434 --
2435 -- If an unhandled error occurred during the user control validation
2436 -- then raise an error.
2437 --
2438
2439 when others then
2440 --
2441 -- update pay_batch_control_totals bac
2442 -- set bac.control_status = 'E'
2443 -- where current of csr_all_controls;
2444 --
2445 hr_utility.set_message(801,'HR_7481_PLK_USR_CHECK_ERROR');
2446 hr_utility.set_message_token('USER_PROCEDURE',
2447 'the user batch control procedure');
2448 l_error_text := substrb((hr_utility.get_message||' '||sqlerrm),1,240);
2449 --
2450 -- insert into pay_message_lines
2451 -- (LINE_SEQUENCE,
2452 -- PAYROLL_ID,
2453 -- MESSAGE_LEVEL,
2454 -- SOURCE_ID,
2455 -- SOURCE_TYPE,
2456 -- LINE_TEXT)
2457 -- values (
2458 -- pay_message_lines_s.nextval,
2459 -- null,
2460 -- 'F',
2461 -- g_control_record.batch_control_id,
2462 -- 'C',
2463 -- l_error_text);
2464 g_control_count := g_control_count +1;
2465 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2466 g_ctl_mess_tbl(g_control_count) := l_error_text;
2467 g_ctl_stat_tbl(g_control_count) := 'F';
2468 --
2469 raise user_control_error;
2470 end;
2471 --
2472 hr_utility.set_location('payplnk.validate_controls',25);
2473 --
2474 -- If a status has not been returned raise an error.
2475 --
2476 if l_user_status is null then
2477 --
2478 -- update pay_batch_control_totals bac
2479 -- set bac.control_status = 'E'
2480 -- where current of csr_all_controls;
2481 --
2482 hr_utility.set_message(801,'HR_7468_PLK_USR_STATUS_NULL');
2483 hr_utility.set_message_token('USER_PROCEDURE',
2484 'the user batch control procedure');
2485 l_error_text := substrb(hr_utility.get_message, 1, 240);
2486 --
2487 -- insert into pay_message_lines
2488 -- (LINE_SEQUENCE,
2489 -- PAYROLL_ID,
2490 -- MESSAGE_LEVEL,
2491 -- SOURCE_ID,
2492 -- SOURCE_TYPE,
2493 -- LINE_TEXT)
2494 -- values (
2495 -- pay_message_lines_s.nextval,
2496 -- null,
2497 -- 'F',
2498 -- g_control_record.batch_control_id,
2499 -- 'C',
2500 -- l_error_text);
2501 g_control_count := g_control_count +1;
2502 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2503 g_ctl_mess_tbl(g_control_count) := l_error_text;
2504 g_ctl_stat_tbl(g_control_count) := 'F';
2505 --
2506 raise user_control_error;
2507 --
2508 else
2509 open csr_status_chk(l_user_status);
2510 fetch csr_status_chk into l_usr_status_chk;
2511 --
2512 if csr_status_chk%NOTFOUND then
2513 -- An invalid status has been returned, raise an error.
2514 --
2515 -- update pay_batch_control_totals bac
2516 -- set bac.control_status = 'E'
2517 -- where current of csr_all_controls;
2518 --
2519 hr_utility.set_message(801,'HR_7469_PLK_USR_STATUS_INVLD');
2520 hr_utility.set_message_token('USER_PROCEDURE',
2521 'the user batch control procedure');
2522 l_error_text := substrb(hr_utility.get_message, 1, 240);
2523 --
2524 -- insert into pay_message_lines
2525 -- (LINE_SEQUENCE,
2526 -- PAYROLL_ID,
2527 -- MESSAGE_LEVEL,
2528 -- SOURCE_ID,
2529 -- SOURCE_TYPE,
2530 -- LINE_TEXT)
2531 -- values (
2532 -- pay_message_lines_s.nextval,
2533 -- null,
2534 -- 'F',
2535 -- g_control_record.batch_control_id,
2536 -- 'C',
2537 -- l_error_text);
2538 g_control_count := g_control_count +1;
2539 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2540 g_ctl_mess_tbl(g_control_count) := l_error_text;
2541 g_ctl_stat_tbl(g_control_count) := 'F';
2542 --
2543 close csr_status_chk;
2544 --
2545 raise user_control_error;
2546 --
2547 elsif upper(l_user_status) in ('U','T') then
2548 --
2549 -- A status has been returned that is not valid in this context, raise
2550 -- an error.
2551 --
2552 -- update pay_batch_control_totals bac
2553 -- set bac.control_status = 'E'
2554 -- where current of csr_all_controls;
2555 --
2556 hr_utility.set_message(801,'HR_7470_PLK_USR_STATUS_INVLD_C');
2557 hr_utility.set_message_token('USER_PROCEDURE',
2558 'the user batch control procedure');
2559 l_error_text := substrb(hr_utility.get_message, 1, 240);
2560 --
2561 -- insert into pay_message_lines
2562 -- (LINE_SEQUENCE,
2563 -- PAYROLL_ID,
2564 -- MESSAGE_LEVEL,
2565 -- SOURCE_ID,
2566 -- SOURCE_TYPE,
2567 -- LINE_TEXT)
2568 -- values (
2569 -- pay_message_lines_s.nextval,
2570 -- null,
2571 -- 'F',
2572 -- g_control_record.batch_control_id,
2573 -- 'C',
2574 -- l_error_text);
2575 g_control_count := g_control_count +1;
2576 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2577 g_ctl_mess_tbl(g_control_count) := l_error_text;
2578 g_ctl_stat_tbl(g_control_count) := 'F';
2579 --
2580 close csr_status_chk;
2581 --
2582 raise user_control_error;
2583 else
2584 --
2585 -- A valid status has been returned from the user control procedure,
2586 -- therefore set the batch control to the appropriate status and insert
2587 -- a message into the PAY_MESSAGE_LINES table if one has been returned.
2588 --
2589 close csr_status_chk;
2590 --
2591 -- update pay_batch_control_totals bac
2592 -- set bac.control_status = decode(upper(l_user_status),
2593 -- 'W','V',
2594 -- upper(l_user_status))
2595 -- where current of csr_all_controls;
2596 --
2597 if l_user_message is not null then
2598 --
2599 -- insert into pay_message_lines
2600 -- (LINE_SEQUENCE,
2601 -- PAYROLL_ID,
2602 -- MESSAGE_LEVEL,
2603 -- SOURCE_ID,
2604 -- SOURCE_TYPE,
2605 -- LINE_TEXT)
2606 -- values (
2607 -- pay_message_lines_s.nextval,
2608 -- null,
2609 -- decode(upper(l_user_status),'W','W','E','F','I'),
2610 -- g_control_record.batch_control_id,
2611 -- 'C',
2612 -- l_user_message);
2613 g_control_count := g_control_count +1;
2614 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2615 g_ctl_mess_tbl(g_control_count) := l_user_message;
2616 if upper(l_user_status) = 'W' then
2617 g_ctl_stat_tbl(g_control_count) := 'W';
2618 elsif upper(l_user_status) = 'E' then
2619 g_ctl_stat_tbl(g_control_count) := 'F';
2620 else
2621 g_ctl_stat_tbl(g_control_count) := 'I';
2622 end if;
2623 end if;
2624 --
2625 if upper(l_user_status) = 'E' then
2626 raise user_control_error;
2627 end if;
2628 end if;
2629 end if;
2630 --
2631 end if;
2632 --
2633 hr_utility.set_location('payplnk.validate_controls',30);
2634
2635 -- end section dealing with user defined controls as opposed to standard controls
2636 end if;
2637 --
2638 exception
2639 when column_is_null then
2640
2641 --
2642 -- A value was required.
2643 --
2644 g_control_error := true;
2645 --
2646 -- update pay_batch_control_totals bac
2647 -- set bac.control_status = 'E'
2648 -- where current of csr_all_controls;
2649 --
2650 hr_utility.set_message(801,'HR_7449_PLK_COLUMN_NULL');
2651 hr_utility.set_message_token('COLUMN_NAME',l_column_name1);
2652 l_error_text := substrb(hr_utility.get_message, 1, 240);
2653 --
2654 -- insert into pay_message_lines
2655 -- (LINE_SEQUENCE,
2656 -- PAYROLL_ID,
2657 -- MESSAGE_LEVEL,
2658 -- SOURCE_ID,
2659 -- SOURCE_TYPE,
2660 -- LINE_TEXT)
2661 -- values (
2662 -- pay_message_lines_s.nextval,
2663 -- null,
2664 -- 'F',
2665 -- g_control_record.batch_control_id,
2666 -- 'C',
2667 -- l_error_text);
2668 g_control_count := g_control_count +1;
2669 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2670 g_ctl_mess_tbl(g_control_count) := l_error_text;
2671 g_ctl_stat_tbl(g_control_count) := 'F';
2672 --
2673 when invalid_value then
2674 --
2675 -- An invalid value was entered.
2676 --
2677 g_control_error := true;
2678 --
2679 -- update pay_batch_control_totals bac
2680 -- set bac.control_status = 'E'
2681 -- where current of csr_all_controls;
2682 --
2683 hr_utility.set_message(801,'HR_7462_PLK_INVLD_VALUE');
2684 hr_utility.set_message_token('COLUMN_NAME',l_column_name1);
2685 l_error_text := substrb(hr_utility.get_message, 1, 240);
2686 --
2687 -- insert into pay_message_lines
2688 -- (LINE_SEQUENCE,
2689 -- PAYROLL_ID,
2690 -- MESSAGE_LEVEL,
2691 -- SOURCE_ID,
2692 -- SOURCE_TYPE,
2693 -- LINE_TEXT)
2694 -- values (
2695 -- pay_message_lines_s.nextval,
2696 -- null,
2697 -- 'F',
2698 -- g_control_record.batch_control_id,
2699 -- 'C',
2700 -- l_error_text);
2701 g_control_count := g_control_count +1;
2702 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2703 g_ctl_mess_tbl(g_control_count) := l_error_text;
2704 g_ctl_stat_tbl(g_control_count) := 'F';
2705 --
2706 when user_control_error then
2707 --
2708 g_control_error := true;
2709 --
2710 end;
2711 end loop;
2712 --
2713 hr_utility.set_location('payplnk.validate_controls',35);
2714 --
2715 exception
2716 --
2717 when control_types_not_defined then
2718 --
2719 g_control_error := true;
2720 --
2721 for g_control_record in csr_all_controls(p_batch_id) loop
2722 --
2723 -- update pay_batch_control_totals bac
2724 -- set bac.control_status = 'E'
2725 -- where current of csr_all_controls;
2726 --
2727 hr_utility.set_message(801,'HR_7477_PLK_NO_CONTROL_TYPES');
2728 l_error_text := substrb(hr_utility.get_message, 1, 240);
2729 --
2730 -- insert into pay_message_lines
2731 -- (LINE_SEQUENCE,
2732 -- PAYROLL_ID,
2733 -- MESSAGE_LEVEL,
2734 -- SOURCE_ID,
2735 -- SOURCE_TYPE,
2736 -- LINE_TEXT)
2737 -- values (
2738 -- pay_message_lines_s.nextval,
2739 -- null,
2740 -- 'F',
2741 -- g_control_record.batch_control_id,
2742 -- 'C',
2743 -- l_error_text);
2744 g_control_count := g_control_count +1;
2745 g_ctl_id_tbl(g_control_count) := g_control_record.batch_control_id;
2746 g_ctl_mess_tbl(g_control_count) := l_error_text;
2747 g_ctl_stat_tbl(g_control_count) := 'F';
2748 --
2749 end loop;
2750 --
2751 --
2752 when others then
2753 if csr_all_controls%ISOPEN then
2754 close csr_all_controls;
2755 end if;
2756 rollback to BC;
2757 raise;
2758 --
2759 end validate_controls;
2760 --
2761 --
2762 -----------------------------------------------------------------------
2763 -- NAME --
2764 -- payplnk.validate_lines PRIVATE PROCEDURE --
2765 -- --
2766 -- DESCRIPTION --
2767 -- Validates batch line(s) details. This includes the user defined --
2768 -- checks for the line(s) as well as the core validation checks --
2769 -- carried out by the process. This is the final stage of batch --
2770 -- validation. --
2771 -----------------------------------------------------------------------
2772 --
2773 procedure validate_lines (
2774 p_asg_id in number,
2775 p_asg_act_id in number,
2776 p_process_mode in varchar,
2777 -- p_line_id_tbl in out hr_entry.number_table,
2778 -- p_status_tbl in out hr_entry.varchar2_table,
2779 -- p_message_tbl in out varchar2_table2,
2780 p_batch_id in number,
2781 p_business_group_id in number
2782 --p_leg_line_check in boolean
2783 ) is
2784 --
2785 -- LOCAL DECLARATIONS
2786 --
2787 --
2788 l_line_check number;
2789 l_leg_line_check boolean := false;
2790 --
2791 l_input_id_exists boolean := false;
2792 l_reject_if_future_changes_chk boolean := false;
2793 l_action_if_exists_chk boolean := false;
2794 l_date_effective_changes_chk boolean := false;
2795 l_general_message boolean := false;
2796 --
2797 l_element_exists varchar2(1) := null;
2798 l_assignment_exists varchar2(1) := null;
2799 l_usr_status_chk varchar2(1) := null;
2800 l_dummy varchar2(1) := null;
2801 --
2802 l_column_name1 varchar2(30) := null;
2803 l_column_name2 varchar2(30) := null;
2804 l_update_mode varchar2(30) := null;
2805 l_effective_end_date1 date := null;
2806 -- l_business_group_id number := null;
2807 not_upper boolean := false;
2808 --
2809 l_element_type_id pay_batch_lines.element_type_id%TYPE :=null;
2810 l_assignment_id pay_batch_lines.assignment_id%TYPE :=null;
2811 l_element_name pay_batch_lines.element_name%TYPE :=null;
2812 l_assignment_number pay_batch_lines.assignment_number%TYPE :=null;
2813 l_error_text pay_message_lines.line_text%TYPE :=null;
2814 l_input_value_id pay_input_values_f.input_value_id%TYPE :=null;
2815 l_uom pay_input_values_f.uom%TYPE :=null;
2816 l_input_curr_code pay_element_types_f.input_currency_code%TYPE := null;
2817 l_lookup_type pay_input_values_f.lookup_type%TYPE :=null;
2818 l_value_set_id pay_input_values_f.value_set_id%TYPE :=null;
2819 l_costable_type pay_element_links_f.costable_type%TYPE :=null;
2820 l_effective_end_date pay_element_entries_f.effective_end_date%TYPE:=null;
2821 l_effective_start_date pay_element_entries_f.effective_start_date%TYPE:=null;
2822 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE :=null;
2823 e_creator_type pay_element_entries_f.creator_type%TYPE :=null;
2824 e_creator_id pay_element_entries_f.creator_id%TYPE :=null;
2825 l_multiple_entries_allowed
2826 pay_element_types_f.multiple_entries_allowed_flag%TYPE :=null;
2827 l_processing_type pay_element_types_f.processing_type%TYPE :=null;
2828 l_cost_allocation_structure
2829 per_business_groups_perf.cost_allocation_structure%TYPE :=null;
2830 l_legislation_code per_business_groups_perf.legislation_code%TYPE := null;
2831 l_link_id pay_element_links_f.element_link_id%TYPE :=null;
2832 l_user_status pay_batch_lines.batch_line_status%TYPE :=null;
2833 l_user_message pay_message_lines.line_text%TYPE :=null;
2834 --
2835 absence_entry_already_created boolean := false;
2836 l_absence_attendance_type_id number;
2837 l_absence_attendance_id number;
2838 l_hours_or_days varchar2(1);
2839 l_eligible varchar2(1);
2840 l_creator_type varchar2(1);
2841 -- l_absence_days number;
2842 -- l_absence_hours number;
2843 l_start_date date;
2844 l_end_date date;
2845 sql_curs number;
2846 rows_processed integer;
2847 statem varchar2(256);
2848 l_valid number;
2849 l_leg_message pay_message_lines.line_text%TYPE := null;
2850 l_line_changed number;
2851 l_allow_rollback boolean;
2852 --
2853 c_entry_values number :=0;
2854 i binary_integer :=0;
2855 k binary_integer :=0;
2856 l binary_integer :=0;
2857 t binary_integer :=0;
2858 --
2859 --l_entry_value_tbl varchar2_table80; for bug9350651
2860 l_entry_value_tbl varchar2_table2;
2861 l_passed_val_tbl hr_entry.varchar2_table;
2862 --l_passed_val_tbl varchar2_table2;
2863 l_passed_inp_tbl hr_entry.number_table;
2864 l_inpv_uom_tbl hr_entry.varchar2_table;
2865 l_abs_type_tbl hr_entry.number_table;
2866 l_abs_hod_tbl hr_entry.varchar2_table;
2867 l_line_id_tbl hr_entry.number_table;
2868 l_status_tbl hr_entry.varchar2_table;
2869 l_message_tbl varchar2_table2;
2870 l_error_table per_absence_attendances_pkg3.t_message_table;
2871 l_warning_table per_absence_attendances_pkg3.t_message_table;
2872 l_error_num number;
2873 l_warning_num number;
2874 --(bug 372339).
2875 l_health_plan_benefit boolean := TRUE;
2876 l_fnd_sessions_date date;
2877 l_commit number;
2878 --
2879 column_is_null exception;
2880 both_nulls exception;
2881 invalid_entry_value exception;
2882 non_costable_element exception;
2883 multiple_entries_not_allowed exception;
2884 element_entry_exists exception;
2885 more_than_one_entry exception;
2886 future_changes_exist exception;
2887 inputs_not_required exception;
2888 element_entry_not_eligible exception;
2889 no_cost_allocation_structure exception;
2890 line_error_occurred exception;
2891 invalid_date_format exception;
2892 non_updatable_creator_type exception;
2893 --
2894 l_header_err boolean;
2895 l_control_err boolean;
2896 l_err_exists varchar2(1) := null;
2897 --
2898 cursor csr_error_header is
2899 select 'Y'
2900 from pay_batch_headers pbh
2901 where pbh.batch_status = 'E'
2902 and pbh.batch_id = p_batch_id;
2903 --
2904 --
2905 cursor csr_chk_asg_lines (l_batch_id in number, l_asg_id in number) is
2906 select 'Y'
2907 from pay_batch_lines pbl
2908 where pbl.batch_id = l_batch_id
2909 and pbl.assignment_id = l_asg_id
2910 and pbl.batch_line_status in ('T','E');
2911 --
2912 l_chk_asg_lines varchar2(1);
2913 --
2914 --
2915 cursor csr_error_totals is
2916 select 'Y'
2917 from pay_batch_control_totals pct
2918 where pct.control_status = 'E'
2919 and pct.batch_id = p_batch_id;
2920 --
2921 cursor cur_max is
2922 select fnd_number.canonical_to_number(parameter_value)
2923 from pay_action_parameters
2924 where parameter_name = 'BEE_LOCK_MAX_WAIT_SEC';
2925 --
2926 cursor cur_intw is
2927 select fnd_number.canonical_to_number(parameter_value)
2928 from pay_action_parameters
2929 where parameter_name = 'BEE_LOCK_INTERVAL_WAIT_SEC';
2930 --
2931 cursor csr_table_inp_ids(
2932 -- Business group id was taken out because input value inherits the bg from the element type.
2933 -- l_business_group_id in number,
2934 l_element_type_id in number,
2935 l_effective_date in date) is
2936 select inv.input_value_id,
2937 inv.uom,
2938 inv.lookup_type,
2939 inv.value_set_id,
2940 etp.input_currency_code
2941 from pay_input_values_f inv,
2942 pay_element_types_f etp
2943 where inv.element_type_id = l_element_type_id
2944 and etp.element_type_id = l_element_type_id
2945 -- and ((inv.business_group_id +0 = l_business_group_id) OR
2946 -- ((inv.business_group_id IS NULL) AND
2947 -- (l_business_group_id IS NULL)))
2948 and l_effective_date between inv.effective_start_date
2949 and inv.effective_end_date
2950 and l_effective_date between etp.effective_start_date
2951 and etp.effective_end_date
2952 order by inv.display_sequence
2953 , inv.name;
2954 --
2955 cursor csr_element_entries(l_link_id in number,
2956 l_effective_date in date,
2957 l_element_type_id in number,
2958 l_assignment_id in number,
2959 l_date_earned in date) is
2960 select ee.element_entry_id
2961 , ee.creator_type
2962 , ee.creator_id
2963 , ee.effective_end_date
2964 , ee.effective_start_date
2965 from pay_element_entries_f ee
2966 , pay_element_types_f et
2967 , pay_element_links_f el
2968 , per_all_assignments_f asg
2969 where el.element_link_id = ee.element_link_id
2970 and et.element_type_id = el.element_type_id
2971 and ee.assignment_id = asg.assignment_id
2972 and l_effective_date between ee.effective_start_date
2973 and ee.effective_end_date
2974 and l_effective_date between el.effective_start_date
2975 and el.effective_end_date
2976 and l_effective_date between et.effective_start_date
2977 and et.effective_end_date
2978 and l_effective_date between asg.effective_start_date
2979 and asg.effective_end_date
2980 and el.element_link_id = l_link_id
2981 and et.element_type_id = l_element_type_id
2982 and asg.assignment_id = l_assignment_id
2983 and ee.entry_type = 'E'
2984 -- For bug 13496730
2985 and ((ee.date_earned is null and l_date_earned is null)
2986 or ee.date_earned = l_date_earned);
2987 --
2988 cursor csr_future_existence(l_link_id in number,
2989 l_effective_date in date,
2990 l_element_type_id in number,
2991 l_assignment_id in number) is
2992 select null
2993 from pay_element_entries_f ee
2994 , pay_element_types_f et
2995 , pay_element_links_f el
2996 , per_all_assignments_f asg
2997 where el.element_link_id = ee.element_link_id
2998 and et.element_type_id = el.element_type_id
2999 and ee.assignment_id = asg.assignment_id
3000 and ee.effective_start_date > l_effective_date
3001 and el.element_link_id =l_link_id
3002 and et.element_type_id =l_element_type_id
3003 and asg.assignment_id = l_assignment_id
3004 and ee.entry_type = 'E';
3005 --
3006 cursor csr_attendance_types(l_business_group_id in number,
3007 l_passed_inp_val in number,
3008 l_passed_start_date in date,
3009 l_passed_end_date in date) is
3010 select paat.absence_attendance_type_id
3011 , paat.hours_or_days
3012 from per_absence_attendance_types paat
3013 where paat.input_value_id = l_passed_inp_val
3014 and paat.business_group_id +0 = l_business_group_id
3015 and date_effective <= l_passed_start_date
3016 and (nvl(date_end,hr_general.end_of_time) >= l_passed_end_date
3017 or l_passed_end_date is null);
3018 --
3019 -- Bug 488335 - new declaration to avoid invalid parameter modes in
3020 -- Oracle 8. Declare temporary line_record variable in order to
3021 -- preserve original, since we will call api with a new 'in out'
3022 -- parameter mode on line_record.
3023 g_line_record1 csr_asg_lines%ROWTYPE;
3024 g_line_record2 pay_batch_lines%ROWTYPE;
3025 --
3026 l_bee_iv_upgrade varchar2(1);
3027 --
3028 begin
3029 --
3030 hr_utility.set_location('payplnk.validate_lines',5);
3031 -- Added the following call as part of the fix to the bug#7138224.
3032 fnd_profile.put('PER_ASSIGNMENT_ID',p_asg_id);
3033 --
3034 --Only validate batch lines if there are no eerrors in batch header or control totals.
3035 --
3036 l_control_err := false;
3037 l_header_err := false;
3038 --
3039 open csr_error_totals;
3040 fetch csr_error_totals into l_err_exists;
3041 if csr_error_totals%FOUND then
3042 l_control_err := true;
3043 end if;
3044 close csr_error_totals;
3045 --
3046 open csr_error_header;
3047 fetch csr_error_header into l_err_exists;
3048 if csr_error_header%FOUND then
3049 l_header_err := true;
3050 end if;
3051 close csr_error_header;
3052 --
3053 if (p_asg_id is not null) then
3054 --
3055 open csr_chk_asg_lines(p_batch_id,p_asg_id);
3056 fetch csr_chk_asg_lines into l_chk_asg_lines;
3057 close csr_chk_asg_lines;
3058 --
3059 end if;
3060 --
3061 /* if (l_control_err = true or l_header_err = true or l_chk_asg_lines = 'Y') then
3062 return;
3063 end if;
3064 */ /* Bug 13814081 */
3065 --
3066 -- Set the global parameter to disable the triggers.
3067 payplnk.g_payplnk_call := true;
3068 --
3069 -- Set the retry duration and maximum wait values if one hasn't assigned.
3070 if g_lock_max_wait is null then
3071 --
3072 -- Attempt to find out the BEE interlock max wait time
3073 -- and polling interval time from pay_action_parameters. If values
3074 -- cannot be found in this table then default to a max wait of 0
3075 -- seconds and polling interval of 0 seconds.
3076 --
3077 open cur_max;
3078 fetch cur_max into g_lock_max_wait;
3079 if cur_max %notfound then
3080 close cur_max;
3081 -- Value not in table, set to the default
3082 g_lock_max_wait := 0;
3083 else
3084 close cur_max;
3085 end if;
3086 --
3087 open cur_intw;
3088 fetch cur_intw into g_lock_interval;
3089 if cur_intw %notfound then
3090 close cur_intw;
3091 -- Value not in table, set to the default
3092 g_lock_interval := 0;
3093 else
3094 close cur_intw;
3095 end if;
3096 --
3097 end if;
3098 --
3099 SAVEPOINT BL;
3100 --
3101 l_bee_iv_upgrade := get_upgrade_status(p_business_group_id,'BEE_IV_UPG');
3102 --
3103 --
3104 -- Check whether the upgrade process is in progress.
3105 --
3106 if l_bee_iv_upgrade = 'E' then
3107 hr_utility.set_message(800, 'HR_449106_BEE_UPGRADING');
3108 hr_utility.raise_error;
3109 end if;
3110 --
3111 open csr_bl_header(p_batch_id);
3112 fetch csr_bl_header into g_header_record;
3113 --
3114 -- Empty global messages.
3115 g_count := 0;
3116 g_line_error := false;
3117 g_line_id_tbl.delete;
3118 g_status_tbl.delete;
3119 g_message_tbl.delete;
3120 --
3121 --
3122 -- Retrieve the cost allocation structure for the business group.
3123 --
3124 select fnd_number.canonical_to_number(bsg.cost_allocation_structure)
3125 , bsg.legislation_code
3126 into l_cost_allocation_structure
3127 , l_legislation_code
3128 from per_business_groups_perf bsg
3129 where bsg.business_group_id = p_business_group_id
3130 and bsg.enabled_flag = 'Y';
3131 --
3132 --
3133 -- legislative line check
3134 --
3135 begin
3136 statem := 'BEGIN
3137 :line_check := pay_'||lower(l_legislation_code)||'_bee.line_check_supported; END;';
3138 --
3139 if pay_core_utils.get_sql_cursor(statem,sql_curs) then
3140 --
3141 dbms_sql.bind_variable(sql_curs, 'line_check', l_line_check);
3142 --
3143 rows_processed := dbms_sql.execute(sql_curs);
3144 --
3145 dbms_sql.variable_value(sql_curs, 'line_check', l_line_check);
3146 --
3147 if l_line_check = 0 then
3148 l_leg_line_check := TRUE;
3149 else
3150 l_leg_line_check := FALSE;
3151 end if;
3152 --
3153 else
3154 raise error_occurred;
3155 end if;
3156 --
3157 exception
3158 when others then
3159 --
3160 l_leg_line_check := FALSE;
3161 --
3162 end;
3163 --
3164 --
3165 -- Retrieve the cost allocation structure for the business group.
3166 --
3167 -- select fnd_number.canonical_to_number(bsg.cost_allocation_structure)
3168 -- , bsg.legislation_code
3169 -- into l_cost_allocation_structure
3170 -- , l_legislation_code
3171 -- from per_business_groups bsg
3172 -- where bsg.business_group_id = p_business_group_id
3173 -- and bsg.enabled_flag = 'Y';
3174 --
3175 -- Check whether inserting entries for the element with benefit classification
3176 -- and for this classification there are defined contributions.
3177 -- Entries will have to be validated against valid coverage types (bug 372339).
3178 --
3179 open csr_asg_lines(p_batch_id, p_asg_id);
3180 fetch csr_asg_lines into g_line_record;
3181 begin
3182 select null
3183 into l_dummy
3184 from dual
3185 where exists(select null
3186 from pay_element_types_f ET,
3187 ben_benefit_classifications BCL
3188 where ET.element_type_id = g_line_record.element_type_id
3189 and ET.benefit_classification_id = BCL.benefit_classification_id
3190 and BCL.contributions_used = 'Y');
3191 exception
3192 when no_data_found then
3193 l_health_plan_benefit := FALSE;
3194 end;
3195 l_fnd_sessions_date := sysdate;
3196 close csr_asg_lines;
3197 --
3198 --
3199 -- Validate each batch line in turn.
3200 --
3201 for g_line_record in csr_asg_lines(p_batch_id, p_asg_id) loop
3202 begin
3203 --
3204 -- Truncate effective date just in case and update the record (335670)
3205 --
3206 -- label for start of validation
3207 -- used when legislative hook has changed line
3208 <<start_validation>>
3209 g_line_record.effective_date := TRUNC (g_line_record.effective_date);
3210 --
3211 UPDATE pay_batch_lines
3212 SET effective_date = g_line_record.effective_date
3213 WHERE CURRENT OF csr_asg_lines;
3214 --
3215 -- If element is a benefit and effective_date for this line is
3216 -- different from the previous one then update the row in fnd_sessions.
3217 -- This will ensure that the entry is validated against coverage_type as on the
3218 -- date when entry will start (bug 372339).
3219 --
3220 if l_health_plan_benefit and
3221 g_line_record.effective_date <> l_fnd_sessions_date then
3222 dt_fndate.change_ses_date(g_line_record.effective_date,l_commit);
3223 l_fnd_sessions_date := g_line_record.effective_date;
3224 end if;
3225 --
3226 --
3227 -- Initialise variables
3228 --
3229 l_general_message := false;
3230 l_input_id_exists := false;
3231 c_entry_values := 0;
3232 i :=0;
3233 k :=0;
3234 l :=0;
3235 t :=0;
3236 l_creator_type := 'H';
3237 l_absence_attendance_id := 0;
3238 absence_entry_already_created := false;
3239 --
3240 hr_utility.set_location('payplnk.validate_lines',10);
3241 -- Moving the following code into the validate procedure.
3242 -- --
3243 -- -- Carry out the core line validation checks.
3244 -- --
3245 -- l_column_name1 := 'EFFECTIVE DATE';
3246 -- --
3247 -- -- Ensure that an effective date has been entered.
3248 -- --
3249 -- if g_line_record.effective_date is null then
3250 -- raise column_is_null;
3251 -- end if;
3252 --
3253 -- Ensure that a valid element type id or element name has been entered.
3254 --
3255 l_column_name1 := 'ELEMENT TYPE ID';
3256 l_column_name2 := 'ELEMENT NAME';
3257 --
3258 -- If only an element name has been entered, ensure that it is valid.
3259 --
3260 if (g_line_record.element_type_id is null) and
3261 (g_line_record.element_name is not null) then
3262 begin
3263 select elt.element_type_id -- ,business_group_id -- CWA
3264 into l_element_type_id -- ,l_business_group_id -- CWA
3265 from pay_element_types_f elt
3266 where upper(elt.element_name) = upper(g_line_record.element_name)
3267 and g_line_record.effective_date between elt.effective_start_date
3268 and elt.effective_end_date
3269 and (elt.business_group_id = p_business_group_id
3270 or (elt.business_group_id is null
3271 and elt.legislation_code = l_legislation_code)
3272 or (elt.business_group_id is null
3273 and elt.legislation_code is null));
3274 -- Included the assumption legislation code can be null.
3275 --
3276 g_line_record.element_type_id := l_element_type_id;
3277 --
3278 update pay_batch_lines
3279 set element_type_id = g_line_record.element_type_id
3280 where current of csr_asg_lines;
3281 --
3282 exception
3283 when no_data_found then
3284 g_line_error := true;
3285 g_count := g_count + 1;
3286 --
3287 hr_utility.set_message(801,'HR_7465_PLK_NOT_ELGBLE_ELE_NME');
3288 hr_utility.set_message_token('ELEMENT_NAME',
3289 g_line_record.element_name);
3290 l_error_text := substrb(hr_utility.get_message, 1, 240);
3291 --
3292 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3293 l_status_tbl(g_count) :='F';
3294 l_message_tbl(g_count) := l_error_text;
3295 --
3296 raise line_error_occurred;
3297 end;
3298 --
3299 hr_utility.set_location('payplnk.validate_lines',15);
3300 --
3301 -- If neither an element type id or element name has been entered,
3302 -- raise error.
3303 --
3304 elsif (g_line_record.element_type_id is null) and
3305 (g_line_record.element_name is null) then
3306 raise both_nulls;
3307 --
3308 -- If both element type id and element name has been entered ensure they
3309 -- are consistent.
3310 --
3311 elsif (g_line_record.element_type_id is not null) and
3312 (g_line_record.element_name is not null) then
3313 begin
3314 select 'x' -- ,business_group_id -- CWA
3315 into l_element_exists -- ,l_business_group_id -- CWA
3316 from pay_element_types_f elt
3317 where upper(elt.element_name) = upper(g_line_record.element_name)
3318 and elt.element_type_id = g_line_record.element_type_id
3319 and g_line_record.effective_date between elt.effective_start_date
3320 and elt.effective_end_date
3321 and (elt.business_group_id +0 = p_business_group_id
3322 or (elt.business_group_id is null
3323 and elt.legislation_code = l_legislation_code)
3324 or (elt.business_group_id is null
3325 and elt.legislation_code is null));
3326 --
3327 exception
3328 when no_data_found then
3329 g_line_error := true;
3330 g_count := g_count + 1;
3331 --
3332 hr_utility.set_message(801,'HR_7478_PLK_INCONSISTENT_ELE');
3333 hr_utility.set_message_token('ELEMENT_TYPE_ID',
3334 g_line_record.element_type_id);
3335 hr_utility.set_message_token('ELEMENT_NAME',
3336 g_line_record.element_name);
3337 --
3338 l_error_text := substrb(hr_utility.get_message, 1, 240);
3339 --
3340 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3341 l_status_tbl(g_count) :='F';
3342 l_message_tbl(g_count) := l_error_text;
3343 --
3344 raise line_error_occurred;
3345 end;
3346 --
3347 hr_utility.set_location('payplnk.validate_lines',20);
3348 --
3349 -- If only an element type id has been entered, ensure it is valid.
3350 --
3351 else
3352 begin
3353 select 'x' -- ,business_group_id -- CWA
3354 into l_element_exists -- ,l_business_group_id -- CWA
3355 from pay_element_types_f elt
3356 where elt.element_type_id = g_line_record.element_type_id
3357 and g_line_record.effective_date between elt.effective_start_date
3358 and elt.effective_end_date
3359 and (elt.business_group_id +0 = p_business_group_id
3360 or (elt.business_group_id is null
3361 and elt.legislation_code = l_legislation_code)
3362 or (elt.business_group_id is null
3363 and elt.legislation_code is null));
3364 --
3365 exception
3366 when no_data_found then
3367 g_line_error := true;
3368 g_count := g_count + 1;
3369 --
3370 hr_utility.set_message(801,'HR_7464_PLK_NOT_ELGBLE_ELE_TYP');
3371 hr_utility.set_message_token('ELEMENT_TYPE_ID',
3372 g_line_record.element_type_id);
3373 l_error_text := substrb(hr_utility.get_message, 1, 240);
3374 --
3375 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3376 l_status_tbl(g_count) :='F';
3377 l_message_tbl(g_count) := l_error_text;
3378 --
3379 raise line_error_occurred;
3380 end;
3381 end if;
3382 --
3383 hr_utility.set_location('payplnk.validate_lines',25);
3384 -- Moving the following code into the validate procedure.
3385 -- --
3386 -- -- Ensure valid assignment details have been entered.
3387 -- --
3388 -- l_column_name1 := 'ASSIGNMENT ID';
3389 -- l_column_name2 := 'ASSIGNMENT NUMBER';
3390 -- --
3391 -- --
3392 -- -- If only an assignment number has been entered, ensure it is valid.
3393 -- --
3394 -- if (g_line_record.assignment_id is null) and
3395 -- (g_line_record.assignment_number is not null) then
3396 -- begin
3397 -- select asg.assignment_id
3398 -- into l_assignment_id
3399 -- from per_assignments_f asg
3400 -- where asg.assignment_number =
3401 -- g_line_record.assignment_number
3402 -- and g_line_record.effective_date between asg.effective_start_date
3403 -- and asg.effective_end_date
3404 -- and asg.business_group_id + 0 = p_business_group_id;
3405 -- --
3406 -- g_line_record.assignment_id := l_assignment_id;
3407 -- not_upper := false;
3408 -- --
3409 -- exception
3410 -- when no_data_found then
3411 -- not_upper := true;
3412 -- end;
3413 -- -- If upper case is not found, try again for mixed case
3414 -- --
3415 -- begin
3416 -- if(not_upper = true) then
3417 -- select asg.assignment_id
3418 -- into l_assignment_id
3419 -- from per_assignments_f asg
3420 -- where upper(asg.assignment_number) =
3421 -- (g_line_record.assignment_number)
3422 -- and g_line_record.effective_date between
3423 -- asg.effective_start_date
3424 -- and asg.effective_end_date
3425 -- and asg.business_group_id = p_business_group_id;
3426 -- end if;
3427 -- exception
3428 -- when no_data_found then
3429 -- g_line_error := true;
3430 -- g_count := g_count +1;
3431 -- --
3432 -- hr_utility.set_message(801,'HR_7466_PLK_NOT_ELGBLE_ASS_NUM');
3433 -- hr_utility.set_message_token('ASSIGNMENT_NUMBER',
3434 -- g_line_record.assignment_number);
3435 -- l_error_text := substrb(hr_utility.get_message, 1, 240);
3436 -- --
3437 -- l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3438 -- l_status_tbl(g_count) :='F';
3439 -- l_message_tbl(g_count) := l_error_text;
3440 -- --
3441 -- raise line_error_occurred;
3442 -- end;
3443 -- --
3444 -- hr_utility.set_location('payplnk.validate_lines',30);
3445 -- --
3446 -- -- If both assignment id and assignment number are null, raise error.
3447 -- --
3448 -- elsif (g_line_record.assignment_id is null) and
3449 -- (g_line_record.assignment_number is null) then
3450 -- raise both_nulls;
3451 -- --
3452 -- -- If both assignment id and assignment number have been entered, ensure
3453 -- -- that they are consistent.
3454 -- --
3455 -- --
3456 -- elsif (g_line_record.assignment_id is not null) and
3457 -- (g_line_record.assignment_number is not null) then
3458 -- begin
3459 -- select 'x'
3460 -- into l_assignment_exists
3461 -- from per_assignments_f asg
3462 -- where asg.assignment_number =
3463 -- g_line_record.assignment_number
3464 -- and asg.assignment_id = g_line_record.assignment_id
3465 -- and g_line_record.effective_date between asg.effective_start_date
3466 -- and asg.effective_end_date
3467 -- and asg.business_group_id + 0 = p_business_group_id;
3468 -- --
3469 -- not_upper := false;
3470 -- --
3471 -- exception
3472 -- when no_data_found then
3473 -- not_upper := true;
3474 -- end;
3475 -- --
3476 -- --
3477 -- --
3478 -- begin
3479 -- if(not_upper = true) then
3480 -- select 'x'
3481 -- into l_assignment_exists
3482 -- from per_assignments_f asg
3483 -- where upper(asg.assignment_number) =
3484 -- upper(g_line_record.assignment_number)
3485 -- and asg.assignment_id = g_line_record.assignment_id
3486 -- and g_line_record.effective_date between asg.effective_start_date
3487 -- and asg.effective_end_date
3488 -- and asg.business_group_id +0 = p_business_group_id;
3489 -- end if;
3490 -- exception
3491 -- when no_data_found then
3492 -- g_line_error := true;
3493 -- g_count := g_count + 1;
3494 -- --
3495 -- hr_utility.set_message(801,'HR_7479_PLK_INCONSISTENT_ASS');
3496 -- hr_utility.set_message_token('ASSIGNMENT_ID',
3497 -- g_line_record.assignment_id);
3498 -- hr_utility.set_message_token('ASSIGNMENT_NUMBER',
3499 -- g_line_record.assignment_number);
3500 -- l_error_text := substrb(hr_utility.get_message, 1, 240);
3501 -- --
3502 -- l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3503 -- l_status_tbl(g_count) :='F';
3504 -- l_message_tbl(g_count) := l_error_text;
3505 -- --
3506 -- raise line_error_occurred;
3507 -- end;
3508 -- --
3509 -- hr_utility.set_location('payplnk.validate_lines',35);
3510 -- --
3511 -- -- If only an assignment id has been entered, ensure that it is valid.
3512 -- --
3513 -- else
3514 -- begin
3515 -- select 'x'
3516 -- into l_assignment_exists
3517 -- from per_assignments_f asg
3518 -- where asg.assignment_id = g_line_record.assignment_id
3519 -- and g_line_record.effective_date between asg.effective_start_date
3520 -- and asg.effective_end_date
3521 -- and asg.business_group_id +0 = p_business_group_id;
3522 -- --
3523 -- exception
3524 -- when no_data_found then
3525 -- g_line_error := true;
3526 -- g_count := g_count + 1;
3527 -- --
3528 -- hr_utility.set_message(801,'HR_7467_PLK_NOT_ELGBLE_ASS_ID');
3529 -- hr_utility.set_message_token('ASSIGNMENT_ID',
3530 -- g_line_record.assignment_id);
3531 -- l_error_text := substrb(hr_utility.get_message, 1, 240);
3532 -- --
3533 -- l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3534 -- l_status_tbl(g_count) :='F';
3535 -- l_message_tbl(g_count) := l_error_text;
3536 -- --
3537 -- raise line_error_occurred;
3538 -- end;
3539 -- end if;
3540 --
3541 hr_utility.set_location('payplnk.validate_lines',40);
3542 --
3543 -- Validate the entry values, if entered.
3544 --
3545 l_entry_value_tbl(1) := g_line_record.value_1;
3546 l_entry_value_tbl(2) := g_line_record.value_2;
3547 l_entry_value_tbl(3) := g_line_record.value_3;
3548 l_entry_value_tbl(4) := g_line_record.value_4;
3549 l_entry_value_tbl(5) := g_line_record.value_5;
3550 l_entry_value_tbl(6) := g_line_record.value_6;
3551 l_entry_value_tbl(7) := g_line_record.value_7;
3552 l_entry_value_tbl(8) := g_line_record.value_8;
3553 l_entry_value_tbl(9) := g_line_record.value_9;
3554 l_entry_value_tbl(10) := g_line_record.value_10;
3555 l_entry_value_tbl(11) := g_line_record.value_11;
3556 l_entry_value_tbl(12) := g_line_record.value_12;
3557 l_entry_value_tbl(13) := g_line_record.value_13;
3558 l_entry_value_tbl(14) := g_line_record.value_14;
3559 l_entry_value_tbl(15) := g_line_record.value_15;
3560 --
3561 -- If input values id(s) exist for the element type, pass it and its
3562 -- corressponding entry value into a temporary PL/SQL table.
3563 --
3564 open csr_table_inp_ids(
3565 -- p_business_group_id,
3566 g_line_record.element_type_id,
3567 g_line_record.effective_date);
3568 loop
3569 fetch csr_table_inp_ids into l_input_value_id, l_uom,
3570 l_lookup_type, l_value_set_id,
3571 l_input_curr_code;
3572 exit when csr_table_inp_ids%NOTFOUND;
3573 --
3574 i := i+1;
3575 l_input_id_exists := true;
3576 l_passed_inp_tbl(i) := l_input_value_id;
3577 l_inpv_uom_tbl(i) := l_uom;
3578 l_abs_type_tbl(i) := 0;
3579 if l_entry_value_tbl(i) is not null then
3580 --
3581 -- Checks whether the upgrade has been performed.
3582 --
3583 if l_bee_iv_upgrade = 'N' THEN
3584 hr_utility.set_location('payplnk.validate_lines',41);
3585 --
3586 -- BEE now handles input value of date in canonical format.
3587 -- However the EE API expects the data in the DD-MON-YYYY format.
3588 -- The DD-MON-YYYY is the default format of the fnd_date.
3589 --
3590 if l_inpv_uom_tbl(i) = 'D' then
3591 begin
3592 -- bug no. 3734946
3593 /* l_passed_val_tbl(i) := substrb(
3594 fnd_date.date_to_displaydate(
3595 fnd_date.canonical_to_date(l_entry_value_tbl(i))
3596 ),1,60); */
3597
3598 -- BUG 11830805
3599 /* l_passed_val_tbl(i) := fnd_date.date_to_displaydate(
3600 fnd_date.canonical_to_date(l_entry_value_tbl(i))); */
3601
3602 l_passed_val_tbl(i) := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_entry_value_tbl(i)), calendar_aware=>FND_DATE.calendar_aware_alt);
3603
3604 -- BUG 11830805
3605
3606 exception
3607 when others then
3608 close csr_table_inp_ids;
3609 raise invalid_date_format;
3610 end;
3611 else
3612 -- bug no. 3734946
3613 /* l_passed_val_tbl(i) := substrb(l_entry_value_tbl(i),1,60);*/
3614 hr_utility.set_location('payplnk.validate_lines',42);
3615 l_passed_val_tbl(i) := l_entry_value_tbl(i);
3616 end if;
3617 --
3618 else
3619 --
3620 -- bug no. 3734946
3621 /* l_passed_val_tbl(i) := substrb(convert_internal_to_display(l_entry_value_tbl(i),
3622 l_uom,
3623 l_lookup_type,
3624 l_value_set_id,
3625 l_input_curr_code),1,60); */
3626 hr_utility.set_location('payplnk.validate_lines',43);
3627 l_passed_val_tbl(i) := convert_internal_to_display(l_entry_value_tbl(i),
3628 l_uom,
3629 l_lookup_type,
3630 l_value_set_id,
3631 l_input_curr_code);
3632 hr_utility.set_location('payplnk.validate_lines',44);
3633 --
3634 end if;
3635 --
3636 else
3637 l_passed_val_tbl(i) := null;
3638 end if;
3639 --
3640 hr_utility.trace('INPUT VALUE ID :'||l_passed_inp_tbl(i));
3641 hr_utility.trace('INPUT VALUE :'||l_passed_val_tbl(i));
3642 --
3643 c_entry_values := c_entry_values + 1;
3644
3645 -- Exit the loop if it has reached the 15th input value.
3646 if i >= 15 then
3647 exit;
3648 end if;
3649
3650 end loop;
3651 close csr_table_inp_ids;
3652 --
3653 hr_utility.set_location('payplnk.validate_lines',45);
3654 --
3655 -- If there are no input value ids for the element type or the
3656 -- entry values exceed the number of input value id(s) , raise an error.
3657 --
3658 i := i+1;
3659 --
3660 for k in i..15 loop
3661 if (l_entry_value_tbl(k) is not null) and
3662 (l_input_id_exists = false) then
3663 --
3664 raise inputs_not_required;
3665 elsif (l_entry_value_tbl(k) is not null) and
3666 (l_input_id_exists = true) then
3667 --
3668 raise invalid_entry_value;
3669 end if;
3670 end loop;
3671 --
3672 hr_utility.set_location('payplnk.validate_lines', 50);
3673 --
3674 -- Retrieve the link id for the element type and assignment combination.
3675 --
3676 hr_utility.trace('ASS: '||g_line_record.assignment_id);
3677 hr_utility.trace('ETI: '||g_line_record.element_type_id);
3678 hr_utility.trace('EDT: '||g_line_record.effective_date);
3679 hr_utility.trace('LLI: '||l_link_id);
3680 hr_utility.trace('END');
3681 --
3682 l_link_id := hr_entry_api.get_link(g_line_record.assignment_id,
3683 g_line_record.element_type_id,
3684 g_line_record.effective_date);
3685 --
3686 hr_utility.set_location('payplnk.validate_lines',55);
3687 --
3688 -- Raise an error if a link does not exist on the effective date.
3689 --
3690 if l_link_id is null then
3691 raise element_entry_not_eligible;
3692 end if;
3693 --
3694 --
3695 -- Check for time unit, and if so absence attendance type and accrual plan
3696 --
3697 t := i-1;
3698 --
3699 for l in 1..t loop
3700 --
3701 --
3702 -- if a time unit get absence_attendance_type + check with accrual plan
3703 --
3704 if (l_inpv_uom_tbl(l) = 'ND' or l_inpv_uom_tbl(l) = 'H_HH' or l_inpv_uom_tbl(l) = 'H_HHMM'
3705 or l_inpv_uom_tbl(l) = 'H_HHMMSS' or l_inpv_uom_tbl(l) = 'H_DECIMAL1'
3706 or l_inpv_uom_tbl(l) = 'H_DECIMAL2' or l_inpv_uom_tbl(l) = 'H_DECIMAL3'
3707 or l_inpv_uom_tbl(l) = 'HOURS') then
3708 --
3709 hr_utility.set_location('payplnk.validate_lines',53);
3710 --
3711 l_absence_attendance_type_id := null;
3712 --
3713 -- The hours input value may be associated with an Absence
3714 -- Attendance Type, so need to try to retrieve these details.
3715 --
3716 open csr_attendance_types(p_business_group_id,
3717 l_passed_inp_tbl(l),
3718 nvl(g_line_record.effective_start_date,
3719 nvl(g_line_record.effective_end_date,
3720 g_line_record.effective_date)),
3721 nvl(g_line_record.effective_end_date,
3722 nvl(g_line_record.effective_start_date,
3723 g_line_record.effective_date)));
3724 fetch csr_attendance_types into l_absence_attendance_type_id,
3725 l_hours_or_days;
3726 close csr_attendance_types;
3727 -- The above cursor replaces the select statement below. This is due to more
3728 -- than one entry available for a given input_value_id and a business_group_id.
3729 -- begin
3730 -- select paat.absence_attendance_type_id
3731 -- , paat.hours_or_days
3732 -- into l_absence_attendance_type_id
3733 -- , l_hours_or_days
3734 -- from per_absence_attendance_types paat
3735 -- where paat.input_value_id = l_passed_inp_tbl(l)
3736 -- and paat.business_group_id +0 = p_business_group_id;
3737 -- exception
3738 -- when no_data_found then null;
3739 -- end;
3740 --
3741 hr_utility.set_location('payplnk.validate_lines',54);
3742 --
3743 if l_absence_attendance_type_id is not null then
3744 --
3745 l_abs_type_tbl(l) := l_absence_attendance_type_id;
3746 l_abs_hod_tbl(l) := l_hours_or_days;
3747 l_creator_type := 'A';
3748 --
3749 end if;
3750 --
3751 hr_utility.set_location('payplnk.validate_lines',56);
3752 --
3753 --
3754 -- WW Bug# 282299
3755 -- The hours input value may also be associated with an Accrual Plan.
3756 -- If it is, check the ineligibility period for the Accrual. Prevent
3757 -- entry of time taken against an accrual during the ineligible period.
3758 -- This can be handled thru PayMIX, and should also be handled when
3759 -- accrual time taken entries are created via the core Element Entry
3760 -- screen; furthermore, it should be handled by the core Enter Absence
3761 -- screen - ie. absences and accruals can share the same time taken
3762 -- input value.
3763 --
3764 hr_us_accrual_plans.get_accrual_ineligibility(
3765 p_iv_id => l_passed_inp_tbl(l),
3766 p_bg_id => p_business_group_id,
3767 p_asg_id => g_line_record.assignment_id,
3768 p_sess_date => g_line_record.effective_date,
3769 p_eligible => l_eligible);
3770 --
3771 hr_utility.set_location('payplnk.validate_lines',57);
3772 --
3773 if l_eligible = 'N' then
3774 hr_utility.set_location('payplnk.validate_lines',58);
3775 hr_utility.trace('Assignment is in ineligible period for this accrual plan. ');
3776 hr_utility.set_message(801,'PAY_7853_PDT_INELIG_ACCRUAL');
3777 hr_utility.raise_error;
3778 end if;
3779 end if;
3780 end loop;
3781 --
3782 hr_utility.set_location('payplnk.validate_lines',59);
3783 --
3784 -- Validate the costing details entered.
3785 --
3786 -- S.Sinha pseudo bug 493304, for performance fix for BT.
3787 -- The statement below has the business group id index disabled now.
3788 --
3789 select el.costable_type
3790 into l_costable_type
3791 from pay_element_links_f el
3792 where el.element_link_id = l_link_id
3793 and el.business_group_id + 0 = p_business_group_id
3794 and g_line_record.effective_date between el.effective_start_date
3795 and el.effective_end_date;
3796 --
3797 -- Check if any costing details have been entered
3798 --
3799 if (g_line_record.concatenated_segments is not null) or -- { costing exists
3800 (g_line_record.segment1 is not null) or
3801 (g_line_record.segment2 is not null) or
3802 (g_line_record.segment3 is not null) or
3803 (g_line_record.segment4 is not null) or
3804 (g_line_record.segment5 is not null) or
3805 (g_line_record.segment6 is not null) or
3806 (g_line_record.segment7 is not null) or
3807 (g_line_record.segment8 is not null) or
3808 (g_line_record.segment9 is not null) or
3809 (g_line_record.segment10 is not null) or
3810 (g_line_record.segment11 is not null) or
3811 (g_line_record.segment12 is not null) or
3812 (g_line_record.segment13 is not null) or
3813 (g_line_record.segment14 is not null) or
3814 (g_line_record.segment15 is not null) or
3815 (g_line_record.segment16 is not null) or
3816 (g_line_record.segment17 is not null) or
3817 (g_line_record.segment18 is not null) or
3818 (g_line_record.segment19 is not null) or
3819 (g_line_record.segment20 is not null) or
3820 (g_line_record.segment21 is not null) or
3821 (g_line_record.segment22 is not null) or
3822 (g_line_record.segment23 is not null) or
3823 (g_line_record.segment24 is not null) or
3824 (g_line_record.segment25 is not null) or
3825 (g_line_record.segment26 is not null) or
3826 (g_line_record.segment27 is not null) or
3827 (g_line_record.segment28 is not null) or
3828 (g_line_record.segment29 is not null) or
3829 (g_line_record.segment30 is not null) then
3830
3831 if (l_costable_type = 'N') then -- { Costable_type N
3832 --
3833 g_count := g_count + 1;
3834 hr_utility.set_message(801, 'HR_7453_PLK_NON_COSTABLE_ELE');
3835 l_error_text := substrb(hr_utility.get_message, 1, 240);
3836 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3837 l_status_tbl(g_count) := 'W';
3838 l_message_tbl(g_count) := l_error_text;
3839 --
3840 else
3841 --
3842 -- Check if a cost_allocation_structure exists, if not, raise error.
3843 --
3844 if l_cost_allocation_structure is null then -- { no structure
3845 raise no_cost_allocation_structure;
3846 end if; -- } no structure
3847 --
3848 -- Retrieve/generate a cost keyflex id for the costing details.
3849 --
3850 hr_utility.set_location('payplnk.validate_lines',60);
3851 --
3852 g_line_record.cost_allocation_keyflex_id :=
3853 hr_entry.maintain_cost_keyflex(
3854 l_cost_allocation_structure,
3855 -1,
3856 g_line_record.concatenated_segments,
3857 'N',
3858 null,
3859 null,
3860 g_line_record.segment1,
3861 g_line_record.segment2,
3862 g_line_record.segment3,
3863 g_line_record.segment4,
3864 g_line_record.segment5,
3865 g_line_record.segment6,
3866 g_line_record.segment7,
3867 g_line_record.segment8,
3868 g_line_record.segment9,
3869 g_line_record.segment10,
3870 g_line_record.segment11,
3871 g_line_record.segment12,
3872 g_line_record.segment13,
3873 g_line_record.segment14,
3874 g_line_record.segment15,
3875 g_line_record.segment16,
3876 g_line_record.segment17,
3877 g_line_record.segment18,
3878 g_line_record.segment19,
3879 g_line_record.segment20,
3880 g_line_record.segment21,
3881 g_line_record.segment22,
3882 g_line_record.segment23,
3883 g_line_record.segment24,
3884 g_line_record.segment25,
3885 g_line_record.segment26,
3886 g_line_record.segment27,
3887 g_line_record.segment28,
3888 g_line_record.segment29,
3889 g_line_record.segment30);
3890 --
3891 --
3892 end if; -- } costable_type N else
3893 --
3894 end if; -- } costing exists
3895 --
3896 hr_utility.set_location('payplnk.validate_lines',65);
3897 --
3898 -- The batch line core validation checks have been carried out
3899 -- successfully, hence the legislative hook checks (if any) and
3900 -- then the user line validation checks can be called.
3901 --
3902 l_user_status := 'V';
3903
3904 -- The legislative hook to line validation should be executed here
3905 --
3906 -- call legislative header check hook if required
3907 --
3908 hr_utility.set_location('payplnk.validate_lines',66);
3909 if l_leg_line_check = TRUE then
3910 --
3911 begin
3912 hr_utility.set_location('payplnk.validate_lines',67);
3913 statem := 'BEGIN
3914 pay_'||lower(l_legislation_code)||'_bee.validate_line(:batch_line_id, :valid, :leg_message, :line_changed); END;';
3915 --
3916 if pay_core_utils.get_sql_cursor(statem,sql_curs) then
3917 --
3918 dbms_sql.bind_variable(sql_curs, 'batch_line_id', g_line_record.batch_line_id);
3919 dbms_sql.bind_variable(sql_curs, 'valid', l_valid);
3920 dbms_sql.bind_variable(sql_curs, 'leg_message', l_leg_message, 240);
3921 dbms_sql.bind_variable(sql_curs, 'line_changed', l_line_changed);
3922 --
3923 rows_processed := dbms_sql.execute(sql_curs);
3924 --
3925 dbms_sql.variable_value(sql_curs, 'valid', l_valid);
3926 dbms_sql.variable_value(sql_curs, 'leg_message', l_leg_message);
3927 dbms_sql.variable_value(sql_curs, 'line_changed', l_line_changed);
3928 --
3929 else
3930 raise error_occurred;
3931 end if;
3932 --
3933 exception
3934 when others then
3935 hr_utility.set_location('payplnk.validate_lines',68);
3936 --
3937 g_line_error := true;
3938 g_count := g_count + 1;
3939 --
3940 hr_utility.set_message(801,'HR_7481_PLK_USR_CHECK_ERROR');
3941 hr_utility.set_message_token('USER_PROCEDURE',
3942 'the legislative batch line procedure');
3943 l_error_text := substrb((hr_utility.get_message||' '||sqlerrm),1,240);
3944 --
3945 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3946 l_status_tbl(g_count) := 'F';
3947 l_message_tbl(g_count) := l_error_text;
3948 --
3949 raise line_error_occurred;
3950 end;
3951 --
3952 if l_valid = 1 then
3953 hr_utility.set_location('payplnk.validate_lines',688);
3954 --
3955 g_line_error := true;
3956 g_count := g_count + 1;
3957 --
3958 hr_utility.set_message(801,'HR_7469_PLK_USR_STATUS_INVLD');
3959 hr_utility.set_message_token('USER_PROCEDURE',
3960 'the legislative batch line procedure');
3961 l_error_text := substrb((hr_utility.get_message||' '||l_leg_message),1,240);
3962 --
3963 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
3964 l_status_tbl(g_count) := 'F';
3965 l_message_tbl(g_count) := l_error_text;
3966 --
3967 raise line_error_occurred;
3968 end if;
3969 --
3970 if l_line_changed = 0 then
3971 --
3972 -- legislative hook changed the line details
3973 -- so we select them back and go back to beginning of validation for
3974 -- the line
3975 --
3976 select *
3977 into g_line_record2
3978 from pay_batch_lines bal
3979 where bal.batch_id = p_batch_id
3980 and bal.batch_line_id = g_line_record.batch_line_id;
3981
3982 g_line_record := g_line_record2;
3983
3984 goto start_validation;
3985 end if;
3986 --
3987 end if;
3988 --
3989 -- call user line validation checks
3990 --
3991 begin
3992 pay_user_check.validate_line (g_line_record.batch_line_id,
3993 l_user_status,
3994 l_user_message);
3995 exception
3996 --
3997 -- If an unhandled error occurred during the user line validation then
3998 -- raise an error.
3999 --
4000
4001
4002 when others then
4003 --
4004 g_line_error := true;
4005 g_count := g_count + 1;
4006 --
4007 hr_utility.set_message(801,'HR_7481_PLK_USR_CHECK_ERROR');
4008 hr_utility.set_message_token('USER_PROCEDURE',
4009 'the user batch line procedure');
4010 l_error_text := substrb((hr_utility.get_message||' '||sqlerrm),1,240);
4011 --
4012 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4013 l_status_tbl(g_count) := 'F';
4014 l_message_tbl(g_count) := l_error_text;
4015 --
4016 raise line_error_occurred;
4017 end;
4018 --
4019 hr_utility.set_location('payplnk.validate_lines',70);
4020 --
4021 -- If a status has not been returned raise an error.
4022 --
4023 if l_user_status is null then
4024 --
4025 g_line_error := true;
4026 g_count := g_count + 1;
4027 --
4028 hr_utility.set_message(801,'HR_7468_PLK_USR_STATUS_NULL');
4029 hr_utility.set_message_token('USER_PROCEDURE',
4030 'the user batch line procedure');
4031 l_error_text := substrb(hr_utility.get_message, 1, 240);
4032 --
4033 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4034 l_status_tbl(g_count) := 'F';
4035 l_message_tbl(g_count) := l_error_text;
4036 --
4037 raise line_error_occurred;
4038 --
4039 else
4040 open csr_status_chk(l_user_status);
4041 fetch csr_status_chk into l_usr_status_chk;
4042 --
4043 if csr_status_chk%NOTFOUND then
4044 --
4045 -- An invalid status has been returned, raise an error.
4046 --
4047 g_line_error := true;
4048 g_count := g_count + 1;
4049 --
4050 hr_utility.set_message(801,'HR_7469_PLK_USR_STATUS_INVLD');
4051 hr_utility.set_message_token('USER_PROCEDURE',
4052 'the user batch line procedure');
4053 l_error_text := substrb(hr_utility.get_message, 1, 240);
4054 --
4055 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4056 l_status_tbl(g_count) := 'F';
4057 l_message_tbl(g_count) := l_error_text;
4058 --
4059 close csr_status_chk;
4060 --
4061 raise line_error_occurred;
4062 --
4063 elsif upper(l_user_status) in ('U','T') then
4064 --
4065 -- A status has been returned tht is not valid in this context, raise
4066 -- an error.
4067 --
4068 g_line_error := true;
4069 g_count := g_count + 1;
4070 --
4071 hr_utility.set_message(801,'HR_7470_PLK_USR_STATUS_INVLD_C');
4072 hr_utility.set_message_token('USER_PROCEDURE',
4073 'the user batch line procedure');
4074 l_error_text := substrb(hr_utility.get_message, 1, 240);
4075 --
4076 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4077 l_status_tbl(g_count) := 'F';
4078 l_message_tbl(g_count) := l_error_text;
4079 --
4080 close csr_status_chk;
4081 --
4082 raise line_error_occurred;
4083 --
4084 -- A vaid status has been returned. If status is valid or warning and a
4085 -- message has been returned
4086 --
4087 else
4088 close csr_status_chk;
4089 --
4090 if (upper(l_user_status) in ('V','W')) and
4091 (l_user_message is not null) then
4092 l_general_message := true;
4093 g_count := g_count + 1;
4094 --
4095 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4096 --
4097 if upper(l_user_status) = 'W' then
4098 l_status_tbl(g_count) := 'W';
4099 else
4100 l_status_tbl(g_count) := 'I';
4101 end if;
4102 --
4103 l_message_tbl(g_count) := l_user_message;
4104 --
4105 elsif upper(l_user_status) = 'E' then
4106 g_line_error := true;
4107 g_count := g_count + 1;
4108 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4109 l_status_tbl(g_count) := 'F';
4110 l_message_tbl(g_count) := l_user_message;
4111 raise line_error_occurred;
4112 end if;
4113 end if;
4114 end if;
4115 --
4116 hr_utility.set_location('payplnk.validate_lines',75);
4117 --
4118 -- Batch line has been validated successfully. Attempt to insert/update
4119 -- an element entry into the base tables.
4120 --
4121
4122 select et.multiple_entries_allowed_flag
4123 , et.processing_type
4124 into l_multiple_entries_allowed
4125 , l_processing_type
4126 from pay_element_types_f et
4127 where et.element_type_id =g_line_record.element_type_id
4128 and g_line_record.effective_date between
4129 et.effective_start_date and et.effective_end_date;
4130
4131 -- If there is an associated Absence Attendance Type, then need
4132 -- to insert the absence attendance details for the assignment.
4133 --
4134 -- Use the optionally entered effective start and effective end dates
4135
4136 if (g_line_record.effective_start_date is not null) then
4137 l_start_date := g_line_record.effective_start_date;
4138 else
4139 if (g_line_record.effective_end_date is not null) then
4140 l_start_date := g_line_record.effective_end_date;
4141 else
4142 l_start_date := g_line_record.effective_date;
4143 end if;
4144 end if;
4145
4146 if (g_line_record.effective_end_date is not null) then
4147 l_end_date := g_line_record.effective_end_date;
4148 elsif l_processing_type = 'N' then
4149 --
4150 -- Default the end date if its non-recurring. Recurring entries
4151 -- can continue through to the end of time.
4152 -- .
4153 if (g_line_record.effective_start_date is not null) then
4154 l_end_date := g_line_record.effective_start_date;
4155 else
4156 l_end_date := g_line_record.effective_date;
4157 end if;
4158 end if;
4159 --
4160 for l in 1..t loop
4161 if (l_abs_type_tbl(l) <> 0) then
4162
4163 -- if (l_abs_hod_tbl(l) = 'D') then
4164 -- l_absence_days := l_passed_val_tbl(l);
4165 -- l_absence_hours := NULL;
4166 -- else
4167 -- l_absence_days := NULL;
4168 -- -- Start of fix 3156665
4169 -- /* Converting the value entered to a numberic value in hours.
4170 -- 1) If value entered in HH:MI format, then calculating the
4171 -- the total absence hours.
4172 -- 2) If value entered in decimal format like 23.5, will be treated
4173 -- as 23 hours 30 minutes */
4174 -- --
4175 -- --l_absence_hours := l_passed_val_tbl(l);
4176 -- l_passed_val_tbl(l) := ltrim(rtrim(l_passed_val_tbl(l)));
4177 -- if substr(l_passed_val_tbl(l), 3, 1) = ':' then
4178 -- l_absence_hours := to_number((((substr(l_passed_val_tbl(l), 1, 2) * 60) +
4179 -- substr(l_passed_val_tbl(l), 4, 2))/60));
4180 -- else
4181 -- l_absence_hours := to_number(substr(l_passed_val_tbl(l), 1, 5));
4182 -- end if;
4183 -- -- End of 3156665
4184 -- end if;
4185
4186 -- per_absence_attendances_pkg3.insert_abs_for_bee(
4187 -- p_session_date => g_line_record.effective_date,
4188 -- p_absence_att_type_id => l_abs_type_tbl(l),
4189 -- p_absence_attendance_id => l_absence_attendance_id,
4190 -- p_batch_id => p_batch_id,
4191 -- p_assignment_id => g_line_record.assignment_id,
4192 -- p_absence_days => l_absence_days,
4193 -- p_absence_hours => l_absence_hours,
4194 -- p_date_start => l_start_date,
4195 -- p_date_end => l_end_date,
4196 -- p_warning_table => l_warning_table,
4197 -- p_error_table => l_error_table);
4198
4199 -- per_absence_attendances_pkg3.insert_abs_for_bee(
4200 -- p_session_date => g_line_record.effective_date,
4201 -- p_absence_att_type_id => l_abs_type_tbl(l),
4202 -- p_assignment_id => g_line_record.assignment_id,
4203 -- p_batch_id => p_batch_id,
4204 -- p_hours_or_days => l_abs_hod_tbl(l),
4205 -- p_format => l_inpv_uom_tbl(l),
4206 -- p_value => l_passed_val_tbl(l),
4207 -- p_date_start => l_start_date,
4208 -- p_date_end => l_end_date,
4209 -- p_absence_attendance_id => l_absence_attendance_id,
4210 -- p_warning_table => l_warning_table,
4211 -- p_error_table => l_error_table);
4212
4213 --
4214 -- This overloaded Absence API handles creation of both
4215 -- the absence and element entry.
4216 --
4217 per_absence_attendances_pkg3.insert_abs_for_bee(
4218 p_absence_att_type_id => l_abs_type_tbl(l),
4219 p_batch_id => p_batch_id,
4220 p_asg_act_id => p_asg_act_id,
4221 p_entry_values_count => c_entry_values,
4222 p_hours_or_days => l_abs_hod_tbl(l),
4223 p_format => l_inpv_uom_tbl(l),
4224 p_value => l_passed_val_tbl(l), -- A
4225 p_date_start => l_start_date,
4226 p_date_end => l_end_date,
4227 p_absence_attendance_id => l_absence_attendance_id,
4228 p_line_record => g_line_record,
4229 p_passed_inp_tbl => l_passed_inp_tbl,
4230 p_passed_val_tbl => l_passed_val_tbl, -- B
4231 p_warning_table => l_warning_table,
4232 p_error_table => l_error_table);
4233 --
4234
4235 if l_warning_table.COUNT <> 0 then
4236 hr_utility.set_location('payplnk.validate_lines',76);
4237 for l_warning_num in 1..l_warning_table.COUNT loop
4238 hr_utility.trace(l_warning_table(l_warning_num));
4239 if l_warning_table(l_warning_num) = 'EE_CREATED_BY_ABSENCE_API' then
4240 hr_utility.set_location('payplnk.validate_lines',77);
4241 absence_entry_already_created := TRUE;
4242 else
4243 g_count := g_count + 1;
4244 hr_utility.set_message(801,l_warning_table(l_warning_num));
4245 l_error_text := substrb(hr_utility.get_message, 1, 240);
4246 --
4247 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4248 -- Changed from ' V' to 'W'. So that Pay_message_lines would not
4249 -- error outs.
4250 l_status_tbl(g_count) := 'W';
4251 l_message_tbl(g_count) := l_error_text;
4252 end if;
4253 end loop;
4254 end if;
4255
4256 if l_error_table.COUNT <> 0 then
4257 for l_error_num in 1..l_error_table.COUNT loop
4258 g_line_error := true;
4259 g_count := g_count + 1;
4260 hr_utility.set_message(801,l_error_table(l_error_num));
4261 l_error_text := substrb(hr_utility.get_message, 1, 240);
4262 --
4263 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4264 l_status_tbl(g_count) := 'F';
4265 l_message_tbl(g_count) := l_error_text;
4266 end loop;
4267 raise line_error_occurred;
4268 end if;
4269
4270 end if;
4271 end loop;
4272 --
4273 -- The per_absence_attendances_pkg3.insert_abs_for_bee procedure now creates both
4274 -- an absence record AND an absence element entry. Hence we should move to next
4275 -- batch line if an absence entry has been made.
4276 --
4277
4278 if absence_entry_already_created = FALSE then
4279
4280 --
4281 -- Check to see if any element entry(s) for the required combination
4282 -- exist.
4283 --
4284 open csr_element_entries(l_link_id,
4285 g_line_record.effective_date,
4286 g_line_record.element_type_id,
4287 g_line_record.assignment_id,
4288 g_line_record.date_earned);
4289 fetch csr_element_entries into l_element_entry_id,
4290 e_creator_type,
4291 e_creator_id,
4292 l_effective_end_date,
4293 l_effective_start_date;
4294 if csr_element_entries%NOTFOUND then
4295 close csr_element_entries;
4296
4297 -- If no entries of the required combination exist, check the
4298 -- appropriate processing flags to see if an attempt can be
4299 -- made to insert the element entry.
4300 --
4301 if l_reject_if_future_changes_chk = false then
4302 --
4303 l_column_name1 := 'REJECT IF FUTURE CHANGES';
4304 --
4305 if g_header_record.reject_if_future_changes is null then
4306 raise column_is_null;
4307 end if;
4308 --
4309 l_reject_if_future_changes_chk := true;
4310 end if;
4311 --
4312 if (g_header_record.reject_if_future_changes = 'Y') and
4313 (l_multiple_entries_allowed = 'N') then
4314 --
4315 open csr_future_existence(l_link_id,
4316 g_line_record.effective_date,
4317 g_line_record.element_type_id,
4318 g_line_record.assignment_id);
4319 fetch csr_future_existence into l_dummy;
4320 if csr_future_existence%FOUND then
4321 close csr_future_existence;
4322 raise future_changes_exist;
4323 end if;
4324 close csr_future_existence;
4325 end if;
4326 --
4327 hr_utility.set_location('payplnk.validate_lines',80);
4328 --
4329 -- An attempt can be made to insert the element entry as all the
4330 -- prerequisite conditions are in place.
4331 --
4332 -- Bug 488335 - new declaration to avoid invalid parameter
4333 -- mode in Oracle 8
4334 g_line_record1 := g_line_record;
4335 begin
4336 payplnk.insert_element_entry(l_link_id,
4337 g_line_record1,
4338 p_asg_act_id,
4339 l_creator_type,
4340 l_absence_attendance_id,
4341 c_entry_values,
4342 l_passed_inp_tbl,
4343 l_passed_val_tbl);
4344 exception
4345 --
4346 -- If an unhandled error occurred during the element entry validation then
4347 -- raise an error.
4348 --
4349 when others then
4350 --
4351 g_line_error := true;
4352 g_count := g_count + 1;
4353 --
4354 l_error_text := substrb((hr_utility.get_message||' '||sqlerrm),1,240);
4355 --
4356 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4357 l_status_tbl(g_count) := 'F';
4358 l_message_tbl(g_count) := l_error_text;
4359 --
4360 raise line_error_occurred;
4361 end;
4362
4363 --
4364 else
4365 --
4366 -- An element entry(s) does already exist for the required combination.
4367 -- Check the appropriate processing flags and wether or not multiple
4368 -- entries are allowed to see if either the entry can be inserted or
4369 -- the existing entry updated.
4370 --
4371 if l_action_if_exists_chk = false then
4372 --
4373 l_column_name1 := 'ACTION IF EXISTS';
4374 --
4375 if g_header_record.action_if_exists is null then
4376 raise column_is_null;
4377 end if;
4378 --
4379 l_action_if_exists_chk := true;
4380 end if;
4381 --
4382 hr_utility.set_location('payplnk.validate_lines',85);
4383 --
4384 if (l_multiple_entries_allowed = 'Y') and
4385 (g_header_record.action_if_exists = 'I') then
4386 --
4387 g_line_record1 := g_line_record;
4388 begin
4389 payplnk.insert_element_entry (l_link_id,
4390 g_line_record1,
4391 p_asg_Act_id,
4392 l_creator_type,
4393 l_absence_attendance_id,
4394 c_entry_values,
4395 l_passed_inp_tbl,
4396 l_passed_val_tbl);
4397 exception
4398 --
4399 -- If an unhandled error occurred during the element entry validation then
4400 -- raise an error.
4401 --
4402 when others then
4403 --
4404 g_line_error := true;
4405 g_count := g_count + 1;
4406 --
4407 l_error_text := substrb((hr_utility.get_message||' '||sqlerrm),1,240);
4408 --
4409 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4410 l_status_tbl(g_count) := 'F';
4411 l_message_tbl(g_count) := l_error_text;
4412 --
4413 raise line_error_occurred;
4414 end;
4415 --
4416 elsif (l_multiple_entries_allowed = 'N') and
4417 (g_header_record.action_if_exists = 'I') then
4418 --
4419 raise multiple_entries_not_allowed;
4420 end if;
4421 --
4422 hr_utility.set_location('payplnk.validate_lines',90);
4423 --
4424 if g_header_record.action_if_exists = 'R' then
4425 --
4426 raise element_entry_exists;
4427 elsif g_header_record.action_if_exists = 'U' then
4428 --
4429 --
4430 -- Bug 2672143. If creator type is 'SP' then don't allow
4431 -- BEE to update the entry.
4432 --
4433 if e_creator_type = 'SP' then
4434 raise non_updatable_creator_type;
4435 end if;
4436 --
4437 fetch csr_element_entries into l_element_entry_id,
4438 e_creator_type,
4439 e_creator_id,
4440 l_effective_end_date,
4441 l_effective_start_date;
4442 if csr_element_entries%FOUND then
4443 close csr_element_entries;
4444 raise more_than_one_entry;
4445 else
4446 --
4447 close csr_element_entries;
4448 --
4449 if l_processing_type = 'N' then
4450 --
4451 update_element_entry('CORRECTION',
4452 l_element_entry_id,
4453 e_creator_type,
4454 e_creator_id,
4455 FALSE,
4456 p_asg_act_id,
4457 g_line_record,
4458 c_entry_values,
4459 l_passed_inp_tbl,
4460 l_passed_val_tbl);
4461 --
4462 else
4463 --
4464 if l_reject_if_future_changes_chk = false then
4465 l_column_name1 := 'REJECT IF FUTURE CHANGES';
4466 --
4467 if g_header_record.reject_if_future_changes is null then
4468 raise column_is_null;
4469 end if;
4470 --
4471 l_reject_if_future_changes_chk := true;
4472 end if;
4473 --
4474 if (g_header_record.reject_if_future_changes = 'Y') and
4475 (l_effective_end_date <> hr_general.end_of_time) then
4476 raise future_changes_exist;
4477 elsif l_date_effective_changes_chk = false then
4478 l_column_name1 := 'DATE EFFECTIVE CHANGES';
4479 --
4480 if g_header_record.date_effective_changes is null then
4481 raise column_is_null;
4482 end if;
4483 --
4484 l_date_effective_changes_chk := true;
4485 end if;
4486 --
4487 if (l_effective_end_date = hr_general.end_of_time) then
4488 --
4489 if g_header_record.date_effective_changes = 'C' then
4490 l_update_mode := 'CORRECTION';
4491 l_allow_rollback := FALSE;
4492 else
4493 l_update_mode := 'UPDATE';
4494 --
4495 if l_effective_start_date = g_line_record.effective_date then
4496 l_allow_rollback := FALSE;
4497 else
4498 l_allow_rollback := TRUE;
4499 e_creator_id := p_batch_id;
4500 e_creator_type := 'H';
4501 end if;
4502 --
4503 end if;
4504 --
4505 -- The prerequisite conditions allow for an attempt to be made to update
4506 -- the existing element entry.
4507 --
4508 update_element_entry(l_update_mode,
4509 l_element_entry_id,
4510 e_creator_type,
4511 e_creator_id,
4512 l_allow_rollback,
4513 p_asg_act_id,
4514 g_line_record,
4515 c_entry_values,
4516 l_passed_inp_tbl,
4517 l_passed_val_tbl);
4518 --
4519 elsif (g_header_record.reject_if_future_changes = 'N') and
4520 (l_effective_end_date <> hr_general.end_of_time) then
4521 --
4522 if g_header_record.date_effective_changes = 'C' then
4523 l_update_mode := 'CORRECTION';
4524 l_allow_rollback := FALSE;
4525 elsif g_header_record.date_effective_changes = 'U' then
4526 l_update_mode := 'UPDATE_CHANGE_INSERT';
4527 --
4528 if l_effective_start_date = g_line_record.effective_date then
4529 l_allow_rollback := FALSE;
4530 else
4531 l_allow_rollback := TRUE;
4532 e_creator_id := p_batch_id;
4533 e_creator_type := 'H';
4534 end if;
4535 --
4536 else
4537 l_update_mode := 'UPDATE_OVERRIDE';
4538 l_allow_rollback := FALSE;
4539 end if;
4540 --
4541 update_element_entry(l_update_mode,
4542 l_element_entry_id,
4543 e_creator_type,
4544 e_creator_id,
4545 l_allow_rollback,
4546 p_asg_act_id,
4547 g_line_record,
4548 c_entry_values,
4549 l_passed_inp_tbl,
4550 l_passed_val_tbl);
4551 end if;
4552 end if;
4553 end if;
4554 end if;
4555 end if;
4556 --
4557 end if;
4558 --
4559 hr_utility.set_location('payplnk.validate_lines',95);
4560 --
4561 exception
4562 when no_data_found then
4563 g_line_error := true;
4564 if l_general_message = false then
4565 g_count := g_count + 1;
4566 end if;
4567 --
4568 hr_utility.set_message(801,'HR_7462_PLK_INVLD_VALUE');
4569 hr_utility.set_message_token('COLUMN_NAME',l_column_name1);
4570 l_error_text := substrb(hr_utility.get_message, 1, 240);
4571 --
4572 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4573 l_status_tbl(g_count) := 'F';
4574 l_message_tbl(g_count) := l_error_text;
4575 --
4576 when column_is_null then
4577 g_line_error := true;
4578 if l_general_message = false then
4579 g_count := g_count + 1;
4580 end if;
4581 --
4582 hr_utility.set_message(801,'HR_7449_PLK_COLUMN_NULL');
4583 hr_utility.set_message_token('COLUMN_NAME',l_column_name1);
4584 l_error_text := substrb(hr_utility.get_message, 1, 240);
4585 --
4586 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4587 l_status_tbl(g_count) := 'F';
4588 l_message_tbl(g_count) := l_error_text;
4589 --
4590 when both_nulls then
4591 g_line_error := true;
4592 g_count := g_count + 1;
4593 --
4594 hr_utility.set_message(801,'HR_7482_PLK_BOTH_COLUMNS_NULL');
4595 hr_utility.set_message_token('COL1',l_column_name1);
4596 hr_utility.set_message_token('COL2',l_column_name2);
4597 l_error_text := substrb(hr_utility.get_message, 1, 240);
4598 --
4599 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4600 l_status_tbl(g_count) := 'F';
4601 l_message_tbl(g_count) := l_error_text;
4602 --
4603 when invalid_entry_value then
4604 g_line_error := true;
4605 g_count := g_count + 1;
4606 --
4607 hr_utility.set_message(801,'HR_7452_PLK_VALS_EXCEED_INPUTS');
4608 l_error_text := substrb(hr_utility.get_message, 1, 240);
4609 --
4610 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4611 l_status_tbl(g_count) := 'F';
4612 l_message_tbl(g_count) := l_error_text;
4613 --
4614 --
4615 when invalid_date_format then
4616 g_line_error := true;
4617 g_count := g_count + 1;
4618 --
4619 hr_utility.set_message(801,'HR_51155_INVAL_DATE_FORMAT');
4620 l_error_text := substrb(hr_utility.get_message, 1, 240);
4621 --
4622 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4623 l_status_tbl(g_count) := 'F';
4624 l_message_tbl(g_count) := l_error_text;
4625 --
4626 when no_cost_allocation_structure then
4627 g_line_error := true;
4628 g_count := g_count + 1;
4629 --
4630 hr_utility.set_message(801,'HR_7460_PLK_NO_CST_ALLC_STRUCT');
4631 hr_utility.set_message_token('BUSINESS_GROUP_ID',p_business_group_id);
4632 l_error_text := substrb(hr_utility.get_message, 1, 240);
4633 --
4634 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4635 l_status_tbl(g_count) := 'F';
4636 l_message_tbl(g_count) := l_error_text;
4637 --
4638 when non_costable_element then
4639 g_line_error := true;
4640 g_count := g_count + 1;
4641 --
4642 hr_utility.set_message(801,'HR_7453_PLK_NON_COSTABLE_ELE');
4643 l_error_text := substrb(hr_utility.get_message, 1, 240);
4644 --
4645 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4646 l_status_tbl(g_count) := 'F';
4647 l_message_tbl(g_count) := l_error_text;
4648 --
4649 when multiple_entries_not_allowed then
4650 g_line_error := true;
4651 if l_general_message = false then
4652 g_count := g_count + 1;
4653 end if;
4654 --
4655 hr_utility.set_message(801,'HR_7454_PLK_MULT_ENTS_NOT_ALLD');
4656 l_error_text := substrb(hr_utility.get_message, 1, 240);
4657 --
4658 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4659 l_status_tbl(g_count) := 'F';
4660 l_message_tbl(g_count) := l_error_text;
4661 --
4662 when element_entry_exists then
4663 g_line_error := true;
4664 if l_general_message = false then
4665 g_count := g_count + 1;
4666 end if;
4667 --
4668 hr_utility.set_message(801,'HR_7455_PLK_ELE_ENTRY_EXISTS');
4669 l_error_text := substrb(hr_utility.get_message, 1, 240);
4670 --
4671 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4672 l_status_tbl(g_count) := 'F';
4673 l_message_tbl(g_count) := l_error_text;
4674 --
4675 when more_than_one_entry then
4676 g_line_error := true;
4677 if l_general_message = false then
4678 g_count := g_count + 1;
4679 end if;
4680 --
4681 hr_utility.set_message(801,'HR_7456_PLK_MORE_THAN_ONE_ENT');
4682 l_error_text := substrb(hr_utility.get_message, 1, 240);
4683 --
4684 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4685 l_status_tbl(g_count) := 'F';
4686 l_message_tbl(g_count) := l_error_text;
4687 --
4688 when future_changes_exist then
4689 g_line_error := true;
4690 if l_general_message = false then
4691 g_count := g_count + 1;
4692 end if;
4693 --
4694 hr_utility.set_message(801,'HR_7457_PLK_FUTURE_CHGS_EXIST');
4695 l_error_text := substrb(hr_utility.get_message, 1, 240);
4696 --
4697 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4698 l_status_tbl(g_count) := 'F';
4699 l_message_tbl(g_count) := l_error_text;
4700 --
4701 when element_entry_not_eligible then
4702 g_line_error := true;
4703 g_count := g_count + 1;
4704 --
4705 hr_utility.set_message(801,'HR_7459_PLK_NOT_ELGBLE_ELE_ENT');
4706 l_error_text := substrb(hr_utility.get_message, 1, 240);
4707 --
4708 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4709 l_status_tbl(g_count) := 'F';
4710 l_message_tbl(g_count) := l_error_text;
4711 --
4712 when non_updatable_creator_type then
4713 g_line_error := true;
4714 g_count := g_count + 1;
4715 --
4716 hr_utility.set_message(801,'HR_7014_ELE_ENTRY_CREATOR_UPD');
4717 hr_utility.set_message_token('CREATOR_MEANING', hr_general.decode_lookup('CREATOR_TYPE','SP'));
4718 l_error_text := substrb(hr_utility.get_message, 1, 240);
4719 --
4720 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4721 l_status_tbl(g_count) := 'F';
4722 l_message_tbl(g_count) := l_error_text;
4723 --
4724 when hr_utility.hr_error then
4725 g_line_error := true;
4726 if l_general_message = false then
4727 g_count := g_count + 1;
4728 end if;
4729 --
4730 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4731 l_status_tbl(g_count) := 'F';
4732 l_message_tbl(g_count) := substrb(hr_utility.get_message, 1, 240);
4733 --
4734 when inputs_not_required then
4735 g_line_error := true;
4736 g_count := g_count + 1;
4737 --
4738 hr_utility.set_message(801,'HR_7458_PLK_ENT_VALS_NOT_REQD');
4739 l_error_text := substrb(hr_utility.get_message, 1, 240);
4740 --
4741 l_line_id_tbl(g_count) := g_line_record.batch_line_id;
4742 l_status_tbl(g_count) := 'F';
4743 l_message_tbl(g_count) := l_error_text;
4744 --
4745 when line_error_occurred then
4746 null;
4747 end;
4748 --
4749 -- Close any cursors left open.
4750 --
4751 if csr_element_entries%ISOPEN then
4752 close csr_element_entries;
4753 end if;
4754 --
4755 end loop;
4756 --
4757 --
4758 g_line_id_tbl := l_line_id_tbl;
4759 g_status_tbl := l_status_tbl;
4760 g_message_tbl := l_message_tbl;
4761 --
4762 --
4763 -- Successfully validated line(s) may as a result have created
4764 -- element entrie(s) in the base tables, therefore the entries must be
4765 -- ROLLED BACK as the requested batch operation was 'VALIDATE'.
4766 --
4767 if p_process_mode = 'VALIDATE' then
4768 rollback to BL;
4769 end if;
4770 --
4771 -- Raise error if there is a failed line exists.
4772 if g_line_error then
4773 raise no_data_found;
4774 end if;
4775 --
4776 hr_utility.set_location('payplnk.validate_lines',500);
4777 --
4778 -- Close the header cursor if it is open.
4779 if csr_bl_header%ISOPEN then
4780 close csr_bl_header;
4781 end if;
4782 --
4783 --Set the global parameter to enable the triggers.
4784 payplnk.g_payplnk_call := false;
4785 --
4786 /* Do Not continue to transfer even if one line is errored */
4787 if (l_control_err = true or l_header_err = true or (l_chk_asg_lines = 'Y' and
4788 p_process_mode = 'TRANSFER')) then /* Bug 13814081 */
4789 g_line_error := true;
4790 return;
4791 end if;
4792 --
4793 exception
4794 when others then
4795 if csr_asg_lines%ISOPEN then
4796 close csr_asg_lines;
4797 end if;
4798 if csr_bl_header%ISOPEN then
4799 close csr_bl_header;
4800 end if;
4801 rollback to BL;
4802 --Set the global parameter to enable the triggers.
4803 payplnk.g_payplnk_call := false;
4804 hr_utility.set_message(800, 'HR_289719_BEE_LINE_ERROR');
4805 hr_utility.raise_error;
4806 -- raise;
4807 --
4808 /* Do Not continue to transfer even if one line is errored 13814081 */
4809 if (l_control_err = true or l_header_err = true or (l_chk_asg_lines = 'Y' and
4810 p_process_mode = 'TRANSFER')) then /* Bug 13814081 */
4811 g_line_error := true;
4812 return;
4813 end if;
4814
4815 end validate_lines;
4816 --
4817 -----------------------------------------------------------------------
4818 -- NAME --
4819 -- payplnk.purge_messages PRIVATE PROCEDURE --
4820 -- --
4821 -- DESCRIPTION --
4822 -- Deletes all messages associated with the batch from the --
4823 -- PAY_MESSAGE_LINES table. --
4824 -- This can either be for whole batch or non-transferred part --
4825 -- of the batch, where p_mode can be either Y or N. --
4826 -----------------------------------------------------------------------
4827 --
4828 procedure purge_messages
4829 (
4830 p_batch_id in number,
4831 p_mode in varchar2
4832 ) is
4833 --
4834 cursor pcl (p_batch_id number) is
4835 select batch_control_id
4836 from pay_batch_control_totals
4837 where batch_id = p_batch_id
4838 and (control_status <> 'T' or p_mode='Y');
4839 --
4840 cursor pbl (p_batch_id number) is
4841 select batch_line_id
4842 from pay_batch_lines
4843 where batch_id = p_batch_id
4844 and (batch_line_status <> 'T' or p_mode='Y');
4845 --
4846 begin
4847 --
4848 hr_utility.set_location('payplnk.purge_messages',5);
4849 --
4850 for pclrec in pcl(p_batch_id) loop
4851 delete from pay_message_lines
4852 where source_type = 'C'
4853 and source_id = pclrec.batch_control_id;
4854 end loop;
4855 --
4856 hr_utility.set_location('payplnk.purge_messages',10);
4857 --
4858 for pblrec in pbl(p_batch_id) loop
4859 delete from pay_message_lines
4860 where source_type = 'L'
4861 and source_id = pblrec.batch_line_id;
4862 end loop;
4863 --
4864 hr_utility.set_location('payplnk.purge_messages',20);
4865 --
4866 delete from pay_message_lines
4867 where source_type = 'H'
4868 and source_id = p_batch_id
4869 and (not exists
4870 (select null
4871 from pay_batch_lines pbl
4872 where pbl.batch_id = p_batch_id
4873 and pbl.batch_line_status = 'T')
4874 or p_mode='Y');
4875 --
4876 hr_utility.set_location('payplnk.purge_messages',25);
4877 --
4878 end purge_messages;
4879 --
4880 ----------------------------------------------------------------------
4881 -- NAME --
4882 -- payplnk.insert_element_entry PRIVATE PROCEDURE --
4883 -- --
4884 -- DESCRIPTION --
4885 -- Inserts an element entry into the PAY_ELEMENT_ENTRIES_F table --
4886 ----------------------------------------------------------------------
4887 --
4888 procedure insert_element_entry
4889 (
4890 p_link_id in number,
4891 l_line_record in out nocopy csr_all_lines%ROWTYPE,
4892 p_asg_act_id in number,
4893 p_creator_type in varchar2,
4894 p_absence_attendance_id in number,
4895 p_entry_values_count in number,
4896 p_passed_inp_tbl in hr_entry.number_table,
4897 p_passed_val_tbl in hr_entry.varchar2_table
4898 ) is
4899 --
4900 l_effective_end_date date := null;
4901 l_effective_start_date date := null;
4902 l_element_entry_id number := null;
4903 l_reason hr_lookups.lookup_code%TYPE := null;
4904 l_creator_id number;
4905 c_passed_inp_tbl hr_entry.number_table;
4906 c_passed_val_tbl hr_entry.varchar2_table;
4907 i binary_integer :=0;
4908 j number;
4909 --
4910 cursor csr_check_classification is
4911 select 'Y'
4912 from pay_element_types_f pet,
4913 pay_element_classifications pec
4914 where pet.element_type_id = l_line_record.element_type_id
4915 and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
4916 and pet.PROCESSING_TYPE = 'R'
4917 and pec.legislation_code is not null
4918 and pec.CLASSIFICATION_name like 'EXTERNAL_REPORTING%'
4919 and pec.legislation_code = 'GB';
4920 --
4921 l_dummy_chk varchar2(1);
4922 l_stime number(30);
4923 l_etime number(30);
4924 l_time_out boolean := FALSE;
4925 --
4926 begin
4927 --
4928 hr_utility.set_location('payplnk.insert_element_entry',5);
4929 --
4930 -- Bug 423237 - Convert passed in reason to lookup_code for api
4931 --
4932 IF l_line_record.reason IS NOT NULL THEN
4933 select hl.lookup_code
4934 into l_reason
4935 from hr_lookups hl
4936 where hl.lookup_type = 'ELE_ENTRY_REASON'
4937 and hl.meaning = l_line_record.reason;
4938 END IF;
4939 --
4940 -- If the element is an Abssnce Creator_type is 'A' and
4941 -- Creator_id is the absence_attendance_id.
4942 -- Otherwise creator_type is 'H' and the creator_id
4943 -- is the batch_id.
4944 --
4945 if (p_creator_type = 'A') then
4946 l_creator_id := p_absence_attendance_id;
4947 else
4948 l_creator_id := g_line_record.batch_id;
4949 end if;
4950 --
4951 j := 1;
4952 -- Overiding version of the bug 1002790 fixes.
4953 for i in 1..p_entry_values_count loop
4954 /** sbilling **/
4955 if p_passed_val_tbl.exists(i) and
4956 p_passed_val_tbl(i) is not null then
4957 c_passed_inp_tbl(j) := p_passed_inp_tbl(i);
4958 c_passed_val_tbl(j) := p_passed_val_tbl(i);
4959 j := j + 1;
4960 end if;
4961 end loop;
4962 --
4963 j := j - 1;
4964
4965 if ghr_utility.is_ghr = 'TRUE' then
4966 hr_utility.set_location('GHR Installed....insert_element_entry .. Pre...',5);
4967 ghr_session.set_session_var_for_core
4968 (p_effective_date => l_line_record.effective_date);
4969 end if;
4970
4971 -- This is to support the predefined duration of the element, especially for GB.
4972 open csr_check_classification;
4973 fetch csr_check_classification into l_dummy_chk;
4974 if (csr_check_classification%found and l_line_record.effective_start_date is not null) then
4975 l_effective_start_date := l_line_record.effective_start_date;
4976 else
4977 l_effective_start_date := l_line_record.effective_date;
4978 end if;
4979 close csr_check_classification;
4980
4981 -- Sets the start time.
4982 --
4983 if ( g_lock_max_wait > 0 ) then
4984 l_time_out := TRUE;
4985 select to_number(((to_char(sysdate, 'J') - 1 ) * 86400) +
4986 to_char(sysdate, 'SSSSS'))
4987 into l_stime
4988 from sys.dual;
4989 end if;
4990 --
4991 loop
4992 --
4993 begin
4994 --
4995 if (l_time_out) then
4996 savepoint INS_EE;
4997 end if;
4998 --
4999 hr_entry_api.insert_element_entry(
5000 p_effective_start_date => l_effective_start_date,
5001 p_effective_end_date => l_effective_end_date,
5002 p_element_entry_id => l_element_entry_id,
5003 p_assignment_id => l_line_record.assignment_id,
5004 p_element_link_id => p_link_id,
5005 p_creator_type => p_creator_type,
5006 p_creator_id => l_creator_id,
5007 p_entry_type => 'E',
5008 p_cost_allocation_keyflex_id =>
5009 l_line_record.cost_allocation_keyflex_id,
5010 p_reason => l_reason,
5011 --
5012 p_subpriority => l_line_record.subpriority,
5013 p_date_earned => l_line_record.date_earned,
5014 p_personal_payment_method_id => l_line_record.personal_payment_method_id,
5015 --
5016 p_attribute_category => l_line_record.attribute_category,
5017 p_attribute1 => l_line_record.attribute1,
5018 p_attribute2 => l_line_record.attribute2,
5019 p_attribute3 => l_line_record.attribute3,
5020 p_attribute4 => l_line_record.attribute4,
5021 p_attribute5 => l_line_record.attribute5,
5022 p_attribute6 => l_line_record.attribute6,
5023 p_attribute7 => l_line_record.attribute7,
5024 p_attribute8 => l_line_record.attribute8,
5025 p_attribute9 => l_line_record.attribute9,
5026 p_attribute10 =>l_line_record.attribute10,
5027 p_attribute11 =>l_line_record.attribute11,
5028 p_attribute12 =>l_line_record.attribute12,
5029 p_attribute13 =>l_line_record.attribute13,
5030 p_attribute14 =>l_line_record.attribute14,
5031 p_attribute15 =>l_line_record.attribute15,
5032 p_attribute16 =>l_line_record.attribute16,
5033 p_attribute17 =>l_line_record.attribute17,
5034 p_attribute18 =>l_line_record.attribute18,
5035 p_attribute19 =>l_line_record.attribute19,
5036 p_attribute20 =>l_line_record.attribute20,
5037 p_entry_information_category => l_line_record.entry_information_category,
5038 p_entry_information1 => l_line_record.entry_information1,
5039 p_entry_information2 => l_line_record.entry_information2,
5040 p_entry_information3 => l_line_record.entry_information3,
5041 p_entry_information4 => l_line_record.entry_information4,
5042 p_entry_information5 => l_line_record.entry_information5,
5043 p_entry_information6 => l_line_record.entry_information6,
5044 p_entry_information7 => l_line_record.entry_information7,
5045 p_entry_information8 => l_line_record.entry_information8,
5046 p_entry_information9 => l_line_record.entry_information9,
5047 p_entry_information10 => l_line_record.entry_information10,
5048 p_entry_information11 => l_line_record.entry_information11,
5049 p_entry_information12 => l_line_record.entry_information12,
5050 p_entry_information13 => l_line_record.entry_information13,
5051 p_entry_information14 => l_line_record.entry_information14,
5052 p_entry_information15 => l_line_record.entry_information15,
5053 p_entry_information16 => l_line_record.entry_information16,
5054 p_entry_information17 => l_line_record.entry_information17,
5055 p_entry_information18 => l_line_record.entry_information18,
5056 p_entry_information19 => l_line_record.entry_information19,
5057 p_entry_information20 => l_line_record.entry_information20,
5058 p_entry_information21 => l_line_record.entry_information21,
5059 p_entry_information22 => l_line_record.entry_information22,
5060 p_entry_information23 => l_line_record.entry_information23,
5061 p_entry_information24 => l_line_record.entry_information24,
5062 p_entry_information25 => l_line_record.entry_information25,
5063 p_entry_information26 => l_line_record.entry_information26,
5064 p_entry_information27 => l_line_record.entry_information27,
5065 p_entry_information28 => l_line_record.entry_information28,
5066 p_entry_information29 => l_line_record.entry_information29,
5067 p_entry_information30 => l_line_record.entry_information30,
5068 /** sbilling **/
5069 --p_num_entry_values => p_entry_values_count,
5070 p_num_entry_values => j,
5071 p_input_value_id_tbl => c_passed_inp_tbl,
5072 p_entry_value_tbl => c_passed_val_tbl);
5073 --
5074 -- Exit the loop.
5075 exit;
5076 --
5077 exception
5078 --
5079 when others then
5080 hr_message.provide_error;
5081 if (l_time_out and hr_message.last_message_name = 'HR_7165_OBJECT_LOCKED') then
5082 --
5083 select to_number(((to_char(sysdate, 'J') - 1 ) * 86400) +
5084 to_char(sysdate, 'SSSSS'))
5085 into l_etime
5086 from sys.dual;
5087 --
5088 if ( (l_etime - l_stime) >= g_lock_max_wait) then
5089 raise;
5090 end if;
5091 --
5092 rollback to INS_EE;
5093 dbms_lock.sleep(g_lock_interval);
5094 --
5095 else
5096 raise;
5097 end if;
5098 --
5099 --
5100 end;
5101 --
5102 end loop;
5103
5104 -- Set the origin of the entry as the batch and its assignment action.
5105 update pay_element_entries_f
5106 set source_id = p_asg_act_id
5107 where element_entry_id = l_element_entry_id;
5108 --
5109
5110 open csr_check_classification;
5111 fetch csr_check_classification into l_dummy_chk;
5112 if (csr_check_classification%found and l_line_record.effective_end_date is not null and l_element_entry_id is not null) then
5113 l_effective_end_date := l_line_record.effective_end_date;
5114 hr_entry_api.delete_element_entry(p_dt_delete_mode => 'DELETE',
5115 p_session_date => l_effective_end_date,
5116 p_element_entry_id =>l_element_entry_id);
5117 end if;
5118 close csr_check_classification;
5119
5120
5121 if ghr_utility.is_ghr = 'TRUE' then
5122 hr_utility.set_location('GHR Installed....insert_element_entry .. Post ',6);
5123 ghr_history_api.post_update_process;
5124 end if;
5125 --
5126 hr_utility.set_location('payplnk.insert_element_entry',10);
5127 --
5128 end insert_element_entry;
5129 --
5130 --
5131 ---------------------------------------------------------------------
5132 -- NAME --
5133 -- payplnk.update_element_entry PRIVATE PROCEDURE --
5134 -- --
5135 -- DESCRIPTION --
5136 -- Updates an existing element entry in the PAY_ELEMENT_ENTRIES_F --
5137 -- table. --
5138 ---------------------------------------------------------------------
5139 --
5140 procedure update_element_entry
5141 (
5142 p_update_mode in varchar2,
5143 p_element_entry_id in number,
5144 p_creator_type in varchar2,
5145 p_creator_id in number,
5146 p_allow_rollback in boolean,
5147 p_asg_act_id in number,
5148 l_line_record in csr_all_lines%ROWTYPE,
5149 p_entry_values_count in number,
5150 p_passed_inp_tbl in hr_entry.number_table,
5151 p_passed_val_tbl in hr_entry.varchar2_table
5152 ) is
5153 --
5154 l_reason hr_lookups.lookup_code%TYPE := null;
5155 --
5156 l_stime number(30);
5157 l_etime number(30);
5158 l_time_out boolean := FALSE;
5159 --
5160 begin
5161 --
5162 hr_utility.set_location('payplnk.update_element_entry',5);
5163 --
5164 -- Bug 423237 - Convert passed in reason to lookup_code for api
5165 --
5166 IF l_line_record.reason IS NOT NULL THEN
5167 select hl.lookup_code
5168 into l_reason
5169 from hr_lookups hl
5170 where hl.lookup_type = 'ELE_ENTRY_REASON'
5171 and hl.meaning = l_line_record.reason;
5172 END IF;
5173 --
5174 if ghr_utility.is_ghr = 'TRUE' then
5175 hr_utility.set_location('GHR Installed....update_element_entry .. Pre ',5);
5176 ghr_session.set_session_var_for_core
5177 (p_effective_date => l_line_record.effective_date);
5178 end if;
5179
5180 -- Sets the start time.
5181 --
5182 if ( g_lock_max_wait > 0 ) then
5183 l_time_out := TRUE;
5184 select to_number(((to_char(sysdate, 'J') - 1 ) * 86400) +
5185 to_char(sysdate, 'SSSSS'))
5186 into l_stime
5187 from sys.dual;
5188 end if;
5189 --
5190 loop
5191 --
5192 begin
5193 --
5194 if (l_time_out) then
5195 savepoint UPD_EE;
5196 end if;
5197 --
5198 hr_entry_api.update_element_entry(
5199 p_dt_update_mode => p_update_mode,
5200 p_session_date => l_line_record.effective_date,
5201 p_element_entry_id => p_element_entry_id,
5202 p_cost_allocation_keyflex_id =>
5203 l_line_record.cost_allocation_keyflex_id,
5204 p_reason => l_reason,
5205 --
5206 p_subpriority => l_line_record.subpriority,
5207 p_date_earned => l_line_record.date_earned,
5208 p_personal_payment_method_id => l_line_record.personal_payment_method_id,
5209 --
5210 p_creator_id => null,
5211 p_attribute_category => l_line_record.attribute_category,
5212 p_attribute1 => l_line_record.attribute1,
5213 p_attribute2 => l_line_record.attribute2,
5214 p_attribute3 => l_line_record.attribute3,
5215 p_attribute4 => l_line_record.attribute4,
5216 p_attribute5 => l_line_record.attribute5,
5217 p_attribute6 => l_line_record.attribute6,
5218 p_attribute7 => l_line_record.attribute7,
5219 p_attribute8 => l_line_record.attribute8,
5220 p_attribute9 => l_line_record.attribute9,
5221 p_attribute10 => l_line_record.attribute10,
5222 p_attribute11 => l_line_record.attribute11,
5223 p_attribute12 => l_line_record.attribute12,
5224 p_attribute13 => l_line_record.attribute13,
5225 p_attribute14 => l_line_record.attribute14,
5226 p_attribute15 => l_line_record.attribute15,
5227 p_attribute16 => l_line_record.attribute16,
5228 p_attribute17 => l_line_record.attribute17,
5229 p_attribute18 => l_line_record.attribute18,
5230 p_attribute19 => l_line_record.attribute19,
5231 p_attribute20 => l_line_record.attribute20,
5232 p_entry_information_category => l_line_record.entry_information_category,
5233 p_entry_information1 => l_line_record.entry_information1,
5234 p_entry_information2 => l_line_record.entry_information2,
5235 p_entry_information3 => l_line_record.entry_information3,
5236 p_entry_information4 => l_line_record.entry_information4,
5237 p_entry_information5 => l_line_record.entry_information5,
5238 p_entry_information6 => l_line_record.entry_information6,
5239 p_entry_information7 => l_line_record.entry_information7,
5240 p_entry_information8 => l_line_record.entry_information8,
5241 p_entry_information9 => l_line_record.entry_information9,
5242 p_entry_information10 => l_line_record.entry_information10,
5243 p_entry_information11 => l_line_record.entry_information11,
5244 p_entry_information12 => l_line_record.entry_information12,
5245 p_entry_information13 => l_line_record.entry_information13,
5246 p_entry_information14 => l_line_record.entry_information14,
5247 p_entry_information15 => l_line_record.entry_information15,
5248 p_entry_information16 => l_line_record.entry_information16,
5249 p_entry_information17 => l_line_record.entry_information17,
5250 p_entry_information18 => l_line_record.entry_information18,
5251 p_entry_information19 => l_line_record.entry_information19,
5252 p_entry_information20 => l_line_record.entry_information20,
5253 p_entry_information21 => l_line_record.entry_information21,
5254 p_entry_information22 => l_line_record.entry_information22,
5255 p_entry_information23 => l_line_record.entry_information23,
5256 p_entry_information24 => l_line_record.entry_information24,
5257 p_entry_information25 => l_line_record.entry_information25,
5258 p_entry_information26 => l_line_record.entry_information26,
5259 p_entry_information27 => l_line_record.entry_information27,
5260 p_entry_information28 => l_line_record.entry_information28,
5261 p_entry_information29 => l_line_record.entry_information29,
5262 p_entry_information30 => l_line_record.entry_information30,
5263 p_num_entry_values => p_entry_values_count,
5264 p_input_value_id_tbl => p_passed_inp_tbl,
5265 p_entry_value_tbl => p_passed_val_tbl);
5266 --
5267 -- Exit the loop.
5268 exit;
5269 --
5270 exception
5271 --
5272 when others then
5273 hr_message.provide_error;
5274 if (l_time_out and hr_message.last_message_name = 'HR_7165_OBJECT_LOCKED') then
5275 --
5276 select to_number(((to_char(sysdate, 'J') - 1 ) * 86400) +
5277 to_char(sysdate, 'SSSSS'))
5278 into l_etime
5279 from sys.dual;
5280 --
5281 if ( (l_etime - l_stime) >= g_lock_max_wait) then
5282 raise;
5283 end if;
5284 --
5285 rollback to UPD_EE;
5286 dbms_lock.sleep(g_lock_interval);
5287 --
5288 else
5289 raise;
5290 end if;
5291 --
5292 --
5293 end;
5294 --
5295 end loop;
5296
5297
5298 if ghr_utility.is_ghr = 'TRUE' then
5299 hr_utility.set_location('GHR Installed....update_element_entry .. Post ',5);
5300 ghr_history_api.post_update_process;
5301 end if;
5302
5303 -- For MIX rollback - for non absence must set creator_id to null after update
5304 -- for absence null batch_id on per_absence_attendances
5305 -- for this element
5306
5307 if (p_creator_type = 'A') then
5308 --
5309 hr_utility.set_location('payplnk.update_element_entry',10);
5310 --
5311 update per_absence_attendances
5312 set batch_id = null
5313 where absence_attendance_id = p_creator_id;
5314 else
5315 --
5316 hr_utility.set_location('payplnk.update_element_entry',15);
5317 --
5318 if p_allow_rollback then
5319 --Recurring element of creator type 'H' or 'F' with update and update insert change.
5320 --
5321 update pay_element_entries_f
5322 set creator_id = p_creator_id,
5323 creator_type = p_creator_type,
5324 source_id = p_asg_act_id
5325 where element_entry_id = p_element_entry_id
5326 and l_line_record.effective_date between effective_start_date
5327 and effective_end_date
5328 and creator_type in ('H','F');
5329 else
5330 --Non-recurring.
5331 --
5332 update pay_element_entries_f
5333 set creator_id = null,
5334 source_id = null
5335 where element_entry_id = p_element_entry_id
5336 and l_line_record.effective_date between effective_start_date
5337 and effective_end_date
5338 and creator_type ='H';
5339 end if;
5340 end if;
5341
5342 --
5343 hr_utility.set_location('payplnk.update_element_entry',20);
5344 --
5345 end update_element_entry;
5346 --
5347 end payplnk;