[Home] [Help]
PACKAGE BODY: APPS.GMA_EDITEXT_ATTACH_MIG
Source
1 PACKAGE BODY GMA_EDITEXT_ATTACH_MIG AS
2 /* $Header: GMAEATHB.pls 120.3 2006/11/03 21:28:20 txdaniel noship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) :='GMA_EDITEXT_ATTACH_MIG';
8 g_module_prefix CONSTANT VARCHAR2(40) := g_pkg_name || '.';
9
10 -- All top API's should call Attachment_Main Procedure
11 PROCEDURE Attachment_Main(
12 p_text_code IN VARCHAR2 default null,
13 p_text_table_tl IN VARCHAR2 default null,
14 p_sy_para_cds_table_name IN VARCHAR2 default null,
15 p_attach_form_short_name IN VARCHAR2 default null,
16 p_attach_table_name IN VARCHAR2 default null,
17 p_attach_pk1_value IN VARCHAR2 default null,
18 p_attach_pk2_value IN VARCHAR2 default null,
19 p_attach_pk3_value IN VARCHAR2 default null,
20 p_attach_pk4_value IN VARCHAR2 default null,
21 p_attach_pk5_value IN VARCHAR2 default null
22 )
23 IS
24
25 l_api_name CONSTANT VARCHAR2(30) := 'Attachment_Main' ;
26 l_api_version CONSTANT NUMBER := 1.0 ;
27 l_progress VARCHAR2(3) := '000';
28 l_long_message VARCHAR2(4000);
29
30 CURSOR Cur_Paragraph(x_table_name varchar2)
31 IS
32 SELECT
33 B.TABLE_NAME,
34 B.PARAGRAPH_CODE,
35 B.SUB_PARACODE,
36 B.PARA_DESC
37 FROM SY_PARA_CDS_VL B
38 WHERE B.TABLE_NAME=x_table_name;
39
40 CURSOR Cur_Paragraph_Lang(x_table_name varchar2,
41 x_paragraph_code VARCHAR2,
42 x_sub_paracode VARCHAR2)
43 IS
44 SELECT
45 T.LANG_CODE,
46 T.PARA_DESC,
47 T.SOURCE_LANG,
48 T.LANGUAGE
49 FROM SY_PARA_CDS_TL T, FND_LANGUAGES L
50 WHERE t.TABLE_NAME=x_table_name
51 AND t.paragraph_code = x_paragraph_code
52 AND t.sub_paracode = x_sub_paracode
53 AND t.language = l.language_code
54 ORDER BY installed_flag;
55
56 l_attachment_function_id fnd_attachment_functions.attachment_function_id%type;
57 l_attached_document_id fnd_documents.document_id%TYPE;
58 l_document_id fnd_documents.document_id%TYPE;
59 l_media_id NUMBER;
60 l_Cur_Paragraph Cur_Paragraph%rowtype;
61 l_Document_exist varchar2(10);
62 l_file_name VARCHAR2(240);
63 l_para_count PLS_INTEGER DEFAULT 0;
64 l_paragraph_exists BOOLEAN;
65 l_seq_num PLS_INTEGER DEFAULT 0;
66 Begin
67 -- First Check the if Attachment functionality is enabled/not and retreive funciton_id
68 GMA_EDITEXT_ATTACH_MIG.Check_Fnd_Attachment_Defined
69 (
70 p_text_code => p_text_code,
71 p_sy_para_cds_table_name => p_sy_para_cds_table_name,
72 p_form_short_name => p_attach_form_short_name,
73 p_table_name => p_attach_table_name ,
74 p_attachment_function_id => l_attachment_function_id
75 );
76
77 -- OPEN Cursor for All paragraphs for specific table eg: IC_ITEM_MST
78 FOR rec in Cur_Paragraph(p_sy_para_cds_table_name)
79 LOOP
80
81 /* Create the document row only if the paragraph is associated */
82 /* with the text code */
83 Check_Text_Paragraph_Match (p_text_code => p_text_code
84 ,p_text_tl_table => p_text_table_tl
85 ,p_paragraph_code => rec.paragraph_code
86 ,p_sub_paracode => rec.sub_paracode
87 ,x_paragraph_exists=> l_paragraph_exists
88 );
89
90 IF l_paragraph_exists THEN
91
92
93 l_para_count := l_para_count + 1;
94
95 -- Enable all languages if not in FND_LANGUAGES
96 GMA_EDITEXT_ATTACH_MIG.Fnd_Document_Set_Languages
97 (
98 p_text_code => p_text_code,
99 p_sy_para_cds_table_name => p_sy_para_cds_table_name,
100 p_text_tl_table => p_text_table_tl,
101 p_paragraph_code => rec.paragraph_code,
102 p_sub_paracode => rec.sub_paracode
103 );
104
105 l_document_id:=null;
106
107 -- Lets check if the document is already exist or not for all paragraphs with each Text code
108 -- if exists then do not create otherwise create additional if any one is missing
109 GMA_EDITEXT_ATTACH_MIG.Check_Fnd_Document_Exists
110 (
111 p_text_tl_table => p_text_table_tl,
112 p_sy_para_cds_table_name => p_sy_para_cds_table_name,
113 p_Text_code => p_text_code,
114 p_paragraph_code => rec.paragraph_code,
115 p_sub_paracode => rec.sub_paracode,
116 p_pk1_value => p_attach_pk1_value,
117 p_pk2_value => p_attach_pk2_value,
118 p_pk3_value => p_attach_pk3_value,
119 p_pk4_value => p_attach_pk4_value,
120 p_pk5_value => p_attach_pk5_value,
121 p_paragraph_count => l_para_count,
122 p_document_exist => l_document_exist,
123 p_file_name => l_file_name
124 );
125 -- Lets create the Attachment if does not exists
126 IF l_document_exist='FALSE' then
127 FOR lang_rec IN Cur_Paragraph_Lang(p_sy_para_cds_table_name,
128 rec.paragraph_code,
129 rec.sub_paracode)
130 LOOP
131 l_media_id := NULL;
132 l_document_id := NULL;
133 l_seq_num := l_seq_num + 10;
134
135 -- Lets create the Attachment for all paragraphs with each Text code
136 GMA_EDITEXT_ATTACH_MIG.Create_Fnd_Document
137 (
138 p_text_code => p_text_code,
139 p_sy_para_cds_table_name => p_sy_para_cds_table_name,
140 p_entity_name => p_attach_table_name,
141 p_pk1_value => p_attach_pk1_value,
142 p_pk2_value => p_attach_pk2_value,
143 p_pk3_value => p_attach_pk3_value,
144 p_pk4_value => p_attach_pk4_value,
145 p_pk5_value => p_attach_pk5_value,
146 p_file_name => l_file_name,
147 x_description => rec.para_desc||' - '||lang_rec.language,
148 x_attached_document_id => l_attached_document_id,
149 x_document_id => l_document_id,
150 x_media_id => l_media_id,
151 p_attachment_function_id => l_attachment_function_id,
152 p_sequence_num => l_seq_num
153 );
154
155 GMA_EDITEXT_ATTACH_MIG.Create_Fnd_Short_Text
156 (
157 p_text_tl_table => p_text_table_tl,
158 p_sy_para_cds_table_name => p_sy_para_cds_table_name,
159 p_Text_code => p_text_code,
160 p_paragraph_code => rec.paragraph_code,
161 p_sub_paracode => rec.sub_paracode,
162 p_language => lang_rec.language,
163 p_attached_document_id => l_attached_document_id,
164 p_document_id => l_document_id,
165 p_media_id => l_media_id,
166 p_pk1_value => p_attach_pk1_value,
167 p_pk2_value => p_attach_pk2_value,
168 p_pk3_value => p_attach_pk3_value,
169 p_pk4_value => p_attach_pk4_value,
170 p_pk5_value => p_attach_pk5_value,
171 p_paragraph_count => Cur_Paragraph%rowcount
172 );
173
174 END LOOP;
175
176 END IF;
177
178 END IF;
179
180 END LOOP;
181
182 If NOT(l_Paragraph_exists) AND (l_para_count = 0) Then
183
184 -- When no pragraph exists insert the following message
185 FND_MESSAGE.SET_NAME('GMA', 'GMA_NO_PARAGRAPH_EXIST');
186 FND_MESSAGE.SET_TOKEN('FORM_NAME',p_attach_form_short_name,FALSE);
187 FND_MESSAGE.SET_TOKEN('TABLE_NAME',p_sy_para_cds_table_name,FALSE);
188 FND_MSG_PUB.ADD;
189
190 End if;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
195 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
196 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
197 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
198 FND_MSG_PUB.ADD;
199
200 RAISE;
201
202 End Attachment_Main;
203
204 -- Procedure to check if Attachment functionality is defined or not
205 PROCEDURE Check_Fnd_Attachment_Defined(
206 p_text_code IN VARCHAR2 default null,
207 p_sy_para_cds_table_name in VARCHAR2 default null,
208 p_form_short_name IN VARCHAR2 default null,
209 p_table_name IN VARCHAR2 default null,
210 p_attachment_function_id OUT NOCOPY NUMBER
211 )
212 Is
213 l_attachment_function_id fnd_attachment_functions.attachment_function_id%type;
214 l_api_name CONSTANT VARCHAR2(30) := 'Check_Fnd_Attachment_Defined' ;
215 l_api_version CONSTANT NUMBER := 1.0 ;
216 l_progress VARCHAR2(3) := '000';
217 l_long_message VARCHAR2(4000);
218
219 Begin
220 -- Validating the attachment functionality for given form and table
221 SELECT attachment_function_id
222 into l_attachment_function_id
223 FROM fnd_attachment_functions
224 WHERE attachment_function_id
225 in(SELECT attachment_function_id
226 from fnd_attachment_blocks
227 WHERE attachment_blk_id
228 in(SELECT attachment_Blk_id
229 from fnd_attachment_blk_entities
230 WHERE data_object_code
231 in(SELECT data_object_code
232 from fnd_document_entities
233 WHERE UPPER(table_name) in(p_table_name)
234 )
235 )
236 )
237 AND upper(FUNCTION_NAME)=p_form_short_name
238 AND FUNCTION_TYPE='O';
239
240 --When returns something then pass back the attachment_function_id
241 p_attachment_function_id:=l_attachment_function_id;
242
243 EXCEPTION
244 WHEN NO_DATA_FOUND THEN
245 -- if no attachment functionality is assigned, then stop further text migration processing
246 p_attachment_function_id:=-1;
247
248 FND_MESSAGE.SET_NAME('GMA','GMA_ATTACHMENT_NOT_EXISTS');
249 FND_MESSAGE.SET_TOKEN('FORM_NAME',p_form_short_name,FALSE);
250 FND_MESSAGE.SET_TOKEN('ATTACHMENT_ID',p_attachment_function_id,FALSE);
251 FND_MSG_PUB.ADD;
252
253 Raise;
254
255 WHEN OTHERS THEN
256 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
257 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
258 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
259 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
260 FND_MSG_PUB.ADD;
261
262 RAISE;
263
264 End Check_Fnd_Attachment_Defined;
265
266 -- Procedure to check if Attachment documents already exists or not
267 PROCEDURE Check_Fnd_Document_Exists
268 (
269 p_text_tl_table IN VARCHAR2 default null,
270 p_sy_para_cds_table_name IN VARCHAR2 default null,
271 p_text_code IN VARCHAR2,
272 p_paragraph_code IN VARCHAR2,
273 p_sub_paracode IN NUMBER,
274 p_pk1_value IN VARCHAR2 Default null,
275 p_pk2_value IN VARCHAR2 Default null,
276 p_pk3_value IN VARCHAR2 Default null,
277 p_pk4_value IN VARCHAR2 Default null,
278 p_pk5_value IN VARCHAR2 Default null,
279 p_paragraph_count IN NUMBER Default null,
280 p_document_exist OUT NOCOPY VARCHAR2,
281 p_file_name OUT NOCOPY VARCHAR2
282 )
283 IS
284 l_api_name CONSTANT VARCHAR2(30) := 'Check_Fnd_Document_Exists' ;
285 l_api_version CONSTANT NUMBER := 1.0 ;
286 l_progress VARCHAR2(3) := '000';
287 l_long_message VARCHAR2(4000);
288
289 CURSOR cur_paragraph(x_table_name varchar2,
290 x_paragraph_code in varchar2,
291 x_sub_paracode in number)
292 is
293 SELECT
294 L.LANGUAGE_CODE,
295 B.PARAGRAPH_CODE,
296 B.SUB_PARACODE,
297 B.PARA_DESC,
298 B.language
299 FROM SY_PARA_CDS_TL B, FND_LANGUAGES L
300 WHERE L.INSTALLED_FLAG IN ('B')
301 AND B.PARAGRAPH_CODE=x_paragraph_code
302 AND B.SUB_PARACODE=x_sub_paracode
303 AND B.TABLE_NAME=x_table_name --'IC_ITEM_MST'
304 AND B.LANGUAGE=L.LANGUAGE_CODE;
305
306 CURSOR cur_fnd_documents(x_filename in varchar2)
307 is
308 SELECT
309 DOCUMENT_ID,
310 FILE_NAME,
311 MEDIA_ID
312 FROM FND_DOCUMENTS
313 WHERE FILE_NAME=x_filename;
314
315 l_flag varchar2(10):='FALSE';
316 l_filename varchar2(2000);
317
318 Begin
319
320 -- Lets validate the filename through Cursor Paragraph and sub_paracode
321 FOR crec IN Cur_Paragraph( p_sy_para_cds_table_name,
322 p_paragraph_code,
323 p_sub_paracode)
324 LOOP
325
326 -- Lets define the attachment filename as unique for each language into FND_DOCUMENTS_TL table
327 l_filename:=p_text_code||'.'||crec.language;
328
329
330 if nvl(p_pk1_value,'N')<>'N' then
331 l_filename:=l_filename||'.'||p_pk1_value;
332 end if;
333 if nvl(p_pk2_value,'N')<>'N' then
334 l_filename:=l_filename||'.'||p_pk2_value;
335 end if;
336 if nvl(p_pk3_value,'N')<>'N' then
337 l_filename:=l_filename||'.'||p_pk3_value;
338 end if;
339 if nvl(p_pk4_value,'N')<>'N' then
340 l_filename:=l_filename||'.'||p_pk4_value;
341 end if;
342 if nvl(p_pk5_value,'N')<>'N' then
343 l_filename:=l_filename||'.'||p_pk5_value;
344 end if;
345
346 l_filename:=l_filename||'.'||p_paragraph_count;
347 p_file_name := l_filename;
348 -- Lets validate the filename through Cursor
349 for rec IN Cur_fnd_documents(l_filename)
350 loop
351
352 -- Lets validate the filename is already present or not
353 l_flag:='TRUE';
354 l_long_message:= 'Paragraph_Code='||crec.paragraph_code||
355 ' Sub_Paracode='||crec.sub_paracode||
356 ' Para_Desc='||crec.para_desc||
357 ' Document_Id='||rec.DOCUMENT_ID||
358 ' File_Name='||rec.FILE_NAME||
359 ' Media_ID='||rec.MEDIA_ID||
360 ' Language='||crec.LANGUAGE||
361 ' ... Document Already Exist in FND_DOCUMENTS_TL (Cannot Create).';
362
363
364 fnd_file.put_line(fnd_file.log,l_long_message);
365 fnd_file.put_line(fnd_file.output,l_long_message);
366 exit;
367 end loop;
368
369 END LOOP;
370
371 -- if filename is already present for any language then assume that it is already creatd earlier
372 p_document_exist:=l_flag;
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
377 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
378 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
379 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
380 FND_MSG_PUB.ADD;
381 RAISE;
382
383 End Check_Fnd_Document_Exists;
384
385 -- Procedure to get the PK12345 column names for Attachment defined in APPS dev resp
386 -- This procedure is for external use and can be used to find the pk columns
387 PROCEDURE Get_Fnd_Attachment_Blk_PK(
388 p_text_code IN VARCHAR2 default null,
389 p_sy_para_cds_table_name in VARCHAR2 default null,
390 p_form_short_name IN VARCHAR2 default null,
391 p_table_name IN VARCHAR2 default null,
392 p_attachment_function_id IN NUMBER,
393 p_pk1_value OUT NOCOPY VARCHAR2,
394 p_pk2_value OUT NOCOPY VARCHAR2 ,
395 p_pk3_value OUT NOCOPY VARCHAR2 ,
396 p_pk4_value OUT NOCOPY VARCHAR2 ,
397 p_pk5_value OUT NOCOPY VARCHAR2
398 )
399 Is
400 l_pk1_field fnd_attachment_blk_entities.pk1_field%type;
401 l_pk2_field fnd_attachment_blk_entities.pk2_field%type;
402 l_pk3_field fnd_attachment_blk_entities.pk3_field%type;
403 l_pk4_field fnd_attachment_blk_entities.pk4_field%type;
404 l_pk5_field fnd_attachment_blk_entities.pk5_field%type;
405
406 l_api_name CONSTANT VARCHAR2(30) := 'Get_Fnd_Attachment_Blk_PK' ;
407 l_api_version CONSTANT NUMBER := 1.0 ;
408 l_progress VARCHAR2(3) := '000';
409 l_long_message VARCHAR2(4000);
410 Begin
411
412 -- Validate and get the PK12345 for the attachment block
413 select PK1_FIELD,
414 PK2_FIELD,
415 PK3_FIELD,
416 PK4_FIELD,
417 PK5_FIELD
418 into p_pk1_value,
419 p_pk2_value,
420 p_pk3_value,
421 p_pk4_value,
422 p_pk5_value
423 from fnd_attachment_blk_entities
424 where data_object_code=p_table_name
425 and attachment_blk_id=
426 (SELECT attachment_blk_id
427 FROM fnd_attachment_blocks
428 WHERE attachment_function_id=p_attachment_function_id
429 AND block_name=p_table_name);
430
431 EXCEPTION
432 WHEN NO_DATA_FOUND THEN
433
434 -- if no PK columns defined for attachment functionality then ignore
435 FND_MESSAGE.SET_NAME('GMA','GMA_ATTACH_COL_NOT_EXISTS');
436 FND_MESSAGE.SET_TOKEN('FORM_NAME',p_form_short_name,FALSE);
437 FND_MESSAGE.SET_TOKEN('pk1_value',p_pk1_value,FALSE);
438 FND_MESSAGE.SET_TOKEN('pk2_value',p_pk2_value,FALSE);
439 FND_MESSAGE.SET_TOKEN('pk3_value',p_pk3_value,FALSE);
440 FND_MESSAGE.SET_TOKEN('pk4_value',p_pk4_value,FALSE);
441 FND_MESSAGE.SET_TOKEN('pk5_value',p_pk5_value,FALSE);
442 FND_MSG_PUB.ADD;
443
444
445 WHEN OTHERS THEN
446 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
447 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
448 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
449 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
450 FND_MSG_PUB.ADD;
451
452 RAISE;
453
454
455 End Get_Fnd_Attachment_Blk_PK;
456
457 -- Procedure to Set the languages in FND_LANGUAGE if not installed
458 PROCEDURE Fnd_Document_set_languages
459 (
460 p_text_code in NUMBER Default null,
461 p_sy_para_cds_table_name in VARCHAR2 default null,
462 p_text_tl_table in VARCHAR2 Default null,
463 p_paragraph_code in VARCHAR2 Default null,
464 p_sub_paracode in Number Default null
465 )
466 IS
467 l_api_name CONSTANT VARCHAR2(30) := 'Fnd_Document_set_languages' ;
468 l_api_version CONSTANT NUMBER := 1.0 ;
469 l_progress VARCHAR2(3) := '000';
470 l_long_message VARCHAR2(4000);
471
472 l_sql_stmt VARCHAR2(3200);
473 l_sql_stmt1 VARCHAR2(3200);
474 l_cursor INTEGER := NULL;
475 l_rows_processed INTEGER := NULL;
476
477 l_language varchar2(6);
478 l_paragraph_code varchar2(4);
479 l_sub_paracode number(5);
480 l_lang_code varchar2(4);
481 l_total_rows number(5);
482 Begin
483
484
485
486 -- Lets define dynamic SQL stmt for languages
487 l_sql_stmt := 'SELECT DISTINCT language,
488 paragraph_code,
489 sub_paracode,
490 lang_code
491 FROM '||P_text_tl_table||'
492 WHERE text_code=:P_text_code
493 AND paragraph_code=:P_paragraph_code
494 AND sub_paracode=:P_sub_paracode
495 ORDER BY LANGUAGE';
496
497 -- Lets define dynamic SQL stmt for languages
498 l_sql_stmt1 := 'SELECT filename,
499 FROM FND_DOCUMENT_TL
500 WHERE filename=:P_filename
501 AND language=:P_language';
502
503 l_cursor := DBMS_SQL.OPEN_CURSOR;
504
505 DBMS_SQL.PARSE( l_cursor, l_sql_stmt , DBMS_SQL.NATIVE );
506
507 DBMS_SQL.BIND_VARIABLE(l_cursor,'P_text_code' ,P_text_code);
508 DBMS_SQL.BIND_VARIABLE(l_cursor,'P_paragraph_code' ,P_paragraph_code );
509 DBMS_SQL.BIND_VARIABLE(l_cursor,'P_sub_paracode' ,P_sub_paracode );
510
511 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_language,6);
512 DBMS_SQL.DEFINE_COLUMN(l_cursor, 2, l_paragraph_code,4);
513 DBMS_SQL.DEFINE_COLUMN(l_cursor, 3, l_sub_paracode);
514 DBMS_SQL.DEFINE_COLUMN(l_cursor, 4, l_lang_code,4);
515
516 l_rows_processed := DBMS_SQL.EXECUTE(l_cursor);
517
518 loop
519
520 IF ( DBMS_SQL.FETCH_ROWS(l_cursor) > 0 ) THEN
521
522 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_language);
523 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_paragraph_code);
524 DBMS_SQL.COLUMN_VALUE(l_cursor, 3, l_sub_paracode);
525 DBMS_SQL.COLUMN_VALUE(l_cursor, 4, l_lang_code);
526
527 UPDATE FND_LANGUAGES
528 set installed_flag='I'
529 WHERE
530 language_code=l_language
531 AND installed_flag not in('I','B');
532
533
534 ELSE
535 l_language := NULL;
536 l_paragraph_code := NULL;
537 l_sub_paracode := NULL;
538 l_lang_code := NULL;
539 exit;
540 END IF;
541
542 end loop;
543
544 DBMS_SQL.CLOSE_CURSOR(l_cursor);
545
546 Exception
547 WHEN OTHERS THEN
548
549 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
550 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
551 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
552 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
553 FND_MSG_PUB.ADD;
554
555
556 IF DBMS_SQL.IS_OPEN(l_cursor) THEN
557 DBMS_SQL.CLOSE_CURSOR(l_cursor);
558 END IF;
559
560 RAISE;
561
562 End Fnd_Document_Set_Languages;
563
564 -- Procedure to define the common category for all attachment accross OPM
565 -- If category exists it uses otherwise it creates
566 PROCEDURE Get_Fnd_Attachment_Category
567 (
568 p_text_code IN VARCHAR2 default null,
569 p_sy_para_cds_table_name in VARCHAR2 default null,
570 p_category_name IN VARCHAR2 default 'OPM_MIGRATED_TEXT',
571 p_user_name IN VARCHAR2 default 'GMA Migration Text',
572 p_category_exists OUT NOCOPY NUMBER,
573 p_category_id OUT NOCOPY NUMBER
574 )
575 IS
576
577 l_api_name CONSTANT VARCHAR2(30) := 'Get_Fnd_Attachment_Category' ;
578 l_api_version CONSTANT NUMBER := 1.0 ;
579 l_progress VARCHAR2(3) := '000';
580 l_long_message VARCHAR2(4000);
581
582 TYPE VARCHAR_TBL_TYPE IS TABLE OF VARCHAR2(30)
583 INDEX BY BINARY_INTEGER;
584
585 l_Rowid VARCHAR2(200);
586 l_sysdate DATE := SYSDATE;
587 l_application_id NUMBER :=550; -- GMA
588 l_attribute1_value VARCHAR2(2000);
589 l_attribute2_value VARCHAR2(2000);
590 l_attribute3_value VARCHAR2(2000);
591 l_attribute4_value VARCHAR2(2000);
592 l_attribute5_value VARCHAR2(2000);
593 l_attribute6_value VARCHAR2(2000);
594 l_attribute7_value VARCHAR2(2000);
595 l_attribute8_value VARCHAR2(2000);
596 l_attribute9_value VARCHAR2(2000);
597 l_attribute10_value VARCHAR2(2000);
598 l_attribute11_value VARCHAR2(2000);
599 l_attribute12_value VARCHAR2(2000);
600 l_attribute13_value VARCHAR2(2000);
601 l_attribute14_value VARCHAR2(2000);
602 l_attribute15_value VARCHAR2(2000);
603 l_name_tbl VARCHAR_TBL_TYPE;
604 l_value_tbl VARCHAR_TBL_TYPE;
605 l_user_name fnd_document_categories_tl.user_name%TYPE :=p_user_name;
606 l_dummy VARCHAR2(1);
607 l_category_id NUMBER := 0;
608
609 l_user_id number := 0 ;
610
611 Begin
612
613 -- Get category Id from fnd_document_categories.
614 SELECT category_id,
615 application_id
616 INTO l_category_id,
617 l_application_id
618 FROM fnd_document_categories_vl
619 WHERE user_name = p_user_name;
620
621 p_category_exists:=1;
622 p_category_id:=l_category_id;
623
624 -- Insert message in FND_LOG_MESSAGE through external GMA common Logging
625
626
627 -- fnd_file.put_line(fnd_file.log,l_long_message);
628 -- fnd_file.put_line(fnd_file.output,l_long_message);
629
630 EXCEPTION
631 WHEN NO_DATA_FOUND THEN
632
633 -- Get category id from a sequence.
634 SELECT fnd_document_categories_s.nextval
635 INTO l_category_id
636 FROM dual;
637
638 -- Call fnd's package to create the Category
639 fnd_doc_categories_pkg.insert_row
640 (
641 X_ROWID => l_Rowid,
642 X_CATEGORY_ID => l_category_id,
643 X_APPLICATION_ID => l_application_id,
644 X_NAME => p_category_name,
645 X_START_DATE_ACTIVE => null,
646 X_END_DATE_ACTIVE => null,
647 X_ATTRIBUTE_CATEGORY => null,
648 X_ATTRIBUTE1 => l_attribute1_value,
649 X_ATTRIBUTE2 => l_attribute2_value,
650 X_ATTRIBUTE3 => l_attribute3_value,
651 X_ATTRIBUTE4 => l_attribute4_value,
652 X_ATTRIBUTE5 => l_attribute5_value,
653 X_ATTRIBUTE6 => l_attribute6_value,
654 X_ATTRIBUTE7 => l_attribute7_value,
655 X_ATTRIBUTE8 => l_attribute8_value,
656 X_ATTRIBUTE9 => l_attribute9_value,
657 X_ATTRIBUTE10 => l_attribute10_value,
658 X_ATTRIBUTE11 => l_attribute11_value,
659 X_ATTRIBUTE12 => l_attribute12_value,
660 X_ATTRIBUTE13 => l_attribute13_value,
661 X_ATTRIBUTE14 => l_attribute14_value,
662 X_ATTRIBUTE15 => l_attribute15_value,
663 X_DEFAULT_DATATYPE_ID => 1, -- Short Text
664 X_USER_NAME => l_user_name,
665 X_CREATION_DATE => l_sysdate,
666 X_CREATED_BY => FND_GLOBAL.login_id,
667 X_LAST_UPDATE_DATE => l_sysdate,
668 X_LAST_UPDATED_BY => FND_GLOBAL.login_id,
669 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id);
670
671 p_category_exists:=-1;
672 p_category_id:=l_category_id;
673
674 FND_MESSAGE.SET_NAME('GMA','GMA_CATEGORY_CREATED');
675 FND_MESSAGE.SET_TOKEN('category_name',p_category_name,FALSE);
676 FND_MESSAGE.SET_TOKEN('category_id',l_category_id,FALSE);
677 FND_MSG_PUB.ADD;
678 WHEN OTHERS THEN
679 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
680 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
681 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
682 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
683 FND_MSG_PUB.ADD;
684
685 RAISE;
686
687 End Get_Fnd_Attachment_Category;
688
689 -- Procedure to find/create the Category usuage which is needed for every category
690 PROCEDURE Get_Fnd_Category_Usage(
691 p_text_code IN VARCHAR2 default null,
692 p_sy_para_cds_table_name in VARCHAR2 default null,
693 p_category_id IN NUMBER,
694 p_attachment_function_id IN NUMBER,
695 p_category_usage_exists OUT NOCOPY NUMBER
696 )
697 IS
698 l_api_name CONSTANT VARCHAR2(30) := 'Get_Fnd_Category_Usage' ;
699 l_api_version CONSTANT NUMBER := 1.0 ;
700 l_progress VARCHAR2(3) := '000';
701 l_long_message VARCHAR2(4000);
702
703 l_sysdate DATE := SYSDATE;
704 l_doc_category_usage_id NUMBER := 0;
705 l_user_id number := 0 ;
706
707 Begin
708
709 -- Get category Id from fnd_document_categories.
710 select doc_category_usage_id
711 INTO l_doc_category_usage_id
712 from fnd_doc_category_usages
713 WHERE category_id = p_category_id
714 and attachment_function_id=p_attachment_function_id;
715
716 p_category_usage_exists:=1;
717
718 EXCEPTION
719 WHEN NO_DATA_FOUND THEN
720
721 -- Get category id from a sequence.
722 select fnd_doc_category_usages_s.nextval
723 into l_doc_category_usage_id
724 from dual;
725
726 INSERT INTO FND_DOC_CATEGORY_USAGES
727 (DOC_CATEGORY_USAGE_ID,
728 CATEGORY_ID,
729 ATTACHMENT_FUNCTION_ID,
730 ENABLED_FLAG,
731 CREATION_DATE,
732 CREATED_BY,
733 LAST_UPDATE_DATE,
734 LAST_UPDATED_BY,
735 LAST_UPDATE_LOGIN)
736 VALUES (l_doc_category_usage_id,
737 p_category_id,
738 p_attachment_function_id,
739 'Y',
740 l_sysdate,
741 l_user_id,
742 l_sysdate,
743 l_user_id,
744 l_user_id);
745
746 p_category_usage_exists:=-1;
747 FND_MESSAGE.SET_NAME('GMA','GMA_CATEGORY_USAGE_ID_CREATED');
748 FND_MESSAGE.SET_TOKEN('category_usage_id',l_doc_category_usage_id,FALSE);
749 FND_MESSAGE.SET_TOKEN('category_id',p_category_id,FALSE);
750 FND_MSG_PUB.ADD;
751
752 WHEN OTHERS THEN
753 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
754 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
755 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
756 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
757 FND_MSG_PUB.ADD;
758
759 RAISE;
760
761 End Get_Fnd_Category_Usage;
762
763 -- Procedure to create the attachment document for all languages
764 PROCEDURE Create_Fnd_Document(
765 p_text_code IN VARCHAR2 default null,
766 p_sy_para_cds_table_name in VARCHAR2 default null,
767 p_entity_name in VARCHAR2 Default 'GMA Migration',
768 p_pk1_value in VARCHAR2 Default null,
769 p_pk2_value in VARCHAR2 Default null,
770 p_pk3_value in VARCHAR2 Default null,
771 p_pk4_value in VARCHAR2 Default null,
772 p_pk5_value in VARCHAR2 Default null,
773 x_description in VARCHAR2 Default null,
774 p_file_name IN VARCHAR2 DEFAULT NULL,
775 x_attached_document_id IN OUT NOCOPY NUMBER,
776 x_document_id IN OUT NOCOPY NUMBER,
777 x_media_id IN OUT NOCOPY NUMBER,
778 p_attachment_function_id in NUMBER,
779 p_sequence_num IN NUMBER
780 )
781 IS
782 l_api_name CONSTANT VARCHAR2(30) := 'Create_Fnd_Document' ;
783 l_api_version CONSTANT NUMBER := 1.0 ;
784 l_progress VARCHAR2(3) := '000';
785 l_long_message VARCHAR2(4000);
786
787
788 l_new_attachment_id NUMBER;
789 l_row_id VARCHAR2(30);
790 l_current_date DATE := sysdate;
791 l_attachment_function_id fnd_attachment_functions.attachment_function_id%type;
792 l_attached_document_id fnd_documents.document_id%TYPE;
793 l_create_Attached_Doc boolean := true;
794 l_dummy fnd_documents.document_id%TYPE;
795
796 CURSOR c_attached_doc_id IS
797 SELECT FND_ATTACHED_DOCUMENTS_S.nextval
798 FROM dual;
799
800 CURSOR c_attached_doc_id_exists (l_id IN NUMBER) IS
801 SELECT 1
802 FROM FND_ATTACHED_DOCUMENTS
803 WHERE document_id = l_id;
804
805 l_category_name fnd_document_categories_tl.name%TYPE:='OPM_MIGRATED_TEXT';
806 l_user_name fnd_document_categories_tl.user_name%TYPE:='GMA Migration Text';
807 l_category_exists fnd_document_categories_tl.category_id%TYPE;
808 l_category_id fnd_document_categories_tl.category_id%TYPE;
809 l_category_usage_exists fnd_document_categories_tl.category_id%TYPE;
810
811 Begin
812
813
814 -- Get the GMA migration attachment category
815 Get_Fnd_Attachment_Category(p_text_code,
816 p_sy_para_cds_table_name,
817 l_category_name,
818 l_user_name,
819 l_category_exists,
820 l_category_id
821 );
822
823 -- Get the GMA migration attachment category usage for the category
824 Get_Fnd_Category_Usage(p_text_code,
825 p_sy_para_cds_table_name,
826 l_category_id,
827 p_attachment_function_id,
828 l_category_usage_exists
829 );
830
831 -- Validating the attachment functionality for given form and table
832 -- IF p_Fnd_Attachment_rec.attached_DOCUMENT_ID IS NULL THEN
833 LOOP
834 l_dummy := NULL;
835 OPEN c_attached_doc_id;
836 FETCH c_attached_doc_id INTO l_attached_document_ID;
837 CLOSE c_attached_doc_id;
838
839 OPEN c_attached_doc_id_exists(l_attached_document_ID);
840 FETCH c_attached_doc_id_exists INTO l_dummy;
841 CLOSE c_attached_doc_id_exists;
842 EXIT WHEN l_dummy IS NULL;
843 END LOOP;
844
845 x_attached_document_id := l_attached_document_id;
846
847 /* Populate FND Attachments */
848 fnd_attached_documents_pkg.Insert_Row
849 ( x_rowid => l_row_id,
850 X_attached_document_id => l_attached_document_ID,
851 X_document_id => x_document_ID,
852 X_creation_date => sysdate,
853 X_created_by => FND_GLOBAL.USER_ID,
854 X_last_update_date => sysdate,
855 X_last_updated_by => FND_GLOBAL.USER_ID,
856 X_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
857 X_seq_num => p_sequence_num,
858 X_entity_name => p_entity_name, --'GMA Migration',
859 x_column1 => null,
860 X_pk1_value => p_pk1_value,
861 X_pk2_value => p_pk2_value,
862 X_pk3_value => p_pk3_value,
863 X_pk4_value => p_pk4_value,
864 X_pk5_value => p_pk5_value,
865 X_automatically_added_flag => 'N',
866 X_datatype_id => 1,
867 X_category_id => l_category_id,
868 X_security_type => 1,
869 X_publish_flag => 'Y',
870 X_usage_type => 'O', -- May be changed as checked for attachment
871 X_language => null,
872 X_description => x_description, --'General Text',
873 X_file_name => p_file_name,
874 X_media_id => x_media_id,
875 X_doc_attribute_Category => null,
876 X_doc_attribute1 => null,
877 X_doc_attribute2 => null,
878 X_doc_attribute3 => null,
879 X_doc_attribute4 => null,
880 X_doc_attribute5 => null,
881 X_doc_attribute6 => null,
882 X_doc_attribute7 => null,
883 X_doc_attribute8 => null,
884 X_doc_attribute9 => null,
885 X_doc_attribute10 => null,
886 X_doc_attribute11 => null,
887 X_doc_attribute12 => null,
888 X_doc_attribute13 => null,
889 X_doc_attribute14 => null,
890 X_doc_attribute15 => null
891 );
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
896 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
897 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
898 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
899 FND_MSG_PUB.ADD;
900
901 RAISE;
902
903 End Create_Fnd_Document;
904
905 -- Procedure which creates the actual text for attachments as unique to each language
906 PROCEDURE Create_Fnd_Short_Text
907 (
908 p_text_tl_table IN VARCHAR2 default null,
909 p_sy_para_cds_table_name IN VARCHAR2 default null,
910 p_text_code IN VARCHAR2,
911 p_paragraph_code IN VARCHAR2,
912 p_sub_paracode IN NUMBER,
913 p_language IN VARCHAR2,
914 p_attached_document_id IN OUT NOCOPY NUMBER,
915 p_document_id IN OUT NOCOPY NUMBER,
916 p_media_id IN OUT NOCOPY NUMBER,
917 p_pk1_value in VARCHAR2 Default null,
918 p_pk2_value in VARCHAR2 Default null,
919 p_pk3_value in VARCHAR2 Default null,
920 p_pk4_value in VARCHAR2 Default null,
921 p_pk5_value in VARCHAR2 Default null,
922 p_paragraph_count in NUMBER Default null
923 )
924 IS
925
926 l_api_name CONSTANT VARCHAR2(30) := 'Create_Fnd_Short_Text' ;
927 l_api_version CONSTANT NUMBER := 1.0 ;
928 l_progress VARCHAR2(3) := '000';
929 l_long_message VARCHAR2(4000);
930
931 l_row_id VARCHAR2(30);
932 l_current_date DATE := sysdate;
933 l_attached_document_id fnd_documents.document_id%TYPE;
934 l_document_ID fnd_documents.document_id%TYPE;
935
936 l_file_id number;
937 l_long_text long;
938 l_text ic_text_tbl_tl.text%type;
939 l_flag varchar2(6):='FALSE';
940
941 l_text_tl VARCHAR2(70);
942 l_sql_stmt VARCHAR2(3200);
943 l_cursor INTEGER := NULL;
944 l_rows_processed INTEGER := NULL;
945
946 Begin
947 -- Get the GMA migratio attachment category
948 l_file_id:=p_media_id;
949
950 l_long_text:=null;
951
952 l_flag:='FALSE';
953
954 l_cursor := DBMS_SQL.OPEN_CURSOR;
955
956 -- Lets define dynamic sql for attachment text
957 l_sql_stmt := 'SELECT text '||
958 ' FROM ' || p_text_tl_table ||
959 ' WHERE text_code= :x_Text_Code '||
960 ' AND paragraph_code=:x_paragraph_code'||
961 ' AND sub_paracode= :x_sub_paracode '||
962 ' AND language= :x_language '||
963 ' AND line_no>-1' ||
964 ' ORDER BY line_no';
965
966 DBMS_SQL.PARSE( l_cursor, l_sql_stmt , DBMS_SQL.NATIVE );
967
968 -- Lets define dynamic sql bind variables
969 DBMS_SQL.BIND_VARIABLE(l_cursor,'x_Text_Code' ,P_text_code);
970 DBMS_SQL.BIND_VARIABLE(l_cursor,'x_paragraph_code' ,P_paragraph_code );
971 DBMS_SQL.BIND_VARIABLE(l_cursor,'x_sub_paracode', P_sub_paracode );
972 DBMS_SQL.BIND_VARIABLE(l_cursor,'x_language',P_language );
973
974 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_text_tl,70);
975
976 l_rows_processed := DBMS_SQL.EXECUTE(l_cursor);
977
978 loop
979
980 IF ( DBMS_SQL.FETCH_ROWS(l_cursor) > 0 ) THEN
981 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_text_tl);
982 l_long_text:=l_long_text||l_text_tl;
983 l_flag:='TRUE';
984 ELSE
985 exit;
986 END IF;
987
988 end loop;
989
990 DBMS_SQL.CLOSE_CURSOR(l_cursor);
991
992 if l_flag='TRUE' then
993 -- if true then insert the attachment into fnd_documents_long_text table
994 INSERT INTO
995 fnd_documents_short_text
996 (
997 MEDIA_ID,
998 APP_SOURCE_VERSION,
999 SHORT_TEXT
1000 )
1001 VALUES
1002 (
1003 l_file_id,
1004 NULL,
1005 l_long_text
1006 );
1007
1008 end if;
1009
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
1013 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
1014 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
1015 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
1016 FND_MSG_PUB.ADD;
1017 RAISE;
1018 End Create_Fnd_Short_Text;
1019
1020
1021 -- Procedure to check if text has been defined for the give paragraph
1022 -- code for the given text code.
1023 PROCEDURE Check_Text_Paragraph_Match
1024 (
1025 p_text_code IN NUMBER,
1026 p_text_tl_table IN VARCHAR2,
1027 p_paragraph_code IN VARCHAR2,
1028 p_sub_paracode IN NUMBER,
1029 x_paragraph_exists OUT NOCOPY BOOLEAN
1030 )
1031 IS
1032 l_api_name CONSTANT VARCHAR2(30) := 'Check_Text_Paragraph_Match' ;
1033
1034 l_sql_stmt VARCHAR2(3200);
1035
1036 -- REF cursor definition
1037 TYPE REF_CUR is REF CURSOR;
1038 l_ref_cur REF_CUR;
1039
1040 l_exists PLS_INTEGER;
1041 Begin
1042
1043 -- Lets initialize the paragraph exists flag to FALSE
1044 x_paragraph_exists := FALSE;
1045
1046 -- Lets define dynamic SQL stmt for checking the paragraph
1047 l_sql_stmt := ' SELECT 1 '||
1048 ' FROM '||P_text_tl_table||
1049 ' WHERE text_code=:1 '||
1050 ' AND paragraph_code=:2 '||
1051 ' AND sub_paracode=:3';
1052
1053 OPEN l_ref_cur FOR l_sql_stmt using P_text_code, P_paragraph_code, P_sub_paracode;
1054 FETCH l_ref_cur INTO l_exists;
1055 IF l_ref_cur%FOUND THEN
1056 x_paragraph_exists := TRUE;
1057 END IF;
1058 CLOSE l_ref_cur;
1059
1060 Exception
1061 WHEN OTHERS THEN
1062
1063 FND_MESSAGE.SET_NAME('FND', 'FND_AS_UNEXPECTED_ERROR');
1064 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
1065 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GMA_EDITEXT_ATTACH_MIG');
1066 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', l_api_name);
1067 FND_MSG_PUB.ADD;
1068
1069 RAISE;
1070
1071 End Check_Text_Paragraph_Match;
1072
1073 END GMA_EDITEXT_ATTACH_MIG;