[Home] [Help]
PACKAGE BODY: APPS.PO_DATATEMPLATE_PKG
Source
1 PACKAGE BODY PO_DATATEMPLATE_PKG AS
2 /* $Header: PO_DATATEMPLATE_PKG.plb 120.14.12020000.4 2013/03/22 06:40:48 vpeddi ship $ */
3
4 FUNCTION LineLocation_Filter(p_type VARCHAR2, complex_work_po VARCHAR2) RETURN Boolean IS
5 Begin
6 IF (p_type <> 'ADVANCE' AND p_type <> 'DELIVERY' AND complex_work_po = 'N') THEN
7 RETURN (TRUE);
8 End If;
9 RETURN(FALSE);
10 END LineLocation_Filter;
11
12 FUNCTION LineDelivery_FILTER(p_type VARCHAR2, complex_work_po VARCHAR2) RETURN Boolean IS
13 Begin
14 IF (p_type = 'DELIVERY' AND complex_work_po = 'Y') THEN
15 RETURN (TRUE);
16 End If;
17 RETURN(FALSE);
18 END LineDelivery_Filter;
19
20 FUNCTION get_location_id(t_orgid NUMBER) RETURN NUMBER IS
21 l_legal_entity_id NUMBER;
22 x_return_status VARCHAR2(500);
23 x_msg_count NUMBER;
24 x_msg_data VARCHAR2(500);
25 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
26 l_location_id NUMBER;
27 Begin
28 l_legal_entity_id := XLE_UTILITIES_GRP.GET_DefaultLegalContext_OU(t_orgid);
29 XLE_UTILITIES_GRP.Get_LegalEntity_Info(x_return_status, x_msg_count, x_msg_data, null, l_legal_entity_id, x_legalentity_info);
30 l_location_id := x_legalentity_info.location_id;
31
32 RETURN (l_location_id);
33 END get_location_id;
34
35 FUNCTION get_location_name(t_orgid NUMBER) RETURN VARCHAR2 IS
36 l_legal_entity_id NUMBER;
37 x_return_status VARCHAR2(500);
38 x_msg_count NUMBER;
39 x_msg_data VARCHAR2(500);
40 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
41 l_location_name VARCHAR2(500);
42 Begin
43 l_legal_entity_id := XLE_UTILITIES_GRP.GET_DefaultLegalContext_OU(t_orgid);
44 XLE_UTILITIES_GRP.Get_LegalEntity_Info(x_return_status, x_msg_count, x_msg_data, null, l_legal_entity_id, x_legalentity_info);
45 l_location_name := x_legalentity_info.name;
46
47 RETURN (l_location_name);
48 END get_location_name;
49
50 FUNCTION get_header_amount_ordered(p_header_id NUMBER, p_draft_id NUMBER) RETURN NUMBER IS
51 order_amount NUMBER;
52 Begin
53 order_amount := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(PO_CORE_S.g_doc_level_HEADER, p_header_id,PO_DOCUMENT_TOTALS_PVT.g_data_source_TRANSACTION , NULL, p_draft_id);
54
55 RETURN (order_amount);
56 END get_header_amount_ordered;
57
58 FUNCTION get_line_amount_ordered(p_line_id NUMBER, p_draft_id NUMBER) RETURN NUMBER IS
59 order_amount NUMBER;
60 Begin
61 order_amount := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(PO_CORE_S.g_doc_level_LINE, p_line_id,PO_DOCUMENT_TOTALS_PVT.g_data_source_TRANSACTION , NULL, p_draft_id);
62
63 RETURN (order_amount);
64 END get_line_amount_ordered;
65
66 FUNCTION get_without_opt_amount_ordered(p_header_id NUMBER, p_draft_id NUMBER) RETURN NUMBER IS
67 order_amount NUMBER;
68 Begin
69 order_amount := PO_DOCUMENT_TOTALS_PVT.getAmountOrderedExclOptions(PO_CORE_S.g_doc_level_HEADER, p_header_id, PO_DOCUMENT_TOTALS_PVT.g_data_source_TRANSACTION , NULL, p_draft_id);
70
71 RETURN (order_amount);
72 END get_without_opt_amount_ordered;
73
74 FUNCTION get_bilateral_indicator(p_header_id IN NUMBER, p_draft_id IN NUMBER, p_auth_status IN VARCHAR2, p_bil_indicator IN VARCHAR2)
75 RETURN VARCHAR2 IS
76 bil_indicator VARCHAR2(50);
77
78 Begin
79 bil_indicator := 'N';
80 IF (p_auth_status <> 'INCOMPLETE' AND p_auth_status <> 'IN PROCESS') THEN
81
82 Begin
83 SELECT 'S' INTO bil_indicator FROM PO_ACCEPTANCES WHERE po_header_id = p_header_id AND draft_id = p_draft_id
84 AND accepting_party = 'S' AND accepted_flag = 'Y' AND signature_flag = 'Y';
85
86 Exception
87 when no_data_found then
88 BEGIN
89 SELECT 'B' INTO bil_indicator FROM PO_ACCEPTANCES WHERE po_header_id = p_header_id AND draft_id = p_draft_id
90 AND accepting_party = 'B' AND accepted_flag = 'Y' AND signature_flag = 'Y';
91
92 Exception
93 when no_data_found then
94 bil_indicator := 'N';
95 when others then
96 bil_indicator := 'N';
97 END;
98 END;
99
100 END IF;
101
102 RETURN (bil_indicator);
103 END get_bilateral_indicator;
104
105 FUNCTION GET_LINE_ATTRIBUTE_VAL_XML(p_categoryId NUMBER, p_lineId NUMBER)
106 RETURN XMLType IS
107 CURSOR C_ATTR_KEY IS
108 SELECT ICA.ATTRIBUTE_ID, ICA.RT_CATEGORY_ID, REPLACE(ICA.KEY, ' ', '_') KEY, ICA.STORED_IN_TABLE, ICA.STORED_IN_COLUMN
109 from icx_cat_agreement_attrs_v ICA where
110 (ICA.rt_category_id = p_categoryId
111 or (ICA.rt_category_id =0 and ICA.attribute_id> 1000)
112 or ICA.key in ('LONG_DESCRIPTION','SUPPLIER_URL', 'MANUFACTURER', 'MANUFACTURER_URL', 'MANUFACTURER_PART_NUM',
113 'ATTACHMENT_URL', 'UNSPSC', 'PICTURE', 'THUMBNAIL_IMAGE', 'LEAD_TIME', 'AVAILABILITY', 'COMMENTS', 'ALIAS'
114 )) and language = userenv('lang');
115
116 S_SQL VARCHAR2(32767);
117 T_CTR NUMBER;
118 po_attr_uda_xml XMLTYPE;
119 BEGIN
120
121 T_CTR := 0 ;
122 FOR REC IN C_ATTR_KEY LOOP
123 IF T_CTR = 0 THEN
124 S_SQL := '(select XMLElement("PO_ATTRIBUTE_VALUES", XMLForest(' || REC.STORED_IN_COLUMN || ' as ' || REC.KEY;
125 ELSE
126 S_SQL := S_SQL || ',' || REC.STORED_IN_COLUMN || ' as ' || REC.KEY;
127 END IF;
128 T_CTR := T_CTR + 1;
129 END LOOP;
130 S_SQL := S_SQL || ' ))';
131
132 S_SQL := S_SQL || ' from PO_ATTRIBUTE_VALUES PAV, PO_ATTRIBUTE_VALUES_TLP PAVT
133 where PAV.po_line_id = :1 AND PAVT.po_line_id = PAV.po_line_id and PAVT.language =:2)' ;
134
135 execute immediate s_sql into po_attr_uda_xml USING p_lineId, USERENV('lang') ;
136 RETURN (po_attr_uda_xml);
137 END GET_LINE_ATTRIBUTE_VAL_XML;
138
139 FUNCTION GET_LINE_ATTR_VAL_DRAFT_XML(p_categoryId NUMBER, p_lineId NUMBER, p_draftId NUMBER)
140 RETURN XMLType IS
141 CURSOR C_ATTR_KEY IS
142 SELECT ICA.ATTRIBUTE_ID, ICA.RT_CATEGORY_ID, REPLACE(ICA.KEY, ' ', '_') KEY, ICA.STORED_IN_TABLE, ICA.STORED_IN_COLUMN
143 from icx_cat_agreement_attrs_v ICA where
144 (ICA.rt_category_id = p_categoryId
145 or (ICA.rt_category_id =0 and ICA.attribute_id> 1000)
146 or ICA.key in ('LONG_DESCRIPTION','SUPPLIER_URL', 'MANUFACTURER', 'MANUFACTURER_URL', 'MANUFACTURER_PART_NUM',
147 'ATTACHMENT_URL', 'UNSPSC', 'PICTURE', 'THUMBNAIL_IMAGE', 'LEAD_TIME', 'AVAILABILITY', 'COMMENTS', 'ALIAS'
148 )) and language = userenv('lang');
149
150 S_SQL VARCHAR2(32767);
151 T_CTR NUMBER;
152 po_attr_uda_xml XMLTYPE;
153 BEGIN
154
155 T_CTR := 0 ;
156 FOR REC IN C_ATTR_KEY LOOP
157 IF T_CTR = 0 THEN
158 S_SQL := '(select XMLElement("PO_ATTRIBUTE_VALUES", XMLForest(' || REC.STORED_IN_COLUMN || ' as ' || REC.KEY;
159 ELSE
160 S_SQL := S_SQL || ',' || REC.STORED_IN_COLUMN || ' as ' || REC.KEY;
161 END IF;
162 T_CTR := T_CTR + 1;
163 END LOOP;
164 S_SQL := S_SQL || ' ))';
165
166 S_SQL := S_SQL || ' from PO_ATTRIBUTE_VALUES_DRAFT PAV, PO_ATTRIBUTE_VALUES_TLP_DRAFT PAVT
167 where PAV.po_line_id = :1 AND PAVT.po_line_id = PAV.po_line_id and PAVT.language =:2 and PAV.draft_id = :3 and PAVT.draft_id = PAV.draft_id)' ;
168
169 execute immediate s_sql into po_attr_uda_xml USING p_lineId, USERENV('lang'), p_draftId ;
170 RETURN (po_attr_uda_xml);
171 END GET_LINE_ATTR_VAL_DRAFT_XML;
172
173 function get_uda_attr_desc_sql(p_doc_type varchar2, p_pk1_value number,
174 p_pk2_value number,
175 p_data_type varchar2,
176 p_appl_col_name varchar2,
177 p_end_user_col_name varchar2,
178 p_attr_group varchar2, p_attr_id number)
179 return varchar2 is
180
181 p_application_id number := 201;
182 p_attr_group_type varchar2(200);
183 p_object_name varchar2(200);
184 p_pk1_column_name varchar2(200);
185 p_pk2_column_name varchar2(200) := 'DRAFT_ID';
186 l_sql varchar2(1000);
187 begin
188 if p_doc_type = 'SHIPMENT' then
189 p_attr_group_type := 'PO_SHIPMENTS_EXT_ATTRS';
190 p_object_name := 'PO_LINE_LOCATIONS_ALL_EXT_B';
191 p_pk1_column_name := 'LINE_LOCATION_ID';
192 elsif p_doc_type = 'LINES' then
193 p_attr_group_type := 'PO_LINE_EXT_ATTRS';
194 p_object_name := 'PO_LINES_ALL_EXT_B';
195 p_pk1_column_name := 'PO_LINE_ID';
196 elsif p_doc_type = 'HEADER' then
197 p_attr_group_type := 'PO_HEADER_EXT_ATTRS';
198 p_object_name := 'PO_HEADERS_ALL_EXT_B';
199 p_pk1_column_name := 'PO_HEADER_ID';
200 end if;
201
202 if (p_data_type = 'C' or p_data_type = 'A' ) then
203 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
204 p_application_id || ',' || ' null, ' ||
205 p_appl_col_name|| ', null , ''' ||
206 p_end_user_col_name|| ''',''' ||
207 p_attr_group_type || ''',''' ||
208 P_ATTR_GROUP|| ''',' ||
209 p_attr_id || ',''' ||
210 p_object_name || ''',''' ||
211 p_pk1_column_name || ''',' ||
212 p_pk1_value || ',''' ||
213 p_pk2_column_name || ''',' ||
214 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC' ;
215 elsif p_data_type = 'N' then
216 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
217 p_application_id || ',' || ' null, null, ' ||
218 p_appl_col_name|| ', ''' ||
219 p_end_user_col_name|| ''',''' ||
220 p_attr_group_type || ''',''' ||
221 p_attr_group || ''',' ||
222 p_attr_id || ',''' ||
223 p_object_name || ''',''' ||
224 p_pk1_column_name || ''',' ||
225 p_pk1_value || ',''' ||
226 p_pk2_column_name || ''',' ||
227 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC' ;
228 elsif (p_data_type = 'X' or p_data_type = 'Y') then
229 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
230 p_application_id || ',' || p_appl_col_name ||
231 ' , null, null, ''' ||
232 p_end_user_col_name|| ''',''' ||
233 p_attr_group_type || ''',''' ||
234 p_attr_group || ''',' ||
235 p_attr_id || ',''' ||
236 p_object_name || ''',''' ||
237 p_pk1_column_name || ''',' ||
238 p_pk1_value || ',''' ||
239 p_pk2_column_name || ''',' ||
240 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC ' ;
241 end if;
242 return(l_sql);
243 end get_uda_attr_desc_sql;
244
245 FUNCTION GET_UDA_HEADER_XML_PVT(p_headerId number, p_draft_id number)
246 RETURN XMLType IS
247 CURSOR C_ATTR_GRP IS
248 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
249 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
250 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
251 AG.MULTI_ROW MULTI_ROW
252 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
253 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
254 WHERE POT.TEMPLATE_ID = POTU.TEMPLATE_ID
255 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
256 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
257 AND potu.attribute_group_id in (select distinct attr_group_id
258 from po_headers_all_ext_b
259 where po_header_id = p_headerId
260 and draft_id = p_draft_id and uda_template_id = potu.template_id
261 and attr_group_id = potu.attribute_group_id)
262 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
263
264 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
265 SELECT EFDFCE.ATTR_ID ,
266 EFDFCE.APPLICATION_COLUMN_NAME ,
267 FCU.END_USER_COLUMN_NAME,
268 fcu.flex_value_set_id,
269 EFDFCE.data_type
270 FROM
271 EGO_FND_DF_COL_USGS_EXT EFDFCE,
272 FND_DESCR_FLEX_COLUMN_USAGES FCU
273 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
274 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
275 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
276 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
277 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
278 AND FCU.DISPLAY_FLAG <> 'H';
279
280 L_SQL VARCHAR2(32767);
281 L_OP VARCHAR2(32767);
282 L_ATTR_GRP NUMBER;
283 L_CTR NUMBER;
284 L_O_CTR NUMBER;
285 l_uda_xml XMLTYPE;
286 BEGIN
287
288 L_O_CTR := 0;
289 FOR REC IN C_ATTR_GRP LOOP
290 IF REC.MULTI_ROW = 'Y' THEN
291 IF L_O_CTR > 0 THEN
292 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
293 || '", XMLAgg(XMLForest(' ;
294 ELSE
295 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLAgg(XMLForest(';
296 END IF;
297 ELSE
298 IF L_O_CTR > 0 THEN
299 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
300 || '", XMLForest(' ;
301 ELSE
302 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
303 END IF;
304 END IF;
305 L_CTR := 0 ;
306 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
307 IF L_CTR = 0 THEN
308 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
309 ELSE
310 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
311 END IF;
312 if r.flex_value_set_id is not null then
313 if (r.data_type = 'C' or r.data_type = 'A' ) then
314 l_sql := l_sql || ',' || get_uda_attr_desc_sql('HEADER',
315 p_headerId,
316 p_draft_Id,
317 r.data_type,
318 r.application_column_name,
319 r.end_user_column_name,
320 rec.attr_group,
321 r.ATTR_ID );
322 end if;
323 end if;
324 L_CTR := L_CTR + 1;
325 END LOOP;
326 IF REC.MULTI_ROW = 'Y' THEN
327 L_SQL := L_SQL || ')))';
328 ELSE
329 L_SQL := L_SQL || ' ))';
330 END IF;
331 L_SQL := L_SQL || ' from po_headers_all_ext_vl where po_header_id = ' ||
332 p_headerId || ' AND draft_id = ' || p_draft_id ||
333 ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
334 L_O_CTR := L_O_CTR + 1;
335 END LOOP;
336
337 l_op := ' select XMLConcat(' || l_sql || ') from dual';
338 execute immediate l_op into l_uda_xml;
339 RETURN (l_uda_xml);
340 END GET_UDA_HEADER_XML_PVT;
341
342 function get_uda_llxml_pvt(p_line_location_id number, p_draft_id number)
343 return XMLType is
344 CURSOR C_ATTR_GRP IS
345 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
346 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
347 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
348 AG.MULTI_ROW MULTI_ROW
349 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
350 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
351 WHERE POT.TEMPLATE_ID = POTU.TEMPLATE_ID
352 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
353 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
354 AND potu.attribute_group_id in (select distinct attr_group_id
355 from po_line_locations_all_ext_b
356 where line_location_id = p_line_location_id
357 and draft_id = p_draft_Id and uda_template_id = potu.template_id
358 and attr_group_id = potu.attribute_group_id)
359 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
360
361 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
362 SELECT EFDFCE.ATTR_ID ,
363 EFDFCE.APPLICATION_COLUMN_NAME ,
364 FCU.END_USER_COLUMN_NAME,
365 fcu.flex_value_set_id,
366 EFDFCE.data_type
367 FROM
368 EGO_FND_DF_COL_USGS_EXT EFDFCE
369 , FND_DESCR_FLEX_COLUMN_USAGES FCU
370 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
371 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
372 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
373 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
374 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
375 AND FCU.DISPLAY_FLAG <> 'H';
376
377 L_SQL VARCHAR2(32767);
378 L_OP VARCHAR2(32767);
379 L_ATTR_GRP NUMBER;
380 L_CTR NUMBER;
381 L_O_CTR NUMBER;
382 l_uda_xml XMLTYPE;
383
384 BEGIN
385
386 L_O_CTR := 0;
387 FOR REC IN C_ATTR_GRP LOOP
388 IF REC.MULTI_ROW = 'Y' THEN
389 IF L_O_CTR > 0 THEN
390 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
391 || '", XMLAgg(XMLForest(' ;
392 ELSE
393 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP
394 || '", XMLAgg(XMLForest(';
395 END IF;
396 ELSE
397 IF L_O_CTR > 0 THEN
398 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
399 || '", XMLForest(' ;
400 ELSE
401 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
402 END IF;
403 END IF;
404 L_CTR := 0 ;
405 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
406 IF L_CTR = 0 THEN
407 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as '
408 || R.END_USER_COLUMN_NAME;
409 ELSE
410 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as '
411 || R.END_USER_COLUMN_NAME;
412 END IF;
413
414 if r.flex_value_set_id is not null then
415 l_sql := l_sql || ',' || get_uda_attr_desc_sql('SHIPMENT',
416 p_LINE_LOCATION_ID,
417 p_draft_Id,
418 r.data_type,
419 r.application_column_name,
420 r.end_user_column_name,
421 rec.attr_group,
422 r.ATTR_ID );
423 end if;
424 L_CTR := L_CTR + 1;
425 END LOOP;
426 IF REC.MULTI_ROW = 'Y' THEN
427 L_SQL := L_SQL || ')))';
428 ELSE
429 L_SQL := L_SQL || ' ))';
430 END IF;
431 L_SQL := L_SQL || ' from po_line_locations_all_ext_vl where line_location_id = ' ||
432 p_line_location_id || ' AND draft_id = ' || p_draft_id
433 || ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
434 L_O_CTR := L_O_CTR + 1;
435 END LOOP;
436
437 l_op := ' select XMLConcat(' || l_sql || ') from dual';
438 execute immediate l_op into l_uda_xml;
439 RETURN (l_uda_xml);
440
441 end get_uda_llxml_pvt;
442
443 FUNCTION get_uda_line_xml_pvt(p_po_line_id NUMBER, p_draft_id number)
444 RETURN XMLType IS
445 CURSOR C_ATTR_GRP IS
446 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
447 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
448 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
449 AG.MULTI_ROW MULTI_ROW
450 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
451 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
452 WHERE POT.TEMPLATE_ID = POTU.TEMPLATE_ID
453 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
454 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
455 AND potu.attribute_group_id in (select distinct attr_group_id
456 from po_lines_all_ext_b
457 where po_line_id = p_po_line_id
458 and draft_id = p_draft_Id
459 and attr_group_id = potu.attribute_group_id and uda_template_id = potu.template_id
460 and pk1_value IS NULL)
461 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
462
463 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
464 SELECT EFDFCE.ATTR_ID ,
465 EFDFCE.APPLICATION_COLUMN_NAME ,
466 FCU.END_USER_COLUMN_NAME,
467 fcu.flex_value_set_id,
468 EFDFCE.data_type
469 FROM
470 EGO_FND_DF_COL_USGS_EXT EFDFCE ,
471 FND_DESCR_FLEX_COLUMN_USAGES FCU
472 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
473 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
474 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
475 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
476 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
477 AND FCU.DISPLAY_FLAG <> 'H';
478
479 L_SQL VARCHAR2(32767);
480 L_OP VARCHAR2(32767);
481 L_ATTR_GRP NUMBER;
482 L_CTR NUMBER;
483 L_O_CTR NUMBER;
484 l_uda_xml XMLTYPE;
485 BEGIN
486
487 L_O_CTR := 0;
488 FOR REC IN C_ATTR_GRP LOOP
489 IF REC.MULTI_ROW = 'Y' THEN
490 IF L_O_CTR > 0 THEN
491 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
492 || '", XMLAgg(XMLForest(' ;
493 ELSE
494 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLAgg(XMLForest(';
495 END IF;
496 ELSE
497 IF L_O_CTR > 0 THEN
498 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
499 || '", XMLForest(' ;
500 ELSE
501 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
502 END IF;
503 END IF;
504 L_CTR := 0 ;
505 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
506 IF L_CTR = 0 THEN
507 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as '
508 || R.END_USER_COLUMN_NAME;
509 ELSE
510 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as '
511 || R.END_USER_COLUMN_NAME;
512 END IF;
513
514 if r.flex_value_set_id is not null then
515 l_sql := l_sql || ',' || get_uda_attr_desc_sql('LINES',
516 p_po_line_id,
517 p_draft_Id,
518 r.data_type,
519 r.application_column_name,
520 r.end_user_column_name,
521 rec.attr_group,
522 r.ATTR_ID );
523 end if;
524 L_CTR := L_CTR + 1;
525 END LOOP;
526 IF REC.MULTI_ROW = 'Y' THEN
527 L_SQL := L_SQL || ')))';
528 ELSE
529 L_SQL := L_SQL || ' ))';
530 END IF;
531 L_SQL := L_SQL || ' from po_lines_all_ext_vl where po_line_id = ' ||
532 p_po_line_id || ' AND draft_id = ' || p_draft_Id ||
533 ' AND pk1_value IS NULL and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
534 L_O_CTR := L_O_CTR + 1;
535 END LOOP;
536
537 l_op := ' select XMLConcat(' || l_sql || ') from dual';
538 execute immediate l_op into l_uda_xml;
539 RETURN (l_uda_xml);
540 end get_uda_line_xml_pvt;
541
542 FUNCTION GET_UDA_HEADER_XML RETURN XMLType IS
543 begin
544 return(get_uda_header_xml_pvt(pt_headerId, -1));
545 END GET_UDA_HEADER_XML;
546
547 FUNCTION GET_UDA_HEADER_DRAFTS_XML(p_headerid NUMBER) RETURN XMLType IS
548 begin
549 return(get_uda_header_xml_pvt(p_headerid, draftId));
550 END GET_UDA_HEADER_DRAFTS_XML;
551
552 FUNCTION GET_UDA_LINES_XML(p_line_id number) RETURN XMLType IS
553 begin
554 return(get_uda_line_xml_pvt(p_line_id, -1));
555 END get_uda_lines_xml;
556
557 FUNCTION GET_UDA_LINES_DRAFTS_XML(p_po_line_id NUMBER) RETURN XMLType IS
558 begin
559 return(get_uda_line_xml_pvt(p_po_line_id, draftId));
560 END GET_UDA_LINES_DRAFTS_XML;
561
562 FUNCTION GET_UDA_Line_Locations_XML(p_line_location_id NUMBER)
563 RETURN XMLType IS
564 begin
565 return(get_uda_llxml_pvt(p_line_location_id, -1));
566 END GET_UDA_Line_Locations_XML;
567
568 FUNCTION GET_UDA_LL_DRAFTS_XML(p_line_location_id NUMBER) RETURN XMLType IS
569 begin
570 return(get_uda_llxml_pvt(p_line_location_id, draftId));
571 END GET_UDA_LL_DRAFTS_XML;
572
573 function get_addressxml_pvt(p_header_id number, p_draft_id number) return CLOB as
574 l_where_sql varchar2(1000);
575 l_addr_sql varchar2(1000);
576 l_sql varchar2(4000);
577 l_col_name varchar2(50);
578 l_attr_grp_id number;
579 clob_addr CLOB;
580 c_addr SYS_REFCURSOR;
581 l_address varchar2(4000);
582 begin
583
584 clob_addr := PO_DT_CUSTOM_PKG.get_address_xml(p_header_id, p_draft_id);
585
586 IF clob_addr IS NULL THEN
587 l_where_sql := ' from po_headers_all_ext_vl where po_header_id = :1 and draft_id = :2';
588
589 begin
590 SELECT ag.attr_group_id,
591 efdfce.application_column_name
592 into l_attr_grp_id, l_col_name
593 FROM ego_fnd_dsc_flx_ctx_ext ag,
594 ego_fnd_df_col_usgs_ext efdfce,
595 fnd_descr_flex_column_usages fcu
596 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
597 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
598 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
599 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
600 and fcu.application_column_name = efdfce.application_column_name
601 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
602 and upper(ag.descriptive_flex_context_code) = upper('addresses')
603 and upper(fcu.end_user_column_name) = upper('addressdtlsxml');
604
605 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
606
607 l_addr_sql := 'select ' || l_col_name || ' as address ' ;
608
609 exception
610 when no_data_found then
611 l_addr_sql := '';
612 end;
613
614 if l_addr_sql is not null then
615 l_sql := l_addr_sql || l_where_sql;
616
617 dbms_lob.createtemporary(clob_addr, true);
618 dbms_lob.open(clob_addr, dbms_lob.lob_readwrite);
619 open c_addr for l_sql using p_header_id, p_draft_id, l_attr_grp_id;
620 loop
621 fetch c_addr into l_address;
622 exit when c_addr%notfound;
623 if l_address is not null then
624 dbms_lob.writeappend(clob_addr, length(l_address), l_address);
625 end if;
626 end loop;
627 dbms_lob.close(clob_addr);
628 end if;
629 end if;
630 return(clob_addr);
631 end get_addressxml_pvt;
632
633
634 function get_contactsxml_pvt(p_header_id number , p_draft_id number) return CLOB as
635 l_cnt_sql varchar2(1000);
636 l_where_sql varchar2(1000);
637 l_sql varchar2(4000);
638 l_cnt varchar2(4000);
639 l_col_name varchar2(50);
640 l_attr_grp_id number;
641 c_cnt SYS_REFCURSOR;
642 clob_cnt CLOB;
643
644 begin
645 l_where_sql := ' from po_headers_all_ext_vl where po_header_id = :1 and draft_id = :2';
646
647 begin
648 SELECT ag.attr_group_id,
649 efdfce.application_column_name
650 into l_attr_grp_id, l_col_name
651 FROM ego_fnd_dsc_flx_ctx_ext ag,
652 ego_fnd_df_col_usgs_ext efdfce,
653 fnd_descr_flex_column_usages fcu
654 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
655 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
656 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
657 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
658 and fcu.application_column_name = efdfce.application_column_name
659 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
660 and upper(ag.descriptive_flex_context_code) = upper('addresses')
661 and upper(fcu.end_user_column_name) = upper('contactdtlsxml');
662
663 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
664
665 l_cnt_sql := 'select ' || l_col_name || ' as contacts ' ;
666
667 exception
668 when no_data_found then
669 l_cnt_sql := '';
670 end;
671
672 if l_cnt_sql is not null then
673 l_sql := l_cnt_sql || l_where_sql;
674
675 dbms_lob.createtemporary(clob_cnt, true);
676 dbms_lob.open(clob_cnt , dbms_lob.lob_readwrite);
677 open c_cnt for l_sql using p_header_id, p_draft_id, l_attr_grp_id;
678 loop
679 fetch c_cnt into l_cnt;
680 exit when c_cnt%notfound;
681 if l_cnt is not null then
682 dbms_lob.writeappend(clob_cnt, length(l_cnt), l_cnt);
683 end if;
684 end loop;
685 dbms_lob.close(clob_cnt);
686 end if;
687 return(clob_cnt);
688 end get_contactsxml_pvt;
689
690 FUNCTION get_addressxml RETURN CLOB AS
691 BEGIN
692 return(get_addressxml_pvt(pt_headerId, -1));
693 END get_addressxml;
694
695 FUNCTION get_addressxml(p_headerId NUMBER) RETURN CLOB AS
696 BEGIN
697 return(get_addressxml_pvt(p_headerId, -1));
698 END get_addressxml;
699
700 FUNCTION get_contactsxml RETURN CLOB AS
701 begin
702 return(get_contactsxml_pvt(pt_headerId, -1));
703 END get_contactsxml;
704
705 FUNCTION get_drafts_addressxml(p_headerId NUMBER) RETURN CLOB AS
706 begin
707 return(get_addressxml_pvt(p_headerId, draftId));
708 END get_drafts_addressxml;
709
710 FUNCTION get_drafts_contactsxml(p_headerId NUMBER) RETURN CLOB AS
711 begin
712 return(get_contactsxml_pvt(p_headerId, draftId));
713 END get_drafts_contactsxml;
714
715 FUNCTION get_shipaddressxml_pvt(p_line_location_id number, p_draft_id number) return CLOB as
716
717 l_where_sql varchar2(1000);
718 l_addr_sql varchar2(1000);
719 l_sql varchar2(4000);
720 l_col_name varchar2(50);
721 l_attr_grp_id number;
722 clob_addr CLOB;
723 c_addr SYS_REFCURSOR;
724 l_address varchar2(4000);
725
726 begin
727
728 l_where_sql := ' from PO_LINE_LOCATIONS_ALL_EXT_VL where line_location_id = :1 and draft_id = :2';
729
730 begin
731 SELECT ag.attr_group_id,
732 efdfce.application_column_name
733 into l_attr_grp_id, l_col_name
734
735 FROM ego_fnd_dsc_flx_ctx_ext ag,
736 ego_fnd_df_col_usgs_ext efdfce,
737 fnd_descr_flex_column_usages fcu
738
739 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
740 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
741 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
742 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
743 and fcu.application_column_name = efdfce.application_column_name
744 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_SHIPMENTS_EXT_ATTRS'
745 and upper(ag.descriptive_flex_context_code) = upper('SHIP_INFO')
746 and upper(fcu.end_user_column_name) = upper('hiddShipAddXml');
747
748 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
749
750 l_addr_sql := 'select ' || l_col_name || ' as ship_address ' ;
751
752 exception
753 when no_data_found then
754 l_addr_sql := '';
755 end;
756
757 if l_addr_sql is not null then
758 l_sql := l_addr_sql || l_where_sql;
759 begin
760 execute immediate l_sql into clob_addr using p_line_location_id, p_draft_id,l_attr_grp_id ;
761 exception
762 when others then
763 select '' into clob_addr
764 from dual;
765 end;
766 end if;
767 return(clob_addr);
768 end get_shipaddressxml_pvt;
769
770 FUNCTION get_shipaddressxml(p_line_location_id number) return CLOB as
771 begin
772 return(get_shipaddressxml_pvt(p_line_location_id, -1));
773 end get_shipaddressxml;
774
775 FUNCTION get_mod_shipaddressxml(p_line_location_id number) return CLOB as
776 begin
777 return(get_shipaddressxml_pvt(p_line_location_id, draftId));
778 end get_mod_shipaddressxml;
779
780 FUNCTION get_vendor_address_details(uda_template_id NUMBER, p_header_id NUMBER, p_draft_id NUMBER) RETURN CLOB AS
781 v_clob_addr CLOB;
782 BEGIN
783 SELECT PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(uda_template_id, NULL, p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
784 'SUPPLIER_DTLS', NULL, 'HIDD_ADDRS_XML', 'INTERNAL_VALUE') INTO v_clob_addr FROM dual;
785
786 RETURN v_clob_addr;
787 END get_vendor_address_details;
788
789 FUNCTION get_vendor_contact_details(uda_template_id NUMBER, p_header_id NUMBER, p_draft_id NUMBER) RETURN CLOB AS
790 v_clob_addr CLOB;
791 BEGIN
792 SELECT PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(uda_template_id, NULL, p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
793 'SUPPLIER_DTLS', NULL, 'HIDD_CNTCT_XML', 'INTERNAL_VALUE') INTO v_clob_addr FROM dual;
794
795 RETURN v_clob_addr;
796 END get_vendor_contact_details;
797
798 FUNCTION get_inv_office_details(uda_template_id NUMBER, p_header_id NUMBER, p_draft_id NUMBER) RETURN VARCHAR2 AS
799 p_result VARCHAR2 (100);
800 inv_office VARCHAR2 (1000);
801 pay_office VARCHAR2 (1000);
802 Begin
803
804 inv_office := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(uda_template_id, NULL, p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
805 'addresses', NULL, 'addresscode', 'INV_OFFICE', 'INTERNAL_VALUE');
806 pay_office := PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(uda_template_id, NULL, p_header_id, p_draft_id, NULL, NULL, NULL, NULL,
807 'addresses', NULL, 'addresscode', 'PAY_OFFICE', 'INTERNAL_VALUE');
808
809 IF (inv_office = pay_office)
810 then p_result := 'Y';
811 ELSE p_result := 'N';
812 END IF;
813 RETURN (p_result);
814
815 EXCEPTION
816 WHEN Others THEN
817 p_result := NULL;
818
819 END get_inv_office_details;
820
821 FUNCTION get_fob_lookup_code RETURN BOOLEAN AS
822 l_sql_d varchar2(4000);
823 l_sql_o varchar2(4000);
824 l_col_name varchar2(50);
825 l_attr_grp_id number;
826 l_value NUMBER;
827 l_count number;
828 l_fob_value_dest varchar2(100);
829 l_fob_value_origin varchar2(100);
830 l_first_bind number;
831 l_draftId number;
832 Begin
833
834 Select eag.attr_group_id, eav.database_column INTO l_attr_grp_id, l_col_name
835 from ego_attrs_v eav, ego_attr_groups_v eag
836 where eav.attr_group_name = 'SHIP_INFO' and eav.attr_group_type = 'PO_SHIPMENTS_EXT_ATTRS'
837 AND eav.ATTR_NAME = 'FOB' and eag.attr_group_name = 'SHIP_INFO' and
838 eag.attr_group_type = 'PO_SHIPMENTS_EXT_ATTRS';
839
840 IF (draftID = -1) then
841 begin
842 SELECT 1 INTO l_value
843 FROM PO_LINE_LOCATIONS_ALL
844 WHERE PO_HEADER_ID = pt_headerId
845 and rownum = 1;
846 exception
847 When no_data_found then
848 p_fob_value_dest := '';
849 p_fob_value_origin := '';
850 return(true);
851 end;
852
853 l_sql_o := '(SELECT line_location_id from po_line_locations_all where PO_HEADER_ID =:1)';
854 l_first_bind := pt_headerId;
855 l_draftId := -1;
856
857 else
858 begin
859 SELECT 1 INTO l_value
860 FROM PO_LINE_LOCATIONS_draft_all
861 WHERE draft_id = draftId
862 and rownum = 1;
863 exception
864 When no_data_found then
865 p_fob_value_dest := '';
866 p_fob_value_origin := '';
867 return(true);
868 end;
869
870 l_sql_o := '(SELECT pla.line_location_id from po_line_locations_draft_all pla,' ||
871 ' po_drafts pd where pd.draft_id = :1 and pd.document_id = pla.po_header_id)';
872 l_first_bind := draftId;
873 l_draftId := draftId;
874 end if;
875
876 l_sql_d := '(SELECT COUNT(NVL( ' || l_col_name || ' , ''N'')) '
877 || 'FROM PO_LINE_LOCATIONS_All_ext_b WHERE line_location_id IN '
878 || l_sql_o
879 || ' and draft_id = :2 and attr_group_id =:3 AND '
880 || ' DECODE( NVL(' || l_col_name || ', ''N''), ''OSP'', ''Origin'', ''OAL'', ''Origin'',
881 NVL(' || l_col_name || ', ''N'')) <> :4)';
882
883 execute immediate l_sql_d into l_count using l_first_bind, l_draftId, l_attr_grp_id, 'DEST' ;
884
885 if l_count = 0 then
886 p_fob_value_dest := '''Destination''';
887 else
888 p_fob_value_dest := '''See Schedule''';
889 end if;
890
891 execute immediate l_sql_d into l_count using l_first_bind, l_draftId, l_attr_grp_id, 'Origin' ;
892
893 if l_count = 0 then
894 p_fob_value_origin := '''Origin''';
895 else
896 p_fob_value_origin := '''N''';
897 end if;
898
899
900 RETURN (TRUE);
901
902 EXCEPTION
903 WHEN Others THEN
904 RETURN (FALSE);
905
906 END get_fob_lookup_code;
907
908 function set_business_classification return boolean as
909 l_supp_size varchar2(500);
910 l_uda_template_id number;
911 l_draft_id number;
912 begin
913
914 p_fp_of347_small := '''N''';
915 p_fp_of347_other := '''N''';
916 p_fp_of347_disadv := '''N''';
917 p_fp_of347_women := '''N''';
918 p_fp_of347_hubzone := '''N''';
919 p_fp_of347_SDVO := '''N''';
920 p_fp_of347_WOSB := '''N''';
921 p_fp_of347_EDWOSB := '''N''';
922
923 select uda_template_id, draft_id
924 into l_uda_template_id,l_draft_id
925 from po_headers_merge_v
926 where po_header_id = headerId
927 and draft_id = nvl(draftId, -1);
928
929 l_supp_size := PO_UDA_PUB.get_single_attr_value(l_uda_template_id,
930 NULL, headerId, l_draft_id , NULL, NULL, NULL, NULL,
931 'SUPPLIER_DTLS', NULL, 'SUPLR_SIZE', 'INTERNAL_VALUE');
932
933 if l_supp_size is not null then
934 if l_supp_size = 'SMALL' then
935 p_fp_of347_small := '''Y''';
936 elsif l_supp_size = 'OTHER' then
937 p_fp_of347_other := '''Y''';
938 end if;
939 end if;
940
941 begin
942 select decode(EDWOSB_COUNT, 0, '''N''', '''Y''') EDWOSB,
943 decode(HUBZ_COUNT, 0, '''N''', '''Y''') HUBZ,
944 decode(WOSB_COUNT, 0, '''N''', '''Y''') WOSB,
945 decode(SDVO_COUNT, 0, '''N''', '''Y''') SDVO,
946 decode(WOMEN_COUNT, 0, '''N''', '''Y''') WO,
947 decode(SDB_COUNT, 0,
948 decode(SDBSBA_COUNT, 0, '''N''', '''Y'''),
949 '''Y''') SDB
950 into
951 p_fp_of347_EDWOSB,
952 p_fp_of347_hubzone,
953 p_fp_of347_WOSB,
954 p_fp_of347_SDVO,
955 p_fp_of347_women,
956 p_fp_of347_disadv
957 from
958 (
959 select pca.lookup_code
960 from POS_BUS_CLASS_ATTR pca, po_vendors supp, po_headers_merge_v hdr
961 where hdr.po_header_id = headerId
962 and hdr.draft_id = nvl(draftId, -1)
963 and hdr.vendor_id = supp.vendor_id
964 and pca.party_id = supp.party_id
965 AND ( pca.end_date_active is null or trunc(pca.end_date_active) > sysdate )
966 AND pca.status='A'
967 AND pca.class_status = 'APPROVED'
968 AND pca.classification_id not in
969 (
970 SELECT classification_id
971 FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
972 WHERE psm.party_id = pca.party_id
973 AND psm.mapping_id = pbcr.mapping_id
974 AND pbcr.request_status = 'PENDING'
975 AND pbcr.request_type in ( 'ADD', 'UPDATE' )
976 AND pbcr.classification_id is not null)
977 )
978 pivot ( count(lookup_code) as count
979 for lookup_code in ('EDWOSB' as EDWOSB,
980 'HUB_ZONE' as HUBZ ,
981 'WOMEN_OWNED_WOSB_PRGM' as WOSB,
982 'DISABLED_VETERAN_OWNED' as SDVO,
983 'WOMEN_OWNED' as WOMEN,
984 '27' as SDB, 'A4' as SDBSBA)) ;
985
986 end;
987
988 return(true);
989 exception
990 when others then
991 p_fp_of347_small := '''N''';
992 p_fp_of347_other := '''N''';
993 p_fp_of347_disadv := '''N''';
994 p_fp_of347_women := '''N''';
995 p_fp_of347_hubzone := '''N''';
996 p_fp_of347_SDVO := '''N''';
997 p_fp_of347_WOSB := '''N''';
998 p_fp_of347_EDWOSB := '''N''';
999
1000 return(false);
1001 end set_business_classification;
1002 FUNCTION get_req_numbers(p_header_id NUMBER, p_draft_id NUMBER, p_line_id NUMBER) RETURN XMLType IS
1003
1004 l_req_nums XMLType;
1005 BEGIN
1006
1007 SELECT XMLAgg(XMLElement("REF_PR_NUM", segment1))
1008 INTO l_req_nums
1009 FROM (SELECT distinct h.segment1
1010 FROM po_distributions_merge_v pod,
1011 po_req_distributions_all prd,
1012 po_requisition_headers_all h,
1013 po_requisition_lines_all l
1014 WHERE pod.po_header_id = p_header_id
1015 and pod.po_line_id = p_line_id
1016 and pod.draft_id = p_draft_id
1017 and pod.req_distribution_id = prd.distribution_id
1018 and prd.requisition_line_id = l.requisition_line_id
1019 and l.requisition_header_id = h.requisition_header_id);
1020
1021 RETURN l_req_nums;
1022 END get_req_numbers;
1023
1024
1025 FUNCTION get_bpa_req_numbers(p_auction_header_id NUMBER, p_auction_line_number NUMBER) RETURN XMLType IS
1026
1027 l_req_nums XMLType;
1028 BEGIN
1029
1030 SELECT XMLAgg(XMLElement("REF_PR_NUM", requisition_number))
1031 INTO l_req_nums
1032 FROM (SELECT distinct requisition_number
1033 FROM pon_backing_requisitions
1034 WHERE auction_header_id = p_auction_header_id
1035 and line_number = p_auction_line_number);
1036
1037 RETURN l_req_nums;
1038 END get_bpa_req_numbers;
1039
1040
1041 FUNCTION get_acrns(p_header_id NUMBER, p_draft_id NUMBER, p_line_id NUMBER) RETURN XMLType IS
1042
1043 l_acrns XMLType;
1044 BEGIN
1045 SELECT XMLAgg(XMLElement("ACRN", acrn))
1046 INTO l_acrns
1047 FROM (SELECT distinct pod.acrn
1048 FROM po_distributions_merge_v pod
1049 WHERE pod.po_header_id = p_header_id
1050 and pod.po_line_id = p_line_id
1051 and pod.draft_id = p_draft_id);
1052
1053 RETURN l_acrns;
1054 END get_acrns;
1055
1056
1057 FUNCTION AfterPForm RETURN Boolean AS
1058
1059 l_return Boolean;
1060 l_header_id NUMBER;
1061 BEGIN
1062
1063 IF is_doc_mod = 'Y' THEN
1064 SELECT document_id INTO l_header_id FROM po_drafts WHERE draft_id = draftId;
1065 pt_headerId := l_header_id;
1066 ELSE
1067 pt_headerId := headerId;
1068 END IF;
1069 l_return := get_fob_lookup_code;
1070
1071 RETURN (TRUE);
1072
1073 END AfterPForm;
1074
1075 FUNCTION calc_award_enc_amt(p_header_id NUMBER) RETURN NUMBER IS
1076 l_return NUMBER := 0;
1077 l_line_loc_id NUMBER;
1078 l_shipment_type VARCHAR2(25);
1079 l_rate NUMBER;
1080 l_dist_id NUMBER;
1081 l_enc_amt NUMBER;
1082 CURSOR cur_shipments IS
1083 SELECT line_location_id,
1084 shipment_type
1085 FROM PO_LINE_LOCATIONS_ALL
1086 WHERE po_header_id = p_header_id;
1087
1088 CURSOR cur_distributions (line_loc_id NUMBER) IS
1089 SELECT rate,
1090 po_distribution_id,
1091 encumbered_amount
1092 FROM PO_DISTRIBUTIONS_ALL
1093 WHERE line_location_id = line_loc_id;
1094
1095 BEGIN
1096 OPEN cur_shipments;
1097 LOOP
1098 FETCH cur_shipments INTO l_line_loc_id, l_shipment_type;
1099 OPEN cur_distributions(l_line_loc_id);
1100 LOOP
1101 FETCH cur_distributions INTO l_rate,l_dist_id, l_enc_amt;
1102 l_return := l_return + PO_INQ_SV.get_active_enc_amount(x_rate => l_rate
1103 ,x_enc_amount => l_enc_amt
1104 ,x_shipment_type => l_shipment_type
1105 ,x_po_distribution_id => l_dist_id);
1106 EXIT WHEN cur_distributions%NOTFOUND;
1107 END LOOP;
1108 CLOSE cur_distributions;
1109 EXIT WHEN cur_shipments%NOTFOUND;
1110 END LOOP;
1111 RETURN (l_return);
1112 END calc_award_enc_amt;
1113
1114 FUNCTION Get_closeout_uda_details(p_header_id NUMBER,
1115 p_template_id NUMBER)
1116 RETURN XMLTYPE
1117 AS
1118 l_sql VARCHAR2(4000);
1119 l_attr_col VARCHAR2(50);
1120 l_attr_grp_id NUMBER;
1121 l_attr_name VARCHAR2(50);
1122 l_attr_grp_name VARCHAR2(100);
1123 l_ctr NUMBER;
1124 po_attr_uda_xml XMLTYPE;
1125 CURSOR curr_uda_groups IS
1126 SELECT gr.attr_group_name,
1127 t1.attribute_group_id
1128 FROM po_uda_ag_template_usages t1,
1129 ego_attr_groups_v gr
1130 WHERE t1.template_id = p_template_id
1131 AND t1.attribute_category = 'CLOSEOUT'
1132 AND t1.attribute_group_id = gr.attr_group_id
1133 AND gr.attr_group_type = 'PO_HEADER_EXT_ATTRS';
1134 CURSOR curr_uda_columns (
1135 p_attr_group_id NUMBER) IS
1136 SELECT val.attr_name,
1137 val.database_column
1138 FROM po_uda_ag_template_usages t1,
1139 ego_attr_groups_v gr,
1140 ego_attrs_v val
1141 WHERE t1.template_id = p_template_id
1142 AND t1.attribute_category = 'CLOSEOUT'
1143 AND t1.attribute_group_id = gr.attr_group_id
1144 AND gr.attr_group_id = p_attr_group_id
1145 AND gr.attr_group_name = val.attr_group_name
1146 AND gr.attr_group_type = val.attr_group_type
1147 AND gr.attr_group_type = 'PO_HEADER_EXT_ATTRS';
1148 BEGIN
1149 OPEN curr_uda_groups;
1150
1151 LOOP
1152 FETCH curr_uda_groups INTO l_attr_grp_name,l_attr_grp_id ;
1153
1154 EXIT WHEN curr_uda_groups%NOTFOUND;
1155
1156 l_ctr := 0;
1157
1158 OPEN curr_uda_columns (l_attr_grp_id);
1159
1160 l_sql := l_sql || '(select XMLElement("' || l_attr_grp_name || '", XMLForest(';
1161
1162 LOOP
1163 FETCH curr_uda_columns INTO l_attr_name, l_attr_col
1164 ;
1165
1166 EXIT WHEN curr_uda_columns%NOTFOUND;
1167
1168 IF l_ctr = 0 THEN
1169 l_sql := l_sql
1170 || l_attr_col
1171 || ' as '
1172 || l_attr_name;
1173 ELSE
1174 l_sql := l_sql
1175 || ','
1176 || l_attr_col
1177 || ' as '
1178 || l_attr_name;
1179 END IF;
1180
1181 l_ctr := l_ctr + 1;
1182 END LOOP;
1183
1184 CLOSE curr_uda_columns;
1185
1186 l_sql := l_sql
1187 || ' ))';
1188
1189 l_sql := l_sql
1190 || ' from po_headers_all_ext_vl where po_header_id = '
1191 || p_header_id
1192 || ' AND draft_id = -1 '
1193 || ' AND attr_group_id ='
1194 || l_attr_grp_id
1195 || ')';
1196 END LOOP;
1197 EXECUTE IMMEDIATE l_sql INTO po_attr_uda_xml;
1198
1199 RETURN ( po_attr_uda_xml );
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202
1203 RETURN (po_attr_uda_xml);
1204 END get_closeout_uda_details;
1205
1206 END;