#-----------------------------------------------------------
#--オプトインメール(CK_M_OPTINM-CK_SELECT_001)
#--CKPRC02601
#--メール受信設定管理対象の取得。
#-----------------------------------------------------------

--KEY:CK_SELECT_001

SELECT
    CK0131.OPTINM_NO
FROM
    CK_M_OPTINM CK0131
WHERE
    CK0131.OPTINM_TSTAYMD BETWEEN TO_CHAR(TO_DATE(?) - 2, 'yyyymmdd') AND TO_CHAR(TO_DATE(?) + 2,'yyyymmdd') AND
    CK0131.RSV_APLY_YMD || CK0131.GENE_ADD_DTM = 
        (SELECT
            MAX(CK0131_GENE.RSV_APLY_YMD || CK0131_GENE.GENE_ADD_DTM) AS CK0131_MAX
        FROM
            CK_M_OPTINM CK0131_GENE
        WHERE
            CK0131_GENE.OPTINM_NO = CK0131.OPTINM_NO
            AND CK0131_GENE.RSV_APLY_YMD <= ?
            AND CK0131_GENE.MK_FLG = '0')
    AND CK0131.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--オプトインメール(CK_M_OPTINM-CN_SELECT_001)
#--CNPRC03301_コンテンツ利用規約自動同意登録
#--メール受信設定登録するオプトインメールのリストを取得する。
#-----------------------------------------------------------

--KEY:CN_SELECT_001

SELECT
    A.OPTINM_NO
FROM  CK_M_OPTINM A
WHERE A.OPTINM_TSTAYMD <= ?
   AND A.OPTINM_TENDYMD >= ?
   AND A.KOKAI_CD = '1'
   AND A.RSV_APLY_YMD || A.GENE_ADD_DTM =
      (
       SELECT MAX(SUB_B.RSV_APLY_YMD || SUB_B.GENE_ADD_DTM) AS SUB_B_MAX
         FROM CK_M_OPTINM SUB_B
        WHERE SUB_B.OPTINM_NO = A.OPTINM_NO
          AND SUB_B.RSV_APLY_YMD <= ?
          AND SUB_B.MK_FLG = '0'
      )
ORDER BY A.DSP_JUN

#-