[Home] [Help]
PACKAGE BODY: APPS.EDR_ISIGN_TEMPLATE_PKG
Source
1 PACKAGE BODY EDR_ISIGN_TEMPLATE_PKG AS
2 /* $Header: EDRITPB.pls 120.0.12000000.1 2007/01/18 05:54:05 appldev ship $ */
3
4 -- EDR_ISIGN_TEMPLATE_PKG.GET_XSLFO_TEMPLATE procedure is called from EDRRuleXMLPublisherHandler Class
5 -- to get the handle of XSLFO Blob. It passes template name and template type as input and get the template
6 -- blob and other information as output.
7
8 -- P_STYLE_SHEET - Original file name of template
9 -- P_STYLE_SHEET_VER - Version label of template
10 -- X_SS_BLOB - Blob containing template
11 -- X_SS_APPROVED - Is template approved Y or N
12 -- X_ERROR_CODE - Error code if XSLFO cannot be found
13 -- X_ERROR_MSG - Error message in case of any errors
14
15 PROCEDURE GET_XSLFO_TEMPLATE( p_style_sheet VARCHAR2,
16 p_style_sheet_ver VARCHAR2,
17 x_ss_blob out nocopy BLOB,
18 x_ss_approved out nocopy VARCHAR2,
19 x_ss_type out nocopy VARCHAR2,
20 x_error_code out nocopy VARCHAR2,
21 x_error_msg OUT NOCOPY VARCHAR2)
22 AS
23
24 l_status VARCHAR2(1) ;
25 l_fnd_document_id NUMBER ;
26 l_file_id_template NUMBER;
27 l_position number ;
28
29 BEGIN
30 --Bug 4074173 : start
31 l_status := 'F';
32 l_fnd_document_id := null;
33 l_file_id_template := null;
34 --Bug 4074173 : end
35
36 BEGIN
37 -- Bug 3461469 - Start - changed query criteria to file_name
38 SELECT
39 status , fnd_document_id, file_id
40 INTO
41 l_status , l_fnd_document_id, l_file_id_template
42 FROM
43 edr_files_b
44 WHERE
45 file_name = p_style_sheet
46 AND
47 version_label = p_style_sheet_ver
48 AND
49 attribute_category = 'EDR_EREC_TEMPLATE';
50 -- Bug 3461469 - End
51
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 x_error_msg := 'Required Template is not found';
55 x_error_code := '100';
56 RETURN;
57 END;
58
59 IF (l_status = 'A') THEN
60 x_ss_approved := 'Y';
61 ELSE
62 x_ss_approved := 'N';
63 END IF;
64
65
66 SELECT fnl.FILE_DATA into x_ss_blob
67 FROM
68 fnd_lobs fnl,
69 fnd_attached_documents fnddoc,
70 fnd_documents_vl fndt
71 WHERE fnddoc.ENTITY_NAME = 'EDR_XSLFO_TEMPLATE'
72 AND fnddoc.pk1_value = to_Char(l_file_id_template)
73 AND fnddoc.document_id = fndt.document_id
74 AND fndt.media_id = fnl.file_id
75 AND fnddoc.document_id =(select max(document_id)
76 from
77 fnd_attached_documents
78 where pk1_value = to_char(l_file_id_template)
79 and entity_name = 'EDR_XSLFO_TEMPLATE');
80
81 l_position := instr(p_style_sheet, '.xsl',1);
82
83 if(l_position > 0) then
84 x_ss_type := 'XSL';
85 else if (instr(p_style_sheet, '.rtf',1) > 0) then
86 x_ss_type := 'RTF';
87 else if (instr(p_style_sheet, '.pdf',1) >0 ) then
88 x_ss_type := 'PDF';
89 end if;
90 end if;
91 end if;
92
93 END GET_XSLFO_TEMPLATE;
94
95 -- EDR_ISIGN_TEMPLATE_PKG.GET_TEMPLATE procedure is called from TemplateManager Class
96 -- to get the handle of RTF Template Blob. It passes unique p_event_key which is equal
97 -- to file_id in ISIGN edr_files_b table.
98 --
99 -- P_EVENT_KEY - File Id for the ISIGN EDR_FILES_B Table repository
100 -- X_TEMPALTE_TYPE - Return template type as string e.g. RTF, XSL, PDF
101 -- X_TEMPLATE_BLOB - Return Template contents in BLOB
102 -- X_TEMPLATE_FILE_NAME - Return Template File Name as String
103
104 PROCEDURE GET_TEMPLATE (p_event_key VARCHAR2,
105 x_template_type OUT NOCOPY VARCHAR2,
106 x_template_blob OUT NOCOPY BLOB,
107 x_template_file_name OUT NOCOPY VARCHAR2
108 )
109 AS
110 l_file_name varchar2(256);
111 l_position number;
112 l_extension varchar2(10);
113 l_file_data BLOB;
114 l_display_name varchar2(256);
115 BEGIN
116 --Bug : 3499311 : Start - Specified number format in call TO_NUMBER
117 select fl.file_data, isign_files.ORIGINAL_FILE_NAME, isign_files.FILE_NAME
118 into l_file_data, l_file_name, l_display_name
119 from
120 fnd_lobs fl, fnd_documents_vl fdt, edr_files_b isign_files
121 where
122 isign_files.file_id = to_number(p_event_key,'999999999999.999999')
123 and fdt.document_id = isign_files.fnd_document_id
124 and fl.file_id = fdt.media_id;
125 --Bug : 3499311 : End
126
127 l_position := instr(l_file_name,'.',1);
128 l_extension := substr(l_file_name, l_position+1,3);
129
130 x_template_type := UPPER(l_extension);
131 x_template_blob := l_file_data;
132 x_template_file_name := l_display_name;
133
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 BEGIN
137 x_template_type := 'NOTFOUND';
138 x_template_blob := null;
139 x_template_file_name := 'NOTFOUND';
140 END;
141
142 END GET_TEMPLATE;
143
144 END EDR_ISIGN_TEMPLATE_PKG;