1 PACKAGE BODY OE_Blkt_Release_Util AS
2 /* $Header: OEXUBRLB.pls 120.6 2008/05/09 06:27:33 smanian ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Blkt_Release_Util';
7
8 ---------------------------------------------------------------------------
9 -- LOCAL FUNCTION Get_Line_Number
10 -- Used to set line_number token in error messages
11 ---------------------------------------------------------------------------
12 FUNCTION Get_Line_Number
13 (p_line_id IN NUMBER
14 ) RETURN NUMBER
15 IS
16 l_line_number NUMBER;
17 BEGIN
18
19 SELECT LINE_NUMBER
20 INTO l_line_number
21 FROM OE_ORDER_LINES
22 WHERE LINE_ID = p_line_id;
23
24 RETURN l_line_number;
25
26 EXCEPTION
27 WHEN OTHERS THEN
28 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
29 THEN
30 OE_MSG_PUB.Add_Exc_Msg
31 (G_PKG_NAME
32 ,'Get_Line_Number'
33 );
34 END IF;
35 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
36 END Get_Line_Number;
37
38 ---------------------------------------------------------------------------
39 -- LOCAL FUNCTION Get_Shipment_Number
40 -- Used to set shipment_number token in error messages
41 ---------------------------------------------------------------------------
42 FUNCTION Get_Shipment_Number
43 (p_line_id IN NUMBER
44 ) RETURN NUMBER
45 IS
46 l_shipment_number NUMBER;
47 BEGIN
48
49 SELECT SHIPMENT_NUMBER
50 INTO l_shipment_number
51 FROM OE_ORDER_LINES
52 WHERE LINE_ID = p_line_id;
53
54 RETURN l_shipment_number;
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
59 THEN
60 OE_MSG_PUB.Add_Exc_Msg
61 (G_PKG_NAME
62 ,'Get_Shipment_Number'
63 );
64 END IF;
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END Get_Shipment_Number;
67
68 ---------------------------------------------------------------------------
69 -- PUBLIC FUNCTION Convert_Amount
70 -- Also called from OEXVFULB.pls
71 -- Converts amounts from one currency to another
72 -- If there is no direct conversion rate available between the FROM and
73 -- TO currencies, currency triangulation approach is used =>
74 -- First, converts from FROM currency to Set of Books (SOB) currency
75 -- Next, converts above converted amount from SOB currency to TO currency
76 ---------------------------------------------------------------------------
77 FUNCTION Convert_Amount
78 (p_from_currency IN VARCHAR2
79 ,p_to_currency IN VARCHAR2
80 ,p_conversion_date IN DATE
81 ,p_conversion_type IN VARCHAR2
82 ,p_amount IN NUMBER
83 ) RETURN NUMBER
84 IS
85 l_amount NUMBER;
86 No_Conversion_Rate EXCEPTION;
87 --
88 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
89 --
90 BEGIN
91
92 BEGIN
93 if l_debug_level > 0 then
94 OE_DEBUG_PUB.ADD('Convert from '||p_from_currency||
95 ' to '||p_to_currency||' Amount :'||p_amount);
96 end if;
97 l_amount := gl_currency_api.convert_closest_amount_sql
98 (x_from_currency => p_from_currency
99 ,x_to_currency => p_to_currency
100 ,x_conversion_date => p_conversion_date
101 ,x_conversion_type => p_conversion_type
102 ,x_user_rate => NULL
103 ,x_amount => p_amount
104 ,x_max_roll_days => -1
105 );
106 if l_debug_level > 0 then
107 OE_DEBUG_PUB.ADD('Converted amount 1 :'||l_amount);
108 end if;
109
110 IF l_amount < 0 THEN
111 RAISE No_Conversion_Rate;
112 END IF;
113
114 EXCEPTION
115 WHEN No_Conversion_Rate THEN
116 DECLARE
117 l_sob_rec OE_Order_Cache.Set_Of_Books_Rec_Type;
118 BEGIN
119
120 l_sob_rec := OE_Order_Cache.Load_Set_Of_Books;
121
122 if l_debug_level > 0 then
123 OE_DEBUG_PUB.ADD('SOB Currency :'||l_sob_rec.currency_code);
124 end if;
125
126 IF l_sob_rec.currency_code <> p_to_currency THEN
127
128 l_amount := gl_currency_api.convert_closest_amount_sql
129 (x_from_currency => p_from_currency
130 ,x_to_currency => l_sob_rec.currency_code
131 ,x_conversion_date => p_conversion_date
132 ,x_conversion_type => p_conversion_type
133 ,x_user_rate => NULL
134 ,x_amount => p_amount
135 ,x_max_roll_days => -1
136 );
137 if l_debug_level > 0 then
138 OE_DEBUG_PUB.ADD('Converted amount 2 :'||l_amount);
139 end if;
140
141 IF l_amount < 0 THEN
142 RAISE No_Conversion_Rate;
143 END IF;
144
145 l_amount := gl_currency_api.convert_closest_amount_sql
146 (x_from_currency => l_sob_rec.currency_code
147 ,x_to_currency => p_to_currency
148 ,x_conversion_date => p_conversion_date
149 ,x_conversion_type => p_conversion_type
150 ,x_user_rate => NULL
151 ,x_amount => l_amount
152 ,x_max_roll_days => -1
153 );
154 if l_debug_level > 0 then
155 OE_DEBUG_PUB.ADD('Converted amount 3 :'||l_amount);
156 end if;
157
158 IF l_amount < 0 THEN
159 RAISE No_Conversion_Rate;
160 END IF;
161
162 ELSE
163
164 RAISE No_Conversion_Rate;
165
166 END IF; -- Convert to SOB currency, if <> blanket currency
167
168 END;
169 END;
170
171 RETURN l_amount;
172
173 EXCEPTION
174 WHEN No_Conversion_Rate THEN
175 if l_debug_level > 0 then
176 oe_debug_pub.add('no rate found in either blanket or sob currency');
177 end if;
178 FND_MESSAGE.SET_NAME('ONT','OE_BL_MISSING_CONV_RATE');
179 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',p_to_currency);
180 OE_MSG_PUB.ADD;
181 RAISE FND_API.G_EXC_ERROR;
182 WHEN OTHERS THEN
183 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
184 THEN
185 OE_MSG_PUB.Add_Exc_Msg
186 (G_PKG_NAME
187 ,'Convert_Amount'
188 );
189 END IF;
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END Convert_Amount;
192
193 ---------------------------------------------------------------------------
194 -- LOCAL PROCEDURE Cache_Blanket
195 ---------------------------------------------------------------------------
196 PROCEDURE Cache_Blanket
197 (p_blanket_number IN NUMBER
198 ,p_blanket_line_number IN NUMBER
199 ,p_lock IN VARCHAR2 DEFAULT 'Y'
200 ,x_blanket_line_id IN OUT NOCOPY NUMBER
201 ,x_blanket_header_id IN OUT NOCOPY NUMBER
202 )
203 IS
204
205 CURSOR c_blanket_line IS
206 SELECT L.HEADER_ID
207 ,BL.OVERRIDE_BLANKET_CONTROLS_FLAG
208 ,BL.OVERRIDE_RELEASE_CONTROLS_FLAG
209 ,nvl(BL.RELEASED_AMOUNT,0)
210 ,BL.MIN_RELEASE_AMOUNT
211 ,BL.MAX_RELEASE_AMOUNT
212 ,BL.BLANKET_LINE_MAX_AMOUNT
213 ,BL.BLANKET_MAX_QUANTITY
214 ,BL.RELEASED_QUANTITY
215 ,BL.FULFILLED_QUANTITY
216 ,BL.FULFILLED_AMOUNT
217 ,BL.MIN_RELEASE_QUANTITY
218 ,BL.MAX_RELEASE_QUANTITY
219 ,L.ORDER_QUANTITY_UOM
220 ,nvl(BL.RETURNED_QUANTITY,0)
221 ,nvl(BL.RETURNED_AMOUNT,0)
222 ,'N' -- locked_flag
223 FROM OE_BLANKET_LINES L,OE_BLANKET_LINES_EXT BL
224 WHERE L.LINE_ID = x_blanket_line_id
225 AND L.LINE_ID = BL.LINE_ID
226 AND L.SALES_DOCUMENT_TYPE_CODE = 'B';
227
228 CURSOR c_blanket_header IS
229 SELECT BH.OVERRIDE_AMOUNT_FLAG
230 ,nvl(BH.RELEASED_AMOUNT,0)
231 ,nvl(BH.RETURNED_AMOUNT,0)
232 ,nvl(BH.FULFILLED_AMOUNT,0)
233 ,BH.BLANKET_MAX_AMOUNT
234 ,H.TRANSACTIONAL_CURR_CODE
235 ,H.CONVERSION_TYPE_CODE
236 ,'N' -- locked_flag
237 FROM OE_BLANKET_HEADERS H,OE_BLANKET_HEADERS_EXT BH
238 WHERE H.HEADER_ID = x_blanket_header_id
239 AND H.ORDER_NUMBER = BH.ORDER_NUMBER
240 AND H.SALES_DOCUMENT_TYPE_CODE = 'B';
241 --
242 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
243 --
244 BEGIN
245
246 if l_debug_level > 0 then
247 oe_debug_pub.add('Enter cache blanket');
248 oe_debug_pub.add('lock :'||p_lock);
249 oe_debug_pub.add('blanket number :'||p_blanket_number);
250 oe_debug_pub.add('blanket number :'||p_blanket_line_number);
251 end if;
252
253 SELECT line_id
254 INTO x_blanket_line_id
255 FROM OE_BLANKET_LINES_EXT BL
256 WHERE BL.ORDER_NUMBER = p_blanket_number
257 AND BL.LINE_NUMBER = p_blanket_line_number;
258
259 if l_debug_level > 0 then
260 oe_debug_pub.add('blanket line id :'||x_blanket_line_id);
261 if g_blkt_line_tbl.exists(x_blanket_line_id) then
262 oe_debug_pub.add('locked flag :'|| g_blkt_line_tbl(x_blanket_line_id).locked_flag);
263 end if;
264 end if;
265
266 --------------------------------------------------------------------
267 -- CACHE BLANKET LINE
268 --------------------------------------------------------------------
269
270 IF ( NOT g_blkt_line_tbl.EXISTS(x_blanket_line_id) )
271 -- re-query if blanket is to be locked and it was not locked before
272 OR (p_lock = 'Y'
273 AND nvl(g_blkt_line_tbl(x_blanket_line_id).locked_flag,'N') = 'N'
274 )
275 THEN
276
277 if l_debug_level > 0 then
278 oe_debug_pub.add('query blanket line');
279 end if;
280
281 OPEN c_blanket_line;
282 FETCH c_blanket_line INTO
283 g_blkt_line_tbl(x_blanket_line_id).header_id
284 ,g_blkt_line_tbl(x_blanket_line_id).override_blanket_controls_flag
285 ,g_blkt_line_tbl(x_blanket_line_id).override_release_controls_flag
286 ,g_blkt_line_tbl(x_blanket_line_id).released_amount
287 ,g_blkt_line_tbl(x_blanket_line_id).min_release_amount
288 ,g_blkt_line_tbl(x_blanket_line_id).max_release_amount
289 ,g_blkt_line_tbl(x_blanket_line_id).blanket_line_max_amount
290 ,g_blkt_line_tbl(x_blanket_line_id).blanket_max_quantity
291 ,g_blkt_line_tbl(x_blanket_line_id).released_quantity
292 ,g_blkt_line_tbl(x_blanket_line_id).fulfilled_quantity
293 ,g_blkt_line_tbl(x_blanket_line_id).fulfilled_amount
294 ,g_blkt_line_tbl(x_blanket_line_id).min_release_quantity
295 ,g_blkt_line_tbl(x_blanket_line_id).max_release_quantity
296 ,g_blkt_line_tbl(x_blanket_line_id).uom
297 ,g_blkt_line_tbl(x_blanket_line_id).returned_quantity
298 ,g_blkt_line_tbl(x_blanket_line_id).returned_amount
299 ,g_blkt_line_tbl(x_blanket_line_id).locked_flag
300 ;
301 CLOSE c_blanket_line;
302
303 END IF;
304
305 IF p_lock = 'Y'
306 AND g_blkt_line_tbl(x_blanket_line_id).locked_flag = 'N'
307 THEN
308 if l_debug_level > 0 then
309 oe_debug_pub.add('lock blanket line');
310 end if;
311 SELECT 'Y'
312 INTO g_blkt_line_tbl(x_blanket_line_id).locked_flag
313 FROM oe_blanket_lines_all
314 WHERE line_id = x_blanket_line_id
315 FOR UPDATE NOWAIT;
316 END IF;
317
318 --------------------------------------------------------------------
319 -- CACHE BLANKET HEADER
320 --------------------------------------------------------------------
321
322 x_blanket_header_id := g_blkt_line_tbl(x_blanket_line_id).header_id;
323
324 IF ( NOT g_blkt_hdr_tbl.EXISTS(x_blanket_header_id) )
325 -- re-query if blanket is to be locked and it was not locked before
326 OR (p_lock = 'Y'
327 AND nvl(g_blkt_hdr_tbl(x_blanket_header_id).locked_flag,'N') = 'N'
328 )
329 THEN
330
331 if l_debug_level > 0 then
332 oe_debug_pub.add('query blanket header');
333 end if;
334
335 OPEN c_blanket_header;
336 FETCH c_blanket_header INTO
337 g_blkt_hdr_tbl(x_blanket_header_id).override_amount_flag
338 ,g_blkt_hdr_tbl(x_blanket_header_id).released_amount
339 ,g_blkt_hdr_tbl(x_blanket_header_id).returned_amount
340 ,g_blkt_hdr_tbl(x_blanket_header_id).fulfilled_amount
341 ,g_blkt_hdr_tbl(x_blanket_header_id).blanket_max_amount
342 ,g_blkt_hdr_tbl(x_blanket_header_id).currency_code
343 ,g_blkt_hdr_tbl(x_blanket_header_id).conversion_type_code
344 ,g_blkt_hdr_tbl(x_blanket_header_id).locked_flag
345 ;
346 CLOSE c_blanket_header;
347
348 END IF;
349
350 IF p_lock = 'Y'
351 AND g_blkt_hdr_tbl(x_blanket_header_id).locked_flag = 'N'
352 THEN
353 if l_debug_level > 0 then
354 oe_debug_pub.add('lock blanket header');
355 end if;
356 SELECT 'Y'
357 INTO g_blkt_hdr_tbl(x_blanket_header_id).locked_flag
358 FROM oe_blanket_headers_all
359 WHERE header_id = x_blanket_header_id
360 FOR UPDATE NOWAIT;
361 END IF;
362
363 if l_debug_level > 0 then
364 oe_debug_pub.add('Exit cache blanket');
365 end if;
366
367 EXCEPTION
368 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
369 FND_MESSAGE.Set_Name('ONT','OE_BL_LOCKED');
370 FND_MESSAGE.Set_Token('BLANKET_NUMBER',p_blanket_number);
371 OE_MSG_PUB.Add;
372 RAISE FND_API.G_EXC_ERROR;
373 WHEN OTHERS THEN
374 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
375 THEN
376 OE_MSG_PUB.Add_Exc_Msg
377 (G_PKG_NAME
378 ,'Cache_Blanket'
379 );
380 END IF;
381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
382 END Cache_Blanket;
383
384
385 ---------------------------------------------------------------------------
386 -- PROCEDURE Update_Released_Qty_Amount
387 ---------------------------------------------------------------------------
388 PROCEDURE Update_Released_Qty_Amount
389 (p_line_id IN NUMBER
390 ,p_line_set_id IN NUMBER
391 ,p_blanket_number IN NUMBER
392 ,p_blanket_line_number IN NUMBER
393 ,p_old_quantity IN NUMBER
394 ,p_quantity IN NUMBER
395 ,p_old_order_qty_uom IN VARCHAR2
396 ,p_order_qty_uom IN VARCHAR2
397 ,p_old_unit_selling_price IN NUMBER
398 ,p_unit_selling_price IN NUMBER
399 ,p_old_inv_item_id IN NUMBER
400 ,p_inv_item_id IN NUMBER
401 ,p_currency_code IN VARCHAR2
402 ,p_fulfilled_flag IN VARCHAR2
403 ,x_return_status OUT NOCOPY VARCHAR2
404 )
405 IS
406
407 l_blanket_line_id NUMBER;
408 l_blanket_header_id NUMBER;
409
410 l_quantity NUMBER := 0;
411 l_old_quantity NUMBER := 0;
412 l_amount NUMBER := 0;
413 l_old_amount NUMBER := 0;
414 l_released_amount NUMBER := 0;
415 l_hdr_released_amount NUMBER := 0;
416 l_released_quantity NUMBER := 0;
417
418 --
419 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
420 --
421 BEGIN
422
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 Cache_Blanket(p_blanket_number
426 ,p_blanket_line_number
427 ,'Y' -- p_lock
428 ,l_blanket_line_id
429 ,l_blanket_header_id
430 );
431
432 --------------------------------------------------------------------
433 -- COMPUTE RELEASE QUANTITY IF Blanket UOM Exists
434 --------------------------------------------------------------------
435
436 if l_debug_level > 0 then
437 oe_debug_pub.add('Blanket UOM:'||g_blkt_line_tbl(l_blanket_line_id).uom);
438 oe_debug_pub.add('INITIAL blkt line released qty: '||
439 g_blkt_line_tbl(l_blanket_line_id).released_quantity);
440 oe_debug_pub.add('INITIAL blkt line released amt: '||
441 g_blkt_line_tbl(l_blanket_line_id).released_amount);
442 oe_debug_pub.add('INITIAL blkt hdr released amt: '||
443 g_blkt_hdr_tbl(l_blanket_header_id).released_amount);
444 oe_debug_pub.add('new qty :'||p_quantity);
445 oe_debug_pub.add('old qty :'||p_old_quantity);
446 oe_debug_pub.add('new SP :'||p_unit_selling_price);
447 oe_debug_pub.add('old SP :'||p_old_unit_selling_price);
448 oe_debug_pub.add('from uom :'||p_order_qty_uom);
449 oe_debug_pub.add('inv item :'||p_inv_item_id);
450 end if;
451
452 IF p_order_qty_uom IS NULL THEN
453 l_quantity := 0;
454 ELSE
455 l_quantity := nvl(p_quantity,0);
456 END IF;
457
458 IF p_old_order_qty_uom IS NULL THEN
459 l_old_quantity := 0;
460 ELSE
461 l_old_quantity := nvl(p_old_quantity,0);
462 END IF;
463
464 IF g_blkt_line_tbl(l_blanket_line_id).uom IS NOT NULL THEN
465
466 IF l_quantity > 0
467 AND p_order_qty_uom <> g_blkt_line_tbl(l_blanket_line_id).uom
468 THEN
469 l_quantity := OE_Order_Misc_Util.Convert_UOM
470 (p_item_id => p_inv_item_id
471 ,p_from_uom_code => p_order_qty_uom
472 ,p_to_uom_code => g_blkt_line_tbl(l_blanket_line_id).uom
473 ,p_from_qty => l_quantity
474 );
475 if l_debug_level > 0 then
476 oe_debug_pub.add('conv new qty :'||l_quantity);
477 end if;
478 IF l_quantity < 0 THEN
479 FND_MESSAGE.SET_NAME('ONT','OE_BL_UOM_CONV_FAILED');
480 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
481 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
482 g_blkt_line_tbl(l_blanket_line_id).uom);
483 OE_MSG_PUB.ADD;
484 RAISE FND_API.G_EXC_ERROR;
485 END IF;
486 END IF;
487
488 IF l_old_quantity > 0
489 AND p_old_order_qty_uom <> g_blkt_line_tbl(l_blanket_line_id).uom
490 THEN
491 l_old_quantity := OE_Order_Misc_Util.Convert_UOM
492 (p_item_id => p_old_inv_item_id
493 ,p_from_uom_code => p_old_order_qty_uom
494 ,p_to_uom_code => g_blkt_line_tbl(l_blanket_line_id).uom
495 ,p_from_qty => l_old_quantity
496 );
497 if l_debug_level > 0 then
498 oe_debug_pub.add('conv old qty :'||l_quantity);
499 end if;
500 IF l_old_quantity < 0 THEN
501 FND_MESSAGE.SET_NAME('ONT','OE_BL_UOM_CONV_FAILED');
502 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
503 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
504 g_blkt_line_tbl(l_blanket_line_id).uom);
505 OE_MSG_PUB.ADD;
506 RAISE FND_API.G_EXC_ERROR;
507 END IF;
508 END IF;
509
510 -- INCREMENT Cumulative Released Quantity
511
512 l_released_quantity := nvl(g_blkt_line_tbl(l_blanket_line_id).released_quantity,0)
513 + (l_quantity - l_old_quantity);
514
518 --------------------------------------------------------------------
515 END IF; -- End if blanket uom is not null
516
517
519 -- COMPUTE RELEASE AMOUNT
520 --------------------------------------------------------------------
521
522 l_amount := nvl(p_quantity,0) * nvl(p_unit_selling_price,0);
523 l_old_amount := nvl(p_old_quantity,0) * nvl(p_old_unit_selling_price,0);
524
525 IF p_currency_code
526 <> g_blkt_hdr_tbl(l_blanket_header_id).currency_code
527 THEN
528
529 IF g_blkt_hdr_tbl(l_blanket_header_id).conversion_type_code IS NULL THEN
530 FND_MESSAGE.SET_NAME('ONT','OE_BL_MISS_CONVERSION_TYPE');
531 FND_MESSAGE.SET_TOKEN('BLANKET_NUMBER',p_blanket_number);
532 oe_msg_pub.add;
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535
536 IF l_amount <> 0 THEN
537 l_amount := Convert_Amount
538 (p_from_currency => p_currency_code
539 ,p_to_currency =>
540 g_blkt_hdr_tbl(l_blanket_header_id).currency_code
541 ,p_conversion_date => sysdate
542 ,p_conversion_type =>
543 g_blkt_hdr_tbl(l_blanket_header_id).conversion_type_code
544 ,p_amount => l_amount
545 );
546 END IF;
547
548 IF l_old_amount <> 0 THEN
549 l_old_amount := Convert_Amount
550 (p_from_currency => p_currency_code
551 ,p_to_currency =>
552 g_blkt_hdr_tbl(l_blanket_header_id).currency_code
553 ,p_conversion_date => sysdate
554 ,p_conversion_type =>
555 g_blkt_hdr_tbl(l_blanket_header_id).conversion_type_code
556 ,p_amount => l_old_amount
557 );
558 END IF;
559
560 END IF;
561
562 -- INCREMENT Released Amounts
563
564 l_released_amount := g_blkt_line_tbl(l_blanket_line_id).released_amount
565 + (l_amount - l_old_amount);
566 l_hdr_released_amount := g_blkt_hdr_tbl(l_blanket_header_id).released_amount
567 + (l_amount - l_old_amount);
568
569 if l_debug_level > 0 then
570 oe_debug_pub.add('curr line amt :'||l_amount);
571 oe_debug_pub.add('bl line released qty :'||l_released_quantity);
572 oe_debug_pub.add('bl line released amt :'||l_released_amount);
573 oe_debug_pub.add('hdr released amt :'||l_hdr_released_amount);
574 end if;
575
576 --------------------------------------------------------------------
577 -- VALIDATE MIN/MAX IF OVERRIDE NOT ALLOWED
578 --------------------------------------------------------------------
579
580 -- (1) Blanket line min/max qty and amount checks
581 -- Check not needed for 0 qtys or full cancellations
582
583 IF l_quantity <> 0 THEN
584
585 -- (1a) Check Individual Release Qty/Amount Validations
586 --ER 6526974
587 --IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_RELEASE_CONTROLS_FLAG = 'N'
588 --THEN
589
590 -- Qty checks only needed if blanket UOM is not null
591 IF g_blkt_line_tbl(l_blanket_line_id).uom IS NOT NULL
592 AND (l_quantity <
593 nvl(g_blkt_line_tbl(l_blanket_line_id).min_release_quantity,0)
594 OR l_quantity >
595 nvl(g_blkt_line_tbl(l_blanket_line_id).max_release_quantity
596 ,l_quantity)
597 )
598 THEN
599 -- If shipment line, only a warning is issued. Error status is not set.
600 IF p_line_set_id IS NOT NULL THEN
601 IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_RELEASE_CONTROLS_FLAG = 'N' THEN
602 FND_MESSAGE.SET_NAME('ONT','OE_BL_MIN_MAX_SHIPMENT_QTY');
603 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
604 FND_MESSAGE.SET_TOKEN('SHIPMENT_NUMBER'
605 ,Get_Shipment_Number(p_line_id));
606 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
607 g_blkt_line_tbl(l_blanket_line_id).uom);
608 FND_MESSAGE.SET_TOKEN('MINIMUM',
609 g_blkt_line_tbl(l_blanket_line_id).min_release_quantity);
610 FND_MESSAGE.SET_TOKEN('MAXIMUM',
611 g_blkt_line_tbl(l_blanket_line_id).max_release_quantity);
612 oe_msg_pub.add;
613 ELSE
614 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
615 FND_MESSAGE.SET_NAME('ONT','OE_BL_MIN_MAX_SHIPMENT_QTY');
616 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
617 FND_MESSAGE.SET_TOKEN('SHIPMENT_NUMBER'
618 ,Get_Shipment_Number(p_line_id));
619 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
620 g_blkt_line_tbl(l_blanket_line_id).uom);
621 FND_MESSAGE.SET_TOKEN('MINIMUM',
622 g_blkt_line_tbl(l_blanket_line_id).min_release_quantity);
623 FND_MESSAGE.SET_TOKEN('MAXIMUM',
624 g_blkt_line_tbl(l_blanket_line_id).max_release_quantity);
625 oe_msg_pub.add;
626 END IF;
627 END IF;
628
629 -- For regular lines, raise error.
630 ELSE
631 IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_RELEASE_CONTROLS_FLAG = 'N' THEN
632 FND_MESSAGE.SET_NAME('ONT','OE_BL_MIN_MAX_REL_QTY');
633 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
634 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
635 g_blkt_line_tbl(l_blanket_line_id).uom);
636 FND_MESSAGE.SET_TOKEN('MINIMUM',
640 oe_msg_pub.add;
637 g_blkt_line_tbl(l_blanket_line_id).min_release_quantity);
638 FND_MESSAGE.SET_TOKEN('MAXIMUM',
639 g_blkt_line_tbl(l_blanket_line_id).max_release_quantity);
641 x_return_status := FND_API.G_RET_STS_ERROR;
642
643 ELSE
644 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
645 FND_MESSAGE.SET_NAME('ONT','OE_BL_MIN_MAX_REL_QTY');
646 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
647 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
648 g_blkt_line_tbl(l_blanket_line_id).uom);
649 FND_MESSAGE.SET_TOKEN('MINIMUM',
650 g_blkt_line_tbl(l_blanket_line_id).min_release_quantity);
651 FND_MESSAGE.SET_TOKEN('MAXIMUM',
652 g_blkt_line_tbl(l_blanket_line_id).max_release_quantity);
653 oe_msg_pub.add;
654 END IF;
655
656 END IF;
657
658
659 END IF;
660 END IF;
661
662 /* Added this new condition, to check the max and min amount only if the values for
663 actual min and max amounts defined as a part of the Sales Agreement only. We need to
664 check the condition if the release amount is greater then or not?
665 To address this condition, introduced a new condition to check if the Actual MAX/MIN amount
666 of a BSA has some value other then Zero. Then only the below condition logic will be
667 allowed to trigger. Added based on the bug #4697134. */
668
669 IF nvl(g_blkt_line_tbl(l_blanket_line_id).min_release_amount,0) > 0 or
670 nvl(g_blkt_line_tbl(l_blanket_line_id).max_release_amount,l_amount) > 0
671 THEN
672
673
674 IF (l_amount <
675 nvl(g_blkt_line_tbl(l_blanket_line_id).min_release_amount,0)
676 OR l_amount >
677 nvl(g_blkt_line_tbl(l_blanket_line_id).max_release_amount,l_amount)
678 )
679 THEN
680 -- If shipment line, only a warning is issued. Error status is not set.
681 IF p_line_set_id IS NOT NULL THEN
682 IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_RELEASE_CONTROLS_FLAG = 'N' THEN
683 FND_MESSAGE.SET_NAME('ONT','OE_BL_MIN_MAX_SHIPMENT_AMT');
684 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
685 FND_MESSAGE.SET_TOKEN('SHIPMENT_NUMBER'
686 ,Get_Shipment_Number(p_line_id));
687 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
688 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
689 FND_MESSAGE.SET_TOKEN('MINIMUM',
690 g_blkt_line_tbl(l_blanket_line_id).min_release_amount);
691 FND_MESSAGE.SET_TOKEN('MAXIMUM',
692 g_blkt_line_tbl(l_blanket_line_id).max_release_amount);
693 oe_msg_pub.add;
694 ELSE
695 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
696 FND_MESSAGE.SET_NAME('ONT','OE_BL_MIN_MAX_SHIPMENT_AMT');
697 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
698 FND_MESSAGE.SET_TOKEN('SHIPMENT_NUMBER'
699 ,Get_Shipment_Number(p_line_id));
700 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
701 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
702 FND_MESSAGE.SET_TOKEN('MINIMUM',
703 g_blkt_line_tbl(l_blanket_line_id).min_release_amount);
704 FND_MESSAGE.SET_TOKEN('MAXIMUM',
705 g_blkt_line_tbl(l_blanket_line_id).max_release_amount);
706 oe_msg_pub.add;
707 END IF;
708
709 END IF;
710
711 -- For regular lines, raise error.
712 ELSE
713 IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_RELEASE_CONTROLS_FLAG = 'N' THEN
714 FND_MESSAGE.SET_NAME('ONT','OE_BL_MIN_MAX_REL_AMT');
715 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
716 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
717 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
718 FND_MESSAGE.SET_TOKEN('MINIMUM',
719 g_blkt_line_tbl(l_blanket_line_id).min_release_amount);
720 FND_MESSAGE.SET_TOKEN('MAXIMUM',
721 g_blkt_line_tbl(l_blanket_line_id).max_release_amount);
722 oe_msg_pub.add;
723 x_return_status := FND_API.G_RET_STS_ERROR;
724
725 ELSE
726 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
727 FND_MESSAGE.SET_NAME('ONT','OE_BL_MIN_MAX_REL_AMT');
728 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Line_Number(p_line_id));
729 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
730 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
731 FND_MESSAGE.SET_TOKEN('MINIMUM',
732 g_blkt_line_tbl(l_blanket_line_id).min_release_amount);
733 FND_MESSAGE.SET_TOKEN('MAXIMUM',
734 g_blkt_line_tbl(l_blanket_line_id).max_release_amount);
735 oe_msg_pub.add;
736 END IF;
737
738 END IF;
739
740 END IF;
741 END IF;
742 END IF;
743
744 --END IF; -- IF blanket line override release control = 'N'
745
746 -- (1b) Check Blanket Qty/Amount (Sum of Released Qty/Amount) Validations
747 -- ER 6526974
748 --IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_BLANKET_CONTROLS_FLAG = 'N'
749 --THEN
750
751 -- Qty checks only needed if blanket UOM is not null
752 IF g_blkt_line_tbl(l_blanket_line_id).uom IS NOT NULL
753 AND l_released_quantity >
757 IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_BLANKET_CONTROLS_FLAG = 'N' THEN
754 (g_blkt_line_tbl(l_blanket_line_id).blanket_max_quantity
755 + g_blkt_line_tbl(l_blanket_line_id).returned_quantity)
756 THEN
758 FND_MESSAGE.SET_NAME('ONT','OE_BL_LIN_MAX_QTY_EXCEEDED');
759 FND_MESSAGE.SET_TOKEN('BLANKET_NUMBER',p_blanket_number);
760 FND_MESSAGE.SET_TOKEN('BLANKET_LINE_NUMBER',p_blanket_line_number);
761 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
762 g_blkt_line_tbl(l_blanket_line_id).uom);
763 FND_MESSAGE.SET_TOKEN('MAXIMUM',
764 g_blkt_line_tbl(l_blanket_line_id).blanket_max_quantity);
765 oe_msg_pub.add;
766 x_return_status := FND_API.G_RET_STS_ERROR;
767 ELSE
768 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
769 FND_MESSAGE.SET_NAME('ONT','OE_BL_LIN_MAX_QTY_EXCEEDED');
770 FND_MESSAGE.SET_TOKEN('BLANKET_NUMBER',p_blanket_number);
771 FND_MESSAGE.SET_TOKEN('BLANKET_LINE_NUMBER',p_blanket_line_number);
772 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
773 g_blkt_line_tbl(l_blanket_line_id).uom);
774 FND_MESSAGE.SET_TOKEN('MAXIMUM',
775 g_blkt_line_tbl(l_blanket_line_id).blanket_max_quantity);
776 oe_msg_pub.add;
777 END IF;
778
779 END IF;
780
781 END IF;
782
783 IF l_released_amount >
784 (g_blkt_line_tbl(l_blanket_line_id).blanket_line_max_amount
785 + g_blkt_line_tbl(l_blanket_line_id).returned_amount)
786 THEN
787 IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_BLANKET_CONTROLS_FLAG = 'N' THEN
788 FND_MESSAGE.SET_NAME('ONT','OE_BL_LIN_MAX_AMT_EXCEEDED');
789 FND_MESSAGE.SET_TOKEN('BLANKET_NUMBER',p_blanket_number);
790 FND_MESSAGE.SET_TOKEN('BLANKET_LINE_NUMBER',p_blanket_line_number);
791 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
792 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
793 FND_MESSAGE.SET_TOKEN('MAXIMUM',
794 g_blkt_line_tbl(l_blanket_line_id).blanket_line_max_amount);
795 oe_msg_pub.add;
796 x_return_status := FND_API.G_RET_STS_ERROR;
797 ELSE
798 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
799 FND_MESSAGE.SET_NAME('ONT','OE_BL_LIN_MAX_AMT_EXCEEDED');
800 FND_MESSAGE.SET_TOKEN('BLANKET_NUMBER',p_blanket_number);
801 FND_MESSAGE.SET_TOKEN('BLANKET_LINE_NUMBER',p_blanket_line_number);
802 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
803 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
804 FND_MESSAGE.SET_TOKEN('MAXIMUM',
805 g_blkt_line_tbl(l_blanket_line_id).blanket_line_max_amount);
806 oe_msg_pub.add;
807 END IF;
808
809 END IF;
810
811 END IF;
812
813 -- END IF; -- IF override blanket control = 'N'
814
815 END IF; -- End of blanket line checks
816
817 -- (2) Blanket header max amount check
818
819 -- ER 6526974
820 --IF g_blkt_hdr_tbl(l_blanket_header_id).override_amount_flag = 'N'
821 -- Check not needed for 0 amount or full cancellations
822 -- AND
823 IF l_amount <> 0
824 THEN
825
826 IF l_hdr_released_amount >
827 (g_blkt_hdr_tbl(l_blanket_header_id).blanket_max_amount
828 + g_blkt_hdr_tbl(l_blanket_header_id).returned_amount)
829 THEN
830 IF g_blkt_hdr_tbl(l_blanket_header_id).override_amount_flag = 'N' THEN
831 FND_MESSAGE.SET_NAME('ONT','OE_BL_HDR_MAX_AMT_EXCEEDED');
832 FND_MESSAGE.SET_TOKEN('BLANKET_NUMBER',p_blanket_number);
833 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
834 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
835 FND_MESSAGE.SET_TOKEN('MAXIMUM',
836 g_blkt_hdr_tbl(l_blanket_header_id).blanket_max_amount);
837 oe_msg_pub.add;
838 x_return_status := FND_API.G_RET_STS_ERROR;
839 ELSE
840 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
841 FND_MESSAGE.SET_NAME('ONT','OE_BL_HDR_MAX_AMT_EXCEEDED');
842 FND_MESSAGE.SET_TOKEN('BLANKET_NUMBER',p_blanket_number);
843 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
844 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
845 FND_MESSAGE.SET_TOKEN('MAXIMUM',
846 g_blkt_hdr_tbl(l_blanket_header_id).blanket_max_amount);
847 oe_msg_pub.add;
848 END IF;
849
850 END IF;
851
852 END IF;
853
854 END IF; -- IF blanket header override amount control = 'N'
855
856 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
857
858 -- Update quantity fields on cached blanket records
859 IF g_blkt_line_tbl(l_blanket_line_id).uom IS NOT NULL THEN
860 g_blkt_line_tbl(l_blanket_line_id).released_quantity
861 := l_released_quantity;
862 IF p_fulfilled_flag = 'Y' THEN
863 g_blkt_line_tbl(l_blanket_line_id).fulfilled_quantity
864 := nvl(g_blkt_line_tbl(l_blanket_line_id).fulfilled_quantity,0)
865 + (l_quantity - l_old_quantity);
866 END IF;
867 END IF;
868
869 -- Update amount fields on cached blanket records
870 g_blkt_line_tbl(l_blanket_line_id).released_amount
871 := l_released_amount;
872 g_blkt_hdr_tbl(l_blanket_header_id).released_amount
873 := l_hdr_released_amount;
874 IF p_fulfilled_flag = 'Y' THEN
875 g_blkt_line_tbl(l_blanket_line_id).fulfilled_amount
879 := g_blkt_hdr_tbl(l_blanket_header_id).fulfilled_amount
876 := g_blkt_line_tbl(l_blanket_line_id).fulfilled_amount
877 + (l_amount - l_old_amount);
878 g_blkt_hdr_tbl(l_blanket_header_id).fulfilled_amount
880 + (l_amount - l_old_amount);
881 END IF;
882
883 END IF;
884
885 if l_debug_level > 0 then
886 oe_debug_pub.add('Final blkt line released qty: '||
887 g_blkt_line_tbl(l_blanket_line_id).released_quantity);
888 oe_debug_pub.add('Final blkt line released amt: '||
889 g_blkt_line_tbl(l_blanket_line_id).released_amount);
890 oe_debug_pub.add('Final blkt hdr released amt: '||
891 g_blkt_hdr_tbl(l_blanket_header_id).released_amount);
892 end if;
893
894 -- End of min-max checks
895
896 EXCEPTION
897 WHEN FND_API.G_EXC_ERROR THEN
898 x_return_status := FND_API.G_RET_STS_ERROR;
899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
901 WHEN OTHERS THEN
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
903 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
904 THEN
905 OE_MSG_PUB.Add_Exc_Msg
906 (G_PKG_NAME
907 ,'Update_Released_Qty_Amount'
908 );
909 END IF;
910 END Update_Released_Qty_Amount;
911
912
913 ---------------------------------------------------------------------------
914 -- PROCEDURE Update_Blankets
915 ---------------------------------------------------------------------------
916 PROCEDURE Update_Blankets
917 (x_return_status OUT NOCOPY VARCHAR2
918 )
919 IS
920 l_index NUMBER;
921 BEGIN
922
923 l_index := g_blkt_hdr_tbl.FIRST;
924 WHILE l_index IS NOT NULL LOOP
925
926 UPDATE OE_BLANKET_HEADERS_EXT
927 SET RELEASED_AMOUNT = g_blkt_hdr_tbl(l_index).released_amount
928 ,FULFILLED_AMOUNT = g_blkt_hdr_tbl(l_index).fulfilled_amount
929 WHERE ORDER_NUMBER IN (SELECT H.ORDER_NUMBER
930 FROM OE_BLANKET_HEADERS H
931 WHERE H.HEADER_ID = l_index);
932
933 UPDATE OE_BLANKET_HEADERS
934 SET LOCK_CONTROL = LOCK_CONTROL + 1
935 WHERE HEADER_ID = l_index;
936
937 l_index := g_blkt_hdr_tbl.NEXT(l_index);
938
939 END LOOP;
940
941 l_index := g_blkt_line_tbl.FIRST;
942 WHILE l_index IS NOT NULL LOOP
943
944 UPDATE OE_BLANKET_LINES_EXT
945 SET RELEASED_AMOUNT = g_blkt_line_tbl(l_index).released_amount
946 ,RELEASED_QUANTITY = g_blkt_line_tbl(l_index).released_quantity
947 ,FULFILLED_AMOUNT = g_blkt_line_tbl(l_index).fulfilled_amount
948 ,FULFILLED_QUANTITY = g_blkt_line_tbl(l_index).fulfilled_quantity
949 WHERE LINE_ID = l_index;
950
951 UPDATE OE_BLANKET_LINES
952 SET LOCK_CONTROL = LOCK_CONTROL + 1
953 WHERE LINE_ID = l_index;
954
955 l_index := g_blkt_line_tbl.NEXT(l_index);
956
957 END LOOP;
958
959 EXCEPTION
960 WHEN OTHERS THEN
961 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
962 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
963 THEN
964 OE_MSG_PUB.Add_Exc_Msg
965 (G_PKG_NAME
966 ,'Update_Blankets'
967 );
968 END IF;
969 END Update_Blankets;
970
971
972 ---------------------------------------------------------------------------
973 -- PROCEDURE Validate_Release_Shipments
974 -- Validates that sum of quantities/amounts across all shipments that
975 -- reference the same blanket are within min/max release qty/amount
976 -- limits on the blanket line.
977 ---------------------------------------------------------------------------
978 PROCEDURE Validate_Release_Shipments
979 (p_line_set_id IN NUMBER
980 ,p_blanket_number IN NUMBER
981 ,p_blanket_line_number IN NUMBER
982 ,p_currency_code IN VARCHAR2
983 ,x_return_status OUT NOCOPY VARCHAR2
984 )
985 IS
986
987 l_blanket_line_id NUMBER;
988 l_blanket_header_id NUMBER;
989
990 l_set_quantity NUMBER := 0;
991 l_set_amount NUMBER := 0;
992 l_uom VARCHAR2(3);
993 l_inv_item_id NUMBER;
994 l_set_line_number NUMBER;
995 --
996 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
997 --
998
999 FUNCTION Get_Set_Line_Number RETURN NUMBER IS
1000 BEGIN
1001
1002 SELECT line_number
1003 INTO l_set_line_number
1004 FROM OE_ORDER_LINES
1005 WHERE LINE_SET_ID = p_line_set_id
1006 AND ROWNUM = 1;
1007
1008 RETURN l_set_line_number;
1009
1010 END Get_Set_Line_Number;
1011
1012
1013 BEGIN
1014
1015 x_return_status := FND_API.G_RET_STS_SUCCESS;
1016
1017 Cache_Blanket(p_blanket_number
1018 ,p_blanket_line_number
1019 ,'Y' -- p_lock
1020 ,l_blanket_line_id
1021 ,l_blanket_header_id
1022 );
1023
1024 --ER 6526974
1028 THEN
1025 -- NO Min/Max Validations need, return
1026 /* IF nvl(g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_RELEASE_CONTROLS_FLAG
1027 ,'Y') = 'Y'
1029 RETURN;
1030 END IF;*/
1031
1032 SELECT ordered_quantity_uom, inventory_item_id
1033 INTO l_uom, l_inv_item_id
1034 FROM OE_SETS
1035 WHERE set_id = p_line_set_id;
1036
1037 SELECT SUM(nvl(ordered_quantity,0))
1038 ,SUM(nvl(ordered_quantity,0) * nvl(unit_selling_price,0))
1039 INTO l_set_quantity
1040 ,l_set_amount
1041 FROM OE_ORDER_LINES
1042 WHERE line_set_id = p_line_set_id
1043 AND blanket_number = p_blanket_number
1044 AND blanket_line_number = p_blanket_line_number;
1045
1046 if l_debug_level > 0 then
1047 oe_debug_pub.add('Set Qty :'||l_set_quantity);
1048 oe_debug_pub.add('Set Amt :'||l_set_amount);
1049 end if;
1050
1051 -- Min/Max Qty Validations only if UOM exists on blanket line
1052 IF g_blkt_line_tbl(l_blanket_line_id).uom IS NOT NULL
1053 AND l_set_quantity > 0
1054 THEN
1055
1056 IF NOT OE_GLOBALS.EQUAL(l_uom,
1057 g_blkt_line_tbl(l_blanket_line_id).uom)
1058 THEN
1059
1060 oe_debug_pub.add('convert uom');
1061 l_set_quantity := OE_Order_Misc_Util.Convert_UOM
1062 (p_item_id => l_inv_item_id
1063 ,p_from_uom_code => l_uom
1064 ,p_to_uom_code => g_blkt_line_tbl(l_blanket_line_id).uom
1065 ,p_from_qty => l_set_quantity
1066 );
1067 IF l_set_quantity < 0 THEN
1068 FND_MESSAGE.SET_NAME('ONT','OE_BL_UOM_CONV_FAILED');
1069 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Set_Line_Number);
1070 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
1071 g_blkt_line_tbl(l_blanket_line_id).uom);
1072 OE_MSG_PUB.ADD;
1073 RAISE FND_API.G_EXC_ERROR;
1074 END IF;
1075
1076 END IF;
1077
1078 IF l_set_quantity <
1079 nvl(g_blkt_line_tbl(l_blanket_line_id).min_release_quantity,0)
1080 OR l_set_quantity >
1081 nvl(g_blkt_line_tbl(l_blanket_line_id).max_release_quantity
1082 ,l_set_quantity)
1083 THEN
1084 IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_RELEASE_CONTROLS_FLAG = 'N' THEN
1085 FND_MESSAGE.SET_NAME('ONT','OE_BL_SUM_SPLIT_MIN_MAX_QTY');
1086 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Set_Line_Number);
1087 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
1088 g_blkt_line_tbl(l_blanket_line_id).uom);
1089 FND_MESSAGE.SET_TOKEN('MINIMUM',
1090 g_blkt_line_tbl(l_blanket_line_id).min_release_quantity);
1091 FND_MESSAGE.SET_TOKEN('MAXIMUM',
1092 g_blkt_line_tbl(l_blanket_line_id).max_release_quantity);
1093 oe_msg_pub.add;
1094 x_return_status := FND_API.G_RET_STS_ERROR;
1095 ELSE
1096 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
1097 FND_MESSAGE.SET_NAME('ONT','OE_BL_SUM_SPLIT_MIN_MAX_QTY');
1098 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Set_Line_Number);
1099 FND_MESSAGE.SET_TOKEN('BLANKET_UOM',
1100 g_blkt_line_tbl(l_blanket_line_id).uom);
1101 FND_MESSAGE.SET_TOKEN('MINIMUM',
1102 g_blkt_line_tbl(l_blanket_line_id).min_release_quantity);
1103 FND_MESSAGE.SET_TOKEN('MAXIMUM',
1104 g_blkt_line_tbl(l_blanket_line_id).max_release_quantity);
1105 oe_msg_pub.add;
1106 END IF;
1107
1108 END IF;
1109
1110 END IF;
1111
1112 END IF;
1113
1114 oe_debug_pub.add('Amt Checks');
1115 -- Min/Max Amount Validations
1116 IF l_set_amount <> 0 THEN
1117
1118 IF p_currency_code
1119 <> g_blkt_hdr_tbl(l_blanket_header_id).currency_code
1120 THEN
1121
1122 IF g_blkt_hdr_tbl(l_blanket_header_id).conversion_type_code IS NULL THEN
1123 FND_MESSAGE.SET_NAME('ONT','OE_BL_MISS_CONVERSION_TYPE');
1124 FND_MESSAGE.SET_TOKEN('BLANKET_NUMBER',p_blanket_number);
1125 oe_msg_pub.add;
1126 RAISE FND_API.G_EXC_ERROR;
1127 END IF;
1128
1129 l_set_amount := Convert_Amount
1130 (p_from_currency => p_currency_code
1131 ,p_to_currency =>
1132 g_blkt_hdr_tbl(l_blanket_header_id).currency_code
1133 ,p_conversion_date => sysdate
1134 ,p_conversion_type =>
1135 g_blkt_hdr_tbl(l_blanket_header_id).conversion_type_code
1136 ,p_amount => l_set_amount
1137 );
1138
1139 END IF;
1140
1141 IF (l_set_amount < nvl(g_blkt_line_tbl(l_blanket_line_id).min_release_amount,0)
1142 OR l_set_amount > nvl(g_blkt_line_tbl(l_blanket_line_id).max_release_amount
1143 ,l_set_amount)
1144 )
1145 THEN
1146 IF g_blkt_line_tbl(l_blanket_line_id).OVERRIDE_RELEASE_CONTROLS_FLAG = 'N' THEN
1147 FND_MESSAGE.SET_NAME('ONT','OE_BL_SUM_SPLIT_MIN_MAX_AMT');
1148 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Set_Line_Number);
1149 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
1150 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
1151 FND_MESSAGE.SET_TOKEN('MINIMUM',
1152 g_blkt_line_tbl(l_blanket_line_id).min_release_amount);
1153 FND_MESSAGE.SET_TOKEN('MAXIMUM',
1154 g_blkt_line_tbl(l_blanket_line_id).max_release_amount);
1155 oe_msg_pub.add;
1159 FND_MESSAGE.SET_NAME('ONT','OE_BL_SUM_SPLIT_MIN_MAX_AMT');
1156 x_return_status := FND_API.G_RET_STS_ERROR;
1157 ELSE
1158 IF NVL(FND_PROFILE.VALUE('ONT_BSA_MIN_MAX_VIOLATION'),'N') = 'Y' THEN
1160 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',Get_Set_Line_Number);
1161 FND_MESSAGE.SET_TOKEN('BLANKET_CURRENCY',
1162 g_blkt_hdr_tbl(l_blanket_header_id).currency_code);
1163 FND_MESSAGE.SET_TOKEN('MINIMUM',
1164 g_blkt_line_tbl(l_blanket_line_id).min_release_amount);
1165 FND_MESSAGE.SET_TOKEN('MAXIMUM',
1166 g_blkt_line_tbl(l_blanket_line_id).max_release_amount);
1167 oe_msg_pub.add;
1168 END IF;
1169 END IF;
1170
1171 END IF;
1172
1173 END IF;
1174
1175
1176 EXCEPTION
1177 WHEN FND_API.G_EXC_ERROR THEN
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1181 WHEN OTHERS THEN
1182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1183 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1184 THEN
1185 OE_MSG_PUB.Add_Exc_Msg
1186 (G_PKG_NAME
1187 ,'Validate_Release_Shipments'
1188 );
1189 END IF;
1190 END Validate_Release_Shipments;
1191
1192 ---------------------------------------------------------------------------
1193 -- PROCEDURE Process_Releases
1194 -- This is the main procedure for release validations against controls
1195 -- defined on the blanket orders.
1196 -- 1. First, requests of the type - VALIDATE_RELEASE_SHIPMENTS - are
1197 -- executed. This would validate that sum of quantities/amounts
1198 -- across shipments are within the min/max release qty/amount.
1199 -- NOTE: If there is an error here, the procedure returns with
1200 -- a status of error. No further validations are executed.
1201 -- 2. Next, requests of type - PROCESS_RELEASE - are executed. This
1202 -- would validate that individual order lines are within the min
1203 -- /max release qty/amount. If line is a shipment, only a warning
1204 -- is issued else an error is raised.
1205 -- Also, cumulative qties/amounts across releases for a blanket
1206 -- are also validated that it is within min/max blanket amounts. If
1207 -- there is a validation failure here, an error is raised.
1208 -- Cumulative qties/amounts are updated on cached blanket records:
1209 -- g_blkt_line_tbl,g_blkt_hdr_tbl.
1210 -- 3. If there is a failure in 2, all requests of type PROCESS_RELEASE
1211 -- are re-set to error. The reason being that the accumulation logic
1212 -- should be re-executed for all lines as it would not be known
1213 -- which line caused it to go over the limits.
1214 -- 4. If 2 is successful (all lines pass all blanket validations),
1215 -- Update Blanket Tables with the new cumulative released qties/
1216 -- amounts.
1217 ---------------------------------------------------------------------------
1218 PROCEDURE Process_Releases
1219 (p_request_tbl IN OUT NOCOPY OE_ORDER_PUB.Request_Tbl_Type
1220 ,x_return_status OUT NOCOPY VARCHAR2
1221 )
1222 IS
1223 I NUMBER;
1224 l_return_status VARCHAR2(3);
1225 l_header_id NUMBER;
1226 l_qty NUMBER;
1227 l_currency_code VARCHAR2(15);
1228 l_hdr_id NUMBER;
1229 --
1230 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1231 --
1232 BEGIN
1233
1234 if l_debug_level > 0 then
1235 oe_debug_pub.add('ENTER OE_Blanket_Util.Process_Releases, Num Requests :'
1236 || p_request_tbl.COUNT);
1237 end if;
1238
1239 g_blkt_line_tbl.delete;
1240 g_blkt_hdr_tbl.delete;
1241
1242 x_return_status := FND_API.G_RET_STS_SUCCESS;
1243
1244 I := p_request_tbl.FIRST;
1245 WHILE I IS NOT NULL LOOP
1246
1247 -- Bug 3007584 - skip requests with null request type
1248 IF p_request_tbl(I).request_type IS NULL
1249 OR p_request_tbl(I).request_type <> 'VALIDATE_RELEASE_SHIPMENTS' THEN
1250 GOTO end_of_loop;
1251 END IF;
1252
1253 if l_debug_level > 0 then
1254 oe_debug_pub.add('VALIDATE_RELEASE_SHIPMENTS, line set id: '
1255 ||p_request_tbl(I).entity_id);
1256 end if;
1257
1258 Validate_Release_Shipments
1259 (p_line_set_id => p_request_tbl(I).entity_id
1260 ,p_blanket_number => p_request_tbl(I).request_unique_key1
1261 ,p_blanket_line_number => p_request_tbl(I).request_unique_key2
1262 ,p_currency_code => p_request_tbl(I).param1
1263 ,x_return_status => l_return_status
1264 );
1265
1266 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1267 oe_debug_pub.add('ret sts of error');
1268 x_return_status := FND_API.G_RET_STS_ERROR;
1269 RETURN;
1270 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1271 oe_debug_pub.add('ret sts of unexp error');
1272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1273 RETURN;
1274 END IF;
1275
1276 p_request_tbl(I).processed := 'Y';
1277
1278 <<end_of_loop>>
1279
1280 I := p_request_tbl.NEXT(I);
1281
1282 END LOOP;
1283
1284 I := p_request_tbl.FIRST;
1285 WHILE I IS NOT NULL LOOP
1286
1290 GOTO end_of_loop;
1287 -- Bug 3007584 - skip requests with null request type
1288 IF p_request_tbl(I).request_type IS NULL
1289 OR p_request_tbl(I).request_type <> 'PROCESS_RELEASE' THEN
1291 END IF;
1292
1293 oe_debug_pub.add('PROCESS_RELEASE, line id: '||p_request_tbl(I).entity_id);
1294
1295 begin
1296 select header_id
1297 into l_hdr_id
1298 from oe_order_lines
1299 where line_id = p_request_tbl(I).entity_id;
1300
1301
1302 --ER6795052
1303 OE_MSG_PUB.Set_Msg_Context(
1304 p_entity_code => 'LINE'
1305 , p_entity_id => p_request_tbl(I).entity_id
1306 , p_header_id => l_hdr_id
1307 , p_line_id => p_request_tbl(I).entity_id );
1308
1309 Exception
1310 when others then
1311 NULL;
1312 End;
1313 -- BUG 2746595, currency code is in request_unique_key1 parameter.
1314 -- This is required as 2 distinct requests need to be logged for
1315 -- currency updates.
1316
1317 l_currency_code := p_request_tbl(I).request_unique_key1;
1318
1319 if l_debug_level > 0 then
1320 oe_debug_pub.add('header currency :'||l_currency_code);
1321 oe_debug_pub.add('old blanket num :'||p_request_tbl(I).param1);
1322 oe_debug_pub.add('new blanket num:'||p_request_tbl(I).param11);
1323 oe_debug_pub.add('old blanket line num:'||p_request_tbl(I).param2);
1324 oe_debug_pub.add('new blanket line num:'||p_request_tbl(I).param12);
1325 end if;
1326
1327 -- Blanket line remained same but some other field affecting
1328 -- qty or price was updated
1329 IF p_request_tbl(I).param1 = p_request_tbl(I).param11
1330 AND p_request_tbl(I).param2 = p_request_tbl(I).param12
1331 THEN
1332
1333 if l_debug_level > 0 then
1334 oe_debug_pub.add('Update qty/amt for existing blanket num :'
1335 ||p_request_tbl(I).param11);
1336 end if;
1337
1338 Update_Released_Qty_Amount
1339 (p_line_id => p_request_tbl(I).entity_id
1340 ,p_blanket_number => p_request_tbl(I).param11
1341 ,p_blanket_line_number => p_request_tbl(I).param12
1342 ,p_old_quantity => p_request_tbl(I).param3
1343 ,p_quantity => p_request_tbl(I).param13
1344 ,p_old_order_qty_uom => p_request_tbl(I).param4
1345 ,p_order_qty_uom => p_request_tbl(I).param14
1346 ,p_old_unit_selling_price => p_request_tbl(I).param5
1347 ,p_unit_selling_price => p_request_tbl(I).param15
1348 ,p_old_inv_item_id => p_request_tbl(I).param6
1349 ,p_inv_item_id => p_request_tbl(I).param16
1350 ,p_currency_code => l_currency_code
1351 ,p_fulfilled_flag => p_request_tbl(I).param8
1352 ,p_line_set_id => p_request_tbl(I).param9
1353 ,x_return_status => l_return_status
1354 );
1355
1356 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1357 oe_debug_pub.add('1. ret sts of error');
1358 x_return_status := FND_API.G_RET_STS_ERROR;
1359 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1360 oe_debug_pub.add('1. ret sts of unexp error');
1361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1362 END IF;
1363
1364 ELSE
1365
1366 -- Increment qty/amount for new blanket line
1367 IF p_request_tbl(I).param11 IS NOT NULL THEN
1368
1369 if l_debug_level > 0 then
1370 oe_debug_pub.add('Increment qty/amt for new blanket num :'
1371 ||p_request_tbl(I).param11);
1372 end if;
1373
1374 Update_Released_Qty_Amount
1375 (p_line_id => p_request_tbl(I).entity_id
1376 ,p_blanket_number => p_request_tbl(I).param11
1377 ,p_blanket_line_number => p_request_tbl(I).param12
1378 ,p_old_quantity => 0
1379 ,p_quantity => p_request_tbl(I).param13
1380 ,p_old_order_qty_uom => null
1381 ,p_order_qty_uom => p_request_tbl(I).param14
1382 ,p_old_unit_selling_price => 0
1383 ,p_unit_selling_price => p_request_tbl(I).param15
1384 ,p_old_inv_item_id => null
1385 ,p_inv_item_id => p_request_tbl(I).param16
1386 ,p_currency_code => l_currency_code
1387 ,p_fulfilled_flag => p_request_tbl(I).param8
1388 ,p_line_set_id => p_request_tbl(I).param9
1389 ,x_return_status => l_return_status
1390 );
1391
1392 END IF;
1393
1394 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1395 oe_debug_pub.add('2. ret sts of error');
1396 x_return_status := FND_API.G_RET_STS_ERROR;
1397 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1398 oe_debug_pub.add('2. ret sts of unexp error');
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 END IF;
1401
1402 -- Decrement qty/amount for old blanket line
1403 IF p_request_tbl(I).param1 IS NOT NULL THEN
1404
1405 if l_debug_level > 0 then
1406 oe_debug_pub.add('Decrement qty/amt for old blanket num :'
1410 Update_Released_Qty_Amount
1407 ||p_request_tbl(I).param1);
1408 end if;
1409
1411 (p_line_id => p_request_tbl(I).entity_id
1412 ,p_blanket_number => p_request_tbl(I).param1
1413 ,p_blanket_line_number => p_request_tbl(I).param2
1414 ,p_old_quantity => p_request_tbl(I).param3
1415 ,p_quantity => 0
1416 ,p_old_order_qty_uom => p_request_tbl(I).param4
1417 ,p_order_qty_uom => null
1418 ,p_old_unit_selling_price => p_request_tbl(I).param5
1419 ,p_unit_selling_price => 0
1420 ,p_old_inv_item_id => p_request_tbl(I).param6
1421 ,p_inv_item_id => null
1422 ,p_currency_code => l_currency_code
1423 ,p_fulfilled_flag => p_request_tbl(I).param8
1424 ,p_line_set_id => p_request_tbl(I).param9
1425 ,x_return_status => l_return_status
1426 );
1427
1428 END IF;
1429
1430 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1431 oe_debug_pub.add('3. ret sts of error');
1432 x_return_status := FND_API.G_RET_STS_ERROR;
1433 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1434 oe_debug_pub.add('3. ret sts of unexp error');
1435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1436 END IF;
1437
1438 END IF;
1439
1440 p_request_tbl(I).processed := 'Y';
1441
1442 <<end_of_loop>>
1443
1444 I := p_request_tbl.NEXT(I);
1445
1446 OE_MSG_PUB.Reset_Msg_Context('LINE');
1447 END LOOP;
1448
1449
1450 -- Error during updates, mark the requests as NOT processed
1451 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1452
1453 if l_debug_level > 0 then
1454 oe_debug_pub.add('Overall Ret Sts :'||x_return_status);
1455 end if;
1456
1457 I := p_request_tbl.FIRST;
1458 WHILE I IS NOT NULL LOOP
1459 IF p_request_tbl(I).request_type = 'PROCESS_RELEASE' THEN
1460 if l_debug_level > 0 then
1461 oe_debug_pub.add('Set req to NOT processed at index :'||I);
1462 end if;
1463 p_request_tbl(I).processed := 'N';
1464 END IF;
1465 I := p_request_tbl.NEXT(I);
1466 END LOOP;
1467
1468 -- Updates of quantity/amounts was successful!
1469 -- Update these values on the DB for blanket headers/lines table.
1470 ELSIF g_blkt_line_tbl.COUNT > 0 THEN
1471
1472 Update_Blankets(l_return_status);
1473
1474 -- Only if blankets update is successful,
1475 -- delete the old order released qty/amount also.
1476 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1477
1478 oe_debug_pub.add('delete cache');
1479 g_bl_order_val_tbl.delete;
1480 g_bh_order_val_tbl.delete;
1481
1482 END IF;
1483
1484 END IF;
1485
1486 if l_debug_level > 0 then
1487 oe_debug_pub.add('EXIT OE_Blanket_Util.Process_Releases');
1488 end if;
1489
1490 EXCEPTION
1491 WHEN FND_API.G_EXC_ERROR THEN
1492 x_return_status := FND_API.G_RET_STS_ERROR;
1493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1495 WHEN OTHERS THEN
1496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1497 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1498 THEN
1499 OE_MSG_PUB.Add_Exc_Msg
1500 (G_PKG_NAME
1501 ,'Process_Releases'
1502 );
1503 END IF;
1504 END Process_Releases;
1505
1506 -- Sub-procedure, called twice for both old and new blanket
1507 -- reference on the line.
1508 PROCEDURE Populate_Old_Values
1509 (p_blanket_number IN NUMBER
1510 ,p_blanket_line_number IN NUMBER
1511 ,p_line_id IN NUMBER
1512 ,p_old_quantity IN NUMBER DEFAULT NULL
1513 ,p_old_unit_sp IN NUMBER DEFAULT NULL
1514 ,p_header_id IN NUMBER DEFAULT NULL
1515 )
1516 IS
1517 l_return_status VARCHAR2(3);
1518 l_header_id NUMBER;
1519 l_blanket_header_id NUMBER;
1520 l_blanket_line_id NUMBER;
1521 l_old_quantity NUMBER;
1522 l_old_unit_sp NUMBER;
1523 l_old_amount NUMBER;
1524 l_rem_bl_line_qty NUMBER;
1525 l_rem_bl_line_amt NUMBER;
1526 l_rem_bl_hdr_amt NUMBER;
1527 l_currency_code VARCHAR2(15);
1528 --
1529 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1530 --
1531 BEGIN
1532
1533 if l_debug_level > 0 then
1534 oe_debug_pub.add('Enter Populate_Old_Values');
1535 oe_debug_pub.add('Blanket Num :'||p_blanket_number);
1536 oe_debug_pub.add('Blanket Line Num :'||p_blanket_line_number);
1537 oe_debug_pub.add('Line ID :'||p_line_id);
1538 oe_debug_pub.add('Header ID :'||p_header_id);
1539 oe_debug_pub.add('Old Qty :'||p_old_quantity);
1540 oe_debug_pub.add('Old Selling Price :'||p_old_unit_sp);
1541 end if;
1542
1543 -- Do not lock when caching old values
1544 Cache_Blanket
1545 (p_blanket_number => p_blanket_number
1546 ,p_blanket_line_number => p_blanket_line_number
1547 ,p_lock => 'N'
1548 ,x_blanket_header_id => l_blanket_header_id
1549 ,x_blanket_line_id => l_blanket_line_id
1550 );
1551
1552 -- If not cached already, retrieve the old values
1553 IF NOT g_bl_order_val_tbl.exists(l_blanket_line_id)THEN
1554
1555 IF p_header_id IS NULL
1556 OR p_old_quantity IS NULL
1557 THEN
1558
1559 BEGIN
1560 SELECT HEADER_ID, ORDERED_QUANTITY, UNIT_SELLING_PRICE
1561 INTO l_header_id, l_old_quantity, l_old_unit_sp
1562 FROM OE_ORDER_LINES_ALL
1563 WHERE LINE_ID = p_line_id;
1564 EXCEPTION
1565 -- If QP sourcing API is called while the line is still not
1566 -- saved e.g. when tabbing out of qty field from the UI, no data
1567 -- found exception will result and it should be handled.
1568 WHEN NO_DATA_FOUND THEN
1569 l_old_quantity := 0;
1570 l_old_unit_sp := 0;
1571 END;
1572
1573 END IF;
1574
1575 IF p_old_quantity IS NOT NULL THEN
1576 l_old_quantity := p_old_quantity;
1577 END IF;
1578
1579 IF p_old_unit_sp IS NOT NULL THEN
1580 l_old_unit_sp := p_old_unit_sp;
1581 END IF;
1582
1583 IF p_header_id IS NOT NULL THEN
1584 l_header_id := p_header_id;
1585 END IF;
1586
1587 -- Bug 3390070
1588 -- Use l_old_quantity/unit_sp instead of p_old_quantity/unit_sp
1592 l_old_amount := nvl(l_old_quantity,0) * nvl(l_old_unit_sp,0);
1589 -- Because it was using wrong variables, old amount was not
1590 -- calculated correctly thus resulting in incorrect values for
1591 -- blanket amount and blanket line amount accumulations.
1593
1594 -- Compute the old qty/amt consumed against this blanket line
1595 -- from other lines on this order
1596 SELECT sum(nvl(ordered_quantity,0)),
1597 sum(nvl(ordered_quantity,0)*nvl(unit_selling_price,0))
1598 INTO l_rem_bl_line_qty
1599 ,l_rem_bl_line_amt
1600 FROM OE_ORDER_LINES_ALL
1601 WHERE HEADER_ID = l_header_id
1602 AND BLANKET_NUMBER = p_blanket_number
1603 AND BLANKET_LINE_NUMBER = p_blanket_line_number
1604 AND LINE_ID <> p_line_id;
1605
1606 -- Update old order values on the blanket line cache
1607 g_bl_order_val_tbl(l_blanket_line_id).order_released_quantity
1608 := nvl(l_rem_bl_line_qty,0) + l_old_quantity;
1609 g_bl_order_val_tbl(l_blanket_line_id).order_released_amount
1610 := nvl(l_rem_bl_line_amt,0) + l_old_amount;
1611
1612 -- If blanket hdr amt NOT cached already, cache the old values
1613 IF NOT g_bh_order_val_tbl.exists(l_blanket_header_id)THEN
1614
1615 -- Compute the old qty/amt consumed against this blanket header
1616 -- from other lines on this order
1617 SELECT sum(nvl(ordered_quantity,0)*nvl(unit_selling_price,0))
1618 INTO l_rem_bl_hdr_amt
1619 FROM OE_ORDER_LINES_ALL
1620 WHERE HEADER_ID = l_header_id
1621 AND BLANKET_NUMBER = p_blanket_number
1622 AND LINE_ID <> p_line_id;
1623
1624 -- Update old order values on the blanket header cache
1625 g_bh_order_val_tbl(l_blanket_header_id).order_released_amount
1626 := nvl(l_rem_bl_hdr_amt,0) + l_old_amount;
1627
1628 END IF;
1629
1630 if l_debug_level > 0 then
1631 oe_debug_pub.add('order header id :'||l_header_id);
1632 oe_debug_pub.add('rem bl line qty :'||nvl(l_rem_bl_line_qty,0));
1633 oe_debug_pub.add('rem bl line amt :'||nvl(l_rem_bl_line_amt,0));
1634 oe_debug_pub.add('rem bl hdr amt :'||nvl(l_rem_bl_hdr_amt,0));
1635 oe_debug_pub.add('old qty :'||l_old_quantity);
1636 oe_debug_pub.add('old amt :'||l_old_amount);
1637 end if;
1638
1639 END IF; -- if not cached
1640
1641 EXCEPTION
1642 WHEN FND_API.G_EXC_ERROR THEN
1643 RAISE FND_API.G_EXC_ERROR;
1644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1646 WHEN OTHERS THEN
1647 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1648 THEN
1649 OE_MSG_PUB.Add_Exc_Msg
1650 (G_PKG_NAME
1651 ,'Populate_Old_Values'
1652 );
1653 END IF;
1654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1655 END Populate_Old_Values;
1656
1657 PROCEDURE Cache_Order_Qty_Amt
1658 (p_request_rec IN OUT NOCOPY OE_ORDER_PUB.Request_Rec_Type
1659 ,x_return_status OUT NOCOPY VARCHAR2
1660 )
1661 IS
1662 l_return_status VARCHAR2(3);
1663 l_header_id NUMBER;
1664 l_blanket_header_id NUMBER;
1665 l_blanket_line_id NUMBER;
1666 l_blanket_number NUMBER;
1667 l_blanket_line_number NUMBER;
1668 l_old_line_quantity NUMBER;
1669 l_rem_lines_quantity NUMBER;
1670 l_currency_code VARCHAR2(15);
1671 --
1672 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1673 --
1674
1675 BEGIN
1676
1677 if l_debug_level > 0 then
1678 oe_debug_pub.add('ENTER OE_Blkt_Release_Util.Cache_Order_Qty_Amt');
1679 end if;
1680
1681 x_return_status := fnd_api.g_ret_sts_success;
1682
1683 -- Blanket reference not updated, some other attribute on line updated
1684 IF p_request_rec.param1 = p_request_rec.param11
1685 AND p_request_rec.param2 = p_request_rec.param12
1686 THEN
1687
1688 Populate_Old_Values
1689 (p_blanket_number => p_request_rec.param1
1690 ,p_blanket_line_number => p_request_rec.param2
1691 ,p_line_id => p_request_rec.entity_id
1692 ,p_old_quantity => nvl(p_request_rec.param3,0)
1693 ,p_old_unit_sp => nvl(p_request_rec.param5,0)
1694 );
1695
1696 ELSE
1697
1698 -- New reference, hence no values were sourced for this blanket
1699 -- from this line previously.
1700 IF p_request_rec.param11 IS NOT NULL THEN
1701
1702 Populate_Old_Values
1703 (p_blanket_number => p_request_rec.param11
1704 ,p_blanket_line_number => p_request_rec.param12
1705 ,p_line_id => p_request_rec.entity_id
1706 ,p_old_quantity => 0
1707 ,p_old_unit_sp => 0
1708 );
1709
1710 END IF;
1711
1712 -- Cleared an old blanket reference
1713 IF p_request_rec.param1 IS NOT NULL THEN
1714
1715 Populate_Old_Values
1716 (p_blanket_number => p_request_rec.param1
1717 ,p_blanket_line_number => p_request_rec.param2
1718 ,p_line_id => p_request_rec.entity_id
1719 ,p_old_quantity => nvl(p_request_rec.param3,0)
1720 ,p_old_unit_sp => nvl(p_request_rec.param5,0)
1721 );
1722
1723 END IF;
1724
1725 END IF;
1726
1727 if l_debug_level > 0 then
1728 oe_debug_pub.add('EXIT OE_Blkt_Release_Util.Cache_Order_Qty_Amt');
1729 end if;
1730
1731 EXCEPTION
1732 WHEN FND_API.G_EXC_ERROR THEN
1733 x_return_status := FND_API.G_RET_STS_ERROR;
1734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1736 WHEN OTHERS THEN
1737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1738 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1739 THEN
1740 OE_MSG_PUB.Add_Exc_Msg
1741 (G_PKG_NAME
1742 ,'Cache_Order_Qty_Amt'
1743 );
1744 END IF;
1745 END Cache_Order_Qty_Amt;
1746
1747 END;
[Home] [Help]