1 PACKAGE BODY pos_revision_differences AS
2 /* $Header: POSPORVB.pls 120.2 2011/12/19 10:38:51 ramkandu ship $ */
3
4 /*********************************************************************
5 * NAME
6 * compare_po_to_all
7 *
8 * PURPOSE
9 * Call the procedure COMPARE_PO with ALL as comparison flag.
10 *
11 * ARGUMENTS
12 * p_header_id Unique identified for the current PO in
13 * PO_HEADERS_ARCHIVE_ALL table
14 * p_release_id Unique identifier for the PO release in
15 * PO_RELEASES_ARCHIVE_ALL table
16 * p_revision_num Current PO revision number
17 * p_dummy_in4 Not required
18 * p_dummy_in5 Not required
19 * p_dummy_in6 Not required
20 * p_dummy_in7 Not required
21 * p_dummy_in8 Not required
22 * p_dummy_in9 Not required
23 * p_dummy_in10 Not required
24 * p_sequence_num Sequence number to identify the comparison
25 * results for a specific record in
26 * ICX_PO_REVISIONS_TEMP table.
27 * p_dummy_out2 Not required
28 * p_dummy_out3 Not required
29 * p_dummy_out4 Not required
30 * p_dummy_out5 Not required
31 * p_dummy_out6 Not required
32 * p_dummy_out7 Not required
33 * p_dummy_out8 Not required
34 * p_dummy_out9 Not required
35 * p_dummy_out10 Not required
36 *
37 * NOTES
38 * This procedures serves the purpose of a link from WAD to call the
39 * procedure COMPARE_PO. There is no way to store the comparison flag
40 * in the link. Therefore, the link calls this procedure, which calls
41 * COMPARE_PO with the appropriate flag.
42 *
43 * Normally, you do not want to call this procedure directly. You can
44 * simply call COMPARE_PO with the appropriate parameters.
45 *
46 * HISTORY
47 * 20-NOV-1997 Rami Haddad Created
48 ********************************************************************/
49 PROCEDURE compare_po_to_all(
50 p_header_id IN NUMBER,
51 p_release_id IN NUMBER,
52 p_revision_num IN NUMBER,
53 p_dummy_in4 IN NUMBER DEFAULT NULL,
54 p_dummy_in5 IN NUMBER DEFAULT NULL,
55 p_dummy_in6 IN NUMBER DEFAULT NULL,
56 p_dummy_in7 IN NUMBER DEFAULT NULL,
57 p_dummy_in8 IN NUMBER DEFAULT NULL,
58 p_dummy_in9 IN NUMBER DEFAULT NULL,
59 p_dummy_in10 IN NUMBER DEFAULT NULL,
60 p_sequence_num OUT NOCOPY NUMBER,
61 p_dummy_out2 OUT NOCOPY NUMBER,
62 p_dummy_out3 OUT NOCOPY NUMBER,
63 p_dummy_out4 OUT NOCOPY NUMBER,
64 p_dummy_out5 OUT NOCOPY NUMBER,
65 p_dummy_out6 OUT NOCOPY NUMBER,
66 p_dummy_out7 OUT NOCOPY NUMBER,
67 p_dummy_out8 OUT NOCOPY NUMBER,
68 p_dummy_out9 OUT NOCOPY NUMBER,
69 p_dummy_out10 OUT NOCOPY NUMBER
70 ) AS
71
72 v_sequence_num NUMBER := NULL;
73 v_progress VARCHAR2(3);
74
75 BEGIN
76
77 v_progress := '010';
78 compare_po(
79 p_header_id,
80 p_release_id,
81 p_revision_num,
82 'ALL',
83 v_sequence_num );
84
85 p_sequence_num := v_sequence_num;
86
87 EXCEPTION
88 WHEN others THEN
89 PO_MESSAGE_S.SQL_ERROR(
90 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_ALL',
91 v_progress,
92 sqlcode );
93 RAISE;
94
95 END compare_po_to_all;
96
97
98
99 /*********************************************************************
100 * NAME
101 * compare_po_to_original
102 *
103 * PURPOSE
104 * Call the procedure COMPARE_PO with the ORIGINAL as comparison flag.
105 *
106 * ARGUMENTS
107 * p_header_id Unique identified for the current PO in
108 * PO_HEADERS_ARCHIVE_ALL table
109 * p_release_id Unique identifier for the PO release in
110 * PO_RELEASES_ARCHIVE_ALL table
111 * p_revision_num Current PO revision number
112 * p_dummy_in4 Not required
113 * p_dummy_in5 Not required
114 * p_dummy_in6 Not required
115 * p_dummy_in7 Not required
116 * p_dummy_in8 Not required
117 * p_dummy_in9 Not required
118 * p_dummy_in10 Not required
119 * p_sequence_num Sequence number to identify the comparison
120 * results for a specific record in
121 * ICX_PO_REVISIONS_TEMP table.
122 * p_dummy_out2 Not required
123 * p_dummy_out3 Not required
124 * p_dummy_out4 Not required
125 * p_dummy_out5 Not required
126 * p_dummy_out6 Not required
127 * p_dummy_out7 Not required
128 * p_dummy_out8 Not required
129 * p_dummy_out9 Not required
130 * p_dummy_out10 Not required
131 *
132 * NOTES
133 * This procedures serves the purpose of a link from WAD to call the
134 * procedure COMPARE_PO. There is no way to store the comparison flag
135 * in the link. Therefore, the link calls this procedure, which calls
136 * COMPARE_PO with the appropriate flag.
137 *
138 * Normally, you do not want to call this procedure directly. You can
139 * simply call COMPARE_PO with the appropriate parameters.
140 *
141 * HISTORY
142 * 01-SEP-1997 Rami Haddad Created
143 * 05-SEP-1997 Matt Denton Debugged
144 ********************************************************************/
145 PROCEDURE compare_po_to_original(
146 p_header_id IN NUMBER,
147 p_release_id IN NUMBER,
148 p_revision_num IN NUMBER,
149 p_dummy_in4 IN NUMBER DEFAULT NULL,
150 p_dummy_in5 IN NUMBER DEFAULT NULL,
151 p_dummy_in6 IN NUMBER DEFAULT NULL,
152 p_dummy_in7 IN NUMBER DEFAULT NULL,
153 p_dummy_in8 IN NUMBER DEFAULT NULL,
154 p_dummy_in9 IN NUMBER DEFAULT NULL,
155 p_dummy_in10 IN NUMBER DEFAULT NULL,
156 p_sequence_num OUT NOCOPY NUMBER,
157 p_dummy_out2 OUT NOCOPY NUMBER,
158 p_dummy_out3 OUT NOCOPY NUMBER,
159 p_dummy_out4 OUT NOCOPY NUMBER,
160 p_dummy_out5 OUT NOCOPY NUMBER,
161 p_dummy_out6 OUT NOCOPY NUMBER,
162 p_dummy_out7 OUT NOCOPY NUMBER,
163 p_dummy_out8 OUT NOCOPY NUMBER,
164 p_dummy_out9 OUT NOCOPY NUMBER,
165 p_dummy_out10 OUT NOCOPY NUMBER
166 ) AS
167
168 v_sequence_num NUMBER := NULL;
169 v_progress VARCHAR2(3);
170
171 BEGIN
172
173 v_progress := '020';
174
175 compare_po(
176 p_header_id,
177 p_release_id,
178 p_revision_num,
179 'ORIGINAL',
180 v_sequence_num );
181
182 p_sequence_num := v_sequence_num;
183
184 EXCEPTION
185 WHEN others THEN
186 PO_MESSAGE_S.SQL_ERROR(
187 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_ORIGINAL',
188 v_progress,
189 sqlcode );
190 RAISE;
191
192 END compare_po_to_original;
193
194
195
196 /*********************************************************************
197 * NAME
198 * compare_po_to_previous
199 *
200 * PURPOSE
201 * Call the procedure COMPARE_PO with the PREVIOUS as comparison flag.
202 *
203 * ARGUMENTS
204 * p_header_id Unique identified for the current PO in
205 * PO_HEADERS_ARCHIVE_ALL table
206 * p_release_id Unique identifier for the PO release in
207 * PO_RELEASES_ARCHIVE_ALL table
208 * p_revision_num Current PO revision number
209 * p_dummy_in4 Not required
210 * p_dummy_in5 Not required
211 * p_dummy_in6 Not required
212 * p_dummy_in7 Not required
213 * p_dummy_in8 Not required
214 * p_dummy_in9 Not required
215 * p_dummy_in10 Not required
216 * p_sequence_num Sequence number to identify the comparison
217 * results for a specific record in
218 * ICX_PO_REVISIONS_TEMP table.
219 * p_dummy_out2 Not required
220 * p_dummy_out3 Not required
221 * p_dummy_out4 Not required
222 * p_dummy_out5 Not required
223 * p_dummy_out6 Not required
224 * p_dummy_out7 Not required
225 * p_dummy_out8 Not required
226 * p_dummy_out9 Not required
227 * p_dummy_out10 Not required
228 *
229 * NOTES
230 * This procedures serves the purpose of a link from WAD to call the
231 * procedure COMPARE_PO. There is no way to store the comparison flag
232 * in the link. Therefore, the link calls this procedure, which calls
233 * COMPARE_PO with the appropriate flag.
234 *
235 * Normally, you do not want to call this procedure directly. You can
236 * simply call COMPARE_PO with the appropriate parameters.
237 *
238 * HISTORY
239 * 01-SEP-1997 Rami Haddad Created
240 * 05-SEP-1997 Matt Denton Debugged
241 ********************************************************************/
242 PROCEDURE compare_po_to_previous(
243 p_header_id IN NUMBER,
244 p_release_id IN NUMBER,
245 p_revision_num IN NUMBER,
246 p_dummy_in4 IN NUMBER DEFAULT NULL,
247 p_dummy_in5 IN NUMBER DEFAULT NULL,
248 p_dummy_in6 IN NUMBER DEFAULT NULL,
249 p_dummy_in7 IN NUMBER DEFAULT NULL,
250 p_dummy_in8 IN NUMBER DEFAULT NULL,
251 p_dummy_in9 IN NUMBER DEFAULT NULL,
252 p_dummy_in10 IN NUMBER DEFAULT NULL,
253 p_sequence_num OUT NOCOPY NUMBER,
254 p_dummy_out2 OUT NOCOPY NUMBER,
255 p_dummy_out3 OUT NOCOPY NUMBER,
256 p_dummy_out4 OUT NOCOPY NUMBER,
257 p_dummy_out5 OUT NOCOPY NUMBER,
258 p_dummy_out6 OUT NOCOPY NUMBER,
259 p_dummy_out7 OUT NOCOPY NUMBER,
260 p_dummy_out8 OUT NOCOPY NUMBER,
261 p_dummy_out9 OUT NOCOPY NUMBER,
262 p_dummy_out10 OUT NOCOPY NUMBER
263 ) AS
264
265 v_sequence_num NUMBER := NULL;
266 v_progress VARCHAR2(3);
267
268 BEGIN
269
270 v_progress := '030';
271
272 compare_po(
273 p_header_id,
274 p_release_id,
275 p_revision_num,
276 'PREVIOUS',
277 v_sequence_num );
278
279 p_sequence_num := v_sequence_num;
280
281 EXCEPTION
282 WHEN others THEN
283 PO_MESSAGE_S.SQL_ERROR(
284 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_PREVIOUS',
285 v_progress,
286 sqlcode );
287 RAISE;
288
289 END compare_po_to_previous;
290
291
292 /*********************************************************************
293 * NAME
294 * compare_po_to_lastsign
295 *
296 * PURPOSE
297 * Call the procedure COMPARE_PO with the LASTSIGN as comparison flag.
298 *
299 * ARGUMENTS
300 * p_header_id Unique identified for the current PO in
301 * PO_HEADERS_ARCHIVE_ALL table
302 * p_release_id Unique identifier for the PO release in
303 * PO_RELEASES_ARCHIVE_ALL table
304 * p_revision_num Current PO revision number
305 * p_dummy_in4 Not required
306 * p_dummy_in5 Not required
307 * p_dummy_in6 Not required
308 * p_dummy_in7 Not required
309 * p_dummy_in8 Not required
310 * p_dummy_in9 Not required
311 * p_dummy_in10 Not required
312 * p_sequence_num Sequence number to identify the comparison
313 * results for a specific record in
314 * ICX_PO_REVISIONS_TEMP table.
315 * p_dummy_out2 Not required
316 * p_dummy_out3 Not required
317 * p_dummy_out4 Not required
318 * p_dummy_out5 Not required
319 * p_dummy_out6 Not required
320 * p_dummy_out7 Not required
321 * p_dummy_out8 Not required
322 * p_dummy_out9 Not required
323 * p_dummy_out10 Not required
324 *
325 * NOTES
326 * This procedures serves the purpose of a link from WAD to call the
327 * procedure COMPARE_PO. There is no way to store the comparison flag
328 * in the link. Therefore, the link calls this procedure, which calls
329 * COMPARE_PO with the appropriate flag.
330 *
331 * Normally, you do not want to call this procedure directly. You can
332 * simply call COMPARE_PO with the appropriate parameters.
333 *
334 * HISTORY
335 * 12-SEP-2003 ammitra created
336 ********************************************************************/
337 PROCEDURE compare_po_to_lastsign(
338 p_header_id IN NUMBER,
339 p_release_id IN NUMBER,
340 p_revision_num IN NUMBER,
341 p_dummy_in4 IN NUMBER DEFAULT NULL,
342 p_dummy_in5 IN NUMBER DEFAULT NULL,
343 p_dummy_in6 IN NUMBER DEFAULT NULL,
344 p_dummy_in7 IN NUMBER DEFAULT NULL,
345 p_dummy_in8 IN NUMBER DEFAULT NULL,
346 p_dummy_in9 IN NUMBER DEFAULT NULL,
347 p_dummy_in10 IN NUMBER DEFAULT NULL,
348 p_sequence_num OUT NOCOPY NUMBER,
349 p_dummy_out2 OUT NOCOPY NUMBER,
350 p_dummy_out3 OUT NOCOPY NUMBER,
351 p_dummy_out4 OUT NOCOPY NUMBER,
352 p_dummy_out5 OUT NOCOPY NUMBER,
353 p_dummy_out6 OUT NOCOPY NUMBER,
354 p_dummy_out7 OUT NOCOPY NUMBER,
355 p_dummy_out8 OUT NOCOPY NUMBER,
356 p_dummy_out9 OUT NOCOPY NUMBER,
357 p_dummy_out10 OUT NOCOPY NUMBER
358 ) AS
359
360 v_sequence_num NUMBER := NULL;
361 v_progress VARCHAR2(3);
362
363 BEGIN
364
365 v_progress := '030';
366
367 compare_po(
368 p_header_id,
369 p_release_id,
370 p_revision_num,
371 'LASTSIGN',
372 v_sequence_num );
373
374 p_sequence_num := v_sequence_num;
375
376 EXCEPTION
377 WHEN others THEN
378 PO_MESSAGE_S.SQL_ERROR(
379 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_PREVIOUS',
380 v_progress,
381 sqlcode );
382 RAISE;
383
384 END compare_po_to_lastsign;
385
386
387 /*********************************************************************
388 * NAME
389 * compare_po
390 *
391 * PURPOSE
392 * Compare the a PO with the previous or the original
393 * revision---including all lines, shipments, and distributions.
394 *
395 * ARGUMENTS
396 * p_header_id Unique identified for the current PO in
397 * PO_HEADERS_ARCHIVE_ALL table
398 * p_release_id Unique identifier for the PO release in
399 * PO_RELEASES_ARCHIVE_ALL table
400 * p_revision_num Current PO revision number
401 * p_comparison_flag Indicator to compare the PO with the previous
402 * or original revision
403 * p_sequence_num Sequence number to identify the comparison
404 * results for a specific record in
405 * ICX_PO_REVISIONS_TEMP table.
406 *
407 * NOTES
408 *
409 * HISTORY
410 * 01-SEP-1997 Rami Haddad Created
411 * 05-SEP-1997 Matt Denton Debugged
412 * 25-NOV-1997 Rami Haddad Handle comparison flag 'ALL,' which
413 * loops through and compare all
414 * revisions.
415 ********************************************************************/
416 PROCEDURE compare_po(
417 p_header_id IN NUMBER,
418 p_release_id IN NUMBER,
419 p_revision_num IN NUMBER,
420 p_comparison_flag IN VARCHAR2,
421 p_sequence_num OUT NOCOPY NUMBER
422 ) AS
423
424 v_sequence_num NUMBER;
425 v_revision_counter NUMBER := p_revision_num;
426 v_comparison_flag VARCHAR2( 80 ) := p_comparison_flag;
427 v_progress VARCHAR2(3);
428 l_last_sign_revision NUMBER;
429 x_base_revision NUMBER;
430 x_global_agree_flag varchar2(1);
431
432 BEGIN
433
434 v_progress := '040';
435
436 SELECT icx_po_history_details_s.nextval
437 INTO v_sequence_num
438 FROM DUAL;
439 p_sequence_num := v_sequence_num;
440
441
442 select nvl(global_agreement_flag,'N')
443 into x_global_agree_flag
444 from po_headers_all
445 where po_header_id = p_header_id;
446
447 IF v_comparison_flag = 'ALL' then
448 v_comparison_flag := 'PREVIOUS';
449 x_base_revision := 1;
450 END IF;
451
452 LOOP
453 v_progress := '050';
454 compare_headers(
455 p_header_id,
456 p_release_id,
457 v_revision_counter,
458 v_comparison_flag,
459 v_sequence_num ,
460 p_comparison_flag);
461
462 if (x_global_agree_flag = 'Y') then
463 compare_ga_assignments(
464 p_header_id,
465 v_revision_counter,
466 v_comparison_flag,
467 v_sequence_num );
468 end if;
469
470 v_progress := '060';
471
472 -- Bug# 1761302. Added the IF THEN condition as there is
473 -- no change in lines for revisions against Releases.
474
475 IF (p_release_id is NULL) THEN
476 compare_lines(
477 p_header_id,
478 v_revision_counter,
479 v_comparison_flag,
480 v_sequence_num);
481 END IF;
482
483
484 compare_pdiffs_line(
485 p_header_id,
486 v_revision_counter,
487 v_comparison_flag,
488 v_sequence_num);
489
490
491 compare_pdiffs_ship(
492 p_header_id,
493 v_revision_counter,
494 v_comparison_flag,
495 v_sequence_num);
496
497
498
499 v_progress := '070';
500 compare_locations(
501 p_header_id,
502 p_release_id,
503 v_revision_counter,
504 v_comparison_flag,
505 v_sequence_num);
506
507 v_progress := '080';
508
509 -- forward port 5392285 - comparing distributions
510 --bug # 366756 not comparing distribututions
511
512 compare_distributions(
513 p_header_id,
514 p_release_id,
515 v_revision_counter,
516 v_comparison_flag,
517 v_sequence_num);
518
519
520
521 v_revision_counter := v_revision_counter - 1;
522
523 IF (v_revision_counter < x_base_revision) OR
524 (p_comparison_flag <> 'ALL')
525
526 THEN
527 EXIT;
528 END IF;
529
530 END LOOP;
531
532 v_progress := '090';
533 pos_compare_revisions.verify_no_differences( v_sequence_num );
534
535 EXCEPTION
536 WHEN others THEN
537 PO_MESSAGE_S.SQL_ERROR(
538 'PO_REVISION_DIFFERENCES.COMPARE_PO',
539 v_progress,
540 sqlcode );
541 RAISE;
542
543 END compare_po;
544
545
546
547 /*********************************************************************
548 * NAME
549 * compare_headers
550 *
551 * PURPOSE
552 * Compare two headers, the first is according to the passed in
553 * parameters, P_HEADER_ID and P_REVISION_NUM. The second depends on
554 * the comparison flag, either the original PO (revision 0), or the
555 * previous revision (P_REVISION_NUM - 1 ).
556 *
557 * ARGUMENTS
558 * p_header_id Unique identified for the PO in
559 * PO_HEADERS_ARCHIVE_ALL table
560 * p_release_id Unique identifier for the PO release in
561 * PO_RELEASES_ARCHIVE_ALL table
562 * p_revision_num Current PO revision number
563 * p_comparison_flag Indicator to compare the PO with the previous
564 * or original revision
565 * p_sequence_num Sequence number to identify the comparison
566 * results for a specific record in
567 * ICX_PO_REVISIONS_TEMP table.
568 *
569 * NOTES
570 *
571 * HISTORY
572 * 01-SEP-1997 Rami Haddad Created
573 * 05-SEP-1997 Matt Denton Debugged
574 ********************************************************************/
575 PROCEDURE compare_headers(
576 p_header_id IN NUMBER,
577 p_release_id IN NUMBER,
578 p_revision_num IN NUMBER,
579 p_comparison_flag IN VARCHAR2,
580 p_sequence_num IN NUMBER,
581 p_comparison_flag_org IN VARCHAR2
582 ) AS
583
584 v_header_from po_headers_archive_all%ROWTYPE;
585 v_header_to po_headers_archive_all%ROWTYPE;
586
587 v_release_from po_releases_archive_all%ROWTYPE;
588 v_release_to po_releases_archive_all%ROWTYPE;
589
590 v_previous_revision_num NUMBER;
591 v_progress VARCHAR2(3);
592 l_msg_data varchar2(2000);
593 l_msg_count number;
594 l_revision_num number;
595 l_signed_records varchar2(20);
596 l_return_status varchar2(20);
597 get_lastsign_rev_failed exception;
598
599 BEGIN
600
601 v_progress := '100';
602
603 IF p_revision_num <= 0
604 THEN
605 RETURN;
606 END IF;
607
608 /* What is the revision number we will compare against? */
609 IF p_comparison_flag = 'ORIGINAL'
610 THEN
611 v_previous_revision_num := 0;
612 ELSIF p_comparison_flag = 'LASTSIGN' then
613
614 PO_CONTERMS_UTL_GRP.get_last_signed_revision(
615 p_api_version => 1.0,
616 p_init_msg_list => FND_API.G_FALSE,
617 p_header_id => p_header_id,
618 p_revision_num => p_revision_num,
619 x_signed_revision_num => l_revision_num,
620 x_signed_records => l_signed_records,
621 x_return_status => l_return_status,
622 x_msg_data => l_msg_data,
623 x_msg_count => l_msg_count);
624
625 if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
626 v_previous_revision_num := l_revision_num;
627 else
628 raise get_lastsign_rev_failed;
629 end if;
630
631 ELSE
632 v_previous_revision_num := p_revision_num - 1;
633 END IF;
634
635 IF p_release_id IS NULL THEN
636
637 BEGIN
638
639 v_progress := '110';
640 SELECT *
641 INTO v_header_to
642 FROM po_headers_archive_all
643 WHERE
644 po_header_id = p_header_id
645 AND revision_num = p_revision_num;
646
647 EXCEPTION
648 WHEN no_data_found THEN
649 v_header_to := NULL;
650
651 END;
652
653 BEGIN
654
655 v_progress := '120';
656 SELECT *
657 INTO v_header_from
658 FROM po_headers_archive_all
659 WHERE
660 po_header_id = p_header_id
661 AND revision_num = v_previous_revision_num;
662
663 EXCEPTION
664 WHEN no_data_found THEN
665 v_header_from := NULL;
666
667 END;
668
669 v_progress := '130';
670 pos_compare_revisions.compare_headers(
671 v_header_from,
672 v_header_to,
673 p_sequence_num,
674 p_comparison_flag_org );
675
676 ELSE
677
678 BEGIN
679
680 v_progress := '140';
681 SELECT *
682 INTO v_release_to
683 FROM po_releases_archive_all
684 WHERE
685 po_release_id = p_release_id
686 AND revision_num = p_revision_num;
687
688 EXCEPTION
689 WHEN no_data_found THEN
690 v_release_to := NULL;
691
692 END;
693
694 BEGIN
695
696 v_progress := '150';
697 SELECT *
698 INTO v_release_from
699 FROM po_releases_archive_all
700 WHERE
701 po_release_id = p_release_id
702 AND revision_num = v_previous_revision_num;
703
704 EXCEPTION
705 WHEN no_data_found THEN
706 v_release_from := NULL;
707
708 END;
709
710 v_progress := '160';
711 pos_compare_revisions.compare_releases(
712 v_release_from,
713 v_release_to,
714 p_sequence_num );
715
716 END IF;
717
718 EXCEPTION
719 WHEN get_lastsign_rev_failed THEN
720 PO_MESSAGE_S.SQL_ERROR(
721 'PO_REVISION_DIFFERENCES.COMPARE_HEADERS',
722 l_msg_data,
723 sqlcode );
724 WHEN others THEN
725 PO_MESSAGE_S.SQL_ERROR(
726 'PO_REVISION_DIFFERENCES.COMPARE_HEADERS',
727 v_progress,
728 sqlcode );
729 RAISE;
730
731 END compare_headers;
732
733
734
735 /********************************************************************
736 * NAME
737 * compare_ga_assignments
738 * PURPOSE
739 * Compare Global Agreement Org Assignments for a Global Agreement
740 * ARGUMENTS
741 * p_header_id Unique identified for the PO in
742 * PO_HEADERS_ARCHIVE_ALL table
743 * p_release_id Unique identifier for the PO release in
744 * PO_RELEASES_ARCHIVE_ALL table
745 * p_revision_num Current PO revision number
746 * p_comparison_flag Indicator to compare the PO with the previous
747 * or original revision
748 * p_sequence_num Sequence number to identify the comparison
749 * results for a specific record in
750 * ICX_PO_REVISIONS_TEMP table.
751 *
752 * NOTES
753 *
754 * HISTORY
755 * 19-SEP-2003 Amitabh Mitra Added
756 ********************************************************************/
757
758 PROCEDURE compare_ga_assignments(
759 p_header_id IN NUMBER,
760 p_revision_num IN NUMBER,
761 p_comparison_flag IN VARCHAR2,
762 p_sequence_num IN NUMBER
763 ) AS
764
765 CURSOR current_ga_assign_cursor(
766 current_header_id NUMBER,
767 current_revision_num NUMBER ) IS
768 SELECT *
769 FROM po_ga_org_assignments_archive pga1
770 WHERE
771 po_header_id = current_header_id AND
772 revision_num = (SELECT MAX( revision_num )
773 FROM po_ga_org_assignments_archive pga2
774 WHERE
775 revision_num <= current_revision_num
776 AND pga2.organization_id = pga1.organization_id
777 AND pga2.po_header_id = pga1.po_header_id
778 );
779
780
781 v_ga_ass_to po_ga_org_assignments_archive%ROWTYPE;
782 v_ga_ass_from po_ga_org_assignments_archive%ROWTYPE;
783
784 v_previous_revision_num NUMBER;
785 v_progress VARCHAR2(3);
786
787 BEGIN
788
789 v_progress := '270';
790
791 IF p_revision_num <= 0 THEN
792 RETURN;
793 END IF;
794
795 IF p_comparison_flag = 'ORIGINAL'
796 THEN
797 v_previous_revision_num := 0;
798 ELSE
799 v_previous_revision_num := p_revision_num - 1;
800 END IF;
801
802 FOR v_ga_ass_to IN current_ga_assign_cursor(
803 p_header_id,
804 p_revision_num
805 ) LOOP
806 IF v_ga_ass_to.revision_num > v_previous_revision_num
807 THEN
808
809 BEGIN
810 v_progress := '180';
811
812
813 SELECT *
814 INTO v_ga_ass_from
815 FROM po_ga_org_assignments_archive
816 WHERE
817 revision_num = (
818 SELECT MAX( revision_num )
819 FROM po_ga_org_assignments_archive
820 WHERE
821 revision_num <= v_previous_revision_num
822 AND organization_id = v_ga_ass_to.organization_id
823 AND po_header_id = v_ga_ass_to.po_header_id
824 )
825 AND organization_id = v_ga_ass_to.organization_id
826 AND po_header_id = v_ga_ass_to.po_header_id;
827
828 EXCEPTION
829 WHEN no_data_found THEN
830 v_ga_ass_from := NULL;
831 END;
832
833 v_progress := '290';
834 pos_compare_revisions.compare_ga_assignments(
835 v_ga_ass_from,
836 v_ga_ass_to,
837 p_sequence_num);
838 END IF;
839 END LOOP;
840 EXCEPTION
841 WHEN others THEN
842 PO_MESSAGE_S.SQL_ERROR(
843 'PO_REVISION_DIFFERENCES.COMPARE_GA_ASSIGNMENTS',
844 v_progress,
845 sqlcode );
846 RAISE;
847
848 END compare_ga_assignments;
849
850
851
852 /*********************************************************************
853 * NAME
854 * compare_lines
855 *
856 * PURPOSE
857 * Compare lines for two POs, the first is according to the passed in
858 * parameters, P_HEADER_ID and P_REVISION_NUM. The second depends on
859 * the comparison flag, either the original PO (revision 0), or the
860 * previous revision (P_REVISION_NUM - 1 ).
861 *
862 * ARGUMENTS
863 * p_header_id Unique identified for the PO in
864 * PO_HEADERS_ARCHIVE_ALL table
865 * p_release_id Unique identifier for the PO release in
866 * PO_RELEASES_ARCHIVE_ALL table
867 * p_revision_num Current PO revision number
868 * p_comparison_flag Indicator to compare the PO with the previous
869 * or original revision
870 * p_sequence_num Sequence number to identify the comparison
871 * results for a specific record in
872 * ICX_PO_REVISIONS_TEMP table.
873 *
874 * NOTES
875 *
876 * HISTORY
877 * 01-SEP-1997 Rami Haddad Created
878 * 05-SEP-1997 Matt Denton Debugged
879 * 20-NOV-1997 Rami Haddad Changed cursor to select latest
880 * revision of lines for the PO, given
881 * its revision number, p_revision_num.
882 ********************************************************************/
883 PROCEDURE compare_lines(
884 p_header_id IN NUMBER,
885 p_revision_num IN NUMBER,
886 p_comparison_flag IN VARCHAR2,
887 p_sequence_num IN NUMBER
888 ) AS
889
890 CURSOR current_lines_cursor(
891 current_header_id NUMBER,
892 current_revision_num NUMBER ) IS
893 SELECT *
894 FROM po_lines_archive_all pla1
895 WHERE
896 po_header_id = current_header_id
897 AND revision_num = (
898 SELECT MAX( revision_num )
899 FROM po_lines_archive_all pla2
900 WHERE
901 revision_num <= current_revision_num
902 AND pla2.po_line_id = pla1.po_line_id
903 );
904
905 v_line_to po_lines_archive_all%ROWTYPE;
906 v_line_from po_lines_archive_all%ROWTYPE;
907
908 v_previous_revision_num NUMBER;
909 v_progress VARCHAR2(3);
910
911 BEGIN
912
913 v_progress := '170';
914
915 IF p_revision_num <= 0 THEN
916 RETURN;
917 END IF;
918
919 IF p_comparison_flag = 'ORIGINAL'
920 THEN
921 v_previous_revision_num := 0;
922 ELSE
923 v_previous_revision_num := p_revision_num - 1;
924 END IF;
925
926 FOR v_line_to IN current_lines_cursor(
927 p_header_id,
928 p_revision_num
929 ) LOOP
930
931 /* Compare the lines only if the current line has a revision
932 * greater than the calculated revision number of previous
933 * line.
934 */
935 IF v_line_to.revision_num > v_previous_revision_num
936 THEN
937
938 BEGIN
939
940 v_progress := '180';
941 SELECT *
942 INTO v_line_from
943 FROM po_lines_archive_all
944 WHERE
945 revision_num = (
946 SELECT MAX( revision_num )
947 FROM po_lines_archive_all
948 WHERE
949 revision_num <=
950 v_previous_revision_num
951 AND po_line_id = v_line_to.po_line_id
952 )
953 AND po_line_id = v_line_to.po_line_id;
954
955 EXCEPTION
956 WHEN no_data_found THEN
957 v_line_from := NULL;
958
959 END;
960
961 v_progress := '190';
962 pos_compare_revisions.compare_lines(
963 v_line_from,
964 v_line_to,
965 p_sequence_num);
966 END IF;
967
968 END LOOP;
969
970 EXCEPTION
971 WHEN others THEN
972 PO_MESSAGE_S.SQL_ERROR(
973 'PO_REVISION_DIFFERENCES.COMPARE_LINES',
974 v_progress,
975 sqlcode );
976 RAISE;
977
978 END compare_lines;
979
980
981
982 PROCEDURE compare_pdiffs_line(
983 p_header_id IN NUMBER,
984 p_revision_num IN NUMBER,
985 p_comparison_flag IN VARCHAR2,
986 p_sequence_num IN NUMBER
987 ) AS
988
989 CURSOR current_pdiffs_cursor(
990 current_header_id NUMBER,
991 current_revision_num NUMBER ) IS
992
993 SELECT PDIFF1.PRICE_DIFFERENTIAL_ID,
994 PDIFF1.PRICE_DIFFERENTIAL_NUM,
995 PDIFF1.ENTITY_ID ,
996 PDIFF1.ENTITY_TYPE ,
997 PDIFF1.PRICE_TYPE ,
998 PDIFF1.REVISION_NUM ,
999 PDIFF1.ENABLED_FLAG ,
1000 PDIFF1.MIN_MULTIPLIER ,
1001 PDIFF1.LATEST_EXTERNAL_FLAG,
1002 PDIFF1.MAX_MULTIPLIER ,
1003 PDIFF1.MULTIPLIER ,
1004 PDIFF1.CREATED_BY ,
1005 PDIFF1.CREATION_DATE ,
1006 PDIFF1.LAST_UPDATED_BY ,
1007 PDIFF1.LAST_UPDATE_DATE ,
1008 PDIFF1.LAST_UPDATE_LOGIN
1009 FROM po_price_differentials_archive pdiff1,po_lines_all pol
1010 WHERE
1011 pol.po_header_id = current_header_id and
1012 pol.po_line_id = pdiff1.entity_id and
1013 pol.revision_num = (
1014 SELECT MAX( pdiff2.revision_num )
1015 FROM po_price_differentials_archive pdiff2,
1016 po_lines_all pol2
1017 WHERE
1018 pol2.po_header_id = current_header_id and
1019 pol2.po_line_id = pdiff2.entity_id and
1020 pdiff2.entity_type in ('PO LINE','BLANKET LINE') and
1021 pdiff2.revision_num <= current_revision_num
1022 AND pdiff1.price_differential_id = pdiff2.price_differential_id
1023 );
1024
1025
1026 v_pdiff_from po_price_differentials_archive%ROWTYPE;
1027 v_pdiff_to po_price_differentials_archive%ROWTYPE;
1028
1029
1030 v_previous_revision_num NUMBER;
1031 v_progress VARCHAR2(3);
1032
1033 BEGIN
1034
1035 v_progress := '170';
1036
1037 IF p_revision_num <= 0 THEN
1038 RETURN;
1039 END IF;
1040
1041 IF p_comparison_flag = 'ORIGINAL'
1042 THEN
1043 v_previous_revision_num := 0;
1044 ELSE
1045 v_previous_revision_num := p_revision_num - 1;
1046 END IF;
1047
1048
1049 FOR v_pdiff_to IN current_pdiffs_cursor(
1050 p_header_id,
1051 p_revision_num
1052 ) LOOP
1053
1054 IF v_pdiff_to.revision_num > v_previous_revision_num
1055 THEN
1056 BEGIN
1057
1058 v_progress := '180';
1059
1060 SELECT *
1061 INTO v_pdiff_from
1062 FROM po_price_differentials_archive
1063 WHERE
1064 revision_num = (
1065 SELECT MAX( revision_num )
1066 FROM po_price_differentials_archive
1067 WHERE
1068 revision_num <= v_previous_revision_num
1069 AND price_differential_id = v_pdiff_to.price_differential_id
1070 )
1071 AND price_differential_id = v_pdiff_to.price_differential_id;
1072
1073 EXCEPTION
1074 WHEN no_data_found THEN
1075 v_pdiff_from := NULL;
1076
1077 END;
1078
1079 v_progress := '190';
1080 pos_compare_revisions.compare_price_diffs(
1081 v_pdiff_from,
1082 v_pdiff_to,
1083 p_sequence_num);
1084 END IF;
1085
1086
1087 END LOOP;
1088 EXCEPTION
1089 WHEN others THEN
1090 PO_MESSAGE_S.SQL_ERROR(
1091 'PO_REVISION_DIFFERENCES.COMPARE_LINES',
1092 v_progress,
1093 sqlcode );
1094 RAISE;
1095
1096 END compare_pdiffs_line;
1097
1098
1099
1100 PROCEDURE compare_pdiffs_ship(
1101 p_header_id IN NUMBER,
1102 p_revision_num IN NUMBER,
1103 p_comparison_flag IN VARCHAR2,
1104 p_sequence_num IN NUMBER
1105 ) AS
1106
1107 CURSOR current_pdiffs_cursor(
1108 current_header_id NUMBER,
1109 current_revision_num NUMBER ) IS
1110
1111 SELECT PDIFF1.PRICE_DIFFERENTIAL_ID,
1112 PDIFF1.PRICE_DIFFERENTIAL_NUM,
1113 PDIFF1.ENTITY_ID ,
1114 PDIFF1.ENTITY_TYPE ,
1115 PDIFF1.PRICE_TYPE ,
1116 PDIFF1.REVISION_NUM ,
1117 PDIFF1.ENABLED_FLAG ,
1118 PDIFF1.MIN_MULTIPLIER ,
1119 PDIFF1.LATEST_EXTERNAL_FLAG,
1120 PDIFF1.MAX_MULTIPLIER ,
1121 PDIFF1.MULTIPLIER ,
1122 PDIFF1.CREATED_BY ,
1123 PDIFF1.CREATION_DATE ,
1124 PDIFF1.LAST_UPDATED_BY ,
1125 PDIFF1.LAST_UPDATE_DATE ,
1126 PDIFF1.LAST_UPDATE_LOGIN
1127 FROM po_price_differentials_archive pdiff1,po_line_locations_all pll
1128 WHERE
1129 pll.po_header_id = current_header_id and
1130 pll.line_location_id = pdiff1.entity_id and
1131 pll.revision_num = (
1132 SELECT MAX( pdiff2.revision_num )
1133 FROM po_price_differentials_archive pdiff2,
1134 po_line_locations_all pll2
1135 WHERE
1136 pll2.po_header_id = current_header_id and
1137 pll2.line_location_id = pdiff2.entity_id and
1138 pdiff2.entity_type in ('PRICE BREAK') and
1139 pdiff2.revision_num <= current_revision_num and
1140 pdiff1.price_differential_id = pdiff2.price_differential_id
1141 );
1142
1143
1144 v_pdiff_from po_price_differentials_archive%ROWTYPE;
1145 v_pdiff_to po_price_differentials_archive%ROWTYPE;
1146
1147
1148 v_previous_revision_num NUMBER;
1149 v_progress VARCHAR2(3);
1150
1151 BEGIN
1152
1153 v_progress := '170';
1154
1155 IF p_revision_num <= 0 THEN
1156 RETURN;
1157 END IF;
1158
1159 IF p_comparison_flag = 'ORIGINAL'
1160 THEN
1161 v_previous_revision_num := 0;
1162 ELSE
1163 v_previous_revision_num := p_revision_num - 1;
1164 END IF;
1165
1166 FOR v_pdiff_to IN current_pdiffs_cursor(
1167 p_header_id,
1168 p_revision_num
1169 ) LOOP
1170
1171 IF v_pdiff_to.revision_num > v_previous_revision_num
1172 THEN
1173 BEGIN
1174
1175 v_progress := '180';
1176
1177 SELECT *
1178 INTO v_pdiff_from
1179 FROM po_price_differentials_archive
1180 WHERE
1181 revision_num = (
1182 SELECT MAX( revision_num )
1183 FROM po_price_differentials_archive
1184 WHERE
1185 revision_num <= v_previous_revision_num
1186 AND price_differential_id = v_pdiff_to.price_differential_id
1187 )
1188 AND price_differential_id = v_pdiff_to.price_differential_id;
1189
1190 EXCEPTION
1191 WHEN no_data_found THEN
1192 v_pdiff_from := NULL;
1193
1194 END;
1195
1196 pos_compare_revisions.compare_price_diffs(
1197 v_pdiff_from,
1198 v_pdiff_to,
1199 p_sequence_num);
1200 END IF;
1201
1202
1203
1204 END LOOP;
1205
1206 EXCEPTION
1207 WHEN others THEN
1208 PO_MESSAGE_S.SQL_ERROR(
1209 'PO_REVISION_DIFFERENCES.COMPARE_LINES',
1210 v_progress,
1211 sqlcode );
1212 RAISE;
1213
1214 END compare_pdiffs_ship;
1215
1216
1217
1218
1219 /*********************************************************************
1220 * NAME
1221 * compare_locations
1222 *
1223 * PURPOSE
1224 * Compare shipments for two POs, the first is according to the passed
1225 * in parameters, P_HEADER_ID and P_REVISION_NUM. The second depends
1226 * on the comparison flag, either the original PO (revision 0), or the
1227 * previous revision (P_REVISION_NUM - 1 ).
1228 *
1229 * ARGUMENTS
1230 * p_header_id Unique identified for the PO in
1231 * PO_HEADERS_ARCHIVE_ALL table
1232 * p_release_id Unique identifier for the PO release in
1233 * PO_RELEASES_ARCHIVE_ALL table
1234 * p_revision_num Current PO revision number
1235 * p_comparison_flag Indicator to compare the PO with the previous
1236 * or original revision
1237 * p_sequence_num Sequence number to identify the comparison
1238 * results for a specific record in
1239 * ICX_PO_REVISIONS_TEMP table.
1240 *
1241 * NOTES
1242 *
1243 * HISTORY
1244 * 01-SEP-1997 Rami Haddad Created
1245 * 05-SEP-1997 Matt Denton Debugged
1246 * 20-NOV-1997 Rami Haddad Changed cursor to select latest
1247 * revision of shipments for the PO,
1248 * given its revision number,
1249 * p_revision_num.
1250 ********************************************************************/
1251 PROCEDURE compare_locations(
1252 p_header_id IN NUMBER,
1253 p_release_id IN NUMBER,
1254 p_revision_num IN NUMBER,
1255 p_comparison_flag IN VARCHAR2,
1256 p_sequence_num IN NUMBER
1257 ) AS
1258
1259 CURSOR current_locations_cursor(
1260 current_header_id NUMBER,
1261 current_release_id NUMBER,
1262 current_revision_num NUMBER ) IS
1263 SELECT *
1264 FROM po_line_locations_archive_all plla1
1265 WHERE po_header_id = current_header_id
1266 AND NVL( po_release_id, -99 ) = current_release_id
1267 AND revision_num = (
1268 SELECT MAX( revision_num )
1269 FROM po_line_locations_archive_all plla2
1270 WHERE
1271 revision_num <= current_revision_num
1272 AND plla2.line_location_id = plla1.line_location_id
1273 );
1274
1275 v_loc_from po_line_locations_archive_all%ROWTYPE;
1276 v_loc_to po_line_locations_archive_all%ROWTYPE;
1277
1278 v_previous_revision_num NUMBER;
1279 v_progress VARCHAR2(3);
1280
1281 BEGIN
1282
1283 v_progress := '200';
1284
1285 IF p_revision_num <= 0 THEN
1286 RETURN;
1287 END IF;
1288
1289 IF p_comparison_flag = 'ORIGINAL'
1290 THEN
1291 v_previous_revision_num := 0;
1292 ELSE
1293 v_previous_revision_num := p_revision_num - 1;
1294 END IF;
1295
1296 FOR v_loc_to in current_locations_cursor(
1297 p_header_id,
1298 NVL( p_release_id, -99 ),
1299 p_revision_num
1300 ) LOOP
1301
1302 /* Compare the lines only if the current line has a revision
1303 * greater than the calculated revision number of previous
1304 * line.
1305 */
1306 IF v_loc_to.revision_num > v_previous_revision_num THEN
1307
1308 BEGIN
1309
1310 v_progress := '210';
1311 SELECT *
1312 INTO v_loc_from
1313 FROM po_line_locations_archive_all
1314 WHERE
1315 revision_num = (
1316 SELECT MAX( revision_num )
1317 FROM po_line_locations_archive_all
1318 WHERE
1319 revision_num <=
1320 v_previous_revision_num
1321 AND line_location_id =
1322 v_loc_to.line_location_id
1323 )
1324 AND line_location_id = v_loc_to.line_location_id;
1325
1326 EXCEPTION
1327 WHEN no_data_found THEN
1328 v_loc_from := NULL;
1329
1330 END;
1331
1332 v_progress := '220';
1333 pos_compare_revisions.compare_locations(
1334 v_loc_from,
1335 v_loc_to,
1336 p_sequence_num );
1337 END IF;
1338
1339 END LOOP;
1340
1341 EXCEPTION
1342 WHEN others THEN
1343 PO_MESSAGE_S.SQL_ERROR(
1344 'PO_REVISION_DIFFERENCES.COMPARE_LINES',
1345 v_progress,
1346 sqlcode );
1347 RAISE;
1348
1349 END compare_locations;
1350
1351
1352
1353 /*********************************************************************
1354 * NAME
1355 * compare_distributions
1356 *
1357 * PURPOSE
1358 * Compare distributions for two POs, the first is according to the
1359 * passed in parameters, P_HEADER_ID and P_REVISION_NUM. The second
1360 * depends on the comparison flag, either the original PO (revision
1361 * 0), or the previous revision (P_REVISION_NUM - 1 ).
1362 *
1363 * ARGUMENTS
1364 * p_header_id Unique identified for the PO in
1365 * PO_HEADERS_ARCHIVE_ALL table
1366 * p_release_id Unique identifier for the PO release in
1367 * PO_RELEASES_ARCHIVE_ALL table
1368 * p_revision_num Current PO revision number
1369 * p_comparison_flag Indicator to compare the PO with the previous
1370 * or original revision
1371 * p_sequence_num Sequence number to identify the comparison
1372 * results for a specific record in
1373 * ICX_PO_REVISIONS_TEMP table.
1374 *
1375 * NOTES
1376 *
1377 * HISTORY
1378 * 01-SEP-1997 Rami Haddad Created
1379 * 05-SEP-1997 Matt Denton Debugged
1380 * 20-NOV-1997 Rami Haddad Changed cursor to select latest
1381 * revision of distributions for the PO,
1382 * given its revision number,
1383 * p_revision_num.
1384 ********************************************************************/
1385 PROCEDURE compare_distributions(
1386 p_header_id IN NUMBER,
1387 p_release_id IN NUMBER,
1388 p_revision_num IN NUMBER,
1389 p_comparison_flag IN VARCHAR2,
1390 p_sequence_num IN NUMBER
1391 ) AS
1392
1393 CURSOR current_distributions_cursor(
1394 current_header_id NUMBER,
1395 current_release_id NUMBER,
1396 current_revision_num NUMBER
1397 ) IS
1398 SELECT *
1399 FROM po_distributions_archive_all pda1
1400 WHERE
1401 po_header_id = current_header_id
1402 AND NVL( po_release_id, -99 ) = current_release_id
1403 AND revision_num = (
1404 SELECT MAX( revision_num )
1405 FROM po_distributions_archive_all pda2
1406 WHERE
1407 revision_num <= current_revision_num
1408 AND pda2.po_distribution_id = pda1.po_distribution_id
1409 );
1410
1411 v_dist_from po_distributions_archive_all%ROWTYPE;
1412 v_dist_to po_distributions_archive_all%ROWTYPE;
1413
1414 v_previous_revision_num NUMBER;
1415 v_progress VARCHAR2(3);
1416
1417 BEGIN
1418
1419 v_progress := '230';
1420
1421 IF p_revision_num <= 0 THEN
1422 RETURN;
1423 END IF;
1424
1425 IF p_comparison_flag = 'ORIGINAL' THEN
1426 v_previous_revision_num := 0;
1427 ELSE
1428 v_previous_revision_num := p_revision_num - 1;
1429 END IF;
1430
1431 FOR v_dist_to in current_distributions_cursor(
1432 p_header_id,
1433 NVL( p_release_id, -99 ),
1434 p_revision_num
1435 ) LOOP
1436
1437 /* Compare the lines only if the current line has a revision
1438 * greater than the calculated revision number of previous
1439 * line.
1440 */
1441 IF v_dist_to.revision_num > v_previous_revision_num
1442 THEN
1443
1444 BEGIN
1445
1446 v_progress := '240';
1447 SELECT *
1448 INTO v_dist_from
1449 FROM po_distributions_archive_all
1450 WHERE
1451 revision_num = (
1452 SELECT MAX( revision_num )
1453 FROM po_distributions_archive_all
1454 WHERE revision_num <=
1455 v_previous_revision_num
1456 AND po_distribution_id =
1457 v_dist_to.po_distribution_id
1458 )
1459 AND po_distribution_id =
1460 v_dist_to.po_distribution_id;
1461
1462 EXCEPTION
1463 WHEN no_data_found THEN
1464 v_dist_from := NULL;
1465
1466 END;
1467
1468 v_progress := '250';
1469 pos_compare_revisions.compare_distributions(
1470 v_dist_from,
1471 v_dist_to,
1472 p_sequence_num );
1473 END IF;
1474
1475 END LOOP;
1476
1477 EXCEPTION
1478 WHEN others THEN
1479 PO_MESSAGE_S.SQL_ERROR(
1480 'PO_REVISION_DIFFERENCES.COMPARE_DISTRIBUTIONS',
1481 v_progress,
1482 sqlcode );
1483 RAISE;
1484
1485 END compare_distributions;
1486
1487
1488
1489 /*********************************************************************
1490 * NAME
1491 * compare_line_to_all
1492 *
1493 * PURPOSE
1494 * Call the procedure COMPARE_PO_LINE with ALL as comparison flag.
1495 *
1496 * ARGUMENTS
1497 * p_line_id Unique identified for the current PO line in
1498 * PO_LINES_ARCHIVE_ALL table
1499 * p_release_id Unique identifier for the PO release in
1500 * PO_RELEASES_ARCHIVE_ALL table
1501 * p_revision_num Current PO line revision number
1502 * p_dummy_in4 Not required
1503 * p_dummy_in5 Not required
1504 * p_dummy_in6 Not required
1505 * p_dummy_in7 Not required
1506 * p_dummy_in8 Not required
1507 * p_dummy_in9 Not required
1508 * p_dummy_in10 Not required
1509 * p_sequence_num Sequence number to identify the comparison
1510 * results for a specific record in
1511 * ICX_PO_REVISIONS_TEMP table.
1512 * p_dummy_out2 Not required
1513 * p_dummy_out3 Not required
1514 * p_dummy_out4 Not required
1515 * p_dummy_out5 Not required
1516 * p_dummy_out6 Not required
1517 * p_dummy_out7 Not required
1518 * p_dummy_out8 Not required
1519 * p_dummy_out9 Not required
1520 * p_dummy_out10 Not required
1521 *
1522 * NOTES
1523 * This procedures serves the purpose of a link from WAD to call the
1524 * procedure COMPARE_PO_LINE. There is no way to store the comparison
1525 * flag in the link. Therefore, the link calls this procedure, which
1526 * calls COMPARE_PO_LINE with the appropriate flag.
1527 *
1528 * Normally, you do not want to call this procedure directly. You can
1529 * simply call COMPARE_PO_LINE with the appropriate parameters.
1530 *
1531 * HISTORY
1532 * 01-SEP-1997 Rami Haddad Created
1533 * 05-SEP-1997 Matt Denton Debugged
1534 ********************************************************************/
1535 PROCEDURE compare_line_to_all(
1536 p_line_id IN NUMBER,
1537 p_release_id IN NUMBER,
1538 p_revision_num IN NUMBER,
1539 p_dummy_in4 IN NUMBER DEFAULT NULL,
1540 p_dummy_in5 IN NUMBER DEFAULT NULL,
1541 p_dummy_in6 IN NUMBER DEFAULT NULL,
1542 p_dummy_in7 IN NUMBER DEFAULT NULL,
1543 p_dummy_in8 IN NUMBER DEFAULT NULL,
1544 p_dummy_in9 IN NUMBER DEFAULT NULL,
1545 p_dummy_in10 IN NUMBER DEFAULT NULL,
1546 p_sequence_num OUT NOCOPY NUMBER,
1547 p_dummy_out2 OUT NOCOPY NUMBER,
1548 p_dummy_out3 OUT NOCOPY NUMBER,
1549 p_dummy_out4 OUT NOCOPY NUMBER,
1550 p_dummy_out5 OUT NOCOPY NUMBER,
1551 p_dummy_out6 OUT NOCOPY NUMBER,
1552 p_dummy_out7 OUT NOCOPY NUMBER,
1553 p_dummy_out8 OUT NOCOPY NUMBER,
1554 p_dummy_out9 OUT NOCOPY NUMBER,
1555 p_dummy_out10 OUT NOCOPY NUMBER
1556 ) AS
1557
1558 v_sequence_num NUMBER := NULL;
1559 v_progress VARCHAR2(3);
1560
1561 BEGIN
1562
1563 v_progress := '260';
1564
1565 compare_po_line(
1566 p_line_id,
1567 p_release_id,
1568 p_revision_num,
1569 'ALL',
1570 v_sequence_num );
1571
1572 p_sequence_num := v_sequence_num;
1573
1574 EXCEPTION
1575 WHEN others THEN
1576 PO_MESSAGE_S.SQL_ERROR(
1577 'PO_REVISION_DIFFERENCES.COMPARE_LINE_TO_ALL',
1578 v_progress,
1579 sqlcode );
1580 RAISE;
1581
1582 END compare_line_to_all;
1583
1584
1585
1586 /*********************************************************************
1587 * NAME
1588 * compare_line_to_original
1589 *
1590 * PURPOSE
1591 * Call the procedure COMPARE_PO_LINE with the ORIGINAL as comparison
1592 * flag.
1593 *
1594 * ARGUMENTS
1595 * p_line_id Unique identified for the current PO line in
1596 * PO_LINES_ARCHIVE_ALL table
1597 * p_release_id Unique identifier for the PO release in
1598 * PO_RELEASES_ARCHIVE_ALL table
1599 * p_revision_num Current PO line revision number
1600 * p_dummy_in4 Not required
1601 * p_dummy_in5 Not required
1602 * p_dummy_in6 Not required
1603 * p_dummy_in7 Not required
1604 * p_dummy_in8 Not required
1605 * p_dummy_in9 Not required
1606 * p_dummy_in10 Not required
1607 * p_sequence_num Sequence number to identify the comparison
1608 * results for a specific record in
1609 * ICX_PO_REVISIONS_TEMP table.
1610 * p_dummy_out2 Not required
1611 * p_dummy_out3 Not required
1612 * p_dummy_out4 Not required
1613 * p_dummy_out5 Not required
1614 * p_dummy_out6 Not required
1615 * p_dummy_out7 Not required
1616 * p_dummy_out8 Not required
1617 * p_dummy_out9 Not required
1618 * p_dummy_out10 Not required
1619 *
1620 * NOTES
1621 * This procedures serves the purpose of a link from WAD to call the
1622 * procedure COMPARE_PO_LINE. There is no way to store the comparison
1623 * flag in the link. Therefore, the link calls this procedure, which
1624 * calls COMPARE_PO_LINE with the appropriate flag.
1625 *
1626 * Normally, you do not want to call this procedure directly. You can
1627 * simply call COMPARE_PO_LINE with the appropriate parameters.
1628 *
1629 * HISTORY
1630 * 01-SEP-1997 Rami Haddad Created
1631 * 05-SEP-1997 Matt Denton Debugged
1632 ********************************************************************/
1633 PROCEDURE compare_line_to_original(
1634 p_line_id IN NUMBER,
1635 p_release_id IN NUMBER,
1636 p_revision_num IN NUMBER,
1637 p_dummy_in4 IN NUMBER DEFAULT NULL,
1638 p_dummy_in5 IN NUMBER DEFAULT NULL,
1639 p_dummy_in6 IN NUMBER DEFAULT NULL,
1640 p_dummy_in7 IN NUMBER DEFAULT NULL,
1641 p_dummy_in8 IN NUMBER DEFAULT NULL,
1642 p_dummy_in9 IN NUMBER DEFAULT NULL,
1643 p_dummy_in10 IN NUMBER DEFAULT NULL,
1644 p_sequence_num OUT NOCOPY NUMBER,
1645 p_dummy_out2 OUT NOCOPY NUMBER,
1646 p_dummy_out3 OUT NOCOPY NUMBER,
1647 p_dummy_out4 OUT NOCOPY NUMBER,
1648 p_dummy_out5 OUT NOCOPY NUMBER,
1649 p_dummy_out6 OUT NOCOPY NUMBER,
1650 p_dummy_out7 OUT NOCOPY NUMBER,
1651 p_dummy_out8 OUT NOCOPY NUMBER,
1652 p_dummy_out9 OUT NOCOPY NUMBER,
1653 p_dummy_out10 OUT NOCOPY NUMBER
1654 ) AS
1655
1656 v_sequence_num NUMBER := NULL;
1657 v_progress VARCHAR2(3);
1658
1659 BEGIN
1660
1661 v_progress := '270';
1662
1663 compare_po_line(
1664 p_line_id,
1665 p_release_id,
1666 p_revision_num,
1667 'ORIGINAL',
1668 v_sequence_num );
1669
1670 p_sequence_num := v_sequence_num;
1671
1672 EXCEPTION
1673 WHEN others THEN
1674 PO_MESSAGE_S.SQL_ERROR(
1675 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_ORIGINAL',
1676 v_progress,
1677 sqlcode );
1678 RAISE;
1679
1680 END compare_line_to_original;
1681
1682
1683
1684 /*********************************************************************
1685 * NAME
1686 * compare_line_to_previous
1687 *
1688 * PURPOSE
1689 * Call the procedure COMPARE_PO_LINE with the PREVIOUS as comparison
1690 * flag.
1691 *
1692 * ARGUMENTS
1693 * p_line_id Unique identified for the current PO line in
1694 * PO_LINES_ARCHIVE_ALL table
1695 * p_release_id Unique identifier for the PO release in
1696 * PO_RELEASES_ARCHIVE_ALL table
1697 * p_revision_num Current PO line revision number
1698 * p_dummy_in4 Not required
1699 * p_dummy_in5 Not required
1700 * p_dummy_in6 Not required
1701 * p_dummy_in7 Not required
1702 * p_dummy_in8 Not required
1703 * p_dummy_in9 Not required
1704 * p_dummy_in10 Not required
1705 * p_sequence_num Sequence number to identify the comparison
1706 * results for a specific record in
1707 * ICX_PO_REVISIONS_TEMP table.
1708 * p_dummy_out2 Not required
1709 * p_dummy_out3 Not required
1710 * p_dummy_out4 Not required
1711 * p_dummy_out5 Not required
1712 * p_dummy_out6 Not required
1713 * p_dummy_out7 Not required
1714 * p_dummy_out8 Not required
1715 * p_dummy_out9 Not required
1716 * p_dummy_out10 Not required
1717 *
1718 * NOTES
1719 * This procedures serves the purpose of a link from WAD to call the
1720 * procedure COMPARE_PO_LINE. We need to have two links in the flow
1721 * to compare the PO line with the original or with the previous.
1722 * There is no way to store the comparison flag in the link.
1723 * Therefore, the link calls this procedure, which calls
1724 * COMPARE_PO_LINE with ORIGINAL flag.
1725 *
1726 * Normally, you do not want to call this procedure directly. You can
1727 * simply call COMPARE_PO_LINE with the appropriate parameters.
1728 *
1729 * HISTORY
1730 * 01-SEP-1997 Rami Haddad Created
1731 * 05-SEP-1997 Matt Denton Debugged
1732 ********************************************************************/
1733 PROCEDURE compare_line_to_previous(
1734 p_line_id IN NUMBER,
1735 p_release_id IN NUMBER,
1736 p_revision_num IN NUMBER,
1737 p_dummy_in4 IN NUMBER DEFAULT NULL,
1738 p_dummy_in5 IN NUMBER DEFAULT NULL,
1739 p_dummy_in6 IN NUMBER DEFAULT NULL,
1740 p_dummy_in7 IN NUMBER DEFAULT NULL,
1741 p_dummy_in8 IN NUMBER DEFAULT NULL,
1742 p_dummy_in9 IN NUMBER DEFAULT NULL,
1743 p_dummy_in10 IN NUMBER DEFAULT NULL,
1744 p_sequence_num OUT NOCOPY NUMBER,
1745 p_dummy_out2 OUT NOCOPY NUMBER,
1746 p_dummy_out3 OUT NOCOPY NUMBER,
1747 p_dummy_out4 OUT NOCOPY NUMBER,
1748 p_dummy_out5 OUT NOCOPY NUMBER,
1749 p_dummy_out6 OUT NOCOPY NUMBER,
1750 p_dummy_out7 OUT NOCOPY NUMBER,
1751 p_dummy_out8 OUT NOCOPY NUMBER,
1752 p_dummy_out9 OUT NOCOPY NUMBER,
1753 p_dummy_out10 OUT NOCOPY NUMBER
1754 ) AS
1755
1756 v_sequence_num NUMBER := NULL;
1757 v_progress VARCHAR2(3);
1758
1759 BEGIN
1760
1761 v_progress := '280';
1762
1763 compare_po_line(
1764 p_line_id,
1765 p_release_id,
1766 p_revision_num,
1767 'PREVIOUS',
1768 v_sequence_num);
1769
1770 p_sequence_num := v_sequence_num;
1771
1772 EXCEPTION
1773 WHEN others THEN
1774 PO_MESSAGE_S.SQL_ERROR(
1775 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_PREVIOUS',
1776 v_progress,
1777 sqlcode );
1778 RAISE;
1779
1780 END compare_line_to_previous;
1781
1782
1783
1784 /*********************************************************************
1785 * NAME
1786 * compare_po_line
1787 *
1788 * PURPOSE
1789 * Compare a PO line with the previous or the original
1790 * revision---including all shipments, and distributions.
1791 *
1792 * ARGUMENTS
1793 * p_line_id Unique identified for the current PO line in
1794 * PO_LINES_ARCHIVE_ALL table
1795 * p_release_id Unique identifier for the PO release in
1796 * PO_RELEASES_ARCHIVE_ALL table
1797 * p_revision_num Current PO line revision number
1798 * p_comparison_flag Indicator to compare the PO with the previous
1799 * or original revision
1800 * p_sequence_num Sequence number to identify the comparison
1801 * results for a specific record in
1802 * ICX_PO_REVISIONS_TEMP table.
1803 *
1804 * NOTES
1805 *
1806 * HISTORY
1807 * 01-SEP-1997 Rami Haddad Created
1808 * 05-SEP-1997 Matt Denton Debugged
1809 * 26-NOV-1997 Rami Haddad Handle comparison flag 'ALL.'
1810 ********************************************************************/
1811 PROCEDURE compare_po_line(
1812 p_line_id IN NUMBER,
1813 p_release_id IN NUMBER,
1814 p_revision_num IN NUMBER,
1815 p_comparison_flag IN VARCHAR2,
1816 p_sequence_num OUT NOCOPY NUMBER
1817 ) AS
1818
1819 v_sequence_num NUMBER := NULL;
1820 v_revision_counter NUMBER := p_revision_num;
1821 v_comparison_flag VARCHAR2( 80 ) := p_comparison_flag;
1822 v_progress VARCHAR2(3);
1823
1824 BEGIN
1825
1826 v_progress := '290';
1827 SELECT icx_po_history_details_s.nextval
1828 INTO v_sequence_num
1829 FROM DUAL;
1830 p_sequence_num := v_sequence_num;
1831
1832 v_progress := '300';
1833 IF v_comparison_flag = 'ALL' THEN
1834 v_comparison_flag := 'PREVIOUS';
1835 END IF;
1836
1837 LOOP
1838 v_progress := '310';
1839
1840 --Bug# 1788753.Adding the IF THEN condition.
1841 IF (p_release_id is NULL) THEN
1842 compare_line(
1843 p_line_id,
1844 v_revision_counter,
1845 v_comparison_flag,
1846 v_sequence_num );
1847 END IF;
1848
1849 v_progress := '320';
1850 compare_line_locs(
1851 p_line_id,
1852 p_release_id,
1853 v_revision_counter,
1854 v_comparison_flag,
1855 v_sequence_num );
1856
1857 v_progress := '330';
1858 compare_line_dists(
1859 p_line_id,
1860 p_release_id,
1861 v_revision_counter,
1862 v_comparison_flag,
1863 v_sequence_num );
1864
1865 v_revision_counter := v_revision_counter - 1;
1866
1867 IF v_revision_counter < 1 OR
1868 p_comparison_flag <> 'ALL'
1869 THEN
1870 EXIT;
1871 END IF;
1872
1873 END LOOP;
1874
1875 v_progress := '340';
1876 pos_compare_revisions.verify_no_differences( v_sequence_num );
1877
1878 EXCEPTION
1879 WHEN others THEN
1880 PO_MESSAGE_S.SQL_ERROR(
1881 'PO_REVISION_DIFFERENCES.COMPARE_PO_LINE',
1882 v_progress,
1883 sqlcode );
1884 RAISE;
1885
1886 END compare_po_line;
1887
1888
1889
1890 /*********************************************************************
1891 * NAME
1892 * compare_line
1893 *
1894 * PURPOSE
1895 * Compare a PO line with the previous or the original
1896 * revision---including all shipments, and distributions.
1897 *
1898 * ARGUMENTS
1899 * p_line_id Unique identified for the current PO line in
1900 * PO_LINES_ARCHIVE_ALL table
1901 * p_revision_num Current PO line revision number
1902 * p_comparison_flag Indicator to compare the PO with the previous
1903 * or original revision
1904 * p_sequence_num Sequence number to identify the comparison
1905 * results for a specific record in
1906 * ICX_PO_REVISIONS_TEMP table.
1907 *
1908 * NOTES
1909 * The original revision of a line is not necessarily 0. When
1910 * comparing with the original, obtain the PO line with the lowest
1911 * revision.
1912 *
1913 * HISTORY
1914 * 01-SEP-1997 Rami Haddad Created
1915 * 05-SEP-1997 Matt Denton Debugged
1916 * 24-NOV-1997 Rami Haddad Select PO line with minimum revision
1917 * as the original PO line, instead of
1918 * the one with revision 0.
1919 ********************************************************************/
1920 PROCEDURE compare_line(
1921 p_line_id IN NUMBER,
1922 p_revision_num IN NUMBER,
1923 p_comparison_flag IN VARCHAR2,
1924 p_sequence_num IN NUMBER
1925 ) AS
1926
1927 v_line_to po_lines_archive_all%ROWTYPE;
1928 v_line_from po_lines_archive_all%ROWTYPE;
1929
1930 v_previous_revision_num NUMBER;
1931 v_progress VARCHAR2(3);
1932
1933 BEGIN
1934
1935 v_progress := '350';
1936 IF p_revision_num <= 0
1937 THEN
1938 RETURN;
1939 END IF;
1940
1941 IF p_comparison_flag = 'ORIGINAL' THEN
1942 SELECT MIN( revision_num )
1943 INTO v_previous_revision_num
1944 FROM po_lines_archive_all
1945 WHERE po_line_id = p_line_id;
1946 ELSE
1947 v_previous_revision_num := p_revision_num - 1;
1948 END IF;
1949
1950 BEGIN
1951
1952 v_progress := '360';
1953 SELECT *
1954 INTO v_line_to
1955 FROM po_lines_archive_all pla1
1956 WHERE
1957 po_line_id = p_line_id
1958 AND revision_num = p_revision_num;
1959
1960 EXCEPTION
1961 WHEN no_data_found THEN
1962 v_line_to := NULL;
1963
1964 END;
1965
1966 BEGIN
1967
1968 v_progress := '370';
1969 SELECT *
1970 INTO v_line_from
1971 FROM po_lines_archive_all
1972 WHERE
1973 po_line_id = p_line_id
1974 AND revision_num = (
1975 SELECT MAX( revision_num )
1976 FROM po_lines_archive_all
1977 WHERE
1978 revision_num <= v_previous_revision_num
1979 AND po_line_id = p_line_id
1980 );
1981
1982 EXCEPTION
1983 WHEN no_data_found THEN
1984 v_line_from := NULL;
1985
1986 END;
1987
1988 v_progress := '380';
1989 pos_compare_revisions.compare_lines(
1990 v_line_from,
1991 v_line_to,
1992 p_sequence_num );
1993
1994 EXCEPTION
1995 WHEN others THEN
1996 PO_MESSAGE_S.SQL_ERROR(
1997 'PO_REVISION_DIFFERENCES.COMPARE_LINE',
1998 v_progress,
1999 sqlcode );
2000 RAISE;
2001
2002 END compare_line;
2003
2004
2005
2006 /*********************************************************************
2007 * NAME
2008 * compare_line_locs
2009 *
2010 * PURPOSE
2011 * Compare a PO line shipments with the previous or the original
2012 * revision---including all shipments, and distributions.
2013 *
2014 * ARGUMENTS
2015 * p_line_id Unique identified for the current PO line in
2016 * PO_LINES_ARCHIVE_ALL table
2017 * p_release_id Unique identified for the current PO release
2018 * in PO_RELEASES_ARCHIVE_ALL table
2019 * p_revision_num Current PO line revision number
2020 * p_comparison_flag Indicator to compare the PO with the previous
2021 * or original revision
2022 * p_sequence_num Sequence number to identify the comparison
2023 * results for a specific record in
2024 * ICX_PO_REVISIONS_TEMP table.
2025 *
2026 * NOTES
2027 *
2028 * HISTORY
2029 * 01-SEP-1997 Rami Haddad Created
2030 * 05-SEP-1997 Matt Denton Debugged
2031 * 20-NOV-1997 Rami Haddad Changed cursor to select latest
2032 * revision of shipments for the line,
2033 * given its revision number,
2034 * p_revision_num.
2035 ********************************************************************/
2036 PROCEDURE compare_line_locs(
2037 p_line_id IN NUMBER,
2038 p_release_id IN NUMBER,
2039 p_revision_num IN NUMBER,
2040 p_comparison_flag IN VARCHAR2,
2041 p_sequence_num IN NUMBER
2042 ) AS
2043
2044 CURSOR current_locations(
2045 current_line_id NUMBER,
2046 current_release_id NUMBER
2047 ) IS
2048 SELECT *
2049 FROM po_line_locations_archive_all plla1
2050 WHERE
2051 po_line_id = current_line_id
2052 AND NVL( po_release_id, -99 ) = current_release_id
2053 AND revision_num = (
2054 SELECT MAX( revision_num )
2055 FROM po_line_locations_archive_all plla2
2056 WHERE
2057 revision_num <= p_revision_num
2058 AND plla2.line_location_id = plla1.line_location_id
2059 );
2060
2061 v_loc_from po_line_locations_archive_all%ROWTYPE;
2062 v_loc_to po_line_locations_archive_all%ROWTYPE;
2063
2064 v_previous_revision_num NUMBER;
2065 v_progress VARCHAR2(3);
2066
2067 BEGIN
2068
2069 v_progress := '390';
2070 IF p_revision_num <= 0 THEN
2071 RETURN;
2072 END IF;
2073
2074 IF p_comparison_flag = 'ORIGINAL' THEN
2075 SELECT MIN( revision_num )
2076 INTO v_previous_revision_num
2077 FROM po_lines_archive_all
2078 WHERE po_line_id = p_line_id;
2079 ELSE
2080 v_previous_revision_num := p_revision_num - 1;
2081 END IF;
2082
2083 v_progress := '400';
2084 FOR v_loc_to IN current_locations(
2085 p_line_id,
2086 NVL( p_release_id, -99 )
2087 ) loop
2088
2089 /* Compare the lines only if the current line has a revision
2090 * greater than the calculated revision number of previous
2091 * line.
2092 */
2093 IF v_loc_to.revision_num > v_previous_revision_num THEN
2094
2095 BEGIN
2096
2097 v_progress := '410';
2098 SELECT *
2099 INTO v_loc_from
2100 FROM po_line_locations_archive_all
2101 WHERE
2102 line_location_id = v_loc_to.line_location_id
2103 AND revision_num =
2104 (
2105 SELECT MAX(revision_num)
2106 FROM po_line_locations_archive_all
2107 WHERE
2108 revision_num <=
2109 v_previous_revision_num
2110 AND line_location_id =
2111 v_loc_to.line_location_id
2112 );
2113
2114 EXCEPTION
2115 WHEN no_data_found THEN
2116 v_loc_from := NULL;
2117
2118 END;
2119
2120 v_progress := '420';
2121 pos_compare_revisions.compare_locations(
2122 v_loc_from,
2123 v_loc_to,
2124 p_sequence_num );
2125 END IF;
2126
2127 END loop;
2128
2129 EXCEPTION
2130 WHEN others THEN
2131 PO_MESSAGE_S.SQL_ERROR(
2132 'PO_REVISION_DIFFERENCES.COMPARE_LINE_LOCS',
2133 v_progress,
2134 sqlcode );
2135 RAISE;
2136
2137 END compare_line_locs;
2138
2139
2140
2141 /*********************************************************************
2142 * NAME
2143 * compare_line_dists
2144 *
2145 * PURPOSE
2146 * Compare a PO line distributions with the previous or the original
2147 * revision.
2148 *
2149 * ARGUMENTS
2150 * p_line_id Unique identified for the current PO line in
2151 * PO_LINES_ARCHIVE_ALL table
2152 * p_release_id Unique identified for the current PO release
2153 * in PO_RELEASES_ARCHIVE_ALL table
2154 * p_revision_num Current PO line revision number
2155 * p_comparison_flag Indicator to compare the PO with the previous
2156 * or original revision
2157 * p_sequence_num Sequence number to identify the comparison
2158 * results for a specific record in
2159 * ICX_PO_REVISIONS_TEMP table.
2160 *
2161 * NOTES
2162 *
2163 * HISTORY
2164 * 01-SEP-1997 Rami Haddad Created
2165 * 05-SEP-1997 Matt Denton Debugged
2166 * 20-NOV-1997 Rami Haddad Documented
2167 * Changed cursor to select latest
2168 * revision of distributions for the
2169 * line, given its revision number,
2170 * p_revision_num.
2171 ********************************************************************/
2172 PROCEDURE compare_line_dists(
2173 p_line_id IN NUMBER,
2174 p_release_id IN NUMBER,
2175 p_revision_num IN NUMBER,
2176 p_comparison_flag IN VARCHAR2,
2177 p_sequence_num IN NUMBER
2178 ) AS
2179
2180 CURSOR current_distributions(
2181 current_line_id number,
2182 current_release_id number
2183 ) IS
2184 SELECT *
2185 FROM po_distributions_archive_all pda1
2186 WHERE
2187 po_line_id = current_line_id
2188 AND NVL( po_release_id, -99 ) = current_release_id
2189 AND revision_num = (
2190 SELECT MAX( revision_num )
2191 FROM po_distributions_archive_all pda2
2192 WHERE
2193 revision_num <= p_revision_num
2194 AND pda2.po_distribution_id = pda1.po_distribution_id
2195 );
2196
2197 v_dist_from po_distributions_archive_all%ROWTYPE;
2198 v_dist_to po_distributions_archive_all%ROWTYPE;
2199
2200 v_previous_revision_num NUMBER;
2201 v_progress VARCHAR2(3);
2202
2203 BEGIN
2204
2205 v_progress := '430';
2206 IF p_revision_num <= 0 THEN
2207 RETURN;
2208 END IF;
2209
2210 IF p_comparison_flag = 'ORIGINAL' THEN
2211 SELECT MIN( revision_num )
2212 INTO v_previous_revision_num
2213 FROM po_lines_archive_all
2214 WHERE po_line_id = p_line_id;
2215 ELSE
2216 v_previous_revision_num := p_revision_num - 1;
2217 END IF;
2218
2219 FOR v_dist_to in current_distributions(
2220 p_line_id,
2221 NVL( p_release_id, -99 )
2222 ) LOOP
2223
2224 /* Compare the lines only if the current line has a revision
2225 * greater than the calculated revision number of previous
2226 * line.
2227 */
2228 IF v_dist_to.revision_num > v_previous_revision_num
2229 THEN
2230
2231 BEGIN
2232
2233 v_progress := '440';
2234 SELECT *
2235 INTO v_dist_from
2236 FROM po_distributions_archive_all
2237 WHERE
2238 po_distribution_id =
2239 v_dist_to.po_distribution_id
2240 AND revision_num = (
2241 SELECT MAX( revision_num )
2242 FROM po_distributions_archive_all
2243 WHERE revision_num <=
2244 v_previous_revision_num
2245 AND po_distribution_id =
2246 v_dist_to.po_distribution_id
2247 );
2248
2249 EXCEPTION
2250 WHEN no_data_found THEN
2251 v_dist_from := NULL;
2252
2253 END;
2254
2255 v_progress := '450';
2256 pos_compare_revisions.compare_distributions(
2257 v_dist_from,
2258 v_dist_to,
2259 p_sequence_num );
2260 END IF;
2261
2262 END LOOP;
2263
2264 EXCEPTION
2265 WHEN others THEN
2266 PO_MESSAGE_S.SQL_ERROR(
2267 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_ALL',
2268 v_progress,
2269 sqlcode );
2270 RAISE;
2271
2272 END compare_line_dists;
2273
2274 END pos_revision_differences;
[Home] [Help]