#-----------------------------------------------------------
#--オプトインメール受信設定(CK_T_OPTINM_RCV_SET-CK_SELECT_001)
#--CKPRC00901
#--お客様回復情報ファイルのSYSIDに紐づくオプトインメール受信設定情報のキー項目の取得を行う。
#-----------------------------------------------------------

--KEY:CK_SELECT_001

SELECT
  CK0141.OPTINM_NO,
  CK0141.SYSID,
  CK0141.MLAD,
  CK0141.GENE_ADD_DTM
FROM
  CK_T_OPTINM_RCV_SET CK0141
WHERE
 CK0141.SYSID = ? AND
 CK0141.GENE_ADD_DTM =
      (SELECT MAX(CK0141_GENE.GENE_ADD_DTM) AS CK0141_MAX
       FROM   CK_T_OPTINM_RCV_SET CK0141_GENE
       WHERE  CK0141_GENE.OPTINM_NO = CK0141.OPTINM_NO
       AND    CK0141_GENE.SYSID = CK0141.SYSID
       AND    CK0141_GENE.MLAD = CK0141.MLAD
       AND    CK0141_GENE.MK_FLG = '0') AND
 CK0141.MK_FLG = '0'

#-

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

#-----------------------------------------------------------
#--オプトインメール受信設定(CK_T_OPTINM_RCV_SET-CK_DELETE_901)
#--SYSIDをキーにレコードを物理削除する
#-----------------------------------------------------------

--KEY:CK_DELETE_901

DELETE CK_T_OPTINM_RCV_SET
 WHERE SYSID = ?

#-

#-----------------------------------------------------------
#--オプトインメール受信設定(CK_T_OPTINM_RCV_SET-CK_SELECT_901)
#--SYSIDをキーにオプトインメール受信設定ステータスが有効の最新データを取得する。
#-----------------------------------------------------------

--KEY:CK_SELECT_901

SELECT  SETTE.OPTINM_NO
       ,SETTE.SYSID
       ,SETTE.MLAD
       ,SETTE.GENE_ADD_DTM
       ,SETTE.OPTINM_RCV_SETTE_STAT
       ,SETTE.RCV_DIV
       ,SETTE.RCV_SETTE_YMD
       ,SETTE.RCV_RLS_YMD
       ,SETTE.OPTINM_RCV_SETTE_MK_YMD
       ,SETTE.OPTINM_RCV_SETTE_KAIHK_YMD
       ,SETTE.ADD_DTM
       ,SETTE.ADD_OPEACNT
       ,SETTE.UPD_DTM
       ,SETTE.UPD_OPEACNT
       ,SETTE.DEL_DTM
       ,SETTE.DEL_OPEACNT
       ,SETTE.MK_FLG
  FROM CK_T_OPTINM_RCV_SET SETTE
 WHERE SETTE.SYSID = ?
   AND SETTE.OPTINM_RCV_SETTE_STAT = '010'
   AND SETTE.GENE_ADD_DTM = 
       (
        SELECT MAX(SUB_SETTE.GENE_ADD_DTM)
          FROM CK_T_OPTINM_RCV_SET SUB_SETTE
         WHERE SUB_SETTE.OPTINM_NO = SETTE.OPTINM_NO
           AND SUB_SETTE.SYSID = SETTE.SYSID
           AND SUB_SETTE.MLAD = SETTE.MLAD
           AND SUB_SETTE.MK_FLG = '0'
       )

#-

#-----------------------------------------------------------
#--オプトインメール受信設定(CK_T_OPTINM_RCV_SET-CK_SELECT_903)
#--オプトインメール番号とメールアドレスをキーとして、有効なメールアドレスをもつレコードを抽出する。
#-----------------------------------------------------------

--KEY:CK_SELECT_903

SELECT
   SUB_ALL.OPTINM_NO
  ,SUB_ALL.SYSID
  ,SUB_ALL.MLAD
  ,SUB_ALL.GENE_ADD_DTM
  ,SUB_ALL.OPTINM_RCV_SETTE_STAT
  ,SUB_ALL.RCV_DIV
  ,SUB_ALL.RCV_SETTE_YMD
  ,SUB_ALL.RCV_RLS_YMD
  ,SUB_ALL.OPTINM_RCV_SETTE_MK_YMD
  ,SUB_ALL.OPTINM_RCV_SETTE_KAIHK_YMD
  ,SUB_ALL.ADD_DTM
  ,SUB_ALL.ADD_OPEACNT
  ,SUB_ALL.UPD_DTM
  ,SUB_ALL.UPD_OPEACNT
  ,SUB_ALL.DEL_DTM
  ,SUB_ALL.DEL_OPEACNT
  ,SUB_ALL.MK_FLG
  ,SUB_ALL.ADD_UNYO_YMD
  ,SUB_ALL.ADD_TRN_ID
  ,SUB_ALL.UPD_UNYO_YMD
  ,SUB_ALL.UPD_TRN_ID
  ,SUB_ALL.DEL_UNYO_YMD
  ,SUB_ALL.DEL_TRN_ID
FROM
  (
    SELECT
       A.OPTINM_NO
      ,A.SYSID
      ,A.MLAD
      ,A.GENE_ADD_DTM
      ,A.OPTINM_RCV_SETTE_STAT
      ,A.RCV_DIV
      ,A.RCV_SETTE_YMD
      ,A.RCV_RLS_YMD
      ,A.OPTINM_RCV_SETTE_MK_YMD
      ,A.OPTINM_RCV_SETTE_KAIHK_YMD
      ,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
      ,A.DEL_UNYO_YMD
      ,A.DEL_TRN_ID
      ,C.RRKS_NO
    FROM
      CK_T_OPTINM_RCV_SET A
       INNER JOIN CK_T_RRKS C
                  ON  A.SYSID = C.SYSID
                  AND A.MLAD = C.MLAD
     INNER JOIN 
       (SELECT OPTINM_RCV_SET.OPTINM_NO,
               OPTINM_RCV_SET.SYSID,
               OPTINM_RCV_SET.MLAD,
            MAX(OPTINM_RCV_SET.UPD_DTM) AS LAST_UPD_DTM
       FROM CK_T_OPTINM_RCV_SET OPTINM_RCV_SET
      WHERE OPTINM_RCV_SET.MLAD = ?
      GROUP BY OPTINM_RCV_SET.OPTINM_NO,OPTINM_RCV_SET.SYSID,OPTINM_RCV_SET.MLAD
     )OPTINM_RCV_SET_LAST
       ON A.OPTINM_NO = OPTINM_RCV_SET_LAST.OPTINM_NO
      AND A.SYSID = OPTINM_RCV_SET_LAST.SYSID
      AND A.MLAD  = OPTINM_RCV_SET_LAST.MLAD
    WHERE
          A.MLAD = ?
      AND A.OPTINM_NO IN (?)
      AND A.OPTINM_RCV_SETTE_STAT = '010'
      AND A.RCV_DIV = '1'
      AND A.GENE_ADD_DTM =
      (
          SELECT MAX(SUB_A.GENE_ADD_DTM) AS SUB_A_MAX
            FROM CK_T_OPTINM_RCV_SET SUB_A
           WHERE SUB_A.OPTINM_NO = A.OPTINM_NO
             AND SUB_A.SYSID = A.SYSID
             AND SUB_A.MLAD = A.MLAD
             AND SUB_A.MK_FLG = '0'
       )
      AND C.RRK_WAY_CD = '001'
      AND C.RRKS_BUNRUI_CD = '1'
      AND C.RSV_APLY_CD = '2'
      AND C.RSV_APLY_YMD || C.GENE_ADD_DTM =
         (
          SELECT MAX(SUB_C.RSV_APLY_YMD || SUB_C.GENE_ADD_DTM) AS SUB_C_MAX
            FROM CK_T_RRKS SUB_C
           WHERE SUB_C.RRKS_NO = C.RRKS_NO
             AND SUB_C.RSV_APLY_YMD <= ?
             AND SUB_C.RSV_APLY_CD = '2'
             AND SUB_C.MK_FLG = '0'
         )
    UNION ALL
    SELECT
       A.OPTINM_NO
      ,A.SYSID
      ,A.MLAD
      ,A.GENE_ADD_DTM
      ,A.OPTINM_RCV_SETTE_STAT
      ,A.RCV_DIV
      ,A.RCV_SETTE_YMD
      ,A.RCV_RLS_YMD
      ,A.OPTINM_RCV_SETTE_MK_YMD
      ,A.OPTINM_RCV_SETTE_KAIHK_YMD
      ,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
      ,A.DEL_UNYO_YMD
      ,A.DEL_TRN_ID
      ,'999999999999' AS RRKS_NO
    FROM
      CK_T_OPTINM_RCV_SET A
       INNER JOIN KK_T_OPSVKEI_ISP D
                  ON  A.MLAD = D.MLAD
       INNER JOIN KK_T_OP_SVC_KEI E
                  ON  D.OP_SVC_KEI_NO = E.OP_SVC_KEI_NO
                  AND A.SYSID = E.SYSID
       INNER JOIN 
       (SELECT OPTINM_RCV_SET.OPTINM_NO,
               OPTINM_RCV_SET.SYSID,
               OPTINM_RCV_SET.MLAD,
            MAX(OPTINM_RCV_SET.UPD_DTM) AS LAST_UPD_DTM
       FROM CK_T_OPTINM_RCV_SET OPTINM_RCV_SET
      WHERE OPTINM_RCV_SET.MLAD = ?
      GROUP BY OPTINM_RCV_SET.OPTINM_NO,OPTINM_RCV_SET.SYSID,OPTINM_RCV_SET.MLAD
       )OPTINM_RCV_SET_LAST
       ON A.OPTINM_NO = OPTINM_RCV_SET_LAST.OPTINM_NO
      AND A.SYSID = OPTINM_RCV_SET_LAST.SYSID
      AND A.MLAD  = OPTINM_RCV_SET_LAST.MLAD
    WHERE
          A.MLAD = ?
      AND A.OPTINM_NO IN (?)
      AND A.OPTINM_RCV_SETTE_STAT = '010'
      AND A.RCV_DIV = '1'
      AND A.GENE_ADD_DTM =
      (
          SELECT MAX(SUB_A.GENE_ADD_DTM) AS SUB_A_MAX
            FROM CK_T_OPTINM_RCV_SET SUB_A
           WHERE SUB_A.OPTINM_NO = A.OPTINM_NO
             AND SUB_A.SYSID = A.SYSID
             AND SUB_A.MLAD = A.MLAD
             AND SUB_A.MK_FLG = '0'
       )
      AND D.GENE_ADD_DTM = E.GENE_ADD_DTM
      AND E.OP_SVC_KEI_STAT IN ('020', '030', '100', '210', '220')
      AND (E.SHOSA_DSL_FIN_CD IS NULL OR E.SHOSA_DSL_FIN_CD <> '2')
      AND E.RSV_APLY_YMD || E.GENE_ADD_DTM =
         (
          SELECT MAX(SUB_E.RSV_APLY_YMD || SUB_E.GENE_ADD_DTM) AS SUB_E_MAX
            FROM KK_T_OP_SVC_KEI SUB_E
           WHERE SUB_E.OP_SVC_KEI_NO = E.OP_SVC_KEI_NO
             AND SUB_E.RSV_APLY_YMD <= ?
             AND SUB_E.RSV_APLY_CD = '2'
             AND SUB_E.MK_FLG = '0'
         )
  ) SUB_ALL
    WHERE NOT EXISTS
         (
         SELECT SUB_F.MLAD
           FROM CK_T_RRKS SUB_F
          WHERE SUB_F.MLAD = ?
            AND SUB_F.RRK_WAY_CD = '001'
            AND SUB_F.RSV_APLY_CD = '1'
            AND SUB_F.RRKS_BUNRUI_CD = '1'
            AND SUB_F.GENE_ADD_DTM =
               (
               SELECT MAX ( SUB_G.GENE_ADD_DTM ) AS SUB_G_MAX
                 FROM CK_T_RRKS SUB_G
                WHERE SUB_G.RRKS_NO = SUB_F.RRKS_NO
                  AND SUB_G.RSV_APLY_CD IN ('1','2')
                  AND SUB_G.MK_FLG = '0'
               )
            AND SUB_ALL.MLAD = SUB_F.MLAD
            AND SUB_ALL.RRKS_NO = SUB_F.RRKS_NO
         )
GROUP BY  SUB_ALL.OPTINM_NO
         ,SUB_ALL.SYSID
         ,SUB_ALL.MLAD
         ,SUB_ALL.GENE_ADD_DTM
         ,SUB_ALL.OPTINM_RCV_SETTE_STAT
         ,SUB_ALL.RCV_DIV
         ,SUB_ALL.RCV_SETTE_YMD
         ,SUB_ALL.RCV_RLS_YMD
         ,SUB_ALL.OPTINM_RCV_SETTE_MK_YMD
         ,SUB_ALL.OPTINM_RCV_SETTE_KAIHK_YMD
         ,SUB_ALL.ADD_DTM
         ,SUB_ALL.ADD_OPEACNT
         ,SUB_ALL.UPD_DTM
         ,SUB_ALL.UPD_OPEACNT
         ,SUB_ALL.DEL_DTM
         ,SUB_ALL.DEL_OPEACNT
         ,SUB_ALL.MK_FLG
         ,SUB_ALL.ADD_UNYO_YMD
         ,SUB_ALL.ADD_TRN_ID
         ,SUB_ALL.UPD_UNYO_YMD
         ,SUB_ALL.UPD_TRN_ID
         ,SUB_ALL.DEL_UNYO_YMD
         ,SUB_ALL.DEL_TRN_ID

#-

#-----------------------------------------------------------
#--オプトインメール受信設定(CK_T_OPTINM_RCV_SET-CK_SELECT_002)
#--SYSID・メールアドレスに紐づく有効なオプトインメール受信設定を取得する。
#-----------------------------------------------------------

--KEY:CK_SELECT_002

SELECT
  CK0141.OPTINM_NO,
  CK0141.SYSID,
  CK0141.MLAD,
  CK0141.GENE_ADD_DTM,
  CK0141.RCV_DIV,
  CK0141.RCV_SETTE_YMD
FROM
  CK_T_OPTINM_RCV_SET CK0141
WHERE
 CK0141.SYSID = ? AND
 CK0141.MLAD = ? AND
 CK0141.OPTINM_RCV_SETTE_STAT = '010' AND
 CK0141.GENE_ADD_DTM =
      (SELECT MAX(CK0141_GENE.GENE_ADD_DTM) AS CK0141_MAX
       FROM   CK_T_OPTINM_RCV_SET CK0141_GENE
       WHERE  CK0141_GENE.OPTINM_NO = CK0141.OPTINM_NO
       AND    CK0141_GENE.SYSID = CK0141.SYSID
       AND    CK0141_GENE.MLAD = CK0141.MLAD
       AND    CK0141_GENE.MK_FLG = '0') AND
 CK0141.MK_FLG = '0'

#-

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

#-----------------------------------------------------------
#--オプトインメール受信設定(CK_T_OPTINM_RCV_SET-CN_SELECT_001)
#--SYSID・メールアドレスに紐づく有効なオプトインメール受信設定を取得する。
#-----------------------------------------------------------

--KEY:CN_SELECT_001

SELECT
  CK0141.OPTINM_NO,
  CK0141.SYSID,
  CK0141.MLAD,
  CK0141.RCV_DIV
FROM
  CK_T_OPTINM_RCV_SET CK0141
WHERE
  CK0141.SYSID = ? AND
  CK0141.MLAD = ? AND
  CK0141.OPTINM_RCV_SETTE_STAT = '010' AND
  CK0141.GENE_ADD_DTM =
      (SELECT MAX(CK0141_GENE.GENE_ADD_DTM) AS CK0141_MAX
       FROM   CK_T_OPTINM_RCV_SET CK0141_GENE
       WHERE  CK0141_GENE.OPTINM_NO = CK0141.OPTINM_NO
       AND    CK0141_GENE.SYSID = CK0141.SYSID
       AND    CK0141_GENE.MLAD = CK0141.MLAD
       AND    CK0141_GENE.MK_FLG = '0') AND
  CK0141.MK_FLG = '0'

#-