#-----------------------------------------------------------
#--ＫＤＤＩ割引契約異動通知(KK_T_KDDI_WKEI_IDT-KK_DELETE_001)
#--サービス契約番号が未設定で「スマートバリュー照合結果」のデータ
#-----------------------------------------------------------

--KEY:KK_DELETE_001

DELETE FROM KK_T_KDDI_WKEI_IDT WHERE KDDI_WKEI_IDT_NO = ?

#-

#-----------------------------------------------------------
#--ＫＤＤＩ割引契約異動通知(KK_T_KDDI_WKEI_IDT-KK_SELECT_001)
#--スマートバリュー異動連携情報作成処理に必要な情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT /*+ ORDERED */
   KK2461.TAJGS_WRIB_KEI_NO
   ,KK2461.KDDI_WKEI_IDT_NO
   ,KK2461.SVC_KEI_UCWK_NO
   ,KK2461.SMTVL_IDO_DTL_CD
   ,KK2461.PRG_STAT
   ,KK2461.IDO_DTM
   ,KK2461.SMTVL_DSL_CNCL_RSN_CD
   ,KK2451.TAJGSWKEI_TGKEI_NO
   ,KK2451.GENE_ADD_DTM
   ,KK2451.DSL_TAJGS_TCH_YMD
   ,KK2451.PPCHG_TAJGS_TCH_YMD
   ,KK2441.KDDI_JGS_KEI_KANRI_NO
   ,KK2441.KDDI_VAL_CD
   ,KK0081.SVC_KEI_NO
   ,KK0081.SVC_KEI_STAT
   ,KK0081.SVC_CD
   ,KK0081.PRC_GRP_CD
   ,KK0081.PCRS_CD
   ,KK0081.PPLAN_CD
   ,KK0081.SHOSA_YMD
   ,KK0081.SHOSA_CL_YMD
   ,KK0081.SVC_CANCEL_YMD
   ,KK0081.SVC_CHRG_STAYMD
   ,KK0081.PAUSE_STP_CD
   ,KK0081.SVC_PAUSE_YMD
   ,KK0081.SVC_DSL_KISAN_YMD
   ,KK0081.SHOSA_DSL_FIN_CD
   ,KK2461.ADD_OPEACNT
   ,(SELECT
        EOHNT_PPLAN_TIKI_SKCD
      FROM
        KK_M_PPLAN KK0591
      WHERE
        KK0591.PCRS_CD = KK0081.PCRS_CD
        AND KK0591.PPLAN_CD = KK0081.PPLAN_CD
        AND KK0591.PPLAN_TSTAYMD <= ?
        AND KK0591.PPLAN_TENDYMD >= ?
        AND KK0591.MK_FLG= '0'
        ) EOHNT_PPLAN_TIKI_SKCD
   ,KU0011.KJ_KR_CNCL_FLG
   ,KU0011.KR_CNCL_UK_YMD
 FROM
   KK_T_KDDI_WKEI_IDT KK2461
   INNER JOIN KK_T_TAJGSWKEI_TGKEI KK2451 ON
        KK2451.TAJGS_WRIB_KEI_NO = KK2461.TAJGS_WRIB_KEI_NO
        AND KK2451.SVC_KEI_NO = KK2461.SVC_KEI_NO
        AND (KK2451.TAJGS_WRIB_KEI_NO, KK2451.SVC_KEI_NO, KK2451.GENE_ADD_DTM) IN 
              (SELECT KK2451_GENE.TAJGS_WRIB_KEI_NO, KK2451_GENE.SVC_KEI_NO, MAX(KK2451_GENE.GENE_ADD_DTM) 
               FROM 
               KK_T_TAJGSWKEI_TGKEI KK2451_GENE
               WHERE
               KK2451.TAJGS_WRIB_KEI_NO = KK2451_GENE.TAJGS_WRIB_KEI_NO
               AND KK2451.SVC_KEI_NO = KK2451_GENE.SVC_KEI_NO
               AND KK2451_GENE.MK_FLG = '0'
               GROUP BY KK2451_GENE.TAJGS_WRIB_KEI_NO, KK2451_GENE.SVC_KEI_NO
              )
        AND KK2451.TAJGSWKEI_TGKEI_TSTAYMD <= ?
        AND KK2451.TAJGSWKEI_TGKEI_TENDYMD >= ?
        AND KK2451.MK_FLG = '0'
   INNER JOIN KK_T_TAJGS_WRIB_KEI KK2441 ON
        KK2441.TAJGS_WRIB_KEI_NO = KK2451.TAJGS_WRIB_KEI_NO
        AND (KK2441.TAJGS_WRIB_KEI_NO, KK2441.GENE_ADD_DTM) =
            (SELECT KK2441_GENE.TAJGS_WRIB_KEI_NO, MAX(KK2441_GENE.GENE_ADD_DTM)
             FROM   KK_T_TAJGS_WRIB_KEI KK2441_GENE
             WHERE  KK2441_GENE.TAJGS_WRIB_KEI_NO = KK2441.TAJGS_WRIB_KEI_NO
             AND    KK2441_GENE.MK_FLG = '0'
             GROUP BY KK2441_GENE.TAJGS_WRIB_KEI_NO)
        AND KK2441.TAJGS_TCH_YOKSI_UM = '0'
        AND KK2441.MK_FLG = '0'
   INNER JOIN KK_T_SVC_KEI KK0081 ON
        KK0081.SVC_KEI_NO = KK2451.SVC_KEI_NO
        AND (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)
              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.MK_FLG = '0'
   LEFT OUTER JOIN
       (SELECT KU0081.SVC_KEI_NO
               ,KU0011_SUB.KJ_KR_CNCL_FLG
               ,KU0011_SUB.KR_CNCL_UK_YMD
               ,ROW_NUMBER() OVER (PARTITION BY KU0081.SVC_KEI_NO ORDER BY KU0011_SUB.KOJI_UK_OPTNTY_IDO_DTM DESC) ROW_NUMBER
        FROM   KU_T_KOJIAK KU0011_SUB
        INNER JOIN KU_T_SVKEI_KOJIAK KU0081 ON
               KU0011_SUB.KOJIAK_NO = KU0081.KOJIAK_NO
        WHERE  KU0011_SUB.KOJIAK_SBT_CD = '001'
        AND    ((KU0011_SUB.KOJIAK_STAT <> '900' AND KU0011_SUB.MANS_KOJIAK_STAT_CD IS NULL)
               OR KU0011_SUB.MANS_KOJIAK_STAT_CD <> '900')
        AND    ? BETWEEN KU0081.SVKEI_KOJIAK_TSTAYMD AND KU0081.SVKEI_KOJIAK_TENDYMD
        AND    KU0011_SUB.MK_FLG = '0'
        AND    KU0081.MK_FLG = '0') KU0011 ON
        KU0011.SVC_KEI_NO = KK0081.SVC_KEI_NO
        AND KU0011.ROW_NUMBER = 1
 WHERE
   KK2461.KDDI_WKEI_IDT_STAT_CD = '0'
   AND KK2461.MK_FLG = '0'
 ORDER BY KK2441.TAJGS_WRIB_KEI_NO, KK0081.SVC_KEI_NO, KK2461.KDDI_WKEI_IDT_NO DESC

#-

#-----------------------------------------------------------
#--ＫＤＤＩ割引契約異動通知(KK_T_KDDI_WKEI_IDT-KK_SELECT_002)
#--他事業者割引契約番号、サービス契約番号、KDDI割引契約異動通知状態コードごとの最新の異動情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
   KK2461.KDDI_WKEI_IDT_NO
   ,KK2461.TAJGS_WRIB_KEI_NO
   ,KK2461.KDDI_WKEI_IDT_DTM
   ,KK2461.SVC_KEI_UCWK_NO
   ,KK2461.SMTVL_IDO_DTL_CD
   ,KK2461.PRG_STAT
   ,KK2461.IDO_DTM
   ,KK2461.SMTVL_DSL_CNCL_RSN_CD
 FROM
   KK_T_KDDI_WKEI_IDT KK2461
 WHERE
   KK2461.TAJGS_WRIB_KEI_NO = ?
   AND KK2461.SVC_KEI_NO = ?
   AND KK2461.KDDI_WKEI_IDT_NO =
            (SELECT MAX(KK2461_SUB.KDDI_WKEI_IDT_NO) AS KK2461_MAX
             FROM   KK_T_KDDI_WKEI_IDT KK2461_SUB
             WHERE  KK2461_SUB.TAJGS_WRIB_KEI_NO = KK2461.TAJGS_WRIB_KEI_NO
             AND    KK2461_SUB.SVC_KEI_NO = KK2461.SVC_KEI_NO
             AND    KK2461_SUB.KDDI_WKEI_IDT_SBT_CD = '3'
             AND    KK2461_SUB.KDDI_WKEI_IDT_STAT_CD IN ('1','3')
             AND    KK2461_SUB.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--ＫＤＤＩ割引契約異動通知(KK_T_KDDI_WKEI_IDT-KK_SELECT_003)
#--サービス契約番号が設定されており「スマートバリュー照合結果」のデータ
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT KDDI_WKEI_IDT_NO
FROM KK_T_KDDI_WKEI_IDT
WHERE KDDI_WKEI_IDT_SBT_CD = '1'
AND KDDI_WKEI_IDT_DTM < ?
AND KDDI_WKEI_IDT_ERR_CD > '0'
AND SVC_KEI_NO IS NULL

#-

#-----------------------------------------------------------
#--ＫＤＤＩ割引契約異動通知(KK_T_KDDI_WKEI_IDT-KK_SELECT_004)
#--サービス契約番号が設定されており「スマートバリュー照合依頼」のデータ
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT KDDI_WKEI_IDT_NO
FROM KK_T_KDDI_WKEI_IDT
WHERE KDDI_WKEI_IDT_SBT_CD = '1'
AND KDDI_WKEI_IDT_DTM < ?
AND KDDI_WKEI_IDT_ERR_CD > '0'
AND SVC_KEI_NO > '0'

#-

#-----------------------------------------------------------
#--ＫＤＤＩ割引契約異動通知(KK_T_KDDI_WKEI_IDT-KK_UPDATE_001)
#--送信ステータス更新
#-----------------------------------------------------------

--KEY:KK_UPDATE_001

UPDATE KK_T_KDDI_WKEI_IDT
 SET KDDI_WKEI_IDT_RSLT_CD = ?
    ,KDDI_WKEI_IDT_STAT_CD = NVL(?, KDDI_WKEI_IDT_STAT_CD)
    ,KDDI_WKEI_IDT_ERR_CD = ?
    ,KDDI_WKEI_IDT_DTM = ?
    ,UPD_DTM        = 'UPD_DTM'
    ,UPD_UNYO_YMD   = 'UPD_UNYO'
    ,UPD_TRN_ID     = 'UPD_TRN'
    ,UPD_OPEACNT    = 'UPD_OPE'
    ,MK_FLG         = '0'
WHERE KDDI_WKEI_IDT_NO = ?

#-

#-----------------------------------------------------------
#--ＫＤＤＩ割引契約異動通知(KK_T_KDDI_WKEI_IDT-KK_SELECT_005)
#--KDDI割引契約異動通知テーブルから異動通知が未通知かつ進捗ステータスが
#--キャンセルまたはコース変更かつ異動年月日時分秒がバッチ運用日が同日または未来のデータを抽出する。
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT COUNT(*) CNT from KK_T_KDDI_WKEI_IDT KK2461
WHERE KK2461.TAJGS_WRIB_KEI_NO = ?
AND KK2461.SVC_KEI_NO = ?
AND KK2461.KDDI_WKEI_IDT_STAT_CD = '0'
AND KK2461.MK_FLG = '0'
AND ((KK2461.PRG_STAT in ( '1201' , '1422' ) AND SUBSTR(KK2461.IDO_DTM,1,8) >= ?) 
OR  (KK2461.PRG_STAT = '2203' AND SUBSTR(KK2461.IDO_DTM,1,8) > ?))
AND KK2461.ADD_OPEACNT = 'BATSERVICE'

#-