#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_001)
#--KKPRC09701
#--割引サービス契約内訳データ抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT 
 KK2361.WRIB_SVC_KEI_NO,
 KK2361.WRIB_SVC_KEI_UCWK_NO,
 KK2361.GENE_ADD_DTM
FROM
KK_T_WRISVC_KEI_UCWK KK2361
WHERE
 KK2361.WRIB_SVC_KEI_NO = ? AND
 KK2361.MK_FLG = '0' AND
 KK2361.GENE_ADD_DTM = (SELECT MAX(KK2361_GENE.GENE_ADD_DTM) KK2361_MAX
                        FROM KK_T_WRISVC_KEI_UCWK KK2361_GENE
                        WHERE KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
                              AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
                              AND KK2361_GENE.MK_FLG = '0') 
ORDER BY WRIB_SVC_KEI_UCWK_NO

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_002)
#--・以下の条件に一致する割引サービス契約内訳情報を抽出する。
#--　＜抽出条件＞
#--　　１．割引サービス契約番号が該当の割引サービス契約番号に一致
#--　　２．割引サービス詳細コードが該当の割引サービス詳細コードに一致
#--　　３．世代登録年月日時分秒が最大
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT 
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.GENE_ADD_DTM,
    KK2361.WRIB_SVC_KEI_UCWK_STAT,
    KK2361.WRIB_SVC_DTL_CD,
    KK2361.WRIB_APLY_CNT,
    KK2361.SVC_CHRG_STAYMD,
    KK2361.SVC_CHRG_ENDYMD,
    KK2361.CHRG_STA_YMD_HOSEI_UM,
    KK2361.FIRST_WRIB_APLY_YMD,
    KK2361.ADD_DTM,
    KK2361.ADD_OPEACNT,
    KK2361.UPD_DTM,
    KK2361.UPD_OPEACNT,
    KK2361.DEL_DTM,
    KK2361.DEL_OPEACNT,
    KK2361.MK_FLG
FROM 
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ?
AND KK2361.WRIB_SVC_DTL_CD = ? 
AND KK2361.GENE_ADD_DTM =
         (SELECT MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_MAX
          FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE
          WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
          AND    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
          AND    KK2361_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_003)
#--・以下の条件に一致する割引サービス契約内訳情報の件数を取得する。
#--　＜抽出条件＞
#--　　１．割引サービス契約番号が該当の割引サービス契約番号に一致
#--　　２．カレントレコードであること
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT   COUNT(*) AS UCWK_CNT
FROM     KK_T_WRISVC_KEI_UCWK KK2361
WHERE    KK2361.WRIB_SVC_KEI_NO = ?
AND      KK2361.GENE_ADD_DTM =
         (SELECT MAX(KK2361_GENE_01.GENE_ADD_DTM) AS KK2361_MAX
          FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE_01
          WHERE  KK2361_GENE_01.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
          AND    KK2361_GENE_01.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
          AND    KK2361_GENE_01.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_004)
#--・以下の条件に一致する割引サービス契約内訳情報の件数を取得する。
#--　＜抽出条件＞
#--　　１．割引サービス契約番号が該当の割引サービス契約番号に一致
#--　　２．カレントレコードであること
#--　　３．割引サービス契約内訳ステータスが登録済以外であること
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT   COUNT(*) AS TEKIYO_ZUMI_UCWK_CNT
FROM     KK_T_WRISVC_KEI_UCWK KK2361
WHERE    KK2361.WRIB_SVC_KEI_NO = ?
AND      KK2361.GENE_ADD_DTM =
         (SELECT MAX(KK2361_GENE_01.GENE_ADD_DTM) AS KK2361_MAX
          FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE_01
          WHERE  KK2361_GENE_01.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
          AND    KK2361_GENE_01.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
          AND    KK2361_GENE_01.MK_FLG = '0')
AND       WRIB_SVC_KEI_UCWK_STAT <> '010'

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_005)
#--長期継続利用割引終了
#--長期継続利用割引終了対象抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT
    KK0451.WRIB_SVC_KEI_NO
   ,KK0481.WRIB_SVC_TRGT_KEI_NO
   ,KK2361.WRIB_SVC_KEI_UCWK_NO
   ,KK0451.WRIB_AMNT_SNST_STDARDYMD
FROM  KK_T_WRIB_SVC_KEI KK0451

  INNER JOIN  KK_T_WRISVC_KEI_UCWK KK2361
  ON  KK2361.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO

  INNER JOIN  KK_T_WRISVC_TG_KEI KK0481
  ON  KK0481.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO

WHERE  KK0451.WRIB_SVC_KEI_STAT = '100'
  AND  KK0451.WRIB_SVC_KEI_AT_KEIZK_SKCD = '0'
  AND  KK0451.WRIB_SVC_CD = 'W00000041'
  AND  KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM = (
    SELECT  MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) AS KK0451_GENE_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  KK2361.WRIB_SVC_KEI_UCWK_STAT = '010'
  AND  KK2361.GENE_ADD_DTM = (
    SELECT MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_GENE_MAX
    FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE
    WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
    AND    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
    AND    KK2361_GENE.MK_FLG = '0')

  AND  KK0481.GENE_ADD_DTM = (
    SELECT MAX(KK0481_GENE.GENE_ADD_DTM) AS KK0481_GENE_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    ? BETWEEN KK0481.WRISVC_TG_KEI_TSTAYMD AND KK0481.WRISVC_TG_KEI_TENDYMD
    AND    KK0481_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_006)
#--長期継続利用割引終了継続設定
#--同一割引サービス契約番号に対して割引サービス契約ステータスが「適用済」ではないレコードの存在チェック
#-----------------------------------------------------------

--KEY:KK_SELECT_006

SELECT 1 FROM DUAL
WHERE EXISTS (
  SELECT 1
  FROM  KK_T_WRISVC_KEI_UCWK KK2361
  WHERE KK2361.WRIB_SVC_KEI_NO = ?
  AND   KK2361.WRIB_SVC_KEI_UCWK_STAT <> '300'
  AND   KK2361.GENE_ADD_DTM = (
    SELECT MAX(KK2361_GENE.GENE_ADD_DTM) AS MAX_GENE
    FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE
    WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
    AND    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
    AND    KK2361_GENE.MK_FLG = '0'))

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_007)
#--KKPRC00909
#--「割引サービス契約内訳ステータス」が「登録済」の割引サービス契約内訳の抽出を行う。
#-----------------------------------------------------------

--KEY:KK_SELECT_007

SELECT
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.GENE_ADD_DTM,
    KK2361.WRIB_SVC_DTL_CD,
    KK2361.SVC_CHRG_STAYMD
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_UCWK_STAT = '010' AND
    KK2361.GENE_ADD_DTM =
    (SELECT
         MAX(KK2361_GENE.GENE_ADD_DTM) KK2361_MAX
       FROM
         KK_T_WRISVC_KEI_UCWK KK2361_GENE
      WHERE
         KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO AND
         KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO AND
         KK2361_GENE.MK_FLG = '0')
ORDER BY KK2361.WRIB_SVC_KEI_NO

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_008)
#--・以下の条件に一致する割引サービス契約内訳情報を抽出する。
#--　＜抽出条件＞
#--　　１．割引サービス契約番号が該当の割引サービス契約番号に一致
#--　　２．世代登録年月日時分秒が最大
#-----------------------------------------------------------

--KEY:KK_SELECT_008

SELECT 
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.GENE_ADD_DTM,
    KK2361.WRIB_SVC_KEI_UCWK_STAT,
    KK2361.WRIB_SVC_DTL_CD,
    KK2361.WRIB_APLY_CNT,
    KK2361.SVC_CHRG_STAYMD,
    KK2361.SVC_CHRG_ENDYMD,
    KK2361.CHRG_STA_YMD_HOSEI_UM,
    KK2361.FIRST_WRIB_APLY_YMD,
    KK2361.ADD_DTM,
    KK2361.ADD_OPEACNT,
    KK2361.UPD_DTM,
    KK2361.UPD_OPEACNT,
    KK2361.DEL_DTM,
    KK2361.DEL_OPEACNT,
    KK2361.MK_FLG
FROM 
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ?
AND KK2361.GENE_ADD_DTM =
         (SELECT MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_MAX
          FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE
          WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
          AND    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
          AND    KK2361_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_009) 
#--・以下の条件に一致する割引サービス契約内訳情報を抽出する。
#--　＜抽出条件＞
#--　　１．割引サービス契約内訳ステータスが"登録済"
#--　　２．割引サービス詳細の適用数判別コードが「月数」あるいは「日数」
#--　　３．世代登録年月日時分秒が最大
#--　　４．サービス課金終了年月日　＜　運用日
#--使用ジョブ
#--EO2D90110J0_割引サービス契約期間満了抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_009

SELECT
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
    JOIN KK_M_WRIB_SVC_DTL KK2371
    ON KK2371.WRIB_SVC_DTL_CD = KK2361.WRIB_SVC_DTL_CD
        AND KK2371.RSV_APLY_YMD || KK2371.GENE_ADD_DTM =
        (SELECT
            MAX(KK2371_GENE.RSV_APLY_YMD || KK2371_GENE.GENE_ADD_DTM) AS KK2371_MAX
        FROM
            KK_M_WRIB_SVC_DTL KK2371_GENE
        WHERE
            KK2371_GENE.WRIB_SVC_DTL_CD = KK2361.WRIB_SVC_DTL_CD
            AND KK2371_GENE.RSV_APLY_YMD <= ?
            AND KK2371_GENE.MK_FLG = '0')
        AND KK2371.WRIB_SVC_DTL_TSTAYMD <= ?
        AND KK2371.WRIB_SVC_DTL_TENDYMD >= ?
        AND KK2371.APLY_CNT_HAMBET_CD IN ('3', '4')
WHERE
    KK2361.WRIB_SVC_KEI_UCWK_STAT = '010'
    AND KK2361.GENE_ADD_DTM =
         (SELECT MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_MAX
          FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE
          WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
          AND    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
          AND    KK2361_GENE.MK_FLG = '0')
    AND ? > DECODE(TRIM(KK2361.SVC_CHRG_ENDYMD), null, '20991231', KK2361.SVC_CHRG_ENDYMD)
ORDER BY
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_010)
#--割引サービス契約内訳のカレントレコードを取得する。
#--使用ジョブ
#--EO2D90210J0_割引サービス契約期間満了登録
#-----------------------------------------------------------

--KEY:KK_SELECT_010

SELECT
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.GENE_ADD_DTM,
    KK2361.WRIB_SVC_KEI_UCWK_STAT,
    KK2361.WRIB_SVC_DTL_CD,
    KK2361.WRIB_APLY_CNT,
    KK2361.SVC_CHRG_STAYMD,
    KK2361.SVC_CHRG_ENDYMD,
    KK2361.CHRG_STA_YMD_HOSEI_UM,
    KK2361.FIRST_WRIB_APLY_YMD,
    KK2361.ADD_DTM,
    KK2361.ADD_OPEACNT,
    KK2361.UPD_DTM,
    KK2361.UPD_OPEACNT,
    KK2361.DEL_DTM,
    KK2361.DEL_OPEACNT,
    KK2361.MK_FLG,
    KK2361.ADD_UNYO_YMD,
    KK2361.ADD_TRN_ID,
    KK2361.UPD_UNYO_YMD,
    KK2361.UPD_TRN_ID,
    KK2361.DEL_UNYO_YMD,
    KK2361.DEL_TRN_ID
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ?
    AND KK2361.WRIB_SVC_KEI_UCWK_NO = ?
    AND KK2361.GENE_ADD_DTM =
         (SELECT MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_MAX
          FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE
          WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
          AND    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
          AND    KK2361_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_011)
#--長期継続利用割引終了継続設定、高速割適用満了登録
#--割引サービス契約番号、割引サービス契約内訳番号に紐づくカレントを排他ロックで取得
#-----------------------------------------------------------

--KEY:KK_SELECT_011

SELECT KK2361.GENE_ADD_DTM
FROM   KK_T_WRISVC_KEI_UCWK KK2361
WHERE  KK2361.WRIB_SVC_KEI_NO = ?
AND    KK2361.WRIB_SVC_KEI_UCWK_NO = ?
AND    KK2361.GENE_ADD_DTM = (
  SELECT MAX(KK2361_GENE.GENE_ADD_DTM) AS MAX_GENE
  FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE
  WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
  AND    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
  AND    KK2361_GENE.MK_FLG = '0')
FOR UPDATE

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_013)
#--KKPRC00914
#--割引サービス契約番号から履歴あり更新対象のデータを抽出する。
#-----------------------------------------------------------

--KEY:KK_SELECT_013

SELECT 
    KK2361.WRIB_SVC_KEI_NO, 
    KK2361.WRIB_SVC_KEI_UCWK_NO, 
    KK2361.GENE_ADD_DTM, 
    KK2361.WRIB_SVC_KEI_UCWK_STAT, 
    KK2361.WRIB_SVC_DTL_CD, 
    KK2361.WRIB_APLY_CNT, 
    KK2361.SVC_CHRG_STAYMD, 
    KK2361.SVC_CHRG_ENDYMD, 
    KK2361.CHRG_STA_YMD_HOSEI_UM, 
    KK2361.FIRST_WRIB_APLY_YMD 
FROM
KK_T_WRISVC_KEI_UCWK KK2361
WHERE
 KK2361.WRIB_SVC_KEI_NO = ? AND
 KK2361.GENE_ADD_DTM = (SELECT MAX(KK2361_GENE.GENE_ADD_DTM) KK2361_MAX
                        FROM KK_T_WRISVC_KEI_UCWK KK2361_GENE
                        WHERE KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
                              AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
                              AND KK2361_GENE.MK_FLG = '0') 
ORDER BY WRIB_SVC_KEI_UCWK_NO

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_014)
#--割引サービス契約内訳ステータスが「登録済み」の割引サービス契約内訳番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_014

SELECT
  KK2361.WRIB_SVC_KEI_UCWK_NO
 FROM
  KK_T_WRISVC_KEI_UCWK KK2361
 WHERE
  KK2361.WRIB_SVC_KEI_NO = ?
 AND
  KK2361.GENE_ADD_DTM =
         (SELECT MAX(KK2361_GENE_01.GENE_ADD_DTM) AS KK2361_MAX
          FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE_01
          WHERE  KK2361_GENE_01.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
          AND    KK2361_GENE_01.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
          AND    KK2361_GENE_01.MK_FLG = '0')
 AND
  WRIB_SVC_KEI_UCWK_STAT = '010'

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_016)
#--KKPRC00920
#--登録済でサービス課金開始日に値のない最新レコード情報
#-----------------------------------------------------------

--KEY:KK_SELECT_016

SELECT
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.GENE_ADD_DTM,
    KK2361.WRIB_SVC_KEI_UCWK_STAT,
    KK2361.WRIB_SVC_DTL_CD
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ? AND
    KK2361.WRIB_SVC_KEI_UCWK_NO = ? AND
    KK2361.GENE_ADD_DTM =  
    (SELECT 
         MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_MAX 
     FROM 
         KK_T_WRISVC_KEI_UCWK KK2361_GENE
     WHERE 
         KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO AND
         KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO AND
         KK2361_GENE.WRIB_SVC_KEI_UCWK_STAT = '010' AND
         KK2361_GENE.SVC_CHRG_STAYMD IS NULL AND
         KK2361_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_017)
#--割引サービス契約番号を条件にして、割引サービス契約内訳番号の一覧を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_017

SELECT
    KK2361.WRIB_SVC_KEI_UCWK_NO
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ?
AND KK2361.GENE_ADD_DTM = (
        SELECT
            MAX(GENE.GENE_ADD_DTM)
        FROM
            KK_T_WRISVC_KEI_UCWK GENE
        WHERE
            GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
        AND GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
        AND GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_UPDATE_001)
#--長期継続利用割引終了更新
#--終了処理更新
#-----------------------------------------------------------

--KEY:KK_UPDATE_001

UPDATE  KK_T_WRISVC_KEI_UCWK KK2361
   SET  KK2361.WRIB_SVC_KEI_UCWK_STAT = '300',
        KK2361.SVC_CHRG_ENDYMD = ?,
        KK2361.UPD_DTM = ?,
        KK2361.UPD_OPEACNT = ?,
        KK2361.UPD_UNYO_YMD = ?,
        KK2361.UPD_TRN_ID = ?
 WHERE  KK2361.WRIB_SVC_KEI_NO = ?
   AND  KK2361.WRIB_SVC_KEI_UCWK_NO = ?
   AND  KK2361.GENE_ADD_DTM = (
     SELECT  MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_GENE_MAX
       FROM  KK_T_WRISVC_KEI_UCWK KK2361_GENE
      WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
        AND  KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
        AND  KK2361_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_UPDATE_002)
#--割引サービス契約番号・割引サービス契約内訳番号を条件にカレントのサービス課金終了年月日を更新する。
#-----------------------------------------------------------

--KEY:KK_UPDATE_002

UPDATE
    KK_T_WRISVC_KEI_UCWK KK2361
SET
    KK2361.SVC_CHRG_ENDYMD = ?,
    KK2361.UPD_DTM = 'UPD_DTM',
    KK2361.UPD_UNYO_YMD = 'UPD_UNYO',
    KK2361.UPD_TRN_ID = 'UPD_TRN',
    KK2361.UPD_OPEACNT = 'UPD_OPE'
WHERE
    KK2361.WRIB_SVC_KEI_NO = ?
AND KK2361.WRIB_SVC_KEI_UCWK_NO = ?
AND KK2361.GENE_ADD_DTM = (
      SELECT
          MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_GENE_MAX
      FROM
          KK_T_WRISVC_KEI_UCWK KK2361_GENE
      WHERE
          KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
      AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
      AND KK2361_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KV_SELECT_001)
#--KVPRC00201:(9)割引サービス契約情報取得の続き
#-----------------------------------------------------------

--KEY:KV_SELECT_001

SELECT
    KK2361.WRIB_SVC_DTL_CD,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.SVC_CHRG_STAYMD,
    KK2361.SVC_CHRG_ENDYMD,
    KK2361.WRIB_APLY_CNT
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ? AND
    KK2361.MK_FLG = '0' AND
    KK2361.GENE_ADD_DTM = (SELECT
                                MAX(KK2361a.GENE_ADD_DTM)
                            FROM
                                KK_T_WRISVC_KEI_UCWK KK2361a
                            WHERE
                                KK2361a.WRIB_SVC_KEI_NO = ? AND
                                KK2361a.MK_FLG = '0'
                            )
ORDER BY
    KK2361.WRIB_SVC_KEI_UCWK_NO

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_015)
#--スマートリンクプレミアムオプションの割引サービス契約内訳を特定します
#-----------------------------------------------------------

--KEY:KK_SELECT_015

SELECT
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.WRIB_SVC_DTL_CD
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
   JOIN KK_M_WRIB_SVC_DTL KK2371
   ON KK2371.WRIB_SVC_DTL_CD = KK2361.WRIB_SVC_DTL_CD
   AND KK2371.RSV_APLY_YMD || KK2371.GENE_ADD_DTM =
       (SELECT
            MAX(KK2371_GENE.RSV_APLY_YMD || KK2371_GENE.GENE_ADD_DTM) AS KK2371_MAX
        FROM
            KK_M_WRIB_SVC_DTL KK2371_GENE
        WHERE
            KK2371_GENE.WRIB_SVC_DTL_CD = KK2361.WRIB_SVC_DTL_CD AND
            KK2371_GENE.RSV_APLY_YMD <= ? AND
            KK2371_GENE.MK_FLG = '0'
        ) AND KK2371.WRIB_SVC_DTL_TSTAYMD <= ? 
          AND KK2371.WRIB_SVC_DTL_TENDYMD >= ? 
          AND KK2371.OP_SVC_CD = ?
WHERE
    KK2361.GENE_ADD_DTM = (SELECT
                                MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_MAX
                            FROM
                                KK_T_WRISVC_KEI_UCWK KK2361_GENE
                            WHERE
                                KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO AND
                                KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO AND
                                KK2361_GENE.MK_FLG = '0'
                            ) AND
    KK2361.WRIB_SVC_KEI_NO = ?
ORDER BY
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO

#-


#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_INSERT_001)
#--プラン変更時ＣＰ復活登録
#-----------------------------------------------------------

--KEY:KK_INSERT_001

INSERT INTO KK_T_WRISVC_KEI_UCWK
    SELECT
        KK2361.WRIB_SVC_KEI_NO,
        KK2361.WRIB_SVC_KEI_UCWK_NO,
        ?,
        KK2361.WRIB_SVC_KEI_UCWK_STAT,
        KK2361.WRIB_SVC_DTL_CD,
        KK2361.WRIB_APLY_CNT,
        KK2361.SVC_CHRG_STAYMD,
        KK2361.SVC_CHRG_ENDYMD,
        KK2361.CHRG_STA_YMD_HOSEI_UM,
        KK2361.FIRST_WRIB_APLY_YMD,
        'ADD_DTM',
        'ADD_OPE',
        'UPD_DTM',
        'UPD_OPE',
        KK2361.DEL_DTM,
        KK2361.DEL_OPEACNT,
        ?,
        'ADD_UNYO',
        'ADD_TRN',
        'UPD_UNYO',
        'UPD_TRN',
        KK2361.DEL_UNYO_YMD,
        KK2361.DEL_TRN_ID
    FROM
        KK_T_WRISVC_KEI_UCWK KK2361
    WHERE
        KK2361.WRIB_SVC_KEI_NO = ? 
    AND
        KK2361.WRIB_SVC_KEI_UCWK_NO = ? 
    AND
        KK2361.GENE_ADD_DTM = 
            (SELECT 
                 MAX(KK2361_GENE.GENE_ADD_DTM) KK2361_MAX
             FROM 
                 KK_T_WRISVC_KEI_UCWK KK2361_GENE
             WHERE 
                 KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
             AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
             AND KK2361_GENE.WRIB_SVC_KEI_UCWK_STAT = ?
             AND KK2361_GENE.MK_FLG = '0')  

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_INSERT_002)
#--割引サービス契約(KK_T_WRISVC_KEI_UCWK-KK_INSERT_002)
#--UIA17_バッチ機能仕様書(KKPRC51001_解約後割引サービス契約情報補正)
#-----------------------------------------------------------

--KEY:KK_INSERT_002

INSERT INTO KK_T_WRISVC_KEI_UCWK
    SELECT
        ?,
        ?,
        ?,
        ?,
        KK2361.WRIB_SVC_DTL_CD,
        KK2361.WRIB_APLY_CNT,
        KK2361.SVC_CHRG_STAYMD,
        ?,
        KK2361.CHRG_STA_YMD_HOSEI_UM,
        KK2361.FIRST_WRIB_APLY_YMD,
        'ADD_DTM',
        'ADD_OPE',
        'UPD_DTM',
        'UPD_OPE',
        KK2361.DEL_DTM,
        KK2361.DEL_OPEACNT,
        ?,
        'ADD_UNYO',
        'ADD_TRN',
        'UPD_UNYO',
        'UPD_TRN',
        KK2361.DEL_UNYO_YMD,
        KK2361.DEL_TRN_ID
    FROM
        KK_T_WRISVC_KEI_UCWK KK2361
    WHERE
        KK2361.WRIB_SVC_KEI_NO = ? 
    AND KK2361.WRIB_SVC_KEI_UCWK_NO = ? 
    AND KK2361.GENE_ADD_DTM = 
            (SELECT 
                 MAX(KK2361_GENE.GENE_ADD_DTM) KK2361_MAX
             FROM 
                 KK_T_WRISVC_KEI_UCWK KK2361_GENE
             WHERE 
                 KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
             AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
             AND KK2361_GENE.MK_FLG = '0')  

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_018)
#--KKPRC00920
#-----------------------------------------------------------

--KEY:KK_SELECT_018

SELECT KK2361.WRIB_SVC_KEI_NO,
       KK2361.WRIB_SVC_KEI_UCWK_NO,
       KK2361.GENE_ADD_DTM,
       KK2361.WRIB_APLY_CNT,
       KK2371.APLY_CNT_HAMBET_CD
  FROM KK_T_WRISVC_KEI_UCWK KK2361
 INNER JOIN (
    SELECT KK2371.WRIB_SVC_DTL_CD, KK2371.APLY_CNT_HAMBET_CD
      FROM KK_M_WRIB_SVC_DTL KK2371
     WHERE(KK2371.WRIB_SVC_DTL_CD, KK2371.GENE_ADD_DTM)
                   = (SELECT KK2371.WRIB_SVC_DTL_CD, MAX(KK2371.GENE_ADD_DTM)
                        FROM KK_M_WRIB_SVC_DTL KK2371_GENE
                       WHERE KK2371_GENE.WRIB_SVC_DTL_CD = KK2371.WRIB_SVC_DTL_CD
                         AND KK2371_GENE.RSV_APLY_YMD <= ?
                         AND KK2371_GENE.MK_FLG='0'
                       GROUP BY KK2371_GENE.WRIB_SVC_DTL_CD)
 ) KK2371
 ON KK2361.WRIB_SVC_DTL_CD = KK2371.WRIB_SVC_DTL_CD
 WHERE KK2361.WRIB_SVC_KEI_NO = ?
   AND KK2361.WRIB_SVC_KEI_UCWK_STAT <> '910'
   AND KK2361.SVC_CHRG_STAYMD IS NOT NULL
   AND (KK2361.WRIB_SVC_KEI_NO, KK2361.WRIB_SVC_KEI_UCWK_NO, KK2361.GENE_ADD_DTM)
              = (SELECT KK2361_GENE.WRIB_SVC_KEI_NO, KK2361_GENE.WRIB_SVC_KEI_UCWK_NO, MAX(KK2361_GENE.GENE_ADD_DTM)
                   FROM KK_T_WRISVC_KEI_UCWK KK2361_GENE
                  WHERE KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
                    AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
                    AND KK2361_GENE.MK_FLG='0'
                  GROUP BY KK2361_GENE.WRIB_SVC_KEI_NO, KK2361_GENE.WRIB_SVC_KEI_UCWK_NO)

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_019)
#--KKPRC42706
#-----------------------------------------------------------

--KEY:KK_SELECT_019

SELECT
	MAX(KK2361.WRIB_APLY_CNT) AS WRIB_APLY_CNT
FROM
	KK_T_WRISVC_KEI_UCWK KK2361
WHERE
	KK2361.WRIB_SVC_KEI_NO = ?
AND
	KK2361.GENE_ADD_DTM = 
	(SELECT 
		MAX(KK2361_GENE.GENE_ADD_DTM) KK2361_MAX
	 FROM 
		KK_T_WRISVC_KEI_UCWK KK2361_GENE
	 WHERE 
		KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
	 AND 
		KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
	 AND
		KK2361_GENE.MK_FLG = '0')

#-


#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_020)
#--JBSbatKKKapKeiWaribSetで機器提供サービス契約番号から
#--引継元の割引サービス内訳.割引適用回数の最大値を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_020

SELECT 
     MAX(WRIB_UCWK.WRIB_APLY_CNT) AS WRIB_APLY_CNT
FROM
 (SELECT 
     KK2361.WRIB_APLY_CNT,
     KK2361.WRIB_SVC_KEI_NO
  FROM
     KK_T_WRISVC_KEI_UCWK KK2361
  WHERE
  	 KK2361.WRIB_SVC_DTL_CD = ?
  AND
     KK2361.GENE_ADD_DTM = 
     (SELECT 
         MAX(KK2361_GENE.GENE_ADD_DTM) KK2361_MAX
      FROM 
         KK_T_WRISVC_KEI_UCWK KK2361_GENE
      WHERE 
         KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
      AND 
         KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
      AND
         KK2361_GENE.MK_FLG = '0'))WRIB_UCWK
INNER JOIN 
   (SELECT 
       WRIB_INFO.WRIB_SVC_KEI_NO 
    FROM 
      (SELECT
          KK0451.WRIB_SVC_KEI_NO,
          KK0451.RSV_APLY_YMD,
          KK0451.GENE_ADD_DTM,
          ROW_NUMBER() OVER (ORDER BY KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM DESC) AS LINE
       FROM 
          KK_T_KAP_KEI KK2541
       INNER JOIN
          KK_T_WRISVC_TG_KEI KK0481
       ON
          KK2541.KKTK_SVC_KEI_NO = KK0481.KKTK_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
          KK0481.WRIB_SVC_KEI_NO = 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')
       INNER JOIN
          KK_M_WRIB_SVC KK0841
       ON
          KK0451.WRIB_SVC_CD = 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.WRIB_TYPE_CD = '42'
           AND
              KK0841_GENE.MK_FLG = '0')
       WHERE
         KK2541.KAP_KEI_NO = 
         (SELECT
         	KK2541_AFT.HKTGI_MOTO_KAP_KEI_NO
          FROM
          	KK_T_KAP_KEI KK2541_AFT
          WHERE
          	KK2541_AFT.KKTK_SVC_KEI_NO = ?
          AND
          	KK2541_AFT.RSV_APLY_YMD || KK2541_AFT.GENE_ADD_DTM =
          	(SELECT  
             MAX(KK2541_AFT_GENE.RSV_APLY_YMD || KK2541_AFT_GENE.GENE_ADD_DTM) AS KAP_KEI_MAX
			 FROM
			 	KK_T_KAP_KEI KK2541_AFT_GENE
	         WHERE 
				KK2541_AFT_GENE.KAP_KEI_NO = KK2541_AFT.KAP_KEI_NO
	         AND
				KK2541_AFT_GENE.RSV_APLY_YMD <= ?
	         AND
				KK2541_AFT_GENE.RSV_APLY_CD = '2'
	         AND
				KK2541_AFT_GENE.MK_FLG = '0'
			)
         )
       AND 
         KK2541.RSV_APLY_YMD || KK2541.GENE_ADD_DTM =
         (SELECT  
             MAX(KK2541_GENE.RSV_APLY_YMD || KK2541_GENE.GENE_ADD_DTM) AS KAP_KEI_MAX
          FROM    
             KK_T_KAP_KEI KK2541_GENE
          WHERE 
             KK2541_GENE.KAP_KEI_NO = KK2541.KAP_KEI_NO
          AND
             KK2541_GENE.RSV_APLY_YMD <= ?
          AND
             KK2541_GENE.RSV_APLY_CD = '2'
          AND
             KK2541_GENE.MK_FLG = '0'))WRIB_INFO
    WHERE 
      LINE = 1)WRIB_MAX
ON WRIB_MAX.WRIB_SVC_KEI_NO = WRIB_UCWK.WRIB_SVC_KEI_NO

#-


#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_021)
#--KKPRC36101_キャンペーン割引適用情報反映
#-----------------------------------------------------------

--KEY:KK_SELECT_021

SELECT /* KK_T_WRISVC_KEI_UCWK.SQL KK_SELECT_021 */
       KK2361.WRIB_APLY_CNT
 FROM  KK_T_WRISVC_KEI_UCWK KK2361
WHERE  KK2361.WRIB_SVC_KEI_NO = ?
  AND  KK2361.WRIB_SVC_DTL_CD = ?
  AND  KK2361.MK_FLG = '0'
  AND  KK2361.GENE_ADD_DTM =
       (
       SELECT MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_MAX
          FROM KK_T_WRISVC_KEI_UCWK KK2361_GENE
         WHERE KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
           AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
           AND KK2361_GENE.MK_FLG = '0'
       )

#-


#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_INSERT_003)
#--KKPRC61601
#--割引サービス契約内訳の解約を行う
#-----------------------------------------------------------

--KEY:KK_INSERT_003

INSERT INTO KK_T_WRISVC_KEI_UCWK
    SELECT
        KK2361.WRIB_SVC_KEI_NO,
        KK2361.WRIB_SVC_KEI_UCWK_NO,
        ?,
        ?,
        KK2361.WRIB_SVC_DTL_CD,
        KK2361.WRIB_APLY_CNT,
        KK2361.SVC_CHRG_STAYMD,
        ?,
        KK2361.CHRG_STA_YMD_HOSEI_UM,
        KK2361.FIRST_WRIB_APLY_YMD,
        'ADD_DTM',
        'ADD_OPE',
        'UPD_DTM',
        'UPD_OPE',
        KK2361.DEL_DTM,
        KK2361.DEL_OPEACNT,
        KK2361.MK_FLG,
        'ADD_UNYO',
        'ADD_TRN',
        'UPD_UNYO',
        'UPD_TRN',
        KK2361.DEL_UNYO_YMD,
        KK2361.DEL_TRN_ID
    FROM
        KK_T_WRISVC_KEI_UCWK KK2361
    WHERE
        KK2361.WRIB_SVC_KEI_NO = ?
    AND KK2361.WRIB_SVC_DTL_CD = ?
    AND KK2361.GENE_ADD_DTM = 
            (SELECT
                 MAX(KK2361_GENE.GENE_ADD_DTM)
             FROM
                 KK_T_WRISVC_KEI_UCWK KK2361_GENE
             WHERE
                 KK2361.WRIB_SVC_KEI_NO = KK2361_GENE.WRIB_SVC_KEI_NO
             AND KK2361.WRIB_SVC_KEI_UCWK_NO = KK2361_GENE.WRIB_SVC_KEI_UCWK_NO
             AND KK2361_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_022)
#--KKPRC61801
#--登録済で最大の割引適用回数を抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_022

SELECT/*KK_T_WRISVC_KEI_UCWK.SQL KK_SELECT_022*/
    MAX(KK2361.WRIB_APLY_CNT) AS WRIB_APLY_CNT
FROM 
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ?
AND KK2361.WRIB_SVC_KEI_UCWK_STAT = '010' 
AND (KK2361.WRIB_SVC_KEI_NO, KK2361.WRIB_SVC_KEI_UCWK_NO, KK2361.GENE_ADD_DTM) =
         (SELECT KK2361_GENE.WRIB_SVC_KEI_NO,
                      KK2361_GENE.WRIB_SVC_KEI_UCWK_NO,
                      MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_MAX
          FROM   KK_T_WRISVC_KEI_UCWK KK2361_GENE
          WHERE  KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
          AND    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
          AND    KK2361_GENE.MK_FLG = '0'
          GROUP BY KK2361_GENE.WRIB_SVC_KEI_NO,
                    KK2361_GENE.WRIB_SVC_KEI_UCWK_NO)

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_023)
#--KKPRC02204
#--割引サービス契約番号からカレントレコードを抽出する。
#-----------------------------------------------------------

--KEY:KK_SELECT_023

SELECT 
    KK2361.WRIB_SVC_KEI_NO, 
    KK2361.WRIB_SVC_KEI_UCWK_NO, 
    KK2361.GENE_ADD_DTM 
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ? AND
    KK2361.GENE_ADD_DTM = (SELECT MAX(KK2361_GENE.GENE_ADD_DTM) KK2361_MAX
                             FROM KK_T_WRISVC_KEI_UCWK KK2361_GENE
                            WHERE KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
                              AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
                              AND KK2361_GENE.MK_FLG = '0') 

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-KK_SELECT_024)
#--KKPRC64201
#--割引サービス契約番号に紐づく解約対象の割引サービス契約内訳情報を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_024

SELECT
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.GENE_ADD_DTM
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ?
AND KK2361.WRIB_SVC_KEI_UCWK_STAT = '010'
AND (KK2361.WRIB_SVC_KEI_NO, KK2361.WRIB_SVC_KEI_UCWK_NO, KK2361.GENE_ADD_DTM) =
    (SELECT
         KK2361_GENE.WRIB_SVC_KEI_NO, KK2361_GENE.WRIB_SVC_KEI_UCWK_NO, MAX(KK2361_GENE.GENE_ADD_DTM) AS KK2361_GENE_MAX
     FROM
         KK_T_WRISVC_KEI_UCWK KK2361_GENE
     WHERE
         KK2361_GENE.WRIB_SVC_KEI_NO = KK2361.WRIB_SVC_KEI_NO
     AND KK2361_GENE.WRIB_SVC_KEI_UCWK_NO = KK2361.WRIB_SVC_KEI_UCWK_NO
     AND KK2361_GENE.MK_FLG = '0'
     GROUP BY KK2361_GENE.WRIB_SVC_KEI_NO, KK2361_GENE.WRIB_SVC_KEI_UCWK_NO)

#-

#-----------------------------------------------------------
#--割引サービス契約内訳(KK_T_WRISVC_KEI_UCWK-AK_SELECT_001)
#--割引サービス契約内訳のカレントレコードを取得する。
#--使用ジョブ
#--AKPRC10602_補正対象割引情報作成
#-----------------------------------------------------------

--KEY:AK_SELECT_001

SELECT
    KK2361.WRIB_SVC_KEI_NO,
    KK2361.WRIB_SVC_KEI_UCWK_NO,
    KK2361.GENE_ADD_DTM,
    KK2361.WRIB_SVC_KEI_UCWK_STAT,
    KK2361.WRIB_SVC_DTL_CD,
    KK2361.WRIB_APLY_CNT,
    KK2361.SVC_CHRG_STAYMD,
    KK2361.SVC_CHRG_ENDYMD,
    KK2361.CHRG_STA_YMD_HOSEI_UM,
    KK2361.FIRST_WRIB_APLY_YMD,
    KK2361.ADD_DTM,
    KK2361.ADD_OPEACNT,
    KK2361.UPD_DTM,
    KK2361.UPD_OPEACNT,
    KK2361.DEL_DTM,
    KK2361.DEL_OPEACNT,
    KK2361.MK_FLG,
    KK2361.ADD_UNYO_YMD,
    KK2361.ADD_TRN_ID,
    KK2361.UPD_UNYO_YMD,
    KK2361.UPD_TRN_ID,
    KK2361.DEL_UNYO_YMD,
    KK2361.DEL_TRN_ID
FROM
    KK_T_WRISVC_KEI_UCWK KK2361
WHERE
    KK2361.WRIB_SVC_KEI_NO = ?
    AND    KK2361.MK_FLG = '0'
ORDER BY
    KK2361.WRIB_SVC_KEI_UCWK_NO DESC,
    KK2361.GENE_ADD_DTM DESC

#-