#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KK_SELECT_001)
#--・割引サービス情報を抽出する。
#--    ＜抽出条件＞
#--        １．割引サービス.受付開始年月日時分が申込起算日以前であるデータ。
#--        ２．割引サービス.受付終了年月日時分が申込起算日以後であるデータ。
#--        ３．割引サービス.割引適用契機コードが自動適用（２）であるデータ。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
    KK0841.WRIB_SVC_CD,
    KK0841.WRIB_TYPE_CD,
    KK0841.DSP_CAMPAIGN_CD,
    KK0841.WRIB_SVC_NM,
    KK0841.UK_STA_YMDHM,
    KK0841.UK_END_YMDHM,
    KK0841.WRIB_APLY_OPTNTY_CD,
    KK0841.WRIB_SBT_CD,
    KK0841.CAMPAIGN_STA_CD,
    KK0841.STA_ADJ_CD,
    KK0841.STA_ADJ,
    KK0841.CAMPAIGN_END_CD,
    KK0841.UPPL_APLY_CNT,
    KK0841.WRIB_AGING_PRD,
    KK0841.UPPL_KEI_CNT,
    KK0841.YUSEN_JUN_MDL_CD,
    KK0841.YUSEN_JUN_KIND_CD,
    KK0841.YUSEN_JUN_TYPE_CD,
    KK0841.YUSEN_JUN_TYPE_JUN,
    KK0841.JOKEN_TSTAYMD,
    KK0841.JOKEN_TENDYMD,
    KK0841.APLY_CNT_HAMBET_CD,
    KK0841.APLY_CNT,
    KK0841.APLY_JOKEN_CD,
    KK0841.WRIB_ADD_JOKEN_CD,
    KK0841.AGNT_CD,
    KK0841.MSKM_FORM_CD,
    KK0841.GRP_SBT_CD,
    KK0841.INTR_SBT_CD,
    KK0841.RSV_APLY_YMD,
    KK0841.WRIB_SVC_TSTAYMD,
    KK0841.WRIB_SVC_TENDYMD
FROM
    KK_M_WRIB_SVC KK0841
WHERE ? BETWEEN KK0841.UK_STA_YMDHM AND KK0841.UK_END_YMDHM
AND     KK0841.WRIB_APLY_OPTNTY_CD = '2'
AND     KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM =
        (SELECT MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
        FROM    KK_M_WRIB_SVC KK0841_GENE
        WHERE  KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
        AND    KK0841_GENE.RSV_APLY_YMD <= ?
        AND    KK0841_GENE.MK_FLG = '0')
ORDER BY KK0841.YUSEN_JUN_MDL_CD,KK0841.YUSEN_JUN_KIND_CD,KK0841.YUSEN_JUN_TYPE_CD,KK0841.YUSEN_JUN_TYPE_JUN

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KK_SELECT_002)
#--KKPRC00909
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
 KK0481.WRIB_SVC_KEI_NO,
 KK0481.SVC_KEI_NO,
 KK0481.SVC_KEI_UCWK_NO,
 KK0481.OP_SVC_KEI_NO,
 KK0481.SBOP_SVC_KEI_NO,
 KK0481.KKTK_SVC_KEI_NO
FROM
 KK_T_WRIB_SVC_KEI KK0451 INNER JOIN
 KK_T_WRISVC_TG_KEI KK0481
ON
 KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO INNER JOIN 
 KK_M_WRIB_SVC KK0841 
ON 
 KK0451.WRIB_SVC_CD = KK0841.WRIB_SVC_CD 
WHERE
 KK0481.WRIB_SVC_KEI_NO = ? AND
 KK0841.WRIB_SVC_CD = ? AND
 KK0481.GENE_ADD_DTM =
      (SELECT MAX(KK0481_GENE.GENE_ADD_DTM) AS KK0481_MAX
       FROM   KK_T_WRISVC_TG_KEI KK0481_GENE
       WHERE  KK0481_GENE.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO 
       AND    KK0481_GENE.WRIB_SVC_TRGT_KEI_NO = KK0481.WRIB_SVC_TRGT_KEI_NO 
       AND    KK0481_GENE.MK_FLG = '0') AND
 KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM =
      (SELECT MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) AS KK0451_MAX
       FROM   KK_T_WRIB_SVC_KEI KK0451_GENE
       WHERE  KK0451_GENE.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO 
       AND    KK0451_GENE.RSV_APLY_YMD <= ?
       AND    KK0451_GENE.RSV_APLY_CD = '2'
       AND    KK0451_GENE.MK_FLG = '0') AND
 KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM =
      (SELECT MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
       FROM   KK_M_WRIB_SVC KK0841_GENE
       WHERE  KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD 
       AND    KK0841_GENE.RSV_APLY_YMD <= ?
       AND    KK0841_GENE.MK_FLG = '0') AND
 KK0481.MK_FLG = '0' AND
 KK0841.MK_FLG = '0' AND
 KK0451.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KK_SELECT_003)
#--KKPRC00909
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
 KK0841.CAMPAIGN_STA_CD,
 KK0841.STA_ADJ_CD,
 KK0841.STA_ADJ,
 KK0841.APLY_CNT_HAMBET_CD,
 KK0841.APLY_CNT,
 KK0841.INTR_SBT_CD
FROM
 KK_M_WRIB_SVC KK0841
WHERE
 KK0841.WRIB_SVC_CD = ? AND
 KK0841.GENE_ADD_DTM = (SELECT MAX(KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
                        FROM KK_M_WRIB_SVC KK0841_GENE
                        WHERE KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD AND
                              KK0841_GENE.MK_FLG = '0') AND
 KK0841.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KK_SELECT_004)
#--・以下の条件に一致する割引サービス情報を抽出する。
#--　＜抽出条件＞
#--　　１．割引サービスコードが該当の割引サービスコードに一致
#--　　２．バッチ運用年月日が割引サービス適用期間（適用開始・終了）内に存在
#--　　３．予約適用年月日がバッチ運用年月日以下
#--　　４．世代登録年月日時分秒が最大
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
    KK0841.WRIB_SVC_CD,
    KK0841.WRIB_SBT_CD,
    KK0841.APLY_CNT_HAMBET_CD,
    KK0841.APLY_CNT
FROM
  KK_M_WRIB_SVC KK0841
WHERE KK0841.WRIB_SVC_CD = ?
AND   KK0841.WRIB_SVC_TSTAYMD <= ?
AND   KK0841.WRIB_SVC_TENDYMD >= ?
AND   KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM =
         (SELECT MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
          FROM   KK_M_WRIB_SVC KK0841_GENE
          WHERE  KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
          AND    KK0841_GENE.RSV_APLY_YMD <= ?
          AND    KK0841_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KK_SELECT_006)
#--KKPRC02204（割引タイプコード取得）
#-----------------------------------------------------------

--KEY:KK_SELECT_006

SELECT
    KK0841.WRIB_SVC_CD,
    KK0841.GENE_ADD_DTM,
    KK0841.WRIB_TYPE_CD
FROM
    KK_T_SVC_KEI KK0081
    INNER JOIN
        KK_T_WRISVC_TG_KEI KK0481
    ON
        KK0481.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
        KK0481.GENE_ADD_DTM =
      (SELECT MAX(KK0481_GENE.GENE_ADD_DTM) AS KK0481_MAX
       FROM   KK_T_WRISVC_TG_KEI KK0481_GENE
       WHERE  KK0481_GENE.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO 
       AND    KK0481_GENE.WRIB_SVC_TRGT_KEI_NO = KK0481.WRIB_SVC_TRGT_KEI_NO 
       AND    KK0481_GENE.MK_FLG = '0')
    INNER JOIN
        KK_T_WRIB_SVC_KEI KK0451
    ON
        KK0451.WRIB_SVC_KEI_NO = KK0481.WRIB_SVC_KEI_NO
    AND KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM =
    (SELECT
        MAX(KK0451_GENE.RSV_APLY_YMD ||KK0451_GENE.GENE_ADD_DTM) AS KK0451_MAX
    FROM
        KK_T_WRIB_SVC_KEI KK0451_GENE
    WHERE
        KK0451_GENE.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO
        AND KK0451_GENE.RSV_APLY_YMD <= ?
        AND KK0451_GENE.RSV_APLY_CD = '2'
        AND KK0451_GENE.MK_FLG = '0')
    INNER JOIN
        KK_M_WRIB_SVC KK0841
    ON
        KK0841.WRIB_SVC_CD = KK0451.WRIB_SVC_CD
        AND KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM =
        (SELECT
            MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
        FROM
            KK_M_WRIB_SVC KK0841_GENE
        WHERE
            KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
            AND KK0841_GENE.RSV_APLY_YMD <= ?
            AND KK0841_GENE.MK_FLG = '0')
   WHERE
         KK0081.SVC_KEI_NO = ?
        AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM =
        (SELECT
            MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX
        FROM
            KK_T_SVC_KEI KK0081_GENE
        WHERE
            KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO
            AND KK0081_GENE.RSV_APLY_YMD <= ?
            AND KK0081_GENE.RSV_APLY_CD = '2'
            AND KK0081_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KK_SELECT_007)
#--KKPRC00909,KKPRC00914
#--割引サービス情報取得
#-----------------------------------------------------------

--KEY:KK_SELECT_007

SELECT
    KK0841.WRIB_SVC_CD,
    KK0841.WRIB_SVC_NM,
    KK0841.WRIB_TYPE_CD
FROM
    KK_T_WRIB_SVC_KEI KK0451
INNER JOIN
    KK_M_WRIB_SVC KK0841
ON
    KK0841.WRIB_SVC_CD = KK0451.WRIB_SVC_CD
    AND KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM =
    (SELECT
        MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
    FROM
        KK_M_WRIB_SVC KK0841_GENE
    WHERE
        KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
        AND KK0841_GENE.RSV_APLY_YMD <= ?
        AND KK0841_GENE.MK_FLG = '0')
WHERE
    KK0451.WRIB_SVC_KEI_NO = ?
    AND KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM =
    (SELECT
        MAX(KK0451_GENE.RSV_APLY_YMD ||KK0451_GENE.GENE_ADD_DTM) AS KK0451_MAX
    FROM
        KK_T_WRIB_SVC_KEI KK0451_GENE
    WHERE
        KK0451_GENE.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO
        AND KK0451_GENE.RSV_APLY_YMD <= ?
        AND KK0451_GENE.RSV_APLY_CD = '2'
        AND KK0451_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KK_SELECT_008)
#--キャンペーンコードを条件に該当キャンペーンが存在するかチェックする
#-----------------------------------------------------------

--KEY:KK_SELECT_008

SELECT
    '1'
FROM
    KK_M_WRIB_SVC KK0841
WHERE
    KK0841.DSP_CAMPAIGN_CD = ?
AND (KK0841.WRIB_SVC_CD, KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM) = (
    SELECT
        KK0841GENE.WRIB_SVC_CD,
        MAX(KK0841GENE.RSV_APLY_YMD || KK0841GENE.GENE_ADD_DTM)
    FROM
        KK_M_WRIB_SVC KK0841GENE
    WHERE
        KK0841GENE.WRIB_SVC_CD  = KK0841.WRIB_SVC_CD
    AND KK0841GENE.RSV_APLY_YMD <= ?
    AND KK0841GENE.MK_FLG       = '0'
    GROUP BY
        KK0841GENE.WRIB_SVC_CD)

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KK_SELECT_009)
#--キャンペーンコードと料金プランコードを条件に検索し、適用対象となるキャンペーンが存在するかチェックする。
#-----------------------------------------------------------

--KEY:KK_SELECT_009

SELECT
    KK0841.WRIB_SVC_CD,
    KK0851.WRIB_SVC_TRGT_SVC_CD,
    KK0841.UK_STA_YMDHM,
    KK0841.UK_END_YMDHM
FROM
    KK_M_WRIB_SVC KK0841
    INNER JOIN  KK_M_WRISVC_TG_SVC KK0851
        ON  KK0851.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
        AND KK0851.PPLAN_CD                   = ?
        AND KK0851.WRIB_SVC_TRGT_SVC_TSTAYMD <= ?
        AND KK0851.WRIB_SVC_TRGT_SVC_TENDYMD >= ?
        AND KK0851.RSV_APLY_YMD || KK0851.GENE_ADD_DTM = (
                SELECT
                    MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM)
                FROM
                    KK_M_WRISVC_TG_SVC GENE
                WHERE
                    GENE.WRIB_SVC_TRGT_SVC_CD  = KK0851.WRIB_SVC_TRGT_SVC_CD
                AND GENE.RSV_APLY_YMD         <= ?
                AND GENE.MK_FLG                = '0')
WHERE
    KK0841.DSP_CAMPAIGN_CD = ?
AND KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM = (
        SELECT
            MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM)
        FROM
            KK_M_WRIB_SVC GENE
        WHERE
            GENE.WRIB_SVC_CD   = KK0841.WRIB_SVC_CD
        AND GENE.RSV_APLY_YMD <= ?
        AND GENE.MK_FLG        = '0')

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KV_SELECT_001)
#--割引タイプコード、割引適用契機コードを取得します。
#-----------------------------------------------------------

--KEY:KV_SELECT_001

SELECT
    KK0841.WRIB_TYPE_CD,
    KK0841.WRIB_APLY_OPTNTY_CD
FROM
    KK_M_WRIB_SVC KK0841
WHERE
    KK0841.WRIB_SVC_CD = ? AND
    KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM =       (SELECT
                                                            MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
                                                        FROM
                                                            KK_M_WRIB_SVC KK0841_GENE
                                                        WHERE
                                                            KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD        AND
                                                            KK0841_GENE.RSV_APLY_YMD <= ?        AND
                                                            KK0841_GENE.MK_FLG = '0'
                                                        )

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KV_SELECT_002)
#--カレントレコードの割引サービス契約.割引サービスコードを取得します。
#-----------------------------------------------------------

--KEY:KV_SELECT_002

SELECT  KK0841.WRIB_SVC_CD,
        KK0841.WRIB_TYPE_CD
FROM    KK_M_WRIB_SVC KK0841
WHERE   KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM = ( SELECT  MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
                                                       FROM    KK_M_WRIB_SVC KK0841_GENE
                                                       WHERE   KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
                                                       AND     KK0841_GENE.RSV_APLY_YMD <= ?
                                                       AND     KK0841_GENE.MK_FLG = '0' )
ORDER BY KK0841.WRIB_SVC_CD

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KV_SELECT_003)
#--サービス契約の項目をキーに割引サービス、割引サービス対象サービスマスタを参照し、適用可能なキャンペーンを抽出する。
#-----------------------------------------------------------

--KEY:KV_SELECT_003

SELECT DISTINCT
    KK0841.WRIB_SVC_CD,
    KK0841.DSP_CAMPAIGN_CD,
    KK0841.WRIB_SVC_NM,
    KK0841.UK_STA_YMDHM,
    KK0841.UK_END_YMDHM,
    KK0841.WRIB_APLY_OPTNTY_CD,
    KK0841.JOKEN_TSTAYMD,
    KK0841.JOKEN_TENDYMD,
    KK0841.WRIB_ADD_JOKEN_CD,
    KK0841.WRIB_SVC_TSTAYMD,
    KK0841.WRIB_SVC_TENDYMD
FROM
    KK_M_WRISVC_TG_SVC KK0851,
    KK_M_WRIB_SVC KK0841
WHERE 
    KK0851.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
AND ? BETWEEN KK0851.WRIB_SVC_TRGT_SVC_TSTAYMD AND KK0851.WRIB_SVC_TRGT_SVC_TENDYMD
AND KK0851.RSV_APLY_YMD || KK0851.GENE_ADD_DTM = (
    SELECT
        MAX(KK0851_GENE.RSV_APLY_YMD || KK0851_GENE.GENE_ADD_DTM) AS KK0851_MAX
    FROM
        KK_M_WRISVC_TG_SVC KK0851_GENE
    WHERE
        KK0851_GENE.WRIB_SVC_CD = KK0851.WRIB_SVC_CD
    AND KK0851_GENE.RSV_APLY_YMD <= ?
    AND KK0851_GENE.MK_FLG = '0')
AND KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM = (
    SELECT
        MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
    FROM
        KK_M_WRIB_SVC KK0841_GENE
    WHERE
        KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
    AND KK0841_GENE.WRIB_APLY_OPTNTY_CD IN ('1', '2')
    AND KK0841_GENE.WRIB_TYPE_CD IN ('50', '51')
    AND KK0841_GENE.RSV_APLY_YMD <= ?
    AND KK0841_GENE.MK_FLG = '0')
AND (KK0851.WRIB_SVC_TRGT_SVC_SBT_CD = '1'
OR   KK0851.WRIB_SVC_TRGT_SVC_SBT_CD = '2')
AND ? BETWEEN SUBSTR(KK0841.UK_STA_YMDHM, 1, 8) AND SUBSTR(KK0841.UK_END_YMDHM, 1, 8)
AND (KK0851.SVC_CD = ? OR KK0851.SVC_CD IS NULL)
AND (KK0851.PRC_GRP_CD = ? OR KK0851.PRC_GRP_CD IS NULL)
AND (KK0851.PCRS_CD = ? OR KK0851.PCRS_CD IS NULL)
AND (KK0851.PPLAN_CD = ? OR KK0851.PPLAN_CD IS NULL)
ORDER BY WRIB_SVC_CD

#-

#-----------------------------------------------------------
#--割引サービス(KK_M_WRIB_SVC-KV_SELECT_004)
#--割引サービス契約.割引サービスコードに紐づく、割引タイプコード（カレント）を取得します。
#-----------------------------------------------------------

--KEY:KV_SELECT_004

SELECT   KK0841.WRIB_TYPE_CD
FROM     KK_M_WRIB_SVC KK0841
WHERE KK0841.WRIB_SVC_CD = ?
AND   KK0841.WRIB_TYPE_CD IN ('30', '31', '32')
AND   KK0841.RSV_APLY_YMD || KK0841.GENE_ADD_DTM =
      (SELECT MAX(KK0841_GENE.RSV_APLY_YMD || KK0841_GENE.GENE_ADD_DTM) AS KK0841_MAX
       FROM   KK_M_WRIB_SVC KK0841_GENE
       WHERE  KK0841_GENE.WRIB_SVC_CD = KK0841.WRIB_SVC_CD
       AND    KK0841_GENE.RSV_APLY_YMD <= ?
       AND    KK0841_GENE.MK_FLG = '0')

#-