#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-AC_SELECT_001)
#--対象サービス契約内訳抽出
#--基準日が"番ポ工事完了年月日"の場合
#-----------------------------------------------------------

--KEY:AC_SELECT_001

SELECT
    TU0011.SVC_KEI_UCWK_NO,
    TU0011.NTT_KEI_TEL_KAISEN_NO,
    TU0011.BMP_KOJI_FIN_YMD
FROM
    TU_T_BMP_KOJI TU0011
WHERE
  TU0011.GENE_ADD_DTM = 
    (SELECT
        MAX(TU0011_GENE.GENE_ADD_DTM) AS TU0011_MAX
       FROM TU_T_BMP_KOJI TU0011_GENE
      WHERE TU0011_GENE.BMP_KOJI_NO           = TU0011.BMP_KOJI_NO
        AND TU0011_GENE.NTT_KEI_TEL_KAISEN_NO = TU0011.NTT_KEI_TEL_KAISEN_NO
        AND TU0011_GENE.MK_FLG = '0')
  AND TU0011.MK_FLG         = '0'

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_001)
#--世代登録年月日時分秒を問わず、法人に未送信で結果確認済みの番ポ工事情報を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_001

SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM,
    BMP.NTT_REQ_SKBT_NO,
    BMP.HJIN_EO_YKAE_SVKEI_NO,
    BMP.NTT_KEI_TEL_KAISEN_NO,
    BMP.BMP_MSKMSHO_TYPE_CD,
    BMP.NTT_UK_YMD,
    BMP.NTT_UK_NO,
    BMP.TK_KH_ANS_YMD,
    BMP.BMP_KOJI_KH,
    BMP.TK_FAIL_RSN_CD,
    BMP.TK_FAIL_RSN_MEMO,
    BMP.BMP_KOJI_FIX_YMDH,
    BMP.BMPKJ_FIX_TIME_CD,
    BMP.NTT_TNTSHA_NM,
    BMP.NTT_TELNO,
    BMP.NTT_FAX_NO,
    BMP.NTT_SNAI_KOJI_UM,
    BMP.NTT_SGAI_KOJI_UM,
    BMP.NTT_KIJIRAN,
    BMP.TOGO_ODR_ID,
    BMP.HUKUSU_KAISEN_GRP_ID,
    BMP.ITENS_MSKM_DAY,
    BMP.SOTEI_SWITCH_TIMEZ,
    BMP.BMP_ORDER_NO,
    BMP.CHUMN_KIND,
    BMP.ITNM_ODKH_JDGTK_KH_KTDT,
    BMP.ITNM_KIJIRAN_ITENS_JGSMK,
    BMP.ITNM_ODKH_JDGTK_KH,
    BMP.ITNM_ODKH_JDGTK_FRSN,
    BMP.ITNM_ODKH_JDGTK_FRSN_OTR,
    BMP.ITNM_UK_TNT_NM,
    BMP.ITNM_UK_TNT_TELNO,
    BMP.ITNM_KOJI_TNT_NM,
    BMP.ITNM_KOJI_TNT_TELNO,
    BMP.NOSK_ODKH_JDGTK_KH_KTDT,
    BMP.NOSK_KIJIRAN_ITENS_JGSMK,
    BMP.NOSK_ODKH_JDGTK_KH,
    BMP.NOSK_ODKH_JDGTK_FRSN,
    BMP.NOSK_ODKH_JDGTK_FRSN_OTR,
    BMP.NOSK_UK_TNT_NM,
    BMP.NOSK_UK_TNT_TELNO,
    BMP.NOSK_KOJI_TNT_NM,
    BMP.NOSK_KOJI_TNT_TELNO,
    BMP.ITNM_ANS_WAY_SKBT,
    BMP.NOSK_ANS_WAY_SKBT,
    BMP.NAIBU_SKBT_NO,
    BMP.CHGE_MOTO_ORDER_SKBT_NO,
    BMP.MSKMJIG_USE_CD,
    BMP.ITNM_TK_KH_KTDT,
    BMP.ITNM_TK_KH,
    BMP.ITNM_TK_FRSN,
    BMP.ITNM_TK_FRSN_OTR_RSN,
    BMP.NOSK_TK_KH_KTDT,
    BMP.NOSK_TK_KH,
    BMP.NOSK_TK_FRSN,
    BMP.NOSK_TK_FRSN_OTR_RSN
FROM
    TU_T_BMP_KOJI BMP
WHERE
   BMP.HOJIN_KOJIN_CD = '2' AND
   BMP.HOJIN_RSLT_OPUT_ZUMI_FLG = '0' AND
   BMP.BMP_KOJI_STAT IN ( '023', '031' ) AND
   BMP.MK_FLG = '0'
ORDER BY
   BMP.NTT_REQ_SKBT_NO ASC
FOR UPDATE

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_002)
#--番ポ工事ステータス、申込事業者使用コードから番ポ工事番号、世代登録年月日時分秒を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_002

SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM,
    BMP.NTT_KEI_TEL_KAISEN_NO,
    BMP.BMP_KOJI_KIBO_YMDH,
    BMP.BMPKJ_KIBO_TIME_CD
FROM
    TU_T_BMP_KOJI BMP
WHERE
    BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0') AND
    BMP.MK_FLG = '0' AND
    BMP.BMP_KOJI_STAT = ? AND
    BMP.MSKMJIG_USE_CD = ?
FOR UPDATE

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_003)
#--レコードロック用SQL
#-----------------------------------------------------------

--KEY:TU_SELECT_003

SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM
FROM
    TU_T_BMP_KOJI BMP
WHERE
    BMP.BMP_KOJI_NO  = ? 
AND BMP.GENE_ADD_DTM = ?
AND BMP.GENE_ADD_DTM = (SELECT
                            MAX(BMP_GENE.GENE_ADD_DTM)
                        FROM
                            TU_T_BMP_KOJI BMP_GENE
                        WHERE
                            BMP_GENE.BMP_KOJI_NO = BMP.BMP_KOJI_NO  AND
                            BMP_GENE.MK_FLG = '0'  
                       )
AND BMP.BMP_KOJI_STAT IN ('031', '032')
AND BMP.BMP_MSKMSHO_TYPE_CD IN ('1', '2')
AND BMP.BMP_KOJI_FIX_YMDH <= ?
FOR UPDATE

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_004)
#--番ポ廃止対象のデータをサービス終了年月日が一番過去から40件分取得
#-----------------------------------------------------------

--KEY:TU_SELECT_004

SELECT
    *
FROM
    (SELECT
        BMP.SVC_KEI_UCWK_NO,
        BMP.HOJIN_SVC_KEI_UK_NO,
        BMP.HJIN_EO_YKAE_SVKEI_NO,
        BMP.HOJIN_KOJIN_CD,
        BMP.NTT_KEI_TEL_KAISEN_NO,
        BMP.TA_JGYOSHA_UR_NO,
        BMP.ROUTING_NO,
        BMP.NTT_KEISHA_NM,
        BMP.NTT_KSH_KANA,
        BMP.NTT_KEISHA_AD_CD,
        BMP.NTT_KEISHA_PCD,
        BMP.NTT_KEISHA_STATE_NM,
        BMP.NTT_KEISHA_CITY_NM,
        BMP.NTT_KEISHA_OAZTSU_NM,
        BMP.NTT_KEISHA_AZCHO_NM,
        BMP.NTT_KEISHA_BNCHIGO,
        BMP.NTT_KEISHA_ADRTTM,
        BMP.NTT_KEISHA_ADRRM,
        BMP.NKYS_AD_MAN_INPUT_FLG,
        BMP.RRKS_TELNO,
        BMP.SETPLACE_AD_CD,
        BMP.SETPLACE_PCD,
        BMP.SETPLACE_STATE_NM,
        BMP.SETPLACE_CITY_NM,
        BMP.SETPLACE_OAZTSU_NM,
        BMP.SETPLACE_AZCHO_NM,
        BMP.SETPLACE_BNCHIGO,
        BMP.SETPLACE_ADRTTM,
        BMP.SETPLACE_ADRRM,
        BMP.SETPLACE_AD_MAN_INPUT_FLG,
        BMP.BMP_UK_TNT_USER_ID,
        BMP.ITNM_TSJGS_CD,
        BMP.ITNM_TSJGS_KTN_CD,
        BMP.MSKMJIG_USE_CD,
        BMP.BMPKJ_MSKM_MT_TSJGS_CD,
        BMP.MSKMJIG_SNAI_KOJI_UM,
        BMP.MSKMJIG_SGAI_KOJI_UM,
        BMP.BMP_MSKM_JIGSHO_NO,
        BMP.BMP_KOJI_MSKM_JIGSHO_NO,
        ROWNUM
    FROM
        TU_T_BMP_KOJI BMP,
        KK_T_SVC_KEI_UCWK UCWK,
        KK_T_SVKEIUW_EOH_TEL UCWK_TEL
    WHERE
        UCWK_TEL.BMP_UM = '1'
    AND UCWK_TEL.MK_FLG = '0'
    AND UCWK.SVC_ENDYMD <= ?
    AND UCWK.SVC_KEI_UCWK_STAT = '910'
    AND (UCWK.SVC_KEI_UCWK_NO, UCWK.RSV_APLY_YMD || UCWK.GENE_ADD_DTM) = 
        (SELECT
            MAXUCWK.SVC_KEI_UCWK_NO,
            MAX(MAXUCWK.RSV_APLY_YMD || MAXUCWK.GENE_ADD_DTM) 
        FROM
            KK_T_SVC_KEI_UCWK MAXUCWK 
        WHERE
            UCWK.SVC_KEI_UCWK_NO = MAXUCWK.SVC_KEI_UCWK_NO
        AND MAXUCWK.RSV_APLY_YMD <= ?
        AND MAXUCWK.RSV_APLY_CD = '2'
        AND MAXUCWK.MK_FLG = '0'
        GROUP BY
            MAXUCWK.SVC_KEI_UCWK_NO)
    AND UCWK.MK_FLG = '0'
    AND BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0')
    AND BMP.BMP_KOJI_STAT = '050'
    AND BMP.MK_FLG = '0'
    AND BMP.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO
    AND UCWK.SVC_KEI_UCWK_NO = UCWK_TEL.SVC_KEI_UCWK_NO
    AND UCWK.GENE_ADD_DTM = UCWK_TEL.GENE_ADD_DTM
    AND NOT EXISTS (SELECT
            1
        FROM
            TU_T_JGSHAK_RTRN JGSHAK
        WHERE
            JGSHAK.MK_FLG = '0'
        AND BMP.SVC_KEI_UCWK_NO = JGSHAK.SVC_KEI_UCWK_NO
        )
    AND NOT EXISTS (SELECT
            1
        FROM
            TU_T_BMP_KOJI HAISHI_BMP
        WHERE
            HAISHI_BMP.BMP_MSKMSHO_TYPE_CD = '3'
        AND HAISHI_BMP.MK_FLG = '0'
        AND UCWK_TEL.SVC_KEI_UCWK_NO = HAISHI_BMP.SVC_KEI_UCWK_NO
        AND UCWK_TEL.TELNO = HAISHI_BMP.NTT_KEI_TEL_KAISEN_NO)
    ORDER BY
        UCWK.SVC_ENDYMD ASC
    ) SUB_Q
WHERE
    ROWNUM <= 40

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_005)
#--番ポ工事完了対象のデータを取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_005

SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM,
    BMP.SVC_KEI_UCWK_NO
    ,BMP.NTT_KEI_TEL_KAISEN_NO
    ,BMP.HOJIN_KOJIN_CD
    ,BMP.BMP_MSKMSHO_TYPE_CD
    ,BMP.BMP_KOJI_STAT
FROM
    TU_T_BMP_KOJI BMP
WHERE
    BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0') AND
    ((BMP.BMP_KOJI_STAT = '040' AND BMP.BMP_MSKMSHO_TYPE_CD <> '3') OR
     (BMP.BMP_KOJI_STAT = '031' AND BMP.BMP_MSKMSHO_TYPE_CD =  '3') OR
     (BMP.BMP_KOJI_STAT = '031' AND BMP.HOJIN_KOJIN_CD = '2')) AND
    BMP.MK_FLG = '0' AND
    SUBSTR(BMP.BMP_KOJI_FIX_YMDH, 1, 8) <= ?
FOR UPDATE

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_006)
#--番ポ申込書タイプコードが”移転”または”変更”で同時工事有無が”有”且つ番ポ工事ステータスが”工事実施依頼待ち”または番ポ工事ステータスが”工事実施依頼済み”で番ポ工事確定年月日時が処理時刻＋3時間を含む過去のデータを取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_006

SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM,
    BMP.HOJIN_KOJIN_CD,
    SK.SVC_KEI_NO,
    SK.SYSID,
    UCWK.SVC_KEI_UCWK_NO,
    UCWK.GENE_ADD_DTM AS GENE_ADD_DTM_SK,
    UCWK.SVC_KEI_UCWK_STAT
FROM
    TU_T_BMP_KOJI BMP,
    KK_T_SVC_KEI SK,
    KK_T_SVC_KEI_UCWK UCWK
WHERE
    BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0')
AND BMP.BMP_KOJI_STAT IN ('031', '032')
AND BMP.BMP_MSKMSHO_TYPE_CD IN ('1', '2')
AND BMP.BMP_KOJI_FIX_YMDH <= ?
AND BMP.HOJIN_KOJIN_CD = '1'
AND BMP.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO
AND UCWK.KEIZK_AF_KEI_CHGECHU_FLG = '0'
AND UCWK.RSV_APLY_YMD || UCWK.GENE_ADD_DTM = 
    (SELECT
        MAX(MAXUCWK.RSV_APLY_YMD || MAXUCWK.GENE_ADD_DTM) 
    FROM
        KK_T_SVC_KEI_UCWK MAXUCWK 
    WHERE
        UCWK.SVC_KEI_UCWK_NO = MAXUCWK.SVC_KEI_UCWK_NO
    AND MAXUCWK.RSV_APLY_YMD <= ?
    AND MAXUCWK.RSV_APLY_CD = '2'
    AND MAXUCWK.MK_FLG = '0')
AND SK.SVC_KEI_NO = UCWK.SVC_KEI_NO
AND SK.RSV_APLY_YMD || SK.GENE_ADD_DTM = 
    (SELECT
        MAX(MAXSK.RSV_APLY_YMD || MAXSK.GENE_ADD_DTM) 
    FROM
        KK_T_SVC_KEI MAXSK 
    WHERE
        SK.SVC_KEI_NO = MAXSK.SVC_KEI_NO
    AND MAXSK.RSV_APLY_YMD <= ?
    AND MAXSK.RSV_APLY_CD = '2'
    AND MAXSK.MK_FLG = '0')
AND NOT EXISTS(SELECT
                   1
               FROM
                   KK_T_ADCHG_DTL ACD1
               INNER JOIN KK_T_ADCHG_DTL ACD2
               ON  ACD1.ADCHG_NO = ACD2.ADCHG_NO
               AND ACD2.ADCHG_DTL_SBT_CD = '01'
               AND ACD2.CHBF_SKBT_NO IS NOT NULL
               INNER JOIN KK_T_SVC_KEI SKSUB
               ON ACD2.CHAF_SKBT_NO = SKSUB.SVC_KEI_NO
               AND SKSUB.SVC_CD = '02'
               WHERE
                   ACD1.ADCHG_DTL_SBT_CD = '02'
               AND ACD1.CHAF_SKBT_NO = BMP.SVC_KEI_UCWK_NO
               AND SKSUB.RSV_APLY_YMD || SKSUB.GENE_ADD_DTM = 
                   (SELECT
                       MAX(MAXSKSUB.RSV_APLY_YMD || MAXSKSUB.GENE_ADD_DTM) 
                   FROM
                       KK_T_SVC_KEI MAXSKSUB
                   WHERE
                       SKSUB.SVC_KEI_NO = MAXSKSUB.SVC_KEI_NO
                   AND MAXSKSUB.RSV_APLY_YMD <= ?
                   AND MAXSKSUB.RSV_APLY_CD = '2'
                   AND MAXSKSUB.MK_FLG = '0')
               AND ACD1.MK_FLG = '0'
               AND ACD2.MK_FLG = '0')
AND BMP.MK_FLG = '0'
AND UCWK.MK_FLG = '0'
AND SK.MK_FLG = '0'
UNION ALL
SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM,
    BMP.HOJIN_KOJIN_CD,
    NULL AS SVC_KEI_NO,
    NULL AS SYSID,
    NULL AS SVC_KEI_UCWK_NO,
    NULL AS GENE_ADD_DTM_SK,
    NULL AS SVC_KEI_UCWK_STAT
FROM
    TU_T_BMP_KOJI BMP
WHERE
    BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0')
AND BMP.BMP_KOJI_STAT IN ('031', '032')
AND BMP.BMP_MSKMSHO_TYPE_CD IN ('1', '2')
AND BMP.BMP_KOJI_FIX_YMDH <= ?
AND BMP.HOJIN_KOJIN_CD = '2'
AND BMP.MK_FLG = '0'
ORDER BY
    BMP_KOJI_NO ASC,
    GENE_ADD_DTM ASC

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_007)
#--法人eo読替サービス契約番号,番ポ申込書タイプコード,お客様申込年月日,NTT契約電話回線番号より番ポ工事情報を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_007

SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM
FROM
    TU_T_BMP_KOJI BMP
WHERE
   BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0') AND
   BMP.HJIN_EO_YKAE_SVKEI_NO = ? AND
   BMP.BMP_MSKMSHO_TYPE_CD = ? AND
   BMP.CUST_MSKM_YMD = ? AND
   BMP.NTT_KEI_TEL_KAISEN_NO = ? AND
   BMP.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_008)
#--運用日+1と運用日+31を含む範囲内を条件に番ポ工事希望年月日時を年月日と時間で集約したデータ件数を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_008

SELECT
    KOJI.BMP_KOJI_KIBO_YMD,
    KOJI.BMP_KOJI_KIBO_H,
    COUNT(*) AS BMP_KOJI_KIBO_H_CNT
FROM
    (SELECT
        SUBSTR(KOJI.BMP_KOJI_KIBO_YMDH, 1, 8) AS BMP_KOJI_KIBO_YMD,
        SUBSTR(KOJI.BMP_KOJI_KIBO_YMDH, 9, 2) AS BMP_KOJI_KIBO_H
    FROM
        TU_T_BMP_KOJI KOJI
    WHERE
        KOJI.MK_FLG = '0' AND
        KOJI.BMP_KOJI_KIBO_YMDH BETWEEN ? AND ? AND
        KOJI.GENE_ADD_DTM = (SELECT MAX(MAXKOJI.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXKOJI WHERE KOJI.BMP_KOJI_NO = MAXKOJI.BMP_KOJI_NO AND MAXKOJI.MK_FLG = '0')
    ) KOJI
GROUP BY
    KOJI.BMP_KOJI_KIBO_YMD,
    KOJI.BMP_KOJI_KIBO_H
ORDER BY
    KOJI.BMP_KOJI_KIBO_YMD ASC, 
    KOJI.BMP_KOJI_KIBO_H ASC

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_009)
#--運用日+1と運用日+31を含む範囲内を条件に番ポ工事確定年月日時を年月日と時間で集約したデータ件数を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_009

SELECT
    KOJI.BMP_KOJI_FIX_YMD,
    KOJI.BMP_KOJI_FIX_H,
    COUNT(*) AS BMP_KOJI_FIX_H_CNT
FROM
    (SELECT
        SUBSTR(KOJI.BMP_KOJI_FIX_YMDH, 1, 8) AS BMP_KOJI_FIX_YMD,
        SUBSTR(KOJI.BMP_KOJI_FIX_YMDH, 9, 2) AS BMP_KOJI_FIX_H
    FROM
        TU_T_BMP_KOJI KOJI
    WHERE
        KOJI.MK_FLG = '0' AND
        KOJI.BMP_KOJI_FIX_YMDH BETWEEN ? AND ? AND
        KOJI.GENE_ADD_DTM = (SELECT MAX(MAXKOJI.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXKOJI WHERE KOJI.BMP_KOJI_NO = MAXKOJI.BMP_KOJI_NO AND MAXKOJI.MK_FLG = '0')
    ) KOJI
GROUP BY
    KOJI.BMP_KOJI_FIX_YMD,
    KOJI.BMP_KOJI_FIX_H
ORDER BY
    KOJI.BMP_KOJI_FIX_YMD ASC,
    KOJI.BMP_KOJI_FIX_H ASC

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_010)
#--番ポ工事未実施のサービス契約番号、番ポ工事希望年月日時が同じ2件以上のサービス契約番号、番ポ工事希望年月日時を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_010

SELECT
    SKU_Q.SVC_KEI_NO,
    SKU_Q.BMP_KOJI_KIBO_YMDH
FROM
(
    SELECT /*+ LEADING(BMP SKU) INDEX(BMP) */
        SKU.SVC_KEI_NO,
        BMP.BMP_KOJI_KIBO_YMDH,
        COUNT(*) AS CNT
    FROM
        TU_T_BMP_KOJI BMP,
        KK_T_SVC_KEI_UCWK SKU
    WHERE
        BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0')
    AND BMP.HOJIN_KOJIN_CD = '1'
    AND BMP.BMP_KOJI_STAT IN ('010', '011', '012', '021', '022', '031')
    AND BMP.MK_FLG = '0'
    AND BMP.SVC_KEI_UCWK_NO = SKU.SVC_KEI_UCWK_NO
    AND (SKU.RSV_APLY_YMD || SKU.GENE_ADD_DTM) = 
        (
        SELECT
            MAX(MAXSKU.RSV_APLY_YMD || MAXSKU.GENE_ADD_DTM) 
        FROM
            KK_T_SVC_KEI_UCWK MAXSKU 
        WHERE
            SKU.SVC_KEI_UCWK_NO = MAXSKU.SVC_KEI_UCWK_NO
        AND MAXSKU.RSV_APLY_YMD <= ?
        AND MAXSKU.RSV_APLY_CD = '2'
        AND MAXSKU.MK_FLG = '0'
        )
    AND SKU.MK_FLG = '0'
    GROUP BY
        SKU.SVC_KEI_NO,
        BMP.BMP_KOJI_KIBO_YMDH
    ) SKU_Q
WHERE
    SKU_Q.CNT >= 2
ORDER BY
    SKU_Q.BMP_KOJI_KIBO_YMDH ASC,
    SKU_Q.SVC_KEI_NO ASC

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_011)
#--サービス契約番号、番ポ工事希望日時から2番号情報を取得する
#-----------------------------------------------------------

--KEY:TU_SELECT_011

SELECT
    BMP_Q.NTT_KEI_TEL_KAISEN_NO,
    CUST.CUST_NM,
    BMP_Q.MSKM_JIGSHONM,
    BMP_Q.MKM_JIGSHO_TNTSHA_RRKS_TLN,
    BMP_Q.MKM_JIGSHO_TNTSHA_FAX_NO,
    BMP_Q.TSJGS_NM
FROM
    KK_T_SVC_KEI SK,
    KK_T_SVC_KEI_UCWK SKU,
    (SELECT
        BMP.SVC_KEI_UCWK_NO,
        BMP.NTT_KEI_TEL_KAISEN_NO,
        MJ.MSKM_JIGSHONM,
        MJ.MKM_JIGSHO_TNTSHA_RRKS_TLN,
        MJ.MKM_JIGSHO_TNTSHA_FAX_NO,
        TJ.TSJGS_NM
     FROM 
        TU_T_BMP_KOJI BMP,
        TU_M_MSKM_JIGSHO MJ,
        TU_M_TUSHIN_JGYOSHA TJ
     WHERE
        BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0') AND
        BMP.BMP_KOJI_STAT IN ('010', '011', '012', '021', '022', '031') AND
        BMP.BMP_KOJI_MSKM_JIGSHO_NO = MJ.MSKM_JIGSHO_NO(+) AND
        BMP.BMP_KOJI_KIBO_YMDH = ? AND
        BMP.ITNM_TSJGS_CD = TJ.TSJGS_CD(+) AND
        BMP.MK_FLG = '0' AND
        MJ.MK_FLG(+) = '0' AND
        TJ.MK_FLG(+) = '0'
    ) BMP_Q,
    CK_T_CUST CUST
WHERE
    SK.SVC_KEI_NO = ? AND
    SK.SVC_KEI_NO = SKU.SVC_KEI_NO AND
    SK.RSV_APLY_YMD || SK.GENE_ADD_DTM = 
        (
        SELECT MAX(MAXSK.RSV_APLY_YMD || MAXSK.GENE_ADD_DTM) 
        FROM KK_T_SVC_KEI MAXSK 
        WHERE SK.SVC_KEI_NO = MAXSK.SVC_KEI_NO AND 
            MAXSK.RSV_APLY_YMD <= ? AND 
            MAXSK.RSV_APLY_CD = '2' AND
            MAXSK.MK_FLG = '0'
        ) AND
    SKU.RSV_APLY_YMD || SKU.GENE_ADD_DTM = 
        (
        SELECT MAX(MAXSKU.RSV_APLY_YMD || MAXSKU.GENE_ADD_DTM) 
        FROM KK_T_SVC_KEI_UCWK MAXSKU 
        WHERE SKU.SVC_KEI_UCWK_NO = MAXSKU.SVC_KEI_UCWK_NO AND 
            MAXSKU.RSV_APLY_YMD <= ? AND 
            MAXSKU.RSV_APLY_CD = '2' AND
            MAXSKU.MK_FLG = '0'
        ) AND
    SK.SYSID = CUST.SYSID AND
    CUST.RSV_APLY_YMD || CUST.GENE_ADD_DTM = 
        (
        SELECT MAX(MAXCUST.RSV_APLY_YMD || MAXCUST.GENE_ADD_DTM) 
        FROM CK_T_CUST MAXCUST 
        WHERE CUST.SYSID = MAXCUST.SYSID AND 
            MAXCUST.RSV_APLY_YMD <= ? AND 
            MAXCUST.RSV_APLY_CD = '2' AND
            MAXCUST.MK_FLG = '0'
        ) AND
    SKU.SVC_KEI_UCWK_NO = BMP_Q.SVC_KEI_UCWK_NO AND
    SK.MK_FLG = '0' AND
    SKU.MK_FLG = '0' AND
    CUST.MK_FLG = '0'
ORDER BY
    SKU.SVC_KEI_UCWK_NO ASC

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_012)
#--世代登録年月日時分秒を問わず、番ポ工事ステータスがNTT申請済みで対応記録連携情報作成済フラグが未連携のデータを抽出する。
#-----------------------------------------------------------

--KEY:TU_SELECT_012

SELECT /*+ LEADING(BMP) INDEX(BMP TU_T_BMP_KOJI_IDX_16) */
    BMP.SVC_KEI_UCWK_NO,
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM,
    BMP.BMP_UK_TNT_USER_ID,
    BMP.NTT_RNKI_WAY_CD,
	NVL(TJ.TSJGS_NM,' ') AS TSJGS_NM
FROM
    TU_T_BMP_KOJI BMP,
    TU_M_MSKM_JIGSHO BMP_MJ,
    TU_M_MSKM_JIGSHO BMP_KOJI_MJ,
	TU_M_TUSHIN_JGYOSHA TJ
WHERE
    BMP.BMP_KOJI_STAT IN ('022', '023', '031', '032', '040', '050')
AND BMP.TIOKR_RNK_INF_SKSI_ZM_FLG = '0'
AND BMP.BMP_MSKM_JIGSHO_NO = BMP_MJ.MSKM_JIGSHO_NO(+)
AND BMP.BMP_KOJI_MSKM_JIGSHO_NO = BMP_KOJI_MJ.MSKM_JIGSHO_NO(+)
AND BMP.ITNM_TSJGS_CD = TJ.TSJGS_CD(+)
AND BMP.MK_FLG = '0'
AND BMP_MJ.MK_FLG(+) = '0'
AND BMP_KOJI_MJ.MK_FLG(+) = '0'
AND TJ.MK_FLG(+) = '0'
FOR UPDATE OF BMP.BMP_KOJI_NO

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_013)
#--番ポ工事ステータスが"工事希望日未入力"または、申込区分が「移転」で番ポ工事ステータスがＮＴＴ申請前（"NTT申請待ち"）または、申込区分が「変更」で番ポ工事ステータスが他事業者依頼前（"他事業者確認要"、"他事業者依頼待ち"）で、同番移転判定コードが"判定中"以外で業務パラメータ設定された値が移転元通信事業者コードに存在しないデータ抽出する。
#-----------------------------------------------------------

--KEY:TU_SELECT_013

SELECT
    TU0011.BMP_KOJI_NO,
    TU0011.GENE_ADD_DTM,
    TU0011.BMP_KOJI_STAT,
    TU0011.BMP_MSKMSHO_TYPE_CD,
    TU0011.SETPLACE_PCD,
    TU0011.SETPLACE_STATE_NM,
    TU0011.SETPLACE_CITY_NM,
    TU0011.ITNM_TSJGS_KTN_CD,
    TU0011.TNKJ_RSVD_HANEI_ZM_FLG,
    TU0011.KIJIRAN_1_ITNM_JGSMK,
    TU0011.KIJIRAN_1_NOSK_JGSMK,
    TU0011.KOJI_JSSI_WAY,
    TU0011.SOTEI_SWITCH_TIMEZ
FROM
    TU_T_BMP_KOJI TU0011,
    KK_T_SVC_KEI_UCWK KK0161,
    KK_T_SVKEIUW_EOH_TEL KK0191
WHERE
    TU0011.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = 
    (SELECT
        MAXKK0161.SVC_KEI_UCWK_NO,
        MAX(MAXKK0161.RSV_APLY_YMD || MAXKK0161.GENE_ADD_DTM) 
    FROM
        KK_T_SVC_KEI_UCWK MAXKK0161 
    WHERE
        KK0161.SVC_KEI_UCWK_NO = MAXKK0161.SVC_KEI_UCWK_NO
    AND MAXKK0161.RSV_APLY_YMD <= ?
    AND MAXKK0161.RSV_APLY_CD = '2'
    AND MAXKK0161.MK_FLG = '0'
    GROUP BY
        MAXKK0161.SVC_KEI_UCWK_NO)
AND KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO
AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM
AND TU0011.NTT_KEI_TEL_KAISEN_NO = ?
AND ((TU0011.BMP_KOJI_STAT = '000')
    OR
    (TU0011.BMP_KOJI_STAT = '021' AND TU0011.BMP_MSKMSHO_TYPE_CD = '1')
    OR
    (TU0011.BMP_KOJI_STAT = '010' AND TU0011.BMP_MSKMSHO_TYPE_CD = '1'))
AND KK0191.DOBANITEN_JDG_CD <> '3'
AND TU0011.MK_FLG = '0'
AND KK0161.MK_FLG = '0'
AND KK0191.MK_FLG = '0'
AND (TU0011.BMP_KOJI_NO, TU0011.GENE_ADD_DTM) = 
    (SELECT
        MAXTU0011.BMP_KOJI_NO,
        MAX(MAXTU0011.GENE_ADD_DTM)
    FROM
        TU_T_BMP_KOJI MAXTU0011
    WHERE
        TU0011.BMP_KOJI_NO = MAXTU0011.BMP_KOJI_NO
    AND MAXTU0011.MK_FLG = '0'
    GROUP BY
        MAXTU0011.BMP_KOJI_NO)
AND NOT EXISTS(
    SELECT
        1
    FROM
        ZM_M_WORK_PARAM_KNRI ZM0321
    WHERE
        ZM0321.WORK_PARAM_ID LIKE 'TU_RNKFAIL_TSJGS_CD%'
    AND ? BETWEEN ZM0321.WORK_PARAM_TSTAYMD AND ZM0321.WORK_PARAM_TENDYMD
    AND ZM0321.MK_FLG = '0'
    AND TU0011.ITNM_TSJGS_CD = ZM0321.WORK_PARAM_SETTE_VALUE
   )
FOR UPDATE OF TU0011.BMP_KOJI_NO

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_014)
#--電話番号から番ポ工事情報を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_014

SELECT
  TU0011.BMP_UK_TNT_USER_ID
  , KK0161.SVC_KEI_NO
  , TU0051.TSJGS_NM 
FROM
  ( 
    SELECT
      TU0011.SVC_KEI_UCWK_NO
      , TU0011.BMP_UK_TNT_USER_ID
      , TU0011.ITNM_TSJGS_CD 
    FROM
      TU_T_BMP_KOJI TU0011 
    WHERE
      TU0011.NTT_KEI_TEL_KAISEN_NO = ? 
      AND TU0011.BMP_KOJI_STAT NOT IN ('050', '090') 
      AND TU0011.GENE_ADD_DTM = ( 
        SELECT
          MAX(MAXTU0011.GENE_ADD_DTM) 
        FROM
          TU_T_BMP_KOJI MAXTU0011 
        WHERE
          TU0011.BMP_KOJI_NO = MAXTU0011.BMP_KOJI_NO 
          AND MAXTU0011.MK_FLG = '0'
      ) 
      AND TU0011.MK_FLG = '0'
  ) TU0011 
  LEFT OUTER JOIN TU_M_TUSHIN_JGYOSHA TU0051 
    ON TU0051.TSJGS_CD = TU0011.ITNM_TSJGS_CD 
    AND TU0051.MK_FLG = '0'
  , KK_T_SVC_KEI_UCWK KK0161 
WHERE
  TU0011.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO 
  AND KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = ( 
    SELECT
      MAX(MAXUCWK.RSV_APLY_YMD || MAXUCWK.GENE_ADD_DTM) 
    FROM
      KK_T_SVC_KEI_UCWK MAXUCWK 
    WHERE
      KK0161.SVC_KEI_UCWK_NO = MAXUCWK.SVC_KEI_UCWK_NO 
      AND MAXUCWK.RSV_APLY_YMD <= ? 
      AND MAXUCWK.RSV_APLY_CD = '2' 
      AND MAXUCWK.MK_FLG = '0'
  ) 
  AND EXISTS ( 
    SELECT
      1 
    FROM
      KK_T_SVKEIUW_EOH_TEL KK0191 
    WHERE
      KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO 
      AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM
  ) 
  AND KK0161.MK_FLG = '0'



#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_015)
#--番ポ工事ステータス、申込事業者使用コードから番ポ工事情報、サービス契約内訳情報、通信事業者情報を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_015

SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM,
    BMP.NTT_KEI_TEL_KAISEN_NO,
    BMP.NTT_KEISHA_NM,
    BMP.BMP_UK_TNT_USER_ID,
    UCWK.SVC_KEI_NO,
    (SELECT
        TJ.TSJGS_NM
    FROM
        TU_M_TUSHIN_JGYOSHA TJ
    WHERE
        BMP.ITNM_TSJGS_CD = TJ.TSJGS_CD
    AND TJ.MK_FLG = '0') AS TSJGS_NM,
    BMP.ITNM_TSJGS_CD,
    BMP.KOJI_JSSI_WAY,
    (SELECT
        KANRI.CD_DIV_NM
    FROM
        ZM_M_CD_NM_KANRI KANRI
    WHERE
        KANRI.CD_SBT_CD = 'CD01832'
    AND BMP.KOJI_JSSI_WAY = KANRI.CD_DIV
    AND KANRI.MK_FLG = '0') AS CD_DIV_NM,
    BMP.HUKUSU_KAISEN_BMP_UM,
    (SELECT CD_DIV_NM
     FROM ZM_M_CD_NM_KANRI
     WHERE CD_SBT_CD = 'CD01833'
     AND CD_DIV = ?
     UNION
     SELECT CD_DIV_NM
     FROM ZM_M_CD_NM_KANRI
     WHERE CD_SBT_CD = 'CD01837'
     AND CD_DIV = ?) AS CD_DIV_ALI
FROM
    TU_T_BMP_KOJI BMP
    INNER JOIN KK_T_SVC_KEI_UCWK UCWK ON
        BMP.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO
    AND (UCWK.SVC_KEI_UCWK_NO, UCWK.RSV_APLY_YMD || UCWK.GENE_ADD_DTM) =
        (SELECT
            MAXUCWK.SVC_KEI_UCWK_NO,
            MAX(MAXUCWK.RSV_APLY_YMD || MAXUCWK.GENE_ADD_DTM) 
        FROM
            KK_T_SVC_KEI_UCWK MAXUCWK 
        WHERE
            UCWK.SVC_KEI_UCWK_NO = MAXUCWK.SVC_KEI_UCWK_NO
        AND MAXUCWK.RSV_APLY_YMD <= ?
        AND MAXUCWK.RSV_APLY_CD = '2'
        AND MAXUCWK.MK_FLG = '0'
        GROUP BY
            MAXUCWK.SVC_KEI_UCWK_NO
        )
   AND UCWK.MK_FLG = '0'
WHERE
    BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0')
AND BMP.MK_FLG = '0'
AND BMP.BMP_KOJI_STAT = ?
AND BMP.MSKMJIG_USE_CD = ?
AND BMP.NTT_KEI_TEL_KAISEN_NO = ?
AND BMP.HOJIN_KOJIN_CD = '1'
UNION ALL
SELECT
    BMP.BMP_KOJI_NO,
    BMP.GENE_ADD_DTM,
    BMP.NTT_KEI_TEL_KAISEN_NO,
    BMP.NTT_KEISHA_NM,
    BMP.BMP_UK_TNT_USER_ID,
    NULL AS SVC_KEI_NO,
    (SELECT
        TJ.TSJGS_NM
    FROM
        TU_M_TUSHIN_JGYOSHA TJ
    WHERE
        BMP.ITNM_TSJGS_CD = TJ.TSJGS_CD
    AND TJ.MK_FLG = '0') AS TSJGS_NM,
    BMP.ITNM_TSJGS_CD,
    BMP.KOJI_JSSI_WAY,
    (SELECT
        KANRI.CD_DIV_NM
    FROM
        ZM_M_CD_NM_KANRI KANRI
    WHERE
        KANRI.CD_SBT_CD = 'CD01832'
    AND BMP.KOJI_JSSI_WAY = KANRI.CD_DIV
    AND KANRI.MK_FLG = '0') AS CD_DIV_NM,
    BMP.HUKUSU_KAISEN_BMP_UM,
    (SELECT CD_DIV_NM
     FROM ZM_M_CD_NM_KANRI
     WHERE CD_SBT_CD = 'CD01833'
     AND CD_DIV = ?
     UNION
     SELECT CD_DIV_NM
     FROM ZM_M_CD_NM_KANRI
     WHERE CD_SBT_CD = 'CD01837'
     AND CD_DIV = ?) AS CD_DIV_ALI
FROM
    TU_T_BMP_KOJI BMP
WHERE
    BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0')
AND BMP.MK_FLG = '0'
AND BMP.BMP_KOJI_STAT = ?
AND BMP.MSKMJIG_USE_CD = ?
AND BMP.NTT_KEI_TEL_KAISEN_NO = ?
AND BMP.HOJIN_KOJIN_CD = '2'

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-TU_SELECT_016)
#--該当番ポ工事番号の最新世代登録年月日時分秒を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_016

SELECT
    BMP.GENE_ADD_DTM
FROM
    TU_T_BMP_KOJI BMP
WHERE
    BMP.BMP_KOJI_NO = ? AND
    BMP.GENE_ADD_DTM = (SELECT MAX(MAXBMP.GENE_ADD_DTM) FROM TU_T_BMP_KOJI MAXBMP WHERE BMP.BMP_KOJI_NO = MAXBMP.BMP_KOJI_NO AND MAXBMP.MK_FLG = '0') AND
    BMP.MK_FLG = '0'
FOR UPDATE

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_001)
#--対象サービス契約内訳抽出
#--基準日が"番ポ工事完了年月日"の場合
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
    TU0011.BMP_KOJI_FIN_YMD
FROM
    TU_T_BMP_KOJI TU0011
WHERE
    TU0011.SVC_KEI_UCWK_NO = ?
    AND TU0011.BMP_KOJI_STAT = '050'
    AND TU0011.BMP_KOJI_NO || TU0011.GENE_ADD_DTM = 
        (SELECT 
            MAX(TU0011_GENE.BMP_KOJI_NO || TU0011_GENE.GENE_ADD_DTM) AS TU0011_MAX
         FROM 
             TU_T_BMP_KOJI TU0011_GENE
         WHERE 
             TU0011_GENE.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO 
             AND TU0011_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_002)
#--番ポ工事当日通知ファイル作成
#--工事確定日が到来（システム日時）した関連データを抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
  KK0161.SVC_KEI_UCWK_NO
 ,KK0161.GENE_ADD_DTM
 ,KK0161.SVC_KEI_NO
 ,KK0191.TELNO
 ,TU0011.SETPLACE_AD_CD
 ,TU0011.BMP_KOJI_FIX_YMDH
 ,TU0011.BMP_UK_TNT_USER_ID
 ,TU0011.BMP_KOJI_KIBO_YMDH
 ,TU0011.KOJI_JSSI_WAY
 ,TU0011.SOTEI_SWITCH_TIMEZ
 ,TU0011.BMPKJ_KIBO_YMD_PDING_FLG
 ,TU0011.ITNM_TSJGS_CD
 ,TU0011.NTT_KEISHA_NM
 ,CK0011.CUST_NM
FROM TU_T_BMP_KOJI TU0011

  INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON  KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO

  INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191
  ON  KK0191.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
  AND KK0191.GENE_ADD_DTM = KK0161.GENE_ADD_DTM

  INNER JOIN KK_T_SVC_KEI KK0081
  ON  KK0081.SVC_KEI_NO = KK0161.SVC_KEI_NO

  INNER JOIN CK_T_CUST CK0011
  ON  CK0011.SYSID = KK0081.SYSID

WHERE KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM = (
  SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS G_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')

AND   KK0191.BMP_UM = '1'

AND   KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM = (
  SELECT MAX(KK0081_G.RSV_APLY_YMD || KK0081_G.GENE_ADD_DTM) AS MAX_G
  FROM   KK_T_SVC_KEI KK0081_G
  WHERE  KK0081_G.SVC_KEI_NO = KK0081.SVC_KEI_NO
  AND    KK0081_G.RSV_APLY_YMD <= ?
  AND    KK0081_G.RSV_APLY_CD = '2'
  AND    KK0081_G.MK_FLG = '0')

AND   CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM = (
  SELECT MAX(CK0011_G.RSV_APLY_YMD || CK0011_G.GENE_ADD_DTM) AS MAX_G
  FROM   CK_T_CUST CK0011_G
  WHERE  CK0011_G.SYSID = CK0011.SYSID
  AND    CK0011_G.RSV_APLY_YMD <= ?
  AND    CK0011_G.RSV_APLY_CD = '2'
  AND    CK0011_G.MK_FLG = '0')

AND   TU0011.BMP_KOJI_STAT NOT IN ('050', '090')
AND  (TU0011.BMP_KOJI_FIN_YMD IS NULL OR TU0011.BMP_KOJI_FIN_YMD = '20991231')
AND   TU0011.BMP_KOJI_FIX_YMDH LIKE ?

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_003)
#--【廃止住所データ取得】
#--サービス契約番号、SYSYID、NTT契約者住所コード、
#--設置場所住所コード、加入権移転先住所コードを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
  KK0081.SYSID,
  KK0161.SVC_KEI_NO,
  TU0011.NTT_KEISHA_AD_CD,
  TU0011.SETPLACE_AD_CD,
  TU0011.KANYKN_ITENS_AD_CD
FROM
  TU_T_BMP_KOJI TU0011
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO
  AND KK0161.GENE_ADD_DTM =
      (SELECT
         MAX(KK0161_GENE.GENE_ADD_DTM)
       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_CD = '2'
         AND KK0161_GENE.MK_FLG = '0')
 INNER JOIN
  KK_T_SVC_KEI KK0081
 ON
  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
  KK0081.GENE_ADD_DTM =
    (SELECT
        MAX(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_CD = '2' AND
        KK0081_GENE.MK_FLG = '0') AND
  KK0081.MK_FLG = '0'
WHERE
  (TU0011.NTT_KEISHA_AD_CD = ? OR TU0011.SETPLACE_AD_CD = ? OR TU0011.KANYKN_ITENS_AD_CD = ?)
  AND TU0011.GENE_ADD_DTM =
      (SELECT
         MAX(TU0011_GENE.GENE_ADD_DTM)
       FROM
         TU_T_BMP_KOJI TU0011_GENE
       WHERE
         TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
         AND TU0011_GENE.MK_FLG = '0')
  AND TU0011.BMP_KOJI_STAT IN ( '000', '010', '011', '012' )
  AND TU0011.MK_FLG = '0'

#-


#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_004)
#--【変更住所データ取得】
#--サービス契約番号、SYSYID、NTT契約者住所コード、
#--設置場所住所コード、加入権移転先住所コードを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
    KK0081.SYSID,
    KK0161.SVC_KEI_NO,
    TU0011.BMP_KOJI_NO,
    TU0011.NTT_KEISHA_AD_CD,
    TU0011.NTT_KEISHA_PCD,
    TU0011.NTT_KEISHA_STATE_NM,
    TU0011.NTT_KEISHA_CITY_NM,
    TU0011.NTT_KEISHA_OAZTSU_NM,
    TU0011.NTT_KEISHA_AZCHO_NM,
    TU0011.NTT_KEISHA_BNCHIGO,
    KK2661.AD_CD,
    KK2661.PCD,
    KK2661.STATE_NM,
    KK2661.CITY_NM,
    KK2661.OAZTSU_NM,
    KK2661.AZCHO_NM,
    KK2661.AD_SHUSEI_CD,
    KK2661.NEW_AD_CD,
    KK2661.NEW_PCD,
    KK2661.NEW_STATE_NM,
    KK2661.NEW_CITY_NM,
    KK2661.NEW_OAZTSU_NM,
    KK2661.NEW_AZCHO_NM,
    KK2661.AD_CHG_SBT_CD
FROM
  TU_T_BMP_KOJI TU0011
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO
  AND KK0161.GENE_ADD_DTM =
      (SELECT
         MAX(KK0161_GENE.GENE_ADD_DTM)
       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_CD = '2'
         AND KK0161_GENE.MK_FLG = '0')
 INNER JOIN
  KK_T_SVC_KEI KK0081
 ON
  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
  KK0081.GENE_ADD_DTM =
    (SELECT
        MAX(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_CD = '2' AND
        KK0081_GENE.MK_FLG = '0') AND
  KK0081.MK_FLG = '0'
INNER JOIN
        KK_T_CHGTGAD_CD_WK KK2661
    ON
        TU0011.NTT_KEISHA_AD_CD = KK2661.AD_CD
    AND KK2661.CHG_AD_TRN_STAT_CD = '0'
WHERE
  TU0011.NTT_KEISHA_AD_CD = ?
  AND TU0011.GENE_ADD_DTM =
      (SELECT
         MAX(TU0011_GENE.GENE_ADD_DTM)
       FROM
         TU_T_BMP_KOJI TU0011_GENE
       WHERE
         TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
         AND TU0011_GENE.MK_FLG = '0')
  AND (TU0011.NKYS_AD_MAN_INPUT_FLG != '1' OR TU0011.NKYS_AD_MAN_INPUT_FLG IS NULL)
  AND TU0011.BMP_KOJI_STAT IN ( '000', '010', '011', '021' )
  AND TU0011.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-

--KEY:KK_SELECT_005

SELECT
    KK0081.SYSID,
    KK0161.SVC_KEI_NO,
    TU0011.BMP_KOJI_NO,
    TU0011.SETPLACE_AD_CD,
    TU0011.SETPLACE_PCD,
    TU0011.SETPLACE_STATE_NM,
    TU0011.SETPLACE_CITY_NM,
    TU0011.SETPLACE_OAZTSU_NM,
    TU0011.SETPLACE_AZCHO_NM,
    TU0011.SETPLACE_BNCHIGO,
    KK2661.AD_CD,
    KK2661.PCD,
    KK2661.STATE_NM,
    KK2661.CITY_NM,
    KK2661.OAZTSU_NM,
    KK2661.AZCHO_NM,
    KK2661.AD_SHUSEI_CD,
    KK2661.NEW_AD_CD,
    KK2661.NEW_PCD,
    KK2661.NEW_STATE_NM,
    KK2661.NEW_CITY_NM,
    KK2661.NEW_OAZTSU_NM,
    KK2661.NEW_AZCHO_NM,
    KK2661.AD_CHG_SBT_CD
FROM
  TU_T_BMP_KOJI TU0011
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO
  AND KK0161.GENE_ADD_DTM =
      (SELECT
         MAX(KK0161_GENE.GENE_ADD_DTM)
       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_CD = '2'
         AND KK0161_GENE.MK_FLG = '0')
 INNER JOIN
  KK_T_SVC_KEI KK0081
 ON
  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
  KK0081.GENE_ADD_DTM =
    (SELECT
        MAX(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_CD = '2' AND
        KK0081_GENE.MK_FLG = '0') AND
  KK0081.MK_FLG = '0'
INNER JOIN
        KK_T_CHGTGAD_CD_WK KK2661
    ON
        TU0011.SETPLACE_AD_CD = KK2661.AD_CD
    AND KK2661.CHG_AD_TRN_STAT_CD = '0'
WHERE
  TU0011.SETPLACE_AD_CD = ?
  AND TU0011.GENE_ADD_DTM =
      (SELECT
         MAX(TU0011_GENE.GENE_ADD_DTM)
       FROM
         TU_T_BMP_KOJI TU0011_GENE
       WHERE
         TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
         AND TU0011_GENE.MK_FLG = '0')
  AND (TU0011.SETPLACE_AD_MAN_INPUT_FLG != '1' OR TU0011.SETPLACE_AD_MAN_INPUT_FLG IS NULL)
  AND TU0011.BMP_KOJI_STAT IN ( '000', '010', '011', '021' )
  AND TU0011.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-

--KEY:KK_SELECT_006

SELECT
    KK0081.SYSID,
    KK0161.SVC_KEI_NO,
    TU0011.BMP_KOJI_NO,
    TU0011.KANYKN_ITENS_AD_CD,
    TU0011.KANYKN_ITENS_PCD,
    TU0011.KANYKN_ITENS_STATE_NM,
    TU0011.KANYKN_ITENS_CITY_NM,
    TU0011.KANYKN_ITENS_OAZTSU_NM,
    TU0011.KANYKN_ITENS_AZCHO_NM,
    TU0011.KANYKN_ITENS_BNCHIGO,
    KK2661.AD_CD,
    KK2661.PCD,
    KK2661.STATE_NM,
    KK2661.CITY_NM,
    KK2661.OAZTSU_NM,
    KK2661.AZCHO_NM,
    KK2661.AD_SHUSEI_CD,
    KK2661.NEW_AD_CD,
    KK2661.NEW_PCD,
    KK2661.NEW_STATE_NM,
    KK2661.NEW_CITY_NM,
    KK2661.NEW_OAZTSU_NM,
    KK2661.NEW_AZCHO_NM,
    KK2661.AD_CHG_SBT_CD
FROM
  TU_T_BMP_KOJI TU0011
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO
  AND KK0161.GENE_ADD_DTM =
      (SELECT
         MAX(KK0161_GENE.GENE_ADD_DTM)
       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_CD = '2'
         AND KK0161_GENE.MK_FLG = '0')
 INNER JOIN
  KK_T_SVC_KEI KK0081
 ON
  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
  KK0081.GENE_ADD_DTM =
    (SELECT
        MAX(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_CD = '2' AND
        KK0081_GENE.MK_FLG = '0') AND
  KK0081.MK_FLG = '0'
INNER JOIN
        KK_T_CHGTGAD_CD_WK KK2661
    ON
        TU0011.KANYKN_ITENS_AD_CD = KK2661.AD_CD
    AND KK2661.CHG_AD_TRN_STAT_CD = '0'
WHERE
  TU0011.KANYKN_ITENS_AD_CD = ?
  AND TU0011.GENE_ADD_DTM =
      (SELECT
         MAX(TU0011_GENE.GENE_ADD_DTM)
       FROM
         TU_T_BMP_KOJI TU0011_GENE
       WHERE
         TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
         AND TU0011_GENE.MK_FLG = '0')
  AND (TU0011.KNKN_ITNSAD_MAN_INPUT_FLG != '1' OR TU0011.KNKN_ITNSAD_MAN_INPUT_FLG IS NULL)
  AND TU0011.BMP_KOJI_STAT IN ( '000', '010', '011', '021' )
  AND TU0011.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_007)
#--【変更住所データ取得】
#--サービス契約番号、SYSYID、NTT契約者住所コード、
#--設置場所住所コード、加入権移転先住所コードを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_007

SELECT
    KK0081.SYSID,
    KK0161.SVC_KEI_NO,
    TU0011.BMP_KOJI_NO,
    TU0011.NTT_KEISHA_AD_CD,
    TU0011.NTT_KEISHA_PCD,
    TU0011.NTT_KEISHA_STATE_NM,
    TU0011.NTT_KEISHA_CITY_NM,
    TU0011.NTT_KEISHA_OAZTSU_NM,
    TU0011.NTT_KEISHA_AZCHO_NM,
    KK2661.AD_CD,
    KK2661.PCD,
    KK2661.STATE_NM,
    KK2661.CITY_NM,
    KK2661.OAZTSU_NM,
    KK2661.AZCHO_NM,
    KK2661.AD_SHUSEI_CD,
    KK2661.NEW_AD_CD,
    KK2661.NEW_PCD,
    KK2661.NEW_STATE_NM,
    KK2661.NEW_CITY_NM,
    KK2661.NEW_OAZTSU_NM,
    KK2661.NEW_AZCHO_NM,
    KK2661.AD_CHG_SBT_CD
FROM
  TU_T_BMP_KOJI TU0011
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO
  AND KK0161.GENE_ADD_DTM =
      (SELECT
         MAX(KK0161_GENE.GENE_ADD_DTM)
       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_CD = '2'
         AND KK0161_GENE.MK_FLG = '0')
 INNER JOIN
  KK_T_SVC_KEI KK0081
 ON
  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
  KK0081.GENE_ADD_DTM =
    (SELECT
        MAX(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_CD = '2' AND
        KK0081_GENE.MK_FLG = '0') AND
  KK0081.MK_FLG = '0'
INNER JOIN
        KK_T_CHGTGAD_CD_WK KK2661
    ON
        TU0011.NTT_KEISHA_AD_CD = KK2661.AD_CD
    AND KK2661.MANIPT_CHG_AD_TRN_STAT_CD = '0'
WHERE
  SUBSTR(TU0011.NTT_KEISHA_AD_CD, 1, 5) = SUBSTR( ?, 1, 5)
  AND TU0011.NKYS_AD_MAN_INPUT_FLG = '1'
  AND TU0011.GENE_ADD_DTM =
      (SELECT
         MAX(TU0011_GENE.GENE_ADD_DTM)
       FROM
         TU_T_BMP_KOJI TU0011_GENE
       WHERE
         TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
         AND TU0011_GENE.MK_FLG = '0')
  AND TU0011.BMP_KOJI_STAT IN ( '000', '010', '011', '021' )
  AND TU0011.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-

--KEY:KK_SELECT_008

SELECT
    KK0081.SYSID,
    KK0161.SVC_KEI_NO,
    TU0011.BMP_KOJI_NO,
    TU0011.SETPLACE_AD_CD,
    TU0011.SETPLACE_PCD,
    TU0011.SETPLACE_STATE_NM,
    TU0011.SETPLACE_CITY_NM,
    TU0011.SETPLACE_OAZTSU_NM,
    TU0011.SETPLACE_AZCHO_NM,
    KK2661.AD_CD,
    KK2661.PCD,
    KK2661.STATE_NM,
    KK2661.CITY_NM,
    KK2661.OAZTSU_NM,
    KK2661.AZCHO_NM,
    KK2661.AD_SHUSEI_CD,
    KK2661.NEW_AD_CD,
    KK2661.NEW_PCD,
    KK2661.NEW_STATE_NM,
    KK2661.NEW_CITY_NM,
    KK2661.NEW_OAZTSU_NM,
    KK2661.NEW_AZCHO_NM,
    KK2661.AD_CHG_SBT_CD
FROM
  TU_T_BMP_KOJI TU0011
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO
  AND KK0161.GENE_ADD_DTM =
      (SELECT
         MAX(KK0161_GENE.GENE_ADD_DTM)
       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_CD = '2'
         AND KK0161_GENE.MK_FLG = '0')
 INNER JOIN
  KK_T_SVC_KEI KK0081
 ON
  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
  KK0081.GENE_ADD_DTM =
    (SELECT
        MAX(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_CD = '2' AND
        KK0081_GENE.MK_FLG = '0') AND
  KK0081.MK_FLG = '0'
INNER JOIN
        KK_T_CHGTGAD_CD_WK KK2661
    ON
        TU0011.SETPLACE_AD_CD = KK2661.AD_CD
    AND KK2661.MANIPT_CHG_AD_TRN_STAT_CD = '0'
WHERE
  SUBSTR(TU0011.SETPLACE_AD_CD, 1, 5) = SUBSTR( ?, 1, 5)
  AND TU0011.SETPLACE_AD_MAN_INPUT_FLG = '1'
  AND TU0011.GENE_ADD_DTM =
      (SELECT
         MAX(TU0011_GENE.GENE_ADD_DTM)
       FROM
         TU_T_BMP_KOJI TU0011_GENE
       WHERE
         TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
         AND TU0011_GENE.MK_FLG = '0')
  AND TU0011.BMP_KOJI_STAT IN ( '000', '010', '011', '021' )
  AND TU0011.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-

--KEY:KK_SELECT_009

SELECT
    KK0081.SYSID,
    KK0161.SVC_KEI_NO,
    TU0011.BMP_KOJI_NO,
    TU0011.KANYKN_ITENS_AD_CD,
    TU0011.KANYKN_ITENS_PCD,
    TU0011.KANYKN_ITENS_STATE_NM,
    TU0011.KANYKN_ITENS_CITY_NM,
    TU0011.KANYKN_ITENS_OAZTSU_NM,
    TU0011.KANYKN_ITENS_AZCHO_NM,
    KK2661.AD_CD,
    KK2661.PCD,
    KK2661.STATE_NM,
    KK2661.CITY_NM,
    KK2661.OAZTSU_NM,
    KK2661.AZCHO_NM,
    KK2661.AD_SHUSEI_CD,
    KK2661.NEW_AD_CD,
    KK2661.NEW_PCD,
    KK2661.NEW_STATE_NM,
    KK2661.NEW_CITY_NM,
    KK2661.NEW_OAZTSU_NM,
    KK2661.NEW_AZCHO_NM,
    KK2661.AD_CHG_SBT_CD
FROM
  TU_T_BMP_KOJI TU0011
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO
  AND KK0161.GENE_ADD_DTM =
      (SELECT
         MAX(KK0161_GENE.GENE_ADD_DTM)
       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_CD = '2'
         AND KK0161_GENE.MK_FLG = '0')
 INNER JOIN
  KK_T_SVC_KEI KK0081
 ON
  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
  KK0081.GENE_ADD_DTM =
    (SELECT
        MAX(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_CD = '2' AND
        KK0081_GENE.MK_FLG = '0') AND
  KK0081.MK_FLG = '0'
INNER JOIN
        KK_T_CHGTGAD_CD_WK KK2661
    ON
        TU0011.KANYKN_ITENS_AD_CD = KK2661.AD_CD
    AND KK2661.MANIPT_CHG_AD_TRN_STAT_CD = '0'
WHERE
  SUBSTR(TU0011.KANYKN_ITENS_AD_CD, 1, 5) = SUBSTR( ?, 1, 5)
  AND TU0011.KNKN_ITNSAD_MAN_INPUT_FLG = '1'
  AND TU0011.GENE_ADD_DTM =
      (SELECT
         MAX(TU0011_GENE.GENE_ADD_DTM)
       FROM
         TU_T_BMP_KOJI TU0011_GENE
       WHERE
         TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
         AND TU0011_GENE.MK_FLG = '0')
  AND TU0011.BMP_KOJI_STAT IN ( '000', '010', '011', '021' )
  AND TU0011.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-


--KEY:KK_SELECT_010

SELECT
  TU0011.BMP_KOJI_STAT,
  TU0011.BMP_KOJI_FIN_YMD,
  CASE WHEN TU0011.BMP_KOJI_FIN_YMD IS NOT NULL THEN
      TO_CHAR(TO_DATE(TU0011.BMP_KOJI_FIN_YMD, 'YYYYMMDD') + 10, 'YYYYMMDD') 
  ELSE
      TU0011.BMP_KOJI_FIN_YMD
  END AS BMP_KOJI_FIN_YMD_ADD10
FROM 
    (SELECT TU0011_WK.SVC_KEI_UCWK_NO,TU0011_WK.BMP_KOJI_STAT,TU0011_WK.BMP_KOJI_FIN_YMD
    FROM
      TU_T_BMP_KOJI TU0011_WK
    WHERE
      TU0011_WK.GENE_ADD_DTM = 
      (SELECT MAX(TU0011_GENE.GENE_ADD_DTM) AS TU0011_MAX
      FROM
        TU_T_BMP_KOJI TU0011_GENE
      WHERE
        TU0011_GENE.BMP_KOJI_NO = TU0011_WK.BMP_KOJI_NO
      AND TU0011_GENE.MK_FLG = '0')
    ) TU0011
WHERE TU0011.SVC_KEI_UCWK_NO = ?

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_011)
#--KKPRC00901
#--サービス契約内訳番号より番ポ工事が存在するか判定用
#-----------------------------------------------------------

--KEY:KK_SELECT_011

SELECT
    TU0011.BMP_KOJI_NO,
    TU0011.BMP_KOJI_STAT,
    TU0011.BMP_KOJI_FIN_YMD
FROM
    TU_T_BMP_KOJI TU0011
WHERE
    TU0011.SVC_KEI_UCWK_NO = ? AND
    TU0011.GENE_ADD_DTM = (SELECT
                                MAX(TU0011_GENE.GENE_ADD_DTM) AS TU0011_MAX
                            FROM
                                TU_T_BMP_KOJI TU0011_GENE
                            WHERE
                                TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO  AND
                                TU0011_GENE.MK_FLG = '0'
                            )

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_012)
#--KKPRC394
#--サービス契約内訳番号より番ポ工事を取得し状態判断
#-----------------------------------------------------------

--KEY:KK_SELECT_012

SELECT
    TU0011.BMP_KOJI_NO
FROM
    TU_T_BMP_KOJI TU0011
WHERE
    TU0011.SVC_KEI_UCWK_NO = ? 
AND
    TU0011.BMP_KOJI_STAT IN ('000','010','011','021')
AND
    TU0011.BMP_MSKMSHO_TYPE_CD IN ('1','2')
and
    TU0011.GENE_ADD_DTM = (SELECT
                                MAX(TU0011_GENE.GENE_ADD_DTM) AS TU0011_MAX
                            FROM
                                TU_T_BMP_KOJI TU0011_GENE
                            WHERE
                                TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO  AND
                                TU0011_GENE.MK_FLG = '0'
                            )

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_014)
#--KKPRC14901_サービスオーダ送信依頼
#--サービス契約番号、サービス契約内訳番号から番ポ工事ステータスの優先度が高い番ポ工事を取得する。
#--(023:NTT申請不可、013:他事業者調整不可、090:番ポ工事取消の優先度は低くする)
#-----------------------------------------------------------

--KEY:KK_SELECT_014

SELECT /*TU_T_BMP_KOJI.SQL KK_SELECT_014*/
    TU0011.BMP_KOJI_NO,
    TU0011.GENE_ADD_DTM,
    TU0011.ROUTING_NO
FROM
    TU_T_BMP_KOJI TU0011
WHERE
    TU0011.SVC_KEI_UCWK_NO = ?
AND TU0011.NTT_KEI_TEL_KAISEN_NO = ?
AND TU0011.BMP_MSKMSHO_TYPE_CD IN ('1', '2')
AND TU0011.GENE_ADD_DTM = (SELECT
                                MAX(TU0011_GENE.GENE_ADD_DTM) AS TU0011_MAX
                            FROM
                                TU_T_BMP_KOJI TU0011_GENE
                            WHERE
                                TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
                            AND TU0011_GENE.MK_FLG = '0'
                            )
ORDER BY
    (CASE WHEN TU0011.BMP_KOJI_STAT NOT IN ('023', '013', '090') THEN TU0011.BMP_KOJI_STAT
          WHEN TU0011.BMP_KOJI_STAT = '023' THEN '-30'
          WHEN TU0011.BMP_KOJI_STAT = '013' THEN '-20'
          WHEN TU0011.BMP_KOJI_STAT = '090' THEN '-10'
     END) DESC

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_INSERT_002)
#-----------------------------------------------------------

--KEY:KK_INSERT_002

INSERT INTO  TU_T_BMP_KOJI
SELECT
    TU0011.BMP_KOJI_NO,
    ?,
    TU0011.BMP_KOJI_STAT,
    TU0011.SVC_KEI_UCWK_NO,
    TU0011.HOJIN_SVC_KEI_UK_NO,
    TU0011.HJIN_EO_YKAE_SVKEI_NO,
    TU0011.HOJIN_KOJIN_CD,
    TU0011.CUST_MSKM_YMD,
    TU0011.BMP_MSKMSHO_TYPE_CD,
    TU0011.DOJI_KOJI_UM,
    TU0011.DOBANITEN_UM,
    TU0011.NTT_KEI_TEL_KAISEN_NO,
    TU0011.TA_JGYOSHA_UR_NO,
    TU0011.ROUTING_NO,
    TU0011.NTT_KEISHA_NM,
    TU0011.NTT_KSH_KANA,
    TU0011.NTT_KEISHA_AD_CD,
    TU0011.NTT_KEISHA_PCD,
    TU0011.NTT_KEISHA_STATE_NM,
    TU0011.NTT_KEISHA_CITY_NM,
    TU0011.NTT_KEISHA_OAZTSU_NM,
    TU0011.NTT_KEISHA_AZCHO_NM,
    TU0011.NTT_KEISHA_BNCHIGO,
    TU0011.NTT_KEISHA_ADRTTM,
    TU0011.NTT_KEISHA_ADRRM,
    TU0011.NKYS_AD_MAN_INPUT_FLG,
    TU0011.RRKS_TELNO,
    TU0011.SETPLACE_AD_CD,
    TU0011.SETPLACE_PCD,
    TU0011.SETPLACE_STATE_NM,
    TU0011.SETPLACE_CITY_NM,
    TU0011.SETPLACE_OAZTSU_NM,
    TU0011.SETPLACE_AZCHO_NM,
    TU0011.SETPLACE_BNCHIGO,
    TU0011.SETPLACE_ADRTTM,
    TU0011.SETPLACE_ADRRM,
    TU0011.SETPLACE_AD_MAN_INPUT_FLG,
    TU0011.BMP_KOJI_UK_YMD,
    TU0011.BMP_KOJI_KIBO_YMDH,
    TU0011.BMPKJ_KIBO_TIME_CD,
    TU0011.BMPKJ_KIBO_YMD_PDING_FLG,
    TU0011.BMP_UK_TNT_USER_ID,
    TU0011.BMPKJ_TNT_USER_ID,
    TU0011.PAUSE_HYO_SOHUS_CD,
    TU0011.TEL_KNKN_TRAT_CD,
    TU0011.TEL_KNKN_TRAT_OTR_MEMO,
    TU0011.KANYKN_ITENS_AD_CD,
    TU0011.KANYKN_ITENS_PCD,
    TU0011.KANYKN_ITENS_STATE_NM,
    TU0011.KANYKN_ITENS_CITY_NM,
    TU0011.KANYKN_ITENS_OAZTSU_NM,
    TU0011.KANYKN_ITENS_AZCHO_NM,
    TU0011.KANYKN_ITENS_BNCHIGO,
    TU0011.KANYKN_ITENS_ADRTTM,
    TU0011.KANYKN_ITENS_ADRRM,
    TU0011.KNKN_ITNSAD_MAN_INPUT_FLG,
    TU0011.ITNM_TSJGS_CD,
    TU0011.ITNM_TSJGS_KTN_CD,
    TU0011.MSKMJIG_USE_CD,
    TU0011.BMPKJ_MSKM_MT_TSJGS_CD,
    TU0011.HUKUSU_KAISEN_BMP_UM,
    TU0011.HUKUSU_KAISEN_BMP_CNT,
    TU0011.HUKUSU_KAISEN_GRP_ID,
    TU0011.MSKMJIG_SNAI_KOJI_UM,
    TU0011.MSKMJIG_SGAI_KOJI_UM,
    TU0011.MSKMJIG_KIJIRAN_1,
    TU0011.MSKMJIG_KIJIRAN_2,
    TU0011.MSKMJIG_MSKM_YMDHM,
    TU0011.BMP_KOJI_CHOSA_REQYMD,
    TU0011.TAJGS_RNKI_SEND_DTM,
    TU0011.TA_JGS_REQ_CNT,
    TU0011.TA_JGYOSHA_RNKI_WAY_CD,
    TU0011.JGSHAK_RTRN_ADJ_SKBT_NO,
    TU0011.NTT_RNKI_SEND_DTM,
    TU0011.NTT_REQ_CNT,
    TU0011.NTT_RNKI_WAY_CD,
    TU0011.NTT_REQ_SKBT_NO,
    TU0011.CHGE_MOTO_ORDER_SKBT_NO,
    TU0011.NTT_REQ_ORDER_CHGRE_MEMO,
    TU0011.TK_KH_ANS_YMD,
    TU0011.BMP_KOJI_KH,
    TU0011.BMP_KOJI_FIX_YMDH,
    TU0011.BMPKJ_FIX_TIME_CD,
    TU0011.TK_FAIL_RSN_CD,
    TU0011.TK_FAIL_RSN_MEMO,
    TU0011.TA_JIGYOSHA_MI_CFM_FLG,
    TU0011.BMP_KOJI_REQYMD,
    TU0011.BMP_KOJI_REQ_RSLT_CD,
    TU0011.TAJGS_UK_NO,
    TU0011.TAJGS_TNTSHA_NM,
    TU0011.TAJGS_TELNO,
    TU0011.TAJGS_FAX_NO,
    TU0011.TAJGS_KIJIRAN,
    TU0011.TAJGS_UK_YMD,
    TU0011.TA_JIGYOSHA_ODR_SBT_CD,
    TU0011.TA_JIGYOSHA_ORG_NM,
    TU0011.NTT_UK_NO,
    TU0011.NTT_TNTSHA_NM,
    TU0011.NTT_TELNO,
    TU0011.NTT_FAX_NO,
    TU0011.NTT_SNAI_KOJI_UM,
    TU0011.NTT_SGAI_KOJI_UM,
    TU0011.NTT_KIJIRAN,
    TU0011.NTT_UK_YMD,
    TU0011.BMP_KOJI_FIN_YMD,
    TU0011.BMP_MSKM_JIGSHO_NO,
    TU0011.BMP_KOJI_MSKM_JIGSHO_NO,
    TU0011.BMP_KOJI_STP_CD,
    TU0011.BMP_KOJI_STP_YMD,
    TU0011.HOJIN_RSLT_OPUT_ZUMI_FLG,
    TU0011.TIOKR_RNK_INF_SKSI_ZM_FLG,
    TU0011.DOJI_KOJI_JDG_CD,
    TU0011.TNKJ_RSVD_HANEI_YMD,
    TU0011.TNKJ_RSVD_HANEI_ZM_FLG,
    TU0011.ADD_DTM,
    TU0011.ADD_OPEACNT,
    'UPD_DTM',
    'UPD_OPE',
    NULL,
    NULL,
    '0',
    TU0011.ADD_UNYO_YMD,
    TU0011.ADD_TRN_ID,
    'UPD_UNYO',
    'UPD_TRN',
    NULL,
    NULL,
    TU0011.JGSK_ADJ_RSLT_TRKM_YMD,
    TU0011.BMP_KJ_MSKM_RSLT_TRKM_YMD,
    TU0011.JGSK_ADJ_RSLT_TRKM_DTM,
    TU0011.BMP_KJ_MSKM_RSLT_TRKM_DTM
FROM TU_T_BMP_KOJI TU0011
WHERE TU0011.BMP_KOJI_NO = ?
AND TU0011.GENE_ADD_DTM = (
    SELECT
        MAX(TU0011_GENE.GENE_ADD_DTM)
    FROM
        TU_T_BMP_KOJI TU0011_GENE
    WHERE
        TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO
        AND TU0011_GENE.MK_FLG = '0')
AND TU0011.MK_FLG = '0'

#-

--KEY:KK_UPDATE_002

UPDATE
    TU_T_BMP_KOJI
SET
    NTT_KEISHA_AD_CD = ? ,
    NTT_KEISHA_PCD = ? ,
    NTT_KEISHA_STATE_NM = ? ,
    NTT_KEISHA_CITY_NM = ? ,
    NTT_KEISHA_OAZTSU_NM = ? ,
    NTT_KEISHA_AZCHO_NM = ?
WHERE
    BMP_KOJI_NO = ?
AND GENE_ADD_DTM = ?

#-

--KEY:KK_UPDATE_003

UPDATE
    TU_T_BMP_KOJI
SET
    SETPLACE_AD_CD = ? ,
    SETPLACE_PCD = ? ,
    SETPLACE_STATE_NM = ? ,
    SETPLACE_CITY_NM = ? ,
    SETPLACE_OAZTSU_NM = ? ,
    SETPLACE_AZCHO_NM = ?
WHERE
    BMP_KOJI_NO = ?
AND GENE_ADD_DTM = ?

#-

--KEY:KK_UPDATE_004

UPDATE
    TU_T_BMP_KOJI
SET
    KANYKN_ITENS_AD_CD = ? ,
    KANYKN_ITENS_PCD = ? ,
    KANYKN_ITENS_STATE_NM = ? ,
    KANYKN_ITENS_CITY_NM = ? ,
    KANYKN_ITENS_OAZTSU_NM = ? ,
    KANYKN_ITENS_AZCHO_NM = ?
WHERE
    BMP_KOJI_NO = ?
AND GENE_ADD_DTM = ?

#-


#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KV_SELECT_001)
#--料金グループコード電話で番ポ有の場合、番ポ工事ステータス取得
#-----------------------------------------------------------

--KEY:KV_SELECT_001

SELECT
    TU0011.BMP_KOJI_NO,
    TU0011.BMP_KOJI_STAT
FROM
    TU_T_BMP_KOJI TU0011
WHERE
    TU0011.SVC_KEI_UCWK_NO = ? AND
    TU0011.MK_FLG = '0' AND
    TU0011.GENE_ADD_DTM = (SELECT
                                MAX(TU0011_GENE.GENE_ADD_DTM) AS TU0011_MAX
                            FROM
                                TU_T_BMP_KOJI TU0011_GENE
                            WHERE
                                TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO  AND
                                TU0011_GENE.MK_FLG = '0'
                            )

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_013)
#--KKPRC394
#--番ポ工事番号と世代指定で番ポ工事から番ポ番号と申込事業者記事欄1を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_013

SELECT
    TU0011.BMP_KOJI_NO,
    TU0011.MSKMJIG_KIJIRAN_1
FROM
    TU_T_BMP_KOJI TU0011
WHERE
    TU0011.BMP_KOJI_NO = ?
AND TU0011.GENE_ADD_DTM = ?

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_015)
#--KKPRC00501
#--NTT契約者名_NTT_KEISHA_NM
#--NTT契約者カナ名_NTT_KSH_KANA
#--NTT契約者郵便番号_NTT_KEISHA_PCD
#--NTT契約者都道府県名_NTT_KEISHA_STATE_NM
#--NTT契約者市区町村名_NTT_KEISHA_CITY_NM
#--NTT契約者大字通称名_NTT_KEISHA_OAZTSU_NM
#--NTT契約者字丁目名_NTT_KEISHA_AZCHO_NM
#--NTT契約者番地号_NTT_KEISHA_BNCHIGO
#--NTT契約者住所補記・建物名_NTT_KEISHA_ADRTTM
#--NTT契約者住所補記・部屋番号_NTT_KEISHA_ADRRM
#--移転元通信事業者コード_ITNM_TSJGS_CD
#--サービス契約内訳番号, 電話番号で番ポ工事から番号ポータビリティ情報を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_015

SELECT/*TU_T_BMP_KOJI.SQL KK_SELECT_015*/
       TU0011.NTT_KEISHA_NM
     , TU0011.NTT_KSH_KANA
     , TU0011.NTT_KEISHA_PCD
     , TU0011.NTT_KEISHA_STATE_NM
     , TU0011.NTT_KEISHA_CITY_NM
     , TU0011.NTT_KEISHA_OAZTSU_NM
     , TU0011.NTT_KEISHA_AZCHO_NM
     , TU0011.NTT_KEISHA_BNCHIGO
     , TU0011.NTT_KEISHA_ADRTTM
     , TU0011.NTT_KEISHA_ADRRM
     , TU0011.ITNM_TSJGS_CD
FROM
    TU_T_BMP_KOJI TU0011
WHERE
    TU0011.SVC_KEI_UCWK_NO = ?
AND TU0011.NTT_KEI_TEL_KAISEN_NO = ?
AND TU0011.MK_FLG = '0'
AND
    TU0011.GENE_ADD_DTM = (SELECT
                                MAX(TU0011_GENE.GENE_ADD_DTM) AS TU0011_MAX
                            FROM
                                TU_T_BMP_KOJI TU0011_GENE
                            WHERE
                                TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO  AND
                                TU0011_GENE.MK_FLG = '0'
                            )
ORDER BY
    (CASE WHEN TU0011.BMP_KOJI_STAT NOT IN ('023', '013', '090') THEN TU0011.BMP_KOJI_STAT
          WHEN TU0011.BMP_KOJI_STAT = '023' THEN '-30'
          WHEN TU0011.BMP_KOJI_STAT = '013' THEN '-20'
          WHEN TU0011.BMP_KOJI_STAT = '090' THEN '-10'
     END) DESC

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_016)
#-----------------------------------------------------------

--KEY:KK_SELECT_016

SELECT
    TU0011.BMP_KOJI_NO
  , TU0011.CHUMN_KIND
FROM TU_T_BMP_KOJI TU0011
WHERE TU0011.NTT_KEI_TEL_KAISEN_NO = ?
AND   TU0011.BMP_MSKMSHO_TYPE_CD = '3'
AND   TU0011.BMP_KOJI_STAT = '050'
AND   TU0011.BMP_KOJI_FIN_YMD = ?

#-

#-----------------------------------------------------------
#--番ポ工事(TU_T_BMP_KOJI-KK_SELECT_017)
#-----------------------------------------------------------

--KEY:KK_SELECT_017

SELECT
    TU0011.BMP_KOJI_NO
  , TU0011.CHUMN_KIND
FROM TU_T_BMP_KOJI TU0011
WHERE NTT_KEI_TEL_KAISEN_NO = ?
AND   BMP_MSKMSHO_TYPE_CD IN ('1','2')
AND   BMP_KOJI_STAT IN ('031','032','040','041','050')
AND   SUBSTR(BMP_KOJI_FIX_YMDH, 0,8) = ?
AND   (TU0011.BMP_KJ_MSKM_RSLT_TRKM_YMD) = 
    (
        SELECT MAX(TU0011_GENE.BMP_KJ_MSKM_RSLT_TRKM_YMD) FROM TU_T_BMP_KOJI TU0011_GENE
        WHERE TU0011_GENE.NTT_KEI_TEL_KAISEN_NO = TU0011.NTT_KEI_TEL_KAISEN_NO
    )

#-