[Home] [Help]
PACKAGE BODY: APPS.GCS_DYN_FEM_POSTING_PKG
Source
1 PACKAGE BODY GCS_DYN_FEM_POSTING_PKG AS
2
3 -- Store the log level
4 runtimeLogLevel NUMBER := FND_LOG.g_current_runtime_level;
5 statementLogLevel CONSTANT NUMBER := FND_LOG.level_statement;
6 procedureLogLevel CONSTANT NUMBER := FND_LOG.level_procedure;
7 exceptionLogLevel CONSTANT NUMBER := FND_LOG.level_exception;
8 errorLogLevel CONSTANT NUMBER := FND_LOG.level_error;
9 unexpectedLogLevel CONSTANT NUMBER := FND_LOG.level_unexpected;
10
11 g_src_sys_code NUMBER := GCS_UTILITY_PKG.g_gcs_source_system_code;
12 g_dimension_attr_info gcs_utility_pkg.t_hash_dimension_attr_info
13 := gcs_utility_pkg.g_dimension_attr_info;
14
15 no_proc_data_err EXCEPTION;
16
17
18
19 PROCEDURE Populate_GT_Table(
20 p_category_code VARCHAR2,
21 p_cons_entity_id NUMBER,
22 p_child_entity_id NUMBER,
23 p_run_name VARCHAR2,
24 p_run_detail_id NUMBER,
25 p_entry_id NUMBER,
26 p_cal_period_year NUMBER,
27 errbuf IN OUT NOCOPY VARCHAR2,
28 retcode IN OUT NOCOPY VARCHAR2 ) IS
29
30 l_recur_entry_flag VARCHAR2(1);
31 l_entry_id_list DBMS_SQL.NUMBER_TABLE;
32 l_entity_id_list DBMS_SQL.NUMBER_TABLE;
33 l_currency_code_list DBMS_SQL.VARCHAR2_TABLE;
34
35 BEGIN
36
37 IF (procedureloglevel >= runtimeloglevel ) THEN
38 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.POPULATE_GT_TABLE.begin', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
39 END IF;
40
41 IF (p_entry_id IS NOT NULL OR p_run_detail_id IS NOT NULL) THEN
42 IF (p_entry_id IS NOT NULL) THEN
43 SELECT entry_id, entity_id, currency_code
44 BULK COLLECT INTO l_entry_id_list, l_entity_id_list, l_currency_code_list
45 FROM GCS_ENTRY_HEADERS
46 WHERE entry_id = p_entry_id;
47
48 ELSE -- p_run_detail_id is not null
49 SELECT ghd.entry_id, ghd.entity_id, ghd.currency_code
50 BULK COLLECT INTO l_entry_id_list, l_entity_id_list, l_currency_code_list
51 FROM GCS_CONS_ENG_RUN_DTLS GCR,
52 GCS_ENTRY_HEADERS GHD
53 WHERE GCR.run_detail_id = p_run_detail_id
54 AND GHD.entry_id in ( GCR.entry_id, GCR.stat_entry_id);
55 END IF;
56
57 IF (SQL%ROWCOUNT = 0) THEN
58 RAISE no_proc_data_err;
59 END IF;
60
61 FORALL i IN l_entry_id_list.FIRST..l_entry_id_list.LAST
62 INSERT
63 INTO GCS_FEM_POSTING_GT(
64 ENTRY_ID,
65 SEQUENCE_NUM,
66 CURRENCY_CODE,
67 COMPANY_COST_CENTER_ORG_ID,
68 INTERCOMPANY_ID,
69 ENTITY_ID,
70 LINE_ITEM_ID,
71 XTD_BALANCE_E,
72 YTD_BALANCE_E,
73 PTD_DEBIT_BALANCE_E,
74 PTD_CREDIT_BALANCE_E,
75 YTD_DEBIT_BALANCE_E,
76 YTD_CREDIT_BALANCE_E
77 )
78 SELECT
79 l_entry_id_list(i),
80 GCS_FEM_BAL_S.nextval,
81 l_currency_code_list(i),
82 GLE.COMPANY_COST_CENTER_ORG_ID,
83 GLE.INTERCOMPANY_ID,
84 l_entity_id_list(i),
85 GLE.LINE_ITEM_ID,
86 nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E),
87 GLE.YTD_BALANCE_E,
88 nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E),
89 nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E),
90 GLE.YTD_DEBIT_BALANCE_E,
91 GLE.YTD_CREDIT_BALANCE_E
92 FROM GCS_ENTRY_LINES GLE
93 WHERE GLE.entry_id = l_entry_id_list(i);
94
95 -- The following is happening when consolidating adjustments on operating entities
96 ELSIF (p_child_entity_id is not null) THEN
97
98 BEGIN
99 SELECT 'Y'
100 INTO l_recur_entry_flag
101 FROM dual
102 WHERE EXISTS
103 (SELECT 1
104 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
105 GCS_ENTRY_HEADERS GHD
106 WHERE GCERD.run_name = p_run_name
107 AND GCERD.consolidation_entity_id = p_cons_entity_id
108 AND GCERD.child_entity_id = p_child_entity_id
109 AND GCERD.category_code = p_category_code
110 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
111 AND (GHD.end_cal_period_id IS NULL OR ghd.start_cal_period_id <> ghd.end_cal_period_id));
112
113 -- bug fix 5080422: swap position of line_item_id and intercompany_id
114 INSERT
115 INTO GCS_FEM_POSTING_GT(
116 ENTRY_ID,
117 SEQUENCE_NUM,
118 CURRENCY_CODE,
119 COMPANY_COST_CENTER_ORG_ID,
120 INTERCOMPANY_ID,
121 ENTITY_ID,
122 LINE_ITEM_ID,
123 XTD_BALANCE_E,
124 YTD_BALANCE_E,
125 PTD_DEBIT_BALANCE_E,
126 PTD_CREDIT_BALANCE_E,
127 YTD_DEBIT_BALANCE_E,
128 YTD_CREDIT_BALANCE_E
129 )
130 SELECT
131 GFB.ENTRY_ID,
132 GCS_FEM_BAL_S.nextval,
133 GFB.CURRENCY_CODE,
134 GFB.COMPANY_COST_CENTER_ORG_ID,
135 GFB.INTERCOMPANY_ID,
136 GFB.ENTITY_ID,
137 GFB.LINE_ITEM_ID,
138
139 GFB.XTD_BALANCE_E,
140 GFB.YTD_BALANCE_E,
141 GFB.PTD_DEBIT_BALANCE_E,
142 GFB.PTD_CREDIT_BALANCE_E,
143 GFB.YTD_DEBIT_BALANCE_E,
144 GFB.YTD_CREDIT_BALANCE_E
145 FROM (
146 SELECT max(GHD.entry_id) entry_id,
147 GHD.currency_code,
148 GLE.COMPANY_COST_CENTER_ORG_ID,
149 GLE.INTERCOMPANY_ID,
150 GLE.LINE_ITEM_ID,
151 max(GHD.ENTITY_ID) ENTITY_ID,
152 sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
153 sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
154 sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
155 sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
156 sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
157 sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
158 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
159 GCS_ENTRY_HEADERS GHD,
160 GCS_ENTRY_LINES GLE
161 WHERE GCERD.run_name = p_run_name
162 AND GCERD.consolidation_entity_id = p_cons_entity_id
163 AND GCERD.child_entity_id = p_child_entity_id
164 AND GCERD.category_code = p_category_code
165 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
166 AND GLE.entry_id = GHD.entry_id
167 AND ((GHD.start_cal_period_id = GHD.end_cal_period_id)
168 OR ((GHD.start_cal_period_id <> GHD.end_cal_period_id OR GHD.end_cal_period_id is NULL)
169 AND (GHD.year_to_apply_re IS NULL OR (p_cal_period_year >= GHD.year_to_apply_re AND GLE.line_type_code <> 'PROFIT_LOSS')
170 OR (p_cal_period_year < GHD.year_to_apply_re AND GLE.line_type_code <> 'CALCULATED'))))
171 GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
172 GLE.intercompany_id ) GFB;
173
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176
177 -- bug fix 5080422: swap position of line_item_id and intercompany_id
178 INSERT
179 INTO GCS_FEM_POSTING_GT(
180 ENTRY_ID,
181 SEQUENCE_NUM,
182 CURRENCY_CODE,
183 COMPANY_COST_CENTER_ORG_ID,
184 INTERCOMPANY_ID,
185 ENTITY_ID,
186 LINE_ITEM_ID,
187 XTD_BALANCE_E,
188 YTD_BALANCE_E,
189 PTD_DEBIT_BALANCE_E,
190 PTD_CREDIT_BALANCE_E,
191 YTD_DEBIT_BALANCE_E,
192 YTD_CREDIT_BALANCE_E
193 )
194 SELECT
195 GFB.ENTRY_ID,
196 GCS_FEM_BAL_S.nextval,
197 GFB.CURRENCY_CODE,
198 GFB.COMPANY_COST_CENTER_ORG_ID,
199 GFB.INTERCOMPANY_ID,
200 GFB.ENTITY_ID,
201 GFB.LINE_ITEM_ID,
202
203 GFB.XTD_BALANCE_E,
204 GFB.YTD_BALANCE_E,
205 GFB.PTD_DEBIT_BALANCE_E,
206 GFB.PTD_CREDIT_BALANCE_E,
207 GFB.YTD_DEBIT_BALANCE_E,
208 GFB.YTD_CREDIT_BALANCE_E
209 FROM (
210 SELECT max(GHD.entry_id) entry_id,
211 GHD.currency_code,
212 GLE.COMPANY_COST_CENTER_ORG_ID,
213 GLE.INTERCOMPANY_ID,
214 GLE.LINE_ITEM_ID,
215 max(GHD.ENTITY_ID) ENTITY_ID,
216 sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
217 sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
218 sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
219 sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
220 sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
221 sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
222 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
223 GCS_ENTRY_HEADERS GHD,
224 GCS_ENTRY_LINES GLE
225 WHERE GCERD.run_name = p_run_name
226 AND GCERD.consolidation_entity_id = p_cons_entity_id
227 AND GCERD.child_entity_id = p_child_entity_id
228 AND GCERD.category_code = p_category_code
229 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
230 AND GLE.entry_id = GHD.entry_id
231 GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
232 GLE.intercompany_id ) GFB;
233 END;
234
235 -- The following is happening when consolidating adjustments on consolidation entities
236 ELSE
237
238 BEGIN
239 SELECT 'Y'
240 INTO l_recur_entry_flag
241 FROM dual
242 WHERE EXISTS
243 (SELECT 1
244 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
245 GCS_ENTRY_HEADERS GHD
246 WHERE GCERD.run_name = p_run_name
247 AND GCERD.consolidation_entity_id = p_cons_entity_id
248 AND GCERD.category_code = p_category_code
249 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
250 AND (GHD.end_cal_period_id IS NULL OR ghd.start_cal_period_id <> ghd.end_cal_period_id));
251
252 -- bug fix 5080422: swap position of line_item_id and intercompany_id
253 INSERT
254 INTO GCS_FEM_POSTING_GT(
255 ENTRY_ID,
256 SEQUENCE_NUM,
257 CURRENCY_CODE,
258 COMPANY_COST_CENTER_ORG_ID,
259 INTERCOMPANY_ID,
260 ENTITY_ID,
261 LINE_ITEM_ID,
262 XTD_BALANCE_E,
263 YTD_BALANCE_E,
264 PTD_DEBIT_BALANCE_E,
265 PTD_CREDIT_BALANCE_E,
266 YTD_DEBIT_BALANCE_E,
267 YTD_CREDIT_BALANCE_E
268 )
269 SELECT
270 GFB.ENTRY_ID,
271 GCS_FEM_BAL_S.nextval,
272 GFB.CURRENCY_CODE,
273 GFB.COMPANY_COST_CENTER_ORG_ID,
274 GFB.INTERCOMPANY_ID,
275 GFB.ENTITY_ID,
276 GFB.LINE_ITEM_ID,
277
278 GFB.XTD_BALANCE_E,
279 GFB.YTD_BALANCE_E,
280 GFB.PTD_DEBIT_BALANCE_E,
281 GFB.PTD_CREDIT_BALANCE_E,
282 GFB.YTD_DEBIT_BALANCE_E,
283 GFB.YTD_CREDIT_BALANCE_E
284 FROM (
285 SELECT max(GHD.entry_id) entry_id,
286 GHD.currency_code,
287 GLE.COMPANY_COST_CENTER_ORG_ID,
288 GLE.INTERCOMPANY_ID,
289 GLE.LINE_ITEM_ID,
290 max(GHD.ENTITY_ID) ENTITY_ID,
291 sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
292 sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
293 sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
294 sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
295 sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
296 sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
297 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
298 GCS_ENTRY_HEADERS GHD,
299 GCS_ENTRY_LINES GLE
300 WHERE GCERD.run_name = p_run_name
301 AND GCERD.consolidation_entity_id = p_cons_entity_id
302 AND GCERD.category_code = p_category_code
303 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
304 AND GLE.entry_id = GHD.entry_id
305 AND ((GHD.start_cal_period_id = GHD.end_cal_period_id)
306 OR ((GHD.start_cal_period_id <> GHD.end_cal_period_id OR GHD.end_cal_period_id is NULL)
307 AND (GHD.year_to_apply_re IS NULL OR (p_cal_period_year >= GHD.year_to_apply_re AND GLE.line_type_code <> 'PROFIT_LOSS')
308 OR (p_cal_period_year < GHD.year_to_apply_re AND GLE.line_type_code <> 'CALCULATED'))))
309 GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
310 GLE.intercompany_id ) GFB;
311
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314
315 -- bug fix 5080422: swap position of line_item_id and intercompany_id
316 INSERT
317 INTO GCS_FEM_POSTING_GT(
318 ENTRY_ID,
319 SEQUENCE_NUM,
320 CURRENCY_CODE,
321 COMPANY_COST_CENTER_ORG_ID,
322 INTERCOMPANY_ID,
323 ENTITY_ID,
324 LINE_ITEM_ID,
325 XTD_BALANCE_E,
326 YTD_BALANCE_E,
327 PTD_DEBIT_BALANCE_E,
328 PTD_CREDIT_BALANCE_E,
329 YTD_DEBIT_BALANCE_E,
330 YTD_CREDIT_BALANCE_E
331 )
332 SELECT
333 GFB.ENTRY_ID,
334 GCS_FEM_BAL_S.nextval,
335 GFB.CURRENCY_CODE,
336 GFB.COMPANY_COST_CENTER_ORG_ID,
337 GFB.INTERCOMPANY_ID,
338 GFB.ENTITY_ID,
339 GFB.LINE_ITEM_ID,
340
341 GFB.XTD_BALANCE_E,
342 GFB.YTD_BALANCE_E,
343 GFB.PTD_DEBIT_BALANCE_E,
344 GFB.PTD_CREDIT_BALANCE_E,
345 GFB.YTD_DEBIT_BALANCE_E,
346 GFB.YTD_CREDIT_BALANCE_E
347 FROM (
348 SELECT max(GHD.entry_id) entry_id,
349 GHD.currency_code,
350 GLE.COMPANY_COST_CENTER_ORG_ID,
351 GLE.INTERCOMPANY_ID,
352 GLE.LINE_ITEM_ID,
353 max(GHD.ENTITY_ID) ENTITY_ID,
354 sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
355 sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
356 sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
357 sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
358 sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
359 sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
360 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
361 GCS_ENTRY_HEADERS GHD,
362 GCS_ENTRY_LINES GLE
363 WHERE GCERD.run_name = p_run_name
367 AND GLE.entry_id = GHD.entry_id
364 AND GCERD.consolidation_entity_id = p_cons_entity_id
365 AND GCERD.category_code = p_category_code
366 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
368 GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
369 GLE.intercompany_id ) GFB;
370
371 END;
372 END IF;
373
374 IF (procedureloglevel >= runtimeloglevel ) THEN
375 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.POPULATE_GT_TABLE.end', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
376 END IF;
377
378 END Populate_GT_Table;
379
380
381 PROCEDURE Process_Insert( p_hier_dataset_code NUMBER,
382 p_object_id NUMBER,
383 p_category_code VARCHAR2,
384 p_cons_entity_id NUMBER,
385 p_child_entity_id NUMBER,
386 p_cal_period_id NUMBER,
387 p_cal_period_year NUMBER,
388 p_ledger_id NUMBER,
389 p_run_name VARCHAR2,
390 p_run_detail_id NUMBER,
391 p_entry_id NUMBER,
392 p_undo VARCHAR2,
393 p_xlate VARCHAR2,
394 --Bugfix 5646770: Added parameter for topmost entity flag
395 p_topmost_entity_flag VARCHAR2,
396 errbuf IN OUT NOCOPY VARCHAR2,
397 retcode IN OUT NOCOPY VARCHAR2
398 ) IS
399
400 l_req_id NUMBER := FND_GLOBAL.conc_request_id;
401 l_login_id NUMBER := FND_GLOBAL.login_id;
402 l_user_id NUMBER := FND_GLOBAL.user_id;
403 l_entries_id DBMS_SQL.number_table;
404 l_currencies_code DBMS_SQL.varchar2_table;
405 l_entities_id DBMS_SQL.number_table;
406
407 BEGIN
408
409 IF (procedureloglevel >= runtimeloglevel ) THEN
410 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT.begin', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
411 END IF;
412
413 Populate_Gt_Table(p_category_code => p_category_code,
414 p_cons_entity_id => p_cons_entity_id,
415 p_child_entity_id => p_child_entity_id,
416 p_run_name => p_run_name,
417 p_run_detail_id => p_run_detail_id,
418 p_entry_id => p_entry_id,
419 p_cal_period_year => p_cal_period_year,
420 errbuf => errbuf,
421 retcode => retcode);
422
423 INSERT INTO FEM_BALANCES(
424 DATASET_CODE,
425 CAL_PERIOD_ID,
426 CREATION_ROW_SEQUENCE,
427 SOURCE_SYSTEM_CODE,
428 LEDGER_ID,
429 COMPANY_COST_CENTER_ORG_ID,
430 CURRENCY_CODE,
431 CURRENCY_TYPE_CODE,
432 INTERCOMPANY_ID,
433 ENTITY_ID,
434 LINE_ITEM_ID,
435 CREATED_BY_REQUEST_ID,
436 CREATED_BY_OBJECT_ID,
437 LAST_UPDATED_BY_REQUEST_ID,
438 LAST_UPDATED_BY_OBJECT_ID,
439 XTD_BALANCE_E,
440 YTD_BALANCE_E,
441 PTD_DEBIT_BALANCE_E,
442 PTD_CREDIT_BALANCE_E,
443 YTD_DEBIT_BALANCE_E,
444 YTD_CREDIT_BALANCE_E,
445 --Bugfix 5646770: Added _F Columns for Top Most Entity
446 XTD_BALANCE_F,
447 YTD_BALANCE_F
448 )
449 SELECT
450 p_hier_dataset_code,
451 p_cal_period_id,
452 sequence_num,
453 g_src_sys_code,
454 p_ledger_id,
455 company_cost_center_org_id,
456 currency_code,
457 'TOTAL',
458 intercompany_id,
459 entity_id,
460 line_item_id,
461 l_req_id,
462 p_object_id,
463 l_req_id,
464 p_object_id,
465 XTD_BALANCE_E,
466 YTD_BALANCE_E,
467 PTD_DEBIT_BALANCE_E,
468 PTD_CREDIT_BALANCE_E,
469 YTD_DEBIT_BALANCE_E,
470 YTD_CREDIT_BALANCE_E,
471 --Bugfix 5646770: Added _F Columns for topmost entity
472 DECODE(p_topmost_entity_flag, 'Y', XTD_BALANCE_E, NULL) XTD_BALANCE_F,
473 DECODE(p_topmost_entity_flag, 'Y', YTD_BALANCE_E, NULL) YTD_BALANCE_F
474 FROM GCS_FEM_POSTING_GT;
475
476 IF (procedureloglevel >= runtimeloglevel ) THEN
477 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT.end', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
478 END IF;
479
480 EXCEPTION
481 WHEN NO_DATA_FOUND THEN
482 IF (unexpectedloglevel >= runtimeloglevel ) THEN
483 FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT', 'GCS_NO_DATA_FOUND');
484 END IF;
485 retcode := '0';
486 errbuf := 'GCS_NO_DATA_FOUND';
487 RAISE NO_DATA_FOUND;
488
489 WHEN OTHERS THEN
493 END IF;
490 errbuf := substr( SQLERRM, 1, 2000);
491 IF (unexpectedloglevel >= runtimeloglevel ) THEN
492 FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT', errbuf);
494 retcode := '0';
495 RAISE;
496
497 END Process_Insert;
498 PROCEDURE Process_Merge(p_hier_dataset_code NUMBER,
499 p_mode VARCHAR2,
500 p_object_id NUMBER,
501 p_category_code VARCHAR2,
502 p_cons_entity_id NUMBER,
503 p_child_entity_id NUMBER,
504 p_cal_period_id NUMBER,
505 p_cal_period_year NUMBER,
506 p_ledger_id NUMBER,
507 p_run_name VARCHAR2,
508 p_run_detail_id NUMBER,
509 p_entry_id NUMBER,
510 p_undo VARCHAR2,
511 p_xlate VARCHAR2,
512 errbuf IN OUT NOCOPY VARCHAR2,
513 retcode IN OUT NOCOPY VARCHAR2 ) IS
514
515 l_req_id NUMBER := FND_GLOBAL.conc_request_id;
516 l_login_id NUMBER := FND_GLOBAL.login_id;
517 l_user_id NUMBER := FND_GLOBAL.user_id;
518
519 BEGIN
520
521 IF (procedureloglevel >= runtimeloglevel ) THEN
522 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_MERGE.begin', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
523 END IF;
524
525 Populate_Gt_Table(p_category_code => p_category_code,
526 p_cons_entity_id => p_cons_entity_id,
527 p_child_entity_id => p_child_entity_id,
528 p_run_name => p_run_name,
529 p_run_detail_id => p_run_detail_id,
530 p_entry_id => p_entry_id,
531 p_cal_period_year => p_cal_period_year,
532 errbuf => errbuf,
533 retcode => retcode);
534
535 IF (p_mode = 'M') THEN
536 MERGE INTO FEM_BALANCES FB
537 USING(
538 SELECT
539 p_hier_dataset_code DATASET_CODE,
540 p_cal_period_id CAL_PERIOD_ID,
541 g_src_sys_code SOURCE_SYSTEM_CODE,
542 p_ledger_id LEDGER_ID,
543 GLE.SEQUENCE_NUM CREATION_ROW_SEQUENCE,
544 l_req_id CREATED_BY_REQUEST_ID,
545 p_object_id CREATED_BY_OBJECT_ID,
546 l_req_id LAST_UPDATED_BY_REQUEST_ID,
547 p_object_id LAST_UPDATED_BY_OBJECT_ID,
548 GLE.COMPANY_COST_CENTER_ORG_ID COMPANY_COST_CENTER_ORG_ID,
549 GLE.LINE_ITEM_ID LINE_ITEM_ID,
550 GLE.INTERCOMPANY_ID INTERCOMPANY_ID,
551 GLE.CURRENCY_CODE CURRENCY_CODE,
552 GLE.ENTITY_ID ENTITY_ID,
553 GLE.XTD_BALANCE_E,
554 GLE.YTD_BALANCE_E YTD_BALANCE_E,
555 GLE.PTD_DEBIT_BALANCE_E,
556 GLE.PTD_CREDIT_BALANCE_E,
557 GLE.YTD_DEBIT_BALANCE_E YTD_DEBIT_BALANCE_E,
558 GLE.YTD_CREDIT_BALANCE_E YTD_CREDIT_BALANCE_E
559 FROM GCS_FEM_POSTING_GT GLE) GFB
560 ON (
561 FB.CREATED_BY_OBJECT_ID = GFB.CREATED_BY_OBJECT_ID
562 AND FB.CREATED_BY_REQUEST_ID = GFB.CREATED_BY_REQUEST_ID
563 AND FB.CREATION_ROW_SEQUENCE = GFB.CREATION_ROW_SEQUENCE)
564 WHEN MATCHED THEN UPDATE SET
565 FB.xtd_balance_e = GFB.xtd_balance_e,
566 FB.ptd_credit_balance_e = GFB.ptd_credit_balance_e,
567 FB.ptd_debit_balance_e = GFB.ptd_debit_balance_e,
568 FB.ytd_balance_e = GFB.ytd_balance_e,
569 FB.ytd_credit_balance_e = GFB.ytd_credit_balance_e,
570 FB.ytd_debit_balance_e = GFB.ytd_debit_balance_e
571 WHEN NOT MATCHED THEN INSERT
572 (
573 FB.DATASET_CODE,
574 FB.CAL_PERIOD_ID,
575 FB.CREATION_ROW_SEQUENCE,
576 FB.SOURCE_SYSTEM_CODE,
577 FB.LEDGER_ID,
578 FB.COMPANY_COST_CENTER_ORG_ID,
579 FB.CURRENCY_CODE,
580 FB.CURRENCY_TYPE_CODE,
581 FB.LINE_ITEM_ID,
582 FB.ENTITY_ID,
583 FB.INTERCOMPANY_ID,
584 FB.CREATED_BY_REQUEST_ID,
585 FB.CREATED_BY_OBJECT_ID,
586 FB.LAST_UPDATED_BY_REQUEST_ID,
587 FB.LAST_UPDATED_BY_OBJECT_ID,
588 FB.XTD_BALANCE_E,
589 FB.YTD_BALANCE_E,
590 FB.PTD_DEBIT_BALANCE_E,
591 FB.PTD_CREDIT_BALANCE_E,
592 FB.YTD_DEBIT_BALANCE_E,
593 FB.YTD_CREDIT_BALANCE_E
594 )
595 VALUES
596 (
597 GFB.DATASET_CODE,
598 GFB.CAL_PERIOD_ID,
599 GFB.CREATION_ROW_SEQUENCE,
600 GFB.SOURCE_SYSTEM_CODE,
601 GFB.LEDGER_ID,
602 GFB.COMPANY_COST_CENTER_ORG_ID,
603 GFB.CURRENCY_CODE,
604 'TOTAL',
605 GFB.LINE_ITEM_ID,
606 GFB.ENTITY_ID,
607 GFB.INTERCOMPANY_ID,
608 GFB.CREATED_BY_REQUEST_ID,
609 GFB.CREATED_BY_OBJECT_ID,
610 GFB.LAST_UPDATED_BY_REQUEST_ID,
611 GFB.LAST_UPDATED_BY_OBJECT_ID,
612 GFB.XTD_BALANCE_E,
613 GFB.YTD_BALANCE_E,
614 GFB.PTD_DEBIT_BALANCE_E,
615 GFB.PTD_CREDIT_BALANCE_E,
616 GFB.YTD_DEBIT_BALANCE_E,
617 GFB.YTD_CREDIT_BALANCE_E);
618
619 ELSE
620 MERGE INTO FEM_BALANCES FB
621 USING(
622 SELECT
623 p_hier_dataset_code DATASET_CODE,
624 p_cal_period_id CAL_PERIOD_ID,
625 g_src_sys_code SOURCE_SYSTEM_CODE,
626 p_ledger_id LEDGER_ID,
627 GLE.SEQUENCE_NUM CREATION_ROW_SEQUENCE,
628 l_req_id CREATED_BY_REQUEST_ID,
629 p_object_id CREATED_BY_OBJECT_ID,
630 l_req_id LAST_UPDATED_BY_REQUEST_ID,
634 GLE.INTERCOMPANY_ID INTERCOMPANY_ID,
631 p_object_id LAST_UPDATED_BY_OBJECT_ID,
632 GLE.COMPANY_COST_CENTER_ORG_ID COMPANY_COST_CENTER_ORG_ID,
633 GLE.LINE_ITEM_ID LINE_ITEM_ID,
635 GLE.CURRENCY_CODE CURRENCY_CODE,
636 GLE.ENTITY_ID ENTITY_ID,
637 GLE.XTD_BALANCE_E,
638 GLE.YTD_BALANCE_E YTD_BALANCE_E,
639 GLE.PTD_DEBIT_BALANCE_E,
640 GLE.PTD_CREDIT_BALANCE_E,
641 GLE.YTD_DEBIT_BALANCE_E YTD_DEBIT_BALANCE_E,
642 GLE.YTD_CREDIT_BALANCE_E YTD_CREDIT_BALANCE_E
643 FROM GCS_FEM_POSTING_GT GLE) GFB
644 ON (
645 FB.CREATED_BY_OBJECT_ID = GFB.CREATED_BY_OBJECT_ID
646 AND FB.CREATED_BY_REQUEST_ID = GFB.CREATED_BY_REQUEST_ID
647 AND FB.CREATION_ROW_SEQUENCE = GFB.CREATION_ROW_SEQUENCE)
648 WHEN MATCHED THEN UPDATE SET
649 FB.xtd_balance_e = FB.xtd_balance_e + GFB.xtd_balance_e,
650 FB.ptd_credit_balance_e = FB.ptd_credit_balance_e + GFB.ptd_credit_balance_e,
651 FB.ptd_debit_balance_e = FB.ptd_debit_balance_e + GFB.ptd_debit_balance_e,
652 FB.ytd_balance_e = FB.ytd_balance_e + GFB.ytd_balance_e,
653 FB.ytd_credit_balance_e = FB.ytd_credit_balance_e + GFB.ytd_credit_balance_e,
654 FB.ytd_debit_balance_e = FB.ytd_debit_balance_e + GFB.ytd_debit_balance_e
655 WHEN NOT MATCHED THEN INSERT
656 (
657 FB.DATASET_CODE,
658 FB.CAL_PERIOD_ID,
659 FB.CREATION_ROW_SEQUENCE,
660 FB.SOURCE_SYSTEM_CODE,
661 FB.LEDGER_ID,
662 FB.COMPANY_COST_CENTER_ORG_ID,
663 FB.CURRENCY_CODE,
664 FB.CURRENCY_TYPE_CODE,
665 FB.LINE_ITEM_ID,
666 FB.ENTITY_ID,
667 FB.INTERCOMPANY_ID,
668 FB.CREATED_BY_REQUEST_ID,
669 FB.CREATED_BY_OBJECT_ID,
670 FB.LAST_UPDATED_BY_REQUEST_ID,
671 FB.LAST_UPDATED_BY_OBJECT_ID,
672 FB.XTD_BALANCE_E,
673 FB.YTD_BALANCE_E,
674 FB.PTD_DEBIT_BALANCE_E,
675 FB.PTD_CREDIT_BALANCE_E,
676 FB.YTD_DEBIT_BALANCE_E,
677 FB.YTD_CREDIT_BALANCE_E
678 )
679 VALUES
680 (
681 GFB.DATASET_CODE,
682 GFB.CAL_PERIOD_ID,
683 GFB.CREATION_ROW_SEQUENCE,
684 GFB.SOURCE_SYSTEM_CODE,
685 GFB.LEDGER_ID,
686 GFB.COMPANY_COST_CENTER_ORG_ID,
687 GFB.CURRENCY_CODE,
688 'TOTAL',
689 GFB.LINE_ITEM_ID,
690 GFB.ENTITY_ID,
691 GFB.INTERCOMPANY_ID,
692 GFB.CREATED_BY_REQUEST_ID,
693 GFB.CREATED_BY_OBJECT_ID,
694 GFB.LAST_UPDATED_BY_REQUEST_ID,
695 GFB.LAST_UPDATED_BY_OBJECT_ID,
696 GFB.XTD_BALANCE_E,
697 GFB.YTD_BALANCE_E,
698 GFB.PTD_DEBIT_BALANCE_E,
699 GFB.PTD_CREDIT_BALANCE_E,
700 GFB.YTD_DEBIT_BALANCE_E,
701 GFB.YTD_CREDIT_BALANCE_E);
702
703 END IF; -- p_mode
704
705
706 INSERT
707 INTO GCS_FEM_CONTRIBUTIONS_H(
708 DATASET_CODE,
709 CAL_PERIOD_ID,
710 CREATED_BY_OBJECT_ID,
711 CREATION_ROW_SEQUENCE,
712 ENTRY_ID,
713 CREATION_DATE,
714 CREATED_BY,
715 LAST_UPDATE_DATE,
716 LAST_UPDATED_BY,
717 LAST_UPDATE_LOGIN )
718 SELECT
719 p_hier_dataset_code,
720 p_cal_period_id,
721 P_object_id,
722 GFPG.sequence_num,
723 GFPG.entry_id,
724 sysdate,
725 l_user_id,
726 sysdate,
727 l_user_id,
728 l_login_id
729 FROM GCS_FEM_POSTING_GT GFPG;
730
731 IF (statementloglevel >= runtimeloglevel ) THEN
732 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST.PROCESS_MERGE.rowcount ', to_char(SQL%ROWCOUNT));
733 END IF;
734
735 -- If there are any rows processed, then register to FEM_DATA_LOCATIONS
736 IF (SQL%ROWCOUNT <> 0) THEN
737 FEM_DIMENSION_UTIL_PKG.Register_Data_Location
738 (P_REQUEST_ID => l_req_id,
739 P_OBJECT_ID => p_object_id,
740 P_TABLE_NAME => 'FEM_BALANCES',
741 P_LEDGER_ID => p_ledger_id,
742 P_CAL_PER_ID => p_cal_period_id,
743 P_DATASET_CD => p_hier_dataset_code,
744 P_SOURCE_CD => g_src_sys_code,
745 P_LOAD_STATUS => 'COMPLETE');
746
747 END IF;
748
749 IF (procedureloglevel >= runtimeloglevel ) THEN
750 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_MERGE.end', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
751 END IF;
752
753 -- FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success || 'PROCESS_MERGE' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
754
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 IF (unexpectedloglevel >= runtimeloglevel ) THEN
758 FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', 'GCS_NO_DATA_FOUND');
759 END IF;
760 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
761 -- 'PROCESS_MERGE' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
762 retcode := '0';
763 errbuf := 'GCS_NO_DATA_FOUND';
764 RAISE NO_DATA_FOUND;
765
766 WHEN OTHERS THEN
767 errbuf := substr( SQLERRM, 1, 2000);
768 IF (unexpectedloglevel >= runtimeloglevel ) THEN
769 FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', errbuf);
770 END IF;
771 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
772 -- 'PROCESS_MERGE' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
773 retcode := '0';
774 RAISE;
775
776 END Process_Merge;
777
778 PROCEDURE Gcs_Fem_Post (
779 errbuf OUT NOCOPY VARCHAR2,
780 retcode OUT NOCOPY VARCHAR2,
781 p_run_name VARCHAR2,
782 p_hierarchy_id NUMBER,
783 p_balance_type_code VARCHAR2,
784 p_category_code VARCHAR2,
785 p_cons_entity_id NUMBER,
786 p_child_entity_id NUMBER,
787 p_cal_period_id NUMBER,
788 p_undo VARCHAR2,
789 p_xlate VARCHAR2,
790 p_run_detail_id NUMBER,
791 p_mode VARCHAR2,
792 p_entry_id NUMBER,
793 p_hier_dataset_code NUMBER) IS
794
795 l_ledger_id NUMBER;
796 l_cal_period_info GCS_UTILITY_PKG.r_cal_period_info;
797 l_cal_period_year NUMBER;
798 l_object_id NUMBER;
799 module VARCHAR2(30) := 'GCS_FEM_POST';
800
801 --Bugfix 5646770: Flag to state whether entity is topmost
802 l_topmost_entity_flag VARCHAR2(1) := 'N';
803
804 --Bugfix 5704055: Delete Translated Balances at the Same time as Aggregated Balances
805 l_entity_id NUMBER;
806
807 BEGIN
808
809 runtimeLogLevel := FND_LOG.g_current_runtime_level;
810
811 IF (procedureloglevel >= runtimeloglevel ) THEN
812 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST.begin' || GCS_UTILITY_PKG.g_module_enter, to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
813 END IF;
814 IF (statementloglevel >= runtimeloglevel ) THEN
815 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', 'p_run_name = ' || p_run_name);
816 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', 'p_hierarchy_id = ' || to_char(p_hierarchy_id));
817 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', 'p_balance_type_code = ' || p_balance_type_code);
818 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', 'p_mode = ' || p_mode);
819 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', 'p_category_code = ' || p_category_code);
820 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', 'p_entry_id = ' || to_char(p_entry_id));
821 END IF;
822
823 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter || 'GCS_FEM_POST' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
824
825 -- get ledger_id
826 SELECT fem_ledger_id
827 INTO l_ledger_id
828 FROM GCS_HIERARCHIES_B
829 WHERE hierarchy_id = p_hierarchy_id;
830
831 -- Get current and previous period information.
832 GCS_UTILITY_PKG.get_cal_period_details(p_cal_period_id, l_cal_period_info);
833
834 l_cal_period_year := l_cal_period_info.cal_period_year;
835
836 -- Get object_id
837 SELECT associated_object_id
838 INTO l_object_id
839 FROM GCS_CATEGORIES_B
840 WHERE category_code = p_category_code;
841
842 -- Bugfix 5646770: Add check to determine if its the topmost entity
843 IF (p_category_code = 'AGGREGATION') THEN
844 SELECT DECODE(top_entity_id, p_cons_entity_id, 'Y', 'N')
845 INTO l_topmost_entity_flag
846 FROM gcs_hierarchies_b
847 WHERE hierarchy_id = p_hierarchy_id;
848 END IF;
849
850 BEGIN
851
852 -- Delete data from FEM_BALANCES for UNDO mode
853 -- Bugfix 5704055: This mode will only be called when removing Data Prep and Aggregation Rows
854 IF p_undo = 'Y' AND p_entry_id IS NULL THEN
855 SELECT child_entity_id
856 INTO l_entity_id
857 FROM gcs_cons_eng_run_dtls
858 WHERE run_detail_id = p_run_detail_id;
859
860 DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
861 WHERE dataset_code = p_hier_dataset_code
862 AND cal_period_id = p_cal_period_id
863 AND ledger_id = l_ledger_id
867
864 AND created_by_object_id = l_object_id
865 AND source_system_code = g_src_sys_code
866 AND entity_id = l_entity_id;
868 IF (statementloglevel >= runtimeloglevel ) THEN
869 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.undo.balrowcount ', to_char(SQL%ROWCOUNT));
870 END IF;
871 --Bugfix 5704055: Removed Deletion from GCS_FEM_CONTRIBUTIONS_H
872 --This mode will only be called for removal of Translation Rows
873 ELSIF p_undo = 'Y' AND p_entry_id IS NOT NULL THEN
874 SELECT entity_id
875 INTO l_entity_id
876 FROM gcs_entry_headers
877 WHERE entry_id = p_entry_id;
878
879 DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
880 WHERE dataset_code = p_hier_dataset_code
881 AND cal_period_id = p_cal_period_id
882 AND ledger_id = l_ledger_id
883 AND created_by_object_id = l_object_id
884 AND source_system_code = g_src_sys_code
885 AND entity_id = l_entity_id;
886
887 IF (statementloglevel >= runtimeloglevel ) THEN
888 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.undo.balrowcount ', to_char(SQL%ROWCOUNT));
889 END IF;
890 --Bugfix 5704055: Removed Deletion from GCS_FEM_CONTRIBUTIONS_H
891 END IF;
892 EXCEPTION
893 WHEN OTHERS THEN
894 NULL;
895 END;
896
897 IF p_mode = 'I' THEN
898 process_insert( p_hier_dataset_code => p_hier_dataset_code,
899 p_object_id => l_object_id,
900 p_category_code => p_category_code,
901 p_cons_entity_id => p_cons_entity_id,
902 p_child_entity_id => p_child_entity_id,
903 p_cal_period_id => p_cal_period_id,
904 p_cal_period_year => l_cal_period_year,
905 p_ledger_id => l_ledger_id,
906 p_run_name => p_run_name,
907 p_run_detail_id => p_run_detail_id,
908 p_entry_id => p_entry_id,
909 p_undo => p_undo,
910 p_xlate => p_xlate,
911 --Bugfix 5646770: Added parameter for topmost entity flag
912 p_topmost_entity_flag => l_topmost_entity_flag,
913 errbuf => errbuf,
914 retcode => retcode);
915
916 retcode := '1';
917 ELSIF (p_mode = 'M' OR p_mode = 'D') THEN
918 process_merge(
919 p_hier_dataset_code,
920 p_mode,
921 l_object_id,
922 p_category_code,
923 p_cons_entity_id,
924 p_child_entity_id,
925 p_cal_period_id,
926 l_cal_period_year,
927 l_ledger_id,
928 p_run_name,
929 p_run_detail_id,
930 p_entry_id,
931 p_undo,
932 p_xlate,
933 errbuf,
934 retcode);
935 retcode := '1';
936 END IF;
937
938
939 IF (procedureloglevel >= runtimeloglevel ) THEN
940 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST.end', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
941 END IF;
942 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
943 -- 'gcs_fem_post' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
944 EXCEPTION
945 WHEN NO_DATA_FOUND THEN
946 IF (unexpectedloglevel >= runtimeloglevel ) THEN
947 FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', 'GCS_NO_DATA_FOUND');
948 END IF;
949 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
950 -- 'GCS_FEM_POST' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
951 retcode := '0';
952 errbuf := 'GCS_NO_DATA_FOUND';
953 RAISE NO_DATA_FOUND;
954
955 WHEN no_proc_data_err THEN
956 retcode := gcs_utility_pkg.g_ret_sts_warn;
957 errbuf := 'No processing data found.';
958 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
959 fnd_log.STRING (fnd_log.level_error,
960 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST',
961 gcs_utility_pkg.g_module_failure
962 || ' '
963 || errbuf
964 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
965 );
966 END IF;
967
968 WHEN OTHERS THEN
969 errbuf := substr( SQLERRM, 1, 2000);
970 IF (unexpectedloglevel >= runtimeloglevel ) THEN
971 FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST', errbuf);
972 END IF;
973 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
974 -- 'GCS_FEM_POST' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
975 retcode := '0';
976 RAISE;
977
978 END Gcs_Fem_Post;
979
983 p_hierarchy_id NUMBER,
980 PROCEDURE Gcs_Fem_Delete(
981 errbuf OUT NOCOPY VARCHAR2,
982 retcode OUT NOCOPY VARCHAR2,
984 p_balance_type_code VARCHAR2,
985 p_cal_period_id NUMBER,
986 p_entity_type VARCHAR2,
987 p_entity_id NUMBER,
988 p_hier_dataset_code NUMBER) IS
989 l_ledger_id NUMBER;
990 l_objects_id DBMS_SQL.NUMBER_TABLE;
991 l_oper_entity_id NUMBER;
992 l_elim_entity_id NUMBER;
993 g_oper_entity_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').attribute_id;
994 g_elim_entity_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').attribute_id;
995 g_oper_entity_ver NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').version_id;
996 g_elim_entity_ver NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').version_id;
997 BEGIN
998
999 IF (procedureloglevel >= runtimeloglevel ) THEN
1000 FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE.begin' || GCS_UTILITY_PKG.g_module_enter, to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
1001 END IF;
1002 IF (statementloglevel >= runtimeloglevel ) THEN
1003 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_hierarchy_id = ' || to_char(p_hierarchy_id));
1004 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_cal_period_id = ' || to_char(p_cal_period_id));
1005 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_balance_type_code = ' || p_balance_type_code);
1006 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_entity_id = ' || to_char(p_entity_id));
1007 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_entity_type = ' || p_entity_type);
1008 END IF;
1009
1010 -- Get the ledger_id
1011 SELECT ghb.fem_ledger_id
1012 INTO l_ledger_id
1013 FROM gcs_hierarchies_b ghb
1014 WHERE ghb.hierarchy_id = p_hierarchy_id;
1015
1016 -- 'E' is for consolidation entities
1017 IF p_entity_type = 'E' THEN
1018 BEGIN
1019 -- Get the operating entity
1020 SELECT nvl(dim_attribute_numeric_member, -1)
1021 INTO l_oper_entity_id
1022 FROM fem_entities_attr
1023 WHERE entity_id = p_entity_id
1024 AND version_id = g_oper_entity_ver
1025 AND attribute_id = g_oper_entity_attr;
1026 EXCEPTION
1027 WHEN no_data_found THEN
1028 l_oper_entity_id := -1;
1029 END;
1030
1031 -- Get the elim entity
1032 SELECT dim_attribute_numeric_member
1033 INTO l_elim_entity_id
1034 FROM fem_entities_attr
1035 WHERE entity_id = p_entity_id
1036 AND version_id = g_elim_entity_ver
1037 AND attribute_id = g_elim_entity_attr;
1038
1039 BEGIN
1040 SELECT associated_object_id
1041 BULK COLLECT INTO l_objects_id
1042 FROM gcs_categories_b
1043 WHERE category_type_code IN ('ELIMINATION_RULE', 'CONSOLIDATION_RULE')
1044 AND target_entity_code IN ('PARENT', 'ELIMINATION');
1045 EXCEPTION
1046 WHEN no_data_found THEN
1047 RETURN;
1048 END;
1049
1050 -- Delete data from FEM_BALANCES for both the operating and elim entity
1051 --Bugfix 5704055: Added hints for the deletion
1052 FORALL i in l_objects_id.FIRST..l_objects_id.LAST
1053 DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
1054 WHERE dataset_code = p_hier_dataset_code
1055 AND cal_period_id = p_cal_period_id
1056 AND source_system_code = g_src_sys_code
1057 AND ledger_id = l_ledger_id
1058 AND entity_id IN (l_oper_entity_id, l_elim_entity_id)
1059 AND created_by_object_id = l_objects_id(i);
1060
1061 ELSE
1062 BEGIN
1063 SELECT associated_object_id
1064 BULK COLLECT INTO l_objects_id
1065 FROM gcs_categories_b
1066 WHERE category_type_code IN ('ELIMINATION_RULE', 'CONSOLIDATION_RULE')
1067 AND target_entity_code = 'CHILD';
1068 EXCEPTION
1069 WHEN no_data_found THEN
1070 RETURN;
1071 END;
1072
1073 -- Delete data from FEM_BALANCES for the operating entity
1074 --Bugfix 5704055: Added hints for the deletion
1075 FORALL i in l_objects_id.FIRST..l_objects_id.LAST
1076 DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
1077 WHERE dataset_code = p_hier_dataset_code
1078 AND cal_period_id = p_cal_period_id
1079 AND source_system_code = g_src_sys_code
1080 AND ledger_id = l_ledger_id
1081 AND entity_id = p_entity_id
1082 AND created_by_object_id = l_objects_id(i);
1083
1084 END IF;
1085
1086 IF (statementloglevel >= runtimeloglevel ) THEN
1087 FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE.rowcount ', to_char(SQL%ROWCOUNT));
1088 END IF;
1089
1090 EXCEPTION
1091 WHEN NO_DATA_FOUND THEN
1092 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1093 FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'GCS_NO_DATA_FOUND');
1094 END IF;
1095 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1096 -- 'GCS_FEM_DELETE' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1097 retcode := '0';
1098 errbuf := 'GCS_NO_DATA_FOUND';
1099 RAISE NO_DATA_FOUND;
1100
1101 WHEN OTHERS THEN
1102 errbuf := substr( SQLERRM, 1, 2000);
1103 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1104 FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', errbuf);
1105 END IF;
1106 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1107 -- 'GCS_FEM_DELETE' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1108 retcode := '0';
1109 RAISE;
1110
1111 END Gcs_Fem_Delete;
1112
1113 END GCS_DYN_FEM_POSTING_PKG;
1114