[Home] [Help]
PACKAGE BODY: APPS.GCS_TEMPLATES_PKG
Source
1 PACKAGE BODY GCS_TEMPLATES_PKG AS
2 /* $Header: gcstempb.pls 120.4 2007/10/08 21:23:03 skamdar ship $ */
3
4 --
5 -- Private Global Variables
6 --
7 -- The API name
8 g_pkg_name CONSTANT VARCHAR2 (50) := 'gcs.plsql.GCS_TEMPLATES_PKG';
9 -- A newline character. Included for convenience when writing long strings.
10 g_nl CONSTANT VARCHAR2 (1) := '
11 ';
12
13 PROCEDURE create_dynamic_pkg (
14 x_errbuf IN VARCHAR2,
15 x_retcode IN VARCHAR2
16 )
17 IS
18 l_proc_body VARCHAR2 (32767);
19 l_select_clause VARCHAR2 (5000); -- vars to form a cursor
20 l_insert_clause VARCHAR2 (5000); -- vars to form a cursor
21 l_from_clause VARCHAR2 (5000); -- vars to form a cursor
22 l_where_clause VARCHAR2 (5000); -- vars to form a cursor
23 l_index_column_name VARCHAR2 (30);
24 l_decode_text VARCHAR2(5000);
25 l_decode_group_text VARCHAR2(5000);
26 l_bal_decode_text VARCHAR2(5000);
27 l_gel_dims_text VARCHAR2(5000);
28 l_src_dims_text VARCHAR2(5000);
29 l_gdt_dims_text VARCHAR2(5000);
30 l_equal_text VARCHAR2(5000);
31 l_bind_vars_text VARCHAR2(5000);
32 l_bal_bind_vars_text VARCHAR2(5000);
33 l_bind_dims_text VARCHAR2(5000);
34 l_bind_dims_var_text VARCHAR2(5000);
35 err VARCHAR2 (10);
36 curr_index NUMBER (5) := 1;
37 lines NUMBER (5) := 0;
38 body_len NUMBER (5);
39 l_api_name VARCHAR2 (30);
40 BEGIN
41 l_api_name := 'CREATE_PROCESS';
42 SAVEPOINT create_start;
43
44 l_proc_body := 'CREATE or REPLACE PACKAGE BODY GCS_TEMPLATES_DYNAMIC_PKG AS
45 /* $Header: gcstempb.pls 120.4 2007/10/08 21:23:03 skamdar ship $ */
46 gcs_tmp_invalid_hierarchy EXCEPTION;
47 gcs_tmp_invalid_sign EXCEPTION;
48 gcs_tmp_balancing_failed EXCEPTION;
49 -- The API name
50 g_pkg_name VARCHAR2 (50) := ''gcs.plsql.GCS_TEMPLATES_DYNAMIC_PKG'';
51 -- A newline character. Included for convenience when writing long strings.
52 g_nl VARCHAR2 (1) := ''
53 '';
54
55 -- Used to obtain specific intercompany id
56 CURSOR intercompany_c IS
57 SELECT SPECIFIC_INTERCOMPANY_ID
58 FROM GCS_CATEGORIES_B
59 WHERE CATEGORY_CODE = ''INTRACOMPANY'';
60
61 --
62 -- Public Procedures
63 --
64 PROCEDURE calculate_re (
65 p_entry_id NUMBER,
66 p_hierarchy_id NUMBER,
67 p_bal_type_code VARCHAR2,
68 p_entity_id NUMBER,
69 p_data_prep_flag VARCHAR2 DEFAULT ''N''
70 )
71 IS
72 l_merge_statement VARCHAR2 (5000);
73
74 -- Used to obtain hierarchy information
75 CURSOR hierarchy_c
76 IS
77 SELECT hb.balance_by_org_flag, hb.column_name
78 FROM gcs_hierarchies_b hb
79 WHERE hb.hierarchy_id = p_hierarchy_id;
80
81 org_tracking_flag VARCHAR2 (1);
82 secondary_dimension_column VARCHAR2 (30);
83
84 -- Used to obtain sign information
85 CURSOR sign_c IS
86 SELECT fxata.number_assign_value
87 FROM gcs_dimension_templates dt,
88 fem_ln_items_attr flia,
89 fem_ext_acct_types_attr fxata
90 WHERE dt.hierarchy_id = p_hierarchy_id
91 AND dt.template_code = ''RE''
92 AND flia.line_item_id = dt.line_item_id
93 AND flia.attribute_id = GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
94 AND flia.version_id = GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
95 AND fxata.ext_account_type_code = flia.dim_attribute_varchar_member
96 AND fxata.attribute_id = GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').attribute_id
97 AND fxata.version_id = GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').version_id;
98
99 sign_value NUMBER;
100 l_intercompany_id NUMBER(15);
101
102 -- Used to get the org and secondary dimension value IDs to use
103 -- in the balancing account, and the credit excess amount.
104 l_org_id NUMBER;
105 l_re_required VARCHAR2(1);
106 l_api_name VARCHAR2 (30) := ''CALCULATE_RE'';
107 BEGIN
108 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
109 THEN
110 fnd_log.STRING (fnd_log.level_procedure,
111 g_pkg_name || ''.'' || l_api_name,
112 gcs_utility_pkg.g_module_enter
113 || '' ''
114 || l_api_name
115 || ''() ''
116 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
117 );
118 END IF;
119
120 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
121 THEN
122 fnd_log.STRING (fnd_log.level_statement,
123 g_pkg_name || ''.'' || l_api_name,
124 ''SELECT ''''X''''''
125 || g_nl
126 || ''FROM gcs_entry_headers ''
127 || g_nl
128 || ''WHERE entry_id = ''
129 || p_entry_id
130 || g_nl
131 || ''AND start_cal_period_id <> end_cal_period_id ''
132 );
133 END IF;
134
135 IF p_data_prep_flag = ''N'' THEN
136 BEGIN
137 SELECT ''Y''
138 INTO l_re_required
139 FROM gcs_entry_headers
140 WHERE entry_id = p_entry_id
141 AND start_cal_period_id <> nvl(end_cal_period_id, 0);
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 RETURN;
145 END;
146 ELSE
147 l_re_required := ''Y'';
148 END IF;
149
150 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
151 THEN
152 fnd_log.STRING (fnd_log.level_statement,
153 g_pkg_name || ''.'' || l_api_name,
154 ''SELECT hb.balance_by_org_flag, hb.column_name''
155 || g_nl
156 || ''FROM gcs_hierarchies_b hb ''
157 || g_nl
158 || ''WHERE hb.hierarchy_id = ''
159 || p_hierarchy_id
160 );
161 END IF;
162
163 -- Get org tracking and secondary tracking information.
164 OPEN hierarchy_c;
165
166 FETCH hierarchy_c
167 INTO org_tracking_flag, secondary_dimension_column;
168
169 IF hierarchy_c%NOTFOUND
170 THEN
171 CLOSE hierarchy_c;
172
173 RAISE gcs_tmp_invalid_hierarchy;
174 END IF;
175
176 CLOSE hierarchy_c;
177
178 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
179 THEN
180 fnd_log.STRING (fnd_log.level_statement,
181 g_pkg_name || ''.'' || l_api_name,
182 ''SELECT specific_intercompany_id FROM gcs_hierarchies_b ''
183 || '' WHERE hierarchy_id = '' || p_hierarchy_id
184 || '' AND INTERCOMPANY_ORG_TYPE_CODE = ''''SPECIFIC_VALUE'''''');
185 END IF;
186
187 -- Get specific intercompany_id, if null, using orgs
188 OPEN intercompany_c;
189
190 FETCH intercompany_c
191 INTO l_intercompany_id;
192
193 CLOSE intercompany_c;
194
195 -- Get the signage of the suspense line item
196 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
197 THEN
198 fnd_log.STRING (fnd_log.level_statement,
199 g_pkg_name || ''.'' || l_api_name,
200 ''SELECT fxata.number_assign_value'' || g_nl ||
201 ''FROM gcs_dimension_templates dt, '' || g_nl ||
202 '' fem_ln_items_attr flia,'' || g_nl ||
203 '' fem_ext_acct_types_attr fxata'' || g_nl ||
204 ''WHERE dt.hierarchy_id = '' || p_hierarchy_id || g_nl ||
205 ''AND dt.template_code = ''''RE'''''' || g_nl ||
206 ''AND flia.line_item_id = dt.line_item_id'' || g_nl ||
207 ''AND flia.attribute_id = '' ||
208 GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id || g_nl ||
209 ''AND flia.version_id = '' ||
210 GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id || g_nl ||
211 ''AND fxata.ext_account_type_code = flia.dim_attribute_varchar_member'' || g_nl ||
212 ''AND fxata.attribute_id = '' ||
213 GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').attribute_id || g_nl ||
214 ''AND fxata.version_id = '' ||
215 GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').version_id
216 );
217 END IF;
218
219 -- Get signage information
220 OPEN sign_c;
221 FETCH sign_c INTO sign_value;
222 IF sign_c%NOTFOUND
223 THEN
224 CLOSE sign_c;
225 RAISE gcs_tmp_invalid_sign;
226 END IF;
227 CLOSE sign_c;
228
229
230 IF org_tracking_flag = ''N''
231 THEN
232 l_org_id := gcs_utility_pkg.Get_Org_Id(p_entity_id => p_entity_id,
233 p_hierarchy_id => p_hierarchy_id);
234 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
235 THEN
236 fnd_log.STRING (fnd_log.level_statement,
237 g_pkg_name || ''.'' || l_api_name,
238 ''l_org_id = '' || l_org_id
239 );
240 END IF;
241
242 END IF;
243 ';
244 curr_index := 1;
245 body_len := LENGTH (l_proc_body);
246
247 WHILE curr_index <= body_len
248 LOOP
249 lines := lines + 1;
250 ad_ddl.build_statement (SUBSTR (l_proc_body, curr_index, 200),
251 lines);
252 curr_index := curr_index + 200;
253 END LOOP;
254
255 l_index_column_name := gcs_utility_pkg.g_gcs_dimension_info.FIRST;
256
257 WHILE (l_index_column_name <= gcs_utility_pkg.g_gcs_dimension_info.LAST)
258 LOOP
259 IF ( l_index_column_name <> 'ENTITY_ID'
260 AND gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).required_for_gcs =
261 'Y'
262 )
263 THEN
264 l_gel_dims_text :=
265 l_gel_dims_text
266 || ' gel.'||l_index_column_name||','||g_nl;
267 IF l_index_column_name = 'INTERCOMPANY_ID' THEN
268 l_src_dims_text :=
269 l_src_dims_text
270 || ' nvl(:20, src.COMPANY_COST_CENTER_ORG_ID),'||g_nl;
271 l_equal_text :=
272 l_equal_text
273 || ' AND gel.'||l_index_column_name
274 || '= nvl(:20, src.COMPANY_COST_CENTER_ORG_ID)'||g_nl;
275 ELSE
276 l_src_dims_text :=
277 l_src_dims_text
278 || ' src.'||l_index_column_name||','||g_nl;
279 l_equal_text :=
280 l_equal_text
281 || ' AND gel.'||l_index_column_name
282 || '= src.'|| l_index_column_name||g_nl;
283
284 IF l_index_column_name <> 'COMPANY_COST_CENTER_ORG_ID' THEN
285 IF l_decode_group_text IS NULL THEN
286 l_decode_group_text := 'decode(:10, '''''||l_index_column_name
287 || ''''', gel_1.'||l_index_column_name
288 || ', gdt.' ||l_index_column_name ||')';
289 ELSE
290 l_decode_group_text :=
291 l_decode_group_text||','||g_nl||'decode(:10, '''''||l_index_column_name
292 || ''''', gel_1.'||l_index_column_name
293 || ', gdt.' ||l_index_column_name ||')';
294 END IF;
295 l_decode_text :=
296 l_decode_text||'decode(:10, '''''||l_index_column_name
297 || ''''', gel_1.'||l_index_column_name
298 || ', gdt.' ||l_index_column_name ||') '||l_index_column_name||','||g_nl;
299 l_bal_decode_text :=
300 l_bal_decode_text||'decode(''''''||secondary_dimension_column||'''''', '''''
301 ||l_index_column_name
302 || ''''', gel_1.''||secondary_dimension_column||'', :5) '||l_index_column_name||','||g_nl;
303 l_bind_vars_text :=
304 l_bind_vars_text||' secondary_dimension_column,'||g_nl;
305 l_bal_bind_vars_text :=
306 l_bal_bind_vars_text||' p_template.'
307 ||l_index_column_name||', '||g_nl;
308 l_gdt_dims_text :=
309 l_gdt_dims_text
310 || ' gdt.'||l_index_column_name||','||g_nl;
311 l_bind_dims_text :=
312 l_bind_dims_text || ':1 '||l_index_column_name||', '||g_nl;
313 l_bind_dims_var_text :=
314 l_bind_dims_var_text || 'p_template.'|| l_index_column_name||', '||g_nl;
315 END IF;
316 END IF;
317
318 END IF;
319
320 l_index_column_name :=
321 gcs_utility_pkg.g_gcs_dimension_info.NEXT (l_index_column_name);
322 END LOOP;
323
324 --Bugfix 6072367: Added check for line type code as calculated
325 l_equal_text := l_equal_text || ' AND gel.line_type_code = ''''CALCULATED'''' ';
326 l_proc_body :=
327 '
328
329 IF org_tracking_flag = ''Y'' AND secondary_dimension_column IS NOT NULL
330 THEN
331 l_merge_statement :=
332 ''MERGE INTO gcs_entry_lines gel
333 USING (
334 SELECT gel_1.company_cost_center_org_id,
335 ' ||l_decode_text
336 ||'
337 SUM ( NVL (gel_1.ytd_credit_balance_e, 0)
338 - NVL (gel_1.ytd_debit_balance_e, 0)
339 ) amount
340 FROM gcs_dimension_templates gdt, gcs_entry_lines gel_1, fem_ln_items_attr flia,
341 fem_ext_acct_types_attr feata
342 WHERE gdt.hierarchy_id = :1
343 AND gdt.template_code = ''''RE''''
344 AND gel_1.entry_id = :2
345 AND feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
346 AND feata.dim_attribute_numeric_member IS NULL
347 AND flia.value_set_id = ''
348 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id
349 || ''
350 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
351 AND flia.attribute_id = ''
352 || gcs_utility_pkg.g_dimension_attr_info
353 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
354 || ''
355 AND feata.attribute_id = ''
356 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id
357 || ''
358 AND flia.version_id = ''
359 || gcs_utility_pkg.g_dimension_attr_info
360 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
361 || ''
362 AND feata.version_id = ''
363 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id
364 || ''
365 AND gel_1.line_item_id = flia.line_item_id
366 GROUP BY
367 ' ||l_decode_group_text
368 ||', gel_1.company_cost_center_org_id ) src
369 ON ( gel.entry_id = :2
370 ' ||l_equal_text
371 ||')
372 WHEN MATCHED THEN
373 UPDATE
374 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * -src.amount,
375 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * -src.amount,
376 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.amount), 1, 0,-src.amount),
377 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
378 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.amount), 1, 0,-src.amount),
379 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
380 gel.last_update_date = SYSDATE,
381 gel.last_updated_by = :3
382 WHEN NOT MATCHED THEN
383 INSERT (entry_id, line_type_code, description, '||l_gel_dims_text
384 ||'gel.xtd_balance_e, gel.ytd_balance_e,
385 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
386 gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
387 creation_date, created_by, last_update_date, last_updated_by,
388 last_update_login)
389 VALUES (:2, ''''CALCULATED'''', ''''RE_LINE'''', '||l_src_dims_text
390 ||' -src.amount * '' || sign_value || '', -src.amount * '' || sign_value || '',
391 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
392 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
393 SYSDATE, :3, SYSDATE, :3, :4)
394 '';
395
396 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
397 THEN
398 fnd_log.STRING (fnd_log.level_statement,
399 g_pkg_name || ''.'' || l_api_name,
400 ''l_merge_statement = ''|| l_merge_statement
401 );
402 END IF;
403
404 EXECUTE IMMEDIATE l_merge_statement
405 USING '|| l_bind_vars_text
406 ||' p_hierarchy_id,
407 p_entry_id,
408 '|| l_bind_vars_text
409 ||' p_entry_id,
410 l_intercompany_id,
411 fnd_global.user_id,
412 p_entry_id,
413 l_intercompany_id,
414 fnd_global.user_id,
415 fnd_global.user_id,
416 fnd_global.login_id;
417 ELSIF secondary_dimension_column IS NOT NULL
418 THEN
419 l_merge_statement :=
420 ''MERGE INTO gcs_entry_lines gel
421 USING (
422 SELECT :6 company_cost_center_org_id,
423 ' ||l_decode_text
424 ||'
425 SUM ( NVL (gel_1.ytd_credit_balance_e, 0)
426 - NVL (gel_1.ytd_debit_balance_e, 0)) amount
427 FROM gcs_dimension_templates gdt, gcs_entry_lines gel_1, fem_ln_items_attr flia,
428 fem_ext_acct_types_attr feata
429 WHERE gdt.hierarchy_id = :1
430 AND gdt.template_code = ''''RE''''
431 AND gel_1.entry_id = :2
432 AND feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
433 AND feata.dim_attribute_numeric_member IS NULL
434 AND flia.value_set_id = ''
435 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id
436 || ''
437 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
438 AND flia.attribute_id = ''
439 || gcs_utility_pkg.g_dimension_attr_info
440 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
441 || ''
442 AND feata.attribute_id = ''
443 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id
444 || ''
445 AND flia.version_id = ''
446 || gcs_utility_pkg.g_dimension_attr_info
447 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
448 || ''
449 AND feata.version_id = ''
450 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id
451 || ''
452 AND gel_1.line_item_id = flia.line_item_id
453 GROUP BY
454 ' ||l_decode_group_text
455 ||') src
456 ON ( gel.entry_id = :2
457 ' ||l_equal_text
458 ||')
459 WHEN MATCHED THEN
460 UPDATE
461 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * -src.amount,
462 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * -src.amount,
463 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
464 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
465 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
466 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
467 gel.last_update_date = SYSDATE,
468 gel.last_updated_by = :3
469 WHEN NOT MATCHED THEN
470 INSERT (entry_id, line_type_code, description, '||l_gel_dims_text
471 ||' gel.xtd_balance_e, gel.ytd_balance_e,
472 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
473 gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
474 creation_date, created_by, last_update_date, last_updated_by,
475 last_update_login)
476 VALUES (:2, ''''CALCULATED'''', ''''RE_LINE'''', '||l_src_dims_text
477 ||' -src.amount * '' || sign_value || '', -src.amount * '' || sign_value || '',
478 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
479 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
480 SYSDATE, :3, SYSDATE, :3, :4)
481 '';
482
483 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
484 THEN
485 fnd_log.STRING (fnd_log.level_statement,
486 g_pkg_name || ''.'' || l_api_name,
487 ''l_merge_statement = ''|| l_merge_statement
488 );
489 END IF;
490
491 EXECUTE IMMEDIATE l_merge_statement
492 USING l_org_id,
493 '|| l_bind_vars_text
494 ||' p_hierarchy_id,
495 p_entry_id,
496 '|| l_bind_vars_text
497 ||' p_entry_id,
498 l_intercompany_id,
499 fnd_global.user_id,
500 p_entry_id,
501 l_intercompany_id,
502 fnd_global.user_id,
503 fnd_global.user_id,
504 fnd_global.login_id;
505 ELSIF org_tracking_flag = ''Y''
506 THEN
507 l_merge_statement :=
508 ''MERGE INTO gcs_entry_lines gel
509 USING (SELECT gel_1.company_cost_center_org_id, '||l_gdt_dims_text||'
510 SUM ( NVL (gel_1.ytd_credit_balance_e, 0)
511 - NVL (gel_1.ytd_debit_balance_e, 0)
512 ) amount
513 FROM gcs_dimension_templates gdt, gcs_entry_lines gel_1, fem_ln_items_attr flia,
514 fem_ext_acct_types_attr feata
515 WHERE gdt.hierarchy_id = :1
516 AND gdt.template_code = ''''RE''''
517 AND gel_1.entry_id = :2
518 AND feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
519 AND feata.dim_attribute_numeric_member IS NULL
520 AND flia.value_set_id = ''
521 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id
522 || ''
523 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
524 AND flia.attribute_id = ''
525 || gcs_utility_pkg.g_dimension_attr_info
526 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
527 || ''
528 AND feata.attribute_id = ''
529 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id
530 || ''
531 AND flia.version_id = ''
532 || gcs_utility_pkg.g_dimension_attr_info
533 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
534 || ''
535 AND feata.version_id = ''
536 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id
537 || ''
538 AND gel_1.line_item_id = flia.line_item_id
539 GROUP BY '||l_gdt_dims_text||'gel_1.company_cost_center_org_id) src
540 ON ( gel.entry_id = :2
541 ' ||l_equal_text
542 ||')
543 WHEN MATCHED THEN
544 UPDATE
545 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * -src.amount,
546 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * -src.amount,
547 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
548 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
549 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
550 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
551 gel.last_update_date = SYSDATE,
552 gel.last_updated_by = :5
553 WHEN NOT MATCHED THEN
554 INSERT (entry_id, line_type_code, description, '||l_gel_dims_text|| 'gel.xtd_balance_e, gel.ytd_balance_e,
555 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
556 gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
557 creation_date, created_by, last_update_date, last_updated_by,
558 last_update_login)
559 VALUES (:2, ''''CALCULATED'''', ''''RE_LINE'''', '||l_src_dims_text
560 ||' -src.amount * '' || sign_value || '', -src.amount * '' || sign_value || '',
561 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
562 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
563 SYSDATE, :3, SYSDATE, :3, :4)
564 '';
565
566 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
567 THEN
568 fnd_log.STRING (fnd_log.level_statement,
569 g_pkg_name || ''.'' || l_api_name,
570 ''l_merge_statement = ''|| l_merge_statement
571 );
572 END IF;
573
574 EXECUTE IMMEDIATE l_merge_statement
575 USING p_hierarchy_id,
576 p_entry_id,
577 p_entry_id,
578 l_intercompany_id,
579 fnd_global.user_id,
580 p_entry_id,
581 l_intercompany_id,
582 fnd_global.user_id,
583 fnd_global.user_id,
584 fnd_global.login_id;
585 ELSE
586 l_merge_statement :=
587 ''MERGE INTO gcs_entry_lines gel
588 USING (SELECT :6 company_cost_center_org_id, '||l_gdt_dims_text||'
589 SUM ( NVL (gel_1.ytd_credit_balance_e, 0)
590 - NVL (gel_1.ytd_debit_balance_e, 0)
591 ) amount
592 FROM gcs_dimension_templates gdt, gcs_entry_lines gel_1, fem_ln_items_attr flia,
593 fem_ext_acct_types_attr feata
594 WHERE gdt.hierarchy_id = :1
595 AND gdt.template_code = ''''RE''''
596 AND gel_1.entry_id = :2
597 AND feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
598 AND feata.dim_attribute_numeric_member IS NULL
599 AND flia.value_set_id = ''
600 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id
601 || ''
602 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
603 AND flia.attribute_id = ''
604 || gcs_utility_pkg.g_dimension_attr_info
605 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
606 || ''
607 AND feata.attribute_id = ''
608 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id
609 || ''
610 AND flia.version_id = ''
611 || gcs_utility_pkg.g_dimension_attr_info
612 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
613 || ''
614 AND feata.version_id = ''
615 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id
616 || ''
617 AND gel_1.line_item_id = flia.line_item_id
618 GROUP BY '||SUBSTR(l_gdt_dims_text, 0, LENGTH(l_gdt_dims_text)-2)||') src
619 ON ( gel.entry_id = :2
620 ' ||l_equal_text
621 ||')
622 WHEN MATCHED THEN
623 UPDATE
624 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * -src.amount,
625 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * -src.amount,
626 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
627 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
628 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
629 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
630 gel.last_update_date = SYSDATE,
631 gel.last_updated_by = :5
632 WHEN NOT MATCHED THEN
633 INSERT (entry_id, line_type_code, description, '||l_gel_dims_text||'gel.xtd_balance_e, gel.ytd_balance_e,
634 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
635 gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
636 creation_date, created_by, last_update_date, last_updated_by,
637 last_update_login)
638 VALUES (:2, ''''CALCULATED'''', ''''RE_LINE'''', '||l_src_dims_text
639 ||' -src.amount * '' || sign_value || '', -src.amount * '' || sign_value || '',
640 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
641 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
642 SYSDATE, :3, SYSDATE, :3, :4)
643 '';
644
645 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
646 THEN
647 fnd_log.STRING (fnd_log.level_statement,
648 g_pkg_name || ''.'' || l_api_name,
649 ''l_merge_statement = ''|| l_merge_statement
650 );
651 END IF;
652
653 EXECUTE IMMEDIATE l_merge_statement
654 USING l_org_id,
655 p_hierarchy_id,
656 p_entry_id,
657 p_entry_id,
658 l_intercompany_id,
659 fnd_global.user_id,
660 p_entry_id,
661 l_intercompany_id,
662 fnd_global.user_id,
663 fnd_global.user_id,
664 fnd_global.login_id;
665 END IF;
666
667 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
668 THEN
669 fnd_log.STRING (fnd_log.level_procedure,
670 g_pkg_name || ''.'' || l_api_name,
671 gcs_utility_pkg.g_module_success
672 || '' ''
673 || l_api_name
674 || ''() ''
675 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
676 );
677 END IF;
678 EXCEPTION
679 WHEN gcs_tmp_invalid_sign
680 THEN
681 fnd_message.set_name (''GCS'', ''GCS_TMP_INVALID_SIGN'');
682
683 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
684 THEN
685 fnd_log.STRING (fnd_log.level_procedure,
686 g_pkg_name || ''.'' || l_api_name,
687 gcs_utility_pkg.g_module_failure
688 || '' ''
689 || l_api_name
690 || ''() ''
691 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
692 );
693 END IF;
694 RAISE gcs_tmp_balancing_failed;
695 END calculate_re;
696 ';
697 curr_index := 1;
698 body_len := LENGTH (l_proc_body);
699
700 WHILE curr_index <= body_len
701 LOOP
702 lines := lines + 1;
703 ad_ddl.build_statement (SUBSTR (l_proc_body, curr_index, 200),
704 lines);
705 curr_index := curr_index + 200;
706 END LOOP;
707
708 l_proc_body := '
709 PROCEDURE calculate_dp_re (
710 p_entry_id NUMBER,
711 p_hierarchy_id NUMBER,
712 p_bal_type_code VARCHAR2,
713 p_entity_id NUMBER,
714 p_pre_cal_period_id NUMBER,
715 p_first_ever_data_prep VARCHAR2
716 )
717 IS
718 l_merge_statement VARCHAR2 (5000);
719
720 -- Used to obtain hierarchy information
721 CURSOR hierarchy_c
722 IS
723 SELECT hb.balance_by_org_flag, hb.column_name
724 FROM gcs_hierarchies_b hb
725 WHERE hb.hierarchy_id = p_hierarchy_id;
726
727 org_tracking_flag VARCHAR2 (1);
728 secondary_dimension_column VARCHAR2 (30);
729
730 -- Used to obtain sign information
731 CURSOR sign_c IS
732 SELECT fxata.number_assign_value
733 FROM gcs_dimension_templates dt,
734 fem_ln_items_attr flia,
735 fem_ext_acct_types_attr fxata
736 WHERE dt.hierarchy_id = p_hierarchy_id
737 AND dt.template_code = ''RE''
738 AND flia.line_item_id = dt.line_item_id
739 AND flia.attribute_id = GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
740 AND flia.version_id = GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
741 AND fxata.ext_account_type_code = flia.dim_attribute_varchar_member
742 AND fxata.attribute_id = GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').attribute_id
743 AND fxata.version_id = GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').version_id;
744
745 sign_value NUMBER;
746 l_intercompany_id NUMBER(15);
747
748 -- Used to get the org and secondary dimension value IDs to use
749 -- in the balancing account, and the credit excess amount.
750 l_org_id NUMBER;
751 l_api_name VARCHAR2 (30) := ''CALCULATE_DP_RE'';
752 BEGIN
753
754 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
755 THEN
756 fnd_log.STRING (fnd_log.level_statement,
757 g_pkg_name || ''.'' || l_api_name,
758 ''SELECT hb.balance_by_org_flag, hb.column_name''
759 || g_nl
760 || ''FROM gcs_hierarchies_b hb ''
761 || g_nl
762 || ''WHERE hb.hierarchy_id = ''
763 || p_hierarchy_id
764 );
765 END IF;
766
767 IF p_first_ever_data_prep = ''Y'' THEN
768 calculate_re( p_entry_id => p_entry_id,
769 p_hierarchy_id => p_hierarchy_id,
770 p_bal_type_code => p_bal_type_code,
771 p_entity_id => p_entity_id,
772 p_data_prep_flag => ''Y''
773 );
774 RETURN;
775 END IF;
776
777 -- Get org tracking and secondary tracking information.
778 OPEN hierarchy_c;
779
780 FETCH hierarchy_c
781 INTO org_tracking_flag, secondary_dimension_column;
782
783 IF hierarchy_c%NOTFOUND
784 THEN
785 CLOSE hierarchy_c;
786
787 RAISE gcs_tmp_invalid_hierarchy;
788 END IF;
789
790 CLOSE hierarchy_c;
791
792 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
793 THEN
794 fnd_log.STRING (fnd_log.level_statement,
795 g_pkg_name || ''.'' || l_api_name,
796 ''SELECT specific_intercompany_id FROM gcs_hierarchies_b ''
797 || '' WHERE hierarchy_id = '' || p_hierarchy_id
798 || '' AND INTERCOMPANY_ORG_TYPE_CODE = ''''SPECIFIC_VALUE'''''');
799 END IF;
800
801 -- Get specific intercompany_id, if null, using orgs
802 OPEN intercompany_c;
803
804 FETCH intercompany_c
805 INTO l_intercompany_id;
806
807 CLOSE intercompany_c;
808
809 -- Get the signage of the suspense line item
810 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
811 THEN
812 fnd_log.STRING (fnd_log.level_statement,
813 g_pkg_name || ''.'' || l_api_name,
814 ''SELECT fxata.number_assign_value'' || g_nl ||
815 ''FROM gcs_dimension_templates dt'' || g_nl ||
816 '' fem_ln_items_attr flia,'' || g_nl ||
817 '' fem_ext_acct_types_attr fxata'' || g_nl ||
818 ''WHERE dt.hierarchy_id = '' || p_hierarchy_id || g_nl ||
819 ''AND dt.template_code = ''''RE'''''' || g_nl ||
820 ''AND flia.line_item_id = dt.line_item_id'' || g_nl ||
821 ''AND flia.attribute_id = '' ||
822 GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id || g_nl ||
823 ''AND flia.version_id = '' ||
824 GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id || g_nl ||
825 ''AND fxata.ext_account_type_code = flia.dim_attribute_varchar_member'' || g_nl ||
826 ''AND fxata.attribute_id = '' ||
827 GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').attribute_id|| g_nl ||
828 ''AND fxata.version_id = '' ||
829 GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').version_id
830 );
831 END IF;
832
833 -- Get signage information
834 OPEN sign_c;
835 FETCH sign_c INTO sign_value;
836 IF sign_c%NOTFOUND
837 THEN
838 CLOSE sign_c;
839 RAISE gcs_tmp_invalid_sign;
840 END IF;
841 CLOSE sign_c;
842
843
844 IF org_tracking_flag = ''N''
845 THEN
846 l_org_id := gcs_utility_pkg.Get_Org_Id(p_entity_id => p_entity_id,
847 p_hierarchy_id => p_hierarchy_id);
848 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
849 THEN
850 fnd_log.STRING (fnd_log.level_statement,
851 g_pkg_name || ''.'' || l_api_name,
852 ''l_org_id = '' || l_org_id
853 );
854 END IF;
855 END IF;
856
857 ';
858 curr_index := 1;
859 body_len := LENGTH (l_proc_body);
860
861 WHILE curr_index <= body_len
862 LOOP
863 lines := lines + 1;
864 ad_ddl.build_statement (SUBSTR (l_proc_body, curr_index, 200),
865 lines);
866 curr_index := curr_index + 200;
867 END LOOP;
868
869 l_proc_body :=
870 '
871
872 IF org_tracking_flag = ''Y'' AND secondary_dimension_column IS NOT NULL
873 THEN
874 l_merge_statement :=
875 ''MERGE INTO gcs_entry_lines gel
876 USING (
877 SELECT fb.company_cost_center_org_id,
878 ' ||l_decode_text
879 ||'
880 SUM ( NVL (fb.ytd_credit_balance_e, 0)
881 - NVL (fb.ytd_debit_balance_e, 0)
882 ) amount
883 FROM gcs_dimension_templates gdt, fem_balances fb, fem_ln_items_attr flia,
884 fem_ext_acct_types_attr feata, FEM_SOURCE_SYSTEMS_B fssb
885 WHERE gdt.hierarchy_id = :1
886 AND gdt.template_code = ''''RE''''
887 AND fb.cal_period_id = :2
888 AND fb.line_item_id = flia.line_item_id
889 AND fssb.source_system_display_code = ''''GCS''''
890 AND fb.hierarchy_id = gdt.hierarchy_id
891 AND fb.source_system_code = fssb.source_system_code
892 AND feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
893 AND feata.dim_attribute_numeric_member IS NULL
894 AND flia.value_set_id = ''
895 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id
896 || ''
897 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
898 AND flia.attribute_id = ''
899 || gcs_utility_pkg.g_dimension_attr_info
900 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
901 || ''
902 AND feata.attribute_id = ''
903 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id
904 || ''
905 AND flia.version_id = ''
906 || gcs_utility_pkg.g_dimension_attr_info
907 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
908 || ''
909 AND feata.version_id = ''
910 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id
911 || ''
912 AND fb.line_item_id = flia.line_item_id
913 GROUP BY
914 ' ||l_decode_group_text
915 ||', fb.company_cost_center_org_id ) src
916 ON ( gel.entry_id = :2
917 ' ||l_equal_text
918 ||')
919 WHEN MATCHED THEN
920 UPDATE
921 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * -src.amount,
922 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * -src.amount,
923 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
924 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
925 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
926 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
927 gel.last_update_date = SYSDATE,
928 gel.last_updated_by = :3
929 WHEN NOT MATCHED THEN
930 INSERT (entry_id, line_type_code, description, '||l_gel_dims_text
931 ||'gel.xtd_balance_e, gel.ytd_balance_e,
932 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
933 gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
934 creation_date, created_by, last_update_date, last_updated_by,
935 last_update_login)
936 VALUES (:2, ''''CALCULATED'''', ''''RE_LINE'''', '||l_src_dims_text
937 ||' -src.amount * '' || sign_value || '', -src.amount * '' || sign_value || '',
938 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
939 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
940 SYSDATE, :3, SYSDATE, :3, :4)
941 '';
942
943 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
944 THEN
945 fnd_log.STRING (fnd_log.level_statement,
946 g_pkg_name || ''.'' || l_api_name,
947 ''l_merge_statement = ''|| l_merge_statement
948 );
949 END IF;
950
951 EXECUTE IMMEDIATE l_merge_statement
952 USING '|| l_bind_vars_text
953 ||' p_hierarchy_id,
954 p_pre_cal_period_id,
955 '|| l_bind_vars_text
956 ||' p_entry_id,
957 l_intercompany_id,
958 fnd_global.user_id,
959 p_entry_id,
960 l_intercompany_id,
961 fnd_global.user_id,
962 fnd_global.user_id,
963 fnd_global.login_id;
964 ELSIF secondary_dimension_column IS NOT NULL
965 THEN
966 l_merge_statement :=
967 ''MERGE INTO gcs_entry_lines gel
968 USING (
969 SELECT :6 company_cost_center_org_id,
970 ' ||l_decode_text
971 ||'
972 SUM ( NVL (fb.ytd_credit_balance_e, 0)
973 - NVL (fb.ytd_debit_balance_e, 0)
974 ) amount
975 FROM gcs_dimension_templates gdt, fem_balances fb, fem_ln_items_attr flia,
976 fem_ext_acct_types_attr feata, FEM_SOURCE_SYSTEMS_B fssb
977 WHERE gdt.hierarchy_id = :1
978 AND gdt.template_code = ''''RE''''
979 AND fb.cal_period_id = :2
980 AND fb.line_item_id = flia.line_item_id
981 AND fssb.source_system_display_code = ''''GCS''''
982 AND fb.hierarchy_id = gdt.hierarchy_id
983 AND fb.source_system_code = fssb.source_system_code
984 AND feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
985 AND feata.dim_attribute_numeric_member IS NULL
986 AND flia.value_set_id = ''
987 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id
988 || ''
989 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
990 AND flia.attribute_id = ''
991 || gcs_utility_pkg.g_dimension_attr_info
992 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
993 || ''
994 AND feata.attribute_id = ''
995 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id
996 || ''
997 AND flia.version_id = ''
998 || gcs_utility_pkg.g_dimension_attr_info
999 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
1000 || ''
1001 AND feata.version_id = ''
1002 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id
1003 || ''
1004 AND fb.line_item_id = flia.line_item_id
1005 GROUP BY
1006 ' ||l_decode_group_text
1007 ||') src
1008 ON ( gel.entry_id = :2
1009 ' ||l_equal_text
1010 ||')
1011 WHEN MATCHED THEN
1012 UPDATE
1013 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * -src.amount,
1014 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * -src.amount,
1015 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
1016 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
1017 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
1018 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
1019 gel.last_update_date = SYSDATE,
1020 gel.last_updated_by = :3
1021 WHEN NOT MATCHED THEN
1022 INSERT (entry_id, line_type_code, description, '||l_gel_dims_text
1023 ||' gel.xtd_balance_e, gel.ytd_balance_e,
1024 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
1025 gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
1026 creation_date, created_by, last_update_date, last_updated_by,
1027 last_update_login)
1028 VALUES (:2, ''''CALCULATED'''', ''''RE_LINE'''', '||l_src_dims_text
1029 ||' -src.amount * '' || sign_value || '', -src.amount * '' || sign_value || '',
1030 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
1031 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
1032 SYSDATE, :3, SYSDATE, :3, :4)
1033 '';
1034
1035 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1036 THEN
1037 fnd_log.STRING (fnd_log.level_statement,
1038 g_pkg_name || ''.'' || l_api_name,
1039 ''l_merge_statement = ''|| l_merge_statement
1040 );
1041 END IF;
1042
1043 EXECUTE IMMEDIATE l_merge_statement
1044 USING l_org_id,
1045 '|| l_bind_vars_text
1046 ||' p_hierarchy_id,
1047 p_pre_cal_period_id,
1048 '|| l_bind_vars_text
1049 ||' p_entry_id,
1050 l_intercompany_id,
1051 fnd_global.user_id,
1052 p_entry_id,
1053 l_intercompany_id,
1054 fnd_global.user_id,
1055 fnd_global.user_id,
1056 fnd_global.login_id;
1057 ELSIF org_tracking_flag = ''Y''
1058 THEN
1059 l_merge_statement :=
1060 ''MERGE INTO gcs_entry_lines gel
1061 USING (SELECT fb.company_cost_center_org_id, '||l_gdt_dims_text||'
1062 SUM ( NVL (fb.ytd_credit_balance_e, 0)
1063 - NVL (fb.ytd_debit_balance_e, 0)
1064 ) amount
1065 FROM gcs_dimension_templates gdt, fem_balances fb, fem_ln_items_attr flia,
1066 fem_ext_acct_types_attr feata, FEM_SOURCE_SYSTEMS_B fssb
1067 WHERE gdt.hierarchy_id = :1
1068 AND gdt.template_code = ''''RE''''
1069 AND fb.cal_period_id = :2
1070 AND fb.line_item_id = flia.line_item_id
1071 AND fssb.source_system_display_code = ''''GCS''''
1072 AND fb.hierarchy_id = gdt.hierarchy_id
1073 AND fb.source_system_code = fssb.source_system_code
1074 AND feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
1075 AND feata.dim_attribute_numeric_member IS NULL
1076 AND flia.value_set_id = ''
1077 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id
1078 || ''
1079 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
1080 AND flia.attribute_id = ''
1081 || gcs_utility_pkg.g_dimension_attr_info
1082 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
1083 || ''
1084 AND feata.attribute_id = ''
1085 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id
1086 || ''
1087 AND flia.version_id = ''
1088 || gcs_utility_pkg.g_dimension_attr_info
1089 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
1090 || ''
1091 AND feata.version_id = ''
1092 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id
1093 || ''
1094 AND fb.line_item_id = flia.line_item_id
1095 GROUP BY '||l_gdt_dims_text||'fb.company_cost_center_org_id) src
1096 ON ( gel.entry_id = :2
1097 ' ||l_equal_text
1098 ||')
1099 WHEN MATCHED THEN
1100 UPDATE
1101 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * -src.amount,
1102 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * -src.amount,
1103 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
1104 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
1105 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
1106 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
1107 gel.last_update_date = SYSDATE,
1108 gel.last_updated_by = :5
1109 WHEN NOT MATCHED THEN
1110 INSERT (entry_id, line_type_code, description, '||l_gel_dims_text|| 'gel.xtd_balance_e, gel.ytd_balance_e,
1111 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
1112 gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
1113 creation_date, created_by, last_update_date, last_updated_by,
1114 last_update_login)
1115 VALUES (:2, ''''CALCULATED'''', ''''RE_LINE'''', '||l_src_dims_text
1116 ||' -src.amount * '' || sign_value || '', -src.amount * '' || sign_value || '',
1117 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
1118 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
1119 SYSDATE, :3, SYSDATE, :3, :4)
1120 '';
1121
1122 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1123 THEN
1124 fnd_log.STRING (fnd_log.level_statement,
1125 g_pkg_name || ''.'' || l_api_name,
1126 ''l_merge_statement = ''|| l_merge_statement
1127 );
1128 END IF;
1129
1130 EXECUTE IMMEDIATE l_merge_statement
1131 USING p_hierarchy_id,
1132 p_pre_cal_period_id,
1133 p_entry_id,
1134 l_intercompany_id,
1135 fnd_global.user_id,
1136 p_entry_id,
1137 l_intercompany_id,
1138 fnd_global.user_id,
1139 fnd_global.user_id,
1140 fnd_global.login_id;
1141 ELSE
1142 l_merge_statement :=
1143 ''MERGE INTO gcs_entry_lines gel
1144 USING (SELECT :6 company_cost_center_org_id, '||l_gdt_dims_text||'
1145 SUM ( NVL (fb.ytd_credit_balance_e, 0)
1146 - NVL (fb.ytd_debit_balance_e, 0)
1147 ) amount
1148 FROM gcs_dimension_templates gdt, fem_balances fb, fem_ln_items_attr flia,
1149 fem_ext_acct_types_attr feata, FEM_SOURCE_SYSTEMS_B fssb
1150 WHERE gdt.hierarchy_id = :1
1151 AND gdt.template_code = ''''RE''''
1152 AND fb.cal_period_id = :2
1153 AND fb.line_item_id = flia.line_item_id
1154 AND fssb.source_system_display_code = ''''GCS''''
1155 AND fb.hierarchy_id = gdt.hierarchy_id
1156 AND fb.source_system_code = fssb.source_system_code
1157 AND feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
1158 AND feata.dim_attribute_numeric_member IS NULL
1159 AND flia.value_set_id = ''
1160 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id
1161 || ''
1162 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
1163 AND flia.attribute_id = ''
1164 || gcs_utility_pkg.g_dimension_attr_info
1165 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
1166 || ''
1167 AND feata.attribute_id = ''
1168 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id
1169 || ''
1170 AND flia.version_id = ''
1171 || gcs_utility_pkg.g_dimension_attr_info
1172 (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
1173 || ''
1174 AND feata.version_id = ''
1175 || gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id
1176 || ''
1177 AND fb.line_item_id = flia.line_item_id
1178 GROUP BY '||SUBSTR(l_gdt_dims_text, 0, LENGTH(l_gdt_dims_text)-2)||') src
1179 ON ( gel.entry_id = :2
1180 ' ||l_equal_text
1181 ||')
1182 WHEN MATCHED THEN
1183 UPDATE
1184 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * -src.amount,
1185 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * -src.amount,
1186 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
1187 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
1188 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.amount), 1, 0, -src.amount),
1189 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.amount), 1, src.amount, 0),
1190 gel.last_update_date = SYSDATE,
1191 gel.last_updated_by = :5
1192 WHEN NOT MATCHED THEN
1193 INSERT (entry_id, line_type_code, description, '||l_gel_dims_text||'gel.xtd_balance_e, gel.ytd_balance_e,
1194 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
1195 gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
1196 creation_date, created_by, last_update_date, last_updated_by,
1197 last_update_login)
1198 VALUES (:2, ''''CALCULATED'''', ''''RE_LINE'''', '||l_src_dims_text
1199 ||' -src.amount * '' || sign_value || '', -src.amount * '' || sign_value || '',
1200 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
1201 decode(sign(src.amount), 1, 0, -src.amount), decode(sign(src.amount), 1, src.amount, 0),
1202 SYSDATE, :3, SYSDATE, :3, :4)
1203 '';
1204
1205 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1206 THEN
1207 fnd_log.STRING (fnd_log.level_statement,
1208 g_pkg_name || ''.'' || l_api_name,
1209 ''l_merge_statement = ''|| l_merge_statement
1210 );
1211 END IF;
1212
1213 EXECUTE IMMEDIATE l_merge_statement
1214 USING l_org_id,
1215 p_hierarchy_id,
1216 p_pre_cal_period_id,
1217 p_entry_id,
1218 l_intercompany_id,
1219 fnd_global.user_id,
1220 p_entry_id,
1221 l_intercompany_id,
1222 fnd_global.user_id,
1223 fnd_global.user_id,
1224 fnd_global.login_id;
1225 END IF;
1226
1227 EXCEPTION
1228 WHEN gcs_tmp_invalid_sign
1229 THEN
1230 fnd_message.set_name (''GCS'', ''GCS_TMP_INVALID_SIGN'');
1231
1232 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1233 THEN
1234 fnd_log.STRING (fnd_log.level_procedure,
1235 g_pkg_name || ''.'' || l_api_name,
1236 gcs_utility_pkg.g_module_failure
1237 || '' ''
1238 || l_api_name
1239 || ''() ''
1240 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1241 );
1242 END IF;
1243 RAISE gcs_tmp_balancing_failed;
1244 END calculate_dp_re;
1245 ';
1246 curr_index := 1;
1247 body_len := LENGTH (l_proc_body);
1248
1249 WHILE curr_index <= body_len
1250 LOOP
1251 lines := lines + 1;
1252 ad_ddl.build_statement (SUBSTR (l_proc_body, curr_index, 200),
1253 lines);
1254 curr_index := curr_index + 200;
1255 END LOOP;
1256
1257 l_proc_body :=
1258 '
1259 PROCEDURE balance (
1260 p_entry_id NUMBER,
1261 p_template gcs_templates_pkg.templaterecord,
1262 p_bal_type_code VARCHAR2,
1263 p_hierarchy_id NUMBER,
1264 p_entity_id NUMBER,
1265 p_threshold NUMBER DEFAULT 0,
1266 p_threshold_currency_code VARCHAR2 DEFAULT NULL
1267 )
1268 IS
1269 l_merge_statement VARCHAR2 (5000);
1270
1271 -- Used to obtain hierarchy information
1272 CURSOR hierarchy_c
1273 IS
1274 SELECT hb.balance_by_org_flag, hb.column_name
1275 FROM gcs_hierarchies_b hb
1276 WHERE hb.hierarchy_id = p_hierarchy_id;
1277
1278 -- Used to get the category cdoe
1279 CURSOR category_c
1280 IS
1281 SELECT cb.category_code,
1282 cb.category_type_code
1283 FROM gcs_entry_headers eh,
1284 gcs_categories_b cb
1285 WHERE eh.entry_id = p_entry_id
1286 AND cb.category_code = eh.category_code;
1287
1288 org_tracking_flag VARCHAR2 (1);
1289 secondary_dimension_column VARCHAR2 (30);
1290
1291 -- Used to obtain sign information
1292 CURSOR sign_c IS
1293 SELECT fxata.number_assign_value
1294 FROM fem_ln_items_attr flia,
1295 fem_ext_acct_types_attr fxata
1296 WHERE flia.line_item_id = p_template.line_item_id
1297 AND flia.attribute_id = GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id
1298 AND flia.version_id = GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id
1299 AND fxata.ext_account_type_code = flia.dim_attribute_varchar_member
1300 AND fxata.attribute_id = GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').attribute_id
1301 AND fxata.version_id = GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').version_id;
1302
1303 sign_value NUMBER;
1304 l_intercompany_id NUMBER(15);
1305 l_category VARCHAR2(50);
1306 l_category_type VARCHAR2(50);
1307 l_currency_code VARCHAR2(30);
1308 l_cal_period_id NUMBER;
1309 l_errbuf VARCHAR2(4000);
1310 l_errcode VARCHAR2 (50);
1311 l_corp_rate NUMBER;
1312 -- Used to get the org and secondary dimension value IDs to use
1313 -- in the balancing account, and the credit excess amount.
1314 l_org_id NUMBER;
1315 l_threshold_passed_flag VARCHAR2 (1);
1316 l_threshold_amount NUMBER;
1317 l_api_name VARCHAR2 (30) := ''BALANCE'';
1318 l_enforce_balancing_flag VARCHAR2(1); -- Bug 5085697 : SMATAM
1319 BEGIN
1320 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1321 THEN
1322 fnd_log.STRING (fnd_log.level_procedure,
1323 g_pkg_name || ''.'' || l_api_name,
1324 gcs_utility_pkg.g_module_enter
1325 || '' ''
1326 || l_api_name
1327 || ''() ''
1328 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1329 );
1330 END IF;
1331 -- Bug 5085697 : start : SMATAM
1332 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
1333 fnd_log.STRING(fnd_log.level_statement,
1334 g_pkg_name || ''.'' || l_api_name,
1335 ''SELECT enforce_balancing_flag'' || g_nl ||
1336 ''FROM gcs_data_type_codes_b '' || g_nl ||
1337 ''WHERE data_type_code = '' || p_bal_type_code);
1338 END IF;
1339 SELECT enforce_balancing_flag
1340 INTO l_enforce_balancing_flag
1341 FROM gcs_data_type_codes_b
1342 WHERE data_type_code = p_bal_type_code;
1343
1344 IF (l_enforce_balancing_flag IS NULL OR l_enforce_balancing_flag = ''N'') THEN
1345 --Log that no balancing is needed
1346 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
1347 fnd_log.STRING(fnd_log.level_statement,
1348 g_pkg_name || ''.'' || l_api_name,
1349 ''No Balancing is required for the balance_type_code, '' || p_bal_type_code);
1350 END IF;
1351 return;
1352 END IF;
1353
1354 -- Bug 5085697 : End : SMATAM
1355 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1356 THEN
1357 fnd_log.STRING (fnd_log.level_statement,
1358 g_pkg_name || ''.'' || l_api_name,
1359 ''SELECT hb.balance_by_org_flag, hb.column_name''
1360 || g_nl
1361 || ''FROM gcs_hierarchies_b hb ''
1362 || g_nl
1363 || ''WHERE hb.hierarchy_id = ''
1364 || p_hierarchy_id
1365 );
1366 END IF;
1367
1368 -- Get org tracking and secondary tracking information.
1369 OPEN hierarchy_c;
1370
1371 FETCH hierarchy_c
1372 INTO org_tracking_flag, secondary_dimension_column;
1373
1374 IF hierarchy_c%NOTFOUND
1375 THEN
1376 CLOSE hierarchy_c;
1377
1378 RAISE gcs_tmp_invalid_hierarchy;
1379 END IF;
1380
1381 CLOSE hierarchy_c;
1382
1383 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1384 THEN
1385 fnd_log.STRING (fnd_log.level_statement,
1386 g_pkg_name || ''.'' || l_api_name,
1387 ''SELECT category_code''
1388 || g_nl
1389 || ''FROM gcs_entry_headers ''
1390 || g_nl
1391 || ''WHERE entry_id = ''
1392 || p_entry_id
1393 );
1394 END IF;
1395
1396 -- bug fix 3797306
1397 -- Get category code
1398 OPEN category_c;
1399
1400 FETCH category_c
1401 INTO l_category, l_category_type;
1402 CLOSE category_c;
1403
1404 -- end of bug fix 3797306
1405
1406 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1407 THEN
1408 fnd_log.STRING (fnd_log.level_statement,
1409 g_pkg_name || ''.'' || l_api_name,
1410 ''SELECT specific_intercompany_id FROM gcs_hierarchies_b ''
1411 || '' WHERE hierarchy_id = '' || p_hierarchy_id
1412 || '' AND INTERCOMPANY_ORG_TYPE_CODE = ''''SPECIFIC_VALUE'''''');
1413 END IF;
1414
1415 -- Get specific intercompany_id, if null, using orgs
1416 OPEN intercompany_c;
1417
1418 FETCH intercompany_c
1419 INTO l_intercompany_id;
1420
1421 CLOSE intercompany_c;
1422
1423 -- Get the signage of the suspense line item
1424 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1425 THEN
1426 fnd_log.STRING (fnd_log.level_statement,
1427 g_pkg_name || ''.'' || l_api_name,
1428 ''SELECT fxata.number_assign_value'' || g_nl ||
1429 ''FROM fem_ln_items_attr flia,'' || g_nl ||
1430 '' fem_ext_acct_types_attr fxata'' || g_nl ||
1431 ''WHERE flia.line_item_id = '' || p_template.line_item_id || g_nl ||
1432 ''AND flia.attribute_id = '' ||
1433 GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id || g_nl ||
1434 ''AND flia.version_id = '' ||
1435 GCS_UTILITY_PKG.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id || g_nl ||
1436 ''AND fxata.ext_account_type_code = flia.dim_attribute_varchar_member'' || g_nl ||
1437 ''AND fxata.attribute_id = '' ||
1438 GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').attribute_id || g_nl ||
1439 ''AND fxata.version_id = '' ||
1440 GCS_UTILITY_PKG.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-SIGN'').version_id
1441 );
1442 END IF;
1443
1444 -- Get signage information
1445 OPEN sign_c;
1446 FETCH sign_c INTO sign_value;
1447 IF sign_c%NOTFOUND
1448 THEN
1449 CLOSE sign_c;
1450 RAISE gcs_tmp_invalid_sign;
1451 END IF;
1452 CLOSE sign_c;
1453
1454 l_threshold_amount := p_threshold;
1455 IF p_threshold_currency_code IS NOT NULL
1456 THEN
1457 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1458 THEN
1459 fnd_log.STRING (fnd_log.level_statement,
1460 g_pkg_name || ''.'' || l_api_name,
1461 '' select start_cal_period_id, currency_code
1462 into l_cal_period_id, l_currency_code
1463 from gcs_entry_headers
1464 where entry_id = '' || p_entry_id
1465 );
1466 END IF;
1467
1468 select start_cal_period_id, currency_code
1469 into l_cal_period_id, l_currency_code
1470 from gcs_entry_headers
1471 where entry_id = p_entry_id;
1472
1473 IF l_currency_code <> p_threshold_currency_code THEN
1474
1475 GCS_UTILITY_PKG.Get_Conversion_Rate
1476 (p_source_currency => p_threshold_currency_code,
1477 p_target_currency => l_currency_code,
1478 p_cal_period_id => l_cal_period_id,
1479 p_conversion_rate => l_corp_rate,
1480 P_errbuf => l_errbuf,
1481 p_errcode => l_errcode);
1482
1483 l_threshold_amount := l_corp_rate * p_threshold;
1484 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1485 THEN
1486 fnd_log.STRING (fnd_log.level_statement,
1487 g_pkg_name || ''.'' || l_api_name,
1488 ''p_threshold = ''||p_threshold||'', l_corp_rate = '' || l_corp_rate
1489 || '', l_threshold_amount = '' || l_threshold_amount
1490 );
1491 END IF;
1492 END IF;
1493 END IF;
1494
1495
1496 IF org_tracking_flag = ''N''
1497 THEN
1498 l_org_id := gcs_utility_pkg.Get_Org_Id(p_entity_id => p_entity_id,
1499 p_hierarchy_id => p_hierarchy_id);
1500 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1501 THEN
1502 fnd_log.STRING (fnd_log.level_statement,
1503 g_pkg_name || ''.'' || l_api_name,
1504 ''l_org_id = '' || l_org_id
1505 );
1506 END IF;
1507 END IF;
1508
1509
1510 IF org_tracking_flag = ''Y'' AND secondary_dimension_column IS NOT NULL
1511 THEN
1512 l_merge_statement :=
1513 ''MERGE INTO gcs_entry_lines gel
1514 USING (
1515 SELECT gel_1.company_cost_center_org_id,
1516 ' ||l_bal_decode_text
1517 ||'
1518 SUM ( NVL (gel_1.ytd_credit_balance_e, 0)
1519 - NVL (gel_1.ytd_debit_balance_e, 0)
1520 ) ytd_amount,
1521 SUM ( NVL (gel_1.ptd_credit_balance_e, 0)
1522 - NVL (gel_1.ptd_debit_balance_e, 0)
1523 ) ptd_amount
1524 FROM gcs_entry_lines gel_1
1525 WHERE gel_1.entry_id = :2
1526 GROUP BY gel_1.company_cost_center_org_id, ''||secondary_dimension_column||''
1527 HAVING SUM ( NVL (gel_1.ytd_credit_balance_e, 0) - NVL (gel_1.ytd_debit_balance_e, 0) ) <>0 ) src
1528 ON ( gel.entry_id = :2
1529 ' ||l_equal_text
1530 ||')
1531 WHEN MATCHED THEN
1532 UPDATE
1533 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * src.ytd_amount,
1534 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.ytd_amount), -1, 0, src.ytd_amount),
1535 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.ytd_amount), -1, -src.ytd_amount, 0),'';
1536
1537 IF l_category_type <> ''CONSOLIDATION_RULE'' THEN
1538 l_merge_statement := l_merge_statement || ''
1539 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * src.ptd_amount,
1540 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.ptd_amount), -1, 0, src.ptd_amount),
1541 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.ptd_amount), -1, -src.ptd_amount, 0),'';
1542 ELSE
1543 l_merge_statement := l_merge_statement || ''
1544 gel.xtd_balance_e = null,
1545 gel.ptd_debit_balance_e = null,
1546 gel.ptd_credit_balance_e = null,'';
1547 END IF;
1548
1549 l_merge_statement := l_merge_statement || ''
1550 gel.last_update_date = SYSDATE,
1551 gel.description = decode(sign(abs(gel.ytd_balance_e + src.ytd_amount)-nvl(:11, 0)), 1, decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_EXCEEDED''''),
1552 decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_LINE'''')),
1553 gel.last_updated_by = :3
1554 WHEN NOT MATCHED THEN
1555 INSERT (entry_id, description, '||l_gel_dims_text
1556 ||' gel.xtd_balance_e,
1557 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
1558 gel.ytd_balance_e, gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
1559 creation_date, created_by, last_update_date, last_updated_by,
1560 last_update_login)
1561 VALUES (:2, decode(sign(abs(src.ytd_amount)-nvl(:11,0)), 1, decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_EXCEEDED''''),
1562 decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_LINE'''')), '||l_src_dims_text
1563 ||' '';
1564
1565 IF l_category_type <> ''CONSOLIDATION_RULE'' THEN
1566 l_merge_statement := l_merge_statement || ''
1567 src.ptd_amount * '' || sign_value || '',
1568 decode(sign(src.ptd_amount), -1, 0, src.ptd_amount), decode(sign(src.ptd_amount), -1, -src.ptd_amount, 0),'';
1569 ELSE
1570 l_merge_statement := l_merge_statement || ''
1571 null,
1572 null, null,'';
1573 END IF;
1574
1575 l_merge_statement := l_merge_statement || ''
1576 src.ytd_amount * '' || sign_value || '',
1577 decode(sign(src.ytd_amount), -1, 0, src.ytd_amount), decode(sign(src.ytd_amount), -1, -src.ytd_amount, 0),
1578 SYSDATE, :3, SYSDATE, :3, :4)
1579 '';
1580
1581 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1582 THEN
1583 fnd_log.STRING (fnd_log.level_statement,
1584 g_pkg_name || ''.'' || l_api_name,
1585 ''l_merge_statement = ''|| l_merge_statement
1586 );
1587 END IF;
1588
1589 EXECUTE IMMEDIATE l_merge_statement
1590 USING '|| l_bal_bind_vars_text
1591 ||' p_entry_id,
1592 p_entry_id,
1593 l_intercompany_id,
1594 l_threshold_amount,
1595 l_category,
1596 l_category,
1597 fnd_global.user_id,
1598 p_entry_id,
1599 l_threshold_amount,
1600 l_category,
1601 l_category,
1602 l_intercompany_id,
1603 fnd_global.user_id,
1604 fnd_global.user_id,
1605 fnd_global.login_id;
1606 ';
1607 curr_index := 1;
1608 body_len := LENGTH (l_proc_body);
1609
1610 WHILE curr_index <= body_len
1611 LOOP
1612 lines := lines + 1;
1613 ad_ddl.build_statement (SUBSTR (l_proc_body, curr_index, 200),
1614 lines);
1615 curr_index := curr_index + 200;
1616 END LOOP;
1617
1618 l_proc_body :=
1619 '
1620 ELSIF secondary_dimension_column IS NOT NULL
1621 THEN
1622 l_merge_statement :=
1623 ''MERGE INTO gcs_entry_lines gel
1624 USING (
1625 SELECT :6 company_cost_center_org_id,
1626 ' ||l_bal_decode_text
1627 ||'
1628 SUM ( NVL (gel_1.ytd_credit_balance_e, 0)
1629 - NVL (gel_1.ytd_debit_balance_e, 0)) ytd_amount,
1630 SUM ( NVL (gel_1.ptd_credit_balance_e, 0)
1631 - NVL (gel_1.ptd_debit_balance_e, 0)) ptd_amount
1632 FROM gcs_entry_lines gel_1
1633 WHERE gel_1.entry_id = :2
1634 GROUP BY ''||secondary_dimension_column||''
1635 HAVING SUM ( NVL (gel_1.ytd_credit_balance_e, 0) - NVL (gel_1.ytd_debit_balance_e, 0) ) <>0 ) src
1636 ON ( gel.entry_id = :2
1637 ' ||l_equal_text
1638 ||')
1639 WHEN MATCHED THEN
1640 UPDATE
1641 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * src.ytd_amount,
1642 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.ytd_amount), -1, 0, src.ytd_amount),
1643 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.ytd_amount), -1, -src.ytd_amount, 0),'';
1644
1645 IF l_category_type <> ''CONSOLIDATION_RULE'' THEN
1646 l_merge_statement := l_merge_statement || ''
1647 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * src.ptd_amount,
1648 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.ptd_amount), -1, 0, src.ptd_amount),
1649 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.ptd_amount), -1, -src.ptd_amount, 0),'';
1650 ELSE
1651 l_merge_statement := l_merge_statement || ''
1652 gel.xtd_balance_e = null,
1653 gel.ptd_debit_balance_e = null,
1654 gel.ptd_credit_balance_e = null,'';
1655 END IF;
1656
1657 l_merge_statement := l_merge_statement || ''
1658 gel.last_update_date = SYSDATE,
1659 gel.description = decode(sign(abs(gel.ytd_balance_e + src.ytd_amount)-nvl(:11, 0)), 1, decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_EXCEEDED''''),
1660 decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_LINE'''')),
1661 gel.last_updated_by = :3
1662 WHEN NOT MATCHED THEN
1663 INSERT (entry_id, description, '||l_gel_dims_text
1664 ||' gel.xtd_balance_e,
1665 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
1666 gel.ytd_balance_e, gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
1667 creation_date, created_by, last_update_date, last_updated_by,
1668 last_update_login)
1669 VALUES (:2, decode(sign(abs(src.ytd_amount)-nvl(:11, 0)), 1, decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_EXCEEDED''''),
1670 decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_LINE'''')), '||l_src_dims_text
1671 ||' '';
1672
1673 IF l_category_type <> ''CONSOLIDATION_RULE'' THEN
1674 l_merge_statement := l_merge_statement || ''
1675 src.ptd_amount * '' || sign_value || '', src.ytd_amount * '' || sign_value || '',
1676 decode(sign(src.ptd_amount), -1, 0, src.ptd_amount), decode(sign(src.ptd_amount), -1, -src.ptd_amount, 0),'';
1677 ELSE
1678 l_merge_statement := l_merge_statement || ''
1679 null,
1680 null, null,'';
1681 END IF;
1682
1683 --Bugfix 6193096: Merge Statement is incorrectly defined
1684 l_merge_statement := l_merge_statement || ''
1685 src.ytd_amount * '' || sign_value || '',
1686 decode(sign(src.ytd_amount), -1, 0, src.ytd_amount), decode(sign(src.ytd_amount), -1, -src.ytd_amount, 0),
1687 SYSDATE, :3, SYSDATE, :3, :4)
1688 '';
1689
1690 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1691 THEN
1692 fnd_log.STRING (fnd_log.level_statement,
1693 g_pkg_name || ''.'' || l_api_name,
1694 ''l_merge_statement = ''|| l_merge_statement
1695 );
1696 END IF;
1697
1698 EXECUTE IMMEDIATE l_merge_statement
1699 USING l_org_id,
1700 '|| l_bal_bind_vars_text
1701 ||' p_entry_id,
1702 p_entry_id,
1703 l_intercompany_id,
1704 l_threshold_amount,
1705 l_category,
1706 l_category,
1707 fnd_global.user_id,
1708 p_entry_id,
1709 l_threshold_amount,
1710 l_category,
1711 l_category,
1712 l_intercompany_id,
1713 fnd_global.user_id,
1714 fnd_global.user_id,
1715 fnd_global.login_id;
1716 ELSIF org_tracking_flag = ''Y''
1717 THEN
1718 l_merge_statement :=
1719 ''MERGE INTO gcs_entry_lines gel
1720 USING (SELECT gel_1.company_cost_center_org_id, '||l_bind_dims_text
1721 ||' SUM ( NVL (gel_1.ytd_credit_balance_e, 0)
1722 - NVL (gel_1.ytd_debit_balance_e, 0)
1723 ) ytd_amount,
1724 SUM ( NVL (gel_1.ptd_credit_balance_e, 0)
1725 - NVL (gel_1.ptd_debit_balance_e, 0)
1726 ) ptd_amount
1727 FROM gcs_entry_lines gel_1
1728 WHERE gel_1.entry_id = :2
1729 GROUP BY company_cost_center_org_id
1730 HAVING SUM ( NVL (gel_1.ytd_credit_balance_e, 0) - NVL (gel_1.ytd_debit_balance_e, 0) ) <>0 ) src
1731 ON ( gel.entry_id = :2
1732 ' ||l_equal_text
1733 ||')
1734 WHEN MATCHED THEN
1735 UPDATE
1736 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * src.ytd_amount,
1737 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.ytd_amount), -1, 0, src.ytd_amount),
1738 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.ytd_amount), -1, -src.ytd_amount, 0),'';
1739
1740 IF l_category_type <> ''CONSOLIDATION_RULE'' THEN
1741 l_merge_statement := l_merge_statement || ''
1742 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * src.ptd_amount,
1743 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.ptd_amount), -1, 0, src.ptd_amount),
1744 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.ptd_amount), -1, -src.ptd_amount, 0),'';
1745 ELSE
1746 l_merge_statement := l_merge_statement || ''
1747 gel.xtd_balance_e = null,
1748 gel.ptd_debit_balance_e = null,
1749 gel.ptd_credit_balance_e = null,'';
1750 END IF;
1751
1752 l_merge_statement := l_merge_statement || ''
1753 gel.last_update_date = SYSDATE,
1754 gel.description = decode(sign(abs(gel.ytd_balance_e + src.ytd_amount)-nvl(:11, 0)), 1, decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_EXCEEDED''''),
1755 decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_LINE'''')),
1756 gel.last_updated_by = :5
1757 WHEN NOT MATCHED THEN
1758 INSERT (entry_id, description, '||l_gel_dims_text
1759 ||' gel.xtd_balance_e,
1760 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
1761 gel.ytd_balance_e, gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
1762 creation_date, created_by, last_update_date, last_updated_by,
1763 last_update_login)
1764 VALUES (:2, decode(sign(abs(src.ytd_amount)-nvl(:11, 0)), 1, decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_EXCEEDED''''),
1765 decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_LINE'''')), '||l_src_dims_text
1766 ||' '';
1767
1768 IF l_category_type <> ''CONSOLIDATION_RULE'' THEN
1769 l_merge_statement := l_merge_statement || ''
1770 src.ptd_amount * '' || sign_value || '',
1771 decode(sign(src.ptd_amount), -1, 0, src.ptd_amount), decode(sign(src.ptd_amount), -1, -src.ptd_amount, 0),'';
1772 ELSE
1773 l_merge_statement := l_merge_statement || ''
1774 null,
1775 null, null,'';
1776 END IF;
1777
1778 l_merge_statement := l_merge_statement || ''
1779 src.ytd_amount * '' || sign_value || '',
1780 decode(sign(src.ytd_amount), -1, 0, src.ytd_amount), decode(sign(src.ytd_amount), -1, -src.ytd_amount, 0),
1781 SYSDATE, :3, SYSDATE, :3, :4)
1782 '';
1783
1784 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1785 THEN
1786 fnd_log.STRING (fnd_log.level_statement,
1787 g_pkg_name || ''.'' || l_api_name,
1788 ''l_merge_statement = ''|| l_merge_statement
1789 );
1790 END IF;
1791
1792 EXECUTE IMMEDIATE l_merge_statement
1793 USING '|| l_bind_dims_var_text
1794 ||' p_entry_id,
1795 p_entry_id,
1796 l_intercompany_id,
1797 l_threshold_amount,
1798 l_category,
1799 l_category,
1800 fnd_global.user_id,
1801 p_entry_id,
1802 l_threshold_amount,
1803 l_category,
1804 l_category,
1805 l_intercompany_id,
1806 fnd_global.user_id,
1807 fnd_global.user_id,
1808 fnd_global.login_id;
1809 ';
1810 curr_index := 1;
1811 body_len := LENGTH (l_proc_body);
1812
1813 WHILE curr_index <= body_len
1814 LOOP
1815 lines := lines + 1;
1816 ad_ddl.build_statement (SUBSTR (l_proc_body, curr_index, 200),
1817 lines);
1818 curr_index := curr_index + 200;
1819 END LOOP;
1820
1821 l_proc_body :=
1822 '
1823 ELSE
1824 l_merge_statement :=
1825 ''MERGE INTO gcs_entry_lines gel
1826 USING (SELECT :4 company_cost_center_org_id, '||l_bind_dims_text
1827 ||'
1828 SUM ( NVL (gel_1.ytd_credit_balance_e, 0)
1829 - NVL (gel_1.ytd_debit_balance_e, 0)
1830 ) ytd_amount,
1831 SUM ( NVL (gel_1.ptd_credit_balance_e, 0)
1832 - NVL (gel_1.ptd_debit_balance_e, 0)
1833 ) ptd_amount
1834 FROM gcs_entry_lines gel_1
1835 WHERE gel_1.entry_id = :2
1836 HAVING SUM ( NVL (gel_1.ytd_credit_balance_e, 0) - NVL (gel_1.ytd_debit_balance_e, 0) ) <>0 ) src
1837 ON ( gel.entry_id = :2
1838 ' ||l_equal_text
1839 ||')
1840 WHEN MATCHED THEN
1841 UPDATE
1842 SET gel.ytd_balance_e = gel.ytd_balance_e + '' || sign_value || '' * src.ytd_amount,
1843 gel.ytd_debit_balance_e = gel.ytd_debit_balance_e + decode(sign(src.ytd_amount), -1, 0, src.ytd_amount),
1844 gel.ytd_credit_balance_e = gel.ytd_credit_balance_e + decode(sign(src.ytd_amount), -1, -src.ytd_amount, 0),'';
1845
1846 IF l_category_type <> ''CONSOLIDATION_RULE'' THEN
1847 l_merge_statement := l_merge_statement || ''
1848 gel.xtd_balance_e = gel.xtd_balance_e + '' || sign_value || '' * src.ptd_amount,
1849 gel.ptd_debit_balance_e = gel.ptd_debit_balance_e + decode(sign(src.ptd_amount), -1, 0, src.ptd_amount),
1850 gel.ptd_credit_balance_e = gel.ptd_credit_balance_e + decode(sign(src.ptd_amount), -1, -src.ptd_amount, 0),'';
1851 ELSE
1852 l_merge_statement := l_merge_statement || ''
1853 gel.xtd_balance_e = null,
1854 gel.ptd_debit_balance_e = null,
1855 gel.ptd_credit_balance_e = null,'';
1856 END IF;
1857
1858 l_merge_statement := l_merge_statement || ''
1859 gel.last_update_date = SYSDATE,
1860 gel.description = decode(sign(abs(gel.ytd_balance_e + src.ytd_amount)-nvl(:11, 0)), 1, decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_EXCEEDED''''),
1861 decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_LINE'''')),
1862 gel.last_updated_by = :5
1863 WHEN NOT MATCHED THEN
1864 INSERT (entry_id, description, '||l_gel_dims_text
1865 ||' gel.xtd_balance_e,
1866 gel.ptd_debit_balance_e, gel.ptd_credit_balance_e,
1867 gel.ytd_balance_e, gel.ytd_debit_balance_e, gel.ytd_credit_balance_e,
1868 creation_date, created_by, last_update_date, last_updated_by,
1869 last_update_login)
1870 VALUES (:2, decode(sign(abs(src.ytd_amount)-nvl(:11,0)), 1, decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_EXCEEDED''''),
1871 decode(:CATEGORY, ''''TRANSLATION'''', ''''CTA_LINE'''', ''''SUSPENSE_LINE'''')), '||l_src_dims_text
1872 ||' '';
1873
1874 IF l_category_type <> ''CONSOLIDATION_RULE'' THEN
1875 l_merge_statement := l_merge_statement || ''
1876 src.ptd_amount * '' || sign_value || '',
1877 decode(sign(src.ptd_amount), -1, 0, src.ptd_amount), decode(sign(src.ptd_amount), -1, -src.ptd_amount, 0),'';
1878 ELSE
1879 l_merge_statement := l_merge_statement || ''
1880 null,
1881 null, null,'';
1882 END IF;
1883
1884 l_merge_statement := l_merge_statement || ''
1885 src.ytd_amount * '' || sign_value || '',
1886 decode(sign(src.ytd_amount), -1, 0, src.ytd_amount), decode(sign(src.ytd_amount), -1, -src.ytd_amount, 0),
1887 SYSDATE, :3, SYSDATE, :3, :4)
1888 '';
1889
1890 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1891 THEN
1892 fnd_log.STRING (fnd_log.level_statement,
1893 g_pkg_name || ''.'' || l_api_name,
1894 ''l_merge_statement = ''|| l_merge_statement
1895 );
1896 END IF;
1897
1898 EXECUTE IMMEDIATE l_merge_statement
1899 USING l_org_id,
1900 '|| l_bind_dims_var_text
1901 ||' p_entry_id,
1902 p_entry_id,
1903 l_intercompany_id,
1904 l_threshold_amount,
1905 l_category,
1906 l_category,
1907 fnd_global.user_id,
1908 p_entry_id,
1909 l_threshold_amount,
1910 l_category,
1911 l_category,
1912 l_intercompany_id,
1913 fnd_global.user_id,
1914 fnd_global.user_id,
1915 fnd_global.login_id;
1916 END IF;
1917
1918 BEGIN
1919 SELECT ''Y''
1920 INTO l_threshold_passed_flag
1921 FROM dual
1922 WHERE EXISTS(
1923 SELECT ''X''
1924 FROM gcs_entry_lines
1925 WHERE entry_id = p_entry_id
1926 AND description = ''SUSPENSE_EXCEEDED'');
1927
1928 UPDATE gcs_entry_lines
1929 SET description = ''SUSPENSE_LINE''
1930 WHERE description = ''SUSPENSE_EXCEEDED''
1931 AND entry_id = p_entry_id;
1932
1933 EXCEPTION
1934 WHEN no_data_found THEN
1935 null;
1936 END;
1937
1938 IF l_threshold_passed_flag = ''Y'' THEN
1939 UPDATE gcs_entry_headers
1940 SET suspense_exceeded_flag = ''Y''
1941 WHERE entry_id = p_entry_id;
1942 END IF;
1943
1944 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1945 THEN
1946 fnd_log.STRING (fnd_log.level_procedure,
1947 g_pkg_name || ''.'' || l_api_name,
1948 gcs_utility_pkg.g_module_success
1949 || '' ''
1950 || l_api_name
1951 || ''() ''
1952 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1953 );
1954 END IF;
1955
1956 EXCEPTION
1957 WHEN gcs_tmp_invalid_hierarchy
1958 THEN
1959 fnd_message.set_name (''GCS'', ''GCS_TMP_NO_HIERARCHY'');
1960 fnd_message.set_token (''ENTRY_ID'', p_entry_id);
1961
1962 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1963 THEN
1964 fnd_log.STRING (fnd_log.level_procedure,
1965 g_pkg_name || ''.'' || l_api_name,
1966 gcs_utility_pkg.g_module_failure
1967 || '' ''
1968 || l_api_name
1969 || ''() ''
1970 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1971 );
1972 END IF;
1973
1974 RAISE gcs_tmp_balancing_failed;
1975 WHEN gcs_tmp_invalid_sign
1976 THEN
1977 fnd_message.set_name (''GCS'', ''GCS_TMP_INVALID_SIGN'');
1978
1979 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1980 THEN
1981 fnd_log.STRING (fnd_log.level_procedure,
1982 g_pkg_name || ''.'' || l_api_name,
1983 gcs_utility_pkg.g_module_failure
1984 || '' ''
1985 || l_api_name
1986 || ''() ''
1987 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1988 );
1989 END IF;
1990
1991 RAISE gcs_tmp_balancing_failed;
1992 WHEN OTHERS
1993 THEN
1994 fnd_file.put_line (fnd_file.LOG, SQLERRM);
1995 fnd_message.set_name (''GCS'', ''GCS_TMP_UNEXPECTED'');
1996 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1997 THEN
1998 fnd_log.STRING (fnd_log.level_procedure,
1999 g_pkg_name || ''.'' || l_api_name,
2000 gcs_utility_pkg.g_module_failure
2001 || '' ''
2002 || l_api_name
2003 || ''() ''
2004 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2005 );
2006 END IF;
2007
2008
2009 RAISE gcs_tmp_balancing_failed;
2010 END balance;
2011 END gcs_templates_dynamic_pkg;
2012 ';
2013 curr_index := 1;
2014 body_len := LENGTH (l_proc_body);
2015
2016 WHILE curr_index <= body_len
2017 LOOP
2018 lines := lines + 1;
2019 ad_ddl.build_statement (SUBSTR (l_proc_body, curr_index, 200),
2020 lines);
2021 curr_index := curr_index + 200;
2022 END LOOP;
2023
2024 ad_ddl.create_plsql_object (applsys_schema => GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
2025 application_short_name => 'GCS',
2026 object_name => 'GCS_TEMPLATES_DYNAMIC_PKG',
2027 lb => 1,
2028 ub => lines,
2029 insert_newlines => 'FALSE',
2030 comp_error => err
2031 );
2032
2033
2034 EXCEPTION
2035 WHEN OTHERS
2036 THEN
2037 ROLLBACK TO create_start;
2038 fnd_message.set_name ('GCS', 'GCS_TMP_UNEXP_ERR');
2039
2040 END create_dynamic_pkg;
2041
2042 --
2043 -- Public Procedures
2044 --
2045 PROCEDURE get_dimension_template (
2046 p_hierarchy_id NUMBER,
2047 p_template_code VARCHAR2,
2048 p_balance_type_code VARCHAR2,
2049 p_template_record OUT NOCOPY templaterecord
2050 )
2051 IS
2052 fn_name VARCHAR2(30);
2053
2054 CURSOR get_template
2055 IS
2056 SELECT financial_elem_id, product_id, natural_account_id,
2057 channel_id, line_item_id, project_id, customer_id, task_id,
2058 user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id,
2059 user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id,
2060 user_dim9_id, user_dim10_id
2061 FROM gcs_dimension_templates
2062 WHERE hierarchy_id = p_hierarchy_id
2063 AND template_code = p_template_code;
2064 BEGIN
2065 fn_name := 'GET_DIMENSION_TEMPLATE';
2066 OPEN get_template;
2067
2068 FETCH get_template
2069 INTO p_template_record;
2070
2071 CLOSE get_template;
2072
2073 IF (p_balance_type_code = 'ADB')
2074 THEN
2075 p_template_record.financial_elem_id :=
2076 gcs_utility_pkg.g_avg_fin_elem;
2077 END IF;
2078
2079 EXCEPTION
2080 WHEN OTHERS
2081 THEN
2082 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected)
2083 THEN
2084 fnd_log.STRING (fnd_log.level_unexpected,
2085 g_pkg_name || '.' || fn_name,
2086 SUBSTR (SQLERRM, 1, 255)
2087 );
2088 END IF;
2089
2090 RAISE;
2091 END get_dimension_template;
2092
2093 END gcs_templates_pkg;
2094