[Home] [Help]
PACKAGE BODY: APPS.EDR_GENERAL_SUBS
Source
1 PACKAGE BODY EDR_GENERAL_SUBS AS
2 /* $Header: EDRGSUBB.pls 120.1.12000000.1 2007/01/18 05:53:57 appldev ship $ */
3
4 G_XSL_CATEGORY constant varchar2(30) := 'EDR_XSL_STYLESHEET';
5
6 --Bug # 3170251 : Added variable to store erecord template category name
7 G_TMP_CATEGORY constant varchar2(30) := 'EDR_EREC_TEMPLATE';
8
9
10 G_YES constant varchar2(25) := 'COMPLETE:Y';
11 G_NO constant varchar2(25) := 'COMPLETE:N';
12 G_XSL_EXTENSION CONSTANT varchar2(10) := 'XSL';
13
14 PROCEDURE UPLOAD_STYLESHEET(p_itemtype VARCHAR2,
15 p_itemkey VARCHAR2,
16 p_actid NUMBER,
17 p_funcmode VARCHAR2,
18 p_resultout OUT NOCOPY VARCHAR2)
19 AS
20
21 l_event_name varchar2(240);
22 l_event_key varchar2(240);
23
24 l_author VARCHAR2(100);
25 l_file_name VARCHAR2(300);
26 l_product VARCHAR2(50);
27 l_file_data BLOB;
28 l_file_data_c CLOB;
29 l_xsl_ret_code NUMBER;
30 l_xsl_ret_msg VARCHAR2(200);
31 l_upload_status VARCHAR2(300);
32 l_extension VARCHAR2(30);
33 l_return_status VARCHAR2(25);
34 l_category_name VARCHAR2(30);
35 l_event_status VARCHAR2(15);
36 l_buffer1 raw(2000);
37 l_buffer2 varchar2(2000);
38 l_data_length number;
39 l_block_size number;
40 l_kilo number;
41 l_chunk_size number;
42 l_offset number;
43 l_version varchar2(15);
44 l_version_num number(15,3);
45 l_db_xsl_version number(15,3);
46 l_pos number;
47 l_psig_event WF_EVENT_T;
48 l_upload varchar2(10);
49 l_no_approval_status VARCHAR2(20);
50 l_success_status VARCHAR2(20);
51 BEGIN
52 --Bug 4074173 : start
53 l_event_name :=WF_ENGINE.GETITEMATTRTEXT(
54 itemtype=>p_itemtype,
55 itemkey=>P_itemkey,
56 aname=>'EVENT_NAME');
57
58 l_event_key :=WF_ENGINE.GETITEMATTRTEXT(
59 itemtype=>p_itemtype,
60 itemkey=>P_itemkey,
61 aname=>'EVENT_KEY');
62
63 l_return_status := G_NO;
64 l_upload := 'TRUE';
65 l_no_approval_status := 'NO APPROVAL';
66 l_success_status := 'SUCCESS';
67 l_kilo := 1000;
68 l_offset := 1;
69 --Bug 4074173 : end
70
71 wf_log_pkg.string(6, 'UPLOAD_XSL','event name '||l_event_name);
72 wf_log_pkg.string(6, 'UPLOAD_XSL','event key '||l_event_key);
73
74 --start with checking that the file is a stylesheet
75 EDR_FILE_UTIL_PUB.GET_FILE_NAME(l_event_key, l_file_name);
76 wf_log_pkg.string(6, 'UPLOAD_XSL','file name'||l_file_name);
77
78 --Locate beginning of the extension in the file name string to use
79 --position to split up the file name
80 l_extension := NULL;
81 l_pos := INSTR(l_file_name, '.',-1,1);
82
83 IF l_pos <> 0 THEN
84 l_extension := SUBSTR(l_file_name,l_pos+1,LENGTH(l_file_name));
85 END IF;
86
87 wf_log_pkg.string(6, 'UPLOAD_XSL','extension '||l_extension);
88
89 --only if the extension of the file is xsl proceed further
90 if (UPPER(l_extension) = G_XSL_EXTENSION) then
91 --get the category of the file
92 EDR_FILE_UTIL_PUB.GET_CATEGORY_NAME(l_event_key, l_category_name);
93 wf_log_pkg.string(6, 'UPLOAD_XSL','category name '||l_category_name);
94
95 --Bug # 3170251 : check if category is either G_XSL_CATEGORY OR G_TMP_CATEGORY
96
97 if ((l_category_name = G_XSL_CATEGORY) OR (l_category_name = G_TMP_CATEGORY)) then
98 wf_log_pkg.string(6, 'UPLOAD_XSL','xsl category name found');
99 l_return_status := G_YES;
100 end if;
101 --Bug # 3170251 : End
102
103 end if;
104
105 --now that we have made certain that the file extension is xsl and the category
106 --is stylesheet go ahead and try to upload it
107 if (l_return_status = G_YES) then
108 wf_log_pkg.string(6, 'UPLOAD_XSL','return status is yes...starting upload');
109
110 --set the file author
111 EDR_FILE_UTIL_PUB.GET_AUTHOR_NAME(l_event_key, l_author);
112 wf_log_pkg.string(6, 'UPLOAD_XSL','author '||l_author);
113 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'AUTHOR', l_author);
114 wf_log_pkg.string(6, 'UPLOAD_XSL','author name set in the workflow');
115
116 --set the file name
117 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'XSL_NAME', l_file_name);
118 wf_log_pkg.string(6, 'UPLOAD_XSL','file name set in the workflow');
119
120 --set the version
121 EDR_FILE_UTIL_PUB.GET_VERSION_LABEL(l_event_key, l_version);
122 l_version_num := l_version;
123 wf_log_pkg.string(6, 'UPLOAD_XSL','version num '||l_version_num);
124 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'VERSION', l_version);
125
126
127 --set the product name of the file owner product
128 EDR_FILE_UTIL_PUB.GET_ATTRIBUTE(l_event_key, 'ATTRIBUTE1', l_product);
129 wf_log_pkg.string(6, 'UPLOAD_XSL','PRODUCT ' ||l_product);
130
131 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'PRODUCT', l_product);
132
133 --convert to lower case for upload to ecx repository
134 l_product := LOWER(l_product);
135
136 l_event_status := wf_engine.getitemattrtext(p_itemtype,p_itemkey,'FILE_STATUS');
137 wf_log_pkg.string(6, 'UPLOAD_XSL','event status' ||l_event_status);
138
139
140 --if the status is SUCCESS only then upload it to the database
141 --Bug 3161353: Start
142 if (l_event_status = l_success_status OR l_event_status = l_no_approval_status) then
143 --if (l_event_status = 'SUCCESS') then
144 --Bug 3161353: End
145 EDR_FILE_UTIL_PUB.GET_FILE_DATA(l_event_key, l_file_data);
146
147 wf_log_pkg.string(6, 'UPLOAD_XSL','file size: '||dbms_lob.getlength(l_file_data));
148
149 --convert the blob data to clob
150 dbms_lob.createtemporary(l_file_data_c,TRUE);
151 l_data_length := dbms_lob.getlength(l_file_data);
152 l_block_size := ceil(l_data_length/l_kilo);
153 for j in 1..l_block_size
154 loop
155 if (l_kilo*j <= l_data_length) then
156 l_chunk_size :=l_kilo;
157 else
158 l_chunk_size :=l_data_length - l_kilo *(j-1);
159 end if;
160 dbms_lob.read(l_file_data, l_chunk_size, l_offset, l_buffer1);
161 l_buffer2 := utl_raw.cast_to_varchar2(l_buffer1);
162 dbms_lob.writeappend(l_file_data_c, l_chunk_size,l_buffer2);
163 l_offset := l_offset + l_kilo;
164 end loop;
165
166 --get the existing version number for the file
167 --if the version in the DB is >= version in EDR table
168 --then dont upload
169
170 select NVL(max(version),0) into l_db_xsl_version
171 from ecx_files
172 where name = l_file_name;
173
174 if (l_db_xsl_version > 0) then
175 if (l_db_xsl_version >= l_version_num) then
176 wf_log_pkg.string(6, 'UPLOAD_XSL','db version '||l_db_xsl_version);
177 l_upload_status := fnd_message.get_string('EDR','EDR_FILES_BAD_XSL_VERSION3');
178 l_upload := 'FALSE';
179 end if;
180 end if;
181
182 if (l_upload = 'TRUE') then
183 wf_log_pkg.string(6, 'UPLOAD_XSL','version '||l_version);
184
185 --call ecx api to upload xsl
186 ECX_XSLT_UTILS.INS(i_filename => l_file_name,
187 i_version => l_version,
188 i_application_code => l_product,
189 i_payload => l_file_data_c,
190 i_retcode => l_xsl_ret_code,
191 i_retmsg => l_xsl_ret_msg);
192 if (l_xsl_ret_code = 0) then
193
194 l_upload_status := fnd_message.get_string('EDR','EDR_FILES_XSL_SUCCESS');
195 else
196 l_upload_status := fnd_message.get_string('EDR','EDR_FILES_XSL_ECX_FAILURE')||l_xsl_ret_msg;
197 end if;
198
199 end if;
200
201 elsif (l_event_status = 'REJECTED') then
202 l_upload_status := fnd_message.get_string('EDR','EDR_FILES_APPROVAL_REJECTION');
203 else
204 l_upload_status := fnd_message.get_string('EDR','EDR_FILES_XSL_FAILURE');
205 end if;
206 end if;
207
208 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'UPLOAD_STATUS', l_upload_status);
209
210 p_resultout := l_return_status;
211 END UPLOAD_STYLESHEET;
212
213 END EDR_GENERAL_SUBS;