#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-AC_SELECT_001)
#--【内容】
#--    サービス契約番号(KK0351.SVC_KEI_NO)
#--【検索条件】
#--    ０５０オプション電話番号(KK0371.N_050_OP_TELNO)
#--    予約適用年月日(KK0351_GENE.RSV_APLY_YMD)
#--【結合テーブル】
#--    オプションサービス契約(KK_T_OP_SVC_KEI)
#--【結合条件】
#--    オプションサービス契約番号(KK0371.OP_SVC_KEI_NO) = オプションサービス契約番号(KK0351.OP_SVC_KEI_NO)
#--【抽出対象】
#--    一意
#--【世代管理】
#--    あり
#--【予約管理】
#--    あり
#--【無効フラグ考慮】
#--    あり
#-----------------------------------------------------------

--KEY:AC_SELECT_001

SELECT
    KK0351.SVC_KEI_NO,
    KK0351.SVC_STAYMD,
    KK0351.SVC_ENDYMD
FROM
    (SELECT
         KK0371.OP_SVC_KEI_NO
     FROM
         KK_T_OPSVKEI_TEL KK0371
     INNER JOIN
         (SELECT
              KK0371_GENE.OP_SVC_KEI_NO,
              MAX(KK0371_GENE.GENE_ADD_DTM) AS KK0371_MAX
          FROM
              KK_T_OPSVKEI_TEL KK0371_GENE
          WHERE
                  KK0371_GENE.N_050_OP_TELNO = ?
              AND KK0371_GENE.RSV_APLY_CD = '2'
              AND KK0371_GENE.MK_FLG = '0'
          GROUP BY
              KK0371_GENE.OP_SVC_KEI_NO
         )KK0371_GENE
     ON
             KK0371.OP_SVC_KEI_NO = KK0371_GENE.OP_SVC_KEI_NO
         AND KK0371.GENE_ADD_DTM  = KK0371_GENE.KK0371_MAX
    )KK0371
    INNER JOIN
    (SELECT
         KK0351.OP_SVC_KEI_NO,
         KK0351.SVC_KEI_NO,
         KK0351.SVC_STAYMD,
         KK0351.SVC_ENDYMD
     FROM
         KK_T_OP_SVC_KEI KK0351
     WHERE
         KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM =
             (SELECT
                  MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_MAX
              FROM
                  KK_T_OP_SVC_KEI KK0351_GENE
              WHERE
                      KK0351_GENE.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO
                  AND KK0351_GENE.RSV_APLY_YMD <= ?
                  AND KK0351_GENE.RSV_APLY_CD = '2'
                  AND KK0351_GENE.MK_FLG = '0'
             )
    )KK0351
    ON
        KK0371.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-AC_SELECT_002)
#--【内容】
#--    サービス契約番号(KK0351.SVC_KEI_NO)
#--【検索条件】
#--    ０５０オプション電話番号(KK0371.N_050_OP_TELNO)
#--    予約適用年月日(KK0351_GENE.RSV_APLY_YMD)
#--【結合テーブル】
#--    オプションサービス契約(KK_T_OP_SVC_KEI)
#--【結合条件】
#--    オプションサービス契約番号(KK0371.OP_SVC_KEI_NO) = オプションサービス契約番号(KK0351.OP_SVC_KEI_NO)
#--【抽出対象】
#--    一意
#--【世代管理】
#--    なし
#--【予約管理】
#--    あり
#--【無効フラグ考慮】
#--    あり
#-----------------------------------------------------------

--KEY:AC_SELECT_002

SELECT
  KK0351.SVC_KEI_NO
FROM
  KK_T_OPSVKEI_TEL KK0371
  INNER JOIN KK_T_OP_SVC_KEI KK0351
  ON KK0371.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO
WHERE
  KK0371.N_050_OP_TELNO = ?
  AND KK0371.RSV_APLY_CD = '2'
  AND KK0351.RSV_APLY_YMD =
    (SELECT MAX(KK0351_GENE.RSV_APLY_YMD) AS KK0351_MAX
    FROM KK_T_OP_SVC_KEI KK0351_GENE
    WHERE KK0351_GENE.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO
    AND KK0351_GENE.RSV_APLY_YMD <= ?
    AND KK0351_GENE.RSV_APLY_CD = '2'
    AND KK0351_GENE.MK_FLG = '0')
  AND KK0371.MK_FLG = '0'
  AND KK0351.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-AC_SELECT_003)
#--UIA027_ACPRC00801_eo光電話利用呼情報取込_参照
#-----------------------------------------------------------

--KEY:AC_SELECT_003

SELECT
    KK0351.SVC_STAYMD,
    KK0351.SVC_ENDYMD,
    KK0161.TELNO
FROM
    KK_T_OP_SVC_KEI KK0351
    INNER JOIN
        (SELECT
             KK0371.OP_SVC_KEI_NO,
             KK0371.GENE_ADD_DTM
         FROM
             KK_T_OPSVKEI_TEL KK0371
             INNER JOIN
                 (SELECT
                     KK0371_GENE.OP_SVC_KEI_NO,
                     MAX(KK0371_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM
                  FROM
                     KK_T_OPSVKEI_TEL KK0371_GENE
                  WHERE
                     KK0371_GENE.N_050_OP_TELNO     = ?
                     AND KK0371_GENE.RSV_APLY_CD    = '2'
                     AND KK0371_GENE.MK_FLG         = '0'
                  GROUP BY
                     KK0371_GENE.OP_SVC_KEI_NO
                 ) KK0371_GENE
             ON
                 KK0371.OP_SVC_KEI_NO = KK0371_GENE.OP_SVC_KEI_NO
             AND
                 KK0371.GENE_ADD_DTM  = KK0371_GENE.MAX_GENE_DTM
        ) KK0371
    ON
        KK0351.OP_SVC_KEI_NO = KK0371.OP_SVC_KEI_NO
    INNER JOIN
        (SELECT
             KK0351_GENE.OP_SVC_KEI_NO,
             MAX(KK0351_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM
         FROM
             KK_T_OP_SVC_KEI KK0351_GENE
         WHERE
             KK0351_GENE.MK_FLG = '0'
         GROUP BY
              KK0351_GENE.OP_SVC_KEI_NO
        ) KK0351_GENE
    ON
        KK0351.OP_SVC_KEI_NO = KK0351_GENE.OP_SVC_KEI_NO
    AND
        KK0351.GENE_ADD_DTM  = KK0351_GENE.MAX_GENE_DTM
    INNER JOIN
        (SELECT
             KK0191.TELNO,
             KK0161.SVC_KEI_UCWK_NO,
             KK0161.SVC_STA_YMD,
             KK0161.SVC_ENDYMD
         FROM
             KK_T_SVC_KEI_UCWK KK0161
             INNER JOIN
                (SELECT
                     KK0161_GENE.SVC_KEI_UCWK_NO,
                     MAX(KK0161_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM
                 FROM
                     KK_T_SVC_KEI_UCWK KK0161_GENE
                 GROUP BY
                     KK0161_GENE.SVC_KEI_UCWK_NO
                ) KK0161_GENE
             ON
                 KK0161.SVC_KEI_UCWK_NO = KK0161_GENE.SVC_KEI_UCWK_NO
             AND
                 KK0161.GENE_ADD_DTM    = KK0161_GENE.MAX_GENE_DTM
             INNER JOIN
                 KK_T_SVKEIUW_EOH_TEL KK0191
                 INNER JOIN
                     (SELECT
                          KK0191_GENE.SVC_KEI_UCWK_NO,
                          MAX(KK0191_GENE.GENE_ADD_DTM) AS MAX_GENE_DTM
                      FROM
                          KK_T_SVKEIUW_EOH_TEL KK0191_GENE
                      WHERE
                          KK0191_GENE.RSV_APLY_CD = '2'
                      AND
                          KK0191_GENE.MK_FLG      = '0'
                      GROUP BY
                          KK0191_GENE.SVC_KEI_UCWK_NO
                     ) KK0191_GENE
                 ON
                     KK0191.SVC_KEI_UCWK_NO = KK0191_GENE.SVC_KEI_UCWK_NO
                 AND
                     KK0191.GENE_ADD_DTM    = KK0191_GENE.MAX_GENE_DTM
             ON
                 KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO
         WHERE
             KK0161.RSV_APLY_YMD <= ?
         AND
             KK0161.RSV_APLY_CD   = '2'
         AND
             KK0161.MK_FLG        = '0'
        ) KK0161
    ON
        KK0351.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
    AND
        KK0161.SVC_STA_YMD    <= KK0351.SVC_STAYMD
    AND
        KK0161.SVC_ENDYMD     >= KK0351.SVC_ENDYMD
WHERE
    KK0351.RSV_APLY_YMD <= ?
AND
    KK0351.RSV_APLY_CD   = '2'
AND
    KK0351.MK_FLG        = '0'
ORDER BY
    KK0351.SVC_STAYMD,
    KK0351.SVC_ENDYMD

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-AC_SELECT_004)
#--UIA17_ACPRC06101_トビラシステムズ事業者間精算情報作成_参照
#-----------------------------------------------------------

--KEY:AC_SELECT_004

SELECT KK0351.SYSID ,
  KK0371.TOBILAPH_ID ,
  KK0161.SVC_STA_YMD ,
  KK0161.SVC_ENDYMD ,
  KK0161.SVC_KEI_UCWK_STAT ,
  KK0161.SVC_DSL_YMD
FROM KK_T_OP_SVC_KEI KK0351
INNER JOIN KK_T_OPSVKEI_TEL KK0371
ON KK0371.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO
AND KK0371.GENE_ADD_DTM = KK0351.GENE_ADD_DTM
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
ON KK0161.SVC_KEI_UCWK_NO = KK0351.SVC_KEI_UCWK_NO
WHERE ( KK0161.SVC_KEI_UCWK_NO , KK0161.RSV_APLY_YMD
|| KK0161.GENE_ADD_DTM ) =
  (SELECT KK0161_GENE.SVC_KEI_UCWK_NO ,
    MAX( KK0161_GENE.RSV_APLY_YMD
    || KK0161_GENE.GENE_ADD_DTM ) AS KK0161_MAX
  FROM KK_T_SVC_KEI_UCWK KK0161_GENE
  WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
  AND KK0161_GENE.RSV_APLY_YMD     <= ?
  AND KK0161_GENE.RSV_APLY_CD       = '2'
  AND KK0161_GENE.MK_FLG            = '0'
  GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO
  )
AND ( KK0351.SVC_KEI_UCWK_NO , KK0351.RSV_APLY_YMD
  || KK0351.GENE_ADD_DTM ) =
  (SELECT KK0351_GENE.SVC_KEI_UCWK_NO ,
    MAX( KK0351_GENE.RSV_APLY_YMD
    || KK0351_GENE.GENE_ADD_DTM ) AS KK0351_MAX
  FROM KK_T_OP_SVC_KEI KK0351_GENE
  WHERE KK0351_GENE.SVC_KEI_UCWK_NO = KK0351.SVC_KEI_UCWK_NO
  AND KK0351_GENE.RSV_APLY_YMD     <= ?
  AND KK0351_GENE.RSV_APLY_CD       = '2'
  AND KK0351_GENE.MK_FLG            = '0'
  GROUP BY KK0351_GENE.SVC_KEI_UCWK_NO
  )
AND KK0161.SVC_KEI_UCWK_NO = ?
AND KK0351.OP_SVC_CD       = ?

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-KK_INSERT_001)
#--指定したオプションサービス契約番号と世代登録年月日時分秒から全情報を取得し、休止のカレントレコードを生成する。
#-----------------------------------------------------------

--KEY:KK_INSERT_001

INSERT INTO KK_T_OPSVKEI_TEL
SELECT
    OP_SVC_KEI_NO, 
    ?,              --GENE_ADD_DTM (1)
    N_050_OP_TELNO, 
    '2',            --RSV_APLY_CD
    ?,              --ADD_DTM (2)
    ?,              --ADD_OPEACNT (3)
    ?,              --UPD_DTM (4)
    ?,              --UPD_OPEACNT (5)
    null,           --DEL_DTM
    null,           --DEL_OPEACNT
    '0'             --MK_FLG
FROM
    KK_T_OPSVKEI_TEL
WHERE
    OP_SVC_KEI_NO = ?   --(6)
AND
    GENE_ADD_DTM = ?    --(7)
AND
    MK_FLG = '0'

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-KK_INSERT_002)
#--消去実行
#-----------------------------------------------------------

--KEY:KK_INSERT_002

INSERT INTO KK_T_OPSVKEI_TEL(
    OP_SVC_KEI_NO,
    GENE_ADD_DTM,
    N_050_OP_TELNO,
    RSV_APLY_CD,
    TWRYO_STIAM_TTU_TCHI_STIAM,
    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
    OP_SVC_KEI_NO,
    ?,
    N_050_OP_TELNO,
    RSV_APLY_CD,
    TWRYO_STIAM_TTU_TCHI_STIAM,
    ADD_DTM,
    ADD_OPEACNT,
    'UPD_DTM',
    'UPD_OPE',
    NULL,
    NULL,
    '0',
    ADD_UNYO_YMD,
    ADD_TRN_ID,
    'UPD_UNYO',
    'UPD_TRN',
    NULL,
    NULL
FROM KK_T_OPSVKEI_TEL
WHERE OP_SVC_KEI_NO = ?
AND GENE_ADD_DTM = ?

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-KK_SELECT_001)
#--SOD発行に必要なデータを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT 
    KTOT.N_050_OP_TELNO 
FROM 
    KK_T_OPSVKEI_TEL KTOT 
WHERE 
    KTOT.MK_FLG = '0' 
AND
    KTOT.OP_SVC_KEI_NO = ? 
AND
    KTOT.GENE_ADD_DTM = ? 

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-KK_SELECT_002)
#--サービス契約番号とサービス契約内訳番号に紐づくオプションサービス契約のデータを取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT 
    KTOT.OP_SVC_KEI_NO,
    KTOT.GENE_ADD_DTM, 
    KTOT.N_050_OP_TELNO,
    KTOSK.OP_SVC_KEI_STAT
FROM 
    KK_T_OP_SVC_KEI KTOSK,
    KK_T_OPSVKEI_TEL KTOT
WHERE 
    KTOSK.SVC_KEI_UCWK_NO = ? 
AND
    KTOSK.OP_SVC_CD = NVL(?, 'B029')
AND
    KTOSK.OP_SVC_KEI_NO = KTOT.OP_SVC_KEI_NO
AND
    KTOT.GENE_ADD_DTM = KTOSK.GENE_ADD_DTM
AND KTOSK.RSV_APLY_YMD || KTOSK.GENE_ADD_DTM = (
         SELECT  MAX(SUB_KTOSK.RSV_APLY_YMD || SUB_KTOSK.GENE_ADD_DTM) AS MAX_OP_SVC_GENE_ADD_DTM
         FROM KK_T_OP_SVC_KEI SUB_KTOSK
         WHERE SUB_KTOSK.OP_SVC_KEI_NO = KTOSK.OP_SVC_KEI_NO
         AND SUB_KTOSK.RSV_APLY_YMD <= ?
         AND SUB_KTOSK.RSV_APLY_CD = '2'
         AND SUB_KTOSK.MK_FLG = '0')
AND KTOT.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-KK_SELECT_003)
#--050電話番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT  OPSVKEI_TEL.N_050_OP_TELNO
FROM KK_T_OPSVKEI_TEL OPSVKEI_TEL
INNER JOIN KK_T_OP_SVC_KEI OP_SVC_KEI
ON OP_SVC_KEI.GENE_ADD_DTM = OPSVKEI_TEL.GENE_ADD_DTM
AND OPSVKEI_TEL.OP_SVC_KEI_NO = ?
AND OP_SVC_KEI.RSV_APLY_YMD || OPSVKEI_TEL.GENE_ADD_DTM = (
    SELECT
        MAX(OP_SVC_KEI_GENE.RSV_APLY_YMD || OP_SVC_KEI_GENE.GENE_ADD_DTM) AS MAX_OP_SVC_GENE_ADD_DTM
    FROM
        KK_T_OP_SVC_KEI OP_SVC_KEI_GENE
    WHERE
        OP_SVC_KEI_GENE.OP_SVC_KEI_NO = OP_SVC_KEI.OP_SVC_KEI_NO
        AND OP_SVC_KEI_GENE.RSV_APLY_YMD <= ?
        AND OP_SVC_KEI_GENE.RSV_APLY_CD = '2'
        AND OP_SVC_KEI_GENE.MK_FLG = '0')
AND OPSVKEI_TEL.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-KK_SELECT_004)
#--サービス契約内訳番号とオプションサービスコードに紐づくオプションサービス契約の履歴を取得する
#-- EO24501サービスオーダ送信依頼（2013/02/06～　KT1-2013-0000170対応）
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT 
    KTOSK.*
FROM 
    KK_T_OP_SVC_KEI KTOSK
JOIN
    KK_T_OPSVKEI_TEL KTOT 
    ON  KTOT.OP_SVC_KEI_NO = KTOSK.OP_SVC_KEI_NO 
    AND KTOT.GENE_ADD_DTM  = KTOSK.GENE_ADD_DTM 
WHERE 
     KTOSK.SVC_KEI_UCWK_NO = ? 
AND KTOSK.OP_SVC_CD = ? 
AND KTOSK.RSV_APLY_YMD <= ?
AND KTOSK.GENE_ADD_DTM <= ?
AND KTOSK.RSV_APLY_CD = '2'
AND KTOSK.MK_FLG = '0'
AND KTOT.RSV_APLY_CD = '2'
AND KTOT.MK_FLG = '0'
ORDER BY
    KTOSK.GENE_ADD_DTM DESC

#-

#-----------------------------------------------------------
#--オプションサービス契約＜電話＞(KK_T_OPSVKEI_TEL-KK_SELECT_005)
#--サービス契約番号とサービス契約内訳番号に紐づくオプションサービス契約のデータを取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT 
    KTOT.OP_SVC_KEI_NO,
    KTOT.GENE_ADD_DTM, 
    KTOT.TOBILAPH_ID,
    KTOSK.SVC_USE_STA_KIBO_YMD,
    KTOSK.OP_SVC_KEI_STAT
FROM 
    KK_T_OP_SVC_KEI KTOSK,
    KK_T_OPSVKEI_TEL KTOT
WHERE 
    KTOSK.SVC_KEI_UCWK_NO = ? 
AND
    KTOSK.OP_SVC_CD = 'B135'
AND
    KTOSK.OP_SVC_KEI_NO = KTOT.OP_SVC_KEI_NO
AND
    KTOT.GENE_ADD_DTM = KTOSK.GENE_ADD_DTM
AND KTOSK.RSV_APLY_YMD || KTOSK.GENE_ADD_DTM = (
         SELECT  MAX(SUB_KTOSK.RSV_APLY_YMD || SUB_KTOSK.GENE_ADD_DTM) AS MAX_OP_SVC_GENE_ADD_DTM
         FROM KK_T_OP_SVC_KEI SUB_KTOSK
         WHERE SUB_KTOSK.OP_SVC_KEI_NO = KTOSK.OP_SVC_KEI_NO
         AND SUB_KTOSK.RSV_APLY_YMD <= ?
         AND SUB_KTOSK.RSV_APLY_CD = '2'
         AND SUB_KTOSK.MK_FLG = '0')
AND KTOT.MK_FLG = '0'
AND KTOSK.OP_SVC_KEI_STAT < '910'
AND KTOSK.OP_SVC_KEI_STAT > '010'

#-