[Home] [Help]
PACKAGE BODY: APPS.OE_UPGRADE_MISC_NEW
Source
1 PACKAGE BODY OE_Upgrade_Misc_New AS
2 /* $Header: OEXNUMSB.pls 120.0 2005/06/01 02:21:56 appldev noship $ */
3
4 -- Procedure to convert the passed in Freight Amount to the specified
5 -- currency.
6
7 PROCEDURE CONVERT_CURRENCY
8 ( p_freight_amount IN NUMBER
9 , p_from_currency IN VARCHAR2
10 , p_to_currency IN VARCHAR2
11 , p_conversion_date IN DATE
12 , p_conversion_rate IN NUMBER
13 , p_conversion_type IN VARCHAR2
14 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
15 , x_freight_amount OUT NOCOPY /* file.sql.39 change */ NUMBER
16 )IS
17 l_conversion_type VARCHAR2(30);
18 l_rate_exists VARCHAR2(1);
19 l_converted_amount NUMBER;
20 l_max_roll_days NUMBER;
21 l_denominator NUMBER;
22 l_numerator NUMBER;
23 l_rate NUMBER;
24 No_User_Defined_Rate EXCEPTION;
25 --
26 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
27 --
28 BEGIN
29 x_return_status := 'S';
30 l_max_roll_days := 300;
31 l_conversion_type := NVL(p_conversion_type, 'Corporate');
32
33 IF (GL_CURRENCY_API.Is_Fixed_Rate( p_from_currency,
34 p_to_currency, p_conversion_date) = 'Y')
35 THEN
36 x_freight_amount := GL_CURRENCY_API.convert_amount(
37 p_from_currency,
38 p_to_currency,
39 NVL(p_conversion_date,sysdate),
40 l_conversion_type,
41 p_freight_amount
42 );
43 ELSIF (l_conversion_type = 'User')
44 THEN
45 IF (p_conversion_rate IS NOT NULL) THEN
46 x_freight_amount := p_freight_amount * p_conversion_rate;
47 ELSE
48 RAISE No_User_Defined_Rate;
49 END IF;
50 ELSE
51 l_rate_exists := GL_CURRENCY_API.Rate_Exists(
52 x_from_currency => p_from_currency,
53 x_to_currency => p_to_currency,
54 x_conversion_date => NVL(p_conversion_date,sysdate),
55 x_conversion_type => l_conversion_type
56 );
57 IF (l_rate_exists = 'Y') THEN
58 x_freight_amount := GL_CURRENCY_API.convert_amount(
59 p_from_currency,
60 p_to_currency,
61 NVL(p_conversion_date,sysdate),
62 l_conversion_type,
63 p_freight_amount
64 );
65 ELSE
66 GL_CURRENCY_API.convert_closest_amount(
67 x_from_currency => p_from_currency,
68 x_to_currency => p_to_currency,
69 x_conversion_date => NVL(p_conversion_date,sysdate),
70 x_conversion_type => l_conversion_type,
71 x_user_rate => p_conversion_rate,
72 x_amount => p_freight_amount,
73 x_max_roll_days => l_max_roll_days,
74 x_converted_amount=> l_converted_amount,
75 x_denominator => l_denominator,
76 x_numerator => l_numerator,
77 x_rate => l_rate);
78 x_freight_amount := l_converted_amount;
79 END IF;
80
81 END IF;
82 EXCEPTION
83 WHEN OTHERS THEN
84 x_return_status := 'E';
85
86 END CONVERT_CURRENCY;
87
88
89 PROCEDURE CREATE_FREIGHT_RECORD
90 (
91 p_header_id IN NUMBER
92 , p_line_id IN NUMBER
93 , p_freight_charge_id IN NUMBER
94 , p_currency_code IN VARCHAR2
95 , p_charge_type_code IN VARCHAR2
96 , p_adjusted_amount IN VARCHAR2
97 , p_creation_date IN DATE
98 , p_created_by IN NUMBER
99 , p_last_update_date IN DATE
100 , p_last_updated_by IN NUMBER
101 , p_last_update_login IN NUMBER
102 , p_context IN VARCHAR2
103 , p_attribute1 IN VARCHAR2
104 , p_attribute2 IN VARCHAR2
105 , p_attribute3 IN VARCHAR2
106 , p_attribute4 IN VARCHAR2
107 , p_attribute5 IN VARCHAR2
108 , p_attribute6 IN VARCHAR2
109 , p_attribute7 IN VARCHAR2
110 , p_attribute8 IN VARCHAR2
111 , p_attribute9 IN VARCHAR2
112 , p_attribute10 IN VARCHAR2
113 , p_attribute11 IN VARCHAR2
114 , p_attribute12 IN VARCHAR2
115 , p_attribute13 IN VARCHAR2
116 , p_attribute14 IN VARCHAR2
117 , p_attribute15 IN VARCHAR2
118 , p_ac_context IN VARCHAR2
119 , p_ac_attribute1 IN VARCHAR2
120 , p_ac_attribute2 IN VARCHAR2
121 , p_ac_attribute3 IN VARCHAR2
122 , p_ac_attribute4 IN VARCHAR2
123 , p_ac_attribute5 IN VARCHAR2
124 , p_ac_attribute6 IN VARCHAR2
125 , p_ac_attribute7 IN VARCHAR2
126 , p_ac_attribute8 IN VARCHAR2
127 , p_ac_attribute9 IN VARCHAR2
128 , p_ac_attribute10 IN VARCHAR2
129 , p_ac_attribute11 IN VARCHAR2
130 , p_ac_attribute12 IN VARCHAR2
131 , p_ac_attribute13 IN VARCHAR2
132 , p_ac_attribute14 IN VARCHAR2
133 , p_ac_attribute15 IN VARCHAR2
134 , p_invoice_status IN VARCHAR2
135 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
136 ) IS
137
138 l_price_adjustment_id NUMBER;
139 l_list_header_id NUMBER;
140 l_list_line_id NUMBER;
141 l_count NUMBER;
142 l_pricing_phase_id NUMBER;
143 l_pricing_group_sequence NUMBER;
144
145 ERROR_IN_GETTING_SETUP EXCEPTION;
146 ERROR_IN_MAPPING_FREIGHT EXCEPTION;
147
148 --
149 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
150 --
151 BEGIN
152 x_return_status := 'S';
153 l_count := 0;
154 SELECT count(*)
155 INTO l_count
156 FROM OE_PRICE_ADJUSTMENTS
157 WHERE header_id = p_header_id
158 AND NVL(line_id,-99) = NVL(p_line_id,-99)
159 AND cost_id = p_freight_charge_id;
160
161 IF l_count = 0 THEN
162
163 -- Get the Setup details for the Freight record.
164 BEGIN
165 SELECT DISTINCT b.list_header_id,
166 a.list_line_id,
167 a.pricing_phase_id,
168 a.pricing_group_sequence
169 INTO l_list_header_id,
170 l_list_line_id,
171 l_pricing_phase_id,
172 l_pricing_group_sequence
173 FROM qp_list_lines a,
174 qp_list_headers_b b,
175 qp_list_headers_tl tl
176 WHERE b.currency_code = p_currency_code
177 AND tl.list_header_id = b.list_header_id
178 AND b.list_type_code = 'CHARGES'
179 AND tl.name = 'FREIGHTUPGIN'||p_currency_code
180 AND b.list_header_id = a.list_header_id
181 AND a.list_line_type_code = 'FREIGHT_CHARGE'
182 AND a.charge_type_code = p_charge_type_code
183 AND a.modifier_level_code = 'ORDER'
184 AND ROWNUM = 1;
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 raise ERROR_IN_GETTING_SETUP;
188 END;
189
190
191 -- Get the price_adjustment_id
192 SELECT OE_PRICE_ADJUSTMENTS_S.nextval INTO l_price_adjustment_id FROM DUAL;
193
194 INSERT INTO OE_PRICE_ADJUSTMENTS
195 (
196 PRICE_ADJUSTMENT_ID
197 , CREATION_DATE
198 , CREATED_BY
199 , LAST_UPDATE_DATE
200 , LAST_UPDATED_BY
201 , LAST_UPDATE_LOGIN
202 , HEADER_ID
203 , AUTOMATIC_FLAG
204 , LINE_ID
205 , CONTEXT
206 , ATTRIBUTE1
207 , ATTRIBUTE2
208 , ATTRIBUTE3
209 , ATTRIBUTE4
210 , ATTRIBUTE5
211 , ATTRIBUTE6
212 , ATTRIBUTE7
213 , ATTRIBUTE8
214 , ATTRIBUTE9
215 , ATTRIBUTE10
216 , ATTRIBUTE11
217 , ATTRIBUTE12
218 , ATTRIBUTE13
219 , ATTRIBUTE14
220 , ATTRIBUTE15
221 , LIST_HEADER_ID
222 , LIST_LINE_ID
223 , LIST_LINE_TYPE_CODE
224 , UPDATED_FLAG
225 , UPDATE_ALLOWED
226 , APPLIED_FLAG
227 , CHANGE_REASON_CODE
228 , CHANGE_REASON_TEXT
229 , OPERAND
230 , ARITHMETIC_OPERATOR
231 , INVOICED_FLAG
232 , ESTIMATED_FLAG
233 , INC_IN_SALES_PERFORMANCE
234 , SPLIT_ACTION_CODE
235 , ADJUSTED_AMOUNT
236 , PRICING_PHASE_ID
237 , PRICING_GROUP_SEQUENCE
238 , CHARGE_TYPE_CODE
239 , CHARGE_SUBTYPE_CODE
240 , CREDIT_OR_CHARGE_FLAG
241 , INCLUDE_ON_RETURNS_FLAG
242 , COST_ID
243 , AC_CONTEXT
244 , AC_ATTRIBUTE1
245 , AC_ATTRIBUTE2
246 , AC_ATTRIBUTE3
247 , AC_ATTRIBUTE4
248 , AC_ATTRIBUTE5
249 , AC_ATTRIBUTE6
250 , AC_ATTRIBUTE7
251 , AC_ATTRIBUTE8
252 , AC_ATTRIBUTE9
253 , AC_ATTRIBUTE10
254 , AC_ATTRIBUTE11
255 , AC_ATTRIBUTE12
256 , AC_ATTRIBUTE13
257 , AC_ATTRIBUTE14
258 , AC_ATTRIBUTE15
259
260 )
261 VALUES
262 (
263 l_price_adjustment_id
264 , p_creation_date
265 , p_created_by
266 , p_LAST_UPDATE_DATE
267 , p_LAST_UPDATED_BY
268 , p_LAST_UPDATE_LOGIN
269 , p_HEADER_ID
270 , 'Y'
271 , p_LINE_ID
272 , p_CONTEXT
273 , p_ATTRIBUTE1
274 , p_ATTRIBUTE2
275 , p_ATTRIBUTE3
276 , p_ATTRIBUTE4
277 , p_ATTRIBUTE5
278 , p_ATTRIBUTE6
279 , p_ATTRIBUTE7
280 , p_ATTRIBUTE8
281 , p_ATTRIBUTE9
282 , p_ATTRIBUTE10
283 , p_ATTRIBUTE11
284 , p_ATTRIBUTE12
285 , p_ATTRIBUTE13
286 , p_ATTRIBUTE14
287 , p_ATTRIBUTE15
288 , l_LIST_HEADER_ID
289 , l_LIST_LINE_ID
290 , 'FREIGHT_CHARGE'
291 , 'Y'
292 , 'Y'
293 , 'Y'
294 , 'MISC'
295 , 'Upgraded Freight Charge'
296 , p_adjusted_amount
297 , 'LUMPSUM'
298 , DECODE(p_invoice_status,NULL,'N','Y')
299 , 'N'
300 , NULL
301 , NULL
302 , p_adjusted_amount
303 , l_PRICING_PHASE_ID
304 , l_PRICING_GROUP_SEQUENCE
305 , P_CHARGE_TYPE_CODE
306 , NULL
307 , 'D'
308 , 'Y'
309 , p_freight_charge_id
310 , p_AC_CONTEXT
311 , p_AC_ATTRIBUTE1
312 , p_AC_ATTRIBUTE2
313 , p_AC_ATTRIBUTE3
314 , p_AC_ATTRIBUTE4
315 , p_AC_ATTRIBUTE5
316 , p_AC_ATTRIBUTE6
317 , p_AC_ATTRIBUTE7
318 , p_AC_ATTRIBUTE8
319 , p_AC_ATTRIBUTE9
320 , p_AC_ATTRIBUTE10
321 , p_AC_ATTRIBUTE11
322 , p_AC_ATTRIBUTE12
323 , p_AC_ATTRIBUTE13
324 , p_AC_ATTRIBUTE14
325 , p_AC_ATTRIBUTE15
326 );
327 END IF;
328 EXCEPTION
329 WHEN ERROR_IN_GETTING_SETUP THEN
330 x_return_status := 'N';
331 WHEN ERROR_IN_MAPPING_FREIGHT THEN
332 x_return_status := 'C';
333 WHEN OTHERS THEN
334 x_return_status := 'E';
335
336 END CREATE_FREIGHT_RECORD;
337
338 PROCEDURE Round_Amount(
339 p_Amount IN NUMBER
340 , p_Currency_Code IN VARCHAR2
341 , x_Round_Amount OUT NOCOPY /* file.sql.39 change */ NUMBER
342 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
343 )
344 IS
345 l_precision NUMBER;
346 l_minimum_accountable_unit NUMBER;
347 --
348 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
349 --
350 BEGIN
351 x_return_status := 'S';
352 SELECT precision, nvl(minimum_accountable_unit, 0)
353 INTO l_precision, l_minimum_accountable_unit
354 FROM fnd_currencies
355 WHERE currency_code = p_Currency_Code;
356
357 x_Round_Amount := Round(p_Amount, l_precision);
358 EXCEPTION
359 WHEN OTHERS THEN
360 x_return_status := 'E';
361 END Round_Amount;
362
363 FUNCTION GET_SOB_CURRENCY(p_org_id IN NUMBER)
364 RETURN VARCHAR2
365 IS
366 l_curr_code VARCHAR2(15);
367 l_AR_Sys_Param_Rec AR_SYSTEM_PARAMETERS_ALL%ROWTYPE;
368 l_sob_id NUMBER;
369
370 --
371 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
372 --
373 BEGIN
374
375 IF oe_code_control.code_release_level < '110510' THEN
376 SELECT b.currency_code
377 INTO l_curr_code
378 FROM ar_system_parameters_all a,
379 GL_SETS_OF_BOOKS b
380 WHERE NVL(a.org_id,-99) = NVL(p_org_id,-99)
381 AND a.set_of_books_id = b.set_of_books_id
382 AND ROWNUM =1;
383 RETURN l_curr_code;
384 ELSE
385 l_AR_Sys_Param_Rec := OE_Sys_Parameters_Pvt.Get_AR_Sys_Params(p_org_id);
386 l_sob_id := l_AR_Sys_Param_Rec.set_of_books_id;
387 SELECT b.currency_code
388 INTO l_curr_code
389 FROM GL_SETS_OF_BOOKS b
390 WHERE b.set_of_books_id = l_sob_id
391 AND ROWNUM =1;
392 RETURN l_curr_code;
393 END IF;
394
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 RETURN NULL;
399 END GET_SOB_CURRENCY;
400
401 END OE_Upgrade_Misc_New;