The following lines contain the word 'select', 'insert', 'update' or 'delete':
| delete from global temp tables so that |
| successive calls from OAF are allowed |
| in case of errors and no rollback is |
| issued in-between. |
| C_TMPL_PUSH_INTERF_DATA_STMT: |
| introduced deletion of interface |
| global temp table as per explanation |
| above. |
| 28-JUL-04 A.Quaglia compile_tad: |
| moved call to init_global_variables |
| before messages are raised. |
| log_ccid_disabled_error (generated): |
| log_null_segments_error (generated): |
| log_ccid_not_found_error (generated): |
| added param p_account_type_code |
| and logic to derive the acc.type name |
| changed calls to these routines |
| changed various message tokens |
| 28-JUL-04 A.Quaglia C_TMPL_BATCH_BUILD_CCID_STMTS: |
| gt.processed_flag must be gtint. |
| in the statements that creates the new |
| ccids. |
| 05-JAN-05 K.Boussema Split up C_TMPL_TAD_PACKAGE_BODY_PART_1 |
| and C_TMPL_TAD_PACKAGE_BODY_PART_2. |
| 18-MAI-05 K.Boussema added the column dummy_rowid in GT tables |
| to fix bug 4344773 |
| 08-AUG-2006 Jorge Larre Bug 5368196 |
| Use FIRST instead of 0 and 1 in the index of the array in the |
| statements C_TMPL_PUSH_INTERF_DATA_STMT and |
| C_TMPL_POP_INTERF_DATA_STMT. |
| 23-AUG-2006 Jorge Larre Bug 5411930 |
| When calling FND_FLEX_EXT.get_ccid we need to convert the date |
| into the format accepted by the routine : 'YYYY/MM/DD HH24:MI:SS' |
+======================================================================*/
--
-- Private exceptions
--
ge_fatal_error EXCEPTION;
INSERT INTO XLA_TAB_ERRORS_GT
(
base_rowid
,msg_data
)
(
SELECT p_rowid
,l_encoded_message
FROM dual
WHERE 0 = (SELECT COUNT(*)
FROM XLA_TAB_ERRORS_GT xte
WHERE xte.base_rowid = p_rowid
AND xte.msg_data = l_encoded_message
)
AND ROWNUM = 1
);
SELECT xtat.name
INTO l_account_type_name
FROM xla_tab_acct_types_tl xtat
WHERE xtat.application_id = $APPLICATION_ID_2$
AND xtat.account_type_code = p_account_type_code;
SELECT xtat.name
INTO l_account_type_name
FROM xla_tab_acct_types_tl xtat
WHERE xtat.application_id = $APPLICATION_ID_2$
AND xtat.account_type_code = p_account_type_code;
SELECT xtat.name
INTO l_account_type_name
FROM xla_tab_acct_types_tl xtat
WHERE xtat.application_id = $APPLICATION_ID_2$
AND xtat.account_type_code = p_account_type_code;
INSERT
INTO XLA_TAB_NEW_CCIDS_GT
( code_combination_id
,base_rowid
,concatenated_segments
,msg_data
)
VALUES
( l_code_combination_id
,p_rowid
,p_concatenated_segments
,NULL
);
INSERT
INTO XLA_TAB_NEW_CCIDS_GT
( code_combination_id
,base_rowid
,concatenated_segments
,msg_data
)
VALUES
( l_code_combination_id
,p_rowid
,p_concatenated_segments
,l_encoded_message
);
SELECT id_flex_structure_name
INTO l_chart_of_accounts_name
FROM fnd_id_flex_structures_vl ffsvl
WHERE ffsvl.application_id = 101
AND ffsvl.id_flex_code = ''GL#''
AND ffsvl.id_flex_num = p_chart_of_accounts_id;
SELECT UPPER(fifs.application_column_name)
BULK COLLECT
INTO l_table_segment_column_names
FROM fnd_id_flex_segments fifs
WHERE fifs.application_id = 101
AND fifs.id_flex_code = ''GL#''
AND fifs.id_flex_num = p_chart_of_accounts_id
AND fifs.enabled_flag = ''Y''
ORDER BY fifs.segment_num;
SELECT name
INTO l_source_name
FROM xla_sources_vl xsv
WHERE xsv.source_code = p_source_code
AND xsv.source_type_code = p_source_type_code
AND xsv.application_id = p_source_application_id;
SELECT
gcc.segment1
, gcc.segment2
, gcc.segment3
, gcc.segment4
, gcc.segment5
, gcc.segment6
, gcc.segment7
, gcc.segment8
, gcc.segment9
, gcc.segment10
, gcc.segment11
, gcc.segment12
, gcc.segment13
, gcc.segment14
, gcc.segment15
, gcc.segment16
, gcc.segment17
, gcc.segment18
, gcc.segment19
, gcc.segment20
, gcc.segment21
, gcc.segment22
, gcc.segment23
, gcc.segment24
, gcc.segment25
, gcc.segment26
, gcc.segment27
, gcc.segment28
, gcc.segment29
, gcc.segment30
INTO
l_value_segment1
,l_value_segment2
,l_value_segment3
,l_value_segment4
,l_value_segment5
,l_value_segment6
,l_value_segment7
,l_value_segment8
,l_value_segment9
,l_value_segment10
,l_value_segment11
,l_value_segment12
,l_value_segment13
,l_value_segment14
,l_value_segment15
,l_value_segment16
,l_value_segment17
,l_value_segment18
,l_value_segment19
,l_value_segment20
,l_value_segment21
,l_value_segment22
,l_value_segment23
,l_value_segment24
,l_value_segment25
,l_value_segment26
,l_value_segment27
,l_value_segment28
,l_value_segment29
,l_value_segment30
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = p_ccid
AND gcc.chart_of_accounts_id = p_chart_of_accounts_id
AND gcc.template_id IS NULL
AND gcc.enabled_flag = ''Y'';
SELECT name
INTO l_source_name
FROM xla_sources_vl xsv
WHERE xsv.source_code = p_source_code
AND xsv.source_type_code = p_source_type_code
AND xsv.application_id = p_source_application_id;
l_update_statement_text CLOB;
l_update_statements_text CLOB;
l_update_stmts_wrapper_text CLOB;
SELECT xtdd.application_id
,xtdd.amb_context_code
,xtdd.account_type_code
,xtdd.flexfield_segment_code
,xtdd.segment_rule_type_code
,xtdd.segment_rule_code
,xtta.object_name_affix
,xtta.compile_status_code
,xtta.rule_assignment_code
,NULL
BULK COLLECT
INTO l_table_of_tad_details
FROM xla_tab_acct_def_details xtdd
,xla_tab_acct_types_b xtta
WHERE xtdd.application_id = $APPLICATION_ID_2$
AND xtdd.account_definition_code = ''$TAD_CODE_2$''
AND xtdd.account_definition_type_code = ''$TAD_TYPE_CODE_2$''
AND xtdd.amb_context_code = ''$AMB_CONTEXT_CODE_2$''
AND xtta.application_id = xtdd.application_id
AND xtta.account_type_code = xtdd.account_type_code
ORDER BY xtta.object_name_affix
,xtdd.flexfield_segment_code
,xtdd.account_type_code;
l_update_statements_text := l_update_statements_text || l_update_statement_text;
l_update_statement_text := NULL;
l_update_statement_text := g_batch_build_ccid_stmts;
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,''$TABLE_NAME$''
,NVL(l_current_temp_table_name, '' '')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,''$C_TMPL_WHERE_SEGMENT_NULL_ANDS$''
,NVL(l_tmpl_where_segment_null_ands, '' '')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,''$C_TMPL_WHERE_SEGMENT_NULL_ORS$''
,NVL(l_tmpl_where_segment_null_ors, '' '')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,''$C_TMPL_UPD_SET_SEGMENT_COMMAS$''
,NVL(l_tmpl_upd_set_segment_commas, '' '')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,''$C_TMPL_SEL_NVL_SEGMENT_COMMAS$''
,NVL(l_tmpl_sel_nvl_segment_commas, '' '')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,''$C_TMPL_WHERE_SEGMENTS_EQUALS$''
,NVL(l_tmpl_where_segments_equals, '' '')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,''$C_TMPL_CONCAT_SEGMENTS$''
,NVL(l_tmpl_concat_segments, '' '')
);
END IF; --new update statement
l_update_statements_text := l_update_statements_text || l_update_statement_text;
l_update_stmts_wrapper_text := ''
DECLARE
l_chart_of_accounts_id NUMBER
:= $TRANSACTION_COA_ID$;
l_update_stmts_wrapper_text:= xla_cmp_string_pkg.replace_token(
l_update_stmts_wrapper_text
,''$TRANSACTION_COA_ID$''
,TO_CHAR(p_chart_of_accounts_id)
);
l_update_stmts_wrapper_text:= xla_cmp_string_pkg.replace_token(
l_update_stmts_wrapper_text
,''$TRANSACTION_COA_NAME$''
,NVL(p_chart_of_accounts_name, '' '')
);
l_update_stmts_wrapper_text:= xla_cmp_string_pkg.replace_token(
l_update_stmts_wrapper_text
,''$CONCAT_SEGMENTS_TEMPLATE$''
,NVL(p_concat_segments_template, '' '')
);
l_update_stmts_wrapper_text := xla_cmp_string_pkg.replace_token
( l_update_stmts_wrapper_text
,''$C_TMPL_BATCH_BUILD_CCID_SQL$''
,l_update_statements_text
);
p_text => l_update_stmts_wrapper_text
);
p_dml_text => l_update_stmts_wrapper_text
,p_msg_mode => xla_cmp_tad_pkg.G_OA_MESSAGE
)
THEN
l_fatal_error_message := ''xla_cmp_create_pkg.execute_dml failed'';
DELETE
FROM xla_tab_errors_gt;
DELETE
FROM xla_tab_new_ccids_gt;
DELETE
FROM $TABLE_NAME$;
INSERT
INTO
( SELECT gt.DUMMY_ROWID
,gt.SOURCE_DISTRIBUTION_ID_NUM_1
,gt.SOURCE_DISTRIBUTION_ID_NUM_2
,gt.SOURCE_DISTRIBUTION_ID_NUM_3
,gt.SOURCE_DISTRIBUTION_ID_NUM_4
,gt.SOURCE_DISTRIBUTION_ID_NUM_5
,gt.ACCOUNT_TYPE_CODE
--START of source list$C_TMPL_TAB_PUSH_INTERF_SOURCES$
--END of source list
,gt.TARGET_CCID
,gt.CONCATENATED_SEGMENTS
,gt.MSG_COUNT
,gt.MSG_DATA
FROM $TABLE_NAME$ gt
)
VALUES
$TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i);
|| '' row(s) inserted into $TABLE_NAME$''
,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
$TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.DELETE;
SELECT ROWID
,SOURCE_DISTRIBUTION_ID_NUM_1
,SOURCE_DISTRIBUTION_ID_NUM_2
,SOURCE_DISTRIBUTION_ID_NUM_3
,SOURCE_DISTRIBUTION_ID_NUM_4
,SOURCE_DISTRIBUTION_ID_NUM_5
,ACCOUNT_TYPE_CODE
--START of source list$C_TMPL_TAB_POP_INTERF_SOURCES$
--END of source list
,TARGET_CCID
,CONCATENATED_SEGMENTS
,MSG_COUNT
,MSG_DATA
BULK COLLECT
INTO $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$
FROM $TABLE_NAME$ gt;
UPDATE $table_name$ gt
SET
$C_TMPL_SET_CLAUSES$;
FUNCTION build_batch_update_statements
(
p_table_of_tad_details IN gt_table_of_tad_details
,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
,p_table_of_adrs_ext IN xla_cmp_adr_pkg.gt_table_of_adrs_out
,p_update_statements_text OUT NOCOPY CLOB
)
RETURN BOOLEAN;
,p_update_statements_text OUT NOCOPY CLOB
)
RETURN BOOLEAN;
| update_tad_compilation_status |
| |
| This program initializes the global variables required by the |
| package. It retrieves the user name. |
| |
| |
+======================================================================*/
FUNCTION update_tad_compilation_status
( p_compilation_status_code IN VARCHAR2
,p_application_id IN NUMBER
,p_account_definition_code IN VARCHAR2
,p_account_definition_type_code IN VARCHAR2
,p_amb_context_code IN VARCHAR2
)
RETURN BOOLEAN
IS
l_return_value BOOLEAN;
l_log_module := C_DEFAULT_MODULE||'.update_tad_compilation_status';
UPDATE xla_tab_acct_defs_b xtad
SET xtad.compile_status_code = p_compilation_status_code
WHERE xtad.application_id = p_application_id
AND xtad.account_definition_code = p_account_definition_code
AND xtad.account_definition_type_code = p_account_definition_type_code
AND xtad.amb_context_code = p_amb_context_code;
|| ' row(s) updated in xla_tab_acct_defs_b'
,p_level => C_LEVEL_STATEMENT);
( p_location => 'xla_cmp_tad_pkg.update_tad_compilation_status'
);
( p_location => 'xla_cmp_tad_pkg.update_tad_compilation_status'
,p_msg_mode => g_msg_mode
);
END update_tad_compilation_status;
(SELECT xtdv.application_id
,xtdv.account_definition_code
,xtdv.account_definition_type_code
,xtdv.amb_context_code
,xtdv.name
FROM xla_tab_acct_defs_vl xtdv
WHERE xtdv.application_id = p_application_id
AND xtdv.amb_context_code = l_amb_context_code
AND xtdv.enabled_flag = 'Y'
)
LOOP
--If tad_compilation successful
IF compile_tad
(
p_application_id => cur_tad.application_id
,p_account_definition_code => cur_tad.account_definition_code
,p_account_definition_type_code => cur_tad.account_definition_type_code
,p_amb_context_code => cur_tad.amb_context_code
)
THEN
--Report the "successfully compiled" message in the output
fnd_file.put_line
(
fnd_file.output
,xla_messages_pkg.get_message
(
'XLA'
,'XLA_TAB_CMP_TAD_SUCCEEDED'
,'TRX_ACCT_DEF', cur_tad.name
)
);
SELECT name
,enabled_flag
INTO l_tad_name
,l_tad_enabled_flag
FROM xla_tab_acct_defs_vl
WHERE application_id = p_application_id
AND account_definition_code = p_account_definition_code
AND account_definition_type_code = p_account_definition_type_code
AND amb_context_code = p_amb_context_code;
SELECT xtat.name
INTO l_uncomp_tat_name
FROM xla_tab_acct_types_vl xtat
WHERE xtat.application_id = p_application_id
AND xtat.enabled_flag = 'Y'
AND ( xtat.compile_status_code IS NULL
OR xtat.compile_status_code
<> xla_cmp_common_pkg.G_COMPILE_STATUS_CODE_COMPILED
)
AND ROWNUM = 1;
SELECT xtdd.application_id
,xtdd.amb_context_code
,xtdd.account_type_code
,xtdd.flexfield_segment_code
,xtdd.segment_rule_type_code
,xtdd.segment_rule_code
,xtta.object_name_affix
,xtta.compile_status_code
,xtta.rule_assignment_code
,NULL
BULK COLLECT
INTO l_table_of_tad_details
FROM xla_tab_acct_def_details xtdd
,xla_tab_acct_types_b xtta
WHERE xtdd.application_id = p_application_id
AND xtdd.account_definition_code = p_account_definition_code
AND xtdd.account_definition_type_code = p_account_definition_type_code
AND xtdd.amb_context_code = p_amb_context_code
AND xtta.application_id = xtdd.application_id
AND xtta.account_type_code = xtdd.account_type_code
ORDER BY xtta.object_name_affix
,xtdd.flexfield_segment_code
,xtdd.account_type_code;
SELECT application_id --application_id
,segment_rule_type_code --segment_rule_type_code
,segment_rule_code --segment_rule_code
,amb_context_code --amb_context_code
BULK COLLECT
INTO l_table_of_adrs
FROM
(
SELECT DISTINCT
xtdd.application_id
,xtdd.segment_rule_type_code
,xtdd.segment_rule_code
,xtdd.amb_context_code
FROM xla_tab_acct_def_details xtdd
,xla_tab_acct_types_b xtta
WHERE xtdd.application_id = p_application_id
AND xtdd.account_definition_code = p_account_definition_code
AND xtdd.account_definition_type_code = p_account_definition_type_code
AND xtdd.amb_context_code = p_amb_context_code
AND xtta.application_id = xtdd.application_id
AND xtta.account_type_code = xtdd.account_type_code
);
SELECT count(*)
INTO l_count_missing_required_tat
FROM xla_tab_acct_types_b xtat
,xla_tab_acct_def_details xtad
WHERE xtat.application_id = p_application_id
AND xtat.enabled_flag = 'Y'
AND xtat.rule_assignment_code = 'REQUIRED'
AND xtad.application_id (+)= xtat.application_id
AND xtad.account_type_code (+)= xtat.account_type_code
AND xtad.account_definition_code (+)= p_account_definition_code
AND xtad.account_definition_type_code (+)= p_account_definition_type_code
AND xtad.amb_context_code (+)= p_amb_context_code
AND xtad.flexfield_segment_code IS NULL;
IF NOT update_tad_compilation_status
( p_compilation_status_code =>
xla_cmp_common_pkg.G_COMPILE_STATUS_CODE_COMPILED
,p_application_id => p_application_id
,p_account_definition_code => p_account_definition_code
,p_account_definition_type_code => p_account_definition_type_code
,p_amb_context_code => p_amb_context_code
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'Could not update the TAD compilation status'
,p_level => C_LEVEL_ERROR);
IF NOT update_tad_compilation_status
( p_compilation_status_code =>
xla_cmp_common_pkg.G_COMPILE_STATUS_CODE_ERROR
,p_application_id => p_application_id
,p_account_definition_code => p_account_definition_code
,p_account_definition_type_code => p_account_definition_type_code
,p_amb_context_code => p_amb_context_code
)
THEN
IF (C_LEVEL_ERROR >= g_log_level) THEN
trace
(p_module => l_log_module
,p_msg => 'Could not update the TAD compilation status'
,p_level => C_LEVEL_ERROR);
l_update_statements_text CLOB;
IF NOT build_batch_update_statements
(
p_table_of_tad_details => p_table_of_tad_details
,p_table_of_adrs => p_table_of_adrs
,p_table_of_adrs_ext => p_table_of_adrs_ext
,p_update_statements_text => l_update_statements_text
)
THEN
l_return_value := FALSE;
' build_batch_update_statements failed'
,p_level => C_LEVEL_ERROR);
,p_update_statements_text => l_batch_ccid_proc_stmts_text
)
THEN
l_return_value := FALSE;
,p_replacement_text => NVL(l_update_statements_text, ' ')
);
| build_batch_update_statements |
| |
| |
| |
| |
+======================================================================*/
FUNCTION build_batch_update_statements
(
p_table_of_tad_details IN gt_table_of_tad_details
,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
,p_table_of_adrs_ext IN xla_cmp_adr_pkg.gt_table_of_adrs_out
,p_update_statements_text OUT NOCOPY CLOB
)
RETURN BOOLEAN
IS
l_update_statement_text CLOB;
l_update_statements_text CLOB;
l_log_module := C_DEFAULT_MODULE||'.build_batch_update_statements';
l_update_statement_text:= xla_cmp_common_pkg.replace_token
(
p_original_text => l_update_statement_text
,p_token => '$C_TMPL_SET_CLAUSES$'
,p_replacement_text => NVL(l_set_clauses_text, ' ')
);
l_update_statements_text := l_update_statements_text || l_update_statement_text;
l_update_statement_text := NULL;
l_update_statement_text := C_TMPL_BATCH_CCID_SEG_UPD_STMT;
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,'$table_name$'
,NVL(l_current_temp_table_name, ' ')
);
END IF; --new update statement
l_update_statement_text:= xla_cmp_common_pkg.replace_token
(
p_original_text => l_update_statement_text
,p_token => '$C_TMPL_SET_CLAUSES$'
,p_replacement_text => NVL(l_set_clauses_text, ' ')
);
l_update_statements_text := l_update_statements_text || l_update_statement_text;
p_update_statements_text := l_update_statements_text;
(p_location => 'xla_cmp_tad_pkg.build_batch_update_statements'
,p_msg_mode => g_msg_mode
);
END build_batch_update_statements;
,p_update_statements_text OUT NOCOPY CLOB
)
RETURN BOOLEAN
IS
l_update_statement_text CLOB;
l_update_statements_text CLOB;
l_update_statements_text := C_TMPL_BATCH_BLD_CCID_DYN_STMS;
l_update_statements_text := l_update_statements_text || l_update_statement_text;
l_update_statement_text := NULL;
l_update_statement_text := C_TMPL_BATCH_BUILD_CCID_STMTS;
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,'$TABLE_NAME$'
,NVL(l_current_temp_table_name, ' ')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,'$C_TMPL_WHERE_SEGMENT_NULL_ANDS$'
,NVL(l_tmpl_where_segment_null_ands, ' ')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,'$C_TMPL_WHERE_SEGMENT_NULL_ORS$'
,NVL(l_tmpl_where_segment_null_ors, ' ')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,'$C_TMPL_UPD_SET_SEGMENT_COMMAS$'
,NVL(l_tmpl_upd_set_segment_commas, ' ')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,'$C_TMPL_SEL_NVL_SEGMENT_COMMAS$'
,NVL(l_tmpl_sel_nvl_segment_commas, ' ')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,'$C_TMPL_WHERE_SEGMENTS_EQUALS$'
,NVL(l_tmpl_where_segments_equals, ' ')
);
l_update_statement_text:= xla_cmp_string_pkg.replace_token(
l_update_statement_text
,'$C_TMPL_CONCAT_SEGMENTS$'
,NVL(l_tmpl_concat_segments, ' ')
);
END IF; --new update statement
l_update_statements_text := l_update_statements_text || l_update_statement_text;
p_update_statements_text := l_update_statements_text;
END IF; --new update statement
SELECT xtd.hash_id
,xtd.enabled_flag
,xtd.chart_of_accounts_id
INTO l_tad_hash_id
,l_tad_enabled_flag
,l_chart_of_accounts_id
FROM xla_tab_acct_defs_b xtd
WHERE xtd.application_id = g_application_info.application_id
AND xtd.account_definition_code = p_account_definition_code
AND xtd.account_definition_type_code = p_account_definition_type_code
AND xtd.amb_context_code = p_amb_context_code;
SELECT xla_tab_acct_defs_b_s.NEXTVAL
INTO l_tad_hash_id
FROM DUAL;
UPDATE xla_tab_acct_defs_b xtd
SET xtd.hash_id = l_tad_hash_id
WHERE xtd.application_id = g_application_info.application_id
AND xtd.account_definition_code = p_account_definition_code
AND xtd.account_definition_type_code = p_account_definition_type_code
AND xtd.amb_context_code = p_amb_context_code;
|| ' row(s) updated in xla_tab_acct_defs_b'
,p_level => C_LEVEL_STATEMENT);
SELECT id_flex_structure_name
INTO p_chart_of_accounts_name
FROM fnd_id_flex_structures_vl ffsvl
WHERE ffsvl.application_id = 101
AND ffsvl.id_flex_code = 'GL#'
AND ffsvl.id_flex_num = p_chart_of_accounts_id;
SELECT UPPER(fifs.application_column_name)
BULK COLLECT
INTO l_table_segment_names
FROM fnd_id_flex_segments fifs
WHERE fifs.application_id = 101
AND fifs.id_flex_code = 'GL#'
AND fifs.id_flex_num = p_chart_of_accounts_id
AND fifs.enabled_flag = 'Y'
ORDER BY fifs.segment_num;