[Home] [Help]
PACKAGE BODY: APPS.EDR_ATTACHMENTS_GRP
Source
1 PACKAGE BODY EDR_ATTACHMENTS_GRP AS
2 /* $Header: EDRGATCB.pls 120.4.12000000.1 2007/01/18 05:53:18 appldev ship $ */
3
4 G_ENTITY_NAME constant varchar2(10) := 'ERECORD';
5 G_TEMP_ENTITY_NAME constant varchar2(15) := 'TEMPERECORD';
6 G_PUBLISH_FLAG_N constant varchar2(1) := 'N';
7 G_PUBLISH_FLAG_Y constant varchar2(1) := 'Y';
8 G_SECURITY_OFF constant NUMBER := 4;
9 G_SECURITY_ON constant NUMBER := 1;
10
11 -- Bug 4731317 :start
12 /*
13 -- Bug 4381237: Start
14 PROCEDURE copy_attachments(X_from_entity_name IN VARCHAR2,
15 X_from_pk1_value IN VARCHAR2,
16 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
17 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
18 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
19 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
20 X_to_entity_name IN VARCHAR2,
21 X_to_pk1_value IN VARCHAR2,
22 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
23 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
24 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
25 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
26 X_created_by IN NUMBER DEFAULT NULL,
27 X_last_update_login IN NUMBER DEFAULT NULL,
28 X_program_application_id IN NUMBER DEFAULT NULL,
29 X_program_id IN NUMBER DEFAULT NULL,
30 X_request_id IN NUMBER DEFAULT NULL,
31 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
32 X_from_category_id IN NUMBER DEFAULT NULL,
33 X_to_category_id IN NUMBER DEFAULT NULL) IS
34 CURSOR doclist IS
35 SELECT fad.seq_num, fad.document_id,
36 fad.attribute_category, fad.attribute1, fad.attribute2,
37 fad.attribute3, fad.attribute4, fad.attribute5,
38 fad.attribute6, fad.attribute7, fad.attribute8,
39 fad.attribute9, fad.attribute10, fad.attribute11,
40 fad.attribute12, fad.attribute13, fad.attribute14,
41 fad.attribute15, fad.column1, fad.automatically_added_flag,
42 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
43 fad.pk4_value, fad.pk5_value,
44 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
45 fd.publish_flag, fd.image_type, fd.storage_type,
46 fd.usage_type, fd.start_date_active, fd.end_date_active,
47 fdtl.language, fdtl.description, fdtl.file_name,
48 fdtl.media_id, fdtl.doc_attribute_category dattr_cat,
49 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
50 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
51 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
52 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
53 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
54 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
55 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
56 fdtl.doc_attribute15 dattr15
57 FROM fnd_attached_documents fad,
58 fnd_documents fd,
59 fnd_documents_tl fdtl
60 WHERE fad.document_id = fd.document_id
61 AND fd.document_id = fdtl.document_id
62 AND fdtl.language = userenv('LANG')
63 AND fad.entity_name = X_from_entity_name
64 AND fad.pk1_value = X_from_pk1_value
65 AND (X_from_pk2_value IS NULL
66 OR fad.pk2_value = X_from_pk2_value)
67 AND (X_from_pk3_value IS NULL
68 OR fad.pk3_value = X_from_pk3_value)
69 AND (X_from_pk4_value IS NULL
70 OR fad.pk4_value = X_from_pk4_value)
71 AND (X_from_pk5_value IS NULL
72 OR fad.pk5_value = X_from_pk5_value)
73 AND (X_from_category_id IS NULL
74 OR (fad.category_id = X_from_category_id
75 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
76 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag);
77
78 CURSOR shorttext (mid NUMBER) IS
79 SELECT short_text
80 FROM fnd_documents_short_text
81 WHERE media_id = mid;
82
83 CURSOR longtext (mid NUMBER) IS
84 SELECT long_text
85 FROM fnd_documents_long_text
86 WHERE media_id = mid;
87
88 CURSOR fnd_lobs_cur (mid NUMBER) IS
89 SELECT file_id,
90 file_name,
91 file_content_type,
92 upload_date,
93 expiration_date,
94 program_name,
95 program_tag,
96 file_data,
97 language,
98 oracle_charset,
99 file_format
100 FROM fnd_lobs
101 WHERE file_id = mid;
102
103 media_id_tmp NUMBER;
104 document_id_tmp NUMBER;
105 row_id_tmp VARCHAR2(30);
106 short_text_tmp VARCHAR2(2000);
107 long_text_tmp LONG;
108 fnd_lobs_rec fnd_lobs_cur%ROWTYPE;
109 BEGIN
110 -- Use cursor loop to get all attachments associated with
111 -- the from_entity
112 FOR docrec IN doclist LOOP
113 -- One-Time docs that Short Text or Long Text will have
114 -- to be copied into a new document (Long Text will be
115 -- truncated to 32K). Create the new document records
116 -- before creating the attachment record
117
118 --Bug 4381237: Start
119 --We are changing the logic of the FND copy atachment in the following manner:
120 --1. make a new physical copy of the attachment even in case when its of type
121 -- standard. But do this ONLY when the security on the base document is enforced
122 -- i.e. when the security type <> 4 and security id <> null
123 --IF (docrec.usage_type = 'O'
124 -- AND docrec.datatype_id IN (1,2,5,6) ) THEN
125 IF docrec.datatype_id IN (1,2,5,6) AND
126 (
127 (docrec.usage_type = 'S' and (docrec.security_type <> 4
128 or (docrec.security_type = 4 and
129 docrec.security_id is not NULL
130 )
131 )
132 )
133 OR
134 docrec.usage_type = 'O'
135 )
136
137 THEN
138 --Bug 4381237: End
139 -- Create Documents records
140 FND_DOCUMENTS_PKG.Insert_Row
141 (row_id_tmp,
142 document_id_tmp,
143 SYSDATE,
144 NVL(X_created_by,0),
145 SYSDATE,
146 NVL(X_created_by,0),
147 X_last_update_login,
148 docrec.datatype_id,
149 NVL(X_to_category_id, docrec.category_id),
150 --Bug 4381237: Start
151 --security is always enforced as 4 and security id as null
152 --docrec.security_type,
153 4,
154 --docrec.security_id,
155 NULL,
156 --Bug 4381237: End
157 docrec.publish_flag,
158 docrec.image_type,
159 docrec.storage_type,
160 docrec.usage_type,
161 docrec.start_date_active,
162 docrec.end_date_active,
163 X_request_id,
164 X_program_application_id,
165 X_program_id,
166 SYSDATE,
167 docrec.language,
168 docrec.description,
169 docrec.file_name,
170 media_id_tmp,
171 docrec.dattr_cat, docrec.dattr1,
172 docrec.dattr2, docrec.dattr3,
173 docrec.dattr4, docrec.dattr5,
174 docrec.dattr6, docrec.dattr7,
175 docrec.dattr8, docrec.dattr9,
176 docrec.dattr10, docrec.dattr11,
177 docrec.dattr12, docrec.dattr13,
178 docrec.dattr14, docrec.dattr15);
179
180 -- overwrite document_id from original
181 -- cursor for later insert into
182 -- fnd_attached_documents
183 docrec.document_id := document_id_tmp;
184
185 -- Duplicate short or long text
186 IF (docrec.datatype_id = 1) THEN
187 -- Handle short Text
188 -- get original data
189 OPEN shorttext(docrec.media_id);
190 FETCH shorttext INTO short_text_tmp;
191 CLOSE shorttext;
192
193 INSERT INTO fnd_documents_short_text
194 (media_id,
195 short_text)
196 VALUES
197 (media_id_tmp,
198 short_text_tmp);
199
200 media_id_tmp := '';
201 ELSIF (docrec.datatype_id = 2) THEN
202 -- Handle long text
203 -- get original data
204 OPEN longtext(docrec.media_id);
205 FETCH longtext INTO long_text_tmp;
206 CLOSE longtext;
207
208 INSERT INTO fnd_documents_long_text
209 (media_id,
210 long_text)
211 VALUES
212 (media_id_tmp,
213 long_text_tmp);
214
215 media_id_tmp := '';
216 ELSIF (docrec.datatype_id=6) THEN
217 OPEN fnd_lobs_cur(docrec.media_id);
218 FETCH fnd_lobs_cur INTO
219 fnd_lobs_rec.file_id,
220 fnd_lobs_rec.file_name,
221 fnd_lobs_rec.file_content_type,
222 fnd_lobs_rec.upload_date,
223 fnd_lobs_rec.expiration_date,
224 fnd_lobs_rec.program_name,
225 fnd_lobs_rec.program_tag,
226 fnd_lobs_rec.file_data,
227 fnd_lobs_rec.language,
228 fnd_lobs_rec.oracle_charset,
229 fnd_lobs_rec.file_format;
230 CLOSE fnd_lobs_cur;
231
232 INSERT INTO fnd_lobs
233 (file_id,
234 file_name,
235 file_content_type,
236 upload_date,
237 expiration_date,
238 program_name,
239 program_tag,
240 file_data,
241 language,
242 oracle_charset,
243 file_format)
244 VALUES
245 (media_id_tmp,
246 fnd_lobs_rec.file_name,
247 fnd_lobs_rec.file_content_type,
248 fnd_lobs_rec.upload_date,
249 fnd_lobs_rec.expiration_date,
250 fnd_lobs_rec.program_name,
251 fnd_lobs_rec.program_tag,
252 fnd_lobs_rec.file_data,
253 fnd_lobs_rec.language,
254 fnd_lobs_rec.oracle_charset,
255 fnd_lobs_rec.file_format);
256
257 media_id_tmp := '';
258 END IF; -- end of duplicating text
259 END IF; -- end if datatype in (1,2,6)
260
261 -- Create attachment record
262 INSERT INTO fnd_attached_documents
263 (attached_document_id,
264 document_id,
265 creation_date,
266 created_by,
267 last_update_date,
268 last_updated_by,
269 last_update_login,
270 seq_num,
271 entity_name,
272 pk1_value, pk2_value, pk3_value,
273 pk4_value, pk5_value,
274 automatically_added_flag,
275 program_application_id, program_id,
276 program_update_date, request_id,
277 attribute_category, attribute1,
278 attribute2, attribute3, attribute4,
279 attribute5, attribute6, attribute7,
280 attribute8, attribute9, attribute10,
281 attribute11, attribute12, attribute13,
282 attribute14, attribute15, column1, category_id)
283 VALUES
284 (fnd_attached_documents_s.nextval,
285 docrec.document_id,
286 sysdate,
287 NVL(X_created_by,0),
288 sysdate,
289 NVL(X_created_by,0),
290 X_last_update_login,
291 docrec.seq_num,
292 X_to_entity_name,
293 X_to_pk1_value,
294 NVL(X_to_pk2_value, docrec.pk2_value),
295 NVL(X_to_pk3_value, docrec.pk3_value),
296 NVL(X_to_pk4_value, docrec.pk4_value),
297 NVL(X_to_pk5_value, docrec.pk5_value),
298 docrec.automatically_added_flag,
299 X_program_application_id, X_program_id,
300 sysdate, X_request_id,
301 docrec.attribute_category, docrec.attribute1,
302 docrec.attribute2, docrec.attribute3,
303 docrec.attribute4, docrec.attribute5,
304 docrec.attribute6, docrec.attribute7,
305 docrec.attribute8, docrec.attribute9,
306 docrec.attribute10, docrec.attribute11,
307 docrec.attribute12, docrec.attribute13,
308 docrec.attribute14, docrec.attribute15,
309 docrec.column1,
310 NVL(X_to_category_id, NVL(docrec.att_cat, docrec.category_id)));
311
312 -- Update the document to be a std document if it
313 -- was an ole or image that wasn't already a std doc
314 -- (images should be created as Std, but just in case)
315 IF (docrec.datatype_id IN (3,4)
316 AND docrec.usage_type <> 'S') THEN
317 UPDATE fnd_documents
318 SET usage_type = 'S'
319 WHERE document_id = docrec.document_id;
320 END IF;
321
322 END LOOP; -- end of working through all attachments
323
324 EXCEPTION WHEN OTHERS THEN
325
326 CLOSE shorttext;
327 CLOSE longtext;
328 CLOSE fnd_lobs_cur;
329
330 END copy_attachments;
331 */
332
333 PROCEDURE copy_attachments(X_from_entity_name IN VARCHAR2,
334 X_from_pk1_value IN VARCHAR2,
335 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
336 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
337 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
338 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
339 X_to_entity_name IN VARCHAR2,
340 X_to_pk1_value IN VARCHAR2,
341 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
342 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
343 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
344 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
345 X_created_by IN NUMBER DEFAULT NULL,
346 X_last_update_login IN NUMBER DEFAULT NULL,
347 X_program_application_id IN NUMBER DEFAULT NULL,
348 X_program_id IN NUMBER DEFAULT NULL,
349 X_request_id IN NUMBER DEFAULT NULL,
350 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
351 X_from_category_id IN NUMBER DEFAULT NULL,
352 X_to_category_id IN NUMBER DEFAULT NULL) IS
353 BEGIN
354
355 fnd_attached_documents2_pkg.copy_attachments(
356 X_from_entity_name,
357 X_from_pk1_value,
358 X_from_pk2_value,
359 X_from_pk3_value,
360 X_from_pk4_value,
361 X_from_pk5_value,
362 X_to_entity_name,
363 X_to_pk1_value,
364 X_to_pk2_value,
365 X_to_pk3_value,
366 X_to_pk4_value,
367 X_to_pk5_value,
368 X_created_by,
369 X_last_update_login,
370 X_program_application_id,
371 X_program_id,
372 X_request_id,
373 X_automatically_added_flag,
374 X_from_category_id,
375 X_to_category_id);
376 END;
377
378
379 -- Bug 4731317 : end
380 -- Bug 4731317 :start
381 /*
382 PROCEDURE copy_one_attachment(X_from_entity_name IN VARCHAR2,
383 X_from_pk1_value IN VARCHAR2,
384 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
385 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
386 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
387 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
388 X_to_entity_name IN VARCHAR2,
389 X_to_pk1_value IN VARCHAR2,
390 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
391 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
392 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
393 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
394 X_document_id IN NUMBER,
395 X_created_by IN NUMBER DEFAULT NULL,
396 X_last_update_login IN NUMBER DEFAULT NULL,
397 X_program_application_id IN NUMBER DEFAULT NULL,
398 X_program_id IN NUMBER DEFAULT NULL,
399 X_request_id IN NUMBER DEFAULT NULL,
400 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
404 SELECT fad.seq_num, fad.document_id,
401 X_from_category_id IN NUMBER DEFAULT NULL,
402 X_to_category_id IN NUMBER DEFAULT NULL) IS
403 CURSOR doclist IS
405 fad.attribute_category, fad.attribute1, fad.attribute2,
406 fad.attribute3, fad.attribute4, fad.attribute5,
407 fad.attribute6, fad.attribute7, fad.attribute8,
408 fad.attribute9, fad.attribute10, fad.attribute11,
409 fad.attribute12, fad.attribute13, fad.attribute14,
410 fad.attribute15, fad.column1, fad.automatically_added_flag,
411 fad.category_id att_cat, fad.pk2_value, fad.pk3_value,
412 fad.pk4_value, fad.pk5_value,
413 fd.datatype_id, fd.category_id, fd.security_type, fd.security_id,
414 fd.publish_flag, fd.image_type, fd.storage_type,
415 fd.usage_type, fd.start_date_active, fd.end_date_active,
416 fdtl.language, fdtl.description, fdtl.file_name,
417 fdtl.media_id, fdtl.doc_attribute_category dattr_cat,
418 fdtl.doc_attribute1 dattr1, fdtl.doc_attribute2 dattr2,
419 fdtl.doc_attribute3 dattr3, fdtl.doc_attribute4 dattr4,
420 fdtl.doc_attribute5 dattr5, fdtl.doc_attribute6 dattr6,
421 fdtl.doc_attribute7 dattr7, fdtl.doc_attribute8 dattr8,
422 fdtl.doc_attribute9 dattr9, fdtl.doc_attribute10 dattr10,
423 fdtl.doc_attribute11 dattr11, fdtl.doc_attribute12 dattr12,
424 fdtl.doc_attribute13 dattr13, fdtl.doc_attribute14 dattr14,
425 fdtl.doc_attribute15 dattr15
426 FROM fnd_attached_documents fad,
427 fnd_documents fd,
428 fnd_documents_tl fdtl
429 --WHERE fad.document_id = fd.document_id
430 WHERE fad.document_id = X_document_id
431 AND fad.document_id = fd.document_id
432 AND fd.document_id = fdtl.document_id
433 AND fdtl.language = userenv('LANG')
434 AND fad.entity_name = X_from_entity_name
435 AND fad.pk1_value = X_from_pk1_value
436 AND (X_from_pk2_value IS NULL
437 OR fad.pk2_value = X_from_pk2_value)
438 AND (X_from_pk3_value IS NULL
439 OR fad.pk3_value = X_from_pk3_value)
440 AND (X_from_pk4_value IS NULL
441 OR fad.pk4_value = X_from_pk4_value)
442 AND (X_from_pk5_value IS NULL
443 OR fad.pk5_value = X_from_pk5_value)
444 AND (X_from_category_id IS NULL
445 OR (fad.category_id = X_from_category_id
446 OR (fad.category_id is NULL AND fd.category_id = X_from_category_id)))
447 AND fad.automatically_added_flag like decode(X_automatically_added_flag,NULL,'%',X_automatically_added_flag);
448
449 CURSOR shorttext (mid NUMBER) IS
450 SELECT short_text
451 FROM fnd_documents_short_text
452 WHERE media_id = mid;
453
454 CURSOR longtext (mid NUMBER) IS
455 SELECT long_text
456 FROM fnd_documents_long_text
457 WHERE media_id = mid;
458
459 CURSOR fnd_lobs_cur (mid NUMBER) IS
460 SELECT file_id,
461 file_name,
462 file_content_type,
463 upload_date,
464 expiration_date,
465 program_name,
466 program_tag,
467 file_data,
468 language,
469 oracle_charset,
470 file_format
471 FROM fnd_lobs
472 WHERE file_id = mid;
473
474 media_id_tmp NUMBER;
475 document_id_tmp NUMBER;
476 row_id_tmp VARCHAR2(30);
477 short_text_tmp VARCHAR2(2000);
478 long_text_tmp LONG;
479 fnd_lobs_rec fnd_lobs_cur%ROWTYPE;
480 BEGIN
481 -- Use cursor loop to get all attachments associated with
482 -- the from_entity
483 FOR docrec IN doclist LOOP
484 -- One-Time docs that Short Text or Long Text will have
485 -- to be copied into a new document (Long Text will be
486 -- truncated to 32K). Create the new document records
487 -- before creating the attachment record
488
489 --Bug 4381237: Start
490 --We are changing the logic of the FND copy atachment in the following manner:
491 --1. make a new physical copy of the attachment even in case when its of type
492 -- standard. But do this ONLY when the security on the base document is enforced
493 -- i.e. when the security type <> 4 and security id <> null
494 --IF (docrec.usage_type = 'O'
495 -- AND docrec.datatype_id IN (1,2,5,6) ) THEN
496 IF docrec.datatype_id IN (1,2,5,6) AND
497 (
498 (docrec.usage_type = 'S' and (docrec.security_type <> 4
499 or (docrec.security_type = 4 and
500 docrec.security_id is not NULL
501 )
502 )
503 )
504 OR
505 docrec.usage_type = 'O'
506 )
507
508 THEN
509 --Bug 4381237: End
510 -- Create Documents records
511 FND_DOCUMENTS_PKG.Insert_Row
512 (row_id_tmp,
513 document_id_tmp,
514 SYSDATE,
515 NVL(X_created_by,0),
516 SYSDATE,
517 NVL(X_created_by,0),
518 X_last_update_login,
519 docrec.datatype_id,
520 NVL(X_to_category_id, docrec.category_id),
521 --Bug 4381237: Start
522 --security is always enforced as 4 and security id as null
523 --docrec.security_type,
524 4,
525 --docrec.security_id,
526 NULL,
527 --Bug 4381237: End
528 docrec.publish_flag,
532 docrec.start_date_active,
529 docrec.image_type,
530 docrec.storage_type,
531 docrec.usage_type,
533 docrec.end_date_active,
534 X_request_id,
535 X_program_application_id,
536 X_program_id,
537 SYSDATE,
538 docrec.language,
539 docrec.description,
540 docrec.file_name,
541 media_id_tmp,
542 docrec.dattr_cat, docrec.dattr1,
543 docrec.dattr2, docrec.dattr3,
544 docrec.dattr4, docrec.dattr5,
545 docrec.dattr6, docrec.dattr7,
546 docrec.dattr8, docrec.dattr9,
547 docrec.dattr10, docrec.dattr11,
548 docrec.dattr12, docrec.dattr13,
549 docrec.dattr14, docrec.dattr15);
550
551 -- overwrite document_id from original
552 -- cursor for later insert into
553 -- fnd_attached_documents
554 docrec.document_id := document_id_tmp;
555
556 -- Duplicate short or long text
557 IF (docrec.datatype_id = 1) THEN
558 -- Handle short Text
559 -- get original data
560 OPEN shorttext(docrec.media_id);
561 FETCH shorttext INTO short_text_tmp;
562 CLOSE shorttext;
563
564 INSERT INTO fnd_documents_short_text
565 (media_id,
566 short_text)
567 VALUES
568 (media_id_tmp,
569 short_text_tmp);
570
571 media_id_tmp := '';
572 ELSIF (docrec.datatype_id = 2) THEN
573 -- Handle long text
574 -- get original data
575 OPEN longtext(docrec.media_id);
576 FETCH longtext INTO long_text_tmp;
577 CLOSE longtext;
578
579 INSERT INTO fnd_documents_long_text
580 (media_id,
581 long_text)
582 VALUES
583 (media_id_tmp,
584 long_text_tmp);
585
586 media_id_tmp := '';
587 ELSIF (docrec.datatype_id=6) THEN
588 OPEN fnd_lobs_cur(docrec.media_id);
589 FETCH fnd_lobs_cur INTO
590 fnd_lobs_rec.file_id,
591 fnd_lobs_rec.file_name,
592 fnd_lobs_rec.file_content_type,
593 fnd_lobs_rec.upload_date,
594 fnd_lobs_rec.expiration_date,
595 fnd_lobs_rec.program_name,
596 fnd_lobs_rec.program_tag,
597 fnd_lobs_rec.file_data,
598 fnd_lobs_rec.language,
599 fnd_lobs_rec.oracle_charset,
600 fnd_lobs_rec.file_format;
601 CLOSE fnd_lobs_cur;
602
603 INSERT INTO fnd_lobs
604 (file_id,
605 file_name,
606 file_content_type,
607 upload_date,
608 expiration_date,
609 program_name,
610 program_tag,
611 file_data,
612 language,
613 oracle_charset,
614 file_format)
615 VALUES
616 (media_id_tmp,
617 fnd_lobs_rec.file_name,
618 fnd_lobs_rec.file_content_type,
619 fnd_lobs_rec.upload_date,
620 fnd_lobs_rec.expiration_date,
621 fnd_lobs_rec.program_name,
622 fnd_lobs_rec.program_tag,
623 fnd_lobs_rec.file_data,
624 fnd_lobs_rec.language,
625 fnd_lobs_rec.oracle_charset,
626 fnd_lobs_rec.file_format);
627
628 media_id_tmp := '';
629 END IF; -- end of duplicating text
630 END IF; -- end if datatype in (1,2,6)
631
632 -- Create attachment record
633 INSERT INTO fnd_attached_documents
634 (attached_document_id,
635 document_id,
636 creation_date,
637 created_by,
638 last_update_date,
639 last_updated_by,
640 last_update_login,
641 seq_num,
642 entity_name,
643 pk1_value, pk2_value, pk3_value,
644 pk4_value, pk5_value,
645 automatically_added_flag,
646 program_application_id, program_id,
647 program_update_date, request_id,
648 attribute_category, attribute1,
649 attribute2, attribute3, attribute4,
650 attribute5, attribute6, attribute7,
651 attribute8, attribute9, attribute10,
652 attribute11, attribute12, attribute13,
653 attribute14, attribute15, column1, category_id)
654 VALUES
655 (fnd_attached_documents_s.nextval,
656 docrec.document_id,
657 sysdate,
658 NVL(X_created_by,0),
659 sysdate,
660 NVL(X_created_by,0),
661 X_last_update_login,
662 docrec.seq_num,
663 X_to_entity_name,
664 X_to_pk1_value,
665 NVL(X_to_pk2_value, docrec.pk2_value),
666 NVL(X_to_pk3_value, docrec.pk3_value),
667 NVL(X_to_pk4_value, docrec.pk4_value),
668 NVL(X_to_pk5_value, docrec.pk5_value),
669 docrec.automatically_added_flag,
670 X_program_application_id, X_program_id,
671 sysdate, X_request_id,
672 docrec.attribute_category, docrec.attribute1,
673 docrec.attribute2, docrec.attribute3,
674 docrec.attribute4, docrec.attribute5,
675 docrec.attribute6, docrec.attribute7,
676 docrec.attribute8, docrec.attribute9,
677 docrec.attribute10, docrec.attribute11,
678 docrec.attribute12, docrec.attribute13,
679 docrec.attribute14, docrec.attribute15,
683 -- Update the document to be a std document if it
680 docrec.column1,
681 NVL(X_to_category_id, NVL(docrec.att_cat, docrec.category_id)));
682
684 -- was an ole or image that wasn't already a std doc
685 -- (images should be created as Std, but just in case)
686 IF (docrec.datatype_id IN (3,4)
687 AND docrec.usage_type <> 'S') THEN
688 UPDATE fnd_documents
689 SET usage_type = 'S'
690 WHERE document_id = docrec.document_id;
691 END IF;
692
693 END LOOP; -- end of working through all attachments
694
695 EXCEPTION WHEN OTHERS THEN
696
697 CLOSE shorttext;
698 CLOSE longtext;
699 CLOSE fnd_lobs_cur;
700
701 END copy_one_attachment;
702
703 -- Bug 4381237 : End
704 */
705 PROCEDURE copy_one_attachment(X_from_entity_name IN VARCHAR2,
706 X_from_pk1_value IN VARCHAR2,
707 X_from_pk2_value IN VARCHAR2 DEFAULT NULL,
708 X_from_pk3_value IN VARCHAR2 DEFAULT NULL,
709 X_from_pk4_value IN VARCHAR2 DEFAULT NULL,
710 X_from_pk5_value IN VARCHAR2 DEFAULT NULL,
711 X_to_entity_name IN VARCHAR2,
712 X_to_pk1_value IN VARCHAR2,
713 X_to_pk2_value IN VARCHAR2 DEFAULT NULL,
714 X_to_pk3_value IN VARCHAR2 DEFAULT NULL,
715 X_to_pk4_value IN VARCHAR2 DEFAULT NULL,
716 X_to_pk5_value IN VARCHAR2 DEFAULT NULL,
717 X_document_id IN NUMBER,
718 X_created_by IN NUMBER DEFAULT NULL,
719 X_last_update_login IN NUMBER DEFAULT NULL,
720 X_program_application_id IN NUMBER DEFAULT NULL,
721 X_program_id IN NUMBER DEFAULT NULL,
722 X_request_id IN NUMBER DEFAULT NULL,
723 X_automatically_added_flag IN VARCHAR2 DEFAULT NULL,
724 X_from_category_id IN NUMBER DEFAULT NULL,
725 X_to_category_id IN NUMBER DEFAULT NULL) IS
726 BEGIN
727 fnd_attached_documents2_pkg.copy_attachments(
728 X_from_entity_name,
729 X_from_pk1_value,
730 X_from_pk2_value,
731 X_from_pk3_value,
732 X_from_pk4_value,
733 X_from_pk5_value,
734 X_to_entity_name,
735 X_to_pk1_value,
736 X_to_pk2_value,
737 X_to_pk3_value,
738 X_to_pk4_value,
739 X_to_pk5_value,
740 X_created_by,
741 X_last_update_login,
742 X_program_application_id,
743 X_program_id,
744 X_request_id,
745 X_automatically_added_flag,
746 X_from_category_id,
747 X_to_category_id);
748 END copy_one_attachment;
749 -- Bug 4731317 :end
750 PROCEDURE ATTACH_ERP_AUT(p_entity_name VARCHAR2,
751 p_pk1_value VARCHAR2,
752 p_pk2_value VARCHAR2,
753 p_pk3_value VARCHAR2,
754 p_pk4_value VARCHAR2,
755 p_pk5_value VARCHAR2,
756 p_category VARCHAR2,
757 p_target_value varchar2)
758 AS PRAGMA AUTONOMOUS_TRANSACTION;
759 l_erecord_id number;
760 l_pk2_value VARCHAR2(100);
761 l_pk3_value VARCHAR2(100);
762 l_pk4_value VARCHAR2(100);
763 l_pk5_value VARCHAR2(100);
764 l_category VARCHAR2(400);
765
766 l_category_id NUMBER;
767 l_category_names VARCHAR2(400);
768 l_category_name VARCHAR2(30);
769 l_user_id NUMBER;
770 l_login_id NUMBER;
771 BEGIN
772 wf_log_pkg.string(6, 'ATTACH_ERP_AUT','Inside Atonomous');
773 l_user_id := fnd_global.user_id;
774 l_login_id := fnd_global.login_id;
775 wf_log_pkg.string(6, 'ATTACH_ERP_AUT','PK1 Value...'|| p_pk1_value);
776 wf_log_pkg.string(6, 'ATTACH_ERP_AUT','PK2 Value...'|| p_pk2_value);
777 wf_log_pkg.string(6, 'ATTACH_ERP_AUT','PK3 Value...'|| p_pk3_value);
778 wf_log_pkg.string(6, 'ATTACH_ERP_AUT','PK4 Value...'|| p_pk4_value);
779 wf_log_pkg.string(6, 'ATTACH','PK5 Value...'|| p_pk5_value);
780
781
782 if (upper(p_pk2_value) = '''NULL''') then
783 l_pk2_value := NULL;
784 else
785 l_pk2_value := p_pk2_value;
786 end if;
787
788 if (upper(p_pk3_value) = '''NULL''') then
789 l_pk3_value := NULL;
790 else
791 l_pk3_value := p_pk3_value;
792 end if;
793
794 if (upper(p_pk4_value) = '''NULL''') then
795 l_pk4_value := NULL;
796 else
797 l_pk4_value := p_pk4_value;
798 end if;
799
800 if (upper(p_pk5_value) = '''NULL''') then
801 l_pk5_value := NULL;
802 else
803 l_pk5_value := p_pk5_value;
804 end if;
805
806 if (upper(p_category) = '''NULL''') then
807 l_category:= NULL;
808 else
809 l_category := p_category;
810 end if;
811
812 -- get the erecord id from the temporary table populate by the subscription
813 -- rule function before invoking XML Gateway
814 l_erecord_id := p_target_value;
815
816 --obtain the name of the categories being passed from the call
817 l_category_names := l_category;
818
819 --parse the string of semicolon separated category names and get the
820 --individual category name
821 l_category_name := edr_utilities.get_delimited_string(l_category_names,';');
822
823 wf_log_pkg.string(6, 'ATTACH_ERP_AUT','Category PArsed');
824 loop
825
826 begin
827 if (l_category_name is not null) then
828 select category_id into l_category_id
832 wf_log_pkg.string(6, 'ATTACH_ERP_AUT','Calling Copy API');
829 from fnd_document_categories_vl
830 where name = l_category_name;
831 end if;
833 --call the fnd api to copy the attachment from the original
834 --business entity to the ERECORD entity
835 --change the category of the attachment to ERES
836
837 -- call copy attachment for each category
838 --Bug 4381237: Start
839 --use the new copy attachment API from EDR instead of FND
840 --fnd_attached_documents2_pkg.copy_attachments(
841 copy_attachments(
842 X_from_entity_name => p_entity_name,
843 X_from_pk1_value => p_pk1_value,
844 X_from_pk2_value => l_pk2_value,
845 X_from_pk3_value => l_pk3_value,
846 X_from_pk4_value => l_pk4_value,
847 X_from_pk5_value => l_pk5_value,
848 X_to_entity_name => G_TEMP_ENTITY_NAME,
849 X_to_pk1_value => l_erecord_id,
850 X_to_pk2_value => null,
851 X_to_pk3_value => null,
852 X_to_pk4_value => null,
853 X_to_pk5_value => null,
854 X_created_by => l_user_id,
855 X_last_update_login => l_login_id,
856 X_program_application_id => null,
857 X_program_id => null,
858 X_request_id => null,
859 X_automatically_added_flag => 'N',
860 X_from_category_id => l_category_id,
861 X_to_category_id => l_category_id);
862 --Bug 4381237: End
863 exception
864 when no_data_found then
865 wf_log_pkg.string(6, 'EDR_ATTACHMENTS_GRP.ATTACH_ERP_AUT','Category Not Found: '||l_category_name);
866 end;
867
868 --get the new value of the category name
869 if (l_category_name is not null) then
870 l_category_name := edr_utilities.get_delimited_string(l_category_names,';');
871 end if;
872
873 EXIT WHEN l_category_name is null;
874
875 end loop;
876
877 COMMIT;
878
879 END ATTACH_ERP_AUT;
880
881 PROCEDURE ATTACH_ERP (p_entity_name VARCHAR2,
882 p_pk1_value VARCHAR2,
883 p_pk2_value VARCHAR2,
884 p_pk3_value VARCHAR2,
885 p_pk4_value VARCHAR2,
886 p_pk5_value VARCHAR2,
887 p_category VARCHAR2) AS
888 CURSOR GET_TEMP_EREC_ID IS
889 select document_id
890 from edr_erecord_id_temp;
891 l_erecord_id number;
892 BEGIN
893 -- get the erecord id from the temporary table populate by the subscription
894 -- rule function before invoking XML Gateway
895 wf_log_pkg.string(6, 'ATTACH_ERP','Inside Attachment Package');
896
897 -- Bug 3186732
898 -- Issue if XML Map has attachment procedure call then
899 -- for those maps user used to get "NO DATA FOUND" Error
900 -- because getting eRecord ID from edr_erecord_id_temp
901 -- was using SELECT .. INTO .. for utility functions this
902 -- row is not present it used give error
903 -- modified the select statement and also added a condition
904 -- to call copy attachments API only when eRecord ID is present.
905 --
906
907 OPEN GET_TEMP_EREC_ID;
908 FETCH GET_TEMP_EREC_ID INTO l_erecord_id;
909 CLOSE GET_TEMP_EREC_ID;
910
911 wf_log_pkg.string(6, 'ATTACH_ERP','Event ID '||l_erecord_id);
912
913 /* Call ATTACH_ERP_AUT only when temp eRecord ID present in GET_TEMP_EREC_ID */
914
915 IF l_eRecord_id is not null THEN
916 ATTACH_ERP_AUT(p_entity_name,p_pk1_value,p_pk2_value,p_pk3_value,p_pk4_value,
917 p_pk5_value,p_category,l_erecord_id);
918 END IF;
919 END ATTACH_ERP;
920
921
922 PROCEDURE EVENT_POST_OP (p_file_id VARCHAR2) AS
923 l_event_status VARCHAR2(15);
924 l_status VARCHAR2(1);
925 l_document_id NUMBER;
926 l_publish BOOLEAN := FALSE;
927 l_return_status VARCHAR2(1);
928 l_file_status VARCHAR2(1);
929 BEGIN
930 --Bug 4374548: Start
931 select status into l_file_status
932 from edr_files_vl
933 where file_id = p_file_id;
934 --Bug 4374548: End
935
936 --Bug 4374548: Added this if statement that encloses all processing
937 if (l_file_status = 'P') then
938 l_event_status := EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS;
939
940 if (l_event_status = 'SUCCESS') then
941 l_status := 'A';
942 l_publish := TRUE;
943 elsif (l_event_status = 'REJECTED') then
944 l_status := 'R';
945 --Bug 4086319: Start
946 --Do not publish rejected documents.
947 --l_publish := TRUE;
948 --Bug 4086319: End
949 elsif (l_event_status = 'TIMEDOUT') then
950 l_status := 'N';
951 end if;
952
953 -- Bug 4090471 : Start
954 EDR_ISIGN_CHECKLIST_PVT.ATTACH_CHECKLIST
955 (p_file_id => p_file_id,
956 x_return_status => l_return_status);
957 -- Bug 4090471 : End
958
959 UPDATE EDR_FILES_B
960 SET STATUS = l_status
961 where file_id = p_file_id;
962
963 --publish the document existing in fnd tables if the file has been
964 --approved or rejected
965 if (l_publish = TRUE) then
966 select fnd_document_id
967 into l_document_id
968 from edr_files_b
972 --through the Document Catalog button in the attachment Forms UI
969 where file_id = p_file_id;
970
971 --this would allow the file to be attached to other business objects
973 update fnd_documents set
974 security_type = G_SECURITY_OFF,
975 publish_flag = G_PUBLISH_FLAG_Y
976 where document_id = l_document_id;
977 end if;
978
979 wf_event.raise2
980 (p_event_name => 'oracle.apps.edr.file.approvalcompletion',
981 p_event_key => p_file_id,
982 p_event_data => null,
983 p_parameter_name1 => 'FILE_STATUS',
984 p_parameter_value1 => l_event_status
985 );
986 else
987 wf_log_pkg.string(6, 'ATTACH_FILE_AUT','File status is not Pending.');
988 wf_log_pkg.string(6, 'ATTACH_FILE_AUT','Cannot do any post op processing');
989 end if;
990 EXCEPTION
991 WHEN OTHERS THEN
992 wf_log_pkg.string(6, 'ATTACH_FILE_AUT','Unexpected error in post op');
993 wf_log_pkg.string(6, 'ATTACH_FILE_AUT',SQLERRM);
994 END EVENT_POST_OP;
995
996 PROCEDURE GET_CATEGORY_NAME (P_CATEGORY_NAME IN VARCHAR2,
997 P_DISPLAY_NAME in out nocopy VARCHAR2)
998 AS
999 BEGIN
1000 wf_log_pkg.string(6, 'GET_CATEGORY_NAME','In the get_category_name procedure');
1001
1002 SELECT USER_NAME INTO P_DISPLAY_NAME
1003 FROM FND_DOCUMENT_CATEGORIES_VL
1004 WHERE NAME = P_CATEGORY_NAME;
1005 EXCEPTION WHEN NO_DATA_FOUND then
1006 P_DISPLAY_NAME := 'Invalid Category';
1007
1008 wf_log_pkg.string(6, 'GET_CATEGORY_NAME','Returning from get_category_name procedure');
1009
1010 END GET_CATEGORY_NAME;
1011
1012 PROCEDURE GET_DESC_FLEX_ALL_PROMPTS(P_APPLICATION_ID IN VARCHAR2,
1013 P_DESC_FLEX_DEF_NAME IN VARCHAR2,
1014 P_DESC_FLEX_CONTEXT IN VARCHAR2,
1015 P_PROMPT_TYPE IN VARCHAR2,
1016 P_COLUMN1_NAME IN VARCHAR2,
1017 P_COLUMN2_NAME IN VARCHAR2,
1018 P_COLUMN3_NAME IN VARCHAR2,
1019 P_COLUMN4_NAME IN VARCHAR2,
1020 P_COLUMN5_NAME IN VARCHAR2,
1021 P_COLUMN6_NAME IN VARCHAR2,
1022 P_COLUMN7_NAME IN VARCHAR2,
1023 P_COLUMN8_NAME IN VARCHAR2,
1024 P_COLUMN9_NAME IN VARCHAR2,
1025 P_COLUMN10_NAME IN VARCHAR2,
1026 P_COLUMN1_PROMPT out nocopy VARCHAR2,
1027 P_COLUMN2_PROMPT out nocopy VARCHAR2,
1028 P_COLUMN3_PROMPT out nocopy VARCHAR2,
1029 P_COLUMN4_PROMPT out nocopy VARCHAR2,
1030 P_COLUMN5_PROMPT out nocopy VARCHAR2,
1031 P_COLUMN6_PROMPT out nocopy VARCHAR2,
1032 P_COLUMN7_PROMPT out nocopy VARCHAR2,
1033 P_COLUMN8_PROMPT out nocopy VARCHAR2,
1034 P_COLUMN9_PROMPT out nocopy VARCHAR2,
1035 P_COLUMN10_PROMPT out nocopy VARCHAR2) AS
1036 L_PROMPT11 VARCHAR2(255);
1037 L_PROMPT12 VARCHAR2(255);
1038 L_PROMPT13 VARCHAR2(255);
1039 L_PROMPT14 VARCHAR2(255);
1040 L_PROMPT15 VARCHAR2(255);
1041 L_PROMPT16 VARCHAR2(255);
1042 L_PROMPT17 VARCHAR2(255);
1043 L_PROMPT18 VARCHAR2(255);
1044 L_PROMPT19 VARCHAR2(255);
1045 L_PROMPT20 VARCHAR2(255);
1046 L_PROMPT21 VARCHAR2(255);
1047 L_PROMPT22 VARCHAR2(255);
1048 L_PROMPT23 VARCHAR2(255);
1049 L_PROMPT24 VARCHAR2(255);
1050 L_PROMPT25 VARCHAR2(255);
1051 L_PROMPT26 VARCHAR2(255);
1052 L_PROMPT27 VARCHAR2(255);
1053 L_PROMPT28 VARCHAR2(255);
1054 L_PROMPT29 VARCHAR2(255);
1055 L_PROMPT30 VARCHAR2(255);
1056
1057 BEGIN
1058 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','In the get_desc_flex_all_prompts procedure');
1059
1060 EDR_STANDARD.GET_DESC_FLEX_ALL_PROMPTS(
1061 P_APPLICATION_ID => P_APPLICATION_ID,
1062 P_DESC_FLEX_DEF_NAME => P_DESC_FLEX_DEF_NAME,
1063 P_DESC_FLEX_CONTEXT => P_DESC_FLEX_CONTEXT,
1064 P_PROMPT_TYPE => P_PROMPT_TYPE,
1065 P_COLUMN1_NAME => P_COLUMN1_NAME,
1066 P_COLUMN2_NAME => P_COLUMN2_NAME,
1067 P_COLUMN3_NAME => P_COLUMN3_NAME,
1068 P_COLUMN4_NAME => P_COLUMN4_NAME,
1069 P_COLUMN5_NAME => P_COLUMN5_NAME,
1070 P_COLUMN6_NAME => P_COLUMN6_NAME,
1071 P_COLUMN7_NAME => P_COLUMN7_NAME,
1072 P_COLUMN8_NAME => P_COLUMN8_NAME,
1073 P_COLUMN9_NAME => P_COLUMN9_NAME,
1074 P_COLUMN10_NAME => P_COLUMN10_NAME,
1075 P_COLUMN1_PROMPT => P_COLUMN1_PROMPT,
1076 P_COLUMN2_PROMPT => P_COLUMN2_PROMPT,
1077 P_COLUMN3_PROMPT => P_COLUMN3_PROMPT,
1078 P_COLUMN4_PROMPT => P_COLUMN4_PROMPT,
1079 P_COLUMN5_PROMPT => P_COLUMN5_PROMPT,
1080 P_COLUMN6_PROMPT => P_COLUMN6_PROMPT,
1081 P_COLUMN7_PROMPT => P_COLUMN7_PROMPT,
1082 P_COLUMN8_PROMPT => P_COLUMN8_PROMPT,
1083 P_COLUMN9_PROMPT => P_COLUMN9_PROMPT,
1084 P_COLUMN10_PROMPT => P_COLUMN10_PROMPT,
1085 P_COLUMN11_PROMPT => L_PROMPT11,
1086 P_COLUMN12_PROMPT => L_PROMPT12,
1087 P_COLUMN13_PROMPT => L_PROMPT13,
1088 P_COLUMN14_PROMPT => L_PROMPT14,
1089 P_COLUMN15_PROMPT => L_PROMPT15,
1090 P_COLUMN16_PROMPT => L_PROMPT16,
1091 P_COLUMN17_PROMPT => L_PROMPT17,
1092 P_COLUMN18_PROMPT => L_PROMPT18,
1093 P_COLUMN19_PROMPT => L_PROMPT19,
1094 P_COLUMN20_PROMPT => L_PROMPT20,
1095 P_COLUMN21_PROMPT => L_PROMPT21,
1096 P_COLUMN22_PROMPT => L_PROMPT22,
1097 P_COLUMN23_PROMPT => L_PROMPT23,
1098 P_COLUMN24_PROMPT => L_PROMPT24,
1099 P_COLUMN25_PROMPT => L_PROMPT25,
1100 P_COLUMN26_PROMPT => L_PROMPT26,
1104 P_COLUMN30_PROMPT => L_PROMPT30);
1101 P_COLUMN27_PROMPT => L_PROMPT27,
1102 P_COLUMN28_PROMPT => L_PROMPT28,
1103 P_COLUMN29_PROMPT => L_PROMPT29,
1105
1106 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt1 '||P_COLUMN1_PROMPT);
1107 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt2 '||P_COLUMN2_PROMPT);
1108 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt3 '||P_COLUMN3_PROMPT);
1109 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt4 '||P_COLUMN4_PROMPT);
1110 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt5 '||P_COLUMN5_PROMPT);
1111 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt6 '||P_COLUMN6_PROMPT);
1112 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt7 '||P_COLUMN7_PROMPT);
1113 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt8 '||P_COLUMN8_PROMPT);
1114 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt9 '||P_COLUMN9_PROMPT);
1115 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_PROMPTS','Prompt10 '||P_COLUMN10_PROMPT);
1116
1117 END GET_DESC_FLEX_ALL_PROMPTS;
1118
1119 -- Bug 4501520 : rvsingh:start
1120
1121 PROCEDURE GET_DESC_FLEX_ALL_VALUES(P_APPLICATION_ID IN VARCHAR2,
1122 P_DESC_FLEX_DEF_NAME IN VARCHAR2,
1123 P_DESC_FLEX_CONTEXT IN VARCHAR2,
1124 P_COLUMN1_NAME IN VARCHAR2,
1125 P_COLUMN2_NAME IN VARCHAR2,
1126 P_COLUMN3_NAME IN VARCHAR2,
1127 P_COLUMN4_NAME IN VARCHAR2,
1128 P_COLUMN5_NAME IN VARCHAR2,
1129 P_COLUMN6_NAME IN VARCHAR2,
1130 P_COLUMN7_NAME IN VARCHAR2,
1131 P_COLUMN8_NAME IN VARCHAR2,
1132 P_COLUMN9_NAME IN VARCHAR2,
1133 P_COLUMN10_NAME IN VARCHAR2,
1134 P_COLUMN1_ID_VAL IN VARCHAR2,
1135 P_COLUMN2_ID_VAL IN VARCHAR2,
1136 P_COLUMN3_ID_VAL IN VARCHAR2,
1137 P_COLUMN4_ID_VAL IN VARCHAR2,
1138 P_COLUMN5_ID_VAL IN VARCHAR2,
1139 P_COLUMN6_ID_VAL IN VARCHAR2,
1140 P_COLUMN7_ID_VAL IN VARCHAR2,
1141 P_COLUMN8_ID_VAL IN VARCHAR2,
1142 P_COLUMN9_ID_VAL IN VARCHAR2,
1143 P_COLUMN10_ID_VAL IN VARCHAR2,
1144 P_COLUMN1_VAL out nocopy VARCHAR2,
1145 P_COLUMN2_VAL out nocopy VARCHAR2,
1146 P_COLUMN3_VAL out nocopy VARCHAR2,
1147 P_COLUMN4_VAL out nocopy VARCHAR2,
1148 P_COLUMN5_VAL out nocopy VARCHAR2,
1149 P_COLUMN6_VAL out nocopy VARCHAR2,
1150 P_COLUMN7_VAL out nocopy VARCHAR2,
1151 P_COLUMN8_VAL out nocopy VARCHAR2,
1152 P_COLUMN9_VAL out nocopy VARCHAR2,
1153 P_COLUMN10_VAL out nocopy VARCHAR2) AS
1154 L_VAL11 VARCHAR2(255);
1155 L_VAL12 VARCHAR2(255);
1156 L_VAL13 VARCHAR2(255);
1157 L_VAL14 VARCHAR2(255);
1158 L_VAL15 VARCHAR2(255);
1159 L_VAL16 VARCHAR2(255);
1160 L_VAL17 VARCHAR2(255);
1161 L_VAL18 VARCHAR2(255);
1162 L_VAL19 VARCHAR2(255);
1163 L_VAL20 VARCHAR2(255);
1164 L_VAL21 VARCHAR2(255);
1165 L_VAL22 VARCHAR2(255);
1166 L_VAL23 VARCHAR2(255);
1167 L_VAL24 VARCHAR2(255);
1168 L_VAL25 VARCHAR2(255);
1169 L_VAL26 VARCHAR2(255);
1170 L_VAL27 VARCHAR2(255);
1171 L_VAL28 VARCHAR2(255);
1172 L_VAL29 VARCHAR2(255);
1173 L_VAL30 VARCHAR2(255);
1174
1175 BEGIN
1176 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','In the GET_DESC_FLEX_ALL_VALUES procedure');
1177
1178 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL1 '||P_COLUMN1_ID_VAL);
1179 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL2 '||P_COLUMN2_ID_VAL);
1180 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL3 '||P_COLUMN3_ID_VAL);
1181 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL4 '||P_COLUMN4_ID_VAL);
1182 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL5 '||P_COLUMN5_ID_VAL);
1183 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL6 '||P_COLUMN6_ID_VAL);
1184 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL7 '||P_COLUMN7_ID_VAL);
1185 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL8 '||P_COLUMN8_ID_VAL);
1186 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL9 '||P_COLUMN9_ID_VAL);
1187 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','ID_VAL10 '||P_COLUMN10_ID_VAL);
1188
1189 EDR_STANDARD.GET_DESC_FLEX_ALL_VALUES(
1190 P_APPLICATION_ID => P_APPLICATION_ID,
1191 P_DESC_FLEX_DEF_NAME => P_DESC_FLEX_DEF_NAME,
1192 P_DESC_FLEX_CONTEXT => P_DESC_FLEX_CONTEXT,
1193 P_COLUMN1_NAME => P_COLUMN1_NAME,
1194 P_COLUMN2_NAME => P_COLUMN2_NAME,
1195 P_COLUMN3_NAME => P_COLUMN3_NAME,
1196 P_COLUMN4_NAME => P_COLUMN4_NAME,
1197 P_COLUMN5_NAME => P_COLUMN5_NAME,
1198 P_COLUMN6_NAME => P_COLUMN6_NAME,
1199 P_COLUMN7_NAME => P_COLUMN7_NAME,
1200 P_COLUMN8_NAME => P_COLUMN8_NAME,
1201 P_COLUMN9_NAME => P_COLUMN9_NAME,
1202 P_COLUMN10_NAME => P_COLUMN10_NAME,
1203 P_COLUMN1_ID_VAL => P_COLUMN1_ID_VAL,
1204 P_COLUMN2_ID_VAL => P_COLUMN2_ID_VAL,
1205 P_COLUMN3_ID_VAL => P_COLUMN3_ID_VAL,
1206 P_COLUMN4_ID_VAL => P_COLUMN4_ID_VAL,
1207 P_COLUMN5_ID_VAL => P_COLUMN5_ID_VAL,
1208 P_COLUMN6_ID_VAL => P_COLUMN6_ID_VAL,
1209 P_COLUMN7_ID_VAL => P_COLUMN7_ID_VAL,
1210 P_COLUMN8_ID_VAL => P_COLUMN8_ID_VAL,
1211 P_COLUMN9_ID_VAL => P_COLUMN9_ID_VAL,
1212 P_COLUMN10_ID_VAL => P_COLUMN10_ID_VAL,
1213 P_COLUMN1_VAL => P_COLUMN1_VAL,
1214 P_COLUMN2_VAL => P_COLUMN2_VAL,
1215 P_COLUMN3_VAL => P_COLUMN3_VAL,
1216 P_COLUMN4_VAL => P_COLUMN4_VAL,
1217 P_COLUMN5_VAL => P_COLUMN5_VAL,
1221 P_COLUMN9_VAL => P_COLUMN9_VAL,
1218 P_COLUMN6_VAL => P_COLUMN6_VAL,
1219 P_COLUMN7_VAL => P_COLUMN7_VAL,
1220 P_COLUMN8_VAL => P_COLUMN8_VAL,
1222 P_COLUMN10_VAL => P_COLUMN10_VAL,
1223 P_COLUMN11_VAL => L_VAL11,
1224 P_COLUMN12_VAL => L_VAL12,
1225 P_COLUMN13_VAL => L_VAL13,
1226 P_COLUMN14_VAL => L_VAL14,
1227 P_COLUMN15_VAL => L_VAL15,
1228 P_COLUMN16_VAL => L_VAL16,
1229 P_COLUMN17_VAL => L_VAL17,
1230 P_COLUMN18_VAL => L_VAL18,
1231 P_COLUMN19_VAL => L_VAL19,
1232 P_COLUMN20_VAL => L_VAL20,
1233 P_COLUMN21_VAL => L_VAL21,
1234 P_COLUMN22_VAL => L_VAL22,
1235 P_COLUMN23_VAL => L_VAL23,
1236 P_COLUMN24_VAL => L_VAL24,
1237 P_COLUMN25_VAL => L_VAL25,
1238 P_COLUMN26_VAL => L_VAL26,
1239 P_COLUMN27_VAL => L_VAL27,
1240 P_COLUMN28_VAL => L_VAL28,
1241 P_COLUMN29_VAL => L_VAL29,
1242 P_COLUMN30_VAL => L_VAL30);
1243
1244 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL1 '||P_COLUMN1_VAL);
1245 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL2 '||P_COLUMN2_VAL);
1246 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL3 '||P_COLUMN3_VAL);
1247 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL4 '||P_COLUMN4_VAL);
1248 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL5 '||P_COLUMN5_VAL);
1249 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL6 '||P_COLUMN6_VAL);
1250 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL7 '||P_COLUMN7_VAL);
1251 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL8 '||P_COLUMN8_VAL);
1252 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL9 '||P_COLUMN9_VAL);
1253 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','VAL10 '||P_COLUMN10_VAL);
1254
1255
1256 END GET_DESC_FLEX_ALL_VALUES;
1257
1258
1259 -- Bug 4501520 : rvsingh:end
1260
1261 PROCEDURE ATTACH_FILE (p_document_id VARCHAR2) AS
1262 CURSOR GET_TEMP_EREC_ID IS
1263 select document_id
1264 from edr_erecord_id_temp;
1265 l_erecord_id number;
1266
1267 BEGIN
1268 -- get the erecord id from the temporary table populate by the subscription
1269 -- rule function before invoking XML Gateway
1270 wf_log_pkg.string(6, 'ATTACH_FILE','Inside Attachment Package, ATTACH_FILE procedure');
1271 -- Bug 3186732
1272 -- Issue if XML Map has attachment procedure call then
1273 -- for those maps user used to get "NO DATA FOUND" Error
1274 -- because getting eRecord ID from edr_erecord_id_temp
1275 -- was using SELECT .. INTO .. for utility functions this
1276 -- row is not present it used give error
1277 -- modified the select statement and also added a condition
1278 -- to call copy attachments API only when eRecord ID is present.
1279 --
1280
1281 OPEN GET_TEMP_EREC_ID;
1282 FETCH GET_TEMP_EREC_ID INTO l_erecord_id;
1283
1284 CLOSE GET_TEMP_EREC_ID;
1285
1286 -- select document_id into l_erecord_id from edr_erecord_id_temp;
1287 wf_log_pkg.string(6, 'ATTACH_FILE','Event ID '||l_erecord_id);
1288 IF l_eRecord_id is not null THEN
1289 ATTACH_FILE_AUT(p_document_id, l_erecord_id);
1290 END IF;
1291 END ATTACH_FILE;
1292
1293 PROCEDURE ATTACH_FILE_AUT (p_document_id VARCHAR2, p_target_value VARCHAR2)
1294 AS PRAGMA AUTONOMOUS_TRANSACTION;
1295 l_rowid VARCHAR2(100);
1296 l_atc_doc_id NUMBER;
1297 l_document_id NUMBER;
1298 l_user_id NUMBER;
1299 l_login_id NUMBER;
1300 l_media_id NUMBER;
1301 l_language VARCHAR2(10);
1302 BEGIN
1303 l_document_id := p_document_id;
1304 wf_log_pkg.string(6, 'ATTACH_FILE_AUT','The FND Document id is '||l_document_id);
1305
1306 select fnd_attached_documents_s.nextval into l_atc_doc_id
1307 from dual;
1308
1309 l_user_id := fnd_global.user_id;
1310 l_login_id := fnd_global.login_id;
1311
1312 select userenv('lang') into l_language from dual;
1313
1314 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row
1315 (X_Rowid => l_rowid,
1316 X_attached_document_id => l_atc_doc_id,
1317 X_document_id => l_document_id,
1318 X_creation_date => SYSDATE,
1319 X_created_by => l_user_id,
1320 X_last_update_date => SYSDATE,
1321 X_last_updated_by => l_user_id,
1322 X_last_update_login => l_login_id,
1323 X_seq_num => 1,
1324 X_entity_name => G_TEMP_ENTITY_NAME,
1325 X_column1 => null,
1326 X_pk1_value => p_target_value,
1327 X_pk2_value => null,
1328 X_pk3_value => null,
1329 X_pk4_value => null,
1330 X_pk5_value => null,
1331 X_automatically_added_flag => 'N',
1332 X_datatype_id => 6,
1333 X_category_id => NULL,
1334 X_security_type => NULL,
1335 X_publish_flag => NULL,
1336 X_language => l_language,
1337 X_media_id => l_media_id);
1338
1339 commit;
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 wf_log_pkg.string(6, 'ATTACH_FILE_AUT','Unexpected error while writing to FND tables');
1343 wf_log_pkg.string(6, 'ATTACH_FILE_AUT',SQLERRM);
1344
1345 END ATTACH_FILE_AUT;
1346
1347
1348 --Bug 3893101: Start
1352
1349 FUNCTION PARSE_ATTACHMENT_STRING(P_ATTACHMENT_STRING IN VARCHAR2)
1350 RETURN ERES_ATTACHMENT_TBL_TYPE
1351 is
1353 l_entity_name VARCHAR2(240);
1354 l_pk1_value VARCHAR2(100);
1355 l_pk2_value VARCHAR2(100);
1356 l_pk3_value VARCHAR2(100);
1357 l_pk4_value VARCHAR2(100);
1358 l_pk5_value VARCHAR2(100);
1359 l_category_value VARCHAR2(100);
1360 l_attachment_details_tbl ERES_ATTACHMENT_TBL_TYPE;
1361 l_identified_position NUMBER;
1362 l_temp_count NUMBER;
1363 l_attachment_string VARCHAR2(2000);
1364 l_attachment_string_list FND_TABLE_OF_VARCHAR2_4000;
1365
1366 BEGIN
1367
1368 l_attachment_string := p_attachment_string;
1369 --Create a new attachment string list.
1370 --This would hold each of the attachment strings specified through the input
1371 --parameter.
1372 l_attachment_string_list := new FND_TABLE_OF_VARCHAR2_4000();
1373 l_temp_count := 0;
1374
1375 l_identified_position := instr(l_attachment_string,'~',1,1);
1376
1377 --Parse the string and identify the individual attachment strings contained.
1378 while l_identified_position > 0 loop
1379 l_temp_count := l_temp_count + 1;
1380 l_attachment_string_list.extend;
1381 l_attachment_string_list(l_temp_count) := substr(l_attachment_string,1,l_identified_position-1);
1382 l_attachment_string := substr(l_attachment_string,l_identified_position+1,length(l_attachment_string)-l_identified_position);
1383 l_identified_position := instr(l_attachment_string,'~',1,1);
1384 end loop;
1385
1386 l_temp_count := l_temp_count + 1;
1387 l_attachment_string_list.extend;
1388 l_attachment_string_list(l_temp_count) := l_attachment_string;
1389
1390 --Parse Each attachment string obtained and get the attachment attributes.
1391 for i in 1..l_attachment_string_list.count loop
1392 l_attachment_string := l_attachment_string_list(i);
1393 l_entity_name:=substr(l_attachment_string,instr(l_attachment_string,'=')+1,
1394 instr(l_attachment_string,'&')-(instr(l_attachment_string,'=')+1));
1395
1396 --This parsing procedure would obtain the attachmewnt attribute values from
1397 --the attachment string.
1398 if instr(l_attachment_string,'&',1,3) > 0 then
1399 l_pk1_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,3)+1,
1400 instr(l_attachment_string,'&',1,3)-(instr(l_attachment_string,'=',1,3)+1));
1401
1402 if instr(l_attachment_string,'&',1,5) > 0 then
1403 l_pk2_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,5)+1,
1404 instr(l_attachment_string,'&',1,5)-(instr(l_attachment_string,'=',1,5)+1));
1405
1406 if instr(l_attachment_string,'&',1,7) > 0 then
1407 l_pk3_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,7)+1,
1408 instr(l_attachment_string,'&',1,7)-(instr(l_attachment_string,'=',1,7)+1));
1409
1410 if instr(l_attachment_string,'&',1,9) > 0 then
1411 l_pk4_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,9)+1,
1412 instr(l_attachment_string,'&',1,9)-(instr(l_attachment_string,'=',1,9)+1));
1413
1414 if instr(l_attachment_string,'&',1,11) > 0 then
1415 l_pk5_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,11)+1,
1416 instr(l_attachment_string,'&',1,11)-(instr(l_attachment_string,'=',1,11)+1));
1417
1418 if instr(l_attachment_string,'=',1,12) > 0 then
1419 l_category_value := substr(l_attachment_string,instr(l_attachment_string,'=',1,12)+1);
1420 end if;
1421 else
1422
1423 if(instr(l_attachment_string,'&',1,10) > 0 and instr(l_attachment_string,'=',1,11) >0) then
1424 l_pk5_value := substr(l_attachment_string,instr(l_attachment_string,'=',1,11)+1);
1425 elsif(instr(l_attachment_string,'&',1,9) > 0 and instr(l_attachment_string,'=',1,10) > 0) then
1426 l_category_value := substr(l_attachment_string,instr(l_attachment_string,'=',1,10)+1);
1427 end if;
1428 end if;
1429 else
1430 if(instr(l_attachment_string,'&',1,8) > 0 and instr(l_attachment_string,'=',1,9) >0) then
1431 l_pk4_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,9)+1);
1432
1433 elsif(instr(l_attachment_string,'&',1,7) > 0 and instr(l_attachment_string,'=',1,8) > 0) then
1434 l_category_value := substr(l_attachment_string,instr(l_attachment_string,'=',1,8)+1);
1435
1436 end if;
1437 end if;
1438
1439 else
1440
1441 if(instr(l_attachment_string,'&',1,6) > 0 and instr(l_attachment_string,'=',1,7) >0) then
1442 l_pk3_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,7)+1);
1443
1444 elsif(instr(l_attachment_string,'&',1,5) > 0 and instr(l_attachment_string,'=',1,6) > 0) then
1445 l_category_value := substr(l_attachment_string,instr(l_attachment_string,'=',1,6)+1);
1446 end if;
1447 end if;
1448
1449 else
1450 if(instr(l_attachment_string,'&',1,4) > 0 and instr(l_attachment_string,'=',1,5) >0) then
1451 l_pk2_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,5)+1);
1452
1453 elsif(instr(l_attachment_string,'&',1,3) > 0 and instr(l_attachment_string,'=',1,4) > 0) then
1454 l_category_value := substr(l_attachment_string,instr(l_attachment_string,'=',1,4)+1);
1455 end if;
1456 end if;
1457
1458 else
1459 l_pk1_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,3)+1);
1460 END IF;
1461
1467 l_attachment_details_tbl(i).PK4_VALUE := l_pk4_value;
1462 --Set the attachment attribute values.
1463 l_attachment_details_tbl(i).ENTITY_NAME := l_entity_name;
1464 l_attachment_details_tbl(i).PK1_VALUE := l_pk1_value;
1465 l_attachment_details_tbl(i).PK2_VALUE := l_pk2_value;
1466 l_attachment_details_tbl(i).PK3_VALUE := l_pk3_value;
1468 l_attachment_details_tbl(i).PK5_VALUE := l_pk5_value;
1469 l_attachment_details_tbl(i).CATEGORY := l_category_value;
1470 END LOOP;
1471 --Return this table of attachment details
1472 return l_attachment_details_tbl;
1473
1474 EXCEPTION
1475 when others then
1476 null;
1477
1478 END PARSE_ATTACHMENT_STRING;
1479
1480 --This method would be used to add an ERP attachment for each of the
1481 --attachment strings specified in the input parameter.
1482 PROCEDURE ADD_ERP_ATTACH(P_ATTACHMENT_STRING IN VARCHAR2)
1483 is
1484
1485 l_attachment_details_tbl ERES_ATTACHMENT_TBL_TYPE;
1486
1487 BEGIN
1488
1489 --Obtain the details of the attachment string in a table of attachment
1490 --attributes.
1491 l_attachment_details_tbl := PARSE_ATTACHMENT_STRING(P_ATTACHMENT_STRING);
1492
1493 if l_attachment_details_tbl is not null and l_attachment_details_tbl.count > 0 then
1494 for i in 1..l_attachment_details_tbl.count loop
1495 --Call the attachment API for each attachment attribute details found.
1496 ATTACH_ERP (p_entity_name => l_attachment_details_tbl(i).ENTITY_NAME,
1497 p_pk1_value => l_attachment_details_tbl(i).PK1_VALUE,
1498 p_pk2_value => l_attachment_details_tbl(i).PK2_VALUE,
1499 p_pk3_value => l_attachment_details_tbl(i).PK3_VALUE,
1500 p_pk4_value => l_attachment_details_tbl(i).PK4_VALUE,
1501 p_pk5_value => l_attachment_details_tbl(i).PK5_VALUE,
1502 p_category => l_attachment_details_tbl(i).CATEGORY
1503 );
1504 end loop;
1505 end if;
1506
1507
1508 exception
1509 when others then
1510 null;
1511
1512 END ADD_ERP_ATTACH;
1513 --Bug 3893101: End
1514
1515 END EDR_ATTACHMENTS_GRP;