SELECT
        KK0531.CRECARD_COMP_CD,
        KK0531.CRECARD_COMP_NM,
        KK0531.CREDIT_KOKAN_CD
    FROM
        KK_M_CRECARD_COMP KK0531
        INNER JOIN
            (SELECT
                KK0531_GENE.CRECARD_COMP_CD
               ,KK0531_GENE.CRECARD_JIGYO_CD
               ,SUBSTR(MAX(KK0531_GENE.RSV_APLY_YMD || KK0531_GENE.GENE_ADD_DTM),9,17) AS MAX_GENE_DTM
            FROM
                KK_M_CRECARD_COMP KK0531_GENE
            WHERE
                KK0531_GENE.CRECARD_JIGYO_CD      = '2'
            AND KK0531_GENE.RSV_APLY_YMD         <= :OPE_DATE
            AND KK0531_GENE.CRECARD_COMP_TSTAYMD <= :OPE_DATE
            AND KK0531_GENE.CRECARD_COMP_TENDYMD >= :OPE_DATE
            AND KK0531_GENE.MK_FLG                = '0'
            GROUP BY
                KK0531_GENE.CRECARD_COMP_CD
               ,KK0531_GENE.CRECARD_JIGYO_CD
            ) KK0531_GENE
            ON  KK0531.CRECARD_COMP_CD  = KK0531_GENE.CRECARD_COMP_CD
            AND KK0531.CRECARD_JIGYO_CD = KK0531_GENE.CRECARD_JIGYO_CD
            AND KK0531.GENE_ADD_DTM     = KK0531_GENE.MAX_GENE_DTM