SELECT
    KK0081.SVC_KEI_NO
  , KK0081.PCRS_CD
  , KK0081.PPLAN_CD
  , KK0081.SVC_CHRG_STAYMD
  , KK0081.PLAN_CHRG_STAYMD
  , KK0081.PLAN_CHRG_ENDYMD
  , KK0081.GENE_ADD_DTM
FROM
    KK_T_SVC_KEI KK0081
    INNER JOIN
        (SELECT
            KK0081_GENE.SVC_KEI_NO
          , MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM
        FROM
            KK_T_SVC_KEI KK0081_GENE
        WHERE
            KK0081_GENE.RSV_APLY_CD   = '2'
        AND KK0081_GENE.RSV_APLY_YMD <= :OPE_DATE
        AND KK0081_GENE.MK_FLG        = '0'
        GROUP BY
            KK0081_GENE.SVC_KEI_NO
           ,KK0081_GENE.PCRS_CD
           ,KK0081_GENE.PPLAN_CD
        ) KK0081_GENE
        ON  KK0081.SVC_KEI_NO                          = KK0081_GENE.SVC_KEI_NO
        AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = KK0081_GENE.MAX_GENE_DTM