1 PACKAGE BODY EGO_ITEM_COMPARE_PKG AS
2 /* $Header: EGOPICMB.pls 120.6 2007/08/06 07:05:49 pfarkade ship $ */
3
4
5 -- =================================================================
6 -- Global constants that need to be used.
7 -- =================================================================
8 -- The user language (to display the error messages in appropriate language)
9 G_SESSION_LANG VARCHAR2(99) := USERENV('LANG');
10
11 -- =================================================================
12 -- Global variables
13 -- =================================================================
14
15 G_USER_ID NUMBER := -1;
16 G_LOGIN_ID NUMBER := -1;
17 G_PROG_APPID NUMBER := -1;
18 G_PROG_ID NUMBER := -1;
19 G_REQUEST_ID NUMBER := -1;
20
21
22 --These columns need translation from Code to a Meaning.
23 G_ITEM_TYPE VARCHAR2(30) := 'ITEM_TYPE';
24 G_PRIMARY_UOM VARCHAR2(30) := 'PRIMARY_UOM_CODE';
25 G_CATALOG_GROUP_ID VARCHAR2(30) := 'ITEM_CATALOG_GROUP_ID';
26 G_LIFECYCLE_ID VARCHAR2(30) := 'LIFECYCLE_ID';
27 G_LIFECYCLE_PHASE_ID VARCHAR2(30) := 'CURRENT_PHASE_ID';
28 G_ITEM_DETAIL_LINK VARCHAR2(30) := 'ITEM_DETAIL_LINK';
29 G_APPROVAL_STATUS VARCHAR2(30) := 'APPROVAL_STATUS'; --For Bug 3424153 by absinha
30 G_CONVERSIONS VARCHAR2(30) := 'ALLOWED_UNITS_LOOKUP_CODE'; --For Bug 3424153 by absinha
31 G_STYLE_FLAG VARCHAR2(30) := 'STYLE_ITEM_FLAG' ; -- Bug 6156769
32 G_STYLE_ITEM VARCHAR2(30) := 'STYLE_ITEM_ID' ; -- Bug 6156769
33 G_TRADE_ITEM_DESCRIPTOR VARCHAR2(30) := 'TRADE_ITEM_DESCRIPTOR' ; -- Bug 6156769
34
35 --This is used to log errors into IDC_DEBUG table. (incr programatically)
36 G_LINE_NUM PLS_INTEGER := 2000;
37
38 TYPE g_item_num_table IS TABLE OF MTL_SYSTEM_ITEMS_VL.CONCATENATED_SEGMENTS%TYPE INDEX BY BINARY_INTEGER;
39
40 /*----------------------------------------------------------------------
41 IMPORTANT NOTES:
42
43 1. The functions Get_Item_Type_Disp_Val and Get_Primary_UOM_Disp_Val
44 need to be before the calling function : Get_Item_Attr_Val
45 as they are not exposed in the SPEC, and will give an error otherwise.
46 Eg:
47 PLS-00306: wrong number or types of arguments in call to
48 'GET_ITEM_TYPE_DISP_VAL'
49
50 2.
51 ----------------------------------------------------------------------*/
52
53 -- =========================
54 -- PROCEDURES AND FUNCTIONS
55 -- =========================
56 PROCEDURE log_debug (p_message VARCHAR2) IS
57 -- Start OF comments
58 -- API name : debug function
59 -- TYPE : PRIVATE
60 -- Pre-reqs : None
61 -- FUNCTION : log the error in IDC_DEBUG table
62 --
63 -- Parameters:
64 -- IN : message to be logged
65 BEGIN
66 --INSERT INTO idc_debug values (G_LINE_NUM, TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS') || ' --> '||p_message);
67 G_LINE_NUM := G_LINE_NUM + 1;
68
69 END log_debug;
70
71
72 --To get the Item Type 'meaning'
73 FUNCTION Get_Item_Type_Disp_Val
74 (
75 p_item_type IN VARCHAR2
76 )
77 RETURN VARCHAR2 IS
78
79 l_display_val FND_LOOKUP_VALUES_VL.MEANING%TYPE;
80
81 CURSOR c_item_type_meaning(p_lookup_code VARCHAR) IS
82 SELECT meaning
83 FROM fnd_lookup_values_vl
84 WHERE lookup_type = 'ITEM_TYPE'
85 AND lookup_code = p_lookup_code;
86
87 BEGIN
88
89 OPEN c_item_type_meaning(p_item_type);
90 FETCH c_item_type_meaning INTO l_display_val;
91
92 --If 'meaning' is not retrieved, set the code as the display value.
93 IF (c_item_type_meaning%NOTFOUND) THEN
94 l_display_val := p_item_type;
95 END IF;
96 CLOSE c_item_type_meaning;
97
98 RETURN l_display_val;
99
100 END Get_Item_Type_Disp_Val;
101
102 --To get Approval Status Meaning
103 -- For Bug 3424153 by absinha
104 FUNCTION Get_Approval_Status_Val
105 (
106 p_approval_status IN VARCHAR2
107 )
108 RETURN VARCHAR2 IS
109
110 l_display_val FND_LOOKUP_VALUES_VL.MEANING%TYPE;
111
112 CURSOR c_approval_status_meaning(p_approval_status_val VARCHAR) IS
113 SELECT meaning
114 FROM fnd_lookup_values
115 WHERE language = userenv('LANG')
116 AND lookup_type = 'INV_ITEM_APPROVAL_STATUS'
117 AND lookup_code = p_approval_status_val;
118
119 BEGIN
120
121 OPEN c_approval_status_meaning(p_approval_status);
122 FETCH c_approval_status_meaning INTO l_display_val;
123
124 --If 'meaning' is not retrieved, set the code as the display value.
125 IF (c_approval_status_meaning%NOTFOUND) THEN
126 l_display_val := p_approval_status;
127 END IF;
128 CLOSE c_approval_status_meaning;
129
130 RETURN l_display_val;
131
132 END Get_Approval_Status_Val;
133
134 --To get the Conversion Meaning
135 --For Bug 3424153 by absinha
136 FUNCTION Get_Conversions_Val
137 (
138 p_conversion_meaning IN VARCHAR2
139 )
140 RETURN VARCHAR2 IS
141
142 l_display_val FND_LOOKUP_VALUES_VL.MEANING%TYPE;
143
144 CURSOR c_conversion_meaning(p_conversion_val VARCHAR) IS
145 SELECT meaning
146 FROM fnd_lookup_values_vl
147 WHERE lookup_type = 'MTL_CONVERSION_TYPE'
148 AND lookup_code = p_conversion_val;
149
150 BEGIN
151
152 OPEN c_conversion_meaning(p_conversion_meaning);
153 FETCH c_conversion_meaning INTO l_display_val;
154
155 --If 'meaning' is not retrieved, set the code as the display value.
156 IF (c_conversion_meaning%NOTFOUND) THEN
157 l_display_val := p_conversion_meaning;
158 END IF;
159 CLOSE c_conversion_meaning;
160
161 RETURN l_display_val;
162
163 END Get_Conversions_Val;
164
165 --To get the Primary Unit of Measure 'meaning'
166 FUNCTION Get_Primary_UOM_Disp_Val
167 (
168 p_primary_uom_code IN VARCHAR2
169 )
170 RETURN VARCHAR2 IS
171
172 l_display_val MTL_UNITS_OF_MEASURE_TL.DESCRIPTION%TYPE;
173
174 CURSOR c_uom_desc(p_uom_code VARCHAR) IS
175 SELECT description
176 FROM mtl_units_of_measure_tl
177 WHERE language = userenv('LANG')
178 AND uom_code = p_uom_code;
179
180 BEGIN
181
182 OPEN c_uom_desc(p_primary_uom_code);
183 FETCH c_uom_desc INTO l_display_val;
184
185 --If 'meaning' is not retrieved, set the code as the display value.
186 IF (c_uom_desc%NOTFOUND) THEN
187 l_display_val := p_primary_uom_code;
188 END IF;
189 CLOSE c_uom_desc;
190
191 RETURN l_display_val;
192
193 END Get_Primary_UOM_Disp_Val;
194
195 --To get the Catalog Group Name
196 FUNCTION Get_Catalog_Group_Disp_Val
197 (
198 p_Catalog_Group_id IN VARCHAR2
199 )
200 RETURN VARCHAR2 IS
201
202 l_display_val EGO_CATALOG_GROUPS_V.CATALOG_GROUP%TYPE;
203
204 CURSOR c_catalog_group_name(p_catalog_group_id VARCHAR2) IS
205 SELECT catalog_group
206 FROM ego_catalog_groups_v
207 WHERE catalog_group_id = p_catalog_group_id;
208
209 BEGIN
210
211 OPEN c_catalog_group_name(p_catalog_group_id);
212 FETCH c_catalog_group_name INTO l_display_val;
213
214 --If name is not retrieved, set the ID as the display value.
215 IF (c_catalog_group_name%NOTFOUND) THEN
216 l_display_val := p_catalog_group_id;
217 END IF;
218 CLOSE c_catalog_group_name;
219
220 RETURN l_display_val;
221
222 END Get_Catalog_Group_Disp_Val;
223
224 --To get the Lifecycle Name
225 FUNCTION Get_Lifecycle_Disp_Val
226 (
227 p_lifecycle_id IN VARCHAR2
228 )
229 RETURN VARCHAR2 IS
230
231 l_display_val PA_EGO_LIFECYCLES_V.NAME%TYPE;
232
233 CURSOR c_lifecycle_name(p_lifecycle_id VARCHAR2) IS
234 SELECT name lifecycle_name
235 FROM pa_ego_lifecycles_v
236 -- FROM pa_proj_elements
237 WHERE proj_element_id = p_lifecycle_id;
238
239 BEGIN
240
241 OPEN c_lifecycle_name(p_lifecycle_id);
242 FETCH c_lifecycle_name INTO l_display_val;
243
244 --If name is not retrieved, set the ID as the display value.
245 IF (c_lifecycle_name%NOTFOUND) THEN
246 l_display_val := p_lifecycle_id;
247 END IF;
248 CLOSE c_lifecycle_name;
249
250 RETURN l_display_val;
251
252 END Get_Lifecycle_Disp_Val;
253
254
255 --To get the Lifecycle Phase Name
256 FUNCTION Get_Lifecycle_Phase_Disp_Val
257 (
258 p_lifecycle_phase_id IN VARCHAR2
259 )
260 RETURN VARCHAR2 IS
261
262 l_display_val PA_EGO_PHASES_V.NAME%TYPE;
263
264 CURSOR c_lifecycle_phase_name(p_lifecycle_phase_id VARCHAR2) IS
265 SELECT name lifecycle_phase_name
266 FROM pa_ego_phases_v
267 -- FROM pa_proj_elements
268 WHERE proj_element_id = p_lifecycle_phase_id;
269
270 BEGIN
271
272 OPEN c_lifecycle_phase_name(p_lifecycle_phase_id);
273 FETCH c_lifecycle_phase_name INTO l_display_val;
274
275 --If name is not retrieved, set the ID as the display value.
276 IF (c_lifecycle_phase_name%NOTFOUND) THEN
277 l_display_val := p_lifecycle_phase_id;
278 END IF;
279 CLOSE c_lifecycle_phase_name;
280
281 RETURN l_display_val;
282
283 END Get_Lifecycle_Phase_Disp_Val;
284
285 --Start Bug 6156769
286
287 FUNCTION Get_Style_Flag_Val
288 (
289 p_style_flag IN VARCHAR2
290 )
291 RETURN VARCHAR2 IS
292
293 l_display_val FND_LOOKUP_VALUES_VL.MEANING%TYPE;
294
295 CURSOR c_style_flag_meaning(p_style_flag VARCHAR) IS
296 SELECT meaning
297 FROM fnd_lookup_values
298 WHERE language = userenv('LANG')
299 AND lookup_type = 'EGO_YES_NO'
300 AND lookup_code = p_style_flag;
301
302 BEGIN
303
304 OPEN c_style_flag_meaning(p_style_flag);
305 FETCH c_style_flag_meaning INTO l_display_val;
306
307 --If 'meaning' is not retrieved, set the code as the display value.
308 IF (c_style_flag_meaning%NOTFOUND) THEN
309 l_display_val := p_style_flag;
310 END IF;
311 CLOSE c_style_flag_meaning;
312
313 RETURN l_display_val;
314
315 END Get_Style_Flag_Val;
316
317
318 FUNCTION Get_Style_Item_Disp_Val
319 (
320 p_style_item_id IN NUMBER,
321 p_organization_id IN NUMBER
322 )
323 RETURN VARCHAR2 IS
324
325 l_display_val MTL_SYSTEM_ITEMS_VL.CONCATENATED_SEGMENTS%TYPE;
326
327 CURSOR c_style_item_name(p_style_item_id NUMBER, p_organization_id NUMBER) IS
328 SELECT CONCATENATED_SEGMENTS
329 FROM MTL_SYSTEM_ITEMS_VL
330 WHERE inventory_item_id = p_style_item_id
331 AND organization_id = p_organization_id;
332
333 BEGIN
334
335 OPEN c_style_item_name(p_style_item_id , p_organization_id);
336 FETCH c_style_item_name INTO l_display_val;
337 CLOSE c_style_item_name ;
338
339 RETURN l_display_val;
340
341 END Get_Style_Item_Disp_Val;
342
343 FUNCTION Get_Trade_Item_Desc_Disp_Val
344 (
345 p_trade_item_desc IN VARCHAR2
346 )
347 RETURN VARCHAR2 IS
348
349 l_display_val EGO_VALUE_SET_VALUES_V.DISPLAY_NAME%TYPE;
350
351 CURSOR c_trade_item_desc(p_trade_item_desc VARCHAR) IS
352 SELECT DISPLAY_NAME
353 FROM EGO_VALUE_SET_VALUES_V
354 WHERE ENABLED_CODE = 'Y'
355 AND ((START_DATE IS NULL) OR (START_DATE IS NOT NULL AND START_DATE <= SYSDATE))
356 AND ((END_DATE IS NULL) OR (END_DATE IS NOT NULL AND END_DATE >= SYSDATE))
357 AND INTERNAL_NAME = p_trade_item_desc;
358
359 BEGIN
360
361 OPEN c_trade_item_desc(p_trade_item_desc);
362 FETCH c_trade_item_desc INTO l_display_val;
363
364 --If 'meaning' is not retrieved, set the code as the display value.
365 IF (c_trade_item_desc%NOTFOUND) THEN
366 l_display_val := p_trade_item_desc;
367 END IF;
368 CLOSE c_trade_item_desc;
369
370 RETURN l_display_val;
371
372 END Get_Trade_Item_Desc_Disp_Val;
373
374 --End Bug 6156769
375
376 --Get the Item Attributes Values
377 FUNCTION Get_Item_Attr_Val (
378 p_inventory_item_id IN VARCHAR2
379 ,p_organization_id IN VARCHAR2
380 ,p_attr_name IN VARCHAR2
381 ) RETURN VARCHAR2 IS
382
383 l_dyn_cur INTEGER := DBMS_SQL.OPEN_CURSOR;
384
385 --Assumption: All the 'Main' attributes to be displayed are not more than
386 --1000 chars. Long Description is of 4000 char length, but actual value
387 --populated is lesser length than that.
388 l_item_attr_val VARCHAR2(1000);
389
390 --Some Item Attribute values are CODEs that need to be translated into
391 --Meanings, which will be displayed to the user.
392 --FND_LOOKUP_VALUES.MEANING is VARCHAR(80).
393 --There might be other types of Code to Meaning translation other than
394 --through FND_LOOKUP_VALUES, so having a little more length.
395 l_display_val VARCHAR2(200);
396
397 l_exec_val PLS_INTEGER;
398
399 l_db_column VARCHAR2(100);
400
401 l_dyn_sql VARCHAR2(1000);
402
403 l_msg_txt VARCHAR2(200);
404 BEGIN
405
406 --To save making an unncessary DB trip
407 IF (p_inventory_item_id = '-1' OR p_organization_id = '-1') THEN
408
409 l_item_attr_val := 'NULL';
410 --For BUG 3321433
411 DBMS_SQL.CLOSE_CURSOR(l_dyn_cur);
412
413 ELSE
414
415 l_db_column := Substr(p_attr_name, Instr(p_attr_name,'.')+1);
416
417 --For Item Detail link, processing is different.
418 IF (l_db_column NOT IN (G_ITEM_DETAIL_LINK)) THEN
419
420 --Made this into bind variable SQL for 'SQL Compliance Project':11.5.9+ (5/1/2003)
421 l_dyn_sql := '';
425 l_dyn_sql := l_dyn_sql || ' AND ORGANIZATION_ID = :ORGANIZATION_ID ';
422 l_dyn_sql := l_dyn_sql || ' SELECT '|| l_db_column;
423 l_dyn_sql := l_dyn_sql || ' FROM MTL_SYSTEM_ITEMS_VL ';
424 l_dyn_sql := l_dyn_sql || ' WHERE INVENTORY_ITEM_ID = :INVENTORY_ITEM_ID ';
426
427 --log_debug('l_dyn_sql - '||l_dyn_sql);
428
429 DBMS_SQL.PARSE(l_dyn_cur, l_dyn_sql, DBMS_SQL.NATIVE);
430
431 DBMS_SQL.DEFINE_COLUMN (l_dyn_cur, 1, l_item_attr_val, 1000);
432
433 DBMS_SQL.BIND_VARIABLE(l_dyn_cur,':INVENTORY_ITEM_ID', p_inventory_item_id);
434 DBMS_SQL.BIND_VARIABLE(l_dyn_cur,':ORGANIZATION_ID', p_organization_id);
435
436 l_exec_val := DBMS_SQL.EXECUTE_AND_FETCH(l_dyn_cur);
437
438 DBMS_SQL.COLUMN_VALUE(l_dyn_cur, 1, l_item_attr_val);
439
440 DBMS_SQL.CLOSE_CURSOR(l_dyn_cur);
441 --log_debug('OK '||l_exec_val);
442 --log_debug('l_item_attr_val : '||l_item_attr_val);
443
444 --For specific columns, code to meaning translations are needed.
445 IF (l_item_attr_val IS NOT NULL) THEN
446 IF (l_db_column = G_ITEM_TYPE ) THEN
447 l_item_attr_val := Get_Item_Type_Disp_Val(l_item_attr_val);
448 ELSIF (l_db_column = G_PRIMARY_UOM ) THEN
449 l_item_attr_val := Get_Primary_UOM_Disp_Val(l_item_attr_val);
450 ELSIF (l_db_column = G_CATALOG_GROUP_ID ) THEN
451 l_item_attr_val := Get_Catalog_Group_Disp_Val(l_item_attr_val);
452 ELSIF (l_db_column = G_LIFECYCLE_ID ) THEN
453 l_item_attr_val := Get_Lifecycle_Disp_Val(l_item_attr_val);
454 ELSIF (l_db_column = G_LIFECYCLE_PHASE_ID ) THEN
455 l_item_attr_val := Get_Lifecycle_Phase_Disp_Val(l_item_attr_val);
456 ELSIF (l_db_column = G_APPROVAL_STATUS ) THEN --For Bug 3424153 by absinha
457 l_item_attr_val := Get_Approval_Status_Val(l_item_attr_val);
458 ELSIF (l_db_column = G_CONVERSIONS ) THEN --For Bug 3424153 by absinha
459 l_item_attr_val := Get_Conversions_Val(l_item_attr_val);
460 ELSIF (l_db_column = G_STYLE_FLAG ) THEN --Bug 6156769
461 l_item_attr_val := Get_Style_Flag_Val(l_item_attr_val);
462 ELSIF (l_db_column = G_STYLE_ITEM ) THEN --Bug 6156769
463 l_item_attr_val := Get_Style_Item_Disp_Val(l_item_attr_val,p_organization_id);
464 ELSIF (l_db_column = G_TRADE_ITEM_DESCRIPTOR ) THEN --Bug 6156769
465 l_item_attr_val := Get_Trade_Item_Desc_Disp_Val(l_item_attr_val);
466 END IF;
467 ELSIF (l_db_column = G_APPROVAL_STATUS ) THEN --For Bug 3424153 by absinha
468 l_item_attr_val := Get_Approval_Status_Val('A');
469 END IF;
470
471 ELSE --IF (l_db_column NOT IN (G_ITEM_DETAIL_LINK)) THEN
472
473 IF (l_db_column = G_ITEM_DETAIL_LINK) THEN
474
475 FND_MESSAGE.SET_NAME('EGO','EGO_ITEM_DETAIL_LINK');
476 FND_MESSAGE.SET_TOKEN('INV_ITEM_ID', p_inventory_item_id);
477 FND_MESSAGE.SET_TOKEN('ORG_ID',p_organization_id);
478 --l_item_attr_val := '<a href="/OA.jsp?OAFunc=EGO_ITEM_OVERVIEW\&inventoryItemId=6992&organizationId=204"><img src="/OA_MEDIA/cabo/cache/gb-FNDPREFS.gif" alt="" border="0"></a>';
479 l_item_attr_val := FND_MESSAGE.GET;
480
481 END IF;
482 END IF;
483
484 END IF;
485
486 RETURN l_item_attr_val;
487 --For Bug 3321433
488 EXCEPTION when others then
489 DBMS_SQL.CLOSE_CURSOR(l_dyn_cur);
490 RETURN l_item_attr_val;
491 END Get_Item_Attr_Val;
492
493 --------------------------------------------------------------------------
494 -- DESCRIPTION
495 -- Get User Attribute value.
496 --------------------------------------------------------------------------
497 FUNCTION Get_User_Attr_Val (
498 p_appl_id IN NUMBER
499 ,p_attr_grp_type IN VARCHAR2
500 ,p_attr_grp_name IN VARCHAR2
501 ,p_attr_name IN VARCHAR2
502 ,p_inventory_item_id IN VARCHAR2
503 ,p_organization_id IN VARCHAR2
504 ,p_data_level_name IN VARCHAR2
505 ,p_failed_priv_check_str IN VARCHAR2 DEFAULT NULL
506 )
507 RETURN VARCHAR2 IS
508
509 l_AG_view_privilege FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;--4105308
510 l_user_party_id VARCHAR2(30);
511 l_can_see_value VARCHAR2(1) := 'T';
512 l_user_attr_val VARCHAR2(1000);
513 l_ag_defaulting VARCHAR2(30); -- Defaulting behavior of attribute
514 -- group.
515 l_item_to_query NUMBER; -- Inventory item ID of the item
516 -- which contains the value we want to return.
517
518 BEGIN
519
520 log_debug('Get_User_Attr_Val():');
521 log_debug('Get_User_Attr_Val(): Get_User_Attr_Val (');
522 log_debug('Get_User_Attr_Val(): p_appl_id => ' || p_appl_id);
523 log_debug('Get_User_Attr_Val(): p_attr_grp_type => ' || p_attr_grp_type);
524 log_debug('Get_User_Attr_Val(): p_attr_grp_name => ' || p_attr_grp_name);
528 log_debug('Get_User_Attr_Val(): p_data_level_name => ' || p_data_level_name);
525 log_debug('Get_User_Attr_Val(): p_attr_name => ' || p_attr_name);
526 log_debug('Get_User_Attr_Val(): p_inventory_item_id => ' || p_inventory_item_id);
527 log_debug('Get_User_Attr_Val(): p_organization_id => ' || p_organization_id);
529 log_debug('Get_User_Attr_Val(): p_failed_priv_check_str => ' || p_failed_priv_check_str);
530 log_debug('Get_User_Attr_Val(): )');
531
532 IF (p_inventory_item_id = '-1' OR p_organization_id = '-1') THEN
533
534 l_user_attr_val := 'NULL';
535
536 ELSE
537
538 ------------------------------------------------------------
539 -- First, see whether the Attr Group has a View privilege --
540 ------------------------------------------------------------
541 l_AG_view_privilege := EGO_EXT_FWK_PUB.Get_Privilege_For_Attr_Group (
542 p_application_id => p_appl_id
543 ,p_attr_group_type => p_attr_grp_type
544 ,p_attr_group_name => p_attr_grp_name
545 ,p_which_priv_to_return => 'VIEW'
546 );
547
548 -------------------------------------------------------------
549 -- If there is such a privilege, make sure the user has it --
550 -------------------------------------------------------------
551 IF (l_AG_view_privilege IS NOT NULL) THEN
552
553 l_can_see_value := 'F';
554
555 BEGIN
556
557 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
558 INTO l_user_party_id
559 FROM EGO_PEOPLE_V
560 WHERE USER_NAME = FND_GLOBAL.USER_NAME;
561
562 l_can_see_value := EGO_DATA_SECURITY.Check_Function(
563 p_api_version => 1.0
564 ,p_function => l_AG_view_privilege
565 ,p_object_name => 'EGO_ITEM'
566 ,p_instance_pk1_value => p_inventory_item_id
567 ,p_instance_pk2_value => p_organization_id
568 ,p_user_name => l_user_party_id
569 );
570
571 IF (l_can_see_value IS NULL) THEN
572 l_can_see_value := 'F';
573 END IF;
574
575 EXCEPTION
576 WHEN NO_DATA_FOUND THEN
577 l_can_see_value := 'F';
578 END;
579
580 ------------------------------------------------------------
581 -- If the user doesn't have privileges to see this value, --
582 -- we return the passed-in "fall-back" string instead --
583 ------------------------------------------------------------
584 IF (NOT FND_API.TO_BOOLEAN(l_can_see_value)) THEN
585
586 l_user_attr_val := NVL(p_failed_priv_check_str, 'NULL');
587
588 END IF;
589 END IF;
590
591 -------------------------------------------
592 -- Determine which item we need to query --
593 -------------------------------------------
594
595 -- First, determine the attribute group defaulting behavior
596 SELECT defaulting
597 INTO l_ag_defaulting
598 FROM ego_attr_group_dl
599 WHERE attr_group_id =
600 (SELECT attr_group_id
601 FROM ego_attr_groups_v
602 WHERE application_id = p_appl_id
603 AND attr_group_type = p_attr_grp_type
604 AND attr_group_name = p_attr_grp_name
605 )
606 AND data_level_id =
607 (SELECT data_level_id
608 FROM ego_data_level_b
609 WHERE data_level_name = p_data_level_name
610 AND attr_group_type = p_attr_grp_type
611 AND application_id = p_appl_id
612 );
613
614 -- In light of the defaulting behavior, determine under which item the
615 -- attribute value is stored.
616 IF l_ag_defaulting = G_INHERITED_AG THEN
617 -- If this is a SKU item, we need to query its style item
618 SELECT COALESCE(style_item_id, inventory_item_id) item_id
619 INTO l_item_to_query
620 FROM mtl_system_items_b
621 WHERE inventory_item_id = p_inventory_item_id AND
622 organization_ID = p_organization_id;
623
624 ELSE
625 -- We always query the item by the caller
626 l_item_to_query := p_inventory_item_id;
627 END IF;
628
629 log_debug('Get_User_Attr_Val(): item with ID ' || l_item_to_query || ' will be queried.');
630
631
632 IF (FND_API.TO_BOOLEAN(l_can_see_value)) THEN
633
634 -------------------------------------------
635 -- Finally, query the item for the value --
636 -------------------------------------------
637 l_user_attr_val := EGO_USER_ATTRS_DATA_PVT.Get_User_Attr_Val (
638 p_appl_id => p_appl_id
639 ,p_attr_grp_type => p_attr_grp_type
640 ,p_attr_grp_name => p_attr_grp_name
641 ,p_attr_name => p_attr_name
642 ,p_object_name => 'EGO_ITEM'
643 ,p_pk_col1 => 'INVENTORY_ITEM_ID'
644 ,p_pk_col2 => 'ORGANIZATION_ID'
645 ,p_pk_value1 => l_item_to_query
646 ,p_pk_value2 => p_organization_id
647 ,p_data_level => p_data_level_name
648 );
649
650 END IF;
651 END IF;
652
653 --log_debug('User Ext Attribute Value '||l_user_attr_val);
654
655 RETURN l_user_attr_val;
656
657 END Get_User_Attr_Val;
658
659 END EGO_ITEM_COMPARE_PKG;
[Home] [Help]