[Home] [Help]
PACKAGE BODY: APPS.EDR_FILES_PUB
Source
1 PACKAGE BODY EDR_FILES_PUB AS
2 /* $Header: EDRPFILB.pls 120.1.12000000.1 2007/01/18 05:54:35 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EDR_FILES_PUB';
5 G_PUBLISH_FLAG_N constant varchar2(1) := 'N';
6 G_PUBLISH_FLAG_Y constant varchar2(1) := 'Y';
7 G_SECURITY_OFF constant NUMBER := 4;
8 G_SECURITY_ON constant NUMBER := 1;
9
10 PROCEDURE UPLOAD_FILE( p_api_version IN NUMBER,
11 p_commit IN VARCHAR2,
12 p_called_from_forms IN VARCHAR2,
13 p_file_name IN VARCHAR2,
14 p_category IN VARCHAR2,
15 p_content_type IN VARCHAR2,
16 p_version_label IN VARCHAR2,
17 p_file_data IN BLOB,
18 p_file_format IN VARCHAR2,
19 p_source_lang IN VARCHAR2,
20 p_description IN VARCHAR2,
21 p_file_exists_action IN VARCHAR2,
22 p_submit_for_approval IN VARCHAR2,
23 p_attribute1 IN VARCHAR2,
24 p_attribute2 IN VARCHAR2,
25 p_attribute3 IN VARCHAR2,
26 p_attribute4 IN VARCHAR2,
27 p_attribute5 IN VARCHAR2,
28 p_attribute6 IN VARCHAR2,
29 p_attribute7 IN VARCHAR2,
30 p_attribute8 IN VARCHAR2,
31 p_attribute9 IN VARCHAR2,
32 p_attribute10 IN VARCHAR2,
33 p_created_by IN NUMBER,
34 p_creation_date IN DATE,
35 p_last_updated_by IN NUMBER,
36 p_last_update_login IN NUMBER,
37 p_last_update_date IN DATE,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_data OUT NOCOPY VARCHAR2)
40 AS
41
42 -- Alpha Variables
43 L_API_NAME CONSTANT VARCHAR2(30) := 'UPLOAD_FILE';
44 L_FILE_NAME VARCHAR2(2000);
45 L_EXTENSION VARCHAR2(2000);
46 L_RETURN_STATUS VARCHAR2(1);
47 L_CONCAT_SEGS VARCHAR2(200);
48 L_STATUS VARCHAR2(30);
49 L_EVENT_USER_KEY VARCHAR2(2000);
50 L_USER VARCHAR2(240);
51 L_MSG_DATA VARCHAR2(2000);
52 L_FILE_DATA BLOB;
53
54 L_SEG_ARRAY FND_FLEX_EXT.SEGMENTARRAY;
55
56 -- Numeric Variables
57 L_COUNT NUMBER;
58 L_ORACLE_ERROR NUMBER;
59 L_API_VERSION CONSTANT NUMBER := 1.0;
60 L_VERSION NUMBER;
61 L_POS NUMBER;
62 L_RESP_ID NUMBER;
63 L_RESP_APPL_ID NUMBER;
64 L_ROW_ID VARCHAR2(240);
65 L_FILE_ID NUMBER;
66 L_CATEGORY_ID NUMBER;
67 L_FND_DOCUMENT_ID NUMBER;
68 L_ATTACHED_DOCUMENT_ID NUMBER;
69 X_DOCUMENT_ID NUMBER;
70 L_MEDIA_ID NUMBER;
71
72 I INTEGER;
73
74 -- Exceptions
75 EDR_FILES_FILE_NAME_NULL EXCEPTION;
76 EDR_FILES_CATEGORY_NULL EXCEPTION;
77 EDR_FILES_FILE_NULL EXCEPTION;
78 EDR_FILES_FORMAT_NULL EXCEPTION;
79 EDR_FILES_SRC_LANG_NULL EXCEPTION;
80 EDR_FILES_EXIST_ACT_NULL EXCEPTION;
81 EDR_FILES_ERES_ERROR EXCEPTION;
82 EDR_FILES_INSERT_ERROR EXCEPTION;
83 EDR_FILES_INV_FLEX_VALUE EXCEPTION;
84 EDR_FILES_ALREADY_EXISTS EXCEPTION;
85 EDR_FILES_OVERWRITE_ERROR EXCEPTION;
86 EDR_FILES_APPL_ID_NULL EXCEPTION;
87 EDR_FILES_RESP_ID_NULL EXCEPTION;
88 EDR_FILES_USER_NULL EXCEPTION;
89 edr_commit_flag_error EXCEPTION;
90 --Bug 3581517:Start
91 EDR_FILES_INVALID_CATEGORY EXCEPTION;
92 --Bug 3581517:End
93
94 L_EVENT EDR_ERES_EVENT_PUB.ERES_EVENT_REC_TYPE;
95 L_CHILDREN EDR_ERES_EVENT_PUB.ERECORD_ID_TBL_TYPE;
96
97 L_MSG_COUNT NUMBER;
98 L_MSG_index NUMBER;
99
100 L_ERECORD_ID NUMBER;
101 L_EVENT_STATUS VARCHAR2(20);
102
103 l_send_ackn boolean := FALSE;
104 l_trans_status varchar2(30);
105
106 -- Cursors
107 CURSOR c_get_category IS
108 SELECT category_id
109 FROM fnd_document_categories
110 WHERE name = p_category;
111 LocalCatRecord c_get_category%ROWTYPE;
112
113 CURSOR c_get_exists IS
114 SELECT version_number, status
115 FROM edr_files_b
116 WHERE original_file_name = p_file_name
117 AND category_id = LocalCatRecord.category_id;
118 LocalExistsRecord c_get_exists%ROWTYPE;
119
120 CURSOR c_get_seq_num IS
121 SELECT edr_files_b_s.nextval
122 FROM dual;
123 LocalSeqRecord c_get_seq_num%ROWTYPE;
124
125 CURSOR c_get_event IS
126 SELECT a.file_name, b.name
127 FROM edr_files_b a, fnd_document_categories b
128 WHERE a.category_id = b.category_id
129 AND a.file_id = l_file_id;
130 LocalEventRecord c_get_event%ROWTYPE;
131
132 -- Used to get the next attached_document_id for a new attachment
133 CURSOR c_get_id IS
134 SELECT fnd_attached_documents_s.nextval
135 FROM dual;
136
137 -- Used to check if the attribute is required in the flexfield
138 CURSOR c_get_flex1 IS
139 SELECT count(*)
140 FROM fnd_descr_flex_column_usages
141 WHERE application_id = '709'
142 AND descriptive_flexfield_name = 'EDR_FILE_ATTRIBUTES'
143 AND descriptive_flex_context_code = p_category;
144 LocalFlex1Record c_get_flex1%ROWTYPE;
145
146 BEGIN
147 -- Initialize API return status to success
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 -- Get ids
151 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
152 l_resp_id := FND_GLOBAL.RESP_ID;
153 l_user := FND_GLOBAL.USER_NAME;
154
155 IF l_resp_appl_id IS NULL THEN
156 RAISE EDR_FILES_APPL_ID_NULL;
157 END IF;
158
159 IF l_resp_id IS NULL THEN
160 RAISE EDR_FILES_RESP_ID_NULL;
161 END IF;
162
163 IF l_user IS NULL THEN
164 RAISE EDR_FILES_USER_NULL;
165 END IF;
166
167 --Check the API version passed in matches the
168 --internal API version.
169
170 IF NOT FND_API.Compatible_API_Call
171 (g_api_version,
172 p_api_version,
173 l_api_name,
174 g_pkg_name) THEN
175
176 RAISE FND_API.G_Exc_Unexpected_Error;
177 END IF;
178
179 -- Check for required parameters
180 IF p_file_name IS NULL THEN
181 RAISE EDR_FILES_FILE_NAME_NULL;
182 END IF;
183
184 IF p_category IS NULL THEN
185 RAISE EDR_FILES_CATEGORY_NULL;
186 END IF;
187
188 IF p_file_data IS NULL THEN
189 RAISE EDR_FILES_FILE_NULL;
190 END IF;
191
192 IF p_file_format IS NULL THEN
193 RAISE EDR_FILES_FORMAT_NULL;
194 END IF;
195
196 IF p_source_lang IS NULL THEN
197 RAISE EDR_FILES_SRC_LANG_NULL;
198 END IF;
199
200 IF p_file_exists_action IS NULL THEN
201 RAISE EDR_FILES_EXIST_ACT_NULL;
202 END IF;
203
204 --Bug 3265035: Start
205 --Add additional check to make sure that the commit takes place
206 --when the file is being sent for approval
207 --this is required because it doesnt make sense to send a file
208 --for approval without it having been commited to the database
209 --and technically it wouldn't work because the ERES rule function
210 --is doing its commits autonomously
211 if (p_commit <> 'T' AND p_submit_for_approval = 'Y') then
212 raise edr_commit_flag_error;
213 end if;
214 --Bug 3265035: End
215
216 -- Default the internal version in case the document does not already exist
217 l_version := 0;
218
219 -- The status is always 'N'(New)
220 l_status := 'N';
221
222 -- Select the category id
223 OPEN c_get_category;
224 FETCH c_get_category INTO LocalCatRecord;
225
226 --Bug 3581517:Start
227 --comment 1 line
228 --l_category_id := LocalCatRecord.category_id;
229
230 --Check if the category exists or not
231 IF c_get_category%NOTFOUND THEN
232 RAISE EDR_FILES_INVALID_CATEGORY;
233 ELSE
234 l_category_id := LocalCatRecord.category_id;
235 END IF;
236 --Bug 3581517:End
237 CLOSE c_get_category;
238
239 -- Check to see if file already exists
240 OPEN c_get_exists;
241 FETCH c_get_exists INTO LocalExistsRecord;
242
243 -- File already exists and not be overwritten, return error
244 IF c_get_exists%FOUND THEN
245 l_version := LocalExistsRecord.version_number;
246 IF p_file_exists_action = 'D' THEN
247 RAISE EDR_FILES_ALREADY_EXISTS;
248 END IF;
249 END IF;
250
251 -- File exists as approved, pending or approval not required,
252 -- and not to be versioned, return error
253 IF c_get_exists%FOUND THEN
254 l_version := LocalExistsRecord.version_number;
255 IF p_file_exists_action = 'O' AND LocalExistsRecord.status in ('A', 'P', 'S') THEN
256 RAISE EDR_FILES_OVERWRITE_ERROR;
257 END IF;
258 END IF;
259 CLOSE c_get_exists;
260
261 -- Validate flexfield values
262 FND_FLEX_DESCVAL.set_context_value(p_category);
263
264 i := 0;
265 IF p_category IS NOT NULL THEN
266 i := i +1;
267 l_seg_array(i) := p_category;
268 END IF;
269
270 OPEN c_get_flex1;
271 FETCH c_get_flex1 INTO l_count;
272
273 --validate the flexfields
274 --Bug 3581517:Start
275 --comment 1 line
276 --IF c_get_flex1%FOUND THEN
277 IF l_count > 0 THEN
278 --comment the code that conditionally sets the flexfield segment value
279 --and instead unconditionally set all the values
280 --one sample of older code that is removed is given below...the rest of
281 --the block is deleted and replaced by new code
282 /*
283 IF l_count >= i THEN
284 i := i + 1;
285 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1);
286 END IF;
287 */
288 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1);
289 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2);
290 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3);
291 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4);
292 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5);
293 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6);
294 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7);
295 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8);
296 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9);
297 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10);
298
299 --Check for validity of the flexfield data
300 --For bug 3581517 this check is now moved inside the top if
301 --block (l_count>0) instead of doing an unguarded check
302 IF NOT (FND_FLEX_DESCVAL.validate_desccols('EDR',
303 'EDR_FILE_ATTRIBUTES',
304 'V',
305 SYSDATE))
306 THEN
307 RAISE EDR_FILES_INV_FLEX_VALUE;
308 END IF;
309 --Bug 3581517:End
310
311 END IF;
312 CLOSE c_get_flex1;
313
314 --Increment internal version number
315 l_version := l_version + 1;
316
317 -- Locate beginning of the extension in the file name string to use position to
318 --split up the file name
319 l_extension := NULL;
320 l_file_name := p_file_name;
321 l_pos := INSTR(p_file_name, '.',-1,1);
322
323 IF l_pos <> 0 THEN
324 l_extension := SUBSTR(l_file_name,l_pos,LENGTH(l_file_name));
325 l_file_name := SUBSTR(p_file_name,1,l_pos-1);
326 END IF;
327
328 --Set up the display name for the attachment
329 l_file_name := l_file_name ||'_v_'||p_version_label||l_extension;
330
331 --Get the next sequence number for file_id
332 SELECT edr_files_b_s.nextval INTO l_file_id FROM dual;
333
334 --Call private package to insert data into the tables
335 --the whole idea is that instead of inserting one row in edr_files_b/tl tables
336 -- we would have to insert a row in:
337 -- edr_files_b/tl
338 -- fnd_documents
339 --fnd_lobs
340 --fnd_attached_documents (attach the file to a default enity named EDR_FILES_B
341
342 EDR_FILES_PKG.Insert_Row (
343 X_ROWID =>l_row_id,
344 X_FILE_ID =>l_file_id,
345 X_FILE_NAME =>l_file_name,
346 X_ORIGINAL_FILE_NAME =>p_file_name,
347 X_VERSION_LABEL =>p_version_label,
348 X_CATEGORY_ID =>l_category_id,
349 X_CONTENT_TYPE =>p_content_type,
350 X_FILE_FORMAT =>p_file_format,
351 X_STATUS =>l_status,
352 X_VERSION_NUMBER =>l_version,
353 X_FND_DOCUMENT_ID => NULL,
354 X_ATTRIBUTE_CATEGORY =>p_category,
355 X_ATTRIBUTE1=>p_attribute1,
356 X_ATTRIBUTE2=>p_attribute2,
357 X_ATTRIBUTE3=>p_attribute3,
358 X_ATTRIBUTE4=>p_attribute4,
359 X_ATTRIBUTE5 =>p_attribute5,
360 X_ATTRIBUTE6=>p_attribute6,
361 X_ATTRIBUTE7=>p_attribute7,
362 X_ATTRIBUTE8=>p_attribute8,
363 X_ATTRIBUTE9=>p_attribute9,
364 X_ATTRIBUTE10=>p_attribute10,
365 X_ATTRIBUTE11=>NULL,
366 X_ATTRIBUTE12=>NULL,
367 X_ATTRIBUTE13=>NULL,
368 X_ATTRIBUTE14=>NULL,
372 X_CREATED_BY =>p_created_by,
369 X_ATTRIBUTE15=>NULL,
370 X_DESCRIPTION =>p_description,
371 X_CREATION_DATE=>p_creation_date,
373 X_LAST_UPDATE_DATE=>p_last_update_date,
374 X_LAST_UPDATED_BY=>p_last_updated_by,
375 X_LAST_UPDATE_LOGIN =>p_last_update_login);
376
377 --now that the row is inserted in edr_files table
378 -- we have insert into fn_documents, fnd_attached_documents
379 -- and fnd_lobs
380
381 --to insert in fnd_documents and fnd_attached_dcouments
382 --use FND_ATTACHED_DOCUMENTS_PKG.Insert_Row
383
384 OPEN c_get_id;
385 FETCH c_get_id INTO l_attached_document_id;
386 CLOSE c_get_id;
387
388 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(X_Rowid=>l_row_id,
389 X_attached_document_id=>l_attached_document_id,
390 X_document_id=>x_document_id,
391 X_creation_date=>p_creation_date,
392 X_created_by=>p_created_by,
393 X_last_update_date=>p_last_update_date,
394 X_last_updated_by=>p_last_updated_by,
395 X_last_update_login=>p_last_update_login,
396 X_seq_num=>1,
397 X_entity_name=>'EDR_FILES_B',
398 X_column1=>NULL,
399 X_pk1_value=>NULL,
400 X_pk2_value=>NULL,
401 X_pk3_value=>NULL,
402 X_pk4_value=>NULL,
403 X_pk5_value=>NULL,
404 X_automatically_added_flag=>'N',
405 X_datatype_id=>6,
406 X_category_id=>l_category_id,
407 --Bug 4381237: Start
408 --We dont want any attachment security in the file
409 --X_security_type=>1,
410 --X_security_id=>-1,
411 X_security_type=>4,
412 X_security_id=> null,
413 --Bug Bug 4381237: End
414 X_publish_flag=>'N',
415 X_storage_type=>1,
416 X_usage_type=>'S',
417 X_language=>p_source_lang,
418 X_description=>p_description,
419 X_file_name=>l_file_name,
420 X_media_id=>l_media_id,
421 X_doc_attribute_category=>p_category,
422 X_doc_attribute1=>p_attribute1,
423 X_doc_attribute2=>p_attribute2,
424 X_doc_attribute3=>p_attribute3,
425 X_doc_attribute4=>p_attribute4,
426 X_doc_attribute5=>p_attribute5,
427 X_doc_attribute6=>p_attribute6,
428 X_doc_attribute7=>p_attribute7,
429 X_doc_attribute8=>p_attribute8,
430 X_doc_attribute9=>p_attribute9,
431 X_doc_attribute10=>p_attribute10,
432 X_create_doc=>'N');
433
434 -- use a direct INSERT statement to insert
435 -- into FND_LOBS
436 INSERT into FND_LOBS
437 (file_id,
438 file_name,
439 file_data,
440 file_content_type,
441 file_format)
442 VALUES
443 (l_media_id,
444 l_file_name,
445 --Bug 3265035: Start
446 p_file_data,
447 --l_file_data,
448 --Bug 3265035: End
449 p_content_type,
450 p_file_format);
451
452 --now update the row in the edr_files_b table with the fnd_document_id
453 --that you get from inserting a row in FND_DOCUMENTS table
454
455 UPDATE EDR_FILES_B
456 SET fnd_document_id = x_document_id
457 WHERE file_id = l_file_id;
458
459 --If sending for approval, raise ERES event
460 IF p_submit_for_approval = 'Y' THEN
461
462 --Bug 3265035: Start
463 --if the file is being sent for approval go ahead and commit
464 --u dont need to check commit flag as the commit flag would have
465 --to be Y in order for the control to come here
466 commit;
467 --Bug 3265035: End
468
469 OPEN c_get_event;
470 FETCH c_get_event INTO LocalEventRecord;
471 l_event_user_key := LocalEventRecord.file_name ||'-'||LocalEventRecord.name;
472 CLOSE c_get_event;
473
474 --Bug 3265035: Start
475 -- raise the event
476
477 --create the payload first
478 l_event.param_name_1 := 'DEFERRED';
479 l_event.param_value_1 := 'Y';
480 l_event.param_name_2 := 'POST_OPERATION_API';
481 l_event.param_value_2 := 'EDR_ATTACHMENTS_GRP.EVENT_POST_OP('||l_file_id||')';
482 l_event.param_name_3 := 'PSIG_USER_KEY_LABEL';
483 l_event.param_value_3 := 'File Approval:';
484 l_event.param_name_4 := 'PSIG_USER_KEY_VALUE';
485 l_event.param_value_4 := l_event_user_key;
486 l_event.param_name_5 := 'PSIG_TRANSACTION_AUDIT_ID';
487 l_event.param_value_5 := '-1';
488 l_event.param_name_6 := '#WF_SOURCE_APPLICATION_TYPE';
489 l_event.param_value_6 := 'DB';
490 l_event.param_name_7 := '#WF_SIGN_REQUESTER';
491 l_event.param_value_7 := l_user;
492
493 --now create the eres event
494 l_event.event_name := 'oracle.apps.edr.file.approve';
495 l_event.event_key := l_file_id;
496 l_event.erecord_id := l_erecord_id;
497 l_event.event_status := l_event_status;
498
499 --now raise the eres event
500 EDR_ERES_EVENT_PUB.RAISE_ERES_EVENT
501 ( p_api_version => 1.0 ,
502 p_init_msg_list => FND_API.G_TRUE ,
503 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
504 x_return_status => l_return_status ,
505 x_msg_count => l_msg_count ,
506 x_msg_data => l_msg_data ,
507 p_child_erecords => l_children ,
508 x_event => l_event
509 );
510
511 -- If any errors happen abort API.
515 RAISE FND_API.G_EXC_ERROR;
512 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
514 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
516 END IF;
517
518 /*
519 wf_event.raise2(p_event_name => 'oracle.apps.edr.file.approve',
520 p_event_key => l_file_id,
521 p_event_data => null,
522 p_parameter_name1 => 'DEFERRED',
523 p_parameter_value1 => 'Y',
524 p_parameter_name2 => 'POST_OPERATION_API',
525 p_parameter_value2 => 'EDR_ATTACHMENTS_GRP.EVENT_POST_OP('||l_file_id||')',
526 p_parameter_name3 => 'PSIG_USER_KEY_LABEL',
527 p_parameter_value3 => 'File Approval:',
528 p_parameter_name4 => 'PSIG_USER_KEY_VALUE',
529 p_parameter_value4 => l_event_user_key,
530 p_parameter_name5 => 'PSIG_TRANSACTION_AUDIT_ID',
531 p_parameter_value5 => '-1',
532 p_parameter_name6 => '#WF_SOURCE_APPLICATION_TYPE',
533 p_parameter_value6 => 'DB',
534 p_parameter_name7 => '#WF_SIGN_REQUESTER',
535 p_parameter_value7 => l_user);
536
537 EDR_STANDARD.PSIG_STATUS
538 ( p_event => 'oracle.apps.edr.file.approve' ,
539 p_event_key => l_file_id ,
540 p_status => l_status
541 );
542 */
543
544 l_erecord_id := l_event.erecord_id;
545 l_event_status := l_event.event_status;
546
547 --if the erecord id has been generated it means that the file required some
548 --erecord and/or esignature. in that case update the status of the file and
549 --also send out an acknowldgement to the evidence store
550
551 if (l_erecord_id is not null) then
552 -- If the status of the erecord in the evidence store is NOACTION
553 -- it means that signature was not required.
554 -- if its PENDING means that the offine notification is sent out
555 -- to the approver and its waiting approval
556 -- in any other case raise an error
557
558 if (l_event_status = 'PENDING') then
559 l_send_ackn := TRUE;
560 l_trans_status := 'SUCCESS';
561
562 --update the file status and put the erecord
563 --id in attribute 14
564
565 UPDATE edr_files_b
566 SET status = 'P',
567 attribute14 = l_erecord_id
568 WHERE file_id = l_file_id
569 AND file_name = LocalEventRecord.file_name;
570 elsif (l_event_status = 'NOACTION') then
571 l_send_ackn := TRUE;
572 l_trans_status := 'SUCCESS';
573
574 UPDATE edr_files_b
575 SET status = 'S',
576 attribute14 = l_erecord_id
577 WHERE file_id = l_file_id
578 AND file_name = LocalEventRecord.file_name;
579
580 --Bug 3265035: Start
581 --this would allow the file to be attached to other business objects
582 --through the Document Catalog button in the attachment Forms UI
583 update fnd_documents set
584 security_type = G_SECURITY_OFF,
585 publish_flag = G_PUBLISH_FLAG_Y
586 where document_id = x_document_id;
587 --For the Status 'NO APPROVAL' Raise the Approval completion Event
588 wf_event.raise2(p_event_name => 'oracle.apps.edr.file.approvalcompletion',
589 p_event_key => l_file_id,
590 p_event_data => null,
591 p_parameter_name1 => 'FILE_STATUS',
592 p_parameter_value1 => 'NO APPROVAL'
593 );
594 --Bug 3265035: End
595 end if;
596
597
598 --send transaction acknowledgement
599 if l_send_ackn = TRUE then
600 EDR_TRANS_ACKN_PUB.SEND_ACKN
601 ( p_api_version => 1.0,
602 p_init_msg_list => FND_API.G_TRUE ,
603 x_return_status => l_return_status,
604 x_msg_count => l_msg_count,
605 x_msg_data => l_msg_data,
606 p_event_name => l_event.event_name,
607 p_event_key => l_event.event_key,
608 p_erecord_id => l_erecord_id,
609 p_trans_status => l_trans_status,
610 p_ackn_by => 'FILE_UPLOAD_API',
611 p_ackn_note => '',
612 p_autonomous_commit => 'T'
613 );
614
615 -- If any errors happen abort API.
616 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
619 RAISE FND_API.G_EXC_ERROR;
620 END IF;
621 end if;
622 end if;
623 --Bug 3265035: End
624 END IF;
625
626 --if everything is successful and commit is yes, issue a commit
627 IF p_commit = 'T' THEN
628 COMMIT;
629 END IF;
630
631 EXCEPTION
632 WHEN FND_API.G_EXC_ERROR THEN
633 x_return_status := FND_API.G_RET_STS_ERROR ;
634 FND_MSG_PUB.Count_And_Get
635 (p_count => l_msg_count ,
636 p_data => x_msg_data
637 );
638 APP_EXCEPTION.RAISE_EXCEPTION;
639
640 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
642 FND_MSG_PUB.Count_And_Get
643 (p_count => l_msg_count ,
647
644 p_data => x_msg_data
645 );
646 APP_EXCEPTION.RAISE_EXCEPTION;
648 WHEN EDR_FILES_FILE_NAME_NULL THEN
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_FILE_NAME_NULL');
651 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
652 FND_MSG_PUB.ADD;
653 APP_EXCEPTION.RAISE_EXCEPTION;
654
655 WHEN EDR_FILES_APPL_ID_NULL THEN
656 x_return_status := FND_API.G_RET_STS_ERROR;
657 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_APPL_ID_NULL');
658 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
659 FND_MSG_PUB.ADD;
660 APP_EXCEPTION.RAISE_EXCEPTION;
661
662 WHEN EDR_FILES_RESP_ID_NULL THEN
663 x_return_status := FND_API.G_RET_STS_ERROR;
664 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_RESP_ID_NULL');
665 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
666 FND_MSG_PUB.ADD;
667 APP_EXCEPTION.RAISE_EXCEPTION;
668
669 WHEN EDR_FILES_USER_NULL THEN
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_USER_NULL');
672 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
673 FND_MSG_PUB.ADD;
674 APP_EXCEPTION.RAISE_EXCEPTION;
675
676 WHEN EDR_FILES_CATEGORY_NULL THEN
677 x_return_status := FND_API.G_RET_STS_ERROR;
678 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_CATEGORY_NULL');
679 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
680 FND_MSG_PUB.ADD;
681 APP_EXCEPTION.RAISE_EXCEPTION;
682
683 WHEN EDR_FILES_FILE_NULL THEN
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_FILE_NULL');
686 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
687 FND_MSG_PUB.ADD;
688 APP_EXCEPTION.RAISE_EXCEPTION;
689
690 WHEN EDR_FILES_FORMAT_NULL THEN
691 x_return_status := FND_API.G_RET_STS_ERROR;
692 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_FORMAT_NULL');
693 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
694 FND_MSG_PUB.ADD;
695 APP_EXCEPTION.RAISE_EXCEPTION;
696
697 WHEN EDR_FILES_SRC_LANG_NULL THEN
698 x_return_status := FND_API.G_RET_STS_ERROR;
699 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_SRC_LANG_NULL');
700 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
701 FND_MSG_PUB.ADD;
702 APP_EXCEPTION.RAISE_EXCEPTION;
703
704 WHEN EDR_FILES_EXIST_ACT_NULL THEN
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_EXISTS_ACT_NULL');
707 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
708 FND_MSG_PUB.ADD;
709 APP_EXCEPTION.RAISE_EXCEPTION;
710
711 WHEN EDR_FILES_ERES_ERROR THEN
712 x_return_status := FND_API.G_RET_STS_ERROR;
713 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_ERES_ERROR');
714 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
715 FND_MSG_PUB.ADD;
716 APP_EXCEPTION.RAISE_EXCEPTION;
717
718 WHEN EDR_FILES_INSERT_ERROR THEN
719 x_return_status := FND_API.G_RET_STS_ERROR;
720 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_INSERT_ERROR');
721 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
722 FND_MSG_PUB.ADD;
723 APP_EXCEPTION.RAISE_EXCEPTION;
724
725 WHEN EDR_FILES_INV_FLEX_VALUE THEN
726 x_return_status := FND_API.G_RET_STS_ERROR;
727 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_INV_FLEX_VALUE');
728 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
729 FND_MSG_PUB.ADD;
730 APP_EXCEPTION.RAISE_EXCEPTION;
731
732 WHEN EDR_FILES_ALREADY_EXISTS THEN
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_ALREADY_EXISTS');
735 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
736 FND_MSG_PUB.ADD;
737 APP_EXCEPTION.RAISE_EXCEPTION;
738
739 WHEN EDR_FILES_OVERWRITE_ERROR THEN
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_OVERWRITE_ERROR');
742 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
743 FND_MSG_PUB.ADD;
744 APP_EXCEPTION.RAISE_EXCEPTION;
745
746 WHEN edr_commit_flag_error THEN
747 x_return_status := FND_API.G_RET_STS_ERROR;
748 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_COMMIT_FLAG_ERROR');
749 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
750 FND_MSG_PUB.ADD;
751 APP_EXCEPTION.RAISE_EXCEPTION;
752
753 WHEN EDR_FILES_INVALID_CATEGORY THEN
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_INVALID_CATEGORY');
756 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
757 FND_MSG_PUB.ADD;
758 APP_EXCEPTION.RAISE_EXCEPTION;
759
760 WHEN OTHERS THEN
761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
762 x_msg_data := 'Error = '||SQLERRM;
763 FND_MSG_PUB.ADD;
764 APP_EXCEPTION.RAISE_EXCEPTION;
765
766 END UPLOAD_FILE;
767
768 END EDR_FILES_PUB;