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