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