[Home] [Help]
PACKAGE BODY: APPS.HR_DU_UTILITY
Source
1 PACKAGE BODY hr_du_utility AS
2 /* $Header: perdutil.pkb 115.6 2002/11/28 17:05:05 apholt noship $ */
3
4 /*--------------------------- GLOBAL VARIABLES ----------------------------*/
5
6 -- message globals
7 -- start
8
9 g_debug_message_log VARCHAR2(50);
10 g_debug_message_indent NUMBER;
11 g_debug_message_indent_size NUMBER := 2;
12
13 -- message globals
14 -- end
15
16 /*----------------------------------------------------------------------------*/
17
18
19 -- 11i / 11.0 specific code
20 -- start
21
22 -- ------------------------- local_chr ------------------------
23 -- Description: In 11i fnd_global.local_CHR(i) will return the local
24 -- equivelent of the ASCII character. This does not exit pre 11i.
25 --
26 --
27 -- Input Parameters
28 -- p_char_code - character code to convert
29 --
30 --
31 -- Output Parameters
32 -- - converted character
33 --
34 -- ------------------------------------------------------------------------
35
36 FUNCTION local_chr(p_char_code IN NUMBER) RETURN VARCHAR2 IS
37
38 l_char VARCHAR2(30);
39
40 --
41 BEGIN
42 --
43
44 -- uncomment appropriate version
45
46 -- 11i version
47 l_char := fnd_global.local_chr(p_char_code);
48
49 -- pre 11i version
50 -- l_char := CHR(p_char_code);
51
52
53 RETURN(l_char);
54
55 -- error handling
56 EXCEPTION
57 WHEN OTHERS THEN
58 error(SQLCODE,'hr_du_utility.local_chr','(none)','R');
59 RAISE;
60
61 --
62 END local_chr;
63 --
64
65 -- ------------------------- dynamic_sql ------------------------
66 -- Description: Perform dynamic SQL which returns no value
67 --
68 -- 11i - use EXECUTE IMMEDIATE
69 --
70 -- pre 11i - use dmb_sql package
71 --
72 -- Input Parameters
73 -- p_string - string to execute
74 --
75 --
76 -- Output Parameters
77 -- - none
78 --
79 -- ------------------------------------------------------------------------
80
81 PROCEDURE dynamic_sql(p_string IN VARCHAR2) IS
82
83 -- pre 11i version
84 /*
85 l_cursor_handle INT;
86 l_rows_processed INT;
87 */
88
89 --
90 BEGIN
91 --
92 message('PARA','(p_string - ' || p_string || ')', 10);
93
94 -- uncomment appropriate version
95
96 -- 11i version
97 EXECUTE IMMEDIATE p_string;
98
99 -- pre 11i version
100 /*
101 l_cursor_handle := dbms_sql.open_cursor;
102 dbms_sql.parse(l_cursor_handle, p_string, dbms_sql.native);
103 l_rows_processed := dbms_sql.execute(l_cursor_handle);
104 dbms_sql.close_cursor(l_cursor_handle);
105 */
106
107 message('INFO','Executed dynamic sql', 15);
108 message('SUMM','Executed dynamic sql', 20);
109
110 -- error handling
111 EXCEPTION
112 WHEN OTHERS THEN
113 error(SQLCODE,'hr_du_utility.dynamic_sql','(none)','R');
114 RAISE;
115
116 --
117 END dynamic_sql;
118 --
119
120 -- ------------------------- dynamic_sql_num ------------------------
121 -- Description: Perform dynamic SQL which returns a number
122 --
123 -- 11i - use EXECUTE IMMEDIATE
124 --
125 -- pre 11i - use dmb_sql package
126 --
127 --
128 -- Input Parameters
129 -- p_string - string to execute
130 -- p_return_value - variable for return value
131 --
132 --
133 -- ------------------------------------------------------------------------
134
135 PROCEDURE dynamic_sql_num(p_string IN VARCHAR2,
136 p_return_value IN OUT NOCOPY NUMBER) IS
137
138
139 -- pre 11i version
140 /*
141 l_cursor_handle INT;
142 l_rows_processed INT;
143 */
144
145 --
146 BEGIN
147 --
148
149 message('PARA','(p_string - ' || p_string ||
150 ')(p_return_value - ' || p_return_value || ')', 10);
151
152
153 -- uncomment appropriate version
154
155 -- 11i version
156 EXECUTE IMMEDIATE p_string INTO p_return_value;
157
158 -- pre 11i version
159 /*
160 l_cursor_handle := dbms_sql.open_cursor;
161 dbms_sql.parse(l_cursor_handle, p_string, DBMS_SQL.v7);
162 dbms_sql.define_column(l_cursor_handle, 1, p_return_value);
163 l_rows_processed := dbms_sql.execute(l_cursor_handle);
164
165 if dbms_sql.fetch_rows(l_cursor_handle) > 0 then
166 dbms_sql.column_value(l_cursor_handle, 1, p_return_value);
167 end if;
168
169 dbms_sql.close_cursor(l_cursor_handle);
170 */
171
172
173 message('INFO','p_return_value - ' || p_return_value, 12);
174
175
176 message('INFO','Executed dynamic sql', 15);
177 message('SUMM','Executed dynamic sql', 20);
178 message('PARA','(none)', 30);
179
180 -- error handling
181 EXCEPTION
182 WHEN OTHERS THEN
183 error(SQLCODE,'hr_du_utility.dynamic_sql_num','(none)','R');
184 RAISE;
185
186 --
187 END dynamic_sql_num;
188 --
189
190
191 -- ----------------------- DYNAMIC_SQL_NUM_USER_KEY -----------------------
192 -- Description: Perform dynamic SQL which returns a number. If the SQL
193 -- statement fails then there isn't a valid id that exists and a cursor
194 -- is run to give an error message
195 --
196 -- 11i - use EXECUTE IMMEDIATE
197 --
198 -- pre 11i - use dmb_sql package
199 --
200 --
201 -- Input Parameters
202 -- p_string - string to execute
203 -- p_api_module_id - Identifies the API
204 -- p_return_value - variable for return value
205 -- p_column_id - Identifies the column in the flat file
206 --
207 --
208 -- ------------------------------------------------------------------------
209
210 PROCEDURE dynamic_sql_num_user_key(
211 p_string IN VARCHAR2,
212 p_api_module_id IN NUMBER,
213 p_column_id IN NUMBER,
214 p_return_value IN OUT NOCOPY NUMBER) IS
215
216 CURSOR csr_module_name IS
217 SELECT module_name
218 FROM hr_api_modules
219 WHERE api_module_id = p_api_module_id;
220
221
222 l_api_module_name VARCHAR2(200);
223
224 -- pre 11i version
225 /*
226 l_cursor_handle INT;
227 l_rows_processed INT;
228 */
229
230 --
231 BEGIN
232 --
233
234 message('ROUT','(p_string - ' || p_string ||
235 ')(p_return_value - ' || p_return_value || ')', 10);
236
237
238 -- uncomment appropriate version
239
240 -- 11i version
241 EXECUTE IMMEDIATE p_string INTO p_return_value;
242
243 -- pre 11i version
244 /*
245 l_cursor_handle := dbms_sql.open_cursor;
246 dbms_sql.parse(l_cursor_handle, p_string, DBMS_SQL.v7);
247 dbms_sql.define_column(l_cursor_handle, 1, p_return_value);
248 l_rows_processed := dbms_sql.execute(l_cursor_handle);
249
250 if dbms_sql.fetch_rows(l_cursor_handle) > 0 then
251 dbms_sql.column_value(l_cursor_handle, 1, p_return_value);
252 end if;
253
254 dbms_sql.close_cursor(l_cursor_handle);
255 */
256
257
258 message('INFO','p_return_value - ' || p_return_value, 12);
259
260
261 message('INFO','Executed dynamic sql', 15);
262 message('SUMM','Executed dynamic sql', 20);
263 message('PARA','(none)', 30);
264
265 -- error handling
266 EXCEPTION
267 WHEN OTHERS THEN
268 --
269 OPEN csr_module_name;
270 --
271 FETCH csr_module_name INTO l_api_module_name;
272 --
273 CLOSE csr_module_name;
274 --
275 error(SQLCODE,'hr_du_utility.dynamic_sql_num_user_key',' Unable to fine ID '
276 || p_column_id || ' in API ' || l_api_module_name || '. Referencing '
277 || 'column in other file has this invalid reference. ' ,'R');
278 RAISE;
279
280 --
281 END DYNAMIC_SQL_NUM_USER_KEY;
282 --
283
284 -- ------------------------- dynamic_sql_str ------------------------
285 -- Description: Perform dynamic SQL which returns a string
286 --
287 -- 11i - use EXECUTE IMMEDIATE
288 --
289 -- pre 11i - use dmb_sql package
290 --
291 --
292 -- Input Parameters
293 -- p_string - string to execute
294 -- p_return_value - variable for return value
295 -- p_string_length - max size of return value
296 --
297 -- ------------------------------------------------------------------------
298
299 PROCEDURE dynamic_sql_str(p_string IN VARCHAR2,
300 p_return_value IN OUT NOCOPY VARCHAR2,
301 p_string_length IN NUMBER) IS
302
303
304 -- pre 11i version
305 /*
306 l_cursor_handle INT;
307 l_rows_processed INT;
308 */
309
310 --
311 BEGIN
312 --
313
314 message('PARA','(p_string - ' || p_string ||
315 ')(p_return_value - ' || p_return_value ||
316 ')(p_string_length - ' || p_string_length || ')', 10);
317
318
319 -- uncomment appropriate version
320
321 -- 11i version
322 EXECUTE IMMEDIATE p_string INTO p_return_value;
323
324 -- pre 11i version
325 /*
326 l_cursor_handle := dbms_sql.open_cursor;
327 dbms_sql.parse(l_cursor_handle, p_string, DBMS_SQL.v7);
328 dbms_sql.define_column(l_cursor_handle, 1, p_return_value, p_string_length);
329 l_rows_processed := dbms_sql.execute(l_cursor_handle);
330
331 if dbms_sql.fetch_rows(l_cursor_handle) > 0 then
332 dbms_sql.column_value(l_cursor_handle, 1, p_return_value);
333 end if;
334
335 dbms_sql.close_cursor(l_cursor_handle);
336
337 */
338
339 message('INFO','p_return_value - ' || p_return_value, 12);
340
341 message('INFO','Executed dynamic sql', 15);
342 message('SUMM','Executed dynamic sql', 20);
343 message('PARA','(none)', 30);
344
345
346 -- error handling
347 EXCEPTION
348 WHEN OTHERS THEN
349 error(SQLCODE,'hr_du_utility.dynamic_sql_str','(none)','R');
350 RAISE;
351
352 --
353 END dynamic_sql_str;
354 --
355
356
357
358 -- 11i / 11.0 specific code
359 -- end
360
361
362
363
364 --
365 PROCEDURE error (p_sqlcode IN NUMBER, p_procedure IN VARCHAR2,
366 p_extra IN VARCHAR2, p_rollback IN VARCHAR2 DEFAULT 'R') IS
367 --
368 --
369 BEGIN
370 --
371
372 message('ROUT','entry:hr_du_utility.error', 5);
373 message('PARA','(p_sqlcode - ' || p_sqlcode ||
374 ')(p_procedure - ' || p_procedure || ')', 10);
375
376 message('FAIL',p_sqlcode || ':' || SQLERRM(p_sqlcode) || ':'
377 || p_extra, 15);
378
379 IF (p_rollback = 'R') THEN
380 ROLLBACK;
381 END IF;
382 IF (p_rollback = 'C') THEN
383 COMMIT;
384 END IF;
385
386 message('INFO','Error Handler - ' || p_procedure, 20);
387 message('SUMM','Error Handler - ' || p_procedure, 25);
388 message('ROUT','exit:hr_du_utility.error', 30);
389 message('PARA','(none)', 35);
390
391 -- error handling
392 EXCEPTION
393 WHEN OTHERS THEN
394 error(SQLCODE,'hr_du_utility.error','(none)','R');
395 RAISE;
396
397
398 --
399 END error;
400 --
401
402 -- error procedures
403 -- end
404
405 /*-------------------------------------------------------------------------------------------------------*/
406
407 PROCEDURE message_init IS
408 --
409
410 CURSOR csr_c2 IS
411 SELECT parameter_value
412 FROM pay_action_parameters
413 WHERE parameter_name = 'HR_DU_DEBUG_LOG';
414 --
415 BEGIN
416 --
417
418 -- read values from pay_action_parameters
419
420 OPEN csr_c2;
421 LOOP
422 FETCH csr_c2 INTO g_debug_message_log;
423 EXIT WHEN csr_c2%NOTFOUND;
424 END LOOP;
425 CLOSE csr_c2;
426
427 -- ensure that summary and fail settings are set
428
429 IF ((INSTR(g_debug_message_log, 'SUMM') IS NULL) OR
430 (INSTR(g_debug_message_log, 'SUMM') = 0)) THEN
431 g_debug_message_log := g_debug_message_log || ':SUMM';
432 END IF;
433
434 IF ((INSTR(g_debug_message_log, 'FAIL') IS NULL) OR
435 (INSTR(g_debug_message_log, 'FAIL') = 0)) THEN
436 g_debug_message_log := g_debug_message_log || ':FAIL';
437 END IF;
438
439 -- start the indenting to zero indentation
440 g_debug_message_indent := 0;
441
442 -- error handling
443 EXCEPTION
444 WHEN OTHERS THEN
445 error(SQLCODE,'hr_du_utility.message_init','(none)','R');
446 RAISE;
447
448 --
449 END message_init;
450 --
451
452
453
454 -- ------------------------- message ------------------------
455 -- Description: Logs the message to the log file and / or the
456 -- pipe for the options that have been configured by calling message_init.
457 --
458 --
459 -- Input Parameters
460 -- p_type - message type
461 --
462 -- p_message - text of message
463 --
464 -- p_position - position value for piped messages
465 --
466 --
467 -- Output Parameters
468 --
469 --
470 -- ------------------------------------------------------------------------
471
472 --
473 PROCEDURE message (p_type IN VARCHAR2, p_message IN VARCHAR2,
474 p_position IN NUMBER) IS
475 --
476
477 l_header VARCHAR2(30);
478 l_message VARCHAR2(32767);
479
480 --
481 BEGIN
482 --
483
484 l_message := p_message;
485
486 l_header := p_type || ':' || TO_CHAR(sysdate,'HH24MISS');
487 -- hr_utility.trace( l_header || ':-:' || ' ' || l_message);
488
489
490
491 IF (INSTR(g_debug_message_log, p_type) <> 0) THEN
492 l_message := p_message;
493 IF (p_type <> 'ROUT') THEN
494 l_message := ' ' || l_message;
495 END IF;
496
500 g_debug_message_indent := g_debug_message_indent -
497 -- for ROUT entry messages change indent
498 -- decrease for exit messages
499 IF (p_type = 'ROUT') AND (substr(p_message,1,5) = 'exit:') THEN
501 g_debug_message_indent_size;
502 END IF;
503
504
505 -- indent all messages to show nesting of functions
506 l_message := rpad(' ', g_debug_message_indent) || l_message;
507
508 -- for ROUT entry messages change indent
509 -- increase for entry messages
510 IF (p_type = 'ROUT') AND (substr(p_message,1,6) = 'entry:') THEN
511 g_debug_message_indent := g_debug_message_indent +
512 g_debug_message_indent_size;
513 END IF;
514
515 -- build header
516 l_header := p_type || ':' || TO_CHAR(sysdate,'HH24MISS');
517
518 FND_FILE.PUT_LINE(FND_FILE.LOG, l_header || ':-:' || l_message);
519 END IF;
520
521 -- error handling
522 EXCEPTION
523 WHEN OTHERS THEN
524 error(SQLCODE,'hr_du_utility.message','(none)','R');
525 RAISE;
526
527 --
528 END message;
529 --
530 -- message procedures
531 -- end
532
533
534
535 -- ------------------------- get_uploads_status ------------------------
536 -- Description: Reads the status of the passed phase from the hr_du_uploads
537 -- table.
538 --
539 -- Input Parameters
540 -- p_upload_id - upload id of current uploads
541 --
542 -- Output Parameters
543 -- <none>
544 --
545 -- Return Value
546 -- status of phase
547 --
548 -- ------------------------------------------------------------------------
549 --
550 FUNCTION get_uploads_status(p_upload_id IN NUMBER)
551 RETURN VARCHAR2 IS
552 --
553
554 l_phase_status VARCHAR2(30);
555
556 CURSOR csr_status IS
557 SELECT status
558 FROM hr_du_uploads
559 WHERE (upload_id = p_upload_id);
560
561 --
562 BEGIN
563 --
564
565 message('ROUT','entry:hr_du_utility.get_uploads_status', 5);
566 message('PARA','(p_upload_id - ' || p_upload_id || ')', 10);
567
568 OPEN csr_status;
569 LOOP
570 FETCH csr_status INTO l_phase_status;
571 EXIT when csr_status%NOTFOUND;
572 END LOOP;
573 CLOSE csr_status;
574
575 -- use a ? to represent a null value being returned
576 l_phase_status := NVL(l_phase_status, '?');
577
578
579 message('INFO','Find Phase Status', 15);
580 message('SUMM','Find Phase Status', 20);
581 message('ROUT','exit:hr_du_utility.get_phase_status', 25);
582 message('PARA','(l_phase_status - ' || l_phase_status || ')', 30);
583
584 RETURN(l_phase_status);
585
586 -- error handling
587 EXCEPTION
588 WHEN OTHERS THEN
589 error(SQLCODE,'hr_du_utility.get_uploads_status','(none)','R');
590 RAISE;
591
592 --
593 END get_uploads_status;
594 --
595
596
597 -- ------------------------- get_upload_headers_status ------------------------
598 -- Description: Reads the status of the passed phase from the hr_du_upload_headers
599 -- table.
600 --
601 --
602 -- Input Parameters
603 -- p_upload_header_id - upload_headers_id of current upload header
604 --
605 --
606 -- Output Parameters
607 -- <none>
608 --
609 --
610 -- Return Value
611 -- status of phase
612 --
613 --
614 -- ------------------------------------------------------------------------
615
616 --
617 FUNCTION get_upload_headers_status(p_upload_header_id IN NUMBER)
618 RETURN VARCHAR2 IS
619 --
620
621 l_phase_status VARCHAR2(30);
622
623 CURSOR csr_status IS
624 SELECT status
625 FROM hr_du_upload_headers
626 WHERE (upload_header_id = p_upload_header_id);
627
628 --
629 BEGIN
630 --
631
632 message('ROUT','entry:hr_du_utility.get_upload_headers_status', 5);
633 message('PARA','(p_upload_header_id - ' || p_upload_header_id || ')', 10);
634
635 OPEN csr_status;
636 LOOP
637 FETCH csr_status INTO l_phase_status;
638 EXIT when csr_status%NOTFOUND;
639 END LOOP;
640 CLOSE csr_status;
641
642 -- use a ? to represent a null value being returned
643 l_phase_status := NVL(l_phase_status, '?');
644
645
646 message('INFO','Find Phase Status', 15);
647 message('SUMM','Find Phase Status', 20);
648 message('ROUT','exit:hr_du_utility.get_upload_headers_status', 25);
649 message('PARA','(l_phase_status - ' || l_phase_status || ')', 30);
650
651 RETURN(l_phase_status);
652
653 -- error handling
654 EXCEPTION
655 WHEN OTHERS THEN
656 error(SQLCODE,'hr_du_utility.get_upload_headers_status','(none)','R');
657 RAISE;
658
659 --
660 END get_upload_headers_status;
661
662
663 -- ------------------------- get_upload_lines_status ------------------------
664 -- Description: Reads the status of the passed phase from the hr_du_upload_lines
665 -- table.
666 --
670 --
667 --
668 -- Input Parameters
669 -- p_upload_lines_id - upload_line_id of current upload_line
671 -- Output Parameters
672 -- <none>
673 --
674 -- Return Value
675 -- status of phase
676 --
677 -- ------------------------------------------------------------------------
678
679 --
680 FUNCTION get_upload_lines_status(p_upload_lines_id IN NUMBER)
681 RETURN VARCHAR2 IS
682 --
683
684 l_phase_status VARCHAR2(30);
685
686 CURSOR csr_status IS
687 SELECT status
688 FROM hr_du_upload_lines
689 WHERE (upload_line_id = p_upload_lines_id);
690
691 --
692 BEGIN
693 --
694
695 message('ROUT','entry:hr_du_utility.get_upload_lines_status', 5);
696 message('PARA','(p_upload_lines_id - ' || p_upload_lines_id || ')', 10);
697
698 OPEN csr_status;
699 LOOP
700 FETCH csr_status INTO l_phase_status;
701 EXIT when csr_status%NOTFOUND;
702 END LOOP;
703 CLOSE csr_status;
704
705 -- use a ? to represent a null value being returned
706 l_phase_status := NVL(l_phase_status, '?');
707
708
709 message('INFO','Find Phase Status', 15);
710 message('SUMM','Find Phase Status', 20);
711 message('ROUT','exit:hr_du_utility.get_upload_lines_status', 25);
712 message('PARA','(l_phase_status - ' || l_phase_status || ')', 30);
713
714 RETURN(l_phase_status);
715
716 -- error handling
717 EXCEPTION
718 WHEN OTHERS THEN
719 error(SQLCODE,'hr_du_utility.get_upload_lines_status','(none)','R');
720 RAISE;
721
722 --
723 END get_upload_lines_status;
724
725
726 -- update status procedures
727 -- start
728
729 -- ------------------------- update_uploads ------------------------
730 -- Description: Updates the status of the uploads in the hr_du_uploads
731 -- table. If the status is to be set to C then all child entries in
732 -- hr_du_headers are checked to ensure that they have completed.
733 --
734 -- Input Parameters
735 -- p_new_status - new status code
736 --
737 -- p_id - uploads id
738 --
739 -- Output Parameters
740 -- <none>
741 --
742 -- ------------------------------------------------------------------------
743
744 --
745 PROCEDURE update_uploads (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
746 --
747 -- table is hr_du_uploads
748 -- parent of hr_du_upload_headers
749 -- child of (none)
750
751 l_complete VARCHAR2(30);
752
753 -- search child table for all complete
754 CURSOR csr_child_table_complete IS
755 SELECT status
756 FROM hr_du_upload_headers
757 WHERE ((upload_id = p_id)
758 AND (status <> 'C'));
759
760 --
761 BEGIN
762 --
763 message('ROUT','entry:hr_du_utility.update_uploads', 5);
764 message('PARA','(p_new_status - ' || p_new_status ||
765 ')(p_id - ' || p_id || ')', 10);
766
767 -- non-complete
768 IF (p_new_status IN('S', 'NS', 'E')) THEN
769 -- update the status for this row
770 UPDATE hr_du_uploads
771 SET status = p_new_status
772 WHERE upload_id = p_id;
773 COMMIT;
774 END IF;
775
776 -- complete
777 IF (p_new_status = 'C') THEN
778 -- check if really complete
779 -- are any child rows not complete?
780 OPEN csr_child_table_complete;
781 FETCH csr_child_table_complete INTO l_complete;
782
783 IF (csr_child_table_complete%NOTFOUND) THEN
784 -- update the status for this row since no child rows
785 -- are incomplete
786 UPDATE hr_du_uploads
787 SET status = p_new_status
788 WHERE upload_id = p_id;
789 COMMIT;
790 END IF;
791 CLOSE csr_child_table_complete;
792 END IF;
793
794 message('INFO','Update status - update_uploads', 15);
795 message('SUMM','Update status - update_uploads', 20);
796 message('ROUT','exit:hr_du_utility.update_uploads', 25);
797 message('PARA','(none)', 30);
798
799 -- error handling
800 EXCEPTION
801 WHEN OTHERS THEN
802 error(SQLCODE,'hr_du_utility.update_uploads','(none)','R');
803 RAISE;
804
805 --
806 END update_uploads;
807 --
808
809
810 -- ------------------------- update_upload_lines ----------------------
811 -- Description: Updates the status of the upload lines in the
812 -- hr_du_upload_lines table. If the status is to be set to C or E then
813 -- the update status is cascaded up to the parent phase.
814 --
815 --
816 -- Input Parameters
817 -- p_new_status - new status code
818 --
819 -- p_id - upload_line_id
820 --
821 -- Output Parameters
822 -- <none>
823 --
824 -- ------------------------------------------------------------------------
825
826 --
827 PROCEDURE update_upload_lines (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
828 --
829 -- table is hr_du_upload_lines
830 -- parent of n/a
831 -- child of hr_du_upload_headers
832
833 l_parent_table_id NUMBER(9);
834
835 -- find parent table id
839 WHERE upload_line_id = p_id;
836 CURSOR csr_parent_id IS
837 SELECT upload_header_id
838 FROM hr_du_upload_lines
840
841 --
842 BEGIN
843 --
844
845 message('ROUT','entry:hr_du_utility.update_upload_lines', 5);
846 message('PARA','(p_new_status - ' || p_new_status ||
847 ')(p_id - ' || p_id || ')', 10);
848
849 -- update the status for this row
850 UPDATE hr_du_upload_lines
851 SET status = p_new_status
852 WHERE upload_line_id = p_id;
853 COMMIT;
854
855 -- update parent?
856 IF (p_new_status IN('C', 'E')) THEN
857 OPEN csr_parent_id;
858 FETCH csr_parent_id INTO l_parent_table_id;
859 CLOSE csr_parent_id;
860 update_upload_headers(p_new_status,l_parent_table_id);
861 END IF;
862
863 message('INFO','Update status - update_upload_lines', 15);
864 message('SUMM','Update status - update_upload_lines', 20);
865 message('ROUT','exit:hr_du_utility.update_upload_lines', 25);
866 message('PARA','(none)', 30);
867
868 -- error handling
869 EXCEPTION
870 WHEN OTHERS THEN
871 error(SQLCODE,'hr_du_utility.update_upload_lines','(none)','R');
872 RAISE;
873
874 --
875 END update_upload_lines;
876 --
877
878 -- ------------------------- update_upload_headers ----------------------
879 -- Description: Updates the status of the upload header in the
880 -- hr_du_upload_headers table. If the status is to be set to C or E then
881 -- the update status is cascaded up to the parent phase. For a C,
882 -- the status of all the child rows in the hr_du_upload_lines are
883 -- checked.
884 --
885 --
886 -- Input Parameters
887 -- p_new_status - new status code
888 --
889 -- p_id - upload header id
890 --
891 -- Output Parameters
892 -- <none>
893 --
894 -- ------------------------------------------------------------------------
895
896 --
897 PROCEDURE update_upload_headers (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
898 --
899 -- table is hr_du_upload_headers
900 -- parent of hr_du_upload_lines
901 -- child of hr_du_uploads
902
903 l_parent_table_id NUMBER(9);
904 l_complete VARCHAR2(30);
905 l_new_status VARCHAR2(30);
906
907 -- search child table for all complete
908 CURSOR csr_child_table_complete IS
909 SELECT status
910 FROM hr_du_upload_lines
911 WHERE ((upload_header_id = p_id)
912 AND (status <> 'C'));
913
914 -- find parent table id
915 CURSOR csr_parent_id IS
916 SELECT upload_id
917 FROM hr_du_upload_headers
918 WHERE upload_header_id = p_id;
919
920 --
921 BEGIN
922 --
923
924 message('ROUT','entry:hr_du_utility.update_upload_headers', 5);
925 message('PARA','(p_new_status - ' || p_new_status ||
926 ')(p_id - ' || p_id || ')', 10);
927
928 l_new_status := p_new_status;
929
930 -- non-complete
931 IF (l_new_status IN('S', 'NS', 'E')) THEN
932 -- update the status for this row
933 UPDATE hr_du_upload_headers
934 SET status = l_new_status
935 WHERE upload_header_id = p_id;
936 COMMIT;
937 END IF;
938
939 -- complete
940 IF (l_new_status = 'C') THEN
941 -- check if really complete
942 -- are any child rows not complete?
943 OPEN csr_child_table_complete;
944 FETCH csr_child_table_complete INTO l_complete;
945
946 IF (csr_child_table_complete%NOTFOUND) THEN
947 -- update the status for this row since no child rows
948 -- are incomplete
949 UPDATE hr_du_upload_headers
950 SET status = l_new_status
951 WHERE upload_header_id = p_id;
952 COMMIT;
953 ELSE
954 -- unset status to preven cascade
955 l_new_status := 'c';
956 END IF;
957 CLOSE csr_child_table_complete;
958 END IF;
959
960 -- update parent?
961 IF (l_new_status IN('C', 'E')) THEN
962 OPEN csr_parent_id;
963 FETCH csr_parent_id INTO l_parent_table_id;
964 CLOSE csr_parent_id;
965 update_uploads(l_new_status,l_parent_table_id);
966 END IF;
967
968
969 message('INFO','Update status - update_upload_headers', 15);
970 message('SUMM','Update status - update_upload_headers', 20);
971 message('ROUT','exit:hr_dm_utility.update_upload_headers', 25);
972 message('PARA','(none)', 30);
973
974 -- error handling
975 EXCEPTION
976 WHEN OTHERS THEN
977 error(SQLCODE,'hr_dm_utility.update_upload_headers','(none)','R');
978 RAISE;
979
980 --
981 END update_upload_headers;
982 --
983
984 -- update status procedures
985 -- end
986
987
988
989 -- ------------------------- Return_Spreadsheet_row ------------------------
990 -- Description: Takes in a number and returns the corresponding row letters
991 -- to point at the correct row cell in the spreadsheet
992 --
993 -- Input Parameters
994 -- p_upload_header_id - upload_headers_id of current upload header
995 --
996 -- Return Value
997 -- status of phase
998 --
999 --
1000 -- ------------------------------------------------------------------------
1001
1002 --
1003 FUNCTION Return_Spreadsheet_row(p_row_number IN NUMBER) RETURN VARCHAR2
1004 IS
1005 --
1006
1007 l_ASCII_1 VARCHAR2(10) :=null;
1008 l_ASCII_2 VARCHAR2(10) :=null;
1009 l_return VARCHAR2(10) :=null;
1010 l_divide NUMBER;
1011 l_mod NUMBER;
1012
1013 BEGIN
1014 --
1015 l_divide := TRUNC(p_row_number / 26);
1016 l_mod := p_row_number MOD 26;
1017
1018 --Statement catches the boundary values
1019 IF l_mod = 0 AND l_divide > 0 THEN
1020 l_mod := l_mod + 26;
1021 l_divide := l_divide - 1;
1022 END IF;
1023
1024 --Statement builds up the string to be glued together
1025 IF l_divide = 0 THEN
1026 l_ASCII_1 := ' ';
1027 ELSE
1028 l_divide := l_divide + 64;
1029 l_ASCII_1 := local_CHR(l_divide);
1030 END IF;
1031
1032 --
1033 IF l_mod = 0 THEN
1034 l_ASCII_2 := ' ';
1035 ELSE
1036 l_mod := l_mod + 64;
1037 l_ASCII_2 := local_CHR(l_mod);
1038 END IF;
1039
1040 --
1041 l_return := l_ASCII_1 || l_ASCII_2;
1042 RETURN l_return;
1043
1044
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047 hr_du_utility.error(SQLCODE, 'hr_du_utility.return_spreadsheet_row','(none)', 'R');
1048 RAISE;
1049 --
1050
1051 END Return_Spreadsheet_row;
1052
1053
1054 -- --------------------------- chunk_size ---------------------------------
1055 -- Description: Finds the chunk size to use for the various phases
1056 -- to use by looking at pay_action_parameters which is striped by business
1057 -- group id.
1058 --
1059 -- Return Value
1060 -- chunk_size
1061 --
1062 --------------------------------------------------------------------------
1063 FUNCTION chunk_size RETURN NUMBER IS
1064 --
1065
1066 l_chunk_size NUMBER;
1067
1068 CURSOR csr_chunk_size IS
1069 SELECT PARAMETER_VALUE
1070 FROM pay_action_parameters
1071 WHERE PARAMETER_NAME = 'CHUNK_SIZE';
1072
1073 --
1074 BEGIN
1075 --
1076 message('ROUT','entry:hr_du_utility.chunk_size', 5);
1077
1078 OPEN csr_chunk_size;
1079 FETCH csr_chunk_size INTO l_chunk_size;
1080 IF csr_chunk_size%NOTFOUND THEN
1081 l_chunk_size := 10;
1082 END IF;
1083 CLOSE csr_chunk_size;
1084
1085 message('INFO','Found chunk size', 15);
1086 message('SUMM','Found chunk size', 20);
1087 message('ROUT','exit:hr_dm_utility.chunk_size', 25);
1088 message('PARA','(l_chunk_size - ' || l_chunk_size || ')', 30);
1089
1090 RETURN(l_chunk_size);
1091
1092 -- error handling
1093 EXCEPTION
1094 WHEN OTHERS THEN
1095 error(SQLCODE,'hr_du_utility.chunk_size','(none)','R');
1096 RAISE;
1097
1098 --
1099 END CHUNK_SIZE;
1100
1101 end hr_du_utility;