SELECT
     KK0351.OP_SVC_KEI_NO
    ,KK0351.PPLAN_CD
    ,KK0351.SVC_KEI_UCWK_NO
    ,SUBSTR(TO_CHAR(TO_DATE(KK0351.PLAN_CHRG_ENDYMD, 'YYYYMMDD') + 1, 'YYYYMMDD'), 1, 6) AS PLAN_CHRG_ENDYMD
    ,KK0351.OP_SVC_KEI_STAT
FROM
    KK_T_OP_SVC_KEI KK0351
        INNER JOIN
        (
            SELECT
                KK0351_GENE.OP_SVC_KEI_NO
               ,MAX(KK0351_GENE.GENE_ADD_DTM) AS MAX_GENE_ADD_DTM
            FROM
                KK_T_OP_SVC_KEI KK0351_GENE
            INNER JOIN
                (
                SELECT
                    TRIM(CH0401.PRC_SVC_CD) AS PRC_SVC_CD
                FROM
                    CH_M_PRC_KMK_CS_CHGE CH0401
                    INNER JOIN
                        (SELECT
                              CH0401_GENE.SYS_CD
                            , CH0401_GENE.WORK_KINO_SKBT_CD
                            , CH0401_GENE.CHSHT_CHG_CD
                            , CH0401_GENE.PRC_GRP_CD
                            , CH0401_GENE.PCRS_CD
                            , CH0401_GENE.PRC_SVC_CD
                            , CH0401_GENE.PRC_KMK_CD
                            , MAX(CH0401_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM
                         FROM
                            CH_M_PRC_KMK_CS_CHGE CH0401_GENE
                            INNER JOIN
                                (SELECT
                                    WORK_PARAM_SETTE_VALUE
                                FROM
                                    ZM_M_WORK_PARAM_KNRI ZM0321
                                WHERE
                                        ZM0321.WORK_PARAM_ID                =  :WORK_PARAM_ID
                                    AND ZM0321.WORK_PARAM_TSTAYMD           <= :OPEDATE
                                    AND ZM0321.WORK_PARAM_TENDYMD           >= :OPEDATE
                                    AND ZM0321.MK_FLG                       =  '0'
                                )ZM0321
                            ON
                                CH0401_GENE.WORK_KINO_SKBT_CD = ZM0321.WORK_PARAM_SETTE_VALUE
                         WHERE
                                CH0401_GENE.SYS_CD                  =  :SYS_CD
                            AND CH0401_GENE.CHSHT_CHG_CD            =  :CHSHT_CHG_CD
                            AND CH0401_GENE.PRC_KMK_CS_CHGE_TSTAYMD <= :OPEDATE
                            AND CH0401_GENE.PRC_KMK_CS_CHGE_TENDYMD >= :OPEDATE
                            AND CH0401_GENE.MK_FLG                  =  '0'
                        GROUP BY
                              CH0401_GENE.SYS_CD
                            , CH0401_GENE.WORK_KINO_SKBT_CD
                            , CH0401_GENE.CHSHT_CHG_CD
                            , CH0401_GENE.PRC_GRP_CD
                            , CH0401_GENE.PCRS_CD
                            , CH0401_GENE.PRC_SVC_CD
                            , CH0401_GENE.PRC_KMK_CD
                        ) CH0401_GENE
                    ON
                            CH0401.SYS_CD       = CH0401_GENE.SYS_CD
                        AND CH0401.WORK_KINO_SKBT_CD = CH0401_GENE.WORK_KINO_SKBT_CD
                        AND CH0401.CHSHT_CHG_CD = CH0401_GENE.CHSHT_CHG_CD
                        AND CH0401.PRC_GRP_CD   = CH0401_GENE.PRC_GRP_CD
                        AND CH0401.PCRS_CD      = CH0401_GENE.PCRS_CD
                        AND CH0401.PRC_SVC_CD   = CH0401_GENE.PRC_SVC_CD
                        AND CH0401.PRC_KMK_CD   = CH0401_GENE.PRC_KMK_CD
                        AND CH0401.GENE_ADD_DTM = CH0401_GENE.MAX_GENE_DTM
                ) TRGT_PRC_SVC_CD
                ON
                    TRGT_PRC_SVC_CD.PRC_SVC_CD = KK0351_GENE.PPLAN_CD
            WHERE
                    KK0351_GENE.RSV_APLY_CD = '2'
                AND KK0351_GENE.RSV_APLY_YMD <= :PREYMD
                AND KK0351_GENE.MK_FLG = '0'
            GROUP BY
                KK0351_GENE.OP_SVC_KEI_NO
        ) KK0351_GENE
        ON KK0351.OP_SVC_KEI_NO = KK0351_GENE.OP_SVC_KEI_NO
        AND KK0351.GENE_ADD_DTM = KK0351_GENE.MAX_GENE_ADD_DTM