[Home] [Help]
PACKAGE BODY: APPS.ECE_PO_ARCHIVE_PKG
Source
1 PACKAGE BODY ECE_PO_ARCHIVE_PKG AS
2 -- $Header: ECEPOARB.pls 120.2 2005/09/28 11:53:06 arsriniv ship $
3
4 -- Private Procedure declarations
5 PROCEDURE PORARDOCUMENT (
6 P_DOCUMENT_TYPE IN VARCHAR2,
7 P_DOCUMENT_SUBTYPE IN VARCHAR2,
8 P_DOCUMENT_ID IN NUMBER);
9 PROCEDURE PORARPOCHECK (
10 P_DOCUMENT_ID IN NUMBER,
11 P_REVISION_NUM OUT NOCOPY NUMBER,
12 P_ARCHIVE_OK OUT NOCOPY VARCHAR2);
13 PROCEDURE PORARERELEASECHECK (
14 P_DOCUMENT_ID IN NUMBER,
15 P_REVISION_NUM OUT NOCOPY NUMBER,
16 P_ARCHIVE_OK OUT NOCOPY VARCHAR2);
17 PROCEDURE PORARHEADER (
18 P_DOCUMENT_ID IN NUMBER);
19 PROCEDURE PORARLINES (
20 P_DOCUMENT_ID IN NUMBER,
21 P_REVISION_NUM IN NUMBER);
22 PROCEDURE PORARSHIPDIST (
23 P_DOCUMENT_ID IN NUMBER,
24 P_REVISION_NUM IN NUMBER);
25 PROCEDURE PORARRELEASE (
26 P_DOCUMENT_ID IN NUMBER,
27 P_REVISION_NUM IN NUMBER);
28
29 global_stack VARCHAR2(2000);
30
31 -- ============================================================================
32 -- Name: porarchive
33 -- Desc: Archving cover routine
34 -- Args: IN: p_document_type
35 -- p_document_subtype
36 -- p_document_id
37 -- p_process - Process that called this routine
38 -- 'PRINT' or 'APPROVE'.
39 -- Err : Any value other than 0 in p_error_code indicates an oracle error
40 -- occurred. Currently the only errors that are raised are oracle
41 -- errors. No other error codes are reserved for special meanings.
42 -- The Oracle Error Message is given in p_error_buf and the context
43 -- or call stack is in p_error_stack.
44 -- Algr: Check if archiving is neccesary. If not, exit.
45 -- Set a savepoint.
46 -- Call porardocument()
47 -- If the archiving was successfull return.
48 -- else rollback to the savepoint and set p_error_code and return.
49 --
50 -- Conditions for archiving:
51 -- archive_external_revision code in PO_DOCUMENT_TYPES
52 -- (PRINT or APPROVE) of given document type must be the same
53 -- as process (PRINT or APPROVE).
54 -- Note: Routine does NOT do a commit, this must be done in the calling
55 -- routine!
56 -- ============================================================================
57
58 PROCEDURE PORARCHIVE (
59 P_DOCUMENT_TYPE IN VARCHAR2,
60 P_DOCUMENT_SUBTYPE IN VARCHAR2,
61 P_DOCUMENT_ID IN NUMBER,
62 P_PROCESS IN VARCHAR2,
63 P_ERROR_CODE OUT NOCOPY NUMBER,
64 P_ERROR_BUF OUT NOCOPY VARCHAR2,
65 P_ERROR_STACK OUT NOCOPY VARCHAR2)
66 IS
67 l_when_to_archive PO_DOCUMENT_TYPES.ARCHIVE_EXTERNAL_REVISION_CODE%TYPE;
68 l_return_status VARCHAR2(1);
69 l_msg_count NUMBER;
70 l_msg_data VARCHAR2(2000);
71
72 BEGIN
73 -- Initialize error_code to 0 - no error
74 -- Initialize global_stack to Null on entry
75 p_error_code := 0;
76 global_stack := NULL;
77
78 -- Set a savepoint so we can rollback when something goes wrong.
79 SAVEPOINT PORAR_1;
80
81 -- Validate the p_process parameter
82 -- If this parameter isn't validated, if an invalid value is allowed
83 -- this function will always succeed without doing anything due to
84 -- comparison between p_process and l_when_to_archive below.
85 IF p_process NOT IN ('PRINT','APPROVE') THEN
86 global_stack := '10';
87 RAISE NO_DATA_FOUND;
88 END IF;
89
90 -- Check if the given document_type and subtype exists and
91 -- if archiving is neccesary for this document.
92
93 SELECT ARCHIVE_EXTERNAL_REVISION_CODE
94 INTO l_when_to_archive
95 FROM PO_DOCUMENT_TYPES
96 WHERE DOCUMENT_TYPE_CODE = p_document_type
97 AND DOCUMENT_SUBTYPE = p_document_subtype;
98
99 -- Check if we need to archive the document.
100 IF p_process = l_when_to_archive THEN
101
102 -- Assert: The routine is called from the print routine and we need
103 -- to archive on print OR it is called from the approval
104 -- routine and we need to archive at approval.
105
106 IF (PO_CODE_RELEASE_GRP.Current_Release >=
107 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J) THEN
108 PO_EDI_INTEGRATION_GRP.archive_po(
109 p_api_version => 1.0,
110 p_document_id => p_document_id,
111 p_document_type => p_document_type,
112 p_document_subtype => p_document_subtype,
113 x_return_status => l_return_status,
114 x_msg_count => l_msg_count,
115 x_msg_data => l_msg_data);
116 ELSE
117 ECE_PO_ARCHIVE_PKG.PORARDOCUMENT(p_document_type,
118 p_document_subtype,
119 p_document_id);
120 END IF;
121 END IF;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 global_stack := 'PORARCHIVE(P_DOCUMENT_TYPE='''||p_document_type||
126 ''' P_DOCUMENT_SUBTYPE='''||p_document_subtype||
127 ''' P_DOCUMENT_ID='''||p_document_id||
128 ''' P_PROCESS='''||p_process||
129 '''):'||global_stack;
130 p_error_code := SQLCODE;
131 p_error_buf := SQLERRM;
132 p_error_stack := global_stack;
133
134 ROLLBACK TO PORAR_1;
135
136 END PORARCHIVE;
137
138 -- ============================================================================
139 -- Name: porardocument
140 -- Desc: Archving routine
141 -- Args: IN: p_document_type
142 -- p_document_subtype
143 -- p_document_id
144 -- Err : Error message context returned in global_stack.
145 -- Algr: Check if the current revision is already archived. If so return.
146 -- Case entity.document_type is
147 -- When PO
148 -- Case entity.document_subtype is
149 -- When STANDARD or PLANNED
150 -- archive PO_HEADERS
151 -- when modified archive PO_LINES, PO_LINE_LOCATIONS and
152 -- PO_DISTRIBUTIONS.
153 -- End Case
154 -- When PA
155 -- Case entity.document_subtype is
156 -- When BLANKET
157 -- archive PO_HEADERS
158 -- when modified archive PO_LINES.
159 -- When CONTRACT
160 -- archive PO_HEADERS
161 -- When RELEASE
162 -- archive PO_RELEASES
163 -- when modified archive PO_LINE_LOCATIONS and PO_DISTRIBUTIONS.
164 -- End Case
165 -- Note: Private Procedure
166 -- ============================================================================
167
168 PROCEDURE PORARDOCUMENT (
169 P_DOCUMENT_TYPE IN VARCHAR2,
170 P_DOCUMENT_SUBTYPE IN VARCHAR2,
171 P_DOCUMENT_ID IN NUMBER)
172 IS
173
174 l_archive_ok VARCHAR2(1);
175 l_revision_num NUMBER;
176
177 BEGIN
178 -- Determine what kind of document this is.
179 IF p_document_type = 'PO' then
180 -- Assert: It is a Purchase Order.
181 IF p_document_subtype IN ('STANDARD','PLANNED') THEN
182 -- Assert: It is a Standard or Planned Purchase Order.
183 -- Get the revision number and check if it is different from the
184 -- latest archived version.
185 -- l_revision_num and l_archive_ok are OUT variables and are
186 -- populated by this procedure call
187 ECE_PO_ARCHIVE_PKG.PORARPOCHECK(p_document_id,
188 l_revision_num,
189 l_archive_ok);
190 -- Check if porarcpcheckpo said we need to archive.
191 IF l_archive_ok = 'N' THEN
192 -- Assert: No need to archive.
193 NULL;
194 ELSE
195 -- Archive the Header.
196 ECE_PO_ARCHIVE_PKG.PORARHEADER(p_document_id);
197 -- Archive the Lines.
198 ECE_PO_ARCHIVE_PKG.PORARLINES(p_document_id, l_revision_num);
199 -- Archive the Shipments and Distributions.
200 ECE_PO_ARCHIVE_PKG.PORARSHIPDIST(p_document_id, l_revision_num);
201 END IF;
202 ELSE
203 global_stack := '40'; -- Unknown document subtype
204 raise NO_DATA_FOUND;
205 END IF;
206 ELSIF p_document_type = 'PA' THEN
207 -- Assert: It is a Purchase Agreement.
208 IF p_document_subtype = 'BLANKET' THEN
209 -- Assert: It is a Blanket Purchase Agreement.
210 -- Get the revision number and check if it is different
211 -- from the latest archived version.
212 -- l_revision_num and l_archive_ok are OUT variables and are
213 -- populated by this procedure call
214 ECE_PO_ARCHIVE_PKG.PORARPOCHECK(p_document_id,
215 l_revision_num,
216 l_archive_ok);
217 -- Check if porarpocheck said we need to archive.
218 IF l_archive_ok = 'N' then
219 -- Assert: No need to archive.
220 NULL;
221 ELSE
222 -- Archive the Header.
223 ECE_PO_ARCHIVE_PKG.PORARHEADER(p_document_id);
224 -- Archive the Lines.
225 ECE_PO_ARCHIVE_PKG.PORARLINES(p_document_id, l_revision_num);
226 END IF;
227
228 ELSIF p_document_subtype = 'CONTRACT' THEN
229 -- Assert: It is a Contract Purchase Agreement.
230 ECE_PO_ARCHIVE_PKG.PORARPOCHECK(p_document_id,
231 l_revision_num,
232 l_archive_ok);
233 -- Check if porarpocheck said we need to archive.
234 IF l_archive_ok = 'N' THEN
235 -- Assert: No need to archive.
236 NULL;
237 ELSE
238 -- Archive the Header.
239 ECE_PO_ARCHIVE_PKG.PORARHEADER(p_document_id);
240 END IF;
241 ELSE
242 global_stack := '30'; -- Unknown document subtype
243 RAISE NO_DATA_FOUND;
244 END IF;
245
246 ELSIF p_document_type = 'RELEASE' THEN
247 -- Assert: It is a Release.
248 IF p_document_subtype IN ('SCHEDULED', 'BLANKET') THEN
249 -- Assert: It is a Scheduled Release or a Blanket Release.
250 -- Get the revision number and check if it is
251 -- different from the latest archived version.
252 -- l_revision_num and l_archive_ok are OUT variables and are
253 -- populated by this procedure call
254 ECE_PO_ARCHIVE_PKG.PORARERELEASECHECK(p_document_id,
255 l_revision_num,
256 l_archive_ok);
257 -- Check if porarereleasecheck said we need to archive.
258 IF l_archive_ok = 'N' THEN
259 -- Assert: No need to archive.
260 NULL;
261 ELSE
262 ECE_PO_ARCHIVE_PKG.PORARRELEASE(p_document_id, l_revision_num);
263 END IF;
264 ELSE
265 global_stack := '20'; -- Unknown document subtype
266 raise NO_DATA_FOUND;
267 END IF;
268 ELSE
269 global_stack := '10'; -- Unknown document type
270 raise NO_DATA_FOUND;
271 END IF;
272 EXCEPTION
273 WHEN OTHERS THEN
274 global_stack := 'PORARDOCUMENT(P_DOCUMENT_TYPE='''||p_document_type||
275 ''' P_DOCUMENT_SUBTYPE='''||p_document_subtype||
276 ''' P_DOCUMENT_ID='''||p_document_id||
277 '''):'||global_stack;
278 RAISE;
279
280 END PORARDOCUMENT;
281
282 -- ============================================================================
283 -- Name: porarpocheck
284 -- Desc: Get the current revision number and check if it is already archived.
285 -- Args: IN: p_document_id - The unique identifier of the Purchase Order.
286 -- OUT: p_revision_num - The revision number of the Purchase Order.
287 -- p_archive_ok - Do we need to archive? 'Y' or 'N'.
288 -- Err : Add context to global_stack
289 -- Algr: Select the given PO and compare the revision number with
290 -- the revision number of the latest archived version.
291 -- If current revision_num = latest archived revision_num
292 -- archive_ok = 'N'
293 -- Else
294 -- archive_ok = 'Y'
295 -- Note: 1) Private function - Used only in porardocument()
296 -- ============================================================================
297
298
299 PROCEDURE PORARPOCHECK (
300 P_DOCUMENT_ID IN NUMBER,
301 P_REVISION_NUM OUT NOCOPY NUMBER,
302 P_ARCHIVE_OK OUT NOCOPY VARCHAR2)
303 IS
304 l_revision_num NUMBER DEFAULT 0;
305 l_archived_revision_num NUMBER DEFAULT -1;
306 BEGIN
307 -- Check if the Purchase Order exists, get the revision number.
308 -- If revision number does not exist (which should never be the
309 -- case), we default to 0.
310 SELECT NVL(REVISION_NUM, 0)
311 INTO l_revision_num
312 FROM PO_HEADERS
313 WHERE PO_HEADER_ID = p_document_id;
314 -- Assert: Purchase Order exists.
315 -- Check if Purchase Order is already archived.
316 -- If revision_num does not exist (which should never be the
317 -- case), we default to -1. In case no archive record exists
318 -- revision_num will be -1 due to initialization.
319 BEGIN
320
321 SELECT NVL(REVISION_NUM, -1)
322 INTO l_archived_revision_num
323 FROM PO_HEADERS_ARCHIVE
324 WHERE PO_HEADER_ID = p_document_id
325 AND LATEST_EXTERNAL_FLAG = 'Y';
326
327 EXCEPTION
328 WHEN NO_DATA_FOUND THEN
329 NULL;
330 END;
331
332
333 IF l_revision_num = l_archived_revision_num THEN
334 -- Assert: The current revision of the Purchase Order is already
335 -- archived.
336 p_archive_ok := 'N';
337 ELSE
338 -- Assert: We need to archive; revision_num is different or there is
339 -- no previously archived record (archived_revision_num = -1).
340 p_archive_ok := 'Y';
341 END IF;
342
343 p_revision_num := l_revision_num;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 global_stack := 'PORARPOCHECK(P_DOCUMENT_ID='''||p_document_id||
348 '''):'||global_stack;
349 RAISE;
350
351 END PORARPOCHECK;
352
353 -- ============================================================================
354 -- Name: porarereleasecheck
355 -- Desc: Get the current revision number of the Release and check if it is
356 -- already archived.
357 -- Args: IN: p_document_id - The unique identifier of the Release.
358 -- OUT: p_revision_num - The Revision number of the Release.
359 -- p_archive_ok - Do we need to archive. 'Y' or 'N'.
360 -- Err : Add context to global stack reraise error
361 -- Algr: Select the given Revision and compare the revision number
362 -- with the revision number of the latest archived version.
363 -- If current revision_num = latest archived revision_num
364 -- archive_ok = 'N'
365 -- Else
366 -- archive_ok = 'Y'
367 -- Note: 1) Private function - Used only in porardocument.
368 -- ============================================================================
369
370 PROCEDURE PORARERELEASECHECK (
371 P_DOCUMENT_ID IN NUMBER,
372 P_REVISION_NUM OUT NOCOPY NUMBER,
373 P_ARCHIVE_OK OUT NOCOPY VARCHAR2)
374 IS
375
376 l_revision_num NUMBER DEFAULT 0;
377 l_archived_revision_num NUMBER DEFAULT -1;
378
379 BEGIN
380 -- Check if the Relase exists, get the revision number.
381 -- If the revision_num does not exist (which should never be the
382 -- case), we default to 0.
383 SELECT NVL(REVISION_NUM,0)
384 INTO l_revision_num
385 FROM PO_RELEASES
386 WHERE PO_RELEASE_ID = p_document_id;
387
388 -- Assert: Release exists.
389 -- Check if Release is already archived.
390 -- If the revision_num does not exist (which should never be the
391 -- case), we default to -1.
392 BEGIN
393
394 SELECT NVL(REVISION_NUM,-1)
395 INTO l_archived_revision_num
396 FROM PO_RELEASES_ARCHIVE
397 WHERE PO_RELEASE_ID = p_document_id
398 AND LATEST_EXTERNAL_FLAG = 'Y';
399
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 NULL;
403 END;
404
405 IF l_revision_num = l_archived_revision_num THEN
406 -- Assert: The current revision of the Release is already archived.
407 p_archive_ok := 'N';
408 ELSE
409 -- Assert: We need to archive; revision_num is different or there is
410 -- no previously archived record (archived_revision_num = -1).
411 p_archive_ok := 'Y';
412 END IF;
413
414 p_revision_num := l_revision_num;
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 global_stack := 'PORARERELEASECHECK(P_DOCUMENT_ID='''||p_document_id||
419 '''):'||global_stack;
420 RAISE;
421
422 END PORARERELEASECHECK;
423
424
425 -- ============================================================================
426 -- Name: porarheader
427 -- Desc: Archive PO_HEADERS
428 -- Args: IN: p_document_id - The unique identifier of the Purchase Order
429 -- Err : Error message context returned in global_stack.
430 -- Algr: Set the LATEST_EXTERNAL_FLAG of the current archived header to "N"
431 -- Archive the Header.
432 -- Note: Private Procedure
433 -- ============================================================================
434
435 PROCEDURE PORARHEADER (
436 P_DOCUMENT_ID IN NUMBER)
437 IS
438
439 BEGIN
440
441 UPDATE PO_HEADERS_ARCHIVE
442 SET LATEST_EXTERNAL_FLAG = 'N'
443 WHERE PO_HEADER_ID = p_document_id
444 AND LATEST_EXTERNAL_FLAG = 'Y';
445
446 -- Archive the header.
447 -- This will be an exact copy of po_headers except for
448 -- the latest_external_flag. Keep the columns in
449 -- alphabetical order for easy verification.
450 INSERT INTO PO_HEADERS_ARCHIVE
451 (
452 ACCEPTANCE_DUE_DATE ,
453 ACCEPTANCE_REQUIRED_FLAG ,
454 AGENT_ID ,
455 AMOUNT_LIMIT ,
456 APPROVAL_REQUIRED_FLAG ,
457 APPROVED_DATE ,
458 APPROVED_FLAG ,
459 ATTRIBUTE1 ,
460 ATTRIBUTE10 ,
461 ATTRIBUTE11 ,
462 ATTRIBUTE12 ,
463 ATTRIBUTE13 ,
464 ATTRIBUTE14 ,
465 ATTRIBUTE15 ,
466 ATTRIBUTE2 ,
467 ATTRIBUTE3 ,
468 ATTRIBUTE4 ,
469 ATTRIBUTE5 ,
470 ATTRIBUTE6 ,
471 ATTRIBUTE7 ,
472 ATTRIBUTE8 ,
473 ATTRIBUTE9 ,
474 ATTRIBUTE_CATEGORY ,
475 AUTHORIZATION_STATUS ,
476 BILL_TO_LOCATION_ID ,
477 BLANKET_TOTAL_AMOUNT ,
478 CANCEL_FLAG ,
479 CLOSED_CODE ,
480 CLOSED_DATE ,
481 COMMENTS ,
482 CONFIRMING_ORDER_FLAG ,
483 CREATED_BY ,
484 CREATION_DATE ,
485 CURRENCY_CODE ,
486 ENABLED_FLAG ,
487 END_DATE ,
488 END_DATE_ACTIVE ,
489 FIRM_STATUS_LOOKUP_CODE ,
490 FOB_LOOKUP_CODE ,
491 FREIGHT_TERMS_LOOKUP_CODE ,
492 FROM_HEADER_ID ,
493 FROM_TYPE_LOOKUP_CODE ,
494 FROZEN_FLAG ,
495 GOVERNMENT_CONTEXT ,
496 LAST_UPDATED_BY ,
497 LAST_UPDATE_DATE ,
498 LAST_UPDATE_LOGIN ,
499 LATEST_EXTERNAL_FLAG ,
500 MIN_RELEASE_AMOUNT ,
501 NOTE_TO_AUTHORIZER ,
502 NOTE_TO_RECEIVER ,
503 NOTE_TO_VENDOR ,
504 PO_HEADER_ID ,
505 PRINTED_DATE ,
506 PRINT_COUNT ,
507 PROGRAM_APPLICATION_ID ,
508 PROGRAM_ID ,
509 PROGRAM_UPDATE_DATE ,
510 QUOTATION_CLASS_CODE ,
511 QUOTE_TYPE_LOOKUP_CODE ,
512 QUOTE_VENDOR_QUOTE_NUMBER ,
513 QUOTE_WARNING_DELAY ,
514 QUOTE_WARNING_DELAY_UNIT ,
515 RATE ,
516 RATE_DATE ,
517 RATE_TYPE ,
518 REPLY_DATE ,
519 REPLY_METHOD_LOOKUP_CODE ,
520 REQUEST_ID ,
521 REVISED_DATE ,
522 REVISION_NUM ,
523 RFQ_CLOSE_DATE ,
524 SEGMENT1 ,
525 SEGMENT2 ,
526 SEGMENT3 ,
527 SEGMENT4 ,
528 SEGMENT5 ,
529 SHIP_TO_LOCATION_ID ,
530 SHIP_VIA_LOOKUP_CODE ,
531 START_DATE ,
532 START_DATE_ACTIVE ,
533 SUMMARY_FLAG ,
534 TERMS_ID ,
535 TYPE_LOOKUP_CODE ,
536 USER_HOLD_FLAG ,
537 USSGL_TRANSACTION_CODE ,
538 VENDOR_CONTACT_ID ,
539 VENDOR_ID ,
540 VENDOR_ORDER_NUM ,
541 VENDOR_SITE_ID )
542 SELECT
543 ACCEPTANCE_DUE_DATE ,
544 ACCEPTANCE_REQUIRED_FLAG ,
545 AGENT_ID ,
546 AMOUNT_LIMIT ,
547 APPROVAL_REQUIRED_FLAG ,
548 APPROVED_DATE ,
549 APPROVED_FLAG ,
550 ATTRIBUTE1 ,
551 ATTRIBUTE10 ,
552 ATTRIBUTE11 ,
553 ATTRIBUTE12 ,
554 ATTRIBUTE13 ,
555 ATTRIBUTE14 ,
556 ATTRIBUTE15 ,
557 ATTRIBUTE2 ,
558 ATTRIBUTE3 ,
559 ATTRIBUTE4 ,
560 ATTRIBUTE5 ,
561 ATTRIBUTE6 ,
562 ATTRIBUTE7 ,
563 ATTRIBUTE8 ,
564 ATTRIBUTE9 ,
565 ATTRIBUTE_CATEGORY ,
566 AUTHORIZATION_STATUS ,
567 BILL_TO_LOCATION_ID ,
568 BLANKET_TOTAL_AMOUNT ,
569 CANCEL_FLAG ,
570 CLOSED_CODE ,
571 CLOSED_DATE ,
572 COMMENTS ,
573 CONFIRMING_ORDER_FLAG ,
574 CREATED_BY ,
575 CREATION_DATE ,
576 CURRENCY_CODE ,
577 ENABLED_FLAG ,
578 END_DATE ,
579 END_DATE_ACTIVE ,
580 FIRM_STATUS_LOOKUP_CODE ,
581 FOB_LOOKUP_CODE ,
582 FREIGHT_TERMS_LOOKUP_CODE ,
583 FROM_HEADER_ID ,
584 FROM_TYPE_LOOKUP_CODE ,
585 FROZEN_FLAG ,
586 GOVERNMENT_CONTEXT ,
587 LAST_UPDATED_BY ,
588 LAST_UPDATE_DATE ,
589 LAST_UPDATE_LOGIN ,
590 'Y' ,
591 MIN_RELEASE_AMOUNT ,
592 NOTE_TO_AUTHORIZER ,
593 NOTE_TO_RECEIVER ,
594 NOTE_TO_VENDOR ,
595 PO_HEADER_ID ,
596 PRINTED_DATE ,
597 PRINT_COUNT ,
598 PROGRAM_APPLICATION_ID ,
599 PROGRAM_ID ,
600 PROGRAM_UPDATE_DATE ,
601 QUOTATION_CLASS_CODE ,
602 QUOTE_TYPE_LOOKUP_CODE ,
603 QUOTE_VENDOR_QUOTE_NUMBER ,
604 QUOTE_WARNING_DELAY ,
605 QUOTE_WARNING_DELAY_UNIT ,
606 RATE ,
607 RATE_DATE ,
608 RATE_TYPE ,
609 REPLY_DATE ,
610 REPLY_METHOD_LOOKUP_CODE ,
611 REQUEST_ID ,
612 REVISED_DATE ,
613 REVISION_NUM ,
614 RFQ_CLOSE_DATE ,
615 SEGMENT1 ,
616 SEGMENT2 ,
617 SEGMENT3 ,
618 SEGMENT4 ,
619 SEGMENT5 ,
620 SHIP_TO_LOCATION_ID ,
621 SHIP_VIA_LOOKUP_CODE ,
622 START_DATE ,
623 START_DATE_ACTIVE ,
624 SUMMARY_FLAG ,
625 TERMS_ID ,
626 TYPE_LOOKUP_CODE ,
627 USER_HOLD_FLAG ,
628 USSGL_TRANSACTION_CODE ,
629 VENDOR_CONTACT_ID ,
630 VENDOR_ID ,
631 VENDOR_ORDER_NUM ,
632 VENDOR_SITE_ID
633 FROM PO_HEADERS
634 WHERE PO_HEADER_ID = p_document_id;
635
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 global_stack := 'PORARRELEASE(P_DOCUMENT_ID='''||p_document_id||
640 '''):'||global_stack;
641 RAISE;
642
643 END PORARHEADER;
644
645 -- ============================================================================
646 -- Name: porarlines
647 -- Desc: Archive PO_LINES
648 -- Args: IN: p_document_id - The unique identifier of the Purchase Order
649 -- p_revision_num - The revision number of the header
650 -- Err : Error message context returned in global_stack.
651 -- Algr: Set the LATEST_EXTERNAL_FLAG of the current archived lines to "N"
652 -- Archive the lines.
653 -- Note: Private Procedure
654 -- ============================================================================
655
656 PROCEDURE PORARLINES (
657 P_DOCUMENT_ID IN NUMBER,
658 P_REVISION_NUM IN NUMBER)
659 IS
660
661 BEGIN
662 -- Archive the lines.
663 -- This will be an exact copy of po_lines except for the
664 -- latest_external_flag and the revision_num. Keep the columns
665 -- in alphabetical order for easy verification.
666 INSERT INTO PO_LINES_ARCHIVE
667 (
668 ALLOW_PRICE_OVERRIDE_FLAG ,
669 ATTRIBUTE1 ,
670 ATTRIBUTE10 ,
671 ATTRIBUTE11 ,
672 ATTRIBUTE12 ,
673 ATTRIBUTE13 ,
674 ATTRIBUTE14 ,
675 ATTRIBUTE15 ,
676 ATTRIBUTE2 ,
677 ATTRIBUTE3 ,
678 ATTRIBUTE4 ,
679 ATTRIBUTE5 ,
680 ATTRIBUTE6 ,
681 ATTRIBUTE7 ,
682 ATTRIBUTE8 ,
683 ATTRIBUTE9 ,
684 ATTRIBUTE_CATEGORY ,
685 CANCELLED_BY ,
686 CANCEL_DATE ,
687 CANCEL_FLAG ,
688 CANCEL_REASON ,
689 CAPITAL_EXPENSE_FLAG ,
690 CATEGORY_ID ,
691 CLOSED_BY ,
692 CLOSED_CODE ,
693 CLOSED_DATE ,
694 CLOSED_FLAG ,
695 CLOSED_REASON ,
696 COMMITTED_AMOUNT ,
697 CONTRACT_NUM ,
698 CREATED_BY ,
699 CREATION_DATE ,
700 FIRM_STATUS_LOOKUP_CODE ,
701 FROM_HEADER_ID ,
702 FROM_LINE_ID ,
703 GOVERNMENT_CONTEXT ,
704 HAZARD_CLASS_ID ,
705 ITEM_DESCRIPTION ,
706 ITEM_ID ,
707 ITEM_REVISION ,
708 LAST_UPDATED_BY ,
709 LAST_UPDATE_DATE ,
710 LAST_UPDATE_LOGIN ,
711 LATEST_EXTERNAL_FLAG ,
712 LINE_NUM ,
713 LINE_TYPE_ID ,
714 LIST_PRICE_PER_UNIT ,
715 MARKET_PRICE ,
716 MAX_ORDER_QUANTITY ,
717 MIN_ORDER_QUANTITY ,
718 MIN_RELEASE_AMOUNT ,
719 NEGOTIATED_BY_PREPARER_FLAG ,
720 NOTE_TO_VENDOR ,
721 NOT_TO_EXCEED_PRICE ,
722 OVER_TOLERANCE_ERROR_FLAG ,
723 PO_HEADER_ID ,
724 PO_LINE_ID ,
725 PRICE_BREAK_LOOKUP_CODE ,
726 PRICE_TYPE_LOOKUP_CODE ,
727 PROGRAM_APPLICATION_ID ,
728 PROGRAM_ID ,
729 PROGRAM_UPDATE_DATE ,
730 QTY_RCV_TOLERANCE ,
731 QUANTITY ,
732 QUANTITY_COMMITTED ,
733 REFERENCE_NUM ,
734 REQUEST_ID ,
735 REVISION_NUM ,
736 TAXABLE_FLAG ,
737 TRANSACTION_REASON_CODE ,
738 TYPE_1099 ,
739 UNIT_MEAS_LOOKUP_CODE ,
740 UNIT_PRICE ,
741 UNORDERED_FLAG ,
742 UN_NUMBER_ID ,
743 USER_HOLD_FLAG ,
744 USSGL_TRANSACTION_CODE ,
745 VENDOR_PRODUCT_NUM )
746 SELECT
747 POL.ALLOW_PRICE_OVERRIDE_FLAG ,
748 POL.ATTRIBUTE1 ,
749 POL.ATTRIBUTE10 ,
750 POL.ATTRIBUTE11 ,
751 POL.ATTRIBUTE12 ,
752 POL.ATTRIBUTE13 ,
753 POL.ATTRIBUTE14 ,
754 POL.ATTRIBUTE15 ,
755 POL.ATTRIBUTE2 ,
756 POL.ATTRIBUTE3 ,
757 POL.ATTRIBUTE4 ,
758 POL.ATTRIBUTE5 ,
759 POL.ATTRIBUTE6 ,
760 POL.ATTRIBUTE7 ,
761 POL.ATTRIBUTE8 ,
762 POL.ATTRIBUTE9 ,
763 POL.ATTRIBUTE_CATEGORY ,
764 POL.CANCELLED_BY ,
765 POL.CANCEL_DATE ,
766 POL.CANCEL_FLAG ,
767 POL.CANCEL_REASON ,
768 POL.CAPITAL_EXPENSE_FLAG ,
769 POL.CATEGORY_ID ,
770 POL.CLOSED_BY ,
771 POL.CLOSED_CODE ,
772 POL.CLOSED_DATE ,
773 POL.CLOSED_FLAG ,
774 POL.CLOSED_REASON ,
775 POL.COMMITTED_AMOUNT ,
776 POL.CONTRACT_NUM ,
777 POL.CREATED_BY ,
778 POL.CREATION_DATE ,
779 POL.FIRM_STATUS_LOOKUP_CODE ,
780 POL.FROM_HEADER_ID ,
781 POL.FROM_LINE_ID ,
782 POL.GOVERNMENT_CONTEXT ,
783 POL.HAZARD_CLASS_ID ,
784 POL.ITEM_DESCRIPTION ,
785 POL.ITEM_ID ,
786 POL.ITEM_REVISION ,
787 POL.LAST_UPDATED_BY ,
788 POL.LAST_UPDATE_DATE ,
789 POL.LAST_UPDATE_LOGIN ,
790 'Y' ,
791 POL.LINE_NUM ,
792 POL.LINE_TYPE_ID ,
793 POL.LIST_PRICE_PER_UNIT ,
794 POL.MARKET_PRICE ,
795 POL.MAX_ORDER_QUANTITY ,
796 POL.MIN_ORDER_QUANTITY ,
797 POL.MIN_RELEASE_AMOUNT ,
798 POL.NEGOTIATED_BY_PREPARER_FLAG ,
799 POL.NOTE_TO_VENDOR ,
800 POL.NOT_TO_EXCEED_PRICE ,
801 POL.OVER_TOLERANCE_ERROR_FLAG ,
802 POL.PO_HEADER_ID ,
803 POL.PO_LINE_ID ,
804 POL.PRICE_BREAK_LOOKUP_CODE ,
805 POL.PRICE_TYPE_LOOKUP_CODE ,
806 POL.PROGRAM_APPLICATION_ID ,
807 POL.PROGRAM_ID ,
808 POL.PROGRAM_UPDATE_DATE ,
809 POL.QTY_RCV_TOLERANCE ,
810 POL.QUANTITY ,
811 POL.QUANTITY_COMMITTED ,
812 POL.REFERENCE_NUM ,
813 POL.REQUEST_ID ,
814 p_revision_num ,
815 POL.TAXABLE_FLAG ,
816 POL.TRANSACTION_REASON_CODE ,
817 POL.TYPE_1099 ,
818 POL.UNIT_MEAS_LOOKUP_CODE ,
819 POL.UNIT_PRICE ,
820 POL.UNORDERED_FLAG ,
821 POL.UN_NUMBER_ID ,
822 POL.USER_HOLD_FLAG ,
823 POL.USSGL_TRANSACTION_CODE ,
824 POL.VENDOR_PRODUCT_NUM
825 FROM PO_LINES POL,
826 PO_LINES_ARCHIVE POLA
827 WHERE POL.PO_HEADER_ID = p_document_id
828 AND POL.PO_LINE_ID = POLA.PO_LINE_ID (+)
829 AND POLA.LATEST_EXTERNAL_FLAG (+) = 'Y'
830 AND (
831 (POLA.PO_LINE_ID IS NULL)
832 OR (POL.LINE_NUM <> POLA.LINE_NUM)
833 OR (POL.QUANTITY <> POLA.QUANTITY)
834 OR (POL.QUANTITY IS NULL AND POLA.QUANTITY IS NOT NULL)
835 OR (POL.QUANTITY IS NOT NULL AND POLA.QUANTITY IS NULL)
836 OR (POL.ITEM_ID <> POLA.ITEM_ID)
837 OR (POL.ITEM_ID IS NULL AND POLA.ITEM_ID IS NOT NULL)
838 OR (POL.ITEM_ID IS NOT NULL AND POLA.ITEM_ID IS NULL)
839 OR (POL.ITEM_REVISION <> POLA.ITEM_REVISION)
840 OR (POL.ITEM_REVISION IS NULL AND POLA.ITEM_REVISION IS NOT NULL)
841 OR (POL.ITEM_REVISION IS NOT NULL AND POLA.ITEM_REVISION IS NULL)
842 OR (POL.ITEM_DESCRIPTION <> POLA.ITEM_DESCRIPTION)
843 OR (POL.ITEM_DESCRIPTION IS NULL
844 AND POLA.ITEM_DESCRIPTION IS NOT NULL)
845 OR (POL.ITEM_DESCRIPTION IS NOT NULL
846 AND POLA.ITEM_DESCRIPTION IS NULL)
847 OR (POL.UNIT_MEAS_LOOKUP_CODE <> POLA.UNIT_MEAS_LOOKUP_CODE)
848 OR (POL.UNIT_MEAS_LOOKUP_CODE IS NULL
849 AND POLA.UNIT_MEAS_LOOKUP_CODE IS NOT NULL)
850 OR (POL.UNIT_MEAS_LOOKUP_CODE IS NOT NULL
851 AND POLA.UNIT_MEAS_LOOKUP_CODE IS NULL)
852 OR (POL.QUANTITY_COMMITTED <> POLA.QUANTITY_COMMITTED)
853 OR (POL.QUANTITY_COMMITTED IS NULL
854 AND POLA.QUANTITY_COMMITTED IS NOT NULL)
855 OR (POL.QUANTITY_COMMITTED IS NOT NULL
856 AND POLA.QUANTITY_COMMITTED IS NULL)
857 OR (POL.COMMITTED_AMOUNT <> POLA.COMMITTED_AMOUNT)
858 OR (POL.COMMITTED_AMOUNT IS NULL
859 AND POLA.COMMITTED_AMOUNT IS NOT NULL)
860 OR (POL.COMMITTED_AMOUNT IS NOT NULL
861 AND POLA.COMMITTED_AMOUNT IS NULL)
862 OR (POL.UNIT_PRICE <> POLA.UNIT_PRICE)
863 OR (POL.UNIT_PRICE IS NULL AND POLA.UNIT_PRICE IS NOT NULL)
864 OR (POL.UNIT_PRICE IS NOT NULL AND POLA.UNIT_PRICE IS NULL)
865 OR (POL.UN_NUMBER_ID <> POLA.UN_NUMBER_ID)
866 OR (POL.UN_NUMBER_ID IS NULL AND POLA.UN_NUMBER_ID IS NOT NULL)
867 OR (POL.UN_NUMBER_ID IS NOT NULL AND POLA.UN_NUMBER_ID IS NULL)
868 OR (POL.HAZARD_CLASS_ID <> POLA.HAZARD_CLASS_ID)
869 OR (POL.HAZARD_CLASS_ID IS NULL
870 AND POLA.HAZARD_CLASS_ID IS NOT NULL)
871 OR (POL.HAZARD_CLASS_ID IS NOT NULL
872 AND POLA.HAZARD_CLASS_ID IS NULL)
873 OR (POL.NOTE_TO_VENDOR <> POLA.NOTE_TO_VENDOR)
874 OR (POL.NOTE_TO_VENDOR IS NULL
875 AND POLA.NOTE_TO_VENDOR IS NOT NULL)
876 OR (POL.NOTE_TO_VENDOR IS NOT NULL
877 AND POLA.NOTE_TO_VENDOR IS NULL)
878 OR (POL.FROM_HEADER_ID <> POLA.FROM_HEADER_ID)
879 OR (POL.FROM_HEADER_ID IS NULL
880 AND POLA.FROM_HEADER_ID IS NOT NULL)
881 OR (POL.FROM_HEADER_ID IS NOT NULL
882 AND POLA.FROM_HEADER_ID IS NULL)
883 OR (POL.FROM_LINE_ID <> POLA.FROM_LINE_ID)
884 OR (POL.FROM_LINE_ID IS NULL
885 AND POLA.FROM_LINE_ID IS NOT NULL)
886 OR (POL.FROM_LINE_ID IS NOT NULL
887 AND POLA.FROM_LINE_ID IS NULL)
888 OR (POL.CLOSED_FLAG = 'Y'
889 AND NVL(POLA.CLOSED_FLAG, 'N') = 'N')
890 OR (POL.VENDOR_PRODUCT_NUM <> POLA.VENDOR_PRODUCT_NUM)
891 OR (POL.VENDOR_PRODUCT_NUM IS NULL
892 AND POLA.VENDOR_PRODUCT_NUM IS NOT NULL)
893 OR (POL.VENDOR_PRODUCT_NUM IS NOT NULL
894 AND POLA.VENDOR_PRODUCT_NUM IS NULL)
895 OR (POL.CONTRACT_NUM <> POLA.CONTRACT_NUM)
896 OR (POL.CONTRACT_NUM IS NULL
897 AND POLA.CONTRACT_NUM IS NOT NULL)
898 OR (POL.CONTRACT_NUM IS NOT NULL
899 AND POLA.CONTRACT_NUM IS NULL)
900 OR (POL.PRICE_TYPE_LOOKUP_CODE <> POLA.PRICE_TYPE_LOOKUP_CODE)
901 OR (POL.PRICE_TYPE_LOOKUP_CODE IS NULL
902 AND POLA.PRICE_TYPE_LOOKUP_CODE IS NOT NULL)
903 OR (POL.PRICE_TYPE_LOOKUP_CODE IS NOT NULL
904 AND POLA.PRICE_TYPE_LOOKUP_CODE IS NULL));
905
906 IF SQL%FOUND THEN
907 -- Assert: Insert statement processed at least one row.
908 -- Set the latest_external_flag to 'N' for all rows which have:
909 -- - latest_external_flag = 'Y'
910 -- - revision_num < p_revision_num (the new revision of the
911 -- header)
912 -- - have no new archived row
913 UPDATE PO_LINES_ARCHIVE POL1
914 SET LATEST_EXTERNAL_FLAG = 'N'
915 WHERE PO_HEADER_ID = p_document_id
916 AND LATEST_EXTERNAL_FLAG = 'Y'
917 AND REVISION_NUM < p_revision_num
918 AND EXISTS
919 (SELECT 'A new archived row'
920 FROM PO_LINES_ARCHIVE POL2
921 WHERE POL2.PO_LINE_ID = POL1.PO_LINE_ID
922 AND POL2.LATEST_EXTERNAL_FLAG = 'Y'
923 AND POL2.REVISION_NUM = p_revision_num);
924 END IF;
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 global_stack := 'PORARLINES(P_DOCUMENT_ID='''||p_document_id||
929 ''' P_REVISION_NUM='''||p_revision_num||
930 '''):'||global_stack;
931 RAISE;
932
933 END PORARLINES;
934
935 -- ============================================================================
936 -- Name: porarshipdist
937 -- Desc: Archive PO_LINE_LOCATIONS and PO_DISTRIBUTIONS
938 -- Args: IN: p_document_id - The unique identifier of the Purchase Order
939 -- p_revision_num - The revision number of the header
940 -- Err : Error message context returned in global_stack.
941 -- Algr: Set the LATEST_EXTERNAL_FLAG of the currently archived line locations
942 -- to "N"
943 -- Archive the line locations.
944 -- Set the LATEST_EXTERNAL_FLAG of the currently archived distributions
945 -- to "N"
946 -- Archive the distributions.
947 -- Note: private procedure
948 -- ============================================================================
949
950 PROCEDURE PORARSHIPDIST (
951 P_DOCUMENT_ID IN NUMBER,
952 P_REVISION_NUM IN NUMBER)
953 IS
954
955 BEGIN
956 -- Archive the line locations.
957 -- This will be an exact copy of po_line_locations except for the
958 -- latest_external_flag and the revision_num. Keep the columns
959 -- in alphabetical order for easy verification.
960 INSERT INTO PO_LINE_LOCATIONS_ARCHIVE
961 (
962 ACCRUE_ON_RECEIPT_FLAG ,
963 ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
964 APPROVED_DATE ,
965 APPROVED_FLAG ,
966 ATTRIBUTE1 ,
967 ATTRIBUTE10 ,
968 ATTRIBUTE11 ,
969 ATTRIBUTE12 ,
970 ATTRIBUTE13 ,
971 ATTRIBUTE14 ,
972 ATTRIBUTE15 ,
973 ATTRIBUTE2 ,
974 ATTRIBUTE3 ,
975 ATTRIBUTE4 ,
976 ATTRIBUTE5 ,
977 ATTRIBUTE6 ,
978 ATTRIBUTE7 ,
979 ATTRIBUTE8 ,
980 ATTRIBUTE9 ,
981 ATTRIBUTE_CATEGORY ,
982 CANCELLED_BY ,
983 CANCEL_DATE ,
984 CANCEL_FLAG ,
985 CANCEL_REASON ,
986 CLOSED_BY ,
987 CLOSED_CODE ,
988 CLOSED_DATE ,
989 CLOSED_FLAG ,
990 CLOSED_REASON ,
991 CREATED_BY ,
992 CREATION_DATE ,
993 DAYS_EARLY_RECEIPT_ALLOWED ,
994 DAYS_LATE_RECEIPT_ALLOWED ,
995 ENCUMBERED_DATE ,
996 ENCUMBERED_FLAG ,
997 ENCUMBER_NOW ,
998 END_DATE ,
999 ENFORCE_SHIP_TO_LOCATION_CODE ,
1000 ESTIMATED_TAX_AMOUNT ,
1001 FIRM_STATUS_LOOKUP_CODE ,
1002 FOB_LOOKUP_CODE ,
1003 FREIGHT_TERMS_LOOKUP_CODE ,
1004 FROM_HEADER_ID ,
1005 FROM_LINE_ID ,
1006 FROM_LINE_LOCATION_ID ,
1007 GOVERNMENT_CONTEXT ,
1008 INSPECTION_REQUIRED_FLAG ,
1009 INVOICE_CLOSE_TOLERANCE ,
1010 LAST_ACCEPT_DATE ,
1011 LAST_UPDATED_BY ,
1012 LAST_UPDATE_DATE ,
1013 LAST_UPDATE_LOGIN ,
1014 LATEST_EXTERNAL_FLAG ,
1015 LEAD_TIME ,
1016 LEAD_TIME_UNIT ,
1017 LINE_LOCATION_ID ,
1018 NEED_BY_DATE ,
1019 PO_HEADER_ID ,
1020 PO_LINE_ID ,
1021 PO_RELEASE_ID ,
1022 PRICE_DISCOUNT ,
1023 PRICE_OVERRIDE ,
1024 PROGRAM_APPLICATION_ID ,
1025 PROGRAM_ID ,
1026 PROGRAM_UPDATE_DATE ,
1027 PROMISED_DATE ,
1028 QTY_RCV_EXCEPTION_CODE ,
1029 QTY_RCV_TOLERANCE ,
1030 QUANTITY ,
1031 QUANTITY_ACCEPTED ,
1032 QUANTITY_BILLED ,
1033 QUANTITY_CANCELLED ,
1034 QUANTITY_RECEIVED ,
1035 QUANTITY_REJECTED ,
1036 RECEIPT_DAYS_EXCEPTION_CODE ,
1037 RECEIPT_REQUIRED_FLAG ,
1038 RECEIVE_CLOSE_TOLERANCE ,
1039 RECEIVING_ROUTING_ID ,
1040 REQUEST_ID ,
1041 REVISION_NUM ,
1042 SHIPMENT_NUM ,
1043 SHIPMENT_TYPE ,
1044 SHIP_TO_LOCATION_ID ,
1045 SHIP_TO_ORGANIZATION_ID ,
1046 SHIP_VIA_LOOKUP_CODE ,
1047 SOURCE_SHIPMENT_ID ,
1048 START_DATE ,
1049 TAXABLE_FLAG ,
1050 TERMS_ID ,
1051 UNENCUMBERED_QUANTITY ,
1052 UNIT_MEAS_LOOKUP_CODE ,
1053 UNIT_OF_MEASURE_CLASS ,
1054 USSGL_TRANSACTION_CODE )
1055 SELECT
1056 POL.ACCRUE_ON_RECEIPT_FLAG ,
1057 POL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
1058 POL.APPROVED_DATE ,
1059 POL.APPROVED_FLAG ,
1060 POL.ATTRIBUTE1 ,
1061 POL.ATTRIBUTE10 ,
1062 POL.ATTRIBUTE11 ,
1063 POL.ATTRIBUTE12 ,
1064 POL.ATTRIBUTE13 ,
1065 POL.ATTRIBUTE14 ,
1066 POL.ATTRIBUTE15 ,
1067 POL.ATTRIBUTE2 ,
1068 POL.ATTRIBUTE3 ,
1069 POL.ATTRIBUTE4 ,
1070 POL.ATTRIBUTE5 ,
1071 POL.ATTRIBUTE6 ,
1072 POL.ATTRIBUTE7 ,
1073 POL.ATTRIBUTE8 ,
1074 POL.ATTRIBUTE9 ,
1075 POL.ATTRIBUTE_CATEGORY ,
1076 POL.CANCELLED_BY ,
1077 POL.CANCEL_DATE ,
1078 POL.CANCEL_FLAG ,
1079 POL.CANCEL_REASON ,
1080 POL.CLOSED_BY ,
1081 POL.CLOSED_CODE ,
1082 POL.CLOSED_DATE ,
1083 POL.CLOSED_FLAG ,
1084 POL.CLOSED_REASON ,
1085 POL.CREATED_BY ,
1086 POL.CREATION_DATE ,
1087 POL.DAYS_EARLY_RECEIPT_ALLOWED ,
1088 POL.DAYS_LATE_RECEIPT_ALLOWED ,
1089 POL.ENCUMBERED_DATE ,
1090 POL.ENCUMBERED_FLAG ,
1091 POL.ENCUMBER_NOW ,
1092 POL.END_DATE ,
1093 POL.ENFORCE_SHIP_TO_LOCATION_CODE ,
1094 POL.ESTIMATED_TAX_AMOUNT ,
1095 POL.FIRM_STATUS_LOOKUP_CODE ,
1096 POL.FOB_LOOKUP_CODE ,
1097 POL.FREIGHT_TERMS_LOOKUP_CODE ,
1098 POL.FROM_HEADER_ID ,
1099 POL.FROM_LINE_ID ,
1100 POL.FROM_LINE_LOCATION_ID ,
1101 POL.GOVERNMENT_CONTEXT ,
1102 POL.INSPECTION_REQUIRED_FLAG ,
1103 POL.INVOICE_CLOSE_TOLERANCE ,
1104 POL.LAST_ACCEPT_DATE ,
1105 POL.LAST_UPDATED_BY ,
1106 POL.LAST_UPDATE_DATE ,
1107 POL.LAST_UPDATE_LOGIN ,
1108 'Y' ,
1109 POL.LEAD_TIME ,
1110 POL.LEAD_TIME_UNIT ,
1111 POL.LINE_LOCATION_ID ,
1112 POL.NEED_BY_DATE ,
1113 POL.PO_HEADER_ID ,
1114 POL.PO_LINE_ID ,
1115 POL.PO_RELEASE_ID ,
1116 POL.PRICE_DISCOUNT ,
1117 POL.PRICE_OVERRIDE ,
1118 POL.PROGRAM_APPLICATION_ID ,
1119 POL.PROGRAM_ID ,
1120 POL.PROGRAM_UPDATE_DATE ,
1121 POL.PROMISED_DATE ,
1122 POL.QTY_RCV_EXCEPTION_CODE ,
1123 POL.QTY_RCV_TOLERANCE ,
1124 POL.QUANTITY ,
1125 POL.QUANTITY_ACCEPTED ,
1126 POL.QUANTITY_BILLED ,
1127 POL.QUANTITY_CANCELLED ,
1128 POL.QUANTITY_RECEIVED ,
1129 POL.QUANTITY_REJECTED ,
1130 POL.RECEIPT_DAYS_EXCEPTION_CODE ,
1131 POL.RECEIPT_REQUIRED_FLAG ,
1132 POL.RECEIVE_CLOSE_TOLERANCE ,
1133 POL.RECEIVING_ROUTING_ID ,
1134 POL.REQUEST_ID ,
1135 p_revision_num ,
1136 POL.SHIPMENT_NUM ,
1137 POL.SHIPMENT_TYPE ,
1138 POL.SHIP_TO_LOCATION_ID ,
1139 POL.SHIP_TO_ORGANIZATION_ID ,
1140 POL.SHIP_VIA_LOOKUP_CODE ,
1141 POL.SOURCE_SHIPMENT_ID ,
1142 POL.START_DATE ,
1143 POL.TAXABLE_FLAG ,
1144 POL.TERMS_ID ,
1145 POL.UNENCUMBERED_QUANTITY ,
1146 POL.UNIT_MEAS_LOOKUP_CODE ,
1147 POL.UNIT_OF_MEASURE_CLASS ,
1148 POL.USSGL_TRANSACTION_CODE
1149 FROM PO_LINE_LOCATIONS POL,
1150 PO_LINE_LOCATIONS_ARCHIVE POLA
1151 WHERE POL.PO_HEADER_ID = p_document_id
1152 AND POL.LINE_LOCATION_ID = POLA.LINE_LOCATION_ID (+)
1153 AND POLA.LATEST_EXTERNAL_FLAG (+) = 'Y'
1154 AND (
1155 (POLA.LINE_LOCATION_ID IS NULL)
1156 OR (POL.QUANTITY <> POLA.QUANTITY)
1157 OR (POL.QUANTITY IS NULL AND POLA.QUANTITY IS NOT NULL)
1158 OR (POL.QUANTITY IS NOT NULL AND POLA.QUANTITY IS NULL)
1159 OR (POL.SHIP_TO_LOCATION_ID <> POLA.SHIP_TO_LOCATION_ID)
1160 OR (POL.SHIP_TO_LOCATION_ID IS NULL
1161 AND POLA.SHIP_TO_LOCATION_ID IS NOT NULL)
1162 OR (POL.SHIP_TO_LOCATION_ID IS NOT NULL
1163 AND POLA.SHIP_TO_LOCATION_ID IS NULL)
1164 OR (POL.NEED_BY_DATE <> POLA.NEED_BY_DATE)
1165 OR (POL.NEED_BY_DATE IS NULL
1166 AND POLA.NEED_BY_DATE IS NOT NULL)
1167 OR (POL.NEED_BY_DATE IS NOT NULL
1168 AND POLA.NEED_BY_DATE IS NULL)
1169 OR (POL.PROMISED_DATE <> POLA.PROMISED_DATE)
1170 OR (POL.PROMISED_DATE IS NULL
1171 AND POLA.PROMISED_DATE IS NOT NULL)
1172 OR (POL.PROMISED_DATE IS NOT NULL
1173 AND POLA.PROMISED_DATE IS NULL)
1174 OR (POL.LAST_ACCEPT_DATE <> POLA.LAST_ACCEPT_DATE)
1175 OR (POL.LAST_ACCEPT_DATE IS NULL
1176 AND POLA.LAST_ACCEPT_DATE IS NOT NULL)
1177 OR (POL.LAST_ACCEPT_DATE IS NOT NULL
1178 AND POLA.LAST_ACCEPT_DATE IS NULL)
1179 OR (POL.PRICE_OVERRIDE <> POLA.PRICE_OVERRIDE)
1180 OR (POL.PRICE_OVERRIDE IS NULL
1181 AND POLA.PRICE_OVERRIDE IS NOT NULL)
1182 OR (POL.PRICE_OVERRIDE IS NOT NULL
1183 AND POLA.PRICE_OVERRIDE IS NULL)
1184 OR (POL.TAXABLE_FLAG <> POLA.TAXABLE_FLAG)
1185 OR (POL.TAXABLE_FLAG IS NULL
1186 AND POLA.TAXABLE_FLAG IS NOT NULL)
1187 OR (POL.TAXABLE_FLAG IS NOT NULL
1188 AND POLA.TAXABLE_FLAG IS NULL)
1189 OR (POL.CANCEL_FLAG = 'Y'
1190 AND NVL(POLA.CANCEL_FLAG,'N') = 'N')
1191 OR (POL.SHIPMENT_NUM <> POLA.SHIPMENT_NUM)
1192 OR (POL.SHIPMENT_NUM IS NULL
1193 AND POLA.SHIPMENT_NUM IS NOT NULL)
1194 OR (POL.SHIPMENT_NUM IS NOT NULL
1195 AND POLA.SHIPMENT_NUM IS NULL));
1196
1197 IF SQL%FOUND THEN
1198 -- Assert: At least one row was processed in the sql statement.
1199 -- Set the latest_external_flag to 'N' for all rows which have:
1200 -- - latest_external_flag = 'Y'
1201 -- - revision_num < p_revision_num (the new revision of the
1202 -- header)
1203 -- - have no new archived row
1204 UPDATE PO_LINE_LOCATIONS_ARCHIVE POL1
1205 SET LATEST_EXTERNAL_FLAG = 'N'
1206 WHERE PO_HEADER_ID = p_document_id
1207 AND LATEST_EXTERNAL_FLAG = 'Y'
1208 AND REVISION_NUM < p_revision_num
1209 AND EXISTS
1210 (SELECT 'A new archived row'
1211 FROM PO_LINE_LOCATIONS_ARCHIVE POL2
1212 WHERE POL2.LINE_LOCATION_ID = POL1.LINE_LOCATION_ID
1213 AND POL2.LATEST_EXTERNAL_FLAG = 'Y'
1214 AND POL2.REVISION_NUM = p_revision_num);
1215 END IF;
1216
1217 -- Archive the distributions.
1218 -- This will be an exact copy of po_distributions except for the
1219 -- latest_external_flag and the revision_num. Keep the columns
1220 -- in alphabetical order for easy verification.
1221 INSERT INTO PO_DISTRIBUTIONS_ARCHIVE
1222 (
1223 ACCRUAL_ACCOUNT_ID ,
1224 ACCRUED_FLAG ,
1225 ACCRUE_ON_RECEIPT_FLAG ,
1226 AMOUNT_BILLED ,
1227 ATTRIBUTE1 ,
1228 ATTRIBUTE10 ,
1229 ATTRIBUTE11 ,
1230 ATTRIBUTE12 ,
1231 ATTRIBUTE13 ,
1232 ATTRIBUTE14 ,
1233 ATTRIBUTE15 ,
1234 ATTRIBUTE2 ,
1235 ATTRIBUTE3 ,
1236 ATTRIBUTE4 ,
1237 ATTRIBUTE5 ,
1238 ATTRIBUTE6 ,
1239 ATTRIBUTE7 ,
1240 ATTRIBUTE8 ,
1241 ATTRIBUTE9 ,
1242 ATTRIBUTE_CATEGORY ,
1243 BOM_RESOURCE_ID ,
1244 BUDGET_ACCOUNT_ID ,
1245 CODE_COMBINATION_ID ,
1246 CREATED_BY ,
1247 CREATION_DATE ,
1248 DELIVER_TO_LOCATION_ID ,
1249 DELIVER_TO_PERSON_ID ,
1250 DESTINATION_CONTEXT ,
1251 DESTINATION_ORGANIZATION_ID ,
1252 DESTINATION_SUBINVENTORY ,
1253 DESTINATION_TYPE_CODE ,
1254 DISTRIBUTION_NUM ,
1255 ENCUMBERED_AMOUNT ,
1256 ENCUMBERED_FLAG ,
1257 EXPENDITURE_ITEM_DATE ,
1258 EXPENDITURE_ORGANIZATION_ID ,
1259 EXPENDITURE_TYPE ,
1260 FAILED_FUNDS_LOOKUP_CODE ,
1261 GL_CANCELLED_DATE ,
1262 GL_CLOSED_DATE ,
1263 GL_ENCUMBERED_DATE ,
1264 GL_ENCUMBERED_PERIOD_NAME ,
1265 GOVERNMENT_CONTEXT ,
1266 LAST_UPDATED_BY ,
1267 LAST_UPDATE_DATE ,
1268 LAST_UPDATE_LOGIN ,
1269 LATEST_EXTERNAL_FLAG ,
1270 LINE_LOCATION_ID ,
1271 PO_DISTRIBUTION_ID ,
1272 PO_HEADER_ID ,
1273 PO_LINE_ID ,
1274 PO_RELEASE_ID ,
1275 PREVENT_ENCUMBRANCE_FLAG ,
1276 PROGRAM_APPLICATION_ID ,
1277 PROGRAM_ID ,
1278 PROGRAM_UPDATE_DATE ,
1279 PROJECT_ACCOUNTING_CONTEXT ,
1280 PROJECT_ID ,
1281 QUANTITY_BILLED ,
1282 QUANTITY_CANCELLED ,
1283 QUANTITY_DELIVERED ,
1284 QUANTITY_ORDERED ,
1285 RATE ,
1286 RATE_DATE ,
1287 REQUEST_ID ,
1288 REQ_DISTRIBUTION_ID ,
1289 REQ_HEADER_REFERENCE_NUM ,
1290 REQ_LINE_REFERENCE_NUM ,
1291 REVISION_NUM ,
1292 SET_OF_BOOKS_ID ,
1293 SOURCE_DISTRIBUTION_ID ,
1294 TASK_ID ,
1295 UNENCUMBERED_AMOUNT ,
1296 UNENCUMBERED_QUANTITY ,
1297 USSGL_TRANSACTION_CODE ,
1298 VARIANCE_ACCOUNT_ID ,
1299 WIP_ENTITY_ID ,
1300 WIP_LINE_ID ,
1301 WIP_OPERATION_SEQ_NUM ,
1302 WIP_REPETITIVE_SCHEDULE_ID ,
1303 WIP_RESOURCE_SEQ_NUM )
1304 SELECT
1305 POD.ACCRUAL_ACCOUNT_ID ,
1306 POD.ACCRUED_FLAG ,
1307 POD.ACCRUE_ON_RECEIPT_FLAG ,
1308 POD.AMOUNT_BILLED ,
1309 POD.ATTRIBUTE1 ,
1310 POD.ATTRIBUTE10 ,
1311 POD.ATTRIBUTE11 ,
1312 POD.ATTRIBUTE12 ,
1313 POD.ATTRIBUTE13 ,
1314 POD.ATTRIBUTE14 ,
1315 POD.ATTRIBUTE15 ,
1316 POD.ATTRIBUTE2 ,
1317 POD.ATTRIBUTE3 ,
1318 POD.ATTRIBUTE4 ,
1319 POD.ATTRIBUTE5 ,
1320 POD.ATTRIBUTE6 ,
1321 POD.ATTRIBUTE7 ,
1322 POD.ATTRIBUTE8 ,
1323 POD.ATTRIBUTE9 ,
1324 POD.ATTRIBUTE_CATEGORY ,
1325 POD.BOM_RESOURCE_ID ,
1326 POD.BUDGET_ACCOUNT_ID ,
1327 POD.CODE_COMBINATION_ID ,
1328 POD.CREATED_BY ,
1329 POD.CREATION_DATE ,
1330 POD.DELIVER_TO_LOCATION_ID ,
1331 POD.DELIVER_TO_PERSON_ID ,
1332 POD.DESTINATION_CONTEXT ,
1333 POD.DESTINATION_ORGANIZATION_ID ,
1334 POD.DESTINATION_SUBINVENTORY ,
1335 POD.DESTINATION_TYPE_CODE ,
1336 POD.DISTRIBUTION_NUM ,
1337 POD.ENCUMBERED_AMOUNT ,
1338 POD.ENCUMBERED_FLAG ,
1339 POD.EXPENDITURE_ITEM_DATE ,
1340 POD.EXPENDITURE_ORGANIZATION_ID ,
1341 POD.EXPENDITURE_TYPE ,
1342 POD.FAILED_FUNDS_LOOKUP_CODE ,
1343 POD.GL_CANCELLED_DATE ,
1344 POD.GL_CLOSED_DATE ,
1345 POD.GL_ENCUMBERED_DATE ,
1346 POD.GL_ENCUMBERED_PERIOD_NAME ,
1347 POD.GOVERNMENT_CONTEXT ,
1348 POD.LAST_UPDATED_BY ,
1349 POD.LAST_UPDATE_DATE ,
1350 POD.LAST_UPDATE_LOGIN ,
1351 'Y' ,
1352 POD.LINE_LOCATION_ID ,
1353 POD.PO_DISTRIBUTION_ID ,
1354 POD.PO_HEADER_ID ,
1355 POD.PO_LINE_ID ,
1356 POD.PO_RELEASE_ID ,
1357 POD.PREVENT_ENCUMBRANCE_FLAG ,
1358 POD.PROGRAM_APPLICATION_ID ,
1359 POD.PROGRAM_ID ,
1360 POD.PROGRAM_UPDATE_DATE ,
1361 POD.PROJECT_ACCOUNTING_CONTEXT ,
1362 POD.PROJECT_ID ,
1363 POD.QUANTITY_BILLED ,
1364 POD.QUANTITY_CANCELLED ,
1365 POD.QUANTITY_DELIVERED ,
1366 POD.QUANTITY_ORDERED ,
1367 POD.RATE ,
1368 POD.RATE_DATE ,
1369 POD.REQUEST_ID ,
1370 POD.REQ_DISTRIBUTION_ID ,
1371 POD.REQ_HEADER_REFERENCE_NUM ,
1372 POD.REQ_LINE_REFERENCE_NUM ,
1373 p_revision_num ,
1374 POD.SET_OF_BOOKS_ID ,
1375 POD.SOURCE_DISTRIBUTION_ID ,
1376 POD.TASK_ID ,
1377 POD.UNENCUMBERED_AMOUNT ,
1378 POD.UNENCUMBERED_QUANTITY ,
1379 POD.USSGL_TRANSACTION_CODE ,
1380 POD.VARIANCE_ACCOUNT_ID ,
1381 POD.WIP_ENTITY_ID ,
1382 POD.WIP_LINE_ID ,
1383 POD.WIP_OPERATION_SEQ_NUM ,
1384 POD.WIP_REPETITIVE_SCHEDULE_ID ,
1385 POD.WIP_RESOURCE_SEQ_NUM
1386 FROM PO_DISTRIBUTIONS POD,
1387 PO_DISTRIBUTIONS_ARCHIVE PODA
1388 WHERE POD.PO_HEADER_ID = p_document_id
1389 AND POD.PO_DISTRIBUTION_ID = PODA.PO_DISTRIBUTION_ID (+)
1390 AND PODA.LATEST_EXTERNAL_FLAG (+) = 'Y'
1391 AND (
1392 (PODA.PO_DISTRIBUTION_ID IS NULL)
1393 OR (POD.QUANTITY_ORDERED <> PODA.QUANTITY_ORDERED)
1394 OR (POD.QUANTITY_ORDERED IS NULL
1395 AND PODA.QUANTITY_ORDERED IS NOT NULL)
1396 OR (POD.QUANTITY_ORDERED IS NOT NULL
1397 AND PODA.QUANTITY_ORDERED IS NULL)
1398 OR (POD.DELIVER_TO_PERSON_ID <> PODA.DELIVER_TO_PERSON_ID)
1399 OR (POD.DELIVER_TO_PERSON_ID IS NULL
1400 AND PODA.DELIVER_TO_PERSON_ID IS NOT NULL)
1401 OR (POD.DELIVER_TO_PERSON_ID IS NOT NULL
1402 AND PODA.DELIVER_TO_PERSON_ID IS NULL)
1403 OR (POD.DISTRIBUTION_NUM <> PODA.DISTRIBUTION_NUM));
1404
1405 IF SQL%FOUND THEN
1406 -- Assert: At least one row was processed in the sql statement.
1407 -- Set the latest_external_flag to 'N' for all rows which have:
1408 -- - latest_external_flag = 'Y'
1409 -- - revision_num < p_revision_num (the new revision of the
1410 -- header)
1411 -- - have no new archived row
1412 UPDATE PO_DISTRIBUTIONS_ARCHIVE POD1
1413 SET LATEST_EXTERNAL_FLAG = 'N'
1414 WHERE PO_HEADER_ID = p_document_id
1415 AND LATEST_EXTERNAL_FLAG = 'Y'
1416 AND REVISION_NUM < p_revision_num
1417 AND EXISTS
1418 (SELECT 'A new archived row'
1419 FROM PO_DISTRIBUTIONS_ARCHIVE POD2
1420 WHERE POD2.PO_DISTRIBUTION_ID = POD1.PO_DISTRIBUTION_ID
1421 AND POD2.LATEST_EXTERNAL_FLAG = 'Y'
1422 AND POD2.REVISION_NUM = p_revision_num);
1423
1424 END IF;
1425
1426 EXCEPTION
1427 WHEN OTHERS THEN
1428 global_stack := 'PORARSHIPDIST(P_DOCUMENT_ID='''||p_document_id||
1429 ''' P_REVISION_NUM='''||p_revision_num||
1430 '''):'||global_stack;
1431 RAISE;
1432
1433 END PORARSHIPDIST;
1434
1435 -- ============================================================================
1436 -- Name: porarrelease
1437 -- Desc: Archive PO_RELEASES, PO_LINE_LOCATIONS and PO_DISTRIBUTIONS
1438 -- Args: IN: p_document_id - The unique identifier of the Release
1439 -- p_revision_num - The revision number of the header
1440 -- Err : Error message context returned in global_stack.
1441 -- Algr: Set the LATEST_EXTERNAL_FLAG of the currently archived release
1442 -- to "N"
1443 -- Archive PO_RELEASES
1444 -- Archive PO_LINE_LOCATIONS
1445 -- Archive PO_DISTRIBUTIONS
1446 -- Note: Private Procedure
1447 -- ============================================================================
1448
1449 PROCEDURE PORARRELEASE (
1450 P_DOCUMENT_ID IN NUMBER,
1451 P_REVISION_NUM IN NUMBER)
1452 IS
1453
1454 BEGIN
1455 -- Set the latest_external_flag of the archived header to 'N'.
1456 UPDATE PO_RELEASES_ARCHIVE
1457 SET LATEST_EXTERNAL_FLAG = 'N'
1458 WHERE PO_RELEASE_ID = p_document_id
1459 AND LATEST_EXTERNAL_FLAG = 'Y';
1460
1461 -- Archive the release.
1462 INSERT INTO PO_RELEASES_ARCHIVE
1463 (
1464 ACCEPTANCE_DUE_DATE ,
1465 ACCEPTANCE_REQUIRED_FLAG ,
1466 AGENT_ID ,
1467 APPROVED_DATE ,
1468 APPROVED_FLAG ,
1469 ATTRIBUTE1 ,
1470 ATTRIBUTE10 ,
1471 ATTRIBUTE11 ,
1472 ATTRIBUTE12 ,
1473 ATTRIBUTE13 ,
1474 ATTRIBUTE14 ,
1475 ATTRIBUTE15 ,
1476 ATTRIBUTE2 ,
1477 ATTRIBUTE3 ,
1478 ATTRIBUTE4 ,
1479 ATTRIBUTE5 ,
1480 ATTRIBUTE6 ,
1481 ATTRIBUTE7 ,
1482 ATTRIBUTE8 ,
1483 ATTRIBUTE9 ,
1484 ATTRIBUTE_CATEGORY ,
1485 AUTHORIZATION_STATUS ,
1486 CANCELLED_BY ,
1487 CANCEL_DATE ,
1488 CANCEL_FLAG ,
1489 CANCEL_REASON ,
1490 CLOSED_CODE ,
1491 CREATED_BY ,
1492 CREATION_DATE ,
1493 FIRM_STATUS_LOOKUP_CODE ,
1494 FROZEN_FLAG ,
1495 GOVERNMENT_CONTEXT ,
1496 HOLD_BY ,
1497 HOLD_DATE ,
1498 HOLD_FLAG ,
1499 HOLD_REASON ,
1500 LAST_UPDATED_BY ,
1501 LAST_UPDATE_DATE ,
1502 LAST_UPDATE_LOGIN ,
1503 LATEST_EXTERNAL_FLAG ,
1504 NOTE_TO_VENDOR ,
1505 PO_HEADER_ID ,
1506 PO_RELEASE_ID ,
1507 PRINTED_DATE ,
1508 PRINT_COUNT ,
1509 PROGRAM_APPLICATION_ID ,
1510 PROGRAM_ID ,
1511 PROGRAM_UPDATE_DATE ,
1512 RELEASE_DATE ,
1513 RELEASE_NUM ,
1514 RELEASE_TYPE ,
1515 REQUEST_ID ,
1516 REVISED_DATE ,
1517 REVISION_NUM ,
1518 USSGL_TRANSACTION_CODE )
1519 SELECT
1520 ACCEPTANCE_DUE_DATE ,
1521 ACCEPTANCE_REQUIRED_FLAG ,
1522 AGENT_ID ,
1523 APPROVED_DATE ,
1524 APPROVED_FLAG ,
1525 ATTRIBUTE1 ,
1526 ATTRIBUTE10 ,
1527 ATTRIBUTE11 ,
1528 ATTRIBUTE12 ,
1529 ATTRIBUTE13 ,
1530 ATTRIBUTE14 ,
1531 ATTRIBUTE15 ,
1532 ATTRIBUTE2 ,
1533 ATTRIBUTE3 ,
1534 ATTRIBUTE4 ,
1535 ATTRIBUTE5 ,
1536 ATTRIBUTE6 ,
1537 ATTRIBUTE7 ,
1538 ATTRIBUTE8 ,
1539 ATTRIBUTE9 ,
1540 ATTRIBUTE_CATEGORY ,
1541 AUTHORIZATION_STATUS ,
1542 CANCELLED_BY ,
1543 CANCEL_DATE ,
1544 CANCEL_FLAG ,
1545 CANCEL_REASON ,
1546 CLOSED_CODE ,
1547 CREATED_BY ,
1548 CREATION_DATE ,
1549 FIRM_STATUS_LOOKUP_CODE ,
1550 FROZEN_FLAG ,
1551 GOVERNMENT_CONTEXT ,
1552 HOLD_BY ,
1553 HOLD_DATE ,
1554 HOLD_FLAG ,
1555 HOLD_REASON ,
1556 LAST_UPDATED_BY ,
1557 LAST_UPDATE_DATE ,
1558 LAST_UPDATE_LOGIN ,
1559 'Y' ,
1560 NOTE_TO_VENDOR ,
1561 PO_HEADER_ID ,
1562 PO_RELEASE_ID ,
1563 PRINTED_DATE ,
1564 PRINT_COUNT ,
1565 PROGRAM_APPLICATION_ID ,
1566 PROGRAM_ID ,
1567 PROGRAM_UPDATE_DATE ,
1568 RELEASE_DATE ,
1569 RELEASE_NUM ,
1570 RELEASE_TYPE ,
1571 REQUEST_ID ,
1572 REVISED_DATE ,
1573 REVISION_NUM ,
1574 USSGL_TRANSACTION_CODE
1575 FROM PO_RELEASES
1576 WHERE PO_RELEASE_ID = p_document_id;
1577
1578
1579 -- Archive the Shipments.
1580 INSERT INTO PO_LINE_LOCATIONS_ARCHIVE
1581 (
1582 ACCRUE_ON_RECEIPT_FLAG ,
1583 ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
1584 APPROVED_DATE ,
1585 APPROVED_FLAG ,
1586 ATTRIBUTE1 ,
1587 ATTRIBUTE10 ,
1588 ATTRIBUTE11 ,
1589 ATTRIBUTE12 ,
1590 ATTRIBUTE13 ,
1591 ATTRIBUTE14 ,
1592 ATTRIBUTE15 ,
1593 ATTRIBUTE2 ,
1594 ATTRIBUTE3 ,
1595 ATTRIBUTE4 ,
1596 ATTRIBUTE5 ,
1597 ATTRIBUTE6 ,
1598 ATTRIBUTE7 ,
1599 ATTRIBUTE8 ,
1600 ATTRIBUTE9 ,
1601 ATTRIBUTE_CATEGORY ,
1602 CANCELLED_BY ,
1603 CANCEL_DATE ,
1604 CANCEL_FLAG ,
1605 CANCEL_REASON ,
1606 CLOSED_BY ,
1607 CLOSED_CODE ,
1608 CLOSED_DATE ,
1609 CLOSED_FLAG ,
1610 CLOSED_REASON ,
1611 CREATED_BY ,
1612 CREATION_DATE ,
1613 DAYS_EARLY_RECEIPT_ALLOWED ,
1614 DAYS_LATE_RECEIPT_ALLOWED ,
1615 ENCUMBERED_DATE ,
1616 ENCUMBERED_FLAG ,
1617 ENCUMBER_NOW ,
1618 END_DATE ,
1619 ENFORCE_SHIP_TO_LOCATION_CODE ,
1620 ESTIMATED_TAX_AMOUNT ,
1621 FIRM_STATUS_LOOKUP_CODE ,
1622 FOB_LOOKUP_CODE ,
1623 FREIGHT_TERMS_LOOKUP_CODE ,
1624 FROM_HEADER_ID ,
1625 FROM_LINE_ID ,
1626 FROM_LINE_LOCATION_ID ,
1627 GOVERNMENT_CONTEXT ,
1628 INSPECTION_REQUIRED_FLAG ,
1629 INVOICE_CLOSE_TOLERANCE ,
1630 LAST_ACCEPT_DATE ,
1631 LAST_UPDATED_BY ,
1632 LAST_UPDATE_DATE ,
1633 LAST_UPDATE_LOGIN ,
1634 LATEST_EXTERNAL_FLAG ,
1635 LEAD_TIME ,
1636 LEAD_TIME_UNIT ,
1637 LINE_LOCATION_ID ,
1638 NEED_BY_DATE ,
1639 PO_HEADER_ID ,
1640 PO_LINE_ID ,
1641 PO_RELEASE_ID ,
1642 PRICE_DISCOUNT ,
1643 PRICE_OVERRIDE ,
1644 PROGRAM_APPLICATION_ID ,
1645 PROGRAM_ID ,
1646 PROGRAM_UPDATE_DATE ,
1647 PROMISED_DATE ,
1648 QTY_RCV_EXCEPTION_CODE ,
1649 QTY_RCV_TOLERANCE ,
1650 QUANTITY ,
1651 QUANTITY_ACCEPTED ,
1652 QUANTITY_BILLED ,
1653 QUANTITY_CANCELLED ,
1654 QUANTITY_RECEIVED ,
1655 QUANTITY_REJECTED ,
1656 RECEIPT_DAYS_EXCEPTION_CODE ,
1657 RECEIPT_REQUIRED_FLAG ,
1658 RECEIVE_CLOSE_TOLERANCE ,
1659 RECEIVING_ROUTING_ID ,
1660 REQUEST_ID ,
1661 REVISION_NUM ,
1662 SHIPMENT_NUM ,
1663 SHIPMENT_TYPE ,
1664 SHIP_TO_LOCATION_ID ,
1665 SHIP_TO_ORGANIZATION_ID ,
1666 SHIP_VIA_LOOKUP_CODE ,
1667 SOURCE_SHIPMENT_ID ,
1668 START_DATE ,
1669 TAXABLE_FLAG ,
1670 TERMS_ID ,
1671 UNENCUMBERED_QUANTITY ,
1672 UNIT_MEAS_LOOKUP_CODE ,
1673 UNIT_OF_MEASURE_CLASS ,
1674 USSGL_TRANSACTION_CODE )
1675 SELECT
1676 POL.ACCRUE_ON_RECEIPT_FLAG ,
1677 POL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
1678 POL.APPROVED_DATE ,
1679 POL.APPROVED_FLAG ,
1680 POL.ATTRIBUTE1 ,
1681 POL.ATTRIBUTE10 ,
1682 POL.ATTRIBUTE11 ,
1683 POL.ATTRIBUTE12 ,
1684 POL.ATTRIBUTE13 ,
1685 POL.ATTRIBUTE14 ,
1686 POL.ATTRIBUTE15 ,
1687 POL.ATTRIBUTE2 ,
1688 POL.ATTRIBUTE3 ,
1689 POL.ATTRIBUTE4 ,
1690 POL.ATTRIBUTE5 ,
1691 POL.ATTRIBUTE6 ,
1692 POL.ATTRIBUTE7 ,
1693 POL.ATTRIBUTE8 ,
1694 POL.ATTRIBUTE9 ,
1695 POL.ATTRIBUTE_CATEGORY ,
1696 POL.CANCELLED_BY ,
1697 POL.CANCEL_DATE ,
1698 POL.CANCEL_FLAG ,
1699 POL.CANCEL_REASON ,
1700 POL.CLOSED_BY ,
1701 POL.CLOSED_CODE ,
1702 POL.CLOSED_DATE ,
1703 POL.CLOSED_FLAG ,
1704 POL.CLOSED_REASON ,
1705 POL.CREATED_BY ,
1706 POL.CREATION_DATE ,
1707 POL.DAYS_EARLY_RECEIPT_ALLOWED ,
1708 POL.DAYS_LATE_RECEIPT_ALLOWED ,
1709 POL.ENCUMBERED_DATE ,
1710 POL.ENCUMBERED_FLAG ,
1711 POL.ENCUMBER_NOW ,
1712 POL.END_DATE ,
1713 POL.ENFORCE_SHIP_TO_LOCATION_CODE ,
1714 POL.ESTIMATED_TAX_AMOUNT ,
1715 POL.FIRM_STATUS_LOOKUP_CODE ,
1716 POL.FOB_LOOKUP_CODE ,
1717 POL.FREIGHT_TERMS_LOOKUP_CODE ,
1718 POL.FROM_HEADER_ID ,
1719 POL.FROM_LINE_ID ,
1720 POL.FROM_LINE_LOCATION_ID ,
1721 POL.GOVERNMENT_CONTEXT ,
1722 POL.INSPECTION_REQUIRED_FLAG ,
1723 POL.INVOICE_CLOSE_TOLERANCE ,
1724 POL.LAST_ACCEPT_DATE ,
1725 POL.LAST_UPDATED_BY ,
1726 POL.LAST_UPDATE_DATE ,
1727 POL.LAST_UPDATE_LOGIN ,
1728 'Y' ,
1729 POL.LEAD_TIME ,
1730 POL.LEAD_TIME_UNIT ,
1731 POL.LINE_LOCATION_ID ,
1732 POL.NEED_BY_DATE ,
1733 POL.PO_HEADER_ID ,
1734 POL.PO_LINE_ID ,
1735 POL.PO_RELEASE_ID ,
1736 POL.PRICE_DISCOUNT ,
1737 POL.PRICE_OVERRIDE ,
1738 POL.PROGRAM_APPLICATION_ID ,
1739 POL.PROGRAM_ID ,
1740 POL.PROGRAM_UPDATE_DATE ,
1741 POL.PROMISED_DATE ,
1742 POL.QTY_RCV_EXCEPTION_CODE ,
1743 POL.QTY_RCV_TOLERANCE ,
1744 POL.QUANTITY ,
1745 POL.QUANTITY_ACCEPTED ,
1746 POL.QUANTITY_BILLED ,
1747 POL.QUANTITY_CANCELLED ,
1748 POL.QUANTITY_RECEIVED ,
1749 POL.QUANTITY_REJECTED ,
1750 POL.RECEIPT_DAYS_EXCEPTION_CODE ,
1751 POL.RECEIPT_REQUIRED_FLAG ,
1752 POL.RECEIVE_CLOSE_TOLERANCE ,
1753 POL.RECEIVING_ROUTING_ID ,
1754 POL.REQUEST_ID ,
1755 p_revision_num ,
1756 POL.SHIPMENT_NUM ,
1757 POL.SHIPMENT_TYPE ,
1758 POL.SHIP_TO_LOCATION_ID ,
1759 POL.SHIP_TO_ORGANIZATION_ID ,
1760 POL.SHIP_VIA_LOOKUP_CODE ,
1761 POL.SOURCE_SHIPMENT_ID ,
1762 POL.START_DATE ,
1763 POL.TAXABLE_FLAG ,
1764 POL.TERMS_ID ,
1765 POL.UNENCUMBERED_QUANTITY ,
1766 POL.UNIT_MEAS_LOOKUP_CODE ,
1767 POL.UNIT_OF_MEASURE_CLASS ,
1768 POL.USSGL_TRANSACTION_CODE
1769 FROM PO_LINE_LOCATIONS POL,
1770 PO_LINE_LOCATIONS_ARCHIVE POLA
1771 WHERE POL.PO_RELEASE_ID = p_document_id
1772 AND POL.LINE_LOCATION_ID = POLA.LINE_LOCATION_ID (+)
1773 AND POLA.LATEST_EXTERNAL_FLAG (+) = 'Y'
1774 AND (
1775 (POLA.LINE_LOCATION_ID IS NULL)
1776 OR (POL.QUANTITY <> POLA.QUANTITY)
1777 OR (POL.QUANTITY IS NULL AND POLA.QUANTITY IS NOT NULL)
1778 OR (POL.QUANTITY IS NOT NULL AND POLA.QUANTITY IS NULL)
1779 OR (POL.SHIP_TO_LOCATION_ID <> POLA.SHIP_TO_LOCATION_ID)
1780 OR (POL.SHIP_TO_LOCATION_ID IS NULL
1781 AND POLA.SHIP_TO_LOCATION_ID IS NOT NULL)
1782 OR (POL.SHIP_TO_LOCATION_ID IS NOT NULL
1783 AND POLA.SHIP_TO_LOCATION_ID IS NULL)
1784 OR (POL.NEED_BY_DATE <> POLA.NEED_BY_DATE)
1785 OR (POL.NEED_BY_DATE IS NULL
1786 AND POLA.NEED_BY_DATE IS NOT NULL)
1787 OR (POL.NEED_BY_DATE IS NOT NULL
1788 AND POLA.NEED_BY_DATE IS NULL)
1789 OR (POL.PROMISED_DATE <> POLA.PROMISED_DATE)
1790 OR (POL.PROMISED_DATE IS NULL
1791 AND POLA.PROMISED_DATE IS NOT NULL)
1792 OR (POL.PROMISED_DATE IS NOT NULL
1793 AND POLA.PROMISED_DATE IS NULL)
1794 OR (POL.LAST_ACCEPT_DATE <> POLA.LAST_ACCEPT_DATE)
1795 OR (POL.LAST_ACCEPT_DATE IS NULL
1796 AND POLA.LAST_ACCEPT_DATE IS NOT NULL)
1797 OR (POL.LAST_ACCEPT_DATE IS NOT NULL
1798 AND POLA.LAST_ACCEPT_DATE IS NULL)
1799 OR (POL.PRICE_OVERRIDE <> POLA.PRICE_OVERRIDE)
1800 OR (POL.PRICE_OVERRIDE IS NULL
1801 AND POLA.PRICE_OVERRIDE IS NOT NULL)
1802 OR (POL.PRICE_OVERRIDE IS NOT NULL
1803 AND POLA.PRICE_OVERRIDE IS NULL)
1804 OR (POL.TAXABLE_FLAG <> POLA.TAXABLE_FLAG)
1805 OR (POL.TAXABLE_FLAG IS NULL
1806 AND POLA.TAXABLE_FLAG IS NOT NULL)
1807 OR (POL.TAXABLE_FLAG IS NOT NULL
1808 AND POLA.TAXABLE_FLAG IS NULL)
1809 OR (POL.CANCEL_FLAG = 'Y'
1810 AND NVL(POLA.CANCEL_FLAG, 'N') = 'N')
1811 OR (POL.SHIPMENT_NUM <> POLA.SHIPMENT_NUM)
1812 OR (POL.SHIPMENT_NUM IS NULL
1813 AND POLA.SHIPMENT_NUM IS NOT NULL)
1814 OR (POL.SHIPMENT_NUM IS NOT NULL
1815 AND POLA.SHIPMENT_NUM IS NULL));
1816
1817 IF SQL%FOUND THEN
1818 -- Assert: At least one row was processed in the insert statement.
1819 -- Set the latest_external_flag to 'N' for all rows which have:
1820 -- - latest_external_flag = 'Y'
1821 -- - revision_num < p_revision_num (the new revision of the
1822 -- header)
1823 -- - no new archived row
1824 UPDATE PO_LINE_LOCATIONS_ARCHIVE POL1
1825 SET LATEST_EXTERNAL_FLAG = 'N'
1826 WHERE PO_RELEASE_ID = p_document_id
1827 AND LATEST_EXTERNAL_FLAG = 'Y'
1828 AND REVISION_NUM < p_revision_num
1829 AND EXISTS
1830 (SELECT 'A new archived row'
1831 FROM PO_LINE_LOCATIONS_ARCHIVE POL2
1832 WHERE POL2.LINE_LOCATION_ID = POL1.LINE_LOCATION_ID
1833 AND POL2.LATEST_EXTERNAL_FLAG = 'Y'
1834 AND POL2.REVISION_NUM = p_revision_num);
1835
1836 END IF;
1837
1838 -- Archive the distributions.
1839 INSERT INTO PO_DISTRIBUTIONS_ARCHIVE
1840 (
1841 ACCRUAL_ACCOUNT_ID ,
1842 ACCRUED_FLAG ,
1843 ACCRUE_ON_RECEIPT_FLAG ,
1844 AMOUNT_BILLED ,
1845 ATTRIBUTE1 ,
1846 ATTRIBUTE10 ,
1847 ATTRIBUTE11 ,
1848 ATTRIBUTE12 ,
1849 ATTRIBUTE13 ,
1850 ATTRIBUTE14 ,
1851 ATTRIBUTE15 ,
1852 ATTRIBUTE2 ,
1853 ATTRIBUTE3 ,
1854 ATTRIBUTE4 ,
1855 ATTRIBUTE5 ,
1856 ATTRIBUTE6 ,
1857 ATTRIBUTE7 ,
1858 ATTRIBUTE8 ,
1859 ATTRIBUTE9 ,
1860 ATTRIBUTE_CATEGORY ,
1861 BOM_RESOURCE_ID ,
1862 BUDGET_ACCOUNT_ID ,
1863 CODE_COMBINATION_ID ,
1864 CREATED_BY ,
1865 CREATION_DATE ,
1866 DELIVER_TO_LOCATION_ID ,
1867 DELIVER_TO_PERSON_ID ,
1868 DESTINATION_CONTEXT ,
1869 DESTINATION_ORGANIZATION_ID ,
1870 DESTINATION_SUBINVENTORY ,
1871 DESTINATION_TYPE_CODE ,
1872 DISTRIBUTION_NUM ,
1873 ENCUMBERED_AMOUNT ,
1874 ENCUMBERED_FLAG ,
1875 EXPENDITURE_ITEM_DATE ,
1876 EXPENDITURE_ORGANIZATION_ID ,
1877 EXPENDITURE_TYPE ,
1878 FAILED_FUNDS_LOOKUP_CODE ,
1879 GL_CANCELLED_DATE ,
1880 GL_CLOSED_DATE ,
1881 GL_ENCUMBERED_DATE ,
1882 GL_ENCUMBERED_PERIOD_NAME ,
1883 GOVERNMENT_CONTEXT ,
1884 LAST_UPDATED_BY ,
1885 LAST_UPDATE_DATE ,
1886 LAST_UPDATE_LOGIN ,
1887 LATEST_EXTERNAL_FLAG ,
1888 LINE_LOCATION_ID ,
1889 PO_DISTRIBUTION_ID ,
1890 PO_HEADER_ID ,
1891 PO_LINE_ID ,
1892 PO_RELEASE_ID ,
1893 PREVENT_ENCUMBRANCE_FLAG ,
1894 PROGRAM_APPLICATION_ID ,
1895 PROGRAM_ID ,
1896 PROGRAM_UPDATE_DATE ,
1897 PROJECT_ACCOUNTING_CONTEXT ,
1898 PROJECT_ID ,
1899 QUANTITY_BILLED ,
1900 QUANTITY_CANCELLED ,
1901 QUANTITY_DELIVERED ,
1902 QUANTITY_ORDERED ,
1903 RATE ,
1904 RATE_DATE ,
1905 REQUEST_ID ,
1906 REQ_DISTRIBUTION_ID ,
1907 REQ_HEADER_REFERENCE_NUM ,
1908 REQ_LINE_REFERENCE_NUM ,
1909 REVISION_NUM ,
1910 SET_OF_BOOKS_ID ,
1911 SOURCE_DISTRIBUTION_ID ,
1912 TASK_ID ,
1913 UNENCUMBERED_AMOUNT ,
1914 UNENCUMBERED_QUANTITY ,
1915 USSGL_TRANSACTION_CODE ,
1916 VARIANCE_ACCOUNT_ID ,
1917 WIP_ENTITY_ID ,
1918 WIP_LINE_ID ,
1919 WIP_OPERATION_SEQ_NUM ,
1920 WIP_REPETITIVE_SCHEDULE_ID ,
1921 WIP_RESOURCE_SEQ_NUM )
1922 SELECT
1923 POD.ACCRUAL_ACCOUNT_ID ,
1924 POD.ACCRUED_FLAG ,
1925 POD.ACCRUE_ON_RECEIPT_FLAG ,
1926 POD.AMOUNT_BILLED ,
1927 POD.ATTRIBUTE1 ,
1928 POD.ATTRIBUTE10 ,
1929 POD.ATTRIBUTE11 ,
1930 POD.ATTRIBUTE12 ,
1931 POD.ATTRIBUTE13 ,
1932 POD.ATTRIBUTE14 ,
1933 POD.ATTRIBUTE15 ,
1934 POD.ATTRIBUTE2 ,
1935 POD.ATTRIBUTE3 ,
1936 POD.ATTRIBUTE4 ,
1937 POD.ATTRIBUTE5 ,
1938 POD.ATTRIBUTE6 ,
1939 POD.ATTRIBUTE7 ,
1940 POD.ATTRIBUTE8 ,
1941 POD.ATTRIBUTE9 ,
1942 POD.ATTRIBUTE_CATEGORY ,
1943 POD.BOM_RESOURCE_ID ,
1944 POD.BUDGET_ACCOUNT_ID ,
1945 POD.CODE_COMBINATION_ID ,
1946 POD.CREATED_BY ,
1947 POD.CREATION_DATE ,
1948 POD.DELIVER_TO_LOCATION_ID ,
1949 POD.DELIVER_TO_PERSON_ID ,
1950 POD.DESTINATION_CONTEXT ,
1951 POD.DESTINATION_ORGANIZATION_ID ,
1952 POD.DESTINATION_SUBINVENTORY ,
1953 POD.DESTINATION_TYPE_CODE ,
1954 POD.DISTRIBUTION_NUM ,
1955 POD.ENCUMBERED_AMOUNT ,
1956 POD.ENCUMBERED_FLAG ,
1957 POD.EXPENDITURE_ITEM_DATE ,
1958 POD.EXPENDITURE_ORGANIZATION_ID ,
1959 POD.EXPENDITURE_TYPE ,
1960 POD.FAILED_FUNDS_LOOKUP_CODE ,
1961 POD.GL_CANCELLED_DATE ,
1962 POD.GL_CLOSED_DATE ,
1963 POD.GL_ENCUMBERED_DATE ,
1964 POD.GL_ENCUMBERED_PERIOD_NAME ,
1965 POD.GOVERNMENT_CONTEXT ,
1966 POD.LAST_UPDATED_BY ,
1967 POD.LAST_UPDATE_DATE ,
1968 POD.LAST_UPDATE_LOGIN ,
1969 'Y' ,
1970 POD.LINE_LOCATION_ID ,
1971 POD.PO_DISTRIBUTION_ID ,
1972 POD.PO_HEADER_ID ,
1973 POD.PO_LINE_ID ,
1974 POD.PO_RELEASE_ID ,
1975 POD.PREVENT_ENCUMBRANCE_FLAG ,
1976 POD.PROGRAM_APPLICATION_ID ,
1977 POD.PROGRAM_ID ,
1978 POD.PROGRAM_UPDATE_DATE ,
1979 POD.PROJECT_ACCOUNTING_CONTEXT ,
1980 POD.PROJECT_ID ,
1981 POD.QUANTITY_BILLED ,
1982 POD.QUANTITY_CANCELLED ,
1983 POD.QUANTITY_DELIVERED ,
1984 POD.QUANTITY_ORDERED ,
1985 POD.RATE ,
1986 POD.RATE_DATE ,
1987 POD.REQUEST_ID ,
1988 POD.REQ_DISTRIBUTION_ID ,
1989 POD.REQ_HEADER_REFERENCE_NUM ,
1990 POD.REQ_LINE_REFERENCE_NUM ,
1991 p_revision_num ,
1992 POD.SET_OF_BOOKS_ID ,
1993 POD.SOURCE_DISTRIBUTION_ID ,
1994 POD.TASK_ID ,
1995 POD.UNENCUMBERED_AMOUNT ,
1996 POD.UNENCUMBERED_QUANTITY ,
1997 POD.USSGL_TRANSACTION_CODE ,
1998 POD.VARIANCE_ACCOUNT_ID ,
1999 POD.WIP_ENTITY_ID ,
2000 POD.WIP_LINE_ID ,
2001 POD.WIP_OPERATION_SEQ_NUM ,
2002 POD.WIP_REPETITIVE_SCHEDULE_ID ,
2003 POD.WIP_RESOURCE_SEQ_NUM
2004 FROM PO_DISTRIBUTIONS POD,
2005 PO_DISTRIBUTIONS_ARCHIVE PODA
2006 WHERE POD.PO_RELEASE_ID = p_document_id
2007 AND POD.PO_DISTRIBUTION_ID = PODA.PO_DISTRIBUTION_ID (+)
2008 AND PODA.LATEST_EXTERNAL_FLAG (+) = 'Y'
2009 AND (
2010 (PODA.PO_DISTRIBUTION_ID IS NULL)
2011 OR (POD.QUANTITY_ORDERED <> PODA.QUANTITY_ORDERED)
2012 OR (POD.QUANTITY_ORDERED IS NULL
2013 AND PODA.QUANTITY_ORDERED IS NOT NULL)
2014 OR (POD.QUANTITY_ORDERED IS NOT NULL
2015 AND PODA.QUANTITY_ORDERED IS NULL)
2016 OR (POD.DELIVER_TO_PERSON_ID <> PODA.DELIVER_TO_PERSON_ID)
2017 OR (POD.DELIVER_TO_PERSON_ID IS NULL
2018 AND PODA.DELIVER_TO_PERSON_ID IS NOT NULL)
2019 OR (POD.DELIVER_TO_PERSON_ID IS NOT NULL
2020 AND PODA.DELIVER_TO_PERSON_ID IS NULL)
2021 OR (POD.DISTRIBUTION_NUM <> PODA.DISTRIBUTION_NUM));
2022
2023 IF SQL%FOUND THEN
2024 -- Assert: At least one row was processed in the insert statement.
2025 -- Set the latest_external_flag to 'N' for all rows which have:
2026 -- - latest_external_flag = 'Y'
2027 -- - revision_num < p_revision_num (the new revision of the
2028 -- header)
2029 -- - no new archived row
2030 UPDATE PO_DISTRIBUTIONS_ARCHIVE POD1
2031 SET LATEST_EXTERNAL_FLAG = 'N'
2032 WHERE PO_RELEASE_ID = p_document_id
2033 AND LATEST_EXTERNAL_FLAG = 'Y'
2034 AND REVISION_NUM < p_revision_num
2035 AND EXISTS
2036 (SELECT 'A new archived row'
2037 FROM PO_DISTRIBUTIONS_ARCHIVE POD2
2038 WHERE POD2.PO_DISTRIBUTION_ID = POD1.PO_DISTRIBUTION_ID
2039 AND POD2.LATEST_EXTERNAL_FLAG = 'Y'
2040 AND POD2.REVISION_NUM = p_revision_num);
2041
2042 END IF;
2043
2044 EXCEPTION
2045 WHEN OTHERS THEN
2046 global_stack := 'PORARRELEASE(P_DOCUMENT_ID='''||p_document_id||
2047 ''' P_REVISION_NUM='''||p_revision_num||
2048 '''):'||global_stack;
2049 RAISE;
2050
2051
2052 END PORARRELEASE;
2053
2054
2055 END ECE_PO_ARCHIVE_PKG;
2056
2057