[Home] [Help]
PACKAGE BODY: APPS.PO_ARCHIVE_DOCUMENT_SV
Source
1 PACKAGE BODY PO_ARCHIVE_DOCUMENT_SV AS
2 /* $Header: POXPIARB.pls 115.9 2003/01/10 21:59:33 pparthas ship $ */
3
4 /*
5 DESCRIPTION: Archiving code for Purchase Orders
6 Can archive Blanket PAs using this API
7
8 OWNER: Imran Ali
9
10 CHANGE HISTORY: Created 02/17/98 Iali
11 03/30/00 Preetam Bamb (GML-OPM)
12 Added 5 fields in the insert statement of PO_LINES_ARCHIVE view.
13 */
14
15 -- *************************************************************************************** --
16
17 --
18 -- PRIVATE PROCEDURES.
19 --
20
21 -- Archive PO Header
22
23 procedure archive_header(X_po_header_id IN NUMBER);
24
25 -- Archive PO Lines
26
27 procedure archive_lines(X_po_header_id IN NUMBER, X_revision_num IN NUMBER);
28
29 -- Archive PO Line locations
30
31 procedure archive_line_locations(X_po_header_id IN NUMBER, X_revision_num IN NUMBER);
32
33
34 -- *************************************************************************************** --
35
36
37 --
38 -- PROCEDURE Archive_PO
39 --
40
41 --
42 -- Archives the specified PO.
43 -- The procedure assumes that the document requires archving.
44 -- It does not perform any validation to check if archiving is required.
45 -- The calling program must do this validation.
46 --
47
48 --
49 -- In case of any error all changes to the archive table are rolled back
50 -- and a result of FALSE is returned.
51 --
52
53 PROCEDURE Archive_PO (X_po_header_id IN NUMBER, X_result OUT NOCOPY BOOLEAN)
54 is
55
56 l_revision_num number;
57
58 Begin
59
60 X_result := TRUE;
61 savepoint archiving_po_document;
62
63 begin
64 select revision_num into l_revision_num
65 from po_headers
66 where po_header_id = X_po_header_id;
67
68 exception
69 when others then
70 l_revision_num := 0;
71 end;
72
73 begin
74
75 -- Archive PO header
76
77 archive_header(X_po_header_id);
78
79 -- Archive PO Lines
80
81 archive_lines(X_po_header_id, l_revision_num);
82
83 -- Archive PO Line locations
84
85 archive_line_locations(X_po_header_id, l_revision_num);
86
87 exception
88 when others then
89 X_result := FALSE;
90 end;
91
92 if not X_result then
93 rollback to archiving_po_document;
94 end if;
95
96 End;
97
98
99 -- *************************************************************************************** --
100
101 --
102 -- PRIVATE PROCEDURES.
103 --
104
105 -- Archive PO Header
106
107 procedure archive_header(X_po_header_id IN NUMBER)
108 is
109 begin
110
111 -- Set the latest_external_flag of the archived header to 'N'.
112
113 UPDATE PO_HEADERS_ARCHIVE
114 SET latest_external_flag = 'N'
115 WHERE po_header_id = X_po_header_id
116 AND latest_external_flag = 'Y';
117
118 /* Archive the header.
119 This will be an exact copy of po_headers except for
120 the latest_external_flag. Keep the columns in
121 alphabetical order for easy verification.
122 */
123 INSERT INTO PO_HEADERS_ARCHIVE
124 (
125 acceptance_due_date ,
126 acceptance_required_flag ,
127 agent_id ,
128 amount_limit ,
129 approval_required_flag ,
130 approved_date ,
131 approved_flag ,
132 attribute1 ,
133 attribute10 ,
134 attribute11 ,
135 attribute12 ,
136 attribute13 ,
137 attribute14 ,
138 attribute15 ,
139 attribute2 ,
140 attribute3 ,
141 attribute4 ,
142 attribute5 ,
143 attribute6 ,
144 attribute7 ,
145 attribute8 ,
146 attribute9 ,
147 attribute_category ,
148 authorization_status ,
149 bill_to_location_id ,
150 blanket_total_amount ,
151 cancel_flag ,
152 closed_code ,
153 closed_date ,
154 comments ,
155 confirming_order_flag ,
156 created_by ,
157 creation_date ,
158 currency_code ,
159 enabled_flag ,
160 end_date ,
161 end_date_active ,
162 firm_status_lookup_code ,
163 fob_lookup_code ,
164 freight_terms_lookup_code ,
165 from_header_id ,
166 from_type_lookup_code ,
167 frozen_flag ,
168 government_context ,
169 global_agreement_flag , -- FPI GA
170 last_updated_by ,
171 last_update_date ,
172 last_update_login ,
173 latest_external_flag ,
174 min_release_amount ,
175 note_to_authorizer ,
176 note_to_receiver ,
177 note_to_vendor ,
178 po_header_id ,
179 printed_date ,
180 print_count ,
181 program_application_id ,
182 program_id ,
183 program_update_date ,
184 quotation_class_code ,
185 quote_type_lookup_code ,
186 quote_vendor_quote_number ,
187 quote_warning_delay ,
188 quote_warning_delay_unit ,
189 rate ,
190 rate_date ,
191 rate_type ,
192 reply_date ,
193 reply_method_lookup_code ,
194 request_id ,
195 revised_date ,
196 revision_num ,
197 rfq_close_date ,
198 segment1 ,
199 segment2 ,
200 segment3 ,
201 segment4 ,
202 segment5 ,
203 ship_to_location_id ,
204 ship_via_lookup_code ,
205 start_date ,
206 start_date_active ,
207 summary_flag ,
208 terms_id ,
209 type_lookup_code ,
210 user_hold_flag ,
211 ussgl_transaction_code ,
212 vendor_contact_id ,
213 vendor_id ,
214 vendor_order_num ,
215 vendor_site_id ,
216 consigned_consumption_flag ) -- FPI Consigned Inventory
217 SELECT
218 acceptance_due_date ,
219 acceptance_required_flag ,
220 agent_id ,
221 amount_limit ,
222 approval_required_flag ,
223 approved_date ,
224 approved_flag ,
225 attribute1 ,
226 attribute10 ,
227 attribute11 ,
228 attribute12 ,
229 attribute13 ,
230 attribute14 ,
231 attribute15 ,
232 attribute2 ,
233 attribute3 ,
234 attribute4 ,
235 attribute5 ,
236 attribute6 ,
237 attribute7 ,
238 attribute8 ,
239 attribute9 ,
240 attribute_category ,
241 authorization_status ,
242 bill_to_location_id ,
243 blanket_total_amount ,
244 cancel_flag ,
245 closed_code ,
246 closed_date ,
247 comments ,
248 confirming_order_flag ,
249 created_by ,
250 creation_date ,
251 currency_code ,
252 enabled_flag ,
253 end_date ,
254 end_date_active ,
255 firm_status_lookup_code ,
256 fob_lookup_code ,
257 freight_terms_lookup_code ,
258 from_header_id ,
259 from_type_lookup_code ,
260 frozen_flag ,
261 government_context ,
262 global_agreement_flag , -- FPI GA
263 last_updated_by ,
264 last_update_date ,
265 last_update_login ,
266 'Y' ,
267 min_release_amount ,
268 note_to_authorizer ,
269 note_to_receiver ,
270 note_to_vendor ,
271 po_header_id ,
272 printed_date ,
273 print_count ,
274 program_application_id ,
275 program_id ,
276 program_update_date ,
277 quotation_class_code ,
278 quote_type_lookup_code ,
279 quote_vendor_quote_number ,
280 quote_warning_delay ,
281 quote_warning_delay_unit ,
282 rate ,
283 rate_date ,
284 rate_type ,
285 reply_date ,
286 reply_method_lookup_code ,
287 request_id ,
288 revised_date ,
289 revision_num ,
290 rfq_close_date ,
291 segment1 ,
292 segment2 ,
293 segment3 ,
294 segment4 ,
295 segment5 ,
296 ship_to_location_id ,
297 ship_via_lookup_code ,
298 start_date ,
299 start_date_active ,
300 summary_flag ,
301 terms_id ,
302 type_lookup_code ,
303 user_hold_flag ,
304 ussgl_transaction_code ,
305 vendor_contact_id ,
306 vendor_id ,
307 vendor_order_num ,
308 vendor_site_id ,
309 consigned_consumption_flag -- FPI Consigned Inventory
310 FROM PO_HEADERS
311 WHERE PO_HEADER_ID = x_po_header_id;
312
313 exception
314 when others then
315 raise;
316 end;
317
318 -- * ----------------------------------------------------------------------------------- * --
319
320 -- Archive PO Lines
321
322 procedure archive_lines(X_po_header_id IN NUMBER, X_revision_num IN NUMBER)
323 is
324 l_first_count number := 0;
325 l_second_count number := 0;
326 begin
327
328 select count(*) into l_first_count
329 from po_lines_archive
330 where po_header_id = x_po_header_id;
331
332 /* Archive the lines.
333 This will be an exact copy of po_lines except for the
334 latest_external_flag and the revision_num. Keep the columns
335 in alphabetical order for easy verification.
336 */
337 INSERT INTO PO_LINES_ARCHIVE
338 (
339 allow_price_override_flag ,
340 attribute1 ,
341 attribute10 ,
342 attribute11 ,
343 attribute12 ,
344 attribute13 ,
345 attribute14 ,
346 attribute15 ,
347 attribute2 ,
348 attribute3 ,
349 attribute4 ,
350 attribute5 ,
351 attribute6 ,
352 attribute7 ,
353 attribute8 ,
354 attribute9 ,
355 attribute_category ,
356 cancelled_by ,
357 cancel_date ,
358 cancel_flag ,
359 cancel_reason ,
360 capital_expense_flag ,
361 category_id ,
362 closed_by ,
363 closed_code ,
364 closed_date ,
365 closed_flag ,
366 closed_reason ,
367 committed_amount ,
368 contract_num ,
369 created_by ,
370 creation_date ,
371 firm_status_lookup_code ,
372 from_header_id ,
373 from_line_id ,
374 government_context ,
375 hazard_class_id ,
376 item_description ,
377 item_id ,
378 item_revision ,
379 last_updated_by ,
380 last_update_date ,
381 last_update_login ,
382 latest_external_flag ,
383 line_num ,
384 line_type_id ,
385 list_price_per_unit ,
386 market_price ,
387 max_order_quantity ,
388 min_order_quantity ,
389 min_release_amount ,
390 negotiated_by_preparer_flag ,
391 note_to_vendor ,
392 not_to_exceed_price ,
393 over_tolerance_error_flag ,
394 po_header_id ,
398 program_application_id ,
395 po_line_id ,
396 price_break_lookup_code ,
397 price_type_lookup_code ,
399 program_id ,
400 program_update_date ,
401 qty_rcv_tolerance ,
402 quantity ,
403 quantity_committed ,
404 reference_num ,
405 request_id ,
406 revision_num ,
407 taxable_flag ,
408 tax_code_id ,
409 transaction_reason_code ,
410 type_1099 ,
411 unit_meas_lookup_code ,
412 unit_price ,
413 unordered_flag ,
414 un_number_id ,
415 user_hold_flag ,
416 ussgl_transaction_code ,
417 vendor_product_num ,
418 expiration_date ,
419 base_qty ,
420 base_uom ,
421 secondary_qty ,
422 secondary_uom ,
423 qc_grade )
424 SELECT
425 POL.allow_price_override_flag ,
426 POL.attribute1 ,
427 POL.attribute10 ,
428 POL.attribute11 ,
429 POL.attribute12 ,
430 POL.attribute13 ,
431 POL.attribute14 ,
432 POL.attribute15 ,
433 POL.attribute2 ,
434 POL.attribute3 ,
435 POL.attribute4 ,
436 POL.attribute5 ,
437 POL.attribute6 ,
438 POL.attribute7 ,
439 POL.attribute8 ,
440 POL.attribute9 ,
441 POL.attribute_category ,
442 POL.cancelled_by ,
443 POL.cancel_date ,
444 POL.cancel_flag ,
445 POL.cancel_reason ,
446 POL.capital_expense_flag ,
447 POL.category_id ,
448 POL.closed_by ,
449 POL.closed_code ,
450 POL.closed_date ,
451 POL.closed_flag ,
452 POL.closed_reason ,
453 POL.committed_amount ,
454 POL.contract_num ,
455 POL.created_by ,
456 POL.creation_date ,
457 POL.firm_status_lookup_code ,
458 POL.from_header_id ,
459 POL.from_line_id ,
460 POL.government_context ,
461 POL.hazard_class_id ,
462 POL.item_description ,
463 POL.item_id ,
464 POL.item_revision ,
465 POL.last_updated_by ,
466 POL.last_update_date ,
467 POL.last_update_login ,
468 'Y' ,
469 POL.line_num ,
470 POL.line_type_id ,
471 POL.list_price_per_unit ,
472 POL.market_price ,
473 POL.max_order_quantity ,
474 POL.min_order_quantity ,
475 POL.min_release_amount ,
476 POL.negotiated_by_preparer_flag ,
477 POL.note_to_vendor ,
478 POL.not_to_exceed_price ,
479 POL.over_tolerance_error_flag ,
480 POL.po_header_id ,
481 POL.po_line_id ,
482 POL.price_break_lookup_code ,
483 POL.price_type_lookup_code ,
484 POL.program_application_id ,
485 POL.program_id ,
486 POL.program_update_date ,
487 POL.qty_rcv_tolerance ,
488 POL.quantity ,
489 POL.quantity_committed ,
490 POL.reference_num ,
491 POL.request_id ,
492 X_revision_num ,
493 POL.taxable_flag ,
494 POL.tax_code_id ,
495 POL.transaction_reason_code ,
496 POL.type_1099 ,
497 POL.unit_meas_lookup_code ,
498 POL.unit_price ,
499 POL.unordered_flag ,
500 POL.un_number_id ,
501 POL.user_hold_flag ,
502 POL.ussgl_transaction_code ,
503 POL.vendor_product_num ,
507 POL.secondary_qty ,
504 POL.expiration_date ,
505 POL.base_qty ,
506 POL.base_uom ,
508 POL.secondary_uom ,
509 POL.qc_grade
510 FROM PO_LINES POL,
511 PO_LINES_ARCHIVE POLA
512 WHERE POL.po_header_id = X_po_header_id
513 AND POL.po_line_id = POLA.po_line_id (+)
514 AND POLA.latest_external_flag (+) = 'Y'
515 AND (
516 (POLA.po_line_id is NULL)
517 OR (POL.line_num <> POLA.line_num)
518 OR (POL.item_id <> POLA.item_id)
519 OR (POL.item_id IS NULL AND POLA.item_id IS NOT NULL)
520 OR (POL.item_id IS NOT NULL AND POLA.item_id IS NULL)
521 OR (POL.item_revision <> POLA.item_revision)
522 OR (POL.item_revision IS NULL AND POLA.item_revision IS NOT NULL)
523 OR (POL.item_revision IS NOT NULL AND POLA.item_revision IS NULL)
524 OR (POL.item_description <> POLA.item_description)
525 OR (POL.item_description IS NULL
526 AND POLA.item_description IS NOT NULL)
527 OR (POL.item_description IS NOT NULL
528 AND POLA.item_description IS NULL)
529 OR (POL.unit_meas_lookup_code <> POLA.unit_meas_lookup_code)
530 OR (POL.unit_meas_lookup_code IS NULL
531 AND POLA.unit_meas_lookup_code IS NOT NULL)
532 OR (POL.unit_meas_lookup_code IS NOT NULL
533 AND POLA.unit_meas_lookup_code IS NULL)
534 OR (POL.quantity_committed <> POLA.quantity_committed)
535 OR (POL.quantity_committed IS NULL
536 AND POLA.quantity_committed IS NOT NULL)
537 OR (POL.quantity_committed IS NOT NULL
538 AND POLA.quantity_committed IS NULL)
539 OR (POL.committed_amount <> POLA.committed_amount)
540 OR (POL.committed_amount IS NULL
541 AND POLA.committed_amount IS NOT NULL)
542 OR (POL.committed_amount IS NOT NULL
543 AND POLA.committed_amount IS NULL)
544 OR (POL.unit_price <> POLA.unit_price)
545 OR (POL.unit_price IS NULL AND POLA.unit_price IS NOT NULL)
546 OR (POL.unit_price IS NOT NULL AND POLA.unit_price IS NULL)
547 OR (POL.un_number_id <> POLA.un_number_id)
548 OR (POL.un_number_id IS NULL AND POLA.un_number_id IS NOT NULL)
549 OR (POL.un_number_id IS NOT NULL AND POLA.un_number_id IS NULL)
550 OR (POL.hazard_class_id <> POLA.hazard_class_id)
551 OR (POL.hazard_class_id IS NULL
552 AND POLA.hazard_class_id IS NOT NULL)
553 OR (POL.hazard_class_id IS NOT NULL
554 AND POLA.hazard_class_id IS NULL)
555 OR (POL.note_to_vendor <> POLA.note_to_vendor)
556 OR (POL.note_to_vendor IS NULL
557 AND POLA.note_to_vendor IS NOT NULL)
558 OR (POL.note_to_vendor IS NOT NULL
559 AND POLA.note_to_vendor IS NULL)
560 OR (POL.from_header_id <> POLA.from_header_id)
561 OR (POL.from_header_id IS NULL
562 AND POLA.from_header_id IS NOT NULL)
563 OR (POL.from_header_id IS NOT NULL
564 AND POLA.from_header_id IS NULL)
565 OR (POL.from_line_id <> POLA.from_line_id)
566 OR (POL.from_line_id IS NULL
567 AND POLA.from_line_id IS NOT NULL)
568 OR (POL.from_line_id IS NOT NULL
569 AND POLA.from_line_id IS NULL)
570 OR (POL.closed_flag = 'Y'
571 AND nvl(POLA.closed_flag, 'N') = 'N')
572 OR (POL.vendor_product_num <> POLA.vendor_product_num)
573 OR (POL.vendor_product_num IS NULL
574 AND POLA.vendor_product_num IS NOT NULL)
575 OR (POL.vendor_product_num IS NOT NULL
576 AND POLA.vendor_product_num IS NULL)
577 OR (POL.contract_num <> POLA.contract_num)
578 OR (POL.contract_num IS NULL
579 AND POLA.contract_num IS NOT NULL)
580 OR (POL.contract_num IS NOT NULL
581 AND POLA.contract_num IS NULL)
582 OR (POL.price_type_lookup_code <> POLA.price_type_lookup_code)
583 OR (POL.price_type_lookup_code IS NULL
584 AND POLA.price_type_lookup_code IS NOT NULL)
585 OR (POL.price_type_lookup_code IS NOT NULL
586 AND POLA.price_type_lookup_code IS NULL)
587 OR (POL.expiration_date <> POLA.expiration_date)
588 OR (POL.expiration_date IS NULL
589 AND POLA.expiration_date IS NOT NULL)
590 OR (POL.expiration_date IS NOT NULL
591 AND POLA.expiration_date IS NULL));
592
593 select count(*) into l_second_count
594 from po_lines_archive
595 where po_header_id = x_po_header_id;
596
597 if l_first_count = l_second_count then
598
599 -- no row inserted
600 null;
601
602 else
603
604 /* Assert: Insert statement processed at least one row.
605 */
606
607 /* Set the latest_external_flag to 'N' for all rows which have:
608 - latest_external_flag = 'Y'
609 - revision_num < X_revision_num (the new revision of the
610 header)
611 - have no new archived row
612 */
613
614 UPDATE PO_LINES_ARCHIVE POL1
615 SET latest_external_flag = 'N'
616 WHERE po_header_id = X_po_header_id
617 AND latest_external_flag = 'Y'
618 AND revision_num < X_revision_num
619 AND EXISTS
620 (SELECT 'A new archived row'
624 AND POL2.revision_num = X_revision_num);
621 FROM PO_LINES_ARCHIVE POL2
622 WHERE POL2.po_line_id = POL1.po_line_id
623 AND POL2.latest_external_flag = 'Y'
625
626 end if;
627
628 exception
629 when others then
630 raise;
631 end;
632
633 -- * ----------------------------------------------------------------------------------- * --
634
635 -- Archive PO Line locations
636
637 procedure archive_line_locations(X_po_header_id IN NUMBER, X_revision_num IN NUMBER)
638 is
639 l_first_count number := 0;
640 l_second_count number := 0;
641 begin
642
643 select count(*) into l_first_count
644 from po_line_locations_archive
645 where po_header_id = x_po_header_id;
646
647 /* Archive the line locations.
648 This will be an exact copy of po_line_locations except for the
649 latest_external_flag and the revision_num. Keep the columns
650 in alphabetical order for easy verification.
651 */
652 /* Bug 2704039. As part of time phased FPI project, we added the
653 * start and end date for a price break. Added these conditions in
654 * the where clause below so that if there are any changes to these
655 * dates, the record will be archived.
656 */
657
658 INSERT INTO PO_LINE_LOCATIONS_ARCHIVE
659 (
660 accrue_on_receipt_flag ,
661 allow_substitute_receipts_flag ,
662 approved_date ,
663 approved_flag ,
664 attribute1 ,
665 attribute10 ,
666 attribute11 ,
667 attribute12 ,
668 attribute13 ,
669 attribute14 ,
670 attribute15 ,
671 attribute2 ,
672 attribute3 ,
673 attribute4 ,
674 attribute5 ,
675 attribute6 ,
676 attribute7 ,
677 attribute8 ,
678 attribute9 ,
679 attribute_category ,
680 cancelled_by ,
681 cancel_date ,
682 cancel_flag ,
683 cancel_reason ,
684 closed_by ,
685 closed_code ,
686 closed_date ,
687 closed_flag ,
688 closed_reason ,
689 created_by ,
690 creation_date ,
691 days_early_receipt_allowed ,
692 days_late_receipt_allowed ,
693 encumbered_date ,
694 encumbered_flag ,
695 encumber_now ,
696 end_date ,
697 enforce_ship_to_location_code ,
698 estimated_tax_amount ,
699 firm_status_lookup_code ,
700 fob_lookup_code ,
701 freight_terms_lookup_code ,
702 from_header_id ,
703 from_line_id ,
704 from_line_location_id ,
705 government_context ,
706 inspection_required_flag ,
707 invoice_close_tolerance ,
708 last_accept_date ,
709 last_updated_by ,
710 last_update_date ,
711 last_update_login ,
712 latest_external_flag ,
713 lead_time ,
714 lead_time_unit ,
715 line_location_id ,
716 need_by_date ,
717 po_header_id ,
718 po_line_id ,
719 po_release_id ,
720 price_discount ,
721 price_override ,
722 program_application_id ,
723 program_id ,
724 program_update_date ,
725 promised_date ,
726 qty_rcv_exception_code ,
727 qty_rcv_tolerance ,
728 quantity ,
729 quantity_accepted ,
730 quantity_billed ,
731 quantity_cancelled ,
732 quantity_received ,
733 quantity_rejected ,
734 receipt_days_exception_code ,
735 receipt_required_flag ,
736 receive_close_tolerance ,
737 receiving_routing_id ,
738 request_id ,
739 revision_num ,
740 shipment_num ,
741 shipment_type ,
742 ship_to_location_id ,
743 ship_to_organization_id ,
744 ship_via_lookup_code ,
745 source_shipment_id ,
746 start_date ,
747 taxable_flag ,
751 unit_meas_lookup_code ,
748 tax_code_id ,
749 terms_id ,
750 unencumbered_quantity ,
752 unit_of_measure_class ,
753 ussgl_transaction_code ,
754 consigned_flag ) -- FPI Consigned Inventory
755 SELECT
756 POL.accrue_on_receipt_flag ,
757 POL.allow_substitute_receipts_flag ,
758 POL.approved_date ,
759 POL.approved_flag ,
760 POL.attribute1 ,
761 POL.attribute10 ,
762 POL.attribute11 ,
763 POL.attribute12 ,
764 POL.attribute13 ,
765 POL.attribute14 ,
766 POL.attribute15 ,
767 POL.attribute2 ,
768 POL.attribute3 ,
769 POL.attribute4 ,
770 POL.attribute5 ,
771 POL.attribute6 ,
772 POL.attribute7 ,
773 POL.attribute8 ,
774 POL.attribute9 ,
775 POL.attribute_category ,
776 POL.cancelled_by ,
777 POL.cancel_date ,
778 POL.cancel_flag ,
779 POL.cancel_reason ,
780 POL.closed_by ,
781 POL.closed_code ,
782 POL.closed_date ,
783 POL.closed_flag ,
784 POL.closed_reason ,
785 POL.created_by ,
786 POL.creation_date ,
787 POL.days_early_receipt_allowed ,
788 POL.days_late_receipt_allowed ,
789 POL.encumbered_date ,
790 POL.encumbered_flag ,
791 POL.encumber_now ,
792 POL.end_date ,
793 POL.enforce_ship_to_location_code ,
794 POL.estimated_tax_amount ,
795 POL.firm_status_lookup_code ,
796 POL.fob_lookup_code ,
797 POL.freight_terms_lookup_code ,
798 POL.from_header_id ,
799 POL.from_line_id ,
800 POL.from_line_location_id ,
801 POL.government_context ,
802 POL.inspection_required_flag ,
803 POL.invoice_close_tolerance ,
804 POL.last_accept_date ,
805 POL.last_updated_by ,
806 POL.last_update_date ,
807 POL.last_update_login ,
808 'Y' ,
809 POL.lead_time ,
810 POL.lead_time_unit ,
811 POL.line_location_id ,
812 POL.need_by_date ,
813 POL.po_header_id ,
814 POL.po_line_id ,
815 POL.po_release_id ,
816 POL.price_discount ,
817 POL.price_override ,
818 POL.program_application_id ,
819 POL.program_id ,
820 POL.program_update_date ,
821 POL.promised_date ,
822 POL.qty_rcv_exception_code ,
823 POL.qty_rcv_tolerance ,
824 POL.quantity ,
825 POL.quantity_accepted ,
826 POL.quantity_billed ,
827 POL.quantity_cancelled ,
828 POL.quantity_received ,
829 POL.quantity_rejected ,
830 POL.receipt_days_exception_code ,
831 POL.receipt_required_flag ,
832 POL.receive_close_tolerance ,
833 POL.receiving_routing_id ,
834 POL.request_id ,
835 X_revision_num ,
836 POL.shipment_num ,
837 POL.shipment_type ,
838 POL.ship_to_location_id ,
839 POL.ship_to_organization_id ,
840 POL.ship_via_lookup_code ,
841 POL.source_shipment_id ,
842 POL.start_date ,
843 POL.taxable_flag ,
844 POL.tax_code_id ,
845 POL.terms_id ,
846 POL.unencumbered_quantity ,
847 POL.unit_meas_lookup_code ,
848 POL.unit_of_measure_class ,
849 POL.ussgl_transaction_code ,
850 POL.consigned_flag -- FPI Consigned Inventory
851 FROM PO_LINE_LOCATIONS POL,
852 PO_LINE_LOCATIONS_ARCHIVE POLA
853 WHERE POL.po_header_id = X_po_header_id
854 AND POL.line_location_id = POLA.line_location_id (+)
855 AND POLA.latest_external_flag (+) = 'Y'
856 AND POL.po_release_id is null
857 AND (
858 (POLA.line_location_id is NULL)
862 OR (POL.ship_to_location_id <> POLA.ship_to_location_id)
859 OR (POL.quantity <> POLA.quantity)
860 OR (POL.quantity IS NULL AND POLA.quantity IS NOT NULL)
861 OR (POL.quantity IS NOT NULL AND POLA.quantity IS NULL)
863 OR (POL.ship_to_location_id IS NULL
864 AND POLA.ship_to_location_id IS NOT NULL)
865 OR (POL.ship_to_location_id IS NOT NULL
866 AND POLA.ship_to_location_id IS NULL)
867 OR (POL.need_by_date <> POLA.need_by_date)
868 OR (POL.need_by_date IS NULL
869 AND POLA.need_by_date IS NOT NULL)
870 OR (POL.need_by_date IS NOT NULL
871 AND POLA.need_by_date IS NULL)
872 OR (POL.promised_date <> POLA.promised_date)
873 OR (POL.promised_date IS NULL
874 AND POLA.promised_date IS NOT NULL)
875 OR (POL.promised_date IS NOT NULL
876 AND POLA.promised_date IS NULL)
877 OR (POL.last_accept_date <> POLA.last_accept_date)
878 OR (POL.last_accept_date IS NULL
879 AND POLA.last_accept_date IS NOT NULL)
880 OR (POL.last_accept_date IS NOT NULL
881 AND POLA.last_accept_date IS NULL)
882 OR (POL.price_override <> POLA.price_override)
883 OR (POL.price_override IS NULL
884 AND POLA.price_override IS NOT NULL)
885 OR (POL.price_override IS NOT NULL
886 AND POLA.price_override IS NULL)
887 OR (POL.taxable_flag <> POLA.taxable_flag)
888 OR (POL.taxable_flag IS NULL
889 AND POLA.taxable_flag IS NOT NULL)
890 OR (POL.taxable_flag IS NOT NULL
891 AND POLA.taxable_flag IS NULL)
892 OR (POL.cancel_flag = 'Y'
893 AND nvl(POLA.cancel_flag,'N') = 'N')
894 OR (POL.shipment_num <> POLA.shipment_num)
895 OR (POL.shipment_num IS NULL
896 AND POLA.shipment_num IS NOT NULL)
897 OR (POL.shipment_num IS NOT NULL
898 AND POLA.shipment_num IS NULL)
899 OR (POL.start_date is not null
900 AND POLA.START_DATE IS NULL)
901 OR (POL.start_date is null
902 AND POLA.START_DATE IS NOT NULL)
903 OR (POL.start_date <> POLA.start_date)
904 OR (POL.end_date is not null
905 AND POLA.end_date IS NULL)
906 OR (POL.end_date is null
907 AND POLA.end_date IS NOT NULL)
908 OR (POL.end_date <> POLA.end_date));
909
910 select count(*) into l_second_count
911 from po_line_locations_archive
912 where po_header_id = x_po_header_id;
913
914 if l_first_count = l_second_count then
915
916 -- no row inserted
917 null;
918
919 else
920
921 /* Assert: At least one row was processed in the sql statement.
922 */
923
924 /* Set the latest_external_flag to 'N' for all rows which have:
925 - latest_external_flag = 'Y'
926 - revision_num < X_revision_num (the new revision of the
927 header)
928 - have no new archived row
929 */
930
931 UPDATE PO_LINE_LOCATIONS_ARCHIVE POL1
932 SET latest_external_flag = 'N'
933 WHERE po_header_id = X_po_header_id
934 AND latest_external_flag = 'Y'
935 AND revision_num < X_revision_num;
936
937 /*
938 Do not need the fol. condition because via EDI we cannot update price breaks.
939 We can only delete the existing price breaks and re-create new price breaks.
940 Hence, even if the line_location_id is not the same we should reset the latest
941 revision flag for older price breaks.
942
943 AND EXISTS
944 (SELECT 'A new archived row'
945 FROM PO_LINE_LOCATIONS_ARCHIVE POL2
946 WHERE POL2.line_location_id = POL1.line_location_id
947 AND POL2.latest_external_flag = 'Y'
948 AND POL2.revision_num = X_revision_num);
949 */
950
951 end if;
952
953 exception
954 when others then
955 raise;
956 end;
957
958
959 end PO_ARCHIVE_DOCUMENT_SV;