[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 06/03/2005 V. Kumar Updated get_transaction_id to include NULL |
| columns for undefined user trx identifier |
| 12/23/2005 V. Kumar Added function get_transaction_id |
| 06/23/2006 V. Kumar Added function get_conc_segments |
+===========================================================================*/
--=============================================================================
-- **************** declaraions ********************
--=============================================================================
TYPE t_rec IS RECORD
(f1 VARCHAR2(80)
,f2 VARCHAR2(80));
,p_select_str OUT NOCOPY VARCHAR2
,p_from_str OUT NOCOPY VARCHAR2
,p_where_str OUT NOCOPY VARCHAR2) IS
CURSOR cols_csr IS
(SELECT xid.transaction_id_col_name_1 trx_col_1
,xid.transaction_id_col_name_2 trx_col_2
,xid.transaction_id_col_name_3 trx_col_3
,xid.transaction_id_col_name_4 trx_col_4
,xid.source_id_col_name_1 src_col_1
,xid.source_id_col_name_2 src_col_2
,xid.source_id_col_name_3 src_col_3
,xid.source_id_col_name_4 src_col_4
,xem.column_name column_name
,xem.column_title PROMPT
,utc.data_type data_type
FROM xla_entity_id_mappings xid
,xla_event_mappings_vl xem
,user_tab_columns utc
WHERE xid.application_id = p_application_id
AND xid.entity_code = p_entity_code
AND xem.application_id = p_application_id
AND xem.entity_code = p_entity_code
AND xem.event_class_code = p_event_class_code
AND utc.table_name = p_reporting_view_name
AND utc.column_name = xem.column_name)
ORDER BY xem.user_sequence;
' SELECT ' ||
' NULL dummy ' ||
l_col_string ||
' FROM ' ||
' DUAL dual ' ||
l_view_name ||
' WHERE ROWNUM = 1 ' ;
p_select_str := l_col_string;
(p_msg => 'p_select_str = '||p_select_str
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT application_column_name
INTO l_balance_segment
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_BALANCING';
SELECT application_column_name
INTO l_account_segment
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_ACCOUNT';
SELECT application_column_name
INTO l_cost_center_segment
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND attribute_value = 'Y'
AND segment_attribute_type = 'FA_COST_CTR';
SELECT application_column_name
INTO l_management_segment
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_MANAGEMENT';
SELECT application_column_name
INTO l_intercompany_segment
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_INTERCOMPANY';
SELECT
CASE data_type_code
WHEN 'C' THEN 'ADV.ANALYTICAL_DETAIL_CHAR_'||TO_CHAR(grouping_order)
WHEN 'D' THEN 'ADV.ANALYTICAL_DETAIL_DATE_'||TO_CHAR(grouping_order)
WHEN 'N' THEN 'ADV.ANALYTICAL_DETAIL_NUMBER_'||TO_CHAR(grouping_order)
ELSE NULL
END CASE
INTO l_column_name
FROM xla_analytical_dtls_b
WHERE analytical_Detail_code = p_anc_detail_code;
' select 1 from xla_ae_header_details ahd, xla_analytical_dtl_vals adv '||
' where ahd.ae_header_id = '||p_table_alias||'.ae_header_id '||
' and adv.analytical_detail_value_id = ahd.analytical_detail_value_id '||
' and adv.'||l_column_name||' = '''||p_anc_detail_value||''''||
' )';
' select 1 from xla_ae_line_details ald, xla_analytical_dtl_vals adv '||
' where ald.ae_header_id = '||p_table_alias||'.ae_header_id '||
' and ald.ae_line_num = '||p_table_alias||'.ae_line_num '||
' and adv.analytical_detail_value_id = ald.analytical_detail_value_id '||
' and adv.'||l_column_name||' = '''||p_anc_detail_value||''''||
' )';
select ledger_id
into l_ledger_id
from gl_ledgers
where ledger_category_code = 'PRIMARY'
and ledger_id = p_ledger_id
and rownum = 1;
select distinct primary_ledger_id
into l_ledger_id_out
from xla_ledger_relationships_v
where ledger_id = p_ledger_id;
select ledger_id
into l_ledger_id
from gl_ledger_set_assignments glsa
where glsa.ledger_id <> p_ledger_id
and glsa.ledger_set_id = p_ledger_id
and rownum = 1;
select distinct primary_ledger_id
into l_ledger_id_out
from xla_ledger_relationships_v
where ledger_id = l_ledger_id;
SELECT object_type_code
INTO l_object_type_code
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
(SELECT DISTINCT
aeh.application_id APPLICATION_ID
,xet.entity_code ENTITY_CODE
,xet.event_class_code EVENT_CLASS_CODE
,xeca.reporting_view_name REPORTING_VIEW_NAME
FROM xla_ae_headers aeh
,xla_event_types_b xet
,xla_event_class_attrs xeca
WHERE xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND xeca.application_id = aeh.application_id
AND xeca.entity_code = xet.entity_code
AND xeca.event_class_code = xet.event_class_code
AND aeh.ledger_id = p_ledger_id
AND aeh.application_id = p_resp_application_id );
(SELECT xid.transaction_id_col_name_1 trx_col_1
,xid.transaction_id_col_name_2 trx_col_2
,xid.transaction_id_col_name_3 trx_col_3
,xid.transaction_id_col_name_4 trx_col_4
,xid.source_id_col_name_1 src_col_1
,xid.source_id_col_name_2 src_col_2
,xid.source_id_col_name_3 src_col_3
,xid.source_id_col_name_4 src_col_4
,xem.column_name column_name
,xem.column_title PROMPT
,utc.data_type data_type
FROM xla_entity_id_mappings xid
,xla_event_mappings_vl xem
,user_tab_columns utc
WHERE xid.application_id = cur_trx.application_id
AND xid.entity_code = cur_trx.entity_code
AND xem.application_id = cur_trx.application_id
AND xem.entity_code = cur_trx.entity_code
AND xem.event_class_code = cur_trx.event_class_code
AND utc.table_name = cur_trx.reporting_view_name
AND utc.column_name = xem.column_name
ORDER BY xem.user_sequence)
LOOP
l_index := l_index + 1;
''' THEN ( SELECT '||l_col_string||
' FROM '||l_view_name ||' WHERE '|| l_join_string ||')';
SELECT p_table_alias||'.'||application_column_name seg
FROM fnd_id_flex_segments
WHERE application_id =101
AND id_flex_code ='GL#'
AND id_flex_num = p_coa_id
ORDER BY segment_num ;
SELECT '||'''||concatenated_segment_delimiter||'''||'
INTO l_conc_seg_delimiter
FROM fnd_id_flex_structures
WHERE application_id =101
AND id_flex_code ='GL#'
AND id_flex_num = p_coa_id;