[Home] [Help]
PACKAGE BODY: APPS.PAY_CORE_FILES
Source
1 package body pay_core_files as
2 /* $Header: pycofile.pkb 120.16 2011/01/04 05:22:30 phattarg noship $ */
3 --
4 -- Setup Datatypes
5 --
6 type t_file_list_rec is record
7 (file_detail_id pay_file_details.file_detail_id%type,
8 file_type pay_file_details.file_type%type,
9 int_file_name varchar2(30),
10 position number,
11 nxtptr number,
12 prvptr number,
13 sequence number,
14 file_locator blob
15 );
16
17 type t_file_list_tab is table of t_file_list_rec index by binary_integer;
18
19 g_head_file_ptr number;
20 --
21 -- Setup Globals
22 --
23 g_file_list t_file_list_tab;
24 --
25 g_tmp_clob clob;
26 g_tmp_blob blob;
27
28 -- Bug 6729909 For Deleting Multiple Root Tag
29
30 g_source_type varchar2(10);
31 g_file_type varchar2(10);
32 g_flag number;
33 g_payroll_id number;
34 g_chld_act number;
35 g_char_set varchar2(30);
36 g_bot_root_tag varchar2(30);
37 --
38 --------------------------- open_file -------------------------
39 /*
40 NAME
41 open_file - Opene a file fragment clob
42 DESCRIPTION
43 Open a clob.
44 NOTES
45 <none>
46 */
47 procedure open_file
48 (p_source_id in number,
49 p_source_type in varchar2,
50 p_file_location in varchar2,
51 p_file_type in varchar2,
52 p_int_file_name in varchar2,
53 p_sequence in number,
54 p_file_id out nocopy number
55 )
56 is
57 --
58 file_id number;
59 --
60 begin
61 --
62 /* chk if an open file already exits */
63
64 begin
65
66 -- Assign values to Global Variable for deleting Mul root tag
67 g_source_type := p_source_type;
68 g_file_type := p_file_type;
69 g_payroll_id := p_source_id;
70 g_char_set := hr_mx_utility.get_IANA_charset;
71
72 select file_detail_id
73 into file_id
74 from pay_file_details
75 where source_id=p_source_id
76 and source_type=p_source_type
77 and internal_file_name=p_int_file_name
78 and sequence=p_sequence;
79
80 g_file_list(file_id).file_detail_id := file_id;
81
82 select blob_file_fragment
83 into g_file_list(file_id).file_locator
84 from pay_file_details
85 where file_detail_id=file_id
86 for update of blob_file_fragment;
87
88
89 if (dbms_lob.isopen(g_file_list(file_id).file_locator)<>1)
90 then
91 dbms_lob.open(g_file_list(file_id).file_locator, DBMS_LOB.LOB_READWRITE);
92 dbms_lob.trim(g_file_list(file_id).file_locator, 0);
93 g_file_list(file_id).position := 1;
94 end if;
95
96 exception
97 when no_data_found then
98 select pay_file_details_s.nextval
99 into file_id
100 from dual;
101 --
102 g_file_list(file_id).file_detail_id := file_id;
103 g_file_list(file_id).position := 1;
104 g_file_list(file_id).file_type := p_file_type;
105 g_file_list(file_id).int_file_name := p_int_file_name;
106 g_file_list(file_id).sequence := p_sequence;
107 --
108 insert into pay_file_details
109 (file_detail_id,
110 source_id,
111 source_type,
112 file_location,
113 file_type,
114 internal_file_name,
115 blob_file_fragment,
116 sequence
117 )
118 values
119 (file_id,
120 p_source_id,
121 p_source_type,
122 p_file_location,
123 p_file_type,
124 p_int_file_name,
125 empty_blob(),
126 p_sequence
127 );
128
129 select blob_file_fragment
130 into g_file_list(file_id).file_locator
131 from pay_file_details
132 where file_detail_id = file_id
133 for update of blob_file_fragment;
134
135 dbms_lob.open(g_file_list(file_id).file_locator, DBMS_LOB.LOB_READWRITE);
136
137 end;
138 --
139 if (g_head_file_ptr is not null) then
140 g_file_list(g_head_file_ptr).prvptr := file_id;
141 end if;
142 g_file_list(file_id).nxtptr := g_head_file_ptr;
143 g_file_list(file_id).prvptr := null;
144 g_head_file_ptr := file_id;
145 --
146 p_file_id := file_id;
147 --
148 end open_file;
149
150 --
151 ------------------------- delete_mul_root_tag -------------------------
152 /*
153 Bug : 6729909
154 NAME
155 delete_mul_root_tag -
156 DESCRIPTION This procedure gets called from read_from_clob
157 and will delete root tag from the text except from the first fragment
158 of first file and last fragment of last file.
159 NOTES
160 <none>
161
162 Bug : 6795217
163 NAME
164 delete_mul_root_tag -
165
166 DESCRIPTION : Modified procedure to remove the end tags in the cases where
167 end tags is broken in 2 fragments,
168 Example End tag is '</ARCHIVE_CHEQUE_WRITER>'
169 Case 1: </ARCHIVE_CHEQUE is coming in second last fragment
170 and remaining _WRITER> is coming in last fragment
171 Case 2: < is coming in second last fragment
172 and remaining /ARCHIVE_CHEQUE_WRITER> is coming in last fragment
173
174 Fix : a.) Identify the Second last segment by searching for '</CHEQUE>' or '</PAYSLIP>'
175 in the last 32 or 26 character (Worse case scenario "</CHEQUE></ARCHIVE_CHEQUE_WRITER" is 32 Characters,
176 similary for Deposit Device its "</PAYSLIP></PAYSLIP_REPORT" is 26 characters).
177 b.) Strip "/" from the fragment after '</CHEQUE>' or '</PAYSLIP>' .
178 c.) Identify the last fragment , if the length of the fragment is less then the Bottom End Root Tag.
179 d.) In the last fragment if the first character is "/" remove that .
180 e.) Replace the last '>' by 'Z/>' making it a dummy tag .
181
182 NOTES
183 <none>
184 */
185 procedure delete_mul_root_tag(p_text in out nocopy raw)
186 is
187
188 l_top_root_tag varchar2(30);
189 l_bot_root_tag varchar2(30);
190 l_broken_text varchar2(30);
191 l_text varchar2(8000);
192
193 begin
194
195 hr_utility.trace('Entering delete_mul_root_tag ');
196 l_text := utl_raw.cast_to_varchar2(p_text);
197 if g_source_type = 'PPA' then
198
199
200 if (substr(l_text,1,5) ='<?xml') then
201 if instr(l_text,'<PAYSLIP_REPORT>') <> 0 then
202 l_top_root_tag := '<PAYSLIP_REPORT>' ;
203 g_bot_root_tag := '</PAYSLIP_REPORT>' ;
204 elsif instr(l_text,'<ARCHIVE_CHEQUE_WRITER>') <> 0 then
205 l_top_root_tag := '<ARCHIVE_CHEQUE_WRITER>';
206 g_bot_root_tag := '</ARCHIVE_CHEQUE_WRITER>';
207 end if;
208
209 if l_top_root_tag in ('<PAYSLIP_REPORT>', '<ARCHIVE_CHEQUE_WRITER>') then
210
211 if g_flag <> 1 then
212 if l_top_root_tag = '<PAYSLIP_REPORT>' then
213 select count(*)
214 into g_chld_act
215 from pay_temp_object_actions
216 where payroll_action_id = g_payroll_id
217 and action_status = 'C';
218 elsif l_top_root_tag = '<ARCHIVE_CHEQUE_WRITER>' then
219
220 select count(*)
221 into g_chld_act
222 from pay_assignment_actions
223 where payroll_action_id = g_payroll_id
224 and action_status IN ('C', 'S');
225 end if;
226 end if;
227
228 g_chld_act := g_chld_act - 1 ;
229
230 if g_flag = 1 then
231 l_text := replace(l_text,('<?xml version="1.0" encoding="'||g_char_set||'"?>')||l_top_root_tag);
232 end if;
233 g_flag := 1;
234 end if;
235
236 end if;
237
238 if (substr(l_text,-17,17) ='</PAYSLIP_REPORT>') and g_chld_act <> 0 then
239 l_bot_root_tag := '</PAYSLIP_REPORT>' ;
240 l_text := replace(l_text,l_bot_root_tag);
241
242 elsif (substr(l_text,-24,24) ='</ARCHIVE_CHEQUE_WRITER>') and g_chld_act <> 0 then
243 l_bot_root_tag := '</ARCHIVE_CHEQUE_WRITER>';
244 l_text := replace(l_text,l_bot_root_tag);
245
246 /* Begin Bug 6795217 */
247 elsif (instr(substr(l_text,-32,32),'</CHEQUE>') <> 0) and g_chld_act <> 0 then
248
249 l_broken_text := substr(l_text,(instr(l_text,'</CHEQUE>')+9));
250 l_broken_text := replace(l_broken_text,'/');
251 l_text := substr(l_text,1,(instr(l_text,'</CHEQUE>')+8))||l_broken_text;
252
253 elsif (instr(substr(l_text,-26,26),'</PAYSLIP>') <> 0) and g_chld_act <> 0 then
254
255 l_broken_text := substr(l_text,(instr(l_text,'</PAYSLIP>')+10));
256 l_broken_text := replace(l_broken_text,'/');
257 l_text := substr(l_text,1,(instr(l_text,'</PAYSLIP>')+9))||l_broken_text;
258
259 elsif (length(l_text) < length(g_bot_root_tag) and g_chld_act <> 0 ) then
260
261 if substr(l_text,1,1) = '/' then
262 l_text := substr(l_text,2);
263 end if;
264
265 l_text := replace(l_text,'>','Z/>');
266 end if;
267 /* End Bug 6795217 */
268
269 end if;
270 p_text := utl_raw.cast_to_raw(l_text);
271 hr_utility.trace('Leaving delete_mul_root_tag ');
272
273 end delete_mul_root_tag;
274
275 --
276 --------------------------- open_clob_direct -------------------------
277 /*
278 NAME
279 open_clob_direct -
280 DESCRIPTION
281 NOTES
282 <none>
283 */
284 procedure open_clob_direct(p_file_id in number)
285 is
286 begin
287 --
288 select file_fragment
289 into g_tmp_clob
290 from pay_file_details
291 where file_detail_id = p_file_id;
292 --
293 dbms_lob.open(g_tmp_clob, DBMS_LOB.LOB_READONLY);
294 --
295 end open_clob_direct;
296
297 procedure open_blob_direct(p_file_id in number)
298 is
299 begin
300 --
301 select blob_file_fragment
302 into g_tmp_blob
303 from pay_file_details
304 where file_detail_id = p_file_id;
305 --
306 dbms_lob.open(g_tmp_blob, DBMS_LOB.LOB_READONLY);
307
308 end open_blob_direct;
309 --
310 --------------------------- read_from_clob_direct ----------------
311 /*
312 NAME
313 read_from_clob_direct - Reads directly from a clob
314 DESCRIPTION
315 NOTES
316 <none>
317 */
318 procedure read_from_clob_direct
319 ( p_clob in clob,
320 p_size in out nocopy number,
321 p_position in number,
322 p_text out nocopy varchar2
323 )
324 is
325 --
326 begin
327 --
328 dbms_lob.read(p_clob,
329 p_size,
330 p_position,
331 p_text
332 );
333 --
334 exception
335 when no_data_found then
336 p_text := null;
337 --
338 end read_from_clob_direct;
339
340
341 procedure read_from_blob_direct
342 ( p_blob in blob,
343 p_size in out nocopy number,
344 p_position in number,
345 p_text out nocopy varchar2
346 )
347 is
348 raw_data raw(8000); --changed raw_data size from 2000 bug no 4775422
349 --
350 begin
351 --
352 dbms_lob.read(p_blob,
353 p_size,
354 p_position,
355 raw_data
356 );
357 p_text:=utl_raw.cast_to_varchar2(raw_data);
358 --
359 exception
360 when no_data_found then
361 p_text := null;
362 --
363 end read_from_blob_direct;
364 --
365 --------------------------- close_clob_direct ----------------
366 /*
367 NAME
368 close_clob_direct - Closes the global clob
369 DESCRIPTION
370 NOTES
371 <none>
372 */
373 procedure close_clob_direct
374 is
375 begin
376 --
377 dbms_lob.close(g_tmp_clob);
378 g_tmp_clob := null;
379 --
380 end close_clob_direct;
381 --
382
383 procedure close_blob_direct
384 is
385 begin
386 --
387 dbms_lob.close(g_tmp_blob);
388 g_tmp_blob := null;
389 --
390 end close_blob_direct;
391 --
392 --------------------------- read_from_clob ----------------
393 /*
394 NAME
395 read_from_clob - Read from a specified clob
396 DESCRIPTION
397 This reads from a specified file/clob in the
398 global pointer.
399 NOTES
400 <none>
401 */
402 procedure read_from_clob
403 (
404 p_file_id in number,
405 p_size in out nocopy number,
406 p_position in number,
407 p_text out nocopy varchar2
408 )
409 is
410 l_size number;
411 l_file_type pay_file_details.file_type%type;
412 l_raw_text raw(8000);
413 begin
414 --
415
416 if (g_tmp_blob is null) then
417 open_blob_direct(p_file_id);
418 end if;
419 --
420 l_size := p_size;
421 read_from_blob_direct
422 (g_tmp_blob,
423 l_size,
424 p_position,
425 p_text
426 );
427
428 /* Bug 6729909 Calling delete_mul_root_tag(p_text) to remove the top Level tag for merging of XML */
429 l_raw_text := utl_raw.cast_to_raw(p_text);
430 delete_mul_root_tag(l_raw_text);
431 p_text := utl_raw.cast_to_varchar2(l_raw_text);
432
433 close_blob_direct;
434 /* commented for bug no 4775422
435 if (p_text is null or p_size <> l_size) then
436 close_blob_direct;
437 end if;*/
438 --
439 p_size := l_size;
440 --
441 end;
442 --
443 procedure read_from_clob
444 (
445 p_clob in clob,
446 p_size in out nocopy number,
447 p_position in number,
448 p_text out nocopy varchar2
449 )
450 is
451 l_size number;
452 begin
453 --
454 l_size := p_size;
455 read_from_clob_direct
456 (p_clob,
457 l_size,
458 p_position,
459 p_text
460 );
461 --
462 p_size := l_size;
463 --
464 end;
465 --------------------------- read_from_clob_raw ----------------
466 /*
467 NAME
468 read_from_clob_raw - Read from a specified clob
469 DESCRIPTION
470 This reads from a specified file/clob in the
471 global pointer and returns raw chunk for File types
472 PDF and CATPDF.
473 NOTES
474 <none>
475 */
476 procedure read_from_clob_raw
477 (
478 p_file_id in number,
479 p_size in out nocopy number,
480 p_position in number,
481 p_text out nocopy raw
482 )
483 is
484 l_size number;
485 l_file_type pay_file_details.file_type%type;
486 raw_data raw(8000);
487 begin
488 --
489 if (g_tmp_blob is null) then
490 open_blob_direct(p_file_id);
491 end if;
492 --
493 Begin
494 l_size := p_size;
495 dbms_lob.read(g_tmp_blob,
496 l_size,
497 p_position,
498 raw_data
499 );
500 p_text:=raw_data;
501
502 exception
503 when no_data_found then
504 p_text := null;
505 l_size := 0;
506 end;
507 --
508 delete_mul_root_tag(p_text);
509 close_blob_direct;
510 --
511 p_size := l_size;
512 --
513 end;
514 --
515 --
516 --------------------------- write_to_file -------------------------
517 /*
518 NAME
519 write_to_file - Write text to the clob
520 DESCRIPTION
521 Write text to the specified clob
522 NOTES
523 <none>
524 */
525 procedure write_to_file
526 (p_file_id in number,
527 p_text in varchar2
528 )
529 is
530 --
531 text_size number;
532 raw_data raw(8000);
533 lob_len number;
534 --
535 begin
536 --
537 raw_data:=utl_raw.cast_to_raw(p_text);
538 text_size:=utl_raw.length(raw_data);
539 hr_utility.trace('p_text size = ' || to_char(length(p_text)));
540 hr_utility.trace('raw_data Size = ' || text_size);
541
542 dbms_lob.write(g_file_list(p_file_id).file_locator,
543 text_size,
544 g_file_list(p_file_id).position,
545 raw_data
546 );
547 g_file_list(p_file_id).position := g_file_list(p_file_id).position
548 + text_size;
549 hr_utility.trace('Blob Size = ' ||
550 to_char(dbms_lob.getlength(g_file_list(p_file_id).file_locator)));
551 --
552 end write_to_file;
553 --
554 --------------------------- write_to_file_raw -------------------------
555 /*
556 NAME
557 write_to_file_raw - Write text to the clob
558 DESCRIPTION
559 Write text to the specified clob
560 NOTES
561 <none>
562 */
563 procedure write_to_file_raw
564 (p_file_id in number,
565 p_text in raw
566 )
567 is
568 --
569 text_size number;
570 raw_data raw(8000);
571 lob_len number;
572 --
573 begin
574 --
575 --
576 raw_data:= p_text;
577 text_size:=utl_raw.length(raw_data);
578 hr_utility.trace('p_text size = ' || to_char(length(p_text)));
579 hr_utility.trace('raw_data Size = ' || text_size);
580
581 dbms_lob.write(g_file_list(p_file_id).file_locator,
582 text_size,
583 g_file_list(p_file_id).position,
584 raw_data
585 );
586 g_file_list(p_file_id).position := g_file_list(p_file_id).position
587 + text_size;
588 hr_utility.trace('Blob Size = ' ||
589 to_char(dbms_lob.getlength(g_file_list(p_file_id).file_locator)));
590 --
591 end write_to_file_raw;
592 --
593 --------------------------- close_file -------------------------
594 /*
595 NAME
596 close_file - Close the clob
597 DESCRIPTION
598 Close teh specified clob and delete the row from the plsql
599 table.
600 NOTES
601 <none>
602 */
603 procedure close_file
604 (p_file_id in number)
605 is
606 prvptr number;
607 nxtptr number;
608 begin
609 --
610 dbms_lob.close(g_file_list(p_file_id).file_locator);
611
612 --
613 prvptr := g_file_list(p_file_id).prvptr ;
614 nxtptr := g_file_list(p_file_id).nxtptr ;
615 --
616 if (prvptr is not null) then
617 g_file_list(prvptr).nxtptr := g_file_list(p_file_id).nxtptr;
618 else
619 g_head_file_ptr := g_file_list(p_file_id).nxtptr;
620 end if;
621 --
622 if (nxtptr is not null) then
623 g_file_list(nxtptr).prvptr := g_file_list(p_file_id).prvptr;
624 end if;
625 --
626 g_file_list.delete(p_file_id);
627 --
628 end close_file;
629 --
630 --------------------------- open_temp_file -------------------------
631 /*
632 NAME
633 open_temp_file - Open temporary file
634 DESCRIPTION
635 open a temporary file
636 NOTES
637 <none>
638 */
639 procedure open_temp_file
640 (p_file in out nocopy clob)
641 is
642 begin
643 dbms_lob.createtemporary(p_file, TRUE);
644 end open_temp_file;
645 --
646 --------------------------- close_temp_file -------------------------
647 /*
648 NAME
649 close_temp_file - Close temporary file
650 DESCRIPTION
651 close a temporary file
652 NOTES
653 <none>
654 */
655 procedure close_temp_file(p_file in out nocopy clob)
656 is
657 begin
658 dbms_lob.freetemporary(p_file);
659 end close_temp_file;
660 --
661 -- Added for Bug # 3688801.
662 --------------------------- form_read_clob ---------------------
663 /*
664 NAME
665 form_read_clob - Read from a clob
666 DESCRIPTION
667 This reads from a clob and is called from the form.
668
669 NOTES
670 <none>
671 */
672 procedure form_read_clob
673 (
674 p_file_id in number,
675 p_size in out nocopy number,
676 p_position in number,
677 p_text out nocopy varchar2
678 )
679 is
680 l_size number;
681 l_clob clob;
682 begin
683 --
684 select file_fragment
685 into l_clob
686 from pay_file_details
687 where file_detail_id = p_file_id;
688 --
689 if l_clob is null then
690 p_size := 0;
691 p_text := null;
692 return;
693 end if;
694 --
695 dbms_lob.open(l_clob, DBMS_LOB.LOB_READONLY);
696 --
697 l_size := p_size;
698 read_from_clob_direct
699 (l_clob,
700 l_size,
701 p_position,
702 p_text
703 );
704 --
705 dbms_lob.close(l_clob);
706 --
707 p_size := l_size;
708 --
709 end form_read_clob;
710 --
711 --------------------------- return_clob_length --------------
712 /*
713 NAME
714 return_clob_length - Get the length of the clob
715 DESCRIPTION
716 This returns the length of the specified file/clob.
717 NOTES
718 <none>
719 */
720 function return_clob_length
721 ( p_file_id in number )
722 return number
723 is
724 --
725 l_clob clob;
726 l_length number;
727 --
728 begin
729 --
730 select file_fragment
731 into l_clob
732 from pay_file_details
733 where file_detail_id = p_file_id;
734 --
735 if l_clob is null then
736 return null;
737 end if;
738 --
739 dbms_lob.open(l_clob, DBMS_LOB.LOB_READONLY);
740 --
741 l_length := DBMS_LOB.GETLENGTH(l_clob);
742
743 dbms_lob.close(l_clob);
744 --
745 return l_length;
746 --
747 end return_clob_length;
748
749 function return_length
750 ( p_file_id in number )
751 return number
752 is
753 --
754 l_blob blob;
755 l_length number;
756 --
757 begin
758 --
759 select blob_file_fragment
760 into l_blob
761 from pay_file_details
762 where file_detail_id = p_file_id;
763 --
764 if l_blob is null then
765 return null;
766 end if;
767 --
768 dbms_lob.open(l_blob, DBMS_LOB.LOB_READONLY);
769 --
770 l_length := DBMS_LOB.GETLENGTH(l_blob);
771
772 dbms_lob.close(l_blob);
773 --
774 return l_length;
775 --
776 end return_length;
777
778 procedure write_to_magtape_lob(p_text in varchar)
779 is
780 text_size number;
781 raw_data raw(32767);
782 begin
783 raw_data:=utl_raw.cast_to_raw(p_text);
784 text_size:=utl_raw.length(raw_data);
785 dbms_lob.writeappend(pay_mag_tape.g_blob_value,
786 text_size,
787 raw_data
788 );
789 end;
790
791 procedure write_to_magtape_lob(p_data in blob)
792 is
793 begin
794 dbms_lob.append(pay_mag_tape.g_blob_value,
795 p_data);
796 end;
797
798 --
799 begin
800 --
801 g_head_file_ptr := null;
802 g_tmp_clob := null;
803 --
804
805 --Bug 6729909 Initializae Variable for Deleting Mul root tag
806 g_source_type := null;
807 g_file_type := null;
808 g_flag := 0;
809 g_payroll_id := 0;
810 g_chld_act := null;
811 g_char_set := null;
812
813 end pay_core_files;