#-----------------------------------------------------------
#--宅内機器ワーク(ZM_T_TAKNKIKI_WK-ZM_DELETE_001)
#--宅内機器ワークのレコードを削除する。
#-----------------------------------------------------------

--KEY:ZM_DELETE_001

DELETE FROM
    ZM_T_TAKNKIKI_WK
WHERE 
    TAKNKIKI_MODEL_CD = ? AND
    KIKI_SEIZO_NO = ?

#-

#-----------------------------------------------------------
#--宅内機器ワーク(ZM_T_TAKNKIKI_WK-ZM_INSERT_001)
#--宅内機器ワークにレコードを追加する。
#-----------------------------------------------------------

--KEY:ZM_INSERT_001

INSERT INTO ZM_T_TAKNKIKI_WK(
  TAKNKIKI_MODEL_CD,
  KIKI_SEIZO_NO,
  TAKNKIKI_STAT,
  TAKNKIKI_SBT_CD,
  SHOYU_CD,
  KANRI_PLACE_SKBT_CD,
  KANRI_PLACE_CD,
  KANRI_PLACE_SHELF_CD,
  GDS_STAT_CD,
  MACAD,
  JOI_TAKNKIKI_MODEL_CD,
  JOI_KIKI_SEIZO_NO,
  RENEWAL_CNT,
  YOBI_GOODS_FLG,
  PLACE_STC_YMD,
  TAKNKIKI_NYUKA_YMD,
  TAKNKIKI_ABDMNT_YMD,
  VER,
  STB_STAT,
  STB_ID,
  CTRL_AREA_CD,
  STB_MTST_GRP_ID_1,
  STB_MTST_GRP_ID_2,
  STB_MTST_GRP_ID_3,
  STB_MTST_GRP_ID_4,
  STB_MTST_GRP_ID_5,
  STB_MTST_GRP_ID_6,
  KARI_KEY_STA_YMD,
  CAS_CARD_SBT_CD,
  CCAS_CARD_STAT,
  LEASE_STA_YMD,
  LEASE_MANRYO_YMD,
  LEASE_DSL_YMD,
  LEASE_PRD,
  LEASE_CNT,
  ADD_DTM,
  ADD_OPEACNT,
  ADD_UNYO_YMD,
  ADD_TRN_ID,
  UPD_DTM,
  UPD_OPEACNT,
  UPD_UNYO_YMD,
  UPD_TRN_ID,
  MK_FLG
) VALUES (
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  ?,
  'ADD_DTM',
  'ADD_OPE',
  'ADD_UNYO',
  'ADD_TRN',
  'UPD_DTM',
  'UPD_OPE',
  'UPD_UNYO',
  'UPD_TRN',
  '0'
)

#-

#-----------------------------------------------------------
#--宅内機器ワーク(ZM_T_TAKNKIKI_WK-KK_SELECT_001)
#--処理対象となる宅内機器ワークを抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT 
  ZM0471.STB_ID
FROM 
  ZM_T_TAKNKIKI_WK ZM0471
WHERE 
  ZM0471.TAKNKIKI_MODEL_CD = ?
AND
  ZM0471.KIKI_SEIZO_NO = ?
AND
  ZM0471.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--宅内機器ワーク(ZM_T_TAKNKIKI_WK-KK_SELECT_002)
#--処理対象となる宅内機器ワークを抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
  ZM0471.STB_ID
FROM
  ZM_T_TAKNKIKI_WK ZM0471
  INNER JOIN KK_T_KKTK_SVC_KEI KK0341
     ON ZM0471.TAKNKIKI_MODEL_CD   = KK0341.TAKNKIKI_MODEL_CD AND ZM0471.KIKI_SEIZO_NO   = KK0341.KIKI_SEIZO_NO
  INNER JOIN KK_T_SVC_KEI_UCWK KK0161
     ON KK0161.SVC_KEI_UCWK_NO = KK0341.SVC_KEI_UCWK_NO
WHERE
     ZM0471.MK_FLG = '0'
 AND KK0341.KKTK_SVC_CD = 'C009'
 AND KK0341.KKTK_SVC_KEI_STAT IN('020','030','100','210','220')
 AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) =
      (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_MAX
       FROM   KK_T_KKTK_SVC_KEI KK0341_GENE
       WHERE  KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
        AND   KK0341_GENE.RSV_APLY_YMD <= ?
        AND   KK0341_GENE.RSV_APLY_CD = '2'
        AND   KK0341_GENE.MK_FLG = '0'
       GROUP BY KK0341_GENE.KKTK_SVC_KEI_NO)
 AND KK0161.SVC_KEI_NO = ?
 AND KK0161.SVC_KEI_UCWK_STAT IN('020','030','100','210','220')
 AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) =
      (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_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'
       GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO)
ORDER BY ZM0471.TAKNKIKI_MODEL_CD, ZM0471.KIKI_SEIZO_NO

#-