[Home] [Help]
PACKAGE BODY: APPS.INV_3PL_SEEDED_SOURCES
Source
1 PACKAGE BODY INV_3PL_SEEDED_SOURCES AS
2 /* $Header: INVSSRCB.pls 120.1 2010/05/26 09:11:20 damahaja noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_3PL_SEEDED_SOURCES';
5 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6
7
8 PROCEDURE debug(
9 p_message IN VARCHAR2
10 ) IS
11 BEGIN
12 IF (g_debug = 1) THEN
13 inv_log_util.trace(p_message, G_PKG_NAME , 10 );
14 END IF;
15 EXCEPTION
16 WHEN OTHERS THEN
17 NULL;
18 END debug;
19
20
21 FUNCTION get_item_uom_code (p_uom_name VARCHAR2) RETURN VARCHAR2 IS
22 l_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%type := NULL;
23
24 BEGIN
25
26 SELECT uom_code
27 INTO l_uom_code
28 FROM mtl_units_of_measure_vl
29 WHERE unit_of_measure = p_uom_name;
30
31 RETURN (l_uom_code);
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 debug('Error in get_item_uom_code function : '||sqlerrm);
36
37 END get_item_uom_code;
38
39
40 PROCEDURE number_receive_transactions
41 (
42 x_counter_value OUT NOCOPY NUMBER,
43 x_return_status OUT NOCOPY VARCHAR2
44 )
45 as
46
47 CURSOR items_record(p_client_code VARCHAR2) IS
48 SELECT * FROM mtl_system_items_kfv msib
49 WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
50 AND nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
51 AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
52 AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
53
54
55 rt_counter NUMBER := 0;
56 l_operating_unit NUMBER;
57 l_client_code varchar2(10);
58 l_temp NUMBER :=0;
59 l_source_to_date date;
60 l_last_computation_date date;
61 l_service_line_start_date date;
62 l_in_loop NUMBER := 0;
63
64 BEGIN
65
66 x_return_status := fnd_api.g_ret_sts_success;
67 l_client_code := INV_3PL_BILLING_PUB.g_billing_source_rec.client_code; --'Business1';
68 l_operating_unit := INV_3PL_BILLING_PUB.g_billing_source_rec.operating_unit; --'Business1';
69 l_source_to_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
70 l_last_computation_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_computation_Date; --to_date('23-NOV-2009', 'DD-MON-YYYY'); --
71 l_service_line_start_date := INV_3PL_BILLING_PUB.g_billing_source_rec.service_line_start_date;
72
73 if l_last_computation_date is null then -- For 1st run of fresh LSP install
74 l_last_computation_Date := l_service_line_start_date;
75 end if;
76
77 debug('Entered INV_3PL_SEEDED_SOURCES.number_receive_transactions ');
78 debug('Got the values client_code => '|| l_client_code);
79 debug('Got the values l_source_to_date => '|| l_source_to_date);
80 debug('Got the values l_last_computation_date => '|| l_last_computation_date);
81 debug('For client => '||l_client_code);
82
83
84 FOR recs IN items_record(l_client_code)
85 LOOP
86 l_in_loop := 1;
87 debug('For client code => '||l_client_code);
88 debug('recs.item name => '||recs.segment1 ||'.'||recs.segment20);
89 debug('recs.item id =====================================> '||recs.inventory_item_id);
90 debug('recs.l_source_to_date => '|| To_Char(l_source_to_date, 'DD-MON-YYYY HH24:MI:SS'));
91 debug('recs.l_last_computation_date => '|| To_Char(l_last_computation_date, 'DD-MON-YYYY HH24:MI:SS'));
92
93 Select count(1) INTO l_temp
94 from rcv_transactions rt , rcv_shipment_lines rsl
95 WHERE rsl.shipment_line_id = rt.shipment_line_id
96 and rt.shipment_header_id = rt.shipment_header_id
97 AND rsl.item_id = recs.inventory_item_id
98 AND rt.creation_date <= l_source_to_date
99 AND rt.creation_date > l_last_computation_Date
100 AND rt.transaction_type = 'RECEIVE'
101 AND rt.organization_id = recs.organization_id;
102
103 debug('in loop fetched l_temp => '|| l_temp);
104 rt_counter := rt_counter + l_temp;
105 x_counter_value := rt_counter;
106 debug('rt_counter in loop => '||rt_counter);
107 END LOOP;
108
109 IF l_in_loop = 0 THEN
110 FND_MESSAGE.SET_NAME('INV','INV_NO_CLIENT_ITM_OU');
111 x_return_status := fnd_api.g_ret_sts_error;
112 x_counter_value := 0;
113 RAISE fnd_api.g_exc_unexpected_error;
114
115 END IF;
116
117 debug('Effective Counter reading => '||rt_counter);
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 debug('Exception raised in seeded source '||sqlerrm);
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123 x_counter_value := 0;
124
125 END number_receive_transactions;
126
127
128 PROCEDURE number_shipment_lines
129 (
130 x_counter_value OUT NOCOPY NUMBER,
131 x_return_status OUT NOCOPY VARCHAR2
132 )
133 as
134 CURSOR items_record(p_client_code VARCHAR2) IS
135 SELECT * FROM mtl_system_items_b msib
136 WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
137 and nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
138 AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
139 AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
140
141 mmt_counter NUMBER := 0;
142 l_operating_unit NUMBER;
143 l_client_code varchar2(10);
144 l_temp NUMBER :=0;
145 l_source_to_date date;
146 l_last_computation_date date;
147 l_service_line_start_date date;
148 l_in_loop NUMBER := 0;
149
150 BEGIN
151
152 x_return_status := FND_API.G_RET_STS_SUCCESS;
153 l_client_code := INV_3PL_BILLING_PUB.g_billing_source_rec.client_code; --'Business1';
154 l_operating_unit := INV_3PL_BILLING_PUB.g_billing_source_rec.operating_unit; --'Business1';
155 l_source_to_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
156 l_last_computation_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_computation_Date; --to_date('23-NOV-2009', 'DD-MON-YYYY'); --
157 l_service_line_start_date := INV_3PL_BILLING_PUB.g_billing_source_rec.service_line_start_date;
158
159 if l_last_computation_date is null then -- For 1st run of fresh LSP install
160 l_last_computation_Date := l_service_line_start_date;
161 end if;
162
163 debug('Entered INV_3PL_SEEDED_SOURCES.number_shipment_lines ');
164 debug('Got the values client_code => '|| l_client_code);
165 debug('Got the values l_source_to_date => '|| l_source_to_date);
166 debug('Got the values l_last_computation_date => '|| l_last_computation_date);
167 debug('For client => '||l_client_code);
168
169
170 FOR recs IN items_record(l_client_code)
171 LOOP
172 l_in_loop := 1;
173 debug('For client code => '||l_client_code);
174 debug('recs.item name => '||recs.segment1 ||'.'||recs.segment20);
175 debug('recs.item id =====================================> '||recs.inventory_item_id);
176 debug('recs.l_source_to_date => '|| To_Char(l_source_to_date, 'DD-MON-YYYY HH24:MI:SS'));
177 debug('recs.l_last_computation_date => '|| To_Char(l_last_computation_date, 'DD-MON-YYYY HH24:MI:SS'));
178
179 Select count(*) INTO l_temp
180 from mtl_material_transactions mmt
181 where transaction_source_type_id = 2
182 AND transaction_type_id = 33
183 AND transaction_action_id= 1
184 AND inventory_item_id = recs.inventory_item_id
185 AND creation_date <= l_source_to_date
186 AND creation_date > l_last_computation_date
187 AND organization_id = recs.organization_id;
188
189 -- debug('in loop fetched l_temp => '|| l_temp);
190 mmt_counter := mmt_counter + l_temp;
191 x_counter_value := mmt_counter;
192 -- debug('rt_counter in loop => '||mmt_counter);
193 END LOOP;
194
195 IF l_in_loop = 0 THEN
196 FND_MESSAGE.SET_NAME('INV','INV_NO_CLIENT_ITM_OU');
197 x_return_status := fnd_api.g_ret_sts_error;
198 RAISE fnd_api.g_exc_unexpected_error;
199 x_counter_value := 0;
200 END IF;
201
202 debug('Effective Counter reading => '||mmt_counter);
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 debug('Exception raised in seeded source '||sqlerrm);
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 x_counter_value := 0;
209 END number_shipment_lines;
210
211
212
213 PROCEDURE number_picking_transactions
214 (
215 x_counter_value OUT NOCOPY NUMBER,
216 x_return_status OUT NOCOPY VARCHAR2
217 )
218 as
219
220 CURSOR items_record(p_client_code VARCHAR2) IS
221 SELECT * FROM mtl_system_items_b msib
222 WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
223 and nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
224 AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
225 AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
226
227 mmt_counter NUMBER := 0;
228 l_operating_unit NUMBER;
229 l_client_code varchar2(10);
230 l_temp NUMBER :=0;
231 l_source_to_date date;
232 l_last_computation_date date;
233 l_service_line_start_date date;
234 l_in_loop NUMBER := 0;
235
236 BEGIN
237
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239 l_client_code := INV_3PL_BILLING_PUB.g_billing_source_rec.client_code; --'Business1';
240 l_operating_unit := INV_3PL_BILLING_PUB.g_billing_source_rec.operating_unit; --'Business1';
241 l_source_to_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
242 l_last_computation_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_computation_Date; --to_date('23-NOV-2009', 'DD-MON-YYYY'); --
243 l_service_line_start_date := INV_3PL_BILLING_PUB.g_billing_source_rec.service_line_start_date;
244
245 if l_last_computation_date is null then -- For 1st run of fresh LSP install
246 l_last_computation_Date := l_service_line_start_date;
247 end if;
248
249 debug('Entered INV_3PL_SEEDED_SOURCES.number_picking_transactions1 ');
250 debug('Got the values client_code => '|| l_client_code);
251 debug('Got the values l_source_to_date => '|| l_source_to_date);
252 debug('Got the values l_last_computation_date => '|| l_last_computation_date);
253 debug('For client => '||l_client_code);
254
255 FOR recs IN items_record(l_client_code)
256 LOOP
257 l_in_loop := 1;
258 debug('For client code => '||l_client_code);
259 debug('recs.item name => '||recs.segment1 ||'.'||recs.segment20);
260 debug('recs.item id =====================================> '||recs.inventory_item_id);
261 debug('recs.l_source_to_date => '|| To_Char(l_source_to_date, 'DD-MON-YYYY HH24:MI:SS'));
262 debug('recs.l_last_computation_date => '|| To_Char(l_last_computation_date, 'DD-MON-YYYY HH24:MI:SS'));
263
264 Select count(*) INTO l_temp
265 from mtl_material_transactions mmt
266 where transaction_source_type_id = 2
267 AND transaction_type_id = 52
268 AND transaction_action_id = 28
269 and inventory_item_id = recs.inventory_item_id
270 AND transaction_quantity > 0
271 AND creation_date <= l_source_to_date
272 AND creation_date > l_last_computation_date
273 AND organization_id = recs.organization_id;
274
275 -- debug('in loop fetched l_temp => '|| l_temp);
276 mmt_counter := mmt_counter + l_temp;
277 x_counter_value := mmt_counter;
278 -- debug('rt_counter in loop => '||mmt_counter);
279 END LOOP;
280
281 IF l_in_loop = 0 THEN
282 FND_MESSAGE.SET_NAME('INV','INV_NO_CLIENT_ITM_OU');
283 x_return_status := fnd_api.g_ret_sts_error;
284 RAISE fnd_api.g_exc_unexpected_error;
285 x_counter_value := 0;
286
287 END IF;
288
289 debug('Effective Counter reading => '||mmt_counter);
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 debug('Exception raised in seeded source '||sqlerrm);
294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
295 x_counter_value := 0;
296 END number_picking_transactions;
297
298 -- Bug 9475436 --
299 -- Changed qty_receiving_transactions TO incorporate an error message
300 -- WHEN the UOM conversion IS NOT defined between the two UOMS
301
302
303 PROCEDURE qty_receiving_transactions
304 (
305 x_counter_value OUT NOCOPY NUMBER,
306 x_return_status OUT NOCOPY VARCHAR2
307 )
308
309 as
310
311 CURSOR items_record(p_client_code VARCHAR2) IS
312 SELECT *
313 FROM mtl_system_items_b msib
314 WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
315 AND nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
316 AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
317 AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
318
319 -- Bug 9475436 --
320
321 Cursor rt_record(p_inventory_item_id NUMBER , p_source_to_date DATE,p_last_computation_Date DATE, p_organization_id NUMBER) IS
322 Select rt.quantity quantity, NVL(rt.uom_code, get_item_uom_code(rt.unit_of_measure)) from_uom_code , rsl.item_id
323 from rcv_transactions rt, rcv_shipment_lines rsl
324 WHERE rsl.shipment_line_id = rt.shipment_line_id
325 and rt.shipment_header_id = rt.shipment_header_id
326 AND rsl.item_id = p_inventory_item_id
327 AND rt.creation_date <= p_source_to_date
328 AND rt.creation_date > p_last_computation_Date
329 AND rt.transaction_type = 'RECEIVE'
330 AND rt.organization_id = p_organization_id;
331
332 rt_counter NUMBER :=0;
333 l_quantity NUMBER :=0;
334 l_operating_unit NUMBER;
335 l_client_code varchar2(10);
336 l_temp NUMBER :=0;
337 l_source_to_date date;
338 l_last_computation_date date;
339 l_service_line_start_date date;
340 l_last_invoice_date DATE;
341 l_billing_uom VARCHAR2(5);
342 l_in_loop NUMBER :=0;
343 -- Bug 9475436 --
344 l_temp_quantity NUMBER :=0;
345 l_conversion NUMBER;
346 -- Bug 9475436 --
347
348 BEGIN
349
350 debug('Starting of INV 3PL qty received ');
351
352 x_return_status := fnd_api.g_ret_sts_success;
353 l_client_code := INV_3PL_BILLING_PUB.g_billing_source_rec.client_code;
354 l_source_to_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
355 l_last_computation_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_computation_Date;
356 l_last_invoice_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_invoice_date;
357 l_operating_unit := INV_3PL_BILLING_PUB.g_billing_source_rec.operating_unit;
358 l_billing_uom := INV_3PL_BILLING_PUB.g_billing_source_rec.billing_uom;
359 l_service_line_start_date := INV_3PL_BILLING_PUB.g_billing_source_rec.service_line_start_date;
360
361
362 if l_last_computation_date is null then -- For 1st run of fresh LSP install
363 l_last_computation_Date := l_service_line_start_date;
364 end if;
365
366 debug('Entered INV_3PL_SEEDED_SOURCES.qty_receiving_transactions ');
367 debug('Got the values client_code => '|| l_client_code);
368 debug('Got the values l_source_to_date => '|| l_source_to_date);
369 debug('Got the values l_last_computation_date => '|| l_last_computation_date);
370 debug(' l_billing_uom => '|| l_billing_uom);
371 debug('For client => '||l_client_code);
372
373 FOR recs IN items_record(l_client_code)
374 LOOP
375
376 l_quantity := 0;
377 l_in_loop := 1;
378 debug('For client code => '||l_client_code);
379 debug('Organization_id => '|| recs.organization_id);
380 debug('recs.item name => '||recs.segment1 ||'.'||recs.segment20);
381 debug('recs.item id =====================================> '||recs.inventory_item_id);
382 debug('recs.l_source_to_date => '|| To_Char(l_source_to_date, 'DD-MON-YYYY HH24:MI:SS'));
383 debug('recs.l_last_computation_date => '|| To_Char(l_last_computation_date, 'DD-MON-YYYY HH24:MI:SS'));
384 debug('recs.l_last_invoice_date => '|| To_Char(l_last_invoice_date, 'DD-MON-YYYY HH24:MI:SS'));
385
386 BEGIN
387
388 -- Bug 9475436 --
389 FOR items IN rt_record ( recs.inventory_item_id , l_source_to_date , l_last_computation_Date , recs.organization_id)
390 LOOP
391
392 SELECT inv_convert.inv_um_convert(items.item_id,items.from_uom_code,l_billing_uom)
393 INTO l_conversion
394 FROM dual;
395
396 debug('Conversion rate between the UOMs '|| l_conversion);
397 IF(l_conversion <= -999)
398 THEN
399 fnd_message.set_name('INV', 'INV_INVALID_UOM_CONV');
400 fnd_message.set_token('VALUE1', items.from_uom_code);
401 fnd_message.set_token('VALUE2', l_billing_uom);
402 debug('No conversion rate defined between the two UOM'|| l_conversion);
403 x_return_status := fnd_api.g_ret_sts_error;
404 x_counter_value := 0;
405 RAISE fnd_api.G_EXC_ERROR;
406 END IF;
407 l_temp_quantity := items.quantity * l_conversion;
408 l_quantity := l_quantity + l_temp_quantity;
409 END LOOP;
410
411 END;
412 debug('in loop fetched l_quantity => '|| nvl(l_quantity, 0));
413 rt_counter := nvl(rt_counter, 0) + nvl(l_quantity, 0);
414 debug('cummulative rt_counter in loop => '|| rt_counter);
415 END LOOP;
416
417 x_counter_value := rt_counter;
418 debug('final x_counter_value => '|| rt_counter);
419
420 IF l_in_loop = 0 THEN
421 FND_MESSAGE.SET_NAME('INV','INV_NO_CLIENT_ITM_OU');
422 x_return_status := fnd_api.g_ret_sts_error;
423 RAISE fnd_api.g_exc_unexpected_error;
424 x_counter_value := 0;
425
426 END IF;
427
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 x_return_status := fnd_api.g_ret_sts_success;
431 x_counter_value := 0;
432 WHEN OTHERS THEN
433 debug('Exception raised in seeded source '||sqlerrm);
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 x_counter_value := 0;
436 END qty_receiving_transactions;
437
438 PROCEDURE number_putaway_transactions
439 (
440 x_counter_value OUT NOCOPY NUMBER,
441 x_return_status OUT NOCOPY VARCHAR2
442 )
443 as
444
445 CURSOR items_record(p_client_code VARCHAR2) IS
446 SELECT * FROM mtl_system_items_b msib
447 WHERE wms_deploy.get_client_code(msib.inventory_item_id) = p_client_code
448 and nvl(msib.USAGE_ITEM_FLAG, 'N') = 'N'
449 AND nvl(msib.SERVICE_ITEM_FLAG, 'N') = 'N'
450 AND nvl(msib.VENDOR_WARRANTY_FLAG, 'N') = 'N';
451
452
453 rt_counter NUMBER := 0;
454 l_operating_unit NUMBER;
455 l_client_code varchar2(10);
456 l_temp NUMBER :=0;
457 l_source_to_date date;
458 l_last_computation_date date;
459 l_service_line_start_date date;
460 l_in_loop NUMBER := 0;
461
462 BEGIN
463
464 x_return_status := FND_API.G_RET_STS_SUCCESS;
465 l_client_code := INV_3PL_BILLING_PUB.g_billing_source_rec.client_code; --'Business1';
466 l_operating_unit := INV_3PL_BILLING_PUB.g_billing_source_rec.operating_unit; --'Business1';
467 l_source_to_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
468 l_last_computation_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_computation_Date; --to_date('23-NOV-2009', 'DD-MON-YYYY'); --
469 l_service_line_start_date := INV_3PL_BILLING_PUB.g_billing_source_rec.service_line_start_date;
470
471 if l_last_computation_date is null then -- For 1st run of fresh LSP install
472 l_last_computation_Date := l_service_line_start_date;
473 end if;
474
475 debug('Entered INV_3PL_SEEDED_SOURCES.number_putaway_transactions ');
476 debug('Got the values client_code => '|| l_client_code);
477 debug('Got the values l_source_to_date => '|| l_source_to_date);
478 debug('Got the values l_last_computation_date => '|| l_last_computation_date);
479
480 FOR recs IN items_record(l_client_code)
481 LOOP
482 l_in_loop := 1;
483 debug('For client code => '||l_client_code);
484 debug('recs.item name => '||recs.segment1 ||'.'||recs.segment20);
485 debug('recs.item id =====================================> '||recs.inventory_item_id);
486 debug('recs.l_source_to_date => '|| To_Char(l_source_to_date, 'DD-MON-YYYY HH24:MI:SS'));
487 debug('recs.l_last_computation_date => '|| To_Char(l_last_computation_date, 'DD-MON-YYYY HH24:MI:SS'));
488
489 Select count(*) INTO l_temp
490 from rcv_transactions rt, rcv_shipment_lines rsl
491 WHERE rsl.shipment_line_id = rt.shipment_line_id
492 and rt.shipment_header_id = rt.shipment_header_id
493 AND rsl.item_id = recs.inventory_item_id
494 AND rt.creation_date <= l_source_to_date
495 AND rt.creation_date > l_last_computation_Date
496 AND rt.transaction_type = 'DELIVER'
497 AND rt.organization_id = recs.organization_id;
498
499
500 debug('in loop fetched l_temp => '|| l_temp);
501 rt_counter := rt_counter + l_temp;
502 x_counter_value := rt_counter;
503 debug('rt_counter in loop => '|| rt_counter);
504 END LOOP;
505
506
507 IF l_in_loop = 0 THEN
508 FND_MESSAGE.SET_NAME('INV','INV_NO_CLIENT_ITM_OU');
509 x_return_status := fnd_api.g_ret_sts_error;
510 RAISE fnd_api.g_exc_unexpected_error;
511 x_counter_value := 0;
512
513 END IF;
514
515
516 debug('Effective Counter reading => '||rt_counter);
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 debug('Exception raised in seeded source '||sqlerrm);
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522 x_counter_value := 0;
523
524 END number_putaway_transactions;
525
526
527 PROCEDURE capacity_number_of_days
528 (
529 x_counter_value OUT NOCOPY NUMBER,
530 x_return_status OUT NOCOPY VARCHAR2
531 ) as
532
533 cursor cur_days_locator_occupied(p_client_code VARCHAR2, p_operating_unit_id NUMBER, p_source_to_date DATE)
534 is
535 SELECT Trunc(greatest(last_receipt_date, Nvl(last_invoiced_date, last_receipt_date))) greater_date
536 , locator_id, client_code, organization_id, Trunc(transaction_date) txn_date, transaction_action_id ,
537 transaction_quantity , current_onhand, Trunc(last_receipt_date) last_rcpt_date,
538 number_of_days, Trunc(last_invoiced_date) last_inv_date
539 from mtl_3pl_locator_occupancy inv
540 WHERE client_code = p_client_code
541 and organization_id in (select organization_id from org_organization_definitions
542 where operating_unit = p_operating_unit_id)
543 and (( nvl(last_invoiced_date, p_source_to_date) <= p_source_to_date )
544 AND ( transaction_date <= p_source_to_date ) )
545 order by organization_id, locator_id;
546
547 l_cumm_reading_old NUMBER := 0;
548 l_cumm_reading_new NUMBER := 0;
549 l_count NUMBER :=0;
550 l_operating_unit NUMBER;
551 l_client_code varchar2(10);
552 l_source_to_date date;
553 l_invoice_date date;
554 l_last_computation_date date;
555 l_last_invoice_date DATE;
556 l_new_number_of_days NUMBER :=0;
557 l_lock_record VARCHAR2(1);
558 l_progid NUMBER;
559 l_reqstid NUMBER;
560 l_applid NUMBER;
561
562 BEGIN
563
564 x_return_status := fnd_api.g_ret_sts_success;
565 l_client_code := INV_3PL_BILLING_PUB.g_billing_source_rec.client_code; --'Business1';
566 l_invoice_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
567 l_source_to_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
568 l_last_computation_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_computation_Date;
569 l_operating_unit := INV_3PL_BILLING_PUB.g_billing_source_rec.operating_unit;
570
571 debug('Entered INV_3PL_SEEDED_SOURCES.capacity_number_of_days ');
572 debug('Got the values client_code => '|| l_client_code);
573 debug('Got the values l_source_to_date => '|| l_source_to_date);
574 debug('Got the values l_last_computation_date => '|| l_last_computation_date);
575
576 savepoint process_locator;
577 FOR locator_occupancy_rec in cur_days_locator_occupied (l_client_code, l_operating_unit, l_invoice_date)
578
579 LOOP
580 debug(' ----------------------------------------------------------------------');
581 debug(' Processing for Client_code, Locator_id -> '||locator_occupancy_rec.client_code ||' , '||locator_occupancy_rec.locator_id );
582 debug(' ----------------------------------------------------------------------');
583
584 l_cumm_reading_old := nvl(l_cumm_reading_new, 0);
585
586 IF NOT (locator_occupancy_rec.current_onhand = 0 AND locator_occupancy_rec.number_of_days = 0) THEN
587
588 debug(' : current_onhand, number_of_days -> '|| locator_occupancy_rec.current_onhand ||' , '|| locator_occupancy_rec.number_of_days );
589
590 IF (locator_occupancy_rec.current_onhand = 0 ) THEN
591 debug(' : cumm reading from prev. locator => ' ||l_cumm_reading_old);
592 l_cumm_reading_new := l_cumm_reading_old + locator_occupancy_rec.number_of_days;
593 -- reset number_of_days counter to 0
594 l_new_number_of_days := 0;
595 debug(' : l_new_number_of_days -> '|| l_new_number_of_days);
596
597 ELSIF locator_occupancy_rec.current_onhand > 0 THEN
598
599 debug(' : src to date, greater_date -> '|| l_source_to_date ||', '|| locator_occupancy_rec.greater_date );
600 debug(' : cumm reading from prev. locator => ' ||l_cumm_reading_old);
601
602 l_cumm_reading_new := nvl(l_cumm_reading_old, 0) + locator_occupancy_rec.number_of_days +
603 abs(trunc(l_source_to_date) - locator_occupancy_rec.greater_date);
604 debug(' : Cummulative Number of days -> '||l_cumm_reading_new );
605 -- reset number_of_days counter to 0
606 l_new_number_of_days := 0;
607 debug(' : l_new_number_of_days -> '|| l_new_number_of_days);
608
609 END IF; /* (locator_occupancy_rec.current_onhand = 0 ) */
610
611
612 l_progid := FND_PROFILE.value('CONC_PROGRAM_ID');
613 l_reqstid := FND_PROFILE.value('CONC_REQUEST_ID');
614 l_applid := FND_PROFILE.value('PROG_APPL_ID');
615
616 BEGIN
617 SELECT 'Y'
618 INTO l_lock_record
619 FROM mtl_3pl_locator_occupancy
620 WHERE locator_id = locator_occupancy_rec.locator_id
621 AND organization_id = locator_occupancy_rec.organization_id
622 AND client_code = locator_occupancy_rec.client_code
623 FOR UPDATE NOWAIT;
624
625 UPDATE mtl_3pl_locator_occupancy
626 SET number_of_days = l_new_number_of_days,
627 last_invoiced_date = l_invoice_date,
628 request_id= l_reqstid,
629 program_application_id = l_reqstid,
630 program_id = l_progid,
631 program_update_date = SYSDATE
632 WHERE locator_id = locator_occupancy_rec.locator_id
633 AND organization_id = locator_occupancy_rec.organization_id
634 AND client_code = locator_occupancy_rec.client_code;
635
636
637 debug(' : Reset Number of days counter, rows updated -> '||sql%rowcount);
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 debug(' : Error While resetting Number of days in mtl_3pl_locator_occupancy ' ||sqlerrm);
642 IF SQLCODE = -54 THEN
643 debug(' : Could not lock the record in mtl_3pl_locator_occupancy ');
644 FND_MESSAGE.SET_NAME('INV','INV_TRX_ROW_LOCKED');
645 x_return_status := fnd_api.g_ret_sts_error;
646 x_counter_value := 0;
647 ELSE
648 x_return_status := fnd_api.g_ret_sts_unexp_error;
649 x_counter_value := 0;
650 -- raise fnd_api.g_exc_unexpected_error;
651 END IF;
652 rollback to process_locator;
653
654 debug(' : Could not get locator occupancy details for following combination ');
655 debug(' ----------------------------------------------------------------------');
656 debug(' : Client_code => '||locator_occupancy_rec.client_code );
657 debug(' : Locator_id => '||locator_occupancy_rec.locator_id );
658 RETURN;
659 END;
660 debug(' : Reading returned from seeded source => '||l_cumm_reading_new);
661 x_counter_value := nvl(l_cumm_reading_new, 0);
662
663 END IF; /* NOT (locator_occupancy_rec.current_onhand = 0 AND locator_occupancy_rec.number_of_days = 0) */
664
665 END LOOP; /* cur_days_locator_occupied */
666
667 END capacity_number_of_days;
668
669
670 PROCEDURE volume_utilized
671 (
672 x_counter_value OUT NOCOPY NUMBER,
673 x_return_status OUT NOCOPY VARCHAR2
674 ) as
675
676 cursor cur_days_locator_occupied(p_client_code VARCHAR2, p_operating_unit_id NUMBER, p_source_to_date DATE)
677 is
678 SELECT Trunc(greatest(last_receipt_date, Nvl(last_invoiced_date, last_receipt_date))) greater_date
679 , locator_id, client_code, organization_id, Trunc(transaction_date) txn_date, transaction_action_id ,
680 transaction_quantity , current_onhand, Trunc(last_receipt_date) last_rcpt_date,
681 number_of_days, Trunc(last_invoiced_date) last_inv_date
682 from mtl_3pl_locator_occupancy inv
683 WHERE client_code = p_client_code
684 and organization_id in (select organization_id from org_organization_definitions
685 where operating_unit = p_operating_unit_id)
686 and (( nvl(last_invoiced_date, p_source_to_date) <= p_source_to_date )
687 AND ( transaction_date <= p_source_to_date ) )
688 order by organization_id, locator_id;
689
690 l_cumm_reading_old NUMBER := 0;
691 l_cumm_reading_new NUMBER := 0;
692 l_count NUMBER :=0;
693 l_operating_unit NUMBER;
694 l_client_code varchar2(10);
695 l_source_to_date date;
696 l_invoice_date date;
697 l_last_computation_date date;
698 l_last_invoice_date DATE;
699 l_new_number_of_days NUMBER :=0;
700 l_lock_record VARCHAR2(1);
701 l_total_loc_volume NUMBER;
702 l_volume_reading_old NUMBER;
703 l_volume_reading_new NUMBER;
704 l_volume_locator NUMBER;
705 l_billing_uom VARCHAR2(10);
706 l_multiply NUMBER :=0;
707 l_progid NUMBER;
708 l_reqstid NUMBER;
709 l_applid NUMBER;
710
711 BEGIN
712
713 debug('Entered INV_3PL_SEEDED_SOURCES.volume_utilized initial');
714
715 x_return_status := fnd_api.g_ret_sts_success;
716 l_client_code := INV_3PL_BILLING_PUB.g_billing_source_rec.client_code; --'Business1';
717 l_invoice_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
718 l_source_to_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
719 l_last_computation_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_computation_Date;
720 l_operating_unit := INV_3PL_BILLING_PUB.g_billing_source_rec.operating_unit;
721 l_billing_uom := INV_3PL_BILLING_PUB.g_billing_source_rec.billing_uom;
722
723 debug('Entered INV_3PL_SEEDED_SOURCES.volume_utilized ');
724 debug('Got the values client_code => '|| l_client_code);
725 debug('Got the values l_source_to_date => '|| l_source_to_date);
726 debug('Got the values l_last_computation_date => '|| l_last_computation_date);
727
728 savepoint process_locator;
729 FOR locator_occupancy_rec in cur_days_locator_occupied (l_client_code, l_operating_unit, l_invoice_date)
730
731 LOOP
732 debug(' ----------------------------------------------------------------------');
733 debug(' Processing for Client_code, Locator_id -> '||locator_occupancy_rec.client_code ||' , '||locator_occupancy_rec.locator_id );
734 debug(' ----------------------------------------------------------------------');
735
736 l_volume_reading_old := nvl(l_volume_reading_new, 0);
737 l_volume_locator := get_volume_for_locator(locator_occupancy_rec.locator_id,locator_occupancy_rec.organization_id,l_billing_uom);
738
739 debug('locator Volume ' || l_volume_locator);
740 IF ( l_volume_locator = -9999)
741 THEN
742 debug(' Could not get locator occupancy details as the UOM conversion is not defined');
743 debug(' ----------------------------------------------------------------------');
744 debug(' Client_code => '||locator_occupancy_rec.client_code );
745 debug(' Locator_id => '||locator_occupancy_rec.locator_id );
746 rollback to process_locator;
747 RAISE fnd_api.G_EXC_ERROR;
748 x_counter_value := 0;
749 x_return_status := fnd_api.g_ret_sts_error;
750 RETURN;
751 END IF;
752
753 IF ( l_volume_locator = -1234)
754 THEN
755 debug(' Could not get locator occupancy details as the volume is zero for the locator');
756 debug(' ----------------------------------------------------------------------');
757 debug(' Client_code => '||locator_occupancy_rec.client_code );
758 debug(' Locator_id => '||locator_occupancy_rec.locator_id );
759 rollback to process_locator;
760
761 FND_MESSAGE.SET_NAME('INV','INV_LOC_VOL_NOT_DEF');
762 RAISE fnd_api.G_EXC_ERROR;
763 x_return_status := fnd_api.g_ret_sts_error;
764 x_counter_value := 0;
765 RETURN;
766 END IF;
767
768 IF NOT (locator_occupancy_rec.current_onhand = 0 AND locator_occupancy_rec.number_of_days = 0) THEN
769 debug(' current_onhand, number_of_days -> '|| locator_occupancy_rec.current_onhand ||' , '|| locator_occupancy_rec.number_of_days );
770
771 IF (locator_occupancy_rec.current_onhand = 0 ) THEN
772
773 debug(' : cumm reading from prev. locator => ' ||l_cumm_reading_old);
774
775 l_total_loc_volume := l_volume_locator * (locator_occupancy_rec.number_of_days );
776 l_volume_reading_new := l_volume_reading_old + l_total_loc_volume;
777 -- reset number_of_days counter to 0
778 l_new_number_of_days := 0;
779 debug(' : l_new_number_of_days -> '|| l_new_number_of_days);
780
781 ELSIF locator_occupancy_rec.current_onhand > 0 THEN
782
783 debug(' : src to date, greater_date -> '|| l_source_to_date ||', '|| locator_occupancy_rec.greater_date );
784 debug(' : cumm reading from prev. locator => ' ||l_cumm_reading_old);
785
786 l_multiply := locator_occupancy_rec.number_of_days +
787 abs(trunc(l_source_to_date) - locator_occupancy_rec.greater_date);
788
789 debug('Number of Days for which the locator was occupied -> '||l_multiply );
790
791 l_total_loc_volume := l_volume_locator * l_multiply;
792 l_volume_reading_new := l_volume_reading_old + l_total_loc_volume;
793
794 debug(' Total Volume Occupied '|| l_total_loc_volume);
795 debug('Cumulative Volume Reading -> '||l_volume_reading_new );
796 -- reset number_of_days counter to 1
797 l_new_number_of_days := 0;
798 debug('l_new_number_of_days -> '|| l_new_number_of_days);
799
800 END IF;
801 /* (locator_occupancy_rec.current_onhand = 0 ) */
802
803
804 l_progid := FND_PROFILE.value('CONC_PROGRAM_ID');
805 l_reqstid := FND_PROFILE.value('CONC_REQUEST_ID');
806 l_applid := FND_PROFILE.value('PROG_APPL_ID');
807
808 BEGIN
809 SELECT 'Y'
810 INTO l_lock_record
811 FROM mtl_3pl_locator_occupancy
812 WHERE locator_id = locator_occupancy_rec.locator_id
813 AND organization_id = locator_occupancy_rec.organization_id
814 AND client_code = locator_occupancy_rec.client_code
815 FOR UPDATE NOWAIT;
816
817 UPDATE mtl_3pl_locator_occupancy
818 SET number_of_days = l_new_number_of_days,
819 last_invoiced_date = l_invoice_date,
820 request_id= l_reqstid,
821 program_application_id = l_reqstid,
822 program_id = l_progid,
823 program_update_date = SYSDATE
824 WHERE locator_id = locator_occupancy_rec.locator_id
825 AND organization_id = locator_occupancy_rec.organization_id
826 AND client_code = locator_occupancy_rec.client_code;
827
828
829 debug(' : Reset Number of days counter, rows updated -> '||sql%rowcount);
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 debug(' : Error While resetting Number of days in mtl_3pl_locator_occupancy ' ||sqlerrm);
834 IF SQLCODE = -54 THEN
835 debug(' : Could not lock the record in mtl_3pl_locator_occupancy ');
836 FND_MESSAGE.SET_NAME('INV','INV_TRX_ROW_LOCKED');
837 x_return_status := fnd_api.g_ret_sts_error;
838 x_counter_value := 0;
839 ELSE
840 x_return_status := fnd_api.g_ret_sts_unexp_error;
841 x_counter_value := 0;
842 -- raise fnd_api.g_exc_unexpected_error;
843 END IF;
844 rollback to process_locator;
845 debug(' : Could not get locator occupancy details for following combination ');
846 debug(' ----------------------------------------------------------------------');
847 debug(' : Client_code => '||locator_occupancy_rec.client_code );
848 debug(' : Locator_id => '||locator_occupancy_rec.locator_id );
849 RETURN;
850 END;
851
852 debug('Cumulative Volume Reading -> '||l_volume_reading_new );
853 debug('Reading returned from seeded source => '||l_volume_reading_new);
854 x_counter_value := nvl(l_volume_reading_new, 0);
855 END IF; /* NOT (locator_occupancy_rec.current_onhand = 0 AND locator_occupancy_rec.number_of_days = 0) */
856
857 END LOOP; /* cur_days_locator_occupied */
858 END volume_utilized;
859
860
861 PROCEDURE area_utilized
862 (
863 x_counter_value OUT NOCOPY NUMBER,
864 x_return_status OUT NOCOPY VARCHAR2
865 ) as
866
867 cursor cur_days_locator_occupied(p_client_code VARCHAR2, p_operating_unit_id NUMBER, p_source_to_date DATE)
868 is
869 SELECT Trunc(greatest(last_receipt_date, Nvl(last_invoiced_date, last_receipt_date))) greater_date
870 , locator_id, client_code, organization_id, Trunc(transaction_date) txn_date, transaction_action_id ,
871 transaction_quantity , current_onhand, Trunc(last_receipt_date) last_rcpt_date,
872 number_of_days, Trunc(last_invoiced_date) last_inv_date
873 from mtl_3pl_locator_occupancy inv
874 WHERE client_code = p_client_code
875 and organization_id in (select organization_id from org_organization_definitions
876 where operating_unit = p_operating_unit_id)
877 and (( nvl(last_invoiced_date, p_source_to_date) <= p_source_to_date )
878 AND ( transaction_date <= p_source_to_date ) )
879 order by organization_id, locator_id;
880
881 l_cumm_reading_old NUMBER := 0;
882 l_cumm_reading_new NUMBER := 0;
883 l_count NUMBER :=0;
884 l_operating_unit NUMBER;
885 l_client_code varchar2(10);
886 l_source_to_date date;
887 l_invoice_date date;
888 l_last_computation_date date;
889 l_last_invoice_date DATE;
890 l_new_number_of_days NUMBER :=0;
891 l_lock_record VARCHAR2(1);
892 l_total_loc_area NUMBER;
893 l_area_reading_old NUMBER;
894 l_area_reading_new NUMBER;
895 l_area_locator NUMBER;
896 l_billing_uom VARCHAR2(10);
897 l_multiply NUMBER :=0;
898 l_progid NUMBER;
899 l_reqstid NUMBER;
900 l_applid NUMBER;
901
902 BEGIN
903
904 debug('Entered INV_3PL_SEEDED_SOURCES.area_utlized initial');
905
906 x_return_status := fnd_api.g_ret_sts_success;
907 l_client_code := INV_3PL_BILLING_PUB.g_billing_source_rec.client_code; --'Business1';
908 l_invoice_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
909 l_source_to_date := INV_3PL_BILLING_PUB.g_billing_source_rec.source_to_date;
910 l_last_computation_date := INV_3PL_BILLING_PUB.g_billing_source_rec.last_computation_Date;
911 l_operating_unit := INV_3PL_BILLING_PUB.g_billing_source_rec.operating_unit;
912 l_billing_uom := INV_3PL_BILLING_PUB.g_billing_source_rec.billing_uom;
913
914 debug('Entered INV_3PL_SEEDED_SOURCES.area_utilized ');
915 debug('Got the values client_code => '|| l_client_code);
916 debug('Got the values l_source_to_date => '|| l_source_to_date);
917 debug('Got the values l_last_computation_date => '|| l_last_computation_date);
918
919 savepoint process_locator;
920 FOR locator_occupancy_rec in cur_days_locator_occupied (l_client_code, l_operating_unit, l_invoice_date)
921
922 LOOP
923 debug(' ----------------------------------------------------------------------');
924 debug(' Processing for Client_code, Locator_id -> '||locator_occupancy_rec.client_code ||' , '||locator_occupancy_rec.locator_id );
925 debug(' ----------------------------------------------------------------------');
926
927 l_area_reading_old := nvl(l_area_reading_new, 0);
928 l_area_locator := get_area_for_locator(locator_occupancy_rec.locator_id,locator_occupancy_rec.organization_id);
929
930 IF ( l_area_locator = -1234)
931 THEN
932 debug(' Could not get locator occupancy details as the area is zero for the locator');
933 debug(' ----------------------------------------------------------------------');
934 debug(' Client_code => '||locator_occupancy_rec.client_code );
935 debug(' Locator_id => '||locator_occupancy_rec.locator_id );
936 rollback to process_locator;
937
938 FND_MESSAGE.SET_NAME('INV','INV_LOC_AREA_NOT_DEF');
939 RAISE fnd_api.G_EXC_ERROR;
940 x_return_status := fnd_api.g_ret_sts_error;
941 x_counter_value := 0;
942 RETURN;
943 END IF;
944
945
946 IF NOT (locator_occupancy_rec.current_onhand = 0 AND locator_occupancy_rec.number_of_days = 0) THEN
947 debug(' current_onhand, number_of_days -> '|| locator_occupancy_rec.current_onhand ||' , '|| locator_occupancy_rec.number_of_days );
948
949 IF (locator_occupancy_rec.current_onhand = 0 ) THEN
950
951 debug(' : cumm reading from prev. locator => ' ||l_cumm_reading_old);
952
953 l_total_loc_area := l_area_locator * (locator_occupancy_rec.number_of_days );
954 l_area_reading_new := l_area_reading_old + l_total_loc_area;
955 -- reset number_of_days counter to 0
956 l_new_number_of_days := 0;
957 debug(' : l_new_number_of_days -> '|| l_new_number_of_days);
958
959 ELSIF locator_occupancy_rec.current_onhand > 0 THEN
960
961 debug(' : src to date, greater_date -> '|| l_source_to_date ||', '|| locator_occupancy_rec.greater_date );
962 debug(' : cumm reading from prev. locator => ' ||l_cumm_reading_old);
963
964 l_multiply := locator_occupancy_rec.number_of_days +
965 abs(trunc(l_source_to_date) - locator_occupancy_rec.greater_date);
966
967 debug('Number of Days for which the locator was occupied -> '||l_multiply );
968
969 l_total_loc_area := l_area_locator * l_multiply;
970 l_area_reading_new := l_area_reading_old + l_total_loc_area;
971
972 debug(' Total Area Occupied '|| l_total_loc_area);
973 debug('Cumulative Area Reading -> '||l_area_reading_new );
974 -- reset number_of_days counter to 0
975 l_new_number_of_days := 0;
976 debug('l_new_number_of_days -> '|| l_new_number_of_days);
977
978 END IF;
979
980 /* (locator_occupancy_rec.current_onhand = 0 ) */
981
982 l_progid := FND_PROFILE.value('CONC_PROGRAM_ID');
983 l_reqstid := FND_PROFILE.value('CONC_REQUEST_ID');
984 l_applid := FND_PROFILE.value('PROG_APPL_ID');
985
986 BEGIN
987 SELECT 'Y'
988 INTO l_lock_record
989 FROM mtl_3pl_locator_occupancy
990 WHERE locator_id = locator_occupancy_rec.locator_id
991 AND organization_id = locator_occupancy_rec.organization_id
992 AND client_code = locator_occupancy_rec.client_code
993 FOR UPDATE NOWAIT;
994
995 UPDATE mtl_3pl_locator_occupancy
996 SET number_of_days = l_new_number_of_days,
997 last_invoiced_date = l_invoice_date,
998 request_id= l_reqstid,
999 program_application_id = l_reqstid,
1000 program_id = l_progid,
1001 program_update_date = SYSDATE
1002 WHERE locator_id = locator_occupancy_rec.locator_id
1003 AND organization_id = locator_occupancy_rec.organization_id
1004 AND client_code = locator_occupancy_rec.client_code;
1005
1006
1007 debug(' : Reset Number of days counter, rows updated -> '||sql%rowcount);
1008
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 debug(' : Error While resetting Number of days in mtl_3pl_locator_occupancy ' ||sqlerrm);
1012 IF SQLCODE = -54 THEN
1013 debug(' : Could not lock the record in mtl_3pl_locator_occupancy ');
1014 FND_MESSAGE.SET_NAME('INV','INV_TRX_ROW_LOCKED');
1015 x_return_status := fnd_api.g_ret_sts_error;
1016 x_counter_value := 0;
1017 ELSE
1018 x_return_status := fnd_api.g_ret_sts_unexp_error;
1019 x_counter_value := 0;
1020 -- raise fnd_api.g_exc_unexpected_error;
1021 END IF;
1022 rollback to process_locator;
1023 debug(' : Could not get locator occupancy details for following combination ');
1024 debug(' ----------------------------------------------------------------------');
1025 debug(' : Client_code => '||locator_occupancy_rec.client_code );
1026 debug(' : Locator_id => '||locator_occupancy_rec.locator_id );
1027 RETURN;
1028 END;
1029
1030 debug('Cumulative Volume Reading -> '||l_area_reading_new );
1031 debug('Reading returned from seeded source => '||l_area_reading_new);
1032 x_counter_value := nvl(l_area_reading_new, 0);
1033 END IF; /* NOT (locator_occupancy_rec.current_onhand = 0 AND locator_occupancy_rec.number_of_days = 0) */
1034
1035 END LOOP; /* cur_days_locator_occupied */
1036 END area_utilized;
1037
1038
1039 FUNCTION get_volume_for_locator(p_inventory_location_id NUMBER , p_organization_id NUMBER , p_billing_uom VARCHAR2)
1040 RETURN Number IS
1041
1042 x_return_status VARCHAR2(1); -- return status (success/error/unexpected_error)
1043 x_msg_count NUMBER; -- number of messages in the message queue
1044 x_msg_data VARCHAR2(100); -- message text when x_msg_count>0
1045 x_volume_uom_code VARCHAR2(10); -- the locator's unit of measure for volume
1046 x_max_cubic_area NUMBER; -- max volume the locator can take
1047 x_current_cubic_area NUMBER; -- current volume in the locator
1048 x_suggested_cubic_area NUMBER; -- suggested volume to be put into locator
1049 x_available_cubic_area NUMBER; -- volume the locator can still take
1050 l_quantity NUMBER;
1051 l_conversion NUMBER;
1052
1053 BEGIN
1054
1055 INV_LOC_WMS_UTILS.get_locator_volume_capacity
1056 ( x_return_status => x_return_status,
1057 x_msg_count => x_msg_count,
1058 x_msg_data => x_msg_data,
1059 x_volume_uom_code => x_volume_uom_code,
1060 x_max_cubic_area => x_max_cubic_area,
1061 x_current_cubic_area => x_current_cubic_area,
1062 x_suggested_cubic_area => x_suggested_cubic_area,
1063 x_available_cubic_area => x_available_cubic_area,
1064 p_organization_id => p_organization_id,
1065 p_inventory_location_id => p_inventory_location_id
1066 );
1067
1068 debug('Max Volume of Locator '|| x_max_cubic_area);
1069 debug('Volume UOM Code : '|| x_volume_uom_code);
1070 debug('Billing UOM : '|| p_billing_uom);
1071
1072 IF( Nvl(x_max_cubic_area,0) = 0)
1073 THEN
1074 RETURN(-1234);
1075 END IF;
1076
1077 SELECT inv_convert.inv_um_convert(0,x_volume_uom_code,p_billing_uom)
1078 INTO l_conversion
1079 FROM dual;
1080
1081 debug('Conversion rate between the UOMs '|| l_conversion);
1082
1083 IF(l_conversion <= -999)
1084 THEN
1085 fnd_message.set_name('INV', 'INV_INVALID_UOM_CONV');
1086 fnd_message.set_token('VALUE1', x_volume_uom_code);
1087 fnd_message.set_token('VALUE2', p_billing_uom);
1088 RETURN(-9999);
1089 ELSE
1090 l_quantity := l_conversion * x_max_cubic_area;
1091 RETURN(l_quantity);
1092 END IF;
1093
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 debug('Error in get_volume_for_locator function : '||sqlerrm);
1097 rollback to process_locator;
1098
1099
1100 END get_volume_for_locator;
1101
1102 FUNCTION get_area_for_locator(p_inventory_location_id NUMBER , p_organization_id NUMBER )
1103 RETURN Number IS
1104
1105 x_max_area NUMBER; -- max volume the locator can take
1106 x_width NUMBER;
1107 x_length NUMBER;
1108
1109 BEGIN
1110 Select width , length
1111 into x_width , x_length
1112 from mtl_item_locations
1113 where inventory_location_id = p_inventory_location_id
1114 and organization_id = p_organization_id;
1115
1116 x_max_area := Nvl(x_width,0) * Nvl ( x_length,0);
1117
1118 debug('Max area of Locator '|| x_max_area);
1119
1120 IF( Nvl(x_max_area,0) = 0)
1121 THEN
1122 RETURN(-1234);
1123 END IF;
1124
1125 RETURN(x_max_area);
1126
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 debug('Error in get_area_for_locator function : '||sqlerrm);
1130 rollback to process_locator;
1131
1132 END get_area_for_locator;
1133
1134 END INV_3PL_SEEDED_SOURCES;