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