[Home] [Help]
PACKAGE BODY: APPS.OE_BLANKET_PRICING_UTIL
Source
1 PACKAGE BODY OE_Blanket_Pricing_Util AS
2 /* $Header: OEXQPBLB.pls 120.4 2006/01/11 15:09:43 spagadal noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Blanket_Pricing_Util';
4
5 FUNCTION IS_BLANKET_PRICE_LIST(p_price_list_id NUMBER
6 -- 11i10 Pricing Change
7 ,p_blanket_header_id NUMBER DEFAULT NULL)
8 RETURN BOOLEAN IS
9 l_dummy VARCHAR2(30);
10 --
11 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
12 --
13 BEGIN
14
15 if l_debug_level > 0 then
16 oe_debug_pub.add('Enter OEXQPBLB IS_BLANKET_PRICE_LIST');
17 end if;
18
19
20 SELECT 'VALID'
21 INTO l_dummy
22 FROM QP_LIST_HEADERS
23 WHERE LIST_HEADER_ID = p_price_list_id
24 AND LIST_SOURCE_CODE = 'BSO'
25 AND orig_system_header_ref = to_char(p_blanket_header_id);
26
27 RETURN TRUE;
28
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 if l_debug_level > 0 then
32 oe_debug_pub.ADD('Not a blanket price list', 1);
33 end if;
34 RETURN FALSE;
35
36 END IS_BLANKET_PRICE_LIST;
37
38 --------------------------------------------------------------------------
39 -- 11i10 Pricing Changes
40 -- Procedure to create modifier header and lines
41 -- Common procedure to process requests of type 'CREATE_MODIFIER_LIST'
42 -- and 'ADD_MODIFIER_LIST_LINE'
43 --------------------------------------------------------------------------
44 PROCEDURE Create_Modifiers
45 (p_index IN NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2
47 )IS
48
49 l_request_rec OE_Order_PUB.Request_Rec_Type;
50 l_modifier_list_id NUMBER;
51 l_blanket_header_id NUMBER;
52 I NUMBER;
53 J NUMBER;
54 l_return_status varchar2(1);
55 l_msg_count NUMBER;
56 l_msg_data varchar2(2000);
57
58 l_modifier_list_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
59 l_modifier_list_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
60 l_modifiers_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
61 l_modifiers_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
62 l_qualifiers_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
63 l_qualifiers_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
64 l_pricing_attr_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
65 l_pricing_attr_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
66 l_x_modifier_list_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
67 l_x_modifier_list_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
68 l_x_modifiers_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
69 l_x_modifiers_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
70 l_x_qualifiers_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
71 l_x_qualifiers_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
72 l_x_qualifier_rules_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type;
73 l_x_qualifier_rules_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
74 l_x_pricing_attr_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
75 l_x_pricing_attr_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
76 l_control_rec QP_GLOBALS.Control_Rec_Type;
77
78 l_line_id_tbl OE_GLOBALS.Number_Tbl_Type;
79 l_hdr_req_index NUMBER;
80 l_line_req_index_tbl OE_GLOBALS.Number_Tbl_Type;
81
82 --
83 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
84 --
85 BEGIN
86
87 if l_debug_level > 0 then
88 oe_debug_pub.add('Enter Create_Modifiers',1);
89 end if;
90
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92
93 l_request_rec := oe_delayed_requests_pvt.g_delayed_requests(p_index);
94
95 IF l_request_rec.request_type = 'CREATE_MODIFIER_LIST' THEN
96
97 l_blanket_header_id := l_request_rec.entity_id;
98
99 -----------------------------------------------------------
100 -- Set up the list header record
101 -----------------------------------------------------------
102 l_modifier_list_rec.list_type_code := 'DLT'; -- simple discount modifier
103 l_modifier_list_rec.name := l_request_rec.param1;
104 -- description is also a required field so send name for description also
105 l_modifier_list_rec.description := l_request_rec.param1;
106 l_modifier_list_rec.currency_code := l_request_rec.param2;
107 l_modifier_list_rec.orig_system_header_ref := l_blanket_header_id;
108 l_modifier_list_rec.list_source_code := 'BSO';
109 l_modifier_list_rec.shareable_flag := 'N';
110 --Add MOAC changes
111 l_modifier_list_rec.org_id := mo_global.get_current_org_id;
112 l_modifier_list_rec.global_flag := 'N';
113 -- Bug 3210361 -
114 -- Blanket modifiers were not being applied
115 l_modifier_list_rec.start_date_active := trunc(sysdate);
116 l_modifier_list_rec.automatic_flag := 'Y';
117 l_modifier_list_rec.operation := QP_GLOBALS.G_OPR_CREATE;
118 l_hdr_req_index := p_index;
119
120 -- Add blanket header qualifier
121 l_qualifiers_tbl(1).excluder_flag := 'N';
122 l_qualifiers_tbl(1).comparison_operator_code := '=';
123 l_qualifiers_tbl(1).qualifier_context := 'ORDER';
124 l_qualifiers_tbl(1).qualifier_precedence := 700;
125 l_qualifiers_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE5';
126 -- Blanket Header ID is the qualifier attribute value
127 l_qualifiers_tbl(1).qualifier_attr_value := l_blanket_header_id;
128 l_qualifiers_tbl(1).qualifier_grouping_no := 1;
129 l_qualifiers_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
130 -- Bug 3314789
131 l_qualifiers_tbl(1).active_flag := 'Y'; /* jhkuo */
132 l_qualifiers_tbl(1).list_type_code := 'DLT'; /* jhkuo */
133
134 if l_debug_level > 0 then
135 oe_debug_pub.add('Modifier Name :'||l_modifier_list_rec.name);
136 oe_debug_pub.add('Modifier Currency :'||l_modifier_list_rec.currency_code);
137 end if;
138
139 ELSIF l_request_rec.request_type = 'ADD_MODIFIER_LIST_LINE' THEN
140
141 l_blanket_header_id := l_request_rec.param6;
142
143 SELECT /* MOAC_SQL_CHANGE */ he.new_modifier_list_id
144 INTO l_modifier_list_id
145 FROM oe_blanket_headers_ext he, oe_blanket_headers_all h
146 WHERE h.header_id = l_blanket_header_id
147 AND he.order_number = h.order_number
148 AND h.org_id = mo_global.get_current_org_id;
149
150 -- If there is no new modifier list at header level, this should
151 -- have been caught in entity validation hence raise unexp error.
152 IF l_modifier_list_id IS NULL THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155
156 END IF;
157
158
159 -------------------------------------------------------------------
160 -- Loop over the requests table, identify blanket lines where discounts
161 -- were entered inline and add to modifier lines table.
162 -------------------------------------------------------------------
163
164 I := oe_delayed_requests_pvt.g_delayed_requests.first;
165 J := 1;
166
167 WHILE I IS NOT NULL LOOP
168
169 l_request_rec := oe_delayed_requests_pvt.g_delayed_requests(I);
170 oe_debug_pub.add('Req Type :'||l_request_rec.request_type);
171
172 IF l_request_rec.request_type = 'ADD_MODIFIER_LIST_LINE' THEN
173
174 -----------------------------------------------------------
175 -- Set up the list line record
176 -----------------------------------------------------------
177 --for bug 3229225
178 IF (l_request_rec.param1 IS NULL OR l_request_rec.param1=FND_API.G_MISS_NUM)
179 AND (l_request_rec.param2 IS NULL OR l_request_rec.param2=FND_API.G_MISS_NUM)
180 THEN
181 IF l_debug_level > 0
182 THEN
183 oe_debug_pub.add('Skip for null values');
184 END IF;
185 oe_delayed_requests_pvt.g_delayed_requests.delete(I);
186 GOTO SKIP_LINE;
187 END IF;
188 --end bug 3229225
189 -- simple discount line
190 l_modifiers_tbl(J).list_line_type_code := 'DIS';
191 -- will be not null if it is being added to an existing modifier
192 l_modifiers_tbl(J).list_header_id := l_modifier_list_id;
193 l_modifiers_tbl(J).automatic_flag:= 'Y';
194 l_modifiers_tbl(J).modifier_level_code := 'LINE';
195 -- Bug 3210361 -
196 -- Blanket modifiers were not being applied
197 l_modifiers_tbl(J).start_date_active := trunc(sysdate);
198 -- 'List Line Adjustment' phase
199 l_modifiers_tbl(J).pricing_phase_id := 2;
200 l_modifiers_tbl(J).product_precedence := 1;
201 -- if discount percent is specified on the blanket line
202 IF l_request_rec.param1 IS NOT NULL THEN
203 l_modifiers_tbl(J).arithmetic_operator := '%';
204 l_modifiers_tbl(J).operand := l_request_rec.param1;
205 -- if discount amount is specified on the blanket line
206 ELSIF l_request_rec.param2 IS NOT NULL THEN
207 l_modifiers_tbl(J).arithmetic_operator := 'AMT';
208 l_modifiers_tbl(J).operand := l_request_rec.param2;
209 END IF;
210 l_modifiers_tbl(J).operation := QP_GLOBALS.G_OPR_CREATE;
211
212 -----------------------------------------------------------
213 -- Set up the item context on pricing attributes record
214 -----------------------------------------------------------
215 l_pricing_attr_tbl(J).product_attribute_context:= 'ITEM';
216 -- if item category context
217 if l_request_rec.param4 = 'CAT' then
218 l_pricing_attr_tbl(J).PRODUCT_ATTRIBUTE := 'PRICING_ATTRIBUTE2';
219 l_pricing_attr_tbl(J).PRODUCT_ATTR_VALUE := l_request_rec.param3;
220 -- if all items context
221 elsif l_request_rec.param4 = 'ALL' then
222 l_pricing_attr_tbl(J).PRODUCT_ATTRIBUTE := 'PRICING_ATTRIBUTE3';
223 l_pricing_attr_tbl(J).PRODUCT_ATTR_VALUE := 'ALL';
224 -- if a specific item context
225 else
226 l_pricing_attr_tbl(J).product_attribute:= 'PRICING_ATTRIBUTE1';
227 l_pricing_attr_tbl(J).PRODUCT_ATTR_VALUE := l_request_rec.param3;
228 end if;
229 l_pricing_attr_tbl(J).modifiers_index := J;
230 l_pricing_attr_tbl(J).operation := QP_GLOBALS.G_OPR_CREATE;
231
232 -- Keep track of which modifier line index corresponds to which
233 -- blanket line ID.
234 l_line_id_tbl(J) := l_request_rec.entity_id;
235 l_line_req_index_tbl(J) := I;
236
237 if l_debug_level > 0 then
238 oe_debug_pub.add('Operator :'||l_modifiers_tbl(J).arithmetic_operator);
239 oe_debug_pub.add('Operand :'||l_modifiers_tbl(J).operand);
240 oe_debug_pub.add('Prod Attr :'||l_pricing_attr_tbl(J).product_attribute);
241 oe_debug_pub.add('Prod Attr Val :'||l_pricing_attr_tbl(J).product_attr_value);
242 end if;
243 J := J + 1;
244
245 END IF;
246 --for bug 3229225
247 <<SKIP_LINE>>
248 I := oe_delayed_requests_pvt.g_delayed_requests.next(I);
249
250 END LOOP;
251
252
253 -------------------------------------------------------------------
254 -- Call Pricing Group API to create modifier header and modifier lines
255 -------------------------------------------------------------------
256 l_control_rec.called_from_ui := 'N';
257
258 QP_Modifiers_GRP.Process_Modifiers
259 ( p_api_version_number => 1.0
260 , p_init_msg_list => FND_API.G_FALSE
261 , p_return_values => FND_API.G_FALSE
262 , p_commit => FND_API.G_FALSE
263 , x_return_status => l_return_status
264 , x_msg_count => l_msg_count
265 , x_msg_data => l_msg_data
266 , p_control_rec => l_control_rec
267 , p_modifier_list_rec => l_modifier_list_rec
268 , p_modifiers_tbl => l_modifiers_tbl
269 , p_qualifiers_tbl => l_qualifiers_tbl
270 , p_pricing_attr_tbl => l_pricing_attr_tbl
271 , x_modifier_list_rec => l_x_modifier_list_rec
272 , x_modifier_list_val_rec => l_x_modifier_list_val_rec
273 , x_modifiers_tbl => l_x_modifiers_tbl
274 , x_modifiers_val_tbl => l_x_modifiers_val_tbl
275 , x_qualifiers_tbl => l_x_qualifiers_tbl
276 , x_qualifiers_val_tbl => l_x_qualifiers_val_tbl
277 , x_pricing_attr_tbl => l_x_pricing_attr_tbl
278 , x_pricing_attr_val_tbl => l_x_pricing_attr_val_tbl
279 );
280
281 if l_return_status = fnd_api.g_ret_sts_error then
282 raise fnd_api.g_exc_error;
283 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
284 raise fnd_api.g_exc_unexpected_error;
285 end if;
286
287 -- As line level qualifiers cannot be created without list line IDs,
288 -- modifier list lines must be created in previous call to qp_modifiers_grp.
289 -- And a separate call is made to qp_qualifiers_pub to create the
290 -- line qualifiers with created list line IDs.
291
292 if l_x_modifiers_tbl.FIRST is not null then
293
294 l_qualifiers_tbl.DELETE;
295 J := l_x_modifiers_tbl.FIRST;
296
297 WHILE J IS NOT NULL LOOP
298
299 -----------------------------------------------------------
300 -- Set up the blanket line qualifier record
301 -----------------------------------------------------------
302 l_qualifiers_tbl(J).excluder_flag := 'N';
303 l_qualifiers_tbl(J).comparison_operator_code := '=';
304 l_qualifiers_tbl(J).qualifier_context := 'ORDER';
305 l_qualifiers_tbl(J).qualifier_precedence := 800;
306 l_qualifiers_tbl(J).qualifier_attribute := 'QUALIFIER_ATTRIBUTE6';
307 -- Blanket Line ID is the qualifier attribute value
308 oe_debug_pub.add('entity id :'||l_line_id_tbl(J));
309 l_qualifiers_tbl(J).qualifier_attr_value := l_line_id_tbl(J);
310 l_qualifiers_tbl(J).qualifier_grouping_no := 1;
311 l_qualifiers_tbl(J).operation := QP_GLOBALS.G_OPR_CREATE;
312 -- Copy list line ID from created modiifer lines table
313 l_qualifiers_tbl(J).list_header_id :=
314 l_x_modifiers_tbl(J).list_header_id;
315 l_qualifiers_tbl(J).list_line_id :=
316 l_x_modifiers_tbl(J).list_line_id;
317 oe_debug_pub.add('sending list header id on qual :'||
318 l_qualifiers_tbl(J).list_header_id);
319 oe_debug_pub.add('sending list line id on qual :'||
320 l_qualifiers_tbl(J).list_line_id);
321
322 J := l_x_modifiers_tbl.NEXT(J);
323
324 END LOOP;
325
326 QP_Qualifier_Rules_PUB.Process_Qualifier_Rules
327 ( p_api_version_number => 1.0
328 , p_init_msg_list => FND_API.G_FALSE
329 , p_return_values => FND_API.G_FALSE
330 , p_commit => FND_API.G_FALSE
331 , x_return_status => l_return_status
332 , x_msg_count => l_msg_count
333 , x_msg_data => l_msg_data
334 , p_qualifiers_tbl => l_qualifiers_tbl
335 , x_qualifiers_tbl => l_x_qualifiers_tbl
336 , x_qualifiers_val_tbl => l_x_qualifiers_val_tbl
337 , x_qualifier_rules_rec => l_x_qualifier_rules_rec
338 , x_qualifier_rules_val_rec => l_x_qualifier_rules_val_rec
339 );
340
344 raise fnd_api.g_exc_error;
341 end if;
342
343 if l_return_status = fnd_api.g_ret_sts_error then
345 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
346 raise fnd_api.g_exc_unexpected_error;
347 end if;
348
349 -------------------------------------------------------------------
350 -- Update modifier IDs on blanket tables
351 -------------------------------------------------------------------
352
353 -- Update modifier list header id on blanket header table
354 IF l_modifier_list_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
355
356 if l_debug_level > 0 then
357 oe_debug_pub.add('blanket header id :'||l_blanket_header_id);
358 oe_debug_pub.add('set modifier list id :'||
359 l_x_modifier_list_rec.list_header_id);
360 end if;
361
362 update oe_blanket_headers
363 set lock_control = lock_control + 1
364 ,last_updated_by = FND_GLOBAL.USER_ID
365 ,last_update_date = sysdate
366 where header_id = l_blanket_header_id;
367
368 update oe_blanket_headers_ext
369 set new_modifier_list_id = l_x_modifier_list_rec.list_header_id
370 where order_number = (select /* MOAC_SQL_CHANGE */ order_number
371 from oe_blanket_headers_all
372 where header_id = l_blanket_header_id
373 and org_id = mo_global.get_current_org_id);
374
375 oe_delayed_requests_pvt.g_delayed_requests.delete(l_hdr_req_index);
376 oe_blanket_util.g_header_rec.new_modifier_list_id := l_x_modifier_list_rec.list_header_id;
377
378 END IF;
379
380 -- Update modifier list line id on blanket line table
381 I := l_line_id_tbl.FIRST;
382 WHILE I IS NOT NULL LOOP
383
384 if l_debug_level > 0 then
385 oe_debug_pub.add('blanket line id :'||l_line_id_tbl(I));
386 oe_debug_pub.add('modifier list line id :'||l_x_modifiers_tbl(I).list_line_id);
387 end if;
388
389 update oe_blanket_lines
390 set lock_control = lock_control + 1
391 ,last_updated_by = FND_GLOBAL.USER_ID
392 ,last_update_date = sysdate
393 where line_id = l_line_id_tbl(I);
394
395 update oe_blanket_lines_ext
396 set modifier_list_line_id = l_x_modifiers_tbl(I).list_line_id
397 where line_id = l_line_id_tbl(I);
398
399 oe_delayed_requests_pvt.g_delayed_requests.delete
400 (l_line_req_index_tbl(I));
401
402 I := l_line_id_tbl.NEXT(I);
403
404 END LOOP;
405
406 OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
407 oe_blanket_util.g_new_modifier_list := FALSE;
408
409 if l_debug_level > 0 then
410 oe_debug_pub.add('Exit Create_Modifiers',1);
411 end if;
412 EXCEPTION
413 WHEN FND_API.G_EXC_ERROR THEN
414 x_return_status := fnd_api.g_ret_sts_error;
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 x_return_status := fnd_api.g_ret_sts_unexp_error;
417 WHEN OTHERS THEN
418 x_return_status := fnd_api.g_ret_sts_unexp_error;
419 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
420 THEN
421 OE_MSG_PUB.Add_Exc_Msg
422 (G_PKG_NAME
423 ,'Create_Modifiers'
424 );
425 END IF;
426 END Create_Modifiers;
427
428 PROCEDURE Deactivate_Price_List
429 (p_list_header_id IN NUMBER DEFAULT NULL
430 ,p_list_line_id IN NUMBER DEFAULT NULL
431 ,x_return_status IN OUT NOCOPY VARCHAR2
432 )
433 IS
434 gpr_msg_count number := 0;
435 gpr_msg_data varchar2(2000);
436 gpr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
437 gpr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
438 ppr_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
439 ppr_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
440 ppr_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
441 ppr_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
442 ppr_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
443 ppr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
444 ppr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
445 ppr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
446 --
447 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
448 --
449 BEGIN
450
451 if l_debug_level > 0 then
452 oe_debug_pub.add('Enter Deactivate_Price_List');
453 oe_debug_pub.add('p_list_header_id :'||p_list_header_id);
454 oe_debug_pub.add('p_list_line_id :'||p_list_line_id);
455 end if;
456
457 IF p_list_header_id IS NOT NULL THEN
458 gpr_price_list_rec.list_header_id := p_list_header_id;
459 gpr_price_list_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
460 gpr_price_list_rec.end_date_active := trunc(sysdate);
461 END IF;
462
463 IF p_list_line_id IS NOT NULL THEN
464 gpr_price_list_line_tbl(1).list_line_id := p_list_line_id;
465 gpr_price_list_line_tbl(1).operation := QP_GLOBALS.G_OPR_UPDATE;
466 gpr_price_list_line_tbl(1).end_date_active := trunc(sysdate);
467 END IF;
468
469 QP_PRICE_LIST_GRP.Process_Price_List
473 , p_commit => FND_API.G_FALSE
470 ( p_api_version_number => 1
471 , p_init_msg_list => FND_API.G_FALSE
472 , p_return_values => FND_API.G_FALSE
474 , x_return_status => x_return_status
475 , x_msg_count => gpr_msg_count
476 , x_msg_data => gpr_msg_data
477 , p_PRICE_LIST_rec => gpr_price_list_rec
478 , p_PRICE_LIST_LINE_tbl => gpr_price_list_line_tbl
479 , x_PRICE_LIST_rec => ppr_price_list_rec
480 , x_PRICE_LIST_val_rec => ppr_price_list_val_rec
481 , x_PRICE_LIST_LINE_tbl => ppr_price_list_line_tbl
482 , x_PRICE_LIST_LINE_val_tbl => ppr_price_list_line_val_tbl
483 , x_QUALIFIERS_tbl => ppr_qualifiers_tbl
484 , x_QUALIFIERS_val_tbl => ppr_qualifiers_val_tbl
485 , x_PRICING_ATTR_tbl => ppr_pricing_attr_tbl
486 , x_PRICING_ATTR_val_tbl => ppr_pricing_attr_val_tbl
487 );
488
489 if l_debug_level > 0 then
490 oe_debug_pub.add('Exit Deactivate_Price_List');
491 end if;
492
493 EXCEPTION
494 WHEN FND_API.G_EXC_ERROR THEN
495 x_return_status := fnd_api.g_ret_sts_error;
496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
497 x_return_status := fnd_api.g_ret_sts_unexp_error;
498 WHEN OTHERS THEN
499 x_return_status := fnd_api.g_ret_sts_unexp_error;
500 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
501 THEN
502 OE_MSG_PUB.Add_Exc_Msg
503 (G_PKG_NAME
504 ,'Deactivate_Price_List'
505 );
506 END IF;
507 END Deactivate_Price_List;
508
509 PROCEDURE Deactivate_Modifier
510 (p_list_header_id IN NUMBER DEFAULT NULL
511 ,p_list_line_id IN NUMBER DEFAULT NULL
512 ,x_return_status IN OUT NOCOPY VARCHAR2
513 )
514 IS
515 l_return_status varchar2(30);
516 l_msg_count number := 0;
517 l_msg_data varchar2(2000);
518 --
519 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
520 --
521 l_modifier_list_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
522 l_modifiers_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
523 l_x_modifier_list_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
524 l_x_modifier_list_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
525 l_x_modifiers_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
526 l_x_modifiers_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
527 l_x_qualifiers_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
528 l_x_qualifiers_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
529 l_x_pricing_attr_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
530 l_x_pricing_attr_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
531 l_control_rec QP_GLOBALS.Control_Rec_Type;
532 BEGIN
533
534 if l_debug_level > 0 then
535 oe_debug_pub.add('Enter Deactivate_Modifier');
536 oe_debug_pub.add('p_list_header_id :'||p_list_header_id);
537 oe_debug_pub.add('p_list_line_id :'||p_list_line_id);
538 end if;
539
540 IF p_list_header_id IS NOT NULL THEN
541 l_modifier_list_rec.list_header_id := p_list_header_id;
542 l_modifier_list_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
543 l_modifier_list_rec.end_date_active := trunc(sysdate);
544 END IF;
545
546 IF p_list_line_id IS NOT NULL THEN
547 l_modifiers_tbl(1).list_line_id := p_list_line_id;
548 l_modifiers_tbl(1).operation := QP_GLOBALS.G_OPR_UPDATE;
549 l_modifiers_tbl(1).end_date_active := trunc(sysdate);
550 END IF;
551
552 -------------------------------------------------------------------
553 -- Call Pricing Group API to update modifier header and modifier lines
554 -------------------------------------------------------------------
555 l_control_rec.called_from_ui := 'N';
556
557 QP_Modifiers_GRP.Process_Modifiers
558 ( p_api_version_number => 1.0
559 , p_init_msg_list => FND_API.G_FALSE
560 , p_return_values => FND_API.G_FALSE
561 , p_commit => FND_API.G_FALSE
562 , x_return_status => l_return_status
563 , x_msg_count => l_msg_count
564 , x_msg_data => l_msg_data
565 , p_control_rec => l_control_rec
566 , p_modifier_list_rec => l_modifier_list_rec
567 , p_modifiers_tbl => l_modifiers_tbl
568 , x_modifier_list_rec => l_x_modifier_list_rec
569 , x_modifier_list_val_rec => l_x_modifier_list_val_rec
570 , x_modifiers_tbl => l_x_modifiers_tbl
571 , x_modifiers_val_tbl => l_x_modifiers_val_tbl
572 , x_qualifiers_tbl => l_x_qualifiers_tbl
573 , x_qualifiers_val_tbl => l_x_qualifiers_val_tbl
574 , x_pricing_attr_tbl => l_x_pricing_attr_tbl
575 , x_pricing_attr_val_tbl => l_x_pricing_attr_val_tbl
576 );
577
578 if l_return_status = fnd_api.g_ret_sts_error then
579 raise fnd_api.g_exc_error;
580 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
581 raise fnd_api.g_exc_unexpected_error;
582 end if;
583
587
584 if l_debug_level > 0 then
585 oe_debug_pub.add('Exit Deactivate_Modifier');
586 end if;
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 x_return_status := fnd_api.g_ret_sts_error;
591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
592 x_return_status := fnd_api.g_ret_sts_unexp_error;
593 WHEN OTHERS THEN
594 x_return_status := fnd_api.g_ret_sts_unexp_error;
595 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
596 THEN
597 OE_MSG_PUB.Add_Exc_Msg
598 (G_PKG_NAME
599 ,'Deactivate_Modifier'
600 );
601 END IF;
602 END Deactivate_Modifier;
603
604 PROCEDURE Deactivate_Pricing
605 (p_blanket_header_id IN NUMBER DEFAULT NULL
606 ,p_blanket_line_id IN NUMBER DEFAULT NULL
607 ,x_return_status IN OUT NOCOPY VARCHAR2
608 )
609 IS
610 l_qp_list_line_id NUMBER;
611 l_mod_list_line_id NUMBER;
612 l_price_list_tbl QP_Price_List_PUB.Price_List_Tbl_Type;
613 l_modifier_list_tbl QP_Modifiers_PUB.Modifier_List_Tbl_Type;
614 l_return_status VARCHAR2(30);
615 l_msg_count NUMBER;
616 l_msg_data VARCHAR2(2000);
617 l_exist_qp_list_line_id VARCHAR2(1) := 'Y';
618 --
619 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
620 --
621 BEGIN
622
623 if l_debug_level > 0 then
624 oe_debug_pub.add('Enter Deactivate_Pricing');
625 oe_debug_pub.add('p_blanket_header_id :'||p_blanket_header_id);
626 oe_debug_pub.add('p_blanket_line_id :'||p_blanket_line_id);
627 end if;
628
629 x_return_status := fnd_api.g_ret_sts_success;
630
631 IF p_blanket_line_id IS NOT NULL THEN
632
633 SELECT qp_list_line_id, modifier_list_line_id
634 INTO l_qp_list_line_id, l_mod_list_line_id
635 FROM OE_BLANKET_LINES_EXT
636 WHERE line_id = p_blanket_line_id;
637 -- Check if the QP line exist in the QP tables.
638 -- For the bug #3985489
639 begin
640 select 'Y'
641 into l_exist_qp_list_line_id
642 from qp_list_lines
643 where list_line_id = l_qp_list_line_id;
644 exception
645 when others then
646 l_exist_qp_list_line_id := 'N';
647 end;
648
649 IF l_qp_list_line_id IS NOT NULL and
650 l_exist_qp_list_line_id = 'Y' THEN
651 Deactivate_Price_List
652 (p_list_line_id => l_qp_list_line_id
653 ,x_return_status => l_return_status
654 );
655 if l_return_status = fnd_api.g_ret_sts_error then
656 raise fnd_api.g_exc_error;
657 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
658 raise fnd_api.g_exc_unexpected_error;
659 end if;
660 END IF;
661
662 IF l_mod_list_line_id IS NOT NULL THEN
663 Deactivate_Modifier
664 (p_list_line_id => l_mod_list_line_id
665 ,x_return_status => l_return_status
666 );
667 if l_return_status = fnd_api.g_ret_sts_error then
668 raise fnd_api.g_exc_error;
669 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
670 raise fnd_api.g_exc_unexpected_error;
671 end if;
672 END IF;
673
674 ELSIF p_blanket_header_id IS NOT NULL THEN
675
676 QP_UTIL_PUB.Get_Blanket_Pricelist_Modifier
677 (p_blanket_header_id => p_blanket_header_id
678 ,x_price_list_tbl => l_price_list_tbl
679 ,x_modifier_list_tbl => l_modifier_list_tbl
680 ,x_return_status => l_return_status
681 ,x_msg_count => l_msg_count
682 ,x_msg_data => l_msg_data
683 );
684
685 FOR I IN 1..l_price_list_tbl.COUNT LOOP
686 Deactivate_Price_List
687 (p_list_header_id => l_price_list_tbl(I).list_header_id
688 ,x_return_status => l_return_status
689 );
690 END LOOP;
691
692 FOR I IN 1..l_modifier_list_tbl.COUNT LOOP
693 Deactivate_Modifier
694 (p_list_header_id => l_modifier_list_tbl(I).list_header_id
695 ,x_return_status => l_return_status
696 );
697 END LOOP;
698
699 END IF;
700
701 if l_debug_level > 0 then
702 oe_debug_pub.add('Exit Deactivate_Pricing');
703 end if;
704
705 EXCEPTION
706 WHEN FND_API.G_EXC_ERROR THEN
707 x_return_status := fnd_api.g_ret_sts_error;
708 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
709 x_return_status := fnd_api.g_ret_sts_unexp_error;
710 WHEN OTHERS THEN
711 x_return_status := fnd_api.g_ret_sts_unexp_error;
712 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
713 THEN
714 OE_MSG_PUB.Add_Exc_Msg
715 (G_PKG_NAME
716 ,'Deactivate_Pricing'
717 );
718 END IF;
719 END Deactivate_Pricing;
720
721
722 FUNCTION Get_Blanket_Header_ID
723 ( p_blanket_number IN NUMBER
724 )RETURN NUMBER IS
725 l_blanket_header_id NUMBER;
726 --
727 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
728 --
729 BEGIN
733 oe_debug_pub.add('Blanket num :'||p_blanket_number);
730
731 if l_debug_level > 0 then
732 oe_debug_pub.add('Enter Get_Blanket_Header_ID');
734 end if;
735
736 IF p_blanket_number IS NOT NULL
737 AND p_blanket_number <> FND_API.G_MISS_NUM
738 THEN
739 select /* MOAC_SQL_CHANGE */ header_id
740 into l_blanket_header_id
741 from oe_blanket_headers_all
742 where order_number = p_blanket_number
743 and org_id = mo_global.get_current_org_id;
744 END IF;
745
746 if l_debug_level > 0 then
747 oe_debug_pub.add('Return Get_Blanket_Header_ID :'||l_blanket_header_id);
748 end if;
749
750 RETURN l_blanket_header_id;
751
752 EXCEPTION
753 WHEN NO_DATA_FOUND THEN
754 if l_debug_level > 0 then
755 oe_debug_pub.add('No data found in Get_Blanket_Header_ID');
756 end if;
757 RETURN NULL;
758 WHEN OTHERS THEN
759 IF l_debug_level > 0 THEN
760 oe_debug_pub.add('other error :'||SQLERRM ) ;
761 END IF;
762 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
763 THEN
764 OE_MSG_PUB.Add_Exc_Msg
765 ( G_PKG_NAME
766 , 'Get_Blanket_Header_ID'
767 );
768 END IF;
769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
770 END Get_Blanket_Header_ID;
771
772 FUNCTION Get_Blanket_Line_ID
773 ( p_blanket_number IN NUMBER
774 , p_blanket_line_number IN NUMBER
775 )RETURN NUMBER IS
776 l_blanket_line_id NUMBER;
777 --
778 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
779 --
780 BEGIN
781
782 if l_debug_level > 0 then
783 oe_debug_pub.add('Enter Get_Blanket_Line_ID');
784 oe_debug_pub.add('Blanket num :'||p_blanket_number);
785 oe_debug_pub.add('Blanket line num :'||p_blanket_line_number);
786 end if;
787
788 IF p_blanket_number IS NOT NULL
789 AND p_blanket_line_number IS NOT NULL
790 THEN
791 select line_id
792 into l_blanket_line_id
793 from oe_blanket_lines_ext
794 where order_number = p_blanket_number
795 and line_number = p_blanket_line_number;
796 END IF;
797
798 if l_debug_level > 0 then
799 oe_debug_pub.add('Return Get_Blanket_Line_ID :'||l_blanket_line_id);
800 end if;
801
802 RETURN l_blanket_line_id;
803
804 EXCEPTION
805 WHEN NO_DATA_FOUND THEN
806 if l_debug_level > 0 then
807 oe_debug_pub.add('No data found in Get_Blanket_Line_ID');
808 end if;
809 RETURN NULL;
810 WHEN OTHERS THEN
811 IF l_debug_level > 0 THEN
812 oe_debug_pub.add('other error :'||SQLERRM ) ;
813 END IF;
814 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
815 THEN
816 OE_MSG_PUB.Add_Exc_Msg
817 ( G_PKG_NAME
818 , 'Get_Blanket_Line_ID'
819 );
820 END IF;
821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
822 END Get_Blanket_Line_ID;
823
824 FUNCTION Get_List_Line_ID
825 ( p_blanket_number IN NUMBER
826 , p_blanket_line_number IN NUMBER
827 )RETURN NUMBER IS
828 l_list_line_id NUMBER;
829 --
830 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
831 --
832 BEGIN
833
834 if l_debug_level > 0 then
835 oe_debug_pub.add('Enter Get_List_Line_ID');
836 oe_debug_pub.add('Blanket num :'||p_blanket_number);
837 oe_debug_pub.add('Blanket line num :'||p_blanket_line_number);
838 end if;
839
840 IF p_blanket_number IS NOT NULL
841 AND p_blanket_line_number IS NOT NULL
842 THEN
843 select qp_list_line_id
844 into l_list_line_id
845 from oe_blanket_lines_ext
846 where order_number = p_blanket_number
847 and line_number = p_blanket_line_number;
848 END IF;
849
850 if l_debug_level > 0 then
851 oe_debug_pub.add('RETURN_Line_ID :'||l_list_line_id);
852 end if;
853
854 RETURN l_list_line_id;
855
856 EXCEPTION
857 WHEN NO_DATA_FOUND THEN
858 if l_debug_level > 0 then
859 oe_debug_pub.add('No data found in Get_List_Line_ID');
860 end if;
861 RETURN NULL;
862 WHEN OTHERS THEN
863 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
864 THEN
865 OE_MSG_PUB.Add_Exc_Msg
866 ( G_PKG_NAME
867 , 'Get_List_Line_ID'
868 );
869 END IF;
870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
871 END Get_List_Line_ID;
872
873 FUNCTION Get_Blanket_Rel_Amt
874 ( p_blanket_number IN NUMBER
875 , p_blanket_line_number IN NUMBER
876 , p_line_id IN NUMBER DEFAULT NULL
877 , p_header_id IN NUMBER DEFAULT NULL
878 , p_transaction_phase_code IN VARCHAR2 DEFAULT NULL
879 )RETURN NUMBER IS
880 l_rel_amt NUMBER;
881 l_released_amount NUMBER;
882 l_returned_amount NUMBER;
883 l_order_rel_amt NUMBER := 0;
887 --
884 l_blanket_header_id NUMBER;
885 --
886 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
888 BEGIN
889
890 if l_debug_level > 0 then
891 oe_debug_pub.add('Enter Get_Blanket_Rel_Amt');
892 oe_debug_pub.add('Blkt Num :'||p_blanket_number);
893 oe_debug_pub.add('Blkt Line Num :'||p_blanket_line_number);
894 oe_debug_pub.add('Line ID :'||p_line_id);
895 oe_debug_pub.add('Header ID :'||p_header_id);
896 end if;
897
898 IF p_blanket_number IS NULL
899 -- Bug 3350448 =>
900 -- Cache blanket will raise a no data found if there is no blanket
901 -- line number. In any case, blanket line number is mandatory
902 -- blanket number is supplied on the release so execute this sourcing
903 -- api also only when both blanket number and blanket line number exist.
904 OR p_blanket_line_number IS NULL
905 THEN
906 RETURN NULL;
907 ENd IF;
908
909 select /* MOAC_SQL_CHANGE */ nvl(bhe.released_amount,0)
910 ,nvl(bhe.returned_amount,0)
911 ,bh.header_id
912 into l_released_amount
913 ,l_returned_amount
914 ,l_blanket_header_id
915 from oe_blanket_headers_ext bhe, oe_blanket_headers_all bh
916 where bhe.order_number = p_blanket_number
917 and bhe.order_number = bh.order_number
918 and bh.org_id = mo_global.get_current_orG_id;
919 oe_debug_pub.add('current blkt rel amt :'||l_released_amount);
920 oe_debug_pub.add('current blkt ret amt :'||l_returned_amount);
921
922 IF nvl(p_transaction_phase_code,'F') = 'F' THEN
923
924 OE_Blkt_Release_Util.Populate_Old_Values
925 (p_blanket_number => p_blanket_number
926 ,p_blanket_line_number => p_blanket_line_number
927 ,p_line_id => p_line_id
928 ,p_header_id => p_header_id
929 );
930
931 IF OE_Blkt_Release_Util.g_blkt_hdr_tbl.EXISTS(l_blanket_header_id) THEN
932 l_order_rel_amt :=
933 OE_Blkt_Release_Util.g_bh_order_val_tbl(l_blanket_header_id).order_released_amount;
934 oe_debug_pub.add('order rel amt :'||l_order_rel_amt);
935 END IF;
936
937 END IF;
938
939 l_rel_amt := l_released_amount - l_returned_amount - l_order_rel_amt;
940
941 if l_debug_level > 0 then
942 oe_debug_pub.add('Blanket Rel Amt :'||l_rel_amt);
943 end if;
944
945 RETURN l_rel_amt;
946
947 EXCEPTION
948 WHEN OTHERS THEN
949 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
950 THEN
951 OE_MSG_PUB.Add_Exc_Msg
952 ( G_PKG_NAME
953 , 'Get_Blanket_Rel_Amt'
954 );
955 END IF;
956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
957 END Get_Blanket_Rel_Amt;
958
959
960 FUNCTION Get_Bl_Line_Rel_Amt
961 ( p_blanket_number IN NUMBER
962 , p_blanket_line_number IN NUMBER
963 , p_line_id IN NUMBER DEFAULT NULL
964 , p_header_id IN NUMBER DEFAULT NULL
965 , p_transaction_phase_code IN VARCHAR2 DEFAULT NULL
966 )RETURN NUMBER IS
967 l_rel_amt NUMBER;
968 l_released_amount NUMBER;
969 l_returned_amount NUMBER;
970 l_order_rel_amt NUMBER := 0;
971 l_blanket_line_id NUMBER;
972 --
973 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
974 --
975 BEGIN
976
977 if l_debug_level > 0 then
978 oe_debug_pub.add('Enter Get_Bl_Line_Rel_Amt');
979 oe_debug_pub.add('Blkt Num :'||p_blanket_number);
980 oe_debug_pub.add('Blkt Line Num :'||p_blanket_line_number);
981 oe_debug_pub.add('Line ID :'||p_line_id);
982 oe_debug_pub.add('Header ID :'||p_header_id);
983 end if;
984
985 IF p_blanket_number IS NULL
986 OR p_blanket_line_number IS NULL
987 THEN
988 RETURN NULL;
989 ENd IF;
990
991 select nvl(released_amount,0)
992 ,nvl(returned_amount,0)
993 ,line_id
994 into l_released_amount
995 ,l_returned_amount
996 ,l_blanket_line_id
997 from oe_blanket_lines_ext
998 where order_number = p_blanket_number
999 and line_number = p_blanket_line_number;
1000 oe_debug_pub.add('current blkt rel amt :'||l_released_amount);
1001 oe_debug_pub.add('current blkt ret amt :'||l_returned_amount);
1002
1003 IF nvl(p_transaction_phase_code,'F') = 'F' THEN
1004
1005 OE_Blkt_Release_Util.Populate_Old_Values
1006 (p_blanket_number => p_blanket_number
1007 ,p_blanket_line_number => p_blanket_line_number
1008 ,p_line_id => p_line_id
1009 ,p_header_id => p_header_id
1010 );
1011
1012 IF OE_Blkt_Release_Util.g_blkt_line_tbl.EXISTS(l_blanket_line_id) THEN
1013 l_order_rel_amt :=
1014 OE_Blkt_Release_Util.g_bl_order_val_tbl(l_blanket_line_id).order_released_amount;
1015 oe_debug_pub.add('order rel amt :'||l_order_rel_amt);
1016 END IF;
1017
1018 END IF;
1019
1020 l_rel_amt := l_released_amount - l_returned_amount - l_order_rel_amt;
1021
1025
1022 if l_debug_level > 0 then
1023 oe_debug_pub.add('BL Line Rel Amt :'||l_rel_amt);
1024 end if;
1026 RETURN l_rel_amt;
1027
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1031 THEN
1032 OE_MSG_PUB.Add_Exc_Msg
1033 ( G_PKG_NAME
1034 , 'Get_Bl_Line_Rel_Amt'
1035 );
1036 END IF;
1037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1038 END Get_Bl_Line_Rel_Amt;
1039
1040 FUNCTION Get_Bl_Line_Rel_Qty
1041 ( p_blanket_number IN NUMBER
1042 , p_blanket_line_number IN NUMBER
1043 , p_line_id IN NUMBER DEFAULT NULL
1044 , p_header_id IN NUMBER DEFAULT NULL
1045 , p_transaction_phase_code IN VARCHAR2 DEFAULT NULL
1046 )RETURN NUMBER IS
1047 l_rel_qty NUMBER;
1048 l_released_quantity NUMBER;
1049 l_returned_quantity NUMBER;
1050 l_order_rel_qty NUMBER := 0;
1051 l_blanket_line_id NUMBER;
1052 --
1053 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1054 --
1055 BEGIN
1056
1057 if l_debug_level > 0 then
1058 oe_debug_pub.add('Enter Get_Bl_Line_Rel_Qty');
1059 oe_debug_pub.add('Blkt Num :'||p_blanket_number);
1060 oe_debug_pub.add('Blkt Line Num :'||p_blanket_line_number);
1061 oe_debug_pub.add('Line ID :'||p_line_id);
1062 oe_debug_pub.add('Header ID :'||p_header_id);
1063 end if;
1064
1065 IF p_blanket_number IS NULL
1066 OR p_blanket_line_number IS NULL
1067 THEN
1068 RETURN NULL;
1069 ENd IF;
1070
1071 select nvl(released_quantity,0)
1072 ,nvl(returned_quantity,0)
1073 ,line_id
1074 into l_released_quantity
1075 ,l_returned_quantity
1076 ,l_blanket_line_id
1077 from oe_blanket_lines_ext
1078 where order_number = p_blanket_number
1079 and line_number = p_blanket_line_number;
1080 oe_debug_pub.add('current blkt rel qty :'||l_released_quantity);
1081 oe_debug_pub.add('current blkt ret qty :'||l_returned_quantity);
1082
1083 IF nvl(p_transaction_phase_code,'F') = 'F' THEN
1084
1085 OE_Blkt_Release_Util.Populate_Old_Values
1086 (p_blanket_number => p_blanket_number
1087 ,p_blanket_line_number => p_blanket_line_number
1088 ,p_line_id => p_line_id
1089 ,p_header_id => p_header_id
1090 );
1091
1095 oe_debug_pub.add('order rel qty :'||l_order_rel_qty);
1092 IF OE_Blkt_Release_Util.g_blkt_line_tbl.EXISTS(l_blanket_line_id) THEN
1093 l_order_rel_qty :=
1094 OE_Blkt_Release_Util.g_bl_order_val_tbl(l_blanket_line_id).order_released_quantity;
1096 END IF;
1097
1098 END IF;
1099
1100 l_rel_qty := l_released_quantity - l_returned_quantity - l_order_rel_qty;
1101
1102 if l_debug_level > 0 then
1103 oe_debug_pub.add('BL Line Rel Qty :'||l_rel_qty);
1104 end if;
1105
1106 RETURN l_rel_qty;
1107
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 IF l_debug_level > 0 THEN
1111 oe_debug_pub.add('other error :'||SQLERRM ) ;
1112 END IF;
1113 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1114 THEN
1115 OE_MSG_PUB.Add_Exc_Msg
1116 ( G_PKG_NAME
1117 , 'Get_Bl_Line_Rel_Qty'
1118 );
1119 END IF;
1120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1121 END Get_Bl_Line_Rel_Qty;
1122
1123 END OE_Blanket_Pricing_Util;