[Home] [Help]
PACKAGE BODY: APPS.PO_SGD_PKG
Source
4 FUNCTION get_addressxml(p_header_id number, p_draft_id number) RETURN CLOB AS
1 PACKAGE BODY PO_SGD_PKG AS
2 /* $Header: PO_SGD_PKG.plb 120.2.12020000.3 2013/02/10 15:05:31 vegajula ship $ */
3
5 l_where_sql varchar2(1000);
6 l_addr_sql varchar2(1000);
7 l_sql varchar2(4000);
8 l_col_name varchar2(50);
9 l_attr_grp_id number;
10 clob_addr CLOB;
11 c_addr SYS_REFCURSOR;
12 l_address varchar2(4000);
13 begin
14 l_where_sql := ' from po_headers_all_ext_vl where po_header_id = :1 and draft_id = :2';
15
16 begin
17 SELECT ag.attr_group_id,
18 efdfce.application_column_name
19 into l_attr_grp_id, l_col_name
20 FROM ego_fnd_dsc_flx_ctx_ext ag,
21 ego_fnd_df_col_usgs_ext efdfce,
22 fnd_descr_flex_column_usages fcu
23 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
24 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
25 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
26 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
27 and fcu.application_column_name = efdfce.application_column_name
28 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
29 and upper(ag.descriptive_flex_context_code) = upper('addresses')
30 and upper(fcu.end_user_column_name) = upper('addressdtlsxml');
31
32 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
33
34 l_addr_sql := 'select ' || l_col_name || ' as address ' ;
35
36 exception
37 when no_data_found then
38 l_addr_sql := '';
39 end;
40
41 if l_addr_sql is not null then
42 l_sql := l_addr_sql || l_where_sql;
43
44 dbms_lob.createtemporary(clob_addr, true);
45 dbms_lob.open(clob_addr, dbms_lob.lob_readwrite);
46 open c_addr for l_sql using p_header_id, p_draft_id, l_attr_grp_id;
47 loop
48 fetch c_addr into l_address;
49 exit when c_addr%notfound;
50 if l_address is not null then
51 dbms_lob.writeappend(clob_addr, length(l_address), l_address);
52 end if;
53 end loop;
54 dbms_lob.close(clob_addr);
55 end if;
56 RETURN(clob_addr);
57 END get_addressxml;
58
59
60 FUNCTION get_contactsxml(p_header_id number , p_draft_id number) RETURN CLOB AS
61 l_cnt_sql varchar2(1000);
62 l_where_sql varchar2(1000);
63 l_sql varchar2(4000);
64 l_cnt varchar2(4000);
65 l_col_name varchar2(50);
66 l_attr_grp_id number;
67 c_cnt SYS_REFCURSOR;
68 clob_cnt CLOB;
69
70 begin
71 l_where_sql := ' from po_headers_all_ext_vl where po_header_id = :1 and draft_id = :2';
72
73 begin
74 SELECT ag.attr_group_id,
75 efdfce.application_column_name
76 into l_attr_grp_id, l_col_name
77 FROM ego_fnd_dsc_flx_ctx_ext ag,
78 ego_fnd_df_col_usgs_ext efdfce,
79 fnd_descr_flex_column_usages fcu
80 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
81 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
82 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
83 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
84 and fcu.application_column_name = efdfce.application_column_name
85 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
86 and upper(ag.descriptive_flex_context_code) = upper('addresses')
87 and upper(fcu.end_user_column_name) = upper('contactdtlsxml');
88
89 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
90
91 l_cnt_sql := 'select ' || l_col_name || ' as contacts ' ;
92
93 exception
94 when no_data_found then
98 if l_cnt_sql is not null then
95 l_cnt_sql := '';
96 end;
97
99 l_sql := l_cnt_sql || l_where_sql;
100
101 dbms_lob.createtemporary(clob_cnt, true);
102 dbms_lob.open(clob_cnt , dbms_lob.lob_readwrite);
103 open c_cnt for l_sql using p_header_id, p_draft_id, l_attr_grp_id;
104 loop
105 fetch c_cnt into l_cnt;
106 exit when c_cnt%notfound;
107 if l_cnt is not null then
108 dbms_lob.writeappend(clob_cnt, length(l_cnt), l_cnt);
109 end if;
110 end loop;
111 dbms_lob.close(clob_cnt);
112 end if;
113 return(clob_cnt);
114 END get_contactsxml;
115
116
117 FUNCTION get_uda_attr_desc_sql(p_doc_type varchar2,
118 p_pk1_value number,
119 p_pk2_value number,
120 p_data_type varchar2,
121 p_appl_col_name varchar2,
122 p_end_user_col_name varchar2,
123 p_attr_group varchar2,
124 p_attr_id number)
125 return varchar2 is
126
127 p_application_id number := 201;
128 p_attr_group_type varchar2(200);
129 p_object_name varchar2(200);
130 p_pk1_column_name varchar2(200);
131 p_pk2_column_name varchar2(200) := 'DRAFT_ID';
132 l_sql CLOB;
133 begin
134 if p_doc_type = 'SHIPMENT' then
135 p_attr_group_type := 'PO_SHIPMENTS_EXT_ATTRS';
136 p_object_name := 'PO_LINE_LOCATIONS_ALL_EXT_B';
137 p_pk1_column_name := 'LINE_LOCATION_ID';
138 elsif p_doc_type = 'LINES' then
139 p_attr_group_type := 'PO_LINE_EXT_ATTRS';
140 p_object_name := 'PO_LINES_ALL_EXT_B';
141 p_pk1_column_name := 'PO_LINE_ID';
142 elsif p_doc_type = 'HEADER' then
143 p_attr_group_type := 'PO_HEADER_EXT_ATTRS';
144 p_object_name := 'PO_HEADERS_ALL_EXT_B';
145 p_pk1_column_name := 'PO_HEADER_ID';
146 end if;
147
148 if (p_data_type = 'C' or p_data_type = 'A' ) then
149 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
150 p_application_id || ',' || ' null, ' ||
151 p_appl_col_name|| ', null , ''' ||
152 p_end_user_col_name|| ''',''' ||
153 p_attr_group_type || ''',''' ||
154 P_ATTR_GROUP|| ''',' ||
155 p_attr_id || ',''' ||
156 p_object_name || ''',''' ||
157 p_pk1_column_name || ''',' ||
158 p_pk1_value || ',''' ||
159 p_pk2_column_name || ''',' ||
160 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC' ;
161 elsif p_data_type = 'N' then
165 p_end_user_col_name|| ''',''' ||
162 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
163 p_application_id || ',' || ' null, null, ' ||
164 p_appl_col_name|| ', ''' ||
166 p_attr_group_type || ''',''' ||
167 p_attr_group || ''',' ||
168 p_attr_id || ',''' ||
169 p_object_name || ''',''' ||
170 p_pk1_column_name || ''',' ||
171 p_pk1_value || ',''' ||
172 p_pk2_column_name || ''',' ||
173 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC' ;
174 elsif (p_data_type = 'X' or p_data_type = 'Y') then
175 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
176 p_application_id || ',' || p_appl_col_name ||
177 ' , null, null, ''' ||
178 p_end_user_col_name|| ''',''' ||
179 p_attr_group_type || ''',''' ||
180 p_attr_group || ''',' ||
181 p_attr_id || ',''' ||
182 p_object_name || ''',''' ||
183 p_pk1_column_name || ''',' ||
184 p_pk1_value || ',''' ||
185 p_pk2_column_name || ''',' ||
186 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC ' ;
187 end if;
188 return(l_sql);
189 end get_uda_attr_desc_sql;
190
191
192 FUNCTION get_uda_header_xml(p_headerId number, p_draft_id number) RETURN XMLType IS
193 CURSOR C_ATTR_GRP IS
194 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
195 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
196 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
197 AG.MULTI_ROW MULTI_ROW
198 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
199 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
200 WHERE POT.TEMPLATE_ID = POTU.TEMPLATE_ID
201 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
202 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
203 AND potu.attribute_group_id in (select distinct attr_group_id
204 from po_headers_all_ext_b
205 where po_header_id = p_headerId
206 and draft_id = p_draft_id and uda_template_id = potu.template_id
207 and attr_group_id = potu.attribute_group_id)
208 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
209
210 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
211 SELECT EFDFCE.ATTR_ID ,
212 EFDFCE.APPLICATION_COLUMN_NAME ,
213 FCU.END_USER_COLUMN_NAME,
214 fcu.flex_value_set_id,
215 EFDFCE.data_type
216 FROM
217 EGO_FND_DF_COL_USGS_EXT EFDFCE,
218 FND_DESCR_FLEX_COLUMN_USAGES FCU
219 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
220 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
221 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
222 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
223 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
224 AND FCU.DISPLAY_FLAG <> 'H';
225
226 L_SQL CLOB;
227 L_OP CLOB;
228 L_ATTR_GRP NUMBER;
229 L_CTR NUMBER;
230 L_O_CTR NUMBER;
231 l_uda_xml XMLTYPE;
232 BEGIN
233
234 L_O_CTR := 0;
235 FOR REC IN C_ATTR_GRP LOOP
236 IF REC.MULTI_ROW = 'Y' THEN
237 IF L_O_CTR > 0 THEN
238 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
239 || '", XMLAgg(XMLForest(' ;
240 ELSE
241 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLAgg(XMLForest(';
242 END IF;
243 ELSE
244 IF L_O_CTR > 0 THEN
245 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
246 || '", XMLForest(' ;
247 ELSE
248 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
249 END IF;
250 END IF;
251 L_CTR := 0 ;
252 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
253 IF L_CTR = 0 THEN
254 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
255 ELSE
256 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
257 END IF;
258 if r.flex_value_set_id is not null then
259 if (r.data_type = 'C' or r.data_type = 'A' ) then
260 l_sql := l_sql || ',' || get_uda_attr_desc_sql('HEADER',
261 p_headerId,
262 p_draft_Id,
263 r.data_type,
264 r.application_column_name,
265 r.end_user_column_name,
266 rec.attr_group,
267 r.ATTR_ID );
268 end if;
269 end if;
270 L_CTR := L_CTR + 1;
271 END LOOP;
272 IF REC.MULTI_ROW = 'Y' THEN
273 L_SQL := L_SQL || ')))';
274 ELSE
275 L_SQL := L_SQL || ' ))';
276 END IF;
277 L_SQL := L_SQL || ' from po_headers_all_ext_vl where po_header_id = ' ||
278 p_headerId || ' AND draft_id = ' || p_draft_id ||
279 ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
280 L_O_CTR := L_O_CTR + 1;
281 END LOOP;
282
283 l_op := ' select XMLConcat(' || l_sql || ') from dual';
287
284 execute immediate l_op into l_uda_xml;
285 RETURN (l_uda_xml);
286 END get_uda_header_xml;
288 FUNCTION get_uda_line_xml(p_po_line_id NUMBER, p_draft_id NUMBER)
289 RETURN XMLType IS
290 CURSOR C_ATTR_GRP IS
291 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
292 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
293 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
294 AG.MULTI_ROW MULTI_ROW
295 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
296 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
297 WHERE POT.TEMPLATE_ID = POTU.TEMPLATE_ID
298 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
299 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
300 AND potu.attribute_group_id in (select distinct attr_group_id
301 from po_lines_all_ext_b
302 where po_line_id = p_po_line_id
303 and draft_id = p_draft_Id
304 and attr_group_id = potu.attribute_group_id and uda_template_id = potu.template_id
305 and pk1_value IS NULL)
306 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
307
308 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
309 SELECT EFDFCE.ATTR_ID ,
310 EFDFCE.APPLICATION_COLUMN_NAME ,
311 FCU.END_USER_COLUMN_NAME,
312 fcu.flex_value_set_id,
313 EFDFCE.data_type
314 FROM
315 EGO_FND_DF_COL_USGS_EXT EFDFCE ,
316 FND_DESCR_FLEX_COLUMN_USAGES FCU
317 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
318 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
319 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
320 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
321 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
322 AND FCU.DISPLAY_FLAG <> 'H';
323
324 L_SQL CLOB;
325 L_OP CLOB;
326 L_ATTR_GRP NUMBER;
327 L_CTR NUMBER;
328 L_O_CTR NUMBER;
329 l_uda_xml XMLTYPE;
330 BEGIN
331
332 L_O_CTR := 0;
333 FOR REC IN C_ATTR_GRP LOOP
334 IF REC.MULTI_ROW = 'Y' THEN
335 IF L_O_CTR > 0 THEN
336 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
337 || '", XMLAgg(XMLForest(' ;
338 ELSE
339 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLAgg(XMLForest(';
340 END IF;
341 ELSE
342 IF L_O_CTR > 0 THEN
343 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
344 || '", XMLForest(' ;
345 ELSE
346 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
347 END IF;
348 END IF;
349 L_CTR := 0 ;
350 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
351 IF L_CTR = 0 THEN
352 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as '
353 || R.END_USER_COLUMN_NAME;
354 ELSE
355 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as '
356 || R.END_USER_COLUMN_NAME;
357 END IF;
358
359 if r.flex_value_set_id is not null then
360 l_sql := l_sql || ',' || get_uda_attr_desc_sql('LINES',
361 p_po_line_id,
362 p_draft_Id,
363 r.data_type,
364 r.application_column_name,
365 r.end_user_column_name,
366 rec.attr_group,
367 r.ATTR_ID );
368 end if;
369 L_CTR := L_CTR + 1;
370 END LOOP;
371 IF REC.MULTI_ROW = 'Y' THEN
372 L_SQL := L_SQL || ')))';
373 ELSE
374 L_SQL := L_SQL || ' ))';
375 END IF;
376 L_SQL := L_SQL || ' from po_lines_all_ext_vl where po_line_id = ' ||
377 p_po_line_id || ' AND draft_id = ' || p_draft_Id ||
378 ' AND pk1_value IS NULL and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
379 L_O_CTR := L_O_CTR + 1;
380 END LOOP;
381
382 l_op := ' select XMLConcat(' || l_sql || ') from dual';
383 execute immediate l_op into l_uda_xml;
384 RETURN (l_uda_xml);
385 END get_uda_line_xml;
386
387
391 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
388 FUNCTION get_uda_lineloc_xml(p_line_location_id number, p_draft_id number) RETURN XMLType IS
389 CURSOR C_ATTR_GRP IS
390 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
392 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
393 AG.MULTI_ROW MULTI_ROW
394 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
395 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
396 WHERE POT.TEMPLATE_ID = POTU.TEMPLATE_ID
397 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
398 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
399 AND potu.attribute_group_id in (select distinct attr_group_id
400 from po_line_locations_all_ext_b
401 where line_location_id = p_line_location_id
402 and draft_id = p_draft_Id and uda_template_id = potu.template_id
403 and attr_group_id = potu.attribute_group_id)
404 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
405
406 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
407 SELECT EFDFCE.ATTR_ID ,
408 EFDFCE.APPLICATION_COLUMN_NAME ,
409 FCU.END_USER_COLUMN_NAME,
410 fcu.flex_value_set_id,
411 EFDFCE.data_type
412 FROM
413 EGO_FND_DF_COL_USGS_EXT EFDFCE
414 , FND_DESCR_FLEX_COLUMN_USAGES FCU
415 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
416 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
417 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
418 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
419 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
420 AND FCU.DISPLAY_FLAG <> 'H';
421
422 L_SQL CLOB;
423 L_OP CLOB;
424 L_ATTR_GRP NUMBER;
425 L_CTR NUMBER;
426 L_O_CTR NUMBER;
427 l_uda_xml XMLTYPE;
428
429 BEGIN
430
431 L_O_CTR := 0;
432 FOR REC IN C_ATTR_GRP LOOP
433 IF REC.MULTI_ROW = 'Y' THEN
434 IF L_O_CTR > 0 THEN
435 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
436 || '", XMLAgg(XMLForest(' ;
437 ELSE
438 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP
439 || '", XMLAgg(XMLForest(';
440 END IF;
441 ELSE
442 IF L_O_CTR > 0 THEN
443 L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
444 || '", XMLForest(' ;
445 ELSE
446 L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
447 END IF;
448 END IF;
449 L_CTR := 0 ;
450 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
451 IF L_CTR = 0 THEN
452 L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as '
453 || R.END_USER_COLUMN_NAME;
454 ELSE
455 L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as '
456 || R.END_USER_COLUMN_NAME;
457 END IF;
458
459 if r.flex_value_set_id is not null then
460 l_sql := l_sql || ',' || get_uda_attr_desc_sql('SHIPMENT',
461 p_LINE_LOCATION_ID,
462 p_draft_Id,
463 r.data_type,
464 r.application_column_name,
465 r.end_user_column_name,
466 rec.attr_group,
467 r.ATTR_ID );
468 end if;
469 L_CTR := L_CTR + 1;
470 END LOOP;
471 IF REC.MULTI_ROW = 'Y' THEN
472 L_SQL := L_SQL || ')))';
473 ELSE
474 L_SQL := L_SQL || ' ))';
475 END IF;
476 L_SQL := L_SQL || ' from po_line_locations_all_ext_vl where line_location_id = ' ||
477 p_line_location_id || ' AND draft_id = ' || p_draft_id
478 || ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
479 L_O_CTR := L_O_CTR + 1;
480 END LOOP;
481
482 l_op := ' select XMLConcat(' || l_sql || ') from dual';
483 execute immediate l_op into l_uda_xml;
484 RETURN (l_uda_xml);
485
486 END get_uda_lineloc_xml;
487
488
489
490 FUNCTION get_shipaddressxml(p_line_location_id number, p_draft_id number) RETURN CLOB as
491
492 l_where_sql varchar2(1000);
493 l_addr_sql varchar2(1000);
494 l_sql varchar2(4000);
495 l_col_name varchar2(50);
496 l_attr_grp_id number;
497 clob_addr CLOB;
498 c_addr SYS_REFCURSOR;
499 l_address varchar2(4000);
500
501 BEGIN
502
503 l_where_sql := ' from po_line_locations_all_ext_vl where line_location_id = :1 and draft_id = :2';
504
505 begin
506 SELECT ag.attr_group_id,
507 efdfce.application_column_name
508 into l_attr_grp_id, l_col_name
509
510 FROM ego_fnd_dsc_flx_ctx_ext ag,
511 ego_fnd_df_col_usgs_ext efdfce,
512 fnd_descr_flex_column_usages fcu
513
514 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
515 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
516 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
517 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
518 and fcu.application_column_name = efdfce.application_column_name
522
519 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_SHIPMENTS_EXT_ATTRS'
520 and upper(ag.descriptive_flex_context_code) = upper('SHIP_INFO')
521 and upper(fcu.end_user_column_name) = upper('hiddShipAddXml');
523 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
524
525 l_addr_sql := 'select ' || l_col_name || ' as ship_address ' ;
526
527 exception
528 when no_data_found then
529 l_addr_sql := '';
530 end;
531
532 if l_addr_sql is not null then
533 l_sql := l_addr_sql || l_where_sql;
534 begin
535 execute immediate l_sql into clob_addr using p_line_location_id, p_draft_id,l_attr_grp_id ;
536 exception
537 when others then
538 select '' into clob_addr
539 from dual;
540 end;
541 end if;
542 RETURN(clob_addr);
543 END get_shipaddressxml;
544
545
546
547 FUNCTION getDiffXML(p_document_type VARCHAR2,
548 p_entity_name VARCHAR2,
549 mod_pk1_value NUMBER,
550 mod_pk2_value NUMBER,
551 mod_pk3_value NUMBER default null,
552 mod_pk4_value NUMBER default null,
553 mod_pk5_value NUMBER default null,
554 base_pk1_value NUMBER default null,
555 base_pk2_value NUMBER default null,
556 base_pk3_value NUMBER default null,
557 base_pk4_value NUMBER default null,
558 base_pk5_value NUMBER default null ) RETURN XMLType IS
559
560 l_where_stmt VARCHAR2(500);
561 l_sql_stmt CLOB;
562 l_xml XMLTYPE;
563
564 BEGIN
565
566
567 -- forming base where clause
568 IF base_pk1_value IS NOT NULL THEN
569 l_where_stmt := 'base_doc_pk1_val = ' || base_pk1_value;
570 END IF;
571 IF base_pk2_value IS NOT NULL THEN
572 IF l_where_stmt IS NOT NULL THEN
573 l_where_stmt := l_where_stmt || ' and base_doc_pk2_val = ' || base_pk2_value;
574 ELSE
575 l_where_stmt := 'base_doc_pk2_val = ' || base_pk2_value;
576 END IF;
577 END IF;
578 IF base_pk3_value IS NOT NULL THEN
579 IF l_where_stmt IS NOT NULL THEN
580 l_where_stmt := l_where_stmt || ' and base_doc_pk3_val = ' || base_pk3_value;
581 ELSE
582 l_where_stmt := 'base_doc_pk3_val = ' || base_pk3_value;
583 END IF;
584 END IF;
585 IF base_pk4_value IS NOT NULL THEN
586 IF l_where_stmt IS NOT NULL THEN
587 l_where_stmt := l_where_stmt || ' and base_doc_pk4_val = ' || base_pk4_value;
588 ELSE
589 l_where_stmt := 'base_doc_pk4_val = ' || base_pk4_value;
590 END IF;
591 END IF;
592 IF base_pk5_value IS NOT NULL THEN
593 IF l_where_stmt IS NOT NULL THEN
594 l_where_stmt := l_where_stmt || ' and base_doc_pk5_val = ' || base_pk5_value;
595 ELSE
596 l_where_stmt := 'base_doc_pk5_val = ' || base_pk5_value;
597 END IF;
598 END IF;
599
600
601 -- forming mod where clause
602 IF mod_pk1_value IS NOT NULL THEN
603 IF l_where_stmt IS NOT NULL THEN
604 l_where_stmt := l_where_stmt || ' and mod_doc_pk1_val = ' || mod_pk1_value;
605 ELSE
606 l_where_stmt := 'mod_doc_pk1_val = ' || mod_pk1_value;
607 END IF;
608 END IF;
609 IF mod_pk2_value IS NOT NULL THEN
610 IF l_where_stmt IS NOT NULL THEN
611 l_where_stmt := l_where_stmt || ' and mod_doc_pk2_val = ' || mod_pk2_value;
612 ELSE
613 l_where_stmt := 'mod_doc_pk2_val = ' || mod_pk2_value;
614 END IF;
615 END IF;
616 IF mod_pk3_value IS NOT NULL THEN
617 IF l_where_stmt IS NOT NULL THEN
618 l_where_stmt := l_where_stmt || ' and mod_doc_pk3_val = ' || mod_pk3_value;
619 ELSE
620 l_where_stmt := 'mod_doc_pk3_val = ' || mod_pk3_value;
621 END IF;
622 END IF;
623 IF mod_pk4_value IS NOT NULL THEN
624 IF l_where_stmt IS NOT NULL THEN
625 l_where_stmt := l_where_stmt || ' and mod_doc_pk4_val = ' || mod_pk4_value;
626 ELSE
627 l_where_stmt := 'mod_doc_pk4_val = ' || mod_pk4_value;
628 END IF;
629 END IF;
630 IF mod_pk5_value IS NOT NULL THEN
631 IF l_where_stmt IS NOT NULL THEN
632 l_where_stmt := l_where_stmt || ' and mod_doc_pk5_val = ' || mod_pk5_value;
633 ELSE
637
634 l_where_stmt := 'mod_doc_pk5_val = ' || mod_pk5_value;
635 END IF;
636 END IF;
638
639 IF l_where_stmt IS NOT NULL AND p_entity_name IS NOT NULL AND p_document_type IS NOT NULL THEN
640
641 l_where_stmt := l_where_stmt || ' and document_type = ''' || p_document_type || ''' and entity_name = ''' || p_entity_name ||'''';
642
643 -- forming the final sql stmt
644 l_sql_stmt := 'select XMLAgg(XMLElement("ATTRIBUTE" , XMLATTRIBUTES( nvl(fnd_message.get_string(''PO'', c.label_message_code), d.column_name) as "name",
645 c.disp_data_type as "datatype",
646 c.sub_entity_name as "sub_entity" ),
647 XMLELement("orig_id", d.orig_value),
648 XMLELement("mod_id", d.mod_value),
649 XMLELement("orig_value", decode(c.disp_data_type, ''N'', nvl2(d.orig_value, d.orig_value, 0),
650 ''M'', nvl2(d.orig_value, d.orig_value, 0),
651 nvl(d.orig_value_desc, d.orig_value)) ),
652 XMLELement("mod_value", decode(c.disp_data_type, ''N'', nvl2(d.mod_value, d.mod_value, 0),
653 ''M'', nvl2(d.mod_value, d.mod_value, 0),
654 nvl(d.mod_value_desc, d.mod_value)) ))
655 ORDER BY c.display_seq_number)
656
657
658 FROM (SELECT mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val, base_doc_pk3_val, base_doc_pk4_val,
659 base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val, mod_doc_pk3_val, mod_doc_pk4_val,
660 mod_doc_pk5_val, column_name, mod_value, orig_value, mod_value_desc, orig_value_desc
661
662 FROM po_entity_differences WHERE '|| l_where_stmt || '
663
664 MINUS
665
666 SELECT mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val, base_doc_pk3_val, base_doc_pk4_val,
667 base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val, mod_doc_pk3_val, mod_doc_pk4_val,
668 mod_doc_pk5_val, column_name, mod_value, orig_value, mod_value_desc, orig_value_desc
669
670 FROM po_entity_rollup_gt WHERE '|| l_where_stmt || '
671 ) d,
672
673 po_diff_config c
674
675 WHERE c.document_type = ''' || p_document_type
676 || ''' and c.entity_name = '''|| p_entity_name
677 || ''' and c.mod_doc_source_name = d.mod_doc_source_name
678 and c.column_name = d.column_name
679 and nvl(c.is_printable_flag, ''N'') = ''Y''
680 and nvl(c.ignore_source_flag,''N'') = ''N'' ';
681
682
683 execute immediate l_sql_stmt into l_xml;
684 END IF;
685
686 RETURN (l_xml);
687 END;
688
689
690
691 FUNCTION getLineSummaryDiffXML(p_document_type VARCHAR2,
692 p_entity_name VARCHAR2,
693 mod_pk1_value NUMBER,
694 mod_pk2_value NUMBER,
695 mod_pk3_value NUMBER default null,
696 mod_pk4_value NUMBER default null,
697 mod_pk5_value NUMBER default null,
698 base_pk1_value NUMBER default null,
699 base_pk2_value NUMBER default null,
700 base_pk3_value NUMBER default null,
701 base_pk4_value NUMBER default null,
702 base_pk5_value NUMBER default null ) RETURN XMLType IS
703
704 l_rollup_rc SYS_REFCURSOR;
705 l_linecont_rc SYS_REFCURSOR;
706
707 l_rollup_stmt VARCHAR2(2000);
708 l_linecont_stmt CLOB;
709
710 l_where_stmt VARCHAR2(500);
711 l_groupby_stmt VARCHAR2(200);
712 l_sql_stmt CLOB;
713 l_xml XMLTYPE;
714
715 l_colname VARCHAR2(60);
716 l_msgcode VARCHAR2(100);
717 l_origvalue VARCHAR2(4000);
718 l_modvalue VARCHAR2(4000);
719 l_origvalue_desc VARCHAR2(4000);
720 l_modvalue_desc VARCHAR2(4000);
721 l_datatype VARCHAR2(1);
722 l_subentity VARCHAR2(100);
723 l_moddoc_source VARCHAR2(30);
724
725
726 l_print_string CLOB;
727 l_fromline VARCHAR2(30);
728 l_toline VARCHAR2(30);
729 l_count NUMBER;
730 l_orderseq VARCHAR2(1);
731
732 BEGIN
733
734 --forming the base where clause
735 IF base_pk1_value IS NOT NULL THEN
736 l_where_stmt := 'base_doc_pk1_val = ' || base_pk1_value;
737 END IF;
738 IF base_pk2_value IS NOT NULL THEN
739 IF l_where_stmt IS NOT NULL THEN
740 l_where_stmt := l_where_stmt || ' and base_doc_pk2_val = ' || base_pk2_value;
741 ELSE
742 l_where_stmt := 'base_doc_pk2_val = ' || base_pk2_value;
743 END IF;
744 END IF;
745 IF base_pk3_value IS NOT NULL THEN
746 IF l_where_stmt IS NOT NULL THEN
747 l_where_stmt := l_where_stmt || ' and base_doc_pk3_val = ' || base_pk3_value;
748 ELSE
749 l_where_stmt := 'base_doc_pk3_val = ' || base_pk3_value;
750 END IF;
751 END IF;
752 IF base_pk4_value IS NOT NULL THEN
753 IF l_where_stmt IS NOT NULL THEN
757 END IF;
754 l_where_stmt := l_where_stmt || ' and base_doc_pk4_val = ' || base_pk4_value;
755 ELSE
756 l_where_stmt := 'base_doc_pk4_val = ' || base_pk4_value;
758 END IF;
759 IF base_pk5_value IS NOT NULL THEN
760 IF l_where_stmt IS NOT NULL THEN
761 l_where_stmt := l_where_stmt || ' and base_doc_pk5_val = ' || base_pk5_value;
762 ELSE
763 l_where_stmt := 'base_doc_pk5_val = ' || base_pk5_value;
764 END IF;
765 END IF;
766
767
768 -- formign the mod where clause
769 IF mod_pk1_value IS NOT NULL THEN
770 IF l_where_stmt IS NOT NULL THEN
771 l_where_stmt := l_where_stmt || ' and mod_doc_pk1_val = ' || mod_pk1_value;
772 ELSE
773 l_where_stmt := 'mod_doc_pk1_val = ' || mod_pk1_value;
774 END IF;
775 END IF;
776 IF mod_pk2_value IS NOT NULL THEN
777 IF l_where_stmt IS NOT NULL THEN
778 l_where_stmt := l_where_stmt || ' and mod_doc_pk2_val = ' || mod_pk2_value;
779 ELSE
780 l_where_stmt := 'mod_doc_pk2_val = ' || mod_pk2_value;
781 END IF;
782 END IF;
783 IF mod_pk3_value IS NOT NULL THEN
784 IF l_where_stmt IS NOT NULL THEN
785 l_where_stmt := l_where_stmt || ' and mod_doc_pk3_val = ' || mod_pk3_value;
786 ELSE
787 l_where_stmt := 'mod_doc_pk3_val = ' || mod_pk3_value;
788 END IF;
789 END IF;
790 IF mod_pk4_value IS NOT NULL THEN
791 IF l_where_stmt IS NOT NULL THEN
792 l_where_stmt := l_where_stmt || ' and mod_doc_pk4_val = ' || mod_pk4_value;
793 ELSE
794 l_where_stmt := 'mod_doc_pk4_val = ' || mod_pk4_value;
795 END IF;
796 END IF;
797 IF mod_pk5_value IS NOT NULL THEN
798 IF l_where_stmt IS NOT NULL THEN
799 l_where_stmt := l_where_stmt || ' and mod_doc_pk5_val = ' || mod_pk5_value;
800 ELSE
801 l_where_stmt := 'mod_doc_pk5_val = ' || mod_pk5_value;
802 END IF;
803 END IF;
804
805
806
807
811 l_groupby_stmt := 'd.mod_doc_source_name, d.column_name, d.orig_value, d.mod_value ';
808 IF l_where_stmt IS NOT NULL AND p_entity_name IS NOT NULL AND p_document_type IS NOT NULL THEN
809
810 l_where_stmt := l_where_stmt || ' and d.document_type = ''' || p_document_type || ''' and d.entity_name = ''' || p_entity_name ||'''';
812
813 --forming the final sql to get the rollup changes
814 l_rollup_stmt := 'SELECT d.mod_doc_source_name,
815 d.column_name,
816 nvl(fnd_message.get_string(''PO'', min(c.label_message_code)), d.column_name),
817 d.orig_value,
818 d.mod_value,
819 decode(min(c.disp_data_type), ''N'', nvl2(d.orig_value, d.orig_value, 0),
820 ''M'', nvl2(d.orig_value, d.orig_value, 0),
821 nvl(min(d.orig_value_desc), d.orig_value)),
822 decode(min(c.disp_data_type), ''N'', nvl2(d.mod_value, d.mod_value, 0),
823 ''M'', nvl2(d.mod_value, d.mod_value, 0),
824 nvl(min(d.mod_value_desc), d.mod_value)),
825 min(c.disp_data_type),
826 min(c.sub_entity_name)
827
828 FROM po_entity_rollup_gt d,
829 po_diff_config c
830
831 WHERE ' || l_where_stmt
832 || ' and c.document_type = ''' || p_document_type
836
833 || ''' and c.entity_name = '''|| p_entity_name
834 || ''' and c.mod_doc_source_name = d.mod_doc_source_name
835 and c.column_name = d.column_name
837 GROUP BY ' || l_groupby_stmt ||
838
839 ' ORDER BY min(c.display_seq_number)';
840
841
842 --looping thru the sql to form the xml for the summary change
843 OPEN l_rollup_rc FOR l_rollup_stmt;
844 LOOP
845 FETCH l_rollup_rc INTO l_moddoc_source, l_colname, l_msgcode, l_origvalue, l_modvalue, l_origvalue_desc, l_modvalue_desc, l_datatype, l_subentity ;
846 EXIT WHEN l_rollup_rc%NOTFOUND;
847
848 l_linecont_stmt := 'SELECT min(line) fromline, max(line) toline, count(*) count, ''A'' orderseq
849
850 FROM (select line_num_display line, line_num_display -row_number() over(order by to_number(line_num_display)) rn, group_line_id
851 FROM po_lines_draft_all
852 WHERE group_line_id is null
853 and po_header_id = ' || mod_pk1_value || '
854 and draft_id = ' || mod_pk2_value || '
855 and po_line_id in (select mod_doc_pk3_val FROM po_entity_rollup_gt d
856 WHERE ' || l_where_stmt || ' and mod_doc_source_name = :1 and column_name = :2
857 and nvl(orig_value, 1) = nvl(:3 ,1) and nvl(mod_value, 1) = nvl(:4 ,1) ))
858
859 GROUP BY group_line_id, rn
860
861 UNION ALL
862
863 SELECT min(line) fromline, max(line) toline, count(*) count, null orderseq
864
865 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
866 from po_lines_draft_all mod,
867 (select line_num_display, rank() over(partition by group_line_id order by line_num_display) rank, po_line_id from po_lines_all
868 where group_line_id is not null and po_header_id = ' || mod_pk1_value || ') base
869
870 WHERE mod.po_line_id = base.po_line_id
871 and mod.group_line_id is not null
872 and mod.po_header_id = ' || mod_pk1_value || '
873 and mod.draft_id = ' || mod_pk2_value || '
874 and mod.po_line_id in (select mod_doc_pk3_val FROM po_entity_rollup_gt d
875 WHERE ' || l_where_stmt || ' and mod_doc_source_name = :5 and column_name = :6
876 and nvl(orig_value, 1) = nvl(:7 ,1) and nvl(mod_value, 1) = nvl(:8 ,1) ))
877
878 GROUP BY group_line_id, rn
879
880 order by orderseq, fromline';
881
882
883 l_print_string := NULL;
884
885 OPEN l_linecont_rc FOR l_linecont_stmt USING l_moddoc_source, l_colname, l_origvalue, l_modvalue,
886 l_moddoc_source, l_colname, l_origvalue, l_modvalue;
887 LOOP
888 FETCH l_linecont_rc INTO l_fromline, l_toline, l_count, l_orderseq;
889 EXIT WHEN l_linecont_rc%NOTFOUND;
890
891 IF l_print_string IS NULL THEN
892 IF l_count = 1 THEN
893 l_print_string := 'LINES ' || l_fromline;
894 ELSE
895 l_print_string := 'LINES ' || l_fromline || '-' || l_toline;
896 END IF;
897 ELSE
898 IF l_count = 1 THEN
899 l_print_string := l_print_string || ', ' || l_fromline;
900 ELSE
901 l_print_string := l_print_string || ', ' || l_fromline || '-' || l_toline;
902 END IF;
903 END IF;
904
905 END LOOP;
906
907
908 IF l_sql_stmt IS NULL THEN
909 l_sql_stmt := '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
910 ''' || l_datatype || ''' as "datatype",
911 ''' || l_subentity || ''' as "sub_entity"),
912 XMLELement("orig_id", ''' || l_origvalue ||'''),
913 XMLELement("mod_id", ''' || l_modvalue ||'''),
914 XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
915 XMLELement("mod_value", ''' || l_modvalue_desc || '''),
916 XMLELement("printable_pks", ''' || l_print_string ||'''))
917 FROM dual) ';
918 ELSE
919 l_sql_stmt := l_sql_stmt || ',' || '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
920 ''' || l_datatype || ''' as "datatype",
921 ''' || l_subentity || ''' as "sub_entity"),
922 XMLELement("orig_id", ''' || l_origvalue ||'''),
923 XMLELement("mod_id", ''' || l_modvalue ||'''),
924 XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
925 XMLELement("mod_value", ''' || l_modvalue_desc || '''),
926 XMLELement("printable_pks", ''' || l_print_string ||'''))
927 FROM dual) ';
928 END IF;
929 END LOOP;
930
931
932 l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
933 execute immediate l_sql_stmt into l_xml;
934 END IF;
935
936 RETURN (l_xml);
937 END;
938
939
940 FUNCTION getLineLocSummaryDiffXML(p_document_type VARCHAR2,
941 p_entity_name VARCHAR2,
942 mod_pk1_value NUMBER,
943 mod_pk2_value NUMBER,
944 mod_pk3_value NUMBER default null,
945 mod_pk4_value NUMBER default null,
946 mod_pk5_value NUMBER default null,
947 base_pk1_value NUMBER default null,
948 base_pk2_value NUMBER default null,
949 base_pk3_value NUMBER default null,
950 base_pk4_value NUMBER default null,
951 base_pk5_value NUMBER default null ) RETURN XMLType IS
952
953 l_rollup_rc SYS_REFCURSOR;
954 l_shipno_rc SYS_REFCURSOR;
955 l_linecont_rc SYS_REFCURSOR;
956
960
957 l_rollup_stmt VARCHAR2(2000);
958 l_shipno_stmt VARCHAR2(500);
959 l_linecont_stmt CLOB;
961 l_where_stmt VARCHAR2(500);
962 l_groupby_stmt VARCHAR2(200);
963 l_sql_stmt CLOB;
964 l_xml XMLTYPE;
965
966 l_colname VARCHAR2(60);
967 l_msgcode VARCHAR2(100);
968 l_origvalue VARCHAR2(4000);
969 l_modvalue VARCHAR2(4000);
970 l_origvalue_desc VARCHAR2(4000);
971 l_modvalue_desc VARCHAR2(4000);
972 l_datatype VARCHAR2(1);
973 l_subentity VARCHAR2(100);
974 l_moddoc_source VARCHAR2(30);
975
976
977 l_print_string CLOB;
978 l_mod_pk4_disp VARCHAR2(30);
979
980 l_fromline VARCHAR2(30);
981 l_toline VARCHAR2(30);
982 l_count NUMBER;
983 l_orderseq VARCHAR2(1);
984 l_firsttime NUMBER;
985
986 BEGIN
987
988 --forming the base where clause
989 IF base_pk1_value IS NOT NULL THEN
990 l_where_stmt := 'base_doc_pk1_val = ' || base_pk1_value;
991 END IF;
992 IF base_pk2_value IS NOT NULL THEN
993 IF l_where_stmt IS NOT NULL THEN
994 l_where_stmt := l_where_stmt || ' and base_doc_pk2_val = ' || base_pk2_value;
995 ELSE
996 l_where_stmt := 'base_doc_pk2_val = ' || base_pk2_value;
997 END IF;
998 END IF;
999 IF base_pk3_value IS NOT NULL THEN
1000 IF l_where_stmt IS NOT NULL THEN
1001 l_where_stmt := l_where_stmt || ' and base_doc_pk3_val = ' || base_pk3_value;
1002 ELSE
1003 l_where_stmt := 'base_doc_pk3_val = ' || base_pk3_value;
1004 END IF;
1005 END IF;
1006 IF base_pk4_value IS NOT NULL THEN
1007 IF l_where_stmt IS NOT NULL THEN
1008 l_where_stmt := l_where_stmt || ' and base_doc_pk4_val = ' || base_pk4_value;
1009 ELSE
1010 l_where_stmt := 'base_doc_pk4_val = ' || base_pk4_value;
1011 END IF;
1012 END IF;
1013 IF base_pk5_value IS NOT NULL THEN
1014 IF l_where_stmt IS NOT NULL THEN
1015 l_where_stmt := l_where_stmt || ' and base_doc_pk5_val = ' || base_pk5_value;
1016 ELSE
1017 l_where_stmt := 'base_doc_pk5_val = ' || base_pk5_value;
1018 END IF;
1019 END IF;
1020
1021
1022 -- formign the mod where clause
1023 IF mod_pk1_value IS NOT NULL THEN
1024 IF l_where_stmt IS NOT NULL THEN
1025 l_where_stmt := l_where_stmt || ' and mod_doc_pk1_val = ' || mod_pk1_value;
1026 ELSE
1027 l_where_stmt := 'mod_doc_pk1_val = ' || mod_pk1_value;
1028 END IF;
1029 END IF;
1030 IF mod_pk2_value IS NOT NULL THEN
1031 IF l_where_stmt IS NOT NULL THEN
1032 l_where_stmt := l_where_stmt || ' and mod_doc_pk2_val = ' || mod_pk2_value;
1033 ELSE
1034 l_where_stmt := 'mod_doc_pk2_val = ' || mod_pk2_value;
1035 END IF;
1036 END IF;
1037 IF mod_pk3_value IS NOT NULL THEN
1038 IF l_where_stmt IS NOT NULL THEN
1039 l_where_stmt := l_where_stmt || ' and mod_doc_pk3_val = ' || mod_pk3_value;
1040 ELSE
1041 l_where_stmt := 'mod_doc_pk3_val = ' || mod_pk3_value;
1042 END IF;
1043 END IF;
1044 IF mod_pk4_value IS NOT NULL THEN
1045 IF l_where_stmt IS NOT NULL THEN
1046 l_where_stmt := l_where_stmt || ' and mod_doc_pk4_val = ' || mod_pk4_value;
1047 ELSE
1048 l_where_stmt := 'mod_doc_pk4_val = ' || mod_pk4_value;
1049 END IF;
1050 END IF;
1051 IF mod_pk5_value IS NOT NULL THEN
1052 IF l_where_stmt IS NOT NULL THEN
1053 l_where_stmt := l_where_stmt || ' and mod_doc_pk5_val = ' || mod_pk5_value;
1054 ELSE
1055 l_where_stmt := 'mod_doc_pk5_val = ' || mod_pk5_value;
1056 END IF;
1057 END IF;
1058
1059
1060
1061 IF l_where_stmt IS NOT NULL AND p_entity_name IS NOT NULL AND p_document_type IS NOT NULL THEN
1062
1063 l_where_stmt := l_where_stmt || ' and d.document_type = ''' || p_document_type || ''' and d.entity_name = ''' || p_entity_name ||'''';
1064 l_groupby_stmt := 'd.mod_doc_source_name, d.column_name, d.orig_value, d.mod_value ';
1065
1066 --forming the final sql to get the rollup changes
1067 l_rollup_stmt := 'SELECT d.mod_doc_source_name,
1068 d.column_name,
1069 nvl(fnd_message.get_string(''PO'', min(c.label_message_code)), d.column_name),
1070 d.orig_value,
1071 d.mod_value,
1072 decode(min(c.disp_data_type), ''N'', nvl2(d.orig_value, d.orig_value, 0),
1073 ''M'', nvl2(d.orig_value, d.orig_value, 0),
1074 nvl(min(d.orig_value_desc), d.orig_value)),
1075 decode(min(c.disp_data_type), ''N'', nvl2(d.mod_value, d.mod_value, 0),
1076 ''M'', nvl2(d.mod_value, d.mod_value, 0),
1077 nvl(min(d.mod_value_desc), d.mod_value)),
1078 min(c.disp_data_type),
1079 min(c.sub_entity_name)
1080
1081 FROM po_entity_rollup_gt d,
1082 po_diff_config c
1083
1084 WHERE ' || l_where_stmt
1085 || ' and c.document_type = ''' || p_document_type
1086 || ''' and c.entity_name = '''|| p_entity_name
1087 || ''' and c.mod_doc_source_name = d.mod_doc_source_name
1088 and c.column_name = d.column_name
1089
1090 GROUP BY ' || l_groupby_stmt ||
1094 -- forming the sql to find the rollup pk values
1091
1092 ' ORDER BY min(c.display_seq_number)';
1093
1095 l_shipno_stmt := 'SELECT pll.shipment_num
1096
1097 FROM po_entity_rollup_gt d, po_line_locations_all pll
1098
1099 WHERE ' || l_where_stmt
1100 || ' and mod_doc_source_name = :1
1101 and column_name = :2
1102 and nvl(orig_value, 1) = nvl(:3 ,1)
1103 and nvl(mod_value, 1) = nvl(:4 ,1)
1104 and d.mod_doc_pk4_val = pll.line_location_id
1105
1106 GROUP BY pll.shipment_num
1107
1108 ORDER BY pll.shipment_num';
1109
1110 --looping thru the sql to form the xml for the summary change
1111 OPEN l_rollup_rc FOR l_rollup_stmt;
1112 LOOP
1113 FETCH l_rollup_rc INTO l_moddoc_source, l_colname, l_msgcode, l_origvalue, l_modvalue, l_origvalue_desc, l_modvalue_desc, l_datatype, l_subentity ;
1114 EXIT WHEN l_rollup_rc%NOTFOUND;
1115
1116 l_print_string := NULL;
1117
1118 OPEN l_shipno_rc FOR l_shipno_stmt USING l_moddoc_source, l_colname, l_origvalue, l_modvalue;
1119 LOOP
1120 FETCH l_shipno_rc INTO l_mod_pk4_disp;
1121 EXIT WHEN l_shipno_rc%NOTFOUND;
1122
1123 l_linecont_stmt := 'SELECT min(line) fromline, max(line) toline, count(*) count, ''A'' orderseq
1124
1125 FROM (select line_num_display line, line_num_display -row_number() over(order by to_number(line_num_display)) rn, group_line_id
1126 FROM po_lines_draft_all
1127 WHERE group_line_id is null
1128 and po_header_id = ' || mod_pk1_value || '
1129 and draft_id = ' || mod_pk2_value || '
1130 and po_line_id in ( SELECT mod_doc_pk3_val FROM po_entity_rollup_gt d, po_line_locations_all pll
1134
1131 WHERE ' || l_where_stmt || ' and mod_doc_source_name = :1
1132 and column_name = :2 and nvl(orig_value, 1) = nvl(:3 ,1)
1133 and nvl(mod_value, 1) = nvl(:4 ,1) and d.mod_doc_pk4_val = pll.line_location_id and pll.shipment_num = :5 ) )
1135 GROUP BY group_line_id, rn
1136
1137 UNION ALL
1138
1139 SELECT min(line) fromline, max(line) toline, count(*) count, null orderseq
1140
1141 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
1142 from po_lines_draft_all mod,
1143 (select line_num_display, rank() over(partition by group_line_id order by line_num_display) rank, po_line_id from po_lines_all
1144 where group_line_id is not null and po_header_id = ' || mod_pk1_value || ') base
1145
1146 WHERE mod.po_line_id = base.po_line_id
1147 and mod.group_line_id is not null
1148 and mod.po_header_id = ' || mod_pk1_value || '
1149 and mod.draft_id = ' || mod_pk2_value || '
1150 and mod.po_line_id in (SELECT mod_doc_pk3_val FROM po_entity_rollup_gt d, po_line_locations_all pll
1151 WHERE ' || l_where_stmt || ' and mod_doc_source_name = :6
1152 and column_name = :7 and nvl(orig_value, 1) = nvl(:8 ,1)
1153 and nvl(mod_value, 1) = nvl(:9 ,1) and d.mod_doc_pk4_val = pll.line_location_id and pll.shipment_num = :10 ))
1154
1155 GROUP BY group_line_id, rn
1156
1157 order by orderseq, fromline';
1158
1159 IF l_print_string IS NULL THEN
1160 l_print_string := 'LINES ';
1161 ELSE
1162 l_print_string := l_print_string || ', LINES ';
1163 END IF;
1164
1165 l_firsttime := 1;
1166
1167 OPEN l_linecont_rc FOR l_linecont_stmt USING l_moddoc_source, l_colname, l_origvalue, l_modvalue, l_mod_pk4_disp,
1168 l_moddoc_source, l_colname, l_origvalue, l_modvalue, l_mod_pk4_disp;
1169 LOOP
1170 FETCH l_linecont_rc INTO l_fromline, l_toline, l_count, l_orderseq;
1171 EXIT WHEN l_linecont_rc%NOTFOUND;
1172
1173 IF l_firsttime = 1 THEN
1174 IF l_count = 1 THEN
1175 l_print_string := l_print_string || l_fromline;
1176 ELSE
1177 l_print_string := l_print_string || l_fromline || '-' || l_toline;
1178 END IF;
1179 l_firsttime := 2;
1180 ELSE
1181 IF l_count = 1 THEN
1182 l_print_string := l_print_string || ', ' || l_fromline;
1183 ELSE
1184 l_print_string := l_print_string || ', ' || l_fromline || '-' || l_toline;
1185 END IF;
1186 END IF;
1187
1188 END LOOP;
1189
1190 l_print_string := l_print_string || ' SHIP ' || l_mod_pk4_disp;
1191
1192 END LOOP;
1193
1194
1195 IF l_sql_stmt IS NULL THEN
1196 l_sql_stmt := '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
1197 ''' || l_datatype || ''' as "datatype",
1198 ''' || l_subentity || ''' as "sub_entity"),
1202 XMLELement("mod_value", ''' || l_modvalue_desc || '''),
1199 XMLELement("orig_id", ''' || l_origvalue ||'''),
1200 XMLELement("mod_id", ''' || l_modvalue ||'''),
1201 XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
1203 XMLELement("printable_pks", ''' || l_print_string ||'''))
1204 FROM dual) ';
1205 ELSE
1206 l_sql_stmt := l_sql_stmt || ',' || '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
1207 ''' || l_datatype || ''' as "datatype",
1208 ''' || l_subentity || ''' as "sub_entity"),
1209 XMLELement("orig_id", ''' || l_origvalue ||'''),
1210 XMLELement("mod_id", ''' || l_modvalue ||'''),
1211 XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
1212 XMLELement("mod_value", ''' || l_modvalue_desc || '''),
1213 XMLELement("printable_pks", ''' || l_print_string ||'''))
1214 FROM dual) ';
1215 END IF;
1216 END LOOP;
1217
1218 l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
1219 execute immediate l_sql_stmt into l_xml;
1220
1221 END IF;
1222 RETURN (l_xml);
1223 END;
1224
1225
1226 FUNCTION getDistSummaryDiffXML(p_document_type VARCHAR2,
1227 p_entity_name VARCHAR2,
1228 mod_pk1_value NUMBER,
1229 mod_pk2_value NUMBER,
1230 mod_pk3_value NUMBER default null,
1231 mod_pk4_value NUMBER default null,
1232 mod_pk5_value NUMBER default null,
1233 base_pk1_value NUMBER default null,
1234 base_pk2_value NUMBER default null,
1235 base_pk3_value NUMBER default null,
1236 base_pk4_value NUMBER default null,
1237 base_pk5_value NUMBER default null ) RETURN XMLType IS
1238
1239 l_rollup_rc SYS_REFCURSOR;
1240 l_shipno_rc SYS_REFCURSOR;
1241 l_linecont_rc SYS_REFCURSOR;
1242
1243 l_rollup_stmt VARCHAR2(2000);
1244 l_shipno_stmt VARCHAR2(1000);
1245 l_linecont_stmt CLOB;
1246
1247 l_where_stmt VARCHAR2(500);
1248 l_groupby_stmt VARCHAR2(200);
1249 l_sql_stmt CLOB;
1250 l_xml XMLTYPE;
1251
1252 l_colname VARCHAR2(60);
1253 l_msgcode VARCHAR2(100);
1254 l_origvalue VARCHAR2(4000);
1255 l_modvalue VARCHAR2(4000);
1256 l_origvalue_desc VARCHAR2(4000);
1257 l_modvalue_desc VARCHAR2(4000);
1258 l_datatype VARCHAR2(1);
1259 l_subentity VARCHAR2(100);
1260 l_moddoc_source VARCHAR2(30);
1261
1262
1263 l_print_string CLOB;
1264 l_mod_pk4_disp VARCHAR2(30);
1265 l_mod_pk5_disp VARCHAR2(30);
1266
1267 l_fromline VARCHAR2(30);
1268 l_toline VARCHAR2(30);
1269 l_count NUMBER;
1270 l_orderseq VARCHAR2(1);
1271 l_firsttime NUMBER;
1272
1273 BEGIN
1274
1275 --forming the base where clause
1276 IF base_pk1_value IS NOT NULL THEN
1277 l_where_stmt := 'base_doc_pk1_val = ' || base_pk1_value;
1278 END IF;
1279 IF base_pk2_value IS NOT NULL THEN
1280 IF l_where_stmt IS NOT NULL THEN
1281 l_where_stmt := l_where_stmt || ' and base_doc_pk2_val = ' || base_pk2_value;
1282 ELSE
1283 l_where_stmt := 'base_doc_pk2_val = ' || base_pk2_value;
1284 END IF;
1285 END IF;
1286 IF base_pk3_value IS NOT NULL THEN
1287 IF l_where_stmt IS NOT NULL THEN
1288 l_where_stmt := l_where_stmt || ' and base_doc_pk3_val = ' || base_pk3_value;
1289 ELSE
1290 l_where_stmt := 'base_doc_pk3_val = ' || base_pk3_value;
1291 END IF;
1292 END IF;
1293 IF base_pk4_value IS NOT NULL THEN
1294 IF l_where_stmt IS NOT NULL THEN
1295 l_where_stmt := l_where_stmt || ' and base_doc_pk4_val = ' || base_pk4_value;
1296 ELSE
1300 IF base_pk5_value IS NOT NULL THEN
1297 l_where_stmt := 'base_doc_pk4_val = ' || base_pk4_value;
1298 END IF;
1299 END IF;
1301 IF l_where_stmt IS NOT NULL THEN
1302 l_where_stmt := l_where_stmt || ' and base_doc_pk5_val = ' || base_pk5_value;
1303 ELSE
1304 l_where_stmt := 'base_doc_pk5_val = ' || base_pk5_value;
1305 END IF;
1306 END IF;
1307
1308
1309 -- formign the mod where clause
1310 IF mod_pk1_value IS NOT NULL THEN
1311 IF l_where_stmt IS NOT NULL THEN
1312 l_where_stmt := l_where_stmt || ' and mod_doc_pk1_val = ' || mod_pk1_value;
1313 ELSE
1314 l_where_stmt := 'mod_doc_pk1_val = ' || mod_pk1_value;
1315 END IF;
1316 END IF;
1317 IF mod_pk2_value IS NOT NULL THEN
1318 IF l_where_stmt IS NOT NULL THEN
1319 l_where_stmt := l_where_stmt || ' and mod_doc_pk2_val = ' || mod_pk2_value;
1320 ELSE
1321 l_where_stmt := 'mod_doc_pk2_val = ' || mod_pk2_value;
1322 END IF;
1323 END IF;
1324 IF mod_pk3_value IS NOT NULL THEN
1325 IF l_where_stmt IS NOT NULL THEN
1326 l_where_stmt := l_where_stmt || ' and mod_doc_pk3_val = ' || mod_pk3_value;
1327 ELSE
1328 l_where_stmt := 'mod_doc_pk3_val = ' || mod_pk3_value;
1329 END IF;
1330 END IF;
1331 IF mod_pk4_value IS NOT NULL THEN
1332 IF l_where_stmt IS NOT NULL THEN
1333 l_where_stmt := l_where_stmt || ' and mod_doc_pk4_val = ' || mod_pk4_value;
1334 ELSE
1335 l_where_stmt := 'mod_doc_pk4_val = ' || mod_pk4_value;
1336 END IF;
1337 END IF;
1338 IF mod_pk5_value IS NOT NULL THEN
1339 IF l_where_stmt IS NOT NULL THEN
1340 l_where_stmt := l_where_stmt || ' and mod_doc_pk5_val = ' || mod_pk5_value;
1341 ELSE
1342 l_where_stmt := 'mod_doc_pk5_val = ' || mod_pk5_value;
1343 END IF;
1344 END IF;
1345
1346
1347 IF l_where_stmt IS NOT NULL AND p_entity_name IS NOT NULL AND p_document_type IS NOT NULL THEN
1348
1349 l_where_stmt := l_where_stmt || ' and d.document_type = ''' || p_document_type || ''' and d.entity_name = ''' || p_entity_name ||'''';
1350 l_groupby_stmt := 'd.mod_doc_source_name, d.column_name, d.orig_value, d.mod_value ';
1351
1352 --forming the final sql to get the rollup changes
1353 l_rollup_stmt := 'SELECT d.mod_doc_source_name,
1354 d.column_name,
1355 nvl(fnd_message.get_string(''PO'', min(c.label_message_code)), d.column_name),
1356 d.orig_value,
1357 d.mod_value,
1358 decode(min(c.disp_data_type), ''N'', nvl2(d.orig_value, d.orig_value, 0),
1359 ''M'', nvl2(d.orig_value, d.orig_value, 0),
1360 nvl(min(d.orig_value_desc), d.orig_value)),
1361 decode(min(c.disp_data_type), ''N'', nvl2(d.mod_value, d.mod_value, 0),
1362 ''M'', nvl2(d.mod_value, d.mod_value, 0),
1363 nvl(min(d.mod_value_desc), d.mod_value)),
1364 min(c.disp_data_type),
1365 min(c.sub_entity_name)
1366
1367 FROM po_entity_rollup_gt d,
1368 po_diff_config c
1369
1370 WHERE ' || l_where_stmt
1371 || ' and c.document_type = ''' || p_document_type
1372 || ''' and c.entity_name = '''|| p_entity_name
1376 GROUP BY ' || l_groupby_stmt ||
1373 || ''' and c.mod_doc_source_name = d.mod_doc_source_name
1374 and c.column_name = d.column_name
1375
1377
1378 ' ORDER BY min(c.display_seq_number)';
1379
1380 -- forming the sql to find the rollup pk values
1381 l_shipno_stmt := 'SELECT pll.shipment_num,
1382 pd.distribution_num
1383
1384 FROM po_entity_rollup_gt d,
1385 po_line_locations_all pll,
1386 po_distributions_all pd
1387
1388 WHERE ' || l_where_stmt
1389 || ' and mod_doc_source_name = :1
1390 and column_name = :2
1391 and nvl(orig_value, 1) = nvl(:3 ,1)
1392 and nvl(mod_value, 1) = nvl(:4 ,1)
1393 and d.mod_doc_pk4_val = pll.line_location_id
1394 and d.mod_doc_pk5_val = pd.po_distribution_id
1395
1396 GROUP BY pll.shipment_num,
1397 pd.distribution_num
1398
1399 ORDER BY pll.shipment_num,
1400 pd.distribution_num ';
1401
1402
1403 --looping thru the sql to form the xml for the summary change
1404 OPEN l_rollup_rc FOR l_rollup_stmt;
1405 LOOP
1406 FETCH l_rollup_rc INTO l_moddoc_source, l_colname, l_msgcode, l_origvalue, l_modvalue, l_origvalue_desc, l_modvalue_desc, l_datatype, l_subentity ;
1407 EXIT WHEN l_rollup_rc%NOTFOUND;
1408
1409 l_print_string := NULL;
1410
1411 OPEN l_shipno_rc FOR l_shipno_stmt USING l_moddoc_source, l_colname, l_origvalue, l_modvalue;
1412 LOOP
1413 FETCH l_shipno_rc INTO l_mod_pk4_disp, l_mod_pk5_disp;
1414 EXIT WHEN l_shipno_rc%NOTFOUND;
1415
1416 l_linecont_stmt := 'SELECT min(line) fromline, max(line) toline, count(*) count, ''A'' orderseq
1417
1418 FROM (select line_num_display line, line_num_display -row_number() over(order by to_number(line_num_display)) rn, group_line_id
1419 FROM po_lines_draft_all
1420 WHERE group_line_id is null
1421 and po_header_id = ' || mod_pk1_value || '
1422 and draft_id = ' || mod_pk2_value || '
1423 and po_line_id in ( SELECT mod_doc_pk3_val FROM po_entity_rollup_gt d, po_line_locations_all pll, po_distributions_all pd
1424 WHERE ' || l_where_stmt || ' and mod_doc_source_name = :1 and column_name = :2
1425 and nvl(orig_value, 1) = nvl(:3 ,1) and nvl(mod_value, 1) = nvl(:4 ,1)
1426 and d.mod_doc_pk4_val = pll.line_location_id and d.mod_doc_pk5_val = pd.po_distribution_id
1427 and pll.shipment_num = :5 and pd.distribution_num = :6 ))
1428
1429 GROUP BY group_line_id, rn
1430
1431 UNION ALL
1432
1433 SELECT min(line) fromline, max(line) toline, count(*) count, null orderseq
1434
1435 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
1436 from po_lines_draft_all mod,
1437 (select line_num_display, rank() over(partition by group_line_id order by line_num_display) rank, po_line_id from po_lines_all
1438 where group_line_id is not null and po_header_id = ' || mod_pk1_value || ') base
1439
1440 WHERE mod.po_line_id = base.po_line_id
1441 and mod.group_line_id is not null
1442 and mod.po_header_id = ' || mod_pk1_value || '
1443 and mod.draft_id = ' || mod_pk2_value || '
1444 and mod.po_line_id in ( SELECT mod_doc_pk3_val FROM po_entity_rollup_gt d, po_line_locations_all pll, po_distributions_all pd
1445 WHERE ' || l_where_stmt || ' and mod_doc_source_name = :7 and column_name = :8
1446 and nvl(orig_value, 1) = nvl(:9 ,1) and nvl(mod_value, 1) = nvl(:10 ,1)
1447 and d.mod_doc_pk4_val = pll.line_location_id and d.mod_doc_pk5_val = pd.po_distribution_id
1448 and pll.shipment_num = :11 and pd.distribution_num = :12 ))
1449
1450 GROUP BY group_line_id, rn
1451
1452 order by orderseq, fromline';
1453
1454
1455 IF l_print_string IS NULL THEN
1456 l_print_string := 'LINES ';
1457 ELSE
1458 l_print_string := l_print_string || ', LINES ';
1459 END IF;
1460
1461 l_firsttime := 1;
1462
1463 OPEN l_linecont_rc FOR l_linecont_stmt USING l_moddoc_source, l_colname, l_origvalue, l_modvalue, l_mod_pk4_disp, l_mod_pk5_disp,
1464 l_moddoc_source, l_colname, l_origvalue, l_modvalue, l_mod_pk4_disp, l_mod_pk5_disp;
1465 LOOP
1466 FETCH l_linecont_rc INTO l_fromline, l_toline, l_count, l_orderseq;
1467 EXIT WHEN l_linecont_rc%NOTFOUND;
1468
1469 IF l_firsttime = 1 THEN
1470 IF l_count = 1 THEN
1471 l_print_string := l_print_string || l_fromline;
1472 ELSE
1473 l_print_string := l_print_string || l_fromline || '-' || l_toline;
1474 END IF;
1475 l_firsttime := 2;
1476 ELSE
1477 IF l_count = 1 THEN
1478 l_print_string := l_print_string || ', ' || l_fromline;
1479 ELSE
1480 l_print_string := l_print_string || ', ' || l_fromline || '-' || l_toline;
1481 END IF;
1482 END IF;
1483 END LOOP;
1484
1485 l_print_string := l_print_string || ' SHIP ' || l_mod_pk4_disp || ' DIST ' || l_mod_pk5_disp;
1486
1487 END LOOP;
1488
1489
1490 IF l_sql_stmt IS NULL THEN
1491 l_sql_stmt := '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
1492 ''' || l_datatype || ''' as "datatype",
1493 ''' || l_subentity || ''' as "sub_entity"),
1494 XMLELement("orig_id", ''' || l_origvalue ||'''),
1495 XMLELement("mod_id", ''' || l_modvalue ||'''),
1496 XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
1497 XMLELement("mod_value", ''' || l_modvalue_desc || '''),
1498 XMLELement("printable_pks", ''' || l_print_string ||'''))
1502 ''' || l_datatype || ''' as "datatype",
1499 FROM dual) ';
1500 ELSE
1501 l_sql_stmt := l_sql_stmt || ',' || '(select XMLElement("ATTRIBUTE" , XMLATTRIBUTES(''' || l_msgcode || ''' as "name",
1503 ''' || l_subentity || ''' as "sub_entity"),
1504 XMLELement("orig_id", ''' || l_origvalue ||'''),
1505 XMLELement("mod_id", ''' || l_modvalue ||'''),
1506 XMLELement("orig_value", ''' || l_origvalue_desc ||'''),
1507 XMLELement("mod_value", ''' || l_modvalue_desc || '''),
1508 XMLELement("printable_pks", ''' || l_print_string ||'''))
1509 FROM dual) ';
1510 END IF;
1511 END LOOP;
1512
1513
1514
1515 l_sql_stmt := 'select XMLConcat(' || l_sql_stmt || ') from dual';
1516 execute immediate l_sql_stmt into l_xml;
1517
1518
1519 END IF;
1520 RETURN (l_xml);
1521 END;
1522
1523
1524
1525 PROCEDURE find_rollup (p_document_type VARCHAR2,
1526 p_entity_name VARCHAR2,
1527 base_pk1_value NUMBER,
1528 base_pk2_value NUMBER,
1529 base_pk3_value NUMBER,
1530 base_pk4_value NUMBER,
1531 base_pk5_value NUMBER,
1532 mod_pk1_value NUMBER,
1533 mod_pk2_value NUMBER,
1534 mod_pk3_value NUMBER,
1535 mod_pk4_value NUMBER,
1536 mod_pk5_value NUMBER) IS
1537
1538 CURSOR c_entities IS
1539 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';
1540
1541 CURSOR c_sources IS
1542 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';
1543
1544 BEGIN
1545
1546 IF p_entity_name = 'ALL' THEN
1547 FOR REC IN c_entities LOOP
1548 generate_rollup (p_document_type,
1549 REC.entity_name,
1550 REC.base_doc_source_name,
1551 base_pk1_value,
1552 base_pk2_value,
1553 base_pk3_value,
1554 base_pk4_value,
1555 base_pk5_value,
1556 REC.mod_doc_source_name,
1557 mod_pk1_value,
1558 mod_pk2_value,
1559 mod_pk3_value,
1560 mod_pk4_value,
1561 mod_pk5_value );
1562 END LOOP;
1563 ELSE
1564 FOR REC IN c_sources LOOP
1565 generate_rollup (p_document_type,
1566 p_entity_name,
1567 REC.base_doc_source_name,
1568 base_pk1_value,
1569 base_pk2_value,
1570 base_pk3_value,
1571 base_pk4_value,
1572 base_pk5_value,
1573 REC.mod_doc_source_name,
1574 mod_pk1_value,
1575 mod_pk2_value,
1576 mod_pk3_value,
1577 mod_pk4_value,
1578 mod_pk5_value );
1579 END LOOP;
1580 END IF;
1581
1582 END;
1583
1584
1585
1586 PROCEDURE generate_rollup (p_document_type VARCHAR2,
1587 p_entity_name VARCHAR2,
1588 p_base_doc_source VARCHAR2,
1589 base_pk1_value NUMBER,
1590 base_pk2_value NUMBER,
1591 base_pk3_value NUMBER,
1592 base_pk4_value NUMBER,
1593 base_pk5_value NUMBER,
1594 p_mod_doc_source VARCHAR2,
1595 mod_pk1_value NUMBER,
1596 mod_pk2_value NUMBER,
1597 mod_pk3_value NUMBER,
1598 mod_pk4_value NUMBER,
1599 mod_pk5_value NUMBER) IS
1600
1601 l_rc SYS_REFCURSOR;
1602 l_rfc_stmt CLOB;
1603 l_insert_stmt CLOB;
1604
1605 l_groupby_stmt VARCHAR2(1000);
1606 l_where_stmt VARCHAR2(2000);
1607
1608 l_column_name VARCHAR2(60);
1609 l_orig_value VARCHAR2(4000);
1610 l_mod_value VARCHAR2(4000);
1611
1612
1613 BEGIN
1614
1615 --forming base doc where clause
1616 IF base_pk1_value IS NOT NULL THEN
1617 l_where_stmt := 'd.base_doc_pk1_val = ' || base_pk1_value;
1618 END IF;
1619 IF base_pk2_value IS NOT NULL THEN
1620 IF l_where_stmt IS NOT NULL THEN
1621 l_where_stmt := l_where_stmt || ' and d.base_doc_pk2_val = ' || base_pk2_value;
1622 ELSE
1623 l_where_stmt := 'd.base_doc_pk2_val = ' || base_pk2_value;
1624 END IF;
1625 END IF;
1626 IF base_pk3_value IS NOT NULL THEN
1627 IF l_where_stmt IS NOT NULL THEN
1628 l_where_stmt := l_where_stmt || ' and d.base_doc_pk3_val = ' || base_pk3_value;
1629 ELSE
1630 l_where_stmt := 'd.base_doc_pk3_val = ' || base_pk3_value;
1631 END IF;
1632 END IF;
1633 IF base_pk4_value IS NOT NULL THEN
1634 IF l_where_stmt IS NOT NULL THEN
1635 l_where_stmt := l_where_stmt || ' and d.base_doc_pk4_val = ' || base_pk4_value;
1636 ELSE
1637 l_where_stmt := 'd.base_doc_pk4_val = ' || base_pk4_value;
1638 END IF;
1639 END IF;
1640 IF base_pk5_value IS NOT NULL THEN
1641 IF l_where_stmt IS NOT NULL THEN
1642 l_where_stmt := l_where_stmt || ' and d.base_doc_pk5_val = ' || base_pk5_value;
1643 ELSE
1644 l_where_stmt := 'd.base_doc_pk5_val = ' || base_pk5_value;
1645 END IF;
1646 END IF;
1647
1648 --forming mod doc where clause
1649 IF mod_pk1_value IS NOT NULL THEN
1650 IF l_where_stmt IS NOT NULL THEN
1651 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk1_val = ' || mod_pk1_value;
1652 ELSE
1653 l_where_stmt := 'd.mod_doc_pk1_val = ' || mod_pk1_value;
1654 END IF;
1655 END IF;
1656 IF mod_pk2_value IS NOT NULL THEN
1657 IF l_where_stmt IS NOT NULL THEN
1661 END IF;
1658 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk2_val = ' || mod_pk2_value;
1659 ELSE
1660 l_where_stmt := 'd.mod_doc_pk2_val = ' || mod_pk2_value;
1662 END IF;
1663 IF mod_pk3_value IS NOT NULL THEN
1664 IF l_where_stmt IS NOT NULL THEN
1665 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk3_val = ' || mod_pk3_value;
1666 ELSE
1667 l_where_stmt := 'd.mod_doc_pk3_val = ' || mod_pk3_value;
1668 END IF;
1669 END IF;
1670 IF mod_pk4_value IS NOT NULL THEN
1671 IF l_where_stmt IS NOT NULL THEN
1672 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk4_val = ' || mod_pk4_value;
1673 ELSE
1674 l_where_stmt := 'd.mod_doc_pk4_val = ' || mod_pk4_value;
1675 END IF;
1676 END IF;
1677 IF mod_pk5_value IS NOT NULL THEN
1678 IF l_where_stmt IS NOT NULL THEN
1679 l_where_stmt := l_where_stmt || ' and d.mod_doc_pk5_val = ' || mod_pk5_value;
1680 ELSE
1681 l_where_stmt := 'd.mod_doc_pk5_val = ' || mod_pk5_value;
1682 END IF;
1683 END IF;
1684
1685
1686 IF l_where_stmt IS NOT NULL THEN
1687
1688 l_groupby_stmt := 'd.column_name, d.orig_value, d.mod_value';
1689 l_where_stmt := l_where_stmt || ' and d.document_type = ''' || p_document_type ||
1690 ''' and d.entity_name = ''' || p_entity_name ||
1691 ''' and d.mod_doc_source_name = ''' || p_mod_doc_source || '''';
1692
1693 --sql stmt to get the rollup changes
1694 l_rfc_stmt := 'SELECT d.column_name, d.orig_value, d.mod_value from po_entity_differences d, po_diff_config c
1695 where ' || l_where_stmt ||
1696 ' and c.column_name = d.column_name
1697 and nvl(c.rollup_eligibility_flag, ''N'') = ''Y''
1698 and c.document_type = '''|| p_document_type ||'''
1699 and c.entity_name = '''|| p_entity_name ||'''
1700 and c.mod_doc_source_name = '''|| p_mod_doc_source ||'''
1701 and nvl(c.ignore_source_flag, ''N'') = ''N''
1702 group by '|| l_groupby_stmt ||' HAVING count(*) > 1 ';
1703
1704
1705 --sql stmt to get the rollup changes rows and populating it to the gt table
1706 l_insert_stmt := 'INSERT INTO po_entity_rollup_gt (
1707 document_type, entity_name, mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val,
1708 base_doc_pk3_val, base_doc_pk4_val, base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val,
1709 mod_doc_pk3_val, mod_doc_pk4_val, mod_doc_pk5_val, column_name, mod_value, orig_value,
1710 mod_value_desc, orig_value_desc, creation_date, created_by, last_update_date, last_updated_by)
1711 SELECT document_type, entity_name, mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val,
1712 base_doc_pk3_val, base_doc_pk4_val, base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val,
1713 mod_doc_pk3_val, mod_doc_pk4_val, mod_doc_pk5_val, column_name, mod_value, orig_value,
1714 mod_value_desc, orig_value_desc, creation_date, created_by, last_update_date, last_updated_by
1715 from po_entity_differences d
1716 WHERE '|| l_where_stmt ||' and column_name = :1 and nvl(orig_value, 1) = nvl(:2, 1) and nvl(mod_value, 1) = nvl(:3, 1) ';
1717
1718 OPEN l_rc FOR l_rfc_stmt ;
1719 LOOP
1720 FETCH l_rc INTO l_column_name, l_orig_value, l_mod_value;
1721 EXIT WHEN l_rc%NOTFOUND;
1722
1723 execute immediate l_insert_stmt USING l_column_name, l_orig_value, l_mod_value;
1724 END LOOP;
1725
1726 END IF;
1727 END;
1728
1729
1730 FUNCTION get_req_numbers(p_header_id NUMBER, p_draft_id NUMBER, p_line_id NUMBER) RETURN XMLType IS
1731
1732 l_req_nums XMLType;
1733 BEGIN
1734
1735 SELECT XMLAgg(XMLElement("REF_PR_NUM", segment1))
1736 INTO l_req_nums
1737 FROM (SELECT distinct h.segment1
1738 FROM po_distributions_merge_v pod,
1739 po_req_distributions_all prd,
1740 po_requisition_headers_all h,
1741 po_requisition_lines_all l
1742 WHERE pod.po_header_id = p_header_id
1743 and pod.po_line_id = p_line_id
1744 and pod.draft_id = p_draft_id
1745 and pod.req_distribution_id = prd.distribution_id
1746 and prd.requisition_line_id = l.requisition_line_id
1747 and l.requisition_header_id = h.requisition_header_id);
1748
1749 RETURN l_req_nums;
1750 END get_req_numbers;
1751
1752
1753 FUNCTION get_bpa_req_numbers(p_auction_header_id NUMBER, p_auction_line_number NUMBER) RETURN XMLType IS
1754
1755 l_req_nums XMLType;
1756 BEGIN
1757
1758 SELECT XMLAgg(XMLElement("REF_PR_NUM", requisition_number))
1759 INTO l_req_nums
1760 FROM (SELECT distinct requisition_number
1761 FROM pon_backing_requisitions
1762 WHERE auction_header_id = p_auction_header_id
1763 and line_number = p_auction_line_number);
1764
1765 RETURN l_req_nums;
1766 END get_bpa_req_numbers;
1767
1768
1769 FUNCTION get_acrns(p_header_id NUMBER, p_draft_id NUMBER, p_line_id NUMBER) RETURN XMLType IS
1770
1771 l_acrns XMLType;
1772 BEGIN
1773 SELECT XMLAgg(XMLElement("ACRN", acrn))
1774 INTO l_acrns
1775 FROM (SELECT distinct pod.acrn
1776 FROM po_distributions_merge_v pod
1777 WHERE pod.po_header_id = p_header_id
1778 and pod.po_line_id = p_line_id
1779 and pod.draft_id = p_draft_id);
1780
1781 RETURN l_acrns;
1782 END get_acrns;
1783
1784 END;
1785