1 PACKAGE BODY OEXPURGE AS
2 /* $Header: OEXPURGB.pls 115.6 99/07/16 08:15:25 porting ship $ */
3
4 PROCEDURE select_purge_orders
5 (dummy_1 VARCHAR2,
6 dummy_2 VARCHAR2,
7 p_low_order_number NUMBER,
8 p_high_order_number NUMBER,
9 p_low_cdate DATE,
10 p_high_cdate DATE,
11 p_low_ddate DATE,
12 p_high_ddate DATE,
13 p_order_category VARCHAR2,
14 p_order_type_id NUMBER,
15 p_customer_id NUMBER)
16 IS
17
18 a_id NUMBER := 0;
19 a_number NUMBER := 0;
20 a_name VARCHAR2(30);
21 return_status NUMBER := 0;
22 v_error_message VARCHAR2(2000);
23
24 CURSOR acursor IS
25 SELECT so.header_id
26 , so.order_number
27 , sot.name
28 FROM so_order_types sot,
29 so_headers so
30 WHERE so.order_number BETWEEN NVL(p_low_order_number,so.order_number)
31 AND NVL( p_high_order_number, so.order_number )
32 -- The time component in Order Date and Creation Date has been stripped off
33 -- before making comparision. Bug# 916858 - propagated from Rel. 11
34 -- ( Bug# 914321 ).
35 AND TRUNC(so.creation_date) BETWEEN
36 NVL( TRUNC(p_low_cdate), TRUNC(so.creation_date) )
37 AND NVL( TRUNC(p_high_cdate), TRUNC(so.creation_date) )
38 AND TRUNC(so.date_ordered) BETWEEN
39 NVL( TRUNC(p_low_ddate), TRUNC(so.date_ordered) )
40 AND NVL( TRUNC(p_high_ddate), TRUNC(so.date_ordered) )
41 AND so.order_category = NVL( p_order_category, so.order_category )
42 AND so.order_type_id = NVL( p_order_type_id, so.order_type_id )
43 AND so.customer_id = NVL( p_customer_id, so.customer_id )
44 AND sot.order_type_id = so.order_type_id
45 AND so.open_flag is null;
46
47 BEGIN
48
49 DELETE FROM SO_PURGE_ORDERS
50 WHERE REQUEST_ID IS NULL;
51
52 /* commit; */
53
54 OPEN ACURSOR;
55 LOOP
56 FETCH ACURSOR INTO a_id, a_number, a_name;
57 EXIT WHEN ACURSOR%NOTFOUND OR ACURSOR%NOTFOUND IS NULL;
58
59 return_status := 0;
60
61 IF return_status = 0 THEN
62 v_error_message := 'Open demand exists for order number: ';
63 return_status := OEXPURGE.so_check_open_demand_orders
64 ( TO_CHAR(a_number), a_name );
65 END IF;
66
67 IF return_status = 0 THEN
68 v_error_message:='Open orders exist in WIP for order number: ';
69 return_status := OEXPURGE.so_check_open_orders
70 ( TO_CHAR(a_number), a_name );
71 END IF;
72
73 IF return_status = 0 THEN
74 v_error_message := 'Open invoices exist for order number: ';
75 return_status := OEXPURGE.so_check_open_invoiced_orders
76 ( TO_CHAR(a_number), a_name );
77 END IF;
78
79 IF return_status = 0 THEN
80 v_error_message := 'Open returns exist for order number: ';
81 return_status := OEXPURGE.so_check_open_returns(a_number,
82 a_name);
83 END IF;
84
85 IF return_status = 0 THEN
86
87 INSERT INTO SO_PURGE_ORDERS
88 (HEADER_ID,
89 CREATION_DATE,
90 CREATED_BY,
91 LAST_UPDATE_DATE,
92 LAST_UPDATED_BY,
93 LAST_UPDATE_LOGIN,
94 REQUEST_ID,
95 PROGRAM_ID,
96 PROGRAM_APPLICATION_ID)
97 VALUES (a_id,
98 sysdate,
99 -1,
100 sysdate,
101 -1,
102 NULL,
103 NULL,
104 0,
105 300);
106
107 END IF;
108
109 IF return_status > 0 THEN
110 v_error_message := v_error_message || TO_CHAR(a_number);
111 ELSE
112 IF return_status < 0 THEN
113 null;
114 END IF;
115 END IF;
116
117 END LOOP;
118 CLOSE ACURSOR;
119
120 COMMIT;
121
122 IF a_id = 0 THEN
123 v_error_message := 'No Open Orders To Purge';
124 -- dbms_output.put_line(v_error_message);
125 END IF;
126
127 END select_purge_orders;
128
129 FUNCTION so_check_open_demand_orders
130 ( p_order_number VARCHAR2,
131 p_order_type_name VARCHAR2 ) RETURN NUMBER IS
132
133 CURSOR SO_PROBLEM_CHECK IS
134 SELECT 'Open demand for this sales order'
135 FROM mtl_sales_orders mso,
136 mtl_demand md
137 WHERE mso.segment1 = p_order_number
138 AND mso.segment2 = p_order_type_name
139 AND mso.sales_order_id = md.demand_source_header_id
140 AND md.demand_source_type IN (2,8)
141 AND md.primary_uom_quantity > NVL( md.completed_quantity, 0 )
142 AND md.row_status_flag = 1;
143
144 x_fetch_value varchar2(80);
145 x_records_exists boolean;
146
147 BEGIN
148 OPEN SO_PROBLEM_CHECK;
149 FETCH SO_PROBLEM_CHECK INTO x_fetch_value;
150 x_records_exists := SO_PROBLEM_CHECK%FOUND;
151 CLOSE SO_PROBLEM_CHECK;
152
153 if (not x_records_exists) then
154 return (0);
155 end if;
156
157 return (1);
158
159 EXCEPTION
160 WHEN OTHERS THEN RETURN (-1);
161 END so_check_open_demand_orders;
162
163
164 FUNCTION so_check_open_invoiced_orders
165 ( p_order_number VARCHAR2,
166 p_order_type_name VARCHAR2 ) RETURN NUMBER IS
167
168 CURSOR SO_PROBLEM_CHECK IS
169 SELECT 'Open invoices for this sales order'
170 FROM ra_customer_trx_lines rctl,
171 ra_customer_trx rct
172 WHERE rctl.interface_line_attribute1 = p_order_number
173 AND rctl.interface_line_attribute2 = p_order_type_name
174 AND rctl.customer_trx_id = rct.customer_trx_id
175 AND rct.complete_flag = 'N';
176
177 x_fetch_value varchar2(80);
178 x_records_exists boolean;
179 BEGIN
180
181 OPEN SO_PROBLEM_CHECK;
182 FETCH SO_PROBLEM_CHECK INTO x_fetch_value;
183 x_records_exists := SO_PROBLEM_CHECK%FOUND;
184 CLOSE SO_PROBLEM_CHECK;
185
186 if (not x_records_exists) then
187 return (0);
188 end if;
189
190 return (1);
191
192 EXCEPTION
193 WHEN OTHERS THEN RETURN (-1);
194 END so_check_open_invoiced_orders;
195
196
197 FUNCTION so_check_open_orders
198 ( p_order_number VARCHAR2,
199 p_order_type_name VARCHAR2 ) RETURN NUMBER IS
200
201 CURSOR SO_PROBLEM_CHECK IS
202 SELECT 'Open work orders for this sales order'
203 FROM mtl_sales_orders mso,
204 wip_so_allocations wsa,
205 wip_discrete_jobs wdj
206 WHERE mso.segment1 = p_order_number
207 AND mso.segment2 = p_order_type_name
208 AND mso.sales_order_id = wsa.demand_source_header_id
209 AND wsa.wip_entity_id = wdj.wip_entity_id
210 AND wdj.date_closed IS NULL
211 AND wdj.status_type = 1;
212
213 x_fetch_value varchar2(80);
214 x_records_exists boolean;
215 BEGIN
216 OPEN SO_PROBLEM_CHECK;
217 FETCH SO_PROBLEM_CHECK INTO x_fetch_value;
218 x_records_exists := SO_PROBLEM_CHECK%FOUND;
219 CLOSE SO_PROBLEM_CHECK;
220
221 if (not x_records_exists) then
222 return (0);
223 end if;
224
225 return (1);
226
227 EXCEPTION
228 WHEN OTHERS THEN RETURN (-1);
229 END so_check_open_orders;
230
231
232 FUNCTION so_check_open_returns( p_order_number NUMBER,
233 p_order_type_name VARCHAR2 ) RETURN NUMBER IS
234
235 CURSOR SO_PROBLEM_CHECK IS
236 SELECT 'Open return for this sales order'
237 FROM so_lines sl1,
238 so_lines sl2,
239 so_headers sh,
240 so_order_types ot
241 WHERE sh.order_number = p_order_number
242 AND sh.order_type_id = ot.order_type_id
243 AND ot.name = p_order_type_name
244 AND sl1.header_id = sh.header_id
245 AND sl2.return_reference_id = sl1.line_id
246 AND sl2.line_type_code = 'RETURN'
247 AND sl2.return_reference_type_code IN ( 'ORDER', 'PO' )
248 AND nvl(sl2.open_flag, 'N') = 'Y';
249
250 x_fetch_value varchar2(80);
251 x_records_exists boolean;
252 BEGIN
253 OPEN SO_PROBLEM_CHECK;
254 FETCH SO_PROBLEM_CHECK INTO x_fetch_value;
255 x_records_exists := SO_PROBLEM_CHECK%FOUND;
256 CLOSE SO_PROBLEM_CHECK;
257
258 if (not x_records_exists) then
259 return (0);
260 end if;
261
262 return (1);
263
264 EXCEPTION
265 WHEN OTHERS THEN RETURN (-1);
266 END so_check_open_returns;
267
268
269
270 PROCEDURE so_order_purge
271 ( p_dummy_1 VARCHAR2,
272 p_dummy_2 VARCHAR2,
273 p_commit_point NUMBER )
274
275 /*=========================================================================*
276 | |
277 | NAME |
278 | so_order_purge purge process |
279 | |
280 | DESCRIPTION |
281 | This procedure drives the order purge process. |
282 | It locks all of the records within is request_id to be |
283 | purged. For each order to be purged, it executes the various purge |
284 | functions deleting all related sales order rows. The purged orders |
285 | are committed based on the commit_point parameter. |
286 | |
287 | If a database error is detected during the execution of any purge |
288 | function, all uncommitted rows are rolled back and an error message |
289 | is written to SO_EXCEPTIONS; using OEPUR as the context prefix. |
290 | |
291 | ARGUMENTS |
292 | Input: p_request_id NUMBER Identifies orders to be purged by this |
293 | sub-process. |
294 | p_commit_point NUMBER Identifies the number of orders to be |
295 | purged prior to committing. |
296 | |
297 | HISTORY |
298 | Date Author Comments |
299 | --------- -------- ---------------------------------------------------- |
300 | 28-Feb-96 tgoldsmi Created |
301 *=========================================================================*/
302
303 IS
304
305 v_header_id NUMBER := 0;
306 commit_ctr NUMBER := 0;
307 return_status NUMBER := 0;
308 p_request_id NUMBER := 0;
309
310 CURSOR purging IS SELECT header_id FROM so_purge_orders;
311
312 BEGIN
313
314 p_request_id := FND_GLOBAL.CONC_REQUEST_ID;
315
316 OPEN purging;
317
318 LOOP
319
320 FETCH purging INTO v_header_id;
321
322 EXIT WHEN purging%NOTFOUND OR purging%NOTFOUND IS NULL;
323 -- end of fetch or empty cursor
324
325 /*********************************************************************
326 * return_status *
327 * Success: Any positive value. Processing continues.. *
328 * 0 = Success, rows deleted/purged *
329 * 100, 1403 are considered to be successful *
330 * Failure: Any negative value. Processing is rolled back. *
331 * Error has already been recorded in so_exceptions *
332 *********************************************************************/
333
334 return_status := OEXPURGE.so_purge_headers( v_header_id,
335 p_request_id);
336
337 IF return_status > -1 THEN
338 return_status := OEXPURGE.so_purge_line_approvals( v_header_id,
339 p_request_id );
340 END IF;
341
342 IF return_status > -1 THEN
343 return_status := OEXPURGE.so_purge_backorder_cancelled(
344 v_header_id,
345 p_request_id );
346 END IF;
347
348 IF return_status > -1 THEN
349 return_status := OEXPURGE.so_purge_lines( v_header_id,
350 p_request_id );
351 END IF;
352
353 IF return_status > -1 THEN
354 return_status := OEXPURGE.so_purge_note_references(v_header_id,
355 p_request_id );
356 END IF;
357
358 IF return_status > -1 THEN
359 return_status := OEXPURGE.so_purge_order_approvals(v_header_id,
360 p_request_id );
361 END IF;
362
363 IF return_status > -1 THEN
364 return_status := OEXPURGE.so_purge_order_cancellations(
365 v_header_id,
366 p_request_id);
367 END IF;
368
369 IF return_status > -1 THEN
370 return_status := OEXPURGE.so_purge_order_holds( v_header_id,
371 p_request_id );
372 END IF;
373
374 IF return_status > -1 THEN
375 return_status := OEXPURGE.so_purge_picking_headers(v_header_id,
376 p_request_id );
377 END IF;
378
379 IF return_status > -1 THEN
380 return_status := OEXPURGE.so_purge_picking_rules( v_header_id,
381 p_request_id );
382 END IF;
383
384 IF return_status > -1 THEN
385 return_status:=OEXPURGE.so_purge_price_adjustments(v_header_id,
386 p_request_id );
387 END IF;
388
389 IF return_status > -1 THEN
390 return_status := OEXPURGE.so_purge_sales_credits( v_header_id,
391 p_request_id );
392 END IF;
393
394 -- dbms_output.put_line('before delete');
395
396 IF return_status > -1 THEN -- Success !
397 DELETE FROM so_purge_orders -- Delete the purge record
398 WHERE header_id = v_header_id;
399
400 -- dbms_output.put_line('after delete');
401
402 commit_ctr := commit_ctr + 1;
403 IF commit_ctr > p_commit_point THEN -- Commit purged orders if
404 COMMIT; -- threshold is exceeded
405 commit_ctr := 0;
406 END IF;
407 ELSE commit_ctr := 0; -- Failure, error has already
408 END IF; -- been recorded
409
410 END LOOP;
411
412 CLOSE purging;
413
414 /* IF commit_ctr > p_commit_point
415 THEN COMMIT; -- Commit remaining purged orders
416 ELSE ROLLBACK; -- Rollback to release any remaining resources
417 -- dbms_output.put_line('rollback1');
418 END IF;
419 */
420 COMMIT;
421 EXCEPTION -- Exception handler to record error
422 WHEN OTHERS THEN
423 return_status := SQLCODE;
424 ROLLBACK;
425 -- dbms_output.put_line('rollback 2');
426 so_record_errors( return_status, p_request_id, v_header_id,
427 'OEPUR: SO_PURGE_ORDERS', NULL );
428 CLOSE purging;
429 END so_order_purge;
430
431
432
433 FUNCTION so_purge_freight_charges
434 ( p_picking_header_id NUMBER,
435 p_request_id NUMBER ) RETURN NUMBER IS
436
437 return_status NUMBER := 0;
438 syntax_required NUMBER := 0;
439
440 CURSOR SO_LOCK_RECORDS IS
441 SELECT picking_header_id
442 FROM so_freight_charges
443 WHERE picking_header_id = p_picking_header_id
444 FOR UPDATE NOWAIT;
445 BEGIN
446 OPEN SO_LOCK_RECORDS; -- Lock all rows to be purged
447 CLOSE SO_LOCK_RECORDS;
448
449 DELETE FROM so_freight_charges -- Delete all rows to be purged
450 WHERE picking_header_id = p_picking_header_id;
451
452 RETURN SQLCODE;
453
454 EXCEPTION -- Exception handler to record error
455 WHEN OTHERS THEN
456 return_status := SQLCODE;
457 ROLLBACK;
458 so_record_errors( return_status,
459 p_request_id,
460 p_picking_header_id,
461 'OEPUR: SO_FREIGHT_CHARGES',
462 NULL );
463 CLOSE SO_LOCK_RECORDS;
464 RETURN return_status;
465
466 END so_purge_freight_charges;
467
468
469 FUNCTION so_purge_headers
470 ( p_header_id NUMBER,
471 p_request_id NUMBER ) RETURN NUMBER IS
472
473 return_status NUMBER := 0;
474 syntax_required NUMBER := 0;
475
476 CURSOR SO_LOCK_RECORDS IS
477 SELECT header_id -- Lock all rows to be purged
478 FROM so_headers
479 WHERE header_id = p_header_id
480 FOR UPDATE NOWAIT;
481 BEGIN
482 OPEN SO_LOCK_RECORDS; -- Lock all rows to be purged
483 CLOSE SO_LOCK_RECORDS;
484
485 DELETE FROM so_headers
486 WHERE header_id = p_header_id;
487
488 RETURN SQLCODE;
489
490 EXCEPTION -- Exception handler to record error
491 WHEN OTHERS THEN
492 return_status := SQLCODE;
493 ROLLBACK;
494 so_record_errors( return_status,
495 p_request_id,
496 p_header_id,
497 'OEPUR: SO_HEADERS',
498 NULL );
499 CLOSE SO_LOCK_RECORDS;
500 RETURN return_status;
501
502 END so_purge_headers;
503
504
505 FUNCTION so_purge_hold_releases
506 ( p_release_id NUMBER,
507 p_request_id NUMBER ) RETURN NUMBER IS
508
509 return_status NUMBER := 0;
510 syntax_required NUMBER := 0;
511
512 CURSOR SO_LOCK_RECORDS IS
513 SELECT hold_release_id -- Lock rows to be purged
514 FROM so_hold_releases
515 WHERE hold_release_id = p_release_id
516 FOR UPDATE NOWAIT;
517 BEGIN
518 OPEN SO_LOCK_RECORDS; -- Lock all rows to be purged
519 CLOSE SO_LOCK_RECORDS;
520
521 DELETE FROM so_hold_releases
522 WHERE hold_release_id = p_release_id;
523
524 RETURN SQLCODE;
525
526 EXCEPTION -- Exception handler to record error
527 WHEN OTHERS THEN
528 return_status := SQLCODE;
529 ROLLBACK;
530 so_record_errors( return_status,
531 p_request_id,
532 p_release_id,
533 'OEPUR: SO_HOLD_RELEASES',
534 NULL );
535 CLOSE SO_LOCK_RECORDS;
536 RETURN return_status;
537
538 END so_purge_hold_releases;
539
540
541 FUNCTION so_purge_hold_sources
542 ( p_source_id NUMBER,
543 p_request_id NUMBER ) RETURN NUMBER IS
544
545 return_status NUMBER := 0;
546 syntax_required NUMBER := 0;
547
548 CURSOR SO_LOCK_RECORDS IS
549 SELECT hold_source_id -- Lock rows to be purged
550 FROM so_hold_sources
551 WHERE hold_source_id = p_source_id
552 FOR UPDATE NOWAIT;
553 BEGIN
554 OPEN SO_LOCK_RECORDS; -- Lock all rows to be purged
555 CLOSE SO_LOCK_RECORDS;
556
557 DELETE FROM so_hold_sources
558 WHERE hold_source_id = p_source_id;
559
560 RETURN SQLCODE;
561
562 EXCEPTION -- Exception handler to record error
563 WHEN OTHERS THEN
564 return_status := SQLCODE;
565 ROLLBACK;
566 so_record_errors( return_status,
567 p_request_id,
568 p_source_id,
569 'OEPUR: SO_HOLD_SOURCES',
570 NULL );
571 CLOSE SO_LOCK_RECORDS;
572 RETURN return_status;
573
574 END so_purge_hold_sources;
575
576
577 FUNCTION so_purge_line_approvals
578 ( p_header_id NUMBER,
579 p_request_id NUMBER ) RETURN NUMBER IS
580
581 return_status NUMBER := 0;
582 syntax_required NUMBER := 0;
583
584 CURSOR SO_LOCK_RECORDS IS
585 SELECT header_id -- Lock rows to be purged
586 FROM so_line_approvals
587 WHERE header_id = p_header_id
588 FOR UPDATE NOWAIT;
589 BEGIN
590 OPEN SO_LOCK_RECORDS; -- Lock all rows to be purged
591 CLOSE SO_LOCK_RECORDS;
592
593 DELETE FROM so_line_approvals
594 WHERE header_id = p_header_id;
595
596 RETURN SQLCODE;
597
598 EXCEPTION -- Exception handler to record error
599 WHEN OTHERS THEN
600 return_status := SQLCODE;
601 ROLLBACK;
602 so_record_errors( return_status,
603 p_request_id,
604 p_header_id,
605 'OEPUR: SO_LINE_APPROVALS',
606 NULL );
607 CLOSE SO_LOCK_RECORDS;
608 RETURN return_status;
609
610 END so_purge_line_approvals;
611
612
613 FUNCTION so_purge_line_details
614 ( p_line_id NUMBER,
615 p_request_id NUMBER ) RETURN NUMBER IS
616
617 return_status NUMBER := 0;
618 syntax_required NUMBER := 0;
619
620 CURSOR SO_LOCK_RECORDS IS
621 SELECT line_id
622 FROM so_line_details
623 WHERE line_id = p_line_id
624 FOR UPDATE NOWAIT;
625 BEGIN
626 OPEN SO_LOCK_RECORDS; -- Lock all rows to be purged
627 CLOSE SO_LOCK_RECORDS;
628
629 DELETE FROM so_line_details
630 WHERE line_id = p_line_id;
631
632 RETURN SQLCODE;
633
634 EXCEPTION -- Exception handler to record error
635 WHEN OTHERS THEN
636 return_status := SQLCODE;
637 ROLLBACK;
638 so_record_errors( return_status,
639 p_request_id,
640 p_line_id,
641 'OEPUR: SO_LINE_DETAILS',
642 NULL );
643 CLOSE SO_LOCK_RECORDS;
644 RETURN return_status;
645
646 END so_purge_line_details;
647
648
649 FUNCTION so_purge_line_service_details
650 ( p_line_id NUMBER,
651 p_request_id NUMBER ) RETURN NUMBER IS
652
653 return_status NUMBER := 0;
654 syntax_required NUMBER := 0;
655
656 CURSOR SO_LOCK_RECORDS IS
657 SELECT line_id
658 FROM so_line_service_details
659 WHERE line_id = p_line_id
660 FOR UPDATE NOWAIT;
661 BEGIN
662 OPEN SO_LOCK_RECORDS; -- Lock all rows to be purged
663 CLOSE SO_LOCK_RECORDS;
664
665 DELETE FROM so_line_service_details
666 WHERE line_id = p_line_id;
667
668 RETURN SQLCODE;
669
670 EXCEPTION -- Exception handler to record error
671 WHEN OTHERS THEN
672 return_status := SQLCODE;
673 ROLLBACK;
674 so_record_errors( return_status,
675 p_request_id,
676 p_line_id,
677 'OEPUR: SO_LINE_SERVICE_DETAILS',
678 NULL );
679 CLOSE SO_LOCK_RECORDS;
680 RETURN return_status;
681
682 END so_purge_line_service_details;
683
684
685 FUNCTION so_purge_lines
686 ( p_header_id NUMBER,
687 p_request_id NUMBER ) RETURN NUMBER IS
688
689 return_status NUMBER := 0;
690 syntax_required NUMBER := 0;
691 v_line_id NUMBER := 0;
692
693 CURSOR purge_lines IS
694 SELECT DISTINCT line_id -- Select the unique line_ids to be purged
695 FROM so_lines
696 WHERE header_id = p_header_id;
697 BEGIN
698 OPEN purge_lines;
699
700 LOOP
701 FETCH purge_lines INTO v_line_id;
702
703 EXIT WHEN purge_lines%NOTFOUND -- end of fetch
704 OR purge_lines%NOTFOUND IS NULL; -- empty cursor
705
706 SELECT line_id
707 INTO syntax_required
708 FROM so_lines
709 WHERE line_id = v_line_id
710 FOR UPDATE NOWAIT;
711
712 return_status := so_purge_line_details( v_line_id,
713 p_request_id );
714 IF return_status > -1 THEN
715 return_status := so_purge_line_service_details( v_line_id,
716 p_request_id );
717 END IF;
718
719 IF return_status > -1 THEN
720 return_status := so_purge_order_cancel_lines( v_line_id,
721 p_request_id );
722 END IF;
723
724 /* bug 683844 -- clean mtl_so_rma_interface table */
725 IF return_status > -1 THEN
726 return_status := so_purge_mtl_so_rma_interface( v_line_id,
727 p_request_id );
728 END IF;
729
730 IF return_status > -1 THEN
731 DELETE FROM so_lines
732 WHERE line_id = v_line_id;
733 ELSE
734 EXIT; -- abort additional processing
735 END IF;
736
737 END LOOP;
738
739 CLOSE purge_lines;
740 RETURN SQLCODE;
741
742 EXCEPTION -- Exception handler to record error
743 WHEN OTHERS THEN
744 return_status := SQLCODE;
745 ROLLBACK;
746 so_record_errors( return_status,
747 p_request_id,
748 v_line_id,
749 'OEPUR: SO_LINES',
750 NULL );
751 CLOSE purge_lines;
752 RETURN return_status;
753
754 END so_purge_lines;
755
756
757 FUNCTION so_purge_note_references
758 ( p_header_id NUMBER,
759 p_request_id NUMBER ) RETURN NUMBER IS
760
761 return_status NUMBER := 0;
762 syntax_required NUMBER := 0;
763
764 CURSOR SO_LOCK_RECORDS IS
765 SELECT header_id -- Lock rows to be purged
766 FROM so_note_references
767 WHERE header_id = p_header_id
768 FOR UPDATE NOWAIT;
769 BEGIN
770 OPEN SO_LOCK_RECORDS;
771 CLOSE SO_LOCK_RECORDS;
772
773 DELETE FROM so_note_references
774 WHERE header_id = p_header_id;
775
776 RETURN SQLCODE;
777
778 EXCEPTION -- Exception handler to record error
779 WHEN OTHERS THEN
780 return_status := SQLCODE;
781 ROLLBACK;
782 so_record_errors( return_status,
783 p_request_id,
784 p_header_id,
785 'OEPUR: SO_NOTE_REFERENCES',
786 NULL );
787 CLOSE SO_LOCK_RECORDS;
788 RETURN return_status;
789
790 END so_purge_note_references;
791
792
793 FUNCTION so_purge_order_approvals
794 ( p_header_id NUMBER,
795 p_request_id NUMBER ) RETURN NUMBER IS
796
797 return_status NUMBER := 0;
798 syntax_required NUMBER := 0;
799
800 CURSOR SO_LOCK_RECORDS IS
801 SELECT header_id -- Lock rows to be purged
802 FROM so_order_approvals
803 WHERE header_id = p_header_id
804 FOR UPDATE NOWAIT;
805 BEGIN
806 OPEN SO_LOCK_RECORDS;
807 CLOSE SO_LOCK_RECORDS;
808
809 DELETE FROM so_order_approvals
810 WHERE header_id = p_header_id;
811
812 RETURN SQLCODE;
813
814 EXCEPTION -- Exception handler to record error
815 WHEN OTHERS THEN
816 return_status := SQLCODE;
817 ROLLBACK;
818 so_record_errors( return_status,
819 p_request_id,
820 p_header_id,
821 'OEPUR: SO_ORDER_APPROVALS',
822 NULL );
823 CLOSE SO_LOCK_RECORDS;
824 RETURN return_status;
825
826 END so_purge_order_approvals;
827
828
829 /*************************************************************
830 * The lines are purged in this case because before 9.4.2 we *
831 * used to leave the header_id null and only fill in the *
832 * line_id and the data was never changed during upgrade. *
833 *************************************************************/
834
835 FUNCTION so_purge_order_cancel_lines
836 ( p_line_id NUMBER,
837 p_request_id NUMBER ) RETURN NUMBER IS
838
839 return_status NUMBER := 0;
840 syntax_required NUMBER := 0;
841
842 CURSOR SO_LOCK_RECORDS IS
843 SELECT line_id -- Lock rows to be purged
844 FROM so_order_cancellations
845 WHERE line_id = p_line_id
846 FOR UPDATE NOWAIT;
847 BEGIN
848 OPEN SO_LOCK_RECORDS;
849 CLOSE SO_LOCK_RECORDS;
850
851 DELETE FROM so_order_cancellations
852 WHERE line_id = p_line_id;
853
854 RETURN SQLCODE;
855
856 EXCEPTION -- Exception handler to record error
857 WHEN OTHERS THEN
858 return_status := SQLCODE;
859 ROLLBACK;
860 so_record_errors( return_status,
861 p_request_id,
862 p_line_id,
863 'OEPUR: SO_ORDER_CANCELLATIONS',
864 NULL );
865 CLOSE SO_LOCK_RECORDS;
866 RETURN return_status;
867
868 END so_purge_order_cancel_lines;
869
870 /*************************************************************
871 * The mtl_so_rma_interface needs to be cleaned when the *
872 * corresponding returns lines are purged for the following *
873 * reasons: *
874 * 1. the reference to line_id becomes dangling pointer *
875 * 2. Inventory can't delete the item with error message: *
876 * RMA's exist for this item in the interface table *
877 * (MTL_SO_RMA_INTERFACE) *
878 *************************************************************/
879
880 FUNCTION so_purge_mtl_so_rma_interface
881 ( p_line_id NUMBER,
882 p_request_id NUMBER ) RETURN NUMBER IS
883
884 return_status NUMBER := 0;
885 v_rma_interface_id NUMBER := 0;
886
887 CURSOR purge_mtl_so_rma_interface IS
888 SELECT rma_interface_id -- Lock rows to be purged
889 FROM mtl_so_rma_interface
890 WHERE rma_line_id = p_line_id;
891 BEGIN
892 OPEN purge_mtl_so_rma_interface;
893 LOOP
894 FETCH purge_mtl_so_rma_interface into v_rma_interface_id;
895 EXIT WHEN purge_mtl_so_rma_interface%NOTFOUND -- end of fetch
896 OR purge_mtl_so_rma_interface%NOTFOUND IS NULL; -- empty cursor
897
898 return_status := so_purge_mtl_so_rma_receipts( v_rma_interface_id,
899 p_request_id );
900 IF return_status > -1 THEN
901 DELETE FROM mtl_so_rma_interface
902 WHERE rma_interface_id = v_rma_interface_id;
903 ELSE
904 EXIT;
905 END IF;
906
907 END LOOP;
908 CLOSE purge_mtl_so_rma_interface;
909 RETURN SQLCODE;
910
911 EXCEPTION -- Exception handler to record error
912 WHEN OTHERS THEN
913 return_status := SQLCODE;
914 ROLLBACK;
915 so_record_errors( return_status,
916 p_request_id,
917 p_line_id,
918 'OEPUR: MTL_SO_RMA_INTERFACE',
919 NULL );
920 CLOSE purge_mtl_so_rma_interface;
921 RETURN return_status;
922
923 END so_purge_mtl_so_rma_interface;
924
925 /*************************************************************
926 * The mtl_so_rma_receipts needs to be cleaned when the *
927 * corresponding rma_interface line is purged for the *
928 * following reasons: *
929 * 1. the reference to rma_interface_id becomes dangling *
930 * 2. when a return line is purged, corresponding interfaced *
931 * line needs to be purged because: *
932 * Inventory can't delete the item with error message: *
933 * RMA's exist for this item in the interface table *
934 * (MTL_SO_RMA_INTERFACE) *
935 *************************************************************/
936
937 FUNCTION so_purge_mtl_so_rma_receipts
938 ( p_rma_interface_id NUMBER,
939 p_request_id NUMBER ) RETURN NUMBER IS
940
941 return_status NUMBER := 0;
942 v_rma_receipt_id NUMBER := 0;
943
944 CURSOR purge_mtl_so_rma_receipts IS
945 SELECT rma_receipt_id -- Lock rows to be purged
946 FROM mtl_so_rma_receipts
947 WHERE rma_interface_id = p_rma_interface_id;
948 BEGIN
949 OPEN purge_mtl_so_rma_receipts;
950 LOOP
951 FETCH purge_mtl_so_rma_receipts into v_rma_receipt_id;
952 EXIT WHEN purge_mtl_so_rma_receipts%NOTFOUND -- end of fetch
953 OR purge_mtl_so_rma_receipts%NOTFOUND IS NULL; -- empty cursor
954
955 DELETE FROM mtl_so_rma_receipts
956 WHERE rma_receipt_id = v_rma_receipt_id;
957
958 END LOOP;
959 CLOSE purge_mtl_so_rma_receipts;
960 RETURN SQLCODE;
961
962 EXCEPTION -- Exception handler to record error
963 WHEN OTHERS THEN
964 return_status := SQLCODE;
965 ROLLBACK;
966 so_record_errors( return_status,
967 p_request_id,
968 p_rma_interface_id,
969 'OEPUR: MTL_SO_RMA_RECEIPTS',
970 NULL );
971 CLOSE purge_mtl_so_rma_receipts;
972 RETURN return_status;
973
974 END so_purge_mtl_so_rma_receipts;
975
976 FUNCTION so_purge_order_cancellations
977 ( p_header_id NUMBER,
978 p_request_id NUMBER ) RETURN NUMBER IS
979
980 return_status NUMBER := 0;
981 syntax_required NUMBER := 0;
982
983 CURSOR SO_LOCK_RECORDS IS
984 SELECT header_id -- Lock rows to be purged
985 FROM so_order_cancellations
986 WHERE header_id = p_header_id
987 FOR UPDATE NOWAIT;
988 BEGIN
989 OPEN SO_LOCK_RECORDS;
990 CLOSE SO_LOCK_RECORDS;
991
992 DELETE FROM so_order_cancellations
993 WHERE header_id = p_header_id;
994
995 RETURN SQLCODE;
996
997 EXCEPTION -- Exception handler to record error
998 WHEN OTHERS THEN
999 return_status := SQLCODE;
1000 ROLLBACK;
1001 so_record_errors( return_status,
1002 p_request_id,
1003 p_header_id,
1004 'OEPUR: SO_ORDER_CANCELLATIONS',
1005 NULL );
1006 CLOSE SO_LOCK_RECORDS;
1007 RETURN return_status;
1008
1009 END so_purge_order_cancellations;
1010
1011 FUNCTION so_purge_order_holds
1012 ( p_header_id NUMBER,
1013 p_request_id NUMBER ) RETURN NUMBER IS
1014
1015 return_status NUMBER := 0;
1016 syntax_required NUMBER := 0;
1017 v_release_id NUMBER := 0;
1018 v_source_id NUMBER := 0;
1019 v_order_hold_id NUMBER := 0;
1020
1021 CURSOR purge_holds IS
1022 SELECT DISTINCT NVL( hold_release_id, 0 ),
1023 NVL( hold_source_id, 0 ),
1024 order_hold_id
1025 FROM so_order_holds
1026 WHERE header_id = p_header_id;
1027 BEGIN
1028 OPEN purge_holds;
1029
1030 LOOP
1031 FETCH purge_holds INTO v_release_id, v_source_id, v_order_hold_id;
1032
1033 EXIT WHEN purge_holds%NOTFOUND -- end of fetch
1034 OR purge_holds%NOTFOUND IS NULL; -- empty cursor
1035
1036
1037 IF v_release_id > 0 THEN
1038 return_status := so_purge_hold_releases( v_release_id,
1039 p_request_id );
1040 END IF;
1041
1042 IF return_status > -1 AND v_source_id > 0 THEN
1043 return_status := so_purge_hold_sources( v_source_id,
1044 p_request_id );
1045 END IF;
1046
1047 IF return_status > -1 THEN
1048 SELECT header_id
1049 INTO syntax_required
1050 FROM so_order_holds
1051 WHERE order_hold_id = v_order_hold_id
1052 FOR UPDATE NOWAIT;
1053
1054 DELETE FROM so_order_holds
1055 WHERE order_hold_id = v_order_hold_id;
1056 ELSE
1057 EXIT; -- abort additional processing
1058 END IF;
1059
1060 END LOOP;
1061
1062 CLOSE purge_holds;
1063 RETURN SQLCODE;
1064
1065 EXCEPTION -- Exception handler to record error
1066 WHEN OTHERS THEN
1067 return_status := SQLCODE;
1068 ROLLBACK;
1069 so_record_errors( return_status,
1070 p_request_id,
1071 v_order_hold_id,
1072 'OEPUR: SO_ORDER_HOLDS',
1073 NULL );
1074 CLOSE purge_holds;
1075 RETURN return_status;
1076
1077 END so_purge_order_holds;
1078
1079
1080 FUNCTION so_purge_picking_batches
1081 ( p_batch_id NUMBER,
1082 p_header_id NUMBER,
1083 p_request_id NUMBER ) RETURN NUMBER IS
1084
1085 return_status NUMBER := 0;
1086 syntax_required NUMBER := 0;
1087
1088 CURSOR SO_LOCK_RECORDS IS
1089 SELECT batch_id -- Lock rows to be purged
1090 FROM so_picking_batches
1091 WHERE batch_id = p_batch_id
1092 AND header_id = p_header_id
1093 FOR UPDATE NOWAIT;
1094 BEGIN
1095 OPEN SO_LOCK_RECORDS;
1096 CLOSE SO_LOCK_RECORDS;
1097
1098 DELETE FROM so_picking_batches
1099 WHERE batch_id = p_batch_id
1100 AND header_id = p_header_id;
1101
1102 RETURN SQLCODE;
1103
1104 EXCEPTION -- Exception handler to record error
1105 WHEN OTHERS THEN
1106 return_status := SQLCODE;
1107 ROLLBACK;
1108 so_record_errors( return_status,
1109 p_request_id,
1110 p_batch_id,
1111 'OEPUR: SO_PICKING_BATCHES',
1112 NULL );
1113 CLOSE SO_LOCK_RECORDS;
1114 RETURN return_status;
1115
1116 END so_purge_picking_batches;
1117
1118 FUNCTION so_purge_picking_cancellations
1119 ( p_picking_line_id NUMBER,
1120 p_request_id NUMBER ) RETURN NUMBER IS
1121
1122 return_status NUMBER := 0;
1123 syntax_required NUMBER := 0;
1124
1125 CURSOR SO_LOCK_RECORDS IS
1126 SELECT picking_line_id -- Lock rows to be purged
1127 FROM so_picking_cancellations
1128 WHERE picking_line_id = p_picking_line_id
1129 FOR UPDATE NOWAIT;
1130 BEGIN
1131 OPEN SO_LOCK_RECORDS;
1132 CLOSE SO_LOCK_RECORDS;
1133
1134 DELETE FROM so_picking_cancellations
1135 WHERE picking_line_id = p_picking_line_id;
1136
1137 RETURN SQLCODE;
1138
1139 EXCEPTION -- Exception handler to record error
1140 WHEN OTHERS THEN
1141 return_status := SQLCODE;
1142 ROLLBACK;
1143 so_record_errors( return_status,
1144 p_request_id,
1145 p_picking_line_id,
1146 'OEPUR: SO_PICKING_CANCELLATIONS',
1147 NULL );
1148 CLOSE SO_LOCK_RECORDS;
1149 RETURN return_status;
1150
1151 END so_purge_picking_cancellations;
1152
1153
1154 FUNCTION so_purge_picking_headers
1155 ( p_header_id NUMBER,
1156 p_request_id NUMBER ) RETURN NUMBER IS
1157
1158 syntax_required NUMBER := 0;
1159 return_status NUMBER := 0;
1160 v_batch_id NUMBER := 0;
1161 v_picking_header_id NUMBER := 0;
1162
1163 CURSOR purge_picking IS
1164 SELECT picking_header_id,
1165 batch_id
1166 FROM so_picking_headers
1167 WHERE order_header_id = p_header_id;
1168 BEGIN
1169 OPEN purge_picking;
1170
1171 LOOP
1172 FETCH purge_picking INTO v_picking_header_id, v_batch_id;
1173
1174 EXIT WHEN purge_picking%NOTFOUND -- end of fetch
1175 OR purge_picking%NOTFOUND IS NULL; -- empty cursor
1176
1177 SELECT order_header_id -- Lock rows to be purged
1178 INTO syntax_required
1179 FROM so_picking_headers
1180 WHERE picking_header_id = v_picking_header_id
1181 AND batch_id = v_batch_id
1182 FOR UPDATE NOWAIT;
1183
1184 return_status := so_purge_picking_batches( v_batch_id,
1185 p_header_id,
1186 p_request_id );
1187
1188 IF return_status > -1 THEN
1189 return_status := so_purge_picking_lines( v_picking_header_id,
1190 p_request_id );
1191 END IF;
1192
1193 IF return_status > -1 THEN
1194 return_status := so_purge_freight_charges( v_picking_header_id,
1195 p_request_id );
1196 END IF;
1197
1198 IF return_status > -1 THEN
1199 DELETE FROM so_picking_headers
1200 WHERE picking_header_id = v_picking_header_id
1201 AND batch_id = v_batch_id;
1202 ELSE
1203 EXIT; -- abort additional processing
1204 END IF;
1205
1206 END LOOP;
1207
1208 CLOSE purge_picking;
1209 RETURN SQLCODE;
1210
1211 EXCEPTION -- Exception handler to record error
1212 WHEN OTHERS THEN
1213 return_status := SQLCODE;
1214 ROLLBACK;
1215 so_record_errors( return_status,
1216 p_request_id,
1217 v_picking_header_id,
1218 'OEPUR: SO_PICKING_HEADERS',
1219 NULL );
1220 CLOSE purge_picking;
1221 RETURN return_status;
1222
1223 END so_purge_picking_headers;
1224
1225 FUNCTION so_purge_picking_line_details
1226 ( p_picking_line_id NUMBER,
1227 p_request_id NUMBER ) RETURN NUMBER IS
1228
1229 return_status NUMBER := 0;
1230 syntax_required NUMBER := 0;
1231
1232 CURSOR SO_LOCK_RECORDS IS
1233 SELECT picking_line_id -- Lock rows to be purged
1234 FROM so_picking_line_details
1235 WHERE picking_line_id = p_picking_line_id
1236 FOR UPDATE NOWAIT;
1237 BEGIN
1238 OPEN SO_LOCK_RECORDS;
1239 CLOSE SO_LOCK_RECORDS;
1240
1241 DELETE FROM so_picking_line_details
1242 WHERE picking_line_id = p_picking_line_id;
1243
1244 RETURN SQLCODE;
1245
1246 EXCEPTION -- Exception handler to record error
1247 WHEN OTHERS THEN
1248 return_status := SQLCODE;
1249 ROLLBACK;
1250 so_record_errors( return_status,
1251 p_request_id,
1252 p_picking_line_id,
1253 'OEPUR: SO_PICKING_LINE_DETAILS',
1254 NULL );
1255 CLOSE SO_LOCK_RECORDS;
1256 RETURN return_status;
1257
1258 END so_purge_picking_line_details;
1259
1260
1261 FUNCTION so_purge_picking_lines
1262 ( p_picking_header_id NUMBER,
1263 p_request_id NUMBER ) RETURN NUMBER IS
1264
1265 return_status NUMBER := 0;
1266 syntax_required NUMBER := 0;
1267 v_picking_line_id NUMBER := 0;
1268
1269 CURSOR purge_lines IS
1270 SELECT picking_line_id
1271 FROM so_picking_lines
1272 WHERE picking_header_id = p_picking_header_id;
1273 BEGIN
1274
1275 OPEN purge_lines;
1276
1277 LOOP
1278 FETCH purge_lines
1279 INTO v_picking_line_id;
1280
1281 EXIT WHEN purge_lines%NOTFOUND -- end of fetch
1282 OR purge_lines%NOTFOUND IS NULL; -- empty cursor
1283
1284 SELECT picking_line_id -- Lock rows to be purged
1285 INTO syntax_required
1286 FROM so_picking_lines
1287 WHERE picking_line_id = v_picking_line_id
1288 FOR UPDATE NOWAIT;
1289
1290 return_status := so_purge_picking_line_details( v_picking_line_id,
1291 p_request_id );
1292 IF return_status > -1 THEN
1293 return_status:=so_purge_picking_cancellations(v_picking_line_id,
1294 p_request_id );
1295 END IF;
1296
1297 IF return_status > -1 THEN
1298 DELETE FROM so_picking_lines
1299 WHERE picking_line_id = v_picking_line_id;
1300 ELSE
1301 EXIT; -- abort additional processing
1302 END IF;
1303
1304 END LOOP;
1305
1306 CLOSE purge_lines;
1307 RETURN SQLCODE;
1308
1309 EXCEPTION -- Exception handler to record error
1310 WHEN OTHERS THEN
1311 return_status := SQLCODE;
1312 ROLLBACK;
1313 so_record_errors( return_status,
1314 p_request_id,
1315 v_picking_line_id,
1316 'OEPUR: SO_PICKING_LINES',
1317 NULL );
1318 CLOSE purge_lines;
1319 RETURN return_status;
1320
1321 END so_purge_picking_lines;
1322
1323
1324 FUNCTION so_purge_backorder_cancelled
1325 ( p_header_id NUMBER,
1326 p_request_id NUMBER ) RETURN NUMBER IS
1327
1328 return_status NUMBER := 0;
1329 syntax_required NUMBER := 0;
1330 v_picking_line_id NUMBER := 0;
1331
1332 CURSOR purge_backorder_lines IS
1333 SELECT picking_line_id
1334 FROM so_picking_lines spl, so_lines sl
1335 WHERE spl.picking_header_id = -1
1336 AND sl.line_id = spl.order_line_id
1337 AND sl.header_id = p_header_id;
1338 BEGIN
1339
1340 OPEN purge_backorder_lines;
1341
1342 LOOP
1343 FETCH purge_backorder_lines
1344 INTO v_picking_line_id;
1345
1346 EXIT WHEN purge_backorder_lines%NOTFOUND -- end of fetch
1347 OR purge_backorder_lines%NOTFOUND IS NULL; -- empty cursor
1348
1349 SELECT picking_line_id -- Lock rows to be purged
1350 INTO syntax_required
1351 FROM so_picking_lines
1352 WHERE picking_line_id = v_picking_line_id
1353 FOR UPDATE NOWAIT;
1354
1355 return_status := so_purge_picking_line_details( v_picking_line_id,
1356 p_request_id );
1357 IF return_status > -1 THEN
1358 return_status:=so_purge_picking_cancellations(v_picking_line_id,
1359 p_request_id );
1360 END IF;
1361 IF return_status > -1 THEN
1362 DELETE FROM so_picking_lines
1363 WHERE picking_line_id = v_picking_line_id;
1364 ELSE
1365 EXIT; -- abort additional processing
1366 END IF;
1367
1368 END LOOP;
1369
1370 CLOSE purge_backorder_lines;
1371 RETURN SQLCODE;
1372
1373 EXCEPTION -- Exception handler to record error
1374 WHEN OTHERS THEN
1375 return_status := SQLCODE;
1376 ROLLBACK;
1377 so_record_errors( return_status,
1378 p_request_id,
1379 v_picking_line_id,
1380 'OEPUR: SO_PICKING_LINES',
1381 NULL );
1382 CLOSE purge_backorder_lines;
1383 RETURN return_status;
1384
1385 END so_purge_backorder_cancelled;
1386
1387
1388 FUNCTION so_purge_picking_rules
1389 ( p_header_id NUMBER,
1390 p_request_id NUMBER ) RETURN NUMBER IS
1391
1392 return_status NUMBER := 0;
1393 syntax_required NUMBER := 0;
1394
1395 CURSOR SO_LOCK_RECORDS IS
1396 SELECT header_id -- Lock rows to be purged
1397 FROM so_picking_rules
1398 WHERE header_id = p_header_id
1399 FOR UPDATE NOWAIT;
1400 BEGIN
1401 OPEN SO_LOCK_RECORDS;
1402 CLOSE SO_LOCK_RECORDS;
1403
1404 DELETE FROM so_picking_rules
1405 WHERE header_id = p_header_id;
1406
1407 RETURN SQLCODE;
1408
1409 EXCEPTION -- Exception handler to record error
1410 WHEN OTHERS THEN
1411 return_status := SQLCODE;
1412 ROLLBACK;
1413 so_record_errors( return_status,
1414 p_request_id,
1415 p_header_id,
1416 'OEPUR: SO_PICKING_RULES',
1417 NULL );
1418 CLOSE SO_LOCK_RECORDS;
1419 RETURN return_status;
1420
1421 END so_purge_picking_rules;
1422
1423
1424 FUNCTION so_purge_price_adjustments
1425 ( p_header_id NUMBER,
1426 p_request_id NUMBER ) RETURN NUMBER IS
1427
1428 return_status NUMBER := 0;
1429 syntax_required NUMBER := 0;
1430
1431 CURSOR SO_LOCK_RECORDS IS
1432 SELECT header_id -- Lock rows to be purged
1433 FROM so_price_adjustments
1434 WHERE header_id = p_header_id
1435 FOR UPDATE NOWAIT;
1436 BEGIN
1437 OPEN SO_LOCK_RECORDS;
1438 CLOSE SO_LOCK_RECORDS;
1439
1440 DELETE FROM so_price_adjustments
1441 WHERE header_id = p_header_id;
1442
1443 RETURN SQLCODE;
1444
1445 EXCEPTION -- Exception handler to record error
1446 WHEN OTHERS THEN
1447 return_status := SQLCODE;
1448 ROLLBACK;
1449 so_record_errors( return_status,
1450 p_request_id,
1451 p_header_id,
1452 'OEPUR: SO_PRICE_ADJUSTMENTS',
1453 NULL );
1454 CLOSE SO_LOCK_RECORDS;
1455 RETURN return_status;
1456
1457 END so_purge_price_adjustments;
1458
1459
1460 FUNCTION so_purge_sales_credits
1461 ( p_header_id NUMBER,
1462 p_request_id NUMBER ) RETURN NUMBER IS
1463
1464 return_status NUMBER := 0;
1465 syntax_required NUMBER := 0;
1466
1467 CURSOR SO_LOCK_RECORDS IS
1468 SELECT header_id -- Lock rows to be purged
1469 FROM so_sales_credits
1470 WHERE header_id = p_header_id
1471 FOR UPDATE NOWAIT;
1472 BEGIN
1473 OPEN SO_LOCK_RECORDS;
1474 CLOSE SO_LOCK_RECORDS;
1475
1476 DELETE FROM so_sales_credits
1477 WHERE header_id = p_header_id;
1478
1479 RETURN SQLCODE;
1480
1481 EXCEPTION -- Exception handler to record error
1482 WHEN OTHERS THEN
1483 return_status := SQLCODE;
1484 ROLLBACK;
1485 so_record_errors( return_status,
1486 p_request_id,
1487 p_header_id,
1488 'OEPUR: SO_SALES_CREDITS',
1489 NULL );
1490 CLOSE SO_LOCK_RECORDS;
1491 RETURN return_status;
1492
1493 END so_purge_sales_credits;
1494
1495 PROCEDURE so_record_errors
1496 ( p_return_status IN NUMBER,
1497 p_request_id IN NUMBER,
1498 p_id_number IN NUMBER,
1499 p_context IN VARCHAR2,
1500 p_error_message IN VARCHAR2 ) IS
1501
1502 return_status NUMBER := 0; -- success/failue from called routine
1503 v_exception_id NUMBER := 0; -- next exception_id
1504 v_application_id NUMBER := 0; -- fnd_application id for OE
1505 v_message_text VARCHAR2(512); -- SQL error text corresponding to
1506 -- return_status from called routine
1507 v_uid NUMBER := 0; -- User identification
1508 v_program_id NUMBER;
1509
1510 BEGIN
1511 IF p_return_status = 0 THEN
1512 v_message_text := p_error_message;
1513 ELSE
1514 v_message_text := SQLERRM( p_return_status );
1515 END IF;
1516
1517 SELECT so_exceptions_s.NEXTVAL
1518 INTO v_exception_id
1519 FROM DUAL;
1520
1521 SELECT UID
1522 INTO v_uid
1523 FROM dual;
1524
1525 SELECT application_id
1526 INTO v_application_id
1527 FROM fnd_application
1528 WHERE application_short_name = 'OE';
1529
1530
1531 SELECT concurrent_program_id
1532 INTO v_program_id
1533 FROM fnd_concurrent_programs
1534 WHERE application_id=v_application_id
1535 AND concurrent_program_name='OEXPURGE';
1536
1537 INSERT INTO so_exceptions -- record an error
1538 VALUES ( v_exception_id, -- sequence number
1539 SYSDATE, -- creation date
1540 v_uid, -- created by
1541 SYSDATE, -- last updated date
1542 v_uid, -- last updated by
1543 v_uid, -- last update login
1544 p_request_id, -- request id
1545 v_application_id, -- program application id
1546 v_program_id, -- program_id
1547 SYSDATE, -- last date changed by current pgm
1548 p_context, -- object on which exception occurred
1549 p_id_number, -- id number,
1550 v_message_text ); -- SQLERRM
1551
1552 IF p_return_status <> 0 -- If writting a SQL error
1553 THEN -- then commit the record
1554 COMMIT; -- else (assume) it is commited by the caller
1555 END IF;
1556
1557 END so_record_errors;
1558
1559 END OEXPURGE; /* end of OEXPURGE package */
[Home] [Help]