[Home] [Help]
PACKAGE BODY: APPS.AZ_COMP_REPORTER
Source
1 PACKAGE BODY az_comp_reporter AS
2 /* $Header: azcompreporterb.pls 120.23 2011/05/19 06:40:49 halla ship $ */
3 -- Private type declarations
4 -- type <TypeName> is <Datatype>
5
6 -- Private constant declarations
7 --DIFF_SCHEMA_URL CONSTANT VARCHAR2(4000) :='http://isetup.oracle.com/2006/diffresultdata.xsd';
8 diff_schema_url VARCHAR2(4000);
9
10 exclude_details VARCHAR2(1);
11 commit_batch_size NUMBER;
12 v_dml_count NUMBER;
13
14 c_log_head constant VARCHAR2(30) := 'az.plsql.AZ_COMP_REPORTER.';
15 output_a_stylesheet constant VARCHAR2(4000) := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
16 <xsl:template match="/">
17 <H>
18 <xsl:apply-templates/>
19 </H>
20 </xsl:template>
21 <xsl:template match="V">
22 <xsl:element name="V">
23 <xsl:attribute name="N">
24 <xsl:value-of select="@N" />
25 </xsl:attribute>
26 <xsl:attribute name="D">
27 <xsl:value-of select="@D" />
28 </xsl:attribute>
29 <xsl:if test="@U=''Y''">
30 <xsl:attribute name="U">
31 <xsl:value-of select="@U" />
32 </xsl:attribute>
33 </xsl:if >
34 <xsl:copy-of select="A" />
35 <B></B>
36 </xsl:element>
37 </xsl:template>
38 </xsl:stylesheet>';
39
40 output_b_stylesheet constant VARCHAR2(4000) := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
41 <xsl:template match="/">
42 <H>
43 <xsl:apply-templates/>
44 </H>
45 </xsl:template>
46 <xsl:template match="V">
47 <xsl:element name="V">
48 <xsl:attribute name="N">
49 <xsl:value-of select="@N" />
50 </xsl:attribute>
51 <xsl:attribute name="D">
52 <xsl:value-of select="@D" />
53 </xsl:attribute>
54 <xsl:if test="@U=''Y''">
55 <xsl:attribute name="U">
56 <xsl:value-of select="@U" />
57 </xsl:attribute>
58 </xsl:if >
59 <A>
60 </A>
61 <xsl:copy-of select="B" />
62 </xsl:element>
63 </xsl:template>
64 </xsl:stylesheet>';
65
66 --v_prim_xmltype xmltype:= xmltype.createXml(PRIM_STYLESHEET);
67 v_a_xmltype xmltype := xmltype.createxml(output_a_stylesheet);
68 v_b_xmltype xmltype := xmltype.createxml(output_b_stylesheet);
69
70 -- <ConstantName> constant <Datatype> := <Value>
71
72 -- Private variable declarations
73 -- <VariableName> <Datatype>
74
75 -- Function and procedure implementations
76
77 /*********************Procedure declarations*************************************/
78 PROCEDURE output_df(p_request_id IN NUMBER, p_source IN VARCHAR2, p_depth IN NUMBER, p_results_id IN OUT nocopy NUMBER, p_results_pid IN NUMBER, p_data_pid_a IN NUMBER, p_data_pid_b IN NUMBER);
79
80 PROCEDURE output_a_only(p_request_id IN NUMBER, p_source IN VARCHAR2, p_depth IN NUMBER, p_results_id IN OUT nocopy NUMBER, p_results_pid IN NUMBER, p_data_id IN NUMBER);
81
82 PROCEDURE output_b_only(p_request_id IN NUMBER, p_source IN VARCHAR2, p_depth IN NUMBER, p_results_id IN OUT nocopy NUMBER, p_results_pid IN NUMBER, p_data_id IN NUMBER);
83
84 PROCEDURE update_for_show_only_diff(p_request_id IN NUMBER, p_source IN VARCHAR2);
85
86 PROCEDURE raise_error_msg(errcode IN NUMBER, errmsg IN VARCHAR2, procedurename IN VARCHAR2, statement IN VARCHAR2);
87
88 PROCEDURE commit_if_required;
89
90 PROCEDURE update_diff_type_counts(p_request_id IN NUMBER, p_source IN VARCHAR2);
91
92 /**********************************************************/
93
94 PROCEDURE compare(p_request_id IN NUMBER, p_source IN VARCHAR2, p_diff_schema_url IN VARCHAR2, p_exclude_details IN VARCHAR2) IS
95
96 v_am_name_a VARCHAR2(4000);
97 v_am_disp_name_a VARCHAR2(4000);
98 v_results_id NUMBER := 1;
99 -- id for az_diff_results
100 -- v_attr_clob CLOB;
101 v_hcd_a VARCHAR2(4000);
102 l_api_name constant VARCHAR2(30) := 'compare : ';
103 BEGIN
104
105 diff_schema_url := p_diff_schema_url;
106 exclude_details := p_exclude_details;
107 commit_batch_size := fnd_profile.VALUE('AZ_COMMIT_ROWCOUNT');
108 v_dml_count := 0;
109
110 SELECT name,
111 display_name,
112 hashcode_details
113 INTO v_am_name_a,
114 v_am_disp_name_a,
115 v_hcd_a
116 FROM az_reporter_data
117 WHERE request_id = p_request_id
118 AND source = p_source
119 AND id = 1
120 AND type = -1
121 AND is_primary = 'Y';
122
123 EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=''SIMILAR''';
124
125 INSERT
126 INTO az_diff_results(name, display_name, request_id, source, type, id, parent_id, hashcode_details, depth, is_different, is_transformed, show_only_diff, param2, attr_diff)
127 VALUES(v_am_name_a, -- name
128 v_am_disp_name_a, p_request_id, p_source, -1, -- type
129 v_results_id, 0, -- parent_id
130 v_hcd_a, 1, -- depth
131 'N', -- isdifferent
132 'N', -- istransformed
133 'N', -- show_only_diff
134 'Y', -- exclude details for AM
135 NULL);
136 -- attr_diff
137
138 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
139 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name || to_char(systimestamp), 'Inserting into az_diff_results for request_id: '
140 || p_request_id || ' source: ' || p_source);
141 END IF;
142
143 v_dml_count := v_dml_count + 1;
144 commit_if_required;
145
146 UPDATE az_reporter_data
147 SET deleted_flag = 'Y'
148 WHERE request_id = p_request_id
149 AND source = p_source
150 AND id = 1
151 AND is_primary = 'N';
152 -- delete root AM in B
153
154 v_dml_count := v_dml_count + 1;
155 commit_if_required;
156
157 v_results_id := v_results_id + 2;
158 -- v_results_id 2 is skipped for Details of AM (which is not there)
159 output_df(p_request_id, p_source,
160
161 /* v_attr_clob,*/ 2,
162
163 /*depth*/ v_results_id, 1
164
165 /*v_results_pid*/, 1
166
167 /*v_data_pid_A*/, 1
168
169 /*v_data_pid_B*/);
170 COMMIT;
171 update_for_show_only_diff(p_request_id, p_source);
172 ---LMATHUR - need to update the individual counts based on the number of differences
173 update_diff_type_counts(p_request_id, p_source);
174 COMMIT;
175
176 EXCEPTION
177 WHEN application_exception THEN
178 RAISE;
179 WHEN others THEN
180 raise_error_msg(SQLCODE, sqlerrm, 'compare', 'procedure end');
181 END compare;
182
183 /* functionality of output_DF - output its children
184 functionality of output_A_only - output this and its children
185 */ PROCEDURE output_df(p_request_id IN NUMBER, p_source IN VARCHAR2, p_depth IN NUMBER, p_results_id IN OUT nocopy NUMBER, p_results_pid IN NUMBER, p_data_pid_a IN NUMBER, p_data_pid_b IN NUMBER) IS
186 -- its children have this ref entity counter
187
188 v_matching_vo_id_b NUMBER;
189 v_count NUMBER;
190 v_type NUMBER;
191 v_results_pid NUMBER;
192
193 v_children_a_name_list typ_nest_tab_varchar;
194 v_children_a_disp_name_list typ_nest_tab_varchar;
195 v_children_a_id_list typ_nest_tab_number;
196 v_children_a_hc_list typ_nest_tab_number;
197 v_children_a_hcd_list typ_nest_tab_varchar;
198
199 v_children_b_id_list typ_nest_tab_number;
200 v_children_b_hc_list typ_nest_tab_number;
201 v_children_b_hcd_list typ_nest_tab_varchar;
202
203 v_matching_vos_of_b_list typ_nest_tab_number;
204 v_attr_str VARCHAR2(32767);
205 v_amount INTEGER;
206 v_is_different VARCHAR2(1) := 'N';
207 v_show_only_diff VARCHAR2(1) := 'N';
208 v_debug_str VARCHAR2(32767);
209 v_temp_xmltype xmltype;
210
211 v_exclude_details VARCHAR2(1);
212 v_exclude_details_temp VARCHAR2(1);
213 l_api_name constant VARCHAR2(30) := 'output_DF : ';
214 l_log_query VARCHAR2(32000);
215 BEGIN
216 SELECT name,
217 display_name,
218 id,
219 hashcode,
220 hashcode_details bulk collect
221 INTO v_children_a_name_list,
222 v_children_a_disp_name_list,
223 v_children_a_id_list,
224 v_children_a_hc_list,
225 v_children_a_hcd_list
226 FROM az_reporter_data
227 WHERE request_id = p_request_id
228 AND source = p_source
229 AND parent_id = p_data_pid_a
230 AND is_primary = 'Y';
231
232 IF v_children_a_id_list.COUNT <> 0 THEN
233 FOR i IN 1 .. v_children_a_id_list.COUNT
234 LOOP
235 BEGIN
236 -- get matching VO of B
237 SELECT id
238 INTO v_matching_vo_id_b
239 FROM az_reporter_data
240 WHERE request_id = p_request_id
241 AND source = p_source
242 AND hashcode = v_children_a_hc_list(i)
243 AND parent_id = p_data_pid_b
244 AND is_primary = 'N'
245 AND deleted_flag = 'N'
246 AND rownum = 1;
247
248 UPDATE az_reporter_data
249 SET deleted_flag = 'Y'
250 WHERE request_id = p_request_id
251 AND source = p_source
252 AND id = v_matching_vo_id_b
253 AND is_primary = 'N';
254
255 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
256 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
257 || to_char(systimestamp), 'Updating v_children_A_ID_List_I: '
258 || v_children_a_hc_list(i) || ' p_data_pid_B: ' || p_data_pid_b
259 || ' p_request_id: ' || p_request_id || ' v_matching_VO_id_B: ' || v_matching_vo_id_b);
260 END IF;
261
262 v_dml_count := v_dml_count + 1;
263 commit_if_required;
264 --------------------------------------------------------------------------------
265
266 v_debug_str := '->A ID=' || v_children_a_id_list(i) || ',B ID=' || v_matching_vo_id_b;
267
268 SELECT xmlquery('for $a in 1
269 let $common := for $i in $PRIM/H/V, $j in $SEC/H/V
270 where ($i/@N eq $j/@N)
274 else <V N="{$i/@N}" D="{$i/@D}">{($i/A)}{($j/B)}</V>
271 return
272 if ($i/@U = "Y")
273 then <V N="{$i/@N}" D="{$i/@D}" U="Y">{($i/A)}{($j/B)}</V>
275 let $exsec := for $j in $SEC/H/V
276 return
277 if (some $i in $common satisfies $i/@N = $j/@N)
278 then ""
279 else $j
280 let $exprim := for $j in $PRIM/H/V
281 return
282 if (some $i in $common satisfies $i/@N = $j/@N)
283 then ""
284 else $j
285 return <H>{$common}{$exprim}{$exsec}</H>' passing
286 PRIM.attributes as "PRIM", SEC.attributes as "SEC"
287 returning content).createSchemaBasedXml(DIFF_SCHEMA_URL)
288 into v_temp_xmltype
289 from az_reporter_data PRIM,az_reporter_data SEC
290 where PRIM.request_id = SEC.request_id
291 and PRIM.source = SEC.source
292 and PRIM.request_id = p_request_id
293 and PRIM.source = p_source
294 and PRIM.id=v_children_A_ID_List(i)
295 and PRIM.is_primary='Y'
296 and SEC.id = v_matching_VO_id_B
297 and SEC.is_primary = 'N';
298
299 /* a is null/empty and b is not empty) or (b is null/empty and a is not empty) or (a is not equal to b) */
300 select decode(existsNode(v_temp_xmltype,'/H/V[((not(./A/text()!="") and ./B/text()!="") or (not(./B/text()!="") and ./A/text()!="") or (./A/text()!=./B/text())) and not(@U="Y")]'),1,'C','N')
301 INTO v_is_different
302 FROM dual;
303
304 SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
305 INTO v_exclude_details_temp
306 FROM dual;
307
308 IF(v_exclude_details_temp = '0' OR exclude_details = 'Y') THEN
309 v_exclude_details := 'Y';
310 ELSE
311 v_exclude_details := 'N';
312 END IF;
313
314 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
315 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
316 || to_char(systimestamp), 'Constructing exclude details and is different '
317 || ' v_is_different: ' || v_is_different || ' v_exclude_details_temp: '
318 || v_exclude_details_temp || ' v_exclude_details: ' || v_exclude_details);
319 END IF;
320
321 v_debug_str := v_debug_str || ',is_different=' || v_is_different;
322 ---------------------------------------------------------------------------------
323
324 IF p_results_pid = 1 THEN
325 v_type := 0;
326 ELSE
327 v_type := 2;
328 END IF;
329
330 INSERT
331 INTO az_diff_results(name, display_name, request_id, source, type, id, parent_id, hashcode_details, depth, is_different, is_transformed, show_only_diff, param2, attr_diff)
332 VALUES(v_children_a_name_list(i), v_children_a_disp_name_list(i), p_request_id, p_source, v_type, p_results_id, p_results_pid, v_children_a_hcd_list(i), p_depth, -- depth
333 v_is_different, -- isdifferent
334 'N', -- istransformed
335 decode(v_is_different, 'C', 'Y', 'N'), --show only diff--v_show_only_diff, -- show_only_diff
336 v_exclude_details, v_temp_xmltype);
337
338 v_dml_count := v_dml_count + 1;
339 commit_if_required;
340
341 v_results_pid := p_results_id;
342 p_results_id := p_results_id + 2;
343 v_is_different := 'N';
344 v_show_only_diff := 'N';
345 -- ************************************************************************
346
347 output_df(p_request_id, p_source, p_depth + 1, p_results_id, v_results_pid
348
349 /*results_pid*/, v_children_a_id_list(i)
350
351 /*data_pid_A*/, v_matching_vo_id_b
352
353 /*data_pid_B*/);
354
355 EXCEPTION
356 WHEN no_data_found THEN
357 output_a_only(p_request_id, p_source, p_depth, p_results_id, p_results_pid, v_children_a_id_list(i)
358
359 /*p_data_id_A*/);
360 WHEN application_exception THEN
361 RAISE;
362 WHEN others THEN
363 raise_error_msg(SQLCODE, sqlerrm, 'output_DF', 'get matching VO of B, collect attributes and insert');
364 END;
365 END LOOP;
366
367 -- children A cursor loop
368 END IF;
369
370 -- children A count > 0
371
372 SELECT id bulk collect
373 INTO v_children_b_id_list
374 FROM az_reporter_data
375 WHERE request_id = p_request_id
376 AND source = p_source
377 AND parent_id = p_data_pid_b
378 AND is_primary = 'N'
379 AND deleted_flag = 'N';
380
381 IF v_children_b_id_list.COUNT <> 0 THEN
382 FOR i IN 1 .. v_children_b_id_list.COUNT
383 LOOP
384 output_b_only(p_request_id, p_source, p_depth, p_results_id, p_results_pid, v_children_b_id_list(i)
385
386 /*p_data_id_B*/);
387
388 END LOOP;
389 END IF;
390
391 ----------------------------------------------
392
393 EXCEPTION
394 WHEN application_exception THEN
395 RAISE;
396 WHEN others THEN
397 raise_error_msg(SQLCODE, sqlerrm, 'output_DF', 'procedure end');
398 END;
399
400 -- output p_data_id and its children
401
405 v_disp_name_a VARCHAR2(4000);
402 PROCEDURE output_a_only(p_request_id IN NUMBER, p_source IN VARCHAR2, p_depth IN NUMBER, p_results_id IN OUT nocopy NUMBER, p_results_pid IN NUMBER, p_data_id IN NUMBER) IS
403
404 v_name_a VARCHAR2(4000);
406 v_id_a NUMBER;
407 v_hc_a NUMBER;
408 v_hcd_a VARCHAR2(4000);
409 v_results_pid NUMBER;
410
411 v_children_a_id_list typ_nest_tab_number;
412 v_temp_xmltype xmltype;
413 v_amount INTEGER;
414 v_type NUMBER;
415
416 v_exclude_details VARCHAR2(1);
417 v_exclude_details_temp VARCHAR2(1);
418 l_api_name constant VARCHAR2(30) := 'output_A_only : ';
419 BEGIN
420 -- output this
421
422 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
423 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
424 || to_char(systimestamp), 'Called output_A_only with ' || ' p_request_id: '
425 || p_request_id || ' p_source: ' || p_source || ' p_depth: ' || p_depth
426 || ' p_data_id: ' || p_data_id);
427 END IF;
428
429 SELECT name,
430 display_name,
431 id,
432 hashcode,
433 hashcode_details
434 INTO v_name_a,
435 v_disp_name_a,
436 v_id_a,
437 v_hc_a,
438 v_hcd_a
439 FROM az_reporter_data
440 WHERE request_id = p_request_id
441 AND source = p_source
442 AND id = p_data_id
443 AND is_primary = 'Y';
444
445 SELECT d.attributes.transform(v_a_xmltype)
446 INTO v_temp_xmltype
447 FROM az_reporter_data d
448 WHERE request_id = p_request_id
449 AND source = p_source
450 AND id = p_data_id
451 AND is_primary = 'Y';
452
453 SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
454 INTO v_exclude_details_temp
455 FROM dual;
456
457 IF(v_exclude_details_temp = '0' OR exclude_details = 'Y') THEN
458 v_exclude_details := 'Y';
459 ELSE
460 v_exclude_details := 'N';
461 END IF;
462
463 IF p_results_pid = 1 THEN
464 v_type := 0;
465 ELSE
466 v_type := 2;
467 END IF;
468
469 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
470 -- IF(LENGTH(v_temp_xmltype.getClobVal()) > 0 ) THEN
471 -- fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
472 -- || to_char(systimestamp), ' v_temp_xmltype: ' || v_temp_xmltype.getclobval());
473 -- END IF;
474 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
475 || to_char(systimestamp), 'v_exclude_details: ' || v_exclude_details || ' p_results_pid: ' || p_results_pid);
476 END IF;
477
478 INSERT
479 INTO az_diff_results(name, display_name, request_id, source, type, id, parent_id, hashcode_details, depth, is_different, is_transformed, show_only_diff, param2, attr_diff)
480 VALUES(v_name_a, v_disp_name_a, p_request_id, p_source, v_type, p_results_id, p_results_pid, v_hcd_a, p_depth, -- depth
481 'A', -- isdifferent
482 'N', -- istransformed
483 'Y', -- show_diff_only
484 v_exclude_details, xmltype(v_temp_xmltype.getclobval(), diff_schema_url, 1, 1));
485
486 v_dml_count := v_dml_count + 1;
487 commit_if_required;
488
489 v_results_pid := p_results_id;
490 p_results_id := p_results_id + 2;
491
492 -- output children
493 SELECT id bulk collect
494 INTO v_children_a_id_list
495 FROM az_reporter_data
496 WHERE request_id = p_request_id
497 AND source = p_source
498 AND parent_id = p_data_id
499 AND is_primary = 'Y';
500
501 IF v_children_a_id_list.COUNT <> 0 THEN
502 FOR i IN 1 .. v_children_a_id_list.COUNT
503 LOOP
507 -- children A cursor
504 output_a_only(p_request_id, p_source, p_depth + 1, p_results_id, v_results_pid, v_children_a_id_list(i));
505 END LOOP;
506
508 END IF;
509
510 EXCEPTION
511 WHEN application_exception THEN
512 RAISE;
513 WHEN others THEN
514 raise_error_msg(SQLCODE, sqlerrm, 'output_A_only', 'procedure end');
515 END;
516
517 -- output p_data_id and its children
518
519 PROCEDURE output_b_only(p_request_id IN NUMBER, p_source IN VARCHAR2, p_depth IN NUMBER, p_results_id IN OUT nocopy NUMBER, p_results_pid IN NUMBER, p_data_id IN NUMBER) IS
520
521 v_name_b VARCHAR2(4000);
522 v_disp_name_b VARCHAR2(4000);
523 v_id_b NUMBER;
524 v_hc_b NUMBER;
525 v_hcd_b VARCHAR2(4000);
526 v_results_pid NUMBER;
527
528 v_children_b_id_list typ_nest_tab_number;
529
530 v_attr_str VARCHAR2(32767);
531 v_amount INTEGER;
532 v_type NUMBER;
533 v_temp_xmltype xmltype;
534 v_exclude_details VARCHAR2(1);
535 v_exclude_details_temp VARCHAR2(1);
536 l_api_name constant VARCHAR2(30) := 'output_B_only : ';
537 BEGIN
538 -- output this
539
540 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
541 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
542 || to_char(systimestamp), 'Called output_B_only with ' || ' p_request_id: ' || p_request_id
543 || ' p_source: ' || p_source || ' p_depth: ' || p_depth || ' p_results_pid: ' || p_results_pid
544 || ' p_data_id: ' || p_data_id);
545 END IF;
546
547 SELECT name,
548 display_name,
549 id,
550 hashcode,
551 hashcode_details
552 INTO v_name_b,
553 v_disp_name_b,
554 v_id_b,
555 v_hc_b,
556 v_hcd_b
557 FROM az_reporter_data
558 WHERE request_id = p_request_id
559 AND source = p_source
560 AND id = p_data_id
561 AND is_primary = 'N';
562
563 SELECT d.attributes.transform(v_b_xmltype)
564 INTO v_temp_xmltype
565 FROM az_reporter_data d
566 WHERE request_id = p_request_id
567 AND source = p_source
568 AND id = p_data_id
569 AND is_primary = 'N';
570
571 SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
572 INTO v_exclude_details_temp
573 FROM dual;
574
575 IF(v_exclude_details_temp = '0' OR exclude_details = 'Y') THEN
576 v_exclude_details := 'Y';
577 ELSE
578 v_exclude_details := 'N';
579 END IF;
580
581 IF p_results_pid = 1 THEN
582 v_type := 0;
583 ELSE
584 v_type := 2;
585 END IF;
586
587 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
588 IF(LENGTH(v_temp_xmltype.getClobVal()) > 0 ) THEN
589 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
590 || to_char(systimestamp), ' v_temp_xmltype: ' || v_temp_xmltype.getclobval());
591 END IF;
592 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
593 || to_char(systimestamp), 'v_exclude_details: ' || v_exclude_details || ' p_results_pid: '
594 || p_results_pid);
595 END IF;
596
597 INSERT
598 INTO az_diff_results(name, display_name, request_id, source, type, id, parent_id, hashcode_details, depth, is_different, is_transformed, show_only_diff, param2, attr_diff)
599 VALUES(v_name_b, v_disp_name_b, p_request_id, p_source, v_type, p_results_id, p_results_pid, v_hcd_b, p_depth, -- depth
600 'B', -- isdifferent
601 'N', -- istransformed
602 'Y', -- show_diff_only
603 v_exclude_details, xmltype(v_temp_xmltype.getclobval(), diff_schema_url, 1, 1));
604
605 v_dml_count := v_dml_count + 1;
606 commit_if_required;
607
608 UPDATE az_reporter_data
609 SET deleted_flag = 'Y'
610 WHERE request_id = p_request_id
611 AND source = p_source
612 AND id = v_id_b
613 AND is_primary = 'N';
614
615 v_dml_count := v_dml_count + 1;
616 commit_if_required;
617
618 v_results_pid := p_results_id;
619 p_results_id := p_results_id + 2;
620
621 -- output children
622 SELECT id bulk collect
623 INTO v_children_b_id_list
624 FROM az_reporter_data
625 WHERE request_id = p_request_id
626 AND source = p_source
627 AND parent_id = p_data_id
628 AND is_primary = 'N'
629 AND deleted_flag = 'N';
630
631 IF v_children_b_id_list.COUNT <> 0 THEN
632 FOR i IN 1 .. v_children_b_id_list.COUNT
633 LOOP
634 output_b_only(p_request_id, p_source, p_depth + 1, p_results_id, v_results_pid, v_children_b_id_list(i));
635 END LOOP;
636
637 -- children A cursor
638 END IF;
642 RAISE;
639
640 EXCEPTION
641 WHEN application_exception THEN
643 WHEN others THEN
644 raise_error_msg(SQLCODE, sqlerrm, 'output_B_only', 'procedure end');
645 END;
646
647 PROCEDURE update_for_show_only_diff(p_request_id IN NUMBER, p_source IN VARCHAR2) IS
648
649 v_different_id_list typ_nest_tab_number;
650 v_different_pid_list typ_nest_tab_number;
651 v_show_only_diff VARCHAR2(1);
652 v_parent_id NUMBER;
653 v_id NUMBER;
654 v_is_different VARCHAR2(1);
655 l_api_name constant VARCHAR2(30) := 'update_for_show_only_diff : ';
656 BEGIN
657 SELECT id,
658 parent_id bulk collect
659 INTO v_different_id_list,
660 v_different_pid_list
661 FROM az_diff_results
662 WHERE request_id = p_request_id
663 AND source = p_source
664 AND is_different <> 'N' -- gets you A, B or C
665 ORDER BY depth;
666
667 IF v_different_id_list.COUNT <> 0 THEN
668 FOR i IN 1 .. v_different_id_list.COUNT
669 LOOP
670 v_show_only_diff := 'N';
671 v_parent_id := v_different_pid_list(i);
672 LOOP
673 EXIT
674 WHEN v_show_only_diff = 'Y';
675 BEGIN
676 v_id := v_parent_id;
677 -- avoiding recursion
678 SELECT parent_id,
679 is_different,
680 show_only_diff
681 INTO v_parent_id,
685 WHERE request_id = p_request_id
682 v_is_different,
683 v_show_only_diff
684 FROM az_diff_results
686 AND source = p_source
687 AND id = v_id;
688
689 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level)
690 THEN
691 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
692 || to_char(systimestamp), 'v_different_ID_List_I: ' || v_different_id_list(i)
693 || ' v_different_PID_List: ' || v_different_pid_list(i) || ' v_parent_id: ' || v_parent_id ||
694 ' v_is_different: ' || v_is_different || ' v_show_only_diff: ' || v_show_only_diff);
695 END IF;
696
697 -- if is_different is A, B or C let it remain as it is
698 -- valid (is_different, show_only_diff) combinations till now - (A,Y), (B,Y), (C,Y), (N,N). introduce (D,Y) now.
699
700 IF v_is_different = 'N'
701 AND v_show_only_diff = 'N' THEN
702
703 UPDATE az_diff_results
704 SET show_only_diff = 'Y',
705 is_different = 'D'
706 WHERE request_id = p_request_id
707 AND source = p_source
708 AND id = v_id;
709
710 v_dml_count := v_dml_count + 1;
711 commit_if_required;
712
713 END IF;
714
715 EXCEPTION
716 WHEN no_data_found THEN
717 v_show_only_diff := 'Y';
718 -- for AM
719 WHEN others THEN
720 raise_error_msg(SQLCODE, sqlerrm, 'update_for_show_only_diff', 'select show_only_diff column of parents');
721 END;
722 END LOOP;
723 END LOOP;
724
725 -- for loop closes
726 END IF;
727
728 -- if count <>0
729
730 EXCEPTION
731 WHEN application_exception THEN
732 RAISE;
733 WHEN others THEN
734 raise_error_msg(SQLCODE, sqlerrm, 'update_for_show_only_diff', 'procedure end');
738 PROCEDURE raise_error_msg (ErrCode IN NUMBER,
735
736 END update_for_show_only_diff;
737
739 ErrMsg IN VARCHAR2,
740 ProcedureName IN VARCHAR2,
741 Statement IN VARCHAR2) IS
742
743 v_message VARCHAR2(2048);
744
745 BEGIN
746
747 if( FND_LOG.LEVEL_UNEXPECTED >=
748 FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
749 FND_MESSAGE.SET_NAME('AZ', 'AZ_R12_PLSQL_EXCEPTION'); -- Seeded Message
750 -- Runtime Information
751 FND_MESSAGE.SET_TOKEN('ERROR_CODE', ErrCode);
752 FND_MESSAGE.SET_TOKEN('ERROR_MESG', ErrMsg);
753 FND_MESSAGE.SET_TOKEN('ERROR_PROC', 'az_comp_reporter.' || ProcedureName);
754 IF (Statement IS NOT NULL) THEN
755 FND_MESSAGE.SET_TOKEN('ERROR_STMT', Statement);
756 ELSE
757 FND_MESSAGE.SET_TOKEN('ERROR_STMT', 'none');
758 END IF;
759 raise_application_error(-20001, FND_MESSAGE.GET);
760 end if;
761 END raise_error_msg;
762
763
764 PROCEDURE commit_if_required IS
765 BEGIN
766
767 IF MOD(v_dml_count, commit_batch_size) = 0 THEN
768 COMMIT;
769
770 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
771 fnd_log.string(fnd_log.level_statement, c_log_head || 'commit_if_required : ' || to_char(systimestamp), 'Committed transaction');
772 END IF;
773
774 END IF;
775
776 END commit_if_required;
777 --LMATHUR added to update the record count in selection set XML for the given source, based on the type of differences
778 PROCEDURE update_diff_type_counts(p_request_id IN NUMBER, p_source IN VARCHAR2) IS
779
780 v_diff_type_list typ_nest_tab_varchar;
781 v_diff_count_list typ_nest_tab_varchar;
782 v_transform_xml VARCHAR2(32767) := '';
783 l_api_name constant VARCHAR2(40) := 'update_diff_type_counts : ';
784 BEGIN
785
786 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
787 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name || to_char(systimestamp), 'update_diff_type_counts called with p_request_id: ' || p_request_id || ' p_source: ' || p_source);
788 END IF;
789
790 SELECT nvl(COUNT, 0),
791 e.column_value.getrootelement() AS
792 col_name bulk collect
793 INTO v_diff_count_list,
794 v_diff_type_list
795 FROM
796 (SELECT decode(is_different, 'A', 'P6', 'B', 'P7', 'C', 'P8', 'D', 'P8', 'N', 'P9') name,
797 COUNT(is_different) COUNT
798 FROM az_diff_results d
799 WHERE request_id = p_request_id
800 AND source = p_source
801 AND parent_id = 1
802 GROUP BY is_different)
803 k,
804 TABLE(xmlsequence(EXTRACT(xmltype('<Root><P6/><P7/><P8/><P9/></Root>'), '/Root/node()'))) e
805 WHERE e.column_value.getrootelement() = name(+);
806
807 FOR i IN 1 .. v_diff_type_list.COUNT
808 LOOP
809
810 v_transform_xml := v_transform_xml || '<xsl:when test="name(.)=''''' || v_diff_type_list(i) || '''''">
811 <xsl:copy>
812 <xsl:choose>
813 <xsl:when test="./../V[@N=''''EntityOccuranceCode'''' and .=''''' || p_source || ''''']">' || v_diff_count_list(i) || '</xsl:when>
814 <xsl:otherwise><xsl:value-of select="."/></xsl:otherwise>
815 </xsl:choose>
816 </xsl:copy>
817
818 </xsl:when>';
819
820 END LOOP;
821
822 IF LENGTH(v_transform_xml) > 0 THEN
823 v_transform_xml := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
824 <xsl:template match="EXT">
825 <xsl:copy>
826 <xsl:copy-of select="@*"/>
827 <xsl:apply-templates/>
828 </xsl:copy>
829 </xsl:template>
830 <xsl:template match="H">
831 <xsl:copy>
832 <xsl:copy-of select="@*"/>
833 <xsl:for-each select="*">
834 <xsl:choose>' || v_transform_xml || '<xsl:otherwise>
835 <xsl:copy-of select="."/>
836 </xsl:otherwise>
837 </xsl:choose>
838 </xsl:for-each>
839 </xsl:copy>
840 </xsl:template>
841 </xsl:stylesheet>';
842
843 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
844 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name ||
848
845 to_char(systimestamp), 'Query to update selection set with counts : update az_requests d set d.selection_set = d.selection_set.transform(xmltype(''' ||
846 v_transform_xml || ''')).createSchemabasedxml(d.selection_set.getSchemaURL()) WHERE request_id=' || p_request_id || ' and request_type=''C''');
847 END IF;
849 EXECUTE IMMEDIATE 'update az_requests d set d.selection_set = d.selection_set.transform(xmltype(''' || v_transform_xml || ''')).createSchemabasedxml(d.selection_set.getSchemaURL()) WHERE request_id=' || p_request_id || ' and request_type=''C''';
850 END IF;
851
852 EXCEPTION
853 WHEN no_data_found THEN
854
855 NULL;
856 WHEN application_exception THEN
857 RAISE;
858 WHEN others THEN
859 raise_error_msg(SQLCODE, sqlerrm, 'update_diff_type_counts', 'Error while updating the count based on type of differences');
860
861 END update_diff_type_counts;
862
863 END az_comp_reporter;