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