#-----------------------------------------------------------
#--対応記録内訳(CR_T_TAIOKRK_UCWK-CR_UPDATE_001)
#--問合せ情報一括削除_対応記録内訳論理削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_001

UPDATE CR_T_TAIOKRK_UCWK
SET
 UPD_DTM = ?,
 UPD_UNYO_YMD = 'UPD_UNYO',
 UPD_TRN_ID = 'UPD_TRN',
 UPD_OPEACNT = ?,
 DEL_DTM = ?,
 DEL_UNYO_YMD = 'DEL_UNYO',
 DEL_TRN_ID = 'DEL_TRN',
 DEL_OPEACNT = ?,
 MK_FLG = '1'
WHERE
 TAIO_KIROK_NO = ?

#-

#-----------------------------------------------------------
#--対応記録内訳(CR_T_TAIOKRK_UCWK-CR_SELECT_001)
#--問合せ処理_問合せモニターデータ登録
#--・問合せ処理共通部品
#-----------------------------------------------------------

--KEY:CR_SELECT_001

SELECT
 UCWK.TAIO_KIROK_UCWK_NO
FROM
 CR_T_TAIOKRK_UCWK UCWK
WHERE
 UCWK.TAIO_KIROK_NO = ?
 AND UCWK.MK_FLG = '0'
ORDER BY
 UCWK.TAIO_KIROK_NO,
 UCWK.TAIO_KIROK_UCWK_NO

#-

#-----------------------------------------------------------
#--対応記録内訳(CR_T_TAIOKRK_UCWK-CR_SELECT_002)
#--ホットボイスデータ取得
#-----------------------------------------------------------

--KEY:CR_SELECT_002

SELECT
  TAIOKRK_UCWK.TAIO_KIROK_NO,
  TAIOKRK_UCWK.TAIO_KIROK_UCWK_NO,
  TAIOKRK_UCWK.CUST_KOE,
  TAIOKRK_UCWK.CSR_KOE,
  NM_KANRI.CD_DIV_NM AS RSP_DIV_NM
FROM
  CR_T_TAIOKRK_UCWK TAIOKRK_UCWK
  LEFT OUTER JOIN ZM_M_CD_NM_KANRI NM_KANRI
  ON TAIOKRK_UCWK.TOIAWASE_RSP_DIV = NM_KANRI.CD_DIV
  AND NM_KANRI.CD_SBT_CD = 'CD00234'
  AND NM_KANRI.MK_FLG = '0' ,  
  CR_T_TIOKR_UW_CST_WK UW_CST_WK
  
WHERE
  TAIOKRK_UCWK.TAIO_KIROK_NO = ?
  AND TAIOKRK_UCWK.TAIO_KIROK_NO = UW_CST_WK.TAIO_KIROK_NO
  AND TAIOKRK_UCWK.TAIO_KIROK_UCWK_NO = UW_CST_WK.TAIO_KIROK_UCWK_NO 
  AND
  (
      TRIM(TAIOKRK_UCWK.CUST_KOE) IS NOT NULL
      OR TRIM(TAIOKRK_UCWK.CSR_KOE) IS NOT NULL
  )
  AND UW_CST_WK.CHSHT_DATA_SBT_CD  = '4'
  AND TAIOKRK_UCWK.MK_FLG = '0'
  AND UW_CST_WK.MK_FLG = '0'
ORDER BY
  TAIOKRK_UCWK.TAIO_KIROK_NO ,
  TAIOKRK_UCWK.TAIO_KIROK_UCWK_NO

#-

#-----------------------------------------------------------
#--対応記録内訳(CR_T_TAIOKRK_UCWK-CR_SELECT_003)
#--応対区分抽出処理_応対データ検索
#-----------------------------------------------------------

--KEY:CR_SELECT_003

SELECT
  TAIOKRK_UCWK.TAIO_KIROK_NO,
  TAIOKRK_UCWK.TAIO_KIROK_UCWK_NO,
  TAIOKRK_UCWK.TOIAWASE_RSP_DIV,
  NM_KANRI.CD_DIV_NM,
  UW_CST_WK.CHSHT_DATA_SBT_CD
FROM
  CR_T_TAIOKRK_UCWK TAIOKRK_UCWK
  LEFT OUTER JOIN ZM_M_CD_NM_KANRI NM_KANRI
  ON TAIOKRK_UCWK.TOIAWASE_RSP_DIV = NM_KANRI.CD_DIV
  AND NM_KANRI.CD_SBT_CD = 'CD00234'
  AND NM_KANRI.MK_FLG = '0' ,  
  CR_T_TIOKR_UW_CST_WK UW_CST_WK
WHERE
  TAIOKRK_UCWK.TAIO_KIROK_NO = ?
  AND TAIOKRK_UCWK.TAIO_KIROK_NO = UW_CST_WK.TAIO_KIROK_NO
  AND TAIOKRK_UCWK.TAIO_KIROK_UCWK_NO = UW_CST_WK.TAIO_KIROK_UCWK_NO
  AND TAIOKRK_UCWK.TOIAWASE_RSP_DIV IS NOT NULL
  AND UW_CST_WK.CHSHT_DATA_SBT_CD  = '5'
  AND TAIOKRK_UCWK.MK_FLG = '0'
  AND UW_CST_WK.MK_FLG = '0'
ORDER BY
  TAIOKRK_UCWK.TAIO_KIROK_NO ,
  TAIOKRK_UCWK.TAIO_KIROK_UCWK_NO

#-

#-----------------------------------------------------------
#--対応記録内訳(CR_T_TAIOKRK_UCWK-CR_UPDATE_002)
#--対応記録内訳論理削除
#--・未加入問合せ情報削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_002

UPDATE CR_T_TAIOKRK_UCWK
SET
  UPD_DTM = ?,
  UPD_UNYO_YMD = 'UPD_UNYO',
  UPD_TRN_ID = 'UPD_TRN',
  UPD_OPEACNT = ?,
  DEL_DTM = ?,
  DEL_UNYO_YMD = 'DEL_UNYO',
  DEL_TRN_ID = 'DEL_TRN',
  DEL_OPEACNT = ?,
  MK_FLG = '1'
WHERE
  TAIO_KIROK_NO IN (
    SELECT
      TKRK.TAIO_KIROK_NO
    FROM
      CR_T_TAIO_KIROK TKRK,
      CK_T_PROSCST PCST
    WHERE
      TKRK.PROSCST_NO = PCST.PROSCST_NO
      AND TKRK.MK_FLG = '0'
      AND PCST.MK_FLG = '2'
  )
  AND MK_FLG = '0'

#-

#-----------------------------------------------------------
#--対応記録内訳(CR_T_TAIOKRK_UCWK-CR_UPDATE_003)
#--対応記録内訳論理削除
#--・問合せ情報一括削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_003

UPDATE CR_T_TAIOKRK_UCWK
SET
  UPD_DTM = ?,
  UPD_UNYO_YMD = 'UPD_UNYO',
  UPD_TRN_ID = 'UPD_TRN',
  UPD_OPEACNT = ?,
  DEL_DTM = ?,
  DEL_UNYO_YMD = 'DEL_UNYO',
  DEL_TRN_ID = 'DEL_TRN',
  DEL_OPEACNT = ?,
  MK_FLG = '1'
WHERE
  TAIO_KIROK_NO IN (
    SELECT
      TKRK.TAIO_KIROK_NO
    FROM
      CR_T_TAIO_KIROK TKRK
    WHERE
      TKRK.QUE_BOX_NO = ?
      AND TKRK.MK_FLG = '0'
  )
  AND MK_FLG = '0'

#-