#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_INSERT_001)
#-----------------------------------------------------------

--KEY:KK_INSERT_001

 INSERT INTO KK_T_TK_HSK_KEI_PRC
  (
 TK_HOSHIKI_KEI_NO
,MANS_PRC_SKBT_CD
,GENE_ADD_DTM
,OWNR_KEI_SEIKY_AMNT
,KANYU_KEI_SEIKY_AMNT
,KKTK_SBT_CD
,TAKNKIKI_SBT_CD
,KANYU_KEI_AITAI_PPLAN_CD
,SEIKY_AMNT_TSTAYMD
,SEIKY_AMNT_TENDYMD
,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_TK_HSK_KEI_PRC-KK_UPDATE_001)
#-----------------------------------------------------------

--KEY:KK_UPDATE_001

 UPDATE 
     KK_T_TK_HSK_KEI_PRC
 SET 
    SEIKY_AMNT_TSTAYMD = ?
    ,SEIKY_AMNT_TENDYMD = ?
    ,UPD_DTM = ?
    ,UPD_OPEACNT = ?
    ,UPD_UNYO_YMD = ?
    ,UPD_TRN_ID = ?
 WHERE 
     TK_HOSHIKI_KEI_NO = ?
 AND MANS_PRC_SKBT_CD = ?
 AND SEIKY_AMNT_TSTAYMD = ?

#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_SELECT_001)
#--マンション受信情報M-IDによる存在チェック（マンション料金情報）
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT DISTINCT
    KK1921.SEIKY_AMNT_TSTAYMD
FROM
    KK_T_TK_HSK_KEI_PRC KK1921
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ?  AND
    KK1921.SEIKY_AMNT_TSTAYMD = ? AND
    KK1921.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_SELECT_004)
#--提供方式情報 ロック（マンションレプリカ料金情報）
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
    TK_HOSHIKI_KEI_NO,
    MANS_PRC_SKBT_CD,
    GENE_ADD_DTM
FROM
    KK_T_TK_HSK_KEI_PRC
WHERE
    TK_HOSHIKI_KEI_NO = ? AND
    MK_FLG = 0  FOR UPDATE

#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_SELECT_005)
#--KKPRC00905
#--住所形態が「マンション」の場合の加入契約請求金額の取得
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT /*KK_T_TK_HSK_KEI_PRC.SQL KK_SELECT_005*/
    KK1921.KANYU_KEI_SEIKY_AMNT
FROM
    KK_T_TK_HSK_KEI_PRC KK1921
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ? AND
    KK1921.MANS_PRC_SKBT_CD = '011' AND
    KK1921.GENE_ADD_DTM =
    (SELECT
        MAX(KK1921_GENE.GENE_ADD_DTM) AS KK1921_MAX
    FROM
        KK_T_TK_HSK_KEI_PRC KK1921_GENE
    WHERE
        KK1921_GENE.TK_HOSHIKI_KEI_NO = KK1921.TK_HOSHIKI_KEI_NO AND
        KK1921_GENE.MANS_PRC_SKBT_CD = KK1921.MANS_PRC_SKBT_CD AND
        ? BETWEEN KK1921_GENE.SEIKY_AMNT_TSTAYMD AND KK1921_GENE.SEIKY_AMNT_TENDYMD AND
        KK1921_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_SELECT_006)
#--KKPRC09201
#--住所形態が「マンション」の場合のオーナー契約請求金額の取得
#-----------------------------------------------------------

--KEY:KK_SELECT_006

SELECT
    KK1921.TK_HOSHIKI_KEI_NO, KK1921.MANS_PRC_SKBT_CD, KK1921.GENE_ADD_DTM, KK1921.OWNR_KEI_SEIKY_AMNT
FROM
    KK_T_TK_HSK_KEI_PRC KK1921
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ? AND
    KK1921.MANS_PRC_SKBT_CD = '001' AND
    KK1921.SEIKY_AMNT_TENDYMD >= ? AND
    KK1921.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_SELECT_007)
#--KKPRC55302
#--「マンション」の場合の加入契約請求金額の取得
#-----------------------------------------------------------

--KEY:KK_SELECT_007

SELECT
    KK1921.KANYU_KEI_SEIKY_AMNT
FROM
    KK_T_TK_HSK_KEI_PRC KK1921
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ? AND
    KK1921.MANS_PRC_SKBT_CD = ? AND
    KK1921.GENE_ADD_DTM =
    (SELECT
        MAX(KK1921_GENE.GENE_ADD_DTM) AS KK1921_MAX
    FROM
        KK_T_TK_HSK_KEI_PRC KK1921_GENE
    WHERE
        KK1921_GENE.TK_HOSHIKI_KEI_NO = KK1921.TK_HOSHIKI_KEI_NO AND
        KK1921_GENE.MANS_PRC_SKBT_CD = KK1921.MANS_PRC_SKBT_CD AND
        ? BETWEEN KK1921_GENE.SEIKY_AMNT_TSTAYMD AND KK1921_GENE.SEIKY_AMNT_TENDYMD AND
        KK1921_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_SELECT_008)
#--KKPRC55302
#--「マンション」の場合の基本料金の取得
#-----------------------------------------------------------

--KEY:KK_SELECT_008

SELECT
    KK1921.KANYU_KEI_SEIKY_AMNT
FROM
    KK_T_TK_HSK_KEI_PRC KK1921
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ? AND
    KK1921.MANS_PRC_SKBT_CD = '001' AND
    KK1921.GENE_ADD_DTM =
    (SELECT
        MAX(KK1921_GENE.GENE_ADD_DTM) AS KK1921_MAX
    FROM
        KK_T_TK_HSK_KEI_PRC KK1921_GENE
    WHERE
        KK1921_GENE.TK_HOSHIKI_KEI_NO = KK1921.TK_HOSHIKI_KEI_NO AND
        KK1921_GENE.MANS_PRC_SKBT_CD = KK1921.MANS_PRC_SKBT_CD AND
        KK1921_GENE.KANYU_KEI_AITAI_PPLAN_CD = ? AND
        ? BETWEEN KK1921_GENE.SEIKY_AMNT_TSTAYMD AND KK1921_GENE.SEIKY_AMNT_TENDYMD AND
        KK1921_GENE.MK_FLG = '0')
#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_INSERT_002)
#--KKPRC59901
#--提供方式契約番号、マンション料金識別コード、加入契約相対料金プランコードから
#--レコードをコピーして登録する
#-----------------------------------------------------------

--KEY:KK_INSERT_002

INSERT /*KK_T_TK_HSK_KEI_PRC.SQL KK_INSERT_002*/ INTO KK_T_TK_HSK_KEI_PRC(
    TK_HOSHIKI_KEI_NO
  , MANS_PRC_SKBT_CD
  , GENE_ADD_DTM
  , OWNR_KEI_SEIKY_AMNT
  , KANYU_KEI_SEIKY_AMNT
  , KKTK_SBT_CD
  , TAKNKIKI_SBT_CD
  , KANYU_KEI_AITAI_PPLAN_CD
  , SEIKY_AMNT_TSTAYMD
  , SEIKY_AMNT_TENDYMD
  , 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
    ?
  , ?
  , ?
  , KK1921.OWNR_KEI_SEIKY_AMNT
  , KK1921.KANYU_KEI_SEIKY_AMNT
  , KK1921.KKTK_SBT_CD
  , KK1921.TAKNKIKI_SBT_CD
  , ?
  , ?
  , ?
  , 'ADD_DTM'
  , 'ADD_OPE'
  , 'UPD_DTM'
  , 'UPD_OPE'
  , NULL
  , NULL
  , '0'
  , 'ADD_UNYO'
  , 'ADD_TRN'
  , 'UPD_UNYO'
  , 'UPD_TRN'
  , NULL
  , NULL
FROM
    KK_T_TK_HSK_KEI_PRC KK1921
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ?
AND
    KK1921.MANS_PRC_SKBT_CD = ?
AND
    KK1921.KANYU_KEI_AITAI_PPLAN_CD = ?
AND
    KK1921.GENE_ADD_DTM = (SELECT
                               MAX(KK1921_GENE.GENE_ADD_DTM) AS KK1921_MAX
                           FROM
                               KK_T_TK_HSK_KEI_PRC KK1921_GENE
                           WHERE
                               KK1921_GENE.TK_HOSHIKI_KEI_NO = KK1921.TK_HOSHIKI_KEI_NO
                           AND
                               KK1921_GENE.MANS_PRC_SKBT_CD = KK1921.MANS_PRC_SKBT_CD
                           AND
                               ? BETWEEN KK1921_GENE.SEIKY_AMNT_TSTAYMD AND KK1921_GENE.SEIKY_AMNT_TENDYMD
                           AND
                               KK1921_GENE.MK_FLG = '0')
#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_UPDATE_002)
#--KKPRC59901
#--提供方式契約番号、マンション料金識別コード、加入契約相対料金プランコードから
#--更新年月日時分秒、更新オペレータアカウント、更新運用年月日、更新処理IDを更新する
#-----------------------------------------------------------

--KEY:KK_UPDATE_002

UPDATE /*KK_T_TK_HSK_KEI_PRC.SQL KK_UPDATE_002*/
    KK_T_TK_HSK_KEI_PRC KK1921
SET
    KK1921.UPD_DTM = 'UPD_DTM'
  , KK1921.UPD_OPEACNT = 'UPD_OPE'
  , KK1921.UPD_UNYO_YMD = 'UPD_UNYO'
  , KK1921.UPD_TRN_ID = 'UPD_TRN'
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ?
AND
    KK1921.MANS_PRC_SKBT_CD = ?
AND
    KK1921.KANYU_KEI_AITAI_PPLAN_CD = ?
AND
    KK1921.GENE_ADD_DTM = (SELECT
                               MAX(KK1921_GENE.GENE_ADD_DTM) AS KK1921_MAX
                           FROM
                               KK_T_TK_HSK_KEI_PRC KK1921_GENE
                           WHERE
                               KK1921_GENE.TK_HOSHIKI_KEI_NO = KK1921.TK_HOSHIKI_KEI_NO
                           AND
                               KK1921_GENE.MANS_PRC_SKBT_CD = KK1921.MANS_PRC_SKBT_CD
                           AND
                               KK1921_GENE.KANYU_KEI_AITAI_PPLAN_CD = KK1921.KANYU_KEI_AITAI_PPLAN_CD
                           AND
                               ? BETWEEN KK1921_GENE.SEIKY_AMNT_TSTAYMD AND KK1921_GENE.SEIKY_AMNT_TENDYMD
                           AND
                               KK1921_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_UPDATE_003)
#--KKPRC59901
#--提供方式契約番号、マンション料金識別コードから
#--更新年月日時分秒、更新オペレータアカウント、更新運用年月日、更新処理IDを更新する
#-----------------------------------------------------------

--KEY:KK_UPDATE_003

UPDATE /*KK_T_TK_HSK_KEI_PRC.SQL KK_UPDATE_003*/
    KK_T_TK_HSK_KEI_PRC KK1921
SET
    KK1921.UPD_DTM = 'UPD_DTM'
  , KK1921.UPD_OPEACNT = 'UPD_OPE'
  , KK1921.UPD_UNYO_YMD = 'UPD_UNYO'
  , KK1921.UPD_TRN_ID = 'UPD_TRN'
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ?
AND
    KK1921.MANS_PRC_SKBT_CD = ?
AND
    KK1921.GENE_ADD_DTM = (SELECT
                               MAX(KK1921_GENE.GENE_ADD_DTM) AS KK1921_MAX
                           FROM
                               KK_T_TK_HSK_KEI_PRC KK1921_GENE
                           WHERE
                               KK1921_GENE.TK_HOSHIKI_KEI_NO = KK1921.TK_HOSHIKI_KEI_NO
                           AND
                               KK1921_GENE.MANS_PRC_SKBT_CD = KK1921.MANS_PRC_SKBT_CD
                           AND
                               ? BETWEEN KK1921_GENE.SEIKY_AMNT_TSTAYMD AND KK1921_GENE.SEIKY_AMNT_TENDYMD
                           AND
                               KK1921_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--提供方式契約料金(KK_T_TK_HSK_KEI_PRC-KK_SELECT_009)
#--KKPRC09201
#--指定のマンション料金識別コードのカレントレコードの取得
#-----------------------------------------------------------

--KEY:KK_SELECT_009

SELECT
    KK1921.KANYU_KEI_SEIKY_AMNT
FROM
    KK_T_TK_HSK_KEI_PRC KK1921
WHERE
    KK1921.TK_HOSHIKI_KEI_NO = ?
AND
    KK1921.MANS_PRC_SKBT_CD = ?
AND
    KK1921.KANYU_KEI_AITAI_PPLAN_CD = ?
AND
    KK1921.GENE_ADD_DTM = (SELECT
                               MAX(KK1921_GENE.GENE_ADD_DTM) AS KK1921_MAX
                           FROM
                               KK_T_TK_HSK_KEI_PRC KK1921_GENE
                           WHERE
                               KK1921_GENE.TK_HOSHIKI_KEI_NO = KK1921.TK_HOSHIKI_KEI_NO
                           AND
                               KK1921_GENE.MANS_PRC_SKBT_CD = KK1921.MANS_PRC_SKBT_CD
                           AND
                               ? BETWEEN KK1921_GENE.SEIKY_AMNT_TSTAYMD AND KK1921_GENE.SEIKY_AMNT_TENDYMD
                           AND
                               KK1921_GENE.MK_FLG = '0')

#-