[Home] [Help]
PACKAGE BODY: APPS.PAY_CORE_FILES
Source
1 package body pay_core_files as
2 /* $Header: pycofile.pkb 120.11.12010000.3 2008/12/26 07:52:41 priupadh ship $ */
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 varchar2)
186 is
187
188 l_top_root_tag varchar2(30);
189 l_bot_root_tag varchar2(30);
190 l_broken_text varchar2(30);
191
192 begin
193
194 hr_utility.trace('Entering delete_mul_root_tag ');
195 if g_source_type = 'PPA' then
196
197
198 if (substr(p_text,1,5) ='<?xml') then
199 if instr(p_text,'<PAYSLIP_REPORT>') <> 0 then
200 l_top_root_tag := '<PAYSLIP_REPORT>' ;
201 g_bot_root_tag := '</PAYSLIP_REPORT>' ;
202 elsif instr(p_text,'<ARCHIVE_CHEQUE_WRITER>') <> 0 then
203 l_top_root_tag := '<ARCHIVE_CHEQUE_WRITER>';
204 g_bot_root_tag := '</ARCHIVE_CHEQUE_WRITER>';
205 end if;
206
207 if l_top_root_tag in ('<PAYSLIP_REPORT>', '<ARCHIVE_CHEQUE_WRITER>') then
208
209 if g_flag <> 1 then
210 if l_top_root_tag = '<PAYSLIP_REPORT>' then
211 select count(*)
212 into g_chld_act
213 from pay_temp_object_actions
214 where payroll_action_id = g_payroll_id
215 and action_status = 'C';
216 elsif l_top_root_tag = '<ARCHIVE_CHEQUE_WRITER>' then
217
218 select count(*)
219 into g_chld_act
220 from pay_assignment_actions
221 where payroll_action_id = g_payroll_id
222 and action_status IN ('C', 'S');
223 end if;
224 end if;
225
226 g_chld_act := g_chld_act - 1 ;
227
228 if g_flag = 1 then
229 p_text := replace(p_text,('<?xml version="1.0" encoding="'||g_char_set||'"?>')||l_top_root_tag);
230 end if;
231 g_flag := 1;
232 end if;
233
234 end if;
235
236 if (substr(p_text,-17,17) ='</PAYSLIP_REPORT>') and g_chld_act <> 0 then
237 l_bot_root_tag := '</PAYSLIP_REPORT>' ;
238 p_text := replace(p_text,l_bot_root_tag);
239
240 elsif (substr(p_text,-24,24) ='</ARCHIVE_CHEQUE_WRITER>') and g_chld_act <> 0 then
241 l_bot_root_tag := '</ARCHIVE_CHEQUE_WRITER>';
242 p_text := replace(p_text,l_bot_root_tag);
243
244 /* Begin Bug 6795217 */
245 elsif (instr(substr(p_text,-32,32),'</CHEQUE>') <> 0) and g_chld_act <> 0 then
246
247 l_broken_text := substr(p_text,(instr(p_text,'</CHEQUE>')+9));
248 l_broken_text := replace(l_broken_text,'/');
249 p_text := substr(p_text,1,(instr(p_text,'</CHEQUE>')+8))||l_broken_text;
250
251 elsif (instr(substr(p_text,-26,26),'</PAYSLIP>') <> 0) and g_chld_act <> 0 then
252
253 l_broken_text := substr(p_text,(instr(p_text,'</PAYSLIP>')+10));
254 l_broken_text := replace(l_broken_text,'/');
255 p_text := substr(p_text,1,(instr(p_text,'</PAYSLIP>')+9))||l_broken_text;
256
257 elsif (length(p_text) < length(g_bot_root_tag) and g_chld_act <> 0 ) then
258
259 if substr(p_text,1,1) = '/' then
260 p_text := substr(p_text,2);
261 end if;
262
263 p_text := replace(p_text,'>','Z/>');
264 end if;
265 /* End Bug 6795217 */
266
267 end if;
268 hr_utility.trace('Leaving delete_mul_root_tag ');
269
270 end delete_mul_root_tag;
271
272 --
273 --------------------------- open_clob_direct -------------------------
274 /*
275 NAME
276 open_clob_direct -
277 DESCRIPTION
278 NOTES
279 <none>
280 */
281 procedure open_clob_direct(p_file_id in number)
282 is
283 begin
284 --
285 select file_fragment
286 into g_tmp_clob
287 from pay_file_details
288 where file_detail_id = p_file_id;
289 --
290 dbms_lob.open(g_tmp_clob, DBMS_LOB.LOB_READONLY);
291 --
292 end open_clob_direct;
293
294 procedure open_blob_direct(p_file_id in number)
295 is
296 begin
297 --
298 select blob_file_fragment
299 into g_tmp_blob
300 from pay_file_details
301 where file_detail_id = p_file_id;
302 --
303 dbms_lob.open(g_tmp_blob, DBMS_LOB.LOB_READONLY);
304
305 end open_blob_direct;
306 --
307 --------------------------- read_from_clob_direct ----------------
308 /*
309 NAME
310 read_from_clob_direct - Reads directly from a clob
311 DESCRIPTION
312 NOTES
313 <none>
314 */
315 procedure read_from_clob_direct
316 ( p_clob in clob,
317 p_size in out nocopy number,
318 p_position in number,
319 p_text out nocopy varchar2
320 )
321 is
322 --
323 begin
324 --
325 dbms_lob.read(p_clob,
326 p_size,
327 p_position,
328 p_text
329 );
330 --
331 exception
332 when no_data_found then
333 p_text := null;
334 --
335 end read_from_clob_direct;
336
337
338 procedure read_from_blob_direct
339 ( p_blob in blob,
340 p_size in out nocopy number,
341 p_position in number,
342 p_text out nocopy varchar2
343 )
344 is
345 raw_data raw(8000); --changed raw_data size from 2000 bug no 4775422
346 --
347 begin
348 --
349 dbms_lob.read(p_blob,
350 p_size,
351 p_position,
352 raw_data
353 );
354 p_text:=utl_raw.cast_to_varchar2(raw_data);
355 --
356 exception
357 when no_data_found then
358 p_text := null;
359 --
360 end read_from_blob_direct;
361 --
362 --------------------------- close_clob_direct ----------------
363 /*
364 NAME
365 close_clob_direct - Closes the global clob
366 DESCRIPTION
367 NOTES
368 <none>
369 */
370 procedure close_clob_direct
371 is
372 begin
373 --
374 dbms_lob.close(g_tmp_clob);
375 g_tmp_clob := null;
376 --
377 end close_clob_direct;
378 --
379
380 procedure close_blob_direct
381 is
382 begin
383 --
384 dbms_lob.close(g_tmp_blob);
385 g_tmp_blob := null;
386 --
387 end close_blob_direct;
388 --
389 --------------------------- read_from_clob ----------------
390 /*
391 NAME
392 read_from_clob - Read from a specified clob
393 DESCRIPTION
394 This reads from a specified file/clob in the
395 global pointer.
396 NOTES
397 <none>
398 */
399 procedure read_from_clob
400 (
401 p_file_id in number,
402 p_size in out nocopy number,
403 p_position in number,
404 p_text out nocopy varchar2
405 )
406 is
407 l_size number;
408 l_file_type pay_file_details.file_type%type;
409 begin
410 --
411
412 if (g_tmp_blob is null) then
413 open_blob_direct(p_file_id);
414 end if;
415 --
416 l_size := p_size;
417 read_from_blob_direct
418 (g_tmp_blob,
419 l_size,
420 p_position,
421 p_text
422 );
423
424 /* Bug 6729909 Calling delete_mul_root_tag(p_text) to remove the top Level tag for merging of XML */
425 delete_mul_root_tag(p_text);
426
427 close_blob_direct;
428 /* commented for bug no 4775422
429 if (p_text is null or p_size <> l_size) then
430 close_blob_direct;
431 end if;*/
432 --
433 p_size := l_size;
434 --
435 end;
436 --
437 procedure read_from_clob
438 (
439 p_clob in clob,
440 p_size in out nocopy number,
441 p_position in number,
442 p_text out nocopy varchar2
443 )
444 is
445 l_size number;
446 begin
447 --
448 l_size := p_size;
449 read_from_clob_direct
450 (p_clob,
451 l_size,
452 p_position,
453 p_text
454 );
455 --
456 p_size := l_size;
457 --
458 end;
459 --------------------------- read_from_clob_raw ----------------
460 /*
461 NAME
462 read_from_clob_raw - Read from a specified clob
463 DESCRIPTION
464 This reads from a specified file/clob in the
465 global pointer and returns raw chunk for File types
466 PDF and CATPDF.
467 NOTES
468 <none>
469 */
470 procedure read_from_clob_raw
471 (
472 p_file_id in number,
473 p_size in out nocopy number,
474 p_position in number,
475 p_text out nocopy raw
476 )
477 is
478 l_size number;
479 l_file_type pay_file_details.file_type%type;
480 raw_data raw(8000);
481 begin
482 --
483 if (g_tmp_blob is null) then
484 open_blob_direct(p_file_id);
485 end if;
486 --
487 Begin
488 l_size := p_size;
489 dbms_lob.read(g_tmp_blob,
490 l_size,
491 p_position,
492 raw_data
493 );
494 p_text:=raw_data;
495
496 exception
497 when no_data_found then
498 p_text := null;
499 l_size := 0;
500 end;
501 close_blob_direct;
502 --
503 p_size := l_size;
504 --
505 end;
506 --
507 --
508 --------------------------- write_to_file -------------------------
509 /*
510 NAME
511 write_to_file - Write text to the clob
512 DESCRIPTION
513 Write text to the specified clob
514 NOTES
515 <none>
516 */
517 procedure write_to_file
518 (p_file_id in number,
519 p_text in varchar2
520 )
521 is
522 --
523 text_size number;
524 raw_data raw(8000);
525 lob_len number;
526 --
527 begin
528 --
529 raw_data:=utl_raw.cast_to_raw(p_text);
530 text_size:=utl_raw.length(raw_data);
531 hr_utility.trace('p_text size = ' || to_char(length(p_text)));
532 hr_utility.trace('raw_data Size = ' || text_size);
533
534 dbms_lob.write(g_file_list(p_file_id).file_locator,
535 text_size,
536 g_file_list(p_file_id).position,
537 raw_data
538 );
539 g_file_list(p_file_id).position := g_file_list(p_file_id).position
540 + text_size;
541 hr_utility.trace('Blob Size = ' ||
542 to_char(dbms_lob.getlength(g_file_list(p_file_id).file_locator)));
543 --
544 end write_to_file;
545 --
546 --------------------------- write_to_file_raw -------------------------
547 /*
548 NAME
549 write_to_file_raw - Write text to the clob
550 DESCRIPTION
551 Write text to the specified clob
552 NOTES
553 <none>
554 */
555 procedure write_to_file_raw
556 (p_file_id in number,
557 p_text in raw
558 )
559 is
560 --
561 text_size number;
562 raw_data raw(8000);
563 lob_len number;
564 --
565 begin
566 --
567 --
568 raw_data:= p_text;
569 text_size:=utl_raw.length(raw_data);
570 hr_utility.trace('p_text size = ' || to_char(length(p_text)));
571 hr_utility.trace('raw_data Size = ' || text_size);
572
573 dbms_lob.write(g_file_list(p_file_id).file_locator,
574 text_size,
575 g_file_list(p_file_id).position,
576 raw_data
577 );
578 g_file_list(p_file_id).position := g_file_list(p_file_id).position
579 + text_size;
580 hr_utility.trace('Blob Size = ' ||
581 to_char(dbms_lob.getlength(g_file_list(p_file_id).file_locator)));
582 --
583 end write_to_file_raw;
584 --
585 --------------------------- close_file -------------------------
586 /*
587 NAME
588 close_file - Close the clob
589 DESCRIPTION
590 Close teh specified clob and delete the row from the plsql
591 table.
592 NOTES
593 <none>
594 */
595 procedure close_file
596 (p_file_id in number)
597 is
598 prvptr number;
599 nxtptr number;
600 begin
601 --
602 dbms_lob.close(g_file_list(p_file_id).file_locator);
603
604 --
605 prvptr := g_file_list(p_file_id).prvptr ;
606 nxtptr := g_file_list(p_file_id).nxtptr ;
607 --
608 if (prvptr is not null) then
609 g_file_list(prvptr).nxtptr := g_file_list(p_file_id).nxtptr;
610 else
611 g_head_file_ptr := g_file_list(p_file_id).nxtptr;
612 end if;
613 --
614 if (nxtptr is not null) then
615 g_file_list(nxtptr).prvptr := g_file_list(p_file_id).prvptr;
616 end if;
617 --
618 g_file_list.delete(p_file_id);
619 --
620 end close_file;
621 --
622 --------------------------- open_temp_file -------------------------
623 /*
624 NAME
625 open_temp_file - Open temporary file
626 DESCRIPTION
627 open a temporary file
628 NOTES
629 <none>
630 */
631 procedure open_temp_file
632 (p_file in out nocopy clob)
633 is
634 begin
635 dbms_lob.createtemporary(p_file, TRUE);
636 end open_temp_file;
637 --
638 --------------------------- close_temp_file -------------------------
639 /*
640 NAME
641 close_temp_file - Close temporary file
642 DESCRIPTION
643 close a temporary file
644 NOTES
645 <none>
646 */
647 procedure close_temp_file(p_file in out nocopy clob)
648 is
649 begin
650 dbms_lob.freetemporary(p_file);
651 end close_temp_file;
652 --
653 -- Added for Bug # 3688801.
654 --------------------------- form_read_clob ---------------------
655 /*
656 NAME
657 form_read_clob - Read from a clob
658 DESCRIPTION
659 This reads from a clob and is called from the form.
660
661 NOTES
662 <none>
663 */
664 procedure form_read_clob
665 (
666 p_file_id in number,
667 p_size in out nocopy number,
668 p_position in number,
669 p_text out nocopy varchar2
670 )
671 is
672 l_size number;
673 l_clob clob;
674 begin
675 --
676 select file_fragment
677 into l_clob
678 from pay_file_details
679 where file_detail_id = p_file_id;
680 --
681 if l_clob is null then
682 p_size := 0;
683 p_text := null;
684 return;
685 end if;
686 --
687 dbms_lob.open(l_clob, DBMS_LOB.LOB_READONLY);
688 --
689 l_size := p_size;
690 read_from_clob_direct
691 (l_clob,
692 l_size,
693 p_position,
694 p_text
695 );
696 --
697 dbms_lob.close(l_clob);
698 --
699 p_size := l_size;
700 --
701 end form_read_clob;
702 --
703 --------------------------- return_clob_length --------------
704 /*
705 NAME
706 return_clob_length - Get the length of the clob
707 DESCRIPTION
708 This returns the length of the specified file/clob.
709 NOTES
710 <none>
711 */
712 function return_clob_length
713 ( p_file_id in number )
714 return number
715 is
716 --
717 l_clob clob;
718 l_length number;
719 --
720 begin
721 --
722 select file_fragment
723 into l_clob
724 from pay_file_details
725 where file_detail_id = p_file_id;
726 --
727 if l_clob is null then
728 return null;
729 end if;
730 --
731 dbms_lob.open(l_clob, DBMS_LOB.LOB_READONLY);
732 --
733 l_length := DBMS_LOB.GETLENGTH(l_clob);
734
735 dbms_lob.close(l_clob);
736 --
737 return l_length;
738 --
739 end return_clob_length;
740
741 function return_length
742 ( p_file_id in number )
743 return number
744 is
745 --
746 l_blob blob;
747 l_length number;
748 --
749 begin
750 --
751 select blob_file_fragment
752 into l_blob
753 from pay_file_details
754 where file_detail_id = p_file_id;
755 --
756 if l_blob is null then
757 return null;
758 end if;
759 --
760 dbms_lob.open(l_blob, DBMS_LOB.LOB_READONLY);
761 --
762 l_length := DBMS_LOB.GETLENGTH(l_blob);
763
764 dbms_lob.close(l_blob);
765 --
766 return l_length;
767 --
768 end return_length;
769
770 procedure write_to_magtape_lob(p_text in varchar)
771 is
772 text_size number;
773 raw_data raw(32767);
774 begin
775 raw_data:=utl_raw.cast_to_raw(p_text);
776 text_size:=utl_raw.length(raw_data);
777 dbms_lob.writeappend(pay_mag_tape.g_blob_value,
778 text_size,
779 raw_data
780 );
781 end;
782
783 procedure write_to_magtape_lob(p_data in blob)
784 is
785 begin
786 dbms_lob.append(pay_mag_tape.g_blob_value,
787 p_data);
788 end;
789
790 --
791 begin
792 --
793 g_head_file_ptr := null;
794 g_tmp_clob := null;
795 --
796
797 --Bug 6729909 Initializae Variable for Deleting Mul root tag
798 g_source_type := null;
799 g_file_type := null;
800 g_flag := 0;
801 g_payroll_id := 0;
802 g_chld_act := null;
803 g_char_set := null;
804
805 end pay_core_files;