[Home] [Help]
PACKAGE BODY: APPS.GCS_AGGREGATION_DYN_BUILD_PKG
Source
1 PACKAGE BODY GCS_AGGREGATION_DYN_BUILD_PKG AS
2 /* $Header: gcsaggbb.pls 120.3 2006/03/06 23:05:29 yingliu noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 g_api VARCHAR2(40) := 'gcs.plsql.GCS_AGGREGATION_DYN_BUILD_PKG';
8 g_line_size NUMBER := 250;
9
10 g_common_str VARCHAR2(6500);
11 g_subqry_sel VARCHAR2(1000);
12 g_subqry_grp VARCHAR2(600);
13
14 --
15 -- PRIVATE FUNCTIONS
16 --
17
18 --
19 -- Procedure
20 -- build_dim_match_str
21 -- Notes
22 -- Build nested DECODE to match the dimensions with the given template.
23 -- =============================================================
24 -- decode(fb.<dimA>, <template alias>.<dimA>,
25 -- decode(fb.<dimB>, <template_alias>.<dimB>,
26 -- ...
27 -- 'Y'), ... 'N'),
28 -- =============================================================
29 FUNCTION build_dim_match_str(p_template_alias VARCHAR2) RETURN VARCHAR2 IS
30 fn_name VARCHAR2(30) := 'BUILD_DIM_MATCH_STR';
31 l_col_name VARCHAR2(30);
32 l_dim_req VARCHAR2(1);
33 l_num_dims NUMBER;
34
35 l_prefix VARCHAR2(1800);
36 l_suffix VARCHAR2(200);
37 BEGIN
38 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
39 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
40 g_api || '.' || fn_name,
41 GCS_UTILITY_PKG.g_module_enter || fn_name ||
42 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
43 END IF;
44 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
45 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
46
47 -- **************************************************
48
49 l_col_name := GCS_UTILITY_PKG.g_gcs_dimension_info.FIRST;
50 l_num_dims := 0;
51 l_prefix := '';
52 l_suffix := '';
53
54 LOOP
55 EXIT WHEN (l_col_name IS NULL);
56
57 l_dim_req := GCS_UTILITY_PKG.get_dimension_required(l_col_name);
58
59 -- skip certain dimensions, and only process required dimensions
60 IF ( l_dim_req = 'Y'
61 AND l_col_name NOT IN ('COMPANY_COST_CENTER_ORG_ID',
62 'ENTITY_ID',
63 'INTERCOMPANY_ID')) THEN
64 l_num_dims := l_num_dims + 1;
65
66 -- add a level of decode on the dimension
67 l_prefix := l_prefix || lpad(' ', l_num_dims) ||
68 'decode(fb.' || l_col_name || ', ' || p_template_alias ||
69 '.' || l_col_name || ',
70 ';
71
72 IF (l_num_dims = 1) THEN
73 l_suffix := l_suffix || ' ''Y''),';
74 ELSE
75 l_suffix := l_suffix || ' ''N''),';
76 END IF;
77 END IF;
78
79 l_col_name := GCS_UTILITY_PKG.g_gcs_dimension_info.NEXT(l_col_name);
80 END LOOP;
81
82 -- **************************************************
83
84 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
85 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
86 g_api || '.' || fn_name,
87 GCS_UTILITY_PKG.g_module_success || fn_name ||
88 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
89 END IF;
90 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
91 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
92
93 -- **************************************************
94 RETURN (l_prefix || l_suffix);
95 -- **************************************************
96 EXCEPTION
97 WHEN OTHERS THEN
98 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
99 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
100 g_api || '.' || fn_name,
101 GCS_UTILITY_PKG.g_module_failure || fn_name ||
102 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
103 END IF;
104 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
105 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
106 RAISE;
107 END build_dim_match_str;
108
109 --
110 -- Procedure
111 -- build_org_id_str
112 -- Notes
113 -- The select/group by string to be used by org and intercompany id:
114 -- =============================================================
115 -- decode(l_bal_by_org_flag, 'N',
116 -- decode('Y',
117 -- <RE?>, <consolidation entity's org id>,
118 -- <SUSPENSE?>, <consolidation entity's org id>,
119 -- <CTA?>, <consolidation entity's org id>,
120 -- company_cost_center_org_id),
121 -- company_cost_center_org_id),
122 -- =============================================================
123 -- RE and Suspense are the same for the whole hierarchy, therefore were
124 -- retrieved once and compared with the dimensions using nested DECODE.
125 -- CTA is optional and can differ by relationship. The GCT table in the
126 -- statement already takes care of the matching, so simply check if
127 -- any column in GCT is not null will suffice.
128 --
129 PROCEDURE build_org_id_str IS
130 fn_name VARCHAR2(30) := 'BUILD_ORG_ID_STR';
131 BEGIN
132 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
133 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
134 g_api || '.' || fn_name,
135 GCS_UTILITY_PKG.g_module_enter || fn_name ||
136 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
137 END IF;
138 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
139 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
140
141 -- **************************************************
142 -- GCS_UTILITY_PKG.init_dimension_info;
143
144 g_common_str := ' decode(''Y'',
145 -- matching against Retained Earnings Account template
146 ' || build_dim_match_str('l_re_template') || ' l_default_org_id,
147 -- matching against Suspense Account template
148 ' || build_dim_match_str('l_sus_template') || ' l_default_org_id,
149 company_cost_center_org_id),';
150
151 -- **************************************************
152 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
153 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
154 g_api || '.' || fn_name,
155 GCS_UTILITY_PKG.g_module_success || fn_name ||
156 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
157 END IF;
158 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
159 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
164 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
165 g_api || '.' || fn_name,
166 GCS_UTILITY_PKG.g_module_failure || fn_name ||
167 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
168 END IF;
169 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
170 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
171 RAISE;
172 END build_org_id_str;
173
174 --
175 -- Procedure
176 -- build_gct_subqry_clauses
177 -- Purpose
178 -- Build the select/group by strings for the GCT query table.
179 -- Notes
180 -- The CTA_ prefix in user dimension column names are omitted in the
181 -- final alias, so the join can utilize utility procedures.
182 --
183 PROCEDURE build_gct_subqry_clauses IS
184 fn_name VARCHAR2(30) := 'BUILD_GCT_SUBQRY_CLAUSES';
185 l_col_name VARCHAR2(30);
186 l_dim_req VARCHAR2(1);
187 l_ct_prefix VARCHAR2(10);
188 BEGIN
189 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
190 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
191 g_api || '.' || fn_name,
192 GCS_UTILITY_PKG.g_module_enter || fn_name ||
193 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
194 END IF;
195 g_subqry_sel := '';
196 g_subqry_grp := '';
197
198 l_col_name := GCS_UTILITY_PKG.g_gcs_dimension_info.FIRST;
199 LOOP
200 EXIT WHEN (l_col_name IS NULL);
201
202 l_dim_req := GCS_UTILITY_PKG.get_dimension_required(l_col_name);
203
204 -- skip certain dimensions, and only process required dimensions
205 IF ( l_dim_req = 'Y'
206 AND l_col_name NOT IN ('COMPANY_COST_CENTER_ORG_ID',
207 'ENTITY_ID',
208 'INTERCOMPANY_ID',
209 'LINE_ITEM_ID')) THEN
210 -- CTA: user dimensions are prefixed with 'CTA_'
211 IF (SUBSTR(l_col_name, 1, 8) = 'USER_DIM') THEN
212 l_ct_prefix := 'CTA_';
213 ELSE
214 l_ct_prefix := '';
215 END IF;
216
217 -- CTA columns will not have the CTA prefix in the alias
218 g_subqry_sel := g_subqry_sel || ' ' ||
219 l_ct_prefix || l_col_name || ' ' || l_col_name || ',
220 ';
221
222 g_subqry_grp := g_subqry_grp || ' ' ||
223 l_ct_prefix || l_col_name || ',
224 ';
225 END IF;
226
227 l_col_name := GCS_UTILITY_PKG.g_gcs_dimension_info.NEXT(l_col_name);
228 END LOOP;
229
230 -- **************************************************
231
232 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
233 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
234 g_api || '.' || fn_name,
235 GCS_UTILITY_PKG.g_module_success || fn_name ||
236 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
237 END IF;
238 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
239 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
244 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
245 g_api || '.' || fn_name,
246 GCS_UTILITY_PKG.g_module_failure || fn_name ||
247 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
248 END IF;
249 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
250 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
251 RAISE;
252 END build_gct_subqry_clauses;
253
254
255 --
256 -- PUBLIC FUNCTIONS
257 --
258 PROCEDURE create_package IS
259 fn_name VARCHAR2(30) := 'CREATE_PACKAGE';
260
261 -- example: control each line to < 80 chars and put <= 50 lines each time
262 body_block VARCHAR2(4000);
263 body_len NUMBER;
264
265 curr_pos NUMBER;
266 line_num NUMBER := 1;
267 comp_err VARCHAR2(10);
268
269 l_org_dim_str VARCHAR2(6500);
270 l_ic_dim_str VARCHAR2(6500);
271 BEGIN
272 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
273 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
274 g_api || '.' || fn_name,
275 GCS_UTILITY_PKG.g_module_enter || fn_name ||
276 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
277 END IF;
278 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
279 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
280
281 -- prepare building block strings
282 build_org_id_str;
283 build_gct_subqry_clauses;
284
285 l_org_dim_str := ' -- company_cost_center_org_id
286 ' || g_common_str || GCS_UTILITY_PKG.g_nl;
287
288 l_ic_dim_str := ' -- intercompany_id
289 decode(intercompany_id, company_cost_center_org_id,
290 decode(l_intercompany_id, NULL,
291 ' || g_common_str || '
292 intercompany_id), intercompany_id),' || GCS_UTILITY_PKG.g_nl;
293
294 body_block :=
295 'CREATE OR REPLACE PACKAGE BODY GCS_AGGREGATION_DYNAMIC_PKG AS
296 /* $Header: gcsaggbb.pls 120.3 2006/03/06 23:05:29 yingliu noship $ */
297
298 --
299 -- PRIVATE GLOBAL VARIABLES
300 --
301 g_api VARCHAR2(40) := ''gcs.plsql.GCS_AGGREGATION_DYNAMIC_PKG'';
302
303 TYPE t_entity_org_info IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
304 g_entity_org_info T_ENTITY_ORG_INFO;
305
306 --
307 -- PUBLIC FUNCTIONS
308 --
309
310 FUNCTION retrieve_org_id(p_entity_id NUMBER) RETURN NUMBER IS
311 BEGIN
312 RETURN g_entity_org_info(p_entity_id);
313 END retrieve_org_id;
314
315 --
316 -- Procedure
317 -- insert_full_entry_lines
318 -- Notes
319 -- The QRY select table has the following structure:
320 -- <entity id> <CTA template dimensions> <default org id>
321 -- Entity id Child entities of the consolidation entity
322 -- that has any CTAs from the hierarchy under it
323 -- Default org id Default org id for the entity id
324 -- CTA template dimensions All CTAs from the hierarchy under the entity
325 --
326 PROCEDURE insert_full_entry_lines(
327 p_entry_id NUMBER,
328 p_stat_entry_id NUMBER,
329 p_cons_entity_id NUMBER,
330 p_hierarchy_id NUMBER,
331 p_relationship_id NUMBER,
332 p_cal_period_id NUMBER,
333 p_period_end_date DATE,
334 p_currency_code VARCHAR2,
335 p_balance_type_code VARCHAR2,
336 p_dataset_code NUMBER)
337 IS
338 fn_name VARCHAR2(30) := ''INSERT_FULL_ENTRY_LINES'';
339
340 l_bal_by_org_flag VARCHAR2(1);
341 l_default_org_id NUMBER;
342 l_intercompany_id NUMBER;
343
344 l_re_template GCS_TEMPLATES_PKG.TemplateRecord;
345 l_sus_template GCS_TEMPLATES_PKG.TemplateRecord;
346
347 CURSOR get_child_info IS
348 SELECT child_entity_id,
349 gcs_utility_pkg.get_org_id(child_entity_id, hierarchy_id) org_id
350 FROM gcs_cons_relationships
351 WHERE hierarchy_id = p_hierarchy_id
352 AND parent_entity_id = p_cons_entity_id
353 AND actual_ownership_flag = ''Y'';
354
355 BEGIN
356 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
357 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
358 g_api || ''.'' || fn_name,
359 GCS_UTILITY_PKG.g_module_enter || fn_name ||
360 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
361 END IF;
362 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
363 -- fn_name || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
364
365 -- Get information of the hierarchy
366 SELECT balance_by_org_flag
367 INTO l_bal_by_org_flag
368 FROM gcs_hierarchies_b
369 WHERE hierarchy_id = p_hierarchy_id;
370
371 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
372 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
373 g_api || ''.'' || fn_name,
374 ''l_bal_by_org_flag = '' || l_bal_by_org_flag || '' '' ||
375 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
376 END IF;
377
378 -- Balancing by org or by entity
379 IF (l_bal_by_org_flag = ''Y'') THEN -- Balancing by org
380
381 -- Create entry lines
382 -- bug fix 5066467: removed ordered hint
386 ';
383 INSERT /*+ APPEND */ INTO GCS_ENTRY_LINES
384 (entry_id, line_type_code,
385 company_cost_center_org_id, line_item_id, intercompany_id,
387
388 curr_pos := 1;
389 body_len := LENGTH(body_block);
390 WHILE curr_pos <= body_len LOOP
391 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
392 line_num);
393 curr_pos := curr_pos + g_line_size;
394 line_num := line_num + 1;
395 END LOOP;
396
397 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
398 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
399
400 body_block :=
401 ' xtd_balance_e, ytd_balance_e,
402 ptd_debit_balance_e, ptd_credit_balance_e,
403 ytd_debit_balance_e, ytd_credit_balance_e,
404 creation_date, created_by,
405 last_update_date, last_updated_by, last_update_login)
406 SELECT
407 decode(currency_code, ''STAT'', p_stat_entry_id, p_entry_id), null,
408 company_cost_center_org_id, line_item_id, intercompany_id,
409 ';
410
411 curr_pos := 1;
412 body_len := LENGTH(body_block);
413 WHILE curr_pos <= body_len LOOP
414 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
415 line_num);
416 curr_pos := curr_pos + g_line_size;
417 line_num := line_num + 1;
418 END LOOP;
419
420 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
421 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
422
423 body_block :=
424 ' sum(xtd_balance_e), sum(ytd_balance_e),
425 sum(ptd_debit_balance_e), sum(ptd_credit_balance_e),
426 sum(ytd_debit_balance_e), sum(ytd_credit_balance_e),
427 sysdate, GCS_AGGREGATION_PKG.g_fnd_user_id,
428 sysdate, GCS_AGGREGATION_PKG.g_fnd_user_id,
429 GCS_AGGREGATION_PKG.g_fnd_login_id
430 FROM
431 GCS_HIERARCHIES_B ghb,
432 FEM_BALANCES fb,
433 GCS_CONS_RELATIONSHIPS gcr,
434 GCS_TREATMENTS_B gt
435 WHERE
436 ghb.hierarchy_id = p_hierarchy_id
437 AND gcr.hierarchy_id = ghb.hierarchy_id
438 AND gcr.parent_entity_id = p_cons_entity_id
439 AND gcr.actual_ownership_flag = ''Y''
440 AND p_period_end_date BETWEEN gcr.start_date
441 AND nvl(gcr.end_date, p_period_end_date)
442 AND gt.treatment_id (+) = gcr.treatment_id
443 AND nvl(gt.consolidation_type_code, ''FULL'') <> ''NONE''
444 AND fb.dataset_code = p_dataset_code
445 AND fb.ledger_id = ghb.fem_ledger_id
446 AND fb.cal_period_id = p_cal_period_id
447 AND fb.source_system_code = GCS_UTILITY_PKG.g_gcs_source_system_code
448 AND fb.currency_code IN (p_currency_code, ''STAT'')
449 AND fb.entity_id = gcr.child_entity_id
450 GROUP BY
451 fb.currency_code,
452 fb.company_cost_center_org_id,
453 fb.intercompany_id,
454 ';
455
456 curr_pos := 1;
457 body_len := LENGTH(body_block);
458 WHILE curr_pos <= body_len LOOP
459 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
460 line_num);
461 curr_pos := curr_pos + g_line_size;
462 line_num := line_num + 1;
463 END LOOP;
464
465 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
466 ' fb.', GCS_UTILITY_PKG.g_nl, '', line_num);
467
468 body_block :=
469 ' fb.line_item_id;
470
471 ELSE -- Balancing by Entity: need special handling of RE/Suspense/CTA
472
473 -- Values used for the special processing:
474 -- * default org id for the consolidation entity
475 l_default_org_id := GCS_UTILITY_PKG.get_org_id(p_cons_entity_id,
476 p_hierarchy_id);
477
478 -- * For determining intercompany type
479 SELECT specific_intercompany_id
480 INTO l_intercompany_id
481 FROM GCS_CATEGORIES_B
482 WHERE category_code = ''AGGREGATION'';
483
484 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
485 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
486 g_api || ''.'' || fn_name,
487 ''l_intercompany_id = '' || l_intercompany_id || '' '' ||
488 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
489 END IF;
490
491 -- * Retained earnings account template
492 GCS_TEMPLATES_PKG.get_dimension_template(p_hierarchy_id, ''RE'',
493 p_balance_type_code,
494 l_re_template);
495
496 -- * Suspense account template
497 GCS_TEMPLATES_PKG.get_dimension_template(p_hierarchy_id, ''SUSPENSE'',
498 p_balance_type_code,
499 l_sus_template);
500
501 -- For CTA processing: find default org id of all direct child entities
502 FOR rec IN get_child_info LOOP
503 g_entity_org_info(rec.child_entity_id) := rec.org_id;
504 END LOOP;
505
506
507 -- bug fix 5066467: rewrite the code for hanlding RE/SUSPENSE/CTA
508 -- Now we first select the rows from fb to gcs_entry_lines_gt table, RE/SUSPENSE will be handled in this step
509 -- Then we update gcs_entry_lines_gt for CTA lines
510 -- Lastly, we move everything from gcs_entry_lines_gt to gcs_entry_lines
511 INSERT INTO gcs_entry_lines_gt
512 (entry_id, line_item_id, company_cost_center_org_id, intercompany_id,
513 ';
514
515 curr_pos := 1;
516 body_len := LENGTH(body_block);
517 WHILE curr_pos <= body_len LOOP
521 line_num := line_num + 1;
518 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
519 line_num);
520 curr_pos := curr_pos + g_line_size;
522 END LOOP;
523
524 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
525 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
526
527 body_block :=
528 ' xtd_balance_e, ytd_balance_e,
529 ptd_debit_balance_e, ptd_credit_balance_e,
530 ytd_debit_balance_e, ytd_credit_balance_e)
531 SELECT
532 decode(currency_code, ''STAT'', p_stat_entry_id, p_entry_id),
533 fb.line_item_id,
534 ';
535
536 curr_pos := 1;
537 body_len := LENGTH(body_block);
538 WHILE curr_pos <= body_len LOOP
539 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
540 line_num);
541 curr_pos := curr_pos + g_line_size;
542 line_num := line_num + 1;
543 END LOOP;
544
545 -- company_cost_center_org_id
546 curr_pos := 1;
547 body_len := LENGTH(l_org_dim_str);
548 WHILE curr_pos <= body_len LOOP
549 ad_ddl.build_statement(SUBSTR(l_org_dim_str, curr_pos, g_line_size),
550 line_num);
551 curr_pos := curr_pos + g_line_size;
552 line_num := line_num + 1;
553 END LOOP;
554
555 -- intercompany_id
556 curr_pos := 1;
557 body_len := LENGTH(l_ic_dim_str);
558 WHILE curr_pos <= body_len LOOP
559 ad_ddl.build_statement(SUBSTR(l_ic_dim_str, curr_pos, g_line_size),
560 line_num);
561 curr_pos := curr_pos + g_line_size;
562 line_num := line_num + 1;
563 END LOOP;
564
565 -- optional active dimensions
566 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
567 ' fb.', GCS_UTILITY_PKG.g_nl, '', line_num);
568
569 body_block :=
570 ' sum(xtd_balance_e), sum(ytd_balance_e),
571 sum(ptd_debit_balance_e), sum(ptd_credit_balance_e),
572 sum(ytd_debit_balance_e), sum(ytd_credit_balance_e)
573 FROM
574 GCS_HIERARCHIES_B ghb,
575 FEM_BALANCES fb,
576 GCS_CONS_RELATIONSHIPS gcr,
577 GCS_TREATMENTS_B gt
578 WHERE ghb.hierarchy_id = p_hierarchy_id
579 AND gcr.hierarchy_id = p_hierarchy_id
580 AND gcr.parent_entity_id = p_cons_entity_id
581 AND gcr.actual_ownership_flag = ''Y''
582 AND p_period_end_date BETWEEN gcr.start_date AND
583 NVL (gcr.end_date, p_period_end_date)
584 AND gt.treatment_id(+) = gcr.treatment_id
585 AND NVL(gt.consolidation_type_code, ''FULL'') <> ''NONE''
586 AND fb.dataset_code = p_dataset_code
587 AND fb.ledger_id = ghb.fem_ledger_id
588 AND fb.cal_period_id = p_cal_period_id
589 AND fb.source_system_code = gcs_utility_pkg.g_gcs_source_system_code
590 AND fb.currency_code IN (p_currency_code, ''STAT'')
591 AND fb.entity_id = gcr.child_entity_id
592 GROUP BY
593 fb.currency_code,
594 ';
595
596 curr_pos := 1;
597 body_len := LENGTH(body_block);
598 WHILE curr_pos <= body_len LOOP
599 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
600 line_num);
601 curr_pos := curr_pos + g_line_size;
602 line_num := line_num + 1;
603 END LOOP;
604
605 -- company_cost_center_org_id
606 curr_pos := 1;
607 body_len := LENGTH(l_org_dim_str);
608 WHILE curr_pos <= body_len LOOP
609 ad_ddl.build_statement(SUBSTR(l_org_dim_str, curr_pos, g_line_size),
610 line_num);
611 curr_pos := curr_pos + g_line_size;
612 line_num := line_num + 1;
613 END LOOP;
614
615 -- intercompany_id
616 curr_pos := 1;
617 body_len := LENGTH(l_ic_dim_str);
618 WHILE curr_pos <= body_len LOOP
619 ad_ddl.build_statement(SUBSTR(l_ic_dim_str, curr_pos, g_line_size),
620 line_num);
621 curr_pos := curr_pos + g_line_size;
622 line_num := line_num + 1;
623 END LOOP;
624
625 -- optional active dimensions
626 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
627 ' fb.', GCS_UTILITY_PKG.g_nl, '', line_num);
628
629 body_block :=
630 ' fb.line_item_id;
631
632 UPDATE gcs_entry_lines_gt gelg
633 SET company_cost_center_org_id = l_default_org_id,
634 intercompany_id = DECODE(intercompany_id, company_cost_center_org_id,
635 DECODE(l_intercompany_id, NULL, l_default_org_id),
636 intercompany_id)
637 WHERE (
638 ';
639
640 curr_pos := 1;
641 body_len := LENGTH(body_block);
642 WHILE curr_pos <= body_len LOOP
643 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
644 line_num);
645 curr_pos := curr_pos + g_line_size;
646 line_num := line_num + 1;
647 END LOOP;
648
649 -- optional active dimensions
650 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
651 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
652
653 body_block :=
654 ' line_item_id, company_cost_center_org_id) IN (
655 SELECT
656 ' || g_subqry_grp;
657
658 curr_pos := 1;
659 body_len := LENGTH(body_block);
660 WHILE curr_pos <= body_len LOOP
661 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
662 line_num);
663 curr_pos := curr_pos + g_line_size;
667 body_block :=
664 line_num := line_num + 1;
665 END LOOP;
666
668 ' line_item_id,
669 retrieve_org_id (cr2.child_entity_id)
670 FROM gcs_cons_relationships cr2,
671 gcs_curr_treatments_b gctb
672 WHERE cr2.parent_entity_id = p_cons_entity_id
673 AND cr2.hierarchy_id = p_hierarchy_id
674 AND cr2.actual_ownership_flag = ''Y''
675 AND p_period_end_date BETWEEN cr2.start_date
676 AND NVL (cr2.end_date,
677 p_period_end_date
678 )
679 AND gctb.curr_treatment_id IN (
680 SELECT gcr.curr_treatment_id
681 FROM gcs_cons_relationships gcr
682 START WITH gcr.hierarchy_id = p_hierarchy_id
683 AND gcr.parent_entity_id =
684 p_cons_entity_id
685 AND gcr.actual_ownership_flag = ''Y''
686 AND p_period_end_date
687 BETWEEN gcr.start_date
688 AND NVL (gcr.end_date,
689 p_period_end_date
690 )
691 CONNECT BY PRIOR gcr.child_entity_id =
692 gcr.parent_entity_id
693 AND gcr.hierarchy_id = p_hierarchy_id
694 AND gcr.actual_ownership_flag = ''Y''
695 AND p_period_end_date
696 BETWEEN gcr.start_date
697 AND NVL (gcr.end_date,
698 p_period_end_date
699 ))
700 GROUP BY
701 ' || g_subqry_grp ||' line_item_id, cr2.child_entity_id);
702
703 INSERT /*+ append */INTO gcs_entry_lines
704 (entry_id, company_cost_center_org_id, line_item_id,
705 intercompany_id,
706 ';
707
708 curr_pos := 1;
709 body_len := LENGTH(body_block);
710 WHILE curr_pos <= body_len LOOP
711 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
712 line_num);
713 curr_pos := curr_pos + g_line_size;
714 line_num := line_num + 1;
715 END LOOP;
716
717 -- optional active dimensions
718 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
719 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
720
721 body_block :=
722 ' xtd_balance_e, ytd_balance_e,
723 ptd_debit_balance_e, ptd_credit_balance_e,
724 ytd_debit_balance_e, ytd_credit_balance_e,
725 creation_date, created_by, last_update_date,
726 last_updated_by, last_update_login)
727 SELECT entry_id, company_cost_center_org_id, line_item_id,
728 intercompany_id,
729 ';
730
731 curr_pos := 1;
732 body_len := LENGTH(body_block);
733 WHILE curr_pos <= body_len LOOP
734 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
735 line_num);
736 curr_pos := curr_pos + g_line_size;
737 line_num := line_num + 1;
738 END LOOP;
739
740 -- optional active dimensions
741 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
742 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
743
744 body_block :=
745 '
746 SUM (xtd_balance_e), SUM (ytd_balance_e),
747 SUM (ptd_debit_balance_e), SUM (ptd_credit_balance_e),
748 SUM (ytd_debit_balance_e), SUM (ytd_credit_balance_e),
749 SYSDATE, gcs_aggregation_pkg.g_fnd_user_id, SYSDATE,
750 gcs_aggregation_pkg.g_fnd_user_id,
751 gcs_aggregation_pkg.g_fnd_login_id
752 FROM gcs_entry_lines_gt
753 GROUP BY entry_id,
754 ';
755
756 curr_pos := 1;
757 body_len := LENGTH(body_block);
758 WHILE curr_pos <= body_len LOOP
759 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
760 line_num);
761 curr_pos := curr_pos + g_line_size;
762 line_num := line_num + 1;
763 END LOOP;
764
765 -- optional active dimensions
766 line_num := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(
767 ' ', GCS_UTILITY_PKG.g_nl, '', line_num);
768
769 body_block :=
770 ' company_cost_center_org_id,
771 line_item_id,
772 intercompany_id;
773 END IF; -- l_bal_by_org_flag
774
775 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
776 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
777 g_api || ''.'' || fn_name,
778 ''Inserted '' || to_char(SQL%ROWCOUNT) || '' row(s)'');
779 END IF;
780
781 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
782 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
783 g_api || ''.'' || fn_name,
784 GCS_UTILITY_PKG.g_module_success || fn_name ||
785 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
786 END IF;
787 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
788 -- fn_name || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
793 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
794 g_api || ''.'' || fn_name,
795 GCS_UTILITY_PKG.g_module_failure || fn_name ||
796 to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
797 END IF;
798 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
799 -- fn_name || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
800 RAISE;
801 END insert_full_entry_lines;
802
803 END GCS_AGGREGATION_DYNAMIC_PKG;';
804
805 curr_pos := 1;
806 body_len := LENGTH(body_block);
807 WHILE curr_pos <= body_len LOOP
808 ad_ddl.build_statement(SUBSTR(body_block, curr_pos, g_line_size),
809 line_num);
810 curr_pos := curr_pos + g_line_size;
811 line_num := line_num + 1;
812 END LOOP;
813
814 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
815 'GCS', 'GCS_AGGREGATION_DYNAMIC_PKG',
816 1, line_num - 1, 'FALSE', comp_err);
817
818 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
819 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
820 g_api || '.' || fn_name,
821 GCS_UTILITY_PKG.g_module_success || fn_name ||
822 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
823 END IF;
824 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
825 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
826
827 EXCEPTION
828 WHEN OTHERS THEN
829 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
830 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
831 g_api || '.' || fn_name,
832 SUBSTR(SQLERRM, 1, 4000));
833 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
834 g_api || '.' || fn_name,
835 GCS_UTILITY_PKG.g_module_failure || fn_name ||
836 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
837 END IF;
838 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
839 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
840 END create_package;
841
842 END GCS_AGGREGATION_DYN_BUILD_PKG;