set head off;
set termout off;
set echo off;
set feedback off;
set trims on;
set colsep ,
set linesize 400
spool ${MID_DIR_KK}/EO2E80110J0.csv
SELECT       KK0321.SEIKY_KEI_NO
    || ',' || KK0021.MSKM_YMD
    || ',' || SUBSTR(KK0011.MSKM_UK_DTM, 1, 8)
    || ',' || KK0081.SHOSA_YMD
    || ',' || KK1051.ICJKN_SETTE_APLY_MON
    || ',' || KK1051.ICJKN_AMNT
    || ',' || KK1021.SVC_KEI_NO
    || ',' || KK0081.SYSID
    || ',' || SUBSTR(KK1051.TMP_PAY_PRC_NO, 1, 11)
    || ',' || KK0721.TMP_PAY_PRC_NM
    || ',' || ZM0051.USER_NM
    || ',' || ZM0061.ORG_NM
    || ',' || KK0011.MSKM_SBT_CD
    || ',' || KK0971.MSKM_SBT_NM
    || ',' || KK1021.ICJKN_SETTE_NO
    || ',' || KK1051.KICJKN_NO
    || ',' || KK1051.TMP_PAY_PRC_NO
    || ',' || KK1051.APLY_STAT_CD
    || ',' || KK1021.ICJKN_SETTE_CD
FROM
    KK_T_KICJKN KK1051
    INNER JOIN  KK_T_ICJKN_SETTE KK1021  ON KK1021.ICJKN_SETTE_NO = KK1051.ICJKN_SETTE_NO
    INNER JOIN  KK_M_TMP_PAY_PRC KK0721  ON KK0721.TMP_PAY_PRC_NO = KK1051.TMP_PAY_PRC_NO
    INNER JOIN  KK_T_MSKM_DTL    KK0021  ON KK0021.MSKM_DTL_NO    = KK1021.MSKM_DTL_NO
                                        AND KK0021.GENE_ADD_DTM = (
                                                SELECT
                                                    MAX(GENE.GENE_ADD_DTM)
                                                FROM
                                                    KK_T_MSKM_DTL GENE
                                                WHERE
                                                    GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
                                                AND GENE.MK_FLG      = '0')
    INNER JOIN  KK_T_MSKM        KK0011  ON KK0011.MSKM_NO        = KK0021.MSKM_NO
                                        AND KK0011.GENE_ADD_DTM = (
                                                SELECT
                                                    MAX(GENE.GENE_ADD_DTM)
                                                FROM
                                                    KK_T_MSKM GENE
                                                WHERE
                                                    GENE.MSKM_NO = KK0011.MSKM_NO
                                                AND GENE.MK_FLG  = '0')
    INNER JOIN  KK_T_SVC_KEI     KK0081  ON KK0081.SVC_KEI_NO     = KK1021.SVC_KEI_NO
                                        AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (
                                                SELECT
                                                    MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM)
                                                FROM
                                                    KK_T_SVC_KEI GENE
                                                WHERE
                                                    GENE.SVC_KEI_NO    = KK0081.SVC_KEI_NO
                                                AND GENE.RSV_APLY_YMD <= '${OPEDATE}'
                                                AND GENE.RSV_APLY_CD   = '2'
                                                AND GENE.MK_FLG        = '0')
    INNER JOIN  KK_T_KAKINS      KK0321  ON KK0321.SVC_KEI_NO     = KK1021.SVC_KEI_NO
    INNER JOIN  KK_M_MSKM_SBT    KK0971  ON KK0971.MSKM_SBT_CD    = KK0011.MSKM_SBT_CD
    INNER JOIN  KK_T_PRG         KK1091  ON KK1091.SVC_KEI_NO     = KK1021.SVC_KEI_NO
                                        AND KK1091.PRG_STAT = '0900'
                                        AND KK1091.PRG_DTM = (
                                               SELECT MAX(KK1091_01.PRG_DTM)
                                               FROM   KK_T_PRG KK1091_01
                                               WHERE  KK1091_01.SVC_KEI_NO = KK1091.SVC_KEI_NO
                                               AND    KK1091_01.PRG_STAT = '0900'
                                               AND    KK1091_01.MK_FLG = '0')
    INNER JOIN  ZM_M_USER         ZM0051 ON ZM0051.USER_ID        = KK1091.ADD_OPEACNT
    INNER JOIN  ZM_M_ORGANIZATION ZM0061 ON ZM0061.ORG_CD         = ZM0051.SHUMU_SHOZK_ORG_CD
WHERE
    KK1051.ICJKN_SETTE_APLY_MON = '${TRGT_YM}'
AND KK1051.CL_RENKEI_STAT_CD IS NULL
AND KK1051.ICJKN_AMNT != 0
AND KK0021.MSKM_YMD <= '20140331'
AND KK0081.SHOSA_YMD >= '20140401'
AND KK0011.MSKM_SBT_CD != '00017'
AND KK0321.KAKINS_TSTAYMD <= '${OPEDATE}'
AND KK0321.KAKINS_TENDYMD >= '${OPEDATE}'
AND KK1051.MK_FLG = '0'
AND KK1021.MK_FLG = '0'
AND KK0721.MK_FLG = '0'
AND KK0321.MK_FLG = '0'
AND KK0971.MK_FLG = '0'
AND ZM0051.MK_FLG = '0'
AND ZM0061.MK_FLG = '0'
ORDER BY
    KK0321.SEIKY_KEI_NO
;
spool off