[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fdb.DIMENSION_VARCHAR_LABEL,
fxd.MEMBER_B_TABLE_NAME,
fxd.INTF_MEMBER_B_TABLE_NAME,
fxd.INTF_MEMBER_TL_TABLE_NAME,
fxd.INTF_ATTRIBUTE_TABLE_NAME,
fxd.HIERARCHY_TABLE_NAME || '_T',
fxd.MEMBER_DISPLAY_CODE_COL,
fxd.MEMBER_NAME_COL,
fdb.dimension_id,
fxd.LOADER_OBJECT_DEF_ID
BULK COLLECT INTO l_index_dimension_info
FROM fem_xdim_dimensions fxd, fem_dimensions_b fdb
WHERE fxd.dimension_id = fdb.dimension_id
AND fxd.member_col IN
('COMPANY_COST_CENTER_ORG_ID', 'FINANCIAL_ELEM_ID', 'PRODUCT_ID',
'NATURAL_ACCOUNT_ID', 'CHANNEL_ID', 'LINE_ITEM_ID', 'PROJECT_ID',
'CUSTOMER_ID', 'TASK_ID', 'USER_DIM1_ID', 'USER_DIM2_ID',
'USER_DIM3_ID', 'USER_DIM4_ID', 'USER_DIM5_ID', 'USER_DIM6_ID',
'USER_DIM7_ID', 'USER_DIM8_ID', 'USER_DIM9_ID', 'USER_DIM10_ID',
'COMPANY_ID', 'COST_CENTER_ID');
DELETE FROM
gcs_data_sub_dtls
WHERE
entity_id = p_entity_name
AND to_char(cal_period_id) = p_period
AND balance_type_code = p_balance_type
AND currency_type_code = p_currency_type
AND nvl(currency_code, 'NULL') = nvl(p_currency_code,'NULL')
AND most_recent_flag = 'X'
AND EXISTS (SELECT 'X'
FROM gcs_data_sub_dtls check_exists
WHERE check_exists.entity_id = p_entity_name
AND to_char(check_exists.cal_period_id) = p_period
AND check_exists.balance_type_code = p_balance_type
AND check_exists.currency_type_code = p_currency_type
AND nvl(check_exists.currency_code, 'NULL') = nvl(p_currency_code, 'NULL')
AND check_exists. most_recent_flag = 'X' );
INSERT INTO gcs_data_sub_dtls
( load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
rule_set_id,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
end_time,
status_code,
locked_flag,
most_recent_flag,
associated_request_id,
validation_rule_set_id,
balances_rule_id)
VALUES(
p_load_id,
p_load_name,
p_entity_name,
p_period,
p_currency_code,
p_balance_type,
p_load_method,
p_currency_type,
p_amount_type,
p_measure_type,
p_rule_set,
'N',
null,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
1,
sysdate,
null,
'IN_PROGRESS',
'N',
'X',
null,
null,
null);
l_event_name VARCHAR2(100) := 'oracle.apps.gcs.setup.historicalrates.update';
'DELETE FROM gcs_historical_rates ' || g_nl ||
' WHERE hierarchy_id = ' || p_hierarchy_id || g_nl ||
' AND entity_id = ' || p_entity_id || g_nl ||
' AND cal_period_id = ' || p_cal_period_id || g_nl ||
' AND update_flag = ''N''');
DELETE FROM gcs_historical_rates
WHERE hierarchy_id = p_hierarchy_id
AND entity_id = p_entity_id
AND cal_period_id = p_cal_period_id
AND update_flag = 'N';
' UPDATE gcs_historical_rates ghr set update_flag = ''N'', account_type_code = ' || g_nl ||
'( select dim_attribute_varchar_member from fem_ln_items_attr ' || g_nl ||
' where attribute_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
.attribute_id || g_nl || ' AND version_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
.version_id || g_nl ||
' and line_item_id = ghr.line_item_id)' || g_nl ||
' WHERE hierarchy_id = ' || p_hierarchy_id || g_nl ||
' AND entity_id = ' || p_entity_id || g_nl ||
' AND cal_period_id = ' || p_cal_period_id);
UPDATE gcs_historical_rates ghr
SET update_flag = 'N',
account_type_code = (select dim_attribute_varchar_member
from fem_ln_items_attr
where attribute_id =
gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
.attribute_id
and version_id =
gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
.version_id
and line_item_id = ghr.line_item_id)
WHERE hierarchy_id = p_hierarchy_id
AND entity_id = p_entity_id
AND cal_period_id = p_cal_period_id;
l_select_cols VARCHAR2(2500);
l_view_select_cols VARCHAR2(2500);
l_hrate_select_cols VARCHAR2(2500);
l_hrate_view_select_cols VARCHAR2(2500);
SELECT interface_code,
sequence_num,
display_name,
language
BULK COLLECT
INTO l_interface_code,
l_sequence_num,
l_display_name,
l_language
FROM (SELECT bicb.interface_code,
bicb.sequence_num,
ftctl.display_name,
ftctl.language
FROM fem_tab_columns_tl ftctl,
bne_interface_cols_b bicb,
fem_tab_columns_b ftcb
WHERE ftctl.table_name = 'FEM_BALANCES'
AND bicb.application_id = l_app_id
AND ftcb.table_name = ftctl.table_name
AND ftcb.column_name = ftctl.column_name
AND ftcb.fem_data_type_code = 'DIMENSION'
AND ((bicb.interface_code IN ('GCS_AD_TB_INTF',
'GCS_HRATE_INTF',
'GCS_ENTRY_LINES_INTF',
'GCS_AD_ENTRY_LINE_INTF',
'GCS_HRATE_RE_INTF')
AND ftctl.column_name = bicb.interface_col_name)
OR
(bicb.interface_code IN ('GCS_DATASUB_LINE_INTF',
'GCS_DATASUB_IDT_LINE_INTF')
AND bicb.interface_col_name =
decode(ftctl.column_name,
'COMPANY_COST_CENTER_ORG_ID', 'CCTR_ORG_DISPLAY_CODE',
SUBSTR(ftctl.column_name, 0, LENGTH(
ftctl.column_name) - 3) ||
'_DISPLAY_CODE'))));
UPDATE bne_interface_cols_tl
SET prompt_left = l_display_name(l_counter),
prompt_above = l_display_name(l_counter),
--Bug Fix : 5563482
--last_update_date = SYSDATE,
last_update_login = l_login_id,
last_updated_by = l_user_id
WHERE application_id = l_app_id
AND interface_code = l_interface_code(l_counter)
AND language = l_language(l_counter)
AND sequence_num = l_sequence_num(l_counter);
SELECT * BULK COLLECT
INTO l_index_dim_info
FROM (SELECT fxd.member_col,
fxd.member_name_col,
fxd.MEMBER_TL_TABLE_NAME
FROM fem_xdim_dimensions fxd
WHERE fxd.member_col IN
('COMPANY_COST_CENTER_ORG_ID', 'FINANCIAL_ELEM_ID',
'PRODUCT_ID', 'NATURAL_ACCOUNT_ID', 'CHANNEL_ID',
'LINE_ITEM_ID', 'PROJECT_ID', 'CUSTOMER_ID', 'TASK_ID',
'USER_DIM1_ID', 'USER_DIM2_ID', 'USER_DIM3_ID',
'USER_DIM4_ID', 'USER_DIM5_ID', 'USER_DIM6_ID',
'USER_DIM7_ID', 'USER_DIM8_ID', 'USER_DIM9_ID',
'USER_DIM10_ID')
UNION ALL
SELECT 'INTERCOMPANY_ID',
'INTERCOMPANY_NAME',
'FEM_CCTR_ORGS_TL'
FROM dual);
UPDATE bne_interface_cols_b
SET display_flag = 'N',
not_null_flag = 'N',
required_flag = 'N',
--Bug Fix : 5563482
--last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE application_id = l_app_id
AND interface_code IN ('GCS_AD_TB_INTF', 'GCS_ENTRY_LINES_INTF',
'GCS_HRATE_INTF', 'GCS_HRATE_RE_INTF','GCS_AD_ENTRY_LINE_INTF')
AND interface_col_name IN
('COMPANY_COST_CENTER_ORG_ID', 'FINANCIAL_ELEM_ID', 'PRODUCT_ID',
'NATURAL_ACCOUNT_ID', 'CHANNEL_ID', 'LINE_ITEM_ID', 'PROJECT_ID',
'CUSTOMER_ID', 'INTERCOMPANY_ID', 'TASK_ID', 'USER_DIM1_ID',
'USER_DIM2_ID', 'USER_DIM3_ID', 'USER_DIM4_ID', 'USER_DIM5_ID',
'USER_DIM6_ID', 'USER_DIM7_ID', 'USER_DIM8_ID', 'USER_DIM9_ID',
'USER_DIM10_ID');
UPDATE bne_interface_cols_b
SET display_flag = 'N',
not_null_flag = 'N',
required_flag = 'N',
--Bug Fix : 5563482
--last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE application_id = l_app_id
AND interface_code = 'GCS_DATASUB_LINE_INTF'
AND interface_col_name LIKE '%_DISPLAY_CODE';
UPDATE bne_interface_cols_b
SET display_flag = 'Y',
not_null_flag = 'Y',
required_flag = 'Y',
--Bug Fix : 5563482
--last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE application_id = l_app_id
AND interface_code = 'GCS_DATASUB_LINE_INTF'
AND interface_col_name =
decode(l_index_column_name,
'COMPANY_COST_CENTER_ORG_ID',
'CCTR_ORG_DISPLAY_CODE',
SUBSTR(l_index_column_name,
0,
LENGTH(l_index_column_name) - 3) ||
'_DISPLAY_CODE');
UPDATE bne_interface_cols_b
SET display_flag = 'Y',
not_null_flag = 'Y',
required_flag = 'Y',
--Bug Fix : 5563482
--last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE application_id = l_app_id
AND interface_code in
('GCS_AD_TB_INTF', 'GCS_HRATE_RE_INTF', 'GCS_ENTRY_LINES_INTF',
'GCS_AD_ENTRY_LINE_INTF')
AND interface_col_name = l_index_column_name;
l_view_select_cols := l_view_select_cols || ', ' ||
l_index_dim_info(l_counter).dim_col_name;
l_select_cols := l_select_cols ||
', inter.company_cost_center_org_name intercompany_name';
l_select_cols := l_select_cols ||
', fcot.company_cost_center_org_name ';
l_select_cols := l_select_cols || ', ' ||
l_index_dim_info(l_counter).dim_col_name;
UPDATE bne_interface_cols_b
SET display_flag = 'Y',
not_null_flag = 'Y',
required_flag = 'Y',
last_update_login = l_login_id
WHERE application_id = l_app_id
AND interface_code = 'GCS_HRATE_INTF'
AND interface_col_name = l_index_column_name;
l_hrate_view_select_cols := l_hrate_view_select_cols || ', ' ||
l_index_dim_info(l_counter).dim_col_name;
l_hrate_select_cols := l_hrate_select_cols ||
', inter.company_cost_center_org_name intercompany_name';
l_hrate_select_cols := l_hrate_select_cols ||
', fcot.company_cost_center_org_name ';
l_hrate_select_cols := l_hrate_select_cols || ', ' ||
l_index_dim_info(l_counter).dim_col_name;
'Select Columns: ' || l_select_cols);
DELETE
FROM
BNE_INTERFACE_KEY_COLS
WHERE APPLICATION_ID = 266
AND SEQUENCE_NUM > 9
AND INTERFACE_CODE IN ('GCS_AD_ENTRY_LINE_INTF',
'GCS_AD_TB_INTF','GCS_HRATE_INTF', 'GCS_HRATE_RE_INTF');
INSERT INTO BNE_INTERFACE_KEY_COLS
(APPLICATION_ID,
KEY_CODE,
SEQUENCE_NUM,
OBJECT_VERSION_NUMBER,
INTERFACE_APP_ID,
INTERFACE_CODE,
INTERFACE_SEQ_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT
l_app_id,
decode(INTERFACE_CODE,
'GCS_AD_ENTRY_LINE_INTF',
'GCS_AD_ENTRY_KEY_CODE',
'GCS_AD_TB_INTF',
'GCS_AD_TB_KEY_CODE',
'GCS_HRATE_RE_INTF',
'GCS_HRATE_RE_KEY_CODE' ),
SEQUENCE_NUM+10,
1,
l_app_id,
INTERFACE_CODE,
SEQUENCE_NUM,
l_user_id,
CREATION_DATE,
l_user_id,
l_login_id,
--Bug Fix : 5563482
LAST_UPDATE_DATE
FROM bne_interface_cols_b
WHERE interface_col_name = g_non_ds_req_dimensions(i)
AND interface_code IN
('GCS_AD_ENTRY_LINE_INTF', 'GCS_AD_TB_INTF',
'GCS_HRATE_RE_INTF' ); -- HRates Enhancement
INSERT INTO BNE_INTERFACE_KEY_COLS
(APPLICATION_ID,
KEY_CODE,
SEQUENCE_NUM,
OBJECT_VERSION_NUMBER,
INTERFACE_APP_ID,
INTERFACE_CODE,
INTERFACE_SEQ_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT
l_app_id,
'GCS_HRATE_KEY_CODE',
SEQUENCE_NUM+5,
1,
l_app_id,
INTERFACE_CODE,
SEQUENCE_NUM,
l_user_id,
CREATION_DATE,
l_user_id,
l_login_id,
LAST_UPDATE_DATE
FROM bne_interface_cols_b
WHERE interface_col_name = l_hrate_drm_dimensions(i)
AND interface_code = 'GCS_HRATE_INTF' ;
l_query := 'SELECT ''Trial Balance'' template_type,flv2.meaning category_code, gat.transaction_date,ght.hierarchy_name,' ||
' fet1.entity_name consolidation_entity_name,fct.NAME currency_code, ' ||
' fet2.entity_name operating_entity_name,gat.ad_transaction_id, entry.entry_name recur_entry_name, ' ||
' entry.description, gat.total_consideration consideration_amount, flv.meaning trial_balance_seq, ' ||
' credit_amount, debit_amount' || l_view_select_cols ||
' FROM fnd_lookup_values flv, gcs_ad_transactions gat, gcs_entry_headers entry, ' ||
' fnd_lookup_values flv2, fem_entities_tl fet1, fem_entities_tl fet2, gcs_cons_relationships gcr, ' ||
' gcs_entity_cons_attrs geca, gcs_hierarchies_tl ght, fnd_currencies_tl fct, ' ||
' (SELECT tb.ad_transaction_id, credit_amount, debit_amount,
tb.trial_balance_seq ' || l_select_cols ||
' FROM gcs_ad_trial_balances tb' || l_from_clause ||
' WHERE ' || substr(l_where_clause, 5) || ') adtb' ||
' WHERE adtb.ad_transaction_id(+) = gat.ad_transaction_id AND gat.assoc_entry_id = entry.entry_id(+) ' ||
' AND nvl(gat.post_cons_relationship_id, gat.pre_cons_relationship_id) = gcr.cons_relationship_id AND fet1.entity_id = gcr.parent_entity_id ' ||
' AND fet1.language = USERENV(''LANG'') AND fet2.language = USERENV(''LANG'') ' ||
' AND ght.language = USERENV(''LANG'') AND fct.language = USERENV(''LANG'') ' ||
' AND fet2.entity_id = gcr.child_entity_id AND geca.entity_id = gcr.parent_entity_id ' ||
' AND geca.hierarchy_id = gcr.hierarchy_id AND gcr.hierarchy_id = ght.hierarchy_id ' ||
' AND geca.currency_code = fct.currency_code AND gat.transaction_type_code = flv2.lookup_code ' ||
' AND flv2.lookup_type = ''TRANSACTION_TYPE_CODE'' AND NVL (adtb.trial_balance_seq, 1) = flv.lookup_code ' ||
' AND flv.lookup_type = ''GCS_TB_SEQUENCE'' AND flv.LANGUAGE = USERENV (''LANG'') AND flv2.LANGUAGE = USERENV (''LANG'') ' ||
' AND flv.view_application_id = 266 AND flv2.view_application_id = 266 ';
l_query := 'SELECT template_type, category_code, transaction_date,hierarchy_name,' ||
' consolidation_entity_name,currency_code, ' ||
' operating_entity_name,ad_transaction_id, recur_entry_name, ' ||
' description, consideration_amount, trial_balance_seq, ' ||
' credit_amount, debit_amount' || l_view_select_cols ||
' FROM gcs_tb_webadi_vl ' ||
' WHERE ad_transaction_id = $param$.xns_id ';
'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
gcs_utility_pkg.g_nl ||
' WHERE application_id=l_app_id AND content_code=''GCS_AD_TB_CNT''');
UPDATE bne_stored_sql
SET QUERY = l_query
--Bug Fix : 5563482
--last_update_date = SYSDATE
WHERE application_id = l_app_id
AND content_code = 'GCS_AD_TB_CNT';
l_query := 'SELECT ''Manual Adjustment'' template_type, flv2.meaning category_code,' ||
' gat.transaction_date, ght.hierarchy_name,' ||
' fet1.entity_name consolidation_entity_name, fct.NAME currency_code,' ||
' fet2.entity_name operating_entity_name, gat.ad_transaction_id,' ||
' entry.entry_name recur_entry_name, entry.description,' ||
' gat.total_consideration consideration_amount, ' ||
' adtb.description lines_description, ' ||
' credit_amount, debit_amount' || l_view_select_cols ||
' FROM gcs_ad_transactions gat,gcs_entry_headers entry,' ||
' fnd_lookup_values flv2, fem_entities_tl fet1,fem_entities_tl fet2,gcs_cons_relationships gcr,' ||
' gcs_entity_cons_attrs geca, gcs_hierarchies_tl ght, fnd_currencies_tl fct,' ||
' (SELECT tb.entry_id, ytd_credit_balance_e credit_amount,
ytd_debit_balance_e debit_amount, tb.description ' ||
l_select_cols || ' FROM gcs_entry_lines tb' || l_from_clause ||
' WHERE NVL (tb.line_type_code, '' '') <> ''CALCULATED'' ' ||
l_where_clause || ') adtb ' ||
' WHERE adtb.entry_id(+) = gat.assoc_entry_id AND gat.assoc_entry_id = entry.entry_id(+) ' ||
' AND NVL (gat.post_cons_relationship_id, gat.pre_cons_relationship_id) = gcr.cons_relationship_id ' ||
' AND fet1.entity_id = gcr.parent_entity_id AND fet2.entity_id = gcr.child_entity_id ' ||
' AND fet1.language = USERENV(''LANG'') AND fet2.language = USERENV(''LANG'') ' ||
' AND ght.language = USERENV(''LANG'') AND fct.language = USERENV(''LANG'') ' ||
' AND geca.entity_id = gcr.parent_entity_id AND geca.hierarchy_id = gcr.hierarchy_id ' ||
' AND gcr.hierarchy_id = ght.hierarchy_id AND geca.currency_code = fct.currency_code ' ||
' AND gat.transaction_type_code = flv2.lookup_code AND flv2.lookup_type = ''TRANSACTION_TYPE_CODE'' ' ||
' AND flv2.LANGUAGE = USERENV (''LANG'') AND flv2.view_application_id = 266 ';
l_query := 'SELECT template_type, category_code,' ||
' transaction_date, hierarchy_name,' ||
' consolidation_entity_name, currency_code,' ||
' operating_entity_name, ad_transaction_id,' ||
' recur_entry_name, description,' ||
' consideration_amount, ' || ' lines_description,' ||
' credit_amount, debit_amount' || l_view_select_cols ||
' FROM gcs_adentry_webadi_vl ' ||
' WHERE ad_transaction_id = $param$.xns_id ';
'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
gcs_utility_pkg.g_nl ||
' WHERE application_id=l_app_id AND content_code=''GCS_AD_ENTRY_CNT''');
UPDATE bne_stored_sql
SET QUERY = l_query
--Bug Fix : 5563482
--last_update_date = SYSDATE
WHERE application_id = l_app_id
AND content_code = 'GCS_AD_ENTRY_CNT';
l_query := ' SELECT hierarchy_name, entity_name, fct_from.NAME from_currency, ' ||
' fct_to.NAME to_currency, translated_rate rate, ' ||
' translated_amount amount, flv.meaning AS rate_type, period.cal_period_name period, ' ||
' tb.hierarchy_id, tb.entity_id, tb.cal_period_id ' ||
l_select_cols ||
' FROM gcs_dimension_templates gdt,gcs_hierarchies_tl ght, fnd_lookup_values flv, fem_entities_tl entity, ' ||
' gcs_historical_rates tb, fnd_currencies_tl fct_from, fnd_currencies_tl fct_to, ' ||
' fem_cal_periods_tl period ' || l_from_clause ||
' WHERE gdt.hierarchy_id = tb.hierarchy_id AND gdt.template_code = ''RE''' ||
' AND tb.hierarchy_id = ght.hierarchy_id AND tb.entity_id = entity.entity_id ' ||
' AND tb.rate_type_code = flv.lookup_code AND flv.lookup_type = ''HISTORICAL_RATE_TYPE'' ' ||
' AND flv.LANGUAGE = USERENV (''LANG'') and flv.view_application_id = 266 ' ||
' AND fct_from.LANGUAGE = USERENV (''LANG'') and fct_to.LANGUAGE = USERENV (''LANG'') ' ||
' AND ght.LANGUAGE = USERENV (''LANG'') and entity.LANGUAGE = USERENV (''LANG'') ' ||
' AND tb.cal_period_id = period.cal_period_id AND period.language = USERENV(''LANG'')' ||
' AND fct_to.currency_code = tb.to_currency AND fct_from.currency_code = tb.from_currency ' ||
' AND fct_to.language = USERENV(''LANG'') AND fct_from.language = USERENV(''LANG'') ' ||
l_hr_re_where_clause ; -- Bug Fix - 5968398
l_query := ' SELECT hierarchy_name, entity_name, from_currency, ' ||
' to_currency, rate, ' || ' amount, rate_type, period ' ||
l_view_select_cols || ' FROM gcs_hr_re_webadi_vl tb ' ||
' WHERE hierarchy_id = $param$.hierarchy_id AND entity_id = $param$.entity_id ' ||
' AND cal_period_id = $param$.cal_period_id ';
'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
gcs_utility_pkg.g_nl ||
' WHERE application_id=l_app_id AND content_code=''GCS_HRATE_RE_CNT''');
UPDATE bne_stored_sql
SET QUERY = l_query
WHERE application_id = l_app_id
AND content_code = 'GCS_HRATE_RE_CNT';
l_query := ' SELECT hierarchy_name, entity_name, fct_from.NAME from_currency, ' ||
' fct_to.NAME to_currency, translated_rate rate, ' ||
' translated_amount amount, flv.meaning AS rate_type, period.cal_period_name period, ' ||
' tb.hierarchy_id, tb.entity_id, tb.cal_period_id ' ||
l_hrate_select_cols ||
' FROM gcs_dimension_templates gdt, gcs_hierarchies_tl ght, fnd_lookup_values flv, fem_entities_tl entity, ' ||
' gcs_historical_rates tb, fnd_currencies_tl fct_from, fnd_currencies_tl fct_to, ' ||
' fem_cal_periods_tl period ' || l_hrate_from_clause ||
' WHERE gdt.hierarchy_id = tb.hierarchy_id AND gdt.template_code = ''RE''' ||
' AND tb.hierarchy_id = ght.hierarchy_id AND tb.entity_id = entity.entity_id ' ||
' AND tb.rate_type_code = flv.lookup_code AND flv.lookup_type = ''HISTORICAL_RATE_TYPE'' ' ||
' AND flv.LANGUAGE = USERENV (''LANG'') and flv.view_application_id = 266 ' ||
' AND fct_from.LANGUAGE = USERENV (''LANG'') and fct_to.LANGUAGE = USERENV (''LANG'') ' ||
' AND ght.LANGUAGE = USERENV (''LANG'') and entity.LANGUAGE = USERENV (''LANG'') ' ||
' AND tb.cal_period_id = period.cal_period_id AND period.language = USERENV(''LANG'')' ||
' AND fct_to.currency_code = tb.to_currency AND fct_from.currency_code = tb.from_currency ' ||
' AND fct_to.language = USERENV(''LANG'') AND fct_from.language = USERENV(''LANG'') ' ||
l_hrate_where_clause || ' AND ( '|| l_hrate_where_dim_clause || ' )';
l_query := ' SELECT hierarchy_name, entity_name, from_currency, ' ||
' to_currency, rate, ' || ' amount, rate_type, period ' ||
l_hrate_view_select_cols || ' FROM gcs_hr_webadi_vl tb ' ||
' WHERE hierarchy_id = $param$.hierarchy_id AND entity_id = $param$.entity_id ' ||
' AND cal_period_id = $param$.cal_period_id ';
'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
gcs_utility_pkg.g_nl ||
' WHERE application_id=l_app_id AND content_code=''GCS_HRATE_CNT''');
UPDATE bne_stored_sql
SET QUERY = l_query
WHERE application_id = l_app_id
AND content_code = 'GCS_HRATE_CNT';
l_query := ' SELECT hierarchy_name, gct.category_name as category_code,gdtctl.data_type_name as balance_type_code, ' ||
' entity_name, eh.description, fct.NAME currency_code, credit, ' ||
' debit, flv1.meaning as process_code, start_period.cal_period_name start_period, ' ||
' end_period.cal_period_name end_period, eh.entry_name, eh.entry_id ,adtb.ENTRY_LINES_DESCRIPTION ' ||
l_view_select_cols ||
' FROM gcs_hierarchies_tl ght, fnd_lookup_values flv1, gcs_categories_tl gct, fem_entities_tl entity, ' ||
' gcs_entry_headers eh, fnd_currencies_tl fct, fem_cal_periods_tl start_period, '||
' gcs_data_type_codes_b gdtcb,gcs_data_type_codes_tl gdtctl, ' ||
' fem_cal_periods_tl end_period, ' ||
' (SELECT tb.entry_id, tb.description ENTRY_LINES_DESCRIPTION, ytd_credit_balance_e credit,
ytd_debit_balance_e debit ' || l_select_cols ||
' FROM gcs_entry_lines tb' || l_from_clause ||
' WHERE NVL(tb.line_type_code, '' '') <> ''CALCULATED'' ' ||
l_where_clause || ') adtb ' ||
' WHERE eh.hierarchy_id = ght.hierarchy_id AND eh.entity_id = entity.entity_id ' ||
' AND eh.process_code = flv1.lookup_code and flv1.lookup_type = ''GCS_ENTRY_PROCESS_CODE'' ' ||
' AND flv1.LANGUAGE = USERENV (''LANG'') AND eh.category_code = gct.category_code ' ||
' AND ght.LANGUAGE = USERENV (''LANG'') AND flv1.view_application_id = 266 ' ||
' AND entity.LANGUAGE = USERENV (''LANG'') AND fct.LANGUAGE = USERENV (''LANG'') ' ||
' AND start_period.LANGUAGE = USERENV (''LANG'') AND end_period.LANGUAGE (+)= USERENV (''LANG'') ' ||
' AND gct.LANGUAGE = USERENV (''LANG'') AND eh.start_cal_period_id = start_period.cal_period_id ' ||
' AND eh.end_cal_period_id = end_period.cal_period_id (+) AND eh.entry_id = adtb.entry_id (+)' ||
' AND fct.currency_code = eh.currency_code '||
' AND eh.balance_type_code = gdtcb.data_type_code '||
' AND gdtcb.data_type_id = gdtctl.data_type_id '||
' AND gdtctl.LANGUAGE = USERENV(''LANG'') ';
l_query := ' SELECT hierarchy_name, category_code, balance_type_code, ' ||
' entity_name, description, currency_code, credit, ' ||
' debit, process_code, start_period, ' ||
' end_period, entry_name, entry_id, ENTRY_LINES_DESCRIPTION ' || l_view_select_cols ||
' FROM gcs_entry_webadi_vl tb ' ||
' WHERE tb.entry_id=$PARAM$.entry_id ';
'UPDATE bne_stored_SQL SET QUERY=' || l_query ||
gcs_utility_pkg.g_nl ||
' WHERE application_id=l_app_id AND content_code=''GCS_ENTRY_LINES_CNT''');
UPDATE bne_stored_sql
SET QUERY = l_query
--Bug Fix : 5563482
--last_update_date = SYSDATE
WHERE application_id = l_app_id
AND content_code = 'GCS_ENTRY_LINES_CNT';
'INSERT INTO ' ||
g_dimension_info(p_dimension_varchar_label).b_t_table_name || ' (' ||
g_dimension_info(p_dimension_varchar_label).display_code || ',
value_set_display_code,
status )
SELECT display_code,
value_set_display_code,
''LOAD''
FROM gcs_dimension_members_t
WHERE sequence_num = :1 '
USING p_sequence_num;
'INSERT INTO ' ||
g_dimension_info(p_dimension_varchar_label).b_t_table_name || ' (' ||
g_dimension_info(p_dimension_varchar_label).display_code || ',
value_set_display_code,
status,
dimension_group_display_code)
SELECT display_code,
value_set_display_code,
''LOAD'',
dimension_group_display_code
FROM gcs_dimension_members_t
WHERE sequence_num = :1 '
USING p_sequence_num;
'INSERT INTO ' ||
g_dimension_info(p_dimension_varchar_label).tl_t_table_name || ' (' ||
g_dimension_info(p_dimension_varchar_label).display_code || ',
value_set_display_code,
language, ' ||
g_dimension_info(p_dimension_varchar_label).name || ',
description,
status)
SELECT display_code,
value_set_display_code,
USERENV(''LANG''),
name,
description,
''LOAD''
FROM gcs_dimension_members_t
WHERE sequence_num = :1 '
USING p_sequence_num;
SELECT fdab.attribute_id, fxd.member_display_code_col, fxd.member_b_table_name,
fxd.member_col, fdab.default_assignment, fdab.attribute_varchar_label
BULK COLLECT INTO l_attribute_id_list, l_member_display_code_list, l_member_b_table_list,
l_member_col_list, l_default_assign_list, l_attr_varchar_list
FROM fem_xdim_dimensions fxd, fem_dim_attributes_b fdab
WHERE fxd.dimension_id (+)= fdab.attribute_dimension_id
AND fdab.dimension_id = g_dimension_info(p_dimension_varchar_label).dimension_id
AND fdab.attribute_required_flag = 'Y';
'SELECT ' || l_member_display_code_list(i) || '
FROM ' || l_member_b_table_list(i) || '
WHERE ' || l_member_col_list(i) || ' = :1 '
INTO l_default_assign_list(i)
USING l_default_assign_list(i);
' INSERT INTO ' ||
g_dimension_info(p_dimension_varchar_label).attr_t_table_name || ' (' ||
g_dimension_info(p_dimension_varchar_label).display_code || ',
value_set_display_code,
attribute_varchar_label,
attribute_assign_value,
attr_assign_vs_display_code,
version_display_code ,
status)
SELECT gdmt.display_code,
gdmt.value_set_display_code,
:1,
DECODE(:2, ''EXTENDED_ACCOUNT_TYPE'',
gdmt.ext_account_type_code, :3),
NULL,
fdavb.version_display_code,
''LOAD''
FROM gcs_dimension_members_t gdmt,
fem_dim_attr_versions_b fdavb
WHERE fdavb.default_version_flag = ''Y''
AND fdavb.attribute_id = :4
AND gdmt.sequence_num = :5 '
USING l_attr_varchar_list(i),
l_attr_varchar_list(i),
l_default_assign_list(i),
l_attribute_id_list(i),
p_sequence_num;
INSERT INTO fem_cctr_orgs_attr_t
(cctr_org_display_code,
value_set_display_code,
attribute_varchar_label,
attribute_assign_value,
attr_assign_vs_display_code,
version_display_code,
status)
SELECT display_code,
value_set_display_code,
fdab.attribute_varchar_label,
cost_center_display_code,
cost_center_vs_display_code,
fdavb.version_display_code,
'LOAD'
FROM gcs_dimension_members_t ,
fem_dim_attr_versions_b fdavb,
fem_dim_attributes_b fdab
WHERE fdavb.default_version_flag = 'Y'
AND fdavb.attribute_id = fdab.attribute_id
AND fdab.attribute_varchar_label = 'COST_CENTER'
AND fdab.dimension_id = 8
AND sequence_num = p_sequence_num
AND cost_center_display_code is not null;
INSERT INTO fem_cctr_orgs_attr_t
(cctr_org_display_code,
value_set_display_code,
attribute_varchar_label,
attribute_assign_value,
attr_assign_vs_display_code,
version_display_code,
status)
SELECT display_code,
value_set_display_code,
fdab.attribute_varchar_label,
company_display_code,
company_vs_display_code,
fdavb.version_display_code,
'LOAD'
FROM gcs_dimension_members_t ,
fem_dim_attr_versions_b fdavb,
fem_dim_attributes_b fdab
WHERE fdavb.default_version_flag = 'Y'
AND fdavb.attribute_id = fdab.attribute_id
AND fdab.attribute_varchar_label = 'COMPANY'
AND fdab.dimension_id = 8
AND sequence_num = p_sequence_num
AND company_display_code is not null;
SELECT status_code
INTO l_status_code
FROM Fnd_Concurrent_Requests
WHERE request_id = FND_GLOBAL.conc_request_id;
SELECT fxd.member_tl_table_name ,
fxd.attribute_table_name
INTO l_member_tl_table_name,
l_member_attr_table_name
FROM fem_xdim_dimensions fxd
WHERE fxd.dimension_id = g_dimension_info(p_dimension_varchar_label).dimension_id ;
DELETE FROM gcs_dimension_members_t
WHERE sequence_num = p_sequence_num;
DELETE FROM gcs_dimension_members_t
WHERE sequence_num = p_sequence_num;
INSERT INTO gcs_hier_members_t
(sequence_num,
parent_vs_display_code,
parent_display_code,
child_vs_display_code,
child_display_code,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT DISTINCT p_sequence_num,
parent_vs_display_code,
parent_display_code,
parent_vs_display_code,
parent_display_code,
1,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id
FROM gcs_hier_members_t
WHERE sequence_num = p_sequence_num;
SELECT folder_name
INTO l_folder_name
FROM fem_folders_tl
WHERE language = userenv('LANG')
AND folder_id = 1100;
INSERT INTO fem_hierarchies_t
(hierarchy_object_name,
folder_name,
language,
dimension_varchar_label,
hierarchy_type_code,
group_sequence_enforced_code,
multi_top_flag,
multi_value_set_flag,
hierarchy_usage_code,
flattened_rows_flag,
status,
hier_obj_def_display_name,
effective_start_date,
effective_end_date,
calendar_display_code)
VALUES
(p_hierarchy_name,
l_folder_name,
USERENV('LANG'),
p_dimension_varchar_label,
'OPEN',
decode(p_analysis_flag,
'Y',
'SEQUENCE_ENFORCED_SKIP_LEVEL',
'NO_GROUPS'),
'Y',
p_mvs_flag,
'STANDARD',
decode(p_mvs_flag, 'Y', 'N', 'Y'),
'LOAD',
p_version_name,
p_version_start_date,
nvl(p_version_end_date, p_version_start_date + 365 * 20),
null);
DELETE FROM fem_hier_value_sets_t
WHERE hierarchy_object_name = p_hierarchy_name;
INSERT INTO fem_hier_value_sets_t
(hierarchy_object_name, value_set_display_code, language, status)
SELECT DISTINCT p_hierarchy_name,
child_vs_display_code,
USERENV('LANG'),
'LOAD'
FROM gcs_hier_members_t
WHERE sequence_num = p_sequence_num;
INSERT INTO fem_hier_value_sets_t
(hierarchy_object_name, value_set_display_code, language, status)
SELECT DISTINCT p_hierarchy_name,
parent_vs_display_code,
USERENV('LANG'),
'LOAD'
FROM gcs_hier_members_t
WHERE sequence_num = p_sequence_num;
l_statement := 'INSERT INTO ' ||
g_dimension_info(p_dimension_varchar_label)
.hier_t_table_name || ' (
hierarchy_object_name,
hierarchy_obj_def_display_name,
parent_display_code,
parent_value_set_display_code,
child_display_code,
child_value_set_display_code,
display_order_num,
weighting_pct,
status,
language)
SELECT :1,
:2,
NVL(parent_display_code, child_display_code),
parent_vs_display_code,
child_display_code,
child_vs_display_code,
rownum, -- bugfix : 5411156
NULL,
''LOAD'',
USERENV(''LANG'')
FROM gcs_hier_members_t
WHERE sequence_num = :3 ';
SELECT status_code
INTO l_status_code
FROM Fnd_Concurrent_Requests
WHERE request_id = FND_GLOBAL.conc_request_id;
EXECUTE IMMEDIATE 'SELECT dim_table.parent_display_code, dim_table.child_display_code, ' ||
' dim_table.parent_value_set_display_code, dim_table.child_value_set_display_code, dim_table.status ' ||
' FROM ' ||
g_dimension_info(p_dimension_varchar_label)
.hier_t_table_name ||
' dim_table, gcs_hier_members_t intf_table' ||
' WHERE intf_table.parent_display_code = dim_table.parent_display_code ' ||
' AND intf_table.child_display_code = dim_table.child_display_code ' ||
' AND intf_table.sequence_num = :1 ' BULK COLLECT
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
USING p_sequence_num;
DELETE FROM fem_hierarchies_t
WHERE hierarchy_object_name = p_hierarchy_name
AND hier_obj_def_display_name = p_version_name;
DELETE FROM fem_hier_value_sets_t
WHERE hierarchy_object_name = p_hierarchy_name;
'DELETE FROM ' ||
g_dimension_info(p_dimension_varchar_label).hier_t_table_name || '
WHERE parent_display_code =:1
AND child_display_code = :2
AND parent_value_set_display_code = :3
AND child_value_set_display_code = :4
AND hierarchy_object_name = :5
AND hierarchy_obj_def_display_name = :6'
USING
l_err_parent_display_code(i),
l_err_child_display_code(i),
l_err_parent_vs_display_code(i),
l_err_child_vs_display_code(i),
p_hierarchy_name,
p_version_name
;
SELECT foct.object_id,
fh.dimension_id,
fgvcd.value_set_id,
fodb.effective_start_date,
fodb.effective_end_date
INTO l_object_id,
l_dimension_id,
l_consolidation_vs_id,
l_effective_start_date,
l_effective_end_date
FROM fem_object_catalog_tl foct,
fem_object_definition_b fodb,
fem_object_definition_tl fodt,
fem_hierarchies fh,
fem_global_vs_combo_defs fgvcd,
gcs_system_options gso
WHERE foct.language = USERENV('LANG')
AND fodb.object_definition_id = fodt.object_definition_id
AND foct.object_name = p_hierarchy_name
AND foct.object_id = fodt.object_id
AND fodt.display_name = p_version_name
AND fodt.language = USERENV('LANG')
AND foct.object_id = fh.hierarchy_obj_id
AND gso.fch_global_vs_combo_id = fgvcd.global_vs_combo_id
AND fgvcd.dimension_id = fh.dimension_id;
UPDATE fem_xdim_dimensions fxd
SET default_mvs_hierarchy_obj_id = l_object_id
WHERE dimension_id = l_dimension_id;
gcs_cons_impact_analysis_pkg.value_set_map_updated( p_dimension_id => l_dimension_id,
p_eff_start_date => l_effective_start_date,
p_eff_end_date => l_effective_end_date,
p_consolidation_vs_id => l_consolidation_vs_id);
DELETE FROM gcs_hier_members_t WHERE sequence_num = p_sequence_num;
DELETE FROM gcs_hier_members_t WHERE sequence_num = p_sequence_num;
DELETE FROM gcs_hier_members_t WHERE sequence_num = p_sequence_num;
' SELECT count(*)
INTO l_cnt
FROM gcs_interco_map_dtls; ');
SELECT count(*)
INTO l_cnt
FROM gcs_interco_map_dtls;
' UPDATE GCS_SYSTEM_OPTIONS
SET INTERCO_MAP_ENABLED_FLAG = ''Y''; ');
UPDATE GCS_SYSTEM_OPTIONS
SET INTERCO_MAP_ENABLED_FLAG = 'Y';
' UPDATE GCS_SYSTEM_OPTIONS
SET INTERCO_MAP_ENABLED_FLAG = ''N''; ');
UPDATE GCS_SYSTEM_OPTIONS
SET INTERCO_MAP_ENABLED_FLAG = 'N';