[Home] [Help]
PACKAGE BODY: APPS.GCS_WEBADI_PKG
Source
1 PACKAGE BODY GCS_WEBADI_PKG AS
2 /* $Header: gcswebadib.pls 120.45 2008/02/05 14:28:44 hakumar noship $ */
3 --
4 -- PRIVATE GLOBAL VARIABLES
5 --
6
7 -- The API name
8 g_pkg_name CONSTANT VARCHAR2(30) := 'gcs.plsql.GCS_WEBADI_PKG';
9 -- dimension info from gcs_utility_pkg
10 g_dimension_attr_info gcs_utility_pkg.t_hash_dimension_attr_info := gcs_utility_pkg.g_dimension_attr_info;
11 g_gcs_dimension_info gcs_utility_pkg.t_hash_gcs_dimension_info := gcs_utility_pkg.g_gcs_dimension_info;
12 -- A newline character. Included for convenience when writing long strings.
13 g_nl VARCHAR2(1) := '
14 ';
15
16 --
17 -- Exceptions
18 --
19 level_program_error EXCEPTION;
20
21
22 PROCEDURE init_dimension_attrs IS
23 TYPE t_index_dimension_info IS TABLE OF r_dimension_info;
24 l_index_dimension_info t_index_dimension_info;
25 BEGIN
26 SELECT fdb.DIMENSION_VARCHAR_LABEL,
27 fxd.MEMBER_B_TABLE_NAME,
28 fxd.INTF_MEMBER_B_TABLE_NAME,
29 fxd.INTF_MEMBER_TL_TABLE_NAME,
30 fxd.INTF_ATTRIBUTE_TABLE_NAME,
31 fxd.HIERARCHY_TABLE_NAME || '_T',
32 fxd.MEMBER_DISPLAY_CODE_COL,
33 fxd.MEMBER_NAME_COL,
34 fdb.dimension_id,
35 fxd.LOADER_OBJECT_DEF_ID
36 BULK COLLECT INTO l_index_dimension_info
37 FROM fem_xdim_dimensions fxd, fem_dimensions_b fdb
38 WHERE fxd.dimension_id = fdb.dimension_id
39 AND fxd.member_col IN
40 ('COMPANY_COST_CENTER_ORG_ID', 'FINANCIAL_ELEM_ID', 'PRODUCT_ID',
41 'NATURAL_ACCOUNT_ID', 'CHANNEL_ID', 'LINE_ITEM_ID', 'PROJECT_ID',
42 'CUSTOMER_ID', 'TASK_ID', 'USER_DIM1_ID', 'USER_DIM2_ID',
43 'USER_DIM3_ID', 'USER_DIM4_ID', 'USER_DIM5_ID', 'USER_DIM6_ID',
44 'USER_DIM7_ID', 'USER_DIM8_ID', 'USER_DIM9_ID', 'USER_DIM10_ID',
45 'COMPANY_ID', 'COST_CENTER_ID');
46
47 IF l_index_dimension_info.FIRST IS NOT NULL THEN
48 FOR l_counter IN l_index_dimension_info.FIRST .. l_index_dimension_info.LAST LOOP
49 g_dimension_info(l_index_dimension_info(l_counter).dimension_varchar_label) := l_index_dimension_info(l_counter);
50 END LOOP;
51 END IF;
52
53
54 END init_dimension_attrs;
55 --
56 -- PUBLIC PROCEDURES
57 --
58 ---------------------------------------------------------------------------
59 /*
60 ** datasub_upload
61 */
62 -- Bugfix : 5690166 , added logic for uploading the Header info. to gcs_dat_sub_dtls
63 PROCEDURE datasub_upload(
64 p_load_id IN NUMBER,
65 p_load_name IN VARCHAR2,
66 p_entity_name IN VARCHAR2,
67 p_period IN VARCHAR2,
68 p_balance_type IN VARCHAR2,
69 p_load_method IN VARCHAR2,
70 p_currency_type IN VARCHAR2,
71 p_currency_code IN VARCHAR2,
72 p_amount_type IN VARCHAR2,
73 p_measure_type IN VARCHAR2,
74 p_rule_set IN VARCHAR2) IS
75 l_user_id NUMBER := fnd_global.user_id;
76 l_login_id NUMBER := fnd_global.login_id;
77 l_api_name CONSTANT VARCHAR2(30) := 'datasub_upload';
78
79 BEGIN
80
81 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
82 fnd_log.STRING(fnd_log.level_procedure,
83 g_pkg_name || '.' || l_api_name,
84 gcs_utility_pkg.g_module_enter || ' ' || l_api_name ||
85 '() p_load_id= ' || p_load_id ||
86 ' p_load_name= ' || p_load_name ||
87 ' p_entity_name= ' || p_entity_name ||
88 ' p_balance_type= ' || p_balance_type || ' ' ||
89 ' p_period= ' || p_period || ' ' ||
90 ' p_load_method= ' || p_load_method || ' ' ||
91 ' p_currency_type= ' || p_currency_type || ' ' ||
92 ' p_currency_code= ' || p_currency_code || ' ' ||
93 ' p_amount_type= ' || p_amount_type || ' ' ||
94 ' p_measure_type= ' || p_measure_type || ' ' ||
95 ' p_rule_set= ' || p_rule_set || ' ' ||
96 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
97 END IF;
98
99 -- Delete the row created by Data Submission OA UI
100 -- Bugfix 5736334: Added nvl for curreny_code as it can be null for transactional currency.
101 DELETE FROM
102 gcs_data_sub_dtls
103 WHERE
104 entity_id = p_entity_name
105 AND to_char(cal_period_id) = p_period
106 AND balance_type_code = p_balance_type
107 AND currency_type_code = p_currency_type
108 AND nvl(currency_code, 'NULL') = nvl(p_currency_code,'NULL')
109 AND most_recent_flag = 'X'
110 AND EXISTS (SELECT 'X'
111 FROM gcs_data_sub_dtls check_exists
112 WHERE check_exists.entity_id = p_entity_name
113 AND to_char(check_exists.cal_period_id) = p_period
114 AND check_exists.balance_type_code = p_balance_type
115 AND check_exists.currency_type_code = p_currency_type
116 AND nvl(check_exists.currency_code, 'NULL') = nvl(p_currency_code, 'NULL')
117 AND check_exists. most_recent_flag = 'X' );
118
119 -- Always create a new row with the Header data.
120
121 INSERT INTO gcs_data_sub_dtls
122 ( load_id,
123 load_name,
124 entity_id,
125 cal_period_id,
126 currency_code,
127 balance_type_code,
128 load_method_code,
129 currency_type_code,
130 amount_type_code,
131 measure_type_code,
132 rule_set_id,
133 notify_options_code,
134 notification_text,
135 creation_date,
136 created_by,
137 last_update_date,
138 last_updated_by,
139 last_update_login,
140 object_version_number,
141 start_time,
142 end_time,
143 status_code,
144 locked_flag,
145 most_recent_flag,
146 associated_request_id,
147 validation_rule_set_id,
148 balances_rule_id)
149 VALUES(
150 p_load_id,
151 p_load_name,
152 p_entity_name,
153 p_period,
154 p_currency_code,
155 p_balance_type,
156 p_load_method,
157 p_currency_type,
158 p_amount_type,
159 p_measure_type,
160 p_rule_set,
161 'N',
162 null,
163 sysdate,
164 l_user_id,
165 sysdate,
166 l_user_id,
167 l_login_id,
168 1,
169 sysdate,
170 null,
171 'IN_PROGRESS',
172 'N',
173 'X',
174 null,
175 null,
176 null);
177
178 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
179 fnd_log.STRING(fnd_log.level_procedure,
180 g_pkg_name || '.' || l_api_name,
181 gcs_utility_pkg.g_module_success || ' ' || l_api_name ||
182 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
183 END IF;
184
185
186 END;
187
188
189 /*
190 ** HRATE_Import
191 */
192 PROCEDURE hrate_import(p_hierarchy_id IN NUMBER,
193 p_entity_id IN NUMBER,
194 p_cal_period_id IN NUMBER) IS
195 l_event_name VARCHAR2(100) := 'oracle.apps.gcs.setup.historicalrates.update';
196 l_event_key VARCHAR2(100) := NULL;
197 l_parameter_list wf_parameter_list_t;
198 l_api_name VARCHAR2(30) := 'HRATE_IMPORT';
199 BEGIN
200 -- In case of an error, we will roll back to this point in time.
201 SAVEPOINT gcs_hrate_import_start;
202
203 FND_FILE.PUT_LINE(FND_FILE.LOG,
204 g_pkg_name || '.' || l_api_name || ' ENTER');
205 FND_FILE.NEW_LINE(FND_FILE.LOG);
206
207 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
208 fnd_log.STRING(fnd_log.level_procedure,
209 g_pkg_name || '.' || l_api_name,
210 gcs_utility_pkg.g_module_enter || ' ' || l_api_name ||
211 '() p_hierarchy_id= ' || p_hierarchy_id ||
212 ' p_entity_id= ' || p_entity_id ||
213 ' p_cal_period_id= ' || p_cal_period_id || ' ' ||
214 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
215 END IF;
216
217 -- Bug Fix : 5352902
218 /***
219 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
220 fnd_log.STRING(fnd_log.level_statement,
221 g_pkg_name || '.' || l_api_name,
222 'DELETE FROM gcs_historical_rates ' || g_nl ||
223 ' WHERE hierarchy_id = ' || p_hierarchy_id || g_nl ||
224 ' AND entity_id = ' || p_entity_id || g_nl ||
225 ' AND cal_period_id = ' || p_cal_period_id || g_nl ||
226 ' AND update_flag = ''N''');
227 END IF;
228
229 DELETE FROM gcs_historical_rates
230 WHERE hierarchy_id = p_hierarchy_id
231 AND entity_id = p_entity_id
232 AND cal_period_id = p_cal_period_id
233 AND update_flag = 'N';
234
235 ***/
236
237 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
238 fnd_log.STRING(fnd_log.level_statement,
239 g_pkg_name || '.' || l_api_name,
240 ' UPDATE gcs_historical_rates ghr set update_flag = ''N'', account_type_code = ' || g_nl ||
241 '( select dim_attribute_varchar_member from fem_ln_items_attr ' || g_nl ||
242 ' where attribute_id = ' ||
243 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
244 .attribute_id || g_nl || ' AND version_id = ' ||
245 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
246 .version_id || g_nl ||
247 ' and line_item_id = ghr.line_item_id)' || g_nl ||
248 ' WHERE hierarchy_id = ' || p_hierarchy_id || g_nl ||
249 ' AND entity_id = ' || p_entity_id || g_nl ||
250 ' AND cal_period_id = ' || p_cal_period_id);
251 END IF;
252
253 UPDATE gcs_historical_rates ghr
254 SET update_flag = 'N',
255 account_type_code = (select dim_attribute_varchar_member
256 from fem_ln_items_attr
257 where attribute_id =
258 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
259 .attribute_id
260 and version_id =
261 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
262 .version_id
263 and line_item_id = ghr.line_item_id)
264 WHERE hierarchy_id = p_hierarchy_id
265 AND entity_id = p_entity_id
266 AND cal_period_id = p_cal_period_id;
267
268 wf_event.addparametertolist(p_name => 'PERIOD_ID',
269 p_value => p_cal_period_id,
270 p_parameterlist => l_parameter_list);
271 wf_event.addparametertolist(p_name => 'HIERARCHY_ID',
272 p_value => p_hierarchy_id,
273 p_parameterlist => l_parameter_list);
274 wf_event.addparametertolist(p_name => 'ENTITY_ID',
275 p_value => p_entity_id,
276 p_parameterlist => l_parameter_list);
277 begin
278 FND_FILE.PUT_LINE(FND_FILE.LOG,
279 g_pkg_name || '.' || l_api_name ||
280 ' RAISE WF_EVENT');
281 FND_FILE.NEW_LINE(FND_FILE.LOG);
282 wf_event.RAISE(p_event_name => l_event_name,
283 p_event_key => l_event_key,
284 p_parameters => l_parameter_list);
285 EXCEPTION
286 WHEN OTHERS THEN
287 FND_FILE.PUT_LINE(FND_FILE.LOG,
288 g_pkg_name || '.' || l_api_name ||
289 ' WF_EVENT FAILED');
290 FND_FILE.NEW_LINE(FND_FILE.LOG);
291 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
292 fnd_log.STRING(fnd_log.level_error,
293 g_pkg_name || '.' || l_api_name,
294 ' wf_event.raise failed ' || ' ' ||
295 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
296 END IF;
297 END;
298
299 FND_FILE.PUT_LINE(FND_FILE.LOG,
300 g_pkg_name || '.' || l_api_name || ' EXIT');
301 FND_FILE.NEW_LINE(FND_FILE.LOG);
302
303 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
304 fnd_log.STRING(fnd_log.level_procedure,
305 g_pkg_name || '.' || l_api_name,
306 gcs_utility_pkg.g_module_success || ' ' || l_api_name ||
307 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
308 END IF;
309
310 EXCEPTION
311 WHEN OTHERS THEN
312 ROLLBACK TO gcs_hrate_import_start;
313
314 FND_FILE.PUT_LINE(FND_FILE.LOG,
315 g_pkg_name || '.' || l_api_name || ' ERROR : ' ||
316 SQLERRM);
317 FND_FILE.NEW_LINE(FND_FILE.LOG);
318
319 -- Write the appropriate information to the execution report
320 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
321 fnd_log.STRING(fnd_log.level_error,
322 g_pkg_name || '.' || l_api_name,
323 gcs_utility_pkg.g_module_failure || ' ' || SQLERRM || ' ' ||
324 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
325 END IF;
326 END hrate_import;
327
328 ---------------------------------------------------------------------------
329 ---------------------------------------------------------------------------
330 /*
331 ** Execute_Event
332 */
333 FUNCTION execute_event(p_subscription_guid IN RAW,
334 p_event IN OUT NOCOPY wf_event_t)
335 RETURN VARCHAR2 IS
336 TYPE dim_info_rec_type IS RECORD(
337 dim_col VARCHAR2(30),
338 dim_col_name VARCHAR2(30),
339 tl_table_name VARCHAR2(30));
340
341 TYPE index_dim_info_tbl_type IS TABLE OF dim_info_rec_type INDEX BY BINARY_INTEGER;
342
343 -- start bugfix: 5496678 - IF we have a record with all these parameters, bulk
344 -- update with forall will not work, so we will create multiple tables and
345 -- bulk-fetch into them.
346 TYPE l_interface_code_tbl_type IS TABLE OF BNE_INTERFACE_COLS_B.INTERFACE_CODE%TYPE INDEX BY BINARY_INTEGER;
347 TYPE l_sequence_num_tbl_type IS TABLE OF BNE_INTERFACE_COLS_B.SEQUENCE_NUM%TYPE INDEX BY BINARY_INTEGER;
348 TYPE l_display_name_tbl_type IS TABLE OF FEM_TAB_COLUMNS_TL.DISPLAY_NAME%TYPE INDEX BY BINARY_INTEGER;
349 TYPE l_language_tbl_type IS TABLE OF BNE_INTERFACE_COLS_TL.LANGUAGE%TYPE INDEX BY BINARY_INTEGER;
350
351 l_interface_code l_interface_code_tbl_type;
352 l_sequence_num l_sequence_num_tbl_type;
353 l_display_name l_display_name_tbl_type;
354 l_language l_language_tbl_type;
355 -- end bugbix: 5496678
356
357 l_index_dim_info index_dim_info_tbl_type;
358 l_query VARCHAR2(5000);
359 l_select_cols VARCHAR2(2500);
360 l_view_select_cols VARCHAR2(2500);
361 l_from_clause VARCHAR2(500);
362 l_where_clause VARCHAR2(2500);
363 l_index_column_name VARCHAR2(30);
364 l_user_id NUMBER := fnd_global.user_id;
365 l_login_id NUMBER := fnd_global.login_id;
366 l_app_id CONSTANT NUMBER(15) := 266;
367 body VARCHAR2(5000);
368 l_api_name CONSTANT VARCHAR2(30) := 'Execute_Event';
369
370 g_non_ds_cnt NUMBER := -1;
371 g_non_ds_req_dimensions DBMS_SQL.varchar2_table;
372
373 --- Bug Fix : 5707630, HRates Enhancemnent
374 l_hrate_select_cols VARCHAR2(2500);
375 l_hrate_view_select_cols VARCHAR2(2500);
376 l_hrate_from_clause VARCHAR2(500);
377 l_hrate_where_clause VARCHAR2(2500);
378
379 -- Bug fix: 5968398
380 l_hr_re_where_clause VARCHAR2(2500);
381 l_hr_dim_counter NUMBER := 0 ;
382 l_hrate_where_dim_clause VARCHAR2(2500);
383
384 l_hrate_drm_cnt NUMBER := -1;
385 l_hrate_drm_dimensions DBMS_SQL.varchar2_table;
386
387
388 BEGIN
389
390 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
391 fnd_log.STRING(fnd_log.level_procedure,
392 g_pkg_name || '.' || l_api_name,
393 gcs_utility_pkg.g_module_enter || ' ' || l_api_name ||
394 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
395 END IF;
396
397 gcs_utility_pkg.init_dimension_info;
398
399 -- Bugfix 5052607: Adding checks for dimension information count
400 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
401 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
402 'Active dimension count: ' || gcs_utility_pkg.g_gcs_dimension_info.COUNT);
403 END IF;
404
405 IF (gcs_utility_pkg.g_gcs_dimension_info.COUNT = 0) THEN
406 --We must skip the rest of the program if the active dimensions haven't been defined
407 return 'SUCCESS';
408 END IF;
409
410 -- start bugfix: 5496678 - Retrieve the display name for the dimensions to be
411 -- updated in the bne_interface_tl table.
412 -- bugfix 5655870: Added the special decode for company cost center for data sub interface.
413 -- Bug Fix : 6502423, Update bne_interface_cols_tl for HRate RE interface to show user display names
414
415 SELECT interface_code,
416 sequence_num,
417 display_name,
418 language
419 BULK COLLECT
420 INTO l_interface_code,
421 l_sequence_num,
422 l_display_name,
423 l_language
424 FROM (SELECT bicb.interface_code,
425 bicb.sequence_num,
426 ftctl.display_name,
427 ftctl.language
428 FROM fem_tab_columns_tl ftctl,
429 bne_interface_cols_b bicb,
430 fem_tab_columns_b ftcb
431 WHERE ftctl.table_name = 'FEM_BALANCES'
432 AND bicb.application_id = l_app_id
433 AND ftcb.table_name = ftctl.table_name
434 AND ftcb.column_name = ftctl.column_name
435 AND ftcb.fem_data_type_code = 'DIMENSION'
436 AND ((bicb.interface_code IN ('GCS_AD_TB_INTF',
437 'GCS_HRATE_INTF',
438 'GCS_ENTRY_LINES_INTF',
439 'GCS_AD_ENTRY_LINE_INTF',
440 'GCS_HRATE_RE_INTF')
441 AND ftctl.column_name = bicb.interface_col_name)
442 OR
443 (bicb.interface_code IN ('GCS_DATASUB_LINE_INTF',
444 'GCS_DATASUB_IDT_LINE_INTF')
445 AND bicb.interface_col_name =
446 decode(ftctl.column_name,
447 'COMPANY_COST_CENTER_ORG_ID', 'CCTR_ORG_DISPLAY_CODE',
448 SUBSTR(ftctl.column_name, 0, LENGTH(
449 ftctl.column_name) - 3) ||
450 '_DISPLAY_CODE'))));
451
452 -- update the table bne_interface_cols_tl's dimension display name.
453 IF (l_interface_code.COUNT <> 0) THEN
454 FORALL l_counter IN l_interface_code.FIRST .. l_interface_code.LAST
455 UPDATE bne_interface_cols_tl
456 SET prompt_left = l_display_name(l_counter),
457 prompt_above = l_display_name(l_counter),
458 --Bug Fix : 5563482
459 --last_update_date = SYSDATE,
460 last_update_login = l_login_id,
461 last_updated_by = l_user_id
462 WHERE application_id = l_app_id
463 AND interface_code = l_interface_code(l_counter)
464 AND language = l_language(l_counter)
465 AND sequence_num = l_sequence_num(l_counter);
466 END IF;
467 -- end bugbix: 5496678
468
469 -- initiate l_gcs_user_dim_info
470 SELECT * BULK COLLECT
471 INTO l_index_dim_info
472 FROM (SELECT fxd.member_col,
473 fxd.member_name_col,
474 fxd.MEMBER_TL_TABLE_NAME
475 FROM fem_xdim_dimensions fxd
476 WHERE fxd.member_col IN
477 ('COMPANY_COST_CENTER_ORG_ID', 'FINANCIAL_ELEM_ID',
478 'PRODUCT_ID', 'NATURAL_ACCOUNT_ID', 'CHANNEL_ID',
479 'LINE_ITEM_ID', 'PROJECT_ID', 'CUSTOMER_ID', 'TASK_ID',
480 'USER_DIM1_ID', 'USER_DIM2_ID', 'USER_DIM3_ID',
481 'USER_DIM4_ID', 'USER_DIM5_ID', 'USER_DIM6_ID',
482 'USER_DIM7_ID', 'USER_DIM8_ID', 'USER_DIM9_ID',
483 'USER_DIM10_ID')
484 UNION ALL
485 SELECT 'INTERCOMPANY_ID',
486 'INTERCOMPANY_NAME',
487 'FEM_CCTR_ORGS_TL'
488 FROM dual);
489
490 -- update bne_interface_cols_b table for visible dimensions
491 -- first hide all dimension columns
492 -- Bug fix 3809676: limit interface_col_name not to include "TRIALBALANCE_SEQ_NUM"
493 UPDATE bne_interface_cols_b
494 SET display_flag = 'N',
495 not_null_flag = 'N',
496 required_flag = 'N',
497 --Bug Fix : 5563482
498 --last_update_date = SYSDATE,
499 last_update_login = l_login_id
500 WHERE application_id = l_app_id
501 AND interface_code IN ('GCS_AD_TB_INTF', 'GCS_ENTRY_LINES_INTF',
502 'GCS_HRATE_INTF', 'GCS_HRATE_RE_INTF','GCS_AD_ENTRY_LINE_INTF')
503 AND interface_col_name IN
504 ('COMPANY_COST_CENTER_ORG_ID', 'FINANCIAL_ELEM_ID', 'PRODUCT_ID',
505 'NATURAL_ACCOUNT_ID', 'CHANNEL_ID', 'LINE_ITEM_ID', 'PROJECT_ID',
506 'CUSTOMER_ID', 'INTERCOMPANY_ID', 'TASK_ID', 'USER_DIM1_ID',
507 'USER_DIM2_ID', 'USER_DIM3_ID', 'USER_DIM4_ID', 'USER_DIM5_ID',
508 'USER_DIM6_ID', 'USER_DIM7_ID', 'USER_DIM8_ID', 'USER_DIM9_ID',
509 'USER_DIM10_ID');
510
511 UPDATE bne_interface_cols_b
512 SET display_flag = 'N',
513 not_null_flag = 'N',
514 required_flag = 'N',
515 --Bug Fix : 5563482
516 --last_update_date = SYSDATE,
517 last_update_login = l_login_id
518 WHERE application_id = l_app_id
519 AND interface_code = 'GCS_DATASUB_LINE_INTF'
520 AND interface_col_name LIKE '%_DISPLAY_CODE';
521
522
523 FOR l_counter IN l_index_dim_info.FIRST .. l_index_dim_info.LAST LOOP
524 l_index_column_name := l_index_dim_info(l_counter).dim_col;
525
526
527 --Ensure the column is required for FEM
528 IF (gcs_utility_pkg.Get_Fem_Dim_Required(l_index_column_name) = 'Y' and
529 l_index_column_name <> 'ENTITY_ID') THEN
530 -- then set the user-chosen dimensions as visible and not-null
531 UPDATE bne_interface_cols_b
532 SET display_flag = 'Y',
533 not_null_flag = 'Y',
534 required_flag = 'Y',
535 --Bug Fix : 5563482
536 --last_update_date = SYSDATE,
537 last_update_login = l_login_id
538 WHERE application_id = l_app_id
539 AND interface_code = 'GCS_DATASUB_LINE_INTF'
540 AND interface_col_name =
541 decode(l_index_column_name,
542 'COMPANY_COST_CENTER_ORG_ID',
543 'CCTR_ORG_DISPLAY_CODE',
544 SUBSTR(l_index_column_name,
545 0,
546 LENGTH(l_index_column_name) - 3) ||
547 '_DISPLAY_CODE');
548
549 END IF;
550
551
552 --Ensure the column is required for GCS II
553 IF (gcs_utility_pkg.get_dimension_required(l_index_column_name) = 'Y' and
554 l_index_column_name <> 'ENTITY_ID') THEN
555
556 --Code for DRM of Spread sheets other than Data Submission
557 g_non_ds_cnt := g_non_ds_cnt + 1;
558 g_non_ds_req_dimensions(g_non_ds_cnt) := l_index_column_name;
559
560
561 -- then set the user-chosen dimensions as visible and not-null
562 UPDATE bne_interface_cols_b
563 SET display_flag = 'Y',
564 not_null_flag = 'Y',
565 required_flag = 'Y',
566 --Bug Fix : 5563482
567 --last_update_date = SYSDATE,
568 last_update_login = l_login_id
569 WHERE application_id = l_app_id
570 AND interface_code in
571 ('GCS_AD_TB_INTF', 'GCS_HRATE_RE_INTF', 'GCS_ENTRY_LINES_INTF',
572 'GCS_AD_ENTRY_LINE_INTF')
573 AND interface_col_name = l_index_column_name;
574
575 l_view_select_cols := l_view_select_cols || ', ' ||
576 l_index_dim_info(l_counter).dim_col_name;
577
578 IF (l_index_column_name = 'INTERCOMPANY_ID') THEN
579 l_select_cols := l_select_cols ||
580 ', inter.company_cost_center_org_name intercompany_name';
581 l_from_clause := l_from_clause || ', fem_cctr_orgs_tl inter';
582 l_where_clause := l_where_clause ||
583 ' and tb.intercompany_id = inter.company_cost_center_org_id ' ||
584 ' and inter.language = USERENV(''LANG'')';
585 -- Bug fix: 5968398
586 l_hr_re_where_clause := l_hr_re_where_clause ||
587 ' and tb.intercompany_id = inter.company_cost_center_org_id ' ||
588 ' and inter.language = userenv(''LANG'')';
589
590 ELSIF (l_index_column_name = 'COMPANY_COST_CENTER_ORG_ID') THEN
591 l_select_cols := l_select_cols ||
592 ', fcot.company_cost_center_org_name ';
593 l_from_clause := l_from_clause || ', fem_cctr_orgs_tl fcot';
594 l_where_clause := l_where_clause ||
595 ' and tb.company_cost_center_org_id = fcot.company_cost_center_org_id ' ||
596 ' and fcot.language = userenv(''LANG'')';
597 -- Bug fix: 5968398
598 l_hr_re_where_clause := l_hr_re_where_clause ||
599 ' and tb.company_cost_center_org_id = fcot.company_cost_center_org_id ' ||
600 ' and fcot.language = userenv(''LANG'')';
601 ELSE
602 l_select_cols := l_select_cols || ', ' ||
603 l_index_dim_info(l_counter).dim_col_name;
604
605 l_from_clause := l_from_clause || ', ' ||
606 l_index_dim_info(l_counter).tl_table_name;
607 l_where_clause := l_where_clause || ' and tb.' ||
608 l_index_column_name || ' = ' ||
609 l_index_dim_info(l_counter)
610 .tl_table_name || '.' || l_index_column_name ||
611 ' and ' || l_index_dim_info(l_counter)
612 .tl_table_name ||
613 '.language = userenv(''LANG'')';
614
615 -- Bug fix: 5968398
616 l_hr_re_where_clause := l_hr_re_where_clause || ' and tb.' ||
617 l_index_column_name || ' = ' ||
618 l_index_dim_info(l_counter)
619 .tl_table_name || '.' || l_index_column_name ||
620 ' and ' || l_index_dim_info(l_counter)
621 .tl_table_name ||
622 '.language = userenv(''LANG'') ' ;
623
624 END IF;
625 END IF;
626
627
628 --- Bug Fix : 5707630, HRates Enhancement
629 --- Start of the fix : 5707630
630 IF (gcs_utility_pkg.get_Hrate_Dim_required(l_index_column_name) = 'Y' and
631 l_index_column_name <> 'ENTITY_ID') THEN
632
633 --Code for DRM of HIstorical Rates Spread sheet
634 l_hrate_drm_cnt := l_hrate_drm_cnt + 1;
635 l_hrate_drm_dimensions(l_hrate_drm_cnt) := l_index_column_name;
636
637
638 -- then set the user-chosen dimensions as visible and not-null
639 UPDATE bne_interface_cols_b
640 SET display_flag = 'Y',
641 not_null_flag = 'Y',
642 required_flag = 'Y',
643 last_update_login = l_login_id
644 WHERE application_id = l_app_id
645 AND interface_code = 'GCS_HRATE_INTF'
646 AND interface_col_name = l_index_column_name;
647
648 l_hrate_view_select_cols := l_hrate_view_select_cols || ', ' ||
649 l_index_dim_info(l_counter).dim_col_name;
650
651 IF (l_index_column_name = 'INTERCOMPANY_ID') THEN
652 l_hrate_select_cols := l_hrate_select_cols ||
653 ', inter.company_cost_center_org_name intercompany_name';
654 l_hrate_from_clause := l_hrate_from_clause || ', fem_cctr_orgs_tl inter';
655 l_hrate_where_clause := l_hrate_where_clause ||
656 ' and tb.intercompany_id = inter.company_cost_center_org_id ' ||
657 ' and inter.language = userenv(''LANG'')';
658 ELSIF (l_index_column_name = 'COMPANY_COST_CENTER_ORG_ID') THEN
659 l_hrate_select_cols := l_hrate_select_cols ||
660 ', fcot.company_cost_center_org_name ';
661 l_hrate_from_clause := l_hrate_from_clause || ', fem_cctr_orgs_tl fcot';
662 l_hrate_where_clause := l_hrate_where_clause ||
663 ' and tb.company_cost_center_org_id = fcot.company_cost_center_org_id ' ||
664 ' and fcot.language = userenv(''LANG'')';
665 ELSE
666 l_hrate_select_cols := l_hrate_select_cols || ', ' ||
667 l_index_dim_info(l_counter).dim_col_name;
668
669 l_hrate_from_clause := l_hrate_from_clause || ', ' ||
670 l_index_dim_info(l_counter).tl_table_name;
671
672 -- Bug fix: 5968398
673 l_hrate_where_clause := l_hrate_where_clause || ' and tb.' ||
674 l_index_column_name || ' = ' ||
675 l_index_dim_info(l_counter)
676 .tl_table_name || '.' || l_index_column_name ||
677 ' and ' || l_index_dim_info(l_counter)
678 .tl_table_name ||
679 '.language = userenv(''LANG'') ' ;
680
681 IF l_hr_dim_counter = 0 THEN
682 l_hrate_where_dim_clause := ' gdt.' ||
683 l_index_column_name || ' <> tb.' ||
684 l_index_column_name ;
685 l_hr_dim_counter := l_hr_dim_counter+1 ;
686 ELSE
687 l_hrate_where_dim_clause := l_hrate_where_dim_clause || ' OR gdt.' ||
688 l_index_column_name || ' <> tb.' ||
689 l_index_column_name ;
690
691 END IF;
692
693 END IF;
694 END IF;
695 --- End of the fix : 5707630
696
697 END LOOP;
698
699
700 -- Bugfix 5052607: Added additional debug information
701 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
702 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
703 'Select Columns: ' || l_select_cols);
704 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
705 'From Clause: ' || l_from_clause);
706 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
707 'Where Clause: ' || l_where_clause);
708 END IF;
709
710
711
712 --- Bug Fix : 5707630, HRates Enhancement
713 --- Start of the fix : 5707630
714
715 --- Code for DRM
716 DELETE
717 FROM
718 BNE_INTERFACE_KEY_COLS
719 WHERE APPLICATION_ID = 266
720 AND SEQUENCE_NUM > 9
721 AND INTERFACE_CODE IN ('GCS_AD_ENTRY_LINE_INTF',
722 'GCS_AD_TB_INTF','GCS_HRATE_INTF', 'GCS_HRATE_RE_INTF');
723
724 -- Bug Fix : 5679021
725 -- Key columns for Entry interface ('GCS_ENTRY_LINES_INTF') not needed, as DRM is dropped.
726
727
728 --- DRM for Historical Rates - Retained Earnings added
729 IF (g_non_ds_cnt >= 0) THEN
730 FORALL i IN g_non_ds_req_dimensions.FIRST .. g_non_ds_req_dimensions.LAST
731 INSERT INTO BNE_INTERFACE_KEY_COLS
732 (APPLICATION_ID,
733 KEY_CODE,
734 SEQUENCE_NUM,
735 OBJECT_VERSION_NUMBER,
736 INTERFACE_APP_ID,
737 INTERFACE_CODE,
738 INTERFACE_SEQ_NUM,
739 CREATED_BY,
740 CREATION_DATE,
741 LAST_UPDATED_BY,
742 LAST_UPDATE_LOGIN,
743 LAST_UPDATE_DATE)
744 SELECT
745 l_app_id,
746 decode(INTERFACE_CODE,
747 'GCS_AD_ENTRY_LINE_INTF',
748 'GCS_AD_ENTRY_KEY_CODE',
749 'GCS_AD_TB_INTF',
750 'GCS_AD_TB_KEY_CODE',
751 'GCS_HRATE_RE_INTF',
752 'GCS_HRATE_RE_KEY_CODE' ),
753 SEQUENCE_NUM+10,
754 1,
755 l_app_id,
756 INTERFACE_CODE,
757 SEQUENCE_NUM,
758 l_user_id,
759 CREATION_DATE,
760 l_user_id,
761 l_login_id,
762 --Bug Fix : 5563482
763 LAST_UPDATE_DATE
764 FROM bne_interface_cols_b
765 WHERE interface_col_name = g_non_ds_req_dimensions(i)
766 AND interface_code IN
767 ('GCS_AD_ENTRY_LINE_INTF', 'GCS_AD_TB_INTF',
768 'GCS_HRATE_RE_INTF' ); -- HRates Enhancement
769 END IF;
770
771
772 --- DRM for Historical Rates
773 IF (l_hrate_drm_cnt >= 0) THEN
774 FORALL i IN l_hrate_drm_dimensions.FIRST .. l_hrate_drm_dimensions.LAST
775 INSERT INTO BNE_INTERFACE_KEY_COLS
776 (APPLICATION_ID,
777 KEY_CODE,
778 SEQUENCE_NUM,
779 OBJECT_VERSION_NUMBER,
780 INTERFACE_APP_ID,
781 INTERFACE_CODE,
782 INTERFACE_SEQ_NUM,
783 CREATED_BY,
784 CREATION_DATE,
785 LAST_UPDATED_BY,
786 LAST_UPDATE_LOGIN,
787 LAST_UPDATE_DATE)
788 SELECT
789 l_app_id,
790 'GCS_HRATE_KEY_CODE',
791 SEQUENCE_NUM+5,
792 1,
793 l_app_id,
794 INTERFACE_CODE,
795 SEQUENCE_NUM,
796 l_user_id,
797 CREATION_DATE,
798 l_user_id,
799 l_login_id,
800 LAST_UPDATE_DATE
801 FROM bne_interface_cols_b
802 WHERE interface_col_name = l_hrate_drm_dimensions(i)
803 AND interface_code = 'GCS_HRATE_INTF' ;
804 END IF;
805
806 --- End of the fix : 5707630
807
808
809 l_query := 'SELECT ''Trial Balance'' template_type,flv2.meaning category_code, gat.transaction_date,ght.hierarchy_name,' ||
810 ' fet1.entity_name consolidation_entity_name,fct.NAME currency_code, ' ||
811 ' fet2.entity_name operating_entity_name,gat.ad_transaction_id, entry.entry_name recur_entry_name, ' ||
812 ' entry.description, gat.total_consideration consideration_amount, flv.meaning trial_balance_seq, ' ||
813 ' credit_amount, debit_amount' || l_view_select_cols ||
814 ' FROM fnd_lookup_values flv, gcs_ad_transactions gat, gcs_entry_headers entry, ' ||
815 ' fnd_lookup_values flv2, fem_entities_tl fet1, fem_entities_tl fet2, gcs_cons_relationships gcr, ' ||
816 ' gcs_entity_cons_attrs geca, gcs_hierarchies_tl ght, fnd_currencies_tl fct, ' ||
817 ' (SELECT tb.ad_transaction_id, credit_amount, debit_amount,
818 tb.trial_balance_seq ' || l_select_cols ||
819 ' FROM gcs_ad_trial_balances tb' || l_from_clause ||
820 ' WHERE ' || substr(l_where_clause, 5) || ') adtb' ||
821 ' WHERE adtb.ad_transaction_id(+) = gat.ad_transaction_id AND gat.assoc_entry_id = entry.entry_id(+) ' ||
822 ' AND nvl(gat.post_cons_relationship_id, gat.pre_cons_relationship_id) = gcr.cons_relationship_id AND fet1.entity_id = gcr.parent_entity_id ' ||
823 ' AND fet1.language = USERENV(''LANG'') AND fet2.language = USERENV(''LANG'') ' ||
824 ' AND ght.language = USERENV(''LANG'') AND fct.language = USERENV(''LANG'') ' ||
825 ' AND fet2.entity_id = gcr.child_entity_id AND geca.entity_id = gcr.parent_entity_id ' ||
826 ' AND geca.hierarchy_id = gcr.hierarchy_id AND gcr.hierarchy_id = ght.hierarchy_id ' ||
827 ' AND geca.currency_code = fct.currency_code AND gat.transaction_type_code = flv2.lookup_code ' ||
828 ' AND flv2.lookup_type = ''TRANSACTION_TYPE_CODE'' AND NVL (adtb.trial_balance_seq, 1) = flv.lookup_code ' ||
829 ' AND flv.lookup_type = ''GCS_TB_SEQUENCE'' AND flv.LANGUAGE = USERENV (''LANG'') AND flv2.LANGUAGE = USERENV (''LANG'') ' ||
830 ' AND flv.view_application_id = 266 AND flv2.view_application_id = 266 ';
831
832 body := ' CREATE OR REPLACE FORCE VIEW GCS_TB_WEBADI_VL AS ' || l_query;
833
834 -- Bugfix 5052607: Adding additional debug information
835 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
836 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
837 'View Definition for GCS_TB_WEBADI_VL');
838 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
839 body);
840
841 END IF;
842
843 ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
844 'GCS',
845 ad_ddl.create_view,
846 body,
847 'GCS_TB_WEBADI_VL');
848
849 l_query := 'SELECT template_type, category_code, transaction_date,hierarchy_name,' ||
850 ' consolidation_entity_name,currency_code, ' ||
851 ' operating_entity_name,ad_transaction_id, recur_entry_name, ' ||
852 ' description, consideration_amount, trial_balance_seq, ' ||
853 ' credit_amount, debit_amount' || l_view_select_cols ||
854 ' FROM gcs_tb_webadi_vl ' ||
855 ' WHERE ad_transaction_id = $param$.xns_id ';
856
857 -- update stored SQL
858 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
859 fnd_log.STRING(fnd_log.level_statement,
860 g_pkg_name || '.' || l_api_name,
861 'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
862 gcs_utility_pkg.g_nl ||
863 ' WHERE application_id=l_app_id AND content_code=''GCS_AD_TB_CNT''');
864 END IF;
865
866 UPDATE bne_stored_sql
867 SET QUERY = l_query
868 --Bug Fix : 5563482
869 --last_update_date = SYSDATE
870 WHERE application_id = l_app_id
871 AND content_code = 'GCS_AD_TB_CNT';
872
873 l_query := 'SELECT ''Manual Adjustment'' template_type, flv2.meaning category_code,' ||
874 ' gat.transaction_date, ght.hierarchy_name,' ||
875 ' fet1.entity_name consolidation_entity_name, fct.NAME currency_code,' ||
876 ' fet2.entity_name operating_entity_name, gat.ad_transaction_id,' ||
877 ' entry.entry_name recur_entry_name, entry.description,' ||
878 ' gat.total_consideration consideration_amount, ' ||
879 ' adtb.description lines_description, ' ||
880 ' credit_amount, debit_amount' || l_view_select_cols ||
881 ' FROM gcs_ad_transactions gat,gcs_entry_headers entry,' ||
882 ' fnd_lookup_values flv2, fem_entities_tl fet1,fem_entities_tl fet2,gcs_cons_relationships gcr,' ||
883 ' gcs_entity_cons_attrs geca, gcs_hierarchies_tl ght, fnd_currencies_tl fct,' ||
884 ' (SELECT tb.entry_id, ytd_credit_balance_e credit_amount,
885 ytd_debit_balance_e debit_amount, tb.description ' ||
886 l_select_cols || ' FROM gcs_entry_lines tb' || l_from_clause ||
887 ' WHERE NVL (tb.line_type_code, '' '') <> ''CALCULATED'' ' ||
888 l_where_clause || ') adtb ' ||
889 ' WHERE adtb.entry_id(+) = gat.assoc_entry_id AND gat.assoc_entry_id = entry.entry_id(+) ' ||
890 ' AND NVL (gat.post_cons_relationship_id, gat.pre_cons_relationship_id) = gcr.cons_relationship_id ' ||
891 ' AND fet1.entity_id = gcr.parent_entity_id AND fet2.entity_id = gcr.child_entity_id ' ||
892 ' AND fet1.language = USERENV(''LANG'') AND fet2.language = USERENV(''LANG'') ' ||
893 ' AND ght.language = USERENV(''LANG'') AND fct.language = USERENV(''LANG'') ' ||
894 ' AND geca.entity_id = gcr.parent_entity_id AND geca.hierarchy_id = gcr.hierarchy_id ' ||
895 ' AND gcr.hierarchy_id = ght.hierarchy_id AND geca.currency_code = fct.currency_code ' ||
896 ' AND gat.transaction_type_code = flv2.lookup_code AND flv2.lookup_type = ''TRANSACTION_TYPE_CODE'' ' ||
897 ' AND flv2.LANGUAGE = USERENV (''LANG'') AND flv2.view_application_id = 266 ';
898
899 body := ' CREATE OR REPLACE FORCE VIEW GCS_ADENTRY_WEBADI_VL AS ' ||
900 l_query;
901
902 -- Bugfix 5052607: Adding additional debug information
903 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
904 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
905 'View Definition for GCS_ADENTRY_WEBADI_VL');
906 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
907 body);
908
909 END IF;
910
911 ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
912 'GCS',
913 ad_ddl.create_view,
914 body,
915 'GCS_ADENTRY_WEBADI_VL');
916
917 l_query := 'SELECT template_type, category_code,' ||
918 ' transaction_date, hierarchy_name,' ||
919 ' consolidation_entity_name, currency_code,' ||
920 ' operating_entity_name, ad_transaction_id,' ||
921 ' recur_entry_name, description,' ||
922 ' consideration_amount, ' || ' lines_description,' ||
923 ' credit_amount, debit_amount' || l_view_select_cols ||
924 ' FROM gcs_adentry_webadi_vl ' ||
925 ' WHERE ad_transaction_id = $param$.xns_id ';
926 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
927 fnd_log.STRING(fnd_log.level_statement,
928 g_pkg_name || '.' || l_api_name,
929 'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
930 gcs_utility_pkg.g_nl ||
931 ' WHERE application_id=l_app_id AND content_code=''GCS_AD_ENTRY_CNT''');
932 END IF;
933
934 UPDATE bne_stored_sql
935 SET QUERY = l_query
936 --Bug Fix : 5563482
937 --last_update_date = SYSDATE
938 WHERE application_id = l_app_id
939 AND content_code = 'GCS_AD_ENTRY_CNT';
940
941 --- Bug Fix : 5707630, HRates Enhancement
942 --- Start of the fix : 5707630
943
944 l_query := ' SELECT hierarchy_name, entity_name, fct_from.NAME from_currency, ' ||
945 ' fct_to.NAME to_currency, translated_rate rate, ' ||
946 ' translated_amount amount, flv.meaning AS rate_type, period.cal_period_name period, ' ||
947 ' tb.hierarchy_id, tb.entity_id, tb.cal_period_id ' ||
948 l_select_cols ||
949 ' FROM gcs_dimension_templates gdt,gcs_hierarchies_tl ght, fnd_lookup_values flv, fem_entities_tl entity, ' ||
950 ' gcs_historical_rates tb, fnd_currencies_tl fct_from, fnd_currencies_tl fct_to, ' ||
951 ' fem_cal_periods_tl period ' || l_from_clause ||
952 ' WHERE gdt.hierarchy_id = tb.hierarchy_id AND gdt.template_code = ''RE''' ||
953 ' AND tb.hierarchy_id = ght.hierarchy_id AND tb.entity_id = entity.entity_id ' ||
954 ' AND tb.rate_type_code = flv.lookup_code AND flv.lookup_type = ''HISTORICAL_RATE_TYPE'' ' ||
955 ' AND flv.LANGUAGE = USERENV (''LANG'') and flv.view_application_id = 266 ' ||
956 ' AND fct_from.LANGUAGE = USERENV (''LANG'') and fct_to.LANGUAGE = USERENV (''LANG'') ' ||
957 ' AND ght.LANGUAGE = USERENV (''LANG'') and entity.LANGUAGE = USERENV (''LANG'') ' ||
958 ' AND tb.cal_period_id = period.cal_period_id AND period.language = USERENV(''LANG'')' ||
959 ' AND fct_to.currency_code = tb.to_currency AND fct_from.currency_code = tb.from_currency ' ||
960 ' AND fct_to.language = USERENV(''LANG'') AND fct_from.language = USERENV(''LANG'') ' ||
961 l_hr_re_where_clause ; -- Bug Fix - 5968398
962
963 body := ' CREATE OR REPLACE FORCE VIEW GCS_HR_RE_WEBADI_VL AS ' || l_query;
964
965 -- Bugfix 5052607: Adding additional debug information
966 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
967 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
968 'View Definition for GCS_HR_RE_WEBADI_VL');
969 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
970 body);
971
972 END IF;
973
974 ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
975 'GCS',
976 ad_ddl.create_view,
977 body,
978 'GCS_HR_RE_WEBADI_VL');
979
980 l_query := ' SELECT hierarchy_name, entity_name, from_currency, ' ||
981 ' to_currency, rate, ' || ' amount, rate_type, period ' ||
982 l_view_select_cols || ' FROM gcs_hr_re_webadi_vl tb ' ||
983 ' WHERE hierarchy_id = $param$.hierarchy_id AND entity_id = $param$.entity_id ' ||
984 ' AND cal_period_id = $param$.cal_period_id ';
985
986 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
987 fnd_log.STRING(fnd_log.level_statement,
988 g_pkg_name || '.' || l_api_name,
989 'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
990 gcs_utility_pkg.g_nl ||
991 ' WHERE application_id=l_app_id AND content_code=''GCS_HRATE_RE_CNT''');
992 END IF;
993
994 UPDATE bne_stored_sql
995 SET QUERY = l_query
996 WHERE application_id = l_app_id
997 AND content_code = 'GCS_HRATE_RE_CNT';
998
999 -- Historical Rates View
1000
1001 l_query := ' SELECT hierarchy_name, entity_name, fct_from.NAME from_currency, ' ||
1002 ' fct_to.NAME to_currency, translated_rate rate, ' ||
1003 ' translated_amount amount, flv.meaning AS rate_type, period.cal_period_name period, ' ||
1004 ' tb.hierarchy_id, tb.entity_id, tb.cal_period_id ' ||
1005 l_hrate_select_cols ||
1006 ' FROM gcs_dimension_templates gdt, gcs_hierarchies_tl ght, fnd_lookup_values flv, fem_entities_tl entity, ' ||
1007 ' gcs_historical_rates tb, fnd_currencies_tl fct_from, fnd_currencies_tl fct_to, ' ||
1008 ' fem_cal_periods_tl period ' || l_hrate_from_clause ||
1009 ' WHERE gdt.hierarchy_id = tb.hierarchy_id AND gdt.template_code = ''RE''' ||
1010 ' AND tb.hierarchy_id = ght.hierarchy_id AND tb.entity_id = entity.entity_id ' ||
1011 ' AND tb.rate_type_code = flv.lookup_code AND flv.lookup_type = ''HISTORICAL_RATE_TYPE'' ' ||
1012 ' AND flv.LANGUAGE = USERENV (''LANG'') and flv.view_application_id = 266 ' ||
1013 ' AND fct_from.LANGUAGE = USERENV (''LANG'') and fct_to.LANGUAGE = USERENV (''LANG'') ' ||
1014 ' AND ght.LANGUAGE = USERENV (''LANG'') and entity.LANGUAGE = USERENV (''LANG'') ' ||
1015 ' AND tb.cal_period_id = period.cal_period_id AND period.language = USERENV(''LANG'')' ||
1016 ' AND fct_to.currency_code = tb.to_currency AND fct_from.currency_code = tb.from_currency ' ||
1017 ' AND fct_to.language = USERENV(''LANG'') AND fct_from.language = USERENV(''LANG'') ' ||
1018 l_hrate_where_clause || ' AND ( '|| l_hrate_where_dim_clause || ' )';
1019
1020 body := ' CREATE OR REPLACE FORCE VIEW GCS_HR_WEBADI_VL AS ' || l_query;
1021
1022 -- Bugfix 5052607: Adding additional debug information
1023 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
1024 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
1025 'View Definition for GCS_HR_WEBADI_VL');
1026 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
1027 body);
1028
1029 END IF;
1030
1031 ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
1032 'GCS',
1033 ad_ddl.create_view,
1034 body,
1035 'GCS_HR_WEBADI_VL');
1036
1037 l_query := ' SELECT hierarchy_name, entity_name, from_currency, ' ||
1038 ' to_currency, rate, ' || ' amount, rate_type, period ' ||
1039 l_hrate_view_select_cols || ' FROM gcs_hr_webadi_vl tb ' ||
1040 ' WHERE hierarchy_id = $param$.hierarchy_id AND entity_id = $param$.entity_id ' ||
1041 ' AND cal_period_id = $param$.cal_period_id ';
1042
1043
1044 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
1045 fnd_log.STRING(fnd_log.level_statement,
1046 g_pkg_name || '.' || l_api_name,
1047 'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
1048 gcs_utility_pkg.g_nl ||
1049 ' WHERE application_id=l_app_id AND content_code=''GCS_HRATE_CNT''');
1050 END IF;
1051
1052 UPDATE bne_stored_sql
1053 SET QUERY = l_query
1054 WHERE application_id = l_app_id
1055 AND content_code = 'GCS_HRATE_CNT';
1056
1057 --- End of the fix : 5707630
1058
1059 l_query := ' SELECT hierarchy_name, gct.category_name as category_code,gdtctl.data_type_name as balance_type_code, ' ||
1060 ' entity_name, eh.description, fct.NAME currency_code, credit, ' ||
1061 ' debit, flv1.meaning as process_code, start_period.cal_period_name start_period, ' ||
1062 ' end_period.cal_period_name end_period, eh.entry_name, eh.entry_id ,adtb.ENTRY_LINES_DESCRIPTION ' ||
1063 l_view_select_cols ||
1064 ' FROM gcs_hierarchies_tl ght, fnd_lookup_values flv1, gcs_categories_tl gct, fem_entities_tl entity, ' ||
1065 ' gcs_entry_headers eh, fnd_currencies_tl fct, fem_cal_periods_tl start_period, '||
1066 ' gcs_data_type_codes_b gdtcb,gcs_data_type_codes_tl gdtctl, ' ||
1067 ' fem_cal_periods_tl end_period, ' ||
1068 ' (SELECT tb.entry_id, tb.description ENTRY_LINES_DESCRIPTION, ytd_credit_balance_e credit,
1069 ytd_debit_balance_e debit ' || l_select_cols ||
1070 ' FROM gcs_entry_lines tb' || l_from_clause ||
1071 ' WHERE NVL(tb.line_type_code, '' '') <> ''CALCULATED'' ' ||
1072 l_where_clause || ') adtb ' ||
1073 ' WHERE eh.hierarchy_id = ght.hierarchy_id AND eh.entity_id = entity.entity_id ' ||
1074 ' AND eh.process_code = flv1.lookup_code and flv1.lookup_type = ''GCS_ENTRY_PROCESS_CODE'' ' ||
1075 ' AND flv1.LANGUAGE = USERENV (''LANG'') AND eh.category_code = gct.category_code ' ||
1076 ' AND ght.LANGUAGE = USERENV (''LANG'') AND flv1.view_application_id = 266 ' ||
1077 ' AND entity.LANGUAGE = USERENV (''LANG'') AND fct.LANGUAGE = USERENV (''LANG'') ' ||
1078 ' AND start_period.LANGUAGE = USERENV (''LANG'') AND end_period.LANGUAGE (+)= USERENV (''LANG'') ' ||
1079 ' AND gct.LANGUAGE = USERENV (''LANG'') AND eh.start_cal_period_id = start_period.cal_period_id ' ||
1080 ' AND eh.end_cal_period_id = end_period.cal_period_id (+) AND eh.entry_id = adtb.entry_id (+)' ||
1081 ' AND fct.currency_code = eh.currency_code '||
1082 ' AND eh.balance_type_code = gdtcb.data_type_code '||
1083 ' AND gdtcb.data_type_id = gdtctl.data_type_id '||
1084 ' AND gdtctl.LANGUAGE = USERENV(''LANG'') ';
1085
1086 body := ' CREATE OR REPLACE FORCE VIEW GCS_ENTRY_WEBADI_VL AS ' ||
1087 l_query;
1088
1089 -- Bugfix 5052607: Adding additional debug information
1090 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
1091 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
1092 'View Definition for GCS_ENTRY_WEBADI_VL');
1093 fnd_log.STRING(fnd_log.level_statement, g_pkg_name || '.' || l_api_name,
1094 body);
1095
1096 END IF;
1097
1098 ad_ddl.do_ddl(GCS_DYNAMIC_UTIL_PKG.g_applsys_username,
1099 'GCS',
1100 ad_ddl.create_view,
1101 body,
1102 'GCS_ENTRY_WEBADI_VL');
1103
1104 l_query := ' SELECT hierarchy_name, category_code, balance_type_code, ' ||
1105 ' entity_name, description, currency_code, credit, ' ||
1106 ' debit, process_code, start_period, ' ||
1107 ' end_period, entry_name, entry_id, ENTRY_LINES_DESCRIPTION ' || l_view_select_cols ||
1108 ' FROM gcs_entry_webadi_vl tb ' ||
1109 ' WHERE tb.entry_id=$PARAM$.entry_id ';
1110
1111 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
1112 fnd_log.STRING(fnd_log.level_statement,
1113 g_pkg_name || '.' || l_api_name,
1114 'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
1115 gcs_utility_pkg.g_nl ||
1116 ' WHERE application_id=l_app_id AND content_code=''GCS_ENTRY_LINES_CNT''');
1117 END IF;
1118
1119 UPDATE bne_stored_sql
1120 SET QUERY = l_query
1121 --Bug Fix : 5563482
1122 --last_update_date = SYSDATE
1123 WHERE application_id = l_app_id
1124 AND content_code = 'GCS_ENTRY_LINES_CNT';
1125
1126 COMMIT;
1127
1128 -- Write the appropriate information to the execution report
1129 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1130 fnd_log.STRING(fnd_log.level_procedure,
1131 g_pkg_name || '.' || l_api_name,
1132 gcs_utility_pkg.g_module_success || ' ' || l_api_name ||
1133 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1134 END IF;
1135
1136 RETURN 'SUCCESS';
1137 EXCEPTION
1138 WHEN OTHERS THEN
1139 ROLLBACK;
1140 --Bugfix 5052607: Commented calling WF_CORE APIs and setting message name
1141 --fnd_message.set_name('GCS', 'GCS_AD_TB_UNEXPECTED_ERR');
1142
1143 -- Write the appropriate information to the execution report
1144 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
1145 fnd_log.STRING(fnd_log.level_error,
1146 g_pkg_name || '.' || l_api_name,
1147 gcs_utility_pkg.g_module_failure || ' ' ||
1148 l_api_name || '() ' ||
1149 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1150 fnd_log.STRING(fnd_log.level_error,
1151 g_pkg_name || '.' || l_api_name,
1152 SQLERRM);
1153 END IF;
1154
1155 --wf_core.CONTEXT(g_pkg_name,
1156 -- l_api_name,
1157 -- p_event.geteventname(),
1158 -- p_subscription_guid);
1159 --wf_event.seterrorinfo(p_event, 'ERROR');
1160 RETURN 'ERROR';
1161 END execute_event;
1162
1163
1164 --
1165 -- Procedure
1166 -- Dim_Member_Import
1167 -- Purpose
1168 -- An API to import dimension members from Web ADI
1169 -- Arguments
1170 -- Notes
1171 --
1172 PROCEDURE dim_member_import(x_errbuf OUT NOCOPY VARCHAR2,
1173 x_retcode OUT NOCOPY VARCHAR2,
1174 p_sequence_num IN NUMBER,
1175 p_dimension_varchar_label IN VARCHAR2) IS
1176
1177 l_attribute_id_list DBMS_SQL.varchar2_table;
1178 l_member_display_code_list DBMS_SQL.varchar2_table;
1179 l_member_b_table_list DBMS_SQL.varchar2_table;
1180 l_member_col_list DBMS_SQL.varchar2_table;
1181 l_default_assign_list DBMS_SQL.varchar2_table;
1182 l_attr_varchar_list DBMS_SQL.varchar2_table;
1183
1184 -- Bug Fix : 5232709 , Variables for holding the _tl, _attr table names of the upload dimension
1185 l_member_tl_table_name VARCHAR2(30);
1186 l_member_attr_table_name VARCHAR2(30);
1187
1188 l_status_code VARCHAR2(1);
1189
1190 l_api_name VARCHAR2(30) := 'DIM_MEMBER_IMPORT';
1191 BEGIN
1192 SAVEPOINT dm_import_start;
1193
1194 FND_FILE.NEW_LINE(FND_FILE.LOG);
1195 FND_FILE.PUT_LINE(FND_FILE.LOG, '<<<< Beginning Dimension Member Load >>>>');
1196 FND_FILE.NEW_LINE(FND_FILE.LOG);
1197 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Beginning of Parameters');
1198 FND_FILE.NEW_LINE(FND_FILE.LOG);
1199 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sequence Number: ' || p_sequence_num);
1200 FND_FILE.NEW_LINE(FND_FILE.LOG);
1201 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Dimension Varchar Label: ' || p_dimension_varchar_label);
1202 FND_FILE.NEW_LINE(FND_FILE.LOG);
1203 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End of Parameters');
1204 FND_FILE.NEW_LINE(FND_FILE.LOG);
1205
1206
1207 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1208 fnd_log.STRING(fnd_log.level_procedure,
1209 g_pkg_name || '.' || l_api_name,
1210 gcs_utility_pkg.g_module_enter || ' p_sequence_num = ' ||
1211 p_sequence_num || ', p_dimension_varchar_label = ' ||
1212 p_dimension_varchar_label || ' ' ||
1213 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1214 END IF;
1215
1216 -- Bug Fix : 5116449, Inserted dimension_group_display_code to the FEM dimension interface tables
1217
1218 IF (p_dimension_varchar_label = 'FINANCIAL_ELEMENT') THEN
1219 EXECUTE IMMEDIATE
1220 'INSERT INTO ' ||
1221 g_dimension_info(p_dimension_varchar_label).b_t_table_name || ' (' ||
1222 g_dimension_info(p_dimension_varchar_label).display_code || ',
1223 value_set_display_code,
1224 status )
1225 SELECT display_code,
1226 value_set_display_code,
1227 ''LOAD''
1228 FROM gcs_dimension_members_t
1229 WHERE sequence_num = :1 '
1230 USING p_sequence_num;
1231 ELSE
1232 EXECUTE IMMEDIATE
1233 'INSERT INTO ' ||
1234 g_dimension_info(p_dimension_varchar_label).b_t_table_name || ' (' ||
1235 g_dimension_info(p_dimension_varchar_label).display_code || ',
1236 value_set_display_code,
1237 status,
1238 dimension_group_display_code)
1239 SELECT display_code,
1240 value_set_display_code,
1241 ''LOAD'',
1242 dimension_group_display_code
1243 FROM gcs_dimension_members_t
1244 WHERE sequence_num = :1 '
1245 USING p_sequence_num;
1246 END IF;
1247
1248 EXECUTE IMMEDIATE
1249 'INSERT INTO ' ||
1250 g_dimension_info(p_dimension_varchar_label).tl_t_table_name || ' (' ||
1251 g_dimension_info(p_dimension_varchar_label).display_code || ',
1252 value_set_display_code,
1253 language, ' ||
1254 g_dimension_info(p_dimension_varchar_label).name || ',
1255 description,
1256 status)
1257 SELECT display_code,
1258 value_set_display_code,
1259 USERENV(''LANG''),
1260 name,
1261 description,
1262 ''LOAD''
1263 FROM gcs_dimension_members_t
1264 WHERE sequence_num = :1 '
1265 USING p_sequence_num;
1266
1267 SELECT fdab.attribute_id, fxd.member_display_code_col, fxd.member_b_table_name,
1268 fxd.member_col, fdab.default_assignment, fdab.attribute_varchar_label
1269 BULK COLLECT INTO l_attribute_id_list, l_member_display_code_list, l_member_b_table_list,
1270 l_member_col_list, l_default_assign_list, l_attr_varchar_list
1271 FROM fem_xdim_dimensions fxd, fem_dim_attributes_b fdab
1272 WHERE fxd.dimension_id (+)= fdab.attribute_dimension_id
1273 AND fdab.dimension_id = g_dimension_info(p_dimension_varchar_label).dimension_id
1274 AND fdab.attribute_required_flag = 'Y';
1275
1276 FOR i IN l_member_col_list.FIRST .. l_member_col_list.LAST LOOP
1277 IF ( l_member_col_list(i) IS NOT NULL) THEN
1278 EXECUTE IMMEDIATE
1279 'SELECT ' || l_member_display_code_list(i) || '
1280 FROM ' || l_member_b_table_list(i) || '
1281 WHERE ' || l_member_col_list(i) || ' = :1 '
1282 INTO l_default_assign_list(i)
1283 USING l_default_assign_list(i);
1284 END IF;
1285 END LOOP;
1286
1287 FORALL i IN l_default_assign_list.FIRST .. l_default_assign_list.LAST
1288 EXECUTE IMMEDIATE
1289 ' INSERT INTO ' ||
1290 g_dimension_info(p_dimension_varchar_label).attr_t_table_name || ' (' ||
1291 g_dimension_info(p_dimension_varchar_label).display_code || ',
1292 value_set_display_code,
1293 attribute_varchar_label,
1294 attribute_assign_value,
1295 attr_assign_vs_display_code,
1296 version_display_code ,
1297 status)
1298 SELECT gdmt.display_code,
1299 gdmt.value_set_display_code,
1300 :1,
1301 DECODE(:2, ''EXTENDED_ACCOUNT_TYPE'',
1302 gdmt.ext_account_type_code, :3),
1303 NULL,
1304 fdavb.version_display_code,
1305 ''LOAD''
1306 FROM gcs_dimension_members_t gdmt,
1307 fem_dim_attr_versions_b fdavb
1308 WHERE fdavb.default_version_flag = ''Y''
1309 AND fdavb.attribute_id = :4
1310 AND gdmt.sequence_num = :5 '
1311 USING l_attr_varchar_list(i),
1312 l_attr_varchar_list(i),
1313 l_default_assign_list(i),
1314 l_attribute_id_list(i),
1315 p_sequence_num;
1316
1317 IF (p_dimension_varchar_label = 'COMPANY_COST_CENTER_ORG') THEN
1318
1319 INSERT INTO fem_cctr_orgs_attr_t
1320 (cctr_org_display_code,
1321 value_set_display_code,
1322 attribute_varchar_label,
1323 attribute_assign_value,
1324 attr_assign_vs_display_code,
1325 version_display_code,
1326 status)
1327 SELECT display_code,
1328 value_set_display_code,
1329 fdab.attribute_varchar_label,
1330 cost_center_display_code,
1331 cost_center_vs_display_code,
1332 fdavb.version_display_code,
1333 'LOAD'
1334 FROM gcs_dimension_members_t ,
1335 fem_dim_attr_versions_b fdavb,
1336 fem_dim_attributes_b fdab
1337 WHERE fdavb.default_version_flag = 'Y'
1338 AND fdavb.attribute_id = fdab.attribute_id
1339 AND fdab.attribute_varchar_label = 'COST_CENTER'
1340 AND fdab.dimension_id = 8
1341 AND sequence_num = p_sequence_num
1342 AND cost_center_display_code is not null;
1343
1344 INSERT INTO fem_cctr_orgs_attr_t
1345 (cctr_org_display_code,
1346 value_set_display_code,
1347 attribute_varchar_label,
1348 attribute_assign_value,
1349 attr_assign_vs_display_code,
1350 version_display_code,
1351 status)
1352 SELECT display_code,
1353 value_set_display_code,
1354 fdab.attribute_varchar_label,
1355 company_display_code,
1356 company_vs_display_code,
1357 fdavb.version_display_code,
1358 'LOAD'
1359 FROM gcs_dimension_members_t ,
1360 fem_dim_attr_versions_b fdavb,
1361 fem_dim_attributes_b fdab
1362 WHERE fdavb.default_version_flag = 'Y'
1363 AND fdavb.attribute_id = fdab.attribute_id
1364 AND fdab.attribute_varchar_label = 'COMPANY'
1365 AND fdab.dimension_id = 8
1366 AND sequence_num = p_sequence_num
1367 AND company_display_code is not null;
1368
1369 END IF;
1370
1371
1372 FND_FILE.PUT_LINE(FND_FILE.LOG,'Executing EPF Loader');
1373 FND_FILE.NEW_LINE(FND_FILE.LOG);
1374
1375 FEM_DIM_MEMBER_LOADER_PKG.Main(errbuf => x_errbuf,
1376 retcode => x_retcode,
1377 p_execution_mode => 'S',
1378 p_dimension_id => g_dimension_info(p_dimension_varchar_label)
1379 .dimension_id);
1380
1381 SELECT status_code
1382 INTO l_status_code
1383 FROM Fnd_Concurrent_Requests
1384 WHERE request_id = FND_GLOBAL.conc_request_id;
1385
1386
1387 IF (l_status_code = 'E') THEN
1388
1389 -- Bug Fix : 5232709 , Start
1390 -- Retreive the _tl, _attr table names of the upload dimension and display the error message
1391
1392 SELECT fxd.member_tl_table_name ,
1393 fxd.attribute_table_name
1394 INTO l_member_tl_table_name,
1395 l_member_attr_table_name
1396 FROM fem_xdim_dimensions fxd
1397 WHERE fxd.dimension_id = g_dimension_info(p_dimension_varchar_label).dimension_id ;
1398
1399 FND_MESSAGE.set_name( 'GCS', 'GCS_DM_IMPORT_FEM_LDR_ERR' );
1400 FND_MESSAGE.set_token( 'DIM_B_TABLE' , g_dimension_info(p_dimension_varchar_label).b_table_name );
1401 FND_MESSAGE.set_token( 'DIM_TL_TABLE' , l_member_tl_table_name);
1402 FND_MESSAGE.set_token( 'DIM_ATTR_TABLE', l_member_attr_table_name );
1403
1404 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<<< Beginning of Error >>>>');
1405 FND_FILE.NEW_LINE(FND_FILE.LOG);
1406 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get );
1407 FND_FILE.NEW_LINE(FND_FILE.LOG);
1408 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<<< End of Error >>>>');
1409 FND_FILE.NEW_LINE(FND_FILE.LOG);
1410
1411 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1412 fnd_log.STRING(fnd_log.level_procedure,
1413 g_pkg_name || '.' || l_api_name, FND_MESSAGE.get );
1414 END IF;
1415
1416 ELSE
1417
1418 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<<<< Dimension Member Load completed successfully >>>>>> ');
1419 FND_FILE.NEW_LINE(FND_FILE.LOG);
1420 -- Bug Fix : 5232709 , End
1421
1422 END IF ;
1423
1424 EXCEPTION
1425
1426 -- Bug Fix : 5232709 , Start
1427 -- Catch the Unique Constraint Validation exception on the interface tables and display the error message.
1428 WHEN DUP_VAL_ON_INDEX THEN
1429 ROLLBACK TO dm_import_start;
1430 FND_MESSAGE.set_name( 'GCS', 'GCS_DM_IMPORT_DUP_VAL_ERR' );
1431 FND_MESSAGE.set_token( 'DIM_B_TABLE' , g_dimension_info(p_dimension_varchar_label).b_t_table_name );
1432 FND_MESSAGE.set_token( 'DIM_TL_TABLE' , g_dimension_info(p_dimension_varchar_label).tl_t_table_name );
1433 FND_MESSAGE.set_token( 'DIM_ATTR_TABLE' ,g_dimension_info(p_dimension_varchar_label).attr_t_table_name );
1434
1435 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<<< Beginning of Error >>>>');
1436 FND_FILE.NEW_LINE(FND_FILE.LOG);
1437 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get );
1438 FND_FILE.NEW_LINE(FND_FILE.LOG);
1439 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<<< End of Error >>>>');
1440 FND_FILE.NEW_LINE(FND_FILE.LOG);
1441
1442 x_errbuf := SQLERRM;
1443 x_retcode := '2';
1444
1445 -- delete submitted data for this run
1446 DELETE FROM gcs_dimension_members_t
1447 WHERE sequence_num = p_sequence_num;
1448
1449
1450 -- Write the appropriate information to the execution report
1451 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
1452 fnd_log.STRING(fnd_log.level_error,
1453 g_pkg_name || '.' || l_api_name,
1454 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf || ' ' ||
1455 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1456 END IF;
1457
1458 -- Bug Fix : 5232709 , End
1459
1460 WHEN OTHERS THEN
1461 ROLLBACK TO dm_import_start;
1462
1463 x_errbuf := SQLERRM;
1464 x_retcode := '2';
1465
1466 -- delete submitted data for this run
1467 DELETE FROM gcs_dimension_members_t
1468 WHERE sequence_num = p_sequence_num;
1469
1470 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<<< Beginning of Error >>>>');
1471 FND_FILE.NEW_LINE(FND_FILE.LOG);
1472 FND_FILE.PUT_LINE(FND_FILE.LOG, x_errbuf );
1473 FND_FILE.NEW_LINE(FND_FILE.LOG);
1474 FND_FILE.PUT_LINE(FND_FILE.LOG,'<<<< End of Error >>>>');
1475 FND_FILE.NEW_LINE(FND_FILE.LOG);
1476
1477 -- Write the appropriate information to the execution report
1478 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
1479 fnd_log.STRING(fnd_log.level_error,
1480 g_pkg_name || '.' || l_api_name,
1481 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf || ' ' ||
1482 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1483 END IF;
1484 END dim_member_import;
1485
1486 ---*************************************************************-----
1487
1488 --
1489 -- Procedure
1490 -- DIM_HIER_IMPORT
1491 -- Purpose
1492 -- An API to import dimension hierarchies from Web ADI
1493 -- Arguments
1494 -- Notes
1495 --
1496 PROCEDURE dim_hier_import(x_errbuf OUT NOCOPY VARCHAR2,
1497 x_retcode OUT NOCOPY VARCHAR2,
1498 p_sequence_num IN NUMBER,
1499 p_dimension_varchar_label IN VARCHAR2,
1500 p_hierarchy_name IN VARCHAR2,
1501 p_version_name IN VARCHAR2,
1502 p_version_start_dt IN VARCHAR2,
1503 p_version_end_dt IN VARCHAR2,
1504 p_analysis_flag IN VARCHAR2,
1505 p_parent_vs_display_code IN VARCHAR2,
1506 p_mvs_flag IN VARCHAR2) IS
1507 l_err_parent_display_code DBMS_SQL.varchar2_table;
1508 l_err_child_display_code DBMS_SQL.varchar2_table;
1509 l_err_parent_vs_display_code DBMS_SQL.varchar2_table;
1510 l_err_child_vs_display_code DBMS_SQL.varchar2_table;
1511 l_err_status DBMS_SQL.varchar2_table;
1512
1513 l_level_exists_flag VARCHAR2(1);
1514 l_status_code VARCHAR2(1);
1515 l_folder_name VARCHAR2(150);
1516 l_statement VARCHAR2(1000);
1517 l_user_id NUMBER := fnd_global.user_id;
1518 l_login_id NUMBER := fnd_global.login_id;
1519 l_api_name VARCHAR2(30) := 'DIM_HIER_IMPORT';
1520
1521 --Bugfix 4665921: Added support for causing impact when value set map is uploaded
1522 l_object_id NUMBER;
1523 l_dimension_id NUMBER(15);
1524 l_consolidation_vs_id NUMBER;
1525 l_effective_start_date DATE;
1526 l_effective_end_date DATE;
1527 --Bugfix 4924074 : Date Fromat error
1528 p_version_start_date DATE;
1529 p_version_end_date DATE;
1530
1531 BEGIN
1532 SAVEPOINT dh_import_start;
1533
1534 p_version_start_date := FND_CONC_DATE.STRING_TO_DATE(p_version_start_dt);
1535 p_version_end_date := FND_CONC_DATE.STRING_TO_DATE(p_version_end_dt);
1536
1537 FND_FILE.NEW_LINE(FND_FILE.LOG);
1538 FND_FILE.PUT_LINE(FND_FILE.LOG,
1539 g_pkg_name || '.' || l_api_name ||
1540 ' ENTER : p_sequence_num = ' || p_sequence_num ||
1541 ', p_dimension_varchar_label = ' ||
1542 p_dimension_varchar_label || ', p_hierarchy_name = ' ||
1543 p_hierarchy_name || ', p_version_name = ' ||
1544 p_version_name || ', p_version_start_date = ' ||
1545 p_version_start_date || ', p_version_end_date = ' ||
1546 p_version_end_date || ', p_analysis_flag = ' ||
1547 p_analysis_flag || ', p_mvs_flag = ' || p_mvs_flag ||
1548 ', p_parent_vs_display_code = ' ||
1549 p_parent_vs_display_code);
1550
1551 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1552 fnd_log.STRING(fnd_log.level_procedure,
1553 g_pkg_name || '.' || l_api_name,
1554 gcs_utility_pkg.g_module_enter ||
1555 ' p_sequence_num = ' || p_sequence_num ||
1556 ', p_dimension_varchar_label = ' ||
1557 p_dimension_varchar_label || ', p_hierarchy_name = ' ||
1558 p_hierarchy_name || ', p_version_name = ' ||
1559 p_version_name || ', p_version_start_date = ' ||
1560 p_version_start_date || ', p_version_end_date = ' ||
1561 p_version_end_date || ', p_analysis_flag = ' ||
1562 p_analysis_flag || ', p_mvs_flag = ' || p_mvs_flag ||
1563 ', p_parent_vs_display_code = ' ||
1564 p_parent_vs_display_code);
1565 END IF;
1566
1567 IF (p_mvs_flag = 'Y') THEN
1568 -- add root nodes
1569 INSERT INTO gcs_hier_members_t
1570 (sequence_num,
1571 parent_vs_display_code,
1572 parent_display_code,
1573 child_vs_display_code,
1574 child_display_code,
1575 object_version_number,
1576 creation_date,
1577 created_by,
1578 last_update_date,
1579 last_updated_by,
1580 last_update_login)
1581 SELECT DISTINCT p_sequence_num,
1582 parent_vs_display_code,
1583 parent_display_code,
1584 parent_vs_display_code,
1585 parent_display_code,
1586 1,
1587 SYSDATE,
1588 l_user_id,
1589 SYSDATE,
1590 l_user_id,
1591 l_login_id
1592 FROM gcs_hier_members_t
1593 WHERE sequence_num = p_sequence_num;
1594
1595 END IF;
1596
1597 SELECT folder_name
1598 INTO l_folder_name
1599 FROM fem_folders_tl
1600 WHERE language = userenv('LANG')
1601 AND folder_id = 1100;
1602
1603 INSERT INTO fem_hierarchies_t
1604 (hierarchy_object_name,
1605 folder_name,
1606 language,
1607 dimension_varchar_label,
1608 hierarchy_type_code,
1609 group_sequence_enforced_code,
1610 multi_top_flag,
1611 multi_value_set_flag,
1612 hierarchy_usage_code,
1613 flattened_rows_flag,
1614 status,
1615 hier_obj_def_display_name,
1616 effective_start_date,
1617 effective_end_date,
1618 calendar_display_code)
1619 VALUES
1620 (p_hierarchy_name,
1621 l_folder_name,
1622 USERENV('LANG'),
1623 p_dimension_varchar_label,
1624 'OPEN',
1625 decode(p_analysis_flag,
1626 'Y',
1627 'SEQUENCE_ENFORCED_SKIP_LEVEL',
1628 'NO_GROUPS'),
1629 'Y',
1630 p_mvs_flag,
1631 'STANDARD',
1632 decode(p_mvs_flag, 'Y', 'N', 'Y'),
1633 'LOAD',
1634 p_version_name,
1635 p_version_start_date,
1636 nvl(p_version_end_date, p_version_start_date + 365 * 20),
1637 null);
1638
1639 DELETE FROM fem_hier_value_sets_t
1640 WHERE hierarchy_object_name = p_hierarchy_name;
1641
1642 INSERT INTO fem_hier_value_sets_t
1643 (hierarchy_object_name, value_set_display_code, language, status)
1644 SELECT DISTINCT p_hierarchy_name,
1645 child_vs_display_code,
1646 USERENV('LANG'),
1647 'LOAD'
1648 FROM gcs_hier_members_t
1649 WHERE sequence_num = p_sequence_num;
1650
1651 BEGIN
1652 INSERT INTO fem_hier_value_sets_t
1653 (hierarchy_object_name, value_set_display_code, language, status)
1654 SELECT DISTINCT p_hierarchy_name,
1655 parent_vs_display_code,
1656 USERENV('LANG'),
1657 'LOAD'
1658 FROM gcs_hier_members_t
1659 WHERE sequence_num = p_sequence_num;
1660 EXCEPTION
1661 WHEN OTHERS THEN
1662 NULL;
1663 END;
1664
1665 l_statement := 'INSERT INTO ' ||
1666 g_dimension_info(p_dimension_varchar_label)
1667 .hier_t_table_name || ' (
1668 hierarchy_object_name,
1669 hierarchy_obj_def_display_name,
1670 parent_display_code,
1671 parent_value_set_display_code,
1672 child_display_code,
1673 child_value_set_display_code,
1674 display_order_num,
1675 weighting_pct,
1676 status,
1677 language)
1678 SELECT :1,
1679 :2,
1680 NVL(parent_display_code, child_display_code),
1681 parent_vs_display_code,
1682 child_display_code,
1683 child_vs_display_code,
1684 rownum, -- bugfix : 5411156
1685 NULL,
1686 ''LOAD'',
1687 USERENV(''LANG'')
1688 FROM gcs_hier_members_t
1689 WHERE sequence_num = :3 ';
1690
1691 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement THEN
1692 fnd_log.STRING(fnd_log.level_statement,
1693 g_pkg_name || '.' || l_api_name,
1694 ' l_statement = ' || l_statement);
1695 END IF;
1696
1697 EXECUTE IMMEDIATE l_statement
1698 USING p_hierarchy_name, p_version_name, p_sequence_num;
1699
1700 FND_FILE.PUT_LINE(FND_FILE.LOG,
1701 g_pkg_name || '.' || l_api_name ||
1702 ' calling FEM_HIER_LOADER_PKG ');
1703 FND_FILE.NEW_LINE(FND_FILE.LOG);
1704
1705 fem_hier_loader_pkg.Main(errbuf => x_errbuf,
1706 retcode => x_retcode,
1707 p_execution_mode => 'S',
1708 p_object_definition_id => g_dimension_info(p_dimension_varchar_label)
1709 .obj_defn_id,
1710 p_dimension_varchar_label => p_dimension_varchar_label,
1711 p_hierarchy_object_name => p_hierarchy_name,
1712 p_hier_obj_def_display_name => p_version_name);
1713
1714 SELECT status_code
1715 INTO l_status_code
1716 FROM Fnd_Concurrent_Requests
1717 WHERE request_id = FND_GLOBAL.conc_request_id;
1718
1719 FND_FILE.PUT_LINE(FND_FILE.LOG,
1720 g_pkg_name || '.' || l_api_name ||
1721 ' FEM_HIER_LOADER_PKG return status : ' ||
1722 l_status_code);
1723 FND_FILE.NEW_LINE(FND_FILE.LOG);
1724
1725 IF (l_status_code = 'E') THEN
1726 FND_FILE.PUT_LINE(FND_FILE.LOG,
1727 g_pkg_name || '.' || l_api_name ||
1728 ' FEM_HIER_LOADER_PKG failed');
1729 FND_FILE.NEW_LINE(FND_FILE.LOG);
1730
1731 EXECUTE IMMEDIATE 'SELECT dim_table.parent_display_code, dim_table.child_display_code, ' ||
1732 ' dim_table.parent_value_set_display_code, dim_table.child_value_set_display_code, dim_table.status ' ||
1733 ' FROM ' ||
1734 g_dimension_info(p_dimension_varchar_label)
1735 .hier_t_table_name ||
1736 ' dim_table, gcs_hier_members_t intf_table' ||
1737 ' WHERE intf_table.parent_display_code = dim_table.parent_display_code ' ||
1738 ' AND intf_table.child_display_code = dim_table.child_display_code ' ||
1739 ' AND intf_table.sequence_num = :1 ' BULK COLLECT
1740 INTO l_err_parent_display_code, l_err_child_display_code, l_err_parent_vs_display_code, l_err_child_vs_display_code, l_err_status
1741 USING p_sequence_num;
1742
1743 IF l_err_parent_display_code.FIRST IS NOT NULL THEN
1744 FOR i IN l_err_parent_display_code.FIRST .. l_err_parent_display_code.LAST LOOP
1745 FND_FILE.PUT_LINE(FND_FILE.LOG,
1746 ' Errored parent : ' ||
1747 l_err_parent_display_code(i) ||
1748 '; Errored parent value set : ' ||
1749 l_err_parent_vs_display_code(i) ||
1750 '; Errored child : ' ||
1751 l_err_child_display_code(i) ||
1752 '; Errored child value set : ' ||
1753 l_err_child_vs_display_code(i) ||
1754 '; Errored cause : ' || l_err_status(i));
1755 FND_FILE.NEW_LINE(FND_FILE.LOG);
1756 END LOOP;
1757
1758 DELETE FROM fem_hierarchies_t
1759 WHERE hierarchy_object_name = p_hierarchy_name
1760 AND hier_obj_def_display_name = p_version_name;
1761
1762 DELETE FROM fem_hier_value_sets_t
1763 WHERE hierarchy_object_name = p_hierarchy_name;
1764
1765 FORALL i IN l_err_parent_display_code.FIRST .. l_err_parent_display_code.LAST
1766 EXECUTE IMMEDIATE
1767 'DELETE FROM ' ||
1768 g_dimension_info(p_dimension_varchar_label).hier_t_table_name || '
1769 WHERE parent_display_code =:1
1770 AND child_display_code = :2
1771 AND parent_value_set_display_code = :3
1772 AND child_value_set_display_code = :4
1773 AND hierarchy_object_name = :5
1774 AND hierarchy_obj_def_display_name = :6'
1775 USING
1776 l_err_parent_display_code(i),
1777 l_err_child_display_code(i),
1778 l_err_parent_vs_display_code(i),
1779 l_err_child_vs_display_code(i),
1780 p_hierarchy_name,
1781 p_version_name
1782 ;
1783
1784 END IF;
1785
1786 ELSIF (p_mvs_flag = 'Y') THEN
1787
1788 --Bugfix 4665921: Added support for causing impact when value set map is uploaded
1789 SELECT foct.object_id,
1790 fh.dimension_id,
1791 fgvcd.value_set_id,
1792 fodb.effective_start_date,
1793 fodb.effective_end_date
1794 INTO l_object_id,
1795 l_dimension_id,
1796 l_consolidation_vs_id,
1797 l_effective_start_date,
1798 l_effective_end_date
1799 FROM fem_object_catalog_tl foct,
1800 fem_object_definition_b fodb,
1801 fem_object_definition_tl fodt,
1802 fem_hierarchies fh,
1803 fem_global_vs_combo_defs fgvcd,
1804 gcs_system_options gso
1805 WHERE foct.language = USERENV('LANG')
1806 AND fodb.object_definition_id = fodt.object_definition_id
1807 AND foct.object_name = p_hierarchy_name
1808 AND foct.object_id = fodt.object_id
1809 AND fodt.display_name = p_version_name
1810 AND fodt.language = USERENV('LANG')
1811 AND foct.object_id = fh.hierarchy_obj_id
1812 AND gso.fch_global_vs_combo_id = fgvcd.global_vs_combo_id
1813 AND fgvcd.dimension_id = fh.dimension_id;
1814
1815 UPDATE fem_xdim_dimensions fxd
1816 SET default_mvs_hierarchy_obj_id = l_object_id
1817 WHERE dimension_id = l_dimension_id;
1818
1819 gcs_cons_impact_analysis_pkg.value_set_map_updated( p_dimension_id => l_dimension_id,
1820 p_eff_start_date => l_effective_start_date,
1821 p_eff_end_date => l_effective_end_date,
1822 p_consolidation_vs_id => l_consolidation_vs_id);
1823
1824 END IF;
1825
1826 -- delete submitted data for this run
1827 DELETE FROM gcs_hier_members_t WHERE sequence_num = p_sequence_num;
1828
1829 FND_FILE.PUT_LINE(FND_FILE.LOG,
1830 g_pkg_name || '.' || l_api_name || ' EXIT');
1831 FND_FILE.NEW_LINE(FND_FILE.LOG);
1832
1833 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1834 fnd_log.STRING(fnd_log.level_procedure,
1835 g_pkg_name || '.' || l_api_name,
1836 gcs_utility_pkg.g_module_success || ' ' || l_api_name ||
1837 '() ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1838 END IF;
1839
1840 EXCEPTION
1841 WHEN level_program_error THEN
1842 ROLLBACK TO dh_import_start;
1843
1844 x_retcode := '2';
1845
1846 DELETE FROM gcs_hier_members_t WHERE sequence_num = p_sequence_num;
1847
1848 FND_FILE.PUT_LINE(FND_FILE.LOG,
1849 g_pkg_name || '.' || l_api_name || ' ERROR : ' ||
1850 x_errbuf);
1851 FND_FILE.NEW_LINE(FND_FILE.LOG);
1852 FND_FILE.PUT_LINE(FND_FILE.LOG,
1853 'Conflicting levels exist for some members');
1854 FND_FILE.NEW_LINE(FND_FILE.LOG);
1855
1856 WHEN OTHERS THEN
1857 ROLLBACK TO dh_import_start;
1858
1859 x_errbuf := SQLERRM;
1860 x_retcode := '2';
1861
1862 DELETE FROM gcs_hier_members_t WHERE sequence_num = p_sequence_num;
1863
1864 FND_FILE.PUT_LINE(FND_FILE.LOG,
1865 g_pkg_name || '.' || l_api_name || ' ERROR : ' ||
1866 x_errbuf);
1867 FND_FILE.NEW_LINE(FND_FILE.LOG);
1868
1869 -- Write the appropriate information to the execution report
1870 IF fnd_log.g_current_runtime_level <= fnd_log.level_error THEN
1871 fnd_log.STRING(fnd_log.level_error,
1872 g_pkg_name || '.' || l_api_name,
1873 gcs_utility_pkg.g_module_failure || ' ' || x_errbuf || ' ' ||
1874 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1875 END IF;
1876 END dim_hier_import;
1877
1878 --
1879 -- Procedure
1880 -- dim_hier_upload
1881 -- Purpose
1882 -- An API to upload dimension hierarchies header info from Web ADI
1883 -- Arguments
1884 -- Notes
1885 --
1886 PROCEDURE dim_hier_upload(p_dimension_varchar_label IN VARCHAR2,
1887 p_hierarchy_name IN VARCHAR2,
1888 p_version_name IN VARCHAR2,
1889 p_version_start_date IN VARCHAR2,
1890 p_version_end_date IN VARCHAR2,
1891 p_analysis_flag IN VARCHAR2,
1892 p_mvs_flag IN VARCHAR2) IS
1893 BEGIN
1894 NULL;
1895 END;
1896
1897 --
1898 -- Procedure
1899 -- handle_interco_map_flag
1900 -- Purpose
1901 -- An API to set the value for the GCS_SYSTEM_OPTIONS.INTERCO_MAP_ENABLED_FLAG
1902 -- Arguments
1903 -- Notes
1904 --
1905 PROCEDURE handle_interco_map_flag IS
1906 l_cnt NUMBER ;
1907 l_api_name VARCHAR2(30) := 'handle_interco_map_flag';
1908 BEGIN
1909 FND_FILE.NEW_LINE(FND_FILE.LOG);
1910 FND_FILE.PUT_LINE(FND_FILE.LOG,
1911 g_pkg_name || '.' || l_api_name );
1912 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1913 fnd_log.STRING(fnd_log.level_procedure,
1914 g_pkg_name || '.' || l_api_name ,'Begin');
1915 END IF;
1916 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1917 fnd_log.STRING(fnd_log.level_procedure,
1918 g_pkg_name || '.' || l_api_name ,
1919 ' SELECT count(*)
1920 INTO l_cnt
1921 FROM gcs_interco_map_dtls; ');
1922 END IF;
1923 SELECT count(*)
1924 INTO l_cnt
1925 FROM gcs_interco_map_dtls;
1926
1927 IF l_cnt > 0 THEN
1928 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1929 fnd_log.STRING(fnd_log.level_procedure,
1930 g_pkg_name || '.' || l_api_name ,
1931 ' UPDATE GCS_SYSTEM_OPTIONS
1932 SET INTERCO_MAP_ENABLED_FLAG = ''Y''; ');
1933 END IF;
1934 UPDATE GCS_SYSTEM_OPTIONS
1935 SET INTERCO_MAP_ENABLED_FLAG = 'Y';
1936
1937 ELSE
1938 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure THEN
1939 fnd_log.STRING(fnd_log.level_procedure,
1940 g_pkg_name || '.' || l_api_name ,
1941 ' UPDATE GCS_SYSTEM_OPTIONS
1942 SET INTERCO_MAP_ENABLED_FLAG = ''N''; ');
1943 END IF;
1944 UPDATE GCS_SYSTEM_OPTIONS
1945 SET INTERCO_MAP_ENABLED_FLAG = 'N';
1946
1947 END IF;
1948 COMMIT;
1949 END handle_interco_map_flag ;
1950
1951 BEGIN
1952
1953 init_dimension_attrs();
1954
1955 END gcs_webadi_pkg;
1956