[Home] [Help]
PACKAGE BODY: APPS.GCS_BUILD_FEM_POSTING_PKG
Source
1 PACKAGE BODY GCS_BUILD_FEM_POSTING_PKG AS
2 /* $Header: gcsfempdb.pls 120.8 2007/03/27 20:25:46 skamdar noship $ */
3 --
4 -- Package
5 -- Create_Package
6 -- Purpose
7 -- Creates GCS_DYN_FEM_POSTING_PKG
8 -- History
9 -- 12-MAR-04 R Goyal Created
10 --
11 --
12
13 --
14 -- Public procedures
15 --
16 PROCEDURE Create_Package IS
17
18 -- row number to be used in dynamically creating the package
19 r NUMBER := 1;
20 body VARCHAR2(10000);
21 body_len NUMBER;
22 curr_pos NUMBER;
23 line_num NUMBER := 1;
24
25 err VARCHAR2(2000);
26
27 BEGIN
28
29 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
30 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'GCS_BUILD_FEM_POSTING_PKG' || '.' || 'CREATE_PACKAGE',
31 GCS_UTILITY_PKG.g_module_enter || 'CREATE_PACKAGE' ||
32 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
33 END IF;
34 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter || 'CREATE_PACKAGE' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
35
36
37 -- Create the package body
38 body:=
39 'CREATE OR REPLACE PACKAGE BODY GCS_DYN_FEM_POSTING_PKG AS
40
41 -- Store the log level
42 runtimeLogLevel NUMBER := FND_LOG.g_current_runtime_level;
43 statementLogLevel CONSTANT NUMBER := FND_LOG.level_statement;
44 procedureLogLevel CONSTANT NUMBER := FND_LOG.level_procedure;
45 exceptionLogLevel CONSTANT NUMBER := FND_LOG.level_exception;
46 errorLogLevel CONSTANT NUMBER := FND_LOG.level_error;
47 unexpectedLogLevel CONSTANT NUMBER := FND_LOG.level_unexpected;
48
49 g_src_sys_code NUMBER := GCS_UTILITY_PKG.g_gcs_source_system_code;
50 g_dimension_attr_info gcs_utility_pkg.t_hash_dimension_attr_info
51 := gcs_utility_pkg.g_dimension_attr_info;
52
53 no_proc_data_err EXCEPTION;
54
55
56 ';
57 curr_pos := 1;
58 body_len := LENGTH(body);
59 WHILE curr_pos <= body_len LOOP
60 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
61 curr_pos := curr_pos + g_line_size;
62 r := r + 1;
63 END LOOP;
64
65 body:=
66 '
67 PROCEDURE Populate_GT_Table(
68 p_category_code VARCHAR2,
69 p_cons_entity_id NUMBER,
70 p_child_entity_id NUMBER,
71 p_run_name VARCHAR2,
72 p_run_detail_id NUMBER,
73 p_entry_id NUMBER,
74 p_cal_period_year NUMBER,
75 errbuf IN OUT NOCOPY VARCHAR2,
76 retcode IN OUT NOCOPY VARCHAR2 ) IS
77
78 l_recur_entry_flag VARCHAR2(1);
79 l_entry_id_list DBMS_SQL.NUMBER_TABLE;
80 l_entity_id_list DBMS_SQL.NUMBER_TABLE;
81 l_currency_code_list DBMS_SQL.VARCHAR2_TABLE;
82
83 BEGIN
84
85 IF (procedureloglevel >= runtimeloglevel ) THEN
86 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.POPULATE_GT_TABLE.begin'', to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
87 END IF;
88
89 IF (p_entry_id IS NOT NULL OR p_run_detail_id IS NOT NULL) THEN
90 IF (p_entry_id IS NOT NULL) THEN
91 SELECT entry_id, entity_id, currency_code
92 BULK COLLECT INTO l_entry_id_list, l_entity_id_list, l_currency_code_list
93 FROM GCS_ENTRY_HEADERS
94 WHERE entry_id = p_entry_id;
95
96 ELSE -- p_run_detail_id is not null
97 SELECT ghd.entry_id, ghd.entity_id, ghd.currency_code
98 BULK COLLECT INTO l_entry_id_list, l_entity_id_list, l_currency_code_list
99 FROM GCS_CONS_ENG_RUN_DTLS GCR,
100 GCS_ENTRY_HEADERS GHD
101 WHERE GCR.run_detail_id = p_run_detail_id
102 AND GHD.entry_id in ( GCR.entry_id, GCR.stat_entry_id);
103 END IF;
104
105 IF (SQL%ROWCOUNT = 0) THEN
106 RAISE no_proc_data_err;
107 END IF;
108
109 FORALL i IN l_entry_id_list.FIRST..l_entry_id_list.LAST
110 INSERT
111 INTO GCS_FEM_POSTING_GT(
112 ENTRY_ID,
113 SEQUENCE_NUM,
114 CURRENCY_CODE,
115 COMPANY_COST_CENTER_ORG_ID,
116 INTERCOMPANY_ID,
117 ENTITY_ID,
118 LINE_ITEM_ID,
119 ';
120 curr_pos := 1;
121 body_len := LENGTH(body);
122 WHILE curr_pos <= body_len LOOP
123 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
124 curr_pos := curr_pos + g_line_size;
125 r := r + 1;
126 END LOOP;
127
128 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' ', GCS_UTILITY_PKG.g_nl, '', r);
129
130 body:=
131 ' XTD_BALANCE_E,
132 YTD_BALANCE_E,
133 PTD_DEBIT_BALANCE_E,
134 PTD_CREDIT_BALANCE_E,
135 YTD_DEBIT_BALANCE_E,
136 YTD_CREDIT_BALANCE_E
137 )
138 SELECT
139 l_entry_id_list(i),
140 GCS_FEM_BAL_S.nextval,
141 l_currency_code_list(i),
142 GLE.COMPANY_COST_CENTER_ORG_ID,
143 GLE.INTERCOMPANY_ID,
144 l_entity_id_list(i),
145 GLE.LINE_ITEM_ID,
146 ';
147
148 curr_pos := 1;
149 body_len := LENGTH(body);
150 WHILE curr_pos <= body_len LOOP
151 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
152 curr_pos := curr_pos + g_line_size;
153 r := r + 1;
154 END LOOP;
155
156 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
157 body:=
158 ' nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E),
159 GLE.YTD_BALANCE_E,
160 nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E),
161 nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E),
162 GLE.YTD_DEBIT_BALANCE_E,
163 GLE.YTD_CREDIT_BALANCE_E
164 FROM GCS_ENTRY_LINES GLE
165 WHERE GLE.entry_id = l_entry_id_list(i);
166
167 -- The following is happening when consolidating adjustments on operating entities
168 ELSIF (p_child_entity_id is not null) THEN
169
170 BEGIN
171 SELECT ''Y''
172 INTO l_recur_entry_flag
173 FROM dual
174 WHERE EXISTS
175 (SELECT 1
176 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
177 GCS_ENTRY_HEADERS GHD
178 WHERE GCERD.run_name = p_run_name
179 AND GCERD.consolidation_entity_id = p_cons_entity_id
180 AND GCERD.child_entity_id = p_child_entity_id
181 AND GCERD.category_code = p_category_code
182 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
183 AND (GHD.end_cal_period_id IS NULL OR ghd.start_cal_period_id <> ghd.end_cal_period_id));
184
185 -- bug fix 5080422: swap position of line_item_id and intercompany_id
186 INSERT
187 INTO GCS_FEM_POSTING_GT(
188 ENTRY_ID,
189 SEQUENCE_NUM,
190 CURRENCY_CODE,
191 COMPANY_COST_CENTER_ORG_ID,
192 INTERCOMPANY_ID,
193 ENTITY_ID,
194 LINE_ITEM_ID,
195 ';
196 curr_pos := 1;
197 body_len := LENGTH(body);
198 WHILE curr_pos <= body_len LOOP
199 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
200 curr_pos := curr_pos + g_line_size;
201 r := r + 1;
202 END LOOP;
203
204 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' ', GCS_UTILITY_PKG.g_nl, '', r);
205
206 body:=
207 ' XTD_BALANCE_E,
208 YTD_BALANCE_E,
209 PTD_DEBIT_BALANCE_E,
210 PTD_CREDIT_BALANCE_E,
211 YTD_DEBIT_BALANCE_E,
212 YTD_CREDIT_BALANCE_E
213 )
214 SELECT
215 GFB.ENTRY_ID,
216 GCS_FEM_BAL_S.nextval,
217 GFB.CURRENCY_CODE,
218 GFB.COMPANY_COST_CENTER_ORG_ID,
219 GFB.INTERCOMPANY_ID,
220 GFB.ENTITY_ID,
221 GFB.LINE_ITEM_ID,
222 ';
223
224 curr_pos := 1;
225 body_len := LENGTH(body);
226 WHILE curr_pos <= body_len LOOP
227 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
228 curr_pos := curr_pos + g_line_size;
229 r := r + 1;
230 END LOOP;
231
232 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GFB.', GCS_UTILITY_PKG.g_nl, '', r);
233
234 body := '
235 GFB.XTD_BALANCE_E,
236 GFB.YTD_BALANCE_E,
237 GFB.PTD_DEBIT_BALANCE_E,
238 GFB.PTD_CREDIT_BALANCE_E,
239 GFB.YTD_DEBIT_BALANCE_E,
240 GFB.YTD_CREDIT_BALANCE_E
241 FROM (
242 SELECT max(GHD.entry_id) entry_id,
243 GHD.currency_code,
244 GLE.COMPANY_COST_CENTER_ORG_ID,
245 GLE.INTERCOMPANY_ID,
246 GLE.LINE_ITEM_ID,
247 ';
248
249 curr_pos := 1;
250 body_len := LENGTH(body);
251 WHILE curr_pos <= body_len LOOP
252 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
253 curr_pos := curr_pos + g_line_size;
254 r := r + 1;
255 END LOOP;
256
257 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
258
259 body:=
260 ' max(GHD.ENTITY_ID) ENTITY_ID,
261 sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
262 sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
263 sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
264 sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
265 sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
266 sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
267 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
268 GCS_ENTRY_HEADERS GHD,
269 GCS_ENTRY_LINES GLE
270 WHERE GCERD.run_name = p_run_name
271 AND GCERD.consolidation_entity_id = p_cons_entity_id
272 AND GCERD.child_entity_id = p_child_entity_id
273 AND GCERD.category_code = p_category_code
274 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
275 AND GLE.entry_id = GHD.entry_id
276 AND ((GHD.start_cal_period_id = GHD.end_cal_period_id)
277 OR ((GHD.start_cal_period_id <> GHD.end_cal_period_id OR GHD.end_cal_period_id is NULL)
278 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'')
279 OR (p_cal_period_year < GHD.year_to_apply_re AND GLE.line_type_code <> ''CALCULATED''))))
280 GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
281 ';
282
283 curr_pos := 1;
284 body_len := LENGTH(body);
285 WHILE curr_pos <= body_len LOOP
286 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
287 curr_pos := curr_pos + g_line_size;
288 r := r + 1;
289 END LOOP;
290
291 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
292
293 body:= 'GLE.intercompany_id ) GFB;
294
295 EXCEPTION
296 WHEN NO_DATA_FOUND THEN
297
298 -- bug fix 5080422: swap position of line_item_id and intercompany_id
299 INSERT
300 INTO GCS_FEM_POSTING_GT(
301 ENTRY_ID,
302 SEQUENCE_NUM,
303 CURRENCY_CODE,
304 COMPANY_COST_CENTER_ORG_ID,
305 INTERCOMPANY_ID,
306 ENTITY_ID,
307 LINE_ITEM_ID,
308 ';
309 curr_pos := 1;
310 body_len := LENGTH(body);
311 WHILE curr_pos <= body_len LOOP
312 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
313 curr_pos := curr_pos + g_line_size;
314 r := r + 1;
315 END LOOP;
316
317 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' ', GCS_UTILITY_PKG.g_nl, '', r);
318
319 body:=
320 ' XTD_BALANCE_E,
321 YTD_BALANCE_E,
322 PTD_DEBIT_BALANCE_E,
323 PTD_CREDIT_BALANCE_E,
324 YTD_DEBIT_BALANCE_E,
325 YTD_CREDIT_BALANCE_E
326 )
327 SELECT
328 GFB.ENTRY_ID,
329 GCS_FEM_BAL_S.nextval,
330 GFB.CURRENCY_CODE,
331 GFB.COMPANY_COST_CENTER_ORG_ID,
332 GFB.INTERCOMPANY_ID,
333 GFB.ENTITY_ID,
334 GFB.LINE_ITEM_ID,
335 ';
336
337 curr_pos := 1;
338 body_len := LENGTH(body);
339 WHILE curr_pos <= body_len LOOP
340 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
341 curr_pos := curr_pos + g_line_size;
342 r := r + 1;
343 END LOOP;
344
345 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GFB.', GCS_UTILITY_PKG.g_nl, '', r);
346
347 body := '
348 GFB.XTD_BALANCE_E,
349 GFB.YTD_BALANCE_E,
350 GFB.PTD_DEBIT_BALANCE_E,
351 GFB.PTD_CREDIT_BALANCE_E,
352 GFB.YTD_DEBIT_BALANCE_E,
353 GFB.YTD_CREDIT_BALANCE_E
354 FROM (
355 SELECT max(GHD.entry_id) entry_id,
356 GHD.currency_code,
357 GLE.COMPANY_COST_CENTER_ORG_ID,
358 GLE.INTERCOMPANY_ID,
359 GLE.LINE_ITEM_ID,
360 ';
361
362 curr_pos := 1;
363 body_len := LENGTH(body);
364 WHILE curr_pos <= body_len LOOP
365 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
366 curr_pos := curr_pos + g_line_size;
367 r := r + 1;
368 END LOOP;
369
370 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
371
372 body:=
373 ' max(GHD.ENTITY_ID) ENTITY_ID,
374 sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
375 sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
376 sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
377 sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
378 sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
379 sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
380 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
381 GCS_ENTRY_HEADERS GHD,
382 GCS_ENTRY_LINES GLE
383 WHERE GCERD.run_name = p_run_name
384 AND GCERD.consolidation_entity_id = p_cons_entity_id
385 AND GCERD.child_entity_id = p_child_entity_id
386 AND GCERD.category_code = p_category_code
387 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
388 AND GLE.entry_id = GHD.entry_id
389 GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
390 ';
391
392 curr_pos := 1;
393 body_len := LENGTH(body);
394 WHILE curr_pos <= body_len LOOP
395 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
396 curr_pos := curr_pos + g_line_size;
397 r := r + 1;
398 END LOOP;
399
400 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
401
402 body:= 'GLE.intercompany_id ) GFB;
403 END;
404
408 BEGIN
405 -- The following is happening when consolidating adjustments on consolidation entities
406 ELSE
407
409 SELECT ''Y''
410 INTO l_recur_entry_flag
411 FROM dual
412 WHERE EXISTS
413 (SELECT 1
414 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
415 GCS_ENTRY_HEADERS GHD
416 WHERE GCERD.run_name = p_run_name
417 AND GCERD.consolidation_entity_id = p_cons_entity_id
418 AND GCERD.category_code = p_category_code
419 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
420 AND (GHD.end_cal_period_id IS NULL OR ghd.start_cal_period_id <> ghd.end_cal_period_id));
421
422 -- bug fix 5080422: swap position of line_item_id and intercompany_id
423 INSERT
424 INTO GCS_FEM_POSTING_GT(
425 ENTRY_ID,
426 SEQUENCE_NUM,
427 CURRENCY_CODE,
428 COMPANY_COST_CENTER_ORG_ID,
429 INTERCOMPANY_ID,
430 ENTITY_ID,
431 LINE_ITEM_ID,
432 ';
433 curr_pos := 1;
434 body_len := LENGTH(body);
435 WHILE curr_pos <= body_len LOOP
436 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
437 curr_pos := curr_pos + g_line_size;
438 r := r + 1;
439 END LOOP;
440
441 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' ', GCS_UTILITY_PKG.g_nl, '', r);
442
443 body:=
444 ' XTD_BALANCE_E,
445 YTD_BALANCE_E,
446 PTD_DEBIT_BALANCE_E,
447 PTD_CREDIT_BALANCE_E,
448 YTD_DEBIT_BALANCE_E,
449 YTD_CREDIT_BALANCE_E
450 )
451 SELECT
452 GFB.ENTRY_ID,
453 GCS_FEM_BAL_S.nextval,
454 GFB.CURRENCY_CODE,
455 GFB.COMPANY_COST_CENTER_ORG_ID,
456 GFB.INTERCOMPANY_ID,
457 GFB.ENTITY_ID,
458 GFB.LINE_ITEM_ID,
459 ';
460
461 curr_pos := 1;
462 body_len := LENGTH(body);
463 WHILE curr_pos <= body_len LOOP
464 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
465 curr_pos := curr_pos + g_line_size;
466 r := r + 1;
467 END LOOP;
468
469 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GFB.', GCS_UTILITY_PKG.g_nl, '', r);
470
471 body := '
472 GFB.XTD_BALANCE_E,
473 GFB.YTD_BALANCE_E,
474 GFB.PTD_DEBIT_BALANCE_E,
475 GFB.PTD_CREDIT_BALANCE_E,
476 GFB.YTD_DEBIT_BALANCE_E,
477 GFB.YTD_CREDIT_BALANCE_E
478 FROM (
479 SELECT max(GHD.entry_id) entry_id,
480 GHD.currency_code,
481 GLE.COMPANY_COST_CENTER_ORG_ID,
482 GLE.INTERCOMPANY_ID,
483 GLE.LINE_ITEM_ID,
484 ';
485
486 curr_pos := 1;
487 body_len := LENGTH(body);
488 WHILE curr_pos <= body_len LOOP
489 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
490 curr_pos := curr_pos + g_line_size;
491 r := r + 1;
492 END LOOP;
493
494 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
495
496 body:=
497 ' max(GHD.ENTITY_ID) ENTITY_ID,
498 sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
499 sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
500 sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
501 sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
502 sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
503 sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
504 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
505 GCS_ENTRY_HEADERS GHD,
506 GCS_ENTRY_LINES GLE
507 WHERE GCERD.run_name = p_run_name
508 AND GCERD.consolidation_entity_id = p_cons_entity_id
509 AND GCERD.category_code = p_category_code
510 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
511 AND GLE.entry_id = GHD.entry_id
512 AND ((GHD.start_cal_period_id = GHD.end_cal_period_id)
513 OR ((GHD.start_cal_period_id <> GHD.end_cal_period_id OR GHD.end_cal_period_id is NULL)
514 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'')
515 OR (p_cal_period_year < GHD.year_to_apply_re AND GLE.line_type_code <> ''CALCULATED''))))
516 GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
517 ';
518
519 curr_pos := 1;
520 body_len := LENGTH(body);
521 WHILE curr_pos <= body_len LOOP
522 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
523 curr_pos := curr_pos + g_line_size;
524 r := r + 1;
525 END LOOP;
526
530
527 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
528
529 body:= 'GLE.intercompany_id ) GFB;
531 EXCEPTION
532 WHEN NO_DATA_FOUND THEN
533
534 -- bug fix 5080422: swap position of line_item_id and intercompany_id
535 INSERT
536 INTO GCS_FEM_POSTING_GT(
537 ENTRY_ID,
538 SEQUENCE_NUM,
539 CURRENCY_CODE,
540 COMPANY_COST_CENTER_ORG_ID,
541 INTERCOMPANY_ID,
542 ENTITY_ID,
543 LINE_ITEM_ID,
544 ';
545 curr_pos := 1;
546 body_len := LENGTH(body);
547 WHILE curr_pos <= body_len LOOP
548 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
549 curr_pos := curr_pos + g_line_size;
550 r := r + 1;
551 END LOOP;
552
553 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' ', GCS_UTILITY_PKG.g_nl, '', r);
554
555 body:=
556 ' XTD_BALANCE_E,
557 YTD_BALANCE_E,
558 PTD_DEBIT_BALANCE_E,
559 PTD_CREDIT_BALANCE_E,
560 YTD_DEBIT_BALANCE_E,
561 YTD_CREDIT_BALANCE_E
562 )
563 SELECT
564 GFB.ENTRY_ID,
565 GCS_FEM_BAL_S.nextval,
566 GFB.CURRENCY_CODE,
567 GFB.COMPANY_COST_CENTER_ORG_ID,
568 GFB.INTERCOMPANY_ID,
569 GFB.ENTITY_ID,
570 GFB.LINE_ITEM_ID,
571 ';
572
573 curr_pos := 1;
574 body_len := LENGTH(body);
575 WHILE curr_pos <= body_len LOOP
576 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
577 curr_pos := curr_pos + g_line_size;
578 r := r + 1;
579 END LOOP;
580
581 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GFB.', GCS_UTILITY_PKG.g_nl, '', r);
582
583 body := '
584 GFB.XTD_BALANCE_E,
585 GFB.YTD_BALANCE_E,
586 GFB.PTD_DEBIT_BALANCE_E,
587 GFB.PTD_CREDIT_BALANCE_E,
588 GFB.YTD_DEBIT_BALANCE_E,
589 GFB.YTD_CREDIT_BALANCE_E
590 FROM (
591 SELECT max(GHD.entry_id) entry_id,
592 GHD.currency_code,
593 GLE.COMPANY_COST_CENTER_ORG_ID,
594 GLE.INTERCOMPANY_ID,
595 GLE.LINE_ITEM_ID,
596 ';
597
598 curr_pos := 1;
599 body_len := LENGTH(body);
600 WHILE curr_pos <= body_len LOOP
601 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
602 curr_pos := curr_pos + g_line_size;
603 r := r + 1;
604 END LOOP;
605
606 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
607
608 body:=
609 ' max(GHD.ENTITY_ID) ENTITY_ID,
610 sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
611 sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
612 sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
613 sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
614 sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
615 sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
616 FROM GCS_CONS_ENG_RUN_DTLS GCERD,
617 GCS_ENTRY_HEADERS GHD,
618 GCS_ENTRY_LINES GLE
619 WHERE GCERD.run_name = p_run_name
620 AND GCERD.consolidation_entity_id = p_cons_entity_id
621 AND GCERD.category_code = p_category_code
622 AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
623 AND GLE.entry_id = GHD.entry_id
624 GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
625 ';
626
627 curr_pos := 1;
628 body_len := LENGTH(body);
629 WHILE curr_pos <= body_len LOOP
630 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
631 curr_pos := curr_pos + g_line_size;
632 r := r + 1;
633 END LOOP;
634
635 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List(' GLE.', GCS_UTILITY_PKG.g_nl, '', r);
636
637 body:= 'GLE.intercompany_id ) GFB;
638
639 END;
640 END IF;
641
642 IF (procedureloglevel >= runtimeloglevel ) THEN
643 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.POPULATE_GT_TABLE.end'', to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
644 END IF;
645
646 END Populate_GT_Table;
647
648 ';
649 curr_pos := 1;
650 body_len := LENGTH(body);
651 WHILE curr_pos <= body_len LOOP
652 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
653 curr_pos := curr_pos + g_line_size;
654 r := r + 1;
655 END LOOP;
656
657 body:=
658 '
659 PROCEDURE Process_Insert( p_hier_dataset_code NUMBER,
660 p_object_id NUMBER,
661 p_category_code VARCHAR2,
662 p_cons_entity_id NUMBER,
663 p_child_entity_id NUMBER,
667 p_run_name VARCHAR2,
664 p_cal_period_id NUMBER,
665 p_cal_period_year NUMBER,
666 p_ledger_id NUMBER,
668 p_run_detail_id NUMBER,
669 p_entry_id NUMBER,
670 p_undo VARCHAR2,
671 p_xlate VARCHAR2,
672 --Bugfix 5646770: Added parameter for topmost entity flag
673 p_topmost_entity_flag VARCHAR2,
674 errbuf IN OUT NOCOPY VARCHAR2,
675 retcode IN OUT NOCOPY VARCHAR2
676 ) IS
677
678 l_req_id NUMBER := FND_GLOBAL.conc_request_id;
679 l_login_id NUMBER := FND_GLOBAL.login_id;
680 l_user_id NUMBER := FND_GLOBAL.user_id;
681 l_entries_id DBMS_SQL.number_table;
682 l_currencies_code DBMS_SQL.varchar2_table;
683 l_entities_id DBMS_SQL.number_table;
684
685 BEGIN
686
687 IF (procedureloglevel >= runtimeloglevel ) THEN
688 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT.begin'', to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
689 END IF;
690
691 Populate_Gt_Table(p_category_code => p_category_code,
692 p_cons_entity_id => p_cons_entity_id,
693 p_child_entity_id => p_child_entity_id,
694 p_run_name => p_run_name,
695 p_run_detail_id => p_run_detail_id,
696 p_entry_id => p_entry_id,
697 p_cal_period_year => p_cal_period_year,
698 errbuf => errbuf,
699 retcode => retcode);
700
701 INSERT INTO FEM_BALANCES(
702 DATASET_CODE,
703 CAL_PERIOD_ID,
704 CREATION_ROW_SEQUENCE,
705 SOURCE_SYSTEM_CODE,
706 LEDGER_ID,
707 COMPANY_COST_CENTER_ORG_ID,
708 CURRENCY_CODE,
709 CURRENCY_TYPE_CODE,
710 INTERCOMPANY_ID,
711 ENTITY_ID,
712 LINE_ITEM_ID,
713 ';
714 curr_pos := 1;
715 body_len := LENGTH(body);
716 WHILE curr_pos <= body_len LOOP
717 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
718 curr_pos := curr_pos + g_line_size;
719 r := r + 1;
720 END LOOP;
721
722 r := GCS_DYNAMIC_UTIL_PKG.Build_Fem_Comma_List(' ', GCS_UTILITY_PKG.g_nl, '', r, 'N');
723
724 body:=
725 ' CREATED_BY_REQUEST_ID,
726 CREATED_BY_OBJECT_ID,
727 LAST_UPDATED_BY_REQUEST_ID,
728 LAST_UPDATED_BY_OBJECT_ID,
729 XTD_BALANCE_E,
730 YTD_BALANCE_E,
731 PTD_DEBIT_BALANCE_E,
732 PTD_CREDIT_BALANCE_E,
733 YTD_DEBIT_BALANCE_E,
734 YTD_CREDIT_BALANCE_E,
735 --Bugfix 5646770: Added _F Columns for Top Most Entity
736 XTD_BALANCE_F,
737 YTD_BALANCE_F
738 )
739 SELECT
740 p_hier_dataset_code,
741 p_cal_period_id,
742 sequence_num,
743 g_src_sys_code,
744 p_ledger_id,
745 company_cost_center_org_id,
746 currency_code,
747 ''TOTAL'',
748 intercompany_id,
749 entity_id,
750 line_item_id,
751 ';
752
753 curr_pos := 1;
754 body_len := LENGTH(body);
755 WHILE curr_pos <= body_len LOOP
756 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
757 curr_pos := curr_pos + g_line_size;
758 r := r + 1;
759 END LOOP;
760 r := GCS_DYNAMIC_UTIL_PKG.Build_Fem_Comma_List(' ', GCS_UTILITY_PKG.g_nl, '', r, 'Y');
761 body:=
762 ' l_req_id,
763 p_object_id,
764 l_req_id,
765 p_object_id,
766 XTD_BALANCE_E,
767 YTD_BALANCE_E,
768 PTD_DEBIT_BALANCE_E,
769 PTD_CREDIT_BALANCE_E,
770 YTD_DEBIT_BALANCE_E,
771 YTD_CREDIT_BALANCE_E,
772 --Bugfix 5646770: Added _F Columns for topmost entity
773 DECODE(p_topmost_entity_flag, ''Y'', XTD_BALANCE_E, NULL) XTD_BALANCE_F,
774 DECODE(p_topmost_entity_flag, ''Y'', YTD_BALANCE_E, NULL) YTD_BALANCE_F
775 FROM GCS_FEM_POSTING_GT;
776
777 IF (procedureloglevel >= runtimeloglevel ) THEN
778 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT.end'', to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
779 END IF;
780
781 EXCEPTION
782 WHEN NO_DATA_FOUND THEN
783 IF (unexpectedloglevel >= runtimeloglevel ) THEN
784 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT'', ''GCS_NO_DATA_FOUND'');
785 END IF;
786 retcode := ''0'';
787 errbuf := ''GCS_NO_DATA_FOUND'';
788 RAISE NO_DATA_FOUND;
789
790 WHEN OTHERS THEN
791 errbuf := substr( SQLERRM, 1, 2000);
792 IF (unexpectedloglevel >= runtimeloglevel ) THEN
793 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT'', errbuf);
797
794 END IF;
795 retcode := ''0'';
796 RAISE;
798 END Process_Insert;
799 ';
800 curr_pos := 1;
801 body_len := LENGTH(body);
802 WHILE curr_pos <= body_len LOOP
803 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
804 curr_pos := curr_pos + g_line_size;
805 r := r + 1;
806 END LOOP;
807
808 body :=
809 'PROCEDURE Process_Merge(p_hier_dataset_code NUMBER,
810 p_mode VARCHAR2,
811 p_object_id NUMBER,
812 p_category_code VARCHAR2,
813 p_cons_entity_id NUMBER,
814 p_child_entity_id NUMBER,
815 p_cal_period_id NUMBER,
816 p_cal_period_year NUMBER,
817 p_ledger_id NUMBER,
818 p_run_name VARCHAR2,
819 p_run_detail_id NUMBER,
820 p_entry_id NUMBER,
821 p_undo VARCHAR2,
822 p_xlate VARCHAR2,
823 errbuf IN OUT NOCOPY VARCHAR2,
824 retcode IN OUT NOCOPY VARCHAR2 ) IS
825
826 l_req_id NUMBER := FND_GLOBAL.conc_request_id;
827 l_login_id NUMBER := FND_GLOBAL.login_id;
828 l_user_id NUMBER := FND_GLOBAL.user_id;
829
830 BEGIN
831
832 IF (procedureloglevel >= runtimeloglevel ) THEN
833 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_MERGE.begin'', to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
834 END IF;
835
836 Populate_Gt_Table(p_category_code => p_category_code,
837 p_cons_entity_id => p_cons_entity_id,
838 p_child_entity_id => p_child_entity_id,
839 p_run_name => p_run_name,
840 p_run_detail_id => p_run_detail_id,
841 p_entry_id => p_entry_id,
842 p_cal_period_year => p_cal_period_year,
843 errbuf => errbuf,
844 retcode => retcode);
845
846 IF (p_mode = ''M'') THEN
847 MERGE INTO FEM_BALANCES FB
848 USING(
849 SELECT
850 p_hier_dataset_code DATASET_CODE,
851 p_cal_period_id CAL_PERIOD_ID,
852 g_src_sys_code SOURCE_SYSTEM_CODE,
853 p_ledger_id LEDGER_ID,
854 GLE.SEQUENCE_NUM CREATION_ROW_SEQUENCE,
855 l_req_id CREATED_BY_REQUEST_ID,
856 p_object_id CREATED_BY_OBJECT_ID,
857 l_req_id LAST_UPDATED_BY_REQUEST_ID,
858 p_object_id LAST_UPDATED_BY_OBJECT_ID,
859 GLE.COMPANY_COST_CENTER_ORG_ID COMPANY_COST_CENTER_ORG_ID,
860 GLE.LINE_ITEM_ID LINE_ITEM_ID,
861 GLE.INTERCOMPANY_ID INTERCOMPANY_ID,
862 ';
863
864 curr_pos := 1;
865 body_len := LENGTH(body);
866 WHILE curr_pos <= body_len LOOP
867 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
868 curr_pos := curr_pos + g_line_size;
869 r := r + 1;
870 END LOOP;
871
872 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List('GLE.', GCS_UTILITY_PKG.g_nl, '', r);
873
874 body :=
875 ' GLE.CURRENCY_CODE CURRENCY_CODE,
876 GLE.ENTITY_ID ENTITY_ID,
877 GLE.XTD_BALANCE_E,
878 GLE.YTD_BALANCE_E YTD_BALANCE_E,
879 GLE.PTD_DEBIT_BALANCE_E,
880 GLE.PTD_CREDIT_BALANCE_E,
881 GLE.YTD_DEBIT_BALANCE_E YTD_DEBIT_BALANCE_E,
882 GLE.YTD_CREDIT_BALANCE_E YTD_CREDIT_BALANCE_E
883 FROM GCS_FEM_POSTING_GT GLE) GFB
884 ';
885
886 curr_pos := 1;
887 body_len := LENGTH(body);
888 WHILE curr_pos <= body_len LOOP
889 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
890 curr_pos := curr_pos + g_line_size;
891 r := r + 1;
892 END LOOP;
893
894 body:=
895 ' ON (
896 FB.CREATED_BY_OBJECT_ID = GFB.CREATED_BY_OBJECT_ID
897 AND FB.CREATED_BY_REQUEST_ID = GFB.CREATED_BY_REQUEST_ID
898 AND FB.CREATION_ROW_SEQUENCE = GFB.CREATION_ROW_SEQUENCE)
899 WHEN MATCHED THEN UPDATE SET
900 FB.xtd_balance_e = GFB.xtd_balance_e,
901 FB.ptd_credit_balance_e = GFB.ptd_credit_balance_e,
902 FB.ptd_debit_balance_e = GFB.ptd_debit_balance_e,
903 FB.ytd_balance_e = GFB.ytd_balance_e,
904 FB.ytd_credit_balance_e = GFB.ytd_credit_balance_e,
905 FB.ytd_debit_balance_e = GFB.ytd_debit_balance_e
906 WHEN NOT MATCHED THEN INSERT
907 (
908 FB.DATASET_CODE,
909 FB.CAL_PERIOD_ID,
910 FB.CREATION_ROW_SEQUENCE,
911 FB.SOURCE_SYSTEM_CODE,
912 FB.LEDGER_ID,
913 FB.COMPANY_COST_CENTER_ORG_ID,
914 FB.CURRENCY_CODE,
915 FB.CURRENCY_TYPE_CODE,
916 FB.LINE_ITEM_ID,
917 FB.ENTITY_ID,
918 FB.INTERCOMPANY_ID,
919 ';
920
921 curr_pos := 1;
922 body_len := LENGTH(body);
923 WHILE curr_pos <= body_len LOOP
924 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
925 curr_pos := curr_pos + g_line_size;
926 r := r + 1;
927 END LOOP;
928
929 r := GCS_DYNAMIC_UTIL_PKG.Build_Fem_Comma_List('FB.', GCS_UTILITY_PKG.g_nl, '', r, 'N');
930
931 body:= 'FB.CREATED_BY_REQUEST_ID,
932 FB.CREATED_BY_OBJECT_ID,
936 FB.YTD_BALANCE_E,
933 FB.LAST_UPDATED_BY_REQUEST_ID,
934 FB.LAST_UPDATED_BY_OBJECT_ID,
935 FB.XTD_BALANCE_E,
937 FB.PTD_DEBIT_BALANCE_E,
938 FB.PTD_CREDIT_BALANCE_E,
939 FB.YTD_DEBIT_BALANCE_E,
940 FB.YTD_CREDIT_BALANCE_E
941 )
942 VALUES
943 (
944 GFB.DATASET_CODE,
945 GFB.CAL_PERIOD_ID,
946 GFB.CREATION_ROW_SEQUENCE,
947 GFB.SOURCE_SYSTEM_CODE,
948 GFB.LEDGER_ID,
949 GFB.COMPANY_COST_CENTER_ORG_ID,
950 GFB.CURRENCY_CODE,
951 ''TOTAL'',
952 GFB.LINE_ITEM_ID,
953 GFB.ENTITY_ID,
954 GFB.INTERCOMPANY_ID,
955 ';
956
957 curr_pos := 1;
958 body_len := LENGTH(body);
959 WHILE curr_pos <= body_len LOOP
960 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
961 curr_pos := curr_pos + g_line_size;
962 r := r + 1;
963 END LOOP;
964
965 r := GCS_DYNAMIC_UTIL_PKG.Build_Fem_Comma_List('GFB.', GCS_UTILITY_PKG.g_nl, '', r, 'Y');
966
967 body := ' GFB.CREATED_BY_REQUEST_ID,
968 GFB.CREATED_BY_OBJECT_ID,
969 GFB.LAST_UPDATED_BY_REQUEST_ID,
970 GFB.LAST_UPDATED_BY_OBJECT_ID,
971 GFB.XTD_BALANCE_E,
972 GFB.YTD_BALANCE_E,
973 GFB.PTD_DEBIT_BALANCE_E,
974 GFB.PTD_CREDIT_BALANCE_E,
975 GFB.YTD_DEBIT_BALANCE_E,
976 GFB.YTD_CREDIT_BALANCE_E);
977
978 ELSE
979 MERGE INTO FEM_BALANCES FB
980 USING(
981 SELECT
982 p_hier_dataset_code DATASET_CODE,
983 p_cal_period_id CAL_PERIOD_ID,
984 g_src_sys_code SOURCE_SYSTEM_CODE,
985 p_ledger_id LEDGER_ID,
986 GLE.SEQUENCE_NUM CREATION_ROW_SEQUENCE,
987 l_req_id CREATED_BY_REQUEST_ID,
988 p_object_id CREATED_BY_OBJECT_ID,
989 l_req_id LAST_UPDATED_BY_REQUEST_ID,
990 p_object_id LAST_UPDATED_BY_OBJECT_ID,
991 GLE.COMPANY_COST_CENTER_ORG_ID COMPANY_COST_CENTER_ORG_ID,
992 GLE.LINE_ITEM_ID LINE_ITEM_ID,
993 GLE.INTERCOMPANY_ID INTERCOMPANY_ID,
994 ';
995
996 curr_pos := 1;
997 body_len := LENGTH(body);
998 WHILE curr_pos <= body_len LOOP
999 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1000 curr_pos := curr_pos + g_line_size;
1001 r := r + 1;
1002 END LOOP;
1003
1004 r := GCS_DYNAMIC_UTIL_PKG.Build_Comma_List('GLE.', GCS_UTILITY_PKG.g_nl, '', r);
1005
1006 body :=
1007 ' GLE.CURRENCY_CODE CURRENCY_CODE,
1008 GLE.ENTITY_ID ENTITY_ID,
1009 GLE.XTD_BALANCE_E,
1010 GLE.YTD_BALANCE_E YTD_BALANCE_E,
1011 GLE.PTD_DEBIT_BALANCE_E,
1012 GLE.PTD_CREDIT_BALANCE_E,
1013 GLE.YTD_DEBIT_BALANCE_E YTD_DEBIT_BALANCE_E,
1014 GLE.YTD_CREDIT_BALANCE_E YTD_CREDIT_BALANCE_E
1015 FROM GCS_FEM_POSTING_GT GLE) GFB
1016 ';
1017
1018 curr_pos := 1;
1019 body_len := LENGTH(body);
1020 WHILE curr_pos <= body_len LOOP
1021 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1022 curr_pos := curr_pos + g_line_size;
1023 r := r + 1;
1024 END LOOP;
1025
1026 body:=
1027 ' ON (
1028 FB.CREATED_BY_OBJECT_ID = GFB.CREATED_BY_OBJECT_ID
1029 AND FB.CREATED_BY_REQUEST_ID = GFB.CREATED_BY_REQUEST_ID
1030 AND FB.CREATION_ROW_SEQUENCE = GFB.CREATION_ROW_SEQUENCE)
1031 WHEN MATCHED THEN UPDATE SET
1032 FB.xtd_balance_e = FB.xtd_balance_e + GFB.xtd_balance_e,
1033 FB.ptd_credit_balance_e = FB.ptd_credit_balance_e + GFB.ptd_credit_balance_e,
1034 FB.ptd_debit_balance_e = FB.ptd_debit_balance_e + GFB.ptd_debit_balance_e,
1035 FB.ytd_balance_e = FB.ytd_balance_e + GFB.ytd_balance_e,
1036 FB.ytd_credit_balance_e = FB.ytd_credit_balance_e + GFB.ytd_credit_balance_e,
1037 FB.ytd_debit_balance_e = FB.ytd_debit_balance_e + GFB.ytd_debit_balance_e
1038 WHEN NOT MATCHED THEN INSERT
1039 (
1040 FB.DATASET_CODE,
1041 FB.CAL_PERIOD_ID,
1042 FB.CREATION_ROW_SEQUENCE,
1043 FB.SOURCE_SYSTEM_CODE,
1044 FB.LEDGER_ID,
1045 FB.COMPANY_COST_CENTER_ORG_ID,
1046 FB.CURRENCY_CODE,
1047 FB.CURRENCY_TYPE_CODE,
1048 FB.LINE_ITEM_ID,
1049 FB.ENTITY_ID,
1050 FB.INTERCOMPANY_ID,
1051 ';
1052
1053 curr_pos := 1;
1054 body_len := LENGTH(body);
1055 WHILE curr_pos <= body_len LOOP
1056 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1057 curr_pos := curr_pos + g_line_size;
1058 r := r + 1;
1059 END LOOP;
1060
1061 r := GCS_DYNAMIC_UTIL_PKG.Build_Fem_Comma_List('FB.', GCS_UTILITY_PKG.g_nl, '', r, 'N');
1062
1063 body:= 'FB.CREATED_BY_REQUEST_ID,
1064 FB.CREATED_BY_OBJECT_ID,
1065 FB.LAST_UPDATED_BY_REQUEST_ID,
1066 FB.LAST_UPDATED_BY_OBJECT_ID,
1067 FB.XTD_BALANCE_E,
1068 FB.YTD_BALANCE_E,
1069 FB.PTD_DEBIT_BALANCE_E,
1070 FB.PTD_CREDIT_BALANCE_E,
1071 FB.YTD_DEBIT_BALANCE_E,
1072 FB.YTD_CREDIT_BALANCE_E
1073 )
1074 VALUES
1075 (
1076 GFB.DATASET_CODE,
1077 GFB.CAL_PERIOD_ID,
1078 GFB.CREATION_ROW_SEQUENCE,
1079 GFB.SOURCE_SYSTEM_CODE,
1080 GFB.LEDGER_ID,
1081 GFB.COMPANY_COST_CENTER_ORG_ID,
1082 GFB.CURRENCY_CODE,
1083 ''TOTAL'',
1084 GFB.LINE_ITEM_ID,
1085 GFB.ENTITY_ID,
1086 GFB.INTERCOMPANY_ID,
1087 ';
1088
1089 curr_pos := 1;
1093 curr_pos := curr_pos + g_line_size;
1090 body_len := LENGTH(body);
1091 WHILE curr_pos <= body_len LOOP
1092 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1094 r := r + 1;
1095 END LOOP;
1096
1097 r := GCS_DYNAMIC_UTIL_PKG.Build_Fem_Comma_List('GFB.', GCS_UTILITY_PKG.g_nl, '', r, 'Y');
1098
1099 body := ' GFB.CREATED_BY_REQUEST_ID,
1100 GFB.CREATED_BY_OBJECT_ID,
1101 GFB.LAST_UPDATED_BY_REQUEST_ID,
1102 GFB.LAST_UPDATED_BY_OBJECT_ID,
1103 GFB.XTD_BALANCE_E,
1104 GFB.YTD_BALANCE_E,
1105 GFB.PTD_DEBIT_BALANCE_E,
1106 GFB.PTD_CREDIT_BALANCE_E,
1107 GFB.YTD_DEBIT_BALANCE_E,
1108 GFB.YTD_CREDIT_BALANCE_E);
1109
1110 END IF; -- p_mode
1111
1112
1113 INSERT
1114 INTO GCS_FEM_CONTRIBUTIONS_H(
1115 DATASET_CODE,
1116 CAL_PERIOD_ID,
1117 CREATED_BY_OBJECT_ID,
1118 CREATION_ROW_SEQUENCE,
1119 ENTRY_ID,
1120 CREATION_DATE,
1121 CREATED_BY,
1122 LAST_UPDATE_DATE,
1123 LAST_UPDATED_BY,
1124 LAST_UPDATE_LOGIN )
1125 SELECT
1126 p_hier_dataset_code,
1127 p_cal_period_id,
1128 P_object_id,
1129 GFPG.sequence_num,
1130 GFPG.entry_id,
1131 sysdate,
1132 l_user_id,
1133 sysdate,
1134 l_user_id,
1135 l_login_id
1136 FROM GCS_FEM_POSTING_GT GFPG;
1137
1138 IF (statementloglevel >= runtimeloglevel ) THEN
1139 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST.PROCESS_MERGE.rowcount '', to_char(SQL%ROWCOUNT));
1140 END IF;
1141
1142 -- If there are any rows processed, then register to FEM_DATA_LOCATIONS
1143 IF (SQL%ROWCOUNT <> 0) THEN
1144 FEM_DIMENSION_UTIL_PKG.Register_Data_Location
1145 (P_REQUEST_ID => l_req_id,
1146 P_OBJECT_ID => p_object_id,
1147 P_TABLE_NAME => ''FEM_BALANCES'',
1148 P_LEDGER_ID => p_ledger_id,
1149 P_CAL_PER_ID => p_cal_period_id,
1150 P_DATASET_CD => p_hier_dataset_code,
1151 P_SOURCE_CD => g_src_sys_code,
1152 P_LOAD_STATUS => ''COMPLETE'');
1153
1154 END IF;
1155
1156 IF (procedureloglevel >= runtimeloglevel ) THEN
1157 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_MERGE.end'', to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
1158 END IF;
1159
1160 -- FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success || ''PROCESS_MERGE'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1161
1162 EXCEPTION
1163 WHEN NO_DATA_FOUND THEN
1164 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1165 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', ''GCS_NO_DATA_FOUND'');
1166 END IF;
1167 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1168 -- ''PROCESS_MERGE'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1169 retcode := ''0'';
1170 errbuf := ''GCS_NO_DATA_FOUND'';
1171 RAISE NO_DATA_FOUND;
1172
1173 WHEN OTHERS THEN
1174 errbuf := substr( SQLERRM, 1, 2000);
1175 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1176 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', errbuf);
1177 END IF;
1178 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1179 -- ''PROCESS_MERGE'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1180 retcode := ''0'';
1181 RAISE;
1182
1183 END Process_Merge;
1184 ';
1185
1186 curr_pos := 1;
1187 body_len := LENGTH(body);
1188 WHILE curr_pos <= body_len LOOP
1189 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1190 curr_pos := curr_pos + g_line_size;
1191 r := r + 1;
1192 END LOOP;
1193
1194 body:=
1195 '
1196 PROCEDURE Gcs_Fem_Post (
1197 errbuf OUT NOCOPY VARCHAR2,
1198 retcode OUT NOCOPY VARCHAR2,
1199 p_run_name VARCHAR2,
1200 p_hierarchy_id NUMBER,
1201 p_balance_type_code VARCHAR2,
1202 p_category_code VARCHAR2,
1203 p_cons_entity_id NUMBER,
1204 p_child_entity_id NUMBER,
1205 p_cal_period_id NUMBER,
1206 p_undo VARCHAR2,
1207 p_xlate VARCHAR2,
1208 p_run_detail_id NUMBER,
1209 p_mode VARCHAR2,
1210 p_entry_id NUMBER,
1211 p_hier_dataset_code NUMBER) IS
1212
1213 l_ledger_id NUMBER;
1214 l_cal_period_info GCS_UTILITY_PKG.r_cal_period_info;
1215 l_cal_period_year NUMBER;
1216 l_object_id NUMBER;
1217 module VARCHAR2(30) := ''GCS_FEM_POST'';
1218
1219 --Bugfix 5646770: Flag to state whether entity is topmost
1220 l_topmost_entity_flag VARCHAR2(1) := ''N'';
1221
1225 BEGIN
1222 --Bugfix 5704055: Delete Translated Balances at the Same time as Aggregated Balances
1223 l_entity_id NUMBER;
1224
1226
1227 runtimeLogLevel := FND_LOG.g_current_runtime_level;
1228
1229 IF (procedureloglevel >= runtimeloglevel ) THEN
1230 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''));
1231 END IF;
1232 IF (statementloglevel >= runtimeloglevel ) THEN
1233 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', ''p_run_name = '' || p_run_name);
1234 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', ''p_hierarchy_id = '' || to_char(p_hierarchy_id));
1235 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', ''p_balance_type_code = '' || p_balance_type_code);
1236 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', ''p_mode = '' || p_mode);
1237 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', ''p_category_code = '' || p_category_code);
1238 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', ''p_entry_id = '' || to_char(p_entry_id));
1239 END IF;
1240
1241 --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''));
1242 ';
1243
1244 curr_pos := 1;
1245 body_len := LENGTH(body);
1246 WHILE curr_pos <= body_len LOOP
1247 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1248 curr_pos := curr_pos + g_line_size;
1249 r := r + 1;
1250 END LOOP;
1251
1252 body:=
1253 '
1254 -- get ledger_id
1255 SELECT fem_ledger_id
1256 INTO l_ledger_id
1257 FROM GCS_HIERARCHIES_B
1258 WHERE hierarchy_id = p_hierarchy_id;
1259
1260 -- Get current and previous period information.
1261 GCS_UTILITY_PKG.get_cal_period_details(p_cal_period_id, l_cal_period_info);
1262
1263 l_cal_period_year := l_cal_period_info.cal_period_year;
1264
1265 -- Get object_id
1266 SELECT associated_object_id
1267 INTO l_object_id
1268 FROM GCS_CATEGORIES_B
1269 WHERE category_code = p_category_code;
1270
1271 -- Bugfix 5646770: Add check to determine if its the topmost entity
1272 IF (p_category_code = ''AGGREGATION'') THEN
1273 SELECT DECODE(top_entity_id, p_cons_entity_id, ''Y'', ''N'')
1274 INTO l_topmost_entity_flag
1275 FROM gcs_hierarchies_b
1276 WHERE hierarchy_id = p_hierarchy_id;
1277 END IF;
1278
1279 BEGIN
1280
1281 -- Delete data from FEM_BALANCES for UNDO mode
1282 -- Bugfix 5704055: This mode will only be called when removing Data Prep and Aggregation Rows
1283 IF p_undo = ''Y'' AND p_entry_id IS NULL THEN
1284 SELECT child_entity_id
1285 INTO l_entity_id
1286 FROM gcs_cons_eng_run_dtls
1287 WHERE run_detail_id = p_run_detail_id;
1288
1289 DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
1290 WHERE dataset_code = p_hier_dataset_code
1291 AND cal_period_id = p_cal_period_id
1292 AND ledger_id = l_ledger_id
1293 AND created_by_object_id = l_object_id
1294 AND source_system_code = g_src_sys_code
1295 AND entity_id = l_entity_id;
1296
1297 IF (statementloglevel >= runtimeloglevel ) THEN
1298 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.undo.balrowcount '', to_char(SQL%ROWCOUNT));
1299 END IF;
1300 --Bugfix 5704055: Removed Deletion from GCS_FEM_CONTRIBUTIONS_H
1301 --This mode will only be called for removal of Translation Rows
1302 ELSIF p_undo = ''Y'' AND p_entry_id IS NOT NULL THEN
1303 SELECT entity_id
1304 INTO l_entity_id
1305 FROM gcs_entry_headers
1306 WHERE entry_id = p_entry_id;
1307
1308 DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
1309 WHERE dataset_code = p_hier_dataset_code
1310 AND cal_period_id = p_cal_period_id
1311 AND ledger_id = l_ledger_id
1312 AND created_by_object_id = l_object_id
1313 AND source_system_code = g_src_sys_code
1314 AND entity_id = l_entity_id;
1315
1316 IF (statementloglevel >= runtimeloglevel ) THEN
1317 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.undo.balrowcount '', to_char(SQL%ROWCOUNT));
1318 END IF;
1319 --Bugfix 5704055: Removed Deletion from GCS_FEM_CONTRIBUTIONS_H
1320 END IF;
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 NULL;
1324 END;
1325
1326 IF p_mode = ''I'' THEN
1327 process_insert( p_hier_dataset_code => p_hier_dataset_code,
1328 p_object_id => l_object_id,
1329 p_category_code => p_category_code,
1330 p_cons_entity_id => p_cons_entity_id,
1331 p_child_entity_id => p_child_entity_id,
1332 p_cal_period_id => p_cal_period_id,
1336 p_run_detail_id => p_run_detail_id,
1333 p_cal_period_year => l_cal_period_year,
1334 p_ledger_id => l_ledger_id,
1335 p_run_name => p_run_name,
1337 p_entry_id => p_entry_id,
1338 p_undo => p_undo,
1339 p_xlate => p_xlate,
1340 --Bugfix 5646770: Added parameter for topmost entity flag
1341 p_topmost_entity_flag => l_topmost_entity_flag,
1342 errbuf => errbuf,
1343 retcode => retcode);
1344
1345 retcode := ''1'';
1346 ELSIF (p_mode = ''M'' OR p_mode = ''D'') THEN
1347 process_merge(
1348 p_hier_dataset_code,
1349 p_mode,
1350 l_object_id,
1351 p_category_code,
1352 p_cons_entity_id,
1353 p_child_entity_id,
1354 p_cal_period_id,
1355 l_cal_period_year,
1356 l_ledger_id,
1357 p_run_name,
1358 p_run_detail_id,
1359 p_entry_id,
1360 p_undo,
1361 p_xlate,
1362 errbuf,
1363 retcode);
1364 retcode := ''1'';
1365 END IF;
1366
1367
1368 IF (procedureloglevel >= runtimeloglevel ) THEN
1369 FND_LOG.STRING(procedureloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST.end'', to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));
1370 END IF;
1371 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
1372 -- ''gcs_fem_post'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1373 ';
1374 curr_pos := 1;
1375 body_len := LENGTH(body);
1376 WHILE curr_pos <= body_len LOOP
1377 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1378 curr_pos := curr_pos + g_line_size;
1379 r := r + 1;
1380 END LOOP;
1381
1382 body:=
1383 ' EXCEPTION
1384 WHEN NO_DATA_FOUND THEN
1385 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1386 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', ''GCS_NO_DATA_FOUND'');
1387 END IF;
1388 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1389 -- ''GCS_FEM_POST'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1390 retcode := ''0'';
1391 errbuf := ''GCS_NO_DATA_FOUND'';
1392 RAISE NO_DATA_FOUND;
1393
1394 WHEN no_proc_data_err THEN
1395 retcode := gcs_utility_pkg.g_ret_sts_warn;
1396 errbuf := ''No processing data found.'';
1397 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
1398 fnd_log.STRING (fnd_log.level_error,
1399 ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'',
1400 gcs_utility_pkg.g_module_failure
1401 || '' ''
1402 || errbuf
1403 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1404 );
1405 END IF;
1406
1407 WHEN OTHERS THEN
1408 errbuf := substr( SQLERRM, 1, 2000);
1409 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1410 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_POST'', errbuf);
1411 END IF;
1412 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1413 -- ''GCS_FEM_POST'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1414 retcode := ''0'';
1415 RAISE;
1416
1417 END Gcs_Fem_Post;
1418
1419 PROCEDURE Gcs_Fem_Delete(
1420 errbuf OUT NOCOPY VARCHAR2,
1421 retcode OUT NOCOPY VARCHAR2,
1422 p_hierarchy_id NUMBER,
1423 p_balance_type_code VARCHAR2,
1424 p_cal_period_id NUMBER,
1425 p_entity_type VARCHAR2,
1426 p_entity_id NUMBER,
1427 p_hier_dataset_code NUMBER) IS
1428 l_ledger_id NUMBER;
1429 l_objects_id DBMS_SQL.NUMBER_TABLE;
1430 l_oper_entity_id NUMBER;
1431 l_elim_entity_id NUMBER;
1432 g_oper_entity_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info(''ENTITY_ID-OPERATING_ENTITY'').attribute_id;
1433 g_elim_entity_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info(''ENTITY_ID-ELIMINATION_ENTITY'').attribute_id;
1434 g_oper_entity_ver NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info(''ENTITY_ID-OPERATING_ENTITY'').version_id;
1435 g_elim_entity_ver NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info(''ENTITY_ID-ELIMINATION_ENTITY'').version_id;
1436 BEGIN
1437
1438 IF (procedureloglevel >= runtimeloglevel ) THEN
1439 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''));
1440 END IF;
1441 IF (statementloglevel >= runtimeloglevel ) THEN
1442 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE'', ''p_hierarchy_id = '' || to_char(p_hierarchy_id));
1446 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE'', ''p_entity_type = '' || p_entity_type);
1443 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE'', ''p_cal_period_id = '' || to_char(p_cal_period_id));
1444 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE'', ''p_balance_type_code = '' || p_balance_type_code);
1445 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE'', ''p_entity_id = '' || to_char(p_entity_id));
1447 END IF;
1448
1449 ';
1450
1451 curr_pos := 1;
1452 body_len := LENGTH(body);
1453 WHILE curr_pos <= body_len LOOP
1454 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1455 curr_pos := curr_pos + g_line_size;
1456 r := r + 1;
1457 END LOOP;
1458 body:=
1459 ' -- Get the ledger_id
1460 SELECT ghb.fem_ledger_id
1461 INTO l_ledger_id
1462 FROM gcs_hierarchies_b ghb
1463 WHERE ghb.hierarchy_id = p_hierarchy_id;
1464
1465 -- ''E'' is for consolidation entities
1466 IF p_entity_type = ''E'' THEN
1467 BEGIN
1468 -- Get the operating entity
1469 SELECT nvl(dim_attribute_numeric_member, -1)
1470 INTO l_oper_entity_id
1471 FROM fem_entities_attr
1472 WHERE entity_id = p_entity_id
1473 AND version_id = g_oper_entity_ver
1474 AND attribute_id = g_oper_entity_attr;
1475 EXCEPTION
1476 WHEN no_data_found THEN
1477 l_oper_entity_id := -1;
1478 END;
1479
1480 -- Get the elim entity
1481 SELECT dim_attribute_numeric_member
1482 INTO l_elim_entity_id
1483 FROM fem_entities_attr
1484 WHERE entity_id = p_entity_id
1485 AND version_id = g_elim_entity_ver
1486 AND attribute_id = g_elim_entity_attr;
1487
1488 BEGIN
1489 SELECT associated_object_id
1490 BULK COLLECT INTO l_objects_id
1491 FROM gcs_categories_b
1492 WHERE category_type_code IN (''ELIMINATION_RULE'', ''CONSOLIDATION_RULE'')
1493 AND target_entity_code IN (''PARENT'', ''ELIMINATION'');
1494 EXCEPTION
1495 WHEN no_data_found THEN
1496 RETURN;
1497 END;
1498
1499 -- Delete data from FEM_BALANCES for both the operating and elim entity
1500 --Bugfix 5704055: Added hints for the deletion
1501 FORALL i in l_objects_id.FIRST..l_objects_id.LAST
1502 DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
1503 WHERE dataset_code = p_hier_dataset_code
1504 AND cal_period_id = p_cal_period_id
1505 AND source_system_code = g_src_sys_code
1506 AND ledger_id = l_ledger_id
1507 AND entity_id IN (l_oper_entity_id, l_elim_entity_id)
1508 AND created_by_object_id = l_objects_id(i);
1509
1510 ELSE
1511 BEGIN
1512 SELECT associated_object_id
1513 BULK COLLECT INTO l_objects_id
1514 FROM gcs_categories_b
1515 WHERE category_type_code IN (''ELIMINATION_RULE'', ''CONSOLIDATION_RULE'')
1516 AND target_entity_code = ''CHILD'';
1517 EXCEPTION
1518 WHEN no_data_found THEN
1519 RETURN;
1520 END;
1521
1522 -- Delete data from FEM_BALANCES for the operating entity
1523 --Bugfix 5704055: Added hints for the deletion
1524 FORALL i in l_objects_id.FIRST..l_objects_id.LAST
1525 DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
1526 WHERE dataset_code = p_hier_dataset_code
1527 AND cal_period_id = p_cal_period_id
1528 AND source_system_code = g_src_sys_code
1529 AND ledger_id = l_ledger_id
1530 AND entity_id = p_entity_id
1531 AND created_by_object_id = l_objects_id(i);
1532
1533 END IF;
1534
1535 IF (statementloglevel >= runtimeloglevel ) THEN
1536 FND_LOG.STRING(statementloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE.rowcount '', to_char(SQL%ROWCOUNT));
1537 END IF;
1538
1539 EXCEPTION
1540 WHEN NO_DATA_FOUND THEN
1541 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1542 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE'', ''GCS_NO_DATA_FOUND'');
1543 END IF;
1544 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1545 -- ''GCS_FEM_DELETE'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1546 retcode := ''0'';
1547 errbuf := ''GCS_NO_DATA_FOUND'';
1548 RAISE NO_DATA_FOUND;
1549
1550 WHEN OTHERS THEN
1551 errbuf := substr( SQLERRM, 1, 2000);
1552 IF (unexpectedloglevel >= runtimeloglevel ) THEN
1553 FND_LOG.STRING(unexpectedloglevel, ''gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE'', errbuf);
1554 END IF;
1555 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1556 -- ''GCS_FEM_DELETE'' || to_char(sysdate, '' DD-MON-YYYY HH:MI:SS''));
1557 retcode := ''0'';
1558 RAISE;
1559
1560 END Gcs_Fem_Delete;
1561
1562 END GCS_DYN_FEM_POSTING_PKG;
1563
1564 ';
1565 curr_pos := 1;
1566 body_len := LENGTH(body);
1567 WHILE curr_pos <= body_len LOOP
1568 ad_ddl.build_statement(SUBSTR(body, curr_pos, g_line_size),r);
1569 curr_pos := curr_pos + g_line_size;
1570 r := r + 1;
1571 END LOOP;
1572
1573 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username, 'GCS', 'GCS_DYN_FEM_POSTING_PKG',1, r - 1, 'FALSE', err);
1574
1575 -- dbms_output.put_line('Error' || AD_DDL.error_buf);
1576
1577 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1578 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'GCS_BUILD_FEM_POSTING_PKG' || '.' || 'CREATE_PACKAGE',
1579 GCS_UTILITY_PKG.g_module_success || 'Create package' ||
1580 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1581 END IF;
1582 -- FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success || 'CREATE PACKAGE' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1583
1584 EXCEPTION
1585 WHEN OTHERS THEN
1586 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1587 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1588 'GCS_BUILD_FEM_POSTING_PKG' || '.' || 'CREATE_PACKAGE',
1589 SUBSTR(SQLERRM, 1, 255));
1590 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1591 'GCS_BUILD_FEM_POSTING_PKG' || '.' || 'CREATE_PACKAGE',
1592 GCS_UTILITY_PKG.g_module_failure || 'CREATE_PACKAGE' ||
1593 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1594 END IF;
1595 -- FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
1596 -- 'CREATE_PACKAGE' || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
1597 END Create_Package;
1598
1599 END GCS_BUILD_FEM_POSTING_PKG;