#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_001)
#--【イーサネット帯域変更依頼抽出】
#--イーサネット帯域変更依頼情報を抽出する
#-----------------------------------------------------------

--KEY:KU_SELECT_001

SELECT
    KU0111.SETTE_KOJI_NO
    ,KU0111.SETTE_KOJI_STAT
    ,KU0111.KOJIAK_NO
    ,KU0111.OLD_PCRS_CD
    ,KU0111.OLD_WRIB_TYPE_CD
    ,KU0111.NEW_PCRS_CD
    ,KU0111.NEW_WRIB_TYPE_CD
    ,KU0111.MANSION_COURSE_CD
    ,KU0111.MANS_RM_TUSHIN_KIKI_SBT_CD
    ,KU0111.SETTE_KOJI_RSV_YMD
    ,KU0111.UPD_TRGT_KOJIAK_NO
    ,KU0111.SVC_KEI_NO
    ,KU0111.SETTE_KOJI_RSLT_CD
    ,KU0111.SETTE_KOJI_UK_DTM
    ,KU0111.SETTE_KOJI_REQYMD
    ,KU0111.SETTE_KOJI_CD
    ,KU0111.SETTE_KOJI_YMD
    ,KU0111.SETTE_KOJI_FIN_YMD
    ,KU0111.SETTE_KOJI_CANCEL_DTM 
    ,KU0111.SETTE_KOJI_STP_YMD
    ,KU0111.RNKS_REQ_DTM
    ,KU0111.RNKS_RSP_DTM
    ,KU0111.RNKS_RENKEI_SBT_CD
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    KU0111.SETTE_KOJI_STAT = '130'
AND KU0111.SETTE_KOJI_CD = '1'
AND KU0111.MK_FLG = '0'
ORDER BY SETTE_KOJI_UK_DTM

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_002)
#--【イーサネット帯域変更結果反映】
#--イーサネット帯域変更結果を反映する対象を取得する
#-----------------------------------------------------------

--KEY:KU_SELECT_002

SELECT
    KU0111.SETTE_KOJI_NO
    ,KU0111.SETTE_KOJI_STAT
    ,KU0111.KOJIAK_NO
    ,KU0111.OLD_PCRS_CD
    ,KU0111.OLD_WRIB_TYPE_CD
    ,KU0111.NEW_PCRS_CD
    ,KU0111.NEW_WRIB_TYPE_CD
    ,KU0111.MANSION_COURSE_CD
    ,KU0111.MANS_RM_TUSHIN_KIKI_SBT_CD
    ,KU0111.SETTE_KOJI_RSV_YMD
    ,KU0111.UPD_TRGT_KOJIAK_NO
    ,KU0111.SVC_KEI_NO
    ,KU0111.SETTE_KOJI_RSLT_CD
    ,KU0111.SETTE_KOJI_UK_DTM
    ,KU0111.SETTE_KOJI_REQYMD
    ,KU0111.SETTE_KOJI_CD
    ,KU0111.SETTE_KOJI_YMD
    ,KU0111.SETTE_KOJI_FIN_YMD
    ,KU0111.SETTE_KOJI_CANCEL_DTM
    ,KU0111.SETTE_KOJI_STP_YMD
    ,KU0111.RNKS_REQ_DTM
    ,KU0111.RNKS_RSP_DTM
    ,KU0111.RNKS_RENKEI_SBT_CD
    ,KU0111.ADD_DTM
    ,KU0111.ADD_OPEACNT
    ,KU0111.UPD_DTM
    ,KU0111.UPD_OPEACNT
    ,KU0111.DEL_DTM
    ,KU0111.DEL_OPEACNT
    ,KU0111.MK_FLG
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    KU0111.KOJIAK_NO = ?
AND KU0111.OLD_PCRS_CD = ?
AND KU0111.NEW_PCRS_CD = ?
AND KU0111.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_003)
#--【ＦＣＤＳＯＬＴ設定変更依頼抽出】
#--OLT設定変更依頼を抽出する
#-----------------------------------------------------------

--KEY:KU_SELECT_003

SELECT
     KU0111.SETTE_KOJI_NO
    ,KU0111.SETTE_KOJI_STAT
    ,KU0111.KOJIAK_NO
    ,KU0111.OLD_PCRS_CD
    ,KU0111.OLD_WRIB_TYPE_CD
    ,KU0111.NEW_PCRS_CD
    ,KU0111.NEW_WRIB_TYPE_CD
    ,KU0111.MANSION_COURSE_CD
    ,KU0111.MANS_RM_TUSHIN_KIKI_SBT_CD
    ,KU0111.SETTE_KOJI_RSV_YMD
    ,KU0111.UPD_TRGT_KOJIAK_NO
    ,KU0111.SVC_KEI_NO
    ,KU0111.SETTE_KOJI_RSLT_CD
    ,KU0111.SETTE_KOJI_UK_DTM
    ,KU0111.SETTE_KOJI_REQYMD
    ,KU0111.SETTE_KOJI_CD
    ,KU0111.SETTE_KOJI_YMD
    ,KU0111.SETTE_KOJI_FIN_YMD
    ,KU0111.SETTE_KOJI_CANCEL_DTM 
    ,KU0111.SETTE_KOJI_STP_YMD
    ,KU0111.RNKS_REQ_DTM
    ,KU0111.RNKS_RSP_DTM
    ,KU0111.RNKS_RENKEI_SBT_CD
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    KU0111.SETTE_KOJI_STAT = '130'
AND KU0111.SETTE_KOJI_CD = '2'
AND KU0111.MK_FLG = '0'
ORDER BY SETTE_KOJI_UK_DTM

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_005)
#--【ＦＣＤＳＯＬＴ設定変更結果反映】
#--FCDSOLT設定変更結果を反映する対象を取得する
#-----------------------------------------------------------

--KEY:KU_SELECT_005

SELECT
    KU0111.SETTE_KOJI_NO
    ,KU0111.SETTE_KOJI_STAT
    ,KU0111.KOJIAK_NO
    ,KU0111.OLD_PCRS_CD
    ,KU0111.OLD_WRIB_TYPE_CD
    ,KU0111.NEW_PCRS_CD
    ,KU0111.NEW_WRIB_TYPE_CD
    ,KU0111.MANSION_COURSE_CD
    ,KU0111.MANS_RM_TUSHIN_KIKI_SBT_CD
    ,KU0111.SETTE_KOJI_RSV_YMD
    ,KU0111.UPD_TRGT_KOJIAK_NO
    ,KU0111.SVC_KEI_NO
    ,KU0111.SETTE_KOJI_RSLT_CD
    ,KU0111.SETTE_KOJI_UK_DTM
    ,KU0111.SETTE_KOJI_REQYMD
    ,KU0111.SETTE_KOJI_CD
    ,KU0111.SETTE_KOJI_YMD
    ,KU0111.SETTE_KOJI_FIN_YMD
    ,KU0111.SETTE_KOJI_CANCEL_DTM
    ,KU0111.SETTE_KOJI_STP_YMD
    ,KU0111.RNKS_REQ_DTM
    ,KU0111.RNKS_RSP_DTM
    ,KU0111.RNKS_RENKEI_SBT_CD
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    KU0111.KOJIAK_NO = ?
AND KU0111.OLD_PCRS_CD = ?
AND KU0111.NEW_PCRS_CD = ?
AND KU0111.SETTE_KOJI_STAT = '140'
AND (KU0111.SETTE_KOJI_RSLT_CD IS NULL
     OR KU0111.SETTE_KOJI_RSLT_CD = '2')
AND KU0111.SETTE_KOJI_FIN_YMD IS NULL
AND KU0111.MK_FLG = '0'
ORDER BY
    KU0111.SETTE_KOJI_UK_DTM DESC

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_006)
#--【マンション顧客情報抽出】
#--設定工事からマンション顧客情報を抽出する
#-----------------------------------------------------------

--KEY:KU_SELECT_006

SELECT
    KU0111.SETTE_KOJI_NO
    ,KU0111.SETTE_KOJI_STAT
    ,KU0111.KOJIAK_NO
    ,KU0111.OLD_PCRS_CD
    ,KU0111.OLD_WRIB_TYPE_CD
    ,KU0111.NEW_PCRS_CD
    ,KU0111.NEW_WRIB_TYPE_CD
    ,KU0111.MANSION_COURSE_CD
    ,KU0111.MANS_RM_TUSHIN_KIKI_SBT_CD
    ,KU0111.SETTE_KOJI_RSV_YMD
    ,KU0111.UPD_TRGT_KOJIAK_NO
    ,KU0111.SVC_KEI_NO
    ,KU0111.SETTE_KOJI_RSLT_CD
    ,KU0111.SETTE_KOJI_UK_DTM
    ,KU0111.SETTE_KOJI_REQYMD
    ,KU0111.SETTE_KOJI_CD
    ,KU0111.SETTE_KOJI_YMD
    ,KU0111.SETTE_KOJI_FIN_YMD
    ,KU0111.SETTE_KOJI_CANCEL_DTM 
    ,KU0111.SETTE_KOJI_STP_YMD
    ,KU0111.RNKS_REQ_DTM
    ,KU0111.RNKS_RSP_DTM
    ,KU0111.RNKS_RENKEI_SBT_CD
    ,CASE KU0111.SETTE_KOJI_STAT
        WHEN '130' THEN
            KU0111.SETTE_KOJI_UK_DTM
        WHEN '900' THEN
            KU0111.SETTE_KOJI_CANCEL_DTM
    END AS TAISHO_DTM
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    (
        (KU0111.SETTE_KOJI_STAT = '130'
        AND KU0111.SETTE_KOJI_CD = '0'
        AND KU0111.RNKS_RENKEI_SBT_CD = '0')
    OR
        (KU0111.SETTE_KOJI_STAT = '900'
        AND KU0111.SETTE_KOJI_CD = '0'
        AND KU0111.RNKS_RENKEI_SBT_CD = '1')
    )
AND KU0111.MK_FLG = '0'
ORDER BY TAISHO_DTM

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_007)
#--【マンション顧客変更情報抽出】
#--設定工事からマンション顧客変更情報を抽出する
#-----------------------------------------------------------

--KEY:KU_SELECT_007

SELECT
    KU0111.SETTE_KOJI_NO
    ,KU0111.SETTE_KOJI_STAT
    ,KU0111.KOJIAK_NO
    ,KU0111.OLD_PCRS_CD
    ,KU0111.OLD_WRIB_TYPE_CD
    ,KU0111.NEW_PCRS_CD
    ,KU0111.NEW_WRIB_TYPE_CD
    ,KU0111.MANSION_COURSE_CD
    ,KU0111.MANS_RM_TUSHIN_KIKI_SBT_CD
    ,KU0111.SETTE_KOJI_RSV_YMD
    ,KU0111.UPD_TRGT_KOJIAK_NO
    ,KU0111.SVC_KEI_NO
    ,KU0111.SETTE_KOJI_RSLT_CD
    ,KU0111.SETTE_KOJI_UK_DTM
    ,KU0111.SETTE_KOJI_REQYMD
    ,KU0111.SETTE_KOJI_CD
    ,KU0111.SETTE_KOJI_YMD
    ,KU0111.SETTE_KOJI_FIN_YMD
    ,KU0111.SETTE_KOJI_CANCEL_DTM
    ,KU0111.SETTE_KOJI_STP_YMD
    ,KU0111.RNKS_REQ_DTM
    ,KU0111.RNKS_RSP_DTM
    ,KU0111.RNKS_RENKEI_SBT_CD
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    KU0111.KOJIAK_NO = ?
AND
    KU0111.SETTE_KOJI_CD = '0'
AND
    KU0111.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_008)
#--【ＧｅｎｅｓｉｓＯＬＴ設定変更情報抽出】
#--旧料金コースを取得する
#-----------------------------------------------------------

--KEY:KU_SELECT_008

SELECT
     KU0111.SETTE_KOJI_NO
    ,KU0111.SETTE_KOJI_STAT
    ,KU0111.KOJIAK_NO
    ,KU0111.OLD_PCRS_CD
    ,KU0111.OLD_WRIB_TYPE_CD
    ,KU0111.NEW_PCRS_CD
    ,KU0111.NEW_WRIB_TYPE_CD
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    KU0111.KOJIAK_NO = ?
AND
    KU0111.SETTE_KOJI_CD = '2'
AND
    KU0111.SETTE_KOJI_STAT <> '900'
AND
    KU0111.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_009)
#--【強制解約工事案件作成】
#--強制解約対象となるサービス契約番号に紐づく設定工事を取得する。
#-----------------------------------------------------------

--KEY:KU_SELECT_009

SELECT
    SETTE_KOJI_NO,
    SETTE_KOJI_STAT,
    KOJIAK_NO,
    OLD_PCRS_CD,
    OLD_WRIB_TYPE_CD,
    NEW_PCRS_CD,
    NEW_WRIB_TYPE_CD,
    MANSION_COURSE_CD,
    MANS_RM_TUSHIN_KIKI_SBT_CD,
    SETTE_KOJI_RSV_YMD,
    UPD_TRGT_KOJIAK_NO,
    SVC_KEI_NO,
    SETTE_KOJI_RSLT_CD,
    SETTE_KOJI_UK_DTM,
    SETTE_KOJI_REQYMD,
    SETTE_KOJI_CD,
    SETTE_KOJI_YMD,
    SETTE_KOJI_FIN_YMD,
    SETTE_KOJI_CANCEL_DTM,
    SETTE_KOJI_STP_YMD
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    KU0111.KOJIAK_NO = ?
AND
    KU0111.SETTE_KOJI_STAT = '140'
AND
    KU0111.SETTE_KOJI_CD = '0'
AND
    KU0111.MK_FLG = '0'
ORDER BY
    KU0111.RNKS_REQ_DTM DESC

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KU_SELECT_010)
#--【解約情報反映】
#--キャンセル情報連携におけるマンション顧客情報の取得
#-----------------------------------------------------------

--KEY:KU_SELECT_010

SELECT
    KU0111.SETTE_KOJI_NO,
    KU0111.KOJIAK_NO,
    KU0111.SETTE_KOJI_STAT
FROM
    KU_T_SETTE_KOJI KU0111
WHERE
    KU0111.SETTE_KOJI_CD = '0'
AND
    KU0111.RNKS_RENKEI_SBT_CD = '1'
AND
    KU0111.MK_FLG = '0'
AND
    EXISTS (SELECT
                1
            FROM
                KU_T_SVKEI_KOJIAK KU0081 
            INNER JOIN
                KU_T_KOJIAK KU0011
            ON
                KU0011.KOJIAK_NO = KU0081.KOJIAK_NO
            AND
                KU0011.KOJIAK_SBT_CD IN ('001','003')
            AND
                KU0011.MK_FLG = '0'
            WHERE
                KU0081.KOJIAK_NO = KU0111.KOJIAK_NO
            AND
                KU0081.SVC_KEI_NO = ?
            AND
                KU0081.MK_FLG = '0')
ORDER BY
    KU0111.RNKS_REQ_DTM DESC

#-

#-----------------------------------------------------------
#--設定工事(KU_T_SETTE_KOJI-KK_SELECT_001)
#--KKPRC02201, KKPRC02202
#--「設定工事完了年月日」
#--取得
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
 KU0111.SETTE_KOJI_NO,
 KU0111.SETTE_KOJI_FIN_YMD
FROM
 KU_T_SETTE_KOJI  KU0111
WHERE
 KU0111.KOJIAK_NO = ? AND
 KU0111.MK_FLG = '0'
ORDER BY KU0111.SETTE_KOJI_NO DESC

#-