[Home] [Help]
PACKAGE BODY: APPS.GL_ENT_FUNC_BAL_UPGRADE_PKG
Source
1 PACKAGE BODY GL_ENT_FUNC_BAL_UPGRADE_PKG AS
2 /* $Header: gluefbub.pls 120.4 2006/11/19 10:17:23 ticheng noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 g_api CONSTANT VARCHAR2(40) := 'gl.plsql.GL_ENT_FUNC_BAL_UPGRADE_PKG';
8
9 g_std_bal_table CONSTANT VARCHAR2(30) := 'GL_BALANCES';
10 g_adb_bal_table CONSTANT VARCHAR2(30) := 'GL_DAILY_BALANCES';
11 g_mm_std_table CONSTANT VARCHAR2(30) := 'GL_MOVEMERGE_BAL_';
12 g_mm_adb_table CONSTANT VARCHAR2(30) := 'GL_MOVEMERGE_DAILY_BAL_';
13
14 g_table_name CONSTANT VARCHAR2(30) := 'GL_CODE_COMBINATIONS';
15 g_id_column CONSTANT VARCHAR2(30) := 'CODE_COMBINATION_ID';
16 g_script_name CONSTANT VARCHAR2(30) := 'gluefbub.pls';
17
18 --
19 -- PRIVATE FUNCTIONS
20 --
21
22 --
23 -- Procedure
24 -- prepare_std_bal_gt
25 -- Purpose
26 -- Insert data into the standard balances GT, gl_efb_upgrade_std.
27 -- History
28 -- 03/10/2005 T Cheng Created
29 -- Arguments
30 -- x_src_table Source table of the balances, either GL_BALANCES
31 -- or GL_MOVEMERGE_BAL_<req_id>
32 -- x_start_id Start id for AD parallel range, gl_balances upgrade only
33 -- x_end_id End id for AD parallel range, gl_balances upgrade only
34 --
35 PROCEDURE prepare_std_bal_gt(x_src_table VARCHAR2,
36 x_start_id NUMBER DEFAULT NULL,
37 x_end_id NUMBER DEFAULT NULL) IS
38 fn_name CONSTANT VARCHAR2(30) := 'PREPARE_STD_BAL_GT';
39 StdInterimInsertStr VARCHAR2(2200);
40 ccid_range VARCHAR2(100);
41 hint_txt VARCHAR2(500);
42 BEGIN
43 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
44 g_api || '.' || fn_name);
45 -- parameters
46 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
47 g_api || '.' || fn_name,
48 'x_src_table = ' || x_src_table);
49 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
50 g_api || '.' || fn_name,
51 'x_start_id = ' || x_start_id);
52 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
53 g_api || '.' || fn_name,
54 'x_end_id = ' || x_end_id);
55
56 IF (x_src_table = g_std_bal_table) THEN
57 ccid_range :=
58 'AND b1.code_combination_id between :start_id and :end_id ';
59 hint_txt := '/*+ ORDERED INDEX(b1 gl_balances_n1) ' ||
60 'INDEX(p1 gl_period_statuses_u3) ' ||
61 'INDEX(ps gl_period_statuses_u4) */ ';
62 ELSE
63 ccid_range := '';
64 hint_txt := '';
65 END IF;
66
67 StdInterimInsertStr :=
68 'INSERT INTO gl_efb_upgrade_std ' ||
69 '(ledger_id, code_combination_id, currency_code,' ||
70 ' period_name, actual_flag, translated_flag,' ||
71 ' period_net_dr_beq, period_net_cr_beq,' ||
72 ' quarter_to_date_dr_beq, quarter_to_date_cr_beq,' ||
73 ' begin_balance_dr_beq, begin_balance_cr_beq,' ||
74 ' project_to_date_dr_beq, project_to_date_cr_beq,' ||
75 ' template_id) '||
76 'SELECT ' || hint_txt ||
77 'b1.ledger_id, b1.code_combination_id, b1.currency_code, ' ||
78 'ps.period_name, b1.actual_flag, b1.translated_flag, ' ||
79 'sum(decode(b1.period_name, ps.period_name, b1.period_net_dr_beq,0)), ' ||
80 'sum(decode(b1.period_name, ps.period_name, b1.period_net_cr_beq,0)), ' ||
81 'sum(decode(p1.period_year, ps.period_year, ' ||
82 ' decode(p1.quarter_num, ps.quarter_num, ' ||
83 ' decode(p1.period_num, ps.period_num, 0, ' ||
84 ' b1.period_net_dr_beq),0),0)), ' ||
85 'sum(decode(p1.period_year, ps.period_year, ' ||
86 ' decode(p1.quarter_num, ps.quarter_num, ' ||
87 ' decode(p1.period_num, ps.period_num, 0, ' ||
88 ' b1.period_net_cr_beq),0),0)), ' ||
89 'sum(decode(b1.period_name, ps.period_name, b1.begin_balance_dr_beq,0)),'||
90 'sum(decode(b1.period_name, ps.period_name, b1.begin_balance_cr_beq,0)),'||
91 'sum(decode(p1.period_year, ps.period_year, ' ||
92 ' decode(p1.period_num, ps.period_num, 0, ' ||
93 ' b1.period_net_dr_beq), b1.period_net_dr_beq)), ' ||
94 'sum(decode(p1.period_year, ps.period_year, ' ||
95 ' decode(p1.period_num, ps.period_num, 0, ' ||
96 ' b1.period_net_cr_beq), b1.period_net_cr_beq)), ' ||
97 'b1.template_id ' ||
98 'FROM ' || x_src_table ||
99 ' b1, gl_period_statuses p1, gl_period_statuses ps ' ||
100 'WHERE b1.actual_flag = ''A'' ' ||
101 'AND b1.currency_code <> ''STAT'' ' ||
102 'AND b1.translated_flag = ''R'' ' ||
103 ccid_range ||
104 'AND ps.ledger_id = b1.ledger_id ' ||
105 'AND ps.application_id = 101 ' ||
106 'AND ps.closing_status not in (''N'', ''F'') ' ||
107 'AND p1.effective_period_num <= ps.effective_period_num ' ||
108 'AND p1.ledger_id = b1.ledger_id ' ||
109 'AND p1.application_id = 101 ' ||
110 'AND p1.period_name = b1.period_name ' ||
111 'GROUP BY b1.ledger_id, b1.code_combination_id,b1.currency_code, ' ||
112 ' b1.actual_flag, b1.translated_flag, b1.template_id, ps.period_name';
113
114 IF (x_src_table = g_std_bal_table) THEN
115 EXECUTE IMMEDIATE StdInterimInsertStr USING x_start_id, x_end_id;
116 ELSE
117 EXECUTE IMMEDIATE StdInterimInsertStr;
118 END IF;
119
120 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
121 g_api || '.' || fn_name);
122 EXCEPTION
123 WHEN OTHERS THEN
124 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
125 g_api || '.' || fn_name,
126 SUBSTR(SQLERRM, 1, 4000));
127 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
128 g_api || '.' || fn_name);
129 RAISE;
130 END prepare_std_bal_gt;
131
132
133 --
134 -- Procedure
135 -- update_std_foreign_ent_bal
136 -- Purpose
137 -- Update the QTD and PJTD BEQ columns for foreign entered balances.
138 -- History
139 -- 03/10/2005 T Cheng Created
140 -- Arguments
141 -- x_src_table Source table of the balances, either GL_BALANCES
142 -- or GL_MOVEMERGE_BAL_<req_id>
143 -- x_start_id Start id for AD parallel range, gl_balances upgrade only
144 -- x_end_id End id for AD parallel range, gl_balances upgrade only
145 --
146 PROCEDURE update_std_foreign_ent_bal(x_src_table VARCHAR2,
147 x_start_id NUMBER DEFAULT NULL,
148 x_end_id NUMBER DEFAULT NULL) IS
149 fn_name CONSTANT VARCHAR2(30) := 'UPDATE_STD_FOREIGN_ENT_BAL';
150 StdUpdateFrgnEntBalStr VARCHAR2(1000);
151 bal_where_clause VARCHAR2(200);
152 hint_txt VARCHAR2(500);
153 BEGIN
154 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
155 g_api || '.' || fn_name);
156 -- parameters
157 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
158 g_api || '.' || fn_name,
159 'x_src_table = ' || x_src_table);
160 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
161 g_api || '.' || fn_name,
162 'x_start_id = ' || x_start_id);
163 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
164 g_api || '.' || fn_name,
165 'x_end_id = ' || x_end_id);
166
167 IF (x_src_table = g_std_bal_table) THEN
168 bal_where_clause :=
169 'AND b1.code_combination_id between :start_id and :end_id ' ||
170 'AND (b1.code_combination_id, b1.period_name) in ' ||
171 ' (select code_combination_id, period_name ' ||
172 ' from gl_efb_upgrade_std) ';
173 hint_txt := '/*+ INDEX(b1 gl_balances_n1) */';
174 ELSE
175 bal_where_clause := '';
176 hint_txt := '';
177 END IF;
178
179 StdUpdateFrgnEntBalStr :=
180 'UPDATE ' || x_src_table || ' b1 ' ||
181 'SET (b1.quarter_to_date_dr_beq, b1.quarter_to_date_cr_beq, ' ||
182 ' b1.project_to_date_dr_beq, b1.project_to_date_cr_beq) ' ||
183 ' = (select /*+ INDEX (b2 gl_efb_upgrade_std_n1 ) */ b2.quarter_to_date_dr_beq, b2.quarter_to_date_cr_beq, ' ||
184 ' b2.project_to_date_dr_beq, b2.project_to_date_cr_beq ' ||
185 ' from gl_efb_upgrade_std b2 ' ||
186 ' where b2.ledger_id = b1.ledger_id ' ||
187 ' and b2.code_combination_id = b1.code_combination_id ' ||
188 ' and b2.currency_code = b1.currency_code ' ||
189 ' and b2.period_name = b1.period_name ' ||
190 ' and b2.actual_flag = ''A'' ' ||
191 ' and b2.translated_flag = ''R'') ' ||
192 'WHERE b1.translated_flag = ''R'' ' ||
193 'AND b1.actual_flag = ''A'' ' ||
194 bal_where_clause;
195
196 IF (x_src_table = g_std_bal_table) THEN
197 EXECUTE IMMEDIATE StdUpdateFrgnEntBalStr USING x_start_id, x_end_id;
198 ELSE
199 EXECUTE IMMEDIATE StdUpdateFrgnEntBalStr;
200 END IF;
201
202 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
203 g_api || '.' || fn_name);
204 EXCEPTION
205 WHEN OTHERS THEN
206 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
207 g_api || '.' || fn_name,
208 SUBSTR(SQLERRM, 1, 4000));
209 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
210 g_api || '.' || fn_name);
211 RAISE;
212 END update_std_foreign_ent_bal;
213
214
215 --
216 -- Procedure
217 -- update_std_func_ent_bal
218 -- Purpose
219 -- Update all 8 BEQ columns for functional entered balances.
220 -- History
221 -- 03/10/2005 T Cheng Created
222 -- Arguments
223 -- x_src_table Source table of the balances, either GL_BALANCES
224 -- or GL_MOVEMERGE_BAL_<req_id>
225 -- x_start_id Start id for AD parallel range, gl_balances upgrade only
226 -- x_end_id End id for AD parallel range, gl_balances upgrade only
227 --
228 PROCEDURE update_std_func_ent_bal(x_src_table VARCHAR2,
229 x_start_id NUMBER DEFAULT NULL,
230 x_end_id NUMBER DEFAULT NULL) IS
231 fn_name CONSTANT VARCHAR2(30) := 'UPDATE_STD_FUNC_ENT_BAL';
232 StdUpdateFuncEntBalStr VARCHAR2(1400);
233 ccid_range VARCHAR2(100);
234 hint_txt VARCHAR2(500);
235 BEGIN
236 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
237 g_api || '.' || fn_name);
238 -- parameters
239 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
240 g_api || '.' || fn_name,
241 'x_src_table = ' || x_src_table);
242 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
243 g_api || '.' || fn_name,
244 'x_start_id = ' || x_start_id);
245 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
246 g_api || '.' || fn_name,
247 'x_end_id = ' || x_end_id);
248
249 IF (x_src_table = g_std_bal_table) THEN
250 ccid_range :=
251 'AND b1.code_combination_id between :start_id and :end_id ';
252 hint_txt := '/*+ INDEX(b1 gl_balances_n1) */';
253 ELSE
254 ccid_range := '';
255 hint_txt := '';
256 END IF;
257
258 StdUpdateFuncEntBalStr :=
259 'UPDATE ' || hint_txt || x_src_table || ' b1 ' ||
260 'SET (b1.period_net_dr_beq, b1.period_net_cr_beq, ' ||
261 ' b1.begin_balance_dr_beq, b1.begin_balance_cr_beq, ' ||
262 ' b1.quarter_to_date_dr_beq, b1.quarter_to_date_cr_beq, ' ||
263 ' b1.project_to_date_dr_beq, b1.project_to_date_cr_beq) ' ||
264 ' = (select /*+ INDEX (b2 gl_efb_upgrade_std_n1 ) */ ' ||
265 ' (b1.period_net_dr - nvl(sum(b2.period_net_dr_beq),0)), ' ||
266 ' (b1.period_net_cr - nvl(sum(b2.period_net_cr_beq),0)), ' ||
267 ' (b1.begin_balance_dr - nvl(sum(b2.begin_balance_dr_beq),0)), ' ||
268 ' (b1.begin_balance_cr - nvl(sum(b2.begin_balance_cr_beq),0)), ' ||
269 ' (b1.quarter_to_date_dr - nvl(sum(b2.quarter_to_date_dr_beq),0)),'||
270 ' (b1.quarter_to_date_cr - nvl(sum(b2.quarter_to_date_cr_beq),0)),'||
271 ' (b1.project_to_date_dr - nvl(sum(b2.project_to_date_dr_beq),0)),'||
272 ' (b1.project_to_date_cr - nvl(sum(b2.project_to_date_cr_beq),0)) '||
273 ' from gl_efb_upgrade_std b2 ' ||
274 ' where b2.period_name = b1.period_name ' ||
275 ' and b2.ledger_id = b1.ledger_id ' ||
276 ' and b2.actual_flag = ''A'' ' ||
277 ' and b2.translated_flag = ''R'' ' ||
278 ' and b2.code_combination_id = b1.code_combination_id) ' ||
279 'WHERE b1.currency_code <> ''STAT'' ' ||
280 'AND b1.actual_flag = ''A'' ' ||
281 ccid_range ||
282 'AND b1.translated_flag IS NULL ' ||
283 'AND b1.currency_code = ' ||
284 ' (select currency_code ' ||
285 ' from gl_ledgers ' ||
286 ' where ledger_id = b1.ledger_id)';
287
288 IF (x_src_table = g_std_bal_table) THEN
289 EXECUTE IMMEDIATE StdUpdateFuncEntBalStr USING x_start_id, x_end_id;
290 ELSE
291 EXECUTE IMMEDIATE StdUpdateFuncEntBalStr;
292 END IF;
293
294 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
295 g_api || '.' || fn_name);
296 EXCEPTION
297 WHEN OTHERS THEN
298 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
299 g_api || '.' || fn_name,
300 SUBSTR(SQLERRM, 1, 4000));
301 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
302 g_api || '.' || fn_name);
303 RAISE;
304 END update_std_func_ent_bal;
305
306
307 --
308 -- Procedure
309 -- prepare_adb_bal_gt
310 -- Purpose
311 -- Insert data into the ADB balances GT, gl_efb_upgrade_adb.
312 -- History
313 -- 03/10/2005 T Cheng Created
314 -- Arguments
315 -- x_src_table Source table of the balances, either GL_DAILY_BALANCES
316 -- or GL_MOVEMERGE_DAILY_BAL_<req_id>
317 -- x_start_id Start id for AD parallel range, gl_daily_balances only
318 -- x_end_id End id for AD parallel range, gl_daily_balances only
319 --
320 PROCEDURE prepare_adb_bal_gt(x_src_table VARCHAR2,
321 x_start_id NUMBER DEFAULT NULL,
322 x_end_id NUMBER DEFAULT NULL) IS
323 fn_name CONSTANT VARCHAR2(30) := 'PREPARE_ADB_BAL_GT';
324 AdbInterimInsertStr VARCHAR2(8000);
325 ccid_range VARCHAR2(100);
326 BEGIN
327 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
328 g_api || '.' || fn_name);
329 -- parameters
330 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
331 g_api || '.' || fn_name,
332 'x_src_table = ' || x_src_table);
333 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
334 g_api || '.' || fn_name,
335 'x_start_id = ' || x_start_id);
336 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
337 g_api || '.' || fn_name,
338 'x_end_id = ' || x_end_id);
339
340 IF (x_src_table = g_adb_bal_table) THEN
341 ccid_range :=
342 'AND b1.code_combination_id between :start_id and :end_id ';
343 ELSE
344 ccid_range := '';
345 END IF;
346
347 AdbInterimInsertStr :=
348 'INSERT INTO gl_efb_upgrade_adb ' ||
349 '(ledger_id, code_combination_id, currency_code,' ||
350 ' period_name, period_start_date, period_end_date,' ||
351 ' quarter_start_date, year_start_date,'||
352 ' period_type, period_year, period_num, template_id,' ||
353 ' opening_period_aggregate,' ||
354 ' opening_quarter_aggregate,' ||
355 ' opening_year_aggregate,' ||
356 ' period_aggregate1, period_aggregate2, period_aggregate3,'||
357 ' period_aggregate4, period_aggregate5, period_aggregate6,' ||
358 ' period_aggregate7, period_aggregate8, period_aggregate9,' ||
359 ' period_aggregate10, period_aggregate11, period_aggregate12,' ||
360 ' period_aggregate13, period_aggregate14, period_aggregate15,' ||
361 ' period_aggregate16, period_aggregate17, period_aggregate18,' ||
362 ' period_aggregate19, period_aggregate20, period_aggregate21,' ||
363 ' period_aggregate22, period_aggregate23, period_aggregate24,' ||
364 ' period_aggregate25, period_aggregate26, period_aggregate27,' ||
365 ' period_aggregate28, period_aggregate29, period_aggregate30,' ||
366 ' period_aggregate31, period_aggregate32, period_aggregate33,' ||
367 ' period_aggregate34, period_aggregate35) ' ||
368 'SELECT ' ||
369 'b1.ledger_id, b1.code_combination_id, max(ldg.currency_code), ' ||
370 'b1.period_name, max(b1.period_start_date), max(b1.period_end_date), ' ||
371 'max(b1.quarter_start_date), max(b1.year_start_date), ' ||
372 'max(b1.period_type), max(b1.period_year), max(b1.period_num), ' ||
373 'b1.template_id, ' ||
374 'sum(decode(b1.currency_code, ldg.currency_code,' ||
375 ' decode(b1.currency_type, ''U'', b1.opening_period_aggregate,' ||
376 ' ''C'', -b1.opening_period_aggregate,0),0)), ' ||
377 'sum(decode(b1.currency_code, ldg.currency_code,' ||
378 ' decode(b1.currency_type, ''U'', b1.opening_quarter_aggregate,' ||
379 ' ''C'', -b1.opening_quarter_aggregate,0),0)), ' ||
380 'sum(decode(b1.currency_code, ldg.currency_code,' ||
381 ' decode(b1.currency_type, ''U'', b1.opening_year_aggregate,'||
382 ' ''C'', -b1.opening_year_aggregate,0),0)), ' ||
383 'sum(decode(b1.currency_code, ldg.currency_code,' ||
384 ' decode(b1.currency_type, ''U'', b1.period_aggregate1,' ||
385 ' ''C'', -b1.period_aggregate1,0),0)), ' ||
386 'sum(decode(b1.currency_code, ldg.currency_code,' ||
387 ' decode(b1.currency_type, ''U'', b1.period_aggregate2,' ||
388 ' ''C'', -b1.period_aggregate2,0),0)), ' ||
389 'sum(decode(b1.currency_code, ldg.currency_code,' ||
390 ' decode(b1.currency_type, ''U'', b1.period_aggregate3,' ||
391 ' ''C'', -b1.period_aggregate3,0),0)), ' ||
392 'sum(decode(b1.currency_code, ldg.currency_code,' ||
393 ' decode(b1.currency_type, ''U'', b1.period_aggregate4,' ||
394 ' ''C'', -b1.period_aggregate4,0),0)), ' ||
395 'sum(decode(b1.currency_code, ldg.currency_code,' ||
396 ' decode(b1.currency_type, ''U'', b1.period_aggregate5,' ||
397 ' ''C'', -b1.period_aggregate5,0),0)), ' ||
398 'sum(decode(b1.currency_code, ldg.currency_code,' ||
399 ' decode(b1.currency_type, ''U'', b1.period_aggregate6,' ||
400 ' ''C'', -b1.period_aggregate6,0),0)), ' ||
401 'sum(decode(b1.currency_code, ldg.currency_code,' ||
402 ' decode(b1.currency_type, ''U'', b1.period_aggregate7,' ||
403 ' ''C'', -b1.period_aggregate7,0),0)), ' ||
404 'sum(decode(b1.currency_code, ldg.currency_code,' ||
405 ' decode(b1.currency_type, ''U'', b1.period_aggregate8,' ||
406 ' ''C'', -b1.period_aggregate8,0),0)), ' ||
407 'sum(decode(b1.currency_code, ldg.currency_code,' ||
408 ' decode(b1.currency_type, ''U'', b1.period_aggregate9,' ||
409 ' ''C'', -b1.period_aggregate9,0),0)), ' ||
410 'sum(decode(b1.currency_code, ldg.currency_code,' ||
411 ' decode(b1.currency_type, ''U'', b1.period_aggregate10,' ||
412 ' ''C'', -b1.period_aggregate10,0),0)), ' ||
413 'sum(decode(b1.currency_code, ldg.currency_code,' ||
414 ' decode(b1.currency_type, ''U'', b1.period_aggregate11,' ||
415 ' ''C'', -b1.period_aggregate11,0),0)), ' ||
416 'sum(decode(b1.currency_code, ldg.currency_code,' ||
417 ' decode(b1.currency_type, ''U'', b1.period_aggregate12,' ||
418 ' ''C'', -b1.period_aggregate12,0),0)), ' ||
419 'sum(decode(b1.currency_code, ldg.currency_code,' ||
420 ' decode(b1.currency_type, ''U'', b1.period_aggregate13,' ||
421 ' ''C'', -b1.period_aggregate13,0),0)), ' ||
422 'sum(decode(b1.currency_code, ldg.currency_code,' ||
423 ' decode(b1.currency_type, ''U'', b1.period_aggregate14,' ||
424 ' ''C'', -b1.period_aggregate14,0),0)), ' ||
425 'sum(decode(b1.currency_code, ldg.currency_code,' ||
426 ' decode(b1.currency_type, ''U'', b1.period_aggregate15,' ||
427 ' ''C'', -b1.period_aggregate15,0),0)), ' ||
428 'sum(decode(b1.currency_code, ldg.currency_code,' ||
429 ' decode(b1.currency_type, ''U'', b1.period_aggregate16,' ||
430 ' ''C'', -b1.period_aggregate16,0),0)), ' ||
431 'sum(decode(b1.currency_code, ldg.currency_code,' ||
432 ' decode(b1.currency_type, ''U'', b1.period_aggregate17,' ||
433 ' ''C'', -b1.period_aggregate17,0),0)), ' ||
434 'sum(decode(b1.currency_code, ldg.currency_code,' ||
435 ' decode(b1.currency_type, ''U'', b1.period_aggregate18,' ||
436 ' ''C'', -b1.period_aggregate18,0),0)), ' ||
437 'sum(decode(b1.currency_code, ldg.currency_code,' ||
438 ' decode(b1.currency_type, ''U'', b1.period_aggregate19,' ||
439 ' ''C'', -b1.period_aggregate19,0),0)), ' ||
440 'sum(decode(b1.currency_code, ldg.currency_code,' ||
441 ' decode(b1.currency_type, ''U'', b1.period_aggregate20,' ||
442 ' ''C'', -b1.period_aggregate20,0),0)), ' ||
443 'sum(decode(b1.currency_code, ldg.currency_code,' ||
444 ' decode(b1.currency_type, ''U'', b1.period_aggregate21,' ||
445 ' ''C'', -b1.period_aggregate21,0),0)), ' ||
446 'sum(decode(b1.currency_code, ldg.currency_code,' ||
447 ' decode(b1.currency_type, ''U'', b1.period_aggregate22,' ||
448 ' ''C'', -b1.period_aggregate22,0),0)), ' ||
449 'sum(decode(b1.currency_code, ldg.currency_code,' ||
450 ' decode(b1.currency_type, ''U'', b1.period_aggregate23,' ||
451 ' ''C'', -b1.period_aggregate23,0),0)), ' ||
452 'sum(decode(b1.currency_code, ldg.currency_code,' ||
453 ' decode(b1.currency_type, ''U'', b1.period_aggregate24,' ||
454 ' ''C'', -b1.period_aggregate24,0),0)), ' ||
455 'sum(decode(b1.currency_code, ldg.currency_code,' ||
456 ' decode(b1.currency_type, ''U'', b1.period_aggregate25,' ||
457 ' ''C'', -b1.period_aggregate25,0),0)), ' ||
458 'sum(decode(b1.currency_code, ldg.currency_code,' ||
459 ' decode(b1.currency_type, ''U'', b1.period_aggregate26,' ||
460 ' ''C'', -b1.period_aggregate26,0),0)), ' ||
461 'sum(decode(b1.currency_code, ldg.currency_code,' ||
462 ' decode(b1.currency_type, ''U'', b1.period_aggregate27,' ||
463 ' ''C'', -b1.period_aggregate27,0),0)), ' ||
464 'sum(decode(b1.currency_code, ldg.currency_code,' ||
465 ' decode(b1.currency_type, ''U'', b1.period_aggregate28,' ||
466 ' ''C'', -b1.period_aggregate28,0),0)), ' ||
467 'sum(decode(b1.currency_code, ldg.currency_code,' ||
468 ' decode(b1.currency_type, ''U'', b1.period_aggregate29,' ||
469 ' ''C'', -b1.period_aggregate29,0),0)), ' ||
470 'sum(decode(b1.currency_code, ldg.currency_code,' ||
471 ' decode(b1.currency_type, ''U'', b1.period_aggregate30,' ||
472 ' ''C'', -b1.period_aggregate30,0),0)), ' ||
473 'sum(decode(b1.currency_code, ldg.currency_code,' ||
474 ' decode(b1.currency_type, ''U'', b1.period_aggregate31,' ||
475 ' ''C'', -b1.period_aggregate31,0),0)), ' ||
476 'sum(decode(b1.currency_code, ldg.currency_code,' ||
477 ' decode(b1.currency_type, ''U'', b1.period_aggregate32,' ||
478 ' ''C'', -b1.period_aggregate32,0),0)), ' ||
479 'sum(decode(b1.currency_code, ldg.currency_code,' ||
480 ' decode(b1.currency_type, ''U'', b1.period_aggregate33,' ||
481 ' ''C'', -b1.period_aggregate33,0),0)), ' ||
482 'sum(decode(b1.currency_code, ldg.currency_code,' ||
483 ' decode(b1.currency_type, ''U'', b1.period_aggregate34,' ||
484 ' ''C'', -b1.period_aggregate34,0),0)), ' ||
485 'sum(decode(b1.currency_code, ldg.currency_code,' ||
486 ' decode(b1.currency_type, ''U'', b1.period_aggregate35,' ||
487 ' ''C'', -b1.period_aggregate35,0),0)) ' ||
488 'FROM ' || x_src_table || ' b1, gl_ledgers ldg ' ||
489 'WHERE ldg.ledger_id = b1.ledger_id ' ||
490 'AND ldg.currency_code = b1.currency_code ' ||
491 'AND ldg.enable_average_balances_flag = ''Y'' ' ||
492 'AND b1.actual_flag = ''A'' ' ||
493 ccid_range ||
494 'GROUP BY b1.ledger_id, b1.code_combination_id, ' ||
495 ' b1.period_name, b1.template_id';
496
497 IF (x_src_table = g_adb_bal_table) THEN
498 EXECUTE IMMEDIATE AdbInterimInsertStr USING x_start_id, x_end_id;
499 ELSE
500 EXECUTE IMMEDIATE AdbInterimInsertStr;
501 END IF;
502
503 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
504 g_api || '.' || fn_name);
505 EXCEPTION
506 WHEN OTHERS THEN
507 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
508 g_api || '.' || fn_name,
509 SUBSTR(SQLERRM, 1, 4000));
510 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
511 g_api || '.' || fn_name);
512 RAISE;
513 END prepare_adb_bal_gt;
514
515
516 --
517 -- Procedure
518 -- update_adb_func_ent_bal
519 -- Purpose
520 -- Update existing functional entered balances.
521 -- History
522 -- 03/10/2005 T Cheng Created
523 -- Arguments
524 -- x_src_table Source table of the balances, either GL_DAILY_BALANCES
525 -- or GL_MOVEMERGE_DAILY_BAL_<req_id>
526 -- x_start_id Start id for AD parallel range, gl_daily_balances only
527 -- x_end_id End id for AD parallel range, gl_daily_balances only
528 --
529 PROCEDURE update_adb_func_ent_bal(x_src_table VARCHAR2,
530 x_start_id NUMBER DEFAULT NULL,
531 x_end_id NUMBER DEFAULT NULL) IS
532 fn_name CONSTANT VARCHAR2(30) := 'UPDATE_ADB_FUNC_ENT_BAL';
533 AdbUpdateFuncEntBalStr VARCHAR2(2600);
534 ccid_range VARCHAR2(200);
535 BEGIN
536 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
537 g_api || '.' || fn_name);
538 -- parameters
539 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
540 g_api || '.' || fn_name,
541 'x_src_table = ' || x_src_table);
542 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
543 g_api || '.' || fn_name,
544 'x_start_id = ' || x_start_id);
545 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
546 g_api || '.' || fn_name,
547 'x_end_id = ' || x_end_id);
548
549 IF (x_src_table = g_adb_bal_table) THEN
550 ccid_range :=
551 'AND b1.code_combination_id between :start_id and :end_id ';
552 ELSE
553 ccid_range := '';
554 END IF;
555
556 AdbUpdateFuncEntBalStr :=
557 'UPDATE ' || x_src_table || ' b1 ' ||
558 'SET (b1.opening_period_aggregate,'||
559 ' b1.opening_quarter_aggregate,' ||
560 ' b1.opening_year_aggregate,' ||
561 ' b1.period_aggregate1, b1.period_aggregate2,' ||
562 ' b1.period_aggregate3, b1.period_aggregate4,' ||
563 ' b1.period_aggregate5, b1.period_aggregate6,' ||
564 ' b1.period_aggregate7, b1.period_aggregate8,' ||
565 ' b1.period_aggregate9, b1.period_aggregate10,' ||
566 ' b1.period_aggregate11, b1.period_aggregate12,' ||
567 ' b1.period_aggregate13, b1.period_aggregate14,' ||
568 ' b1.period_aggregate15, b1.period_aggregate16,' ||
569 ' b1.period_aggregate17, b1.period_aggregate18,' ||
570 ' b1.period_aggregate19, b1.period_aggregate20,' ||
571 ' b1.period_aggregate21, b1.period_aggregate22,' ||
572 ' b1.period_aggregate23, b1.period_aggregate24,' ||
573 ' b1.period_aggregate25, b1.period_aggregate26,' ||
574 ' b1.period_aggregate27, b1.period_aggregate28,' ||
575 ' b1.period_aggregate29, b1.period_aggregate30,' ||
576 ' b1.period_aggregate31, b1.period_aggregate32,' ||
577 ' b1.period_aggregate33, b1.period_aggregate34,' ||
578 ' b1.period_aggregate35) ' ||
579 ' = (SELECT '||
580 ' di.opening_period_aggregate,' ||
581 ' di.opening_quarter_aggregate,' ||
582 ' di.opening_year_aggregate,' ||
583 ' di.period_aggregate1, di.period_aggregate2,' ||
584 ' di.period_aggregate3, di.period_aggregate4,' ||
585 ' di.period_aggregate5, di.period_aggregate6,' ||
586 ' di.period_aggregate7, di.period_aggregate8,' ||
587 ' di.period_aggregate9, di.period_aggregate10,' ||
588 ' di.period_aggregate11, di.period_aggregate12,' ||
589 ' di.period_aggregate13, di.period_aggregate14,' ||
590 ' di.period_aggregate15, di.period_aggregate16,' ||
591 ' di.period_aggregate17, di.period_aggregate18,' ||
592 ' di.period_aggregate19, di.period_aggregate20,' ||
593 ' di.period_aggregate21, di.period_aggregate22,' ||
594 ' di.period_aggregate23, di.period_aggregate24,' ||
595 ' di.period_aggregate25, di.period_aggregate26,' ||
596 ' di.period_aggregate27, di.period_aggregate28,' ||
597 ' di.period_aggregate29, di.period_aggregate30,' ||
598 ' di.period_aggregate31, di.period_aggregate32,' ||
599 ' di.period_aggregate33, di.period_aggregate34,' ||
600 ' di.period_aggregate35' ||
601 ' FROM gl_efb_upgrade_adb di' ||
602 ' WHERE di.ledger_id = b1.ledger_id' ||
603 ' AND di.code_combination_id = b1.code_combination_id' ||
604 ' AND di.currency_code = b1.currency_code' ||
605 ' AND di.period_name = b1.period_name) ' ||
606 'WHERE b1.currency_code = ' ||
607 ' (select currency_code' ||
608 ' from gl_ledgers' ||
609 ' where ledger_id = b1.ledger_id) ' ||
610 'AND b1.actual_flag = ''A'' ' ||
611 'AND b1.currency_type = ''E'' ' ||
612 'AND b1.converted_from_currency IS NULL ' ||
613 ccid_range;
614
615 IF (x_src_table = g_adb_bal_table) THEN
616 EXECUTE IMMEDIATE AdbUpdateFuncEntBalStr USING x_start_id, x_end_id;
617 ELSE
618 EXECUTE IMMEDIATE AdbUpdateFuncEntBalStr;
619 END IF;
620
621 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
622 g_api || '.' || fn_name);
623 EXCEPTION
624 WHEN OTHERS THEN
625 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
626 g_api || '.' || fn_name,
627 SUBSTR(SQLERRM, 1, 4000));
628 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
629 g_api || '.' || fn_name);
630 RAISE;
631 END update_adb_func_ent_bal;
632
633
634 --
635 -- Procedure
636 -- insert_adb_func_ent_bal
637 -- Purpose
638 -- Insert missing functional entered balances.
639 -- History
640 -- 03/10/2005 T Cheng Created
641 -- Arguments
642 -- x_src_table Source table of the balances, either GL_DAILY_BALANCES
643 -- or GL_MOVEMERGE_DAILY_BAL_<req_id>
644 --
645 PROCEDURE insert_adb_func_ent_bal(x_src_table VARCHAR2) IS
646 fn_name CONSTANT VARCHAR2(30) := 'INSERT_ADB_FUNC_ENT_BAL';
647 AdbInsertFuncEntBalStr VARCHAR2(2800);
648
649 l_who_cols VARCHAR2(150);
650 l_who_vals VARCHAR2(30);
651 BEGIN
652 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
653 g_api || '.' || fn_name);
654 -- parameters
655 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
656 g_api || '.' || fn_name,
657 'x_src_table = ' || x_src_table);
658
659 IF (x_src_table = g_adb_bal_table) THEN
660 l_who_cols := ' creation_date, created_by, last_update_date,' ||
661 ' last_updated_by, last_update_login,';
662 l_who_vals := ' sysdate, 1, sysdate, 1, 0,';
663 ELSE
664 l_who_cols := '';
665 l_who_vals := '';
666 END IF;
667
668 AdbInsertFuncEntBalStr :=
669 'INSERT INTO ' || x_src_table || ' ' ||
670 '(ledger_id, code_combination_id, currency_code,' ||
671 ' currency_type, actual_flag, period_name,' ||
672 ' period_start_date, period_end_date,' ||
673 ' quarter_start_date, year_start_date,' ||
674 l_who_cols ||
675 ' converted_from_currency, period_type, period_year,' ||
676 ' period_num, template_id,' ||
677 ' opening_period_aggregate,' ||
678 ' opening_quarter_aggregate,' ||
679 ' opening_year_aggregate,' ||
680 ' period_aggregate1, period_aggregate2, period_aggregate3,'||
681 ' period_aggregate4, period_aggregate5, period_aggregate6,' ||
682 ' period_aggregate7, period_aggregate8, period_aggregate9,' ||
683 ' period_aggregate10, period_aggregate11, period_aggregate12,' ||
684 ' period_aggregate13, period_aggregate14, period_aggregate15,' ||
685 ' period_aggregate16, period_aggregate17, period_aggregate18,' ||
686 ' period_aggregate19, period_aggregate20, period_aggregate21,' ||
687 ' period_aggregate22, period_aggregate23, period_aggregate24,' ||
688 ' period_aggregate25, period_aggregate26, period_aggregate27,' ||
689 ' period_aggregate28, period_aggregate29, period_aggregate30,' ||
690 ' period_aggregate31, period_aggregate32, period_aggregate33,' ||
691 ' period_aggregate34, period_aggregate35) ' ||
692 'SELECT ' ||
693 ' di.ledger_id, di.code_combination_id, di.currency_code,' ||
694 ' ''E'', ''A'', di.period_name,' ||
695 ' di.period_start_date, di.period_end_date,' ||
696 ' di.quarter_start_date, di.year_start_date,' ||
697 l_who_vals ||
698 ' NULL, di.period_type, di.period_year,' ||
699 ' di.period_num, di.template_id,' ||
700 ' di.opening_period_aggregate,' ||
701 ' di.opening_quarter_aggregate,' ||
702 ' di.opening_year_aggregate,' ||
703 ' di.period_aggregate1, di.period_aggregate2, di.period_aggregate3,' ||
704 ' di.period_aggregate4, di.period_aggregate5, di.period_aggregate6,' ||
705 ' di.period_aggregate7, di.period_aggregate8, di.period_aggregate9,' ||
706 ' di.period_aggregate10, di.period_aggregate11, di.period_aggregate12,'||
707 ' di.period_aggregate13, di.period_aggregate14, di.period_aggregate15,'||
708 ' di.period_aggregate16, di.period_aggregate17, di.period_aggregate18,'||
709 ' di.period_aggregate19, di.period_aggregate20, di.period_aggregate21,'||
710 ' di.period_aggregate22, di.period_aggregate23, di.period_aggregate24,'||
711 ' di.period_aggregate25, di.period_aggregate26, di.period_aggregate27,'||
712 ' di.period_aggregate28, di.period_aggregate29, di.period_aggregate30,'||
713 ' di.period_aggregate31, di.period_aggregate32, di.period_aggregate33,'||
714 ' di.period_aggregate34, di.period_aggregate35 ' ||
715 'FROM gl_efb_upgrade_adb di ' ||
716 'WHERE not exists' ||
717 ' (select 1' ||
718 ' from ' || x_src_table || ' b2' ||
719 ' where b2.ledger_id = di.ledger_id' ||
720 ' and b2.code_combination_id = di.code_combination_id' ||
721 ' and b2.currency_code = di.currency_code' ||
722 ' and b2.currency_type = ''E''' ||
723 ' and b2.actual_flag = ''A''' ||
724 ' and b2.period_name = di.period_name' ||
725 ' and b2.converted_from_currency is null)';
726
727 EXECUTE IMMEDIATE AdbInsertFuncEntBalStr;
728
729 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
730 g_api || '.' || fn_name);
731 EXCEPTION
732 WHEN OTHERS THEN
733 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
734 g_api || '.' || fn_name,
735 SUBSTR(SQLERRM, 1, 4000));
736 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
737 g_api || '.' || fn_name);
738 RAISE;
739 END insert_adb_func_ent_bal;
740
741
742 --
743 -- Procedure
744 -- check_mm_table_column
745 -- Purpose
746 -- Check if the given move/merge table name exists, and if it has
747 -- a set_of_books_id column.
748 -- History
749 -- 03/11/2005 T Cheng Created
750 -- Arguments
751 -- x_gl_schema GL schema
752 -- x_table_name Movemerge interim table name
753 -- x_table_exists If the given table exists
754 -- x_column_exists If a set_of_books_id column exists in the table.
755 -- Default to false if table does not exist.
756 --
757 PROCEDURE check_mm_table_column(x_gl_schema VARCHAR2,
758 x_table_name VARCHAR2,
759 x_table_exists OUT NOCOPY BOOLEAN,
760 x_column_exists OUT NOCOPY BOOLEAN) IS
761 fn_name CONSTANT VARCHAR2(30) := 'CHECK_MM_TABLE_COLUMN';
762 l_table_exists NUMBER := 0;
763 l_column_exists NUMBER := 0;
764 BEGIN
765 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
766 g_api || '.' || fn_name);
767 -- parameters
768 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
769 g_api || '.' || fn_name,
770 'x_gl_schema = ' || x_gl_schema);
771 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
772 g_api || '.' || fn_name,
773 'x_table_name = ' || x_table_name);
774
775 x_table_exists := FALSE;
776 x_column_exists := FALSE;
777
778 -- check if table exists
779 SELECT nvl(max(1), 0)
780 INTO l_table_exists
781 FROM DBA_TABLES
782 WHERE table_name = x_table_name
783 AND owner = x_gl_schema;
784
785 IF (l_table_exists = 1) THEN
786 x_table_exists := TRUE;
787
788 -- check if the table was created in 11i with set_of_books_id column
789 SELECT nvl(max(1), 0)
790 INTO l_column_exists
791 FROM ALL_TAB_COLUMNS
792 WHERE table_name = x_table_name
793 AND owner = x_gl_schema
794 AND column_name = 'SET_OF_BOOKS_ID';
795
796 IF (l_column_exists = 1) THEN
797 x_column_exists := TRUE;
798 END IF;
799 END IF;
800
801 -- out parameters (print the local variables)
802 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
803 g_api || '.' || fn_name,
804 'l_table_exists = ' || l_table_exists);
805 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
806 g_api || '.' || fn_name,
807 'l_column_exists = ' || l_column_exists);
808 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
809 g_api || '.' || fn_name);
810 EXCEPTION
811 WHEN OTHERS THEN
812 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
813 g_api || '.' || fn_name,
814 SUBSTR(SQLERRM, 1, 4000));
815 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
816 g_api || '.' || fn_name);
817 RAISE;
818 END check_mm_table_column;
819
820 --
821 -- Procedure
822 -- alter_movemerge_tables
823 -- Purpose
824 -- Add new columns to the move/merge interim tables for the given request.
825 -- History
826 -- 03/11/2005 T Cheng Created
827 -- Arguments
828 -- x_mm_request_id Move/Merge request ID
829 -- x_gl_schema GL schema
830 -- x_applsys_schema FND schema
831 --
832 PROCEDURE alter_movemerge_tables(x_mm_request_id NUMBER,
833 x_gl_schema VARCHAR2,
834 x_applsys_schema VARCHAR2,
835 x_std_tab_exists OUT NOCOPY BOOLEAN,
836 x_adb_tab_exists OUT NOCOPY BOOLEAN) IS
837 fn_name CONSTANT VARCHAR2(30) := 'ALTER_MOVEMERGE_TABLES';
838 l_table_name VARCHAR2(30);
839 l_column_exists BOOLEAN;
840
841 sql_stmt VARCHAR2(200);
842 BEGIN
843 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
844 g_api || '.' || fn_name);
845 -- parameters
846 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
847 g_api || '.' || fn_name,
848 'x_mm_request_id = ' || x_mm_request_id);
849 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
850 g_api || '.' || fn_name,
851 'x_gl_schema = ' || x_gl_schema);
852 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
853 g_api || '.' || fn_name,
854 'x_applsys_schema = ' || x_applsys_schema);
855
856 -- upgrade standard balances table
857 l_table_name := g_mm_std_table || x_mm_request_id;
858
859 check_mm_table_column(x_gl_schema, l_table_name,
860 x_std_tab_exists, l_column_exists);
861 IF (l_column_exists) THEN
862 -- rename set_books_id column to ledger_id
863 sql_stmt := 'ALTER TABLE ' || l_table_name ||
864 ' RENAME COLUMN set_of_books_id TO ledger_id';
865
866 AD_DDL.DO_DDL(x_applsys_schema, 'SQLGL', AD_DDL.ALTER_TABLE,
867 sql_stmt, l_table_name);
868
869 -- add four BEQ columns
870 sql_stmt := 'ALTER TABLE ' || l_table_name || ' ADD ' ||
871 '(QUARTER_TO_DATE_DR_BEQ NUMBER,' ||
872 ' QUARTER_TO_DATE_CR_BEQ NUMBER,' ||
873 ' PROJECT_TO_DATE_DR_BEQ NUMBER,' ||
874 ' PROJECT_TO_DATE_CR_BEQ NUMBER)';
875
876 AD_DDL.DO_DDL(x_applsys_schema, 'SQLGL', AD_DDL.ALTER_TABLE,
877 sql_stmt, l_table_name);
878 END IF;
879
880 -- upgrade ADB balances table
881 l_table_name := g_mm_adb_table || x_mm_request_id;
882
883 check_mm_table_column(x_gl_schema, l_table_name,
884 x_adb_tab_exists, l_column_exists);
885 IF (l_column_exists) THEN
886 -- rename set_books_id column to ledger_id
887 sql_stmt := 'ALTER TABLE ' || l_table_name ||
888 ' RENAME COLUMN set_of_books_id TO ledger_id';
889
890 AD_DDL.DO_DDL(x_applsys_schema, 'SQLGL', AD_DDL.ALTER_TABLE,
891 sql_stmt, l_table_name);
892 END IF;
893
894 -- dummy commit, to make sure the rollback segment will be set
895 FND_CONCURRENT.AF_COMMIT;
896
897 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
898 g_api || '.' || fn_name);
899 EXCEPTION
900 WHEN OTHERS THEN
901 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
902 g_api || '.' || fn_name,
903 SUBSTR(SQLERRM, 1, 4000));
904 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
905 g_api || '.' || fn_name);
906 RAISE;
907 END alter_movemerge_tables;
908
909
910 --
911 -- PUBLIC FUNCTIONS
912 --
913
914 PROCEDURE upgrade_ent_func_bal(
915 x_errbuf OUT NOCOPY VARCHAR2,
916 x_retcode OUT NOCOPY VARCHAR2,
917 x_batch_size NUMBER,
918 x_num_workers NUMBER) IS
919 fn_name CONSTANT VARCHAR2(30) := 'UPGRADE_ENT_FUNC_BAL';
920 SUBMIT_REQ_ERROR EXCEPTION;
921
922 l_req_data VARCHAR2(10);
923 l_req_id NUMBER;
924
925 l_efb_upgrade_flag VARCHAR2(1);
926
927 l_retstatus BOOLEAN;
928 l_status VARCHAR2(30);
929 l_industry VARCHAR2(30);
930 l_table_owner VARCHAR2(30);
931 l_gl_schema VARCHAR2(30);
932 l_applsys_schema VARCHAR2(30);
933 BEGIN
934 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
935 g_api || '.' || fn_name);
936 -- parameters
937 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
938 g_api || '.' || fn_name,
939 'x_batch_size = ' || x_batch_size);
940 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
941 g_api || '.' || fn_name,
942 'x_num_workers = ' || x_num_workers);
943
944 -- AD_CONC_UTILS_PKG.submit_subrequests sets request data
945 l_req_data := FND_CONC_GLOBAL.request_data;
946
947 IF (l_req_data IS NULL) THEN -- First time
948 -- if the balances upgrade were done, no need to submit requests
949 SELECT efb_upgrade_flag
950 INTO l_efb_upgrade_flag
951 FROM GL_SYSTEM_USAGES
952 WHERE rownum = 1;
953
954 IF (l_efb_upgrade_flag <> 'Y') THEN
955 -- get schema name for GL and FND
956 l_retstatus := fnd_installation.get_app_info(
957 'SQLGL', l_status, l_industry, l_gl_schema);
958 IF ( (NOT l_retstatus)
959 OR (l_gl_schema is null)) THEN
960 raise_application_error(-20001,
961 'Cannot get schema name for product : SQLGL');
962 END IF;
963
964 l_retstatus := fnd_installation.get_app_info(
965 'FND', l_status, l_industry, l_applsys_schema);
966 IF ( (NOT l_retstatus)
967 OR (l_applsys_schema is null)) THEN
968 raise_application_error(-20001,
969 'Cannot get schema name for product : FND');
970 END IF;
971
972 -- Submit one child request for move/merge
973 l_req_id := FND_REQUEST.submit_request(
974 APPLICATION => 'SQLGL',
975 PROGRAM => 'GLEFBMM',
976 SUB_REQUEST => TRUE,
977 ARGUMENT1 => l_gl_schema,
978 ARGUMENT2 => l_applsys_schema);
979
980 IF (l_req_id = 0) THEN
981 RAISE SUBMIT_REQ_ERROR;
982 END IF;
983
984 -- Clean up AD update information in case number of workers changed
985 -- Note: this procedure implicitly commits
986 AD_PARALLEL_UPDATES_PKG.delete_update_information(
987 ad_parallel_updates_pkg.ID_RANGE,
988 l_gl_schema,
989 g_table_name,
990 g_script_name);
991
992 -- Submit child requests to upgrade balances
993 AD_CONC_UTILS_PKG.submit_subrequests(
994 X_errbuf => x_errbuf,
995 X_retcode => x_retcode,
996 X_workerconc_app_shortname => 'SQLGL',
997 X_workerconc_progname => 'GLEFBAL',
998 X_batch_size => x_batch_size,
999 -- One worker is used for the move/merge request
1000 X_num_workers => x_num_workers - 1,
1001 X_argument4 => l_gl_schema);
1002
1003 -- If the request data hasn't been set, then the AD API did not
1004 -- successfully submit all child requests.
1005 l_req_data := FND_CONC_GLOBAL.request_data;
1006 IF (l_req_data IS NULL) THEN
1007 RAISE SUBMIT_REQ_ERROR;
1008 END IF;
1009
1010 ELSE
1011 GL_MESSAGE.WRITE_LOG(msg_name => 'EFCB0001',
1012 log_level => FND_LOG.LEVEL_PROCEDURE,
1013 module => g_api || '.' || fn_name);
1014 x_retcode := AD_CONC_UTILS_PKG.CONC_WARNING;
1015 END IF;
1016
1017 ELSE -- Restart case
1018 -- check status of all subrequests (including the move/merge one, since
1019 -- the program is not really used for a restart)
1020 -- * If we want to produce an execution report, it may be more effecient
1021 -- not to use the API since that would mean we are getting
1022 -- sub-requests and loop through them twice.
1023
1024 AD_CONC_UTILS_PKG.submit_subrequests(
1025 X_errbuf => x_errbuf,
1026 X_retcode => x_retcode,
1027 -- for restart, the rest of the parameters are not really used
1028 X_workerconc_app_shortname => 'SQLGL',
1029 X_workerconc_progname => 'GLEFBAL',
1030 X_batch_size => x_batch_size,
1031 X_num_workers => x_num_workers - 1,
1032 X_argument4 => l_gl_schema);
1033
1034 IF (x_retcode = AD_CONC_UTILS_PKG.CONC_SUCCESS) THEN
1035 UPDATE GL_SYSTEM_USAGES
1036 SET efb_upgrade_flag = 'Y',
1037 last_update_date = sysdate,
1038 last_updated_by = 1,
1039 last_update_login = 0;
1040 END IF;
1041 END IF;
1042
1043 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
1044 g_api || '.' || fn_name);
1045 EXCEPTION
1046 WHEN SUBMIT_REQ_ERROR THEN
1047 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
1048 GL_MESSAGE.WRITE_LOG(msg_name => 'SHRD0055',
1049 token_num => 1,
1050 t1 => 'ROUTINE',
1051 v1 => fn_name,
1052 log_level => FND_LOG.LEVEL_PROCEDURE,
1053 module => g_api || '.' || fn_name);
1054 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
1055 g_api || '.' || fn_name);
1056 RAISE;
1057 WHEN OTHERS THEN
1058 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
1059 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
1060 g_api || '.' || fn_name,
1061 SUBSTR(SQLERRM, 1, 4000));
1062 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
1063 g_api || '.' || fn_name);
1064 RAISE;
1065 END upgrade_ent_func_bal;
1066
1067
1068 PROCEDURE upgrade_balance_tables(
1069 x_errbuf OUT NOCOPY VARCHAR2,
1070 x_retcode OUT NOCOPY VARCHAR2,
1071 x_batch_size NUMBER,
1072 x_worker_Id NUMBER,
1073 x_num_workers NUMBER,
1074 x_argument4 VARCHAR2) IS
1075 fn_name CONSTANT VARCHAR2(30) := 'UPGRADE_BALANCE_TABLES';
1076
1077 l_any_rows_to_process BOOLEAN;
1078
1079 l_start_id NUMBER;
1080 l_end_id NUMBER;
1081 l_rows_processed NUMBER;
1082 BEGIN
1083 FND_FILE.PUT_LINE(FND_FILE.LOG, 'X_Worker_Id : ' || X_Worker_Id);
1084 FND_FILE.PUT_LINE(FND_FILE.LOG, 'X_Num_Workers : ' || X_Num_Workers);
1085 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Session Id : ' ||
1086 FND_GLOBAL.session_id);
1087
1088 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
1089 g_api || '.' || fn_name);
1090 -- parameters
1091 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
1092 g_api || '.' || fn_name,
1093 'x_batch_size = ' || x_batch_size);
1094 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
1095 g_api || '.' || fn_name,
1096 'x_argument4 = ' || x_argument4);
1097
1098 ad_parallel_updates_pkg.initialize_id_range(
1099 ad_parallel_updates_pkg.ID_RANGE,
1100 x_argument4,
1101 g_table_name,
1102 g_script_name,
1103 g_id_column,
1104 x_worker_id,
1105 x_num_workers,
1106 x_batch_size, 0);
1107
1108 ad_parallel_updates_pkg.get_id_range(
1109 l_start_id,
1110 l_end_id,
1111 l_any_rows_to_process,
1112 x_batch_size,
1113 TRUE);
1114
1115 while (l_any_rows_to_process)
1116 loop
1117 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_start_id : ' || l_start_id);
1118 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_end_id : ' || l_end_id);
1119
1120 prepare_std_bal_gt(g_std_bal_table, l_start_id, l_end_id);
1121 update_std_foreign_ent_bal(g_std_bal_table, l_start_id, l_end_id);
1122 update_std_func_ent_bal(g_std_bal_table, l_start_id, l_end_id);
1123
1124 prepare_adb_bal_gt(g_adb_bal_table, l_start_id, l_end_id);
1125 update_adb_func_ent_bal(g_adb_bal_table, l_start_id, l_end_id);
1126 insert_adb_func_ent_bal(g_adb_bal_table);
1127
1128 SELECT count(*)
1129 INTO l_rows_processed
1130 FROM gl_code_combinations
1131 WHERE code_combination_id between l_start_id and l_end_id;
1132
1133 ad_parallel_updates_pkg.processed_id_range(
1134 l_rows_processed,
1135 l_end_id);
1136
1137 fnd_concurrent.af_commit;
1138
1139 ad_parallel_updates_pkg.get_id_range(
1140 l_start_id,
1141 l_end_id,
1142 l_any_rows_to_process,
1143 x_batch_size,
1144 FALSE);
1145
1146 end loop;
1147
1148 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
1149
1150 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
1151 g_api || '.' || fn_name);
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 x_errbuf := SUBSTR(SQLERRM, 1, 240);
1155 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
1156 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
1157 g_api || '.' || fn_name,
1158 SUBSTR(SQLERRM, 1, 4000));
1159 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
1160 g_api || '.' || fn_name);
1161 RAISE;
1162 END upgrade_balance_tables;
1163
1164
1165 PROCEDURE upgrade_movemerge_int_tables(
1166 x_errbuf OUT NOCOPY VARCHAR2,
1167 x_retcode OUT NOCOPY VARCHAR2,
1168 x_gl_schema VARCHAR2,
1169 x_applsys_schema VARCHAR2) IS
1170 fn_name CONSTANT VARCHAR2(30) := 'UPGRADE_MOVEMERGE_INT_TABLES';
1171
1172 l_last_purged_eff_period_num NUMBER;
1173 l_std_table_exists BOOLEAN;
1174 l_adb_table_exists BOOLEAN;
1175
1176 l_table_name VARCHAR2(30);
1177 sql_stmt VARCHAR2(200);
1178
1179 CURSOR c_ledgers IS
1180 SELECT ledger_id
1181 FROM GL_LEDGERS
1182 WHERE object_type_code = 'L';
1183
1184 -- requests that have completed move/merge successfully
1185 CURSOR c_mm_requests(v_ledger_id NUMBER) IS
1186 SELECT movemerge_request_id mm_req_id
1187 FROM gl_movemerge_requests
1188 WHERE ledger_id = v_ledger_id
1189 AND status_code = 'MC';
1190 BEGIN
1191 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
1192 g_api || '.' || fn_name);
1193 -- parameters
1194 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
1195 g_api || '.' || fn_name,
1196 'x_gl_schema = ' || x_gl_schema);
1197 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
1198 g_api || '.' || fn_name,
1199 'x_applsys_schema = ' || x_applsys_schema);
1200
1201 FOR rec IN c_ledgers LOOP
1202 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ledger_id : ' || rec.ledger_id);
1203
1204 -- get latest purged period for the ledger
1205 SELECT NVL(MAX(last_purged_eff_period_num), 0)
1206 INTO l_last_purged_eff_period_num
1207 FROM GL_ARCHIVE_HISTORY
1208 WHERE ledger_id = rec.ledger_id
1209 AND actual_flag = 'A'
1210 AND data_type = 'A';
1211
1212 FOR req IN c_mm_requests(rec.ledger_id) LOOP
1213 FND_FILE.PUT_LINE(FND_FILE.LOG, 'mm_req_id : ' || req.mm_req_id);
1214
1215 -- data model changes
1216 alter_movemerge_tables(req.mm_req_id, x_gl_schema, x_applsys_schema,
1217 l_std_table_exists, l_adb_table_exists);
1218 -- data upgrades:
1219 -- always delete data for purged periods first, then upgrade data
1220 IF (l_std_table_exists) THEN
1221 l_table_name := g_mm_std_table || req.mm_req_id;
1222 sql_stmt := 'DELETE FROM ' || l_table_name ||
1223 ' WHERE (PERIOD_YEAR * 10000 + PERIOD_NUM) <= :p_num' ||
1224 ' AND ACTUAL_FLAG = ''A'' ';
1225 EXECUTE IMMEDIATE sql_stmt USING l_last_purged_eff_period_num;
1226
1227 prepare_std_bal_gt(l_table_name);
1228 update_std_foreign_ent_bal(l_table_name);
1229 update_std_func_ent_bal(l_table_name);
1230 END IF;
1231
1232 IF (l_adb_table_exists) THEN
1233 l_table_name := g_mm_adb_table || req.mm_req_id;
1234 sql_stmt := 'DELETE FROM ' || l_table_name ||
1235 ' WHERE (PERIOD_YEAR * 10000 + PERIOD_NUM) <= :p_num' ||
1236 ' AND ACTUAL_FLAG = ''A'' ';
1237 EXECUTE IMMEDIATE sql_stmt USING l_last_purged_eff_period_num;
1238
1239 prepare_adb_bal_gt(l_table_name);
1240 update_adb_func_ent_bal(l_table_name);
1241 insert_adb_func_ent_bal(l_table_name);
1242 END IF;
1243
1244 FND_CONCURRENT.AF_COMMIT;
1245 END LOOP;
1246 END LOOP;
1247
1248 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
1249
1250 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
1251 g_api || '.' || fn_name);
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 x_errbuf := SUBSTR(SQLERRM, 1, 240);
1255 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
1256 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
1257 g_api || '.' || fn_name,
1258 SUBSTR(SQLERRM, 1, 4000));
1259 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
1260 g_api || '.' || fn_name);
1261 RAISE;
1262 END upgrade_movemerge_int_tables;
1263
1264 END GL_ENT_FUNC_BAL_UPGRADE_PKG;