1 PACKAGE BODY POS_PO_RCV_QTY as
2 /* $Header: POSRCQTB.pls 120.3.12020000.2 2013/02/09 13:57:23 hvutukur ship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME: get_net_qty
7
8 ===========================================================================*/
9 FUNCTION get_net_qty (x_parent_trx_id IN NUMBER,
10 x_primary_qty IN NUMBER
11 ) RETURN NUMBER is
12
13
14 x_correct_qty number;
15 x_net_qty number;
16
17
18 BEGIN
19
20 SELECT nvl(sum(rt.primary_quantity), 0)
21 INTO x_correct_qty
22 FROM rcv_transactions rt
23 WHERE rt.parent_transaction_id = x_parent_trx_id
24 AND rt.transaction_type = 'CORRECT';
25
26 x_net_qty := x_primary_qty - x_correct_qty;
27
28 return (x_net_qty);
29
30 END get_net_qty;
31
32 FUNCTION get_receive_rcv( x_shipment_line_id in number) return number
33 is
34 l_quantity number;
35 begin
36 select sum(quantity) into l_quantity from rcv_transactions where
37 shipment_line_id = x_shipment_line_id
38 and transaction_type = 'RECEIVE';
39
40 return l_quantity;
41
42 end get_receive_rcv;
43
44 /* Bug 1086123
45 * We used to call the function get_net_quantity to get
46 * the quantity received in which we the primary_quantity from
47 * rcv_transactions for the Correct transaction type
48 * and x_net_qty := x_net_qty - x_correct_qty;
49 * This is wrong since it should ve x_net_qty + x_correct_qty.
50 * Also now we have to consider RETURN transactions.
51 * So created this new function get_net_qty_rcv which will
52 * be called from the view POS_RCV_TRANSACTIONS_V.
53 */
54 /* Bug 9648379
55 modified the function to pass the transaction id of the received shipment transaction
56 which can be used to get correct net received quantity for a particular distribution
57 */
58 FUNCTION get_net_qty_rcv (x_shipment_line_id IN NUMBER,
59 x_primary_qty IN NUMBER,
60 x_transaction_id IN NUMBER
61 ) RETURN NUMBER
62 IS
63
64 cursor c1(x_shipment_line_id NUMBER, x_parent_id NUMBER) IS
65 SELECT rt2.quantity, rt2.transaction_type, rt.transaction_type
66 FROM rcv_transactions rt2, rcv_transactions rt
67 WHERE rt2.shipment_line_id = x_shipment_line_id
68 AND
69 (
70 (rt2.parent_transaction_id = x_parent_id AND rt2.transaction_type IN ('CORRECT', 'RETURN TO VENDOR') AND rt.transaction_id = x_parent_id) OR
71 (rt.transaction_id=rt2.parent_transaction_id and rt.transaction_type = 'RETURN TO VENDOR' )
72 );
73
74 x_correct_qty number;
75 x_net_qty number;
76 x_trans_type varchar2(25);
77 x_parent_type varchar2(25);
78 x_parent_id NUMBER;
79
80 BEGIN
81 x_net_qty := x_primary_qty;
82 x_parent_id := x_transaction_id;
83
84 open c1(x_shipment_line_id, x_parent_id);
85 loop
86 fetch c1 into x_correct_qty, x_trans_type, x_parent_type;
87 exit when c1%NOTFOUND;
88
89 if (x_trans_type = 'CORRECT' and x_parent_type='RECEIVE') then
90 x_net_qty := x_net_qty + x_correct_qty;
91 elsif(x_trans_type = 'CORRECT' AND x_parent_type='RETURN TO VENDOR') then
92 x_net_qty := x_net_qty - x_correct_qty;
93 end if;
94 if (x_trans_type = 'RETURN TO VENDOR') then
95 x_net_qty := x_net_qty - x_correct_qty;
96 end if;
97 end loop;
98 close c1;
99 return (x_net_qty);
100
101 END get_net_qty_rcv;
102
103 /* Bug 9648379
104 added this function to pass the transaction id of the received shipment transaction
105 which can be used to get correct net received amount for a particular distribution
106 */
107 FUNCTION get_net_rcv_amt(x_shipment_line_id IN NUMBER,
108 x_primary_amt IN NUMBER,
109 x_transaction_id IN NUMBER
110 ) RETURN NUMBER
111
112 IS
113
114 cursor c1(x_shipment_line_id NUMBER, x_parent_id NUMBER) is
115 SELECT rt2.amount, rt2.transaction_type, rt.transaction_type
116 FROM rcv_transactions rt2, rcv_transactions rt
117 WHERE rt2.shipment_line_id = x_shipment_line_id
118 AND rt2.parent_transaction_id = x_parent_id
119 AND rt2.transaction_type IN ('CORRECT', 'RETURN TO VENDOR')
120 AND rt.transaction_id = x_parent_id;
121
122 x_correct_amt number;
123 x_net_amt number;
124 x_trans_type varchar2(25);
125 x_parent_type varchar2(25);
126 x_parent_id NUMBER;
127
128 BEGIN
129 x_net_amt := x_primary_amt;
130 x_parent_id := x_transaction_id;
131
132 open c1(x_shipment_line_id, x_parent_id);
133 loop
134 fetch c1 into x_correct_amt, x_trans_type, x_parent_type;
135 exit when c1%NOTFOUND;
136
137 if (x_trans_type = 'CORRECT' and x_parent_type='RECEIVE') then
138 x_net_amt := x_net_amt + x_correct_amt;
139 elsif(x_trans_type = 'CORRECT' AND x_parent_type='RETURN TO VENDOR') then
140 x_net_amt := x_net_amt - x_correct_amt;
141 end if;
142 if (x_trans_type = 'RETURN TO VENDOR') then
143 x_net_amt := x_net_amt - x_correct_amt;
144 end if;
145 end loop;
146 close c1;
147 return (x_net_amt);
148
149 END get_net_rcv_amt;
150
151 FUNCTION get_net_received_qty (p_txn_id IN NUMBER)
152 return number IS
153
154 X_received_quantity NUMBER := 0;
155
156 l_txn_id NUMBER := 0;
157 l_quantity NUMBER := 0;
158 l_transaction_type VARCHAR2(25) := '';
159 l_parent_id NUMBER := 0;
160 l_parent_type VARCHAR2(25) := '';
161
162 CURSOR l_txn_history_csr (c_transaction_id NUMBER) IS
163 SELECT transaction_id,
164 quantity,
165 transaction_type,
166 parent_transaction_id
167 FROM rcv_transactions
168 START WITH transaction_id = c_transaction_id
169 CONNECT BY parent_transaction_id = PRIOR transaction_id;
170
171 BEGIN
172
173 OPEN l_txn_history_csr(p_txn_id);
174 LOOP
175 FETCH l_txn_history_csr INTO l_txn_id,
176 l_quantity,
177 l_transaction_type,
178 l_parent_id;
179 EXIT WHEN l_txn_history_csr%NOTFOUND;
180
181 IF l_transaction_type = 'RECEIVE' THEN
182 X_received_quantity := X_received_quantity + l_quantity;
183
184 ELSIF l_transaction_type = 'RETURN TO VENDOR' THEN
185 X_received_quantity := X_received_quantity - l_quantity;
186
187 ELSIF l_transaction_type = 'CORRECT' THEN
188 /* The correction function is based on parent transaction type */
189
190 SELECT transaction_type
191 INTO l_parent_type
192 FROM rcv_transactions
193 WHERE transaction_id = l_parent_id;
194
195 IF l_parent_type = 'RECEIVE' THEN
196 X_received_quantity := X_received_quantity + l_quantity;
197
198 ELSIF l_parent_type = 'RETURN TO VENDOR' THEN
199 X_received_quantity := X_received_quantity - l_quantity;
200
201 END IF;
202
203 END IF;
204
205 END LOOP;
206
207 CLOSE l_txn_history_csr;
208
209 return X_received_quantity;
210
211 EXCEPTION
212 WHEN others THEN
213 IF l_txn_history_csr%isopen THEN
214 close l_txn_history_csr;
215 end if;
216 raise;
217 end get_net_received_qty;
218
219
220 /* Code changes for bug - 9502912 - Start
221 Added this funtion to get the qty_received from po_line_locations_all table
222 if the value in po_line_locations_archive_all is zero.
223 */
224
225 FUNCTION Get_qty_rcv (line_loc_id IN NUMBER)
226 RETURN NUMBER
227 IS
228 qty_rec NUMBER;
229 BEGIN
230 SELECT quantity_received
231 INTO qty_rec
232 FROM po_line_locations_all
233 WHERE line_location_id = line_loc_id;
234
235 RETURN qty_rec;
236 END get_qty_rcv;
237
238
239 end pos_po_rcv_qty;
[Home] [Help]