SELECT
   KK0191.TELNO             AS TELNO
  ,KK0081.SVC_KEI_NO        AS SVC_KEI_NO
  ,KK0161.SVC_KEI_UCWK_NO   AS SVC_KEI_UCWK_NO
  ,KK0081.SYSID             AS SYSID
  ,NULL                     AS PACK_STI
  ,NULL                     AS KEI_NO_B27
  ,NULL                     AS KEI_NO_B28
  ,NULL                     AS KEI_NO_D07
  ,NULL                     AS KEI_NO_B29
  ,NULL                     AS KEI_NO_D08
  ,NULL                     AS KEI_NO_B30
  ,NULL                     AS KEI_NO_B31
  ,NULL                     AS PACK_STI_KIDO
  ,NULL                     AS KEI_NO_B27_KIDO
  ,NULL                     AS KEI_NO_B28_KIDO
  ,NULL                     AS KEI_NO_D07_KIDO
  ,NULL                     AS KEI_NO_B29_KIDO
  ,NULL                     AS KEI_NO_D08_KIDO
  ,NULL                     AS KEI_NO_B30_KIDO
  ,NULL                     AS KEI_NO_B31_KIDO
  ,NULL                     AS MESSAGE
FROM
(
  SELECT
    UCWK_SUB.SVC_KEI_UCWK_NO
  FROM
    (
      SELECT
         KK0351.SVC_KEI_UCWK_NO
         ,COUNT(KK0351.SVC_KEI_UCWK_NO) OP_CNT
      FROM
         KK_T_OP_SVC_KEI KK0351
      WHERE
          KK0351.OP_SVC_KEI_STAT < '910'
      AND KK0351.OP_SVC_CD IN ('B023', 'B024', 'B025', 'B026', 'B027')
      AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) =
            (SELECT KK0351_GENE.OP_SVC_KEI_NO, MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_MAX 
             FROM   KK_T_OP_SVC_KEI KK0351_GENE
             WHERE 
                    KK0351_GENE.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO
               AND  KK0351_GENE.RSV_APLY_YMD <= :BASE_YMD
               AND  KK0351_GENE.RSV_APLY_CD = '2'
               AND  KK0351_GENE.MK_FLG = '0'
             GROUP BY KK0351_GENE.OP_SVC_KEI_NO)
      GROUP BY SVC_KEI_UCWK_NO
    ) UCWK_SUB
  WHERE
    UCWK_SUB.OP_CNT >= 2
) TARGET_UCWK
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
 ON KK0161.SVC_KEI_UCWK_NO = TARGET_UCWK.SVC_KEI_UCWK_NO
INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191
 ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO
     AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM
INNER JOIN KK_T_SVC_KEI KK0081
  ON KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO
WHERE
   KK0161.SVC_KEI_UCWK_STAT < '910'
   AND (KK0161.SVC_STA_YMD IS NULL OR KK0161.SVC_STA_YMD > :BASE_YMD)
   AND KK0081.SVC_KEI_STAT < '910'
   AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) =
         (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX 
          FROM   KK_T_SVC_KEI_UCWK KK0161_GENE
          WHERE 
                 KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
             AND KK0161_GENE.RSV_APLY_YMD <= :BASE_YMD
             AND KK0161_GENE.RSV_APLY_CD = '2'
             AND KK0161_GENE.MK_FLG = '0'
          GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO)
   AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) =
         (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX 
          FROM   KK_T_SVC_KEI KK0081_GENE
          WHERE 
                 KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO
            AND  KK0081_GENE.RSV_APLY_YMD <= :BASE_YMD
            AND  KK0081_GENE.RSV_APLY_CD = '2'
            AND  KK0081_GENE.MK_FLG = '0'
          GROUP BY KK0081_GENE.SVC_KEI_NO)