[Home] [Help]
PACKAGE BODY: APPS.PO_COMPARE_REVISIONS
Source
1 PACKAGE BODY po_compare_revisions AS
2 /* $Header: POXPOCMB.pls 115.18 2002/11/23 02:50:02 sbull ship $ */
3
4 /*********************************************************************
5 * NAME
6 * purge
7 *
8 * PURPOSE
9 * Delete records from the temp table, ICX_PO_REVISIONS_TEMP, where
10 * all the records for differences are stored.
11 *
12 * ARGUMENTS
13 * p_date Purges all records that are older than this date. The
14 * date defaults to two hours back from the current date.
15 *
16 * NOTES
17 * You need to set-up a concurrent program in Oracle Applications to
18 * call this program in a specific frequency.
19 *
20 * HISTORY
21 * 10-SEP-97 Rami Haddad Created
22 * 20-OCT-97 Winston Lang Added errbuf and retcode parameters.
23 ********************************************************************/
24 PROCEDURE purge(
25 errbuf OUT NOCOPY VARCHAR2,
26 retcode OUT NOCOPY NUMBER,
27 p_date IN VARCHAR2
28 ) AS
29 v_progress VARCHAR2(3);
30
31 BEGIN
32
33 retcode := 0;
34 errbuf := '';
35
36 v_progress := '010';
37
38 DELETE icx_po_revisions_temp
39 WHERE creation_date < NVL( to_date(p_date, 'YYYY/MM/DD HH24:MI:SS'),SYSDATE - 1/12 );
40
41 COMMIT;
42
43 EXCEPTION
44 WHEN others THEN
45 PO_MESSAGE_S.SQL_ERROR(
46 'PO_COMPARE_REVISIONS.PURGE',
47 v_progress,
48 sqlcode );
49 RAISE;
50
51 END purge;
52
53
54
55 /*********************************************************************
56 * NAME
57 * get_un_number
58 *
59 * PURPOSE
60 * Resolves the UN number.
61 *
62 * ARGUMENTS
63 * p_un_number_id Unique identifier for UN number in
64 * PO_UN_NUMBERS table.
65 *
66 * NOTES
67 * Return NULL if an error occurs.
68 *
69 * HISTORY
70 * 08-AUG-97 Nilo Paredes Created
71 * 22-SEP-97 Rami Haddad Return NULL if no values found.
72 ********************************************************************/
73 FUNCTION get_un_number( p_un_number_id IN NUMBER )
74 RETURN VARCHAR2 AS
75
76 v_un_number po_un_numbers.un_number%TYPE;
77 v_progress VARCHAR2(3);
78
79 BEGIN
80
81 v_progress := '020';
82
83 SELECT un_number
84 INTO v_un_number
85 FROM po_un_numbers
86 WHERE un_number_id = p_un_number_id;
87
88 RETURN v_un_number;
89
90 EXCEPTION
91 WHEN no_data_found THEN
92 RETURN NULL;
93 WHEN others THEN
94 PO_MESSAGE_S.SQL_ERROR(
95 'PO_COMPARE_REVISIONS.GET_UN_NUMBER',
96 v_progress,
97 sqlcode );
98 RAISE;
99
100 END get_un_number;
101
102
103
104 /*********************************************************************
105 * NAME
106 * get_item_number
107 *
108 * PURPOSE
109 * Resolves the item number.
110 *
111 * ARGUMENTS
112 * p_item_id Unique identifier for item number in
113 * MTL_SYSTEM_ITEMS_KFV view.
114 *
115 * NOTES
116 * Return NULL if an error occurs.
117 *
118 * HISTORY
119 * 08-AUG-97 Nilo Paredes Created
120 * 22-SEP-97 Rami Haddad Return NULL if no values found.
121 * Obtain item number by calling
122 * ICX_UTIL.ITEM_FLEX_SEG function.
123 ********************************************************************/
124 FUNCTION get_item_number(p_item_id IN NUMBER)
125 RETURN VARCHAR2 AS
126
127 v_item_num varchar2( 4000 );
128 v_progress varchar2(3);
129
130 BEGIN
131
132 v_progress := '030';
133
134 SELECT icx_util.item_flex_seg( msi.rowid )
135 INTO v_item_num
136 FROM
137 mtl_system_items msi,
138 financials_system_parameters fsp
139 WHERE
140 p_item_id = msi.inventory_item_id (+)
141 AND fsp.inventory_organization_id = NVL(
142 msi.organization_id,
143 fsp.inventory_organization_id );
144
145 RETURN v_item_num;
146
147 EXCEPTION
148 WHEN no_data_found THEN
149 RETURN NULL;
150 WHEN others THEN
151 PO_MESSAGE_S.SQL_ERROR(
152 'PO_COMPARE_REVISIONS.GET_ITEM_NUMBER',
153 v_progress,
154 sqlcode );
155 RAISE;
156
157 END get_item_number;
158
159
160
161 /*********************************************************************
162 * NAME
163 * get_hazard_class
164 *
165 * PURPOSE
166 * Resolves the hazard class.
167 *
168 * ARGUMENTS
169 * p_hazard_class_id Unique identifier for hazard class in
170 * PO_HAZARD_CLASSES table.
171 *
172 * NOTES
173 * Return NULL if an error occurs.
174 *
175 * HISTORY
176 * 08-AUG-97 Nilo Paredes Created
177 * 22-SEP-97 Rami Haddad Return NULL if no values found.
178 ********************************************************************/
179 FUNCTION get_hazard_class( p_hazard_class_id IN NUMBER )
180 RETURN VARCHAR2 AS
181
182 v_hazard_class po_hazard_classes.hazard_class%TYPE;
183 v_progress varchar2(3);
184
185 BEGIN
186
187 v_progress := '040';
188
189 SELECT hazard_class
190 INTO v_hazard_class
191 FROM po_hazard_classes
192 WHERE hazard_class_id = p_hazard_class_id;
193
194 RETURN v_hazard_class;
195
196 EXCEPTION
197 WHEN no_data_found THEN
198 RETURN NULL;
199 WHEN others THEN
200 PO_MESSAGE_S.SQL_ERROR(
201 'PO_COMPARE_REVISIONS.GET_HAZARD_CLASS',
202 v_progress,
203 sqlcode );
204 RAISE;
205
206 END get_hazard_class;
207
208
209
210 /*********************************************************************
211 * NAME
212 * get_ap_terms
213 *
214 * PURPOSE
215 * Resolves the payment terms.
216 *
217 * ARGUMENTS
218 * p_term_id Unique identifier for hazard class in
219 * PO_HAZARD_CLASSES table.
220 *
221 * NOTES
222 * Return NULL if no matching records were found.
223 *
224 * HISTORY
225 * 08-AUG-97 Nilo Paredes Created
226 * 22-SEP-97 Rami Haddad Return NULL if no values found.
227 ********************************************************************/
228 FUNCTION get_ap_terms( p_term_id IN NUMBER )
229 RETURN VARCHAR2 AS
230
231 v_ap_terms ap_terms.description%TYPE;
232 v_progress varchar2(3);
233
234 BEGIN
235
236 v_progress := '060';
237
238 SELECT description
239 INTO v_ap_terms
240 FROM ap_terms
241 WHERE term_id = p_term_id;
242
243 RETURN v_ap_terms;
244
245 EXCEPTION
246 WHEN no_data_found THEN
247 RETURN NULL;
248 WHEN others THEN
249 PO_MESSAGE_S.SQL_ERROR(
250 'PO_COMPARE_REVISIONS.GET_AP_TERMS',
251 v_progress,
252 sqlcode );
253 RAISE;
254
255 END get_ap_terms;
256
257
258
259 /*********************************************************************
260 * NAME
261 * get_buyer
262 *
263 * PURPOSE
264 * Resolves the buyer name.
265 *
266 * ARGUMENTS
267 * p_agent_id Unique identifier for buyer in PER_PEOPLE_F table.
268 *
269 * NOTES
270 * Return NULL if no matching records were found.
271 *
272 * HISTORY
273 * 08-AUG-97 Nilo Paredes Created
274 * 22-SEP-97 Rami Haddad Return NULL if no values found.
275 ********************************************************************/
276 FUNCTION get_buyer( p_agent_id IN NUMBER )
277 RETURN VARCHAR2 AS
278
279 v_full_name per_people_f.full_name%TYPE;
280 v_progress varchar2(3);
281
282 BEGIN
283
284 v_progress := '070';
285
286 --Bug 1915684 Added distinct to retrieve name of buyer
287 -- to avoid multiple rows
288 -- Bug 2111528 Added condition to get buyers with current effective
289 -- date
290 SELECT distinct full_name
291 INTO v_full_name
292 FROM per_people_f
293 WHERE person_id = p_agent_id
294 and TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
295
296 RETURN v_full_name;
297
298 EXCEPTION
299 WHEN no_data_found THEN
300 RETURN NULL;
301 WHEN others THEN
302 PO_MESSAGE_S.SQL_ERROR(
303 'PO_COMPARE_REVISIONS.GET_BUYER',
304 v_progress,
305 sqlcode );
306 RAISE;
307
308 END get_buyer;
309
310
311
312 /*********************************************************************
313 * NAME
314 * get_vendor_contact
315 *
316 * PURPOSE
317 * Resolves the supplier contact.
318 *
319 * ARGUMENTS
320 * p_vendor_contact_id Unique identifier for vendor in
321 * PO_VENDOR_CONTACTS table.
322 *
323 * NOTES
324 * Return NULL if no matching records were found.
325 *
326 * HISTORY
327 * 08-AUG-97 Nilo Paredes Created
328 * 22-SEP-97 Rami Haddad Return NULL if no values found.
329 * Obtain name from PO_VENDOR_CONTACTS
330 * table.
331 ********************************************************************/
332 FUNCTION get_vendor_contact( p_vendor_contact_id IN NUMBER )
333 RETURN VARCHAR2 AS
334
335 v_full_name varchar2( 40 );
336 v_progress varchar2(3);
337
338 BEGIN
339
340 v_progress := '080';
341
342 SELECT DECODE(last_name, NULL, NULL, last_name || ',' || first_name)
343 INTO v_full_name
344 FROM po_vendor_contacts
345 WHERE vendor_contact_id = p_vendor_contact_id;
346
347 RETURN v_full_name;
348
349 EXCEPTION
350 WHEN no_data_found THEN
351 RETURN NULL;
352 WHEN others THEN
353 PO_MESSAGE_S.SQL_ERROR(
354 'PO_COMPARE_REVISIONS.GET_VENDOR_CONTACT',
355 v_progress,
356 sqlcode );
357 RAISE;
358
359 END get_vendor_contact;
360
361
362
363 /*********************************************************************
364 * NAME
365 * get_location
366 *
367 * PURPOSE
368 * Resolves the location code
369 *
370 * ARGUMENTS
371 * p_location_id Unique identifier for the location in
372 * HR_LOCATIONS table.
373 *
374 * NOTES
375 * Return NULL if no matching records were found.
376 *
377 * HISTORY
378 * 08-AUG-97 Nilo Paredes Created
379 * 22-SEP-97 Rami Haddad Return NULL if no values found.
380 ********************************************************************/
381 FUNCTION get_location( p_location_id IN NUMBER )
382 RETURN VARCHAR2 AS
383
384 v_location_code hr_locations.location_code%TYPE;
385 v_progress varchar2(3);
386
387 BEGIN
388
389 v_progress := '090';
390
391 SELECT location_code
392 INTO v_location_code
393 FROM hr_locations
394 WHERE location_id = p_location_id;
395
396 RETURN v_location_code;
397
398 EXCEPTION
399 WHEN no_data_found THEN
400 RETURN NULL;
401
402 WHEN others THEN
403 PO_MESSAGE_S.SQL_ERROR(
404 'PO_COMPARE_REVISIONS.GET_LOCATION',
405 v_progress,
406 sqlcode );
407 RAISE;
408
409 END get_location;
410
411
412
413 /*********************************************************************
414 * NAME
415 * get_source_quotation_header
416 *
417 * PURPOSE
418 * Resolves the source quotation PO number.
419 *
420 * ARGUMENTS
421 * p_header_id Unique identifier for PO in PO_HEADERS table.
422 *
423 * NOTES
424 * Return NULL if no matching records were found.
425 *
426 * HISTORY
427 * 23-SEP-97 Rami Haddad Created
428 ********************************************************************/
429 FUNCTION get_source_quotation_header( p_header_id in number )
430 RETURN VARCHAR2 AS
431
432 v_po_num po_headers.segment1%TYPE;
433 v_progress varchar2(3);
434
435 BEGIN
436
437 v_progress := '100';
438
439 SELECT segment1
440 INTO v_po_num
441 FROM po_headers
442 WHERE po_header_id = p_header_id;
443
444 RETURN v_po_num;
445
446 EXCEPTION
447 WHEN no_data_found THEN
448 RETURN NULL;
449 WHEN others THEN
450 PO_MESSAGE_S.SQL_ERROR(
451 'PO_COMPARE_REVISIONS.GET_SOURCE_QUOTATION_HEADER',
452 v_progress,
453 sqlcode );
454 RAISE;
455
456 END get_source_quotation_header;
457
458
459
460 /*********************************************************************
461 * NAME
462 * get_source_quotation_line
463 *
464 * PURPOSE
465 * Resolves the source quotation PO line number.
466 *
467 * ARGUMENTS
468 * p_line_id Unique identifier for PO line in PO_LINES table.
469 *
470 * NOTES
471 * Return NULL if no matching records were found.
472 *
473 * HISTORY
474 * 23-SEP-97 Rami Haddad Created
475 ********************************************************************/
476 FUNCTION get_source_quotation_line( p_line_id in number )
477 RETURN VARCHAR2 AS
478
479 v_line_num po_lines.line_num%TYPE;
480 v_progress varchar2(3);
481
482 BEGIN
483
484 v_progress := '110';
485
486 SELECT line_num
487 INTO v_line_num
488 FROM po_lines
489 WHERE po_line_id = p_line_id;
490
491 RETURN v_line_num;
492
493 EXCEPTION
494 WHEN no_data_found THEN
495 RETURN NULL;
496 WHEN others THEN
497 PO_MESSAGE_S.SQL_ERROR(
498 'PO_COMPARE_REVISIONS.GET_SOURCE_QUOTATION_LINE',
499 v_progress,
500 sqlcode );
501 RAISE;
502
503 END get_source_quotation_line;
504
505
506
507 /*********************************************************************
508 * NAME
509 * get_po_lookup
510 *
511 * PURPOSE
512 *
513 *
514 * ARGUMENTS
515 *
516 *
517 * NOTES
518 *
519 *
520 * HISTORY
521 * 31-OCT-97 Rami Haddad Created
522 ********************************************************************/
523 FUNCTION get_po_lookup(
524 p_lookup_type IN VARCHAR2,
525 p_lookup_code IN VARCHAR2
526 ) RETURN VARCHAR2 AS
527
528 v_description po_lookup_codes.description%TYPE;
529 v_progress varchar2(3);
530
531 BEGIN
532
533 v_progress := '120';
534
535 SELECT description
536 INTO v_description
537 FROM po_lookup_codes
538 WHERE
539 lookup_type = p_lookup_type
540 AND lookup_code = p_lookup_code;
541
542 RETURN v_description;
543
544 EXCEPTION
545 WHEN no_data_found THEN
546 RETURN NULL;
547 WHEN others THEN
548 PO_MESSAGE_S.SQL_ERROR(
549 'PO_COMPARE_REVISIONS.GET_PO_LOOKUP',
550 v_progress,
551 sqlcode );
552 RAISE;
553
554 END get_po_lookup;
555
556 /*********************************************************************
557 * NAME
558 * get_vendor_site
559 *
560 * PURPOSE
561 * Resolves the vendor site
562 *
563 * ARGUMENTS
564 * p_vendor_site_id Unique identifier for the location in
565 * PO_VENDOR_SITES table.
566 *
567 * NOTES
571 * 09-AUG-01 Amitabh Mitra created
568 * Return NULL if no matching records were found.
569 *
570 * HISTORY
572 ********************************************************************/
573 FUNCTION get_vendor_site( p_vendor_site_id IN NUMBER )
574 RETURN VARCHAR2 AS
575
576 v_site_code varchar2(20);
577 v_progress varchar2(3);
578
579 BEGIN
580
581 v_progress := '140';
582
583 SELECT vendor_site_code
584 INTO v_site_code
585 FROM po_vendor_sites_all
586 WHERE vendor_site_id = p_vendor_site_id;
587
588 RETURN v_site_code;
589
590 EXCEPTION
591 WHEN no_data_found THEN
592 RETURN NULL;
593
594 WHEN others THEN
595 PO_MESSAGE_S.SQL_ERROR(
596 'PO_COMPARE_REVISIONS.GET_VENDOR_SITE',
597 v_progress,
598 sqlcode );
599 RAISE;
600
601 END get_vendor_site;
602
603
604 /*********************************************************************
605 * NAME
606 * insert_changes
607 *
608 * PURPOSE
609 * Insert the comparison result into the temp table.
610 *
611 * ARGUMENTS
612 * p_line_seq Sequence number to identify the comparison
613 * results for a specific record.
614 * p_header_id Unique identifier for PO.
615 * p_release_id Unique identifier for PO release.
616 * p_line_id Unique identifier for PO line.
617 * p_location_id Unique identifier for PO line location.
618 * p_distribution_id Unique identifier for PO distribution.
619 * p_item_id Unique identified for line item.
620 * p_po_num PO number.
621 * p_line_num PO line number.
622 * p_location_num PO line location number.
623 * p_distribution_num PO distribution number.
624 * p_level_altered Level altered. Possible values are:
625 *
626 * Code User-friendly name
627 * ---- ------------------
628 * ICX_DISTRIBUTION Distribution
629 * ICX_HEADER Header
630 * ICX_LINE Line
631 * ICX_SHIPMENT Shipment
632 *
633 * p_field_altered Field altered. Possible values are:
634 *
635 * Code User-friendly name
636 * ---- ------------------
637 * ICX_ACCEPTANCE_DUE_DATE Acceptance Due Date
638 * ICX_ACCEPTANCE_REQUIRED Acceptance Required
639 * ICX_AMOUNT Amount
640 * ICX_AMOUNT_AGREED Amount Agreed
641 * ICX_AMOUNT_DUE_DATE Amount Due Date
642 * ICX_AMOUNT_LIMIT Amount Limit
643 * ICX_BILL_TO Bill To
644 * ICX_BUYER Buyer
645 * ICX_CANCELLED Cancelled
646 * ICX_CHARGE_ACCT Charge Account
647 * ICX_CLOSED_CODE Closed
648 * ICX_CONFIRMED Confirm
649 * ICX_CONTRACT_NUMBER Contract Number
650 * ICX_EFFECTIVE_DATE Effective Date
651 * ICX_ERROR Error
652 * ICX_EXPIRATION_DATE Expiration Date
653 * ICX_FOB FOB
654 * ICX_FREIGHT_TERMS Freight Terms
655 * ICX_HAZARD_CLASS Hazard Class
656 * ICX_ITEM Item
657 * ICX_ITEM_DESCRIPTION Item Description
658 * ICX_ITEM_REVISION Item Revision
659 * ICX_LAST_ACCEPT_DATE Last Acceptance Date
660 * ICX_LINE_NUMBER Line Number
661 * ICX_NEED_BY_DATE Need By Date
662 * ICX_NEW New
663 * ICX_NOTE_TO_VENDOR Note To Vendor
664 * ICX_PAYMENT_TERMS Payment Terms
665 * ICX_PRICE_BREAK Price Break
666 * ICX_PRICE_TYPE Price Type
667 * ICX_PROMISED_DATE Promised Date
668 * ICX_QUANTITY Quantity
669 * ICX_QUANTITY_AGREED Quantity Agreed
670 * ICX_RELEASE_DATE Released Date
671 * ICX_RELEASE_NUMBER Release Number
672 * ICX_REQUESTOR Requestor
673 * ICX_SHIP_NUM Shipment Number
674 * ICX_SHIP_TO Ship To
675 * ICX_SHIP_VIA Ship Via
676 * ICX_SOURCE_QT_HEADER Source Quotation Header
677 * ICX_SOURCE_QT_LINE Source Quotation Line
678 * ICX_SUPPLIER_CONTACT Supplier Contact
679 * ICX_SUPPLIER_ITEM_NUM Supplier Item Number
680 * ICX_TAXABLE_FLAG Taxable
681 * ICX_UNIT_PRICE Unit Price
682 * ICX_UN_NUMBER UN Number
683 * ICX_UOM UOM
684 *
685 * p_changes_from Previous value of field altered.
686 * p_changes_to New value of field altered.
687 *
688 * NOTES
689 * Stamps every line with the current system date. Use that value
690 * when purging the table, to remove 2-hours old records for example.
691 *
692 * Replace IDs that are NULL with -99, to do the sorting correctly.
693 * When sorting in an ascending order, NULL values are at the last,
694 * while, to sort these records correctly, they should be the first.
695 *
696 * HISTORY
697 * 08-AUG-97 Nilo Paredes Created
698 * 22-SEP-97 Rami Haddad Removed prompts look-up in AK.
699 * Replace NULL with -99 for sorting.
700 ********************************************************************/
701 PROCEDURE insert_changes(
702 p_line_seq IN NUMBER,
703 p_header_id IN NUMBER,
704 p_release_id IN NUMBER,
705 p_line_id IN NUMBER,
706 p_location_id IN NUMBER,
707 p_distribution_id IN NUMBER,
708 p_item_id IN NUMBER,
709 p_po_num IN VARCHAR2,
710 p_revision_num IN NUMBER,
711 p_line_num IN NUMBER,
712 p_location_num IN NUMBER,
713 p_distribution_num IN NUMBER,
714 p_level_altered IN VARCHAR2,
715 p_field_altered IN VARCHAR2,
716 p_changes_from IN VARCHAR2,
720 v_progress VARCHAR2(3);
717 p_changes_to IN VARCHAR2
718 ) AS
719
721
722 BEGIN
723
724 v_progress := '900';
725
726 INSERT INTO
727 icx_po_revisions_temp(
728 line_seq,
729 creation_date,
730 header_id,
731 release_id,
732 line_id,
733 location_id,
734 distribution_id,
735 item_id,
736 po_num,
737 revision_num,
738 line_num,
739 location_num,
740 distribution_num,
741 level_altered,
742 field_altered,
743 changes_from,
744 changes_to
745 )
746 VALUES
747 (
748 p_line_seq,
749 SYSDATE,
750 p_header_id,
751 p_release_id,
752 p_line_id,
753 p_location_id,
754 p_distribution_id,
755 p_item_id,
756 p_po_num,
757 p_revision_num,
758 p_line_num,
759 p_location_num,
760 p_distribution_num,
761 p_level_altered,
762 p_field_altered,
763 p_changes_from,
764 p_changes_to
765 );
766
767 COMMIT;
768
769 EXCEPTION
770 WHEN others THEN
771 PO_MESSAGE_S.SQL_ERROR(
772 'PO_COMPARE_REVISIONS.INSERT_CHANGES',
773 v_progress,
774 sqlcode );
775 RAISE;
776
777 END insert_changes;
778
779
780
781 /*********************************************************************
782 * NAME
783 * verify_no_differences
784 *
785 * PURPOSE
786 * Insert a line in the ICX_PO_REVISIONS_TEMP table indicating that
787 * there are no differences between the compared records.
788 *
789 * ARGUMENTS
790 * p_line_seq Sequence number to identify the comparison
791 * results for a specific record.
792 *
793 * NOTES
794 * Refer to bug#549414 for more details.
795 *
796 * This is used specifically to handle AK functionality. AK is
797 * expecting a row in table with the PK. The initial table in this
798 * case is actually a procedure, so AK fails. The procedure checks.
799 * If there are no differences, insert a dummy row in the table that
800 * say something like 'No differences.'
801 *
802 * HISTORY
803 * 31-OCT-97 Rami Haddad Created
804 ********************************************************************/
805 PROCEDURE verify_no_differences( p_line_seq IN NUMBER ) AS
806
807 records_exist number;
808 v_progress varchar2(3);
809
810 BEGIN
811
812 v_progress := '130';
813
814 SELECT COUNT(*)
815 INTO records_exist
816 FROM icx_po_revisions_temp
817 WHERE line_seq = p_line_seq;
818
819 IF records_exist = 0 THEN
820 insert_changes(
821 p_line_seq,
822 -99,
823 NULL,
824 NULL,
825 NULL,
826 NULL,
827 NULL,
828 NULL,
829 0, -- -99
830 NULL,
831 NULL,
832 NULL,
833 'ICX_HEADER',
834 --fnd_message.get_String('PO', 'POS_NO_DIFFERENCE'),
835 'ICX_NO_DIFFERENCE',
836 NULL,
837 NULL
838 );
839 END IF;
840
841 EXCEPTION
842 WHEN others THEN
843 PO_MESSAGE_S.SQL_ERROR(
844 'PO_COMPARE_REVISIONS.VERIFY_NO_DIFFERENCES',
845 v_progress,
846 sqlcode );
847 RAISE;
848
849 END verify_no_differences;
850
851
852
853 /*********************************************************************
854 * NAME
855 * compare_headers
856 *
857 * PURPOSE
858 * Accepts two records of the same PO with different revisions,
859 * compare the data in both POs, and stores the differences in a
860 * temporary table.
861 *
862 * ARGUMENTS
863 * p_po_from Old version of the PO.
864 * p_po_to New version of the PO.
865 * p_sequence Sequence number to use in the temp table to identify
866 * the records for delta.
867 *
868 * NOTES
869 * The comparison is not done on all fields, but only the ones than
870 * cause a revision change, according to Oracle Purchasing Reference
871 * Manual.
872 *
873 * The fields that can be changed on PO header, and cause a revision
874 * number increase are:
875 *
876 * Cancel Flag
877 * Buyer
878 * Vendor contact
879 * Confirming flag
880 * Ship-to location
881 * Bill-to location
882 * Payment terms
883 * Amount
884 * Ship via
885 * FOB
886 * Freignt terms
887 * Note to vendor
888 * Acceptance required
889 * Acceptance due date
890 * Amount Limit
891 * Start Date
892 * End Date
893 *
894 * HISTORY
895 * 08-AUG-97 Nilo Paredes Created.
896 * 22-SEP-97 Rami Haddad Added buyer comparison.
897 ********************************************************************/
898 PROCEDURE compare_headers(
899 p_po_from in po_headers_archive%ROWTYPE,
900 p_po_to in po_headers_archive%ROWTYPE,
901 p_sequence in number
902 ) AS
903
904 /*
905 * Constant variables to pass for insert_changes
906 */
907 c_level_altered icx_po_revisions_temp.level_altered%TYPE
911 c_revision_num NUMBER;
908 := 'ICX_HEADER';
909 c_po_header_id NUMBER;
910 c_po_num po_headers_archive.segment1%TYPE;
912 c_release_id NUMBER := NULL;
913 c_line_id NUMBER := NULL;
914 c_line_num NUMBER := NULL;
915 c_location_id NUMBER := NULL;
916 c_location_num NUMBER := NULL;
917 c_distribution_id NUMBER := NULL;
918 c_distribution_num NUMBER := NULL;
919 c_item_id NUMBER := NULL;
920
921 v_amount_from NUMBER;
922 v_amount_to NUMBER;
923 v_progress VARCHAR2(3);
924
925 BEGIN
926
927 /*
928 * At least the latest revision should exist.
929 */
930 IF p_po_to.po_header_id IS NULL THEN
931 RETURN;
932 END IF;
933
934 /*
935 * Set values for all constants
936 */
937 c_po_header_id := p_po_to.po_header_id;
938 c_po_num := p_po_to.segment1;
939 c_revision_num := p_po_to.revision_num;
940
941 /*
942 * If the old record does not exist, then this is an error.
943 */
944 v_progress := '910';
945
946 IF p_po_from.po_header_id IS NULL THEN
947 insert_changes(
948 p_sequence,
949 c_po_header_id,
950 c_release_id,
951 c_line_id,
952 c_location_id,
953 c_distribution_id,
954 c_item_id,
955 c_po_num,
956 c_revision_num,
957 c_line_num,
958 c_location_num,
959 c_distribution_num,
960 c_level_altered,
961 --fnd_message.get_String('PO', 'POS_ERROR'),
962 'ICX_ERROR',
963 NULL,
964 NULL
965 );
966 RETURN;
967 END IF;
968
969 /* Are the POs the same? */
970 IF p_po_from.po_header_id <> p_po_to.po_header_id THEN
971 RETURN;
972 END IF;
973
974 /* Do not compare POs of the same revision number. */
975 IF NVL( p_po_from.revision_num, -99 ) =
976 NVL( p_po_to.revision_num, -99 ) THEN
977 RETURN;
978 END IF;
979
980 v_progress := '140';
981
982 /* Check for cancelled PO. */
983 IF p_po_to.cancel_flag = 'Y' THEN
984 IF p_po_from.cancel_flag = 'Y'
985 THEN
986 RETURN;
987 ELSE
988 insert_changes(
989 p_sequence,
990 c_po_header_id,
991 c_release_id,
992 c_line_id,
993 c_location_id,
994 c_distribution_id,
995 c_item_id,
996 c_po_num,
997 c_revision_num,
998 c_line_num,
999 c_location_num,
1000 c_distribution_num,
1001 c_level_altered,
1002 --fnd_message.get_string('PO', 'POS_CANCELLED'),
1003 'ICX_CANCELLED',
1004 NULL,
1005 NULL
1006 );
1007 RETURN;
1008 END IF;
1009 END IF;
1010 /*
1011 * Check for the differences
1012 */
1013
1014 v_progress := '150';
1015
1016 /* Buyer */
1017 IF p_po_from.agent_id <> p_po_to.agent_id THEN
1018 insert_changes(
1019 p_sequence,
1020 c_po_header_id,
1021 c_release_id,
1022 c_line_id,
1023 c_location_id,
1024 c_distribution_id,
1025 c_item_id,
1026 c_po_num,
1027 c_revision_num,
1028 c_line_num,
1029 c_location_num,
1030 c_distribution_num,
1031 c_level_altered,
1032 --fnd_message.get_String('PO', 'POS_BUYER'),
1033 'ICX_BUYER',
1034 get_buyer( p_po_from.agent_id ),
1035 get_buyer( p_po_to.agent_id )
1036 );
1037 END IF;
1038
1039 v_progress := '160';
1040
1041 /* Vendor contact */
1042 IF NVL( p_po_from.vendor_contact_id, -99 ) <>
1043 NVL( p_po_to.vendor_contact_id, -99 ) THEN
1044 insert_changes(
1045 p_sequence,
1046 c_po_header_id,
1047 c_release_id,
1048 c_line_id,
1049 c_location_id,
1050 c_distribution_id,
1051 c_item_id,
1052 c_po_num,
1053 c_revision_num,
1054 c_line_num,
1055 c_location_num,
1056 c_distribution_num,
1057 c_level_altered,
1058 --fnd_message.get_String('PO', 'POS_SUPPLIER_CONTACT'),
1059 'ICX_SUPPLIER_CONTACT',
1060 get_vendor_contact( p_po_from.vendor_contact_id ),
1061 get_vendor_contact( p_po_to.vendor_contact_id )
1062 );
1063 END IF;
1064
1065 v_progress := '170';
1066
1067 /* Confirming flag */
1068 IF NVL( p_po_from.confirming_order_flag, ' ' ) <>
1069 NVL( p_po_to.confirming_order_flag, ' ' ) THEN
1070 insert_changes(
1071 p_sequence,
1072 c_po_header_id,
1073 c_release_id,
1074 c_line_id,
1075 c_location_id,
1076 c_distribution_id,
1077 c_item_id,
1078 c_po_num,
1079 c_revision_num,
1080 c_line_num,
1081 c_location_num,
1082 c_distribution_num,
1083 c_level_altered,
1084 --fnd_message.get_String('PO', 'POS_CONFIRMED'),
1085 'ICX_CONFIRMED',
1086 p_po_from.confirming_order_flag,
1087 p_po_to.confirming_order_flag
1088 );
1089 END IF;
1090
1091 v_progress := '180';
1092
1093 /* Ship-to location */
1094 IF NVL( p_po_from.ship_to_location_id, -99 ) <>
1095 NVL( p_po_to.ship_to_location_id, -99 ) THEN
1099 c_release_id,
1096 insert_changes(
1097 p_sequence,
1098 c_po_header_id,
1100 c_line_id,
1101 c_location_id,
1102 c_distribution_id,
1103 c_item_id,
1104 c_po_num,
1105 c_revision_num,
1106 c_line_num,
1107 c_location_num,
1108 c_distribution_num,
1109 c_level_altered,
1110 --fnd_message.get_String('PO', 'POS_SHIP_TO'),
1111 'ICX_SHIP_TO',
1112 get_location( p_po_from.ship_to_location_id ),
1113 get_location( p_po_to.ship_to_location_id )
1114 );
1115 END IF;
1116
1117 v_progress := '190';
1118
1119 /* Bill-to location */
1120 IF NVL( p_po_from.bill_to_location_id, -99 ) <>
1121 NVL( p_po_to.bill_to_location_id, -99 ) THEN
1122 insert_changes(
1123 p_sequence,
1124 c_po_header_id,
1125 c_release_id,
1126 c_line_id,
1127 c_location_id,
1128 c_distribution_id,
1129 c_item_id,
1130 c_po_num,
1131 c_revision_num,
1132 c_line_num,
1133 c_location_num,
1134 c_distribution_num,
1135 c_level_altered,
1136 --fnd_message.get_String('PO', 'POS_BILL_TO'),
1137 'ICX_BILL_TO',
1138 get_location( p_po_from.bill_to_location_id ),
1139 get_location( p_po_to.bill_to_location_id )
1140 );
1141 END IF;
1142
1143 v_progress := '200';
1144
1145 /* Payment terms */
1146 IF NVL( p_po_from.terms_id, -99 ) <> NVL( p_po_to.terms_id, -99 ) THEN
1147 insert_changes(
1148 p_sequence,
1149 c_po_header_id,
1150 c_release_id,
1151 c_line_id,
1152 c_location_id,
1153 c_distribution_id,
1154 c_item_id,
1155 c_po_num,
1156 c_revision_num,
1157 c_line_num,
1158 c_location_num,
1159 c_distribution_num,
1160 c_level_altered,
1161 --fnd_message.get_String('PO', 'POS_PAYMENT_TERMS'),
1162 'ICX_PAYMENT_TERMS',
1163 get_ap_terms( p_po_from.terms_id ),
1164 get_ap_terms( p_po_to.terms_id )
1165 );
1166 END IF;
1167
1168 /* Amount */
1169 v_amount_from := po_totals_po_sv.get_po_archive_total(
1170 c_po_header_id,
1171 p_po_from.revision_num );
1172 v_amount_to := po_totals_po_sv.get_po_archive_total(
1173 c_po_header_id,
1174 p_po_to.revision_num );
1175
1176 v_progress := '210';
1177
1178 IF v_amount_from <> v_amount_to THEN
1179 insert_changes(
1180 p_sequence,
1181 c_po_header_id,
1182 c_release_id,
1183 c_line_id,
1184 c_location_id,
1185 c_distribution_id,
1186 c_item_id,
1187 c_po_num,
1188 c_revision_num,
1189 c_line_num,
1190 c_location_num,
1191 c_distribution_num,
1192 c_level_altered,
1193 --fnd_message.get_String('PO', 'POS_AMOUNT'),
1194 'ICX_AMOUNT',
1195 v_amount_from,
1196 v_amount_to
1197 );
1198 END IF;
1199
1200 v_progress := '220';
1201
1202 /* Ship via */
1203 IF NVL( p_po_from.ship_via_lookup_code, ' ' ) <>
1204 NVL( p_po_to.ship_via_lookup_code, ' ' ) THEN
1205 insert_changes(
1206 p_sequence,
1207 c_po_header_id,
1208 c_release_id,
1209 c_line_id,
1210 c_location_id,
1211 c_distribution_id,
1212 c_item_id,
1213 c_po_num,
1214 c_revision_num,
1215 c_line_num,
1216 c_location_num,
1217 c_distribution_num,
1218 c_level_altered,
1219 --fnd_message.get_String('PO', 'POS_SHIP_VIA'),
1220 'ICX_SHIP_VIA',
1221 p_po_from.ship_via_lookup_code,
1222 p_po_to.ship_via_lookup_code
1223 );
1224 END IF;
1225
1226 v_progress := '230';
1227
1228 /* FOB */
1229 IF NVL( p_po_from.fob_lookup_code, ' ' ) <>
1230 NVL( p_po_to.fob_lookup_code, ' ' ) THEN
1231 insert_changes(
1232 p_sequence,
1233 c_po_header_id,
1234 c_release_id,
1235 c_line_id,
1236 c_location_id,
1237 c_distribution_id,
1238 c_item_id,
1239 c_po_num,
1240 c_revision_num,
1241 c_line_num,
1242 c_location_num,
1243 c_distribution_num,
1244 c_level_altered,
1245 --fnd_message.get_String('PO', 'POS_FOB'),
1246 'ICX_FOB',
1247 get_po_lookup( 'FOB', p_po_from.fob_lookup_code ),
1248 get_po_lookup( 'FOB', p_po_to.fob_lookup_code )
1249 );
1250 END IF;
1251
1252 v_progress := '240';
1253
1254 /* Freignt terms */
1255 IF NVL( p_po_from.freight_terms_lookup_code, ' ' ) <>
1256 NVL( p_po_to.freight_terms_lookup_code, ' ' ) THEN
1257 insert_changes(
1258 p_sequence,
1259 c_po_header_id,
1260 c_release_id,
1261 c_line_id,
1262 c_location_id,
1263 c_distribution_id,
1264 c_item_id,
1265 c_po_num,
1266 c_revision_num,
1267 c_line_num,
1268 c_location_num,
1269 c_distribution_num,
1270 c_level_altered,
1271 --fnd_message.get_String('PO', 'POS_FREIGHT_TERMS'),
1272 'ICX_FREIGHT_TERMS',
1273 get_po_lookup(
1274 'FREIGHT TERMS',
1275 p_po_from.freight_terms_lookup_code ),
1276 get_po_lookup(
1277 'FREIGHT TERMS',
1281
1278 p_po_to.freight_terms_lookup_code )
1279 );
1280 END IF;
1282 v_progress := '250';
1283
1284 /* Note to vendor */
1285 IF NVL( p_po_from.note_to_vendor, ' ' ) <>
1286 NVL( p_po_to.note_to_vendor, ' ' ) THEN
1287 insert_changes(
1288 p_sequence,
1289 c_po_header_id,
1290 c_release_id,
1291 c_line_id,
1292 c_location_id,
1293 c_distribution_id,
1294 c_item_id,
1295 c_po_num,
1296 c_revision_num,
1297 c_line_num,
1298 c_location_num,
1299 c_distribution_num,
1300 c_level_altered,
1301 --fnd_message.get_String('PO', 'POS_NOTE_TO_VENDOR'),
1302 'ICX_NOTE_TO_VENDOR',
1303 p_po_from.note_to_vendor,
1304 p_po_to.note_to_vendor
1305 );
1306 END IF;
1307
1308 v_progress := '260';
1309
1310 /* Acceptance required */
1311 IF NVL( p_po_from.acceptance_required_flag, ' ' ) <>
1312 NVL( p_po_to.acceptance_required_flag, ' ' ) THEN
1313 insert_changes(
1314 p_sequence,
1315 c_po_header_id,
1316 c_release_id,
1317 c_line_id,
1318 c_location_id,
1319 c_distribution_id,
1320 c_item_id,
1321 c_po_num,
1322 c_revision_num,
1323 c_line_num,
1324 c_location_num,
1325 c_distribution_num,
1326 c_level_altered,
1327 --fnd_message.get_String('PO', 'POS_ACCEPTANCE_REQUIRED'),
1328 'ICX_ACCEPTANCE_REQUIRED',
1329 p_po_from.acceptance_required_flag,
1330 p_po_to.acceptance_required_flag
1331 );
1332 END IF;
1333
1334 v_progress := '270';
1335
1336 /* Acceptance due date */
1337 IF NVL( p_po_from.acceptance_due_date,
1338 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1339 NVL( p_po_to.acceptance_due_date,
1340 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1341 insert_changes(
1342 p_sequence,
1343 c_po_header_id,
1344 c_release_id,
1345 c_line_id,
1346 c_location_id,
1347 c_distribution_id,
1348 c_item_id,
1349 c_po_num,
1350 c_revision_num,
1351 c_line_num,
1352 c_location_num,
1353 c_distribution_num,
1354 c_level_altered,
1355 --fnd_message.get_String('PO', 'POS_ACCEPTANCE_DUE_DATE'),
1356 'ICX_ACCEPTANCE_DUE_DATE',
1357 to_char(p_po_from.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
1358 to_char(p_po_to.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
1359 );
1360 END IF;
1361
1362 v_progress := '280';
1363
1364 /* Amount limit */
1365 IF NVL( p_po_from.amount_limit, -99 ) <>
1366 NVL( p_po_to.amount_limit, -99 ) THEN
1367 insert_changes(
1368 p_sequence,
1369 c_po_header_id,
1370 c_release_id,
1371 c_line_id,
1372 c_location_id,
1373 c_distribution_id,
1374 c_item_id,
1375 c_po_num,
1376 c_revision_num,
1377 c_line_num,
1378 c_location_num,
1379 c_distribution_num,
1380 c_level_altered,
1381 --fnd_message.get_String('PO', 'POS_AMOUNT_LIMIT'),
1382 'ICX_AMOUNT_LIMIT',
1383 p_po_from.amount_limit,
1384 p_po_to.amount_limit
1385 );
1386 END IF;
1387
1388 v_progress := '290';
1389
1390 /* Effective date */
1391 IF NVL( p_po_from.start_date,
1392 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1393 NVL( p_po_to.start_date,
1394 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1395 insert_changes(
1396 p_sequence,
1397 c_po_header_id,
1398 c_release_id,
1399 c_line_id,
1400 c_location_id,
1401 c_distribution_id,
1402 c_item_id,
1403 c_po_num,
1404 c_revision_num,
1405 c_line_num,
1406 c_location_num,
1407 c_distribution_num,
1408 c_level_altered,
1409 --fnd_message.get_String('PO', 'POS_EFFECTIVE_DATE'),
1410 'ICX_EFFECTIVE_DATE',
1411 to_char(p_po_from.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
1412 to_char(p_po_to.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
1413 );
1414 END IF;
1415
1416 v_progress := '300';
1417
1418 /* Expiration date */
1419 IF NVL( p_po_from.end_date,
1420 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1421 NVL( p_po_to.end_date,
1422 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1423 insert_changes(
1424 p_sequence,
1425 c_po_header_id,
1426 c_release_id,
1427 c_line_id,
1428 c_location_id,
1429 c_distribution_id,
1430 c_item_id,
1431 c_po_num,
1432 c_revision_num,
1433 c_line_num,
1434 c_location_num,
1435 c_distribution_num,
1436 c_level_altered,
1437 --fnd_message.get_String('PO', 'POS_EXPIRATION_DATE'),
1438 'ICX_EXPIRATION_DATE',
1439 to_char(p_po_from.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
1440 to_char(p_po_to.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
1441 );
1442 END IF;
1443
1444 v_progress := '310';
1445
1446 /* Amount agreed */
1447 IF NVL( p_po_from.blanket_total_amount, -99 ) <>
1448 NVL( p_po_to.blanket_total_amount, -99 ) THEN
1449 insert_changes(
1450 p_sequence,
1451 c_po_header_id,
1455 c_distribution_id,
1452 c_release_id,
1453 c_line_id,
1454 c_location_id,
1456 c_item_id,
1457 c_po_num,
1458 c_revision_num,
1459 c_line_num,
1460 c_location_num,
1461 c_distribution_num,
1462 c_level_altered,
1463 --fnd_message.get_String('PO', 'POS_AMOUNT_AGREED'),
1464 'ICX_AMOUNT_AGREED',
1465 p_po_from.blanket_total_amount,
1466 p_po_to.blanket_total_amount
1467 );
1468 END IF;
1469 v_progress := '320';
1470
1471 /* Supplier Site */
1472 IF NVL( p_po_from.vendor_site_id, -99 ) <>
1473 NVL( p_po_to.vendor_site_id, -99 ) THEN
1474 insert_changes(
1475 p_sequence,
1476 c_po_header_id,
1477 c_release_id,
1478 c_line_id,
1479 c_location_id,
1480 c_distribution_id,
1481 c_item_id,
1482 c_po_num,
1483 c_revision_num,
1484 c_line_num,
1485 c_location_num,
1486 c_distribution_num,
1487 c_level_altered,
1488 'ICX_VENDOR_SITE',
1489 get_vendor_site(p_po_from.vendor_site_id),
1490 get_vendor_site(p_po_to.vendor_site_id)
1491 );
1492 END IF;
1493
1494 EXCEPTION
1495 WHEN others THEN
1496 PO_MESSAGE_S.SQL_ERROR(
1497 'PO_COMPARE_REVISIONS.COMPARE_HEADERS',
1498 v_progress,
1499 sqlcode );
1500 RAISE;
1501
1502 END compare_headers;
1503
1504
1505
1506 /*********************************************************************
1507 * NAME
1508 * compare_releases
1509 *
1510 * PURPOSE
1511 * Accepts two records of the same release with different revisions,
1512 * compare the data in both releases, and stores the differences in a
1513 * temporary table.
1514 *
1515 * ARGUMENTS
1516 * p_release_from Old version of the PO.
1517 * p_release_to New version of the PO.
1518 * p_sequence Sequence number to use in the temp table to
1519 * identify the records for delta.
1520 *
1521 * NOTES
1522 * The comparison is not done on all fields, but only the ones than
1523 * cause a revision change, according to Oracle Purchasing Reference
1524 * Manual.
1525 *
1526 * The fields that can be changed on PO header, and cause a revision
1527 * number increase are:
1528 *
1529 * Cancel Flag
1530 * Buyer
1531 * Acceptance required
1532 * Acceptance due date
1533 * Release number
1534 * Release date
1535 *
1536 * HISTORY
1537 * 08-AUG-97 Nilo Paredes Created.
1538 * 22-SEP-97 Rami Haddad Added buyer comparison.
1539 ********************************************************************/
1540 PROCEDURE compare_releases(
1541 p_release_from in po_releases_archive%ROWTYPE,
1542 p_release_to in po_releases_archive%ROWTYPE,
1543 p_sequence IN NUMBER
1544 ) AS
1545
1546 /*
1547 * Constant variables to pass for insert_changes
1548 */
1549 c_level_altered icx_po_revisions_temp.level_altered%TYPE
1550 := 'ICX_HEADER';
1551 c_po_header_id NUMBER;
1552 c_po_num po_headers_archive.segment1%TYPE;
1553 c_release_id NUMBER;
1554 c_revision_num NUMBER;
1555 c_line_id NUMBER := NULL;
1556 c_line_num NUMBER := NULL;
1557 c_location_id NUMBER := NULL;
1558 c_location_num NUMBER := NULL;
1559 c_distribution_id NUMBER := NULL;
1560 c_distribution_num NUMBER := NULL;
1561 c_item_id NUMBER := NULL;
1562 v_progress VARCHAR2(3);
1563
1564 v_po_num po_headers_archive.segment1%TYPE;
1565
1566 BEGIN
1567
1568 /*
1569 * At least the latest revision should exist.
1570 */
1571 IF p_release_to.po_header_id IS NULL THEN
1572 RETURN;
1573 END IF;
1574
1575 /*
1576 * Set values for all constants
1577 */
1578 c_po_header_id := p_release_to.po_header_id;
1579
1580 v_progress := '320';
1581
1582 SELECT segment1
1583 INTO v_po_num
1584 FROM po_headers_archive
1585 WHERE
1586 po_header_id = p_release_to.po_header_id
1587 AND latest_external_flag = 'Y';
1588
1589 c_po_num := v_po_num || '-' ||
1590 p_release_to.release_num;
1591 c_revision_num := p_release_to.revision_num;
1592 c_release_id := p_release_to.po_release_id;
1593
1594 /*
1595 * If the old record does not exist, then this is a new one.
1596 */
1597
1598 v_progress := '330';
1599
1600 IF p_release_from.po_header_id IS NULL THEN
1601 insert_changes(
1602 p_sequence,
1603 c_po_header_id,
1604 c_release_id,
1605 c_line_id,
1606 c_location_id,
1607 c_distribution_id,
1608 c_item_id,
1609 c_po_num,
1610 c_revision_num,
1611 c_line_num,
1612 c_location_num,
1613 c_distribution_num,
1614 c_level_altered,
1615 --fnd_message.get_String('PO', 'POS_NEW'),
1616 'ICX_NEW',
1617 NULL,
1618 NULL
1619 );
1620 RETURN;
1621 END IF;
1622
1623 /*
1624 * Are the releases the same?
1628 RETURN;
1625 */
1626 IF NVL( p_release_from.po_release_id, -99 ) <>
1627 NVL( p_release_to.po_release_id, -99 ) THEN
1629 END IF;
1630
1631 /*
1632 * Do not compare releases of the same revision number.
1633 */
1634
1635 IF NVL( p_release_from.revision_num, -99 ) =
1636 NVL( p_release_to.revision_num, -99 ) THEN
1637 RETURN;
1638 END IF;
1639
1640 v_progress := '340';
1641
1642 /*
1643 * Check for cancelled release.
1644 */
1645 IF p_release_to.cancel_flag = 'Y' THEN
1646 IF p_release_from.cancel_flag = 'Y'
1647 THEN
1648 RETURN;
1649 ELSE
1650 insert_changes(
1651 p_sequence,
1652 c_po_header_id,
1653 c_release_id,
1654 c_line_id,
1655 c_location_id,
1656 c_distribution_id,
1657 c_item_id,
1658 c_po_num,
1659 c_revision_num,
1660 c_line_num,
1661 c_location_num,
1662 c_distribution_num,
1663 c_level_altered,
1664 --fnd_message.get_string('PO', 'POS_CANCELLED'),
1665 'ICX_CANCELLED',
1666 NULL,
1667 NULL
1668 );
1669 RETURN;
1670 END IF;
1671 END IF;
1672
1673 v_progress := '350';
1674
1675 /* Buyer */
1676 IF p_release_from.agent_id <> p_release_to.agent_id THEN
1677 insert_changes(
1678 p_sequence,
1679 c_po_header_id,
1680 c_release_id,
1681 c_line_id,
1682 c_location_id,
1683 c_distribution_id,
1684 c_item_id,
1685 c_po_num,
1686 c_revision_num,
1687 c_line_num,
1688 c_location_num,
1689 c_distribution_num,
1690 c_level_altered,
1691 --fnd_message.get_String('PO', 'POS_BUYER'),
1692 'ICX_BUYER',
1693 get_buyer( p_release_from.agent_id ),
1694 get_buyer( p_release_to.agent_id )
1695 );
1696 END IF;
1697
1698 v_progress := '360';
1699
1700 /* Acceptance due date */
1701 IF NVL( p_release_from.acceptance_due_date,
1702 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1703 NVL( p_release_to.acceptance_due_date,
1704 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1705 insert_changes(
1706 p_sequence,
1707 c_po_header_id,
1708 c_release_id,
1709 c_line_id,
1710 c_location_id,
1711 c_distribution_id,
1712 c_item_id,
1713 c_po_num,
1714 c_revision_num,
1715 c_line_num,
1716 c_location_num,
1717 c_distribution_num,
1718 c_level_altered,
1719 --fnd_message.get_String('PO', 'POS_ACCEPTANCE_DUE_DATE'),
1720 'ICX_ACCEPTANCE_DUE_DATE',
1721 to_char(p_release_from.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
1722 to_char(p_release_to.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
1723 );
1724 END IF;
1725
1726 v_progress := '380';
1727
1728 /* Acceptance required */
1729 IF NVL( p_release_from.acceptance_required_flag, ' ' ) <>
1730 NVL( p_release_to.acceptance_required_flag, ' ' ) THEN
1731 insert_changes(
1732 p_sequence,
1733 c_po_header_id,
1734 c_release_id,
1735 c_line_id,
1736 c_location_id,
1737 c_distribution_id,
1738 c_item_id,
1739 c_po_num,
1740 c_revision_num,
1741 c_line_num,
1742 c_location_num,
1743 c_distribution_num,
1744 c_level_altered,
1745 --fnd_message.get_String('PO', 'POS_ACCEPTANCE_REQUIRED'),
1746 'ICX_ACCEPTANCE_REQUIRED',
1747 p_release_from.acceptance_required_flag,
1748 p_release_to.acceptance_required_flag
1749 );
1750 END IF;
1751
1752 v_progress := '390';
1753
1754 /* Release number */
1755 IF NVL( p_release_from.release_num, -99 ) <>
1756 NVL( p_release_to.release_num, -99 ) THEN
1757 insert_changes(
1758 p_sequence,
1759 c_po_header_id,
1760 c_release_id,
1761 c_line_id,
1762 c_location_id,
1763 c_distribution_id,
1764 c_item_id,
1765 c_revision_num,
1766 c_po_num,
1767 c_line_num,
1768 c_location_num,
1769 c_distribution_num,
1770 c_level_altered,
1771 --fnd_message.get_String('PO', 'POS_RELEASE_NUMBER'),
1772 'ICX_RELEASE_NUMBER',
1773 p_release_from.release_num,
1774 p_release_to.release_num
1775 );
1776 END IF;
1777
1778 v_progress := '400';
1779
1780 /* Release date */
1781 IF NVL( p_release_from.release_date,
1782 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1783 NVL( p_release_to.release_date,
1784 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1785 insert_changes(
1786 p_sequence,
1787 c_po_header_id,
1788 c_release_id,
1789 c_line_id,
1790 c_location_id,
1791 c_distribution_id,
1792 c_item_id,
1793 c_po_num,
1794 c_revision_num,
1795 c_line_num,
1796 c_location_num,
1797 c_distribution_num,
1798 c_level_altered,
1799 --fnd_message.get_String('PO', 'POS_RELEASE_DATE'),
1800 'ICX_RELEASE_DATE',
1801 to_char(p_release_from.release_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
1802 to_char(p_release_to.release_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
1803 );
1804 END IF;
1805
1806 EXCEPTION
1807 WHEN others THEN
1811 sqlcode );
1808 PO_MESSAGE_S.SQL_ERROR(
1809 'PO_COMPARE_REVISIONS.COMPARE_RELEASES',
1810 v_progress,
1812 RAISE;
1813
1814 END compare_releases;
1815
1816
1817
1818 /*********************************************************************
1819 * NAME
1820 * compare_lines
1821 *
1822 * PURPOSE
1823 * Accepts two records of the same lines with different revisions,
1824 * compare the data in both lines, and stores the differences in a
1825 * temporary table.
1826 *
1827 * ARGUMENTS
1828 * p_line_from Old version of the PO.
1829 * p_line_to New version of the PO.
1830 * p_sequence Sequence number to use in the temp table to identify
1831 * the records for delta.
1832 *
1833 * NOTES
1834 * The comparison is not done on all fields, but only the ones than
1835 * cause a revision change, according to Oracle Purchasing Reference
1836 * Manual.
1837 *
1838 * The fields that can be changed on PO header, and cause a revision
1839 * number increase are:
1840 *
1841 * Cancel Flag
1842 * Unit price
1843 * Line number
1844 * Item
1845 * Item revision
1846 * Item description
1847 * Quantity
1848 * UOM
1849 * Source quotation header
1850 * Source quotation line
1851 * Hazard class
1852 * Contract number
1853 * Supplier item number
1854 * Note to vendor
1855 * UN number
1856 * Price type
1857 * Quantity Agreed
1858 * Amount Agreed
1859 * Closed Code
1860 *
1861 * HISTORY
1862 * 08-AUG-97 Nilo Paredes Created.
1863 * 22-SEP-97 Rami Haddad Added comparison for buyer, source
1864 * quotation header, source quotation
1865 * line, supplier item number, quantity
1866 * agreed, and amount agreed.
1867 * 08-SEP-97 Rami Haddad Compare closed code
1868 ********************************************************************/
1869 PROCEDURE compare_lines(
1870 p_line_from in po_lines_archive%ROWTYPE,
1871 p_line_to in po_lines_archive%ROWTYPE,
1872 p_sequence IN NUMBER
1873 ) AS
1874
1875 /*
1876 * Constant variables to pass for insert_changes
1877 */
1878 c_level_altered icx_po_revisions_temp.level_altered%TYPE
1879 := 'ICX_LINE';
1880 c_po_header_id NUMBER;
1881 c_release_id NUMBER := NULL;
1882 c_po_num po_headers_archive.segment1%TYPE;
1883 c_line_id NUMBER;
1884 c_line_num NUMBER;
1885 c_revision_num NUMBER;
1886 c_location_id NUMBER := NULL;
1887 c_location_num NUMBER := NULL;
1888 c_distribution_id NUMBER := NULL;
1889 c_distribution_num NUMBER := NULL;
1890 c_item_id NUMBER := NULL;
1891
1892 v_progress VARCHAR2(3);
1893
1894 BEGIN
1895
1896 /*
1897 * At least the latest revision should exist.
1898 */
1899 IF p_line_to.po_header_id IS NULL THEN
1900 RETURN;
1901 END IF;
1902
1903 /*
1904 * Set values for all constants
1905 */
1906 c_po_header_id := p_line_to.po_header_id;
1907 c_line_id := p_line_to.po_line_id;
1908 c_revision_num := p_line_to.revision_num;
1909 c_line_num := p_line_to.line_num;
1910 c_item_id := p_line_to.item_id;
1911
1912 v_progress := '410';
1913
1914 /*
1915 * If the old record does not exist, then this is a new one.
1916 */
1917 IF p_line_from.po_header_id IS NULL THEN
1918 insert_changes(
1919 p_sequence,
1920 c_po_header_id,
1921 c_release_id,
1922 c_line_id,
1923 c_location_id,
1924 c_distribution_id,
1925 c_item_id,
1926 c_po_num,
1927 c_revision_num,
1928 c_line_num,
1929 c_location_num,
1930 c_distribution_num,
1931 c_level_altered,
1932 --fnd_message.get_String('PO', 'POS_NEW'),
1933 'ICX_NEW',
1934 NULL,
1935 NULL
1936 );
1937 RETURN;
1938 END IF;
1939
1940 /*
1941 * Are the lines the same?
1942 */
1943 IF NVL( p_line_from.po_line_id, -99 ) <>
1944 NVL( p_line_to.po_line_id, -99 ) THEN
1945 RETURN;
1946 END IF;
1947
1948 /*
1949 * Do not compare lines of the same revision number.
1950 */
1951
1952 IF NVL( p_line_from.revision_num, -99 ) =
1953 NVL( p_line_to.revision_num, -99 ) THEN
1954 RETURN;
1955 END IF;
1956
1957 v_progress := '420';
1958
1959 /*
1960 * If current line is cancelled, then check if the prior one
1961 * is cancelled as well. If it is, then there is no
1962 * change. Otherwise, the line is cancelled for the current
1963 * revision.
1964 */
1965 IF p_line_to.cancel_flag = 'Y' THEN
1966 IF p_line_from.cancel_flag ='Y'
1967 THEN
1968 RETURN;
1969 ELSE
1970 insert_changes(
1971 p_sequence,
1972 c_po_header_id,
1973 c_release_id,
1974 c_line_id,
1975 c_location_id,
1976 c_distribution_id,
1977 c_item_id,
1978 c_po_num,
1979 c_revision_num,
1980 c_line_num,
1981 c_location_num,
1982 c_distribution_num,
1983 c_level_altered,
1984 --fnd_message.get_string('PO', 'POS_CANCELLED'),
1985 'ICX_CANCELLED',
1986 NULL,
1987 NULL
1988 );
1992
1989 RETURN;
1990 END IF;
1991 END IF;
1993 /*
1994 * Line not cancelled in current PO. Compare all fields with
1995 * line in prior revision.
1996 */
1997
1998 v_progress := '430';
1999
2000 /* Unit price */
2001 IF NVL( p_line_from.unit_price, -99 ) <>
2002 NVL( p_line_to.unit_price, -99 ) THEN
2003 insert_changes(
2004 p_sequence,
2005 c_po_header_id,
2006 c_release_id,
2007 c_line_id,
2008 c_location_id,
2009 c_distribution_id,
2010 c_item_id,
2011 c_po_num,
2012 c_revision_num,
2013 c_line_num,
2014 c_location_num,
2015 c_distribution_num,
2016 c_level_altered,
2017 --fnd_message.get_String('PO', 'POS_UNIT_PRICE'),
2018 'ICX_UNIT_PRICE',
2019 p_line_from.unit_price,
2020 p_line_to.unit_price
2021 );
2022 END IF;
2023
2024 v_progress := '440';
2025
2026 /* Line number */
2027 IF NVL( p_line_from.line_num, -99 ) <>
2028 NVL( p_line_to.line_num, -99 ) THEN
2029 insert_changes(
2030 p_sequence,
2031 c_po_header_id,
2032 c_release_id,
2033 c_line_id,
2034 c_location_id,
2035 c_distribution_id,
2036 c_item_id,
2037 c_po_num,
2038 c_revision_num,
2039 c_line_num,
2040 c_location_num,
2041 c_distribution_num,
2042 c_level_altered,
2043 --fnd_message.get_String('PO', 'POS_LINE_NUMBER'),
2044 'ICX_LINE_NUMBER',
2045 p_line_from.line_num,
2046 p_line_to.line_num
2047 );
2048 END IF;
2049
2050 v_progress := '450';
2051
2052 /* Item */
2053 IF NVL( p_line_from.item_id, -99 ) <> NVL( p_line_to.item_id, -99 )
2054 THEN
2055 insert_changes(
2056 p_sequence,
2057 c_po_header_id,
2058 c_release_id,
2059 c_line_id,
2060 c_location_id,
2061 c_distribution_id,
2062 c_item_id,
2063 c_po_num,
2064 c_revision_num,
2065 c_line_num,
2066 c_location_num,
2067 c_distribution_num,
2068 c_level_altered,
2069 --fnd_message.get_String('PO', 'POS_ITEM'),
2070 'ICX_ITEM',
2071 get_item_number( p_line_from.item_id ),
2072 get_item_number( p_line_to.item_id )
2073 );
2074 END IF;
2075
2076 v_progress := '460';
2077
2078 /* Item revision */
2079 IF NVL( p_line_from.item_revision, ' ' ) <>
2080 NVL( p_line_to.item_revision, ' ' ) THEN
2081 insert_changes(
2082 p_sequence,
2083 c_po_header_id,
2084 c_release_id,
2085 c_line_id,
2086 c_location_id,
2087 c_distribution_id,
2088 c_item_id,
2089 c_po_num,
2090 c_revision_num,
2091 c_line_num,
2092 c_location_num,
2093 c_distribution_num,
2094 c_level_altered,
2095 --fnd_message.get_String('PO', 'POS_ITEM_REVISION'),
2096 'ICX_ITEM_REVISION',
2097 p_line_from.item_revision,
2098 p_line_to.item_revision
2099 );
2100 END IF;
2101
2102 v_progress := '470';
2103
2104 /* Item description */
2105 IF NVL( p_line_from.item_description, ' ' ) <>
2106 NVL( p_line_to.item_description, ' ' ) THEN
2107 insert_changes(
2108 p_sequence,
2109 c_po_header_id,
2110 c_release_id,
2111 c_line_id,
2112 c_location_id,
2113 c_distribution_id,
2114 c_item_id,
2115 c_po_num,
2116 c_revision_num,
2117 c_line_num,
2118 c_location_num,
2119 c_distribution_num,
2120 c_level_altered,
2121 --fnd_message.get_String('PO', 'POS_ITEM_DESCRIPTION'),
2122 'ICX_ITEM_DESCRIPTION',
2123 p_line_from.item_description,
2124 p_line_to.item_description
2125 );
2126 END IF;
2127
2128 v_progress := '480';
2129
2130 /* Quantity */
2131 IF NVL( p_line_from.quantity, -99 ) <>
2132 NVL( p_line_to.quantity, -99 ) THEN
2133 insert_changes(
2134 p_sequence,
2135 c_po_header_id,
2136 c_release_id,
2137 c_line_id,
2138 c_location_id,
2139 c_distribution_id,
2140 c_item_id,
2141 c_po_num,
2142 c_revision_num,
2143 c_line_num,
2144 c_location_num,
2145 c_distribution_num,
2146 c_level_altered,
2147 --fnd_message.get_String('PO', 'POS_QUANTITY'),
2148 'ICX_QUANTITY',
2149 p_line_from.quantity,
2150 p_line_to.quantity
2151 );
2152 END IF;
2153
2154 v_progress := '490';
2155
2156 /* UOM */
2157 IF NVL( p_line_from.unit_meas_lookup_code, ' ' ) <>
2158 NVL( p_line_to.unit_meas_lookup_code, ' ' ) THEN
2159 insert_changes(
2160 p_sequence,
2161 c_po_header_id,
2162 c_release_id,
2163 c_line_id,
2164 c_location_id,
2165 c_distribution_id,
2166 c_item_id,
2167 c_po_num,
2168 c_revision_num,
2169 c_line_num,
2170 c_location_num,
2171 c_distribution_num,
2172 c_level_altered,
2173 --fnd_message.get_String('PO', 'POS_UOM'),
2174 'ICX_UOM',
2175 p_line_from.unit_meas_lookup_code,
2176 p_line_to.unit_meas_lookup_code
2177 );
2178 END IF;
2179
2180 v_progress := '500';
2181
2182 /* Source quotation header */
2186 p_sequence,
2183 IF NVL( p_line_from.from_header_id, -99 ) <>
2184 NVL( p_line_to.from_header_id, -99 ) THEN
2185 insert_changes(
2187 c_po_header_id,
2188 c_release_id,
2189 c_line_id,
2190 c_location_id,
2191 c_distribution_id,
2192 c_item_id,
2193 c_po_num,
2194 c_revision_num,
2195 c_line_num,
2196 c_location_num,
2197 c_distribution_num,
2198 c_level_altered,
2199 --fnd_message.get_String('PO', 'POS_SOURCE_QT_HEADER'),
2200 'ICX_SOURCE_QT_HEADER',
2201 get_source_quotation_header(
2202 p_line_from.from_header_id ),
2203 get_source_quotation_header(
2204 p_line_to.from_header_id )
2205
2206 );
2207 END IF;
2208
2209 v_progress := '510';
2210
2211 /* Source quotation line */
2212 IF NVL( p_line_from.from_line_id, -99 ) <>
2213 NVL( p_line_to.from_line_id, -99 ) THEN
2214 insert_changes(
2215 p_sequence,
2216 c_po_header_id,
2217 c_release_id,
2218 c_line_id,
2219 c_location_id,
2220 c_distribution_id,
2221 c_item_id,
2222 c_po_num,
2223 c_revision_num,
2224 c_line_num,
2225 c_location_num,
2226 c_distribution_num,
2227 c_level_altered,
2228 --fnd_message.get_String('PO', 'POS_SOURCE_QT_LINE'),
2229 'ICX_SOURCE_QT_LINE',
2230 get_source_quotation_line(
2231 p_line_from.from_line_id ),
2232 get_source_quotation_line(
2233 p_line_to.from_line_id )
2234
2235 );
2236 END IF;
2237
2238 v_progress := '520';
2239
2240 /* Hazard class */
2241 IF NVL( p_line_from.hazard_class_id, -99 ) <>
2242 NVL( p_line_to.hazard_class_id, -99 ) THEN
2243 insert_changes(
2244 p_sequence,
2245 c_po_header_id,
2246 c_release_id,
2247 c_line_id,
2248 c_location_id,
2249 c_distribution_id,
2250 c_item_id,
2251 c_po_num,
2252 c_revision_num,
2253 c_line_num,
2254 c_location_num,
2255 c_distribution_num,
2256 c_level_altered,
2257 --fnd_message.get_String('PO', 'POS_HAZARD_CLASS'),
2258 'ICX_HAZARD_CLASS',
2259 get_hazard_class( p_line_from.hazard_class_id ),
2260 get_hazard_class( p_line_to.hazard_class_id )
2261 );
2262 END IF;
2263
2264 v_progress := '530';
2265
2266 /* Contract number */
2267 IF NVL( p_line_from.contract_num, ' ' ) <>
2268 NVL( p_line_to.contract_num, ' ' ) THEN
2269 insert_changes(
2270 p_sequence,
2271 c_po_header_id,
2272 c_release_id,
2273 c_line_id,
2274 c_location_id,
2275 c_distribution_id,
2276 c_item_id,
2277 c_po_num,
2278 c_revision_num,
2279 c_line_num,
2280 c_location_num,
2281 c_distribution_num,
2282 c_level_altered,
2283 --fnd_message.get_String('PO', 'POS_CONTRACT_NUMBER'),
2284 'ICX_CONTRACT_NUMBER',
2285 p_line_from.contract_num,
2286 p_line_to.contract_num
2287 );
2288 END IF;
2289
2290 v_progress := '540';
2291
2292 /* Supplie item number */
2293 IF NVL( p_line_from.vendor_product_num, ' ' ) <>
2294 NVL( p_line_to.vendor_product_num, ' ' ) THEN
2295 insert_changes(
2296 p_sequence,
2297 c_po_header_id,
2298 c_release_id,
2299 c_line_id,
2300 c_location_id,
2301 c_distribution_id,
2302 c_item_id,
2303 c_po_num,
2304 c_revision_num,
2305 c_line_num,
2306 c_location_num,
2307 c_distribution_num,
2308 c_level_altered,
2309 --fnd_message.get_String('PO', 'POS_SUPPLIER_ITEM_NUM'),
2310 'ICX_SUPPLIER_ITEM_NUM',
2311 p_line_from.vendor_product_num,
2312 p_line_to.vendor_product_num
2313 );
2314 END IF;
2315
2316 v_progress := '550';
2317
2318 /* Note to vendor */
2319 IF NVL( p_line_from.note_to_vendor, ' ' ) <>
2320 NVL( p_line_to.note_to_vendor, ' ' ) THEN
2321 insert_changes(
2322 p_sequence,
2323 c_po_header_id,
2324 c_release_id,
2325 c_line_id,
2326 c_location_id,
2327 c_distribution_id,
2328 c_item_id,
2329 c_po_num,
2330 c_revision_num,
2331 c_line_num,
2332 c_location_num,
2333 c_distribution_num,
2334 c_level_altered,
2335 --fnd_message.get_String('PO', 'POS_NOTE_TO_VENDOR'),
2336 'ICX_NOTE_TO_VENDOR',
2337 p_line_from.note_to_vendor,
2338 p_line_to.note_to_vendor
2339 );
2340 END IF;
2341
2342 v_progress := '560';
2343
2344 /* UN number */
2345 IF NVL( p_line_from.un_number_id, -99 ) <>
2346 NVL( p_line_to.un_number_id, -99 ) THEN
2347 insert_changes(
2348 p_sequence,
2349 c_po_header_id,
2350 c_release_id,
2351 c_line_id,
2352 c_location_id,
2353 c_distribution_id,
2354 c_item_id,
2355 c_po_num,
2356 c_revision_num,
2357 c_line_num,
2358 c_location_num,
2359 c_distribution_num,
2360 c_level_altered,
2361 --fnd_message.get_String('PO', 'POS_UN_NUMBER'),
2362 'ICX_UN_NUMBER',
2363 get_un_number( p_line_from.un_number_id ),
2364 get_un_number( p_line_to.un_number_id )
2365 );
2366 END IF;
2367
2371 IF NVL( p_line_from.price_type_lookup_code, ' ' ) <>
2368 v_progress := '570';
2369
2370 /* Price type */
2372 NVL( p_line_to.price_type_lookup_code, ' ' ) THEN
2373 insert_changes(
2374 p_sequence,
2375 c_po_header_id,
2376 c_release_id,
2377 c_line_id,
2378 c_location_id,
2379 c_distribution_id,
2380 c_item_id,
2381 c_po_num,
2382 c_revision_num,
2383 c_line_num,
2384 c_location_num,
2385 c_distribution_num,
2386 c_level_altered,
2387 --fnd_message.get_String('PO', 'POS_PRICE_TYPE'),
2388 'ICX_PRICE_TYPE',
2389 get_po_lookup(
2390 'PRICE TYPE',
2391 p_line_from.price_type_lookup_code ),
2392 get_po_lookup(
2393 'PRICE TYPE',
2394 p_line_to.price_type_lookup_code )
2395 );
2396 END IF;
2397
2398 v_progress := '580';
2399
2400 /* Quantity agreed */
2401 /*Bug 1461326
2402 Quantity_commited is a number field and to handle nulls we
2403 were incorrectly using the following
2404 NVL( p_line_from.quantity_committed, ' ' )
2405 NVL( p_line_to.quantity_committed, ' ' )
2406 Code replaced to the following and resolved the issue
2407 NVL( p_line_from.quantity_committed,-99)
2408 NVL( p_line_to.quantity_committed,-99)
2409 */
2410
2411 IF NVL( p_line_from.quantity_committed, -99 ) <>
2412 NVL( p_line_to.quantity_committed,-99 ) THEN
2413 insert_changes(
2414 p_sequence,
2415 c_po_header_id,
2416 c_release_id,
2417 c_line_id,
2418 c_location_id,
2419 c_distribution_id,
2420 c_item_id,
2421 c_po_num,
2422 c_revision_num,
2423 c_line_num,
2424 c_location_num,
2425 c_distribution_num,
2426 c_level_altered,
2427 --fnd_message.get_String('PO', 'POS_QUANTITY_AGREED'),
2428 'ICX_QUANTITY_AGREED',
2429 p_line_from.quantity_committed,
2430 p_line_to.quantity_committed
2431 );
2432 END IF;
2433
2434 v_progress := '590';
2435
2436 /* Amount agreed */
2437 /*Bug 1461326
2438 Comitted_amount is a number field and to handle nulls we
2439 were incorrectly using the following
2440 NVL( p_line_from.committed_amount, ' ' )
2441 NVL( p_line_to.committed_amount, ' ' )
2442 Code replaced to the following and resolved the issue
2443 NVL( p_line_from.committed_amount,-99)
2444 NVL( p_line_to.committed_amount,-99)
2445 */
2446 IF NVL( p_line_from.committed_amount, -99 ) <>
2447 NVL( p_line_to.committed_amount, -99 ) THEN
2448 insert_changes(
2449 p_sequence,
2450 c_po_header_id,
2451 c_release_id,
2452 c_line_id,
2453 c_location_id,
2454 c_distribution_id,
2455 c_item_id,
2456 c_po_num,
2457 c_revision_num,
2458 c_line_num,
2459 c_location_num,
2460 c_distribution_num,
2461 c_level_altered,
2462 --fnd_message.get_String('PO', 'POS_AMOUNT_AGREED'),
2463 'ICX_AMOUNT_AGREED',
2464 p_line_from.committed_amount,
2465 p_line_to.committed_amount
2466 );
2467 END IF;
2468
2469 /* Closed code */
2470 IF NVL( p_line_from.closed_code, ' ' ) <>
2471 NVL( p_line_to.closed_code , ' ' ) THEN
2472 insert_changes(
2473 p_sequence,
2474 c_po_header_id,
2475 c_release_id,
2476 c_line_id,
2477 c_location_id,
2478 c_distribution_id,
2479 c_item_id,
2480 c_po_num,
2481 c_revision_num,
2482 c_line_num,
2483 c_location_num,
2484 c_distribution_num,
2485 c_level_altered,
2486 --fnd_message.get_String('PO', 'POS_CLOSED_CODE'),
2487 'ICX_CLOSED_CODE',
2488 get_po_lookup( 'DOCUMENT STATE',
2489 p_line_from.committed_amount ),
2490 get_po_lookup( 'DOCUMENT STATE',
2491 p_line_to.committed_amount )
2492 );
2493 END IF;
2494
2495 /* Bug - 1260356 - Need to show archived changes for Line Level Expiration date */
2496 IF NVL( p_line_from.expiration_date,
2497 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
2498 NVL( p_line_to.expiration_date,
2499 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
2500 insert_changes(
2501 p_sequence,
2502 c_po_header_id,
2503 c_release_id,
2504 c_line_id,
2505 c_location_id,
2506 c_distribution_id,
2507 c_item_id,
2508 c_po_num,
2509 c_revision_num,
2510 c_line_num,
2511 c_location_num,
2512 c_distribution_num,
2513 c_level_altered,
2514 --fnd_message.get_String('PO', 'POS_EXPIRATION_DATE'),
2515 'ICX_EXPIRATION_DATE',
2516 to_char(p_line_from.expiration_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
2517 to_char(p_line_to.expiration_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
2518 );
2519 END IF;
2520
2521 EXCEPTION
2522 WHEN others THEN
2523 PO_MESSAGE_S.SQL_ERROR(
2524 'PO_COMPARE_REVISIONS.COMPARE_LINES',
2525 v_progress,
2526 sqlcode );
2527 RAISE;
2528
2529 END compare_lines;
2530
2531
2535 * compare_locations
2532
2533 /*********************************************************************
2534 * NAME
2536 *
2537 * PURPOSE
2538 * Accepts two records of the same locations with different revisions,
2539 * compare the data in both locations, and stores the differences in a
2540 * temporary table.
2541 *
2542 * ARGUMENTS
2543 * p_loc_from Old version of the line location.
2544 * p_loc_to New version of the line location.
2545 * p_sequence Sequence number to use in the temp table to identify
2546 * the records for delta.
2547 *
2548 * NOTES
2549 * The comparison is not done on all fields, but only the ones than
2550 * cause a revision change, according to Oracle Purchasing Reference
2551 * Manual.
2552 *
2553 * The fields that can be changed on PO header, and cause a revision
2554 * number increase are:
2555 *
2556 * Cancel Flag
2557 * Shipment number
2558 * Ship-to location
2559 * Quantity
2560 * Promised date
2561 * Need-by date
2562 * Last accept date
2563 * Taxable flag
2564 * Shipment price
2565 *
2566 * HISTORY
2567 * 08-AUG-97 Nilo Paredes Created.
2568 * 23-SEP-97 Rami Haddad Added comparison for shipment number
2569 * and shipment price.
2570 ********************************************************************/
2571 PROCEDURE compare_locations(
2572 p_loc_from in po_line_locations_archive%ROWTYPE,
2573 p_loc_to in po_line_locations_archive%ROWTYPE,
2574 p_sequence IN NUMBER
2575 ) AS
2576
2577 /* Constant variables to pass for insert_changes */
2578 c_level_altered icx_po_revisions_temp.level_altered%TYPE
2579 := 'ICX_SHIPMENT';
2580 c_po_header_id NUMBER;
2581 c_release_id NUMBER;
2582 c_po_num po_headers_archive.segment1%TYPE := NULL;
2583 c_line_id NUMBER;
2584 c_line_num NUMBER := NULL;
2585 c_location_id NUMBER;
2586 c_location_num NUMBER;
2587 c_revision_num NUMBER;
2588 c_distribution_id NUMBER := NULL;
2589 c_distribution_num NUMBER := NULL;
2590 c_item_id NUMBER := NULL;
2591
2592 v_progress VARCHAR2(3);
2593
2594 BEGIN
2595
2596 /*
2597 * At least the latest revision should exist.
2598 */
2599 IF p_loc_to.po_header_id IS NULL THEN
2600 RETURN;
2601 END IF;
2602
2603 /* Set values for all constants */
2604 c_po_header_id := p_loc_to.po_header_id;
2605 c_release_id := p_loc_to.po_release_id;
2606 c_line_id := p_loc_to.po_line_id;
2607 c_location_id := p_loc_to.line_location_id;
2608 c_location_num := p_loc_to.shipment_num;
2609 c_revision_num := p_loc_to.revision_num;
2610
2611
2612 --get the line number using line id
2613 /* Bug 2201739, we should fetch item_id from line level */
2614
2615 select line_num, item_id
2616 into c_line_num, c_item_id
2617 from po_lines_all
2618 where po_line_id = p_loc_to.po_line_id;
2619
2620 v_progress := '600';
2621
2622 /* If the previous record does not exist, then this is a new one. */
2623 IF p_loc_from.po_header_id IS NULL THEN
2624 insert_changes(
2625 p_sequence,
2626 c_po_header_id,
2627 c_release_id,
2628 c_line_id,
2629 c_location_id,
2630 c_distribution_id,
2631 c_item_id,
2632 c_po_num,
2633 c_revision_num,
2634 c_line_num,
2635 c_location_num,
2636 c_distribution_num,
2637 c_level_altered,
2638 --fnd_message.get_String('PO', 'POS_NEW'),
2639 'ICX_NEW',
2640 NULL,
2641 NULL
2642 );
2643 RETURN;
2644 END IF;
2645
2646 /* Are the lines the same? */
2647 IF p_loc_from.line_location_id <> p_loc_to.line_location_id THEN
2648 RETURN;
2649 END IF;
2650
2651 /* Do not compare lines of the same revision number. */
2652 IF NVL( p_loc_from.revision_num, -99 ) =
2653 NVL( p_loc_to.revision_num, -99 ) THEN
2654 RETURN;
2655 END IF;
2656
2657 /*
2658 * If current line location is cancelled, then check if the priior one
2659 * is cancelled as well. If it is, then there is no change. Otherwise
2660 * the line is cancelled for the current revision.
2661 */
2662
2663 v_progress := '610';
2664
2665 IF p_loc_to.cancel_flag = 'Y' THEN
2666 IF p_loc_from.cancel_flag = 'Y'
2667 THEN
2668 RETURN;
2669 ELSE
2670 insert_changes(
2671 p_sequence,
2672 c_po_header_id,
2673 c_release_id,
2674 c_line_id,
2675 c_location_id,
2676 c_distribution_id,
2677 c_item_id,
2678 c_po_num,
2679 c_revision_num,
2680 c_line_num,
2681 c_location_num,
2682 c_distribution_num,
2683 c_level_altered,
2684 --fnd_message.get_string('PO', 'POS_CANCELLED'),
2685 'ICX_CANCELLED',
2686 NULL,
2687 NULL
2688 );
2689 RETURN;
2690 END IF;
2691 END IF;
2692
2693 /*
2694 * Line location not cancelled in current PO. Compare all field with
2695 * line in prior revision.
2696 */
2697
2698 v_progress := '620';
2699
2700 /* Shipment number */
2701 IF NVL( p_loc_from.shipment_num, -99 ) <>
2702 NVL( p_loc_to.shipment_num, -99 ) THEN
2703 insert_changes(
2704 p_sequence,
2708 c_location_id,
2705 c_po_header_id,
2706 c_release_id,
2707 c_line_id,
2709 c_distribution_id,
2710 c_item_id,
2711 c_po_num,
2712 c_revision_num,
2713 c_line_num,
2714 c_location_num,
2715 c_distribution_num,
2716 c_level_altered,
2717 --fnd_message.get_String('PO', 'POS_SHIP_NUM'),
2718 'ICX_SHIP_NUM',
2719 p_loc_from.shipment_num,
2720 p_loc_to.shipment_num
2721 );
2722 END IF;
2723
2724 v_progress := '630';
2725
2726 /* Ship-to location */
2727 IF NVL( p_loc_from.ship_to_location_id, -99 ) <>
2728 NVL( p_loc_to.ship_to_location_id, -99 ) THEN
2729 insert_changes(
2730 p_sequence,
2731 c_po_header_id,
2732 c_release_id,
2733 c_line_id,
2734 c_location_id,
2735 c_distribution_id,
2736 c_item_id,
2737 c_po_num,
2738 c_revision_num,
2739 c_line_num,
2740 c_location_num,
2741 c_distribution_num,
2742 c_level_altered,
2743 --fnd_message.get_String('PO', 'POS_SHIP_TO'),
2744 'ICX_SHIP_TO',
2745 get_location( p_loc_from.ship_to_location_id ),
2746 get_location( p_loc_to.ship_to_location_id )
2747 );
2748 END IF;
2749
2750 v_progress := '640';
2751
2752 /* Quantity */
2753 IF NVL( p_loc_from.quantity, -99 ) <> NVL( p_loc_to.quantity, -99 )
2754 THEN
2755 insert_changes(
2756 p_sequence,
2757 c_po_header_id,
2758 c_release_id,
2759 c_line_id,
2760 c_location_id,
2761 c_distribution_id,
2762 c_item_id,
2763 c_po_num,
2764 c_revision_num,
2765 c_line_num,
2766 c_location_num,
2767 c_distribution_num,
2768 c_level_altered,
2769 --fnd_message.get_String('PO', 'POS_QUANTITY'),
2770 'ICX_QUANTITY',
2771 p_loc_from.quantity,
2772 p_loc_to.quantity
2773 );
2774 END IF;
2775
2776 v_progress := '650';
2777
2778 /* promised date */
2779 IF NVL( p_loc_from.promised_date,
2780 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
2781 NVL( p_loc_to.promised_date,
2782 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
2783 insert_changes(
2784 p_sequence,
2785 c_po_header_id,
2786 c_release_id,
2787 c_line_id,
2788 c_location_id,
2789 c_distribution_id,
2790 c_item_id,
2791 c_po_num,
2792 c_revision_num,
2793 c_line_num,
2794 c_location_num,
2795 c_distribution_num,
2796 c_level_altered,
2797 --fnd_message.get_String('PO', 'POS_PROMISED_DATE'),
2798 'ICX_PROMISED_DATE',
2799 to_char(p_loc_from.promised_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
2800 to_char(p_loc_to.promised_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
2801
2802 );
2803 END IF;
2804
2805 v_progress := '660';
2806
2807 /* Need-by date */
2808 IF NVL( p_loc_from.need_by_date,
2809 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
2810 NVL( p_loc_to.need_by_date,
2811 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
2812 insert_changes(
2813 p_sequence,
2814 c_po_header_id,
2815 c_release_id,
2816 c_line_id,
2817 c_location_id,
2818 c_distribution_id,
2819 c_item_id,
2820 c_po_num,
2821 c_revision_num,
2822 c_line_num,
2823 c_location_num,
2824 c_distribution_num,
2825 c_level_altered,
2826 --fnd_message.get_String('PO', 'POS_NEED_BY_DATE'),
2827 'ICX_NEED_BY_DATE',
2828 to_char(p_loc_from.need_by_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
2829 to_char(p_loc_to.need_by_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
2830 );
2831 END IF;
2832
2833 v_progress := '670';
2834
2835 /* Last accept date */
2836 IF NVL( p_loc_from.last_accept_date,
2837 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
2838 NVL( p_loc_to.last_accept_date,
2839 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
2840 insert_changes(
2841 p_sequence,
2842 c_po_header_id,
2843 c_release_id,
2844 c_line_id,
2845 c_location_id,
2846 c_distribution_id,
2847 c_item_id,
2848 p_loc_from.revision_num || '-' || p_loc_to.revision_num,
2849 c_revision_num,
2850 c_line_num,
2851 c_location_num,
2852 c_distribution_num,
2853 c_level_altered,
2854 --fnd_message.get_String('PO', 'POS_LAST_ACCEPT_DATE'),
2855 'ICX_LAST_ACCEPT_DATE',
2856 to_char(p_loc_from.last_accept_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
2857 to_char(p_loc_to.last_accept_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
2858 );
2859 END IF;
2860
2861 v_progress := '680';
2862
2863 /* Taxable flag */
2864 IF NVL( p_loc_from.taxable_flag, ' ' ) <>
2865 NVL( p_loc_to.taxable_flag, ' ' ) THEN
2866 insert_changes(
2867 p_sequence,
2868 c_po_header_id,
2869 c_release_id,
2870 c_line_id,
2871 c_location_id,
2872 c_distribution_id,
2873 c_item_id,
2874 c_po_num,
2875 c_revision_num,
2876 c_line_num,
2877 c_location_num,
2878 c_distribution_num,
2879 c_level_altered,
2883 'YES/NO',
2880 --fnd_message.get_String('PO', 'POS_TAXABLE_FLAG'),
2881 'ICX_TAXABLE_FLAG',
2882 get_po_lookup(
2884 p_loc_from.taxable_flag ),
2885 get_po_lookup(
2886 'YES/NO',
2887 p_loc_to.taxable_flag )
2888 );
2889 END IF;
2890
2891
2892 v_progress := '690';
2893
2894 /* Price break */
2895 IF NVL( p_loc_from.price_override, -99 ) <>
2896 NVL( p_loc_to.price_override, -99 ) THEN
2897 insert_changes(
2898 p_sequence,
2899 c_po_header_id,
2900 c_release_id,
2901 c_line_id,
2902 c_location_id,
2903 c_distribution_id,
2904 c_item_id,
2905 c_po_num,
2906 c_revision_num,
2907 c_line_num,
2908 c_location_num,
2909 c_distribution_num,
2910 c_level_altered,
2911 --fnd_message.get_String('PO', 'POS_PRICE_BREAK'),
2912 'ICX_PRICE_BREAK',
2913 p_loc_from.price_override,
2914 p_loc_to.price_override
2915 );
2916 END IF;
2917
2918 EXCEPTION
2919 WHEN others THEN
2920 PO_MESSAGE_S.SQL_ERROR(
2921 'PO_COMPARE_REVISIONS.COMPARE_LOCATIONS',
2922 v_progress,
2923 sqlcode );
2924 RAISE;
2925
2926 END compare_locations;
2927
2928
2929
2930 /*********************************************************************
2931 * NAME
2932 * compare_distributions
2933 *
2934 * PURPOSE
2935 * Accepts two records of the same distribution with different
2936 * revisions, compare the data in both distributions, and stores the
2937 * differences in a temporary table.
2938 *
2939 * ARGUMENTS
2940 * p_dist_from Old version of the distribution.
2941 * p_dist_to New version of the distribution.
2942 * p_sequence Sequence number to use in the temp table to identify
2943 * the records for delta.
2944 *
2945 * NOTES
2946 * The comparison is not done on all fields, but only the ones than
2947 * cause a revision change, according to Oracle Purchasing Reference
2948 * Manual.
2949 *
2950 * The fields that can be changed on PO header, and cause a revision
2951 * number increase are:
2952 *
2953 * Cancel Flag
2954 * Quantity ordered
2955 * Requestor
2956 * Charge account
2957 *
2958 * Distributions cannot be cancelled, so there is no need to check for
2959 * cancelled distribution lines.
2960 *
2961 * HISTORY
2962 * 08-AUG-97 Nilo Paredes Created.
2963 ********************************************************************/
2964 PROCEDURE compare_distributions(
2965 p_dist_from in po_distributions_archive%ROWTYPE,
2966 p_dist_to in po_distributions_archive%ROWTYPE,
2967 p_sequence IN NUMBER
2968 ) AS
2969
2970 /*
2971 * Constant variables to pass for insert_changes
2972 */
2973 c_level_altered icx_po_revisions_temp.level_altered%TYPE
2974 := 'ICX_DISTRIBUTION';
2975 c_po_header_id NUMBER;
2976 c_release_id NUMBER;
2977 c_po_num po_headers_archive.segment1%TYPE := NULL;
2978 c_line_id NUMBER;
2979 c_line_num NUMBER := NULL;
2980 c_location_id NUMBER;
2981 c_location_num NUMBER := NULL;
2982 c_distribution_id NUMBER;
2983 c_distribution_num NUMBER;
2984 c_revision_num NUMBER;
2985 c_item_id NUMBER := NULL;
2986
2987 v_progress VARCHAR2(3);
2988
2989 BEGIN
2990
2991 /*
2992 * At least the latest revision should exist.
2993 */
2994 IF p_dist_to.po_header_id IS NULL THEN
2995 RETURN;
2996 END IF;
2997
2998 /*
2999 * Set values for all constants
3000 */
3001 c_po_header_id := p_dist_to.po_header_id;
3002 c_release_id := p_dist_to.po_release_id;
3003 c_line_id := p_dist_to.po_line_id;
3004 c_location_id := p_dist_to.line_location_id;
3005 c_distribution_id := p_dist_to.po_distribution_id;
3006 c_distribution_num := p_dist_to.distribution_num;
3007 c_revision_num := p_dist_to.revision_num;
3008
3009 v_progress := '700';
3010
3011 /* Bug# 1893770 */
3012 --get the line number using line id
3013
3014 select line_num
3015 into c_line_num
3016 from po_lines_all
3017 where po_line_id = p_dist_to.po_line_id;
3018
3019 --get the shipment number using line location id
3020
3021 select shipment_num
3022 into c_location_num
3023 from po_line_locations_all
3024 where line_location_id = p_dist_to.line_location_id;
3025
3026 /*
3027 * If the old record does not exist, then this is a new one.
3028 */
3029
3030 IF p_dist_from.po_header_id IS NULL THEN
3031 insert_changes(
3032 p_sequence,
3033 c_po_header_id,
3034 c_release_id,
3035 c_line_id,
3036 c_location_id,
3037 c_distribution_id,
3038 c_item_id,
3039 c_po_num,
3040 c_revision_num,
3041 c_line_num,
3042 c_location_num,
3043 c_distribution_num,
3044 c_level_altered,
3045 --fnd_message.get_String('PO', 'POS_NEW'),
3046 'ICX_NEW',
3047 NULL,
3048 NULL
3049 );
3050 RETURN;
3051 END IF;
3052
3053 /*
3054 * Are the lines the same?
3055 */
3059 END IF;
3056 IF NVL( p_dist_from.line_location_id, -99 ) <>
3057 NVL( p_dist_to.line_location_id, -99 ) THEN
3058 RETURN;
3060
3061 /*
3062 * Do not compare lines of the same revision number.
3063 */
3064
3065 IF NVL( p_dist_from.revision_num, -99 ) =
3066 NVL( p_dist_to.revision_num, -99 ) THEN
3067 RETURN;
3068 END IF;
3069
3070 v_progress := '710';
3071
3072 /* Quantity ordered */
3073 IF NVL( p_dist_from.quantity_ordered, -99 ) <>
3074 NVL( p_dist_to.quantity_ordered, -99 ) THEN
3075 insert_changes(
3076 p_sequence,
3077 c_po_header_id,
3078 c_release_id,
3079 c_line_id,
3080 c_location_id,
3081 c_distribution_id,
3082 c_item_id,
3083 c_po_num,
3084 c_revision_num,
3085 c_line_num,
3086 c_location_num,
3087 c_distribution_num,
3088 c_level_altered,
3089 --fnd_message.get_String('PO', 'POS_QUANTITY'),
3090 'ICX_QUANTITY',
3091 p_dist_from.quantity_ordered,
3092 p_dist_to.quantity_ordered
3093 );
3094 END IF;
3095
3096 v_progress := '720';
3097
3098 /* Requestor */
3099 IF NVL( p_dist_from.deliver_to_person_id, -99 ) <>
3100 NVL( p_dist_to.deliver_to_person_id, -99 ) THEN
3101 insert_changes(
3102 p_sequence,
3103 c_po_header_id,
3104 c_release_id,
3105 c_line_id,
3106 c_location_id,
3107 c_distribution_id,
3108 c_item_id,
3109 c_po_num,
3110 c_revision_num,
3111 c_line_num,
3112 c_location_num,
3113 c_distribution_num,
3114 c_level_altered,
3115 --fnd_message.get_String('PO', 'POS_REQUESTOR'),
3116 'ICX_REQUESTOR',
3117 p_dist_from.deliver_to_person_id,
3118 p_dist_to.deliver_to_person_id
3119 );
3120 END IF;
3121
3122 v_progress := '730';
3123
3124 /* Charge account */
3125
3126 /* Bug 1181007
3127 inserting the associated charge account instead of the
3128 ccid by using the function get_charge_account.
3129 */
3130
3131 IF NVL( p_dist_from.code_combination_id, -99 ) <>
3132 NVL( p_dist_to.code_combination_id, -99 ) THEN
3133 insert_changes(
3134 p_sequence,
3135 c_po_header_id,
3136 c_release_id,
3137 c_line_id,
3138 c_location_id,
3139 c_distribution_id,
3140 c_item_id,
3141 c_po_num,
3142 c_revision_num,
3143 c_line_num,
3144 c_location_num,
3145 c_distribution_num,
3146 c_level_altered,
3147 --fnd_message.get_String('PO', 'POS_CHARGE_ACCT'),
3148 'ICX_CHARGE_ACCT',
3149 get_charge_account(p_dist_from.code_combination_id),
3150 get_charge_account(p_dist_to.code_combination_id)
3151 );
3152 END IF;
3153
3154 EXCEPTION
3155 WHEN others THEN
3156 PO_MESSAGE_S.SQL_ERROR(
3157 'PO_COMPARE_REVISIONS.PO_COMPARE_DISTRIBUTIONS',
3158 v_progress,
3159 sqlcode );
3160 RAISE;
3161
3162 END compare_distributions;
3163
3164 /*Bug 1181007
3165 The following function get_charge_account is added to fetch
3166 the charge account when the ccid is given
3167 */
3168
3169 /*********************************************************************
3170 * NAME
3171 * get_charge_account
3172 *
3173 * PURPOSE
3174 * To fetch the charge account based on the ccid from the
3175 * gl_code_combinations_kfv.
3176 *
3177 * ARGUMENTS
3178 * p_code_combiation_id Unique identifier for charge account in
3179 * GL_CODE_COMBINATIONS_KFV view.
3180 *
3181 * NOTES
3182 * Return NULL if an error occurs.
3183 *
3184 * HISTORY
3185 * 01-FEB-2000 Suresh Arunachalam Created
3186 ********************************************************************/
3187
3188 FUNCTION get_charge_account(p_code_combination_id IN NUMBER)
3189 RETURN VARCHAR2 AS
3190 v_charge_account varchar2( 4000 );
3191 v_progress varchar2(3);
3192
3193 BEGIN
3194
3195 v_progress := '830';
3196
3197 SELECT concatenated_segments
3198 INTO v_charge_account
3199 FROM gl_code_combinations_kfv
3200 WHERE code_combination_id = p_code_combination_id;
3201
3202 RETURN v_charge_account;
3203
3204 EXCEPTION
3205 WHEN no_data_found THEN
3206 RETURN NULL;
3207 WHEN others THEN
3208 PO_MESSAGE_S.SQL_ERROR(
3209 'PO_COMPARE_REVISIONS.GET_CHARGE_ACCOUNT',
3210 v_progress,
3211 sqlcode );
3212 RAISE;
3213
3214 END get_charge_account;
3215
3216 END po_compare_revisions;