[Home] [Help]
PACKAGE BODY: APPS.PON_SGD_PKG
Source
1 PACKAGE BODY PON_SGD_PKG AS
2 /* $Header: PON_SGD_PKG.plb 120.4 2012/01/23 06:24:05 nbingi noship $ */
3
4 FUNCTION get_uda_header_xml RETURN XMLType IS
5 CURSOR C_ATTR_GRP IS
6 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP, AG.ATTR_GROUP_ID
7 ATTR_GROUP_ID,
8 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME, AG.MULTI_ROW
9 MULTI_ROW
10 FROM EGO_FND_DSC_FLX_CTX_EXT AG, pon_auction_headers_all H,
11 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
12 WHERE H.auction_header_id = p_auction_header_id
13 AND H.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
14 AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
15 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
16 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
17 and POTU.attribute_group_id in (select distinct attr_group_id from PON_AUCTION_HEADERS_EXT_B
18 where AUCTION_HEADER_ID = H.auction_header_id and attr_group_id = POTU.attribute_group_id)
19
20 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
21
22 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
23 SELECT EFDFCE.ATTR_ID ,
24 EFDFCE.APPLICATION_COLUMN_NAME ,
25 FCU.END_USER_COLUMN_NAME,
26 fcu.flex_value_set_id,
27 EFDFCE.data_type
28 FROM
29 EGO_FND_DF_COL_USGS_EXT EFDFCE
30 , FND_DESCR_FLEX_COLUMN_USAGES FCU
31 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
32 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
33 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE =
34 EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
35 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME =
36 EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
37 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
38 AND FCU.DISPLAY_FLAG <> 'H';
39
40 L_SQL CLOB;
41 L_OP CLOB;
42 L_ATTR_GRP NUMBER;
43 L_CTR NUMBER;
44 L_O_CTR NUMBER;
45 l_uda_xml XMLTYPE;
46
47 p_application_id number := 201;
48 p_attr_internal_str_value varchar2(100);
49
50 p_attr_internal_name varchar2(100);
51 p_attr_group_type varchar2(200) := 'PON_AUC_HDRS_EXT_ATTRS';
52 p_attr_group_int_name varchar2(200);
53
54 p_object_name varchar2(200) := 'PON_AUCTION_HEADERS_EXT_B';
55
56 p_pk1_column_name varchar2(200) := 'AUCTION_HEADER_ID';
57 p_pk1_value number := p_auction_header_id;
58
59
60 BEGIN
61
62 L_O_CTR := 0;
63 FOR REC IN C_ATTR_GRP LOOP
64 IF REC.MULTI_ROW = 'Y' THEN
65 IF L_O_CTR > 0 THEN
66 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
67 || '", XMLAgg(XMLForest(' ;
68 ELSE
69 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '",
70 XMLAgg(XMLForest(';
71 END IF;
72 ELSE
73 IF L_O_CTR > 0 THEN
74 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
75 || '", XMLForest(' ;
76 ELSE
77 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
78 END IF;
79 END IF;
80 L_CTR := 0 ;
81 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
82 IF L_CTR = 0 THEN
83 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' ||
84 R.END_USER_COLUMN_NAME;
85 ELSE
86 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' ||
87 R.END_USER_COLUMN_NAME;
88 END IF;
89
90 if r.flex_value_set_id is not null then
91 if (r.data_type = 'C' or r.data_type = 'A' ) then
92 l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
93 p_application_id || ',' || ' null, ' ||
94 R.APPLICATION_COLUMN_NAME || ', null , ''' ||
95 R.END_USER_COLUMN_NAME || ''',''' ||
96 p_attr_group_type || ''',''' ||
97 REC.ATTR_GROUP || ''',' ||
98 r.attr_id || ',''' ||
99 p_object_name || ''',''' ||
100 p_pk1_column_name || ''',' ||
101 p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
102 elsif r.data_type = 'N' then
103 l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
104 p_application_id || ',' || ' null, null, ' ||
105 R.APPLICATION_COLUMN_NAME || ', ''' ||
106 R.END_USER_COLUMN_NAME || ''',''' ||
107 p_attr_group_type || ''',''' ||
108 REC.ATTR_GROUP || ''',' ||
109 r.attr_id || ',''' ||
110 p_object_name || ''',''' ||
111 p_pk1_column_name || ''',' ||
112 p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
113 elsif (r.data_type = 'X' or r.data_type = 'Y') then
114 l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
115 p_application_id || ',' || R.APPLICATION_COLUMN_NAME ||
116 ' , null, null, ''' ||
117 R.END_USER_COLUMN_NAME || ''',''' ||
118 p_attr_group_type || ''',''' ||
119 REC.ATTR_GROUP || ''',' ||
120 r.attr_id || ',''' ||
121 p_object_name || ''',''' ||
122 p_pk1_column_name || ''',' ||
123 p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
124 end if;
125 end if;
126
127
128
129 L_CTR := L_CTR + 1;
130 END LOOP;
131 IF REC.MULTI_ROW = 'Y' THEN
132 L_SQL := L_SQL || ')))';
133 ELSE
134 L_SQL := L_SQL || ' ))';
135 END IF;
136 L_SQL := L_SQL || ' from PON_AUCTION_HEADERS_EXT_VL where
137 AUCTION_HEADER_ID = ' || p_auction_header_id || ' and attr_group_id = '
138 || REC.ATTR_GROUP_ID || ')' ;
139
140 L_O_CTR := L_O_CTR + 1;
141 END LOOP;
142
143 l_op := ' select XMLConcat(' || l_sql || ') from dual';
144 execute immediate l_op into l_uda_xml;
145
146 RETURN (l_uda_xml);
147 END;
148
149
150 FUNCTION get_addressxml(p_header_id number) RETURN CLOB as
151 l_where_sql varchar2(1000);
152 l_addr_sql varchar2(1000);
153 l_sql varchar2(4000);
154 l_col_name varchar2(50);
155 l_attr_grp_id number;
156 clob_addr CLOB;
157 c_addr SYS_REFCURSOR;
158 l_address varchar2(4000);
159 begin
160 l_where_sql := ' from PON_AUCTION_HEADERS_EXT_VL where AUCTION_HEADER_ID = :1';
161
162 begin
163 SELECT ag.attr_group_id,
164 efdfce.application_column_name
165 into l_attr_grp_id, l_col_name
166 FROM ego_fnd_dsc_flx_ctx_ext ag,
167 ego_fnd_df_col_usgs_ext efdfce,
168 fnd_descr_flex_column_usages fcu
169 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
170 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
171 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
172 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
173 and fcu.application_column_name = efdfce.application_column_name
174 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_HDRS_EXT_ATTRS'
175 and upper(ag.descriptive_flex_context_code) = upper('addresses')
176 and upper(fcu.end_user_column_name) = upper('addressdtlsxml');
177
178 l_where_sql := l_where_sql || ' and attr_group_id = :2 ' ;
179
180 l_addr_sql := 'select ' || l_col_name || ' as address ' ;
181
182 exception
183 when no_data_found then
184 l_addr_sql := '';
185 end;
186
187 if l_addr_sql is not null then
188 l_sql := l_addr_sql || l_where_sql;
189
190 dbms_lob.createtemporary(clob_addr, true);
191 dbms_lob.open(clob_addr, dbms_lob.lob_readwrite);
192 open c_addr for l_sql using p_header_id,l_attr_grp_id;
193 loop
194 fetch c_addr into l_address;
195 exit when c_addr%notfound;
196 if l_address is not null then
197 dbms_lob.writeappend(clob_addr, length(l_address), l_address);
198 end if;
199 end loop;
200 dbms_lob.close(clob_addr);
201 end if;
202 return(clob_addr);
203 end get_addressxml;
204
205 FUNCTION get_contactsxml(p_header_id number) RETURN CLOB as
206 l_cnt_sql varchar2(1000);
207 l_where_sql varchar2(1000);
208 l_sql varchar2(4000);
209 l_cnt varchar2(4000);
210 l_col_name varchar2(50);
211 l_attr_grp_id number;
212 c_cnt SYS_REFCURSOR;
213 clob_cnt CLOB;
214
215 begin
216 l_where_sql := ' from PON_AUCTION_HEADERS_EXT_VL where AUCTION_HEADER_ID = :1';
217
218 begin
219 SELECT ag.attr_group_id,
220 efdfce.application_column_name
221 into l_attr_grp_id, l_col_name
222 FROM ego_fnd_dsc_flx_ctx_ext ag,
223 ego_fnd_df_col_usgs_ext efdfce,
224 fnd_descr_flex_column_usages fcu
225 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
226 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
227 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
228 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
229 and fcu.application_column_name = efdfce.application_column_name
230 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_HDRS_EXT_ATTRS'
231 and upper(ag.descriptive_flex_context_code) = upper('addresses')
232 and upper(fcu.end_user_column_name) = upper('contactdtlsxml');
233
234 l_where_sql := l_where_sql || ' and attr_group_id = :2 ' ;
235
236 l_cnt_sql := 'select ' || l_col_name || ' as contacts ' ;
237
238 exception
239 when no_data_found then
240 l_cnt_sql := '';
241 end;
242
243 if l_cnt_sql is not null then
244 l_sql := l_cnt_sql || l_where_sql;
245
246 dbms_lob.createtemporary(clob_cnt, true);
247 dbms_lob.open(clob_cnt , dbms_lob.lob_readwrite);
248 open c_cnt for l_sql using p_header_id,l_attr_grp_id;
249 loop
250 fetch c_cnt into l_cnt;
251 exit when c_cnt%notfound;
252 if l_cnt is not null then
253 dbms_lob.writeappend(clob_cnt, length(l_cnt), l_cnt);
254 end if;
255 end loop;
256 dbms_lob.close(clob_cnt);
257 end if;
258 return(clob_cnt);
259 end get_contactsxml;
260
261 FUNCTION get_uda_lines_xml(p_line_id NUMBER) RETURN XMLTYPE IS
262 CURSOR C_ATTR_GRP IS
263 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP, AG.ATTR_GROUP_ID
264 ATTR_GROUP_ID,
265 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME, AG.MULTI_ROW
266 MULTI_ROW
267 FROM EGO_FND_DSC_FLX_CTX_EXT AG, pon_auction_item_prices_all L,
268 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
269 WHERE L.auction_header_id = p_auction_header_id
270 AND L.LINE_NUMBER = p_line_id
271 AND L.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
272 AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
273 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
274 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
275 and potu.attribute_group_id in (select distinct attr_group_id from PON_AUCTION_ITEM_PRICES_EXT_B
276 where AUCTION_HEADER_ID = L.auction_header_id and LINE_NUMBER=L.LINE_NUMBER and attr_group_id = potu.attribute_group_id)
277 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
278
279 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
280 SELECT EFDFCE.ATTR_ID ,
281 EFDFCE.APPLICATION_COLUMN_NAME ,
282 FCU.END_USER_COLUMN_NAME ,
283 fcu.flex_value_set_id,
284 EFDFCE.data_type
285
286 FROM
287 EGO_FND_DF_COL_USGS_EXT EFDFCE
288 , FND_DESCR_FLEX_COLUMN_USAGES FCU
289 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
290 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
291 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE =
292 EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
293 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME =
294 EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
295 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
296 AND FCU.DISPLAY_FLAG <> 'H';
297
298 L_SQL CLOB;
299 L_OP CLOB;
300 L_ATTR_GRP NUMBER;
301 L_CTR NUMBER;
302 L_O_CTR NUMBER;
303 l_uda_xml XMLTYPE;
304
305 p_application_id number := 201;
306 p_attr_internal_str_value varchar2(100);
307
308 p_attr_internal_name varchar2(100);
309 p_attr_group_type varchar2(200) := 'PON_AUC_PRICES_EXT_ATTRS';
310 p_attr_group_int_name varchar2(200);
311
312 p_object_name varchar2(200) := 'PON_AUCTION_ITEM_PRICES_EXT_B';
313
314 p_pk1_column_name varchar2(200) := 'AUCTION_HEADER_ID';
315 p_pk1_value number := p_auction_header_id;
316 p_pk2_column_name varchar2(200) := 'LINE_NUMBER';
317 p_pk2_value number := p_line_id;
318
319 BEGIN
320
321 L_O_CTR := 0;
322 FOR REC IN C_ATTR_GRP LOOP
323 IF REC.MULTI_ROW = 'Y' THEN
324 IF L_O_CTR > 0 THEN
325 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
326 || '", XMLAgg(XMLForest(' ;
327 ELSE
328 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '",
329 XMLAgg(XMLForest(';
330 END IF;
331 ELSE
332 IF L_O_CTR > 0 THEN
333 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
334 || '", XMLForest(' ;
335 ELSE
336 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
337 END IF;
338 END IF;
339 L_CTR := 0 ;
340 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
341 IF L_CTR = 0 THEN
342 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' ||
343 R.END_USER_COLUMN_NAME;
344 ELSE
345 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' ||
346 R.END_USER_COLUMN_NAME;
347 END IF;
348
349 if r.flex_value_set_id is not null then
350 if (r.data_type = 'C' or r.data_type = 'A' ) then
351 l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
352 p_application_id || ',' || ' null, ' ||
353 R.APPLICATION_COLUMN_NAME || ', null , ''' ||
354 R.END_USER_COLUMN_NAME || ''',''' ||
355 p_attr_group_type || ''',''' ||
356 REC.ATTR_GROUP || ''',' ||
357 r.attr_id || ',''' ||
358 p_object_name || ''',''' ||
359 p_pk1_column_name || ''',' ||
360 p_pk1_value || ',''' ||
361 p_pk2_column_name || ''',' ||
362 p_pk2_value || ') as ' || R.end_user_column_name || '_DESC' ;
363 elsif r.data_type = 'N' then
364 l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
365 p_application_id || ',' || ' null, null, ' ||
366 R.APPLICATION_COLUMN_NAME || ', ''' ||
367 R.END_USER_COLUMN_NAME || ''',''' ||
368 p_attr_group_type || ''',''' ||
369 REC.ATTR_GROUP || ''',' ||
370 r.attr_id || ',''' ||
371 p_object_name || ''',''' ||
372 p_pk1_column_name || ''',' ||
373 p_pk1_value || ',''' ||
374 p_pk2_column_name || ''',' ||
375 p_pk2_value || ') as ' || R.end_user_column_name || '_DESC' ;
376 elsif (r.data_type = 'X' or r.data_type = 'Y') then
377 l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
378 p_application_id || ',' || R.APPLICATION_COLUMN_NAME ||
379 ' , null, null, ''' ||
380 R.END_USER_COLUMN_NAME || ''',''' ||
381 p_attr_group_type || ''',''' ||
382 REC.ATTR_GROUP || ''',' ||
383 r.attr_id || ',''' ||
384 p_object_name || ''',''' ||
385 p_pk1_column_name || ''',' ||
386 p_pk1_value || ',''' ||
387 p_pk2_column_name || ''',' ||
388 p_pk2_value || ') as ' || R.end_user_column_name || '_DESC' ;
389 end if;
390 end if;
391
392
393 L_CTR := L_CTR + 1;
394 END LOOP;
395 IF REC.MULTI_ROW = 'Y' THEN
396 L_SQL := L_SQL || ')))';
397 ELSE
398 L_SQL := L_SQL || ' ))';
399 END IF;
400 L_SQL := L_SQL || ' from PON_AUCTION_ITEM_PRICES_EXT_VL where
401 AUCTION_HEADER_ID = '||p_auction_header_id||' and LINE_NUMBER = ' ||
402 p_line_id || ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
403
404 L_O_CTR := L_O_CTR + 1;
405 END LOOP;
406
407 l_op := ' select XMLConcat(' || l_sql || ') from dual';
408 execute immediate l_op into l_uda_xml;
409
410 RETURN (l_uda_xml);
411 END;
412
413
414 FUNCTION get_shipaddressxml(p_line_id number) return CLOB as
415
416 l_where_sql varchar2(1000);
417 l_addr_sql varchar2(1000);
418 l_sql varchar2(4000);
419 l_col_name varchar2(50);
420 l_attr_grp_id number;
421 clob_addr CLOB;
422 c_addr SYS_REFCURSOR;
423 l_address varchar2(4000);
424
425 begin
426
427 l_where_sql := ' from PON_AUCTION_ITEM_PRICES_EXT_VL where AUCTION_HEADER_ID = :1 and LINE_NUMBER= :2';
428
429 begin
430 SELECT ag.attr_group_id,
431 efdfce.application_column_name
432 into l_attr_grp_id, l_col_name
433
434 FROM ego_fnd_dsc_flx_ctx_ext ag,
435 ego_fnd_df_col_usgs_ext efdfce,
436 fnd_descr_flex_column_usages fcu
437
438 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
439 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
440 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
441 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
442 and fcu.application_column_name = efdfce.application_column_name
443 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PON_AUC_PRICES_EXT_ATTRS'
444 and upper(ag.descriptive_flex_context_code) = upper('SHIP_INFO')
445 and upper(fcu.end_user_column_name) = upper('hiddShipAddXml');
446
447 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
448
449 l_addr_sql := 'select ' || l_col_name || ' as ship_address ' ;
450
451 exception
452 when no_data_found then
453 l_addr_sql := '';
454 end;
455
456 if l_addr_sql is not null then
457 l_sql := l_addr_sql || l_where_sql;
458
459
460 begin
461 execute immediate l_sql into clob_addr using p_auction_header_id,p_line_id,l_attr_grp_id ;
462 exception
463 when others then
464 select '' into clob_addr from dual;
465 end;
466 end if;
467 return(clob_addr);
468 END get_shipaddressxml;
469
470
471 FUNCTION getDiffXML(p_document_type VARCHAR2,
472 p_entity_name VARCHAR2,
473 p_mod_pk1_value NUMBER,
474 p_mod_pk2_value NUMBER default null,
475 p_mod_pk3_value NUMBER default null,
476 p_mod_pk4_value NUMBER default null,
477 p_mod_pk5_value NUMBER default null,
478 p_base_pk1_value NUMBER default null,
479 p_base_pk2_value NUMBER default null,
480 p_base_pk3_value NUMBER default null,
481 p_base_pk4_value NUMBER default null,
482 p_base_pk5_value NUMBER default null ) RETURN XMLType IS
483
484 l_finalXML XMLTYPE;
485 l_sql_stmt CLOB;
486
487 BEGIN
488
489 IF p_mod_pk1_value IS NOT NULL AND p_entity_name IS NOT NULL AND p_document_type IS NOT NULL THEN
490
491 SELECT (XMLAgg(XMLElement("ATTRIBUTE" , XMLAttributes(diff.label_message_code as "name",
492 diff.disp_data_type as "datatype",
493 diff.sub_entity_name as "sub_entity"),
494 XMLElement("orig_id", diff.orig_value),
495 XMLElement("mod_id", diff.mod_value),
496 XMLElement("orig_value", diff.orig_value_desc),
497 XMLElement("mod_value", diff.mod_value_desc) ))) finalXML
498 INTO l_finalXML
499 FROM
500 (
501 SELECT nvl(fnd_message.get_string('PON', c.label_message_code),d.column_name) label_message_code,
502 c.disp_data_type,
503 c.sub_entity_name,
504 d.orig_value,
505 d.mod_value,
506 decode(c.disp_data_type, 'N', nvl2(d.orig_value, d.orig_value, 0),
507 'M', nvl2(d.orig_value, d.orig_value, 0),
508 nvl(d.orig_value_desc, d.orig_value)) orig_value_desc,
509 decode(c.disp_data_type, 'N', nvl2(d.mod_value, d.mod_value, 0),
510 'M', nvl2(d.mod_value, d.mod_value, 0),
511 nvl(d.mod_value_desc, d.mod_value)) mod_value_desc
512
513 FROM pon_entity_differences d,
514 po_diff_config c
515
516 WHERE d.document_type = p_document_type
517 and d.entity_name = p_entity_name
518 and d.mod_doc_pk1_val = p_mod_pk1_value
519 and (p_mod_pk2_value IS NULL or mod_doc_pk2_val = p_mod_pk2_value)
520 and (p_mod_pk3_value IS NULL or mod_doc_pk3_val = p_mod_pk3_value)
521 and (p_mod_pk4_value IS NULL or mod_doc_pk4_val = p_mod_pk4_value)
522 and (p_mod_pk5_value IS NULL or mod_doc_pk5_val = p_mod_pk5_value)
523 and c.document_type = d.document_type
524 and c.entity_name = d.entity_name
525 and c.mod_doc_source_name = d.mod_doc_source_name
526 and c.column_name = d.column_name
527 and nvl(c.is_printable_flag, 'N') = 'Y'
528 and nvl(c.ignore_source_flag, 'N') = 'N'
529
530 and not exists(SELECT 1 from po_entity_rollup_gt
531 WHERE document_type = d.document_type
532 and entity_name = d.entity_name
533 and mod_doc_source_name = d.mod_doc_source_name
534 and column_name = d.column_name
535 and mod_doc_pk1_val = d.mod_doc_pk1_val
536 and nvl(mod_doc_pk2_val, '0') = nvl(d.mod_doc_pk2_val, '0')
537 and nvl(mod_doc_pk3_val, '0') = nvl(d.mod_doc_pk3_val, '0')
538 and nvl(mod_doc_pk4_val, '0') = nvl(d.mod_doc_pk4_val, '0')
539 and nvl(mod_doc_pk5_val, '0') = nvl(d.mod_doc_pk5_val, '0')
540 )
541 ORDER BY c.display_seq_number
542 ) diff;
543 ELSE
544 --if document_type or entity_name is not passed, then we have to pass empty xml instead of null
545 --becasue getClobVal() on this return XML is used, if it is null getClobVal() function will error out.
546 l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
547 execute immediate l_sql_stmt into l_finalXML;
548 END IF;
549
550 RETURN l_finalXML;
551
552 END getDiffXML;
553
554
555 FUNCTION getLineSummaryDiffXML(p_document_type VARCHAR2,
556 p_entity_name VARCHAR2,
557 mod_pk1_value NUMBER,
558 mod_pk2_value NUMBER default null,
559 mod_pk3_value NUMBER default null,
560 mod_pk4_value NUMBER default null,
561 mod_pk5_value NUMBER default null,
562 base_pk1_value NUMBER default null,
563 base_pk2_value NUMBER default null,
564 base_pk3_value NUMBER default null,
565 base_pk4_value NUMBER default null,
566 base_pk5_value NUMBER default null ) RETURN XMLType IS
567
568 --cursor which gets the rollup change by grouping rows in po_entity_rollup_gt table
569 CURSOR c_rollup_changes IS
570 SELECT d.mod_doc_source_name,
571 d.column_name,
572 nvl(fnd_message.get_string('PON', min(c.label_message_code)),d.column_name) label_message_code,
573 d.orig_value,
574 d.mod_value,
575 decode(min(c.disp_data_type), 'N', nvl2(d.orig_value, d.orig_value, 0),
576 'M', nvl2(d.orig_value, d.orig_value, 0),
577 nvl(min(d.orig_value_desc), d.orig_value)) orig_value_desc,
578 decode(min(c.disp_data_type), 'N', nvl2(d.mod_value, d.mod_value, 0),
579 'M', nvl2(d.mod_value, d.mod_value, 0),
580 nvl(min(d.mod_value_desc), d.mod_value)) mod_value_desc,
581 min(c.disp_data_type) disp_data_type,
582 min(c.sub_entity_name) sub_entity_name
583
584 FROM po_entity_rollup_gt d,
585 po_diff_config c
586
587 WHERE d.document_type = p_document_type
588 and d.entity_name = p_entity_name
589 and d.mod_doc_pk1_val = mod_pk1_value
590 and c.document_type = p_document_type
591 and c.entity_name = p_entity_name
592 and c.mod_doc_source_name = d.mod_doc_source_name
593 and c.column_name = d.column_name
594
595 GROUP BY d.mod_doc_source_name, d.column_name, d.orig_value, d.mod_value
596
597 ORDER BY min(c.display_seq_number);
598
599
600 --cursor which gets the line numbers for a rollup change
601 CURSOR c_rollup_pks(c_mod_doc_source VARCHAR2, c_column_name VARCHAR2, c_orig_value VARCHAR2, c_mod_value VARCHAR2) IS
602 SELECT (case when(fromline = toline) then fromline
603 else fromline || '-' || toline end ) line_display
604 FROM (
605 SELECT min(line) fromline, max(line) toline, 'A' orderseq
606 FROM (SELECT line_num_display line, line_num_display -row_number() over(order by to_number(line_num_display)) rn, group_line_id
607 FROM pon_auction_item_prices_all
608 WHERE group_line_id is null
609 and auction_header_id = mod_pk1_value
610 and line_number IN (select mod_doc_pk2_val FROM po_entity_rollup_gt d
611 WHERE d.document_type = p_document_type
612 and d.entity_name = p_entity_name
613 and d.mod_doc_pk1_val = mod_pk1_value
614 and mod_doc_source_name = c_mod_doc_source
615 and column_name = c_column_name
616 and nvl(orig_value, '*') = nvl(c_orig_value ,'*')
617 and nvl(mod_value, '*') = nvl(c_mod_value ,'*')
618 )
619 )
620 GROUP BY group_line_id, rn
621
622 UNION ALL
623
624 SELECT min(line) fromline, max(line) toline, null orderseq
625 FROM (SELECT mod.line_num_display line, mod.group_line_id, base.rank - row_number() over(partition by mod.group_line_id order by mod.line_num_display) rn
626 FROM pon_auction_item_prices_all mod,
627 (SELECT paip.line_num_display, rank() over(partition by paip.group_line_id order by paip.line_num_display) rank, paip.line_number
628 FROM pon_auction_item_prices_all paip, pon_auction_headers_all pah
629 WHERE paip.group_line_id is not null and pah.auction_header_id = mod_pk1_value
630 and paip.auction_header_id = pah.auction_header_id_prev_amend
631 ) base
632 WHERE mod.line_number = base.line_number
633 and mod.group_line_id is not null
634 and mod.auction_header_id = mod_pk1_value
635 and mod.line_number IN (SELECT mod_doc_pk2_val FROM po_entity_rollup_gt d
636 WHERE d.document_type = p_document_type
637 and d.entity_name = p_entity_name
638 and d.mod_doc_pk1_val = mod_pk1_value
639 and mod_doc_source_name = c_mod_doc_source
640 and column_name = c_column_name
641 and nvl(orig_value, '*') = nvl(c_orig_value ,'*')
642 and nvl(mod_value, '*') = nvl(c_mod_value ,'*')
643 )
644 )
645 GROUP BY group_line_id, rn
646
647 ORDER By orderseq, fromline
648 );
649
650 l_finalXML XMLTYPE;
651 l_ctr number := 0;
652 rollup_pk CLOB;
653 l_sql_stmt CLOB;
654
655 BEGIN
656
657 IF p_document_type IS NOT NULL AND p_entity_name IS NOT NULL AND mod_pk1_value IS NOT NULL THEN
658
659 --for each rollup change
660 FOR r_change IN c_rollup_changes
661 LOOP
662 l_ctr := 0;
663 rollup_pk := '';
664
665 -- For a rollup change, we have to get all the line numbers and concatenate to form a final printable string.
666 FOR r_pk IN c_rollup_pks(r_change.mod_doc_source_name, r_change.column_name, r_change.orig_value, r_change.mod_value)
667 LOOP
668 IF l_ctr > 0 then
669 rollup_pk := rollup_pk || ', ';
670 END IF;
671
672 rollup_pk := rollup_pk || r_pk.line_display;
673 l_ctr := l_ctr + 1;
674 END LOOP;
675
676 SELECT XMLConcat(l_finalXML, XMLElement("ATTRIBUTE" , XMLAttributes(r_change.label_message_code as "name",
677 r_change.disp_data_type as "datatype",
678 r_change.sub_entity_name as "sub_entity"),
679 XMLElement("orig_id", r_change.orig_value),
680 XMLElement("mod_id", r_change.mod_value),
681 XMLElement("orig_value", r_change.orig_value_desc),
682 XMLElement("mod_value", r_change.mod_value_desc),
683 XMLElement("printable_pks", fnd_message.get_string('PON', 'PON_FED_AMEND_LINE_TITLE_MSG') || ' ' || rollup_pk )))
684 INTO l_finalXML from dual;
685 END LOOP;
686 ELSE
687 --if document_type or entity_name is not passed, then we have to pass empty xml instead of null
688 --becasue getClobVal() on this return XML is used, if it is null getClobVal() function will error out.
689 l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
690 execute immediate l_sql_stmt into l_finalXML;
691 END IF;
692
693 RETURN l_finalXML;
694 END getLineSummaryDiffXML;
695
696
697 PROCEDURE find_rollup (p_document_type VARCHAR2,
698 p_entity_name VARCHAR2,
699 base_pk1_value NUMBER,
700 base_pk2_value NUMBER,
701 base_pk3_value NUMBER,
702 base_pk4_value NUMBER,
703 base_pk5_value NUMBER,
704 mod_pk1_value NUMBER,
705 mod_pk2_value NUMBER,
706 mod_pk3_value NUMBER,
707 mod_pk4_value NUMBER,
708 mod_pk5_value NUMBER ) IS
709
710 CURSOR c_entities IS
711 SELECT DISTINCT entity_name, base_doc_source_name, mod_doc_source_name FROM po_diff_config WHERE document_type = p_document_type and nvl(rollup_eligibility_flag, 'N') = 'Y' and nvl(ignore_source_flag, 'N') = 'N';
712
713 CURSOR c_sources IS
714 SELECT DISTINCT base_doc_source_name, mod_doc_source_name FROM po_diff_config WHERE document_type = p_document_type and entity_name = p_entity_name and nvl(rollup_eligibility_flag, 'N') = 'Y' and nvl(ignore_source_flag, 'N') = 'N';
715
716 BEGIN
717
718 IF p_entity_name = 'ALL' THEN
719 FOR REC IN c_entities LOOP
720 generate_rollup (p_document_type,
721 REC.entity_name,
722 REC.base_doc_source_name,
723 base_pk1_value,
724 base_pk2_value,
725 base_pk3_value,
726 base_pk4_value,
727 base_pk5_value,
728 REC.mod_doc_source_name,
729 mod_pk1_value,
730 mod_pk2_value,
731 mod_pk3_value,
732 mod_pk4_value,
733 mod_pk5_value );
734 END LOOP;
735 ELSE
736 FOR REC IN c_sources LOOP
737 generate_rollup (p_document_type,
738 p_entity_name,
739 REC.base_doc_source_name,
740 base_pk1_value,
741 base_pk2_value,
742 base_pk3_value,
743 base_pk4_value,
744 base_pk5_value,
745 REC.mod_doc_source_name,
746 mod_pk1_value,
747 mod_pk2_value,
748 mod_pk3_value,
749 mod_pk4_value,
750 mod_pk5_value );
751 END LOOP;
752 END IF;
753
754 END;
755
756
757
758 PROCEDURE generate_rollup (p_document_type VARCHAR2,
759 p_entity_name VARCHAR2,
760 p_base_doc_source VARCHAR2,
761 base_pk1_value NUMBER,
762 base_pk2_value NUMBER,
763 base_pk3_value NUMBER,
764 base_pk4_value NUMBER,
765 base_pk5_value NUMBER,
766 p_mod_doc_source VARCHAR2,
767 mod_pk1_value NUMBER,
768 mod_pk2_value NUMBER,
769 mod_pk3_value NUMBER,
770 mod_pk4_value NUMBER,
771 mod_pk5_value NUMBER) IS
772
773 l_rc SYS_REFCURSOR;
774 l_rfc_stmt CLOB;
775 l_insert_stmt CLOB;
776
777 l_groupby_stmt VARCHAR2(1000);
778 l_where_stmt VARCHAR2(2000);
779
780 l_column_name VARCHAR2(60);
781 l_orig_value VARCHAR2(4000);
782 l_mod_value VARCHAR2(4000);
783
784
785 BEGIN
786
787 --forming base doc where clause
788 IF base_pk1_value IS NOT NULL THEN
789 l_where_stmt := 'd.base_doc_pk1_val = ' || base_pk1_value;
790 END IF;
791 IF base_pk2_value IS NOT NULL THEN
792 IF l_where_stmt IS NOT NULL THEN
793 l_where_stmt := l_where_stmt || ' and d.base_doc_pk2_val = ' || base_pk2_value;
794 ELSE
795 l_where_stmt := 'd.base_doc_pk2_val = ' || base_pk2_value;
796 END IF;
797 END IF;
798 IF base_pk3_value IS NOT NULL THEN
799 IF l_where_stmt IS NOT NULL THEN
800 l_where_stmt := l_where_stmt || ' and d.base_doc_pk3_val = ' || base_pk3_value;
801 ELSE
802 l_where_stmt := 'd.base_doc_pk3_val = ' || base_pk3_value;
803 END IF;
804 END IF;
805 IF base_pk4_value IS NOT NULL THEN
806 IF l_where_stmt IS NOT NULL THEN
807 l_where_stmt := l_where_stmt || ' and d.base_doc_pk4_val = ' || base_pk4_value;
808 ELSE
809 l_where_stmt := 'd.base_doc_pk4_val = ' || base_pk4_value;
810 END IF;
811 END IF;
812 IF base_pk5_value IS NOT NULL THEN
813 IF l_where_stmt IS NOT NULL THEN
814 l_where_stmt := l_where_stmt || ' and d.base_doc_pk5_val = ' || base_pk5_value;
815 ELSE
816 l_where_stmt := 'd.base_doc_pk5_val = ' || base_pk5_value;
817 END IF;
818 END IF;
819
820 --forming mod doc where clause
821 IF mod_pk1_value IS NOT NULL THEN
822 IF l_where_stmt IS NOT NULL THEN
823 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk1_val = ' || mod_pk1_value;
824 ELSE
825 l_where_stmt := 'd.mod_doc_pk1_val = ' || mod_pk1_value;
826 END IF;
827 END IF;
828 IF mod_pk2_value IS NOT NULL THEN
829 IF l_where_stmt IS NOT NULL THEN
830 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk2_val = ' || mod_pk2_value;
831 ELSE
832 l_where_stmt := 'd.mod_doc_pk2_val = ' || mod_pk2_value;
833 END IF;
834 END IF;
835 IF mod_pk3_value IS NOT NULL THEN
836 IF l_where_stmt IS NOT NULL THEN
837 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk3_val = ' || mod_pk3_value;
838 ELSE
839 l_where_stmt := 'd.mod_doc_pk3_val = ' || mod_pk3_value;
840 END IF;
841 END IF;
842 IF mod_pk4_value IS NOT NULL THEN
843 IF l_where_stmt IS NOT NULL THEN
844 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk4_val = ' || mod_pk4_value;
845 ELSE
846 l_where_stmt := 'd.mod_doc_pk4_val = ' || mod_pk4_value;
847 END IF;
848 END IF;
849 IF mod_pk5_value IS NOT NULL THEN
850 IF l_where_stmt IS NOT NULL THEN
851 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk5_val = ' || mod_pk5_value;
852 ELSE
853 l_where_stmt := 'd.mod_doc_pk5_val = ' || mod_pk5_value;
854 END IF;
855 END IF;
856
857
858 IF l_where_stmt IS NOT NULL THEN
859
860 l_groupby_stmt := 'd.column_name, d.orig_value, d.mod_value';
861 l_where_stmt := l_where_stmt || ' and d.document_type = ''' || p_document_type ||
862 ''' and d.entity_name = ''' || p_entity_name ||
863 ''' and d.mod_doc_source_name = ''' || p_mod_doc_source || '''';
864
865 --sql stmt to get the rollup changes
866 l_rfc_stmt := 'SELECT d.column_name, d.orig_value, d.mod_value from pon_entity_differences d, po_diff_config c
867 where ' || l_where_stmt ||
868 ' and c.column_name = d.column_name
869 and nvl(c.rollup_eligibility_flag, ''N'') = ''Y''
870 and c.document_type = '''|| p_document_type ||'''
871 and c.entity_name = '''|| p_entity_name ||'''
872 and c.mod_doc_source_name = '''|| p_mod_doc_source ||'''
873 and nvl(c.ignore_source_flag, ''N'') = ''N''
874 group by '|| l_groupby_stmt ||' HAVING count(*) > 1 ';
875
876
877 --sql stmt to get the rollup changes rows and populating it to the gt table
878 l_insert_stmt := 'INSERT INTO po_entity_rollup_gt (
879 document_type, entity_name, mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val,
880 base_doc_pk3_val, base_doc_pk4_val, base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val,
881 mod_doc_pk3_val, mod_doc_pk4_val, mod_doc_pk5_val, column_name, mod_value, orig_value,
882 mod_value_desc, orig_value_desc, creation_date, created_by, last_update_date, last_updated_by)
883 SELECT document_type, entity_name, mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val,
884 base_doc_pk3_val, base_doc_pk4_val, base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val,
885 mod_doc_pk3_val, mod_doc_pk4_val, mod_doc_pk5_val, column_name, mod_value, orig_value,
886 mod_value_desc, orig_value_desc, creation_date, created_by, last_update_date, last_updated_by
887 from pon_entity_differences d
888
889 WHERE '|| l_where_stmt ||' and column_name = :1 and nvl(orig_value, ''*'') = nvl(:2, ''*'') and nvl(mod_value, ''*'') = nvl(:3, ''*'') ';
890
891 OPEN l_rc FOR l_rfc_stmt ;
892 LOOP
893 FETCH l_rc INTO l_column_name, l_orig_value, l_mod_value;
894 EXIT WHEN l_rc%NOTFOUND;
895
896 execute immediate l_insert_stmt USING l_column_name, l_orig_value, l_mod_value;
897 END LOOP;
898
899 END IF;
900 END;
901
902 END;
903