1 PACKAGE BODY gma_standard AS
2 /* $Header: GMASTNDB.pls 120.2 2005/12/15 11:10:25 txdaniel noship $ */
3
4 counter pls_integer;
5 v_eventQuery EDR_STANDARD.eventQuery;
6 v_ameRuleinputvalues EDR_STANDARD.ameRuleinputvalues;
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EDR_FILES_PUB';
9
10 PROCEDURE PSIG_STATUS
11 (
12 p_event in varchar2,
13 p_event_key in varchar2,
14 P_status out NOCOPY varchar2
15 ) IS
16 BEGIN
17 edr_standard.psig_status(p_event, p_event_key, p_status);
18 END PSIG_STATUS;
19
20 /* signature Requirement. This Procedure returns signature requireemnt for a given event.
21 The status is 'Yes' */
22 PROCEDURE PSIG_REQUIRED
23 (
24 p_event in varchar2,
25 p_event_key in varchar2,
26 P_status out NOCOPY boolean
27 ) is
28 BEGIN
29 edr_standard.psig_required(p_event, p_event_key, p_status);
30 END PSIG_REQUIRED;
31
32 /* eRecord Requirement. This Procedure returns signature requirement for a given event.
33 The status is 'true' or 'false' */
34 PROCEDURE EREC_REQUIRED
35 (
36 p_event in varchar2,
37 p_event_key in varchar2,
38 P_status out NOCOPY boolean
39 ) is
40 BEGIN
41 edr_standard.erec_required(p_event, p_event_key, p_status);
42 END EREC_REQUIRED;
43
44 FUNCTION PSIG_QUERY(p_eventQuery GMA_STANDARD.eventQuery) return number IS PRAGMA AUTONOMOUS_TRANSACTION;
45 i number;
46 begin
47 v_eventQuery.DELETE; -- Added for bug 4874228
48 for counter IN 1..p_eventQuery.COUNT loop
49 v_eventQuery(counter).event_name := p_eventQuery(counter).event_name;
50 v_eventQuery(counter).event_key := p_eventQuery(counter).event_key;
51 v_eventQuery(counter).key_type := p_eventQuery(counter).key_type;
52 end loop;
53 i := edr_standard.psig_query(v_eventQuery);
54 return i;
55 END PSIG_QUERY;
56
57 PROCEDURE GET_AMERULE_INPUT_VALUES( ameapplication IN varchar2,
58 ameruleid IN NUMBER,
59 amerulename IN VARCHAR2,
60 ameruleinputvalues OUT NOCOPY GMA_STANDARD.ameruleinputvalues) is
61 BEGIN
62 for counter IN 1..ameruleinputvalues.COUNT loop
63 v_ameRuleinputvalues(counter).input_name := ameruleinputvalues(counter).input_name;
64 v_ameRuleinputvalues(counter).input_value := ameruleinputvalues(counter).input_value;
65 end loop;
66
67 edr_standard.get_amerule_input_values(ameapplication, ameruleid, amerulename, v_ameRuleinputvalues);
68
69 for counter IN 1..ameruleinputvalues.COUNT loop
70 v_ameRuleinputvalues(counter).input_name := ameruleinputvalues(counter).input_name;
71 v_ameRuleinputvalues(counter).input_value := ameruleinputvalues(counter).input_value;
72 end loop;
73 END GET_AMERULE_INPUT_VALUES;
74
75 PROCEDURE DISPLAY_DATE(P_DATE_IN in DATE , P_DATE_OUT OUT NOCOPY Varchar2) IS
76 BEGIN
77 edr_standard.display_date(p_date_in, p_date_out);
78 END DISPLAY_DATE;
79
80 PROCEDURE UPLOAD_FILE( p_api_version IN NUMBER,
81 p_commit IN VARCHAR2,
82 p_called_from_forms IN VARCHAR2,
83 p_file_name IN VARCHAR2,
84 p_category IN VARCHAR2,
85 p_content_type IN VARCHAR2,
86 p_version_label IN VARCHAR2,
87 p_file_data IN BLOB,
88 p_file_format IN VARCHAR2,
89 p_source_lang IN VARCHAR2,
90 p_description IN VARCHAR2,
91 p_file_exists_action IN VARCHAR2,
92 p_submit_for_approval IN VARCHAR2,
93 p_attribute1 IN VARCHAR2,
94 p_attribute2 IN VARCHAR2,
95 p_attribute3 IN VARCHAR2,
96 p_attribute4 IN VARCHAR2,
97 p_attribute5 IN VARCHAR2,
98 p_attribute6 IN VARCHAR2,
99 p_attribute7 IN VARCHAR2,
100 p_attribute8 IN VARCHAR2,
101 p_attribute9 IN VARCHAR2,
102 p_attribute10 IN VARCHAR2,
103 p_created_by IN NUMBER,
104 p_creation_date IN DATE,
105 p_last_updated_by IN NUMBER,
106 p_last_update_login IN NUMBER,
107 p_last_update_date IN DATE,
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_msg_data OUT NOCOPY VARCHAR2)
110 AS
111
112 /* Alpha Variables */
113 L_API_NAME CONSTANT VARCHAR2(30) := 'UPLOAD_FILE';
114 L_FILE_NAME VARCHAR2(2000);
115 L_EXTENSION VARCHAR2(2000);
116 L_RETURN_STATUS VARCHAR2(1);
117 L_CONCAT_SEGS VARCHAR2(200);
118 L_STATUS VARCHAR2(30);
119 L_EVENT_USER_KEY VARCHAR2(2000);
120 L_USER VARCHAR2(240);
121 L_MSG_DATA VARCHAR2(200);
122 L_FILE_DATA BLOB;
123
124 L_SEG_ARRAY FND_FLEX_EXT.SEGMENTARRAY;
125
126 /* Numeric Variables */
127 L_COUNT NUMBER;
128 L_ORACLE_ERROR NUMBER;
129 L_API_VERSION CONSTANT NUMBER := 1.0;
130 L_VERSION NUMBER;
131 L_POS NUMBER;
132 L_RESP_ID NUMBER;
133 L_RESP_APPL_ID NUMBER;
134 L_ROW_ID VARCHAR2(240);
135 L_FILE_ID NUMBER;
136 L_CATEGORY_ID NUMBER;
137 L_FND_DOCUMENT_ID NUMBER;
138 L_ATTACHED_DOCUMENT_ID NUMBER;
139 X_DOCUMENT_ID NUMBER;
140 L_MEDIA_ID NUMBER;
141
142 I INTEGER;
143
144 /* Exceptions */
145 EDR_FILES_FILE_NAME_NULL EXCEPTION;
146 EDR_FILES_CATEGORY_NULL EXCEPTION;
147 EDR_FILES_FILE_NULL EXCEPTION;
148 EDR_FILES_FORMAT_NULL EXCEPTION;
149 EDR_FILES_SRC_LANG_NULL EXCEPTION;
150 EDR_FILES_EXIST_ACT_NULL EXCEPTION;
151 EDR_FILES_ERES_ERROR EXCEPTION;
152 EDR_FILES_INSERT_ERROR EXCEPTION;
153 EDR_FILES_INV_FLEX_VALUE EXCEPTION;
154 EDR_FILES_ALREADY_EXISTS EXCEPTION;
155 EDR_FILES_OVERWRITE_ERROR EXCEPTION;
156 EDR_FILES_APPL_ID_NULL EXCEPTION;
157 EDR_FILES_RESP_ID_NULL EXCEPTION;
158 EDR_FILES_USER_NULL EXCEPTION;
159
160 /* Cursors */
161 CURSOR c_get_category IS
162 SELECT category_id
163 FROM fnd_document_categories
164 WHERE name = p_category;
165 LocalCatRecord c_get_category%ROWTYPE;
166
167 CURSOR c_get_exists IS
168 SELECT version_number, status
169 FROM edr_files_b
170 WHERE original_file_name = p_file_name
171 AND category_id = LocalCatRecord.category_id;
172 LocalExistsRecord c_get_exists%ROWTYPE;
173
174 CURSOR c_get_seq_num IS
175 SELECT edr_files_b_s.nextval
176 FROM dual;
177 LocalSeqRecord c_get_seq_num%ROWTYPE;
178
179 CURSOR c_get_event IS
180 SELECT a.file_name, b.name
181 FROM edr_files_b a, fnd_document_categories b
182 WHERE a.category_id = b.category_id
183 AND a.file_id = l_file_id;
184 LocalEventRecord c_get_event%ROWTYPE;
185
186 /* Used to get the next attached_document_id for a new attachment SKARIMIS*/
187 CURSOR c_get_id IS
188 SELECT fnd_attached_documents_s.nextval
189 FROM dual;
190
191 /* Used to check if the attribute is required in the flexfield */
192 CURSOR c_get_flex1 IS
193 SELECT count(*)
194 FROM fnd_descr_flex_column_usages
195 WHERE application_id = '709'
196 AND descriptive_flexfield_name = 'EDR_FILE_ATTRIBUTES'
197 AND descriptive_flex_context_code = p_category;
198 LocalFlex1Record c_get_flex1%ROWTYPE;
199
200 BEGIN
201 /* Get ids */
202 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
203 l_resp_id := FND_GLOBAL.RESP_ID;
204 l_user := FND_GLOBAL.USER_NAME;
205
206 IF l_resp_appl_id IS NULL THEN
207 RAISE EDR_FILES_APPL_ID_NULL;
208 END IF;
209
210 IF l_resp_id IS NULL THEN
211 RAISE EDR_FILES_RESP_ID_NULL;
212 END IF;
213
214 IF l_user IS NULL THEN
215 RAISE EDR_FILES_USER_NULL;
216 END IF;
217
218 /* Check the API version passed in matches the
219 ** internal API version.
220 */
221
222 IF NOT FND_API.Compatible_API_Call
223 (g_api_version,
224 p_api_version,
225 l_api_name,
226 g_pkg_name) THEN
227
228 RAISE FND_API.G_Exc_Unexpected_Error;
229 END IF;
230
231 /* Check for required parameters */
232 IF p_file_name IS NULL THEN
233 RAISE EDR_FILES_FILE_NAME_NULL;
234 END IF;
235
236 IF p_category IS NULL THEN
237 RAISE EDR_FILES_CATEGORY_NULL;
238 END IF;
239
240 IF p_file_data IS NULL THEN
241 RAISE EDR_FILES_FILE_NULL;
242 END IF;
243
244 IF p_file_format IS NULL THEN
245 RAISE EDR_FILES_FORMAT_NULL;
246 END IF;
247
248 IF p_source_lang IS NULL THEN
249 RAISE EDR_FILES_SRC_LANG_NULL;
250 END IF;
251
252 IF p_file_exists_action IS NULL THEN
253 RAISE EDR_FILES_EXIST_ACT_NULL;
254 END IF;
255
256 /* Default the internal version in case the document does not already exist */
257 l_version := 0;
258
259 /* The status is always 'N'(Not Approved)*/
260 l_status := 'N';
261
262 /* Select the category id */
263 OPEN c_get_category;
264 FETCH c_get_category INTO LocalCatRecord;
265
266 l_category_id := LocalCatRecord.category_id;
267 CLOSE c_get_category;
268
269 /* Check to see if file already exists */
270 OPEN c_get_exists;
271 FETCH c_get_exists INTO LocalExistsRecord;
272
273 /* File already exists and not be overwritten, return error */
274 IF c_get_exists%FOUND THEN
275 l_version := LocalExistsRecord.version_number;
276 IF p_file_exists_action = 'D' THEN
277 RAISE EDR_FILES_ALREADY_EXISTS;
278 END IF;
279 END IF;
280
281 /* File exists as approved, pending or approval not required, and not to be versioned, return error */
282 IF c_get_exists%FOUND THEN
283 l_version := LocalExistsRecord.version_number;
284 IF p_file_exists_action = 'O' AND LocalExistsRecord.status in ('A', 'P', 'S') THEN
285 RAISE EDR_FILES_OVERWRITE_ERROR;
286 END IF;
287 END IF;
288 CLOSE c_get_exists;
289
290 /* Validate flexfield values */
291 FND_FLEX_DESCVAL.set_context_value(p_category);
292
293 i := 0;
294 IF p_category IS NOT NULL THEN
295 i := i +1;
296 l_seg_array(i) := p_category;
297 END IF;
298
299 OPEN c_get_flex1;
300 FETCH c_get_flex1 INTO l_count;
301 IF c_get_flex1%FOUND THEN
302 IF l_count >= i THEN
303 i := i + 1;
304 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
305 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1);
306 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
307 END IF;
308 IF l_count >= i THEN
309 i := i + 1;
310 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
311 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2);
312 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
313 END IF;
314 IF l_count >= i THEN
315 i := i + 1;
316 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
317 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3);
318 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
319 END IF;
320 IF l_count >= i THEN
321 i := i + 1;
322 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
323 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4);
324 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
325 END IF;
326 IF l_count >= i THEN
327 i := i + 1;
328 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
329 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5);
330 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
331 END IF;
332 IF l_count >= i THEN
333 i := i + 1;
334 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
335 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6);
336 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
337 END IF;
338 IF l_count >= i THEN
339 i := i + 1;
340 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
341 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7);
342 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
343 END IF;
344 IF l_count >= i THEN
345 i := i + 1;
346 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
347 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8);
348 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
349 END IF;
350 IF l_count >= i THEN
351 i := i + 1;
352 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
353 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9);
354 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
355 END IF;
356 IF l_count >= i THEN
357 i := i + 1;
358 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield columns */
359 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10);
360 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
361 END IF;
362 END IF;
363 CLOSE c_get_flex1;
364
365 /* 25 Aug 2003 Bug 3103346 Mercy Thomas Added the following code to validate the descriptive flexfield */
366
367 IF NOT (FND_FLEX_DESCVAL.validate_desccols('EDR', 'EDR_FILE_ATTRIBUTES',
368 'V', SYSDATE)) THEN
369 RAISE EDR_FILES_INV_FLEX_VALUE;
370 END IF;
371 /* 25 Aug 2003 Bug 3103346 Mercy Thomas End of the code changes */
372
373
374 /* Increment internal version number */
375 l_version := l_version + 1;
376
377 /* Locate beginning of the extension in the file name string to use position to
378 split up the file name
379 */
380 l_extension := NULL;
381 l_file_name := p_file_name;
382 l_pos := INSTR(p_file_name, '.',-1,1);
383
384 IF l_pos <> 0 THEN
385 l_extension := SUBSTR(l_file_name,l_pos,LENGTH(l_file_name));
386 l_file_name := SUBSTR(p_file_name,1,l_pos-1);
387 END IF;
388
389 /* Set up the display name for the attachment */
390 l_file_name := l_file_name ||'_v_'||p_version_label||l_extension;
391
392 /* Get the next sequence number for file_id */
393 /* Fixed SKARIMIS */
394 SELECT edr_files_b_s.nextval INTO l_file_id FROM dual;
395
396 /* Call private package to insert data into the tables */
397 --cj the whole idea is that instead of inserting one row in edr_files_b/tl tables
398 -- we would have to insert a row in:
399 -- edr_files_b/tl
400 -- fnd_documents
401 --fnd_lobs
402 --fnd_attached_documents (attach the file to a default enity named EDR_FILES_B
403
404 --CJ look at the new definition of this package
405 --it would not have the BLOB col FILE_DATA
406 --change this call accordingly
407
408 EDR_FILES_PKG.Insert_Row (
409 X_ROWID =>l_row_id,
410 X_FILE_ID =>l_file_id,
411 X_FILE_NAME =>l_file_name,
412 X_ORIGINAL_FILE_NAME =>p_file_name,
413 X_VERSION_LABEL =>p_version_label,
414 X_CATEGORY_ID =>l_category_id,
415 X_CONTENT_TYPE =>p_content_type,
416 X_FILE_FORMAT =>p_file_format,
417 X_STATUS =>l_status,
418 X_VERSION_NUMBER =>l_version,
419 X_FND_DOCUMENT_ID => NULL,
420 X_ATTRIBUTE_CATEGORY =>p_category,
421 X_ATTRIBUTE1=>p_attribute1,
422 X_ATTRIBUTE2=>p_attribute2,
423 X_ATTRIBUTE3=>p_attribute3,
424 X_ATTRIBUTE4=>p_attribute4,
425 X_ATTRIBUTE5 =>p_attribute5,
426 X_ATTRIBUTE6=>p_attribute6,
427 X_ATTRIBUTE7=>p_attribute7,
428 X_ATTRIBUTE8=>p_attribute8,
429 X_ATTRIBUTE9=>p_attribute9,
430 X_ATTRIBUTE10=>p_attribute10,
431 X_ATTRIBUTE11=>NULL,
432 X_ATTRIBUTE12=>NULL,
433 X_ATTRIBUTE13=>NULL,
434 X_ATTRIBUTE14=>NULL,
435 X_ATTRIBUTE15=>NULL,
436 X_DESCRIPTION =>p_description,
437 X_CREATION_DATE=>p_creation_date,
438 X_CREATED_BY =>p_created_by,
439 X_LAST_UPDATE_DATE=>p_last_update_date,
440 X_LAST_UPDATED_BY=>p_last_updated_by,
441 X_LAST_UPDATE_LOGIN =>p_last_update_login);
442
443 --CJ now that the row is inserted in edr_files table
444 -- we have insert into fn_documents, fnd_attached_documents
445 -- and fnd_lobs
446
447 --CJ to insert in fnd_documents and fnd_attached_dcouments
448 --use FND_ATTACHED_DOCUMENTS_PKG.Insert_Row
449 -- i am attaching the java code to help u understand
450 -- the parameters to be passed
451
452 OPEN c_get_id;
453 FETCH c_get_id INTO l_attached_document_id;
454 CLOSE c_get_id;
455
456 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(X_Rowid=>l_row_id,
457 X_attached_document_id=>l_attached_document_id,
458 X_document_id=>x_document_id,
459 X_creation_date=>p_creation_date,
460 X_created_by=>p_created_by,
461 X_last_update_date=>p_last_update_date,
462 X_last_updated_by=>p_last_updated_by,
463 X_last_update_login=>p_last_update_login,
464 X_seq_num=>1,
465 X_entity_name=>'EDR_FILES_B',
466 X_column1=>NULL,
467 X_pk1_value=>NULL,
468 X_pk2_value=>NULL,
469 X_pk3_value=>NULL,
470 X_pk4_value=>NULL,
471 X_pk5_value=>NULL,
472 X_automatically_added_flag=>'N',
473 X_datatype_id=>6,
474 X_category_id=>l_category_id,
475 X_security_type=>1,
476 X_security_id=>-1,
477 X_publish_flag=>'N',
478 X_storage_type=>1,
479 X_usage_type=>'S',
480 X_language=>p_source_lang,
481 X_description=>p_description,
482 X_file_name=>l_file_name,
483 X_media_id=>l_media_id,
484 X_doc_attribute_category=>p_category,
485 X_doc_attribute1=>p_attribute1,
486 X_doc_attribute2=>p_attribute2,
487 X_doc_attribute3=>p_attribute3,
488 X_doc_attribute4=>p_attribute4,
489 X_doc_attribute5=>p_attribute5,
490 X_doc_attribute6=>p_attribute6,
491 X_doc_attribute7=>p_attribute7,
492 X_doc_attribute8=>p_attribute8,
493 X_doc_attribute9=>p_attribute9,
494 X_doc_attribute10=>p_attribute10,
495 X_create_doc=>'N');
496
497 -- use a direct INSERT statement to insert
498 -- into FND_LOBS
499 -- i am attaching the java code for inserting into this table
500 --just to show u what the values of the parameters should be
501 INSERT into FND_LOBS
502 (file_id,
503 file_name,
504 file_data,
505 file_content_type,
506 file_format)
507 VALUES
508 (l_media_id,
509 l_file_name,
510 l_file_data,
511 p_content_type,
512 p_file_format);
513
514 --CJ now update the row in the edr_files_b table with the fnd_document_id that you get
515 -- from inserting a row in FND_DOCUMENTS table
516
517 UPDATE EDR_FILES_B
518 SET fnd_document_id = x_document_id
519 WHERE file_id = l_file_id;
520
521 /* If sending for approval, raise ERES event */
522
523 IF p_submit_for_approval = 'Y' THEN
524 OPEN c_get_event;
525 FETCH c_get_event INTO LocalEventRecord;
526 l_event_user_key := LocalEventRecord.file_name ||'-'||LocalEventRecord.name;
527 CLOSE c_get_event;
528
529 -- cj change this api to edr_raise_event when its available
530
531 wf_event.raise2(p_event_name => 'oracle.apps.edr.file.approve',
532 p_event_key => l_file_id,
533 p_event_data => null,
534 p_parameter_name1 => 'DEFERRED',
535 p_parameter_value1 => 'Y',
536 p_parameter_name2 => 'POST_OPERATION_API',
537 p_parameter_value2 => 'EDR_ATTACHMENTS_GRP.EVENT_POST_OP('||l_file_id||')',
538 p_parameter_name3 => 'PSIG_USER_KEY_LABEL',
539 p_parameter_value3 => 'File Approval:',
540 p_parameter_name4 => 'PSIG_USER_KEY_VALUE',
541 p_parameter_value4 => l_event_user_key,
542 p_parameter_name5 => 'PSIG_TRANSACTION_AUDIT_ID',
543 p_parameter_value5 => '-1',
544 p_parameter_name6 => '#WF_SOURCE_APPLICATION_TYPE',
545 p_parameter_value6 => 'DB',
546 p_parameter_name7 => '#WF_SIGN_REQUESTER',
547 p_parameter_value7 => l_user);
548
549
550 EDR_STANDARD.PSIG_STATUS
551 ( p_event => 'oracle.apps.edr.file.approve' ,
552 p_event_key => l_file_id ,
553 p_status => l_status
554 );
555
556 -- If the status of the erecord in the evidence store is COMPLETE
557 -- it means that signature was not required.
558 -- if its PENDING means that the offine notification is sent out
559 -- to the approver and its waiting approval
560 -- in any other case raise an error
561
562 if l_status = 'PENDING' then
563 UPDATE edr_files_b
564 SET status = 'P'
565 WHERE file_id = l_file_id
566 AND file_name = LocalEventRecord.file_name;
567 elsif l_status = 'COMPLETE' then
568 UPDATE edr_files_b
569 SET status = 'S'
570 WHERE file_id = l_file_id
571 AND file_name = LocalEventRecord.file_name;
572 else
573 RAISE EDR_FILES_ERES_ERROR;
574 ROLLBACK;
575 END IF;
576
577 END IF;
578
579 /*IF everything is successful and commit is yes, issue a commit*/
580 IF p_commit = 'T' THEN
581 COMMIT;
582 END IF;
583
584 EXCEPTION
585 /*
586 **
587 */
588 WHEN EDR_FILES_FILE_NAME_NULL THEN
589 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_FILE_NAME_NULL');
590 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
591 FND_MSG_PUB.ADD;
592 APP_EXCEPTION.RAISE_EXCEPTION;
593 x_return_status := FND_API.G_RET_STS_ERROR;
594
595 WHEN EDR_FILES_APPL_ID_NULL THEN
596 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_APPL_ID_NULL');
597 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
598 FND_MSG_PUB.ADD;
599 APP_EXCEPTION.RAISE_EXCEPTION;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601
602 WHEN EDR_FILES_RESP_ID_NULL THEN
603 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_RESP_ID_NULL');
604 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
605 FND_MSG_PUB.ADD;
606 APP_EXCEPTION.RAISE_EXCEPTION;
607 x_return_status := FND_API.G_RET_STS_ERROR;
608
609 WHEN EDR_FILES_USER_NULL THEN
610 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_USER_NULL');
611 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
612 FND_MSG_PUB.ADD;
613 APP_EXCEPTION.RAISE_EXCEPTION;
614 x_return_status := FND_API.G_RET_STS_ERROR;
615
616 WHEN EDR_FILES_CATEGORY_NULL THEN
617 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_CATEGORY_NULL');
618 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
619 FND_MSG_PUB.ADD;
620 APP_EXCEPTION.RAISE_EXCEPTION;
621 x_return_status := FND_API.G_RET_STS_ERROR;
622
623 WHEN EDR_FILES_FILE_NULL THEN
624 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_FILE_NULL');
625 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
626 FND_MSG_PUB.ADD;
627 APP_EXCEPTION.RAISE_EXCEPTION;
628 x_return_status := FND_API.G_RET_STS_ERROR;
629
630 WHEN EDR_FILES_FORMAT_NULL THEN
631 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_FORMAT_NULL');
632 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
633 FND_MSG_PUB.ADD;
634 APP_EXCEPTION.RAISE_EXCEPTION;
635 x_return_status := FND_API.G_RET_STS_ERROR;
636
637 WHEN EDR_FILES_SRC_LANG_NULL THEN
638 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_SRC_LANG_NULL');
639 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
640 FND_MSG_PUB.ADD;
641 APP_EXCEPTION.RAISE_EXCEPTION;
642 x_return_status := FND_API.G_RET_STS_ERROR;
643
644 WHEN EDR_FILES_EXIST_ACT_NULL THEN
645 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_EXISTS_ACT_NULL');
646 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
647 FND_MSG_PUB.ADD;
648 APP_EXCEPTION.RAISE_EXCEPTION;
649 x_return_status := FND_API.G_RET_STS_ERROR;
650
651 WHEN EDR_FILES_ERES_ERROR THEN
652 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_ERES_ERROR');
653 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
654 FND_MSG_PUB.ADD;
655 APP_EXCEPTION.RAISE_EXCEPTION;
656 x_return_status := FND_API.G_RET_STS_ERROR;
657
658 WHEN EDR_FILES_INSERT_ERROR THEN
659 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_INSERT_ERROR');
660 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
661 FND_MSG_PUB.ADD;
662 APP_EXCEPTION.RAISE_EXCEPTION;
663 x_return_status := FND_API.G_RET_STS_ERROR;
664
665 WHEN EDR_FILES_INV_FLEX_VALUE THEN
666 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_INV_FLEX_VALUE');
667 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
668 FND_MSG_PUB.ADD;
669 APP_EXCEPTION.RAISE_EXCEPTION;
670 x_return_status := FND_API.G_RET_STS_ERROR;
671
672 WHEN EDR_FILES_ALREADY_EXISTS THEN
673 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_ALREADY_EXISTS');
674 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
675 FND_MSG_PUB.ADD;
676 APP_EXCEPTION.RAISE_EXCEPTION;
677 x_return_status := FND_API.G_RET_STS_ERROR;
678
679 WHEN EDR_FILES_OVERWRITE_ERROR THEN
680 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_OVERWRITE_ERROR');
681 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
682 FND_MSG_PUB.ADD;
683 APP_EXCEPTION.RAISE_EXCEPTION;
684 x_return_status := FND_API.G_RET_STS_ERROR;
685
686 WHEN OTHERS THEN
687 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 l_msg_data := 'Error = '||SQLERRM;
689
690 END UPLOAD_FILE;
691
692 /* Added for Melanie Grosser as a fix for bug# 3280763 */
693 /*===========================================================================
694 -- FUNCTION:
695 -- build_eres_query
696 --
697 -- DESCRIPTION:
698 -- This PL/SQL procedure is used to build an ERES query based upon document
699 -- attachments for a given entity. It will return the ID of the query that
700 -- was built so that the calling program may execute it.
701 --
702 -- PARAMETERS:
703 -- p_entity_name IN VARCHAR2 - Entity that attachments are associated with
704 -- p_pk1_value IN VARCHAR2 - Primary key value 1 ifor entity
705 -- p_pk2_value IN VARCHAR2 - Primary key value 2 ifor entity (may be null)
706 -- p_pk3_value IN VARCHAR2 - Primary key value 3 ifor entity (may be null)
707 -- p_pk4_value IN VARCHAR2 - Primary key value 4 ifor entity (may ne null)
708 -- p_pk5_value IN VARCHAR2 - Primary key value 5 ifor entity (may be null)
709 -- x_error_message OUT VARCHAR2 - If there is an error, send back the approriate message
710 -- x_return_status OUT VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
711 --
712 -- SYNOPSIS:
713 -- l_query_id := GMA_STANDARD.build_eres_query('GR_ITEM_GENERAL',l_item_code,NULL,NULL,NULL,
714 -- NULL,NULL,l_err_message,l_return_status);
715 -- IF (l_query_id > 0 ) THEN
716 -- GMA_WF_SIGNi.show_trans_query(p_query_id => l_query_id);
717 -- END IF;
718 --
719 -- HISTORY
720 -- M. Grosser 18-Nov-2003 Created
721 -- R. Tardio 25-Nov-2003 Added to GMA_STANDARD.
722 --=========================================================================== */
723 FUNCTION build_eres_query (p_entity_name IN VARCHAR2,
724 p_pk1_value IN VARCHAR2,
725 p_pk2_value IN VARCHAR2,
726 p_pk3_value IN VARCHAR2,
727 p_pk4_value IN VARCHAR2,
728 p_pk5_value IN VARCHAR2,
729 x_error_message OUT NOCOPY VARCHAR2,
730 x_return_status OUT NOCOPY VARCHAR2
731 ) RETURN NUMBER IS
732
733
734 /* ------------- LOCAL VARIABLES ------------------- */
735 l_return_status VARCHAR2(2) := 'S';
736 l_oracle_error NUMBER;
737 l_query_id NUMBER;
738 l_event_key VARCHAR2(240);
739 i NUMBER :=1;
740 x_event_group GMA_STANDARD.eventQuery;
741
742 /* ------------------ CURSORS ---------------------- */
743 CURSOR c_get_event_key IS
744 SELECT file_id
745 FROM fnd_attached_documents f, edr_files_b e
746 WHERE entity_name = p_entity_name
747 AND pk1_value = p_pk1_value
748 AND NVL(pk2_value,' ') = NVL(p_pk2_value,' ')
749 AND NVL(pk3_value,' ') = NVL(p_pk3_value,' ')
750 AND NVL(pk4_value,' ') = NVL(p_pk4_value,' ')
751 AND NVL(pk5_value,' ') = NVL(p_pk5_value,' ')
752 AND e.fnd_document_id = f.document_id;
753
754 BEGIN
755 -- Use cursor to retrieve file_id based upon document attachments
756 OPEN c_get_event_key;
757 FETCH c_get_event_key INTO l_event_key;
758
759 -- No attachments or not in file upload system
760 IF c_get_event_key%NOTFOUND THEN
761 RETURN (0);
762 ELSE
763 -- Build query group
764 WHILE c_get_event_key%FOUND LOOP
765 x_event_group(i).event_name := 'oracle.apps.edr.file.approve';
766 x_event_group(i).event_key := l_event_key;
767 i := i + 1;
768 FETCH c_get_event_key INTO l_event_key;
769 END LOOP;
770
771 -- Retrieve query id
772 l_query_id := GMA_STANDARD.PSIG_QUERY(x_event_group);
773 END IF;
774 CLOSE c_get_event_key;
775
776 RETURN(l_query_id);
777
778 EXCEPTION
779 WHEN OTHERS THEN
780 x_error_message:= SQLERRM;
781 x_return_status := 'U';
782 RETURN(-1);
783
784 END build_eres_query;
785
786 /*===========================================================================
787 -- FUNCTION:
788 -- get_erecord_id
789 --
790 -- DESCRIPTION:
791 -- This PL/SQL procedure is used to get erecord_id for a event/key
792 -- combination.
793 --
794 -- PARAMETERS:
795 -- p_event_name IN VARCHAR2 - Name of the event
796 -- p_event_key IN VARCHAR2 - event key
797 --
798 --
799 -- HISTORY
800 -- Thomas Daniel 19-Jul-2005 Added for the erecord enhancement for GME.
801 -- Details in bug 4328588
802 --=========================================================================== */
803 FUNCTION GET_ERECORD_ID
804 ( p_event_name IN VARCHAR2
805 ,p_event_key IN VARCHAR2
806 ) RETURN NUMBER IS
807 BEGIN
808 RETURN EDR_STANDARD_PUB.GET_ERECORD_ID
809 ( p_event_name => p_event_name
810 ,p_event_key => p_event_key
811 );
812
813 END GET_ERECORD_ID;
814
815 end GMA_STANDARD;
[Home] [Help]