4
1 PACKAGE BODY PO_CORE_S2 AS
2 /* $Header: POXCOC2B.pls 120.5.12020000.2 2013/03/26 06:34:14 xueche ship $*/
3
5 /*===========================================================================
6 Private package variables
7 ===========================================================================*/
8 -- Logging/debugging
9 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_CORE_S2';
10 g_log_head CONSTANT VARCHAR2(50) :=
11 'po.plsql.' || g_pkg_name || '.';
12 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
13 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
14
15
16
17
18 /*===========================================================================
19 PROCEDURE NAME: GET_REQ_CURRENCY
20 ===========================================================================*/
21 PROCEDURE GET_REQ_CURRENCY (x_object_id IN NUMBER,
22 x_base_currency OUT NOCOPY VARCHAR2 ,
23 p_org_id IN NUMBER) IS --bug#5092574
24 x_progress VARCHAR2(3) := NULL;
25 BEGIN
26 x_progress := 10;
27 --bug#5092574 Use the doc_org_id to join with FSP to retrieve the
28 --Req Currency
29 SELECT GSB.currency_code
30 INTO x_base_currency
31 FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
32 GL_SETS_OF_BOOKS GSB
33 WHERE FSP.set_of_books_id = GSB.set_of_books_id
34 AND FSP.ORG_ID=p_org_id;
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 RAISE;
39 END GET_REQ_CURRENCY;
40
41 /*===========================================================================
42 PROCEDURE NAME: GET_CURRENCY_INFO
43 ===========================================================================*/
44 PROCEDURE GET_CURRENCY_INFO (x_currency_code IN VARCHAR2,
45 x_precision OUT NOCOPY NUMBER,
46 x_min_unit OUT NOCOPY NUMBER ) is
47 x_ext_precision NUMBER;
48 x_progress VARCHAR2(3) := NULL;
49 BEGIN
50 x_progress := 10;
51
52 fnd_currency.get_info(x_currency_code,
53 x_precision,
54 x_ext_precision,
55 x_min_unit );
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 RAISE;
60 END GET_CURRENCY_INFO;
61
62 /*===========================================================================
63 PROCEDURE NAME: GET_CURRENCY_INFO_DETAILS
64 ===========================================================================*/
65 PROCEDURE GET_CURRENCY_INFO_DETAILS (p_currency_code IN VARCHAR2,
69
66 x_precision OUT NOCOPY NUMBER,
67 x_ext_precision OUT NOCOPY NUMBER,
68 x_min_unit OUT NOCOPY NUMBER ) is
70 x_progress VARCHAR2(3) := NULL;
71 BEGIN
72 x_progress := 10;
73
74 fnd_currency.get_info(p_currency_code,
75 x_precision,
76 x_ext_precision,
77 x_min_unit );
78
79 x_ext_precision := Nvl(x_ext_precision,Nvl(x_precision,5));
80 IF x_min_unit IS NULL AND x_precision IS NULL THEN
81 x_precision := 2;
82 END IF;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 RAISE;
87 END GET_CURRENCY_INFO_DETAILS;
88
89 /*===========================================================================
90 PROCEDURE NAME: GET_PO_CURRENCY
91 ===========================================================================*/
92 PROCEDURE GET_PO_CURRENCY (x_object_id IN NUMBER,
93 x_base_currency OUT NOCOPY VARCHAR2,
94 x_po_currency OUT NOCOPY VARCHAR2) is
95 x_progress VARCHAR2(3) := NULL;
96 BEGIN
97 x_progress := 10;
98
99 SELECT GSB.currency_code,
100 POH.currency_code
101 INTO x_base_currency,
102 x_po_currency
103 FROM PO_HEADERS_ALL POH, -- Bug 3012328 (Changed to all table so that this does not fail for GA's)
104 FINANCIALS_SYSTEM_PARAMS_ALL FSP, -- Bug 5221311 Changed to all table
105 GL_SETS_OF_BOOKS GSB
106 WHERE POH.po_header_id = x_object_id
107 AND FSP.set_of_books_id = GSB.set_of_books_id
108 AND FSP.org_id = POH.org_id; --< R12 MOAC>
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 RAISE;
113 END GET_PO_CURRENCY;
114
115 --<R12 MOAC START>
116 FUNCTION get_base_currency(p_org_id po_system_parameters_all.org_id%TYPE)
117 return VARCHAR2 IS
118 x_currency_code gl_sets_of_books.currency_code%TYPE;
119 BEGIN
120
121 SELECT gsb.currency_code
122 INTO x_currency_code
123 FROM financials_system_params_all fsp,
124 gl_sets_of_books gsb
125 WHERE fsp.set_of_books_id = gsb.set_of_books_id
126 AND fsp.org_id = p_org_id;
127
128 return(x_currency_code);
129
130 END get_base_currency;
131 --<R12 MOAC END>
132 /*===========================================================================
133
134 PROCEDURE NAME: get_base_currency
135
136 ===========================================================================*/
137
138 FUNCTION get_base_currency return VARCHAR2 IS
139 x_currency_code VARCHAR2(30):= '';
140 BEGIN
141
142 SELECT GSB.currency_code
143 INTO x_currency_code
144 FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
145 GL_SETS_OF_BOOKS GSB
146 WHERE FSP.set_of_books_id = GSB.set_of_books_id;
147
148 return(x_currency_code);
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 RAISE;
153 END;
154
155 /*===========================================================================
156 PROCEDURE NAME: GET_PO_CURRENCY_INFO
157 ===========================================================================*/
158 PROCEDURE GET_PO_CURRENCY_INFO (p_po_header_id IN NUMBER,
159 x_currency_code OUT NOCOPY VARCHAR2,
160 x_curr_rate_type OUT NOCOPY VARCHAR2,
161 x_curr_rate_date OUT NOCOPY DATE,
162 x_currency_rate OUT NOCOPY NUMBER) is
163 x_progress VARCHAR2(3) := NULL;
164 BEGIN
165 x_progress := 10;
166
167 IF p_po_header_id is null THEN
168 Return;
169 END IF;
170
171 SELECT POH.currency_code,
172 POH.rate_type,
173 POH.rate_date,
174 POH.rate
175 INTO x_currency_code,
176 x_curr_rate_type,
177 x_curr_rate_date,
178 x_currency_rate
179 FROM PO_HEADERS_ALL POH
180 WHERE POH.po_header_id = p_po_header_id;
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 RAISE;
185 END GET_PO_CURRENCY_INFO;
186
187
188 --<ENCUMBRANCE FPJ START>
189 --Added a centralized, bulk routine for currency conversion and rounding
190 -------------------------------------------------------------------------------
191 --Start of Comments
192 --Name: round_and_convert_currency
193 --Pre-reqs:
194 --
195 --Modifies:
196 -- PO_SESSION_GT
197 --Locks:
198 -- None.
199 --Function:
200 -- Performs bulk currency conversion and/or currency rounding
201 -- of the input amounts.
202 --Parameters:
203 --IN:
204 --p_unique_id_tbl --added for bug 4878973
205 -- A unique identifier for each row in the passed in table.
206 -- This is used to ensure the output table ordering is the same
207 -- as the input table ordering.
208 --p_amount_in_tbl
209 -- A table of values that need to be converted/rounded
210 --p_exchange_rate_tbl
211 -- A table of numbers that contains the exchange rates if this
212 -- procedure is being used for currency conversion.
213 -- Note: even if not doing a currency conversion, this procedure
214 -- expects this tbl parameter to be the same length as input var
215 -- p_amount_in_tbl, though it may be filled with NULL values
216 --p_from_currency_precision_tbl
217 -- Precision defined for currency of the input amounts (in FND_CURRENCIES).
221 -- Precision defined for desired output currency (in FND_CURRENCIES).
218 -- If both from_currency_precision and from_currency_mau are null, then
219 -- the from_currency is not rounded before conversion to to_currency
220 --p_to_currency_precision_tbl
222 -- If both to_currency_precision and to_currency_mau are null, then
223 -- the to_currency is not rounded after conversion from from_currency.
224 --p_from_currency_mau_tbl
225 -- Minimum accountable unit defined for currency of the input amounts
226 -- (in FND_CURRENCIES).
227 -- If from_currency_mau is null, from_currency_precision is used.
228 -- If both from_currency_precision and from_currency_mau are null, then
229 -- the from_currency is not rounded before conversion to to_currency
230 --p_to_currency_mau_tbl
231 -- Minimum accountable unit defined for desired output currency
232 -- (in FND_CURRENCIES).
233 -- If to_currency_mau is null, to_currency_precision is used.
234 -- If both to_currency_precision and to_currency_mau are null, then
235 -- the to_currency is not rounded after conversion from from_currency.
236 --p_round_only_flag_tbl
237 -- If the flag value = 'Y', then skip the currency conversion,
238 -- but still round the amounts using the to_currency precision/mau.
239 -- If the flag value = 'N', then perform the currency conversion of amounts
240 -- using the exchange rate and from_currency precisions/mau,
241 -- and then round the converted amounts using the to_currency precision/mau.
242 -- Most callers will pass this as 'N'.
243 --OUT:
244 --x_return_status
245 -- APPS Standard parameter
246 -- Indicates whether this procedure completed successfully or not
247 --x_amount_out_tbl
248 -- A table containing the rounded/converted equivalent of p_amount_in_tbl
249 --Testing:
250 --
251 --End of Comments
252 -------------------------------------------------------------------------------
253 PROCEDURE round_and_convert_currency(
254 x_return_status OUT NOCOPY VARCHAR2
255 , p_unique_id_tbl IN PO_TBL_NUMBER --bug 4878973
256 , p_amount_in_tbl IN PO_TBL_NUMBER
257 , p_exchange_rate_tbl IN PO_TBL_NUMBER
258 , p_from_currency_precision_tbl IN PO_TBL_NUMBER
259 , p_from_currency_mau_tbl IN PO_TBL_NUMBER
260 , p_to_currency_precision_tbl IN PO_TBL_NUMBER
261 , p_to_currency_mau_tbl IN PO_TBL_NUMBER
262 , p_round_only_flag_tbl IN PO_TBL_VARCHAR1 --bug 3568671
263 , x_amount_out_tbl OUT NOCOPY PO_TBL_NUMBER
264 )
265 IS
266 l_api_name CONSTANT varchar2(40) := 'ROUND_AND_CONVERT_CURRENCY';
267 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
268 l_progress VARCHAR2(3) := '000';
269
270 l_transaction_id NUMBER;
271
272 BEGIN
273
274 IF g_debug_stmt THEN
275 PO_DEBUG.debug_begin(l_log_head);
276 PO_DEBUG.debug_var(l_log_head,l_progress,'p_unique_id_tbl'
277 ,p_unique_id_tbl);
278 PO_DEBUG.debug_var(l_log_head,l_progress,'p_amount_in_tbl'
279 ,p_amount_in_tbl);
280 PO_DEBUG.debug_var(l_log_head,l_progress,'p_exchange_rate_tbl'
281 ,p_exchange_rate_tbl);
282 PO_DEBUG.debug_var(l_log_head,l_progress,'p_from_currency_precision_tbl'
283 ,p_from_currency_precision_tbl);
284 PO_DEBUG.debug_var(l_log_head,l_progress,'p_from_currency_mau_tbl'
285 ,p_from_currency_mau_tbl);
286 PO_DEBUG.debug_var(l_log_head,l_progress,'p_to_currency_precision_tbl'
287 ,p_to_currency_precision_tbl);
288 PO_DEBUG.debug_var(l_log_head,l_progress,'p_to_currency_mau_tbl'
289 ,p_to_currency_mau_tbl);
290 END IF;
291
292 x_return_status := FND_API.g_ret_sts_success;
293
294 SELECT PO_SESSION_GT_S.nextval
295 INTO l_transaction_id
296 FROM DUAL;
297
298 IF g_debug_stmt THEN
299 PO_DEBUG.debug_var(l_log_head,l_progress,
300 'l_transaction_id', l_transaction_id);
301 END IF;
302
303 l_progress := '010';
304
305 FORALL i in 1 .. p_amount_in_tbl.COUNT
306 INSERT INTO PO_SESSION_GT TEMP
307 (
308 key
309 , num1 --sequence number
310 , num2 --input amount
311 , num3 --exchange rate
312 , num4 --from currency precision
313 , num5 --from currency MAU
314 , num6 --to currency precision
315 , num7 --to currency MAU
316 , char1 --round only flag (to skip currency convert)
317 )
318 VALUES
319 (
320 l_transaction_id
321 , p_unique_id_tbl(i) --bug 4878973: use this instead of rownum
322 , p_amount_in_tbl(i)
323 , NVL(p_exchange_rate_tbl(i), 1)
324 , p_from_currency_precision_tbl(i)
325 , p_from_currency_mau_tbl(i)
326 , p_to_currency_precision_tbl(i)
327 , p_to_currency_mau_tbl(i)
328 , NVL(p_round_only_flag_tbl(i), 'N') --bug 3568671
329 )
330 ;
331
332 l_progress := '020';
333
334 -- First, do a currency conversion if necessary
335 -- bug 3578482: If both 'from currency' precision and MAU are null,
336 -- don't round the 'from currency' value in num2 before doing currency conversion.
337
338 UPDATE PO_SESSION_GT TEMP
339 SET TEMP.num2 =
340 ( DECODE( TEMP.num5
341
342 -- if from MAU is null, use precision
343 -- if precision null, don't round at all.
344 , NULL, DECODE( temp.num4
345 , NULL, TEMP.num2
346 , round(TEMP.num2, TEMP.num4)
347 )
348
349 -- if MAU not null, use MAU
350 , (round (TEMP.num2 / TEMP.num5) * TEMP.num5)
351 )
352 * TEMP.num3 --exchange rate
353 )
354 WHERE TEMP.key = l_transaction_id
355 --bug 3568671: do not do this first calculation if the caller
356 --has specified to skip the currency conversion step
357 AND TEMP.char1 = 'N'
358 ;
359
360 l_progress := '030';
361
362 -- Next, do the rounding using the new currency settings
363 -- bug 3578482: If both 'to currency' precision and MAU are null,
364 -- don't round the 'to currency' value in num2 to get num8.
365
366 UPDATE PO_SESSION_GT TEMP
367 SET TEMP.num8 = --output amount
368 ( DECODE( TEMP.num7
369
370 -- if MAU is null, use precision
371 -- if precision null, don't round at all
372 , NULL, DECODE( temp.num6
373 , NULL, TEMP.num2
374 , round(TEMP.num2, TEMP.num6)
375 )
376
377 -- if MAU not null, use MAU
378 , (round (TEMP.num2 / TEMP.num7) * TEMP.num7)
379 )
380 )
381 WHERE TEMP.key = l_transaction_id
382 ;
383
384
385 IF g_debug_stmt THEN
386 l_progress := '040';
387 SELECT rowid BULK COLLECT INTO PO_DEBUG.g_rowid_tbl
388 FROM PO_SESSION_GT WHERE key = l_transaction_id;
389
390 PO_DEBUG.debug_table(l_log_head,l_progress,'PO_SESSION_GT',
391 PO_DEBUG.g_rowid_tbl,
392 po_tbl_varchar30('num1','num2','num3',
393 'num4','num5','num6',
394 'num7')
395 );
396 END IF;
397
398 l_progress := '045';
399
400 -- Retrieve the final amount into the output table
401 SELECT TEMP.num8
402 BULK COLLECT INTO x_amount_out_tbl
403 FROM PO_SESSION_GT TEMP
404 WHERE TEMP.key = l_transaction_id
405 ORDER BY TEMP.num1; --input and output tbls have same ordering
406
407 l_progress := '050';
408 IF g_debug_stmt THEN
409 PO_DEBUG.debug_var(l_log_head,l_progress,'x_amount_out_tbl'
410 ,x_amount_out_tbl);
411 PO_DEBUG.debug_end(l_log_head);
412 END IF;
413
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 x_return_status := FND_API.g_ret_sts_unexp_error;
418
419 --add a message to the stack and log a debug message
420 po_message_s.sql_error(g_pkg_name, l_api_name,
421 l_progress, SQLCODE, SQLERRM);
422 fnd_msg_pub.add;
423 IF g_debug_unexp THEN
424 PO_DEBUG.debug_exc(
425 p_log_head => l_log_head
426 , p_progress => l_progress
427 );
428 END IF;
429 END round_and_convert_currency;
430 --<ENCUMBRANCE FPJ END>
431
432
433 END PO_CORE_S2;
[Home] [Help]