[Home] [Help]
PACKAGE BODY: APPS.INV_3PL_LOC_PVT
Source
1 PACKAGE BODY INV_3PL_LOC_PVT
2 /* $Header: INVVSSCB.pls 120.1 2010/05/25 11:15:54 damahaja noship $ */
3 AS
4 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5
6
7 PROCEDURE update_locator_capacity
8 ( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
9 x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
10 x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
11 p_inventory_location_id IN NUMBER, -- identifier of locator
12 p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
13 p_client_code IN VARCHAR2, -- identifier of item
14 p_transaction_action_id IN NUMBER, -- transaction action id for pack,unpack,issue,receive,transfer
15 p_quantity IN NUMBER,
16 p_transaction_date IN DATE
17 )
18 IS
19 l_return_status VARCHAR2(1);
20 l_msg_count NUMBER;
21 l_msg_data VARCHAR2(1000);
22 l_old_record mtl_3pl_locator_occupancy%ROWTYPE;
23 l_Last_Receipt_Date DATE;
24 l_current_onhand NUMBER;
25 l_locator_id NUMBER;
26 l_transaction_date DATE;
27 l_transaction_action NUMBER;
28 l_transaction_quantity NUMBER;
29 -- l_-- last_invoiced_date DATE;
30 l_number_of_days NUMBER :=1;
31 l_success VARCHAR2(1);
32 l_client_code VARCHAR2(10);
33 l_organization_id NUMBER;
34
35 BEGIN
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37
38 IF (g_debug = 1) THEN
39 inv_trx_util_pub.TRACE('In update locator capcity ', 'update_current_capacity', 4);
40 inv_trx_util_pub.TRACE('Locator ID' || p_inventory_location_id , 'update_current_capacity',4);
41 inv_trx_util_pub.TRACE('Client Code ' || p_client_code, 'update_current_capacity', 4);
42 END IF;
43
44 BEGIN
45 Select *
46 INTO l_old_Record
47 FROM mtl_3pl_locator_occupancy
48 WHERE locator_id = p_inventory_location_id
49 and organization_id = p_organization_id
50 AND client_code = p_client_code;
51
52 IF (g_debug = 1) THEN
53 inv_trx_util_pub.TRACE('Locator Record Exists in Occupancy table', 'update_current_capacity', 4);
54 END IF;
55
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 IF (g_debug = 1) THEN
59 inv_trx_util_pub.TRACE('Locator Record Does not exist , creating a new record in Occupancy table', 'update_current_capacity', 4);
60 END IF;
61
62 inv_trx_util_pub.TRACE('p_transaction_date => ' ||p_transaction_date, 'update_current_capacity', 4);
63 inv_trx_util_pub.TRACE('p_quantity => ' || p_quantity, 'update_current_capacity', 4);
64 inv_trx_util_pub.TRACE('p_inventory_location_id => ' ||p_inventory_location_id, 'update_current_capacity', 4);
65 inv_trx_util_pub.TRACE('p_transaction_date => ' ||p_transaction_date, 'update_current_capacity', 4);
66 inv_trx_util_pub.TRACE('p_transaction_action_id => ' ||p_transaction_action_id, 'update_current_capacity', 4);
67 inv_trx_util_pub.TRACE('p_organization_id => ' ||p_organization_id, 'update_current_capacity', 4);
68 inv_trx_util_pub.TRACE('l_client_code => ' ||p_client_code, 'update_current_capacity', 4);
69
70 l_success := insert_3pl_loc_occupancy(
71 p_transaction_date ,
72 p_quantity ,
73 p_inventory_location_id ,
74 p_transaction_date ,
75 p_transaction_action_id ,
76 p_quantity ,
77 p_organization_id ,
78 p_client_code,
79 1
80 );
81
82 IF ( l_success <> fnd_api.g_ret_sts_success )
83 THEN
84 x_msg_data := SQLERRM;
85 END IF;
86 x_return_status := l_success;
87 RETURN;
88 END;
89
90 IF (g_debug = 1) THEN
91 inv_trx_util_pub.TRACE('Updating The current record ', 'update_current_capacity', 4);
92 inv_trx_util_pub.TRACE('Transaction action ID' || p_transaction_action_id, 'update_current_capacity', 4);
93 end if;
94
95 IF ( p_quantity > 0 and l_old_Record.locator_id is not null ) --AND NOT(l_old_Record.Current_Onhand + p_quantity < 0) )
96 THEN
97 IF (g_debug = 1) THEN
98 inv_trx_util_pub.TRACE('Just before adding days .... :', 'update_current_capacity', 4);
99 inv_trx_util_pub.TRACE('Transaction_date :' || p_transaction_date , 'update_current_capacity', 4);
100 inv_trx_util_pub.TRACE('l_old_Record.Last_Receipt_Date :' || l_old_Record.Last_Receipt_Date , 'update_current_capacity', 4);
101 inv_trx_util_pub.TRACE('l_number_of_days :' || l_number_of_days , 'update_current_capacity', 4);
102 end if;
103
104 IF ( to_date(l_old_Record.last_invoiced_date,'dd/mm/yyyy') <> to_date(p_transaction_date,'dd/mm/yyyy') ) -- Here the transaction date is same as the creation date
105 THEN
106 IF l_old_Record.Current_Onhand = 0 Then
107 l_number_of_days := l_number_of_days + 1;
108
109 ELSIF ( p_transaction_date >= l_old_Record.Last_Receipt_Date AND l_old_Record.Current_Onhand > 0 )
110 THEN
111 IF (g_debug = 1) THEN
112 inv_trx_util_pub.TRACE('Inside 2nd if' || l_number_of_days , 'update_current_capacity', 4);
113 end if;
114 l_number_of_days := l_old_Record.number_of_days + to_number(to_date(p_transaction_date,'dd/mm/yyyy') - to_date(l_old_Record.Last_Receipt_Date,'dd/mm/yyyy'));
115 END IF;
116 ELSE
117 l_number_of_days := l_old_record.number_of_days;
118 END IF;
119
120 IF (g_debug = 1) THEN
121 inv_trx_util_pub.TRACE('New number of days' || l_number_of_days , 'update_current_capacity', 4);
122 END IF;
123
124 l_Last_Receipt_Date := p_transaction_date ;
125 l_current_onhand := NVL(l_old_record.current_onhand,0) + p_quantity;
126 l_locator_id := p_inventory_location_id;
127 l_transaction_date := p_transaction_date;
128 l_transaction_action := p_transaction_action_id;
129 l_transaction_quantity := p_quantity;
130 l_organization_id := p_organization_id;
131 l_client_code := p_client_code;
132 -- l_-- last_invoiced_date := NULL;
133
134 IF (g_debug = 1) THEN
135 inv_trx_util_pub.TRACE(' Calling update_3pl_loc_occupancy ', 'update_current_capacity', 4);
136 END IF;
137
138 l_success := update_3pl_loc_occupancy(
139 l_Last_Receipt_Date ,
140 l_current_onhand ,
141 l_locator_id ,
142 l_transaction_date ,
143 l_transaction_action ,
144 l_transaction_quantity ,
145 l_organization_id,
146 l_client_code ,
147 l_number_of_days
148 );
149
150 ELSIF ( p_quantity < 0 and l_old_Record.locator_id is not null )
151 THEN
152 IF (g_debug = 1) THEN
153 inv_trx_util_pub.TRACE('For issue transactions', 'update_current_capacity', 4);
154 inv_trx_util_pub.TRACE('Just before adding days .... :', 'update_current_capacity', 4);
155 inv_trx_util_pub.TRACE('p_transaction_date :' || p_transaction_date , 'update_current_capacity', 4);
156 inv_trx_util_pub.TRACE('l_old_Record.Last_Receipt_Date :' || l_old_Record.Last_Receipt_Date , 'update_current_capacity', 4);
157 inv_trx_util_pub.TRACE('l_number_of_days :' || l_number_of_days , 'update_current_capacity', 4);
158 inv_trx_util_pub.TRACE('p_quantity :' || p_quantity , 'update_current_capacity', 4);
159 END IF;
160
161 IF ( to_date(l_old_Record.last_invoiced_date,'dd/mm/yyyy') <> to_date(p_transaction_date,'dd/mm/yyyy') ) -- Here the transaction date is same as the creation date
162 THEN
163 IF ( l_old_Record.Current_Onhand + p_quantity = 0 ) Then
164 l_number_of_days := NVL(l_old_record.number_of_days,0) + to_number(p_transaction_date - l_old_Record.last_receipt_date );--MAX(l_old_Record.last_receipt_date,l_old_record.Last_invoice_date) );
165 ELSE
166 l_number_of_days := NVL(l_old_record.number_of_days,0);
167 END IF;
168 ELSE
169 l_number_of_days := NVL(l_old_record.number_of_days,0);
170 END IF;
171
172 l_Last_Receipt_Date := l_old_record.Last_Receipt_Date ;
173 l_current_onhand := NVL(l_old_record.current_onhand,0) + p_quantity;
174 l_locator_id := p_inventory_location_id;
175 l_transaction_date := p_transaction_date;
176 l_transaction_action := p_transaction_action_id;
177 l_transaction_quantity := p_quantity;
178 l_organization_id := p_organization_id;
179 l_client_code := p_client_code;
180
181 IF (g_debug = 1) THEN
182 inv_trx_util_pub.TRACE('Before calling update_3pl_loc_occupancy ', 'update_current_capacity', 4);
183 END IF;
184
185 l_success := update_3pl_loc_occupancy(
186 l_Last_Receipt_Date ,
187 l_current_onhand ,
188 l_locator_id ,
189 l_transaction_date ,
190 l_transaction_action ,
191 l_transaction_quantity ,
192 l_organization_id,
193 l_client_code,
194 l_number_of_days
195 );
196
197 IF ( l_success <> fnd_api.g_ret_sts_success )
198 THEN
199 x_msg_data := SQLERRM;
200 END IF;
201 x_return_status := l_success;
202 RETURN;
203 END IF;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 IF (g_debug = 1) THEN
208 inv_trx_util_pub.TRACE(' Exception in update_locator_capacity => '||sqlerrm, 'update_current_capacity', 4);
209 END IF;
210 x_return_status := fnd_api.g_ret_sts_unexp_error;
211 END update_locator_capacity;
212
213 Function update_3pl_loc_occupancy (
214 l_Last_Receipt_Date date,
215 l_current_onhand NUMBER ,
216 l_locator_id NUMBER ,
217 l_transaction_date DATE ,
218 l_transaction_action NUMBER ,
219 l_transaction_quantity NUMBER ,
220 l_organization_id NUMBER,
221 l_client_code VARCHAR2 ,
222 l_number_of_days number
223 ) RETURN VARCHAR2
224 IS
225
226 cursor c is select client_code , locator_id , organization_id
227 FROM mtl_3pl_locator_occupancy
228 WHERE client_code = l_client_code
229 AND locator_id = l_locator_id
230 and organization_id = l_organization_id
231 FOR UPDATE nowait;
232
233 BEGIN
234
235 IF (g_debug = 1) THEN
236 inv_trx_util_pub.TRACE('In update_3pl_loc_occupancy ', 'update_current_capacity', 4);
237 END IF;
238
239 FOR recinfo IN c
240 LOOP
241 IF (g_debug = 1) THEN
242 inv_trx_util_pub.TRACE('recinfo.locator_id ' || recinfo.locator_id , 'update_current_capacity', 4);
243 inv_trx_util_pub.TRACE('recinfo.client_code ' || recinfo.client_code , 'update_current_capacity', 4);
244 END IF;
245
246 Update mtl_3pl_locator_occupancy
247 set Last_Receipt_Date = l_Last_Receipt_Date,
248 current_onhand = l_current_onhand ,
249 transaction_date = l_transaction_date ,
250 transaction_action_id = l_transaction_action ,
251 transaction_quantity = l_transaction_quantity ,
252 number_of_days = l_number_of_days
253 where locator_id = recinfo.locator_id
254 and client_code = recinfo.client_code
255 and organization_id = recinfo.organization_id;
256 END LOOP;
257
258 RETURN FND_API.G_RET_STS_SUCCESS;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 IF (g_debug = 1) THEN
263 inv_trx_util_pub.TRACE(' Exception in update_3pl_loc_occupancy => '||sqlerrm, 'update_current_capacity', 4);
264 END IF;
265
266 RETURN FND_API.G_RET_STS_ERROR;
267 END update_3pl_loc_occupancy;
268
269 Function insert_3pl_loc_occupancy (
270 l_Last_Receipt_Date date,
271 l_current_onhand NUMBER ,
272 l_locator_id NUMBER ,
273 l_transaction_date DATE ,
274 l_transaction_action NUMBER ,
275 l_transaction_quantity NUMBER ,
276 l_organization_id NUMBER,
277 l_client_code VARCHAR2 ,
278 l_number_of_days number
279 ) RETURN VARCHAR2
280 IS
281 BEGIN
282
283 IF (g_debug = 1) THEN
284 inv_trx_util_pub.TRACE('IN insert_3pl_loc_occupancy ','update_current_capacity', 4);
285 inv_trx_util_pub.TRACE('l_locator_id ' || l_locator_id,'update_current_capacity', 4);
286 inv_trx_util_pub.TRACE('IN l_client_code ' || l_client_code ,'update_current_capacity', 4);
287 END IF;
288
289 INSERT INTO mtl_3pl_locator_occupancy
290 ( Last_Receipt_Date,
291 current_onhand ,
292 transaction_date ,
293 transaction_action_id ,
294 transaction_quantity ,
295 last_invoiced_date ,
296 number_of_days ,
297 locator_id,
298 organization_id ,
299 client_code
300 )
301 VALUES
302 ( l_Last_Receipt_Date,
303 l_current_onhand ,
304 l_transaction_date ,
305 l_transaction_action ,
306 l_transaction_quantity ,
307 NULL ,
308 l_number_of_days ,
309 l_locator_id,
310 l_organization_id ,
311 l_client_code
312 );
313
314 RETURN FND_API.G_RET_STS_SUCCESS;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 IF (g_debug = 1) THEN
319 inv_trx_util_pub.TRACE(' Exception in insert_3pl_loc_occupancy => '||sqlerrm, 'update_current_capacity', 4);
320 END IF;
321 RETURN FND_API.G_RET_STS_ERROR;
322 END insert_3pl_loc_occupancy;
323
324 END INV_3PL_LOC_PVT;