[Home] [Help]
PACKAGE BODY: APPS.OKC_MRV_UTIL
Source
1 PACKAGE BODY okc_mrv_util
2 /*$Header: OKCMRVUB.pls 120.1 2011/12/09 14:09:28 serukull noship $*/
3 AS
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7
8 ---------------------------------------------------------------------------
9 -- GLOBAL MESSAGE CONSTANTS
10 ---------------------------------------------------------------------------
11 g_fnd_app CONSTANT VARCHAR2 (200) := okc_api.g_fnd_app;
12 ---------------------------------------------------------------------------
13 -- GLOBAL VARIABLES
14 ---------------------------------------------------------------------------
15 g_pkg_name CONSTANT VARCHAR2 (200)
16 := 'OKC_K_ENTITY_LOCKS_GRP';
17 g_app_name CONSTANT VARCHAR2 (3) := okc_api.g_app_name;
18 ------------------------------------------------------------------------------
19 -- GLOBAL CONSTANTS
20 ------------------------------------------------------------------------------
21 g_false CONSTANT VARCHAR2 (1) := fnd_api.g_false;
22 g_true CONSTANT VARCHAR2 (1) := fnd_api.g_true;
23 g_ret_sts_success CONSTANT VARCHAR2 (1) := fnd_api.g_ret_sts_success;
24 g_ret_sts_error CONSTANT VARCHAR2 (1) := fnd_api.g_ret_sts_error;
25 g_ret_sts_unexp_error CONSTANT VARCHAR2 (1)
26 := fnd_api.g_ret_sts_unexp_error;
27 g_unexpected_error CONSTANT VARCHAR2 (200) := 'OKC_UNEXPECTED_ERROR';
28 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'ERROR_MESSAGE';
29 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'ERROR_CODE';
30 g_amend_code_deleted CONSTANT VARCHAR2 (30) := 'DELETED';
31 g_amend_code_added CONSTANT VARCHAR2 (30) := 'ADDED';
32 g_amend_code_updated CONSTANT VARCHAR2 (30) := 'UPDATED';
33 g_stmt_level NUMBER := fnd_log.level_statement;
34
35 /*==============================================================
36 PRIVATE PROCEDURES
37 =============================================================*/
38 FUNCTION get_uda_attr_desc_sql (
39 p_pk1_value NUMBER,
40 p_pk2_value NUMBER,
41 p_data_type VARCHAR2,
42 p_appl_col_name VARCHAR2,
43 p_end_user_col_name VARCHAR2,
44 p_attr_group VARCHAR2,
45 p_attr_id NUMBER
46 )
47 RETURN VARCHAR2
48 IS
49 -- p_application_id number := 510;
50 p_application_id NUMBER;
51 p_attr_group_type VARCHAR2 (200) := 'OKC_K_ART_VAR_EXT_ATTRS';
52 p_object_name VARCHAR2 (200) := 'OKC_K_ART_VAR_EXT_B';
53 p_pk1_column_name VARCHAR2 (200) := 'CAT_ID';
54 p_pk2_column_name VARCHAR2 (200) := 'VARIABLE_CODE';
55 l_sql VARCHAR2 (1000);
56 BEGIN
57 -- Get application ID from fnd_application
58 SELECT application_id
59 INTO p_application_id
60 FROM fnd_application
61 WHERE application_short_name = 'OKC';
62
63 IF (p_data_type = 'C' OR p_data_type = 'A')
64 THEN
65 l_sql :=
66 ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( '
67 || p_application_id
68 || ','
69 || ' null, '
70 || p_appl_col_name
71 || ', null , '''
72 || p_end_user_col_name
73 || ''','''
74 || p_attr_group_type
75 || ''','''
76 || p_attr_group
77 || ''','
78 || p_attr_id
79 || ','''
80 || p_object_name
81 || ''','''
82 || p_pk1_column_name
83 || ''','
84 || p_pk1_value
85 || ','''
86 || p_pk2_column_name
87 || ''','
88 || p_pk2_value
89 || ') as '
90 || p_end_user_col_name
91 || '_DESC';
92 ELSIF p_data_type = 'N'
93 THEN
94 l_sql :=
95 ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( '
96 || p_application_id
97 || ','
98 || ' null, null, '
99 || p_appl_col_name
100 || ', '''
101 || p_end_user_col_name
102 || ''','''
103 || p_attr_group_type
104 || ''','''
105 || p_attr_group
106 || ''','
107 || p_attr_id
108 || ','''
109 || p_object_name
110 || ''','''
111 || p_pk1_column_name
112 || ''','
113 || p_pk1_value
114 || ','''
115 || p_pk2_column_name
116 || ''','
117 || p_pk2_value
118 || ') as '
119 || p_end_user_col_name
120 || '_DESC';
121 ELSIF (p_data_type = 'X' OR p_data_type = 'Y')
122 THEN
123 l_sql :=
124 ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( '
125 || p_application_id
126 || ','
127 || p_appl_col_name
128 || ' , null, null, '''
129 || p_end_user_col_name
130 || ''','''
131 || p_attr_group_type
132 || ''','''
133 || p_attr_group
134 || ''','
135 || p_attr_id
136 || ','''
137 || p_object_name
138 || ''','''
139 || p_pk1_column_name
140 || ''','
141 || p_pk1_value
142 || ','''
143 || p_pk2_column_name
144 || ''','
145 || p_pk2_value
146 || ') as '
147 || p_end_user_col_name
148 || '_DESC ';
149 END IF;
150
151 RETURN (l_sql);
152 END get_uda_attr_desc_sql;
153
154 /*==============================================================
155 PUBLIC PROCEDURES
156 =============================================================*/
157
158 PROCEDURE update_k_art_var (
159 p_cat_id IN NUMBER,
160 p_variable_code IN VARCHAR2,
161 p_blobdata IN BLOB,
162 p_type IN VARCHAR2
163 )
164 IS
165 l_clob CLOB;
166 BEGIN
167 l_clob := okc_word_download_upload.blob_to_clob (p_blobdata);
168
169 IF p_type = 'XML'
170 THEN
171 --
172 UPDATE okc_k_art_variables
173 SET mr_variable_xml = l_clob
174 WHERE variable_code = p_variable_code AND cat_id = p_cat_id;
175 ELSIF p_type = 'HTML'
176 THEN
177 UPDATE okc_k_art_variables
178 SET mr_variable_html = l_clob
179 WHERE variable_code = p_variable_code AND cat_id = p_cat_id;
180 END IF;
181 EXCEPTION
182 WHEN OTHERS THEN
183 RAISE;
184 END update_k_art_var;
185
186 FUNCTION get_k_art_var (
187 p_cat_id IN NUMBER,
188 p_variable_code IN VARCHAR2,
189 p_type IN VARCHAR2
190 )
191 RETURN BLOB
192 IS
193 l_clob CLOB;
194 BEGIN
195 IF p_type = 'HTML'
196 THEN
197 SELECT mr_variable_html
198 INTO l_clob
199 FROM okc_k_art_variables
200 WHERE variable_code = p_variable_code AND cat_id = p_cat_id;
201
202 RETURN (okc_word_download_upload.clob_to_blob (l_clob));
203 ELSIF p_type = 'XML'
204 THEN
205 SELECT mr_variable_xml
206 INTO l_clob
207 FROM okc_k_art_variables
208 WHERE variable_code = p_variable_code AND cat_id = p_cat_id;
209
210 RETURN (okc_word_download_upload.clob_to_blob (l_clob));
211 END IF;
212 EXCEPTION
213 WHEN OTHERS THEN
214 RAISE;
215 END get_k_art_var;
216
217
218 FUNCTION get_uda_attr_xml(p_cat_id IN NUMBER,
219 p_VARIABLE_CODE IN VARCHAR2,
220 p_Attr_group_id IN NUMBER
221 ) RETURN CLOB
222 IS
223 CURSOR C_ATTR_GRP IS
224 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
225 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
226 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
227 AG.MULTI_ROW MULTI_ROW
228 FROM EGO_FND_DSC_FLX_CTX_EXT AG
229 WHERE 1=1
230 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = 'OKC_K_ART_VAR_EXT_ATTRS'
231 AND AG.ATTR_GROUP_ID = p_Attr_group_id
232 ;
233
234 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
235 SELECT EFDFCE.ATTR_ID,
236 EFDFCE.APPLICATION_COLUMN_NAME,
237 FCU.END_USER_COLUMN_NAME,
238 fcu.flex_value_set_id,
239 EFDFCE.data_type
240 FROM
241 EGO_FND_DF_COL_USGS_EXT EFDFCE,
242 FND_DESCR_FLEX_COLUMN_USAGES FCU
243 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
244 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
245 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
246 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
247 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
248 AND FCU.DISPLAY_FLAG <> 'H';
249
250 L_SQL VARCHAR2(32767);
251 L_OP VARCHAR2(32767);
252 L_ATTR_GRP NUMBER;
253 L_CTR NUMBER;
254 L_O_CTR NUMBER;
255 l_uda_xml XMLTYPE;
256
257 BEGIN
258
259 L_O_CTR := 0;
260 FOR REC IN C_ATTR_GRP LOOP
261 IF REC.MULTI_ROW = 'Y' THEN
262 IF L_O_CTR > 0 THEN
263 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
264 || '", XMLAgg(XMLForest(' ;
265 ELSE
266 L_SQL := '(select XMLElement ( "VAR_VALUE" , XMLAgg( XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
267 END IF;
268 ELSE
269 IF L_O_CTR > 0 THEN
270 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
271 || '", XMLForest(' ;
272 ELSE
273 L_SQL := '(select XMLElement ( "VAR_VALUE" , XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
274 END IF;
275 END IF;
276 L_CTR := 0 ;
277 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
278 IF L_CTR = 0 THEN
279 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
280 ELSE
281 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
282 END IF;
283 if r.flex_value_set_id is not null then
284 if (r.data_type = 'C' or r.data_type = 'A' ) then
285 l_sql := l_sql || ',' || get_uda_attr_desc_sql(
286 p_cat_id,
287 p_VARIABLE_CODE,
288 r.data_type,
289 r.application_column_name,
290 r.end_user_column_name,
291 rec.attr_group,
292 r.ATTR_ID );
293 end if;
294 end if;
295 L_CTR := L_CTR + 1;
296 END LOOP;
297 IF REC.MULTI_ROW = 'Y' THEN
298 L_SQL := L_SQL || '))))';
299 ELSE
300 L_SQL := L_SQL || ' )))';
301 END IF;
302 L_SQL := L_SQL || ' from OKC_K_ART_VAR_EXT_VL where CAT_ID = ' ||
303 p_cat_Id || ' AND VARIABLE_CODE = ' || p_VARIABLE_CODE ||
304 ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
305 L_O_CTR := L_O_CTR + 1;
306 END LOOP;
307
308 l_op := 'select XMLConcat(' || l_sql || ') from dual';
309 execute immediate l_op into l_uda_xml;
310
311 RETURN (l_uda_xml.getClobVal());
312 EXCEPTION
313 WHEN OTHERS THEN
314 RAISE;
315 END get_uda_attr_xml;
316
317
318 PROCEDURE update_uda_attr_xml (
319 p_init_msg_list IN VARCHAR2 ,
320 p_cat_id IN NUMBER,
321 p_variable_code IN VARCHAR2,
322 p_attr_group_id IN NUMBER,
323 p_mode IN VARCHAR2 DEFAULT 'NORMAL',
324 p_locking_enabled IN VARCHAR2 DEFAULT 'N',
325 x_return_status OUT NOCOPY VARCHAR2,
326 x_msg_count OUT NOCOPY NUMBER,
327 x_msg_data OUT NOCOPY VARCHAR2
328 )
329 IS
330
331 l_api_name varchar2(240) := 'update_uda_attr_xml';
332 BEGIN
333
334 x_return_status := G_RET_STS_SUCCESS;
335
336 -- Standard Start of API savepoint
337 SAVEPOINT g_update_uda_attr_xml_GRP;
338
339 -- Initialize message list if p_init_msg_list is set to TRUE.
340 IF FND_API.to_Boolean( p_init_msg_list ) THEN
341 FND_MSG_PUB.initialize;
342 END IF;
343
344
345 -- for Mode = AMEND mark articles as amended
346 IF p_mode='AMEND' THEN
347
348 OKC_K_ARTICLES_GRP.update_article(
349 p_api_version =>1,
350 p_init_msg_list => FND_API.G_FALSE,
351 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
352 p_validate_commit => FND_API.G_FALSE,
353 p_validation_string => NULL,
354 p_commit => FND_API.G_FALSE,
355 p_mode => p_mode,
356 x_return_status => x_return_status,
357 x_msg_count => x_msg_count,
358 x_msg_data => x_msg_data,
359 p_id => p_cat_id,
360 p_amendment_description => NULL,
361 p_print_text_yn =>NULL,
362 p_object_version_number => NULL,
363 p_lock_terms_yn => p_locking_enabled
364 );
365 --------------------------------------------
366 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
368 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
369 RAISE FND_API.G_EXC_ERROR ;
370 END IF;
371 --------------------------------------------
372
373 END IF; -- mode = AMEND
374
375
376
377 UPDATE okc_k_art_variables
378 SET mr_variable_xml =
379 get_uda_attr_xml (p_cat_id, p_variable_code, p_attr_group_id)
380 WHERE cat_id = p_cat_id AND variable_code = p_variable_code;
381
382 EXCEPTION
383 WHEN FND_API.G_EXC_ERROR THEN
384 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
385 fnd_log.STRING (g_stmt_level,g_pkg_name,'300: Leaving update_uda_attr_xml: OKC_API.G_EXCEPTION_ERROR Exception');
386 END IF;
387
388 ROLLBACK TO g_update_uda_attr_xml_GRP;
389 x_return_status := G_RET_STS_ERROR ;
390 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
391
392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
393 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
394 fnd_log.STRING (g_stmt_level,g_pkg_name,'400: Leaving update_uda_attr_xml: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
395 END IF;
396
397
398 ROLLBACK TO g_update_uda_attr_xml_GRP;
399 x_return_status := G_RET_STS_UNEXP_ERROR ;
400 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
401
402 WHEN OTHERS THEN
403 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
404 fnd_log.STRING (g_stmt_level,g_pkg_name,'500: Leaving update_uda_attr_xml: '||sqlerrm);
405 END IF;
406
407 OKC_API.SET_MESSAGE(
408 p_app_name => G_APP_NAME,
409 p_msg_name => G_UNEXPECTED_ERROR,
410 p_token1 => G_SQLCODE_TOKEN,
411 p_token1_value => SQLCODE,
412 p_token2 => G_SQLERRM_TOKEN,
413 p_token2_value => SQLERRM);
414
415 ROLLBACK TO g_update_uda_attr_xml_GRP;
416 x_return_status := G_RET_STS_UNEXP_ERROR ;
417 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
418 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
419 END IF;
420
421 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
422 END update_uda_attr_xml;
423
424 PROCEDURE checkdochasmrv (
425 docid IN NUMBER,
426 doctype IN VARCHAR2,
427 dochasmrv OUT NOCOPY VARCHAR2
428 )
429 IS
430 BEGIN
431 SELECT 'Y'
432 INTO dochasmrv
433 FROM okc_k_articles_b kart
434 WHERE document_type = doctype
435 AND document_id = docid
436 AND EXISTS (
437 SELECT 'Y'
438 FROM okc_k_art_variables kvar, okc_bus_variables_b var
439 WHERE 1 = 1
440 AND kvar.cat_id = kart.ID
441 AND kvar.variable_code = var.variable_code
442 AND var.mrv_flag = 'Y')
443 AND ROWNUM = 1;
444 EXCEPTION
445 WHEN NO_DATA_FOUND
446 THEN
447 dochasmrv := 'N';
448 WHEN OTHERS
449 THEN
450 dochasmrv := 'N';
451 END checkdochasmrv;
452
453
454
455 PROCEDURE MRV_PRE_PROCESS(DocID IN NUMBER, DocType IN VARCHAR2 )
456 IS
457 l_doc_xml CLOB;
458
459 CURSOR multiRow_vars IS
460 SELECT '<var name="'||t2."varName"||'" type="'||t2."vartype"||'" meaning="'||t2."varMeaning"||'"/>' mrVar,
461 xmltype('<html>'||Dbms_Lob.SubStr(MR_VARIABLE_HTML,dbms_lob.getLength(MR_VARIABLE_HTML),Dbms_Lob.InStr(MR_VARIABLE_HTML,'<head>'))).extract('//style/text()').getClobVal() mrStyle,
462 xmltype('<html>'||Dbms_Lob.SubStr(MR_VARIABLE_HTML,dbms_lob.getLength(MR_VARIABLE_HTML),Dbms_Lob.InStr(MR_VARIABLE_HTML,'<head>'))).extract('//body/*|text()').getClobVal() mrBody
463 FROM okc_mrv_t t,
464 xmltable('//SectionsArticlesToPrintVORow' PASSING xmltype('<dummy>'||regexp_replace(OKC_WORD_DOWNLOAD_UPLOAD.blob_to_clob(blob_data),' ',' ')||'</dummy>')
465 COLUMNS "artVersionId" NUMBER PATH '//SectionsArticlesToPrintVORow/ArticleVersionId/text()',
466 "catId" NUMBER PATH '//SectionsArticlesToPrintVORow/CatId/text()') t1,
467 okc_article_versions av,
468 xmltable('//var' PASSING xmltype('<dummy>'||regexp_replace(av.article_text,' ',' ')||'</dummy>')
469 COLUMNS "varName" VARCHAR2(30) PATH '//var/@name',
470 "vartype" VARCHAR2(1) PATH '//var/@type',
471 "varMeaning" VARCHAR2(30) PATH '//var/@meaning'
472 ) t2,
473 okc_k_art_variables akv
474 WHERE 1=1
475 AND av.article_version_id = t1."artVersionId"
476 AND akv.variable_code = t2."varName"
477 AND akv.cat_id = t1."catId"
478 AND dbms_lob.getLength(akv.MR_VARIABLE_HTML) > 0;
479
480 l_mrStyle CLOB;
481 l_mrBody CLOB;
482 l_mrHtml CLOB;
483 BEGIN
484 SELECT OKC_WORD_DOWNLOAD_UPLOAD.blob_to_clob(blob_data) INTO l_doc_xml
485 FROM okc_mrv_t;
486
487 FOR l_mr_vars IN multiRow_vars LOOP
488 l_mrBody := l_mr_vars.mrBody;
489 --replace style tags
490 l_mrStyle := '<DUMMY>'||regexp_replace(regexp_replace(regexp_replace(l_mr_vars.mrStyle,'\.c','<row1><class1> c'),'{','</class1><styleAttr>'),'}','</styleAttr></row1>')||'</DUMMY>';
491 DECLARE
492 CURSOR allClasses IS
493 SELECT 'class="'||Trim(cl."className")||'"' srcStr,
494 'style="'||Trim(cl."styleAttr")||'"' trgStr
495 FROM xmltable('//row1' PASSING xmltype(l_mrStyle)
496 COLUMNS "className" VARCHAR2(10) PATH '//row1/class1/text()',
497 "styleAttr" VARCHAR2(2000) PATH '//row1/styleAttr/text()') cl;
498
499 BEGIN
500 FOR l_allClasses IN allClasses LOOP
501 l_mrBody := regexp_replace(l_mrBody,l_allClasses.srcStr,l_allClasses.trgStr);
502 END LOOP;
503
504 END;
505 l_doc_xml := regexp_replace(l_doc_xml,l_mr_vars.mrVar,l_mrBody,1,1);
506 END LOOP;
507
508
509 UPDATE okc_mrv_t
510 SET blob_output = OKC_WORD_DOWNLOAD_UPLOAD.clob_to_blob(l_doc_xml)
511 WHERE doc_id= DocID
512 AND doc_type =DocType;
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 RAISE;
517 END MRV_PRE_PROCESS;
518
519 -- This function is called from view okc_bus_variables_search_v.
520 FUNCTION getattributegroupdispname (attrgroupid IN VARCHAR2)
521 RETURN VARCHAR2
522 IS
523 l_agdispname VARCHAR2 (240);
524 BEGIN
525 SELECT attr.attr_group_disp_name
526 INTO l_agdispname
527 FROM ego_attr_groups_v attr, fnd_application fa
528 WHERE attr.attr_group_type = 'OKC_K_ART_VAR_EXT_ATTRS'
529 AND fa.application_short_name = 'OKC'
530 AND attr.application_id = fa.application_id
531 AND attr.attr_group_id = TO_NUMBER (attrgroupid);
532
533 RETURN l_agdispname;
534 EXCEPTION
535 WHEN OTHERS
536 THEN
537 RETURN NULL;
538 END getattributegroupdispname;
539
540 FUNCTION gettemplatename (mrv_tmpl_code IN VARCHAR2)
541 RETURN VARCHAR2
542 IS
543 l_tmplname VARCHAR2 (240);
544 BEGIN
545 IF mrv_tmpl_code IS NULL
546 THEN
547 RETURN NULL;
548 ELSE
549 SELECT template_name
550 INTO l_tmplname
551 FROM xdo_templates_vl xtv
552 WHERE xtv.template_code = mrv_tmpl_code AND xtv.ds_app_short_name(+) =
553 'OKC';
554
555 RETURN l_tmplname;
556 END IF;
557 EXCEPTION
558 WHEN OTHERS
559 THEN
560 RETURN NULL;
561 END gettemplatename;
562
563 PROCEDURE copy_variable_uda_data (
564 p_from_cat_id IN NUMBER,
565 p_from_variable_code IN VARCHAR2,
566 p_to_cat_id IN NUMBER,
567 p_to_variable_code IN VARCHAR2,
568 x_return_status OUT NOCOPY VARCHAR2,
569 x_msg_count OUT NOCOPY NUMBER,
570 x_msg_data OUT NOCOPY VARCHAR2
571 )
572 IS
573 l_api_name VARCHAR2 (30) := 'copy_variable_uda_data';
574 l_progress VARCHAR2 (3) := '000';
575 l_object_id fnd_objects.object_id%TYPE;
576 l_attr_group_type VARCHAR2 (300);
577
578 l_dtlevel_col_value_pairs ego_col_name_value_pair_array;
579 l_main_data_level_id ego_data_level_b.data_level_id%TYPE;
580 x_external_attr_value_pairs ego_col_name_value_pair_table;
581 x_errorcode NUMBER;
582 from_pk_col_value_pairs ego_col_name_value_pair_array;
583 to_pk_col_value_pairs ego_col_name_value_pair_array;
584 x_pk1_col_name VARCHAR2 (100);
585 x_pk1_value NUMBER;
586 x_pk2_col_name VARCHAR2 (100);
587 x_pk2_value NUMBER;
588
589 BEGIN
590 x_pk1_col_name := 'CAT_ID';
591 x_pk2_col_name := 'VARIABLE_CODE';
592 x_pk1_value := p_from_cat_id;
593 x_pk2_value := p_from_variable_code;
594 from_pk_col_value_pairs :=
595 ego_col_name_value_pair_array
596 (ego_col_name_value_pair_obj ('CAT_ID',
597 x_pk1_value
598 ),
599 ego_col_name_value_pair_obj ('VARIABLE_CODE',
600 x_pk2_value
601 )
602 );
603 to_pk_col_value_pairs :=
604 ego_col_name_value_pair_array
605 (ego_col_name_value_pair_obj ('CAT_ID',
606 p_to_cat_id
607 ),
608 ego_col_name_value_pair_obj ('VARIABLE_CODE',
609 p_to_variable_code
610 )
611 );
612
613 BEGIN
614 SELECT object_id
615 INTO l_object_id
616 FROM fnd_objects
617 WHERE obj_name = 'OKC_K_ART_VARIABLES';
618
619 SELECT data_level_id
620 INTO l_main_data_level_id
621 FROM ego_data_level_b
622 WHERE attr_group_type = 'OKC_K_ART_VAR_EXT_ATTRS'
623 -- AND DATA_LEVEL_NAME LIKE '%CLAUSE%'
624 ;
625 EXCEPTION
626 WHEN OTHERS
627 THEN
628 RAISE;
629 END;
630
631 l_dtlevel_col_value_pairs :=
632 ego_col_name_value_pair_array (ego_col_name_value_pair_obj ('PK1_VALUE',
633 NULL
634 )
635 );
636 ego_user_attrs_data_pvt.copy_user_attrs_data
637 (p_api_version => 1.0,
638 p_application_id => 510,
639 p_object_id => l_object_id,
640 p_object_name => 'OKC_K_ART_VARIABLES',
641 p_old_pk_col_value_pairs => from_pk_col_value_pairs,
642 p_old_data_level_id => l_main_data_level_id,
643 p_old_dtlevel_col_value_pairs => l_dtlevel_col_value_pairs,
644 p_new_pk_col_value_pairs => to_pk_col_value_pairs,
645 p_new_data_level_id => l_main_data_level_id,
646 p_new_dtlevel_col_value_pairs => l_dtlevel_col_value_pairs,
647 p_commit => fnd_api.g_false,
648 x_return_status => x_return_status,
649 x_errorcode => x_errorcode,
650 x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data
652 );
653 EXCEPTION
654 WHEN OTHERS
655 THEN
656 RAISE;
657 END copy_variable_uda_data;
658
659 PROCEDURE update_uda_attr_xml (
660 p_cat_id IN NUMBER,
661 p_variable_code IN VARCHAR2,
662 p_attr_group_id IN NUMBER
663 )
664 IS
665
666 BEGIN
667 UPDATE okc_k_art_variables
668 SET mr_variable_xml =
669 get_uda_attr_xml (p_cat_id, p_variable_code, p_attr_group_id)
670 WHERE cat_id = p_cat_id AND variable_code = p_variable_code;
671 EXCEPTION
672 WHEN OTHERS THEN
673 RAISE;
674
675
676 END update_uda_attr_xml;
677
678 END okc_mrv_util;