[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_date DATE;
g_last_update_login INTEGER;
g_last_updated_by INTEGER;
DELETE
FROM xla_amb_setup_errors
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND product_rule_code IS NULL;
Xla_amb_setup_err_pkg.insert_errors;
SELECT 'x'
FROM xla_extract_objects e
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code;
SELECT h.entity_code, h.event_class_code
FROM xla_prod_acct_headers h
WHERE h.application_id = p_application_id
AND h.amb_context_code = p_amb_context_code
AND h.product_rule_type_code = p_product_rule_type_code
AND h.product_rule_code = p_product_rule_code
AND h.accounting_required_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_extract_objects e
WHERE e.application_id = h.application_id
AND e.entity_code = h.entity_code
AND e.event_class_code = h.event_class_code);
SELECT object_name
,object_type_code
,C_REF_OBJECT_FLAG_N ref_object_flag
FROM xla_extract_objects e
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND not exists (SELECT 'x'
FROM xla_extract_objects_gt o
WHERE o.object_name = e.object_name)
--
-- Get all reference objects for the event class that are not defined in the
-- database
UNION ALL
SELECT r.reference_object_name
,e.object_type_code
,C_REF_OBJECT_FLAG_Y ref_object_flag
FROM xla_reference_objects r
,xla_extract_objects e
WHERE r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND e.application_id = r.application_id
AND e.entity_code = r.entity_code
AND e.event_class_code = r.event_class_code
AND e.object_name = r.object_name
AND not exists (SELECT 'x'
FROM xla_reference_objects_gt o
WHERE o.reference_object_name = r.reference_object_name);
SELECT e.entity_code
,e.event_class_code
,e.object_name
,e.object_type_code
,C_REF_OBJECT_FLAG_N ref_object_flag
FROM xla_extract_objects e, xla_prod_acct_headers h
WHERE h.application_id = p_application_id
AND h.amb_context_code = p_amb_context_code
AND h.product_rule_type_code = p_product_rule_type_code
AND h.product_rule_code = p_product_rule_code
AND h.accounting_required_flag = 'Y'
AND e.application_id = h.application_id
AND e.entity_code = h.entity_code
AND e.event_class_code = h.event_class_code
AND not exists (SELECT 'x'
FROM xla_extract_objects_gt o
WHERE o.object_name = e.object_name)
UNION ALL
SELECT r.entity_code
,r.event_class_code
,r.reference_object_name
,e.object_type_code
,C_REF_OBJECT_FLAG_Y ref_object_flag
FROM xla_reference_objects r,
xla_extract_objects e,
xla_prod_acct_headers h
WHERE h.application_id = p_application_id
AND h.amb_context_code = p_amb_context_code
AND h.product_rule_type_code = p_product_rule_type_code
AND h.product_rule_code = p_product_rule_code
AND h.accounting_required_flag = 'Y'
AND r.application_id = h.application_id
AND r.entity_code = h.entity_code
AND r.event_class_code = h.event_class_code
AND e.application_id = r.application_id
AND e.entity_code = r.entity_code
AND e.event_class_code = r.event_class_code
AND not exists (SELECT 'x'
FROM xla_reference_objects_gt o
WHERE o.reference_object_name = r.reference_object_name);
| This routine is called to insert all sources for an event class into |
| a global temporary table before calling validate_sources_with_extract |
| |
+======================================================================*/
FUNCTION Validate_sources
(p_application_id IN NUMBER
,p_entity_code IN VARCHAR2
,p_event_class_code IN VARCHAR2)
RETURN BOOLEAN
IS
-- Variable Declaration
l_application_id NUMBER(15);
SELECT 'x'
FROM xla_evt_class_sources_gt
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code;
INSERT INTO xla_evt_class_sources_gt
(application_id
,entity_code
,event_class_code
,source_application_id
,source_code
,source_datatype_code,source_level_code)
(SELECT e.application_id
,e.entity_code
,e.event_class_code
,e.source_application_id
,e.source_code
,decode(s.datatype_code,'N','NUMBER',
'C','VARCHAR2', 'D','DATE') source_datatype_code,
decode(s.translated_flag,'N',
decode(e.source_code,'LANGUAGE',
decode(e.level_code,'H','HEADER_MLS','L','LINE_MLS'),
decode(e.level_code,'H',
'HEADER','L','LINE')),
'Y',
decode(e.level_code,'H','HEADER_MLS','L','LINE_MLS'))
source_level_code
FROM xla_event_sources e, xla_sources_b s
WHERE e.source_application_id = s.application_id
AND e.source_code = s.source_code
AND e.source_type_code = s.source_type_code
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code);
SELECT g.source_application_id, g.source_code,
o.object_name extract_object_name,
o.object_type_code extract_object_type,
o.always_populated_flag extract_object_pop_flag,
g.source_datatype_code column_datatype_code
FROM xla_evt_class_sources_gt g, xla_extract_objects o,
xla_extract_objects_gt og
WHERE g.application_id = o.application_id
AND g.entity_code = o.entity_code
AND g.event_class_code = o.event_class_code
AND g.source_level_code = o.object_type_code
AND g.source_application_id = o.application_id
AND og.object_name = o.object_name
AND EXISTS (
-- SELECT /*+ no_unnest */ --1 --added for bug13344804
/* FROM dba_tab_columns t
WHERE og.owner = t.owner
AND o.object_name = t.table_name
AND t.column_name = g.source_code
AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
)
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND o.always_populated_flag = 'Y'
AND g.extract_object_name IS NULL; */
SELECT g.source_application_id, g.source_code,
o.object_name extract_object_name,
o.object_type_code extract_object_type,
o.always_populated_flag extract_object_pop_flag,
g.source_datatype_code column_datatype_code
FROM xla_evt_class_sources_gt g, xla_extract_objects o,
xla_extract_objects_gt og
WHERE g.application_id = o.application_id
AND g.entity_code = o.entity_code
AND g.event_class_code = o.event_class_code
AND g.source_level_code = o.object_type_code
AND g.source_application_id = o.application_id
AND og.object_name = o.object_name
AND EXISTS (
SELECT /*+ no_unnest */ 1 --added for bug13344804
FROM dba_tab_columns t,user_objects uo
WHERE og.owner = t.owner
AND o.object_name = t.table_name
AND uo.object_name = o.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = g.source_code
AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
UNION ALL
--SELECT /*+ no_unnest */ 1 --added for bug13344804
--FROM dba_tab_columns t,user_synonyms s,user_objects uo
--WHERE s.table_owner = t.owner
--AND s.table_name = t.table_name
--AND s.synonym_name =o.object_name
--AND uo.object_name = o.object_name
--AND uo.object_type = 'SYNONYM'
--AND t.column_name = g.source_code
--AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
SELECT /*+ no_unnest */ 1
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = O.OBJECT_NAME
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = G.SOURCE_CODE
and DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
)
)
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND o.always_populated_flag = 'Y'
AND g.extract_object_name IS NULL;
SELECT g.source_application_id, g.source_code, r.reference_object_name extract_object_name,
o.object_type_code extract_object_type,
r.always_populated_flag extract_object_pop_flag,
g.source_datatype_code column_datatype_code,
r.join_condition, r.linked_to_ref_obj_name
FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
xla_reference_objects_gt og
WHERE g.application_id = r.application_id
AND g.entity_code = r.entity_code
AND g.event_class_code = r.event_class_code
AND g.source_application_id = r.reference_object_appl_id
AND g.source_level_code = o.object_type_code
AND r.application_id = o.application_id
AND r.entity_code = o.entity_code
AND r.event_class_code = o.event_class_code
AND r.object_name = o.object_name
AND og.reference_object_name = r.reference_object_name
AND EXISTS (
-- SELECT /*+ no_unnest */-- 1 --added for bug13344804
/* FROM dba_tab_columns t
WHERE og.owner = t.owner
AND r.reference_object_name = t.table_name
AND t.column_name = g.source_code
AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
)
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND r.always_populated_flag = 'Y'
AND g.extract_object_name IS NULL;
SELECT g.source_application_id, g.source_code, r.reference_object_name extract_object_name,
o.object_type_code extract_object_type,
r.always_populated_flag extract_object_pop_flag,
g.source_datatype_code column_datatype_code,
r.join_condition, r.linked_to_ref_obj_name
FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
xla_reference_objects_gt og
WHERE g.application_id = r.application_id
AND g.entity_code = r.entity_code
AND g.event_class_code = r.event_class_code
AND g.source_application_id = r.reference_object_appl_id
AND g.source_level_code = o.object_type_code
AND r.application_id = o.application_id
AND r.entity_code = o.entity_code
AND r.event_class_code = o.event_class_code
AND r.object_name = o.object_name
AND og.reference_object_name = r.reference_object_name
AND EXISTS (
SELECT /*+ no_unnest */ 1 --added for bug13344804
FROM dba_tab_columns t,user_objects uo
WHERE og.owner = t.owner
AND r.reference_object_name = t.table_name
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = g.source_code
AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
UNION ALL
--SELECT /*+ no_unnest */ 1 --added for bug13344804
--FROM dba_tab_columns t,user_synonyms s,user_objects uo
--WHERE s.table_owner = t.owner
--AND s.table_name = t.table_name
--AND s.synonym_name =r.reference_object_name
--AND uo.object_name = r.reference_object_name
--AND uo.object_type = 'SYNONYM'
--AND t.column_name = g.source_code
--AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
SELECT /*+ no_unnest */ 1
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = r.reference_object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = G.SOURCE_CODE
and DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
)
)
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND r.always_populated_flag = 'Y'
AND g.extract_object_name IS NULL;
SELECT g.source_application_id, g.source_code,
o.object_name extract_object_name,
o.object_type_code extract_object_type,
o.always_populated_flag extract_object_pop_flag,
g.source_datatype_code column_datatype_code
FROM xla_evt_class_sources_gt g, xla_extract_objects o,
xla_extract_objects_gt og
WHERE g.application_id = o.application_id
AND g.entity_code = o.entity_code
AND g.event_class_code = o.event_class_code
AND g.source_level_code = o.object_type_code
AND g.source_application_id = o.application_id
AND og.object_name = o.object_name
AND EXISTS (
-- SELECT /*+ no_unnest */-- 1 --added for bug13344804
/* FROM dba_tab_columns t
WHERE og.owner = t.owner
AND o.object_name = t.table_name
AND t.column_name = g.source_code
AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = g.source_datatype_code
)
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND g.extract_object_name IS NULL; */
SELECT g.source_application_id, g.source_code,
o.object_name extract_object_name,
o.object_type_code extract_object_type,
o.always_populated_flag extract_object_pop_flag,
g.source_datatype_code column_datatype_code
FROM xla_evt_class_sources_gt g, xla_extract_objects o,
xla_extract_objects_gt og
WHERE g.application_id = o.application_id
AND g.entity_code = o.entity_code
AND g.event_class_code = o.event_class_code
AND g.source_level_code = o.object_type_code
AND g.source_application_id = o.application_id
AND og.object_name = o.object_name
AND EXISTS (
SELECT /*+ no_unnest */ 1 --added for bug13344804
FROM dba_tab_columns t,user_objects uo
WHERE og.owner = t.owner
AND o.object_name = t.table_name
AND uo.object_name = o.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = g.source_code
AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = g.source_datatype_code
UNION ALL
--SELECT /*+ no_unnest */ 1 --added for bug13344804
--FROM dba_tab_columns t,user_synonyms s,user_objects uo
--WHERE s.table_owner = t.owner
--AND s.table_name = t.table_name
--AND s.synonym_name =o.object_name
--AND uo.object_name = o.object_name
--AND uo.object_type = 'SYNONYM'
--AND t.column_name = g.source_code
--AND DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = g.source_datatype_code
SELECT /*+ no_unnest */ 1
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = o.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = G.SOURCE_CODE
and DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
)
)
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND g.extract_object_name IS NULL;
SELECT g.source_application_id, g.source_code,
r.reference_object_name extract_object_name,
o.object_type_code extract_object_type,
r.always_populated_flag extract_object_pop_flag,
g.source_datatype_code column_datatype_code,
r.join_condition, r.linked_to_ref_obj_name
FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
xla_reference_objects_gt og
WHERE g.application_id = r.application_id
AND g.entity_code = r.entity_code
AND g.event_class_code = r.event_class_code
AND g.source_application_id = r.reference_object_appl_id
AND g.source_level_code = o.object_type_code
AND r.application_id = o.application_id
AND r.entity_code = o.entity_code
AND r.event_class_code = o.event_class_code
AND r.object_name = o.object_name
AND og.reference_object_name = r.reference_object_name
AND EXISTS (
-- SELECT /*+ no_unnest */ --1 --added for bug13344804
/* FROM dba_tab_columns t
WHERE og.owner = t.owner
AND r.reference_object_name = t.table_name
AND t.column_name = g.source_code
AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
)
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND g.extract_object_name IS NULL; */
SELECT g.source_application_id, g.source_code,
r.reference_object_name extract_object_name,
o.object_type_code extract_object_type,
r.always_populated_flag extract_object_pop_flag,
g.source_datatype_code column_datatype_code,
r.join_condition, r.linked_to_ref_obj_name
FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
xla_reference_objects_gt og
WHERE g.application_id = r.application_id
AND g.entity_code = r.entity_code
AND g.event_class_code = r.event_class_code
AND g.source_application_id = r.reference_object_appl_id
AND g.source_level_code = o.object_type_code
AND r.application_id = o.application_id
AND r.entity_code = o.entity_code
AND r.event_class_code = o.event_class_code
AND r.object_name = o.object_name
AND og.reference_object_name = r.reference_object_name
AND EXISTS (
SELECT /*+ no_unnest */ 1 --added for bug13344804
FROM dba_tab_columns t,user_objects uo
WHERE og.owner = t.owner
AND r.reference_object_name = t.table_name
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = g.source_code
AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
UNION ALL
--SELECT /*+ no_unnest */ 1 --added for bug13344804
--FROM dba_tab_columns t,user_synonyms s,user_objects uo
--WHERE s.table_owner = t.owner
--AND s.table_name = t.table_name
--AND s.synonym_name =r.reference_object_name
--AND uo.object_name = r.reference_object_name
--AND uo.object_type = 'SYNONYM'
--AND t.column_name = g.source_code
--AND DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) = g.source_datatype_code
SELECT /*+ no_unnest */ 1
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = r.reference_object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = G.SOURCE_CODE
and DECODE(T.DATA_TYPE,'CHAR','VARCHAR2',T.DATA_TYPE) = G.SOURCE_DATATYPE_CODE
)
)
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND g.extract_object_name IS NULL;
SELECT DISTINCT
g.source_application_id, g.source_code,
o.object_name extract_object_name,
o.object_type_code extract_object_type,
o.always_populated_flag extract_object_pop_flag,
-- 4713242 Performance Fix
(SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
FROM dba_tab_columns T
WHERE og.owner = t.owner
AND o.object_name = t.table_name
AND t.column_name = g.source_code)
FROM xla_evt_class_sources_gt g, xla_extract_objects o,
xla_extract_objects_gt og
WHERE g.application_id = o.application_id
AND g.entity_code = o.entity_code
AND g.event_class_code = o.event_class_code
AND g.source_level_code = o.object_type_code
AND g.source_application_id = o.application_id
AND og.object_name = o.object_name
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND g.extract_object_name IS NULL
AND EXISTS (SELECT /*+ no_unnest */-- 1 --added for bug13344804
/* FROM dba_tab_columns t
WHERE og.owner = t.owner
AND o.object_name = t.table_name
AND t.column_name = g.source_code); */
SELECT DISTINCT
g.source_application_id, g.source_code,
o.object_name extract_object_name,
o.object_type_code extract_object_type,
o.always_populated_flag extract_object_pop_flag,
-- 4713242 Performance Fix
(SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
FROM dba_tab_columns T,user_objects uo
WHERE og.owner = t.owner
AND uo.object_name = t.table_name
AND uo.object_name = o.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = g.source_code
UNION ALL
SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
FROM dba_tab_columns T,user_objects uo,user_synonyms s
WHERE t.table_name = s.table_name
AND s.synonym_name = uo.object_name
AND s.table_owner = t.owner
AND uo.object_name = o.object_name
AND uo.object_type = 'SYNONYM'
AND t.column_name = g.source_code
)
FROM xla_evt_class_sources_gt g, xla_extract_objects o,
xla_extract_objects_gt og
WHERE g.application_id = o.application_id
AND g.entity_code = o.entity_code
AND g.event_class_code = o.event_class_code
AND g.source_level_code = o.object_type_code
AND g.source_application_id = o.application_id
AND og.object_name = o.object_name
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND g.extract_object_name IS NULL
AND EXISTS (SELECT /*+ no_unnest */ 1 --added for bug13344804
FROM dba_tab_columns t,user_objects uo
WHERE og.owner = t.owner
AND o.object_name = t.table_name
AND uo.object_name = o.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = g.source_code
UNION ALL
--SELECT /*+ no_unnest */ 1 --added for bug13344804
--FROM dba_tab_columns t,user_synonyms s,user_objects uo
--WHERE s.table_owner = t.owner
--AND s.table_name = t.table_name
--AND s.synonym_name =o.object_name
--AND uo.object_name = o.object_name
--AND uo.object_type = 'SYNONYM'
--AND t.column_name = g.source_code
SELECT /*+ no_unnest */ 1
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = o.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = G.SOURCE_CODE
)
);
SELECT DISTINCT g.source_application_id
,g.source_code
,r.reference_object_name extract_object_name
,o.object_type_code extract_object_type
,o.always_populated_flag extract_object_pop_flag
-- 4713242 Performance Fix
,(SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
FROM dba_tab_columns T
WHERE og.owner = t.owner
AND r.reference_object_name = t.table_name
AND t.column_name = g.source_code)
,r.join_condition
FROM xla_evt_class_sources_gt g
,xla_reference_objects r
,xla_extract_objects o
,xla_reference_objects_gt og
WHERE g.application_id = r.application_id
AND g.entity_code = r.entity_code
AND g.event_class_code = r.event_class_code
AND g.source_level_code = o.object_type_code
AND r.application_id = o.application_id
AND r.entity_code = o.entity_code
AND r.event_class_code = o.event_class_code
AND r.object_name = o.object_name
AND og.reference_object_name = r.reference_object_name
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND g.extract_object_name IS NULL
AND EXISTS (SELECT /*+ no_unnest */-- 1 --added for bug13344804
/* FROM dba_tab_columns t
WHERE og.owner = t.owner
AND r.reference_object_name = t.table_name
AND t.column_name = g.source_code); */
SELECT DISTINCT g.source_application_id
,g.source_code
,r.reference_object_name extract_object_name
,o.object_type_code extract_object_type
,o.always_populated_flag extract_object_pop_flag
-- 4713242 Performance Fix
,(SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
FROM dba_tab_columns T,user_objects uo
WHERE og.owner = t.owner
AND r.reference_object_name = t.table_name
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = g.source_code
UNION ALL
SELECT DECODE(t.data_type,'CHAR','VARCHAR2',t.data_type) COLUMN_DATATYPE_CODE
FROM dba_tab_columns T,user_objects uo,user_synonyms s
WHERE s.table_owner = t.owner
AND s.table_name = t.table_name
AND s.synonym_name = uo.object_name
AND uo.object_name = r.reference_object_name
AND uo.object_type = 'SYNONYM'
AND t.column_name = g.source_code)
,r.join_condition
FROM xla_evt_class_sources_gt g
,xla_reference_objects r
,xla_extract_objects o
,xla_reference_objects_gt og
WHERE g.application_id = r.application_id
AND g.entity_code = r.entity_code
AND g.event_class_code = r.event_class_code
AND g.source_level_code = o.object_type_code
AND r.application_id = o.application_id
AND r.entity_code = o.entity_code
AND r.event_class_code = o.event_class_code
AND r.object_name = o.object_name
AND og.reference_object_name = r.reference_object_name
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND g.extract_object_name IS NULL
AND EXISTS (SELECT /*+ no_unnest */ 1 --added for bug13344804
FROM dba_tab_columns t,user_objects uo
WHERE og.owner = t.owner
AND r.reference_object_name = t.table_name
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = g.source_code
UNION ALL
--SELECT /*+ no_unnest */ 1 --added for bug13344804
--FROM dba_tab_columns t,user_synonyms s,user_objects uo
--WHERE s.table_owner = t.owner
--AND s.table_name = t.table_name
--AND s.synonym_name =r.reference_object_name
--AND uo.object_name = r.reference_object_name
--AND uo.object_type = 'SYNONYM'
--AND t.column_name = g.source_code
SELECT /*+ no_unnest */ 1
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = r.reference_object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = G.SOURCE_CODE
)
);
SELECT source_application_id, source_code, source_level_code
FROM xla_evt_class_sources_gt g
WHERE g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code
AND extract_object_name IS NULL;
SELECT source_application_id, source_code, extract_object_name,
extract_object_type_code
FROM xla_evt_class_sources_gt g
WHERE source_datatype_code <> column_datatype_code
AND extract_object_name IS NOT NULL
AND g.application_id = p_application_id
AND g.entity_code = p_entity_code
AND g.event_class_code = p_event_class_code;
UPDATE xla_evt_class_sources_gt gt
SET gt.extract_object_name = l_array_pop_object_name(i),
gt.extract_object_type_code = l_array_pop_object_type(i),
gt.always_populated_flag = l_array_pop_pop_flag(i),
gt.column_datatype_code = l_array_pop_col_datatype(i),
gt.reference_object_flag = C_REF_OBJECT_FLAG_N
WHERE gt.source_application_id = l_array_pop_source_appl_id(i)
AND gt.source_code = l_array_pop_source_code(i)
AND gt.application_id = p_application_id
AND gt.entity_code = p_entity_code
AND gt.event_class_code = p_event_class_code;
UPDATE xla_evt_class_sources_gt gt
SET gt.extract_object_name = l_array_ref_pop_object_name(i),
gt.extract_object_type_code = l_array_ref_pop_object_type(i),
gt.always_populated_flag = l_array_ref_pop_pop_flag(i),
gt.column_datatype_code = l_array_ref_pop_col_datatype(i),
gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
gt.join_condition = l_array_ref_pop_join_condition(i)
WHERE gt.source_application_id = l_array_ref_pop_source_appl_id(i)
AND gt.source_code = l_array_ref_pop_source_code(i)
AND gt.application_id = p_application_id
AND gt.entity_code = p_entity_code
AND gt.event_class_code = p_event_class_code
AND l_array_ref_pop_linked_obj(i) IS NULL;
UPDATE xla_evt_class_sources_gt gt
SET gt.extract_object_name = l_array_ref_pop_object_name(i),
gt.extract_object_type_code = l_array_ref_pop_object_type(i),
gt.always_populated_flag = l_array_ref_pop_pop_flag(i),
gt.column_datatype_code = l_array_ref_pop_col_datatype(i),
gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
gt.join_condition = l_array_ref_pop_join_condition(i)
WHERE gt.source_application_id = l_array_ref_pop_source_appl_id(i)
AND gt.source_code = l_array_ref_pop_source_code(i)
AND gt.application_id = p_application_id
AND gt.entity_code = p_entity_code
AND gt.event_class_code = p_event_class_code
AND gt.extract_object_name IS NULL
AND l_array_ref_pop_linked_obj(i) IS NOT NULL;
UPDATE xla_evt_class_sources_gt gt
SET gt.extract_object_name = l_array_object_name(i),
gt.extract_object_type_code = l_array_object_type(i),
gt.always_populated_flag = l_array_pop_flag(i),
gt.column_datatype_code = l_array_col_datatype(i),
gt.reference_object_flag = C_REF_OBJECT_FLAG_N
WHERE gt.source_application_id = l_array_source_appl_id(i)
AND gt.source_code = l_array_source_code(i)
AND gt.application_id = p_application_id
AND gt.entity_code = p_entity_code
AND gt.event_class_code = p_event_class_code;
UPDATE xla_evt_class_sources_gt gt
SET gt.extract_object_name = l_array_ref_object_name(i),
gt.extract_object_type_code = l_array_ref_object_type(i),
gt.always_populated_flag = l_array_ref_pop_flag(i),
gt.column_datatype_code = l_array_ref_col_datatype(i),
gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
gt.join_condition = l_array_ref_join_condition(i)
WHERE gt.source_application_id = l_array_ref_source_appl_id(i)
AND gt.source_code = l_array_ref_source_code(i)
AND gt.application_id = p_application_id
AND gt.entity_code = p_entity_code
AND gt.event_class_code = p_event_class_code
AND l_array_ref_linked_obj(i) IS NULL;
UPDATE xla_evt_class_sources_gt gt
SET gt.extract_object_name = l_array_ref_object_name(i),
gt.extract_object_type_code = l_array_ref_object_type(i),
gt.always_populated_flag = l_array_ref_pop_flag(i),
gt.column_datatype_code = l_array_ref_col_datatype(i),
gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
gt.join_condition = l_array_ref_join_condition(i)
WHERE gt.source_application_id = l_array_ref_source_appl_id(i)
AND gt.source_code = l_array_ref_source_code(i)
AND gt.application_id = p_application_id
AND gt.entity_code = p_entity_code
AND gt.event_class_code = p_event_class_code
AND gt.extract_object_name IS NULL
AND l_array_ref_linked_obj(i) IS NOT NULL;
UPDATE xla_evt_class_sources_gt gt
SET gt.extract_object_name = l_array_dt_object_name(j),
gt.extract_object_type_code = l_array_dt_object_type(j),
gt.always_populated_flag = l_array_dt_pop_flag(j),
gt.column_datatype_code = l_array_dt_col_datatype(j),
gt.reference_object_flag = C_REF_OBJECT_FLAG_N
WHERE gt.source_application_id = l_array_dt_source_appl_id(j)
AND gt.source_code = l_array_dt_source_code(j)
AND gt.application_id = p_application_id
AND gt.entity_code = p_entity_code
AND gt.event_class_code = p_event_class_code;
UPDATE xla_evt_class_sources_gt gt
SET gt.extract_object_name = l_array_ref_dt_object_name(j),
gt.extract_object_type_code = l_array_ref_dt_object_type(j),
gt.always_populated_flag = l_array_ref_dt_pop_flag(j),
gt.column_datatype_code = l_array_ref_dt_col_datatype(j),
gt.reference_object_flag = C_REF_OBJECT_FLAG_Y,
gt.join_condition = l_array_ref_dt_join_condition(j)
WHERE gt.source_application_id = l_array_ref_dt_source_appl_id(j)
AND gt.source_code = l_array_ref_dt_source_code(j)
AND gt.application_id = p_application_id
AND gt.entity_code = p_entity_code
AND gt.event_class_code = p_event_class_code;
SELECT distinct ext.object_name, C_REF_OBJECT_FLAG_N reference_object_flag
FROM xla_extract_objects ext, xla_prod_acct_headers hdr
WHERE ext.application_id = hdr.application_id
AND ext.entity_code = hdr.entity_code
AND ext.event_class_code = hdr.event_class_code
AND hdr.application_id = p_application_id
AND hdr.amb_context_code = p_amb_context_code
AND hdr.product_rule_type_code = p_product_rule_type_code
AND hdr.product_rule_code = p_product_rule_code
UNION ALL
SELECT distinct rfr.reference_object_name, C_REF_OBJECT_FLAG_Y reference_object_flag
FROM xla_reference_objects rfr, xla_prod_acct_headers hdr
WHERE rfr.application_id = hdr.application_id
AND rfr.entity_code = hdr.entity_code
AND rfr.event_class_code = hdr.event_class_code
AND hdr.application_id = p_application_id
AND hdr.amb_context_code = p_amb_context_code
AND hdr.product_rule_type_code = p_product_rule_type_code
AND hdr.product_rule_code = p_product_rule_code;
SELECT usr.object_type
FROM user_objects usr
WHERE usr.object_name = l_object_name;
SELECT syn.table_owner
FROM user_synonyms syn
WHERE syn.synonym_name = l_object_name;
DELETE FROM xla_extract_objects_gt;
DELETE FROM xla_reference_objects_gt;
SELECT user
INTO l_user
FROM DUAL;
INSERT
INTO xla_extract_objects_gt
(object_name
,owner)
SELECT ext.object_name, l_user
FROM xla_extract_objects ext
WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
FROM user_objects usr
WHERE ext.object_name = usr.object_name
AND usr.object_type <> 'SYNONYM' )
AND ext.application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code;
INSERT
INTO xla_reference_objects_gt
(reference_object_name
,owner)
SELECT rfr.reference_object_name, l_user
FROM xla_reference_objects rfr
WHERE
EXISTS (SELECT /*+ no_unnest */ 'c'
FROM user_objects usr
WHERE rfr.reference_object_name = usr.object_name
AND usr.object_type <> 'SYNONYM' )
AND rfr.application_id = p_application_id
AND rfr.entity_code = p_entity_code
AND rfr.event_class_code = p_event_class_code;
INSERT
INTO xla_extract_objects_gt
(object_name
,owner)
SELECT ext.object_name
,(SELECT syn.table_owner
FROM user_objects usr
,user_synonyms syn
WHERE ext.object_name = usr.object_name
AND ext.object_name = syn.synonym_name
AND usr.object_type = 'SYNONYM')
FROM xla_extract_objects ext
WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
FROM user_objects usr
,user_synonyms syn
WHERE ext.object_name = usr.object_name
AND ext.object_name = syn.synonym_name
AND usr.object_type = 'SYNONYM')
AND ext.application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code;
INSERT
INTO xla_reference_objects_gt
(reference_object_name
,owner)
SELECT rfr.reference_object_name
,(SELECT syn.table_owner
FROM user_objects usr
,user_synonyms syn
-- change rfr.object_name to rfr.reference_object_name, as told by dimple
WHERE rfr.reference_object_name = usr.object_name
AND rfr.reference_object_name = syn.synonym_name
AND usr.object_type = 'SYNONYM')
FROM xla_reference_objects rfr
WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
FROM user_objects usr
,user_synonyms syn
-- change rfr.object_name to rfr.reference_object_name, as told by dimple
WHERE rfr.reference_object_name = usr.object_name
AND rfr.reference_object_name = syn.synonym_name
AND usr.object_type = 'SYNONYM')
AND rfr.application_id = p_application_id
AND rfr.entity_code = p_entity_code
AND rfr.event_class_code = p_event_class_code;
INSERT
INTO xla_extract_objects_gt
(object_name
,owner)
VALUES(l_object_name
,l_user);
INSERT
INTO xla_reference_objects_gt
(reference_object_name
,owner)
VALUES(l_object_name
,l_user);
INSERT
INTO xla_extract_objects_gt
(object_name
,owner)
VALUES(l_object_name
,l_syn_owner);
INSERT
INTO xla_reference_objects_gt
(reference_object_name
,owner)
VALUES(l_object_name
,l_syn_owner);
SELECT distinct extract_object_name, extract_object_type_code
FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND extract_object_name IS NOT NULL
AND extract_object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND t.column_name = 'EVENT_ID'
AND t.data_type = 'NUMBER');
SELECT distinct extract_object_name, extract_object_type_code
FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND extract_object_name IS NOT NULL
AND extract_object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t, user_objects uo
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND uo.object_name = og.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = 'EVENT_ID'
AND t.data_type = 'NUMBER'
UNION ALL
--SELECT 'x'
-- FROM dba_tab_columns t,user_synonyms s,user_objects uo
-- WHERE s.table_owner = t.owner
-- AND s.table_name = t.table_name
-- AND s.synonym_name =og.object_name
-- AND uo.object_name = og.object_name
-- AND uo.object_type = 'SYNONYM'
-- AND t.column_name = 'EVENT_ID'
-- AND t.data_type = 'NUMBER'
SELECT /*+ no_unnest */ 'x'
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = og.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = 'EVENT_ID'
and t.data_type = 'NUMBER'
)
);
SELECT distinct extract_object_name, extract_object_type_code
FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND extract_object_name IS NOT NULL
AND extract_object_type_code IN ('HEADER_MLS','LINE_MLS')
AND extract_object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND t.column_name = 'LANGUAGE'
AND t.data_type = 'VARCHAR2');
SELECT distinct extract_object_name, extract_object_type_code
FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND extract_object_name IS NOT NULL
AND extract_object_type_code IN ('HEADER_MLS','LINE_MLS')
AND extract_object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t, user_objects uo
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND uo.object_name = og.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = 'LANGUAGE'
AND t.data_type = 'VARCHAR2'
UNION ALL
--SELECT 'x'
-- FROM dba_tab_columns t,user_synonyms s,user_objects uo
-- WHERE s.table_owner = t.owner
-- AND s.table_name = t.table_name
-- AND s.synonym_name =og.object_name
-- AND uo.object_name = og.object_name
-- AND uo.object_type = 'SYNONYM'
-- AND t.column_name = 'LANGUAGE'
-- AND t.data_type = 'VARCHAR2'
SELECT /*+ no_unnest */ 'x'
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = og.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = 'LANGUAGE'
and t.data_type = 'VARCHAR2'
)
);
SELECT distinct extract_object_name, extract_object_type_code
FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND extract_object_name IS NOT NULL
AND extract_object_type_code IN ('LINE','LINE_MLS')
AND extract_object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND t.column_name = 'LINE_NUMBER'
AND t.data_type = 'NUMBER');
SELECT distinct extract_object_name, extract_object_type_code
FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND extract_object_name IS NOT NULL
AND extract_object_type_code IN ('LINE','LINE_MLS')
AND extract_object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t, user_objects uo
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND uo.object_name = og.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = 'LINE_NUMBER'
AND t.data_type = 'NUMBER'
UNION ALL
--SELECT 'x'
-- FROM dba_tab_columns t,user_synonyms s,user_objects uo
-- WHERE s.table_owner = t.owner
-- AND s.table_name = t.table_name
-- AND s.synonym_name =og.object_name
-- AND uo.object_name = og.object_name
-- AND uo.object_type = 'SYNONYM'
-- AND t.column_name = 'LINE_NUMBER'
-- AND t.data_type = 'NUMBER'
SELECT /*+ no_unnest */ 'x'
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = og.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = 'LINE_NUMBER'
and t.data_type = 'NUMBER'
)
);
SELECT distinct extract_object_name, extract_object_type_code
FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og, xla_subledgers app
WHERE e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.extract_object_name IS NOT NULL
AND e.extract_object_type_code IN ('LINE','LINE_MLS')
AND e.extract_object_name = og.object_name
AND e.application_id = app.application_id
AND app.alc_enabled_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND t.column_name = 'LEDGER_ID'
AND t.data_type = 'NUMBER'); */
SELECT distinct extract_object_name, extract_object_type_code
FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og, xla_subledgers app
WHERE e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.extract_object_name IS NOT NULL
AND e.extract_object_type_code IN ('LINE','LINE_MLS')
AND e.extract_object_name = og.object_name
AND e.application_id = app.application_id
AND app.alc_enabled_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t, user_objects uo
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND uo.object_name = og.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = 'LEDGER_ID'
AND t.data_type = 'NUMBER'
UNION ALL
--SELECT 'x'
-- FROM dba_tab_columns t,user_synonyms s,user_objects uo
-- WHERE s.table_owner = t.owner
-- AND s.table_name = t.table_name
-- AND s.synonym_name =og.object_name
-- AND uo.object_name = og.object_name
-- AND uo.object_type = 'SYNONYM'
-- AND t.column_name = 'LEDGER_ID'
-- AND t.data_type = 'NUMBER'
SELECT /*+ no_unnest */ 'x'
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = og.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = 'LEDGER_ID'
and t.data_type = 'NUMBER'
)
);
SELECT e.object_name, object_type_code
FROM xla_extract_objects e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND e.object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND t.column_name = 'EVENT_ID'
AND t.data_type = 'NUMBER')
-- 4420371 AND t.nullable = 'N')
AND EXISTS (SELECT 'y'
FROM xla_extract_objects_gt a
WHERE a.object_name = e.object_name); */
SELECT e.object_name, object_type_code
FROM xla_extract_objects e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND e.object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t, user_objects uo
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND uo.object_name = og.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = 'EVENT_ID'
AND t.data_type = 'NUMBER'
UNION ALL
--SELECT 'x'
-- FROM dba_tab_columns t,user_synonyms s,user_objects uo
-- WHERE s.table_owner = t.owner
-- AND s.table_name = t.table_name
-- AND s.synonym_name =og.object_name
-- AND uo.object_name = og.object_name
-- AND uo.object_type = 'SYNONYM'
-- AND t.column_name = 'EVENT_ID'
-- AND t.data_type = 'NUMBER'
SELECT /*+ no_unnest */ 'x'
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = og.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = 'EVENT_ID'
and t.data_type = 'NUMBER'
)
)
-- 4420371 AND t.nullable = 'N')
AND EXISTS (SELECT 'y'
FROM xla_extract_objects_gt a
WHERE a.object_name = e.object_name);
SELECT e.object_name, object_type_code
FROM xla_extract_objects e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND object_type_code IN ('HEADER_MLS','LINE_MLS')
AND e.object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND t.column_name = 'LANGUAGE'
AND t.data_type = 'VARCHAR2')
-- 4420371 AND t.nullable = 'N')
AND EXISTS (SELECT 'y'
FROM xla_extract_objects_gt a
WHERE a.object_name = e.object_name); */
SELECT e.object_name, object_type_code
FROM xla_extract_objects e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND object_type_code IN ('HEADER_MLS','LINE_MLS')
AND e.object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t, user_objects uo
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND uo.object_name = og.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = 'LANGUAGE'
AND t.data_type = 'VARCHAR2'
UNION ALL
--SELECT 'x'
-- FROM dba_tab_columns t,user_synonyms s,user_objects uo
-- WHERE s.table_owner = t.owner
-- AND s.table_name = t.table_name
-- AND s.synonym_name =og.object_name
-- AND uo.object_name = og.object_name
-- AND uo.object_type = 'SYNONYM'
-- AND t.column_name = 'LANGUAGE'
-- AND t.data_type = 'VARCHAR2'
SELECT /*+ no_unnest */ 'x'
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = og.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = 'LANGUAGE'
and t.data_type = 'VARCHAR2'
)
)
-- 4420371 AND t.nullable = 'N')
AND EXISTS (SELECT 'y'
FROM xla_extract_objects_gt a
WHERE a.object_name = e.object_name);
SELECT e.object_name, object_type_code
FROM xla_extract_objects e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND object_type_code IN ('LINE','LINE_MLS')
AND e.object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND t.column_name = 'LINE_NUMBER'
AND t.data_type = 'NUMBER')
-- 4420371 AND t.nullable = 'N')
AND EXISTS (SELECT 'y'
FROM xla_extract_objects_gt a
WHERE a.object_name = e.object_name); */
SELECT e.object_name, object_type_code
FROM xla_extract_objects e, xla_extract_objects_gt og
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND object_type_code IN ('LINE','LINE_MLS')
AND e.object_name = og.object_name
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t, user_objects uo
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND uo.object_name = og.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = 'LINE_NUMBER'
AND t.data_type = 'NUMBER'
UNION ALL
--SELECT 'x'
-- FROM dba_tab_columns t,user_synonyms s,user_objects uo
-- WHERE s.table_owner = t.owner
-- AND s.table_name = t.table_name
-- AND s.synonym_name =og.object_name
-- AND uo.object_name = og.object_name
-- AND uo.object_type = 'SYNONYM'
-- AND t.column_name = 'LINE_NUMBER'
-- AND t.data_type = 'NUMBER'
SELECT /*+ no_unnest */ 'x'
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = og.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = 'LINE_NUMBER'
and t.data_type = 'NUMBER'
)
)
-- 4420371 AND t.nullable = 'N')
AND EXISTS (SELECT 'y'
FROM xla_extract_objects_gt a
WHERE a.object_name = e.object_name);
SELECT e.object_name, object_type_code
FROM xla_extract_objects e, xla_extract_objects_gt og, xla_subledgers app
WHERE e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_type_code IN ('LINE','LINE_MLS')
AND e.object_name = og.object_name
AND e.application_id = app.application_id
AND app.alc_enabled_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND t.column_name = 'LEDGER_ID'
AND t.data_type = 'NUMBER')
AND EXISTS (SELECT 'y'
FROM xla_extract_objects_gt a
WHERE a.object_name = e.object_name); */
SELECT e.object_name, object_type_code
FROM xla_extract_objects e, xla_extract_objects_gt og, xla_subledgers app
WHERE e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_type_code IN ('LINE','LINE_MLS')
AND e.object_name = og.object_name
AND e.application_id = app.application_id
AND app.alc_enabled_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM dba_tab_columns t, user_objects uo
WHERE t.table_name = og.object_name
AND og.owner = t.owner
AND uo.object_name = og.object_name
AND uo.object_type <> 'SYNONYM'
AND t.column_name = 'LEDGER_ID'
AND t.data_type = 'NUMBER'
UNION ALL
--SELECT 'x'
-- FROM dba_tab_columns t,user_synonyms s,user_objects uo
-- WHERE s.table_owner = t.owner
-- AND s.table_name = t.table_name
-- AND s.synonym_name =og.object_name
-- AND uo.object_name = og.object_name
-- AND uo.object_type = 'SYNONYM'
-- AND t.column_name = 'LEDGER_ID'
-- AND t.data_type = 'NUMBER'
SELECT /*+ no_unnest */ 'x'
FROM USER_SYNONYMS S,USER_OBJECTS UO
WHERE 1=1
AND UO.OBJECT_NAME = og.object_name
AND UO.OBJECT_TYPE = 'SYNONYM'
AND S.SYNONYM_NAME = UO.OBJECT_NAME
AND exists (select /*+ no_unnest */ 'x'
from DBA_TAB_COLUMNS T
where S.TABLE_OWNER = T.OWNER
AND S.TABLE_NAME = T.TABLE_NAME
AND T.COLUMN_NAME = 'LEDGER_ID'
and t.data_type = 'NUMBER'
))
AND EXISTS (SELECT 'y'
FROM xla_extract_objects_gt a
WHERE a.object_name = e.object_name);
SELECT accounting_attribute_code
FROM xla_acct_attributes_b a
WHERE a.assignment_required_code = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_evt_class_acct_attrs e
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.accounting_attribute_code = a.accounting_attribute_code
AND e.default_flag = 'Y');
SELECT distinct assignment_group_code
FROM xla_acct_attributes_b a
WHERE assignment_group_code IS NOT NULL
AND EXISTS (SELECT 'x'
FROM xla_evt_class_acct_attrs e
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.accounting_attribute_code = a.accounting_attribute_code
AND e.default_flag = 'Y');
SELECT accounting_attribute_code
FROM xla_acct_attributes_b a
WHERE a.assignment_required_code = 'G'
AND a.assignment_group_code = l_mapping_groups.assignment_group_code
AND NOT EXISTS (SELECT 'x'
FROM xla_evt_class_acct_attrs e
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.accounting_attribute_code = a.accounting_attribute_code
AND e.default_flag = 'Y');
SELECT allow_budgets_flag, allow_encumbrance_flag
FROM xla_event_class_attrs e
WHERE e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code;
SELECT 'x'
FROM xla_evt_class_acct_attrs e
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.accounting_attribute_code = 'BUDGET_VERSION_ID'
AND e.default_flag = 'Y';
SELECT 'x'
FROM xla_evt_class_acct_attrs e
WHERE e.application_id = p_application_id
AND e.event_class_code = p_event_class_code
AND e.accounting_attribute_code = 'ENCUMBRANCE_TYPE_ID'
AND e.default_flag = 'Y';
SELECT a.accounting_attribute_code, a.assignment_group_code,
a.source_type_code, a.source_code
FROM xla_evt_class_acct_attrs_fvl a
WHERE a.application_id = p_application_id
AND a.event_class_code = p_event_class_code
AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID2'
AND default_flag = 'Y';
SELECT 'x'
FROM xla_evt_class_acct_attrs a
WHERE a.application_id = p_application_id
AND a.event_class_code = p_event_class_code
AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_2'
AND default_flag = 'Y';
SELECT a.accounting_attribute_code, a.assignment_group_code
FROM xla_evt_class_acct_attrs_fvl a
WHERE a.application_id = p_application_id
AND a.event_class_code = p_event_class_code
AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID3'
AND default_flag = 'Y';
SELECT 'x'
FROM xla_evt_class_acct_attrs a
WHERE a.application_id = p_application_id
AND a.event_class_code = p_event_class_code
AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_3'
AND a.default_flag = 'Y';
SELECT a.accounting_attribute_code, a.assignment_group_code
FROM xla_evt_class_acct_attrs_fvl a
WHERE a.application_id = p_application_id
AND a.event_class_code = p_event_class_code
AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID4'
AND default_flag = 'Y';
SELECT 'x'
FROM xla_evt_class_acct_attrs a
WHERE a.application_id = p_application_id
AND a.event_class_code = p_event_class_code
AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_4'
AND default_flag = 'Y';
SELECT a.accounting_attribute_code, a.assignment_group_code
FROM xla_evt_class_acct_attrs_fvl a
WHERE a.application_id = p_application_id
AND a.event_class_code = p_event_class_code
AND a.accounting_attribute_code = 'REVERSED_DISTRIBUTION_ID5'
AND a.default_flag = 'Y';
SELECT 'x'
FROM xla_evt_class_acct_attrs a
WHERE a.application_id = p_application_id
AND a.event_class_code = p_event_class_code
AND a.accounting_attribute_code = 'DISTRIBUTION_IDENTIFIER_5'
AND a.default_flag = 'Y';
SELECT s.accounting_attribute_code,
s.source_type_code, s.source_code
FROM xla_evt_class_acct_attrs s
WHERE s.application_id = p_application_id
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_type_code = 'S'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources e
WHERE e.application_id = s.application_id
AND e.event_class_code = s.event_class_code
AND e.source_application_id = s.source_application_id
AND e.source_type_code = s.source_type_code
AND e.source_code = s.source_code
AND e.active_flag = 'Y');
SELECT s.accounting_attribute_code, s.source_application_id,
s.source_type_code, s.source_code
FROM xla_evt_class_acct_attrs s
WHERE s.application_id = p_application_id
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_type_code = 'D';
SELECT language_code
FROM fnd_languages
WHERE installed_flag in ('I','B');
SELECT distinct c.column_name
FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND e.object_name = og.object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_type_code IN ('HEADER_MLS','LINE_MLS')
AND c.data_type IN ('NUMBER','DATE')
AND c.column_name NOT IN ('EVENT_ID','LINE_NUMBER','LEDGER_ID');
SELECT distinct(c.column_name) source_code
,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
,CASE e.object_type_code
WHEN 'HEADER' THEN 'N'
WHEN 'LINE' THEN 'N'
ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
END translated_flag
FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND e.object_name = og.object_name
AND og.owner = c.owner
--
-- Bug 5120836
-- Do not create the LANGUAGE column from non-MLS objects
--
AND DECODE(e.object_type_code
,'HEADER_MLS'
,'MLS_COLUMNS'
,'LINE_MLS'
,'MLS_COLUMNS'
,c.column_name) <> 'LANGUAGE'
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND NOT EXISTS (SELECT 'x'
FROM xla_sources_b s
WHERE s.application_id = e.application_id
AND s.source_type_code = 'S'
AND s.source_code = c.column_name);
SELECT DISTINCT r.reference_object_appl_id
, c.column_name source_code
,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
,CASE e.object_type_code
WHEN 'HEADER' THEN 'N'
WHEN 'LINE' THEN 'N'
ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
END translated_flag
FROM dba_tab_columns c, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND r.reference_object_name = og.reference_object_name
AND og.owner = c.owner
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
--
-- Bug 5120836
-- Do not create the LANGUAGE column from non-MLS objects
--
AND DECODE(e.object_type_code
,'HEADER_MLS'
,'MLS_COLUMNS'
,'LINE_MLS'
,'MLS_COLUMNS'
,c.column_name) <> 'LANGUAGE'
AND NOT EXISTS (SELECT 'x'
FROM xla_sources_b s
WHERE s.application_id = r.reference_object_appl_id
AND s.source_type_code = 'S'
AND s.source_code = c.column_name); */
SELECT distinct c.column_name
FROM dba_tab_columns c,user_objects uo,xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND uo.object_name = e.object_name
AND uo.object_type <> 'SYNONYM'
AND e.object_name = og.object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_type_code IN ('HEADER_MLS','LINE_MLS')
AND c.data_type IN ('NUMBER','DATE')
AND c.column_name NOT IN ('EVENT_ID','LINE_NUMBER','LEDGER_ID')
UNION ALL
SELECT distinct c.column_name
FROM dba_tab_columns c,user_objects uo,user_synonyms s,xla_extract_objects e, xla_extract_objects_gt og
WHERE s.table_owner = c.owner
AND s.table_name = c.table_name
AND s.synonym_name = e.object_name
AND uo.object_name = e.object_name
AND uo.object_type = 'SYNONYM'
AND e.object_name = og.object_name
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_type_code IN ('HEADER_MLS','LINE_MLS')
AND c.data_type IN ('NUMBER','DATE')
AND c.column_name NOT IN ('EVENT_ID','LINE_NUMBER','LEDGER_ID');
SELECT distinct(c.column_name) source_code
,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
,CASE e.object_type_code
WHEN 'HEADER' THEN 'N'
WHEN 'LINE' THEN 'N'
ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
END translated_flag
FROM dba_tab_columns c,user_objects uo, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND uo.object_name = e.object_name
AND uo.object_type <> 'SYNONYM'
AND e.object_name = og.object_name
AND og.owner = c.owner
--
-- Bug 5120836
-- Do not create the LANGUAGE column from non-MLS objects
--
AND DECODE(e.object_type_code
,'HEADER_MLS'
,'MLS_COLUMNS'
,'LINE_MLS'
,'MLS_COLUMNS'
,c.column_name) <> 'LANGUAGE'
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND NOT EXISTS (SELECT 'x'
FROM xla_sources_b s
WHERE s.application_id = e.application_id
AND s.source_type_code = 'S'
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct(c.column_name) source_code
,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
,CASE e.object_type_code
WHEN 'HEADER' THEN 'N'
WHEN 'LINE' THEN 'N'
ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
END translated_flag
FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_extract_objects e, xla_extract_objects_gt og
WHERE s.table_owner = c.owner
AND c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name =e.object_name
AND uo.object_type = 'SYNONYM'
AND e.object_name = og.object_name
--
-- Bug 5120836
-- Do not create the LANGUAGE column from non-MLS objects
--
AND DECODE(e.object_type_code
,'HEADER_MLS'
,'MLS_COLUMNS'
,'LINE_MLS'
,'MLS_COLUMNS'
,c.column_name) <> 'LANGUAGE'
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND NOT EXISTS (SELECT 'x'
FROM xla_sources_b s
WHERE s.application_id = e.application_id
AND s.source_type_code = 'S'
AND s.source_code = c.column_name);
SELECT DISTINCT r.reference_object_appl_id
, c.column_name source_code
,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
,CASE e.object_type_code
WHEN 'HEADER' THEN 'N'
WHEN 'LINE' THEN 'N'
ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
END translated_flag
FROM dba_tab_columns c,user_objects uo, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND r.reference_object_name = og.reference_object_name
AND og.owner = c.owner
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
--
-- Bug 5120836
-- Do not create the LANGUAGE column from non-MLS objects
--
AND DECODE(e.object_type_code
,'HEADER_MLS'
,'MLS_COLUMNS'
,'LINE_MLS'
,'MLS_COLUMNS'
,c.column_name) <> 'LANGUAGE'
AND NOT EXISTS (SELECT 'x'
FROM xla_sources_b s
WHERE s.application_id = r.reference_object_appl_id
AND s.source_type_code = 'S'
AND s.source_code = c.column_name)
UNION ALL
SELECT DISTINCT r.reference_object_appl_id
, c.column_name source_code
,decode(c.data_type,'VARCHAR2','C','CHAR','C','NUMBER','N','DATE','D','C') data_type_code
,decode(c.column_name,'EVENT_ID','N','LINE_NUMBER','N','LEDGER_ID','N','LANGUAGE','N','Y') visible_flag
,CASE e.object_type_code
WHEN 'HEADER' THEN 'N'
WHEN 'LINE' THEN 'N'
ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
END translated_flag
FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE s.table_owner = c.owner
AND c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name = r.reference_object_name
AND uo.object_type = 'SYNONYM'
AND r.reference_object_name = og.reference_object_name
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
--
-- Bug 5120836
-- Do not create the LANGUAGE column from non-MLS objects
--
AND DECODE(e.object_type_code
,'HEADER_MLS'
,'MLS_COLUMNS'
,'LINE_MLS'
,'MLS_COLUMNS'
,c.column_name) <> 'LANGUAGE'
AND NOT EXISTS (SELECT 'x'
FROM xla_sources_b s
WHERE s.application_id = r.reference_object_appl_id
AND s.source_type_code = 'S'
AND s.source_code = c.column_name);
SELECT distinct source_code
FROM xla_sources_b e
WHERE e.application_id = p_application_id
AND NOT EXISTS (SELECT 'x'
FROM xla_sources_vl s
WHERE s.application_id = e.application_id
AND s.source_type_code = e.source_type_code
AND s.source_code = e.source_code);
SELECT distinct reference_object_appl_id, source_code
FROM xla_sources_b e, xla_reference_objects r
WHERE e.application_id = r.reference_object_appl_id
AND r.application_id = p_application_id
AND NOT EXISTS (SELECT 'x'
FROM xla_sources_vl s
WHERE s.application_id = r.reference_object_appl_id
AND s.source_type_code = e.source_type_code
AND s.source_code = e.source_code);
IF (g_last_update_date is NULL) THEN
g_last_update_date := sysdate;
IF (g_last_update_login is NULL) THEN
g_last_update_login := xla_environment_pkg.g_login_id;
IF (g_last_updated_by is NULL) THEN
g_last_updated_by := xla_environment_pkg.g_usr_id;
INSERT INTO xla_sources_b
(source_code
,application_id
,source_type_code
,datatype_code
,sum_flag
,visible_flag
,enabled_flag
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login
,translated_flag
,key_flexfield_flag)
VALUES
(l_array_source_code(i)
,p_application_id
,'S'
,l_array_datatype_code(i)
,'N'
,l_array_visible_flag(i)
,'Y'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
,l_array_translated_flag(i)
,'N');
INSERT INTO xla_sources_b
(source_code
,application_id
,source_type_code
,datatype_code
,sum_flag
,visible_flag
,enabled_flag
,key_flexfield_flag
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login
,translated_flag)
VALUES
(l_array_ref_source_code(i)
,l_array_ref_source_appl_id(i)
,'S'
,l_array_ref_datatype_code(i)
,'N'
,l_array_ref_visible_flag(i)
,'Y'
,'N'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
,l_array_ref_translated_flag(i));
INSERT INTO xla_sources_tl
(source_code
,application_id
,source_type_code
,name
,language
,source_lang
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
VALUES
(l_array_tl_source_code(i)
,p_application_id
,'S'
,l_array_tl_source_code(i)
,l_language_code
,USERENV('LANG')
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login);
INSERT INTO xla_sources_tl
(source_code
,application_id
,source_type_code
,name
,language
,source_lang
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
VALUES
(l_array_ref_tl_source_code(i)
,l_array_ref_tl_source_appl_id(i)
,'S'
,l_array_ref_tl_source_code(i)
,l_language_code
,USERENV('LANG')
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login);
IF (g_last_update_date is NULL) THEN
g_last_update_date := sysdate;
IF (g_last_update_login is NULL) THEN
g_last_update_login := xla_environment_pkg.g_login_id;
IF (g_last_updated_by is NULL) THEN
g_last_updated_by := xla_environment_pkg.g_usr_id;
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND og.object_name = e.object_name
AND og.owner = c.owner
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND og.reference_object_name = r.reference_object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND og.object_name = e.object_name
AND og.owner = c.owner
AND e.object_type_code IN ('LINE','LINE_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND og.reference_object_name = r.reference_object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('LINE','LINE_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND og.object_name = e.object_name
AND og.owner = c.owner
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND og.reference_object_name = r.reference_object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND og.object_name = e.object_name
AND og.owner = c.owner
AND e.object_type_code IN ('LINE','LINE_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND og.reference_object_name = r.reference_object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('LINE','LINE_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name)); */
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c,user_objects uo, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND uo.object_name = e.object_name
AND uo.object_type <> 'SYNONYM'
AND og.object_name = e.object_name
AND og.owner = c.owner
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_extract_objects e, xla_extract_objects_gt og
WHERE s.table_owner = c.owner
AND c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name = e.object_name
AND uo.object_type = 'SYNONYM'
AND og.object_name = e.object_name
--AND og.owner = c.owner
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_reference_objects r,user_objects uo,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND og.reference_object_name = r.reference_object_name
AND og.owner = c.owner
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_reference_objects r,user_objects uo, user_synonyms s,xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = s.table_name
AND og.reference_object_name = r.reference_object_name
AND s.table_owner = c.owner
AND uo.object_name = s.synonym_name
AND uo.object_name = r.reference_object_name
AND uo.object_type = 'SYNONYM'
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c,user_objects uo, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND uo.object_name = e.object_name
AND uo.object_type <> 'SYNONYM'
AND og.object_name = e.object_name
AND og.owner = c.owner
AND e.object_type_code IN ('LINE','LINE_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, user_objects uo,user_synonyms s,xla_extract_objects e, xla_extract_objects_gt og
WHERE s.table_owner = c.owner
AND c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name = e.object_name
AND uo.object_type = 'SYNONYM'
AND og.object_name = e.object_name
--AND og.owner = c.owner
AND e.object_type_code IN ('LINE','LINE_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_reference_objects r,user_objects uo,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND og.reference_object_name = r.reference_object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('LINE','LINE_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, xla_reference_objects r,user_objects uo,user_synonyms s,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name = r.reference_object_name
AND uo.object_type = 'SYNONYM'
AND og.reference_object_name = r.reference_object_name
AND s.table_owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('LINE','LINE_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, user_objects uo,xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND uo.object_name = e.object_name
AND uo.object_type <> 'SYNONYM'
AND og.object_name = e.object_name
AND og.owner = c.owner
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, user_objects uo,user_synonyms s,xla_extract_objects e, xla_extract_objects_gt og
WHERE s.table_owner = c.owner
AND c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name = e.object_name
AND uo.object_type = 'SYNONYM'
AND og.object_name = e.object_name
--AND og.owner = c.owner
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c,user_objects uo, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND og.reference_object_name = r.reference_object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'H'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE s.table_owner = c.owner
AND c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name = r.reference_object_name
AND uo.object_type = 'SYNONYM'
AND og.reference_object_name = r.reference_object_name
-- AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('HEADER','HEADER_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c,user_objects uo, xla_extract_objects e, xla_extract_objects_gt og
WHERE c.table_name = e.object_name
AND uo.object_name = e.object_name
AND uo.object_type <> 'SYNONYM'
AND og.object_name = e.object_name
AND og.owner = c.owner
AND e.object_type_code IN ('LINE','LINE_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,p_application_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c, user_objects uo,user_synonyms s,xla_extract_objects e, xla_extract_objects_gt og
WHERE s.table_owner = c.owner
AND c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name = e.object_name
AND uo.object_type = 'SYNONYM'
AND og.object_name = e.object_name
-- AND og.owner = c.owner
AND e.object_type_code IN ('LINE','LINE_MLS')
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = p_application_id
AND s.source_code = c.column_name));
INSERT INTO xla_event_sources
(source_code
,application_id
,entity_code
,event_class_code
,source_application_id
,source_type_code
,active_flag
,level_code
,creation_date
,created_by
,last_updated_by
,last_update_date
,last_update_login)
(SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c,user_objects uo, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE c.table_name = r.reference_object_name
AND uo.object_name = r.reference_object_name
AND uo.object_type <> 'SYNONYM'
AND og.reference_object_name = r.reference_object_name
AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('LINE','LINE_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name)
UNION ALL
SELECT distinct (c.column_name)
,p_application_id
,p_entity_code
,p_event_class_code
,r.reference_object_appl_id
,'S'
,'Y'
,'L'
,g_creation_date
,g_created_by
,g_last_updated_by
,g_last_update_date
,g_last_update_login
FROM dba_tab_columns c,user_objects uo,user_synonyms s, xla_reference_objects r,
xla_reference_objects_gt og, xla_extract_objects e
WHERE s.table_owner = c.owner
AND c.table_name = s.table_name
AND uo.object_name = s.synonym_name
AND uo.object_name = r.reference_object_name
AND uo.object_type = 'SYNONYM'
AND og.reference_object_name = r.reference_object_name
-- AND og.owner = c.owner
AND e.application_id = p_application_id
AND e.entity_code = p_entity_code
AND e.event_class_code = p_event_class_code
AND e.object_name = r.object_name
AND e.object_type_code IN ('LINE','LINE_MLS')
AND r.application_id = p_application_id
AND r.entity_code = p_entity_code
AND r.event_class_code = p_event_class_code
AND r.always_populated_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM xla_event_sources s
WHERE s.application_id = p_application_id
AND s.entity_code = p_entity_code
AND s.event_class_code = p_event_class_code
AND s.source_application_id = r.reference_object_appl_id
AND s.source_code = c.column_name));