PACKAGE BODY: APPS.RCV_RMA_RCPT_PURGE

Source

1 PACKAGE BODY RCV_RMA_RCPT_PURGE AS
2 /* $Header: RCVPURGB.pls 120.3 2006/05/22 04:39:15 atiwari noship $*/
3 /*============================== RCV_RMA_RCPT_PURGE =========================*/
4 
5 PROCEDURE Check_Open_Receipts(x_order_line_id  IN  NUMBER,
6                               x_status   OUT NOCOPY  VARCHAR2,
7                               x_message  OUT NOCOPY  VARCHAR2) IS
8 
9 x_progress       varchar2(3) := '000';
10 x_interface_ct   number;
11 
12 BEGIN
13   x_progress := '010';
14 
15 --Bugfix5216533: Rewritten the query for performance issues.
16   select count(*) into x_interface_ct
17   from rcv_transactions_interface
18   where oe_order_line_id = x_order_line_id
19   and processing_status_code in ('PENDING','ERROR');
20 
21   x_progress := '020';
22 
23   if x_interface_ct > 0  then
24    x_status  := 'FALSE';
25    x_message := fnd_message.get_string('PO','RCV_RMA_RCPT_IN_INTERFACE');
26   end if;
27 
28   x_progress := '030';
29   x_status := 'TRUE';
30 
31 EXCEPTION
32 
33   when others then
34     po_message_s.sql_error('Check_Open_Receipts', x_progress, sqlcode);
35     x_message := fnd_message.get;
36     x_status := 'FALSE';
37 
38 END Check_Open_Receipts;
39 
40 /*============================================================================*/
41 
42 PROCEDURE Purge_Receipts (x_order_line_id  IN  NUMBER,
43                           x_status   OUT NOCOPY  VARCHAR2,
44                           x_message  OUT NOCOPY  VARCHAR2) IS
45 
46 x_progress        varchar2(3) := '000';
47 x_ship_line_id    number;
48 x_ship_header_id  number;
49 x_line_count      number;
50 
51 CURSOR c1(x_order_line_id  number) IS
52 select shipment_line_id,shipment_header_id
53 from rcv_shipment_lines
54 where oe_order_line_id = x_order_line_id;
55 
56 BEGIN
57   x_progress := '010';
58 
59  SAVEPOINT purge_receipts_savepoint;
60 
61  OPEN c1(x_order_line_id);
62 
63  LOOP
64   FETCH c1 into x_ship_line_id,
65                 x_ship_header_id ;
66   EXIT WHEN c1%NOTFOUND;
67 
68   /* get a list of transaction_id for this x_ship_line_id */
69 
70    x_progress := '010';
71   /* delete from rcv_lots_supply */
72  delete from rcv_lots_supply
73  where shipment_line_id = x_ship_line_id;
74 
75    x_progress := '020';
76  /* delete from rcv_serials_supply */
77  delete from rcv_serials_supply
78  where shipment_line_id = x_ship_line_id;
79 
80    x_progress := '030';
81  /* delete from rcv_lot_transactions */
82  delete from rcv_lot_transactions
83  where shipment_line_id = x_ship_line_id;
84 
85    x_progress := '040';
86  /* delete from rcv_lots_supply */
87  delete from rcv_serial_transactions
88  where shipment_line_id = x_ship_line_id;
89 
90    x_progress := '050';
91  /* delete from rcv_shipment_lines */
92  delete from rcv_shipment_lines
93  where shipment_line_id = x_ship_line_id;
94 
95   /* if the shipment header for this line does not have any more  lines
96    * then delete the header .
97    */
98   select count(*) into x_line_count
99   from rcv_shipment_lines
100   where shipment_header_id = x_ship_header_id;
101 
102   if x_line_count = 0 then
103     delete from rcv_shipment_headers
104     where shipment_header_id = x_ship_header_id;
105   end if;
106 
107    x_progress := '060';
108 
109  /* delete from rcv_transactions */
110  delete from rcv_transactions
111  where shipment_line_id = x_ship_line_id;
112 
113  END LOOP;
114  CLOSE c1;
115 
116          x_progress := '070';
117          x_status := 'TRUE';
118 
119 EXCEPTION
120 
121   when others then
122     po_message_s.sql_error('Purge_Receipts', x_progress, sqlcode);
123     x_message := fnd_message.get;
124     x_status := 'FALSE';
125     ROLLBACK TO SAVEPOINT purge_receipts_savepoint;
126     raise;
127 
128 END Purge_Receipts;
129 
130 END RCV_RMA_RCPT_PURGE;