#-----------------------------------------------------------
#--サービス契約＜ダミー＞(KK_T_SVC_KEI_DUMMY-KK_INSERT_001)
#--マンション提供方式情報作成ファイルを元に登録を行う(処理区分：１)
#-----------------------------------------------------------

--KEY:KK_INSERT_001

INSERT INTO  KK_T_SVC_KEI_DUMMY (
SVC_KEI_NO,
GENE_ADD_DTM,
RSV_APLY_CD,
REPLICA_MOTO_TK_HSK_KEI_NO,
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
)
VALUES
(
?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?
)

#-

#-----------------------------------------------------------
#--サービス契約＜ダミー＞(KK_T_SVC_KEI_DUMMY-KK_UPDATE_001)
#--マンション提供方式情報作成ファイルを元に更新を行う(処理区分：２)
#-----------------------------------------------------------

--KEY:KK_UPDATE_001

UPDATE
    KK_T_SVC_KEI_DUMMY
SET
    GENE_ADD_DTM = ?,
    UPD_DTM = ?,
    UPD_OPEACNT = ?,
    UPD_UNYO_YMD = ?,
    UPD_TRN_ID = ?
WHERE
    SVC_KEI_NO = ? AND
    GENE_ADD_DTM = ? AND
    MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約＜ダミー＞(KK_T_SVC_KEI_DUMMY-KK_SELECT_001)
#--提供方式情報ロック
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
    KK2281.GENE_ADD_DTM ,
    KK2281.UPD_DTM ,
    KK2281.UPD_OPEACNT ,
    KK2281.UPD_UNYO_YMD ,
    KK2281.UPD_TRN_ID ,
    KK2281.SVC_KEI_NO
FROM
    KK_T_SVC_KEI_DUMMY  KK2281 ,
    KK_T_TK_HOSHIKI_KEI KK0891,
    KK_T_KAISEN_USE_KEI KK0881
WHERE
    KK2281.REPLICA_MOTO_TK_HSK_KEI_NO = KK0891.TK_HOSHIKI_KEI_NO AND
    KK0891.KAISEN_USE_KEI_NO = KK0881.KAISEN_USE_KEI_NO AND
    KK0881.KAISEN_USE_KEI_NO  = ? AND
    KK0881.OWNR_KEI_NO = ? AND
    KK0891.TK_HOSHIKI_CD  = ? AND
    KK2281.MK_FLG = '0' AND
    KK0881.MK_FLG = '0' AND
    KK0891.MK_FLG = '0' FOR UPDATE

#-

#-----------------------------------------------------------
#--サービス契約＜ダミー＞(KK_T_SVC_KEI_DUMMY-KK_SELECT_002)
#--提供方式契約番号よりサービス契約ダミーに紐づく請求先番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
KK2281.SVC_KEI_NO,
KK0321.SEIKY_KEI_NO
FROM
KK_T_SVC_KEI_DUMMY KK2281
INNER JOIN  KK_T_KAKINS KK0321
ON KK2281.SVC_KEI_NO = KK0321.SVC_KEI_NO
AND KK0321.KAKINS_TSTAYMD <= ?
AND (KK0321.KAKINS_TSTAYMD IS NOT  null OR KK0321.KAKINS_TENDYMD >= ?)
AND KK0321.MK_FLG = '0'
WHERE KK2281.REPLICA_MOTO_TK_HSK_KEI_NO = ?
    AND  KK2281.GENE_ADD_DTM = (
        SELECT MAX(KK2281_GENE.GENE_ADD_DTM)
            FROM   KK_T_SVC_KEI_DUMMY  KK2281_GENE
                WHERE  KK2281_GENE.SVC_KEI_NO = KK2281.SVC_KEI_NO
                    AND    KK2281_GENE.RSV_APLY_CD = '2'
                    AND    KK2281_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約＜ダミー＞(KK_T_SVC_KEI_DUMMY-KK_SELECT_002)
#--サービス契約ダミーに紐づく提供方式契約.マンション営業管理対象物件名を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_001

SELECT /* KK_T_SVC_KEI_DUMMY-CH_SELECT_001 */
    KK0891.MSBSN_KRTG_BKN_NM
FROM
   (SELECT KK2281.REPLICA_MOTO_TK_HSK_KEI_NO
    FROM   KK_T_SVC_KEI_DUMMY KK2281
    WHERE  KK2281.GENE_ADD_DTM = 
        (SELECT MAX(KK2281_GENE.GENE_ADD_DTM)
         FROM   KK_T_SVC_KEI_DUMMY KK2281_GENE
         WHERE  KK2281_GENE.SVC_KEI_NO = KK2281.SVC_KEI_NO
         AND    KK2281_GENE.RSV_APLY_CD = '2'
         AND    KK2281_GENE.MK_FLG = '0'
        )
    AND    KK2281.SVC_KEI_NO = ?
    AND    KK2281.RSV_APLY_CD = '2'
    AND    KK2281.MK_FLG = '0'
   ) KK2281
    INNER JOIN KK_T_TK_HOSHIKI_KEI KK0891
       ON KK0891.TK_HOSHIKI_KEI_NO = KK2281.REPLICA_MOTO_TK_HSK_KEI_NO
       AND KK0891.MK_FLG = '0'

#-