[Home] [Help]
PACKAGE BODY: APPS.OE_FND_ATTACHMENTS_PVT
Source
1 PACKAGE BODY oe_fnd_attachments_pvt as
2 /* $Header: OEXVATTB.pls 120.5 2011/04/28 16:31:55 ckasera ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'oe_fnd_attachments_pvt';
6
7 TYPE Attribute_Rec_Type IS RECORD
8 ( attribute_code VARCHAR2(30)
9 , column_name VARCHAR2(30)
10 , data_type VARCHAR2(30)
11 , value VARCHAR2(50)
12 );
13
14 TYPE Attribute_Tbl_Type IS TABLE OF Attribute_Rec_Type
15 INDEX BY BINARY_INTEGER;
16
17 TYPE Document_Rec_Type IS RECORD
18 (
19 document_id number
20 );
21
22 TYPE Document_Tbl_Type IS TABLE OF Document_Rec_Type
23 INDEX BY BINARY_INTEGER;
24
25 G_CACHED_ATTRIBUTES VARCHAR2(1) := 'N';
26 G_HDR_ENTITY_SQL VARCHAR2(2000);
27 G_LIN_ENTITY_SQL VARCHAR2(2000);
28 G_HDR_ATTRIBUTE_TBL Attribute_Tbl_Type;
29 G_LIN_ATTRIBUTE_TBL Attribute_Tbl_Type;
30
31 --------------------------------------------------------------------
32 /* LOCAL PROCEDURES/FUNCTIONS */
33 --------------------------------------------------------------------
34
35 --------------------------------------------------------------------------
36 -- FUNCTION Fetch_Attribute_Values
37 -- Called By: Add_Attachments_Automatic
38 -- This function will construct a dynamic sql to select the values of
39 -- all columns that are attachments enabled for the given entity
40 -- (p_entity_name) and for the given primary key values (p_pk1_value...)
41 -- The attributes and attribute values are returned in a table
42 --------------------------------------------------------------------------
43 FUNCTION Fetch_Attribute_Values
44 ( p_entity_name IN VARCHAR2
45 , p_database_object_name IN VARCHAR2
46 , p_pk1_value IN VARCHAR2
47 , p_pk2_value IN VARCHAR2
48 , p_pk3_value IN VARCHAR2
49 , p_pk4_value IN VARCHAR2
50 , p_pk5_value IN VARCHAR2
51 )
52 RETURN ATTRIBUTE_TBL_TYPE
53 IS
54 l_database_object_name VARCHAR2(30) := 'OE_AK_ORDER_HEADERS_V';
55
56 CURSOR c_attachment_attributes IS
57 sELECT o.attribute_code, akoa.column_name, aka.data_type
58 FROM oe_ak_obj_attr_ext o
59 , ak_object_attributes akoa
60 , ak_attributes aka
61 WHERE o.database_object_name = l_database_object_name
62 AND o.attachments_enabled_flag = 'Y'
63 AND o.database_object_name = akoa.database_object_name
64 AND o.attribute_code = akoa.attribute_code
65 AND o.attribute_application_id = akoa.attribute_application_id
66 AND aka.attribute_code = akoa.attribute_code
67 AND aka.attribute_application_id = akoa.attribute_application_id;
68 l_index NUMBER;
69 l_entity_sql VARCHAR2(2000);
70 l_sqlCursor integer;
71 l_dummy number;
72 l_attribute_tbl ATTRIBUTE_TBL_TYPE;
73 l_entity_Table fnd_document_entities.table_name%TYPE;
74 l_pk1_Column fnd_document_entities.pk1_column%TYPE;
75 l_pk2_Column fnd_document_entities.pk2_column%TYPE;
76 l_pk3_Column fnd_document_entities.pk3_column%TYPE;
77 l_pk4_Column fnd_document_entities.pk4_column%TYPE;
78 l_pk5_Column fnd_document_entities.pk5_column%TYPE;
79 --
80 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
81 --
82 BEGIN
83 IF l_debug_level > 0 THEN
84 oe_debug_pub.add( 'ENTER FETCH_ATTRIBUTE_VALUES' , 1 ) ;
85 END IF;
86
87 SELECT table_name, pk1_column, pk2_column
88 , pk3_column, pk4_column, pk5_column
89 INTO l_entity_table, l_pk1_column, l_pk2_column
90 , l_pk3_column, l_pk4_column, l_pk5_column
91 FROM FND_DOCUMENT_ENTITIES
92 WHERE data_object_code = p_entity_name;
93
94 --------------------------------------------------------------
95 -- CONSTRUCT THE SQL STATEMENT
96 --------------------------------------------------------------
97
98 IF g_cached_attributes = 'N' THEN
99 --cache header and line entities
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add( 'CACHING ATTACHMENT ATTRIBUTES' , 1 ) ;
102 END IF;
103 l_dummy := 1;
104
105 LOOP -- loop twice: once for header, once for lines
106 IF l_dummy = 2 THEN
107 l_database_object_name := 'OE_AK_ORDER_LINES_V';
108 l_attribute_tbl.delete;
109 END IF;
110
111 IF l_debug_level > 0 THEN
112 oe_debug_pub.add( 'BUILDING SQL TO SELECT COLUMNS FROM ENTITY' , 1 ) ;
113 END IF;
114 l_index := 1;
115
116 -- select columns that have attachments_enabled_flag = 'Y'
117 -- e.g SELECT CUSTOMER_PO_NUMBER, to_char(SOLD_TO_ORG_ID), to_char(ORDER_TYPE_ID)
118
119 l_entity_sql := 'SELECT ';
120 OPEN c_attachment_attributes;
121 LOOP
122 FETCH c_attachment_attributes
123 INTO l_attribute_tbl(l_index).attribute_code
124 ,l_attribute_tbl(l_index).column_name
125 ,l_attribute_tbl(l_index).data_type;
126 EXIT WHEN c_attachment_attributes%notfound;
127 if l_index = 1 then
128 if l_attribute_tbl(l_index).data_type <> 'VARCHAR2' then
129 l_entity_sql := l_entity_sql||'to_char('||l_attribute_tbl(l_index).column_name||') ';
130 else
131 l_entity_sql := l_entity_sql||l_attribute_tbl(l_index).column_name||' ';
132 end if;
133 else
134 if l_attribute_tbl(l_index).data_type <> 'VARCHAR2' then
135 l_entity_sql := l_entity_sql||' ,to_char('||l_attribute_tbl(l_index).column_name||') ';
136 else
137 l_entity_sql := l_entity_sql||' ,'||l_attribute_tbl(l_index).column_name||' ';
138 end if;
139 end if;
140 l_index := l_index+1;
141 END LOOP;
142 CLOSE c_attachment_attributes;
143
144 IF l_dummy = 1 THEN
145 g_hdr_entity_sql := l_entity_sql;
146 g_hdr_attribute_tbl := l_attribute_tbl;
147 ELSE
148 g_lin_entity_sql := l_entity_sql;
149 g_lin_attribute_tbl := l_attribute_tbl;
150 END IF;
151
152 EXIT WHEN l_dummy = 2;
153 l_dummy := l_dummy + 1;
154 END LOOP; --loop twice: once for headers, once for lines
155
156 g_cached_attributes := 'Y';
157
158 END IF; --attributes aren't cached
159
160 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
161 IF l_debug_level > 0 THEN
162 oe_debug_pub.add( 'USING HEADER ATTRIBUTES CACHE' , 1 ) ;
163 END IF;
164 l_entity_sql := g_hdr_entity_sql;
165 l_attribute_tbl := g_hdr_attribute_tbl;
166 ELSE
167 IF l_debug_level > 0 THEN
168 oe_debug_pub.add( 'USING LINE ATTRIBUTES CACHE' , 1 ) ;
169 END IF;
170 l_entity_sql := g_lin_entity_sql;
171 l_attribute_tbl := g_lin_attribute_tbl;
172 END IF;
173
174
175 <<WHERE_CLAUSE>>
176 -- Append entity name and values for the primary keys
177 -- e.g FROM OE_ORDER_HEADERS_ALL WHERE HEADER_ID = 1234
178
179 l_entity_sql := l_entity_sql || 'FROM ' || l_entity_table;
180 l_entity_sql := l_entity_sql || ' WHERE ' || l_pk1_Column || ' = :p_pk1_value';
181 if (l_pk2_Column IS NOT NULL) then
182 l_entity_sql := l_entity_sql || ' AND ( ' || l_pk2_column || ' IS NULL';
183 l_entity_sql := l_entity_sql || ' OR ' || l_pk2_column || ' = :p_pk2_value) ';
184 end if;
185 if (l_pk3_column IS NOT NULL) then
186 l_entity_sql := l_entity_sql || ' AND ( ' || l_pk3_column || ' IS NULL';
187 l_entity_sql := l_entity_sql || ' OR ' || l_pk3_column || ' = :p_pk3_value) ';
188 end if;
189 if (l_pk4_column IS NOT NULL) then
190 l_entity_sql := l_entity_sql || ' AND ( ' || l_pk4_column || ' IS NULL';
191 l_entity_sql := l_entity_sql || ' OR ' || l_pk4_column || ' = :p_pk4_value) ';
192 end if;
193 if (l_pk4_column IS NOT NULL) then
194 l_entity_sql := l_entity_sql || ' AND ( ' || l_pk4_column || ' IS NULL';
195 l_entity_sql := l_entity_sql || ' OR ' || l_pk4_column || ' = :p_pk4_value) ';
196 end if;
197 if (l_pk5_column IS NOT NULL) then
198 l_entity_sql := l_entity_sql || ' AND ( ' || l_pk5_column || ' IS NULL';
199 l_entity_sql := l_entity_sql || ' OR ' || l_pk5_column || ' = :p_pk5_value) ';
200 end if;
201
202 IF l_debug_level > 0 THEN
203 oe_debug_pub.add( 'FINAL SQL TO RETRIEVE ENTITY REFERENCE : ' ) ;
204 END IF;
205 IF l_debug_level > 0 THEN
206 oe_debug_pub.add( L_ENTITY_SQL ) ;
207 END IF;
208
209
210 -------------------------------------------------------------------
211 -- CREATE DYNAMIC SQL CURSOR AND EXECUTE
212 -------------------------------------------------------------------
213 l_sqlCursor := DBMS_SQL.open_cursor;
214 IF l_debug_level > 0 THEN
215 oe_debug_pub.add( 'FETCH_ATTRIBUTE_VALUES:CURSOR OPEN OK' ) ;
216 END IF;
217
218 DBMS_SQL.parse(l_sqlCursor, l_entity_sql, DBMS_SQL.NATIVE);
219
220 DBMS_SQL.bind_variable(l_sqlCursor, 'p_pk1_value',p_pk1_value );
221 if (l_pk2_column IS NOT NULL) then
222 DBMS_SQL.bind_variable(l_sqlCursor, 'p_pk2_value',p_pk2_value );
223 end if;
224 if (l_pk3_column IS NOT NULL) then
225 DBMS_SQL.bind_variable(l_sqlCursor, 'p_pk3_value',p_pk3_value );
226 end if;
227 if (l_pk4_column IS NOT NULL) then
228 DBMS_SQL.bind_variable(l_sqlCursor, 'p_pk4_value',p_pk4_value );
229 end if;
230 if (l_pk5_column IS NOT NULL) then
231 DBMS_SQL.bind_variable(l_sqlCursor, 'p_pk5_value',p_pk5_value );
232 end if;
233
234 for l_index in 1..l_attribute_tbl.COUNT loop
235 IF l_debug_level > 0 THEN
236 oe_debug_pub.add( 'DEFINING COLUMN FOR ATTRIBUTE: '|| L_ATTRIBUTE_TBL ( L_INDEX ) .ATTRIBUTE_CODE ) ;
237 END IF;
238 DBMS_SQL.define_column(l_sqlCursor, l_index, l_attribute_tbl(l_index).value, 50);
239 end loop;
240
241 l_dummy := DBMS_SQL.execute(l_sqlCursor);
242 IF l_debug_level > 0 THEN
243 oe_debug_pub.add( 'FETCH_ATTRIBUTE_VALUES:EXECUTE OK' ) ;
244 END IF;
245
246
247 -------------------------------------------------------------------
248 -- FETCH VALUES FROM SQL CURSOR INTO l_attribute_tbl
249 -------------------------------------------------------------------
250
251 if (DBMS_SQL.fetch_rows(l_sqlCursor) <> 0) then
252 IF l_debug_level > 0 THEN
253 oe_debug_pub.add( 'FETCH_ATTRIBUTE_VALUES:FETCH_ROWS OK' ) ;
254 END IF;
255 for l_index in 1..l_attribute_tbl.COUNT loop
256 DBMS_SQL.column_value(l_sqlcursor, l_index, l_attribute_tbl(l_index).value);
257 IF l_debug_level > 0 THEN
258 oe_debug_pub.add( 'ATTRIBUTE VALUE: '||L_ATTRIBUTE_TBL ( L_INDEX ) .VALUE ) ;
259 END IF;
260 end loop;
261 else
262 raise no_data_found;
263 end if;
264 DBMS_SQL.close_cursor(l_sqlCursor);
265
266 IF l_debug_level > 0 THEN
267 oe_debug_pub.add( 'EXIT FETCH_ATTRIBUTE_VALUES' , 1 ) ;
268 END IF;
269
270 RETURN l_attribute_tbl;
271
272 END Fetch_Attribute_Values;
273
274 --------------------------------------------------------------------------
275 -- FUNCTION Fetch_Documents
276 -- This function will evaluate the existing attachment rules/rule elements
277 -- based on the attribute values passed to it (p_attribute_tbl) and
278 -- identify the rules that apply to the entity and returns the
279 -- documents corresponding to the applicable rules
280 -- This function also constructs a dynamic sql to select the rules/documents
281 -- that apply to this entity
282 --------------------------------------------------------------------------
283 FUNCTION Fetch_Documents
284 ( p_database_object_name IN VARCHAR2
285 , p_attribute_tbl IN Attribute_Tbl_Type
286 )
287 RETURN Document_Tbl_Type IS
288 l_index NUMBER;
289 l_rule_sql VARCHAR2(5000);
290 l_sqlCursor integer;
291 l_dummy number;
292 l_document_tbl Document_Tbl_Type;
293 l_document_id NUMBER;
294 l_newline CONSTANT VARCHAR2(10) := '
295 ';
296 l_first_value BOOLEAN;
297 l_rows NUMBER;
298 --
299 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
300 --
301 BEGIN
302
303 IF l_debug_level > 0 THEN
304 oe_debug_pub.add( 'ENTER FETCH_DOCUMENTS' , 1 ) ;
305 END IF;
306
307 -------------------------------------------------------------------
308 -- CONSTRUCT THE SQL STATEMENT
309 -------------------------------------------------------------------
310
311 -- select valid documents attached to applicable rules for this entity
312
313 l_rule_sql := 'select distinct r.document_id'||l_newline||
314 'from oe_attachment_rules r'||l_newline||
315 ' , oe_attachment_rule_elements e1'||l_newline||
316 ' , fnd_documents d'||l_newline||
317 'where r.database_object_name = :p_database_object_name'||l_newline||
318 ' and e1.rule_id = r.rule_id'||l_newline||
319 ' and r.document_id = d.document_id'||l_newline||
320 ' AND sysdate between NVL(d.start_date_active, sysdate)'||l_newline||
321 ' and NVL(d.end_date_active, sysdate )'||l_newline||
322 ' and ('
323 ;
324
325 -- select only those rules where the attribute values match those
326 -- on the entity record
327
328 l_first_value := TRUE;
329 for l_index in 1..p_attribute_tbl.COUNT loop
330
331 if p_attribute_tbl(l_index).value is not null then
332
333 -- include 'or' in the statement if it is not the first attribute
334 if (l_first_value) then
335 l_rule_sql := l_rule_sql||l_newline||
336 ' (e1.attribute_code = :p_attribute_code'||l_index||l_newline||
337 ' and e1.attribute_value = :p_attribute_value'||l_index||')';
338 else
339 l_rule_sql := l_rule_sql||l_newline||
340 ' or (e1.attribute_code = :p_attribute_code'||l_index||l_newline||
341 ' and e1.attribute_value = :p_attribute_value'||l_index||')';
342 end if;
343
344 l_first_value := FALSE;
345 end if;
346
347 end loop;
348
349 -- but do not select the rules if they are AND conditions with attribute
350 -- values that do not match ( or <>) those on the entity
351
352 l_rule_sql := l_rule_sql||l_newline||
353 ' )'||l_newline||
354 ' and not exists ( select null'||l_newline||
355 ' from oe_attachment_rule_elements e2'||l_newline||
356 ' where e2.rule_id = r.rule_id'||l_newline||
357 ' and e2.group_number = e1.group_number'||l_newline||
358 ' and e2.rowid <> e1.rowid'||l_newline||
359 ' and (';
360
361 for l_index in 1..p_attribute_tbl.COUNT loop
362
363 -- include 'or' in the statements if it is not the first attribute
364 if l_index = 1 then
365 l_rule_sql := l_rule_sql||l_newline
366 ||' (e2.attribute_code = :p_attribute_code'||l_index||l_newline||
367 ' and e2.attribute_value <> nvl(:p_attribute_value'||l_index||','' ''))';
368 else
369 l_rule_sql := l_rule_sql||l_newline||
370 ' or (e2.attribute_code = :p_attribute_code'||l_index||l_newline||
371 ' and e2.attribute_value <> nvl(:p_attribute_value'||l_index||','' ''))';
372 end if;
373
374 end loop;
375
376 l_rule_sql := l_rule_sql||l_newline
377 ||' ) )';
378
379 IF l_debug_level > 0 THEN
380 oe_debug_pub.add( 'FINISHED BUILDING RULES SQL:' ) ;
381 END IF;
382 IF l_debug_level > 0 THEN
383 oe_debug_pub.add( L_RULE_SQL ) ;
384 END IF;
385
386 -------------------------------------------------------------------
387 -- CREATE DYNAMIC SQL CURSOR AND EXECUTE
388 -------------------------------------------------------------------
389 l_sqlCursor := DBMS_SQL.open_cursor;
390 IF l_debug_level > 0 THEN
391 oe_debug_pub.add( 'FETCH_DOCUMENTS:CURSOR OPEN OK' ) ;
392 END IF;
393
394 DBMS_SQL.parse(l_sqlCursor, l_rule_sql, DBMS_SQL.NATIVE);
395
396 DBMS_SQL.bind_variable(l_sqlCursor, 'p_database_object_name',p_database_object_name);
397 for l_index in 1..p_attribute_tbl.COUNT loop
398 DBMS_SQL.bind_variable(l_sqlCursor, 'p_attribute_code'||l_index,p_attribute_tbl(l_index).attribute_code );
399 DBMS_SQL.bind_variable(l_sqlCursor, 'p_attribute_value'||l_index,p_attribute_tbl(l_index).value );
400 end loop;
401
402 DBMS_SQL.define_column(l_sqlCursor, 1, l_document_id);
403
404 l_dummy := DBMS_SQL.execute(l_sqlCursor);
405 IF l_debug_level > 0 THEN
406 oe_debug_pub.add( 'FETCH_DOCUMENTS:EXECUTE OK' ) ;
407 END IF;
408
409
410 -------------------------------------------------------------------
411 -- FETCH VALUES FROM SQL CURSOR INTO l_document_tbl
412 -------------------------------------------------------------------
413
414 l_index := 1;
415 while (DBMS_SQL.fetch_rows(l_sqlCursor)<> 0) loop
416 IF l_debug_level > 0 THEN
417 oe_debug_pub.add( 'FETCH_DOCUMENTS:FETCH_ROWS OK' ) ;
418 END IF;
419 DBMS_SQL.column_value(l_sqlcursor, 1, l_document_id);
420 IF l_debug_level > 0 THEN
421 oe_debug_pub.add( 'DOCUMENT ID:'||L_DOCUMENT_ID ) ;
422 END IF;
423 l_document_tbl(l_index).document_id := l_document_id;
424 l_index := l_index+1;
425 end loop;
426 DBMS_SQL.close_cursor(l_sqlCursor);
427
428 IF l_debug_level > 0 THEN
429 oe_debug_pub.add( 'EXIT FETCH_DOCUMENTS' , 1 ) ;
430 END IF;
431
432 RETURN l_document_tbl;
433
434 END Fetch_Documents;
435
436 --------------------------------------------------------------------
437 /* PUBLIC PROCEDURES/FUNCTIONS */
438 --------------------------------------------------------------------
439
440 --------------------------------------------------------------------
441 PROCEDURE Add_Attachments_Automatic
442 (
443 p_api_version in number,
444 p_entity_name in varchar2,
445 p_pk1_value in varchar2,
446 p_pk2_value in varchar2 default null,
447 p_pk3_value in varchar2 default null,
448 p_pk4_value in varchar2 default null,
449 p_pk5_value in varchar2 default null,
450 p_commit in varchar2 := fnd_api.G_FALSE,
451 x_attachment_count out nocopy number,
452
453 x_return_status out nocopy varchar2,
454
455 x_msg_count out nocopy number,
456
457 x_msg_data out nocopy varchar2
458
459 )
460 IS
461 l_api_name CONSTANT VARCHAR2(30) := 'ADD_ATTACHMENTS_AUTOMATIC';
462 l_api_version_number CONSTANT NUMBER := 1.0;
463 l_attachment_id NUMBER;
464 l_attribute_tbl Attribute_Tbl_Type;
465 l_database_object_name VARCHAR2(30);
466 l_documentTbl Document_Tbl_Type;
467 l_documentID number;
468 i number;
469 l_attachment_exist number:=0;
470 l_need_delete_insert BOOLEAN :=TRUE;
471 --
472 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
473 --
474 BEGIN
475 IF l_debug_level > 0 THEN
476 oe_debug_pub.add( 'ENTER OE_FND_ATTACHMENTS_PVT.ADD_ATTACHMENTS_AUTOMATIC' , 1 ) ;
477 END IF;
478
479 -- Initialize API return status to success
480 x_return_status := FND_API.G_RET_STS_SUCCESS;
481
482 -- Standard call to check for call compatibility
483
484 IF NOT FND_API.Compatible_API_Call
485 ( l_api_version_number
486 , p_api_version
487 , l_api_name
488 , G_PKG_NAME
489 )
490 THEN
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492 END IF;
493
494 x_attachment_count := 0;
495
496 IF l_debug_level > 0 THEN
497 oe_debug_pub.add( 'GET DATABASE_OBJECT_NAME' , 1 ) ;
498 END IF;
499
500 SELECT database_object_name
501 INTO l_database_object_name
502 FROM oe_ak_objects_ext
503 WHERE data_object_code = p_entity_name;
504
505 --------------------------------------------------------------
506 -- FETCH VALUES OF ALL THE ATTACHMENTS ENABLED ATTRIBUTES
507 -- FROM THE ENTITY
508 --------------------------------------------------------------
509
510 l_attribute_tbl := Fetch_Attribute_Values
511 (p_entity_name => p_entity_name
512 ,p_database_object_name => l_database_object_name
513 ,p_pk1_value => p_pk1_value
514 ,p_pk2_value => p_pk2_value
515 ,p_pk3_value => p_pk3_value
516 ,p_pk4_value => p_pk4_value
517 ,p_pk5_value => p_pk5_value
518 );
519
520 ---------------------------------------------------------------
521 -- EVALUATE ADDITION RULES AND FETCH DOCUMENTS THAT SHOULD
522 -- BE ATTACHED TO THIS ENTITY
523 ---------------------------------------------------------------
524
525 l_documentTbl := Fetch_Documents
526 (p_database_object_name => l_database_object_name
527 ,p_attribute_tbl => l_attribute_tbl
528 );
529
530 IF l_debug_level > 0 THEN
531 oe_debug_pub.add( 'COUNT OF DOCUMENTS :'||L_DOCUMENTTBL.COUNT ||'FIRST ELEMENT: '||L_DOCUMENTTBL.FIRST ) ;
532 END IF;
533
534
535 ---------------------------------------------------------------------
536 -- DELETE EXISTING AUTOMATIC ATTACHMENTS
537 ---------------------------------------------------------------------
538
539 IF l_debug_level > 0 THEN
540 oe_debug_pub.add( 'DELETING EXISTING AUTOMATIC ATTACHMENTS' ) ;
541 END IF;
542 /* Check if the same attachment is already there */
543
544 /*6896311
545 BEGIN
546 select 1
547 into l_attachment_exist
548 from fnd_attached_documents
549 where entity_name = p_entity_name
550 and pk1_value = p_pk1_value
551 and nvl(pk2_value,'NULL') = nvl(p_pk2_value,'NULL')
552 and nvl(pk3_value,'NULL') = nvl(p_pk3_value,'NULL')
553 and nvl(pk4_value,'NULL') = nvl(p_pk4_value,'NULL')
554 and nvl(pk5_value,'NULL') = nvl(p_pk5_value,'NULL') ;
555
556 if l_attachment_exist > 0 THEN
557 l_need_delete_insert := FALSE;
558 IF l_debug_level > 0 THEN
559 oe_debug_pub.add( 'ATTACHMENTS EXIST DO NOT DELETE IT' ) ;
560 oe_debug_pub.add( 'entity Name :'|| p_entity_name,1);
561 oe_debug_pub.add( 'x_pk1_value:'|| p_pk1_value,1);
562 oe_debug_pub.add( 'x_pk2_value:'|| p_pk2_value,1);
563 oe_debug_pub.add( 'x_pk3_value:'|| p_pk3_value,1);
564 oe_debug_pub.add( ' x_pk4_value:'|| p_pk4_value,1);
565 oe_debug_pub.add( 'x_pk5_value:'|| p_pk5_value,1);
566 END IF;
567 else
568 l_need_delete_insert := TRUE;
569 IF l_debug_level > 0 THEN
570 oe_debug_pub.add( 'ATTACHMENTS DO NOT EXIST DELETE IF THERE IS
571 ANY' ) ;
572 oe_debug_pub.add( 'entity Name :'|| p_entity_name,1);
573 oe_debug_pub.add( 'x_pk1_value:'|| p_pk1_value,1);
574 oe_debug_pub.add( 'x_pk2_value:'|| p_pk2_value,1);
575 oe_debug_pub.add( 'x_pk3_value:'|| p_pk3_value,1);
576 oe_debug_pub.add( 'x_pk4_value:'|| p_pk4_value,1);
577 oe_debug_pub.add( 'x_pk5_value:'|| p_pk5_value,1);
578 END IF;
579 end if;
580 EXCEPTION
581 when others then
582 null;
583 END;
584
585 IF l_need_delete_insert THEN
586 6896311*/
587
588 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
589 (x_entity_name => p_entity_name
590 ,x_pk1_value => p_pk1_value
591 ,x_pk2_value => p_pk2_value
592 ,x_pk3_value => p_pk3_value
593 ,x_pk4_value => p_pk4_value
594 ,x_pk5_value => p_pk5_value
595 ,x_automatically_added_flag => 'Y'
596 );
597
598
599 ---------------------------------------------------------------------
600 -- CREATE AUTOMATIC ATTACHMENTS
601 ---------------------------------------------------------------------
602
603 IF l_debug_level > 0 THEN
604 oe_debug_pub.add( 'CREATING AUTOMATIC ATTACHMENTS' ) ;
605 END IF;
606
607 i := l_documentTbl.first;
608 while i is not null loop
609
610 -- attach the document to the entity
611 oe_fnd_attachments_pvt.Add_Attachment(
612 p_api_version => 1.0,
613 p_entity_name => p_entity_name,
614 p_pk1_value => p_pk1_value,
615 p_pk2_value => p_pk2_value,
616 p_pk3_value => p_pk3_value,
617 p_pk4_value => p_pk4_value,
618 p_pk5_value => p_pk5_value,
619 p_automatic_flag => 'Y',
620 p_document_id => l_documentTbl(i).document_id,
621 x_attachment_id => l_attachment_id,
622 x_return_status => x_return_status,
623 x_msg_count => x_msg_count,
624 x_msg_data => x_msg_data
625 );
626
627 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
628 RAISE FND_API.G_EXC_ERROR;
629 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
631 END IF;
632
633 i := l_documentTbl.next(i);
634
635 end loop;
636
637 x_attachment_count := l_documentTbl.COUNT;
638 x_return_status := FND_API.G_RET_STS_SUCCESS;
639
640 OE_MSG_PUB.Count_And_Get
641 ( p_count => x_msg_count
642 , p_data => x_msg_data
643 );
644
645 --6896311 END IF; --l_need_delete_insert ?
646
647 IF l_debug_level > 0 THEN
648 oe_debug_pub.add( 'EXIT OE_FND_ATTACHMENTS_PVT.ADD_ATTACHMENTS_AUTOMATIC' , 1 ) ;
649 END IF;
650
651 EXCEPTION
652 WHEN FND_API.G_EXC_ERROR THEN
653 x_return_status := FND_API.G_RET_STS_ERROR;
654 OE_MSG_PUB.Count_And_Get
655 ( p_count => x_msg_count
656 , p_data => x_msg_data
657 );
658 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
660 OE_MSG_PUB.Count_And_Get
661 ( p_count => x_msg_count
662 , p_data => x_msg_data
663 );
664 WHEN OTHERS THEN
665 IF l_debug_level > 0 THEN
666 oe_debug_pub.add( 'ADD_ATTACHMENTS_AUTOMATIC:AN EXCEPTION HAS OCCURED' , 1 ) ;
667 END IF;
668 IF l_debug_level > 0 THEN
669 oe_debug_pub.add( 'ERROR: '||SUBSTR ( SQLERRM , 1 , 250 ) , 1 ) ;
670 END IF;
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 IF OE_MSG_PUB.Check_Msg_Level
673 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
674 THEN
675 OE_MSG_PUB.Add_Exc_Msg
676 ( G_PKG_NAME
677 , l_api_name
678 );
679 END IF;
680 OE_MSG_PUB.Count_And_Get
681 ( p_count => x_msg_count
682 , p_data => x_msg_data
683 );
684 END Add_Attachments_Automatic;
685
686
687 --------------------------------------------------------------------
688 PROCEDURE Add_Attachment
689 (
690 p_api_version in number,
691 p_entity_name in varchar,
692 p_pk1_value in varchar2,
693 p_pk2_value in varchar2 default null,
694 p_pk3_value in varchar2 default null,
695 p_pk4_value in varchar2 default null,
696 p_pk5_value in varchar2 default null,
697 p_automatic_flag in varchar2 default 'N',
698 p_document_id in number,
699 p_validate_flag in varchar2 default 'Y',
700 x_attachment_id out nocopy number,
701
702 x_return_status out nocopy varchar2,
703
704 x_msg_count out nocopy number,
705
706 x_msg_data out nocopy varchar2
707
708 )
709 --------------------------------------------------------------------
710 IS
711 l_api_name CONSTANT VARCHAR2(30) := 'ADD_ATTACHMENT';
712 l_api_version_number CONSTANT NUMBER := 1.0;
713
714 l_attachment_id number;
715 l_dummy varchar(1);
716
717 CURSOR C_DOC
718 IS
719 SELECT 'Y'
720 FROM FND_DOCUMENTS
721 WHERE document_id = p_document_id;
722
723 l_user_id number:=fnd_global.USER_ID;
724 l_login_id number:=fnd_global.LOGIN_ID;
725
726 l_seq_num NUMBER;
727 l_attached_document_id NUMBER;
728 l_media_id NUMBER;
729 l_document_id NUMBER := p_document_id;
730 l_rowid VARCHAR2(60);
731 --included to fix bug 1903257 Begin
732 l_usage_type fnd_documents.usage_type%TYPE := null;
733 l_datatype_id fnd_documents.datatype_id%TYPE := null;
734 l_category_id fnd_documents.category_id%TYPE := null;
735 l_security_type fnd_documents.security_type%TYPE := null;
736 l_publish_flag fnd_documents.publish_flag%TYPE := null;
737 l_description fnd_documents_vl.description%TYPE := null;
738 l_file_name fnd_documents_vl.file_name%TYPE := null;
739 l_create_doc varchar2(1):= null;
740 --included to fix bug 1903257 End
741 l_title fnd_documents_vl.title%TYPE := null; ---bug 12402550
742
743 --
744 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
745 --
746 BEGIN
747
748 -- Initialize API return status to success
749 x_return_status := FND_API.G_RET_STS_SUCCESS;
750
751 -- Standard call to check for call compatibility
752
753 IF NOT FND_API.Compatible_API_Call
754 ( l_api_version_number
755 , p_api_version
756 , l_api_name
757 , G_PKG_NAME
758 )
759 THEN
760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
761 END IF;
762
763 if ( p_validate_flag = 'Y') then
764
765 -- validate the document_id
766 open C_DOC;
767 fetch C_DOC into l_dummy;
768 if (C_DOC%NOTFOUND) then
769 -- invalid document id!
770 close C_DOC;
771 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_DOCUMENT_ID');
772 OE_MSG_PUB.ADD;
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 end if;
775 close C_DOC;
776
777 end if;
778
779 /*
780 -- validate the entity
781 BEGIN
782 select data_object_code
783 into l_data_object
784 from oe_ak_obj_attr_ext
785 where database_object_name = p_database_object_name
786 and data_object_code is not null;
787 EXCEPTION
788 WHEN no_data_found THEN
789 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_DOCUMENT_ENTITY');
790 OE_MSG_PUB.ADD;
791 RAISE FND_API.G_EXC_ERROR;
792 END;
793 */
794
795 -- calculate the sequence number for the document and
796 -- the attached document id to be passed to the AOL API
797
798 select (nvl(max(seq_num),0) + 10)
799 into l_seq_num
800 from fnd_attached_documents
801 where entity_name = p_entity_name
802 and pk1_value = p_pk1_value;
803
804 select fnd_attached_documents_s.nextval
805 into l_attached_document_id
806 from dual;
807 --Code changes made to fix bug 1903257 Begin
808 begin
809 select usage_type
810 into l_usage_type
811 from fnd_documents
812 where document_id = p_document_id ;
813 if l_usage_type = 'S' then
814 l_create_doc := null;
815 elsif l_usage_type in ('T','O') then
816 begin
817 select datatype_id,category_id,security_type,
818 publish_flag,media_id,file_name,description , title
819 into l_datatype_id,l_category_id,l_security_type,
820 l_publish_flag,l_media_id,l_file_name,l_description ,l_title ---bug 12402550
821 from fnd_documents_vl where document_id = p_document_id;
822 l_create_doc := 'Y';
823 l_document_id := null;
824 exception
825 when others then
826 null;
827 end;
828 end if;
829 exception
830 when others then
831 null;
832 end;
833 --Code changes made to fix bug 1903257 End
834 -- included a new parameter X_Create_doc below to fix bug 1903257
835
836 FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
837 (x_rowid => l_rowid
838 , x_attached_document_id => l_attached_document_id
839 , x_document_id => l_document_id
840 , x_seq_num => l_seq_num
841 , x_entity_name => p_entity_name
842 , x_pk1_value => p_pk1_value
843 , x_pk2_value => p_pk2_value
844 , x_pk3_value => p_pk3_value
845 , x_pk4_value => p_pk4_value
846 , x_pk5_value => p_pk5_value
847 , x_automatically_added_flag => p_automatic_flag
848 , x_creation_date => sysdate
849 , x_created_by => l_user_id
850 , x_last_update_date => sysdate
851 , x_last_updated_by => l_user_id
852 , x_last_update_login => l_login_id
853 -- following parameters are required for the API but we do not
854 -- use so send in as null
855 , x_column1 => null
856 , x_datatype_id => l_datatype_id
857 , x_category_id => l_category_id
858 , x_security_type => l_security_type
859 , X_security_id => null
860 , X_publish_flag => l_publish_flag
861 , X_image_type => null
862 , X_storage_type => null
863 , X_usage_type => l_usage_type
864 , X_language => null
865 , X_description => l_description
866 , X_file_name => l_file_name
867 , X_media_id => l_media_id
868 , X_doc_attribute_Category => null
869 , X_doc_attribute1 => null
870 , X_doc_attribute2 => null
871 , X_doc_attribute3 => null
872 , X_doc_attribute4 => null
873 , X_doc_attribute5 => null
874 , X_doc_attribute6 => null
875 , X_doc_attribute7 => null
876 , X_doc_attribute8 => null
877 , X_doc_attribute9 => null
878 , X_doc_attribute10 => null
879 , X_doc_attribute11 => null
880 , X_doc_attribute12 => null
881 , X_doc_attribute13 => null
882 , X_doc_attribute14 => null
883 , X_doc_attribute15 => null
884 , X_create_doc => l_create_doc
885 ,X_title => l_title ---bug 12402550
886 );
887
888 x_attachment_id := l_attached_document_id;
889
890 OE_MSG_PUB.Count_And_Get
891 ( p_count => x_msg_count
892 , p_data => x_msg_data
893 );
894
895 EXCEPTION
896 WHEN FND_API.G_EXC_ERROR THEN
897 x_return_status := FND_API.G_RET_STS_ERROR;
898 OE_MSG_PUB.Count_And_Get
899 ( p_count => x_msg_count
900 , p_data => x_msg_data
901 );
902 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904 OE_MSG_PUB.Count_And_Get
905 ( p_count => x_msg_count
906 , p_data => x_msg_data
907 );
908 WHEN OTHERS THEN
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910 IF OE_MSG_PUB.Check_Msg_Level
911 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
912 THEN
913 OE_MSG_PUB.Add_Exc_Msg
914 ( G_PKG_NAME
915 , l_api_name
916 );
917 END IF;
918 OE_MSG_PUB.Count_And_Get
919 ( p_count => x_msg_count
920 , p_data => x_msg_data
921 );
922 END Add_Attachment;
923 --------------------------------------------------------------------
924
925
926 -- Start of Comments
927 -- API name Delete_Attachments
928 -- Type PRIVATE
929 -- Function
930 --
931 -- Pre-reqs
932 --
933 -- Parameters
934 --
935 -- Version Current version = 1.0
936 -- Initial version = 1.0
937 --
938 -- Notes
939 --
940 -- End of Comments
941 ---------------------------------------------------------------------
942 PROCEDURE Delete_Attachments
943 --------------------------------------------------------------------
944 (
945 p_api_version in number,
946 p_entity_name in varchar2,
947 p_pk1_value in varchar2,
948 p_pk2_value in varchar2 default null,
949 p_pk3_value in varchar2 default null,
950 p_pk4_value in varchar2 default null,
951 p_pk5_value in varchar2 default null,
952 p_automatic_atchmts_only in varchar2 default 'N',
953 x_return_status out nocopy varchar2
954
955 )
956 IS
957 --
958 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
959 --
960 BEGIN
961
962 x_return_status := FND_API.G_RET_STS_SUCCESS;
963
964 FND_ATTACHED_DOCUMENTS2_PKG.Delete_Attachments
965 (x_entity_name => p_entity_name
966 ,x_pk1_value => p_pk1_value
967 ,x_pk2_value => p_pk2_value
968 ,x_pk3_value => p_pk3_value
969 ,x_pk4_value => p_pk4_value
970 ,x_pk5_value => p_pk5_value
971 );
972
973 EXCEPTION
974 WHEN OTHERS THEN
975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
976 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
977 THEN
978 OE_MSG_PUB.Add_Exc_Msg
979 ( G_PKG_NAME
980 , 'Delete_Attachments'
981 );
982 END IF;
983 END Delete_Attachments;
984
985 END oe_fnd_attachments_pvt;