1 PACKAGE BODY po_revision_differences AS
2 /* $Header: POXPORVB.pls 115.4 2002/11/26 23:35:58 sbull 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 /*********************************************************************
294 * NAME
295 * compare_po
296 *
297 * PURPOSE
298 * Compare the a PO with the previous or the original
299 * revision---including all lines, shipments, and distributions.
300 *
301 * ARGUMENTS
302 * p_header_id Unique identified for the current PO in
303 * PO_HEADERS_ARCHIVE_ALL table
304 * p_release_id Unique identifier for the PO release in
305 * PO_RELEASES_ARCHIVE_ALL table
306 * p_revision_num Current PO revision number
307 * p_comparison_flag Indicator to compare the PO with the previous
308 * or original revision
309 * p_sequence_num Sequence number to identify the comparison
310 * results for a specific record in
311 * ICX_PO_REVISIONS_TEMP table.
312 *
313 * NOTES
314 *
315 * HISTORY
316 * 01-SEP-1997 Rami Haddad Created
317 * 05-SEP-1997 Matt Denton Debugged
318 * 25-NOV-1997 Rami Haddad Handle comparison flag 'ALL,' which
319 * loops through and compare all
320 * revisions.
321 ********************************************************************/
322 PROCEDURE compare_po(
323 p_header_id IN NUMBER,
324 p_release_id IN NUMBER,
325 p_revision_num IN NUMBER,
326 p_comparison_flag IN VARCHAR2,
327 p_sequence_num OUT NOCOPY NUMBER
328 ) AS
329
330 v_sequence_num NUMBER;
331 v_revision_counter NUMBER := p_revision_num;
332 v_comparison_flag VARCHAR2( 80 ) := p_comparison_flag;
333 v_progress VARCHAR2(3);
334
335 BEGIN
336
337 v_progress := '040';
338
339 SELECT icx_po_history_details_s.nextval
340 INTO v_sequence_num
341 FROM DUAL;
342 p_sequence_num := v_sequence_num;
343
344 IF v_comparison_flag = 'ALL' THEN
345 v_comparison_flag := 'PREVIOUS';
346 END IF;
347
348 LOOP
349 v_progress := '050';
350 compare_headers(
351 p_header_id,
352 p_release_id,
353 v_revision_counter,
354 v_comparison_flag,
355 v_sequence_num );
356
357 v_progress := '060';
358
359 -- Bug# 1761302. Added the IF THEN condition as there is
360 -- no change in lines for revisions against Releases.
361
362 IF (p_release_id is NULL) THEN
363 compare_lines(
364 p_header_id,
365 v_revision_counter,
366 v_comparison_flag,
367 v_sequence_num);
368 END IF;
369
370 v_progress := '070';
371 compare_locations(
372 p_header_id,
373 p_release_id,
374 v_revision_counter,
375 v_comparison_flag,
376 v_sequence_num);
377
378 v_progress := '080';
379 compare_distributions(
380 p_header_id,
381 p_release_id,
382 v_revision_counter,
383 v_comparison_flag,
384 v_sequence_num);
385
386 v_revision_counter := v_revision_counter - 1;
387
388 IF v_revision_counter < 1 OR
389 p_comparison_flag <> 'ALL'
390 THEN
391 EXIT;
392 END IF;
393
394 END LOOP;
395
396 v_progress := '090';
397 po_compare_revisions.verify_no_differences( v_sequence_num );
398
399 EXCEPTION
400 WHEN others THEN
401 PO_MESSAGE_S.SQL_ERROR(
402 'PO_REVISION_DIFFERENCES.COMPARE_PO',
403 v_progress,
404 sqlcode );
405 RAISE;
406
407 END compare_po;
408
409
410
411 /*********************************************************************
412 * NAME
413 * compare_headers
414 *
415 * PURPOSE
416 * Compare two headers, the first is according to the passed in
417 * parameters, P_HEADER_ID and P_REVISION_NUM. The second depends on
418 * the comparison flag, either the original PO (revision 0), or the
419 * previous revision (P_REVISION_NUM - 1 ).
420 *
421 * ARGUMENTS
422 * p_header_id Unique identified for the PO in
423 * PO_HEADERS_ARCHIVE_ALL table
424 * p_release_id Unique identifier for the PO release in
425 * PO_RELEASES_ARCHIVE_ALL table
426 * p_revision_num Current PO revision number
427 * p_comparison_flag Indicator to compare the PO with the previous
428 * or original revision
429 * p_sequence_num Sequence number to identify the comparison
430 * results for a specific record in
431 * ICX_PO_REVISIONS_TEMP table.
432 *
433 * NOTES
434 *
435 * HISTORY
436 * 01-SEP-1997 Rami Haddad Created
437 * 05-SEP-1997 Matt Denton Debugged
438 ********************************************************************/
439 PROCEDURE compare_headers(
440 p_header_id IN NUMBER,
441 p_release_id IN NUMBER,
442 p_revision_num IN NUMBER,
443 p_comparison_flag IN VARCHAR2,
444 p_sequence_num IN NUMBER
445 ) AS
446
447 v_header_from po_headers_archive%ROWTYPE;
448 v_header_to po_headers_archive%ROWTYPE;
449
450 v_release_from po_releases_archive%ROWTYPE;
451 v_release_to po_releases_archive%ROWTYPE;
452
453 v_previous_revision_num NUMBER;
454 v_progress VARCHAR2(3);
455
456 BEGIN
457
458 v_progress := '100';
459
460 IF p_revision_num <= 0
461 THEN
462 RETURN;
463 END IF;
464
465 /* What is the revision number we will compare against? */
466 IF p_comparison_flag = 'ORIGINAL'
467 THEN
468 v_previous_revision_num := 0;
469 ELSE
470 v_previous_revision_num := p_revision_num - 1;
471 END IF;
472
473 IF p_release_id IS NULL THEN
474
475 BEGIN
476
477 v_progress := '110';
478 SELECT *
479 INTO v_header_to
480 FROM po_headers_archive
481 WHERE
482 po_header_id = p_header_id
483 AND revision_num = p_revision_num;
484
485 EXCEPTION
486 WHEN no_data_found THEN
487 v_header_to := NULL;
488
489 END;
490
491 BEGIN
492
493 v_progress := '120';
494 SELECT *
495 INTO v_header_from
496 FROM po_headers_archive
497 WHERE
498 po_header_id = p_header_id
499 AND revision_num = v_previous_revision_num;
500
501 EXCEPTION
502 WHEN no_data_found THEN
503 v_header_from := NULL;
504
505 END;
506
507 v_progress := '130';
508 po_compare_revisions.compare_headers(
509 v_header_from,
510 v_header_to,
511 p_sequence_num );
512
513 ELSE
514
515 BEGIN
516
517 v_progress := '140';
518 SELECT *
519 INTO v_release_to
523 AND revision_num = p_revision_num;
520 FROM po_releases_archive
521 WHERE
522 po_release_id = p_release_id
524
525 EXCEPTION
526 WHEN no_data_found THEN
527 v_release_to := NULL;
528
529 END;
530
531 BEGIN
532
533 v_progress := '150';
534 SELECT *
535 INTO v_release_from
536 FROM po_releases_archive
537 WHERE
538 po_release_id = p_release_id
539 AND revision_num = v_previous_revision_num;
540
541 EXCEPTION
542 WHEN no_data_found THEN
543 v_release_from := NULL;
544
545 END;
546
547 v_progress := '160';
548 po_compare_revisions.compare_releases(
549 v_release_from,
550 v_release_to,
551 p_sequence_num );
552
553 END IF;
554
555 EXCEPTION
556 WHEN others THEN
557 PO_MESSAGE_S.SQL_ERROR(
558 'PO_REVISION_DIFFERENCES.COMPARE_HEADERS',
559 v_progress,
560 sqlcode );
561 RAISE;
562
563 END compare_headers;
564
565
566
567 /*********************************************************************
568 * NAME
569 * compare_lines
570 *
571 * PURPOSE
572 * Compare lines for two POs, the first is according to the passed in
573 * parameters, P_HEADER_ID and P_REVISION_NUM. The second depends on
574 * the comparison flag, either the original PO (revision 0), or the
575 * previous revision (P_REVISION_NUM - 1 ).
576 *
577 * ARGUMENTS
578 * p_header_id Unique identified for the PO in
579 * PO_HEADERS_ARCHIVE_ALL table
580 * p_release_id Unique identifier for the PO release in
581 * PO_RELEASES_ARCHIVE_ALL table
582 * p_revision_num Current PO revision number
583 * p_comparison_flag Indicator to compare the PO with the previous
584 * or original revision
585 * p_sequence_num Sequence number to identify the comparison
586 * results for a specific record in
587 * ICX_PO_REVISIONS_TEMP table.
588 *
589 * NOTES
590 *
591 * HISTORY
592 * 01-SEP-1997 Rami Haddad Created
593 * 05-SEP-1997 Matt Denton Debugged
594 * 20-NOV-1997 Rami Haddad Changed cursor to select latest
595 * revision of lines for the PO, given
596 * its revision number, p_revision_num.
597 ********************************************************************/
598 PROCEDURE compare_lines(
599 p_header_id IN NUMBER,
600 p_revision_num IN NUMBER,
601 p_comparison_flag IN VARCHAR2,
602 p_sequence_num IN NUMBER
603 ) AS
604
605 CURSOR current_lines_cursor(
606 current_header_id NUMBER,
607 current_revision_num NUMBER ) IS
608 SELECT *
609 FROM po_lines_archive pla1
610 WHERE
611 po_header_id = current_header_id
612 AND revision_num = (
613 SELECT MAX( revision_num )
614 FROM po_lines_archive pla2
615 WHERE
616 revision_num <= current_revision_num
617 AND pla2.po_line_id = pla1.po_line_id
618 );
619
620 v_line_to po_lines_archive%ROWTYPE;
621 v_line_from po_lines_archive%ROWTYPE;
622
623 v_previous_revision_num NUMBER;
624 v_progress VARCHAR2(3);
625
626 BEGIN
627
628 v_progress := '170';
629
630 IF p_revision_num <= 0 THEN
631 RETURN;
632 END IF;
633
634 IF p_comparison_flag = 'ORIGINAL'
635 THEN
636 v_previous_revision_num := 0;
637 ELSE
638 v_previous_revision_num := p_revision_num - 1;
639 END IF;
640
641 FOR v_line_to IN current_lines_cursor(
642 p_header_id,
643 p_revision_num
644 ) LOOP
645
646 /* Compare the lines only if the current line has a revision
647 * greater than the calculated revision number of previous
648 * line.
649 */
650 IF v_line_to.revision_num > v_previous_revision_num
651 THEN
652
653 BEGIN
654
655 v_progress := '180';
656 SELECT *
657 INTO v_line_from
658 FROM po_lines_archive
659 WHERE
660 revision_num = (
661 SELECT MAX( revision_num )
662 FROM po_lines_archive
663 WHERE
664 revision_num <=
665 v_previous_revision_num
666 AND po_line_id = v_line_to.po_line_id
667 )
668 AND po_line_id = v_line_to.po_line_id;
669
670 EXCEPTION
671 WHEN no_data_found THEN
672 v_line_from := NULL;
673
674 END;
675
676 v_progress := '190';
677 po_compare_revisions.compare_lines(
678 v_line_from,
679 v_line_to,
680 p_sequence_num);
681 END IF;
682
683 END LOOP;
684
685 EXCEPTION
686 WHEN others THEN
687 PO_MESSAGE_S.SQL_ERROR(
688 'PO_REVISION_DIFFERENCES.COMPARE_LINES',
689 v_progress,
690 sqlcode );
691 RAISE;
692
693 END compare_lines;
694
695
696
697 /*********************************************************************
698 * NAME
699 * compare_locations
700 *
701 * PURPOSE
702 * Compare shipments for two POs, the first is according to the passed
703 * in parameters, P_HEADER_ID and P_REVISION_NUM. The second depends
704 * on the comparison flag, either the original PO (revision 0), or the
705 * previous revision (P_REVISION_NUM - 1 ).
706 *
707 * ARGUMENTS
708 * p_header_id Unique identified for the PO in
709 * PO_HEADERS_ARCHIVE_ALL table
710 * p_release_id Unique identifier for the PO release in
711 * PO_RELEASES_ARCHIVE_ALL table
712 * p_revision_num Current PO revision number
713 * p_comparison_flag Indicator to compare the PO with the previous
714 * or original revision
718 *
715 * p_sequence_num Sequence number to identify the comparison
716 * results for a specific record in
717 * ICX_PO_REVISIONS_TEMP table.
719 * NOTES
720 *
721 * HISTORY
722 * 01-SEP-1997 Rami Haddad Created
723 * 05-SEP-1997 Matt Denton Debugged
724 * 20-NOV-1997 Rami Haddad Changed cursor to select latest
725 * revision of shipments for the PO,
726 * given its revision number,
727 * p_revision_num.
728 ********************************************************************/
729 PROCEDURE compare_locations(
730 p_header_id IN NUMBER,
731 p_release_id IN NUMBER,
732 p_revision_num IN NUMBER,
733 p_comparison_flag IN VARCHAR2,
734 p_sequence_num IN NUMBER
735 ) AS
736
737 CURSOR current_locations_cursor(
738 current_header_id NUMBER,
739 current_release_id NUMBER,
740 current_revision_num NUMBER ) IS
741 SELECT *
742 FROM po_line_locations_archive plla1
743 WHERE po_header_id = current_header_id
744 AND NVL( po_release_id, -99 ) = current_release_id
745 AND revision_num = (
746 SELECT MAX( revision_num )
747 FROM po_line_locations_archive plla2
748 WHERE
749 revision_num <= current_revision_num
750 AND plla2.line_location_id = plla1.line_location_id
751 );
752
753 v_loc_from po_line_locations_archive%ROWTYPE;
754 v_loc_to po_line_locations_archive%ROWTYPE;
755
756 v_previous_revision_num NUMBER;
757 v_progress VARCHAR2(3);
758
759 BEGIN
760
761 v_progress := '200';
762
763 IF p_revision_num <= 0 THEN
764 RETURN;
765 END IF;
766
767 IF p_comparison_flag = 'ORIGINAL'
768 THEN
769 v_previous_revision_num := 0;
770 ELSE
771 v_previous_revision_num := p_revision_num - 1;
772 END IF;
773
774 FOR v_loc_to in current_locations_cursor(
775 p_header_id,
776 NVL( p_release_id, -99 ),
777 p_revision_num
778 ) LOOP
779
780 /* Compare the lines only if the current line has a revision
781 * greater than the calculated revision number of previous
782 * line.
783 */
784 IF v_loc_to.revision_num > v_previous_revision_num THEN
785
786 BEGIN
787
788 v_progress := '210';
789 SELECT *
790 INTO v_loc_from
791 FROM po_line_locations_archive
792 WHERE
793 revision_num = (
794 SELECT MAX( revision_num )
795 FROM po_line_locations_archive
796 WHERE
797 revision_num <=
798 v_previous_revision_num
799 AND line_location_id =
800 v_loc_to.line_location_id
801 )
802 AND line_location_id = v_loc_to.line_location_id;
803
804 EXCEPTION
805 WHEN no_data_found THEN
806 v_loc_from := NULL;
807
808 END;
809
810 v_progress := '220';
811 po_compare_revisions.compare_locations(
812 v_loc_from,
813 v_loc_to,
814 p_sequence_num );
815 END IF;
816
817 END LOOP;
818
819 EXCEPTION
820 WHEN others THEN
821 PO_MESSAGE_S.SQL_ERROR(
822 'PO_REVISION_DIFFERENCES.COMPARE_LINES',
823 v_progress,
824 sqlcode );
825 RAISE;
826
827 END compare_locations;
828
829
830
831 /*********************************************************************
832 * NAME
833 * compare_distributions
834 *
835 * PURPOSE
836 * Compare distributions for two POs, the first is according to the
837 * passed in parameters, P_HEADER_ID and P_REVISION_NUM. The second
838 * depends on the comparison flag, either the original PO (revision
839 * 0), or the previous revision (P_REVISION_NUM - 1 ).
840 *
841 * ARGUMENTS
842 * p_header_id Unique identified for the PO in
843 * PO_HEADERS_ARCHIVE_ALL table
844 * p_release_id Unique identifier for the PO release in
845 * PO_RELEASES_ARCHIVE_ALL table
846 * p_revision_num Current PO revision number
847 * p_comparison_flag Indicator to compare the PO with the previous
848 * or original revision
849 * p_sequence_num Sequence number to identify the comparison
850 * results for a specific record in
851 * ICX_PO_REVISIONS_TEMP table.
852 *
853 * NOTES
854 *
855 * HISTORY
856 * 01-SEP-1997 Rami Haddad Created
857 * 05-SEP-1997 Matt Denton Debugged
858 * 20-NOV-1997 Rami Haddad Changed cursor to select latest
859 * revision of distributions for the PO,
860 * given its revision number,
861 * p_revision_num.
862 ********************************************************************/
863 PROCEDURE compare_distributions(
864 p_header_id IN NUMBER,
865 p_release_id IN NUMBER,
866 p_revision_num IN NUMBER,
867 p_comparison_flag IN VARCHAR2,
868 p_sequence_num IN NUMBER
869 ) AS
870
871 CURSOR current_distributions_cursor(
872 current_header_id NUMBER,
873 current_release_id NUMBER,
874 current_revision_num NUMBER
875 ) IS
876 SELECT *
877 FROM po_distributions_archive pda1
878 WHERE
879 po_header_id = current_header_id
880 AND NVL( po_release_id, -99 ) = current_release_id
881 AND revision_num = (
882 SELECT MAX( revision_num )
883 FROM po_distributions_archive pda2
884 WHERE
885 revision_num <= current_revision_num
886 AND pda2.po_distribution_id = pda1.po_distribution_id
887 );
888
889 v_dist_from po_distributions_archive%ROWTYPE;
890 v_dist_to po_distributions_archive%ROWTYPE;
891
892 v_previous_revision_num NUMBER;
893 v_progress VARCHAR2(3);
894
895 BEGIN
896
897 v_progress := '230';
898
899 IF p_revision_num <= 0 THEN
900 RETURN;
901 END IF;
905 ELSE
902
903 IF p_comparison_flag = 'ORIGINAL' THEN
904 v_previous_revision_num := 0;
906 v_previous_revision_num := p_revision_num - 1;
907 END IF;
908
909 FOR v_dist_to in current_distributions_cursor(
910 p_header_id,
911 NVL( p_release_id, -99 ),
912 p_revision_num
913 ) LOOP
914
915 /* Compare the lines only if the current line has a revision
916 * greater than the calculated revision number of previous
917 * line.
918 */
919 IF v_dist_to.revision_num > v_previous_revision_num
920 THEN
921
922 BEGIN
923
924 v_progress := '240';
925 SELECT *
926 INTO v_dist_from
927 FROM po_distributions_archive
928 WHERE
929 revision_num = (
930 SELECT MAX( revision_num )
931 FROM po_distributions_archive
932 WHERE revision_num <=
933 v_previous_revision_num
934 AND po_distribution_id =
935 v_dist_to.po_distribution_id
936 )
937 AND po_distribution_id =
938 v_dist_to.po_distribution_id;
939
940 EXCEPTION
941 WHEN no_data_found THEN
942 v_dist_from := NULL;
943
944 END;
945
946 v_progress := '250';
947 po_compare_revisions.compare_distributions(
948 v_dist_from,
949 v_dist_to,
950 p_sequence_num );
951 END IF;
952
953 END LOOP;
954
955 EXCEPTION
956 WHEN others THEN
957 PO_MESSAGE_S.SQL_ERROR(
958 'PO_REVISION_DIFFERENCES.COMPARE_DISTRIBUTIONS',
959 v_progress,
960 sqlcode );
961 RAISE;
962
963 END compare_distributions;
964
965
966
967 /*********************************************************************
968 * NAME
969 * compare_line_to_all
970 *
971 * PURPOSE
972 * Call the procedure COMPARE_PO_LINE with ALL as comparison flag.
973 *
974 * ARGUMENTS
975 * p_line_id Unique identified for the current PO line in
976 * PO_LINES_ARCHIVE_ALL table
977 * p_release_id Unique identifier for the PO release in
978 * PO_RELEASES_ARCHIVE_ALL table
979 * p_revision_num Current PO line revision number
980 * p_dummy_in4 Not required
981 * p_dummy_in5 Not required
982 * p_dummy_in6 Not required
983 * p_dummy_in7 Not required
984 * p_dummy_in8 Not required
985 * p_dummy_in9 Not required
986 * p_dummy_in10 Not required
987 * p_sequence_num Sequence number to identify the comparison
988 * results for a specific record in
989 * ICX_PO_REVISIONS_TEMP table.
990 * p_dummy_out2 Not required
991 * p_dummy_out3 Not required
992 * p_dummy_out4 Not required
993 * p_dummy_out5 Not required
994 * p_dummy_out6 Not required
995 * p_dummy_out7 Not required
996 * p_dummy_out8 Not required
997 * p_dummy_out9 Not required
998 * p_dummy_out10 Not required
999 *
1000 * NOTES
1001 * This procedures serves the purpose of a link from WAD to call the
1002 * procedure COMPARE_PO_LINE. There is no way to store the comparison
1003 * flag in the link. Therefore, the link calls this procedure, which
1004 * calls COMPARE_PO_LINE with the appropriate flag.
1005 *
1006 * Normally, you do not want to call this procedure directly. You can
1007 * simply call COMPARE_PO_LINE with the appropriate parameters.
1008 *
1009 * HISTORY
1010 * 01-SEP-1997 Rami Haddad Created
1011 * 05-SEP-1997 Matt Denton Debugged
1012 ********************************************************************/
1013 PROCEDURE compare_line_to_all(
1014 p_line_id IN NUMBER,
1015 p_release_id IN NUMBER,
1016 p_revision_num IN NUMBER,
1017 p_dummy_in4 IN NUMBER DEFAULT NULL,
1018 p_dummy_in5 IN NUMBER DEFAULT NULL,
1019 p_dummy_in6 IN NUMBER DEFAULT NULL,
1020 p_dummy_in7 IN NUMBER DEFAULT NULL,
1021 p_dummy_in8 IN NUMBER DEFAULT NULL,
1022 p_dummy_in9 IN NUMBER DEFAULT NULL,
1023 p_dummy_in10 IN NUMBER DEFAULT NULL,
1024 p_sequence_num OUT NOCOPY NUMBER,
1025 p_dummy_out2 OUT NOCOPY NUMBER,
1026 p_dummy_out3 OUT NOCOPY NUMBER,
1027 p_dummy_out4 OUT NOCOPY NUMBER,
1028 p_dummy_out5 OUT NOCOPY NUMBER,
1029 p_dummy_out6 OUT NOCOPY NUMBER,
1030 p_dummy_out7 OUT NOCOPY NUMBER,
1031 p_dummy_out8 OUT NOCOPY NUMBER,
1032 p_dummy_out9 OUT NOCOPY NUMBER,
1033 p_dummy_out10 OUT NOCOPY NUMBER
1034 ) AS
1035
1036 v_sequence_num NUMBER := NULL;
1037 v_progress VARCHAR2(3);
1038
1039 BEGIN
1040
1041 v_progress := '260';
1042
1043 compare_po_line(
1044 p_line_id,
1045 p_release_id,
1046 p_revision_num,
1047 'ALL',
1048 v_sequence_num );
1049
1050 p_sequence_num := v_sequence_num;
1051
1052 EXCEPTION
1053 WHEN others THEN
1054 PO_MESSAGE_S.SQL_ERROR(
1055 'PO_REVISION_DIFFERENCES.COMPARE_LINE_TO_ALL',
1056 v_progress,
1057 sqlcode );
1058 RAISE;
1059
1060 END compare_line_to_all;
1061
1062
1063
1064 /*********************************************************************
1065 * NAME
1066 * compare_line_to_original
1067 *
1068 * PURPOSE
1069 * Call the procedure COMPARE_PO_LINE with the ORIGINAL as comparison
1070 * flag.
1071 *
1072 * ARGUMENTS
1073 * p_line_id Unique identified for the current PO line in
1074 * PO_LINES_ARCHIVE_ALL table
1075 * p_release_id Unique identifier for the PO release in
1076 * PO_RELEASES_ARCHIVE_ALL table
1077 * p_revision_num Current PO line revision number
1078 * p_dummy_in4 Not required
1079 * p_dummy_in5 Not required
1080 * p_dummy_in6 Not required
1081 * p_dummy_in7 Not required
1082 * p_dummy_in8 Not required
1083 * p_dummy_in9 Not required
1084 * p_dummy_in10 Not required
1088 * p_dummy_out2 Not required
1085 * p_sequence_num Sequence number to identify the comparison
1086 * results for a specific record in
1087 * ICX_PO_REVISIONS_TEMP table.
1089 * p_dummy_out3 Not required
1090 * p_dummy_out4 Not required
1091 * p_dummy_out5 Not required
1092 * p_dummy_out6 Not required
1093 * p_dummy_out7 Not required
1094 * p_dummy_out8 Not required
1095 * p_dummy_out9 Not required
1096 * p_dummy_out10 Not required
1097 *
1098 * NOTES
1099 * This procedures serves the purpose of a link from WAD to call the
1100 * procedure COMPARE_PO_LINE. There is no way to store the comparison
1101 * flag in the link. Therefore, the link calls this procedure, which
1102 * calls COMPARE_PO_LINE with the appropriate flag.
1103 *
1104 * Normally, you do not want to call this procedure directly. You can
1105 * simply call COMPARE_PO_LINE with the appropriate parameters.
1106 *
1107 * HISTORY
1108 * 01-SEP-1997 Rami Haddad Created
1109 * 05-SEP-1997 Matt Denton Debugged
1110 ********************************************************************/
1111 PROCEDURE compare_line_to_original(
1112 p_line_id IN NUMBER,
1113 p_release_id IN NUMBER,
1114 p_revision_num IN NUMBER,
1115 p_dummy_in4 IN NUMBER DEFAULT NULL,
1116 p_dummy_in5 IN NUMBER DEFAULT NULL,
1117 p_dummy_in6 IN NUMBER DEFAULT NULL,
1118 p_dummy_in7 IN NUMBER DEFAULT NULL,
1119 p_dummy_in8 IN NUMBER DEFAULT NULL,
1120 p_dummy_in9 IN NUMBER DEFAULT NULL,
1121 p_dummy_in10 IN NUMBER DEFAULT NULL,
1122 p_sequence_num OUT NOCOPY NUMBER,
1123 p_dummy_out2 OUT NOCOPY NUMBER,
1124 p_dummy_out3 OUT NOCOPY NUMBER,
1125 p_dummy_out4 OUT NOCOPY NUMBER,
1126 p_dummy_out5 OUT NOCOPY NUMBER,
1127 p_dummy_out6 OUT NOCOPY NUMBER,
1128 p_dummy_out7 OUT NOCOPY NUMBER,
1129 p_dummy_out8 OUT NOCOPY NUMBER,
1130 p_dummy_out9 OUT NOCOPY NUMBER,
1131 p_dummy_out10 OUT NOCOPY NUMBER
1132 ) AS
1133
1134 v_sequence_num NUMBER := NULL;
1135 v_progress VARCHAR2(3);
1136
1137 BEGIN
1138
1139 v_progress := '270';
1140
1141 compare_po_line(
1142 p_line_id,
1143 p_release_id,
1144 p_revision_num,
1145 'ORIGINAL',
1146 v_sequence_num );
1147
1148 p_sequence_num := v_sequence_num;
1149
1150 EXCEPTION
1151 WHEN others THEN
1152 PO_MESSAGE_S.SQL_ERROR(
1153 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_ORIGINAL',
1154 v_progress,
1155 sqlcode );
1156 RAISE;
1157
1158 END compare_line_to_original;
1159
1160
1161
1162 /*********************************************************************
1163 * NAME
1164 * compare_line_to_previous
1165 *
1166 * PURPOSE
1167 * Call the procedure COMPARE_PO_LINE with the PREVIOUS as comparison
1168 * flag.
1169 *
1170 * ARGUMENTS
1171 * p_line_id Unique identified for the current PO line in
1172 * PO_LINES_ARCHIVE_ALL table
1173 * p_release_id Unique identifier for the PO release in
1174 * PO_RELEASES_ARCHIVE_ALL table
1175 * p_revision_num Current PO line revision number
1176 * p_dummy_in4 Not required
1177 * p_dummy_in5 Not required
1178 * p_dummy_in6 Not required
1179 * p_dummy_in7 Not required
1180 * p_dummy_in8 Not required
1181 * p_dummy_in9 Not required
1182 * p_dummy_in10 Not required
1183 * p_sequence_num Sequence number to identify the comparison
1184 * results for a specific record in
1185 * ICX_PO_REVISIONS_TEMP table.
1186 * p_dummy_out2 Not required
1187 * p_dummy_out3 Not required
1188 * p_dummy_out4 Not required
1189 * p_dummy_out5 Not required
1190 * p_dummy_out6 Not required
1191 * p_dummy_out7 Not required
1192 * p_dummy_out8 Not required
1193 * p_dummy_out9 Not required
1194 * p_dummy_out10 Not required
1195 *
1196 * NOTES
1197 * This procedures serves the purpose of a link from WAD to call the
1198 * procedure COMPARE_PO_LINE. We need to have two links in the flow
1199 * to compare the PO line with the original or with the previous.
1200 * There is no way to store the comparison flag in the link.
1201 * Therefore, the link calls this procedure, which calls
1202 * COMPARE_PO_LINE with ORIGINAL flag.
1203 *
1204 * Normally, you do not want to call this procedure directly. You can
1205 * simply call COMPARE_PO_LINE with the appropriate parameters.
1206 *
1207 * HISTORY
1208 * 01-SEP-1997 Rami Haddad Created
1209 * 05-SEP-1997 Matt Denton Debugged
1210 ********************************************************************/
1211 PROCEDURE compare_line_to_previous(
1212 p_line_id IN NUMBER,
1213 p_release_id IN NUMBER,
1214 p_revision_num IN NUMBER,
1215 p_dummy_in4 IN NUMBER DEFAULT NULL,
1216 p_dummy_in5 IN NUMBER DEFAULT NULL,
1217 p_dummy_in6 IN NUMBER DEFAULT NULL,
1218 p_dummy_in7 IN NUMBER DEFAULT NULL,
1219 p_dummy_in8 IN NUMBER DEFAULT NULL,
1220 p_dummy_in9 IN NUMBER DEFAULT NULL,
1221 p_dummy_in10 IN NUMBER DEFAULT NULL,
1222 p_sequence_num OUT NOCOPY NUMBER,
1223 p_dummy_out2 OUT NOCOPY NUMBER,
1224 p_dummy_out3 OUT NOCOPY NUMBER,
1225 p_dummy_out4 OUT NOCOPY NUMBER,
1226 p_dummy_out5 OUT NOCOPY NUMBER,
1227 p_dummy_out6 OUT NOCOPY NUMBER,
1228 p_dummy_out7 OUT NOCOPY NUMBER,
1229 p_dummy_out8 OUT NOCOPY NUMBER,
1230 p_dummy_out9 OUT NOCOPY NUMBER,
1231 p_dummy_out10 OUT NOCOPY NUMBER
1232 ) AS
1233
1234 v_sequence_num NUMBER := NULL;
1235 v_progress VARCHAR2(3);
1236
1237 BEGIN
1238
1239 v_progress := '280';
1240
1241 compare_po_line(
1242 p_line_id,
1243 p_release_id,
1244 p_revision_num,
1245 'PREVIOUS',
1246 v_sequence_num);
1247
1248 p_sequence_num := v_sequence_num;
1249
1250 EXCEPTION
1251 WHEN others THEN
1252 PO_MESSAGE_S.SQL_ERROR(
1256 RAISE;
1253 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_PREVIOUS',
1254 v_progress,
1255 sqlcode );
1257
1258 END compare_line_to_previous;
1259
1260
1261
1262 /*********************************************************************
1263 * NAME
1264 * compare_po_line
1265 *
1266 * PURPOSE
1267 * Compare a PO line with the previous or the original
1268 * revision---including all shipments, and distributions.
1269 *
1270 * ARGUMENTS
1271 * p_line_id Unique identified for the current PO line in
1272 * PO_LINES_ARCHIVE_ALL table
1273 * p_release_id Unique identifier for the PO release in
1274 * PO_RELEASES_ARCHIVE_ALL table
1275 * p_revision_num Current PO line revision number
1276 * p_comparison_flag Indicator to compare the PO with the previous
1277 * or original revision
1278 * p_sequence_num Sequence number to identify the comparison
1279 * results for a specific record in
1280 * ICX_PO_REVISIONS_TEMP table.
1281 *
1282 * NOTES
1283 *
1284 * HISTORY
1285 * 01-SEP-1997 Rami Haddad Created
1286 * 05-SEP-1997 Matt Denton Debugged
1287 * 26-NOV-1997 Rami Haddad Handle comparison flag 'ALL.'
1288 ********************************************************************/
1289 PROCEDURE compare_po_line(
1290 p_line_id IN NUMBER,
1291 p_release_id IN NUMBER,
1292 p_revision_num IN NUMBER,
1293 p_comparison_flag IN VARCHAR2,
1294 p_sequence_num OUT NOCOPY NUMBER
1295 ) AS
1296
1297 v_sequence_num NUMBER := NULL;
1298 v_revision_counter NUMBER := p_revision_num;
1299 v_comparison_flag VARCHAR2( 80 ) := p_comparison_flag;
1300 v_progress VARCHAR2(3);
1301
1302 BEGIN
1303
1304 v_progress := '290';
1305 SELECT icx_po_history_details_s.nextval
1306 INTO v_sequence_num
1307 FROM DUAL;
1308 p_sequence_num := v_sequence_num;
1309
1310 v_progress := '300';
1311 IF v_comparison_flag = 'ALL' THEN
1312 v_comparison_flag := 'PREVIOUS';
1313 END IF;
1314
1315 LOOP
1316 v_progress := '310';
1317
1318 --Bug# 1788753.Adding the IF THEN condition.
1319 IF (p_release_id is NULL) THEN
1320 compare_line(
1321 p_line_id,
1322 v_revision_counter,
1323 v_comparison_flag,
1324 v_sequence_num );
1325 END IF;
1326
1327 v_progress := '320';
1328 compare_line_locs(
1329 p_line_id,
1330 p_release_id,
1331 v_revision_counter,
1332 v_comparison_flag,
1333 v_sequence_num );
1334
1335 v_progress := '330';
1336 compare_line_dists(
1337 p_line_id,
1338 p_release_id,
1339 v_revision_counter,
1340 v_comparison_flag,
1341 v_sequence_num );
1342
1343 v_revision_counter := v_revision_counter - 1;
1344
1345 IF v_revision_counter < 1 OR
1346 p_comparison_flag <> 'ALL'
1347 THEN
1348 EXIT;
1349 END IF;
1350
1351 END LOOP;
1352
1353 v_progress := '340';
1354 po_compare_revisions.verify_no_differences( v_sequence_num );
1355
1356 EXCEPTION
1357 WHEN others THEN
1358 PO_MESSAGE_S.SQL_ERROR(
1359 'PO_REVISION_DIFFERENCES.COMPARE_PO_LINE',
1360 v_progress,
1361 sqlcode );
1362 RAISE;
1363
1364 END compare_po_line;
1365
1366
1367
1368 /*********************************************************************
1369 * NAME
1370 * compare_line
1371 *
1372 * PURPOSE
1373 * Compare a PO line with the previous or the original
1374 * revision---including all shipments, and distributions.
1375 *
1376 * ARGUMENTS
1377 * p_line_id Unique identified for the current PO line in
1378 * PO_LINES_ARCHIVE_ALL table
1379 * p_revision_num Current PO line revision number
1380 * p_comparison_flag Indicator to compare the PO with the previous
1381 * or original revision
1382 * p_sequence_num Sequence number to identify the comparison
1383 * results for a specific record in
1384 * ICX_PO_REVISIONS_TEMP table.
1385 *
1386 * NOTES
1387 * The original revision of a line is not necessarily 0. When
1388 * comparing with the original, obtain the PO line with the lowest
1389 * revision.
1390 *
1391 * HISTORY
1392 * 01-SEP-1997 Rami Haddad Created
1393 * 05-SEP-1997 Matt Denton Debugged
1394 * 24-NOV-1997 Rami Haddad Select PO line with minimum revision
1395 * as the original PO line, instead of
1396 * the one with revision 0.
1397 ********************************************************************/
1398 PROCEDURE compare_line(
1399 p_line_id IN NUMBER,
1400 p_revision_num IN NUMBER,
1401 p_comparison_flag IN VARCHAR2,
1402 p_sequence_num IN NUMBER
1403 ) AS
1404
1405 v_line_to po_lines_archive%ROWTYPE;
1406 v_line_from po_lines_archive%ROWTYPE;
1407
1408 v_previous_revision_num NUMBER;
1409 v_progress VARCHAR2(3);
1410
1411 BEGIN
1412
1413 v_progress := '350';
1414 IF p_revision_num <= 0
1415 THEN
1416 RETURN;
1417 END IF;
1418
1419 IF p_comparison_flag = 'ORIGINAL' THEN
1420 SELECT MIN( revision_num )
1421 INTO v_previous_revision_num
1422 FROM po_lines_archive
1423 WHERE po_line_id = p_line_id;
1424 ELSE
1425 v_previous_revision_num := p_revision_num - 1;
1426 END IF;
1427
1428 BEGIN
1429
1430 v_progress := '360';
1431 SELECT *
1432 INTO v_line_to
1433 FROM po_lines_archive pla1
1434 WHERE
1435 po_line_id = p_line_id
1436 AND revision_num = p_revision_num;
1437
1438 EXCEPTION
1439 WHEN no_data_found THEN
1440 v_line_to := NULL;
1441
1442 END;
1443
1444 BEGIN
1445
1446 v_progress := '370';
1447 SELECT *
1448 INTO v_line_from
1449 FROM po_lines_archive
1450 WHERE
1451 po_line_id = p_line_id
1452 AND revision_num = (
1453 SELECT MAX( revision_num )
1454 FROM po_lines_archive
1455 WHERE
1456 revision_num <= v_previous_revision_num
1457 AND po_line_id = p_line_id
1458 );
1459
1460 EXCEPTION
1461 WHEN no_data_found THEN
1462 v_line_from := NULL;
1463
1464 END;
1465
1466 v_progress := '380';
1467 po_compare_revisions.compare_lines(
1468 v_line_from,
1469 v_line_to,
1470 p_sequence_num );
1471
1472 EXCEPTION
1473 WHEN others THEN
1474 PO_MESSAGE_S.SQL_ERROR(
1475 'PO_REVISION_DIFFERENCES.COMPARE_LINE',
1476 v_progress,
1477 sqlcode );
1478 RAISE;
1479
1480 END compare_line;
1481
1482
1483
1484 /*********************************************************************
1485 * NAME
1486 * compare_line_locs
1487 *
1488 * PURPOSE
1489 * Compare a PO line shipments with the previous or the original
1490 * revision---including all shipments, and distributions.
1491 *
1492 * ARGUMENTS
1493 * p_line_id Unique identified for the current PO line in
1494 * PO_LINES_ARCHIVE_ALL table
1495 * p_release_id Unique identified for the current PO release
1496 * in PO_RELEASES_ARCHIVE_ALL table
1497 * p_revision_num Current PO line revision number
1498 * p_comparison_flag Indicator to compare the PO with the previous
1499 * or original revision
1500 * p_sequence_num Sequence number to identify the comparison
1501 * results for a specific record in
1502 * ICX_PO_REVISIONS_TEMP table.
1503 *
1504 * NOTES
1505 *
1506 * HISTORY
1507 * 01-SEP-1997 Rami Haddad Created
1508 * 05-SEP-1997 Matt Denton Debugged
1509 * 20-NOV-1997 Rami Haddad Changed cursor to select latest
1510 * revision of shipments for the line,
1511 * given its revision number,
1512 * p_revision_num.
1513 ********************************************************************/
1514 PROCEDURE compare_line_locs(
1515 p_line_id IN NUMBER,
1516 p_release_id IN NUMBER,
1517 p_revision_num IN NUMBER,
1518 p_comparison_flag IN VARCHAR2,
1519 p_sequence_num IN NUMBER
1520 ) AS
1521
1522 CURSOR current_locations(
1523 current_line_id NUMBER,
1524 current_release_id NUMBER
1525 ) IS
1526 SELECT *
1527 FROM po_line_locations_archive plla1
1528 WHERE
1529 po_line_id = current_line_id
1530 AND NVL( po_release_id, -99 ) = current_release_id
1531 AND revision_num = (
1532 SELECT MAX( revision_num )
1533 FROM po_line_locations_archive plla2
1534 WHERE
1535 revision_num <= p_revision_num
1536 AND plla2.line_location_id = plla1.line_location_id
1537 );
1538
1539 v_loc_from po_line_locations_archive%ROWTYPE;
1540 v_loc_to po_line_locations_archive%ROWTYPE;
1541
1542 v_previous_revision_num NUMBER;
1543 v_progress VARCHAR2(3);
1544
1545 BEGIN
1546
1547 v_progress := '390';
1548 IF p_revision_num <= 0 THEN
1549 RETURN;
1550 END IF;
1551
1552 IF p_comparison_flag = 'ORIGINAL' THEN
1553 SELECT MIN( revision_num )
1554 INTO v_previous_revision_num
1555 FROM po_lines_archive
1556 WHERE po_line_id = p_line_id;
1557 ELSE
1558 v_previous_revision_num := p_revision_num - 1;
1559 END IF;
1560
1561 v_progress := '400';
1562 FOR v_loc_to IN current_locations(
1563 p_line_id,
1564 NVL( p_release_id, -99 )
1565 ) loop
1566
1567 /* Compare the lines only if the current line has a revision
1568 * greater than the calculated revision number of previous
1569 * line.
1570 */
1571 IF v_loc_to.revision_num > v_previous_revision_num THEN
1572
1573 BEGIN
1574
1575 v_progress := '410';
1576 SELECT *
1577 INTO v_loc_from
1578 FROM po_line_locations_archive
1579 WHERE
1580 line_location_id = v_loc_to.line_location_id
1581 AND revision_num =
1582 (
1583 SELECT MAX(revision_num)
1584 FROM po_line_locations_archive
1585 WHERE
1586 revision_num <=
1587 v_previous_revision_num
1588 AND line_location_id =
1589 v_loc_to.line_location_id
1590 );
1591
1592 EXCEPTION
1593 WHEN no_data_found THEN
1594 v_loc_from := NULL;
1595
1596 END;
1597
1598 v_progress := '420';
1599 po_compare_revisions.compare_locations(
1600 v_loc_from,
1601 v_loc_to,
1602 p_sequence_num );
1603 END IF;
1604
1605 END loop;
1606
1607 EXCEPTION
1608 WHEN others THEN
1609 PO_MESSAGE_S.SQL_ERROR(
1610 'PO_REVISION_DIFFERENCES.COMPARE_LINE_LOCS',
1611 v_progress,
1612 sqlcode );
1613 RAISE;
1614
1615 END compare_line_locs;
1616
1617
1618
1619 /*********************************************************************
1620 * NAME
1621 * compare_line_dists
1622 *
1623 * PURPOSE
1624 * Compare a PO line distributions with the previous or the original
1625 * revision.
1626 *
1627 * ARGUMENTS
1628 * p_line_id Unique identified for the current PO line in
1629 * PO_LINES_ARCHIVE_ALL table
1630 * p_release_id Unique identified for the current PO release
1631 * in PO_RELEASES_ARCHIVE_ALL table
1632 * p_revision_num Current PO line revision number
1633 * p_comparison_flag Indicator to compare the PO with the previous
1634 * or original revision
1635 * p_sequence_num Sequence number to identify the comparison
1636 * results for a specific record in
1637 * ICX_PO_REVISIONS_TEMP table.
1638 *
1639 * NOTES
1640 *
1641 * HISTORY
1645 * Changed cursor to select latest
1642 * 01-SEP-1997 Rami Haddad Created
1643 * 05-SEP-1997 Matt Denton Debugged
1644 * 20-NOV-1997 Rami Haddad Documented
1646 * revision of distributions for the
1647 * line, given its revision number,
1648 * p_revision_num.
1649 ********************************************************************/
1650 PROCEDURE compare_line_dists(
1651 p_line_id IN NUMBER,
1652 p_release_id IN NUMBER,
1653 p_revision_num IN NUMBER,
1654 p_comparison_flag IN VARCHAR2,
1655 p_sequence_num IN NUMBER
1656 ) AS
1657
1658 CURSOR current_distributions(
1659 current_line_id number,
1660 current_release_id number
1661 ) IS
1662 SELECT *
1663 FROM po_distributions_archive pda1
1664 WHERE
1665 po_line_id = current_line_id
1666 AND NVL( po_release_id, -99 ) = current_release_id
1667 AND revision_num = (
1668 SELECT MAX( revision_num )
1669 FROM po_distributions_archive pda2
1670 WHERE
1671 revision_num <= p_revision_num
1672 AND pda2.po_distribution_id = pda1.po_distribution_id
1673 );
1674
1675 v_dist_from po_distributions_archive%ROWTYPE;
1676 v_dist_to po_distributions_archive%ROWTYPE;
1677
1678 v_previous_revision_num NUMBER;
1679 v_progress VARCHAR2(3);
1680
1681 BEGIN
1682
1683 v_progress := '430';
1684 IF p_revision_num <= 0 THEN
1685 RETURN;
1686 END IF;
1687
1688 IF p_comparison_flag = 'ORIGINAL' THEN
1689 SELECT MIN( revision_num )
1690 INTO v_previous_revision_num
1691 FROM po_lines_archive
1692 WHERE po_line_id = p_line_id;
1693 ELSE
1694 v_previous_revision_num := p_revision_num - 1;
1695 END IF;
1696
1697 FOR v_dist_to in current_distributions(
1698 p_line_id,
1699 NVL( p_release_id, -99 )
1700 ) LOOP
1701
1702 /* Compare the lines only if the current line has a revision
1703 * greater than the calculated revision number of previous
1704 * line.
1705 */
1706 IF v_dist_to.revision_num > v_previous_revision_num
1707 THEN
1708
1709 BEGIN
1710
1711 v_progress := '440';
1712 SELECT *
1713 INTO v_dist_from
1714 FROM po_distributions_archive
1715 WHERE
1716 po_distribution_id =
1717 v_dist_to.po_distribution_id
1718 AND revision_num = (
1719 SELECT MAX( revision_num )
1720 FROM po_distributions_archive
1721 WHERE revision_num <=
1722 v_previous_revision_num
1723 AND po_distribution_id =
1724 v_dist_to.po_distribution_id
1725 );
1726
1727 EXCEPTION
1728 WHEN no_data_found THEN
1729 v_dist_from := NULL;
1730
1731 END;
1732
1733 v_progress := '450';
1734 po_compare_revisions.compare_distributions(
1735 v_dist_from,
1736 v_dist_to,
1737 p_sequence_num );
1738 END IF;
1739
1740 END LOOP;
1741
1742 EXCEPTION
1743 WHEN others THEN
1744 PO_MESSAGE_S.SQL_ERROR(
1745 'PO_REVISION_DIFFERENCES.COMPARE_PO_TO_ALL',
1746 v_progress,
1747 sqlcode );
1748 RAISE;
1749
1750 END compare_line_dists;
1751
1752 END po_revision_differences;
[Home] [Help]