[Home] [Help]
PACKAGE BODY: APPS.AZ_COMP_REPORTER
Source
1 PACKAGE BODY az_comp_reporter AS
2 /* $Header: azcompreporterb.pls 120.20 2008/05/30 09:16:37 gnamasiv 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)
271 return
272 if ($i/@U = "Y")
273 then <V N="{$i/@N}" D="{$i/@D}" U="Y">{($i/A)}{($j/B)}</V>
274 else <V N="{$i/@N}" D="{$i/@D}">{($i/A)}{($j/B)}</V>
275 let $exsec := for $j in $SEC/H/V
276 return
277 if ($common/@N = $j/@N)
278 then ""
279 else $j
280 let $exprim := for $j in $PRIM/H/V
281 return
282 if ($common/@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 = p_request_id and PRIM.source = p_source
291 and PRIM.id=v_children_A_ID_List(i) and PRIM.is_primary='Y'
292 and SEC.request_id = p_request_id and SEC.source = p_source
293 and SEC.id = v_matching_VO_id_B
294 and SEC.is_primary = 'N';
295
296 SELECT decode(existsnode(v_temp_xmltype, '/H/V[./A/text()!=./B/text() and not(@U="Y")]'), 1, 'C', 'N')
297 INTO v_is_different
298 FROM dual;
299
300 SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
301 INTO v_exclude_details_temp
302 FROM dual;
303
304 IF(v_exclude_details_temp = '0' OR exclude_details = 'Y') THEN
305 v_exclude_details := 'Y';
306 ELSE
307 v_exclude_details := 'N';
308 END IF;
309
310 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
311 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
312 || to_char(systimestamp), 'Constructing exclude details and is different '
313 || ' v_is_different: ' || v_is_different || ' v_exclude_details_temp: '
314 || v_exclude_details_temp || ' v_exclude_details: ' || v_exclude_details);
315 END IF;
316
317 v_debug_str := v_debug_str || ',is_different=' || v_is_different;
318 ---------------------------------------------------------------------------------
319
320 IF p_results_pid = 1 THEN
321 v_type := 0;
322 ELSE
323 v_type := 2;
324 END IF;
325
326 INSERT
327 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)
328 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
329 v_is_different, -- isdifferent
330 'N', -- istransformed
331 decode(v_is_different, 'C', 'Y', 'N'), --show only diff--v_show_only_diff, -- show_only_diff
332 v_exclude_details, v_temp_xmltype);
333
334 v_dml_count := v_dml_count + 1;
335 commit_if_required;
336
337 v_results_pid := p_results_id;
338 p_results_id := p_results_id + 2;
339 v_is_different := 'N';
340 v_show_only_diff := 'N';
341 -- ************************************************************************
342
343 output_df(p_request_id, p_source, p_depth + 1, p_results_id, v_results_pid
344
345 /*results_pid*/, v_children_a_id_list(i)
346
347 /*data_pid_A*/, v_matching_vo_id_b
348
349 /*data_pid_B*/);
350
351 EXCEPTION
352 WHEN no_data_found THEN
353 output_a_only(p_request_id, p_source, p_depth, p_results_id, p_results_pid, v_children_a_id_list(i)
354
355 /*p_data_id_A*/);
356 WHEN application_exception THEN
357 RAISE;
358 WHEN others THEN
359 raise_error_msg(SQLCODE, sqlerrm, 'output_DF', 'get matching VO of B, collect attributes and insert');
360 END;
361 END LOOP;
362
363 -- children A cursor loop
364 END IF;
365
366 -- children A count > 0
367
368 SELECT id bulk collect
369 INTO v_children_b_id_list
373 AND parent_id = p_data_pid_b
370 FROM az_reporter_data
371 WHERE request_id = p_request_id
372 AND source = p_source
374 AND is_primary = 'N'
375 AND deleted_flag = 'N';
376
377 IF v_children_b_id_list.COUNT <> 0 THEN
378 FOR i IN 1 .. v_children_b_id_list.COUNT
379 LOOP
380 output_b_only(p_request_id, p_source, p_depth, p_results_id, p_results_pid, v_children_b_id_list(i)
381
382 /*p_data_id_B*/);
383
384 END LOOP;
385 END IF;
386
387 ----------------------------------------------
388
389 EXCEPTION
390 WHEN application_exception THEN
391 RAISE;
392 WHEN others THEN
393 raise_error_msg(SQLCODE, sqlerrm, 'output_DF', 'procedure end');
394 END;
395
396 -- output p_data_id and its children
397
398 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
399
400 v_name_a VARCHAR2(4000);
401 v_disp_name_a VARCHAR2(4000);
402 v_id_a NUMBER;
403 v_hc_a NUMBER;
404 v_hcd_a VARCHAR2(4000);
405 v_results_pid NUMBER;
406
407 v_children_a_id_list typ_nest_tab_number;
408 v_temp_xmltype xmltype;
409 v_amount INTEGER;
410 v_type NUMBER;
411
412 v_exclude_details VARCHAR2(1);
413 v_exclude_details_temp VARCHAR2(1);
414 l_api_name constant VARCHAR2(30) := 'output_A_only : ';
415 BEGIN
416 -- output this
417
418 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
419 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
420 || to_char(systimestamp), 'Called output_A_only with ' || ' p_request_id: '
421 || p_request_id || ' p_source: ' || p_source || ' p_depth: ' || p_depth
422 || ' p_data_id: ' || p_data_id);
423 END IF;
424
425 SELECT name,
426 display_name,
427 id,
428 hashcode,
429 hashcode_details
430 INTO v_name_a,
431 v_disp_name_a,
432 v_id_a,
433 v_hc_a,
434 v_hcd_a
435 FROM az_reporter_data
436 WHERE request_id = p_request_id
437 AND source = p_source
438 AND id = p_data_id
439 AND is_primary = 'Y';
440
441 SELECT d.attributes.transform(v_a_xmltype)
442 INTO v_temp_xmltype
443 FROM az_reporter_data d
444 WHERE request_id = p_request_id
445 AND source = p_source
446 AND id = p_data_id
447 AND is_primary = 'Y';
448
449 SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
450 INTO v_exclude_details_temp
451 FROM dual;
452
453 IF(v_exclude_details_temp = '0' OR exclude_details = 'Y') THEN
454 v_exclude_details := 'Y';
455 ELSE
456 v_exclude_details := 'N';
457 END IF;
458
459 IF p_results_pid = 1 THEN
460 v_type := 0;
461 ELSE
462 v_type := 2;
463 END IF;
464
465 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
466 -- IF(LENGTH(v_temp_xmltype.getClobVal()) > 0 ) THEN
467 -- fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
468 -- || to_char(systimestamp), ' v_temp_xmltype: ' || v_temp_xmltype.getclobval());
469 -- END IF;
470 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
471 || to_char(systimestamp), 'v_exclude_details: ' || v_exclude_details || ' p_results_pid: ' || p_results_pid);
472 END IF;
473
474 INSERT
475 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)
476 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
477 'A', -- isdifferent
478 'N', -- istransformed
479 'Y', -- show_diff_only
480 v_exclude_details, xmltype(v_temp_xmltype.getclobval(), diff_schema_url, 1, 1));
481
482 v_dml_count := v_dml_count + 1;
483 commit_if_required;
484
485 v_results_pid := p_results_id;
486 p_results_id := p_results_id + 2;
487
488 -- output children
489 SELECT id bulk collect
490 INTO v_children_a_id_list
491 FROM az_reporter_data
492 WHERE request_id = p_request_id
493 AND source = p_source
494 AND parent_id = p_data_id
495 AND is_primary = 'Y';
496
497 IF v_children_a_id_list.COUNT <> 0 THEN
498 FOR i IN 1 .. v_children_a_id_list.COUNT
499 LOOP
500 output_a_only(p_request_id, p_source, p_depth + 1, p_results_id, v_results_pid, v_children_a_id_list(i));
501 END LOOP;
502
503 -- children A cursor
504 END IF;
505
506 EXCEPTION
507 WHEN application_exception THEN
508 RAISE;
509 WHEN others THEN
510 raise_error_msg(SQLCODE, sqlerrm, 'output_A_only', 'procedure end');
511 END;
512
516
513 -- output p_data_id and its children
514
515 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
517 v_name_b VARCHAR2(4000);
518 v_disp_name_b VARCHAR2(4000);
519 v_id_b NUMBER;
520 v_hc_b NUMBER;
521 v_hcd_b VARCHAR2(4000);
522 v_results_pid NUMBER;
523
524 v_children_b_id_list typ_nest_tab_number;
525
526 v_attr_str VARCHAR2(32767);
527 v_amount INTEGER;
528 v_type NUMBER;
529 v_temp_xmltype xmltype;
530 v_exclude_details VARCHAR2(1);
531 v_exclude_details_temp VARCHAR2(1);
532 l_api_name constant VARCHAR2(30) := 'output_B_only : ';
533 BEGIN
534 -- output this
535
536 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
537 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
538 || to_char(systimestamp), 'Called output_B_only with ' || ' p_request_id: ' || p_request_id
539 || ' p_source: ' || p_source || ' p_depth: ' || p_depth || ' p_results_pid: ' || p_results_pid
540 || ' p_data_id: ' || p_data_id);
541 END IF;
542
543 SELECT name,
544 display_name,
545 id,
546 hashcode,
547 hashcode_details
548 INTO v_name_b,
549 v_disp_name_b,
550 v_id_b,
551 v_hc_b,
552 v_hcd_b
553 FROM az_reporter_data
554 WHERE request_id = p_request_id
555 AND source = p_source
556 AND id = p_data_id
557 AND is_primary = 'N';
558
559 SELECT d.attributes.transform(v_b_xmltype)
560 INTO v_temp_xmltype
561 FROM az_reporter_data d
562 WHERE request_id = p_request_id
563 AND source = p_source
564 AND id = p_data_id
565 AND is_primary = 'N';
566
567 SELECT existsnode(v_temp_xmltype, '/H/V[not(@U="Y")]')
568 INTO v_exclude_details_temp
569 FROM dual;
570
571 IF(v_exclude_details_temp = '0' OR exclude_details = 'Y') THEN
572 v_exclude_details := 'Y';
573 ELSE
574 v_exclude_details := 'N';
575 END IF;
576
577 IF p_results_pid = 1 THEN
578 v_type := 0;
579 ELSE
580 v_type := 2;
581 END IF;
582
583 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
584 IF(LENGTH(v_temp_xmltype.getClobVal()) > 0 ) THEN
585 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
586 || to_char(systimestamp), ' v_temp_xmltype: ' || v_temp_xmltype.getclobval());
587 END IF;
588 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
589 || to_char(systimestamp), 'v_exclude_details: ' || v_exclude_details || ' p_results_pid: '
590 || p_results_pid);
591 END IF;
592
593 INSERT
594 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)
595 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
596 'B', -- isdifferent
597 'N', -- istransformed
598 'Y', -- show_diff_only
599 v_exclude_details, xmltype(v_temp_xmltype.getclobval(), diff_schema_url, 1, 1));
600
601 v_dml_count := v_dml_count + 1;
602 commit_if_required;
603
604 UPDATE az_reporter_data
605 SET deleted_flag = 'Y'
606 WHERE request_id = p_request_id
607 AND source = p_source
608 AND id = v_id_b
609 AND is_primary = 'N';
610
611 v_dml_count := v_dml_count + 1;
612 commit_if_required;
613
614 v_results_pid := p_results_id;
615 p_results_id := p_results_id + 2;
616
617 -- output children
618 SELECT id bulk collect
619 INTO v_children_b_id_list
620 FROM az_reporter_data
621 WHERE request_id = p_request_id
622 AND source = p_source
623 AND parent_id = p_data_id
624 AND is_primary = 'N'
625 AND deleted_flag = 'N';
626
627 IF v_children_b_id_list.COUNT <> 0 THEN
628 FOR i IN 1 .. v_children_b_id_list.COUNT
629 LOOP
630 output_b_only(p_request_id, p_source, p_depth + 1, p_results_id, v_results_pid, v_children_b_id_list(i));
631 END LOOP;
632
633 -- children A cursor
634 END IF;
635
636 EXCEPTION
637 WHEN application_exception THEN
638 RAISE;
639 WHEN others THEN
640 raise_error_msg(SQLCODE, sqlerrm, 'output_B_only', 'procedure end');
641 END;
642
643 PROCEDURE update_for_show_only_diff(p_request_id IN NUMBER, p_source IN VARCHAR2) IS
644
645 v_different_id_list typ_nest_tab_number;
646 v_different_pid_list typ_nest_tab_number;
647 v_show_only_diff VARCHAR2(1);
648 v_parent_id NUMBER;
649 v_id NUMBER;
650 v_is_different VARCHAR2(1);
654 parent_id bulk collect
651 l_api_name constant VARCHAR2(30) := 'update_for_show_only_diff : ';
652 BEGIN
653 SELECT id,
655 INTO v_different_id_list,
656 v_different_pid_list
657 FROM az_diff_results
658 WHERE request_id = p_request_id
659 AND source = p_source
660 AND is_different <> 'N' -- gets you A, B or C
661 ORDER BY depth;
662
663 IF v_different_id_list.COUNT <> 0 THEN
664 FOR i IN 1 .. v_different_id_list.COUNT
665 LOOP
666 v_show_only_diff := 'N';
667 v_parent_id := v_different_pid_list(i);
668 LOOP
669 EXIT
670 WHEN v_show_only_diff = 'Y';
671 BEGIN
672 v_id := v_parent_id;
673 -- avoiding recursion
674 SELECT parent_id,
675 is_different,
676 show_only_diff
677 INTO v_parent_id,
678 v_is_different,
679 v_show_only_diff
680 FROM az_diff_results
681 WHERE request_id = p_request_id
682 AND source = p_source
683 AND id = v_id;
684
685 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level)
686 THEN
687 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
688 || to_char(systimestamp), 'v_different_ID_List_I: ' || v_different_id_list(i)
689 || ' v_different_PID_List: ' || v_different_pid_list(i) || ' v_parent_id: ' || v_parent_id ||
690 ' v_is_different: ' || v_is_different || ' v_show_only_diff: ' || v_show_only_diff);
691 END IF;
692
693 -- if is_different is A, B or C let it remain as it is
694 -- valid (is_different, show_only_diff) combinations till now - (A,Y), (B,Y), (C,Y), (N,N). introduce (D,Y) now.
695
696 IF v_is_different = 'N'
697 AND v_show_only_diff = 'N' THEN
698
699 UPDATE az_diff_results
700 SET show_only_diff = 'Y',
701 is_different = 'D'
702 WHERE request_id = p_request_id
703 AND source = p_source
704 AND id = v_id;
705
706 v_dml_count := v_dml_count + 1;
707 commit_if_required;
708
709 END IF;
710
711 EXCEPTION
712 WHEN no_data_found THEN
713 v_show_only_diff := 'Y';
714 -- for AM
715 WHEN others THEN
716 raise_error_msg(SQLCODE, sqlerrm, 'update_for_show_only_diff', 'select show_only_diff column of parents');
717 END;
718 END LOOP;
719 END LOOP;
720
721 -- for loop closes
722 END IF;
723
724 -- if count <>0
725
726 EXCEPTION
727 WHEN application_exception THEN
728 RAISE;
729 WHEN others THEN
730 raise_error_msg(SQLCODE, sqlerrm, 'update_for_show_only_diff', 'procedure end');
731
732 END update_for_show_only_diff;
733
734 PROCEDURE raise_error_msg (ErrCode IN NUMBER,
735 ErrMsg IN VARCHAR2,
736 ProcedureName IN VARCHAR2,
737 Statement IN VARCHAR2) IS
738
739 v_message VARCHAR2(2048);
740
741 BEGIN
742
743 if( FND_LOG.LEVEL_UNEXPECTED >=
744 FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
745 FND_MESSAGE.SET_NAME('AZ', 'AZ_R12_PLSQL_EXCEPTION'); -- Seeded Message
746 -- Runtime Information
747 FND_MESSAGE.SET_TOKEN('ERROR_CODE', ErrCode);
748 FND_MESSAGE.SET_TOKEN('ERROR_MESG', ErrMsg);
749 FND_MESSAGE.SET_TOKEN('ERROR_PROC', 'az_comp_reporter.' || ProcedureName);
750 IF (Statement IS NOT NULL) THEN
751 FND_MESSAGE.SET_TOKEN('ERROR_STMT', Statement);
752 ELSE
753 FND_MESSAGE.SET_TOKEN('ERROR_STMT', 'none');
754 END IF;
755 raise_application_error(-20001, FND_MESSAGE.GET);
756 end if;
757 END raise_error_msg;
758
759
760 PROCEDURE commit_if_required IS
761 BEGIN
762
763 IF MOD(v_dml_count, commit_batch_size) = 0 THEN
764 COMMIT;
765
766 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
767 fnd_log.string(fnd_log.level_statement, c_log_head || 'commit_if_required : ' || to_char(systimestamp), 'Committed transaction');
768 END IF;
769
770 END IF;
771
772 END commit_if_required;
773 --LMATHUR added to update the record count in selection set XML for the given source, based on the type of differences
774 PROCEDURE update_diff_type_counts(p_request_id IN NUMBER, p_source IN VARCHAR2) IS
775
776 v_diff_type_list typ_nest_tab_varchar;
777 v_diff_count_list typ_nest_tab_varchar;
778 v_transform_xml VARCHAR2(32767) := '';
779 l_api_name constant VARCHAR2(40) := 'update_diff_type_counts : ';
780 BEGIN
781
782 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
783 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);
784 END IF;
785
789 INTO v_diff_count_list,
786 SELECT nvl(COUNT, 0),
787 e.column_value.getrootelement() AS
788 col_name bulk collect
790 v_diff_type_list
791 FROM
792 (SELECT decode(is_different, 'A', 'P6', 'B', 'P7', 'C', 'P8', 'D', 'P8', 'N', 'P9') name,
793 COUNT(is_different) COUNT
794 FROM az_diff_results d
795 WHERE request_id = p_request_id
796 AND source = p_source
797 AND parent_id = 1
798 GROUP BY is_different)
799 k,
800 TABLE(xmlsequence(EXTRACT(xmltype('<Root><P6/><P7/><P8/><P9/></Root>'), '/Root/node()'))) e
801 WHERE e.column_value.getrootelement() = name(+);
802
803 FOR i IN 1 .. v_diff_type_list.COUNT
804 LOOP
805
806 v_transform_xml := v_transform_xml || '<xsl:when test="name(.)=''''' || v_diff_type_list(i) || '''''">
807 <xsl:copy>
808 <xsl:choose>
809 <xsl:when test="./../V[@N=''''EntityOccuranceCode'''' and .=''''' || p_source || ''''']">' || v_diff_count_list(i) || '</xsl:when>
810 <xsl:otherwise><xsl:value-of select="."/></xsl:otherwise>
811 </xsl:choose>
812 </xsl:copy>
813
814 </xsl:when>';
815
816 END LOOP;
817
818 IF LENGTH(v_transform_xml) > 0 THEN
819 v_transform_xml := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
820 <xsl:template match="EXT">
821 <xsl:copy>
822 <xsl:copy-of select="@*"/>
823 <xsl:apply-templates/>
824 </xsl:copy>
825 </xsl:template>
826 <xsl:template match="H">
827 <xsl:copy>
828 <xsl:copy-of select="@*"/>
829 <xsl:for-each select="*">
830 <xsl:choose>' || v_transform_xml || '<xsl:otherwise>
831 <xsl:copy-of select="."/>
832 </xsl:otherwise>
833 </xsl:choose>
834 </xsl:for-each>
835 </xsl:copy>
836 </xsl:template>
837 </xsl:stylesheet>';
838
839 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
840 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name ||
841 to_char(systimestamp), 'Query to update selection set with counts : update az_requests d set d.selection_set = d.selection_set.transform(xmltype(''' ||
842 v_transform_xml || ''')).createSchemabasedxml(d.selection_set.getSchemaURL()) WHERE request_id=' || p_request_id || ' and request_type=''C''');
843 END IF;
844
845 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''';
846 END IF;
847
848 EXCEPTION
849 WHEN no_data_found THEN
850
851 NULL;
852 WHEN application_exception THEN
853 RAISE;
854 WHEN others THEN
855 raise_error_msg(SQLCODE, sqlerrm, 'update_diff_type_counts', 'Error while updating the count based on type of differences');
856
857 END update_diff_type_counts;
858
859 END az_comp_reporter;