[Home] [Help]
PACKAGE BODY: APPS.PO_UDA_PUB
Source
1 PACKAGE BODY PO_UDA_PUB AS
2 /* $Header: PO_UDA_PUB.plb 120.7 2011/02/01 15:00:59 abhinraj ship $ */
3 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_UDA_PUB');
4
5 --- variables for persisting address value read --
6 g_pk1_value number;
7 g_pk2_value number;
8 g_pk3_value number;
9 g_pk4_value number;
10 g_pk5_value number;
11 g_entity_code varchar2(100);
12 g_attr_id number;
13 g_attr_grp_id number;
14 g_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
15 g_mode varchar2(100);
16
17 /*
18 mode take two possible values - 'INTERNAL_VALUE', 'DISPLAY_VALUE'.
19 */
20
21 PROCEDURE GET_ATTR_VALUE (
22 p_template_id IN NUMBER DEFAULT NULL,
23 p_entity_code IN VARCHAR2 DEFAULT null,
24 pk1_value IN VARCHAR2 DEFAULT NULL,
25 pk2_value IN VARCHAR2 DEFAULT NULL,
26 pk3_value IN VARCHAR2 DEFAULT NULL,
27 pk4_value IN VARCHAR2 DEFAULT NULL,
28 pk5_value IN VARCHAR2 DEFAULT NULL,
29 p_attr_grp_id IN NUMBER DEFAULT NULL,
30 p_attr_grp_int_name IN VARCHAR2 DEFAULT NULL,
31 p_attr_id IN NUMBER DEFAULT NULL,
32 p_attr_int_name IN VARCHAR2 DEFAULT NULL,
33 p_mode IN VARCHAR2 DEFAULT 'INTERNAL_VALUE',
34 p_attr_grp_pk_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
35 x_multi_row_code OUT NOCOPY VARCHAR2,
36 x_single_attr_value OUT NOCOPY VARCHAR2,
37 x_multi_attr_value OUT NOCOPY PO_TBL_VARCHAR4000,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_data OUT NOCOPY VARCHAR2
40 )
41 is
42
43 ATTR_GRP_REQD EXCEPTION;
44 ATTR_REQD EXCEPTION;
45 MULTI_ROW_ATTR_GRP EXCEPTION;
46 d_api_name CONSTANT VARCHAR2(30) := 'GET_ATTR_VALUE';
47 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
48 d_progress NUMBER;
49 l_attr_group_Type VARCHAR2(50);
50 l_ext_table_name VARCHAR2(100);
51 l_db_object_name VARCHAR2(100);
52 l_attr_grp_id NUMBER;
53 l_attr_grp_name VARCHAR2(100);
54 l_db_col_name VARCHAR2(100);
55 l_sql_stmt VARCHAR2(1000);
56 l_attr_value_str VARCHAR2(4000);
57 l_attr_value_date DATE;
58 l_attr_value_number NUMBER;
59 l_attr_disp_value VARCHAR2(4000);
60 l_application_id NUMBER;
61 l_pk1_col_name VARCHAR2(100);
62 l_pk2_col_name VARCHAR2(100);
63 l_pk3_col_name VARCHAR2(100);
64 l_pk4_col_name VARCHAR2(100);
65 l_pk5_col_name VARCHAR2(100);
66 l_attr_int_name VARCHAR2(100);
67 l_multi_row_code VARCHAR2(1);
68 l_attr_id NUMBER;
69 l_pk_index NUMBER := 1;
70
71 l_multi_attr_value_index NUMBER;
72
73 cursor get_key_cols (p_attr_group_type varchar2,
74 p_attr_group_name varchar2 ) is
75 select database_column
76 from ego_attrs_v
77 where ATTR_GROUP_TYPE = p_attr_group_type
78 and ATTR_GROUP_NAME = p_attr_group_name
79 and UNIQUE_KEY_FLAG = 'Y'
80 order by sequence;
81
82 l_data_type_code VARCHAR2(2);
83
84
85
86 BEGIN
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88 d_progress := 010;
89 IF (PO_LOG.d_proc) THEN
90 PO_LOG.proc_begin(d_module);
91 PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
92 PO_LOG.proc_begin(d_module, 'p_entity_code',p_template_id);
93 PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
94 PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
95 PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
96 PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
97 PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
98 PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
99 PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
100 PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
101 PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
102 PO_LOG.proc_begin(d_module, 'p_mode',p_mode);
103 END IF;
104
105 IF p_entity_code IS NULL THEN
106 SELECT ENTITY_CODE
107 INTO l_attr_group_Type
108 FROM PO_UDA_AG_TEMPLATES
109 WHERE TEMPLATE_ID = p_template_id;
110 ELSE
111 l_attr_group_Type := p_entity_code;
112 END IF;
113
114 IF PO_LOG.d_stmt THEN
115 PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
116 END IF;
117
118 d_progress := 020;
119 l_ext_table_name := po_uda_data_util.g_object_dff_tl(l_attr_group_Type).l_object_ext_b_table;
120 l_db_object_name := po_uda_data_util.g_object_dff_tl(l_attr_group_Type).l_object_name;
121
122 -- if attr grp id is supplied, get the name. If the attr grp name is supplied, get the id
123 d_progress := 030;
124 begin
125 if p_attr_grp_id is not null then
126 l_attr_grp_id := p_attr_grp_id;
127
128 select attr_group_name, MULTI_ROW_CODE
129 into l_attr_grp_name, l_multi_row_code
130 from ego_attr_groups_v
131 where ATTR_GROUP_TYPE = l_attr_group_Type
132 and attr_group_id = l_attr_grp_id;
133
134 elsif p_attr_grp_int_name is not null then
135 l_attr_grp_name := p_attr_grp_int_name;
136
137 select attr_group_id, MULTI_ROW_CODE
138 into l_attr_grp_id, l_multi_row_code
139 from ego_attr_groups_v
140 where ATTR_GROUP_TYPE = l_attr_group_Type
141 and attr_group_name = l_attr_grp_name;
142
143 else
144 raise ATTR_GRP_REQD;
145 end if;
146 exception
147 when no_data_found then
148 po_log.exc(d_module,d_progress, 'At '|| d_progress ||': invalid attribute group');
149 raise ATTR_GRP_REQD;
150 end;
151
152 IF PO_LOG.d_stmt THEN
153 PO_LOG.stmt(d_module,d_progress,'l_attr_grp_id',l_attr_grp_id);
154 PO_LOG.stmt(d_module,d_progress,'l_attr_grp_name',l_attr_grp_name);
155 END IF;
156
157 -- next hit ego_attrs_v to get the db column name
158 d_progress := 040;
159 begin
160 if p_attr_id is not null then
161
162 select database_column, application_id, attr_name, attr_id, DATA_TYPE_CODE
163 into l_db_col_name, l_application_id, l_attr_int_name, l_attr_id, l_data_type_code
164 from ego_attrs_v
165 where attr_id = p_attr_id
166 and ATTR_GROUP_NAME = l_attr_grp_name
167 and ATTR_GROUP_TYPE = l_attr_group_Type;
168
169 elsif p_attr_int_name is not null then
170 select database_column, application_id, attr_name, attr_id, DATA_TYPE_CODE
171 into l_db_col_name, l_application_id, l_attr_int_name, l_attr_id, l_data_type_code
172 from ego_attrs_v
173 where ATTR_NAME = p_attr_int_name
174 and ATTR_GROUP_NAME = l_attr_grp_name
175 and ATTR_GROUP_TYPE = l_attr_group_Type;
176
177 else
178 raise ATTR_REQD;
179 end if;
180 exception
181 when no_data_found then
182 po_log.exc(d_module,d_progress,'At '|| d_progress ||': invalid attribute');
183 raise ATTR_REQD;
184 end;
185 IF PO_LOG.d_stmt THEN
186 PO_LOG.stmt(d_module,d_progress,'l_db_col_name',l_db_col_name);
187 END IF;
188
189 -- construct the sql query on the ext table
190 d_progress := 050;
191 l_sql_stmt := 'select '|| l_db_col_name ||' from '||l_ext_table_name
192 ||' where attr_group_id = '|| l_attr_grp_id ;
193 -- construct the primary key part
194 select pk1_column_name, pk2_column_name, pk3_column_name, pk4_column_name, pk5_column_name
195 into l_pk1_col_name, l_pk2_col_name, l_pk3_col_name, l_pk4_col_name, l_pk5_col_name
196 from fnd_objects
197 where obj_name = l_db_object_name
198 and rownum < 2;
199
200 if l_pk1_col_name is not null then
201 l_sql_stmt := l_sql_stmt ||' and '||l_pk1_col_name ||' = '|| pk1_value;
202 end if;
203 if l_pk2_col_name is not null then
204 l_sql_stmt := l_sql_stmt ||' and '||l_pk2_col_name ||' = '|| pk2_value;
205 end if;
206 if l_pk3_col_name is not null then
207 l_sql_stmt := l_sql_stmt ||' and '||l_pk3_col_name ||' = '|| pk3_value;
208 end if;
209 if l_pk4_col_name is not null then
210 l_sql_stmt := l_sql_stmt ||' and '||l_pk4_col_name ||' = '|| pk4_value;
211 end if;
212 if l_pk5_col_name is not null then
213 l_sql_stmt := l_sql_stmt ||' and '||l_pk5_col_name ||' = '|| pk5_value;
214 end if;
215 IF PO_LOG.d_stmt THEN
216 PO_LOG.stmt(d_module,d_progress,'l_sql_stmt',l_sql_stmt);
217 END IF;
218
219 -- execute the query to get the attribute value
220 d_progress := 060;
221 if l_multi_row_code = 'N' then -- single row attribute group
222
223 x_multi_row_code := 'N';
224 IF l_data_type_code = 'D' THEN
225 execute immediate l_sql_stmt into l_attr_value_date;
226 IF PO_LOG.d_stmt THEN
227 PO_LOG.stmt(d_module,d_progress,'l_attr_value_date',substr(l_attr_value_date, 1, 2000));
228 END IF;
229 d_progress := 065;
230
231 if p_mode = 'INTERNAL_VALUE' then
232 x_single_attr_value := To_Char(l_attr_value_date);
233 return;
234 end if;
235
236
237 ELSIF l_data_type_code = 'N' THEN
238 execute immediate l_sql_stmt into l_attr_value_number;
239 IF PO_LOG.d_stmt THEN
240 PO_LOG.stmt(d_module,d_progress,'l_attr_value_number',substr(l_attr_value_number, 1, 2000));
241 END IF;
242
243 d_progress := 065;
244
245 if p_mode = 'INTERNAL_VALUE' then
246 x_single_attr_value := To_Char(l_attr_value_number);
247 return;
248 end if;
249
250
251 ELSE
252 execute immediate l_sql_stmt into l_attr_value_str;
253 IF PO_LOG.d_stmt THEN
254 PO_LOG.stmt(d_module,d_progress,'l_attr_value_str',substr(l_attr_value_str, 1, 2000));
255 END IF;
256 d_progress := 065;
257
258 if p_mode = 'INTERNAL_VALUE' then
259 x_single_attr_value := l_attr_value_str;
260 return;
261 end if;
262
263 END IF;
264
265
266 -- call ego api to get the display value
267 d_progress := 070;
268 IF l_data_type_code = 'D' THEN
269 l_attr_disp_value := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
270 p_application_id => l_application_id
271 ,p_attr_internal_date_value => l_attr_value_date
272 ,p_attr_internal_name => l_attr_int_name
273 ,p_attr_group_type => l_attr_group_Type
274 ,p_attr_group_int_name => l_attr_grp_name
275 ,p_attr_id => l_attr_id
276 ,p_object_name => l_db_object_name
277 ,p_pk1_column_name => l_pk1_col_name
278 ,p_pk1_value => pk1_value
279 ,p_pk2_column_name => l_pk2_col_name
280 ,p_pk2_value => pk2_value
281 ,p_pk3_column_name => l_pk3_col_name
282 ,p_pk3_value => pk3_value
283 ,p_pk4_column_name => l_pk4_col_name
284 ,p_pk4_value => pk4_value
285 ,p_pk5_column_name => l_pk5_col_name
286 ,p_pk5_value => pk5_value
287 );
288 ELSIF l_data_type_code = 'N' THEN
289 l_attr_disp_value := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
290 p_application_id => l_application_id
291 ,p_attr_internal_num_value => l_attr_value_number
292 ,p_attr_internal_name => l_attr_int_name
293 ,p_attr_group_type => l_attr_group_Type
294 ,p_attr_group_int_name => l_attr_grp_name
295 ,p_attr_id => l_attr_id
296 ,p_object_name => l_db_object_name
297 ,p_pk1_column_name => l_pk1_col_name
298 ,p_pk1_value => pk1_value
299 ,p_pk2_column_name => l_pk2_col_name
300 ,p_pk2_value => pk2_value
301 ,p_pk3_column_name => l_pk3_col_name
302 ,p_pk3_value => pk3_value
303 ,p_pk4_column_name => l_pk4_col_name
304 ,p_pk4_value => pk4_value
305 ,p_pk5_column_name => l_pk5_col_name
306 ,p_pk5_value => pk5_value
307 );
308 ELSE
309 l_attr_disp_value := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
310 p_application_id => l_application_id
311 ,p_attr_internal_str_value => l_attr_value_str
312 ,p_attr_internal_name => l_attr_int_name
313 ,p_attr_group_type => l_attr_group_Type
314 ,p_attr_group_int_name => l_attr_grp_name
315 ,p_attr_id => l_attr_id
316 ,p_object_name => l_db_object_name
317 ,p_pk1_column_name => l_pk1_col_name
318 ,p_pk1_value => pk1_value
319 ,p_pk2_column_name => l_pk2_col_name
320 ,p_pk2_value => pk2_value
321 ,p_pk3_column_name => l_pk3_col_name
322 ,p_pk3_value => pk3_value
323 ,p_pk4_column_name => l_pk4_col_name
324 ,p_pk4_value => pk4_value
325 ,p_pk5_column_name => l_pk5_col_name
326 ,p_pk5_value => pk5_value
327 );
328 END IF;
329
330 x_single_attr_value := l_attr_disp_value;
331 return;
332 else -- multi row attribute group, need to add more conditions to the query
333 d_progress := 065;
334 -- fetch the unique key columns for the attribute group.
335 IF p_attr_grp_pk_tbl IS NOT NULL THEN
336 l_pk_index := p_attr_grp_pk_tbl.first;
337
338 for col_rec in get_key_cols (l_attr_group_Type, l_attr_grp_name) loop
339 if l_pk_index is not null then
340 l_sql_stmt := l_sql_stmt ||' and '|| col_rec.DATABASE_COLUMN ||' = '''||p_attr_grp_pk_tbl(l_pk_index) || '''';
341 l_pk_index := p_attr_grp_pk_tbl.NEXT(l_pk_index + 1);
342 end if;
343 end loop;
344 END IF;
345
346 execute immediate l_sql_stmt bulk collect into x_multi_attr_value;
347
348 IF p_mode = 'DISPLAY_VALUE' THEN
349 l_multi_attr_value_index := x_multi_attr_value.first;
350 WHILE (x_multi_attr_value IS NOT NULL AND l_multi_attr_value_index <= x_multi_attr_value.LAST)
351 LOOP
352
353 IF l_data_type_code = 'D' THEN
354 l_attr_value_date := x_multi_attr_value(l_multi_attr_value_index);
355 l_attr_disp_value := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
356 p_application_id => l_application_id
357 ,p_attr_internal_date_value => l_attr_value_date
358 ,p_attr_internal_name => l_attr_int_name
359 ,p_attr_group_type => l_attr_group_Type
360 ,p_attr_group_int_name => l_attr_grp_name
361 ,p_attr_id => l_attr_id
362 ,p_object_name => l_db_object_name
363 ,p_pk1_column_name => l_pk1_col_name
364 ,p_pk1_value => pk1_value
365 ,p_pk2_column_name => l_pk2_col_name
366 ,p_pk2_value => pk2_value
367 ,p_pk3_column_name => l_pk3_col_name
368 ,p_pk3_value => pk3_value
369 ,p_pk4_column_name => l_pk4_col_name
370 ,p_pk4_value => pk4_value
371 ,p_pk5_column_name => l_pk5_col_name
372 ,p_pk5_value => pk5_value
373 );
374 ELSIF l_data_type_code = 'N' THEN
375 l_attr_value_number := x_multi_attr_value(l_multi_attr_value_index);
376 l_attr_disp_value := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
377 p_application_id => l_application_id
378 ,p_attr_internal_num_value => l_attr_value_number
379 ,p_attr_internal_name => l_attr_int_name
380 ,p_attr_group_type => l_attr_group_Type
381 ,p_attr_group_int_name => l_attr_grp_name
382 ,p_attr_id => l_attr_id
383 ,p_object_name => l_db_object_name
384 ,p_pk1_column_name => l_pk1_col_name
385 ,p_pk1_value => pk1_value
386 ,p_pk2_column_name => l_pk2_col_name
387 ,p_pk2_value => pk2_value
388 ,p_pk3_column_name => l_pk3_col_name
389 ,p_pk3_value => pk3_value
390 ,p_pk4_column_name => l_pk4_col_name
391 ,p_pk4_value => pk4_value
392 ,p_pk5_column_name => l_pk5_col_name
393 ,p_pk5_value => pk5_value
394 );
395 ELSE
396 l_attr_value_str := x_multi_attr_value(l_multi_attr_value_index);
397 l_attr_disp_value := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
398 p_application_id => l_application_id
399 ,p_attr_internal_str_value => l_attr_value_str
400 ,p_attr_internal_name => l_attr_int_name
401 ,p_attr_group_type => l_attr_group_Type
402 ,p_attr_group_int_name => l_attr_grp_name
403 ,p_attr_id => l_attr_id
404 ,p_object_name => l_db_object_name
405 ,p_pk1_column_name => l_pk1_col_name
406 ,p_pk1_value => pk1_value
407 ,p_pk2_column_name => l_pk2_col_name
408 ,p_pk2_value => pk2_value
409 ,p_pk3_column_name => l_pk3_col_name
410 ,p_pk3_value => pk3_value
411 ,p_pk4_column_name => l_pk4_col_name
412 ,p_pk4_value => pk4_value
413 ,p_pk5_column_name => l_pk5_col_name
414 ,p_pk5_value => pk5_value
415 );
416 END IF;
417 d_progress := 090;
418 x_multi_attr_value(l_multi_attr_value_index) := l_attr_disp_value;
419 d_progress := 100;
420 l_multi_attr_value_index := x_multi_attr_value.NEXT(l_multi_attr_value_index);
421 d_progress := 110;
422 END LOOP;
423 END IF;
424
425 if x_multi_attr_value.count > 1 then
426 x_multi_row_code := 'Y';
427 ELSIF x_multi_attr_value.count = 1 then
428 x_multi_row_code := 'N';
429 x_single_attr_value := x_multi_attr_value(x_multi_attr_value.first);
430 ELSE
431 raise MULTI_ROW_ATTR_GRP;
432 END IF;
433 end if;
434
435
436 EXCEPTION
437 WHEN ATTR_GRP_REQD then
438 PO_LOG.exc(d_module,d_progress,'Either attribute group id or attribute group internal name must be specified');
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 x_msg_data := 'Either attribute group id or attribute group internal name must be specified' ;
441 return;
442 WHEN ATTR_REQD then
443 PO_LOG.exc(d_module,d_progress,'Either attribute id or attribute internal name must be specified');
444 x_return_status := FND_API.G_RET_STS_ERROR;
445 x_msg_data := 'Either attribute id or attribute internal name must be specified' ;
446 return;
447 WHEN MULTI_ROW_ATTR_GRP THEN
448 PO_LOG.exc(d_module,d_progress,'x_multi_attr_value is not fetched properly');
449 x_return_status := FND_API.G_RET_STS_ERROR;
450 x_msg_data := 'x_multi_attr_value is not fetched properly' ;
451 return;
452 when others then
453 PO_LOG.exc(d_module,d_progress,'Unexpected error: '|| SQLERRM || 'at '||d_progress);
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
455 x_msg_data := 'Unexpected error: '|| SQLERRM || 'at '||d_progress ;
456 return;
457 END GET_ATTR_VALUE;
458
459 FUNCTION GET_ADDRESS_ATTR_VALUE (
460 p_template_id IN NUMBER default null,
461 p_entity_code IN VARCHAR2 default null,
462 pk1_value IN NUMBER default null,
463 pk2_value IN NUMBER default null,
464 pk3_value IN NUMBER default null,
465 pk4_value IN NUMBER default null,
466 pk5_value IN NUMBER default null,
467 p_attr_grp_id IN NUMBER default null,
468 p_attr_grp_int_name IN VARCHAR2 default 'addresses',
469 p_attr_id IN NUMBER default null,
470 p_attr_int_name IN VARCHAR2 default null,
471 p_address_type IN VARCHAR2,
472 p_mode IN VARCHAR2 DEFAULT 'DISPLAY_VALUE'
473 ) RETURN VARCHAR2 is
474
475 d_api_name CONSTANT VARCHAR2(30) := 'GET_ADDRESS_ATTR_VALUE';
476 d_module constant varchar2(100) := '';
477 d_progress NUMBER;
478
479 --exceptions ---
480 ATTR_GRP_REQD exception;
481 INVALID_DB_OBJ exception;
482 ATTR_REQD exception;
483 ATTR_GRP_DATA_NOT_FOUND exception;
484
485 l_attr_group_Type VARCHAR2(50);
486 l_attr_grp_id number;
487 l_ag_type varchar2(100);
488 l_ag_name varchar2(100);
489 l_appl_id number;
490 l_attr_id number;
491 l_attr_name varchar2(100);
492 l_attr_value varchar2(4000);
493 l_data_found varchar2(1) := 'N';
494 l_ext_table_name VARCHAR2(100);
495 l_db_object_name VARCHAR2(100);
496 l_data_level varchar2(100);
497 l_pk1_col_name varchar2(30);
498 l_pk2_col_name varchar2(30);
499 l_pk3_col_name varchar2(30);
500 l_pk4_col_name varchar2(30);
501 l_pk5_col_name varchar2(30);
502 l_attr_list varchar2(2000);
503 l_return_status VARCHAR2(100);
504 l_errorcode NUMBER;
505 l_msg_count NUMBER;
506 l_msg_data VARCHAR2(100);
507 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
508 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE := EGO_ATTR_GROUP_REQUEST_TABLE();
509 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
510 l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
511
512 begin
513 d_progress := 010;
514 IF (PO_LOG.d_proc) THEN
515 PO_LOG.proc_begin(d_module);
516 PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
517 PO_LOG.proc_begin(d_module, 'p_entity_code',p_entity_code);
518 PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
519 PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
520 PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
521 PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
522 PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
523 PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
524 PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
525 PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
526 PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
527 PO_LOG.proc_begin(d_module, 'p_mode',p_mode);
528 END IF;
529
530 -- Determine the attribute group type ---------------
531 IF p_entity_code IS NULL THEN
532 SELECT ENTITY_CODE
533 INTO l_attr_group_Type
534 FROM PO_UDA_AG_TEMPLATES
535 WHERE TEMPLATE_ID = p_template_id;
536 ELSE
537 l_attr_group_Type := p_entity_code;
538 END IF;
539
540 IF PO_LOG.d_stmt THEN
541 PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
542 END IF;
543
544 -- determine the attribute group --------------------
545 d_progress := 020;
546 begin
547 if p_attr_grp_id is null and p_attr_grp_int_name is null then
548 raise ATTR_GRP_REQD;
549 end if;
550
551 if p_attr_grp_int_name is not null and p_attr_grp_id is null then
552 select attr_group_id, attr_group_name, attr_group_type, application_id
553 into l_attr_grp_id, l_ag_name, l_ag_type, l_appl_id
554 from ego_attr_groups_v
555 where ATTR_GROUP_TYPE = l_attr_group_Type
556 and attr_group_name = p_attr_grp_int_name;
557 else
558 select attr_group_id, attr_group_name, attr_group_type, application_id
559 into l_attr_grp_id, l_ag_name, l_ag_type, l_appl_id
560 from ego_attr_groups_v
561 where ATTR_GROUP_TYPE = l_attr_group_Type
562 and attr_group_id = p_attr_grp_id;
563 end if;
564 exception
565 when no_data_found then
566 po_log.exc(d_module,d_progress, 'At '|| d_progress ||': invalid attribute group');
567 raise ATTR_GRP_REQD;
568 end;
569
570 IF PO_LOG.d_stmt THEN
571 PO_LOG.stmt(d_module,d_progress,'l_attr_grp_id:', l_attr_grp_id);
572 END IF;
573
574 -- determine the attribute id ---
575 d_progress := 030;
576 if p_attr_id is null and p_attr_int_name is null then
577 po_log.exc(d_module,d_progress, 'At '|| d_progress ||': invalid attribute');
578 raise ATTR_REQD;
579 end if;
580
581 begin
582 if p_attr_int_name is not null and p_attr_id is null then
583 select attr_id , attr_name
584 into l_attr_id , l_attr_name
585 from ego_attrs_v
586 where attr_name = p_attr_int_name
587 and attr_group_name = l_ag_name
588 and attr_group_type = l_attr_group_type;
589 else
590 select attr_id , attr_name
591 into l_attr_id, l_attr_name
592 from ego_attrs_v
593 where attr_id = p_attr_id
594 and attr_group_name = l_ag_name
595 and attr_group_type = l_attr_group_type;
596 end if;
597 exception
598 when no_data_found then
599 po_log.exc(d_module,d_progress, 'At '|| d_progress ||': invalid attribute');
600 raise ATTR_REQD;
601 end;
602
603 IF PO_LOG.d_stmt THEN
604 PO_LOG.stmt(d_module,d_progress,'l_attr_id:', l_attr_id);
605 END IF;
606
607 -------- read the cached value -----------------
608 -------- Bug 11675463 Commenting logic to take value from cache -----------------
609 /* d_progress := 040;
610 if (nvl(g_pk1_value, 0) = nvl(pk1_value, 0) and
611 nvl(g_pk2_value, 0) = nvl(pk2_value, 0) and
612 nvl(g_pk3_value, 0) = nvl(pk3_value, 0) and
613 nvl(g_pk4_value, 0) = nvl(pk4_value, 0) and
614 nvl(g_pk5_value, 0) = nvl(pk5_value, 0) and
615 nvl(g_attr_id,0) = nvl(l_attr_id,0) and
616 nvl(g_entity_code, '*') = nvl(l_attr_group_type, '*') and
617 nvl(g_attr_grp_id, 0) = nvl(l_attr_grp_id , 0) and
618 nvl(g_mode, '*') = nvl(p_mode, '*')
619 ) then
620 if g_attributes_data_table is not null then
621 for i in 1..g_attributes_data_table.count loop
622 if g_attributes_data_table(i).ATTR_NAME = 'addresstype'
623 and g_attributes_data_table(i).ATTR_VALUE_STR = p_address_type then
624 -- take value from the immediate next object.
625 if(p_mode = 'DISPLAY_VALUE') then
626 l_attr_value := g_attributes_data_table(i+1).ATTR_DISP_VALUE;
627 else
628 if(g_attributes_data_table(i+1).ATTR_VALUE_STR is not null) then
629 l_attr_value := g_attributes_data_table(i+1).ATTR_VALUE_STR;
630 elsif(g_attributes_data_table(i+1).ATTR_VALUE_NUM is not null) then
631 l_attr_value := g_attributes_data_table(i+1).ATTR_VALUE_NUM;
632 else
633 l_attr_value := g_attributes_data_table(i+1).ATTR_VALUE_DATE;
634 end if;
635 end if;
636 l_data_found := 'Y';
637 exit;
638 end if;
639 end loop;
640 end if;
641 if l_data_found = 'Y' then
642 IF PO_LOG.d_stmt THEN
643 PO_LOG.stmt(d_module,d_progress,'l_attr_value from cache:', l_attr_value);
644 END IF;
645 return(l_attr_value);
646 end if;
647 end if;
648 */
649 --- last read values do not match fetch new values ----------------
650
651 d_progress := 050;
652 l_ext_table_name := po_uda_data_util.g_object_dff_tl(l_attr_group_Type).l_object_ext_b_table;
653 l_db_object_name := po_uda_data_util.g_object_dff_tl(l_attr_group_Type).l_object_name;
654
655 IF PO_LOG.d_stmt THEN
656 PO_LOG.stmt(d_module,d_progress,'l_ext_table_name:', l_ext_table_name);
657 PO_LOG.stmt(d_module,d_progress,'l_db_object_name', l_db_object_name);
658 END IF;
659
660 d_progress := 060;
661 begin
662 select pk1_column_name, pk2_column_name, pk3_column_name,
663 pk4_column_name, pk5_column_name
664 into l_pk1_col_name, l_pk2_col_name, l_pk3_col_name,
665 l_pk4_col_name, l_pk5_col_name
666 from fnd_objects
667 where obj_name = l_db_object_name
668 and rownum < 2;
669
670 IF PO_LOG.d_stmt THEN
671 PO_LOG.stmt(d_module,d_progress,'l_pk1_col_name:', l_pk1_col_name);
672 PO_LOG.stmt(d_module,d_progress,'l_pk2_col_name', l_pk2_col_name);
673 PO_LOG.stmt(d_module,d_progress,'l_pk3_col_name', l_pk3_col_name);
674 PO_LOG.stmt(d_module,d_progress,'l_pk4_col_name', l_pk4_col_name);
675 PO_LOG.stmt(d_module,d_progress,'l_pk5_col_name', l_pk5_col_name);
676 END IF;
677 exception
678 when no_data_found then
679 po_log.exc(d_module,d_progress,'At '|| d_progress ||': DB Object not found');
680 raise INVALID_DB_OBJ;
681 end;
682
683 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
684
685 if l_pk1_col_name is not null and pk1_value is not null then
686 l_pk_column_name_value_pairs.extend(1);
687 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.count) :=
688 EGO_COL_NAME_VALUE_PAIR_OBJ(l_pk1_col_name,pk1_value);
689 end if;
690
691 if l_pk2_col_name is not null and pk2_value is not null then
692 l_pk_column_name_value_pairs.extend(1);
693 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.count) :=
694 EGO_COL_NAME_VALUE_PAIR_OBJ(l_pk2_col_name,pk2_value);
695 end if;
696
697 if l_pk3_col_name is not null and pk3_value is not null then
698 l_pk_column_name_value_pairs.extend(1);
699 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.count) :=
700 EGO_COL_NAME_VALUE_PAIR_OBJ(l_pk3_col_name,pk3_value);
701 end if;
702
703 if l_pk4_col_name is not null and pk4_value is not null then
704 l_pk_column_name_value_pairs.extend(1);
705 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.count) :=
706 EGO_COL_NAME_VALUE_PAIR_OBJ(l_pk4_col_name,pk4_value);
707 end if;
708
709 if l_pk5_col_name is not null and pk5_value is not null then
710 l_pk_column_name_value_pairs.extend(1);
711 l_pk_column_name_value_pairs(l_pk_column_name_value_pairs.count) :=
712 EGO_COL_NAME_VALUE_PAIR_OBJ(l_pk5_col_name,pk5_value);
713 end if;
714
715
716 --- data level ----------------
717 d_progress := 070;
718 begin
719 select data_level_name
720 into l_data_level
721 from EGO_DATA_LEVEL_B
722 where attr_group_type = l_attr_group_type
723 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
724 end;
725
726 if PO_LOG.d_stmt then
727 PO_LOG.stmt(d_module,d_progress,'l_data_level_name:', l_data_level);
728 end if;
729
730 ---- attribute list --------------------------------
731 l_attr_list := 'addresstype' || ',' || l_attr_name;
732
733 IF PO_LOG.d_stmt THEN
734 PO_LOG.stmt(d_module,d_progress,'l_attr_list:', l_attr_list);
735 END IF;
736
737 -------- build the attr group request object ---
738 d_progress := 080;
739 l_attr_group_request_table.EXTEND(1);
740 l_attr_group_request_table(l_attr_group_request_table.COUNT) :=
741 ego_attr_group_request_obj(
742 l_attr_grp_id
743 ,l_appl_id
744 ,l_ag_type
745 ,l_ag_name
746 ,l_data_level
747 ,NULL
748 ,NULL
749 ,NULL
750 ,NULL
751 ,NULL
752 ,l_attr_list
753 );
754
755 -- get the data ---
756 d_progress := 090;
757 EGO_USER_ATTRS_DATA_PVT.Get_User_Attrs_Data (
758 p_api_version => 1.0
759 ,p_object_name => l_db_object_name
760 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
761 ,p_attr_group_request_table => l_attr_group_request_table
762 ,x_attributes_row_table => l_attributes_row_table
763 ,x_attributes_data_table => l_attributes_data_table
764 ,x_return_status => l_return_status
765 ,x_errorcode => l_errorcode
766 ,x_msg_count => l_msg_count
767 ,x_msg_data => l_msg_data);
768 if l_return_status = FND_API.G_RET_STS_SUCCESS then
769 l_attr_value := '';
770 for i in 1..l_attributes_data_table.count loop
771 if l_attributes_data_table(i).ATTR_NAME = 'addresstype'
772 and l_attributes_data_table(i).ATTR_VALUE_STR = p_address_type then
773 -- take value from the immediate next object.
774 if(p_mode = 'DISPLAY_VALUE') then
775 l_attr_value := l_attributes_data_table(i+1).ATTR_DISP_VALUE;
776 else
777 if(l_attributes_data_table(i+1).ATTR_VALUE_STR is not null) then
778 l_attr_value := l_attributes_data_table(i+1).ATTR_VALUE_STR;
779 elsif(l_attributes_data_table(i+1).ATTR_VALUE_NUM is not null) then
780 l_attr_value := l_attributes_data_table(i+1).ATTR_VALUE_NUM;
781 else
782 l_attr_value := l_attributes_data_table(i+1).ATTR_VALUE_DATE;
783 end if;
784 end if;
785 exit;
786 end if;
787 end loop;
788 else
789 l_attr_value := '';
790 end if;
791 IF PO_LOG.d_stmt THEN
792 PO_LOG.stmt(d_module,d_progress,'l_attr_value:', l_attr_value);
793 END IF;
794
795 ---- persist values for caching -------
796 g_pk1_value := pk1_value;
797 g_pk2_value := pk2_value;
798 g_pk3_value := pk3_value;
799 g_pk4_value := pk4_value;
800 g_pk5_value := pk5_value;
801 g_entity_code := l_attr_group_type;
802 g_attr_grp_id := l_attr_grp_id;
803 g_attr_id := l_attr_id;
804 g_mode := p_mode;
805
806 if l_attributes_data_table is not null then
807 g_attributes_data_table := EGO_USER_ATTR_DATA_TABLE();
808 g_attributes_data_table.extend(l_attributes_data_table.count);
809 for i in 1..l_attributes_data_table.count loop
810 g_attributes_data_table(i) := l_attributes_data_table(i);
811 end loop;
812 end if;
813
814 return l_attr_value;
815 exception
816 WHEN ATTR_GRP_REQD then
817 PO_LOG.exc(d_module,d_progress,
818 'Either attribute group id or attribute group internal name must be specified');
819 return '';
820 WHEN ATTR_GRP_DATA_NOT_FOUND then
821 PO_LOG.exc(d_module,d_progress,'Attribute group data not found.');
822 return '';
823 WHEN ATTR_REQD then
824 PO_LOG.exc(d_module,d_progress,
825 'Either attribute id or attribute internal name must be specified');
826 return '';
827 WHEN INVALID_DB_OBJ THEN
828 PO_LOG.exc(d_module,d_progress,'unable to determine the db object');
829 return '';
830 when others then
831 PO_LOG.exc(d_module,d_progress,
832 'Unexpected error: '|| SQLERRM || 'at '||d_progress);
833 return '';
834 end GET_ADDRESS_ATTR_VALUE;
835
836 function get_multi_attr_value (
837 p_template_id IN NUMBER,
838 p_entity_code IN VARCHAR2,
839 pk1_value IN NUMBER,
840 pk2_value IN NUMBER,
841 pk3_value IN NUMBER,
842 pk4_value IN NUMBER,
843 pk5_value IN NUMBER,
844 p_attr_grp_id IN NUMBER,
845 p_attr_grp_int_name IN VARCHAR2,
846 p_attr_id IN NUMBER,
847 p_attr_int_name IN VARCHAR2,
848 p_attr_grp_pk_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL,
849 p_mode IN VARCHAR2 DEFAULT 'INTERNAL_VALUE'
850 ) RETURN PO_TBL_VARCHAR4000
851 IS
852
853 d_api_name CONSTANT VARCHAR2(30) := 'get_multi_attr_value';
854 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
855 d_progress NUMBER;
856
857 l_multi_row_code VARCHAR2(3);
858 l_single_attr_value VARCHAR2(4000);
859 l_multi_attr_value PO_TBL_VARCHAR4000;
860 l_return_status VARCHAR2(3);
861 l_msg_data VARCHAR2(200);
862 l_attr_grp_pk_tbl PO_TBL_VARCHAR4000;
863
864 BEGIN
865
866 PO_UDA_PUB.GET_ATTR_VALUE
867 (
868 p_template_id => p_template_id
869 ,p_entity_code => p_entity_code
870 ,pk1_value => pk1_value
871 ,pk2_value => pk2_value
872 ,pk3_value => pk3_value
873 ,pk4_value => pk4_value
874 ,pk5_value => pk5_value
875 ,p_attr_grp_id => p_attr_grp_id
876 ,p_attr_grp_int_name => p_attr_grp_int_name
877 ,p_attr_id => p_attr_id
878 ,p_attr_int_name => p_attr_int_name
879 ,p_mode => p_mode
880 ,p_attr_grp_pk_tbl => p_attr_grp_pk_tbl
881 ,x_multi_row_code => l_multi_row_code
882 ,x_single_attr_value => l_single_attr_value
883 ,x_multi_attr_value => l_multi_attr_value
884 ,x_return_status => l_return_status
885 ,x_msg_data => l_msg_data
886 );
887
888 return l_multi_attr_value;
889
890 EXCEPTION
891 WHEN OTHERS THEN
892 PO_LOG.exc(d_module,d_progress,'Unexpected error: '|| SQLERRM || 'at '||d_progress);
893 END get_multi_attr_value;
894
895
896 function get_single_attr_value
897 (
898 p_template_id IN NUMBER DEFAULT NULL,
899 p_entity_code IN VARCHAR2 DEFAULT null,
900 pk1_value IN VARCHAR2 DEFAULT NULL,
901 pk2_value IN VARCHAR2 DEFAULT NULL,
902 pk3_value IN VARCHAR2 DEFAULT NULL,
903 pk4_value IN VARCHAR2 DEFAULT NULL,
904 pk5_value IN VARCHAR2 DEFAULT NULL,
905 p_attr_grp_id IN NUMBER DEFAULT NULL,
906 p_attr_grp_int_name IN VARCHAR2 DEFAULT NULL,
907 p_attr_id IN NUMBER DEFAULT NULL,
908 p_attr_int_name IN VARCHAR2 DEFAULT NULL,
909 p_mode IN VARCHAR2 DEFAULT 'INTERNAL_VALUE'
910 ) RETURN VARCHAR2
911 IS
912 d_api_name CONSTANT VARCHAR2(30) := 'get_single_attr_value';
913 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
914 d_progress NUMBER;
915
916 l_multi_row_code VARCHAR2(3);
917 l_single_attr_value VARCHAR2(4000);
918 l_multi_attr_value PO_TBL_VARCHAR4000;
919 l_return_status VARCHAR2(3);
920 l_msg_data VARCHAR2(200);
921
922 BEGIN
923
924 PO_UDA_PUB.GET_ATTR_VALUE
925 (
926 p_template_id => p_template_id
927 ,p_entity_code => p_entity_code
928 ,pk1_value => pk1_value
929 ,pk2_value => pk2_value
930 ,pk3_value => pk3_value
931 ,pk4_value => pk4_value
932 ,pk5_value => pk5_value
933 ,p_attr_grp_id => p_attr_grp_id
934 ,p_attr_grp_int_name => p_attr_grp_int_name
935 ,p_attr_id => p_attr_id
936 ,p_attr_int_name => p_attr_int_name
937 ,p_mode => p_mode
938 ,x_multi_row_code => l_multi_row_code
939 ,x_single_attr_value => l_single_attr_value
940 ,x_multi_attr_value => l_multi_attr_value
941 ,x_return_status => l_return_status
942 ,x_msg_data => l_msg_data
943 );
944
945 return l_single_attr_value;
946
947 EXCEPTION
948 WHEN OTHERS THEN
949 PO_LOG.exc(d_module,d_progress,'Unexpected error: '|| SQLERRM || 'at '||d_progress);
950 END get_single_attr_value;
951
952 END PO_UDA_PUB;