[Home] [Help]
PACKAGE BODY: APPS.EGO_TEMPL_ATTRS_PUB
Source
1 PACKAGE BODY EGO_TEMPL_ATTRS_PUB AS
2 /* $Header: EGOTMPLB.pls 120.7 2007/05/17 21:55:04 ssarnoba ship $ */
3
4 ------------------------------------
5 -- Global Variables and Constants --
6 ------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_TEMPL_ATTRS_PUB';
9 G_CURRENT_USER_ID NUMBER;
10 G_CURRENT_LOGIN_ID NUMBER;
11
12 -- For use with error-reporting --
13 G_ADD_ERRORS_TO_FND_STACK CONSTANT VARCHAR2(1) := 'Y';
14 G_DUMMY_ENTITY_INDEX NUMBER;
15 G_DUMMY_ENTITY_ID VARCHAR2(50);
16 G_DUMMY_MESSAGE_TYPE VARCHAR2(1);
17
18 ----------------------
19 -- Set Globals
20 ----------------------
21 PROCEDURE SetGlobals IS
22 BEGIN
23 G_CURRENT_USER_ID := FND_GLOBAL.User_Id;
24 G_CURRENT_LOGIN_ID := FND_GLOBAL.Login_Id;
25 END;
26
27 ------------------------
28 -- Private Procedures --
29 ------------------------
30
31 ------------------------------------------------------------------------------
32 --
33 -- DESCRIPTION
34 -- Gets the data level ID for the given attribute group
35 --
36 -- AUTHOR
37 -- ssarnoba
38 --
39 -- RELEASE
40 -- R12C
41 --
42 -- NOTES
43 -- (-) For items coming from MTL_SYSTEM_ITEMS, the data level is always ITEM_ORG
44 -- (-) For attribute groups attached to items coming from MTL_SYSTEM_ITEMS, the
45 -- attribute group type is always EGO_MASTER_ITEMS
46
47 -------------------------------------------------------------------------------
48 Procedure Get_Data_Level_ID (
49 p_attribute_group_id IN ego_obj_ag_assocs_b.attr_group_id %TYPE,
50 -- NUMBER
51 p_data_level_name IN ego_data_level_b.data_level_name %TYPE
52 := 'ITEM_ORG', -- VARCHAR2
53 p_attr_group_type IN ego_data_level_b.attr_group_type %TYPE
54 := 'EGO_MASTER_ITEMS', -- VARCHAR2
55 p_application_id IN ego_data_level_b.application_id %TYPE
56 := 431, -- NUMBER
57 x_data_level_id OUT NOCOPY ego_data_level_b.data_level_id %TYPE);
58 -- NUMBER
59
60 ------------------------------------------------------------------------------
61 --
62 -- DESCRIPTION
63 -- Gets the Attribute Group ID and Attribute ID for the named attribute
64 --
65 -- AUTHOR
66 -- ssarnoba
67 --
68 -- RELEASE
69 -- R12C
70 --
71 ------------------------------------------------------------------------------
72 Procedure Get_Attr_Group_And_Attr_ID (
73 p_attr_name IN ego_attrs_v.attr_name %TYPE,
74 -- VARCHAR2(30)
75 p_attr_group_type IN ego_data_level_b.attr_group_type %TYPE
76 := 'EGO_MASTER_ITEMS', -- VARCHAR2
77 p_application_id IN ego_data_level_b.application_id %TYPE
78 := 431, -- NUMBER
79 x_attr_group_id OUT NOCOPY ego_obj_ag_assocs_b.attr_group_id %TYPE,
80 -- NUMBER
81 x_attr_id OUT NOCOPY ego_attrs_v.attr_id %TYPE);
82 -- NUMBER(15)
83
84 ---------------------------------
85 -- Private Debugging Procedure --
86 ---------------------------------
87
88 /* ----------------------------------------------------------------------
89 * The following procedure is for debugging purposes. Its functionality is
90 * controlled by the global variable G_DEBUG_OUTPUT_LEVEL, whose values are:
91 *
92 * 3: LONG debug messages
93 * 2: MEDIUM debug messages
94 * 1: SHORT debug messages
95 * 0: NO debug messages
96 *
97 * The procedure will only print messages at the specified level or lower.
98 * When logging messages, specify their debug level or let it default to 3.
99 *(You will also have to call "set serveroutput on" to see the output.)
100 * ---------------------------------------------------------------------- */
101
102 PROCEDURE Debug_Msg (
103 p_message IN VARCHAR2
104 ,p_level_of_debug IN NUMBER DEFAULT 3
105 )
106 IS
107
108 PRAGMA AUTONOMOUS_TRANSACTION;
109
110 BEGIN
111 null;
112 -- dbms_output.put_line('EGOTMPLB - ' || p_message);
113 END Debug_Msg;
114
115 ----------------------------------------------------------------
116 --Sync_Template is a procedure provided to sync up all operational attributes
117 --associated with the template
118 --in mtl_item_templ_attributes with ego_templ_attributes
119 --parameters:
120 -- p_attribute_name is the full attribute name in mtl_item_templ_attributes
121 ----------------------------------------------------------------
122
123 Procedure Sync_Template( p_template_id IN NUMBER,
124 p_commit IN VARCHAR2 := FND_API.G_FALSE,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_message_text OUT NOCOPY VARCHAR2
127 )
128 IS
129
130 r_templ_attribute template_attribute_rec_type;
131 e_sync_exception EXCEPTION ;
132 l_api_name VARCHAR2(30) := 'SYNC_TEMPLATE';
133 l_always_insert VARCHAR2(1);
134
135 CURSOR c_templ_attributes IS
136 SELECT
137 EXT.attr_id,
138 FL_CTX_EXT.attr_group_id,
139 EXT.application_column_name,
140 EXT.descriptive_flex_context_code as attr_group_name,
141 ITA.template_id,
142 ITA.enabled_flag,
143 ITA.attribute_name,
144 ITA.attribute_value
145 FROM
146 EGO_FND_DF_COL_USGS_EXT EXT,
147 EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT,
148 MTL_ITEM_TEMPL_ATTRIBUTES ITA
149 WHERE
150 EXT.descriptive_flex_context_code = FL_CTX_EXT.descriptive_flex_context_code and
151 EXT.application_id = FL_CTX_EXT.application_id and
152 EXT.descriptive_flexfield_name = FL_CTX_EXT.descriptive_flexfield_name and
153 EXT.descriptive_flexfield_name = 'EGO_MASTER_ITEMS' and
154 ITA.attribute_name = 'MTL_SYSTEM_ITEMS.'|| EXT.application_column_name and
155 ITA.template_id = p_template_id;
156
157 BEGIN
158
159 Debug_Msg('Sync_Template: BEGIN');
160
161 BEGIN
162 SELECT 'F' INTO l_always_insert
163 FROM ego_templ_attributes
164 WHERE template_id = p_template_id;
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 l_always_insert := 'T';
168 WHEN OTHERS THEN
169 l_always_insert := 'F';
170 END;
171
172 -- sync up each attribute associated to the template
173 FOR r_templ_attribute IN c_templ_attributes LOOP
174 Debug_Msg('Sync_Template: Syncing ' || r_templ_attribute.attribute_name);
175
176 Sync_Template_Attribute(
177 r_templ_attribute.template_id,
178 r_templ_attribute.attribute_name,
179 r_templ_attribute.attribute_value,
180 r_templ_attribute.enabled_flag,
181 p_commit,
182 r_templ_attribute.attr_id,
183 r_templ_attribute.attr_group_id,
184 x_return_status,
185 x_message_text,
186 l_always_insert
187 );
188 IF( x_return_status <> 'S' ) THEN
189 raise e_sync_exception;
190 END IF;
191 END LOOP;
192
193 EXCEPTION
194 WHEN e_sync_exception THEN
195 rollback;
196
197 WHEN OTHERS THEN
198 x_return_status := 'U';
199 x_message_text := 'Unexpected error syncing data';
200 Debug_Msg(l_api_name || x_message_text);
201 rollback;
202
203 END Sync_Template;
204
205 ----------------------------------------------------------------
206 --Sync_Template_Attribute is a procedure provided to sync up operational attribute
207 --values in mtl_item_templ_attributes with ego_templ_attributes
208 --parameters:
209 -- p_attribute_name is the full attribute name in mtl_item_templ_attributes
210 ----------------------------------------------------------------
211
212 Procedure Sync_Template_Attribute
213 ( p_template_id IN NUMBER,
214 p_attribute_name IN VARCHAR2,
215 p_attribute_value IN VARCHAR2,
216 p_enabled_flag IN VARCHAR2,
217 p_commit IN VARCHAR2 := FND_API.G_FALSE,
218 p_ego_attr_id IN NUMBER ,
219 p_ego_attr_group_id IN NUMBER ,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_message_text OUT NOCOPY VARCHAR2,
222 p_always_insert IN VARCHAR2 := FND_API.G_FALSE
223 )
224 IS
225
226 --5101284 : Perf issues
227 CURSOR c_check_template_attribute(cp_template_id NUMBER
228 ,cp_attribute_name VARCHAR2) IS
229 SELECT 1
230 FROM fnd_descr_flex_column_usages fl_col ,
231 ego_fnd_df_col_usgs_ext ext,
232 ego_templ_attributes eta
233 WHERE ext.application_id = fl_col.application_id
234 AND ext.descriptive_flexfield_name = fl_col.descriptive_flexfield_name
235 AND ext.descriptive_flex_context_code = fl_col.descriptive_flex_context_code
236 AND ext.application_column_name = fl_col.application_column_name
237 AND fl_col.descriptive_flexfield_name = 'EGO_MASTER_ITEMS'
238 AND eta.attribute_id = ext.attr_id
239 AND 'MTL_SYSTEM_ITEMS.'||fl_col.application_column_name = cp_attribute_name
240 AND eta.template_id = cp_template_id
241 AND rownum = 1;
242
243 l_exists NUMBER;
244 r_inv_templ_attribute mtl_item_templ_attributes%ROWTYPE;
245 l_api_name VARCHAR2(30) := 'SYNC_TEMPLATE_ATTRIBUTE-9: ';
246 l_insert VARCHAR2(1);
247 l_attribute_group_id NUMBER;
248 l_data_level_name VARCHAR2(30);
249 l_data_level_id NUMBER;
250 l_attribute_id ego_attrs_v.attr_id%TYPE; -- NUMBER(15)
251 BEGIN
252
253 Debug_Msg(l_api_name || 'BEGIN');
254 Debug_Msg(l_api_name || 'Syncing template ' || p_template_id ||
255 ' for attribute ' || p_attribute_name);
256
257 ----------------------------------------------------------------------------
258 -- Get the attribute ID and the attribute group ID for the attribute --
259 -- whose name is specified by p_attribute_name --
260 ----------------------------------------------------------------------------
261
262 Get_Attr_Group_And_Attr_ID (
263 p_attr_name => p_attribute_name
264 , x_attr_group_id => l_attribute_group_id
265 , x_attr_id => l_attribute_id -- we never use this
266 );
267
268 ----------------------------------------------------------------------------
269 -- Get the Data Level ID at which this attribute group applies to the --
270 -- item. This will become the data level at which the template values --
271 -- apply for the attribute group too. --
272 ----------------------------------------------------------------------------
273
274 Get_Data_Level_ID (
275 p_attribute_group_id => l_attribute_group_id,
276 x_data_level_id => l_data_level_id
277 );
278
279 Debug_Msg(l_api_name || 'Data level ID is ' || l_data_level_id);
280
281 ----------------------------------------------------------------------------
282 -- Determine whether an INSERT or UPDATE is required --
283 ----------------------------------------------------------------------------
284
285 IF FND_API.TO_BOOLEAN(p_always_insert) THEN
286 l_insert := p_always_insert;
287 ELSE
288 --5101284 : Perf issues
289 OPEN c_check_template_attribute(p_template_id,p_attribute_name);
290 FETCH c_check_template_attribute INTO l_exists;
291 CLOSE c_check_template_attribute;
292 /*--5101284 : Perf issues
293 BEGIN
294 SELECT
295 1 into l_exists
296 FROM
297 dual
298 WHERE
299 exists ( select attr_id from ego_templ_attributes eta, ego_attrs_v av
300 where 'MTL_SYSTEM_ITEMS.'||av.database_column = p_attribute_name
301 and eta.attribute_id = av.attr_id
302 and av.attr_group_type = 'EGO_MASTER_ITEMS'
303 and template_id = p_template_id);
304
305 EXCEPTION
306 WHEN NO_DATA_FOUND THEN
307 l_exists := 0;
308 WHEN OTHERS THEN
309 l_exists := 0;
310 END;
311 */
312
313 l_exists := NVL(l_exists,0);
314 IF( l_exists = 0 ) THEN
315 l_insert := FND_API.G_TRUE;
316 ELSE
317 l_insert := FND_API.G_FALSE;
318 END IF;
319 END IF;
320
321 ----------------------------------------------------------------------------
322 -- Carry out the necessary DML operation --
323 ----------------------------------------------------------------------------
324
325 IF FND_API.TO_BOOLEAN(l_insert) THEN
326 Insert_Template_Attribute( p_template_id,
327 p_ego_attr_group_id,
328 p_ego_attr_id,
329 l_data_level_id,
330 p_enabled_flag,
331 p_attribute_value,
332 p_commit,
333 x_return_status,
334 x_message_text
335 );
336 ELSE
337 -- update the row in ego_templ_attributes for this attribute
338 Update_Template_Attribute( p_template_id,
339 p_ego_attr_group_id,
340 p_ego_attr_id,
341 l_data_level_id,
342 p_enabled_flag,
343 p_attribute_value,
344 p_commit,
345 x_return_status,
346 x_message_text
347 );
348 END IF;
349
350 EXCEPTION
351 WHEN OTHERS THEN
352 x_return_status := 'U';
353 x_message_text := 'Unexpected error syncing data';
354 Debug_Msg(l_api_name || x_message_text);
355 rollback;
356 END Sync_Template_Attribute;
357
358 ----------------------------------------------------------------
359 --Sync_Template_Attribute is a procedure provided to sync up operational attribute
360 --values in mtl_item_templ_attributes with ego_templ_attributes
361 --parameters:
362 -- p_attribute_name is the full attribute name in mtl_item_templ_attributes
363 ----------------------------------------------------------------
364
365 Procedure Sync_Template_Attribute
366 ( p_template_id IN NUMBER,
367 p_attribute_name IN VARCHAR2,
368 p_commit IN VARCHAR2 := FND_API.G_FALSE,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_message_text OUT NOCOPY VARCHAR2,
371 p_always_insert IN VARCHAR2 := FND_API.G_FALSE
372 )
373 IS
374
375 --5101284 : Perf issues
376 CURSOR c_check_template_attribute(cp_template_id NUMBER
380 ego_fnd_df_col_usgs_ext ext,
377 ,cp_attribute_name VARCHAR2) IS
378 SELECT 1
379 FROM fnd_descr_flex_column_usages fl_col ,
381 ego_templ_attributes eta
382 WHERE ext.application_id = fl_col.application_id
383 AND ext.descriptive_flexfield_name = fl_col.descriptive_flexfield_name
384 AND ext.descriptive_flex_context_code = fl_col.descriptive_flex_context_code
385 AND ext.application_column_name = fl_col.application_column_name
386 AND fl_col.descriptive_flexfield_name = 'EGO_MASTER_ITEMS'
387 AND eta.attribute_id = ext.attr_id
388 AND 'MTL_SYSTEM_ITEMS.'||fl_col.application_column_name = cp_attribute_name
389 AND eta.template_id = cp_template_id
390 AND rownum = 1;
391
392 l_exists NUMBER;
393 r_inv_templ_attribute mtl_item_templ_attributes%ROWTYPE;
394 l_attribute_group_id NUMBER;
395 l_attribute_id NUMBER;
396 l_insert VARCHAR2(1);
397 l_data_level_id NUMBER;
398 l_data_level_name VARCHAR2(30);
399 l_api_name VARCHAR2(30) := 'SYNC_TEMPLATE_ATTRIBUTE: ';
400
401 BEGIN
402
403 Debug_Msg(l_api_name || 'BEGIN');
404 Debug_Msg(l_api_name || 'Syncing template ' || p_template_id ||
405 ' for attribute ' || p_attribute_name);
406
407 ----------------------------------------------------------------------------
408 -- Collect the template data relevant to the specified attribute --
409 ----------------------------------------------------------------------------
410
411 Debug_Msg(l_api_name || 'About to collect template data for attribute');
412
413 SELECT *
414 INTO r_inv_templ_attribute
415 FROM mtl_item_templ_attributes mta
416 WHERE mta.ATTRIBUTE_NAME = p_attribute_name
417 AND mta.template_id = p_template_id;
418
419 Debug_Msg(l_api_name || 'Collected template data for attribute');
420
421 ----------------------------------------------------------------------------
422 -- Get the attribute ID and the attribute group ID for the attribute --
423 -- whose name is specified by p_attribute_name --
424 ----------------------------------------------------------------------------
425
426 Get_Attr_Group_And_Attr_ID (
427 p_attr_name => p_attribute_name
428 , x_attr_group_id => l_attribute_group_id
429 , x_attr_id => l_attribute_id
430 );
431
432 ----------------------------------------------------------------------------
433 -- Get the Data Level ID at which this attribute group applies to the --
434 -- item. This will become the data level at which the template values --
435 -- apply for the attribute group too. --
436 ----------------------------------------------------------------------------
437
438 Get_Data_Level_ID (
439 p_attribute_group_id => l_attribute_group_id,
440 x_data_level_id => l_data_level_id);
441
442 ----------------------------------------------------------------------------
443 -- Determine whether an INSERT or UPDATE is required --
444 ----------------------------------------------------------------------------
445
446 IF( l_attribute_id IS NOT null AND l_attribute_group_id IS NOT null ) THEN
447
448 IF FND_API.TO_BOOLEAN(p_always_insert) THEN
449 l_insert := p_always_insert;
450 ELSE
451 --5101284 : Perf issues
452 OPEN c_check_template_attribute(p_template_id,p_attribute_name);
453 FETCH c_check_template_attribute INTO l_exists;
454 CLOSE c_check_template_attribute;
455 /*--5101284 : Perf issues
456 BEGIN
457 SELECT
458 1 into l_exists
459 FROM
460 dual
461 WHERE
462 exists ( select attr_id from ego_templ_attributes eta, ego_attrs_v av
463 where 'MTL_SYSTEM_ITEMS.'||av.database_column = p_attribute_name
464 and eta.attribute_id = av.attr_id
465 and av.attr_group_type = 'EGO_MASTER_ITEMS'
466 and template_id = p_template_id);
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 l_exists := 0;
470 WHEN OTHERS THEN
471 l_exists := 0;
472 END;
473 */
474 l_exists := NVL(l_exists,0);
475 IF( l_exists = 0 ) THEN
476 l_insert := FND_API.G_TRUE;
477 ELSE
478 l_insert := FND_API.G_FALSE;
479 END IF;
480 END IF;
481
482 --------------------------------------------------------------------------
483 -- Carry out the necessary DML operation --
484 --------------------------------------------------------------------------
485
486 IF FND_API.TO_BOOLEAN(l_insert) THEN
487 Debug_Msg(l_api_name || 'Performing insert.');
488 Insert_Template_Attribute( r_inv_templ_attribute.template_id,
489 l_attribute_group_id,
490 l_attribute_id,
491 l_data_level_id,
492 r_inv_templ_attribute.enabled_flag,
496 x_message_text
493 r_inv_templ_attribute.attribute_value,
494 p_commit,
495 x_return_status,
497 );
498
499 ELSE
500 Debug_Msg(l_api_name || 'Performing update.');
501
502 -- update the row in ego_templ_attributes for this attribute
503 Update_Template_Attribute( r_inv_templ_attribute.template_id,
504 l_attribute_group_id,
505 l_attribute_id,
506 l_data_level_id,
507 r_inv_templ_attribute.enabled_flag,
508 r_inv_templ_attribute.attribute_value,
509 p_commit,
510 x_return_status,
511 x_message_text
512 );
513 END IF;
514 END IF; -- if attribute is defined as base attribute
515
516 Debug_Msg(l_api_name || 'Return status from DML operation: ' || x_return_status );
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 x_return_status := 'U';
521 x_message_text := 'Unexpected error syncing data';
522 Debug_Msg(l_api_name || x_message_text);
523 rollback;
524
525 END Sync_Template_Attribute;
526
527 ---------------------------------------
528 -- Insert_Template_Attribute
529 ---------------------------------------
530 Procedure Insert_Template_Attribute
531 ( p_template_id IN NUMBER,
532 p_attribute_group_id IN NUMBER,
533 p_attribute_id IN NUMBER,
534 p_data_level_id IN NUMBER,
535 p_enabled_flag IN VARCHAR2,
536 p_attribute_value IN VARCHAR2,
537 p_commit IN VARCHAR2 := FND_API.G_FALSE,
538 x_return_status OUT NOCOPY VARCHAR2,
539 x_message_text OUT NOCOPY VARCHAR2
540 )
541 IS
542 l_row_num NUMBER;
543 l_attr_string_value VARCHAR2(150);
544 l_attr_date_value DATE;
545 l_attr_number_value NUMBER;
546 l_attr_translated_value VARCHAR2(1000);
547 l_classification_code VARCHAR2(150);
548 l_data_type_code VARCHAR2(1);
549 e_data_type_missing EXCEPTION;
550 l_api_name VARCHAR2(50) := 'INSERT_TEMPLATE_ATTRIBUTE';
551
552 BEGIN
553
554 SetGlobals();
555 -- all base attribute groups have single row attributes
556 l_row_num := 1;
557
558 -- having classification_code = -1 will mark all operational attribute groups in EGO_TEMPL_ATTRIBUTES
559 l_classification_code := '-1';
560
561 -- get data type code
562 select eav.data_type_code into l_data_type_code
563 from ego_attrs_v eav
564 where attr_id = p_attribute_id;
565
566 IF( l_data_type_code = G_CHAR_DATA_TYPE ) THEN
567 l_attr_string_value := p_attribute_value;
568 ELSIF( l_data_type_code = G_NUMBER_DATA_TYPE ) THEN
569 -- convert attribute value to number
570 select to_number(p_attribute_value) into l_attr_number_value from dual;
571 ELSIF( l_data_type_code = G_DATE_DATA_TYPE ) THEN
572 -- convert attribute value to date
573 select to_date(p_attribute_value, 'DD/MM/YYYY') into l_attr_date_value from dual;
574 ELSIF( l_data_type_code = G_DATE_TIME_DATA_TYPE ) THEN
575 -- convert attribute value to date time
576 select to_date(p_attribute_value, 'DD/MM/YYYY HH:MM:SS AM') into l_attr_date_value from dual;
577 ELSIF( l_data_type_code = G_TRANS_TEXT_DATA_TYPE ) THEN
578 l_attr_translated_value := p_attribute_value;
579 ELSE
580 RAISE e_data_type_missing;
581 END IF;
582
583 insert into ego_templ_attributes(template_id,
584 attribute_group_id,
585 attribute_id,
586 enabled_flag,
587 last_update_date,
588 last_updated_by,
589 creation_date,
590 created_by,
591 row_number,
592 attribute_string_value,
593 attribute_date_value,
594 attribute_number_value,
595 attribute_translated_value,
596 classification_code,
597 data_level_id
598 )
599 values( p_template_id,
600 p_attribute_group_id,
601 p_attribute_id,
602 p_enabled_flag,
603 sysdate,
604 g_current_user_id,
605 sysdate,
606 g_current_user_id,
607 l_row_num,
608 l_attr_string_value,
609 l_attr_date_value,
610 l_attr_number_value,
611 l_attr_translated_value,
612 l_classification_code,
613 p_data_level_id
614 );
615
616 x_return_status := 'S';
617 IF( p_commit = fnd_api.g_TRUE ) THEN
618 commit;
619 END IF;
620
621 EXCEPTION
622 WHEN OTHERS THEN
623 x_return_status := 'U';
624 x_message_text := 'Failure to insert new row for template attribute';
625 rollback;
626 END Insert_Template_Attribute;
627
628 ---------------------------------------
629 -- Update_Template_Attribute
630 ---------------------------------------
634 p_attribute_id IN NUMBER,
631 Procedure Update_Template_Attribute
632 ( p_template_id IN NUMBER,
633 p_attribute_group_id IN NUMBER,
635 p_data_level_id IN NUMBER,
636 p_enabled_flag IN VARCHAR2,
637 p_attribute_value IN VARCHAR2,
638 p_commit IN VARCHAR2 := FND_API.G_FALSE,
639 x_return_status OUT NOCOPY VARCHAR2,
640 x_message_text OUT NOCOPY VARCHAR2
641 )
642 IS
643
644 l_attr_string_value VARCHAR2(150);
645 l_attr_date_value DATE;
646 l_attr_number_value NUMBER;
647 l_attr_translated_value VARCHAR2(1000);
648 l_data_type_code VARCHAR2(1);
649
650 e_data_type_missing EXCEPTION;
651 l_api_name VARCHAR2(50) := 'UPDATE_TEMPLATE_ATTRIBUTE';
652
653 BEGIN
654
655 Debug_Msg(l_api_name || ' p_template_id => ' || p_template_id);
656 Debug_Msg(l_api_name || ' p_attribute_group_id => ' || p_attribute_group_id);
657 Debug_Msg(l_api_name || ' p_attribute_id => ' || p_attribute_id);
658 Debug_Msg(l_api_name || ' p_data_level_id => ' || p_data_level_id);
659 Debug_Msg(l_api_name || ' p_enabled_flag => ' || p_enabled_flag);
660 Debug_Msg(l_api_name || ' p_attribute_value => ' || p_attribute_value);
661 Debug_Msg(l_api_name || ' p_commit => ' || p_commit);
662
663 SetGlobals();
664 -- get data type code
665 select eav.data_type_code into l_data_type_code
666 from ego_attrs_v eav
667 where attr_id = p_attribute_id;
668
669 IF( l_data_type_code = G_CHAR_DATA_TYPE ) THEN
670 l_attr_string_value := p_attribute_value;
671
672 ELSIF( l_data_type_code = G_NUMBER_DATA_TYPE ) THEN
673 -- convert attribute value to number
674 select to_number(p_attribute_value) into l_attr_number_value from dual;
675
676 ELSIF( l_data_type_code = G_DATE_DATA_TYPE ) THEN
677 -- convert attribute value to date
678 select to_date(p_attribute_value, 'DD/MM/YYYY') into l_attr_date_value from dual;
679
680 ELSIF( l_data_type_code = G_DATE_TIME_DATA_TYPE ) THEN
681 select to_date(p_attribute_value, 'DD/MM/YYYY HH:MM:SS AM') into l_attr_date_value from dual;
682
683 ELSIF( l_data_type_code = G_TRANS_TEXT_DATA_TYPE ) THEN
684 l_attr_translated_value := p_attribute_value;
685
686 ELSE
687 RAISE e_data_type_missing;
688 END IF;
689
690 update ego_templ_attributes
691 set attribute_string_value = l_attr_string_value,
692 attribute_number_value = l_attr_number_value,
693 attribute_date_value = l_attr_date_value,
694 attribute_translated_value = l_attr_translated_value,
695 enabled_flag = p_enabled_flag,
696 created_by = g_current_user_id,
697 creation_date = sysdate,
698 last_updated_by = g_current_user_id,
699 last_update_date = sysdate,
700 last_update_login = g_current_login_id,
701 data_level_id = p_data_level_id
702 where classification_code = '-1'
703 and attribute_id = p_attribute_id
704 and attribute_group_id = p_attribute_group_id
705 and template_id = p_template_id;
706
707 x_return_status := 'S';
708
709 IF( p_commit = fnd_api.g_TRUE ) THEN
710 commit;
711 END IF;
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 x_return_status := 'U';
716 x_message_text := 'Failure to update EGO_TEMPL_ATTRIBUTES';
717 rollback;
718
719 END Update_Template_Attribute;
720
721 ------------------------------------------------------------------------------
722 --
723 -- DESCRIPTION
724 -- Gets the data level ID for the given attribute group
725 --
726 -- AUTHOR
727 -- ssarnoba
728 --
729 -- RELEASE
730 -- R12C
731 --
732 -- NOTES
733 -- (-) For items coming from MTL_SYSTEM_ITEMS, the data level is always ITEM_ORG
734 -- (-) For attribute groups attached to items coming from MTL_SYSTEM_ITEMS, the
735 -- attribute group type is always EGO_MASTER_ITEMS
736
737 -------------------------------------------------------------------------------
738 Procedure Get_Data_Level_ID (
739 p_attribute_group_id IN ego_obj_ag_assocs_b.attr_group_id %TYPE,
740 -- NUMBER
741 p_data_level_name IN ego_data_level_b.data_level_name %TYPE,
742 -- VARCHAR2
743 p_attr_group_type IN ego_data_level_b.attr_group_type %TYPE,
744 -- VARCHAR2
745 p_application_id IN ego_data_level_b.application_id %TYPE,
746 -- NUMBER
747 x_data_level_id OUT NOCOPY ego_data_level_b.data_level_id %TYPE)
748 -- NUMBER
749 IS
750 l_api_name VARCHAR2(30) := 'Get_Data_Level_ID: ';
751
752 BEGIN
753
754 Debug_Msg(l_api_name || ' p_attribute_group_id => ' || p_attribute_group_id);
755 Debug_Msg(l_api_name || ' p_data_level_name => ' || p_data_level_name);
756 Debug_Msg(l_api_name || ' p_attr_group_type => ' || p_attr_group_type);
757 Debug_Msg(l_api_name || ' p_application_id => ' || p_application_id);
758
759 SELECT data_level_id
760 INTO x_data_level_id
761 FROM ego_data_level_b
762 WHERE application_id = p_application_id AND
763 attr_group_type = p_attr_group_type AND
764 data_level_name = p_data_level_name;
765
766 END Get_Data_Level_ID;
767
768
769 ------------------------------------------------------------------------------
770 --
771 -- DESCRIPTION
772 -- Gets the Attribute Group ID and Attribute ID for the named attribute
773 --
774 -- AUTHOR
775 -- ssarnoba
776 --
777 -- RELEASE
778 -- R12C
779 --
780 ------------------------------------------------------------------------------
781 Procedure Get_Attr_Group_And_Attr_ID (
782 p_attr_name IN ego_attrs_v.attr_name %TYPE,
783 -- VARCHAR2(30)
784 p_attr_group_type IN ego_data_level_b.attr_group_type %TYPE,
785 -- VARCHAR2
786 p_application_id IN ego_data_level_b.application_id %TYPE,
787 -- NUMBER
788 x_attr_group_id OUT NOCOPY ego_obj_ag_assocs_b.attr_group_id %TYPE,
789 -- NUMBER
790 x_attr_id OUT NOCOPY ego_attrs_v.attr_id %TYPE)
791 -- NUMBER(15)
792 IS
793 l_api_name VARCHAR2(30) := 'Get_Attr_Group_And_Attr_ID: ';
794 BEGIN
795
796 Debug_Msg(l_api_name || ' p_attr_name => ' || p_attr_name);
797 Debug_Msg(l_api_name || ' p_attr_group_type => ' || p_attr_group_type);
798 Debug_Msg(l_api_name || ' p_application_id => ' || p_application_id);
799
800 SELECT eav.attr_id, eagv.attr_group_id
801 INTO x_attr_id, x_attr_group_id
802 FROM ego_attrs_v eav, ego_attr_groups_v eagv
803 WHERE 'MTL_SYSTEM_ITEMS.'||eav.database_column = p_attr_name
804 AND eav.attr_group_type = eagv.attr_group_type
805 AND eav.attr_group_name = eagv.attr_group_name
806 AND eav.application_id = p_application_id
807 -- This filtering is added to Supply the Index
808 -- and thus eliminating full table scan Bug 4926750
809 AND eav.application_id = eagv.application_id
810 AND eav.attr_group_type = p_attr_group_type;
811
812 Debug_Msg(l_api_name || 'Attribute Group ID is ' || x_attr_group_id);
813 Debug_Msg(l_api_name || 'Attribute ID is ' || x_attr_id);
814
815 EXCEPTION
816 WHEN NO_DATA_FOUND THEN
817 x_attr_id := null;
818 x_attr_group_id := null;
819 Debug_Msg(l_api_name || 'ERROR - NO DATA FOUND' );
820 WHEN OTHERS THEN
821 x_attr_id := null;
822 x_attr_group_id := null;
823 Debug_Msg(l_api_name || 'ERROR' );
824 END Get_Attr_Group_And_Attr_ID;
825
826
827 END EGO_TEMPL_ATTRS_PUB;