[Home] [Help]
PACKAGE BODY: APPS.FII_RECONVERSION_PKG
Source
1 PACKAGE BODY FII_RECONVERSION_PKG AS
2 /* $Header: FIICRECB.pls 120.2 2005/10/14 18:21:44 lpoon noship $ */
3
4 g_page_line_no NUMBER := 65;
5 INVALID_SETUP EXCEPTION;
6 RECONV_ERROR EXCEPTION;
7 MISSING_RATE EXCEPTION;
8
9 -- PROCEDURE
10 -- reconvert_amounts()
11 --
12 -- DESCRIPTION:
13 -- This is the main function of this currency reconversion package. It will
14 -- initialize the variables and validate the passed in parameters from the
15 -- concurrent manager. Then, it will call other procdures to cache rates,
16 -- reconvert global amounts for different products and print execution report.
17 PROCEDURE reconvert_amounts( errbuf IN OUT NOCOPY VARCHAR2
18 , retcode IN OUT NOCOPY VARCHAR2
19 , p_currency_type IN VARCHAR2
20 , p_primary_rate_type IN VARCHAR2
21 , p_secondary_rate_type IN VARCHAR2
22 , p_from_date IN VARCHAR2
23 , p_to_date IN VARCHAR2
24 , p_transaction_type IN VARCHAR2
25 , p_log_filename IN VARCHAR2
26 , p_output_filename IN VARCHAR2
27 ) IS
28 l_user_id NUMBER := NULL;
29 l_conc_req_id NUMBER := NULL;
30 l_installed_flag VARCHAR2(1) := 'N';
31 l_reconv_gl_flag VARCHAR2(1) := 'N';
32 l_reconv_ap_flag VARCHAR2(1) := 'N';
33 l_reconv_ar_flag VARCHAR2(1) := 'N';
34 l_gl_name VARCHAR2(240);
35 l_ap_name VARCHAR2(240);
36 l_ar_name VARCHAR2(240);
37
38 l_primaryText VARCHAR2(30);
39 l_primary_currency VARCHAR2(15) := NULL;
40 l_primary_mau NUMBER := NULL;
41 l_secondaryText VARCHAR2(30);
42 l_secondary_currency VARCHAR2(15) := NULL;
43 l_secondary_mau NUMBER := NULL;
44
45 l_from_date DATE := NULL;
46 l_to_date DATE := NULL;
47 l_from_date_id NUMBER := NULL;
48 l_to_date_id NUMBER := NULL;
49
50 l_caching_status VARCHAR2(1) := 'N';
51
52 TYPE CHILD_REQ_REC_TYPE IS RECORD (request_id NUMBER,
53 application_id NUMBER,
54 status VARCHAR2(80),
55 phase VARCHAR2(80),
56 dev_status VARCHAR2(50),
57 dev_phase VARCHAR2(50),
58 completion_msg VARCHAR2(300));
59 TYPE CHILD_REQ_TBL_TYPE IS TABLE OF CHILD_REQ_REC_TYPE;
60 l_req_list CHILD_REQ_TBL_TYPE := CHILD_REQ_TBL_TYPE();
61 l_launched_ct NUMBER := 0;
62 l_running_ct NUMBER := 0;
63 i INTEGER;
64
65 l_completion_status VARCHAR2(1) := 'S';
66 l_return_val BOOLEAN := FALSE;
67
68 l_err_msg VARCHAR2(500) := NULL;
69 l_process_step VARCHAR2(50) := 'INIT_LOG_UTIL';
70 l_procedure_name VARCHAR2(30) := 'reconvert_amounts';
71 BEGIN
72 -- ================
73 -- 1 Initialization
74 -- ================
75
76 --
77 -- 1.1 Initialize the logging
78 --
79 FII_UTIL.initialize(p_log_filename || '.log',
80 p_output_filename || '.out',
81 NULL, 'FII_RECONVERSION_PKG');
82 -- LOG: Function enter
83 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
84
85 -- LOG: List all passed parameters
86 l_process_step := 'LOG_LIST_PARAMS';
87 FII_UTIL.put_line(' ');
88 FII_UTIL.put_line(FND_MESSAGE.get_string('FND', 'CONC-PARAMETERS') || ':');
89 FII_UTIL.put_line(FII_MESSAGE.get_message(
90 'FII_RECONV_TRX_TYPE_PARAM_P', NULL
91 , 'P_VALUE', p_transaction_type));
92 FII_UTIL.put_line(FII_MESSAGE.get_message(
93 'FII_RECONV_CURR_TYPE_PARAM_P', NULL
94 , 'P_VALUE', p_currency_type));
95 FII_UTIL.put_line(FII_MESSAGE.get_message(
96 'FII_RECONV_PRI_RTYPE_PARAM_P', NULL
97 , 'P_VALUE', p_primary_rate_type));
98 FII_UTIL.put_line(FII_MESSAGE.get_message(
99 'FII_RECONV_SEC_RTYPE_PARAM_P', NULL
100 , 'P_VALUE', p_secondary_rate_type));
101 FII_UTIL.put_line(FII_MESSAGE.get_message(
102 'FII_RECONV_FROM_DATE_PARAM_P', NULL
103 , 'P_VALUE', p_from_date));
104 FII_UTIL.put_line(FII_MESSAGE.get_message(
105 'FII_RECONV_TO_DATE_PARAM_P', NULL
106 , 'P_VALUE', p_to_date));
107
108 -- LOG: FII_RECONV_INIT_STEP || time stamp
109 FII_UTIL.put_line(' ');
110 FII_UTIL.put_timestamp(FII_MESSAGE.get_message('FII_RECONV_INIT_STEP', NULL));
111 -- LOG: State debug mode is on if it does
112 FII_UTIL.debug_line('This process is running in debug mode');
113
114 --
115 -- 1.2 Initialize the variables and validate the parameters
116 --
117
118 -- Set l_conc_req_id
119 l_process_step := 'GET_CONC_REQ_ID';
120 l_conc_req_id := FND_GLOBAL.conc_request_id;
121 FII_UTIL.debug_line('Variables:');
122 FII_UTIL.debug_line(' l_conc_req_id = ' || l_conc_req_id);
123 l_user_id := FND_GLOBAL.user_id;
124 FII_UTIL.debug_line(' l_user_id = ' || l_user_id);
125
126 -- Set l_reconv_gl_flag based on p_transaction_type
127 l_process_step := 'CHECK_RECONV_GL';
128 IF (p_transaction_type = 'GL' OR p_transaction_type = 'ALL')
129 THEN
130 -- Check if GL is installed
131 BEGIN
132 SELECT 'Y'
133 INTO l_installed_flag
134 FROM FND_PRODUCT_INSTALLATIONS
135 WHERE application_id = 101;
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 l_installed_flag := 'N';
139 END;
140
141 -- Get GL application name
142 BEGIN
143 SELECT application_name
144 INTO l_gl_name
145 FROM FND_APPLICATION_TL
146 WHERE application_id = 101;
147 EXCEPTION
148 WHEN OTHERS THEN
149 l_gl_name := 'Oracle General Ledger';
150 END;
151 FII_UTIL.debug_line(' l_gl_name = ' || l_gl_name);
152
153 -- Set l_reconv_gl_flag
154 IF (l_installed_flag = 'Y')
155 THEN
156 l_reconv_gl_flag := 'Y';
157
158 ELSE
159 l_reconv_gl_flag := 'N';
160
161 IF (p_transaction_type = 'GL')
162 THEN
163 -- Only GL is selected for reconversion but it's not installed
164 -- => Get error message and raise INVALID_SETUP exception
165 l_err_msg := FII_MESSAGE.get_message(
166 'FII_RECONV_PROD_NOT_INSTALLED', NULL
167 , 'PROD_NAME', l_gl_name);
168 RAISE INVALID_SETUP;
169 END IF; -- IF (p_transaction_type = 'GL')
170
171 END IF; -- IF (l_installed_flag = 'Y')
172 END IF; -- IF (p_transaction_type = 'GL' OR p_transaction_type = 'ALL')
173 FII_UTIL.debug_line(' l_reconv_gl_flag = ' || l_reconv_gl_flag);
174
175 -- Set l_reconv_ap_flag based on p_transaction_type
176 l_process_step := 'CHECK_RECONV_AP';
177 IF (p_transaction_type = 'AP' OR p_transaction_type = 'ALL')
178 THEN
179 -- Check if AP is installed
180 BEGIN
181 SELECT 'Y' INTO l_installed_flag
182 FROM FND_PRODUCT_INSTALLATIONS
183 WHERE application_id = 200;
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 l_installed_flag := 'N';
187 END;
188
189 -- Get AP application name
190 BEGIN
191 SELECT application_name INTO l_ap_name
192 FROM FND_APPLICATION_TL
193 WHERE application_id = 200;
194 EXCEPTION
195 WHEN OTHERS THEN
196 l_ap_name := 'Oracle Payables';
197 END;
198 FII_UTIL.debug_line(' l_ap_name = ' || l_ap_name);
199
200 -- Set l_reconv_ap_flag
201 IF (l_installed_flag = 'Y')
202 THEN
203 l_reconv_ap_flag := 'Y';
204
205 ELSE
206 l_reconv_ap_flag := 'N';
207
208 IF (p_transaction_type = 'AP')
209 THEN
210 -- Only AP is selected for reconversion but it's not installed
211 -- => Get error message and raise INVALID_SETUP exception
212 l_err_msg := FII_MESSAGE.get_message(
213 'FII_RECONV_PROD_NOT_INSTALLED', NULL
214 , 'PROD_NAME', l_ap_name);
215 RAISE INVALID_SETUP;
216 END IF; -- IF (p_transaction_type = 'AP')
217
218 END IF; -- IF (l_installed_flag = 'Y')
219 END IF; -- IF (p_transaction_type = 'AP' OR p_transaction_type = 'ALL')
220 FII_UTIL.debug_line(' l_reconv_ap_flag = ' || l_reconv_ap_flag);
221
222 -- Set l_reconv_ar_flag based on p_transaction_type
223 l_process_step := 'CHECK_RECONV_AR';
224 IF (p_transaction_type = 'AR' OR p_transaction_type = 'ALL')
225 THEN
226 -- Check if AR is installed
227 BEGIN
228 SELECT 'Y' INTO l_installed_flag
229 FROM FND_PRODUCT_INSTALLATIONS
230 WHERE application_id = 222;
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 l_installed_flag := 'N';
234 END;
235
236 -- Get AR application name
237 BEGIN
238 SELECT application_name INTO l_ar_name
239 FROM FND_APPLICATION_TL
240 WHERE application_id = 222;
241 EXCEPTION
242 WHEN OTHERS THEN
243 l_ar_name := 'Oracle Receivables';
244 END;
245 FII_UTIL.debug_line(' l_ar_name = ' || l_ar_name);
246
247 -- Set l_reconv_ar_flag
248 IF (l_installed_flag = 'Y')
249 THEN
250 l_reconv_ar_flag := 'Y';
251
252 ELSE
253 l_reconv_ar_flag := 'N';
254
255 IF (p_transaction_type = 'AR')
256 THEN
257 -- Only AR is selected for reconversion but it's not installed
258 -- => Get error message and raise INVALID_SETUP exception
259 l_err_msg := FII_MESSAGE.get_message(
260 'FII_RECONV_PROD_NOT_INSTALLED', NULL
261 , 'PROD_NAME', l_ar_name);
262 RAISE INVALID_SETUP;
263 END IF; -- IF (p_transaction_type = 'AR')
264
265 END IF; -- IF (l_installed_flag = 'Y')
266 END IF; -- IF (p_transaction_type = 'AR' OR p_transaction_type = 'ALL')
267 FII_UTIL.debug_line(' l_reconv_ar_flag = ' || l_reconv_ar_flag);
268
269 l_process_step := 'GET_PRI_SEC_TEXT';
270 -- Set l_primaryText
271 l_primaryText := FII_MESSAGE.get_message('FII_RECONV_PRIMARY', NULL);
272 FII_UTIL.debug_line(' l_primaryText = ' || l_primaryText);
273 -- Set l_secondaryText
274 l_secondaryText := FII_MESSAGE.get_message('FII_RECONV_SECONDARY', NULL);
275 FII_UTIL.debug_line(' l_secondaryText = ' || l_secondaryText);
276
277 -- Validate Primary global currency setup
278 l_process_step := 'VALIDATE_PRI_SETUP';
279 IF (p_currency_type = 'PRIMARY' OR p_currency_type = 'ALL')
280 THEN
281 -- Check if the primary rate type is provided
282 IF (p_primary_rate_type IS NULL)
283 THEN
284 -- It's not provided
285 -- => Get error message and raise INVALID_SETUP exception
286 l_err_msg := FII_MESSAGE.get_message( 'FII_RECONV_NO_RATE_TYPE', NULL
287 , 'CURR_TYPE1', l_primaryText
288 , 'CURR_TYPE2', l_primaryText);
289 RAISE INVALID_SETUP;
290
291 ELSE
292 -- Set l_primary_currency and l_primary_mau
293 l_primary_currency := BIS_COMMON_PARAMETERS.get_currency_code;
294 FII_UTIL.debug_line(' l_primary_currency = ' || l_primary_currency);
295 l_primary_mau := FII_CURRENCY.get_mau_primary;
296 FII_UTIL.debug_line(' l_primary_mau = ' || l_primary_mau);
297
298 -- Validate primary currency and its MAU
299 IF (l_primary_currency IS NULL AND p_currency_type = 'PRIMARY')
300 THEN
301 -- Primary is selected only but fails to find its currency code
302 -- => Get error message and raise INVALID_SETUP exception
303 l_err_msg := FII_MESSAGE.get_message( 'FII_RECONV_CURR_NOT_FOUND', NULL
304 , 'CURR_TYPE', l_primaryText);
305 RAISE INVALID_SETUP;
306 END IF; -- IF (l_primary_currency IS NULL AND p_currency_type = 'PRIMARY')
307
308 IF (l_primary_currency IS NOT NULL AND l_primary_mau IS NULL)
309 THEN
310 -- Cannot find the MAU for primary global currency
311 -- => Get error message and raise INVALID_SETUP exception
312 l_err_msg := FII_MESSAGE.get_message( 'FII_RECONV_INVALID_MAU', NULL
313 , 'CURR_TYPE', l_primaryText);
314 RAISE INVALID_SETUP;
315 END IF; -- IF (l_primary_currency IS NOT NULL AND l_primary_mau IS NULL)
316
317 END IF; -- IF (p_primary_rate_type IS NULL)
318 END IF; -- IF (p_currency_type = 'PRIMARY' OR p_currency_type = 'ALL')
319
320 -- Validate Secondary global currency setup
321 l_process_step := 'VALIDATE_SEC_SETUP';
322 IF (p_currency_type = 'SECONDARY' OR p_currency_type = 'ALL')
323 THEN
324 -- Check if the secondary rate type is provided
325 IF (p_secondary_rate_type IS NULL)
326 THEN
327 -- It's not provided
328 -- => Get error message and raise INVALID_SETUP exception
329 l_err_msg := FII_MESSAGE.get_message( 'FII_RECONV_NO_RATE_TYPE', NULL
330 , 'CURR_TYPE1', l_secondaryText
331 , 'CURR_TYPE2', l_secondaryText);
332 RAISE INVALID_SETUP;
333
334 ELSE
335 l_secondary_currency := BIS_COMMON_PARAMETERS.get_secondary_currency_code;
336 FII_UTIL.debug_line(' l_secondary_currency = ' || l_secondary_currency);
337 l_secondary_mau := FII_CURRENCY.get_mau_secondary;
338 FII_UTIL.debug_line(' l_secondary_mau = ' || l_secondary_mau);
339
340 IF (l_secondary_currency IS NULL AND p_currency_type = 'SECONDARY')
341 THEN
342 -- Secondary is selected only but fails to find its currency code
343 -- => Get error message and raise INVALID_SETUP exception
344 l_err_msg := FII_MESSAGE.get_message( 'FII_RECONV_CURR_NOT_FOUND', NULL
345 , 'CURR_TYPE', l_secondaryText);
346 RAISE INVALID_SETUP;
347 END IF; -- IF (l_secondary_currency IS NULL AND p_currency_type = ...
348
349 IF (l_secondary_currency IS NOT NULL AND l_secondary_mau IS NULL)
350 THEN
351 -- Cannot find the MAU for secondary global currency
352 -- => Get error message and raise INVALID_SETUP exception
353 l_err_msg := FII_MESSAGE.get_message( 'FII_RECONV_INVALID_MAU', NULL
354 , 'CURR_TYPE', l_secondaryText);
355 RAISE INVALID_SETUP;
356 END IF; -- IF (l_secondary_currency IS NOT NULL AND l_secondary_mau ...
357
358 END IF; -- IF (p_secondary_rate_type IS NULL)
359 END IF; -- IF (p_currency_type = 'SECONDARY' OR p_currency_type = 'ALL')
360
361 -- Convert date strings to date format
362 l_process_step := 'CONVERT_STR_TO_DATE';
363 l_from_date := TO_DATE(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
364 FII_UTIL.debug_line(' l_from_date = ' || l_from_date);
365 l_to_date := TO_DATE(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
366 FII_UTIL.debug_line(' l_to_date = ' || l_to_date);
367
368 -- Validate the date range
369 l_process_step := 'CHECK_FROM_BEFORE_TO';
370 IF (l_from_date > l_to_date)
371 THEN
372 -- From Date is after To Date
373 -- => Get error message and raise INVALID_SETUP exception
374 l_err_msg := FII_MESSAGE.get_message('FII_RECONV_INVALID_DATE_RANGE', NULL);
375 RAISE INVALID_SETUP;
376
377 ELSE
378 -- Validate the passed From Date which is defined in the global calendar
379 l_process_step := 'VALIDATE_FROM_DATE';
380 BEGIN
381 SELECT report_date_julian INTO l_from_date_id
382 FROM FII_TIME_DAY
383 WHERE report_date = l_from_date;
384 EXCEPTION
385 WHEN NO_DATA_FOUND THEN
386 -- From Date is not defined in the calendar
387 -- => Get error message and raise INVALID_SETUP exception
388 l_err_msg := FII_MESSAGE.get_message( 'FII_RECONV_INVALID_DATE', NULL
389 , 'P_DATE', l_from_date);
390 RAISE INVALID_SETUP;
391 END;
392 FII_UTIL.debug_line(' l_from_date_id = ' || l_from_date_id);
393
394 -- Validate the passed To Date which is defined in the global calendar
395 l_process_step := 'VALIDATE_TO_DATE';
396 BEGIN
397 SELECT report_date_julian INTO l_to_date_id
398 FROM FII_TIME_DAY
399 WHERE report_date = l_to_date;
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 -- To Date is not defined in the calendar
403 -- => Get error message and raise INVALID_SETUP exception
404 l_err_msg := FII_MESSAGE.get_message( 'FII_RECONV_INVALID_DATE', NULL
405 , 'P_DATE', l_to_date);
406 RAISE INVALID_SETUP;
407 END;
408 FII_UTIL.debug_line(' l_to_date_id = ' || l_to_date_id);
409 END IF; -- IF (l_from_date > l_to_date)
410
411 -- ===========================
412 -- 2. Cache reconversion rates
413 -- ===========================
414 l_process_step := 'CACHE_RATES';
415 l_caching_status := FII_RECONVERSION_PKG.cache_rates(
416 l_conc_req_id, l_gl_name, l_ap_name, l_ar_name
417 , l_primary_currency, p_primary_rate_type
418 , l_secondary_currency, p_secondary_rate_type
419 , l_reconv_gl_flag, l_reconv_ap_flag, l_reconv_ar_flag
420 , l_from_date_id, l_to_date_id);
421
422 l_process_step := 'CHECK_CACHING_STATUS';
423 IF (l_caching_status = 'C')
424 THEN
425 -- All required rates are cached successfully, so launch 1 sub-request to
426 -- reconvert global amounts for each product
427
428 -- LOG: FII_RECONV_LAUNCH_STEP || time stamp
429 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
430 'FII_RECONV_LAUNCH_STEP', NULL));
431
432 -- ====================================================
433 -- 3. Launch request to reconvert global amounts for GL
434 -- ====================================================
435 IF (l_reconv_gl_flag = 'Y')
436 THEN
437 l_process_step := 'LAUNCH_REQUEST_GL';
438 -- Launch the request and store the reqeust record
439 l_launched_ct := l_launched_ct + 1;
440 l_req_list.extend;
441 l_req_list(l_launched_ct).application_id := 101;
442 l_req_list(l_launched_ct).request_id :=
443 FND_REQUEST.SUBMIT_REQUEST( 'FII'
444 , 'FII_CURR_RECONV_GL_SUBWORKER'
445 , ''
446 , ''
447 , FALSE
448 , l_conc_req_id
449 , l_user_id
450 , l_gl_name
451 , l_primary_currency
452 -- 09/26/05: Changed to convert MAUs to char properly in order to avoid from
453 -- encountering ORA-06502: PL/SQL: numeric or value error
454 , TO_CHAR(NVL(l_primary_mau, -1),
455 'FM99999.99999999999999999999')
456 , l_secondary_currency
457 , TO_CHAR(NVL(l_secondary_mau, -1),
458 'FM99999.99999999999999999999')
459 , l_from_date_id
460 , l_to_date_id
461 , p_log_filename
462 , p_output_filename);
463
464 -- Check if the child request is submitted successfully
465 IF (l_req_list(l_launched_ct).request_id = 0)
466 THEN
467 -- Fail to submit the child request
468 l_completion_status := 'E';
469 FII_UTIL.put_line(FII_MESSAGE.get_message(
470 'FII_RECONV_LAUNCH_ERROR', NULL
471 , 'PROD_NAME', l_gl_name));
472 FII_UTIL.put_line(' ');
473 ELSE
474 -- Succeed to submit the child request
475 -- Commit it in order to launch the child request
476 COMMIT;
477 FII_UTIL.put_line(FII_MESSAGE.get_message(
478 'FII_RECONV_LAUNCH_SUCCESS', NULL
479 , 'REQ_ID', l_req_list(l_launched_ct).request_id
480 , 'PROD_NAME', l_gl_name));
481 l_running_ct := l_running_ct + 1;
482 END IF;
483 END IF; -- IF (l_reconv_gl_flag)
484
485 -- ====================================================
486 -- 4. Launch request to reconvert global amounts for AP
487 -- ====================================================
488 IF (l_reconv_ap_flag = 'Y')
489 THEN
490 l_process_step := 'LAUNCH_REQUEST_AP';
491 -- Launch the request and store the reqeust record
492 l_launched_ct := l_launched_ct + 1;
493 l_req_list.extend;
494 l_req_list(l_launched_ct).application_id := 200;
495 l_req_list(l_launched_ct).request_id :=
496 FND_REQUEST.SUBMIT_REQUEST( 'FII'
497 , 'FII_CURR_RECONV_AP_SUBWORKER'
498 , ''
499 , ''
500 , FALSE
501 , l_conc_req_id
502 , l_user_id
503 , l_ap_name
504 , l_primary_currency
505 -- 09/26/05: Changed to convert MAUs to char properly in order to avoid from
506 -- encountering ORA-06502: PL/SQL: numeric or value error
507 , TO_CHAR(NVL(l_primary_mau, -1),
508 'FM99999.99999999999999999999')
509 , l_secondary_currency
510 , TO_CHAR(NVL(l_secondary_mau, -1),
511 'FM99999.99999999999999999999')
512 , l_from_date_id
513 , l_to_date_id
514 , p_log_filename
515 , p_output_filename);
516
517 -- Check if the child request is submitted successfully
518 IF (l_req_list(l_launched_ct).request_id = 0)
519 THEN
520 -- Fail to submit the child request
521 l_completion_status := 'E';
522 FII_UTIL.put_line(FII_MESSAGE.get_message(
523 'FII_RECONV_LAUNCH_ERROR', NULL
524 , 'PROD_NAME', l_ap_name));
525 ELSE
526 -- Succeed to submit the child request
527 -- Commit it in order to launch the child request
528 COMMIT;
529 FII_UTIL.put_line(FII_MESSAGE.get_message(
530 'FII_RECONV_LAUNCH_SUCCESS', NULL
531 , 'REQ_ID', l_req_list(l_launched_ct).request_id
532 , 'PROD_NAME', l_ap_name));
533 l_running_ct := l_running_ct + 1;
534 END IF;
535 END IF; -- IF (l_reconv_ap_flag)
536
537 -- ====================================================
538 -- 5. Launch request to reconvert global amounts for AR
539 -- ====================================================
540 IF (l_reconv_ar_flag = 'Y')
541 THEN
542 l_process_step := 'LAUNCH_REQUEST_AR';
543 -- Launch the request and store the reqeust record
544 l_launched_ct := l_launched_ct + 1;
545 l_req_list.extend;
546 l_req_list(l_launched_ct).application_id := 222;
547 l_req_list(l_launched_ct).request_id :=
548 FND_REQUEST.SUBMIT_REQUEST( 'FII'
549 , 'FII_CURR_RECONV_AR_SUBWORKER'
550 , ''
551 , ''
552 , FALSE
553 , l_conc_req_id
554 , l_user_id
555 , l_ar_name
556 , l_primary_currency
557 -- 09/26/05: Changed to convert MAUs to char properly in order to avoid from
558 -- encountering ORA-06502: PL/SQL: numeric or value error
559 , TO_CHAR(NVL(l_primary_mau, -1),
560 'FM99999.99999999999999999999')
561 , l_secondary_currency
562 , TO_CHAR(NVL(l_secondary_mau, -1),
563 'FM99999.99999999999999999999')
564 , l_from_date_id
565 , l_to_date_id
566 , p_log_filename
567 , p_output_filename);
568
569 -- Check if the child request is submitted successfully
570 IF (l_req_list(l_launched_ct).request_id = 0)
571 THEN
572 -- Fail to submit the child request
573 l_completion_status := 'E';
574 FII_UTIL.put_line(FII_MESSAGE.get_message(
575 'FII_RECONV_LAUNCH_ERROR', NULL
576 , 'PROD_NAME', l_ar_name));
577 ELSE
578 -- Succeed to submit the child request
579 -- Commit it in order to launch the child request
580 COMMIT;
581 FII_UTIL.put_line(FII_MESSAGE.get_message(
582 'FII_RECONV_LAUNCH_SUCCESS', NULL
583 , 'REQ_ID', l_req_list(l_launched_ct).request_id
584 , 'PROD_NAME', l_ar_name));
585 l_running_ct := l_running_ct + 1;
586 END IF;
587 END IF; -- IF (l_reconv_ar_flag)
588
589 -- ========================================================
590 -- 6. Wait until all running requests completed/errored out
591 -- ========================================================
592 IF (l_running_ct > 0)
593 THEN
594 -- At least one request is submitted successfully, so wait for it to
595 -- complete before proceeding to print the execution report
596 l_process_step := 'WAIT_FOR_REQUESTS';
597 -- LOG: FII_RECONV_LAUNCH_STEP || time stamp
598 FII_UTIL.put_line(' ');
599 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
600 'FII_RECONV_WAIT_STEP', NULL));
601
602 WHILE (l_running_ct > 0 )
603 LOOP
604 FOR i IN 1..l_launched_ct
605 LOOP
606 IF (l_req_list(i).request_id <> 0
607 AND NVL(l_req_list(i).dev_phase, 'PENDING') <> 'COMPLETE')
608 THEN
609 -- Get the request status
610 l_return_val := FND_CONCURRENT.get_request_status(
611 l_req_list(i).request_id
612 , NULL
613 , NULL
614 , l_req_list(i).phase
615 , l_req_list(i).status
616 , l_req_list(i).dev_phase
617 , l_req_list(i).dev_status
618 , l_req_list(i).completion_msg);
619
620 IF (l_req_list(i).dev_phase = 'COMPLETE')
621 THEN
622 -- The launched request has completed
623 l_running_ct := l_running_ct - 1;
624 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
625 'FII_RECONV_CHILD_COMPLETE', NULL
626 , 'REQ_ID', l_req_list(i).request_id
627 , 'STATUS', l_req_list(i).status));
628
629 -- Check if it errors out
630 IF (l_req_list(i).dev_status = 'ERROR')
631 THEN
632 -- The child process errors out, so print error message
633 FII_UTIL.put_line(FII_MESSAGE.get_message(
634 'FII_RECONV_CHILD_ERROR', NULL
635 , 'REQ_ID', l_req_list(i).request_id));
636 FII_UTIL.put_line(' ' || l_req_list(i).completion_msg);
637
638 -- If any child request errors out, this process will error out
639 l_completion_status := 'E';
640 END IF; -- IF (l_req_list(i).dev_status = 'ERROR')
641 END IF; -- IF (l_req_list(i).dev_phase = 'COMPLETE')
642 END IF; -- IF (l_req_list(i).request_id <> 0)
643 END LOOP; -- FOR LOOP
644
645 -- Sleep 30 Seconds
646 DBMS_LOCK.sleep(30);
647 END LOOP; -- WHILE LOOP
648 END IF; -- IF (l_running_ct > 0)
649
650 ELSIF (l_caching_status = 'M')
651 THEN
652 -- There are missing rates, so exit the program with error
653 l_completion_status := 'E';
654 l_err_msg := FII_MESSAGE.get_message('FII_RECONV_MISSING_RATES', NULL);
655
656 ELSE
657 -- There are no transactions selected for reconversion. We will still
658 -- exit the program successfully
659 FII_UTIL.put_line(FII_MESSAGE.get_message(
660 'FII_RECONV_NO_TRANSACTIONS', NULL));
661
662 END IF; -- IF (l_caching_status = 'C')
663
664 -- =========================
665 -- 7. Print execution report
666 -- =========================
667 l_process_step := 'PRINT_EXECUTION_REPORT';
668 -- LOG: FII_RECONV_PRINT_RPT_STEP || time stamp
669 FII_UTIL.put_line(' ');
670 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
671 'FII_RECONV_PRINT_RPT_STEP', NULL));
672 FII_RECONVERSION_PKG.print_report( l_conc_req_id
673 , p_transaction_type
674 , p_currency_type
675 , p_primary_rate_type
676 , p_secondary_rate_type
677 , p_from_date
678 , p_to_date
679 , l_primary_currency
680 , l_secondary_currency
681 , l_caching_status
682 , l_completion_status);
683
684 -- ===========================
685 -- 8. Cleanup the cached rates
686 -- ===========================
687 l_process_step := 'CLEANUP_CACHED_RATES';
688 -- LOG: FII_RECONV_EXIT_PROCESS_STEP || time stamp
689 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
690 'FII_RECONV_CLEANUP_STEP', NULL));
691 -- Delete cached rates from FII_RECONV_RATES for this request and commit
692 DELETE FROM FII_RECONV_RATES
693 WHERE request_id = l_conc_req_id;
694 COMMIT;
695
696 -- ===================
697 -- 9. Exit the process
698 -- ===================
699 l_process_step := 'EXIT_PROCESS';
700 -- LOG: FII_RECONV_EXIT_PROCESS_STEP || time stamp
701 FII_UTIL.put_line(' ');
702 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
703 'FII_RECONV_EXIT_PROCESS_STEP', NULL));
704
705 IF (l_completion_status = 'E')
706 THEN
707 -- Process errors out
708 IF (l_caching_status = 'C')
709 THEN
710 FII_UTIL.debug_line('This process errors out because of sub-request(s)');
711 errbuf := FII_MESSAGE.get_message('FII_RECONV_PROCESS_FAIL', NULL);
712 l_process_step := 'LAUNCH_REQUESTS';
713 ELSE
714 FII_UTIL.debug_line('This process errors out because of missing rate(s)');
715 errbuf := l_err_msg;
716 l_process_step := 'CACHE_RATES';
717 END IF;
718 -- Print error messages to log
719 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, errbuf);
720 -- Set the concurrent program completion status to ERROR
721 l_return_val := FND_CONCURRENT.set_completion_status('ERROR', NULL);
722 -- Raise application error
723 RAISE_APPLICATION_ERROR(-20010, errbuf);
724
725 ELSE
726 -- Process coompletes successfully
727 FII_UTIL.debug_line('This process completes successfully');
728 -- Commit changes to the database
729 FND_CONCURRENT.af_commit;
730 -- Set the concurrent program completion status
731 l_return_val := FND_CONCURRENT.set_completion_status('COMPLETE', NULL);
732 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
733 END IF;
734
735 EXCEPTION
736 WHEN INVALID_SETUP THEN
737 -- Process fails because of invalid setup
738 -- Note: We don't need to rollback since there are no updates/insert
739 FII_UTIL.debug_line('This process errors out because of invalid setup');
740 -- Print error messages to log
741 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, l_err_msg);
742 -- Set the concurrent program completion status to ERROR
743 l_return_val := FND_CONCURRENT.set_completion_status('ERROR', NULL);
744 -- Raise application error
745 errbuf := l_err_msg;
746 RAISE_APPLICATION_ERROR(-20020, errbuf);
747
748 WHEN RECONV_ERROR THEN
749 -- Process fails because of exceptions raised from other functions
750 -- and we need to rollback
751 FII_UTIL.debug_line('This process errors out because of other functions');
752 -- Rollback
753 FND_CONCURRENT.af_rollback;
754 -- Print error messages to log file
755 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step);
756 -- Set the concurrent program completion status to ERROR
757 l_return_val := FND_CONCURRENT.set_completion_status('ERROR', NULL);
758 -- Raise application error
759 errbuf := sqlerrm;
760 retcode := sqlcode;
761 RAISE_APPLICATION_ERROR(-20030, errbuf);
762
763 WHEN OTHERS THEN
764 -- Process fails because of other exceptions and we need to rollback
765 FII_UTIL.debug_line('This process errors out because of other exceptions');
766 -- Rollback
767 FND_CONCURRENT.af_rollback;
768 -- Print error messages to log file
769 errbuf := sqlerrm;
770 retcode := sqlcode;
771 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, errbuf);
772 -- Set the concurrent program completion status to ERROR
773 l_return_val := FND_CONCURRENT.set_completion_status('ERROR', NULL);
774 -- Raise application error
775 RAISE_APPLICATION_ERROR(-20040, errbuf);
776
777 END reconvert_amounts;
778
779
780 --
781 -- FUCNTION
782 -- cache_rates()
783 --
784 -- DESCRIPTION:
785 -- It will insert global conversion rates (from set of books currencies to
786 -- global currencies) into FII_RECONV_RATES for the specified date range.
787 --
788 -- The return value can be:
789 -- 'C' - all the required rates are cached
790 -- 'M' - there are missing rates
791 -- 'N' - no rates are cached
792 FUNCTION cache_rates( p_request_id IN NUMBER
793 , p_gl_name IN VARCHAR2
794 , p_ap_name IN VARCHAR2
795 , p_ar_name IN VARCHAR2
796 , p_primary_currency IN VARCHAR2
797 , p_primary_rate_type IN VARCHAR2
798 , p_secondary_currency IN VARCHAR2
799 , p_secondary_rate_type IN VARCHAR2
800 , p_cache_for_gl_flag IN VARCHAR2
801 , p_cache_for_ap_flag IN VARCHAR2
802 , p_cache_for_ar_flag IN VARCHAR2
803 , p_from_date_id IN NUMBER
804 , p_to_date_id IN NUMBER
805 ) RETURN VARCHAR2 IS
806 l_status VARCHAR2(1) := 'C';
807 l_cached_rate_count NUMBER := 0;
808 l_cached_rate_flag BOOLEAN := FALSE;
809 l_missing_rate_count NUMBER := 0;
810
811 l_process_step VARCHAR2(50) := 'INIT_CACHE_RATES';
812 l_procedure_name VARCHAR2(30) := 'cache_rates';
813 BEGIN
814 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
815
816 IF (p_cache_for_gl_flag = 'Y')
817 THEN
818 --
819 -- 2.1 Cache rates for GL
820 --
821 l_process_step := 'CACHE_RATES_FOR_GL';
822
823 -- LOG: FII_RECONV_CACHE_RATE_STEP || time stamp for GL
824 FII_UTIL.put_line(' ');
825 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
826 'FII_RECONV_CACHE_RATE_STEP', NULL
827 , 'PROD_NAME', p_gl_name));
828
829 -- Insert rates to FII_RECONV_RATES based on the transactions of
830 -- FII_GL_JE_SUMMARY_B within the passed date range
831 INSERT INTO FII_RECONV_RATES
832 ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
833 , PRIMARY_RATE, SECONDARY_RATE)
834 (SELECT DISTINCT
835 p_request_id, t.report_date_julian, t.report_date, sob.currency_code
836 , DECODE( p_primary_currency, sob.currency_code, 1, NULL, NULL
837 , FII_CURRENCY.GET_RATE( sob.currency_code, p_primary_currency
838 , t.report_date, p_primary_rate_type))
839 , DECODE( p_secondary_currency, sob.currency_code, 1, NULL, NULL
840 , FII_CURRENCY.GET_RATE( sob.currency_code, p_secondary_currency
841 , t.report_date, p_secondary_rate_type))
842 FROM FII_TIME_DAY t, GL_LEDGERS_PUBLIC_V sob
843 WHERE t.report_date_julian BETWEEN p_from_date_id and p_to_date_id
844 AND EXISTS (SELECT 'This date has GL transaction'
845 FROM FII_GL_JE_SUMMARY_B gl
846 WHERE gl.time_id = t.report_date_julian
847 -- Bug fix 4637659: Added to check PERIOD_TYPE_ID since it is the first column
848 -- of the changed FII_GL_JE_SUMMARY_B_U1
849 AND gl.period_type_id = 1
850 AND gl.ledger_id = sob.ledger_id));
851
852 -- LOG: Print the number of cached rates for GL
853 l_cached_rate_count := NVL(SQL%ROWCOUNT, 0);
854 IF (l_cached_rate_count > 0)
855 THEN
856 l_cached_rate_flag := TRUE;
857 END IF;
858 FND_MESSAGE.set_name('FND', 'GENERIC_ROWS_PROCESSED');
859 FND_MESSAGE.set_token('ROWS', l_cached_rate_count);
860 FII_UTIL.put_timestamp(FND_MESSAGE.get);
861 END IF; -- IF (p_cache_for_gl_flag = 'Y')
862
863 IF (p_cache_for_ap_flag = 'Y')
864 THEN
865 --
866 -- 2.2 Cache rates for AP only if they're not cached yet
867 --
868 l_process_step := 'CACHE_RATES_FOR_AP';
869
870 -- LOG: FII_RECONV_CACHE_RATE_STEP || time stamp for AP
871 FII_UTIL.put_line(' ');
872 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
873 'FII_RECONV_CACHE_RATE_STEP', NULL
874 , 'PROD_NAME', p_ap_name));
875
876 -- Insert rates to FII_RECONV_RATES based on the transactions of
877 -- FII_AP_INV_B within the passed date range and they are not yet cached
878 INSERT INTO FII_RECONV_RATES
879 ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
880 , PRIMARY_RATE, SECONDARY_RATE)
881 (SELECT DISTINCT
882 p_request_id, t.report_date_julian, t.report_date, sob.currency_code
883 , DECODE( p_primary_currency, sob.currency_code, 1, NULL, NULL
884 , FII_CURRENCY.GET_RATE( sob.currency_code, p_primary_currency
885 , t.report_date, p_primary_rate_type))
886 , DECODE( p_secondary_currency, sob.currency_code, 1, NULL, NULL
887 , FII_CURRENCY.GET_RATE( sob.currency_code, p_secondary_currency
888 , t.report_date, p_secondary_rate_type))
889 FROM FII_TIME_DAY t, GL_LEDGERS_PUBLIC_V sob
890 WHERE t.report_date_julian BETWEEN p_from_date_id and p_to_date_id
891 AND EXISTS (SELECT /*+ parallel(ap) */ 'This date has AP transaction'
892 FROM FII_AP_INV_B ap
893 WHERE ap.account_date_id = t.report_date_julian
894 AND ap.ledger_id = sob.ledger_id)
895 AND NOT EXISTS (SELECT 'This rate has been cached'
896 FROM FII_RECONV_RATES r
897 WHERE r.request_id = p_request_id
898 AND r.rate_date_id = t.report_date_julian
899 AND r.from_currency = sob.currency_code));
900
901 -- LOG: Print the number of cached rates for AP
902 l_cached_rate_count := NVL(SQL%ROWCOUNT, 0);
903 IF (l_cached_rate_count > 0)
904 THEN
905 l_cached_rate_flag := TRUE;
906 END IF;
907 FND_MESSAGE.set_name('FND', 'GENERIC_ROWS_PROCESSED');
908 FND_MESSAGE.set_token('ROWS', l_cached_rate_count);
909 FII_UTIL.put_timestamp(FND_MESSAGE.get);
910 END IF; -- IF (p_cache_for_ap_flag = 'Y')
911
912 IF (p_cache_for_ar_flag = 'Y')
913 THEN
914 --
915 -- 2.3 Cache rates for AR only if they're not cached yet
916 --
917 l_process_step := 'CACHE_RATES_FOR_AR';
918
919 -- LOG: FII_RECONV_CACHE_RATE_STEP || time stamp for AR
920 FII_UTIL.put_line(' ');
921 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
922 'FII_RECONV_CACHE_RATE_STEP', NULL
923 , 'PROD_NAME', p_ar_name));
924
925 -- Insert rates to FII_RECONV_RATES based on the transactions of
926 -- FII_AR_REVENUE_B within the passed date range and they are not yet cached
927 INSERT INTO FII_RECONV_RATES
928 ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
929 , PRIMARY_RATE, SECONDARY_RATE)
930 (SELECT DISTINCT
931 p_request_id, t.report_date_julian, t.report_date, sob.currency_code
932 , DECODE( p_primary_currency, sob.currency_code, 1, NULL, NULL
933 , FII_CURRENCY.GET_RATE( sob.currency_code, p_primary_currency
934 , t.report_date, p_primary_rate_type))
935 , DECODE( p_secondary_currency, sob.currency_code, 1, NULL, NULL
936 , FII_CURRENCY.GET_RATE( sob.currency_code, p_secondary_currency
937 , t.report_date, p_secondary_rate_type))
938 FROM FII_TIME_DAY t, GL_LEDGERS_PUBLIC_V sob
939 WHERE t.report_date_julian BETWEEN p_from_date_id and p_to_date_id
940 AND EXISTS (SELECT /*+ parallel(ar) */ 'This date has AR transaction'
941 FROM FII_AR_REVENUE_B ar
942 WHERE ar.gl_date_id = t.report_date_julian
943 AND ar.ledger_id = sob.ledger_id)
944 AND NOT EXISTS (SELECT 'This rate has been cached'
945 FROM FII_RECONV_RATES r
946 WHERE r.request_id = p_request_id
947 AND r.rate_date_id = t.report_date_julian
948 AND r.from_currency = sob.currency_code));
949
950 -- LOG: Print the number of cached rates for AR
951 l_cached_rate_count := NVL(SQL%ROWCOUNT, 0);
952 IF (l_cached_rate_count > 0)
953 THEN
954 l_cached_rate_flag := TRUE;
955 END IF;
956 FND_MESSAGE.set_name('FND', 'GENERIC_ROWS_PROCESSED');
957 FND_MESSAGE.set_token('ROWS', l_cached_rate_count);
958 FII_UTIL.put_timestamp(FND_MESSAGE.get);
959 END IF; -- IF (p_cache_for_ar_flag = 'Y')
960
961 --
962 -- 2.4 Determine the caching rate status
963 --
964 l_process_step := 'SET_CACHINE_STATUS';
965 IF (l_cached_rate_flag)
966 THEN
967 -- Check if there are any missing/invalid rates (i.e. rate <= 0)
968 -- only when there are cached rates
969 l_process_step := 'CHECK_MISSING_RATES';
970 SELECT count(*) INTO l_missing_rate_count
971 FROM FII_RECONV_RATES
972 WHERE request_id = p_request_id
973 AND (NVL(primary_rate, 1) <= 0 OR NVL(secondary_rate, 1) <= 0);
974 FII_UTIL.debug_line(' ');
975 FII_UTIL.debug_line(TO_CHAR(l_missing_rate_count)
976 || ' days have missing rates');
977
978 IF (l_missing_rate_count > 0)
979 THEN
980 -- There are missing rates
981 l_status := 'M';
982
983 ELSE
984 -- All rates are cached
985 l_status := 'C';
986
987 END IF;
988
989 ELSE
990 -- No rates are cached
991 l_status := 'N';
992
993 END IF;
994
995 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
996 RETURN l_status;
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 -- Print error code and messages to log file and raise error
1001 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, sqlerrm);
1002 -- Raise RECONV_ERROR which will be handled in reconvert_amounts()
1003 RAISE RECONV_ERROR;
1004
1005 END cache_rates;
1006
1007
1008 -- PROCEDURE
1009 -- `()
1010 --
1011 -- DESCRIPTION:
1012 -- It will reconvert global amounts for the GL base table FII_GL_JE_SUMMARY_B.
1013 PROCEDURE reconvert_gl( errbuf IN OUT NOCOPY VARCHAR2
1014 , retcode IN OUT NOCOPY VARCHAR2
1015 , p_request_id IN NUMBER
1016 , p_user_id IN NUMBER
1017 , p_product_name IN VARCHAR2
1018 , p_primary_currency IN VARCHAR2
1019 , p_primary_mau IN NUMBER
1020 , p_secondary_currency IN VARCHAR2
1021 , p_secondary_mau IN NUMBER
1022 , p_from_date_id IN NUMBER
1023 , p_to_date_id IN NUMBER
1024 , p_log_filename IN VARCHAR2
1025 , p_output_filename IN VARCHAR2
1026 ) IS
1027 l_use_id_columns BOOLEAN := FALSE;
1028 l_processSQL DBMS_SQL.VARCHAR2S;
1029 i NUMBER := 0;
1030 l_cursorID INTEGER;
1031 l_message VARCHAR2(255);
1032 l_processed_rows NUMBER := 0;
1033 l_return_val BOOLEAN := FALSE;
1034
1035 l_process_step VARCHAR2(50) := 'INIT_RECONV_GL';
1036 l_procedure_name VARCHAR2(30) := 'reconvert_gl';
1037 BEGIN
1038 --
1039 -- 3.1 Initialize the logging for this child request
1040 --
1041 FII_UTIL.initialize(p_log_filename || '_GL.log',
1042 p_output_filename || '_GL.out',
1043 NULL, 'FII_RECONVERSION_PKG');
1044 -- LOG: Function enter
1045 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
1046 -- LOG: FII_RECONV_INIT_STEP || time stamp
1047 FII_UTIL.put_line(' ');
1048 FII_UTIL.put_timestamp(FII_MESSAGE.get_message('FII_RECONV_INIT_STEP', NULL));
1049 -- LOG: State debug mode is on if it does
1050 FII_UTIL.debug_line('This process is running in debug mode');
1051
1052 -- Determine if we should use ID columns or not
1053 l_process_step := 'CHECK_USE_ID_COLS';
1054 BEGIN
1055 -- If it can pass through this SQL, it means we should use ID columns
1056
1057 -- Build the SELECT SQL to check FII_GL_JE_SUMMARY_B
1058 l_processSQL(1) := 'SELECT company_id, cost_center_id, fin_category_id';
1059 l_processSQL(2) := 'FROM FII_GL_JE_SUMMARY_B WHERE rownum = 1';
1060
1061 -- Open cursor
1062 l_cursorID := DBMS_SQL.OPEN_CURSOR;
1063 -- Parse the SQL
1064 DBMS_SQL.PARSE(l_cursorID, l_processSQL, 1, 2, TRUE, dbms_sql.native);
1065 -- Execute the SQL
1066 l_processed_rows := DBMS_SQL.EXECUTE(l_cursorID);
1067 -- Close cursor
1068 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1069
1070 -- Modify the above SQL to check FII_RECONV_GL_ROLLUP_GT
1071 l_processSQL(2) := 'FROM FII_RECONV_GL_ROLLUP_GT WHERE rownum =1';
1072
1073 -- Open cursor
1074 l_cursorID := DBMS_SQL.OPEN_CURSOR;
1075 -- Parse the SQL
1076 DBMS_SQL.PARSE(l_cursorID, l_processSQL, 1, 2, TRUE, dbms_sql.native);
1077 -- Execute the SQL
1078 l_processed_rows := DBMS_SQL.EXECUTE(l_cursorID);
1079 -- Close cursor
1080 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1081
1082 -- It can select the ID columns from FII_GL_JE_SUMMARY_B and
1083 -- FII_RECONV_GL_ROLLUP_GT, so set l_use_id_columns to TRUE
1084 l_use_id_columns := TRUE;
1085 FII_UTIL.debug_line('ID columns exists');
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088 l_use_id_columns := FALSE;
1089 FII_UTIL.debug_line('ID columns do not exist');
1090 END;
1091
1092 -- LOG: FII_RECONV_RECONVERT_AMT_STEP || time stamp for GL
1093 FII_UTIL.put_line(' ');
1094 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
1095 'FII_RECONV_RECONVERT_AMT_STEP', NULL
1096 , 'PROD_NAME', p_product_name));
1097
1098 --
1099 -- 3.2 Populate daily reconversion differences into FII_RECONV_GL_ROLLUP_GT
1100 --
1101
1102 -- Build the dynamic INSERT SQL for inserting daily differences
1103 l_process_step := 'BUILD_DAILY_SQL_INSERT';
1104
1105 -- INSERT clause
1106 -- There are 3 INSERT SQLS and their first 8 lines are the same.
1107 l_processSQL(1) := 'INSERT INTO FII_RECONV_GL_ROLLUP_GT';
1108
1109 IF (l_use_id_columns)
1110 THEN
1111 l_processSQL(2) :=
1112 '( COMPANY_ID, COST_CENTER_ID, FIN_CATEGORY_ID, JE_SOURCE';
1113 ELSE
1114 l_processSQL(2) := '( COMPANY, COST_CENTER, NATURAL_ACCOUNT, JE_SOURCE';
1115 END IF;
1116
1117 -- Bug fix 4637659: START
1118 l_processSQL(3) := ' , JE_CATEGORY, CHART_OF_ACCOUNTS_ID, PROD_CATEGORY_ID
1119 , USER_DIM1_ID, USER_DIM2_ID, POSTED_DATE';
1120 l_processSQL(4) := ' , LEDGER_ID, TIME_ID, PERIOD_TYPE_ID';
1121 l_processSQL(5) :=
1122 ' , PRIMARY_DIFFERENCE, COMMITTED_AMT_PRI_DIFF
1123 , OBLIGATED_AMT_PRI_DIFF, OTHER_AMT_PRI_DIFF
1124 , SECONDARY_DIFFERENCE)';
1125 -- Bug fix 4637659: END
1126
1127 -- SELECT clause
1128 l_process_step := 'BUILD_DAILY_SQL_SELECT';
1129 l_processSQL(6) := '(SELECT /*+ use_hash(g) parallel(g) parallel(r) */';
1130
1131 -- COLUMNS: COMPANY/COMPANY_ID, COST_CENTER/COST_CENTER_ID,
1132 -- NATURAL_ACCOUNT/FIN_CATEGORY_ID, JE_SOURCE
1133 IF (l_use_id_columns)
1134 THEN
1135 l_processSQL(7) :=
1136 ' g.company_id, g.cost_center_id, g.fin_category_id, g.je_source';
1137 ELSE
1138 l_processSQL(7) :=
1139 ' g.company, g.cost_center, g.natural_account, g.je_source';
1140 END IF;
1141
1142 -- Bug fix 4637659: START
1143 -- COLUMNS: JE_CATEGORY, CHART_OF_ACCOUNTS_ID, PROD_CATEGORY_ID, USER_DIM1_ID,
1144 -- USER_DIM2_ID, POSTED_DATE
1145 l_processSQL(8) :=
1146 ' , g.je_category, g.chart_of_accounts_id, g.prod_category_id
1147 , g.user_dim1_id, g.user_dim2_id, g.posted_date';
1148
1149 -- Set the line count to 9
1150 i := 9;
1151 -- COLUMNS: LEDGER_ID, TIME_ID, PERIOD_TYPE_ID
1152 l_processSQL(i) := ' , g.ledger_id, g.time_id, 1';
1153
1154 -- COLUMNS: PRIMARY_DIFFERENCE, COMMITTED_AMT_PRI_DIFF,
1155 -- OBLIGATED_AMT_PRI_DIFF, OTHER_AMT_PRI_DIFF
1156 i := i + 1;
1157 IF (p_primary_currency IS NOT NULL)
1158 THEN
1159 l_processSQL(i) := ' , Round((g.amount_b*r.primary_rate)/:pri_mau1)';
1160 i := i + 1;
1161 l_processSQL(i) := ' *:pri_mau2 - NVL(g.prim_amount_g,0)';
1162 i := i + 1;
1163 l_processSQL(i) := ' , Round((g.committed_amount_b*r.primary_rate)/:pri_mau3)';
1164 i := i + 1;
1165 l_processSQL(i) := ' *:pri_mau4 - NVL(g.committed_amount_prim,0)';
1166 i := i + 1;
1167 l_processSQL(i) := ' , Round((g.obligated_amount_b*r.primary_rate)/:pri_mau5)';
1168 i := i + 1;
1169 l_processSQL(i) := ' *:pri_mau6 - NVL(g.obligated_amount_prim,0)';
1170 i := i + 1;
1171 l_processSQL(i) := ' , Round((g.other_amount_b*r.primary_rate)/:pri_mau7)';
1172 i := i + 1;
1173 l_processSQL(i) := ' *:pri_mau8 - NVL(g.other_amount_prim,0)';
1174 ELSE
1175 l_processSQL(i) := ' , 0, 0, 0, 0';
1176 END IF; -- IF (p_primary_currency IS NOT NULL)
1177 -- Bug fix 4637659: END
1178
1179 -- COLUMN: SECONDARY_DIFFERENCE
1180 i := i + 1;
1181 IF (p_secondary_currency IS NOT NULL)
1182 THEN
1183 l_processSQL(i) := ' , Round((g.amount_b*r.secondary_rate)/:sec_mau1)';
1184 i := i + 1;
1185 l_processSQL(i) := ' *:sec_mau2 - NVL(g.sec_amount_g,0)';
1186 ELSE
1187 l_processSQL(i) := ' , 0';
1188 END IF; -- IF (p_secondary_currency IS NOT NULL)
1189
1190 -- FROM clause
1191 l_process_step := 'BUILD_DAILY_SQL_FROM';
1192 i := i + 1;
1193 l_processSQL(i) := ' FROM FII_GL_JE_SUMMARY_B g, FII_RECONV_RATES r';
1194
1195 -- WHERE clauses
1196 l_process_step := 'BUILD_DAILY_SQL_WHERE';
1197 i := i + 1;
1198 l_processSQL(i) :=
1199 ' WHERE r.request_id = :req_id AND r.rate_date_id = g.time_id';
1200 i := i + 1;
1201 l_processSQL(i) := ' AND g.time_id BETWEEN :from_date_id AND :to_date_id';
1202 i := i + 1;
1203 l_processSQL(i) := ' AND g.period_type_id = 1 AND g.amount_b <> 0';
1204 i := i + 1;
1205 l_processSQL(i) := ' AND g.functional_currency = r.from_currency';
1206
1207 -- Check if we need to check primary differences
1208 IF (p_primary_currency IS NOT NULL)
1209 THEN
1210 i := i + 1;
1211 -- Bug fix 4637659: START
1212 l_processSQL(i) := ' AND ( (Round((g.amount_b*r.primary_rate)/:pri_mau9)';
1213 i := i + 1;
1214 l_processSQL(i) := ' *:pri_mau10 - NVL(g.prim_amount_g,0)) <> 0';
1215 i := i + 1;
1216 l_processSQL(i) := ' OR (Round((g.committed_amount_b*r.primary_rate)/:pri_mau11)';
1217 i := i + 1;
1218 l_processSQL(i) := ' *:pri_mau12 - NVL(g.committed_amount_prim,0)) <> 0';
1219 i := i + 1;
1220 l_processSQL(i) := ' OR (Round((g.obligated_amount_b*r.primary_rate)/:pri_mau13)';
1221 i := i + 1;
1222 l_processSQL(i) := ' *:pri_mau14 - NVL(g.obligated_amount_prim,0)) <> 0';
1223 i := i + 1;
1224 l_processSQL(i) := ' OR (Round((g.other_amount_b*r.primary_rate)/:pri_mau15)';
1225 i := i + 1;
1226 l_processSQL(i) := ' *:pri_mau16 - NVL(g.other_amount_prim,0)) <> 0';
1227 -- Bug fix 4637659: END
1228 END IF;
1229
1230 -- Check if we need to check secondary differences
1231 IF (p_secondary_currency IS NOT NULL)
1232 THEN
1233 i := i + 1;
1234 IF (p_primary_currency IS NOT NULL)
1235 THEN
1236 l_processSQL(i) :=
1237 ' OR (Round((g.amount_b*r.secondary_rate)/:sec_mau3)';
1238 ELSE
1239 l_processSQL(i) :=
1240 ' AND ( (Round((g.amount_b*r.secondary_rate)/:sec_mau3)';
1241 END IF; -- IF (p_primary_currency IS NOT NULL)
1242
1243 i := i + 1;
1244 l_processSQL(i) := ' *:sec_mau4- NVL(g.sec_amount_g,0)) <> 0';
1245 END IF;
1246 l_processSQL(i) := l_processSQL(i) || '))';
1247
1248 -- Print the dynamic SQL only when debug is on
1249 l_process_step := 'PRINT_DAILY_SQL';
1250 FII_RECONVERSION_PKG.print_sql(
1251 'Insert daily differences SQL:', l_processSQL, i);
1252
1253 -- Open cursor
1254 l_process_step := 'OPEN_DAILY_SQL';
1255 l_cursorID := DBMS_SQL.OPEN_CURSOR;
1256
1257 -- Parse the SQL
1258 l_process_step := 'PARSE_DAILY_SQL';
1259 DBMS_SQL.PARSE(l_cursorID, l_processSQL, 1, i, TRUE, dbms_sql.native);
1260
1261 -- Bind variables
1262 l_process_step := 'BIND_DAILY_SQL';
1263 IF (p_primary_currency IS NOT NULL)
1264 THEN
1265 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau1', p_primary_mau);
1266 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau2', p_primary_mau);
1267 -- Bug fix 4637659: START
1268 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau3', p_primary_mau);
1269 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau4', p_primary_mau);
1270 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau5', p_primary_mau);
1271 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau6', p_primary_mau);
1272 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau7', p_primary_mau);
1273 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau8', p_primary_mau);
1274 -- Bug fix 4637659: END
1275 END IF; -- IF (p_primary_currency IS NOT NULL)
1276
1277 IF (p_secondary_currency IS NOT NULL)
1278 THEN
1279 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':sec_mau1', p_secondary_mau);
1280 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':sec_mau2', p_secondary_mau);
1281 END IF; -- IF (p_secondary_currency IS NOT NULL)
1282
1283 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':req_id', p_request_id);
1284 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':from_date_id', p_from_date_id);
1285 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':to_date_id', p_to_date_id);
1286
1287 IF (p_primary_currency IS NOT NULL)
1288 THEN
1289 -- Bug fix 4637659: START
1290 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau9', p_primary_mau);
1291 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau10', p_primary_mau);
1292 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau11', p_primary_mau);
1293 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau12', p_primary_mau);
1294 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau13', p_primary_mau);
1295 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau14', p_primary_mau);
1296 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau15', p_primary_mau);
1297 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':pri_mau16', p_primary_mau);
1298 -- Bug fix 4637659: END
1299 END IF; -- IF (p_primary_currency IS NOT NULL)
1300
1301 IF (p_secondary_currency IS NOT NULL)
1302 THEN
1303 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':sec_mau3', p_secondary_mau);
1304 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':sec_mau4', p_secondary_mau);
1305 END IF; -- IF (p_secondary_currency IS NOT NULL)
1306
1307 -- Execute the SQL
1308 l_process_step := 'RUN_DAILY_SQL';
1309 l_processed_rows := DBMS_SQL.EXECUTE(l_cursorID);
1310 FND_MESSAGE.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1311 FND_MESSAGE.set_token('ROWS', l_processed_rows);
1312 FII_UTIL.put_timestamp(FND_MESSAGE.get);
1313
1314 -- Close cursor
1315 l_process_step := 'CLOSE_DAILY_SQL';
1316 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1317
1318 -- Check if any daily differences are inserted
1319 IF (l_processed_rows > 0)
1320 THEN
1321 --
1322 -- 3.3 Roll up to weekly differences
1323 --
1324
1325 -- Build the SQL insert weekly differences - we can reuse the first 7 lines
1326 -- of the above SQL and we don't need to care about the extra lines as we
1327 -- will pass the number of lines of the new SQL
1328 l_process_step := 'BUILD_WEEKLY_SQL_SELECT';
1329 -- Reset the hints for this SQL
1330 l_processSQL(6) := '(SELECT /*+ parallel(g) parallel(t) use_hash(g) */';
1331 -- Reset the line count to 9
1332 i := 9;
1333
1334 -- COLUMNS: LEDGER_ID, TIME_ID, PERIOD_TYPE_ID
1335 l_processSQL(i) := ' , g.ledger_id, t.week_id, 16';
1336
1337 -- Bug fix 4637659: START
1338 -- COLUMNS: PRIMARY_DIFFERENCE, COMMITTED_AMT_PRI_DIFF,
1339 -- OBLIGATED_AMT_PRI_DIFF, OTHER_AMT_PRI_DIFF
1340 i := i + 1;
1341 IF (p_primary_currency IS NOT NULL)
1342 THEN
1343 l_processSQL(i) := ' , SUM(g.primary_difference)';
1344 i := i + 1;
1345 l_processSQL(i) := ' , SUM(g.committed_amt_pri_diff)';
1346 i := i + 1;
1347 l_processSQL(i) := ' , SUM(g.obligated_amt_pri_diff)';
1348 i := i + 1;
1349 l_processSQL(i) := ' , SUM(g.other_amt_pri_diff)';
1350 ELSE
1351 l_processSQL(i) := ' , 0, 0, 0, 0';
1352 -- Bug fix 4637659: END
1353 END IF;
1354
1355 -- COLUMN: SECONDARY_DIFFERENCE
1356 i := i + 1;
1357 IF (p_secondary_currency IS NOT NULL)
1358 THEN
1359 l_processSQL(i) := ' , SUM(g.secondary_difference)';
1360 ELSE
1361 l_processSQL(i) := ' , 0';
1362 END IF;
1363
1364 -- FROM clause
1365 l_process_step := 'BUILD_WEEKLY_SQL_FROM';
1366 i := i + 1;
1367 l_processSQL(i) := ' FROM FII_RECONV_GL_ROLLUP_GT g, FII_TIME_DAY t';
1368
1369 -- WHERE clauses
1370 l_process_step := 'BUILD_WEEKLY_SQL_WHERE';
1371 i := i + 1;
1372 l_processSQL(i) := ' WHERE g.time_id = t.report_date_julian';
1373 i := i + 1;
1374 l_processSQL(i) := ' AND g.period_type_id = 1';
1375
1376 -- HAVING clause
1377 l_process_step := 'BUILD_WEEKLY_SQL_HAVING';
1378 -- Check if we need to check primary differences
1379 IF (p_primary_currency IS NOT NULL)
1380 THEN
1381 i := i + 1;
1382 l_processSQL(i) := ' HAVING ( SUM(g.primary_difference) <> 0';
1383 -- Bug fix 4637659: START
1384 i := i + 1;
1385 l_processSQL(i) := ' OR SUM(g.committed_amt_pri_diff) <> 0';
1386 i := i + 1;
1387 l_processSQL(i) := ' OR SUM(g.obligated_amt_pri_diff) <> 0';
1388 i := i + 1;
1389 l_processSQL(i) := ' OR SUM(g.other_amt_pri_diff) <> 0';
1390 -- Bug fix 4637659: END
1391 END IF;
1392
1393 -- Check if we need to check secondary differences
1394 IF (p_secondary_currency IS NOT NULL)
1395 THEN
1396 i := i + 1;
1397 IF (p_primary_currency IS NOT NULL)
1398 THEN
1399 l_processSQL(i) := ' OR SUM(g.secondary_difference) <> 0';
1400 ELSE
1401 l_processSQL(i) := ' HAVING ( SUM(g.secondary_difference) <> 0';
1402 END IF;
1403 END IF;
1404 l_processSQL(i) := l_processSQL(i) || ')';
1405
1406 -- GROUP BY clause
1407 l_process_step := 'BUILD_WEEKLY_SQL_GROUP_BY';
1408 i := i + 1;
1409 IF (l_use_id_columns)
1410 THEN
1411 l_processSQL(i) :=
1412 ' GROUP BY g.company_id, g.cost_center_id, g.fin_category_id';
1413 ELSE
1414 l_processSQL(i) :=
1415 ' GROUP BY g.company, g.cost_center, g.natural_account';
1416 END IF;
1417
1418 i := i + 1;
1419 l_processSQL(i) :=
1420 ' , g.je_source, g.je_category, g.chart_of_accounts_id';
1421 -- Bug fix 4637659: START
1422 i := i + 1;
1423 l_processSQL(i) :=
1424 ' , g.prod_category_id, g.user_dim1_id, g.user_dim2_id';
1425 i := i + 1;
1426 l_processSQL(i) :=
1427 ' , g.posted_date, g.ledger_id, t.week_id)';
1428 -- Bug fix 4637659: END
1429
1430 -- Print the dynamic SQL only when debug is on
1431 l_process_step := 'PRINT_WEEKLY_SQL';
1432 FII_RECONVERSION_PKG.print_sql(
1433 'Rollup weekly differences SQL:', l_processSQL, i);
1434
1435 -- Open cursor
1436 l_process_step := 'OPEN_WEEKLY_SQL';
1437 l_cursorID := DBMS_SQL.OPEN_CURSOR;
1438
1439 -- Parse the SQL
1440 l_process_step := 'PARSE_WEEKLY_SQL';
1441 DBMS_SQL.PARSE(l_cursorID,l_processSQL,1,i,TRUE,dbms_sql.native);
1442
1443 -- Execute the SQL
1444 l_process_step := 'RUN_WEEKLY_SQL';
1445 l_processed_rows := DBMS_SQL.EXECUTE(l_cursorID);
1446 FND_MESSAGE.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1447 FND_MESSAGE.set_token('ROWS', l_processed_rows);
1448 FII_UTIL.put_timestamp(FND_MESSAGE.get);
1449
1450 -- Close cursor
1451 l_process_step := 'CLOSE_WEEKLY_SQL';
1452 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1453
1454 --
1455 -- 3.4 Roll up to periodly, quarterly, and yearly differences
1456 --
1457
1458 -- Build the SQL insert periodly, quarterly, and yearly differences - same
1459 -- as above, we reuse first 7 lines of the previous SQL
1460 l_process_step := 'BUILD_OTHER_SQL_SELECT';
1461
1462 -- Reset the line count to 9
1463 i := 9;
1464
1465 -- COLUMNS: LEDGER_ID, TIME_ID
1466 l_processSQL(i) := ' , g.ledger_id, NVL(t.ent_period_id,';
1467 i := i + 1;
1468 l_processSQL(i) :=
1469 ' NVL(t.ent_qtr_id, t.ent_year_id))';
1470
1471 -- COLUMN: PERIOD_TYPE_ID
1472 i := i + 1;
1473 l_processSQL(i) := ' , DECODE(t.ent_period_id, NULL';
1474 i := i + 1;
1475 l_processSQL(i) := ' , DECODE(t.ent_qtr_id, NULL, 128, 64), 32)';
1476
1477 -- Bug fix 4637659: START
1478 -- COLUMNS: PRIMARY_DIFFERENCE, COMMITTED_AMT_PRI_DIFF,
1479 -- OBLIGATED_AMT_PRI_DIFF, OTHER_AMT_PRI_DIFF
1480 i := i + 1;
1481 IF (p_primary_currency IS NOT NULL)
1482 THEN
1483 l_processSQL(i) := ' , SUM(g.primary_difference)';
1484 i := i + 1;
1485 l_processSQL(i) := ' , SUM(g.committed_amt_pri_diff)';
1486 i := i + 1;
1487 l_processSQL(i) := ' , SUM(g.obligated_amt_pri_diff)';
1488 i := i + 1;
1489 l_processSQL(i) := ' , SUM(g.other_amt_pri_diff)';
1490 ELSE
1491 l_processSQL(i) := ' , 0, 0, 0, 0';
1492 -- Bug fix 4637659: END
1493 END IF;
1494
1495 -- COLUMN: SECONDARY_DIFFERENCE
1496 i := i + 1;
1497 IF (p_secondary_currency IS NOT NULL)
1498 THEN
1499 l_processSQL(i) := ' , SUM(g.secondary_difference)';
1500 ELSE
1501 l_processSQL(i) := ' , 0';
1502 END IF;
1503
1504 -- FROM clause
1505 l_process_step := 'BUILD_OTHER_SQL_FROM';
1506 i := i + 1;
1507 l_processSQL(i) := ' FROM FII_RECONV_GL_ROLLUP_GT g, FII_TIME_DAY t';
1508
1509 -- WHERE clauses
1510 l_process_step := 'BUILD_OTHER_SQL_WHERE';
1511 i := i + 1;
1512 l_processSQL(i) := ' WHERE g.time_id = t.report_date_julian';
1513 i := i + 1;
1514 l_processSQL(i) := ' AND g.period_type_id = 1';
1515
1516 -- HAVING clause
1517 l_process_step := 'BUILD_OTHER_SQL_HAVING';
1518 -- Check if we need to check primary differences
1519 IF (p_primary_currency IS NOT NULL)
1520 THEN
1521 i := i + 1;
1522 l_processSQL(i) := ' HAVING ( SUM(g.primary_difference) <> 0';
1523 -- Bug fix 4637659: START
1524 i := i + 1;
1525 l_processSQL(i) := ' OR SUM(g.committed_amt_pri_diff) <> 0';
1526 i := i + 1;
1527 l_processSQL(i) := ' OR SUM(g.obligated_amt_pri_diff) <> 0';
1528 i := i + 1;
1529 l_processSQL(i) := ' OR SUM(g.other_amt_pri_diff) <> 0';
1530 -- Bug fix 4637659: END
1531 END IF;
1532
1533 -- Check if we need to check secondary differences
1534 IF (p_secondary_currency IS NOT NULL)
1535 THEN
1536 i := i + 1;
1537 IF (p_primary_currency IS NOT NULL)
1538 THEN
1539 l_processSQL(i) := ' OR SUM(g.secondary_difference) <> 0';
1540 ELSE
1541 l_processSQL(i) := ' HAVING ( SUM(g.secondary_difference) <> 0';
1542 END IF;
1543 END IF;
1544 l_processSQL(i) := l_processSQL(i) || ')';
1545
1546 -- GROUP BY clause
1547 l_process_step := 'BUILD_OTHER_SQL_GROUP_BY';
1548 i := i + 1;
1549 IF (l_use_id_columns)
1550 THEN
1551 l_processSQL(i) :=
1552 ' GROUP BY g.company_id, g.cost_center_id, g.fin_category_id';
1553 ELSE
1554 l_processSQL(i) :=
1555 ' GROUP BY g.company, g.cost_center, g.natural_account';
1556 END IF;
1557
1558 i := i + 1;
1559 l_processSQL(i) :=
1560 ' , g.je_source, g.je_category, g.chart_of_accounts_id';
1561 -- Bug fix 4637659: START
1562 i := i + 1;
1563 l_processSQL(i) :=
1564 ' , g.prod_category_id, g.user_dim1_id, g.user_dim2_id';
1565 i := i + 1;
1566 l_processSQL(i) :=
1567 ' , g.posted_date, g.ledger_id, t.ent_year_id';
1568 -- Bug fix 4637659: END
1569 i := i + 1;
1570 l_processSQL(i) := ' , ROLLUP(t.ent_qtr_id, t.ent_period_id))';
1571
1572 -- Print the dynamic SQL only when debug is on
1573 l_process_step := 'PRINT_OTHER_SQL';
1574 FII_RECONVERSION_PKG.print_sql(
1575 'Rollup other differences SQL:', l_processSQL, i);
1576
1577 -- Open cursor
1578 l_process_step := 'OPEN_OTHER_SQL';
1579 l_cursorID := DBMS_SQL.OPEN_CURSOR;
1580
1581 -- Parse the SQL
1582 l_process_step := 'PARSE_OTHER_SQL';
1583 DBMS_SQL.PARSE(l_cursorID, l_processSQL, 1, i, TRUE, dbms_sql.native);
1584
1585 -- Execute the SQL
1586 l_process_step := 'RUN_OTHER_SQL';
1587 l_processed_rows := DBMS_SQL.EXECUTE(l_cursorID);
1588 FND_MESSAGE.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1589 FND_MESSAGE.set_token('ROWS', l_processed_rows);
1590 FII_UTIL.put_timestamp(FND_MESSAGE.get);
1591
1592 -- Close cursor
1593 l_process_step := 'CLOSE_OTHER_SQL';
1594 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1595
1596 --
1597 -- 3.5 Updated FII_GL_JE_SUMMARY_B with the global amount differences,
1598 --
1599
1600 -- Build the UPDATE SQL to update the differences to FII_GL_JE_SUMMARY_B
1601 l_process_step := 'DELETE_SQL_BUFFER';
1602 -- Reset line count to 1 and delete the SQL buffer since the UPDATE SQL is
1603 -- totally different with previous INSERT SQLs
1604 l_processSQL.delete;
1605 i := 1;
1606
1607 -- UPDATE clause
1608 l_process_step := 'BUILD_UPDATE_SQL';
1609 l_processSQL(i) := 'UPDATE /*+ parallel(g) full(g) */ FII_GL_JE_SUMMARY_B g';
1610 -- Bug fix 4637659: START
1611 i := i + 1;
1612 l_processSQL(i) :=
1613 'SET ( PRIM_AMOUNT_G, COMMITTED_AMOUNT_PRIM, OBLIGATED_AMOUNT_PRIM';
1614 i := i + 1;
1615 l_processSQL(i) :=
1616 ' , OTHER_AMOUNT_PRIM, SEC_AMOUNT_G, LAST_UPDATE_DATE';
1617 -- Bug fix 4637659: END
1618 i := i + 1;
1619 l_processSQL(i) := ' , LAST_UPDATED_BY, LAST_UPDATE_LOGIN) = ';
1620
1621 -- SELECT clause
1622 l_process_step := 'BUILD_UPDATE_SQL_SELECT';
1623 -- COLUMN: PRIM_AMOUNT_G
1624 i := i + 1;
1625 l_processSQL(i) :=
1626 '(SELECT NVL(g.prim_amount_g, 0) + r.primary_difference';
1627
1628 -- Bug fix 4637659: START
1629 -- COLUMN: COMMITTED_AMOUNT_PRIM
1630 i := i + 1;
1631 l_processSQL(i) :=
1632 ' , NVL(g.committed_amount_prim, 0) + r.committed_amt_pri_diff';
1633
1634 -- COLUMN: OBLIGATED_AMOUNT_PRIM
1635 i := i + 1;
1636 l_processSQL(i) :=
1637 ' , NVL(g.obligated_amount_prim, 0) + r.obligated_amt_pri_diff';
1638
1639 -- COLUMN: OTHER_AMOUNT_PRIM
1640 i := i + 1;
1641 l_processSQL(i) :=
1642 ' , NVL(g.other_amount_prim, 0) + r.other_amt_pri_diff';
1643 -- Bug fix 4637659: END
1644
1645 -- COLUMN: SEC_AMOUNT_G
1646 i := i + 1;
1647 l_processSQL(i) :=
1648 ' , NVL(g.sec_amount_g, 0) + r.secondary_difference';
1649
1650 -- COLUMNS: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
1651 i := i + 1;
1652 l_processSQL(i) := ' , sysdate, :user_id1, :user_id2';
1653
1654 -- SELECT FROM clause
1655 l_process_step := 'BUILD_UPDATE_SQL_FROM';
1656 i := i + 1;
1657 l_processSQL(i) := ' FROM FII_RECONV_GL_ROLLUP_GT r';
1658
1659 -- SELECT WHERE clauses
1660 l_process_step := 'BUILD_UPDATE_SQL_WHERE1';
1661 i := i + 1;
1662 l_processSQL(i) := ' WHERE r.time_id = g.time_id';
1663 i := i + 1;
1664 l_processSQL(i) := ' AND r.chart_of_accounts_id = g.chart_of_accounts_id';
1665
1666 i := i + 1;
1667 IF (l_use_id_columns)
1668 THEN
1669 l_processSQL(i) := ' AND r.cost_center_id = g.cost_center_id';
1670 i := i + 1;
1671 l_processSQL(i) := ' AND r.fin_category_id = g.fin_category_id';
1672 i := i + 1;
1673 l_processSQL(i) := ' AND r.company_id = g.company_id';
1674 ELSE
1675 l_processSQL(i) := ' AND r.cost_center = g.cost_center';
1676 i := i + 1;
1677 l_processSQL(i) := ' AND r.natural_account = g.natural_account';
1678 i := i + 1;
1679 l_processSQL(i) := ' AND r.company = g.company';
1680 END IF;
1681
1682 i := i + 1;
1683 l_processSQL(i) := ' AND r.je_source = g.je_source';
1684 i := i + 1;
1685 l_processSQL(i) := ' AND r.je_category = g.je_category';
1686 i := i + 1;
1687 l_processSQL(i) := ' AND r.ledger_id = g.ledger_id';
1688 -- Bug fix 4637659: START
1689 i := i + 1;
1690 l_processSQL(i) := ' AND r.period_type_id = g.period_type_id';
1691 i := i + 1;
1692 l_processSQL(i) := ' AND r.user_dim1_id = g.user_dim1_id';
1693 i := i + 1;
1694 l_processSQL(i) := ' AND r.user_dim2_id = g.user_dim2_id';
1695 i := i + 1;
1696 l_processSQL(i) := ' AND NVL(r.posted_date, to_date(''01/01/1950'', ''MM/DD/YYYY''))
1697 = NVL(g.posted_date, to_date(''01/01/1950'', ''MM/DD/YYYY''))';
1698 -- Bug fix 4637659: END
1699 i := i + 1;
1700 l_processSQL(i) := ' AND r.prod_category_id = g.prod_category_id)';
1701
1702 -- UPDATE WHERE clauses
1703 l_process_step := 'BUILD_UPDATE_SQL_WHERE2';
1704 i := i + 1;
1705 IF (l_use_id_columns)
1706 THEN
1707 l_processSQL(i) :=
1708 ' WHERE ( g.time_id, g.chart_of_accounts_id, g.cost_center_id';
1709 i := i + 1;
1710 l_processSQL(i) :=
1711 ' , g.fin_category_id, g.company_id, g.je_source';
1712 ELSE
1713 l_processSQL(i) :=
1714 ' WHERE ( g.time_id, g.chart_of_accounts_id, g.cost_center';
1715 i := i + 1;
1716 l_processSQL(i) :=
1717 ' , g.natural_account, g.company, g.je_source';
1718 END IF;
1719 i := i + 1;
1720 -- Bug fix 4637659: START
1721 l_processSQL(i) :=
1722 ' , g.je_category, g.ledger_id, g.period_type_id';
1723 i := i + 1;
1724 l_processSQL(i) :=
1725 ' , g.user_dim1_id, g.user_dim2_id, g.prod_category_id
1726 , NVL(g.posted_date, to_date(''01/01/1950'', ''MM/DD/YYYY'')))';
1727 -- Bug fix 4637659: END
1728
1729 i := i + 1;
1730 IF (l_use_id_columns)
1731 THEN
1732 l_processSQL(i) :=
1733 ' IN (SELECT r2.time_id, r2.chart_of_accounts_id, r2.cost_center_id';
1734 i := i + 1;
1735 l_processSQL(i) :=
1736 ' , r2.fin_category_id, r2.company_id, r2.je_source';
1737 ELSE
1738 l_processSQL(i) :=
1739 ' IN (SELECT r2.time_id, r2.chart_of_accounts_id, r2.cost_center';
1740 i := i + 1;
1741 l_processSQL(i) :=
1742 ' , r2.natural_account, r2.company, r2.je_source';
1743 END IF;
1744
1745 i := i + 1;
1746 -- Bug fix 4637659: START
1747 l_processSQL(i) :=
1748 ' , r2.je_category, r2.ledger_id, r2.period_type_id';
1749 i := i + 1;
1750 l_processSQL(i) :=
1751 ' , r2.user_dim1_id, r2.user_dim2_id, r2.prod_category_id
1752 , NVL(r2.posted_date, to_date(''01/01/1950'', ''MM/DD/YYYY''))';
1753 -- Bug fix 4637659: END
1754 i := i + 1;
1755 l_processSQL(i) := ' FROM FII_RECONV_GL_ROLLUP_GT r2)';
1756
1757 -- Print the dynamic SQL only when debug is on
1758 l_process_step := 'PRINT_UPDATE_SQL';
1759 FII_RECONVERSION_PKG.print_sql(
1760 'Update GL differences SQL:', l_processSQL, i);
1761
1762 -- Open cursor
1763 l_process_step := 'OPEN_UPDATE_SQL';
1764 l_cursorID := DBMS_SQL.OPEN_CURSOR;
1765
1766 -- Parse the SQL
1767 l_process_step := 'PARSE_UPDATE_SQL';
1768 DBMS_SQL.PARSE(l_cursorID,l_processSQL,1,i,TRUE,dbms_sql.native);
1769
1770 -- Bind variables
1771 l_process_step := 'BIND_UPDATE_SQL';
1772 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':user_id1', p_user_id);
1773 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':user_id2', p_user_id);
1774
1775 -- Execute the SQL
1776 l_process_step := 'RUN_UPDATE_SQL';
1777 l_processed_rows := DBMS_SQL.EXECUTE(l_cursorID);
1778 COMMIT;
1779 FND_MESSAGE.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1780 FND_MESSAGE.set_token('ROWS', l_processed_rows);
1781 FII_UTIL.put_timestamp(FND_MESSAGE.get);
1782
1783 -- Close cursor
1784 l_process_step := 'CLOSE_UPDATE_SQL';
1785 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1786
1787 END IF; -- IF (l_processed_rows > 0)
1788 -- LOG: Print out the number of updated rows for GL
1789 FII_UTIL.put_line(' ');
1790 l_message := FII_MESSAGE.get_message('FII_RECONV_UPDATED_ROWS', NULL,
1791 'NUM_ROWS', l_processed_rows,
1792 'PROD_NAME', p_product_name);
1793 FII_UTIL.put_line(l_message);
1794
1795 --
1796 -- 3.6 This process completes successfully
1797 --
1798 l_process_step := 'EXIT_PROCESS';
1799 -- LOG: FII_RECONV_EXIT_PROCESS_STEP || time stamp
1800 FII_UTIL.put_line(' ');
1801 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
1802 'FII_RECONV_EXIT_PROCESS_STEP', NULL));
1803 FII_UTIL.debug_line('Process complete successfully');
1804
1805 -- Commit changes to the database and print log messages
1806 FND_CONCURRENT.af_commit;
1807 -- Set the concurrent program completion status to COMPLETE
1808 l_return_val := FND_CONCURRENT.set_completion_status('COMPLETE', l_message);
1809
1810 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
1811
1812 EXCEPTION
1813 WHEN RECONV_ERROR THEN
1814 -- Process fails because of exceptions raised from other functions
1815 -- and we need to rollback
1816 FII_UTIL.debug_line('Proces errors out because of other functions'' error');
1817 -- Rollback
1818 FND_CONCURRENT.af_rollback;
1819 -- Print error messages to log file
1820 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step);
1821 -- Set the concurrent program completion status to ERROR
1822 l_return_val := FND_CONCURRENT.set_completion_status('ERROR', NULL);
1823 -- Raise application error
1824 errbuf := sqlerrm;
1825 retcode := sqlcode;
1826 RAISE_APPLICATION_ERROR(-20050, errbuf);
1827
1828 WHEN OTHERS THEN
1829 -- Process fails because of other exceptions and we need to rollback
1830 FII_UTIL.debug_line('Proces errors out because of other exception');
1831 -- Rollback
1832 FND_CONCURRENT.af_rollback;
1833 -- Print error messages to log file
1834 errbuf := sqlerrm;
1835 retcode := sqlcode;
1836 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, errbuf);
1837 -- Set the concurrent program completion status to ERROR
1838 l_return_val := FND_CONCURRENT.set_completion_status('ERROR', NULL);
1839 -- Raise application error
1840 RAISE_APPLICATION_ERROR(-20060, errbuf);
1841
1842 END reconvert_gl;
1843
1844
1845 -- PROCEDURE
1846 -- reconvert_ap()
1847 --
1848 -- DESCRIPTION:
1849 -- It will reconvert global amounts for the AP base table FII_AP_INV_B.
1850 PROCEDURE reconvert_ap( errbuf IN OUT NOCOPY VARCHAR2
1851 , retcode IN OUT NOCOPY VARCHAR2
1852 , p_request_id IN NUMBER
1853 , p_user_id IN NUMBER
1854 , p_product_name IN VARCHAR2
1855 , p_primary_currency IN VARCHAR2
1856 , p_primary_mau IN NUMBER
1857 , p_secondary_currency IN VARCHAR2
1858 , p_secondary_mau IN NUMBER
1859 , p_from_date_id IN NUMBER
1860 , p_to_date_id IN NUMBER
1861 , p_log_filename IN VARCHAR2
1862 , p_output_filename IN VARCHAR2
1863 ) IS
1864 l_processed_rows NUMBER := 0;
1865 l_message VARCHAR2(255);
1866 l_return_val BOOLEAN := FALSE;
1867
1868 l_process_step VARCHAR2(50) := 'INIT_RECONV_AP';
1869 l_procedure_name VARCHAR2(30) := 'reconvert_ap';
1870 BEGIN
1871 --
1872 -- 4.1 Initialize the logging for this child request
1873 --
1874 FII_UTIL.initialize(p_log_filename || '_AP.log',
1875 p_output_filename || '_AP.out',
1876 NULL, 'FII_RECONVERSION_PKG');
1877 FII_UTIL.put_line(' ');
1878 -- LOG: Function enter
1879 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
1880 -- LOG: FII_RECONV_INIT_STEP || time stamp
1881 FII_UTIL.put_timestamp(FII_MESSAGE.get_message('FII_RECONV_INIT_STEP', NULL));
1882 -- LOG: State debug mode is on if it does
1883 FII_UTIL.debug_line('This process is running in debug mode');
1884
1885 -- LOG: FII_RECONV_RECONVERT_AMT_STEP || time stamp for AP
1886 FII_UTIL.put_line(' ');
1887 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
1888 'FII_RECONV_RECONVERT_AMT_STEP', NULL
1889 , 'PROD_NAME', p_product_name));
1890
1891 --
1892 -- 4.2 Update FII_AP_INV_B for the newly different reconverted global amounts
1893 --
1894 l_process_step := 'UPDATE_AP_B';
1895 UPDATE /*+ parallel(ap) */ FII_AP_INV_B ap
1896 SET ( PRIM_AMOUNT_G, SEC_AMOUNT_G, LAST_UPDATE_DATE
1897 , LAST_UPDATED_BY, LAST_UPDATE_LOGIN) =
1898 (SELECT DECODE(p_primary_currency, NULL, ap.prim_amount_g
1899 , ROUND((ap.amount_b*r.primary_rate)
1900 /p_primary_mau)*p_primary_mau)
1901 , DECODE(p_secondary_currency, NULL, ap.sec_amount_g
1902 , ROUND((ap.amount_b*r.secondary_rate)
1903 /p_secondary_mau)*p_secondary_mau)
1904 , sysdate, p_user_id, p_user_id
1905 FROM FII_RECONV_RATES r, GL_LEDGERS_PUBLIC_V sob
1906 WHERE r.request_id = p_request_id
1907 AND r.rate_date_id = ap.account_date_id
1908 AND r.from_currency = sob.currency_code
1909 AND sob.ledger_id = ap.ledger_id)
1910 WHERE ap.amount_b <> 0
1911 AND ap.account_date_id BETWEEN p_from_date_id AND p_to_date_id
1912 AND ( (p_primary_currency IS NOT NULL
1913 AND ap.prim_amount_g <>
1914 (SELECT ROUND((ap.amount_b*r2.primary_rate)
1915 /p_primary_mau)*p_primary_mau
1916 FROM FII_RECONV_RATES r2, GL_LEDGERS_PUBLIC_V sob2
1917 WHERE r2.request_id = p_request_id
1918 AND r2.rate_date_id = ap.account_date_id
1919 AND r2.from_currency = sob2.currency_code
1920 AND sob2.ledger_id = ap.ledger_id))
1921 OR (p_secondary_currency IS NOT NULL
1922 AND ap.sec_amount_g <>
1923 (SELECT ROUND((ap.amount_b*r3.secondary_rate)
1924 /p_secondary_mau)*p_secondary_mau
1925 FROM FII_RECONV_RATES r3, GL_LEDGERS_PUBLIC_V sob3
1926 WHERE r3.request_id = p_request_id
1927 AND r3.rate_date_id = ap.account_date_id
1928 AND r3.from_currency = sob3.currency_code
1929 AND sob3.ledger_id = ap.ledger_id)));
1930 l_processed_rows := SQL%ROWCOUNT;
1931 COMMIT;
1932
1933 -- LOG: Print out the number of updated rows for AP
1934 l_message := FII_MESSAGE.get_message('FII_RECONV_UPDATED_ROWS', NULL,
1935 'NUM_ROWS', NVL(l_processed_rows, 0),
1936 'PROD_NAME', p_product_name);
1937 FII_UTIL.put_line(l_message);
1938
1939 --
1940 -- 4.3 This process completes successfully
1941 --
1942 l_process_step := 'EXIT_PROCESS';
1943 -- LOG: FII_RECONV_EXIT_PROCESS_STEP || time stamp
1944 FII_UTIL.put_line(' ');
1945 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
1946 'FII_RECONV_EXIT_PROCESS_STEP', NULL));
1947 FII_UTIL.debug_line('Process complete successfully');
1948
1949 -- Commit changes to the database and print log messages
1950 FND_CONCURRENT.af_commit;
1951 -- Set the concurrent program completion status to COMPLETE
1952 l_return_val := FND_CONCURRENT.set_completion_status('COMPLETE', l_message);
1953
1954 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
1955
1956 EXCEPTION
1957 WHEN OTHERS THEN
1958 -- Process fails because of other exceptions and we need to rollback
1959 FII_UTIL.debug_line('Proces errors out because of other exception');
1960 -- Rollback
1961 FND_CONCURRENT.af_rollback;
1962 -- Print error messages to log file
1963 errbuf := sqlerrm;
1964 retcode := sqlcode;
1965 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, errbuf);
1966 -- Set the concurrent program completion status to ERROR
1967 l_return_val := FND_CONCURRENT.set_completion_status('ERROR', NULL);
1968 -- Raise application error
1969 RAISE_APPLICATION_ERROR(-20070, errbuf);
1970
1971 END reconvert_ap;
1972
1973
1974 -- PROCEDURE
1975 -- reconvert_ar()
1976 --
1977 -- DESCRIPTION:
1978 -- It will reconvert global amounts for the AR base table FII_AR_REVENUE_B.
1979 PROCEDURE reconvert_ar( errbuf IN OUT NOCOPY VARCHAR2
1980 , retcode IN OUT NOCOPY VARCHAR2
1981 , p_request_id IN NUMBER
1982 , p_user_id IN NUMBER
1983 , p_product_name IN VARCHAR2
1984 , p_primary_currency IN VARCHAR2
1985 , p_primary_mau IN NUMBER
1986 , p_secondary_currency IN VARCHAR2
1987 , p_secondary_mau IN NUMBER
1988 , p_from_date_id IN NUMBER
1989 , p_to_date_id IN NUMBER
1990 , p_log_filename IN VARCHAR2
1991 , p_output_filename IN VARCHAR2
1992 ) IS
1993 l_processed_rows NUMBER := 0;
1994 l_message VARCHAR2(255);
1995 l_return_val BOOLEAN := FALSE;
1996
1997 l_process_step VARCHAR2(50) := 'INIT_LOG_UTIL';
1998 l_procedure_name VARCHAR2(30) := 'reconvert_ar';
1999 BEGIN
2000 --
2001 -- 5.1 Initialize the logging for this child request
2002 --
2003 FII_UTIL.initialize(p_log_filename || '_AR.log',
2004 p_output_filename || '_AR.out',
2005 NULL, 'FII_RECONVERSION_PKG');
2006 FII_UTIL.put_line(' ');
2007 -- LOG: Function enter
2008 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
2009 -- LOG: FII_RECONV_INIT_STEP || time stamp
2010 FII_UTIL.put_timestamp(FII_MESSAGE.get_message('FII_RECONV_INIT_STEP', NULL));
2011 -- LOG: State debug mode is on if it does
2012 FII_UTIL.debug_line('This process is running in debug mode');
2013 -- LOG: FII_RECONV_RECONVERT_AMT_STEP || time stamp for AR
2014 FII_UTIL.put_line(' ');
2015 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
2016 'FII_RECONV_RECONVERT_AMT_STEP', NULL
2017 , 'PROD_NAME', p_product_name));
2018
2019 --
2020 -- 5.2 Update FII_AR_REVENUE_B for the different reconverted global amounts
2021 --
2022 l_process_step := 'UPDATE_AR_B';
2023 UPDATE /*+ parallel(ar) */ FII_AR_REVENUE_B ar
2024 SET ( PRIM_AMOUNT_G, SEC_AMOUNT_G, LAST_UPDATE_DATE
2025 , LAST_UPDATED_BY, LAST_UPDATE_LOGIN) =
2026 (SELECT DECODE(p_primary_currency, NULL, ar.prim_amount_g
2027 , ROUND((ar.amount_b*r.primary_rate)
2028 /p_primary_mau)*p_primary_mau)
2029 , DECODE(p_secondary_currency, NULL, ar.sec_amount_g
2030 , ROUND((ar.amount_b*r.secondary_rate)
2031 /p_secondary_mau)*p_secondary_mau)
2032 , sysdate, p_user_id, p_user_id
2033 FROM FII_RECONV_RATES r
2034 WHERE r.request_id = p_request_id
2035 AND r.rate_date_id = ar.gl_date_id
2036 AND r.from_currency = ar.functional_currency
2037 )
2038 WHERE ar.amount_b <> 0
2039 AND ar.gl_date_id BETWEEN p_from_date_id AND p_to_date_id
2040 AND ( (p_primary_currency IS NOT NULL
2041 AND ar.prim_amount_g <>
2042 (SELECT ROUND((ar.amount_b*r2.primary_rate)
2043 /p_primary_mau)*p_primary_mau
2044 FROM FII_RECONV_RATES r2
2045 WHERE r2.request_id = p_request_id
2046 AND r2.rate_date_id = ar.gl_date_id
2047 AND r2.from_currency = ar.functional_currency))
2048 OR (p_secondary_currency IS NOT NULL
2049 AND ar.sec_amount_g <>
2050 (SELECT ROUND((ar.amount_b*r3.secondary_rate)
2051 /p_secondary_mau)*p_secondary_mau
2052 FROM FII_RECONV_RATES r3
2053 WHERE r3.request_id = p_request_id
2054 AND r3.rate_date_id = ar.gl_date_id
2055 AND r3.from_currency = ar.functional_currency)));
2056 l_processed_rows := SQL%ROWCOUNT;
2057 COMMIT;
2058
2059 -- LOG: Print out the number of updated rows for AR
2060 l_message := FII_MESSAGE.get_message('FII_RECONV_UPDATED_ROWS', NULL,
2061 'NUM_ROWS', NVL(l_processed_rows, 0),
2062 'PROD_NAME', p_product_name);
2063 FII_UTIL.put_line(l_message);
2064
2065 --
2066 -- 4.3 This process completes successfully
2067 --
2068 l_process_step := 'EXIT_PROCESS';
2069 -- LOG: FII_RECONV_EXIT_PROCESS_STEP || time stamp
2070 FII_UTIL.put_line(' ');
2071 FII_UTIL.put_timestamp(FII_MESSAGE.get_message(
2072 'FII_RECONV_EXIT_PROCESS_STEP', NULL));
2073 FII_UTIL.debug_line('Process complete successfully');
2074
2075 -- Commit changes to the database and print log messages
2076 FND_CONCURRENT.af_commit;
2077 -- Set the concurrent program completion status to COMPLETE
2078 l_return_val := FND_CONCURRENT.set_completion_status('COMPLETE', l_message);
2079
2080 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
2081
2082 EXCEPTION
2083 WHEN OTHERS THEN
2084 -- Process fails because of other exceptions and we need to rollback
2085 FII_UTIL.debug_line('Proces errors out because of other exception');
2086 -- Rollback
2087 FND_CONCURRENT.af_rollback;
2088 -- Print error messages to log file
2089 errbuf := sqlerrm;
2090 retcode := sqlcode;
2091 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, errbuf);
2092 -- Set the concurrent program completion status to ERROR
2093 l_return_val := FND_CONCURRENT.set_completion_status('ERROR', NULL);
2094 -- Raise application error
2095 RAISE_APPLICATION_ERROR(-20080, errbuf);
2096
2097 END reconvert_ar;
2098
2099
2100 -- PROCEDURE
2101 -- print_report()
2102 --
2103 -- DESCRIPTION:
2104 -- It will print the execution report for different modes. For ERROR mode, it
2105 -- will list all the missing/invalid rates and proper error messages. For
2106 -- SUCCESS mode, it will list all the cached rates and the number of rows
2107 -- updated for each base table.
2108 --
2109 -- It will return TRUE when the execution report is generated successfuly.
2110 -- Otherwise, it return FALSE.
2111 PROCEDURE print_report( p_request_id IN NUMBER
2112 , p_transaction_type IN VARCHAR2
2113 , p_currency_type IN VARCHAR2
2114 , p_primary_rate_type IN VARCHAR2
2115 , p_secondary_rate_type IN VARCHAR2
2116 , p_from_date IN VARCHAR2
2117 , p_to_date IN VARCHAR2
2118 , p_primary_currency IN VARCHAR2
2119 , p_secondary_currency IN VARCHAR2
2120 , p_cache_rate_status IN VARCHAR2
2121 , p_completion_status IN VARCHAR2
2122 ) IS
2123 CURSOR c_mrate_lines IS
2124 SELECT RPAD(sob_curr, 15, ' ')
2125 || ' ' || RPAD(global_curr, 15, ' ')
2126 || ' ' || TO_CHAR(rate_date, 'DD-MON-YYYY') line_text
2127 FROM (SELECT from_currency sob_curr
2128 , p_primary_currency global_curr
2129 , rate_date
2130 FROM FII_RECONV_RATES
2131 WHERE p_primary_currency IS NOT NULL
2132 AND request_id = p_request_id
2133 AND primary_rate <= 0
2134 UNION
2135 SELECT from_currency sob_curr
2136 , p_secondary_currency global_curr
2137 , rate_date
2138 FROM FII_RECONV_RATES
2139 WHERE p_secondary_currency IS NOT NULL
2140 AND request_id = p_request_id
2141 AND secondary_rate <= 0) Q
2142 ORDER BY sob_curr, global_curr, rate_date;
2143
2144 CURSOR c_crate_lines IS
2145 SELECT RPAD(from_currency, 15, ' ')
2146 || ' ' || TO_CHAR(rate_date, 'DD-MON-YYYY')
2147 || ' ' || LPAD(DECODE(SIGN(primary_rate), +1
2148 , TO_CHAR( ROUND(primary_rate, 15)
2149 , 'FM999999D999999999999999')
2150 , ' '), 22 , ' ')
2151 || ' ' || LPAD(DECODE(SIGN(secondary_rate), +1
2152 , TO_CHAR( ROUND(secondary_rate, 15)
2153 , 'FM999999D999999999999999')
2154 , ' '), 22, ' ') line_text
2155 FROM FII_RECONV_RATES
2156 WHERE request_id = p_request_id
2157 AND (NVL(primary_rate, -1) > 0 OR NVL(secondary_rate, -1) > 0)
2158 ORDER BY from_currency, rate_date;
2159
2160 l_print_common_msg BOOLEAN := TRUE;
2161 l_line_text VARCHAR2(140) := NULL;
2162 l_page_count NUMBER := 1;
2163 l_line_count NUMBER := 1;
2164
2165 l_process_step VARCHAR2(50) := 'PRINT_REPORT_HDR';
2166 l_procedure_name VARCHAR2(30) := 'print_report';
2167 BEGIN
2168 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
2169
2170 --
2171 -- 6.1 Print the report header
2172 --
2173 FII_RECONVERSION_PKG.print_report_hdr(l_line_count, l_page_count);
2174
2175 --
2176 -- 6.2 List the passed IN parameters
2177 --
2178 l_process_step := 'PRINT_IN_PARAMS';
2179 FND_FILE.new_line(FND_FILE.output, 1);
2180 FII_MESSAGE.write_output( 'FII_RECONV_TRX_TYPE_PARAM_P', 1
2181 , 'P_VALUE', p_transaction_type);
2182 FII_MESSAGE.write_output( 'FII_RECONV_CURR_TYPE_PARAM_P', 1
2183 , 'P_VALUE', p_currency_type);
2184 FII_MESSAGE.write_output( 'FII_RECONV_PRI_RTYPE_PARAM_P', 1
2185 , 'P_VALUE', p_primary_rate_type);
2186 FII_MESSAGE.write_output( 'FII_RECONV_SEC_RTYPE_PARAM_P', 1
2187 , 'P_VALUE', p_secondary_rate_type);
2188 FII_MESSAGE.write_output( 'FII_RECONV_FROM_DATE_PARAM_P', 1
2189 , 'P_VALUE', p_from_date);
2190 FII_MESSAGE.write_output('FII_RECONV_TO_DATE_PARAM_P', 1
2191 , 'P_VALUE', p_to_date);
2192 l_line_count := l_line_count + 7;
2193
2194 --
2195 -- 6.3 Print report content
2196 --
2197 IF (p_cache_rate_status = 'N')
2198 THEN
2199 -- There are no transactions selected for reconversion
2200 l_process_step := 'PRINT_NO_TRX_MSG';
2201 FND_FILE.new_line(FND_FILE.output, 1);
2202 -- Print to report
2203 FII_MESSAGE.write_output('FII_RECONV_NO_TRANSACTIONS');
2204 l_line_count := l_line_count + 2;
2205
2206 ELSE
2207 -- There are transactions selected for reconversion
2208
2209 -- Check if it needs to list out the missing rates
2210 IF (p_cache_rate_status = 'M')
2211 THEN
2212 -- THere are missing rates, so list missing rates
2213
2214 -- Print the error message
2215 l_process_step := 'PRINT_MRATE_MSG';
2216 FND_FILE.new_line(FND_FILE.output, 1);
2217 FII_MESSAGE.write_output('FII_RECONV_MISSING_RATES');
2218 FND_FILE.new_line(FND_FILE.output, 1);
2219 FII_MESSAGE.write_output('FII_RECONV_MRATE_LIST_PROMPT');
2220 l_line_count := l_line_count + 4;
2221
2222 -- Print Missing Rates table header
2223 l_process_step := 'PRINT_MTABLE_HDR';
2224 FII_RECONVERSION_PKG.print_mtable_hdr(l_line_count);
2225
2226 -- List SOB Currency, Global Currency, and Rate Date for missing rates
2227
2228 -- Open c_mrate_lines cursor
2229 l_process_step := 'OPEN_MRATE_CURSOR';
2230 OPEN c_mrate_lines;
2231
2232 -- Print each fetched line to report
2233 l_process_step := 'PRINT_MRATE_LINES';
2234 LOOP
2235 FETCH c_mrate_lines INTO l_line_text;
2236 EXIT WHEN c_mrate_lines%NOTFOUND;
2237
2238 IF (l_line_count = 1)
2239 THEN
2240 FII_RECONVERSION_PKG.print_report_hdr(l_line_count, l_page_count);
2241 FII_RECONVERSION_PKG.print_mtable_hdr(l_line_count);
2242 END IF;
2243
2244 FND_FILE.put_line(FND_FILE.output, l_line_text);
2245 l_line_count := l_line_count + 1;
2246
2247 IF (l_line_count >= g_page_line_no - 2)
2248 THEN
2249 l_line_count := 1;
2250 l_page_count := l_page_count + 1;
2251 FND_FILE.new_line(FND_FILE.output, 2);
2252 END IF;
2253
2254 END LOOP; -- c_mrate_lines cursor loop
2255
2256 -- Close c_mrate_lines cursor
2257 l_process_step := 'CLOSE_MRATE_CURSOR';
2258 CLOSE c_mrate_lines;
2259
2260 ELSE
2261 -- All rates are cached
2262 l_process_step := 'PRINT_CRATE_MSG';
2263 FND_FILE.new_line(FND_FILE.output, 1);
2264 IF (p_completion_status = 'S')
2265 THEN
2266 FII_MESSAGE.write_output('FII_RECONV_RECONVERT_SUCCESS');
2267 ELSE
2268 FII_MESSAGE.write_output('FII_RECONV_PROCESS_FAIL');
2269 END IF; -- IF (p_completion_status = 'N')
2270 l_line_count := l_line_count + 2;
2271
2272 END IF; -- IF (p_cache_rate_status = 'M')
2273
2274 -- Print cached rates
2275
2276 -- Open c_crate_lines cursor
2277 l_process_step := 'OPEN_CRATE_CURSOR';
2278 OPEN c_crate_lines;
2279
2280 -- Print each fetched cached rate line to report
2281 l_process_step := 'PRINT_CRATE_LINES';
2282 LOOP
2283 FETCH c_crate_lines INTO l_line_text;
2284 EXIT WHEN c_crate_lines%NOTFOUND;
2285
2286 -- Only print the common msg for the first line
2287 IF (l_print_common_msg)
2288 THEN
2289 -- Print common message
2290 FND_FILE.new_line(FND_FILE.output, 1);
2291 FII_MESSAGE.write_output('FII_RECONV_CRATE_LIST_PROMPT');
2292 l_line_count := l_line_count + 2;
2293
2294 -- Print Cached Rates table header
2295 FII_RECONVERSION_PKG.print_ctable_hdr(l_line_count);
2296
2297 -- Set it to FALSE such that it won't print these again
2298 l_print_common_msg := FALSE;
2299 END IF; -- IF (l_print_common_msg)
2300
2301 IF (l_line_count = 1)
2302 THEN
2303 FII_RECONVERSION_PKG.print_report_hdr(l_line_count, l_page_count);
2304 FII_RECONVERSION_PKG.print_ctable_hdr(l_line_count);
2305 END IF;
2306
2307 FND_FILE.put_line(FND_FILE.output, l_line_text);
2308 l_line_count := l_line_count + 1;
2309
2310 IF (l_line_count >= g_page_line_no - 2)
2311 THEN
2312 l_line_count := 1;
2313 l_page_count := l_page_count + 1;
2314 FND_FILE.new_line(FND_FILE.output, 2);
2315 END IF;
2316 END LOOP; -- c_crate_lines cursor loop
2317
2318 -- Close c_crate_lines cursor
2319 l_process_step := 'CLOSE_CRATE_CURSOR';
2320 CLOSE c_crate_lines;
2321
2322 END IF; -- IF (p_cache_rate_status = 'N')
2323
2324 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
2325 EXCEPTION
2326 WHEN RECONV_ERROR THEN
2327 -- Print error code and messages to log file and raise error
2328 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step);
2329 -- Raise RECONV_ERROR which will be handled in reconvert_amounts()
2330 RAISE RECONV_ERROR;
2331
2332 WHEN OTHERS THEN
2333 -- Print error code and messages to log file and raise error
2334 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, sqlerrm);
2335 -- Raise RECONV_ERROR which will be handled in reconvert_amounts()
2336 RAISE RECONV_ERROR;
2337
2338 END print_report;
2339
2340 -- PROCEDURE
2341 -- print_report_hdr()
2342 --
2343 -- PARAMETERS:
2344 -- None
2345 --
2346 -- DESCRIPTION:
2347 -- It will print the report header.
2348 PROCEDURE print_report_hdr ( p_line_count IN OUT NOCOPY NUMBER
2349 , p_page_count IN NUMBER) IS
2350 l_message VARCHAR2(200);
2351 l_line_text VARCHAR2(200);
2352
2353 l_process_step VARCHAR2(50) := 'APPEND_CURRENT_DATE';
2354 l_procedure_name VARCHAR2(30) := 'print_report_hdr';
2355 BEGIN
2356 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
2357
2358 -- 6.1.1 Print 1 blank line
2359 FND_FILE.new_line(FND_FILE.output, 1);
2360
2361 -- 6.1.2 Print 1 report header line
2362
2363 -- Set the line starting with the report date (i.e. current date)
2364 l_message := FND_MESSAGE.get_string('FND', 'DATE') || ': '
2365 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS');
2366 l_line_text := RPAD(l_message, 36, ' ');
2367
2368 -- Append report title
2369 l_process_step := 'APPEND_TITLE';
2370 l_message := FND_MESSAGE.get_string('FII', 'FII_RECONV_REPORT_TITLE');
2371 l_line_text := l_line_text || LPAD(l_message, 48, ' ');
2372
2373 -- Append page number
2374 l_process_step := 'APPEND_PAGE_NUMBER';
2375 l_message := FII_MESSAGE.get_message( 'FII_RECONV_PAGE_PROMPT', NULL
2376 , 'P_NUM', p_page_count);
2377 l_line_text := l_line_text || LPAD(l_message, 49, ' ');
2378
2379 -- Print the header line
2380 l_process_step := 'PRINT_HEADER_LINE';
2381 FND_FILE.put_line(FND_FILE.output, l_line_text);
2382
2383 -- Added 2 more lines
2384 p_line_count := p_line_count + 2;
2385
2386 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
2387 EXCEPTION
2388 WHEN OTHERS THEN
2389 -- Print error code and messages to log file and raise error
2390 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, sqlerrm);
2391 -- Raise RECONV_ERROR which will be handled in reconvert_amounts()
2392 RAISE RECONV_ERROR;
2393
2394 END print_report_hdr;
2395
2396
2397 -- PROCEDURE
2398 -- print_mtable_hdr()
2399 --
2400 -- PARAMETERS:
2401 -- None
2402 --
2403 -- DESCRIPTION:
2404 -- It will print the missing conversion rate table header.
2405 PROCEDURE print_mtable_hdr (p_line_count IN OUT NOCOPY NUMBER) IS
2406 l_process_step VARCHAR2(50) := 'PRINT_MISSING_RATES_HDR';
2407 l_procedure_name VARCHAR2(30) := 'print_mtable_hdr';
2408 BEGIN
2409 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
2410
2411 -- 6.3.1 Print 1 blank line
2412 FND_FILE.new_line(FND_FILE.output, 1);
2413 -- 6.3.2 Print missing rate table column header
2414 FII_MESSAGE.write_output('FII_RECONV_MTABLE_COLS');
2415 -- 6.3.3 Print missing rate table column line header
2416 FII_MESSAGE.write_output('FII_RECONV_MTABLE_COL_LINE');
2417 p_line_count := p_line_count + 3;
2418
2419 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
2420 EXCEPTION
2421 WHEN OTHERS THEN
2422 -- Print error code and messages to log file and raise error
2423 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, sqlerrm);
2424 -- Raise RECONV_ERROR which will be handled in reconvert_amounts()
2425 RAISE RECONV_ERROR;
2426
2427 END print_mtable_hdr;
2428
2429
2430 -- PROCEDURE
2431 -- print_ctable_hdr()
2432 --
2433 -- PARAMETERS:
2434 -- None
2435 --
2436 -- DESCRIPTION:
2437 -- It will print the cached conversion rate table header.
2438 PROCEDURE print_ctable_hdr (p_line_count IN OUT NOCOPY NUMBER) IS
2439 l_process_step VARCHAR2(50) := 'PRINT_CACHED_RATES_HDR';
2440 l_procedure_name VARCHAR2(30) := 'print_ctable_hdr';
2441 BEGIN
2442 FII_RECONVERSION_PKG.func_enter(l_procedure_name);
2443
2444 -- 6.4.1 Print 1 blank line
2445 FND_FILE.new_line(FND_FILE.output, 1);
2446 -- 6.4.2 Print cached rate table column header
2447 FII_MESSAGE.write_output('FII_RECONV_CTABLE_COLS');
2448 -- 6.4.3 Print cached rate table column line header
2449 FII_MESSAGE.write_output('FII_RECONV_CTABLE_COL_LINE');
2450 p_line_count := p_line_count + 3;
2451
2452 FII_RECONVERSION_PKG.func_succ(l_procedure_name);
2453 EXCEPTION
2454 WHEN OTHERS THEN
2455 -- Print error code and messages to log file and raise error
2456 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, sqlerrm);
2457 -- Raise RECONV_ERROR which will be handled in reconvert_amounts()
2458 RAISE RECONV_ERROR;
2459
2460 END print_ctable_hdr;
2461
2462
2463 -- PROCEDURE
2464 -- print_sql()
2465 --
2466 -- DESCRIPTION:
2467 -- It will print the passed SQL statement to log file.
2468 PROCEDURE print_sql( p_sql_desc IN VARCHAR2
2469 , p_sql_stmt IN DBMS_SQL.VARCHAR2S
2470 , p_num_of_lines IN NUMBER) IS
2471 i NUMBER;
2472
2473 l_process_step VARCHAR2(50) := 'PRINT_BUILT_SQL';
2474 l_procedure_name VARCHAR2(30) := 'print_sql';
2475 BEGIN
2476 FII_UTIL.debug_line(' ');
2477 FII_UTIL.debug_line(p_sql_desc);
2478 FOR i IN 1..p_num_of_lines
2479 LOOP
2480 FII_UTIL.debug_line(p_sql_stmt(i));
2481 END LOOP;
2482 FII_UTIL.debug_line(' ');
2483 EXCEPTION
2484 WHEN OTHERS THEN
2485 -- Print error code and messages to log file and raise error
2486 FII_RECONVERSION_PKG.func_fail(l_procedure_name, l_process_step, sqlerrm);
2487 -- Raise RECONV_ERROR which will be handled in reconvert_amounts()
2488 RAISE RECONV_ERROR;
2489
2490 END print_sql;
2491
2492 -- PROCEDURE
2493 -- func_enter()
2494 --
2495 -- DESCRIPTION:
2496 -- It will print some customerized output to log and then call
2497 -- FII_MESSAGE.func_enter() to print standard output for entering function
2498 PROCEDURE func_enter(p_func_name IN VARCHAR2) IS
2499 BEGIN
2500 FII_UTIL.put_line(' ');
2501 FII_MESSAGE.func_ent(p_func_name);
2502 END func_enter;
2503
2504
2505 -- PROCEDURE
2506 -- func_succ()
2507 --
2508 -- DESCRIPTION:
2509 -- It will print some customerized output to log and then call
2510 -- FII_MESSAGE.func_succ() to print standard output for exiting function
2511 -- successfully
2512 PROCEDURE func_succ(p_func_name IN VARCHAR2) IS
2513 BEGIN
2514 FII_MESSAGE.func_succ(p_func_name);
2515 FII_UTIL.put_line(' ');
2516 END func_succ;
2517
2518
2519 -- PROCEDURE
2520 -- func_fail()
2521 --
2522 -- DESCRIPTION:
2523 -- It will print some additional output/error message to log and then call
2524 -- FII_MESSAGE.func_fail() to print standard output for exiting function with
2525 -- error
2526 PROCEDURE func_fail( p_func_name IN VARCHAR2
2527 , p_debug_step IN VARCHAR2
2528 , p_err_msg IN VARCHAR2) IS
2529 BEGIN
2530 -- Print the debug step when it's in debug mode
2531 FII_UTIL.debug_line(' ');
2532 FII_UTIL.debug_line('Raise error while ' || p_debug_step);
2533
2534 -- Print the error message
2535 FII_UTIL.put_line(' ');
2536 FII_UTIL.put_line(FII_MESSAGE.get_message(
2537 'FII_ERR_ENC_ROUT', NULL
2538 , 'ROUTINE_NAME', p_func_name || '()'));
2539 IF (p_err_msg IS NOT NULL)
2540 THEN
2541 FII_UTIL.put_line(p_err_msg);
2542 END IF;
2543 FII_UTIL.put_line(' ');
2544
2545 -- Print the standard output
2546 FII_MESSAGE.func_fail(p_func_name);
2547 FII_UTIL.put_line(' ');
2548 END func_fail;
2549
2550 END FII_RECONVERSION_PKG;