[Home] [Help]
PACKAGE BODY: APPS.GCS_DP_DYNAMIC_PKG
Source
1 PACKAGE BODY GCS_DP_DYNAMIC_PKG AS
2 --
3 -- PRIVATE GLOBAL VARIABLES
4 --
5 -- The API name
6 g_pkg_name VARCHAR2 (30) := 'gcs.plsql.GCS_DP_DYNAMIC_PKG';
7 -- A newline character. Included for convenience when writing long strings.
8 g_nl CONSTANT VARCHAR2 (1) := '
9 ';
10 g_insert_statement VARCHAR2(32000);
11 g_ln_item_vs_id NUMBER;
12 g_li_eat_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info ('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').attribute_id;
13 g_li_eat_ver_id NUMBER := gcs_utility_pkg.g_dimension_attr_info ('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').version_id;
14
15 g_eatc_batc_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info ('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').attribute_id;
16 g_eatc_batc_ver_id NUMBER := gcs_utility_pkg.g_dimension_attr_info ('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').version_id;
17 g_li_vs_id NUMBER := gcs_utility_pkg.g_gcs_dimension_info ('LINE_ITEM_ID').associated_value_set_id;
18 g_ledger_ssc_attr_id NUMBER := gcs_utility_pkg.g_dimension_attr_info ('LEDGER_ID-SOURCE_SYSTEM_CODE').attribute_id;
19 g_ledger_ssc_ver_id NUMBER := gcs_utility_pkg.g_dimension_attr_info ('LEDGER_ID-SOURCE_SYSTEM_CODE').version_id;
20
21 no_re_template_error EXCEPTION;
22 no_suspense_template_error EXCEPTION;
23 init_mapping_error EXCEPTION;
24 no_data_error EXCEPTION;
25 --
26 -- Private Procedures
27 --
28 FUNCTION init_local_to_master_maps (
29 p_source_ledger_id IN NUMBER,
30 p_cal_period_id IN NUMBER,
31 errbuf OUT NOCOPY VARCHAR2,
32 retcode OUT NOCOPY VARCHAR2,
33 p_inc_mode_flag IN VARCHAR2 DEFAULT NULL
34 ) RETURN VARCHAR2
35 IS
36 l_source_global_vs_combo VARCHAR2 (30);
37 l_index_column_name VARCHAR2 (30);
38 l_source_value_set_id NUMBER;
39 l_hierarchy_obj_def_id NUMBER (9);
40 l_err_code NUMBER;
41 l_err_msg NUMBER;
42 l_mapping_required VARCHAR2(1) := 'N';
43 l_cctr_map_required BOOLEAN := FALSE;
44 l_from_text VARCHAR2(1000);
45 l_where_text VARCHAR2(10000);
46 l_group_text VARCHAR2(1000);
47 global_vs_id_error EXCEPTION;
48 gcs_dp_no_hier_obj_def_id EXCEPTION;
49 l_api_name VARCHAR2(30) := 'INIT_LOCAL_TO_MASTER_MAPS';
50 l_cal_attribute_id NUMBER;
51 l_cal_version_id NUMBER;
52 BEGIN
53 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
54 THEN
55 fnd_log.STRING (fnd_log.level_procedure,
56 g_pkg_name || '.' || l_api_name,
57 gcs_utility_pkg.g_module_enter
58 || ' p_source_ledger_id = ' || p_source_ledger_id
59 || ' '
60 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
61 );
62 END IF;
63 g_ln_item_vs_id := gcs_utility_pkg.g_gcs_dimension_info ('LINE_ITEM_ID').associated_value_set_id;
64 l_cal_attribute_id := gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id;
65 l_cal_version_id := gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
66 BEGIN
67 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
68 THEN
69 fnd_log.STRING (fnd_log.level_statement,
70 g_pkg_name || '.' || l_api_name,
71 ' SELECT fla.dim_attribute_numeric_member
72 INTO l_source_global_vs_combo
73 FROM fem_ledgers_attr fla,
74 fem_dim_attributes_b fdab,
75 fem_dim_attr_versions_b fdavb
76 WHERE fla.ledger_id = ' || p_source_ledger_id || '
77 AND fla.attribute_id = fdab.attribute_id
78 AND fdab.attribute_varchar_label = ''GLOBAL_VS_COMBO''
79 AND fla.version_id = fdavb.version_id
80 AND fdavb.attribute_id = fla.attribute_id
81 AND fdavb.default_version_flag = ''Y'' ');
82 END IF;
83 SELECT fla.dim_attribute_numeric_member
84 INTO l_source_global_vs_combo
85 FROM fem_ledgers_attr fla,
86 fem_dim_attributes_b fdab,
87 fem_dim_attr_versions_b fdavb
88 WHERE fla.ledger_id = p_source_ledger_id
89 AND fla.attribute_id = fdab.attribute_id
90 AND fdab.attribute_varchar_label = 'GLOBAL_VS_COMBO'
91 AND fla.version_id = fdavb.version_id
92 AND fdavb.attribute_id = fla.attribute_id
93 AND fdavb.default_version_flag = 'Y';
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 RAISE global_vs_id_error;
97 END;
98 g_insert_statement := '
99 INSERT INTO gcs_entry_lines_gt
100 (entry_id, cal_period_id,
101 ptd_debit_balance_e,
102 ptd_credit_balance_e,
103 ytd_debit_balance_e,
104 ytd_credit_balance_e,
105 xtd_balance_e,
106 ytd_balance_e)
107 '||'
108 SELECT decode(fb.currency_code,
109 ''STAT'',
110 :l_stat_entry_id,
111 :l_entry_id), fb.cal_period_id, ';
112 l_group_text := ' GROUP BY ';
113 l_index_column_name := gcs_utility_pkg.g_gcs_dimension_info.FIRST;
114 WHILE (l_index_column_name <= gcs_utility_pkg.g_gcs_dimension_info.LAST )
115 LOOP
116 IF ( (gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).associated_value_set_id IS NOT NULL)
117 AND (l_index_column_name <> 'ENTITY_ID')
118 AND (gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).required_for_gcs = 'Y')
119 )
120 THEN
121 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
122 THEN
123 fnd_log.STRING (fnd_log.level_statement,
124 g_pkg_name || '.' || l_api_name,
125 ' SELECT value_set_id
126 INTO l_source_value_set_id
127 FROM fem_global_vs_combo_defs
128 WHERE global_vs_combo_id = '||l_source_global_vs_combo||'
129 AND dimension_id = '||gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id);
130 END IF;
131 SELECT value_set_id
132 INTO l_source_value_set_id
133 FROM fem_global_vs_combo_defs
134 WHERE global_vs_combo_id = l_source_global_vs_combo
135 AND dimension_id = gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id;
136
137 IF ( (l_source_value_set_id IS NULL)
138 OR (l_source_value_set_id = gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).associated_value_set_id)
139 )
140 THEN
141 g_insert_statement := g_insert_statement || 'fb.' || l_index_column_name || ', ';
142 GOTO next_loop;
143 ELSE
144 gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).detail_value_set_id := l_source_value_set_id;
145 l_mapping_required := 'Y';
146 END IF;
147
148 BEGIN
149 SELECT fod.object_definition_id
150 INTO l_hierarchy_obj_def_id
151 FROM fem_xdim_dimensions fxd,
152 fem_object_definition_b fod,
153 fem_cal_periods_attr fcpa
154 WHERE fxd.dimension_id = gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id
155 AND fxd.default_mvs_hierarchy_obj_id = fod.object_id
156 AND fcpa.cal_period_id = p_cal_period_id
157 AND fcpa.attribute_id = l_cal_attribute_id
158 AND fcpa.version_id = l_cal_version_id
159 AND fcpa.date_assign_value BETWEEN fod.effective_start_date AND fod.effective_end_date;
160 EXCEPTION
161 WHEN NO_DATA_FOUND THEN
162 raise gcs_dp_no_hier_obj_def_id;
163 END;
164
165 IF (l_index_column_name = 'NATURAL_ACCOUNT_ID')
166 THEN
167 g_insert_statement := g_insert_statement
168 || 'fnah.parent_id, ';
169 l_from_text := l_from_text || ', fem_nat_accts_hier fnah ';
170 l_where_text := l_where_text
171 || ' AND fnah.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id
172 || ' AND fnah.parent_depth_num = fnah.child_depth_num - 1
173 AND fnah.child_value_set_id = ' || l_source_value_set_id || '
174 AND fnah.child_id = fb.NATURAL_ACCOUNT_ID
175 AND fnah.parent_value_set_id = '
176 || gcs_utility_pkg.g_gcs_dimension_info ('NATURAL_ACCOUNT_ID').associated_value_set_id;
177 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fnah.parent_id ');
178 ELSIF (l_index_column_name = 'COMPANY_COST_CENTER_ORG_ID')
179 THEN
180 l_cctr_map_required := TRUE;
181 g_insert_statement := g_insert_statement
182 || 'fcoh.parent_id, ';
183 l_from_text := l_from_text || ', fem_cctr_orgs_hier fcoh ';
184 l_where_text := l_where_text
185 || ' AND fcoh.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id
186 || ' AND fcoh.parent_depth_num = fcoh.child_depth_num - 1
187 AND fcoh.child_value_set_id = ' || l_source_value_set_id || '
188 AND fcoh.child_id = fb.COMPANY_COST_CENTER_ORG_ID
189 AND fcoh.parent_value_set_id = '
190 || gcs_utility_pkg.g_gcs_dimension_info ('COMPANY_COST_CENTER_ORG_ID').associated_value_set_id;
191 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fcoh.parent_id ');
192 ELSIF (l_index_column_name = 'INTERCOMPANY_ID')
193 THEN
194 g_insert_statement := g_insert_statement
195 || 'fcoh_inter.parent_id, ';
196 l_from_text := l_from_text || ', fem_cctr_orgs_hier fcoh_inter ';
197 l_where_text := l_where_text
198 || ' AND fcoh_inter.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
199 AND fcoh_inter.parent_depth_num = fcoh_inter.child_depth_num - 1
200 AND fcoh_inter.child_value_set_id = ' || l_source_value_set_id || '
201 AND fcoh_inter.child_id = fb.INTERCOMPANY_ID
202 AND fcoh_inter.parent_value_set_id = '
203 || gcs_utility_pkg.g_gcs_dimension_info ('INTERCOMPANY_ID').associated_value_set_id;
204 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fcoh_inter.parent_id ');
205 ELSIF (l_index_column_name = 'LINE_ITEM_ID')
206 THEN
207 g_insert_statement := g_insert_statement
208 || 'flih.parent_id, ';
209 l_from_text := l_from_text || ', fem_ln_items_hier flih ';
210 l_where_text := l_where_text
211 || ' AND flih.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
212 AND flih.parent_depth_num = flih.child_depth_num - 1
213 AND flih.child_value_set_id = ' || l_source_value_set_id || '
214 AND flih.child_id = fb.LINE_ITEM_ID
215 AND flih.parent_value_set_id = '
216 || gcs_utility_pkg.g_gcs_dimension_info ('LINE_ITEM_ID').associated_value_set_id;
217 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'flih.parent_id ');
218 g_ln_item_vs_id := l_source_value_set_id;
219 ELSIF (l_index_column_name = 'PRODUCT_ID')
220 THEN
221 g_insert_statement := g_insert_statement
222 || 'fpdh.parent_id, ';
223 l_from_text := l_from_text || ', fem_products_hier fpdh ';
224 l_where_text := l_where_text
225 || ' AND fpdh.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
226 AND fpdh.parent_depth_num = fpdh.child_depth_num - 1
227 AND fpdh.child_value_set_id = ' || l_source_value_set_id || '
228 AND fpdh.child_id = fb.PRODUCT_ID
229 AND fpdh.parent_value_set_id = '
230 || gcs_utility_pkg.g_gcs_dimension_info ('PRODUCT_ID').associated_value_set_id;
231 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fpdh.parent_id ');
232 ELSIF (l_index_column_name = 'PROJECT_ID')
233 THEN
234 g_insert_statement := g_insert_statement
235 || 'fpjh.parent_id, ';
236 l_from_text := l_from_text || ', fem_projects_hier fpjh ';
237 l_where_text := l_where_text
238 || ' AND fpjh.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
239 AND fpjh.parent_depth_num = fpjh.child_depth_num - 1
240 AND fpjh.child_value_set_id = ' || l_source_value_set_id || '
241 AND fpjh.child_id = fb.PROJECT_ID
242 AND fpjh.parent_value_set_id = '
243 || gcs_utility_pkg.g_gcs_dimension_info ('PROJECT_ID').associated_value_set_id;
244 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fpjh.parent_id ');
245 ELSIF (l_index_column_name = 'CHANNEL_ID')
246 THEN
247 g_insert_statement := g_insert_statement
248 || 'fchh.parent_id, ';
249 l_from_text := l_from_text || ', fem_channels_hier fchh ';
250 l_where_text := l_where_text
251 || ' AND fchh.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
252 AND fchh.parent_depth_num = fchh.child_depth_num - 1
253 AND fchh.child_value_set_id = ' || l_source_value_set_id || '
254 AND fchh.child_id = fb.CHANNEL_ID
255 AND fchh.parent_value_set_id = '
256 || gcs_utility_pkg.g_gcs_dimension_info ('CHANNEL_ID').associated_value_set_id;
257 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fchh.parent_id ');
258 ELSIF (l_index_column_name = 'CUSTOMER_ID')
259 THEN
260 g_insert_statement := g_insert_statement
261 || 'fcuh.parent_id, ';
262 l_from_text := l_from_text || ', fem_customers_hier fcuh ';
263 l_where_text := l_where_text
264 || ' AND fcuh.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
265 AND fcuh.parent_depth_num = fcuh.child_depth_num - 1
266 AND fcuh.child_value_set_id = ' || l_source_value_set_id || '
267 AND fcuh.child_id = fb.CUSTOMER_ID
268 AND fcuh.parent_value_set_id = '
269 || gcs_utility_pkg.g_gcs_dimension_info ('CUSTOMER_ID').associated_value_set_id;
270 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fcuh.parent_id ');
271 ELSIF (l_index_column_name = 'USER_DIM1_ID')
272 THEN
273 g_insert_statement := g_insert_statement
274 || 'fud1h.parent_id, ';
275 l_from_text := l_from_text || ', fem_user_dim1_hier fud1h ';
276 l_where_text := l_where_text
277 || ' AND fud1h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
278 AND fud1h.parent_depth_num = fud1h.child_depth_num - 1
279 AND fud1h.child_value_set_id = ' || l_source_value_set_id || '
280 AND fud1h.child_id = fb.USER_DIM1_ID
281 AND fud1h.parent_value_set_id = '
282 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM1_ID').associated_value_set_id;
283 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud1h.parent_id ');
284 ELSIF (l_index_column_name = 'USER_DIM2_ID')
285 THEN
286 g_insert_statement := g_insert_statement
287 || 'fud2h.parent_id, ';
288 l_from_text := l_from_text || ', fem_user_dim2_hier fud2h ';
289 l_where_text := l_where_text
290 || ' AND fud2h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
291 AND fud2h.parent_depth_num = fud2h.child_depth_num - 1
292 AND fud2h.child_value_set_id = ' || l_source_value_set_id || '
293 AND fud2h.child_id = fb.USER_DIM2_ID
294 AND fud2h.parent_value_set_id = '
295 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM2_ID').associated_value_set_id;
296 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud2h.parent_id ');
297 ELSIF (l_index_column_name = 'USER_DIM3_ID')
298 THEN
299 g_insert_statement := g_insert_statement
300 || 'fud3h.parent_id, ';
301 l_from_text := l_from_text || ', fem_user_dim3_hier fud3h ';
302 l_where_text := l_where_text
303 || ' AND fud3h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
304 AND fud3h.parent_depth_num = fud3h.child_depth_num - 1
305 AND fud3h.child_value_set_id = ' || l_source_value_set_id || '
306 AND fud3h.child_id = fb.USER_DIM3_ID
307 AND fud3h.parent_value_set_id = '
308 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM3_ID').associated_value_set_id;
309 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud3h.parent_id ');
310 ELSIF (l_index_column_name = 'USER_DIM4_ID')
311 THEN
312 g_insert_statement := g_insert_statement
313 || 'fud4h.parent_id, ';
314 l_from_text := l_from_text || ', fem_user_dim4_hier fud4h ';
315 l_where_text := l_where_text
316 || ' AND fud4h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
317 AND fud4h.parent_depth_num = fud4h.child_depth_num - 1
318 AND fud4h.child_value_set_id = ' || l_source_value_set_id || '
319 AND fud4h.child_id = fb.USER_DIM4_ID
320 AND fud4h.parent_value_set_id = '
321 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM4_ID').associated_value_set_id;
322 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud4h.parent_id ');
323 ELSIF (l_index_column_name = 'USER_DIM5_ID')
324 THEN
325 g_insert_statement := g_insert_statement
326 || 'fud5h.parent_id, ';
327 l_from_text := l_from_text || ', fem_user_dim5_hier fud5h ';
328 l_where_text := l_where_text
329 || ' AND fud5h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
330 AND fud5h.parent_depth_num = fud5h.child_depth_num - 1
331 AND fud5h.child_value_set_id = ' || l_source_value_set_id || '
332 AND fud5h.child_id = fb.USER_DIM5_ID
333 AND fud5h.parent_value_set_id = '
334 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM5_ID').associated_value_set_id;
335 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud5h.parent_id ');
336 ELSIF (l_index_column_name = 'USER_DIM6_ID')
337 THEN
338 g_insert_statement := g_insert_statement
339 || 'fud6h.parent_id, ';
340 l_from_text := l_from_text || ', fem_user_dim6_hier fud6h ';
341 l_where_text := l_where_text
342 || ' AND fud6h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
343 AND fud6h.parent_depth_num = fud6h.child_depth_num - 1
344 AND fud6h.child_value_set_id = ' || l_source_value_set_id || '
345 AND fud6h.child_id = fb.USER_DIM6_ID
346 AND fud6h.parent_value_set_id = '
347 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM6_ID').associated_value_set_id;
348 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud7h.parent_id ');
349 ELSIF (l_index_column_name = 'USER_DIM7_ID')
350 THEN
351 g_insert_statement := g_insert_statement
352 || 'fud7h.parent_id, ';
353 l_from_text := l_from_text || ', fem_user_dim7_hier fud7h ';
354 l_where_text := l_where_text
355 || ' AND fud7h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
356 AND fud7h.parent_depth_num = fud7h.child_depth_num - 1
357 AND fud7h.child_value_set_id = ' || l_source_value_set_id || '
358 AND fud7h.child_id = fb.USER_DIM7_ID
359 AND fud7h.parent_value_set_id = '
360 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM7_ID').associated_value_set_id;
361 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud7h.parent_id ');
362 ELSIF (l_index_column_name = 'USER_DIM8_ID')
363 THEN
364 g_insert_statement := g_insert_statement
365 || 'fud8h.parent_id, ';
366 l_from_text := l_from_text || ', fem_user_dim8_hier fud8h ';
367 l_where_text := l_where_text
368 || ' AND fud8h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
369 AND fud8h.parent_depth_num = fud8h.child_depth_num - 1
370 AND fud8h.child_value_set_id = ' || l_source_value_set_id || '
371 AND fud8h.child_id = fb.USER_DIM8_ID
372 AND fud8h.parent_value_set_id = '
373 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM8_ID').associated_value_set_id;
374 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud8h.parent_id ');
375 ELSIF (l_index_column_name = 'USER_DIM9_ID')
376 THEN
377 g_insert_statement := g_insert_statement
378 || 'fud9h.parent_id, ';
379 l_from_text := l_from_text || ', fem_user_dim9_hier fud9h ';
380 l_where_text := l_where_text
381 || ' AND fud9h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
382 AND fud9h.parent_depth_num = fud9h.child_depth_num - 1
383 AND fud9h.child_value_set_id = ' || l_source_value_set_id || '
384 AND fud9h.child_id = fb.USER_DIM9_ID
385 AND fud9h.parent_value_set_id = '
386 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM9_ID').associated_value_set_id;
387 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud9h.parent_id ');
388 ELSIF (l_index_column_name = 'USER_DIM10_ID')
389 THEN
390 g_insert_statement := g_insert_statement
391 || 'fud10h.parent_id, ';
392 l_from_text := l_from_text || ', fem_user_dim10_hier fud10h ';
393 l_where_text := l_where_text
394 || ' AND fud10h.hierarchy_obj_def_id = ' || l_hierarchy_obj_def_id || '
395 AND fud10h.parent_depth_num = fud10h.child_depth_num - 1
396 AND fud10h.child_value_set_id = ' || l_source_value_set_id || '
397 AND fud10h.child_id = fb.USER_DIM10_ID
398 AND fud10h.parent_value_set_id = '
399 || gcs_utility_pkg.g_gcs_dimension_info ('USER_DIM10_ID').associated_value_set_id;
400 l_group_text := replace(l_group_text, 'fb.'||l_index_column_name, 'fud10h.parent_id ');
401 END IF;
402 END IF;
403 <<next_loop>>
404 l_index_column_name :=gcs_utility_pkg.g_gcs_dimension_info.NEXT (l_index_column_name);
405 END LOOP;
406
407 IF (p_inc_mode_flag = 'Y') THEN
408 g_insert_statement := g_insert_statement || '
409 SUM(fb.ptd_debit_balance_e) PTD_DEBIT_BALANCE_E,
410 SUM(fb.ptd_credit_balance_e) PTD_CREDIT_BALANCE_E,
411 SUM(fb.ytd_debit_balance_e) YTD_DEBIT_BALANCE_E,
412 SUM(fb.ytd_credit_balance_e) YTD_CREDIT_BALANCE_E,
413 SUM(NVL(fb.xtd_balance_f, fb.xtd_balance_e)) XTD_BALANCE_E,
414 SUM(NVL(fb.ytd_balance_f, fb.ytd_balance_e)) YTD_BALANCE_E,
415 SYSDATE,
416 FND_GLOBAL.USER_ID,
417 SYSDATE,
418 FND_GLOBAL.USER_ID,
419 FND_GLOBAL.LOGIN_ID ';
420 l_from_text := '
421 FROM fem_balances fb,
422 fem_ledgers_attr fla,
423 gcs_entity_cctr_orgs geco,
424 gcs_cons_impact_analyses gcia,
425 gcs_data_sub_dtls gdsd '|| l_from_text;
426 l_where_text :=
427 '
428 WHERE fb.ledger_id = :p_source_ledger_id
429 AND fb.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
430 AND fla.ledger_id = fb.ledger_id
431 AND fla.attribute_id = gcs_utility_pkg.g_dimension_attr_info(''LEDGER_ID-SOURCE_SYSTEM_CODE'').attribute_id
432 AND fla.version_id = gcs_utility_pkg.g_dimension_attr_info(''LEDGER_ID-SOURCE_SYSTEM_CODE'').version_id
433 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
434 AND geco.entity_id = :p_entity_id
435 AND :p_balance_type_code = DECODE(fb.financial_elem_id, 140, ''ADB'', ''ACTUAL'')
436 AND ((fb.currency_type_code = ''TRANSLATED'' AND fb.currency_code in (''STAT'', :p_source_currency_code))
437 or (fb.currency_type_code = ''ENTERED''))
438 AND fb.currency_type_code = :p_currency_type_code
439 AND fb.dataset_code = :p_source_dataset_code
440 AND fb.last_updated_by_request_id = gdsd.associated_request_id
441 AND gcia.run_name = :p_run_name
442 AND gcia.child_entity_id = :p_entity_id
443 AND gcia.load_id = gdsd.load_id
444 '||l_where_text;
445 ELSE
446 g_insert_statement := g_insert_statement || '
447 fb.ptd_debit_balance_e,
448 fb.ptd_credit_balance_e,
449 DECODE (fb.cal_period_id,
450 :p_max_period, ytd_debit_balance_e, 0
451 ) ytd_debit_balance_e,
452 DECODE (fb.cal_period_id,
453 :p_max_period, ytd_credit_balance_e, 0
454 ) ytd_credit_balance_e,
455 NVL(fb.ptd_debit_balance_e,0) - NVL(fb.ptd_credit_balance_e,0),
456 DECODE (fb.cal_period_id,
457 :p_max_period, NVL(fb.ytd_debit_balance_e,0) - NVL (fb.ytd_credit_balance_e,0), 0
458 ) ytd_balance_e
459 ';
460 l_from_text := '
461 FROM fem_balances fb,
462 gcs_entity_cctr_orgs geco '|| l_from_text;
463 l_where_text := '
464 WHERE :source_cal_period_id = fb.cal_period_id
465 AND fb.ledger_id = :p_source_ledger_id
466 AND fb.source_system_code = :source_system_code
467 AND fb.currency_type_code = :p_currency_type_code
468 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
469 AND geco.entity_id = :p_entity_id
470 AND fb.dataset_code = :source_dataset_code '||l_where_text;
471 END IF;
472 if (l_cctr_map_required) then
473 l_where_text := replace(l_where_text,
474 'AND fb.company_cost_center_org_id = geco.company_cost_center_org_id',
475 'AND fcoh.parent_id = geco.company_cost_center_org_id');
476 end if;
477 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
478 THEN
479 fnd_log.STRING (fnd_log.level_statement,
480 g_pkg_name || '.' || l_api_name,
481 ' g_insert_statement = '|| g_insert_statement
482 );
483 fnd_log.STRING (fnd_log.level_statement,
484 g_pkg_name || '.' || l_api_name,
485 ' l_from_text = '|| l_from_text
486 );
487 fnd_log.STRING (fnd_log.level_statement,
488 g_pkg_name || '.' || l_api_name,
489 ' l_where_text = '|| l_where_text
490 );
491 END IF;
492 g_insert_statement := g_insert_statement
493 || l_from_text
494 || l_where_text;
495 retcode := gcs_utility_pkg.g_ret_sts_success;
496 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
497 THEN
498 fnd_log.STRING (fnd_log.level_procedure,
499 g_pkg_name || '.' || l_api_name,
500 gcs_utility_pkg.g_module_success
501 || ' Mapping Required : '
502 || l_mapping_required
503 || ' '
504 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
505 );
506 END IF;
507 RETURN l_mapping_required;
508 EXCEPTION
509 WHEN gcs_dp_no_hier_obj_def_id THEN
510 retcode := gcs_utility_pkg.g_ret_sts_error;
511 fnd_message.set_name('GCS', 'GCS_DP_NO_HIER_OBJ_DEF_ERR');
512 fnd_message.set_token('DIMENSION', l_index_column_name);
513 errbuf := fnd_message.get;
514 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
515 THEN
516 fnd_log.STRING (fnd_log.level_error,
517 g_pkg_name || '.' || l_api_name,
518 gcs_utility_pkg.g_module_failure
519 || ' '
520 || errbuf
521 || ' ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
522 );
523 END IF;
524 RETURN retcode;
525 WHEN global_vs_id_error THEN
526 retcode := gcs_utility_pkg.g_ret_sts_error;
527 FND_MESSAGE.set_name('GCS', 'GCS_DP_GLOBAL_VS_ERR');
528 errbuf := fnd_message.get;
529 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
530 THEN
531 fnd_log.STRING (fnd_log.level_error,
532 g_pkg_name || '.' || l_api_name,
533 gcs_utility_pkg.g_module_failure
534 || ' '
535 || errbuf
536 || ' ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
537 );
538 END IF;
539 RETURN retcode;
540 WHEN OTHERS THEN
541 retcode := gcs_utility_pkg.G_RET_STS_UNEXP_ERROR;
542 FND_MESSAGE.set_name('GCS', 'GCS_DP_UNEXP_ERR');
543 errbuf := fnd_message.get;
544 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
545 THEN
546 fnd_log.STRING (fnd_log.level_error,
547 g_pkg_name || '.' || l_api_name,
548 gcs_utility_pkg.g_module_failure
549 || ' '
550 || errbuf
551 || ' ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
552 );
553 END IF;
554 RETURN retcode;
555 END init_local_to_master_maps;
556
557 --
558 -- Public Procedures
559 --
560 PROCEDURE process_data (
561 p_source_currency_code IN VARCHAR2,
562 p_target_cal_period_id IN NUMBER,
563 p_max_period IN NUMBER,
564 p_currency_type_code IN VARCHAR2,
565 p_hierarchy_id IN NUMBER,
566 p_entity_id IN NUMBER,
567 p_source_ledger_id IN NUMBER,
568 p_year_end_values_match IN VARCHAR2,
569 p_cal_period_record IN gcs_utility_pkg.r_cal_period_info,
570 p_balance_type_code IN VARCHAR2,
571 p_owner_percentage IN NUMBER,
572 p_run_detail_id IN NUMBER,
573 p_source_dataset_code IN NUMBER,
574 errbuf OUT NOCOPY VARCHAR2,
575 retcode OUT NOCOPY VARCHAR2
576 )
577 IS
578 l_has_row_flag VARCHAR2 (1);
579 l_has_stat_row_flag VARCHAR2 (1);
580 l_first_ever_data_prepped VARCHAR2 (1);
581 l_temp_record gcs_templates_pkg.templaterecord;
582 l_threshold NUMBER;
583 l_threshold_currency VARCHAR2(15);
584 l_entry_id NUMBER (15) := NULL;
585 l_stat_entry_id NUMBER (15) := NULL;
586 l_proportional_entry_id NUMBER (15) := NULL;
587 l_stat_proportional_entry_id NUMBER (15):= NULL;
588 l_mapping_required VARCHAR2 (1);
589 l_precision NUMBER;
590 l_stat_precision NUMBER;
591 l_api_name VARCHAR2 (20) := 'PROCESS_DATA';
592 l_imap_enabled_flag VARCHAR2 (1);
593 l_source_system_code NUMBER;
594 l_periods_list DBMS_SQL.number_table;
595
596 -- Bug Fix: 5843592, Get the attribute id and version id of the CAL_PERIOD_END_DATE of calendar period
597
598 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
599 .attribute_id;
600 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
601 .version_id ;
602
603 -- Bugfix 6068527: Added account type attributes
604 l_line_item_type_attr NUMBER(15) :=
605 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').attribute_id;
606 l_line_item_type_version NUMBER(15) :=
607 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').version_id;
608 l_acct_type_attr NUMBER(15) :=
609 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').attribute_id;
610 l_acct_type_version NUMBER(15) :=
611 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').version_id;
612
613 BEGIN
614 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
615 THEN
616 fnd_log.STRING (fnd_log.level_procedure,
617 g_pkg_name || '.' || l_api_name,
618 gcs_utility_pkg.g_module_enter
619 || ' p_source_currency_code = ' || p_source_currency_code
620 || ', p_target_cal_period_id = ' || p_target_cal_period_id
621 || ', p_max_period = ' || p_max_period
622 || ', p_currency_type_code = ' || p_currency_type_code
623 || ', p_hierarchy_id = ' || p_hierarchy_id
624 || ', p_entity_id = ' || p_entity_id
625 || ', p_source_ledger_id = ' || p_source_ledger_id
626 || ', p_year_end_values_match = ' || p_year_end_values_match
627 || ', p_balance_type_code = ' || p_balance_type_code
628 || ', p_owner_percentage = ' || p_owner_percentage
629 || ', p_run_detail_id = ' || p_run_detail_id
630 || ', p_source_dataset_code = ' || p_source_dataset_code
631 || ' '
632 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
633 );
634 END IF;
635
636 -- init local_to_master mappping
637 l_mapping_required := init_local_to_master_maps (p_source_ledger_id => p_source_ledger_id,
638 p_cal_period_id => p_cal_period_record.cal_period_id,
639 errbuf => errbuf,
640 retcode => retcode);
641
642 IF (retcode <> gcs_utility_pkg.g_ret_sts_success)
643 THEN
644 RAISE init_mapping_error;
645 END IF;
646
647 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
648 THEN
649 fnd_log.STRING (fnd_log.level_statement,
650 g_pkg_name || '.' || l_api_name,
651 'Mapping required flag: ' || l_mapping_required);
652 END IF;
653
654 -- bug fix 5074999: raise no_data_error when source_system_code not found
655
656 -- Bugfix 5843592, Get the source_system_code of the correct entity, depending upon the calendar period
657
658 BEGIN
659 SELECT gea.source_system_code
660 INTO l_source_system_code
661 FROM gcs_entities_attr gea,
662 fem_cal_periods_attr fcpa
663 WHERE gea.entity_id = p_entity_id
664 AND gea.data_type_code = p_balance_type_code
665 AND fcpa.cal_period_id = p_target_cal_period_id
666 AND fcpa.attribute_id = l_period_end_date_attr
667 AND fcpa.version_id = l_period_end_date_version
668 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
669 AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
670
671
672 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
673 THEN
674 fnd_log.STRING (fnd_log.level_statement,
675 g_pkg_name || '.' || l_api_name,
676 'Source system code: ' || l_source_system_code);
677 END IF;
678 EXCEPTION
679 WHEN no_data_found THEN
680 RAISE no_data_error;
681 END;
682
683 -- bug fix 5074999: remove join to p_balance_type_code, which is redundant and incorrect
684 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
685 THEN
686 fnd_log.STRING (fnd_log.level_statement,
687 g_pkg_name || '.' || l_api_name,
688 'SELECT cpmgt.source_cal_period_id cal_period_id
689 BULK COLLECT INTO l_periods_list
690 FROM fem_data_locations fdl,
691 gcs_cal_period_maps_gt cpmgt
692 WHERE fdl.ledger_id = ' ||p_source_ledger_id||'
693 AND fdl.cal_period_id = cpmgt.source_cal_period_id
694 AND fdl.source_system_code = '||l_source_system_code||'
695 AND fdl.dataset_code = ' ||p_source_dataset_code||'
696 AND fdl.table_name = ''FEM_BALANCES''');
697 END IF;
698
699 SELECT cpmgt.source_cal_period_id cal_period_id
700 BULK COLLECT INTO l_periods_list
701 FROM fem_data_locations fdl,
702 gcs_cal_period_maps_gt cpmgt
703 WHERE fdl.ledger_id = p_source_ledger_id
704 AND fdl.cal_period_id = cpmgt.source_cal_period_id
705 AND fdl.source_system_code = l_source_system_code
706 AND fdl.dataset_code = p_source_dataset_code
707 AND fdl.table_name = 'FEM_BALANCES';
708
709 IF l_periods_list.FIRST IS NULL THEN
710 RAISE no_data_error;
711 END IF;
712
713 SELECT gcs_entry_headers_s.NEXTVAL
714 INTO l_entry_id
715 FROM DUAL;
716 SELECT gcs_entry_headers_s.NEXTVAL
717 INTO l_stat_entry_id
718 FROM DUAL;
719
720 -------------------------------------------
721 -- this is the mapping not required case --
722 -------------------------------------------
723 IF (l_mapping_required = 'N') THEN
724 IF (p_balance_type_code = 'ADB' AND p_currency_type_code = 'ENTERED') THEN
725 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
726
727 INSERT INTO gcs_entry_lines_gt
728 (entry_id, cal_period_id,
729 ptd_debit_balance_e,
730 ptd_credit_balance_e,
731 ytd_debit_balance_e,
732 ytd_credit_balance_e,
733 xtd_balance_e,
734 ytd_balance_e)
735 SELECT decode(fb.currency_code, 'STAT', l_stat_entry_id, l_entry_id), fb.cal_period_id,
736 fb.ptd_debit_balance_e,
737 fb.ptd_credit_balance_e,
738 DECODE (fb.cal_period_id,
739 p_max_period, ytd_debit_balance_e, 0
740 ) ytd_debit_balance_e,
741 DECODE (fb.cal_period_id,
742 p_max_period, ytd_credit_balance_e, 0
743 ) ytd_credit_balance_e,
744 NVL(fb.ptd_debit_balance_e,0) - NVL(fb.ptd_credit_balance_e,0),
745 DECODE (fb.cal_period_id,
746 p_max_period, NVL(fb.ytd_debit_balance_e,0) - NVL (fb.ytd_credit_balance_e,0), 0
747 ) ytd_balance_e
748
749 FROM fem_balances fb,
750 gcs_entity_cctr_orgs geco
751 WHERE l_periods_list(counter) = fb.cal_period_id
752 AND fb.source_system_code = l_source_system_code
753 AND fb.ledger_id = p_source_ledger_id
754 AND fb.currency_type_code = p_currency_type_code
755 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
756 AND geco.entity_id = p_entity_id
757 AND fb.dataset_code = p_source_dataset_code
758 AND fb.currency_code IN (p_source_currency_code, 'STAT')
759 AND fb.financial_elem_id = 140;
760 ELSIF (p_balance_type_code = 'ADB') THEN
761 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
762
763 INSERT INTO gcs_entry_lines_gt
764 (entry_id, cal_period_id,
765 ptd_debit_balance_e,
766 ptd_credit_balance_e,
767 ytd_debit_balance_e,
768 ytd_credit_balance_e,
769 xtd_balance_e,
770 ytd_balance_e)
771 SELECT decode(fb.currency_code, 'STAT', l_stat_entry_id, l_entry_id), fb.cal_period_id,
772 fb.ptd_debit_balance_e,
773 fb.ptd_credit_balance_e,
774 DECODE (fb.cal_period_id,
775 p_max_period, ytd_debit_balance_e, 0
776 ) ytd_debit_balance_e,
777 DECODE (fb.cal_period_id,
778 p_max_period, ytd_credit_balance_e, 0
779 ) ytd_credit_balance_e,
780 NVL(fb.ptd_debit_balance_e,0) - NVL(fb.ptd_credit_balance_e,0),
781 DECODE (fb.cal_period_id,
782 p_max_period, NVL(fb.ytd_debit_balance_e,0) - NVL (fb.ytd_credit_balance_e,0), 0
783 ) ytd_balance_e
784
785 FROM fem_balances fb,
786 gcs_entity_cctr_orgs geco
787 WHERE l_periods_list(counter) = fb.cal_period_id
788 AND fb.source_system_code = l_source_system_code
789 AND fb.ledger_id = p_source_ledger_id
790 AND fb.currency_type_code = p_currency_type_code
791 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
792 AND geco.entity_id = p_entity_id
793 AND fb.dataset_code = p_source_dataset_code
794 AND fb.financial_elem_id = 140
795 AND fb.currency_code = p_source_currency_code;
796 ELSIF (p_currency_type_code = 'ENTERED') THEN
797 --Bugfix 5232063: Do not assume Financial Element is populated
798 --Bugfix 5329620: Added l_curr_where_clause
799 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
800
801 INSERT INTO gcs_entry_lines_gt
802 (entry_id, cal_period_id,
803 ptd_debit_balance_e,
804 ptd_credit_balance_e,
805 ytd_debit_balance_e,
806 ytd_credit_balance_e,
807 xtd_balance_e,
808 ytd_balance_e)
809 SELECT decode(fb.currency_code, 'STAT', l_stat_entry_id, l_entry_id), fb.cal_period_id,
810 fb.ptd_debit_balance_e,
811 fb.ptd_credit_balance_e,
812 DECODE (fb.cal_period_id,
813 p_max_period, ytd_debit_balance_e, 0
814 ) ytd_debit_balance_e,
815 DECODE (fb.cal_period_id,
816 p_max_period, ytd_credit_balance_e, 0
817 ) ytd_credit_balance_e,
818 NVL(fb.ptd_debit_balance_e,0) - NVL(fb.ptd_credit_balance_e,0),
819 DECODE (fb.cal_period_id,
820 p_max_period, NVL(fb.ytd_debit_balance_e,0) - NVL (fb.ytd_credit_balance_e,0), 0
821 ) ytd_balance_e
822
823 FROM fem_balances fb,
824 gcs_entity_cctr_orgs geco
825 WHERE l_periods_list(counter) = fb.cal_period_id
826 AND fb.source_system_code = l_source_system_code
827 AND fb.ledger_id = p_source_ledger_id
828 AND fb.currency_type_code = p_currency_type_code
829 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
830 AND geco.entity_id = p_entity_id
831 AND fb.dataset_code = p_source_dataset_code
832 AND fb.currency_code = p_source_currency_code;
833 ELSE
834 --Bugfix 5232063: Do not assume Financial Element is populated
835 --Bugfix 5329620: Added l_curr_where_clause
836 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
837
838 INSERT INTO gcs_entry_lines_gt
839 (entry_id, cal_period_id,
840 ptd_debit_balance_e,
841 ptd_credit_balance_e,
842 ytd_debit_balance_e,
843 ytd_credit_balance_e,
844 xtd_balance_e,
845 ytd_balance_e)
846 SELECT decode(fb.currency_code, 'STAT', l_stat_entry_id, l_entry_id), fb.cal_period_id,
847 fb.ptd_debit_balance_e,
848 fb.ptd_credit_balance_e,
849 DECODE (fb.cal_period_id,
850 p_max_period, ytd_debit_balance_e, 0
851 ) ytd_debit_balance_e,
852 DECODE (fb.cal_period_id,
853 p_max_period, ytd_credit_balance_e, 0
854 ) ytd_credit_balance_e,
855 NVL(fb.ptd_debit_balance_e,0) - NVL(fb.ptd_credit_balance_e,0),
856 DECODE (fb.cal_period_id,
857 p_max_period, NVL(fb.ytd_debit_balance_e,0) - NVL (fb.ytd_credit_balance_e,0), 0
858 ) ytd_balance_e
859
860 FROM fem_balances fb,
861 gcs_entity_cctr_orgs geco
862 WHERE l_periods_list(counter) = fb.cal_period_id
863 AND fb.source_system_code = l_source_system_code
864 AND fb.ledger_id = p_source_ledger_id
865 AND fb.currency_type_code = p_currency_type_code
866 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
867 AND geco.entity_id = p_entity_id
868 AND fb.dataset_code = p_source_dataset_code
869 AND fb.currency_code = p_source_currency_code;
870 END IF; -- p_balance_type_code
871
872 ELSE---------------------------------------
873 -- this is the mapping required case --
874 ---------------------------------------
875 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
876 THEN
877 fnd_log.STRING (fnd_log.level_statement,
878 g_pkg_name || '.' || l_api_name,
879 ' g_insert_statement = '
880 || g_insert_statement
881 );
882 fnd_log.STRING (fnd_log.level_statement,
883 g_pkg_name || '.' || l_api_name,
884 ' FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST '
885 || g_nl
886 || ' EXECUTE IMMEDIATE g_insert_statement '
887 || g_nl
888 || ' USING '
889 || l_stat_entry_id || ', ' || l_entry_id ||', '
890 || p_max_period ||', '|| p_max_period ||', ' || p_max_period ||', '
891 || ' l_periods_list(counter), ' || p_source_ledger_id ||', '
892 || ' l_source_system_code, '
893 || p_currency_type_code ||', '
894 || p_entity_id ||', '|| p_balance_type_code ||', '
895 || p_source_currency_code ||', '
896 || ' p_source_dataset_code '
897 || ' p_source_currency_code '
898 );
899 END IF;
900
901 IF (p_balance_type_code = 'ADB' AND p_currency_type_code = 'ENTERED') THEN
902 g_insert_statement := g_insert_statement || '
903 AND fb.financial_elem_id = 140
904 AND fb.currency_code IN (:p_source_currency_code, ''STAT'') ';
905 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
906 EXECUTE IMMEDIATE g_insert_statement
907 USING l_stat_entry_id, l_entry_id,
908 p_max_period, p_max_period, p_max_period,
909 l_periods_list(counter), p_source_ledger_id,
910 l_source_system_code,
911 p_currency_type_code, p_entity_id,
912 p_source_dataset_code, p_source_currency_code;
913 ELSIF (p_balance_type_code = 'ADB') THEN
914 g_insert_statement := g_insert_statement || '
915 AND fb.financial_elem_id = 140
916 AND fb.currency_code = :p_source_currency_code ';
917 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
918 EXECUTE IMMEDIATE g_insert_statement
919 USING l_stat_entry_id, l_entry_id,
920 p_max_period, p_max_period, p_max_period,
921 l_periods_list(counter), p_source_ledger_id,
922 l_source_system_code,
923 p_currency_type_code, p_entity_id, p_source_dataset_code,
924 p_source_currency_code;
925 ELSIF (p_currency_type_code = 'ENTERED') THEN
926 --Bugfix 5232063: Do not assume Financial Element is populated
927 --Bugfix 5329620: Added curr_vs_map_where clause
928 g_insert_statement := g_insert_statement || '
929 AND fb.currency_code = :p_source_currency_code';
930 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
931 EXECUTE IMMEDIATE g_insert_statement
932 USING l_stat_entry_id, l_entry_id,
933 p_max_period, p_max_period, p_max_period,
934 l_periods_list(counter), p_source_ledger_id,
935 l_source_system_code,
936 p_currency_type_code, p_entity_id,
937 p_source_dataset_code, p_source_currency_code;
938 ELSE
939 --Bugfix 5232063: Do not assume Financial Element is populated
940 --Bugfix 5329620: Added curr_vs_map_where clause
941 g_insert_statement := g_insert_statement || '
942 AND fb.currency_code = :p_source_currency_code';
943 FORALL counter IN l_periods_list.FIRST..l_periods_list.LAST
944 EXECUTE IMMEDIATE g_insert_statement
945 USING l_stat_entry_id, l_entry_id,
946 p_max_period, p_max_period, p_max_period,
947 l_periods_list(counter), p_source_ledger_id,
948 l_source_system_code,
949 p_currency_type_code, p_entity_id, p_source_dataset_code,
950 p_source_currency_code;
951 END IF; -- p_balance_type_code
952
953 END IF; -- end of mapping required check
954
955 -- check if there's any data selected
956 IF (SQL%ROWCOUNT = 0) THEN
957 RAISE no_data_error;
958 END IF;
959
960 BEGIN
961 SELECT 'Y'
962 INTO l_has_row_flag
963 FROM DUAL
964 WHERE EXISTS (SELECT 1 FROM gcs_entry_lines_gt WHERE entry_id = l_entry_id);
965
966 gcs_entry_pkg.create_entry_header
967 (x_errbuf => errbuf,
968 x_retcode => retcode,
969 p_entry_id => l_entry_id,
970 p_hierarchy_id => p_hierarchy_id,
971 p_entity_id => p_entity_id,
972 p_start_cal_period_id => p_target_cal_period_id,
973 p_end_cal_period_id => p_target_cal_period_id,
974 p_entry_type_code => 'AUTOMATIC',
975 p_balance_type_code => p_balance_type_code,
976 p_currency_code => p_source_currency_code,
977 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
978 p_category_code => 'DATAPREPARATION'
979 );
980 IF p_owner_percentage IS NOT NULL AND p_owner_percentage <> 1 THEN
981 SELECT gcs_entry_headers_s.NEXTVAL
982 INTO l_proportional_entry_id
983 FROM DUAL;
984
985 gcs_entry_pkg.create_entry_header
986 (x_errbuf => errbuf,
987 x_retcode => retcode,
988 p_entry_id => l_proportional_entry_id,
989 p_hierarchy_id => p_hierarchy_id,
990 p_entity_id => p_entity_id,
991 p_start_cal_period_id => p_target_cal_period_id,
992 p_end_cal_period_id => p_target_cal_period_id,
993 p_entry_type_code => 'AUTOMATIC',
994 p_balance_type_code => p_balance_type_code,
995 p_currency_code => p_source_currency_code,
996 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
997 p_category_code => 'DATAPREPARATION'
998 );
999 END IF;
1000 EXCEPTION
1001 WHEN NO_DATA_FOUND THEN
1002 l_has_row_flag := 'N';
1003 l_entry_id := NULL;
1004 END;
1005 BEGIN
1006 SELECT 'Y'
1007 INTO l_has_stat_row_flag
1008 FROM DUAL
1009 WHERE EXISTS (SELECT 1 FROM gcs_entry_lines_gt WHERE entry_id = l_stat_entry_id);
1010
1011 gcs_entry_pkg.create_entry_header
1012 (x_errbuf => errbuf,
1013 x_retcode => retcode,
1014 p_entry_id => l_stat_entry_id,
1015 p_hierarchy_id => p_hierarchy_id,
1016 p_entity_id => p_entity_id,
1017 p_start_cal_period_id => p_target_cal_period_id,
1018 p_end_cal_period_id => p_target_cal_period_id,
1019 p_entry_type_code => 'AUTOMATIC',
1020 p_balance_type_code => p_balance_type_code,
1021 p_currency_code => 'STAT',
1022 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
1023 p_category_code => 'DATAPREPARATION'
1024 );
1025
1026 IF p_owner_percentage IS NOT NULL AND p_owner_percentage <> 1 THEN
1027 SELECT gcs_entry_headers_s.NEXTVAL
1028 INTO l_stat_proportional_entry_id
1029 FROM DUAL;
1030
1031 gcs_entry_pkg.create_entry_header
1032 (x_errbuf => errbuf,
1033 x_retcode => retcode,
1034 p_entry_id => l_stat_proportional_entry_id,
1035 p_hierarchy_id => p_hierarchy_id,
1036 p_entity_id => p_entity_id,
1037 p_start_cal_period_id => p_target_cal_period_id,
1038 p_end_cal_period_id => p_target_cal_period_id,
1039 p_entry_type_code => 'AUTOMATIC',
1040 p_balance_type_code => p_balance_type_code,
1041 p_currency_code => 'STAT',
1042 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
1043 p_category_code => 'DATAPREPARATION'
1044 );
1045 END IF;
1046 EXCEPTION
1047 WHEN NO_DATA_FOUND THEN
1048 l_has_stat_row_flag := 'N';
1049 l_stat_entry_id := NULL;
1050 END;
1051
1052 -- insert data into gcs_entry_lines table
1053 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1054 THEN
1055 fnd_log.STRING (fnd_log.level_statement,
1056 g_pkg_name || '.' || l_api_name,
1057 '
1058 INSERT /*+ APPEND */ INTO gcs_entry_lines
1059 (entry_id,
1060 ptd_debit_balance_e,
1061 ptd_credit_balance_e,
1062 ytd_debit_balance_e,
1063 ytd_credit_balance_e,
1064 xtd_balance_e,
1065 ytd_balance_e,
1066 creation_date, created_by, last_update_date,
1067 last_updated_by, last_update_login
1068 )
1069
1070 SELECT /*+ PARALLEL (fb) */ fb.entry_id,
1071 SUM (NVL (fb.ptd_debit_balance_e, 0)),
1072 SUM (NVL (fb.ptd_credit_balance_e, 0)),
1073 SUM (NVL (ytd_debit_balance_e, 0)),
1074 SUM (NVL (ytd_credit_balance_e, 0)),
1075 SUM(DECODE(fea_attr.dim_attribute_varchar_member, ''REVENUE'', NVL(xtd_balance_e,0),
1076 ''EXPENSE'', NVL(xtd_balance_e,0),
1077 NVL(ytd_balance_e,0))),
1078 SUM (NVL (ytd_balance_e, 0)),
1079 SYSDATE,
1080 FND_GLOBAL.USER_ID,
1081 SYSDATE,
1082 FND_GLOBAL.USER_ID,
1083 FND_GLOBAL.LOGIN_ID
1084 FROM gcs_entry_lines_gt fb,
1085 fem_ln_items_attr flia,
1086 fem_ext_acct_types_attr fea_attr
1087 WHERE fb.line_item_id = flia.line_item_id
1088 AND flia.attribute_id = l_line_item_type_attr
1089 AND flia.version_id = l_line_item_type_version
1090 AND flia.dim_attribute_varchar_member = fea_attr.ext_account_type_code
1091 AND fea_attr.attribute_id = l_acct_type_attr
1092 AND fea_attr.version_id = l_acct_type_version
1093 GROUP BY entry_id;'
1094 );
1095 END IF;
1096
1097
1098 INSERT /*+ APPEND */ INTO gcs_entry_lines
1099 (entry_id,
1100 ptd_debit_balance_e,
1101 ptd_credit_balance_e,
1102 ytd_debit_balance_e,
1103 ytd_credit_balance_e,
1104 xtd_balance_e,
1105 ytd_balance_e,
1106 creation_date, created_by, last_update_date,
1107 last_updated_by, last_update_login
1108 )
1109
1110 SELECT /*+ PARALLEL (fb) */ fb.entry_id,
1111 SUM (NVL (fb.ptd_debit_balance_e, 0)),
1112 SUM (NVL (fb.ptd_credit_balance_e, 0)),
1113 SUM (NVL (ytd_debit_balance_e, 0)),
1114 SUM (NVL (ytd_credit_balance_e, 0)),
1115 SUM(DECODE(fea_attr.dim_attribute_varchar_member, 'REVENUE', NVL(xtd_balance_e,0),
1116 'EXPENSE', NVL(xtd_balance_e,0),
1117 NVL(ytd_balance_e,0))),
1118 SUM (NVL (ytd_balance_e, 0)),
1119 SYSDATE,
1120 FND_GLOBAL.USER_ID,
1121 SYSDATE,
1122 FND_GLOBAL.USER_ID,
1123 FND_GLOBAL.LOGIN_ID
1124 FROM gcs_entry_lines_gt fb,
1125 fem_ln_items_attr flia,
1126 fem_ext_acct_types_attr fea_attr
1127 WHERE fb.line_item_id = flia.line_item_id
1128 AND flia.attribute_id = l_line_item_type_attr
1129 AND flia.version_id = l_line_item_type_version
1130 AND flia.dim_attribute_varchar_member = fea_attr.ext_account_type_code
1131 AND fea_attr.attribute_id = l_acct_type_attr
1132 AND fea_attr.version_id = l_acct_type_version
1133 GROUP BY entry_id;
1134
1135 COMMIT;
1136
1137 -- recalculate P/L AND Retained Earnings accounts if year ends not match
1138 IF (l_entry_id IS NOT NULL AND p_year_end_values_match = 'N') THEN
1139 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1140 THEN
1141 fnd_log.STRING (fnd_log.level_statement,
1142 g_pkg_name || '.' || l_api_name,
1143 ' SELECT decode(count(run_name), 0, ''Y'', ''N'')
1144 INTO l_first_ever_data_prepped
1145 FROM gcs_cons_eng_runs
1146 WHERE hierarchy_id = '||p_hierarchy_id||'
1147 AND run_entity_id = '||p_entity_id||'
1148 AND balance_type_code = '||p_balance_type_code||'
1149 AND (cal_period_id = '||p_cal_period_record.prev_cal_period_id||'
1150 OR (cal_period_id = '||p_cal_period_record.cal_period_id||'
1151 AND status_code NOT IN (''NOT_STARTED'', ''IN_PROGRESS'')))');
1152 END IF;
1153 SELECT decode(count(run_name), 0, 'Y', 'N')
1154 INTO l_first_ever_data_prepped
1155 FROM gcs_cons_eng_runs
1156 WHERE hierarchy_id = p_hierarchy_id
1157 AND run_entity_id = p_entity_id
1158 AND balance_type_code = p_balance_type_code
1159 AND ( cal_period_id = p_cal_period_record.prev_cal_period_id
1160 OR (cal_period_id = p_cal_period_record.cal_period_id
1161 AND status_code NOT IN ('NOT_STARTED', 'IN_PROGRESS')));
1162 IF ( l_first_ever_data_prepped = 'Y' OR p_cal_period_record.cal_period_number = 1 )
1163 THEN
1164 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1165 THEN
1166 fnd_log.STRING (fnd_log.level_statement,
1167 g_pkg_name || '.' || l_api_name,
1168 ' UPDATE gcs_entry_lines gel
1169 SET gel.ytd_balance_e = gel.xtd_balance_e,
1170 gel.ytd_debit_balance_e = gel.ptd_debit_balance_e,
1171 gel.ytd_credit_balance_e = gel.ptd_credit_balance_e
1172 WHERE gel.entry_id = '||l_entry_id ||'
1173 AND EXISTS ( SELECT ''X''
1174 FROM fem_ln_items_attr flia,
1175 fem_ext_acct_types_attr feata
1176 WHERE feata.dim_attribute_varchar_member IN (''REVENUE'', ''EXPENSE'')
1177 AND flia.attribute_id ='|| g_li_eat_attr_id ||'
1178 AND flia.version_id ='|| g_li_eat_ver_id ||'
1179 AND flia.value_set_id ='|| g_li_vs_id ||'
1180 AND feata.attribute_id = ' || g_eatc_batc_attr_id || '
1181 AND feata.version_id = ' || g_eatc_batc_ver_id || '
1182 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
1183 AND gel.line_item_id = flia.line_item_id)');
1184 END IF;
1185 UPDATE gcs_entry_lines gel
1186 SET gel.ytd_balance_e = gel.xtd_balance_e,
1187 gel.ytd_debit_balance_e = gel.ptd_debit_balance_e,
1188 gel.ytd_credit_balance_e = gel.ptd_credit_balance_e
1189 WHERE gel.entry_id = l_entry_id
1190 AND EXISTS ( SELECT 'X'
1191 FROM fem_ln_items_attr flia,
1192 fem_ext_acct_types_attr feata
1193 WHERE feata.dim_attribute_varchar_member IN ('REVENUE', 'EXPENSE')
1194 AND flia.attribute_id = g_li_eat_attr_id
1195 AND flia.version_id = g_li_eat_ver_id
1196 AND flia.value_set_id = g_li_vs_id
1197 AND feata.attribute_id = g_eatc_batc_attr_id
1198 AND feata.version_id = g_eatc_batc_ver_id
1199 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
1200 AND gel.line_item_id = flia.line_item_id);
1201 ELSE
1202 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1203 THEN
1204 fnd_log.STRING (fnd_log.level_statement,
1205 g_pkg_name || '.' || l_api_name,
1206 ' UPDATE gcs_entry_lines gel
1207 SET (gel.ytd_balance_e, gel.ytd_credit_balance_e,gel.ytd_debit_balance_e) =
1208 (SELECT NVL (fb.ytd_balance_e, 0)
1209 + NVL (gel.xtd_balance_e, 0),
1210 NVL (fb.ytd_credit_balance_e, 0)
1211 + NVL (gel.ptd_credit_balance_e, 0),
1212 NVL (fb.ytd_debit_balance_e, 0)
1213 + NVL (gel.ptd_debit_balance_e, 0)
1214 FROM fem_balances fb,
1215 fem_ln_items_attr flia,
1216 fem_ext_acct_types_attr feata
1217 WHERE feata.dim_attribute_varchar_member IN
1218 (''REVENUE'', ''EXPENSE'')
1219 AND flia.attribute_id ='|| g_li_eat_attr_id ||'
1220 AND flia.version_id ='|| g_li_eat_ver_id ||'
1221 AND flia.value_set_id ='|| g_li_vs_id ||'
1222 AND feata.attribute_id = ' || g_eatc_batc_attr_id || '
1223 AND feata.version_id = ' || g_eatc_batc_ver_id || '
1224 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
1225 AND fb.cal_period_id ='|| p_cal_period_record.prev_cal_period_id||'
1226 AND fb.line_item_id = flia.line_item_id
1227 AND fb.source_system_code = ' || l_source_system_code||'
1228 )
1229 WHERE gel.entry_id = '||l_entry_id);
1230 END IF;
1231 UPDATE gcs_entry_lines gel
1232 SET (gel.ytd_balance_e, gel.ytd_credit_balance_e,gel.ytd_debit_balance_e) =
1233 (SELECT NVL (fb.ytd_balance_e, 0)
1234 + NVL (gel.xtd_balance_e, 0),
1235 NVL (fb.ytd_credit_balance_e, 0)
1236 + NVL (gel.ptd_credit_balance_e, 0),
1237 NVL (fb.ytd_debit_balance_e, 0)
1238 + NVL (gel.ptd_debit_balance_e, 0)
1239 FROM fem_balances fb,
1240 fem_ln_items_attr flia,
1241 fem_ext_acct_types_attr feata
1242 WHERE feata.dim_attribute_varchar_member IN ('REVENUE', 'EXPENSE')
1243 AND flia.attribute_id = g_li_eat_attr_id
1244 AND flia.version_id = g_li_eat_ver_id
1245 AND flia.value_set_id = g_li_vs_id
1246 AND feata.attribute_id = g_eatc_batc_attr_id
1247 AND feata.version_id = g_eatc_batc_ver_id
1248 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
1249 AND fb.cal_period_id = p_cal_period_record.prev_cal_period_id
1250 AND fb.line_item_id = flia.line_item_id
1251 AND fb.source_system_code = l_source_system_code
1252 )
1253 WHERE gel.entry_id = l_entry_id;
1254 END IF;
1255 GCS_templates_dynamic_PKG.CALCULATE_DP_RE(p_entry_id => l_entry_id,
1256 p_hierarchy_id => p_hierarchy_id,
1257 p_bal_type_code => p_balance_type_code,
1258 p_entity_id => p_entity_id,
1259 p_pre_cal_period_id => p_cal_period_record.prev_cal_period_id,
1260 p_first_ever_data_prep => l_first_ever_data_prepped);
1261 END IF;
1262 retcode := gcs_utility_pkg.g_ret_sts_success;
1263 -- Suspense accounts
1264 IF l_entry_id IS NOT NULL
1265 THEN
1266 -- check balance criteria
1267 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1268 THEN
1269 fnd_log.STRING (fnd_log.level_statement,
1270 g_pkg_name || '.' || l_api_name,
1271 ' SELECT threshold_amount,
1272 threshold_currency
1273 INTO l_threshold,
1274 l_threshold_currency
1275 FROM gcs_hierarchies_b
1276 WHERE hierarchy_id = '||p_hierarchy_id);
1277 END IF;
1278 SELECT threshold_amount, threshold_currency
1279 INTO l_threshold, l_threshold_currency
1280 FROM gcs_hierarchies_b
1281 WHERE hierarchy_id = p_hierarchy_id;
1282 BEGIN
1283 gcs_templates_pkg.get_dimension_template
1284 (p_hierarchy_id => p_hierarchy_id,
1285 p_template_code => 'SUSPENSE',
1286 p_balance_type_code => p_balance_type_code,
1287 p_template_record => l_temp_record
1288 );
1289 EXCEPTION
1290 WHEN OTHERS
1291 THEN
1292 RAISE no_suspense_template_error;
1293 END;
1294 gcs_templates_dynamic_pkg.balance (p_entry_id => l_entry_id,
1295 p_template => l_temp_record,
1296 p_bal_type_code => p_balance_type_code,
1297 p_hierarchy_id => p_hierarchy_id,
1298 p_entity_id => p_entity_id,
1299 p_threshold => l_threshold,
1300 p_threshold_currency_code => l_threshold_currency
1301 );
1302 --bug fix 3797312
1303 SELECT DECODE(SUSPENSE_EXCEEDED_FLAG, 'Y', 'WARNING', 'COMPLETED')
1304 INTO retcode
1305 FROM gcs_entry_headers
1306 WHERE entry_id = l_entry_id;
1307 END IF;
1308
1309 -- bug fix 3800183
1310 IF (l_proportional_entry_id IS NOT NULL or l_stat_proportional_entry_id IS NOT NULL)
1311 THEN
1312 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1313 THEN
1314 fnd_log.STRING (fnd_log.level_statement,
1315 g_pkg_name || '.' || l_api_name,
1316 ' SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
1317 NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
1318 INTO l_precision,
1319 l_stat_precision
1320 FROM fnd_currencies fc_1, fnd_currencies fc_stat
1321 WHERE fc_1.currency_code = ' ||p_source_currency_code||'
1322 AND fc_stat.currency_code = ''STAT''');
1323 END IF;
1324 SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
1325 NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
1326 INTO l_precision, l_stat_precision
1327 FROM fnd_currencies fc_1, fnd_currencies fc_stat
1328 WHERE fc_1.currency_code = p_source_currency_code
1329 AND fc_stat.currency_code = 'STAT';
1330 INSERT INTO gcs_entry_lines
1331 (entry_id,
1332 ptd_debit_balance_e,
1333 ptd_credit_balance_e,
1334 ytd_debit_balance_e,
1335 ytd_credit_balance_e,
1336 xtd_balance_e,
1337 ytd_balance_e,
1338 creation_date, created_by, last_update_date,
1339 last_updated_by, last_update_login
1340 )
1341 SELECT decode(entry_id, l_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id),
1342
1343 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),
1344 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),
1345 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),
1346 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),
1347 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)
1348 - 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),
1349 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)
1350 - 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),
1351 creation_date, created_by, last_update_date,
1352 last_updated_by, last_update_login
1353 FROM gcs_entry_lines
1354 WHERE entry_id in ( l_entry_id, l_stat_entry_id);
1355 -- end of bug fix 3800183
1356 --bug fix 3797312
1357 IF l_proportional_entry_id IS NOT NULL THEN
1358 gcs_templates_dynamic_pkg.balance (p_entry_id => l_proportional_entry_id,
1359 p_template => l_temp_record,
1360 p_bal_type_code => p_balance_type_code,
1361 p_hierarchy_id => p_hierarchy_id,
1362 p_entity_id => p_entity_id,
1363 p_threshold => l_threshold,
1364 p_threshold_currency_code => l_threshold_currency
1365 );
1366 SELECT decode(SUSPENSE_EXCEEDED_FLAG, 'Y', 'WARNING', 'COMPLETED')
1367 INTO retcode
1368 FROM gcs_entry_headers
1369 WHERE entry_id = l_proportional_entry_id;
1370 ELSE
1371 retcode := gcs_utility_pkg.g_ret_sts_success;
1372 END IF;
1373 gcs_cons_eng_run_dtls_pkg.update_entry_headers
1374 (p_run_detail_id => p_run_detail_id,
1375 p_entry_id => l_proportional_entry_id,
1376 p_stat_entry_id => l_stat_proportional_entry_id,
1377 p_pre_prop_entry_id => l_entry_id,
1378 p_pre_prop_stat_entry_id => l_stat_entry_id,
1379 p_request_error_code => retcode,
1380 p_bp_request_error_code => retcode
1381 );
1382 ELSE
1383 gcs_cons_eng_run_dtls_pkg.update_entry_headers
1384 (p_run_detail_id => p_run_detail_id,
1385 p_entry_id => l_entry_id,
1386 p_stat_entry_id => l_stat_entry_id,
1387 p_pre_prop_entry_id => l_proportional_entry_id,
1388 p_pre_prop_stat_entry_id => l_stat_proportional_entry_id,
1389 p_request_error_code => retcode,
1390 p_bp_request_error_code => retcode
1391 );
1392 END IF;
1393 -- Check the GCS_SYSTEM_OPTIONS.INTERCO_MAP_ENABLED_FLAG.
1394 SELECT NVL(interco_map_enabled_flag,'N')
1395 INTO l_imap_enabled_flag
1396 FROM gcs_system_options;
1397 -- If enabled then update the above created entry to populate the correct intercompany values according to the line_item value
1398 IF l_imap_enabled_flag = 'Y'
1399 THEN
1400 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1401 THEN
1402 fnd_log.STRING (fnd_log.level_statement,
1403 g_pkg_name || '.' || l_api_name,
1404 ' UPDATE gcs_entry_lines gel
1405 SET gel.intercompany_id = ( SELECT intercompany_id
1406 FROM gcs_interco_map_dtls gimd
1407 WHERE gimd.line_item_id = gel.line_item_id
1408 )
1409 WHERE gel.entry_id IN( l_entry_id, l_stat_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id)
1410 AND EXISTS ( SELECT intercompany_id
1411 FROM gcs_interco_map_dtls gimd
1412 WHERE gimd.line_item_id = gel.line_item_id
1413 );');
1414 END IF;
1415 UPDATE gcs_entry_lines gel
1416 SET gel.intercompany_id = ( SELECT intercompany_id
1417 FROM gcs_interco_map_dtls gimd
1418 WHERE gimd.line_item_id = gel.line_item_id
1419 )
1420 WHERE gel.entry_id IN( l_entry_id, l_stat_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id)
1421 AND EXISTS ( SELECT intercompany_id
1422 FROM gcs_interco_map_dtls gimd
1423 WHERE gimd.line_item_id = gel.line_item_id
1424 );
1425
1426 END IF;IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1427 THEN
1428 fnd_log.STRING (fnd_log.level_procedure,
1429 g_pkg_name || '.' || l_api_name,
1430 gcs_utility_pkg.g_module_success
1431 || ' '
1432 || l_api_name
1433 || '()'
1434 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1435 );
1436 END IF;
1437 EXCEPTION
1438 WHEN no_suspense_template_error THEN
1439 retcode := gcs_utility_pkg.g_ret_sts_error;
1440 FND_MESSAGE.set_name('GCS', 'GCS_DP_NO_SUSPENSE_ERR');
1441 errbuf := fnd_message.get;
1442
1443 DELETE FROM gcs_entry_headers
1444 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
1445 l_proportional_entry_id, l_stat_proportional_entry_id);
1446 DELETE FROM gcs_entry_lines
1447 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
1448 l_proportional_entry_id, l_stat_proportional_entry_id);
1449
1450 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1451 THEN
1452 fnd_log.STRING (fnd_log.level_error,
1453 g_pkg_name || '.' || l_api_name,
1454 gcs_utility_pkg.g_module_failure
1455 || ' '
1456 || errbuf
1457 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1458 );
1459 END IF;
1460 raise gcs_dp_proc_data_error;
1461 WHEN no_re_template_error THEN
1462 retcode := gcs_utility_pkg.g_ret_sts_error;
1463 FND_MESSAGE.set_name('GCS', 'GCS_DP_NO_RE_ERR');
1464 errbuf := fnd_message.get;
1465
1466 DELETE FROM gcs_entry_headers
1467 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
1468 l_proportional_entry_id, l_stat_proportional_entry_id);
1469 DELETE FROM gcs_entry_lines
1470 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
1471 l_proportional_entry_id, l_stat_proportional_entry_id);
1472
1473 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1474 THEN
1475 fnd_log.STRING (fnd_log.level_error,
1476 g_pkg_name || '.' || l_api_name,
1477 gcs_utility_pkg.g_module_failure
1478 || ' '
1479 || errbuf
1480 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1481 );
1482 END IF;
1483 raise gcs_dp_proc_data_error;
1484 WHEN init_mapping_error THEN
1485 raise gcs_dp_proc_data_error;
1486 WHEN no_data_error THEN
1487 retcode := gcs_utility_pkg.g_ret_sts_warn;
1488 FND_MESSAGE.set_name('GCS', 'GCS_DP_NO_DATA_ERR');
1489 errbuf := fnd_message.get;
1490 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
1491 fnd_log.STRING (fnd_log.level_error,
1492 g_pkg_name || '.' || l_api_name,
1493 gcs_utility_pkg.g_module_failure
1494 || ' '
1495 || errbuf
1496 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1497 );
1498 END IF;
1499 gcs_cons_eng_run_dtls_pkg.update_entry_headers
1500 (p_run_detail_id => p_run_detail_id,
1501 p_entry_id => NULL,
1502 p_stat_entry_id => NULL,
1503 p_pre_prop_entry_id => NULL,
1504 p_pre_prop_stat_entry_id => NULL,
1505 p_request_error_code => 'NOT_APPLICABLE',
1506 p_bp_request_error_code => 'NOT_APPLICABLE'
1507 );
1508 -- bug 5071794 fix: catch unexpected error and reraise it
1509 WHEN others THEN
1510 retcode := gcs_utility_pkg.g_ret_sts_error;
1511 errbuf := SQLERRM;
1512
1513 DELETE FROM gcs_entry_headers
1514 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
1515 l_proportional_entry_id, l_stat_proportional_entry_id);
1516 DELETE FROM gcs_entry_lines
1517 WHERE entry_id IN (l_entry_id, l_stat_entry_id,
1518 l_proportional_entry_id, l_stat_proportional_entry_id);
1519
1520 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
1521 fnd_log.STRING (fnd_log.level_error,
1522 g_pkg_name || '.' || l_api_name,
1523 gcs_utility_pkg.g_module_failure
1524 || ' '
1525 || errbuf
1526 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1527 );
1528 END IF;
1529 RAISE gcs_dp_proc_data_error;
1530 END PROCESS_DATA;
1531
1532 PROCEDURE process_inc_data (
1533 p_source_currency_code IN VARCHAR2,
1534 p_target_cal_period_id IN NUMBER,
1535 p_currency_type_code IN VARCHAR2,
1536 p_hierarchy_id IN NUMBER,
1537 p_entity_id IN NUMBER,
1538 p_source_ledger_id IN NUMBER,
1539 p_balance_type_code IN VARCHAR2,
1540 p_owner_percentage IN NUMBER,
1541 p_run_name IN VARCHAR2,
1542 p_source_dataset_code IN NUMBER,
1543 x_entry_id OUT NOCOPY NUMBER,
1544 x_stat_entry_id OUT NOCOPY NUMBER,
1545 x_prop_entry_id OUT NOCOPY NUMBER,
1546 x_stat_prop_entry_id OUT NOCOPY NUMBER,
1547 errbuf OUT NOCOPY VARCHAR2,
1548 retcode OUT NOCOPY VARCHAR2
1549 )
1550 IS
1551 l_has_row_flag VARCHAR2 (1);
1552 l_has_stat_row_flag VARCHAR2 (1);
1553 l_temp_record gcs_templates_pkg.templaterecord;
1554 l_threshold NUMBER;
1555 l_threshold_currency VARCHAR2 (30);
1556 l_pre_entry_id NUMBER (15);
1557 l_pre_stat_entry_id NUMBER (15);
1558 l_precision NUMBER;
1559 l_stat_precision NUMBER;
1560 l_mapping_required VARCHAR2 (1);
1561 l_api_name VARCHAR2 (20) := 'PROCESS_INC_DATA';
1562 l_imap_enabled_flag VARCHAR2 (1);
1563 BEGIN
1564 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1565 THEN
1566 fnd_log.STRING (fnd_log.level_procedure,
1567 g_pkg_name || '.' || l_api_name,
1568 gcs_utility_pkg.g_module_enter
1569 || ' '
1570 || l_api_name
1571 || '()'
1572 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1573 );
1574 END IF;
1575
1576 -- init local_to_master mappping
1577 l_mapping_required := init_local_to_master_maps (p_source_ledger_id => p_source_ledger_id,
1578 p_cal_period_id => p_target_cal_period_id,
1579 errbuf => errbuf,
1580 retcode => retcode,
1581 p_inc_mode_flag => 'Y');
1582 IF (retcode <> gcs_utility_pkg.g_ret_sts_success)
1583 THEN
1584 RAISE init_mapping_error;
1585 END IF;
1586 SELECT gcs_entry_headers_s.NEXTVAL
1587 INTO x_entry_id
1588 FROM DUAL;
1589 SELECT gcs_entry_headers_s.NEXTVAL
1590 INTO x_stat_entry_id
1591 FROM DUAL;
1592 IF (l_mapping_required = 'N') THEN
1593 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1594 THEN
1595 fnd_log.STRING (fnd_log.level_statement,
1596 g_pkg_name || '.' || l_api_name,
1597 '
1598 INSERT /*+ APPEND */ INTO gcs_entry_lines
1599 (entry_id,
1600 ptd_debit_balance_e,
1601 ptd_credit_balance_e,
1602 ytd_debit_balance_e,
1603 ytd_credit_balance_e,
1604 xtd_balance_e,
1605 ytd_balance_e,
1606 creation_date, created_by, last_update_date,
1607 last_updated_by, last_update_login
1608 )
1609 SELECT decode(fb.currency_code, ''STAT'', x_stat_entry_id, x_entry_id),
1610 SUM(fb.ptd_debit_balance_e) PTD_DEBIT_BALANCE_E,
1611 SUM(fb.ptd_credit_balance_e) PTD_CREDIT_BALANCE_E,
1612 SUM(fb.ytd_debit_balance_e) YTD_DEBIT_BALANCE_E,
1613 SUM(fb.ytd_credit_balance_e) YTD_CREDIT_BALANCE_E,
1614 SUM(NVL(fb.xtd_balance_f, fb.xtd_balance_e)) XTD_BALANCE_E,
1615 SUM(NVL(fb.ytd_balance_f, fb.ytd_balance_e)) YTD_BALANCE_E,
1616 SYSDATE,
1617 FND_GLOBAL.USER_ID,
1618 SYSDATE,
1619 FND_GLOBAL.USER_ID,
1620 FND_GLOBAL.LOGIN_ID
1621 FROM fem_balances fb,
1622 fem_ledgers_attr fla,
1623 gcs_entity_cctr_orgs geco,
1624 gcs_cons_impact_analyses gcia,
1625 gcs_data_sub_dtls gdsd
1626 WHERE fb.ledger_id = p_source_ledger_id
1627 AND fb.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
1628 AND fla.ledger_id = fb.ledger_id
1629 AND fla.attribute_id = g_ledger_ssc_attr_id
1630 AND fla.version_id = g_ledger_ssc_ver_id
1631 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
1632 AND geco.entity_id = p_entity_id
1633 AND p_balance_type_code = DECODE(fb.financial_elem_id, 140, ''ADB'', ''ACTUAL'')
1634 AND ((fb.currency_type_code = ''TRANSLATED'' AND
1635 fb.currency_code IN (''STAT'', p_source_currency_code)) OR
1636 (fb.currency_type_code = ''ENTERED''))
1637 AND fb.currency_type_code = p_currency_type_code
1638 AND fb.dataset_code = p_source_dataset_code
1639 AND fb.last_updated_by_request_id = gdsd.associated_request_id
1640 AND gcia.run_name = p_run_name
1641 AND gcia.child_entity_id = p_entity_id
1642 AND gcia.load_id = gdsd.load_id GROUP BY decode(fb.currency_code, ''STAT'', x_stat_entry_id, x_entry_id);'
1643 );
1644 END IF;
1645
1646 INSERT /*+ APPEND */ INTO gcs_entry_lines
1647 (entry_id,
1648 ptd_debit_balance_e,
1649 ptd_credit_balance_e,
1650 ytd_debit_balance_e,
1651 ytd_credit_balance_e,
1652 xtd_balance_e,
1653 ytd_balance_e,
1654 creation_date, created_by, last_update_date,
1655 last_updated_by, last_update_login
1656 )
1657 SELECT decode(fb.currency_code, 'STAT', x_stat_entry_id, x_entry_id),
1658 SUM(fb.ptd_debit_balance_e) PTD_DEBIT_BALANCE_E,
1659 SUM(fb.ptd_credit_balance_e) PTD_CREDIT_BALANCE_E,
1660 SUM(fb.ytd_debit_balance_e) YTD_DEBIT_BALANCE_E,
1661 SUM(fb.ytd_credit_balance_e) YTD_CREDIT_BALANCE_E,
1662 SUM(NVL(fb.xtd_balance_f, fb.xtd_balance_e)) XTD_BALANCE_E,
1663 SUM(NVL(fb.ytd_balance_f, fb.ytd_balance_e)) YTD_BALANCE_E,
1664 SYSDATE,
1665 FND_GLOBAL.USER_ID,
1666 SYSDATE,
1667 FND_GLOBAL.USER_ID,
1668 FND_GLOBAL.LOGIN_ID
1669 FROM fem_balances fb,
1670 fem_ledgers_attr fla,
1671 gcs_entity_cctr_orgs geco,
1672 gcs_cons_impact_analyses gcia,
1673 gcs_data_sub_dtls gdsd
1674 WHERE fb.ledger_id = p_source_ledger_id
1675 AND fb.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
1676 AND fla.ledger_id = fb.ledger_id
1677 AND fla.attribute_id = g_ledger_ssc_attr_id
1678 AND fla.version_id = g_ledger_ssc_ver_id
1679 AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
1680 AND geco.entity_id = p_entity_id
1681 AND p_balance_type_code = DECODE(fb.financial_elem_id, 140, 'ADB', 'ACTUAL')
1682 AND ((fb.currency_type_code = 'TRANSLATED' AND
1683 fb.currency_code IN ('STAT', p_source_currency_code)) OR
1684 (fb.currency_type_code = 'ENTERED'))
1685 AND fb.currency_type_code = p_currency_type_code
1686 AND fb.dataset_code = p_source_dataset_code
1687 AND fb.last_updated_by_request_id = gdsd.associated_request_id
1688 AND gcia.run_name = p_run_name
1689 AND gcia.child_entity_id = p_entity_id
1690 AND gcia.load_id = gdsd.load_id GROUP BY decode(fb.currency_code, 'STAT', x_stat_entry_id, x_entry_id);
1691 ELSE
1692 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1693 THEN
1694 fnd_log.STRING (fnd_log.level_statement,
1695 g_pkg_name || '.' || l_api_name,
1696 ' g_insert_statement = '
1697 || g_insert_statement
1698 );
1699 fnd_log.STRING (fnd_log.level_statement,
1700 g_pkg_name || '.' || l_api_name,
1701 'EXECUTE IMMEDIATE g_insert_statement '
1702 || g_nl
1703 || ' USING '
1704 || x_stat_entry_id || ', ' || x_entry_id ||', '
1705 || fnd_global.user_id ||', '|| fnd_global.login_id ||', '|| p_source_ledger_id ||', '
1706 || g_ledger_ssc_attr_id ||', '
1707 || g_ledger_ssc_ver_id ||', '
1708 || p_currency_type_code ||', '|| p_hierarchy_id ||', '
1709 || p_entity_id ||', '|| p_balance_type_code ||', '
1710 || g_ln_item_vs_id ||', '
1711 || g_li_eat_attr_id ||', '
1712 || g_eatc_batc_attr_id ||', '
1713 || g_li_eat_ver_id ||', '
1714 || g_eatc_batc_ver_id ||', '
1715 || p_source_currency_code ||', '
1716 || p_source_dataset_code ||', '|| p_run_name ||', '|| p_entity_id ||', '
1717 || x_stat_entry_id || ', ' || x_entry_id
1718 );
1719 END IF;
1720 EXECUTE IMMEDIATE g_insert_statement
1721 USING x_stat_entry_id, x_entry_id,
1722 fnd_global.user_id, fnd_global.user_id, fnd_global.login_id, p_source_ledger_id,
1723 g_ledger_ssc_attr_id, g_ledger_ssc_ver_id, p_currency_type_code,
1724 p_entity_id, p_balance_type_code, g_ln_item_vs_id, g_li_eat_attr_id,
1725 g_eatc_batc_attr_id, g_li_eat_ver_id, g_eatc_batc_ver_id,
1726 p_source_currency_code, p_source_dataset_code, p_run_name,
1727 p_entity_id, x_stat_entry_id, x_entry_id;
1728 END IF;
1729
1730 BEGIN
1731 SELECT 'Y'
1732 INTO l_has_row_flag
1733 FROM DUAL
1734 WHERE EXISTS (SELECT 1 FROM gcs_entry_lines WHERE entry_id = x_entry_id);
1735 EXCEPTION
1736 WHEN NO_DATA_FOUND THEN
1737 l_has_row_flag := 'N';
1738 END;
1739 BEGIN
1740 SELECT 'Y'
1741 INTO l_has_stat_row_flag
1742 FROM DUAL
1743 WHERE EXISTS (SELECT 1 FROM gcs_entry_lines WHERE entry_id = x_stat_entry_id);
1744 EXCEPTION
1745 WHEN NO_DATA_FOUND THEN
1746 l_has_stat_row_flag := 'N';
1747 END;
1748 IF l_has_stat_row_flag = 'N' AND l_has_row_flag = 'N'THEN
1749 RAISE no_data_error;
1750 END IF;
1751
1752 BEGIN
1753 SELECT decode (p_owner_percentage, 1, entry_id, pre_prop_entry_id),
1754 decode (p_owner_percentage, 1, stat_entry_id, pre_prop_stat_entry_id)
1755 INTO l_pre_entry_id, l_pre_stat_entry_id
1756 FROM gcs_cons_eng_run_dtls
1757 WHERE child_entity_id = p_entity_id
1758 AND category_code = 'DATAPREPARATION'
1759 AND run_name in (
1760 SELECT nvl(associated_run_name, run_name)
1761 FROM gcs_cons_eng_runs
1762 WHERE hierarchy_id = p_hierarchy_id
1763 AND cal_period_id = p_target_cal_period_id
1764 AND balance_type_code = p_balance_type_code
1765 AND most_recent_flag = 'Y'
1766 );
1767 UPDATE gcs_entry_lines gel
1768 SET (ptd_debit_balance_e, ptd_credit_balance_e, xtd_balance_e,
1769 ytd_debit_balance_e, ytd_credit_balance_e, ytd_balance_e) =
1770 (SELECT gel.ptd_debit_balance_e - gel_pre.ptd_debit_balance_e,
1771 gel.ptd_credit_balance_e - gel_pre.ptd_credit_balance_e,
1772 gel.xtd_balance_e - gel_pre.xtd_balance_e,
1773 gel.ytd_debit_balance_e - gel_pre.ytd_debit_balance_e,
1774 gel.ytd_credit_balance_e - gel_pre.ytd_credit_balance_e,
1775 gel.ytd_balance_e - gel_pre.ytd_balance_e
1776 FROM gcs_entry_lines gel_pre
1777 WHERE gel_pre.entry_id = decode(gel.entry_id,
1778 x_entry_id,
1779 l_pre_entry_id,
1780 l_pre_stat_entry_id)
1781 )
1782 WHERE gel.entry_id in (x_entry_id, x_stat_entry_id)
1783 AND EXISTS (SELECT 1
1784 FROM gcs_entry_lines gel_pre
1785 WHERE gel_pre.entry_id = decode(gel.entry_id,
1786 x_entry_id,
1787 l_pre_entry_id,
1788 l_pre_stat_entry_id)
1789 ) ;
1790 EXCEPTION
1791 WHEN no_data_found then
1792 null;
1793 END;
1794 IF l_has_stat_row_flag = 'Y' THEN
1795 if p_owner_percentage <> 1 THEN
1796 SELECT gcs_entry_headers_s.NEXTVAL
1797 INTO x_stat_prop_entry_id
1798 FROM DUAL;
1799 END IF;
1800 ELSE
1801 x_stat_entry_id := NULL;
1802 END IF;
1803 IF l_has_row_flag = 'Y' THEN
1804 if p_owner_percentage <> 1 THEN
1805 SELECT gcs_entry_headers_s.NEXTVAL
1806 INTO x_prop_entry_id
1807 FROM DUAL;
1808 END IF;
1809 ELSE
1810 x_entry_id := NULL;
1811 END IF;
1812
1813 IF x_stat_entry_id IS NOT NULL
1814 THEN
1815 gcs_entry_pkg.create_entry_header
1816 (x_errbuf => errbuf,
1817 x_retcode => retcode,
1818 p_entry_id => x_stat_entry_id,
1819 p_hierarchy_id => p_hierarchy_id,
1820 p_entity_id => p_entity_id,
1821 p_start_cal_period_id => p_target_cal_period_id,
1822 p_end_cal_period_id => p_target_cal_period_id,
1823 p_entry_type_code => 'AUTOMATIC',
1824 p_balance_type_code => p_balance_type_code,
1825 p_currency_code => 'STAT',
1826 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
1827 p_category_code => 'DATAPREPARATION'
1828 );
1829 -- insert stat proportional entries
1830 IF (x_stat_prop_entry_id is not null)
1831 THEN
1832 gcs_entry_pkg.create_entry_header
1833 (x_errbuf => errbuf,
1834 x_retcode => retcode,
1835 p_entry_id => x_stat_prop_entry_id,
1836 p_hierarchy_id => p_hierarchy_id,
1837 p_entity_id => p_entity_id,
1838 p_start_cal_period_id => p_target_cal_period_id,
1839 p_end_cal_period_id => p_target_cal_period_id,
1840 p_entry_type_code => 'AUTOMATIC',
1841 p_balance_type_code => p_balance_type_code,
1842 p_currency_code => 'STAT',
1843 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
1844 p_category_code => 'DATAPREPARATION'
1845 );
1846 END IF;
1847 END IF;
1848 IF x_entry_id IS NOT NULL
1849 THEN
1850 gcs_entry_pkg.create_entry_header
1851 (x_errbuf => errbuf,
1852 x_retcode => retcode,
1853 p_entry_id => x_entry_id,
1854 p_hierarchy_id => p_hierarchy_id,
1855 p_entity_id => p_entity_id,
1856 p_start_cal_period_id => p_target_cal_period_id,
1857 p_end_cal_period_id => p_target_cal_period_id,
1858 p_entry_type_code => 'AUTOMATIC',
1859 p_balance_type_code => p_balance_type_code,
1860 p_currency_code => p_source_currency_code,
1861 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
1862 p_category_code => 'DATAPREPARATION'
1863 );
1864 -- insert proportional entries
1865 IF (x_prop_entry_id is not null)
1866 THEN
1867 gcs_entry_pkg.create_entry_header
1868 (x_errbuf => errbuf,
1869 x_retcode => retcode,
1870 p_entry_id => x_prop_entry_id,
1871 p_hierarchy_id => p_hierarchy_id,
1872 p_entity_id => p_entity_id,
1873 p_start_cal_period_id => p_target_cal_period_id,
1874 p_end_cal_period_id => p_target_cal_period_id,
1875 p_entry_type_code => 'AUTOMATIC',
1876 p_balance_type_code => p_balance_type_code,
1877 p_currency_code => p_source_currency_code,
1878 p_process_code => 'SINGLE_RUN_FOR_PERIOD',
1879 p_category_code => 'DATAPREPARATION'
1880 );
1881 END IF;
1882 END IF;
1883 retcode := gcs_utility_pkg.g_ret_sts_success;
1884 -- Suspense accounts
1885 IF x_entry_id IS NOT NULL
1886 THEN
1887 -- check balance criteria
1888 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1889 THEN
1890 fnd_log.STRING (fnd_log.level_statement,
1891 g_pkg_name || '.' || l_api_name,
1892 ' SELECT threshold_amount, threshold_currency
1893 INTO l_threshold, l_threshold_currency
1894 FROM gcs_hierarchies_b
1895 WHERE hierarchy_id = '||p_hierarchy_id);
1896 END IF;
1897 SELECT threshold_amount, threshold_currency
1898 INTO l_threshold, l_threshold_currency
1899 FROM gcs_hierarchies_b
1900 WHERE hierarchy_id = p_hierarchy_id;
1901 BEGIN
1902 gcs_templates_pkg.get_dimension_template
1903 (p_hierarchy_id => p_hierarchy_id,
1904 p_template_code => 'SUSPENSE',
1905 p_balance_type_code => p_balance_type_code,
1906 p_template_record => l_temp_record
1907 );
1908 EXCEPTION
1909 WHEN OTHERS
1910 THEN
1911 RAISE no_suspense_template_error;
1912 END;
1913 gcs_templates_dynamic_pkg.balance (p_entry_id => x_entry_id,
1914 p_template => l_temp_record,
1915 p_bal_type_code => p_balance_type_code,
1916 p_hierarchy_id => p_hierarchy_id,
1917 p_entity_id => p_entity_id,
1918 p_threshold => l_threshold,
1919 p_threshold_currency_code => l_threshold_currency
1920 );
1921 SELECT DECODE(SUSPENSE_EXCEEDED_FLAG, 'Y', 'WARNING', gcs_utility_pkg.g_ret_sts_success)
1922 INTO retcode
1923 FROM gcs_entry_headers
1924 WHERE entry_id = x_entry_id;
1925 END IF;
1926 IF (x_prop_entry_id IS NOT NULL or x_stat_prop_entry_id IS NOT NULL)
1927 THEN
1928 SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
1929 NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
1930 INTO l_precision, l_stat_precision
1931 FROM fnd_currencies fc_1, fnd_currencies fc_stat
1932 WHERE fc_1.currency_code = p_source_currency_code
1933 AND fc_stat.currency_code = 'STAT';
1934 INSERT INTO gcs_entry_lines
1935 (entry_id,
1936 ptd_debit_balance_e,
1937 ptd_credit_balance_e,
1938 ytd_debit_balance_e,
1939 ytd_credit_balance_e,
1940 xtd_balance_e,
1941 ytd_balance_e,
1942 creation_date, created_by, last_update_date,
1943 last_updated_by, last_update_login
1944 )
1945 SELECT decode(entry_id, x_entry_id, x_prop_entry_id, x_stat_prop_entry_id),
1946
1947 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),
1948 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),
1949 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),
1950 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),
1951 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)
1952 - 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),
1953 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)
1954 - 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),
1955 creation_date, created_by, last_update_date,
1956 last_updated_by, last_update_login
1957 FROM gcs_entry_lines
1958 WHERE entry_id in ( x_entry_id, x_stat_entry_id);
1959 IF x_prop_entry_id IS NOT NULL THEN
1960 gcs_templates_dynamic_pkg.balance (p_entry_id => x_prop_entry_id,
1961 p_template => l_temp_record,
1962 p_bal_type_code => p_balance_type_code,
1963 p_hierarchy_id => p_hierarchy_id,
1964 p_entity_id => p_entity_id,
1965 p_threshold => l_threshold,
1966 p_threshold_currency_code => l_threshold_currency
1967 );
1968
1969 SELECT decode(SUSPENSE_EXCEEDED_FLAG, 'Y', 'WARNING', gcs_utility_pkg.g_ret_sts_success)
1970 INTO retcode
1971 FROM gcs_entry_headers
1972 WHERE entry_id = x_prop_entry_id;
1973 ELSE
1974 retcode := gcs_utility_pkg.g_ret_sts_success;
1975 END IF;
1976 END IF;
1977
1978 -- Check the GCS_SYSTEM_OPTIONS.INTERCO_MAP_ENABLED_FLAG.
1979 SELECT NVL(interco_map_enabled_flag,'N')
1980 INTO l_imap_enabled_flag
1981 FROM gcs_system_options;
1982 -- If enabled then update the above created entry to populate the correct intercompany values according to the line_item value
1983 IF l_imap_enabled_flag = 'Y'
1984 THEN
1985 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1986 THEN
1987 fnd_log.STRING (fnd_log.level_statement,
1988 g_pkg_name || '.' || l_api_name,
1989 ' UPDATE gcs_entry_lines gel
1990 SET gel.intercompany_id = ( SELECT intercompany_id
1991 FROM gcs_interco_map_dtls gimd
1992 WHERE gimd.line_item_id = gel.line_item_id
1993 )
1994 WHERE gel.entry_id IN( x_entry_id, x_stat_entry_id, x_prop_entry_id, x_stat_prop_entry_id)
1995 AND EXISTS ( SELECT intercompany_id
1996 FROM gcs_interco_map_dtls gimd
1997 WHERE gimd.line_item_id = gel.line_item_id
1998 );');
1999 END IF;
2000 UPDATE gcs_entry_lines gel
2001 SET gel.intercompany_id = ( SELECT intercompany_id
2002 FROM gcs_interco_map_dtls gimd
2003 WHERE gimd.line_item_id = gel.line_item_id
2004 )
2005 WHERE gel.entry_id IN( x_entry_id, x_stat_entry_id, x_prop_entry_id, x_stat_prop_entry_id)
2006 AND EXISTS ( SELECT intercompany_id
2007 FROM gcs_interco_map_dtls gimd
2008 WHERE gimd.line_item_id = gel.line_item_id
2009 );
2010
2011 END IF;
2012 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
2013 THEN
2014 fnd_log.STRING (fnd_log.level_procedure,
2015 g_pkg_name || '.' || l_api_name,
2016 gcs_utility_pkg.g_module_success
2017 || ' '
2018 || l_api_name
2019 || '()'
2020 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
2021 );
2022 END IF;
2023 EXCEPTION
2024 WHEN no_suspense_template_error THEN
2025 retcode := gcs_utility_pkg.g_ret_sts_error;
2026 FND_MESSAGE.set_name('GCS', 'GCS_DP_NO_SUSPENSE_ERR');
2027 errbuf := fnd_message.get;
2028 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
2029 THEN
2030 fnd_log.STRING (fnd_log.level_error,
2031 g_pkg_name || '.' || l_api_name,
2032 gcs_utility_pkg.g_module_failure
2033 || ' '
2034 || errbuf
2035 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
2036 );
2037 END IF;
2038 raise gcs_dp_proc_data_error;
2039 WHEN no_re_template_error THEN
2040 retcode := gcs_utility_pkg.g_ret_sts_error;
2041 FND_MESSAGE.set_name('GCS', 'GCS_DP_NO_RE_ERR');
2042 errbuf := fnd_message.get;
2043 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
2044 THEN
2045 fnd_log.STRING (fnd_log.level_error,
2046 g_pkg_name || '.' || l_api_name,
2047 gcs_utility_pkg.g_module_failure
2048 || ' '
2049 || errbuf
2050 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
2051 );
2052 END IF;
2053 raise gcs_dp_proc_data_error;
2054 WHEN init_mapping_error THEN
2055 raise gcs_dp_proc_data_error;
2056 WHEN no_data_error THEN
2057 retcode := gcs_utility_pkg.g_ret_sts_warn;
2058 FND_MESSAGE.set_name('GCS', 'GCS_DP_NO_DATA_ERR');
2059 errbuf := fnd_message.get;
2060 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
2061 fnd_log.STRING (fnd_log.level_error,
2062 g_pkg_name || '.' || l_api_name,
2063 gcs_utility_pkg.g_module_failure
2064 || ' '
2065 || errbuf
2066 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
2067 );
2068 END IF;
2069 raise gcs_dp_proc_data_error;
2070 END process_inc_data;
2071 END GCS_DP_DYNAMIC_PKG;
2072