1 package body CTO_PUBLIC_UTILITY_PK as
2 /* $Header: CTOPUTLB.pls 115.1 2003/03/28 01:56:24 sbhaskar noship $*/
3
4 /*----------------------------------------------------------------------------+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA
6 | All rights reserved.
7 | Oracle Manufacturing
8 |
9 | FILE NAME : CTOPUTLS.pls
10 |
11 | DESCRIPTION : This is a PUBLIC package used primarily for having APIs which are
12 | called from other products. We decided to keep these APIs in
13 | seperate file to reduce dependencies.
14 |
15 | HISTORY : Created on 23-DEC-2002 by Shashi Bhaskaran
16 |
17 +-----------------------------------------------------------------------------*/
18
19 /*************************************************************************************
20
21 Bugfix 2695239
22
23 Input Parameters
24 ----------------
25 p_config_line_id : Line Id of the configuration
26 p_model_line_id : Line Id of the ATO model
27 p_quantity : Quantity for which total selling price is to be calculated
28
29
30 Output Parameters
31 -----------------
32 x_unit_selling_price : Unit Selling Price of the model. Calculated.
33 x_qty_selling_price : Total Selling Price of the model for quantity 'p_quantity'. Calculated.
34 x_currency_code : Currencty Code of the order line.
35
36 x_return_status : Return status of the API. Returns FND_API.G_RET_STS_ERROR if others exception is raised.
37 Otherwise G_RET_STS_SUCCESS
38 x_error_code : Return error code. 0 if success. 1 if error.
39
40 Note:
41 In case of hard-error, x_error_code will be set to 1
42 and x_return_status will be set to FND_API.G_RET_STS_ERROR
43 In case of soft-error, x_error_code will be set to 1
44 but x_return_status will be G_RET_STS_SUCCESS
45
46 *************************************************************************************/
47
48
49 procedure get_selling_price (
50 p_config_line_id IN NUMBER
51 , p_model_line_id IN NUMBER DEFAULT NULL
52 , p_quantity IN NUMBER DEFAULT 0
53 , x_unit_selling_price OUT NOCOPY NUMBER
54 , x_qty_selling_price OUT NOCOPY NUMBER
55 , x_currency_code OUT NOCOPY VARCHAR2
56 , x_return_status OUT NOCOPY VARCHAR2
57 , x_error_code OUT NOCOPY NUMBER)
58 is
59 config_ato_line_id NUMBER;
60 config_ordered_qty NUMBER;
61 begin
62
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64 x_error_code := 0;
65
66 x_unit_selling_price := 0;
67 x_qty_selling_price := 0;
68
69 if p_config_line_id is not null then
70 select ato_line_id, ordered_quantity
71 into config_ato_line_id, config_ordered_qty
72 from oe_order_lines_all
73 where line_id = p_config_line_id;
74 end if;
75
76 if config_ordered_qty = 0 then
77 x_unit_selling_price := 0;
78 x_qty_selling_price := 0;
79 return;
80 end if;
81 --
82 -- If config_line_id is passed, then, we get the corresponding ATO line id and sum up the
83 -- selling price. Basically, we ignore the p_model_line_id value.
84 --
85
86 -- Bug 2862057 : Modified the logic to determine the right unit_selling_price.
87
88 select nvl( sum(l.UNIT_SELLING_PRICE * nvl(ORDERED_QUANTITY,0)) / config_ordered_qty , 0),
89 nvl((sum(l.UNIT_SELLING_PRICE * nvl(ORDERED_QUANTITY,0)) / config_ordered_qty) * p_quantity, 0),
90 max(h.TRANSACTIONAL_CURR_CODE)
91 into x_unit_selling_price,
92 x_qty_selling_price,
93 x_currency_code
94 from oe_order_lines_all l,
95 oe_order_headers_all h
96 where l.ato_line_id = decode(p_config_line_id, null, p_model_line_id, config_ato_line_id)
97 and l.header_id = h.header_id;
98
99
100 exception
101 when no_data_found then
102 -- We will not set the return_status since it is a soft-error.
103 x_error_code := 1; -- Set the error code
104
105 when others then
106 -- We will set the return_status here since it will be a hard-error.
107 x_return_status := FND_API.G_RET_STS_ERROR;
108 x_error_code := 1; -- Set the error code
109 end;
110
111 END CTO_PUBLIC_UTILITY_PK;
[Home] [Help]