1 PACKAGE BODY GCS_PERIOD_INIT_DYNAMIC_PKG AS
2 /* $Header: gcspinbb.pls 120.5 2006/09/08 00:30:34 skamdar noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 g_api VARCHAR2(40) := 'gcs.plsql.GCS_PERIOD_INIT_DYNAMIC_PKG';
8 g_li_eat_attr_id NUMBER := GCS_UTILITY_PKG.g_dimension_attr_info
9 ('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').attribute_id;
10 g_li_eat_ver_id NUMBER := GCS_UTILITY_PKG.g_dimension_attr_info
11 ('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').version_id;
12 g_acct_type_attr_id NUMBER := GCS_UTILITY_PKG.g_dimension_attr_info
13 ('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').attribute_id;
14 g_acct_type_ver_id NUMBER := GCS_UTILITY_PKG.g_dimension_attr_info
15 ('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').version_id;
16
17
18 --
19 -- PUBLIC FUNCTIONS
20 --
21
22 PROCEDURE insert_entry_lines(
23 p_run_name VARCHAR2,
24 p_hierarchy_id NUMBER,
25 p_entity_id NUMBER,
26 p_currency_code VARCHAR2,
27 p_bal_by_org VARCHAR2,
28 p_sec_track_col VARCHAR2,
29 p_is_elim_entity VARCHAR2,
30 p_cons_entity_id NUMBER,
31 p_re_template GCS_TEMPLATES_PKG.TemplateRecord,
32 p_cross_year_flag VARCHAR2,
33 p_category_code VARCHAR2,
34 p_init_entry_id NUMBER,
35 p_init_xlate_entry_id NUMBER,
36 p_init_stat_entry_id NUMBER,
37 p_recur_entry_id NUMBER,
38 p_recur_xlate_entry_id NUMBER,
39 p_recur_stat_entry_id NUMBER,
40 --Bugfix 5449718: Added the calendar period year and net to re flag as parameters
41 p_cal_period_year NUMBER,
42 p_net_to_re_flag VARCHAR2)
43 IS
44 fn_name VARCHAR2(30) := 'INSERT_ENTRY_LINES';
45 l_default_org_id NUMBER;
46 l_intercompany_id NUMBER;
47
48 --Bugfix 5449718: Add two lists to store recurring entries. List 1 will store recurring entries where the RE has not yet rolled forward.
49 --List 2 will store entries where RE needs to be rolled forward
50 TYPE r_entry_list IS RECORD (entry_id NUMBER(15),
51 year_to_apply_RE NUMBER(15),
52 currency_code VARCHAR2(30),
53 period_init_entry VARCHAR2(1),
54 diff_in_cal_periods NUMBER );
55 TYPE t_entry_list IS TABLE OF r_entry_list;
56
57 l_entry_id_list t_entry_list;
58 l_recur_entry_id_list t_entry_list := t_entry_list();
59 l_num_recur_entry_id NUMBER(15) := 0;
60 l_entry_list DBMS_SQL.NUMBER_TABLE;
61 l_currency_code_list DBMS_SQL.VARCHAR2_TABLE;
62
63
64 BEGIN
65 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
66 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
67 g_api || '.' || fn_name,
68 GCS_UTILITY_PKG.g_module_enter || fn_name ||
69 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
70 END IF;
71
72 --Bugfix 5449718: Adding information on key parameters
73 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
74 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.'|| fn_name, '<<<<<Begin List of Parameters>>>>>>');
75 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.'|| fn_name, 'Run Name : ' || p_run_name);
76 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.'|| fn_name, 'Consolidation Entity: ' || p_cons_entity_id);
77 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.'|| fn_name, 'Category Code : ' || p_category_code);
78 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.'|| fn_name, 'Cross Year Flag : ' || p_cross_year_flag);
79 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.'|| fn_name, 'Net to RE Flag : ' || p_net_to_re_flag);
80 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.'|| fn_name, '<<<<<End List of Parameters>>>>>>');
81 END IF;
82
83 IF ( p_is_elim_entity = 'Y' ) THEN
84
85 --Bugfix 5449718: Retrieving additional information year_to_apply_re, currency_code
86 --If cross year flag = N then do not select any entries where year to apply RE >= current year
87
88 IF ( p_cross_year_flag = 'N') THEN
89 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
90 geh.year_to_apply_re,
91 geh.currency_code,
92 'N',
93 0
94 BULK COLLECT INTO
95 l_entry_id_list
96 FROM gcs_cons_eng_run_dtls gcerd,
97 gcs_entry_headers geh
98 WHERE gcerd.run_name = p_run_name
99 AND gcerd.consolidation_entity_id = p_cons_entity_id
100 AND gcerd.child_entity_id IS NOT NULL
101 AND gcerd.category_code = p_category_code
102 AND gcerd.entry_id = geh.entry_id
103 AND geh.period_init_entry_flag = 'N'
104 AND p_cal_period_year < NVL(geh.year_to_apply_re, p_cal_period_year+1);
105 ELSIF (p_net_to_re_flag = 'N') THEN
106 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
107 geh.year_to_apply_re,
108 geh.currency_code,
109 'N',
110 0
111 BULK COLLECT INTO
112 l_entry_id_list
113 FROM gcs_cons_eng_run_dtls gcerd,
114 gcs_entry_headers geh
115 WHERE gcerd.run_name = p_run_name
116 AND gcerd.consolidation_entity_id = p_cons_entity_id
117 AND gcerd.child_entity_id IS NOT NULL
118 AND gcerd.category_code = p_category_code
119 AND gcerd.entry_id = geh.entry_id
120 AND geh.period_init_entry_flag = 'N';
121 ELSE
122 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
123 geh.year_to_apply_re,
124 geh.currency_code,
125 geh.period_init_entry_flag,
126 NVL(geh.end_cal_period_id, geh.start_cal_period_id) - geh.start_cal_period_id
127 BULK COLLECT INTO
128 l_entry_id_list
129 FROM gcs_cons_eng_run_dtls gcerd,
130 gcs_entry_headers geh
131 WHERE gcerd.run_name = p_run_name
132 AND gcerd.consolidation_entity_id = p_cons_entity_id
133 AND gcerd.child_entity_id IS NOT NULL
134 AND gcerd.category_code = p_category_code
135 AND gcerd.entry_id = geh.entry_id;
136 END IF;
137
138 ELSE
139
140 IF ( p_cross_year_flag = 'N') THEN
141 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
142 geh.year_to_apply_re,
143 geh.currency_code,
144 'N',
145 0
146 BULK COLLECT INTO
147 l_entry_id_list
148 FROM gcs_cons_eng_run_dtls gcerd,
149 gcs_entry_headers geh
150 WHERE gcerd.run_name = p_run_name
151 AND gcerd.consolidation_entity_id = p_cons_entity_id
152 AND gcerd.child_entity_id = p_entity_id
153 AND gcerd.category_code = p_category_code
154 AND gcerd.entry_id = geh.entry_id
155 AND geh.period_init_entry_flag = 'N'
156 AND p_cal_period_year < NVL(geh.year_to_apply_re, p_cal_period_year+1);
157 ELSIF (p_net_to_re_flag = 'N') THEN
158 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
159 geh.year_to_apply_re,
160 geh.currency_code,
161 'N',
162 0
163 BULK COLLECT INTO
164 l_entry_id_list
165 FROM gcs_cons_eng_run_dtls gcerd,
166 gcs_entry_headers geh
167 WHERE gcerd.run_name = p_run_name
168 AND gcerd.consolidation_entity_id = p_cons_entity_id
169 AND gcerd.child_entity_id = p_entity_id
170 AND gcerd.category_code = p_category_code
171 AND gcerd.entry_id = geh.entry_id
172 AND geh.period_init_entry_flag = 'N';
173 ELSE
174 SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
175 geh.year_to_apply_re,
176 geh.currency_code,
177 geh.period_init_entry_flag,
178 NVL(geh.end_cal_period_id, geh.start_cal_period_id) - geh.start_cal_period_id
179 BULK COLLECT INTO
180 l_entry_id_list
181 FROM gcs_cons_eng_run_dtls gcerd,
182 gcs_entry_headers geh
183 WHERE gcerd.run_name = p_run_name
184 AND gcerd.consolidation_entity_id = p_cons_entity_id
185 AND gcerd.child_entity_id = p_entity_id
186 AND gcerd.category_code = p_category_code
187 AND gcerd.entry_id = geh.entry_id;
188 END IF;
189
190 END IF;
191
192 --Bugfix 5449718: Do not need to copy entries to a single variable anymore.
193 /* FOR i IN l_stat_entry_id_list.FIRST.. l_stat_entry_id_list.LAST LOOP
194 l_entry_id_list(l_entry_id_list.LAST + i) := l_stat_entry_id_list(i);
195 END LOOP;
196 */
197 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
198 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.'|| fn_name, 'Number of entries to process: ' || l_entry_id_list.COUNT);
199 END IF;
200
201 --Bufix 5449718: Go to the end of the procedure as a safe net if there are no entries to process
202 IF (l_entry_id_list.COUNT = 0) THEN
203 GOTO norowstoprocess;
204 END IF;
205
206 --Bugfix 5449718: Must reclassify entries that are crossing the year end boundary to make sure the appropriate lines are applied
207 IF (p_net_to_re_flag = 'Y') THEN
208 FOR i IN l_entry_id_list.FIRST..l_entry_id_list.LAST LOOP
209 IF (l_entry_id_list(i).period_init_entry = 'Y' AND
210 l_entry_id_list(i).diff_in_cal_periods = 0) THEN
211 l_entry_id_list.DELETE(i);
212 ELSIF (l_entry_id_list(i).year_to_apply_re IS NOT NULL) THEN
213 l_num_recur_entry_id := l_num_recur_entry_id + 1;
214 l_recur_entry_id_list.EXTEND(1);
215 l_recur_entry_id_list(l_num_recur_entry_id) := l_entry_id_list(i);
216 l_entry_id_list.DELETE(i);
217 END IF;
218 END LOOP;
219 END IF;
220
221 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
222 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Number of single year entries: ' || l_entry_id_list.COUNT);
223 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Recurring Entries Where Year to Apply RE is not null: ' || l_recur_entry_id_list.COUNT);
224 END IF;
225
226 IF (p_cross_year_flag = 'N') THEN
227
228 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
229 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Processing Within Year');
230 END IF;
231
232 IF (l_entry_id_list.COUNT > 0) THEN
233 --Cannot reference tables of records in BULK statements so initializing individual tables
234 l_entry_list.DELETE;
235 l_currency_code_list.DELETE;
236
237 FOR i IN l_entry_id_list.FIRST..l_entry_id_list.LAST LOOP
238 l_entry_list(i) := l_entry_id_list(i).entry_id;
239 l_currency_code_list(i) := l_entry_id_list(i).currency_code;
240 END LOOP;
241
242 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
243 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Beginning Collection of Single Year Entries');
244 END IF;
245
246 --Bugfix 5449718: Need to insert into GCS_ENTRY_LINES_GT to avoid unique constraint errors
247 FORALL i IN l_entry_id_list.FIRST.. l_entry_id_list.LAST
248 INSERT INTO GCS_ENTRY_LINES_GT
249 (entry_id,
250 description,
251 company_cost_center_org_id,
252 intercompany_id,
253 line_item_id,
254 xtd_balance_e,
255 ytd_balance_e,
256 ptd_debit_balance_e,
257 ptd_credit_balance_e,
258 ytd_debit_balance_e,
259 ytd_credit_balance_e)
260 SELECT
261 --Bugfix 5449718: Remove the references to the init_xlate_entry_id, and removed all group by calcs as this is happening on a line by line basis.
262 --Also remove join to gcs_entry_headers as all of the information is available in the PL/SQL collection
263 decode(l_currency_code_list(i), 'STAT', p_init_stat_entry_id,
264 p_init_entry_id),
265 decode(feata.dim_attribute_varchar_member,
266 'REVENUE', 'PROFIT_LOSS',
267 'EXPENSE', 'PROFIT_LOSS',
268 'BALANCE_SHEET'),
269 l2.company_cost_center_org_id,
270 l2.intercompany_id,
271 l2.line_item_id,
272 decode(feata.dim_attribute_varchar_member,
273 'REVENUE', NVL(ytd_credit_balance_e,0) - NVL(ytd_debit_balance_e,0),
274 'EXPENSE', NVL(ytd_credit_balance_e,0) - NVL(ytd_debit_balance_e,0),
275 0),
276 0,
277 -1*(ytd_debit_balance_e),
278 -1*(ytd_credit_balance_e),
279 0,
280 0
281 FROM
282 GCS_ENTRY_LINES l2,
283 FEM_LN_ITEMS_ATTR lia,
284 FEM_EXT_ACCT_TYPES_ATTR feata
285 WHERE l2.entry_id = l_entry_list(i)
286 AND lia.attribute_id = g_li_eat_attr_id
287 AND lia.version_id = g_li_eat_ver_id
288 AND lia.line_item_id = l2.line_item_id
289 AND feata.attribute_id = g_acct_type_attr_id
290 AND feata.version_id = g_acct_type_ver_id
291 AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
292
293 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
294 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Completed Collection of Single Year Entries');
295 END IF;
296
297 END IF;
298
299 --Bugfix 5449718: Need to insert into GCS_ENTRY_LINES_GT to avoid unique constraint errors. Handle entries where only balance sheet lines must be applied
300 IF (l_recur_entry_id_list.COUNT > 0) THEN
301 --Cannot reference tables of records in BULK statements so initializing individual tables
302
303 l_entry_list.DELETE;
304 l_currency_code_list.DELETE;
305
306 FOR i IN l_recur_entry_id_list.FIRST..l_recur_entry_id_list.LAST LOOP
307 l_entry_list(i) := l_recur_entry_id_list(i).entry_id;
308 l_currency_code_list(i) := l_recur_entry_id_list(i).currency_code;
309 END LOOP;
310
311 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
312 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Beginning Collection of Recurring Entries');
313 END IF;
314
315 FORALL i IN l_recur_entry_id_list.FIRST.. l_recur_entry_id_list.LAST
316 INSERT INTO GCS_ENTRY_LINES_GT l1
317 (entry_id,
318 description,
319 company_cost_center_org_id,
320 intercompany_id,
321 line_item_id,
322 xtd_balance_e,
323 ytd_balance_e,
324 ptd_debit_balance_e,
325 ptd_credit_balance_e,
326 ytd_debit_balance_e,
327 ytd_credit_balance_e)
328 SELECT
329 --Bugfix 5449718: Remove the references to the init_xlate_entry_id, and removed all group by calcs as this is happening on a line by line basis.
330 --Also remove join to gcs_entry_headers as all of the information is available in the PL/SQL collection
331 --Join to line type is no longer required as all rows for recurring entries have the line type code populated
332 decode(l_currency_code_list(i), 'STAT', p_init_stat_entry_id,
333 p_init_entry_id),
334 l2.line_type_code,
335 l2.company_cost_center_org_id,
336 l2.intercompany_id,
337 l2.line_item_id,
338 --XTD Balance should be determined by the line type code
339 DECODE(l2.line_type_code, 'PROFIT_LOSS', NVL(ytd_credit_balance_e, 0) - NVL(ytd_debit_balance_e, 0),
340 0),
341 0,
342 -1*(ytd_debit_balance_e),
343 -1*(ytd_credit_balance_e),
344 0,
345 0
346 FROM
347 GCS_ENTRY_LINES l2
348 WHERE l2.entry_id = l_entry_list(i)
349 AND l2.line_type_code IN ('PROFIT_LOSS', 'BALANCE_SHEET');
350
351 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
352 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Completed Collection of Recurring Entries');
353 END IF;
354
355 END IF;
356
357 --Bugfix 5449718: Needed to add a condition if the Net to RE Flag is N versus Y when crossing the year-end boundary for performance purposes
358 ELSIF (p_net_to_re_flag = 'N') THEN
359
360 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
361 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Processing for Next Year with Net to RE set to N');
362 END IF;
363
364 -- find default org id for RE, in case bal_by_org is 'N'
365 l_default_org_id := GCS_UTILITY_PKG.get_org_id(p_entity_id,
366 p_hierarchy_id);
367
368 -- find default intercompany id for RE if specified
369 SELECT specific_intercompany_id
370 INTO l_intercompany_id
371 FROM GCS_CATEGORIES_B
372 WHERE category_code = 'INTRACOMPANY';
373
374 IF (l_entry_id_list.COUNT > 0) THEN
375 --Cannot reference tables of records in BULK statements so initializing individual tables
376 l_entry_list.DELETE;
377 l_currency_code_list.DELETE;
378
379 FOR i IN l_entry_id_list.FIRST..l_entry_id_list.LAST LOOP
380 l_entry_list(i) := l_entry_id_list(i).entry_id;
381 l_currency_code_list(i) := l_entry_id_list(i).currency_code;
382 END LOOP;
383
384 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
385 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Beginning Collection of Single Year Entries');
386 END IF;
387
388 FORALL i IN l_entry_id_list.FIRST.. l_entry_id_list.LAST
389 INSERT INTO GCS_ENTRY_LINES_GT l1
390 (entry_id,
391 description,
392 company_cost_center_org_id,
393 intercompany_id,
394 line_item_id,
395 xtd_balance_e,
396 ytd_balance_e,
397 ptd_debit_balance_e,
398 ptd_credit_balance_e,
399 ytd_debit_balance_e,
400 ytd_credit_balance_e)
401 SELECT
402 --Bugfix 5449718: No longer need the target entries
403 decode(l_currency_code_list(i), 'STAT', p_init_stat_entry_id,
404 p_init_entry_id),
405 'BALANCE_SHEET',
406 decode(p_bal_by_org,
407 'Y', l2.company_cost_center_org_id,
408 decode(feata.dim_attribute_varchar_member,
409 'REVENUE', l_default_org_id,
410 'EXPENSE', l_default_org_id,
411 l2.company_cost_center_org_id)),
412 -- RE: use org id only if there is no specified intercompany id
413 decode(feata.dim_attribute_varchar_member,
414 'REVENUE', nvl(l_intercompany_id,
415 decode(p_bal_by_org,
416 'Y', l2.company_cost_center_org_id,
417 l_default_org_id)),
418 'EXPENSE', nvl(l_intercompany_id,
419 decode(p_bal_by_org,
420 'Y', l2.company_cost_center_org_id,
421 l_default_org_id)),
422 l2.intercompany_id),
423 -- line item (cannot be secondary tracking column)
424 decode(feata.dim_attribute_varchar_member,
425 'REVENUE', p_re_template.line_item_id,
426 'EXPENSE', p_re_template.line_item_id,
427 l2.line_item_id),
428 --Bugfix 5449718: If net to re flag is N then all balances except ptd debit and ptd credit will be zero
429 0,
430 0,
431 -1*(l2.ytd_debit_balance_e),
432 -1*(l2.ytd_credit_balance_e),
433 0,
434 0
435 FROM
436 --Bugfix 5449718: Remove source, target entry, and category joins.
437 GCS_ENTRY_LINES l2,
438 FEM_LN_ITEMS_ATTR lia,
439 FEM_EXT_ACCT_TYPES_ATTR feata
440 WHERE
441 l2.entry_id = l_entry_list(i)
442 AND lia.attribute_id = g_li_eat_attr_id
443 AND lia.version_id = g_li_eat_ver_id
444 AND lia.line_item_id = l2.line_item_id
445 AND feata.attribute_id = g_acct_type_attr_id
446 AND feata.version_id = g_acct_type_ver_id
447 AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
448 -- Bugfix 5449718: Group by is no longer necessary
449
450 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
451 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Completed Collection of Single Year Entries');
452 END IF;
453
454 END IF;
455
456 --Bugfix 5449718: Needed to add a condition if the Net to RE Flag is N versus Y when crossing the year-end boundary for performance purposes
457 ELSIF (p_net_to_re_flag = 'Y') THEN
458
459 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
460 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Processing for Next Year with Net to RE set to Y');
461 END IF;
462
463 -- find default org id for RE, in case bal_by_org is 'N'
464 l_default_org_id := GCS_UTILITY_PKG.get_org_id(p_entity_id,
465 p_hierarchy_id);
466
467 -- find default intercompany id for RE if specified
468 SELECT specific_intercompany_id
469 INTO l_intercompany_id
470 FROM GCS_CATEGORIES_B
471 WHERE category_code = 'INTRACOMPANY';
472
473 l_entry_list.DELETE;
474 l_currency_code_list.DELETE;
475
476 IF (l_entry_id_list.COUNT > 0) THEN
477 --Cannot reference tables of records in BULK statements so initializing individual tables
478 FOR i IN l_entry_id_list.FIRST..l_entry_id_list.LAST LOOP
479 l_entry_list(i) := l_entry_id_list(i).entry_id;
480 l_currency_code_list(i) := l_entry_id_list(i).currency_code;
481 END LOOP;
482
483 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
484 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Beginning Collection of Single Year Entries');
485 END IF;
486
487 FORALL i IN l_entry_id_list.FIRST.. l_entry_id_list.LAST
488 INSERT INTO GCS_ENTRY_LINES_GT l1
489 (entry_id,
490 description,
491 company_cost_center_org_id,
492 intercompany_id,
493 line_item_id,
494 xtd_balance_e,
495 ytd_balance_e,
496 ptd_debit_balance_e,
497 ptd_credit_balance_e,
498 ytd_debit_balance_e,
499 ytd_credit_balance_e)
500 SELECT
501 --Bugfix 5449718: No longer need the target entries
502 decode(l_currency_code_list(i), 'STAT', p_recur_stat_entry_id,
503 p_recur_entry_id),
504 'BALANCE_SHEET',
505 decode(p_bal_by_org,
506 'Y', l2.company_cost_center_org_id,
507 decode(feata.dim_attribute_varchar_member,
508 'REVENUE', l_default_org_id,
509 'EXPENSE', l_default_org_id,
510 l2.company_cost_center_org_id)),
511 -- RE: use org id only if there is no specified intercompany id
512 decode(feata.dim_attribute_varchar_member,
513 'REVENUE', nvl(l_intercompany_id,
514 decode(p_bal_by_org,
515 'Y', l2.company_cost_center_org_id,
516 l_default_org_id)),
517 'EXPENSE', nvl(l_intercompany_id,
518 decode(p_bal_by_org,
519 'Y', l2.company_cost_center_org_id,
520 l_default_org_id)),
521 l2.intercompany_id),
522 -- line item (cannot be secondary tracking column)
523 decode(feata.dim_attribute_varchar_member,
524 'REVENUE', p_re_template.line_item_id,
525 'EXPENSE', p_re_template.line_item_id,
526 l2.line_item_id),
527 --Bugfix 5449718: If net to re flag is Y then all balances except ptd debit and ptd credit will be zero
528 ytd_balance_e,
529 ytd_balance_e,
530 0,
531 0,
532 ytd_debit_balance_e,
533 ytd_credit_balance_e
534 FROM
535 --Bugfix 5449718: Remove source, target entry, and category joins. Add join to fem_ext_acct_types_attr
536 GCS_ENTRY_LINES l2,
537 FEM_LN_ITEMS_ATTR lia,
538 FEM_EXT_ACCT_TYPES_ATTR feata
539 WHERE
540 l2.entry_id = l_entry_list(i)
541 AND lia.attribute_id = g_li_eat_attr_id
542 AND lia.version_id = g_li_eat_ver_id
543 AND lia.line_item_id = l2.line_item_id
544 AND feata.attribute_id = g_acct_type_attr_id
545 AND feata.version_id = g_acct_type_ver_id
546 AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
547 -- Bugfix 5449718: Group by is no longer necessary
548
549 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
550 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Completed Collection of Single Year');
551 END IF;
552
553
554 END IF;
555
556
557 l_entry_list.DELETE;
558 l_currency_code_list.DELETE;
559
560 IF (l_recur_entry_id_list.COUNT > 0) THEN
561 --Cannot reference tables of records in BULK statements so initializing individual tables
562 FOR i IN l_recur_entry_id_list.FIRST..l_recur_entry_id_list.LAST LOOP
563 l_entry_list(i) := l_recur_entry_id_list(i).entry_id;
564 l_currency_code_list(i) := l_recur_entry_id_list(i).currency_code;
565 END LOOP;
566
567 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
568 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Beginning Collection of Recurring Entries');
569 END IF;
570
571 FORALL i IN l_recur_entry_id_list.FIRST.. l_recur_entry_id_list.LAST
572 INSERT INTO GCS_ENTRY_LINES_GT l1
573 (entry_id,
574 description,
575 company_cost_center_org_id,
576 intercompany_id,
577 line_item_id,
578 xtd_balance_e,
579 ytd_balance_e,
580 ptd_debit_balance_e,
581 ptd_credit_balance_e,
582 ytd_debit_balance_e,
583 ytd_credit_balance_e)
584 SELECT
585 --Bugfix 5449718: No longer need the target entries
586 decode(l_currency_code_list(i), 'STAT', p_recur_stat_entry_id,
587 p_recur_entry_id),
588 'BALANCE_SHEET',
589 l2.company_cost_center_org_id,
590 l2.intercompany_id,
591 l2.line_item_id,
592
593 --Bugfix 5449718: If net to re flag is N then all balances except ptd debit and ptd credit will be zero
594 0,
595 0,
596 -1*ytd_debit_balance_e,
597 -1*ytd_credit_balance_e,
598 0,
599 0
600 FROM
601 --Bugfix 5449718: Remove source, target entry, and category joins.
602 GCS_ENTRY_LINES l2
603 WHERE
604 l2.entry_id = l_entry_list(i)
605 AND l2.line_type_code IN ('CALCULATED', 'BALANCE_SHEET');
606 -- Bugfix 5449718: Group by is no longer necessary
607
608 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
609 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.' || fn_name, 'Completed Collection of Recurring Entries');
610 END IF;
611
612 END IF;
613 END IF; --if..then..cross year flag
614
615 --Bugfix 5449718: Move data from gcs_entry_lines_gt into gcs_entry_lines
616 INSERT INTO gcs_entry_lines
617 (entry_id,
618 line_type_code,
619 company_cost_center_org_id,
620 intercompany_id,
621 line_item_id,
622
623 xtd_balance_e,
624 ytd_balance_e,
625 ptd_debit_balance_e,
626 ptd_credit_balance_e,
627 ytd_debit_balance_e,
628 ytd_credit_balance_e,
629 creation_date,
630 created_by,
631 last_update_date,
632 last_updated_by,
633 last_update_login)
634 SELECT
635 entry_id,
636 MIN(description),
637 company_cost_center_org_id,
638 intercompany_id,
639 line_item_id,
640
641 SUM(NVL(xtd_balance_e,0)),
642 SUM(NVL(ytd_balance_e,0)),
643 SUM(NVL(ptd_debit_balance_e,0)),
644 SUM(NVL(ptd_credit_balance_e,0)),
645 SUM(NVL(ytd_debit_balance_e,0)),
646 SUM(NVL(ytd_credit_balance_e,0)),
647 sysdate,
648 GCS_PERIOD_INIT_PKG.g_fnd_user_id,
649 sysdate,
650 GCS_PERIOD_INIT_PKG.g_fnd_user_id,
651 GCS_PERIOD_INIT_PKG.g_fnd_login_id
652 FROM gcs_entry_lines_gt
653 GROUP BY entry_id,
654 company_cost_center_org_id,
655 line_item_id,
656
657 intercompany_id;
658
659 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
660 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
661 g_api || '.' || fn_name,
662 'Inserted ' || to_char(SQL%ROWCOUNT) || ' row(s)');
663 END IF;
664
665 <<norowstoprocess>>
666
667 COMMIT;
668
669 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
670 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
671 g_api || '.' || fn_name,
672 GCS_UTILITY_PKG.g_module_success || fn_name ||
673 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
674 END IF;
675 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
676 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
681 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
682 g_api || '.' || fn_name,
683 GCS_UTILITY_PKG.g_module_failure || fn_name ||
684 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
685 END IF;
686 FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
687 fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
688 RAISE;
689 END insert_entry_lines;
690
691 END GCS_PERIOD_INIT_DYNAMIC_PKG;
[Home] [Help]