#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-CN_SELECT_001)
#--処理対象日時点で、エイジングに移行したレコード情報を取得する
#-----------------------------------------------------------

--KEY:CN_SELECT_001

SELECT A.CONT_KEI_NO,A.SYSID,A.N_050_TELNO,A.N_050APL_SIP_USER_ID,A.N_050APL_SIP_USER_PWD
FROM CN_T_CONT_KEI_UCWK A
  INNER JOIN ( SELECT B.AGING_TG_VALUE
               FROM ZM_T_AGING B
               WHERE B.AGING_STAT = '300'
               AND B.AGING_SBT_CD = '016'
               AND B.MK_FLG = '0'
               AND B.USE_END_DTM >= ?
               AND B.USE_END_DTM <= ? ) C
  ON A.N_050_TELNO = C.AGING_TG_VALUE
WHERE A.GENE_ADD_DTM = 
  ( SELECT MAX ( D.GENE_ADD_DTM ) GENE_ADD_DTM_MAX
    FROM CN_T_CONT_KEI_UCWK D
    WHERE A.CONT_KEI_UCWK_NO = D.CONT_KEI_UCWK_NO
    AND D.MK_FLG = '0' )
AND A.SVC_END_DTM >= ?
AND A.SVC_END_DTM <= ?

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-CN_SELECT_002)
#--検索対象の電話番号のうち、解約済かつ初期設定済のレコードを取得
#-----------------------------------------------------------

--KEY:CN_SELECT_002

SELECT A.CONT_KEI_NO,A.SYSID,A.N_050_TELNO,A.N_050APL_SIP_USER_ID,A.N_050APL_SIP_USER_PWD,A.N_050APL_SETTE_STAT_CD,A.SVC_END_DTM,A.N_050_TELNO_HRADSI_SBT_CD
FROM CN_T_CONT_KEI_UCWK A
WHERE A.GENE_ADD_DTM = 
  ( SELECT MAX ( GENE.GENE_ADD_DTM ) GENE_ADD_DTM_MAX
    FROM CN_T_CONT_KEI_UCWK GENE
    WHERE A.CONT_KEI_UCWK_NO = GENE.CONT_KEI_UCWK_NO
    AND GENE.MK_FLG = '0' )
AND A.SYSID = ?
AND A.N_050_TELNO = ?
AND A.N_050APL_SIP_USER_ID = ?
AND A.CONT_KEI_UCWK_STAT = '910'
AND A.MK_FLG = '0'
ORDER BY SVC_END_DTM DESC

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-CN_SELECT_003)
#--コンテンツ契約番号と０５０番号に紐づくカレントレコードを取得
#-----------------------------------------------------------

--KEY:CN_SELECT_003

SELECT   CN0331.CONT_KEI_UCWK_STAT
FROM    (SELECT   CN0331.CONT_KEI_NO,
                  CN0331.N_050_TELNO,
                  CN0331.CONT_KEI_UCWK_STAT,
                  CN0331.GENE_ADD_DTM,
                  MAX(CN0331.GENE_ADD_DTM) OVER (PARTITION BY CN0331.CONT_KEI_NO , CN0331.N_050_TELNO) AS MAX_GENE_DTM
         FROM     CN_T_CONT_KEI_UCWK CN0331
         WHERE    CN0331.MK_FLG = '0'
         AND      CN0331.CONT_KEI_NO = ?
         AND      CN0331.N_050_TELNO = ?) CN0331
WHERE    CN0331.GENE_ADD_DTM = CN0331.MAX_GENE_DTM

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-CN_SELECT_004)
#--日付条件に該当するコンテンツ契約内訳番号の一覧を取得する。
#-----------------------------------------------------------

--KEY:CN_SELECT_004

SELECT
CN0331.CONT_KEI_UCWK_NO
FROM CN_T_CONT_KEI_UCWK CN0331
WHERE CN0331.SVC_END_DTM < ?
AND   CN0331.GENE_ADD_DTM =
        (SELECT MAX(CN0331_GENE.GENE_ADD_DTM) AS CN0331_MAX
         FROM   CN_T_CONT_KEI_UCWK CN0331_GENE
         WHERE  CN0331_GENE.CONT_KEI_UCWK_NO = CN0331.CONT_KEI_UCWK_NO
         AND    CN0331_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-CN_DELETE_001)
#--コンテンツ契約内訳番号に合致するレコードを物理削除する。
#-----------------------------------------------------------

--KEY:CN_DELETE_001

DELETE
FROM CN_T_CONT_KEI_UCWK CN0331
WHERE CN0331.CONT_KEI_UCWK_NO = ?

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-KK_SELECT_001)
#--SYSIDに紐づく050電話番号を抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
    CN0331.N_050_TELNO,
    CN0331.CONT_KEI_UCWK_STAT,
    SUBSTR(CN0331.SVC_END_DTM,1,8) AS SVC_END_DTM,
    CN0331.CONT_KEI_UCWK_NO
FROM
    CN_T_CONT_KEI_UCWK CN0331
WHERE
    CN0331.GENE_ADD_DTM =
        (SELECT MAX(CN0331_GENE.GENE_ADD_DTM) AS CN0331_MAX
         FROM   CN_T_CONT_KEI_UCWK CN0331_GENE
         WHERE  CN0331_GENE.CONT_KEI_UCWK_NO = CN0331.CONT_KEI_UCWK_NO
         AND    CN0331_GENE.MK_FLG = '0')
AND CN0331.SYSID = ?
AND (CN0331.CONT_KEI_UCWK_STAT NOT IN ('010', '910', '920')
    OR (SUBSTR(CN0331.SVC_END_DTM , 1 , 8) = ?))
ORDER BY CN0331.N_050_TELNO

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-AC_SELECT_001)
#--【ACPRC00911_コンテンツ利用呼情報集計】050番号利用停止対象の050アプリSIPユーザIDと050アプリSIPユーザIDパスワードを取得
#-----------------------------------------------------------

--KEY:AC_SELECT_001

SELECT
     CN0331.CONT_KEI_UCWK_NO,
     CN0331.CONT_KEI_NO
FROM
    CN_T_CONT_KEI_UCWK CN0331
WHERE 
    CN0331.SYSID = ?
AND
    CN0331.N_050_TELNO = ?
AND
    (CN0331.CONT_KEI_UCWK_NO, CN0331.GENE_ADD_DTM) = 
 ( SELECT 
        CN0331_GENE.CONT_KEI_UCWK_NO, MAX ( CN0331_GENE.GENE_ADD_DTM )             GENE_ADD_DTM_MAX
    FROM 
        CN_T_CONT_KEI_UCWK CN0331_GENE
    WHERE 
        CN0331.CONT_KEI_UCWK_NO =         CN0331_GENE.CONT_KEI_UCWK_NO
    AND 
        CN0331_GENE.MK_FLG = '0'
    GROUP BY 
        CN0331_GENE.CONT_KEI_UCWK_NO
 )

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-AC_SELECT_002)
#--【ACPRC00812_コンテンツ利用呼情報取込（上限通知用）】
#--課金電話番号をキーとしてコンテンツ契約情報を取得する。
#-----------------------------------------------------------

--KEY:AC_SELECT_002

SELECT
       CN0331.CONT_KEI_NO
      ,CN0331.SYSID 
      ,CN0331.SVC_STA_DTM
      ,NVL(CN0331.SVC_END_DTM, '20991231235959999') AS SVC_END_DTM
FROM  
       CN_T_CONT_KEI_UCWK CN0331
WHERE
       CN0331.N_050_TELNO      = ?
       AND CN0331.MK_FLG = '0'
ORDER BY 
       CN0331.SVC_STA_DTM DESC
      ,CN0331.GENE_ADD_DTM DESC

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-AC_SELECT_004)
#--【ACPRC04812_コンテンツ通話料閾値超過チェックデータ出力】
#--コンテンツ契約開始年月日時分秒、コンテンツ契約終了年月日時分秒、コンテンツ支払い方法コードを取得する。
#-----------------------------------------------------------

--KEY:AC_SELECT_004

SELECT
     CN0331.CONT_KEI_NO
FROM  
    CN_T_CONT_KEI_UCWK CN0331
WHERE
       CN0331.N_050_TELNO = ?
AND    CN0331.SYSID  = ?
AND    CN0331.MK_FLG = '0'
ORDER BY 
       CN0331.CONT_KEI_NO

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-AC_INSERT_001)
#--【ACPRC00911_コンテンツ利用呼情報集計】050番号利用停止対象の050アプリサービス利用状態コード、設備停止年月日を更新
#-----------------------------------------------------------

--KEY:AC_INSERT_001

INSERT INTO CN_T_CONT_KEI_UCWK(
    CONT_KEI_UCWK_NO,
    GENE_ADD_DTM,
    CONT_KEI_UCWK_STAT,
    CONT_KEI_NO,
    SYSID,
    N_050_TELNO,
    N_050APL_SIP_USER_ID,
    N_050APL_SIP_USER_PWD,
    N_050APL_SETTE_STAT_CD,
    N_050APL_HRADSI_REQ_CD,
    N_050_TELNO_HRADSI_SBT_CD,
    SVC_STA_DTM,
    SVC_END_DTM,
    N_050APL_HNIN_CFM_TELNO,
    N_050APL_HNCFM_TLN_STYMD,
    TANMT_USE_ENV_INFO,
    TANMT_USE_ENV_INFO_STYMD,
    ADD_DTM,
    ADD_OPEACNT,
    UPD_DTM,
    UPD_OPEACNT,
    DEL_DTM,
    DEL_OPEACNT,
    MK_FLG,
    ADD_UNYO_YMD,
    ADD_TRN_ID,
    UPD_UNYO_YMD,
    UPD_TRN_ID,
    DEL_UNYO_YMD,
    DEL_TRN_ID,
    N_050APL_SVC_USE_STAT_CD,
    EQUIP_STP_YMD)
SELECT 
    CONT_KEI_UCWK_NO,
    ?,
    CONT_KEI_UCWK_STAT,
    CONT_KEI_NO,
    SYSID,
    N_050_TELNO,
    N_050APL_SIP_USER_ID,
    N_050APL_SIP_USER_PWD,
    N_050APL_SETTE_STAT_CD,
    N_050APL_HRADSI_REQ_CD,
    N_050_TELNO_HRADSI_SBT_CD,
    SVC_STA_DTM,
    SVC_END_DTM,
    N_050APL_HNIN_CFM_TELNO,
    N_050APL_HNCFM_TLN_STYMD,
    TANMT_USE_ENV_INFO,
    TANMT_USE_ENV_INFO_STYMD,
    'ADD_DTM',
    'ADD_OPE',
    'UPD_DTM',
    'UPD_OPE',
    DEL_DTM,
    DEL_OPEACNT,
    MK_FLG,
    'ADD_UNYO',
    'ADD_TRN',
    'UPD_UNYO',
    'UPD_TRN',
    DEL_UNYO_YMD,
    DEL_TRN_ID,
    ?,
    ?
FROM   CN_T_CONT_KEI_UCWK CN0331
WHERE  CN0331.CONT_KEI_UCWK_NO   = ?
AND (CN0331.CONT_KEI_UCWK_NO, CN0331.GENE_ADD_DTM) =
        (SELECT CN0331_GENE.CONT_KEI_UCWK_NO, MAX(CN0331_GENE.GENE_ADD_DTM) AS CN0331_MAX
         FROM  CN_T_CONT_KEI_UCWK CN0331_GENE
         WHERE CN0331_GENE.CONT_KEI_UCWK_NO = CN0331.CONT_KEI_UCWK_NO
         AND CN0331_GENE.MK_FLG = '0'
         GROUP BY CN0331_GENE.CONT_KEI_UCWK_NO)

#-

#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-AC_SELECT_003)
#--【ACPRC00811_コンテンツ利用呼情報取込】
#--課金電話番号をキーとしてコンテンツ契約内訳情報を取得する。
#-----------------------------------------------------------

--KEY:AC_SELECT_003

SELECT
       CN0331.CONT_KEI_NO
      ,CN0331.SYSID 
      ,CN0331.SVC_STA_DTM
      ,NVL(CN0331.SVC_END_DTM, '20991231235959999') AS SVC_END_DTM
FROM  
       CN_T_CONT_KEI_UCWK CN0331
WHERE
       CN0331.N_050_TELNO      = ?
       AND CN0331.MK_FLG = '0'
ORDER BY 
       CN0331.SVC_STA_DTM DESC
      ,CN0331.GENE_ADD_DTM DESC

#-
#-----------------------------------------------------------
#--コンテンツ契約内訳(CN_T_CONT_KEI_UCWK-CN_SELECT_005)
#--050番号連携バッチ用
#-----------------------------------------------------------

--KEY:CN_SELECT_005

SELECT
    CN0331_MAX.SYSID,
    CN0331_MAX.CONT_KEI_NO,
    CN0331_MAX.N_050_TELNO,
    CN0331_MAX.CONT_KEI_UCWK_NO,
    CN0331_MAX.CONT_KEI_UCWK_STAT,
    SUBSTR(CN0331_MAX.SVC_END_DTM,1,8) AS SVC_END_DTM,
    CN0331_MAX.GENE_ADD_DTM,
    CK0011_LAST.EOID,
    CK0011_LAST.CUST_NM,
    CK0011_LAST.CUST_KANA,
    CK0011_LAST.CONT_CUST_NM,
    CK0011_LAST.CONT_CUST_KANA,
    CK0011_LAST.MEMBER_SBT_CD,
    SVKEI_LAST.SVC_KEI_NO,
    SVKEI_LAST.PRC_GRP_CD
FROM
    (
        SELECT
            CN0331.N_050_TELNO,
            CN0331.CONT_KEI_NO,
            CN0331.SYSID,
            CN0331.CONT_KEI_UCWK_NO,
            CN0331.GENE_ADD_DTM,
            CN0331.SVC_END_DTM,
            CN0331.CONT_KEI_UCWK_STAT
        FROM
            (SELECT CN0331_GENE.N_050_TELNO,
                    CN0331_GENE.CONT_KEI_NO,
                    CN0331_GENE.SYSID,
                    CN0331_GENE.CONT_KEI_UCWK_NO,
                    CN0331_GENE.CONT_KEI_UCWK_STAT,
                    CN0331_GENE.GENE_ADD_DTM,
                    CN0331_GENE.SVC_END_DTM,
                    MAX(CN0331_GENE.GENE_ADD_DTM) OVER(PARTITION BY CN0331_GENE.CONT_KEI_UCWK_NO) AS GENE_ADD_DTM_MAX
             FROM CN_T_CONT_KEI_UCWK CN0331_GENE
             WHERE CN0331_GENE.MK_FLG = '0') CN0331
        WHERE CN0331.GENE_ADD_DTM = CN0331.GENE_ADD_DTM_MAX
    ) CN0331_MAX
    INNER JOIN
    (
        SELECT
            CK0011.MEMBER_SBT_CD,
            CK0011.EOID,
            CK0011.SYSID,
            CK0011.CUST_NM,
            CK0011.CUST_KANA,
            CK0011.CONT_CUST_NM,
            CK0011.CONT_CUST_KANA
        FROM
            CK_T_CUST CK0011
            INNER JOIN
            (
                SELECT
                    CK0011_GENE.SYSID,
                    CK0011_GENE.RSV_APLY_CD,
                    MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX 
                FROM
                    CK_T_CUST CK0011_GENE 
                WHERE
                    CK0011_GENE.RSV_APLY_CD = '2'
                AND
                    CK0011_GENE.MK_FLG = '0'
                AND
                    RSV_APLY_YMD <= ?
                GROUP BY CK0011_GENE.SYSID,CK0011_GENE.RSV_APLY_CD
            ) CK0011_MAX 
            ON
                CK0011.SYSID = CK0011_MAX.SYSID
            AND
                (CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM)= CK0011_MAX.GENE_ADD_DTM_MAX
            AND
                CK0011.RSV_APLY_CD = CK0011_MAX.RSV_APLY_CD 
        WHERE
            CK0011.MK_FLG = '0' 
        AND
            CK0011.MEMBER_SBT_CD IN ('10','11','20','21','30')
        AND
            CK0011.JOIN_YMD IS NOT NULL
    ) CK0011_LAST
    ON
        CN0331_MAX.SYSID = CK0011_LAST.SYSID
    LEFT OUTER JOIN 
    (
        SELECT
            KK0081.SVC_KEI_NO,
            KK0081.SYSID,
            KK0081.SVC_CD,
            KK0081.GENE_ADD_DTM,
            KK0081.SVC_KEI_STAT,
            KK0081.PRC_GRP_CD
        FROM
            KK_T_SVC_KEI KK0081
        WHERE
            KK0081.MK_FLG = '0'
        AND
            KK0081.SVC_CD = '04'
        AND
            KK0081.PRC_GRP_CD = '14'
        AND
            KK0081.PCRS_CD = 'A58'
        AND
            KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = 
            (
                SELECT
                    MAX(KK0081_1.RSV_APLY_YMD || KK0081_1.GENE_ADD_DTM) AS KTSK_MAX
                FROM
                    KK_T_SVC_KEI KK0081_1
                WHERE
                    KK0081_1.SVC_KEI_NO = KK0081.SVC_KEI_NO
                AND
                    KK0081_1.RSV_APLY_YMD <= ?
                AND
                    KK0081_1.RSV_APLY_CD = '2'
                AND
                    KK0081_1.MK_FLG = '0')
    ) SVKEI_LAST
    ON CK0011_LAST.SYSID = SVKEI_LAST.SYSID
    ORDER BY 
        CN0331_MAX.N_050_TELNO,
        CN0331_MAX.GENE_ADD_DTM DESC
#-