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