#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-TU_SELECT_001)
#--法人に未送信で結果確認済みの同番移転情報を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_001

SELECT
    DOBANITEN.DOBANITEN_NO,
    DOBANITEN.GENE_ADD_DTM,
    DOBANITEN.NTT_REQ_SKBT_NO,
    DOBANITEN.HJIN_EO_YKAE_SVKEI_NO,
    DOBANITEN.NTT_TELNO,
    DOBANITEN.DOBANITEN_UK_YMD,
    DOBANITEN.DOBANITEN_NTT_UK_NO,
    DOBANITEN.DOBANITEN_CHOSA_FIN_YMD,
    DOBANITEN.DOBANITEN_KH,
    DOBANITEN.DOBANITEN_RSLT_CD,
    DOBANITEN.DOBANITEN_RSLT_MEMO,
    DOBANITEN.UK_COMP_KISAI_BIKO,
    JIGSHO.MSKM_JIGSHO_TNTSHA_NM,
    JIGSHO.MKM_JIGSHO_TNTSHA_RRKS_TLN,
    JIGSHO.MKM_JIGSHO_TNTSHA_FAX_NO
FROM
    TU_T_DOBANITEN DOBANITEN,
    TU_M_MSKM_JIGSHO JIGSHO
WHERE
   DOBANITEN.HOJIN_KOJIN_CD = '2' AND
   DOBANITEN.HOJIN_RSLT_OPUT_ZUMI_FLG = '0' AND
   DOBANITEN.DOBANITEN_STAT IN ( '023', '031' ) AND
   DOBANITEN.MK_FLG = '0' AND
   DOBANITEN.MSKM_JIGSHO_NO = JIGSHO.MSKM_JIGSHO_NO(+) AND
   JIGSHO.MK_FLG(+) = '0'
ORDER BY
   DOBANITEN.NTT_REQ_SKBT_NO ASC
FOR UPDATE OF DOBANITEN.DOBANITEN_NO

#-

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

--KEY:TU_SELECT_002

SELECT
    DOBANITEN.DOBANITEN_NO,
    DOBANITEN.GENE_ADD_DTM
FROM
    TU_T_DOBANITEN DOBANITEN
WHERE
    DOBANITEN.GENE_ADD_DTM = (SELECT MAX(MAXDOBANITEN.GENE_ADD_DTM) FROM TU_T_DOBANITEN MAXDOBANITEN WHERE DOBANITEN.DOBANITEN_NO = MAXDOBANITEN.DOBANITEN_NO AND MAXDOBANITEN.MK_FLG = '0') AND
    DOBANITEN.MK_FLG = '0' AND
    DOBANITEN.DOBANITEN_STAT = ? AND
    DOBANITEN.MSKMJIG_USE_CD = ? 
FOR UPDATE

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-TU_SELECT_003)
#--レコードロック用SQL
#-----------------------------------------------------------

--KEY:TU_SELECT_003

SELECT
    DOBANITEN.DOBANITEN_NO,
    DOBANITEN.GENE_ADD_DTM
FROM
    TU_T_DOBANITEN DOBANITEN
WHERE
   DOBANITEN.DOBANITEN_NO = ? AND
   DOBANITEN.GENE_ADD_DTM = ?
FOR UPDATE

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-TU_SELECT_004)
#--同番移転ステータスが”同番移転結果可”で同番移転希望年月日が運用日を含む過去日の同番移転情報を抽出する。
#-----------------------------------------------------------

--KEY:TU_SELECT_004

SELECT
    DOBANITEN.DOBANITEN_NO,
    DOBANITEN.GENE_ADD_DTM,
    DOBANITEN.SVC_KEI_UCWK_NO,
    UCWK.GENE_ADD_DTM AS KK0161_GENE_ADD_DTM,
    UCWK.UPD_DTM
FROM
    TU_T_DOBANITEN DOBANITEN,
    KK_T_SVC_KEI_UCWK UCWK
WHERE
    DOBANITEN.DOBANITEN_KIBO_YMD <= ? AND
    DOBANITEN.DOBANITEN_STAT = '031' AND
    DOBANITEN.GENE_ADD_DTM = (SELECT MAX(MAXDOBANITEN.GENE_ADD_DTM) FROM TU_T_DOBANITEN MAXDOBANITEN WHERE DOBANITEN.DOBANITEN_NO = MAXDOBANITEN.DOBANITEN_NO AND MAXDOBANITEN.MK_FLG = '0') AND
    DOBANITEN.MK_FLG = '0' AND
    UCWK.SVC_KEI_UCWK_NO = DOBANITEN.SVC_KEI_UCWK_NO 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
        EXISTS(
        SELECT 
            1
        FROM 
            KK_T_SVKEIUW_EOH_TEL UCWK_TEL
        WHERE
            UCWK_TEL.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO
            AND UCWK_TEL.GENE_ADD_DTM = UCWK.GENE_ADD_DTM
        ) AND
    UCWK.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-TU_SELECT_005)
#--出力済みフラグが”未出力”の同番移転結果不可情報を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_005

SELECT
    DOBANITEN.DOBANITEN_NO,
    DOBANITEN.GENE_ADD_DTM,
    DOBANITEN.DOBANITEN_MSKM_YMD,
    DOBANITEN.NTT_TELNO,
    DOBANITEN.NTT_KEISHA_NM,
    DOBANITEN.SETPLACE_STATE_NM,
    DOBANITEN.SETPLACE_CITY_NM,
    DOBANITEN.SETPLACE_OAZTSU_NM,
    DOBANITEN.SETPLACE_AZCHO_NM,
    DOBANITEN.SETPLACE_BNCHIGO,
    DOBANITEN.SETPLACE_ADRTTM,
    DOBANITEN.SETPLACE_ADRRM,
    DOBANITEN.DOBANITEN_NTT_UK_NO,
    JIGSHO.MSKM_JIGSHONM,
    UCWK.SVC_KEI_NO
FROM
    TU_T_DOBANITEN DOBANITEN
    INNER JOIN TU_M_MSKM_JIGSHO JIGSHO ON
        DOBANITEN.MSKM_JIGSHO_NO = JIGSHO.MSKM_JIGSHO_NO AND
        JIGSHO.MK_FLG = '0'
    LEFT OUTER JOIN KK_T_SVC_KEI_UCWK UCWK ON
        DOBANITEN.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO AND
        UCWK.RSV_APLY_YMD || UCWK.GENE_ADD_DTM IN 
            (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'
            GROUP BY 
                SVC_KEI_UCWK_NO ) AND
        EXISTS(
            SELECT 
                1
            FROM 
                KK_T_SVKEIUW_EOH_TEL SKUW_TEL
            WHERE
                SKUW_TEL.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO
                AND SKUW_TEL.GENE_ADD_DTM = UCWK.GENE_ADD_DTM
        ) AND
        UCWK.MK_FLG = '0'
WHERE
   DOBANITEN.RSLT_LIST_OPUT_ZUMI_FLG = '0' AND
   DOBANITEN.DOBANITEN_KH = '0' AND
   DOBANITEN.HOJIN_KOJIN_CD = '1' AND
   DOBANITEN.DOBANITEN_STAT = '023' AND
   DOBANITEN.MK_FLG = '0' AND
   DOBANITEN.GENE_ADD_DTM = (SELECT MAX(MAXDOBANITEN.GENE_ADD_DTM) FROM TU_T_DOBANITEN MAXDOBANITEN WHERE DOBANITEN.DOBANITEN_NO = MAXDOBANITEN.DOBANITEN_NO AND MAXDOBANITEN.MK_FLG = '0')
ORDER BY
   UCWK.SVC_KEI_NO ASC,
   DOBANITEN.DOBANITEN_MSKM_YMD ASC, 
   DOBANITEN.NTT_TELNO ASC
FOR UPDATE OF DOBANITEN.DOBANITEN_NO

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-TU_SELECT_006)
#--世代登録年月日時分秒を問わず、同番移転ステータスが調査結果否で、対応記録連携情報作成済フラグが未連携のデータを抽出する。
#-----------------------------------------------------------

--KEY:TU_SELECT_006

SELECT
    UCWK.SVC_KEI_NO,
    DOBANITEN.DOBANITEN_NO,
    DOBANITEN.GENE_ADD_DTM,
    DOBANITEN.DOBANITEN_TNT_USER_ID
FROM
    TU_T_DOBANITEN DOBANITEN,
    KK_T_SVC_KEI_UCWK UCWK
WHERE
    DOBANITEN.DOBANITEN_STAT = '023' AND
    DOBANITEN.TIOKR_RNK_INF_SKSI_ZM_FLG = '0' AND
    DOBANITEN.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO 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
        EXISTS(
        SELECT 
            1
        FROM 
            KK_T_SVKEIUW_EOH_TEL UCWK_TEL
        WHERE
            UCWK_TEL.SVC_KEI_UCWK_NO = UCWK.SVC_KEI_UCWK_NO
            AND UCWK_TEL.GENE_ADD_DTM = UCWK.GENE_ADD_DTM
        ) AND
    DOBANITEN.MK_FLG = '0' AND
    UCWK.MK_FLG = '0'
ORDER BY
    UCWK.SVC_KEI_NO ASC
FOR UPDATE OF DOBANITEN.DOBANITEN_NO

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-TU_SELECT_007)
#--該当同番移転番号の最新世代登録年月日時分秒を取得する。
#-----------------------------------------------------------

--KEY:TU_SELECT_007

SELECT
    DOBANITEN.GENE_ADD_DTM
FROM
    TU_T_DOBANITEN DOBANITEN
WHERE
    DOBANITEN.DOBANITEN_NO = ? AND
    DOBANITEN.GENE_ADD_DTM = (SELECT MAX(MAXDOBANITEN.GENE_ADD_DTM) FROM TU_T_DOBANITEN MAXDOBANITEN WHERE DOBANITEN.DOBANITEN_NO = MAXDOBANITEN.DOBANITEN_NO AND MAXDOBANITEN.MK_FLG = '0') AND
    DOBANITEN.MK_FLG = '0'
FOR UPDATE

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-KU_SELECT_001)
#--【工事案件情報抽出】
#--番ポ有無の設定値の判定に使用する。
#-----------------------------------------------------------

--KEY:KU_SELECT_001

SELECT  COUNT(*)  AS CNT
  FROM  TU_T_DOBANITEN TU0021
 WHERE  TU0021.DOBANITEN_STAT IN  ('031','050')
   AND  TU0021.GENE_ADD_DTM =
       (SELECT MAX(TU0021_GENE.GENE_ADD_DTM)
          FROM TU_T_DOBANITEN TU0021_GENE
         WHERE TU0021.SVC_KEI_UCWK_NO = TU0021_GENE.SVC_KEI_UCWK_NO
           AND TU0021_GENE.MK_FLG = '0')
   AND  TU0021.MK_FLG = '0'
   AND  EXISTS
       (SELECT  1
          FROM  KK_T_SVC_KEI_UCWK KK0161
         WHERE  KK0161.SVC_KEI_UCWK_NO  =  TU0021.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.SVC_KEI_UCWK_NO = KK0161_GENE.SVC_KEI_UCWK_NO
                   AND KK0161_GENE.MK_FLG = '0')
           AND  KK0161.MK_FLG = '0'
           AND  EXISTS
               (SELECT  1
                  FROM  KU_T_SVKEI_KOJIAK KU0081
                 WHERE  KU0081.SVC_KEI_NO = KK0161.SVC_KEI_NO
                   AND  KU0081.KOJIAK_NO = ?
                   AND  KU0081.SVC_CD = '02'
                   AND  KU0081.MK_FLG = '0'
               )
       )

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-KK_SELECT_001)
#--【廃止住所データ取得】
#--サービス契約番号、SYSYID、設置場所住所コード。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
  KK0081.SYSID,
  KK0161.SVC_KEI_NO,
  TU0021.SETPLACE_AD_CD
FROM
  TU_T_DOBANITEN TU0021
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0021.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
  TU0021.SETPLACE_AD_CD = ?
  AND TU0021.GENE_ADD_DTM =
      (SELECT
         MAX(TU0021_GENE.GENE_ADD_DTM)
       FROM
         TU_T_DOBANITEN TU0021_GENE
       WHERE
         TU0021_GENE.SVC_KEI_UCWK_NO = TU0021.SVC_KEI_UCWK_NO
         AND TU0021_GENE.MK_FLG = '0')
  AND TU0021.DOBANITEN_STAT = '021'
  AND TU0021.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-KK_SELECT_002)
#--【変更住所データ取得】
#--サービス契約番号、SYSYID、設置場所住所コード。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
    KK0081.SYSID,
    KK0161.SVC_KEI_NO,
    TU0021.DOBANITEN_NO,
    TU0021.SETPLACE_AD_CD,
    TU0021.SETPLACE_PCD,
    TU0021.SETPLACE_STATE_NM,
    TU0021.SETPLACE_CITY_NM,
    TU0021.SETPLACE_OAZTSU_NM,
    TU0021.SETPLACE_AZCHO_NM,
    TU0021.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_DOBANITEN TU0021
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0021.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
        TU0021.SETPLACE_AD_CD = KK2661.AD_CD
    AND KK2661.CHG_AD_TRN_STAT_CD = '0'
WHERE
  TU0021.SETPLACE_AD_CD = ?
  AND TU0021.GENE_ADD_DTM =
      (SELECT
         MAX(TU0021_GENE.GENE_ADD_DTM)
       FROM
         TU_T_DOBANITEN TU0021_GENE
       WHERE
         TU0021_GENE.DOBANITEN_NO = TU0021.DOBANITEN_NO
         AND TU0021_GENE.MK_FLG = '0')
  AND (TU0021.SETPLACE_AD_MAN_INPUT_FLG != '1' OR TU0021.SETPLACE_AD_MAN_INPUT_FLG IS NULL)
  AND TU0021.DOBANITEN_STAT = '021'
  AND TU0021.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-


#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-KK_SELECT_003)
#--【変更住所データ取得】
#--サービス契約番号、SYSYID、設置場所住所コード。
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
    KK0081.SYSID,
    KK0161.SVC_KEI_NO,
    TU0021.DOBANITEN_NO,
    TU0021.SETPLACE_AD_CD,
    TU0021.SETPLACE_PCD,
    TU0021.SETPLACE_STATE_NM,
    TU0021.SETPLACE_CITY_NM,
    TU0021.SETPLACE_OAZTSU_NM,
    TU0021.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_DOBANITEN TU0021
INNER JOIN KK_T_SVC_KEI_UCWK KK0161
  ON KK0161.SVC_KEI_UCWK_NO = TU0021.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
        TU0021.SETPLACE_AD_CD = KK2661.AD_CD
    AND KK2661.MANIPT_CHG_AD_TRN_STAT_CD = '0'
WHERE
  SUBSTR(TU0021.SETPLACE_AD_CD, 1, 5) = SUBSTR( ?, 1, 5)
  AND TU0021.SETPLACE_AD_MAN_INPUT_FLG = '1'
  AND TU0021.GENE_ADD_DTM =
      (SELECT
         MAX(TU0021_GENE.GENE_ADD_DTM)
       FROM
         TU_T_DOBANITEN TU0021_GENE
       WHERE
         TU0021_GENE.DOBANITEN_NO = TU0021.DOBANITEN_NO
         AND TU0021_GENE.MK_FLG = '0')
  AND TU0021.DOBANITEN_STAT = '021'
  AND TU0021.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-KK_SELECT_004)
#--サービス契約内訳番号をパラメータとして
#--NTT送信待ち、もしくはNTT送信済の有効なレコードを抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
    TU0021.DOBANITEN_NO,
    TU0021.GENE_ADD_DTM,
    TU0021.DOBANITEN_STAT
FROM
    TU_T_DOBANITEN TU0021
WHERE
    TU0021.SVC_KEI_UCWK_NO = ?
    AND
    TU0021.MK_FLG = '0'
    AND
    (TU0021.SVC_KEI_UCWK_NO, TU0021.GENE_ADD_DTM) =
            (SELECT TU0021_GENE.SVC_KEI_UCWK_NO, MAX(TU0021_GENE.GENE_ADD_DTM) AS TU0021_MAX 
             FROM   TU_T_DOBANITEN TU0021_GENE
             WHERE 
                    TU0021_GENE.SVC_KEI_UCWK_NO = TU0021.SVC_KEI_UCWK_NO
               AND  TU0021_GENE.MK_FLG = '0'
             GROUP BY TU0021_GENE.SVC_KEI_UCWK_NO)

#-

#-----------------------------------------------------------
#--同番移転(TU_T_DOBANITEN-KK_INSERT_001)
#-----------------------------------------------------------

--KEY:KK_INSERT_001

INSERT INTO  TU_T_DOBANITEN
SELECT
    TU0021.DOBANITEN_NO,
    ?,
    TU0021.DOBANITEN_STAT,
    TU0021.SVC_KEI_UCWK_NO,
    TU0021.HOJIN_SVC_KEI_UK_NO,
    TU0021.HJIN_EO_YKAE_SVKEI_NO,
    TU0021.HOJIN_KOJIN_CD,
    TU0021.DOBANITEN_MSKM_YMD,
    TU0021.DOBANITEN_SBT_CD,
    TU0021.DOBANITEN_UK_YMD,
    TU0021.DOBANITEN_KIBO_YMD,
    TU0021.NTT_TELNO,
    TU0021.NTT_KEISHA_NM,
    TU0021.NTT_KSH_KANA,
    TU0021.RRKS_NM,
    TU0021.RRKS_TELNO,
    TU0021.SETPLACE_AD_CD,
    TU0021.SETPLACE_PCD,
    TU0021.SETPLACE_STATE_NM,
    TU0021.SETPLACE_CITY_NM,
    TU0021.SETPLACE_OAZTSU_NM,
    TU0021.SETPLACE_AZCHO_NM,
    TU0021.SETPLACE_BNCHIGO,
    TU0021.SETPLACE_ADRTTM,
    TU0021.SETPLACE_ADRRM,
    TU0021.SETPLACE_AD_MAN_INPUT_FLG,
    TU0021.SETPLACE_AD_CFM_CD,
    TU0021.SETPLACE_SBT_CD,
    TU0021.SETPLACE_SBT_OTR_HOKI,
    TU0021.MSKM_JI_BMP_JIGYOSHA_CD,
    TU0021.DOBANITEN_TNT_USER_ID,
    TU0021.MSKM_JIGSHO_NO,
    TU0021.MSKMJIG_KIJIRAN_1,
    TU0021.MSKMJIG_KIJIRAN_2,
    TU0021.MSKMJIG_USE_CD,
    TU0021.DOBANITEN_CHOSA_REQYMD,
    TU0021.SEND_DTM,
    TU0021.SEND_CNT,
    TU0021.NTT_REQ_SKBT_NO,
    TU0021.DOBANITEN_CHOSA_FIN_YMD,
    TU0021.DOBANITEN_NTT_UK_NO,
    TU0021.DOBANITEN_KH,
    TU0021.DOBANITEN_RSLT_CD,
    TU0021.DOBANITEN_RSLT_MEMO,
    TU0021.UK_COMP_KISAI_BIKO,
    TU0021.DOBANITEN_YMD,
    TU0021.DOBANITEN_STP_CD,
    TU0021.DOBANITEN_STP_YMD,
    TU0021.HOJIN_RSLT_OPUT_ZUMI_FLG,
    TU0021.RSLT_LIST_OPUT_ZUMI_FLG,
    TU0021.TIOKR_RNK_INF_SKSI_ZM_FLG,
    TU0021.SYSID,
    TU0021.ADCHM_TPPV_NO,
    TU0021.ADCHM_TLN_TPPV_NO,
    TU0021.DBNITN_KH_RSLT_OPUT_TG_FLG,
    TU0021.ADD_DTM,
    TU0021.ADD_OPEACNT,
    'UPD_DTM',
    'UPD_OPE',
    NULL,
    NULL,
    '0',
    TU0021.ADD_UNYO_YMD,
    TU0021.ADD_TRN_ID,
    'UPD_UNYO',
    'UPD_TRN',
    NULL,
    NULL,
    DBNITN_CHOS_RSLT_TRKM_YMD,
    DBNITN_CHOSA_RSLT_TRKM_DTM
FROM TU_T_DOBANITEN TU0021
WHERE TU0021.DOBANITEN_NO = ?
AND TU0021.GENE_ADD_DTM = (
    SELECT
        MAX(TU0021_GENE.GENE_ADD_DTM)
    FROM
        TU_T_DOBANITEN TU0021_GENE
    WHERE
        TU0021_GENE.DOBANITEN_NO = TU0021.DOBANITEN_NO
        AND TU0021_GENE.MK_FLG = '0')
AND TU0021.MK_FLG = '0'

#-

--KEY:KK_UPDATE_001

UPDATE
    TU_T_DOBANITEN
SET
    SETPLACE_AD_CD = ? ,
    SETPLACE_PCD = ? ,
    SETPLACE_STATE_NM = ? ,
    SETPLACE_CITY_NM = ? ,
    SETPLACE_OAZTSU_NM = ? ,
    SETPLACE_AZCHO_NM = ?
WHERE
    DOBANITEN_NO = ?
AND GENE_ADD_DTM = ?

#-