SELECT
    CH0111.SHUNOKIKAN_CD
   ,CH0111.NYUKIN_ROUTE_CD
   ,CH0111.OUT_ORG_SHUNO_YMD AS SHUNO_YMD_KEY
   ,CH0111.NYUKIN_TRN_YMD AS NYUKIN_TRN_YMD_KEY
   ,DECODE(CH0111.NYUKIN_ADD_WAY_CD,'1','1','5','1','2') AS DIV_TRAN
   ,CH0051.BANK_CD AS SEIKY_BANK_CD
   ,'' AS SEIKY_BANK_NM
   ,CH0051.BANK_SHITEN_CD AS SEIKY_BANK_SHITEN_CD
   ,'' AS SEIKY_BANK_SHITEN_NM
   ,CH0111.KHRI_BANK_CD
   ,'' AS KHRI_BANK_NM
   ,CH0111.KHRI_BANK_SHITEN_CD
   ,'' AS KHRI_BANK_SHITEN_NM
   ,CH0111.YJHRI_BANK_CD
   ,'' AS YJHRI_BANK_NM
   ,CH0111.YJHRI_BANK_SHITEN_CD
   ,'' AS YJHRI_BANK_SHITEN_NM
   ,CH0111.MADOGUCHI_BANK_CD AS MADO_BANK_CD
   ,'' AS MADO_BANK_NM
   ,CH0111.MADOGUCHI_BANK_SHITEN_CD AS MADO_BANK_SHITEN_CD
   ,'' AS MADO_BANK_SHITEN_NM
   ,CH0111.HRAKMISAKI_BANK_CD AS HRAK_BANK_CD
   ,'' AS HARK_BANK_NM
   ,CH0111.HRAKMISAKI_BANK_SHITEN_CD AS HRAK_BANK_SHITEN_CD
   ,'' AS HARK_BANK_SHITEN_NM
   ,CH0111.YHRIKOMI_BANK_CD
   ,'' AS YHRIKOMI_BANK_NM
   ,CH0111.YHRIKOMI_BANK_SHITEN_CD
   ,'' AS YHRIKOMI_BANK_SHITEN_NM
   ,CH0051.CRECARD_NO
   ,CH0051.CRECARD_COMP_CD AS SEIKY_CRECARD_COMP_CD
   ,CH0051.CREDIT_KOKAN_CD AS SEIKY_CREDIT_KOKAN_CD
   ,'' AS SEIKY_CRECARD_COMP_NM
   ,CH0111.CRECARD_COMP_CD AS NYUKIN_CRECARD_COMP_CD
   ,(SELECT
        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
    WHERE
        KK0531.CRECARD_COMP_CD = CH0111.CRECARD_COMP_CD
    ) AS NYUKIN_CREDIT_KOKAN_CD
   ,'' AS NYUKIN_CRECARD_COMP_NM
   ,CH0111.CVSTORE_CD
   ,'' AS CVSTORE_COMP_NM
   ,CH0111.EAN_MAKER_CD
   ,CH0111.UKETORI_ORG_CD
   ,'' AS UKET_ORG_NM
   ,CH0111.YUKEN_UKETORI_ORG_CD
   ,'' AS YUKEN_ORG_NM
   ,CH0111.SKNKSITKCM_CD
   ,'' AS SKNKSITKCM_NM
   ,'' AS SEIKY_KEI_KANA
   ,CH0111.OUT_ORG_SHUNO_YMD
   ,CH0111.NYUKIN_TRN_YMD
   ,CH0111.NYUKIN_CL_YMD
   ,CH0111.NYUKIN_ADD_WAY_CD
   ,TO_CHAR(CH0111.NYUKIN_AMNT)
   ,CH0111.CVSTORE_PAY_RSV_YMD
   ,CH0111.SEIKY_KEI_NO
   ,CH0111.NYUKIN_NO
   ,CH0111.SEIKY_NO
   ,'' AS KHRI_RSLT_CD
   ,CH0051.SHIKOSAKI_COMP_CD
FROM
    CH_T_NYUKIN CH0111
    LEFT OUTER JOIN CH_T_SEIKY CH0051
        ON  CH0111.SEIKY_NO = CH0051.SEIKY_NO
        AND CH0051.MK_FLG   = '0'
WHERE
    CH0111.NYUKIN_STAT     IN (:NYUKIN_STAT1,:NYUKIN_STAT2)
AND CH0111.NYUKIN_TRN_YMD   = :OPE_DATE
AND CH0111.NYUKIN_SYRZM_FLG = :NYUKIN_SYRZM_FLG
AND CH0111.MK_FLG = '0'