[Home] [Help]
PACKAGE BODY: APPS.GCS_DATA_PREP_PKG
Source
1 PACKAGE BODY GCS_DATA_PREP_PKG AS
2 /* $Header: gcsdpb.pls 120.23 2007/11/14 17:09:37 smatam ship $ */
3 --
4 -- Exceptions
5 --
6 gcs_dp_invalid_hierarchy EXCEPTION;
7 gcs_dp_calc_re_failed EXCEPTION;
8 no_cons_rel_id_error EXCEPTION;
9 --
10 -- Private Global Variables
11 --
12 TYPE refcursor IS REF CURSOR;
13 -- A newline character. Included for convenience when writing long strings.
14 g_nl CONSTANT VARCHAR2(1) := '
15 ';
16 -- The API name
17 g_pkg_name CONSTANT VARCHAR2(30) := 'gcs.plsql.GCS_DATA_PREP_PKG';
18 -- get_dimension_text
19 -- Purpose
20 -- Create part of the dynamic sql.
21 -- Arguments
22 -- p_prefix string to be added to the start of each dimension.
23 -- p_suffix string to be added to the end of each dimension.
24 -- p_repeat_flag indicator of whether the dimension column to be appended after suffix
25 -- Notes
26 --
27 FUNCTION get_dimension_text(p_prefix VARCHAR2,
28 p_suffix VARCHAR2,
29 p_repeat_flag VARCHAR2) RETURN VARCHAR2 IS
30 l_index_column_name VARCHAR2(30);
31 l_dim_text VARCHAR2(5000);
32 BEGIN
33 fnd_file.put_line(fnd_file.log,
34 '============================================');
35 fnd_file.put_line(fnd_file.log,
36 'GCS_DATA_PREP_PKG::get_dimension_text -- Enter');
37 fnd_file.put_line(fnd_file.log, 'p_prefix := ' || p_prefix);
38 fnd_file.put_line(fnd_file.log, 'p_suffix := ' || p_suffix);
39 fnd_file.put_line(fnd_file.log, 'p_repeat_flag := ' || p_repeat_flag);
40 fnd_file.put_line(fnd_file.log, 'Beginning of parameters ');
41 fnd_file.put_line(fnd_file.log, 'End of parameters ');
42 l_index_column_name := gcs_utility_pkg.g_gcs_dimension_info.FIRST;
43 WHILE (l_index_column_name <= gcs_utility_pkg.g_gcs_dimension_info.LAST) LOOP
44 IF (l_index_column_name <> 'ENTITY_ID' AND
45 gcs_utility_pkg.g_gcs_dimension_info(l_index_column_name)
46 .required_for_gcs = 'Y') THEN
47 IF (l_dim_text IS NULL) THEN
48 IF (p_repeat_flag = 'Y') THEN
49 l_dim_text := p_prefix || l_index_column_name || p_suffix ||
50 l_index_column_name;
51 ELSE
52 l_dim_text := p_prefix || l_index_column_name || p_suffix || ', ';
53 END IF;
54 ELSE
55 IF (p_repeat_flag = 'Y') THEN
56 l_dim_text := l_dim_text || g_nl || p_prefix ||
57 l_index_column_name || p_suffix ||
58 l_index_column_name;
59 ELSE
60 l_dim_text := l_dim_text || p_prefix || l_index_column_name ||
61 p_suffix || ', ';
62 END IF;
63 END IF;
64 END IF;
65 l_index_column_name := gcs_utility_pkg.g_gcs_dimension_info.NEXT(l_index_column_name);
66 END LOOP;
67 fnd_file.put_line(fnd_file.log, 'l_dim_text := ' || l_dim_text);
68 fnd_file.put_line(fnd_file.log,
69 'GCS_DATA_PREP_PKG::get_dimension_text -- Exit');
70 fnd_file.put_line(fnd_file.log,
71 '============================================');
72 RETURN l_dim_text;
73 END get_dimension_text;
74
75 --
76 -- Procedure
77 -- CHECK_CALENDAR_MAP_REQUIRED
78 -- Purpose
79 -- This procedure determines if calendar period mapping is required or not
80 -- Arguments
81 -- p_entity_id Entity Identifier
82 -- p_target_cal_period_id Target Calendar Period Identifier
83 -- p_ledger_id Source Ledger Identifier
84 -- p_balance_type_code Balance Type
85 -- p_period_mapping_required Checks if Period Mapping is Required
86 -- Notes
87 --
88 PROCEDURE check_calendar_map_required(p_entity_id IN NUMBER,
89 p_target_cal_period_id IN NUMBER,
90 p_ledger_id IN NUMBER,
91 p_balance_type_code IN VARCHAR2,
92 p_period_mapping_required OUT NOCOPY VARCHAR2) IS
93
94 l_api_name VARCHAR2(30) := 'CHECK_CALENDAR_MAP_REQUIRED';
95 l_src_cal_period_id NUMBER;
96 l_src_calendar_id NUMBER;
97 l_src_dimension_grp_id NUMBER;
98 l_tgt_calendar_id NUMBER;
99 l_tgt_dimension_grp_id NUMBER;
100
101 BEGIN
102
103 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
104 fnd_log.STRING(fnd_log.level_procedure,
105 g_pkg_name || '.' || l_api_name || '.begin',
106 '<<Enter>>');
107 END IF;
108
109 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
110 fnd_log.STRING(fnd_log.level_statement,
111 g_pkg_name || '.' || l_api_name,
112 'Beginning of Parameter Listing');
113 fnd_log.STRING(fnd_log.level_statement,
114 g_pkg_name || '.' || l_api_name,
115 'Entity Id : ' || p_entity_id);
116 fnd_log.STRING(fnd_log.level_statement,
117 g_pkg_name || '.' || l_api_name,
118 'Target Calendar Period Id: ' ||
119 p_target_cal_period_id);
120 fnd_log.STRING(fnd_log.level_statement,
121 g_pkg_name || '.' || l_api_name,
122 'Ledger Id : ' || p_ledger_id);
123 fnd_log.STRING(fnd_log.level_statement,
124 g_pkg_name || '.' || l_api_name,
125 'End of Parameter Listing');
126 END IF;
127
128 SELECT gdsd.cal_period_id, fcpb.calendar_id, fcpb.dimension_group_id
129 INTO l_src_cal_period_id, l_src_calendar_id, l_src_dimension_grp_id
130 FROM gcs_data_sub_dtls gdsd, fem_cal_periods_b fcpb
131 WHERE gdsd.entity_id = p_entity_id
132 AND gdsd.balance_type_code = p_balance_type_code
133 AND gdsd.cal_period_id = fcpb.cal_period_id
134 AND ROWNUM < 2;
135
136 SELECT fcpb.calendar_id, fcpb.dimension_group_id
137 INTO l_tgt_calendar_id, l_tgt_dimension_grp_id
138 FROM fem_cal_periods_b fcpb
139 WHERE fcpb.cal_period_id = p_target_cal_period_id;
140
141 IF ((l_src_calendar_id = l_tgt_calendar_id) AND
142 (l_src_dimension_grp_id = l_tgt_dimension_grp_id)) THEN
143 p_period_mapping_required := 'N';
144 ELSE
145 p_period_mapping_required := 'Y';
146 END IF;
147
148 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
149 fnd_log.STRING(fnd_log.level_procedure,
150 g_pkg_name || '.' || l_api_name || '.end',
151 '<<Exit>>');
152 END IF;
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 --no data found in gcs_data_sub_dtls..calendar period mapping is not required
157 p_period_mapping_required := 'N';
158 END;
159
160 -- Bugfix 6037112: Added check to see if data preparation is required
161 -- Procedure
162 -- EXPLODE_CALENDAR_MAP
163 -- Purpose
164 -- This procedure takes the TARGET_CAL_PERIOD_ID, AND using the source entity information determines
165 -- which source period maps to which target information. If there is no information found in GCS_CAL_PERIOD_MAPS
166 -- between the source AND target a dummy record will be insert into GCS_TEMP_CAL_PERIOD_MAPS WHERE the
167 -- source_cal_period_id = target_cal_period_id.
168 -- Arguments
169 -- p_source_ledger_id Source ledger Identifier
170 -- p_target_cal_period_id Target Calendar Period Identifier
171 -- p_cal_period_record gcs_utility_pkg.r_cal_period_info
172 -- p_year_end_values_match Detects if Source/Target Calendar Year Ends Match
173 -- p_entity_id Entity Identifier
174 -- p_prior_cal_period_id Previous Period
175 -- Notes
176 --
177 PROCEDURE explode_calendar_map(p_source_ledger_id IN NUMBER,
178 p_target_cal_period_id IN NUMBER,
179 p_balance_type_code IN VARCHAR2,
180 p_cal_period_record OUT NOCOPY gcs_utility_pkg.r_cal_period_info,
181 p_year_end_values_match OUT NOCOPY VARCHAR2,
182 p_entity_id IN NUMBER,
183 p_prior_cal_period_id OUT NOCOPY NUMBER) IS
184 l_cnt NUMBER(15);
185 l_api_name VARCHAR2(30) := 'EXPLODE_CALENDAR_MAP';
186
187 --Bugfix 6037112: Added variable
188 l_mapping_required VARCHAR2(1) := 'N';
189
190 BEGIN
191 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
192 fnd_log.STRING(fnd_log.level_procedure,
193 g_pkg_name || '.' || l_api_name,
194 gcs_utility_pkg.g_module_enter ||
195 ' p_source_ledger_id = ' || p_source_ledger_id ||
196 ' p_target_cal_period_id = ' || p_target_cal_period_id ||
197 ' p_balance_type_code = ' || p_balance_type_code || ' ' ||
198 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
199 END IF;
200 gcs_utility_pkg.get_cal_period_details(p_target_cal_period_id,
201 p_cal_period_record);
202
203 -- Bugfix 6037112: Checking if calendar mapping is required
204 check_calendar_map_required(p_entity_id => p_entity_id,
205 p_target_cal_period_id => p_target_cal_period_id,
206 p_ledger_id => p_source_ledger_id,
207 p_balance_type_code => p_balance_type_code,
208 p_period_mapping_required => l_mapping_required);
209
210 IF (l_mapping_required = 'Y') THEN
211 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
212 fnd_log.STRING(fnd_log.level_statement,
213 g_pkg_name || '.' || l_api_name,
214 'INSERT INTO gcs_cal_period_maps_gt (source_cal_period_id, target_cal_period_id)
215 SELECT fdl.cal_period_id, ' ||
216 p_target_cal_period_id || '
217 FROM fem_data_locations fdl, ' || '
218 fem_ledgers_attr fla, ' || '
219 fem_cal_periods_attr fcpa_number, ' || '
220 fem_cal_periods_attr fcpa_year, ' || '
221 gcs_cal_period_map_dtls gcpmd, ' || '
222 gcs_cal_period_maps gcpm, ' || '
223 fem_cal_periods_b fcpb_src, ' || '
224 fem_cal_periods_b fcpb_tgt ' || '
225 WHERE fdl.ledger_id = ' ||
226 p_source_ledger_id || '
227 AND fdl.table_name = ''FEM_BALANCES'' ' || '
228 AND fdl.load_status = ''COMPLETE''
229 AND fdl.balance_type_code = ''' ||
230 p_balance_type_code || '''
231 AND fdl.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER ' || '
232 AND fla.ledger_id = fdl.ledger_id' || '
233 AND fla.attribute_id = ' ||
234 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
235 .attribute_id || '
236 AND fla.version_id = ' ||
237 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
238 .version_id || '
239 AND gcpmd.cal_period_map_id = gcpm.cal_period_map_id
240 AND fcpb_src.cal_period_id = fdl.cal_period_id
241 AND gcpm.source_calendar_id = fcpb_src.calendar_id
242 AND gcpm.source_dimension_group_id = fcpb_src.dimension_group_id
243 AND fcpb_tgt.cal_period_id = ' ||
244 p_target_cal_period_id || '
245 AND gcpm.target_calendar_id = fcpb_tgt.calendar_id
246 AND gcpm.target_dimension_group_id = fcpb_tgt.dimension_group_id
247 AND fdl.cal_period_id = fcpa_number.cal_period_id
248 AND fdl.cal_period_id = fcpa_year.cal_period_id
249 AND fcpa_number.attribute_id = ' ||
250 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
251 .attribute_id || '
252 AND fcpa_year.attribute_id = ' ||
253 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
254 .attribute_id || '
255 AND fcpa_number.version_id = ' ||
256 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
257 .version_id || '
258 AND fcpa_year.version_id = ' ||
259 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
260 .version_id || '
261 AND fcpa_number.number_assign_value = gcpmd.source_period_number
262 AND gcpmd.target_period_number = ' ||
263 p_cal_period_record.cal_period_number || '
264 AND fcpa_year.number_assign_value = DECODE (gcpmd.target_relative_year_code,
265 ''CURRENT'', ' ||
266 p_cal_period_record.cal_period_year || ',
267 ''PRIOR'', ' ||
268 p_cal_period_record.cal_period_year ||
269 ' + 1,
270 ''FOLLOWING'', ' ||
271 p_cal_period_record.cal_period_year || ' - 1 )');
272 END IF;
273
274 INSERT INTO gcs_cal_period_maps_gt
275 (source_cal_period_id, target_cal_period_id)
276 SELECT fdl.cal_period_id, p_target_cal_period_id
277 FROM fem_data_locations fdl,
278 fem_ledgers_attr fla,
279 fem_cal_periods_attr fcpa_number,
280 fem_cal_periods_attr fcpa_year,
281 gcs_cal_period_map_dtls gcpmd,
282 gcs_cal_period_maps gcpm,
283 fem_cal_periods_b fcpb_src,
284 fem_cal_periods_b fcpb_tgt
285 WHERE fdl.ledger_id = p_source_ledger_id
286 AND fdl.table_name = 'FEM_BALANCES'
287 AND fdl.load_status = 'COMPLETE'
288 AND fdl.balance_type_code = p_balance_type_code
289 AND fdl.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
290 AND fla.ledger_id = fdl.ledger_id
291 AND fla.attribute_id =
292 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
293 .attribute_id
294 AND fla.version_id =
295 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
296 .version_id
297 AND gcpmd.cal_period_map_id = gcpm.cal_period_map_id
298 AND fcpb_src.cal_period_id = fdl.cal_period_id
299 AND gcpm.source_calendar_id = fcpb_src.calendar_id
300 AND gcpm.source_dimension_group_id = fcpb_src.dimension_group_id
301 AND fcpb_tgt.cal_period_id = p_target_cal_period_id
302 AND gcpm.target_calendar_id = fcpb_tgt.calendar_id
303 AND gcpm.target_dimension_group_id = fcpb_tgt.dimension_group_id
304 AND fdl.cal_period_id = fcpa_number.cal_period_id
305 AND fdl.cal_period_id = fcpa_year.cal_period_id
306 AND fcpa_number.attribute_id =
307 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
308 .attribute_id
309 AND fcpa_year.attribute_id =
310 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
311 .attribute_id
312 AND fcpa_number.version_id =
313 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
314 .version_id
315 AND fcpa_year.version_id =
316 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
317 .version_id
318 AND fcpa_number.number_assign_value = gcpmd.source_period_number
319 AND gcpmd.target_period_number =
320 p_cal_period_record.cal_period_number
321 AND fcpa_year.number_assign_value =
322 DECODE(gcpmd.target_relative_year_code,
323 'CURRENT',
324 p_cal_period_record.cal_period_year,
325 'PRIOR',
326 p_cal_period_record.cal_period_year + 1,
327 'FOLLOWING',
328 p_cal_period_record.cal_period_year - 1);
329
330 SELECT count(*) INTO l_cnt FROM gcs_cal_period_maps_gt;
331 IF (l_cnt = 0) THEN
332 fnd_file.put_line(fnd_file.log,
333 'Calendar Period Mapping does not exist');
334 p_prior_cal_period_id := p_cal_period_record.prev_cal_period_id;
335 p_year_end_values_match := 'Y';
336 ELSE
337 -- Bugfix 6072367: Set Year End Values Match to Y
338 p_year_end_values_match := 'Y';
339 /*
340 -- Check if Year Ends Match
341 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
342 fnd_log.STRING(fnd_log.level_statement,
343 g_pkg_name || '.' || l_api_name,
344 'SELECT decode(COUNT (gcpmd.cal_period_map_id), 0, ''Y'', ''N'')
345 INTO p_year_end_values_match
346 FROM gcs_cal_period_map_dtls gcpmd,
347 gcs_cal_period_maps_gt gcpmg,
348 gcs_cal_period_maps gcpm,
349 fem_cal_periods_b fcpb_src,
350 fem_cal_periods_b fcpb_tgt
351 WHERE gcpmd.cal_period_map_id = gcpm.cal_period_map_id
352 AND gcpmd.target_relative_year_code <> ''CURRENT''
353 AND fcpb_src.cal_period_id = gcpmg.source_cal_period_id
354 AND gcpm.source_calendar_id = fcpb_src.calendar_id
355 AND gcpm.source_dimension_group_id = fcpb_src.dimension_group_id
356 AND fcpb_tgt.cal_period_id = gcpmg.target_cal_period_id
357 AND gcpm.target_calendar_id = fcpb_tgt.calendar_id
358 AND gcpm.target_dimension_group_id = fcpb_tgt.dimension_group_id');
359 END IF;
360
361 SELECT decode(COUNT(gcpmd.cal_period_map_id), 0, 'Y', 'N')
362 INTO p_year_end_values_match
363 FROM gcs_cal_period_map_dtls gcpmd,
364 gcs_cal_period_maps_gt gcpmg,
365 gcs_cal_period_maps gcpm,
366 fem_cal_periods_b fcpb_src,
367 fem_cal_periods_b fcpb_tgt
368 WHERE gcpmd.cal_period_map_id = gcpm.cal_period_map_id
369 AND gcpmd.target_relative_year_code <> 'CURRENT'
370 AND fcpb_src.cal_period_id = gcpmg.source_cal_period_id
371 AND gcpm.source_calendar_id = fcpb_src.calendar_id
372 AND gcpm.source_dimension_group_id = fcpb_src.dimension_group_id
373 AND fcpb_tgt.cal_period_id = gcpmg.target_cal_period_id
374 AND gcpm.target_calendar_id = fcpb_tgt.calendar_id
375 AND gcpm.target_dimension_group_id = fcpb_tgt.dimension_group_id;
376
377 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
378 fnd_log.STRING(fnd_log.level_procedure,
379 g_pkg_name || '.' || l_api_name,
380 gcs_utility_pkg.g_module_success ||
381 ' p_year_end_values_match = ' ||
382 p_year_end_values_match || ' ' ||
383 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
384 END IF;
385 */
386 END IF; -- calendar map existence check
387 ELSE
388 -- no mapping required
389 INSERT INTO gcs_cal_period_maps_gt
390 (source_cal_period_id, target_cal_period_id)
391 VALUES
392 (p_target_cal_period_id, p_target_cal_period_id);
393 p_year_end_values_match := 'Y';
394 END IF;
395
396 EXCEPTION
397 WHEN NO_DATA_FOUND THEN
398 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
399 fnd_log.STRING(fnd_log.level_statement,
400 g_pkg_name || '.' || l_api_name,
401 'INSERT INTO gcs_cal_period_maps_gt (source_cal_period_id, target_cal_period_id )
402 VALUES (' ||
403 p_target_cal_period_id || ', ' ||
404 p_target_cal_period_id || '
405 )');
406 END IF;
407 INSERT INTO gcs_cal_period_maps_gt
408 (source_cal_period_id, target_cal_period_id)
409 VALUES
410 (p_target_cal_period_id, p_target_cal_period_id);
411 p_year_end_values_match := 'Y';
412 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
413 fnd_log.STRING(fnd_log.level_procedure,
414 g_pkg_name || '.' || l_api_name,
415 gcs_utility_pkg.g_module_success ||
416 ' p_year_end_values_match = ' ||
417 p_year_end_values_match || ' ' ||
418 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
419 END IF;
420 END explode_calendar_map;
421 --
422 -- Procedure
423 -- reverse_explode_calendar_map
424 -- Purpose
425 -- This procedure takes the TARGET_CAL_PERIOD_ID, AND using the source entity information determines
426 -- which source period maps to which target information. If there is no information found in GCS_CAL_PERIOD_MAPS
427 -- between the source AND target a dummy record will be insert into GCS_TEMP_CAL_PERIOD_MAPS WHERE the
428 -- source_cal_period_id = target_cal_period_id.
429 -- Arguments
430 -- p_source_ledger_id Source ledger Identifier
431 -- p_target_cal_period_id Target Calendar Period Identifier
432 -- p_cal_period_record gcs_utility_pkg.r_cal_period_info
433 -- Notes
434 --
435 PROCEDURE reverse_explode_calendar_map(p_source_ledger_id IN NUMBER,
436 p_source_cal_period_id IN NUMBER,
437 p_hierarchy_id IN NUMBER) IS
438 l_cal_period_map_id NUMBER(15);
439 l_count_years NUMBER(15);
440 l_calendar_id NUMBER;
441 l_dimension_grp_id NUMBER;
442 l_cal_period_record gcs_utility_pkg.r_cal_period_info;
443 l_api_name VARCHAR2(30) := 'REVERSE_EXPLODE_CALENDAR_MAP';
444 BEGIN
445 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
446 fnd_log.STRING(fnd_log.level_procedure,
447 g_pkg_name || '.' || l_api_name,
448 gcs_utility_pkg.g_module_enter ||
449 ' p_source_ledger_id = ' || p_source_ledger_id ||
450 ' p_source_cal_period_id = ' || p_source_cal_period_id ||
451 ' p_hierarchy_id = ' || p_hierarchy_id || ' ' ||
452 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
453 END IF;
454 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
455 fnd_log.STRING(fnd_log.level_statement,
456 g_pkg_name || '.' || l_api_name,
457 'SELECT ghb.calendar_id, ghb.dimension_group_id
458 INTO l_calendar_id, l_dimension_grp_id
459 FROM gcs_hierarchies_b ghb
460 WHERE ghb.hierarchy_id = ' ||
461 p_hierarchy_id);
462 END IF;
463 SELECT ghb.calendar_id, ghb.dimension_group_id
464 INTO l_calendar_id, l_dimension_grp_id
465 FROM gcs_hierarchies_b ghb
466 WHERE ghb.hierarchy_id = p_hierarchy_id;
467 -- retrieve the cal_period_map_id according to source data
468 -- (l_source_calendar_id, l_target_cal_period_id)
469 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
470 fnd_log.STRING(fnd_log.level_statement,
471 g_pkg_name || '.' || l_api_name,
472 ' SELECT gcpm.cal_period_map_id
473 INTO l_cal_period_map_id
474 FROM gcs_cal_period_maps gcpm,
475 fem_cal_periods_b fcpb
476 WHERE gcpm.target_calendar_id = ' ||
477 l_calendar_id || '
478 AND gcpm.target_dimension_group_id = ' ||
479 l_dimension_grp_id || '
480 AND fcpb.cal_period_id = ' ||
481 p_source_cal_period_id || '
482 AND gcpm.source_calendar_id = fcpb.calendar_id
483 AND gcpm.source_dimension_group_id = fcpb.dimension_group_id');
484 END IF;
485
486 SELECT gcpm.cal_period_map_id
487 INTO l_cal_period_map_id
488 FROM gcs_cal_period_maps gcpm, fem_cal_periods_b fcpb
489 WHERE gcpm.target_calendar_id = l_calendar_id
490 AND gcpm.target_dimension_group_id = l_dimension_grp_id
491 AND fcpb.cal_period_id = p_source_cal_period_id
492 AND gcpm.source_calendar_id = fcpb.calendar_id
493 AND gcpm.source_dimension_group_id = fcpb.dimension_group_id;
494
495 gcs_utility_pkg.get_cal_period_details(p_source_cal_period_id,
496 l_cal_period_record);
497 -- insert mapping data into temp table
498 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
499 fnd_log.STRING(fnd_log.level_statement,
500 g_pkg_name || '.' || l_api_name,
501 'INSERT INTO gcs_cal_period_maps_gt (source_cal_period_id, target_cal_period_id)
502 SELECT fcpb.cal_period_id, p_source_cal_period_id
503 FROM fem_cal_periods_b fcpb,
504 fem_cal_periods_attr fcpa_number,
505 fem_cal_periods_attr fcpa_year,
506 gcs_cal_period_map_dtls gcpmd
507 WHERE gcpmd.cal_period_map_id = ' ||
508 l_cal_period_map_id || '
509 AND fcpb.calendar_id = ' ||
510 l_calendar_id || '
511 AND fcpb.dimension_group_id = ' ||
512 l_dimension_grp_id || '
513 AND fcpb.cal_period_id = fcpa_number.cal_period_id
514 AND fcpb.cal_period_id = fcpa_year.cal_period_id
515 AND fcpa_number.attribute_id =' ||
516 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
517 .attribute_id || '
518 AND fcpa_year.attribute_id =' ||
519 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
520 .attribute_id || '
521 AND fcpa_number.version_id =' ||
522 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
523 .version_id || '
524 AND fcpa_year.version_id =' ||
525 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
526 .version_id || '
527 AND fcpa_number.number_assign_value = gcpmd.target_period_number
528 AND gcpmd.source_period_number = ' ||
529 l_cal_period_record.cal_period_number || '
530 AND fcpa_year.number_assign_value = DECODE (gcpmd.target_relative_year_code,
531 ''CURRENT'', ' ||
532 l_cal_period_record.cal_period_year || ',
533 ''PRIOR'', ' ||
534 l_cal_period_record.cal_period_year - 1 || ',
535 ''FOLLOWING'', ' ||
536 l_cal_period_record.cal_period_year || '
537 + 1 )');
538 END IF;
539
540 INSERT INTO gcs_cal_period_maps_gt
541 (source_cal_period_id, target_cal_period_id)
542 SELECT p_source_cal_period_id, fcpb.cal_period_id
543 FROM fem_cal_periods_b fcpb,
544 fem_cal_periods_attr fcpa_number,
545 fem_cal_periods_attr fcpa_year,
546 gcs_cal_period_map_dtls gcpmd
547 WHERE gcpmd.cal_period_map_id = l_cal_period_map_id
548 AND fcpb.calendar_id = l_calendar_id
549 AND fcpb.dimension_group_id = l_dimension_grp_id
550 AND fcpb.cal_period_id = fcpa_number.cal_period_id
551 AND fcpb.cal_period_id = fcpa_year.cal_period_id
552 AND fcpa_number.attribute_id =
553 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
554 .attribute_id
555 AND fcpa_year.attribute_id =
556 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
557 .attribute_id
558 AND fcpa_number.version_id =
559 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
560 .version_id
561 AND fcpa_year.version_id =
562 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
563 .version_id
564 AND fcpa_number.number_assign_value = gcpmd.target_period_number
565 AND gcpmd.source_period_number =
566 l_cal_period_record.cal_period_number
567 AND fcpa_year.number_assign_value =
568 DECODE(gcpmd.target_relative_year_code,
569 'CURRENT',
570 l_cal_period_record.cal_period_year,
571 'PRIOR',
572 l_cal_period_record.cal_period_year - 1,
573 'FOLLOWING',
574 l_cal_period_record.cal_period_year + 1);
575 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
576 fnd_log.STRING(fnd_log.level_procedure,
577 g_pkg_name || '.' || l_api_name,
578 gcs_utility_pkg.g_module_success || ' ' || l_api_name ||
579 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
580 END IF;
581
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 INSERT INTO gcs_cal_period_maps_gt
585 (source_cal_period_id, target_cal_period_id)
586 VALUES
587 (p_source_cal_period_id, p_source_cal_period_id);
588 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
589 fnd_log.STRING(fnd_log.level_procedure,
590 g_pkg_name || '.' || l_api_name,
591 gcs_utility_pkg.g_module_success || ' ' ||
592 l_api_name || '() ' ||
593 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
594 END IF;
595 END reverse_explode_calendar_map;
596
597 -- Bugfix 6037112: Added procedure to copy entries from one period to another
598 -- Not handling proportional entries at this point in time.
599 -- Procedure
600 -- RETRIEVE_PRIOR_PERIOD_ENTRIES
601 -- Purpose
602 -- Retrieve entries from prior periods
603 -- Arguments
604 -- p_hierarchy_id Hierarchy Identifier
605 -- p_entity_id Entity Identifier
606 -- p_balance_type_code Balance Type
607 -- p_prior_cal_period_id Prior Calendar Period
608 -- p_tgt_cal_period_id Current Calendar Period
609 -- p_run_detail_id Run Detail Identifier
610 -- p_source_currency_code Currency Code of Entity
611 -- Notes
612 --
613 PROCEDURE retrieve_prior_period_entries(p_hierarchy_id IN NUMBER,
614 p_entity_id IN NUMBER,
615 p_balance_type_code IN VARCHAR2,
616 p_prior_cal_period_id IN NUMBER,
617 p_tgt_cal_period_id IN NUMBER,
618 p_run_detail_id IN NUMBER,
619 p_source_currency_code IN VARCHAR2) IS
620
621 l_prior_entry_id NUMBER(15);
622 l_prior_stat_entry_id NUMBER(15);
623 l_entry_id NUMBER(15);
624 l_stat_entry_id NUMBER(15);
625 l_errbuf VARCHAR2(2000);
626 l_retcode VARCHAR2(2000);
627 l_line_item_type_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
628 .attribute_id;
629 l_line_item_type_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
630 .version_id;
631 l_acct_type_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE')
632 .attribute_id;
633 l_acct_type_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE')
634 .version_id;
635 l_request_error_code VARCHAR2(400);
636 BEGIN
637
638 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
639 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
640 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES.begin',
641 '<<Enter>>');
642 END IF;
643
644 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
645 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
646 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
647 'Beginning of Parameter List');
648 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
649 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
650 'Hierarchy Id: ' || p_hierarchy_id);
651 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
652 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
653 'Entity Id : ' || p_entity_id);
654 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
655 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
656 'Balance Type: ' || p_balance_type_code);
657 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
658 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
659 'Prior Calendar Period: ' || p_prior_cal_period_id);
660 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
661 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
662 'Target Calendar Period:' || p_tgt_cal_period_id);
663 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
664 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
665 'Run Detail Identifier: ' || p_run_detail_id);
666 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
667 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
668 'Source Currency Code : ' || p_source_currency_code);
669 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
670 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
671 'End of Parameter List');
672 END IF;
673
674 SELECT gcerd.entry_id, gcerd.stat_entry_id, gcerd.request_error_code
675 INTO l_prior_entry_id, l_prior_stat_entry_id, l_request_error_code
676 FROM gcs_cons_eng_run_dtls gcerd
677 WHERE gcerd.child_entity_id = p_entity_id
678 AND gcerd.category_code = 'DATAPREPARATION'
679 AND EXISTS
680 (SELECT 'X'
681 FROM gcs_cons_eng_runs gcer
682 WHERE gcer.run_name = gcerd.run_name
683 AND gcer.run_entity_id = gcerd.consolidation_entity_id
684 AND gcer.hierarchy_id = p_hierarchy_id
685 AND gcer.cal_period_id = p_prior_cal_period_id
686 AND gcer.balance_type_code = p_balance_type_code
687 AND gcer.most_recent_flag = 'Y');
688
689 IF (l_prior_entry_id IS NOT NULL) THEN
690 SELECT gcs_entry_headers_s.nextval INTO l_entry_id FROM dual;
691
692 gcs_entry_pkg.create_entry_header(x_errbuf => l_errbuf,
693 x_retcode => l_retcode,
694 p_entry_id => l_entry_id,
695 p_hierarchy_id => p_hierarchy_id,
696 p_entity_id => p_entity_id,
697 p_start_cal_period_id => p_tgt_cal_period_id,
698 p_end_cal_period_id => p_tgt_cal_period_id,
699 p_entry_type_code => 'AUTOMATIC',
700 p_balance_type_code => p_balance_type_code,
701 p_currency_code => p_source_currency_code,
702 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
703 p_category_code => 'DATAPREPARATION');
704 END IF;
705
706 IF (l_prior_stat_entry_id IS NOT NULL) THEN
707 SELECT gcs_entry_headers_s.nextval INTO l_stat_entry_id FROM dual;
708
709 gcs_entry_pkg.create_entry_header(x_errbuf => l_errbuf,
710 x_retcode => l_retcode,
711 p_entry_id => l_stat_entry_id,
712 p_hierarchy_id => p_hierarchy_id,
713 p_entity_id => p_entity_id,
714 p_start_cal_period_id => p_tgt_cal_period_id,
715 p_end_cal_period_id => p_tgt_cal_period_id,
716 p_entry_type_code => 'AUTOMATIC',
717 p_balance_type_code => p_balance_type_code,
718 p_currency_code => 'STAT',
719 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
720 p_category_code => 'DATAPREPARATION');
721 END IF;
722
723 INSERT INTO gcs_entry_lines
724 (entry_line_number,
725 product_id,
726 natural_account_id,
727 channel_id,
728 line_item_id,
729 project_id,
730 customer_id,
731 intercompany_id,
732 task_id,
733 user_dim1_id,
734 user_dim2_id,
735 user_dim3_id,
736 user_dim4_id,
737 user_dim5_id,
738 user_dim6_id,
739 user_dim7_id,
740 user_dim8_id,
741 user_dim9_id,
742 user_dim10_id,
743 xtd_balance_e,
744 ytd_balance_e,
745 ptd_debit_balance_e,
746 ptd_credit_balance_e,
747 ytd_debit_balance_e,
748 ytd_credit_balance_e,
749 creation_date,
750 created_by,
751 last_update_date,
752 last_updated_by,
753 last_update_login,
754 entry_id,
755 line_type_code,
756 description,
757 company_cost_center_org_id,
758 financial_elem_id)
759 SELECT gel.entry_line_number,
760 gel.product_id,
761 gel.natural_account_id,
762 gel.channel_id,
763 gel.line_item_id,
764 gel.project_id,
765 gel.customer_id,
766 gel.intercompany_id,
767 gel.task_id,
768 gel.user_dim1_id,
769 gel.user_dim2_id,
770 gel.user_dim3_id,
771 gel.user_dim4_id,
772 gel.user_dim5_id,
773 gel.user_dim6_id,
774 gel.user_dim7_id,
775 gel.user_dim8_id,
776 gel.user_dim9_id,
777 gel.user_dim10_id,
778 DECODE(fea_attr.dim_attribute_varchar_member,
779 'REVENUE',
780 0,
781 'EXPENSE',
782 0,
783 gel.xtd_balance_e),
784 gel.ytd_balance_e,
785 0,
786 0,
787 gel.ytd_debit_balance_e,
788 gel.ytd_credit_balance_e,
789 gel.creation_date,
790 gel.created_by,
791 gel.last_update_date,
792 gel.last_updated_by,
793 gel.last_update_login,
794 DECODE(gel.entry_id,
795 l_prior_entry_id,
796 l_entry_id,
797 l_stat_entry_id),
798 gel.line_type_code,
799 gel.description,
800 gel.company_cost_center_org_id,
801 gel.financial_elem_id
802 FROM gcs_entry_lines gel,
803 fem_ln_items_attr flia,
804 fem_ext_acct_types_attr fea_attr
805 WHERE gel.entry_id IN (l_prior_entry_id, l_prior_stat_entry_id)
806 AND gel.line_item_id = flia.line_item_id
807 AND flia.attribute_id = l_line_item_type_attr
808 AND flia.version_id = l_line_item_type_version
809 AND flia.dim_attribute_varchar_member =
810 fea_attr.ext_account_type_code
811 AND fea_attr.attribute_id = l_acct_type_attr
812 AND fea_attr.version_id = l_acct_type_version;
813
814 gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
815 p_entry_id => l_entry_id,
816 p_stat_entry_id => l_stat_entry_id,
817 p_pre_prop_entry_id => NULL,
818 p_pre_prop_stat_entry_id => NULL,
819 p_request_error_code => l_request_error_code,
820 p_bp_request_error_code => l_request_error_code);
821
822 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
823 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
824 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES.end',
825 '<<Exit>>');
826 END IF;
827
828 EXCEPTION
829 WHEN OTHERS THEN
830 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
831 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
832 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
833 'Beginning of Exception');
834 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
835 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
836 SQLERRM);
837 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
838 g_pkg_name || '.RETRIEVE_PRIOR_PERIOD_ENTRIES',
839 'End of Exception');
840
841 END IF;
842 gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
843 p_entry_id => NULL,
844 p_stat_entry_id => NULL,
845 p_pre_prop_entry_id => NULL,
846 p_pre_prop_stat_entry_id => NULL,
847 p_request_error_code => 'NOT_APPLICABLE',
848 p_bp_request_error_code => 'NOT_APPLICABLE');
849 END;
850
851 --
852 -- PUBLIC PROCEDURES
853 --
854 --
855 -- Procedure
856 -- GCS_MAIN_DATA_PREP
857 -- Purpose
858 -- This procedure will be called via the SRS submission performed by the Consolidation Engine. It will then call the appropriate routines to complete the data preparation process.
859 -- Arguments
860 -- x_errbuf Error Buffer used for SRS
861 -- x_retcode Return Code used for SRS
862 -- p_hierarchy_id Consolidation Hierarchy Identifier
863 -- p_entity_id Internal/External Entity Identifier
864 -- p_target_cal_period_id Target Calendar Period Identifier
865 -- p_stat_entry_id Entry Identifier for lines with Currency Code STAT
866 -- p_entry_id Entry Identifier for other lines
867 -- p_cons_rel_id Consolidation Relationships Identifier
868 -- Used for proportional consolidation
869 -- if = -1, full consolidation
870 -- p_balance_type_code Balance Type Code: ACTUAL or ADB
871 -- Notes
872 --
873 PROCEDURE gcs_main_data_prep(x_errbuf OUT NOCOPY VARCHAR2,
874 x_retcode OUT NOCOPY VARCHAR2,
875 p_hierarchy_id IN NUMBER,
876 p_entity_id IN NUMBER,
877 p_target_cal_period_id IN NUMBER,
878 p_run_detail_id IN NUMBER,
879 p_cons_rel_id IN NUMBER,
880 p_balance_type_code IN VARCHAR2,
881 p_source_dataset_code IN NUMBER) IS
882 l_source_currency_code VARCHAR2(30);
883 l_source_currency_type_code VARCHAR2(30);
884 l_source_ledger_id NUMBER;
885 l_source_ledger_func_currency VARCHAR2(30);
886 l_max_period NUMBER;
887 l_cal_period_record gcs_utility_pkg.r_cal_period_info;
888 l_year_end_match VARCHAR2(1);
889 l_process_flag VARCHAR2(1);
890 l_owner_percentage NUMBER;
891 gcs_dp_src_data_error EXCEPTION;
892
893 -- Bug Fix: 5843592, Get the attribute id and version id of the CAL_PERIOD_END_DATE of calendar period
894 gcs_dp_src_ledger_error EXCEPTION;
895 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
896 .attribute_id;
897 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
898 .version_id;
899 l_api_name VARCHAR2(30) := 'GCS_MAIN_DATA_PREP';
900
901 --Bugfix 6037112: Added prior calendar period variable
902 l_prior_cal_period_id NUMBER;
903
904 BEGIN
905 -- bug 5071794 fix: we don't need the savepoint anymore since we'll need to commit
906 -- within Data Prep
907 --SAVEPOINT main_data_prep_start;
908 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
909 fnd_log.STRING(fnd_log.level_procedure,
910 g_pkg_name || '.' || l_api_name,
911 gcs_utility_pkg.g_module_enter || ' p_hierarchy_id = ' ||
912 p_hierarchy_id || ' p_entity_id = ' || p_entity_id ||
913 ' p_target_cal_period_id = ' || p_target_cal_period_id ||
914 ' p_run_detail_id = ' || p_run_detail_id ||
915 ' p_cons_rel_id = ' || p_cons_rel_id ||
916 ' p_balance_type_code = ' || p_balance_type_code ||
917 ' p_source_dataset_code = ' || p_source_dataset_code || ' ' ||
918 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
919 END IF;
920 -- Extract source ledger, currency, AND entity type information
921 BEGIN
922 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
923 fnd_log.STRING(fnd_log.level_statement,
924 g_pkg_name || '.' || l_api_name,
925 ' SELECT gea.ledger_id,
926 geca.currency_code,
927 DECODE (fla.dim_attribute_varchar_member,
928 geca.currency_code,
929 ''ENTERED'',
930 ''TRANSLATED''
931 )
932 INTO l_source_ledger_id,
933 l_source_currency_code,
934 l_source_currency_type_code
935 FROM gcs_entity_cons_attrs geca, fem_ledgers_attr fla, gcs_entities_attr gea
936 WHERE geca.entity_id = ' ||
937 p_entity_id || '
938 AND geca.hierarchy_id = ' ||
939 p_hierarchy_id || '
940 AND fea.entity_id = geca.entity_id
941 AND gea.data_type_code = ' ||
942 p_balance_type_code || '
943 AND fcpa.cal_period_id = ' ||
944 p_target_cal_period_id || '
945 AND fcpa.attribute_id = ' ||
946 l_period_end_date_attr || '
947 AND fcpa.version_id = ' ||
948 l_period_end_date_version || '
949 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
950 AND NVL(gea.effective_end_date, fcpa.date_assign_value )
951 AND gea.ledger_id = fla.ledger_id
952 AND fla.version_id = ' ||
953 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
954 .version_id || '
955 AND fla.attribute_id = ' ||
956 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
957 .attribute_id);
958 END IF;
959
960 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
961
962 SELECT gea.ledger_id,
963 geca.currency_code,
964 DECODE(fla.dim_attribute_varchar_member,
965 geca.currency_code,
966 'ENTERED',
967 'TRANSLATED')
968 INTO l_source_ledger_id,
969 l_source_currency_code,
970 l_source_currency_type_code
971 FROM gcs_entity_cons_attrs geca,
972 fem_ledgers_attr fla,
973 gcs_entities_attr gea,
974 fem_cal_periods_attr fcpa
975 WHERE geca.entity_id = p_entity_id
976 AND geca.hierarchy_id = p_hierarchy_id
977 AND gea.entity_id = geca.entity_id
978 AND gea.data_type_code = p_balance_type_code
979 AND fcpa.cal_period_id = p_target_cal_period_id
980 AND fcpa.attribute_id = l_period_end_date_attr
981 AND fcpa.version_id = l_period_end_date_version
982 AND fcpa.date_assign_value BETWEEN gea.effective_start_date AND
983 NVL(gea.effective_end_date, fcpa.date_assign_value)
984 AND gea.ledger_id = fla.ledger_id
985 AND fla.version_id =
986 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
987 .version_id
988 AND fla.attribute_id =
989 gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
990 .attribute_id;
991
992 EXCEPTION
993 -- Bugfix: 5843592, create a new user defined exception
994 WHEN no_data_found THEN
995 RAISE gcs_dp_src_ledger_error;
996 END;
997 -- create calendar mapping
998 --Bugfix 6037112: Added parameters to explode calendar period map
999 explode_calendar_map(p_source_ledger_id => l_source_ledger_id,
1000 p_target_cal_period_id => p_target_cal_period_id,
1001 p_balance_type_code => p_balance_type_code,
1002 p_cal_period_record => l_cal_period_record,
1003 p_year_end_values_match => l_year_end_match,
1004 p_entity_id => p_entity_id,
1005 p_prior_cal_period_id => l_prior_cal_period_id);
1006
1007 IF (l_prior_cal_period_id IS NULL) THEN
1008
1009 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
1010 fnd_log.STRING(fnd_log.level_statement,
1011 g_pkg_name || '.' || l_api_name,
1012 ' SELECT MAX (source_cal_period_id)
1013 INTO l_max_period
1014 FROM gcs_cal_period_maps_gt');
1015 END IF;
1016
1017 SELECT MAX(source_cal_period_id)
1018 INTO l_max_period
1019 FROM gcs_cal_period_maps_gt;
1020
1021 BEGIN
1022 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
1023 fnd_log.STRING(fnd_log.level_statement,
1024 g_pkg_name || '.' || l_api_name,
1025 'SELECT nvl(ownership_percent, 100) * 0.01
1026 INTO l_owner_percentage
1027 FROM gcs_cons_relationships gcr,
1028 gcs_treatments_b gtb
1029 WHERE cons_relationship_id =' ||
1030 p_cons_rel_id || '
1031 AND gcr.treatment_id = gtb.treatment_id' || '
1032 AND gtb.consolidation_type_code = ''PARTIAL''');
1033 END IF;
1034
1035 SELECT NVL(ownership_percent, 100) * 0.01
1036 INTO l_owner_percentage
1037 FROM gcs_cons_relationships gcr, gcs_treatments_b gtb
1038 WHERE cons_relationship_id = p_cons_rel_id
1039 AND gcr.treatment_id = gtb.treatment_id
1040 AND gtb.consolidation_type_code = 'PARTIAL';
1041 EXCEPTION
1042 WHEN no_data_found THEN
1043 l_owner_percentage := 1;
1044 END;
1045
1046 --In Full execution mode, we need to perform local to master value set mapping and
1047 --process all the data with source code of system.
1048 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1049 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1050 g_pkg_name || '.' || l_api_name,
1051 'l_source_currency_code : ' ||
1052 l_source_currency_code);
1053 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1054 g_pkg_name || '.' || l_api_name,
1055 'p_target_cal_period_id : ' ||
1056 p_target_cal_period_id);
1057 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1058 g_pkg_name || '.' || l_api_name,
1059 'l_max_period : ' || l_max_period);
1060 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1061 g_pkg_name || '.' || l_api_name,
1062 'l_source_currency_type_code : ' ||
1063 l_source_currency_type_code);
1064 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1065 g_pkg_name || '.' || l_api_name,
1066 'p_hierarchy_id : ' || p_hierarchy_id);
1067 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1068 g_pkg_name || '.' || l_api_name,
1069 'p_entity_id : ' || p_entity_id);
1070 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1071 g_pkg_name || '.' || l_api_name,
1072 'l_source_currency_code : ' ||
1073 l_source_currency_code);
1074 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1075 g_pkg_name || '.' || l_api_name,
1076 'l_source_ledger_id : ' || l_source_ledger_id);
1077 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1078 g_pkg_name || '.' || l_api_name,
1079 'l_year_end_match : ' || l_year_end_match);
1080 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1081 g_pkg_name || '.' || l_api_name,
1082 'l_source_currency_type_code : ' ||
1083 l_source_currency_type_code);
1084 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1085 g_pkg_name || '.' || l_api_name,
1086 'p_balance_type_code : ' || p_balance_type_code);
1087 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1088 g_pkg_name || '.' || l_api_name,
1089 'l_owner_percentage : ' || l_owner_percentage);
1090 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1091 g_pkg_name || '.' || l_api_name,
1092 'p_run_detail_id : ' || p_run_detail_id);
1093 END IF;
1094
1095 gcs_dp_dynamic_pkg.process_data(p_source_currency_code => l_source_currency_code,
1096 p_target_cal_period_id => p_target_cal_period_id,
1097 p_max_period => l_max_period,
1098 p_currency_type_code => l_source_currency_type_code,
1099 p_hierarchy_id => p_hierarchy_id,
1100 p_entity_id => p_entity_id,
1101 p_source_ledger_id => l_source_ledger_id,
1102 p_year_end_values_match => l_year_end_match,
1103 p_cal_period_record => l_cal_period_record,
1104 p_balance_type_code => p_balance_type_code,
1105 p_owner_percentage => l_owner_percentage,
1106 p_run_detail_id => p_run_detail_id,
1107 p_source_dataset_code => p_source_dataset_code,
1108 errbuf => x_errbuf,
1109 retcode => x_retcode);
1110 ELSE
1111 --prior period data retrieval required
1112 retrieve_prior_period_entries(p_hierarchy_id => p_hierarchy_id,
1113 p_entity_id => p_entity_id,
1114 p_balance_type_code => p_balance_type_code,
1115 p_prior_cal_period_id => l_prior_cal_period_id,
1116 p_tgt_cal_period_id => p_target_cal_period_id,
1117 p_run_detail_id => p_run_detail_id,
1118 p_source_currency_code => l_source_currency_code);
1119 END IF;
1120 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1121 fnd_log.STRING(fnd_log.level_procedure,
1122 g_pkg_name || '.' || l_api_name,
1123 gcs_utility_pkg.g_module_success || ' ' || l_api_name ||
1124 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1125 END IF;
1126 EXCEPTION
1127 WHEN gcs_dp_src_ledger_error THEN
1128 -- we don't need the savepoint anymore since we'll need to commit
1129 -- within Data Prep
1130 -- ROLLBACK TO main_data_prep_start;
1131 fnd_message.set_name('GCS', 'GCS_DP_SRC_LEDGER_ERR');
1132 x_errbuf := fnd_message.get;
1133 x_retcode := '2';
1134 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
1135 fnd_log.STRING(fnd_log.level_error,
1136 g_pkg_name || '.' || l_api_name,
1137 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf ||
1138 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1139 END IF;
1140 gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
1141 p_entry_id => NULL,
1142 p_stat_entry_id => NULL,
1143 p_pre_prop_entry_id => NULL,
1144 p_pre_prop_stat_entry_id => NULL,
1145 p_request_error_code => x_errbuf);
1146 WHEN gcs_dp_src_data_error THEN
1147 -- bug 5071794 fix: we don't need the savepoint anymore since we'll need to commit
1148 -- within Data Prep
1149 -- ROLLBACK TO main_data_prep_start;
1150 fnd_message.set_name('GCS', 'GCS_DP_SRC_DATA_ERR');
1151 x_errbuf := fnd_message.get;
1152 x_retcode := '2';
1153 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
1154 fnd_log.STRING(fnd_log.level_error,
1155 g_pkg_name || '.' || l_api_name,
1156 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf ||
1157 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1158 END IF;
1159 gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
1160 p_entry_id => NULL,
1161 p_stat_entry_id => NULL,
1162 p_pre_prop_entry_id => NULL,
1163 p_pre_prop_stat_entry_id => NULL,
1164 p_request_error_code => x_errbuf);
1165 WHEN gcs_dp_dynamic_pkg.gcs_dp_proc_data_error THEN
1166 x_retcode := '2';
1167 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
1168 fnd_log.STRING(fnd_log.level_error,
1169 g_pkg_name || '.' || l_api_name,
1170 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf ||
1171 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1172 END IF;
1173 gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
1174 p_entry_id => NULL,
1175 p_stat_entry_id => NULL,
1176 p_pre_prop_entry_id => NULL,
1177 p_pre_prop_stat_entry_id => NULL,
1178 p_request_error_code => x_errbuf);
1179 WHEN OTHERS THEN
1180 -- bug 5071794 fix: we don't need the savepoint anymore since we'll need to commit
1181 -- within Data Prep
1182 -- ROLLBACK TO main_data_prep_start;
1183 x_errbuf := SQLERRM;
1184 x_retcode := '2';
1185 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
1186 fnd_log.STRING(fnd_log.level_error,
1187 g_pkg_name || '.' || l_api_name,
1188 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf ||
1189 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1190 END IF;
1191 gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
1192 p_entry_id => NULL,
1193 p_stat_entry_id => NULL,
1194 p_pre_prop_entry_id => NULL,
1195 p_pre_prop_stat_entry_id => NULL,
1196 p_request_error_code => x_errbuf);
1197 END gcs_main_data_prep;
1198 -- create_process
1199 -- CREATE_PROCESS
1200 -- Purpose
1201 -- Create PROCESS_DATA procedure using ad_ddl. Returns
1202 -- Arguments
1203 -- x_retcode
1204 -- x_errbuf
1205 --
1206 PROCEDURE create_process(x_errbuf OUT NOCOPY VARCHAR2,
1207 x_retcode OUT NOCOPY VARCHAR2) IS
1208 l_proc_body VARCHAR2(32767);
1209 l_select_gt_clause VARCHAR2(5000); -- vars to form a cursor
1210 l_inc_select_clause VARCHAR2(5000); -- vars to form a cursor
1211 l_insert_clause VARCHAR2(5000); -- vars to form a cursor
1212 l_insert_gt_clause VARCHAR2(5000); -- vars to form a cursor
1213 l_insert_statement VARCHAR2(32767); -- vars to form a cursor
1214 l_insert_from_gt_statement VARCHAR2(32767); -- vars to form a cursor
1215 l_inc_insert_statement VARCHAR2(32767); -- vars to form a cursor
1216 l_from_gt_clause VARCHAR2(5000); -- vars to form a cursor
1217 l_inc_from_clause VARCHAR2(5000); -- vars to form a cursor
1218 l_where_gt_clause VARCHAR2(5000); -- vars to form a cursor
1219 l_inc_where_clause VARCHAR2(5000); -- vars to form a cursor
1220 l_fb_dims VARCHAR2(1000) := get_dimension_text('fb.',
1221 '',
1222 'N');
1223 l_dims VARCHAR2(1000) := get_dimension_text('',
1224 '',
1225 'N');
1226 l_index_column_name VARCHAR2(30);
1227 err VARCHAR2(10);
1228 curr_index NUMBER(5) := 1;
1229 lines NUMBER(5) := 0;
1230 body_len NUMBER(5);
1231 l_api_name VARCHAR2(30) := 'CREATE_PROCESS';
1232
1233 --Bugfix 5329620: Determine Currency Clause Based on Status of Financial Element
1234 l_curr_where_clause VARCHAR2(250);
1235 l_curr_vs_map_where_clause VARCHAR2(250);
1236
1237 BEGIN
1238 SAVEPOINT create_process_start;
1239 fnd_file.put_line(fnd_file.log,
1240 '============================================');
1241 fnd_file.put_line(fnd_file.log,
1242 'GCS_DATA_PREP_PKG::create_process -- Enter');
1243 --Bugfix 5329620: Determine Currency Clause Based on Status of Financial Element
1244 IF (gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID') = 'Y') THEN
1245 l_curr_where_clause := '
1246 AND fb.currency_code IN (p_source_currency_code, ''STAT'')
1247 AND fb.financial_elem_id <> 140';
1248 l_curr_vs_map_where_clause := '
1249 AND fb.currency_code IN (:p_source_currency_code, ''''STAT'''')
1250 AND fb.financial_elem_id <> 140';
1251 ELSE
1252 l_curr_where_clause := '
1253 AND fb.currency_code = p_source_currency_code';
1254 l_curr_vs_map_where_clause := '
1255 AND fb.currency_code = :p_source_currency_code';
1256 END IF;
1257
1258 l_insert_clause := '
1259 INSERT /*+ APPEND */ INTO gcs_entry_lines
1260 (entry_id,' || l_dims || '
1261 ptd_debit_balance_e,
1262 ptd_credit_balance_e,
1263 ytd_debit_balance_e,
1264 ytd_credit_balance_e,
1265 xtd_balance_e,
1266 ytd_balance_e,
1267 creation_date, created_by, last_update_date,
1268 last_updated_by, last_update_login
1269 )' || g_nl;
1270 l_insert_gt_clause := '
1271 INSERT INTO gcs_entry_lines_gt
1272 (entry_id, cal_period_id, ' ||
1273 l_dims || '
1274 ptd_debit_balance_e,
1275 ptd_credit_balance_e,
1276 ytd_debit_balance_e,
1277 ytd_credit_balance_e,
1278 xtd_balance_e,
1279 ytd_balance_e)' || g_nl;
1280
1281 --Bugfix 6068527: Change ytd_balance_e to be equal to ytd debit - ytd credit
1282 l_select_gt_clause := '
1283 fb.ptd_debit_balance_e,
1284 fb.ptd_credit_balance_e,
1285 DECODE (fb.cal_period_id,
1286 p_max_period, ytd_debit_balance_e, 0
1287 ) ytd_debit_balance_e,
1288 DECODE (fb.cal_period_id,
1289 p_max_period, ytd_credit_balance_e, 0
1290 ) ytd_credit_balance_e,
1291 NVL(fb.ptd_debit_balance_e,0) - NVL(fb.ptd_credit_balance_e,0),
1292 DECODE (fb.cal_period_id,
1293 p_max_period, NVL(fb.ytd_debit_balance_e,0) - NVL (fb.ytd_credit_balance_e,0), 0
1294 ) ytd_balance_e ' || g_nl;
1295 l_inc_select_clause := '
1296 SUM(fb.ptd_debit_balance_e) PTD_DEBIT_BALANCE_E,
1297 SUM(fb.ptd_credit_balance_e) PTD_CREDIT_BALANCE_E,
1298 SUM(fb.ytd_debit_balance_e) YTD_DEBIT_BALANCE_E,
1299 SUM(fb.ytd_credit_balance_e) YTD_CREDIT_BALANCE_E,
1300 SUM(NVL(fb.xtd_balance_f, fb.xtd_balance_e)) XTD_BALANCE_E,
1301 SUM(NVL(fb.ytd_balance_f, fb.ytd_balance_e)) YTD_BALANCE_E,
1302 SYSDATE,
1303 FND_GLOBAL.USER_ID,
1304 SYSDATE,
1305 FND_GLOBAL.USER_ID,
1306 FND_GLOBAL.LOGIN_ID ';
1307 l_from_gt_clause := '
1308 FROM fem_balances fb,
1309 gcs_entity_cctr_orgs geco ';
1310 l_inc_from_clause := '
1311 FROM fem_balances fb,
1312 fem_ledgers_attr fla,
1313 gcs_entity_cctr_orgs geco,
1314 gcs_cons_impact_analyses gcia,
1315 gcs_data_sub_dtls gdsd ';
1316 l_where_gt_clause := '
1317 WHERE l_periods_list(counter) = fb.cal_period_id
1318 AND fb.source_system_code = l_source_system_code
1319 AND fb.ledger_id = p_source_ledger_id
1320 AND fb.currency_type_code = p_currency_type_code
1321 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
1322 AND geco.entity_id = p_entity_id
1323 AND fb.dataset_code = p_source_dataset_code ';
1324 l_inc_where_clause := '
1325 WHERE fb.ledger_id = p_source_ledger_id
1326 AND fb.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
1327 AND fla.ledger_id = fb.ledger_id
1328 AND fla.attribute_id = g_ledger_ssc_attr_id
1329 AND fla.version_id = g_ledger_ssc_ver_id
1330 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
1331 AND geco.entity_id = p_entity_id
1332 AND p_balance_type_code = DECODE(fb.financial_elem_id, 140, ''ADB'', ''ACTUAL'')
1333 AND ((fb.currency_type_code = ''TRANSLATED'' AND
1334 fb.currency_code IN (''STAT'', p_source_currency_code)) OR
1335 (fb.currency_type_code = ''ENTERED''))
1336 AND fb.currency_type_code = p_currency_type_code
1337 AND fb.dataset_code = p_source_dataset_code
1338 AND fb.last_updated_by_request_id = gdsd.associated_request_id
1339 AND gcia.run_name = p_run_name
1340 AND gcia.child_entity_id = p_entity_id
1341 AND gcia.load_id = gdsd.load_id ';
1342
1343 l_proc_body := 'CREATE or REPLACE PACKAGE BODY GCS_DP_DYNAMIC_PKG AS
1344 --
1345 -- PRIVATE GLOBAL VARIABLES
1346 --
1347 -- The API name
1348 g_pkg_name VARCHAR2 (30) := ''gcs.plsql.GCS_DP_DYNAMIC_PKG'';
1349 -- A newline character. Included for convenience when writing long strings.
1350 g_nl CONSTANT VARCHAR2 (1) := ''
1351 '';
1352 g_insert_statement VARCHAR2(32000);
1353 g_ln_item_vs_id NUMBER;
1354 g_li_eat_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id;
1355 g_li_eat_ver_id NUMBER := gcs_utility_pkg.g_dimension_attr_info (''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id;
1356
1357 g_eatc_batc_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info (''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id;
1358 g_eatc_batc_ver_id NUMBER := gcs_utility_pkg.g_dimension_attr_info (''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id;
1359 g_li_vs_id NUMBER := gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id;
1360 g_ledger_ssc_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info (''LEDGER_ID-SOURCE_SYSTEM_CODE'').attribute_id;
1361 g_ledger_ssc_ver_id NUMBER := gcs_utility_pkg.g_dimension_attr_info (''LEDGER_ID-SOURCE_SYSTEM_CODE'').version_id;
1362
1363 no_re_template_error EXCEPTION;
1364 no_suspense_template_error EXCEPTION;
1365 init_mapping_error EXCEPTION;
1366 no_data_error EXCEPTION;
1367 --
1368 -- Private Procedures
1369 --
1370 FUNCTION init_local_to_master_maps (
1371 p_source_ledger_id IN NUMBER,
1372 p_cal_period_id IN NUMBER,
1373 errbuf OUT NOCOPY VARCHAR2,
1374 retcode OUT NOCOPY VARCHAR2,
1375 p_inc_mode_flag IN VARCHAR2 DEFAULT NULL
1376 ) RETURN VARCHAR2
1377 IS
1378 l_source_global_vs_combo VARCHAR2 (30);
1379 l_index_column_name VARCHAR2 (30);
1380 l_source_value_set_id NUMBER;
1381 l_hierarchy_obj_def_id NUMBER (9);
1382 l_err_code NUMBER;
1383 l_err_msg NUMBER;
1384 l_mapping_required VARCHAR2(1) := ''N'';
1385 l_cctr_map_required BOOLEAN := FALSE;
1386 l_from_text VARCHAR2(1000);
1387 l_where_text VARCHAR2(10000);
1388 l_group_text VARCHAR2(1000);
1389 global_vs_id_error EXCEPTION;
1390 gcs_dp_no_hier_obj_def_id EXCEPTION;
1391 l_api_name VARCHAR2(30) := ''INIT_LOCAL_TO_MASTER_MAPS'';
1392 l_cal_attribute_id NUMBER;
1393 l_cal_version_id NUMBER;
1394 BEGIN
1395 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1396 THEN
1397 fnd_log.STRING (fnd_log.level_procedure,
1398 g_pkg_name || ''.'' || l_api_name,
1399 gcs_utility_pkg.g_module_enter
1400 || '' p_source_ledger_id = '' || p_source_ledger_id
1401 || '' ''
1402 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1403 );
1404 END IF;
1405 g_ln_item_vs_id := gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id;
1406 l_cal_attribute_id := gcs_utility_pkg.g_dimension_attr_info (''CAL_PERIOD_ID-CAL_PERIOD_END_DATE'').attribute_id;
1407 l_cal_version_id := gcs_utility_pkg.g_dimension_attr_info (''CAL_PERIOD_ID-CAL_PERIOD_END_DATE'').version_id;
1408 BEGIN
1409 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1410 THEN
1411 fnd_log.STRING (fnd_log.level_statement,
1412 g_pkg_name || ''.'' || l_api_name,
1413 '' SELECT fla.dim_attribute_numeric_member
1414 INTO l_source_global_vs_combo
1415 FROM fem_ledgers_attr fla,
1416 fem_dim_attributes_b fdab,
1417 fem_dim_attr_versions_b fdavb
1418 WHERE fla.ledger_id = '' || p_source_ledger_id || ''
1419 AND fla.attribute_id = fdab.attribute_id
1420 AND fdab.attribute_varchar_label = ''''GLOBAL_VS_COMBO''''
1421 AND fla.version_id = fdavb.version_id
1422 AND fdavb.attribute_id = fla.attribute_id
1423 AND fdavb.default_version_flag = ''''Y'''' '');
1424 END IF;
1425 SELECT fla.dim_attribute_numeric_member
1426 INTO l_source_global_vs_combo
1427 FROM fem_ledgers_attr fla,
1428 fem_dim_attributes_b fdab,
1429 fem_dim_attr_versions_b fdavb
1430 WHERE fla.ledger_id = p_source_ledger_id
1431 AND fla.attribute_id = fdab.attribute_id
1432 AND fdab.attribute_varchar_label = ''GLOBAL_VS_COMBO''
1433 AND fla.version_id = fdavb.version_id
1434 AND fdavb.attribute_id = fla.attribute_id
1435 AND fdavb.default_version_flag = ''Y'';
1436 EXCEPTION
1437 WHEN NO_DATA_FOUND THEN
1438 RAISE global_vs_id_error;
1439 END;
1440 g_insert_statement := ''' || l_insert_gt_clause ||
1441 '''||''
1442 SELECT decode(fb.currency_code,
1443 ''''STAT'''',
1444 :l_stat_entry_id,
1445 :l_entry_id), fb.cal_period_id, '';
1446 l_group_text := '' GROUP BY ' || l_fb_dims || ''';
1447 l_index_column_name := gcs_utility_pkg.g_gcs_dimension_info.FIRST;
1448 WHILE (l_index_column_name <= gcs_utility_pkg.g_gcs_dimension_info.LAST )
1449 LOOP
1450 IF ( (gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).associated_value_set_id IS NOT NULL)
1451 AND (l_index_column_name <> ''ENTITY_ID'')
1452 AND (gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).required_for_gcs = ''Y'')
1453 )
1454 THEN
1455 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1456 THEN
1457 fnd_log.STRING (fnd_log.level_statement,
1458 g_pkg_name || ''.'' || l_api_name,
1459 '' SELECT value_set_id
1460 INTO l_source_value_set_id
1461 FROM fem_global_vs_combo_defs
1462 WHERE global_vs_combo_id = ''||l_source_global_vs_combo||''
1463 AND dimension_id = ''||gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id);
1464 END IF;
1465 SELECT value_set_id
1466 INTO l_source_value_set_id
1467 FROM fem_global_vs_combo_defs
1468 WHERE global_vs_combo_id = l_source_global_vs_combo
1469 AND dimension_id = gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id;
1470
1471 IF ( (l_source_value_set_id IS NULL)
1472 OR (l_source_value_set_id = gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).associated_value_set_id)
1473 )
1474 THEN
1475 g_insert_statement := g_insert_statement || ''fb.'' || l_index_column_name || '', '';
1476 GOTO next_loop;
1477 ELSE
1478 gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).detail_value_set_id := l_source_value_set_id;
1479 l_mapping_required := ''Y'';
1480 END IF;
1481
1482 BEGIN
1483 SELECT fod.object_definition_id
1484 INTO l_hierarchy_obj_def_id
1485 FROM fem_xdim_dimensions fxd,
1486 fem_object_definition_b fod,
1487 fem_cal_periods_attr fcpa
1488 WHERE fxd.dimension_id = gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id
1489 AND fxd.default_mvs_hierarchy_obj_id = fod.object_id
1490 AND fcpa.cal_period_id = p_cal_period_id
1491 AND fcpa.attribute_id = l_cal_attribute_id
1492 AND fcpa.version_id = l_cal_version_id
1493 AND fcpa.date_assign_value BETWEEN fod.effective_start_date AND fod.effective_end_date;
1494 EXCEPTION
1495 WHEN NO_DATA_FOUND THEN
1496 raise gcs_dp_no_hier_obj_def_id;
1497 END;
1498 ';
1499 body_len := LENGTH(l_proc_body);
1500 curr_index := 1;
1501 WHILE curr_index <= body_len LOOP
1502 lines := lines + 1;
1503 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
1504 curr_index := curr_index + 200;
1505 END LOOP;
1506 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 1');
1507 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
1508 --
1509 -- create procedure init_local_to_master_maps
1510 --
1511 l_proc_body := '
1512 IF (l_index_column_name = ''NATURAL_ACCOUNT_ID'')
1513 THEN
1514 g_insert_statement := g_insert_statement
1515 || ''fnah.parent_id, '';
1516 l_from_text := l_from_text || '', fem_nat_accts_hier fnah '';
1517 l_where_text := l_where_text
1518 || '' AND fnah.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id
1519 || '' AND fnah.parent_depth_num = fnah.child_depth_num - 1
1520 AND fnah.child_value_set_id = '' || l_source_value_set_id || ''
1521 AND fnah.child_id = fb.NATURAL_ACCOUNT_ID
1522 AND fnah.parent_value_set_id = ''
1523 || gcs_utility_pkg.g_gcs_dimension_info (''NATURAL_ACCOUNT_ID'').associated_value_set_id;
1524 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fnah.parent_id '');
1525 ELSIF (l_index_column_name = ''COMPANY_COST_CENTER_ORG_ID'')
1526 THEN
1527 l_cctr_map_required := TRUE;
1528 g_insert_statement := g_insert_statement
1529 || ''fcoh.parent_id, '';
1530 l_from_text := l_from_text || '', fem_cctr_orgs_hier fcoh '';
1531 l_where_text := l_where_text
1532 || '' AND fcoh.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id
1533 || '' AND fcoh.parent_depth_num = fcoh.child_depth_num - 1
1534 AND fcoh.child_value_set_id = '' || l_source_value_set_id || ''
1535 AND fcoh.child_id = fb.COMPANY_COST_CENTER_ORG_ID
1536 AND fcoh.parent_value_set_id = ''
1537 || gcs_utility_pkg.g_gcs_dimension_info (''COMPANY_COST_CENTER_ORG_ID'').associated_value_set_id;
1538 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fcoh.parent_id '');
1539 ELSIF (l_index_column_name = ''INTERCOMPANY_ID'')
1540 THEN
1541 g_insert_statement := g_insert_statement
1542 || ''fcoh_inter.parent_id, '';
1543 l_from_text := l_from_text || '', fem_cctr_orgs_hier fcoh_inter '';
1544 l_where_text := l_where_text
1545 || '' AND fcoh_inter.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1546 AND fcoh_inter.parent_depth_num = fcoh_inter.child_depth_num - 1
1547 AND fcoh_inter.child_value_set_id = '' || l_source_value_set_id || ''
1548 AND fcoh_inter.child_id = fb.INTERCOMPANY_ID
1549 AND fcoh_inter.parent_value_set_id = ''
1550 || gcs_utility_pkg.g_gcs_dimension_info (''INTERCOMPANY_ID'').associated_value_set_id;
1551 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fcoh_inter.parent_id '');
1552 ELSIF (l_index_column_name = ''LINE_ITEM_ID'')
1553 THEN
1554 g_insert_statement := g_insert_statement
1555 || ''flih.parent_id, '';
1556 l_from_text := l_from_text || '', fem_ln_items_hier flih '';
1557 l_where_text := l_where_text
1558 || '' AND flih.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1559 AND flih.parent_depth_num = flih.child_depth_num - 1
1560 AND flih.child_value_set_id = '' || l_source_value_set_id || ''
1561 AND flih.child_id = fb.LINE_ITEM_ID
1562 AND flih.parent_value_set_id = ''
1563 || gcs_utility_pkg.g_gcs_dimension_info (''LINE_ITEM_ID'').associated_value_set_id;
1564 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''flih.parent_id '');
1565 g_ln_item_vs_id := l_source_value_set_id;
1566 ELSIF (l_index_column_name = ''PRODUCT_ID'')
1567 THEN
1568 g_insert_statement := g_insert_statement
1569 || ''fpdh.parent_id, '';
1570 l_from_text := l_from_text || '', fem_products_hier fpdh '';
1571 l_where_text := l_where_text
1572 || '' AND fpdh.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1573 AND fpdh.parent_depth_num = fpdh.child_depth_num - 1
1574 AND fpdh.child_value_set_id = '' || l_source_value_set_id || ''
1575 AND fpdh.child_id = fb.PRODUCT_ID
1576 AND fpdh.parent_value_set_id = ''
1577 || gcs_utility_pkg.g_gcs_dimension_info (''PRODUCT_ID'').associated_value_set_id;
1578 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fpdh.parent_id '');
1579 ELSIF (l_index_column_name = ''PROJECT_ID'')
1580 THEN
1581 g_insert_statement := g_insert_statement
1582 || ''fpjh.parent_id, '';
1583 l_from_text := l_from_text || '', fem_projects_hier fpjh '';
1584 l_where_text := l_where_text
1585 || '' AND fpjh.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1586 AND fpjh.parent_depth_num = fpjh.child_depth_num - 1
1587 AND fpjh.child_value_set_id = '' || l_source_value_set_id || ''
1588 AND fpjh.child_id = fb.PROJECT_ID
1589 AND fpjh.parent_value_set_id = ''
1590 || gcs_utility_pkg.g_gcs_dimension_info (''PROJECT_ID'').associated_value_set_id;
1591 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fpjh.parent_id '');
1592 ELSIF (l_index_column_name = ''CHANNEL_ID'')
1593 THEN
1594 g_insert_statement := g_insert_statement
1595 || ''fchh.parent_id, '';
1596 l_from_text := l_from_text || '', fem_channels_hier fchh '';
1597 l_where_text := l_where_text
1598 || '' AND fchh.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1599 AND fchh.parent_depth_num = fchh.child_depth_num - 1
1600 AND fchh.child_value_set_id = '' || l_source_value_set_id || ''
1601 AND fchh.child_id = fb.CHANNEL_ID
1602 AND fchh.parent_value_set_id = ''
1603 || gcs_utility_pkg.g_gcs_dimension_info (''CHANNEL_ID'').associated_value_set_id;
1604 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fchh.parent_id '');
1605 ELSIF (l_index_column_name = ''CUSTOMER_ID'')
1606 THEN
1607 g_insert_statement := g_insert_statement
1608 || ''fcuh.parent_id, '';
1609 l_from_text := l_from_text || '', fem_customers_hier fcuh '';
1610 l_where_text := l_where_text
1611 || '' AND fcuh.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1612 AND fcuh.parent_depth_num = fcuh.child_depth_num - 1
1613 AND fcuh.child_value_set_id = '' || l_source_value_set_id || ''
1614 AND fcuh.child_id = fb.CUSTOMER_ID
1615 AND fcuh.parent_value_set_id = ''
1616 || gcs_utility_pkg.g_gcs_dimension_info (''CUSTOMER_ID'').associated_value_set_id;
1617 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fcuh.parent_id '');
1618 ELSIF (l_index_column_name = ''USER_DIM1_ID'')
1619 THEN
1620 g_insert_statement := g_insert_statement
1621 || ''fud1h.parent_id, '';
1622 l_from_text := l_from_text || '', fem_user_dim1_hier fud1h '';
1623 l_where_text := l_where_text
1624 || '' AND fud1h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1625 AND fud1h.parent_depth_num = fud1h.child_depth_num - 1
1626 AND fud1h.child_value_set_id = '' || l_source_value_set_id || ''
1627 AND fud1h.child_id = fb.USER_DIM1_ID
1628 AND fud1h.parent_value_set_id = ''
1629 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM1_ID'').associated_value_set_id;
1630 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud1h.parent_id '');
1631 ELSIF (l_index_column_name = ''USER_DIM2_ID'')
1632 THEN
1633 g_insert_statement := g_insert_statement
1634 || ''fud2h.parent_id, '';
1635 l_from_text := l_from_text || '', fem_user_dim2_hier fud2h '';
1636 l_where_text := l_where_text
1637 || '' AND fud2h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1638 AND fud2h.parent_depth_num = fud2h.child_depth_num - 1
1639 AND fud2h.child_value_set_id = '' || l_source_value_set_id || ''
1640 AND fud2h.child_id = fb.USER_DIM2_ID
1641 AND fud2h.parent_value_set_id = ''
1642 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM2_ID'').associated_value_set_id;
1643 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud2h.parent_id '');
1644 ELSIF (l_index_column_name = ''USER_DIM3_ID'')
1645 THEN
1646 g_insert_statement := g_insert_statement
1647 || ''fud3h.parent_id, '';
1648 l_from_text := l_from_text || '', fem_user_dim3_hier fud3h '';
1649 l_where_text := l_where_text
1650 || '' AND fud3h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1651 AND fud3h.parent_depth_num = fud3h.child_depth_num - 1
1652 AND fud3h.child_value_set_id = '' || l_source_value_set_id || ''
1653 AND fud3h.child_id = fb.USER_DIM3_ID
1654 AND fud3h.parent_value_set_id = ''
1655 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM3_ID'').associated_value_set_id;
1656 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud3h.parent_id '');
1657 ELSIF (l_index_column_name = ''USER_DIM4_ID'')
1658 THEN
1659 g_insert_statement := g_insert_statement
1660 || ''fud4h.parent_id, '';
1661 l_from_text := l_from_text || '', fem_user_dim4_hier fud4h '';
1662 l_where_text := l_where_text
1663 || '' AND fud4h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1664 AND fud4h.parent_depth_num = fud4h.child_depth_num - 1
1665 AND fud4h.child_value_set_id = '' || l_source_value_set_id || ''
1666 AND fud4h.child_id = fb.USER_DIM4_ID
1667 AND fud4h.parent_value_set_id = ''
1668 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM4_ID'').associated_value_set_id;
1669 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud4h.parent_id '');
1670 ELSIF (l_index_column_name = ''USER_DIM5_ID'')
1671 THEN
1672 g_insert_statement := g_insert_statement
1673 || ''fud5h.parent_id, '';
1674 l_from_text := l_from_text || '', fem_user_dim5_hier fud5h '';
1675 l_where_text := l_where_text
1676 || '' AND fud5h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1677 AND fud5h.parent_depth_num = fud5h.child_depth_num - 1
1678 AND fud5h.child_value_set_id = '' || l_source_value_set_id || ''
1679 AND fud5h.child_id = fb.USER_DIM5_ID
1680 AND fud5h.parent_value_set_id = ''
1681 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM5_ID'').associated_value_set_id;
1682 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud5h.parent_id '');
1683 ELSIF (l_index_column_name = ''USER_DIM6_ID'')
1684 THEN
1685 g_insert_statement := g_insert_statement
1686 || ''fud6h.parent_id, '';
1687 l_from_text := l_from_text || '', fem_user_dim6_hier fud6h '';
1688 l_where_text := l_where_text
1689 || '' AND fud6h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1690 AND fud6h.parent_depth_num = fud6h.child_depth_num - 1
1691 AND fud6h.child_value_set_id = '' || l_source_value_set_id || ''
1692 AND fud6h.child_id = fb.USER_DIM6_ID
1693 AND fud6h.parent_value_set_id = ''
1694 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM6_ID'').associated_value_set_id;
1695 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud7h.parent_id '');
1696 ELSIF (l_index_column_name = ''USER_DIM7_ID'')
1697 THEN
1698 g_insert_statement := g_insert_statement
1699 || ''fud7h.parent_id, '';
1700 l_from_text := l_from_text || '', fem_user_dim7_hier fud7h '';
1701 l_where_text := l_where_text
1702 || '' AND fud7h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1703 AND fud7h.parent_depth_num = fud7h.child_depth_num - 1
1704 AND fud7h.child_value_set_id = '' || l_source_value_set_id || ''
1705 AND fud7h.child_id = fb.USER_DIM7_ID
1706 AND fud7h.parent_value_set_id = ''
1707 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM7_ID'').associated_value_set_id;
1708 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud7h.parent_id '');
1709 ELSIF (l_index_column_name = ''USER_DIM8_ID'')
1710 THEN
1711 g_insert_statement := g_insert_statement
1712 || ''fud8h.parent_id, '';
1713 l_from_text := l_from_text || '', fem_user_dim8_hier fud8h '';
1714 l_where_text := l_where_text
1715 || '' AND fud8h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1716 AND fud8h.parent_depth_num = fud8h.child_depth_num - 1
1717 AND fud8h.child_value_set_id = '' || l_source_value_set_id || ''
1718 AND fud8h.child_id = fb.USER_DIM8_ID
1719 AND fud8h.parent_value_set_id = ''
1720 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM8_ID'').associated_value_set_id;
1721 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud8h.parent_id '');
1722 ELSIF (l_index_column_name = ''USER_DIM9_ID'')
1723 THEN
1724 g_insert_statement := g_insert_statement
1725 || ''fud9h.parent_id, '';
1726 l_from_text := l_from_text || '', fem_user_dim9_hier fud9h '';
1727 l_where_text := l_where_text
1728 || '' AND fud9h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1729 AND fud9h.parent_depth_num = fud9h.child_depth_num - 1
1730 AND fud9h.child_value_set_id = '' || l_source_value_set_id || ''
1731 AND fud9h.child_id = fb.USER_DIM9_ID
1732 AND fud9h.parent_value_set_id = ''
1733 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM9_ID'').associated_value_set_id;
1734 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud9h.parent_id '');
1735 ELSIF (l_index_column_name = ''USER_DIM10_ID'')
1736 THEN
1737 g_insert_statement := g_insert_statement
1738 || ''fud10h.parent_id, '';
1739 l_from_text := l_from_text || '', fem_user_dim10_hier fud10h '';
1740 l_where_text := l_where_text
1741 || '' AND fud10h.hierarchy_obj_def_id = '' || l_hierarchy_obj_def_id || ''
1742 AND fud10h.parent_depth_num = fud10h.child_depth_num - 1
1743 AND fud10h.child_value_set_id = '' || l_source_value_set_id || ''
1744 AND fud10h.child_id = fb.USER_DIM10_ID
1745 AND fud10h.parent_value_set_id = ''
1746 || gcs_utility_pkg.g_gcs_dimension_info (''USER_DIM10_ID'').associated_value_set_id;
1747 l_group_text := replace(l_group_text, ''fb.''||l_index_column_name, ''fud10h.parent_id '');
1748 END IF;
1749 END IF;
1750 <<next_loop>>
1751 l_index_column_name :=gcs_utility_pkg.g_gcs_dimension_info.NEXT (l_index_column_name);
1752 END LOOP;
1753 ';
1754 curr_index := 1;
1755 body_len := LENGTH(l_proc_body);
1756 WHILE curr_index <= body_len LOOP
1757 lines := lines + 1;
1758 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
1759 curr_index := curr_index + 200;
1760 END LOOP;
1761 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 2');
1762 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
1763 --
1764 -- create procedure init_local_to_master_maps
1765 --
1766 l_proc_body := '
1767 IF (p_inc_mode_flag = ''Y'') THEN
1768 g_insert_statement := g_insert_statement || ''' ||
1769 l_inc_select_clause || ''';
1770 l_from_text := ''' || l_inc_from_clause ||
1771 '''|| l_from_text;
1772 l_where_text :=
1773 ''
1774 WHERE fb.ledger_id = :p_source_ledger_id
1775 AND fb.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
1776 AND fla.ledger_id = fb.ledger_id
1777 AND fla.attribute_id = gcs_utility_pkg.g_dimension_attr_info(''''LEDGER_ID-SOURCE_SYSTEM_CODE'''').attribute_id
1778 AND fla.version_id = gcs_utility_pkg.g_dimension_attr_info(''''LEDGER_ID-SOURCE_SYSTEM_CODE'''').version_id
1779 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
1780 AND geco.entity_id = :p_entity_id
1781 AND :p_balance_type_code = DECODE(fb.financial_elem_id, 140, ''''ADB'''', ''''ACTUAL'''')
1782 AND ((fb.currency_type_code = ''''TRANSLATED'''' AND fb.currency_code in (''''STAT'''', :p_source_currency_code))
1783 or (fb.currency_type_code = ''''ENTERED''''))
1784 AND fb.currency_type_code = :p_currency_type_code
1785 AND fb.dataset_code = :p_source_dataset_code
1786 AND fb.last_updated_by_request_id = gdsd.associated_request_id
1787 AND gcia.run_name = :p_run_name
1788 AND gcia.child_entity_id = :p_entity_id
1789 AND gcia.load_id = gdsd.load_id
1790 ''||l_where_text;
1791 ELSE
1792 g_insert_statement := g_insert_statement || ''' ||
1793 replace(replace(l_select_gt_clause, '''', ''''''),
1794 'p_max_period',
1795 ':p_max_period') || ''';
1796 l_from_text := ''' || l_from_gt_clause || '''|| l_from_text;
1797 l_where_text := ''
1798 WHERE :source_cal_period_id = fb.cal_period_id
1799 AND fb.ledger_id = :p_source_ledger_id
1800 AND fb.source_system_code = :source_system_code
1801 AND fb.currency_type_code = :p_currency_type_code
1802 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
1803 AND geco.entity_id = :p_entity_id
1804 AND fb.dataset_code = :source_dataset_code ''||l_where_text;
1805 END IF;
1806 if (l_cctr_map_required) then
1807 l_where_text := replace(l_where_text,
1808 ''AND fb.company_cost_center_org_id = geco.company_cost_center_org_id'',
1809 ''AND fcoh.parent_id = geco.company_cost_center_org_id'');
1810 end if;
1811 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1812 THEN
1813 fnd_log.STRING (fnd_log.level_statement,
1814 g_pkg_name || ''.'' || l_api_name,
1815 '' g_insert_statement = ''|| g_insert_statement
1816 );
1817 fnd_log.STRING (fnd_log.level_statement,
1818 g_pkg_name || ''.'' || l_api_name,
1819 '' l_from_text = ''|| l_from_text
1820 );
1821 fnd_log.STRING (fnd_log.level_statement,
1822 g_pkg_name || ''.'' || l_api_name,
1823 '' l_where_text = ''|| l_where_text
1824 );
1825 END IF;
1826 g_insert_statement := g_insert_statement
1827 || l_from_text
1828 || l_where_text;
1829 retcode := gcs_utility_pkg.g_ret_sts_success;
1830 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1831 THEN
1832 fnd_log.STRING (fnd_log.level_procedure,
1833 g_pkg_name || ''.'' || l_api_name,
1834 gcs_utility_pkg.g_module_success
1835 || '' Mapping Required : ''
1836 || l_mapping_required
1837 || '' ''
1838 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1839 );
1840 END IF;
1841 RETURN l_mapping_required;
1842 EXCEPTION
1843 WHEN gcs_dp_no_hier_obj_def_id THEN
1844 retcode := gcs_utility_pkg.g_ret_sts_error;
1845 fnd_message.set_name(''GCS'', ''GCS_DP_NO_HIER_OBJ_DEF_ERR'');
1846 fnd_message.set_token(''DIMENSION'', l_index_column_name);
1847 errbuf := fnd_message.get;
1848 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1849 THEN
1850 fnd_log.STRING (fnd_log.level_error,
1851 g_pkg_name || ''.'' || l_api_name,
1852 gcs_utility_pkg.g_module_failure
1853 || '' ''
1854 || errbuf
1855 || '' '' || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1856 );
1857 END IF;
1858 RETURN retcode;
1859 WHEN global_vs_id_error THEN
1860 retcode := gcs_utility_pkg.g_ret_sts_error;
1861 FND_MESSAGE.set_name(''GCS'', ''GCS_DP_GLOBAL_VS_ERR'');
1862 errbuf := fnd_message.get;
1863 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1864 THEN
1865 fnd_log.STRING (fnd_log.level_error,
1866 g_pkg_name || ''.'' || l_api_name,
1867 gcs_utility_pkg.g_module_failure
1868 || '' ''
1869 || errbuf
1870 || '' '' || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1871 );
1872 END IF;
1873 RETURN retcode;
1874 WHEN OTHERS THEN
1875 retcode := gcs_utility_pkg.G_RET_STS_UNEXP_ERROR;
1876 FND_MESSAGE.set_name(''GCS'', ''GCS_DP_UNEXP_ERR'');
1877 errbuf := fnd_message.get;
1878 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1879 THEN
1880 fnd_log.STRING (fnd_log.level_error,
1881 g_pkg_name || ''.'' || l_api_name,
1882 gcs_utility_pkg.g_module_failure
1883 || '' ''
1884 || errbuf
1885 || '' '' || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
1886 );
1887 END IF;
1888 RETURN retcode;
1889 END init_local_to_master_maps;
1890 ';
1891 body_len := LENGTH(l_proc_body);
1892 curr_index := 1;
1893 WHILE curr_index <= body_len LOOP
1894 lines := lines + 1;
1895 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
1896 curr_index := curr_index + 200;
1897 END LOOP;
1898 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 3');
1899 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
1900 l_insert_statement := l_insert_gt_clause ||
1901 ' SELECT decode(fb.currency_code, ''STAT'', l_stat_entry_id, l_entry_id), ' ||
1902 ' fb.cal_period_id, ' || l_fb_dims ||
1903 l_select_gt_clause || l_from_gt_clause ||
1904 l_where_gt_clause;
1905
1906 l_insert_from_gt_statement := l_insert_clause || '
1907 SELECT /*+ PARALLEL (fb) */ fb.entry_id, ' ||
1908 l_fb_dims || '
1909 SUM (NVL (fb.ptd_debit_balance_e, 0)),
1910 SUM (NVL (fb.ptd_credit_balance_e, 0)),
1911 SUM (NVL (ytd_debit_balance_e, 0)),
1912 SUM (NVL (ytd_credit_balance_e, 0)),
1913 SUM(DECODE(fea_attr.dim_attribute_varchar_member, ''REVENUE'', NVL(xtd_balance_e,0),
1914 ''EXPENSE'', NVL(xtd_balance_e,0),
1915 NVL(ytd_balance_e,0))),
1916 SUM (NVL (ytd_balance_e, 0)),
1917 SYSDATE,
1918 FND_GLOBAL.USER_ID,
1919 SYSDATE,
1920 FND_GLOBAL.USER_ID,
1921 FND_GLOBAL.LOGIN_ID
1922 FROM gcs_entry_lines_gt fb,
1923 fem_ln_items_attr flia,
1924 fem_ext_acct_types_attr fea_attr
1925 WHERE fb.line_item_id = flia.line_item_id
1926 AND flia.attribute_id = l_line_item_type_attr
1927 AND flia.version_id = l_line_item_type_version
1928 AND flia.dim_attribute_varchar_member = fea_attr.ext_account_type_code
1929 AND fea_attr.attribute_id = l_acct_type_attr
1930 AND fea_attr.version_id = l_acct_type_version
1931 GROUP BY ' || l_fb_dims ||
1932 ' entry_id;';
1933
1934 l_inc_insert_statement := l_insert_clause ||
1935 ' SELECT decode(fb.currency_code, ''STAT'', x_stat_entry_id, x_entry_id), ' ||
1936 l_fb_dims || l_inc_select_clause ||
1937 l_inc_from_clause || l_inc_where_clause ||
1938 ' GROUP BY ' || l_fb_dims ||
1939 ' decode(fb.currency_code, ''STAT'', x_stat_entry_id, x_entry_id);';
1940 l_proc_body := '
1941 --
1942 -- Public Procedures
1943 --
1944 PROCEDURE process_data (
1945 p_source_currency_code IN VARCHAR2,
1946 p_target_cal_period_id IN NUMBER,
1947 p_max_period IN NUMBER,
1948 p_currency_type_code IN VARCHAR2,
1949 p_hierarchy_id IN NUMBER,
1950 p_entity_id IN NUMBER,
1951 p_source_ledger_id IN NUMBER,
1952 p_year_end_values_match IN VARCHAR2,
1953 p_cal_period_record IN gcs_utility_pkg.r_cal_period_info,
1954 p_balance_type_code IN VARCHAR2,
1955 p_owner_percentage IN NUMBER,
1956 p_run_detail_id IN NUMBER,
1957 p_source_dataset_code IN NUMBER,
1958 errbuf OUT NOCOPY VARCHAR2,
1959 retcode OUT NOCOPY VARCHAR2
1960 )
1961 IS
1962 l_has_row_flag VARCHAR2 (1);
1963 l_has_stat_row_flag VARCHAR2 (1);
1964 l_first_ever_data_prepped VARCHAR2 (1);
1965 l_temp_record gcs_templates_pkg.templaterecord;
1966 l_threshold NUMBER;
1967 l_threshold_currency VARCHAR2(15);
1968 l_entry_id NUMBER (15) := NULL;
1969 l_stat_entry_id NUMBER (15) := NULL;
1970 l_proportional_entry_id NUMBER (15) := NULL;
1971 l_stat_proportional_entry_id NUMBER (15):= NULL;
1972 l_mapping_required VARCHAR2 (1);
1973 l_precision NUMBER;
1974 l_stat_precision NUMBER;
1975 l_api_name VARCHAR2 (20) := ''PROCESS_DATA'';
1976 l_imap_enabled_flag VARCHAR2 (1);
1977 l_source_system_code NUMBER;
1978 l_periods_list DBMS_SQL.number_table;
1979
1980 -- Bug Fix: 5843592, Get the attribute id and version id of the CAL_PERIOD_END_DATE of calendar period
1981
1982 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info(''CAL_PERIOD_ID-CAL_PERIOD_END_DATE'')
1983 .attribute_id;
1984 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info(''CAL_PERIOD_ID-CAL_PERIOD_END_DATE'')
1985 .version_id ;
1986
1987 -- Bugfix 6068527: Added account type attributes
1988 l_line_item_type_attr NUMBER(15) :=
1989 gcs_utility_pkg.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').attribute_id;
1990 l_line_item_type_version NUMBER(15) :=
1991 gcs_utility_pkg.g_dimension_attr_info(''LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE'').version_id;
1992 l_acct_type_attr NUMBER(15) :=
1993 gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').attribute_id;
1994 l_acct_type_version NUMBER(15) :=
1995 gcs_utility_pkg.g_dimension_attr_info(''EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE'').version_id;
1996
1997 BEGIN
1998 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1999 THEN
2000 fnd_log.STRING (fnd_log.level_procedure,
2001 g_pkg_name || ''.'' || l_api_name,
2002 gcs_utility_pkg.g_module_enter
2003 || '' p_source_currency_code = '' || p_source_currency_code
2004 || '', p_target_cal_period_id = '' || p_target_cal_period_id
2005 || '', p_max_period = '' || p_max_period
2006 || '', p_currency_type_code = '' || p_currency_type_code
2007 || '', p_hierarchy_id = '' || p_hierarchy_id
2008 || '', p_entity_id = '' || p_entity_id
2009 || '', p_source_ledger_id = '' || p_source_ledger_id
2010 || '', p_year_end_values_match = '' || p_year_end_values_match
2011 || '', p_balance_type_code = '' || p_balance_type_code
2012 || '', p_owner_percentage = '' || p_owner_percentage
2013 || '', p_run_detail_id = '' || p_run_detail_id
2014 || '', p_source_dataset_code = '' || p_source_dataset_code
2015 || '' ''
2016 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2017 );
2018 END IF;
2019
2020 -- init local_to_master mappping
2021 l_mapping_required := init_local_to_master_maps (p_source_ledger_id => p_source_ledger_id,
2022 p_cal_period_id => p_cal_period_record.cal_period_id,
2023 errbuf => errbuf,
2024 retcode => retcode);
2025
2026 IF (retcode <> gcs_utility_pkg.g_ret_sts_success)
2027 THEN
2028 RAISE init_mapping_error;
2029 END IF;
2030
2031 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2032 THEN
2033 fnd_log.STRING (fnd_log.level_statement,
2034 g_pkg_name || ''.'' || l_api_name,
2035 ''Mapping required flag: '' || l_mapping_required);
2036 END IF;
2037
2038 -- bug fix 5074999: raise no_data_error when source_system_code not found
2039
2040 -- Bugfix 5843592, Get the source_system_code of the correct entity, depending upon the calendar period
2041
2042 BEGIN
2043 SELECT gea.source_system_code
2044 INTO l_source_system_code
2045 FROM gcs_entities_attr gea,
2046 fem_cal_periods_attr fcpa
2047 WHERE gea.entity_id = p_entity_id
2048 AND gea.data_type_code = p_balance_type_code
2049 AND fcpa.cal_period_id = p_target_cal_period_id
2050 AND fcpa.attribute_id = l_period_end_date_attr
2051 AND fcpa.version_id = l_period_end_date_version
2052 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
2053 AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
2054
2055
2056 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2057 THEN
2058 fnd_log.STRING (fnd_log.level_statement,
2059 g_pkg_name || ''.'' || l_api_name,
2060 ''Source system code: '' || l_source_system_code);
2061 END IF;
2062 EXCEPTION
2063 WHEN no_data_found THEN
2064 RAISE no_data_error;
2065 END;
2066
2067 -- bug fix 5074999: remove join to p_balance_type_code, which is redundant and incorrect
2068 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2069 THEN
2070 fnd_log.STRING (fnd_log.level_statement,
2071 g_pkg_name || ''.'' || l_api_name,
2072 ''SELECT cpmgt.source_cal_period_id cal_period_id
2073 BULK COLLECT INTO l_periods_list
2074 FROM fem_data_locations fdl,
2075 gcs_cal_period_maps_gt cpmgt
2076 WHERE fdl.ledger_id = '' ||p_source_ledger_id||''
2077 AND fdl.cal_period_id = cpmgt.source_cal_period_id
2078 AND fdl.source_system_code = ''||l_source_system_code||''
2079 AND fdl.dataset_code = '' ||p_source_dataset_code||''
2080 AND fdl.table_name = ''''FEM_BALANCES'''''');
2081 END IF;
2082
2083 SELECT cpmgt.source_cal_period_id cal_period_id
2084 BULK COLLECT INTO l_periods_list
2085 FROM fem_data_locations fdl,
2086 gcs_cal_period_maps_gt cpmgt
2087 WHERE fdl.ledger_id = p_source_ledger_id
2088 AND fdl.cal_period_id = cpmgt.source_cal_period_id
2089 AND fdl.source_system_code = l_source_system_code
2090 AND fdl.dataset_code = p_source_dataset_code
2091 AND fdl.table_name = ''FEM_BALANCES'';
2092
2093 IF l_periods_list.FIRST IS NULL THEN
2094 RAISE no_data_error;
2095 END IF;
2096
2097 SELECT gcs_entry_headers_s.NEXTVAL
2098 INTO l_entry_id
2099 FROM DUAL;
2100 SELECT gcs_entry_headers_s.NEXTVAL
2101 INTO l_stat_entry_id
2102 FROM DUAL;
2103
2104 -------------------------------------------
2105 -- this is the mapping not required case --
2106 -------------------------------------------
2107 IF (l_mapping_required = ''N'') THEN
2108 IF (p_balance_type_code = ''ADB'' AND p_currency_type_code = ''ENTERED'') THEN
2109 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
2110 ' || l_insert_statement || '
2111 AND fb.currency_code IN (p_source_currency_code, ''STAT'')
2112 AND fb.financial_elem_id = 140;
2113 ELSIF (p_balance_type_code = ''ADB'') THEN
2114 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
2115 ' || l_insert_statement || '
2116 AND fb.financial_elem_id = 140
2117 AND fb.currency_code = p_source_currency_code;
2118 ELSIF (p_currency_type_code = ''ENTERED'') THEN
2119 --Bugfix 5232063: Do not assume Financial Element is populated
2120 --Bugfix 5329620: Added l_curr_where_clause
2121 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
2122 ' || l_insert_statement || l_curr_where_clause || ';
2123 ELSE
2124 --Bugfix 5232063: Do not assume Financial Element is populated
2125 --Bugfix 5329620: Added l_curr_where_clause
2126 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
2127 ' || l_insert_statement || l_curr_where_clause || ';
2128 END IF; -- p_balance_type_code
2129
2130 ELSE';
2131
2132 body_len := LENGTH(l_proc_body);
2133 curr_index := 1;
2134 WHILE curr_index <= body_len LOOP
2135 lines := lines + 1;
2136 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
2137 curr_index := curr_index + 200;
2138 END LOOP;
2139 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 4');
2140 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
2141
2142 l_proc_body := '---------------------------------------
2143 -- this is the mapping required case --
2144 ---------------------------------------
2145 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2146 THEN
2147 fnd_log.STRING (fnd_log.level_statement,
2148 g_pkg_name || ''.'' || l_api_name,
2149 '' g_insert_statement = ''
2150 || g_insert_statement
2151 );
2152 fnd_log.STRING (fnd_log.level_statement,
2153 g_pkg_name || ''.'' || l_api_name,
2154 '' FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST ''
2155 || g_nl
2156 || '' EXECUTE IMMEDIATE g_insert_statement ''
2157 || g_nl
2158 || '' USING ''
2159 || l_stat_entry_id || '', '' || l_entry_id ||'', ''
2160 || p_max_period ||'', ''|| p_max_period ||'', '' || p_max_period ||'', ''
2161 || '' l_periods_list(counter), '' || p_source_ledger_id ||'', ''
2162 || '' l_source_system_code, ''
2163 || p_currency_type_code ||'', ''
2164 || p_entity_id ||'', ''|| p_balance_type_code ||'', ''
2165 || p_source_currency_code ||'', ''
2166 || '' p_source_dataset_code ''
2167 || '' p_source_currency_code ''
2168 );
2169 END IF;
2170
2171 IF (p_balance_type_code = ''ADB'' AND p_currency_type_code = ''ENTERED'') THEN
2172 g_insert_statement := g_insert_statement || ''
2173 AND fb.financial_elem_id = 140
2174 AND fb.currency_code IN (:p_source_currency_code, ''''STAT'''') '';
2175 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
2176 EXECUTE IMMEDIATE g_insert_statement
2177 USING l_stat_entry_id, l_entry_id,
2178 p_max_period, p_max_period, p_max_period,
2179 l_periods_list(counter), p_source_ledger_id,
2180 l_source_system_code,
2181 p_currency_type_code, p_entity_id,
2182 p_source_dataset_code, p_source_currency_code;
2183 ELSIF (p_balance_type_code = ''ADB'') THEN
2184 g_insert_statement := g_insert_statement || ''
2185 AND fb.financial_elem_id = 140
2186 AND fb.currency_code = :p_source_currency_code '';
2187 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
2188 EXECUTE IMMEDIATE g_insert_statement
2189 USING l_stat_entry_id, l_entry_id,
2190 p_max_period, p_max_period, p_max_period,
2191 l_periods_list(counter), p_source_ledger_id,
2192 l_source_system_code,
2193 p_currency_type_code, p_entity_id, p_source_dataset_code,
2194 p_source_currency_code;
2195 ELSIF (p_currency_type_code = ''ENTERED'') THEN
2196 --Bugfix 5232063: Do not assume Financial Element is populated
2197 --Bugfix 5329620: Added curr_vs_map_where clause
2198 g_insert_statement := g_insert_statement || ''' ||
2199 l_curr_vs_map_where_clause || ''';
2200 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
2201 EXECUTE IMMEDIATE g_insert_statement
2202 USING l_stat_entry_id, l_entry_id,
2203 p_max_period, p_max_period, p_max_period,
2204 l_periods_list(counter), p_source_ledger_id,
2205 l_source_system_code,
2206 p_currency_type_code, p_entity_id,
2207 p_source_dataset_code, p_source_currency_code;
2208 ELSE
2209 --Bugfix 5232063: Do not assume Financial Element is populated
2210 --Bugfix 5329620: Added curr_vs_map_where clause
2211 g_insert_statement := g_insert_statement || ''' ||
2212 l_curr_vs_map_where_clause ||
2213 ''';
2214 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
2215 EXECUTE IMMEDIATE g_insert_statement
2216 USING l_stat_entry_id, l_entry_id,
2217 p_max_period, p_max_period, p_max_period,
2218 l_periods_list(counter), p_source_ledger_id,
2219 l_source_system_code,
2220 p_currency_type_code, p_entity_id, p_source_dataset_code,
2221 p_source_currency_code;
2222 END IF; -- p_balance_type_code
2223
2224 END IF; -- end of mapping required check
2225
2226 -- check if there''s any data selected
2227 IF (SQL%ROWCOUNT = 0) THEN
2228 RAISE no_data_error;
2229 END IF;
2230
2231 BEGIN
2232 SELECT ''Y''
2233 INTO l_has_row_flag
2234 FROM DUAL
2235 WHERE EXISTS (SELECT 1 FROM gcs_entry_lines_gt WHERE entry_id = l_entry_id);
2236
2237 gcs_entry_pkg.create_entry_header
2238 (x_errbuf => errbuf,
2239 x_retcode => retcode,
2240 p_entry_id => l_entry_id,
2241 p_hierarchy_id => p_hierarchy_id,
2242 p_entity_id => p_entity_id,
2243 p_start_cal_period_id => p_target_cal_period_id,
2244 p_end_cal_period_id => p_target_cal_period_id,
2245 p_entry_type_code => ''AUTOMATIC'',
2246 p_balance_type_code => p_balance_type_code,
2247 p_currency_code => p_source_currency_code,
2248 p_process_code => ''SINGLE_RUN_FOR_PERIOD'',
2249 p_category_code => ''DATAPREPARATION''
2250 );
2251 IF p_owner_percentage IS NOT NULL AND p_owner_percentage <> 1 THEN
2252 SELECT gcs_entry_headers_s.NEXTVAL
2253 INTO l_proportional_entry_id
2254 FROM DUAL;
2255
2256 gcs_entry_pkg.create_entry_header
2257 (x_errbuf => errbuf,
2258 x_retcode => retcode,
2259 p_entry_id => l_proportional_entry_id,
2260 p_hierarchy_id => p_hierarchy_id,
2261 p_entity_id => p_entity_id,
2262 p_start_cal_period_id => p_target_cal_period_id,
2263 p_end_cal_period_id => p_target_cal_period_id,
2264 p_entry_type_code => ''AUTOMATIC'',
2265 p_balance_type_code => p_balance_type_code,
2266 p_currency_code => p_source_currency_code,
2267 p_process_code => ''SINGLE_RUN_FOR_PERIOD'',
2268 p_category_code => ''DATAPREPARATION''
2269 );
2270 END IF;
2271 EXCEPTION
2272 WHEN NO_DATA_FOUND THEN
2273 l_has_row_flag := ''N'';
2274 l_entry_id := NULL;
2275 END;
2276 BEGIN
2277 SELECT ''Y''
2278 INTO l_has_stat_row_flag
2279 FROM DUAL
2280 WHERE EXISTS (SELECT 1 FROM gcs_entry_lines_gt WHERE entry_id = l_stat_entry_id);
2281
2282 gcs_entry_pkg.create_entry_header
2283 (x_errbuf => errbuf,
2284 x_retcode => retcode,
2285 p_entry_id => l_stat_entry_id,
2286 p_hierarchy_id => p_hierarchy_id,
2287 p_entity_id => p_entity_id,
2288 p_start_cal_period_id => p_target_cal_period_id,
2289 p_end_cal_period_id => p_target_cal_period_id,
2290 p_entry_type_code => ''AUTOMATIC'',
2291 p_balance_type_code => p_balance_type_code,
2292 p_currency_code => ''STAT'',
2293 p_process_code => ''SINGLE_RUN_FOR_PERIOD'',
2294 p_category_code => ''DATAPREPARATION''
2295 );
2296
2297 IF p_owner_percentage IS NOT NULL AND p_owner_percentage <> 1 THEN
2298 SELECT gcs_entry_headers_s.NEXTVAL
2299 INTO l_stat_proportional_entry_id
2300 FROM DUAL;
2301
2302 gcs_entry_pkg.create_entry_header
2303 (x_errbuf => errbuf,
2304 x_retcode => retcode,
2305 p_entry_id => l_stat_proportional_entry_id,
2306 p_hierarchy_id => p_hierarchy_id,
2307 p_entity_id => p_entity_id,
2308 p_start_cal_period_id => p_target_cal_period_id,
2309 p_end_cal_period_id => p_target_cal_period_id,
2310 p_entry_type_code => ''AUTOMATIC'',
2311 p_balance_type_code => p_balance_type_code,
2312 p_currency_code => ''STAT'',
2313 p_process_code => ''SINGLE_RUN_FOR_PERIOD'',
2314 p_category_code => ''DATAPREPARATION''
2315 );
2316 END IF;
2317 EXCEPTION
2318 WHEN NO_DATA_FOUND THEN
2319 l_has_stat_row_flag := ''N'';
2320 l_stat_entry_id := NULL;
2321 END;
2322
2323 -- insert data into gcs_entry_lines table
2324 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2325 THEN
2326 fnd_log.STRING (fnd_log.level_statement,
2327 g_pkg_name || ''.'' || l_api_name,
2328 ''' ||
2329 replace(l_insert_from_gt_statement, '''', '''''') || '''
2330 );
2331 END IF;
2332
2333 ' || l_insert_from_gt_statement;
2334
2335 body_len := LENGTH(l_proc_body);
2336 curr_index := 1;
2337 WHILE curr_index <= body_len LOOP
2338 lines := lines + 1;
2339 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
2340 curr_index := curr_index + 200;
2341 END LOOP;
2342 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 5');
2343 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
2344 l_proc_body := '
2345
2346 COMMIT;
2347
2348 -- recalculate P/L AND Retained Earnings accounts if year ends not match
2349 IF (l_entry_id IS NOT NULL AND p_year_end_values_match = ''N'') THEN
2350 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2351 THEN
2352 fnd_log.STRING (fnd_log.level_statement,
2353 g_pkg_name || ''.'' || l_api_name,
2354 '' SELECT decode(count(run_name), 0, ''''Y'''', ''''N'''')
2355 INTO l_first_ever_data_prepped
2356 FROM gcs_cons_eng_runs
2357 WHERE hierarchy_id = ''||p_hierarchy_id||''
2358 AND run_entity_id = ''||p_entity_id||''
2359 AND balance_type_code = ''||p_balance_type_code||''
2360 AND (cal_period_id = ''||p_cal_period_record.prev_cal_period_id||''
2361 OR (cal_period_id = ''||p_cal_period_record.cal_period_id||''
2362 AND status_code NOT IN (''''NOT_STARTED'''', ''''IN_PROGRESS'''')))'');
2363 END IF;
2364 SELECT decode(count(run_name), 0, ''Y'', ''N'')
2365 INTO l_first_ever_data_prepped
2366 FROM gcs_cons_eng_runs
2367 WHERE hierarchy_id = p_hierarchy_id
2368 AND run_entity_id = p_entity_id
2369 AND balance_type_code = p_balance_type_code
2370 AND ( cal_period_id = p_cal_period_record.prev_cal_period_id
2371 OR (cal_period_id = p_cal_period_record.cal_period_id
2372 AND status_code NOT IN (''NOT_STARTED'', ''IN_PROGRESS'')));
2373 IF ( l_first_ever_data_prepped = ''Y'' OR p_cal_period_record.cal_period_number = 1 )
2374 THEN
2375 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2376 THEN
2377 fnd_log.STRING (fnd_log.level_statement,
2378 g_pkg_name || ''.'' || l_api_name,
2379 '' UPDATE gcs_entry_lines gel
2380 SET gel.ytd_balance_e = gel.xtd_balance_e,
2381 gel.ytd_debit_balance_e = gel.ptd_debit_balance_e,
2382 gel.ytd_credit_balance_e = gel.ptd_credit_balance_e
2383 WHERE gel.entry_id = ''||l_entry_id ||''
2384 AND EXISTS ( SELECT ''''X''''
2385 FROM fem_ln_items_attr flia,
2386 fem_ext_acct_types_attr feata
2387 WHERE feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
2388 AND flia.attribute_id =''|| g_li_eat_attr_id ||''
2389 AND flia.version_id =''|| g_li_eat_ver_id ||''
2390 AND flia.value_set_id =''|| g_li_vs_id ||''
2391 AND feata.attribute_id = '' || g_eatc_batc_attr_id || ''
2392 AND feata.version_id = '' || g_eatc_batc_ver_id || ''
2393 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
2394 AND gel.line_item_id = flia.line_item_id)'');
2395 END IF;
2396 UPDATE gcs_entry_lines gel
2397 SET gel.ytd_balance_e = gel.xtd_balance_e,
2398 gel.ytd_debit_balance_e = gel.ptd_debit_balance_e,
2399 gel.ytd_credit_balance_e = gel.ptd_credit_balance_e
2400 WHERE gel.entry_id = l_entry_id
2401 AND EXISTS ( SELECT ''X''
2402 FROM fem_ln_items_attr flia,
2403 fem_ext_acct_types_attr feata
2404 WHERE feata.dim_attribute_varchar_member IN (''REVENUE'', ''EXPENSE'')
2405 AND flia.attribute_id = g_li_eat_attr_id
2406 AND flia.version_id = g_li_eat_ver_id
2407 AND flia.value_set_id = g_li_vs_id
2408 AND feata.attribute_id = g_eatc_batc_attr_id
2409 AND feata.version_id = g_eatc_batc_ver_id
2410 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
2411 AND gel.line_item_id = flia.line_item_id);
2412 ELSE
2413 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2414 THEN
2415 fnd_log.STRING (fnd_log.level_statement,
2416 g_pkg_name || ''.'' || l_api_name,
2417 '' UPDATE gcs_entry_lines gel
2418 SET (gel.ytd_balance_e, gel.ytd_credit_balance_e,gel.ytd_debit_balance_e) =
2419 (SELECT NVL (fb.ytd_balance_e, 0)
2420 + NVL (gel.xtd_balance_e, 0),
2421 NVL (fb.ytd_credit_balance_e, 0)
2422 + NVL (gel.ptd_credit_balance_e, 0),
2423 NVL (fb.ytd_debit_balance_e, 0)
2424 + NVL (gel.ptd_debit_balance_e, 0)
2425 FROM fem_balances fb,
2426 fem_ln_items_attr flia,
2427 fem_ext_acct_types_attr feata
2428 WHERE feata.dim_attribute_varchar_member IN
2429 (''''REVENUE'''', ''''EXPENSE'''')
2430 AND flia.attribute_id =''|| g_li_eat_attr_id ||''
2431 AND flia.version_id =''|| g_li_eat_ver_id ||''
2432 AND flia.value_set_id =''|| g_li_vs_id ||''
2433 AND feata.attribute_id = '' || g_eatc_batc_attr_id || ''
2434 AND feata.version_id = '' || g_eatc_batc_ver_id || ''
2435 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
2436 AND fb.cal_period_id =''|| p_cal_period_record.prev_cal_period_id||''
2437 AND fb.line_item_id = flia.line_item_id
2438 AND fb.source_system_code = '' || l_source_system_code||''' || g_nl ||
2439 get_dimension_text('AND fb.', '= gel.', 'Y') || ')
2440 WHERE gel.entry_id = ''||l_entry_id);
2441 END IF;
2442 UPDATE gcs_entry_lines gel
2443 SET (gel.ytd_balance_e, gel.ytd_credit_balance_e,gel.ytd_debit_balance_e) =
2444 (SELECT NVL (fb.ytd_balance_e, 0)
2445 + NVL (gel.xtd_balance_e, 0),
2446 NVL (fb.ytd_credit_balance_e, 0)
2447 + NVL (gel.ptd_credit_balance_e, 0),
2448 NVL (fb.ytd_debit_balance_e, 0)
2449 + NVL (gel.ptd_debit_balance_e, 0)
2450 FROM fem_balances fb,
2451 fem_ln_items_attr flia,
2452 fem_ext_acct_types_attr feata
2453 WHERE feata.dim_attribute_varchar_member IN (''REVENUE'', ''EXPENSE'')
2454 AND flia.attribute_id = g_li_eat_attr_id
2455 AND flia.version_id = g_li_eat_ver_id
2456 AND flia.value_set_id = g_li_vs_id
2457 AND feata.attribute_id = g_eatc_batc_attr_id
2458 AND feata.version_id = g_eatc_batc_ver_id
2459 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
2460 AND fb.cal_period_id = p_cal_period_record.prev_cal_period_id
2461 AND fb.line_item_id = flia.line_item_id
2462 AND fb.source_system_code = l_source_system_code' || g_nl ||
2463 get_dimension_text('AND fb.', '= gel.', 'Y') || ')
2464 WHERE gel.entry_id = l_entry_id;
2465 END IF;
2466 GCS_templates_dynamic_PKG.CALCULATE_DP_RE(p_entry_id => l_entry_id,
2467 p_hierarchy_id => p_hierarchy_id,
2468 p_bal_type_code => p_balance_type_code,
2469 p_entity_id => p_entity_id,
2470 p_pre_cal_period_id => p_cal_period_record.prev_cal_period_id,
2471 p_first_ever_data_prep => l_first_ever_data_prepped);
2472 END IF;
2473 retcode := gcs_utility_pkg.g_ret_sts_success;
2474 -- Suspense accounts
2475 IF l_entry_id IS NOT NULL
2476 THEN
2477 -- check balance criteria
2478 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2479 THEN
2480 fnd_log.STRING (fnd_log.level_statement,
2481 g_pkg_name || ''.'' || l_api_name,
2482 '' SELECT threshold_amount,
2483 threshold_currency
2484 INTO l_threshold,
2485 l_threshold_currency
2486 FROM gcs_hierarchies_b
2487 WHERE hierarchy_id = ''||p_hierarchy_id);
2488 END IF;
2489 SELECT threshold_amount, threshold_currency
2490 INTO l_threshold, l_threshold_currency
2491 FROM gcs_hierarchies_b
2492 WHERE hierarchy_id = p_hierarchy_id;
2493 BEGIN
2494 gcs_templates_pkg.get_dimension_template
2495 (p_hierarchy_id => p_hierarchy_id,
2496 p_template_code => ''SUSPENSE'',
2497 p_balance_type_code => p_balance_type_code,
2498 p_template_record => l_temp_record
2499 );
2500 EXCEPTION
2501 WHEN OTHERS
2502 THEN
2503 RAISE no_suspense_template_error;
2504 END;
2505 gcs_templates_dynamic_pkg.balance (p_entry_id => l_entry_id,
2506 p_template => l_temp_record,
2507 p_bal_type_code => p_balance_type_code,
2508 p_hierarchy_id => p_hierarchy_id,
2509 p_entity_id => p_entity_id,
2510 p_threshold => l_threshold,
2511 p_threshold_currency_code => l_threshold_currency
2512 );
2513 --bug fix 3797312
2514 SELECT DECODE(SUSPENSE_EXCEEDED_FLAG, ''Y'', ''WARNING'', ''COMPLETED'')
2515 INTO retcode
2516 FROM gcs_entry_headers
2517 WHERE entry_id = l_entry_id;
2518 END IF;
2519 ';
2520 curr_index := 1;
2521 body_len := LENGTH(l_proc_body);
2522 WHILE curr_index <= body_len LOOP
2523 lines := lines + 1;
2524 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
2525 curr_index := curr_index + 200;
2526 END LOOP;
2527 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 6');
2528 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
2529 --
2530 -- create procedure init_local_to_master_maps
2531 --
2532 l_proc_body := '
2533 -- bug fix 3800183
2534 IF (l_proportional_entry_id IS NOT NULL or l_stat_proportional_entry_id IS NOT NULL)
2535 THEN
2536 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2537 THEN
2538 fnd_log.STRING (fnd_log.level_statement,
2539 g_pkg_name || ''.'' || l_api_name,
2540 '' SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
2541 NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
2542 INTO l_precision,
2543 l_stat_precision
2544 FROM fnd_currencies fc_1, fnd_currencies fc_stat
2545 WHERE fc_1.currency_code = '' ||p_source_currency_code||''
2546 AND fc_stat.currency_code = ''''STAT'''''');
2547 END IF;
2548 SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
2549 NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
2550 INTO l_precision, l_stat_precision
2551 FROM fnd_currencies fc_1, fnd_currencies fc_stat
2552 WHERE fc_1.currency_code = p_source_currency_code
2553 AND fc_stat.currency_code = ''STAT'';
2554 INSERT INTO gcs_entry_lines
2555 (entry_id,' || l_dims || '
2556 ptd_debit_balance_e,
2557 ptd_credit_balance_e,
2558 ytd_debit_balance_e,
2559 ytd_credit_balance_e,
2560 xtd_balance_e,
2561 ytd_balance_e,
2562 creation_date, created_by, last_update_date,
2563 last_updated_by, last_update_login
2564 )
2565 SELECT decode(entry_id, l_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id),
2566 ' || l_dims || '
2567 ROUND(ptd_debit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
2568 ROUND(ptd_credit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
2569 ROUND(ytd_debit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
2570 ROUND(ytd_credit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
2571 ROUND(ptd_debit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision)
2572 - ROUND(ptd_credit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
2573 ROUND(ytd_debit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision)
2574 - ROUND(ytd_credit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
2575 creation_date, created_by, last_update_date,
2576 last_updated_by, last_update_login
2577 FROM gcs_entry_lines
2578 WHERE entry_id in ( l_entry_id, l_stat_entry_id);
2579 -- end of bug fix 3800183
2580 --bug fix 3797312
2581 IF l_proportional_entry_id IS NOT NULL THEN
2582 gcs_templates_dynamic_pkg.balance (p_entry_id => l_proportional_entry_id,
2583 p_template => l_temp_record,
2584 p_bal_type_code => p_balance_type_code,
2585 p_hierarchy_id => p_hierarchy_id,
2586 p_entity_id => p_entity_id,
2587 p_threshold => l_threshold,
2588 p_threshold_currency_code => l_threshold_currency
2589 );
2590 SELECT decode(SUSPENSE_EXCEEDED_FLAG, ''Y'', ''WARNING'', ''COMPLETED'')
2591 INTO retcode
2592 FROM gcs_entry_headers
2593 WHERE entry_id = l_proportional_entry_id;
2594 ELSE
2595 retcode := gcs_utility_pkg.g_ret_sts_success;
2596 END IF;
2597 gcs_cons_eng_run_dtls_pkg.update_entry_headers
2598 (p_run_detail_id => p_run_detail_id,
2599 p_entry_id => l_proportional_entry_id,
2600 p_stat_entry_id => l_stat_proportional_entry_id,
2601 p_pre_prop_entry_id => l_entry_id,
2602 p_pre_prop_stat_entry_id => l_stat_entry_id,
2603 p_request_error_code => retcode,
2604 p_bp_request_error_code => retcode
2605 );
2606 ELSE
2607 gcs_cons_eng_run_dtls_pkg.update_entry_headers
2608 (p_run_detail_id => p_run_detail_id,
2609 p_entry_id => l_entry_id,
2610 p_stat_entry_id => l_stat_entry_id,
2611 p_pre_prop_entry_id => l_proportional_entry_id,
2612 p_pre_prop_stat_entry_id => l_stat_proportional_entry_id,
2613 p_request_error_code => retcode,
2614 p_bp_request_error_code => retcode
2615 );
2616 END IF;' ||
2617 ---------------------------------------------------------------------------------------------
2618 -- Code inserted by Santosh Matam
2619 --Imapct of Intercompany Mappings
2620 ---------------------------------------------------------------------------------------------
2621 '
2622 -- Check the GCS_SYSTEM_OPTIONS.INTERCO_MAP_ENABLED_FLAG.
2623 SELECT NVL(interco_map_enabled_flag,''N'')
2624 INTO l_imap_enabled_flag
2625 FROM gcs_system_options;
2626 -- If enabled then update the above created entry to populate the correct intercompany values according to the line_item value
2627 IF l_imap_enabled_flag = ''Y''
2628 THEN
2629 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2630 THEN
2631 fnd_log.STRING (fnd_log.level_statement,
2632 g_pkg_name || ''.'' || l_api_name,
2633 '' UPDATE gcs_entry_lines gel
2634 SET gel.intercompany_id = ( SELECT intercompany_id
2635 FROM gcs_interco_map_dtls gimd
2636 WHERE gimd.line_item_id = gel.line_item_id
2637 )
2638 WHERE gel.entry_id IN( l_entry_id, l_stat_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id)
2639 AND EXISTS ( SELECT intercompany_id
2640 FROM gcs_interco_map_dtls gimd
2641 WHERE gimd.line_item_id = gel.line_item_id
2642 );'');
2643 END IF;
2644 UPDATE gcs_entry_lines gel
2645 SET gel.intercompany_id = ( SELECT intercompany_id
2646 FROM gcs_interco_map_dtls gimd
2647 WHERE gimd.line_item_id = gel.line_item_id
2648 )
2649 WHERE gel.entry_id IN( l_entry_id, l_stat_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id)
2650 AND EXISTS ( SELECT intercompany_id
2651 FROM gcs_interco_map_dtls gimd
2652 WHERE gimd.line_item_id = gel.line_item_id
2653 );
2654
2655 END IF;' ||
2656 ------------------------------------------------------------------------------------
2657 --End of the code by Santosh Matam
2658 ------------------------------------------------------------------------------------
2659 'IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
2660 THEN
2661 fnd_log.STRING (fnd_log.level_procedure,
2662 g_pkg_name || ''.'' || l_api_name,
2663 gcs_utility_pkg.g_module_success
2664 || '' ''
2665 || l_api_name
2666 || ''()''
2667 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2668 );
2669 END IF;
2670 EXCEPTION
2671 WHEN no_suspense_template_error THEN
2672 retcode := gcs_utility_pkg.g_ret_sts_error;
2673 FND_MESSAGE.set_name(''GCS'', ''GCS_DP_NO_SUSPENSE_ERR'');
2674 errbuf := fnd_message.get;
2675
2676 DELETE FROM gcs_entry_headers
2677 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
2678 l_proportional_entry_id, l_stat_proportional_entry_id);
2679 DELETE FROM gcs_entry_lines
2680 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
2681 l_proportional_entry_id, l_stat_proportional_entry_id);
2682
2683 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
2684 THEN
2685 fnd_log.STRING (fnd_log.level_error,
2686 g_pkg_name || ''.'' || l_api_name,
2687 gcs_utility_pkg.g_module_failure
2688 || '' ''
2689 || errbuf
2690 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2691 );
2692 END IF;
2693 raise gcs_dp_proc_data_error;
2694 WHEN no_re_template_error THEN
2695 retcode := gcs_utility_pkg.g_ret_sts_error;
2696 FND_MESSAGE.set_name(''GCS'', ''GCS_DP_NO_RE_ERR'');
2697 errbuf := fnd_message.get;
2698
2699 DELETE FROM gcs_entry_headers
2700 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
2701 l_proportional_entry_id, l_stat_proportional_entry_id);
2702 DELETE FROM gcs_entry_lines
2703 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
2704 l_proportional_entry_id, l_stat_proportional_entry_id);
2705
2706 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
2707 THEN
2708 fnd_log.STRING (fnd_log.level_error,
2709 g_pkg_name || ''.'' || l_api_name,
2710 gcs_utility_pkg.g_module_failure
2711 || '' ''
2712 || errbuf
2713 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2714 );
2715 END IF;
2716 raise gcs_dp_proc_data_error;
2717 WHEN init_mapping_error THEN
2718 raise gcs_dp_proc_data_error;
2719 WHEN no_data_error THEN
2720 retcode := gcs_utility_pkg.g_ret_sts_warn;
2721 FND_MESSAGE.set_name(''GCS'', ''GCS_DP_NO_DATA_ERR'');
2722 errbuf := fnd_message.get;
2723 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
2724 fnd_log.STRING (fnd_log.level_error,
2725 g_pkg_name || ''.'' || l_api_name,
2726 gcs_utility_pkg.g_module_failure
2727 || '' ''
2728 || errbuf
2729 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2730 );
2731 END IF;
2732 gcs_cons_eng_run_dtls_pkg.update_entry_headers
2733 (p_run_detail_id => p_run_detail_id,
2734 p_entry_id => NULL,
2735 p_stat_entry_id => NULL,
2736 p_pre_prop_entry_id => NULL,
2737 p_pre_prop_stat_entry_id => NULL,
2738 p_request_error_code => ''NOT_APPLICABLE'',
2739 p_bp_request_error_code => ''NOT_APPLICABLE''
2740 );
2741 -- bug 5071794 fix: catch unexpected error and reraise it
2742 WHEN others THEN
2743 retcode := gcs_utility_pkg.g_ret_sts_error;
2744 errbuf := SQLERRM;
2745
2746 DELETE FROM gcs_entry_headers
2747 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
2748 l_proportional_entry_id, l_stat_proportional_entry_id);
2749 DELETE FROM gcs_entry_lines
2750 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
2751 l_proportional_entry_id, l_stat_proportional_entry_id);
2752
2753 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
2754 fnd_log.STRING (fnd_log.level_error,
2755 g_pkg_name || ''.'' || l_api_name,
2756 gcs_utility_pkg.g_module_failure
2757 || '' ''
2758 || errbuf
2759 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2760 );
2761 END IF;
2762 RAISE gcs_dp_proc_data_error;
2763 END PROCESS_DATA;
2764 ';
2765 curr_index := 1;
2766 body_len := LENGTH(l_proc_body);
2767 WHILE curr_index <= body_len LOOP
2768 lines := lines + 1;
2769 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
2770 curr_index := curr_index + 200;
2771 END LOOP;
2772 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 7');
2773 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
2774 l_proc_body := '
2775 PROCEDURE process_inc_data (
2776 p_source_currency_code IN VARCHAR2,
2777 p_target_cal_period_id IN NUMBER,
2778 p_currency_type_code IN VARCHAR2,
2779 p_hierarchy_id IN NUMBER,
2780 p_entity_id IN NUMBER,
2781 p_source_ledger_id IN NUMBER,
2782 p_balance_type_code IN VARCHAR2,
2783 p_owner_percentage IN NUMBER,
2784 p_run_name IN VARCHAR2,
2785 p_source_dataset_code IN NUMBER,
2786 x_entry_id OUT NOCOPY NUMBER,
2787 x_stat_entry_id OUT NOCOPY NUMBER,
2788 x_prop_entry_id OUT NOCOPY NUMBER,
2789 x_stat_prop_entry_id OUT NOCOPY NUMBER,
2790 errbuf OUT NOCOPY VARCHAR2,
2791 retcode OUT NOCOPY VARCHAR2
2792 )
2793 IS
2794 l_has_row_flag VARCHAR2 (1);
2795 l_has_stat_row_flag VARCHAR2 (1);
2796 l_temp_record gcs_templates_pkg.templaterecord;
2797 l_threshold NUMBER;
2798 l_threshold_currency VARCHAR2 (30);
2799 l_pre_entry_id NUMBER (15);
2800 l_pre_stat_entry_id NUMBER (15);
2801 l_precision NUMBER;
2802 l_stat_precision NUMBER;
2803 l_mapping_required VARCHAR2 (1);
2804 l_api_name VARCHAR2 (20) := ''PROCESS_INC_DATA'';
2805 l_imap_enabled_flag VARCHAR2 (1);
2806 BEGIN
2807 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
2808 THEN
2809 fnd_log.STRING (fnd_log.level_procedure,
2810 g_pkg_name || ''.'' || l_api_name,
2811 gcs_utility_pkg.g_module_enter
2812 || '' ''
2813 || l_api_name
2814 || ''()''
2815 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
2816 );
2817 END IF;
2818
2819 -- init local_to_master mappping
2820 l_mapping_required := init_local_to_master_maps (p_source_ledger_id => p_source_ledger_id,
2821 p_cal_period_id => p_target_cal_period_id,
2822 errbuf => errbuf,
2823 retcode => retcode,
2824 p_inc_mode_flag => ''Y'');
2825 IF (retcode <> gcs_utility_pkg.g_ret_sts_success)
2826 THEN
2827 RAISE init_mapping_error;
2828 END IF;
2829 SELECT gcs_entry_headers_s.NEXTVAL
2830 INTO x_entry_id
2831 FROM DUAL;
2832 SELECT gcs_entry_headers_s.NEXTVAL
2833 INTO x_stat_entry_id
2834 FROM DUAL;
2835 IF (l_mapping_required = ''N'') THEN
2836 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2837 THEN
2838 fnd_log.STRING (fnd_log.level_statement,
2839 g_pkg_name || ''.'' || l_api_name,
2840 ''' ||
2841 replace(l_inc_insert_statement, '''', '''''') || '''
2842 );
2843 END IF;
2844 ' || l_inc_insert_statement || '
2845 ELSE
2846 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
2847 THEN
2848 fnd_log.STRING (fnd_log.level_statement,
2849 g_pkg_name || ''.'' || l_api_name,
2850 '' g_insert_statement = ''
2851 || g_insert_statement
2852 );
2853 fnd_log.STRING (fnd_log.level_statement,
2854 g_pkg_name || ''.'' || l_api_name,
2855 ''EXECUTE IMMEDIATE g_insert_statement ''
2856 || g_nl
2857 || '' USING ''
2858 || x_stat_entry_id || '', '' || x_entry_id ||'', ''
2859 || fnd_global.user_id ||'', ''|| fnd_global.login_id ||'', ''|| p_source_ledger_id ||'', ''
2860 || g_ledger_ssc_attr_id ||'', ''
2861 || g_ledger_ssc_ver_id ||'', ''
2862 || p_currency_type_code ||'', ''|| p_hierarchy_id ||'', ''
2863 || p_entity_id ||'', ''|| p_balance_type_code ||'', ''
2864 || g_ln_item_vs_id ||'', ''
2865 || g_li_eat_attr_id ||'', ''
2866 || g_eatc_batc_attr_id ||'', ''
2867 || g_li_eat_ver_id ||'', ''
2868 || g_eatc_batc_ver_id ||'', ''
2869 || p_source_currency_code ||'', ''
2870 || p_source_dataset_code ||'', ''|| p_run_name ||'', ''|| p_entity_id ||'', ''
2871 || x_stat_entry_id || '', '' || x_entry_id
2872 );
2873 END IF;
2874 EXECUTE IMMEDIATE g_insert_statement
2875 USING x_stat_entry_id, x_entry_id,
2876 fnd_global.user_id, fnd_global.user_id, fnd_global.login_id, p_source_ledger_id,
2877 g_ledger_ssc_attr_id, g_ledger_ssc_ver_id, p_currency_type_code,
2878 p_entity_id, p_balance_type_code, g_ln_item_vs_id, g_li_eat_attr_id,
2879 g_eatc_batc_attr_id, g_li_eat_ver_id, g_eatc_batc_ver_id,
2880 p_source_currency_code, p_source_dataset_code, p_run_name,
2881 p_entity_id, x_stat_entry_id, x_entry_id;
2882 END IF;
2883
2884 BEGIN
2885 SELECT ''Y''
2886 INTO l_has_row_flag
2887 FROM DUAL
2888 WHERE EXISTS (SELECT 1 FROM gcs_entry_lines WHERE entry_id = x_entry_id);
2889 EXCEPTION
2890 WHEN NO_DATA_FOUND THEN
2891 l_has_row_flag := ''N'';
2892 END;
2893 BEGIN
2894 SELECT ''Y''
2895 INTO l_has_stat_row_flag
2896 FROM DUAL
2897 WHERE EXISTS (SELECT 1 FROM gcs_entry_lines WHERE entry_id = x_stat_entry_id);
2898 EXCEPTION
2899 WHEN NO_DATA_FOUND THEN
2900 l_has_stat_row_flag := ''N'';
2901 END;
2902 IF l_has_stat_row_flag = ''N'' AND l_has_row_flag = ''N''THEN
2903 RAISE no_data_error;
2904 END IF;
2905
2906 BEGIN
2907 SELECT decode (p_owner_percentage, 1, entry_id, pre_prop_entry_id),
2908 decode (p_owner_percentage, 1, stat_entry_id, pre_prop_stat_entry_id)
2909 INTO l_pre_entry_id, l_pre_stat_entry_id
2910 FROM gcs_cons_eng_run_dtls
2911 WHERE child_entity_id = p_entity_id
2912 AND category_code = ''DATAPREPARATION''
2913 AND run_name in (
2914 SELECT nvl(associated_run_name, run_name)
2915 FROM gcs_cons_eng_runs
2916 WHERE hierarchy_id = p_hierarchy_id
2917 AND cal_period_id = p_target_cal_period_id
2918 AND balance_type_code = p_balance_type_code
2919 AND most_recent_flag = ''Y''
2920 );
2921 UPDATE gcs_entry_lines gel
2922 SET (ptd_debit_balance_e, ptd_credit_balance_e, xtd_balance_e,
2923 ytd_debit_balance_e, ytd_credit_balance_e, ytd_balance_e) =
2924 (SELECT gel.ptd_debit_balance_e - gel_pre.ptd_debit_balance_e,
2925 gel.ptd_credit_balance_e - gel_pre.ptd_credit_balance_e,
2926 gel.xtd_balance_e - gel_pre.xtd_balance_e,
2927 gel.ytd_debit_balance_e - gel_pre.ytd_debit_balance_e,
2928 gel.ytd_credit_balance_e - gel_pre.ytd_credit_balance_e,
2929 gel.ytd_balance_e - gel_pre.ytd_balance_e
2930 FROM gcs_entry_lines gel_pre
2931 WHERE gel_pre.entry_id = decode(gel.entry_id,
2932 x_entry_id,
2933 l_pre_entry_id,
2934 l_pre_stat_entry_id) ' ||
2935 get_dimension_text('AND gel.', '= gel_pre.', 'Y') || '
2936 )
2937 WHERE gel.entry_id in (x_entry_id, x_stat_entry_id)
2938 AND EXISTS (SELECT 1
2939 FROM gcs_entry_lines gel_pre
2940 WHERE gel_pre.entry_id = decode(gel.entry_id,
2941 x_entry_id,
2942 l_pre_entry_id,
2943 l_pre_stat_entry_id) ' ||
2944 get_dimension_text('AND gel.', '= gel_pre.', 'Y') || '
2945 ) ;
2946 EXCEPTION
2947 WHEN no_data_found then
2948 null;
2949 END;
2950 IF l_has_stat_row_flag = ''Y'' THEN
2951 if p_owner_percentage <> 1 THEN
2952 SELECT gcs_entry_headers_s.NEXTVAL
2953 INTO x_stat_prop_entry_id
2954 FROM DUAL;
2955 END IF;
2956 ELSE
2957 x_stat_entry_id := NULL;
2958 END IF;
2959 IF l_has_row_flag = ''Y'' THEN
2960 if p_owner_percentage <> 1 THEN
2961 SELECT gcs_entry_headers_s.NEXTVAL
2962 INTO x_prop_entry_id
2963 FROM DUAL;
2964 END IF;
2965 ELSE
2966 x_entry_id := NULL;
2967 END IF;
2968 ';
2969 curr_index := 1;
2970 body_len := LENGTH(l_proc_body);
2971 WHILE curr_index <= body_len LOOP
2972 lines := lines + 1;
2973 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
2974 curr_index := curr_index + 200;
2975 END LOOP;
2976 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 8');
2977 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
2978 --
2979 -- create procedure init_local_to_master_maps
2980 --
2981 l_proc_body := '
2982 IF x_stat_entry_id IS NOT NULL
2983 THEN
2984 gcs_entry_pkg.create_entry_header
2985 (x_errbuf => errbuf,
2986 x_retcode => retcode,
2987 p_entry_id => x_stat_entry_id,
2988 p_hierarchy_id => p_hierarchy_id,
2989 p_entity_id => p_entity_id,
2990 p_start_cal_period_id => p_target_cal_period_id,
2991 p_end_cal_period_id => p_target_cal_period_id,
2992 p_entry_type_code => ''AUTOMATIC'',
2993 p_balance_type_code => p_balance_type_code,
2994 p_currency_code => ''STAT'',
2995 p_process_code => ''SINGLE_RUN_FOR_PERIOD'',
2996 p_category_code => ''DATAPREPARATION''
2997 );
2998 -- insert stat proportional entries
2999 IF (x_stat_prop_entry_id is not null)
3000 THEN
3001 gcs_entry_pkg.create_entry_header
3002 (x_errbuf => errbuf,
3003 x_retcode => retcode,
3004 p_entry_id => x_stat_prop_entry_id,
3005 p_hierarchy_id => p_hierarchy_id,
3006 p_entity_id => p_entity_id,
3007 p_start_cal_period_id => p_target_cal_period_id,
3008 p_end_cal_period_id => p_target_cal_period_id,
3009 p_entry_type_code => ''AUTOMATIC'',
3010 p_balance_type_code => p_balance_type_code,
3011 p_currency_code => ''STAT'',
3012 p_process_code => ''SINGLE_RUN_FOR_PERIOD'',
3013 p_category_code => ''DATAPREPARATION''
3014 );
3015 END IF;
3016 END IF;
3017 IF x_entry_id IS NOT NULL
3018 THEN
3019 gcs_entry_pkg.create_entry_header
3020 (x_errbuf => errbuf,
3021 x_retcode => retcode,
3022 p_entry_id => x_entry_id,
3023 p_hierarchy_id => p_hierarchy_id,
3024 p_entity_id => p_entity_id,
3025 p_start_cal_period_id => p_target_cal_period_id,
3026 p_end_cal_period_id => p_target_cal_period_id,
3027 p_entry_type_code => ''AUTOMATIC'',
3028 p_balance_type_code => p_balance_type_code,
3029 p_currency_code => p_source_currency_code,
3030 p_process_code => ''SINGLE_RUN_FOR_PERIOD'',
3031 p_category_code => ''DATAPREPARATION''
3032 );
3033 -- insert proportional entries
3034 IF (x_prop_entry_id is not null)
3035 THEN
3036 gcs_entry_pkg.create_entry_header
3037 (x_errbuf => errbuf,
3038 x_retcode => retcode,
3039 p_entry_id => x_prop_entry_id,
3040 p_hierarchy_id => p_hierarchy_id,
3041 p_entity_id => p_entity_id,
3042 p_start_cal_period_id => p_target_cal_period_id,
3043 p_end_cal_period_id => p_target_cal_period_id,
3044 p_entry_type_code => ''AUTOMATIC'',
3045 p_balance_type_code => p_balance_type_code,
3046 p_currency_code => p_source_currency_code,
3047 p_process_code => ''SINGLE_RUN_FOR_PERIOD'',
3048 p_category_code => ''DATAPREPARATION''
3049 );
3050 END IF;
3051 END IF;
3052 retcode := gcs_utility_pkg.g_ret_sts_success;
3053 -- Suspense accounts
3054 IF x_entry_id IS NOT NULL
3055 THEN
3056 -- check balance criteria
3057 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
3058 THEN
3059 fnd_log.STRING (fnd_log.level_statement,
3060 g_pkg_name || ''.'' || l_api_name,
3061 '' SELECT threshold_amount, threshold_currency
3062 INTO l_threshold, l_threshold_currency
3063 FROM gcs_hierarchies_b
3064 WHERE hierarchy_id = ''||p_hierarchy_id);
3065 END IF;
3066 SELECT threshold_amount, threshold_currency
3067 INTO l_threshold, l_threshold_currency
3068 FROM gcs_hierarchies_b
3069 WHERE hierarchy_id = p_hierarchy_id;
3070 BEGIN
3071 gcs_templates_pkg.get_dimension_template
3072 (p_hierarchy_id => p_hierarchy_id,
3073 p_template_code => ''SUSPENSE'',
3074 p_balance_type_code => p_balance_type_code,
3075 p_template_record => l_temp_record
3076 );
3077 EXCEPTION
3078 WHEN OTHERS
3079 THEN
3080 RAISE no_suspense_template_error;
3081 END;
3082 gcs_templates_dynamic_pkg.balance (p_entry_id => x_entry_id,
3083 p_template => l_temp_record,
3084 p_bal_type_code => p_balance_type_code,
3085 p_hierarchy_id => p_hierarchy_id,
3086 p_entity_id => p_entity_id,
3087 p_threshold => l_threshold,
3088 p_threshold_currency_code => l_threshold_currency
3089 );
3090 SELECT DECODE(SUSPENSE_EXCEEDED_FLAG, ''Y'', ''WARNING'', gcs_utility_pkg.g_ret_sts_success)
3091 INTO retcode
3092 FROM gcs_entry_headers
3093 WHERE entry_id = x_entry_id;
3094 END IF;
3095 IF (x_prop_entry_id IS NOT NULL or x_stat_prop_entry_id IS NOT NULL)
3096 THEN
3097 SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
3098 NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
3099 INTO l_precision, l_stat_precision
3100 FROM fnd_currencies fc_1, fnd_currencies fc_stat
3101 WHERE fc_1.currency_code = p_source_currency_code
3102 AND fc_stat.currency_code = ''STAT'';
3103 INSERT INTO gcs_entry_lines
3104 (entry_id,' || l_dims || '
3105 ptd_debit_balance_e,
3106 ptd_credit_balance_e,
3107 ytd_debit_balance_e,
3108 ytd_credit_balance_e,
3109 xtd_balance_e,
3110 ytd_balance_e,
3111 creation_date, created_by, last_update_date,
3112 last_updated_by, last_update_login
3113 )
3114 SELECT decode(entry_id, x_entry_id, x_prop_entry_id, x_stat_prop_entry_id),
3115 ' || l_dims || '
3116 ROUND(ptd_debit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
3117 ROUND(ptd_credit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
3118 ROUND(ytd_debit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
3119 ROUND(ytd_credit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
3120 ROUND(ptd_debit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision)
3121 - ROUND(ptd_credit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
3122 ROUND(ytd_debit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision)
3123 - ROUND(ytd_credit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
3124 creation_date, created_by, last_update_date,
3125 last_updated_by, last_update_login
3126 FROM gcs_entry_lines
3127 WHERE entry_id in ( x_entry_id, x_stat_entry_id);
3128 IF x_prop_entry_id IS NOT NULL THEN
3129 gcs_templates_dynamic_pkg.balance (p_entry_id => x_prop_entry_id,
3130 p_template => l_temp_record,
3131 p_bal_type_code => p_balance_type_code,
3132 p_hierarchy_id => p_hierarchy_id,
3133 p_entity_id => p_entity_id,
3134 p_threshold => l_threshold,
3135 p_threshold_currency_code => l_threshold_currency
3136 );
3137
3138 SELECT decode(SUSPENSE_EXCEEDED_FLAG, ''Y'', ''WARNING'', gcs_utility_pkg.g_ret_sts_success)
3139 INTO retcode
3140 FROM gcs_entry_headers
3141 WHERE entry_id = x_prop_entry_id;
3142 ELSE
3143 retcode := gcs_utility_pkg.g_ret_sts_success;
3144 END IF;
3145 END IF;
3146 ';
3147 curr_index := 1;
3148 body_len := LENGTH(l_proc_body);
3149 WHILE curr_index <= body_len LOOP
3150 lines := lines + 1;
3151 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
3152 curr_index := curr_index + 200;
3153 END LOOP;
3154 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 9');
3155 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
3156 ---------------------------------------------------------------------------------------------
3157 -- Code inserted by Santosh Matam
3158 --Imapct of Intercompany Mappings
3159 ---------------------------------------------------------------------------------------------
3160 l_proc_body := '
3161 -- Check the GCS_SYSTEM_OPTIONS.INTERCO_MAP_ENABLED_FLAG.
3162 SELECT NVL(interco_map_enabled_flag,''N'')
3163 INTO l_imap_enabled_flag
3164 FROM gcs_system_options;
3165 -- If enabled then update the above created entry to populate the correct intercompany values according to the line_item value
3166 IF l_imap_enabled_flag = ''Y''
3167 THEN
3168 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
3169 THEN
3170 fnd_log.STRING (fnd_log.level_statement,
3171 g_pkg_name || ''.'' || l_api_name,
3172 '' UPDATE gcs_entry_lines gel
3173 SET gel.intercompany_id = ( SELECT intercompany_id
3174 FROM gcs_interco_map_dtls gimd
3175 WHERE gimd.line_item_id = gel.line_item_id
3176 )
3177 WHERE gel.entry_id IN( x_entry_id, x_stat_entry_id, x_prop_entry_id, x_stat_prop_entry_id)
3178 AND EXISTS ( SELECT intercompany_id
3179 FROM gcs_interco_map_dtls gimd
3180 WHERE gimd.line_item_id = gel.line_item_id
3181 );'');
3182 END IF;
3183 UPDATE gcs_entry_lines gel
3184 SET gel.intercompany_id = ( SELECT intercompany_id
3185 FROM gcs_interco_map_dtls gimd
3186 WHERE gimd.line_item_id = gel.line_item_id
3187 )
3188 WHERE gel.entry_id IN( x_entry_id, x_stat_entry_id, x_prop_entry_id, x_stat_prop_entry_id)
3189 AND EXISTS ( SELECT intercompany_id
3190 FROM gcs_interco_map_dtls gimd
3191 WHERE gimd.line_item_id = gel.line_item_id
3192 );
3193
3194 END IF;' ||
3195 ------------------------------------------------------------------------------------
3196 --End of the code by Santosh Matam
3197 ------------------------------------------------------------------------------------
3198 '
3199 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
3200 THEN
3201 fnd_log.STRING (fnd_log.level_procedure,
3202 g_pkg_name || ''.'' || l_api_name,
3203 gcs_utility_pkg.g_module_success
3204 || '' ''
3205 || l_api_name
3206 || ''()''
3207 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
3208 );
3209 END IF;
3210 EXCEPTION
3211 WHEN no_suspense_template_error THEN
3212 retcode := gcs_utility_pkg.g_ret_sts_error;
3213 FND_MESSAGE.set_name(''GCS'', ''GCS_DP_NO_SUSPENSE_ERR'');
3214 errbuf := fnd_message.get;
3215 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
3216 THEN
3217 fnd_log.STRING (fnd_log.level_error,
3218 g_pkg_name || ''.'' || l_api_name,
3219 gcs_utility_pkg.g_module_failure
3220 || '' ''
3221 || errbuf
3222 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
3223 );
3224 END IF;
3225 raise gcs_dp_proc_data_error;
3226 WHEN no_re_template_error THEN
3227 retcode := gcs_utility_pkg.g_ret_sts_error;
3228 FND_MESSAGE.set_name(''GCS'', ''GCS_DP_NO_RE_ERR'');
3229 errbuf := fnd_message.get;
3230 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
3231 THEN
3232 fnd_log.STRING (fnd_log.level_error,
3233 g_pkg_name || ''.'' || l_api_name,
3234 gcs_utility_pkg.g_module_failure
3235 || '' ''
3236 || errbuf
3237 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
3238 );
3239 END IF;
3240 raise gcs_dp_proc_data_error;
3241 WHEN init_mapping_error THEN
3242 raise gcs_dp_proc_data_error;
3243 WHEN no_data_error THEN
3244 retcode := gcs_utility_pkg.g_ret_sts_warn;
3245 FND_MESSAGE.set_name(''GCS'', ''GCS_DP_NO_DATA_ERR'');
3246 errbuf := fnd_message.get;
3247 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
3248 fnd_log.STRING (fnd_log.level_error,
3249 g_pkg_name || ''.'' || l_api_name,
3250 gcs_utility_pkg.g_module_failure
3251 || '' ''
3252 || errbuf
3253 || TO_CHAR (SYSDATE, ''DD-MON-YYYY HH:MI:SS'')
3254 );
3255 END IF;
3256 raise gcs_dp_proc_data_error;
3257 END process_inc_data;
3258 END GCS_DP_DYNAMIC_PKG;
3259 ';
3260 curr_index := 1;
3261 body_len := LENGTH(l_proc_body);
3262 WHILE curr_index <= body_len LOOP
3263 lines := lines + 1;
3264 ad_ddl.build_statement(SUBSTR(l_proc_body, curr_index, 200), lines);
3265 curr_index := curr_index + 200;
3266 END LOOP;
3267 fnd_file.put_line(fnd_file.log, '@ad_ddl.build_statement --- 10');
3268 fnd_file.put_line(fnd_file.log, 'Body Length :=' || body_len);
3269 fnd_file.put_line(fnd_file.log,
3270 'GCS_DATA_PREP_PKG::create_process -- Before ad_ddl.create_plsql_object API Call');
3271 ad_ddl.create_plsql_object(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
3272 'GCS',
3273 'GCS_DP_DYNAMIC_PKG',
3274 1,
3275 lines,
3276 'FALSE',
3277 err);
3278 fnd_file.put_line(fnd_file.log,
3279 'GCS_DATA_PREP_PKG::create_process -- After ad_ddl.create_plsql_object API Call');
3280 fnd_file.put_line(fnd_file.log,
3281 'GCS_DATA_PREP_PKG::create_process -- End');
3282 fnd_file.put_line(fnd_file.log,
3283 '=========================================');
3284 EXCEPTION
3285 -- no required dimension found
3286 -- this shouldn't happen because we know for sure at least line_item is required
3287 WHEN NO_DATA_FOUND THEN
3288 ROLLBACK TO create_process_start;
3289 fnd_message.set_name('GCS', 'GCS_DP_NODIM_ERR');
3290 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
3291 fnd_log.STRING(fnd_log.level_error,
3292 g_pkg_name || '.' || l_api_name,
3293 gcs_utility_pkg.g_module_failure || ' ' ||
3294 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3295 END IF;
3296 fnd_file.put_line(fnd_file.log,
3297 'GCS_DATA_PREP_PKG::create_process -- NO_DATA_FOUND Exception');
3298 fnd_file.put_line(fnd_file.log,
3299 'GCS_DATA_PREP_PKG::create_process -- End');
3300 fnd_file.put_line(fnd_file.log,
3301 '=========================================');
3302 WHEN OTHERS THEN
3303 ROLLBACK TO create_process_start;
3304 x_errbuf := SQLERRM;
3305 fnd_message.set_name('GCS', 'GCS_DP_UNEXP_ERR');
3306 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
3307 fnd_log.STRING(fnd_log.level_error,
3308 g_pkg_name || '.' || l_api_name,
3309 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf || ' ' ||
3310 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3311 END IF;
3312 fnd_file.put_line(fnd_file.log,
3313 'GCS_DATA_PREP_PKG::create_process -- OTHERS Exception := ' ||
3314 x_errbuf);
3315 fnd_file.put_line(fnd_file.log,
3316 'GCS_DATA_PREP_PKG::create_process -- End');
3317 fnd_file.put_line(fnd_file.log,
3318 '=========================================');
3319 END create_process;
3320 ---------------------------------------------------------------------------
3321 --
3322 -- Procedure
3323 -- gcs_incremental_data_prep
3324 -- Purpose
3325 -- This procedure will be called via the SRS submission performed by the Consolidation Engine. It will then call the appropriate routines to complete the data preparation process.
3326 -- Arguments
3327 -- x_errbuf Error Buffer used for SRS
3328 -- x_retcode Return Code used for SRS
3329 -- p_ledger_id Ledger Identifier
3330 -- p_currency_code Currency Code
3331 -- p_target_cal_period_id Target Calendar Period Identifier
3332 -- p_dataset_code Dataset Code
3333 -- Notes
3334 --
3335 PROCEDURE gcs_incremental_data_prep(x_errbuf OUT NOCOPY VARCHAR2,
3336 x_retcode OUT NOCOPY VARCHAR2,
3337 x_entry_id OUT NOCOPY NUMBER,
3338 x_stat_entry_id OUT NOCOPY NUMBER,
3339 x_prop_entry_id OUT NOCOPY NUMBER,
3340 x_stat_prop_entry_id OUT NOCOPY NUMBER,
3341 p_source_cal_period_id IN NUMBER,
3342 p_balance_type_code IN VARCHAR2,
3343 p_ledger_id IN NUMBER,
3344 p_currency_code IN VARCHAR2,
3345 p_source_dataset_code IN NUMBER,
3346 p_run_name IN VARCHAR2,
3347 p_cons_relationship_id IN NUMBER,
3348 p_currency_type_code IN VARCHAR2) IS
3349 cursor c_cons_rel_data IS
3350 SELECT gcr.child_entity_id,
3351 gcr.hierarchy_id,
3352 nvl(ownership_percent, 100) * 0.01 ownership_percent
3353 FROM gcs_cons_relationships gcr, gcs_treatments_b gtb
3354 WHERE cons_relationship_id = p_cons_relationship_id
3355 AND gcr.treatment_id = gtb.treatment_id(+)
3356 AND gtb.consolidation_type_code(+) = 'PARTIAL';
3357 l_target_cal_period_id NUMBER;
3358 l_hierarchy_id NUMBER(15);
3359 l_entity_id NUMBER;
3360 l_owner_percentage NUMBER;
3361 l_api_name VARCHAR2(30) := 'GCS_INCREMENTAL_DATA_PREP';
3362 BEGIN
3363 SAVEPOINT inc_data_prep_start;
3364 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
3365 fnd_log.STRING(fnd_log.level_procedure,
3366 g_pkg_name || '.' || l_api_name,
3367 gcs_utility_pkg.g_module_enter ||
3368 ' p_source_cal_period_id = ' || p_source_cal_period_id ||
3369 ' p_balance_type_code = ' || p_balance_type_code ||
3370 ' p_ledger_id = ' || p_ledger_id ||
3371 ' p_currency_code = ' || p_currency_code ||
3372 ' p_source_dataset_code = ' || p_source_dataset_code ||
3373 ' p_run_name = ' || p_run_name ||
3374 ' p_cons_relationship_id = ' || p_cons_relationship_id ||
3375 ' p_currency_type_code = ' || p_currency_type_code || ' ' ||
3376 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3377 END IF;
3378 l_target_cal_period_id := p_source_cal_period_id;
3379 open c_cons_rel_data;
3380 fetch c_cons_rel_data
3381 INTO l_entity_id, l_hierarchy_id, l_owner_percentage;
3382 close c_cons_rel_data;
3383 gcs_dp_dynamic_pkg.process_inc_data(p_source_currency_code => p_currency_code,
3384 p_target_cal_period_id => l_target_cal_period_id,
3385 p_currency_type_code => p_currency_type_code,
3386 p_hierarchy_id => l_hierarchy_id,
3387 p_entity_id => l_entity_id,
3388 p_source_ledger_id => p_ledger_id,
3389 p_balance_type_code => p_balance_type_code,
3390 p_owner_percentage => l_owner_percentage,
3391 p_run_name => p_run_name,
3392 p_source_dataset_code => p_source_dataset_code,
3393 x_entry_id => x_entry_id,
3394 x_stat_entry_id => x_stat_entry_id,
3395 x_prop_entry_id => x_prop_entry_id,
3396 x_stat_prop_entry_id => x_stat_prop_entry_id,
3397 errbuf => x_errbuf,
3398 retcode => x_retcode);
3399 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
3400 fnd_log.STRING(fnd_log.level_procedure,
3401 g_pkg_name || '.' || l_api_name,
3402 gcs_utility_pkg.g_module_success || ' ' || l_api_name ||
3403 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3404 END IF;
3405 EXCEPTION
3406 WHEN OTHERS THEN
3407 ROLLBACK TO inc_data_prep_start;
3408 x_errbuf := SQLERRM;
3409 x_retcode := gcs_utility_pkg.g_ret_sts_unexp_error;
3410 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
3411 fnd_log.STRING(fnd_log.level_error,
3412 g_pkg_name || '.' || l_api_name,
3413 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf ||
3414 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3415 END IF;
3416 END gcs_incremental_data_prep;
3417 END gcs_data_prep_pkg;