[Home] [Help]
PACKAGE BODY: APPS.FND_ATTACHMENT_UTIL_PKG
Source
1 PACKAGE BODY fnd_attachment_util_pkg as
2 /* $Header: AFAKUTLB.pls 120.3 2010/08/30 19:21:37 ctilley ship $ */
3
4
5 FUNCTION get_atchmt_exists(l_entity_name VARCHAR2,
6 l_pkey1 VARCHAR2,
7 l_pkey2 VARCHAR2 DEFAULT NULL,
8 l_pkey3 VARCHAR2 DEFAULT NULL,
9 l_pkey4 VARCHAR2 DEFAULT NULL,
10 l_pkey5 VARCHAR2 DEFAULT NULL,
11 l_function_name VARCHAR2 DEFAULT NULL,
12 l_function_type VARCHAR2 DEFAULT NULL)
13 RETURN VARCHAR2 IS
14 l_exists VARCHAR2(1);
15 l2_function_name VARCHAR2(30);
16 l2_function_type VARCHAR2(1);
17 BEGIN
18 -- if the function name is not passed in, try using the server-side
19 -- package global
20 IF (l_function_name IS NULL) THEN
21 l2_function_name := FND_ATTACHMENT_UTIL_PKG.function_name;
22 ELSE
23 l2_function_name := l_function_name;
24 END IF;
25
26 IF (l_function_type IS NULL) THEN
27 l2_function_type := FND_ATTACHMENT_UTIL_PKG.function_type;
28 ELSE
29 l2_function_type := l_function_type;
30 END IF;
31
32 IF (l_pkey2 is not null) and (l_pkey3 is not null) then
33
34 SELECT 'Y'
35 INTO l_exists
36 FROM fnd_doc_category_usages fdcu,
37 fnd_documents fd,
38 fnd_attachment_functions af,
39 fnd_attached_documents fad
40 WHERE fad.entity_name = l_entity_name
41 AND fad.pk1_value = l_pkey1
42 AND fad.pk2_value = l_pkey2
43 AND fad.pk3_value = l_pkey3
44 AND (l_pkey4 IS NULL
45 OR fad.pk4_value = l_pkey4)
46 AND (l_pkey5 IS NULL
47 OR fad.pk5_value = l_pkey5)
48 AND fad.document_id = fd.document_id
49 AND fd.category_id = fdcu.category_id
50 AND fdcu.attachment_function_id = af.attachment_function_id
51 AND fdcu.enabled_flag = 'Y'
52 and exists (select 'x' from fnd_attachment_functions af
53 where af.function_type = l2_function_type
54 AND af.function_name = l2_function_name
55 AND fdcu.attachment_function_id = af.attachment_function_id)
56 AND ROWNUM = 1;
57
58 ELSE IF (l_pkey2 is not null) then
59
60 SELECT 'Y'
61 INTO l_exists
62 FROM fnd_doc_category_usages fdcu,
63 fnd_documents fd,
64 fnd_attached_documents fad,
68 AND fad.pk2_value = l_pkey2
65 fnd_attachment_functions af
66 WHERE fad.entity_name = l_entity_name
67 AND fad.pk1_value = l_pkey1
69 AND (l_pkey3 IS NULL
70 OR fad.pk3_value = l_pkey3)
71 AND (l_pkey4 IS NULL
72 OR fad.pk4_value = l_pkey4)
73 AND (l_pkey5 IS NULL
74 OR fad.pk5_value = l_pkey5)
75 AND fad.document_id = fd.document_id
76 AND fd.category_id = fdcu.category_id
77 AND fdcu.attachment_function_id = af.attachment_function_id
78 AND fdcu.enabled_flag = 'Y'
79 and exists (select 'x' from fnd_attachment_functions af
80 where af.function_type = l2_function_type
81 AND af.function_name = l2_function_name
82 AND fdcu.attachment_function_id = af.attachment_function_id)
83 AND ROWNUM = 1;
84
85 ELSE
86
87 SELECT 'Y'
88 INTO l_exists
89 FROM fnd_doc_category_usages fdcu,
90 fnd_documents fd,
91 fnd_attached_documents fad,
92 fnd_attachment_functions af
93 WHERE fad.entity_name = l_entity_name
94 AND fad.pk1_value = l_pkey1
95 AND (l_pkey2 IS NULL
96 OR fad.pk2_value = l_pkey2)
97 AND (l_pkey3 IS NULL
98 OR fad.pk3_value = l_pkey3)
99 AND (l_pkey4 IS NULL
100 OR fad.pk4_value = l_pkey4)
101 AND (l_pkey5 IS NULL
102 OR fad.pk5_value = l_pkey5)
103 AND fad.document_id = fd.document_id
104 AND fd.category_id = fdcu.category_id
105 AND fdcu.attachment_function_id = af.attachment_function_id
106 AND fdcu.enabled_flag = 'Y'
107 and exists (select 'x' from fnd_attachment_functions af
108 where af.function_type = l2_function_type
109 AND af.function_name = l2_function_name
110 AND fdcu.attachment_function_id = af.attachment_function_id)
111 AND ROWNUM = 1;
112 END IF;
113 END IF;
114
115 IF (l_exists<>'Y') THEN
116 return('N');
117 ELSE
118 return('Y');
119 END IF;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 return('N');
124
125 END get_atchmt_exists;
126
127
128 FUNCTION get_atchmt_exists_sql(l_entity_name VARCHAR2,
129 l_pkey1 VARCHAR2,
130 l_pkey2 VARCHAR2 DEFAULT NULL,
131 l_pkey3 VARCHAR2 DEFAULT NULL,
132 l_pkey4 VARCHAR2 DEFAULT NULL,
133 l_pkey5 VARCHAR2 DEFAULT NULL,
134 l_sqlstmt VARCHAR2 DEFAULT NULL,
135 l_function_name VARCHAR2 DEFAULT NULL,
136 l_function_type VARCHAR2 DEFAULT NULL)
137 RETURN VARCHAR2 IS
138 l_exists VARCHAR2(1);
139 l2_function_name VARCHAR2(30);
140 l2_function_type VARCHAR2(1);
141 l_cursor INTEGER;
142 l_rows_processed INTEGER;
143 l_exists_flag VARCHAR2(1);
144 l_sql_stmt VARCHAR2(2000);
145 BEGIN
146 -- if the function name is not passed in, try using the server-side
147 -- package global
148 IF (l_function_name IS NULL) THEN
149 l2_function_name := FND_ATTACHMENT_UTIL_PKG.function_name;
150 ELSE
151 l2_function_name := l_function_name;
152 END IF;
153
154 IF (l_function_type IS NULL) THEN
155 l2_function_type := FND_ATTACHMENT_UTIL_PKG.function_type;
156 ELSE
157 l2_function_type := l_function_type;
158 END IF;
159
160
161 l_sql_stmt := 'SELECT ''Y'' FROM dual WHERE EXISTS ('||
162 'SELECT 1 FROM fnd_attached_documents fad,'||
163 'fnd_doc_category_usages fdcu,'||
164 'fnd_attachment_functions af,'||
165 'fnd_documents fd '||
166 'WHERE fad.entity_name = :ename '||
167 'AND fad.document_id = fd.document_id '||
168 'AND fd.category_id = fdcu.category_id '||
169 'AND fdcu.attachment_function_id = af.attachment_function_id '||
170 'AND fdcu.enabled_flag = ''Y'' '||
171 'AND af.function_type = :function_type '||
172 'AND af.function_name = :function_name ';
173
174 -- include primary keys only if not null
175 IF (l_pkey1 IS NOT NULL) THEN
176 l_sql_stmt := l_sql_stmt|| 'AND fad.pk1_value = :pkey1 ';
177 END IF;
178
179 IF (l_pkey2 IS NOT NULL) THEN
180 l_sql_stmt := l_sql_stmt|| 'AND fad.pk2_value = :pkey2 ';
181 END IF;
182
183 IF (l_pkey3 IS NOT NULL) THEN
184 l_sql_stmt := l_sql_stmt|| 'AND fad.pk3_value = :pkey3 ';
185 END IF;
186
187 IF (l_pkey4 IS NOT NULL) THEN
188 l_sql_stmt := l_sql_stmt|| 'AND fad.pk4_value = :pkey4 ';
189 END IF;
190
191 IF (l_pkey5 IS NOT NULL) THEN
192 l_sql_stmt := l_sql_stmt|| 'AND fad.pk5_value = :pkey5 ';
193 END IF;
194
195 -- open the cursor
196 l_cursor := dbms_sql.open_cursor;
197 -- parse the sql
198 dbms_sql.parse(l_cursor, l_sql_stmt||' '||l_sqlstmt||')',
199 dbms_sql.v7);
200
201 -- define the column
202 dbms_sql.define_column(l_cursor, 1, l_exists_flag, 1);
203
204 -- bind variables
205 dbms_sql.bind_variable(l_cursor, ':ename', l_entity_name);
206 dbms_sql.bind_variable(l_cursor, ':function_type', l2_function_type);
207 dbms_sql.bind_variable(l_cursor, ':function_name', l2_function_name);
208
209 IF (l_pkey1 IS NOT NULL) THEN
210 dbms_sql.bind_variable(l_cursor, ':pkey1', l_pkey1);
211 END IF;
212
213 IF (l_pkey2 IS NOT NULL) THEN
217 IF (l_pkey3 IS NOT NULL) THEN
214 dbms_sql.bind_variable(l_cursor, ':pkey2', l_pkey2);
215 END IF;
216
218 dbms_sql.bind_variable(l_cursor, ':pkey3', l_pkey3);
219 END IF;
220
221 IF (l_pkey4 IS NOT NULL) THEN
222 dbms_sql.bind_variable(l_cursor, ':pkey4', l_pkey4);
223 END IF;
224
225 IF (l_pkey5 IS NOT NULL) THEN
226 dbms_sql.bind_variable(l_cursor, ':pkey5', l_pkey5);
227 END IF;
228
229
230 l_rows_processed := dbms_sql.execute_and_fetch(l_cursor);
231 dbms_sql.column_value(l_cursor,1,l_exists_flag);
232 dbms_sql.close_cursor(l_cursor);
233
234 IF (l_exists_Flag = 'Y') THEN
235 return('Y');
236 ELSE
237 return('N');
238 END IF;
239
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 dbms_sql.close_cursor(l_cursor);
244 return('N');
245
246 END get_atchmt_exists_sql;
247
248
249 PROCEDURE init_atchmt(l_function_name IN OUT NOCOPY VARCHAR2,
250 attachments_defined_flag OUT NOCOPY BOOLEAN,
251 l_function_type IN OUT NOCOPY VARCHAR2) IS
252 m_function_name VARCHAR2(40);
253 m_rowcount NUMBER;
254 BEGIN
255
256
257 -- get attachment definition tied to function
258 SELECT count(*)
259 INTO m_rowcount
260 FROM fnd_attachment_functions
261 WHERE function_name = l_function_name
262 AND function_type = 'F';
263
264 IF (m_rowcount > 0) THEN
265 l_function_name := l_function_name;
266 attachments_defined_Flag := TRUE;
267 l_function_type := 'F';
268 -- set package global
269 FND_ATTACHMENT_UTIL_PKG.function_name := l_function_name;
270 FND_ATTACHMENT_UTIL_PKG.function_type := l_function_type;
271 return;
272 ELSE
273 -- get attachment definition tied to form
274 SELECT function_name,
275 count(*)
276 INTO m_function_name,
277 m_rowcount
278 FROM fnd_attachment_functions
279 WHERE function_type = 'O'
280 AND (function_name, application_id) =
281 (SELECT distinct ff.form_name, ff.application_id
282 FROM fnd_form ff,
283 fnd_form_functions fff
284 WHERE ff.application_id = fff.application_id
285 AND ff.form_id = fff.form_id
286 AND fff.function_name = l_function_name)
287 GROUP BY function_name;
288
289 IF (m_rowcount > 0) THEN
290 l_function_name := m_function_name;
291 attachments_defined_Flag := TRUE;
292 l_function_type := 'O';
293 -- set package global
294 FND_ATTACHMENT_UTIL_PKG.function_name := l_function_name;
295 FND_ATTACHMENT_UTIL_PKG.function_type := l_function_type;
296 return;
297 END IF;
298 END IF;
299
300 -- neither check got any attachment definition, so
301 -- return FALSE
302 attachments_defined_flag := FALSE;
303 FND_ATTACHMENT_UTIL_PKG.function_name := null;
304 FND_ATTACHMENT_UTIL_PKG.function_type := null;
305
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 attachments_defined_flag := FALSE;
310 FND_ATTACHMENT_UTIL_PKG.function_name := null;
311 FND_ATTACHMENT_UTIL_PKG.function_type := null;
312
313 END init_atchmt;
314
315 PROCEDURE init_atchmt(l_function_name IN OUT NOCOPY VARCHAR2,
316 attachments_defined_flag OUT NOCOPY BOOLEAN,
317 l_enabled_flag OUT NOCOPY VARCHAR2,
318 l_session_context_field OUT NOCOPY VARCHAR2,
319 l_function_type OUT NOCOPY VARCHAR2) IS
320 m_function_name VARCHAR2(40);
321 m_enabled_flag VARCHAR2(1);
322 m_session_context_field VARCHAR2(61);
323 BEGIN
324
325 BEGIN
326 -- get attachment definition tied to function
327 SELECT enabled_flag,session_context_field
328 INTO m_enabled_flag,m_session_context_field
329 FROM fnd_attachment_functions
330 WHERE function_name = l_function_name
331 AND function_type = 'F';
332
333 IF (m_enabled_flag = 'Y') THEN
334 l_function_name := l_function_name;
335 attachments_defined_Flag := TRUE;
336 l_enabled_flag := m_enabled_flag;
337 l_session_context_field := m_session_context_field;
338 l_function_type := 'F';
339 -- set package global
340 FND_ATTACHMENT_UTIL_PKG.function_name := l_function_name;
341 FND_ATTACHMENT_UTIL_PKG.function_type := 'F';
342 return;
343 ELSIF (m_enabled_flag = 'N') THEN
344 l_function_name := l_function_name;
345 attachments_defined_flag := FALSE;
346 l_enabled_flag := m_enabled_flag;
347 l_session_context_field := m_session_context_field;
348 l_function_type := 'F';
349 return;
350 END IF;
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN null;
353 WHEN OTHERS THEN RAISE;
354 END;
355
356
357 -- get attachment definition tied to form
358 SELECT function_name,
359 enabled_flag,
360 session_context_field
361 INTO m_function_name,
362 m_enabled_flag,
363 m_session_context_field
364 FROM fnd_attachment_functions
365 WHERE function_type = 'O'
366 AND (function_name, application_id) =
367 (SELECT distinct ff.form_name, ff.application_id
371 AND ff.form_id = fff.form_id
368 FROM fnd_form ff,
369 fnd_form_functions fff
370 WHERE ff.application_id = fff.application_id
372 AND fff.function_name = l_function_name);
373
374 IF ( m_enabled_flag = 'Y') THEN
375 l_function_name := m_function_name;
376 attachments_defined_Flag := TRUE;
377 l_enabled_flag := m_enabled_flag;
378 l_session_context_field := m_session_context_field;
379 l_function_type := 'O';
380 -- set package global
381 FND_ATTACHMENT_UTIL_PKG.function_name := l_function_name;
382 FND_ATTACHMENT_UTIL_PKG.function_type := 'O';
383 return;
384 ELSIF (m_enabled_flag = 'N' ) THEN
385 l_function_name := l_function_name;
386 attachments_defined_flag := FALSE;
387 l_enabled_flag := m_enabled_flag;
388 l_session_context_field := m_session_context_field;
389 l_function_type := 'F';
390 FND_ATTACHMENT_UTIL_PKG.function_name := l_function_name;
391 FND_ATTACHMENT_UTIL_PKG.function_type := 'O';
392 return;
393 END IF;
394
395 -- neither check got any attachment definition, so
396 -- return FALSE
397 attachments_defined_flag := FALSE;
398 FND_ATTACHMENT_UTIL_PKG.function_name := null;
399 FND_ATTACHMENT_UTIL_PKG.function_type := null;
400
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 attachments_defined_flag := FALSE;
404 FND_ATTACHMENT_UTIL_PKG.function_name := null;
405 FND_ATTACHMENT_UTIL_PKG.function_type := null;
406
407 WHEN OTHERS THEN
408 attachments_defined_flag := FALSE;
409 FND_ATTACHMENT_UTIL_PKG.function_name := null;
410 FND_ATTACHMENT_UTIL_PKG.function_type := null;
411
412 END init_atchmt;
413
414 PROCEDURE init_form(X_entity_name IN VARCHAR2,
415 X_user_entity_name OUT NOCOPY VARCHAR2,
416 X_doc_type_meaning OUT NOCOPY VARCHAR2) IS
417 CURSOR c1 IS
418 SELECT meaning
419 FROM fnd_lookup_values
420 WHERE lookup_type = 'ATCHMT_DOCUMENT_TYPE'
421 AND lookup_code = 'O';
422
423 CURSOR c2 IS
424 SELECT user_entity_name
425 FROM fnd_document_entities_vl
426 WHERE data_object_code = X_entity_name;
427 BEGIN
428
429 OPEN c1;
430 FETCH c1 INTO X_doc_type_meaning;
431 CLOSE c1;
432
433
434 IF (X_entity_name IS NOT NULL) THEN
435 OPEN c2;
436 FETCH c2 INTO X_user_entity_name;
437 CLOSE c2;
438 END IF;
439
440 EXCEPTION
441 WHEN NO_DATA_FOUND THEN return;
442
443
444 END init_form;
445
446
447
448 PROCEDURE init_doc_form(X_category_name IN VARCHAR2 DEFAULT NULL,
449 X_category_id OUT NOCOPY NUMBER,
450 X_category_desc OUT NOCOPY VARCHAR2,
451 X_security_type IN NUMBER DEFAULT NULL,
452 X_security_id IN NUMBER DEFAULT NULL,
453 X_security_desc OUT NOCOPY VARCHAR2) IS
454 CURSOR c1 IS
455 SELECT category_id, user_name
456 FROM fnd_document_categories_vl
457 WHERE name = X_category_name;
458
459 l_cursor INTEGER;
460 rows_processed INTEGER;
461 l_security_desc VARCHAR2(255);
462
463 BEGIN
464 -- Get category info
465 IF (X_category_name IS NOT NULL) THEN
466 OPEN c1;
467 FETCH c1 INTO X_category_id, X_category_desc;
468 CLOSE c1;
469 END IF;
470
471 -- get set of books description if security type = 2
472 IF (X_security_type = 2) THEN
473 -- user dynamic sql as AOL may not have reference to
474 -- sets of books
475 l_cursor := dbms_sql.open_cursor;
476 -- parse the statement
477 dbms_sql.parse(l_cursor,
478 'SELECT short_name FROM gl_sets_of_books '||
479 'WHERE set_of_books_id = :sob_id', dbms_sql.v7);
480 -- define the column
481 dbms_sql.define_column(l_cursor, 1, l_security_desc, 20);
482 -- bind variable
483 dbms_sql.bind_variable(l_cursor, ':sob_id', X_security_id);
484
485 rows_processed := dbms_sql.execute_and_fetch(l_cursor);
486 dbms_sql.column_value(l_cursor,1,l_security_desc);
487 dbms_sql.close_cursor(l_cursor);
488 X_security_desc := l_security_desc;
489
490 END IF;
491
492 END init_doc_form;
493
494
495 FUNCTION get_atchmt_function_name RETURN VARCHAR2 IS
496 BEGIN
497 -- return the value in the package global
498 RETURN(FND_ATTACHMENT_UTIL_PKG.function_name);
499 END get_atchmt_function_name;
500
501
502 FUNCTION get_user_function_name(x_function_type IN VARCHAR2,
503 x_application_id IN NUMBER,
504 x_function_name IN VARCHAR2) RETURN VARCHAR2 IS
505 CURSOR get_form IS
506 SELECT user_form_name
507 FROM fnd_form_vl
508 WHERE form_name = x_function_name
509 AND application_id = x_application_id;
510
511 CURSOR get_function IS
512 SELECT user_function_name
516 CURSOR get_report IS
513 FROM fnd_form_functions_vl
514 WHERE function_name = x_function_name;
515
517 SELECT user_concurrent_program_name
518 FROM fnd_concurrent_programs_vl
519 WHERE application_id = x_application_id
520 AND concurrent_program_name = x_function_name;
521
522 l_function_name VARCHAR2(255);
523 BEGIN
524 IF (x_function_type = 'F') THEN
525 -- get function name
526 OPEN get_function;
527 FETCH get_function INTO l_function_name;
528 CLOSE get_function;
529 RETURN(l_function_name);
530
531
532 ELSIF (x_function_type = 'O') THEN
533 -- get form name
534 OPEN get_form;
535 FETCH get_form INTO l_function_name;
536 CLOSE get_form;
537 RETURN(l_function_name);
538
539 ELSIF (x_function_type = 'R') THEN
540 -- get concurrent program name
541 OPEN get_report;
542 FETCH get_report INTO l_function_name;
543 CLOSE get_report;
544 RETURN(l_function_name);
545 ELSE
546 RETURN('INVALID_FUNCTION');
547 END IF;
548
549
550 END get_user_function_name;
551
552 PROCEDURE update_file_metadata(X_file_id IN NUMBER DEFAULT NULL) IS
553
554 l_file_name varchar2(255);
555 l_file_name_lob varchar2(255);
556 l_file_name_tl varchar2(255);
557 l_content_type varchar2(255);
558 l_document_id number := 0;
559 l_language varchar2(16);
560 beg_file_loc number := 1;
561 -- ctx_format varchar2(20);
562
563 BEGIN
564
565 -- get file name and language.
566 select file_name,language
567 into l_file_name_lob, l_language
568 from fnd_lobs
569 where file_id = update_file_metadata.X_file_id ;
570
571 -- Parse the file name to get rid of the directory
572 -- l_file_name := substr(l_file_name_lob,instr(l_file_name_lob,'A',-1)); Original code
573 LOOP
574 if (upper(substr(l_file_name_lob,instr(l_file_name_lob,'.',-1)-beg_file_loc,1)) between 'A' and 'Z'
575 or
576 substr(l_file_name_lob,instr(l_file_name_lob,'.',-1)-beg_file_loc,1) between '0' and '9')
577 then
578 beg_file_loc := beg_file_loc+1;
579 else
580 l_file_name := substr(l_file_name_lob,instr(l_file_name_lob,'.',-1)-beg_file_loc+1);
581 exit;
582 end if;
583 END LOOP;
584
585 -- Get file name from fnd_documents_tl
586 -- BUG#1560000 Added language = l_language to select statement
587 select document_id,file_name
588 into l_document_id,l_file_name_tl
589 from fnd_documents
590 where file_name like '%'||l_file_name ||'%';
591
592 -- Extract content type from file name.
593 l_content_type := substr (l_file_name_tl,instr(l_file_name_tl, ':') + 1 );
594
595 -- Update fnd_document_tl
596 update fnd_documents
597 set file_name = l_file_name,
598 media_id = update_file_metadata.X_file_id
599 where document_id = l_document_id;
600
601 -- Update fnd_lobs
602 update fnd_lobs
603 set file_content_type = l_content_type,
604 program_name = 'FNDATTCH',
605 file_format = fnd_gfm.set_file_format(l_content_type)
606 where file_id = X_file_id;
607
608 EXCEPTION
609 when NO_DATA_FOUND then
610 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
611 fnd_message.set_token('ROUTINE','File ID: '||
612 update_file_metadata.X_file_id);
613 fnd_message.set_token('ERRNO', SQLCODE);
614 fnd_message.set_token('REASON', SQLERRM);
615 RAISE;
616
617 END update_file_metadata;
618
619 ----------------------------------------------------------------------------
620 -- MergeAttachments (PUBLIC)
621 -- This is the procedure being called during the Party Merge.
622 -- FND_ATTACHMENT_UTIL_PKG.MergeAttachments() has been registered
623 -- in Party Merge Data Dict.
624 -- The input/output arguments format matches the document PartyMergeDD.doc.
625 --
626 -- Usage example in pl/sql
627 -- This procedure should only be called from the PartyMerge utility.
628 --
629 procedure MergeAttachments(p_entity_name in varchar2,
630 p_from_id in number,
631 p_to_id in out nocopy number,
632 p_from_fk_id in varchar2,
633 p_to_fk_id in varchar2,
634 p_parent_entity_name in varchar2,
635 p_batch_id in number,
636 p_batch_party_id in number,
637 p_return_status in out nocopy varchar2) is
638 pen varchar2(100);
639 begin
640 p_return_status := FND_API.G_RET_STS_SUCCESS;
641
642 if p_parent_entity_name is NULL then
643 pen := 'HZ_PARTIES';
644 else
645 pen := p_parent_entity_name;
646 end if;
647
648 if (p_from_fk_id <> p_to_fk_id) then
649
650 update fnd_attached_documents
651 set pk1_value = p_to_fk_id
652 where pk1_value = p_from_fk_id
653 and entity_name = pen;
654 -- For Bulk processing this condition has been removed. Updated ldt to include
655 -- bulk flag = 'Y'
656 -- and attached_document_id = p_from_id;
657
658 p_to_id := p_from_id;
659
660 end if;
661
662 end MergeAttachments;
663
664 PROCEDURE MergeCustAttach (
665 req_id NUMBER,
666 set_num NUMBER,
667 process_mode VARCHAR2) IS
668
669 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
670 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
671 INDEX BY BINARY_INTEGER;
675 FND_ATTACHED_DOCUMENTS.PK1_VALUE%TYPE
672 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
673
674 TYPE PK1_VALUE_LIST_TYPE IS TABLE OF
676 INDEX BY BINARY_INTEGER;
677 NUM_COL1_ORIG_LIST PK1_VALUE_LIST_TYPE;
678 NUM_COL1_NEW_LIST PK1_VALUE_LIST_TYPE;
679
680 l_profile_val VARCHAR2(30);
681 CURSOR merged_records IS
682 SELECT distinct CUSTOMER_MERGE_HEADER_ID
683 ,PK1_VALUE
684 FROM FND_ATTACHED_DOCUMENTS yt, ra_customer_merges m
685 WHERE (
686 yt.PK1_VALUE = m.DUPLICATE_ID
687 AND yt.entity_name = 'AR_CUSTOMERS'
688 ) AND m.process_flag = 'N'
689 AND m.request_id = req_id
690 AND m.set_number = set_num;
691 l_last_fetch BOOLEAN := FALSE;
692 l_count NUMBER;
693 BEGIN
694 IF process_mode='LOCK' THEN
695 NULL;
696 ELSE
697 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
698 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','FND_ATTACHED_DOCUMENTS',FALSE);
699 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
700 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
701
702 open merged_records;
703
704 LOOP
705 FETCH merged_records BULK COLLECT INTO
706 MERGE_HEADER_ID_LIST
707 , NUM_COL1_ORIG_LIST
708 ;
709 IF merged_records%NOTFOUND THEN
710 l_last_fetch := TRUE;
711 END IF;
712 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
713 exit;
714 END IF;
715 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
716 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
717 END LOOP;
718 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
719 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
720 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
721 MERGE_LOG_ID,
722 TABLE_NAME,
723 MERGE_HEADER_ID,
724 NUM_COL1_ORIG,
725 NUM_COL1_NEW,
726 ACTION_FLAG,
727 REQUEST_ID,
728 CREATED_BY,
729 CREATION_DATE,
730 LAST_UPDATE_LOGIN,
731 LAST_UPDATE_DATE,
732 LAST_UPDATED_BY
733 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
734 'FND_ATTACHED_DOCUMENTS',
735 MERGE_HEADER_ID_LIST(I),
736 NUM_COL1_ORIG_LIST(I),
737 NUM_COL1_NEW_LIST(I),
738 'U',
739 req_id,
740 hz_utility_pub.CREATED_BY,
741 hz_utility_pub.CREATION_DATE,
742 hz_utility_pub.LAST_UPDATE_LOGIN,
743 hz_utility_pub.LAST_UPDATE_DATE,
744 hz_utility_pub.LAST_UPDATED_BY
745 );
746 END IF;
747
748 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
749 UPDATE FND_ATTACHED_DOCUMENTS yt SET
750 PK1_VALUE=NUM_COL1_NEW_LIST(I)
751 , SEQ_NUM = seq_num+1
752 , LAST_UPDATE_DATE=SYSDATE
753 , last_updated_by=arp_standard.profile.user_id
754 , last_update_login=arp_standard.profile.last_update_login
755 , REQUEST_ID=req_id
756 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
757 , PROGRAM_ID=arp_standard.profile.program_id
758 , PROGRAM_UPDATE_DATE=SYSDATE
759 WHERE yt.entity_name = 'AR_CUSTOMERS'
760 AND yt.pk1_value = NUM_COL1_ORIG_LIST(I)
761 ;
762 l_count := l_count + SQL%ROWCOUNT;
763 IF l_last_fetch THEN
764 EXIT;
765 END IF;
766 END LOOP;
767
768 arp_message.set_name('AR','AR_ROWS_UPDATED');
769 arp_message.set_token('NUM_ROWS',to_char(l_count));
770 END IF;
771 EXCEPTION
772 WHEN OTHERS THEN
773 arp_message.set_line( 'MergeCustAttach');
774 RAISE;
775 END MergeCustAttach;
776
777 END fnd_attachment_util_pkg;