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