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