[Home] [Help]
PACKAGE BODY: APPS.POS_COMPARE_REVISIONS
Source
1 PACKAGE BODY pos_compare_revisions AS
2 /* $Header: POSPOCMB.pls 120.20 2006/09/06 20:35:13 abtrived noship $ */
3
4 /*********************************************************************
5 * NAME
6 * purge
7 *
8 * PURPOSE
9 * Delete records from the temp table, POS_PO_REVISIONS_GT, 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 DATE DEFAULT SYSDATE - 1/12
28 ) AS
29 v_progress VARCHAR2(3);
30
31 BEGIN
32
33 retcode := 0;
34 errbuf := '';
35
36 v_progress := '010';
37
38 DELETE pos_po_revisions_gt
39 WHERE creation_date < NVL( p_date, 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_buyer
315 *
316 * PURPOSE
317 * Resolves the buyer name.
318 *
319 * ARGUMENTS
320 * p_agent_id Unique identifier for buyer in PER_PEOPLE_F table.
321 *
322 * NOTES
323 * Return NULL if no matching records were found.
324 *
325 * HISTORY
326 * 08-AUG-97 Nilo Paredes Created
327 * 22-SEP-97 Rami Haddad Return NULL if no values found.
328 ********************************************************************/
329 FUNCTION get_organization( p_org_id IN NUMBER )
330 RETURN VARCHAR2 AS
331
332 v_org_name hr_all_organization_units_tl.name%TYPE;
333 v_progress varchar2(3);
334
335 BEGIN
336
337 v_progress := '070';
338
339 --bug 4946281
340 /*
341 select organization_name
342 into v_org_name
343 from org_organization_definitions ood
344 where ood.organization_id = p_org_id;
345 */
346 select HAOTL.NAME
347 into v_org_name
348 from HR_ALL_ORGANIZATION_UNITS_TL HAOTL
349 where HAOTL.ORGANIZATION_ID = p_org_id and
350 HAOTL.LANGUAGE = USERENV('LANG');
351
352
353 RETURN v_org_name;
354
355 EXCEPTION
356 WHEN no_data_found THEN
357 RETURN NULL;
358 WHEN others THEN
359 PO_MESSAGE_S.SQL_ERROR(
360 'PO_COMPARE_REVISIONS.GET_ORG_NAME',
361 v_progress,
362 sqlcode );
363 RAISE;
364
365 END get_organization;
366
367
368
369 /*********************************************************************
370 * NAME
371 * get_vendor_contact
372 *
373 * PURPOSE
374 * Resolves the supplier contact.
375 *
376 * ARGUMENTS
377 * p_vendor_contact_id Unique identifier for vendor in
378 * PO_VENDOR_CONTACTS table.
379 *
380 * NOTES
381 * Return NULL if no matching records were found.
382 *
383 * HISTORY
384 * 08-AUG-97 Nilo Paredes Created
385 * 22-SEP-97 Rami Haddad Return NULL if no values found.
386 * Obtain name from PO_VENDOR_CONTACTS
387 * table.
388 ********************************************************************/
389 FUNCTION get_vendor_contact( p_vendor_contact_id IN NUMBER )
390 RETURN VARCHAR2 AS
391
392 v_full_name varchar2( 40 );
393 v_progress varchar2(3);
394
395 BEGIN
396
397 v_progress := '080';
398
399 SELECT DECODE(last_name, NULL, NULL, last_name || ',' || first_name)
400 INTO v_full_name
401 FROM po_vendor_contacts
402 WHERE vendor_contact_id = p_vendor_contact_id;
403
404 RETURN v_full_name;
405
406 EXCEPTION
407 WHEN no_data_found THEN
408 RETURN NULL;
409 WHEN others THEN
410 PO_MESSAGE_S.SQL_ERROR(
411 'PO_COMPARE_REVISIONS.GET_VENDOR_CONTACT',
412 v_progress,
413 sqlcode );
414 RAISE;
415
416 END get_vendor_contact;
417
418
419
420 /*********************************************************************
421 * NAME
422 * get_location
423 *
424 * PURPOSE
425 * Resolves the location code
426 *
427 * ARGUMENTS
428 * p_location_id Unique identifier for the location in
429 * HR_LOCATIONS table.
430 *
431 * NOTES
432 * Return NULL if no matching records were found.
433 *
434 * HISTORY
435 * 08-AUG-97 Nilo Paredes Created
436 * 22-SEP-97 Rami Haddad Return NULL if no values found.
437 ********************************************************************/
438 FUNCTION get_location( p_location_id IN NUMBER )
439 RETURN VARCHAR2 AS
440
441 v_location_code hr_locations.location_code%TYPE;
442 v_progress varchar2(3);
443
444 BEGIN
445
446 v_progress := '090';
447
448 SELECT location_code
449 INTO v_location_code
450 FROM hr_locations_all
451 WHERE location_id = p_location_id;
452
453 RETURN v_location_code;
454
455 EXCEPTION
456 WHEN no_data_found THEN
457 RETURN NULL;
458
459 WHEN others THEN
460 PO_MESSAGE_S.SQL_ERROR(
461 'PO_COMPARE_REVISIONS.GET_LOCATION',
462 v_progress,
463 sqlcode );
464 RAISE;
465
466 END get_location;
467
468
469
470 /*********************************************************************
471 * NAME
472 * get_source_quotation_header
473 *
474 * PURPOSE
475 * Resolves the source quotation PO number.
476 *
477 * ARGUMENTS
478 * p_header_id Unique identifier for PO in PO_HEADERS table.
479 *
480 * NOTES
481 * Return NULL if no matching records were found.
482 *
483 * HISTORY
484 * 23-SEP-97 Rami Haddad Created
485 ********************************************************************/
486 FUNCTION get_source_quotation_header( p_header_id in number )
487 RETURN VARCHAR2 AS
488
489 v_po_num po_headers.segment1%TYPE;
490 v_progress varchar2(3);
491
492 BEGIN
493
494 v_progress := '100';
495
496 SELECT segment1
497 INTO v_po_num
498 FROM po_headers_all
499 WHERE po_header_id = p_header_id;
500
501 RETURN v_po_num;
502
503 EXCEPTION
504 WHEN no_data_found THEN
505 RETURN NULL;
506 WHEN others THEN
507 PO_MESSAGE_S.SQL_ERROR(
508 'PO_COMPARE_REVISIONS.GET_SOURCE_QUOTATION_HEADER',
509 v_progress,
510 sqlcode );
511 RAISE;
512
513 END get_source_quotation_header;
514
515
516
517 /*********************************************************************
518 * NAME
519 * get_source_quotation_line
520 *
521 * PURPOSE
522 * Resolves the source quotation PO line number.
523 *
524 * ARGUMENTS
525 * p_line_id Unique identifier for PO line in PO_LINES table.
526 *
527 * NOTES
528 * Return NULL if no matching records were found.
529 *
530 * HISTORY
531 * 23-SEP-97 Rami Haddad Created
532 ********************************************************************/
533 FUNCTION get_source_quotation_line( p_line_id in number )
534 RETURN VARCHAR2 AS
535
536 v_line_num po_lines.line_num%TYPE;
537 v_progress varchar2(3);
538
539 BEGIN
540
541 v_progress := '110';
542
543 SELECT line_num
544 INTO v_line_num
545 FROM po_lines_all
546 WHERE po_line_id = p_line_id;
547
548 RETURN v_line_num;
549
550 EXCEPTION
551 WHEN no_data_found THEN
552 RETURN NULL;
553 WHEN others THEN
554 PO_MESSAGE_S.SQL_ERROR(
555 'PO_COMPARE_REVISIONS.GET_SOURCE_QUOTATION_LINE',
556 v_progress,
557 sqlcode );
558 RAISE;
559
560 END get_source_quotation_line;
561
562
563
564 /*********************************************************************
565 * NAME
566 * get_po_lookup
567 *
568 * PURPOSE
569 *
570 *
571 * ARGUMENTS
572 *
573 *
574 * NOTES
575 *
576 *
577 * HISTORY
578 * 31-OCT-97 Rami Haddad Created
579 ********************************************************************/
580 FUNCTION get_po_lookup(
581 p_lookup_type IN VARCHAR2,
582 p_lookup_code IN VARCHAR2
583 ) RETURN VARCHAR2 AS
584
585 v_description po_lookup_codes.description%TYPE;
586 v_progress varchar2(3);
587
588 BEGIN
589
590 v_progress := '120';
591
592 SELECT description
593 INTO v_description
594 FROM po_lookup_codes
595 WHERE
596 lookup_type = p_lookup_type
597 AND lookup_code = p_lookup_code;
598
599 RETURN v_description;
600
601 EXCEPTION
602 WHEN no_data_found THEN
603 RETURN NULL;
604 WHEN others THEN
605 PO_MESSAGE_S.SQL_ERROR(
606 'PO_COMPARE_REVISIONS.GET_PO_LOOKUP',
607 v_progress,
608 sqlcode );
609 RAISE;
610
611 END get_po_lookup;
612
613 /*********************************************************************
614 * NAME
615 * get_vendor_site
616 *
617 * PURPOSE
618 * Resolves the vendor site
619 *
620 * ARGUMENTS
621 * p_vendor_site_id Unique identifier for the location in
622 * PO_VENDOR_SITES table.
623 *
624 * NOTES
625 * Return NULL if no matching records were found.
626 *
627 * HISTORY
628 * 09-AUG-01 Amitabh Mitra created
629 ********************************************************************/
630 FUNCTION get_vendor_site( p_vendor_site_id IN NUMBER )
631 RETURN VARCHAR2 AS
632
633 v_site_code varchar2(20);
634 v_progress varchar2(3);
635
636 BEGIN
637
638 v_progress := '140';
639
640 SELECT vendor_site_code
641 INTO v_site_code
642 FROM po_vendor_sites_all
643 WHERE vendor_site_id = p_vendor_site_id;
644
645 RETURN v_site_code;
646
647 EXCEPTION
648 WHEN no_data_found THEN
649 RETURN NULL;
650
651 WHEN others THEN
652 PO_MESSAGE_S.SQL_ERROR(
653 'PO_COMPARE_REVISIONS.GET_VENDOR_SITE',
654 v_progress,
655 sqlcode );
656 RAISE;
657
658 END get_vendor_site;
659
660 /*********************************************************************
661 * NAME
662 * get_pricediff_type
663 *
664 * PURPOSE
665 * Resolves the price differential type
666 *
667 * ARGUMENTS
668 * p_pricediff_code Unique in PO_PRICE_DIFF_LOOKUPS_V
669 *
670 * NOTES
671 * Return NULL if no matching records were found.
672 *
673 * HISTORY
674 * 21-OCT-03 Amitabh Mitra created
675 ********************************************************************/
676 FUNCTION get_pricediff_type( p_pricediff_code IN VARCHAR2 )
677 RETURN VARCHAR2 AS
678
679 v_pricediff_dsp varchar2(240);
680 v_progress varchar2(3);
681
682 BEGIN
683
684 v_progress := '140';
685
686 SELECT PRICE_DIFFERENTIAL_DSP
687 INTO v_pricediff_dsp
688 FROM PO_PRICE_DIFF_LOOKUPS_V
689 WHERE PRICE_DIFFERENTIAL_TYPE = p_pricediff_code;
690
691 RETURN v_pricediff_dsp;
692 EXCEPTION
693 WHEN no_data_found THEN
694 RETURN NULL;
695
696 WHEN others THEN
697 PO_MESSAGE_S.SQL_ERROR(
698 'PO_COMPARE_REVISIONS.GET_PRICEDIFF_TYPE',
699 v_progress,
700 sqlcode );
701 RAISE;
702
703 END get_pricediff_type;
704
705 /*********************************************************************
706 * NAME
707 * insert_changes
708 *
709 * PURPOSE
710 * Insert the comparison result into the temp table.
711 *
712 * ARGUMENTS
713 * p_line_seq Sequence number to identify the comparison
714 * results for a specific record.
715 * p_header_id Unique identifier for PO.
716 * p_release_id Unique identifier for PO release.
717 * p_line_id Unique identifier for PO line.
718 * p_location_id Unique identifier for PO line location.
719 * p_distribution_id Unique identifier for PO distribution.
720 * p_item_id Unique identified for line item.
721 * p_po_num PO number.
722 * p_line_num PO line number.
723 * p_location_num PO line location number.
724 * p_distribution_num PO distribution number.
725 * p_level_altered Level altered. Possible values are:
726 *
727 * Code User-friendly name
728 * ---- ------------------
729 * ICX_DISTRIBUTION Distribution
730 * ICX_HEADER Header
731 * ICX_LINE Line
732 * ICX_SHIPMENT Shipment
733 *
734 * p_field_altered Field altered. Possible values are:
735 *
736 * Code User-friendly name
737 * ---- ------------------
738 * ICX_ACCEPTANCE_DUE_DATE Acceptance Due Date
739 * ICX_ACCEPTANCE_REQUIRED Acceptance Required
740 * ICX_AMOUNT Amount
741 * ICX_AMOUNT_AGREED Amount Agreed
742 * ICX_AMOUNT_DUE_DATE Amount Due Date
743 * ICX_AMOUNT_LIMIT Amount Limit
744 * ICX_BILL_TO Bill To
745 * ICX_BUYER Buyer
746 * ICX_CANCELLED Cancelled
747 * ICX_CHARGE_ACCT Charge Account
748 * ICX_CLOSED_CODE Closed
749 * ICX_CONFIRMED Confirm
750 * ICX_CONTRACT_NUMBER Contract Number
751 * ICX_EFFECTIVE_DATE Effective Date
752 * ICX_ERROR Error
753 * ICX_EXPIRATION_DATE Expiration Date
754 * ICX_FOB FOB
755 * ICX_FREIGHT_TERMS Freight Terms
756 * ICX_HAZARD_CLASS Hazard Class
757 * ICX_ITEM Item
758 * ICX_ITEM_DESCRIPTION Item Description
759 * ICX_ITEM_REVISION Item Revision
760 * ICX_LAST_ACCEPT_DATE Last Acceptance Date
761 * ICX_LINE_NUMBER Line Number
762 * ICX_NEED_BY_DATE Need By Date
763 * ICX_NEW New
764 * ICX_NOTE_TO_VENDOR Note To Vendor
765 * ICX_PAYMENT_TERMS Payment Terms
766 * ICX_PRICE_BREAK Price Break
767 * ICX_PRICE_TYPE Price Type
768 * ICX_PROMISED_DATE Promised Date
769 * ICX_QUANTITY Quantity
770 * ICX_QUANTITY_AGREED Quantity Agreed
771 * ICX_RELEASE_DATE Released Date
772 * ICX_RELEASE_NUMBER Release Number
773 * ICX_REQUESTOR Requestor
774 * ICX_SHIP_NUM Shipment Number
775 * ICX_SHIP_TO Ship To
776 * ICX_SHIP_VIA Ship Via
777 * ICX_SOURCE_QT_HEADER Source Quotation Header
778 * ICX_SOURCE_QT_LINE Source Quotation Line
779 * ICX_SUPPLIER_CONTACT Supplier Contact
780 * ICX_SUPPLIER_ITEM_NUM Supplier Item Number
781 * ICX_TAXABLE_FLAG Taxable
782 * ICX_UNIT_PRICE Unit Price
783 * ICX_UN_NUMBER UN Number
784 * ICX_UOM UOM
785 *
786 * p_changes_from Previous value of field altered.
787 * p_changes_to New value of field altered.
788 *
789 * NOTES
790 * Stamps every line with the current system date. Use that value
791 * when purging the table, to remove 2-hours old records for example.
792 *
793 * Replace IDs that are NULL with -99, to do the sorting correctly.
794 * When sorting in an ascending order, NULL values are at the last,
795 * while, to sort these records correctly, they should be the first.
796 *
797 * HISTORY
798 * 08-AUG-97 Nilo Paredes Created
799 * 22-SEP-97 Rami Haddad Removed prompts look-up in AK.
800 * Replace NULL with -99 for sorting.
801 * 22-APR-06 Abhishek Tri Adding new columns for item and job
802 * instead of calculating value in VO
803 ********************************************************************/
804 PROCEDURE insert_changes(
805 p_line_seq IN NUMBER,
806 p_header_id IN NUMBER,
807 p_release_id IN NUMBER,
808 p_line_id IN NUMBER,
809 p_location_id IN NUMBER,
810 p_distribution_id IN NUMBER,
811 p_item_id IN NUMBER,
812 p_po_num IN VARCHAR2,
813 p_revision_num IN NUMBER,
814 p_line_num IN NUMBER,
815 p_location_num IN NUMBER,
816 p_distribution_num IN NUMBER,
817 p_level_altered IN VARCHAR2,
818 p_field_altered IN VARCHAR2,
819 p_changes_from IN VARCHAR2,
820 p_changes_to IN VARCHAR2,
821 p_enabled_org_name in VARCHAR2 default null,
822 p_price_diff_num in NUMBER default null,
823 p_change_from_date IN DATE DEFAULT NULL,
824 p_change_to_date IN DATE DEFAULT NULL,
825 p_item in varchar2 default null,
826 p_job in varchar2 default null
827 ) AS
828
829 v_progress VARCHAR2(3);
830
831 BEGIN
832
833 v_progress := '900';
834
835 INSERT INTO
836 pos_po_revisions_gt(
837 line_seq,
838 creation_date,
839 header_id,
840 release_id,
841 line_id,
842 location_id,
843 distribution_id,
844 item_id,
845 po_num,
846 revision_num,
847 line_num,
848 location_num,
849 distribution_num,
850 level_altered,
851 field_altered,
852 changes_from,
853 changes_to,
854 enabled_org_name,
855 price_diff_num,
856 change_from_date,
857 change_to_date,
858 item,
859 job
860 )
861 VALUES
862 (
863 p_line_seq,
864 SYSDATE,
865 p_header_id,
866 p_release_id,
867 p_line_id,
868 p_location_id,
869 p_distribution_id,
870 p_item_id,
871 p_po_num,
872 p_revision_num,
873 p_line_num,
874 p_location_num,
875 p_distribution_num,
876 p_level_altered,
877 p_field_altered,
878 p_changes_from,
879 p_changes_to,
880 p_enabled_org_name,
881 p_price_diff_num,
882 p_change_from_date,
883 p_change_to_date,
884 p_item,
885 p_job
886 );
887
888 --COMMIT;
889
890 EXCEPTION
891 WHEN others THEN
892 PO_MESSAGE_S.SQL_ERROR(
893 'PO_COMPARE_REVISIONS.INSERT_CHANGES',
894 v_progress,
895 sqlcode );
896 RAISE;
897
898 END insert_changes;
899
900
901
902 /*********************************************************************
903 * NAME
904 * verify_no_differences
905 *
906 * PURPOSE
907 * Insert a line in the POS_PO_REVISIONS_GT table indicating that
908 * there are no differences between the compared records.
909 *
910 * ARGUMENTS
911 * p_line_seq Sequence number to identify the comparison
912 * results for a specific record.
913 *
914 * NOTES
915 * Refer to bug#549414 for more details.
916 *
917 * This is used specifically to handle AK functionality. AK is
918 * expecting a row in table with the PK. The initial table in this
919 * case is actually a procedure, so AK fails. The procedure checks.
920 * If there are no differences, insert a dummy row in the table that
921 * say something like 'No differences.'
922 *
923 * HISTORY
924 * 31-OCT-97 Rami Haddad Created
925 ********************************************************************/
926 PROCEDURE verify_no_differences( p_line_seq IN NUMBER ) AS
927
928 records_exist number;
929 v_progress varchar2(3);
930
931 BEGIN
932
933 v_progress := '130';
934
935 SELECT COUNT(*)
936 INTO records_exist
937 FROM pos_po_revisions_gt
938 WHERE line_seq = p_line_seq;
939
940 IF records_exist = 0 THEN
941 insert_changes(
942 p_line_seq,
943 -99,
944 NULL,
945 NULL,
946 NULL,
947 NULL,
948 NULL,
949 NULL,
950 0, -- -99
951 NULL,
952 NULL,
953 NULL,
954 'ICX_HEADER',
955 --fnd_message.get_String('PO', 'POS_NO_DIFFERENCE'),
956 'ICX_NO_DIFFERENCE',
957 NULL,
958 NULL
959 );
960 END IF;
961
962 EXCEPTION
963 WHEN others THEN
964 PO_MESSAGE_S.SQL_ERROR(
965 'PO_COMPARE_REVISIONS.VERIFY_NO_DIFFERENCES',
966 v_progress,
967 sqlcode );
968 RAISE;
969
970 END verify_no_differences;
971
972
973
974 /*********************************************************************
975 * NAME
976 * compare_headers
977 *
978 * PURPOSE
979 * Accepts two records of the same PO with different revisions,
980 * compare the data in both POs, and stores the differences in a
981 * temporary table.
982 *
983 * ARGUMENTS
984 * p_po_from Old version of the PO.
985 * p_po_to New version of the PO.
986 * p_sequence Sequence number to use in the temp table to identify
987 * the records for delta.
988 *
989 * NOTES
990 * The comparison is not done on all fields, but only the ones than
991 * cause a revision change, according to Oracle Purchasing Reference
992 * Manual.
993 *
994 * The fields that can be changed on PO header, and cause a revision
995 * number increase are:
996 *
997 * Cancel Flag
998 * Buyer
999 * Vendor contact
1000 * Confirming flag
1001 * Ship-to location
1002 * Bill-to location
1003 * Payment terms
1004 * Amount
1005 * Ship via
1006 * FOB
1007 * Freignt terms
1008 * Note to vendor
1009 * Acceptance required
1010 * Acceptance due date
1011 * Amount Limit
1012 * Start Date
1013 * End Date
1014 * Deliverables Change Date
1015 * Articles Change Date
1016 *
1017 * HISTORY
1018 * 08-AUG-97 Nilo Paredes Created.
1019 * 22-SEP-97 Rami Haddad Added buyer comparison.
1020 ********************************************************************/
1021 PROCEDURE compare_headers(
1022 p_po_from in po_headers_archive_all%ROWTYPE,
1023 p_po_to in po_headers_archive_all%ROWTYPE,
1024 p_sequence in number,
1025 p_comparison_flag in varchar2
1026 ) AS
1027
1028 /*
1029 * Constant variables to pass for insert_changes
1030 */
1031 c_level_altered pos_po_revisions_gt.level_altered%TYPE
1032 := 'ICX_HEADER';
1033 c_po_header_id NUMBER;
1034 c_po_num po_headers_archive_all.segment1%TYPE;
1035 c_revision_num NUMBER;
1036 c_release_id NUMBER := NULL;
1037 c_line_id NUMBER := NULL;
1038 c_line_num NUMBER := NULL;
1039 c_location_id NUMBER := NULL;
1040 c_location_num NUMBER := NULL;
1041 c_distribution_id NUMBER := NULL;
1042 c_distribution_num NUMBER := NULL;
1043 c_item_id NUMBER := NULL;
1044
1045 v_amount_from NUMBER;
1046 v_amount_to NUMBER;
1047 v_progress VARCHAR2(3);
1048
1049 CURSOR current_ga_assign_cursor(
1050 current_header_id NUMBER,
1051 current_revision_num NUMBER ) IS
1052 SELECT *
1053 FROM po_ga_org_assignments_archive
1054 WHERE po_header_id = current_header_id and
1055 revision_num = current_revision_num and
1056 latest_external_flag = 'Y';
1057
1058 BEGIN
1059
1060 /*
1061 * At least the latest revision should exist.
1062 */
1063 IF p_po_to.po_header_id IS NULL THEN
1064 RETURN;
1065 END IF;
1066
1067 /*
1068 * Set values for all constants
1069 */
1070 c_po_header_id := p_po_to.po_header_id;
1071 c_po_num := p_po_to.segment1;
1072 c_revision_num := p_po_to.revision_num;
1073
1074 /*
1075 * If the old record does not exist, then this is an error.
1076 */
1077 v_progress := '910';
1078
1079 IF p_po_from.po_header_id IS NULL THEN
1080 insert_changes(
1081 p_sequence,
1082 c_po_header_id,
1083 c_release_id,
1084 c_line_id,
1085 c_location_id,
1086 c_distribution_id,
1087 c_item_id,
1088 c_po_num,
1089 c_revision_num,
1090 c_line_num,
1091 c_location_num,
1092 c_distribution_num,
1093 c_level_altered,
1094 --fnd_message.get_String('POS', 'POS_ERROR'),
1095 'ICX_ERROR',
1096 NULL,
1097 NULL
1098 );
1099 RETURN;
1100 END IF;
1101
1102 /* Are the POs the same? */
1103 IF p_po_from.po_header_id <> p_po_to.po_header_id THEN
1104 RETURN;
1105 END IF;
1106
1107 /* Do not compare POs of the same revision number. */
1108 IF NVL( p_po_from.revision_num, -99 ) =
1109 NVL( p_po_to.revision_num, -99 ) THEN
1110 RETURN;
1111 END IF;
1112
1113 v_progress := '140';
1114
1115 /* Check for cancelled PO. */
1116 IF p_po_to.cancel_flag = 'Y' THEN
1117 IF p_po_from.cancel_flag = 'Y'
1118 THEN
1119 RETURN;
1120 ELSE
1121 insert_changes(
1122 p_sequence,
1123 c_po_header_id,
1124 c_release_id,
1125 c_line_id,
1126 c_location_id,
1127 c_distribution_id,
1128 c_item_id,
1129 c_po_num,
1130 c_revision_num,
1131 c_line_num,
1132 c_location_num,
1133 c_distribution_num,
1134 c_level_altered,
1135 --fnd_message.get_string('POS', 'POS_CANCELLED'),
1136 'ICX_CANCELLED',
1137 NULL,
1138 NULL
1139 );
1140 RETURN;
1141 END IF;
1142 END IF;
1143 /*
1144 * Check for the differences
1145 */
1146
1147 v_progress := '150';
1148
1149 /* Buyer */
1150 IF p_po_from.agent_id <> p_po_to.agent_id THEN
1151 insert_changes(
1152 p_sequence,
1153 c_po_header_id,
1154 c_release_id,
1155 c_line_id,
1156 c_location_id,
1157 c_distribution_id,
1158 c_item_id,
1159 c_po_num,
1160 c_revision_num,
1161 c_line_num,
1162 c_location_num,
1163 c_distribution_num,
1164 c_level_altered,
1165 --fnd_message.get_String('POS', 'POS_BUYER'),
1166 'ICX_BUYER',
1167 get_buyer( p_po_from.agent_id ),
1168 get_buyer( p_po_to.agent_id )
1169 );
1170 END IF;
1171
1172 v_progress := '160';
1173
1174 /* Vendor contact */
1175 IF NVL( p_po_from.vendor_contact_id, -99 ) <>
1176 NVL( p_po_to.vendor_contact_id, -99 ) THEN
1177 insert_changes(
1178 p_sequence,
1179 c_po_header_id,
1180 c_release_id,
1181 c_line_id,
1182 c_location_id,
1183 c_distribution_id,
1184 c_item_id,
1185 c_po_num,
1186 c_revision_num,
1187 c_line_num,
1188 c_location_num,
1189 c_distribution_num,
1190 c_level_altered,
1191 --fnd_message.get_String('POS', 'POS_SUPPLIER_CONTACT'),
1192 'ICX_SUPPLIER_CONTACT',
1193 get_vendor_contact( p_po_from.vendor_contact_id ),
1194 get_vendor_contact( p_po_to.vendor_contact_id )
1195 );
1196 END IF;
1197
1198 v_progress := '170';
1199
1200 /* Confirming flag */
1201 IF NVL( p_po_from.confirming_order_flag, ' ' ) <>
1202 NVL( p_po_to.confirming_order_flag, ' ' ) THEN
1203 insert_changes(
1204 p_sequence,
1205 c_po_header_id,
1206 c_release_id,
1207 c_line_id,
1208 c_location_id,
1209 c_distribution_id,
1210 c_item_id,
1211 c_po_num,
1212 c_revision_num,
1213 c_line_num,
1214 c_location_num,
1215 c_distribution_num,
1216 c_level_altered,
1217 --fnd_message.get_String('POS', 'POS_CONFIRMED'),
1218 'ICX_CONFIRMED',
1219 p_po_from.confirming_order_flag,
1220 p_po_to.confirming_order_flag
1221 );
1222 END IF;
1223
1224 v_progress := '180';
1225
1226 /* Ship-to location */
1227 IF NVL( p_po_from.ship_to_location_id, -99 ) <>
1228 NVL( p_po_to.ship_to_location_id, -99 ) THEN
1229 insert_changes(
1230 p_sequence,
1231 c_po_header_id,
1232 c_release_id,
1233 c_line_id,
1234 c_location_id,
1235 c_distribution_id,
1236 c_item_id,
1237 c_po_num,
1238 c_revision_num,
1239 c_line_num,
1240 c_location_num,
1241 c_distribution_num,
1242 c_level_altered,
1243 --fnd_message.get_String('POS', 'POS_SHIP_TO'),
1244 'ICX_SHIP_TO',
1245 get_location( p_po_from.ship_to_location_id ),
1246 get_location( p_po_to.ship_to_location_id )
1247 );
1248 END IF;
1249
1250 v_progress := '190';
1251
1252 /* Bill-to location */
1253 IF NVL( p_po_from.bill_to_location_id, -99 ) <>
1254 NVL( p_po_to.bill_to_location_id, -99 ) THEN
1255 insert_changes(
1256 p_sequence,
1257 c_po_header_id,
1258 c_release_id,
1259 c_line_id,
1260 c_location_id,
1261 c_distribution_id,
1262 c_item_id,
1263 c_po_num,
1264 c_revision_num,
1265 c_line_num,
1266 c_location_num,
1267 c_distribution_num,
1268 c_level_altered,
1269 --fnd_message.get_String('PO', 'POS_BILL_TO'),
1270 'ICX_BILL_TO',
1271 get_location( p_po_from.bill_to_location_id ),
1272 get_location( p_po_to.bill_to_location_id )
1273 );
1274 END IF;
1275
1276 v_progress := '200';
1277
1278 /* Payment terms */
1279 IF NVL( p_po_from.terms_id, -99 ) <> NVL( p_po_to.terms_id, -99 ) THEN
1280 insert_changes(
1281 p_sequence,
1282 c_po_header_id,
1283 c_release_id,
1284 c_line_id,
1285 c_location_id,
1286 c_distribution_id,
1287 c_item_id,
1288 c_po_num,
1289 c_revision_num,
1290 c_line_num,
1291 c_location_num,
1292 c_distribution_num,
1293 c_level_altered,
1294 --fnd_message.get_String('PO', 'POS_PAYMENT_TERMS'),
1295 'ICX_PAYMENT_TERMS',
1296 get_ap_terms( p_po_from.terms_id ),
1297 get_ap_terms( p_po_to.terms_id )
1298 );
1299 END IF;
1300
1301 /* Amount */
1302
1303 /* bug 5509777 - Do amount comparison only when PO Type is not in 'BLANKET', 'CONTRACT' */
1304 IF (p_po_from.type_lookup_code <> 'BLANKET' AND p_po_from.type_lookup_code <> 'CONTRACT') THEN
1305
1306 -- using new methods for calculating amount - bug 5398214
1307 /*
1308 v_amount_from := po_totals_po_sv.get_po_archive_total(
1309 c_po_header_id,
1310 p_po_from.revision_num );
1311 v_amount_to := po_totals_po_sv.get_po_archive_total(
1312 c_po_header_id,
1313 p_po_to.revision_num );
1314 */
1315 v_amount_from := POS_TOTALS_PO_SV.get_po_archive_total(
1316 c_po_header_id,
1317 p_po_from.revision_num, p_po_from.type_lookup_code );
1318 v_amount_to := POS_TOTALS_PO_SV.get_po_archive_total(
1319 c_po_header_id,
1320 p_po_to.revision_num, p_po_to.type_lookup_code );
1321
1322 v_progress := '210';
1323
1324 IF v_amount_from <> v_amount_to THEN
1325 insert_changes(
1326 p_sequence,
1327 c_po_header_id,
1328 c_release_id,
1329 c_line_id,
1330 c_location_id,
1331 c_distribution_id,
1332 c_item_id,
1333 c_po_num,
1334 c_revision_num,
1335 c_line_num,
1336 c_location_num,
1337 c_distribution_num,
1338 c_level_altered,
1339 --fnd_message.get_String('PO', 'POS_AMOUNT'),
1340 'ICX_AMOUNT',
1341 v_amount_from,
1342 v_amount_to
1343 );
1344 END IF;
1345
1346 END IF;
1347
1348 v_progress := '220';
1349
1350 /* Ship via */
1351 IF NVL( p_po_from.ship_via_lookup_code, ' ' ) <>
1352 NVL( p_po_to.ship_via_lookup_code, ' ' ) THEN
1353 insert_changes(
1354 p_sequence,
1355 c_po_header_id,
1356 c_release_id,
1357 c_line_id,
1358 c_location_id,
1359 c_distribution_id,
1360 c_item_id,
1361 c_po_num,
1362 c_revision_num,
1363 c_line_num,
1364 c_location_num,
1365 c_distribution_num,
1366 c_level_altered,
1367 --fnd_message.get_String('PO', 'POS_SHIP_VIA'),
1368 'ICX_SHIP_VIA',
1369 p_po_from.ship_via_lookup_code,
1370 p_po_to.ship_via_lookup_code
1371 );
1372 END IF;
1373
1374 v_progress := '230';
1375
1376 /* FOB */
1377 IF NVL( p_po_from.fob_lookup_code, ' ' ) <>
1378 NVL( p_po_to.fob_lookup_code, ' ' ) THEN
1379 insert_changes(
1380 p_sequence,
1381 c_po_header_id,
1382 c_release_id,
1383 c_line_id,
1384 c_location_id,
1385 c_distribution_id,
1386 c_item_id,
1387 c_po_num,
1388 c_revision_num,
1389 c_line_num,
1390 c_location_num,
1391 c_distribution_num,
1392 c_level_altered,
1393 --fnd_message.get_String('PO', 'POS_FOB'),
1394 'ICX_FOB',
1395 get_po_lookup( 'FOB', p_po_from.fob_lookup_code ),
1396 get_po_lookup( 'FOB', p_po_to.fob_lookup_code )
1397 );
1398 END IF;
1399
1400 v_progress := '240';
1401
1402 /* Freignt terms */
1403 IF NVL( p_po_from.freight_terms_lookup_code, ' ' ) <>
1404 NVL( p_po_to.freight_terms_lookup_code, ' ' ) THEN
1405 insert_changes(
1406 p_sequence,
1407 c_po_header_id,
1408 c_release_id,
1409 c_line_id,
1410 c_location_id,
1411 c_distribution_id,
1412 c_item_id,
1413 c_po_num,
1414 c_revision_num,
1415 c_line_num,
1416 c_location_num,
1417 c_distribution_num,
1418 c_level_altered,
1419 --fnd_message.get_String('PO', 'POS_FREIGHT_TERMS'),
1420 'ICX_FREIGHT_TERMS',
1421 get_po_lookup(
1422 'FREIGHT TERMS',
1423 p_po_from.freight_terms_lookup_code ),
1424 get_po_lookup(
1425 'FREIGHT TERMS',
1426 p_po_to.freight_terms_lookup_code )
1427 );
1428 END IF;
1429
1430 v_progress := '250';
1431
1432 /* Note to vendor */
1433 IF NVL( p_po_from.note_to_vendor, ' ' ) <>
1434 NVL( p_po_to.note_to_vendor, ' ' ) THEN
1435 insert_changes(
1436 p_sequence,
1437 c_po_header_id,
1438 c_release_id,
1439 c_line_id,
1440 c_location_id,
1441 c_distribution_id,
1442 c_item_id,
1443 c_po_num,
1444 c_revision_num,
1445 c_line_num,
1446 c_location_num,
1447 c_distribution_num,
1448 c_level_altered,
1449 --fnd_message.get_String('PO', 'POS_NOTE_TO_VENDOR'),
1450 'ICX_NOTE_TO_VENDOR',
1451 p_po_from.note_to_vendor,
1452 p_po_to.note_to_vendor
1453 );
1454 END IF;
1455
1456 v_progress := '260';
1457
1458 /* Acceptance required */
1459 IF NVL( p_po_from.acceptance_required_flag, ' ' ) <>
1460 NVL( p_po_to.acceptance_required_flag, ' ' ) THEN
1461 insert_changes(
1462 p_sequence,
1463 c_po_header_id,
1464 c_release_id,
1465 c_line_id,
1466 c_location_id,
1467 c_distribution_id,
1468 c_item_id,
1469 c_po_num,
1470 c_revision_num,
1471 c_line_num,
1472 c_location_num,
1473 c_distribution_num,
1474 c_level_altered,
1475 --fnd_message.get_String('PO', 'POS_ACCEPTANCE_REQUIRED'),
1476 'ICX_ACCEPTANCE_REQUIRED',
1477 p_po_from.acceptance_required_flag,
1478 p_po_to.acceptance_required_flag
1479 );
1480 END IF;
1481
1482 v_progress := '270';
1483
1484 /* Acceptance due date */
1485 IF NVL( p_po_from.acceptance_due_date,
1486 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1487 NVL( p_po_to.acceptance_due_date,
1488 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1489 insert_changes(
1490 p_sequence,
1491 c_po_header_id,
1492 c_release_id,
1493 c_line_id,
1494 c_location_id,
1495 c_distribution_id,
1496 c_item_id,
1497 c_po_num,
1498 c_revision_num,
1499 c_line_num,
1500 c_location_num,
1501 c_distribution_num,
1502 c_level_altered,
1503 --fnd_message.get_String('PO', 'POS_ACCEPTANCE_DUE_DATE'),
1504 'ICX_ACCEPTANCE_DUE_DATE',
1505 --start bug 4179194
1506 /* to_char(p_po_from.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
1507 to_char(p_po_to.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
1508 );
1509 */
1510 null,
1511 null,
1512 null,
1513 null,
1514 p_po_from.acceptance_due_date,
1515 p_po_to.acceptance_due_date
1516 );
1517 --end bug 4179194
1518 END IF;
1519
1520 v_progress := '280';
1521
1522 /* Amount limit */
1523 IF NVL( p_po_from.amount_limit, -99 ) <>
1524 NVL( p_po_to.amount_limit, -99 ) THEN
1525 insert_changes(
1526 p_sequence,
1527 c_po_header_id,
1528 c_release_id,
1529 c_line_id,
1530 c_location_id,
1531 c_distribution_id,
1532 c_item_id,
1533 c_po_num,
1534 c_revision_num,
1535 c_line_num,
1536 c_location_num,
1537 c_distribution_num,
1538 c_level_altered,
1539 --fnd_message.get_String('PO', 'POS_AMOUNT_LIMIT'),
1540 'ICX_AMOUNT_LIMIT',
1541 p_po_from.amount_limit,
1542 p_po_to.amount_limit
1543 );
1544 END IF;
1545
1546 v_progress := '290';
1547
1548 IF NVL( p_po_from.start_date,
1549 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1550 NVL( p_po_to.start_date,
1551 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1552 insert_changes(
1553 p_sequence,
1554 c_po_header_id,
1555 c_release_id,
1556 c_line_id,
1557 c_location_id,
1558 c_distribution_id,
1559 c_item_id,
1560 c_po_num,
1561 c_revision_num,
1562 c_line_num,
1563 c_location_num,
1564 c_distribution_num,
1565 c_level_altered,
1566 --fnd_message.get_String('PO', 'POS_EFFECTIVE_DATE'),
1567 'ICX_START_DATE',
1568 --start bug 4179194
1569 /* to_char(p_po_from.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
1570 to_char(p_po_to.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
1571 );
1572 */
1573 null,
1574 null,
1575 null,
1576 null,
1577 p_po_from.start_date,
1578 p_po_to.start_date
1579 );
1580 --end bug 4179194
1581
1582 END IF;
1583
1584 v_progress := '300';
1585
1586 /* Expiration date */
1587 IF NVL( p_po_from.end_date,
1588 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1589 NVL( p_po_to.end_date,
1590 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1591 insert_changes(
1592 p_sequence,
1593 c_po_header_id,
1594 c_release_id,
1595 c_line_id,
1596 c_location_id,
1597 c_distribution_id,
1598 c_item_id,
1599 c_po_num,
1600 c_revision_num,
1601 c_line_num,
1602 c_location_num,
1603 c_distribution_num,
1604 c_level_altered,
1605 --fnd_message.get_String('PO', 'POS_EXPIRATION_DATE'),
1606 'ICX_EXPIRATION_DATE',
1607 --start bug 4179194
1608 /* to_char(p_po_from.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
1609 to_char(p_po_to.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
1610 );
1611 */
1612 null,
1613 null,
1614 null,
1615 null,
1616 p_po_from.end_date,
1617 p_po_to.end_date
1618 );
1619 --end bug 4179194
1620 END IF;
1621
1622 v_progress := '310';
1623
1624 /* Amount agreed */
1625 IF NVL( p_po_from.blanket_total_amount, -99 ) <>
1626 NVL( p_po_to.blanket_total_amount, -99 ) THEN
1627 insert_changes(
1628 p_sequence,
1629 c_po_header_id,
1630 c_release_id,
1631 c_line_id,
1632 c_location_id,
1633 c_distribution_id,
1634 c_item_id,
1635 c_po_num,
1636 c_revision_num,
1637 c_line_num,
1638 c_location_num,
1639 c_distribution_num,
1640 c_level_altered,
1641 --fnd_message.get_String('PO', 'POS_AMOUNT_AGREED'),
1642 'ICX_AMOUNT_AGREED',
1643 p_po_from.blanket_total_amount,
1644 p_po_to.blanket_total_amount
1645 );
1646 END IF;
1647 v_progress := '320';
1648
1649 /* Supplier Site */
1650 IF NVL( p_po_from.vendor_site_id, -99 ) <>
1651 NVL( p_po_to.vendor_site_id, -99 ) THEN
1652 insert_changes(
1653 p_sequence,
1654 c_po_header_id,
1655 c_release_id,
1656 c_line_id,
1657 c_location_id,
1658 c_distribution_id,
1659 c_item_id,
1660 c_po_num,
1661 c_revision_num,
1662 c_line_num,
1663 c_location_num,
1664 c_distribution_num,
1665 c_level_altered,
1666 'ICX_VENDOR_SITE',
1667 get_vendor_site(p_po_from.vendor_site_id),
1668 get_vendor_site(p_po_to.vendor_site_id)
1669 );
1670 END IF;
1671 /* Compare Article Change Date and Deliverables Change Date for
1672 Compare to Previous and Compare to Last Signed only */
1673
1674 --compare date for ALL also
1675 --fix for bug # 3616301
1676 if (p_comparison_flag in ('PREVIOUS','LASTSIGN', 'ALL')) then
1677 v_progress := '330';
1678 /* Deliverables Changed Date */
1679 IF NVL( p_po_from.conterms_deliv_upd_date,
1680 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1681 NVL( p_po_to.conterms_deliv_upd_date,
1682 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1683
1684 /*
1685 bug 4140325
1686 inserting the IF to check if p_po_from.conterms_deliv_upd_date is NULL
1687 if NOT NULL, then only show a link for deliverables
1688 */
1689
1690 /*
1691 bug 5471267
1692 inserting the IF to check if p_po_from.conterms_articles_upd_date is NOT NULL
1693 if one of conterms_deliv_upd_date OR conterms_articles_upd_date is NOT NULL, then only show a link for deliverables
1694
1695 */
1696 IF (p_po_from.conterms_deliv_upd_date IS NOT NULL OR p_po_from.conterms_articles_upd_date IS NOT NULL) THEN
1697
1698 insert_changes(
1699 p_sequence,
1700 c_po_header_id,
1701 c_release_id,
1702 c_line_id,
1703 c_location_id,
1704 c_distribution_id,
1705 c_item_id,
1706 c_po_num,
1707 c_revision_num,
1708 c_line_num,
1709 c_location_num,
1710 c_distribution_num,
1711 c_level_altered,
1712 'ICX_CONTERMS_DELIV_DATE',
1713 null,
1714 null
1715 );
1716
1717 END IF;
1718 END IF;
1719
1720 v_progress := '340';
1721 /* Articles Changed Date */
1722 IF NVL( p_po_from.conterms_articles_upd_date,
1723 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
1724 NVL( p_po_to.conterms_articles_upd_date,
1725 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
1726 /*
1727 bug 4140325
1728 inserting the IF to check if p_po_from.conterms_articles_upd_date is NULL
1729 if NULL, then show a link to the contreacts document instead
1730 */
1731 IF (p_po_from.conterms_articles_upd_date IS NULL) THEN
1732
1733 insert_changes(
1734 p_sequence,
1735 c_po_header_id,
1736 c_release_id,
1737 c_line_id,
1738 c_location_id,
1739 c_distribution_id,
1740 c_item_id,
1741 c_po_num,
1742 c_revision_num,
1743 c_line_num,
1744 c_location_num,
1745 c_distribution_num,
1746 c_level_altered,
1747 'ICX_CONTERMS_ARTICLES_DATE_NEW',
1748 null,
1749 null
1750 );
1751
1752 ELSE
1753
1754 insert_changes(
1755 p_sequence,
1756 c_po_header_id,
1757 c_release_id,
1758 c_line_id,
1759 c_location_id,
1760 c_distribution_id,
1761 c_item_id,
1762 c_po_num,
1763 c_revision_num,
1764 c_line_num,
1765 c_location_num,
1766 c_distribution_num,
1767 c_level_altered,
1768 'ICX_CONTERMS_ARTICLES_DATE',
1769 null,
1770 null
1771 );
1772
1773 END IF;
1774
1775 END IF;
1776 end if;
1777
1778 EXCEPTION
1779 WHEN others THEN
1780 PO_MESSAGE_S.SQL_ERROR(
1781 'PO_COMPARE_REVISIONS.COMPARE_HEADERS',
1782 v_progress,
1783 sqlcode );
1784 RAISE;
1785
1786
1787
1788 END compare_headers;
1789
1790
1791
1792 /*********************************************************************
1793 * NAME
1794 * compare_releases
1795 *
1796 * PURPOSE
1797 * Accepts two records of the same release with different revisions,
1798 * compare the data in both releases, and stores the differences in a
1799 * temporary table.
1800 *
1801 * ARGUMENTS
1802 * p_release_from Old version of the PO.
1803 * p_release_to New version of the PO.
1804 * p_sequence Sequence number to use in the temp table to
1805 * identify the records for delta.
1806 *
1807 * NOTES
1808 * The comparison is not done on all fields, but only the ones than
1809 * cause a revision change, according to Oracle Purchasing Reference
1810 * Manual.
1811 *
1812 * The fields that can be changed on PO header, and cause a revision
1813 * number increase are:
1814 *
1815 * Cancel Flag
1816 * Buyer
1817 * Acceptance required
1818 * Acceptance due date
1819 * Release number
1820 * Release date
1821 *
1822 * HISTORY
1823 * 08-AUG-97 Nilo Paredes Created.
1824 * 22-SEP-97 Rami Haddad Added buyer comparison.
1825 ********************************************************************/
1826 PROCEDURE compare_releases(
1827 p_release_from in po_releases_archive_all%ROWTYPE,
1828 p_release_to in po_releases_archive_all%ROWTYPE,
1829 p_sequence IN NUMBER
1830 ) AS
1831
1832 /*
1833 * Constant variables to pass for insert_changes
1834 */
1835 c_level_altered pos_po_revisions_gt.level_altered%TYPE
1836 := 'ICX_HEADER';
1837 c_po_header_id NUMBER;
1838 c_po_num po_headers_archive_all.segment1%TYPE;
1839 c_release_id NUMBER;
1840 c_revision_num NUMBER;
1841 c_line_id NUMBER := NULL;c_line_num NUMBER := NULL;
1842 c_location_id NUMBER := NULL;
1843 c_location_num NUMBER := NULL;
1844 c_distribution_id NUMBER := NULL;
1845 c_distribution_num NUMBER := NULL;
1846 c_item_id NUMBER := NULL;
1847 v_progress VARCHAR2(3);
1848 v_amount_from NUMBER;
1849 v_amount_to NUMBER;
1850 v_po_num po_headers_archive_all.segment1%TYPE;
1851
1852 BEGIN
1853
1854 /*
1855 * At least the latest revision should exist.
1856 */
1857 IF p_release_to.po_header_id IS NULL THEN
1858 RETURN;
1859 END IF;
1860
1861 /*
1862 * Set values for all constants
1863 */
1864 c_po_header_id := p_release_to.po_header_id;
1865
1866 v_progress := '320';
1867
1868 SELECT segment1
1869 INTO v_po_num
1870 FROM po_headers_archive_all
1871 WHERE
1872 po_header_id = p_release_to.po_header_id
1873 AND latest_external_flag = 'Y';
1874
1875 c_po_num := v_po_num || '-' ||
1876 p_release_to.release_num;
1877 c_revision_num := p_release_to.revision_num;
1878 c_release_id := p_release_to.po_release_id;
1879
1880 /*
1881 * If the old record does not exist, then this is a new one.
1882 */
1883
1884 v_progress := '330';
1885
1886 IF p_release_from.po_header_id IS NULL THEN
1887 insert_changes(
1888 p_sequence,
1889 c_po_header_id,
1890 c_release_id,
1891 c_line_id,
1892 c_location_id,
1893 c_distribution_id,
1894 c_item_id,
1895 c_po_num,
1896 c_revision_num,
1897 c_line_num,
1898 c_location_num,
1899 c_distribution_num,
1900 c_level_altered,
1901 --fnd_message.get_String('PO', 'POS_NEW'),
1902 'ICX_NEW',
1903 NULL,
1904 NULL
1905 );
1906 RETURN;
1907 END IF;
1908
1909 /*
1910 * Are the releases the same?
1911 */
1912 IF NVL( p_release_from.po_release_id, -99 ) <>
1913 NVL( p_release_to.po_release_id, -99 ) THEN
1914 RETURN;
1915 END IF;
1916
1917 /*
1918 * Do not compare releases of the same revision number.
1919 */
1920
1921 IF NVL( p_release_from.revision_num, -99 ) =
1922 NVL( p_release_to.revision_num, -99 ) THEN
1923 RETURN;
1924 END IF;
1925
1926 v_progress := '340';
1927
1928 /*
1929 * Check for cancelled release.
1930 */
1931 IF p_release_to.cancel_flag = 'Y' THEN
1932 IF p_release_from.cancel_flag = 'Y'
1933 THEN
1934 RETURN;
1935 ELSE
1936 insert_changes(
1937 p_sequence,
1938 c_po_header_id,
1939 c_release_id,
1940 c_line_id,
1941 c_location_id,
1942 c_distribution_id,
1943 c_item_id,
1944 c_po_num,
1945 c_revision_num,
1946 c_line_num,
1947 c_location_num,
1948 c_distribution_num,
1949 c_level_altered,
1950 --fnd_message.get_string('PO', 'POS_CANCELLED'),
1951 'ICX_CANCELLED',
1952 NULL,
1953 NULL
1954 );
1955 RETURN;
1956 END IF;
1957 END IF;
1958
1959 v_progress := '350';
1960
1961 /* Buyer */
1962 IF p_release_from.agent_id <> p_release_to.agent_id THEN
1963 insert_changes(
1964 p_sequence,
1965 c_po_header_id,
1966 c_release_id,
1967 c_line_id,
1968 c_location_id,
1969 c_distribution_id,
1970 c_item_id,
1971 c_po_num,
1972 c_revision_num,
1973 c_line_num,
1974 c_location_num,
1975 c_distribution_num,
1976 c_level_altered,
1977 --fnd_message.get_String('PO', 'POS_BUYER'),
1978 'ICX_BUYER',
1979 get_buyer( p_release_from.agent_id ),
1980 get_buyer( p_release_to.agent_id )
1981 );
1982 END IF;
1983
1984 v_progress := '360';
1985
1986 /* Amount -- new code to compare amount - bug 5452301 */
1987 v_amount_from := POS_TOTALS_PO_SV.get_release_archive_total(
1988 c_release_id,
1989 p_release_from.revision_num);
1990 v_amount_to := POS_TOTALS_PO_SV.get_release_archive_total(
1991 c_release_id,
1992 p_release_to.revision_num );
1993
1994 IF v_amount_from <> v_amount_to THEN
1995 insert_changes(
1996 p_sequence,
1997 c_po_header_id,
1998 c_release_id,
1999 c_line_id,
2000 c_location_id,
2001 c_distribution_id,
2002 c_item_id,
2003 c_po_num,
2004 c_revision_num,
2005 c_line_num,
2006 c_location_num,
2007 c_distribution_num,
2008 c_level_altered,
2009 --fnd_message.get_String('PO', 'POS_AMOUNT'),
2010 'ICX_AMOUNT',
2011 v_amount_from,
2012 v_amount_to
2013 );
2014 END IF;
2015
2016 v_progress := '370';
2017
2018 /* Acceptance due date */
2019 IF NVL( p_release_from.acceptance_due_date,
2020 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
2021 NVL( p_release_to.acceptance_due_date,
2022 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
2023 insert_changes(
2024 p_sequence,
2025 c_po_header_id,
2026 c_release_id,
2027 c_line_id,
2028 c_location_id,
2029 c_distribution_id,
2030 c_item_id,
2031 c_po_num,
2032 c_revision_num,
2033 c_line_num,
2034 c_location_num,
2035 c_distribution_num,
2036 c_level_altered,
2037 --fnd_message.get_String('PO', 'POS_ACCEPTANCE_DUE_DATE'),
2038 'ICX_ACCEPTANCE_DUE_DATE',
2039 --start bug 4179194
2040 /* to_char(p_release_from.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
2041 to_char(p_release_to.acceptance_due_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
2042 );
2043 */
2044 null,
2045 null,
2046 null,
2047 null,
2048 p_release_from.acceptance_due_date,
2049 p_release_to.acceptance_due_date
2050 );
2051 --end bug 4179194
2052 END IF;
2053
2054 v_progress := '380';
2055
2056 /* Acceptance required */
2057 IF NVL( p_release_from.acceptance_required_flag, ' ' ) <>
2058 NVL( p_release_to.acceptance_required_flag, ' ' ) THEN
2059 insert_changes(
2060 p_sequence,
2061 c_po_header_id,
2062 c_release_id,
2063 c_line_id,
2064 c_location_id,
2065 c_distribution_id,
2066 c_item_id,
2067 c_po_num,
2068 c_revision_num,
2069 c_line_num,
2070 c_location_num,
2071 c_distribution_num,
2072 c_level_altered,
2073 --fnd_message.get_String('PO', 'POS_ACCEPTANCE_REQUIRED'),
2074 'ICX_ACCEPTANCE_REQUIRED',
2075 p_release_from.acceptance_required_flag,
2076 p_release_to.acceptance_required_flag
2077 );
2078 END IF;
2079
2080 v_progress := '390';
2081
2082 /* Release number */
2083 IF NVL( p_release_from.release_num, -99 ) <>
2084 NVL( p_release_to.release_num, -99 ) THEN
2085 insert_changes(
2086 p_sequence,
2087 c_po_header_id,
2088 c_release_id,
2089 c_line_id,
2090 c_location_id,
2091 c_distribution_id,
2092 c_item_id,
2093 c_revision_num,
2094 c_po_num,
2095 c_line_num,
2096 c_location_num,
2097 c_distribution_num,
2098 c_level_altered,
2099 --fnd_message.get_String('PO', 'POS_RELEASE_NUMBER'),
2100 'ICX_RELEASE_NUMBER',
2101 p_release_from.release_num,
2102 p_release_to.release_num
2103 );
2104 END IF;
2105
2106 v_progress := '400';
2107
2108 /* Release date */
2109 IF NVL( p_release_from.release_date,
2110 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
2111 NVL( p_release_to.release_date,
2112 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
2113 insert_changes(
2114 p_sequence,
2115 c_po_header_id,
2116 c_release_id,
2117 c_line_id,
2118 c_location_id,
2119 c_distribution_id,
2120 c_item_id,
2121 c_po_num,
2122 c_revision_num,
2123 c_line_num,
2124 c_location_num,
2125 c_distribution_num,
2126 c_level_altered,
2127 --fnd_message.get_String('PO', 'POS_RELEASE_DATE'),
2128 'ICX_RELEASE_DATE',
2129 --start bug 4179194
2130 /* to_char(p_release_from.release_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
2131 to_char(p_release_to.release_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
2132 );
2133 */
2134 null,
2135 null,
2136 null,
2137 null,
2138 p_release_from.release_date,
2139 p_release_to.release_date
2140 );
2141 --end bug 4179194
2142 END IF;
2143
2144 EXCEPTION
2145 WHEN others THEN
2146 PO_MESSAGE_S.SQL_ERROR(
2147 'PO_COMPARE_REVISIONS.COMPARE_RELEASES',
2148 v_progress,
2149 sqlcode );
2150 RAISE;
2151
2152 END compare_releases;
2153
2154 PROCEDURE compare_price_diffs (
2155 p_pdiffs_from in po_price_differentials_archive%ROWTYPE,
2156 p_pdiffs_to in po_price_differentials_archive%ROWTYPE,
2157 p_sequence IN NUMBER
2158 ) AS
2159
2160 c_level_altered pos_po_revisions_gt.level_altered%TYPE := 'ICX_HEADER';
2161 c_po_header_id NUMBER;
2162 c_po_num po_headers_archive_all.segment1%TYPE;
2163 c_release_id NUMBER;
2164 c_revision_num NUMBER;
2165 c_line_id NUMBER := NULL;
2166 c_line_num NUMBER := NULL;
2167 c_location_id NUMBER := NULL;
2168 c_location_num NUMBER := NULL;
2169 c_distribution_id NUMBER := NULL;
2170 c_distribution_num NUMBER := NULL;
2171 c_item_id NUMBER := NULL;
2172 v_progress VARCHAR2(3);
2173
2174 v_po_num po_headers_archive_all.segment1%TYPE;
2175 c_org_name hr_all_organization_units_tl.name%TYPE;
2176 c_entity_id NUMBER;
2177 c_entity_type po_price_differentials_archive.entity_type%TYPE;
2178 c_price_diff_num po_price_differentials_archive.price_differential_num%TYPE;
2179
2180 BEGIN
2181
2182
2183
2184
2185 IF p_pdiffs_to.price_differential_id IS NULL THEN
2186 RETURN;
2187 END IF;
2188
2189
2190 c_entity_id := p_pdiffs_to.entity_id;
2191 c_entity_type := p_pdiffs_to.entity_type;
2192 c_revision_num := p_pdiffs_to.revision_num;
2193 c_price_diff_num := p_pdiffs_to.price_differential_num;
2194
2195 IF NVL( p_pdiffs_from.revision_num, -99 ) =
2196 NVL( p_pdiffs_to.revision_num, -99 ) THEN
2197 RETURN;
2198 END IF;
2199
2200 if (c_entity_type = 'PO LINE' OR c_entity_type = 'BLANKET LINE') then
2201
2202 SELECT poh.segment1,poh.po_header_id,pol.line_num
2203 INTO c_po_num,c_po_header_id,c_line_num
2204 FROM po_headers_all poh,po_lines_all pol
2205 WHERE
2206 poh.po_header_id = pol.po_header_id and
2207 pol.po_line_id = c_entity_id;
2208
2209
2210 IF nvl(p_pdiffs_from.price_type,-99) <> nvl(p_pdiffs_to.price_type,-99) THEN
2211
2212
2213 insert_changes(
2214 p_sequence,
2215 c_po_header_id,
2216 c_release_id,
2217 c_line_id,
2218 c_location_id,
2219 c_distribution_id,
2220 c_item_id,
2221 c_po_num,
2222 c_revision_num,
2223 c_line_num,
2224 c_location_num,
2225 c_distribution_num,
2226 c_level_altered,
2227 'ICX_PRICE_TYPE',
2228 get_pricediff_type(p_pdiffs_from.price_type),
2229 get_pricediff_type(p_pdiffs_to.price_type),
2230 null,
2231 c_price_diff_num
2232 );
2233 END IF;
2234
2235 IF nvl(p_pdiffs_from.multiplier,-99) <> nvl(p_pdiffs_to.multiplier,-99) THEN
2236
2237 insert_changes(
2238 p_sequence,
2239 c_po_header_id,
2240 c_release_id,
2241 c_line_id,
2242 c_location_id,
2243 c_distribution_id,
2244 c_item_id,
2245 c_po_num,
2246 c_revision_num,
2247 c_line_num,
2248 c_location_num,
2249 c_distribution_num,
2250 c_level_altered,
2251 'ICX_MULTIPLIER',
2252 to_char(p_pdiffs_from.multiplier),
2253 to_char(p_pdiffs_to.multiplier),
2254 null,
2255 c_price_diff_num
2256 );
2257 END IF;
2258 IF nvl(p_pdiffs_from.min_multiplier,-99) <> nvl(p_pdiffs_to.min_multiplier,-99) THEN
2259
2260 insert_changes(
2261 p_sequence,
2262 c_po_header_id,
2263 c_release_id,
2264 c_line_id,
2265 c_location_id,
2266 c_distribution_id,
2267 c_item_id,
2268 c_po_num,
2269 c_revision_num,
2270 c_line_num,
2271 c_location_num,
2272 c_distribution_num,
2273 c_level_altered,
2274 'ICX_MIN_MULTIPLIER',
2275 to_char(p_pdiffs_from.min_multiplier),
2276 to_char(p_pdiffs_to.min_multiplier),
2277 null,
2278 c_price_diff_num
2279 );
2280 END IF;
2281 IF nvl(p_pdiffs_from.max_multiplier,-99) <> nvl(p_pdiffs_to.max_multiplier,-99) THEN
2282
2283 insert_changes(
2284 p_sequence,
2285 c_po_header_id,
2286 c_release_id,
2287 c_line_id,
2288 c_location_id,
2289 c_distribution_id,
2290 c_item_id,
2291 c_po_num,
2292 c_revision_num,
2293 c_line_num,
2294 c_location_num,
2295 c_distribution_num,
2296 c_level_altered,
2297 'ICX_MAX_MULTIPLIER',
2298 to_char(p_pdiffs_from.max_multiplier),
2299 to_char(p_pdiffs_to.max_multiplier),
2300 null,
2301 c_price_diff_num
2302 );
2303 END IF;
2304 ELSE
2305
2306 SELECT segment1,poh.po_header_id,pll.shipment_num,pol.line_num
2307 INTO c_po_num,c_po_header_id,c_location_num,c_line_num
2308 FROM po_headers_all poh,po_line_locations_all pll,po_lines_all pol
2309 WHERE
2310 poh.po_header_id = pll.po_header_id and
2311 pll.line_location_id = c_entity_id and
2312 pol.po_line_id = pll.po_line_id;
2313
2314 IF nvl(p_pdiffs_from.price_type,-99) <> nvl(p_pdiffs_to.price_type,-99) THEN
2315 insert_changes(
2316 p_sequence,
2317 c_po_header_id,
2318 c_release_id,
2319 c_line_id,
2320 c_location_id,
2321 c_distribution_id,
2322 c_item_id,
2323 c_po_num,
2324 c_revision_num,
2325 c_line_num,
2326 c_location_num,
2327 c_distribution_num,
2328 c_level_altered,
2329 'ICX_PRICE_TYPE',
2330 p_pdiffs_from.price_type,
2331 p_pdiffs_to.price_type,
2332 null,
2333 c_price_diff_num
2334 );
2335 END IF;
2336
2337 IF nvl(p_pdiffs_from.multiplier,-99) <> nvl(p_pdiffs_to.multiplier,-99) THEN
2338 insert_changes(
2339 p_sequence,
2340 c_po_header_id,
2341 c_release_id,
2342 c_line_id,
2343 c_location_id,
2344 c_distribution_id,
2345 c_item_id,
2346 c_po_num,
2347 c_revision_num,
2348 c_line_num,
2349 c_location_num,
2350 c_distribution_num,
2351 c_level_altered,
2352 'ICX_MULTIPLIER',
2353 p_pdiffs_from.multiplier,
2354 p_pdiffs_to.multiplier,
2355 null,
2356 c_price_diff_num
2357 );
2358 END IF;
2359 IF nvl(p_pdiffs_from.min_multiplier,-99) <> nvl(p_pdiffs_to.min_multiplier,-99) THEN
2360 insert_changes(
2361 p_sequence,
2362 c_po_header_id,
2363 c_release_id,
2364 c_line_id,
2365 c_location_id,
2366 c_distribution_id,
2367 c_item_id,
2368 c_po_num,
2369 c_revision_num,
2370 c_line_num,
2371 c_location_num,
2372 c_distribution_num,
2373 c_level_altered,
2374 'ICX_MIN_MULTIPLIER',
2375 p_pdiffs_from.min_multiplier,
2376 p_pdiffs_to.min_multiplier,
2377 null,
2378 c_price_diff_num
2379 );
2380 END IF;
2381
2382 IF nvl(p_pdiffs_from.max_multiplier,-99) <> nvl(p_pdiffs_to.max_multiplier,-99) THEN
2383 insert_changes(
2384 p_sequence,
2385 c_po_header_id,
2386 c_release_id,
2387 c_line_id,
2388 c_location_id,
2389 c_distribution_id,
2390 c_item_id,
2391 c_po_num,
2392 c_revision_num,
2393 c_line_num,
2394 c_location_num,
2395 c_distribution_num,
2396 c_level_altered,
2397 'ICX_MAX_MULTIPLIER',
2398 p_pdiffs_from.max_multiplier,
2399 p_pdiffs_to.max_multiplier,
2400 null,
2401 c_price_diff_num
2402 );
2403 END IF;
2404
2405
2406 end if;
2407
2408
2409 EXCEPTION
2410 WHEN others THEN
2411 PO_MESSAGE_S.SQL_ERROR(
2412 'PO_COMPARE_REVISIONS.COMPARE_PDIFS',
2413 v_progress,
2414 sqlcode );
2415 RAISE;
2416
2417 END compare_price_diffs;
2418
2419
2420
2421 PROCEDURE compare_ga_assignments(
2422 p_ga_ass_from in po_ga_org_assignments_archive%ROWTYPE,
2423 p_ga_ass_to in po_ga_org_assignments_archive%ROWTYPE,
2424 p_sequence IN NUMBER
2425 ) AS
2426
2427 c_level_altered pos_po_revisions_gt.level_altered%TYPE
2428 := 'ICX_HEADER';
2429 c_po_header_id NUMBER;
2430 c_po_num po_headers_archive_all.segment1%TYPE;
2431 c_release_id NUMBER;
2432 c_revision_num NUMBER;
2433 c_line_id NUMBER := NULL;
2434 c_line_num NUMBER := NULL;
2435 c_location_id NUMBER := NULL;
2436 c_location_num NUMBER := NULL;
2437 c_distribution_id NUMBER := NULL;
2438 c_distribution_num NUMBER := NULL;
2439 c_item_id NUMBER := NULL;
2440 v_progress VARCHAR2(3);
2441
2442 v_po_num po_headers_archive_all.segment1%TYPE;
2443 c_org_name hr_all_organization_units_tl.name%TYPE;
2444 c_enabled_org_id NUMBER;
2445
2446 BEGIN
2447
2448
2449 IF p_ga_ass_to.organization_id IS NULL THEN
2450 RETURN;
2451 END IF;
2452
2453
2454 c_po_header_id := p_ga_ass_to.po_header_id;
2455 c_enabled_org_id := p_ga_ass_to.organization_id;
2456
2457
2458
2459 SELECT segment1
2460 INTO c_po_num
2461 FROM po_headers_archive_all
2462 WHERE
2463 po_header_id = p_ga_ass_to.po_header_id
2464 AND latest_external_flag = 'Y';
2465
2466 --bug 4946281
2467 /*
2468 select ood.organization_name
2469 into c_org_name
2470 from org_organization_definitions ood
2471 where ood.organization_id = c_enabled_org_id;
2472 */
2473 select HAOTL.NAME
2474 into c_org_name
2475 from HR_ALL_ORGANIZATION_UNITS_TL HAOTL
2476 where HAOTL.ORGANIZATION_ID = c_enabled_org_id and
2477 HAOTL.LANGUAGE = USERENV('LANG');
2478
2479
2480 c_revision_num := p_ga_ass_to.revision_num;
2481
2482
2483 IF NVL( p_ga_ass_from.revision_num, -99 ) =
2484 NVL( p_ga_ass_to.revision_num, -99 ) THEN
2485 RETURN;
2486 END IF;
2487
2488
2489
2490 IF nvl(p_ga_ass_from.purchasing_org_id,-99) <> nvl(p_ga_ass_to.purchasing_org_id,-99) THEN
2491 insert_changes(
2492 p_sequence,
2493 c_po_header_id,
2494 c_release_id,
2495 c_line_id,
2496 c_location_id,
2497 c_distribution_id,
2498 c_item_id,
2499 c_po_num,
2500 c_revision_num,
2501 c_line_num,
2502 c_location_num,
2503 c_distribution_num,
2504 c_level_altered,
2505 'ICX_PURCHASING_ORG',
2506 get_organization(p_ga_ass_from.purchasing_org_id),
2507 get_organization(p_ga_ass_to.purchasing_org_id),
2508 c_org_name
2509 );
2510 END IF;
2511
2512
2513 IF nvl(p_ga_ass_from.vendor_site_id,-99) <> nvl(p_ga_ass_to.vendor_site_id,-99) THEN
2514 insert_changes(
2515 p_sequence,
2516 c_po_header_id,
2517 c_release_id,
2518 c_line_id,
2519 c_location_id,
2520 c_distribution_id,
2521 c_item_id,
2522 c_po_num,
2523 c_revision_num,
2524 c_line_num,
2525 c_location_num,
2526 c_distribution_num,
2527 c_level_altered,
2528 'ICX_ENABLED_VENDOR_SITE',
2529 get_vendor_site(p_ga_ass_from.vendor_site_id),
2530 get_vendor_site(p_ga_ass_to.vendor_site_id),
2531 c_org_name
2532 );
2533 END IF;
2534
2535
2536 IF nvl(p_ga_ass_from.enabled_flag,-99) <> nvl(p_ga_ass_to.enabled_flag,-99) THEN
2537 insert_changes(
2538 p_sequence,
2539 c_po_header_id,
2540 c_release_id,
2541 c_line_id,
2542 c_location_id,
2543 c_distribution_id,
2544 c_item_id,
2545 c_po_num,
2546 c_revision_num,
2547 c_line_num,
2548 c_location_num,
2549 c_distribution_num,
2550 c_level_altered,
2551 'ICX_ENABLED_FLAG',
2552 p_ga_ass_from.enabled_flag,
2553 p_ga_ass_to.enabled_flag,
2554 c_org_name
2555 );
2556
2557 END IF;
2558
2559
2560 EXCEPTION
2561 WHEN others THEN
2562 PO_MESSAGE_S.SQL_ERROR(
2563 'PO_COMPARE_REVISIONS.COMPARE_GA_ORG_ASSIGNMENTS',
2564 v_progress,
2565 sqlcode );
2566 RAISE;
2567
2568 END compare_ga_assignments;
2569
2570
2571
2572
2573 /*********************************************************************
2574 * NAME
2575 * compare_lines
2576 *
2577 * PURPOSE
2578 * Accepts two records of the same lines with different revisions,
2579 * compare the data in both lines, and stores the differences in a
2580 * temporary table.
2581 *
2582 * ARGUMENTS
2583 * p_line_from Old version of the PO.
2584 * p_line_to New version of the PO.
2585 * p_sequence Sequence number to use in the temp table to identify
2586 * the records for delta.
2587 *
2588 * NOTES
2589 * The comparison is not done on all fields, but only the ones than
2590 * cause a revision change, according to Oracle Purchasing Reference
2591 * Manual.
2592 *
2593 * The fields that can be changed on PO header, and cause a revision
2594 * number increase are:
2595 *
2596 * Cancel Flag
2597 * Unit price
2598 * Line number
2599 * Item
2600 * Item revision
2601 * Item description
2602 * Quantity
2603 * UOM
2604 * Source quotation header
2605 * Source quotation line
2606 * Hazard class
2607 * Contract number
2608 * Supplier item number
2609 * Note to vendor
2610 * UN number
2611 * Price type
2612 * Quantity Agreed
2613 * Amount Agreed
2614 * Closed Code
2615 * Service Type Lines for FPJ
2616 * Job Title
2617 * Contractor First Name
2618 * Contractor Last Name
2619 * Start Date
2620 * End Date
2621 * Amount
2622 *
2623 * HISTORY
2624 * 08-AUG-97 Nilo Paredes Created.
2625 * 22-SEP-97 Rami Haddad Added comparison for buyer, source
2626 * quotation header, source quotation
2627 * line, supplier item number, quantity
2628 * agreed, and amount agreed.
2629 * 08-SEP-97 Rami Haddad Compare closed code
2630 ********************************************************************/
2631 PROCEDURE compare_lines(
2632 p_line_from in po_lines_archive_all%ROWTYPE,
2633 p_line_to in po_lines_archive_all%ROWTYPE,
2634 p_sequence IN NUMBER
2635 ) AS
2636
2637 /*
2638 * Constant variables to pass for insert_changes
2639 */
2640 c_level_altered pos_po_revisions_gt.level_altered%TYPE
2641 := 'ICX_LINE';
2642 c_po_header_id NUMBER;
2643 c_release_id NUMBER := NULL;
2644 c_po_num po_headers_archive_all.segment1%TYPE;
2645 c_line_id NUMBER;
2646 c_line_num NUMBER;
2647 c_revision_num NUMBER;
2648 c_location_id NUMBER := NULL;
2649 c_location_num NUMBER := NULL;
2650 c_distribution_id NUMBER := NULL;
2651 c_distribution_num NUMBER := NULL;
2652 c_item_id NUMBER := NULL;
2653
2654 c_job_id NUMBER := NULL;
2655 c_org_id NUMBER := NULL;
2656 c_item mtl_system_items_kfv.concatenated_segments%TYPE;
2657 c_job per_jobs.name%TYPE;
2658
2659 v_progress VARCHAR2(3);
2660
2661 BEGIN
2662
2663 /*
2664 * At least the latest revision should exist.
2665 */
2666 IF p_line_to.po_header_id IS NULL THEN
2667 RETURN;
2668 END IF;
2669
2670 /*
2671 * Set values for all constants
2672 */
2673 c_po_header_id := p_line_to.po_header_id;
2674 c_line_id := p_line_to.po_line_id;
2675 c_revision_num := p_line_to.revision_num;
2676 c_line_num := p_line_to.line_num;
2677 c_item_id := p_line_to.item_id;
2678 c_job_id := p_line_to.job_id;
2679 c_org_id := p_line_to.org_id;
2680
2681 v_progress := '410';
2682
2683 if (c_item_id is not null) then
2684 c_item := get_item(c_item_id, c_org_id);
2685 end if;
2686
2687 if (c_job_id is not null) then
2688 c_job := get_job(c_job_id);
2689 end if;
2690
2691 v_progress := '415';
2692
2693 /*
2694 * If the old record does not exist, then this is a new one.
2695 */
2696 IF p_line_from.po_header_id IS NULL THEN
2697 insert_changes(
2698 p_sequence,
2699 c_po_header_id,
2700 c_release_id,
2701 c_line_id,
2702 c_location_id,
2703 c_distribution_id,
2704 c_item_id,
2705 c_po_num,
2706 c_revision_num,
2707 c_line_num,
2708 c_location_num,
2709 c_distribution_num,
2710 c_level_altered,
2711 'ICX_NEW',
2712 NULL,
2713 NULL,
2714 NULL,
2715 NULL,
2716 NULL,
2717 NULL,
2718 c_item,
2719 c_job
2720 );
2721 RETURN;
2722 END IF;
2723
2724 /*
2725 * Are the lines the same?
2726 */
2727 IF NVL( p_line_from.po_line_id, -99 ) <>
2728 NVL( p_line_to.po_line_id, -99 ) THEN
2729 RETURN;
2730 END IF;
2731
2732 /*
2733 * Do not compare lines of the same revision number.
2734 */
2735
2736 IF NVL( p_line_from.revision_num, -99 ) =
2737 NVL( p_line_to.revision_num, -99 ) THEN
2738 RETURN;
2739 END IF;
2740
2741 v_progress := '420';
2742
2743 /*
2744 * If current line is cancelled, then check if the prior one
2745 * is cancelled as well. If it is, then there is no
2746 * change. Otherwise, the line is cancelled for the current
2747 * revision.
2748 */
2749 IF p_line_to.cancel_flag = 'Y' THEN
2750 IF p_line_from.cancel_flag ='Y'
2751 THEN
2752 RETURN;
2753 ELSE
2754 insert_changes(
2755 p_sequence,
2756 c_po_header_id,
2757 c_release_id,
2758 c_line_id,
2759 c_location_id,
2760 c_distribution_id,
2761 c_item_id,
2762 c_po_num,
2763 c_revision_num,
2764 c_line_num,
2765 c_location_num,
2766 c_distribution_num,
2767 c_level_altered,
2768 --fnd_message.get_string('PO', 'POS_CANCELLED'),
2769 'ICX_CANCELLED',
2770 NULL,
2771 NULL,
2772 NULL,
2773 NULL,
2774 NULL,
2775 NULL,
2776 c_item,
2777 c_job
2778 );
2779 RETURN;
2780 END IF;
2781 END IF;
2782
2783 /*
2784 * Line not cancelled in current PO. Compare all fields with
2785 * line in prior revision.
2786 */
2787
2788 v_progress := '430';
2789
2790 /* Unit price */
2791 IF NVL( p_line_from.unit_price, -99 ) <>
2792 NVL( p_line_to.unit_price, -99 ) THEN
2793 insert_changes(
2794 p_sequence,
2795 c_po_header_id,
2796 c_release_id,
2797 c_line_id,
2798 c_location_id,
2799 c_distribution_id,
2800 c_item_id,
2801 c_po_num,
2802 c_revision_num,
2803 c_line_num,
2804 c_location_num,
2805 c_distribution_num,
2806 c_level_altered,
2807 --fnd_message.get_String('PO', 'POS_UNIT_PRICE'),
2808 'ICX_UNIT_PRICE',
2809 p_line_from.unit_price,
2810 p_line_to.unit_price,
2811 NULL,
2812 NULL,
2813 NULL,
2814 NULL,
2815 c_item,
2816 c_job
2817 );
2818 END IF;
2819
2820 v_progress := '440';
2821
2822 /* Line number */
2823 IF NVL( p_line_from.line_num, -99 ) <>
2824 NVL( p_line_to.line_num, -99 ) THEN
2825 insert_changes(
2826 p_sequence,
2827 c_po_header_id,
2828 c_release_id,
2829 c_line_id,
2830 c_location_id,
2831 c_distribution_id,
2832 c_item_id,
2833 c_po_num,
2834 c_revision_num,
2835 c_line_num,
2836 c_location_num,
2837 c_distribution_num,
2838 c_level_altered,
2839 --fnd_message.get_String('PO', 'POS_LINE_NUMBER'),
2840 'ICX_LINE_NUMBER',
2841 p_line_from.line_num,
2842 p_line_to.line_num,
2843 NULL,
2844 NULL,
2845 NULL,
2846 NULL,
2847 c_item,
2848 c_job
2849 );
2850 END IF;
2851
2852 v_progress := '450';
2853
2854 /* Item */
2855 IF NVL( p_line_from.item_id, -99 ) <> NVL( p_line_to.item_id, -99 )
2856 THEN
2857 insert_changes(
2858 p_sequence,
2859 c_po_header_id,
2860 c_release_id,
2861 c_line_id,
2862 c_location_id,
2863 c_distribution_id,
2864 c_item_id,
2865 c_po_num,
2866 c_revision_num,
2867 c_line_num,
2868 c_location_num,
2869 c_distribution_num,
2870 c_level_altered,
2871 --fnd_message.get_String('PO', 'POS_ITEM'),
2872 'ICX_ITEM',
2873 -- get_item_number( p_line_from.item_id ),
2874 -- get_item_number( p_line_to.item_id ),
2875 get_item( p_line_from.item_id, p_line_from.org_id ),
2876 get_item( p_line_to.item_id, p_line_to.org_id ),
2877 NULL,
2878 NULL,
2879 NULL,
2880 NULL,
2881 c_item,
2882 c_job
2883 );
2884 END IF;
2885
2886 v_progress := '455';
2887
2888 /* Job */
2889 IF NVL( p_line_from.job_id, -99 ) <> NVL( p_line_to.job_id, -99 )
2890 THEN
2891 insert_changes(
2892 p_sequence,
2893 c_po_header_id,
2894 c_release_id,
2895 c_line_id,
2896 c_location_id,
2897 c_distribution_id,
2898 c_item_id,
2899 c_po_num,
2900 c_revision_num,
2901 c_line_num,
2902 c_location_num,
2903 c_distribution_num,
2904 c_level_altered,
2905 --fnd_message.get_String('PO', 'POS_ITEM'),
2906 'ICX_JOB',
2907 get_job( p_line_from.job_id),
2908 get_job( p_line_to.job_id),
2909 NULL,
2910 NULL,
2911 NULL,
2912 NULL,
2913 c_item,
2914 c_job
2915 );
2916 END IF;
2917
2918 v_progress := '458';
2919
2920 /* Shopping category */
2921 IF NVL( p_line_from.ip_category_id, -99 ) <> NVL( p_line_to.ip_category_id, -99 )
2922 THEN
2923 insert_changes(
2924 p_sequence,
2925 c_po_header_id,
2926 c_release_id,
2927 c_line_id,
2928 c_location_id,
2929 c_distribution_id,
2930 c_item_id,
2931 c_po_num,
2932 c_revision_num,
2933 c_line_num,
2934 c_location_num,
2935 c_distribution_num,
2936 c_level_altered,
2937 'ICX_CATEGORY',
2938 get_shopping_category(p_line_from.ip_category_id),
2939 get_shopping_category(p_line_to.ip_category_id),
2940 NULL,
2941 NULL,
2942 NULL,
2943 NULL,
2944 c_item,
2945 c_job
2946 );
2947 END IF;
2948
2949 v_progress := '460';
2950
2951 /* Item revision */
2952 IF NVL( p_line_from.item_revision, ' ' ) <>
2953 NVL( p_line_to.item_revision, ' ' ) THEN
2954 insert_changes(
2955 p_sequence,
2956 c_po_header_id,
2957 c_release_id,
2958 c_line_id,
2959 c_location_id,
2960 c_distribution_id,
2961 c_item_id,
2962 c_po_num,
2963 c_revision_num,
2964 c_line_num,
2965 c_location_num,
2966 c_distribution_num,
2967 c_level_altered,
2968 --fnd_message.get_String('PO', 'POS_ITEM_REVISION'),
2969 'ICX_ITEM_REVISION',
2970 p_line_from.item_revision,
2971 p_line_to.item_revision,
2972 NULL,
2973 NULL,
2974 NULL,
2975 NULL,
2976 c_item,
2977 c_job
2978 );
2979 END IF;
2980
2981 v_progress := '470';
2982
2983 /* Item description */
2984 IF NVL( p_line_from.item_description, ' ' ) <>
2985 NVL( p_line_to.item_description, ' ' ) THEN
2986 insert_changes(
2987 p_sequence,
2988 c_po_header_id,
2989 c_release_id,
2990 c_line_id,
2991 c_location_id,
2992 c_distribution_id,
2993 c_item_id,
2994 c_po_num,
2995 c_revision_num,
2996 c_line_num,
2997 c_location_num,
2998 c_distribution_num,
2999 c_level_altered,
3000 --fnd_message.get_String('PO', 'POS_ITEM_DESCRIPTION'),
3001 'ICX_ITEM_DESCRIPTION',
3002 p_line_from.item_description,
3003 p_line_to.item_description,
3004 NULL,
3005 NULL,
3006 NULL,
3007 NULL,
3008 c_item,
3009 c_job
3010 );
3011 END IF;
3012
3013 v_progress := '480';
3014
3015 /* Quantity */
3016 IF NVL( p_line_from.quantity, -99 ) <>
3017 NVL( p_line_to.quantity, -99 ) THEN
3018 insert_changes(
3019 p_sequence,
3020 c_po_header_id,
3021 c_release_id,
3022 c_line_id,
3023 c_location_id,
3024 c_distribution_id,
3025 c_item_id,
3026 c_po_num,
3027 c_revision_num,
3028 c_line_num,
3029 c_location_num,
3030 c_distribution_num,
3031 c_level_altered,
3032 --fnd_message.get_String('PO', 'POS_QUANTITY'),
3033 'ICX_QUANTITY',
3034 p_line_from.quantity,
3035 p_line_to.quantity,
3036 NULL,
3037 NULL,
3038 NULL,
3039 NULL,
3040 c_item,
3041 c_job
3042 );
3043 END IF;
3044
3045 v_progress := '490';
3046
3047 /* UOM */
3048 IF NVL( p_line_from.unit_meas_lookup_code, ' ' ) <>
3049 NVL( p_line_to.unit_meas_lookup_code, ' ' ) THEN
3050 insert_changes(
3051 p_sequence,
3052 c_po_header_id,
3053 c_release_id,
3054 c_line_id,
3055 c_location_id,
3056 c_distribution_id,
3057 c_item_id,
3058 c_po_num,
3059 c_revision_num,
3060 c_line_num,
3061 c_location_num,
3062 c_distribution_num,
3063 c_level_altered,
3064 --fnd_message.get_String('PO', 'POS_UOM'),
3065 'ICX_UOM',
3066 p_line_from.unit_meas_lookup_code,
3067 p_line_to.unit_meas_lookup_code,
3068 NULL,
3069 NULL,
3070 NULL,
3071 NULL,
3072 c_item,
3073 c_job
3074 );
3075 END IF;
3076
3077 v_progress := '500';
3078
3079 /* Source quotation header */
3080 IF NVL( p_line_from.from_header_id, -99 ) <>
3081 NVL( p_line_to.from_header_id, -99 ) THEN
3082 insert_changes(
3083 p_sequence,
3084 c_po_header_id,
3085 c_release_id,
3086 c_line_id,
3087 c_location_id,
3088 c_distribution_id,
3089 c_item_id,
3090 c_po_num,
3091 c_revision_num,
3092 c_line_num,
3093 c_location_num,
3094 c_distribution_num,
3095 c_level_altered,
3096 --fnd_message.get_String('PO', 'POS_SOURCE_QT_HEADER'),
3097 'ICX_SOURCE_QT_HEADER',
3098 get_source_quotation_header(
3099 p_line_from.from_header_id ),
3100 get_source_quotation_header(
3101 p_line_to.from_header_id ),
3102 NULL,
3103 NULL,
3104 NULL,
3105 NULL,
3106 c_item,
3107 c_job
3108 );
3109 END IF;
3110
3111 v_progress := '510';
3112
3113 /* Source quotation line */
3114 IF NVL( p_line_from.from_line_id, -99 ) <>
3115 NVL( p_line_to.from_line_id, -99 ) THEN
3116 insert_changes(
3117 p_sequence,
3118 c_po_header_id,
3119 c_release_id,
3120 c_line_id,
3121 c_location_id,
3122 c_distribution_id,
3123 c_item_id,
3124 c_po_num,
3125 c_revision_num,
3126 c_line_num,
3127 c_location_num,
3128 c_distribution_num,
3129 c_level_altered,
3130 --fnd_message.get_String('PO', 'POS_SOURCE_QT_LINE'),
3131 'ICX_SOURCE_QT_LINE',
3132 get_source_quotation_line(
3133 p_line_from.from_line_id ),
3134 get_source_quotation_line(
3135 p_line_to.from_line_id ),
3136 NULL,
3137 NULL,
3138 NULL,
3139 NULL,
3140 c_item,
3141 c_job
3142 );
3143 END IF;
3144
3145 v_progress := '520';
3146
3147 /* Hazard class */
3148 IF NVL( p_line_from.hazard_class_id, -99 ) <>
3149 NVL( p_line_to.hazard_class_id, -99 ) THEN
3150 insert_changes(
3151 p_sequence,
3152 c_po_header_id,
3153 c_release_id,
3154 c_line_id,
3155 c_location_id,
3156 c_distribution_id,
3157 c_item_id,
3158 c_po_num,
3159 c_revision_num,
3160 c_line_num,
3161 c_location_num,
3162 c_distribution_num,
3163 c_level_altered,
3164 --fnd_message.get_String('PO', 'POS_HAZARD_CLASS'),
3165 'ICX_HAZARD_CLASS',
3166 get_hazard_class( p_line_from.hazard_class_id ),
3167 get_hazard_class( p_line_to.hazard_class_id ),
3168 NULL,
3169 NULL,
3170 NULL,
3171 NULL,
3172 c_item,
3173 c_job
3174 );
3175 END IF;
3176
3177 v_progress := '530';
3178
3179 /* Contract number */
3180 IF NVL( p_line_from.contract_num, ' ' ) <>
3181 NVL( p_line_to.contract_num, ' ' ) THEN
3182 insert_changes(
3183 p_sequence,
3184 c_po_header_id,
3185 c_release_id,
3186 c_line_id,
3187 c_location_id,
3188 c_distribution_id,
3189 c_item_id,
3190 c_po_num,
3191 c_revision_num,
3192 c_line_num,
3193 c_location_num,
3194 c_distribution_num,
3195 c_level_altered,
3196 --fnd_message.get_String('PO', 'POS_CONTRACT_NUMBER'),
3197 'ICX_CONTRACT_NUMBER',
3198 p_line_from.contract_num,
3199 p_line_to.contract_num,
3200 NULL,
3201 NULL,
3202 NULL,
3203 NULL,
3204 c_item,
3205 c_job
3206 );
3207 END IF;
3208
3209 v_progress := '540';
3210
3211 /* Supplie item number */
3212 IF NVL( p_line_from.vendor_product_num, ' ' ) <>
3213 NVL( p_line_to.vendor_product_num, ' ' ) THEN
3214 insert_changes(
3215 p_sequence,
3216 c_po_header_id,
3217 c_release_id,
3218 c_line_id,
3219 c_location_id,
3220 c_distribution_id,
3221 c_item_id,
3222 c_po_num,
3223 c_revision_num,
3224 c_line_num,
3225 c_location_num,
3226 c_distribution_num,
3227 c_level_altered,
3228 --fnd_message.get_String('PO', 'POS_SUPPLIER_ITEM_NUM'),
3229 'ICX_SUPPLIER_ITEM_NUM',
3230 p_line_from.vendor_product_num,
3231 p_line_to.vendor_product_num,
3232 NULL,
3233 NULL,
3234 NULL,
3235 NULL,
3236 c_item,
3237 c_job
3238 );
3239 END IF;
3240
3241 v_progress := '550';
3242
3243 /* Note to vendor */
3244 IF NVL( p_line_from.note_to_vendor, ' ' ) <>
3245 NVL( p_line_to.note_to_vendor, ' ' ) THEN
3246 insert_changes(
3247 p_sequence,
3248 c_po_header_id,
3249 c_release_id,
3250 c_line_id,
3251 c_location_id,
3252 c_distribution_id,
3253 c_item_id,
3254 c_po_num,
3255 c_revision_num,
3256 c_line_num,
3257 c_location_num,
3258 c_distribution_num,
3259 c_level_altered,
3260 --fnd_message.get_String('PO', 'POS_NOTE_TO_VENDOR'),
3261 'ICX_NOTE_TO_VENDOR',
3262 p_line_from.note_to_vendor,
3263 p_line_to.note_to_vendor,
3264 NULL,
3265 NULL,
3266 NULL,
3267 NULL,
3268 c_item,
3269 c_job
3270 );
3271 END IF;
3272
3273 v_progress := '560';
3274
3275 /* UN number */
3276 IF NVL( p_line_from.un_number_id, -99 ) <>
3277 NVL( p_line_to.un_number_id, -99 ) THEN
3278 insert_changes(
3279 p_sequence,
3280 c_po_header_id,
3281 c_release_id,
3282 c_line_id,
3283 c_location_id,
3284 c_distribution_id,
3285 c_item_id,
3286 c_po_num,
3287 c_revision_num,
3288 c_line_num,
3289 c_location_num,
3290 c_distribution_num,
3291 c_level_altered,
3292 --fnd_message.get_String('PO', 'POS_UN_NUMBER'),
3293 'ICX_UN_NUMBER',
3294 get_un_number( p_line_from.un_number_id ),
3295 get_un_number( p_line_to.un_number_id ),
3296 NULL,
3297 NULL,
3298 NULL,
3299 NULL,
3300 c_item,
3301 c_job
3302 );
3303 END IF;
3304
3305 v_progress := '570';
3306
3307 /* Price type */
3308 IF NVL( p_line_from.price_type_lookup_code, ' ' ) <>
3309 NVL( p_line_to.price_type_lookup_code, ' ' ) THEN
3310 insert_changes(
3311 p_sequence,
3312 c_po_header_id,
3313 c_release_id,
3314 c_line_id,
3315 c_location_id,
3316 c_distribution_id,
3317 c_item_id,
3318 c_po_num,
3319 c_revision_num,
3320 c_line_num,
3321 c_location_num,
3322 c_distribution_num,
3323 c_level_altered,
3324 --fnd_message.get_String('PO', 'POS_PRICE_TYPE'),
3325 'ICX_PRICE_TYPE',
3326 get_po_lookup(
3327 'PRICE TYPE',
3328 p_line_from.price_type_lookup_code ),
3329 get_po_lookup(
3330 'PRICE TYPE',
3331 p_line_to.price_type_lookup_code ),
3332 NULL,
3333 NULL,
3334 NULL,
3335 NULL,
3336 c_item,
3337 c_job
3338 );
3339 END IF;
3340
3341 v_progress := '580';
3342
3343 /* Quantity agreed */
3344 /*Bug 1461326
3345 Quantity_commited is a number field and to handle nulls we
3346 were incorrectly using the following
3347 NVL( p_line_from.quantity_committed, ' ' )
3348 NVL( p_line_to.quantity_committed, ' ' )
3349 Code replaced to the following and resolved the issue
3350 NVL( p_line_from.quantity_committed,-99)
3351 NVL( p_line_to.quantity_committed,-99)
3352 */
3353
3354 IF NVL( p_line_from.quantity_committed, -99 ) <>
3355 NVL( p_line_to.quantity_committed,-99 ) THEN
3356 insert_changes(
3357 p_sequence,
3358 c_po_header_id,
3359 c_release_id,
3360 c_line_id,
3361 c_location_id,
3362 c_distribution_id,
3363 c_item_id,
3364 c_po_num,
3365 c_revision_num,
3366 c_line_num,
3367 c_location_num,
3368 c_distribution_num,
3369 c_level_altered,
3370 --fnd_message.get_String('PO', 'POS_QUANTITY_AGREED'),
3371 'ICX_QUANTITY_AGREED',
3372 p_line_from.quantity_committed,
3373 p_line_to.quantity_committed,
3374 NULL,
3375 NULL,
3376 NULL,
3377 NULL,
3378 c_item,
3379 c_job
3380 );
3381 END IF;
3382
3383 v_progress := '590';
3384
3385 /* Amount agreed */
3386 /*Bug 1461326
3387 Comitted_amount is a number field and to handle nulls we
3388 were incorrectly using the following
3389 NVL( p_line_from.committed_amount, ' ' )
3390 NVL( p_line_to.committed_amount, ' ' )
3391 Code replaced to the following and resolved the issue
3392 NVL( p_line_from.committed_amount,-99)
3393 NVL( p_line_to.committed_amount,-99)
3394 */
3395 IF NVL( p_line_from.committed_amount, -99 ) <>
3396 NVL( p_line_to.committed_amount, -99 ) THEN
3397 insert_changes(
3398 p_sequence,
3399 c_po_header_id,
3400 c_release_id,
3401 c_line_id,
3402 c_location_id,
3403 c_distribution_id,
3404 c_item_id,
3405 c_po_num,
3406 c_revision_num,
3407 c_line_num,
3408 c_location_num,
3409 c_distribution_num,
3410 c_level_altered,
3411 --fnd_message.get_String('PO', 'POS_AMOUNT_AGREED'),
3412 'ICX_AMOUNT_AGREED',
3413 p_line_from.committed_amount,
3414 p_line_to.committed_amount,
3415 NULL,
3416 NULL,
3417 NULL,
3418 NULL,
3419 c_item,
3420 c_job
3421 );
3422 END IF;
3423
3424 v_progress := '600';
3425
3426 /* Closed code */
3427 IF NVL( p_line_from.closed_code, ' ' ) <>
3428 NVL( p_line_to.closed_code , ' ' ) THEN
3429 insert_changes(
3430 p_sequence,
3431 c_po_header_id,
3432 c_release_id,
3433 c_line_id,
3434 c_location_id,
3435 c_distribution_id,
3436 c_item_id,
3437 c_po_num,
3438 c_revision_num,
3439 c_line_num,
3440 c_location_num,
3441 c_distribution_num,
3442 c_level_altered,
3443 --fnd_message.get_String('PO', 'POS_CLOSED_CODE'),
3444 'ICX_CLOSED_CODE',
3445 get_po_lookup( 'DOCUMENT STATE',
3446 p_line_from.committed_amount ),
3447 get_po_lookup( 'DOCUMENT STATE',
3448 p_line_to.committed_amount ),
3449 NULL,
3450 NULL,
3451 NULL,
3452 NULL,
3453 c_item,
3454 c_job
3455 );
3456 END IF;
3457
3458 v_progress := '610';
3459
3460 IF NVL( p_line_from.CONTRACTOR_FIRST_NAME,'') <>
3461 NVL( p_line_to.CONTRACTOR_FIRST_NAME,'') THEN
3462 insert_changes(
3463 p_sequence,
3464 c_po_header_id,
3465 c_release_id,
3466 c_line_id,
3467 c_location_id,
3468 c_distribution_id,
3469 c_item_id,
3470 c_po_num,
3471 c_revision_num,
3472 c_line_num,
3473 c_location_num,
3474 c_distribution_num,
3475 c_level_altered,
3476 'ICX_CONTRATOR_FIRST_NAME',
3477 p_line_from.CONTRACTOR_FIRST_NAME,
3478 p_line_to.CONTRACTOR_FIRST_NAME,
3479 NULL,
3480 NULL,
3481 NULL,
3482 NULL,
3483 c_item,
3484 c_job
3485 );
3486 END IF;
3487
3488 v_progress := '620';
3489 IF NVL( p_line_from.CONTRACTOR_LAST_NAME,'') <>
3490 NVL( p_line_to.CONTRACTOR_LAST_NAME,'') THEN
3491 insert_changes(
3492 p_sequence,
3493 c_po_header_id,
3494 c_release_id,
3495 c_line_id,
3496 c_location_id,
3497 c_distribution_id,
3498 c_item_id,
3499 c_po_num,
3500 c_revision_num,
3501 c_line_num,
3502 c_location_num,
3503 c_distribution_num,
3504 c_level_altered,
3505 'ICX_CONTRATOR_LAST_NAME',
3506 p_line_from.CONTRACTOR_LAST_NAME,
3507 p_line_to.CONTRACTOR_LAST_NAME,
3508 NULL,
3509 NULL,
3510 NULL,
3511 NULL,
3512 c_item,
3513 c_job
3514 );
3515 END IF;
3516
3517 v_progress := '630';
3518 IF NVL( p_line_from.start_date,
3519 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
3520 NVL( p_line_to.start_date,
3521 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
3522 insert_changes(
3523 p_sequence,
3524 c_po_header_id,
3525 c_release_id,
3526 c_line_id,
3527 c_location_id,
3528 c_distribution_id,
3529 c_item_id,
3530 c_po_num,
3531 c_revision_num,
3532 c_line_num,
3533 c_location_num,
3534 c_distribution_num,
3535 c_level_altered,
3536 'ICX_START_DATE',
3537 --start bug 4179194
3538 /* to_char(p_line_from.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
3539 to_char(p_line_to.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
3540 );
3541 */
3542 null,
3543 null,
3544 null,
3545 null,
3546 p_line_from.start_date,
3547 p_line_to.start_date,
3548 c_item,
3549 c_job
3550 );
3551 --end bug 4179194
3552 END IF;
3553
3554 v_progress := '640';
3555 /* Bug - 1260356 - Need to show archived changes for Line Level Expiration date */
3556 IF NVL( p_line_from.expiration_date,
3557 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
3558 NVL( p_line_to.expiration_date,
3559 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
3560 insert_changes(
3561 p_sequence,
3562 c_po_header_id,
3563 c_release_id,
3564 c_line_id,
3565 c_location_id,
3566 c_distribution_id,
3567 c_item_id,
3568 c_po_num,
3569 c_revision_num,
3570 c_line_num,
3571 c_location_num,
3572 c_distribution_num,
3573 c_level_altered,
3574 --fnd_message.get_String('PO', 'POS_EXPIRATION_DATE'),
3575 'ICX_EXPIRATION_DATE',
3576 --start bug 4179194
3577 /* to_char(p_line_from.expiration_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
3578 to_char(p_line_to.expiration_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
3579 );
3580 */
3581 null,
3582 null,
3583 null,
3584 null,
3585 p_line_from.expiration_date,
3586 p_line_to.expiration_date,
3587 c_item,
3588 c_job
3589 );
3590 --end bug 4179194
3591 END IF;
3592
3593 v_progress := '650';
3594 /* Compare Amount field on lines for fixed price services*/
3595 IF NVL( p_line_from.amount, -99 ) <>
3596 NVL( p_line_to.amount, -99 ) THEN
3597 insert_changes(
3598 p_sequence,
3599 c_po_header_id,
3600 c_release_id,
3601 c_line_id,
3602 c_location_id,
3603 c_distribution_id,
3604 c_item_id,
3605 c_po_num,
3606 c_revision_num,
3607 c_line_num,
3608 c_location_num,
3609 c_distribution_num,
3610 c_level_altered,
3611 -- start fix for bug 5388428 - this takes back changes we made for bug 5167605
3612 'ICX_UNIT_PRICE',
3613 -- 'ICX_AMOUNT',
3614 -- end fix
3615 p_line_from.amount,
3616 p_line_to.amount,
3617 NULL,
3618 NULL,
3619 NULL,
3620 NULL,
3621 c_item,
3622 c_job
3623 );
3624 END IF;
3625
3626 IF NVL( p_line_from.not_to_exceed_price, -99 ) <>
3627 NVL( p_line_to.not_to_exceed_price, -99 ) THEN
3628 insert_changes(
3629 p_sequence,
3630 c_po_header_id,
3631 c_release_id,
3632 c_line_id,
3633 c_location_id,
3634 c_distribution_id,
3635 c_item_id,
3636 c_po_num,
3637 c_revision_num,
3638 c_line_num,
3639 c_location_num,
3640 c_distribution_num,
3641 c_level_altered,
3642 'POS_UNIT_PRICE_LIMIT',
3643 p_line_from.not_to_exceed_price,
3644 p_line_to.not_to_exceed_price,
3645 NULL,
3646 NULL,
3647 NULL,
3648 NULL,
3649 c_item,
3650 c_job
3651 );
3652 END IF;
3653
3654
3655 --start complex work ECO 5106340
3656
3657 IF NVL( p_line_from.RETAINAGE_RATE, -99 ) <>
3658 NVL( p_line_to.RETAINAGE_RATE, -99 ) THEN
3659 insert_changes(
3660 p_sequence,
3661 c_po_header_id,
3662 c_release_id,
3663 c_line_id,
3664 c_location_id,
3665 c_distribution_id,
3666 c_item_id,
3667 c_po_num,
3668 c_revision_num,
3669 c_line_num,
3670 c_location_num,
3671 c_distribution_num,
3672 c_level_altered,
3673 'ICX_RETAINAGE_RATE',
3674 p_line_from.RETAINAGE_RATE,
3675 p_line_to.RETAINAGE_RATE,
3676 NULL,
3677 NULL,
3678 NULL,
3679 NULL,
3680 c_item,
3681 c_job
3682 );
3683 END IF;
3684
3685 IF NVL( p_line_from.MAX_RETAINAGE_AMOUNT, -99 ) <>
3686 NVL( p_line_to.MAX_RETAINAGE_AMOUNT, -99 ) THEN
3687 insert_changes(
3688 p_sequence,
3689 c_po_header_id,
3690 c_release_id,
3691 c_line_id,
3692 c_location_id,
3693 c_distribution_id,
3694 c_item_id,
3695 c_po_num,
3696 c_revision_num,
3697 c_line_num,
3698 c_location_num,
3699 c_distribution_num,
3700 c_level_altered,
3701 'ICX_MAX_RETAINAGE_AMOUNT',
3702 p_line_from.MAX_RETAINAGE_AMOUNT,
3703 p_line_to.MAX_RETAINAGE_AMOUNT,
3704 NULL,
3705 NULL,
3706 NULL,
3707 NULL,
3708 c_item,
3709 c_job
3710 );
3711 END IF;
3712
3713 IF NVL( p_line_from.PROGRESS_PAYMENT_RATE, -99 ) <>
3714 NVL( p_line_to.PROGRESS_PAYMENT_RATE, -99 ) THEN
3715 insert_changes(
3716 p_sequence,
3717 c_po_header_id,
3718 c_release_id,
3719 c_line_id,
3720 c_location_id,
3721 c_distribution_id,
3722 c_item_id,
3723 c_po_num,
3724 c_revision_num,
3725 c_line_num,
3726 c_location_num,
3727 c_distribution_num,
3728 c_level_altered,
3729 'ICX_PROGRESS_PAYMENT_RATE',
3730 p_line_from.PROGRESS_PAYMENT_RATE,
3731 p_line_to.PROGRESS_PAYMENT_RATE,
3732 NULL,
3733 NULL,
3734 NULL,
3735 NULL,
3736 c_item,
3737 c_job
3738 );
3739 END IF;
3740
3741 IF NVL( p_line_from.RECOUPMENT_RATE, -99 ) <>
3742 NVL( p_line_to.RECOUPMENT_RATE, -99 ) THEN
3743 insert_changes(
3744 p_sequence,
3745 c_po_header_id,
3746 c_release_id,
3747 c_line_id,
3748 c_location_id,
3749 c_distribution_id,
3750 c_item_id,
3751 c_po_num,
3752 c_revision_num,
3753 c_line_num,
3754 c_location_num,
3755 c_distribution_num,
3756 c_level_altered,
3757 'ICX_RECOUPMENT_RATE',
3758 p_line_from.RECOUPMENT_RATE,
3759 p_line_to.RECOUPMENT_RATE,
3760 NULL,
3761 NULL,
3762 NULL,
3763 NULL,
3764 c_item,
3765 c_job
3766 );
3767 END IF;
3768
3769
3770 /* Doing this comparison at the shipment level and inserting change as change in line
3771 search for ADVAMT to look for code block replacing this */
3772 /*
3773 --start advance amount
3774 IF ( Get_Line_adv_Amount_revision(p_line_from.po_line_id, p_line_from.revision_num ) <>
3775 Get_Line_adv_Amount_revision( p_line_to.po_line_id, p_line_to.revision_num ) THEN
3776 insert_changes(
3777 p_sequence,
3778 c_po_header_id,
3779 c_release_id,
3780 c_line_id,
3781 c_location_id,
3782 c_distribution_id,
3783 c_item_id,
3784 c_po_num,
3785 c_revision_num,
3786 c_line_num,
3787 c_location_num,
3788 c_distribution_num,
3789 c_level_altered,
3790 'ICX_ADVANCE_AMOUNT',
3791 Get_Line_adv_Amount_revision(p_line_from.po_line_id, p_line_from.revision_num,
3792 Get_Line_adv_Amount_revision( p_line_to.po_line_id, p_line_to.revision_num),
3793 NULL,
3794 NULL,
3795 NULL,
3796 NULL,
3797 c_item,
3798 c_job
3799 );
3800 END IF;
3801 --end advance amount
3802 */
3803
3804 --end complex work
3805
3806 EXCEPTION
3807 WHEN others THEN
3808 PO_MESSAGE_S.SQL_ERROR(
3809 'PO_COMPARE_REVISIONS.COMPARE_LINES',
3810 v_progress,
3811 sqlcode );
3812 RAISE;
3813
3814 END compare_lines;
3815
3816
3817
3818
3819 /*********************************************************************
3820 * NAME
3821 * compare_locations
3822 *
3823 * PURPOSE
3824 * Accepts two records of the same locations with different revisions,
3825 * compare the data in both locations, and stores the differences in a
3826 * temporary table.
3827 *
3828 * ARGUMENTS
3829 * p_loc_from Old version of the line location.
3830 * p_loc_to New version of the line location.
3831 * p_sequence Sequence number to use in the temp table to identify
3832 * the records for delta.
3833 *
3834 * NOTES
3835 * The comparison is not done on all fields, but only the ones than
3836 * cause a revision change, according to Oracle Purchasing Reference
3837 * Manual.
3838 *
3839 * The fields that can be changed on PO header, and cause a revision
3840 * number increase are:
3841 *
3842 * Cancel Flag
3843 * Shipment number
3844 * Ship-to location
3845 * Quantity
3846 * Promised date
3847 * Need-by date
3848 * Last accept date
3849 * Taxable flag
3850 * Shipment price
3851 * Start Effective Date
3852 * End Effective Date
3853 *
3854 * HISTORY
3855 * 08-AUG-97 Nilo Paredes Created.
3856 * 23-SEP-97 Rami Haddad Added comparison for shipment number
3857 * and shipment price.
3858 * 14-FEB-2003 Amitabh Mitra Added start_date and end_date
3859 * for blankets
3860 ********************************************************************/
3861 PROCEDURE compare_locations(
3862 p_loc_from in po_line_locations_archive_all%ROWTYPE,
3863 p_loc_to in po_line_locations_archive_all%ROWTYPE,
3864 p_sequence IN NUMBER
3865 ) AS
3866
3867 /* Constant variables to pass for insert_changes */
3868 c_level_altered pos_po_revisions_gt.level_altered%TYPE
3869 := 'ICX_SHIPMENT';
3870 c_po_header_id NUMBER;
3871 c_release_id NUMBER;
3872 c_po_num po_headers_archive_all.segment1%TYPE := NULL;
3873 c_line_id NUMBER;
3874 c_line_num NUMBER := NULL;
3875 c_location_id NUMBER;
3876 c_location_num NUMBER;
3877 c_revision_num NUMBER;
3878 c_distribution_id NUMBER := NULL;
3879 c_distribution_num NUMBER := NULL;
3880 c_item_id NUMBER := NULL;
3881
3882 c_job_id NUMBER := NULL;
3883 c_org_id NUMBER := NULL;
3884 c_item mtl_system_items_kfv.concatenated_segments%TYPE;
3885 c_job per_jobs.name%TYPE;
3886
3887 v_progress VARCHAR2(3);
3888
3889 v_from_payment_type VARCHAR(100) := NULL;
3890 v_to_payment_type VARCHAR2(100) := NULL;
3891 BEGIN
3892
3893 /*
3894 * At least the latest revision should exist.
3895 */
3896 IF p_loc_to.po_header_id IS NULL THEN
3897 RETURN;
3898 END IF;
3899
3900 /* Set values for all constants */
3901 c_po_header_id := p_loc_to.po_header_id;
3902 c_release_id := p_loc_to.po_release_id;
3903 c_line_id := p_loc_to.po_line_id;
3904 c_location_id := p_loc_to.line_location_id;
3905 c_location_num := p_loc_to.shipment_num;
3906 c_revision_num := p_loc_to.revision_num;
3907
3908 v_progress := '580';
3909
3910 --get the line number using line id
3911
3912 select line_num ,item_id, job_id, org_id
3913 into c_line_num,c_item_id, c_job_id, c_org_id
3914 from po_lines_all
3915 where po_line_id = p_loc_to.po_line_id;
3916
3917 if (c_item_id is not null) then
3918 c_item := get_item(c_item_id, c_org_id);
3919 end if;
3920
3921 if (c_job_id is not null) then
3922 c_job := get_job(c_job_id);
3923 end if;
3924
3925 v_progress := '600';
3926
3927 /* If the previous record does not exist, then this is a new one. */
3928 IF p_loc_from.po_header_id IS NULL THEN
3929 insert_changes(
3930 p_sequence,
3931 c_po_header_id,
3932 c_release_id,
3933 c_line_id,
3934 c_location_id,
3935 c_distribution_id,
3936 c_item_id,
3937 c_po_num,
3938 c_revision_num,
3939 c_line_num,
3940 c_location_num,
3941 c_distribution_num,
3942 c_level_altered,
3943 --fnd_message.get_String('PO', 'POS_NEW'),
3944 'ICX_NEW',
3945 NULL,
3946 NULL,
3947 NULL,
3948 NULL,
3949 NULL,
3950 NULL,
3951 c_item,
3952 c_job
3953 );
3954 RETURN;
3955 END IF;
3956
3957 /* Are the lines the same? */
3958 IF p_loc_from.line_location_id <> p_loc_to.line_location_id THEN
3959 RETURN;
3960 END IF;
3961
3962 /* Do not compare lines of the same revision number. */
3963 IF NVL( p_loc_from.revision_num, -99 ) =
3964 NVL( p_loc_to.revision_num, -99 ) THEN
3965 RETURN;
3966 END IF;
3967
3968 --start complex work
3969
3970 --start advance amount
3971 /* for shipments of payment type ADVANCE and shipment type PREPAYMENT, we show change in amount as
3972 change in Advance Amount at line level
3973 This code replaces Advance Amount Code in compare_lines - search for ADVAMT in this file
3974 */
3975
3976 IF ( (NVL(p_loc_from.payment_type, ' ') = 'ADVANCE' and NVL(p_loc_from.shipment_type, ' ') = 'PREPAYMENT' ) or
3977 (NVL(p_loc_to.payment_type, ' ') = 'ADVANCE' and NVL(p_loc_to.shipment_type, ' ') = 'PREPAYMENT' ) ) THEN
3978
3979 IF (NVL( p_loc_from.amount, -99 ) <>
3980 NVL( p_loc_to.amount, -99 )) THEN
3981 insert_changes(
3982 p_sequence,
3983 c_po_header_id,
3984 c_release_id,
3985 c_line_id,
3986 NULL,
3987 NULL,
3988 c_item_id,
3989 c_po_num,
3990 c_revision_num,
3991 c_line_num,
3992 NULL,
3993 NULL,
3994 'ICX_LINE',
3995 'ICX_ADVANCE_AMOUNT',
3996 p_loc_from.amount,
3997 p_loc_to.amount,
3998 NULL,
3999 NULL,
4000 NULL,
4001 NULL,
4002 c_item,
4003 c_job
4004 );
4005 END IF;
4006
4007 END IF;
4008 --end advance amount
4009
4010 /* Do not show shipments of type 'ADVANCE', 'DELIVERY'*/
4011 IF (NVL(p_loc_from.payment_type, ' ') in ('ADVANCE', 'DELIVERY')) THEN
4012 RETURN;
4013 END IF;
4014 --end complex work
4015
4016 /*
4017 * If current line location is cancelled, then check if the priior one
4018 * is cancelled as well. If it is, then there is no change. Otherwise
4019 * the line is cancelled for the current revision.
4020 */
4021
4022 v_progress := '610';
4023
4024 IF p_loc_to.cancel_flag = 'Y' THEN
4025 IF p_loc_from.cancel_flag = 'Y'
4026 THEN
4027 RETURN;
4028 ELSE
4029 insert_changes(
4030 p_sequence,
4031 c_po_header_id,
4032 c_release_id,
4033 c_line_id,
4034 c_location_id,
4035 c_distribution_id,
4036 c_item_id,
4037 c_po_num,
4038 c_revision_num,
4039 c_line_num,
4040 c_location_num,
4041 c_distribution_num,
4042 c_level_altered,
4043 --fnd_message.get_string('PO', 'POS_CANCELLED'),
4044 'ICX_CANCELLED',
4045 NULL,
4046 NULL,
4047 NULL,
4048 NULL,
4049 NULL,
4050 NULL,
4051 c_item,
4052 c_job
4053 );
4054 RETURN;
4055 END IF;
4056 END IF;
4057
4058 /*
4059 * Line location not cancelled in current PO. Compare all field with
4060 * line in prior revision.
4061 */
4062
4063 v_progress := '620';
4064
4065 /* Shipment number */
4066 IF NVL( p_loc_from.shipment_num, -99 ) <>
4067 NVL( p_loc_to.shipment_num, -99 ) THEN
4068 insert_changes(
4069 p_sequence,
4070 c_po_header_id,
4071 c_release_id,
4072 c_line_id,
4073 c_location_id,
4074 c_distribution_id,
4075 c_item_id,
4076 c_po_num,
4077 c_revision_num,
4078 c_line_num,
4079 c_location_num,
4080 c_distribution_num,
4081 c_level_altered,
4082 --fnd_message.get_String('PO', 'POS_SHIP_NUM'),
4083 'ICX_SHIP_NUM',
4084 p_loc_from.shipment_num,
4085 p_loc_to.shipment_num,
4086 NULL,
4087 NULL,
4088 NULL,
4089 NULL,
4090 c_item,
4091 c_job
4092 );
4093 END IF;
4094
4095 v_progress := '630';
4096
4097 /* Ship-to location */
4098 IF NVL( p_loc_from.ship_to_location_id, -99 ) <>
4099 NVL( p_loc_to.ship_to_location_id, -99 ) THEN
4100 insert_changes(
4101 p_sequence,
4102 c_po_header_id,
4103 c_release_id,
4104 c_line_id,
4105 c_location_id,
4106 c_distribution_id,
4107 c_item_id,
4108 c_po_num,
4109 c_revision_num,
4110 c_line_num,
4111 c_location_num,
4112 c_distribution_num,
4113 c_level_altered,
4114 --fnd_message.get_String('PO', 'POS_SHIP_TO'),
4115 'ICX_SHIP_TO',
4116 get_location( p_loc_from.ship_to_location_id ),
4117 get_location( p_loc_to.ship_to_location_id ),
4118 NULL,
4119 NULL,
4120 NULL,
4121 NULL,
4122 c_item,
4123 c_job
4124 );
4125 END IF;
4126
4127 v_progress := '640';
4128
4129 /* Quantity */
4130 IF NVL( p_loc_from.quantity, -99 ) <> NVL( p_loc_to.quantity, -99 )
4131 THEN
4132 insert_changes(
4133 p_sequence,
4134 c_po_header_id,
4135 c_release_id,
4136 c_line_id,
4137 c_location_id,
4138 c_distribution_id,
4139 c_item_id,
4140 c_po_num,
4141 c_revision_num,
4142 c_line_num,
4143 c_location_num,
4144 c_distribution_num,
4145 c_level_altered,
4146 --fnd_message.get_String('PO', 'POS_QUANTITY'),
4147 'ICX_QUANTITY',
4148 p_loc_from.quantity,
4149 p_loc_to.quantity,
4150 NULL,
4151 NULL,
4152 NULL,
4153 NULL,
4154 c_item,
4155 c_job
4156 );
4157 END IF;
4158
4159 v_progress := '650';
4160
4161 /* promised date */
4162 IF NVL( p_loc_from.promised_date,
4163 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
4164 NVL( p_loc_to.promised_date,
4165 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
4166 insert_changes(
4167 p_sequence,
4168 c_po_header_id,
4169 c_release_id,
4170 c_line_id,
4171 c_location_id,
4172 c_distribution_id,
4173 c_item_id,
4174 c_po_num,
4175 c_revision_num,
4176 c_line_num,
4177 c_location_num,
4178 c_distribution_num,
4179 c_level_altered,
4180 --fnd_message.get_String('PO', 'POS_PROMISED_DATE'),
4181 'ICX_PROMISED_DATE',
4182 null,
4183 null,
4184 null,
4185 null,
4186 p_loc_from.promised_date,
4187 p_loc_to.promised_date,
4188 c_item,
4189 c_job
4190 );
4191 END IF;
4192
4193 v_progress := '660';
4194
4195 /* Need-by date */
4196 IF NVL( p_loc_from.need_by_date,
4197 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
4198 NVL( p_loc_to.need_by_date,
4199 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
4200 insert_changes(
4201 p_sequence,
4202 c_po_header_id,
4203 c_release_id,
4204 c_line_id,
4205 c_location_id,
4206 c_distribution_id,
4207 c_item_id,
4208 c_po_num,
4209 c_revision_num,
4210 c_line_num,
4211 c_location_num,
4212 c_distribution_num,
4213 c_level_altered,
4214 --fnd_message.get_String('PO', 'POS_NEED_BY_DATE'),
4215 'ICX_NEED_BY_DATE',
4216 null,
4217 null,
4218 null,
4219 null,
4220 p_loc_from.need_by_date,
4221 p_loc_to.need_by_date,
4222 c_item,
4223 c_job
4224 );
4225 END IF;
4226
4227 v_progress := '670';
4228
4229 /* Last accept date */
4230 IF NVL( p_loc_from.last_accept_date,
4231 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
4232 NVL( p_loc_to.last_accept_date,
4233 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
4234 insert_changes(
4235 p_sequence,
4236 c_po_header_id,
4237 c_release_id,
4238 c_line_id,
4239 c_location_id,
4240 c_distribution_id,
4241 c_item_id,
4242 p_loc_from.revision_num || '-' || p_loc_to.revision_num,
4243 c_revision_num,
4244 c_line_num,
4245 c_location_num,
4246 c_distribution_num,
4247 c_level_altered,
4248 --fnd_message.get_String('PO', 'POS_LAST_ACCEPT_DATE'),
4249 'ICX_LAST_ACCEPT_DATE',
4250 --start bug 4179194
4251 /*
4252 to_char(p_loc_from.last_accept_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
4253 to_char(p_loc_to.last_accept_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
4254 );
4255 */
4256 null,
4257 null,
4258 null,
4259 null,
4260 p_loc_from.last_accept_date,
4261 p_loc_to.last_accept_date,
4262 c_item,
4263 c_job
4264 );
4265 --end bug 4179194
4266 END IF;
4267
4268 v_progress := '680';
4269
4270 /* Taxable flag */
4271 IF NVL( p_loc_from.taxable_flag, ' ' ) <>
4272 NVL( p_loc_to.taxable_flag, ' ' ) THEN
4273 insert_changes(
4274 p_sequence,
4275 c_po_header_id,
4276 c_release_id,
4277 c_line_id,
4278 c_location_id,
4279 c_distribution_id,
4280 c_item_id,
4281 c_po_num,
4282 c_revision_num,
4283 c_line_num,
4284 c_location_num,
4285 c_distribution_num,
4286 c_level_altered,
4287 --fnd_message.get_String('PO', 'POS_TAXABLE_FLAG'),
4288 'ICX_TAXABLE_FLAG',
4289 get_po_lookup(
4290 'YES/NO',
4291 p_loc_from.taxable_flag ),
4292 get_po_lookup(
4293 'YES/NO',
4294 p_loc_to.taxable_flag ),
4295 NULL,
4296 NULL,
4297 NULL,
4298 NULL,
4299 c_item,
4300 c_job
4301 );
4302 END IF;
4303
4304
4305 v_progress := '690';
4306
4307 /* Price break */
4308
4309 --start fix bug 4696960
4310 --for releases show change in price_override as Unit Price Change
4311
4312 IF ((NVL( p_loc_from.price_override, -99 ) <>
4313 NVL( p_loc_to.price_override, -99 )) and p_loc_from.shipment_type = 'PRICE BREAK' and c_release_id is null) THEN
4314 insert_changes(
4315 p_sequence,
4316 c_po_header_id,
4317 c_release_id,
4318 c_line_id,
4319 c_location_id,
4320 c_distribution_id,
4321 c_item_id,
4322 c_po_num,
4323 c_revision_num,
4324 c_line_num,
4325 c_location_num,
4326 c_distribution_num,
4327 c_level_altered,
4328 --fnd_message.get_String('PO', 'POS_PRICE_BREAK'),
4329 'ICX_PRICE_BREAK',
4330 p_loc_from.price_override,
4331 p_loc_to.price_override,
4332 NULL,
4333 NULL,
4334 NULL,
4335 NULL,
4336 c_item,
4337 c_job
4338 );
4339 END IF;
4340
4341
4342 IF ((NVL( p_loc_from.price_override, -99 ) <>
4343 NVL( p_loc_to.price_override, -99 )) /* --bug 5204345 -- and c_release_id is not null*/) THEN
4344 insert_changes(
4345 p_sequence,
4346 c_po_header_id,
4347 c_release_id,
4348 c_line_id,
4349 c_location_id,
4350 c_distribution_id,
4351 c_item_id,
4352 c_po_num,
4353 c_revision_num,
4354 c_line_num,
4355 c_location_num,
4356 c_distribution_num,
4357 c_level_altered,
4358 --fnd_message.get_String('PO', 'POS_UNIT_BREAK'),
4359 'ICX_UNIT_PRICE',
4360 p_loc_from.price_override,
4361 p_loc_to.price_override,
4362 NULL,
4363 NULL,
4364 NULL,
4365 NULL,
4366 c_item,
4367 c_job
4368 );
4369 END IF;
4370
4371 --end fix bug 4696960
4372
4373 -- Added start_date and end_date for blanket price breaks
4374
4375 /* Start Effective date */
4376 IF NVL( p_loc_from.start_date,
4377 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
4378 NVL( p_loc_to.start_date,
4379 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
4380 insert_changes(
4381 p_sequence,
4382 c_po_header_id,
4383 c_release_id,
4384 c_line_id,
4385 c_location_id,
4386 c_distribution_id,
4387 c_item_id,
4388 c_po_num,
4389 c_revision_num,
4390 c_line_num,
4391 c_location_num,
4392 c_distribution_num,
4393 c_level_altered,
4394 'ICX_START_EFF_DATE',
4395 --start bug 4179194
4396 /*
4397 to_char(p_loc_from.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
4398 to_char(p_loc_to.start_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
4399 );
4400 */
4401 null,
4402 null,
4403 null,
4404 null,
4405 p_loc_from.start_date,
4406 p_loc_to.start_date,
4407 c_item,
4408 c_job
4409 );
4410 --end bug 4179194
4411 END IF;
4412
4413 /* End Effective Date */
4414 IF NVL( p_loc_from.end_date,
4415 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <>
4416 NVL( p_loc_to.end_date,
4417 TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) THEN
4418 insert_changes(
4419 p_sequence,
4420 c_po_header_id,
4421 c_release_id,
4422 c_line_id,
4423 c_location_id,
4424 c_distribution_id,
4425 c_item_id,
4426 c_po_num,
4427 c_revision_num,
4428 c_line_num,
4429 c_location_num,
4430 c_distribution_num,
4431 c_level_altered,
4432 'ICX_END_EFF_DATE',
4433 --start bug 4179194
4434 /* to_char(p_loc_from.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')),
4435 to_char(p_loc_to.end_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'))
4436 );
4437 */
4438 null,
4439 null,
4440 null,
4441 null,
4442 p_loc_from.end_date,
4443 p_loc_to.end_date,
4444 c_item,
4445 c_job
4446 );
4447 --end bug 4179194
4448 END IF;
4449
4450 --start bug 5167605
4451 IF (NVL( p_loc_from.amount, -99 ) <>
4452 NVL( p_loc_to.amount, -99 )) THEN
4453 insert_changes(
4454 p_sequence,
4455 c_po_header_id,
4456 c_release_id,
4457 c_line_id,
4458 c_location_id,
4459 c_distribution_id,
4460 c_item_id,
4461 c_po_num,
4462 c_revision_num,
4463 c_line_num,
4464 c_location_num,
4465 c_distribution_num,
4466 c_level_altered,
4467 -- start fix for bug 5388428
4468 'ICX_UNIT_PRICE',
4469 -- 'ICX_AMOUNT',
4470 -- end fix
4471 p_loc_from.amount,
4472 p_loc_to.amount,
4473 NULL,
4474 NULL,
4475 NULL,
4476 NULL,
4477 c_item,
4478 c_job
4479 );
4480 END IF;
4481 --end bug 5167605
4482
4483 --start complex work ECO 5106340
4484
4485 IF (NVL( p_loc_from.work_approver_id, -99 ) <>
4486 NVL( p_loc_to.work_approver_id, -99 )) THEN
4487 insert_changes(
4488 p_sequence,
4489 c_po_header_id,
4490 c_release_id,
4491 c_line_id,
4492 c_location_id,
4493 c_distribution_id,
4494 c_item_id,
4495 c_po_num,
4496 c_revision_num,
4497 c_line_num,
4498 c_location_num,
4499 c_distribution_num,
4500 c_level_altered,
4501 'ICX_OWNER',
4502 get_owner(p_loc_from.work_approver_id),
4503 get_owner(p_loc_to.work_approver_id),
4504 NULL,
4505 NULL,
4506 NULL,
4507 NULL,
4508 c_item,
4509 c_job
4510 );
4511 END IF;
4512
4513 IF NVL( p_loc_from.description, ' ' ) <>
4514 NVL( p_loc_to.description, ' ' ) THEN
4515 insert_changes(
4516 p_sequence,
4517 c_po_header_id,
4518 c_release_id,
4519 c_line_id,
4520 c_location_id,
4521 c_distribution_id,
4522 c_item_id,
4523 c_po_num,
4524 c_revision_num,
4525 c_line_num,
4526 c_location_num,
4527 c_distribution_num,
4528 c_level_altered,
4529 'ICX_PAY_DESCRIPTION',
4530 p_loc_from.description,
4531 p_loc_to.description,
4532 NULL,
4533 NULL,
4534 NULL,
4535 NULL,
4536 c_item,
4537 c_job
4538 );
4539 END IF;
4540
4541 IF NVL( p_loc_from.payment_type, ' ' ) <>
4542 NVL( p_loc_to.payment_type, ' ' ) THEN
4543
4544 if (p_loc_from.payment_type = 'MILESTONE') THEN
4545 v_from_payment_type := fnd_message.get_String('POS','POS_PROG_PAY_MILE');
4546 elsif (p_loc_from.payment_type = 'RATE') THEN
4547 v_from_payment_type := fnd_message.get_String('POS','POS_PROG_PAY_RATE');
4548 elsif (p_loc_from.payment_type = 'LUMPSUM') THEN
4549 v_from_payment_type := fnd_message.get_String('POS','POS_PROG_PAY_LUMP');
4550 else
4551 v_from_payment_type := p_loc_from.payment_type;
4552 end if;
4553
4554 if (p_loc_to.payment_type = 'MILESTONE') THEN
4555 v_to_payment_type := fnd_message.get_String('POS','POS_PROG_PAY_MILE');
4556 elsif (p_loc_to.payment_type = 'RATE') THEN
4557 v_to_payment_type := fnd_message.get_String('POS','POS_PROG_PAY_RATE');
4558 elsif (p_loc_to.payment_type = 'LUMPSUM') THEN
4559 v_to_payment_type := fnd_message.get_String('POS','POS_PROG_PAY_LUMP');
4560 else
4561 v_to_payment_type := p_loc_to.payment_type;
4562 end if;
4563
4564 insert_changes(
4565 p_sequence,
4566 c_po_header_id,
4567 c_release_id,
4568 c_line_id,
4569 c_location_id,
4570 c_distribution_id,
4571 c_item_id,
4572 c_po_num,
4573 c_revision_num,
4574 c_line_num,
4575 c_location_num,
4576 c_distribution_num,
4577 c_level_altered,
4578 'ICX_PAY_TYPE',
4579 v_from_payment_type,
4580 v_to_payment_type,
4581 NULL,
4582 NULL,
4583 NULL,
4584 NULL,
4585 c_item,
4586 c_job
4587 );
4588 END IF;
4589
4590 /*
4591 IF (p_loc_from.payment_type = 'MILESTONE' || p_loc_to.payment_type = 'MILESTONE' ) THEN
4592
4593 IF (Get_ship_val_percent_revision (p_loc_from.line_location_id, p_loc_from.revision_num) <>
4594 Get_ship_val_percent_revision (p_loc_to.line_location_id, p_loc_to.revision_num) ) THEN
4595 insert_changes(
4596 p_sequence,
4597 c_po_header_id,
4598 c_release_id,
4599 c_line_id,
4600 c_location_id,
4601 c_distribution_id,
4602 c_item_id,
4603 c_po_num,
4604 c_revision_num,
4605 c_line_num,
4606 c_location_num,
4607 c_distribution_num,
4608 c_level_altered,
4609 'ICX_VALUE_PERCENT',
4610 Get_ship_val_percent_revision (p_loc_from.line_location_id, p_loc_from.revision_num),
4611 Get_ship_val_percent_revision (p_loc_to.line_location_id, p_loc_to.revision_num),
4612 NULL,
4613 NULL,
4614 NULL,
4615 NULL,
4616 c_item,
4617 c_job
4618 );
4619 END IF;
4620
4621 END IF;
4622 --end complex work
4623
4624 */
4625 EXCEPTION
4626 WHEN others THEN
4627 PO_MESSAGE_S.SQL_ERROR(
4628 'PO_COMPARE_REVISIONS.COMPARE_LOCATIONS',
4629 v_progress,
4630 sqlcode );
4631 RAISE;
4632
4633 END compare_locations;
4634
4635
4636
4637 /*********************************************************************
4638 * NAME
4639 * compare_distributions
4640 *
4641 * PURPOSE
4642 * Accepts two records of the same distribution with different
4643 * revisions, compare the data in both distributions, and stores the
4644 * differences in a temporary table.
4645 *
4646 * ARGUMENTS
4647 * p_dist_from Old version of the distribution.
4648 * p_dist_to New version of the distribution.
4649 * p_sequence Sequence number to use in the temp table to identify
4650 * the records for delta.
4651 *
4652 * NOTES
4653 * The comparison is not done on all fields, but only the ones than
4654 * cause a revision change, according to Oracle Purchasing Reference
4655 * Manual.
4656 *
4657 * The fields that can be changed on PO header, and cause a revision
4658 * number increase are:
4659 *
4660 * Cancel Flag
4661 * Quantity ordered
4662 * Requestor
4663 * Charge account
4664 *
4665 * Distributions cannot be cancelled, so there is no need to check for
4666 * cancelled distribution lines.
4667 *
4668 * HISTORY
4669 * 08-AUG-97 Nilo Paredes Created.
4670 ********************************************************************/
4671 PROCEDURE compare_distributions(
4672 p_dist_from in po_distributions_archive_all%ROWTYPE,
4673 p_dist_to in po_distributions_archive_all%ROWTYPE,
4674 p_sequence IN NUMBER
4675 ) AS
4676
4677 /*
4678 * Constant variables to pass for insert_changes
4679 */
4680 c_level_altered pos_po_revisions_gt.level_altered%TYPE
4681 := 'ICX_DISTRIBUTION';
4682 c_po_header_id NUMBER;
4683 c_release_id NUMBER;
4684 c_po_num po_headers_archive_all.segment1%TYPE := NULL;
4685 c_line_id NUMBER;
4686 c_line_num NUMBER := NULL;
4687 c_location_id NUMBER;
4688 c_location_num NUMBER := NULL;
4689 c_distribution_id NUMBER;
4690 c_distribution_num NUMBER;
4691 c_revision_num NUMBER;
4692 c_item_id NUMBER := NULL;
4693
4694 v_progress VARCHAR2(3);
4695
4696 BEGIN
4697
4698 /*
4699 * At least the latest revision should exist.
4700 */
4701 IF p_dist_to.po_header_id IS NULL THEN
4702 RETURN;
4703 END IF;
4704
4705 /*
4706 * Set values for all constants
4707 */
4708 c_po_header_id := p_dist_to.po_header_id;
4709 c_release_id := p_dist_to.po_release_id;
4710 c_line_id := p_dist_to.po_line_id;
4711 c_location_id := p_dist_to.line_location_id;
4712 c_distribution_id := p_dist_to.po_distribution_id;
4713 c_distribution_num := p_dist_to.distribution_num;
4714 c_revision_num := p_dist_to.revision_num;
4715
4716 v_progress := '700';
4717
4718 /* Bug# 1893770 */
4719 --get the line number using line id
4720
4721 select line_num,item_id
4722 into c_line_num,c_item_id
4723 from po_lines_all
4724 where po_line_id = p_dist_to.po_line_id;
4725
4726 --get the shipment number using line location id
4727
4728 select shipment_num
4729 into c_location_num
4730 from po_line_locations_all
4731 where line_location_id = p_dist_to.line_location_id;
4732
4733 /*
4734 * If the old record does not exist, then this is a new one.
4735 */
4736
4737 IF p_dist_from.po_header_id IS NULL THEN
4738 -- forward port bug 5392285
4739 /*
4740 insert_changes(
4741 p_sequence,
4742 c_po_header_id,
4743 c_release_id,
4744 c_line_id,
4745 c_location_id,
4746 c_distribution_id,
4747 c_item_id,
4748 c_po_num,
4749 c_revision_num,
4750 c_line_num,
4751 c_location_num,
4752 c_distribution_num,
4753 c_level_altered,
4754 --fnd_message.get_String('PO', 'POS_NEW'),
4755 'ICX_NEW',
4756 NULL,
4757 NULL
4758 );
4759 */
4760 RETURN;
4761 END IF;
4762
4763 /*
4764 * Are the lines the same?
4765 */
4766 IF NVL( p_dist_from.line_location_id, -99 ) <>
4767 NVL( p_dist_to.line_location_id, -99 ) THEN
4768 RETURN;
4769 END IF;
4770
4771 /*
4772 * Do not compare lines of the same revision number.
4773 */
4774
4775 IF NVL( p_dist_from.revision_num, -99 ) =
4776 NVL( p_dist_to.revision_num, -99 ) THEN
4777 RETURN;
4778 END IF;
4779
4780 v_progress := '710';
4781
4782 /* Quantity ordered */
4783 /*
4784 -- forward port bug 5392285
4785 IF NVL( p_dist_from.quantity_ordered, -99 ) <>
4786 NVL( p_dist_to.quantity_ordered, -99 ) THEN
4787 insert_changes(
4788 p_sequence,
4789 c_po_header_id,
4790 c_release_id,
4791 c_line_id,
4792 c_location_id,
4793 c_distribution_id,
4794 c_item_id,
4795 c_po_num,
4796 c_revision_num,
4797 c_line_num,
4798 c_location_num,
4799 c_distribution_num,
4800 c_level_altered,
4801 --fnd_message.get_String('PO', 'POS_QUANTITY'),
4802 'ICX_QUANTITY',
4803 p_dist_from.quantity_ordered,
4804 p_dist_to.quantity_ordered
4805 );
4806 END IF;
4807 */
4808 v_progress := '720';
4809
4810 /* Requestor */
4811 IF NVL( p_dist_from.deliver_to_person_id, -99 ) <>
4812 NVL( p_dist_to.deliver_to_person_id, -99 ) THEN
4813 insert_changes(
4814 p_sequence,
4815 c_po_header_id,
4816 c_release_id,
4817 c_line_id,
4818 c_location_id,
4819 c_distribution_id,
4820 c_item_id,
4821 c_po_num,
4822 c_revision_num,
4823 c_line_num,
4824 c_location_num,
4825 c_distribution_num,
4826 c_level_altered,
4827 --fnd_message.get_String('PO', 'POS_REQUESTOR'),
4828 'ICX_REQUESTOR',
4829 get_requestor(p_dist_from.deliver_to_person_id),
4830 get_requestor(p_dist_to.deliver_to_person_id)
4831 );
4832 END IF;
4833
4834 v_progress := '730';
4835
4836 /* Charge account */
4837
4838 /* Bug 1181007
4839 inserting the associated charge account instead of the
4840 ccid by using the function get_charge_account.
4841 */
4842
4843 IF NVL( p_dist_from.code_combination_id, -99 ) <>
4844 NVL( p_dist_to.code_combination_id, -99 ) THEN
4845 insert_changes(
4846 p_sequence,
4847 c_po_header_id,
4848 c_release_id,
4849 c_line_id,
4850 c_location_id,
4851 c_distribution_id,
4852 c_item_id,
4853 c_po_num,
4854 c_revision_num,
4855 c_line_num,
4856 c_location_num,
4857 c_distribution_num,
4858 c_level_altered,
4859 --fnd_message.get_String('PO', 'POS_CHARGE_ACCT'),
4860 'ICX_CHARGE_ACCT',
4861 get_charge_account(p_dist_from.code_combination_id),
4862 get_charge_account(p_dist_to.code_combination_id)
4863 );
4864 END IF;
4865
4866 EXCEPTION
4867 WHEN others THEN
4868 PO_MESSAGE_S.SQL_ERROR(
4869 'PO_COMPARE_REVISIONS.PO_COMPARE_DISTRIBUTIONS',
4870 v_progress,
4871 sqlcode );
4872 RAISE;
4873
4874 END compare_distributions;
4875
4876 /*Bug 1181007
4877 The following function get_charge_account is added to fetch
4878 the charge account when the ccid is given
4879 */
4880
4881 /*********************************************************************
4882 * NAME
4883 * get_charge_account
4884 *
4885 * PURPOSE
4886 * To fetch the charge account based on the ccid from the
4887 * gl_code_combinations_kfv.
4888 *
4889 * ARGUMENTS
4890 * p_code_combiation_id Unique identifier for charge account in
4891 * GL_CODE_COMBINATIONS_KFV view.
4892 *
4893 * NOTES
4894 * Return NULL if an error occurs.
4895 *
4896 * HISTORY
4897 * 01-FEB-2000 Suresh Arunachalam Created
4898 ********************************************************************/
4899
4900 FUNCTION get_charge_account(p_code_combination_id IN NUMBER)
4901 RETURN VARCHAR2 AS
4902 v_charge_account varchar2( 4000 );
4903 v_progress varchar2(3);
4904
4905 BEGIN
4906
4907 v_progress := '830';
4908
4909 SELECT concatenated_segments
4910 INTO v_charge_account
4911 FROM gl_code_combinations_kfv
4912 WHERE code_combination_id = p_code_combination_id;
4913
4914 RETURN v_charge_account;
4915
4916 EXCEPTION
4917 WHEN no_data_found THEN
4918 RETURN NULL;
4919 WHEN others THEN
4920 PO_MESSAGE_S.SQL_ERROR(
4921 'PO_COMPARE_REVISIONS.GET_CHARGE_ACCOUNT',
4922 v_progress,
4923 sqlcode );
4924 RAISE;
4925
4926 END get_charge_account;
4927
4928 /*
4929 bug 4261155
4930 */
4931 /*********************************************************************
4932 * NAME
4933 * get_requestor
4934 *
4935 * PURPOSE
4936 * Resolves the requestor name.
4937 *
4938 * ARGUMENTS
4939 * p_agent_id Unique identifier for buyer in PER_PEOPLE_F table.
4940 *
4941 * NOTES
4942 * Return NULL if no matching records were found.
4943 *
4944 * HISTORY
4945 * 15-Mar-05 Abhishek Trivedi Created
4946 ********************************************************************/
4947 FUNCTION get_requestor( p_agent_id IN NUMBER )
4948 RETURN VARCHAR2 AS
4949
4950 v_full_name per_people_f.full_name%TYPE;
4951 v_progress varchar2(3);
4952
4953 BEGIN
4954
4955 v_progress := '111';
4956
4957 SELECT full_name
4958 INTO v_full_name
4959 FROM per_people_f
4960 WHERE person_id = p_agent_id and rownum = 1
4961 order by effective_start_date desc ;
4962
4963 RETURN v_full_name;
4964
4965 EXCEPTION
4966 WHEN no_data_found THEN
4967 RETURN NULL;
4968 WHEN others THEN
4969 PO_MESSAGE_S.SQL_ERROR(
4970 'PO_COMPARE_REVISIONS.GET_REQUESTOR',
4971 v_progress,
4972 sqlcode );
4973 RAISE;
4974
4975 END get_requestor;
4976
4977
4978 /*
4979 bug 5106221
4980 */
4981 /*********************************************************************
4982 * NAME
4983 * get_shopping_category
4984 *
4985 * PURPOSE
4986 * Resolves the requestor name.
4987 *
4988 * ARGUMENTS
4989 * p_category_id Unique identifier for shopping category
4990 *
4991 * NOTES
4992 * Return NULL if no matching records were found.
4993 *
4994 * HISTORY
4995 * 10-May-05 Abhishek Trivedi Created
4996 ********************************************************************/
4997 FUNCTION get_shopping_category( p_category_id IN NUMBER )
4998 RETURN VARCHAR2 AS
4999
5000 v_category_name icx_cat_categories_v.category_name%TYPE;
5001 v_progress varchar2(3);
5002
5003 BEGIN
5004
5005 v_progress := '111';
5006
5007 select category_name
5008 INTO v_category_name
5009 from icx_cat_categories_v
5010 where rt_category_id = p_category_id
5011 and language = USERENV('LANG')
5012 and rownum = 1;
5013
5014 RETURN v_category_name;
5015
5016 EXCEPTION
5017 WHEN no_data_found THEN
5018 RETURN NULL;
5019 WHEN others THEN
5020 PO_MESSAGE_S.SQL_ERROR(
5021 'PO_COMPARE_REVISIONS.GET_SHOPPING_CATEGORY',
5022 v_progress,
5023 sqlcode );
5024 RAISE;
5025
5026 END get_shopping_category;
5027
5028
5029 /* bug 5215207*/
5030 /*********************************************************************
5031 * NAME
5032 * get_owner
5033 *
5034 * PURPOSE
5035 * Resolves the owner name.
5036 *
5037 * ARGUMENTS
5038 * p_work_approver_id Unique identifier in fnd_user.
5039 *
5040 * NOTES
5041 * Return NULL if no matching records were found.
5042 *
5043 * HISTORY
5044 * 09-May-06 Abhishek Trivedi Created
5045 ********************************************************************/
5046 FUNCTION get_owner( p_work_approver_id IN NUMBER )
5047 RETURN VARCHAR2 AS
5048
5049 v_full_name per_people_f.full_name%TYPE;
5050 v_progress varchar2(3);
5051
5052 BEGIN
5053
5054 v_progress := '111';
5055
5056 select hecv.full_name
5057 INTO v_full_name
5058 from PER_PEOPLE_F --hr_employees_current_v
5059 hecv, fnd_user fu
5060 where fu.user_id = p_work_approver_id and
5061 fu.employee_id = hecv.person_id (+) --employee_id (+)
5062 and rownum = 1
5063 order by hecv.effective_start_date desc ;
5064
5065 RETURN v_full_name;
5066
5067 EXCEPTION
5068 WHEN no_data_found THEN
5069 RETURN NULL;
5070 WHEN others THEN
5071 PO_MESSAGE_S.SQL_ERROR(
5072 'PO_COMPARE_REVISIONS.GET_OWNER',
5073 v_progress,
5074 sqlcode );
5075 RAISE;
5076
5077 END get_owner;
5078
5079
5080 /*
5081 Bug 4347578
5082 */
5083 FUNCTION get_nextval
5084 RETURN NUMBER IS
5085
5086 p_nextval NUMBER;
5087
5088 BEGIN
5089
5090 SELECT icx_po_history_details_s.nextval
5091 INTO p_nextval
5092 FROM DUAL;
5093
5094 RETURN p_nextval;
5095
5096 EXCEPTION
5097 WHEN others THEN
5098 PO_MESSAGE_S.SQL_ERROR(
5099 'PO_COMPARE_REVISIONS.GET_NEXTVAL',
5100 '112',
5101 sqlcode );
5102 RAISE;
5103
5104 END get_nextval;
5105
5106 /*********************************************************************
5107 * NAME
5108 * get_job
5109 *
5110 * PURPOSE
5111 * Resolves the job name
5112 *
5113 * ARGUMENTS
5114 * p_job_id job_id from po_lines_all
5115 *
5116 * NOTES
5117 * Return NULL if no matching records were found.
5118 *
5119 * HISTORY
5120 * 15-Mar-05 Abhishek Trivedi Created
5121 ********************************************************************/
5122 FUNCTION get_job( p_job_id IN NUMBER )
5123 RETURN VARCHAR2 AS
5124
5125 v_job_name per_jobs.name%TYPE;
5126 v_progress varchar2(3);
5127
5128 BEGIN
5129
5130 v_progress := '111';
5131
5132 SELECT name
5133 INTO v_job_name
5134 FROM per_jobs
5135 WHERE job_id = p_job_id and rownum = 1;
5136
5137 RETURN v_job_name;
5138
5139 EXCEPTION
5140 WHEN no_data_found THEN
5141 RETURN NULL;
5142 WHEN others THEN
5143 PO_MESSAGE_S.SQL_ERROR(
5144 'PO_COMPARE_REVISIONS.GET_JOB',
5145 v_progress,
5146 sqlcode );
5147 RAISE;
5148 END get_job;
5149
5150 /*********************************************************************
5151 * NAME
5152 * get_item
5153 *
5154 * PURPOSE
5155 * Resolves the item name
5156 *
5157 * ARGUMENTS
5158 * p_item_id item_id from po_lines_all
5159 * p_org_id org_id from po_lines_all
5160 *
5161 * NOTES
5162 * Return NULL if no matching records were found.
5163 *
5164 * HISTORY
5165 * 15-Mar-05 Abhishek Trivedi Created
5166 ********************************************************************/
5167 FUNCTION get_item( p_item_id IN NUMBER, p_org_id IN NUMBER)
5168 RETURN VARCHAR2 AS
5169
5170 v_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
5171 v_progress varchar2(3);
5172
5173 BEGIN
5174
5175 v_progress := '111';
5176
5177
5178 select msik.concatenated_segments
5179 INTO v_item_name
5180 from mtl_system_items_kfv msik, FINANCIALS_SYSTEM_PARAMS_ALL FSP
5181 where msik.INVENTORY_ITEM_ID = P_ITEM_ID
5182 AND FSP.INVENTORY_ORGANIZATION_ID = NVL(msik.ORGANIZATION_ID,FSP.INVENTORY_ORGANIZATION_ID)
5183 AND FSP.ORG_ID (+)= P_ORG_ID and rownum = 1;
5184
5185 RETURN v_item_name;
5186
5187 EXCEPTION
5188 WHEN no_data_found THEN
5189 RETURN NULL;
5190 WHEN others THEN
5191 PO_MESSAGE_S.SQL_ERROR(
5192 'PO_COMPARE_REVISIONS.GET_ITEM',
5193 v_progress,
5194 sqlcode );
5195 RAISE;
5196 END get_item;
5197
5198 /*********************************************************************
5199 * NAME
5200 * GET_LINE_ADV_AMOUNT_REVISION
5201 *
5202 * PURPOSE
5203 * Calculates the advance amount from shipments
5204 *
5205 * ARGUMENTS
5206 * p_po_line_id po_line_id
5207 * p_revision_num revision number
5208 *
5209 * NOTES
5210 * Return NULL if no matching records were found.
5211 *
5212 * HISTORY
5213 * 15-Mar-05 Abhishek Trivedi Created
5214 ********************************************************************/
5215 FUNCTION GET_LINE_ADV_AMOUNT_REVISION(
5216 p_po_line_id IN NUMBER, p_revision_num IN NUMBER)
5217 RETURN NUMBER as
5218
5219 l_api_name CONSTANT VARCHAR2(30) := 'GET_LINE_ADV_AMOUNT_REVISION';
5220 l_api_version CONSTANT NUMBER := 1.0;
5221
5222 l_advance_amt NUMBER := 0;
5223 x_advance_amt NUMBER := 0;
5224
5225 CURSOR l_advance_amt_csr IS
5226 select PLL.amount
5227 from po_line_locations_archive_all PLL
5228 where PLL.po_line_id = p_po_line_id
5229 and PLL.payment_type = 'ADVANCE'
5230 and PLL.shipment_type = 'PREPAYMENT'
5231 and PLL.revision_num = (select max(revision_num) from po_line_locations_archive_all PLL1
5232 where PLL1.line_location_id = PLL.line_location_id
5233 and PLL1.revision_num <= p_revision_num);
5234 BEGIN
5235
5236 -- Advance Amount, should have only one
5237 OPEN l_advance_amt_csr;
5238 LOOP
5239 FETCH l_advance_amt_csr INTO l_advance_amt;
5240 EXIT WHEN l_advance_amt_csr%NOTFOUND;
5241 END LOOP;
5242 CLOSE l_advance_amt_csr;
5243
5244 x_advance_amt := nvl(l_advance_amt, 0);
5245
5246 RETURN x_advance_amt;
5247
5248 EXCEPTION
5249 WHEN OTHERS THEN
5250 X_advance_amt := 0;
5251 RETURN x_advance_amt;
5252
5253 END GET_LINE_ADV_AMOUNT_REVISION;
5254
5255 /*********************************************************************
5256 * NAME
5257 * GET_SHIP_VAL_PERCENT_REVISION
5258 *
5259 * PURPOSE
5260 * Calculates value percent for a shipment
5261 *
5262 * ARGUMENTS
5263 * p_po_line_location_id po_line_id
5264 * p_revision_num revision number
5265 *
5266 * NOTES
5267 * Return NULL if no matching records were found.
5268 *
5269 * HISTORY
5270 * 15-Mar-05 Abhishek Trivedi Created
5271 ********************************************************************/
5272 FUNCTION Get_ship_val_percent_revision (
5273 p_po_line_location_id IN NUMBER, p_revision_num IN NUMBER)
5274 RETURN NUMBER as
5275
5276 l_value_percent NUMBER := 0;
5277 x_value_percent NUMBER := 0;
5278
5279 CURSOR l_value_percent_csr IS
5280 select ROUND(DECODE(PLL.matching_basis,
5281 'AMOUNT', (NVL(PLL.amount, 0)/POL.amount)*100,
5282 'QUANTITY', (NVL(PLL.price_override, 0)/POL.unit_price)*100))
5283 from PO_LINE_LOCATIONS_ARCHIVE_ALL PLL,
5284 PO_LINES_ARCHIVE_ALL POL
5285 where PLL.po_line_id = POL.po_line_id
5286 and PLL.line_location_id = p_po_line_location_id
5287 and PLL.payment_type = 'MILESTONE'
5288 and PLL.revision_num = (select max(revision_num) from po_line_locations_archive_all PLL1
5289 where PLL1.line_location_id = PLL.line_location_id
5290 and PLL1.revision_num <= p_revision_num)
5291 and POL.revision_num = (select max(revision_num) from po_lines_archive_all POL1
5292 where POL1.po_line_id = POL.po_line_id
5293 and POL1.revision_num <= p_revision_num);
5294
5295 BEGIN
5296
5297 -- Value Percent, only valid for milestone pay items.
5298 OPEN l_value_percent_csr;
5299 LOOP
5300 FETCH l_value_percent_csr INTO l_value_percent;
5301 EXIT WHEN l_value_percent_csr%NOTFOUND;
5302 END LOOP;
5303 CLOSE l_value_percent_csr;
5304
5305 x_value_percent := nvl(l_value_percent, 0);
5306 RETURN X_value_percent;
5307
5308 EXCEPTION
5309 WHEN OTHERS THEN
5310 X_value_percent := 0;
5311 RETURN X_value_percent;
5312
5313 END Get_ship_val_percent_revision;
5314
5315
5316 END pos_compare_revisions;