SELECT
    KK0081.SVC_KEI_NO
   ,KK0081.SVC_ENDYMD
   ,KK0081.SVC_CANCEL_YMD
   ,KK0081.SVC_KEI_STAT
   ,KK0081.SVC_DLRE_CD
   ,KK0081.SVC_CD
   ,KK0081.PRC_GRP_CD
   ,KK0081.PCRS_CD
   ,KK0081.PPLAN_CD
   ,KK0081.SYSID
   ,KK0081.MENKAIHAT_ANKEN_NO
   ,KK0081.TK_HOSHIKI_KEI_NO
   ,KK0081.UPD_DTM
   ,KK0251.SVC_KEI_KAISEN_UCWK_NO
   ,TO_CHAR(KK0251.KAISEN_PLACE_NO)
   ,KK0251.KAISEN_PLACE_AD_CD
   ,KK0251.KAISEN_PLACE_PCD
   ,KK0251.KAISEN_PLACE_STATE_NM
   ,KK0251.KAISEN_PLACE_CITY_NM
   ,KK0251.KAISEN_PLACE_OAZTSU_NM
   ,KK0251.KAISEN_PLACE_AZCHO_NM
   ,KK0251.KAISEN_PLACE_BNCHIGO
   ,KK0251.KAISEN_PLACE_ADRTTM
   ,KK0251.KAISEN_PLACE_ADRRM
   ,KK0251.KISN_PLC_AD_MI_FIX_FLG
   ,TO_CHAR(KK0251.LGTD)
   ,TO_CHAR(KK0251.LTTD)
   ,KK0251.AD_FORM_CD
   ,KK0251.SHUGJ_NYUKYO_FLR_CNT_CD
   ,KK0251.KODATE_KCKU_FLR_CNT_CD
   ,KK0251.DIRECTION_CD_1
   ,KK0251.DIRECTION_CD_2
FROM KK_T_SVC_KEI KK0081
  INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241
  ON  KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO
  INNER JOIN KK_T_SVKEI_KAISEN_UW KK0251
  ON  KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
WHERE KK0081.SVC_KEI_STAT = '910'
AND   KK0081.KAIHK_FAIL_KEI_CST_ZM_FLG = '0'
AND   KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (
  SELECT 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 <= ZZZZZ
  AND    KK0081_GENE.RSV_APLY_CD = '2'
  AND    KK0081_GENE.MK_FLG = '0')
AND (KK0241.SVC_KEI_KAISEN_UCWK_NO, KK0241.KAISEN_UCWK_USE_STAYMD) = (
  SELECT KK0241_P.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0241_P.KAISEN_UCWK_USE_STAYMD)
  FROM   KK_T_KAISEN_TG_SVKEI KK0241_P
  WHERE  KK0241_P.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
  AND    KK0241_P.SVC_KEI_NO = KK0241.SVC_KEI_NO
  AND    KK0241.KAISEN_UCWK_USE_ENDYMD = KK0081.SVC_DSL_YMD
  AND    KK0241_P.MK_FLG = '0'
  GROUP BY KK0241_P.SVC_KEI_KAISEN_UCWK_NO, KK0241_P.KAISEN_UCWK_USE_STAYMD)
AND KK0251.GENE_ADD_DTM = (
  SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX  
  FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
  WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
  AND    KK0251_GENE.MK_FLG = '0')
UNION ALL
SELECT
    KK0081.SVC_KEI_NO
   ,KK0081.SVC_ENDYMD
   ,KK0081.SVC_CANCEL_YMD
   ,KK0081.SVC_KEI_STAT
   ,KK0081.SVC_DLRE_CD
   ,KK0081.SVC_CD
   ,KK0081.PRC_GRP_CD
   ,KK0081.PCRS_CD
   ,KK0081.PPLAN_CD
   ,KK0081.SYSID
   ,KK0081.MENKAIHAT_ANKEN_NO
   ,KK0081.TK_HOSHIKI_KEI_NO
   ,KK0081.UPD_DTM
   ,KK0251.SVC_KEI_KAISEN_UCWK_NO
   ,TO_CHAR(KK0251.KAISEN_PLACE_NO)
   ,KK0251.KAISEN_PLACE_AD_CD
   ,KK0251.KAISEN_PLACE_PCD
   ,KK0251.KAISEN_PLACE_STATE_NM
   ,KK0251.KAISEN_PLACE_CITY_NM
   ,KK0251.KAISEN_PLACE_OAZTSU_NM
   ,KK0251.KAISEN_PLACE_AZCHO_NM
   ,KK0251.KAISEN_PLACE_BNCHIGO
   ,KK0251.KAISEN_PLACE_ADRTTM
   ,KK0251.KAISEN_PLACE_ADRRM
   ,KK0251.KISN_PLC_AD_MI_FIX_FLG
   ,TO_CHAR(KK0251.LGTD)
   ,TO_CHAR(KK0251.LTTD)
   ,KK0251.AD_FORM_CD
   ,KK0251.SHUGJ_NYUKYO_FLR_CNT_CD
   ,KK0251.KODATE_KCKU_FLR_CNT_CD
   ,KK0251.DIRECTION_CD_1
   ,KK0251.DIRECTION_CD_2
FROM     KK_T_SVC_KEI KK0081
INNER JOIN KU_T_SVKEI_KOJIAK KU0081
ON       KU0081.SVC_KEI_NO = KK0081.SVC_KEI_NO
INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241
ON       KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO
INNER JOIN KK_T_SVKEI_KAISEN_UW KK0251
ON       KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
WHERE    KK0081.SVC_KEI_STAT = '920'
AND      KK0081.KAIHK_FAIL_KEI_CST_ZM_FLG = '0'
AND      KU0081.SVKEI_KOJIAK_TSTAYMD <= ZZZZZ
AND      KU0081.SVKEI_KOJIAK_TENDYMD = KK0081.SVC_CANCEL_YMD
AND      KU0081.MK_FLG = '0'
AND      KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM =
         (SELECT 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 <= ZZZZZ
          AND    KK0081_GENE.RSV_APLY_CD = '2'
          AND    KK0081_GENE.MK_FLG = '0')
AND      KK0241.KAISEN_UCWK_USE_STAYMD =
         (SELECT MAX(KK0241_GENE.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAX
          FROM   KK_T_KAISEN_TG_SVKEI KK0241_GENE
          WHERE  KK0241_GENE.SVC_KEI_NO = KK0241.SVC_KEI_NO
          AND    KK0241_GENE.KAISEN_UCWK_USE_STAYMD <= ZZZZZ
          AND    KK0241_GENE.KAISEN_UCWK_USE_ENDYMD = KK0081.SVC_CANCEL_YMD
          AND    KK0241_GENE.MK_FLG = '0')
AND      KK0251.GENE_ADD_DTM =
         (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
          FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
          AND    KK0251_GENE.MK_FLG = '0')