[Home] [Help]
PACKAGE BODY: APPS.FEM_INTG_BAL_RULE_ENG_PKG
Source
1 PACKAGE BODY FEM_INTG_BAL_RULE_ENG_PKG AS
2 /* $Header: fem_intg_bal_eng.plb 120.5 2006/11/21 11:37:38 hakumar noship $ */
3
4 -- -------------------------
5 -- Private Package Variables
6 -- -------------------------
7
8 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
9 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
10 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
11 pc_log_level_exception CONSTANT NUMBER := FND_LOG.level_exception;
12 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
13 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
14 pc_page_line_no CONSTANT NUMBER := 45;
15
16 pv_page_count NUMBER;
17 pv_line_count NUMBER;
18 pv_curr_list VARCHAR2(30);
19
20 OGLEngMain_FatalErr EXCEPTION;
21 OGLEngMain_NoData EXCEPTION;
22
23 -- -------------------------------
24 -- Private Procedures Declarations
25 -- -------------------------------
26 PROCEDURE Generate_Report
27 ( x_completion_code OUT NOCOPY VARCHAR2
28 , p_from_period IN VARCHAR2
29 , p_to_period IN VARCHAR2
30 , p_effective_date IN VARCHAR2
31 , p_bsv_range_low IN VARCHAR2
32 , p_bsv_range_high IN VARCHAR2
33 , p_tot_rows_inserted IN NUMBER
34 , p_tot_rows_valid IN NUMBER
35 , p_tot_rows_posted IN NUMBER);
36
37 PROCEDURE Print_Report_Hdr;
38
39 PROCEDURE Write_Message
40 ( p_app_name IN VARCHAR2
41 , p_msg_name IN VARCHAR2
42 , p_token1 IN VARCHAR2 DEFAULT NULL
43 , p_value1 IN VARCHAR2 DEFAULT NULL
44 , p_trans1 IN VARCHAR2 DEFAULT NULL
45 , p_token2 IN VARCHAR2 DEFAULT NULL
46 , p_value2 IN VARCHAR2 DEFAULT NULL
47 , p_trans2 IN VARCHAR2 DEFAULT NULL
48 , p_token3 IN VARCHAR2 DEFAULT NULL
49 , p_value3 IN VARCHAR2 DEFAULT NULL
50 , p_trans3 IN VARCHAR2 DEFAULT NULL
51 , p_token4 IN VARCHAR2 DEFAULT NULL
52 , p_value4 IN VARCHAR2 DEFAULT NULL
53 , p_trans4 IN VARCHAR2 DEFAULT NULL
54 , p_token5 IN VARCHAR2 DEFAULT NULL
55 , p_value5 IN VARCHAR2 DEFAULT NULL
56 , p_trans5 IN VARCHAR2 DEFAULT NULL
57 , p_token6 IN VARCHAR2 DEFAULT NULL
58 , p_value6 IN VARCHAR2 DEFAULT NULL
59 , p_trans6 IN VARCHAR2 DEFAULT NULL
60 , p_token7 IN VARCHAR2 DEFAULT NULL
61 , p_value7 IN VARCHAR2 DEFAULT NULL
62 , p_trans7 IN VARCHAR2 DEFAULT NULL
63 , p_token8 IN VARCHAR2 DEFAULT NULL
64 , p_value8 IN VARCHAR2 DEFAULT NULL
65 , p_trans8 IN VARCHAR2 DEFAULT NULL
66 , p_token9 IN VARCHAR2 DEFAULT NULL
67 , p_value9 IN VARCHAR2 DEFAULT NULL
68 , p_trans9 IN VARCHAR2 DEFAULT NULL);
69
70 PROCEDURE Write_New_Line;
71
72 PROCEDURE Write_Line
73 (p_line_text IN VARCHAR2);
74
75 -- -----------------
76 -- Public Procedures
77 -- -----------------
78
79 --
80 -- Procedure
81 -- Main
82 -- Purpose
83 -- This is the main routine of the FEM-OGL Integration Balances Rule
84 -- Processing Engine program
85 -- History
86 -- 11-12-04 L Poon Created
87 -- Arguments
88 -- x_errbuf : Output parameter required by Concurrent Manager
89 -- x_retcode : Output parameter required by Concurrent Manager
90 -- p_bal_rule_obj_def_id: Balances rule version to be run
91 -- p_from_period : First period from which balances will be loaded
92 -- p_to_period : Last period from which balances will be loaded
93 -- p_effective_date : Effective date to calculate the average balances
94 -- p_bsv_range_low : First balancing segment value which balances will
95 -- be loaded
96 -- p_bsv_range_high : Last balancing segment value which balances will
97 -- be loaded
98 PROCEDURE Main
99 ( x_errbuf OUT NOCOPY VARCHAR2
100 , x_retcode OUT NOCOPY VARCHAR2
101 , p_bal_rule_obj_def_id IN VARCHAR2
102 , p_coa_id IN VARCHAR2
103 , p_from_period IN VARCHAR2
104 , p_to_period IN VARCHAR2
105 , p_effective_date IN VARCHAR2
106 , p_bsv_range_low IN VARCHAR2
107 , p_bsv_range_high IN VARCHAR2) IS
108 v_module VARCHAR2(100);
109 v_func_name VARCHAR2(80);
110
111 v_completion_code NUMBER;
112 v_completion_status VARCHAR2(30);
113 v_return_status BOOLEAN;
114
115 v_effective_date DATE;
116 v_num_rows_inserted NUMBER;
117 v_num_rows_deleted NUMBER;
118 v_tot_rows_inserted NUMBER;
119 v_tot_rows_valid NUMBER;
120 v_tot_rows_posted NUMBER;
121
122 v_generate_report_flag VARCHAR2(1);
123 v_require_rollback_flag VARCHAR2(1);
124
125 v_param_list wf_parameter_list_t;
126
127 CURSOR req_cur IS
128 SELECT TO_CHAR(REQUEST_ID) REQUEST_ID,
129 PERIOD_NAME,
130 TO_CHAR(CAL_PERIOD_ID) CAL_PERIOD_ID,
131 LOAD_METHOD_CODE
132 FROM FEM_INTG_EXEC_PARAMS_GT
133 WHERE REQUEST_ID IS NOT NULL
134 AND NUM_OF_ROWS_POSTED > 0;
135
136 v_bsv_range_low VARCHAR2(200);
137 v_bsv_range_high VARCHAR2(200);
138
139 BEGIN
140 v_module := 'fem.plsql.fem_intg_bal_eng.main';
141 v_func_name := 'FEM_INTG_BAL_RULE_ENG_PKG.Main';
142
143 -- Log the function entry time to FND_LOG
144 FEM_ENGINES_PKG.Tech_Message
145 (p_severity => pc_log_level_procedure,
146 p_module => v_module,
147 p_app_name => 'FEM',
148 p_msg_name => 'FEM_GL_POST_201',
149 p_token1 => 'FUNC_NAME',
150 p_value1 => v_func_name,
151 p_token2 => 'TIME',
152 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
153
154 -- ---------------------------------------------------------
155 -- 1. List the engine parameters and their values to FND_LOG
156 -- ---------------------------------------------------------
157 FEM_ENGINES_PKG.Tech_Message
158 (p_severity => pc_log_level_statement,
159 p_module => v_module,
160 p_app_name => 'FEM',
161 p_msg_name => 'FEM_GL_POST_204',
162 p_token1 => 'VAR_NAME',
163 p_value1 => 'p_bal_rule_obj_def_id',
164 p_token2 => 'VAR_VAL',
165 p_value2 => p_bal_rule_obj_def_id);
166
167 FEM_ENGINES_PKG.Tech_Message
168 (p_severity => pc_log_level_statement,
169 p_module => v_module,
170 p_app_name => 'FEM',
171 p_msg_name => 'FEM_GL_POST_204',
172 p_token1 => 'VAR_NAME',
173 p_value1 => 'p_from_period',
174 p_token2 => 'VAR_VAL',
175 p_value2 => p_from_period);
176
177 FEM_ENGINES_PKG.Tech_Message
178 (p_severity => pc_log_level_statement,
179 p_module => v_module,
180 p_app_name => 'FEM',
181 p_msg_name => 'FEM_GL_POST_204',
182 p_token1 => 'VAR_NAME',
183 p_value1 => 'p_to_period',
184 p_token2 => 'VAR_VAL',
185 p_value2 => p_to_period);
186
187 FEM_ENGINES_PKG.Tech_Message
188 (p_severity => pc_log_level_statement,
189 p_module => v_module,
190 p_app_name => 'FEM',
191 p_msg_name => 'FEM_GL_POST_204',
192 p_token1 => 'VAR_NAME',
193 p_value1 => 'p_effective_date',
194 p_token2 => 'VAR_VAL',
195 p_value2 => p_effective_date);
196
197 FEM_ENGINES_PKG.Tech_Message
198 (p_severity => pc_log_level_statement,
199 p_module => v_module,
200 p_app_name => 'FEM',
201 p_msg_name => 'FEM_GL_POST_204',
202 p_token1 => 'VAR_NAME',
203 p_value1 => 'p_bsv_range_low',
204 p_token2 => 'VAR_VAL',
205 p_value2 => p_bsv_range_low);
206
207 FEM_ENGINES_PKG.Tech_Message
208 (p_severity => pc_log_level_statement,
209 p_module => v_module,
210 p_app_name => 'FEM',
211 p_msg_name => 'FEM_GL_POST_204',
212 p_token1 => 'VAR_NAME',
213 p_value1 => 'p_bsv_range_high',
214 p_token2 => 'VAR_VAL',
215 p_value2 => p_bsv_range_high);
216
217 -- -----------------------------
218 -- 2. Initialize local variables
219 -- -----------------------------
220 IF p_bsv_range_low IS NOT NULL AND p_bsv_range_high IS NOT NULL THEN
221 v_bsv_range_low := '''' || REPLACE(p_bsv_range_low, '''', '''''') || '''';
222 v_bsv_range_high := '''' || REPLACE(p_bsv_range_high, '''', '''''') || '''';
223 END IF;
224
225 v_completion_status := 'NORMAL';
226 v_effective_date := TO_DATE(substr(p_effective_date, 1, 10), 'YYYY/MM/DD');
227 v_num_rows_inserted := 0;
228 v_tot_rows_inserted := 0;
229 v_tot_rows_valid := 0;
230 v_tot_rows_posted := 0;
231 -- Set Generate Report Flag to No i.e. indicating report should not be
232 -- generated when erroring out
233 v_generate_report_flag := 'N';
234 -- Set Require Rollback Flag to No i.e. indicating it doesn't need to
235 -- rollback changes when erroring out
236 v_require_rollback_flag := 'N';
237
238 -- -----------------------------
239 -- 3. Validate engine parameters
240 -- -----------------------------
241 FEM_ENGINES_PKG.Tech_Message
242 (p_severity => pc_log_level_event,
243 p_module => v_module,
244 p_app_name => 'FEM',
245 p_msg_name => 'FEM_GL_POST_207');
246 FEM_ENGINES_PKG.User_Message
247 (p_app_name => 'FEM',
248 p_msg_name => 'FEM_GL_POST_207');
249
250 -- Initalize v_completion_code to 0 before calling the API
251 v_completion_code := 0;
252 FEM_GL_POST_PROCESS_PKG.Validate_OGL_Eng_Parameters
253 (p_bal_rule_obj_def_id => TO_NUMBER(p_bal_rule_obj_def_id),
254 p_from_period => p_from_period,
255 p_to_period => p_to_period,
256 p_effective_date => v_effective_date,
257 p_bsv_range_low => p_bsv_range_low,
258 p_bsv_range_high => p_bsv_range_high,
259 x_generate_report_flag => v_generate_report_flag,
260 x_completion_code => v_completion_code);
261
262 IF v_completion_code = 1
263 THEN
264 v_completion_status := 'WARNING';
265 ELSIF v_completion_code = 2
266 THEN
267 RAISE OGLEngMain_FatalErr;
268 END IF; -- IF v_completion_code = 1
269
270 -- -----------------------------
271 -- 4. Register process execution
272 -- -----------------------------
273 FEM_ENGINES_PKG.Tech_Message
274 (p_severity => pc_log_level_event,
275 p_module => v_module,
276 p_app_name => 'FEM',
277 p_msg_name => 'FEM_GL_POST_208');
278 FND_FILE.NEW_LINE(fnd_file.log);
279 FEM_ENGINES_PKG.User_Message
280 (p_app_name => 'FEM',
281 p_msg_name => 'FEM_GL_POST_208');
282
283 -- Reset v_completion_code to 0 before calling the API
284 v_completion_code := 0;
285 FEM_GL_POST_PROCESS_PKG.Register_OGL_Process_Execution
286 (x_completion_code => v_completion_code);
287
288 IF v_completion_code = 1
289 THEN
290 v_completion_status := 'WARNING';
291 ELSIF v_completion_code = 2
292 THEN
293 RAISE OGLEngMain_FatalErr;
294 END IF; -- IF v_completion_code = 1
295
296 -- -------------------------
297 -- 5. Load standard balances
298 -- -------------------------
299 FEM_ENGINES_PKG.Tech_Message
300 (p_severity => pc_log_level_event,
301 p_module => v_module,
302 p_app_name => 'FEM',
303 p_msg_name => 'FEM_INTG_BAL_LOAD_STD');
304 FND_FILE.NEW_LINE(fnd_file.log);
305 FEM_ENGINES_PKG.User_Message
306 (p_app_name => 'FEM',
307 p_msg_name => 'FEM_INTG_BAL_LOAD_STD');
308
309 -- Reset v_completion_code to 0 before calling the API
310 v_completion_code := 0;
311 FEM_INTG_BAL_ENG_LOAD_PKG.Load_Std_Balances
312 (x_completion_code => v_completion_code,
313 x_num_rows_inserted => v_num_rows_inserted,
314 p_bsv_range_low => v_bsv_range_low,
315 p_bsv_range_high => v_bsv_range_high,
316 p_maintain_qtd => FEM_GL_POST_PROCESS_PKG.pv_maintain_qtd_flag);
317
318 FEM_ENGINES_PKG.Tech_Message
319 (p_severity => pc_log_level_statement,
320 p_module => v_module,
321 p_app_name => 'FEM',
322 p_msg_name => 'FEM_GL_POST_204',
323 p_token1 => 'VAR_NAME',
324 p_value1 => 'v_num_rows_inserted',
325 p_token2 => 'VAR_VAL',
326 p_value2 => v_num_rows_inserted);
327
328 -- Set the total number of rows inserted
329 v_tot_rows_inserted := v_num_rows_inserted;
330 FEM_ENGINES_PKG.Tech_Message
331 (p_severity => pc_log_level_statement,
332 p_module => v_module,
333 p_app_name => 'FEM',
334 p_msg_name => 'FEM_GL_POST_204',
335 p_token1 => 'VAR_NAME',
336 p_value1 => 'v_tot_rows_inserted',
337 p_token2 => 'VAR_VAL',
338 p_value2 => v_tot_rows_inserted);
339
340 IF v_completion_code = 1
341 THEN
342 v_completion_status := 'WARNING';
343 ELSIF v_completion_code = 2
344 THEN
345 RAISE OGLEngMain_FatalErr;
346 END IF; -- IF v_completion_code = 1
347
348 -- --------------------------------------------------------------------
349 -- 6. Load average balances if the Include Average Balances Flag is Yes
350 -- --------------------------------------------------------------------
351 IF (FEM_GL_POST_PROCESS_PKG.pv_include_avg_bal = 'Y')
352 THEN
353 FEM_ENGINES_PKG.Tech_Message
354 (p_severity => pc_log_level_event,
355 p_module => v_module,
356 p_app_name => 'FEM',
357 p_msg_name => 'FEM_INTG_BAL_LOAD_AVG');
358 FND_FILE.NEW_LINE(fnd_file.log);
359 FEM_ENGINES_PKG.User_Message
360 (p_app_name => 'FEM',
361 p_msg_name => 'FEM_INTG_BAL_LOAD_AVG');
362
363 -- Reset v_completion_code to 0 before calling the API
364 v_completion_code := 0;
365 FEM_INTG_BAL_ENG_LOAD_PKG.Load_Avg_Balances
366 (x_completion_code => v_completion_code,
367 x_num_rows_inserted => v_num_rows_inserted,
368 p_effective_date => v_effective_date,
369 p_bsv_range_low => v_bsv_range_low,
370 p_bsv_range_high => v_bsv_range_high);
371
372 FEM_ENGINES_PKG.Tech_Message
373 (p_severity => pc_log_level_statement,
374 p_module => v_module,
375 p_app_name => 'FEM',
376 p_msg_name => 'FEM_GL_POST_204',
377 p_token1 => 'VAR_NAME',
378 p_value1 => 'v_num_rows_inserted',
379 p_token2 => 'VAR_VAL',
380 p_value2 => v_num_rows_inserted);
381
382 -- Set the total number of rows inserted
383 v_tot_rows_inserted := v_tot_rows_inserted + v_num_rows_inserted;
384
385 FEM_ENGINES_PKG.Tech_Message
386 (p_severity => pc_log_level_statement,
387 p_module => v_module,
388 p_app_name => 'FEM',
389 p_msg_name => 'FEM_GL_POST_204',
390 p_token1 => 'VAR_NAME',
391 p_value1 => 'v_tot_rows_inserted',
392 p_token2 => 'VAR_VAL',
393 p_value2 => v_tot_rows_inserted);
394
395 IF v_completion_code = 1
396 THEN
397 v_completion_status := 'WARNING';
398 ELSIF v_completion_code = 2
399 THEN
400 RAISE OGLEngMain_FatalErr;
401 END IF; -- IF v_completion_code = 1
402
403 END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_include_avg_bal = 'Y')
404
405 -- ------------------------------------------------------------
406 -- 9. Perform post-processing if the Currency Option is Entered
407 -- ------------------------------------------------------------
408
409 -- Bug fix 4330205: Changed to perform post-processing if the Balacne Type
410 -- Actual and the Currency Option is Entered
411 IF (FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED'
412 AND FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd = 'ACTUAL')
413 THEN
414 -- Perform post-processing on FEM_BAL_POST_INTERIM_GT to back out foreign
415 -- converted amounts from the functional entered balances
416 FEM_ENGINES_PKG.Tech_Message
417 (p_severity => pc_log_level_event,
418 p_module => v_module,
419 p_app_name => 'FEM',
420 p_msg_name => 'FEM_INTG_BAL_POST_PROC');
421 FND_FILE.NEW_LINE(fnd_file.log);
422 FEM_ENGINES_PKG.User_Message
423 (p_app_name => 'FEM',
424 p_msg_name => 'FEM_INTG_BAL_POST_PROC');
425
426 -- Reset v_completion_code to 0 before calling the API
427 v_completion_code := 0;
428 FEM_INTG_BAL_ENG_LOAD_PKG.Load_Post_Process
429 (x_completion_code => v_completion_code);
430
431 IF v_completion_code = 1
432 THEN
433 v_completion_status := 'WARNING';
434 ELSIF v_completion_code = 2
435 THEN
436 RAISE OGLEngMain_FatalErr;
437 END IF; -- IF v_completion_code = 1
438
439 END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED')
440
441 -- -------------------------------------------
442 -- 6.9. Remove zero-balance rows
443 -- -------------------------------------------
444
445 DELETE FROM fem_bal_post_interim_gt
446 WHERE nvl(xtd_balance_e,0) = 0
447 AND nvl(xtd_balance_f,0) = 0
448 AND nvl(ytd_balance_e,0) = 0
449 AND nvl(ytd_balance_f,0) = 0
450 AND nvl(qtd_balance_e,0) = 0
451 AND nvl(qtd_balance_f,0) = 0
452 AND nvl(ptd_debit_balance_e,0) = 0
453 AND nvl(ptd_credit_balance_e,0) = 0
454 AND nvl(ytd_debit_balance_e,0) = 0
455 AND nvl(ytd_credit_balance_e,0) = 0;
456
457 v_num_rows_deleted := SQL%ROWCOUNT;
458
459 FEM_ENGINES_PKG.Tech_Message
460 ( p_severity => pc_log_level_event,
461 p_module => v_module,
462 p_app_name => 'FEM',
463 p_msg_text => 'Removed ' || TO_CHAR(v_num_rows_deleted) ||
464 ' zero-balance rows from FEM_BAL_POST_INTERIM_GT');
465
466 v_tot_rows_inserted := v_tot_rows_inserted - v_num_rows_deleted;
467
468 -- -------------------------------------------
469 -- 7. Check if there are any rows to be posted
470 -- -------------------------------------------
471
472 -- Bug fix 4330346: Changed to update the number of rows selected and
473 -- balances selected to 0 for each valid execution
474 -- parameter if no rows are inserted; else, update them
475 -- according to the rows inserted into the posting interim
476 -- table
477
478 -- If there are no rows inserted i.e. no data to be posted
479 IF (v_tot_rows_inserted = 0)
480 THEN
481 -- There are no rows inserted from OGL into the posting interim table,
482 -- so set the number of rows selected and balances selected for each
483 -- valid execution parameter to 0
484 UPDATE FEM_INTG_EXEC_PARAMS_GT
485 SET NUM_OF_ROWS_SELECTED = 0
486 , SELECTED_PTD_DR_BAL = 0
487 , SELECTED_PTD_CR_BAL = 0
488 WHERE ERROR_CODE IS NULL
489 AND REQUEST_ID IS NOT NULL;
490
491 -- Log the number of rows updated in FEM_INTG_EXEC_PARAMS_GT
492 FEM_ENGINES_PKG.Tech_Message
493 (p_severity => pc_log_level_statement,
494 p_module => v_module,
495 p_app_name => 'FEM',
496 p_msg_name => 'FEM_GL_POST_217',
497 p_token1 => 'NUM',
498 p_value1 => TO_CHAR(SQL%ROWCOUNT),
499 p_token2 => 'TABLE',
500 p_value2 => 'FEM_INTG_EXEC_PARAMS_GT');
501
502 -- Raise exception to skip all posting process and jump to print the
503 -- report, perform final process logging, and exit the program with
504 -- Warning status
505 Raise OGLEngMain_NoData;
506
507 ELSE
508 -- At least one row is inserted from OGL into the posting interim table,
509 -- so find the number of rows selected and balances selected for each
510 -- valid execution parameter
511 UPDATE FEM_INTG_EXEC_PARAMS_GT param
512 SET ( NUM_OF_ROWS_SELECTED
513 , SELECTED_PTD_DR_BAL
514 , SELECTED_PTD_CR_BAL) =
515 (SELECT COUNT(*)
516 , SUM(NVL(bpi.PTD_DEBIT_BALANCE_E, 0))
517 , SUM(NVL(bpi.PTD_CREDIT_BALANCE_E, 0))
518 FROM FEM_BAL_POST_INTERIM_GT bpi
519 WHERE bpi.DATASET_CODE = param.OUTPUT_DATASET_CODE
520 AND bpi.CAL_PERIOD_ID = param.CAL_PERIOD_ID)
521 WHERE param.ERROR_CODE IS NULL
522 AND param.REQUEST_ID IS NOT NULL;
523
524 -- Log the number of rows updated in FEM_INTG_EXEC_PARAMS_GT
525 FEM_ENGINES_PKG.Tech_Message
526 (p_severity => pc_log_level_statement,
527 p_module => v_module,
528 p_app_name => 'FEM',
529 p_msg_name => 'FEM_GL_POST_217',
530 p_token1 => 'NUM',
531 p_value1 => TO_CHAR(SQL%ROWCOUNT),
532 p_token2 => 'TABLE',
533 p_value2 => 'FEM_INTG_EXEC_PARAMS_GT');
534
535 END IF;
536
537 -- ---------------------------------------
538 -- 8. Check if there are any invalid CCIDs
539 -- ---------------------------------------
540
541 -- Get the number of valid rows in the posting interim table
542 SELECT COUNT(*)
543 INTO v_tot_rows_valid
544 FROM FEM_BAL_POST_INTERIM_GT
545 WHERE POSTING_ERROR_FLAG = 'N';
546
547 IF (v_tot_rows_valid = 0)
548 THEN
549 -- All CCIDs are not properly mapped
550 -- Log the error messages
551 FEM_ENGINES_PKG.Tech_Message
552 (p_severity => pc_log_level_error,
553 p_module => v_module,
554 p_app_name => 'FEM',
555 p_msg_name => 'FEM_INTG_BAL_ALL_CCID_ERR',
556 p_token1 => 'COA_NAME',
557 p_value1 => FEM_GL_POST_PROCESS_PKG.pv_coa_name);
558 FEM_ENGINES_PKG.User_Message
559 (p_app_name => 'FEM',
560 p_msg_name => 'FEM_INTG_BAL_ALL_CCID_ERR',
561 p_token1 => 'COA_NAME',
562 p_value1 => FEM_GL_POST_PROCESS_PKG.pv_coa_name);
563
564 RAISE OGLEngMain_FatalErr;
565
566 -- Bug fix 4313386: Changed to raise error if any CCID is not mapped
567 -- properly regardless the execution mode
568 ELSIF (v_tot_rows_inserted > v_tot_rows_valid)
569 THEN
570 -- There is at least one CCIDs not properly mapped
571 -- Log the error messags
572 FEM_ENGINES_PKG.Tech_Message
573 (p_severity => pc_log_level_error,
574 p_module => v_module,
575 p_app_name => 'FEM',
576 p_msg_name => 'FEM_INTG_BAL_SNAP_CCID_ERR',
577 p_token1 => 'COA_NAME',
578 p_value1 => FEM_GL_POST_PROCESS_PKG.pv_coa_name);
579 FEM_ENGINES_PKG.User_Message
580 (p_app_name => 'FEM',
581 p_msg_name => 'FEM_INTG_BAL_SNAP_CCID_ERR',
582 p_token1 => 'COA_NAME',
583 p_value1 => FEM_GL_POST_PROCESS_PKG.pv_coa_name);
584
585 RAISE OGLEngMain_FatalErr;
586 END IF; -- IF (v_tot_rows_valid = 0)
587
588 -- -----------------------------------------------------------------------
589 -- 10. Perform advanced Line Item and Financial Element Mappings if needed
590 -- -----------------------------------------------------------------------
591 IF (FEM_GL_POST_PROCESS_PKG.pv_adv_li_fe_mappings_flag = 'Y')
592 THEN
593 -- Override the default Line Item and Financial Element dimension mappings
594 -- based on the Natural Account dimension
595 FEM_ENGINES_PKG.Tech_Message
596 (p_severity => pc_log_level_event,
597 p_module => v_module,
598 p_app_name => 'FEM',
599 p_msg_name => 'FEM_INTG_BAL_MAP_ADV_LI_FE');
600 FND_FILE.NEW_LINE(fnd_file.log);
601 FEM_ENGINES_PKG.User_Message
602 (p_app_name => 'FEM',
603 p_msg_name => 'FEM_INTG_BAL_MAP_ADV_LI_FE');
604
605 -- Reset v_completion_code to 0 before calling the API
606 v_completion_code := 0;
607 FEM_INTG_BAL_ENG_LOAD_PKG.Map_Adv_LI_FE
608 (x_completion_code => v_completion_code);
609
610 IF v_completion_code = 1
611 THEN
612 v_completion_status := 'WARNING';
613 ELSIF v_completion_code = 2
614 THEN
615 RAISE OGLEngMain_FatalErr;
616 END IF; -- IF v_completion_code = 1
617
618 END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_adv_li_fe_mappings_flag = 'Y')
619
620 -- Set up a save point before inserting/updating tables other than the
621 -- global temporary tables such that we can roll back to here as needed
622 SAVEPOINT OGLEngSavePt;
623
624 -- Set Require Rollback Flag to Yes i.e. indicating it needs to rollback
625 -- to the save point when erroring out
626 v_require_rollback_flag := 'Y';
627
628 -- ---------------------------------------------------------------
629 -- 11. Mark the posted incremental balances if the Balance Type is
630 -- Actual/Encumbrance
631 -- ---------------------------------------------------------------
632
633 IF (FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd IN ('ACTUAL', 'ENCUMBRANCE'))
634 THEN
635 -- Mark the posted incremental balances
636 FEM_ENGINES_PKG.Tech_Message
637 (p_severity => pc_log_level_event,
638 p_module => v_module,
639 p_app_name => 'FEM',
640 p_msg_name => 'FEM_INTG_BAL_MARK_INCR_BAL');
641 FND_FILE.NEW_LINE(fnd_file.log);
642 FEM_ENGINES_PKG.User_Message
643 (p_app_name => 'FEM',
644 p_msg_name => 'FEM_INTG_BAL_MARK_INCR_BAL');
645
646 -- Reset v_completion_code to 0 before calling the API
647 v_completion_code := 0;
648 FEM_INTG_BAL_ENG_LOAD_PKG.Mark_Posted_Incr_Bal
649 (x_completion_code => v_completion_code,
650 p_bsv_range_low => v_bsv_range_low,
651 p_bsv_range_high => v_bsv_range_high);
652
653 IF v_completion_code = 1
654 THEN
655 v_completion_status := 'WARNING';
656 ELSIF v_completion_code = 2
657 THEN
658 RAISE OGLEngMain_FatalErr;
659 END IF; -- IF v_completion_code = 1
660
661 END IF;
662
663 -- ----------------------------------------------------------
664 -- 12. Post data from FEM_BAL_POST_INTERIM_GT to FEM_BALANCES
665 -- ----------------------------------------------------------
666
667 -- Posting balances into FEM
668 FEM_ENGINES_PKG.Tech_Message
669 (p_severity => pc_log_level_event,
670 p_module => v_module,
671 p_app_name => 'FEM',
672 p_msg_name => 'FEM_GL_POST_214');
673 FND_FILE.NEW_LINE(fnd_file.log);
674 FEM_ENGINES_PKG.User_Message
675 (p_app_name => 'FEM',
676 p_msg_name => 'FEM_GL_POST_214');
677
678 IF (FEM_GL_POST_PROCESS_PKG.pv_stmt_type = 'INSERT')
679 THEN
680 FEM_ENGINES_PKG.Tech_Message
681 (p_severity => pc_log_level_statement,
682 p_module => v_module,
683 p_app_name => 'FEM',
684 p_msg_name => 'Posting in Snapshot mode');
685
686 -- Reset v_completion_code to 0 before calling the API
687 v_completion_code := 0;
688 FEM_GL_POST_BAL_PKG.Post_Fem_Balances
689 (p_execution_mode => 'S',
690 p_process_slice => 'ogl',
691 x_rows_posted => v_tot_rows_posted,
692 x_completion_code => v_completion_code,
693 p_load_type => 'OGL',
694 p_maintain_qtd => FEM_GL_POST_PROCESS_PKG.pv_maintain_qtd_flag,
695 p_bsv_range_low => v_bsv_range_low,
696 p_bsv_range_high => v_bsv_range_high);
697 ELSE
698 FEM_ENGINES_PKG.Tech_Message
699 (p_severity => pc_log_level_statement,
700 p_module => v_module,
701 p_app_name => 'FEM',
702 p_msg_name => 'Posting in Incremental mode');
703
704 FEM_GL_POST_BAL_PKG.Post_Fem_Balances
705 (p_execution_mode => 'I',
706 p_process_slice => 'ogl',
707 x_rows_posted => v_tot_rows_posted,
708 x_completion_code => v_completion_code,
709 p_load_type => 'OGL',
710 p_maintain_qtd => FEM_GL_POST_PROCESS_PKG.pv_maintain_qtd_flag,
711 p_bsv_range_low => v_bsv_range_low,
712 p_bsv_range_high => v_bsv_range_high);
713 END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_stmt_type = 'INSERT')
714
715 IF v_completion_code = 1
716 THEN
717 v_completion_status := 'WARNING';
718 ELSIF v_completion_code = 2
719 THEN
720 RAISE OGLEngMain_FatalErr;
721 END IF; -- IF v_completion_code = 1
722
723 -- Bug fix 4313386: Since it has been changed to raise error if any CCID is
724 -- not mapped properly regardless the execution mode, we can remove the
725 -- codes to raise posting error or invalid CCID warning for incremental load
726
727 -- -------------------
728 -- 13. Generate Report
729 -- -------------------
730 FEM_ENGINES_PKG.Tech_Message
731 (p_severity => pc_log_level_event,
732 p_module => v_module,
733 p_app_name => 'FEM',
734 p_msg_name => 'FEM_INTG_BAL_GENERATE_REPORT');
735 FND_FILE.NEW_LINE(fnd_file.log);
736 FEM_ENGINES_PKG.User_Message
737 (p_app_name => 'FEM',
738 p_msg_name => 'FEM_INTG_BAL_GENERATE_REPORT');
739
740 -- Set Generate Report Flag to No i.e. indicating report should not be
741 -- generated when erroring out
742 v_generate_report_flag := 'N';
743
744 -- Reset v_completion_code to 0 before calling the API
745 v_completion_code := 0;
746 Generate_Report(x_completion_code => v_completion_code,
747 p_from_period => p_from_period,
748 p_to_period => p_to_period,
749 p_effective_date => p_effective_date,
750 p_bsv_range_low => p_bsv_range_low,
751 p_bsv_range_high => p_bsv_range_high,
752 p_tot_rows_inserted => v_tot_rows_inserted,
753 p_tot_rows_valid => v_tot_rows_valid,
754 p_tot_rows_posted => v_tot_rows_posted);
755
756 IF v_completion_code = 1
757 THEN
758 v_completion_status := 'WARNING';
759 ELSIF v_completion_code = 2
760 THEN
761 RAISE OGLEngMain_FatalErr;
762 END IF; -- IF v_completion_code = 1
763
764 -- ------------------------------------------------------------------------
765 -- 14. Raise business event, perform final process logging, commit and exit
766 -- ------------------------------------------------------------------------
767
768 -- Raise business events to notify other CPM products that new balances have
769 -- been loaded into FEM
770 FEM_ENGINES_PKG.Tech_Message
771 (p_severity => pc_log_level_event,
772 p_module => v_module,
773 p_app_name => 'FEM',
774 p_msg_name => 'FEM_INTG_BAL_RAISE_EVENT');
775 FND_FILE.NEW_LINE(fnd_file.log);
776 FEM_ENGINES_PKG.User_Message
777 (p_app_name => 'FEM',
778 p_msg_name => 'FEM_INTG_BAL_RAISE_EVENT');
779
780 -- Loop for each request which has posted at least one row into FEM and
781 -- raise a business event to notify other CPM products that OGL balances has
782 -- been loaded into FEM
783 FOR v_req IN req_cur LOOP
784 -- List the request ID for raising event to FND_LOG
785 FEM_ENGINES_PKG.Tech_Message
786 (p_severity => pc_log_level_statement,
787 p_module => v_module,
788 p_app_name => 'FEM',
789 p_msg_name => 'FEM_GL_POST_204',
790 p_token1 => 'VAR_NAME',
791 p_value1 => 'v_req.request_id',
792 p_token2 => 'VAR_VAL',
793 p_value2 => v_req.request_id);
794
795 -- Add Request ID to the parameter list
796 WF_EVENT.addparametertolist
797 (p_name => 'REQUEST_ID',
798 p_value => v_req.request_id,
799 p_parameterlist => v_param_list);
800
801 -- Add Balance Rule Object Definition ID to the parameter list
802 WF_EVENT.addparametertolist
803 (p_name => 'BAL_RULE_OBJ_DEF_ID',
804 p_value => p_bal_rule_obj_def_id,
805 p_parameterlist => v_param_list);
806
807 -- Add Period Name to the parameter list
808 WF_EVENT.addparametertolist
809 (p_name => 'PERIOD_NAME',
810 p_value => v_req.period_name,
811 p_parameterlist => v_param_list);
812
813 -- Add Cal Period ID to the parameter list
814 WF_EVENT.addparametertolist
815 (p_name => 'CAL_PERIOD_ID',
816 p_value => v_req.cal_period_id,
817 p_parameterlist => v_param_list);
818
819 -- Add Load Method Code to the parameter list
820 WF_EVENT.addparametertolist
821 (p_name => 'LOAD_METHOD_CODE',
822 p_value => v_req.load_method_code,
823 p_parameterlist => v_param_list);
824
825 -- Add As-of Date to the parameter list
826 WF_EVENT.addparametertolist
827 (p_name => 'AS_OF_DATE',
828 p_value => p_effective_date,
829 p_parameterlist => v_param_list);
830
831 -- Add BSV Range Low to the parameter list
832 WF_EVENT.addparametertolist
833 (p_name => 'BSV_RANGE_LOW',
834 p_value => p_bsv_range_low,
835 p_parameterlist => v_param_list);
836
837 -- Add BSV Range High to the parameter list
838 WF_EVENT.addparametertolist
839 (p_name => 'BSV_RANGE_HIGH',
840 p_value => p_bsv_range_high,
841 p_parameterlist => v_param_list);
842
843 -- Add Completion Status to the parameter list
844 WF_EVENT.addparametertolist
845 (p_name => 'STATUS_CODE',
846 p_value => v_completion_status,
847 p_parameterlist => v_param_list);
848
849 -- Add Base Request Id (as opposed to pseudo-request id) to the parameter list
850 WF_EVENT.addparametertolist
851 (p_name => 'BASE_REQUEST_ID',
852 p_value => fnd_global.conc_request_id,
853 p_parameterlist => v_param_list);
854
855 -- Raise the event
856 WF_EVENT.RAISE
857 (p_event_name => 'oracle.apps.fem.oglintg.balrule.execute',
858 p_event_key => NULL,
859 p_parameters => v_param_list);
860
861 END LOOP; -- req_cur Loop
862
863 -- Clean up the event parameter list if necessary
864 IF (v_param_list IS NOT NULL)
865 THEN
866 v_param_list.DELETE;
867 END IF;
868
869 FEM_ENGINES_PKG.Tech_Message
870 (p_severity => pc_log_level_event,
871 p_module => v_module,
872 p_app_name => 'FEM',
873 p_msg_name => 'FEM_INTG_BAL_FINAL_LOG');
874 FND_FILE.NEW_LINE(fnd_file.log);
875 FEM_ENGINES_PKG.User_Message
876 (p_app_name => 'FEM',
877 p_msg_name => 'FEM_INTG_BAL_FINAL_LOG');
878
879 IF (v_completion_status = 'WARNING')
880 THEN
881 -- Perform post-process logging with an warning message
882 FEM_GL_POST_PROCESS_PKG.Final_OGL_Process_Logging
883 (p_exec_status => 'SUCCESS',
884 p_final_message_name => 'FEM_GL_POST_206');
885 ELSE
886 -- Perform post-process logging
887 FEM_GL_POST_PROCESS_PKG.Final_OGL_Process_Logging
888 (p_exec_status => 'SUCCESS',
889 p_final_message_name => 'FEM_GL_POST_220');
890 END IF; -- IF (v_completion_status = 'WARNING')
891
892 -- Log the function exit time to FND_LOG (successful completion)
893 FEM_ENGINES_PKG.Tech_Message
894 (p_severity => pc_log_level_procedure,
895 p_module => v_module,
896 p_app_name => 'FEM',
897 p_msg_name => 'FEM_GL_POST_202',
898 p_token1 => 'FUNC_NAME',
899 p_value1 => v_func_name,
900 p_token2 => 'TIME',
901 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
902
903 -- Set the concurrent program completion status before exit
904 v_return_status := FND_CONCURRENT.Set_Completion_Status
905 (status => v_completion_status,
906 message => NULL);
907
908 -- Commit the changes and exit
909 Commit;
910
911 EXCEPTION
912 WHEN OGLEngMain_FatalErr THEN
913 -- <<< Fatal error >>>
914
915 -- Check if we need to rollback to the save point before marking the
916 -- posted incremental balances and posting balances into FEM
917 IF (v_require_rollback_flag = 'Y')
918 THEN
919 Rollback To OGLEngSavePt;
920 END IF;
921
922 -- Check if we need to generate the report
923 IF (v_generate_report_flag = 'Y')
924 THEN
925 FEM_ENGINES_PKG.Tech_Message
926 (p_severity => pc_log_level_event,
927 p_module => v_module,
928 p_app_name => 'FEM',
929 p_msg_name => 'FEM_INTG_BAL_GENERATE_REPORT');
930 FND_FILE.NEW_LINE(fnd_file.log);
931 FEM_ENGINES_PKG.User_Message
932 (p_app_name => 'FEM',
933 p_msg_name => 'FEM_INTG_BAL_GENERATE_REPORT');
934
935 Generate_Report(x_completion_code => v_completion_code,
936 p_from_period => p_from_period,
937 p_to_period => p_to_period,
938 p_effective_date => p_effective_date,
939 p_bsv_range_low => p_bsv_range_low,
940 p_bsv_range_high => p_bsv_range_high,
941 p_tot_rows_inserted => v_tot_rows_inserted,
942 p_tot_rows_valid => v_tot_rows_valid,
943 p_tot_rows_posted => 0);
944 END IF;
945
946 -- Perform post-process logging with an error message
947 FEM_ENGINES_PKG.Tech_Message
948 (p_severity => pc_log_level_event,
949 p_module => v_module,
950 p_app_name => 'FEM',
951 p_msg_name => 'FEM_INTG_BAL_FINAL_LOG');
952 FND_FILE.NEW_LINE(fnd_file.log);
953 FEM_ENGINES_PKG.User_Message
954 (p_app_name => 'FEM',
955 p_msg_name => 'FEM_INTG_BAL_FINAL_LOG');
956
957 FEM_GL_POST_PROCESS_PKG.Final_OGL_Process_Logging
958 (p_exec_status => 'ERROR_RERUN',
959 p_final_message_name => 'FEM_GL_POST_205');
960
961 -- Set the output parameters for the concurrent program
962 x_errbuf := FND_MESSAGE.Get_String('FEM', 'FEM_GL_POST_205');
963
964 -- Log the function exit time to FND_LOG (with error)
965 FEM_ENGINES_PKG.Tech_Message
966 (p_severity => pc_log_level_procedure,
967 p_module => v_module,
968 p_app_name => 'FEM',
969 p_msg_name => 'FEM_GL_POST_203',
970 p_token1 => 'FUNC_NAME',
971 p_value1 => v_func_name,
972 p_token2 => 'TIME',
973 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
974 -- Set the concurrent program completion status to ERROR
975 v_return_status := FND_CONCURRENT.Set_Completion_Status
976 (status => 'ERROR',
977 message => NULL);
978 -- Commit the changes and exit
979 Commit;
980
981 WHEN OGLEngMain_NoData THEN
982 -- <<< No data to be loaded >>>
983
984 -- Log the error messages
985 FEM_ENGINES_PKG.Tech_Message
986 (p_severity => pc_log_level_exception,
987 p_module => v_module,
988 p_app_name => 'FEM',
989 p_msg_name => 'FEM_INTG_BAL_NO_DATA');
990 FEM_ENGINES_PKG.User_Message
991 (p_app_name => 'FEM',
992 p_msg_name => 'FEM_INTG_BAL_NO_DATA');
993
994 -- Generate report to list the parameters
995 FEM_ENGINES_PKG.Tech_Message
996 (p_severity => pc_log_level_event,
997 p_module => v_module,
998 p_app_name => 'FEM',
999 p_msg_name => 'FEM_INTG_BAL_GENERATE_REPORT');
1000 FND_FILE.NEW_LINE(fnd_file.log);
1001 FEM_ENGINES_PKG.User_Message
1002 (p_app_name => 'FEM',
1003 p_msg_name => 'FEM_INTG_BAL_GENERATE_REPORT');
1004
1005 Generate_Report(x_completion_code => v_completion_code,
1006 p_from_period => p_from_period,
1007 p_to_period => p_to_period,
1008 p_effective_date => p_effective_date,
1009 p_bsv_range_low => p_bsv_range_low,
1010 p_bsv_range_high => p_bsv_range_high,
1011 p_tot_rows_inserted => v_tot_rows_inserted,
1012 p_tot_rows_valid => v_tot_rows_valid,
1013 p_tot_rows_posted => v_tot_rows_posted);
1014
1015 -- Log message to show the next step is Final Process Logging
1016 FEM_ENGINES_PKG.Tech_Message
1017 (p_severity => pc_log_level_event,
1018 p_module => v_module,
1019 p_app_name => 'FEM',
1020 p_msg_name => 'FEM_INTG_BAL_FINAL_LOG');
1021 FND_FILE.NEW_LINE(fnd_file.log);
1022 FEM_ENGINES_PKG.User_Message
1023 (p_app_name => 'FEM',
1024 p_msg_name => 'FEM_INTG_BAL_FINAL_LOG');
1025
1026 -- Start bug fix 5623102
1027 IF (FEM_GL_POST_PROCESS_PKG.pv_stmt_type = 'INSERT') THEN
1028 -- Bug fix 4330346: Changed to raise warning even when it is a pure
1029 -- snapshot load i.e. pv_stmt_type = 'INSERT'
1030
1031 -- Perform post-process logging with a warning message
1032 FEM_GL_POST_PROCESS_PKG.Final_OGL_Process_Logging
1033 (p_exec_status => 'SUCCESS',
1034 p_final_message_name => 'FEM_GL_POST_206');
1035
1036 -- Log the function exit time to FND_LOG (successful completion)
1037 FEM_ENGINES_PKG.Tech_Message
1038 (p_severity => pc_log_level_procedure,
1039 p_module => v_module,
1040 p_app_name => 'FEM',
1041 p_msg_name => 'FEM_GL_POST_202',
1042 p_token1 => 'FUNC_NAME',
1043 p_value1 => v_func_name,
1044 p_token2 => 'TIME',
1045 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1046
1047 -- Set the concurrent program completion status to WARNING
1048 v_return_status := FND_CONCURRENT.Set_Completion_Status
1049 (status => 'WARNING',
1050 message => NULL);
1051
1052 ELSE
1053
1054 FEM_GL_POST_PROCESS_PKG.Final_OGL_Process_Logging
1055 (p_exec_status => 'SUCCESS',
1056 p_final_message_name => 'FEM_GL_POST_220');
1057
1058 -- Log the function exit time to FND_LOG (successful completion)
1059 FEM_ENGINES_PKG.Tech_Message
1060 (p_severity => pc_log_level_procedure,
1061 p_module => v_module,
1062 p_app_name => 'FEM',
1063 p_msg_name => 'FEM_GL_POST_202',
1064 p_token1 => 'FUNC_NAME',
1065 p_value1 => v_func_name,
1066 p_token2 => 'TIME',
1067 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1068
1069 -- Set the concurrent program completion status to NORMAL
1070 -- Set return code to 1 for FCH data submission to use
1071 x_retcode := 1;
1072 v_return_status := FND_CONCURRENT.Set_Completion_Status
1073 (status => 'NORMAL',
1074 message => NULL);
1075
1076 END IF;
1077 -- End bug fix 5623102
1078
1079 -- Commit the changes
1080 Commit;
1081
1082 WHEN Others THEN
1083 -- <<< Unexpected database exceptions >>>
1084
1085 -- Rollback all the changes
1086 Rollback;
1087
1088 -- Set the output parameters for the concurrent program
1089 x_errbuf := SQLERRM;
1090 x_retcode := SQLCODE;
1091
1092 -- Log the unexpected error message
1093 FEM_ENGINES_PKG.Tech_Message
1094 (p_severity => pc_log_level_unexpected,
1095 p_module => v_module,
1096 p_app_name => 'FEM',
1097 p_msg_name => 'FEM_GL_POST_215',
1098 p_token1 => 'ERR_MSG',
1099 p_value1 => x_errbuf);
1100 FEM_ENGINES_PKG.User_Message
1101 (p_app_name => 'FEM',
1102 p_msg_name => 'FEM_GL_POST_215',
1103 p_token1 => 'ERR_MSG',
1104 p_value1 => x_errbuf);
1105
1106 -- Log message to show the next step is Final Process Logging
1107 FEM_ENGINES_PKG.Tech_Message
1108 (p_severity => pc_log_level_event,
1109 p_module => v_module,
1110 p_app_name => 'FEM',
1111 p_msg_name => 'FEM_INTG_BAL_FINAL_LOG');
1112 FND_FILE.NEW_LINE(fnd_file.log);
1113 FEM_ENGINES_PKG.User_Message
1114 (p_app_name => 'FEM',
1115 p_msg_name => 'FEM_INTG_BAL_FINAL_LOG');
1116
1117 -- Perform post-process logging with an error message
1118 FEM_GL_POST_PROCESS_PKG.Final_OGL_Process_Logging
1119 (p_exec_status => 'ERROR_RERUN',
1120 p_final_message_name => 'FEM_GL_POST_205');
1121
1122 -- Log the function exit time to FND_LOG (with error)
1123 FEM_ENGINES_PKG.Tech_Message
1124 (p_severity => pc_log_level_procedure,
1125 p_module => v_module,
1126 p_app_name => 'FEM',
1127 p_msg_name => 'FEM_GL_POST_203',
1128 p_token1 => 'FUNC_NAME',
1129 p_value1 => v_func_name,
1130 p_token2 => 'TIME',
1131 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1132 -- Set the concurrent program completion status to ERROR
1133 v_return_status := FND_CONCURRENT.Set_Completion_Status
1134 (status => 'ERROR',
1135 message => NULL);
1136 -- Commit the changes
1137 Commit;
1138
1139 END Main;
1140
1141 -- ------------------
1142 -- Private Procedures
1143 -- ------------------
1144
1145 --
1146 -- Procedure
1147 -- Generate_Report
1148 -- Purpose
1149 -- This routine generates the FEM-OGL Integration Balances Rule
1150 -- Processing Engine execution report
1151 -- History
1152 -- 11-19-04 L Poon Created
1153 -- 06-22-05 Harikiran Bug 4394404 - Modified code so that invalid periods
1154 -- related to Budget Balance Rules are reported
1155 -- Arguments
1156 -- x_completion_code : 0 for success, 1 for warning, 2 for failure
1157 -- p_from_period : First period from which balances will be loaded
1158 -- p_to_period : Last period from which balances will be loaded
1159 -- p_effective_date : Effective date to calculate the average balances
1160 -- p_bsv_range_low : First balancing segment value which balances will
1161 -- be loaded
1162 -- p_bsv_range_high : Last balancing segment value which balances will
1163 -- be loaded
1164 -- p_tot_rows_inserted : Total number of rows inserted into the posting
1165 -- interim table
1166 -- p_tot_rows_valid : Total number of valid rows of the posting interim
1167 -- table
1168 -- p_tot_rows_posted : Total number of rows posted into FEM
1169 PROCEDURE Generate_Report
1170 ( x_completion_code OUT NOCOPY VARCHAR2
1171 , p_from_period IN VARCHAR2
1172 , p_to_period IN VARCHAR2
1173 , p_effective_date IN VARCHAR2
1174 , p_bsv_range_low IN VARCHAR2
1175 , p_bsv_range_high IN VARCHAR2
1176 , p_tot_rows_inserted IN NUMBER
1177 , p_tot_rows_valid IN NUMBER
1178 , p_tot_rows_posted IN NUMBER) IS
1179 v_module VARCHAR2(100);
1180 v_func_name VARCHAR2(80);
1181
1182 v_line_text VARCHAR2(1000);
1183 v_message FND_NEW_MESSAGES.message_text%TYPE;
1184 v_dim_name FEM_DIMENSIONS_TL.dimension_name%TYPE;
1185 v_ds_code FEM_DATASETS_B.DATASET_CODE%TYPE;
1186 v_ds_name FEM_DATASETS_TL.dataset_name%TYPE;
1187 v_registered_flag VARCHAR2(1);
1188 v_print_exec_list VARCHAR2(1);
1189 v_eff_per_num FEM_INTG_EXEC_PARAMS_GT.effective_period_num%TYPE;
1190
1191 CURSOR Cur_PerList_Line (p_errText1 IN VARCHAR2,
1192 p_errText2 IN VARCHAR2,
1193 p_errText3 IN VARCHAR2) IS
1194 SELECT DISTINCT
1195 RPAD(PERIOD_NAME, 17, ' ')
1196 || DECODE(ERROR_CODE
1197 , 'INVALID_PERIOD_STATUS', p_errText1
1198 || DECODE(CAL_PERIOD_ID
1199 , -1, ', ' || p_errText2
1200 , '')
1201 , 'PERIOD_NOT_MAPPED' , p_errText2
1202 , 'OTHER_DS_LOADED' , p_errText3)
1203 , EFFECTIVE_PERIOD_NUM
1204 FROM FEM_INTG_EXEC_PARAMS_GT
1205 WHERE ERROR_CODE IN
1206 ('INVALID_PERIOD_STATUS', 'PERIOD_NOT_MAPPED', 'OTHER_DS_LOADED')
1207 ORDER BY EFFECTIVE_PERIOD_NUM;
1208
1209 CURSOR Cur_DS (p_ds_dim_name IN VARCHAR2) IS
1210 SELECT ds.DATASET_CODE,
1211 p_ds_dim_name || ' ' || ds.DATASET_NAME
1212 FROM FEM_DATASETS_TL ds
1213 WHERE ds.DATASET_CODE IN (SELECT DISTINCT OUTPUT_DATASET_CODE
1214 FROM FEM_INTG_EXEC_PARAMS_GT)
1215 AND ds.LANGUAGE = USERENV('LANG');
1216
1217 CURSOR Cur_RegList_Line (p_ds_code IN NUMBER,
1218 p_succText IN VARCHAR2,
1219 p_errText1 IN VARCHAR2,
1220 p_errText2 IN VARCHAR2) IS
1221
1222 SELECT ' ' || RPAD(gt.PERIOD_NAME, 17, ' ')
1223 || DECODE(gt.ERROR_CODE, NULL, p_succText
1224 , 'PERIOD_GAP_EXISTS', p_errText1
1225 , 'EXEC_LOCK_EXISTS' , p_errText2)
1226 , DECODE(gt.ERROR_CODE, NULL, 'Y', 'N')
1227 FROM FEM_INTG_EXEC_PARAMS_GT gt
1228 WHERE (gt.REQUEST_ID IS NOT NULL
1229 OR gt.ERROR_CODE IN ('PERIOD_GAP_EXISTS', 'EXEC_LOCK_EXISTS'))
1230 AND gt.OUTPUT_DATASET_CODE = p_ds_code
1231 ORDER BY gt.EFFECTIVE_PERIOD_NUM;
1232
1233 CURSOR Cur_ExecList_Line (p_ds_code IN NUMBER) IS
1234 SELECT ' ' || RPAD(gt.PERIOD_NAME, 17, ' ')
1235 || LPAD(TO_CHAR(NVL(gt.SELECTED_PTD_DR_BAL, '')), 16) || ' '
1236 || LPAD(TO_CHAR(NVL(gt.SELECTED_PTD_CR_BAL, '')), 16) || ' '
1237 || LPAD(TO_CHAR(NVL(gt.POSTED_PTD_DR_BAL, '')), 16) || ' '
1238 || LPAD(TO_CHAR(NVL(gt.POSTED_PTD_CR_BAL, '')), 16)
1239 FROM FEM_INTG_EXEC_PARAMS_GT gt
1240 WHERE gt.REQUEST_ID IS NOT NULL
1241 AND gt.ERROR_CODE IS NULL
1242 AND gt.OUTPUT_DATASET_CODE = p_ds_code
1243 ORDER BY gt.EFFECTIVE_PERIOD_NUM;
1244
1245 CURSOR Cur_AcctList_Line IS
1246 SELECT SUBSTR(NVL(FND_FLEX_EXT.Get_Segs
1247 ('SQLGL', 'GL#', FEM_GL_POST_PROCESS_PKG.pv_coa_id,
1248 errAcct.CODE_COMBINATION_ID),
1249 errAcct.CODE_COMBINATION_ID), 1, 100)
1250 FROM (SELECT DISTINCT CODE_COMBINATION_ID
1251 FROM FEM_BAL_POST_INTERIM_GT gt
1252 WHERE gt.POSTING_ERROR_FLAG = 'Y') errAcct;
1253
1254 CURSOR Cur_NoDataPerList_Line (p_ds_code IN NUMBER) IS
1255 SELECT ' ' || RPAD(gt.PERIOD_NAME, 17, ' ')
1256 FROM FEM_INTG_EXEC_PARAMS_GT gt
1257 WHERE gt.REQUEST_ID IS NOT NULL
1258 AND gt.ERROR_CODE IS NULL
1259 AND gt.OUTPUT_DATASET_CODE = p_ds_code
1260 AND gt.NUM_OF_ROWS_SELECTED = 0
1261 AND gt.LOAD_METHOD_CODE = 'S'
1262 ORDER BY gt.EFFECTIVE_PERIOD_NUM;
1263
1264 BEGIN
1265 v_module := 'fem.plsql.fem_intg_bal_eng.generate_report';
1266 v_func_name := 'FEM_INTG_BAL_RULE_ENG_PKG.Generate_Report';
1267
1268 -- Log the function entry time to FND_LOG
1269 FEM_ENGINES_PKG.Tech_Message
1270 (p_severity => pc_log_level_procedure,
1271 p_module => v_module,
1272 p_app_name => 'FEM',
1273 p_msg_name => 'FEM_GL_POST_201',
1274 p_token1 => 'FUNC_NAME',
1275 p_value1 => v_func_name,
1276 p_token2 => 'TIME',
1277 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1278
1279 -- -----------------------------------------------------
1280 -- 1. List the IN parameters and their values to FND_LOG
1281 -- -----------------------------------------------------
1282 FEM_ENGINES_PKG.Tech_Message
1283 (p_severity => pc_log_level_statement,
1284 p_module => v_module,
1285 p_app_name => 'FEM',
1286 p_msg_name => 'FEM_GL_POST_204',
1287 p_token1 => 'VAR_NAME',
1288 p_value1 => 'p_tot_rows_inserted',
1289 p_token2 => 'VAR_VAL',
1290 p_value2 => TO_CHAR(p_tot_rows_inserted));
1291 FEM_ENGINES_PKG.Tech_Message
1292 (p_severity => pc_log_level_statement,
1293 p_module => v_module,
1294 p_app_name => 'FEM',
1295 p_msg_name => 'FEM_GL_POST_204',
1296 p_token1 => 'VAR_NAME',
1297 p_value1 => 'p_tot_rows_valid',
1298 p_token2 => 'VAR_VAL',
1299 p_value2 => TO_CHAR(p_tot_rows_valid));
1300 FEM_ENGINES_PKG.Tech_Message
1301 (p_severity => pc_log_level_statement,
1302 p_module => v_module,
1303 p_app_name => 'FEM',
1304 p_msg_name => 'FEM_GL_POST_204',
1305 p_token1 => 'VAR_NAME',
1306 p_value1 => 'p_tot_rows_posted',
1307 p_token2 => 'VAR_VAL',
1308 p_value2 => TO_CHAR(p_tot_rows_posted));
1309
1310 -- ---------------------------------------------------
1311 -- 2. Initialize package variables and local variables
1312 -- ---------------------------------------------------
1313 pv_page_count := 0;
1314 pv_curr_list := 'NONE';
1315 v_dim_name := NULL;
1316 v_ds_name := NULL;
1317
1318 -- ---------------------------------------------------------
1319 -- 3. Populate the number of rows posted and balances posted
1320 -- by each valid request if any rows are posted
1321 -- ---------------------------------------------------------
1322 IF (p_tot_rows_posted > 0)
1323 THEN
1324 -- At least one row is posted into FEM, so find the number of rows
1325 -- posted and balances posted for each valid execution parameter
1326 UPDATE FEM_INTG_EXEC_PARAMS_GT param
1327 SET ( NUM_OF_ROWS_POSTED
1328 , POSTED_PTD_DR_BAL
1329 , POSTED_PTD_CR_BAL) =
1330 (SELECT COUNT(*)
1331 , SUM(NVL(bpi.PTD_DEBIT_BALANCE_E, 0))
1332 , SUM(NVL(bpi.PTD_CREDIT_BALANCE_E, 0))
1333 FROM FEM_BAL_POST_INTERIM_GT bpi
1334 WHERE bpi.DATASET_CODE = param.OUTPUT_DATASET_CODE
1335 AND bpi.CAL_PERIOD_ID = param.CAL_PERIOD_ID
1336 AND bpi.POSTING_ERROR_FLAG = 'N'
1337 AND NOT EXISTS
1338 (SELECT 'Invalid Delta Load'
1339 FROM FEM_INTG_DELTA_LOADS dl
1340 WHERE dl.LEDGER_ID = bpi.LEDGER_ID
1341 AND dl.DATASET_CODE = bpi.DATASET_CODE
1342 AND dl.CAL_PERIOD_ID = bpi.CAL_PERIOD_ID
1343 AND dl.DELTA_RUN_ID = bpi.DELTA_RUN_ID
1344 AND dl.LOADED_FLAG = 'N'))
1345 WHERE param.ERROR_CODE IS NULL
1346 AND param.REQUEST_ID IS NOT NULL;
1347
1348 -- Log the number of rows updated in FEM_INTG_EXEC_PARAMS_GT
1349 FEM_ENGINES_PKG.Tech_Message
1350 (p_severity => pc_log_level_statement,
1351 p_module => v_module,
1352 p_app_name => 'FEM',
1353 p_msg_name => 'FEM_GL_POST_217',
1354 p_token1 => 'NUM',
1355 p_value1 => TO_CHAR(SQL%ROWCOUNT),
1356 p_token2 => 'TABLE',
1357 p_value2 => 'FEM_INTG_EXEC_PARAMS_GT');
1358
1359 END IF; -- IF (p_tot_rows_posted > 0)
1360
1361 -- --------------------------------------------
1362 -- 4. Print the report header of the first page
1363 -- --------------------------------------------
1364 Print_Report_Hdr;
1365
1366 -- ------------------------------------
1367 -- 5. List the passed engine parameters
1368 -- ------------------------------------
1369
1370 -- List the name of the passed Balances Rule Version
1371 v_message := FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_RULE_DEF_TXT');
1372 v_line_text := ' ' || RPAD(v_message, 40, ' ') || ': '
1373 || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_name;
1374 Write_Line(p_line_text => v_line_text);
1375
1376 -- List the From Period
1377 v_message := FND_MESSAGE.get_string('FEM', 'FEM_FROM_PERIOD_TXT');
1378 v_line_text := ' ' || RPAD(v_message, 40, ' ') || ': ' || p_from_period;
1379 Write_Line(p_line_text => v_line_text);
1380
1381 -- List the To Period
1382 v_message := FND_MESSAGE.get_string('FEM', 'FEM_TO_PERIOD_TXT');
1383 v_line_text := ' ' || RPAD(v_message, 40, ' ') || ': ' || p_to_period;
1384 Write_Line(p_line_text => v_line_text);
1385
1386 -- List the Period
1387 v_message := FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_EFF_DATE_TXT');
1388 v_line_text := ' ' || RPAD(v_message, 40, ' ') || ': ' || p_effective_date;
1389 Write_Line(p_line_text => v_line_text);
1390
1391 -- List the From BSV
1392 v_message := FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_FROM_BSV_TXT');
1393 v_line_text := ' ' || RPAD(v_message, 40, ' ') || ': ' || p_bsv_range_low;
1394 Write_Line(p_line_text => v_line_text);
1395
1396 -- List the To BSV
1397 v_message := FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_TO_BSV_TXT');
1398 v_line_text := ' ' || RPAD(v_message, 40, ' ') || ': ' || p_bsv_range_high;
1399 Write_Line(p_line_text => v_line_text);
1400
1401 -- ------------------------------------
1402 -- 6. List the invalid period(s) if any
1403 -- ------------------------------------
1404
1405 -- Check if there are any invalid periods
1406 IF ((FEM_GL_POST_PROCESS_PKG.pv_min_valid_period_eff_num
1407 <> FEM_GL_POST_PROCESS_PKG.pv_from_period_eff_num
1408 OR FEM_GL_POST_PROCESS_PKG.pv_max_valid_period_eff_num
1409 <> FEM_GL_POST_PROCESS_PKG.pv_to_period_eff_num)
1410 -- Bug 4394404 hkaniven start - Report invalid periods belonging to
1411 -- Budget Balances Rules
1412 OR
1413 (FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd = 'BUDGET'
1414 AND FEM_GL_POST_PROCESS_PKG.pv_num_rows
1415 <> FEM_GL_POST_PROCESS_PKG.pv_num_rows_valid))
1416
1417 -- Bug 4394404 hkaniven end - Report invalid periods belonging to
1418 -- Budget Balances Rules
1419
1420 THEN
1421 -- There are invalid periods, so print the error/warning message and
1422 -- the invalid period list
1423
1424 -- Print 2 blanks line
1425 Write_New_Line;
1426 Write_New_Line;
1427
1428 IF (FEM_GL_POST_PROCESS_PKG.pv_min_valid_period_eff_num = -1)
1429 THEN
1430 -- All periods are invalid, so print the error message
1431 -- FEM_INTG_BAL_NO_VALID_PER
1432 -- Bug fix 4170124: The message is changed and doesn't have any tockens.
1433 Write_Message
1434 (p_app_name => 'FEM',
1435 p_msg_name => 'FEM_INTG_BAL_NO_VALID_PER');
1436
1437 ELSE
1438 -- Not all periods are invalid, so print the warning message
1439 -- FEM_INTG_BAL_INVALID_PER
1440 -- Bug fix 4170124: The message is changed and doesn't have any tockens.
1441 Write_Message
1442 (p_app_name => 'FEM',
1443 p_msg_name => 'FEM_INTG_BAL_INVALID_PER');
1444
1445 END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_min_valid_period_eff_num = -1)
1446
1447 -- Print the invalid period list prompt
1448 Write_New_Line;
1449 Write_Message
1450 (p_app_name => 'FEM',
1451 p_msg_name => 'FEM_INTG_BAL_PER_LIST');
1452 Write_New_Line;
1453
1454 -- Set the current list for printing is Invalid Periods
1455 pv_curr_list := 'INVALID_PERIODS';
1456 -- Check if we need to start a new page to print the list
1457 IF (pv_line_count <= pc_page_line_no - 2)
1458 THEN
1459 -- Since at least 3 lines are left for this page, print Invalid Periods
1460 -- list header
1461 Write_Message
1462 (p_app_name => 'FEM',
1463 p_msg_name => 'FEM_INTG_BAL_PER_TABLE_COLS');
1464 Write_Message
1465 (p_app_name => 'FEM',
1466 p_msg_name => 'FEM_INTG_BAL_PER_TABLE_COL_LN');
1467
1468 ELSE
1469 -- Since less than 3 lines are left for this page, start a new page and
1470 -- print the list header on the new page
1471 Print_Report_Hdr;
1472
1473 END IF; -- IF (pv_line_count <= pc_page_line_no - 2)
1474
1475 -- Open the cursor for Invalid Periods list line text
1476 OPEN Cur_PerList_Line
1477 (FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_PER_STATUS_ERR'),
1478 FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_PER_UNMAPPED_ERR'),
1479 FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_PER_DS_ERR'));
1480 -- Loop for each line and write it to the report
1481 LOOP
1482 FETCH Cur_PerList_Line INTO v_line_text, v_eff_per_num;
1483 EXIT WHEN Cur_PerList_Line%NOTFOUND;
1484 Write_Line(v_line_text);
1485 END LOOP;
1486 -- Finish printing the list so set it back to NONE
1487 pv_curr_list := 'NONE';
1488 -- Close the cursor
1489 CLOSE Cur_PerList_Line;
1490
1491 END IF; -- IF (pv_min_valid_period_eff_num <> pv_from_period_eff_num ...
1492
1493 -- Check if there is at least one valid period
1494 IF (FEM_GL_POST_PROCESS_PKG.pv_min_valid_period_eff_num <> -1)
1495 THEN
1496 -- At least one valid periods exist
1497
1498 -- ---------------------------------------------------------------
1499 -- 7. If there are at least one registered periods/datasets, check
1500 -- - if any data are selected for all datasets/periods
1501 -- - if there are any no-data-found datasets/snapshot periods
1502 -- - if there are any unmapped accounts and list them if any
1503 -- but not all
1504 -- ---------------------------------------------------------------
1505
1506 -- Check if any period/dataset is registered successfully
1507 IF (FEM_GL_POST_PROCESS_PKG.pv_stmt_type IS NOT NULL)
1508 THEN
1509 -- -----------------------------------------------------------
1510 -- 7.1 Check if any data are selected for all datasets/periods
1511 -- -----------------------------------------------------------
1512 IF (p_tot_rows_inserted = 0)
1513 THEN
1514 -- No data are selected for all datasets/periods, so it is no need to
1515 -- perform further checks and print message FEM_INTG_BAL_NO_DATA
1516 Write_New_Line;
1517 Write_New_Line;
1518 Write_Message
1519 (p_app_name => 'FEM',
1520 p_msg_name => 'FEM_INTG_BAL_NO_DATA');
1521
1522 ELSE
1523 -- Some data are selected, so we need to perform further checks
1524
1525 -- ------------------------------------------------------------------
1526 -- 7.2 Check if there are any no-data-found datasets/snapshot periods
1527 -- ------------------------------------------------------------------
1528 BEGIN
1529 SELECT 'No-data-found dataset/snapshot period exists'
1530 INTO v_line_text
1531 FROM DUAL
1532 WHERE EXISTS (SELECT 'X'
1533 FROM FEM_INTG_EXEC_PARAMS_GT
1534 WHERE ERROR_CODE IS NULL
1535 AND REQUEST_ID IS NOT NULL
1536 AND NUM_OF_ROWS_SELECTED = 0
1537 AND LOAD_METHOD_CODE = 'S');
1538 EXCEPTION
1539 WHEN NO_DATA_FOUND THEN
1540 -- All datasets/snapshot periods have data found for posting
1541 v_line_text := NULL;
1542 END;
1543
1544 IF (v_line_text IS NOT NULL)
1545 THEN
1546 -- No-data-found datasets/snapshot periods exist
1547
1548 -- Log warning message FEM_INTG_BAL_NO_DATA_PER
1549 FEM_ENGINES_PKG.Tech_Message
1550 (p_severity => pc_log_level_exception,
1551 p_module => v_module,
1552 p_app_name => 'FEM',
1553 p_msg_name => 'FEM_INTG_BAL_NO_DATA_PER');
1554 FEM_ENGINES_PKG.User_Message
1555 (p_app_name => 'FEM',
1556 p_msg_name => 'FEM_INTG_BAL_NO_DATA_PER');
1557
1558 -- Print message FEM_INTG_BAL_NO_DATA_PER to report
1559 Write_New_Line;
1560 Write_New_Line;
1561 Write_Message
1562 (p_app_name => 'FEM',
1563 p_msg_name => 'FEM_INTG_BAL_NO_DATA_PER');
1564
1565 -- Set the return code to indicate warning
1566 x_completion_code := 1;
1567
1568 -- Log that we have set x_completion_code to 1 to FND Log
1569 FEM_ENGINES_PKG.Tech_Message
1570 (p_severity => pc_log_level_statement,
1571 p_module => v_module,
1572 p_app_name => 'FEM',
1573 p_msg_name => 'FEM_GL_POST_204',
1574 p_token1 => 'VAR_NAME',
1575 p_value1 => 'x_completion_code',
1576 p_token2 => 'VAR_VAL',
1577 p_value2 => x_completion_code);
1578
1579 END IF;
1580
1581 -- --------------------------------------------
1582 -- 7.3 Check if there are any unmapped accounts
1583 -- --------------------------------------------
1584 IF (p_tot_rows_inserted > p_tot_rows_valid)
1585 THEN
1586 -- At least one unmapped account exists
1587
1588 -- Print 2 blanks line
1589 Write_New_Line;
1590 Write_New_Line;
1591
1592 -- Check if all accounts are unmapped
1593 IF (p_tot_rows_valid = 0)
1594 THEN
1595 -- Since all accounts are unmapped, we won't list all unmapped
1596 -- accounts and just print message FEM_INTG_BAL_ALL_CCID_ERR
1597 Write_Message
1598 (p_app_name => 'FEM',
1599 p_msg_name => 'FEM_INTG_BAL_ALL_CCID_ERR',
1600 p_token1 => 'COA_NAME',
1601 p_value1 => FEM_GL_POST_PROCESS_PKG.pv_coa_name);
1602
1603 ELSE
1604
1605 -- Since only some accounts are invalid, so print error message
1606 -- and list the unmapped accounts
1607
1608 -- Bug fix 4313386: Since it has been changed to raise error if any
1609 -- invalid account exists regardless the execution mode, we always
1610 -- print the same error message, FEM_INTG_BAL_SNAP_CCID_ERR.
1611 Write_Message
1612 (p_app_name => 'FEM',
1613 p_msg_name => 'FEM_INTG_BAL_SNAP_CCID_ERR',
1614 p_token1 => 'COA_NAME',
1615 p_value1 => FEM_GL_POST_PROCESS_PKG.pv_coa_name);
1616
1617 -- Print the unmapped account list prompt
1618 Write_New_Line;
1619 Write_Message
1620 (p_app_name => 'FEM',
1621 p_msg_name => 'FEM_INTG_BAL_ACCT_LIST');
1622 Write_New_Line;
1623
1624 -- Set the current list for printing is Unmapped Accounts
1625 pv_curr_list := 'UNMAPPED_ACCOUNTS';
1626 -- Check if we need to start a new page to print the list
1627 IF (pv_line_count <= pc_page_line_no - 2)
1628 THEN
1629 -- Since at least 3 lines are left for this page, print Unmapped
1630 -- Accounts list header
1631 Write_Message
1632 (p_app_name => 'FEM',
1633 p_msg_name => 'FEM_INTG_BAL_ACCT_TABLE_COLS');
1634 Write_Message
1635 (p_app_name => 'FEM',
1636 p_msg_name => 'FEM_INTG_BAL_ACCT_TABLE_COL_LN');
1637
1638 ELSE
1639 -- Since less than 3 lines are left for this page, start a new
1640 -- page and print the list header on the new page
1641 Print_Report_Hdr;
1642
1643 END IF; -- IF (pv_line_count <= pc_page_line_no - 2)
1644
1645 -- Open the cursor for Unmapped Accounts list line text
1646 OPEN Cur_AcctList_Line;
1647 -- Loop for each line and write it to the report
1648 LOOP
1649 FETCH Cur_AcctList_Line INTO v_line_text;
1650 EXIT WHEN Cur_AcctList_Line%NOTFOUND;
1651 Write_Line(v_line_text);
1652 END LOOP;
1653 -- Finish printing the list so set it back to NONE
1654 pv_curr_list := 'NONE';
1655 -- Close the cursor
1656 CLOSE Cur_AcctList_Line;
1657
1658 END IF; -- IF (p_tot_rows_valid = 0)
1659 END IF; -- IF (p_tot_rows_inserted > p_tot_rows_valid)
1660
1661 END IF; -- IF (p_tot_rows_inserted = 0)
1662 END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_stmt_type IS NOT NULL)
1663
1664 -- --------------------------------------------------
1665 -- 8. If there are at least one valid periods, list
1666 -- registration status, no-data-found snapshot
1667 -- periods, and execution summary for each Dataset
1668 -- --------------------------------------------------
1669
1670 -- Get the name of Dataset dimension
1671 v_dim_name := NULL;
1672 v_dim_name := FEM_DIMENSION_UTIL_PKG.Get_Dimension_Name
1673 (p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_dataset_dim_id);
1674 IF (v_dim_name IS NULL)
1675 THEN
1676 v_dim_name := 'Dataset';
1677 END IF;
1678
1679 OPEN Cur_DS (v_dim_name);
1680 -- Loop for each Dataset to list its registration statuses and execution
1681 -- summary
1682 LOOP
1683 FETCH Cur_DS INTO v_ds_code, v_ds_name;
1684 EXIT WHEN Cur_DS%NOTFOUND;
1685
1686 -- Print the Dataset name
1687 Write_New_Line;
1688 Write_New_Line;
1689 Write_Line(v_ds_name);
1690 Write_New_Line;
1691
1692 -- --------------------------------------------------------
1693 -- 8.1 List the registration status for the current Dataset
1694 -- --------------------------------------------------------
1695
1696 -- Print the Registration Status list prompt
1697 Write_New_Line;
1698 Write_Message
1699 (p_app_name => 'FEM',
1700 p_msg_name => 'FEM_INTG_BAL_REG_LIST');
1701 Write_New_Line;
1702
1703 -- Set the current list for printing is Registration Status
1704 pv_curr_list := 'REG_STATUS';
1705 -- Check if we need to start a new page to print the list
1706 IF (pv_line_count <= pc_page_line_no - 2)
1707 THEN
1708 -- At least 3 lines are left for this page, so print the list header
1709 Write_Message
1710 (p_app_name => 'FEM',
1711 p_msg_name => 'FEM_INTG_BAL_REG_TABLE_COLS');
1712 Write_Message
1713 (p_app_name => 'FEM',
1714 p_msg_name => 'FEM_INTG_BAL_REG_TABLE_COL_LN');
1715
1716 ELSE
1717 -- Less than 3 lines are left for this page, so start a new page and
1718 -- print the list header on the new page
1719 Print_Report_Hdr;
1720
1721 END IF; -- IF (pv_line_count <= pc_page_line_no - 2)
1722
1723 -- Open the cursor for Registration Status list line text for the
1724 -- current Dataset
1725 OPEN Cur_RegList_Line
1726 (v_ds_code,
1727 FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_REG_SUCC'),
1728 FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_REG_GAP_ERR'),
1729 FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_REG_LOCK_ERR'));
1730 -- Initialize it as not printing the execution summary
1731 v_print_exec_list := 'N';
1732 -- Loop for each line and write it to the report
1733 LOOP
1734 FETCH Cur_RegList_Line INTO v_line_text, v_registered_flag;
1735 EXIT WHEN Cur_RegList_Line%NOTFOUND;
1736 Write_Line(v_line_text);
1737 IF (v_registered_flag = 'Y')
1738 THEN
1739 v_print_exec_list := 'Y';
1740 END IF;
1741 END LOOP;
1742 -- Finish printing the list so set it back to NONE
1743 pv_curr_list := 'NONE';
1744 -- Close the cursor
1745 CLOSE Cur_RegList_Line;
1746
1747 IF (v_print_exec_list = 'Y')
1748 THEN
1749 -- At least one periods are registered successfully for the current
1750 -- Dataset
1751
1752 -- ------------------------------------------------------------
1753 -- 8.2 List the no-data-found snapshot periods if at least one
1754 -- rows are inserted into the balance posting interim table
1755 -- ------------------------------------------------------------
1756 IF (p_tot_rows_inserted > 0)
1757 THEN
1758 -- Print the No-Data-Found Snapshot Periods list prompt
1759 Write_New_Line;
1760 Write_Message
1761 (p_app_name => 'FEM',
1762 p_msg_name => 'FEM_INTG_BAL_NODATA_PER_LIST');
1763 Write_New_Line;
1764
1765 -- Set the current list for printing is No-data-found Snapshot
1766 -- Periods
1767 pv_curr_list := 'NODATA_PERIODS';
1768 -- Check if we need to start a new page to print the list
1769 IF (pv_line_count <= pc_page_line_no - 2)
1770 THEN
1771 -- At least 3 lines are left for this page, so print the list
1772 -- header
1773 Write_Message
1774 (p_app_name => 'FEM',
1775 p_msg_name => 'FEM_INTG_BAL_NODA_TABLE_COLS');
1776 Write_Message
1777 (p_app_name => 'FEM',
1778 p_msg_name => 'FEM_INTG_BAL_NODA_TABLE_COL_LN');
1779
1780 ELSE
1781 -- Less than 3 lines are left for this page, so start a new page
1782 -- and print the list header on the new page
1783 Print_Report_Hdr;
1784
1785 END IF; -- IF (pv_line_count <= pc_page_line_no - 2)
1786
1787 -- Open the cursor for No-data-found Snapshot Periods list line text
1788 -- for the current Dataset
1789 OPEN Cur_NoDataPerList_Line(v_ds_code);
1790 -- Loop for each line and write it to the report
1791 LOOP
1792 FETCH Cur_NoDataPerList_Line INTO v_line_text;
1793 EXIT WHEN Cur_NoDataPerList_Line%NOTFOUND;
1794 Write_Line(v_line_text);
1795 END LOOP;
1796 -- Finish printing the list so set it back to NONE
1797 pv_curr_list := 'NONE';
1798 -- Close the cursor
1799 CLOSE Cur_NoDataPerList_Line;
1800
1801 END IF; -- IF (p_tot_rows_inserted > 0)
1802
1803 -- Bug fix 4313353: Commented out the codes to print selected and posted amounts
1804 /*
1805 -- ------------------------------------------------------
1806 -- 8.3 List the execution summary for the current Dataset
1807 -- ------------------------------------------------------
1808
1809 -- Print the Execution Summary list prompt
1810 Write_New_Line;
1811 Write_Message
1812 (p_app_name => 'FEM',
1813 p_msg_name => 'FEM_INTG_BAL_EXE_SUMMARY');
1814 Write_New_Line;
1815
1816 -- Set the current list for printing is Execution Summary
1817 pv_curr_list := 'EXEC_SUMMARY';
1818 -- Check if we need to start a new page to print the list
1819 IF (pv_line_count <= pc_page_line_no - 2)
1820 THEN
1821 -- At least 3 lines are left for this page, so print the list header
1822 Write_Message
1823 (p_app_name => 'FEM',
1824 p_msg_name => 'FEM_INTG_BAL_EXE_TABLE_COLS');
1825 Write_Message
1826 (p_app_name => 'FEM',
1827 p_msg_name => 'FEM_INTG_BAL_EXE_TABLE_COL_LN');
1828
1829 ELSE
1830 -- Less than 3 lines are left for this page, so start a new page and
1831 -- print the list header on the new page
1832 Print_Report_Hdr;
1833
1834 END IF; -- IF (pv_line_count <= pc_page_line_no - 2)
1835
1836 -- Open the cursor for Execution Summary list line text for the
1837 -- current Dataset
1838 OPEN Cur_ExecList_Line(v_ds_code);
1839 -- Loop for each line and write it to the report
1840 LOOP
1841 FETCH Cur_ExecList_Line INTO v_line_text;
1842 EXIT WHEN Cur_ExecList_Line%NOTFOUND;
1843 Write_Line(v_line_text);
1844 END LOOP;
1845 -- Finish printing the list so set it back to NONE
1846 pv_curr_list := 'NONE';
1847 -- Close the cursor
1848 CLOSE Cur_ExecList_Line;
1849 */
1850 END IF; -- IF (v_print_exec_list = 'Y')
1851
1852 END LOOP; -- End of Dataset cursor loop
1853 END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_min_valid_period_eff_num <> -1)
1854
1855 -- Log the function exit time to FND_LOG (successful completion)
1856 FEM_ENGINES_PKG.Tech_Message
1857 (p_severity => pc_log_level_procedure,
1858 p_module => v_module,
1859 p_app_name => 'FEM',
1860 p_msg_name => 'FEM_GL_POST_202',
1861 p_token1 => 'FUNC_NAME',
1862 p_value1 => v_func_name,
1863 p_token2 => 'TIME',
1864 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1865 EXCEPTION
1866 WHEN OGLEngMain_FatalErr THEN
1867 -- <<< Fatal error >>>
1868
1869 -- Set the return code to indicate fatal error
1870 x_completion_code := 2;
1871
1872 -- Log the function exit time to FND_LOG (with error)
1873 FEM_ENGINES_PKG.Tech_Message
1874 (p_severity => pc_log_level_procedure,
1875 p_module => v_module,
1876 p_app_name => 'FEM',
1877 p_msg_name => 'FEM_GL_POST_203',
1878 p_token1 => 'FUNC_NAME',
1879 p_value1 => v_func_name,
1880 p_token2 => 'TIME',
1881 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1882
1883 WHEN Others THEN
1884 -- <<< Unexpected database exceptions >>>
1885
1886 -- Set the return code to indicate fatal error
1887 x_completion_code := 2;
1888
1889 -- Log the unexpected error message
1890 FEM_ENGINES_PKG.Tech_Message
1891 (p_severity => pc_log_level_unexpected,
1892 p_module => v_module,
1893 p_app_name => 'FEM',
1894 p_msg_name => 'FEM_GL_POST_215',
1895 p_token1 => 'ERR_MSG',
1896 p_value1 => SQLERRM);
1897 FEM_ENGINES_PKG.User_Message
1898 (p_app_name => 'FEM',
1899 p_msg_name => 'FEM_GL_POST_215',
1900 p_token1 => 'ERR_MSG',
1901 p_value1 => SQLERRM);
1902
1903 -- Log the function exit time to FND_LOG (with error)
1904 FEM_ENGINES_PKG.Tech_Message
1905 (p_severity => pc_log_level_procedure,
1906 p_module => v_module,
1907 p_app_name => 'FEM',
1908 p_msg_name => 'FEM_GL_POST_203',
1909 p_token1 => 'FUNC_NAME',
1910 p_value1 => v_func_name,
1911 p_token2 => 'TIME',
1912 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1913
1914 END Generate_Report;
1915
1916 --
1917 -- Procedure
1918 -- Print_Report_Hdr
1919 -- Purpose
1920 -- This routine prints the execution report header
1921 -- History
1922 -- 01-04-05 L Poon Created
1923 -- Arguments
1924 -- None
1925 PROCEDURE Print_Report_Hdr IS
1926 v_module VARCHAR2(100);
1927 v_func_name VARCHAR2(80);
1928
1929 v_line_text VARCHAR2(1000);
1930 v_message VARCHAR2(1000);
1931 BEGIN
1932 v_module := 'fem.plsql.fem_intg_bal_eng.print_report_hdr';
1933 v_func_name := 'FEM_INTG_BAL_RULE_ENG_PKG.Print_Report_Hdr';
1934
1935 -- Log the function entry time to FND_LOG
1936 FEM_ENGINES_PKG.Tech_Message
1937 (p_severity => pc_log_level_procedure,
1938 p_module => v_module,
1939 p_app_name => 'FEM',
1940 p_msg_name => 'FEM_GL_POST_201',
1941 p_token1 => 'FUNC_NAME',
1942 p_value1 => v_func_name,
1943 p_token2 => 'TIME',
1944 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1945
1946 -- If this is not the first page and it needs to print blanks lines before
1947 -- going to a new page
1948 IF (pv_page_count <> 0 AND pv_line_count <= pc_page_line_no)
1949 THEN
1950 FND_FILE.new_line(FND_FILE.output, (pc_page_line_no - pv_line_count + 1));
1951 END IF; -- IF (pv_page_count <> 0 AND pv_line_count <= pc_page_line_no)
1952
1953 -- Increment the page count by 1 and set line count to 1 for a new page
1954 pv_page_count := pv_page_count + 1;
1955 pv_line_count := 1;
1956
1957 -- Print 1 blank line
1958 FND_FILE.new_line(FND_FILE.output, 1);
1959
1960 -- Set the line starting with the report date (i.e. current date)
1961 v_message := FND_MESSAGE.get_string('FND', 'DATE') || ': '
1962 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS');
1963 v_line_text := RPAD(v_message, 36, ' ');
1964 -- Append report title
1965 v_message := FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_RPT_TITLE');
1966 v_line_text := v_line_text || LPAD(v_message, 48, ' ');
1967 -- Append page number
1968 v_message := FND_MESSAGE.get_string('FEM', 'FEM_INTG_BAL_PAGE_PROMPT')
1969 || ' ' || TO_CHAR(pv_page_count);
1970 v_line_text := v_line_text || LPAD(v_message, 48, ' ');
1971
1972 -- Print the report header line
1973 FND_FILE.put_line(FND_FILE.output, v_line_text);
1974
1975 -- Print 1 blank line
1976 FND_FILE.new_line(FND_FILE.output, 1);
1977
1978 -- Added 3 more lines
1979 pv_line_count := pv_line_count + 3;
1980
1981 -- Check if we need to print any list header for the new page
1982 IF (pv_curr_list = 'INVALID_PERIODS')
1983 THEN
1984 -- We're printing the Invalid Periods list, so print its header
1985 Write_Message
1986 (p_app_name => 'FEM',
1987 p_msg_name => 'FEM_INTG_BAL_PER_TABLE_COLS');
1988 Write_Message
1989 (p_app_name => 'FEM',
1990 p_msg_name => 'FEM_INTG_BAL_PER_TABLE_COL_LN');
1991 -- Added 2 more lines
1992 pv_line_count := pv_line_count + 2;
1993 ELSIF (pv_curr_list = 'REG_STATUS')
1994 THEN
1995 -- We're printing the Registration Statuses, so print its header
1996 Write_Message
1997 (p_app_name => 'FEM',
1998 p_msg_name => 'FEM_INTG_BAL_REG_TABLE_COLS');
1999 Write_Message
2000 (p_app_name => 'FEM',
2001 p_msg_name => 'FEM_INTG_BAL_REG_TABLE_COL_LN');
2002 -- Added 2 more lines
2003 pv_line_count := pv_line_count + 2;
2004 ELSIF (pv_curr_list = 'UNMAPPED_ACCOUNTS')
2005 THEN
2006 -- We're printing the Unmapped Accounts list, so print its header
2007 Write_Message
2008 (p_app_name => 'FEM',
2009 p_msg_name => 'FEM_INTG_BAL_ACCT_TABLE_COLS');
2010 Write_Message
2011 (p_app_name => 'FEM',
2012 p_msg_name => 'FEM_INTG_BAL_ACCT_TABLE_COL_LN');
2013 -- Added 2 more lines
2014 pv_line_count := pv_line_count + 2;
2015 ELSIF (pv_curr_list = 'NODATA_PERIODS')
2016 THEN
2017 -- We're printing the No-data-found Snapshot Periods list, so print its
2018 -- header
2019 Write_Message
2020 (p_app_name => 'FEM',
2021 p_msg_name => 'FEM_INTG_BAL_NODA_TABLE_COLS');
2022 Write_Message
2023 (p_app_name => 'FEM',
2024 p_msg_name => 'FEM_INTG_BAL_NODA_TABLE_COL_LN');
2025 -- Added 2 more lines
2026 pv_line_count := pv_line_count + 2;
2027
2028 -- Bug fix 4313353: Commented out the codes to print Execution Summary list
2029 -- header
2030 /*
2031 ELSIF (pv_curr_list = 'EXEC_SUMMARY')
2032 THEN
2033 -- We're printing the Execution Summary list, so print its header
2034 Write_Message
2035 (p_app_name => 'FEM',
2036 p_msg_name => 'FEM_INTG_BAL_EXE_TABLE_COLS');
2037 Write_Message
2038 (p_app_name => 'FEM',
2039 p_msg_name => 'FEM_INTG_BAL_EXE_TABLE_COL_LN');
2040 -- Added 2 more lines
2041 pv_line_count := pv_line_count + 2;
2042 */
2043 END IF;
2044
2045 -- Log the function exit time to FND_LOG (successful completion)
2046 FEM_ENGINES_PKG.Tech_Message
2047 (p_severity => pc_log_level_procedure,
2048 p_module => v_module,
2049 p_app_name => 'FEM',
2050 p_msg_name => 'FEM_GL_POST_202',
2051 p_token1 => 'FUNC_NAME',
2052 p_value1 => v_func_name,
2053 p_token2 => 'TIME',
2054 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2055 EXCEPTION
2056 WHEN Others THEN
2057 -- <<< Unexpected database exceptions >>>
2058
2059 -- Log the unexpected error message
2060 FEM_ENGINES_PKG.Tech_Message
2061 (p_severity => pc_log_level_unexpected,
2062 p_module => v_module,
2063 p_app_name => 'FEM',
2064 p_msg_name => 'FEM_GL_POST_215',
2065 p_token1 => 'ERR_MSG',
2066 p_value1 => SQLERRM);
2067 FEM_ENGINES_PKG.User_Message
2068 (p_app_name => 'FEM',
2069 p_msg_name => 'FEM_GL_POST_215',
2070 p_token1 => 'ERR_MSG',
2071 p_value1 => SQLERRM);
2072
2073 -- Log the function exit time to FND_LOG (with error)
2074 FEM_ENGINES_PKG.Tech_Message
2075 (p_severity => pc_log_level_procedure,
2076 p_module => v_module,
2077 p_app_name => 'FEM',
2078 p_msg_name => 'FEM_GL_POST_203',
2079 p_token1 => 'FUNC_NAME',
2080 p_value1 => v_func_name,
2081 p_token2 => 'TIME',
2082 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2083
2084 Raise OGLEngMain_FatalErr;
2085
2086 END Print_Report_Hdr;
2087
2088 --
2089 -- Procedure
2090 -- Write_Message
2091 -- Purpose
2092 -- This routine write the message text to the output file. If the message
2093 -- length is longer than 132 i.e. the report width, it will write the text
2094 -- into multiple lines.
2095 -- History
2096 -- 01-04-05 L Poon Created
2097 -- Arguments
2098 -- p_app_name Applicaiton name of the message to be written into the report
2099 -- p_msg_name Name of the message to be written into the report
2100 -- p_token<i> Name of <i>th message token
2101 -- p_value<i> Value of <i>th message token
2102 -- p_trans<i> Indicate if the translation is needed for <i>th message token
2103 PROCEDURE Write_Message
2104 ( p_app_name IN VARCHAR2
2105 , p_msg_name IN VARCHAR2
2106 , p_token1 IN VARCHAR2 DEFAULT NULL
2107 , p_value1 IN VARCHAR2 DEFAULT NULL
2108 , p_trans1 IN VARCHAR2 DEFAULT NULL
2109 , p_token2 IN VARCHAR2 DEFAULT NULL
2110 , p_value2 IN VARCHAR2 DEFAULT NULL
2111 , p_trans2 IN VARCHAR2 DEFAULT NULL
2112 , p_token3 IN VARCHAR2 DEFAULT NULL
2113 , p_value3 IN VARCHAR2 DEFAULT NULL
2114 , p_trans3 IN VARCHAR2 DEFAULT NULL
2115 , p_token4 IN VARCHAR2 DEFAULT NULL
2116 , p_value4 IN VARCHAR2 DEFAULT NULL
2117 , p_trans4 IN VARCHAR2 DEFAULT NULL
2118 , p_token5 IN VARCHAR2 DEFAULT NULL
2119 , p_value5 IN VARCHAR2 DEFAULT NULL
2120 , p_trans5 IN VARCHAR2 DEFAULT NULL
2121 , p_token6 IN VARCHAR2 DEFAULT NULL
2122 , p_value6 IN VARCHAR2 DEFAULT NULL
2123 , p_trans6 IN VARCHAR2 DEFAULT NULL
2124 , p_token7 IN VARCHAR2 DEFAULT NULL
2125 , p_value7 IN VARCHAR2 DEFAULT NULL
2126 , p_trans7 IN VARCHAR2 DEFAULT NULL
2127 , p_token8 IN VARCHAR2 DEFAULT NULL
2128 , p_value8 IN VARCHAR2 DEFAULT NULL
2129 , p_trans8 IN VARCHAR2 DEFAULT NULL
2130 , p_token9 IN VARCHAR2 DEFAULT NULL
2131 , p_value9 IN VARCHAR2 DEFAULT NULL
2132 , p_trans9 IN VARCHAR2 DEFAULT NULL) IS
2133 v_module VARCHAR2(100);
2134 v_func_name VARCHAR2(80);
2135
2136 v_token VARCHAR2(30);
2137 v_value VARCHAR2(4000);
2138 v_trans BOOLEAN;
2139
2140 TYPE msg_array IS VARRAY(27) OF VARCHAR2(4000);
2141 tokens_values msg_array;
2142 v_msg_text VARCHAR2(2000);
2143 v_str_buf VARCHAR2(200);
2144 v_str_len NUMBER;
2145 v_str_i NUMBER;
2146
2147 BEGIN
2148 v_module := 'fem.plsql.fem_intg_bal_eng.write_message';
2149 v_func_name := 'FEM_INTG_BAL_RULE_ENG_PKG.Write_Message';
2150
2151 -- Log the function entry time to FND_LOG
2152 FEM_ENGINES_PKG.Tech_Message
2153 (p_severity => pc_log_level_procedure,
2154 p_module => v_module,
2155 p_app_name => 'FEM',
2156 p_msg_name => 'FEM_GL_POST_201',
2157 p_token1 => 'FUNC_NAME',
2158 p_value1 => v_func_name,
2159 p_token2 => 'TIME',
2160 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2161
2162 -- Set application short name and message name
2163 FND_MESSAGE.SET_NAME(p_app_name, p_msg_name);
2164
2165 -- Load message token/value array
2166 tokens_values := msg_array
2167 (p_token1,p_value1,p_trans1,
2168 p_token2,p_value2,p_trans2,
2169 p_token3,p_value3,p_trans3,
2170 p_token4,p_value4,p_trans4,
2171 p_token5,p_value5,p_trans5,
2172 p_token6,p_value6,p_trans6,
2173 p_token7,p_value7,p_trans7,
2174 p_token8,p_value8,p_trans8,
2175 p_token9,p_value9,p_trans9);
2176
2177 -- Substitute values for tokens
2178 FOR i IN 1..27 LOOP
2179 IF (MOD(i,3) = 1)
2180 THEN
2181 v_token := tokens_values(i);
2182 IF (v_token IS NOT NULL)
2183 THEN
2184 v_value := tokens_values(i+1);
2185 IF (tokens_values(i+2) = 'Y')
2186 THEN
2187 v_trans := TRUE;
2188 ELSE
2189 v_trans := FALSE;
2190 END IF;
2191 FND_MESSAGE.SET_TOKEN(v_token,v_value,v_trans);
2192 ELSE
2193 EXIT;
2194 END IF; -- IF (v_token IS NOT NULL)
2195 END IF; -- IF (MOD(i,3) = 1)
2196 END LOOP;
2197
2198 -- Get the message text
2199 v_msg_text := FND_MESSAGE.Get;
2200
2201 -- Write the message text line by line into the report
2202 v_str_i := 1;
2203 WHILE (v_str_i <= LENGTHB(v_msg_text))
2204 LOOP
2205 v_str_buf := SUBSTRB(v_msg_text, v_str_i, 133);
2206 v_str_len := 132;
2207
2208 IF (LENGTHB(v_str_buf) >= 133)
2209 THEN
2210 FOR j IN 0..132 LOOP
2211 IF (SUBSTRB(v_str_buf, 133 - j, 1) = ' ')
2212 THEN
2213
2214 IF (j > 0)
2215 THEN
2216 v_str_len := 133 - j;
2217 END IF;
2218
2219 EXIT;
2220 END IF;
2221 END LOOP;
2222 END IF;
2223
2224 Write_Line(SUBSTRB(v_str_buf, 1, v_str_len));
2225 v_str_i := v_str_i + v_str_len;
2226 END LOOP;
2227
2228 -- Log the function exit time to FND_LOG (successful completion)
2229 FEM_ENGINES_PKG.Tech_Message
2230 (p_severity => pc_log_level_procedure,
2231 p_module => v_module,
2232 p_app_name => 'FEM',
2233 p_msg_name => 'FEM_GL_POST_202',
2234 p_token1 => 'FUNC_NAME',
2235 p_value1 => v_func_name,
2236 p_token2 => 'TIME',
2237 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2238
2239 EXCEPTION
2240 WHEN Others THEN
2241 -- <<< Unexpected database exceptions >>>
2242
2243 -- Log the unexpected error message
2244 FEM_ENGINES_PKG.Tech_Message
2245 (p_severity => pc_log_level_unexpected,
2246 p_module => v_module,
2247 p_app_name => 'FEM',
2248 p_msg_name => 'FEM_GL_POST_215',
2249 p_token1 => 'ERR_MSG',
2250 p_value1 => SQLERRM);
2251 FEM_ENGINES_PKG.User_Message
2252 (p_app_name => 'FEM',
2253 p_msg_name => 'FEM_GL_POST_215',
2254 p_token1 => 'ERR_MSG',
2255 p_value1 => SQLERRM);
2256
2257 -- Log the function exit time to FND_LOG (with error)
2258 FEM_ENGINES_PKG.Tech_Message
2259 (p_severity => pc_log_level_procedure,
2260 p_module => v_module,
2261 p_app_name => 'FEM',
2262 p_msg_name => 'FEM_GL_POST_203',
2263 p_token1 => 'FUNC_NAME',
2264 p_value1 => v_func_name,
2265 p_token2 => 'TIME',
2266 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2267
2268 Raise OGLEngMain_FatalErr;
2269
2270 END Write_Message;
2271
2272 --
2273 -- Procedure
2274 -- Write_New_Line
2275 -- Purpose
2276 -- This routine write a blank line to the report
2277 -- History
2278 -- 01-04-05 L Poon Created
2279 -- Arguments
2280 -- None
2281 PROCEDURE Write_New_Line IS
2282 v_module VARCHAR2(100);
2283 v_func_name VARCHAR2(80);
2284
2285 BEGIN
2286 v_module := 'fem.plsql.fem_intg_bal_eng.write_new_line';
2287 v_func_name := 'FEM_INTG_BAL_RULE_ENG_PKG.Write_New_Line';
2288
2289 -- Log the function entry time to FND_LOG
2290 FEM_ENGINES_PKG.Tech_Message
2291 (p_severity => pc_log_level_procedure,
2292 p_module => v_module,
2293 p_app_name => 'FEM',
2294 p_msg_name => 'FEM_GL_POST_201',
2295 p_token1 => 'FUNC_NAME',
2296 p_value1 => v_func_name,
2297 p_token2 => 'TIME',
2298 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2299
2300 -- Check if the line count is more than the number of lines per page
2301 IF (pv_line_count > pc_page_line_no)
2302 THEN
2303 -- A new page is needed to print this new line, so call Print_Report_Hdr
2304 -- to print the report header for the new page, set pv_line_count to 1,
2305 -- and increment pv_page_count by 1.
2306 Print_Report_Hdr;
2307 END IF;
2308
2309 -- Write a new line to the output and increment the line count by 1
2310 FND_FILE.new_line(FND_FILE.output, 1);
2311 pv_line_count := pv_line_count + 1;
2312
2313 -- Log the function exit time to FND_LOG (successful completion)
2314 FEM_ENGINES_PKG.Tech_Message
2315 (p_severity => pc_log_level_procedure,
2316 p_module => v_module,
2317 p_app_name => 'FEM',
2318 p_msg_name => 'FEM_GL_POST_202',
2319 p_token1 => 'FUNC_NAME',
2320 p_value1 => v_func_name,
2321 p_token2 => 'TIME',
2322 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2323
2324 EXCEPTION
2325 WHEN Others THEN
2326 -- <<< Unexpected database exceptions >>>
2327
2328 -- Log the unexpected error message
2329 FEM_ENGINES_PKG.Tech_Message
2330 (p_severity => pc_log_level_unexpected,
2331 p_module => v_module,
2332 p_app_name => 'FEM',
2333 p_msg_name => 'FEM_GL_POST_215',
2334 p_token1 => 'ERR_MSG',
2335 p_value1 => SQLERRM);
2336 FEM_ENGINES_PKG.User_Message
2337 (p_app_name => 'FEM',
2338 p_msg_name => 'FEM_GL_POST_215',
2339 p_token1 => 'ERR_MSG',
2340 p_value1 => SQLERRM);
2341
2342 -- Log the function exit time to FND_LOG (with error)
2343 FEM_ENGINES_PKG.Tech_Message
2344 (p_severity => pc_log_level_procedure,
2345 p_module => v_module,
2346 p_app_name => 'FEM',
2347 p_msg_name => 'FEM_GL_POST_203',
2348 p_token1 => 'FUNC_NAME',
2349 p_value1 => v_func_name,
2350 p_token2 => 'TIME',
2351 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2352
2353 Raise OGLEngMain_FatalErr;
2354
2355 END Write_New_line;
2356
2357 --
2358 -- Procedure
2359 -- Write_Line
2360 -- Purpose
2361 -- This routine write a line to the report
2362 -- History
2363 -- 01-04-05 L Poon Created
2364 -- Arguments
2365 -- None
2366 PROCEDURE Write_Line
2367 (p_line_text IN VARCHAR2) IS
2368 v_module VARCHAR2(100);
2369 v_func_name VARCHAR2(80);
2370
2371 BEGIN
2372 v_module := 'fem.plsql.fem_intg_bal_eng.write_line';
2373 v_func_name := 'FEM_INTG_BAL_RULE_ENG_PKG.Write_Line';
2374
2375 -- Log the function entry time to FND_LOG
2376 FEM_ENGINES_PKG.Tech_Message
2377 (p_severity => pc_log_level_procedure,
2378 p_module => v_module,
2379 p_app_name => 'FEM',
2380 p_msg_name => 'FEM_GL_POST_201',
2381 p_token1 => 'FUNC_NAME',
2382 p_value1 => v_func_name,
2383 p_token2 => 'TIME',
2384 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2385
2386 -- Check if the line count is more than the number of lines per page
2387 IF (pv_line_count > pc_page_line_no)
2388 THEN
2389 -- A new page is needed to print this line, so call Print_Report_Hdr to
2390 -- print the report header for the new page, set pv_line_count to 1, and
2391 -- increment pv_page_count by 1.
2392 Print_Report_Hdr;
2393 END IF;
2394
2395 -- Write the line text to the output and increment the line count by 1
2396 FND_FILE.put_line(FND_FILE.output, p_line_text);
2397 pv_line_count := pv_line_count + 1;
2398
2399 -- Log the function exit time to FND_LOG (successful completion)
2400 FEM_ENGINES_PKG.Tech_Message
2401 (p_severity => pc_log_level_procedure,
2402 p_module => v_module,
2403 p_app_name => 'FEM',
2404 p_msg_name => 'FEM_GL_POST_202',
2405 p_token1 => 'FUNC_NAME',
2406 p_value1 => v_func_name,
2407 p_token2 => 'TIME',
2408 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2409
2410 EXCEPTION
2411 WHEN Others THEN
2412 -- <<< Unexpected database exceptions >>>
2413
2414 -- Log the unexpected error message
2415 FEM_ENGINES_PKG.Tech_Message
2416 (p_severity => pc_log_level_unexpected,
2417 p_module => v_module,
2418 p_app_name => 'FEM',
2419 p_msg_name => 'FEM_GL_POST_215',
2420 p_token1 => 'ERR_MSG',
2421 p_value1 => SQLERRM);
2422 FEM_ENGINES_PKG.User_Message
2423 (p_app_name => 'FEM',
2424 p_msg_name => 'FEM_GL_POST_215',
2425 p_token1 => 'ERR_MSG',
2426 p_value1 => SQLERRM);
2427
2428 -- Log the function exit time to FND_LOG (with error)
2429 FEM_ENGINES_PKG.Tech_Message
2430 (p_severity => pc_log_level_procedure,
2431 p_module => v_module,
2432 p_app_name => 'FEM',
2433 p_msg_name => 'FEM_GL_POST_203',
2434 p_token1 => 'FUNC_NAME',
2435 p_value1 => v_func_name,
2436 p_token2 => 'TIME',
2437 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2438
2439 Raise OGLEngMain_FatalErr;
2440
2441 END Write_line;
2442
2443 END FEM_INTG_BAL_RULE_ENG_PKG;