Powercenter Objects Available reports
This page will show you all the Available reports for Powercenter Objects, once you select a report name, it will open the report query page. copy and paste it to your PC and run it from, toad or any other DB tools you have.
Available Reports
Mappings
Displays source, targets and all transformations used in a mapping by repository and folder.
select “REPOSITORY_NAME”,
“INSTANCE_NAME”,
“WIDGET_TYPE_NAME”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
“DESCRIPTION”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA as SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME as MAPPING_NAME, MAPPING_WIDGET_INST.WIDGET_TYPE_NAME as WIDGET_TYPE_NAME, MAPPING_WIDGET_INST.INSTANCE_NAME as INSTANCE_NAME, MAPPING_WIDGET_INST.DESCRIPTION as DESCRIPTION FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “WIDGET_TYPE_NAME”, “INSTANCE_NAME”
Displays sessions that are created for invalid mappings by repository and folder.
select “SESSION_INSTANCE_NAME”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
“MAPPING_NAME”,
“SESSION_INSTANCE_ID”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_SESSION_INSTANCES.SESSION_INSTANCE_NAME, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as PARENT_MAPPING_IS_VALID, (COUNT(MAPPING_SESSION_INSTANCES.SESSION_INSTANCE_ID)) as SESSION_INSTANCE_ID FROM REP_SESSION_INSTANCES MAPPING_SESSION_INSTANCES, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_ALL_MAPPINGS ALL_MAPPINGS LEFT OUTER JOIN REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF ON ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_SESS_PARTITION_DEF. MAPPING_ID WHERE (MAPPING_SESSION_INSTANCES.SESSION_ID = MAPPING_SESS_PARTITION_DEF. SESSION_ID AND MAPPING_SESSION_INSTANCES.SESSION_VERSION_NUMBER = MAPPING_SESS_PARTITION_DEF.VERSION_NUMBER AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) AND ALL_MAPPINGS.PARENT_MAPPING_IS_VALID = 0 GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, PARENT_MAPPING_IS_VALID, MAPPING_SESSION_INSTANCES.SESSION_INSTANCE_NAME HAVING (COUNT(MAPPING_SESSION_INSTANCES.SESSION_INSTANCE_ID)) > 0) “query”
where case when (“PARENT_MAPPING_IS_VALID” = 1) then (‘Yes’) else (‘No’) end = ‘No’ and “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_INSTANCE_ID” > 0
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “SESSION_INSTANCE_NAME”, “SESSION_INSTANCE_ID”
Displays targets in an invalid mapping by repository and folder.
select case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”,
“TARGET_VERSION_NUMBER”,
“REPOSITORY_NAME”, “TARGET_NAME”,
“PARENT_TARGET_DATABASE_TYPE”, “TARGET_DESC”,
“PARENT_TARGET_BUSINESS_NAME”,
“MAPPING_NAME”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_ALL_TARGETS.TARGET_NAME, MAPPING_ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME, MAPPING_ALL_TARGETS.TARGET_DESCRIPTION as TARGET_DESC, MAPPING_ALL_TARGETS.TARGET_VERSION_NUMBER, MAPPING_ALL_TARGETS.TARGET_VERSION_STATUS, MAPPING_ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, MAPPING_ALL_TARGETS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG FROM REP_ALL_TARGETS MAPPING_ALL_TARGETS, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO WHERE (MAPPING_ALL_TARGETS.PARENT_TARGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TARGETS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 2 AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘No’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “TARGET_NAME”, “PARENT_TARGET_BUSINESS_NAME”
Displays invalid mappings by repository and folder.
select “MAPPING_VERSION_NUMBER”,
“MAPPING_DESCRIPTION”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
“MAPPING_LAST_SAVED”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
“REPOSITORY_NAME”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ShortcutFlag”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION AS MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT as SHORTCUTFLAG, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION AS PARENT_MAPPING_DESCRIPTION, ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG, (COUNT(DISTINCT(MAPPING_SESS_PARTITION_DEF.SESSION_ID))) AS SESSION_ID FROM REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_ALL_MAPPINGS ALL_MAPPINGS LEFT OUTER JOIN REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF ON ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_SESS_PARTITION_DEF. MAPPING_ID WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION , ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID, ALL_MAPPINGS.MAPPING_LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘No’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “MAPPING_DESCRIPTION”, “MAPPING_VERSION_NUMBER”
Displays mappings by repository and folder. It also displays properties of the mapping such as the number of sources used in a mapping, the number of transformations, and the number of targets.
select “WIDGET_TYPE5”,
“WIDGET_TYPE4”,
“WIDGET_TYPE6”,
“REPOSITORY_NAME”,
“MAPPING_VERSION_NUMBER”,
“SUBJECT_AREA”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
case when (“IS_SHORTCUT1” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT1”,
“MAPPING_NAME”,
“MAPPING_LAST_SAVED”,
“DESCRIPTION”,
case when (“PARENT_MAPPING_IS_VALID” = 1) then (‘yes’) else (‘No’) end as “Calc_PARENT_MAPPING_IS_VALID”,
case when (“IS_SHORTCUT2″ = 1) then (‘ALL_MAPPINGS.PARENT_SUBJECT_AREA’) else (”) end as “Calc_IS_SHORTCUT2”,
“WIDGET_TYPE1”,
“WIDGET_TYPE2”,
“WIDGET_TYPE3”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME AS MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION AS DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER AS MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS AS MAPPING_VERSION_STATUS, ALL_MAPPINGS.MAPPING_LAST_SAVED AS MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT AS IS_SHORTCUT1, ALL_MAPPINGS.PARENT_SUBJECT_AREA AS PARENT_SUBJECT_AREA, ALL_MAPPINGS.IS_SHORTCUT AS IS_SHORTCUT2, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID AS PARENT_MAPPING_IS_VALID , (COUNT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPING_WIDGET_INST.INSTANCE_ID END)) AS WIDGET_TYPE1, (COUNT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 2 THEN MAPPING_WIDGET_INST.INSTANCE_ID END)) AS WIDGET_TYPE2, (COUNT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE NOT IN (1, 2, 44) THEN MAPPING_WIDGET_INST.INSTANCE_ID END)) AS WIDGET_TYPE3, (COUNT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 44 THEN MAPPING_WIDGET_INST.INSTANCE_ID END)) AS WIDGET_TYPE4, (COUNT(DISTINCT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPING_WIDGET_INST.WIDGET_ID END))) AS WIDGET_TYPE5, (COUNT(DISTINCT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 2 THEN MAPPING_WIDGET_INST.WIDGET_ID END))) AS WIDGET_TYPE6 FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.PARENT_SUBJECT_AREA, ALL_MAPPINGS.MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “DESCRIPTION”, “MAPPING_VERSION_NUMBER”, “Calc_MAPPING_VERSION_STATUS”, “MAPPING_LAST_SAVED”, “Calc_IS_SHORTCUT1”, “Calc_IS_SHORTCUT2”, “Calc_PARENT_MAPPING_IS_VALID”
Displays Lookup transformations used in a mapping by repository and folder.
select case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (‘NULL’) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
“ATTR_VALUE”,
case when (“PARENT_WIDGET_IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_WIDGET_IS_REUSABLE”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“MAPPING_NAME”,
“SUBJECT_AREA”,
“WIDGET_NAME”,
“INSTANCE_NAME”,
“DESCRIPTION”,
“WIDGET_VERSION_NUMBER”,
“WIDGET_TYPE_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_WIDGET_INST.INSTANCE_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_NAME, MAPPING_WIDGET_INST.DESCRIPTION as DESCRIPTION, CASE WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_DATATYPE IN (1, 4) THEN CASE WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_TYPE = 5 THEN CASE WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE = 1 THEN ‘Yes’ ELSE ‘No’ END ELSE MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE END ELSE MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE END ATTR_VALUE, MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_STATUS, MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE, MAPPING_ALL_TRANSFORMS.IS_SHORTCUT, MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_WIDGET_ATTR MAPPING_TRANSFORM_WIDGET_ATTR, REP_ALL_TRANSFORMS MAPPING_ALL_TRANSFORMS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_ID = MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_TYPE AND MAPPING_TRANSFORM_WIDGET_ATTR.SESSION_TASK_ID = 0 AND MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_TYPE NOT IN (1, 2, 44) AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TRANSFORMS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPING_WIDGET_INST.WIDGET_TYPE ) AND (MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME = ‘Lookup Procedure’ AND MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_ID = 2 )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mapplets used in a mapping by repository and folder.
select “SUBJECT_AREA”,
“MAPPING_NAME”,
“MAPPLET_NAME”,
case when (“PARENT_MAPPLET_IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_MAPPLET_IS_VALID”,
case when (“MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPLET_VERSION_STATUS”,
“DESCRIPTION”,
“MAPPLET_VERSION_NUMBER”,
“REPOSITORY_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME AS MAPPING_NAME, MAPPING_ALL_MAPPLETS.MAPPLET_NAME AS MAPPLET_NAME, MAPPING_ALL_MAPPLETS.MAPPLET_DESCRIPTION AS DESCRIPTION, MAPPING_ALL_MAPPLETS.MAPPLET_VERSION_NUMBER AS MAPPLET_VERSION_NUMBER, MAPPING_ALL_MAPPLETS.MAPPLET_VERSION_STATUS AS MAPPLET_VERSION_STATUS, MAPPING_ALL_MAPPLETS.IS_SHORTCUT AS IS_SHORTCUT, MAPPING_ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID AS PARENT_MAPPLET_IS_VALID FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_MAPPLETS MAPPING_ALL_MAPPLETS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_MAPPLETS.REF_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_MAPPLETS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 44)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays metadata extensions associated with a mapping and the properties of the metadata extensions.
select “DOMAIN_DESCRIPTION”,
“MAPPING_NAME”,
“METADATA_EXTN_DESCRIPTION”,
“METADATA_EXTN_NAME”,
“SUBJECT_AREA”,
“METADATA_EXTN_VALUE”,
“DOMAIN_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_METADATA_EXTNS.DOMAIN_NAME, MAPPING_METADATA_EXTNS.DOMAIN_DESCRIPTION as DOMAIN_DESCRIPTION, MAPPING_METADATA_EXTNS.METADATA_EXTN_NAME , MAPPING_METADATA_EXTNS.METADATA_EXTN_VALUE as METADATA_EXTN_VALUE, MAPPING_METADATA_EXTNS.METADATA_EXTN_DESCRIPTION as METADATA_EXTN_DESCRIPTION FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_METADATA_EXTNS MAPPING_METADATA_EXTNS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_METADATA_EXTNS. METADATA_EXTN_OBJECT_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_METADATA_EXTNS. VERSION_NUMBER AND ALL_MAPPINGS.SUBJECT_ID = MAPPING_METADATA_EXTNS.SUBJECT_ID AND MAPPING_METADATA_EXTNS.METADATA_EXTN_OBJECT_TYPE = 21)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all transformations from source to target in a mapping and how they are connected to each other.
select “FROM_DESCRIPTION”,
“MAPPING_NAME”,
“FROM_TYPE”,
“TO_NAME”,
“TO_DESCRIPTION”,
“TO_TYPE”,
“FROM_NAME”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_FROM_WIDGET_INST.INSTANCE_NAME as FROM_NAME, MAPPING_FROM_WIDGET_INST.WIDGET_TYPE_NAME as FROM_TYPE , MAPPING_FROM_WIDGET_INST.DESCRIPTION as FROM_DESCRIPTION , MAPPING_TO_WIDGET_INST.INSTANCE_NAME as TO_NAME , MAPPING_TO_WIDGET_INST.WIDGET_TYPE_NAME as TO_TYPE , MAPPING_TO_WIDGET_INST.DESCRIPTION as TO_DESCRIPTION from REP_WIDGET_DEP MAPPING_WIDGET_DEP, REP_WIDGET_INST MAPPING_FROM_WIDGET_INST, REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_TO_WIDGET_INST WHERE (MAPPING_WIDGET_DEP.FROM_INSTANCE_ID = MAPPING_FROM_WIDGET_INST.INSTANCE_ID AND MAPPING_WIDGET_DEP.MAPPING_ID = MAPPING_FROM_WIDGET_INST.MAPPING_ID AND MAPPING_WIDGET_DEP.VERSION_NUMBER = MAPPING_FROM_WIDGET_INST.VERSION_NUMBER AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_DEP.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_DEP. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND MAPPING_WIDGET_DEP.TO_INSTANCE_ID = MAPPING_TO_WIDGET_INST.INSTANCE_ID AND MAPPING_WIDGET_DEP.MAPPING_ID = MAPPING_TO_WIDGET_INST.MAPPING_ID AND MAPPING_WIDGET_DEP.VERSION_NUMBER = MAPPING_TO_WIDGET_INST.VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “FROM_NAME”, “FROM_TYPE”, “FROM_DESCRIPTION”
Displays all transformations from source to target in a mapping and how they are connected to each other through ports.
select “FROM_OBJECT_FIELD_NAME”,
“FROM_OBJECT_NAME”,
“TO_OBJECT_FIELD_NAME”,
“FROM_OBJECT_TYPE_NAME”,
“TO_OBJECT_TYPE_NAME”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”,
“TO_OBJECT_NAME”,
“MAPPING_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_CONN_PORTS.FROM_OBJECT_NAME, MAPPING_CONN_PORTS.FROM_OBJECT_TYPE_NAME, MAPPING_CONN_PORTS.FROM_OBJECT_FIELD_NAME, MAPPING_CONN_PORTS.TO_OBJECT_NAME, MAPPING_CONN_PORTS.TO_OBJECT_TYPE_NAME, MAPPING_CONN_PORTS.TO_OBJECT_FIELD_NAME FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_MAPPING_CONN_PORTS MAPPING_CONN_PORTS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_CONN_PORTS.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_CONN_PORTS. MAPPING_VERSION_NUMBER AND ALL_MAPPINGS.SUBJECT_ID = MAPPING_CONN_PORTS.SUBJECT_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mappings defined as a shortcut by repository and folder.
select “PARENT_MAPPING_LAST_SAVED”,
“PARENT_MAPPING_DESCRIPTION”,
“SUBJECT_AREA”,
“PARENT_MAPPING_VERSION_NUMBER”,
“MAPPING_NAME”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
“REPOSITORY_NAME”,
“MAPPING_VERSION_NUMBER”,
“MAPPING_DESCRIPTION”,
case when (“PARENT_MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_PARENT_MAPPING_VERSION_ST”,
“MAPPING_LAST_SAVED”,
“PARENT_MAPPING_NAME”,
case when (“SHORTCUTFLAG” = 1) then (“PARENT_SUBJECT_AREA”) else (”) end as “Calc_IS_SHORTCUT1”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ShortcutFlag”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION AS MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT as SHORTCUTFLAG, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION AS PARENT_MAPPING_DESCRIPTION, ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG, (COUNT(DISTINCT(MAPPING_SESS_PARTITION_DEF.SESSION_ID))) AS SESSION_ID FROM REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_ALL_MAPPINGS ALL_MAPPINGS LEFT OUTER JOIN REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF ON ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_SESS_PARTITION_DEF. MAPPING_ID WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION , ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID, ALL_MAPPINGS.MAPPING_LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays column names for all sources by repository, folder, and mapping. It also displays properties of these columns such as datatype, precision, and length.
select “SOURCE_NAME”,
“REPOSITORY_NAME”,
“SOURCE_FIELD_PRECISION”,
“SUBJECT_AREA”,
“SOURCE_FIELD_DESCRIPTION”,
“SOURCE_FIELD_BUSINESS_NAME”,
“SOURCE_FIELD_DATATYPE”,
“SOURCE_FIELD_SCALE”,
case when (“SOURCE_FIELD_NULLTYPE” = 1) then (‘Yes’) else (‘No’) end as “Calc_SOURCE_FIELD_NULLTYPE”,
“SOURCE_FIELD_NAME”,
“MAPPING_NAME”,
“SOURCE_FIELD_KEY_TYPE”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_ALL_SOURCES.SOURCE_NAME, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_NUMBER, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_NAME, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_BUSINESS_NAME, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_DESCRIPTION as SOURCE_FIELD_DESCRIPTION, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_DATATYPE, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_PRECISION, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_SCALE, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_KEY_TYPE, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_NULLTYPE FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_SOURCES MAPPING_ALL_SOURCES, REP_ALL_SOURCE_FLDS MAPPING_ALL_SOURCE_FLDS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_SOURCES.PARENT_SOURCE_ID = MAPPING_ALL_SOURCE_FLDS. PARENT_SOURCE_ID AND MAPPING_ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER = MAPPING_ALL_SOURCE_FLDS. PARENT_SOURCE_VERSION_NUMBER AND MAPPING_ALL_SOURCES.PARENT_SOURCE_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_SOURCES.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 1 )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”
Displays sources defined in a mapping by repository and folder.
select “SOURCE_DESCRIPTION”,
“SOURCE_NAME”,
“REPOSITORY_NAME”,
“PARENT_SOURCE_DATABASE_TYPE”,
“MAPPING_NAME”,
“PARENT_SOURCE_BUSINESS_NAME”,
case when (“SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (‘NULL’) end) end) end as “Calc_SOURCE_VERSION_STATUS”,
“SOURCE_VERSION_NUMBER”,
“SOURCE_DATABASE_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME AS MAPPING_NAME, MAPPING_ALL_SOURCES.SOURCE_DATABASE_NAME AS SOURCE_DATABASE_NAME, MAPPING_ALL_SOURCES.SOURCE_NAME AS SOURCE_NAME, MAPPING_ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME AS PARENT_SOURCE_BUSINESS_NAME, MAPPING_ALL_SOURCES.SOURCE_DESCRIPTION AS SOURCE_DESCRIPTION, MAPPING_ALL_SOURCES.SOURCE_VERSION_NUMBER AS SOURCE_VERSION_NUMBER, MAPPING_ALL_SOURCES.SOURCE_VERSION_STATUS AS SOURCE_VERSION_STATUS, MAPPING_ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE AS PARENT_SOURCE_DATABASE_TYPE, MAPPING_ALL_SOURCES.IS_SHORTCUT AS IS_SHORTCUT FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_SOURCES MAPPING_ALL_SOURCES WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_SOURCES.PARENT_SOURCE_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_SOURCES.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 1 )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays targets defined in a mapping by repository and folder.
select case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“TARGET_VERSION_NUMBER”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
“TARGET_NAME”,
“PARENT_TARGET_DATABASE_TYPE”,
“TARGET_DESC”,
“PARENT_TARGET_BUSINESS_NAME”,
“MAPPING_NAME”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_ALL_TARGETS.TARGET_NAME, MAPPING_ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME, MAPPING_ALL_TARGETS.TARGET_DESCRIPTION as TARGET_DESC, MAPPING_ALL_TARGETS.TARGET_VERSION_NUMBER, MAPPING_ALL_TARGETS.TARGET_VERSION_STATUS, MAPPING_ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, MAPPING_ALL_TARGETS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG FROM REP_ALL_TARGETS MAPPING_ALL_TARGETS, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO WHERE (MAPPING_ALL_TARGETS.PARENT_TARGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TARGETS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 2 AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays ports in a transformation by repository, folder, and mapping. It also displays properties of the ports. This report is the second node in a workflow associated with the Mapping List primary report.
select “MAPPING_NAME”,
“EXPRESSION”,
“DATATYPE”,
“SUBJECT_AREA”,
“WIDGET_TYPE_NAME”,
“DESCRIPTION”,
“WGT_PREC”,
“DEFAULT_VALUE”,
“WIDGET_NAME”,
case when (“PORTTYPE” <= 10) then (case when (“PORTTYPE” = 1) then (‘Input Port’) else (case when (“PORTTYPE” = 2) then (‘Output Port’) else (case when (“PORTTYPE” = 3) then (‘Input Output Port’) else (case when (“PORTTYPE” = 6) then (‘Return Port’) else (case when (“PORTTYPE” = 7) then (‘Input Return Port’) else (case when (“PORTTYPE” = 8) then (‘Lookup Port’) else (case when (“PORTTYPE” = 10) then (‘Output Lookup Port’) else (null) end) end) end) end) end) end) end) else (case when (“PORTTYPE” = 14) then (‘Lookup Return Port’) else (case when (“PORTTYPE” = 32) then (‘Variable Port’) else (case when (“PORTTYPE” = 65) then (‘Input Port and Master Detail Port’) else (case when (“PORTTYPE” = 67) then (‘Input Port, Output Port and Master Detail Port’) else (case when (“PORTTYPE” = 130) then (‘Output Port and Generated Key Port’) else (case when (“PORTTYPE” = 258) then (‘Output Port and Generated Column ID Port’) else (case when (“PORTTYPE” = 515) then (‘Input Port, Output Port and Message ID Port’) else (case when (“PORTTYPE” = 1027) then (‘Input Port, Output Port and Message Data Port’) else (case when (“PORTTYPE” = 2050) then (‘Dynamic Lookup Port’) else (null) end) end) end) end) end) end) end) end) end) end as “Calc_PORTTYPE”,
“REPOSITORY_NAME”,
“WGT_SCALE”,
“FIELD_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_NAME, MAPPING_TRANSFORM_WIDGET_FIELD.FIELD_ORDER, MAPPING_TRANSFORM_WIDGET_FIELD.FIELD_NAME, MAPPING_TRANSFORM_WIDGET_FIELD.DESCRIPTION as DESCRIPTION, MAPPING_TRANSFORM_WIDGET_FIELD.PORTTYPE, MAPPING_TRANSFORM_WIDGET_FIELD.DATATYPE, MAPPING_TRANSFORM_WIDGET_FIELD.WGT_PREC, MAPPING_TRANSFORM_WIDGET_FIELD.WGT_SCALE, MAPPING_TRANSFORM_WIDGET_FIELD.DEFAULT_VALUE as DEFAULT_VALUE, MAPPING_TRANSFORM_WIDGET_FIELD.EXPRESSION as EXPRESSION FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_TRANSFORMS MAPPING_ALL_TRANSFORMS, REP_WIDGET_FIELD MAPPING_TRANSFORM_WIDGET_FIELD WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_TRANSFORM_WIDGET_FIELD. WIDGET_ID AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER = MAPPING_TRANSFORM_WIDGET_FIELD.VERSION_NUMBER AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TRANSFORMS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPING_WIDGET_INST.WIDGET_TYPE )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”
Displays transformations used in a mapping by repository and folder. This report is the first node in the analytic workflow associated with the Mapping List primary report.
select case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
“WIDGET_NAME”,
“MAPPING_NAME”,
“WIDGET_DESCRIPTION”,
“WIDGET_TYPE_NAME”,
case when (“PARENT_WIDGET_IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_WIDGET_IS_REUSABLE”,
“WIDGET_VERSION_NUMBER”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, MAPPING_ALL_TRANSFORMS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME AS MAPPING_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME AS WIDGET_TYPE_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_NAME AS WIDGET_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_DESCRIPTION AS WIDGET_DESCRIPTION, MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_NUMBER AS WIDGET_VERSION_NUMBER, MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_STATUS AS WIDGET_VERSION_STATUS , MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE AS PARENT_WIDGET_IS_REUSABLE, MAPPING_ALL_TRANSFORMS.IS_SHORTCUT AS IS_SHORTCUT FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_TRANSFORMS MAPPING_ALL_TRANSFORMS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TRANSFORMS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPING_WIDGET_INST.WIDGET_TYPE )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘Mapping_Name’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “WIDGET_TYPE_NAME”, “WIDGET_NAME”
Displays mappings that do not have any sessions defined by repository and folder.
select “MAPPING_VERSION_NUMBER”,
“MAPPING_DESCRIPTION”,
“SESSION_ID”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
“MAPPING_LAST_SAVED”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
“REPOSITORY_NAME”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ShortcutFlag”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION AS MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT as SHORTCUTFLAG, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION AS PARENT_MAPPING_DESCRIPTION, ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG, (COUNT(DISTINCT(MAPPING_SESS_PARTITION_DEF.SESSION_ID))) AS SESSION_ID FROM REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_ALL_MAPPINGS ALL_MAPPINGS LEFT OUTER JOIN REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF ON ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_SESS_PARTITION_DEF. MAPPING_ID WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION , ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID, ALL_MAPPINGS.MAPPING_LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_ID” = 0
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays the data flow from the source to the target by repository and folder. The report lists all the source and target ports, the mappings in which the ports are connected, the transformation expression that show how data for the target port is derived.
select “REPOSITORY_NAME”,
“TARGET_NAME”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
“SOURCE_FIELD_NAME”,
“TARGET_COLUMN_NAME”,
“SOURCE_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA as SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME as MAPPING_NAME, MAPPING_FLD_MAPPING.SOURCE_NAME as SOURCE_NAME, MAPPING_FLD_MAPPING.SOURCE_FIELD_NAME as SOURCE_FIELD_NAME, MAPPING_FLD_MAPPING.TARGET_NAME as TARGET_NAME, MAPPING_FLD_MAPPING.TARGET_COLUMN_NAME as TARGET_COLUMN_NAME FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_FLD_MAPPING MAPPING_FLD_MAPPING WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_FLD_MAPPING.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_FLD_MAPPING. MAPPING_VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Mapplet
Displays all transformations used in a mapplet by repository and folder.
select “MAPPLET_NAME”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
“INSTANCE_NAME”,
“DESCRIPTION”,
“WIDGET_TYPE_NAME”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA as SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME as MAPPLET_NAME, MAPPLET_WIDGET_INST.WIDGET_TYPE_NAME as WIDGET_TYPE_NAME, MAPPLET_WIDGET_INST.INSTANCE_NAME as INSTANCE_NAME, MAPPLET_WIDGET_INST.DESCRIPTION as DESCRIPTION FROM REP_ALL_MAPPLETS ALL_MAPPLETS, REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPLET_NAME” in (‘<Mapplet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mapplets available by repository and folder. It displays properties of the mapplet such as the number of sources used in a mapplet, the number of transformations, or the number of targets. This is a primary report in an analytic workflow.
select “REPOSITORY_NAME”,
case when (“MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPLET_VERSION_STATUS”,
“MAPPLET_DESCRIPTION”,
“SOURCE_COUNT”,
“MAPPLET_NAME”,
“MAPPLET_VERSION_NUMBER”,
“TRANSFORMATION_COUNT”,
“MAPPLET_LAST_SAVED”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
case when (“SHORTCUTFLAG” = 1) then (“PARENT_SUBJECT_AREA”) else (”) end as “Calc_IS_SHORTCUT1”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION AS MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS, ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION AS PARENT_MAPPLET_DESCRIPTION, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID as VALIDFLAG, ALL_MAPPLETS.IS_SHORTCUT as SHORTCUTFLAG, (COUNT(DISTINCT( MAPPLET_MAPPING_WIDGET_INST.MAPPING_ID))) AS MAPPING_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS SOURCE_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE NOT IN (1, 2, 44) THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS TRANSFORMATION_COUNT FROM REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_MAPPLETS ALL_MAPPLETS LEFT OUTER JOIN REP_WIDGET_INST MAPPLET_MAPPING_WIDGET_INST ON ALL_MAPPLETS.REF_WIDGET_ID = MAPPLET_MAPPING_WIDGET_INST.WIDGET_ID AND ALL_MAPPLETS.SUBJECT_ID = MAPPLET_MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPLET_MAPPING_WIDGET_INST.WIDGET_TYPE = 44 WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS , ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION , ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.IS_SHORTCUT , ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPLET_NAME” in (‘<Mapplet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mapplets defined as a shortcut by repository and folder.
select case when (“MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPLET_VERSION_STATUS”,
“MAPPLET_DESCRIPTION”,
“PARENT_MAPPLET_LAST_SAVED”,
“MAPPLET_NAME”,
“MAPPLET_LAST_SAVED”,
“PARENT_MAPPLET_VERSION_NUMBER”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
case when (“SHORTCUTFLAG” = 1) then (“PARENT_SUBJECT_AREA”) else (”) end as “Calc_IS_SHORTCUT1”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
case when (“PARENT_MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (‘NULL’) end) end) end as “Calc_PARENT_MAPPLET_VERSION_ST”,
“MAPPLET_VERSION_NUMBER”,
“PARENT_MAPPLET_DESCRIPTION”,
“PARENT_MAPPLET_NAME”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION AS MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS, ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION AS PARENT_MAPPLET_DESCRIPTION, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID as VALIDFLAG, ALL_MAPPLETS.IS_SHORTCUT as SHORTCUTFLAG, (COUNT(DISTINCT( MAPPLET_MAPPING_WIDGET_INST.MAPPING_ID))) AS MAPPING_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS SOURCE_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE NOT IN (1, 2, 44) THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS TRANSFORMATION_COUNT FROM REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_MAPPLETS ALL_MAPPLETS LEFT OUTER JOIN REP_WIDGET_INST MAPPLET_MAPPING_WIDGET_INST ON ALL_MAPPLETS.REF_WIDGET_ID = MAPPLET_MAPPING_WIDGET_INST.WIDGET_ID AND ALL_MAPPLETS.SUBJECT_ID = MAPPLET_MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPLET_MAPPING_WIDGET_INST.WIDGET_TYPE = 44 WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS , ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION , ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.IS_SHORTCUT , ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays column names for all sources by repository, folder, and mapplet. It also displays properties for these columns such as datatype, length, and precision.
select “SOURCE_FIELD_PRECISION”,
“SOURCE_FIELD_SCALE”,
“SOURCE_FIELD_DATATYPE”,
“MAPPLET_NAME”,
case when (“SOURCE_FIELD_NULLTYPE” = 1) then (‘Yes’) else (‘No’) end as “Calc_SOURCE_FIELD_NULLTYPE”,
“SOURCE_FIELD_NAME”,
“SOURCE_NAME”,
“SUBJECT_AREA”,
“SOURCE_FIELD_KEY_TYPE”,
“SOURCE_FIELD_DESCRIPTION”,
“SOURCE_FIELD_BUSINESS_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME AS MAPPLET_NAME, MAPPLET_ALL_SOURCES.SOURCE_NAME AS SOURCE_NAME, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_NUMBER AS SOURCE_FIELD_NUMBER, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_NAME AS SOURCE_FIELD_NAME, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_BUSINESS_NAME AS SOURCE_FIELD_BUSINESS_NAME, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_DESCRIPTION AS SOURCE_FIELD_DESCRIPTION, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_DATATYPE AS SOURCE_FIELD_DATATYPE, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_PRECISION AS SOURCE_FIELD_PRECISION, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_SCALE AS SOURCE_FIELD_SCALE, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_KEY_TYPE AS SOURCE_FIELD_KEY_TYPE, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_NULLTYPE AS SOURCE_FIELD_NULLTYPE FROM REP_ALL_MAPPLETS ALL_MAPPLETS, REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_SOURCES MAPPLET_ALL_SOURCES, REP_ALL_SOURCE_FLDS MAPPLET_ALL_SOURCE_FLDS WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND MAPPLET_ALL_SOURCES.PARENT_SOURCE_ID = MAPPLET_ALL_SOURCE_FLDS. PARENT_SOURCE_ID AND MAPPLET_ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER = MAPPLET_ALL_SOURCE_FLDS. PARENT_SOURCE_VERSION_NUMBER AND MAPPLET_ALL_SOURCES.PARENT_SOURCE_ID = MAPPLET_WIDGET_INST.WIDGET_ID AND MAPPLET_ALL_SOURCES.SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND MAPPLET_WIDGET_INST.WIDGET_TYPE = 1)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPLET_NAME” in (‘<Mapplet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPLET_NAME”
This is a subreport in the Mapplet Composite Report. This report displays transformations used in a mapplet by repository and folder.
select “WIDGET_DESCRIPTION”,
case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
case when (“PARENT_WIDGET_IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_WIDGET_IS_REUSABLE”,
“WIDGET_VERSION_NUMBER”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“WIDGET_TYPE_NAME”,
“WIDGET_NAME”,
“MAPPLET_NAME”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME AS MAPPLET_NAME, MAPPLET_ALL_TRANSFORMS.WIDGET_TYPE_NAME AS WIDGET_TYPE_NAME, MAPPLET_ALL_TRANSFORMS.WIDGET_NAME AS WIDGET_NAME, MAPPLET_ALL_TRANSFORMS.WIDGET_DESCRIPTION AS WIDGET_DESCRIPTION, MAPPLET_ALL_TRANSFORMS.WIDGET_VERSION_NUMBER AS WIDGET_VERSION_NUMBER, MAPPLET_ALL_TRANSFORMS.WIDGET_VERSION_STATUS AS WIDGET_VERSION_STATUS, MAPPLET_ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE AS PARENT_WIDGET_IS_REUSABLE, MAPPLET_ALL_TRANSFORMS.IS_SHORTCUT AS IS_SHORTCUT FROM REP_ALL_MAPPLETS ALL_MAPPLETS, REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_TRANSFORMS MAPPLET_ALL_TRANSFORMS WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND MAPPLET_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPLET_WIDGET_INST.WIDGET_ID AND MAPPLET_ALL_TRANSFORMS.SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND MAPPLET_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPLET_WIDGET_INST.WIDGET_TYPE )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPLET_NAME” in (‘<Mapplet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mapplets defined in a folder but not used in any mapping in that folder.
select “REPOSITORY_NAME”,
case when (“MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPLET_VERSION_STATUS”,
“MAPPLET_DESCRIPTION”,
“MAPPLET_NAME”,
“MAPPLET_VERSION_NUMBER”,
“MAPPLET_LAST_SAVED”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION AS MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS, ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION AS PARENT_MAPPLET_DESCRIPTION, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID as VALIDFLAG, ALL_MAPPLETS.IS_SHORTCUT as SHORTCUTFLAG, (COUNT(DISTINCT( MAPPLET_MAPPING_WIDGET_INST.MAPPING_ID))) AS MAPPING_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS SOURCE_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE NOT IN (1, 2, 44) THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS TRANSFORMATION_COUNT FROM REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_MAPPLETS ALL_MAPPLETS LEFT OUTER JOIN REP_WIDGET_INST MAPPLET_MAPPING_WIDGET_INST ON ALL_MAPPLETS.REF_WIDGET_ID = MAPPLET_MAPPING_WIDGET_INST.WIDGET_ID AND ALL_MAPPLETS.SUBJECT_ID = MAPPLET_MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPLET_MAPPING_WIDGET_INST.WIDGET_TYPE = 44 WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS , ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION , ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.IS_SHORTCUT , ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_COUNT” = 0
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Sessions
Displays invalid sessions by repository by folder.
select case when (“REUSABLEFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ReusableFlag”,
“SESSION_NAME”,
“REPOSITORY_NAME”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
“COMMENTS”,
“MAPPING_NAME”,
“SUBJECT_AREA”,
“LAST_SAVED”,
“MAPPING_VERSION_NUMBER”,
“SESSION_VERSION_NUMBER”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA AS SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME AS SESSION_NAME, ALL_SESSIONS.MAPPING_NAME, (COUNT(DISTINCT( SESSION_TASK_INST.WORKFLOW_ID))) AS WORKFLOWID, ALL_SESSIONS.COMMENTS AS COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER AS SESSION_VERSION_NUMBER, ALL_SESSIONS.MAPPING_VERSION_NUMBER AS MAPPING_VERSION_NUMBER, ALL_SESSIONS.IS_VALID AS VALIDFLAG, ALL_SESSIONS.IS_REUSABLE AS REUSABLEFLAG, ALL_SESSIONS.LAST_SAVED AS LAST_SAVED FROM REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_LOAD_SESSIONS ALL_SESSIONS LEFT OUTER JOIN REP_TASK_INST SESSION_TASK_INST ON ALL_SESSIONS.SESSION_ID = SESSION_TASK_INST.TASK_ID AND SESSION_TASK_INST.TASK_TYPE = 68 WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, ALL_SESSIONS.MAPPING_NAME , ALL_SESSIONS.COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER , ALL_SESSIONS.MAPPING_VERSION_NUMBER , ALL_SESSIONS.IS_VALID , ALL_SESSIONS.IS_REUSABLE , ALL_SESSIONS.LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘No’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays, by repository by folder, components included in a workflow, such as pre- and post-session commands and failure and success emails.
select “VAL_NAME”,
“PM_VALUE”,
“SESSION_NAME”,
case when (“OBJECT_TYPE” = 58) then (‘Command’) else (case when (“OBJECT_TYPE” = 65) then (‘Email’) else (”) end) end as “Calc_OBJECT_TYPE”,
“SUBJECT_AREA”,
“DESCRIPTION”,
“REPOSITORY_NAME”
from (SELECT DISTINCT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, SESSION_COMPONENT.OBJECT_TYPE as OBJECT_TYPE, SESSION_COMPONENT.VAL_NAME, SESSION_COMPONENT.DESCRIPTION as DESCRIPTION, SESSION_COMPONENT.PM_VALUE as PM_VALUE FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_COMPONENT SESSION_COMPONENT WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND ALL_SESSIONS.SESSION_ID = SESSION_COMPONENT.TASK_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_COMPONENT.VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays the configurable attributes and their selected values for each session by repository by folder
select “ATTR_VALUE”,
“SUBJECT_AREA”,
“ATTR_NAME”,
“SESSION_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, SESSION_CONFIG_PARM.ATTR_NAME, SESSION_CONFIG_PARM.ATTR_VALUE AS ATTR_VALUE FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_SESS_CONFIG_PARM SESSION_CONFIG_PARM WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND ALL_SESSIONS.SESSION_ID = SESSION_CONFIG_PARM.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_CONFIG_PARM. SESSION_VERSION_NUMBER )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session-Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all sessions and their properties by repository by folder.
select case when (“REUSABLEFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ReusableFlag”,
“SESSION_NAME”,
“REPOSITORY_NAME”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
“COMMENTS”,
“MAPPING_NAME”,
“SUBJECT_AREA”,
“LAST_SAVED”,
“MAPPING_VERSION_NUMBER”,
“SESSION_VERSION_NUMBER”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA AS SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME AS SESSION_NAME, ALL_SESSIONS.MAPPING_NAME, (COUNT(DISTINCT( SESSION_TASK_INST.WORKFLOW_ID))) AS WORKFLOWID, ALL_SESSIONS.COMMENTS AS COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER AS SESSION_VERSION_NUMBER, ALL_SESSIONS.MAPPING_VERSION_NUMBER AS MAPPING_VERSION_NUMBER, ALL_SESSIONS.IS_VALID AS VALIDFLAG, ALL_SESSIONS.IS_REUSABLE AS REUSABLEFLAG, ALL_SESSIONS.LAST_SAVED AS LAST_SAVED FROM REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_LOAD_SESSIONS ALL_SESSIONS LEFT OUTER JOIN REP_TASK_INST SESSION_TASK_INST ON ALL_SESSIONS.SESSION_ID = SESSION_TASK_INST.TASK_ID AND SESSION_TASK_INST.TASK_TYPE = 68 WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, ALL_SESSIONS.MAPPING_NAME , ALL_SESSIONS.COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER , ALL_SESSIONS.MAPPING_VERSION_NUMBER , ALL_SESSIONS.IS_VALID , ALL_SESSIONS.IS_REUSABLE , ALL_SESSIONS.LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays transformation instances and the transformation type for a partition by repository by folder.
select “SUBJECT_AREA”,
“REPOSITORY_NAME”,
“INSTANCE_NAME”,
“SESSION_NAME”,
“PARTITION_ID”,
“TYPE_NAME”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, SESSION_PARTITION_DEF.TYPE_NAME, SESSION_PARTITION_DEF.INSTANCE_NAME, (COUNT(SESSION_PARTITION_DEF.PARTITION_ID)) as PARTITION_ID FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_PARTITION_DEF SESSION_PARTITION_DEF, REP_REPOSIT_INFO SESSION_REPOSIT_INFO WHERE (ALL_SESSIONS.SESSION_ID = SESSION_PARTITION_DEF.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_PARTITION_DEF.VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, SESSION_PARTITION_DEF.TYPE_NAME, SESSION_PARTITION_DEF.INSTANCE_NAME) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays sessions that are not associated with any workflow by repository by folder
select “SESSION_NAME”,
“REPOSITORY_NAME”,
“MAPPING_NAME”,
“WORKFLOWID”,
“SUBJECT_AREA”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA AS SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME AS SESSION_NAME, ALL_SESSIONS.MAPPING_NAME, (COUNT(DISTINCT( SESSION_TASK_INST.WORKFLOW_ID))) AS WORKFLOWID, ALL_SESSIONS.COMMENTS AS COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER AS SESSION_VERSION_NUMBER, ALL_SESSIONS.MAPPING_VERSION_NUMBER AS MAPPING_VERSION_NUMBER, ALL_SESSIONS.IS_VALID AS VALIDFLAG, ALL_SESSIONS.IS_REUSABLE AS REUSABLEFLAG, ALL_SESSIONS.LAST_SAVED AS LAST_SAVED FROM REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_LOAD_SESSIONS ALL_SESSIONS LEFT OUTER JOIN REP_TASK_INST SESSION_TASK_INST ON ALL_SESSIONS.SESSION_ID = SESSION_TASK_INST.TASK_ID AND SESSION_TASK_INST.TASK_TYPE = 68 WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, ALL_SESSIONS.MAPPING_NAME , ALL_SESSIONS.COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER , ALL_SESSIONS.MAPPING_VERSION_NUMBER , ALL_SESSIONS.IS_VALID , ALL_SESSIONS.IS_REUSABLE , ALL_SESSIONS.LAST_SAVED) “query”
where “SUBJECT_AREA” in (<Folder_Name>’) and “WORKFLOWID” = 0
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Sources
Displays relational and non-relational sources by repository and folder. It also shows the source properties.
select “REF_WIDGET_ID_GREATER”,
“PARENT_SOURCE_BUSINESS_NAME”,
“REPOSITORY_NAME”,
“SOURCE_LAST_SAVED”,
case when (“SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_SOURCE_VERSION_STATUS”,
“REF_WIDGET_ID”,
case when (“IS_SHORTCUT” = 1) then (‘yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”,
“SOURCE_NAME”,
“PARENT_SOURCE_DATABASE_TYPE”,
“SOURCE_VERSION_NUMBER”,
“SOURCE_DATABASE_NAME”,
“SOURCE_DESCRIPTION”
from (SELECT DISTINCT SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION AS SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS AS SOURCE_VERSION_STATUS, ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION AS PARENT_SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS AS PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT AS IS_SHORTCUT, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID > 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID_GREATER, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID = 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID , (COUNT(DISTINCT(SRC_WIDGET_INST.MAPPING_ID))) AS MAPPING_ID FROM REP_REPOSIT_INFO SRC_REPOSIT_INFO, REP_ALL_SOURCES ALL_SOURCES LEFT OUTER JOIN REP_WIDGET_INST SRC_WIDGET_INST ON ALL_SOURCES.PARENT_SOURCE_ID = SRC_WIDGET_INST.WIDGET_ID AND ALL_SOURCES.SUBJECT_ID = SRC_WIDGET_INST.SUBJECT_ID AND SRC_WIDGET_INST.WIDGET_TYPE = 1 WHERE (ALL_SOURCES.PARENT_SOURCE_ID <> SRC_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS , ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SOURCE_NAME” in (‘<Source_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mappings that use a particular source by repository and folder.
select “MAPPING_DESCRIPTION”,
“REPOSITORY_NAME”,
“MAPPING_VERSION_NUMBER”,
case when (“PARENT_MAPPING_IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_MAPPING_IS_VALID”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
“SOURCE_NAME”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘LogicallyDeleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (‘NULL’) end) end) end as “Calc_MAPPING_VERSION_STATUS”
from (SELECT DISTINCT SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_NAME, SRC_ALL_MAPPINGS.MAPPING_NAME, SRC_ALL_MAPPINGS.MAPPING_DESCRIPTION as MAPPING_DESCRIPTION, SRC_ALL_MAPPINGS.MAPPING_VERSION_NUMBER, SRC_ALL_MAPPINGS.MAPPING_VERSION_STATUS , SRC_ALL_MAPPINGS.PARENT_MAPPING_IS_VALID FROM REP_REPOSIT_INFO SRC_REPOSIT_INFO, REP_ALL_MAPPINGS SRC_ALL_MAPPINGS, REP_ALL_SOURCES ALL_SOURCES LEFT OUTER JOIN REP_WIDGET_INST SRC_WIDGET_INST ON ALL_SOURCES.PARENT_SOURCE_ID = SRC_WIDGET_INST.WIDGET_ID AND ALL_SOURCES.SUBJECT_ID = SRC_WIDGET_INST.SUBJECT_ID AND SRC_WIDGET_INST.WIDGET_TYPE = 1 WHERE (ALL_SOURCES.PARENT_SOURCE_ID <> SRC_REPOSIT_INFO.REPOSITORY_ID AND SRC_WIDGET_INST.MAPPING_ID = SRC_ALL_MAPPINGS.PARENT_MAPPING_ID AND SRC_WIDGET_INST.VERSION_NUMBER = SRC_ALL_MAPPINGS. PARENT_MAPPING_VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SOURCE_NAME” in (‘<Source_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays sources that are defined as shortcuts by repository and folder.
select “PARENT_SUBJECT_AREA”,
“PARENT_SOURCE_DATABASE_NAME”,
“REPOSITORY_NAME”,
“SOURCE_LAST_SAVED”,
case when (“SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_SOURCE_VERSION_STATUS”,
case when (“IS_SHORTCUT” = 1) then (‘yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
case when (“PARENT_SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_PARENT_SOURCE_VERSION_STA”,
“SUBJECT_AREA”,
“PARENT_SOURCE_VERSION_NUMBER”,
“PARENT_SOURCE_DESCRIPTION”,
“SOURCE_NAME”,
“PARENT_SOURCE_LAST_SAVED”,
“PARENT_SOURCE_DATABASE_TYPE”,
“SOURCE_VERSION_NUMBER”,
“SOURCE_DATABASE_NAME”,
“SOURCE_DESCRIPTION”,
“PARENT_SOURCE_NAME”
from (SELECT DISTINCT SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION AS SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS AS SOURCE_VERSION_STATUS, ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION AS PARENT_SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS AS PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT AS IS_SHORTCUT, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID > 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID_GREATER, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID = 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID , (COUNT(DISTINCT(SRC_WIDGET_INST.MAPPING_ID))) AS MAPPING_ID FROM REP_REPOSIT_INFO SRC_REPOSIT_INFO, REP_ALL_SOURCES ALL_SOURCES LEFT OUTER JOIN REP_WIDGET_INST SRC_WIDGET_INST ON ALL_SOURCES.PARENT_SOURCE_ID = SRC_WIDGET_INST.WIDGET_ID AND ALL_SOURCES.SUBJECT_ID = SRC_WIDGET_INST.SUBJECT_ID AND SRC_WIDGET_INST.WIDGET_TYPE = 1 WHERE (ALL_SOURCES.PARENT_SOURCE_ID <> SRC_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS , ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“IS_SHORTCUT” = 1) then (‘yes’) else (‘No’) end = ‘yes’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays sources that are not used in any mapping or mapplet by repository and folder.
select “SUBJECT_AREA”,
“SOURCE_NAME”,
“PARENT_SOURCE_DATABASE_TYPE”,
“SOURCE_VERSION_NUMBER”,
“PARENT_SOURCE_BUSINESS_NAME”,
“REPOSITORY_NAME”,
“SOURCE_LAST_SAVED”,
“SOURCE_DATABASE_NAME”,
“SOURCE_DESCRIPTION”,
case when (“SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_SOURCE_VERSION_STATUS”,
“MAPPING_ID”,
case when (“IS_SHORTCUT” = 1) then (‘yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION AS SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS AS SOURCE_VERSION_STATUS, ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION AS PARENT_SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS AS PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT AS IS_SHORTCUT, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID > 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID_GREATER, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID = 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID , (COUNT(DISTINCT(SRC_WIDGET_INST.MAPPING_ID))) AS MAPPING_ID FROM REP_REPOSIT_INFO SRC_REPOSIT_INFO, REP_ALL_SOURCES ALL_SOURCES LEFT OUTER JOIN REP_WIDGET_INST SRC_WIDGET_INST ON ALL_SOURCES.PARENT_SOURCE_ID = SRC_WIDGET_INST.WIDGET_ID AND ALL_SOURCES.SUBJECT_ID = SRC_WIDGET_INST.SUBJECT_ID AND SRC_WIDGET_INST.WIDGET_TYPE = 1 WHERE (ALL_SOURCES.PARENT_SOURCE_ID <> SRC_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS , ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT) “query”
where “MAPPING_ID” = 0 and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “SOURCE_DATABASE_NAME”, “SOURCE_NAME”, “PARENT_SOURCE_BUSINESS_NAME”
Targets
Displays relational and non-relational targets available by repository and folder. It also displays the target properties.
select “SUBJECT_AREA”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”,
“REF_WIDGET_ID2”,
“REF_WIDGET_ID1”,
“TARGET_NAME”,
“TARGET_LAST_SAVED”,
“REPOSITORY_NAME”,
“TARGET_DESCRIPTION”,
“PARENT_TARGET_BUSINESS_NAME”,
“PARENT_TARGET_DATABASE_TYPE”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“TARGET_VERSION_NUMBER”
from (SELECT DISTINCT TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION AS TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS as TARGET_VERSION_STATUS, ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION AS PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS as PARENT_TARGET_VERSION_STATUS, ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT as IS_SHORTCUT, ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME as PARENT_TARGET_BUSINESS_NAME, (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS MAPPING_COUNT , (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID1, (COUNT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.INSTANCE_ID END)) AS REF_WIDGET_ID2 FROM REP_REPOSIT_INFO TARG_REPOSIT_INFO, REP_ALL_TARGETS ALL_TARGETS LEFT OUTER JOIN REP_WIDGET_INST TARG_WIDGET_INST ON ALL_TARGETS.PARENT_TARGET_ID = TARG_WIDGET_INST.WIDGET_ID AND ALL_TARGETS.SUBJECT_ID = TARG_WIDGET_INST.SUBJECT_ID AND TARG_WIDGET_INST.WIDGET_TYPE = 2 WHERE (ALL_TARGETS.PARENT_TARGET_ID <> TARG_REPOSIT_INFO.REPOSITORY_ID) GROUP BY TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA , ALL_TARGETS.TARGET_NAME , ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME , ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE , ALL_TARGETS.TARGET_VERSION_NUMBER , ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED , ALL_TARGETS.IS_SHORTCUT, TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS , ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TARGET_NAME” in (‘<Target_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mappings that use a particular target by repository and folder.
select case when (“PARENT_MAPPING_IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_MAPPING_IS_VALID”,
“REPOSITORY_NAME”,
“DESCRPTION”,
“MAPPING_NAME”,
“MAPPING_VERSION_NUMBER”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
“SUBJECT_AREA”,
“TARGET_NAME”
from (SELECT DISTINCT TARG_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA as SUBJECT_AREA, ALL_TARGETS.TARGET_NAME as TARGET_NAME, TARG_ALL_MAPPINGS.MAPPING_NAME as MAPPING_NAME, TARG_ALL_MAPPINGS.MAPPING_DESCRIPTION as DESCRPTION, TARG_ALL_MAPPINGS.MAPPING_VERSION_NUMBER as MAPPING_VERSION_NUMBER, TARG_ALL_MAPPINGS.MAPPING_VERSION_STATUS as MAPPING_VERSION_STATUS, TARG_ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as PARENT_MAPPING_IS_VALID FROM REP_REPOSIT_INFO TARG_REPOSIT_INFO, REP_ALL_MAPPINGS TARG_ALL_MAPPINGS, REP_ALL_TARGETS ALL_TARGETS LEFT OUTER JOIN REP_WIDGET_INST TARG_WIDGET_INST ON ALL_TARGETS.PARENT_TARGET_ID = TARG_WIDGET_INST.WIDGET_ID AND ALL_TARGETS.SUBJECT_ID = TARG_WIDGET_INST.SUBJECT_ID AND TARG_WIDGET_INST.WIDGET_TYPE = 2 WHERE (ALL_TARGETS.PARENT_TARGET_ID <> TARG_REPOSIT_INFO.REPOSITORY_ID AND TARG_WIDGET_INST.MAPPING_ID = TARG_ALL_MAPPINGS.PARENT_MAPPING_ID AND TARG_WIDGET_INST.VERSION_NUMBER = TARG_ALL_MAPPINGS. PARENT_MAPPING_VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TARGET_NAME” in (‘<Target_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays targets that are defined as shortcuts by repository and folder.
select “SUBJECT_AREA”,
“PARENT_TARGET_DESCRIPTION”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”,
“TARGET_LAST_SAVED”,
case when (“PARENT_TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_PARENT_TARGET_VERSION_STA”,
“TARGET_DESCRIPTION”,
“PARENT_TARGET_NAME”,
“TARGET_VERSION_NUMBER”,
“PARENT_TARGET_VERSION_NUMBER”,
“TARGET_NAME”,
“REPOSITORY_NAME”,
“PARENT_SUBJECT_AREA”,
“PARENT_TARGET_LAST_SAVED”,
“PARENT_TARGET_DATABASE_TYPE”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION AS TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS as TARGET_VERSION_STATUS, ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION AS PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS as PARENT_TARGET_VERSION_STATUS, ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT as IS_SHORTCUT, ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME as PARENT_TARGET_BUSINESS_NAME, (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS MAPPING_COUNT , (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID1, (COUNT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.INSTANCE_ID END)) AS REF_WIDGET_ID2 FROM REP_REPOSIT_INFO TARG_REPOSIT_INFO, REP_ALL_TARGETS ALL_TARGETS LEFT OUTER JOIN REP_WIDGET_INST TARG_WIDGET_INST ON ALL_TARGETS.PARENT_TARGET_ID = TARG_WIDGET_INST.WIDGET_ID AND ALL_TARGETS.SUBJECT_ID = TARG_WIDGET_INST.SUBJECT_ID AND TARG_WIDGET_INST.WIDGET_TYPE = 2 WHERE (ALL_TARGETS.PARENT_TARGET_ID <> TARG_REPOSIT_INFO.REPOSITORY_ID) GROUP BY TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA , ALL_TARGETS.TARGET_NAME , ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME , ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE , ALL_TARGETS.TARGET_VERSION_NUMBER , ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED , ALL_TARGETS.IS_SHORTCUT, TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS , ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT) “query”
where case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’ and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays targets that are not used in any mapping by repository and folder.
select “SUBJECT_AREA”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”,
“TARGET_NAME”,
“TARGET_LAST_SAVED”,
“REPOSITORY_NAME”,
“TARGET_DESCRIPTION”,
“MAPPING_COUNT”,
“PARENT_TARGET_DATABASE_TYPE”,
“PARENT_TARGET_BUSINESS_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“TARGET_VERSION_NUMBER”
from (SELECT DISTINCT TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION AS TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS as TARGET_VERSION_STATUS, ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION AS PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS as PARENT_TARGET_VERSION_STATUS, ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT as IS_SHORTCUT, ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME as PARENT_TARGET_BUSINESS_NAME, (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS MAPPING_COUNT , (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID1, (COUNT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.INSTANCE_ID END)) AS REF_WIDGET_ID2 FROM REP_REPOSIT_INFO TARG_REPOSIT_INFO, REP_ALL_TARGETS ALL_TARGETS LEFT OUTER JOIN REP_WIDGET_INST TARG_WIDGET_INST ON ALL_TARGETS.PARENT_TARGET_ID = TARG_WIDGET_INST.WIDGET_ID AND ALL_TARGETS.SUBJECT_ID = TARG_WIDGET_INST.SUBJECT_ID AND TARG_WIDGET_INST.WIDGET_TYPE = 2 WHERE (ALL_TARGETS.PARENT_TARGET_ID <> TARG_REPOSIT_INFO.REPOSITORY_ID) GROUP BY TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA , ALL_TARGETS.TARGET_NAME , ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME , ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE , ALL_TARGETS.TARGET_VERSION_NUMBER , ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED , ALL_TARGETS.IS_SHORTCUT, TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS , ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT) “query”
where “MAPPING_COUNT” = 0 and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Transformations
Displays transformations that are defined as shortcuts by repository and folder.
select “WIDGET_LAST_SAVED”,
case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
“REPOSITORY_NAME”,
“PARENT_SUBJECT_AREA”,
“SUBJECT_AREA”,
“WIDGET_NAME”,
“PARENT_WIDGET_DESCRIPTION”,
“PARENT_WIDGET_NAME”,
case when (“PARENT_WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_PARENT_WIDGET_VERSION_STA”,
“WIDGET_VERSION_NUMBER”,
“PARENT_WIDGET_LAST_SAVED”,
“WIDGET_DESCRIPTION”,
“PARENT_WIDGET_VERSION_NUMBER”,
“WIDGET_TYPE_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME, ALL_TRANSFORMS.SUBJECT_AREA, ALL_TRANSFORMS.WIDGET_TYPE_NAME, ALL_TRANSFORMS.WIDGET_NAME, ALL_TRANSFORMS.WIDGET_DESCRIPTION AS WIDGET_DESCRIPTION, ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.WIDGET_VERSION_STATUS as WIDGET_VERSION_STATUS, ALL_TRANSFORMS.WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_WIDGET_NAME, ALL_TRANSFORMS.PARENT_WIDGET_DESCRIPTION AS PARENT_WIDGET_DESCRIPTION, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_STATUS as PARENT_WIDGET_VERSION_STATUS, ALL_TRANSFORMS.PARENT_WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_SUBJECT_AREA, ALL_TRANSFORMS.IS_SHORTCUT as IS_SHORTCUT , (COUNT(DISTINCT(TRANSFORM_WIDGET_INST.MAPPING_ID))) as MAPPING_ID FROM REP_REPOSIT_INFO TRANSFORM_REPOSIT_INFO, REP_ALL_TRANSFORMS ALL_TRANSFORMS LEFT OUTER JOIN REP_WIDGET_INST TRANSFORM_WIDGET_INST ON ALL_TRANSFORMS.PARENT_WIDGET_ID = TRANSFORM_WIDGET_INST.WIDGET_ID AND ALL_TRANSFORMS.SUBJECT_ID = TRANSFORM_WIDGET_INST.SUBJECT_ID AND ALL_TRANSFORMS.WIDGET_TYPE_ID = TRANSFORM_WIDGET_INST.WIDGET_TYPE AND (TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 1 AND TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 2 AND TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 44 ) AND ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE = 1 WHERE (ALL_TRANSFORMS.PARENT_WIDGET_ID <> TRANSFORM_REPOSIT_INFO.REPOSITORY_ID AND ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE = 1) GROUP BY TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME, ALL_TRANSFORMS.SUBJECT_AREA, ALL_TRANSFORMS.WIDGET_TYPE_NAME, ALL_TRANSFORMS.WIDGET_NAME, ALL_TRANSFORMS.WIDGET_DESCRIPTION, ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.WIDGET_VERSION_STATUS, ALL_TRANSFORMS.WIDGET_LAST_SAVED, ALL_TRANSFORMS.IS_SHORTCUT , ALL_TRANSFORMS.PARENT_WIDGET_NAME, ALL_TRANSFORMS.PARENT_WIDGET_DESCRIPTION , ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_STATUS , PARENT_WIDGET_VERSION_STATUS, ALL_TRANSFORMS.PARENT_WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_SUBJECT_AREA, TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME) “query”
where case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’ and “SUBJECT_AREA” in (‘<Folder_Name’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays transformations that are not used in any mapping or mapplet by repository and folder.
select “WIDGET_LAST_SAVED”,
case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
“WIDGET_VERSION_NUMBER”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”,
“WIDGET_NAME”,
“WIDGET_DESCRIPTION”,
“MAPPING_ID”,
“WIDGET_TYPE_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME, ALL_TRANSFORMS.SUBJECT_AREA, ALL_TRANSFORMS.WIDGET_TYPE_NAME, ALL_TRANSFORMS.WIDGET_NAME, ALL_TRANSFORMS.WIDGET_DESCRIPTION AS WIDGET_DESCRIPTION, ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.WIDGET_VERSION_STATUS as WIDGET_VERSION_STATUS, ALL_TRANSFORMS.WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_WIDGET_NAME, ALL_TRANSFORMS.PARENT_WIDGET_DESCRIPTION AS PARENT_WIDGET_DESCRIPTION, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_STATUS as PARENT_WIDGET_VERSION_STATUS, ALL_TRANSFORMS.PARENT_WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_SUBJECT_AREA, ALL_TRANSFORMS.IS_SHORTCUT as IS_SHORTCUT , (COUNT(DISTINCT(TRANSFORM_WIDGET_INST.MAPPING_ID))) as MAPPING_ID FROM REP_REPOSIT_INFO TRANSFORM_REPOSIT_INFO, REP_ALL_TRANSFORMS ALL_TRANSFORMS LEFT OUTER JOIN REP_WIDGET_INST TRANSFORM_WIDGET_INST ON ALL_TRANSFORMS.PARENT_WIDGET_ID = TRANSFORM_WIDGET_INST.WIDGET_ID AND ALL_TRANSFORMS.SUBJECT_ID = TRANSFORM_WIDGET_INST.SUBJECT_ID AND ALL_TRANSFORMS.WIDGET_TYPE_ID = TRANSFORM_WIDGET_INST.WIDGET_TYPE AND (TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 1 AND TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 2 AND TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 44 ) AND ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE = 1 WHERE (ALL_TRANSFORMS.PARENT_WIDGET_ID <> TRANSFORM_REPOSIT_INFO.REPOSITORY_ID AND ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE = 1) GROUP BY TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME, ALL_TRANSFORMS.SUBJECT_AREA, ALL_TRANSFORMS.WIDGET_TYPE_NAME, ALL_TRANSFORMS.WIDGET_NAME, ALL_TRANSFORMS.WIDGET_DESCRIPTION, ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.WIDGET_VERSION_STATUS, ALL_TRANSFORMS.WIDGET_LAST_SAVED, ALL_TRANSFORMS.IS_SHORTCUT , ALL_TRANSFORMS.PARENT_WIDGET_NAME, ALL_TRANSFORMS.PARENT_WIDGET_DESCRIPTION , ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_STATUS , PARENT_WIDGET_VERSION_STATUS, ALL_TRANSFORMS.PARENT_WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_SUBJECT_AREA, TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME) “query”
where “MAPPING_ID” = 0 and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Workflows
Displays all the reusable schedulers defined in the repository and their description and properties by repository by folder.
select case when (“IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_REUSABLE_FLAG”,
“RUN_COUNT”,
“END_TIME”,
case when (“RUN_OPTIONS” <= 9) then (case when (“RUN_OPTIONS” = 1) then (‘Run On Demand’) else (case when (“RUN_OPTIONS” = 2) then (‘Run Once Schedule’) else (case when (“RUN_OPTIONS” = 3) then (‘Run On Demand and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 5) then (‘Run On Demand and Delta Schedule’) else (case when (“RUN_OPTIONS” = 8) then (‘Custom Repeat’) else (case when (“RUN_OPTIONS” = 9) then (‘Run On Demand and Custom Repeat’) else (”) end) end) end) end) end) end) else (case when (“RUN_OPTIONS” = 18) then (‘Run On Server Init and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 20) then (‘Run On Server Init and Delta Schedule’) else (case when (“RUN_OPTIONS” = 24) then (‘Run On Server Init and Custom Repeat’) else (case when (“RUN_OPTIONS” = 34) then (‘Run Continuously and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 36) then (‘Run Continuously and Delta Schedule’) else (case when (“RUN_OPTIONS” = 40) then (‘Run Continuously and Custom Repeat’) else (”) end) end) end) end) end) end) end as “Calc_RUN_OPTIONS”,
“VERSION_NUMBER”,
case when (“END_OPTIONS” = 0) then (‘End On Date’) else (case when (“END_OPTIONS” = 1) then (‘End After Count’) else (case when (“END_OPTIONS” = 2) then (‘Run Forever’) else (”) end) end) end as “Calc_END_OPTIONS”,
“COUNT_WORKFLOW_ID”,
“SCHEDULER_NAME”,
“DESCRIPTION”,
“START_TIME”,
“DELTA_VALUE”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”,
“LAST_SAVED”
from (SELECT WORKFLOW_SCHEDULER_REPOSITORY.REPOSITORY_NAME AS REPOSITORY_NAME, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA AS SUBJECT_AREA, WORKFLOW_SCHEDULERS.SCHEDULER_NAME AS SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION AS DESCRIPTION, WORKFLOW_SCHEDULERS.VERSION_NUMBER AS VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED AS LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME AS START_TIME, WORKFLOW_SCHEDULERS.END_TIME AS END_TIME, WORKFLOW_SCHEDULERS.RUN_OPTIONS AS RUN_OPTIONS, WORKFLOW_SCHEDULERS.END_OPTIONS AS END_OPTIONS, WORKFLOW_SCHEDULERS.RUN_COUNT AS RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE AS DELTA_VALUE, WORKFLOW_SCHEDULERS.IS_REUSABLE AS IS_REUSABLE, (COUNT(WORKFLOW_SCHEDULER_ASSOCIATION.WORKFLOW_ID)) AS COUNT_WORKFLOW_ID FROM REP_REPOSIT_INFO WORKFLOW_SCHEDULER_REPOSITORY, REP_SUBJECT WORKFLOW_SCHEDULER_FOLDER, REP_ALL_SCHEDULERS WORKFLOW_SCHEDULERS LEFT OUTER JOIN REP_WORKFLOWS WORKFLOW_SCHEDULER_ASSOCIATION ON WORKFLOW_SCHEDULERS.SCHEDULER_ID = WORKFLOW_SCHEDULER_ASSOCIATION. SCHEDULER_ID AND WORKFLOW_SCHEDULERS.VERSION_NUMBER = WORKFLOW_SCHEDULER_ASSOCIATION. SCHEDULER_VERSION_NUMBER WHERE (WORKFLOW_SCHEDULERS.SCHEDULER_ID <> WORKFLOW_SCHEDULER_REPOSITORY. REPOSITORY_ID AND WORKFLOW_SCHEDULERS.SUBJECT_ID = WORKFLOW_SCHEDULER_FOLDER.SUBJECT_ID) AND ( WORKFLOW_SCHEDULERS.IS_REUSABLE = 1 ) GROUP BY WORKFLOW_SCHEDULER_REPOSITORY.REPOSITORY_NAME, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA, WORKFLOW_SCHEDULERS.SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION, WORKFLOW_SCHEDULERS.VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME, WORKFLOW_SCHEDULERS.END_TIME, WORKFLOW_SCHEDULERS.RUN_OPTIONS, WORKFLOW_SCHEDULERS.END_OPTIONS, WORKFLOW_SCHEDULERS.RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE, WORKFLOW_SCHEDULERS.IS_REUSABLE) “query”
where case when (“IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’ and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all workflows using a given reusable scheduler by repository by folder.
select “SCHEDULER_NAME”,
“SUBJECT_AREA”,
“WORKFLOW_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_SCHEDULERS.SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION AS WSD_DESC, WORKFLOW_SCHEDULERS.VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME, WORKFLOW_SCHEDULERS.END_TIME, WORKFLOW_SCHEDULERS.IS_REUSABLE as REUSABLEFLAG, WORKFLOW_SCHEDULERS.RUN_OPTIONS , WORKFLOW_SCHEDULERS.END_OPTIONS , WORKFLOW_SCHEDULERS.RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE, ALL_WORKFLOWS.SERVER_NAME, ALL_WORKFLOWS.SCHEDULER_NAME WORKFLOWLIST_SCHEDULER, ALL_WORKFLOWS.WORKFLOW_COMMENTS AS WORKFLOW_COMMENTS, ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER, ALL_WORKFLOWS.WORKFLOW_IS_VALID as VALIDFLAG, WORKFLOW_ALL_TASKS.IS_ENABLED as ENABLEDFLAG, ALL_WORKFLOWS.WORKFLOW_LAST_SAVED, WORKFLOW_ALL_TASKS.TASK_TYPE, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA AS WSD_SUBJECT_AREA FROM REP_WORKFLOWS ALL_WORKFLOWS, REP_ALL_SCHEDULERS WORKFLOW_SCHEDULERS, REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_ALL_TASKS WORKFLOW_ALL_TASKS , REP_SUBJECT WORKFLOW_SCHEDULER_FOLDER WHERE (ALL_WORKFLOWS.SCHEDULER_ID = WORKFLOW_SCHEDULERS.SCHEDULER_ID AND ALL_WORKFLOWS.SCHEDULER_VERSION_NUMBER = WORKFLOW_SCHEDULERS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_SCHEDULERS.SUBJECT_ID AND ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_ALL_TASKS.TASK_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_ALL_TASKS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_ALL_TASKS.SUBJECT_ID) AND (WORKFLOW_SCHEDULERS.SCHEDULER_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND WORKFLOW_SCHEDULERS.SUBJECT_ID = WORKFLOW_SCHEDULER_FOLDER.SUBJECT_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“REUSABLEFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays workflow events and its properties by repository by folder.
select case when (“EVENT_TYPE” = 0) then (‘Built In’) else (case when (“EVENT_TYPE” = 1) then (‘User Defined’) else (‘NULL’) end) end as “Calc_EVENT_TYPE”,
“LAST_SAVED”,
“REPOSITORY_NAME”,
“EVENT_DESCRIPTION”,
“SUBJECT_AREA”,
“WORKFLOW_NAME”,
“EVENT_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_EVENT.EVENT_TYPE , WORKFLOW_EVENT.EVENT_NAME, WORKFLOW_EVENT.EVENT_DESCRIPTION AS EVENT_DESCRIPTION, WORKFLOW_EVENT.LAST_SAVED FROM REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_WORKFLOWS ALL_WORKFLOWS, REP_EVENT WORKFLOW_EVENT WHERE (ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_EVENT.WORKFLOW_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_EVENT.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_EVENT.SUBJECT_ID )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays workflows and workflow properties by repository by folder. This is a primary report in an analytic workflow.
select “WORKFLOW_COMMENTS”,
“SUBJECT_AREA”,
“SERVER_NAME”,
“WORKFLOW_NAME”,
“WORKFLOW_LAST_SAVED”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_WORKFLOW_IS_VALID”,
“WORKFLOWLIST_SCHEDULER”,
“REPOSITORY_NAME”,
case when (“ENABLEDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_ENABLED”,
“WORKFLOW_VERSION_NUMBER”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_SCHEDULERS.SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION AS WSD_DESC, WORKFLOW_SCHEDULERS.VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME, WORKFLOW_SCHEDULERS.END_TIME, WORKFLOW_SCHEDULERS.IS_REUSABLE as REUSABLEFLAG, WORKFLOW_SCHEDULERS.RUN_OPTIONS , WORKFLOW_SCHEDULERS.END_OPTIONS , WORKFLOW_SCHEDULERS.RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE, ALL_WORKFLOWS.SERVER_NAME, ALL_WORKFLOWS.SCHEDULER_NAME WORKFLOWLIST_SCHEDULER, ALL_WORKFLOWS.WORKFLOW_COMMENTS AS WORKFLOW_COMMENTS, ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER, ALL_WORKFLOWS.WORKFLOW_IS_VALID as VALIDFLAG, WORKFLOW_ALL_TASKS.IS_ENABLED as ENABLEDFLAG, ALL_WORKFLOWS.WORKFLOW_LAST_SAVED, WORKFLOW_ALL_TASKS.TASK_TYPE, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA AS WSD_SUBJECT_AREA FROM REP_WORKFLOWS ALL_WORKFLOWS, REP_ALL_SCHEDULERS WORKFLOW_SCHEDULERS, REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_ALL_TASKS WORKFLOW_ALL_TASKS , REP_SUBJECT WORKFLOW_SCHEDULER_FOLDER WHERE (ALL_WORKFLOWS.SCHEDULER_ID = WORKFLOW_SCHEDULERS.SCHEDULER_ID AND ALL_WORKFLOWS.SCHEDULER_VERSION_NUMBER = WORKFLOW_SCHEDULERS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_SCHEDULERS.SUBJECT_ID AND ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_ALL_TASKS.TASK_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_ALL_TASKS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_ALL_TASKS.SUBJECT_ID) AND (WORKFLOW_SCHEDULERS.SCHEDULER_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND WORKFLOW_SCHEDULERS.SUBJECT_ID = WORKFLOW_SCHEDULER_FOLDER.SUBJECT_ID)) “query”
where “TASK_TYPE” = 71 and “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays, by repository by folder, scheduler settings for a workflow and properties of the scheduler, such as reusable flag and run on demand .This report is the first node in the analytic workflow associated with the Workflow List primary report.
select “RUN_COUNT”,
“SCHEDULER_NAME”,
case when (“END_OPTIONS” = 0) then (‘End On Date’) else (case when (“END_OPTIONS” = 1) then (‘End After Count’) else (case when (“END_OPTIONS” = 2) then (‘Run Forever’) else (”) end) end) end as “Calc_END_OPTIONS”,
“WSD_SUBJECT_AREA”,
case when (“REUSABLEFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_REUSABLE”,
case when (“RUN_OPTIONS” <= 9) then (case when (“RUN_OPTIONS” = 1) then (‘Run On Demand’) else (case when (“RUN_OPTIONS” = 2) then (‘Run Once Schedule’) else (case when (“RUN_OPTIONS” = 3) then (‘Run On Demand and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 5) then (‘Run On Demand and Delta Schedule’) else (case when (“RUN_OPTIONS” = 8) then (‘Custom Repeat’) else (case when (“RUN_OPTIONS” = 9) then (‘Run On Demand and Custom Repeat’) else (null) end) end) end) end) end) end) else (case when (“RUN_OPTIONS” = 18) then (‘Run On Server Init and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 20) then (‘Run On Server Init and Delta Schedule’) else (case when (“RUN_OPTIONS” = 24) then (‘Run On Server Init and Custom Repeat’) else (case when (“RUN_OPTIONS” = 34) then (‘Run Continuously and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 36) then (‘Run Continuously and Delta Schedule’) else (case when (“RUN_OPTIONS” = 40) then (‘Run Continuously and Custom Repeat’) else (null) end) end) end) end) end) end) end as “Calc_Run_Options”,
“WSD_DESC”,
“LAST_SAVED”,
“START_TIME”,
“WORKFLOW_NAME”,
“END_TIME”,
“VERSION_NUMBER”,
“DELTA_VALUE”,
“REPOSITORY_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_SCHEDULERS.SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION AS WSD_DESC, WORKFLOW_SCHEDULERS.VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME, WORKFLOW_SCHEDULERS.END_TIME, WORKFLOW_SCHEDULERS.IS_REUSABLE as REUSABLEFLAG, WORKFLOW_SCHEDULERS.RUN_OPTIONS , WORKFLOW_SCHEDULERS.END_OPTIONS , WORKFLOW_SCHEDULERS.RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE, ALL_WORKFLOWS.SERVER_NAME, ALL_WORKFLOWS.SCHEDULER_NAME WORKFLOWLIST_SCHEDULER, ALL_WORKFLOWS.WORKFLOW_COMMENTS AS WORKFLOW_COMMENTS, ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER, ALL_WORKFLOWS.WORKFLOW_IS_VALID as VALIDFLAG, WORKFLOW_ALL_TASKS.IS_ENABLED as ENABLEDFLAG, ALL_WORKFLOWS.WORKFLOW_LAST_SAVED, WORKFLOW_ALL_TASKS.TASK_TYPE, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA AS WSD_SUBJECT_AREA FROM REP_WORKFLOWS ALL_WORKFLOWS, REP_ALL_SCHEDULERS WORKFLOW_SCHEDULERS, REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_ALL_TASKS WORKFLOW_ALL_TASKS , REP_SUBJECT WORKFLOW_SCHEDULER_FOLDER WHERE (ALL_WORKFLOWS.SCHEDULER_ID = WORKFLOW_SCHEDULERS.SCHEDULER_ID AND ALL_WORKFLOWS.SCHEDULER_VERSION_NUMBER = WORKFLOW_SCHEDULERS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_SCHEDULERS.SUBJECT_ID AND ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_ALL_TASKS.TASK_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_ALL_TASKS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_ALL_TASKS.SUBJECT_ID) AND (WORKFLOW_SCHEDULERS.SCHEDULER_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND WORKFLOW_SCHEDULERS.SUBJECT_ID = WORKFLOW_SCHEDULER_FOLDER.SUBJECT_ID)) “query”
where “WSD_SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name’)
order by “REPOSITORY_NAME”, “WSD_SUBJECT_AREA”
Displays all tasks created in a workflow by repository by folder. This report is the first node in the analytic workflow associated with the Workflow List primary report.
select “SUBJECT_AREA”,
case when (“IS_ENABLED” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_ENABLED”,
“WORKFLOW_NAME”,
“DESCRIPTION”,
“NSTANCE_NAME”,
“TASK_TYPE_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA as SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME as WORKFLOW_NAME, WORKFLOW_TASK_INST.TASK_TYPE_NAME as TASK_TYPE_NAME, WORKFLOW_TASK_INST.INSTANCE_NAME as NSTANCE_NAME, WORKFLOW_TASK_INST.DESCRIPTION as DESCRIPTION, WORKFLOW_TASK_INST.IS_ENABLED as IS_ENABLED FROM REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_WORKFLOWS ALL_WORKFLOWS, REP_TASK_INST WORKFLOW_TASK_INST WHERE (ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_TASK_INST.WORKFLOW_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_TASK_INST.VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays workflow variables and their properties by repository by folder. This report is the first node in the analytic workflow associated with the Workflow List primary report.
select “SUBJECT_AREA”,
“VARIABLE_DEFAULT_VALUE”,
case when (“VARIABLE_TYPE” = 0) then (‘Built In’) else (case when (“VARIABLE_TYPE” = 1) then (‘User Defined’) else (”) end) end as “Calc_VARIABLE_TYPE”,
“WORKFLOW_NAME”,
“LAST_SAVED”,
“DESCRIPTION”,
case when (“VARIABLE_DATATYPE” <= 8) then (case when (“VARIABLE_DATATYPE” = -1) then (‘Text’) else (case when (“VARIABLE_DATATYPE” = -2) then (‘Binary’) else (case when (“VARIABLE_DATATYPE” = -9) then (‘nString’) else (case when (“VARIABLE_DATATYPE” = -10) then (‘nText’) else (case when (“VARIABLE_DATATYPE” = 3) then (‘Decimal’) else (case when (“VARIABLE_DATATYPE” = 4) then (‘Integer’) else (case when (“VARIABLE_DATATYPE” = 5) then (‘Small Integer’) else (case when (“VARIABLE_DATATYPE” = 7) then (‘Real’) else (case when (“VARIABLE_DATATYPE” = 8) then (‘Double’) else (”) end) end) end) end) end) end) end) end) end) else (case when (“VARIABLE_DATATYPE” = 11) then (‘Date/Time’) else (case when (“VARIABLE_DATATYPE” = 12) then (‘String’) else (”) end) end) end as “Calc_VARIABLE_DATATYPE”,
“REPOSITORY_NAME”,
“VARIABLE_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA AS SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME AS WORKFLOW_NAME, WORKFLOW_VAR.VARIABLE_TYPE AS VARIABLE_TYPE, WORKFLOW_VAR.VARIABLE_NAME AS VARIABLE_NAME, WORKFLOW_VAR.VARIABLE_DESCRIPTION AS DESCRIPTION, WORKFLOW_VAR.VARIABLE_DATATYPE as VARIABLE_DATATYPE, WORKFLOW_VAR.VARIABLE_DEFAULT_VALUE AS VARIABLE_DEFAULT_VALUE, WORKFLOW_VAR.LAST_SAVED AS LAST_SAVED FROM REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_WORKFLOWS ALL_WORKFLOWS, REP_WFLOW_VAR WORKFLOW_VAR WHERE (ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_VAR.WORKFLOW_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_VAR.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_VAR.SUBJECT_ID )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Worklets
Displays all invalid worklets by repository by folder.
select case when (“IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_VALID”,
“DESCRIPTION”,
“TASK_NAME”,
“SUBJECT_AREA”,
“VERSION_NUMBER”,
“REPOSITORY_NAME”,
“LAST_SAVED”,
case when (“IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_REUSABLE”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA AS SUBJECT_AREA, ALL_WORKLETS.TASK_NAME AS TASK_NAME, ALL_WORKLETS.DESCRIPTION AS DESCRIPTION, ALL_WORKLETS.VERSION_NUMBER AS VERSION_NUMBER, ALL_WORKLETS.IS_VALID AS IS_VALID, ALL_WORKLETS.IS_REUSABLE AS IS_REUSABLE , ALL_WORKLETS.LAST_SAVED AS LAST_SAVED FROM REP_ALL_TASKS ALL_WORKLETS, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO WHERE (ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where case when (“IS_VALID” = 1) then (‘Yes’) else (‘No’) end = ‘No’ and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays worklet events and its properties by repository by folder. This report is the first node in the analytic workflow associated with the Worklet List primary report.
select “SUBJECT_AREA”,
“LAST_SAVED”,
“TASK_NAME”,
case when (“EVENT_TYPE” = 0) then (‘Built In’) else (case when (“EVENT_TYPE” = 1) then (‘User Defined’) else (”) end) end as “Calc_EVENT_TYPE”,
“REPOSITORY_NAME”,
“EVENT_NAME”,
“EVENT_DESCRIPTION”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_EVENT.EVENT_TYPE as EVENT_TYPE, WORKLET_EVENT.EVENT_NAME, WORKLET_EVENT.EVENT_DESCRIPTION as EVENT_DESCRIPTION, WORKLET_EVENT.LAST_SAVED FROM REP_ALL_TASKS ALL_WORKLETS, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO, REP_EVENT WORKLET_EVENT WHERE (ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70 AND ALL_WORKLETS.TASK_ID = WORKLET_EVENT.WORKFLOW_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_EVENT.VERSION_NUMBER AND ALL_WORKLETS.SUBJECT_ID = WORKLET_EVENT.SUBJECT_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays worklets and worklet properties by repository by folder. This is a primary report in an analytic workflow.
select case when (“IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_VALID”,
“DESCRIPTION”,
“TASK_NAME”,
“SUBJECT_AREA”,
“VERSION_NUMBER”,
“REPOSITORY_NAME”,
“LAST_SAVED”,
case when (“IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_REUSABLE”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA AS SUBJECT_AREA, ALL_WORKLETS.TASK_NAME AS TASK_NAME, ALL_WORKLETS.DESCRIPTION AS DESCRIPTION, ALL_WORKLETS.VERSION_NUMBER AS VERSION_NUMBER, ALL_WORKLETS.IS_VALID AS IS_VALID, ALL_WORKLETS.IS_REUSABLE AS IS_REUSABLE , ALL_WORKLETS.LAST_SAVED AS LAST_SAVED FROM REP_ALL_TASKS ALL_WORKLETS, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO WHERE (ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays how tasks are connected to each other in a worklet by repository by folder. This report is the first node in the analytic workflow associated with the Worklet List primary report.
select “TASK_NAME”,
“TASK_TYPE_NAME”,
“TO_TASK_TYPE_NAME”,
“TO_INSTANCE_NAME”,
“DESCRIPTION”,
“SUBJECT_AREA”,
“INSTANCE_NAME”,
“REPOSITORY_NAME”,
“CONDITION”,
“TO_DESCRIPTION”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_FROM_TASK_INST.INSTANCE_NAME , WORKLET_FROM_TASK_INST.TASK_TYPE_NAME, WORKLET_FROM_TASK_INST.DESCRIPTION as DESCRIPTION, WORKLET_TO_TASK_INST.INSTANCE_NAME as TO_INSTANCE_NAME, WORKLET_TO_TASK_INST.TASK_TYPE_NAME as TO_TASK_TYPE_NAME, WORKLET_TO_TASK_INST.DESCRIPTION as TO_DESCRIPTION, WORKLET_DEP.CONDITION as CONDITION FROM REP_ALL_TASKS ALL_WORKLETS, REP_WORKFLOW_DEP WORKLET_DEP, REP_TASK_INST WORKLET_TO_TASK_INST, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO, REP_TASK_INST WORKLET_FROM_TASK_INST WHERE (ALL_WORKLETS.TASK_ID = WORKLET_DEP.WORKFLOW_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_DEP.VERSION_NUMBER AND ALL_WORKLETS.TASK_TYPE = 70 AND WORKLET_DEP.WORKFLOW_ID = WORKLET_TO_TASK_INST.WORKFLOW_ID AND WORKLET_DEP.VERSION_NUMBER = WORKLET_TO_TASK_INST.VERSION_NUMBER AND WORKLET_DEP.TO_INSTANCE_ID = WORKLET_TO_TASK_INST.INSTANCE_ID AND ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70 AND WORKLET_DEP.WORKFLOW_ID = WORKLET_FROM_TASK_INST.WORKFLOW_ID AND WORKLET_DEP.VERSION_NUMBER = WORKLET_FROM_TASK_INST.VERSION_NUMBER AND WORKLET_DEP.FROM_INSTANCE_ID = WORKLET_FROM_TASK_INST.INSTANCE_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all the tasks created in a worklet by repository by folder. This report is the first node in the analytic workflow associated with the Worklet List primary report.
select “REPOSITORY_NAME”,
“SUBJECT_AREA”,
“INSTANCE_NAME”,
“TASK_NAME”,
case when (“IS_ENABLED” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_ENABLED”,
“TASK_TYPE_NAME”,
“DESCRIPTION”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_TASK_INST.TASK_TYPE_NAME, WORKLET_TASK_INST.INSTANCE_NAME, WORKLET_TASK_INST.DESCRIPTION as DESCRIPTION, WORKLET_TASK_INST.IS_ENABLED FROM REP_ALL_TASKS ALL_WORKLETS, REP_TASK_INST WORKLET_TASK_INST, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO WHERE (ALL_WORKLETS.TASK_ID = WORKLET_TASK_INST.WORKFLOW_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_TASK_INST.VERSION_NUMBER AND ALL_WORKLETS.TASK_TYPE = 70 AND ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays variables defined for a worklet and its properties by repository by folder. This report is the first node in the analytic workflow associated with the Worklet List primary report.
select “VARIABLE_DEFAULT_VALUE”,
case when (“VARIABLE_DATATYPE” <= 8) then (case when (“VARIABLE_DATATYPE” = -1) then (‘Text’) else (case when (“VARIABLE_DATATYPE” = -2) then (‘Binary’) else (case when (“VARIABLE_DATATYPE” = -9) then (‘nString’) else (case when (“VARIABLE_DATATYPE” = -10) then (‘nText’) else (case when (“VARIABLE_DATATYPE” = 3) then (‘Decimal’) else (case when (“VARIABLE_DATATYPE” = 4) then (‘Integer’) else (case when (“VARIABLE_DATATYPE” = 5) then (‘Small Integer’) else (case when (“VARIABLE_DATATYPE” = 7) then (‘Real’) else (case when (“VARIABLE_DATATYPE” = 8) then (‘Double’) else (null) end) end) end) end) end) end) end) end) end) else (case when (“VARIABLE_DATATYPE” = 11) then (‘Date/Time’) else (case when (“VARIABLE_DATATYPE” = 12) then (‘String’) else (null) end) end) end as “Calc_VARIABLE_DATATYPE”,
“LAST_SAVED”,
“REPOSITORY_NAME”,
“VARIABLE_NAME”,
case when (“VARIABLE_TYPE” = 0) then (‘Built In’) else (case when (“VARIABLE_TYPE” = 1) then (‘User Defined’) else (‘NULL’) end) end as “Calc_VARIABLE_TYPE”,
“VARIABLE_DESCRIPTION”,
“TASK_NAME”,
“SUBJECT_AREA”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_VAR.VARIABLE_TYPE , WORKLET_VAR.VARIABLE_NAME, WORKLET_VAR.VARIABLE_DESCRIPTION as VARIABLE_DESCRIPTION, WORKLET_VAR.VARIABLE_DATATYPE , WORKLET_VAR.VARIABLE_DEFAULT_VALUE as VARIABLE_DEFAULT_VALUE, WORKLET_VAR.LAST_SAVED FROM REP_ALL_TASKS ALL_WORKLETS, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO, REP_WFLOW_VAR WORKLET_VAR WHERE (ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70 AND ALL_WORKLETS.TASK_ID = WORKLET_VAR.WORKFLOW_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_VAR.VERSION_NUMBER AND ALL_WORKLETS.SUBJECT_ID = WORKLET_VAR.SUBJECT_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”