eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PON_DATATEMPLATE_PKG

Source


1 PACKAGE BODY PON_DATATEMPLATE_PKG AS
2 /* $Header: PON_DATATEMPLATE_PKG.plb 120.4 2011/02/17 09:23:13 nbingi ship $ */
3 
4 
5 FUNCTION GET_UDA_HEADER_XML RETURN XMLType IS
6 CURSOR C_ATTR_GRP IS
7 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP, AG.ATTR_GROUP_ID
8 ATTR_GROUP_ID,
9       AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME, AG.MULTI_ROW
10 MULTI_ROW
11 FROM EGO_FND_DSC_FLX_CTX_EXT AG, pon_auction_headers_all H,
12 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
13 WHERE H.auction_header_id = p_auction_header_id
14 AND H.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
15 AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
16 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
17 AND AG.ATTR_GROUP_ID  = POTU.ATTRIBUTE_GROUP_ID
18 and POTU.attribute_group_id in (select distinct attr_group_id from PON_AUCTION_HEADERS_EXT_B
19                                   where AUCTION_HEADER_ID = H.auction_header_id and attr_group_id = POTU.attribute_group_id)
20 
21 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
22 
23 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2)  IS
24 SELECT   EFDFCE.ATTR_ID                ,
25   EFDFCE.APPLICATION_COLUMN_NAME      ,
26   FCU.END_USER_COLUMN_NAME,
27   fcu.flex_value_set_id,
28      EFDFCE.data_type
29    FROM
30   EGO_FND_DF_COL_USGS_EXT EFDFCE
31        , FND_DESCR_FLEX_COLUMN_USAGES FCU
32   WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
33 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE  = P_ATTR_GROUP
34        AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE =
35 EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
36        AND FCU.DESCRIPTIVE_FLEXFIELD_NAME =
37 EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
38        AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
39        AND FCU.DISPLAY_FLAG <> 'H';
40 
41 L_SQL VARCHAR2(32767);
42 L_OP VARCHAR2(32767);
43 L_ATTR_GRP NUMBER;
44 L_CTR NUMBER;
45 L_O_CTR NUMBER;
46 l_uda_xml XMLTYPE;
47 
48 p_application_id  number := 201;
49   p_attr_internal_str_value varchar2(100);
50 
51   p_attr_internal_name  varchar2(100);
52   p_attr_group_type varchar2(200) :=  'PON_AUC_HDRS_EXT_ATTRS';
53   p_attr_group_int_name varchar2(200);
54 
55   p_object_name varchar2(200) := 'PON_AUCTION_HEADERS_EXT_B';
56 
57   p_pk1_column_name   varchar2(200) := 'AUCTION_HEADER_ID';
58   p_pk1_value  number := p_auction_header_id;
59 
60 
61 BEGIN
62 
63 L_O_CTR := 0;
64 FOR REC IN C_ATTR_GRP LOOP
65   IF REC.MULTI_ROW = 'Y' THEN
66     IF L_O_CTR > 0 THEN
67       L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
68 || '", XMLAgg(XMLForest(' ;
69     ELSE
70       L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '",
71 XMLAgg(XMLForest(';
72     END IF;
73   ELSE
74     IF L_O_CTR > 0 THEN
75       L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
76 || '", XMLForest(' ;
77     ELSE
78       L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
79     END IF;
80   END IF;
81   L_CTR := 0 ;
82   FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
83       IF L_CTR = 0 THEN
84         L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' ||
85 R.END_USER_COLUMN_NAME;
86       ELSE
87         L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' ||
88 R.END_USER_COLUMN_NAME;
89       END IF;
90 
91       if r.flex_value_set_id is not null then
92           if (r.data_type = 'C' or r.data_type = 'A' ) then
93             l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
94                          p_application_id || ',' || ' null,  ' ||
95                          R.APPLICATION_COLUMN_NAME || ', null , ''' ||
96                          R.END_USER_COLUMN_NAME || ''',''' ||
97                          p_attr_group_type || ''',''' ||
98                          REC.ATTR_GROUP || ''',' ||
99                          r.attr_id || ',''' ||
100                          p_object_name || ''',''' ||
101                          p_pk1_column_name || ''',' ||
102                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
103           elsif  r.data_type = 'N' then
104             l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
105                          p_application_id || ',' || ' null, null, ' ||
106                          R.APPLICATION_COLUMN_NAME || ', ''' ||
107                          R.END_USER_COLUMN_NAME || ''',''' ||
108                          p_attr_group_type || ''',''' ||
109                          REC.ATTR_GROUP || ''',' ||
110                          r.attr_id || ',''' ||
111                          p_object_name || ''',''' ||
112                          p_pk1_column_name || ''',' ||
113                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
114           elsif (r.data_type = 'X' or r.data_type = 'Y') then
115              l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
116                          p_application_id || ',' ||  R.APPLICATION_COLUMN_NAME ||
117                          ' , null, null, ''' ||
118                          R.END_USER_COLUMN_NAME || ''',''' ||
119                          p_attr_group_type || ''',''' ||
120                          REC.ATTR_GROUP || ''',' ||
121                          r.attr_id || ',''' ||
122                          p_object_name || ''',''' ||
123                          p_pk1_column_name || ''',' ||
124                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
125           end if;
126         end if;
127 
128 
129 
130       L_CTR := L_CTR + 1;
131   END LOOP;
132   IF REC.MULTI_ROW = 'Y' THEN
133     L_SQL := L_SQL || ')))';
134   ELSE
135     L_SQL := L_SQL || ' ))';
136   END IF;
137   L_SQL := L_SQL || ' from PON_AUCTION_HEADERS_EXT_VL where
138 AUCTION_HEADER_ID = ' || p_auction_header_id || ' and attr_group_id = '
139 || REC.ATTR_GROUP_ID || ')' ;
140 
141   L_O_CTR := L_O_CTR + 1;
142 END LOOP;
143 
144 l_op := ' select XMLConcat(' || l_sql || ') from dual';
145 execute immediate l_op into l_uda_xml;
146 
147 RETURN (l_uda_xml);
148 END;
149 
150 
151 
152 FUNCTION GET_UDA_LINES_XML(p_line_id NUMBER) RETURN XMLTYPE IS
153 CURSOR C_ATTR_GRP IS
154 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP, AG.ATTR_GROUP_ID
155 ATTR_GROUP_ID,
156       AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME, AG.MULTI_ROW
157 MULTI_ROW
158 FROM EGO_FND_DSC_FLX_CTX_EXT AG, pon_auction_item_prices_all L,
159 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
160 WHERE L.auction_header_id = p_auction_header_id
161 AND L.LINE_NUMBER = p_line_id
162 AND L.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
163 AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
164 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
165 AND AG.ATTR_GROUP_ID  = POTU.ATTRIBUTE_GROUP_ID
166 and potu.attribute_group_id in (select distinct attr_group_id from PON_AUCTION_ITEM_PRICES_EXT_B
167                                   where AUCTION_HEADER_ID = L.auction_header_id and LINE_NUMBER=L.LINE_NUMBER and  attr_group_id = potu.attribute_group_id)
168 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
169 
170 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2)  IS
171 SELECT   EFDFCE.ATTR_ID                ,
172   EFDFCE.APPLICATION_COLUMN_NAME      ,
173   FCU.END_USER_COLUMN_NAME ,
174      fcu.flex_value_set_id,
175      EFDFCE.data_type
176 
177    FROM
178   EGO_FND_DF_COL_USGS_EXT EFDFCE
179        , FND_DESCR_FLEX_COLUMN_USAGES FCU
180   WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
181 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE  = P_ATTR_GROUP
182        AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE =
183 EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
184        AND FCU.DESCRIPTIVE_FLEXFIELD_NAME =
185 EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
186        AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
187        AND FCU.DISPLAY_FLAG <> 'H';
188 
189 L_SQL VARCHAR2(32767);
190 L_OP VARCHAR2(32767);
191 L_ATTR_GRP NUMBER;
192 L_CTR NUMBER;
193 L_O_CTR NUMBER;
194 l_uda_xml XMLTYPE;
195 
196 p_application_id  number := 201;
197   p_attr_internal_str_value varchar2(100);
198 
199   p_attr_internal_name  varchar2(100);
200   p_attr_group_type varchar2(200) :=  'PON_AUC_PRICES_EXT_ATTRS';
201   p_attr_group_int_name varchar2(200);
202 
203   p_object_name varchar2(200) := 'PON_AUCTION_ITEM_PRICES_EXT_B';
204 
205   p_pk1_column_name   varchar2(200) := 'AUCTION_HEADER_ID';
206   p_pk1_value  number := p_auction_header_id;
207   p_pk2_column_name  varchar2(200) :=   'LINE_NUMBER';
208   p_pk2_value  number :=  p_line_id;
209 
210 BEGIN
211 
212 L_O_CTR := 0;
213 FOR REC IN C_ATTR_GRP LOOP
214   IF REC.MULTI_ROW = 'Y' THEN
215     IF L_O_CTR > 0 THEN
216       L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
217 || '", XMLAgg(XMLForest(' ;
218     ELSE
219       L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '",
220 XMLAgg(XMLForest(';
221     END IF;
222   ELSE
223     IF L_O_CTR > 0 THEN
224       L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
225 || '", XMLForest(' ;
226     ELSE
227       L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
228     END IF;
229   END IF;
230   L_CTR := 0 ;
231   FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
232       IF L_CTR = 0 THEN
233         L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' ||
234 R.END_USER_COLUMN_NAME;
235       ELSE
236         L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' ||
237 R.END_USER_COLUMN_NAME;
238       END IF;
239 
240       if r.flex_value_set_id is not null then
241           if (r.data_type = 'C' or r.data_type = 'A' ) then
242             l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
243                          p_application_id || ',' || ' null,  ' ||
244                          R.APPLICATION_COLUMN_NAME || ', null , ''' ||
245                          R.END_USER_COLUMN_NAME || ''',''' ||
246                          p_attr_group_type || ''',''' ||
247                          REC.ATTR_GROUP || ''',' ||
248                          r.attr_id || ',''' ||
249                          p_object_name || ''',''' ||
250                          p_pk1_column_name || ''',' ||
251                          p_pk1_value || ',''' ||
252                          p_pk2_column_name || ''',' ||
253                          p_pk2_value || ') as ' || R.end_user_column_name || '_DESC' ;
254           elsif  r.data_type = 'N' then
255             l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
256                          p_application_id || ',' || ' null, null, ' ||
257                          R.APPLICATION_COLUMN_NAME || ', ''' ||
258                          R.END_USER_COLUMN_NAME || ''',''' ||
259                          p_attr_group_type || ''',''' ||
260                          REC.ATTR_GROUP || ''',' ||
261                          r.attr_id || ',''' ||
262                          p_object_name || ''',''' ||
263                          p_pk1_column_name || ''',' ||
264                          p_pk1_value || ',''' ||
265                          p_pk2_column_name || ''',' ||
266                          p_pk2_value || ') as ' || R.end_user_column_name || '_DESC' ;
267           elsif (r.data_type = 'X' or r.data_type = 'Y') then
268              l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
269                          p_application_id || ',' ||  R.APPLICATION_COLUMN_NAME ||
270                          ' , null, null, ''' ||
271                          R.END_USER_COLUMN_NAME || ''',''' ||
272                          p_attr_group_type || ''',''' ||
273                          REC.ATTR_GROUP || ''',' ||
274                          r.attr_id || ',''' ||
275                          p_object_name || ''',''' ||
276                          p_pk1_column_name || ''',' ||
277                          p_pk1_value || ',''' ||
278                          p_pk2_column_name || ''',' ||
279                          p_pk2_value || ') as ' || R.end_user_column_name || '_DESC' ;
280           end if;
281         end if;
282 
283 
284       L_CTR := L_CTR + 1;
285   END LOOP;
286   IF REC.MULTI_ROW = 'Y' THEN
287     L_SQL := L_SQL || ')))';
288   ELSE
289     L_SQL := L_SQL || ' ))';
290   END IF;
291   L_SQL := L_SQL || ' from PON_AUCTION_ITEM_PRICES_EXT_VL where
292 AUCTION_HEADER_ID = '||p_auction_header_id||' and  LINE_NUMBER = ' ||
293 p_line_id || ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
294 
295   L_O_CTR := L_O_CTR + 1;
296 END LOOP;
297 
298 l_op := ' select XMLConcat(' || l_sql || ') from dual';
299 execute immediate l_op into l_uda_xml;
300 
301 RETURN (l_uda_xml);
302 END;
303 
304 FUNCTION get_addressxml_pvt(p_header_id number) return CLOB as
305   	l_where_sql varchar2(1000);
306   	l_addr_sql varchar2(1000);
307   	l_sql varchar2(4000);
308 	l_col_name varchar2(50);
309 	l_attr_grp_id number;
310   	clob_addr CLOB;
311   	c_addr SYS_REFCURSOR;
312   	l_address varchar2(4000);
313 	begin
314 		l_where_sql := ' from PON_AUCTION_HEADERS_EXT_VL where AUCTION_HEADER_ID = :1';
315 
316 		begin
317 			SELECT 	ag.attr_group_id,
318 							efdfce.application_column_name
319 			into   l_attr_grp_id, l_col_name
320 			FROM ego_fnd_dsc_flx_ctx_ext ag,
321 					 ego_fnd_df_col_usgs_ext efdfce,
322 					 fnd_descr_flex_column_usages fcu
323 			WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
324 			and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
325 			and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
326 			and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
327 			and fcu.application_column_name = efdfce.application_column_name
328 			and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_HDRS_EXT_ATTRS'
329 			and upper(ag.descriptive_flex_context_code) = upper('addresses')
330 			and upper(fcu.end_user_column_name) = upper('addressdtlsxml');
331 
332 			l_where_sql := l_where_sql || ' and attr_group_id = :2 ' ;
333 
334 			l_addr_sql := 'select ' || l_col_name || ' as address ' ;
335 
336 		exception
337 		when no_data_found then
338 			l_addr_sql := '';
339 		end;
340 
341 		if l_addr_sql is not null then
342 			l_sql := l_addr_sql || l_where_sql;
343 
344 			dbms_lob.createtemporary(clob_addr, true);
345 			dbms_lob.open(clob_addr, dbms_lob.lob_readwrite);
346 			open c_addr for l_sql using p_header_id,l_attr_grp_id;
347 			loop
348 				fetch c_addr into l_address;
349 				exit when c_addr%notfound;
350 				if l_address is not null then
351 				dbms_lob.writeappend(clob_addr, length(l_address), l_address);
352 				end if;
353 			end loop;
354 			dbms_lob.close(clob_addr);
355 		end if;
356 		return(clob_addr);
357 	end get_addressxml_pvt;
358 
359 function get_contactsxml_pvt(p_header_id number) return CLOB as
360 		l_cnt_sql varchar2(1000);
361 		l_where_sql varchar2(1000);
362 		l_sql varchar2(4000);
363 		l_cnt varchar2(4000);
364 		l_col_name varchar2(50);
365 		l_attr_grp_id number;
366 		c_cnt SYS_REFCURSOR;
367 		clob_cnt CLOB;
368 
369 	begin
370 		l_where_sql := ' from PON_AUCTION_HEADERS_EXT_VL where AUCTION_HEADER_ID = :1';
371 
372 		begin
373 			SELECT 	ag.attr_group_id,
374 							efdfce.application_column_name
375 			into   l_attr_grp_id, l_col_name
376 			FROM ego_fnd_dsc_flx_ctx_ext ag,
377 					 ego_fnd_df_col_usgs_ext efdfce,
378 					 fnd_descr_flex_column_usages fcu
379 			WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
380 			and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
381 			and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
382 			and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
383 			and fcu.application_column_name = efdfce.application_column_name
384 			and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_HDRS_EXT_ATTRS'
385 			and upper(ag.descriptive_flex_context_code) = upper('addresses')
386 			and upper(fcu.end_user_column_name) = upper('contactdtlsxml');
387 
388 			l_where_sql := l_where_sql || ' and attr_group_id = :2 ' ;
389 
390 			l_cnt_sql := 'select ' || l_col_name || ' as contacts ' ;
391 
392 		exception
393 		when no_data_found then
394 			l_cnt_sql := '';
395 		end;
396 
397 		if l_cnt_sql is not null then
398 			l_sql := l_cnt_sql || l_where_sql;
399 
400 			dbms_lob.createtemporary(clob_cnt, true);
401 			dbms_lob.open(clob_cnt , dbms_lob.lob_readwrite);
402 			open c_cnt for l_sql using p_header_id,l_attr_grp_id;
403 			loop
404 				fetch c_cnt into l_cnt;
405 				exit when c_cnt%notfound;
406 				if l_cnt is not null then
407 				dbms_lob.writeappend(clob_cnt, length(l_cnt), l_cnt);
408 				end if;
409 			end loop;
410 			dbms_lob.close(clob_cnt);
411 		end if;
412 		return(clob_cnt);
413 	end get_contactsxml_pvt;
414 
415 
416 FUNCTION get_addressxml return clob as
417 BEGIN
418  		return(get_addressxml_pvt(p_auction_header_id));
419 END;
420 
421 function get_contactxml return clob as
422 begin
423 		return(get_contactsxml_pvt(p_auction_header_id));
424 END;
425 
426 function get_amd_addressxml return clob as
427 BEGIN
428  		return(get_addressxml_pvt(p_auction_header_id));
429 END;
430 
431 function get_amd_contactxml return clob as
432 begin
433 		return(get_contactsxml_pvt(p_auction_header_id));
434 END;
435 
436 FUNCTION get_shipaddressxml(p_line_id number)  return CLOB as
437 
438   l_where_sql varchar2(1000);
439   l_addr_sql varchar2(1000);
440   l_sql varchar2(4000);
441   l_col_name varchar2(50);
442   l_attr_grp_id number;
443   clob_addr CLOB;
444   c_addr SYS_REFCURSOR;
445   l_address varchar2(4000);
446 
447   begin
448 
449 		l_where_sql := ' from PON_AUCTION_ITEM_PRICES_EXT_VL where AUCTION_HEADER_ID = :1 and LINE_NUMBER= :2';
450 
451 		begin
452 			SELECT 	ag.attr_group_id,
453 				efdfce.application_column_name
454 				into   l_attr_grp_id, l_col_name
455 
456 				FROM ego_fnd_dsc_flx_ctx_ext ag,
457 					 ego_fnd_df_col_usgs_ext efdfce,
458 					 fnd_descr_flex_column_usages fcu
459 
460 			WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
461 			and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
462 			and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
463 			and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
464 			and fcu.application_column_name = efdfce.application_column_name
465 			and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_PRICES_EXT_ATTRS'
466 			and upper(ag.descriptive_flex_context_code) = upper('SHIP_INFO')
467 			and upper(fcu.end_user_column_name) = upper('hiddShipAddXml');
468 
469 			l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
470 
471 			l_addr_sql := 'select ' || l_col_name || ' as ship_address ' ;
472 
473 		exception
474 		when no_data_found then
475 			l_addr_sql := '';
476 		end;
477 
478 		if l_addr_sql is not null then
479 		l_sql := l_addr_sql || l_where_sql;
480 
481 
482 		begin
483 		execute immediate l_sql into clob_addr using p_auction_header_id,p_line_id,l_attr_grp_id ;
484 		exception
485 		when others then
486 		select '' into clob_addr from dual;
487 		end;
488 		end if;
489 return(clob_addr);
490 end get_shipaddressxml;
491 
492 FUNCTION get_FPFobCodeDes return VARCHAR2 IS
493 fob_db_col VARCHAR2(100);
494 l_attr_group_id NUMBER(10);
495 l_fobsql VARCHAR2(500);
496 l_countdest NUMBER(10);
497 Begin
498 
499 select a.database_column, av.attr_group_id into fob_db_col, l_attr_group_id
500 from ego_attrs_v a, ego_attr_groups_v av
501 where a.attr_group_type like 'PON_AUC_PRICES_EXT_ATTRS'
502       and a.attr_group_name like 'SHIP_INFO'
503       and a.attr_name like 'FOB'
504       and av.attr_group_type = a.attr_group_type
505       and av.attr_group_name = a.attr_group_name;
506 
507 l_fobsql := 'select count(extension_id) from pon_auction_item_prices_ext_b where auction_header_id =:1 and attr_group_id = :2 and nvl(' || fob_db_col || ', ''N'') <> ''DEST''';
508 
509 execute immediate l_fobsql into l_countdest using p_auction_header_id,l_attr_group_id;
510 
511 if (l_countdest = 0) then
512   return 'Destination';
513 else
514   return 'See Schedule';
515 end if;
516 
517 exception
518 when others then
519 return NULL;
520 
521 End get_FPFobCodeDes;
522 
523 END;
524