1 PACKAGE BODY OE_ADJ_PRIVILEGE AS
2 /* $Header: OEXSADJB.pls 120.0 2005/06/01 01:09:57 appldev noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ADJ_PRIVILEGE';
7
8 FUNCTION Check_Item_Category
9 ( p_inv_item_id IN NUMBER
10 , p_ent_val IN VARCHAR2
11 , p_orgid IN NUMBER
12 , p_pricing_date IN DATE
13 )
14 RETURN VARCHAR2
15 IS
16
17 l_dummy VARCHAR2(1) := 'X';
18
19 CURSOR c_check_item_category IS
20 SELECT 'Y'
21 FROM mtl_item_categories MTC,
22 mtl_default_category_sets MTDCS,
23 mtl_category_set_valid_cats MCSV,
24 mtl_categories MC
25 WHERE MTDCS.functional_area_id = 7
26 AND MTC.category_set_id = MTDCS.category_set_id
27 AND MTC.inventory_item_id = TO_CHAR( p_inv_item_id )
28 AND MCSV.category_set_id = MTC.category_set_id
29 AND MCSV.category_id = MTC.category_id
30 AND MCSV.category_id = MC.category_id
31 AND p_pricing_date < nvl(MC.disable_date, p_pricing_date+1)
32 AND MTC.organization_id = NVL(p_orgid, MTC.organization_id)
33 AND MTC.category_id = to_number(p_ent_val);
34
35 --
36 --l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
37 --
38 BEGIN
39
40 OPEN c_check_item_category;
41 FETCH c_check_item_category into l_dummy;
42 CLOSE c_check_item_category;
43
44 IF l_dummy = 'Y' then
45 RETURN('Y');
46 ELSE
47 RETURN('N');
48 END IF;
49
50 EXCEPTION
51 WHEN OTHERS THEN
52 RETURN('N');
53 END;
54
55
56 PROCEDURE Check_Manual_Discount_Priv
57 ( p_api_version_number IN NUMBER
58 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
59 , x_return_status OUT NOCOPY VARCHAR2
60
61 , x_msg_count OUT NOCOPY NUMBER
62
63 , x_msg_data OUT NOCOPY VARCHAR2
64
65 , p_order_type_id IN NUMBER := FND_API.G_MISS_NUM
66 , p_header_id IN NUMBER
67 , p_line_id IN NUMBER
68 , p_list_price IN NUMBER
69 , p_discounting_privilege IN VARCHAR2
70 , p_apply_order_adjs_flag IN VARCHAR2 := 'N'
71 , p_check_multiple_adj_flag IN VARCHAR2 := 'Y'
72 , x_adjustment_total OUT NOCOPY NUMBER
73
74 , x_price_adjustment_id OUT NOCOPY NUMBER
75
76 )
77 IS
78 l_api_version_number CONSTANT NUMBER := 1.0;
79 l_api_name CONSTANT VARCHAR2(30) :=
80 'Check_Manual_Discount_Priv';
81 l_dummy NUMBER;
82 l_adjustment_total NUMBER := 0;
83 l_price_adjustment_id NUMBER := NULL;
84
85 --
86 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
87 --
88 BEGIN
89
90 -- Standard call to check for call compatibility
91 IF NOT FND_API.Compatible_API_Call
92 (l_api_version_number,
93 p_api_version_number,
94 l_api_name,
95 G_PKG_NAME
96 )
97 THEN
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100
101
102 -- Initialize message list.
103 IF FND_API.to_Boolean(p_init_msg_list) THEN
104 OE_MSG_PUB.initialize;
105 END IF;
106
107
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 /********************************************************
111 IF p_discounting_privilege = 'NONE' THEN
112
113 fnd_message.set_name('QP', 'OE_MANDIS_DISALLOWED');
114 fnd_message.set_token('REASON', 'OE_MANDIS_NO_PRIVILEGE', TRUE);
115 OE_MSG_PUB.Add;
116
117 RAISE FND_API.G_EXC_ERROR;
118
119 ELSIF (p_list_price IS NULL
120 OR
121 p_list_price = 0)
122 THEN
123
124 fnd_message.set_name('QP', 'OE_MANDIS_DISALLOWED');
125 fnd_message.set_token('REASON',
126 'OE_MANDIS_NO_LIST_PRICE',
127 TRUE);
128 OE_MSG_PUB.Add;
129
130 RAISE FND_API.G_EXC_ERROR;
131
132
133 -- Check to make sure that the list price field is set
134 -- otherwise you cannot apply manual discounts
135 ELSIF p_list_price > 0
136 ********************************************************/
137
138 IF p_list_price > 0 THEN
139
140 -- Check if the order type for this order enforces
141 -- list prices: if it does not or if it does and you
142 -- have 'UNLIMITED' discounting ability then you can
143 -- apply manual discounts
144
145 IF p_order_type_id <> FND_API.G_MISS_NUM THEN
146
147 BEGIN
148 SELECT NULL
149 INTO L_Dummy
150 FROM oe_transaction_types
151 WHERE transaction_type_id = p_order_type_id
152 AND ((ENFORCE_LINE_PRICES_FLAG = 'Y'
153 AND
154 P_Discounting_Privilege = 'UNLIMITED')
155 OR
156 ENFORCE_LINE_PRICES_FLAG = 'N')
157 AND ROWNUM = 1;
158
159 EXCEPTION WHEN NO_DATA_FOUND THEN
160 fnd_message.set_name('ONT', 'OE_MANDIS_DISALLOWED');
161 fnd_message.set_token('REASON',
162 'OE_MANDIS_PRICES_ENFORCED',
163 TRUE);
164 OE_MSG_PUB.Add;
165 RAISE FND_API.G_EXC_ERROR;
166 END;
167 END IF;
168
169 -- Select the price adjustment id of the single manual discount
170 -- that might exist.
171 IF (p_check_multiple_adj_flag = 'Y') THEN
172
173 BEGIN
174 -- Count the number of price adjustments related to this
175 -- line which reference a manual discount
176 SELECT price_adjustment_id
177 INTO l_price_adjustment_id
178 FROM OE_PRICE_ADJUSTMENTS
179 WHERE HEADER_ID = P_HEADER_ID
180 AND LINE_ID = P_LINE_ID
181 AND NVL( AUTOMATIC_FLAG, 'N' ) = 'N'
182 AND (list_line_type_code = 'DIS'
183 OR list_line_type_code = 'SUR')
184 AND ROWNUM = 1;
185
186 x_price_adjustment_id := l_price_adjustment_id;
187 IF l_debug_level > 0 THEN
188 oe_debug_pub.add( 'OEXSADJB. ADJ_ID = '|| TO_CHAR ( L_PRICE_ADJUSTMENT_ID ) ) ;
189 END IF;
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191
192 -- now calculate the total percentage on this
193 -- order line, excluding manual discounts
194 SELECT NVL( SUM( NVL( Adjusted_amount, 0) ), 0)
195 INTO l_adjustment_total
196 FROM oe_price_adjustments
197 WHERE header_id = p_header_id
198 AND ((p_apply_order_adjs_flag = 'Y'
199 AND line_id IS NULL)
200 OR
201 (line_id = p_line_id
202 AND automatic_flag = 'Y'));
203
204 x_adjustment_total := l_adjustment_total;
205
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 x_return_status := FND_API.G_RET_STS_ERROR;
209 END;
210
211 END IF;
212 END IF;
213
214 EXCEPTION
215
216 WHEN FND_API.G_EXC_ERROR THEN
217
218 x_return_status := FND_API.G_RET_STS_ERROR;
219
220 -- Get message count and data
221 OE_MSG_PUB.Count_And_Get
222 ( p_count => x_msg_count
223 , p_data => x_msg_data
224 );
225
226
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
228
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
230
231 -- Get message count and data
232 OE_MSG_PUB.Count_And_Get
233 ( p_count => x_msg_count
234 , p_data => x_msg_data
235 );
236
237
238 WHEN OTHERS THEN
239
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
241
242 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
243 THEN
244 OE_MSG_PUB.Add_Exc_Msg
245 ( G_PKG_NAME
246 , 'Check_Manual_Discount_Priv'
247 );
248 END IF;
249
250 -- Get message count and data
251 OE_MSG_PUB.Count_And_Get
252 ( p_count => x_msg_count
253 , p_data => x_msg_data
254 );
255
256 END Check_Manual_Discount_Priv;
257
258
259 END OE_ADJ_PRIVILEGE;
[Home] [Help]