[Home] [Help]
PACKAGE BODY: APPS.OE_PRICE_LIST_PVT
Source
1 PACKAGE BODY OE_Price_List_PVT AS
2 /* $Header: OEXVLSTB.pls 115.1 99/07/16 08:17:02 porting shi $ */
3
4 -- Global constant holding the package name to be used by
5 -- messaging routines in the case of an unexpected error.
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_Price_List_PVT';
8
9 -- This global variable is used to keep count of the fetch level that
10 -- is currently executed. It is used to decide whether to put a message
11 -- indicating that no list price was found for the item on the price
12 -- list. The requirement is to add this message for the primary price
13 -- list only.
14
15 G_Fetch_Level NUMBER := 0;
16
17 -- Utility function called by Fetch)List_Price API.
18
19 FUNCTION Get_Sec_Price_List
20 ( p_price_list_id IN NUMBER )
21 RETURN NUMBER;
22
23 FUNCTION Get_Price_List_Name
24 ( p_price_list_id IN NUMBER )
25 RETURN VARCHAR2;
26
27 FUNCTION Get_Item_Description
28 ( p_item_id IN NUMBER )
29 RETURN VARCHAR2;
30
31 FUNCTION Get_Unit_Name
32 ( p_unit_code IN VARCHAR2 )
33 RETURN VARCHAR2;
34
35 -- Fetch List price API.
36
37 PROCEDURE Fetch_List_Price
38 ( p_api_version_number IN NUMBER ,
39 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
40 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
41 p_return_status OUT VARCHAR2 ,
42 p_msg_count OUT NUMBER ,
43 p_msg_data OUT VARCHAR2 ,
44 p_price_list_id IN NUMBER := NULL ,
45 p_inventory_item_id IN NUMBER := NULL ,
46 p_unit_code IN VARCHAR2 := NULL ,
47 p_service_duration IN NUMBER := NULL ,
48 p_item_type_code IN VARCHAR2 := NULL ,
49 p_prc_method_code IN VARCHAR2 := NULL ,
50 p_pricing_attribute1 IN VARCHAR2 := NULL ,
51 p_pricing_attribute2 IN VARCHAR2 := NULL ,
52 p_pricing_attribute3 IN VARCHAR2 := NULL ,
53 p_pricing_attribute4 IN VARCHAR2 := NULL ,
54 p_pricing_attribute5 IN VARCHAR2 := NULL ,
55 p_pricing_attribute6 IN VARCHAR2 := NULL ,
56 p_pricing_attribute7 IN VARCHAR2 := NULL ,
57 p_pricing_attribute8 IN VARCHAR2 := NULL ,
58 p_pricing_attribute9 IN VARCHAR2 := NULL ,
59 p_pricing_attribute10 IN VARCHAR2 := NULL ,
60 p_pricing_attribute11 IN VARCHAR2 := NULL ,
61 p_pricing_attribute12 IN VARCHAR2 := NULL ,
62 p_pricing_attribute13 IN VARCHAR2 := NULL ,
63 p_pricing_attribute14 IN VARCHAR2 := NULL ,
64 p_pricing_attribute15 IN VARCHAR2 := NULL ,
65 p_base_price IN NUMBER := NULL ,
66 p_fetch_attempts IN NUMBER := G_PRC_LST_DEF_ATTEMPTS ,
67 p_price_list_id_out OUT NUMBER ,
68 p_prc_method_code_out OUT VARCHAR2 ,
69 p_list_price OUT NUMBER ,
70 p_list_percent OUT NUMBER ,
71 p_rounding_factor OUT NUMBER
72 )
73 IS
74 l_api_version_number CONSTANT NUMBER := 1.0;
75 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_List_Price';
76 l_return_status VARCHAR2(1);
77 l_fetch_attempts NUMBER := p_fetch_attempts;
78 l_validation_error BOOLEAN := FALSE;
79 l_prc_method_code VARCHAR2(4) := p_prc_method_code ;
80 l_price_list_id NUMBER := p_price_list_id ;
81 l_prc_method_code_out VARCHAR2(4) := NULL ;
82 l_list_price NUMBER := NULL ;
83 l_list_percent NUMBER := NULL ;
84 l_rounding_factor NUMBER := NULL ;
85 BEGIN
86
87 -- Standard call to check for call compatibility
88
89 IF NOT FND_API.Compatible_API_Call
90 ( l_api_version_number,
91 p_api_version_number,
92 l_api_name ,
93 G_PKG_NAME )
94 THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97
98 -- Initialize message list if p_init_msg_list is set to TRUE
99
100 IF FND_API.to_Boolean(p_init_msg_list) THEN
101
102 FND_MSG_PUB.initialize;
103
104 END IF;
105
106 -- Initialize p_return_status
107
108 p_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 -- Validate Input. Start with mandatory validation.
111
112 -- Fetch_attempts can not be greater that max attempts allowed
113
114 IF p_fetch_attempts > G_PRC_LST_MAX_ATTEMPTS THEN
115
116 l_validation_error := TRUE;
117
118 FND_MESSAGE.SET_NAME('OE','OE_PRC_LIST_INVALID_FETCH_ATTEMPTS');
119 FND_MESSAGE.SET_TOKEN('PASSED_FETCH_ATTEMPTS',p_fetch_attempts);
120 FND_MESSAGE.SET_TOKEN('MAX_FETCH_ATTEMPTS',G_PRC_LST_MAX_ATTEMPTS);
121 FND_MSG_PUB.Add;
122
123 END IF;
124
125 -- Validation that can be turned off through the use of
126 -- validation level.
127
128 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
129
130 -- Validate :
131 -- price_list_id
132 -- item_id
133 -- unit_code
134 -- item_type_code
135 -- fetch_attempts
136 -- This code needs to be added in the future if we provide a
137 -- public API.
138
139 NULL;
140
141 END IF;
142
143 IF l_validation_error THEN
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146
147 -- Check required parameters.
148
149 IF p_price_list_id IS NULL
150 OR p_inventory_item_id IS NULL
151 OR p_unit_code IS NULL
152 THEN
153 RETURN;
154 END IF;
155
156 -- Set the G_Fetch_level. Since this API calls itself
157 -- recursively, this variable indicates the call level. It is
158 -- used forward on.
159
160 G_Fetch_Level := G_Fetch_Level + 1;
161
162 -- Fetch list price.
163
164 -- There are two fetch statements :
165 -- 1. General statement that drives on item id.
166 -- 2. Special case for Oracle USA where we drive on item_id
167 -- and pricing_attribute2
168 -- In case a customer doesn't have an index on
169 -- PRICING_ATTRIBUTE2, it shouldn't be aproblem because
170 -- the statement will still drive on item_id.
171 --
172 -- The ROWNUM = 1 condition is to accomodate the case where there
173 -- is more than one active price list line that meets the select
174 -- criteria. Inherited from release 9.
175
176
177 -- Block encapsulating the fetch statements to handle the case
178 -- where no rows are found. The reason it is not handled in the
179 -- API exception handler, is that the handler itself may raise
180 -- exceptions that should be handled by the API exception
181 -- handler. are no rows.
182
183 BEGIN
184
185 IF p_pricing_attribute2 IS NULL THEN
186
187 -- Debug info
188 /*
189 FND_MSG_PUB.Add_Exc_Msg
190 ( p_error_text => 'pricing_attribute2 is null' );
191 */
192 -- Fecth driving on item_id
193
194 SELECT /*+ ordered use_nl(OELST OELIN)
195 index(OELST SO_PRICE_LISTS_U1)
196 index(OELIN SO_PRICE_LIST_LINES_N1) */
197 OELST.ROUNDING_FACTOR
198 , OELIN.METHOD_CODE
199 , OELIN.LIST_PRICE
200 INTO
201 l_rounding_factor
202 , l_prc_method_code_out
203 , l_list_price
204 FROM SO_PRICE_LISTS OELST
205 , SO_PRICE_LIST_LINES OELIN
206 WHERE OELIN.INVENTORY_ITEM_ID = p_inventory_item_id
207 AND OELIN.UNIT_CODE = p_unit_code
208 AND OELIN.METHOD_CODE =
209 NVL( l_prc_method_code, OELIN.METHOD_CODE )
210 AND TRUNC(SYSDATE)
211 BETWEEN NVL( OELIN.START_DATE_ACTIVE, TRUNC(SYSDATE) )
212 AND NVL( OELIN.END_DATE_ACTIVE, TRUNC(SYSDATE) )
213 AND NVL( OELIN.PRICING_ATTRIBUTE1, ' ' ) =
214 NVL( p_pricing_attribute1, ' ' )
215 AND NVL( OELIN.PRICING_ATTRIBUTE2, ' ' ) =
216 NVL( p_pricing_attribute2, ' ' )
217 AND NVL( OELIN.PRICING_ATTRIBUTE3, ' ' ) =
218 NVL( p_pricing_attribute3, ' ' )
219 AND NVL( OELIN.PRICING_ATTRIBUTE4, ' ' ) =
220 NVL( p_pricing_attribute4, ' ' )
221 AND NVL( OELIN.PRICING_ATTRIBUTE5, ' ' ) =
222 NVL( p_pricing_attribute5, ' ' )
223 AND NVL( OELIN.PRICING_ATTRIBUTE6, ' ' ) =
224 NVL( p_pricing_attribute6, ' ' )
225 AND NVL( OELIN.PRICING_ATTRIBUTE7, ' ' ) =
226 NVL( p_pricing_attribute7, ' ' )
227 AND NVL( OELIN.PRICING_ATTRIBUTE8, ' ' ) =
228 NVL( p_pricing_attribute8, ' ' )
229 AND NVL( OELIN.PRICING_ATTRIBUTE9, ' ' ) =
230 NVL( p_pricing_attribute9, ' ' )
231 AND NVL( OELIN.PRICING_ATTRIBUTE10, ' ' ) =
232 NVL( p_pricing_attribute10, ' ' )
233 AND NVL( OELIN.PRICING_ATTRIBUTE11, ' ' ) =
234 NVL( p_pricing_attribute11, ' ' )
235 AND NVL( OELIN.PRICING_ATTRIBUTE12, ' ' ) =
236 NVL( p_pricing_attribute12, ' ' )
237 AND NVL( OELIN.PRICING_ATTRIBUTE13, ' ' ) =
238 NVL( p_pricing_attribute13, ' ' )
239 AND NVL( OELIN.PRICING_ATTRIBUTE14, ' ' ) =
240 NVL( p_pricing_attribute14, ' ' )
241 AND NVL( OELIN.PRICING_ATTRIBUTE15, ' ' ) =
242 NVL( p_pricing_attribute15, ' ' )
243 AND OELST.PRICE_LIST_ID = p_price_list_id
244 AND TRUNC(SYSDATE)
245 BETWEEN NVL( OELST.START_DATE_ACTIVE, TRUNC(SYSDATE) )
246 AND NVL( OELST.END_DATE_ACTIVE, TRUNC(SYSDATE) )
247 AND OELST.PRICE_LIST_ID = OELIN.PRICE_LIST_ID
248 AND ROWNUM = 1;
249
250 ELSE
251
252 -- Fetch driving on p_pricing_attribute2
253
254 SELECT /*+ ordered use_nl(OELST OELIN)
255 index(OELST SO_PRICE_LISTS_U1)
256 index(OELIN SO_PRICE_LIST_LINES_N1) */
257 OELST.ROUNDING_FACTOR
258 , OELIN.METHOD_CODE
259 , OELIN.LIST_PRICE
260 INTO
261 l_rounding_factor
262 , l_prc_method_code_out
263 , l_list_price
264 FROM SO_PRICE_LISTS OELST
265 , SO_PRICE_LIST_LINES OELIN
266 WHERE OELIN.INVENTORY_ITEM_ID = p_inventory_item_id
267 AND OELIN.UNIT_CODE = p_unit_code
268 AND OELIN.METHOD_CODE =
269 NVL( l_prc_method_code, OELIN.METHOD_CODE )
270 AND TRUNC(SYSDATE)
271 BETWEEN NVL( OELIN.START_DATE_ACTIVE, TRUNC(SYSDATE) )
272 AND NVL( OELIN.END_DATE_ACTIVE, TRUNC(SYSDATE) )
273 AND NVL( OELIN.PRICING_ATTRIBUTE1, ' ' ) =
274 NVL( p_pricing_attribute1, ' ' )
275 AND OELIN.PRICING_ATTRIBUTE2 = p_pricing_attribute2
276 AND NVL( OELIN.PRICING_ATTRIBUTE3, ' ' ) =
277 NVL( p_pricing_attribute3, ' ' )
278 AND NVL( OELIN.PRICING_ATTRIBUTE4, ' ' ) =
279 NVL( p_pricing_attribute4, ' ' )
280 AND NVL( OELIN.PRICING_ATTRIBUTE5, ' ' ) =
281 NVL( p_pricing_attribute5, ' ' )
282 AND NVL( OELIN.PRICING_ATTRIBUTE6, ' ' ) =
283 NVL( p_pricing_attribute6, ' ' )
284 AND NVL( OELIN.PRICING_ATTRIBUTE7, ' ' ) =
285 NVL( p_pricing_attribute7, ' ' )
286 AND NVL( OELIN.PRICING_ATTRIBUTE8, ' ' ) =
287 NVL( p_pricing_attribute8, ' ' )
288 AND NVL( OELIN.PRICING_ATTRIBUTE9, ' ' ) =
289 NVL( p_pricing_attribute9, ' ' )
290 AND NVL( OELIN.PRICING_ATTRIBUTE10, ' ' ) =
291 NVL( p_pricing_attribute10, ' ' )
292 AND NVL( OELIN.PRICING_ATTRIBUTE11, ' ' ) =
293 NVL( p_pricing_attribute11, ' ' )
294 AND NVL( OELIN.PRICING_ATTRIBUTE12, ' ' ) =
295 NVL( p_pricing_attribute12, ' ' )
296 AND NVL( OELIN.PRICING_ATTRIBUTE13, ' ' ) =
297 NVL( p_pricing_attribute13, ' ' )
298 AND NVL( OELIN.PRICING_ATTRIBUTE14, ' ' ) =
299 NVL( p_pricing_attribute14, ' ' )
300 AND NVL( OELIN.PRICING_ATTRIBUTE15, ' ' ) =
301 NVL( p_pricing_attribute15, ' ' )
302 AND OELST.PRICE_LIST_ID = p_price_list_id
303 AND TRUNC(SYSDATE)
304 BETWEEN NVL( OELST.START_DATE_ACTIVE, TRUNC(SYSDATE) )
305 AND NVL( OELST.END_DATE_ACTIVE, TRUNC(SYSDATE) )
306 AND OELST.PRICE_LIST_ID = OELIN.PRICE_LIST_ID
307 AND ROWNUM = 1;
308
309 END IF;
310
311 IF l_list_price IS NULL THEN
312
313 -- No list price found, clear OUT parameters.
314
315 p_price_list_id_out := NULL;
316 p_prc_method_code_out := NULL;
317 p_list_price := NULL;
318 p_list_percent := NULL;
319 p_rounding_factor := NULL;
320
321 ELSE
322
323 -- Debug info
324 /*
325 FND_MSG_PUB.Add_Exc_Msg
326 ( p_error_text => 'list price is not null - '||
327 ' list_price = '||l_list_price||
328 ' l_prc_method_code = '||l_prc_method_code_out||
329 ' l_rounding_factor = '||l_rounding_factor
330 );
331 */
332 -- Calculate list price.
333
334 IF l_prc_method_code_out = G_PRC_METHOD_AMOUNT THEN
335
336 l_list_price := ROUND ( l_list_price , - l_rounding_factor );
337 l_list_percent := NULL ;
338
339 ELSIF l_prc_method_code_out = G_PRC_METHOD_PERCENT THEN
340
341 -- List percent is the selected list price
342
343 l_list_percent := l_list_price ;
344
345 IF p_base_price IS NULL
346 THEN
347
348 -- No base price
349
350 l_list_price := NULL ;
351
352 ELSE
353
354 l_list_price := l_list_percent * p_base_price / 100 ;
355
356 IF p_item_type_code = G_PRC_ITEM_SERVICE THEN
357
358 l_list_price := l_list_price * p_service_duration ;
359
360 END IF;
361
362 l_list_price := ROUND ( l_list_price , l_rounding_factor );
363
364 END IF;
365
366 ELSE
367
368 -- Unexpected error, invalid pricing method
369
370 IF FND_MSG_PUB.Check_Msg_Level (
371 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
372 THEN
373
374 FND_MSG_PUB.Add_Exc_Msg
375 ( G_PKG_NAME ,
376 l_api_name ,
377 'Invalid pricing method ='||l_prc_method_code_out
378 );
379
380 END IF;
381
382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
383
384 END IF; -- prc_method_code
385
386 p_price_list_id_out := p_price_list_id ;
387 p_prc_method_code_out := l_prc_method_code_out ;
388 p_list_price := l_list_price ;
389 p_list_percent := l_list_percent ;
390 p_rounding_factor := l_rounding_factor ;
391
392 END IF; -- There is a list price
393
394 EXCEPTION
395
396 WHEN NO_DATA_FOUND THEN
397
398 -- Debug info
399 /*
400 FND_MSG_PUB.Add_Exc_Msg
401 ( p_error_text => 'Primary fetch not successful' );
402 */
403 -- Check if the maximum number of attempts has been
404 -- exceeded. When l_fetch attempts is 1 this means there
405 -- should be no more fetch attempts, else, look for a
406 -- secondary list.
407
408 IF l_fetch_attempts > 1 THEN
409
410 l_fetch_attempts := l_fetch_attempts - 1;
411
412 -- Get secondary_price_list_id
413
414 l_price_list_id := Get_Sec_Price_List ( p_price_list_id );
415
416 IF l_price_list_id IS NOT NULL THEN
417
418 -- Call Fetch_List_Price using the sec list.
419
420 Fetch_List_Price
421 ( p_api_version_number ,
422 FND_API.G_FALSE ,
423 FND_API.G_VALID_LEVEL_NONE ,
424 l_return_status ,
425 p_msg_count ,
426 p_msg_data ,
427 l_price_list_id ,
428 p_inventory_item_id ,
429 p_unit_code ,
430 p_service_duration ,
431 p_item_type_code ,
432 p_prc_method_code ,
433 p_pricing_attribute1 ,
434 p_pricing_attribute2 ,
435 p_pricing_attribute3 ,
436 p_pricing_attribute4 ,
437 p_pricing_attribute5 ,
438 p_pricing_attribute6 ,
439 p_pricing_attribute7 ,
440 p_pricing_attribute8 ,
441 p_pricing_attribute9 ,
442 p_pricing_attribute10 ,
443 p_pricing_attribute11 ,
444 p_pricing_attribute12 ,
445 p_pricing_attribute13 ,
446 p_pricing_attribute14 ,
447 p_pricing_attribute15 ,
448 p_base_price ,
449 l_fetch_attempts ,
450 p_price_list_id_out ,
451 p_prc_method_code_out ,
452 l_list_price ,
453 p_list_percent ,
454 p_rounding_factor
455 );
456
457 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
458
459 -- Unexpected error, abort processing
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461
462 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
463
464 -- Error, abort processing
465 RAISE FND_API.G_EXC_ERROR;
466
467 ELSE
468
469 -- Set p_list_price. We don't receive the
470 -- list price in p_list_price because we need
471 -- to check its value after the call.
472
473 p_list_price := l_list_price ;
474
475 END IF;
476
477 END IF; -- There was a secondary price list.
478
479 END IF; -- fetch_attempts > 1
480
481 WHEN OTHERS THEN
482
483 -- Unexpected error
484
485 IF FND_MSG_PUB.Check_Msg_Level(
486 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
487 THEN
488
489 FND_MSG_PUB.Add_Exc_Msg
490 ( G_PKG_NAME ,
491 l_api_name
492 );
493
494 END IF;
495
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497
498 END; -- BEGIN select list price block.
499
500 -- At this point, All processing is done, and all the secondary
501 -- fetches have been performed.
502
503 -- If list_price is NULL and the fetch level =1 meaning that this
504 -- is the execution coresponding to the primary fetch. Then add an
505 -- informational message to inform the caller that the item was
506 -- not found o the price list.
507
508 -- Debug info
509 /*
510 FND_MSG_PUB.Add_Exc_Msg
511 ( p_error_text => 'End of Fetch_List_Price - '||
512 ' l_list_price = '||l_list_price||
513 ' G_Fetch_Level = '||G_Fetch_Level
514 );
515 */
516
517 IF l_list_price IS NULL AND
518 G_Fetch_Level = 1
519 THEN
520
521 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
522 THEN
523
524 FND_MESSAGE.SET_NAME('OE','OE_PRC_NO_LIST_PRICE');
525 FND_MESSAGE.SET_TOKEN('PRICE_LIST', Get_Price_List_Name
526 (p_price_list_id) );
527 FND_MESSAGE.SET_TOKEN('ITEM', Get_Item_Description
528 (p_inventory_item_id) );
529 FND_MESSAGE.SET_TOKEN('UNTI',Get_Unit_Name (p_unit_code ));
530 FND_MSG_PUB.Add;
531
532 END IF;
533
534 END IF;
535
536 -- Decement G_Fetch_Level
537
538 G_Fetch_Level := G_Fetch_Level - 1;
539
540 -- Get message count and if 1, return message data
541
542 FND_MSG_PUB.Count_And_Get
543 ( p_count => p_msg_count ,
544 p_data => p_msg_data
545 );
546
547
548 EXCEPTION
549
550 WHEN FND_API.G_EXC_ERROR THEN
551
552 p_return_status := FND_API.G_RET_STS_ERROR;
553
554 -- Get message count and if 1, return message data
555
556 FND_MSG_PUB.Count_And_Get
557 (p_count => p_msg_count,
558 p_data => p_msg_data
559 );
560
561 -- Decement G_Fetch_Level
562
563 G_Fetch_Level := G_Fetch_Level - 1;
564
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566
567 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
568
569 -- Get message count and if 1, return message data
570
571 FND_MSG_PUB.Count_And_Get
572 (p_count => p_msg_count,
573 p_data => p_msg_data
574 );
575
576 -- Decement G_Fetch_Level
577
578 G_Fetch_Level := G_Fetch_Level - 1;
579
580 WHEN OTHERS THEN
581
582 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
583
584 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
585 FND_MSG_PUB.Add_Exc_Msg
586 ( G_PKG_NAME ,
587 l_api_name
588 );
589 END IF;
590
591 -- Get message count and if 1, return message data
592
593 FND_MSG_PUB.Count_And_Get
594 ( p_count => p_msg_count,
595 p_data => p_msg_data
596 );
597
598 -- Decement G_Fetch_Level
599
600 G_Fetch_Level := G_Fetch_Level - 1;
601
602 END; -- Fetch_List_Price
603
604 -- Utility function called by Fetch)List_Price API.
605
606 FUNCTION Get_Sec_Price_List
607 ( p_price_list_id IN NUMBER )
608 RETURN NUMBER
609 IS
610 l_sec_price_list_id NUMBER := NULL;
611 BEGIN
612
613 IF p_price_list_id IS NULL THEN
614 RETURN NULL;
615 END IF;
616
617 SELECT SECONDARY_PRICE_LIST_ID
618 INTO l_sec_price_list_id
619 FROM SO_PRICE_LISTS
620 WHERE PRICE_LIST_ID = p_price_list_id;
621
622 RETURN l_sec_price_list_id;
623
624 EXCEPTION
625
626 WHEN OTHERS THEN
627
628 FND_MSG_PUB.Add_Exc_Msg
629 ( G_PKG_NAME ,
630 'Get_Sec_Price_List - p_price_list_id = '||p_price_list_id
631 );
632
633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634
635 END Get_Sec_Price_List;
636
637 FUNCTION Get_Price_List_Name
638 ( p_price_list_id IN NUMBER )
639 RETURN VARCHAR2
640 IS
641 l_name VARCHAR2(80) := NULL;
642 BEGIN
643
644 IF p_price_list_id IS NULL THEN
645 RETURN NULL;
646 END IF;
647
648 SELECT NAME
649 INTO l_name
650 FROM SO_PRICE_LISTS
651 WHERE PRICE_LIST_ID = p_price_list_id;
652
653 RETURN l_name;
654
655 EXCEPTION
656
657 WHEN OTHERS THEN
658
659 FND_MSG_PUB.Add_Exc_Msg
660 ( G_PKG_NAME ,
661 'Get_Price_List_Name - p_price_list_id = '||p_price_list_id
662 );
663
664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
665
666 END Get_Price_List_Name;
667
668 FUNCTION Get_Item_Description
669 ( p_item_id IN NUMBER )
670 RETURN VARCHAR2
671 IS
672 l_desc VARCHAR2(240) := NULL;
673 l_org_id NUMBER := NULL;
674 BEGIN
675
676 l_org_id := FND_PROFILE.VALUE ('SO_ORGANIZATION_ID');
677
678 IF p_item_id IS NULL OR
679 l_org_id IS NULL
680 THEN
681 RETURN NULL;
682 END IF;
683
684 SELECT DESCRIPTION
685 INTO l_desc
686 FROM MTL_SYSTEM_ITEMS
687 WHERE INVENTORY_ITEM_ID = p_item_id
688 AND ORGANIZATION_ID = l_org_id;
689
690 RETURN l_desc;
691
692 EXCEPTION
693
694 WHEN OTHERS THEN
695
696 FND_MSG_PUB.Add_Exc_Msg
697 ( G_PKG_NAME ,
698 'Get_Item_Description - p_item_id = '||p_item_id||
699 ' org_id ='||l_org_id
700 );
701
702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
703
704 END Get_Item_Description;
705
706 FUNCTION Get_Unit_Name
707 ( p_unit_code IN VARCHAR2 )
708 RETURN VARCHAR2
709 IS
710 l_name VARCHAR2(80) := NULL;
711 BEGIN
712
713 IF p_unit_code IS NULL THEN
714 RETURN NULL;
715 END IF;
716
717 SELECT UNIT_OF_MEASURE
718 INTO l_name
719 FROM MTL_UNITS_OF_MEASURE
720 WHERE UOM_CODE = p_unit_code;
721
722 RETURN l_name;
723
724 EXCEPTION
725
726 WHEN OTHERS THEN
727
728 FND_MSG_PUB.Add_Exc_Msg
729 ( G_PKG_NAME ,
730 'Get_Unit_Name - p_unit_code = '||p_unit_code
731 );
732
733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
734
735 END Get_Unit_Name;
736
737 END OE_Price_List_PVT;
738