[Home] [Help]
PACKAGE BODY: APPS.FEM_XGL_POST_ENGINE_PKG
Source
1 PACKAGE BODY FEM_XGL_POST_ENGINE_PKG AS
2 /* $Header: fem_xgl_post_eng.plb 120.6 2007/06/27 19:11:57 ghall ship $ */
3
4 /***********************************************************************
5 * 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_error CONSTANT NUMBER := FND_LOG.level_error;
12 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
13
14 pv_snapshot_rows_done BOOLEAN;
15 pv_incr_marking_sql_done BOOLEAN;
16 pv_pkg_variables_reset BOOLEAN := FALSE;
17 pv_req_id_slice NUMBER;
18 pv_process_slice VARCHAR2(50);
19 pv_data_slice_predicate VARCHAR2(32767);
20 pv_partition_clause VARCHAR2(100);
21 pv_schema_name VARCHAR2(30);
22 pv_marking_sql VARCHAR2(32767);
23
24
25 /***********************************************************************
26 * PRIVATE FUNCTIONS *
27 ***********************************************************************/
28
29 -- ======================================================================
30 -- Procedure
31 -- Finish_Condition_String
32 -- Purpose
33 -- Shared code between Main() and Mark_Rows_For_Process(), to
34 -- complete the common portion of SQL that is similar between
35 -- the two routines.
36 -- History
37 -- 05-04-04 G Hall Bug# 3597527: Created
38 -- Arguments
39 -- x_condition_string Comes in as a partial condition string;
40 -- goes out as a complete one.
41 -- ======================================================================
42
43 PROCEDURE Finish_Condition_String
44 (x_condition_string IN OUT NOCOPY VARCHAR2) IS
45
46 BEGIN
47
48 FEM_ENGINES_PKG.Tech_Message
49 (p_severity => pc_log_level_procedure,
50 p_module => 'fem.plsql.xgl_eng.fcs.' || pv_process_slice,
51 p_app_name => 'FEM',
52 p_msg_name => 'FEM_GL_POST_201',
53 p_token1 => 'FUNC_NAME',
54 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Finish_Condition_String',
55 p_token2 => 'TIME',
56 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
57
58 x_condition_string := x_condition_string ||
59 ' AND cal_per_dim_grp_display_code = ''' ||
60 FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd || '''' ||
61 ' AND cal_period_end_date = TO_DATE(''' ||
62 TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date, 'YYYY/MM/DD HH24:MI:SS') ||
63 ''', ''YYYY/MM/DD HH24:MI:SS'')' ||
64 ' AND cal_period_number = TO_NUMBER(''' ||
65 TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_gl_per_number) || ''')' ||
66 ' AND ledger_display_code = ''' ||
67 FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd || '''' ||
68 ' AND ds_balance_type_code = ''' ||
69 FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd || '''';
70
71 IF FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd IS NOT NULL THEN
72
73 x_condition_string := x_condition_string ||
74 ' AND budget_display_code = ''' ||
75 FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd || '''';
76
77 ELSIF FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd IS NOT NULL THEN
78
79 x_condition_string := x_condition_string ||
80 ' AND encumbrance_type_code = ''' ||
81 FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd || '''';
82 END IF;
83
84 FEM_ENGINES_PKG.Tech_Message
85 (p_severity => pc_log_level_procedure,
86 p_module => 'fem.plsql.xgl_eng.fcs.' || pv_process_slice,
87 p_app_name => 'FEM',
88 p_msg_name => 'FEM_GL_POST_202',
89 p_token1 => 'FUNC_NAME',
90 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Finish_Condition_String',
91 p_token2 => 'TIME',
92 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
93
94 END Finish_Condition_String;
95
96
97 -- ======================================================================
98 -- Procedure
99 -- Mark_Rows_For_Process
100 -- Purpose
101 -- This routine will mark all records in FEM_BAL_INTERFACE_T to be
102 -- processed by this posting run based on the execution mode.
103 -- History
104 -- 11-03-03 S Kung Created
105 -- 05-05-04 G Hall Bug# 3597527: Implemented changes for
106 -- multiprocessing
107 -- Arguments
108 -- p_load_set_id The Load Set ID being processed
109 -- x_row_count_in_set Passes back the number of rows marked
110 -- x_completion_code Completion status of the routine
111 -- ======================================================================
112
113 PROCEDURE Mark_Rows_For_Process
114 (p_load_set_id IN NUMBER,
115 x_row_count_in_set OUT NOCOPY NUMBER,
116 x_completion_code OUT NOCOPY NUMBER) IS
117
118 BEGIN
119
120 FEM_ENGINES_PKG.Tech_Message
121 (p_severity => pc_log_level_procedure,
122 p_module => 'fem.plsql.xgl_eng.mrfp.' || pv_process_slice,
123 p_app_name => 'FEM',
124 p_msg_name => 'FEM_GL_POST_201',
125 p_token1 => 'FUNC_NAME',
126 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Mark_Rows_For_Process',
127 p_token2 => 'TIME',
128 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
129
130 x_completion_code := 0;
131 x_row_count_in_set := 0;
132
133 -- ---------------------------------------------------------------------
134 -- Prepare the dynamic SQL string for marking rows to be posted by
135 -- this pass. The SQL is built once for the snapshot pass. It is
136 -- rebuilt once in the first incremental pass (for the first load
137 -- set). Subsequent incremental passes for subsequent load sets
138 -- will reuse the same statement with a bind variable for Load Set ID.
139 -- ---------------------------------------------------------------------
140
141 IF (NOT pv_snapshot_rows_done) OR (NOT pv_incr_marking_sql_done) THEN
142
143 pv_marking_sql :=
144 'UPDATE fem_bal_interface_t ' || pv_partition_clause ||
145 ' SET posting_request_id = :req_id_slice, ' ||
146 'previous_error_flag = ' ||
147 'DECODE(posting_error_code, NULL, NULL, ''Y''), ' ||
148 'posting_error_code = NULL ';
149
150 END IF;
151
152 IF NOT pv_snapshot_rows_done THEN
153
154 -- Mark records with load_method_code of 'S'. We ignore
155 -- LOAD_SET_ID when processing the snapshot rows.
156
157 pv_marking_sql := pv_marking_sql || 'WHERE load_method_code = ''S'' ';
158
159 IF FEM_GL_POST_PROCESS_PKG.pv_exec_mode <> 'S' THEN
160
161 -- For Snapshot mode, we ignore the posting_error_code because
162 -- we will process everything.
163 -- For Incremental and Error Reprocessing modes, we will only pick
164 -- up snapshot rows marked with an error before.
165
166 pv_marking_sql := pv_marking_sql ||
167 'AND posting_error_code IS NOT NULL ';
168
169 END IF;
170
171 ELSIF NOT pv_incr_marking_sql_done THEN
172
173 -- If pv_snapshot_rows_done is TRUE and this routine is called,
174 -- the program is being run in either Incremental or Error
175 -- Reprocessing modes. At this point in time, all snapshot rows
176 -- would have been processed so we only need to look at rows
177 -- with LOAD_METHOD_CODE of 'I' for the given load set.
178
179 pv_marking_sql := pv_marking_sql ||
180 'WHERE load_set_id = :load_set_id ' ||
181 'AND load_method_code = ''I'' ';
182
183 IF FEM_GL_POST_PROCESS_PKG.pv_exec_mode = 'E' THEN
184
185 -- For Error Reprocessing mode, we will only pick up rows
186 -- marked with an error before.
187
188 pv_marking_sql := pv_marking_sql ||
189 'AND posting_error_code IS NOT NULL ';
190
191 END IF;
192
193 END IF;
194
195 IF (NOT pv_snapshot_rows_done) OR (NOT pv_incr_marking_sql_done) THEN
196
197 Finish_Condition_String(pv_marking_sql);
198
199 pv_marking_sql := pv_marking_sql || ' AND ' || pv_data_slice_predicate;
200
201 FEM_ENGINES_PKG.Tech_Message
202 (p_severity => pc_log_level_procedure,
203 p_module => 'fem.plsql.xgl_eng.mrfp.' || pv_process_slice,
204 p_msg_text => 'pv_marking_sql: ' || pv_marking_sql);
205
206 END IF;
207
208 -- ---------------------------------------------------------------------
209 -- Execute the dynamic SQL to mark the rows for the current pass.
210 -- ---------------------------------------------------------------------
211
212 IF NOT pv_snapshot_rows_done THEN
213
214 FEM_ENGINES_PKG.Tech_Message
215 (p_severity => pc_log_level_statement,
216 p_module => 'fem.plsql.xgl_eng.mrfp.' || pv_process_slice,
217 p_msg_text => 'Marking snapshot rows...');
218
219 EXECUTE IMMEDIATE pv_marking_sql
220 USING pv_req_id_slice;
221
222 ELSE
223
224 EXECUTE IMMEDIATE pv_marking_sql
225 USING pv_req_id_slice, p_load_set_id;
226
227 END IF;
228
229 x_row_count_in_set := SQL%ROWCOUNT;
230
231 FEM_ENGINES_PKG.Tech_Message
232 (p_severity => pc_log_level_statement,
233 p_module => 'fem.plsql.xgl_eng.mrfp.' || pv_process_slice,
234 p_app_name => 'FEM',
235 p_msg_name => 'FEM_GL_POST_217',
236 p_token1 => 'NUM',
237 p_value1 => TO_CHAR(x_row_count_in_set),
238 p_token2 => 'TABLE',
239 p_value2 => 'FEM_BAL_INTERFACE_T');
240
241 FEM_ENGINES_PKG.Tech_Message
242 (p_severity => pc_log_level_procedure,
243 p_module => 'fem.plsql.xgl_eng.mrfp.' || pv_process_slice,
244 p_app_name => 'FEM',
245 p_msg_name => 'FEM_GL_POST_202',
246 p_token1 => 'FUNC_NAME',
247 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Mark_Rows_For_Process',
248 p_token2 => 'TIME',
249 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
250
251 COMMIT;
252
253 EXCEPTION
254 WHEN OTHERS THEN
255
256 ROLLBACK;
257
258 x_completion_code := 2;
259
260 FEM_ENGINES_PKG.User_Message
261 (p_app_name => 'FEM',
262 p_msg_name => 'FEM_GL_POST_215',
263 p_token1 => 'ERR_MSG',
264 p_value1 => SQLERRM);
265
266 FEM_ENGINES_PKG.Tech_Message
267 (p_severity => pc_log_level_unexpected,
268 p_module => 'fem.plsql.xgl_eng.mrfp.' || pv_process_slice,
269 p_app_name => 'FEM',
270 p_msg_name => 'FEM_GL_POST_215',
271 p_token1 => 'ERR_MSG',
272 p_value1 => SQLERRM);
273
274 FEM_ENGINES_PKG.Tech_Message
275 (p_severity => pc_log_level_procedure,
276 p_module => 'fem.plsql.xgl_eng.mrfp.' || pv_process_slice,
277 p_app_name => 'FEM',
278 p_msg_name => 'FEM_GL_POST_203',
279 p_token1 => 'FUNC_NAME',
280 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Mark_Rows_For_Process',
281 p_token2 => 'TIME',
282 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
283
284 END Mark_Rows_For_Process;
285
286
287 -- =======================================================================
288 -- Procedure
289 -- Validate_Interface_Data
290 -- Purpose
291 -- This is the routine to validate data in the interface table
292 -- History
293 -- 12-03-03 W Wong Created
294 -- 02-02-04 S Kung Removed redundant validation points that
295 -- are now covered by the unique index.
296 -- 05-05-04 G Hall Bug# 3597527: Implemented changes for
297 -- multiprocessing.
298 -- 05-25-04 G Hall Removed building of v_key_stmt dynamic SQL which
299 -- is no longer used anywhere; fixed treatment
300 -- of CURRENCY_CODE and CURRENCY_TYPE_CODE as members
301 -- of the FEM_BALANCES processing key (by including
302 -- them in the mainstream logic in the building and
303 -- usage of v_key_stmt2).
304 -- 10-27-04 G Hall Bug# 3952885: Created new processing key dimensions
305 -- string v_key_stmt3 using NVL function for use in
306 -- validation #5. Modified validation SQL for #5 to
307 -- also mark rows in the current load set that have
308 -- "potentially" matching error rows in a previous
309 -- load set.
310 -- Arguments
311 -- p_load_set_id: Current load set ID to process
312 -- p_total_row_num: Total number of rows in the load set
313 -- x_records_to_post_flag: Output boolean indicating if there are any
314 -- records ready to be posted
315 -- x_num_invalid_record: Output parameter indicating the number of
316 -- invalid records found during validation
317 -- x_completion_code: Completion code of the procedure.
318 -- (0 for Success; 1 for Warning; 2 for Failure)
319 -- =======================================================================
320
321 PROCEDURE Validate_Interface_Data
322 (p_load_set_id IN NUMBER,
323 p_total_row_num IN NUMBER,
324 x_records_to_post_flag IN OUT NOCOPY BOOLEAN,
325 x_num_invalid_record IN OUT NOCOPY NUMBER,
326 x_completion_code IN OUT NOCOPY NUMBER ) IS
327
328 FEMXGL_mix_load_method EXCEPTION;
329
330 v_proc_snapshot_rows VARCHAR2(1);
331 v_sql_stmt VARCHAR2(32767);
332 v_key_stmt2 VARCHAR2(32767);
333 v_key_stmt3 VARCHAR2(32767);
334 v_first_time VARCHAR2(1);
335 v_rows_updated NUMBER;
336 v PLS_INTEGER;
337 pv_col VARCHAR2(30);
338
339 BEGIN
340
341 FEM_ENGINES_PKG.Tech_Message
342 (p_severity => pc_log_level_procedure,
343 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
344 p_app_name => 'FEM',
345 p_msg_name => 'FEM_GL_POST_201',
346 p_token1 => 'FUNC_NAME',
347 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Validate_Interface_Data',
348 p_token2 => 'TIME',
349 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
350
351 -- Initialize variable
352 x_num_invalid_record := 0;
353
354 IF (not pv_snapshot_rows_done) THEN
355 v_proc_snapshot_rows := 'Y';
356 ELSE
357 v_proc_snapshot_rows := 'N';
358 END IF;
359
360 ---------------------------------------------------------------------------
361 -- 1. Verify that there is only one load_method_code in the given load set.
362 -- (This is only needed for Incremental and Error Reprocessing loads)
363 ---------------------------------------------------------------------------
364
365 IF (FEM_GL_POST_PROCESS_PKG.pv_exec_mode <> 'S')
366 AND (v_proc_snapshot_rows = 'N') THEN
367 v_sql_stmt :=
368 ' UPDATE fem_bal_interface_t ' ||
369 ' SET posting_error_code = ''FEM_GL_POST_MIX_LOAD_METHOD'' ' ||
370 ' WHERE EXISTS ( SELECT 1 ' ||
371 ' FROM fem_bal_interface_t ' ||
372 ' WHERE posting_request_id = :pv_req_id_slice '||
373 ' AND load_set_id = :p_load_set_id ' ||
374 ' HAVING COUNT (DISTINCT load_method_code) > 1 ) ' ||
375 ' AND posting_request_id = :pv_req_id_slice ' ||
376 ' AND load_set_id = :p_load_set_id ';
377
378 -- Print out sql statement for debugging purposes
379 FEM_ENGINES_PKG.Tech_Message
380 (p_severity => pc_log_level_statement,
381 p_module => 'fem.plsql.xgl_eng.vid.1.' || pv_process_slice,
382 p_msg_text => 'v_sql_stmt: ' || v_sql_stmt);
383
384 EXECUTE IMMEDIATE v_sql_stmt
385 USING pv_req_id_slice, p_load_set_id,
386 pv_req_id_slice, p_load_set_id;
387
388 -- If mixed load_method_code is found in the set, no further validation
389 -- will be done for the set. We should return with a warning status.
390 IF ( SQL%ROWCOUNT > 0 ) THEN
391 raise FEMXGL_mix_load_method;
392 END IF;
393 END IF;
394
395 ---------------------------------------------------------------------------
396 -- Construct strings with processing keys
397 -- ( processing_key_1, ... processing_key_N )
398 ---------------------------------------------------------------------------
399 v_first_time := 'Y';
400 v_key_stmt2 := '';
401 v_key_stmt3 := '';
402
403 FEM_ENGINES_PKG.Tech_Message
404 (p_severity => pc_log_level_statement,
405 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
406 p_app_name => 'FEM',
407 p_msg_name => 'FEM_GL_POST_204',
408 p_token1 => 'VAR_NAME',
409 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_proc_key_dim_num',
410 p_token2 => 'VAR_VAL',
411 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_proc_key_dim_num);
412
413 FOR v IN 1..FEM_GL_POST_PROCESS_PKG.pv_proc_key_dim_num LOOP
414
415 IF (FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_int_disp_code_col IN
416 ('CCTR_ORG_DISPLAY_CODE', 'FINANCIAL_ELEM_DISPLAY_CODE',
417 'PRODUCT_DISPLAY_CODE', 'NATURAL_ACCOUNT_DISPLAY_CODE',
418 'CHANNEL_DISPLAY_CODE', 'LINE_ITEM_DISPLAY_CODE', 'CURRENCY_CODE',
419 'CURRENCY_TYPE_CODE', 'PROJECT_DISPLAY_CODE',
420 'CUSTOMER_DISPLAY_CODE', 'SOURCE_SYSTEM_DISPLAY_CODE',
421 'LEDGER_DISPLAY_CODE', 'ENTITY_DISPLAY_CODE',
422 'INTERCOMPANY_DISPLAY_CODE', 'TASK_DISPLAY_CODE',
423 'USER_DIM1_DISPLAY_CODE', 'USER_DIM2_DISPLAY_CODE',
424 'USER_DIM3_DISPLAY_CODE', 'USER_DIM4_DISPLAY_CODE',
425 'USER_DIM5_DISPLAY_CODE', 'USER_DIM6_DISPLAY_CODE',
426 'USER_DIM7_DISPLAY_CODE', 'USER_DIM8_DISPLAY_CODE',
427 'USER_DIM9_DISPLAY_CODE', 'USER_DIM10_DISPLAY_CODE')) THEN
428
429 pv_col :=
430 FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_int_disp_code_col;
431
432 IF v_first_time = 'N' THEN
433 v_key_stmt2 := v_key_stmt2 || ', ';
434 v_key_stmt3 := v_key_stmt3 || ', ';
435 ELSE
436 v_first_time := 'N';
437 END IF;
438
439 v_key_stmt2 := v_key_stmt2 || pv_col;
440 v_key_stmt3 := v_key_stmt3 || 'NVL(s.' || pv_col || ', u.' || pv_col || ')';
441
442 END IF;
443
444 END LOOP;
445
446 FEM_ENGINES_PKG.Tech_Message
447 (p_severity => pc_log_level_statement,
448 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
449 p_msg_text => 'v_key_stmt2: ' || v_key_stmt2);
450
451 FEM_ENGINES_PKG.Tech_Message
452 (p_severity => pc_log_level_statement,
453 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
454 p_msg_text => 'v_key_stmt3: ' || v_key_stmt3);
455
456 ---------------------------------------------------------------------------
457 -- 2. Check for duplicate rows, according to the FEM_BALANCES processing
458 -- key columns combination.
459 -- This check is always needed because:
460 -- 1) We definitely need to check this for Snapshot loads
461 -- 2) Incremental and Error Reprocessing loads can still pick up
462 -- snapshot rows that are marked with error from a prior Snapshot
463 -- load.
464 ---------------------------------------------------------------------------
465 IF v_proc_snapshot_rows = 'Y' THEN
466
467 v_sql_stmt :=
468 ' UPDATE fem_bal_interface_t ' ||
469 ' SET posting_error_code = ''FEM_GL_POST_DUP_PROC_KEYS'' ' ||
470 ' WHERE ( ' || v_key_stmt2 || ') IN' ||
471 ' ( SELECT ' || v_key_stmt2 ||
472 ' FROM fem_bal_interface_t ' ||
473 ' WHERE posting_request_id = :pv_req_id_slice ' ||
474 ' GROUP BY ' || v_key_stmt2 ||
475 ' HAVING COUNT(*) > 1) ' ||
476 ' AND posting_error_code IS NULL ' ||
477 ' AND load_method_code = ''S''' ||
478 ' AND posting_request_id = :pv_req_id_slice ';
479
480 -- Print out sql statement for debugging purposes
481 FEM_ENGINES_PKG.Tech_Message
482 (p_severity => pc_log_level_statement,
483 p_module => 'fem.plsql.xgl_eng.vid.2.' || pv_process_slice,
484 p_msg_text => 'v_sql_stmt: ' || v_sql_stmt);
485
486 EXECUTE IMMEDIATE v_sql_stmt
487 USING pv_req_id_slice,
488 pv_req_id_slice;
489
490 -- Keep track of how many invalid rows marked
491 x_num_invalid_record := x_num_invalid_record + SQL%ROWCOUNT;
492
493 END IF;
494
495 ---------------------------------------------------------------------------
496 -- 3. Verify that BAL_POST_TYPE_CODE, CURRENCY_TYPE_CODE are filled
497 -- in and are valid.
498 -- Valid values for BAL_POST_TYPE_CODE are R and A,
499 -- valid values for CURRENCY_TYPE_CODE are ENTERED and TRANSLATED
500 ---------------------------------------------------------------------------
501 v_sql_stmt :=
502 ' UPDATE fem_bal_interface_t ' ||
503 ' SET posting_error_code = ' ||
504 ' DECODE(currency_type_code, ' ||
505 ' ''ENTERED'', ' ||
506 ' DECODE(bal_post_type_code, ''A'', NULL, ' ||
507 ' ''R'', NULL, ''FEM_GL_POST_INVALID_POST_TYPE''), ' ||
508 ' ''TRANSLATED'', ' ||
509 ' DECODE(bal_post_type_code, ''A'', NULL, ' ||
510 ' ''R'', NULL, ''FEM_GL_POST_INVALID_POST_TYPE''), ' ||
511 ' ''FEM_GL_POST_INVALID_CURR_TYPE'') ' ||
512 ' WHERE (bal_post_type_code NOT IN (''A'', ''R'') OR ' ||
513 ' currency_type_code NOT IN (''ENTERED'', ''TRANSLATED'')) ' ||
514 ' AND posting_request_id = :pv_req_id_slice ' ||
515 ' AND posting_error_code IS NULL ' ||
516 ' AND (load_set_id = :p_load_set_id OR ' ||
517 ' :p_proc_snapshot_rows = ''Y'')';
518
519 -- Print out sql statement for debugging purposes
520 FEM_ENGINES_PKG.Tech_Message
521 (p_severity => pc_log_level_statement,
522 p_module => 'fem.plsql.xgl_eng.vid.3.' || pv_process_slice,
523 p_msg_text => 'v_sql_stmt: ' || v_sql_stmt);
524
525 EXECUTE IMMEDIATE v_sql_stmt
526 USING pv_req_id_slice, p_load_set_id,
527 v_proc_snapshot_rows;
528
529 -- Keep track of how many invalid rows marked
530 x_num_invalid_record := x_num_invalid_record + SQL%ROWCOUNT;
531
532 ---------------------------------------------------------------------------
533 -- 4. Check for missing QTD/YTD values, according to the QTD-YTD parameter
534 -- specification and currency_type_code.
535 -- Also, check for missing _E data if pv_entered_crncy_flag is set to
536 -- Y.
537 ---------------------------------------------------------------------------
538 v_sql_stmt :=
539 ' UPDATE fem_bal_interface_t ' ||
540 ' SET posting_error_code = ''FEM_GL_POST_MISSING_BAL_COL'' ';
541
542 -- Contruct the where clause based on the QTD-YTD parameter specification
543 IF (FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code = 'ALL') THEN
544 v_sql_stmt := v_sql_stmt ||
545 ' WHERE ((currency_type_code = ''ENTERED'' AND ' ||
546 ' (xtd_balance_f IS NULL OR qtd_balance_f IS NULL OR '||
547 ' ytd_balance_f IS NULL)) OR ' ||
548 ' ((currency_type_code = ''TRANSLATED'' OR ' ||
549 ':pv_entered_crncy_flag = ''Y'') AND ' ||
550 ' (xtd_balance_e IS NULL OR qtd_balance_e IS NULL OR '||
551 ' ytd_balance_e IS NULL))) ';
552
553 ELSIF (FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code = 'YTD') THEN
554 v_sql_stmt := v_sql_stmt ||
555 ' WHERE ((currency_type_code = ''ENTERED'' AND ' ||
556 ' (xtd_balance_f IS NULL OR ytd_balance_f IS NULL)) OR '||
557 ' ((currency_type_code = ''TRANSLATED'' OR ' ||
558 ':pv_entered_crncy_flag = ''Y'') AND ' ||
559 ' (xtd_balance_e IS NULL OR ytd_balance_e IS NULL))) ';
560
561 ELSIF (FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code = 'QTD') THEN
562 v_sql_stmt := v_sql_stmt ||
563 ' WHERE ((currency_type_code = ''ENTERED'' AND ' ||
564 ' (xtd_balance_f IS NULL OR qtd_balance_f IS NULL)) OR '||
565 ' ((currency_type_code = ''TRANSLATED'' OR ' ||
566 ':pv_entered_crncy_flag = ''Y'') AND ' ||
567 ' (xtd_balance_e IS NULL OR qtd_balance_e IS NULL))) ';
568
569 ELSIF (FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code = 'PTD') THEN
570 v_sql_stmt := v_sql_stmt ||
571 ' WHERE ((currency_type_code = ''ENTERED''' ||
572 ' AND xtd_balance_f IS NULL) OR ' ||
573 ' ((currency_type_code = ''TRANSLATED'' OR ' ||
574 ':pv_entered_crncy_flag = ''Y'') AND ' ||
575 ' xtd_balance_e IS NULL)) ';
576
577 END IF;
578
579 v_sql_stmt := v_sql_stmt ||
580 ' AND posting_error_code IS NULL ' ||
581 ' AND posting_request_id = :pv_req_id_slice ' ||
582 ' AND (load_set_id = :p_load_set_id OR ' ||
583 ' :p_proc_snapshot_rows = ''Y'') ';
584
585 -- Print out sql statement for debugging purposes
586 FEM_ENGINES_PKG.Tech_Message
587 (p_severity => pc_log_level_statement,
588 p_module => 'fem.plsql.xgl_eng.vid.4.' || pv_process_slice,
589 p_msg_text => 'v_sql_stmt: ' || v_sql_stmt);
590
591 EXECUTE IMMEDIATE v_sql_stmt
592 USING FEM_GL_POST_PROCESS_PKG.pv_entered_crncy_flag,
593 pv_req_id_slice, p_load_set_id,
594 v_proc_snapshot_rows;
595
596 -- Keep track of how many invalid rows marked
597 x_num_invalid_record := x_num_invalid_record + SQL%ROWCOUNT;
598
599 ---------------------------------------------------------------------------
600 -- 5. When validating a subsequent load set, we need to look back at errors
601 -- in matching rows or potentially matching rows from previous load sets
602 -- and mark the FEM_GL_POST_PREV_SET_ERROR into rows in the current load
603 -- set where previous matching or potentially matching error rows are
604 -- found.
605 -- (This is only needed for Incremental and Error Reprocessing loads,
606 -- since for Snapshot loads, everything is processed in a single
607 -- shot)
608 ---------------------------------------------------------------------------
609 IF (FEM_GL_POST_PROCESS_PKG.pv_exec_mode <> 'S') THEN
610 v_sql_stmt :=
611 ' UPDATE fem_bal_interface_t u' ||
612 ' SET posting_error_code = ''FEM_GL_POST_PREV_SET_ERROR'' ' ||
613 ' WHERE ( ' || v_key_stmt2 || ') IN' ||
614 ' ( SELECT ' || v_key_stmt3 ||
615 ' FROM fem_bal_interface_t s' ||
616 ' WHERE s.posting_error_code IS NOT NULL ' ||
617 ' AND s.posting_request_id = :pv_req_id_slice ' ||
618 ' AND s.load_set_id < :p_load_set_id ) ' ||
619 ' AND u.posting_error_code IS NULL ' ||
620 ' AND u.posting_request_id = :pv_req_id_slice ' ||
621 ' AND u.load_set_id = :p_load_set_id ';
622
623 FEM_ENGINES_PKG.TECH_MESSAGE(
624 p_severity => pc_log_level_statement,
625 p_module => 'fem.plsql.xgl_eng.vid.5' || pv_process_slice,
626 p_msg_text => 'v_sql_stmt: ' || v_sql_stmt);
627
628 EXECUTE IMMEDIATE v_sql_stmt
629 USING pv_req_id_slice, p_load_set_id,
630 pv_req_id_slice, p_load_set_id;
631
632 -- Keep track of how many invalid rows marked
633 x_num_invalid_record := x_num_invalid_record + SQL%ROWCOUNT;
634
635 END IF;
636
637 IF x_num_invalid_record > 0 THEN
638
639 FEM_ENGINES_PKG.Tech_Message
640 (p_severity => pc_log_level_error,
641 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
642 p_app_name => 'FEM',
643 p_msg_name => 'FEM_GL_POST_223');
644
645 END IF;
646
647 -- Check if there is any record left to post
648 IF ((p_total_row_num - x_num_invalid_record) > 0) THEN
649 x_records_to_post_flag := TRUE;
650 ELSE
651 x_records_to_post_flag := FALSE;
652 END IF;
653
654 COMMIT;
655
656 x_completion_code := 0;
657
658 FEM_ENGINES_PKG.Tech_Message
659 (p_severity => pc_log_level_procedure,
660 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
661 p_app_name => 'FEM',
662 p_msg_name => 'FEM_GL_POST_202',
663 p_token1 => 'FUNC_NAME',
664 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Validate_Interface_Data',
665 p_token2 => 'TIME',
666 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
667
668 EXCEPTION
669 WHEN FEMXGL_mix_load_method THEN
670
671 COMMIT;
672
673 x_num_invalid_record := p_total_row_num;
674 x_records_to_post_flag := FALSE;
675 x_completion_code := 0;
676
677 FEM_ENGINES_PKG.TECH_MESSAGE
678 (p_severity => pc_log_level_error,
679 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
680 p_msg_text => 'Mixed LOAD_METHOD_CODE values for load set: ' ||
681 TO_CHAR(p_load_set_id) ||
682 '. Skipping processing for this load set.');
683
684 FEM_ENGINES_PKG.Tech_Message
685 (p_severity => pc_log_level_procedure,
686 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
687 p_app_name => 'FEM',
688 p_msg_name => 'FEM_GL_POST_203',
689 p_token1 => 'FUNC_NAME',
690 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Validate_Interface_Data',
691 p_token2 => 'TIME',
692 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
693
694 WHEN OTHERS THEN
695
696 ROLLBACK;
697
698 x_completion_code := 2;
699
700 FEM_ENGINES_PKG.User_Message
701 (p_app_name => 'FEM',
702 p_msg_name => 'FEM_GL_POST_215',
703 p_token1 => 'ERR_MSG',
704 p_value1 => SQLERRM);
705
706 FEM_ENGINES_PKG.Tech_Message
707 (p_severity => pc_log_level_unexpected,
708 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
709 p_app_name => 'FEM',
710 p_msg_name => 'FEM_GL_POST_215',
711 p_token1 => 'ERR_MSG',
712 p_value1 => SQLERRM);
713
714 FEM_ENGINES_PKG.Tech_Message
715 (p_severity => pc_log_level_procedure,
716 p_module => 'fem.plsql.xgl_eng.vid.' || pv_process_slice,
717 p_app_name => 'FEM',
718 p_msg_name => 'FEM_GL_POST_203',
719 p_token1 => 'FUNC_NAME',
720 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Validate_Interface_Data',
721 p_token2 => 'TIME',
722 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
723
724 END Validate_Interface_Data;
725
726
727 -- =======================================================================
728 -- Procedure
729 -- Post_To_Interim
730 -- Purpose
731 -- This routine will purge all records for the given plan type
732 -- in a given time period from FII_BUDGET_BASE.
733 -- History
734 -- 06-20-02 S Kung Created
735 -- 05-05-04 G Hall Bug# 3597527: Implemented changes for
736 -- multiprocessing
737 -- 05-25-04 G Hall Included CURRENCY_CODE in dimensions validation and
738 -- transformation (for free, at least in a coding
739 -- sense -- the metadata for it is already there; only
740 -- had to make CURRENCY_CODE nullable in
741 -- FEM_BAL_POST_INTERIM_GT. Fixed logic in building of
742 -- dynamic SQL for dimensions transformation for when
743 -- the last member of the processing key list is not
744 -- really a dimension and is not included in the
745 -- dimension validation/transformation (e.g.
746 -- CURRENCY_TYPE_CODE).
747 -- Arguments
748 -- p_load_set_id
749 -- x_completion_code
750 -- x_nothing_to_post
751 -- x_prev_err_rows_reproc
752 -- x_cur_data_err_rows
753 -- =======================================================================
754
755 PROCEDURE Post_To_Interim
756 (p_load_set_id IN NUMBER,
757 x_completion_code OUT NOCOPY NUMBER,
758 x_nothing_to_post OUT NOCOPY BOOLEAN,
759 x_prev_err_rows_reproc OUT NOCOPY NUMBER,
760 x_cur_data_err_rows OUT NOCOPY NUMBER) IS
761
762 v_proc_snapshot_rows VARCHAR2(1);
763 v_sql_stmt VARCHAR2(32767);
764 v_upd_cols VARCHAR2(32767);
765 v_sel_stmt VARCHAR2(32767);
766 v_tab_list VARCHAR2(32767);
767 v_join_stmt VARCHAR2(32767);
768 v NUMBER;
769 v_dim_count NUMBER;
770 v_rows_ins_count NUMBER;
771 v_rows_upd_count NUMBER;
772
773 BEGIN
774
775 FEM_ENGINES_PKG.Tech_Message
776 (p_severity => pc_log_level_procedure,
777 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
778 p_app_name => 'FEM',
779 p_msg_name => 'FEM_GL_POST_201',
780 p_token1 => 'FUNC_NAME',
781 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Post_To_Interim',
782 p_token2 => 'TIME',
783 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
784
785 -- Initialize all output counters for this load set
786 x_prev_err_rows_reproc := 0;
787 x_cur_data_err_rows := 0;
788 x_completion_code := 0;
789 x_nothing_to_post := FALSE;
790
791 v_proc_snapshot_rows := NULL;
792 v_sql_stmt := NULL;
793 v_upd_cols := NULL;
794 v_sel_stmt := NULL;
795 v_tab_list := NULL;
796 v_join_stmt := NULL;
797 v_dim_count := 1;
798
799 IF (not pv_snapshot_rows_done) THEN
800 v_proc_snapshot_rows := 'Y';
801 ELSE
802 v_proc_snapshot_rows := 'N';
803 END IF;
804
805 -- Move all records in the current load set into FEM_BAL_POST_INTERIM_GT
806 FEM_ENGINES_PKG.Tech_Message
807 (p_severity => pc_log_level_event,
808 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
809 p_msg_text =>
810 'Moving data from interface table to the interim table...');
811
812 FEM_ENGINES_PKG.Tech_Message
813 (p_severity => pc_log_level_statement,
814 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
815 p_app_name => 'FEM',
816 p_msg_name => 'FEM_GL_POST_204',
817 p_token1 => 'VAR_NAME',
818 p_value1 => 'Start Time',
819 p_token2 => 'VAR_VAL',
820 p_value2 => TO_CHAR(SYSDATE, 'HH24:MI:SS'));
821
822 -- Only interface records with a NULL posting_error_code will be moved
823 -- over because we have cleaned up this code when marking rows for
824 -- processing. So any not null posting error code at this point in time
825 -- comes from data validation.
826 --
827 -- Changes on 02/05/04:
828 -- For Snapshot loads, we will process all load sets together.
829 --
830 -- Bug Fix 3559506
831 -- Instead of using pv_exec_mode in the filter to determine if
832 -- load_set_id can be ignored, we now use variable v_proc_snapshot_rows
833 -- for this.
834
835 INSERT INTO fem_bal_post_interim_gt
836 (interface_rowid,
837 bal_post_type_code,
838 dataset_code,
839 cal_period_id,
840 ledger_id,
841 company_cost_center_org_id,
842 currency_code,
843 currency_type_code,
844 xtd_balance_e,
845 xtd_balance_f,
846 ytd_balance_e,
847 ytd_balance_f,
848 qtd_balance_e,
849 qtd_balance_f,
850 ptd_debit_balance_e,
851 ptd_credit_balance_e,
852 ytd_debit_balance_e,
853 ytd_credit_balance_e,
854 previous_error_flag,
855 posting_error_flag)
856 SELECT
857 rowid,
858 bal_post_type_code,
859 FEM_GL_POST_PROCESS_PKG.pv_dataset_code,
860 FEM_GL_POST_PROCESS_PKG.pv_cal_period_id,
861 FEM_GL_POST_PROCESS_PKG.pv_ledger_id,
862 -1,
863 currency_code,
864 currency_type_code,
865 xtd_balance_e,
866 xtd_balance_f,
867 DECODE(FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code,
868 'PTD', NULL, 'QTD', NULL, ytd_balance_e),
869 DECODE(FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code,
870 'PTD', NULL, 'QTD', NULL, ytd_balance_f),
871 DECODE(FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code,
872 'PTD', NULL, 'YTD', NULL, qtd_balance_e),
873 DECODE(FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code,
874 'PTD', NULL, 'YTD', NULL, qtd_balance_e),
875 ptd_debit_balance_e,
876 ptd_credit_balance_e,
877 ytd_debit_balance_e,
878 ytd_credit_balance_e,
879 previous_error_flag,
880 'N'
881 FROM fem_bal_interface_t
882 WHERE (v_proc_snapshot_rows = 'Y' OR load_set_id = p_load_set_id)
883 AND posting_request_id = pv_req_id_slice
884 AND posting_error_code is NULL;
885
886 v_rows_ins_count := SQL%ROWCOUNT;
887
888 COMMIT;
889
890 FEM_ENGINES_PKG.Tech_Message
891 (p_severity => pc_log_level_statement,
892 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
893 p_app_name => 'FEM',
894 p_msg_name => 'FEM_GL_POST_204',
895 p_token1 => 'VAR_NAME',
896 p_value1 => 'End Time',
897 p_token2 => 'VAR_VAL',
898 p_value2 => TO_CHAR(SYSDATE, 'HH24:MI:SS'));
899
900 FEM_ENGINES_PKG.Tech_Message
901 (p_severity => pc_log_level_statement,
902 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
903 p_app_name => 'FEM',
904 p_msg_name => 'FEM_GL_POST_216',
905 p_token1 => 'NUM',
906 p_value1 => TO_CHAR(v_rows_ins_count),
907 p_token2 => 'TABLE',
908 p_value2 => 'FEM_BAL_POST_INTERIM_GT');
909
910 -- Start building SQL statement to look up numeric IDs for the
911 -- dimension members. We will process 5 dimensions at a time
912 -- due to potential performance implications when joining to
913 -- too many tables at a time
914
915 FEM_ENGINES_PKG.Tech_Message
916 (p_severity => pc_log_level_event,
917 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
918 p_msg_text =>
919 'Looking up member numeric IDs for 5 dimensions at a time...');
920
921 FOR v IN 1..FEM_GL_POST_PROCESS_PKG.pv_proc_key_dim_num LOOP
922
923 IF (v_dim_count = 1) THEN
924
925 -- This is the start of a new statement after one execution,
926 -- so re-initialize all buffers
927 v_sql_stmt := 'UPDATE fem_bal_post_interim_gt g SET ';
928 v_upd_cols := '(';
929 v_sel_stmt := '(SELECT ';
930 v_tab_list := 'FROM fem_bal_interface_t i, ';
931 v_join_stmt := 'WHERE i.rowid = g.interface_rowid ';
932
933 END IF;
934
935 IF (FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_col_name IN
936 ('COMPANY_COST_CENTER_ORG_ID', 'FINANCIAL_ELEM_ID', 'CURRENCY_CODE',
937 'PRODUCT_ID', 'NATURAL_ACCOUNT_ID', 'CHANNEL_ID', 'LINE_ITEM_ID',
938 'PROJECT_ID', 'CUSTOMER_ID', 'ENTITY_ID', 'INTERCOMPANY_ID',
939 'TASK_ID', 'USER_DIM1_ID', 'USER_DIM2_ID', 'USER_DIM3_ID',
940 'USER_DIM4_ID', 'USER_DIM5_ID', 'USER_DIM6_ID', 'USER_DIM7_ID',
941 'USER_DIM8_ID', 'USER_DIM9_ID', 'USER_DIM10_ID',
942 'SOURCE_SYSTEM_CODE')) THEN
943
944 FEM_ENGINES_PKG.Tech_Message
945 (p_severity => pc_log_level_statement,
946 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
947 p_app_name => 'FEM',
948 p_msg_name => 'FEM_GL_POST_204',
949 p_token1 => 'VAR_NAME',
950 p_value1 => 'v',
951 p_token2 => 'VAR_VAL',
952 p_value2 => TO_CHAR(v));
953
954 FEM_ENGINES_PKG.Tech_Message
955 (p_severity => pc_log_level_statement,
956 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
957 p_app_name => 'FEM',
958 p_msg_name => 'FEM_GL_POST_204',
959 p_token1 => 'VAR_NAME',
960 p_value1 => 'v_dim_count',
961 p_token2 => 'VAR_VAL',
962 p_value2 => TO_CHAr(v_dim_count));
963
964 FEM_ENGINES_PKG.Tech_Message
965 (p_severity => pc_log_level_statement,
966 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
967 p_app_name => 'FEM',
968 p_msg_name => 'FEM_GL_POST_204',
969 p_token1 => 'VAR_NAME',
970 p_value1 => 'dim column name',
971 p_token2 => 'VAR_VAL',
972 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_col_name);
973
974 -- Build the list of columns for updating
975 v_upd_cols := v_upd_cols || 'g.' ||
976 FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_col_name || ', ';
977
978 -- Build SELECT column lists
979 v_sel_stmt := v_sel_stmt ||
980 'dm' || TO_CHAR(v_dim_count) || '.' ||
981 FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_member_col || ', ';
982
983 -- Build table list
984 v_tab_list := v_tab_list ||
985 FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_member_b_table_name ||
986 ' dm' || v_dim_count || ', ';
987
988 -- Build related join conditions
989 v_join_stmt :=
990 v_join_stmt ||
991 'AND dm' || v_dim_count || '.' ||
992 FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_member_disp_code_col ||
993 ' = i.' ||
994 FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_int_disp_code_col ||
995 ' AND dm' || v_dim_count || '.' || 'personal_flag = ''N'' ' ||
996 ' AND dm' || v_dim_count || '.' || 'enabled_flag = ''Y'' ';
997
998 IF (FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_vsr_flag = 'Y') THEN
999 v_join_stmt := v_join_stmt ||
1000 'AND dm' || v_dim_count || '.value_set_id = ' ||
1001 TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_vs_id) || ' ';
1002
1003 END IF;
1004
1005 v_dim_count := v_dim_count + 1;
1006
1007 END IF;
1008
1009 -- If 5 dimensions have been built into the statement
1010 -- (i.e. v_dim_count = 6), put the parts together
1011 -- and execute the UPDATE
1012
1013 IF (v_dim_count = 6 or v = FEM_GL_POST_PROCESS_PKG.pv_proc_key_dim_num) THEN
1014
1015 v_sql_stmt := v_sql_stmt ||
1016 RTRIM(v_upd_cols, ', ') || ') = ' ||
1017 RTRIM(v_sel_stmt, ', ') || ' ' ||
1018 RTRIM(v_tab_list, ', ') || ' ' ||
1019 v_join_stmt || ')';
1020
1021 FEM_ENGINES_PKG.Tech_Message
1022 (p_severity => pc_log_level_statement,
1023 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1024 p_msg_text => 'v_sql_stmt: ' || v_sql_stmt);
1025
1026 FEM_ENGINES_PKG.Tech_Message
1027 (p_severity => pc_log_level_statement,
1028 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1029 p_app_name => 'FEM',
1030 p_msg_name => 'FEM_GL_POST_204',
1031 p_token1 => 'VAR_NAME',
1032 p_value1 => 'Start Time',
1033 p_token2 => 'VAR_VAL',
1034 p_value2 => TO_CHAR(SYSDATE, 'HH24:MI:SS'));
1035
1036 EXECUTE IMMEDIATE v_sql_stmt;
1037
1038 v_rows_upd_count := SQL%ROWCOUNT;
1039
1040 FEM_ENGINES_PKG.Tech_Message
1041 (p_severity => pc_log_level_statement,
1042 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1043 p_app_name => 'FEM',
1044 p_msg_name => 'FEM_GL_POST_204',
1045 p_token1 => 'VAR_NAME',
1046 p_value1 => 'End Time',
1047 p_token2 => 'VAR_VAL',
1048 p_value2 => TO_CHAR(SYSDATE, 'HH24:MI:SS'));
1049
1050 FEM_ENGINES_PKG.Tech_Message
1051 (p_severity => pc_log_level_statement,
1052 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1053 p_app_name => 'FEM',
1054 p_msg_name => 'FEM_GL_POST_217',
1055 p_token1 => 'NUM',
1056 p_value1 => TO_CHAR(v_rows_upd_count),
1057 p_token2 => 'TABLE',
1058 p_value2 => 'FEM_BAL_POST_INTERIM_GT');
1059
1060 v_dim_count := 1;
1061
1062 COMMIT;
1063
1064 END IF;
1065
1066 END LOOP;
1067
1068 -- Check all records in FEM_BAL_POST_INTERIM_GT and see if any
1069 -- records have missing dimension member ID.
1070 -- If so, mark them with error code.
1071
1072 FEM_ENGINES_PKG.Tech_Message
1073 (p_severity => pc_log_level_statement,
1074 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1075 p_msg_text => 'Checking for missing dimension member ID...');
1076
1077 v_sql_stmt := 'UPDATE fem_bal_post_interim_gt g ' ||
1078 'SET posting_error_flag = ''Y'' ' || 'WHERE ';
1079
1080 v_dim_count := 1;
1081
1082 FOR v IN 1..FEM_GL_POST_PROCESS_PKG.pv_proc_key_dim_num LOOP
1083
1084 IF (FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_col_name IN
1085 ('COMPANY_COST_CENTER_ORG_ID', 'FINANCIAL_ELEM_ID', 'CURRENCY_CODE',
1086 'PRODUCT_ID', 'NATURAL_ACCOUNT_ID', 'CHANNEL_ID', 'LINE_ITEM_ID',
1087 'PROJECT_ID', 'CUSTOMER_ID', 'ENTITY_ID', 'INTERCOMPANY_ID',
1088 'TASK_ID', 'USER_DIM1_ID', 'USER_DIM2_ID', 'USER_DIM3_ID',
1089 'USER_DIM4_ID', 'USER_DIM5_ID', 'USER_DIM6_ID', 'USER_DIM7_ID',
1090 'USER_DIM8_ID', 'USER_DIM9_ID', 'USER_DIM10_ID',
1091 'SOURCE_SYSTEM_CODE')) THEN
1092
1093 IF (v_dim_count = 1) THEN
1094
1095 v_sql_stmt := v_sql_stmt ||
1096 FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_col_name || ' IS NULL ';
1097 v_dim_count := 0;
1098
1099 ELSE
1100
1101 v_sql_stmt := v_sql_stmt || 'OR ' ||
1102 FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_col_name || ' IS NULL ';
1103
1104 END IF;
1105
1106 END IF;
1107
1108 END LOOP;
1109
1110 FEM_ENGINES_PKG.Tech_Message
1111 (p_severity => pc_log_level_statement,
1112 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1113 p_msg_text => 'v_sql_stmt: ' || v_sql_stmt);
1114
1115 BEGIN
1116
1117 FEM_ENGINES_PKG.Tech_Message
1118 (p_severity => pc_log_level_statement,
1119 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1120 p_app_name => 'FEM',
1121 p_msg_name => 'FEM_GL_POST_204',
1122 p_token1 => 'VAR_NAME',
1123 p_value1 => 'Start Time',
1124 p_token2 => 'VAR_VAL',
1125 p_value2 => TO_CHAR(SYSDATE, 'HH24:MI:SS'));
1126
1127 EXECUTE IMMEDIATE v_sql_stmt;
1128
1129 x_cur_data_err_rows := SQL%ROWCOUNT;
1130
1131 FEM_ENGINES_PKG.Tech_Message
1132 (p_severity => pc_log_level_statement,
1133 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1134 p_app_name => 'FEM',
1135 p_msg_name => 'FEM_GL_POST_204',
1136 p_token1 => 'VAR_NAME',
1137 p_value1 => 'End Time',
1138 p_token2 => 'VAR_VAL',
1139 p_value2 => TO_CHAR(SYSDATE, 'HH24:MI:SS'));
1140
1141 FEM_ENGINES_PKG.Tech_Message
1142 (p_severity => pc_log_level_statement,
1143 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1144 p_app_name => 'FEM',
1145 p_msg_name => 'FEM_GL_POST_217',
1146 p_token1 => 'NUM',
1147 p_value1 => TO_CHAR(x_cur_data_err_rows),
1148 p_token2 => 'TABLE',
1149 p_value2 => 'FEM_BAL_POST_INTERIM_GT');
1150
1151 COMMIT;
1152
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN
1155 NULL;
1156 END;
1157
1158 IF (x_cur_data_err_rows > 0) THEN
1159
1160 IF pv_snapshot_rows_done THEN
1161
1162 FEM_ENGINES_PKG.Tech_Message
1163 (p_severity => pc_log_level_error,
1164 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1165 p_app_name => 'FEM',
1166 p_msg_name => 'FEM_GL_POST_223');
1167
1168 ELSE
1169
1170 FEM_ENGINES_PKG.Tech_Message
1171 (p_severity => pc_log_level_event,
1172 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1173 p_app_name => 'FEM',
1174 p_msg_text => 'Invalid records are found in the snapshot pass');
1175
1176 END IF;
1177
1178 -- Update corresponding error code column in FEM_BAL_INTERFACE_T
1179 -- based on FEM_BAL_POST_INTERIM_GT
1180
1181 FEM_ENGINES_PKG.Tech_Message
1182 (p_severity => pc_log_level_event,
1183 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1184 p_msg_text => 'Updating interface table with error codes...');
1185
1186 UPDATE fem_bal_interface_t
1187 SET posting_error_code = 'FEM_GL_POST_INVALID_DIM_MEMBER'
1188 WHERE rowid IN
1189 (SELECT interface_rowid
1190 FROM fem_bal_post_interim_gt
1191 WHERE posting_error_flag = 'Y');
1192
1193 FEM_ENGINES_PKG.Tech_Message
1194 (p_severity => pc_log_level_statement,
1195 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1196 p_app_name => 'FEM',
1197 p_msg_name => 'FEM_GL_POST_217',
1198 p_token1 => 'NUM',
1199 p_value1 => TO_CHAR(SQL%ROWCOUNT),
1200 p_token2 => 'TABLE',
1201 p_value2 => 'FEM_BAL_INTERFACE_T');
1202
1203 COMMIT;
1204
1205 END IF;
1206
1207 IF (x_cur_data_err_rows = v_rows_ins_count) THEN
1208 -- If number of records with invalid data is equal to
1209 -- the number of rows inserted into the interim table originally,
1210 -- set x_nothing_to_post to TRUE
1211 x_nothing_to_post := TRUE;
1212 END IF;
1213
1214 -- Count number of previous error processed successfully
1215 FEM_ENGINES_PKG.Tech_Message
1216 (p_severity => pc_log_level_statement,
1217 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1218 p_msg_text => 'Counting number of previous error rows reprocessed...');
1219
1220 BEGIN
1221 SELECT count(*)
1222 INTO x_prev_err_rows_reproc
1223 FROM fem_bal_post_interim_gt
1224 WHERE posting_error_flag = 'N'
1225 AND previous_error_flag = 'Y';
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND THEN
1228 x_prev_err_rows_reproc := 0;
1229 END;
1230
1231 FEM_ENGINES_PKG.Tech_Message
1232 (p_severity => pc_log_level_procedure,
1233 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1234 p_app_name => 'FEM',
1235 p_msg_name => 'FEM_GL_POST_202',
1236 p_token1 => 'FUNC_NAME',
1237 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Post_To_Interim',
1238 p_token2 => 'TIME',
1239 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243
1244 ROLLBACK;
1245
1246 x_completion_code := 2;
1247
1248 FEM_ENGINES_PKG.User_Message
1249 (p_app_name => 'FEM',
1250 p_msg_name => 'FEM_GL_POST_215',
1251 p_token1 => 'ERR_MSG',
1252 p_value1 => SQLERRM);
1253
1254 FEM_ENGINES_PKG.Tech_Message
1255 (p_severity => pc_log_level_unexpected,
1256 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1257 p_app_name => 'FEM',
1258 p_msg_name => 'FEM_GL_POST_215',
1259 p_token1 => 'ERR_MSG',
1260 p_value1 => SQLERRM);
1261
1262 FEM_ENGINES_PKG.Tech_Message
1263 (p_severity => pc_log_level_procedure,
1264 p_module => 'fem.plsql.xgl_eng.pti.' || pv_process_slice,
1265 p_app_name => 'FEM',
1266 p_msg_name => 'FEM_GL_POST_203',
1267 p_token1 => 'FUNC_NAME',
1268 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Post_To_Interim',
1269 p_token2 => 'TIME',
1270 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1271
1272 END Post_To_Interim;
1273
1274
1275 -- ======================================================================
1276 -- Procedure
1277 -- Post_Cycle_Handler
1278 -- Purpose
1279 -- This is the wrapper routine for a post cycle. It calls the
1280 -- following routines in order:
1281 -- 1) Mark_Rows_For_Process
1282 -- 2) Validate_Interface_Data
1283 -- 3) Post_To_Interim
1284 -- 4) FEM_GL_POST_BAL_PKG.Post_Fem_Balances
1285 -- History
1286 -- 02-19-04 S Kung Created
1287 -- 05-06-04 G Hall Bug# 3597527: Implemented changes for
1288 -- multiprocessing
1289 -- Arguments
1290 -- p_load_set_id The Load Set ID being processed
1291 -- x_completion_code Completion status of the routine
1292 -- x_rows_marked Number of rows marked for processing
1293 -- x_posted_row_num Number of records posted
1294 -- x_prev_err_rows_reproc Number of previous error records sucessfully
1295 -- reprocessed
1296 -- x_cur_data_err_rows Nunber of current error rows found,
1297 -- including previous error rows that are still
1298 -- in error.
1299 -- ========================================================================
1300
1301 PROCEDURE Post_Cycle_Handler
1302 (p_load_set_id IN NUMBER,
1303 x_completion_code OUT NOCOPY NUMBER,
1304 x_rows_marked OUT NOCOPY NUMBER,
1305 x_posted_row_num OUT NOCOPY NUMBER,
1306 x_prev_err_rows_reproc OUT NOCOPY NUMBER,
1307 x_cur_data_err_rows OUT NOCOPY NUMBER) IS
1308
1309 FEMXGL_fatal_err EXCEPTION;
1310 FEMXGL_skip_the_rest EXCEPTION;
1311 v_compl_code NUMBER;
1312 v_posted_rows NUMBER;
1313 v_prev_err_rows_reproc NUMBER;
1314 v_err_count NUMBER;
1315 v_curr_set_row_count NUMBER;
1316 v_any_valid_data_to_post BOOLEAN;
1317 v_nothing_to_post BOOLEAN;
1318
1319 BEGIN
1320
1321 FEM_ENGINES_PKG.Tech_Message
1322 (p_severity => pc_log_level_procedure,
1323 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1324 p_app_name => 'FEM',
1325 p_msg_name => 'FEM_GL_POST_201',
1326 p_token1 => 'FUNC_NAME',
1327 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Post_Cycle_Handler',
1328 p_token2 => 'TIME',
1329 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1330
1331 x_completion_code := 0;
1332 x_posted_row_num := 0;
1333 x_prev_err_rows_reproc := 0;
1334 x_cur_data_err_rows := 0;
1335
1336 v_posted_rows := 0;
1337 v_prev_err_rows_reproc := 0;
1338 v_curr_set_row_count := 0;
1339 v_any_valid_data_to_post := FALSE;
1340 v_nothing_to_post := FALSE;
1341
1342 -- --------------------------------------------------------------
1343 -- *** Mark rows for processing by populating the request ID ***
1344 -- --------------------------------------------------------------
1345
1346 FEM_ENGINES_PKG.Tech_Message
1347 (p_severity => pc_log_level_event,
1348 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1349 p_app_name => 'FEM',
1350 p_msg_name => 'FEM_GL_POST_222');
1351
1352 Mark_Rows_For_Process
1353 (p_load_set_id => p_load_set_id,
1354 x_row_count_in_set => v_curr_set_row_count,
1355 x_completion_code => v_compl_code);
1356
1357 x_rows_marked := v_curr_set_row_count;
1358
1359 IF v_compl_code = 2 THEN
1360 RAISE FEMXGL_fatal_err;
1361 ELSIF v_curr_set_row_count = 0 THEN
1362 RAISE FEMXGL_skip_the_rest;
1363 END IF;
1364
1365 -- -------------------------------------------
1366 -- *** Validate Interface Data ***
1367 -- -------------------------------------------
1368
1369 FEM_ENGINES_PKG.Tech_Message
1370 (p_severity => pc_log_level_event,
1371 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1372 p_app_name => 'FEM',
1373 p_msg_name => 'FEM_GL_POST_211');
1374
1375 v_err_count := 0;
1376
1377 Validate_Interface_Data
1378 (p_load_set_id => p_load_set_id,
1379 p_total_row_num => v_curr_set_row_count,
1380 x_records_to_post_flag => v_any_valid_data_to_post,
1381 x_num_invalid_record => v_err_count,
1382 x_completion_code => v_compl_code);
1383
1384 x_cur_data_err_rows := v_err_count;
1385
1386 FEM_ENGINES_PKG.Tech_Message
1387 (p_severity => pc_log_level_statement,
1388 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1389 p_app_name => 'FEM',
1390 p_msg_name => 'FEM_GL_POST_204',
1391 p_token1 => 'VAR_NAME',
1392 p_value1 => 'v_err_count',
1393 p_token2 => 'VAR_VAL',
1394 p_value2 => TO_CHAR(v_err_count));
1395
1396 IF v_compl_code = 2 THEN
1397 RAISE FEMXGL_fatal_err;
1398 ELSIF NOT v_any_valid_data_to_post THEN
1399 RAISE FEMXGL_skip_the_rest;
1400 END IF;
1401
1402 -- ------------------------------
1403 -- *** Post to Interim table ***
1404 -- ------------------------------
1405
1406 FEM_ENGINES_PKG.Tech_Message
1407 (p_severity => pc_log_level_event,
1408 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1409 p_app_name => 'FEM',
1410 p_msg_name => 'FEM_GL_POST_212');
1411
1412 v_err_count := 0;
1413
1414 Post_To_Interim
1415 (p_load_set_id => p_load_set_id,
1416 x_completion_code => v_compl_code,
1417 x_nothing_to_post => v_nothing_to_post,
1418 x_prev_err_rows_reproc => v_prev_err_rows_reproc,
1419 x_cur_data_err_rows => v_err_count);
1420
1421 x_prev_err_rows_reproc := v_prev_err_rows_reproc;
1422 x_cur_data_err_rows := x_cur_data_err_rows + v_err_count;
1423
1424 FEM_ENGINES_PKG.Tech_Message
1425 (p_severity => pc_log_level_statement,
1426 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1427 p_app_name => 'FEM',
1428 p_msg_name => 'FEM_GL_POST_204',
1429 p_token1 => 'VAR_NAME',
1430 p_value1 => 'v_prev_err_rows_reproc',
1431 p_token2 => 'VAR_VAL',
1432 p_value2 => TO_CHAR(v_prev_err_rows_reproc));
1433
1434 FEM_ENGINES_PKG.Tech_Message
1435 (p_severity => pc_log_level_statement,
1436 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1437 p_app_name => 'FEM',
1438 p_msg_name => 'FEM_GL_POST_204',
1439 p_token1 => 'VAR_NAME',
1440 p_value1 => 'v_err_count',
1441 p_token2 => 'VAR_VAL',
1442 p_value2 => TO_CHAR(v_err_count));
1443
1444 IF v_compl_code = 2 THEN
1445 RAISE FEMXGL_fatal_err;
1446 ELSIF v_nothing_to_post THEN
1447 RAISE FEMXGL_skip_the_rest;
1448 END IF;
1449
1450 -- -----------------------------
1451 -- *** Post to FEM_BALANCES ***
1452 -- -----------------------------
1453
1454 FEM_ENGINES_PKG.Tech_Message
1455 (p_severity => pc_log_level_event,
1456 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1457 p_app_name => 'FEM',
1458 p_msg_name => 'FEM_GL_POST_214');
1459
1460 FEM_GL_POST_BAL_PKG.Post_Fem_Balances
1461 (p_execution_mode => FEM_GL_POST_PROCESS_PKG.pv_exec_mode,
1462 p_process_slice => pv_process_slice,
1463 x_rows_posted => v_posted_rows,
1464 x_completion_code => v_compl_code);
1465
1466 IF v_compl_code = 2 THEN
1467 RAISE FEMXGL_fatal_err;
1468 END IF;
1469
1470 x_posted_row_num := v_posted_rows;
1471
1472 FEM_ENGINES_PKG.Tech_Message
1473 (p_severity => pc_log_level_statement,
1474 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1475 p_app_name => 'FEM',
1476 p_msg_name => 'FEM_GL_POST_204',
1477 p_token1 => 'VAR_NAME',
1478 p_value1 => 'x_posted_row_num',
1479 p_token2 => 'VAR_VAL',
1480 p_value2 => TO_CHAR(x_posted_row_num));
1481
1482 FEM_ENGINES_PKG.Tech_Message
1483 (p_severity => pc_log_level_procedure,
1484 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1485 p_app_name => 'FEM',
1486 p_msg_name => 'FEM_GL_POST_202',
1487 p_token1 => 'FUNC_NAME',
1488 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Post_Cycle_Handler',
1489 p_token2 => 'TIME',
1490 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1491
1492 EXCEPTION
1493 WHEN FEMXGL_skip_the_rest THEN
1494
1495 x_completion_code := 0;
1496
1497 IF pv_snapshot_rows_done THEN
1498
1499 FEM_ENGINES_PKG.Tech_Message
1500 (p_severity => pc_log_level_event,
1501 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1502 p_app_name => 'FEM',
1503 p_msg_name => 'FEM_GL_POST_219',
1504 p_token1 => 'LOAD_SET_ID',
1505 p_value1 => TO_CHAR(p_load_set_id));
1506
1507 ELSE
1508
1509 FEM_ENGINES_PKG.Tech_Message
1510 (p_severity => pc_log_level_event,
1511 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1512 p_app_name => 'FEM',
1513 p_msg_text => 'Posting process found nothing to post for ' ||
1514 'the snapshot pass');
1515
1516 END IF;
1517
1518 FEM_ENGINES_PKG.Tech_Message
1519 (p_severity => pc_log_level_procedure,
1520 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1521 p_app_name => 'FEM',
1522 p_msg_name => 'FEM_GL_POST_202',
1523 p_token1 => 'FUNC_NAME',
1524 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Post_Cycle_Handler',
1525 p_token2 => 'TIME',
1526 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1527
1528 WHEN FEMXGL_fatal_err THEN
1529
1530 ROLLBACK;
1531
1532 x_completion_code := 2;
1533
1534 FEM_ENGINES_PKG.Tech_Message
1535 (p_severity => pc_log_level_procedure,
1536 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1537 p_app_name => 'FEM',
1538 p_msg_name => 'FEM_GL_POST_203',
1539 p_token1 => 'FUNC_NAME',
1540 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Post_Cycle_Handler',
1541 p_token2 => 'TIME',
1542 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1543
1544 WHEN OTHERS THEN
1545
1546 ROLLBACK;
1547
1548 x_completion_code := 2;
1549
1550 FEM_ENGINES_PKG.User_Message
1551 (p_app_name => 'FEM',
1552 p_msg_name => 'FEM_GL_POST_215',
1553 p_token1 => 'ERR_MSG',
1554 p_value1 => SQLERRM);
1555
1556 FEM_ENGINES_PKG.Tech_Message
1557 (p_severity => pc_log_level_unexpected,
1558 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1559 p_app_name => 'FEM',
1560 p_msg_name => 'FEM_GL_POST_215',
1561 p_token1 => 'ERR_MSG',
1562 p_value1 => SQLERRM);
1563
1564 FEM_ENGINES_PKG.Tech_Message
1565 (p_severity => pc_log_level_procedure,
1566 p_module => 'fem.plsql.xgl_eng.pch.' || pv_process_slice,
1567 p_app_name => 'FEM',
1568 p_msg_name => 'FEM_GL_POST_203',
1569 p_token1 => 'FUNC_NAME',
1570 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.post_cycle_handler',
1571 p_token2 => 'TIME',
1572 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1573
1574 END Post_Cycle_Handler;
1575
1576 -- =======================================================================
1577
1578
1579 -- ======================================================================
1580 -- Procedure
1581 -- Log_Data_Error_Help_Msgs
1582 -- Purpose
1583 -- Log help messages to the concurrent request log file when terminating
1584 -- with any data validation errors.
1585 -- History
1586 -- 07-11-05 G Hall Bug# 3574347: Created
1587 -- ========================================================================
1588
1589 PROCEDURE Log_Data_Error_Help_Msgs IS
1590
1591 CURSOR c1 IS
1592 SELECT cm.interface_column_name, d.dimension_name
1593 FROM fem_tab_columns_b tc,
1594 fem_xdim_dimensions xd,
1595 fem_dimensions_vl d,
1596 fem_int_column_map cm
1597 WHERE tc.table_name = 'FEM_BALANCES'
1598 AND tc.column_name NOT IN ('CAL_PERIOD_ID', 'DATASET_CODE', 'LEDGER_ID')
1599 AND xd.dimension_id = tc.dimension_id
1600 AND xd.value_set_required_flag = 'N'
1601 AND d.dimension_id = tc.dimension_id
1602 AND cm.object_type_code = 'XGL_INTEGRATION'
1603 AND cm.target_column_name = tc.column_name
1604 ORDER BY 1;
1605
1606 CURSOR c2 IS
1607 SELECT cm.interface_column_name, d.dimension_name, vs.value_set_name
1608 FROM fem_tab_columns_b tc,
1609 fem_xdim_dimensions xd,
1610 fem_dimensions_vl d,
1611 fem_int_column_map cm,
1612 fem_global_vs_combo_defs gvscd,
1613 fem_value_sets_vl vs
1614 WHERE tc.table_name = 'FEM_BALANCES'
1615 AND tc.column_name NOT IN ('CAL_PERIOD_ID', 'DATASET_CODE', 'LEDGER_ID')
1616 AND xd.dimension_id = tc.dimension_id
1617 AND xd.value_set_required_flag = 'Y'
1618 AND d.dimension_id = tc.dimension_id
1619 AND cm.object_type_code = 'XGL_INTEGRATION'
1620 AND cm.target_column_name = tc.column_name
1621 AND gvscd.global_vs_combo_id = FEM_GL_POST_PROCESS_PKG.pv_global_vs_combo_id
1622 AND gvscd.dimension_id = tc.dimension_id
1623 AND vs.dimension_id = gvscd.dimension_id
1624 AND vs.value_set_id = gvscd.value_set_id
1625 ORDER BY 1;
1626
1627 BEGIN
1628
1629 -- Log explanation about checking the POSTING_ERROR_CODE column, and
1630 -- info about the FEM_GL_POST_INVALID_DIM_MEMBER error code and the
1631 -- dimension display code columns in the interface table to which it may
1632 -- apply.
1633
1634 FEM_ENGINES_PKG.User_Message
1635 (p_app_name => 'FEM',
1636 p_msg_name => 'FEM_GL_POST_229');
1637
1638 -- List each of the non-VSR dimension display code interface columns to
1639 -- which the FEM_GL_POST_INVALID_DIM_MEMBER error code may apply, and
1640 -- the Dimension Name against which it is validated.
1641
1642 FOR non_vsr_dim IN c1 LOOP
1643
1644 FEM_ENGINES_PKG.User_Message
1645 (p_app_name => 'FEM',
1646 p_msg_name => 'FEM_GL_POST_230',
1647 p_token1 => 'DIM_DC_COL',
1648 p_value1 => non_vsr_dim.interface_column_name,
1649 p_token2 => 'DIM_NAME',
1650 p_value2 => non_vsr_dim.dimension_name);
1651
1652 END LOOP;
1653
1654 -- List each of the VSR dimension display code interface columns to
1655 -- which the FEM_GL_POST_INVALID_DIM_MEMBER error code may apply, and
1656 -- the Dimension Name and Value Set against which it is validated.
1657
1658 FOR vsr_dim IN c2 LOOP
1659
1660 FEM_ENGINES_PKG.User_Message
1661 (p_app_name => 'FEM',
1662 p_msg_name => 'FEM_GL_POST_231',
1663 p_token1 => 'DIM_DC_COL',
1664 p_value1 => vsr_dim.interface_column_name,
1665 p_token2 => 'DIM_NAME',
1666 p_value2 => vsr_dim.dimension_name,
1667 p_token3 => 'VALUE_SET_NAME',
1668 p_value3 => vsr_dim.value_set_name);
1669
1670 END LOOP;
1671
1672 -- Other data validation error codes that may be reported in
1673 -- POSTING_ERROR_CODE are listed below with their descriptions,
1674 -- to aid in correcting the data errors in the interface table:
1675
1676 FEM_ENGINES_PKG.User_Message
1677 (p_app_name => 'FEM',
1678 p_msg_name => 'FEM_GL_POST_232');
1679
1680 -- List descriptions for the following data validation error codes:
1681 -- FEM_GL_POST_DUP_PROC_KEYS
1682 -- FEM_GL_POST_INVALID_CURR_TYPE
1683 -- FEM_GL_POST_INVALID_POST_TYPE
1684 -- FEM_GL_POST_MISSING_BAL_COL
1685 -- FEM_GL_POST_MIX_LOAD_METHOD
1686 -- FEM_GL_POST_PREV_SET_ERROR
1687 -- FEM_GL_POST_TO_BE_REPROCESSED
1688
1689 FEM_ENGINES_PKG.User_Message
1690 (p_app_name => 'FEM',
1691 p_msg_name => 'FEM_GL_POST_233',
1692 p_token1 => 'DATA_ERROR_MSG_NAME',
1693 p_value1 => 'FEM_GL_POST_DUP_PROC_KEYS',
1694 p_trans1 => 'N',
1695 p_token2 => 'DATA_ERROR_MSG_TEXT',
1696 p_value2 => 'FEM_GL_POST_DUP_PROC_KEYS',
1697 p_trans2 => 'Y');
1698
1699 FEM_ENGINES_PKG.User_Message
1700 (p_app_name => 'FEM',
1701 p_msg_name => 'FEM_GL_POST_233',
1702 p_token1 => 'DATA_ERROR_MSG_NAME',
1703 p_value1 => 'FEM_GL_POST_INVALID_CURR_TYPE',
1704 p_trans1 => 'N',
1705 p_token2 => 'DATA_ERROR_MSG_TEXT',
1706 p_value2 => 'FEM_GL_POST_INVALID_CURR_TYPE',
1707 p_trans2 => 'Y');
1708
1709 FEM_ENGINES_PKG.User_Message
1710 (p_app_name => 'FEM',
1711 p_msg_name => 'FEM_GL_POST_233',
1712 p_token1 => 'DATA_ERROR_MSG_NAME',
1713 p_value1 => 'FEM_GL_POST_INVALID_POST_TYPE',
1714 p_trans1 => 'N',
1715 p_token2 => 'DATA_ERROR_MSG_TEXT',
1716 p_value2 => 'FEM_GL_POST_INVALID_POST_TYPE',
1717 p_trans2 => 'Y');
1718
1719 FEM_ENGINES_PKG.User_Message
1720 (p_app_name => 'FEM',
1721 p_msg_name => 'FEM_GL_POST_233',
1722 p_token1 => 'DATA_ERROR_MSG_NAME',
1723 p_value1 => 'FEM_GL_POST_MISSING_BAL_COL',
1724 p_trans1 => 'N',
1725 p_token2 => 'DATA_ERROR_MSG_TEXT',
1726 p_value2 => 'FEM_GL_POST_MISSING_BAL_COL',
1727 p_trans2 => 'Y');
1728
1729 FEM_ENGINES_PKG.User_Message
1730 (p_app_name => 'FEM',
1731 p_msg_name => 'FEM_GL_POST_233',
1732 p_token1 => 'DATA_ERROR_MSG_NAME',
1733 p_value1 => 'FEM_GL_POST_MIX_LOAD_METHOD',
1734 p_trans1 => 'N',
1735 p_token2 => 'DATA_ERROR_MSG_TEXT',
1736 p_value2 => 'FEM_GL_POST_MIX_LOAD_METHOD',
1737 p_trans2 => 'Y');
1738
1739 FEM_ENGINES_PKG.User_Message
1740 (p_app_name => 'FEM',
1741 p_msg_name => 'FEM_GL_POST_233',
1742 p_token1 => 'DATA_ERROR_MSG_NAME',
1743 p_value1 => 'FEM_GL_POST_PREV_SET_ERROR',
1744 p_trans1 => 'N',
1745 p_token2 => 'DATA_ERROR_MSG_TEXT',
1746 p_value2 => 'FEM_GL_POST_PREV_SET_ERROR',
1747 p_trans2 => 'Y');
1748
1749 FEM_ENGINES_PKG.User_Message
1750 (p_app_name => 'FEM',
1751 p_msg_name => 'FEM_GL_POST_233',
1752 p_token1 => 'DATA_ERROR_MSG_NAME',
1753 p_value1 => 'FEM_GL_POST_TO_BE_REPROCESSED',
1754 p_trans1 => 'N',
1755 p_token2 => 'DATA_ERROR_MSG_TEXT',
1756 p_value2 => 'FEM_GL_POST_TO_BE_REPROCESSED',
1757 p_trans2 => 'Y');
1758
1759 END Log_Data_Error_Help_Msgs;
1760
1761 -- ========================================================================
1762
1763
1764 /*****************************************************************
1765 * PUBLIC PROCEDURES *
1766 *****************************************************************/
1767
1768 -- =======================================================================
1769 -- Procedure
1770 -- Main
1771 -- Purpose
1772 -- This is the "engine master" routine of the XGL Posting Engine
1773 -- History
1774 -- 10-23-03 S Kung Created
1775 -- 05-06-04 G Hall Bug# 3597527: Implemented changes for
1776 -- multiprocessing
1777 -- 05-13-04 G Hall Bug# 3597495: Added call to Get_SSC_To_Be_Processed,
1778 -- to be used by Final_Process_Logging to log Data
1779 -- Locations entries for all valid Source System Codes
1780 -- processed in the current set.
1781 -- 05-25-04 G Hall Changed call to Get_SSC_To_Be_Processed to
1782 -- parameterized call to Get_SSC.
1783 -- Arguments
1784 -- p_errbuf: Output parameter required by
1785 -- Concurrent Manager
1786 -- p_retcode: Output parameter required by
1787 -- Concurrent Manager
1788 -- p_ledger_id: Ledger to load data for
1789 -- p_cal_period_id: Period to load data for
1790 -- p_dataset_code: Target dataset to load data into
1791 -- p_xgl_int_obj_def_id: XGL/FEM integration rule object definition ID
1792 -- p_execution_mode: Execution mode, S (Snapshot)/I (Incremental)
1793 -- p_qtd_ytd_code: Specifies whether period-specific QTD and
1794 -- YTD balances will be maintained
1795 -- p_budget_id: Budget to be loaded
1796 -- p_enc_type_id: Encumbrance type to be loaded
1797 -- =======================================================================
1798
1799 PROCEDURE Main
1800 (x_errbuf OUT NOCOPY VARCHAR2,
1801 x_retcode OUT NOCOPY VARCHAR2,
1802 p_execution_mode IN VARCHAR2,
1803 p_ledger_id IN VARCHAR2,
1804 p_cal_period_id IN VARCHAR2,
1805 p_budget_id IN VARCHAR2,
1806 p_enc_type_id IN VARCHAR2,
1807 p_dataset_code IN VARCHAR2,
1808 p_xgl_int_obj_def_id IN VARCHAR2,
1809 p_qtd_ytd_code IN VARCHAR2) IS
1810
1811 FEMXGL_fatal_err EXCEPTION;
1812 FEMXGL_warn EXCEPTION;
1813 FEMXGL_no_data_to_load EXCEPTION;
1814 FEMXGL_all_data_invalid EXCEPTION;
1815
1816 v_status VARCHAR2(30);
1817 v_industry VARCHAR2(30);
1818
1819 v_cp_status VARCHAR2(30);
1820 v_exception_code VARCHAR2(30);
1821 v_eng_step VARCHAR2(12);
1822 v_slices_condition VARCHAR2(32767);
1823 v_reuse_slices VARCHAR2(1);
1824
1825 v_tot_cur_data_err_rows NUMBER;
1826 v_tot_prev_err_rows_reproc NUMBER;
1827 v_tot_posted_rows NUMBER;
1828
1829 TYPE v_msg_list_type IS VARRAY(20) OF
1830 fem_mp_process_ctl_t.message%TYPE;
1831 v_msg_list v_msg_list_type;
1832 i NUMBER;
1833
1834 v_compl_code NUMBER;
1835 v_ret_status BOOLEAN;
1836 v_warn_flag VARCHAR2(1);
1837
1838 v_param_list wf_parameter_list_t;
1839
1840 v_ds_bal_type_attr_name fem_dim_attributes_tl.attribute_name%TYPE;
1841 v_dataset_dim_name fem_dimensions_tl.dimension_name%TYPE;
1842 v_xgl_rule_type_name fem_object_types_tl.object_type_name%TYPE;
1843
1844 BEGIN
1845
1846 FEM_ENGINES_PKG.Tech_Message
1847 (p_severity => pc_log_level_procedure,
1848 p_module => 'fem.plsql.xgl_eng.main',
1849 p_app_name => 'FEM',
1850 p_msg_name => 'FEM_GL_POST_201',
1851 p_token1 => 'FUNC_NAME',
1852 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Main',
1853 p_token2 => 'TIME',
1854 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1855
1856 FEM_ENGINES_PKG.User_Message
1857 (p_app_name => 'FEM',
1858 p_msg_name => 'FEM_GL_POST_207');
1859
1860 FEM_ENGINES_PKG.Tech_Message
1861 (p_severity => pc_log_level_event,
1862 p_module => 'fem.plsql.xgl_eng.main',
1863 p_app_name => 'FEM',
1864 p_msg_name => 'FEM_GL_POST_207');
1865
1866 -- -----------------------------------
1867 -- *** Validate Engine Parameters ***
1868 -- -----------------------------------
1869
1870 v_warn_flag := 'N';
1871
1872 FEM_GL_POST_PROCESS_PKG.Validate_XGL_Eng_Parameters
1873 (p_ledger_id => TO_NUMBER(p_ledger_id),
1874 p_cal_period_id => TO_NUMBER(p_cal_period_id),
1875 p_dataset_code => TO_NUMBER(p_dataset_code),
1876 p_xgl_obj_def_id => TO_NUMBER(p_xgl_int_obj_def_id),
1877 p_exec_mode => p_execution_mode,
1878 p_qtd_ytd_code => p_qtd_ytd_code,
1879 p_budget_id => TO_NUMBER(p_budget_id),
1880 p_enc_type_id => TO_NUMBER(p_enc_type_id),
1881 x_completion_code => v_compl_code);
1882
1883 IF v_compl_code = 1 THEN
1884 v_warn_flag := 'Y';
1885 ELSIF v_compl_code = 2 THEN
1886 v_cp_status := 'ERROR';
1887 RAISE FEMXGL_fatal_err;
1888 END IF;
1889
1890 FEM_ENGINES_PKG.User_Message
1891 (p_app_name => 'FEM',
1892 p_msg_name => 'FEM_GL_POST_208');
1893
1894 FEM_ENGINES_PKG.Tech_Message
1895 (p_severity => pc_log_level_event,
1896 p_module => 'fem.plsql.xgl_eng.main',
1897 p_app_name => 'FEM',
1898 p_msg_name => 'FEM_GL_POST_208');
1899
1900 -- -----------------------------------
1901 -- *** Register Process Execution ***
1902 -- -----------------------------------
1903
1904 FEM_GL_POST_PROCESS_PKG.Register_Process_Execution
1905 (x_completion_code => v_compl_code);
1906
1907 IF v_compl_code = 2 THEN
1908 v_cp_status := 'ERROR';
1909 RAISE FEMXGL_fatal_err;
1910 END IF;
1911
1912 -- -----------------------------------
1913 -- Get schema name for FEM
1914 -- -----------------------------------
1915
1916 IF NOT FND_INSTALLATION.Get_App_Info
1917 (application_short_name => 'FEM',
1918 status => v_status,
1919 industry => v_industry,
1920 oracle_schema => pv_schema_name) THEN
1921
1922 FEM_ENGINES_PKG.User_Message
1923 (p_app_name => 'FEM',
1924 p_msg_name => 'FEM_GL_POST_227');
1925
1926 FEM_ENGINES_PKG.Tech_Message
1927 (p_severity => pc_log_level_error,
1928 p_module => 'fem.plsql.xgl_eng.main',
1929 p_app_name => 'FEM',
1930 p_msg_name => 'FEM_GL_POST_227');
1931
1932 v_cp_status := 'ERROR';
1933 RAISE FEMXGL_fatal_err;
1934
1935 END IF;
1936
1937 -- ---------------------------------------------------------------------
1938 -- Prepare the Condition SQL string to be passed to the MP master to be
1939 -- used as a filter for determining the data slices. For snaphot loads,
1940 -- the condition only needs to include the criteria for selecting the
1941 -- snapshot rows, without distinction between errored rows and new rows.
1942 -- For Incremental and Error Reprocessing modes, the engine will first
1943 -- process snapshot error rows, then it will process incremental rows,
1944 -- so these criteria need to be combined in the condition.
1945 -- ---------------------------------------------------------------------
1946
1947 IF p_execution_mode = 'S' THEN
1948
1949 -- one pass, only for snapshot rows, error and new
1950
1951 v_slices_condition := 'load_method_code = ''S'' ';
1952
1953 ELSIF p_execution_mode = 'I' THEN
1954
1955 -- two passes, first for snapshot errors, second for all incremental
1956
1957 v_slices_condition := '((load_method_code = ''I'') OR' ||
1958 ' (load_method_code = ''S'' AND' ||
1959 ' posting_error_code IS NOT NULL)) ';
1960
1961 ELSIF p_execution_mode = 'E' THEN
1962
1963 -- two passes, first for snapshot errors, second for incremental
1964 -- error rows
1965
1966 v_slices_condition := 'posting_error_code IS NOT NULL';
1967
1968 END IF;
1969
1970 Finish_Condition_String(x_condition_string => v_slices_condition);
1971
1972 -- Print out v_slices_condition for debugging purposes
1973 FEM_ENGINES_PKG.Tech_Message
1974 (p_severity => pc_log_level_statement,
1975 p_module => 'fem.plsql.xgl_eng.main',
1976 p_msg_text => 'v_slices_condition: ' || v_slices_condition);
1977
1978 FEM_GL_POST_PROCESS_PKG.pv_ssc_where := v_slices_condition;
1979
1980 -- ---------------------------------------------------------------------
1981 -- Get the list of distinct Source System Display Codes from the set
1982 -- of data to be processed. It will be used later by
1983 -- Final_Process_Logging to determine what to log in Data Locations.
1984 -- ---------------------------------------------------------------------
1985
1986 FEM_GL_POST_PROCESS_PKG.Get_SSC (p_dest_code => 'TBP');
1987
1988 -- ---------------------------------------------------------------------
1989 -- Call the Multiprocessing Framework master procedure. It will look up
1990 -- the multiprocessing parameters, determine the data slices, and start
1991 -- up one or more concurrent processes which will each invoke the
1992 -- Process_Data_Slice procedure for one data slice at a time, until all
1993 -- the data slices have been processed.
1994 -- ---------------------------------------------------------------------
1995
1996 IF p_execution_mode = 'S' THEN
1997 v_eng_step := 'SNAPSHOT';
1998 ELSE
1999 -- For Incremental mode and for Error Reprocessing mode, use the
2000 -- MP parameters set for the INCREMENTAL step.
2001 v_eng_step := 'INCREMENTAL';
2002 END IF;
2003
2004 IF FEM_GL_POST_PROCESS_PKG.pv_exec_state = 'RESTART' THEN
2005 -- The MP framework picks up where it left off in the previous
2006 -- attempt, with the next unprocessed data slice.
2007 v_reuse_slices := 'Y';
2008 ELSE
2009 -- For NORMAL or RERUN, the MP framework must re-compute the data
2010 -- slices. Some RERUN cases may be able to reuse the previous
2011 -- run's slices, but it's not guaranteed that the data in the table
2012 -- hasn't changed between runs.
2013 v_reuse_slices := 'N';
2014 END IF;
2015
2016 -- Bug 5734885. For each call to Process_Data_Slice, the MP FW will replace
2017 -- the '{{table_partition}}' placeholder value sent in the p_eng_sql parameter
2018 -- with the table partition clause appropriate for that data slice (NULL for
2019 -- no table partitioning).
2020
2021 FEM_MULTI_PROC_PKG.Master
2022 (X_PRG_STAT => v_cp_status,
2023 X_EXCEPTION_CODE => v_exception_code,
2024 P_RULE_ID => FEM_GL_POST_PROCESS_PKG.pv_rule_obj_id,
2025 P_ENG_STEP => v_eng_step,
2026 P_DATA_TABLE => 'FEM_BAL_INTERFACE_T',
2027 P_ENG_SQL => '{{table_partition}}',
2028 P_TABLE_ALIAS => NULL,
2029 P_RUN_NAME => 'XGL Integration ' ||
2030 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
2031 P_ENG_PRG => 'FEM_XGL_POST_ENGINE_PKG.PROCESS_DATA_SLICE',
2032 P_CONDITION => v_slices_condition,
2033 P_FAILED_REQ_ID => FEM_GL_POST_PROCESS_PKG.pv_prev_req_id,
2034 P_REUSE_SLICES => v_reuse_slices,
2035 P_ARG1 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_req_id),
2036 P_ARG2 => FEM_GL_POST_PROCESS_PKG.pv_exec_mode,
2037 P_ARG3 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_rule_obj_id),
2038 P_ARG4 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_dataset_code),
2039 P_ARG5 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_period_id),
2040 P_ARG6 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_ledger_id),
2041 P_ARG7 => FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code,
2042 P_ARG8 => FEM_GL_POST_PROCESS_PKG.pv_entered_crncy_flag,
2043 P_ARG9 => FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd,
2044 P_ARG10 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date,
2045 'YYYY/MM/DD HH24:MI:SS'),
2046 P_ARG11 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_gl_per_number),
2047 P_ARG12 => FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd,
2048 P_ARG13 => FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd,
2049 P_ARG14 => FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd,
2050 P_ARG15 => FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd,
2051 P_ARG16 => pv_schema_name);
2052
2053 FEM_ENGINES_PKG.Tech_Message
2054 (p_severity => pc_log_level_statement,
2055 p_module => 'fem.plsql.xgl_eng.main',
2056 p_app_name => 'FEM',
2057 p_msg_name => 'FEM_GL_POST_204',
2058 p_token1 => 'VAR_NAME',
2059 p_value1 => 'v_cp_status',
2060 p_token2 => 'VAR_VAL',
2061 p_value2 => v_cp_status);
2062
2063 v_cp_status := REPLACE(v_cp_status, 'COMPLETE:', '');
2064
2065 IF v_cp_status IN ('ERROR', 'CANCELLED', 'TERMINATED') THEN
2066
2067 -- -------------------------------------------------------------------
2068 -- The following two cases indicate that no data slice
2069 -- processing was done, either because there was no data
2070 -- found to process, or because of a fatal error in the
2071 -- MP Master.
2072 -- -------------------------------------------------------------------
2073
2074 IF v_exception_code = 'FEM_MP_NO_DATA_SLICES_ERR' THEN
2075
2076 RAISE FEMXGL_no_data_to_load;
2077
2078 ELSIF v_exception_code IS NOT NULL THEN
2079
2080 FEM_ENGINES_PKG.Tech_Message
2081 (p_severity => pc_log_level_statement,
2082 p_module => 'fem.plsql.xgl_eng.main',
2083 p_app_name => 'FEM',
2084 p_msg_name => 'FEM_GL_POST_204',
2085 p_token1 => 'VAR_NAME',
2086 p_value1 => 'v_exception_code',
2087 p_token2 => 'VAR_VAL',
2088 p_value2 => v_exception_code);
2089
2090 v_tot_posted_rows := 0;
2091 v_tot_prev_err_rows_reproc := 0;
2092 v_tot_cur_data_err_rows := 0;
2093
2094 RAISE FEMXGL_fatal_err;
2095
2096 END IF;
2097
2098 END IF;
2099
2100 -- ---------------------------------------------------------------------
2101 -- There should be some data slices; get row totals from the data
2102 -- slices table.
2103 -- ---------------------------------------------------------------------
2104
2105 SELECT SUM(rows_loaded), SUM(rows_processed), SUM(rows_rejected)
2106 INTO v_tot_posted_rows, v_tot_prev_err_rows_reproc, v_tot_cur_data_err_rows
2107 FROM fem_mp_process_ctl_t
2108 WHERE req_id = FEM_GL_POST_PROCESS_PKG.pv_req_id;
2109
2110 FEM_ENGINES_PKG.Tech_Message
2111 (p_severity => pc_log_level_statement,
2112 p_module => 'fem.plsql.xgl_eng.main',
2113 p_app_name => 'FEM',
2114 p_msg_name => 'FEM_GL_POST_204',
2115 p_token1 => 'VAR_NAME',
2116 p_value1 => 'v_tot_posted_rows',
2117 p_token2 => 'VAR_VAL',
2118 p_value2 => TO_CHAR(v_tot_posted_rows));
2119
2120 FEM_ENGINES_PKG.Tech_Message
2121 (p_severity => pc_log_level_statement,
2122 p_module => 'fem.plsql.xgl_eng.main',
2123 p_app_name => 'FEM',
2124 p_msg_name => 'FEM_GL_POST_204',
2125 p_token1 => 'VAR_NAME',
2126 p_value1 => 'v_tot_prev_err_rows_reproc',
2127 p_token2 => 'VAR_VAL',
2128 p_value2 => TO_CHAR(v_tot_prev_err_rows_reproc));
2129
2130 FEM_ENGINES_PKG.Tech_Message
2131 (p_severity => pc_log_level_statement,
2132 p_module => 'fem.plsql.xgl_eng.main',
2133 p_app_name => 'FEM',
2134 p_msg_name => 'FEM_GL_POST_204',
2135 p_token1 => 'VAR_NAME',
2136 p_value1 => 'v_tot_cur_data_err_rows',
2137 p_token2 => 'VAR_VAL',
2138 p_value2 => TO_CHAR(v_tot_cur_data_err_rows));
2139
2140 -- -------------------------------------------------------------------
2141 -- Log any fatal errors that occurred during data slice processing.
2142 -- -------------------------------------------------------------------
2143
2144 IF v_cp_status IN ('ERROR', 'CANCELLED', 'TERMINATED') THEN
2145
2146 -- Get the error messages from all data slice entries with an error status.
2147
2148 v_msg_list := v_msg_list_type();
2149
2150 SELECT DISTINCT(message)
2151 BULK COLLECT INTO v_msg_list
2152 FROM fem_mp_process_ctl_t
2153 WHERE req_id = FEM_GL_POST_PROCESS_PKG.pv_req_id
2154 AND status = 2;
2155
2156 FEM_ENGINES_PKG.Tech_Message
2157 (p_severity => pc_log_level_statement,
2158 p_module => 'fem.plsql.xgl_eng.main',
2159 p_app_name => 'FEM',
2160 p_msg_name => 'FEM_GL_POST_204',
2161 p_token1 => 'VAR_NAME',
2162 p_value1 => 'v_msg_list.count',
2163 p_token2 => 'VAR_VAL',
2164 p_value2 => TO_CHAR(v_msg_list.count));
2165
2166 -- Log all of the messages
2167
2168 FOR i IN 1..v_msg_list.count LOOP
2169
2170 FEM_ENGINES_PKG.Tech_Message
2171 (p_severity => pc_log_level_error,
2172 p_module => 'fem.plsql.xgl_eng.main',
2173 p_app_name => 'FEM',
2174 p_msg_text => v_msg_list(i));
2175
2176 FEM_ENGINES_PKG.User_Message
2177 (p_app_name => 'FEM',
2178 p_msg_text => v_msg_list(i));
2179
2180 END LOOP;
2181
2182 -- Delete this process' data slice entries from the data slices table
2183 FEM_MULTI_PROC_PKG.Delete_Data_Slices(FEM_GL_POST_PROCESS_PKG.pv_req_id);
2184
2185 RAISE FEMXGL_fatal_err;
2186
2187 END IF;
2188
2189 -- Delete this process' data slice entries from the data slices table
2190 FEM_MULTI_PROC_PKG.Delete_Data_Slices(FEM_GL_POST_PROCESS_PKG.pv_req_id);
2191
2192 -- -------------------------------------------------------------------
2193 -- At this point, no fatal error has occurred, but there may be error
2194 -- conditions or warnings based on how much data was (or wasn't)
2195 -- successfully processed.
2196 -- -------------------------------------------------------------------
2197
2198 IF v_tot_posted_rows = 0
2199 AND FEM_GL_POST_PROCESS_PKG.pv_exec_mode = 'S' THEN
2200
2201 -- -------------------------------------------------------------------
2202 -- Since No Data to Post has already been trapped, at this point if
2203 -- Rows_Loaded = 0 it means there was data to post but it all had
2204 -- data errors. This is treated as an error for snapshot mode.
2205 -- -------------------------------------------------------------------
2206
2207 RAISE FEMXGL_all_data_invalid;
2208
2209 END IF;
2210
2211 -- -------------------------------------------------------------------
2212 -- Prepare parameters for raising the business event
2213 -- -------------------------------------------------------------------
2214
2215 WF_EVENT.addparametertolist
2216 (p_name => 'REQUEST_ID',
2217 p_value => FEM_GL_POST_PROCESS_PKG.pv_req_id,
2218 p_parameterlist => v_param_list);
2219
2220 WF_EVENT.addparametertolist
2221 (p_name => 'EXECUTION_MODE',
2222 p_value => p_execution_mode,
2223 p_parameterlist => v_param_list);
2224
2225 WF_EVENT.addparametertolist
2226 (p_name => 'LEDGER_ID',
2227 p_value => p_ledger_id,
2228 p_parameterlist => v_param_list);
2229
2230 WF_EVENT.addparametertolist
2231 (p_name => 'CAL_PERIOD_ID',
2232 p_value => p_cal_period_id,
2233 p_parameterlist => v_param_list);
2234
2235 WF_EVENT.addparametertolist
2236 (p_name => 'DATASET_CODE',
2237 p_value => p_dataset_code,
2238 p_parameterlist => v_param_list);
2239
2240 WF_EVENT.addparametertolist
2241 (p_name => 'OBJECT_DEFINITION_ID',
2242 p_value => p_xgl_int_obj_def_id,
2243 p_parameterlist => v_param_list);
2244
2245 WF_EVENT.addparametertolist
2246 (p_name => 'QTD_YTD_CODE',
2247 p_value => p_qtd_ytd_code,
2248 p_parameterlist => v_param_list);
2249
2250 WF_EVENT.addparametertolist
2251 (p_name => 'BUDGET_ID',
2252 p_value => p_budget_id,
2253 p_parameterlist => v_param_list);
2254
2255 WF_EVENT.addparametertolist
2256 (p_name => 'ENCUMBRANCE_TYPE_ID',
2257 p_value => p_enc_type_id,
2258 p_parameterlist => v_param_list);
2259
2260 -- -------------------------------------------------------------------
2261 -- Check for WARNING Status
2262 -- -------------------------------------------------------------------
2263
2264 IF v_tot_cur_data_err_rows > 0 THEN
2265
2266 -- WARNING: Some rows from the interface table have data errors
2267 -- and could not be loaded. Correct the errors in the remaining
2268 -- interface rows and resubmit them for processing.
2269
2270 FEM_ENGINES_PKG.User_Message
2271 (p_app_name => 'FEM',
2272 p_msg_name => 'FEM_GL_POST_226');
2273
2274 FEM_ENGINES_PKG.Tech_Message
2275 (p_severity => pc_log_level_error,
2276 p_module => 'fem.plsql.xgl_eng.main',
2277 p_app_name => 'FEM',
2278 p_msg_name => 'FEM_GL_POST_226');
2279
2280 Log_Data_Error_Help_Msgs;
2281
2282 RAISE FEMXGL_warn;
2283
2284 ELSIF v_warn_flag = 'Y' THEN
2285
2286 RAISE FEMXGL_warn;
2287
2288 END IF;
2289
2290 -- ------------------------------
2291 -- *** Final Process Logging ***
2292 -- ------------------------------
2293
2294 -- Not ending in WARNING status, so Load Status for the business
2295 -- event is COMPLETE.
2296
2297 WF_EVENT.addparametertolist
2298 (p_name => 'LOAD_STATUS',
2299 p_value => 'COMPLETE',
2300 p_parameterlist => v_param_list);
2301
2302 -- Raise the event
2303
2304 WF_EVENT.RAISE
2305 (p_event_name => 'oracle.apps.fem.xglintg.balrule.execute',
2306 p_event_key => NULL,
2307 p_parameters => v_param_list);
2308
2309 v_param_list.DELETE;
2310
2311 -- Perform final process logging, including Data Locations entries
2312
2313 FEM_GL_POST_PROCESS_PKG.Final_Process_Logging
2314 (p_exec_status => 'SUCCESS',
2315 p_num_data_errors => v_tot_cur_data_err_rows,
2316 p_num_data_errors_reproc => v_tot_prev_err_rows_reproc,
2317 p_num_output_rows => v_tot_posted_rows,
2318 p_final_message_name => 'FEM_GL_POST_220');
2319
2320 FEM_ENGINES_PKG.Tech_Message
2321 (p_severity => pc_log_level_procedure,
2322 p_module => 'fem.plsql.xgl_eng.main',
2323 p_app_name => 'FEM',
2324 p_msg_name => 'FEM_GL_POST_202',
2325 p_token1 => 'FUNC_NAME',
2326 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Main',
2327 p_token2 => 'TIME',
2328 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2329
2330 v_ret_status := FND_CONCURRENT.Set_Completion_Status
2331 (status => 'NORMAL', message => NULL);
2332
2333 EXCEPTION
2334 WHEN FEMXGL_fatal_err THEN
2335
2336 -- Perform post-process logging
2337 FEM_GL_POST_PROCESS_PKG.Final_Process_Logging
2338 (p_exec_status => 'ERROR_RERUN',
2339 p_num_data_errors => v_tot_cur_data_err_rows,
2340 p_num_data_errors_reproc => v_tot_prev_err_rows_reproc,
2341 p_num_output_rows => v_tot_posted_rows,
2342 p_final_message_name => 'FEM_GL_POST_205');
2343
2344 FEM_ENGINES_PKG.Tech_Message
2345 (p_severity => pc_log_level_procedure,
2346 p_module => 'fem.plsql.xgl_eng.main',
2347 p_app_name => 'FEM',
2348 p_msg_name => 'FEM_GL_POST_203',
2349 p_token1 => 'FUNC_NAME',
2350 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Main',
2351 p_token2 => 'TIME',
2352 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2353
2354 v_ret_status := FND_CONCURRENT.Set_Completion_Status
2355 (status => v_cp_status, message => NULL);
2356
2357 COMMIT;
2358
2359 WHEN FEMXGL_warn THEN
2360
2361 -- Ending in WARNING status, so Load Status for the business
2362 -- event is INCOMPLETE.
2363
2364 WF_EVENT.addparametertolist
2365 (p_name => 'LOAD_STATUS',
2366 p_value => 'INCOMPLETE',
2367 p_parameterlist => v_param_list);
2368
2369 -- Raise the event
2370
2371 WF_EVENT.RAISE
2372 (p_event_name => 'oracle.apps.fem.xglintg.balrule.execute',
2373 p_event_key => NULL,
2374 p_parameters => v_param_list);
2375
2376 v_param_list.DELETE;
2377
2378 -- Perform post-process logging, including Data Locations entries
2379
2380 FEM_GL_POST_PROCESS_PKG.Final_Process_Logging
2381 (p_exec_status => 'SUCCESS',
2382 p_num_data_errors => v_tot_cur_data_err_rows,
2383 p_num_data_errors_reproc => v_tot_prev_err_rows_reproc,
2384 p_num_output_rows => v_tot_posted_rows,
2385 p_final_message_name => 'FEM_GL_POST_206');
2386
2387 -- Logging final messages
2388
2389 FEM_ENGINES_PKG.Tech_Message
2390 (p_severity => pc_log_level_procedure,
2391 p_module => 'fem.plsql.xgl_eng.main',
2392 p_app_name => 'FEM',
2393 p_msg_name => 'FEM_GL_POST_202',
2394 p_token1 => 'FUNC_NAME',
2395 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Main',
2396 p_token2 => 'TIME',
2397 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2398
2399 v_ret_status := FND_CONCURRENT.Set_Completion_Status
2400 (status => 'WARNING', message => NULL);
2401
2402 COMMIT;
2403
2404 WHEN FEMXGL_no_data_to_load THEN
2405
2406 SELECT attribute_name
2407 INTO v_ds_bal_type_attr_name
2408 FROM fem_dim_attributes_vl
2409 WHERE attribute_varchar_label = 'DATASET_BALANCE_TYPE_CODE';
2410
2411 SELECT dimension_name
2412 INTO v_dataset_dim_name
2413 FROM fem_dimensions_vl
2414 WHERE dimension_varchar_label = 'DATASET';
2415
2416 SELECT object_type_name
2417 INTO v_xgl_rule_type_name
2418 FROM fem_object_types_vl
2419 WHERE object_type_code = 'XGL_INTEGRATION';
2420
2421 FEM_ENGINES_PKG.User_Message
2422 (p_app_name => 'FEM',
2423 p_msg_name => 'FEM_GL_POST_225',
2424 p_token1 => 'LOAD_METHOD_CODE',
2425 p_value1 => FEM_GL_POST_PROCESS_PKG.pv_exec_mode,
2426 p_token2 => 'CAL_PER_DIM_GRP_DISPLAY_CODE',
2427 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd,
2428 p_token3 => 'CAL_PERIOD_END_DATE',
2429 p_value3 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date) || ' ' ||
2430 TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date, 'HH24:MI:SS'),
2431 p_token4 => 'CAL_PERIOD_NUMBER',
2432 p_value4 => FEM_GL_POST_PROCESS_PKG.pv_gl_per_number,
2433 p_token5 => 'LEDGER_DISPLAY_CODE',
2434 p_value5 => FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd,
2435 p_token6 => 'DS_BALANCE_TYPE_CODE',
2436 p_value6 => FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd,
2437 p_token7 => 'BUDGET_DISPLAY_CODE',
2438 p_value7 => FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd,
2439 p_token8 => 'ENCUMBRANCE_TYPE_CODE',
2440 p_value8 => FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd);
2441
2442 FEM_ENGINES_PKG.User_Message
2443 (p_app_name => 'FEM',
2444 p_msg_name => 'FEM_GL_POST_228',
2445 p_token1 => 'DS_BAL_TYPE_ATTR_NAME',
2446 p_value1 => v_ds_bal_type_attr_name,
2447 p_token2 => 'DATASET_PARAM_NAME',
2448 p_value2 => v_dataset_dim_name,
2449 p_token3 => 'XGL_RULE_TYPE_NAME',
2450 p_value3 => v_xgl_rule_type_name);
2451
2452 FEM_ENGINES_PKG.Tech_Message
2453 (p_severity => pc_log_level_unexpected,
2454 p_module => 'fem.plsql.xgl_eng.main',
2455 p_app_name => 'FEM',
2456 p_msg_name => 'FEM_GL_POST_225',
2457 p_token1 => 'LOAD_METHOD_CODE',
2458 p_value1 => FEM_GL_POST_PROCESS_PKG.pv_exec_mode,
2459 p_token2 => 'CAL_PER_DIM_GRP_DISPLAY_CODE',
2460 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd,
2461 p_token3 => 'CAL_PERIOD_END_DATE',
2462 p_value3 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date) || ' ' ||
2463 TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date, 'HH24:MI:SS'),
2464 p_token4 => 'CAL_PERIOD_NUMBER',
2465 p_value4 => FEM_GL_POST_PROCESS_PKG.pv_gl_per_number,
2466 p_token5 => 'LEDGER_DISPLAY_CODE',
2467 p_value5 => FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd,
2468 p_token6 => 'DS_BALANCE_TYPE_CODE',
2469 p_value6 => FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd,
2470 p_token7 => 'BUDGET_DISPLAY_CODE',
2471 p_value7 => FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd,
2472 p_token8 => 'ENCUMBRANCE_TYPE_CODE',
2473 p_value8 => FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd);
2474
2475 IF FEM_GL_POST_PROCESS_PKG.pv_exec_mode IN ('I', 'E') THEN
2476
2477 -- For Incremental and Error Reprocessing modes, no data found to
2478 -- load may be a common occurrence and is not treated as an error.
2479
2480 FEM_GL_POST_PROCESS_PKG.Final_Process_Logging
2481 (p_exec_status => 'SUCCESS',
2482 p_num_data_errors => 0,
2483 p_num_data_errors_reproc => 0,
2484 p_num_output_rows => 0,
2485 p_final_message_name => 'FEM_GL_POST_220');
2486
2487 v_ret_status := FND_CONCURRENT.Set_Completion_Status
2488 (status => 'WARNING', message => NULL);
2489
2490 ELSE
2491
2492 -- For Snapshot execution mode, no data found to load is treated
2493 -- as an error.
2494
2495 FEM_GL_POST_PROCESS_PKG.Final_Process_Logging
2496 (p_exec_status => 'ERROR_RERUN',
2497 p_num_data_errors => 0,
2498 p_num_data_errors_reproc => 0,
2499 p_num_output_rows => 0,
2500 p_final_message_name => 'FEM_GL_POST_205');
2501
2502 v_ret_status := FND_CONCURRENT.Set_Completion_Status
2503 (status => 'ERROR', message => NULL);
2504
2505 END IF;
2506
2507 FEM_ENGINES_PKG.Tech_Message
2508 (p_severity => pc_log_level_procedure,
2509 p_module => 'fem.plsql.xgl_eng.main',
2510 p_app_name => 'FEM',
2511 p_msg_name => 'FEM_GL_POST_202',
2512 p_token1 => 'FUNC_NAME',
2513 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Main',
2514 p_token2 => 'TIME',
2515 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2516
2517 COMMIT;
2518
2519 WHEN FEMXGL_all_data_invalid THEN
2520
2521 FEM_ENGINES_PKG.User_Message
2522 (p_app_name => 'FEM',
2523 p_msg_name => 'FEM_GL_POST_224');
2524
2525 FEM_ENGINES_PKG.Tech_Message
2526 (p_severity => pc_log_level_error,
2527 p_module => 'fem.plsql.xgl_eng.main',
2528 p_app_name => 'FEM',
2529 p_msg_name => 'FEM_GL_POST_224');
2530
2531 Log_Data_Error_Help_Msgs;
2532
2533 -- Perform post-process logging
2534 FEM_GL_POST_PROCESS_PKG.Final_Process_Logging
2535 (p_exec_status => 'ERROR_RERUN',
2536 p_num_data_errors => v_tot_cur_data_err_rows,
2537 p_num_data_errors_reproc => v_tot_prev_err_rows_reproc,
2538 p_num_output_rows => v_tot_posted_rows,
2539 p_final_message_name => 'FEM_GL_POST_205');
2540
2541 FEM_ENGINES_PKG.Tech_Message
2542 (p_severity => pc_log_level_procedure,
2543 p_module => 'fem.plsql.xgl_eng.main',
2544 p_app_name => 'FEM',
2545 p_msg_name => 'FEM_GL_POST_203',
2546 p_token1 => 'FUNC_NAME',
2547 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Main',
2548 p_token2 => 'TIME',
2549 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2550
2551 v_ret_status := FND_CONCURRENT.Set_Completion_Status
2552 (status => 'ERROR', message => NULL);
2553
2554 COMMIT;
2555
2556 WHEN OTHERS THEN
2557
2558 ROLLBACK;
2559
2560 FEM_ENGINES_PKG.User_Message
2561 (p_app_name => 'FEM',
2562 p_msg_name => 'FEM_GL_POST_215',
2563 p_token1 => 'ERR_MSG',
2564 p_value1 => SQLERRM);
2565
2566 FEM_ENGINES_PKG.Tech_Message
2567 (p_severity => pc_log_level_unexpected,
2568 p_module => 'fem.plsql.xgl_eng.main',
2569 p_app_name => 'FEM',
2570 p_msg_name => 'FEM_GL_POST_215',
2571 p_token1 => 'ERR_MSG',
2572 p_value1 => SQLERRM);
2573
2574 -- Perform post-process logging
2575 FEM_GL_POST_PROCESS_PKG.Final_Process_Logging
2576 (p_exec_status => 'ERROR_RERUN',
2577 p_num_data_errors => v_tot_cur_data_err_rows,
2578 p_num_data_errors_reproc => v_tot_prev_err_rows_reproc,
2579 p_num_output_rows => v_tot_posted_rows,
2580 p_final_message_name => 'FEM_GL_POST_205');
2581
2582 FEM_ENGINES_PKG.Tech_Message
2583 (p_severity => pc_log_level_procedure,
2584 p_module => 'fem.plsql.xgl_eng.main',
2585 p_app_name => 'FEM',
2586 p_msg_name => 'FEM_GL_POST_203',
2587 p_token1 => 'FUNC_NAME',
2588 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Main',
2589 p_token2 => 'TIME',
2590 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2591
2592 v_ret_status := FND_CONCURRENT.Set_Completion_Status
2593 (status => 'ERROR', message => NULL);
2594
2595 COMMIT;
2596
2597 END Main;
2598
2599
2600 -- =======================================================================
2601 -- Procedure
2602 -- Log_Pkg_Variables
2603 -- Purpose
2604 -- Log the values of the FEM_GL_POST_PROCESS_PKG package variables
2605 -- which have been reset from the last 16 parameters.
2606 -- History
2607 -- 05-05-04 G Hall Bug# 3597527: Created
2608 -- =======================================================================
2609
2610 PROCEDURE Log_Pkg_Variables IS
2611
2612 BEGIN
2613
2614 FEM_ENGINES_PKG.Tech_Message
2615 (p_severity => pc_log_level_procedure,
2616 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2617 p_app_name => 'FEM',
2618 p_msg_name => 'FEM_GL_POST_201',
2619 p_token1 => 'FUNC_NAME',
2620 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Log_Package_Variables',
2621 p_token2 => 'TIME',
2622 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2623
2624 FEM_ENGINES_PKG.Tech_Message
2625 (p_severity => pc_log_level_statement,
2626 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2627 p_app_name => 'FEM',
2628 p_msg_name => 'FEM_GL_POST_204',
2629 p_token1 => 'VAR_NAME',
2630 p_value1 => 'pv_process_slice',
2631 p_token2 => 'VAR_VAL',
2632 p_value2 => pv_process_slice);
2633
2634 FEM_ENGINES_PKG.Tech_Message
2635 (p_severity => pc_log_level_statement,
2636 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2637 p_app_name => 'FEM',
2638 p_msg_name => 'FEM_GL_POST_204',
2639 p_token1 => 'VAR_NAME',
2640 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_req_id',
2641 p_token2 => 'VAR_VAL',
2642 p_value2 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_req_id));
2643
2644 FEM_ENGINES_PKG.Tech_Message
2645 (p_severity => pc_log_level_statement,
2646 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2647 p_app_name => 'FEM',
2648 p_msg_name => 'FEM_GL_POST_204',
2649 p_token1 => 'VAR_NAME',
2650 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_exec_mode',
2651 p_token2 => 'VAR_VAL',
2652 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_exec_mode);
2653
2654 FEM_ENGINES_PKG.Tech_Message
2655 (p_severity => pc_log_level_statement,
2656 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2657 p_app_name => 'FEM',
2658 p_msg_name => 'FEM_GL_POST_204',
2659 p_token1 => 'VAR_NAME',
2660 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_rule_obj_id',
2661 p_token2 => 'VAR_VAL',
2662 p_value2 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_rule_obj_id));
2663
2664 FEM_ENGINES_PKG.Tech_Message
2665 (p_severity => pc_log_level_statement,
2666 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2667 p_app_name => 'FEM',
2668 p_msg_name => 'FEM_GL_POST_204',
2669 p_token1 => 'VAR_NAME',
2670 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_dataset_code',
2671 p_token2 => 'VAR_VAL',
2672 p_value2 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_dataset_code));
2673
2674 FEM_ENGINES_PKG.Tech_Message
2675 (p_severity => pc_log_level_statement,
2676 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2677 p_app_name => 'FEM',
2678 p_msg_name => 'FEM_GL_POST_204',
2679 p_token1 => 'VAR_NAME',
2680 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_cal_period_id',
2681 p_token2 => 'VAR_VAL',
2682 p_value2 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_period_id));
2683
2684 FEM_ENGINES_PKG.Tech_Message
2685 (p_severity => pc_log_level_statement,
2686 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2687 p_app_name => 'FEM',
2688 p_msg_name => 'FEM_GL_POST_204',
2689 p_token1 => 'VAR_NAME',
2690 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_ledger_id',
2691 p_token2 => 'VAR_VAL',
2692 p_value2 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_ledger_id));
2693
2694 FEM_ENGINES_PKG.Tech_Message
2695 (p_severity => pc_log_level_statement,
2696 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2697 p_app_name => 'FEM',
2698 p_msg_name => 'FEM_GL_POST_204',
2699 p_token1 => 'VAR_NAME',
2700 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code',
2701 p_token2 => 'VAR_VAL',
2702 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code);
2703
2704 FEM_ENGINES_PKG.Tech_Message
2705 (p_severity => pc_log_level_statement,
2706 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2707 p_app_name => 'FEM',
2708 p_msg_name => 'FEM_GL_POST_204',
2709 p_token1 => 'VAR_NAME',
2710 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_entered_crncy_flag',
2711 p_token2 => 'VAR_VAL',
2712 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_entered_crncy_flag);
2713
2714 FEM_ENGINES_PKG.Tech_Message
2715 (p_severity => pc_log_level_statement,
2716 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2717 p_app_name => 'FEM',
2718 p_msg_name => 'FEM_GL_POST_204',
2719 p_token1 => 'VAR_NAME',
2720 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd',
2721 p_token2 => 'VAR_VAL',
2722 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd);
2723
2724 FEM_ENGINES_PKG.Tech_Message
2725 (p_severity => pc_log_level_statement,
2726 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2727 p_app_name => 'FEM',
2728 p_msg_name => 'FEM_GL_POST_204',
2729 p_token1 => 'VAR_NAME',
2730 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date',
2731 p_token2 => 'VAR_VAL',
2732 p_value2 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date,
2733 'YYYY/MM/DD HH24:MI:SS'));
2734
2735 FEM_ENGINES_PKG.Tech_Message
2736 (p_severity => pc_log_level_statement,
2737 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2738 p_app_name => 'FEM',
2739 p_msg_name => 'FEM_GL_POST_204',
2740 p_token1 => 'VAR_NAME',
2741 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_gl_per_number',
2742 p_token2 => 'VAR_VAL',
2743 p_value2 => TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_gl_per_number));
2744
2745 FEM_ENGINES_PKG.Tech_Message
2746 (p_severity => pc_log_level_statement,
2747 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2748 p_app_name => 'FEM',
2749 p_msg_name => 'FEM_GL_POST_204',
2750 p_token1 => 'VAR_NAME',
2751 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd',
2752 p_token2 => 'VAR_VAL',
2753 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd);
2754
2755 FEM_ENGINES_PKG.Tech_Message
2756 (p_severity => pc_log_level_statement,
2757 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2758 p_app_name => 'FEM',
2759 p_msg_name => 'FEM_GL_POST_204',
2760 p_token1 => 'VAR_NAME',
2761 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd',
2762 p_token2 => 'VAR_VAL',
2763 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd);
2764
2765 FEM_ENGINES_PKG.Tech_Message
2766 (p_severity => pc_log_level_statement,
2767 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2768 p_app_name => 'FEM',
2769 p_msg_name => 'FEM_GL_POST_204',
2770 p_token1 => 'VAR_NAME',
2771 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd',
2772 p_token2 => 'VAR_VAL',
2773 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd);
2774
2775 FEM_ENGINES_PKG.Tech_Message
2776 (p_severity => pc_log_level_statement,
2777 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2778 p_app_name => 'FEM',
2779 p_msg_name => 'FEM_GL_POST_204',
2780 p_token1 => 'VAR_NAME',
2781 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd',
2782 p_token2 => 'VAR_VAL',
2783 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd);
2784
2785 FEM_ENGINES_PKG.Tech_Message
2786 (p_severity => pc_log_level_statement,
2787 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2788 p_app_name => 'FEM',
2789 p_msg_name => 'FEM_GL_POST_204',
2790 p_token1 => 'VAR_NAME',
2791 p_value1 => 'pv_schema_name',
2792 p_token2 => 'VAR_VAL',
2793 p_value2 => pv_schema_name);
2794
2795 FEM_ENGINES_PKG.Tech_Message
2796 (p_severity => pc_log_level_procedure,
2797 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2798 p_app_name => 'FEM',
2799 p_msg_name => 'FEM_GL_POST_202',
2800 p_token1 => 'FUNC_NAME',
2801 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Log_Package_Variables',
2802 p_token2 => 'TIME',
2803 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2804
2805 END Log_Pkg_Variables;
2806
2807
2808 -- =======================================================================
2809 -- Procedure
2810 -- Process_Data_Slice
2811 -- Purpose
2812 -- This is the "engine push procedure" of the XGL Posting Engine
2813 -- History
2814 -- 04-27-04 G Hall Bug# 3597527: Created (moved code from Main)
2815 -- 05-25-04 G Hall Added v_compl_code in call to Get_Proc_Key_Info.
2816 -- Arguments
2817 -- x_slice_status_cd 0 = Successful; 1 = Warning; 2 = Failed. The
2818 -- subrequest will put this value into the
2819 -- STATUS colum in FEM_MP_PROCESS_CTL_T.
2820 -- x_slice_msg NULL if successful; end-user error message
2821 -- if failed with unexpected error. The
2822 -- subrequest will put this value into the
2823 -- MESSAGE colum in FEM_MP_PROCESS_CTL_T.
2824 -- x_slice_errors_reprocessed For XGL, used for reporting the number of
2825 -- previous data errors successfully reprocessed.
2826 -- The subrequest will put this value into the
2827 -- ROWS_PROCESSED column in FEM_MP_PROCESS_CTL_T.
2828 -- x_slice_output_rows The number of rows inserted or merged into
2829 -- FEM_BALANCES for the current data slice. The
2830 -- subrequest will put this value into the
2831 -- ROWS_LOADED column in FEM_MP_PROCESS_CTL_T.
2832 -- x_slice_errors_reported The total number of rows not loaded due to
2833 -- data errors (whether it's a new error or an
2834 -- old one still not successfully reprocessed).
2835 -- The subrequest will put this value into the
2836 -- ROWS_REJECTED column in FEM_MP_PROCESS_CTL_T.
2837 -- p_eng_sql Engine SQL statement shell. From the P_ENG_SQL
2838 -- parameter passed to the MP Master. For XGL, it
2839 -- contains a table partitioning clause appropriate
2840 -- for the data slice (or will be NULL for no table
2841 -- partitioning).
2842 -- p_data_slice_predicate Example:
2843 -- NATURAL_ACCOUNT_DISPLAY_CODE IS BETWEEN
2844 -- 'AAA-XXX-192' AND 'BBB-YYY-104'
2845 -- p_process_number Identifies the calling Subrequest.
2846 -- p_slice_id Uniquely identifies the data slice.
2847 -- p_fetch_limit For use with BULK COLLECT INTO; not used by the
2848 -- XGL engine.
2849 -- The following parameters are the ones passed to the MP Master procedure
2850 -- as P_ARG1 to P_ARG16. The concurrent subrequests pass these parameters
2851 -- positionally to Process_Data_Slice.
2852 -- Corresponding FEM_GL_POST_PROCESS_PKG
2853 -- Package Variable:
2854 -- p_req_id pv_req_id
2855 -- p_exec_mode pv_exec_mode
2856 -- p_rule_obj_id pv_rule_obj_id
2857 -- p_dataset_code pv_dataset_code
2858 -- p_cal_period_id pv_cal_period_id
2859 -- p_ledger_id pv_ledger_id
2860 -- p_qtd_ytd_code pv_qtd_ytd_code
2861 -- p_entered_crncy_flag pv_entered_crncy_flag
2862 -- p_cal_per_dim_grp_dsp_cd pv_cal_per_dim_grp_dsp_cd
2863 -- p_cal_per_end_date pv_cal_per_end_date
2864 -- p_gl_per_number pv_gl_per_number
2865 -- p_ledger_dsp_cd pv_ledger_dsp_cd
2866 -- p_budget_dsp_cd pv_budget_dsp_cd
2867 -- p_enc_type_dsp_cd pv_enc_type_dsp_cd
2868 -- p_ds_balance_type_cd pv_ds_balance_type_cd
2869 -- p_schema_name pv_schema_name (in this package)
2870 -- =======================================================================
2871
2872 PROCEDURE Process_Data_Slice
2873 (x_slice_status_cd OUT NOCOPY NUMBER,
2874 x_slice_msg OUT NOCOPY VARCHAR2,
2875 x_slice_errors_reprocessed OUT NOCOPY NUMBER,
2876 x_slice_output_rows OUT NOCOPY NUMBER,
2877 x_slice_errors_reported OUT NOCOPY NUMBER,
2878 p_eng_sql IN VARCHAR2,
2879 p_data_slice_predicate IN VARCHAR2,
2880 p_process_number IN NUMBER,
2881 p_slice_id IN NUMBER,
2882 p_fetch_limit IN NUMBER,
2883 p_req_id IN VARCHAR2,
2884 p_exec_mode IN VARCHAR2,
2885 p_rule_obj_id IN VARCHAR2,
2886 p_dataset_code IN VARCHAR2,
2887 p_cal_period_id IN VARCHAR2,
2888 p_ledger_id IN VARCHAR2,
2889 p_qtd_ytd_code IN VARCHAR2,
2890 p_entered_crncy_flag IN VARCHAR2,
2891 p_cal_per_dim_grp_dsp_cd IN VARCHAR2,
2892 p_cal_per_end_date IN VARCHAR2,
2893 p_gl_per_number IN VARCHAR2,
2894 p_ledger_dsp_cd IN VARCHAR2,
2895 p_budget_dsp_cd IN VARCHAR2,
2896 p_enc_type_dsp_cd IN VARCHAR2,
2897 p_ds_balance_type_cd IN VARCHAR2,
2898 p_schema_name IN VARCHAR2) IS
2899
2900 v_compl_code NUMBER;
2901
2902 v_rows_marked NUMBER;
2903 v_posted_rows NUMBER;
2904 v_prev_err_rows_reproc NUMBER;
2905 v_cur_data_err_rows NUMBER;
2906
2907 v_tot_rows_marked NUMBER;
2908 v_tot_posted_rows NUMBER;
2909 v_tot_prev_err_rows_reproc NUMBER;
2910 v_tot_cur_data_err_rows NUMBER;
2911
2912 TYPE LoadSetCursor IS REF CURSOR;
2913 GetLoadSet_Cursor LoadSetCursor;
2914
2915 v_incr_cursor_stmt VARCHAR2(32767);
2916 v_err_cursor_stmt VARCHAR2(32767);
2917
2918 v_curr_load_set_id NUMBER;
2919
2920 FEMXGL_fatal_err EXCEPTION;
2921
2922 BEGIN
2923
2924 FEM_GL_POST_PROCESS_PKG.pv_sqlerrm := NULL;
2925
2926 pv_process_slice := '{p' || TO_CHAR(p_process_number) || ':s' ||
2927 TO_CHAR(p_slice_id) || '}';
2928
2929 FEM_ENGINES_PKG.Tech_Message
2930 (p_severity => pc_log_level_procedure,
2931 p_module => 'fem.plsql.xgl_eng.pds.' || pv_process_slice,
2932 p_app_name => 'FEM',
2933 p_msg_name => 'FEM_GL_POST_201',
2934 p_token1 => 'FUNC_NAME',
2935 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Process_Data_Slice',
2936 p_token2 => 'TIME',
2937 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
2938
2939 FEM_ENGINES_PKG.Tech_Message
2940 (p_severity => pc_log_level_statement,
2941 p_module => 'fem.plsql.xgl_eng.pds.' || pv_process_slice,
2942 p_app_name => 'FEM',
2943 p_msg_name => 'FEM_GL_POST_204',
2944 p_token1 => 'VAR_NAME',
2945 p_value1 => 'p_process_number',
2946 p_token2 => 'VAR_VAL',
2947 p_value2 => TO_CHAR(p_process_number));
2948
2949 FEM_ENGINES_PKG.Tech_Message
2950 (p_severity => pc_log_level_statement,
2951 p_module => 'fem.plsql.xgl_eng.pds.' || pv_process_slice,
2952 p_app_name => 'FEM',
2953 p_msg_name => 'FEM_GL_POST_204',
2954 p_token1 => 'VAR_NAME',
2955 p_value1 => 'p_slice_id',
2956 p_token2 => 'VAR_VAL',
2957 p_value2 => TO_CHAR(p_slice_id));
2958
2959 v_tot_rows_marked := 0;
2960 v_tot_cur_data_err_rows := 0;
2961 v_tot_prev_err_rows_reproc := 0;
2962 v_tot_posted_rows := 0;
2963
2964 IF NOT pv_pkg_variables_reset THEN
2965
2966 -- Reset the FEM_GL_POST_PROCESS_PKG package variables from the last
2967 -- 16 parameters to Process_Data_Slice, for use by this procedure and/
2968 -- or the procedures it calls, and populate the processing key info
2969 -- structure. The values will be available for the duration of the
2970 -- calling subrequst's session, i.e. for subsequent invocations of
2971 -- Process_Data_Slice, so this only needs to be done once per subrequest.
2972
2973 FEM_ENGINES_PKG.Tech_Message
2974 (p_severity => pc_log_level_event,
2975 p_module => 'fem.plsql.xgl_eng.lpv.' || pv_process_slice,
2976 p_msg_text => 'First data slice for subrequest; ' ||
2977 'resetting package variables from input parameters.');
2978
2979 FEM_GL_POST_PROCESS_PKG.pv_req_id :=
2980 TO_NUMBER(p_req_id);
2981 FEM_GL_POST_PROCESS_PKG.pv_exec_mode :=
2982 p_exec_mode;
2983 FEM_GL_POST_PROCESS_PKG.pv_rule_obj_id :=
2984 TO_NUMBER(p_rule_obj_id);
2985 FEM_GL_POST_PROCESS_PKG.pv_dataset_code :=
2986 TO_NUMBER(p_dataset_code);
2987 FEM_GL_POST_PROCESS_PKG.pv_cal_period_id :=
2988 TO_NUMBER(p_cal_period_id);
2989 FEM_GL_POST_PROCESS_PKG.pv_ledger_id :=
2990 TO_NUMBER(p_ledger_id);
2991 FEM_GL_POST_PROCESS_PKG.pv_qtd_ytd_code :=
2992 p_qtd_ytd_code;
2993 FEM_GL_POST_PROCESS_PKG.pv_entered_crncy_flag :=
2994 p_entered_crncy_flag;
2995 FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd :=
2996 p_cal_per_dim_grp_dsp_cd;
2997 FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date :=
2998 TO_DATE(p_cal_per_end_date, 'YYYY/MM/DD HH24:MI:SS');
2999 FEM_GL_POST_PROCESS_PKG.pv_gl_per_number :=
3000 TO_NUMBER(p_gl_per_number);
3001 FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd :=
3002 p_ledger_dsp_cd;
3003 FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd :=
3004 p_budget_dsp_cd;
3005 FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd :=
3006 p_enc_type_dsp_cd;
3007 FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd :=
3008 p_ds_balance_type_cd;
3009
3010 pv_schema_name := p_schema_name;
3011
3012 Log_Pkg_Variables;
3013
3014 -- Get the Processing Key information
3015
3016 FEM_GL_POST_PROCESS_PKG.pv_sqlerrm := NULL;
3017
3018 FEM_GL_POST_PROCESS_PKG.Get_Proc_Key_Info
3019 (p_process_slice => pv_process_slice,
3020 x_completion_code => v_compl_code);
3021
3022 IF v_compl_code = 2 THEN
3023 RAISE FEMXGL_fatal_err;
3024 END IF;
3025
3026 pv_pkg_variables_reset := TRUE;
3027
3028 END IF;
3029
3030 pv_data_slice_predicate := p_data_slice_predicate;
3031 pv_partition_clause := p_eng_sql;
3032
3033 FEM_ENGINES_PKG.Tech_Message
3034 (p_severity => pc_log_level_event,
3035 p_module => 'fem.plsql.xgl_eng.pds.' || pv_process_slice,
3036 p_msg_text => 'pv_data_slice_predicate: ' || pv_data_slice_predicate);
3037
3038 FEM_ENGINES_PKG.Tech_Message
3039 (p_severity => pc_log_level_event,
3040 p_module => 'fem.plsql.xgl_eng.pc.' || pv_process_slice,
3041 p_msg_text => 'pv_partition_clause: ' || pv_partition_clause);
3042
3043 -- Set the unique Request ID for this data slice:
3044 pv_req_id_slice := p_req_id + p_slice_id / 100000;
3045
3046 FEM_ENGINES_PKG.Tech_Message
3047 (p_severity => pc_log_level_statement,
3048 p_module => 'fem.plsql.xgl_eng.pds.' || pv_process_slice,
3049 p_app_name => 'FEM',
3050 p_msg_name => 'FEM_GL_POST_204',
3051 p_token1 => 'VAR_NAME',
3052 p_value1 => 'pv_req_id_slice',
3053 p_token2 => 'VAR_VAL',
3054 p_value2 => TO_CHAR(pv_req_id_slice));
3055
3056 -- -------------------------------------------------------------------
3057 -- Comment by SKUNG for Initial 5i Release:
3058 -- First, we will process all snapshot rows. This is because
3059 -- we need to ignore the LOAD_SET_ID column and process all
3060 -- snapshot rows in a single pass.
3061 -- For Snapshot loads, this pass will process everything relevant.
3062 -- For Incremental and Error Reprocessing loads, this pass will
3063 -- process all Snapshot rows marked with errors from a previous
3064 -- run. This is because we need to post all Snapshot rows before
3065 -- posting the incremental rows.
3066 --
3067 -- In this pass, rows will be marked for processing according
3068 -- to the following rules:
3069 -- 1) Snapshot loads
3070 -- All snapshot rows for this ledger/Cal. Period/Dataset
3071 -- will be picked up, regardless of whether it is a brand
3072 -- new record or whether it is marked with an error from
3073 -- previous runs.
3074 -- 2) Incremental and Error Reprocessing loads
3075 -- All snapshot rows for this ledger/Cal. Period/Dataset
3076 -- marked with errors will be picked up.
3077 --
3078 -- This pass is done by calling sub-routine Post_Cycle_Handler
3079 -- when package variable pv_snapshot_rows_done is FALSE
3080 -- -------------------------------------------------------------------
3081
3082 pv_snapshot_rows_done := FALSE;
3083 pv_incr_marking_sql_done := FALSE;
3084
3085 v_rows_marked := 0;
3086 v_posted_rows := 0;
3087 v_cur_data_err_rows := 0;
3088 v_prev_err_rows_reproc := 0;
3089
3090 Post_Cycle_Handler
3091 (p_load_set_id => 1,
3092 x_completion_code => v_compl_code,
3093 x_rows_marked => v_rows_marked,
3094 x_posted_row_num => v_posted_rows,
3095 x_prev_err_rows_reproc => v_prev_err_rows_reproc,
3096 x_cur_data_err_rows => v_cur_data_err_rows);
3097
3098 IF v_compl_code = 2 THEN
3099 RAISE FEMXGL_fatal_err;
3100 END IF;
3101
3102 pv_snapshot_rows_done := TRUE;
3103
3104 v_tot_rows_marked := v_tot_rows_marked + v_rows_marked;
3105 v_tot_posted_rows := v_tot_posted_rows + v_posted_rows;
3106 v_tot_prev_err_rows_reproc :=
3107 v_tot_prev_err_rows_reproc + v_prev_err_rows_reproc;
3108 v_tot_cur_data_err_rows :=
3109 v_tot_cur_data_err_rows + v_cur_data_err_rows;
3110
3111 -- ------------------------------------------------------------
3112 -- Delete successfully posted rows (snapshot) from
3113 -- FEM_BAL_INTERFACE_T.
3114 -- ------------------------------------------------------------
3115
3116 DELETE FROM fem_bal_interface_t
3117 WHERE posting_request_id = pv_req_id_slice
3118 AND posting_error_code is NULL
3119 AND load_method_code = 'S';
3120
3121 FEM_ENGINES_PKG.Tech_Message
3122 (p_severity => pc_log_level_statement,
3123 p_module => 'fem.plsql.xgl_eng.pds.' ||
3124 pv_process_slice || '.ss_rows_del',
3125 p_app_name => 'FEM',
3126 p_msg_name => 'FEM_GL_POST_218',
3127 p_token1 => 'NUM',
3128 p_value1 => TO_CHAR(SQL%ROWCOUNT),
3129 p_token2 => 'TABLE',
3130 p_value2 => 'FEM_BAL_INTERFACE_T');
3131
3132 -- ------------------------------------------------------------
3133 -- Truncate FEM_BAL_POST_INTERIM_GT for the next set
3134 -- ------------------------------------------------------------
3135
3136 FEM_ENGINES_PKG.Tech_Message
3137 (p_severity => pc_log_level_statement,
3138 p_module => 'fem.plsql.xgl_eng.pds.' ||
3139 pv_process_slice || '.ss_trunc',
3140 p_msg_text =>
3141 'Truncating FEM_BAL_POST_INTERIM_GT for the next set');
3142
3143 EXECUTE IMMEDIATE
3144 'TRUNCATE TABLE ' || pv_schema_name || '.fem_bal_post_interim_gt';
3145
3146 COMMIT;
3147
3148 -- ---------------------------------------------------------
3149 -- Comment by SKUNG for Initial 5i Release:
3150 -- Now for Incremental or Error Reprocessing loads, start
3151 -- posting records one load set at a time
3152 -- ---------------------------------------------------------
3153
3154 IF (FEM_GL_POST_PROCESS_PKG.pv_exec_mode <> 'S') THEN
3155
3156 -- -------------------------------------------------------------------
3157 -- Open the appropriate cursor based on execution mode to get load
3158 -- sets for processing.
3159 -- NOTE: We really should include the data slice predicate in these
3160 -- queries, to eliminate all the load sets that do not have
3161 -- any data for the current data slice. This will reduce the
3162 -- number of loop iterations. The reduction can be significant
3163 -- depending on the data slicing columns chosen and other
3164 -- factors. However, the statements are using bind variables,
3165 -- which enables statement reuse in the SGA. But until we
3166 -- implement the Bind Variables Push method in the MP Framework,
3167 -- we can't include the data slice predicate without making
3168 -- the statement different for each data slice.
3169 -- These cursor statements can also be made more efficient by
3170 -- implementing separate code paths for actuals, budget, and
3171 -- encumbrance type.
3172 -- -------------------------------------------------------------------
3173
3174 -- IF (NOT GetLoadSet_Cursor%ISOPEN) THEN Why do we need this?
3175
3176 IF (FEM_GL_POST_PROCESS_PKG.pv_exec_mode = 'I') THEN
3177
3178 v_incr_cursor_stmt :=
3179 'SELECT DISTINCT load_set_id ' ||
3180 'FROM fem_bal_interface_t ' ||
3181 'WHERE load_method_code = ''I'' ' ||
3182 'AND cal_per_dim_grp_display_code = :cal_per_dim_grp_dsp_cd ' ||
3183 'AND cal_period_end_date = :cal_per_end_date ' ||
3184 'AND cal_period_number = :cal_gl_per_num ' ||
3185 'AND ledger_display_code = :ledger_dsp_cd ' ||
3186 'AND (budget_display_code = :budget_dsp_cd ' ||
3187 'OR :budget_dsp_cd is NULL) ' ||
3188 'AND (encumbrance_type_code = :enc_type_dsp_cd ' ||
3189 'OR :enc_type_dsp_cd is NULL) ' ||
3190 'ORDER BY load_set_id ASC';
3191
3192 FEM_ENGINES_PKG.Tech_Message
3193 (p_severity => pc_log_level_statement,
3194 p_module => 'fem.plsql.xgl_eng.pds.' ||
3195 pv_process_slice || '.incr_crs_stmt',
3196 p_app_name => 'FEM',
3197 p_msg_name => 'FEM_GL_POST_204',
3198 p_token1 => 'VAR_NAME',
3199 p_value1 => 'v_incr_cursor_stmt',
3200 p_token2 => 'VAR_VAL',
3201 p_value2 => v_incr_cursor_stmt);
3202
3203 OPEN GetLoadSet_Cursor FOR v_incr_cursor_stmt
3204 USING FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd,
3205 TO_DATE(TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date, 'YYYY/MM/DD HH24:MI:SS'),
3206 'YYYY/MM/DD HH24:MI:SS'),
3207 FEM_GL_POST_PROCESS_PKG.pv_gl_per_number,
3208 FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd,
3209 FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd,
3210 FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd,
3211 FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd,
3212 FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd;
3213
3214 ELSIF (FEM_GL_POST_PROCESS_PKG.pv_exec_mode = 'E') THEN
3215
3216 v_err_cursor_stmt :=
3217 'SELECT DISTINCT load_set_id ' ||
3218 'FROM fem_bal_interface_t ' ||
3219 'WHERE posting_error_code is NOT NULL ' ||
3220 'AND load_method_code = ''I'' ' ||
3221 'AND cal_per_dim_grp_display_code = :cal_per_dim_grp_dsp_cd ' ||
3222 'AND cal_period_end_date = :cal_per_end_date ' ||
3223 'AND cal_period_number = :cal_gl_per_num ' ||
3224 'AND ledger_display_code = :ledger_dsp_cd ' ||
3225 'AND (budget_display_code = :budget_dsp_cd ' ||
3226 'OR :budget_dsp_cd is NULL) ' ||
3227 'AND (encumbrance_type_code = :enc_type_dsp_cd ' ||
3228 'OR :enc_type_dsp_cd is NULL) ' ||
3229 'ORDER BY load_set_id ASC';
3230
3231 FEM_ENGINES_PKG.Tech_Message
3232 (p_severity => pc_log_level_statement,
3233 p_module => 'fem.plsql.xgl_eng.pds.' ||
3234 pv_process_slice || '.err_crs_stmt',
3235 p_msg_text => 'v_err_cursor_stmt: ' || v_err_cursor_stmt);
3236
3237 OPEN GetLoadSet_Cursor FOR v_err_cursor_stmt
3238 USING FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_grp_dsp_cd,
3239 TO_DATE(TO_CHAR(FEM_GL_POST_PROCESS_PKG.pv_cal_per_end_date, 'YYYY/MM/DD HH24:MI:SS'),
3240 'YYYY/MM/DD HH24:MI:SS'),
3241 FEM_GL_POST_PROCESS_PKG.pv_gl_per_number,
3242 FEM_GL_POST_PROCESS_PKG.pv_ledger_dsp_cd,
3243 FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd,
3244 FEM_GL_POST_PROCESS_PKG.pv_budget_dsp_cd,
3245 FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd,
3246 FEM_GL_POST_PROCESS_PKG.pv_enc_type_dsp_cd;
3247 END IF;
3248
3249 -- END IF; If cursor is open -- why needed?
3250
3251 -- -------------------------------------------------------------------
3252 -- Post incremental rows one load set at a time, in load set order.
3253 -- -------------------------------------------------------------------
3254
3255 LOOP
3256
3257 FETCH GetLoadSet_Cursor INTO v_curr_load_set_id;
3258
3259 EXIT WHEN GetLoadSet_Cursor%NOTFOUND OR
3260 GetLoadSet_Cursor%NOTFOUND IS NULL;
3261
3262 FEM_ENGINES_PKG.Tech_Message
3263 (p_severity => pc_log_level_statement,
3264 p_module => 'fem.plsql.xgl_eng.pds.' ||
3265 pv_process_slice || '.load_set',
3266 p_app_name => 'FEM',
3267 p_msg_name => 'FEM_GL_POST_210',
3268 p_token1 => 'LOAD_SET_ID',
3269 p_value1 => TO_CHAR(v_curr_load_set_id));
3270
3271 -- ------------------------------------------------------
3272 -- Process incremental rows for current load set
3273 -- ------------------------------------------------------
3274
3275 v_rows_marked := 0;
3276 v_posted_rows := 0;
3277 v_cur_data_err_rows := 0;
3278 v_prev_err_rows_reproc := 0;
3279
3280 Post_Cycle_Handler
3281 (p_load_set_id => v_curr_load_set_id,
3282 x_completion_code => v_compl_code,
3283 x_rows_marked => v_rows_marked,
3284 x_posted_row_num => v_posted_rows,
3285 x_prev_err_rows_reproc => v_prev_err_rows_reproc,
3286 x_cur_data_err_rows => v_cur_data_err_rows);
3287
3288 IF v_compl_code = 2 THEN
3289 RAISE FEMXGL_fatal_err;
3290 END IF;
3291
3292 pv_incr_marking_sql_done := TRUE;
3293
3294 v_tot_rows_marked := v_tot_rows_marked + v_rows_marked;
3295 v_tot_posted_rows := v_tot_posted_rows + v_posted_rows;
3296 v_tot_prev_err_rows_reproc :=
3297 v_tot_prev_err_rows_reproc + v_prev_err_rows_reproc;
3298 v_tot_cur_data_err_rows :=
3299 v_tot_cur_data_err_rows + v_cur_data_err_rows;
3300
3301 -- ------------------------------------------------------------
3302 -- Delete successfully posted rows (incremental) from
3303 -- FEM_BAL_INTERFACE_T.
3304 -- ------------------------------------------------------------
3305
3306 DELETE FROM fem_bal_interface_t
3307 WHERE posting_request_id = pv_req_id_slice
3308 AND posting_error_code is NULL
3309 AND load_method_code = 'I'
3310 AND load_set_id = v_curr_load_set_id;
3311
3312 FEM_ENGINES_PKG.Tech_Message
3313 (p_severity => pc_log_level_statement,
3314 p_module => 'fem.plsql.xgl_eng.pds.' ||
3315 pv_process_slice || '.incr_rows_del',
3316 p_app_name => 'FEM',
3317 p_msg_name => 'FEM_GL_POST_218',
3318 p_token1 => 'NUM',
3319 p_value1 => TO_CHAR(SQL%ROWCOUNT),
3320 p_token2 => 'TABLE',
3321 p_value2 => 'FEM_BAL_INTERFACE_T');
3322
3323 -- ------------------------------------------------------------
3324 -- Truncate FEM_BAL_POST_INTERIM_GT for the next set
3325 -- ------------------------------------------------------------
3326
3327 FEM_ENGINES_PKG.Tech_Message
3328 (p_severity => pc_log_level_statement,
3329 p_module => 'fem.plsql.xgl_eng.pds.' ||
3330 pv_process_slice || '.incr_trunc',
3331 p_msg_text =>
3332 'Truncating FEM_BAL_POST_INTERIM_GT for the next set');
3333
3334 EXECUTE IMMEDIATE
3335 'TRUNCATE TABLE ' || pv_schema_name || '.fem_bal_post_interim_gt';
3336
3337 COMMIT;
3338
3339 END LOOP; -- For Load set looping
3340
3341 CLOSE GetLoadSet_Cursor;
3342
3343 END IF; -- IF (FEM_GL_POST_PROCESS_PKG.pv_exec_mode <> 'S')...
3344
3345 x_slice_errors_reprocessed := v_tot_prev_err_rows_reproc;
3346 x_slice_output_rows := v_tot_posted_rows;
3347 x_slice_errors_reported := v_tot_cur_data_err_rows;
3348
3349 IF v_tot_posted_rows = 0 THEN
3350
3351 IF v_tot_rows_marked = 0 THEN
3352 -- Note: this should never happen!
3353 x_slice_msg := 'NO_DATA';
3354 ELSIF v_tot_cur_data_err_rows = v_rows_marked THEN
3355 x_slice_msg := 'ALL_ERRORS';
3356 END IF;
3357
3358 x_slice_status_cd := 1;
3359
3360 ELSIF v_tot_cur_data_err_rows > 0 THEN
3361
3362 x_slice_msg := 'SOME_ERRORS';
3363 x_slice_status_cd := 1;
3364
3365 ELSE
3366
3367 x_slice_msg := NULL;
3368 x_slice_status_cd := 0;
3369
3370 END IF;
3371
3372 FEM_ENGINES_PKG.Tech_Message
3373 (p_severity => pc_log_level_procedure,
3374 p_module => 'fem.plsql.xgl_eng.pds',
3375 p_app_name => 'FEM',
3376 p_msg_name => 'FEM_GL_POST_202',
3377 p_token1 => 'FUNC_NAME',
3378 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Process_Data_Slice',
3379 p_token2 => 'TIME',
3380 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
3381
3382 EXCEPTION
3383
3384 WHEN FEMXGL_fatal_err THEN
3385
3386 -- Messages have already been logged where they occurred
3387
3388 -- Set the OUT parameters
3389
3390 x_slice_errors_reprocessed := v_tot_prev_err_rows_reproc;
3391 x_slice_output_rows := v_tot_posted_rows;
3392 x_slice_errors_reported := v_tot_cur_data_err_rows;
3393 x_slice_msg := FEM_GL_POST_PROCESS_PKG.pv_sqlerrm;
3394 x_slice_status_cd := 2;
3395
3396 -- Reset error code for remaing rows not processed which were
3397 -- errors before, so they'll be reprocessed by the next run.
3398
3399 UPDATE fem_bal_interface_t
3400 SET posting_error_code = 'FEM_GL_POST_TO_BE_REPROCESSED'
3401 WHERE posting_request_id = pv_req_id_slice
3402 AND posting_error_code is NULL
3403 AND previous_error_flag = 'Y';
3404
3405 COMMIT;
3406
3407 FEM_ENGINES_PKG.Tech_Message
3408 (p_severity => pc_log_level_procedure,
3409 p_module => 'fem.plsql.xgl_eng.pds.' ||
3410 pv_process_slice || '.fatal_err',
3411 p_app_name => 'FEM',
3412 p_msg_name => 'FEM_GL_POST_203',
3413 p_token1 => 'FUNC_NAME',
3414 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Process_Data_Slice',
3415 p_token2 => 'TIME',
3416 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
3417
3418 WHEN OTHERS THEN
3419
3420 IF FEM_GL_POST_PROCESS_PKG.pv_sqlerrm IS NULL THEN
3421 FEM_GL_POST_PROCESS_PKG.pv_sqlerrm := SQLERRM;
3422 END IF;
3423
3424 ROLLBACK;
3425
3426 -- Log the Oracle error message to FND_LOG with the
3427 -- "unexpected exception" severity level.
3428
3429 FEM_ENGINES_PKG.Tech_Message
3430 (p_severity => pc_log_level_unexpected,
3431 p_module => 'fem.plsql.xgl_eng.pds.' ||
3432 pv_process_slice || '.unexpected_exception',
3433 p_msg_text => FEM_GL_POST_PROCESS_PKG.pv_sqlerrm);
3434
3435 -- Log the Oracle error message to the Concurrent Request Log.
3436
3437 FEM_ENGINES_PKG.User_Message
3438 (p_msg_text => FEM_GL_POST_PROCESS_PKG.pv_sqlerrm);
3439
3440 -- Set the OUT parameters
3441
3442 x_slice_errors_reprocessed := v_tot_prev_err_rows_reproc;
3443 x_slice_output_rows := v_tot_posted_rows;
3444 x_slice_errors_reported := v_tot_cur_data_err_rows;
3445 x_slice_msg := FEM_GL_POST_PROCESS_PKG.pv_sqlerrm;
3446 x_slice_status_cd := 2;
3447
3448 -- Reset error code for remaing rows not processed which were
3449 -- errors before, so they'll be reprocessed by the next run.
3450
3451 UPDATE fem_bal_interface_t
3452 SET posting_error_code = 'FEM_GL_POST_TO_BE_REPROCESSED'
3453 WHERE posting_request_id = pv_req_id_slice
3454 AND posting_error_code is NULL
3455 AND previous_error_flag = 'Y';
3456
3457 COMMIT;
3458
3459 FEM_ENGINES_PKG.Tech_Message
3460 (p_severity => pc_log_level_procedure,
3461 p_module => 'fem.plsql.xgl_eng.pds.' ||
3462 pv_process_slice || '.unexpected_exception',
3463 p_app_name => 'FEM',
3464 p_msg_name => 'FEM_GL_POST_203',
3465 p_token1 => 'FUNC_NAME',
3466 p_value1 => 'FEM_XGL_POST_ENGINE_PKG.Process_Data_Slice',
3467 p_token2 => 'TIME',
3468 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
3469
3470 END Process_Data_Slice;
3471 -- =======================================================================
3472
3473 END FEM_XGL_POST_ENGINE_PKG;