[Home] [Help]
PACKAGE BODY: APPS.ECX_ATTACHMENT
Source
1 PACKAGE BODY ecx_attachment AS
2 -- $Header: ECXATCHB.pls 120.2 2006/05/24 16:05:25 susaha ship $
3
4 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
5 l_statement PLS_INTEGER := ecx_debug.g_statement;
6 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
7 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
8 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
9 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
10
11 -- private variables, types and functions
12 EMBEDDED_PROGRAM_NAME CONSTANT VARCHAR2(32) := 'ECX_ATTACHMENT';
13 EMBEDDED_PROGRAM_TAG CONSTANT VARCHAR2(32) := 'ECX_ATTACHMENT';
14 EMBEDDED_FND_KEY1 CONSTANT VARCHAR2(100) := 'ECX_ATTACHMENT_KEY1';
15 EMBEDDED_ECX_ENTITY_NAME CONSTANT VARCHAR2(40) := 'ECX_ATTACHMENT_ENTITY';
16 EMBEDDED_FND_SEQ_INIT CONSTANT NUMBER := 88;
17 EMBEDDED_FND_SEQ_INTERVAL CONSTANT NUMBER := 10;
18
19 ATTACHMENT_RECORD_DELIMITOR CONSTANT VARCHAR2(1) := '!';
20 ATTACHMENT_FIELD_DELIMITOR CONSTANT VARCHAR2(1) := ':';
21 ATTACHMENT_CID_DELIMITOR CONSTANT VARCHAR2(1) := '@';
22
23 -- Temporary place to hold attachment reference maps for offline (BES) cases
24 i_attachment_maps VARCHAR2(2000) := NULL;
25
26 TYPE attachment_record_type IS RECORD (cid VARCHAR2(256),
27 fid NUMBER,
28 dataType NUMBER);
29
30 TYPE attachment_records_type IS TABLE OF attachment_record_type INDEX BY BINARY_INTEGER;
31
32
33 PROCEDURE get_attachment_records(
34 i_attachments IN VARCHAR2,
35 x_records OUT NOCOPY attachment_records_type);
36
37 -- public procedures
38 PROCEDURE deposit_blob_attachment(
39 i_main_doc_id IN OUT NOCOPY NUMBER,
40 i_file_name IN VARCHAR2,
41 i_file_content_type IN VARCHAR2 DEFAULT NULL,
42 i_file_data IN BLOB,
43 i_expire_date IN DATE,
44 i_lang IN VARCHAR2,
45 i_ora_charset IN VARCHAR2,
46 i_file_format IN VARCHAR2 DEFAULT NULL,
47 x_file_id OUT NOCOPY NUMBER
48 ) AS
49
50 i_method_name varchar2(2000) := 'ecx_attachment.deposit_blob_attachment';
51 l_org_id VARCHAR2(60);
52 l_cur_doc_id NUMBER := NULL;
53 l_security_id NUMBER := NULL;
54 l_attached_id NUMBER := NULL;
55 l_program_app_id NUMBER := 0; -- ??????????
56 l_program_id NUMBER := 0; -- ??????????
57 l_request_id NUMBER := 0; -- ?????????
58 l_seq_num NUMBER := 0;
59 l_row_id_tmp VARCHAR2(30) := NULL;
60 l_main_row_id VARCHAR2(30) := NULL;
61 l_main_media_id NUMBER := NULL;
62 l_install_mode VARCHAR2(200) := NULL;
63 l_progress VARCHAR2(4) := NULL;
64 l_program_name VARCHAR2(32) := EMBEDDED_PROGRAM_NAME;
65 l_program_tag VARCHAR2(32) := EMBEDDED_PROGRAM_TAG;
66 l_category_id_tmp NUMBER;
67 l_language_tmp VARCHAR2(30);
68 l_dynamic_sql_str0 VARCHAR2(2000) := NULL;
69 l_dynamic_sql_str1 VARCHAR2(2000) := NULL;
70 l_dynamic_sql_str2 VARCHAR2(2000) := NULL;
71 l_dynamic_sql_str3 VARCHAR2(2000) := NULL;
72 l_dynamic_sql_str4 VARCHAR2(2000) := NULL;
73 l_dynamic_sql_str5 VARCHAR2(2000) := NULL;
74 l_dynamic_sql_str6 VARCHAR2(2000) := NULL;
75 l_dynamic_sql_str7 VARCHAR2(2000) := NULL;
76 l_description_tmp VARCHAR2(255) := 'main doc for ECX to deposit attachment files';
77 mode_not_support EXCEPTION;
78
79 BEGIN
80
81 if (l_procedureEnabled) then
82 ecx_debug.push(i_method_name);
83 end if;
84 if(l_statementEnabled) then
85 ecx_debug.log(l_statement,'i_main_doc_id', i_main_doc_id,i_method_name);
86 ecx_debug.log(l_statement,'i_file_content_type', i_file_content_type,i_method_name);
87 ecx_debug.log(l_statement,'i_file_format', i_file_format,i_method_name);
88 end if;
89
90 -- Check mode
91 -- ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
92 -- IF (ecx_utils.g_install_mode = 'EMBEDDED') THEN
93
94 l_install_mode := wf_core.translate('WF_INSTALL');
95 IF (l_install_mode = 'EMBEDDED') THEN
96
97 -- Insert a record in fnd_documents representing the current
98 -- attachment
99 l_progress := '0000';
100 l_category_id_tmp := 33;
101 l_language_tmp := i_lang;
102
103 l_dynamic_sql_str1 := 'BEGIN fnd_profile.get('||'''ORG_ID'''||', :orgId); END;';
104 EXECUTE IMMEDIATE l_dynamic_sql_str1 USING OUT l_org_id;
105
106 l_security_id := TO_NUMBER(l_org_id);
107
108 l_dynamic_sql_str2 := 'BEGIN
109 fnd_documents_pkg.insert_row(:1,:2,:3,:4,:5,:6,
110 :7,:8,:9,:10,:11,:12,
111 NULL,
112 NULL,
113 :13,
114 NULL,
115 NULL,
116 NULL,
117 NULL,
118 NULL,
119 :14,
120 :15,
121 :16,
122 NULL,
123 :17,
124 NULL, NULL, NULL, NULL,
125 NULL, NULL, NULL, NULL,
126 NULL, NULL, NULL, NULL,
127 NULL, NULL, NULL, NULL);
128 END;';
129
130 EXECUTE IMMEDIATE l_dynamic_sql_str2
131 USING IN OUT l_row_id_tmp,
132 IN OUT l_cur_doc_id,
133 SYSDATE,
134 0,
135 SYSDATE,
136 0,
137 0,
138 ecx_attachment.EMBEDDED_LOB_DATA_TYPE,
139 l_category_id_tmp,
140 1,
141 l_security_id,
142 'Y',
143 '0',
144 SYSDATE,
145 l_language_tmp,
146 l_description_tmp,
147 IN OUT x_file_id;
148
149 -- Insert the actual blob into the fnd_lobs table
150 l_progress := '0001';
151 IF (x_file_id is NULL) THEN
152
153 l_dynamic_sql_str0 := 'SELECT fnd_lobs_s.nextval FROM dual';
154 EXECUTE IMMEDIATE l_dynamic_sql_str0 INTO x_file_id;
155
156 END IF;
157
158 l_dynamic_sql_str3 := 'INSERT INTO fnd_lobs
159 (file_id, file_name, file_content_type, upload_date,
160 expiration_date, program_name, program_tag,
161 file_data, language, oracle_charset, file_format)
162 VALUES (:1, :2, :3, NULL, :4, :5, :6,
163 :7, :8, :9, :10)';
164
165 EXECUTE IMMEDIATE l_dynamic_sql_str3
166 USING x_file_id, i_file_name, i_file_content_type,
167 i_expire_date, l_program_name, l_program_tag,
168 i_file_data, l_language_tmp, i_ora_charset, i_file_format;
169
170
171 -- Make association between the current attachment file with its
172 -- main business document in fnd_attached_documents table. Note
173 -- l_cur_doc_id is the document id denoting the current attachment,
174 -- while the x_file_id is the file_id of the very same attachment.
175 IF (i_main_doc_id IS NULL) THEN
176
177 -- the main doc does not exist. The mulitple attachments of
178 -- a the same main doc is assoicated with the PK2_VALUE
179 -- column in the fnd_attached_documents table. the value
180 -- of the PK2_VALUE is obtained from the fnd_documents_s
181 -- sequencer.
182 l_progress := '0002';
183
184 l_dynamic_sql_str4 := 'SELECT fnd_documents_s.nextval FROM dual';
185 EXECUTE IMMEDIATE l_dynamic_sql_str4 INTO i_main_doc_id;
186
187 END IF;
188
189 -- Create attachment record in fnd_attached_documents now
190 l_progress := '0003';
191 l_dynamic_sql_str5 := 'SELECT MAX(seq_num)
192 FROM fnd_attached_documents
193 WHERE pk1_value =''' || EMBEDDED_FND_KEY1 || ''' AND
194 entity_name =''' || EMBEDDED_ECX_ENTITY_NAME || '''';
195 EXECUTE IMMEDIATE l_dynamic_sql_str5 INTO l_seq_num;
196
197 IF l_seq_num is NULL THEN
198 l_seq_num := EMBEDDED_FND_SEQ_INIT;
199 ELSE
200 l_seq_num := l_seq_num + EMBEDDED_FND_SEQ_INTERVAL;
201 END IF;
202
203 l_dynamic_sql_str6 := 'SELECT fnd_attached_documents_s.nextval FROM dual';
204 EXECUTE IMMEDIATE l_dynamic_sql_str6 INTO l_attached_id;
205
206 if(l_statementEnabled) then
207 ecx_debug.log(l_statement,'attached_document_id in FND_ATTACHED_DOCUMENTS', l_attached_id,i_method_name);
208 ecx_debug.log(l_statement,'current_attachment_id in FND_ATTACHED_DOCUMENTS',l_cur_doc_id,i_method_name);
209 ecx_debug.log(l_statement,'seq_num in FND_ATTACHED_DOCUMENTS', l_seq_num,i_method_name);
210 ecx_debug.log(l_statement,'pk2_value in FND_ATTACHED_DOCUMENTS', i_main_doc_id,i_method_name);
211 end if;
212
213 l_dynamic_sql_str7 := 'INSERT INTO fnd_attached_documents
214 (attached_document_id,
215 document_id,
216 creation_date,
217 created_by,
218 last_update_date,
219 last_updated_by,
220 last_update_login,
221 seq_num,
222 entity_name,
223 pk1_value, pk2_value, pk3_value,
224 pk4_value, pk5_value,
225 automatically_added_flag,
226 program_application_id, program_id,
227 program_update_date, request_id,
228 attribute_category, attribute1,
229 attribute2, attribute3, attribute4,
230 attribute5, attribute6, attribute7,
231 attribute8, attribute9, attribute10,
232 attribute11, attribute12, attribute13,
233 attribute14, attribute15, column1)
234 VALUES
235 (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10,
236 :11, NULL, NULL, NULL,
237 :12, :13, :14, :15, :16,
238 NULL, NULL, NULL, NULL,
239 NULL, NULL, NULL, NULL,
240 NULL, NULL, NULL, NULL,
241 NULL, NULL, NULL, NULL,
242 NULL)';
243 EXECUTE IMMEDIATE l_dynamic_sql_str7
244 USING l_attached_id, l_cur_doc_id,
245 SYSDATE, 0, SYSDATE, 0, 0, l_seq_num,
246 EMBEDDED_ECX_ENTITY_NAME, EMBEDDED_FND_KEY1, i_main_doc_id,
247 'N', l_program_app_id, l_program_id, SYSDATE, l_request_id;
248
249 ELSE
250
251 -- Standalone mode is not yet supported
252 RAISE mode_not_support;
253
254 END IF; -- EMBEDDED MODE
255
256 l_progress := '0004';
257
258 if (l_procedureEnabled) then
259 ecx_debug.pop(i_method_name);
260 end if;
261
262 EXCEPTION
263
264 WHEN mode_not_support THEN
265
266 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.DEPOSIT_BLOB_ATTACHMENT MODE NOT SUPPORT' || SQLERRM;
267 ecx_utils.i_ret_code := 0;
268 if(l_unexpectedEnabled) then
269 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
270 end if;
271 if (l_procedureEnabled) then
272 ecx_debug.pop(i_method_name);
273 end if;
274 RAISE ecx_utils.PROGRAM_EXIT;
275
276 WHEN OTHERS THEN
277
278
279 if(l_unexpectedEnabled) then
280 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
281 'ECX_ATTACHMENT.DEPOSIT_BLOB_ATTACHMENT');
282 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
283 end if;
284 ecx_utils.error_type := 30;
285 ecx_utils.i_ret_code := 2;
286 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.DEPOSIT_BLOB_ATTACHMENT';
287 if (l_procedureEnabled) then
288 ecx_debug.pop(i_method_name);
289 end if;
290 RAISE ecx_utils.PROGRAM_EXIT;
291
292 END deposit_blob_attachment;
293
294
295 PROCEDURE formulate_content_id(
296 i_file_id IN NUMBER,
297 i_entity_name IN VARCHAR2,
298 i_pk1_value IN VARCHAR2,
299 i_pk2_value IN VARCHAR2,
300 i_pk3_value IN VARCHAR2,
301 i_pk4_value IN VARCHAR2,
302 i_pk5_value IN VARCHAR2,
303 x_cid OUT NOCOPY VARCHAR2
304 ) AS
305 i_method_name varchar2(2000) := 'ecx_attachment.formulate_content_id';
306 l_progress VARCHAR2(4) := NULL;
307 l_file_name VARCHAR2(256) := NULL;
308 l_dynamic_sql_str0 VARCHAR2(2000) := NULL;
309 invalid_input EXCEPTION;
310
311 BEGIN
312
313 if (l_procedureEnabled) then
314 ecx_debug.push(i_method_name);
315 end if;
316 if(l_statementEnabled) then
317 ecx_debug.log(l_statement,'i_file_id', i_file_id,i_method_name);
318 ecx_debug.log(l_statement,'i_entity_name', i_entity_name,i_method_name);
319 ecx_debug.log(l_statement,'i_pk1_value', i_pk1_value,i_method_name);
320 ecx_debug.log(l_statement,'i_pk2_value', i_pk2_value,i_method_name);
321 ecx_debug.log(l_statement,'i_pk3_value', i_pk3_value,i_method_name);
322 ecx_debug.log(l_statement,'i_pk4_value', i_pk4_value,i_method_name);
323 ecx_debug.log(l_statement,'i_pk5_value', i_pk5_value,i_method_name);
324 end if;
325
326 IF (i_entity_name IS NOT NULL) THEN
327
328 x_cid := i_entity_name;
329
330 ELSE
331
332 RAISE invalid_input;
333
334 END IF;
335
336
337 x_cid := x_cid || ATTACHMENT_CID_DELIMITOR;
338
339 IF (i_pk1_value IS NOT NULL) THEN
340
341 x_cid := x_cid || i_pk1_value;
342
343 END IF;
344
345 x_cid := x_cid || ATTACHMENT_CID_DELIMITOR;
346
347 IF (i_pk2_value IS NOT NULL) THEN
351 END IF;
348
349 x_cid := x_cid || i_pk2_value;
350
352
353 x_cid := x_cid || ATTACHMENT_CID_DELIMITOR;
354
355 IF (i_pk3_value IS NOT NULL) THEN
356
357 x_cid := x_cid || i_pk3_value;
358
359 END IF;
360
361 x_cid := x_cid || ATTACHMENT_CID_DELIMITOR;
362
363
364 IF (i_pk4_value IS NOT NULL) THEN
365
366 x_cid := x_cid || i_pk4_value;
367
368 END IF;
369
370 x_cid := x_cid || ATTACHMENT_CID_DELIMITOR;
371
372
373 IF (i_pk5_value IS NOT NULL) THEN
374
375 x_cid := x_cid || i_pk5_value;
376
377 END IF;
378
379 x_cid := x_cid || ATTACHMENT_CID_DELIMITOR;
380
381 IF (i_file_id IS NOT NULL) THEN
382
383 x_cid := x_cid || i_file_id;
384 x_cid := x_cid || ATTACHMENT_CID_DELIMITOR;
385
386 -- append filename
387 l_dynamic_sql_str0 := 'SELECT file_name
388 FROM fnd_lobs
389 WHERE file_id = ' || i_file_id;
390 EXECUTE IMMEDIATE l_dynamic_sql_str0 INTO l_file_name;
391 IF (l_file_name IS NOT NULL) THEN
392
393 x_cid := x_cid || l_file_name;
394
395 END IF;
396
397 ELSE
398
399 RAISE invalid_input;
400
401 END IF;
402
403 if (l_procedureEnabled) then
404 ecx_debug.pop(i_method_name);
405 end if;
406
407 EXCEPTION
408
409 WHEN invalid_input THEN
410
411 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.FORMULATE_CONTENT_ID INVALID INPUTS' || SQLERRM;
412 ecx_utils.i_ret_code := 0;
413 if(l_unexpectedEnabled) then
414 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
415 end if;
416 if (l_procedureEnabled) then
417 ecx_debug.pop(i_method_name);
418 end if;
419 RAISE ecx_utils.PROGRAM_EXIT;
420
421 WHEN OTHERS THEN
422
423 if(l_unexpectedEnabled) then
424 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
425 'ECX_ATTACHMENT.FORMULATE_CONTENT_ID');
426 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
427 end if;
428 ecx_utils.error_type := 30;
429 ecx_utils.i_ret_code := 2;
430 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.FORMULATE_CONTENT_ID';
431 if (l_procedureEnabled) then
432 ecx_debug.pop(i_method_name);
433 end if;
434 RAISE ecx_utils.PROGRAM_EXIT;
435
436 END formulate_content_id;
437
438 PROCEDURE register_attachment_offline(
439 i_cid IN VARCHAR2,
440 i_file_id IN NUMBER,
441 i_data_type IN NUMBER
442 ) AS
443 i_method_name varchar2(2000) := 'ecx_attachment.register_attachment_offline';
444 l_is_append BOOLEAN := TRUE;
445 l_parameterlist wf_parameter_list_t;
446 l_attachments VARCHAR2(2000) := NULL;
447 l_install_mode VARCHAR2(200) := NULL;
448 l_progress VARCHAR2(4) := NULL;
449 l_records ecx_attachment.attachment_records_type;
450 mode_not_support EXCEPTION;
451 invalid_input EXCEPTION;
452
453 BEGIN
454
455 if (l_procedureEnabled) then
456 ecx_debug.push(i_method_name);
457 end if;
458 if(l_statementEnabled) then
459 ecx_debug.log(l_statement,'i_cid', i_cid,i_method_name);
460 ecx_debug.log(l_statement,'i_file_id', i_file_id,i_method_name);
461 ecx_debug.log(l_statement,'i_data_type', i_data_type,i_method_name);
462 end if;
463
464
465 -- Prelimnary check of inputs validity
466 IF ((i_cid is NULL) OR (i_file_id is NULL)) THEN
467
468 RAISE invalid_input;
469
470 END IF;
471
472 -- Check mode
473 l_install_mode := wf_core.translate('WF_INSTALL');
474 IF (l_install_mode = 'EMBEDDED') THEN
475
476 -- Retrieve ECX_ATTACHMENT name/value pairs, which may have registered earlier
477 l_progress := '0000';
478 l_attachments := i_attachment_maps;
479
480 l_attachments := RTRIM(l_attachments);
481 l_attachments := LTRIM(l_attachments);
482 IF ((l_attachments is NULL) OR (l_attachments = ' ')) THEN
483
484 -- No attachments yet, just append the current i_cid and i_file_id
485 -- into the ecx_utils.g_event's ecx_attachment.ECX_UTIL_EVENT_ATTACHMENT name/value pair
486 l_progress := '0001';
487 l_attachments := i_cid || ATTACHMENT_FIELD_DELIMITOR ||
488 i_file_id || ATTACHMENT_FIELD_DELIMITOR ||
489 i_data_type || ATTACHMENT_RECORD_DELIMITOR;
490
491 ELSE
492
493 -- There are attachments registered already, validate the current
494 -- i_cid and i_file_id against the previous ones
495 l_progress := '0002';
496 get_attachment_records(l_attachments, l_records);
497 IF (l_records.COUNT > 0) THEN
498
502 IF (l_records(i).cid = i_cid) THEN
499 -- validate the current i_cid and i_file_id against the previous records
500 FOR i IN l_records.FIRST .. l_records.LAST LOOP
501
503
504 -- Same cid should have the same file_id, in this case,
505 -- nothing would be appended in the l_attachments
506 l_progress := '0003';
507 IF (l_records(i).fid = i_file_id) THEN
508
509 l_is_append := FALSE;
510 EXIT;
511
512 ELSE
513
514 -- Same cid but different file_id, something must be wrong
515 RAISE invalid_input;
516
517 END IF;
518
519 ELSE
520
521 l_is_append := (l_is_append AND TRUE);
522
523 END IF;
524
525 END LOOP;
526
527 IF (l_is_append) THEN
528
529 -- Append this new i_cid
530 l_progress := '0004';
531 l_attachments := l_attachments || i_cid || ATTACHMENT_FIELD_DELIMITOR ||
532 i_file_id || ATTACHMENT_FIELD_DELIMITOR ||
533 i_data_type || ATTACHMENT_RECORD_DELIMITOR;
534
535 END IF;
536
537 ELSE
538
539 -- Should not come here, but for double-safety
540 l_progress := '0005';
541 l_attachments := i_cid || ATTACHMENT_FIELD_DELIMITOR ||
542 i_file_id || ATTACHMENT_FIELD_DELIMITOR ||
543 i_data_type || ATTACHMENT_RECORD_DELIMITOR;
544
545 END IF;
546
547 END IF;
548
549 -- Push the newly modified
550 i_attachment_maps := l_attachments;
551
552 ELSE
553
554 -- Standalone mode is not yet supported
555 RAISE mode_not_support;
556
557 END IF; -- EMBEDDED MODE
558
559 if (l_procedureEnabled) then
560 ecx_debug.pop(i_method_name);
561 end if;
562
563 EXCEPTION
564
565 WHEN invalid_input THEN
566
567 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.REGISTER_ATTACHMENT_OFFLINE INVALID INPUT: CID or FID '|| SQLERRM;
568 ecx_utils.i_ret_code := 0;
569 if(l_unexpectedEnabled) then
570 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
571 end if;
572 if (l_procedureEnabled) then
573 ecx_debug.pop(i_method_name);
574 end if;
575 RAISE ecx_utils.PROGRAM_EXIT;
576
577 WHEN mode_not_support THEN
578
579 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.REGISTER_ATTACHMENT_OFFLINE MODE NOT SUPPORT' || SQLERRM;
580 ecx_utils.i_ret_code := 0;
581 if(l_unexpectedEnabled) then
582 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
583 end if;
584 if (l_procedureEnabled) then
585 ecx_debug.pop(i_method_name);
586 end if;
587 RAISE ecx_utils.PROGRAM_EXIT;
588
589 WHEN OTHERS THEN
590
591 if(l_unexpectedEnabled) then
592 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
593 'ECX_ATTACHMENT.REGISTER_ATTACHMENT_OFFLINE');
594 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
595 end if;
596 ecx_utils.error_type := 30;
597 ecx_utils.i_ret_code := 2;
598 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.REGISTER_ATTACHMENT_OFFLINE';
599 if (l_procedureEnabled) then
600 ecx_debug.pop(i_method_name);
601 end if;
602 RAISE ecx_utils.PROGRAM_EXIT;
603
604 END register_attachment_offline;
605
606
607 PROCEDURE register_attachment(
608 i_cid IN VARCHAR2,
609 i_file_id IN NUMBER,
610 i_data_type IN NUMBER
611 ) AS
612 i_method_name varchar2(2000) := 'ecx_attachment.register_attachment';
613 l_is_append BOOLEAN := TRUE;
614 l_parameterlist wf_parameter_list_t;
615 l_attachments VARCHAR2(2000) := NULL;
616 l_install_mode VARCHAR2(200) := NULL;
617 l_progress VARCHAR2(4) := NULL;
618 l_records ecx_attachment.attachment_records_type;
619 mode_not_support EXCEPTION;
620 invalid_input EXCEPTION;
621
622 BEGIN
623
624 if (l_procedureEnabled) then
625 ecx_debug.push(i_method_name);
626 end if;
627 if(l_statementEnabled) then
628 ecx_debug.log(l_statement,'i_cid', i_cid,i_method_name);
629 ecx_debug.log(l_statement,'i_file_id', i_file_id,i_method_name);
630 ecx_debug.log(l_statement,'i_data_type', i_data_type,i_method_name);
631 end if;
632
633
634 -- Prelimnary check of inputs validity
635 IF ((i_cid is NULL) OR (i_file_id is NULL)) THEN
636
637 RAISE invalid_input;
638
639 END IF;
640
641 -- Check mode
642 -- ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
646 IF (l_install_mode = 'EMBEDDED') THEN
643 -- IF (ecx_utils.g_install_mode = 'EMBEDDED') THEN
644
645 l_install_mode := wf_core.translate('WF_INSTALL');
647
648 -- Retrieve ECX_ATTACHMENT name/value pairs from the
649 -- ecx_utils.g_event workflow event
650 l_progress := '0000';
651 IF (ecx_utils.g_event is NULL)
652 THEN
653 wf_event_t.initialize(ecx_utils.g_event);
654 END IF;
655 l_parameterlist := wf_event_t.getParameterList(ecx_utils.g_event);
656 l_attachments := wf_event.getValueForParameter(ecx_attachment.ECX_UTIL_EVENT_ATTACHMENT,
657 l_parameterlist);
658
659 l_attachments := RTRIM(l_attachments);
660 l_attachments := LTRIM(l_attachments);
661 IF ((l_attachments is NULL) OR (l_attachments = ' ')) THEN
662
663 -- No attachments yet, just append the current i_cid and i_file_id
664 -- into the ecx_utils.g_event's ecx_attachment.ECX_UTIL_EVENT_ATTACHMENT name/value pair
665 l_progress := '0001';
666 l_attachments := i_cid || ATTACHMENT_FIELD_DELIMITOR ||
667 i_file_id || ATTACHMENT_FIELD_DELIMITOR ||
668 i_data_type || ATTACHMENT_RECORD_DELIMITOR;
669
670 ELSE
671
672 -- There are attachments registered already, validate the current
673 -- i_cid and i_file_id against the previous ones
674 l_progress := '0002';
675 get_attachment_records(l_attachments, l_records);
676 IF (l_records.COUNT > 0) THEN
677
678 -- validate the current i_cid and i_file_id against the previous records
679 FOR i IN l_records.FIRST .. l_records.LAST LOOP
680
681 IF (l_records(i).cid = i_cid) THEN
682
683 -- Same cid should have the same file_id, in this case,
684 -- nothing would be appended in the l_attachments
685 l_progress := '0003';
686 IF (l_records(i).fid = i_file_id) THEN
687
688 l_is_append := FALSE;
689 EXIT;
690
691 ELSE
692
693 -- Same cid but different file_id, something must be wrong
694 RAISE invalid_input;
695
696 END IF;
697
698 ELSE
699
700 l_is_append := (l_is_append AND TRUE);
701
702 END IF;
703
704 END LOOP;
705
706 IF (l_is_append) THEN
707
708 -- Append this new i_cid
709 l_progress := '0004';
710 l_attachments := l_attachments || i_cid || ATTACHMENT_FIELD_DELIMITOR ||
711 i_file_id || ATTACHMENT_FIELD_DELIMITOR ||
712 i_data_type || ATTACHMENT_RECORD_DELIMITOR;
713
714 END IF;
715
716 ELSE
717
718 -- Should not come here, but for double-safety
719 l_progress := '0005';
720 l_attachments := i_cid || ATTACHMENT_FIELD_DELIMITOR ||
721 i_file_id || ATTACHMENT_FIELD_DELIMITOR ||
722 i_data_type || ATTACHMENT_RECORD_DELIMITOR;
723
724 END IF;
725
726 END IF;
727
728 -- Push the newly modified
729 ecx_utils.g_event.addParameterToList(ecx_attachment.ECX_UTIL_EVENT_ATTACHMENT, l_attachments);
730
731 ELSE
732
733 -- Standalone mode is not yet supported
734 RAISE mode_not_support;
735
736 END IF; -- EMBEDDED MODE
737
738 if (l_procedureEnabled) then
739 ecx_debug.pop(i_method_name);
740 end if;
741
742 EXCEPTION
743
744 WHEN invalid_input THEN
745
746 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.REGISTER_ATTACHMENT INVALID INPUT: CID or FID '|| SQLERRM;
747 ecx_utils.i_ret_code := 0;
748 if(l_unexpectedEnabled) then
749 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
750 end if;
751 if (l_procedureEnabled) then
752 ecx_debug.pop(i_method_name);
753 end if;
754 RAISE ecx_utils.PROGRAM_EXIT;
755
756 WHEN mode_not_support THEN
757
758 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.REGISTER_ATTACHMENT MODE NOT SUPPORT' || SQLERRM;
759 ecx_utils.i_ret_code := 0;
760 if(l_unexpectedEnabled) then
761 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
762 end if;
763 if (l_procedureEnabled) then
764 ecx_debug.pop(i_method_name);
765 end if;
766 RAISE ecx_utils.PROGRAM_EXIT;
767
768 WHEN OTHERS THEN
769
770 if(l_unexpectedEnabled) then
771 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
772 'ECX_ATTACHMENT.REGISTER_ATTACHMENT');
773 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
774 end if;
775 ecx_utils.error_type := 30;
776 ecx_utils.i_ret_code := 2;
777 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.REGISTER_ATTACHMENT';
778 if (l_procedureEnabled) then
779 ecx_debug.pop(i_method_name);
780 end if;
784 END register_attachment;
781 RAISE ecx_utils.PROGRAM_EXIT;
782
783
785
786
787 PROCEDURE register_attachment_offline(
788 i_entity_name IN VARCHAR2,
789 i_pk1_value IN VARCHAR2,
790 i_pk2_value IN VARCHAR2,
791 i_pk3_value IN VARCHAR2,
792 i_pk4_value IN VARCHAR2,
793 i_pk5_value IN VARCHAR2,
794 i_file_id IN NUMBER,
795 i_data_type IN NUMBER,
796 x_cid OUT NOCOPY VARCHAR2
797 ) AS
798
799 i_method_name varchar2(2000) := 'ecx_attachment.register_attachment_offline';
800 BEGIN
801
802 if (l_procedureEnabled) then
803 ecx_debug.push(i_method_name);
804 end if;
805
806
807 formulate_content_id(i_file_id,
808 i_entity_name,
809 i_pk1_value,
810 i_pk2_value,
811 i_pk3_value,
812 i_pk4_value,
813 i_pk5_value,
814 x_cid);
815
816 register_attachment_offline(x_cid, i_file_id, i_data_type);
817
818 if (l_procedureEnabled) then
819 ecx_debug.pop(i_method_name);
820 end if;
821
822 EXCEPTION
823
824 WHEN OTHERS THEN
825
826 if(l_unexpectedEnabled) then
827 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
828 'ECX_ATTACHMENT.REGISTER_ATTACHMENT_OFFLINE');
829 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
830 end if;
831 ecx_utils.error_type := 30;
832 ecx_utils.i_ret_code := 2;
833 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.REGISTER_ATTACHMENT_OFFLINE';
834 if (l_procedureEnabled) then
835 ecx_debug.pop(i_method_name);
836 end if;
837 RAISE ecx_utils.PROGRAM_EXIT;
838
839 END register_attachment_offline;
840
841
842 PROCEDURE register_attachment(
843 i_entity_name IN VARCHAR2,
844 i_pk1_value IN VARCHAR2,
845 i_pk2_value IN VARCHAR2,
846 i_pk3_value IN VARCHAR2,
847 i_pk4_value IN VARCHAR2,
848 i_pk5_value IN VARCHAR2,
849 i_file_id IN NUMBER,
850 i_data_type IN NUMBER,
851 x_cid OUT NOCOPY VARCHAR2
852 ) AS
853
854 i_method_name varchar2(2000) := 'ecx_attachment.register_attachment ';
855 BEGIN
856
857 if (l_procedureEnabled) then
858 ecx_debug.push(i_method_name);
859 end if;
860
861 formulate_content_id(i_file_id,
862 i_entity_name,
863 i_pk1_value,
864 i_pk2_value,
865 i_pk3_value,
866 i_pk4_value,
867 i_pk5_value,
868 x_cid);
869
870 register_attachment(x_cid, i_file_id, i_data_type);
871
872 if (l_procedureEnabled) then
873 ecx_debug.pop(i_method_name);
874 end if;
875
876 EXCEPTION
877
878 WHEN OTHERS THEN
879
880 if(l_unexpectedEnabled) then
881 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
882 'ECX_ATTACHMENT.REGISTER_ATTACHMENT');
883 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
884 end if;
885 ecx_utils.error_type := 30;
886 ecx_utils.i_ret_code := 2;
887 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.REGISTER_ATTACHMENT';
888 if (l_procedureEnabled) then
889 ecx_debug.pop(i_method_name);
890 end if;
891 RAISE ecx_utils.PROGRAM_EXIT;
892
893 END register_attachment;
894
895
896 PROCEDURE remove_attachmentMaps_offline(
897 x_attachment_maps OUT NOCOPY VARCHAR2
898 ) AS
899
900 BEGIN
901
902 ecx_debug.push('ECX_ATTACHMENT.REMOVE_ATTACHMENTMAPS_OFFLINE');
903
904 x_attachment_maps := i_attachment_maps;
905 i_attachment_maps := NULL;
906
907 ecx_debug.pop('ECX_ATTACHMENT.REMOVE_ATTACHMENTMAPS_OFFLINE');
908
909 END remove_attachmentMaps_offline;
910
911
912
913 PROCEDURE get_attachment_records(
914 i_attachments IN VARCHAR2,
915 x_records OUT NOCOPY attachment_records_type
916 ) AS
917
918 i_method_name varchar2(2000) := 'ecx_attachment.get_attachment_records';
919 l_record_counter NUMBER := 0;
920 l_inner_position NUMBER := 0;
921 l_inner_begin NUMBER := 1;
922 l_occurrence NUMBER := 1;
923 l_begin NUMBER := 1;
924 l_position NUMBER := 0;
925 l_datatype NUMBER := NULL;
926 l_sdatatype VARCHAR2(256) := NULL;
927 l_fid NUMBER := NULL;
931 l_current_record VARCHAR2(300) := NULL;
928 l_sfid VARCHAR2(256) := NULL;
929 l_cid VARCHAR2(256) := NULL;
930 l_progress VARCHAR2(4) := NULL;
932 l_invalid_records EXCEPTION;
933
934 BEGIN
935
936 if (l_procedureEnabled) then
937 ecx_debug.push(i_method_name);
938 end if;
939 if(l_statementEnabled) then
940 ecx_debug.log(l_statement,'ei_attachments', i_attachments,i_method_name);
941 end if;
942
943
944 IF (i_attachments IS NOT NULL) THEN
945
946 l_progress := '0000';
947 LOOP
948
949 l_position := INSTR(i_attachments, ATTACHMENT_RECORD_DELIMITOR, 1, l_occurrence);
950
951 IF (l_position = 0) THEN
952
953 EXIT;
954
955 ELSE
956
957 l_current_record := SUBSTR(i_attachments, l_begin, l_position - l_begin + 1);
958
959 l_progress := '0001';
960 IF (l_current_record IS NOT NULL) THEN
961
962 -- Get CID
963 l_inner_position := INSTR(l_current_record, ATTACHMENT_FIELD_DELIMITOR, 1, 1);
964 IF (l_inner_position = 0) THEN
965
966 RAISE l_invalid_records;
967
968 ELSE
969
970 l_cid := SUBSTR(l_current_record,l_inner_begin, l_inner_position - l_inner_begin);
971 l_inner_begin := l_inner_position + 1;
972
973 END IF;
974 l_inner_position := 0;
975
976 -- GET FID
977 l_inner_position := INSTR(l_current_record, ATTACHMENT_FIELD_DELIMITOR, 1, 2);
978 IF (l_inner_position = 0) THEN
979
980 RAISE l_invalid_records;
981
982 ELSE
983
984 l_sfid := SUBSTR(l_current_record,l_inner_begin, l_inner_position - l_inner_begin);
985 l_fid := TO_NUMBER(l_sfid);
986 l_inner_begin := l_inner_position + 1;
987
988 END IF;
989 l_inner_position := 0;
990
991 -- GET datatype
992 l_inner_position := INSTR(l_current_record, ATTACHMENT_RECORD_DELIMITOR, 1, 1);
993 IF (l_inner_position = 0) THEN
994
995 RAISE l_invalid_records;
996
997 ELSE
998
999 l_sdatatype := SUBSTR(l_current_record,l_inner_begin, l_inner_position - l_inner_begin);
1000 l_datatype := TO_NUMBER(l_sdatatype);
1001
1002 END IF;
1003
1004 l_record_counter := l_record_counter + 1;
1005 x_records(l_record_counter).cid := l_cid;
1006 x_records(l_record_counter).fid := l_fid;
1007 x_records(l_record_counter).dataType := l_datatype;
1008
1009 END IF; -- l_current_record is NOT NULL
1010 l_inner_begin := 1;
1011
1012 END IF;
1013
1014
1015 l_begin := l_position + 1;
1016 l_current_record := NULL;
1017 l_occurrence := l_occurrence + 1;
1018
1019 END LOOP;
1020
1021 END IF;
1022
1023 if (l_procedureEnabled) then
1024 ecx_debug.pop(i_method_name);
1025 end if;
1026
1027 EXCEPTION
1028
1029 WHEN l_invalid_records THEN
1030
1031 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.GET_ATTACHMENT_RECORDS RECORDED ATTACHMENTS ARE WRONG '|| SQLERRM;
1032 ecx_utils.i_ret_code := 0;
1033 if(l_unexpectedEnabled) then
1034 ecx_debug.log(l_unexpected, ecx_utils.i_errbuf,i_method_name);
1035 end if;
1036 if (l_procedureEnabled) then
1037 ecx_debug.pop(i_method_name);
1038 end if;
1039 RAISE ecx_utils.PROGRAM_EXIT;
1040
1041
1042 END get_attachment_records;
1043
1044 PROCEDURE map_attachments(
1045 i_msgid IN RAW
1046 )AS
1047
1048 i_method_name varchar2(2000) := 'ecx_attachment.map_attachments';
1049 l_progress VARCHAR2(4) := NULL;
1050 l_attachments_info VARCHAR2(2000) := NULL;
1051 l_install_mode VARCHAR2(200) := NULL;
1052 l_parameterList wf_parameter_list_t;
1053 l_records ecx_attachment.attachment_records_type;
1054
1055 BEGIN
1056
1057 if (l_procedureEnabled) then
1058 ecx_debug.push(i_method_name);
1059 end if;
1060 if(l_statementEnabled) then
1061 ecx_debug.log(l_statement,'i_msgid', i_msgid,i_method_name);
1062 end if;
1063
1064 l_install_mode := wf_core.translate('WF_INSTALL');
1065 IF (ecx_utils.g_event is NULL) THEN
1066
1067 wf_event_t.initialize(ecx_utils.g_event);
1068
1069 END IF;
1070 l_parameterList := wf_event_t.getParameterList(ecx_utils.g_event);
1071 l_attachments_info := wf_event.getValueForParameter(ecx_attachment.ECX_UTIL_EVENT_ATTACHMENT,
1072 l_parameterList);
1073
1074 l_attachments_info := RTRIM(l_attachments_info);
1075 l_attachments_info := LTRIM(l_attachments_info);
1076 if(l_statementEnabled) then
1077 ecx_debug.log(l_statement,'l_attachments_info', l_attachments_info,i_method_name);
1078 end if;
1079
1080 -- Book keep the attachments info if it exists
1084 get_attachment_records(l_attachments_info, l_records);
1081 IF (l_attachments_info is NOT NULL) THEN
1082
1083 l_progress := '0000';
1085 IF (l_records.COUNT > 0) THEN
1086
1087 l_progress := '0001';
1088 FOR i IN l_records.FIRST .. l_records.LAST LOOP
1089
1090 INSERT INTO ecx_attachment_maps
1091 (msgid,
1092 cid,
1093 fid,
1094 dataType,
1095 orderIndex,
1096 nestParentIndex)
1097 VALUES
1098 (i_msgid,
1099 l_records(i).cid,
1100 l_records(i).fid,
1101 l_records(i).dataType,
1102 i,
1103 0 -- 0 refers to the main doc
1104 );
1105
1106 END LOOP;
1107
1108
1109 END IF;
1110
1111 END IF;
1112
1113 if (l_procedureEnabled) then
1114 ecx_debug.pop(i_method_name);
1115 end if;
1116
1117 EXCEPTION
1118
1119 WHEN OTHERS THEN
1120
1121 if(l_unexpectedEnabled) then
1122 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1123 'ECX_ATTACHMENT.MAP_ATTACHMENTS');
1124 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1125 end if;
1126 ecx_utils.error_type := 30;
1127 ecx_utils.i_ret_code := 2;
1128 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.MAP_ATTACHMENTS';
1129 if (l_procedureEnabled) then
1130 ecx_debug.pop(i_method_name);
1131 end if;
1132 RAISE ecx_utils.PROGRAM_EXIT;
1133
1134 END map_attachments;
1135
1136 PROCEDURE map_attachments(
1137 i_event IN WF_EVENT_T,
1138 i_msgid IN RAW
1139 )AS
1140 i_method_name varchar2(2000) := 'ecx_attachment.map_attachments';
1141 l_progress VARCHAR2(4) := NULL;
1142 l_attachments_info VARCHAR2(2000) := NULL;
1143 l_install_mode VARCHAR2(200) := NULL;
1144 l_parameterList wf_parameter_list_t;
1145 l_records ecx_attachment.attachment_records_type;
1146
1147 BEGIN
1148
1149 if (l_procedureEnabled) then
1150 ecx_debug.push(i_method_name);
1151 end if;
1152
1153 if(l_statementEnabled) then
1154 ecx_debug.log(l_statement,'i_msgid', i_msgid,i_method_name);
1155 end if;
1156
1157 l_install_mode := wf_core.translate('WF_INSTALL');
1158 IF (i_event is not NULL) THEN
1159
1160 l_parameterList := wf_event_t.getParameterList(i_event);
1161 l_attachments_info := wf_event.getValueForParameter(ecx_attachment.ECX_UTIL_EVENT_ATTACHMENT,
1162 l_parameterList);
1163
1164 l_attachments_info := RTRIM(l_attachments_info);
1165 l_attachments_info := LTRIM(l_attachments_info);
1166 if(l_statementEnabled) then
1167 ecx_debug.log(l_statement,'l_attachments_info', l_attachments_info,i_method_name);
1168 end if;
1169
1170 -- Book keep the attachments info if it exists
1171 IF (l_attachments_info is NOT NULL) THEN
1172
1173 l_progress := '0000';
1174 get_attachment_records(l_attachments_info, l_records);
1175 IF (l_records.COUNT > 0) THEN
1176
1177 l_progress := '0001';
1178 FOR i IN l_records.FIRST .. l_records.LAST LOOP
1179
1180 INSERT INTO ecx_attachment_maps
1181 (msgid,
1182 cid,
1183 fid,
1184 dataType,
1185 orderIndex,
1186 nestParentIndex)
1187 VALUES
1188 (i_msgid,
1189 l_records(i).cid,
1190 l_records(i).fid,
1191 l_records(i).dataType,
1192 i,
1193 0 -- 0 refers to the main doc
1194 );
1195
1196 END LOOP;
1197
1198
1199 END IF;
1200
1201 END IF;
1202
1203 END IF;
1204
1205 if (l_procedureEnabled) then
1206 ecx_debug.pop(i_method_name);
1207 end if;
1208
1209 EXCEPTION
1210
1211 WHEN OTHERS THEN
1212
1213 if(l_unexpectedEnabled) then
1214 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1215 'ECX_ATTACHMENT.MAP_ATTACHMENTS');
1216 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1217 end if;
1218 ecx_utils.error_type := 30;
1219 ecx_utils.i_ret_code := 2;
1220 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.MAP_ATTACHMENTS';
1221 if (l_procedureEnabled) then
1222 ecx_debug.pop(i_method_name);
1223 end if;
1224 RAISE ecx_utils.PROGRAM_EXIT;
1225
1226 END map_attachments;
1227
1228
1229
1230 PROCEDURE remap_attachments(
1231 i_msgid IN RAW
1232 )AS
1233 i_method_name varchar2(2000) := 'ecx_attachment.remap_attachments';
1234 l_progress VARCHAR2(4) := NULL;
1238 SELECT *
1235 l_in_aqMsgid RAW(16) := NULL;
1236 l_map_row ECX_ATTACHMENT_MAPS%ROWTYPE;
1237 CURSOR l_map_cursor (msgid RAW) IS
1239 FROM ecx_attachment_maps
1240 WHERE MSGID = msgid;
1241
1242 BEGIN
1243
1244 if (l_procedureEnabled) then
1245 ecx_debug.push(i_method_name);
1246 end if;
1247 if(l_statementEnabled) then
1248 ecx_debug.log(l_statement,'i_msgid of the passthrough document', i_msgid,i_method_name);
1249 end if;
1250
1251 l_progress := '0000';
1252
1253 -- retrieve the original AQ message ID on the ecx_inqueue
1254 SELECT msgId
1255 INTO l_in_aqMsgid
1256 FROM ecx_doclogs
1257 WHERE OUT_MSGID = i_msgid;
1258
1259 l_progress := '0001';
1260 IF (l_in_aqMsgid IS NOT NULL) THEN
1261
1262 FOR l_map_row IN l_map_cursor(l_in_aqMsgid)
1263 LOOP
1264
1265 IF (l_map_row.msgid = l_in_aqMsgid)
1266 THEN
1267
1268 INSERT INTO ecx_attachment_maps (msgId,
1269 cId,
1270 fId,
1271 dataType,
1272 orderIndex,
1273 nestParentIndex
1274 )
1275 VALUES
1276 (
1277 i_msgid,
1278 l_map_row.cId,
1279 l_map_row.fId,
1280 l_map_row.dataType,
1281 l_map_row.orderIndex,
1282 l_map_row.nestParentIndex
1283 );
1284
1285 END IF;
1286
1287 END LOOP;
1288
1289 END IF;
1290 l_progress := '0002';
1291
1292 if (l_procedureEnabled) then
1293 ecx_debug.pop(i_method_name);
1294 end if;
1295
1296
1297
1298 EXCEPTION
1299
1300 WHEN OTHERS THEN
1301
1302 if(l_unexpectedEnabled) then
1303 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1304 'ECX_ATTACHMENT.REMAP_ATTACHMENTS');
1305 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1306 end if;
1307 ecx_utils.error_type := 30;
1308 ecx_utils.i_ret_code := 2;
1309 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.MAP_ATTACHMENTS';
1310 if (l_procedureEnabled) then
1311 ecx_debug.pop(i_method_name);
1312 end if;
1313
1314 RAISE ecx_utils.PROGRAM_EXIT;
1315
1316 END remap_attachments;
1317
1318
1319
1320 PROCEDURE retrieve_attachment(
1321 i_msgid IN RAW,
1322 i_cid IN VARCHAR2,
1323 x_file_name OUT NOCOPY VARCHAR2,
1324 x_file_content_type OUT NOCOPY VARCHAR2,
1325 x_file_data OUT NOCOPY BLOB,
1326 x_ora_charset OUT NOCOPY VARCHAR2,
1327 x_file_format OUT NOCOPY VARCHAR2
1328 ) AS
1329 i_method_name varchar2(2000) := 'ecx_attachment.retrieve_attachment';
1330 l_file_id NUMBER := NULL;
1331 l_datatype NUMBER := NULL;
1332 l_mode VARCHAR2(200) := NULL;
1333 l_progress VARCHAR2(4) := NULL;
1334 l_install_mode VARCHAR2(200) := NULL;
1335 l_dynamic_sql_str0 VARCHAR2(2000) := NULL;
1336 mode_not_support EXCEPTION;
1337 mode_mismatch EXCEPTION;
1338
1339 BEGIN
1340
1341 if (l_procedureEnabled) then
1342 ecx_debug.push(i_method_name);
1343 end if;
1344 if(l_statementEnabled) then
1345 ecx_debug.log(l_statement,'i_msgid', i_msgid,i_method_name);
1346 ecx_debug.log(l_statement,'i_cid', i_cid,i_method_name);
1347 end if;
1348
1349 -- Check mode
1350 -- ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
1351 -- IF (ecx_utils.g_install_mode = 'EMBEDDED') THEN
1352
1353 l_install_mode := wf_core.translate('WF_INSTALL');
1354 IF (l_install_mode = 'EMBEDDED') THEN
1355
1356 l_progress := '0000';
1357 SELECT fid, datatype
1358 INTO l_file_id, l_datatype
1359 FROM ecx_attachment_maps
1360 WHERE msgid = i_msgid AND cid = i_cid;
1361
1362 IF ((l_file_id is NOT NULL) AND (l_datatype is NOT NULL)) THEN
1363
1364 l_progress := '0001';
1365
1366 IF (l_datatype = ecx_attachment.EMBEDDED_LOB_DATA_TYPE) THEN
1367
1368 l_progress := '0002';
1369 l_dynamic_sql_str0 := 'SELECT file_name, file_content_type,
1370 file_data, oracle_charset, file_format
1371 FROM fnd_lobs
1372 WHERE file_id = ' || l_file_id;
1373 EXECUTE IMMEDIATE l_dynamic_sql_str0
1374 INTO x_file_name, x_file_content_type,
1375 x_file_data, x_ora_charset, x_file_format;
1376
1380
1377 ELSIF (l_datatype = ecx_attachment.EMBEDDED_SHORTTEXT_DATA_TYPE) THEN
1378
1379 x_file_content_type := 'text';
1381 -- XXX
1382 -- ELSIF other types one by one
1383 -- XXX
1384
1385 END IF;
1386
1387 END IF; -- ((l_file_id is NOT NULL) AND (l_datatype is NOT NULL))
1388
1389 ELSE
1390
1391 -- Standalone mode is not yet supported
1392 RAISE mode_not_support;
1393
1394 END IF; -- EMBEDDED MODE
1395
1396 if (l_procedureEnabled) then
1397 ecx_debug.pop(i_method_name);
1398 end if;
1399
1400 EXCEPTION
1401
1402 WHEN no_data_found THEN
1403
1404 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT NO DATA FOUND IN ECX_ATTACHMENT_MAP TABLE' || SQLERRM;
1405 ecx_utils.i_ret_code := 0;
1406 if(l_unexpectedEnabled) then
1407 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1408 end if;
1409 if (l_procedureEnabled) then
1410 ecx_debug.pop(i_method_name);
1411 end if;
1412 RAISE ecx_utils.PROGRAM_EXIT;
1413
1414
1415 WHEN mode_not_support THEN
1416
1417 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT MODE NOT SUPPORT' || SQLERRM;
1418 ecx_utils.i_ret_code := 0;
1419 if(l_unexpectedEnabled) then
1420 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1421 end if;
1422 if (l_procedureEnabled) then
1423 ecx_debug.pop(i_method_name);
1424 end if;
1425 RAISE ecx_utils.PROGRAM_EXIT;
1426
1427
1428 WHEN mode_mismatch THEN
1429
1430 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT MODE MISMATCHES' || SQLERRM;
1431 ecx_utils.i_ret_code := 0;
1432 if(l_unexpectedEnabled) then
1433 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1434 end if;
1435 if (l_procedureEnabled) then
1436 ecx_debug.pop(i_method_name);
1437 end if;
1438 RAISE ecx_utils.PROGRAM_EXIT;
1439
1440 WHEN OTHERS THEN
1441
1442 if(l_unexpectedEnabled) then
1443 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1444 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT');
1445 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1446 end if;
1447 ecx_utils.error_type := 30;
1448 ecx_utils.i_ret_code := 2;
1449 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.RETRIEVE_ATTACHMENT';
1450 if (l_procedureEnabled) then
1451 ecx_debug.pop(i_method_name);
1452 end if;
1453 RAISE ecx_utils.PROGRAM_EXIT;
1454
1455 END retrieve_attachment;
1456
1457
1458 PROCEDURE retrieve_attachment(
1459 i_msgid IN RAW,
1460 i_cid IN VARCHAR2,
1461 x_file_name OUT NOCOPY VARCHAR2,
1462 x_file_content_type OUT NOCOPY VARCHAR2,
1463 x_file_data OUT NOCOPY BLOB,
1464 x_language OUT NOCOPY VARCHAR2,
1465 x_ora_charset OUT NOCOPY VARCHAR2,
1466 x_file_format OUT NOCOPY VARCHAR2
1467 ) AS
1468 i_method_name varchar2(2000) := 'ecx_attachment.retrieve_attachment';
1469 l_file_id NUMBER := NULL;
1470 l_datatype NUMBER := NULL;
1471 l_mode VARCHAR2(200) := NULL;
1472 l_progress VARCHAR2(4) := NULL;
1473 l_install_mode VARCHAR2(200) := NULL;
1474 l_dynamic_sql_str0 VARCHAR2(2000) := NULL;
1475 mode_not_support EXCEPTION;
1476 mode_mismatch EXCEPTION;
1477
1478 BEGIN
1479
1480 if (l_procedureEnabled) then
1481 ecx_debug.push(i_method_name);
1482 end if;
1483 if(l_statementEnabled) then
1484 ecx_debug.log(l_statement,'i_msgid', i_msgid,i_method_name);
1485 ecx_debug.log(l_statement,'i_cid', i_cid,i_method_name);
1486 end if;
1487
1488 -- Check mode
1489 -- ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
1490 -- IF (ecx_utils.g_install_mode = 'EMBEDDED') THEN
1491
1492 l_install_mode := wf_core.translate('WF_INSTALL');
1493 IF (l_install_mode = 'EMBEDDED') THEN
1494
1495 l_progress := '0000';
1496 SELECT fid, datatype
1497 INTO l_file_id, l_datatype
1498 FROM ecx_attachment_maps
1499 WHERE msgid = i_msgid AND cid = i_cid;
1500
1501 IF ((l_file_id is NOT NULL) AND (l_datatype is NOT NULL)) THEN
1502
1503 l_progress := '0001';
1504
1505 IF (l_datatype = ecx_attachment.EMBEDDED_LOB_DATA_TYPE) THEN
1506
1507 l_progress := '0002';
1508 l_dynamic_sql_str0 := 'SELECT file_name, file_content_type,
1509 file_data, oracle_charset, file_format,
1510 language
1511 FROM fnd_lobs
1512 WHERE file_id = ' || l_file_id;
1513 EXECUTE IMMEDIATE l_dynamic_sql_str0
1514 INTO x_file_name, x_file_content_type,
1515 x_file_data, x_ora_charset, x_file_format,
1519
1516 x_language;
1517
1518 ELSIF (l_datatype = ecx_attachment.EMBEDDED_SHORTTEXT_DATA_TYPE) THEN
1520 x_file_content_type := 'text';
1521
1522 -- XXX
1523 -- ELSIF other types one by one
1524 -- XXX
1525
1526 END IF;
1527
1528 END IF; -- ((l_file_id is NOT NULL) AND (l_datatype is NOT NULL))
1529
1530 ELSE
1531
1532 -- Standalone mode is not yet supported
1533 RAISE mode_not_support;
1534
1535 END IF; -- EMBEDDED MODE
1536
1537 if (l_procedureEnabled) then
1538 ecx_debug.pop(i_method_name);
1539 end if;
1540
1541
1542 EXCEPTION
1543
1544 WHEN no_data_found THEN
1545
1546 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT NO DATA FOUND IN ECX_ATTACHMENT_MAP TABLE' || SQLERRM;
1547 ecx_utils.i_ret_code := 0;
1548 if(l_unexpectedEnabled) then
1549 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1550 end if;
1551 if (l_procedureEnabled) then
1552 ecx_debug.pop(i_method_name);
1553 end if;
1554
1555 RAISE ecx_utils.PROGRAM_EXIT;
1556
1557
1558 WHEN mode_not_support THEN
1559
1560 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT MODE NOT SUPPORT' || SQLERRM;
1561 ecx_utils.i_ret_code := 0;
1562 if(l_unexpectedEnabled) then
1563 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1564 end if;
1565 if (l_procedureEnabled) then
1566 ecx_debug.pop(i_method_name);
1567 end if;
1568 RAISE ecx_utils.PROGRAM_EXIT;
1569
1570
1571 WHEN mode_mismatch THEN
1572
1573 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT MODE MISMATCHES' || SQLERRM;
1574 ecx_utils.i_ret_code := 0;
1575 if(l_unexpectedEnabled) then
1576 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1577 end if;
1578 if (l_procedureEnabled) then
1579 ecx_debug.pop(i_method_name);
1580 end if;
1581 RAISE ecx_utils.PROGRAM_EXIT;
1582
1583 WHEN OTHERS THEN
1584
1585 if(l_unexpectedEnabled) then
1586 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1587 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT');
1588 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1589 end if;
1590 ecx_utils.error_type := 30;
1591 ecx_utils.i_ret_code := 2;
1592 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.RETRIEVE_ATTACHMENT';
1593 if (l_procedureEnabled) then
1594 ecx_debug.pop(i_method_name);
1595 end if;
1596 RAISE ecx_utils.PROGRAM_EXIT;
1597
1598 END retrieve_attachment;
1599
1600
1601 PROCEDURE retrieve_attachment(
1602 i_file_id IN NUMBER,
1603 i_data_type IN NUMBER,
1604 x_file_name OUT NOCOPY VARCHAR2,
1605 x_file_content_type OUT NOCOPY VARCHAR2,
1606 x_file_data OUT NOCOPY BLOB,
1607 x_language OUT NOCOPY VARCHAR2,
1608 x_ora_charset OUT NOCOPY VARCHAR2,
1609 x_file_format OUT NOCOPY VARCHAR2
1610 ) AS
1611 i_method_name varchar2(2000) := 'ecx_attachment.retrieve_attachment';
1612 l_file_id NUMBER := NULL;
1613 l_datatype NUMBER := NULL;
1614 l_mode VARCHAR2(200) := NULL;
1615 l_progress VARCHAR2(4) := NULL;
1616 l_install_mode VARCHAR2(200) := NULL;
1617 l_dynamic_sql_str0 VARCHAR2(2000) := NULL;
1618 mode_not_support EXCEPTION;
1619 mode_mismatch EXCEPTION;
1620
1621 BEGIN
1622
1623 if (l_procedureEnabled) then
1624 ecx_debug.push(i_method_name);
1625 end if;
1626 if(l_statementEnabled) then
1627 ecx_debug.log(l_statement,'i_file_id', i_file_id,i_method_name);
1628 ecx_debug.log(l_statement,'i_data_type', i_data_type,i_method_name);
1629 end if;
1630
1631 -- Check mode
1632 l_install_mode := wf_core.translate('WF_INSTALL');
1633 IF (l_install_mode = 'EMBEDDED') THEN
1634
1635 l_progress := '0000';
1636 IF ((i_file_id is NOT NULL) AND (i_data_type is NOT NULL)) THEN
1637
1638 l_progress := '0001';
1639 IF (i_data_type = ecx_attachment.EMBEDDED_LOB_DATA_TYPE) THEN
1640
1641 l_progress := '0002';
1642 l_dynamic_sql_str0 := 'SELECT file_name, file_content_type,
1643 file_data, oracle_charset, file_format,
1644 language
1645 FROM fnd_lobs
1646 WHERE file_id = ' || i_file_id;
1647 EXECUTE IMMEDIATE l_dynamic_sql_str0
1648 INTO x_file_name, x_file_content_type,
1649 x_file_data, x_ora_charset, x_file_format,
1650 x_language;
1651
1652 ELSIF (i_data_type = ecx_attachment.EMBEDDED_SHORTTEXT_DATA_TYPE) THEN
1653
1654 x_file_content_type := 'text';
1655
1656 -- XXX
1657 -- ELSIF other types one by one
1658 -- XXX
1659
1660 END IF;
1661
1665
1662 END IF; -- ((i_file_id is NOT NULL) AND (i_data_type is NOT NULL))
1663
1664 ELSE
1666 -- Standalone mode is not yet supported
1667 RAISE mode_not_support;
1668
1669 END IF; -- EMBEDDED MODE
1670
1671 if (l_procedureEnabled) then
1672 ecx_debug.pop(i_method_name);
1673 end if;
1674
1675 EXCEPTION
1676
1677 WHEN no_data_found THEN
1678
1679 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT NO DATA FOUND IN ECX_ATTACHMENT_MAP TABLE' || SQLERRM;
1680 ecx_utils.i_ret_code := 0;
1681 if(l_unexpectedEnabled) then
1682 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1683 end if;
1684 if (l_procedureEnabled) then
1685 ecx_debug.pop(i_method_name);
1686 end if;
1687 RAISE ecx_utils.PROGRAM_EXIT;
1688
1689
1690 WHEN mode_not_support THEN
1691
1692 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT MODE NOT SUPPORT' || SQLERRM;
1693 ecx_utils.i_ret_code := 0;
1694 if(l_unexpectedEnabled) then
1695 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1696 end if;
1697 if (l_procedureEnabled) then
1698 ecx_debug.pop(i_method_name);
1699 end if;
1700 RAISE ecx_utils.PROGRAM_EXIT;
1701
1702
1703 WHEN mode_mismatch THEN
1704
1705 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT MODE MISMATCHES' || SQLERRM;
1706 ecx_utils.i_ret_code := 0;
1707 if(l_unexpectedEnabled) then
1708 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1709 end if;
1710 if (l_procedureEnabled) then
1711 ecx_debug.pop(i_method_name);
1712 end if;
1713 RAISE ecx_utils.PROGRAM_EXIT;
1714
1715 WHEN OTHERS THEN
1716
1717 if(l_unexpectedEnabled) then
1718 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1719 'ECX_ATTACHMENT.RETRIEVE_ATTACHMENT');
1720 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1721 end if;
1722 ecx_utils.error_type := 30;
1723 ecx_utils.i_ret_code := 2;
1724 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.RETRIEVE_ATTACHMENT';
1725 if (l_procedureEnabled) then
1726 ecx_debug.pop(i_method_name);
1727 end if;
1728 RAISE ecx_utils.PROGRAM_EXIT;
1729
1730 END retrieve_attachment;
1731
1732
1733 PROCEDURE reconfig_attachment(
1734 i_msgid IN RAW,
1735 i_cid IN VARCHAR2,
1736 i_entity_name IN VARCHAR2,
1737 i_pk1_value IN VARCHAR2,
1738 i_pk2_value IN VARCHAR2,
1739 i_pk3_value IN VARCHAR2,
1740 i_pk4_value IN VARCHAR2,
1741 i_pk5_value IN VARCHAR2,
1742 i_program_app_id IN NUMBER,
1743 i_program_id IN NUMBER,
1744 i_request_id IN NUMBER,
1745 x_document_id OUT NOCOPY NUMBER
1746 ) AS
1747 i_method_name varchar2(2000) := 'ecx_attachment.deposit_blob_attachment';
1748 l_file_id NUMBER := NULL;
1749 l_datatype NUMBER := NULL;
1750 l_install_mode VARCHAR2(200) := NULL;
1751 l_mode VARCHAR2(200) := NULL;
1752 l_progress VARCHAR2(4) := NULL;
1753 l_dynamic_sql_str0 VARCHAR2(2000) := NULL;
1754 l_dynamic_sql_str1 VARCHAR2(2000) := NULL;
1755 mode_not_support EXCEPTION;
1756 invalid_input EXCEPTION;
1757
1758 BEGIN
1759
1760 if (l_procedureEnabled) then
1761 ecx_debug.push(i_method_name);
1762 end if;
1763 if(l_statementEnabled) then
1764 ecx_debug.log(l_statement,'i_msgid', i_msgid,i_method_name);
1765 ecx_debug.log(l_statement,'i_cid', i_cid,i_method_name);
1766 ecx_debug.log(l_statement,'i_entity_name', i_entity_name,i_method_name);
1767 ecx_debug.log(l_statement,'i_pk1_value', i_pk1_value,i_method_name);
1768 ecx_debug.log(l_statement,'i_pk2_value', i_pk2_value,i_method_name);
1769 ecx_debug.log(l_statement,'i_pk3_value', i_pk3_value,i_method_name);
1770 ecx_debug.log(l_statement,'i_pk4_value', i_pk4_value,i_method_name);
1771 ecx_debug.log(l_statement,'i_pk5_value', i_pk5_value,i_method_name);
1772 ecx_debug.log(l_statement,'i_program_app_id', i_program_app_id,i_method_name);
1773 ecx_debug.log(l_statement,'i_program_id', i_program_id,i_method_name);
1774 ecx_debug.log(l_statement,'i_request_id', i_request_id,i_method_name);
1775 end if;
1776 IF (i_entity_name IS NULL) THEN
1777
1778 RAISE invalid_input;
1779
1780 END IF;
1781
1782
1783 -- Check mode
1784 l_install_mode := wf_core.translate('WF_INSTALL');
1785 IF (l_install_mode = 'EMBEDDED') THEN
1786
1787 l_progress := '0000';
1788 SELECT fid, datatype
1789 INTO l_file_id, l_datatype
1790 FROM ecx_attachment_maps
1791 WHERE msgid = i_msgid AND cid = i_cid;
1792
1793 IF ((l_file_id is NOT NULL) AND (l_datatype is NOT NULL)) THEN
1794
1795 -- Figure out document_id based on file_id
1796 l_progress := '0001';
1797 l_dynamic_sql_str1 := 'SELECT DISTINCT document_id
1801 EXECUTE IMMEDIATE l_dynamic_sql_str1 INTO x_document_id;
1798 FROM fnd_Documents_tl
1799 WHERE media_id = ' || l_file_id;
1800
1802
1803
1804 IF (l_datatype = ecx_attachment.EMBEDDED_LOB_DATA_TYPE) THEN
1805
1806 l_progress := '0002';
1807 l_dynamic_sql_str0 := 'UPDATE fnd_attached_documents SET
1808 entity_name = :1,
1809 pk1_value = :2,
1810 pk2_value = :3,
1811 pk3_value = :4,
1812 pk4_value = :5,
1813 pk5_value = :6,
1814 program_application_id = :7,
1815 program_id = :8,
1816 request_id = :9
1817 WHERE document_id = :10';
1818
1819 EXECUTE IMMEDIATE l_dynamic_sql_str0
1820 USING i_entity_name, i_pk1_value, i_pk2_value, i_pk3_value,
1821 i_pk4_value, i_pk5_value, i_program_app_id,
1822 i_program_id, i_request_id, x_document_id;
1823
1824
1825 END IF;
1826
1827 END IF;
1828
1829 ELSE
1830
1831 -- Standalone mode is not yet supported
1832 RAISE mode_not_support;
1833
1834 END IF;
1835
1836 if (l_procedureEnabled) then
1837 ecx_debug.pop(i_method_name);
1838 end if;
1839 EXCEPTION
1840
1841 WHEN invalid_input THEN
1842
1843 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RECONFIG_ATTACHMENT INVALID INPUTS' || SQLERRM;
1844 ecx_utils.i_ret_code := 0;
1845 if(l_unexpectedEnabled) then
1846 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1847 end if;
1848 if (l_procedureEnabled) then
1849 ecx_debug.pop(i_method_name);
1850 end if;
1851 RAISE ecx_utils.PROGRAM_EXIT;
1852
1853 WHEN mode_not_support THEN
1854
1855 ecx_utils.i_errbuf := 'ECX_ATTACHMENT.RECONFIG_ATTACHMENT MODE NOT SUPPORT' || SQLERRM;
1856 ecx_utils.i_ret_code := 0;
1857 if(l_unexpectedEnabled) then
1858 ecx_debug.log(l_unexpected,ecx_utils.i_errbuf,i_method_name);
1859 end if;
1860 if (l_procedureEnabled) then
1861 ecx_debug.pop(i_method_name);
1862 end if;
1863 RAISE ecx_utils.PROGRAM_EXIT;
1864
1865 WHEN OTHERS THEN
1866
1867 if(l_unexpectedEnabled) then
1868 ecx_debug.log(l_unexpected,'ECX','ECX_PROGRAM_ERROR',i_method_name,'PROGRESS_LEVEL',
1869 'ECX_ATTACHMENT.RECONFIG_ATTACHMENT');
1870 ecx_debug.log(l_unexpected,'ECX','ECX_ERROR_MESSAGE',i_method_name,'ERROR_MESSAGE',SQLERRM);
1871 end if;
1872 ecx_utils.error_type := 30;
1873 ecx_utils.i_ret_code := 2;
1874 ecx_utils.i_errbuf := SQLERRM || ' at ECX_ATTACHMENT.RECONFIG_ATTACHMENT';
1875 if (l_procedureEnabled) then
1876 ecx_debug.pop(i_method_name);
1877 end if;
1878 RAISE ecx_utils.PROGRAM_EXIT;
1879
1880 END reconfig_attachment;
1881
1882
1883 END ecx_attachment;