[Home] [Help]
PACKAGE BODY: APPS.FEM_GL_POST_BAL_PKG
Source
1 PACKAGE BODY FEM_GL_POST_BAL_PKG AS
2 /* $Header: fem_gl_post_bal.plb 120.3 2005/12/02 19:56:20 mikeward ship $ */
3
4 FUNCTION Get_Next_Creation_Row_Seq RETURN NUMBER IS
5 seq_number NUMBER;
6 BEGIN
7 SELECT fem_gl_post_creation_row_s.nextval
8 INTO seq_number
9 FROM DUAL;
10
11 RETURN seq_number;
12 END Get_Next_Creation_Row_Seq;
13
14
15 -- =========================================================================
16 -- Procedure
17 -- Post_Fem_Balances
18 --
19 -- Purpose
20 -- This routine post data from FEM_BAL_POST_INTERIM_GT to FEM_BALANCES.
21 --
22 -- If execution mode is Snapshot, this procedure will do an insert into
23 -- FEM_BALANCES from FEM_BAL_POST_INTERIM_GT.
24 --
25 -- If execution mode is Incremental, this procedure will do a merge into
26 -- FEM_BALANCES from FEM_BAL_POST_INTERIM_GT. It'll update the rows
27 -- if the processing keys between the two tables match. Otherwise, it'll
28 -- do an insert into FEM_BALANCES.
29 --
30 -- History
31 -- 10-15-2003 W Wong Created
32 --
33 -- Arguments
34 -- p_execution_mode Execution Mode
35 -- p_process_slice A character string concatenation of the MP FW
36 -- subrequest process number and the data slice id
37 -- for distinguishing messages logged by different
38 -- executions of FEM_XGL_ENGINE_PKG.Process_Data_Slice.
39 -- x_rows_posted Total number of rows inserted/merged into
40 -- FEM_BALANCES
41 -- x_completion_code 0 for success, 2 for failure
42 -- ==========================================================================
43
44 PROCEDURE Post_Fem_Balances
45 (p_execution_mode IN VARCHAR2,
46 p_process_slice IN VARCHAR2,
47 x_rows_posted IN OUT NOCOPY NUMBER,
48 x_completion_code IN OUT NOCOPY NUMBER) IS
49 BEGIN
50 Post_Fem_Balances(
51 p_execution_mode => p_execution_mode,
52 p_process_slice => p_process_slice,
53 p_load_type => 'XGL',
54 p_maintain_qtd => 'N',
55 p_bsv_range_low => NULL,
56 p_bsv_range_high => NULL,
57 x_rows_posted => x_rows_posted,
58 x_completion_code => x_completion_code);
59 END Post_Fem_Balances;
60
61
62
63
64 -- =========================================================================
65 -- Procedure
66 -- Post_Fem_Balances
67 --
68 -- Purpose
69 -- This routine post data from FEM_BAL_POST_INTERIM_GT to FEM_BALANCES.
70 --
71 -- If execution mode is Snapshot, this procedure will do an insert into
72 -- FEM_BALANCES from FEM_BAL_POST_INTERIM_GT.
73 --
74 -- If execution mode is Incremental, this procedure will do a merge into
75 -- FEM_BALANCES from FEM_BAL_POST_INTERIM_GT. It'll update the rows
76 -- if the processing keys between the two tables match. Otherwise, it'll
77 -- do an insert into FEM_BALANCES.
78 --
79 -- History
80 -- 10-15-2003 W Wong Created
81 --
82 -- Arguments
83 -- p_execution_mode Execution Mode
84 -- p_process_slice A character string concatenation of the MP FW
85 -- subrequest process number and the data slice id
86 -- for distinguishing messages logged by different
87 -- executions of FEM_XGL_ENGINE_PKG.Process_Data_Slice.
88 -- p_bsv_range_low Low value for the range of balancing segment
89 -- values to be filtered in
90 -- p_bsv_range_high High value for the range of balancing segment
91 -- values to be filtered in
92 -- x_rows_posted Total number of rows inserted/merged into
93 -- FEM_BALANCES
94 -- x_completion_code 0 for success, 2 for failure
95 -- ==========================================================================
96
97 PROCEDURE Post_Fem_Balances
98 (p_execution_mode IN VARCHAR2,
99 p_process_slice IN VARCHAR2,
100 p_load_type IN VARCHAR2,
101 p_maintain_qtd IN VARCHAR2,
102 p_bsv_range_low IN VARCHAR2,
103 p_bsv_range_high IN VARCHAR2,
104 x_rows_posted IN OUT NOCOPY NUMBER,
105 x_completion_code IN OUT NOCOPY NUMBER) IS
106
107 DATA_CORRUPTION EXCEPTION;
108 PROC_KEY_ERROR EXCEPTION;
109
110 v_log_level_1 NUMBER;
111 v_log_level_2 NUMBER;
112 v_log_level_3 NUMBER;
113 v_log_level_4 NUMBER;
114 v_log_level_5 NUMBER;
115 v_log_level_6 NUMBER;
116
117 v_sql_stmt VARCHAR2(8000);
118 v_sql_stmt_2 VARCHAR2(24000);
119 v_key_stmt VARCHAR2(4000);
120 v_first_time VARCHAR2(1);
121 v_interim_row_count NUMBER;
122 v_count NUMBER;
123
124 pv_req_id NUMBER;
125 pv_rule_obj_id NUMBER;
126 pv_proc_key_dim_num NUMBER;
127 pv_ledger_id NUMBER;
128 pv_col VARCHAR2(30);
129
130 v_merge_select VARCHAR2(4000);
131 v_req_text VARCHAR2(100);
132
133 v_na_dim_id NUMBER;
134 v_xat_dim_id NUMBER;
135
136 v_na_xat_attr_id NUMBER;
137 v_na_xat_v_id NUMBER;
138 v_xat_bat_attr_id NUMBER;
139 v_xat_bat_v_id NUMBER;
140
141 v_cp_period_num_attr_id NUMBER;
142 v_cp_period_num_v_id NUMBER;
143 v_cp_year_attr_id NUMBER;
144 v_cp_year_v_id NUMBER;
145
146 v_ps_name VARCHAR2(100);
147 v_period_type VARCHAR2(100);
148
149 v_error_code NUMBER;
150
151 v_completion_code NUMBER;
152 BEGIN
153
154 v_log_level_1 := fnd_log.level_statement;
155 v_log_level_2 := fnd_log.level_procedure;
156 v_log_level_3 := fnd_log.level_event;
157 v_log_level_4 := fnd_log.level_exception;
158 v_log_level_5 := fnd_log.level_error;
159 v_log_level_6 := fnd_log.level_unexpected;
160
161 FEM_ENGINES_PKG.Tech_Message
162 (p_severity => v_log_level_2,
163 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
164 p_msg_text => 'BEGIN FEM_GL_POST_BAL_PKG.Post_FEM_Balances');
165
166 IF p_load_type = 'OGL' THEN
167 fem_gl_post_process_pkg.get_proc_key_info(p_process_slice, v_completion_code);
168 IF v_completion_code = 2 THEN
169 x_completion_code := 2;
170 raise PROC_KEY_ERROR;
171 END IF;
172 END IF;
173 -----------------------------------------------------------------------
174 -- Retrive package variables from FEM_GL_POST_PROCESS_PKG and find
175 -- out some attribute information before we insert/merge data into
176 -- the FEM_BALANCES table
177 -----------------------------------------------------------------------
178 pv_req_id := FEM_GL_POST_PROCESS_PKG.pv_req_id;
179 pv_rule_obj_id := FEM_GL_POST_PROCESS_PKG.pv_rule_obj_id;
180 pv_proc_key_dim_num := FEM_GL_POST_PROCESS_PKG.pv_proc_key_dim_num;
181 pv_ledger_id := FEM_GL_POST_PROCESS_PKG.pv_ledger_id;
182
183 -----------------------------------------------------------------------
184 -- IF the executaion mode is Snapshot mode, we will insert data from
185 -- the interim table to FEM_BALANCES.
186 -- ELSE if the execution mode is Incremental, we will try to merge
187 -- data into FEM_BALANCES if the processing keys between the interim
188 -- table and FEM_BALANCES matches. Otherwise we'll insert the data.
189 -----------------------------------------------------------------------
190
191 IF (p_execution_mode = 'S') THEN
192
193 IF p_load_type = 'XGL' THEN
194 v_req_text := ' :pv_req_id, ';
195 ELSE
196 v_req_text := ' param.request_id, ';
197 END IF;
198
199 -- Insert data from FEM_BAL_POST_INTERIM_GT into FEM_BALANCES
200 v_sql_stmt :=
201 'INSERT INTO fem_balances '||
202 ' ( '||
203 ' dataset_code, '||
204 ' cal_period_id, '||
205 ' creation_row_sequence, '||
206 ' source_system_code, '||
207 ' ledger_id, '||
208 ' company_cost_center_org_id, '||
209 ' currency_code, '||
210 ' currency_type_code, '||
211 ' financial_elem_id, '||
212 ' product_id, '||
213 ' natural_account_id, '||
214 ' channel_id, '||
215 ' line_item_id, '||
216 ' project_id, '||
217 ' customer_id, '||
218 ' intercompany_id, '||
219 ' entity_id, '||
220 ' task_id, '||
221 ' user_dim1_id, '||
222 ' user_dim2_id, '||
223 ' user_dim3_id, '||
224 ' user_dim4_id, '||
225 ' user_dim5_id, '||
226 ' user_dim6_id, '||
227 ' user_dim7_id, '||
228 ' user_dim8_id, '||
229 ' user_dim9_id, '||
230 ' user_dim10_id, '||
231 ' created_by_request_id, '||
232 ' created_by_object_id, '||
233 ' last_updated_by_request_id, '||
234 ' last_updated_by_object_id, '||
235 ' xtd_balance_e, '||
236 ' xtd_balance_f, '||
237 ' ytd_balance_e, '||
238 ' ytd_balance_f, '||
239 ' qtd_balance_e, '||
240 ' qtd_balance_f, '||
241 ' ptd_debit_balance_e, '||
242 ' ptd_credit_balance_e, '||
243 ' ytd_debit_balance_e, '||
244 ' ytd_credit_balance_e) '||
245 ' SELECT '||
246 ' bpi.dataset_code, '||
247 ' bpi.cal_period_id, '||
248 ' fem_gl_post_bal_pkg.get_next_creation_row_seq, '||
249 ' bpi.source_system_code, '||
250 ' bpi.ledger_id, '||
251 ' bpi.company_cost_center_org_id, '||
252 ' bpi.currency_code, '||
253 ' bpi.currency_type_code, '||
254 ' bpi.financial_elem_id, '||
255 ' bpi.product_id, '||
256 ' bpi.natural_account_id, '||
257 ' bpi.channel_id, '||
258 ' bpi.line_item_id, '||
259 ' bpi.project_id, '||
260 ' bpi.customer_id, '||
261 ' bpi.intercompany_id, '||
262 ' bpi.entity_id, '||
263 ' bpi.task_id, '||
264 ' bpi.user_dim1_id, '||
265 ' bpi.user_dim2_id, '||
266 ' bpi.user_dim3_id, '||
267 ' bpi.user_dim4_id, '||
268 ' bpi.user_dim5_id, '||
269 ' bpi.user_dim6_id, '||
270 ' bpi.user_dim7_id, '||
271 ' bpi.user_dim8_id, '||
272 ' bpi.user_dim9_id, '||
273 ' bpi.user_dim10_id, ' ||
274 v_req_text ||
275 ' :pv_rule_obj_id, '||
276 v_req_text ||
277 ' :pv_rule_obj_id, '||
278 ' sum(bpi.xtd_balance_e), '||
279 ' sum(bpi.xtd_balance_f), '||
280 ' sum(bpi.ytd_balance_e), '||
281 ' sum(bpi.ytd_balance_f), '||
282 ' sum(bpi.qtd_balance_e), '||
283 ' sum(bpi.qtd_balance_f), '||
284 ' sum(bpi.ptd_debit_balance_e), '||
285 ' sum(bpi.ptd_credit_balance_e), '||
286 ' sum(bpi.ytd_debit_balance_e), '||
287 ' sum(bpi.ytd_credit_balance_e) '||
288 ' FROM fem_bal_post_interim_gt bpi';
289
290 IF p_load_type = 'OGL' THEN
291 v_sql_stmt := v_sql_stmt || ', fem_intg_exec_params_gt param' ||
292 ', gl_code_combinations cc';
293 END IF;
294
295 v_sql_stmt := v_sql_stmt ||
296 ' WHERE bpi.posting_error_flag = ''N'' ';
297
298
299 IF p_load_type = 'OGL' THEN
300 v_sql_stmt := v_sql_stmt ||
301 ' AND param.output_dataset_code = bpi.dataset_code ' ||
302 ' AND param.cal_period_id = bpi.cal_period_id ' ||
303 ' AND param.error_code IS NULL ' ||
304 ' AND param.request_id IS NOT NULL ' ||
305 ' AND cc.code_combination_id = bpi.code_combination_id ' ||
306 ' AND NOT EXISTS ' ||
307 ' (SELECT 1 ' ||
308 ' FROM FEM_INTG_DELTA_LOADS dl ' ||
309 ' WHERE dl.ledger_id = bpi.ledger_id ' ||
310 ' AND dl.dataset_code = bpi.dataset_code ' ||
311 ' AND dl.cal_period_id = bpi.cal_period_id ' ||
312 ' AND dl.delta_run_id = bpi.delta_run_id ' ||
313 ' AND dl.balance_seg_value = cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || ' ' ||
314 ' AND dl.loaded_flag = ''N'')';
315 END IF;
316
317 v_sql_stmt := v_sql_stmt ||
318 ' GROUP BY ' ||
319 ' bpi.dataset_code, '||
320 ' bpi.cal_period_id, '||
321 ' bpi.source_system_code, '||
322 ' bpi.ledger_id, '||
323 ' bpi.company_cost_center_org_id, '||
324 ' bpi.currency_code, '||
325 ' bpi.currency_type_code, '||
326 ' bpi.financial_elem_id, '||
327 ' bpi.product_id, '||
328 ' bpi.natural_account_id, '||
329 ' bpi.channel_id, '||
330 ' bpi.line_item_id, '||
331 ' bpi.project_id, '||
332 ' bpi.customer_id, '||
333 ' bpi.intercompany_id, '||
334 ' bpi.entity_id, '||
335 ' bpi.task_id, '||
336 ' bpi.user_dim1_id, '||
337 ' bpi.user_dim2_id, '||
338 ' bpi.user_dim3_id, '||
339 ' bpi.user_dim4_id, '||
340 ' bpi.user_dim5_id, '||
341 ' bpi.user_dim6_id, '||
342 ' bpi.user_dim7_id, '||
343 ' bpi.user_dim8_id, '||
344 ' bpi.user_dim9_id, '||
345 ' bpi.user_dim10_id';
346
347 IF p_load_type = 'OGL' THEN
348 v_sql_stmt := v_sql_stmt || ', param.request_id';
349 END IF;
350
351
352
353 FOR iterator IN 1..trunc((length(v_sql_stmt)+1499)/1500) LOOP
354 FEM_ENGINES_PKG.Tech_Message
355 (p_severity => v_log_level_2,
356 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
357 p_app_name => 'FEM',
358 p_msg_name => 'FEM_GL_POST_204',
359 p_token1 => 'VAR_NAME',
360 p_value1 => 'v_sql_stmt: ' || iterator,
361 p_token2 => 'VAR_VAL',
362 p_value2 => substr(v_sql_stmt, iterator*1500-1499, 1500));
363 END LOOP;
364
365 IF p_load_type = 'XGL' THEN
366 EXECUTE IMMEDIATE v_sql_stmt
367 USING pv_req_id, pv_rule_obj_id, pv_req_id, pv_rule_obj_id;
368 ELSE
369 EXECUTE IMMEDIATE v_sql_stmt
370 USING pv_rule_obj_id, pv_rule_obj_id;
371 END IF;
372
373 x_rows_posted := SQL%ROWCOUNT;
374
375 FEM_ENGINES_PKG.Tech_Message
376 (p_severity => v_log_level_1,
377 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
378 p_app_name => 'FEM',
379 p_msg_name => 'FEM_GL_POST_216',
380 p_token1 => 'NUM',
381 p_value1 => TO_CHAR(x_rows_posted),
382 p_token2 => 'TABLE',
383 p_value2 => 'FEM_BALANCES');
384
385 ELSE
386 -- Find out primary keys for FEM_BALANCES and construct the ON clause for
387 -- the Merge statement
388 v_first_time := 'Y';
389 v_key_stmt := '';
390
391 FOR v IN 1..pv_proc_key_dim_num LOOP
392 pv_col := FEM_GL_POST_PROCESS_PKG.pv_proc_keys(v).dim_col_name;
393
394 IF (v_first_time = 'N') THEN
395 v_key_stmt := v_key_stmt || ' AND ';
396 END IF;
397
398 IF (pv_col <> 'CREATED_BY_OBJECT_ID') THEN
399 v_key_stmt := v_key_stmt || 'bal.' || pv_col || ' = int.' || pv_col;
400 ELSE
401 v_key_stmt := v_key_stmt || 'bal.' || pv_col || ' = ' || pv_rule_obj_id;
402 END IF;
403
404 v_first_time := 'N';
405 END LOOP;
406
407 -- The select statement to be used in the USING part of the merge statement
408 IF p_load_type = 'XGL' THEN
409 v_merge_select := 'SELECT * FROM FEM_BAL_POST_INTERIM_GT ' ||
410 'WHERE posting_error_flag = ''N''';
411 v_req_text := ':pv_req_id';
412 ELSE
413 v_merge_select :=
414 'SELECT param.request_id, pi.bal_post_type_code, ' ||
415 'pi.dataset_code, pi.cal_period_id, pi.ledger_id, pi.currency_type_code, ' ||
416 'pi.currency_code, pi.company_cost_center_org_id, pi.source_system_code, ' ||
417 'pi.financial_elem_id, pi.product_id, pi.natural_account_id, ' ||
418 'pi.channel_id, pi.line_item_id, pi.project_id, pi.customer_id, ' ||
419 'pi.entity_id, pi.intercompany_id, pi.task_id, pi.user_dim1_id, ' ||
420 'pi.user_dim2_id, pi.user_dim3_id, pi.user_dim4_id, pi.user_dim5_id, ' ||
421 'pi.user_dim6_id, pi.user_dim7_id, pi.user_dim8_id, pi.user_dim9_id, ' ||
422 'pi.user_dim10_id, ' ||
423 'sum(pi.xtd_balance_e) xtd_balance_e, ' ||
424 'sum(pi.xtd_balance_f) xtd_balance_f, ' ||
425 'sum(pi.ytd_balance_e) ytd_balance_e, ' ||
426 'sum(pi.ytd_balance_f) ytd_balance_f, ' ||
427 'sum(pi.qtd_balance_e) qtd_balance_e, ' ||
428 'sum(pi.qtd_balance_f) qtd_balance_f, ' ||
429 'sum(pi.ptd_debit_balance_e) ptd_debit_balance_e, ' ||
430 'sum(pi.ptd_credit_balance_e) ptd_credit_balance_e, ' ||
431 'sum(pi.ytd_debit_balance_e) ytd_debit_balance_e, ' ||
432 'sum(pi.ytd_credit_balance_e) ytd_credit_balance_e ' ||
433 'FROM FEM_BAL_POST_INTERIM_GT pi, ' ||
434 ' FEM_INTG_EXEC_PARAMS_GT param, ' ||
435 ' GL_CODE_COMBINATIONS cc ' ||
436 'WHERE pi.dataset_code = param.output_dataset_code ' ||
437 'AND pi.cal_period_id = param.cal_period_id ' ||
438 'AND pi.posting_error_flag = ''N'' ' ||
439 'AND cc.code_combination_id = pi.code_combination_id ' ||
440 'AND NOT EXISTS ' ||
441 '(SELECT 1 ' ||
442 ' FROM FEM_INTG_DELTA_LOADS dl ' ||
443 ' WHERE dl.ledger_id = pi.ledger_id ' ||
444 ' AND dl.dataset_code = pi.dataset_code ' ||
445 ' AND dl.cal_period_id = pi.cal_period_id ' ||
446 ' AND dl.delta_run_id = pi.delta_run_id ' ||
447 ' AND dl.balance_seg_value = cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || ' ' ||
448 ' AND dl.loaded_flag = ''N'') ' ||
449 'GROUP BY param.request_id, pi.bal_post_type_code, ' ||
450 'pi.dataset_code, pi.cal_period_id, pi.ledger_id, pi.currency_type_code, ' ||
451 'pi.currency_code, pi.company_cost_center_org_id, pi.source_system_code, ' ||
452 'pi.financial_elem_id, pi.product_id, pi.natural_account_id, ' ||
453 'pi.channel_id, pi.line_item_id, pi.project_id, pi.customer_id, ' ||
454 'pi.entity_id, pi.intercompany_id, pi.task_id, pi.user_dim1_id, ' ||
455 'pi.user_dim2_id, pi.user_dim3_id, pi.user_dim4_id, pi.user_dim5_id, ' ||
456 'pi.user_dim6_id, pi.user_dim7_id, pi.user_dim8_id, pi.user_dim9_id, ' ||
457 'pi.user_dim10_id ';
458 v_req_text := 'int.request_id';
459 END IF;
460
461 -- Merge data from FEM_BAL_POST_INTERIM_GT into FEM_BALANCES
462 v_sql_stmt_2 :=
463 ' MERGE INTO FEM_BALANCES bal '||
464 ' USING (' || v_merge_select || ') int '||
465 ' ON ( ' || v_key_stmt ||
466 ')' ||
467 ' WHEN MATCHED THEN UPDATE SET '||
468 ' bal.xtd_balance_e = ' ||
469 ' DECODE(int.bal_post_type_code, ''R'', int.xtd_balance_e, ' ||
470 'DECODE(bal.xtd_balance_e, NULL, int.xtd_balance_e, ' ||
471 'DECODE(int.xtd_balance_e, NULL, bal.xtd_balance_e, ' ||
472 'bal.xtd_balance_e + int.xtd_balance_e))), ' ||
473 ' bal.xtd_balance_f = ' ||
474 ' DECODE(int.bal_post_type_code, ''R'', int.xtd_balance_f, ' ||
475 'DECODE(bal.xtd_balance_f, NULL, int.xtd_balance_f, ' ||
476 'DECODE(int.xtd_balance_f, NULL, bal.xtd_balance_f, ' ||
477 'bal.xtd_balance_f + int.xtd_balance_f))), ' ||
478 ' bal.ytd_balance_e = ' ||
479 ' DECODE(int.bal_post_type_code, ''R'', int.ytd_balance_e, ' ||
480 'DECODE(bal.ytd_balance_e, NULL, int.ytd_balance_e, ' ||
481 'DECODE(int.ytd_balance_e, NULL, bal.ytd_balance_e, ' ||
482 'bal.ytd_balance_e + int.ytd_balance_e))), ' ||
483 ' bal.ytd_balance_f = ' ||
484 ' DECODE(int.bal_post_type_code, ''R'', int.ytd_balance_f, ' ||
485 'DECODE(bal.ytd_balance_f, NULL, int.ytd_balance_f, ' ||
486 'DECODE(int.ytd_balance_f, NULL, bal.ytd_balance_f, ' ||
487 'bal.ytd_balance_f + int.ytd_balance_f))), ' ||
488 ' bal.qtd_balance_e = ' ||
489 ' DECODE(int.bal_post_type_code, ''R'', int.qtd_balance_e, ' ||
490 'DECODE(bal.qtd_balance_e, NULL, int.qtd_balance_e, ' ||
491 'DECODE(int.qtd_balance_e, NULL, bal.qtd_balance_e, ' ||
492 'bal.qtd_balance_e + int.qtd_balance_e))), ' ||
493 ' bal.qtd_balance_f = ' ||
494 ' DECODE(int.bal_post_type_code, ''R'', int.qtd_balance_f, ' ||
495 'DECODE(bal.qtd_balance_f, NULL, int.qtd_balance_f, ' ||
496 'DECODE(int.qtd_balance_f, NULL, bal.qtd_balance_f, ' ||
497 'bal.qtd_balance_f + int.qtd_balance_f))), ' ||
498 ' bal.ptd_debit_balance_e = ' ||
499 ' DECODE(int.bal_post_type_code, ''R'', int.ptd_debit_balance_e, ' ||
500 'DECODE(bal.ptd_debit_balance_e, NULL, int.ptd_debit_balance_e, ' ||
501 'DECODE(int.ptd_debit_balance_e, NULL, bal.ptd_debit_balance_e, ' ||
502 'bal.ptd_debit_balance_e + int.ptd_debit_balance_e))), ' ||
503 ' bal.ptd_credit_balance_e = ' ||
504 ' DECODE(int.bal_post_type_code, ''R'', int.ptd_credit_balance_e, ' ||
505 'DECODE(bal.ptd_credit_balance_e, NULL, int.ptd_credit_balance_e, ' ||
506 'DECODE(int.ptd_credit_balance_e, NULL, bal.ptd_credit_balance_e, ' ||
507 'bal.ptd_credit_balance_e + int.ptd_credit_balance_e))), ' ||
508 ' bal.ytd_debit_balance_e = ' ||
509 ' DECODE(int.bal_post_type_code, ''R'', int.ytd_debit_balance_e, ' ||
510 'DECODE(bal.ytd_debit_balance_e, NULL, int.ytd_debit_balance_e, ' ||
511 'DECODE(int.ytd_debit_balance_e, NULL, bal.ytd_debit_balance_e, ' ||
512 'bal.ytd_debit_balance_e + int.ytd_debit_balance_e))), ' ||
513 ' bal.ytd_credit_balance_e = ' ||
514 ' DECODE(int.bal_post_type_code, ''R'', int.ytd_credit_balance_e, ' ||
515 'DECODE(bal.ytd_credit_balance_e, NULL, int.ytd_credit_balance_e, ' ||
516 'DECODE(int.ytd_credit_balance_e, NULL, bal.ytd_credit_balance_e, ' ||
517 'bal.ytd_credit_balance_e + int.ytd_credit_balance_e))), ' ||
518 ' bal.last_updated_by_request_id = ' || v_req_text || ', '||
519 ' bal.last_updated_by_object_id = :pv_rule_obj_id '||
520 ' WHEN NOT MATCHED THEN INSERT '||
521 ' ( bal.dataset_code, '||
522 ' bal.cal_period_id, '||
523 ' bal.creation_row_sequence, '||
524 ' bal.source_system_code, '||
525 ' bal.ledger_id, '||
526 ' bal.company_cost_center_org_id, '||
527 ' bal.currency_code, '||
528 ' bal.currency_type_code, '||
529 ' bal.financial_elem_id, '||
530 ' bal.product_id, '||
531 ' bal.natural_account_id, '||
532 ' bal.channel_id, '||
533 ' bal.line_item_id, '||
534 ' bal.project_id, '||
535 ' bal.customer_id, '||
536 ' bal.intercompany_id, '||
537 ' bal.entity_id, '||
538 ' bal.task_id, '||
539 ' bal.user_dim1_id, '||
540 ' bal.user_dim2_id, '||
541 ' bal.user_dim3_id, '||
542 ' bal.user_dim4_id, '||
543 ' bal.user_dim5_id, '||
544 ' bal.user_dim6_id, '||
545 ' bal.user_dim7_id, '||
546 ' bal.user_dim8_id, '||
547 ' bal.user_dim9_id, '||
548 ' bal.user_dim10_id, '||
549 ' bal.created_by_request_id, '||
550 ' bal.created_by_object_id, '||
551 ' bal.last_updated_by_request_id, '||
552 ' bal.last_updated_by_object_id, '||
553 ' bal.xtd_balance_e, '||
554 ' bal.xtd_balance_f, '||
555 ' bal.ytd_balance_e, '||
556 ' bal.ytd_balance_f, '||
557 ' bal.qtd_balance_e, '||
558 ' bal.qtd_balance_f, '||
559 ' bal.ptd_debit_balance_e, '||
560 ' bal.ptd_credit_balance_e, '||
561 ' bal.ytd_debit_balance_e, '||
562 ' bal.ytd_credit_balance_e) '||
563 ' VALUES (int.dataset_code, '||
564 ' int.cal_period_id, '||
565 ' fem_gl_post_creation_row_s.nextval, '||
566 ' int.source_system_code, '||
567 ' int.ledger_id, '||
568 ' int.company_cost_center_org_id, '||
569 ' int.currency_code, '||
570 ' int.currency_type_code, '||
571 ' int.financial_elem_id, '||
572 ' int.product_id, '||
573 ' int.natural_account_id, '||
574 ' int.channel_id, '||
575 ' int.line_item_id, '||
576 ' int.project_id, '||
577 ' int.customer_id, '||
578 ' int.intercompany_id, '||
579 ' int.entity_id, '||
580 ' int.task_id, '||
581 ' int.user_dim1_id, '||
582 ' int.user_dim2_id, '||
583 ' int.user_dim3_id, '||
584 ' int.user_dim4_id, '||
585 ' int.user_dim5_id, '||
586 ' int.user_dim6_id, '||
587 ' int.user_dim7_id, '||
588 ' int.user_dim8_id, '||
589 ' int.user_dim9_id, '||
590 ' int.user_dim10_id, '||
591 ' ' || v_req_text || ', ' ||
592 ' :pv_rule_obj_id, '||
593 ' ' || v_req_text || ', ' ||
594 ' :pv_rule_obj_id, '||
595 ' int.xtd_balance_e, '||
596 ' int.xtd_balance_f, '||
597 ' int.ytd_balance_e, '||
598 ' int.ytd_balance_f, '||
599 ' int.qtd_balance_e, '||
600 ' int.qtd_balance_f, '||
601 ' int.ptd_debit_balance_e, '||
602 ' int.ptd_credit_balance_e, '||
603 ' int.ytd_debit_balance_e, '||
604 ' int.ytd_credit_balance_e)';
605
606 -- Print out the merge statement for debugging purposes
607 FOR iterator IN 1..trunc((length(v_sql_stmt_2)+1499)/1500) LOOP
608 FEM_ENGINES_PKG.Tech_Message
609 (p_severity => v_log_level_2,
610 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
611 p_app_name => 'FEM',
612 p_msg_name => 'FEM_GL_POST_204',
613 p_token1 => 'VAR_NAME',
614 p_value1 => 'v_sql_stmt_2: ' || iterator,
615 p_token2 => 'VAR_VAL',
616 p_value2 => substr(v_sql_stmt_2, iterator*1500-1499, 1500));
617 END LOOP;
618
619 -- Only bind the request id if we are in XGL mode
620 IF p_load_type = 'XGL' THEN
621 EXECUTE IMMEDIATE v_sql_stmt_2
622 USING pv_req_id, pv_rule_obj_id, pv_req_id, pv_rule_obj_id,
623 pv_req_id, pv_rule_obj_id;
624 ELSE
625 EXECUTE IMMEDIATE v_sql_stmt_2
626 USING pv_rule_obj_id, pv_rule_obj_id, pv_rule_obj_id;
627 END IF;
628
629 x_rows_posted := SQL%ROWCOUNT;
630
631 FEM_ENGINES_PKG.Tech_Message
632 ( p_severity => v_log_level_1,
633 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
634 p_app_name => 'FEM',
635 p_msg_text => 'Merged ' || TO_CHAR(x_rows_posted) ||
636 ' rows into FEM_BALANCES');
637
638 END IF;
639
640 -- Find out number of rows in the interim table
641 SELECT count(*)
642 INTO v_interim_row_count
643 FROM FEM_BAL_POST_INTERIM_GT bpi
644 WHERE posting_error_flag = 'N'
645 AND NOT EXISTS
646 (SELECT 1
647 FROM FEM_INTG_DELTA_LOADS dl
648 WHERE dl.ledger_id = bpi.ledger_id
649 AND dl.dataset_code = bpi.dataset_code
650 AND dl.cal_period_id = bpi.cal_period_id
651 AND dl.delta_run_id = bpi.delta_run_id
652 AND dl.loaded_flag = 'N');
653
654 IF (p_load_type = 'XGL' AND v_interim_row_count <> x_rows_posted) THEN
655
656 -- This routine has failed with error
657 x_completion_code := 2;
658 RAISE DATA_CORRUPTION;
659
660 END IF;
661
662
663 -- Now, recalculate the QTD balances if applicable
664 IF p_load_type = 'OGL' THEN
665 SELECT dimension_id
666 INTO v_na_dim_id
667 FROM fem_dimensions_b
668 WHERE dimension_varchar_label = 'NATURAL_ACCOUNT';
669
670 SELECT dimension_id
671 INTO v_xat_dim_id
672 FROM fem_dimensions_b
673 WHERE dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE';
674
675 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
676 p_dim_id => v_na_dim_id,
677 p_attr_label => 'EXTENDED_ACCOUNT_TYPE',
678 x_attr_id => v_na_xat_attr_id,
679 x_ver_id => v_na_xat_v_id,
680 x_err_code => v_error_code);
681
682 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
683 p_dim_id => v_xat_dim_id,
684 p_attr_label => 'BASIC_ACCOUNT_TYPE_CODE',
685 x_attr_id => v_xat_bat_attr_id,
686 x_ver_id => v_xat_bat_v_id,
687 x_err_code => v_error_code);
688
689 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
690 p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_id,
691 p_attr_label => 'GL_PERIOD_NUM',
692 x_attr_id => v_cp_period_num_attr_id,
693 x_ver_id => v_cp_period_num_v_id,
694 x_err_code => v_error_code);
695
696 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
697 p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_cal_per_dim_id,
698 p_attr_label => 'ACCOUNTING_YEAR',
699 x_attr_id => v_cp_year_attr_id,
700 x_ver_id => v_cp_year_v_id,
701 x_err_code => v_error_code);
702
703 IF p_maintain_qtd = 'Y' THEN
704 SELECT period_set_name, accounted_period_type
705 INTO v_ps_name, v_period_type
706 FROM gl_ledgers
707 WHERE ledger_id = pv_ledger_id;
708
709 UPDATE FEM_BALANCES fb
710 SET (qtd_balance_e, qtd_balance_f) =
711 (SELECT
712 fb.xtd_balance_e + nvl(sum(nvl(fb_in.xtd_balance_e,0)),0),
713 fb.xtd_balance_f + nvl(sum(nvl(fb_in.xtd_balance_f,0)),0)
714 FROM FEM_BALANCES fb_in,
715 FEM_CAL_PERIODS_B cp_curr,
716 FEM_CAL_PERIODS_B cp_prev,
717 FEM_CAL_PERIODS_ATTR cpa_curr,
718 FEM_CAL_PERIODS_ATTR cpa_prev,
719 FEM_CAL_PERIODS_ATTR cpa_curr_year,
720 FEM_CAL_PERIODS_ATTR cpa_prev_year,
721 GL_PERIODS per_curr,
722 GL_PERIODS per_prev
723 WHERE fb_in.dataset_code = fb.dataset_code
724 AND fb_in.source_system_code = fb.source_system_code
725 AND fb_in.ledger_id = pv_ledger_id
726 AND fb.ledger_id = pv_ledger_id
727 AND fb_in.currency_code = fb.currency_code
728 AND fb_in.currency_type_code = fb.currency_type_code
729 AND fb_in.company_cost_center_org_id = fb.company_cost_center_org_id
730 AND fb_in.product_id = fb.product_id
731 AND fb_in.natural_account_id = fb.natural_account_id
732 AND fb_in.channel_id = fb.channel_id
733 AND fb_in.line_item_id = fb.line_item_id
734 AND fb_in.project_id = fb.project_id
735 AND fb_in.customer_id = fb.customer_id
736 AND fb_in.entity_id = fb.entity_id
737 AND fb_in.intercompany_id = fb.intercompany_id
738 AND fb_in.user_dim1_id = fb.user_dim1_id
739 AND fb_in.user_dim2_id = fb.user_dim2_id
740 AND fb_in.user_dim3_id = fb.user_dim3_id
741 AND fb_in.user_dim4_id = fb.user_dim4_id
742 AND fb_in.user_dim5_id = fb.user_dim5_id
743 AND fb_in.user_dim6_id = fb.user_dim6_id
744 AND fb_in.user_dim7_id = fb.user_dim7_id
745 AND fb_in.user_dim8_id = fb.user_dim8_id
746 AND fb_in.user_dim9_id = fb.user_dim9_id
747 AND fb_in.user_dim10_id = fb.user_dim10_id
748 AND nvl(fb_in.task_id, -1) = nvl(fb.task_id, -1)
749 AND nvl(fb_in.activity_id, -1) = nvl(fb.activity_id, -1)
750 AND nvl(fb_in.cost_object_id, -1) = nvl(fb.cost_object_id, -1)
751 AND nvl(fb_in.financial_elem_id, -1) = nvl(fb.financial_elem_id, -1)
752 AND cp_curr.cal_period_id = fb.cal_period_id
753 AND cp_prev.cal_period_id = fb_in.cal_period_id
754 AND cp_prev.dimension_group_id = cp_curr.dimension_group_id
755 AND cpa_curr.cal_period_id = cp_curr.cal_period_id
756 AND cpa_curr.attribute_id = v_cp_period_num_attr_id
757 AND cpa_curr.version_id = v_cp_period_num_v_id
758 AND cpa_prev.cal_period_id = cp_prev.cal_period_id
759 AND cpa_prev.attribute_id = v_cp_period_num_attr_id
760 AND cpa_prev.version_id = v_cp_period_num_v_id
761 AND cpa_prev.number_assign_value < cpa_curr.number_assign_value
762 AND cpa_curr_year.cal_period_id = cp_curr.cal_period_id
763 AND cpa_curr_year.attribute_id = v_cp_year_attr_id
764 AND cpa_curr_year.version_id = v_cp_year_v_id
765 AND cpa_prev_year.cal_period_id = cp_prev.cal_period_id
766 AND cpa_prev_year.attribute_id = v_cp_year_attr_id
767 AND cpa_prev_year.version_id = v_cp_year_v_id
768 AND cpa_prev_year.number_assign_value = cpa_curr_year.number_assign_value
769 AND per_curr.period_set_name = v_ps_name
770 AND per_curr.period_type = v_period_type
771 AND per_curr.period_year = cpa_curr_year.number_assign_value
772 AND per_curr.period_num = cpa_curr.number_assign_value
773 AND per_prev.period_set_name = v_ps_name
774 AND per_prev.period_type = v_period_type
775 AND per_prev.period_year = cpa_curr_year.number_assign_value
776 AND per_prev.period_num = cpa_prev.number_assign_value
777 AND per_prev.quarter_num = per_curr.quarter_num
778 )
779 WHERE EXISTS
780 (SELECT 1
781 FROM FEM_INTG_EXEC_PARAMS_GT param
782 WHERE param.output_dataset_code = fb.dataset_code
783 AND param.cal_period_id = fb.cal_period_id
784 AND param.error_code IS NULL
785 AND param.request_id IS NOT NULL)
786 AND EXISTS
787 (SELECT 1
788 FROM FEM_NAT_ACCTS_ATTR naa,
789 FEM_EXT_ACCT_TYPES_ATTR xat
790 WHERE naa.attribute_id = v_na_xat_attr_id
791 AND naa.version_id = v_na_xat_v_id
792 AND naa.natural_account_id = fb.natural_account_id
793 AND xat.attribute_id = v_xat_bat_attr_id
794 AND xat.version_id = v_xat_bat_v_id
795 AND xat.ext_account_type_code = naa.dim_attribute_varchar_member
796 AND xat.dim_attribute_varchar_member IN ('REVENUE', 'EXPENSE'));
797
798 FEM_ENGINES_PKG.Tech_Message
799 ( p_severity => v_log_level_1,
800 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
801 p_app_name => 'FEM',
802 p_msg_text => 'Updated ' || TO_CHAR(x_rows_posted) ||
803 ' rows in FEM_BALANCES');
804 END IF;
805 END IF;
806
807
808 -- This routine has completed successfully
809 x_completion_code := 0;
810
811 FEM_ENGINES_PKG.Tech_Message
812 (p_severity => v_log_level_2,
813 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
814 p_msg_text => 'END FEM_GL_POST_BAL_PKG.Post_FEM_Balances');
815
816 EXCEPTION
817 WHEN DATA_CORRUPTION THEN
818
819 FEM_ENGINES_PKG.Tech_Message
820 (p_severity => v_log_level_6,
821 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
822 p_app_name => 'FEM',
823 p_msg_name => 'FEM_GL_POST_401');
824
825 FEM_ENGINES_PKG.Tech_Message
826 (p_severity => v_log_level_6,
827 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
828 p_msg_text => 'Data corruption in FEM_BALANCES!');
829
830 FEM_ENGINES_PKG.User_Message
831 (p_app_name => 'FEM',
832 p_msg_name => 'FEM_GL_POST_401');
833
834 FEM_ENGINES_PKG.Tech_Message
835 (p_severity => v_log_level_2,
836 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
837 p_msg_text => 'END FEM_GL_POST_BAL_PKG.Post_FEM_Balances');
838
839 WHEN PROC_KEY_ERROR THEN
840
841 FEM_ENGINES_PKG.Tech_Message
842 (p_severity => v_log_level_2,
843 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
844 p_msg_text => 'END FEM_GL_POST_BAL_PKG.Post_FEM_Balances');
845
846 WHEN OTHERS THEN
847
848 FEM_GL_POST_PROCESS_PKG.pv_sqlerrm := SQLERRM;
849
850 IF p_load_type = 'XGL' THEN
851 ROLLBACK;
852 END IF;
853
854 x_completion_code := 2;
855
856 FEM_ENGINES_PKG.User_Message
857 (p_app_name => 'FEM',
858 p_msg_name => 'FEM_GL_POST_215',
859 p_token1 => 'ERR_MSG',
860 p_value1 => SQLERRM);
861
862 FEM_ENGINES_PKG.Tech_Message
863 (p_severity => v_log_level_6,
864 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
865 p_app_name => 'FEM',
866 p_msg_name => 'FEM_GL_POST_215',
867 p_token1 => 'ERR_MSG',
868 p_value1 => SQLERRM);
869
870 FEM_ENGINES_PKG.Tech_Message
871 (p_severity => v_log_level_2,
872 p_module => 'fem.plsql.gl_post_bal.pfb.' || p_process_slice,
873 p_msg_text => 'END FEM_GL_POST_BAL_PKG.Post_FEM_Balances');
874
875 END post_fem_balances;
876
877 END FEM_GL_POST_BAL_PKG;