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