#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-ZM_DELETE_001)
#--エイジングマスタ（ZM_T_AGING）
#--エイジングデータの「使用終了年月日時分秒」が、エイジング種別毎の「エイジング削除基準日」以前のデータを、物理削除する。
#-----------------------------------------------------------

--KEY:ZM_DELETE_001

DELETE
FROM
    ZM_T_AGING
WHERE
    MK_FLG = '0' AND
    AGING_STAT = '300' AND
    AGING_SBT_CD = ? AND
    USE_END_DTM <= ?

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-ZM_DELETE_002)
#--エイジングマスタ（ZM_T_AGING）
#--特定の経過期間データの削除
#-----------------------------------------------------------

--KEY:ZM_DELETE_002

DELETE
    ZM_T_AGING
WHERE
    MK_FLG = '0' AND
    AGING_STAT = '300' AND
    AGING_SBT_CD = ? AND
    AGING_TG_VALUE = ? 

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-CK_SELECT_001)
#--エイジング情報の取得を行います。
#-----------------------------------------------------------

--KEY:CK_SELECT_001

SELECT 
    ZM0111.AGING_NO
FROM
    ZM_T_AGING ZM0111
WHERE
    ZM0111.AGING_STAT in ('100','200')
    AND ZM0111.AGING_SBT_CD = '004'
    AND ZM0111.AGING_TG_VALUE = ?
    AND ZM0111.MK_FLG = '0'
UNION ALL
SELECT 
    ZM0111.AGING_NO
FROM
    ZM_T_AGING ZM0111
WHERE
    ZM0111.AGING_STAT in ('100','200')
    AND ZM0111.AGING_SBT_CD = '003'
    AND ZM0111.AGING_TG_VALUE = ?
    AND ZM0111.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--お客様(CK_T_CUST-KK_INSERT_001)
#--消去実行
#-----------------------------------------------------------

--KEY:KK_INSERT_001

INSERT INTO CK_T_CUST(
    SYSID,
    GENE_ADD_DTM,
    CUST_STAT,
    KEISHA_TYPE_CD,
    CUST_NM,
    CUST_KANA,
    CUST_SEX_CD,
    CUST_BIRTHD,
    NM_KISHO,
    CUST_CFM_BIKO,
    KEISHA_AD_CD,
    KEISHA_PCD,
    KEISHA_STATE_NM,
    KEISHA_CITY_NM,
    KEISHA_OAZTSU_NM,
    KEISHA_AZCHO_NM,
    KEISHA_BNCHIGO,
    KEISHA_ADRTTM,
    KEISHA_ADRRM,
    KEISHA_TELNO,
    CUST_RANK_CD,
    RRK_WAY_CD,
    RRK_WAY_HOKI,
    EOID,
    EOID_CHGE_YMD,
    CONT_CUST_NM,
    CONT_CUST_KANA,
    CONT_CUST_SEX_CD,
    CONT_CUST_BIRTHD,
    CONT_AD_CD,
    CONT_PCD,
    CONT_STATE_NM,
    CONT_CITY_NM,
    CONT_OAZTSU_NM,
    CONT_AZCHO_NM,
    CONT_BNCHIGO,
    CONT_ADRTTM,
    CONT_ADRRM,
    CONT_AD_BNKT_STA_PST,
    MEMBER_SBT_CD,
    CONT_ISP_USE_FAIL_FLG,
    CONT_ISP_USE_STP_YMD,
    KARI_ADD_YMD,
    JOIN_YMD,
    RSV_APLY_YMD,
    RSV_CL_YMD,
    RSV_APLY_CD,
    PWD_REMINDER_QS_CD,
    PWD_REMINDER_QS_NAIYO,
    PWD_REMINDER_ANS,
    NKNAME,
    NKNAME_CHGE_YMD,
    NAIHAN_GAIHAN_CD,
    NAIHAN_COMP_SKBT_CD,
    NECESSARY_HOJO_CD,
    EONETID_MI_IKO_FLG,
    LOGIN_NINSHO_KH,
    CUST_TKJK_CD,
    CUST_NTAIKAI_CD,
    CUST_TAIKAI_YMD,
    CUST_TAIKAI_RSN_CD,
    CUST_KAIHK_YMD,
    LOGIN_NINSHO_STP_YMD,
    SHK_EOID,
    SHK_EOID_PWD,
    MMB_SBT_TOKUT_FLG,
    NAYOSE_SAKI_SYSID,
    NAYOSE_YMD,
    EOID_TCHISHO_HAKKOJSKI_CD,
    OLD_EONETMB_NINSHO_ID,
    MMB_ISP_KSSAI_STAT_CD,
    CUST_MK_YMD,
    DEL_TRGT_GAI_FLG,
    KYUMIN_FLG,
    KYUMIN_YMD,
    SVCTK_BUT_DEL_TRN_JSSI_DTM,
    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
)
SELECT
    SYSID,
    ?,
    CUST_STAT,
    KEISHA_TYPE_CD,
    CUST_NM,
    CUST_KANA,
    CUST_SEX_CD,
    CUST_BIRTHD,
    NM_KISHO,
    CUST_CFM_BIKO,
    KEISHA_AD_CD,
    KEISHA_PCD,
    KEISHA_STATE_NM,
    KEISHA_CITY_NM,
    KEISHA_OAZTSU_NM,
    KEISHA_AZCHO_NM,
    KEISHA_BNCHIGO,
    KEISHA_ADRTTM,
    KEISHA_ADRRM,
    KEISHA_TELNO,
    CUST_RANK_CD,
    RRK_WAY_CD,
    RRK_WAY_HOKI,
    EOID,
    EOID_CHGE_YMD,
    CONT_CUST_NM,
    CONT_CUST_KANA,
    CONT_CUST_SEX_CD,
    CONT_CUST_BIRTHD,
    CONT_AD_CD,
    CONT_PCD,
    CONT_STATE_NM,
    CONT_CITY_NM,
    CONT_OAZTSU_NM,
    CONT_AZCHO_NM,
    CONT_BNCHIGO,
    CONT_ADRTTM,
    CONT_ADRRM,
    CONT_AD_BNKT_STA_PST,
    MEMBER_SBT_CD,
    CONT_ISP_USE_FAIL_FLG,
    CONT_ISP_USE_STP_YMD,
    KARI_ADD_YMD,
    JOIN_YMD,
    RSV_APLY_YMD,
    RSV_CL_YMD,
    RSV_APLY_CD,
    PWD_REMINDER_QS_CD,
    PWD_REMINDER_QS_NAIYO,
    PWD_REMINDER_ANS,
    NKNAME,
    NKNAME_CHGE_YMD,
    NAIHAN_GAIHAN_CD,
    NAIHAN_COMP_SKBT_CD,
    NECESSARY_HOJO_CD,
    EONETID_MI_IKO_FLG,
    LOGIN_NINSHO_KH,
    CUST_TKJK_CD,
    CUST_NTAIKAI_CD,
    CUST_TAIKAI_YMD,
    CUST_TAIKAI_RSN_CD,
    CUST_KAIHK_YMD,
    LOGIN_NINSHO_STP_YMD,
    SHK_EOID,
    SHK_EOID_PWD,
    MMB_SBT_TOKUT_FLG,
    NAYOSE_SAKI_SYSID,
    NAYOSE_YMD,
    EOID_TCHISHO_HAKKOJSKI_CD,
    OLD_EONETMB_NINSHO_ID,
    MMB_ISP_KSSAI_STAT_CD,
    CUST_MK_YMD,
    DEL_TRGT_GAI_FLG,
    KYUMIN_FLG,
    KYUMIN_YMD,
    ?,
    ADD_DTM,
    ADD_OPEACNT,
    'UPD_DTM',
    'UPD_OPE',
    NULL,
    NULL,
    '0',
    ADD_UNYO_YMD,
    ADD_TRN_ID,
    'UPD_UNYO',
    'UPD_TRN',
    NULL,
    NULL
FROM CK_T_CUST
WHERE SYSID = ?
AND GENE_ADD_DTM = (
    SELECT
        MAX(CUST_GENE.GENE_ADD_DTM) AS MAX_CUST_GENE_ADD_DTM
    FROM
        CK_T_CUST CUST_GENE
    WHERE
        CUST_GENE.SYSID = ?
        AND CUST_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-KK_UPDATE_001)
#--エイジング消去実行
#-----------------------------------------------------------

--KEY:KK_UPDATE_001

UPDATE ZM_T_AGING
SET AGING_STAT = '300',
    KAIHK_PSB_PRD_END_DTM = ?,
    UPD_DTM        = 'UPD_DTM', 
    UPD_UNYO_YMD   = 'UPD_UNYO',
    UPD_TRN_ID     = 'UPD_TRN',
    UPD_OPEACNT    = 'UPD_OPE',
    MK_FLG         = '0'
WHERE AGING_NO = ?

#-
#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-KK_UPDATE_002)
#--エイジング無効実行
#-----------------------------------------------------------

--KEY:KK_UPDATE_002

 UPDATE
  ZM_T_AGING
 SET
  UPD_DTM = 'UPD_DTM',
  UPD_UNYO_YMD = 'UPD_UNYO',
  UPD_TRN_ID = 'UPD_TRN',
  UPD_OPEACNT = 'UPD_OPE',
  DEL_DTM = 'DEL_DTM',
  DEL_UNYO_YMD = 'DEL_UNYO',
  DEL_TRN_ID = 'DEL_TRN',
  DEL_OPEACNT = 'DEL_OPE',
  MK_FLG = '1'
 WHERE
  AGING_NO = ?

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-KK_SELECT_001)
#--KKPRC02202
#--eo光ネット工事取消レコード抽出。新認証ID解約
#--KKPRC02204
#--エイジング固定IPアドレス情報取得
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
ZM0111.AGING_NO
FROM
ZM_T_AGING ZM0111
WHERE
ZM0111.AGING_SBT_CD = ?  AND
ZM0111.AGING_TG_VALUE = ?  AND
ZM0111.MK_FLG ='0'

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-KK_SELECT_002)
#--エイジング番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT AGING.AGING_NO
FROM ZM_T_AGING AGING
WHERE AGING.AGING_SBT_CD = ?
AND AGING.AGING_TG_VALUE = ?
AND AGING.AGING_STAT = ?
AND AGING.MK_FLG = ?

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-KK_DELETE_001)
#--エイジング（使用中の電話番号）の削除
#-----------------------------------------------------------

--KEY:KK_DELETE_001

DELETE FROM ZM_T_AGING 
WHERE AGING_SBT_CD = '001' 
  AND AGING_TG_VALUE = ? 
  AND AGING_STAT = '100' 

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-KK_DELETE_002)
#--エイジング無効実行
#-----------------------------------------------------------

--KEY:KK_DELETE_002

DELETE ZM_T_AGING
WHERE AGING_NO = ?

#-

#----------------------TU START-----------------

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-TU_SELECT_001)
#--エイジング種別コードが電話番号でエイジング対象値と電話番号が一致するデータのエイジング番号を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_001

SELECT
    ZM0111.AGING_NO
FROM
    ZM_T_AGING ZM0111
WHERE
    ZM0111.AGING_SBT_CD = '001' AND
    ZM0111.AGING_TG_VALUE = ?
FOR UPDATE

#-

#----------------------TU END-------------------

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

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-CK_DELETE_901)
#--エイジング対象値を削除する。
#-----------------------------------------------------------

--KEY:CK_DELETE_901

DELETE ZM_T_AGING
 WHERE AGING_SBT_CD = ?
   AND AGING_TG_VALUE = ?
   AND AGING_STAT = '100'
   AND MK_FLG = '0'

#-

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

#----------------------CN START-------------

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-CN_SELECT_001)
#--回復可能期間を超えた050電話番号のエイジング番号を取得する。
#-----------------------------------------------------------

--KEY:CN_SELECT_001

SELECT    A.AGING_NO
FROM    ZM_T_AGING A 
WHERE    A.AGING_STAT = '200'
AND A.AGING_SBT_CD = '016'
AND A.MK_FLG = '0'
AND A.USE_END_DTM >= ?
AND A.USE_END_DTM <= ?
FOR UPDATE

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-CN_UPDATE_001)
#--エイジングステータスをエイジング中に更新する。
#-----------------------------------------------------------

--KEY:CN_UPDATE_001

UPDATE ZM_T_AGING A SET A.AGING_STAT = '300'
,A.UPD_DTM = 'UPD_DTM'
,A.UPD_OPEACNT = 'UPD_OPE'
,A.UPD_UNYO_YMD = 'UPD_UNYO'
,A.UPD_TRN_ID = 'UPD_TRN'
WHERE AGING_NO = ?

#-

#----------------------CN END---------------

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-KK_DELETE_003)
#--エイジング対象値を削除する。
#--引数１：パラーメタ__エイジング対象値
#--引数２：エイジング種別コード
#--エイジング対象値を削除する。
#-----------------------------------------------------------

--KEY:KK_DELETE_003

DELETE /*ZM_T_AGING.SQL KK_DELETE_003*/
       ZM_T_AGING
 WHERE AGING_TG_VALUE = ?
   AND AGING_SBT_CD = ?
   AND AGING_STAT = '100'
   AND MK_FLG = '0'
#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-CK_SELECT_002)
#--エイジング情報の取得を行います。
#-----------------------------------------------------------

--KEY:CK_SELECT_002

SELECT 
    ZM0111.AGING_NO
FROM
    ZM_T_AGING ZM0111
WHERE
    ZM0111.AGING_STAT = '100'
    AND ZM0111.AGING_SBT_CD = '004'
    AND ZM0111.AGING_TG_VALUE = ?
    AND ZM0111.MK_FLG = '0'
UNION ALL
SELECT 
    ZM0111.AGING_NO
FROM
    ZM_T_AGING ZM0111
WHERE
    ZM0111.AGING_STAT = '100'
    AND ZM0111.AGING_SBT_CD = '003'
    AND ZM0111.AGING_TG_VALUE = ?
    AND ZM0111.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--エイジング(ZM_T_AGING-ZM_SELECT_001)
#--エージング電話値の取得
#-----------------------------------------------------------

--KEY:ZM_SELECT_001

SELECT
	AGING_TG_VALUE
FROM
    ZM_T_AGING
WHERE
    AGING_STAT = '300'
    AND AGING_SBT_CD = ?
    AND USE_END_DTM <= ?
    AND MK_FLG = '0'
    

#-