[Home] [Help]
PACKAGE BODY: APPS.GCS_PERIOD_INIT_DYN_BUILD_PKG
Source
1 PACKAGE BODY GCS_PERIOD_INIT_DYN_BUILD_PKG AS
2 /* $Header: gcspinbb.pls 120.5 2006/09/08 00:30:34 skamdar noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 g_api CONSTANT VARCHAR2(40) := 'gcs.plsql.GCS_PERIOD_INIT_DYN_BUILD_PKG';
8 g_line_size NUMBER := 250;
9
10 g_sel_stmt VARCHAR2(8000);
11
12 --
13 -- PRIVATE FUNCTIONS
14 --
15
16 --
17 -- Procedure
18 -- build_dim_str
19 --
20 PROCEDURE build_dim_str(p_dim_col VARCHAR2) IS
21 fn_name VARCHAR2(30);
22 dim_required VARCHAR2(1);
23 dim_str VARCHAR2(400);
24 BEGIN
25 fn_name := 'BUILD_DIM_STR';
26 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
27 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
28 g_api || '.' || fn_name,
29 GCS_UTILITY_PKG.g_module_enter || fn_name ||
30 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
31 END IF;
32 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
33 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
34
35 -- **************************************************
36
37 dim_required := GCS_UTILITY_PKG.get_dimension_required(UPPER(p_dim_col));
38
39 IF (dim_required = 'Y') THEN
40 dim_str := ',
41 decode(p_sec_track_col,
42 ''' || UPPER(p_dim_col) || ''', l2.' || p_dim_col || ',
43 decode(feata.dim_attribute_varchar_member,
44 ''REVENUE'', p_re_template.' || p_dim_col || ',
45 ''EXPENSE'', p_re_template.' || p_dim_col || ',
46 l2.' || p_dim_col || '))';
47
48 g_sel_stmt := g_sel_stmt || dim_str;
49 END IF;
50
51 -- **************************************************
52
53 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
54 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
55 g_api || '.' || fn_name,
56 GCS_UTILITY_PKG.g_module_success || fn_name ||
57 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
58 END IF;
59 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
60 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
65 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
66 g_api || '.' || fn_name,
67 GCS_UTILITY_PKG.g_module_failure || fn_name ||
68 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
69 END IF;
70 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
71 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
72 RAISE;
73 END build_dim_str;
74
75 --
76 -- Procedure
77 -- build_re_stmt
78 -- Notes
79 -- The final g_sel_stmt does NOT have a trailing ',' after the last
80 -- dimension. The string will be used in both select and group by, and
81 -- each usage will end the string properly (either ',' or ';').
82 PROCEDURE build_re_stmt IS
83 fn_name VARCHAR2(30);
84 BEGIN
85 fn_name := 'BUILD_RE_STMT';
86 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
87 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
88 g_api || '.' || fn_name,
89 GCS_UTILITY_PKG.g_module_enter || fn_name ||
90 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
91 END IF;
92 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
93 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
94
95 -- **************************************************
96
97 -- Company_Cost_Center_Org_Id, Intercompany_Id, and Line_Item_Id
98 g_sel_stmt :=
99 ' decode(p_bal_by_org,
100 ''Y'', l2.company_cost_center_org_id,
101 decode(feata.dim_attribute_varchar_member,
102 ''REVENUE'', l_default_org_id,
103 ''EXPENSE'', l_default_org_id,
104 l2.company_cost_center_org_id)),
105 -- RE: use org id only if there is no specified intercompany id
106 decode(feata.dim_attribute_varchar_member,
107 ''REVENUE'', nvl(l_intercompany_id,
108 decode(p_bal_by_org,
109 ''Y'', l2.company_cost_center_org_id,
110 l_default_org_id)),
111 ''EXPENSE'', nvl(l_intercompany_id,
112 decode(p_bal_by_org,
113 ''Y'', l2.company_cost_center_org_id,
114 l_default_org_id)),
115 l2.intercompany_id),
116 -- line item (cannot be secondary tracking column)
117 decode(feata.dim_attribute_varchar_member,
118 ''REVENUE'', p_re_template.line_item_id,
119 ''EXPENSE'', p_re_template.line_item_id,
120 l2.line_item_id)';
121
122 -- below must be in the same order as GCS_DYNAMIC_UTIL_PKG.Build_Comma_List
123 build_dim_str('financial_elem_id');
124 build_dim_str('product_id');
125 build_dim_str('natural_account_id');
126 build_dim_str('channel_id');
127 build_dim_str('project_id');
128 build_dim_str('customer_id');
129 build_dim_str('task_id');
130 build_dim_str('user_dim1_id');
131 build_dim_str('user_dim2_id');
132 build_dim_str('user_dim3_id');
133 build_dim_str('user_dim4_id');
134 build_dim_str('user_dim5_id');
135 build_dim_str('user_dim6_id');
136 build_dim_str('user_dim7_id');
137 build_dim_str('user_dim8_id');
138 build_dim_str('user_dim9_id');
139 build_dim_str('user_dim10_id');
140
141 -- **************************************************
142
143 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
144 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
145 g_api || '.' || fn_name,
146 GCS_UTILITY_PKG.g_module_success || fn_name ||
147 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
148 END IF;
149 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
150 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
155 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
156 g_api || '.' || fn_name,
157 GCS_UTILITY_PKG.g_module_failure || fn_name ||
158 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
159 END IF;
160 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
161 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
162 RAISE;
163 END build_re_stmt;
164
165
166 --
167 -- PUBLIC FUNCTIONS
168 --
169
170 PROCEDURE create_package IS
171 fn_name VARCHAR2(30);
172
173 -- control each line to < 80 chars and put in <= 50 lines each time
174 body_block VARCHAR2(20000);
175 body_len NUMBER;
176
177 curr_pos NUMBER;
178 line_num NUMBER := 1;
179
180 comp_err VARCHAR2(10);
181 BEGIN
182 fn_name := 'CREATE_PACKAGE';
183 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
184 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
185 g_api || '.' || fn_name,
186 GCS_UTILITY_PKG.g_module_enter || fn_name ||
187 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
188 END IF;
189 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
190 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
191
192 -- initialization
193 GCS_UTILITY_PKG.init_dimension_info;
194 build_re_stmt;
195
196 body_block :=
197 'CREATE OR REPLACE PACKAGE BODY GCS_PERIOD_INIT_DYNAMIC_PKG AS
198 /* $Header: gcspinbb.pls 120.5 2006/09/08 00:30:34 skamdar noship $ */
199
200 --
201 -- PRIVATE GLOBAL VARIABLES
202 --
203 g_api VARCHAR2(40) := ''gcs.plsql.GCS_PERIOD_INIT_DYNAMIC_PKG'';
204 g_li_eat_attr_id NUMBER := GCS_UTILITY_PKG.g_dimension_attr_info
205 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id;
206 g_li_eat_ver_id NUMBER := GCS_UTILITY_PKG.g_dimension_attr_info
207 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id;
208 g_acct_type_attr_id NUMBER := GCS_UTILITY_PKG.g_dimension_attr_info
209 (''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id;
210 g_acct_type_ver_id NUMBER := GCS_UTILITY_PKG.g_dimension_attr_info
211 (''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id;
212
213
214 --
215 -- PUBLIC FUNCTIONS
216 --
217
218 PROCEDURE insert_entry_lines(
219 p_run_name VARCHAR2,
220 p_hierarchy_id NUMBER,
221 p_entity_id NUMBER,
222 p_currency_code VARCHAR2,
223 p_bal_by_org VARCHAR2,
224 p_sec_track_col VARCHAR2,
225 p_is_elim_entity VARCHAR2,
226 p_cons_entity_id NUMBER,
227 p_re_template GCS_TEMPLATES_PKG.TemplateRecord,
228 p_cross_year_flag VARCHAR2,
229 p_category_code VARCHAR2,
230 p_init_entry_id NUMBER,
231 p_init_xlate_entry_id NUMBER,
232 p_init_stat_entry_id NUMBER,
233 p_recur_entry_id NUMBER,
234 p_recur_xlate_entry_id NUMBER,
235 p_recur_stat_entry_id NUMBER,
236 --Bugfix 5449718: Added the calendar period year and net to re flag as parameters
237 p_cal_period_year NUMBER,
238 p_net_to_re_flag VARCHAR2)
239 IS
240 fn_name VARCHAR2(30) := ''INSERT_ENTRY_LINES'';
241 l_default_org_id NUMBER;
242 l_intercompany_id NUMBER;
243
244 --Bugfix 5449718: Add two lists to store recurring entries. List 1 will store recurring entries where the RE has not yet rolled forward.
245 --List 2 will store entries where RE needs to be rolled forward
246 TYPE r_entry_list IS RECORD (entry_id NUMBER(15),
247 year_to_apply_RE NUMBER(15),
248 currency_code VARCHAR2(30),
249 period_init_entry VARCHAR2(1),
250 diff_in_cal_periods NUMBER );
251 TYPE t_entry_list IS TABLE OF r_entry_list;
252
253 l_entry_id_list t_entry_list;
254 l_recur_entry_id_list t_entry_list := t_entry_list();
255 l_num_recur_entry_id NUMBER(15) := 0;
256 l_entry_list DBMS_SQL.NUMBER_TABLE;
257 l_currency_code_list DBMS_SQL.VARCHAR2_TABLE;
258
259
260 BEGIN
261 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
262 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
263 g_api || ''.'' || fn_name,
264 GCS_UTILITY_PKG.g_module_enter || fn_name ||
265 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
266 END IF;
267
268 --Bugfix 5449718: Adding information on key parameters
269 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
270 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.''|| fn_name, ''<<<<<Begin List of Parameters>>>>>>'');
271 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.''|| fn_name, ''Run Name : '' || p_run_name);
272 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.''|| fn_name, ''Consolidation Entity: '' || p_cons_entity_id);
273 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.''|| fn_name, ''Category Code : '' || p_category_code);
274 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.''|| fn_name, ''Cross Year Flag : '' || p_cross_year_flag);
275 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.''|| fn_name, ''Net to RE Flag : '' || p_net_to_re_flag);
276 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || ''.''|| fn_name, ''<<<<<End List of Parameters>>>>>>'');
277 END IF;
278
279 IF ( p_is_elim_entity = ''Y'' ) THEN
280
281 --Bugfix 5449718: Retrieving additional information year_to_apply_re, currency_code
282 --If cross year flag = N then do not select any entries where year to apply RE >= current year
283
284 IF ( p_cross_year_flag = ''N'') THEN
285 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
286 geh.year_to_apply_re,
287 geh.currency_code,
288 ''N'',
289 0
290 BULK COLLECT INTO
291 l_entry_id_list
292 FROM gcs_cons_eng_run_dtls gcerd,
293 gcs_entry_headers geh
294 WHERE gcerd.run_name = p_run_name
295 AND gcerd.consolidation_entity_id = p_cons_entity_id
296 AND gcerd.child_entity_id IS NOT NULL
297 AND gcerd.category_code = p_category_code
298 AND gcerd.entry_id = geh.entry_id
299 AND geh.period_init_entry_flag = ''N''
300 AND p_cal_period_year < NVL(geh.year_to_apply_re, p_cal_period_year+1);
301 ELSIF (p_net_to_re_flag = ''N'') THEN
302 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
303 geh.year_to_apply_re,
304 geh.currency_code,
305 ''N'',
306 0
307 BULK COLLECT INTO
308 l_entry_id_list
309 FROM gcs_cons_eng_run_dtls gcerd,
310 gcs_entry_headers geh
311 WHERE gcerd.run_name = p_run_name
312 AND gcerd.consolidation_entity_id = p_cons_entity_id
313 AND gcerd.child_entity_id IS NOT NULL
314 AND gcerd.category_code = p_category_code
315 AND gcerd.entry_id = geh.entry_id
316 AND geh.period_init_entry_flag = ''N'';
317 ELSE
318 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
319 geh.year_to_apply_re,
320 geh.currency_code,
321 geh.period_init_entry_flag,
322 NVL(geh.end_cal_period_id, geh.start_cal_period_id) - geh.start_cal_period_id
323 BULK COLLECT INTO
324 l_entry_id_list
325 FROM gcs_cons_eng_run_dtls gcerd,
326 gcs_entry_headers geh
327 WHERE gcerd.run_name = p_run_name
328 AND gcerd.consolidation_entity_id = p_cons_entity_id
329 AND gcerd.child_entity_id IS NOT NULL
330 AND gcerd.category_code = p_category_code
331 AND gcerd.entry_id = geh.entry_id;
332 END IF;
333
334 ELSE
335
336 IF ( p_cross_year_flag = ''N'') THEN
337 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
338 geh.year_to_apply_re,
339 geh.currency_code,
340 ''N'',
341 0
342 BULK COLLECT INTO
343 l_entry_id_list
344 FROM gcs_cons_eng_run_dtls gcerd,
345 gcs_entry_headers geh
346 WHERE gcerd.run_name = p_run_name
347 AND gcerd.consolidation_entity_id = p_cons_entity_id
348 AND gcerd.child_entity_id = p_entity_id
349 AND gcerd.category_code = p_category_code
350 AND gcerd.entry_id = geh.entry_id
351 AND geh.period_init_entry_flag = ''N''
352 AND p_cal_period_year < NVL(geh.year_to_apply_re, p_cal_period_year+1);
353 ELSIF (p_net_to_re_flag = ''N'') THEN
354 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
355 geh.year_to_apply_re,
356 geh.currency_code,
357 ''N'',
358 0
359 BULK COLLECT INTO
360 l_entry_id_list
361 FROM gcs_cons_eng_run_dtls gcerd,
362 gcs_entry_headers geh
363 WHERE gcerd.run_name = p_run_name
364 AND gcerd.consolidation_entity_id = p_cons_entity_id
365 AND gcerd.child_entity_id = p_entity_id
366 AND gcerd.category_code = p_category_code
367 AND gcerd.entry_id = geh.entry_id
368 AND geh.period_init_entry_flag = ''N'';
369 ELSE
370 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
371 geh.year_to_apply_re,
372 geh.currency_code,
373 geh.period_init_entry_flag,
374 NVL(geh.end_cal_period_id, geh.start_cal_period_id) - geh.start_cal_period_id
375 BULK COLLECT INTO
376 l_entry_id_list
377 FROM gcs_cons_eng_run_dtls gcerd,
378 gcs_entry_headers geh
379 WHERE gcerd.run_name = p_run_name
380 AND gcerd.consolidation_entity_id = p_cons_entity_id
381 AND gcerd.child_entity_id = p_entity_id
382 AND gcerd.category_code = p_category_code
383 AND gcerd.entry_id = geh.entry_id;
384 END IF;
385
386 END IF;
387
388 --Bugfix 5449718: Do not need to copy entries to a single variable anymore.
389 /* FOR i IN l_stat_entry_id_list.FIRST.. l_stat_entry_id_list.LAST LOOP
390 l_entry_id_list(l_entry_id_list.LAST + i) := l_stat_entry_id_list(i);
391 END LOOP;
392 */
393 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
394 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.''|| fn_name, ''Number of entries to process: '' || l_entry_id_list.COUNT);
395 END IF;
396
397 --Bufix 5449718: Go to the end of the procedure as a safe net if there are no entries to process
398 IF (l_entry_id_list.COUNT = 0) THEN
399 GOTO norowstoprocess;
400 END IF;
401
402 --Bugfix 5449718: Must reclassify entries that are crossing the year end boundary to make sure the appropriate lines are applied
403 IF (p_net_to_re_flag = ''Y'') THEN
404 FOR i IN l_entry_id_list.FIRST..l_entry_id_list.LAST LOOP
405 IF (l_entry_id_list(i).period_init_entry = ''Y'' AND
406 l_entry_id_list(i).diff_in_cal_periods = 0) THEN
407 l_entry_id_list.DELETE(i);
408 ELSIF (l_entry_id_list(i).year_to_apply_re IS NOT NULL) THEN
409 l_num_recur_entry_id := l_num_recur_entry_id + 1;
410 l_recur_entry_id_list.EXTEND(1);
411 l_recur_entry_id_list(l_num_recur_entry_id) := l_entry_id_list(i);
412 l_entry_id_list.DELETE(i);
413 END IF;
414 END LOOP;
415 END IF;
416
417 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
418 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Number of single year entries: '' || l_entry_id_list.COUNT);
419 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Recurring Entries Where Year to Apply RE is not null: '' || l_recur_entry_id_list.COUNT);
420 END IF;
421
422 IF (p_cross_year_flag = ''N'') THEN
423
424 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
425 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Processing Within Year'');
426 END IF;
427
428 IF (l_entry_id_list.COUNT > 0) THEN
429 --Cannot reference tables of records in BULK statements so initializing individual tables
430 l_entry_list.DELETE;
431 l_currency_code_list.DELETE;
432
433 FOR i IN l_entry_id_list.FIRST..l_entry_id_list.LAST LOOP
434 l_entry_list(i) := l_entry_id_list(i).entry_id;
435 l_currency_code_list(i) := l_entry_id_list(i).currency_code;
436 END LOOP;
437
438 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
439 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Beginning Collection of Single Year Entries'');
440 END IF;
441
442 --Bugfix 5449718: Need to insert into GCS_ENTRY_LINES_GT to avoid unique constraint errors
443 FORALL i IN l_entry_id_list.FIRST.. l_entry_id_list.LAST
444 INSERT INTO GCS_ENTRY_LINES_GT
445 (entry_id,
446 description,
447 company_cost_center_org_id,
448 intercompany_id,
449 line_item_id,
450 ';
451
452 curr_pos := 1;
453 body_len := LENGTH(body_block);
454 WHILE curr_pos <= body_len LOOP
455 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
456 line_num);
457 curr_pos := curr_pos + g_line_size;
458 line_num := line_num + 1;
459 END LOOP;
460
461 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
462 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
463
464 body_block :=
465 ' xtd_balance_e,
466 ytd_balance_e,
467 ptd_debit_balance_e,
468 ptd_credit_balance_e,
469 ytd_debit_balance_e,
470 ytd_credit_balance_e)
471 SELECT
472 --Bugfix 5449718: Remove the references to the init_xlate_entry_id, and removed all group by calcs as this is happening on a line by line basis.
473 --Also remove join to gcs_entry_headers as all of the information is available in the PL/SQL collection
474 decode(l_currency_code_list(i), ''STAT'', p_init_stat_entry_id,
475 p_init_entry_id),
476 decode(feata.dim_attribute_varchar_member,
477 ''REVENUE'', ''PROFIT_LOSS'',
478 ''EXPENSE'', ''PROFIT_LOSS'',
479 ''BALANCE_SHEET''),
480 l2.company_cost_center_org_id,
481 l2.intercompany_id,
482 l2.line_item_id,
483 ';
484 curr_pos := 1;
485 body_len := LENGTH(body_block);
486 WHILE curr_pos <= body_len LOOP
487 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
488 line_num);
489 curr_pos := curr_pos + g_line_size;
490 line_num := line_num + 1;
491 END LOOP;
492
493 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
494 ' l2.', GCS_UTILITY_PKG.g_nl, '', line_num);
495
496 body_block :=
497 ' decode(feata.dim_attribute_varchar_member,
498 ''REVENUE'', NVL(ytd_credit_balance_e,0) - NVL(ytd_debit_balance_e,0),
499 ''EXPENSE'', NVL(ytd_credit_balance_e,0) - NVL(ytd_debit_balance_e,0),
500 0),
501 0,
502 -1*(ytd_debit_balance_e),
503 -1*(ytd_credit_balance_e),
504 0,
505 0
506 FROM
507 GCS_ENTRY_LINES l2,
508 FEM_LN_ITEMS_ATTR lia,
509 FEM_EXT_ACCT_TYPES_ATTR feata
510 WHERE l2.entry_id = l_entry_list(i)
511 AND lia.attribute_id = g_li_eat_attr_id
512 AND lia.version_id = g_li_eat_ver_id
513 AND lia.line_item_id = l2.line_item_id
514 AND feata.attribute_id = g_acct_type_attr_id
515 AND feata.version_id = g_acct_type_ver_id
516 AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
517
518 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
519 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Completed Collection of Single Year Entries'');
520 END IF;
521
522 END IF;
523
524 --Bugfix 5449718: Need to insert into GCS_ENTRY_LINES_GT to avoid unique constraint errors. Handle entries where only balance sheet lines must be applied
525 IF (l_recur_entry_id_list.COUNT > 0) THEN
526 --Cannot reference tables of records in BULK statements so initializing individual tables
527
528 l_entry_list.DELETE;
529 l_currency_code_list.DELETE;
530
531 FOR i IN l_recur_entry_id_list.FIRST..l_recur_entry_id_list.LAST LOOP
532 l_entry_list(i) := l_recur_entry_id_list(i).entry_id;
533 l_currency_code_list(i) := l_recur_entry_id_list(i).currency_code;
534 END LOOP;
535
536 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
537 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Beginning Collection of Recurring Entries'');
538 END IF;
539
540 FORALL i IN l_recur_entry_id_list.FIRST.. l_recur_entry_id_list.LAST
541 INSERT INTO GCS_ENTRY_LINES_GT l1
542 (entry_id,
543 description,
544 company_cost_center_org_id,
545 intercompany_id,
546 line_item_id,
547 ';
548
549 curr_pos := 1;
550 body_len := LENGTH(body_block);
551 WHILE curr_pos <= body_len LOOP
552 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
553 line_num);
554 curr_pos := curr_pos + g_line_size;
555 line_num := line_num + 1;
556 END LOOP;
557
558 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
559 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
560
561 body_block :=
562 ' xtd_balance_e,
563 ytd_balance_e,
564 ptd_debit_balance_e,
565 ptd_credit_balance_e,
566 ytd_debit_balance_e,
567 ytd_credit_balance_e)
568 SELECT
569 --Bugfix 5449718: Remove the references to the init_xlate_entry_id, and removed all group by calcs as this is happening on a line by line basis.
570 --Also remove join to gcs_entry_headers as all of the information is available in the PL/SQL collection
571 --Join to line type is no longer required as all rows for recurring entries have the line type code populated
572 decode(l_currency_code_list(i), ''STAT'', p_init_stat_entry_id,
573 p_init_entry_id),
574 l2.line_type_code,
575 l2.company_cost_center_org_id,
576 l2.intercompany_id,
577 l2.line_item_id,
578 ';
579 curr_pos := 1;
580 body_len := LENGTH(body_block);
581 WHILE curr_pos <= body_len LOOP
582 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
583 line_num);
584 curr_pos := curr_pos + g_line_size;
585 line_num := line_num + 1;
586 END LOOP;
587
588 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
589 ' l2.', GCS_UTILITY_PKG.g_nl, '', line_num);
590
591 body_block :=
592 ' --XTD Balance should be determined by the line type code
593 DECODE(l2.line_type_code, ''PROFIT_LOSS'', NVL(ytd_credit_balance_e, 0) - NVL(ytd_debit_balance_e, 0),
594 0),
595 0,
596 -1*(ytd_debit_balance_e),
597 -1*(ytd_credit_balance_e),
598 0,
599 0
600 FROM
601 GCS_ENTRY_LINES l2
602 WHERE l2.entry_id = l_entry_list(i)
603 AND l2.line_type_code IN (''PROFIT_LOSS'', ''BALANCE_SHEET'');
604
605 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
606 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Completed Collection of Recurring Entries'');
607 END IF;
608
609 END IF;
610
611 --Bugfix 5449718: Needed to add a condition if the Net to RE Flag is N versus Y when crossing the year-end boundary for performance purposes
612 ELSIF (p_net_to_re_flag = ''N'') THEN
613
614 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
615 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Processing for Next Year with Net to RE set to N'');
616 END IF;
617
618 -- find default org id for RE, in case bal_by_org is ''N''
619 l_default_org_id := GCS_UTILITY_PKG.get_org_id(p_entity_id,
620 p_hierarchy_id);
621
622 -- find default intercompany id for RE if specified
623 SELECT specific_intercompany_id
624 INTO l_intercompany_id
625 FROM GCS_CATEGORIES_B
626 WHERE category_code = ''INTRACOMPANY'';
627
628 IF (l_entry_id_list.COUNT > 0) THEN
629 --Cannot reference tables of records in BULK statements so initializing individual tables
630 l_entry_list.DELETE;
631 l_currency_code_list.DELETE;
632
633 FOR i IN l_entry_id_list.FIRST..l_entry_id_list.LAST LOOP
634 l_entry_list(i) := l_entry_id_list(i).entry_id;
635 l_currency_code_list(i) := l_entry_id_list(i).currency_code;
636 END LOOP;
637
638 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
639 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Beginning Collection of Single Year Entries'');
640 END IF;
641
642 FORALL i IN l_entry_id_list.FIRST.. l_entry_id_list.LAST
643 INSERT INTO GCS_ENTRY_LINES_GT l1
644 (entry_id,
645 description,
646 company_cost_center_org_id,
647 intercompany_id,
648 line_item_id,
649 ';
650
651 curr_pos := 1;
652 body_len := LENGTH(body_block);
653 WHILE curr_pos <= body_len LOOP
654 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
655 line_num);
656 curr_pos := curr_pos + g_line_size;
657 line_num := line_num + 1;
658 END LOOP;
659
660 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
661 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
662
663 body_block :=
664 ' xtd_balance_e,
665 ytd_balance_e,
666 ptd_debit_balance_e,
667 ptd_credit_balance_e,
668 ytd_debit_balance_e,
669 ytd_credit_balance_e)
670 SELECT
671 --Bugfix 5449718: No longer need the target entries
672 decode(l_currency_code_list(i), ''STAT'', p_init_stat_entry_id,
673 p_init_entry_id),
674 ''BALANCE_SHEET'',
675 ';
676
677 curr_pos := 1;
678 body_len := LENGTH(body_block);
679 WHILE curr_pos <= body_len LOOP
680 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
681 line_num);
682 curr_pos := curr_pos + g_line_size;
683 line_num := line_num + 1;
684 END LOOP;
685
686 -- g_sel_stmt
687 curr_pos := 1;
688 body_len := LENGTH(g_sel_stmt);
689 WHILE curr_pos <= body_len LOOP
690 ad_ddl.build_statement(SUBSTR(g_sel_stmt, curr_pos, g_line_size),
691 line_num);
692 curr_pos := curr_pos + g_line_size;
693 line_num := line_num + 1;
694 END LOOP;
695
696 body_block := ',
697 --Bugfix 5449718: If net to re flag is N then all balances except ptd debit and ptd credit will be zero
698 0,
699 0,
700 -1*(l2.ytd_debit_balance_e),
701 -1*(l2.ytd_credit_balance_e),
702 0,
703 0
704 FROM
705 --Bugfix 5449718: Remove source, target entry, and category joins.
706 GCS_ENTRY_LINES l2,
707 FEM_LN_ITEMS_ATTR lia,
708 FEM_EXT_ACCT_TYPES_ATTR feata
709 WHERE
710 l2.entry_id = l_entry_list(i)
711 AND lia.attribute_id = g_li_eat_attr_id
712 AND lia.version_id = g_li_eat_ver_id
713 AND lia.line_item_id = l2.line_item_id
714 AND feata.attribute_id = g_acct_type_attr_id
715 AND feata.version_id = g_acct_type_ver_id
716 AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
717 -- Bugfix 5449718: Group by is no longer necessary
718
719 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
720 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Completed Collection of Single Year Entries'');
721 END IF;
722
723 END IF;
724 ';
725
726 curr_pos := 1;
727 body_len := LENGTH(body_block);
728 WHILE curr_pos <= body_len LOOP
729 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
730 line_num);
731 curr_pos := curr_pos + g_line_size;
732 line_num := line_num + 1;
733 END LOOP;
734
735 body_block :=
736 '
737 --Bugfix 5449718: Needed to add a condition if the Net to RE Flag is N versus Y when crossing the year-end boundary for performance purposes
738 ELSIF (p_net_to_re_flag = ''Y'') THEN
739
740 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
741 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Processing for Next Year with Net to RE set to Y'');
742 END IF;
743
744 -- find default org id for RE, in case bal_by_org is ''N''
745 l_default_org_id := GCS_UTILITY_PKG.get_org_id(p_entity_id,
746 p_hierarchy_id);
747
748 -- find default intercompany id for RE if specified
749 SELECT specific_intercompany_id
750 INTO l_intercompany_id
751 FROM GCS_CATEGORIES_B
752 WHERE category_code = ''INTRACOMPANY'';
753
754 l_entry_list.DELETE;
755 l_currency_code_list.DELETE;
756
757 IF (l_entry_id_list.COUNT > 0) THEN
758 --Cannot reference tables of records in BULK statements so initializing individual tables
759 FOR i IN l_entry_id_list.FIRST..l_entry_id_list.LAST LOOP
760 l_entry_list(i) := l_entry_id_list(i).entry_id;
761 l_currency_code_list(i) := l_entry_id_list(i).currency_code;
762 END LOOP;
763
764 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
765 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Beginning Collection of Single Year Entries'');
766 END IF;
767
768 FORALL i IN l_entry_id_list.FIRST.. l_entry_id_list.LAST
769 INSERT INTO GCS_ENTRY_LINES_GT l1
770 (entry_id,
771 description,
772 company_cost_center_org_id,
773 intercompany_id,
774 line_item_id,
775 ';
776
777 curr_pos := 1;
778 body_len := LENGTH(body_block);
779 WHILE curr_pos <= body_len LOOP
780 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
781 line_num);
782 curr_pos := curr_pos + g_line_size;
783 line_num := line_num + 1;
784 END LOOP;
785
786 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
787 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
788
789 body_block :=
790 ' xtd_balance_e,
791 ytd_balance_e,
792 ptd_debit_balance_e,
793 ptd_credit_balance_e,
794 ytd_debit_balance_e,
795 ytd_credit_balance_e)
796 SELECT
797 --Bugfix 5449718: No longer need the target entries
798 decode(l_currency_code_list(i), ''STAT'', p_recur_stat_entry_id,
799 p_recur_entry_id),
800 ''BALANCE_SHEET'',
801 ';
802
803 curr_pos := 1;
804 body_len := LENGTH(body_block);
805 WHILE curr_pos <= body_len LOOP
806 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
807 line_num);
808 curr_pos := curr_pos + g_line_size;
809 line_num := line_num + 1;
810 END LOOP;
811
812 -- g_sel_stmt
813 curr_pos := 1;
814 body_len := LENGTH(g_sel_stmt);
815 WHILE curr_pos <= body_len LOOP
816 ad_ddl.build_statement(SUBSTR(g_sel_stmt, curr_pos, g_line_size),
817 line_num);
818 curr_pos := curr_pos + g_line_size;
819 line_num := line_num + 1;
820 END LOOP;
821
822 body_block := ',
823 --Bugfix 5449718: If net to re flag is Y then all balances except ptd debit and ptd credit will be zero
824 ytd_balance_e,
825 ytd_balance_e,
826 0,
827 0,
828 ytd_debit_balance_e,
829 ytd_credit_balance_e
830 FROM
831 --Bugfix 5449718: Remove source, target entry, and category joins. Add join to fem_ext_acct_types_attr
832 GCS_ENTRY_LINES l2,
833 FEM_LN_ITEMS_ATTR lia,
834 FEM_EXT_ACCT_TYPES_ATTR feata
835 WHERE
836 l2.entry_id = l_entry_list(i)
837 AND lia.attribute_id = g_li_eat_attr_id
838 AND lia.version_id = g_li_eat_ver_id
839 AND lia.line_item_id = l2.line_item_id
840 AND feata.attribute_id = g_acct_type_attr_id
841 AND feata.version_id = g_acct_type_ver_id
842 AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
843 -- Bugfix 5449718: Group by is no longer necessary
844
845 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
846 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Completed Collection of Single Year'');
847 END IF;
848
849 ';
850
851 curr_pos := 1;
852 body_len := LENGTH(body_block);
853 WHILE curr_pos <= body_len LOOP
854 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
855 line_num);
856 curr_pos := curr_pos + g_line_size;
857 line_num := line_num + 1;
858 END LOOP;
859
860 body_block :=
861 '
862 END IF;
863
864
865 l_entry_list.DELETE;
866 l_currency_code_list.DELETE;
867
868 IF (l_recur_entry_id_list.COUNT > 0) THEN
869 --Cannot reference tables of records in BULK statements so initializing individual tables
870 FOR i IN l_recur_entry_id_list.FIRST..l_recur_entry_id_list.LAST LOOP
871 l_entry_list(i) := l_recur_entry_id_list(i).entry_id;
872 l_currency_code_list(i) := l_recur_entry_id_list(i).currency_code;
873 END LOOP;
874
875 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
876 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Beginning Collection of Recurring Entries'');
877 END IF;
878
879 FORALL i IN l_recur_entry_id_list.FIRST.. l_recur_entry_id_list.LAST
880 INSERT INTO GCS_ENTRY_LINES_GT l1
881 (entry_id,
882 description,
883 company_cost_center_org_id,
884 intercompany_id,
885 line_item_id,
886 ';
887
888 curr_pos := 1;
889 body_len := LENGTH(body_block);
890 WHILE curr_pos <= body_len LOOP
891 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
892 line_num);
893 curr_pos := curr_pos + g_line_size;
894 line_num := line_num + 1;
895 END LOOP;
896
897 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
898 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
899
900 body_block :=
901 ' xtd_balance_e,
902 ytd_balance_e,
903 ptd_debit_balance_e,
904 ptd_credit_balance_e,
905 ytd_debit_balance_e,
906 ytd_credit_balance_e)
907 SELECT
908 --Bugfix 5449718: No longer need the target entries
909 decode(l_currency_code_list(i), ''STAT'', p_recur_stat_entry_id,
910 p_recur_entry_id),
911 ''BALANCE_SHEET'',
912 l2.company_cost_center_org_id,
913 l2.intercompany_id,
914 l2.line_item_id,
915 ';
916
917 curr_pos := 1;
918 body_len := LENGTH(body_block);
919 WHILE curr_pos <= body_len LOOP
920 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
921 line_num);
922 curr_pos := curr_pos + g_line_size;
923 line_num := line_num + 1;
924 END LOOP;
925
926 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
927 ' l2.', GCS_UTILITY_PKG.g_nl, '', line_num);
928
929 body_block := '
930 --Bugfix 5449718: If net to re flag is N then all balances except ptd debit and ptd credit will be zero
931 0,
932 0,
933 -1*ytd_debit_balance_e,
934 -1*ytd_credit_balance_e,
935 0,
936 0
937 FROM
938 --Bugfix 5449718: Remove source, target entry, and category joins.
939 GCS_ENTRY_LINES l2
940 WHERE
941 l2.entry_id = l_entry_list(i)
942 AND l2.line_type_code IN (''CALCULATED'', ''BALANCE_SHEET'');
943 -- Bugfix 5449718: Group by is no longer necessary
944
945 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
946 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || ''.'' || fn_name, ''Completed Collection of Recurring Entries'');
947 END IF;
948 ';
949
950 curr_pos := 1;
951 body_len := LENGTH(body_block);
952 WHILE curr_pos <= body_len LOOP
953 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
954 line_num);
955 curr_pos := curr_pos + g_line_size;
956 line_num := line_num + 1;
957 END LOOP;
958
959 body_block :=
960 '
961 END IF;
962 END IF; --if..then..cross year flag
963
964 --Bugfix 5449718: Move data from gcs_entry_lines_gt into gcs_entry_lines
965 INSERT INTO gcs_entry_lines
966 (entry_id,
967 line_type_code,
968 company_cost_center_org_id,
969 intercompany_id,
970 line_item_id,
971 ';
972
973 curr_pos := 1;
974 body_len := LENGTH(body_block);
975 WHILE curr_pos <= body_len LOOP
976 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
977 line_num);
978 curr_pos := curr_pos + g_line_size;
979 line_num := line_num + 1;
980 END LOOP;
981
982 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
983 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
984
985 body_block :=
986 '
987 xtd_balance_e,
988 ytd_balance_e,
989 ptd_debit_balance_e,
990 ptd_credit_balance_e,
991 ytd_debit_balance_e,
992 ytd_credit_balance_e,
993 creation_date,
994 created_by,
995 last_update_date,
996 last_updated_by,
997 last_update_login)
998 SELECT
999 entry_id,
1000 MIN(description),
1001 company_cost_center_org_id,
1002 intercompany_id,
1003 line_item_id,
1004 ';
1005
1006 curr_pos := 1;
1007 body_len := LENGTH(body_block);
1008 WHILE curr_pos <= body_len LOOP
1009 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1010 line_num);
1011 curr_pos := curr_pos + g_line_size;
1012 line_num := line_num + 1;
1013 END LOOP;
1014
1015 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
1016 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
1017
1018 body_block :=
1019 '
1020 SUM(NVL(xtd_balance_e,0)),
1021 SUM(NVL(ytd_balance_e,0)),
1022 SUM(NVL(ptd_debit_balance_e,0)),
1023 SUM(NVL(ptd_credit_balance_e,0)),
1024 SUM(NVL(ytd_debit_balance_e,0)),
1025 SUM(NVL(ytd_credit_balance_e,0)),
1026 sysdate,
1027 GCS_PERIOD_INIT_PKG.g_fnd_user_id,
1028 sysdate,
1029 GCS_PERIOD_INIT_PKG.g_fnd_user_id,
1030 GCS_PERIOD_INIT_PKG.g_fnd_login_id
1031 FROM gcs_entry_lines_gt
1032 GROUP BY entry_id,
1033 company_cost_center_org_id,
1034 line_item_id,
1035
1036 ';
1037
1038 curr_pos := 1;
1039 body_len := LENGTH(body_block);
1040 WHILE curr_pos <= body_len LOOP
1041 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1042 line_num);
1043 curr_pos := curr_pos + g_line_size;
1044 line_num := line_num + 1;
1045 END LOOP;
1046
1047 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
1048 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
1049
1050 body_block :=
1051 ' intercompany_id;
1052
1053 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1054 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1055 g_api || ''.'' || fn_name,
1056 ''Inserted '' || to_char(SQL%ROWCOUNT) || '' row(s)'');
1057 END IF;
1058
1059 <<norowstoprocess>>
1060
1061 COMMIT;
1062
1063 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1064 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1065 g_api || ''.'' || fn_name,
1066 GCS_UTILITY_PKG.g_module_success || fn_name ||
1067 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1068 END IF;
1069 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
1070 -- fn_name || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1071
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1075 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1076 g_api || ''.'' || fn_name,
1077 GCS_UTILITY_PKG.g_module_failure || fn_name ||
1078 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1079 END IF;
1080 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1081 fn_name || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1082 RAISE;
1083 END insert_entry_lines;
1084
1085 END GCS_PERIOD_INIT_DYNAMIC_PKG;';
1086
1087 curr_pos := 1;
1088 body_len := LENGTH(body_block);
1089 WHILE curr_pos <= body_len LOOP
1090 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
1091 line_num);
1092 curr_pos := curr_pos + g_line_size;
1093 line_num := line_num + 1;
1094 END LOOP;
1095
1096 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
1097 'GCS', 'GCS_PERIOD_INIT_DYNAMIC_PKG',
1098 1, line_num - 1, 'FALSE', comp_err);
1099
1100 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1101 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1102 g_api || '.' || fn_name,
1103 GCS_UTILITY_PKG.g_module_success || fn_name ||
1104 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1105 END IF;
1106 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
1107 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1112 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1113 g_api || '.' || fn_name,
1114 SUBSTR(SQLERRM, 1, 4000));
1115 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1116 g_api || '.' || fn_name,
1117 GCS_UTILITY_PKG.g_module_failure || fn_name ||
1118 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1119 END IF;
1120 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1121 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1122 END create_package;
1123
1124
1125 END GCS_PERIOD_INIT_DYN_BUILD_PKG;