#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-AC_SELECT_001)
#--UIA17_ACPRC04811_コンテンツ通話料上限超過情報出力.xlsx 参照
#-----------------------------------------------------------

--KEY:AC_SELECT_001

SELECT
     CK0201.MLAD
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.SYSID = ?
AND  
    CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
         (
          SELECT
               MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
          FROM
               CK_T_RRKS CK0201_GENE
          WHERE
               CK0201_GENE.SYSID              = CK0201.SYSID
          AND
               CK0201_GENE.RRK_WAY_CD = ?
          AND
               CK0201_GENE.RRKS_BUNRUI_CD = ?
          AND 
               CK0201_GENE.RSV_APLY_YMD <= ?
          AND 
               CK0201_GENE.RSV_APLY_CD = '2'
          AND
               CK0201_GENE.MK_FLG = '0'
          )
#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-AC_SELECT_002)
#--【ACPRC04812_コンテンツ通話閾値超過チェックデータ出力】
#--メールアドレスを取得する。
#-----------------------------------------------------------

--KEY:AC_SELECT_002

SELECT
     CK0201.MLAD
FROM
     CK_T_RRKS CK0201
WHERE
     CK0201.SYSID = ?
AND  CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
         (
          SELECT
               MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
          FROM
               CK_T_RRKS CK0201_GENE
          WHERE
               CK0201_GENE.SYSID              = CK0201.SYSID
          AND  CK0201_GENE.RRK_WAY_CD         = ?
          AND  CK0201_GENE.RRKS_SBT_CD        = ?
          AND  CK0201_GENE.RRKS_BUNRUI_CD     = ?
          AND  CK0201_GENE.RSV_APLY_YMD      <= ?
          AND  CK0201_GENE.RSV_APLY_CD        = '2'
          AND  CK0201_GENE.MK_FLG             = '0'
          )

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CH_SELECT_001)
#--契約者連絡先電話番号、勤務先電話番号を取得する
#-----------------------------------------------------------

--KEY:CH_SELECT_001

SELECT
        CK0201.SYSID
      , CK0201.TELNO
      , CK0201.RRKS_SBT_CD
FROM
        CK_T_RRKS CK0201
        INNER JOIN
                (SELECT
                        CK0201_GENE.SYSID
                      , MAX(CK0201_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM
                      , CK0201_GENE.RRKS_SBT_CD
                 FROM
                        CK_T_RRKS CK0201_GENE
                 WHERE
                        CK0201_GENE.RSV_APLY_CD  =  '2'
                   AND  CK0201_GENE.RSV_APLY_YMD <=  ?
                   AND  CK0201_GENE.MK_FLG       =  '0'
                 GROUP BY
                        CK0201_GENE.SYSID
                      , CK0201_GENE.RRKS_SBT_CD
                ) CK0201_GENE
        ON  CK0201.SYSID        = CK0201_GENE.SYSID
       AND  CK0201.GENE_ADD_DTM = CK0201_GENE.MAX_GENE_DTM
WHERE
       CK0201.SYSID = ?
  AND  CK0201.RRK_WAY_CD='003'
  AND  CK0201.RRKS_SBT_CD IN (2, 3, 5)
  AND (CK0201.RRKS_CD IS NULL
  OR  CK0201.RRKS_CD ='01')
ORDER BY RRKS_CD DESC
       , CK0201.RRKS_SBT_CD

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CK_SELECT_001)
#--不良顧客チェックファイルのSYSIDに紐づく、電話番号を取得する。
#-----------------------------------------------------------

--KEY:CK_SELECT_001

SELECT
  CK0201.TELNO
FROM
  CK_T_RRKS CK0201
WHERE
  CK0201.SYSID = ?
  AND CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
    (SELECT
      MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
    FROM
      CK_T_RRKS CK0201_GENE
    WHERE
      CK0201_GENE.SYSID = CK0201.SYSID
      AND CK0201_GENE.RSV_APLY_YMD <= ?
      AND CK0201_GENE.RSV_APLY_CD = '2'
      AND CK0201_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CK_SELECT_002)
#--CKPRC02601
#--メール受信設定管理対象の取得。
#-----------------------------------------------------------

--KEY:CK_SELECT_002

SELECT
    CK0201.SYSID,
    CK0201.MLAD,
    CK0011.GENE_ADD_DTM,
    CK0011.UPD_DTM
FROM
    CK_T_RRKS CK0201
INNER JOIN
    CK_T_CUST CK0011
ON
    CK0011.SYSID = CK0201.SYSID
    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')
WHERE
    CK0201.RRK_WAY_CD = '001' AND
    CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM = 
        (SELECT
             MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
         FROM
             CK_T_RRKS CK0201_GENE
         WHERE
             CK0201_GENE.RRKS_NO = CK0201.RRKS_NO AND
             CK0201_GENE.RSV_APLY_YMD <= ? AND
             CK0201_GENE.RSV_APLY_CD = '2' AND
             CK0201_GENE.MK_FLG = '0'
         ) AND
    NOT EXISTS (SELECT 1
                FROM
                    CK_T_OPTINM_RCV_SET CK0141
                WHERE
                    CK0141.OPTINM_NO = ? AND
                    CK0141.SYSID = CK0201.SYSID AND
                    CK0141.MLAD = CK0201.MLAD AND
                    CK0141.MK_FLG = '0'
                ) AND
    (CK0011.CUST_STAT = '100' OR
    (CK0011.CUST_STAT = '010' AND CK0011.CUST_NTAIKAI_CD = '0')) AND
    CK0201.MK_FLG = '0' AND
    CK0011.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CK_SELECT_006)
#--KKPRC68101
#--指定したSYSIDと連絡先種別コードに紐付く連絡先情報を取得する
#-----------------------------------------------------------

--KEY:CK_SELECT_006

SELECT
    CK0201.RRKS_NO,
    CK0201.GENE_ADD_DTM AS CK0201_GENE_ADD_DTM,
    CK0201.SYSID,
    CK0201.RRKS_CD,
    CK0201.RRKS_SBT_CD,
    CK0201.TELNO,
    CK0201.UPD_UNYO_YMD,
    CK0201.RSV_APLY_YMD
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.RSV_APLY_YMD ||CK0201.GENE_ADD_DTM =
         (SELECT MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
          FROM   CK_T_RRKS CK0201_GENE
          WHERE  CK0201_GENE.SYSID = CK0201.SYSID
          AND    CK0201_GENE.RRKS_SBT_CD = CK0201.RRKS_SBT_CD
          AND    CK0201_GENE.RRK_WAY_CD = CK0201.RRK_WAY_CD
          AND    CK0201_GENE.RSV_APLY_YMD <= ?
          AND    CK0201_GENE.RSV_APLY_CD = '2'
          AND    CK0201_GENE.MK_FLG = '0')
AND CK0201.SYSID = ?
AND CK0201.RRKS_SBT_CD = ?
AND CK0201.RRK_WAY_CD = '003'

#-

#-----------------------------------------------------------
#--連絡蜈・€・€(CK_T_RRKS-KK_SELECT_001)
#--連絡先種別コードが「携帯」となる釥€・€・€｣絡先電話番号を取得す繧・€・€
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
RRKS.TELNO
FROM
CK_T_RRKS RRKS
WHERE
RRKS.SYSID = ?
AND
RRKS_SBT_CD = '2'
AND RRKS.RSV_APLY_YMD || RRKS.GENE_ADD_DTM =
         (SELECT MAX(CK_T_RRKS_RCNT.RSV_APLY_YMD || CK_T_RRKS_RCNT.GENE_ADD_DTM) AS RRKS_MAX
         FROM     CK_T_RRKS CK_T_RRKS_RCNT
         WHERE    CK_T_RRKS_RCNT.RRKS_NO = RRKS.RRKS_NO
         AND      CK_T_RRKS_RCNT.RSV_APLY_YMD <= ?
         AND      CK_T_RRKS_RCNT.RSV_APLY_CD = '2'
         AND      CK_T_RRKS_RCNT.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_002)
#--KKPRC02403
#--ＫＣＮ連携契約情報ファイル１のSYSIDに紐づく、お客様個人の連絡先情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT 
  CK0201.TELNO,
  CK0021.OFFC_NM,
  CK0021.OFFC_TELNO,
  CK0021.OFFC_NISNNO
FROM
  CK_T_RRKS CK0201  
  INNER JOIN CK_T_CUST_KOJIN CK0021 ON
     CK0201.SYSID = CK0021.SYSID AND
   CK0021.GENE_ADD_DTM =
        (SELECT MAX(CK0021_GENE.GENE_ADD_DTM) AS CK0021_MAX   FROM
           CK_T_CUST_KOJIN CK0021_GENE
        WHERE
           CK0021_GENE.SYSID = CK0021.SYSID
           AND CK0021_GENE.RSV_APLY_CD = '2'
            AND CK0021_GENE.MK_FLG = '0')
WHERE
 CK0201.SYSID = ?
     AND CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
         (SELECT MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
          FROM   
          CK_T_RRKS CK0201_GENE
          WHERE
            CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
          AND    CK0201_GENE.RSV_APLY_YMD <= ?
          AND    CK0201_GENE.RSV_APLY_CD = '2'
          AND    CK0201.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_003)
#--KKPRC14701にて使用
#--連絡先情報取得。
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
 CK0201.RRKS_NO,
 CK0201.GENE_ADD_DTM,
 CK0201.SYSID,
 CK0201.RRKS_CD,
 CK0201.RRK_WAY_CD,
 CK0201.RRKS_SBT_CD,
 CK0201.RRKS_BUNRUI_CD,
 CK0201.MLAD,
 CK0201.TELNO,
 CK0201.NISNNO,
 CK0201.FAX_NO,
 CK0201.MLAD_CHGE_UK_YMD,
 CK0201.MLAD_CHGE_YMD,
 CK0201.RSV_APLY_YMD,
 CK0201.RSV_CL_YMD,
 CK0201.RSV_APLY_CD,
 CK0201.ADD_DTM,
 CK0201.ADD_OPEACNT,
 CK0201.UPD_DTM,
 CK0201.UPD_OPEACNT,
 CK0201.DEL_DTM,
 CK0201.DEL_OPEACNT,
 CK0201.MK_FLG,
 CK0201.ADD_UNYO_YMD,
 CK0201.ADD_TRN_ID,
 CK0201.UPD_UNYO_YMD,
 CK0201.UPD_TRN_ID,
 CK0201.DEL_UNYO_YMD,
 CK0201.DEL_TRN_ID
FROM
 CK_T_RRKS CK0201
WHERE
 CK0201.SYSID = ? AND 
 CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM = 
    (SELECT
      MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
    FROM
      CK_T_RRKS CK0201_GENE
    WHERE
      CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
      AND CK0201_GENE.RSV_APLY_YMD <= ?
      AND CK0201_GENE.RSV_APLY_CD = '2'
      AND CK0201_GENE.MK_FLG = '0') 
ORDER BY CK0201.RRKS_NO


#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_004)
#--指定したSYSIDの連絡先電話番号取得
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
 CK0201.TELNO
FROM
 CK_T_RRKS CK0201
WHERE
 CK0201.SYSID = ?
AND CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
    (SELECT  MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
     FROM    CK_T_RRKS CK0201_GENE
     WHERE   CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
     AND     CK0201_GENE.RSV_APLY_YMD <= ?
     AND     CK0201_GENE.RSV_APLY_CD = '2'
     AND     CK0201_GENE.MK_FLG = '0' )

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_005)
#--KKPRC00915
#--紹介者情報登録対象出力
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT
  CK0201.TELNO
FROM
  CK_T_RRKS CK0201
WHERE
  CK0201.SYSID = ?
  AND CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
    (SELECT
      MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
    FROM
      CK_T_RRKS CK0201_GENE
    WHERE
      CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
      AND CK0201_GENE.RSV_APLY_YMD <= ?
      AND CK0201_GENE.RSV_APLY_CD = '2'
      AND CK0201_GENE.MK_FLG = '0')
  AND CK0201.RRK_WAY_CD = '003'
  AND CK0201.RRKS_SBT_CD = '5'
  AND CK0201.RRKS_BUNRUI_CD = '0'

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_006)
#--対象日付に登録・更新のあった連絡先を抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_006

SELECT
    CK0201.TELNO,
    CK0201.MLAD,
    KK0081.SYSID,
    KK0081.SVC_KEI_NO
FROM
    CK_T_RRKS CK0201
    INNER JOIN CK_T_CUST CK0011
    ON CK0011.SYSID = CK0201.SYSID
    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')
    INNER JOIN KK_T_SVC_KEI KK0081
    ON KK0081.SYSID = CK0201.SYSID
    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')
WHERE
    CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
        (SELECT  MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
         FROM    CK_T_RRKS CK0201_GENE
         WHERE   CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
         AND     CK0201_GENE.RSV_APLY_YMD <= ?
         AND     CK0201_GENE.RSV_APLY_CD = '2'
         AND     CK0201_GENE.MK_FLG = '0' )
AND (CK0201.ADD_UNYO_YMD = ? OR CK0201.UPD_UNYO_YMD = ?)
AND KK0081.PRC_GRP_CD IN ('02', '03', '04', '10', '11', '12', '13')
AND CK0011.KEISHA_TYPE_CD = '1'

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_007)
#--指定したSYSIDと連絡先種別コードに紐付く連絡先情報を取得する
#--使用ジョブ
#--EO21Y0310J0_CTI電話番号情報(問合せ客)抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_007

SELECT
    CK0201.TELNO
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM =
         (SELECT MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
          FROM   CK_T_RRKS CK0201_GENE
          WHERE  CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
          AND    CK0201_GENE.RSV_APLY_YMD <= ?
          AND    CK0201_GENE.RSV_APLY_CD = '2'
          AND    CK0201_GENE.MK_FLG = '0')
AND CK0201.SYSID = ?
AND CK0201.RRKS_SBT_CD = ?
AND CK0201.RRK_WAY_CD = '003'

#-

#----------------------CK_LCS START-------------

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CK_DELETE_901)
#--SYSIDをキーに物理削除する
#-----------------------------------------------------------

--KEY:CK_DELETE_901

DELETE CK_T_RRKS
 WHERE SYSID = ?

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CK_SELECT_901)
#--変更中メールアドレスクリア対象会員を抽出する。
#-----------------------------------------------------------

--KEY:CK_SELECT_901

SELECT      /*+ LEADING(Q) USE_NL(A) */
            Q.RRKS_NO
           ,Q.GENE_ADD_DTM
           ,Q.SYSID
           ,(SELECT MAX(B.UPD_DTM) FROM CK_T_CUST B WHERE B.SYSID = Q.SYSID) UPD_DTM
           ,Q.MLAD_CHGE_UK_YMD
           ,Q.MLAD
           ,NULL IDO_RSV_NO
           ,NULL PARAMETER_4
           ,NULL PARAMETER_21
      FROM CK_T_RRKS Q
INNER JOIN CK_T_CUST A
        ON A.SYSID = Q.SYSID
       AND A.CUST_STAT = '010'
       AND A.CUST_NTAIKAI_CD = '0'
       AND A.RSV_APLY_YMD || A.GENE_ADD_DTM =
         (SELECT MAX(SUB_A.RSV_APLY_YMD || SUB_A.GENE_ADD_DTM) AS SUB_A_MAX
            FROM CK_T_CUST SUB_A
           WHERE A.SYSID = SUB_A.SYSID
             AND SUB_A.RSV_APLY_CD = '2'
             AND SUB_A.RSV_APLY_YMD <= ?
             AND SUB_A.MK_FLG = '0')
 WHERE Q.MLAD_CHGE_UK_YMD > TO_CHAR(ADD_MONTHS(TO_DATE( ? ,'YYYYMMDD') - ?, -1) , 'YYYYMMDD')
   AND Q.MLAD_CHGE_UK_YMD < TO_CHAR(TO_DATE( ? ,'YYYYMMDD') - ? , 'YYYYMMDD')
   AND Q.RRK_WAY_CD = '001'
   AND Q.RSV_APLY_CD = '1'
   AND Q.RRKS_BUNRUI_CD = '1'
   AND Q.GENE_ADD_DTM =
       (SELECT MAX(SUB_Q.GENE_ADD_DTM) AS SUB_Q_MAX
          FROM CK_T_RRKS SUB_Q
         WHERE SUB_Q.RRKS_NO = Q.RRKS_NO
           AND SUB_Q.MK_FLG = '0'
       )
UNION
SELECT
        NULL RRKS_NO
       ,NULL GENE_ADD_DTM
       ,KK1681.SYSID
       ,NULL UPD_DTM
       ,NULL MLAD_CHGE_UK_YMD
       ,NULL MLAD
       ,KK1681.IDO_RSV_NO
       ,KK1681.PARAMETER_4
       ,KK1681.PARAMETER_21
FROM KK_T_IDO_RSV KK1681
 WHERE KK1681.IDO_DIV = '00089'
   AND KK1681.IDO_RSV_DTL_CD = '031'
   AND KK1681.IDO_RSV_STAT_CD = '04'
   AND KK1681.ADD_UNYO_YMD > TO_CHAR(ADD_MONTHS(TO_DATE( ? ,'YYYYMMDD') - ?, -1) , 'YYYYMMDD')
   AND KK1681.ADD_UNYO_YMD < TO_CHAR(TO_DATE( ? ,'YYYYMMDD') - ? , 'YYYYMMDD')
#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CK_UPDATE_901)
#--変更手続中メールアドレスを
#--予約取消状態にする。
#-----------------------------------------------------------

--KEY:CK_UPDATE_901

UPDATE CK_T_RRKS
   SET RSV_APLY_CD = '3',
       RSV_CL_YMD = ?,
       UPD_DTM = ?,
       UPD_OPEACNT = 'UPD_OPE',
       UPD_UNYO_YMD = 'UPD_UNYO',
       UPD_TRN_ID = 'UPD_TRN'
 WHERE RRKS_NO = ?
   AND GENE_ADD_DTM = ?

#-

#----------------------CK_LCS END---------------

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CR_SELECT_001)
#--SYSIDとメールアドレスで件数を取得する。
#--・問合せ顧客紐付変更
#-----------------------------------------------------------

--KEY:CR_SELECT_001

SELECT
  COUNT(*) CNT
FROM
  CK_T_RRKS RRKS
WHERE
  RRKS.SYSID = ?
  AND RRKS.MLAD = ?
  AND RRKS.RSV_APLY_YMD || RRKS.GENE_ADD_DTM =
      (
        SELECT
          MAX(RRKS_GENE.RSV_APLY_YMD || RRKS_GENE.GENE_ADD_DTM) AS RRKS_MAX
        FROM
          CK_T_RRKS RRKS_GENE
        WHERE
          RRKS_GENE.RRKS_NO = RRKS.RRKS_NO
          AND RRKS_GENE.RSV_APLY_YMD <= ?
          AND RRKS_GENE.RSV_APLY_CD = '2'
          AND RRKS_GENE.MK_FLG = '0'
      )

#-


#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CK_SELECT_007)
#--指定した連絡先番号に紐付く連絡先情報を取得する
#--取得条件①：最新世代の予約適用コードが「予約取消」であること
#--取得条件②：メールアドレスが有効なeoメールアドレスでないこと
#-----------------------------------------------------------

--KEY:CK_SELECT_007

SELECT
    CK0201.RRKS_NO,
    CK0201.GENE_ADD_DTM AS CK0201_GENE_ADD_DTM,
    CK0201.SYSID,
    CK0201.RRKS_CD,
    CK0201.RRKS_SBT_CD,
    CK0201.MLAD,
    CK0201.UPD_UNYO_YMD,
    CK0201.RSV_APLY_YMD
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.RRKS_NO = ?
AND CK0201.RSV_APLY_CD = '3'
AND CK0201.GENE_ADD_DTM =
    (SELECT MAX(CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
     FROM   CK_T_RRKS CK0201_GENE
     WHERE  CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
     AND    CK0201_GENE.MK_FLG = '0')
AND NOT EXISTS (
    SELECT 1
    FROM   KK_T_OP_SVC_KEI KK0351
    INNER JOIN KK_T_OPSVKEI_ISP KK0361
    ON     KK0361.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO
    AND    KK0361.GENE_ADD_DTM = KK0351.GENE_ADD_DTM
    WHERE  KK0361.MLAD = CK0201.MLAD
    AND    KK0351.OP_SVC_KEI_STAT IN ('020', '030', '100', '210', '220')
    AND    (KK0351.SHOSA_DSL_FIN_CD IS NULL OR KK0351.SHOSA_DSL_FIN_CD <> '2')
    AND    (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) =
               (SELECT KK0351_GENE.OP_SVC_KEI_NO, MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_GENE_MAX
                FROM   KK_T_OP_SVC_KEI KK0351_GENE
                WHERE  KK0351_GENE.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO
                AND    KK0351_GENE.RSV_APLY_YMD <= ?
                AND    KK0351_GENE.RSV_APLY_CD = '2'
                AND    KK0351_GENE.MK_FLG = '0'
                GROUP BY KK0351_GENE.OP_SVC_KEI_NO)
)

#-  


#-----------------------------------------------------------
#-- 連絡先(CK_T_RRKS-CK_SELECT_008)
#-- CKPRC03501_お客様情報変更連携抽出（IBUKI）
#-- ※過去世代も取得する必要があるため、カレントの検索方法が通常と異なります。
#-----------------------------------------------------------

--KEY:CK_SELECT_008

SELECT 
    CK0201.RRKS_NO, 
    CK0201.GENE_ADD_DTM, 
    CK0201.DEL_UNYO_YMD
FROM CK_T_RRKS CK0201 
WHERE CK0201.SYSID = ? 
AND CK0201.RRK_WAY_CD = ?
AND CK0201.RRKS_SBT_CD = ?
AND (CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM) = ( 
    SELECT 
        MAX(GENE.RSV_APLY_YMD || GENE.GENE_ADD_DTM) 
    FROM CK_T_RRKS GENE 
    WHERE GENE.RRKS_NO = CK0201.RRKS_NO 
    AND GENE.RSV_APLY_YMD <= ? 
    AND GENE.GENE_ADD_DTM <= ? 
    AND GENE.RSV_APLY_CD = '2' 
    AND (GENE.MK_FLG = '0' OR GENE.DEL_UNYO_YMD > ?) 
) 
ORDER BY CK0201.RSV_APLY_YMD DESC, CK0201.GENE_ADD_DTM DESC 

#-  

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CK_UPDATE_902)
#--変更手続中メールアドレスを
#--予約取消状態にする。
#-----------------------------------------------------------

--KEY:CK_UPDATE_902

UPDATE CK_T_RRKS
   SET RSV_APLY_CD = '3',
       RSV_CL_YMD = ?,
       UPD_DTM = ?,
       UPD_OPEACNT = 'UPD_OPE',
       UPD_UNYO_YMD = 'UPD_UNYO',
       UPD_TRN_ID = 'UPD_TRN'
 WHERE RRKS_NO = ?
   AND MLAD = ?
   AND RSV_APLY_YMD = ?
   AND RSV_APLY_CD = '2'

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-CH_SELECT_002)
#--【CHPRC05201_利用停止予告メール送信者抽出】
#--【CHPRC05301_強制解約予告メール送信者抽出】
#--サービス契約番号に対応するSYSIDの連絡先を取得する
#-----------------------------------------------------------

--KEY:CH_SELECT_002

SELECT
    CK0201.MLAD
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.SYSID = 
        (
         SELECT KK0081.SYSID
         FROM KK_T_SVC_KEI KK0081
         WHERE (KK0081.SVC_KEI_NO,KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) =
             (SELECT
                  KK0081_GENE.SVC_KEI_NO,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'
              GROUP BY KK0081_GENE.SVC_KEI_NO
          )
          AND KK0081.SVC_KEI_NO = ?
        )
AND
    (CK0201.RRKS_NO,CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM) =
        (
         SELECT
              CK0201_GENE.RRKS_NO,MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_MAX
         FROM
              CK_T_RRKS CK0201_GENE
         WHERE
              CK0201_GENE.RRKS_NO            = CK0201.RRKS_NO
         AND  CK0201_GENE.RSV_APLY_YMD      <= ?
         AND  CK0201_GENE.RSV_APLY_CD        = '2'
         AND  CK0201_GENE.MK_FLG             = '0'
         GROUP BY CK0201_GENE.RRKS_NO
        )
AND 
    CK0201.RRK_WAY_CD = '001'
AND
    CK0201.MLAD IS NOT NULL

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_KK_SELECT_008)
#--【内容】
#--   連絡先のメールアドレスを取得。
#--   （世代管理、予約管理を考慮）
#--【条件】
#--   SYSID
#--   予約適用年月日
#--【抽出対象】
#--   連絡先のメールアドレス
#--   連絡先番号の降順
#-----------------------------------------------------------

--KEY:KK_SELECT_008

SELECT
    CK0201.RRKS_NO
  , CK0201.MLAD
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.SYSID = ?
AND CK0201.RRK_WAY_CD = '001'
AND CK0201.RRKS_BUNRUI_CD = '0'
AND CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM = 
     (SELECT
        MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM)
      FROM
        CK_T_RRKS CK0201_GENE
      WHERE
          CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
      AND CK0201_GENE.RSV_APLY_YMD <= ?
      AND CK0201_GENE.RSV_APLY_CD = '2'
      AND CK0201_GENE.MK_FLG = '0') 
ORDER BY CK0201.RRKS_NO DESC

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_009)
#--【内容】
#--   指定したSYSIDに紐付くSMS送信対象でない連絡先のメールアドレスを取得。
#--   （世代管理、予約管理、無効フラグを考慮）
#-----------------------------------------------------------

--KEY:KK_SELECT_009

SELECT
   CK0201.MLAD
FROM
   CK_T_RRKS CK0201
WHERE
   CK0201.SYSID = ?
 AND
   CK0201.RRKS_BUNRUI_CD = '1'
 AND
   CK0201.RRK_WAY_CD = '001'
 AND
  (CK0201.RRKS_NO, CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM) =
    (
    SELECT
     CK0201.RRKS_NO, MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_GENE_MAX
    FROM
     CK_T_RRKS CK0201_GENE
    WHERE
     CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
     AND CK0201_GENE.RSV_APLY_YMD <= ?
     AND CK0201_GENE.RSV_APLY_CD IN ('1', '2')
     AND CK0201_GENE.MK_FLG = '0'
    GROUP BY
     CK0201_GENE.RRKS_NO
    )
 AND CK0201.RSV_APLY_CD='2'
 
#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_010)
#--【内容】
#--   指定したSYSIDに紐付くコンテンツ系連絡先のメールアドレスを取得。
#--   （予約確定世代）
#-----------------------------------------------------------

--KEY:KK_SELECT_010

SELECT
   CK0201.RRKS_NO
  ,CK0201.GENE_ADD_DTM
  ,RSV_APLY_CD
  ,MLAD
FROM
  CK_T_RRKS CK0201
WHERE
  CK0201.SYSID = ?
AND
  CK0201.RRK_WAY_CD = '001'
AND
  CK0201.RRKS_BUNRUI_CD = '1'
AND
  CK0201.RSV_APLY_CD in ('1', '2')
AND
  CK0201.MK_FLG = '0'
ORDER BY 
  CK0201.RRKS_NO asc
 ,CK0201.GENE_ADD_DTM asc

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_011)
#--【内容】
#--   連絡先のメールアドレスを取得（コンテンツ系連絡先）。
#--   （世代管理、予約管理を考慮）
#--【条件】
#--   SYSID
#--   予約適用年月日
#--【抽出対象】
#--   連絡先のメールアドレス
#--   連絡先番号の降順
#-----------------------------------------------------------

--KEY:KK_SELECT_011

SELECT
    CK0201.RRKS_NO
  , CK0201.MLAD
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.SYSID = ?
AND CK0201.RRK_WAY_CD = '001'
AND CK0201.RRKS_BUNRUI_CD = '1'
AND CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM = 
     (SELECT
        MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM)
      FROM
        CK_T_RRKS CK0201_GENE
      WHERE
          CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
      AND CK0201_GENE.RSV_APLY_YMD <= ?
      AND CK0201_GENE.RSV_APLY_CD = '2'
      AND CK0201_GENE.MK_FLG = '0') 
ORDER BY CK0201.RRKS_NO DESC

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_012)
#--【内容】
#--   サービス契約番号を元に、契約メールアドレスを取得。
#--【条件】
#--   サービス契約番号
#--   予約適用年月日
#-----------------------------------------------------------
#-

--KEY:KK_SELECT_012

SELECT
    CK0201.MLAD
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.SYSID = 
        (
         SELECT KK0081.SYSID
         FROM KK_T_SVC_KEI KK0081
         WHERE (KK0081.SVC_KEI_NO,KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) =
             (SELECT
                  KK0081_GENE.SVC_KEI_NO,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'
              GROUP BY KK0081_GENE.SVC_KEI_NO
          )
          AND KK0081.SVC_KEI_NO = ?
        )
    AND CK0201.RRK_WAY_CD = '001' 
    AND CK0201.RRKS_BUNRUI_CD = '1'
    AND
    (CK0201.RRKS_NO, CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM) =
        (
        SELECT
            CK0201_GENE.RRKS_NO, MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_GENE_MAX
        FROM
            CK_T_RRKS CK0201_GENE
        WHERE
            CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
        AND CK0201_GENE.RSV_APLY_CD = '2'
        AND CK0201_GENE.RSV_APLY_YMD  <= ?
        AND CK0201_GENE.MK_FLG = '0'
        GROUP BY
            CK0201_GENE.RRKS_NO
        )
    AND NOT EXISTS (
       SELECT 1
         FROM CK_T_RRKS Q
         WHERE Q.SYSID = CK0201.SYSID
         AND Q.RRK_WAY_CD = '001'
         AND Q.RSV_APLY_CD = '1'
         AND Q.RRKS_BUNRUI_CD = '1'
         AND Q.GENE_ADD_DTM =
           (SELECT MAX(J.GENE_ADD_DTM) AS J_MAX
              FROM CK_T_RRKS J
              WHERE J.RRKS_NO = Q.RRKS_NO
              AND J.MK_FLG = '0'
            )
         AND Q.MLAD = CK0201.MLAD
    )
#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_013)
#--【内容】
#--   サービス契約番号を元に、契約メールアドレス、お客様名を取得。
#--【条件】
#--   サービス契約番号
#--   予約適用年月日
#-----------------------------------------------------------
#-

--KEY:KK_SELECT_013

SELECT
    CK0201.MLAD
  , CK0011.CUST_NM
FROM
    CK_T_RRKS CK0201
    INNER JOIN CK_T_CUST CK0011
    ON CK0011.SYSID = CK0201.SYSID
    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'
         )
WHERE
    CK0201.SYSID = 
        (
         SELECT KK0081.SYSID
         FROM KK_T_SVC_KEI KK0081
         WHERE (KK0081.SVC_KEI_NO,KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) =
             (SELECT
                  KK0081_GENE.SVC_KEI_NO,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'
              GROUP BY KK0081_GENE.SVC_KEI_NO
          )
          AND KK0081.SVC_KEI_NO = ?
        )
    AND CK0201.RRK_WAY_CD = '001' 
    AND CK0201.RRKS_BUNRUI_CD = '1'
    AND
    (CK0201.RRKS_NO, CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM) =
        (
        SELECT
            CK0201_GENE.RRKS_NO, MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_GENE_MAX
        FROM
            CK_T_RRKS CK0201_GENE
        WHERE
            CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
        AND CK0201_GENE.RSV_APLY_CD = '2'
        AND CK0201_GENE.RSV_APLY_YMD  <= ?
        AND CK0201_GENE.MK_FLG = '0'
        GROUP BY
            CK0201_GENE.RRKS_NO
        )
    AND NOT EXISTS (
       SELECT 1
         FROM CK_T_RRKS Q
         WHERE Q.SYSID = CK0201.SYSID
         AND Q.RRK_WAY_CD = '001'
         AND Q.RSV_APLY_CD = '1'
         AND Q.RRKS_BUNRUI_CD = '1'
         AND Q.GENE_ADD_DTM =
           (SELECT MAX(J.GENE_ADD_DTM) AS J_MAX
              FROM CK_T_RRKS J
              WHERE J.RRKS_NO = Q.RRKS_NO
              AND J.MK_FLG = '0'
            )
         AND Q.MLAD = CK0201.MLAD
    )

#-

#-----------------------------------------------------------
#--連絡先(CK_T_RRKS-KK_SELECT_014)
#--KKPRC68101
#--【内容】
#--   仮メールアドレスを対象外としたメールアドレスを取得。
#--【条件】
#--   SYSID
#--   予約適用年月日
#-----------------------------------------------------------
#-

--KEY:KK_SELECT_014

SELECT
    CK0201.MLAD
FROM
    CK_T_RRKS CK0201
WHERE
    CK0201.SYSID = ?
    AND CK0201.RRK_WAY_CD = '001' 
    AND
    (CK0201.RRKS_NO, CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM) =
        (
        SELECT
            CK0201_GENE.RRKS_NO, MAX(CK0201_GENE.RSV_APLY_YMD || CK0201_GENE.GENE_ADD_DTM) AS CK0201_GENE_MAX
        FROM
            CK_T_RRKS CK0201_GENE
        WHERE
            CK0201_GENE.RRKS_NO = CK0201.RRKS_NO
        AND CK0201_GENE.RSV_APLY_CD = '2'
        AND CK0201_GENE.RSV_APLY_YMD  <= ?
        AND CK0201_GENE.MK_FLG = '0'
        GROUP BY
            CK0201_GENE.RRKS_NO
        )
   AND NOT EXISTS (
     SELECT 1
       FROM CK_T_RRKS Q
      WHERE Q.SYSID = CK0201.SYSID
        AND Q.RRK_WAY_CD = '001'
        AND Q.RSV_APLY_CD = '1'
        AND Q.RRKS_BUNRUI_CD = '1'
        AND Q.GENE_ADD_DTM =
              (SELECT MAX(J.GENE_ADD_DTM) AS J_MAX
                 FROM CK_T_RRKS J
                WHERE J.RRKS_NO = Q.RRKS_NO
                  AND J.MK_FLG = '0'
              )
        AND Q.MLAD = CK0201.MLAD
     )

#-