WITH tgt
AS ( SELECT TRIM (
REGEXP_SUBSTR (
REPLACE (MS.tgt_column_name, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_COLUMN_NAME,
TRIM (
REGEXP_SUBSTR (REPLACE (MS.tgt_table_name, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_TABLE_NAME,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.tgt_system_environment_name, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_SYSTEM_ENVIRONMENT_NAME,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.tgt_system_name, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_SYSTEM_NAME,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.tgt_col_datatype, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_COL_DATATYPE,
TRIM (
REGEXP_SUBSTR (REPLACE (MS.tgt_col_length, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_COL_LENGTH,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.tgt_col_precision, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_COL_PRECISION,
TRIM (
REGEXP_SUBSTR (REPLACE (MS.tgt_col_scale, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_COL_SCALE,
TRIM (REGEXP_SUBSTR (REPLACE (MS.tgt_xpath, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_XPATH,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.tgt_primary_key_flag, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_PRIMARY_KEY_FLAG,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.tgt_col_nullable_flag, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_COL_NULLABLE_FLAG,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.tgt_natural_key_flag, CHR (10), ','),
'[^,]+',
1,
LEVEL))
TGT_NATURAL_KEY_FLAG,
map_seq_id,
md.map_id,
proj_id
FROM mapping_specification MS, mapping_details md
WHERE ms.map_id = md.map_id
AND (MD.status = 'Active' OR MD.ispublished = 'Y')
AND MS.proj_id = 39554
-- WHERE ( MS.map_id IN (SELECT map_id
-- FROM mapping_details MD
-- WHERE MD.status = 'Active'
-- OR MD.ispublished = 'Y')
-- AND MS.proj_id IN ( 39554 ) )
AND COALESCE (TRIM (MS.tgt_column_name), NULL, ' ') <> ' '
AND COALESCE (TRIM (MS.tgt_table_name), NULL, ' ') <> ' '
AND COALESCE (TRIM (MS.tgt_system_environment_name),
NULL,
' ') <> ' '
AND COALESCE (TRIM (MS.tgt_system_name), NULL, ' ') <> ' '
CONNECT BY NOCYCLE LEVEL <=
LENGTH (
REPLACE (MS.tgt_column_name,
CHR (10),
','))
- LENGTH (
REPLACE (
REPLACE (MS.tgt_column_name,
CHR (10),
','),
','))
+ 1
AND PRIOR map_seq_id = map_seq_id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL),
src
AS ( SELECT TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_column_name, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_COLUMN_NAME,
TRIM (
REGEXP_SUBSTR (REPLACE (MS.src_table_name, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_TABLE_NAME,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_system_environment_name, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_SYSTEM_ENVIRONMENT_NAME,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_system_name, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_SYSTEM_NAME,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_col_datatype, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_COL_DATATYPE,
TRIM (
REGEXP_SUBSTR (REPLACE (MS.src_col_length, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_COL_LENGTH,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_col_precision, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_COL_PRECISION,
TRIM (
REGEXP_SUBSTR (REPLACE (MS.src_col_scale, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_COL_SCALE,
TRIM (REGEXP_SUBSTR (REPLACE (MS.src_xpath, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_XPATH,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_primary_key_flag, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_PRIMARY_KEY_FLAG,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_col_identity_flag, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_COL_IDENTITY_FLAG,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_col_nullable_flag, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_COL_NULLABLE_FLAG,
TRIM (
REGEXP_SUBSTR (
REPLACE (MS.src_natural_key_flag, CHR (10), ','),
'[^,]+',
1,
LEVEL))
SRC_NATURAL_KEY_FLAG,
map_seq_id,
md.map_id,
proj_id
FROM mapping_specification MS, mapping_details md
WHERE ms.map_id = md.map_id
AND (MD.status = 'Active' OR MD.ispublished = 'Y')
AND MS.proj_id = 39554
-- WHERE ( MS.map_id IN (SELECT map_id
-- FROM mapping_details MD
-- WHERE MD.status = 'Active'
-- OR MD.ispublished = 'Y')
-- AND MS.proj_id IN ( 39554 ) )
AND COALESCE (TRIM (MS.src_column_name), NULL, ' ') <> ' '
AND COALESCE (TRIM (MS.src_table_name), NULL, ' ') <> ' '
AND COALESCE (TRIM (MS.src_system_environment_name),
NULL,
' ') <> ' '
AND COALESCE (TRIM (MS.src_system_name), NULL, ' ') <> ' '
CONNECT BY NOCYCLE LEVEL <=
LENGTH (
REPLACE (MS.src_column_name,
CHR (10),
','))
- LENGTH (
REPLACE (
REPLACE (MS.src_column_name,
CHR (10),
','),
','))
+ 1
AND PRIOR map_seq_id = map_seq_id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL),
srcandtgt
AS (SELECT src.src_system_name,
src.src_system_environment_name,
src.src_table_name,
src.src_column_name,
src.src_col_datatype,
src.src_col_length,
src.src_col_precision,
src.src_col_scale,
src.src_xpath,
src.src_primary_key_flag,
src.src_col_identity_flag,
src.src_col_nullable_flag,
src.src_natural_key_flag,
tgt.*
FROM src JOIN tgt ON (src.map_seq_id = tgt.map_seq_id)),
fwd_cte
AS ( SELECT PRIOR T1.src_column_name AS PARENT_SRC,T1.*
FROM srcandtgt T1
LEFT OUTER JOIN srcandtgt T2
ON ( UPPER (T2.src_column_name) =
UPPER (T1.tgt_column_name)
AND UPPER (T2.src_table_name) =
UPPER (T1.tgt_table_name)
AND UPPER (T2.src_system_name) =
UPPER (T1.tgt_system_name)
AND UPPER (T2.src_system_environment_name) =
UPPER (T1.tgt_system_environment_name))
WHERE ( UPPER (T1.tgt_column_name) <> UPPER (T1.src_column_name)
OR UPPER (t1.tgt_system_environment_name) <>
UPPER (T1.src_system_environment_name)
OR UPPER (T1.tgt_system_name) <> UPPER (T1.src_system_name)
OR UPPER (T1.tgt_table_name) <> UPPER (T1.src_table_name))
START WITH ( TRIM (UPPER (T1.src_system_name)) =
TRIM (UPPER ('ABS'))
AND TRIM (UPPER (T1.src_system_environment_name)) =
TRIM (UPPER ('POPULATION PROJECTION'))
AND TRIM (UPPER (T1.src_table_name)) =
TRIM (UPPER ('ABS POPULATION PROJECTIONS'))
AND TRIM (UPPER (T1.src_column_name)) =
TRIM (UPPER ('COLUMNA')))
CONNECT BY NOCYCLE ( UPPER (t1.src_column_name) =
PRIOR UPPER (T1.tgt_column_name)
AND UPPER (T1.src_table_name) =
PRIOR UPPER (T1.tgt_table_name)
AND UPPER (T1.src_system_name) =
PRIOR UPPER (T1.tgt_system_name)
AND UPPER (T1.src_system_environment_name) =
PRIOR UPPER (
T1.tgt_system_environment_name))),
rev_cte
AS ( SELECT PRIOR T1.tgt_column_name AS PARENT_TGT, T1.*
FROM srcandtgt T1
LEFT OUTER JOIN srcandtgt T2
ON UPPER (T2.tgt_column_name) =
UPPER (T1.src_column_name)
AND UPPER (T2.tgt_table_name) =
UPPER (T1.src_table_name)
AND UPPER (T2.tgt_system_name) =
UPPER (T1.src_system_name)
AND UPPER (T2.tgt_system_environment_name) =
UPPER (T1.src_system_environment_name)
WHERE UPPER (T1.src_column_name) <> UPPER (T1.tgt_column_name)
OR UPPER (T1.src_system_environment_name) <>
UPPER (T1.tgt_system_environment_name)
OR UPPER (T1.src_system_name) <> UPPER (T1.tgt_system_name)
OR UPPER (T1.src_table_name) <> UPPER (T1.tgt_table_name)
START WITH TRIM (UPPER (T1.tgt_system_name)) =
TRIM (UPPER ('ABS'))
AND TRIM (UPPER (T1.tgt_system_environment_name)) =
TRIM (UPPER ('POPULATION PROJECTION'))
AND TRIM (UPPER (T1.tgt_table_name)) =
TRIM (UPPER ('ABS POPULATION PROJECTIONS'))
AND TRIM (UPPER (T1.tgt_column_name)) =
TRIM (UPPER ('COLUMNA'))
CONNECT BY NOCYCLE UPPER (T1.tgt_column_name) =
PRIOR UPPER (T1.src_column_name)
AND UPPER (T1.tgt_table_name) =
PRIOR UPPER (T1.src_table_name)
AND UPPER (T1.tgt_system_name) =
PRIOR UPPER (T1.src_system_name)
AND UPPER (T1.tgt_system_environment_name) =
PRIOR UPPER (
T1.src_system_environment_name)),
fwd_cte2
AS (SELECT CTE.*,
MS.src_system_id,
MS.src_system_name SRC_SYS_NAME,
MS.src_column_name SRC_COL_NAME,
MS.src_system_environment_name SRC_SYS_ENV_NAME,
MS.src_table_name SRC_TAB_NAME,
ms.src_col_def,
ms.src_column_comments,
ms.src_logical_column_name,
ms.src_column_class,
ms.src_column_alias,
ms.src_business_key_flag,
MS.tgt_system_id,
MS.tgt_column_name TGT_COL_NAME,
MS.tgt_table_name TGT_TAB_NAME,
MS.tgt_system_environment_name TGT_SYS_ENV_NAME,
MS.tgt_system_name TGT_SYS_NAME,
ms.tgt_col_def,
ms.tgt_column_comments,
ms.tgt_logical_column_name,
ms.tgt_column_class,
ms.tgt_column_alias,
ms.tgt_business_key_flag,
ms.business_rule,
ms.extended_bus_rule,
MS.lookup_on,
ms.trnsf_lookup_condition,
md.map_name,
md.map_spec_version,
PROJ.proj_name,
MD.job_xref,
MD.user_defined_field1 MAP_USER_DEFINED1
FROM fwd_cte CTE
JOIN mapping_specification MS
ON ( MS.map_seq_id = cte.map_seq_id
AND MS.map_id = cte.map_id
AND ms.proj_id = cte.proj_id)
JOIN mapping_details MD
ON CTE.map_id = md.map_id
AND (md.status = 'Active' OR md.ispublished = 'Y')
JOIN project PROJ ON CTE.proj_id = PROJ.proj_id),
rev_cte2
AS (SELECT CTE.*,
MS.src_system_id,
MS.src_system_name SRC_SYS_NAME,
MS.src_column_name SRC_COL_NAME,
MS.src_system_environment_name SRC_SYS_ENV_NAME,
MS.src_table_name SRC_TAB_NAME,
MS.src_col_def,
MS.src_column_comments,
MS.src_logical_column_name,
ms.src_column_class,
ms.src_column_alias,
ms.src_business_key_flag,
MS.tgt_system_id,
MS.tgt_column_name TGT_COL_NAME,
MS.tgt_table_name TGT_TAB_NAME,
MS.tgt_system_environment_name TGT_SYS_ENV_NAME,
MS.tgt_system_name TGT_SYS_NAME,
MS.tgt_col_def,
MS.tgt_column_comments,
MS.tgt_logical_column_name,
ms.tgt_column_class,
ms.tgt_column_alias,
ms.tgt_business_key_flag,
MS.business_rule,
MS.extended_bus_rule,
MS.lookup_on,
MS.trnsf_lookup_condition,
MD.map_name,
MD.map_spec_version,
PROJ.proj_name,
MD.job_xref,
MD.user_defined_field1 MAP_USER_DEFINED1
FROM rev_cte CTE
JOIN mapping_specification MS
ON ( MS.map_seq_id = CTE.map_seq_id
AND MS.map_id = CTE.map_id
AND MS.proj_id = CTE.proj_id)
JOIN mapping_details MD
ON CTE.map_id = MD.map_id
AND (MD.status = 'Active' OR MD.ispublished = 'Y')
JOIN project PROJ ON CTE.proj_id = PROJ.proj_id)
SELECT DISTINCT t.*
FROM (SELECT DISTINCT *
FROM fwd_cte2
UNION ALL
SELECT DISTINCT *
FROM rev_cte2) t