[Home] [Help]
PACKAGE BODY: APPS.PV_ATTRIBUTE_UTIL
Source
1 PACKAGE BODY PV_ATTRIBUTE_UTIL as
2 /* $Header: pvxvautb.pls 120.4 2005/12/20 22:30:08 amaram ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_ATTRIBUTE_UTIL
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16 FUNCTION MAP_CODE_TO_VALUE ( code VARCHAR2,
17 lov_tbl lov_data_tbl_type
18 )
19 RETURN VARCHAR2;
20
21 FUNCTION MAP_CODE_TO_VALUE1 ( code VARCHAR2,
22 attribute_id NUMBER,
23 lov_string VARCHAR2
24 )
25 RETURN VARCHAR2;
26
27
28 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_ATTRIBUTE_UTIL';
29 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvautb.pls';
30
31 G_USER_ID NUMBER := NVL(FND_GLOBAL.USER_ID, -1);
32 G_LOGIN_ID NUMBER := NVL(FND_GLOBAL.CONC_LOGIN_ID, -1);
33
34 ---------------------------------------------------------------------
35 -- FUNCTION
36 -- GET_ATTR_VALUES_HISTORY
37 --
38 -- PURPOSE
39 -- Based on the attribute_id, entity, entity_id, version This function returns the attribute values appended with comma.
40 --
41 -- PARAMETERS
42 -- attribute_id, entity, entity_id
43 -- returns values separated by comma as varchar2
44 --
45 -- NOTES
46 --
47 ---------------------------------------------------------------------
48
49
50 FUNCTION GET_ATTR_VALUES_HISTORY ( p_attribute_id NUMBER,
51 p_entity VARCHAR2,
52 p_entity_id NUMBER,
53 p_version NUMBER,
54 p_attr_data_type VARCHAR2,
55 p_lov_string VARCHAR2,
56 p_user_date_format VARCHAR2
57 )
58 RETURN VARCHAR2
59 AS
60
61 --CURSOR lc_history_values (pc_attribute_id NUMBER, pc_entity VARCHAR2, pc_entity_id NUMBER, --pc_version NUMBER ) IS
62
63 l_attr_history_values_sql VARCHAR2(32000):=
64
65 'SELECT ' ||
66 'ATTR.ATTRIBUTE_ID "attributeID", '||
67 'ATTR.ATTRIBUTE_TYPE "attributeType", '||
68 'ATTR.DISPLAY_STYLE "displayStyle", '||
69 --ENTY.ATTR_DATA_TYPE "attrDataType",
70 --ENTY.LOV_STRING "lovString",
71 'VAL.ATTR_VALUE "attrValue", '||
72 'VAL.ATTR_VALUE_EXTN "attrValueExtn", '||
73 --CODE.DESCRIPTION "CodeName",
74 --nvl(CODE.DESCRIPTION,VAL.ATTR_VALUE || ' (' || fnd_message.get_String('PV','PV_INVALID_VALUE') || ')') "CodeName",
75 'case when VAL.ATTR_VALUE is null then '''' '||
76 ' when CODE.ATTR_CODE = VAL.ATTR_VALUE then CODE.DESCRIPTION '||
77 ' else VAL.ATTR_VALUE || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'' '||
78 ' end ' ||
79 ' "CodeName", '||
80
81 'VAL.LAST_UPDATE_DATE "date" ' ||
82 'FROM ' ||
83 'PV_ATTRIBUTES_VL ATTR, ' ||
84 'PV_ENTY_ATTR_VALUES VAL, ' ||
85 'PV_ENTITY_ATTRS ENTY, ' ||
86 'PV_ATTRIBUTE_CODES_VL CODE ' ||
87 'WHERE ' ||
88 'ATTR.ATTRIBUTE_ID = :1 AND ' ||
89 'ATTR.ATTRIBUTE_ID = ENTY.ATTRIBUTE_ID AND ' ||
90 'ATTR.ATTRIBUTE_ID = VAL.ATTRIBUTE_ID AND ' ||
91 'ENTY.ENTITY = :2 AND ' ||
92 'VAL.ENTITY = ENTY.ENTITY AND ' ||
93 'VAL.ENTITY_ID = :3 AND ' ||
94 'VAL.VERSION = :4 AND ' ||
95 'CODE.ATTRIBUTE_ID(+)= VAL.ATTRIBUTE_ID AND ' ||
96 'CODE.ATTR_CODE (+)= VAL.ATTR_VALUE ' ||
97 'ORDER BY ' ||
98 'VAL.LAST_UPDATE_DATE '
99 ;
100
101
102 l_attribute_id NUMBER;
103 l_attribute_type VARCHAR2(30);
104 l_display_style VARCHAR2(30);
105 l_attr_data_type VARCHAR2(30) := p_attr_data_type;
106 l_lov_string VARCHAR2(2000):= p_lov_string;
107 l_attr_value VARCHAR2(2000);
108 l_attr_value_extn VARCHAR2(4000);
109 l_code_name VARCHAR2(2500);
110 l_last_update_date DATE;
111
112 l_value VARCHAR2(32000) := '';
113
114 l_lov_data_rec lov_data_rec_type := g_miss_lov_data_rec;
115 l_lov_data_tbl lov_data_tbl_type:= g_miss_lov_data_tbl;
116
117
118 TYPE t_lov_cursor IS REF CURSOR;
119 v_lov_cursor t_lov_cursor;
120 lc_history_values t_lov_cursor;
121
122
123
124 l_counter NUMBER := 0;
125 l_curr_value VARCHAR2(80);
126 l_curr_code VARCHAR2(15);
127 BEGIN
128
129
130 begin
131 if(l_attr_data_type is null) then
132 l_value:= ', ';
133 elsif(l_attr_data_type = 'EXTERNAL' OR l_attr_data_type = 'INT_EXT') then
134 OPEN v_lov_cursor FOR replace(l_lov_string,'?',':1') using p_attribute_id;
135 loop
136 FETCH v_lov_cursor INTO l_lov_data_rec;
137 EXIT WHEN v_lov_cursor%NOTFOUND;
138
139 l_counter :=l_counter+1;
140 l_lov_data_tbl(l_counter) := l_lov_data_rec;
141
142 end loop;
143 end if;
144
145 OPEN lc_history_values FOR l_attr_history_values_sql using p_attribute_id, p_entity, p_entity_id,p_version;
146
147 LOOP
148
149
150 FETCH lc_history_values INTO l_attribute_id, l_attribute_type, l_display_style, l_attr_value, l_attr_value_extn, l_code_name, l_last_update_date;
151 EXIT WHEN lc_history_values%NOTFOUND;
152
153 if (l_attribute_type is null or l_attribute_type ='') then
154 l_value:= '';
155 elsif (l_attribute_type ='DROPDOWN') then
156
157 if(l_attr_data_type is null) then
158 l_value:= ', ';
159 elsif(l_attr_data_type = 'INTERNAL' OR l_attr_data_type = 'EXT_INT') then
160 if (l_display_style is null) then
161 l_value:= ', ';
162 elsif (l_display_style = 'PERCENTAGE' ) then
163 l_value := l_value || l_code_name || '(' || l_attr_value_extn || '%), ';
164 elsif(l_display_style = 'RADIO' or
165 l_display_style = 'SINGLE' or
166 l_display_style = 'MULTI' or
167 l_display_style = 'CHECK') then
168
169 l_value := l_value || l_code_name || ', ';
170
171 else
172 l_value:= ', ';
173 end if;
174
175 elsif(l_attr_data_type = 'EXTERNAL' OR l_attr_data_type = 'INT_EXT') then
176 --has to be processed
177 if(l_attr_value is not null) then
178 l_value := l_value || MAP_CODE_TO_VALUE1(l_attr_value,p_attribute_id, l_lov_string) || ', ';
179 end if;
180 else
181 l_value:= ', ';
182 end if;
183
184
185 else
186 if (l_display_style is null) then
187 l_value:= ', ';
188 elsif (l_display_style = 'DATE') then
189 l_value:= TO_CHAR(TO_DATE(l_attr_value, 'YYYYMMDDHH24MISS'), p_user_date_format) || ', ';
190 elsif (l_display_style = 'CURRENCY') then
191
192
193 begin
194 l_curr_code := SUBSTR(l_attr_value, INSTR(l_attr_value, ':::') + 3,INSTR(l_attr_value, ':::', 1, 2) - (INSTR(l_attr_value, ':::') + 3));
195
196 select name into l_curr_value from fnd_currencies_vl
197 where currency_code=l_curr_code;
198
199
200
201 --l_value:= SUBSTR(l_attr_value, 1, INSTR(l_attr_value, ':::') - 1) ||
202 -- ':::' || l_curr_code || ':::' || l_curr_value || ', ';
203
204 l_value:= SUBSTR(l_attr_value, 1, INSTR(l_attr_value, ':::') - 1) ||
205 ' ' || l_curr_code || ', ';
206
207 /*
208 l_value := pv_check_match_pub.Currency_Conversion(
209 to_number(SUBSTR(l_attr_value, 1, INSTR(l_attr_value, ':::') - 1)),
210 l_curr_code,
211 --p_currency_conversion_date IN DATE := SYSDATE,
212 gl_user_currency_code
213
214 ) || ' ' || gl_user_currency_code || ', ';
215
216 l_value := pv_check_match_pub.Currency_Conversion(
217 l_attr_value,
218 gl_user_currency_code
219
220 ) || ' ' || gl_user_currency_code || ', ';
221
222 */
223
224
225 exception
226 when others then
227 l_value:= ', ';
228 end;
229
230 else
231 l_value:= l_attr_value || ', ';
232 end if;
233
234 end if;
235
236
237
238 END LOOP;
239
240 CLOSE lc_history_values;
241
242 EXCEPTION
243 when others then
244 l_value := ', ';
245
246 end;
247
248 return substr(l_value, 1, length(l_value)-2);
249
250
251
252 END GET_ATTR_VALUES_HISTORY;
253
254
255
256
257 FUNCTION GET_ATTR_VALUES ( p_attribute_id NUMBER,
258 p_entity VARCHAR2,
259 p_entity_id NUMBER,
260 p_attr_data_type VARCHAR2,
261 p_lov_string VARCHAR2,
262 p_is_snap_shot VARCHAR2,
263 p_snap_shot_date VARCHAR2,
264 p_user_date_format VARCHAR2
265 )
266 RETURN VARCHAR2
267 AS
268
269 --CURSOR lc_attr_values (pc_attribute_id NUMBER, pc_entity VARCHAR2, pc_entity_id NUMBER ) IS
270
271 l_attr_values_sql VARCHAR2(32000):=
272
273 'SELECT '||
274 'ATTR.ATTRIBUTE_ID "attributeID", '||
275 'ATTR.ATTRIBUTE_TYPE "attributeType", '||
276 'ATTR.DISPLAY_STYLE "displayStyle", '||
277 'VAL.ATTR_VALUE "attrValue", '||
278 'VAL.ATTR_VALUE_EXTN "attrValueExtn", '||
279 --'CODE.DESCRIPTION "CodeName", '||
280 --'nvl(CODE.DESCRIPTION,VAL.ATTR_VALUE || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'') "CodeName", ' ||
281 --'decode(CODE.DESCRIPTION,null,'',VAL.ATTR_VALUE || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'') "CodeName", ' ||
282 'case when VAL.ATTR_VALUE is null then '''' ' ||
283 'when CODE.ATTR_CODE = VAL.ATTR_VALUE then CODE.DESCRIPTION ' ||
284 'else VAL.ATTR_VALUE || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'' ' ||
285 ' end ' ||
286
287 ' "CodeName", ' ||
288
289
290 'VAL.LAST_UPDATE_DATE "date" '||
291 'FROM '||
292 'PV_ATTRIBUTES_VL ATTR, '||
293 'PV_ENTY_ATTR_VALUES VAL, '||
294 'PV_ENTITY_ATTRS ENTY, '||
295 'PV_ATTRIBUTE_CODES_VL CODE '||
296 'WHERE '||
297 'ATTR.ATTRIBUTE_ID = :1 AND '||
298 'ATTR.ATTRIBUTE_ID = ENTY.ATTRIBUTE_ID AND '||
299 'ATTR.ATTRIBUTE_ID = VAL.ATTRIBUTE_ID AND '||
300 'ENTY.ENTITY = :2 AND '||
301 'VAL.ENTITY = ENTY.ENTITY AND '||
302 'VAL.ENTITY_ID = :3 AND '||
303 'ATTR.ATTRIBUTE_TYPE in (' || '''' || 'DROPDOWN' || '''' || ',' || '''' || 'TEXT' || '''' || ') AND '||
304 'VAL.LATEST_FLAG(+) =' || '''' || 'Y'|| '''' || ' AND '||
305 'CODE.ATTRIBUTE_ID(+)= VAL.ATTRIBUTE_ID AND '||
306 'CODE.ATTR_CODE (+)= VAL.ATTR_VALUE '||
307
308 'UNION '||
309
310 'SELECT '||
311 'ATTR.ATTRIBUTE_ID "attributeID", '||
312 'ATTR.ATTRIBUTE_TYPE "attributeType", '||
313 'ATTR.DISPLAY_STYLE "displayStyle", '||
314 'DECODE(ATTR.RETURN_TYPE, ' || '''' || 'NUMBER' || '''' || ', to_char(VAL.attr_value), VAL.attr_text) "attrValue", '||
315 '''' || '' || '''' || ' "attrValueExtn", '||
316 --'CODE.DESCRIPTION "CodeName", '||
317 --'nvl(CODE.DESCRIPTION,VAL.ATTR_TEXT || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'') "CodeName", ' ||
318 'case when VAL.ATTR_TEXT is null then '''' ' ||
319 'when CODE.ATTR_CODE = VAL.ATTR_TEXT then CODE.DESCRIPTION ' ||
320 'else VAL.ATTR_TEXT || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'' ' ||
321 ' end ' ||
322
323 ' "CodeName", ' ||
324
325 'VAL.LAST_UPDATE_DATE "date" '||
326 'FROM '||
327 'PV_ATTRIBUTES_VL ATTR, '||
328 'PV_SEARCH_ATTR_VALUES VAL, '||
329 'PV_ENTITY_ATTRS ENTY, '||
330 'PV_ATTRIBUTE_CODES_VL CODE '||
331 'WHERE '||
332 'ATTR.ATTRIBUTE_ID = :4 AND '||
333 'ATTR.ATTRIBUTE_ID = ENTY.ATTRIBUTE_ID AND '||
334 'ATTR.ATTRIBUTE_ID = VAL.ATTRIBUTE_ID AND '||
335 'ENTY.ENTITY = :5 AND '||
336 'VAL.PARTY_ID = :6 AND '||
337 'ENTY.DISPLAY_EXTERNAL_VALUE_FLAG = ' || '''' || 'Y' || '''' || ' AND '||
338 'CODE.ATTRIBUTE_ID(+)= VAL.ATTRIBUTE_ID AND '||
339 'CODE.ATTR_CODE (+)= VAL.ATTR_TEXT '
340 ;
341 /*
342 'SELECT ' ||
343 'ATTR.ATTRIBUTE_ID "attributeID", ' ||
344 'ATTR.ATTRIBUTE_TYPE "attributeType", '||
345 'ATTR.DISPLAY_STYLE "displayStyle", ' ||
346 'VAL.ATTR_VALUE "attrValue", ' ||
347 'VAL.ATTR_VALUE_EXTN "attrValueExtn", '||
348 'CODE.DESCRIPTION "CodeName", '||
349 'VAL.LAST_UPDATE_DATE "date" ' ||
350 'FROM ' ||
351 'PV_ATTRIBUTES_VL ATTR, '||
352 'PV_ENTY_ATTR_VALUES VAL, '||
353 'PV_ENTITY_ATTRS ENTY, ' ||
354 'PV_ATTRIBUTE_CODES_VL CODE '||
355 'WHERE ' ||
356 'ATTR.ATTRIBUTE_ID = :1 AND ' ||
357 'ATTR.ATTRIBUTE_ID = ENTY.ATTRIBUTE_ID AND ' ||
358 'ATTR.ATTRIBUTE_ID = VAL.ATTRIBUTE_ID AND ' ||
359 'ENTY.ENTITY = :2 AND ' ||
360 'VAL.ENTITY = ENTY.ENTITY AND ' ||
361 'VAL.ENTITY_ID = :3 AND ' ||
362 'VAL.LATEST_FLAG(+) =' || '''' || 'Y'|| '''' || ' AND ' ||
363 'CODE.ATTRIBUTE_ID(+)= VAL.ATTRIBUTE_ID AND ' ||
364 'CODE.ATTR_CODE (+)= VAL.ATTR_VALUE ' ||
365
366 'ORDER BY '||
367 'VAL.LAST_UPDATE_DATE ';
368 */
369
370 --CURSOR lc_snap_shot_attr_values (pc_attribute_id NUMBER, pc_entity VARCHAR2, pc_entity_id NUMBER, pc_date VARCHAR2 ) IS
371 l_snap_shot_attr_values_sql VARCHAR2(32000):=
372 'select attributeID, attributeType, displayStyle, attrValue, attrValueExtn, CodeName, updateDate from' ||
373 ' (select * from ' || '( SELECT ' ||
374 'ATTR.ATTRIBUTE_ID attributeID, ' ||
375 'ATTR.ATTRIBUTE_TYPE attributeType, ' ||
376 'ATTR.DISPLAY_STYLE displayStyle, ' ||
377 'VAL.ATTR_VALUE attrValue, ' ||
378 'VAL.ATTR_VALUE_EXTN attrValueExtn, ' ||
379 --'CODE.DESCRIPTION CodeName, ' ||
380 --'nvl(CODE.DESCRIPTION,VAL.ATTR_VALUE || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'') "CodeName", ' ||
381 'case when VAL.ATTR_VALUE is null then '''' ' ||
382 'when CODE.ATTR_CODE = VAL.ATTR_VALUE then CODE.DESCRIPTION ' ||
383 'else VAL.ATTR_VALUE || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'' ' ||
384 ' end ' ||
385
386 ' CodeName, ' ||
387
388 'VAL.LAST_UPDATE_DATE updateDate, ' ||
389 '(MAX(VAL.VERSION) OVER (PARTITION BY VAL.ATTRIBUTE_ID)) MaxVersion, ' ||
390 'VAL.VERSION version ' ||
391 'FROM ' ||
392 'PV_ATTRIBUTES_VL ATTR, ' ||
393 'PV_ENTY_ATTR_VALUES VAL, ' ||
394 'PV_ENTITY_ATTRS ENTY, ' ||
395 'PV_ATTRIBUTE_CODES_VL CODE ' ||
396 'WHERE ' ||
397 'ATTR.ATTRIBUTE_ID = :1 AND ' ||
398 'ATTR.ATTRIBUTE_ID = ENTY.ATTRIBUTE_ID AND ' ||
399 'ATTR.ATTRIBUTE_ID = VAL.ATTRIBUTE_ID AND ' ||
400 'ENTY.ENTITY = :2 AND ' ||
401 'VAL.ENTITY = ENTY.ENTITY AND ' ||
402 'VAL.ENTITY_ID = :3 AND ' ||
403 --VAL.LATEST_FLAG(+) ='Y' AND
404 --'VAL.LAST_UPDATE_DATE (+) <= to_date(pc_date,'dd-mon-yy hh:mi:ss') and ' ||
405 --'VAL.LAST_UPDATE_DATE (+) <= to_date(:4,' || '''' || 'dd-mon-yy hh:mi:ss'|| '''' || ') and ' ||
406 'VAL.LAST_UPDATE_DATE (+) <= to_date(:4,:5) and ' ||
407 'CODE.ATTRIBUTE_ID(+)= VAL.ATTRIBUTE_ID AND ' ||
408 'CODE.ATTR_CODE (+)= VAL.ATTR_VALUE ' ||
409 'ORDER BY VAL.LAST_UPDATE_DATE ' ||
410 ') ' ||
411 'where NVL(version,0) = NVL(MaxVersion,0) ' ||
412 ') ' ||
413
414 'UNION '||
415
416 'SELECT '||
417 'ATTR.ATTRIBUTE_ID "attributeID", '||
418 'ATTR.ATTRIBUTE_TYPE "attributeType", '||
419 'ATTR.DISPLAY_STYLE "displayStyle", '||
420 'DECODE(ATTR.RETURN_TYPE, ' || '''' || 'NUMBER' || '''' || ', to_char(VAL.attr_value), VAL.attr_text) "attrValue", '||
421 '''' || '' || '''' || ' "attrValueExtn", '||
422 --'CODE.DESCRIPTION "CodeName", '||
423 --'nvl(CODE.DESCRIPTION,VAL.ATTR_TEXT || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'') "CodeName", ' ||
427 ' end ' ||
424 'case when VAL.ATTR_TEXT is null then '''' ' ||
425 'when CODE.ATTR_CODE = VAL.ATTR_TEXT then CODE.DESCRIPTION ' ||
426 'else VAL.ATTR_TEXT || '' ('' || fnd_message.get_String(''PV'',''PV_INVALID_VALUE'') || '')'' ' ||
428
429 ' "CodeName", ' ||
430
431 'VAL.LAST_UPDATE_DATE "date" '||
432 'FROM '||
433 'PV_ATTRIBUTES_VL ATTR, '||
434 'PV_SEARCH_ATTR_VALUES VAL, '||
435 'PV_ENTITY_ATTRS ENTY, '||
436 'PV_ATTRIBUTE_CODES_VL CODE '||
437 'WHERE '||
438 'ATTR.ATTRIBUTE_ID = :6 AND '||
439 'ATTR.ATTRIBUTE_ID = ENTY.ATTRIBUTE_ID AND '||
440 'ATTR.ATTRIBUTE_ID = VAL.ATTRIBUTE_ID AND '||
441 'ENTY.ENTITY = :7 AND '||
442 'VAL.PARTY_ID = :8 AND '||
443 'ENTY.DISPLAY_EXTERNAL_VALUE_FLAG = ' || '''' || 'Y' || '''' || ' AND '||
444 'CODE.ATTRIBUTE_ID(+)= VAL.ATTRIBUTE_ID AND '||
445 'CODE.ATTR_CODE (+)= VAL.ATTR_TEXT '
446 ;
447
448
449
450
451 l_attribute_id NUMBER;
452 l_attribute_type VARCHAR2(30);
453 l_display_style VARCHAR2(30);
454 l_attr_data_type VARCHAR2(30) := p_attr_data_type;
455 l_lov_string VARCHAR2(2000):= p_lov_string;
456 l_attr_value VARCHAR2(2000);
457 l_attr_value_extn VARCHAR2(4000);
458 l_code_name VARCHAR2(2500);
459 l_last_update_date DATE;
460
461 l_value VARCHAR2(32000) := '';
462
463 l_lov_data_rec lov_data_rec_type := g_miss_lov_data_rec;
464 l_lov_data_tbl lov_data_tbl_type:= g_miss_lov_data_tbl;
465
466 TYPE t_lov_cursor IS REF CURSOR;
467 v_lov_cursor t_lov_cursor;
468 lc_attr_values t_lov_cursor;
469
470 l_counter NUMBER := 0;
471 l_decimal_points NUMBER :=2;
472 l_curr_value VARCHAR2(80);
473 l_curr_code VARCHAR2(15);
474
475 l_query varchar2(32000);
476
477 l_display_external_value_flag varchar2(1);
478
479 gl_user_currency_code VARCHAR(30) :=nvl(fnd_profile.value('ICX_PREFERRED_CURRENCY'), 'USD');
480
481 --l_attribute_type varchar2(30);
482
483 CURSOR c_get_attr_details(cv_attribute_id NUMBER, pc_entity VARCHAR2) IS
484 SELECT attr.attribute_type,attr.decimal_points, enty.DISPLAY_EXTERNAL_VALUE_FLAG
485 FROM PV_ATTRIBUTES_VL attr, pv_entity_attrs enty
486 WHERE attr.attribute_id = cv_attribute_id and
487 attr.attribute_id= enty.attribute_id and
488 enty.entity=pc_entity
489 ;
490
491
492 BEGIN
493
494
495 begin
496
497 for x in c_get_attr_details(cv_attribute_id => p_attribute_id, pc_entity => p_entity )
498 loop
499 l_attribute_type := x.attribute_type;
500 l_decimal_points := x.decimal_points;
501 l_display_external_value_flag := x.display_external_value_flag;
502 end loop;
503
504 if(l_decimal_points is null or l_decimal_points = '') then
505 l_decimal_points := 2;
506 end if;
507
508 if (l_display_external_value_flag is null or l_display_external_value_flag ='' ) then
509 l_display_external_value_flag := 'N';
510 end if;
511
512 --select attribute_type from pv_attributes_b into l_attribute_type where attribute_id = p_attribute_id;
513
514 /*
515 if(l_attr_data_type is null) then
516 l_value:= ', ';
517 elsif(l_attribute_type= 'DROPDOWN' and (l_attr_data_type = 'EXTERNAL' OR l_attr_data_type = 'INT_EXT')) then
518 OPEN v_lov_cursor FOR replace(l_lov_string,'?',':1') using p_attribute_id;
519 loop
520 FETCH v_lov_cursor INTO l_lov_data_rec;
521 EXIT WHEN v_lov_cursor%NOTFOUND;
522
523 l_counter :=l_counter+1;
524 l_lov_data_tbl(l_counter) := l_lov_data_rec;
525
526 end loop;
527 CLOSE v_lov_cursor;
528 end if;
529 */
530
531 if(p_is_snap_shot is null or p_is_snap_shot = '' or p_is_snap_shot = 'N') then
532
533 l_query := l_attr_values_sql;
534 else
535 l_query := l_snap_shot_attr_values_sql;
536
537 end if;
538
539
540 -- dbms_output.put_line('Before Opening cursor::' );
541
542 if(p_is_snap_shot is null or p_is_snap_shot= '' or p_is_snap_shot= 'N') then
543 OPEN lc_attr_values FOR l_query using p_attribute_id, p_entity, p_entity_id,p_attribute_id, p_entity, p_entity_id;
544 else
545 OPEN lc_attr_values FOR l_query using p_attribute_id, p_entity, p_entity_id,p_snap_shot_date,p_user_date_format,p_attribute_id, p_entity, p_entity_id;
546 end if;
547
548 LOOP
549 --dbms_output.put_line('Before fetching');
550
551 FETCH lc_attr_values INTO l_attribute_id, l_attribute_type, l_display_style, l_attr_value, l_attr_value_extn, l_code_name, l_last_update_date;
552 --dbms_output.put_line('fetched:'||l_attribute_id||':'||l_attribute_type ||':'|| l_display_style || ':'||l_attr_value);
553 EXIT WHEN lc_attr_values%NOTFOUND;
554
555 --dbms_output.put_line('inside cursor loop');
556
557 if (l_attribute_type is null or l_attribute_type ='') then
558 l_value:= '';
559 elsif (l_attribute_type ='DROPDOWN') then
560
561 if(l_attr_data_type is null) then
562 l_value:= ', ';
563 elsif(l_attr_data_type = 'INTERNAL' OR l_attr_data_type = 'EXT_INT') then
564 if (l_display_style is null) then
565 l_value:= ', ';
569 l_display_style = 'SINGLE' or
566 elsif (l_display_style = 'PERCENTAGE' ) then
567 l_value := l_value || l_code_name || '(' || l_attr_value_extn || '%), ';
568 elsif(l_display_style = 'RADIO' or
570 l_display_style = 'MULTI' or
571 l_display_style = 'CHECK') then
572
573 l_value := l_value || l_code_name || ', ';
574
575 else
576 l_value:= ', ';
577 end if;
578
579 elsif(l_attr_data_type = 'EXTERNAL' OR l_attr_data_type = 'INT_EXT') then
580 --has to be processed
581
582 if(l_attr_value is not null) then
583 l_value := l_value || MAP_CODE_TO_VALUE1(l_attr_value,p_attribute_id, l_lov_string) || ', ';
584 end if;
585 else
586 l_value:= ', ';
587 end if;
588 elsif (l_attribute_type ='FUNCTION' and
589 (l_display_style is not null and
590 l_display_style='LOV'
591 )
592 ) then
593
594 if(l_attr_data_type is null) then
595 l_value:= ', ';
596 elsif(l_attr_data_type = 'INTERNAL' OR l_attr_data_type = 'EXT_INT') then
597 l_value := l_value || l_code_name || ', ';
598 elsif(l_attr_data_type = 'EXTERNAL' OR l_attr_data_type = 'INT_EXT') then
599 --has to be processed
600
601 l_value := l_value || MAP_CODE_TO_VALUE1(l_attr_value,p_attribute_id, l_lov_string) || ', ';
602
603 else
604 l_value:= ', ';
605 end if;
606
607 elsif (l_attribute_type ='FUNCTION' ) then
608
609 if (l_display_style is null) then
610 l_value:= ', ';
611 elsif (l_display_style = 'DATE') then
612
613 l_value:= l_value || TO_CHAR(TO_DATE(l_attr_value, 'YYYYMMDDHH24MISS'), p_user_date_format) || ', ';
614 elsif (l_display_style = 'PERCENTAGE') then
615 --l_value:=', ';
616 begin
617 if(l_attr_value is null or l_attr_value = '') then
618 l_value:= ', ';
619 else
620 l_value := l_value || to_number(ROUND(l_attr_value, l_decimal_points))*100 || ' %' ||', ';
621 end if;
622 exception
623 when others then
624 --dbms_output.put_line('error:' || SQLERRM);
625
626 l_value:= ', ';
627 end;
628 elsif (l_display_style = 'NUMBER') then
629 --l_value:=', ';
630 begin
631 if(l_attr_value is null or l_attr_value = '') then
632 l_value:= ', ';
633 else
634 l_value := l_value || ROUND(l_attr_value, l_decimal_points) ||', ';
635 end if;
636 exception
637 when others then
638 --dbms_output.put_line('error:' || SQLERRM);
639
640 l_value:= ', ';
641 end;
642
643 elsif (l_display_style = 'CURRENCY') then
644
645 begin
646 if(l_display_external_value_flag = 'N') then
647 l_curr_code := l_value || SUBSTR(l_attr_value, INSTR(l_attr_value, ':::') + 3,INSTR(l_attr_value, ':::', 1, 2) - (INSTR(l_attr_value, ':::') + 3));
648
649 select name into l_curr_value from fnd_currencies_vl
650 where currency_code=l_curr_code;
651 --dbms_output.put_line('curr_val:' || l_curr_value);
652
653
654 l_value:= l_value || SUBSTR(l_attr_value, 1, INSTR(l_attr_value, ':::') - 1) ||
655 ':::' || l_curr_code || ':::' || l_curr_value || ', ';
656
657 else
658 select name into l_curr_value from fnd_currencies_vl
659 where currency_code=gl_user_currency_code;
660
661 l_value := l_value || pv_check_match_pub.Currency_Conversion(
662 l_attr_value,
663 gl_user_currency_code
664
665 ) || ':::' || gl_user_currency_code || ':::' || l_curr_value || ', ';
666
667 end if;
668
669
670
671
672 exception
673 when others then
674 --dbms_output.put_line('error:' || SQLERRM);
675
676 l_value:= ', ';
677 end;
678
679 else
680 l_value:= l_value || l_attr_value || ', ';
681 end if;
682
683
684
685
686 else
687 if (l_display_style is null) then
688 l_value:= ', ';
689 elsif (l_display_style = 'DATE') then
690
691 l_value:= TO_CHAR(TO_DATE(l_attr_value, 'YYYYMMDDHH24MISS'), p_user_date_format) || ', ';
692 elsif (l_display_style = 'PERCENTAGE') then
693 l_value:=', ';
694 begin
695 if(l_attr_value is null or l_attr_value = '') then
696 l_value:= ', ';
697 else
698 l_value := to_number(ROUND(l_attr_value, l_decimal_points))*100 || ' %' ||', ';
699 end if;
700 exception
701 when others then
702 --dbms_output.put_line('error:' || SQLERRM);
703
704 l_value:= ', ';
705 end;
706 elsif (l_display_style = 'NUMBER') then
707 l_value:=', ';
708 begin
709 if(l_attr_value is null or l_attr_value = '') then
710 l_value:= ', ';
711 else
712 l_value := ROUND(l_attr_value, l_decimal_points) ||', ';
713 end if;
714 exception
715 when others then
716 --dbms_output.put_line('error:' || SQLERRM);
717
721 elsif (l_display_style = 'CURRENCY') then
718 l_value:= ', ';
719 end;
720
722
723 begin
724 if(l_display_external_value_flag = 'N') then
725 l_curr_code := SUBSTR(l_attr_value, INSTR(l_attr_value, ':::') + 3,INSTR(l_attr_value, ':::', 1, 2) - (INSTR(l_attr_value, ':::') + 3));
726
727 select name into l_curr_value from fnd_currencies_vl
728 where currency_code=l_curr_code;
729 --dbms_output.put_line('curr_val:' || l_curr_value);
730
731
732 l_value:= SUBSTR(l_attr_value, 1, INSTR(l_attr_value, ':::') - 1) ||
733 ':::' || l_curr_code || ':::' || l_curr_value || ', ';
734 /*
735 l_value:= SUBSTR(l_attr_value, 1, INSTR(l_attr_value, ':::') - 1) ||
736 ':::' || l_curr_code || ', ';
737 */
738 --dbms_output.put_line('curr_val:' || l_value);
739 /*
740 l_value := pv_check_match_pub.Currency_Conversion(
741 to_number(SUBSTR(l_attr_value, 1, INSTR(l_attr_value, ':::') - 1)),
742 l_curr_code,
743 --p_currency_conversion_date IN DATE := SYSDATE,
744 gl_user_currency_code
745
746 ) || ' ' || gl_user_currency_code || ', ';
747 */
748 else
749 select name into l_curr_value from fnd_currencies_vl
750 where currency_code=gl_user_currency_code;
751
752 l_value := pv_check_match_pub.Currency_Conversion(
753 l_attr_value,
754 gl_user_currency_code
755
756 ) || ':::' || gl_user_currency_code || ':::' || l_curr_value || ', ';
757
758 end if;
759
760
761
762
763 exception
764 when others then
765 --dbms_output.put_line('error:' || SQLERRM);
766
767 l_value:= ', ';
768 end;
769
770 else
771 l_value:= l_attr_value || ', ';
772 end if;
773
774 end if;
775
776
777 if(length(l_value)> 3999) then
778 raise VALUE_ERROR;
779 end if;
780
781 END LOOP;
782
783 CLOSE lc_attr_values;
784
785
786
787 EXCEPTION
788
792 if( length(l_value) > 3999 ) then
789 when others then
790 --dbms_output.put_line('in exception::: ' || SQLERRM || ':::'|| SQLCODE);
791 if SQLCODE = -6502 then
793 l_value := rpad(substr(l_value, 1, 3994),3999,'.');
794 else
795 l_value := '';
796 end if;
797 --dbms_output.put_line('in exception::: ' || SQLERRM);
798 end if;
799
800 --l_value := '';
801 end;
802 --dbms_output.put_line('Before returning');
803 return substr(l_value, 1, length(l_value)-2);
804
805
806
807
808 END GET_ATTR_VALUES;
809
810
811 FUNCTION MAP_CODE_TO_VALUE ( code VARCHAR2,
812 lov_tbl lov_data_tbl_type
813 )
814 RETURN VARCHAR2
815
816 AS
817
818
819
820 BEGIN
821
822 FOR i in 1..lov_tbl.count LOOP
823
824 if(rtrim(lov_tbl(i).code) = rtrim(code)) then
825 return rtrim(lov_tbl(i).meaning);
826 end if;
827
828 END LOOP;
829
830 --if(rtrim(code) = '' or code = null) then
831 -- return '';
832 --else
833 return code || ' (' || fnd_message.get_String('PV','PV_INVALID_VALUE') || ')';
834 --end if;
835
836 END MAP_CODE_TO_VALUE;
837
838
839 FUNCTION MAP_CODE_TO_VALUE1 ( code VARCHAR2,
840 attribute_id NUMBER,
841 lov_string VARCHAR2
842 )
843 RETURN VARCHAR2
844
845 AS
846
847 TYPE t_lov_cursor IS REF CURSOR;
848 v_lov_cursor t_lov_cursor;
849 l_attr_value VARCHAR2(2000);
850 l_lov_data_rec lov_data_rec_type := g_miss_lov_data_rec;
851 l_lov_string VARCHAR2(2000);
852
853
854 BEGIN
855
856 l_lov_string := 'select * from ( ' || replace(lov_string,'?',':1') || ' ) where code = :2 ';
857
858 --dbms_output.put_line('l_lov_string:' || l_lov_string);
859
860 begin
861
862 OPEN v_lov_cursor FOR l_lov_string using attribute_id, code;
863 LOOP
864 FETCH v_lov_cursor INTO l_lov_data_rec;
865 EXIT WHEN v_lov_cursor%NOTFOUND;
866 l_attr_value := l_lov_data_rec.meaning;
867 END LOOP;
868 CLOSE v_lov_cursor;
869 --dbms_output.put_line('l_attr_value:' || l_attr_value);
870
871 exception
872 when others then
873 --dbms_output.put_line('error:' || SQLERRM);
874 l_attr_value:= code || ' (' || fnd_message.get_String('PV','PV_INVALID_VALUE') || ')';
875 end;
876
877 if l_attr_value is not null then
878 return l_attr_value;
879 end if;
880
881 return code || ' (' || fnd_message.get_String('PV','PV_INVALID_VALUE') || ')';
882
883
884 END MAP_CODE_TO_VALUE1;
885
886
887
888
889
890 END PV_ATTRIBUTE_UTIL;