[Home] [Help]
PACKAGE BODY: APPS.FEM_INTG_BAL_ENG_LOAD_PKG
Source
1 PACKAGE BODY FEM_INTG_BAL_ENG_LOAD_PKG AS
2 /* $Header: fem_intg_be_load.plb 120.3 2006/10/10 06:46:14 hakumar noship $ */
3
4
5 --
6 -- PRIVATE GLOBAL VARIABLES
7 --
8 pc_log_level_statement CONSTANT NUMBER := fnd_log.level_statement;
9 pc_log_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
10 pc_log_level_event CONSTANT NUMBER := fnd_log.level_event;
11 pc_log_level_exception CONSTANT NUMBER := fnd_log.level_exception;
12 pc_log_level_error CONSTANT NUMBER := fnd_log.level_error;
13 pc_log_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
14
15
16 -- holds a new line character
17 pv_nl VARCHAR2(1);
18
19
20 --
21 -- PRIVATE PROCEDURES
22 --
23
24 --
25 -- Function
26 -- Get_Flex_Values_Query
27 -- Purpose
28 -- Gets the query to pick up the all the ledger's bsvs.
29 -- Return Value
30 -- The text SQL query to get the balances segment values
31 -- Example
32 -- FEM_INTG_BAL_ENG_LOAD_PKG.Get_Flex_Value_Query;
33 -- Notes
34 --
35 FUNCTION Get_Flex_Values_Query RETURN VARCHAR2 IS
36 v_query VARCHAR2(2000);
37
38 v_value_set_id NUMBER;
39 v_value_set_type VARCHAR2(30);
40
41 v_table_name VARCHAR2(50);
42 v_value_col_name VARCHAR2(50);
43 v_where_clause VARCHAR2(1000);
44 v_summary_col_name VARCHAR2(50);
45 BEGIN
46
47 SELECT fvs.flex_value_set_id,
48 fvs.validation_type
49 INTO v_value_set_id,
50 v_value_set_type
51 FROM gl_ledgers lgr,
52 fnd_segment_attribute_values sav,
53 fnd_id_flex_segments ifs,
54 fnd_flex_value_sets fvs
55 WHERE lgr.ledger_id = FEM_GL_POST_PROCESS_PKG.pv_ledger_id
56 AND sav.application_id = 101
57 AND sav.id_flex_code = 'GL#'
58 AND sav.id_flex_num = lgr.chart_of_accounts_id
59 AND sav.segment_attribute_type = 'GL_BALANCING'
60 AND sav.attribute_value = 'Y'
61 AND ifs.application_id = 101
62 AND ifs.id_flex_code = 'GL#'
63 AND ifs.id_flex_num = lgr.chart_of_accounts_id
64 AND ifs.application_column_name = sav.application_column_name
65 AND fvs.flex_value_set_id = ifs.flex_value_set_id;
66
67 -- Set the query text based on the value set type
68 IF v_value_set_type = 'I' THEN -- Independent value set
69 v_query :=
70 'SELECT flex_value' || pv_nl ||
71 'FROM fnd_flex_values' || pv_nl ||
72 'WHERE flex_value_set_id = ' || v_value_set_id || pv_nl ||
73 'AND summary_flag <> ''Y''';
74 ELSE -- Table-validated value set
75 SELECT application_table_name,
76 value_column_name,
77 additional_where_clause,
78 summary_column_name
79 INTO v_table_name,
80 v_value_col_name,
81 v_where_clause,
82 v_summary_col_name
83 FROM fnd_flex_validation_tables
84 WHERE flex_value_set_id = v_value_set_id;
85
86 v_query :=
87 'SELECT flex.flex_value' || pv_nl ||
88 'FROM (SELECT ' || v_value_col_name || ' flex_value,' || pv_nl ||
89 ' ' || v_summary_col_name || ' summary_flag' || pv_nl ||
90 ' FROM ' || v_table_name || pv_nl ||
91 ' ' || v_where_clause || ') flex' || pv_nl ||
92 'WHERE flex.summary_flag <> ''Y''';
93 END IF;
94
95 RETURN v_query;
96 END Get_Flex_Values_Query;
97
98
99
100
101 --
102 -- PUBLIC PROCEDURES
103 --
104
105 PROCEDURE Load_Std_Balances( x_completion_code OUT NOCOPY NUMBER,
106 x_num_rows_inserted OUT NOCOPY NUMBER,
107 p_bsv_range_low VARCHAR2,
108 p_bsv_range_high VARCHAR2,
109 p_maintain_qtd VARCHAR2) IS
110 v_sql VARCHAR2(32767);
111 v_sql_incr VARCHAR2(32767); -- For holding incremental load statements
112
113 -- These three will hold common code that will be used in the statements
114 v_insert VARCHAR2(2000);
115 v_ccmap_selection VARCHAR2(1000);
116 v_ccmap_join VARCHAR2(2000);
117 v_ccy_join VARCHAR2(1000);
118
119 v_intermediate_rows_inserted NUMBER;
120
121 v_xat_basic_type_attr_id NUMBER;
122 v_xat_basic_type_v_id NUMBER;
123 v_xat_sign_attr_id NUMBER;
124 v_xat_sign_v_id NUMBER;
125
126 v_error_code NUMBER;
127
128 v_bsv_low VARCHAR2(100);
129 v_bsv_high VARCHAR2(100);
130
131 CURSOR unmapped_exists_c IS
132 SELECT 1
133 FROM fem_bal_post_interim_gt
134 WHERE posting_error_flag = 'Y'
135 AND (nvl(xtd_balance_e,0) <> 0 OR
136 nvl(xtd_balance_f,0) <> 0 OR
137 nvl(ytd_balance_e,0) <> 0 OR
138 nvl(ytd_balance_f,0) <> 0 OR
139 nvl(qtd_balance_e,0) <> 0 OR
140 nvl(qtd_balance_f,0) <> 0 OR
141 nvl(ptd_debit_balance_e,0) <> 0 OR
142 nvl(ptd_credit_balance_e,0) <> 0 OR
143 nvl(ytd_debit_balance_e,0) <> 0 OR
144 nvl(ytd_credit_balance_e,0) <> 0);
145
146 dummy NUMBER;
147
148 v_module VARCHAR2(100);
149
150 --bug fix 5585720
151 v_flex_query_stmt VARCHAR2(2000);
152
153 BEGIN
154 FEM_ENGINES_PKG.Tech_Message
155 (p_severity => pc_log_level_procedure,
156 p_module => v_module,
157 p_msg_text => 'BEGIN');
158
159 pv_nl := '
160 ';
161 v_module := 'fem.plsql.fem_intg_bal_eng_load.load_std_balances';
162
163 x_completion_code := 0;
164 x_num_rows_inserted := 0;
165
166 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
167 p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_ext_acct_type_dim_id,
168 p_attr_label => 'BASIC_ACCOUNT_TYPE_CODE',
169 x_attr_id => v_xat_basic_type_attr_id,
170 x_ver_id => v_xat_basic_type_v_id,
171 x_err_code => v_error_code);
172
173 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
174 p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_ext_acct_type_dim_id,
175 p_attr_label => 'SIGN',
176 x_attr_id => v_xat_sign_attr_id,
177 x_ver_id => v_xat_sign_v_id,
178 x_err_code => v_error_code);
179
180
181 v_insert :=
182 'INSERT INTO FEM_BAL_POST_INTERIM_GT(INTERFACE_ROWID, DELTA_RUN_ID, ' ||
183 'BAL_POST_TYPE_CODE, DATASET_CODE, CAL_PERIOD_ID, LEDGER_ID, ' ||
184 'SOURCE_SYSTEM_CODE, COMPANY_COST_CENTER_ORG_ID, FINANCIAL_ELEM_ID, ' ||
185 'PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID, LINE_ITEM_ID, PROJECT_ID, ' ||
186 'CUSTOMER_ID, ENTITY_ID, INTERCOMPANY_ID, TASK_ID, USER_DIM1_ID, ' ||
187 'USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID, USER_DIM5_ID, USER_DIM6_ID, ' ||
188 'USER_DIM7_ID, USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID, CURRENCY_CODE, ' ||
189 'CURRENCY_TYPE_CODE, POSTING_ERROR_FLAG, CODE_COMBINATION_ID, ' ||
190 'XTD_BALANCE_E, QTD_BALANCE_E, YTD_BALANCE_E, XTD_BALANCE_F, ' ||
191 'QTD_BALANCE_F, YTD_BALANCE_F, PTD_DEBIT_BALANCE_E, PTD_CREDIT_BALANCE_E, ' ||
192 'YTD_DEBIT_BALANCE_E, YTD_CREDIT_BALANCE_E)' || pv_nl;
193
194 v_ccmap_selection :=
195 ' ccmap.PRODUCT_ID,' || pv_nl ||
196 ' ccmap.NATURAL_ACCOUNT_ID,' || pv_nl ||
197 ' ccmap.CHANNEL_ID,' || pv_nl ||
198 ' ccmap.LINE_ITEM_ID,' || pv_nl ||
199 ' ccmap.PROJECT_ID,' || pv_nl ||
200 ' ccmap.CUSTOMER_ID,' || pv_nl ||
201 ' ccmap.ENTITY_ID,' || pv_nl ||
202 ' ccmap.INTERCOMPANY_ID,' || pv_nl ||
203 ' ccmap.TASK_ID,' || pv_nl ||
204 ' ccmap.USER_DIM1_ID,' || pv_nl ||
205 ' ccmap.USER_DIM2_ID,' || pv_nl ||
206 ' ccmap.USER_DIM3_ID,' || pv_nl ||
207 ' ccmap.USER_DIM4_ID,' || pv_nl ||
208 ' ccmap.USER_DIM5_ID,' || pv_nl ||
209 ' ccmap.USER_DIM6_ID,' || pv_nl ||
210 ' ccmap.USER_DIM7_ID,' || pv_nl ||
211 ' ccmap.USER_DIM8_ID,' || pv_nl ||
212 ' ccmap.USER_DIM9_ID,' || pv_nl ||
213 ' ccmap.USER_DIM10_ID,' || pv_nl;
214
215 v_ccmap_join :=
216 'AND ccmap.global_vs_combo_id (+)= ' || FEM_GL_POST_PROCESS_PKG.pv_global_vs_combo_id || pv_nl ||
217 'AND ccmap.COMPANY_COST_CENTER_ORG_ID (+)<> -1' || pv_nl ||
218 'AND ccmap.NATURAL_ACCOUNT_ID (+)<> -1' || pv_nl ||
219 'AND ccmap.LINE_ITEM_ID (+)<> -1' || pv_nl ||
220 'AND ccmap.PRODUCT_ID (+)<> -1' || pv_nl ||
221 'AND ccmap.CHANNEL_ID (+)<> -1' || pv_nl ||
222 'AND ccmap.PROJECT_ID (+)<> -1' || pv_nl ||
223 'AND ccmap.CUSTOMER_ID (+)<> -1' || pv_nl ||
224 'AND ccmap.ENTITY_ID (+)<> -1' || pv_nl ||
225 'AND ccmap.INTERCOMPANY_ID (+)<> -1' || pv_nl ||
226 'AND ccmap.USER_DIM1_ID (+)<> -1' || pv_nl ||
227 'AND ccmap.USER_DIM2_ID (+)<> -1' || pv_nl ||
228 'AND ccmap.USER_DIM3_ID (+)<> -1' || pv_nl ||
229 'AND ccmap.USER_DIM4_ID (+)<> -1' || pv_nl ||
230 'AND ccmap.USER_DIM5_ID (+)<> -1' || pv_nl ||
231 'AND ccmap.USER_DIM6_ID (+)<> -1' || pv_nl ||
232 'AND ccmap.USER_DIM7_ID (+)<> -1' || pv_nl ||
233 'AND ccmap.USER_DIM8_ID (+)<> -1' || pv_nl ||
234 'AND ccmap.USER_DIM9_ID (+)<> -1' || pv_nl ||
235 'AND ccmap.USER_DIM10_ID (+)<> -1' || pv_nl ||
236 'AND ccmap.TASK_ID (+)<> -1' || pv_nl;
237
238
239 v_ccy_join := '';
240 IF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED' THEN
241 IF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'SPECIFIC' THEN
242 v_ccy_join :=
243 'AND (glb.translated_flag IS NULL OR' || pv_nl ||
244 ' glb.translated_flag = ''R'' OR' || pv_nl ||
245 ' (glb.translated_flag IN (''Y'', ''N'') AND' || pv_nl ||
246 ' EXISTS' || pv_nl ||
247 ' (SELECT 1' || pv_nl ||
248 ' FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
249 ' WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
250 ' AND ccy.xlated_currency_code = glb.currency_code)))' || pv_nl;
251 ELSIF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'NONE' THEN
252 v_ccy_join :=
253 'AND (glb.translated_flag IS NULL OR' || pv_nl ||
254 ' glb.translated_flag = ''R'')' || pv_nl;
255 END IF;
256 ELSIF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'FUNCTIONAL' THEN
257 IF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'SPECIFIC' THEN
258 v_ccy_join :=
259 'AND (glb.translated_flag IS NULL OR' || pv_nl ||
260 ' (glb.translated_flag IN (''Y'', ''N'') AND' || pv_nl ||
261 ' EXISTS' || pv_nl ||
262 ' (SELECT 1' || pv_nl ||
263 ' FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
264 ' WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
265 ' AND ccy.xlated_currency_code = glb.currency_code)))' || pv_nl;
266 ELSIF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'ALL' THEN
267 v_ccy_join :=
268 'AND (glb.translated_flag IS NULL OR' || pv_nl ||
269 ' glb.translated_flag IN (''Y'', ''N''))' || pv_nl;
270 ELSE -- No translated balances
271 v_ccy_join :=
272 'AND glb.translated_flag IS NULL' || pv_nl;
273 END IF;
274 ELSE -- Translated only
275 IF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'SPECIFIC' THEN
276 v_ccy_join :=
277 'AND glb.translated_flag IN (''Y'', ''N'')' || pv_nl ||
278 'AND EXISTS' || pv_nl ||
279 ' (SELECT 1' || pv_nl ||
280 ' FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
281 ' WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
282 ' AND ccy.xlated_currency_code = glb.currency_code)' || pv_nl;
283 ELSE -- All translated balances
284 v_ccy_join :=
285 'AND glb.translated_flag IN (''Y'', ''N'')' || pv_nl;
286 END IF;
287 END IF;
288
289
290 -- If we are doing any snapshots, do it here
291 IF FEM_GL_POST_PROCESS_PKG.pv_from_gl_bal_flag = 'Y' THEN
292 v_sql :=
293 v_insert ||
294 'SELECT' || pv_nl ||
295 ' glb.ROWID,' || pv_nl ||
296 ' null,' || pv_nl ||
297 ' ''R'',' || pv_nl ||
298 ' param.OUTPUT_DATASET_CODE,' || pv_nl ||
299 ' param.CAL_PERIOD_ID,' || pv_nl ||
300 ' ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
301 ' ' || FEM_GL_POST_PROCESS_PKG.pv_gl_source_system_code || ',' || pv_nl ||
302 ' ccmap.COMPANY_COST_CENTER_ORG_ID,' || pv_nl ||
303 ' decode(glb.currency_code,' || pv_nl ||
304 ' ''STAT'', 10000,' || pv_nl ||
305 ' decode(xat_acct.dim_attribute_varchar_member,' || pv_nl ||
306 ' ''REVENUE'', 455,' || pv_nl ||
307 ' ''EXPENSE'', 457,' || pv_nl ||
308 ' 100)),' || pv_nl ||
309 v_ccmap_selection ||
310 ' glb.CURRENCY_CODE,' || pv_nl ||
311 ' decode(glb.translated_flag,' || pv_nl ||
312 ' ''Y'', ''TRANSLATED'',' || pv_nl ||
313 ' ''N'', ''TRANSLATED'',' || pv_nl ||
314 ' ''ENTERED''),' || pv_nl ||
315 ' decode(ccmap.code_combination_id, null, ''Y'', ''N''),' || pv_nl ||
316 ' glb.code_combination_id,' || pv_nl ||
317 ' xat_sign.number_assign_value *' || pv_nl ||
318 ' decode(xat_acct.dim_attribute_varchar_member,' || pv_nl ||
319 ' ''REVENUE'', nvl(glb.period_net_dr,0) -' || pv_nl ||
320 ' nvl(glb.period_net_cr,0),' || pv_nl ||
321 ' ''EXPENSE'', nvl(glb.period_net_dr,0) -' || pv_nl ||
322 ' nvl(glb.period_net_cr,0),' || pv_nl ||
323 ' nvl(glb.begin_balance_dr,0)+nvl(glb.period_net_dr,0) -' || pv_nl ||
324 ' nvl(glb.begin_balance_cr,0)-nvl(glb.period_net_cr,0)),' || pv_nl;
325
326 IF p_maintain_qtd = 'Y' THEN
327 v_sql := v_sql ||
328 ' xat_sign.number_assign_value *' || pv_nl ||
329 ' (nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0) -' || pv_nl ||
330 ' nvl(glb.begin_balance_cr,0) - nvl(glb.period_net_cr,0)),' || pv_nl;
331 ELSE
332 v_sql := v_sql ||
333 ' null,' || pv_nl;
334 END IF;
335
336 v_sql := v_sql ||
337 ' xat_sign.number_assign_value *' || pv_nl ||
338 ' (nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0) -' || pv_nl ||
339 ' nvl(glb.begin_balance_cr,0) - nvl(glb.period_net_cr,0)), ' || pv_nl ||
340 ' xat_sign.number_assign_value *' || pv_nl ||
341 ' decode' || pv_nl ||
342 ' (glb.translated_flag,' || pv_nl ||
343 ' ''Y'', null,' || pv_nl ||
344 ' ''N'', null,' || pv_nl ||
345 ' ''R'', decode' || pv_nl ||
346 ' (xat_acct.dim_attribute_varchar_member,' || pv_nl ||
347 ' ''REVENUE'', nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
348 ' nvl(glb.period_net_cr_beq,0),' || pv_nl ||
349 ' ''EXPENSE'', nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
350 ' nvl(glb.period_net_cr_beq,0),' || pv_nl ||
351 ' nvl(glb.begin_balance_dr_beq,0) +' || pv_nl ||
352 ' nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
353 ' nvl(glb.begin_balance_cr_beq,0) -' || pv_nl ||
354 ' nvl(glb.period_net_cr_beq,0)),' || pv_nl ||
355 ' decode' || pv_nl ||
356 ' (xat_acct.dim_attribute_varchar_member,' || pv_nl ||
357 ' ''REVENUE'', nvl(glb.period_net_dr,0) -' || pv_nl ||
358 ' nvl(glb.period_net_cr,0),' || pv_nl ||
359 ' ''EXPENSE'', nvl(glb.period_net_dr,0) -' || pv_nl ||
360 ' nvl(glb.period_net_cr,0),' || pv_nl ||
361 ' nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0) -' || pv_nl ||
362 ' nvl(glb.begin_balance_cr,0) - nvl(glb.period_net_cr,0))), ' || pv_nl;
363
364 IF p_maintain_qtd = 'Y' THEN
365 v_sql := v_sql ||
366 ' xat_sign.number_assign_value *' || pv_nl ||
367 ' decode(glb.translated_flag,' || pv_nl ||
368 ' ''Y'', null,' || pv_nl ||
369 ' ''N'', null,' || pv_nl ||
370 ' ''R'', nvl(glb.begin_balance_dr_beq,0) +' || pv_nl ||
371 ' nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
372 ' nvl(glb.begin_balance_cr_beq,0) -' || pv_nl ||
373 ' nvl(glb.period_net_cr_beq,0),' || pv_nl ||
374 ' nvl(glb.begin_balance_dr,0)+nvl(glb.period_net_dr,0) -' || pv_nl ||
375 ' nvl(glb.begin_balance_cr,0)-nvl(glb.period_net_cr,0)),' || pv_nl;
376 ELSE
377 v_sql := v_sql ||
378 ' null,' || pv_nl;
379 END IF;
380
381 v_sql := v_sql ||
382 ' xat_sign.number_assign_value *' || pv_nl ||
383 ' decode(glb.translated_flag,' || pv_nl ||
384 ' ''Y'', null,' || pv_nl ||
385 ' ''N'', null,' || pv_nl ||
386 ' ''R'', nvl(glb.begin_balance_dr_beq,0) +' || pv_nl ||
387 ' nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
388 ' nvl(glb.begin_balance_cr_beq,0) -' || pv_nl ||
389 ' nvl(glb.period_net_cr_beq,0),' || pv_nl ||
390 ' nvl(glb.begin_balance_dr,0)+nvl(glb.period_net_dr,0) -' || pv_nl ||
391 ' nvl(glb.begin_balance_cr,0)-nvl(glb.period_net_cr,0)),' || pv_nl ||
392 ' nvl(glb.period_net_dr,0),' || pv_nl ||
393 ' nvl(glb.period_net_cr,0),' || pv_nl ||
394 ' nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0),' || pv_nl ||
395 ' nvl(glb.begin_balance_cr,0) + nvl(glb.period_net_cr,0)' || pv_nl ||
396 'FROM' || pv_nl ||
397 ' FEM_INTG_EXEC_PARAMS_GT param,' || pv_nl ||
398 ' GL_BALANCES glb,' || pv_nl;
399
400 IF FEM_GL_POST_PROCESS_PKG.pv_bsv_option = 'SPECIFIC' THEN
401 v_sql := v_sql ||
402 ' FEM_INTG_BAL_DEF_BSVS bsv,' || pv_nl;
403 END IF;
404
405 v_sql := v_sql ||
406 ' GL_CODE_COMBINATIONS cc,' || pv_nl ||
407 ' FEM_INTG_OGL_CCID_MAP ccmap,' || pv_nl ||
408 ' FEM_EXT_ACCT_TYPES_ATTR xat_acct,' || pv_nl ||
409 ' FEM_EXT_ACCT_TYPES_ATTR xat_sign' || pv_nl ||
410 'WHERE param.error_code IS NULL' || pv_nl;
411
412 IF FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd <> 'BUDGET' THEN
413 v_sql := v_sql ||
414 'AND param.load_method_code = ''S''' || pv_nl;
415 END IF;
416
417 v_sql := v_sql ||
418 'AND param.request_id IS NOT NULL' || pv_nl ||
419 'AND glb.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
420 'AND glb.period_name = param.period_name' || pv_nl;
421
422 IF FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd = 'ACTUAL' THEN
423 v_sql := v_sql ||
424 'AND glb.actual_flag = ''A''' || pv_nl;
425 ELSIF FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd = 'BUDGET' THEN
426 v_sql := v_sql ||
427 'AND glb.actual_flag = ''B''' || pv_nl ||
428 'AND glb.budget_version_id = param.budget_id' || pv_nl;
429 ELSE -- encumbrances
430 v_sql := v_sql ||
431 'AND glb.actual_flag = ''E''' || pv_nl ||
432 'AND glb.encumbrance_type_id = param.encumbrance_type_id' || pv_nl;
433 END IF;
434
435 IF FEM_GL_POST_PROCESS_PKG.pv_bsv_option = 'SPECIFIC' THEN
436 v_sql := v_sql ||
437 'AND bsv.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
438 'AND bsv.balance_seg_value = cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || pv_nl;
439 END IF;
440
441
442 v_sql := v_sql ||
443 'AND cc.code_combination_id = glb.code_combination_id' || pv_nl ||
444 'AND cc.template_id IS NULL' || pv_nl ||
445 'AND ccmap.code_combination_id (+)= cc.code_combination_id' || pv_nl ||
446 v_ccmap_join ||
447 'AND xat_acct.attribute_id (+)= ' || v_xat_basic_type_attr_id || pv_nl ||
448 'AND xat_acct.version_id (+)= ' || v_xat_basic_type_v_id || pv_nl ||
449 'AND xat_acct.ext_account_type_code (+)= ccmap.extended_account_type' || pv_nl ||
450 'AND xat_sign.attribute_id (+)= ' || v_xat_sign_attr_id || pv_nl ||
451 'AND xat_sign.version_id (+)= ' || v_xat_sign_v_id || pv_nl ||
452 'AND xat_sign.ext_account_type_code (+)= ccmap.extended_account_type' || pv_nl;
453
454 -- If there is a range, only pick up delta loads for bsv's in the range
455 IF p_bsv_range_low IS NOT NULL AND p_bsv_range_high IS NOT NULL THEN
456 v_sql := v_sql ||
457 'AND (NOT EXISTS' || pv_nl ||
458 ' (SELECT 1' || pv_nl ||
459 ' FROM fem_balances fb_curr' || pv_nl ||
460 ' WHERE fb_curr.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
461 ' AND fb_curr.dataset_code = param.output_dataset_code' || pv_nl ||
462 ' AND fb_curr.cal_period_id = param.cal_period_id)' || pv_nl ||
463 ' OR cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || ' BETWEEN ' || p_bsv_range_low || ' AND ' || p_bsv_range_high || ')' || pv_nl;
464 END IF;
465
466 -- Add the joins for the currency information here
467 v_sql := v_sql || v_ccy_join;
468
469
470 -- Print the snapshot upload statement
471 FOR iterator IN 1..trunc((length(v_sql)+1499)/1500) LOOP
472 FEM_ENGINES_PKG.Tech_Message
473 (p_severity => pc_log_level_statement,
474 p_module => v_module,
475 p_app_name => 'FEM',
476 p_msg_name => 'FEM_GL_POST_204',
477 p_token1 => 'VAR_NAME',
478 p_value1 => 'v_sql: ' || iterator,
479 p_token2 => 'VAR_VAL',
480 p_value2 => substr(v_sql, iterator*1500-1499, 1500));
481 END LOOP;
482
483 EXECUTE IMMEDIATE v_sql;
484
485 v_intermediate_rows_inserted := SQL%ROWCOUNT;
486
487 -- Print the number of rows inserted
488 FEM_ENGINES_PKG.Tech_Message(
489 p_severity => pc_log_level_statement,
490 p_module => v_module,
491 p_msg_text => 'Inserted ' || TO_CHAR(v_intermediate_rows_inserted) ||
492 ' rows into FEM_BAL_POST_INTERIM_GT');
493
494 x_num_rows_inserted := v_intermediate_rows_inserted;
495 END IF;
496
497
498
499 -- If we are doing any incremental loads, do them here
500 IF FEM_GL_POST_PROCESS_PKG.pv_from_gl_delta_flag = 'Y' THEN
501
502 --start bug fix 5585720
503 v_flex_query_stmt := get_flex_values_query;
504
505 v_sql :=
506 'INSERT INTO FEM_INTG_DELTA_LOADS dl' || pv_nl ||
507 '( LEDGER_ID' || pv_nl ||
508 ' ,DATASET_CODE' || pv_nl ||
509 ' ,CAL_PERIOD_ID' || pv_nl ||
510 ' ,DELTA_RUN_ID' || pv_nl ||
511 ' ,LOADED_FLAG' || pv_nl ||
512 ' ,CREATION_DATE' || pv_nl ||
513 ' ,CREATED_BY' || pv_nl ||
514 ' ,LAST_UPDATE_DATE' || pv_nl ||
515 ' ,LAST_UPDATED_BY' || pv_nl ||
516 ' ,LAST_UPDATE_LOGIN' || pv_nl ||
517 ' ,BALANCE_SEG_VALUE)' || pv_nl ||
518 ' SELECT '|| pv_nl ||
519 FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
520 ' ,param_in.output_dataset_code' || pv_nl ||
521 ' ,param_in.cal_period_id' || pv_nl ||
522 ' ,-1' || pv_nl ||
523 ' ,''Y''' || pv_nl ||
524 ' ,sysdate' || pv_nl ||
525 ' ,'|| FEM_GL_POST_PROCESS_PKG.pv_user_id|| pv_nl ||
526 ' ,sysdate' || pv_nl ||
527 ' ,'|| FEM_GL_POST_PROCESS_PKG.pv_user_id || pv_nl ||
528 ' ,'||FEM_GL_POST_PROCESS_PKG.pv_login_id || pv_nl ||
529 ' ,flex.flex_value' || pv_nl ||
530 'FROM FEM_INTG_EXEC_PARAMS_GT param_in,' || pv_nl ||
531 ' ( '||v_flex_query_stmt||' ) flex' || pv_nl ||
532 'WHERE param_in.error_code IS NULL' || pv_nl ||
533 'AND param_in.request_id IS NOT NULL' || pv_nl ||
534 'AND param_in.load_method_code = ''I''' || pv_nl ||
535 'AND NOT EXISTS ( SELECT 1' || pv_nl ||
536 ' FROM FEM_INTG_DELTA_LOADS' || pv_nl ||
537 ' WHERE LEDGER_ID= '||FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
538 ' AND DATASET_CODE = param_in.output_dataset_code' || pv_nl ||
539 ' AND CAL_PERIOD_ID = param_in.cal_period_id' || pv_nl ||
540 ' AND BALANCE_SEG_VALUE = flex.flex_value)';
541
542 FEM_ENGINES_PKG.Tech_Message(
543 p_severity => pc_log_level_statement,
544 p_module => v_module,
545 p_msg_text => v_sql );
546
547 EXECUTE IMMEDIATE v_sql;
548
549 --end bug fix 5585720
550
551 -- In this case, v_sql will not hold the entire sql statement, since
552 -- two very similar statements must be executed. Instead, v_sql will
553 -- hold the common parts of the two statements, and the rest will be
554 -- appended when the statements are executed.
555 v_sql :=
556 v_insert ||
557 'SELECT' || pv_nl ||
558 ' glb.ROWID,' || pv_nl ||
559 ' glb.delta_run_id,' || pv_nl ||
560 ' ''A'',' || pv_nl ||
561 ' param.OUTPUT_DATASET_CODE,' || pv_nl ||
562 ' param.CAL_PERIOD_ID,' || pv_nl ||
563 ' ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
564 ' ' || FEM_GL_POST_PROCESS_PKG.pv_gl_source_system_code || ',' || pv_nl ||
565 ' ccmap.COMPANY_COST_CENTER_ORG_ID,' || pv_nl ||
566 ' decode(glb.currency_code,' || pv_nl ||
567 ' ''STAT'', 10000,' || pv_nl ||
568 ' decode(xat_acct.dim_attribute_varchar_member,' || pv_nl ||
569 ' ''REVENUE'', 455,' || pv_nl ||
570 ' ''EXPENSE'', 457,' || pv_nl ||
571 ' 100)),' || pv_nl ||
572 v_ccmap_selection ||
573 ' glb.CURRENCY_CODE,' || pv_nl ||
574 ' decode(glb.translated_flag,' || pv_nl ||
575 ' ''Y'', ''TRANSLATED'',' || pv_nl ||
576 ' ''N'', ''TRANSLATED'',' || pv_nl ||
577 ' ''ENTERED''),' || pv_nl ||
578 ' decode(ccmap.code_combination_id, null, ''Y'', ''N''), ' || pv_nl ||
579 ' glb.code_combination_id,' || pv_nl ||
580 ' xat_sign.number_assign_value *' || pv_nl ||
581 ' decode(xat_acct.dim_attribute_varchar_member,' || pv_nl ||
582 ' ''REVENUE'',nvl(glb.period_net_dr,0)-nvl(glb.period_net_cr,0),' || pv_nl ||
583 ' ''EXPENSE'',nvl(glb.period_net_dr,0)-nvl(glb.period_net_cr,0),' || pv_nl ||
584 ' nvl(glb.begin_balance_dr,0)+nvl(glb.period_net_dr,0)-' || pv_nl ||
585 ' nvl(glb.begin_balance_cr,0)-nvl(glb.period_net_cr,0)), ' || pv_nl;
586
587 IF p_maintain_qtd = 'Y' THEN
588 v_sql := v_sql ||
589 ' xat_sign.number_assign_value *' || pv_nl ||
590 ' (nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0) -' || pv_nl ||
591 ' nvl(glb.begin_balance_cr,0) - nvl(glb.period_net_cr,0)),' || pv_nl;
592 ELSE
593 v_sql := v_sql ||
594 ' null,' || pv_nl;
595 END IF;
596
597 v_sql := v_sql ||
598 ' xat_sign.number_assign_value *' || pv_nl ||
599 ' (nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0) -' || pv_nl ||
600 ' nvl(glb.begin_balance_cr,0) - nvl(glb.period_net_cr,0)),' || pv_nl ||
601 ' xat_sign.number_assign_value *' || pv_nl ||
602 ' decode' || pv_nl ||
603 ' (glb.translated_flag,' || pv_nl ||
604 ' ''Y'', null,' || pv_nl ||
605 ' ''N'', null,' || pv_nl ||
606 ' ''R'', decode' || pv_nl ||
607 ' (xat_acct.dim_attribute_varchar_member,' || pv_nl ||
608 ' ''REVENUE'', nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
609 ' nvl(glb.period_net_cr_beq,0),' || pv_nl ||
610 ' ''EXPENSE'', nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
611 ' nvl(glb.period_net_cr_beq,0),' || pv_nl ||
612 ' nvl(glb.begin_balance_dr_beq,0) +' || pv_nl ||
613 ' nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
614 ' nvl(glb.begin_balance_cr_beq,0) -' || pv_nl ||
615 ' nvl(glb.period_net_cr_beq,0)),' || pv_nl ||
616 ' decode' || pv_nl ||
617 ' (xat_acct.dim_attribute_varchar_member,' || pv_nl ||
618 ' ''REVENUE'', nvl(glb.period_net_dr,0) - nvl(glb.period_net_cr,0),' || pv_nl ||
619 ' ''EXPENSE'', nvl(glb.period_net_dr,0) - nvl(glb.period_net_cr,0),' || pv_nl ||
620 ' nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0) -' || pv_nl ||
621 ' nvl(glb.begin_balance_cr,0) - nvl(glb.period_net_cr,0))), ' || pv_nl;
622
623 IF p_maintain_qtd = 'Y' THEN
624 v_sql := v_sql ||
625 ' xat_sign.number_assign_value *' || pv_nl ||
626 ' decode(glb.translated_flag,' || pv_nl ||
627 ' ''Y'', null,' || pv_nl ||
628 ' ''N'', null,' || pv_nl ||
629 ' ''R'', nvl(glb.begin_balance_dr_beq,0) +' || pv_nl ||
630 ' nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
631 ' nvl(glb.begin_balance_cr_beq,0) -' || pv_nl ||
632 ' nvl(glb.period_net_cr_beq,0),' || pv_nl ||
633 ' nvl(glb.begin_balance_dr,0)+nvl(glb.period_net_dr,0) -' || pv_nl ||
634 ' nvl(glb.begin_balance_cr,0)-nvl(glb.period_net_cr,0)),' || pv_nl;
635 ELSE
636 v_sql := v_sql ||
637 ' null,' || pv_nl;
638 END IF;
639
640 v_sql := v_sql ||
641 ' xat_sign.number_assign_value *' || pv_nl ||
642 ' decode(glb.translated_flag,' || pv_nl ||
643 ' ''Y'', null,' || pv_nl ||
644 ' ''N'', null,' || pv_nl ||
645 ' ''R'', nvl(glb.begin_balance_dr_beq,0) +' || pv_nl ||
646 ' nvl(glb.period_net_dr_beq,0) -' || pv_nl ||
647 ' nvl(glb.begin_balance_cr_beq,0) -' || pv_nl ||
648 ' nvl(glb.period_net_cr_beq,0),' || pv_nl ||
649 ' nvl(glb.begin_balance_dr,0)+nvl(glb.period_net_dr,0) -' || pv_nl ||
650 ' nvl(glb.begin_balance_cr,0)-nvl(glb.period_net_cr,0)),' || pv_nl ||
651 ' nvl(glb.period_net_dr,0),' || pv_nl ||
652 ' nvl(glb.period_net_cr,0),' || pv_nl ||
653 ' nvl(glb.begin_balance_dr,0) + nvl(glb.period_net_dr,0),' || pv_nl ||
654 ' nvl(glb.begin_balance_cr,0) + nvl(glb.period_net_cr,0)' || pv_nl ||
655 'FROM' || pv_nl ||
656 ' FEM_INTG_EXEC_PARAMS_GT param,' || pv_nl ||
657 ' GL_BALANCES_DELTA glb,' || pv_nl ||
658 ' FEM_INTG_DELTA_LOADS dl,' || pv_nl;
659
660 IF FEM_GL_POST_PROCESS_PKG.pv_bsv_option = 'SPECIFIC' THEN
661 v_sql := v_sql ||
662 ' FEM_INTG_BAL_DEF_BSVS bsv,' || pv_nl;
663 END IF;
664
665 v_sql := v_sql ||
666 ' GL_CODE_COMBINATIONS cc,' || pv_nl ||
667 ' FEM_INTG_OGL_CCID_MAP ccmap,' || pv_nl ||
668 ' FEM_EXT_ACCT_TYPES_ATTR xat_acct,' || pv_nl ||
669 ' FEM_EXT_ACCT_TYPES_ATTR xat_sign' || pv_nl ||
670 'WHERE param.load_method_code = ''I''' || pv_nl ||
671 'AND param.error_code IS NULL' || pv_nl ||
672 'AND param.request_id IS NOT NULL' || pv_nl ||
673 'AND dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
674 'AND dl.dataset_code = param.output_dataset_code' || pv_nl ||
675 'AND dl.cal_period_id = param.cal_period_id' || pv_nl ||
676 'AND glb.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
677 'AND glb.period_name = param.period_name' || pv_nl;
678
679 IF FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd = 'ACTUAL' THEN
680 v_sql := v_sql ||
681 'AND glb.actual_flag = ''A''' || pv_nl;
682 ELSIF FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd = 'BUDGET' THEN
683 v_sql := v_sql ||
684 'AND glb.actual_flag = ''B''' || pv_nl ||
685 'AND glb.budget_version_id = param.budget_id' || pv_nl;
686 ELSE -- encumbrances
687 v_sql := v_sql ||
688 'AND glb.actual_flag = ''E''' || pv_nl ||
689 'AND glb.encumbrance_type_id = param.encumbrance_type_id' || pv_nl;
690 END IF;
691
692 IF FEM_GL_POST_PROCESS_PKG.pv_bsv_option = 'SPECIFIC' THEN
693 v_sql := v_sql ||
694 'AND bsv.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
695 'AND bsv.balance_seg_value = cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || pv_nl;
696 END IF;
697
698 -- If there is a range specified, only pull data in that range
699 v_sql := v_sql ||
700 'AND cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || ' = dl.balance_seg_value' || pv_nl;
701
702 v_sql := v_sql ||
703 'AND cc.code_combination_id = glb.code_combination_id' || pv_nl ||
704 'AND cc.template_id IS NULL' || pv_nl ||
705 'AND ccmap.code_combination_id (+)= cc.code_combination_id' || pv_nl ||
706 v_ccmap_join ||
707 'AND xat_acct.attribute_id (+)= ' || v_xat_basic_type_attr_id || pv_nl ||
708 'AND xat_acct.version_id (+)= ' || v_xat_basic_type_v_id || pv_nl ||
709 'AND xat_acct.ext_account_type_code (+)= ccmap.extended_account_type' || pv_nl ||
710 'AND xat_sign.attribute_id (+)= ' || v_xat_sign_attr_id || pv_nl ||
711 'AND xat_sign.version_id (+)= ' || v_xat_sign_v_id || pv_nl ||
712 'AND xat_sign.ext_account_type_code (+)= ccmap.extended_account_type' || pv_nl;
713
714 -- If there is a range, only pick up delta loads for bsv's in the range
715 IF p_bsv_range_low IS NOT NULL AND p_bsv_range_high IS NOT NULL THEN
716 v_sql := v_sql ||
717 'AND dl.balance_seg_value BETWEEN ' || p_bsv_range_low || ' AND ' ||
718 p_bsv_range_high || pv_nl;
719 END IF;
720
721 -- Add the joins for the currency information here
722 v_sql := v_sql || v_ccy_join;
723
724 v_sql_incr := v_sql ||
725 'AND dl.loaded_flag = ''N''' || pv_nl ||
726 'AND glb.delta_run_id = dl.delta_run_id';
727
728
729 -- Print the incremental upload statement
730 FOR iterator IN 1..trunc((length(v_sql_incr)+1499)/1500) LOOP
731 FEM_ENGINES_PKG.Tech_Message
732 (p_severity => pc_log_level_statement,
733 p_module => v_module,
734 p_app_name => 'FEM',
735 p_msg_name => 'FEM_GL_POST_204',
736 p_token1 => 'VAR_NAME',
737 p_value1 => 'v_sql_incr: ' || iterator,
738 p_token2 => 'VAR_VAL',
739 p_value2 => substr(v_sql_incr, iterator*1500-1499, 1500));
740 END LOOP;
741
742 EXECUTE IMMEDIATE v_sql_incr;
743
744 v_intermediate_rows_inserted := SQL%ROWCOUNT;
745
746 -- Print the number of rows inserted
747 FEM_ENGINES_PKG.Tech_Message(
748 p_severity => pc_log_level_statement,
749 p_module => v_module,
750 p_msg_text => 'Inserted ' || TO_CHAR(v_intermediate_rows_inserted) ||
751 ' rows into FEM_BAL_POST_INTERIM_GT');
752
753 x_num_rows_inserted := x_num_rows_inserted + v_intermediate_rows_inserted;
754
755
756 v_sql_incr := v_sql ||
757 'AND dl.loaded_flag = ''Y''' || pv_nl ||
758 'AND glb.delta_run_id BETWEEN dl.delta_run_id + 1 AND ' || FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id;
759
760 -- Print the incremental upload statement
761 FOR iterator IN 1..trunc((length(v_sql_incr)+1499)/1500) LOOP
762 FEM_ENGINES_PKG.Tech_Message
763 (p_severity => pc_log_level_statement,
764 p_module => v_module,
765 p_app_name => 'FEM',
766 p_msg_name => 'FEM_GL_POST_204',
767 p_token1 => 'VAR_NAME',
768 p_value1 => 'v_sql_incr: ' || iterator,
769 p_token2 => 'VAR_VAL',
770 p_value2 => substr(v_sql_incr, iterator*1500-1499, 1500));
771 END LOOP;
772
773 EXECUTE IMMEDIATE v_sql_incr;
774
775 v_intermediate_rows_inserted := SQL%ROWCOUNT;
776
777 -- Print the number of rows inserted
778 FEM_ENGINES_PKG.Tech_Message(
779 p_severity => pc_log_level_statement,
780 p_module => v_module,
781 p_msg_text => 'Inserted ' || TO_CHAR(v_intermediate_rows_inserted) ||
782 ' rows into FEM_BAL_POST_INTERIM_GT');
783
784 x_num_rows_inserted := x_num_rows_inserted + v_intermediate_rows_inserted;
785 END IF;
786
787 OPEN unmapped_exists_c;
788 FETCH unmapped_exists_c INTO dummy;
789 IF unmapped_exists_c%FOUND THEN
790 x_completion_code := 1;
791 END IF;
792 CLOSE unmapped_exists_c;
793
794 FEM_ENGINES_PKG.Tech_Message
795 (p_severity => pc_log_level_procedure,
796 p_module => v_module,
797 p_msg_text => 'END');
798
799 EXCEPTION
800 WHEN OTHERS THEN
801 x_completion_code := 2;
802
803 FEM_ENGINES_PKG.User_Message
804 (p_app_name => 'FEM',
805 p_msg_name => 'FEM_GL_POST_215',
806 p_token1 => 'ERR_MSG',
807 p_value1 => SQLERRM);
808
809 FEM_ENGINES_PKG.Tech_Message
810 (p_severity => pc_log_level_unexpected,
811 p_module => v_module,
812 p_app_name => 'FEM',
813 p_msg_name => 'FEM_GL_POST_215',
814 p_token1 => 'ERR_MSG',
815 p_value1 => SQLERRM);
816
817 FEM_ENGINES_PKG.Tech_Message
818 (p_severity => pc_log_level_procedure,
819 p_module => v_module,
820 p_msg_text => 'END');
821 END Load_Std_Balances;
822
823
824
825
826 PROCEDURE Load_Avg_Balances( x_completion_code OUT NOCOPY NUMBER,
827 x_num_rows_inserted OUT NOCOPY NUMBER,
828 p_effective_date DATE,
829 p_bsv_range_low VARCHAR2,
830 p_bsv_range_high VARCHAR2) IS
831
832 -- Since the statements are only going to vary by a few parts of the
833 -- string, it makes more sense to create a template, and then replace the
834 -- necessary parts of the statement with the information for each period
835 v_sql_template VARCHAR2(32767);
836 v_sql VARCHAR2(32767);
837
838 v_intermediate_rows_inserted NUMBER;
839
840 -- This is used to loop through all periods for which average balances
841 -- should be uploaded
842 CURSOR load_periods_c IS
843 SELECT DISTINCT
844 param.period_name,
845 ps.end_date period_end_date,
846 ps.start_date period_start_date,
847 ps.quarter_start_date,
848 ps.year_start_date
849 FROM FEM_INTG_EXEC_PARAMS_GT param,
850 GL_PERIOD_STATUSES ps
851 WHERE param.error_code IS NULL
852 AND param.request_id IS NOT NULL
853 AND ps.application_id = 101
854 AND ps.ledger_id = FEM_GL_POST_PROCESS_PKG.pv_ledger_id
855 AND ps.period_name = param.period_name;
856
857 v_end_date DATE; -- Date to be used in calculating average balances
858
859 v_xat_basic_type_attr_id NUMBER;
860 v_xat_basic_type_v_id NUMBER;
861 v_xat_sign_attr_id NUMBER;
862 v_xat_sign_v_id NUMBER;
863
864 v_error_code NUMBER;
865
866 v_module VARCHAR2(100);
867 BEGIN
868 FEM_ENGINES_PKG.Tech_Message
869 (p_severity => pc_log_level_procedure,
870 p_module => v_module,
871 p_msg_text => 'BEGIN');
872
873 pv_nl := '
874 ';
875 v_module := 'fem.plsql.fem_intg_bal_eng_load.load_avg_balances';
876
877 x_completion_code := 0;
878 x_num_rows_inserted := 0;
879
880 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
881 p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_ext_acct_type_dim_id,
882 p_attr_label => 'BASIC_ACCOUNT_TYPE_CODE',
883 x_attr_id => v_xat_basic_type_attr_id,
884 x_ver_id => v_xat_basic_type_v_id,
885 x_err_code => v_error_code);
886
887 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
888 p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_ext_acct_type_dim_id,
889 p_attr_label => 'SIGN',
890 x_attr_id => v_xat_sign_attr_id,
891 x_ver_id => v_xat_sign_v_id,
892 x_err_code => v_error_code);
893
894 -- Print the As-of-date
895 FEM_ENGINES_PKG.Tech_Message(
896 p_severity => pc_log_level_statement,
897 p_module => v_module,
898 p_app_name => 'FEM',
899 p_msg_name => 'FEM_GL_POST_204',
900 p_token1 => 'VAR_NAME',
901 p_value1 => 'p_effective_date',
902 p_token2 => 'VAR_VAL',
903 p_value2 => p_effective_date);
904
905
906 -- We will now create the load statement template. The portions inside the
907 -- '<<<' and '>>>' will be replaced by actual numbers when we actually run
908 -- the statement itself.
909 v_sql_template :=
910 'INSERT INTO FEM_BAL_POST_INTERIM_GT(INTERFACE_ROWID, BAL_POST_TYPE_CODE, ' ||
911 'DATASET_CODE, CAL_PERIOD_ID, LEDGER_ID, SOURCE_SYSTEM_CODE, ' ||
912 'COMPANY_COST_CENTER_ORG_ID, FINANCIAL_ELEM_ID, PRODUCT_ID, ' ||
913 'NATURAL_ACCOUNT_ID, CHANNEL_ID, LINE_ITEM_ID, PROJECT_ID, CUSTOMER_ID, ' ||
914 'ENTITY_ID, INTERCOMPANY_ID, TASK_ID, USER_DIM1_ID, USER_DIM2_ID, ' ||
915 'USER_DIM3_ID, USER_DIM4_ID, USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID, ' ||
916 'USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID, CURRENCY_CODE, ' ||
917 'CURRENCY_TYPE_CODE, POSTING_ERROR_FLAG, CODE_COMBINATION_ID, ' ||
918 'XTD_BALANCE_E, QTD_BALANCE_E, YTD_BALANCE_E, XTD_BALANCE_F, ' ||
919 'QTD_BALANCE_F, YTD_BALANCE_F, PTD_DEBIT_BALANCE_E, PTD_CREDIT_BALANCE_E, ' ||
920 'YTD_DEBIT_BALANCE_E, YTD_CREDIT_BALANCE_E)' || pv_nl ||
921 'SELECT' || pv_nl ||
922 ' glb.ROWID,' || pv_nl ||
923 ' ''R'',' || pv_nl ||
924 ' param.OUTPUT_DATASET_CODE,' || pv_nl ||
925 ' param.CAL_PERIOD_ID,' || pv_nl ||
926 ' ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
927 ' ' || FEM_GL_POST_PROCESS_PKG.pv_gl_source_system_code || ',' || pv_nl ||
928 ' ccmap.COMPANY_COST_CENTER_ORG_ID,' || pv_nl ||
929 ' 140,' || pv_nl ||
930 ' ccmap.PRODUCT_ID,' || pv_nl ||
931 ' ccmap.NATURAL_ACCOUNT_ID,' || pv_nl ||
932 ' ccmap.CHANNEL_ID,' || pv_nl ||
933 ' ccmap.LINE_ITEM_ID,' || pv_nl ||
934 ' ccmap.PROJECT_ID,' || pv_nl ||
935 ' ccmap.CUSTOMER_ID,' || pv_nl ||
936 ' ccmap.ENTITY_ID,' || pv_nl ||
937 ' ccmap.INTERCOMPANY_ID,' || pv_nl ||
938 ' ccmap.TASK_ID,' || pv_nl ||
939 ' ccmap.USER_DIM1_ID,' || pv_nl ||
940 ' ccmap.USER_DIM2_ID,' || pv_nl ||
941 ' ccmap.USER_DIM3_ID,' || pv_nl ||
942 ' ccmap.USER_DIM4_ID,' || pv_nl ||
943 ' ccmap.USER_DIM5_ID,' || pv_nl ||
944 ' ccmap.USER_DIM6_ID,' || pv_nl ||
945 ' ccmap.USER_DIM7_ID,' || pv_nl ||
946 ' ccmap.USER_DIM8_ID,' || pv_nl ||
947 ' ccmap.USER_DIM9_ID,' || pv_nl ||
948 ' ccmap.USER_DIM10_ID,' || pv_nl ||
949 ' glb.CURRENCY_CODE,' || pv_nl ||
950 ' decode(glb.currency_type,' || pv_nl ||
951 ' ''T'', ''TRANSLATED'',' || pv_nl ||
952 ' ''O'', ''TRANSLATED'',' || pv_nl ||
953 ' ''ENTERED''),' || pv_nl ||
954 ' decode(ccmap.code_combination_id, null, ''Y'', ''N''),' || pv_nl ||
955 ' glb.code_combination_id,' || pv_nl ||
956 ' round(xat_sign.number_assign_value *' || pv_nl ||
957 ' nvl(glb.period_aggregate<<<period_days>>>,0) / <<<period_days>>> / ccy_mau.mau) * ccy_mau.mau,' || pv_nl ||
958 ' round(xat_sign.number_assign_value *' || pv_nl ||
959 ' decode(glb.currency_type,' || pv_nl ||
960 ' ''T'', nvl(glb.quarter_aggregate<<<period_days>>>,0),' || pv_nl ||
961 ' ''O'', nvl(glb.quarter_aggregate<<<period_days>>>,0),' || pv_nl ||
962 ' nvl(glb.opening_quarter_aggregate,0) +' || pv_nl ||
963 ' nvl(glb.period_aggregate<<<period_days>>>,0)) /' || pv_nl ||
964 ' <<<quarter_days>>> / ccy_mau.mau) * ccy_mau.mau,' || pv_nl ||
965 ' round(xat_sign.number_assign_value *' || pv_nl ||
966 ' decode(glb.currency_type,' || pv_nl ||
967 ' ''T'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
968 ' ''O'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
969 ' nvl(glb.opening_year_aggregate,0) +' || pv_nl ||
970 ' nvl(glb.period_aggregate<<<period_days>>>,0)) /' || pv_nl ||
971 ' <<<year_days>>> / ccy_mau.mau) * ccy_mau.mau,' || pv_nl ||
972 ' round(xat_sign.number_assign_value *' || pv_nl ||
973 ' decode(glb.currency_type,' || pv_nl ||
974 ' ''T'', null,' || pv_nl ||
975 ' ''O'', null,' || pv_nl;
976
977 IF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED' THEN
978 v_sql_template := v_sql_template ||
979 ' ''E'', nvl(glbc.period_aggregate<<<period_days>>>,0),' || pv_nl;
980 END IF;
981
982 v_sql_template := v_sql_template ||
983 ' nvl(glb.period_aggregate<<<period_days>>>,0)) /' || pv_nl ||
984 ' <<<period_days>>> / nvl(ccy_mau_c.mau, ccy_mau.mau)) * nvl(ccy_mau_c.mau, ccy_mau.mau),' || pv_nl ||
985 ' round(xat_sign.number_assign_value *' || pv_nl ||
986 ' decode(glb.currency_type,' || pv_nl ||
987 ' ''T'', null,' || pv_nl ||
988 ' ''O'', null,' || pv_nl;
989
990 IF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED' THEN
991 v_sql_template := v_sql_template ||
992 ' ''E'', nvl(glbc.opening_quarter_aggregate,0) +' || pv_nl ||
993 ' nvl(glbc.period_aggregate<<<period_days>>>,0),' || pv_nl;
994 END IF;
995
996 v_sql_template := v_sql_template ||
997 ' nvl(glb.opening_quarter_aggregate,0) +' || pv_nl ||
998 ' nvl(glb.period_aggregate<<<period_days>>>,0)) /' || pv_nl ||
999 ' <<<quarter_days>>> / nvl(ccy_mau_c.mau, ccy_mau.mau)) * nvl(ccy_mau_c.mau, ccy_mau.mau),' || pv_nl ||
1000 ' round(xat_sign.number_assign_value *' || pv_nl ||
1001 ' decode(glb.currency_type,' || pv_nl ||
1002 ' ''T'', null,' || pv_nl ||
1003 ' ''O'', null,' || pv_nl;
1004
1005 IF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED' THEN
1006 v_sql_template := v_sql_template ||
1007 ' ''E'', nvl(glbc.opening_year_aggregate,0) +' || pv_nl ||
1008 ' nvl(glbc.period_aggregate<<<period_days>>>,0),' || pv_nl;
1009 END IF;
1010
1011 v_sql_template := v_sql_template ||
1012 ' nvl(glb.opening_year_aggregate,0) +' || pv_nl ||
1013 ' nvl(glb.period_aggregate<<<period_days>>>,0)) /' || pv_nl ||
1014 ' <<<year_days>>> / nvl(ccy_mau_c.mau, ccy_mau.mau)) * nvl(ccy_mau_c.mau, ccy_mau.mau),' || pv_nl ||
1015 ' decode(sign(glb.period_aggregate<<<period_days>>>),' || pv_nl ||
1016 ' -1, null,' || pv_nl ||
1017 ' round(nvl(glb.period_aggregate<<<period_days>>>,0) / <<<period_days>>> / ccy_mau.mau) * ccy_mau.mau),' || pv_nl ||
1018 ' decode(sign(glb.period_aggregate<<<period_days>>>),' || pv_nl ||
1019 ' -1, -round(nvl(glb.period_aggregate<<<period_days>>>,0) / <<<period_days>>> / ccy_mau.mau) * ccy_mau.mau,' || pv_nl ||
1020 ' null),' || pv_nl ||
1021 ' decode(sign(decode(glb.currency_type,' || pv_nl ||
1022 ' ''T'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
1023 ' ''O'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
1024 ' nvl(glb.opening_year_aggregate,0) +' || pv_nl ||
1025 ' nvl(glb.period_aggregate<<<period_days>>>,0))),' || pv_nl ||
1026 ' -1, null,' || pv_nl ||
1027 ' round(decode(glb.currency_type,' || pv_nl ||
1028 ' ''T'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
1029 ' ''O'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
1030 ' nvl(glb.opening_year_aggregate,0) +' || pv_nl ||
1031 ' nvl(glb.period_aggregate<<<period_days>>>,0)) /' || pv_nl ||
1032 ' <<<year_days>>> / ccy_mau.mau) * ccy_mau.mau),' || pv_nl ||
1033 ' decode(sign(decode(glb.currency_type,' || pv_nl ||
1034 ' ''T'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
1035 ' ''O'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
1036 ' nvl(glb.opening_year_aggregate,0) +' || pv_nl ||
1037 ' nvl(glb.period_aggregate<<<period_days>>>,0))),' || pv_nl ||
1038 ' -1, -round(decode(glb.currency_type,' || pv_nl ||
1039 ' ''T'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
1040 ' ''O'', nvl(glb.year_aggregate<<<period_days>>>,0),' || pv_nl ||
1041 ' nvl(glb.opening_year_aggregate,0) +' || pv_nl ||
1042 ' nvl(glb.period_aggregate<<<period_days>>>,0)) /' || pv_nl ||
1043 ' <<<year_days>>> / ccy_mau.mau) * ccy_mau.mau,' || pv_nl ||
1044 ' null)' || pv_nl ||
1045 'FROM' || pv_nl ||
1046 ' FEM_INTG_EXEC_PARAMS_GT param,' || pv_nl ||
1047 ' GL_DAILY_BALANCES glb,' || pv_nl;
1048
1049 IF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED' THEN
1050 v_sql_template := v_sql_template ||
1051 ' GL_DAILY_BALANCES glbc,' || pv_nl;
1052 END IF;
1053
1054 IF FEM_GL_POST_PROCESS_PKG.pv_bsv_option = 'SPECIFIC' THEN
1055 v_sql_template := v_sql_template ||
1056 ' FEM_INTG_BAL_DEF_BSVS bsv,' || pv_nl;
1057 END IF;
1058
1059 v_sql_template := v_sql_template ||
1060 ' GL_CODE_COMBINATIONS cc,' || pv_nl ||
1061 ' FEM_INTG_OGL_CCID_MAP ccmap,' || pv_nl ||
1062 ' FEM_EXT_ACCT_TYPES_ATTR xat_sign,' || pv_nl ||
1063 ' (SELECT currency_code,' || pv_nl ||
1064 ' nvl(minimum_accountable_unit, power(10,-precision)) mau' || pv_nl ||
1065 ' FROM FND_CURRENCIES fccy) ccy_mau,' || pv_nl ||
1066 ' (SELECT currency_code,' || pv_nl ||
1067 ' nvl(minimum_accountable_unit, power(10,-precision)) mau' || pv_nl ||
1068 ' FROM FND_CURRENCIES fccy) ccy_mau_c' || pv_nl ||
1069 'WHERE param.error_code IS NULL' || pv_nl ||
1070 'AND param.request_id IS NOT NULL' || pv_nl ||
1071 'AND param.period_name = ''<<<period_name>>>''' || pv_nl ||
1072 'AND ccy_mau.currency_code = glb.currency_code' || pv_nl;
1073
1074 IF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED' THEN
1075 v_sql_template := v_sql_template ||
1076 'AND ccy_mau_c.currency_code (+)= glbc.currency_code' || pv_nl;
1077 ELSE
1078 v_sql_template := v_sql_template ||
1079 'AND ccy_mau_c.currency_code = glb.currency_code' || pv_nl;
1080 END IF;
1081
1082 v_sql_template := v_sql_template ||
1083 'AND glb.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
1084 'AND glb.period_name = param.period_name' || pv_nl ||
1085 'AND glb.actual_flag = ''A''' || pv_nl ||
1086 'AND glb.currency_code <> ''STAT''' || pv_nl;
1087
1088 IF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED' THEN
1089 v_sql_template := v_sql_template ||
1090 'AND glbc.ledger_id (+)= ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
1091 'AND glbc.code_combination_id (+)= glb.code_combination_id' || pv_nl ||
1092 'AND glbc.currency_code (+)= ''' || FEM_GL_POST_PROCESS_PKG.pv_func_ccy_code || '''' || pv_nl ||
1093 'AND glbc.currency_type (+)= ''C''' || pv_nl ||
1094 'AND glbc.actual_flag (+)= ''A''' || pv_nl ||
1095 'AND glbc.period_name (+)= glb.period_name' || pv_nl ||
1096 'AND glbc.converted_from_currency (+)= glb.currency_code' || pv_nl;
1097 END IF;
1098
1099 IF FEM_GL_POST_PROCESS_PKG.pv_bsv_option = 'SPECIFIC' THEN
1100 v_sql_template := v_sql_template ||
1101 'AND bsv.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
1102 'AND bsv.balance_seg_value = cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || pv_nl;
1103 END IF;
1104
1105 v_sql_template := v_sql_template ||
1106 'AND cc.code_combination_id = glb.code_combination_id' || pv_nl ||
1107 'AND cc.template_id IS NULL' || pv_nl ||
1108 'AND ccmap.code_combination_id (+)= cc.code_combination_id' || pv_nl ||
1109 'AND ccmap.global_vs_combo_id (+)= ' || FEM_GL_POST_PROCESS_PKG.pv_global_vs_combo_id || pv_nl ||
1110 'AND ccmap.COMPANY_COST_CENTER_ORG_ID (+)<> -1' || pv_nl ||
1111 'AND ccmap.NATURAL_ACCOUNT_ID (+)<> -1' || pv_nl ||
1112 'AND ccmap.LINE_ITEM_ID (+)<> -1' || pv_nl ||
1113 'AND ccmap.PRODUCT_ID (+)<> -1' || pv_nl ||
1114 'AND ccmap.CHANNEL_ID (+)<> -1' || pv_nl ||
1115 'AND ccmap.PROJECT_ID (+)<> -1' || pv_nl ||
1116 'AND ccmap.CUSTOMER_ID (+)<> -1' || pv_nl ||
1117 'AND ccmap.ENTITY_ID (+)<> -1' || pv_nl ||
1118 'AND ccmap.INTERCOMPANY_ID (+)<> -1' || pv_nl ||
1119 'AND ccmap.USER_DIM1_ID (+)<> -1' || pv_nl ||
1120 'AND ccmap.USER_DIM2_ID (+)<> -1' || pv_nl ||
1121 'AND ccmap.USER_DIM3_ID (+)<> -1' || pv_nl ||
1122 'AND ccmap.USER_DIM4_ID (+)<> -1' || pv_nl ||
1123 'AND ccmap.USER_DIM5_ID (+)<> -1' || pv_nl ||
1124 'AND ccmap.USER_DIM6_ID (+)<> -1' || pv_nl ||
1125 'AND ccmap.USER_DIM7_ID (+)<> -1' || pv_nl ||
1126 'AND ccmap.USER_DIM8_ID (+)<> -1' || pv_nl ||
1127 'AND ccmap.USER_DIM9_ID (+)<> -1' || pv_nl ||
1128 'AND ccmap.USER_DIM10_ID (+)<> -1' || pv_nl ||
1129 'AND ccmap.TASK_ID (+)<> -1' || pv_nl ||
1130 'AND xat_sign.attribute_id (+)= ' || v_xat_sign_attr_id || pv_nl ||
1131 'AND xat_sign.version_id (+)= ' || v_xat_sign_v_id || pv_nl ||
1132 'AND xat_sign.ext_account_type_code (+)= ccmap.extended_account_type' || pv_nl;
1133
1134
1135 -- If there is a range, only pick up delta loads for bsv's in the range
1136 IF p_bsv_range_low IS NOT NULL AND p_bsv_range_high IS NOT NULL THEN
1137 v_sql_template := v_sql_template ||
1138 'AND (NOT EXISTS' || pv_nl ||
1139 ' (SELECT 1' || pv_nl ||
1140 ' FROM fem_balances fb_curr' || pv_nl ||
1141 ' WHERE fb_curr.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
1142 ' AND fb_curr.dataset_code = param.output_dataset_code' || pv_nl ||
1143 ' AND fb_curr.cal_period_id = param.cal_period_id)' || pv_nl ||
1144 ' OR cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || ' BETWEEN ' || p_bsv_range_low || ' AND ' || p_bsv_range_high || ')' || pv_nl;
1145 END IF;
1146
1147 IF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'ENTERED' THEN
1148 IF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'SPECIFIC' THEN
1149 v_sql_template := v_sql_template ||
1150 'AND (glb.currency_type IN (''U'', ''E'') OR' || pv_nl ||
1151 ' (glb.currency_type IN (''T'', ''O'') AND' || pv_nl ||
1152 ' EXISTS' || pv_nl ||
1153 ' (SELECT 1' || pv_nl ||
1154 ' FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
1155 ' WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
1156 ' AND ccy.xlated_currency_code = glb.currency_code)))' || pv_nl;
1157 ELSIF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'ALL' THEN
1158 v_sql_template := v_sql_template ||
1159 'AND glb.currency_type IN (''U'', ''E'', ''T'', ''O'')' || pv_nl;
1160 ELSE -- no translated balances
1161 v_sql_template := v_sql_template ||
1162 'AND glb.currency_type IN (''U'', ''E'')' || pv_nl;
1163 END IF;
1164 ELSIF FEM_GL_POST_PROCESS_PKG.pv_curr_option = 'FUNCTIONAL' THEN
1165 IF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'SPECIFIC' THEN
1166 v_sql_template := v_sql_template ||
1167 'AND (glb.currency_type = ''U'' OR' || pv_nl ||
1168 ' (glb.currency_type IN (''T'', ''O'') AND' || pv_nl ||
1169 ' EXISTS' || pv_nl ||
1170 ' (SELECT 1' || pv_nl ||
1171 ' FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
1172 ' WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
1173 ' AND ccy.xlated_currency_code = glb.currency_code)))' || pv_nl;
1174 ELSIF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'ALL' THEN
1175 v_sql_template := v_sql_template ||
1176 'AND glb.currency_type IN (''U'', ''T'', ''O'')' || pv_nl;
1177 ELSE -- no translated balances
1178 v_sql_template := v_sql_template ||
1179 'AND glb.currency_type = ''U''' || pv_nl;
1180 END IF;
1181 ELSE -- Translated balances only
1182 IF FEM_GL_POST_PROCESS_PKG.pv_xlated_bal_option = 'SPECIFIC' THEN
1183 v_sql_template := v_sql_template ||
1184 'AND glb.currency_type IN (''T'', ''O'')' || pv_nl ||
1185 'AND EXISTS' || pv_nl ||
1186 ' (SELECT 1' || pv_nl ||
1187 ' FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
1188 ' WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
1189 ' AND ccy.xlated_currency_code = glb.currency_code)' || pv_nl;
1190 ELSE -- All translated balances
1191 v_sql_template := v_sql_template ||
1192 'AND glb.currency_type IN (''T'', ''O'')' || pv_nl;
1193 END IF;
1194 END IF;
1195
1196
1197 -- Print the statement template
1198 FOR iterator IN 1..trunc((length(v_sql_template)+1499)/1500) LOOP
1199 FEM_ENGINES_PKG.Tech_Message
1200 (p_severity => pc_log_level_statement,
1201 p_module => v_module,
1202 p_app_name => 'FEM',
1203 p_msg_name => 'FEM_GL_POST_204',
1204 p_token1 => 'VAR_NAME',
1205 p_value1 => 'v_sql_template: ' || iterator,
1206 p_token2 => 'VAR_VAL',
1207 p_value2 => substr(v_sql_template, iterator*1500-1499, 1500));
1208 END LOOP;
1209
1210 -- Loop through all periods being loaded, and load the information
1211 FOR load_period_info IN load_periods_c LOOP
1212
1213 -- If the as-of-date is in this period, then use it instead of the
1214 -- period end date
1215 IF p_effective_date < load_period_info.period_end_date THEN
1216 v_end_date := p_effective_date;
1217 ELSE
1218 v_end_date := load_period_info.period_end_date;
1219 END IF;
1220
1221 v_sql := v_sql_template;
1222
1223 -- Start replacing the artificial tags we put in the template
1224 v_sql := replace( v_sql,
1225 '<<<period_days>>>',
1226 v_end_date - load_period_info.period_start_date + 1);
1227
1228 v_sql := replace( v_sql,
1229 '<<<quarter_days>>>',
1230 v_end_date - load_period_info.quarter_start_date + 1);
1231
1232 v_sql := replace( v_sql,
1233 '<<<year_days>>>',
1234 v_end_date - load_period_info.year_start_date + 1);
1235
1236 v_sql := replace( v_sql,
1237 '<<<period_name>>>',
1238 load_period_info.period_name);
1239
1240
1241 -- Print the statement
1242 FOR iterator IN 1..trunc((length(v_sql)+1499)/1500) LOOP
1243 FEM_ENGINES_PKG.Tech_Message
1244 (p_severity => pc_log_level_statement,
1245 p_module => v_module,
1246 p_app_name => 'FEM',
1247 p_msg_name => 'FEM_GL_POST_204',
1248 p_token1 => 'VAR_NAME',
1249 p_value1 => 'v_sql: ' || iterator,
1250 p_token2 => 'VAR_VAL',
1251 p_value2 => substr(v_sql, iterator*1500-1499, 1500));
1252 END LOOP;
1253
1254 EXECUTE IMMEDIATE v_sql;
1255
1256 v_intermediate_rows_inserted := SQL%ROWCOUNT;
1257 x_num_rows_inserted := x_num_rows_inserted+v_intermediate_rows_inserted;
1258
1259 -- Print the number of rows inserted
1260 FEM_ENGINES_PKG.Tech_Message(
1261 p_severity => pc_log_level_statement,
1262 p_module => v_module,
1263 p_msg_text => 'Inserted ' || TO_CHAR(v_intermediate_rows_inserted) ||
1264 ' rows into FEM_BAL_POST_INTERIM_GT');
1265
1266 END LOOP;
1267
1268 FEM_ENGINES_PKG.Tech_Message
1269 (p_severity => pc_log_level_procedure,
1270 p_module => v_module,
1271 p_msg_text => 'END');
1272
1273 EXCEPTION
1274 WHEN OTHERS THEN
1275 x_completion_code := 2;
1276
1277 FEM_ENGINES_PKG.User_Message
1278 (p_app_name => 'FEM',
1279 p_msg_name => 'FEM_GL_POST_215',
1280 p_token1 => 'ERR_MSG',
1281 p_value1 => SQLERRM);
1282
1283 FEM_ENGINES_PKG.Tech_Message
1284 (p_severity => pc_log_level_unexpected,
1285 p_module => v_module,
1286 p_app_name => 'FEM',
1287 p_msg_name => 'FEM_GL_POST_215',
1288 p_token1 => 'ERR_MSG',
1289 p_value1 => SQLERRM);
1290
1291 FEM_ENGINES_PKG.Tech_Message
1292 (p_severity => pc_log_level_procedure,
1293 p_module => v_module,
1294 p_msg_text => 'END');
1295
1296 END Load_Avg_Balances;
1297
1298
1299 PROCEDURE Load_Post_Process(x_completion_code OUT NOCOPY NUMBER) IS
1300
1301 v_module VARCHAR2(100);
1302 BEGIN
1303 FEM_ENGINES_PKG.Tech_Message
1304 (p_severity => pc_log_level_procedure,
1305 p_module => v_module,
1306 p_msg_text => 'BEGIN');
1307
1308 pv_nl := '
1309 ';
1310 v_module := 'fem.plsql.fem_intg_bal_eng_load.load_post_process';
1311
1312 x_completion_code := 0;
1313
1314 -- Print the As-of-date
1315 FEM_ENGINES_PKG.Tech_Message(
1316 p_severity => pc_log_level_statement,
1317 p_module => v_module,
1318 p_app_name => 'FEM',
1319 p_msg_name => 'FEM_GL_POST_204',
1320 p_token1 => 'VAR_NAME',
1321 p_value1 => 'FEM_GL_POST_PROCESS_PKG.pv_func_ccy_code',
1322 p_token2 => 'VAR_VAL',
1323 p_value2 => FEM_GL_POST_PROCESS_PKG.pv_func_ccy_code);
1324
1325 INSERT INTO fem_intg_bpi_curr_gt(
1326 dataset_code, cal_period_id, code_combination_id, financial_elem_id,
1327 delta_run_id, xtd_balance_f_sum, qtd_balance_f_sum, ytd_balance_f_sum)
1328 SELECT /*+ full(fem_bal_post_interim_gt) */
1329 dataset_code, cal_period_id, code_combination_id,
1330 financial_elem_id, delta_run_id,
1331 SUM(nvl(xtd_balance_f,0)),
1332 SUM(nvl(qtd_balance_f,0)),
1333 SUM(nvl(ytd_balance_f,0))
1334 FROM fem_bal_post_interim_gt
1335 WHERE currency_code <> FEM_GL_POST_PROCESS_PKG.pv_func_ccy_code
1336 AND currency_code <> 'STAT'
1337 AND currency_type_code = 'ENTERED'
1338 AND posting_error_flag = 'N'
1339 GROUP BY dataset_code, cal_period_id, code_combination_id,
1340 financial_elem_id, delta_run_id;
1341
1342 UPDATE /*+ FULL(bpi) */ FEM_BAL_POST_INTERIM_GT bpi
1343 SET (xtd_balance_e,
1344 qtd_balance_e,
1345 ytd_balance_e,
1346 xtd_balance_f,
1347 qtd_balance_f,
1348 ytd_balance_f) =
1349 (SELECT
1350 bpi.xtd_balance_e - nvl(bpi_beq.xtd_balance_f_sum,0),
1351 bpi.qtd_balance_e - nvl(bpi_beq.qtd_balance_f_sum,0),
1352 bpi.ytd_balance_e - nvl(bpi_beq.ytd_balance_f_sum,0),
1353 bpi.xtd_balance_e - nvl(bpi_beq.xtd_balance_f_sum,0),
1354 bpi.qtd_balance_e - nvl(bpi_beq.qtd_balance_f_sum,0),
1355 bpi.ytd_balance_e - nvl(bpi_beq.ytd_balance_f_sum,0)
1356 FROM FEM_INTG_BPI_CURR_GT bpi_beq
1357 WHERE bpi_beq.dataset_code = bpi.dataset_code
1358 AND bpi_beq.cal_period_id = bpi.cal_period_id
1359 AND bpi_beq.code_combination_id = bpi.code_combination_id
1360 AND bpi_beq.financial_elem_id = bpi.financial_elem_id
1361 AND ((bpi_beq.delta_run_id IS NULL AND bpi.delta_run_id IS NULL) OR
1362 bpi_beq.delta_run_id = bpi.delta_run_id)
1363 )
1364 WHERE bpi.currency_code = FEM_GL_POST_PROCESS_PKG.pv_func_ccy_code
1365 AND bpi.posting_error_flag = 'N'
1366 AND EXISTS
1367 (SELECT 1
1368 FROM fem_intg_bpi_curr_gt bpi_beq
1369 WHERE bpi_beq.dataset_code = bpi.dataset_code
1370 AND bpi_beq.cal_period_id = bpi.cal_period_id
1371 AND bpi_beq.code_combination_id = bpi.code_combination_id
1372 AND bpi_beq.financial_elem_id = bpi.financial_elem_id
1373 AND ((bpi_beq.delta_run_id IS NULL AND bpi.delta_run_id IS NULL) OR
1374 bpi_beq.delta_run_id = bpi.delta_run_id));
1375
1376 -- Print the number of rows inserted
1377 FEM_ENGINES_PKG.Tech_Message(
1378 p_severity => pc_log_level_statement,
1379 p_module => v_module,
1380 p_msg_text => 'Updated ' || TO_CHAR(SQL%ROWCOUNT) ||
1381 ' rows in FEM_BAL_POST_INTERIM_GT');
1382
1383 FEM_ENGINES_PKG.Tech_Message
1384 (p_severity => pc_log_level_procedure,
1385 p_module => v_module,
1386 p_msg_text => 'END');
1387
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 x_completion_code := 2;
1391
1392 FEM_ENGINES_PKG.User_Message
1393 (p_app_name => 'FEM',
1394 p_msg_name => 'FEM_GL_POST_215',
1395 p_token1 => 'ERR_MSG',
1396 p_value1 => SQLERRM);
1397
1398 FEM_ENGINES_PKG.Tech_Message
1399 (p_severity => pc_log_level_unexpected,
1400 p_module => v_module,
1401 p_app_name => 'FEM',
1402 p_msg_name => 'FEM_GL_POST_215',
1403 p_token1 => 'ERR_MSG',
1404 p_value1 => SQLERRM);
1405
1406 FEM_ENGINES_PKG.Tech_Message
1407 (p_severity => pc_log_level_procedure,
1408 p_module => v_module,
1409 p_msg_text => 'END');
1410
1411 END Load_Post_Process;
1412
1413
1414 PROCEDURE Map_Adv_LI_FE(x_completion_code OUT NOCOPY NUMBER) IS
1415 v_na_fe_attr_id NUMBER;
1416 v_na_fe_v_id NUMBER;
1417 v_na_li_attr_id NUMBER;
1418 v_na_li_v_id NUMBER;
1419
1420 v_error_code NUMBER;
1421
1422 v_module VARCHAR2(100);
1423 BEGIN
1424 FEM_ENGINES_PKG.Tech_Message
1425 (p_severity => pc_log_level_procedure,
1426 p_module => v_module,
1427 p_msg_text => 'BEGIN');
1428
1429 pv_nl := '
1430 ';
1431 v_module := 'fem.plsql.fem_intg_bal_eng_load.map_adv_li_fe';
1432
1433 x_completion_code := 0;
1434
1435 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
1436 p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_nat_acct_dim_id,
1437 p_attr_label => 'FINANCIAL_ELEMENT',
1438 x_attr_id => v_na_fe_attr_id,
1439 x_ver_id => v_na_fe_v_id,
1440 x_err_code => v_error_code);
1441
1442 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id(
1443 p_dim_id => FEM_GL_POST_PROCESS_PKG.pv_nat_acct_dim_id,
1444 p_attr_label => 'LINE_ITEM',
1445 x_attr_id => v_na_li_attr_id,
1446 x_ver_id => v_na_li_v_id,
1447 x_err_code => v_error_code);
1448
1449
1450 UPDATE FEM_BAL_POST_INTERIM_GT bpi
1451 SET (FINANCIAL_ELEM_ID, LINE_ITEM_ID) =
1452 (SELECT nvl(naa_fe.dim_attribute_numeric_member, bpi.financial_elem_id),
1453 nvl(naa_li.dim_attribute_numeric_member, bpi.line_item_id)
1454 FROM FEM_NAT_ACCTS_ATTR naa_fe,
1455 FEM_NAT_ACCTS_ATTR naa_li
1456 WHERE naa_fe.attribute_id (+)= v_na_fe_attr_id
1457 AND naa_fe.version_id (+)= v_na_fe_v_id
1458 AND naa_fe.natural_account_id (+)= bpi.natural_account_id
1459 AND naa_li.attribute_id (+)= v_na_li_attr_id
1460 AND naa_li.version_id (+)= v_na_li_v_id
1461 AND naa_li.natural_account_id (+)= bpi.natural_account_id)
1462 WHERE bpi.posting_error_flag = 'N';
1463
1464 -- Print the number of rows updated. This may not be the number of
1465 -- overrides performed, since the outer-joins will also give you rows
1466 -- without overrides.
1467 FEM_ENGINES_PKG.Tech_Message(
1468 p_severity => pc_log_level_statement,
1469 p_module => v_module,
1470 p_msg_text => 'Updated ' || TO_CHAR(SQL%ROWCOUNT) ||
1471 ' rows in FEM_BAL_POST_INTERIM_GT');
1472
1473 FEM_ENGINES_PKG.Tech_Message
1474 (p_severity => pc_log_level_procedure,
1475 p_module => v_module,
1476 p_msg_text => 'END');
1477
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 x_completion_code := 2;
1481
1482 FEM_ENGINES_PKG.User_Message
1483 (p_app_name => 'FEM',
1484 p_msg_name => 'FEM_GL_POST_215',
1485 p_token1 => 'ERR_MSG',
1486 p_value1 => SQLERRM);
1487
1488 FEM_ENGINES_PKG.Tech_Message
1489 (p_severity => pc_log_level_unexpected,
1490 p_module => v_module,
1491 p_app_name => 'FEM',
1492 p_msg_name => 'FEM_GL_POST_215',
1493 p_token1 => 'ERR_MSG',
1494 p_value1 => SQLERRM);
1495
1496 FEM_ENGINES_PKG.Tech_Message
1497 (p_severity => pc_log_level_procedure,
1498 p_module => v_module,
1499 p_msg_text => 'END');
1500
1501 END Map_Adv_LI_FE;
1502
1503 PROCEDURE Mark_Posted_Incr_Bal(x_completion_code OUT NOCOPY NUMBER,
1504 p_bsv_range_low VARCHAR2,
1505 p_bsv_range_high VARCHAR2) IS
1506 v_actual_flag VARCHAR2(1);
1507
1508 v_delete_stmt VARCHAR2(4000);
1509 v_insert_stmt VARCHAR2(4000);
1510 v_merge_stmt VARCHAR2(8000);
1511 v_update_stmt VARCHAR2(4000);
1512 v_flex_query_stmt VARCHAR2(2000);
1513
1514 v_module VARCHAR2(100);
1515 BEGIN
1516 FEM_ENGINES_PKG.Tech_Message
1517 (p_severity => pc_log_level_procedure,
1518 p_module => v_module,
1519 p_msg_text => 'BEGIN');
1520
1521 pv_nl := '
1522 ';
1523 v_module := 'fem.plsql.fem_intg_bal_eng_load.mark_posted_incr_bal';
1524
1525 x_completion_code := 0;
1526
1527 IF FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd = 'ACTUAL' THEN
1528 v_actual_flag := 'A';
1529 ELSIF FEM_GL_POST_PROCESS_PKG.pv_ds_balance_type_cd = 'BUDGET' THEN
1530 v_actual_flag := 'B';
1531 ELSE -- Encumbrance
1532 v_actual_flag := 'E';
1533 END IF;
1534
1535
1536 -- First, insert rows into GL_TRACK_DELTA_BALANCES to flag that delta
1537 -- balances should be tracked from now in GL
1538 INSERT INTO GL_TRACK_DELTA_BALANCES(
1539 ledger_id, program_code, period_name, actual_flag,
1540 extract_level_code, currency_type_code, enabled_flag, last_update_date,
1541 last_updated_by, creation_date, created_by, last_update_login)
1542 SELECT DISTINCT FEM_GL_POST_PROCESS_PKG.pv_ledger_id,
1543 'FEM',
1544 param.period_name,
1545 v_actual_flag,
1546 'DTL',
1547 'B',
1548 'Y',
1549 sysdate,
1550 FEM_GL_POST_PROCESS_PKG.pv_user_id,
1551 sysdate,
1552 FEM_GL_POST_PROCESS_PKG.pv_user_id,
1553 FEM_GL_POST_PROCESS_PKG.pv_login_id
1554 FROM FEM_INTG_EXEC_PARAMS_GT param
1555 WHERE param.error_code IS NULL
1556 AND param.request_id IS NOT NULL
1557 AND NOT EXISTS
1558 (SELECT 1
1559 FROM GL_TRACK_DELTA_BALANCES tdb
1560 WHERE tdb.ledger_id = FEM_GL_POST_PROCESS_PKG.pv_ledger_id
1561 AND tdb.program_code = 'FEM'
1562 AND tdb.period_name = param.period_name
1563 AND tdb.actual_flag = v_actual_flag
1564 AND tdb.extract_level_code = 'DTL'
1565 AND tdb.currency_type_code = 'B'
1566 AND tdb.enabled_flag = 'Y');
1567
1568 FEM_ENGINES_PKG.Tech_Message(
1569 p_severity => pc_log_level_statement,
1570 p_module => v_module,
1571 p_msg_text => 'Inserted ' || TO_CHAR(SQL%ROWCOUNT) ||
1572 ' rows into GL_TRACK_DELTA_BALANCES');
1573
1574
1575 -- Now, remove rows from FEM_INTG_DELTA_LOADS if previously errored
1576 -- delta runs were successfully loaded
1577 v_delete_stmt :=
1578 'DELETE FROM FEM_INTG_DELTA_LOADS dl' || pv_nl ||
1579 'WHERE dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
1580 'AND dl.loaded_flag = ''N''' || pv_nl;
1581
1582 -- Now, remove rows from FEM_INTG_DELTA_LOADS if previously errored
1583 -- delta runs were successfully loaded
1584 IF p_bsv_range_low IS NOT NULL AND p_bsv_range_high IS NOT NULL THEN
1585 v_delete_stmt := v_delete_stmt ||
1586 'AND dl.balance_seg_value BETWEEN ' || p_bsv_range_low || ' AND ' ||
1587 p_bsv_range_high || pv_nl;
1588 END IF;
1589
1590 v_delete_stmt := v_delete_stmt ||
1591 'AND EXISTS' || pv_nl ||
1592 ' (SELECT 1' || pv_nl ||
1593 ' FROM FEM_INTG_EXEC_PARAMS_GT param' || pv_nl ||
1594 ' WHERE param.output_dataset_code = dl.dataset_code' || pv_nl ||
1595 ' AND param.cal_period_id = dl.cal_period_id' || pv_nl ||
1596 ' AND param.error_code IS NULL' || pv_nl ||
1597 ' AND param.request_id IS NOT NULL)' || pv_nl ||
1598 'AND NOT EXISTS' || pv_nl ||
1599 ' (SELECT 1' || pv_nl ||
1600 ' FROM FEM_BAL_POST_INTERIM_GT bpi,' || pv_nl ||
1601 ' GL_CODE_COMBINATIONS from_cc' || pv_nl ||
1602 ' WHERE bpi.delta_run_id = dl.delta_run_id' || pv_nl ||
1603 ' AND bpi.posting_error_flag = ''Y''' || pv_nl ||
1604 ' AND from_cc.code_combination_id = bpi.code_combination_id' || pv_nl ||
1605 ' AND from_cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name ||
1606 ' = dl.balance_seg_value)';
1607
1608 -- Print the statement
1609 FOR iterator IN 1..trunc((length(v_delete_stmt)+1499)/1500) LOOP
1610 FEM_ENGINES_PKG.Tech_Message
1611 (p_severity => pc_log_level_statement,
1612 p_module => v_module,
1613 p_app_name => 'FEM',
1614 p_msg_name => 'FEM_GL_POST_204',
1615 p_token1 => 'VAR_NAME',
1616 p_value1 => 'v_delete_stmt: ' || iterator,
1617 p_token2 => 'VAR_VAL',
1618 p_value2 => substr(v_delete_stmt, iterator*1500-1499, 1500));
1619 END LOOP;
1620
1621 EXECUTE IMMEDIATE v_delete_stmt;
1622
1623 FEM_ENGINES_PKG.Tech_Message(
1624 p_severity => pc_log_level_statement,
1625 p_module => v_module,
1626 p_msg_text => 'Deleted ' || TO_CHAR(SQL%ROWCOUNT) ||
1627 ' rows from FEM_INTG_DELTA_LOADS');
1628
1629
1630 -- Now, insert errored delta runs that were attempted for the first
1631 -- time in this load
1632 v_insert_stmt :=
1633 'INSERT INTO FEM_INTG_DELTA_LOADS(LEDGER_ID, DATASET_CODE, CAL_PERIOD_ID, ' ||
1634 'DELTA_RUN_ID, BALANCE_SEG_VALUE, LOADED_FLAG, CREATION_DATE, CREATED_BY, ' ||
1635 'LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)' || pv_nl ||
1636 'SELECT DISTINCT ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
1637 ' param.output_dataset_code,' || pv_nl ||
1638 ' param.cal_period_id,' || pv_nl ||
1639 ' bpi.delta_run_id,' || pv_nl ||
1640 ' dl.balance_seg_value,' || pv_nl ||
1641 ' ''N'',' || pv_nl ||
1642 ' sysdate,' || pv_nl ||
1643 ' ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
1644 ' sysdate,' || pv_nl ||
1645 ' ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
1646 ' ' || FEM_GL_POST_PROCESS_PKG.pv_login_id || pv_nl ||
1647 'FROM FEM_INTG_EXEC_PARAMS_GT param,' || pv_nl ||
1648 ' FEM_BAL_POST_INTERIM_GT bpi,' || pv_nl ||
1649 ' GL_CODE_COMBINATIONS from_cc,' || pv_nl ||
1650 ' FEM_INTG_DELTA_LOADS dl' || pv_nl ||
1651 'WHERE param.load_method_code = ''I''' || pv_nl ||
1652 'AND param.error_code IS NULL' || pv_nl ||
1653 'AND param.request_id IS NOT NULL' || pv_nl ||
1654 'AND dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
1655 'AND dl.dataset_code = param.output_dataset_code' || pv_nl ||
1656 'AND dl.cal_period_id = param.cal_period_id' || pv_nl ||
1657 'AND dl.loaded_flag = ''Y''' || pv_nl;
1658
1659 IF p_bsv_range_low IS NOT NULL AND p_bsv_range_high IS NOT NULL THEN
1660 v_insert_stmt := v_insert_stmt ||
1661 'AND dl.balance_seg_value BETWEEN ' || p_bsv_range_low || ' AND ' ||
1662 p_bsv_range_high || pv_nl;
1663 END IF;
1664
1665 v_insert_stmt := v_insert_stmt ||
1666 'AND from_cc.code_combination_id = bpi.code_combination_id' || pv_nl ||
1667 'AND from_cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name ||
1668 ' = dl.balance_seg_value' || pv_nl ||
1669 'AND bpi.dataset_code = param.output_dataset_code' || pv_nl ||
1670 'AND bpi.cal_period_id = param.cal_period_id' || pv_nl ||
1671 'AND bpi.bal_post_type_code = ''A''' || pv_nl ||
1672 'AND bpi.posting_error_flag = ''Y''' || pv_nl ||
1673 'AND bpi.delta_run_id BETWEEN dl.delta_run_id + 1 AND ' ||
1674 FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id;
1675
1676
1677 -- Print the statement
1678 FOR iterator IN 1..trunc((length(v_insert_stmt)+1499)/1500) LOOP
1679 FEM_ENGINES_PKG.Tech_Message
1680 (p_severity => pc_log_level_statement,
1681 p_module => v_module,
1682 p_app_name => 'FEM',
1683 p_msg_name => 'FEM_GL_POST_204',
1684 p_token1 => 'VAR_NAME',
1685 p_value1 => 'v_insert_stmt: ' || iterator,
1686 p_token2 => 'VAR_VAL',
1687 p_value2 => substr(v_insert_stmt, iterator*1500-1499, 1500));
1688 END LOOP;
1689
1690 EXECUTE IMMEDIATE v_insert_stmt;
1691
1692 FEM_ENGINES_PKG.Tech_Message(
1693 p_severity => pc_log_level_statement,
1694 p_module => v_module,
1695 p_msg_text => 'Inserted ' || TO_CHAR(SQL%ROWCOUNT) ||
1696 ' rows into FEM_INTG_DELTA_LOADS');
1697
1698 v_flex_query_stmt := get_flex_values_query;
1699
1700 v_merge_stmt :=
1701 'MERGE INTO FEM_INTG_DELTA_LOADS dl' || pv_nl ||
1702 'USING (SELECT param_in.output_dataset_code,' || pv_nl ||
1703 ' param_in.cal_period_id,' || pv_nl ||
1704 ' flex.flex_value' || pv_nl ||
1705 ' FROM FEM_INTG_EXEC_PARAMS_GT param_in,' || pv_nl ||
1706 ' (' || v_flex_query_stmt || ') flex' || pv_nl ||
1707 ' WHERE param_in.error_code IS NULL' || pv_nl ||
1708 ' AND param_in.request_id IS NOT NULL' || pv_nl ||
1709 ' AND param_in.load_method_code = ''S'') param' || pv_nl ||
1710 'ON (dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ' AND' || pv_nl ||
1711 ' dl.dataset_code = param.output_dataset_code AND' || pv_nl ||
1712 ' dl.cal_period_id = param.cal_period_id AND' || pv_nl ||
1713 ' dl.balance_seg_value = param.flex_value AND' || pv_nl ||
1714 ' dl.loaded_flag = ''Y'')' || pv_nl ||
1715 'WHEN MATCHED THEN' || pv_nl ||
1716 ' UPDATE SET dl.delta_run_id = ' || FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id || ',' || pv_nl ||
1717 ' dl.last_update_date = sysdate,' || pv_nl ||
1718 ' dl.last_updated_by = ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
1719 ' dl.last_update_login = ' || FEM_GL_POST_PROCESS_PKG.pv_login_id || pv_nl ||
1720 'WHEN NOT MATCHED THEN' || pv_nl ||
1721 ' INSERT(LEDGER_ID, DATASET_CODE, CAL_PERIOD_ID, DELTA_RUN_ID, ' ||
1722 'BALANCE_SEG_VALUE, LOADED_FLAG, CREATION_DATE, CREATED_BY, ' ||
1723 'LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)' || pv_nl ||
1724 ' VALUES' || pv_nl ||
1725 ' (' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
1726 ' param.output_dataset_code,' || pv_nl ||
1727 ' param.cal_period_id,' || pv_nl ||
1728 ' ' || FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id || ',' || pv_nl ||
1729 ' param.flex_value,' || pv_nl ||
1730 ' ''Y'',' || pv_nl ||
1731 ' sysdate,' || pv_nl ||
1732 ' ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
1733 ' sysdate,' || pv_nl ||
1734 ' ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
1735 ' ' || FEM_GL_POST_PROCESS_PKG.pv_login_id || ')';
1736
1737
1738 FOR iterator IN 1..trunc((length(v_merge_stmt)+1499)/1500) LOOP
1739 FEM_ENGINES_PKG.Tech_Message
1740 (p_severity => pc_log_level_statement,
1741 p_module => v_module,
1742 p_app_name => 'FEM',
1743 p_msg_name => 'FEM_GL_POST_204',
1744 p_token1 => 'VAR_NAME',
1745 p_value1 => 'v_merge_stmt: ' || iterator,
1746 p_token2 => 'VAR_VAL',
1747 p_value2 => substr(v_merge_stmt, iterator*1500-1499, 1500));
1748 END LOOP;
1749
1750 EXECUTE IMMEDIATE v_merge_stmt;
1751
1752 FEM_ENGINES_PKG.Tech_Message(
1753 p_severity => pc_log_level_statement,
1754 p_module => v_module,
1755 p_msg_text => 'Merged ' || TO_CHAR(SQL%ROWCOUNT) ||
1756 ' rows into FEM_INTG_DELTA_LOADS');
1757
1758 v_update_stmt :=
1759 'UPDATE fem_intg_delta_loads dl' || pv_nl ||
1760 'SET delta_run_id = ' || FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id || ',' || pv_nl ||
1761 ' last_update_date = sysdate,' || pv_nl ||
1762 ' last_updated_by = ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
1763 ' last_update_login = ' || FEM_GL_POST_PROCESS_PKG.pv_login_id || pv_nl ||
1764 'WHERE dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
1765 'AND dl.loaded_flag = ''Y''' || pv_nl;
1766
1767 IF p_bsv_range_low IS NOT NULL AND p_bsv_range_high IS NOT NULL THEN
1768 v_update_stmt := v_update_stmt ||
1769 'AND dl.balance_seg_value BETWEEN ' || p_bsv_range_low || ' AND ' ||
1770 p_bsv_range_high || pv_nl;
1771 END IF;
1772
1773 v_update_stmt := v_update_stmt ||
1774 'AND (dataset_code, cal_period_id) IN' || pv_nl ||
1775 ' (SELECT param.output_dataset_code,' || pv_nl ||
1776 ' param.cal_period_id' || pv_nl ||
1777 ' FROM fem_intg_exec_params_gt param' || pv_nl ||
1778 ' WHERE param.error_code IS NULL' || pv_nl ||
1779 ' AND param.request_id IS NOT NULL' || pv_nl ||
1780 ' AND param.load_method_code = ''I'')';
1781
1782
1783 FOR iterator IN 1..trunc((length(v_update_stmt)+1499)/1500) LOOP
1784 FEM_ENGINES_PKG.Tech_Message
1785 (p_severity => pc_log_level_statement,
1786 p_module => v_module,
1787 p_app_name => 'FEM',
1788 p_msg_name => 'FEM_GL_POST_204',
1789 p_token1 => 'VAR_NAME',
1790 p_value1 => 'v_update_stmt: ' || iterator,
1791 p_token2 => 'VAR_VAL',
1792 p_value2 => substr(v_update_stmt, iterator*1500-1499, 1500));
1793 END LOOP;
1794
1795 EXECUTE IMMEDIATE v_update_stmt;
1796
1797 FEM_ENGINES_PKG.Tech_Message(
1798 p_severity => pc_log_level_statement,
1799 p_module => v_module,
1800 p_msg_text => 'Updated ' || TO_CHAR(SQL%ROWCOUNT) ||
1801 ' rows into FEM_INTG_DELTA_LOADS');
1802
1803 FEM_ENGINES_PKG.Tech_Message
1804 (p_severity => pc_log_level_procedure,
1805 p_module => v_module,
1806 p_msg_text => 'END');
1807
1808 EXCEPTION
1809 WHEN OTHERS THEN
1810 x_completion_code := 2;
1811
1812 FEM_ENGINES_PKG.User_Message
1813 (p_app_name => 'FEM',
1814 p_msg_name => 'FEM_GL_POST_215',
1815 p_token1 => 'ERR_MSG',
1816 p_value1 => SQLERRM);
1817
1818 FEM_ENGINES_PKG.Tech_Message
1819 (p_severity => pc_log_level_unexpected,
1820 p_module => v_module,
1821 p_app_name => 'FEM',
1822 p_msg_name => 'FEM_GL_POST_215',
1823 p_token1 => 'ERR_MSG',
1824 p_value1 => SQLERRM);
1825
1826 FEM_ENGINES_PKG.Tech_Message
1827 (p_severity => pc_log_level_procedure,
1828 p_module => v_module,
1829 p_msg_text => 'END');
1830
1831 END Mark_Posted_Incr_Bal;
1832
1833 END FEM_INTG_BAL_ENG_LOAD_PKG;