[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 |
| 02/16/2009 N. K. Surana Overloading function get_transaction_id |
| to handle more than 50 event classes per |
| application id required for FSAH Customers.|
| 3-Mar-2010 rajose 9323360 to implement caching for CCID desc |
| function by using PLSQL nested table |
| hashing |
| 26/07/2011 NKSURANA 12663084: Removed the exists check on |
| xla_ae_headers when fetching the |
| reporting view names in get_transaction_id |
+===========================================================================*/
--=============================================================================
-- **************** declaraions ********************
--=============================================================================
TYPE t_rec IS RECORD
(f1 VARCHAR2(80)
,f2 VARCHAR2(80));
select validation_type
from fnd_flex_value_sets
where flex_value_set_id = p_flex_value_set_id ;
SELECT application_column_name
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND application_id = 101
AND flex_value_set_id =
( SELECT parent_flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_flex_value_set_id
);
SELECT display_flag
FROM fnd_id_flex_segments fid
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND application_column_name = p_segment_code;
,p_select_str OUT NOCOPY VARCHAR2
,p_from_str OUT NOCOPY VARCHAR2
,p_where_str OUT NOCOPY VARCHAR2) IS
-- Split the join between Entity Mapping and Event Mappings as Report Ends in Error
-- with SQL Syntax erro when User Transaction Identifiers are not provided in
-- Accounting Event Class Options Window (Added for Bug 11691458)
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 */
);
(SELECT xem.column_name column_name
,xem.column_title PROMPT
,utc.data_type data_type
FROM (SELECT t.table_name , t.column_name ,t.data_type
FROM user_tab_columns t , user_objects o
WHERE t.table_name = o.object_name
AND o.object_name = p_reporting_view_name
AND o.object_type <> 'SYNONYM'
UNION ALL
SELECT dt.table_name, dt.column_name , dt.data_type
FROM dba_tab_columns dt
WHERE (dt.table_name , dt.owner)
IN ( SELECT s.table_name , s.table_owner
FROM user_synonyms s , user_objects o
WHERE 1 = 1
AND o.object_name = p_reporting_view_name
AND o.object_type = 'SYNONYM'
AND s.synonym_name = o.object_name ) ) utc,
xla_event_mappings_vl xem
WHERE xem.application_id = p_application_id
AND xem.entity_code = p_entity_code
AND xem.event_class_code = p_event_class_code
AND utc.column_name = xem.column_name
ORDER BY xem.user_sequence)
LOOP
l_index := l_index + 1;
' 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
xet.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_event_types_b xet
,xla_event_class_attrs xeca
WHERE xeca.entity_code = xet.entity_code
AND xeca.event_class_code = xet.event_class_code
AND xeca.application_id = p_resp_application_id
AND xet.application_id = xeca.application_id) ; --added for bug 7688085,7707717, removed xah for bug 12663084
(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;
(SELECT xem.column_name column_name
,xem.column_title PROMPT
,utc.data_type data_type
FROM (SELECT t.table_name , t.column_name ,t.data_type
FROM user_tab_columns t , user_objects o
WHERE t.table_name = o.object_name
AND o.object_name = cur_trx.reporting_view_name
AND o.object_type <> 'SYNONYM'
UNION ALL
SELECT dt.table_name, dt.column_name , dt.data_type
FROM dba_tab_columns dt
WHERE (dt.table_name , dt.owner)
IN ( SELECT s.table_name , s.table_owner
FROM user_synonyms s , user_objects o
WHERE 1 = 1
AND o.object_name = cur_trx.reporting_view_name
AND o.object_type = 'SYNONYM'
AND s.synonym_name = o.object_name ) ) utc,
xla_event_mappings_vl xem
WHERE 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.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 DISTINCT
xet.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_event_types_b xet
,xla_event_class_attrs xeca
WHERE xeca.entity_code = xet.entity_code
AND xeca.event_class_code = xet.event_class_code
AND xeca.application_id = p_resp_application_id
AND xet.application_id = xeca.application_id) ; --added for bug 7688085,7707717, removed xah for bug 12663084
(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;
(SELECT xem.column_name column_name
,xem.column_title PROMPT
,utc.data_type data_type
FROM (SELECT t.table_name , t.column_name ,t.data_type
FROM user_tab_columns t , user_objects o
WHERE t.table_name = o.object_name
AND o.object_name = cur_trx.reporting_view_name
AND o.object_type <> 'SYNONYM'
UNION ALL
SELECT dt.table_name, dt.column_name , dt.data_type
FROM dba_tab_columns dt
WHERE (dt.table_name , dt.owner)
IN ( SELECT s.table_name , s.table_owner
FROM user_synonyms s , user_objects o
WHERE 1 = 1
AND o.object_name = cur_trx.reporting_view_name
AND o.object_type = 'SYNONYM'
AND s.synonym_name = o.object_name ) ) utc,
xla_event_mappings_vl xem
WHERE 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.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 ||')';
''' 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;