Informatica Queries for Deployment Groups

Informatica Queries for Deployment Groups

1. Deployment Details

Displays, by group name, type, and creator, the details of the deployed objects by a particular deployment group. This report is the first node in the analytic workflow associated with the Deployment Group History primary report.


 SELECT DEP_GROUP_NAME,
         REPOSITORY_NAME,
         CREATED_BY,
         CASE WHEN (IS_SHORTCUT = 1) THEN ('Yes') ELSE ('No') END
             AS Calc_IS_SHORTCUT,
         OBJECT_TYPE_NAME,
         CASE
             WHEN (GROUP_TYPE = 0) THEN ('Normal')
             ELSE (CASE WHEN (GROUP_TYPE = 1) THEN ('Dynamic') ELSE ('') END)
         END
             AS Calc_GROUP_TYPE,
         TARG_SUBJECT_AREA,
         OBJECT_NAME,
         SRC_VERSION_NUMBER,
         TARGET_REP_NAME,
         SRC_SUBJECT_AREA,
         DEPLOY_TIME,
         TARG_VERSION_NUMBER
    FROM (SELECT DISTINCT DEPLOYMENT_GROUP.DEP_GROUP_NAME,
                          DEPLOYMENT_GROUP.GROUP_TYPE       AS GROUP_TYPE,
                          DEPLOYMENT_GROUP.CREATED_BY,
                          DEPLOYMENT_GROUP_DETAIL.DEPLOY_TIME,
                          DEPLOYMENT_REPOSIT_INFO.REPOSITORY_NAME,
                          DEPLOYMENT_GROUP_DETAIL.TARGET_REP_NAME,
                          DEPLOYMENT_GROUP_DETAIL.OBJECT_NAME,
                          DEPLOYMENT_GROUP_DETAIL.OBJECT_TYPE_NAME,
                          DEPLOYMENT_GROUP_DETAIL.SRC_VERSION_NUMBER,
                          DEPLOYMENT_GROUP_DETAIL.TARG_VERSION_NUMBER,
                          DEPLOYMENT_GROUP_DETAIL.SRC_SUBJECT_AREA,
                          DEPLOYMENT_GROUP_DETAIL.TARG_SUBJECT_AREA,
                          DEPLOYMENT_GROUP_DETAIL.IS_SHORTCUT AS IS_SHORTCUT,
                          DEPLOYMENT_GROUP.DESCRIPTION      AS DESCRIPTION,
                          DEPLOYMENT_GROUP_DETAIL.USER_NAME
            FROM REP_REPOSIT_INFO DEPLOYMENT_REPOSIT_INFO,
                 REP_DEPLOY_GROUP DEPLOYMENT_GROUP
                 LEFT OUTER JOIN
                 REP_DEPLOY_GROUP_DETAIL DEPLOYMENT_GROUP_DETAIL
                     ON DEPLOYMENT_GROUP.DEP_GROUP_ID =
                            DEPLOYMENT_GROUP_DETAIL.DEP_GROUP_ID
           WHERE (DEPLOYMENT_GROUP.DEP_GROUP_ID <>
                      DEPLOYMENT_REPOSIT_INFO.REPOSITORY_ID)) query

ORDER BY DEP_GROUP_NAME, Calc_GROUP_TYPE, CREATED_BY

2. Deployment Group History

Displays, by group, deployment groups and the dates they were deployed. It also displays the source and target repository names of the deployment group for all deployment dates. This is a primary report in an analytic workflow. 

    

  SELECT DEP_GROUP_NAME,
         DESCRIPTION,
         REPOSITORY_NAME,
         CREATED_BY,
         CASE
             WHEN (GROUP_TYPE = 0) THEN ('Normal')
             ELSE (CASE WHEN (GROUP_TYPE = 1) THEN ('Dynamic') ELSE ('') END)
         END
             AS Calc_GROUP_TYPE,
         TARGET_REP_NAME,
         USER_NAME,
         DEPLOY_TIME
    FROM (SELECT DISTINCT DEPLOYMENT_GROUP.DEP_GROUP_NAME,
                          DEPLOYMENT_GROUP.GROUP_TYPE       AS GROUP_TYPE,
                          DEPLOYMENT_GROUP.CREATED_BY,
                          DEPLOYMENT_GROUP_DETAIL.DEPLOY_TIME,
                          DEPLOYMENT_REPOSIT_INFO.REPOSITORY_NAME,
                          DEPLOYMENT_GROUP_DETAIL.TARGET_REP_NAME,
                          DEPLOYMENT_GROUP_DETAIL.OBJECT_NAME,
                          DEPLOYMENT_GROUP_DETAIL.OBJECT_TYPE_NAME,
                          DEPLOYMENT_GROUP_DETAIL.SRC_VERSION_NUMBER,
                          DEPLOYMENT_GROUP_DETAIL.TARG_VERSION_NUMBER,
                          DEPLOYMENT_GROUP_DETAIL.SRC_SUBJECT_AREA,
                          DEPLOYMENT_GROUP_DETAIL.TARG_SUBJECT_AREA,
                          DEPLOYMENT_GROUP_DETAIL.IS_SHORTCUT AS IS_SHORTCUT,
                          DEPLOYMENT_GROUP.DESCRIPTION      AS DESCRIPTION,
                          DEPLOYMENT_GROUP_DETAIL.USER_NAME
            FROM REP_REPOSIT_INFO DEPLOYMENT_REPOSIT_INFO,
                 REP_DEPLOY_GROUP DEPLOYMENT_GROUP
                 LEFT OUTER JOIN
                 REP_DEPLOY_GROUP_DETAIL DEPLOYMENT_GROUP_DETAIL
                     ON DEPLOYMENT_GROUP.DEP_GROUP_ID =
                            DEPLOYMENT_GROUP_DETAIL.DEP_GROUP_ID
           WHERE (DEPLOYMENT_GROUP.DEP_GROUP_ID <>
                      DEPLOYMENT_REPOSIT_INFO.REPOSITORY_ID)) query

ORDER BY DEP_GROUP_NAME