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