#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-AC_SELECT_001)
#--【会計用コンテンツ決済売掛金データ抽出】
#--クレジットカード決済対象の課金額を抽出する。
#-----------------------------------------------------------

--KEY:AC_SELECT_001

SELECT
    MAIN.KAIK_UCWK_SBT_CD
    ,MAIN.KAIK_UCWK_SBT_DTL_CD
    ,MAIN.KAIK_UCWK_CD
    ,MAIN.PRC_GRP_CD AS PRC_GRP_CD
    ,MAIN.PCRS_CD AS PCRS_CD
    ,MAIN.PRC_SVC_CD AS PRC_SVC_CD
    ,MIN(MAIN.PRC_KMK_CD) AS PRC_KMK_CD
    ,MAIN.CRECARD_COMP_CD
    ,MAIN.CONT_SVC_CD
    ,COUNT(1) AS CNT
    ,SUM(MAIN.AMNT) AS AMNT
FROM
(
    SELECT
        CHRG.CONT_KEI_NO
        ,CH0691.KAIK_UCWK_SBT_CD
        ,CH0691.KAIK_UCWK_SBT_DTL_CD
        ,CH0691.KAIK_UCWK_CD
        ,MAX(CH0691.PRC_GRP_CD) AS PRC_GRP_CD
        ,MAX(CH0691.PCRS_CD) AS PCRS_CD
        ,MAX(CH0691.PRC_SVC_CD) AS PRC_SVC_CD
        ,MAX(CH0691.PRC_KMK_CD) AS PRC_KMK_CD
        ,DECODE(CN0101.CRECARD_COMP_CD, '00003', '00002', CN0101.CRECARD_COMP_CD) AS CRECARD_COMP_CD
        ,CN0021.CONT_SVC_CD
        ,SUM(CHRG.CHRG_AMNT - CHRG.SALES_GEN_AMNT -CHRG.COMPENSATION) AS AMNT
    FROM
        (
            SELECT
                CN0071.CONT_KEI_NO,
                CN0071.CHRG_TRGT_YM,
                CN0071.SYSID,
                NVL(CN0071.CHRG_AMNT, 0) AS CHRG_AMNT,
                0 AS SALES_GEN_AMNT,
                0 AS COMPENSATION,
                CN0071.CRECARD_ID,
                CN0071.CONT_SVC_CD,
                CN0071.CONT_CD,
                CN0071.TAX_RT
            FROM
                CN_T_CONT_CRECA_CHRG CN0071
            WHERE
                CN0071.CHRG_TRGT_YM =
                (
                    SELECT
                        CH0501.SEIKY_YM
                    FROM
                        CH_M_PRC_SCHDL_TEIGI CH0501
                    WHERE
                        CH0501.EVENT_CD  = ?
                        AND CH0501.EVENT_YMD = ?
                        AND CH0501.MK_FLG    = '0'
                )
                AND CN0071.CHRG_STAT_CD = '0'
                AND CN0071.MK_FLG = '0'
            UNION ALL
            SELECT
                CN0091.CONT_KEI_NO,
                SUBSTR(CN0091.CHRG_TEISE_YMD, 0, 6) AS CHRG_TRGT_YM,
                CN0091.SYSID,
                0 AS CHRG_AMNT,
                NVL(CN0091.SALES_GEN_AMNT, 0) AS SALES_GEN_AMNT,
                NVL(CN0091.COMPENSATION, 0) AS COMPENSATION,
                CN0091.CRECARD_ID,
                CN0091.CONT_SVC_CD,
                CN0091.CONT_CD,
                CN0091.CHRG_JTN_TAX_RT AS TAX_RT
            FROM
                CN_T_CONTCHRGTESEI CN0091
            WHERE
                CN0091.CHRG_TEISE_YMD LIKE
                (
                    SELECT
                        CH0501.SEIKY_YM
                    FROM
                        CH_M_PRC_SCHDL_TEIGI CH0501
                    WHERE
                        CH0501.EVENT_CD  = ?
                        AND CH0501.EVENT_YMD = ?
                        AND CH0501.MK_FLG    = '0'
                ) || '%'
                AND CN0091.CONT_PAYWAY_CD = '1'
                AND CN0091.MK_FLG = '0'
        ) CHRG
        INNER JOIN
        CK_T_CUST CK0011
            ON  CHRG.SYSID = CK0011.SYSID
        INNER JOIN
        CN_T_CONTJIGYO_CRECA CN0101
            ON CHRG.CRECARD_ID = CN0101.CRECARD_ID
        INNER JOIN
        (
            SELECT
                CN0101_GENE.CRECARD_ID,
                MAX(CN0101_GENE.CHRG_TRGT_YM) AS MAX_CHRG_TRGT_YM
            FROM
                CN_T_CONTJIGYO_CRECA CN0101_GENE
            WHERE
                CN0101_GENE.CHRG_TRGT_YM <=
                (
                    SELECT
                        CH0501.SEIKY_YM
                    FROM
                        CH_M_PRC_SCHDL_TEIGI CH0501
                    WHERE
                        CH0501.EVENT_CD  = ?
                        AND CH0501.EVENT_YMD = ?
                        AND CH0501.MK_FLG    = '0'
                )
                AND CN0101_GENE.MK_FLG = '0'
            GROUP BY
                CN0101_GENE.CRECARD_ID
        ) CN0101_GENE
            ON
            CN0101.CHRG_TRGT_YM = CN0101_GENE.MAX_CHRG_TRGT_YM
            AND CN0101.CRECARD_ID = CN0101_GENE.CRECARD_ID
        INNER JOIN
        CH_M_KAIK_UCWK_CHG CH0691
            ON  (CN0101.CRECARD_COMP_CD  = CH0691.CRECARD_COMP_CD
                 OR (CN0101.CRECARD_COMP_CD  = '00003' AND CH0691.KAIK_UCWK_CD LIKE '4%') )
            AND CH0691.KAIK_UCWK_SBT_CD = ?
            AND CH0691.KAIK_UCWK_SBT_DTL_CD = ?
        INNER JOIN
        CN_T_CONT_KEI CN0021
            ON  CN0021.CONT_KEI_NO = CHRG.CONT_KEI_NO
            AND CN0021.CONT_SVC_CD = CH0691.CONT_SVC_CD
    WHERE
        CK0011.KEISHA_TYPE_CD IN ('1', '2')
        AND CK0011.ORSJGS_CD IS NULL
        AND CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM =
            (
                SELECT
                    MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM) AS CK0011_MAX
                FROM
                    CK_T_CUST CK0011_GENE
                WHERE
                    CK0011_GENE.SYSID = CK0011.SYSID
                    AND CK0011_GENE.RSV_APLY_YMD <= ?
                    AND CK0011_GENE.RSV_APLY_CD = '2'
                    AND CK0011_GENE.MK_FLG = '0'
            )
        AND (CN0101.SHIKOSAKI_COMP_CD <> '99' OR CN0101.SHIKOSAKI_COMP_CD IS NULL)
        AND EXISTS
        (
            SELECT
                1
            FROM
                KK_M_CRECARD_COMP KK0531
            WHERE
                KK0531.CRECARD_COMP_CD = CN0101.CRECARD_COMP_CD
                AND KK0531.CREDIT_KOKAN_CD = CN0101.CREDIT_KOKAN_CD
        )
        AND CH0691.RSV_APLY_YMD || CH0691.GENE_ADD_DTM =
            (
                 SELECT
                     MAX(CH0691_GENE.RSV_APLY_YMD || CH0691_GENE.GENE_ADD_DTM) AS CH0691_MAX
                 FROM
                     CH_M_KAIK_UCWK_CHG CH0691_GENE
                 WHERE
                         CH0691_GENE.KAIK_UCWK_SBT_DTL_CD = CH0691.KAIK_UCWK_SBT_DTL_CD
                     AND CH0691_GENE.PRC_GRP_CD = CH0691.PRC_GRP_CD
                     AND CH0691_GENE.PCRS_CD = CH0691.PCRS_CD
                     AND CH0691_GENE.PRC_SVC_CD = CH0691.PRC_SVC_CD
                     AND CH0691_GENE.PRC_KMK_CD = CH0691.PRC_KMK_CD
                     AND CH0691_GENE.NYUKIN_STAT = CH0691.NYUKIN_STAT
                     AND CH0691_GENE.NYUKIN_ROUTE_CD = CH0691.NYUKIN_ROUTE_CD
                     AND CH0691_GENE.SHUNOKIKAN_CD = CH0691.SHUNOKIKAN_CD
                     AND CH0691_GENE.BANK_CD = CH0691.BANK_CD
                     AND CH0691_GENE.BANK_SHITEN_CD = CH0691.BANK_SHITEN_CD
                     AND CH0691_GENE.SHITEIKOZA_CD = CH0691.SHITEIKOZA_CD
                     AND CH0691_GENE.CRECARD_COMP_CD = CH0691.CRECARD_COMP_CD
                     AND CH0691_GENE.CVS_PAY_RSV_YM_JDG_RSLT_CD = CH0691.CVS_PAY_RSV_YM_JDG_RSLT_CD
                     AND CH0691_GENE.EAN_MAKER_CD = CH0691.EAN_MAKER_CD
                     AND CH0691_GENE.SEIKY_SBT_CD = CH0691.SEIKY_SBT_CD
                     AND CH0691_GENE.KNK_MOVE_CD = CH0691.KNK_MOVE_CD
                     AND CH0691_GENE.SUMUP_GETU_SKBT_CD = CH0691.SUMUP_GETU_SKBT_CD
                     AND CH0691_GENE.SUMUP_SK_KNKPK_CD = CH0691.SUMUP_SK_KNKPK_CD
                     AND CH0691_GENE.SUMUP_MT_KNKPK_CD = CH0691.SUMUP_MT_KNKPK_CD
                     AND CH0691_GENE.CONT_SVC_CD = CH0691.CONT_SVC_CD
                     AND CH0691_GENE.TOKUSOKU_STAT = CH0691.TOKUSOKU_STAT
                     AND CH0691_GENE.RSV_APLY_YMD <= ?
                     AND CH0691_GENE.KAIK_UCWK_CHG_TSTAYMD <= ?
                     AND CH0691_GENE.KAIK_UCWK_CHG_TENDYMD >= ?
                     AND CH0691_GENE.MK_FLG = '0'
            )
        AND CN0021.MK_FLG = '0'
        AND (
             EXISTS
             (
              SELECT 1
              FROM
                  (SELECT 
                       CN0311_01.CONT_SVC_CD,CN0311_01.TAXAREA,CN0311_01.PRC_KMK_CD,CN0311_01.RNKSAKI_CD,CN0311_01.CHRG_TAX_RT 
                  FROM CN_M_CONT_PRC_CD_CHG CN0311_01 INNER JOIN
                      (SELECT 
                           CN0311_02.CONT_SVC_CD,CN0311_02.TAXAREA,CN0311_02.RNKSAKI_CD,CN0311_02.CHRG_TAX_RT,MAX(CN0311_02.CONT_PRC_CD_CHG_TSTAYMD) AS CONT_PRC_CD_CHG_TSTAYMD_MAX ,MAX(CN0311_02.CONT_PRC_CD_CHG_NO) CONT_PRC_CD_CHG_NO_MAX
                      FROM CN_M_CONT_PRC_CD_CHG CN0311_02 
                      WHERE CN0311_02.MK_FLG = '0'
                        AND CN0311_02.CONT_PRC_CD_CHG_TSTAYMD < ?
                       GROUP BY CN0311_02.CONT_SVC_CD,CN0311_02.TAXAREA,CN0311_02.RNKSAKI_CD,CN0311_02.CHRG_TAX_RT) CN0311_03
                  ON  CN0311_03.CONT_SVC_CD = CN0311_01.CONT_SVC_CD
                  AND CN0311_03.TAXAREA = CN0311_01.TAXAREA
                  AND ((CN0311_03.RNKSAKI_CD = CN0311_01.RNKSAKI_CD AND CN0311_01.RNKSAKI_CD IS NOT NULL) OR (CN0311_03.RNKSAKI_CD IS NULL AND CN0311_01.RNKSAKI_CD IS NULL))
                  AND CN0311_03.CHRG_TAX_RT = CN0311_01.CHRG_TAX_RT
                  AND CN0311_03.CONT_PRC_CD_CHG_TSTAYMD_MAX = CN0311_01.CONT_PRC_CD_CHG_TSTAYMD
                  AND CN0311_03.CONT_PRC_CD_CHG_NO_MAX = CN0311_01.CONT_PRC_CD_CHG_NO
                  AND CN0311_01.MK_FLG = '0') CN0311 
              WHERE  CN0311.CONT_SVC_CD <> '0070'
                AND  CN0311.CONT_SVC_CD = CN0021.CONT_SVC_CD
                AND  CN0311.TAXAREA = CN0021.TAXAREA
                AND  ((CN0311.RNKSAKI_CD  = CN0021.RNKSAKI_CD AND CN0311.RNKSAKI_CD IS NOT NULL) OR (CN0311.RNKSAKI_CD IS NULL AND CN0021.RNKSAKI_CD = '1') OR (CN0311.RNKSAKI_CD IS NULL AND CN0021.RNKSAKI_CD IS NULL))
                AND  CN0311.CHRG_TAX_RT = TRIM(TO_CHAR(CHRG.TAX_RT,'00'))
                AND  CN0311.PRC_KMK_CD = CH0691.PRC_KMK_CD
             )
             OR
             EXISTS
             (
              SELECT 1
              FROM  ZM_M_SYS_PARAM_KNRI ZM0311
              WHERE ZM0311.SYS_PARAM_BUNRUI_CD = 'WORK_050_PRC_KWK_CD'
              AND   ZM0311.SYS_PARAM_TSTAYMD <= ?
              AND   ZM0311.SYS_PARAM_TENDYMD >= ?
              AND   ZM0311.SYS_PARAM_SETTE_VALUE_1 = CHRG.CONT_CD
              AND   ZM0311.SYS_PARAM_SETTE_VALUE_2 = TRIM(TO_CHAR(CHRG.TAX_RT,'00'))
              AND   ZM0311.SYS_PARAM_SETTE_VALUE_3 = CH0691.PRC_KMK_CD
              AND   CHRG.CONT_SVC_CD = '0070'
             )
            )
    GROUP BY
        CHRG.CONT_KEI_NO
        ,CH0691.KAIK_UCWK_SBT_CD
        ,CH0691.KAIK_UCWK_SBT_DTL_CD
        ,CH0691.KAIK_UCWK_CD
        ,CN0101.CRECARD_COMP_CD
        ,CN0021.CONT_SVC_CD
) MAIN
GROUP BY
    MAIN.KAIK_UCWK_SBT_CD
    ,MAIN.KAIK_UCWK_SBT_DTL_CD
    ,MAIN.KAIK_UCWK_CD
    ,MAIN.PRC_GRP_CD
    ,MAIN.PCRS_CD
    ,MAIN.PRC_SVC_CD
    ,MAIN.CRECARD_COMP_CD
    ,MAIN.CONT_SVC_CD
ORDER BY
    MAIN.KAIK_UCWK_SBT_CD
    ,MAIN.KAIK_UCWK_SBT_DTL_CD
    ,MAIN.KAIK_UCWK_CD
    ,MAIN.CRECARD_COMP_CD
    ,MAIN.CONT_SVC_CD

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-AC_SELECT_002)
#--【会計用コンテンツ決済売掛金データ抽出（卸）】
#--クレジットカード決済対象の課金額を抽出する。
#-----------------------------------------------------------

--KEY:AC_SELECT_002

SELECT
    MAIN.ORSJGS_CD
    ,MAIN.KAIK_UCWK_SBT_CD
    ,MAIN.KAIK_UCWK_SBT_DTL_CD
    ,MAIN.KAIK_UCWK_CD
    ,MAIN.PRC_GRP_CD AS PRC_GRP_CD
    ,MAIN.PCRS_CD AS PCRS_CD
    ,MAIN.PRC_SVC_CD AS PRC_SVC_CD
    ,MIN(MAIN.PRC_KMK_CD) AS PRC_KMK_CD
    ,MAIN.CRECARD_COMP_CD
    ,MAIN.CONT_SVC_CD
    ,COUNT(1) AS CNT
    ,SUM(MAIN.AMNT) AS AMNT
FROM
(
    SELECT
        CK0011.ORSJGS_CD
        ,CHRG.CONT_KEI_NO
        ,CH0691.KAIK_UCWK_SBT_CD
        ,CH0691.KAIK_UCWK_SBT_DTL_CD
        ,CH0691.KAIK_UCWK_CD
        ,MAX(CH0691.PRC_GRP_CD) AS PRC_GRP_CD
        ,MAX(CH0691.PCRS_CD) AS PCRS_CD
        ,MAX(CH0691.PRC_SVC_CD) AS PRC_SVC_CD
        ,MAX(CH0691.PRC_KMK_CD) AS PRC_KMK_CD
        ,DECODE(CN0101.CRECARD_COMP_CD, '00003', '00002', CN0101.CRECARD_COMP_CD) AS CRECARD_COMP_CD
        ,CN0021.CONT_SVC_CD
        ,SUM(CHRG.CHRG_AMNT - CHRG.SALES_GEN_AMNT -CHRG.COMPENSATION) AS AMNT
    FROM
        (
            SELECT
                CN0071.CONT_KEI_NO,
                CN0071.CHRG_TRGT_YM,
                CN0071.SYSID,
                NVL(CN0071.CHRG_AMNT, 0) AS CHRG_AMNT,
                0 AS SALES_GEN_AMNT,
                0 AS COMPENSATION,
                CN0071.CRECARD_ID,
                CN0071.CONT_SVC_CD,
                CN0071.CONT_CD,
                CN0071.TAX_RT
            FROM
                CN_T_CONT_CRECA_CHRG CN0071
            WHERE
                CN0071.CHRG_TRGT_YM =
                (
                    SELECT
                        CH0501.SEIKY_YM
                    FROM
                        CH_M_PRC_SCHDL_TEIGI CH0501
                    WHERE
                        CH0501.EVENT_CD  = ?
                        AND CH0501.EVENT_YMD = ?
                        AND CH0501.MK_FLG    = '0'
                )
                AND CN0071.CHRG_STAT_CD = '0'
                AND CN0071.MK_FLG = '0'
            UNION ALL
            SELECT
                CN0091.CONT_KEI_NO,
                SUBSTR(CN0091.CHRG_TEISE_YMD, 0, 6) AS CHRG_TRGT_YM,
                CN0091.SYSID,
                0 AS CHRG_AMNT,
                NVL(CN0091.SALES_GEN_AMNT, 0) AS SALES_GEN_AMNT,
                NVL(CN0091.COMPENSATION, 0) AS COMPENSATION,
                CN0091.CRECARD_ID,
                CN0091.CONT_SVC_CD,
                CN0091.CONT_CD,
                CN0091.CHRG_JTN_TAX_RT AS TAX_RT
            FROM
                CN_T_CONTCHRGTESEI CN0091
            WHERE
                CN0091.CHRG_TEISE_YMD LIKE
                (
                    SELECT
                        CH0501.SEIKY_YM
                    FROM
                        CH_M_PRC_SCHDL_TEIGI CH0501
                    WHERE
                        CH0501.EVENT_CD  = ?
                        AND CH0501.EVENT_YMD = ?
                        AND CH0501.MK_FLG    = '0'
                ) || '%'
                AND CN0091.CONT_PAYWAY_CD = '1'
                AND CN0091.MK_FLG = '0'
        ) CHRG
        INNER JOIN
        CK_T_CUST CK0011
            ON  CHRG.SYSID = CK0011.SYSID
        INNER JOIN
        CN_T_CONTJIGYO_CRECA CN0101
            ON CHRG.CRECARD_ID = CN0101.CRECARD_ID
        INNER JOIN
        (
            SELECT
                CN0101_GENE.CRECARD_ID,
                MAX(CN0101_GENE.CHRG_TRGT_YM) AS MAX_CHRG_TRGT_YM
            FROM
                CN_T_CONTJIGYO_CRECA CN0101_GENE
            WHERE
                CN0101_GENE.CHRG_TRGT_YM <=
                (
                    SELECT
                        CH0501.SEIKY_YM
                    FROM
                        CH_M_PRC_SCHDL_TEIGI CH0501
                    WHERE
                        CH0501.EVENT_CD  = ?
                        AND CH0501.EVENT_YMD = ?
                        AND CH0501.MK_FLG    = '0'
                )
                AND CN0101_GENE.MK_FLG = '0'
            GROUP BY
                CN0101_GENE.CRECARD_ID
        ) CN0101_GENE
            ON
            CN0101.CHRG_TRGT_YM = CN0101_GENE.MAX_CHRG_TRGT_YM
            AND CN0101.CRECARD_ID = CN0101_GENE.CRECARD_ID
        INNER JOIN
        CH_M_KAIK_UCWK_CHG CH0691
            ON  (CN0101.CRECARD_COMP_CD  = CH0691.CRECARD_COMP_CD
                 OR (CN0101.CRECARD_COMP_CD  = '00003' AND CH0691.KAIK_UCWK_CD LIKE '4%') )
            AND CH0691.KAIK_UCWK_SBT_CD = ?
            AND CH0691.KAIK_UCWK_SBT_DTL_CD = ?
        INNER JOIN
        CN_T_CONT_KEI CN0021
            ON  CN0021.CONT_KEI_NO = CHRG.CONT_KEI_NO
            AND CN0021.CONT_SVC_CD = CH0691.CONT_SVC_CD
    WHERE
        CK0011.KEISHA_TYPE_CD IN ('1', '2')
        AND CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM =
            (
                SELECT
                    MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM) AS CK0011_MAX
                FROM
                    CK_T_CUST CK0011_GENE
                WHERE
                    CK0011_GENE.SYSID = CK0011.SYSID
                    AND CK0011_GENE.RSV_APLY_YMD <= ?
                    AND CK0011_GENE.RSV_APLY_CD = '2'
                    AND CK0011_GENE.MK_FLG = '0'
            )
        AND (CN0101.SHIKOSAKI_COMP_CD <> '99' OR CN0101.SHIKOSAKI_COMP_CD IS NULL)
        AND EXISTS
        (
            SELECT
                1
            FROM
                KK_M_CRECARD_COMP KK0531
            WHERE
                KK0531.CRECARD_COMP_CD = CN0101.CRECARD_COMP_CD
                AND KK0531.CREDIT_KOKAN_CD = CN0101.CREDIT_KOKAN_CD
        )
        AND CH0691.RSV_APLY_YMD || CH0691.GENE_ADD_DTM =
            (
                 SELECT
                     MAX(CH0691_GENE.RSV_APLY_YMD || CH0691_GENE.GENE_ADD_DTM) AS CH0691_MAX
                 FROM
                     CH_M_KAIK_UCWK_CHG CH0691_GENE
                 WHERE
                         CH0691_GENE.KAIK_UCWK_SBT_DTL_CD = CH0691.KAIK_UCWK_SBT_DTL_CD
                     AND CH0691_GENE.PRC_GRP_CD = CH0691.PRC_GRP_CD
                     AND CH0691_GENE.PCRS_CD = CH0691.PCRS_CD
                     AND CH0691_GENE.PRC_SVC_CD = CH0691.PRC_SVC_CD
                     AND CH0691_GENE.PRC_KMK_CD = CH0691.PRC_KMK_CD
                     AND CH0691_GENE.NYUKIN_STAT = CH0691.NYUKIN_STAT
                     AND CH0691_GENE.NYUKIN_ROUTE_CD = CH0691.NYUKIN_ROUTE_CD
                     AND CH0691_GENE.SHUNOKIKAN_CD = CH0691.SHUNOKIKAN_CD
                     AND CH0691_GENE.BANK_CD = CH0691.BANK_CD
                     AND CH0691_GENE.BANK_SHITEN_CD = CH0691.BANK_SHITEN_CD
                     AND CH0691_GENE.SHITEIKOZA_CD = CH0691.SHITEIKOZA_CD
                     AND CH0691_GENE.CRECARD_COMP_CD = CH0691.CRECARD_COMP_CD
                     AND CH0691_GENE.CVS_PAY_RSV_YM_JDG_RSLT_CD = CH0691.CVS_PAY_RSV_YM_JDG_RSLT_CD
                     AND CH0691_GENE.EAN_MAKER_CD = CH0691.EAN_MAKER_CD
                     AND CH0691_GENE.SEIKY_SBT_CD = CH0691.SEIKY_SBT_CD
                     AND CH0691_GENE.KNK_MOVE_CD = CH0691.KNK_MOVE_CD
                     AND CH0691_GENE.SUMUP_GETU_SKBT_CD = CH0691.SUMUP_GETU_SKBT_CD
                     AND CH0691_GENE.SUMUP_SK_KNKPK_CD = CH0691.SUMUP_SK_KNKPK_CD
                     AND CH0691_GENE.SUMUP_MT_KNKPK_CD = CH0691.SUMUP_MT_KNKPK_CD
                     AND CH0691_GENE.CONT_SVC_CD = CH0691.CONT_SVC_CD
                     AND CH0691_GENE.TOKUSOKU_STAT = CH0691.TOKUSOKU_STAT
                     AND CH0691_GENE.RSV_APLY_YMD <= ?
                     AND CH0691_GENE.KAIK_UCWK_CHG_TSTAYMD <= ?
                     AND CH0691_GENE.KAIK_UCWK_CHG_TENDYMD >= ?
                     AND CH0691_GENE.MK_FLG = '0'
            )
        AND CN0021.MK_FLG = '0'
        AND (
             EXISTS
             (
              SELECT 1
              FROM
                  (SELECT 
                       CN0311_01.CONT_SVC_CD,CN0311_01.TAXAREA,CN0311_01.PRC_KMK_CD,CN0311_01.RNKSAKI_CD,CN0311_01.CHRG_TAX_RT 
                  FROM CN_M_CONT_PRC_CD_CHG CN0311_01 INNER JOIN
                      (SELECT 
                           CN0311_02.CONT_SVC_CD,CN0311_02.TAXAREA,CN0311_02.RNKSAKI_CD,CN0311_02.CHRG_TAX_RT,MAX(CN0311_02.CONT_PRC_CD_CHG_TSTAYMD) AS CONT_PRC_CD_CHG_TSTAYMD_MAX ,MAX(CN0311_02.CONT_PRC_CD_CHG_NO) CONT_PRC_CD_CHG_NO_MAX
                      FROM CN_M_CONT_PRC_CD_CHG CN0311_02 
                      WHERE CN0311_02.MK_FLG = '0'
                        AND CN0311_02.CONT_PRC_CD_CHG_TSTAYMD < ?
                       GROUP BY CN0311_02.CONT_SVC_CD,CN0311_02.TAXAREA,CN0311_02.RNKSAKI_CD,CN0311_02.CHRG_TAX_RT) CN0311_03
                  ON  CN0311_03.CONT_SVC_CD = CN0311_01.CONT_SVC_CD
                  AND CN0311_03.TAXAREA = CN0311_01.TAXAREA
                  AND ((CN0311_03.RNKSAKI_CD = CN0311_01.RNKSAKI_CD AND CN0311_01.RNKSAKI_CD IS NOT NULL) OR (CN0311_03.RNKSAKI_CD IS NULL AND CN0311_01.RNKSAKI_CD IS NULL))
                  AND CN0311_03.CHRG_TAX_RT = CN0311_01.CHRG_TAX_RT
                  AND CN0311_03.CONT_PRC_CD_CHG_TSTAYMD_MAX = CN0311_01.CONT_PRC_CD_CHG_TSTAYMD
                  AND CN0311_03.CONT_PRC_CD_CHG_NO_MAX = CN0311_01.CONT_PRC_CD_CHG_NO
                  AND CN0311_01.MK_FLG = '0') CN0311 
              WHERE  CN0311.CONT_SVC_CD <> '0070'
                AND  CN0311.CONT_SVC_CD = CN0021.CONT_SVC_CD
                AND  CN0311.TAXAREA = CN0021.TAXAREA
                AND  ((CN0311.RNKSAKI_CD  = CN0021.RNKSAKI_CD AND CN0311.RNKSAKI_CD IS NOT NULL) OR (CN0311.RNKSAKI_CD IS NULL AND CN0021.RNKSAKI_CD = '1') OR (CN0311.RNKSAKI_CD IS NULL AND CN0021.RNKSAKI_CD IS NULL))
                AND  CN0311.CHRG_TAX_RT = TRIM(TO_CHAR(CHRG.TAX_RT,'00'))
                AND  CN0311.PRC_KMK_CD = CH0691.PRC_KMK_CD
             )
             OR
             EXISTS
             (
              SELECT 1
              FROM  ZM_M_SYS_PARAM_KNRI ZM0311
              WHERE ZM0311.SYS_PARAM_BUNRUI_CD = 'WORK_050_PRC_KWK_CD'
              AND   ZM0311.SYS_PARAM_TSTAYMD <= ?
              AND   ZM0311.SYS_PARAM_TENDYMD >= ?
              AND   ZM0311.SYS_PARAM_SETTE_VALUE_1 = CHRG.CONT_CD
              AND   ZM0311.SYS_PARAM_SETTE_VALUE_2 = TRIM(TO_CHAR(CHRG.TAX_RT,'00'))
              AND   ZM0311.SYS_PARAM_SETTE_VALUE_3 = CH0691.PRC_KMK_CD
              AND   CHRG.CONT_SVC_CD = '0070'
             )
            )
    GROUP BY
        CK0011.ORSJGS_CD
        ,CHRG.CONT_KEI_NO
        ,CH0691.KAIK_UCWK_SBT_CD
        ,CH0691.KAIK_UCWK_SBT_DTL_CD
        ,CH0691.KAIK_UCWK_CD
        ,CN0101.CRECARD_COMP_CD
        ,CN0021.CONT_SVC_CD
) MAIN
GROUP BY
    MAIN.ORSJGS_CD
    ,MAIN.KAIK_UCWK_SBT_CD
    ,MAIN.KAIK_UCWK_SBT_DTL_CD
    ,MAIN.KAIK_UCWK_CD
    ,MAIN.PRC_GRP_CD
    ,MAIN.PCRS_CD
    ,MAIN.PRC_SVC_CD
    ,MAIN.CRECARD_COMP_CD
    ,MAIN.CONT_SVC_CD
ORDER BY
    MAIN.ORSJGS_CD
    ,MAIN.KAIK_UCWK_SBT_CD
    ,MAIN.KAIK_UCWK_SBT_DTL_CD
    ,MAIN.KAIK_UCWK_CD
    ,MAIN.CRECARD_COMP_CD
    ,MAIN.CONT_SVC_CD

#-

#-----------------------CN START----------------------
#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_SELECT_001)
#--コンテンツクレカ課金の会員が、テスト用会員かどうか判定する。
#-----------------------------------------------------------

--KEY:CN_SELECT_001

SELECT COUNT(*) CNT
FROM CN_T_CONT_CRECA_CHRG F INNER JOIN 
(SELECT A.CONT_KEI_NO 
FROM CN_T_CONT_KEI A,
(SELECT C.SYSID 
FROM CK_T_CUST C INNER JOIN 
(SELECT D.SYSID,MAX(D.RSV_APLY_YMD || D.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX 
FROM CK_T_CUST D 
WHERE (D.RSV_APLY_CD = '2' OR D.RSV_APLY_CD IS NULL) AND D.MK_FLG = '0' AND D.RSV_APLY_YMD <= ?
GROUP BY D.SYSID) E 
ON C.SYSID = E.SYSID 
AND (C.RSV_APLY_YMD || C.GENE_ADD_DTM) = E.GENE_ADD_DTM_MAX 
WHERE C.KEISHA_TYPE_CD IN ('1','2','4')) B
WHERE A.CONT_KEI_NO = ?
AND A.SYSID = B.SYSID
AND A.MK_FLG = '0') G
ON F.CONT_KEI_NO = G.CONT_KEI_NO
WHERE F.CONT_KEI_NO_SEQ = ?
AND F.CONT_KEI_TEISE_SEQ = ?

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_SELECT_002)
#--お客様、コンテンツ契約と紐づく有料コンテンツデータをコンテンツクレカ課金より抽出する。
#-----------------------------------------------------------

--KEY:CN_SELECT_002

SELECT
K.CHRG_TRAN_YMD,
K.CHRG_TRGT_YM,
K.CHRG_JTN_MEMBER_SBT_CD,
K.CONT_TANKA,
K.CONT_BUY_CNT,
K.TAX_RT,
K.CHRG_AMNT,
K.CHRG_STAT_CD,
K.PLMIN_DIV,
K.CONT_KEI_NO,
K.CONT_CD,
J.CONT_KEI_NO_SEQ,
J.CONT_KEI_TEISE_SEQ,
J.CONT_KEI_NO,
J.CONT_CD,
J.CONT_OUT_CD,
J.SYSID,
J.CONT_KEI_STA_DTM,
J.CONT_KEI_END_DTM,
J.CONT_KEI_SBT_CD,
J.CONT_SVC_CD,
J.CONT_NM,
J.CONT_SBT_CD,
J.CONT_KEI_YRYO_MRYO_FLG,
J.CONT_SONZAI_FLG,
J.KEI_TRAN_YMD
FROM CN_T_CONT_CRECA_CHRG K
INNER JOIN
(SELECT
A.CONT_KEI_NO_SEQ,
A.CONT_KEI_TEISE_SEQ,
I.CONT_KEI_NO,
I.CONT_CD,
I.CONT_OUT_CD,
I.SYSID,
I.CONT_KEI_STA_DTM,
I.CONT_KEI_END_DTM,
I.CONT_KEI_SBT_CD,
I.CONT_SVC_CD,
I.CONT_NM,
I.CONT_SBT_CD,
I.CONT_KEI_YRYO_MRYO_FLG,
I.CONT_SONZAI_FLG,
I.KEI_TRAN_YMD
FROM CN_T_CONT_KEI I
INNER JOIN
(SELECT B.SYSID, C.CONT_KEI_NO, C.CONT_KEI_NO_SEQ, C.CONT_KEI_TEISE_SEQ
FROM CK_T_CUST B INNER JOIN 
(SELECT D.SYSID,MAX(D.RSV_APLY_YMD || D.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX, E.CONT_KEI_NO, E.CONT_KEI_NO_SEQ, E.CONT_KEI_TEISE_SEQ
FROM CK_T_CUST D 
INNER JOIN
(SELECT F.SYSID, H.CONT_KEI_NO, H.CONT_KEI_NO_SEQ, H.CONT_KEI_TEISE_SEQ
FROM CN_T_CONT_KEI F
INNER JOIN
(SELECT G.CONT_KEI_NO, G.CONT_KEI_NO_SEQ, G.CONT_KEI_TEISE_SEQ
FROM CN_T_CONT_CRECA_CHRG G
WHERE G.CONT_KEI_NO = ?
AND G.CONT_KEI_NO_SEQ = ?
AND G.CONT_KEI_TEISE_SEQ = ?) H
ON F.CONT_KEI_NO = H.CONT_KEI_NO
AND F.MK_FLG = '0') E
ON D.SYSID = E.SYSID
WHERE D.RSV_APLY_CD = '2' AND D.MK_FLG = '0' AND D.RSV_APLY_YMD <= ?
GROUP BY D.SYSID, E.CONT_KEI_NO, E.CONT_KEI_NO_SEQ, E.CONT_KEI_TEISE_SEQ) C
ON B.SYSID = C.SYSID 
AND (B.RSV_APLY_YMD || B.GENE_ADD_DTM) = C.GENE_ADD_DTM_MAX 
WHERE B.KEISHA_TYPE_CD IN ('1','2','4')) A 
ON I.CONT_KEI_NO = A.CONT_KEI_NO
AND I.MK_FLG = '0') J
ON K.CONT_KEI_NO = J.CONT_KEI_NO
AND K.CONT_KEI_NO_SEQ = J.CONT_KEI_NO_SEQ
AND K.CONT_KEI_TEISE_SEQ = J.CONT_KEI_TEISE_SEQ
AND K.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_UPDATE_001)
#--課金対象年月に合致するコンテンツクレカ課金ステータス、クレカ決済請求年月日を更新する。
#-----------------------------------------------------------

--KEY:CN_UPDATE_001

UPDATE CN_T_CONT_CRECA_CHRG A
SET A.CONT_CRECARD_CHRG_STAT = ?,
    A.CRECARD_KSSAI_SEIYMD = ?,
    A.UPD_DTM = 'UPD_DTM',
    A.UPD_OPEACNT = 'UPD_OPE',
    A.UPD_UNYO_YMD = 'UPD_UNYO',
    A.UPD_TRN_ID = 'UPD_TRN'
WHERE A.CHRG_TRGT_YM = ?
  AND A.CONT_CRECARD_CHRG_STAT = ?
  AND A.MK_FLG = ?

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_SELECT_004)
#--コンテンツクレカ課金の請求情報を取得する。
#-----------------------------------------------------------

--KEY:CN_SELECT_004

SELECT
Z.CONT_KEI_NO,Z.CONT_KEI_NO_SEQ,Z.CHRG_TRGT_YM,Z.SYSID,Z.CHRG_AMNT,Z.SALES_GEN_AMNT,Z.COMPENSATION,Z.CONT_CRECARD_CHRG_STAT
,Z.CREDIT_KOKAN_CD,Z.CRECARD_NO,Z.CRECARD_YK_KIGEN,Z.AUTHORI_SHONIN_NO,Z.CRECARD_NO_AZKRI_ID, Z.CRECARD_ID, Z.KSSAI_ID, Z.MERCHANT_DEAL_ID, Z.CRECARD_COMP_CD
FROM
(SELECT
 A.CONT_KEI_NO,A.CONT_KEI_NO_SEQ,A.CHRG_TRGT_YM,A.SYSID,A.CHRG_AMNT,A.SALES_GEN_AMNT,A.COMPENSATION,A.CONT_CRECARD_CHRG_STAT
 ,F.CREDIT_KOKAN_CD,F.CRECARD_NO,F.CRECARD_YK_KIGEN,F.AUTHORI_SHONIN_NO,F.CRECARD_NO_AZKRI_ID, A.CRECARD_ID, F.KSSAI_ID, F.MERCHANT_DEAL_ID, F.CRECARD_COMP_CD
FROM
(SELECT
  CONT_KEI_NO,CONT_KEI_NO_SEQ,CHRG_TRGT_YM,SYSID,CHRG_AMNT,0 AS SALES_GEN_AMNT,0 AS COMPENSATION,CONT_CRECARD_CHRG_STAT,CRECARD_ID
 FROM CN_T_CONT_CRECA_CHRG
 WHERE CHRG_STAT_CD = '0'
 AND MK_FLG = '0'
 UNION ALL
 SELECT
  CONT_KEI_NO,CONT_KEI_NO_SEQ,SUBSTR(CHRG_TEISE_YMD,0,6) AS CHRG_TRGT_YM,SYSID,0 AS CHRG_AMNT,SALES_GEN_AMNT,COMPENSATION,CONTCHRGTESEI_STAT AS CONT_CRECARD_CHRG_STAT,CRECARD_ID
 FROM CN_T_CONTCHRGTESEI
 WHERE CONT_PAYWAY_CD = '1'
 AND MK_FLG = '0') A
 INNER JOIN
 (SELECT B.SYSID
 FROM CK_T_CUST B INNER JOIN
 (SELECT C.SYSID,MAX(C.RSV_APLY_YMD || C.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX 
 FROM CK_T_CUST C
 WHERE C.RSV_APLY_CD = '2' AND C.RSV_APLY_YMD <= ? AND C.MK_FLG = '0'
 GROUP BY C.SYSID) D
 ON B.SYSID = D.SYSID
 AND (B.RSV_APLY_YMD || B.GENE_ADD_DTM)  = D.GENE_ADD_DTM_MAX 
 WHERE B.KEISHA_TYPE_CD IN ('1','2','4')) E
 ON E.SYSID = A.SYSID
 INNER JOIN CN_T_CONTJIGYO_CRECA F
 ON A.SYSID = F.SYSID
 AND A.CRECARD_ID = F.CRECARD_ID
 AND F.MK_FLG = '0'
 INNER JOIN
 (SELECT
 SYSID,CRECARD_ID,MAX(CHRG_TRGT_YM) AS MAX_CHRG_TRGT_YM
 FROM CN_T_CONTJIGYO_CRECA
 WHERE MK_FLG = '0'
 AND CHRG_TRGT_YM <= ?
 GROUP BY SYSID,CRECARD_ID) G
 ON F.SYSID = G.SYSID
 AND F.CHRG_TRGT_YM = G.MAX_CHRG_TRGT_YM
 AND F.CRECARD_ID = G.CRECARD_ID
 ORDER BY A.CONT_KEI_NO,A.CONT_KEI_NO_SEQ) Z
WHERE Z.CHRG_TRGT_YM = ?
AND Z.CONT_CRECARD_CHRG_STAT = '000'
ORDER BY Z.CREDIT_KOKAN_CD,Z.CRECARD_NO,Z.CRECARD_NO_AZKRI_ID,Z.SYSID

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_INSERT_001)
#--コンテンツレプリカ処理にて受領したデータをコンテンツクレカ課金へ登録する
#-----------------------------------------------------------

--KEY:CN_INSERT_001

INSERT INTO CN_T_CONT_CRECA_CHRG A
(A.CONT_KEI_NO
,A.CONT_KEI_NO_SEQ
,A.CONT_KEI_TEISE_SEQ
,A.CONT_CRECARD_CHRG_STAT
,A.SYSID
,A.CONT_SVC_CD
,A.CONT_CD
,A.CONT_OUT_CD
,A.CONT_CAMPAIGN_ID
,A.CONT_CAMPAIGN_NM
,A.CONT_CMP_TG_MMB_BUNRUI_CD
,A.CONT_CHOKI_WRIB_MON_CNT
,A.CONT_CAMPAIGN_FORM_CD
,A.CONT_CAMPAIGN_WRIB_RT
,A.CONT_CAMPAIGN_WRIB_AMNT
,A.PLMIN_DIV
,A.CRECARD_ID
,A.CHRG_TRGT_YM
,A.CHRG_STAT_CD
,A.CHRG_JTN_MEMBER_SBT_CD
,A.CHRG_AMNT
,A.CONT_TANKA
,A.TAX_RT
,A.CONT_BUY_CNT
,A.CRECARD_KSSAI_SEIYMD
,A.CHRG_TRAN_YMD
,A.CCMG_ADD_USER_ID
,A.CCMG_UPD_USER_ID
,A.ADD_DTM
,A.ADD_OPEACNT
,A.UPD_DTM
,A.UPD_OPEACNT
,A.DEL_DTM
,A.DEL_OPEACNT
,A.MK_FLG
,A.ADD_UNYO_YMD
,A.ADD_TRN_ID
,A.UPD_UNYO_YMD
,A.UPD_TRN_ID
)VALUES(
?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,'ADD_OPE'
,?
,'UPD_OPE'
,?
,?
,'0'
,'ADD_UNYO'
,'ADD_TRN'
,'UPD_UNYO'
,'UPD_TRN'
)

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_UPDATE_002)
#--コンテンツレプリカ処理にて受領したデータをコンテンツクレカ課金へ更新する
#-----------------------------------------------------------

--KEY:CN_UPDATE_002

UPDATE CN_T_CONT_CRECA_CHRG A SET
A.CONT_CRECARD_CHRG_STAT = ?,
A.SYSID = ?,
A.CONT_SVC_CD = ?,
A.CONT_CD = ?,
A.CONT_OUT_CD = ?,
A.CONT_CAMPAIGN_ID = ?,
A.CONT_CAMPAIGN_NM = ?,
A.CONT_CMP_TG_MMB_BUNRUI_CD = ?,
A.CONT_CHOKI_WRIB_MON_CNT = ?,
A.CONT_CAMPAIGN_FORM_CD = ?,
A.CONT_CAMPAIGN_WRIB_RT = ?,
A.CONT_CAMPAIGN_WRIB_AMNT = ?,
A.PLMIN_DIV = ?,
A.CRECARD_ID = ?,
A.CHRG_TRGT_YM = ?,
A.CHRG_STAT_CD = ?,
A.CHRG_JTN_MEMBER_SBT_CD = ?,
A.CHRG_AMNT = ?,
A.CONT_TANKA = ?,
A.TAX_RT = ?,
A.CONT_BUY_CNT = ?,
A.CRECARD_KSSAI_SEIYMD = ?,
A.CHRG_TRAN_YMD = ?,
A.CCMG_ADD_USER_ID = ?,
A.CCMG_UPD_USER_ID = ?,
A.ADD_DTM = ?,
A.UPD_DTM = ?,
A.UPD_OPEACNT = 'UPD_OPE',
A.MK_FLG = '0',
A.UPD_UNYO_YMD = 'UPD_UNYO',
A.UPD_TRN_ID = 'UPD_TRN'
WHERE
A.CONT_KEI_NO = ?
AND A.CONT_KEI_NO_SEQ = ?
AND A.CONT_KEI_TEISE_SEQ = ?

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_SELECT_005)
#--コンテンツ契約番号、コンテンツ契約番号枝番、コンテンツ訂正通番に合致するコンテンツクレカ課金のレコード件数を取得する。
#-----------------------------------------------------------

--KEY:CN_SELECT_005

SELECT COUNT(CONT_KEI_NO) CNT
FROM CN_T_CONT_CRECA_CHRG A
WHERE A.CONT_KEI_NO = ?
AND A.CONT_KEI_NO_SEQ = ?
AND A.CONT_KEI_TEISE_SEQ = ?

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_UPDATE_003)
#--課金対象年月に合致するコンテンツクレカ課金ステータスを更新する。
#-----------------------------------------------------------

--KEY:CN_UPDATE_003

UPDATE CN_T_CONT_CRECA_CHRG A
SET A.CONT_CRECARD_CHRG_STAT = ?,
    A.UPD_DTM = 'UPD_DTM',
    A.UPD_OPEACNT = 'UPD_OPE',
    A.UPD_UNYO_YMD = 'UPD_UNYO',
    A.UPD_TRN_ID = 'UPD_TRN'
    
WHERE A.CHRG_TRGT_YM = ?
  AND A.CONT_CRECARD_CHRG_STAT = ?
  AND A.MK_FLG = ?

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_SELECT_007)
#--コンテンツクレカ課金を駆動表とし、課金対象年月が集計対象月に合致し、且つ、請求済のコンテンツ契約情報を取得する（マスタ会員用）。
#-----------------------------------------------------------

--KEY:CN_SELECT_007

SELECT
Z.CONT_KEI_NO,
Z.CONT_KEI_NO_SEQ,
Z.CONT_SVC_CD,
Z.CONT_CD,
Y.CONT_SVC_NM,
Z.CONT_NM,
Z.CONT_OUT_CD,
Z.CONT_KEI_STA_DTM,
Z.CONT_KEI_END_DTM,
Z.CONT_SONZAI_FLG,
Z.CONT_SVC_CD,
Z.CHRG_TRGT_YM,
Z.SYSID,
Z.PLMIN_DIV,
Z.CONT_TANKA,
Z.CONT_BUY_CNT,
Z.CHRG_AMNT,
Z.SUPPLIER,
Z.CONT_CRECARD_CHRG_STAT,
Z.CREDIT_KOKAN_CD,
Z.KEISHA_TYPE_CD,
Z.MEMBER_SBT_CD
FROM
(SELECT
  A.CONT_KEI_NO,
  A.CONT_KEI_NO_SEQ,
  A.CONT_KEI_TEISE_SEQ,
  A.CONT_SVC_CD,
  A.CONT_OUT_CD,
  A.CONT_CD,
  A.CONT_NM,
  A.CONT_KEI_STA_DTM,
  A.CONT_KEI_END_DTM,
  A.CHRG_TRGT_YM,
  A.SYSID,
  A.PLMIN_DIV,
  A.CONT_TANKA,
  A.CONT_BUY_CNT,
  A.CHRG_AMNT,
  A.SUPPLIER,
  A.CONT_CRECARD_CHRG_STAT,
  A.CONT_SONZAI_FLG,
  F.CREDIT_KOKAN_CD,
  E.KEISHA_TYPE_CD,
  E.MEMBER_SBT_CD
FROM
  (SELECT
    A1.CONT_KEI_NO,
    A1.CONT_KEI_NO_SEQ,
    A1.CONT_KEI_TEISE_SEQ,
    A1.CONT_SVC_CD,
    A1.CONT_CD,
    A2.CONT_NM,
    A1.CONT_OUT_CD,
    A2.CONT_KEI_STA_DTM,
    A2.CONT_KEI_END_DTM,
    A1.CHRG_TRGT_YM,
    A1.SYSID,
    A1.PLMIN_DIV,
    A1.CONT_TANKA,
    A1.CONT_BUY_CNT,
    A1.CHRG_AMNT,
    A2.SUPPLIER,
    A1.CONT_CRECARD_CHRG_STAT,
    A1.CRECARD_ID,
    A2.CONT_SONZAI_FLG
 FROM CN_T_CONT_CRECA_CHRG A1 INNER JOIN CN_T_CONT_KEI A2 
ON  A1.CONT_KEI_NO = A2.CONT_KEI_NO
WHERE A1.CHRG_STAT_CD = '0' AND A1.MK_FLG = '0' AND A2.MK_FLG = '0' AND A1.CHRG_TRGT_YM = ? AND A1.CONT_CRECARD_CHRG_STAT = '001') A
 INNER JOIN
 (SELECT B.SYSID,B.KEISHA_TYPE_CD,B.MEMBER_SBT_CD
 FROM CK_T_CUST B INNER JOIN
 (SELECT C.SYSID,MAX(C.RSV_APLY_YMD || C.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX 
 FROM CK_T_CUST C
 WHERE C.RSV_APLY_CD = '2' AND C.RSV_APLY_YMD <= ? AND C.MK_FLG = '0'
 GROUP BY C.SYSID) D
 ON B.SYSID = D.SYSID
 AND (B.RSV_APLY_YMD || B.GENE_ADD_DTM)  = D.GENE_ADD_DTM_MAX 
 ) E
 ON E.SYSID = A.SYSID
 INNER JOIN CN_T_CONTJIGYO_CRECA F
 ON A.SYSID = F.SYSID
 AND A.CRECARD_ID = F.CRECARD_ID
 AND F.MK_FLG = '0'
 INNER JOIN
 (SELECT
 SYSID,CRECARD_ID,MAX(CHRG_TRGT_YM) AS MAX_CHRG_TRGT_YM
 FROM CN_T_CONTJIGYO_CRECA
 WHERE MK_FLG = '0'
 AND CHRG_TRGT_YM <= ?
 GROUP BY SYSID,CRECARD_ID) G
 ON F.SYSID = G.SYSID
 AND F.CHRG_TRGT_YM = G.MAX_CHRG_TRGT_YM
 AND F.CRECARD_ID = G.CRECARD_ID
 ORDER BY A.CONT_SVC_CD) Z
INNER JOIN
(SELECT X.CONT_SVC_CD,
X.CONT_SVC_NM
FROM CN_M_CONT_SVC X
WHERE X.MK_FLG = '0') Y
ON Y.CONT_SVC_CD = Z.CONT_SVC_CD
AND Z.CHRG_TRGT_YM = ?
AND Z.CONT_CRECARD_CHRG_STAT = '001'
ORDER BY Z.SYSID,Z.CONT_KEI_NO,Z.CONT_KEI_NO_SEQ,Z.CONT_KEI_TEISE_SEQ

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_SELECT_008)
#--コンテンツクレカ課金を駆動表とし、課金対象年月が集計対象月に合致し、且つ、請求済のコンテンツ契約情報を取得する（ファミリー会員用）。
#-----------------------------------------------------------

--KEY:CN_SELECT_008

SELECT
A.CONT_KEI_NO,
A.CONT_KEI_NO_SEQ,
B.CONT_SVC_CD,
C.CONT_SVC_NM,
B.CONT_CD,
B.CONT_OUT_CD,
B.CONT_SONZAI_FLG,
B.CONT_NM,
B.CONT_KEI_STA_DTM,
B.CONT_KEI_END_DTM,
A.PLMIN_DIV,
A.CONT_TANKA,
A.CONT_BUY_CNT,
A.CHRG_AMNT,
J.CRECARD_COMP_NM,
B.SUPPLIER,
A.SYSID,
' ' AS    SVC_KEI_NO,
X.KEISHA_TYPE_CD,
X.MEMBER_SBT_CD,0 AS DSP_JUN
FROM (((((CN_T_CONT_CRECA_CHRG A
INNER JOIN (SELECT
            AA.CONT_KEI_NO,
            AA.CONT_KEI_NO_SEQ AS CONT_KEI_NO_SEQ,
            MAX(AA.CONT_KEI_TEISE_SEQ) AS CONT_KEI_TEISE_SEQ_MAX    
            FROM CN_T_CONT_CRECA_CHRG AA
            WHERE AA.CHRG_TRGT_YM = ?
            AND AA.CONT_CRECARD_CHRG_STAT = '001'
            AND AA.MK_FLG = '0'
            GROUP BY AA.CONT_KEI_NO, AA.CONT_KEI_NO_SEQ) BB
ON A.CONT_KEI_NO = BB.CONT_KEI_NO
AND A.CONT_KEI_NO_SEQ = BB.CONT_KEI_NO_SEQ
AND A.CONT_KEI_TEISE_SEQ = BB.CONT_KEI_TEISE_SEQ_MAX) 
    INNER JOIN (CN_T_CONT_KEI B
        INNER JOIN CN_M_CONT_SVC C
        ON B.CONT_SVC_CD = C.CONT_SVC_CD
        AND C.MK_FLG = '0')
    ON A.CONT_KEI_NO = B.CONT_KEI_NO
    AND B.MK_FLG = '0')
        INNER JOIN (SELECT 
                    D.KEISHA_TYPE_CD,
                    D.MEMBER_SBT_CD,
                    D.SYSID
                    FROM CK_T_CUST D 
                    WHERE  (D.RSV_APLY_YMD || D.GENE_ADD_DTM)= (SELECT 
                                    MAX(V.RSV_APLY_YMD || V.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX 
                                    FROM CK_T_CUST V 
                                    WHERE V.SYSID = D.SYSID
                                    AND V.RSV_APLY_CD = '2' 
                                    AND V.MK_FLG = '0' 
                                    AND V.RSV_APLY_YMD <= ?
                                    GROUP BY V.SYSID)
                    AND D.MEMBER_SBT_CD IN('11', '21')) X
        ON A.SYSID = X.SYSID)
            INNER JOIN (SELECT 
                        MAX(I.CRECARD_COMP_NM) AS CRECARD_COMP_NM,
                        E.CRECARD_ID
                        FROM CN_T_CONTJIGYO_CRECA E
                            INNER JOIN (SELECT 
                                        F.CRECARD_COMP_NM,
                                        F.CRECARD_COMP_CD 
                                        FROM KK_M_CRECARD_COMP F 
                                            INNER JOIN (SELECT 
                                                        G.CRECARD_COMP_CD,
                                                        MAX(G.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX,
                                                        G.CRECARD_JIGYO_CD
                                                        FROM KK_M_CRECARD_COMP G 
                                                        WHERE G.CRECARD_COMP_TSTAYMD <= ? 
                                                          AND G.CRECARD_COMP_TENDYMD >= ? 
                                                          AND (G.RSV_APLY_YMD <= ?)
                                                          AND G.MK_FLG = '0'
                                                        GROUP BY G.CRECARD_COMP_CD, G.CRECARD_JIGYO_CD) H 
                                            ON F.CRECARD_COMP_CD = H.CRECARD_COMP_CD 
                                            AND F.GENE_ADD_DTM = H.GENE_ADD_DTM_MAX
                                            AND F.CRECARD_JIGYO_CD = H.CRECARD_JIGYO_CD
                                            AND F.CRECARD_JIGYO_CD = '1') I
                            ON E.CRECARD_COMP_CD = I.CRECARD_COMP_CD
                        WHERE E.MK_FLG = '0'
                        GROUP BY E.CRECARD_ID) J
                        ON A.CRECARD_ID = J.CRECARD_ID))
ORDER BY A.SYSID,A.CONT_KEI_NO


#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_SELECT_009)
#--コンテンツクレカ課金を駆動表とし、課金対象年月が集計対象月に合致し、且つ、請求済のコンテンツ契約情報を取得する（非eo会員、オープン会員用）。
#-----------------------------------------------------------

--KEY:CN_SELECT_009

SELECT
A.CONT_KEI_NO,
A.CONT_KEI_NO_SEQ,
B.CONT_SVC_CD,
C.CONT_SVC_NM,
B.CONT_CD,
B.CONT_OUT_CD,
B.CONT_SONZAI_FLG,
B.CONT_NM,
B.CONT_KEI_STA_DTM,
B.CONT_KEI_END_DTM,
A.PLMIN_DIV,
A.CONT_TANKA,
A.CONT_BUY_CNT,
A.CHRG_AMNT,
J.CRECARD_COMP_NM,
B.SUPPLIER,
A.SYSID,
X.KEISHA_TYPE_CD,
X.MEMBER_SBT_CD
FROM ((((CN_T_CONT_CRECA_CHRG A
INNER JOIN (SELECT
            AA.CONT_KEI_NO,
            CONT_KEI_NO_SEQ,
            MAX(AA.CONT_KEI_TEISE_SEQ) AS CONT_KEI_TEISE_SEQ_MAX    
            FROM CN_T_CONT_CRECA_CHRG AA
            WHERE AA.CHRG_TRGT_YM = ?
            AND AA.CONT_CRECARD_CHRG_STAT = '001'
            AND AA.MK_FLG = '0'
            GROUP BY AA.CONT_KEI_NO,CONT_KEI_NO_SEQ) BB
ON A.CONT_KEI_NO = BB.CONT_KEI_NO
AND A.CONT_KEI_NO_SEQ = BB.CONT_KEI_NO_SEQ
AND A.CONT_KEI_TEISE_SEQ = BB.CONT_KEI_TEISE_SEQ_MAX) 
    INNER JOIN (CN_T_CONT_KEI B
        INNER JOIN CN_M_CONT_SVC C
        ON B.CONT_SVC_CD = C.CONT_SVC_CD
        AND C.MK_FLG = '0')
    ON A.CONT_KEI_NO = B.CONT_KEI_NO
    AND B.MK_FLG = '0')
        INNER JOIN (SELECT 
                    D.KEISHA_TYPE_CD,
                    D.MEMBER_SBT_CD,
                    D.SYSID
                    FROM CK_T_CUST D 
                        INNER JOIN (SELECT 
                                    V.SYSID,
                                    V.RSV_APLY_CD,
                                    MAX(V.GENE_ADD_DTM || V.RSV_APLY_YMD) AS GENE_ADD_DTM_MAX 
                                    FROM CK_T_CUST V 
                                    WHERE (V.RSV_APLY_CD = '2') 
                                    AND V.MK_FLG = '0' 
                                    AND V.RSV_APLY_YMD <= ?
                                    GROUP BY V.SYSID,V.RSV_APLY_CD) W 
                        ON D.SYSID = W.SYSID 
                        AND (D.GENE_ADD_DTM || D.RSV_APLY_YMD)= W.GENE_ADD_DTM_MAX
                        AND D.RSV_APLY_CD = W.RSV_APLY_CD 
                    WHERE D.MEMBER_SBT_CD IN('00', '30')) X
        ON A.SYSID = X.SYSID)
            INNER JOIN (SELECT 
                            MAX(I.CRECARD_COMP_NM) AS CRECARD_COMP_NM,
                            E.CRECARD_ID
                            FROM CN_T_CONTJIGYO_CRECA E
                                INNER JOIN (SELECT 
                                            F.CRECARD_COMP_NM,
                                            F.CRECARD_COMP_CD 
                                            FROM KK_M_CRECARD_COMP F 
                                                INNER JOIN (SELECT 
                                                            G.CRECARD_COMP_CD,
                                                            MAX(G.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX,
                                                            G.CRECARD_JIGYO_CD
                                                            FROM KK_M_CRECARD_COMP G 
                                                        WHERE G.CRECARD_COMP_TSTAYMD <= ? 
                                                              AND G.CRECARD_COMP_TENDYMD >= ? 
                                                              AND (G.RSV_APLY_YMD <= ?)
                                                              AND G.MK_FLG = '0'
                                                            GROUP BY G.CRECARD_COMP_CD, G.CRECARD_JIGYO_CD) H 
                                                ON F.CRECARD_COMP_CD = H.CRECARD_COMP_CD 
                                                AND F.GENE_ADD_DTM = H.GENE_ADD_DTM_MAX
                                                AND F.CRECARD_JIGYO_CD = H.CRECARD_JIGYO_CD
                                                AND F.CRECARD_JIGYO_CD = '1') I
                                ON E.CRECARD_COMP_CD = I.CRECARD_COMP_CD
                            WHERE E.MK_FLG = '0'
                            GROUP BY E.CRECARD_ID) J
            ON A.CRECARD_ID = J.CRECARD_ID)

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_SELECT_010)
#--コンテンツクレカ課金を駆動表として、クレジットカード会社コード、クレジットカード会社名、コンテンツサービスコード、課金額を取得する。※テスト用ID、未移行会員は除く。
#-----------------------------------------------------------

--KEY:CN_SELECT_010

SELECT
Z.CONT_SVC_CD,
Z.CHRG_TRGT_YM,
Z.SYSID,
Z.CHRG_AMNT,
Z.CONT_CRECARD_CHRG_STAT,
Z.CREDIT_KOKAN_CD,
Y.CONT_SVC_NM,
V.DSP_JUN
FROM
(SELECT
  A.CONT_SVC_CD,
  A.CHRG_TRGT_YM,
  A.SYSID,
  A.CHRG_AMNT,
  A.CONT_CRECARD_CHRG_STAT,
  F.CREDIT_KOKAN_CD
FROM
  (SELECT
    CONT_SVC_CD,
    CHRG_TRGT_YM,
    SYSID,
    CHRG_AMNT,
    CONT_CRECARD_CHRG_STAT,
    CRECARD_ID
 FROM CN_T_CONT_CRECA_CHRG
 WHERE CHRG_STAT_CD = '0'
 AND MK_FLG = '0') A
 INNER JOIN
 (SELECT B.SYSID
 FROM CK_T_CUST B INNER JOIN
 (SELECT C.SYSID,MAX(C.RSV_APLY_YMD || C.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX 
 FROM CK_T_CUST C
 WHERE C.RSV_APLY_CD = '2' AND C.RSV_APLY_YMD <= ? AND C.MK_FLG = '0'
 GROUP BY C.SYSID) D
 ON B.SYSID = D.SYSID
 AND (B.RSV_APLY_YMD || B.GENE_ADD_DTM)  = D.GENE_ADD_DTM_MAX 
 WHERE B.KEISHA_TYPE_CD IN ('1','2','4')) E
 ON E.SYSID = A.SYSID
 INNER JOIN CN_T_CONTJIGYO_CRECA F
 ON A.SYSID = F.SYSID
 AND A.CRECARD_ID = F.CRECARD_ID
 AND F.MK_FLG = '0'
 INNER JOIN
 (SELECT
 SYSID,CRECARD_ID,MAX(CHRG_TRGT_YM) AS MAX_CHRG_TRGT_YM
 FROM CN_T_CONTJIGYO_CRECA
 WHERE MK_FLG = '0'
 AND CHRG_TRGT_YM <= ?
 GROUP BY SYSID,CRECARD_ID) G
 ON F.SYSID = G.SYSID
 AND F.CHRG_TRGT_YM = G.MAX_CHRG_TRGT_YM
 AND F.CRECARD_ID = G.CRECARD_ID
 ORDER BY A.CONT_SVC_CD) Z
LEFT OUTER JOIN
(SELECT X.CONT_SVC_CD,
X.CONT_SVC_NM
FROM CN_M_CONT_SVC X
WHERE X.MK_FLG = '0') Y
ON Z.CONT_SVC_CD = Y.CONT_SVC_CD
INNER JOIN
(SELECT W.DSP_JUN,
W.CD_DIV_NM
FROM ZM_M_CD_NM_KANRI W
WHERE W.MK_FLG = '0'
AND W.CD_DIV_ALI = '0'
AND W.CD_SBT_CD = 'CD01200') V
ON Z.CONT_SVC_CD = V.CD_DIV_NM
WHERE Z.CHRG_TRGT_YM = ?
AND Z.CONT_CRECARD_CHRG_STAT = '001'
ORDER BY Z.CREDIT_KOKAN_CD,Z.SYSID

#-

#-----------------------------------------------------------
#--コンテンツクレカ課金(CN_T_CONT_CRECA_CHRG-CN_DELETE_001)
#--コンテンツ契約番号に合致するレコードを物理削除する。
#-----------------------------------------------------------

--KEY:CN_DELETE_001

DELETE
FROM CN_T_CONT_CRECA_CHRG CN0071
WHERE CN0071.CONT_KEI_NO = ?

#-

#-----------------------CN END----------------------