[Home] [Help]
PACKAGE BODY: APPS.PO_PROTESTS_UTIL
Source
1 PACKAGE BODY po_protests_util AS
2 /* $Header: PO_PROTESTS_UTIL.plb 120.0.12020000.3 2013/02/28 08:09:55 vpeddi noship $ */
3
4 PROCEDURE Store_zip_blob(p_protest_id NUMBER,
5 x_media_id OUT nocopy NUMBER)
6 IS
7 row_id_tmp VARCHAR2(100);
8 document_id_tmp NUMBER;
9 media_id_tmp NUMBER;
10 l_blob_data BLOB;
11 l_entity_name VARCHAR2(30);
12 seq_num NUMBER;
13 l_category_id NUMBER;
14 l_count NUMBER;
15 l_file_name fnd_lobs.file_name%TYPE;
16 l_file_content_type fnd_lobs.file_content_type%TYPE;
17 l_api_name CONSTANT VARCHAR2(25) := 'Store_Blob';
18 l_progress VARCHAR2(3);
19 p_blob_type VARCHAR2(4);
20 BEGIN
21 l_progress := '000';
22
23 p_blob_type := 'ZIP';
24
25 l_file_content_type := 'application/x-zip-compressed';
26
27 l_file_name := 'Attachments.zip';
28
29 l_progress := '010';
30
31 l_blob_data := Empty_blob();
32
33 l_count := 0;
34
35 l_progress := '020';
36
37 l_entity_name := 'PO_PROTESTS';
38
39 l_progress := '030';
40
41 BEGIN
42 SELECT media_id
43 INTO x_media_id
44 FROM fnd_documents_vl
45 WHERE document_id IN (SELECT document_id
46 FROM fnd_attached_documents
47 WHERE pk1_value = p_protest_id
48 AND entity_name = 'PO_PROTESTS'
49 AND category_id IN (SELECT category_id
50 FROM
51 fnd_document_categories_vl
52 WHERE name =
53 'CUSTOM5641'
54 ));
55 EXCEPTION
56 WHEN OTHERS THEN
57 -- do nothing. If no_data_found then x_media_id will be null and a new lob
58 -- locator will be inserted into fnd_lobs. If too_many_rows (though should
59 -- not occur because the locator gets overwritten everytime)
60 -- then the first blob found will be overwritten
61 NULL;
62 END;
63
64 l_progress := '040';
65
66 IF x_media_id IS NULL THEN
67 l_progress := '050';
68
69 --Get the Category Id of 'PO Documents' Category
70 SELECT category_id
71 INTO l_category_id
72 FROM fnd_document_categories_vl
73 WHERE name = 'CUSTOM5641';
74
75 l_progress := '006';
76
77 fnd_documents_pkg.Insert_row(row_id_tmp, document_id_tmp, SYSDATE, 1,
78 --NVL(X_created_by,0),
79 SYSDATE, 1, --NVL(X_created_by,0),
80 1, --X_last_update_login,
81 6, l_category_id, --Get the value for the category id 'Documents'
82 1, --null,--security_type,
83 NULL, --security_id,
84 'Y', --null,--publish_flag,
85 NULL, --image_type,
86 NULL, --storage_type,
87 'O', --usage_type,
88 SYSDATE, --start_date_active,
89 NULL, --end_date_active,
90 NULL, --X_request_id, --null
91 NULL, --X_program_application_id, --null
92 NULL, --X_program_id,--null
93 SYSDATE, NULL, --language,
94 NULL, --description,
95 l_file_name, x_media_id);
96
97 l_progress := '060';
98
99 INSERT INTO fnd_lobs
100 (file_id,
101 file_name,
102 file_content_type,
103 upload_date,
104 expiration_date,
105 program_name,
106 program_tag,
107 file_data,
108 LANGUAGE,
109 oracle_charset,
110 file_format)
111 VALUES ( x_media_id,
112 l_file_name,
113 l_file_content_type,
114 SYSDATE,
115 NULL,
116 NULL,
117 NULL,
118 l_blob_data,
119 NULL,
120 NULL,
121 'binary' );
122
123 l_progress := '070';
124
125 INSERT INTO fnd_attached_documents
126 (attached_document_id,
127 document_id,
128 creation_date,
129 created_by,
130 last_update_date,
131 last_updated_by,
132 last_update_login,
133 seq_num,
134 entity_name,
135 pk1_value,
136 pk2_value,
137 pk3_value,
138 pk4_value,
139 pk5_value,
140 automatically_added_flag,
141 program_application_id,
142 program_id,
143 program_update_date,
144 request_id,
145 attribute_category,
146 attribute1,
147 attribute2,
148 attribute3,
149 attribute4,
150 attribute5,
151 attribute6,
152 attribute7,
153 attribute8,
154 attribute9,
155 attribute10,
156 attribute11,
157 attribute12,
158 attribute13,
159 attribute14,
160 attribute15,
161 column1,
162 category_id)
163 VALUES ( fnd_attached_documents_s.NEXTVAL,
164 document_id_tmp,
165 SYSDATE,
166 1,
167 SYSDATE,
168 1,
169 NULL,
170 10,
171 l_entity_name,
172 To_char(p_protest_id),
173 NULL,
174 NULL,
175 NULL,
176 NULL,
177 'N',
178 NULL,
179 NULL,
180 SYSDATE,
181 NULL,
182 NULL,
183 NULL,
184 NULL,
185 NULL,
186 NULL,
187 NULL,
188 NULL,
189 NULL,
190 NULL,
191 NULL,
192 NULL,
193 NULL,
194 NULL,
195 NULL,
196 NULL,
197 NULL,
198 NULL,
199 l_category_id );
200
201 l_progress := '080';
202 END IF;
203 EXCEPTION
204 WHEN OTHERS THEN
205 RAISE;
206 END store_zip_blob;
207 PROCEDURE Zip_attach(document_id IN VARCHAR2,
208 content_type IN VARCHAR2,
209 document IN OUT nocopy BLOB,
210 document_type IN OUT nocopy VARCHAR2)
211 IS
212 l_filename fnd_lobs.file_name%TYPE;
213 l_document_id po_headers_all.po_header_id%TYPE;
214 l_document fnd_lobs.file_data%TYPE;
215 l_document_length NUMBER;
216 l_progress VARCHAR2(300);
217 l_filecontent_type fnd_lobs.file_content_type%TYPE;
218 l_message fnd_new_messages.message_text%TYPE;
219 BEGIN
220 l_progress := 'PO_PROTESTS_UTIL.zip_attach : Begin';
221
222 -- Docuement ID passed to this procedure is the file id in the fnd_lobs
223 l_document_id := document_id;
224
225 l_progress := 'PO_PROTESTS_UTIL.zip_attach : Get item attributes';
226
227 l_progress := 'PO_PROTESTS_UTIL.zip_attach : Query the Zip blob';
228
229 --Bug #4865352 - Added a join with fnd_documents and selected media_id from it
230 BEGIN
231 SELECT fl.file_data,
232 fl.file_content_type,
233 fl.file_name
234 INTO l_document, l_filecontent_type, l_filename
235 FROM fnd_lobs fl
236 WHERE fl.file_id = l_document_id;
237 EXCEPTION
238 WHEN OTHERS THEN
239 l_progress := 'PO_PROTESTS_UTIL.zip_attach : no_data_found';
240
241 RAISE;
242 END;
243
244 l_progress := 'PO_PROTESTS_UTIL.zip_attach : Get blob length';
245
246 l_document_length := dbms_lob.Getlength(l_document);
247
248 l_progress := 'PO_PROTESTS_UTIL.zip_attach : Copy zip blob';
249
250 dbms_lob.Copy(document, l_document, l_document_length, 1, 1);
251
252 l_progress := 'PO_PROTESTS_UTIL.zip_attach : Set document type';
253
254 document_type := l_filecontent_type
255 ||'; name='
256 || l_filename;
257
258 l_progress := 'PO_PROTESTS_UTIL.zip_attach : End';
259 EXCEPTION
260 WHEN OTHERS THEN
261 document_type := 'text/html; name='
262 || l_filename;
263
264 l_message := fnd_message.Get_string('PO', 'PO_ZIP_FAILED');
265
266 dbms_lob.WRITE(document, Lengthb(l_message), 1,
267 utl_raw.Cast_to_raw(l_message));
268 END zip_attach;
269 PROCEDURE Send(to_emp_user VARCHAR2,
270 from_user VARCHAR2,
271 notification_subject VARCHAR2,
272 file_id NUMBER,
273 protestnotes VARCHAR2,
274 protestid NUMBER)
275 IS
276 PRAGMA autonomous_transaction;
277 l_notification_id NUMBER;
278 BEGIN
279 -- DBMS_OUTPUT.PUT_LINE(to_emp_user);
280 -- DBMS_OUTPUT.PUT_LINE('1');
281 IF ( file_id = 0
282 OR file_id =- 999 ) THEN
283 l_notification_id := wf_notification.Send(ROLE => to_emp_user,
284 msg_type => 'POPRNT',
285 msg_name => 'PROTEST_NOTIF_WITHOUT_ATT'
286 );
287
288 wf_notification.Setattrtext(nid => l_notification_id,
289 aname => '#FROM_ROLE',
290 avalue => from_user);
291
292 wf_notification.Setattrtext(nid => l_notification_id,
293 aname =>'NOTIFICATION_SUBJECT',
294 avalue => notification_subject);
295 wf_notification.Setattrtext(nid => l_notification_id,
296 aname => 'NOTIFICATION_REGION',
297 avalue =>'JSP:/OA_HTML/OA.jsp?OAFunc=PROTEST_NOTIFICATION_REGION&protestId='|| protestid || '&mode=V&protestNotes='|| protestnotes);
298 ELSE
299 l_notification_id := wf_notification.Send(ROLE => to_emp_user,
300 msg_type => 'POPRNT',
301 msg_name => 'PROTEST_NOTIF'
302 );
303
304 wf_notification.Setattrtext(nid => l_notification_id,
305 aname => '#FROM_ROLE',
306 avalue => from_user);
307
308 wf_notification.Setattrtext(nid => l_notification_id,
309 aname =>'NOTIFICATION_SUBJECT',
310 avalue => notification_subject);
311
312 wf_notification.Setattrtext(nid => l_notification_id,
313 aname => 'NOTIFICATION_REGION',
314 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PROTEST_NOTIFICATION_REGION&protestId='|| protestid || '&mode=V&protestNotes='|| protestnotes);
315 wf_notification.Setattrtext(nid => l_notification_id,
316 aname => 'ZIP_ATTACHMENT',
317 avalue =>'PLSQLBLOB:PO_PROTESTS_UTIL.ZIP_ATTACH/'||file_id);
318 END IF;
319
320 COMMIT;
321 EXCEPTION
322 WHEN OTHERS THEN
323 --DBMS_OUTPUT.PUT_LINE('ERROR');
324 ROLLBACK;
325 END send;
326 PROCEDURE Send_notifications(performer_list PO_TBL_VARCHAR30,
327 from_user VARCHAR2,
328 notification_subject VARCHAR2,
329 file_id NUMBER,
330 protest_notes VARCHAR2,
331 protest_id NUMBER)
332 IS
333 PRAGMA autonomous_transaction;
334
335 l_adhocuser_lang VARCHAR2(100);
336 l_adhocuser_territory VARCHAR2(100);
337 l_po_email_performer VARCHAR2(100);
338 p_email_address VARCHAR2(100);
339 l_performer_exists NUMBER(3);
340 l_display_name VARCHAR2(100);
341 l_notification_preference VARCHAR2(20) := 'MAILHTM2';
342 l_user_exists NUMBER(3);
343 BEGIN
344 FOR i IN 1.. performer_list.count LOOP
345 --insert into test_tab_protest values (PERFORMER_LIST(i));
346 SELECT Count(1)
347 INTO l_user_exists
348 FROM wf_roles
349 WHERE name = Performer_list(i);
350
351 IF ( l_user_exists = 0 ) THEN
352 p_email_address := Performer_list(i);
353
354 SELECT wfl.nls_language,
355 wfl.nls_territory
356 INTO l_adhocuser_lang, l_adhocuser_territory
357 FROM wf_languages wfl,
358 fnd_languages_vl flv
359 WHERE wfl.code = flv.language_code
360 AND flv.installed_flag = 'B';
361
362 l_po_email_performer := p_email_address
363 || '.'
364 || l_adhocuser_lang;
365
366 l_po_email_performer := Upper(l_po_email_performer);
367
368 SELECT Count(1)
369 INTO l_performer_exists
370 FROM wf_roles
371 WHERE name = l_po_email_performer;
372
373 IF ( l_performer_exists = 0 ) THEN
374 wf_directory.Createadhocuser(l_po_email_performer, l_display_name,
375 l_adhocuser_lang,
376 l_adhocuser_territory,
377 NULL
378 , l_notification_preference, p_email_address, NULL, 'ACTIVE', NULL
379 );
380 END IF;
381 ELSE
382 l_po_email_performer := Performer_list(i);
383 END IF;
384 commit;
385
386 Send(l_po_email_performer, from_user, notification_subject, file_id,
387 protest_notes, protest_id);
388 END LOOP;
389 END send_notifications;
390 END po_protests_util;