#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_SELECT_001)
#--問合せ情報一括削除_メール受信番号取得
#-----------------------------------------------------------

--KEY:CR_SELECT_001

SELECT
 DTL.MAIL_RCV_NO
FROM
 CR_T_TAIOKRK_DTL DTL
WHERE
 DTL.TAIO_KIROK_NO = ?
 AND DTL.MAIL_RCV_NO IS NOT NULL
 AND DTL.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_SELECT_002)
#--問合せ情報一括削除_メール送信番号取得
#-----------------------------------------------------------

--KEY:CR_SELECT_002

SELECT
 DTL.MAIL_SEND_NO
FROM
 CR_T_TAIOKRK_DTL DTL
WHERE
 DTL.TAIO_KIROK_NO = ?
 AND DTL.MAIL_SEND_NO IS NOT NULL
 AND DTL.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_UPDATE_001)
#--問合せ情報一括削除_対応記録明細論理削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_001

UPDATE CR_T_TAIOKRK_DTL
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_DTL-CR_SELECT_004)
#--ホットボイス抽出処理コール対応データ取得
#-----------------------------------------------------------

--KEY:CR_SELECT_004

SELECT
  /*+ index(TAIOKRK_DTL CR_T_TAIOKRK_DTL_IDX_03) */
  TAIOKRK_DTL.TAIO_KIROK_NO,
  TAIOKRK_DTL.TAIO_KIROK_DTL_NO,
  TAIOKRK_DTL.TOIAWASE_NAIYO,
  TAIOKRK_DTL.ANS_NAIYO,
  TAIOKRK_DTL.ACTION_STA_DTM,
  M_USER.USER_NM,
  SYSDT_TBL.SYSDTM
FROM
  CR_T_TAIOKRK_DTL TAIOKRK_DTL
  ,ZM_M_USER M_USER
  ,( SELECT
      ? || '235959' AS SYSDTM
    FROM DUAL
  ) SYSDT_TBL
  ,(
      SELECT
        BAT_TBL.BATCTRL_VALUE AS DATE_VAL
      FROM
        CR_M_TAIORRK_BATCTRL BAT_TBL
      WHERE
        BAT_TBL.TAIORRK_BATCTRL_NO = 'EO90E01100'
        AND BAT_TBL.MK_FLG = '0'
        AND BAT_TBL.BATCTRL_VALUE IS NOT NULL
      UNION ALL
      SELECT
        TO_CHAR((TO_DATE(SYSDT_TBL.SYSDTM, 'yyyymmddhh24miss')-1),'yyyymmddhh24miss') AS DATE_VAL
      FROM
        CR_M_TAIORRK_BATCTRL BAT_TBL,
        (
          SELECT
            ? || '235959' AS SYSDTM
          FROM DUAL
        ) SYSDT_TBL
      WHERE
        BAT_TBL.TAIORRK_BATCTRL_NO = 'EO90E01100'
        AND BAT_TBL.MK_FLG = '0'
        AND BAT_TBL.BATCTRL_VALUE IS NULL
  ) TBL1
WHERE
  TAIOKRK_DTL.ACTION_STA_DTM > TBL1.DATE_VAL
  AND TAIOKRK_DTL.ACTION_STA_DTM <= SYSDT_TBL.SYSDTM
  AND TAIOKRK_DTL.TAIO_USER_ID = M_USER.USER_ID
  AND TAIOKRK_DTL.ACTION_TYPE_CD = '02'
  AND TAIOKRK_DTL.MK_FLG = '0'
  AND M_USER.MK_FLG = '0'
  AND M_USER.USER_TSTAYMD <= SUBSTR(TAIOKRK_DTL.UPD_DTM, 1, 8)
  AND M_USER.USER_TENDYMD >= SUBSTR(TAIOKRK_DTL.UPD_DTM, 1, 8)
ORDER BY
  TAIOKRK_DTL.TAIO_KIROK_NO,
  TAIOKRK_DTL.TAIO_KIROK_DTL_NO

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_SELECT_005)
#--応対区分抽出_コール対応データ検索
#-----------------------------------------------------------

--KEY:CR_SELECT_005

SELECT
  /*+ index(TAIOKRK_DTL CR_T_TAIOKRK_DTL_IDX_03) */
  TAIOKRK_DTL.TAIO_KIROK_NO,
  TAIOKRK_DTL.TAIO_KIROK_DTL_NO,
  TAIOKRK_DTL.TAIO_USER_ID,
  TAIOKRK_DTL.TOIAWASE_NAIYO,
  TAIOKRK_DTL.ANS_NAIYO,
  SYSDT_TBL.SYSDTM
FROM
  CR_T_TAIOKRK_DTL TAIOKRK_DTL,
  ( SELECT
      ? || '235959' AS SYSDTM
    FROM DUAL
  ) SYSDT_TBL
  ,(
      SELECT
        BAT_TBL.BATCTRL_VALUE AS DATE_VAL
      FROM
        CR_M_TAIORRK_BATCTRL BAT_TBL
      WHERE
        BAT_TBL.TAIORRK_BATCTRL_NO = 'EO90E02100'
        AND BAT_TBL.MK_FLG = '0'
        AND BAT_TBL.BATCTRL_VALUE IS NOT NULL
      UNION ALL
      SELECT
        TO_CHAR((TO_DATE(SYSDT_TBL.SYSDTM, 'yyyymmddhh24miss')-1),'yyyymmddhh24miss') AS DATE_VAL
      FROM
        CR_M_TAIORRK_BATCTRL BAT_TBL,
        (
          SELECT
            ? || '235959' AS SYSDTM
          FROM DUAL
        ) SYSDT_TBL
      WHERE
        BAT_TBL.TAIORRK_BATCTRL_NO = 'EO90E02100'
        AND BAT_TBL.MK_FLG = '0'
        AND BAT_TBL.BATCTRL_VALUE IS NULL
  ) TBL1
WHERE
  TAIOKRK_DTL.ACTION_STA_DTM > TBL1.DATE_VAL
  AND TAIOKRK_DTL.ACTION_STA_DTM <= SYSDT_TBL.SYSDTM
  AND TAIOKRK_DTL.ACTION_TYPE_CD = '02'
  AND TAIOKRK_DTL.MK_FLG = '0'
ORDER BY
  TAIOKRK_DTL.TAIO_KIROK_NO,
  TAIOKRK_DTL.TAIO_KIROK_DTL_NO

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_SELECT_006)
#--最大の対応記録明細番号取得
#--・エスカレーション一括処理
#--・メモ登録一括処理
#--・メール送信一括処理
#--・変更申込書類送付完了処理
#-----------------------------------------------------------

--KEY:CR_SELECT_006

SELECT
  MAX(TKDL.TAIO_KIROK_DTL_NO) AS TAIO_KIROK_DTL_NO
FROM
  CR_T_TAIOKRK_DTL TKDL
WHERE
  TKDL.TAIO_KIROK_NO = ?

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_UPDATE_002)
#--編集状態フラグ更新
#--・メモ登録一括処理
#-----------------------------------------------------------

--KEY:CR_UPDATE_002

UPDATE CR_T_TAIOKRK_DTL
SET
 HENSHU_STAT_FLG = '0',
 UPD_DTM = ?,
 UPD_UNYO_YMD = 'UPD_UNYO',
 UPD_TRN_ID = 'UPD_TRN',
 UPD_OPEACNT = ?
WHERE
 TAIO_KIROK_NO = ?
 AND HENSHU_STAT_FLG = '1'
 AND MK_FLG = '0'

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_UPDATE_003)
#--送信不要明細更新
#--・メール送信一括処理
#-----------------------------------------------------------

--KEY:CR_UPDATE_003

UPDATE CR_T_TAIOKRK_DTL
SET
 SEND_STAT_CD = '3',
 UPD_DTM = ?,
 UPD_UNYO_YMD = 'UPD_UNYO',
 UPD_TRN_ID = 'UPD_TRN',
 UPD_OPEACNT = ?
WHERE
 TAIO_KIROK_NO = ?
 AND TAIO_KIROK_DTL_NO <> ?
 AND ACTION_TYPE_CD = ?
 AND SEND_STAT_CD = '1'
 AND MK_FLG = '0'

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_SELECT_007)
#--未送信明細レコード件数取得
#--・メール送信一括処理
#-----------------------------------------------------------

--KEY:CR_SELECT_007

SELECT
  COUNT(*) AS CNT
FROM
  CR_T_TAIOKRK_DTL TKDL
WHERE
  TKDL.TAIO_KIROK_NO = ?
  AND TKDL.TAIO_KIROK_DTL_NO <> ?
  AND TKDL.ACTION_TYPE_CD IN ('06', '08')
  AND TKDL.ACTION_TYPE_CD <> ?
  AND TKDL.SEND_STAT_CD = '1'
  AND TKDL.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_UPDATE_004)
#--対応記録明細論理削除
#--・未加入問合せ情報削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_004

UPDATE CR_T_TAIOKRK_DTL
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_DTL-CR_UPDATE_005)
#--対応記録明細論理削除
#--・問合せ情報一括削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_005

UPDATE CR_T_TAIOKRK_DTL
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'

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-CR_SELECT_008)
#--対応記録明細件数取得
#--・エスカレーション一括処理
#-----------------------------------------------------------

--KEY:CR_SELECT_008

SELECT
  COUNT(*) AS CNT
FROM
  CR_T_TAIOKRK_DTL TKDL
WHERE
  TKDL.TAIO_KIROK_NO = ?
  AND TKDL.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--対応記録明細(CR_T_TAIOKRK_DTL-KK_SELECT_001)
#--問合せ内容、回答内容、メモ内容の取得
#--該当レコードが複数の場合は、更新年月日時分秒が最大のレコードから取得する。
#--・圧着ハガキ抑止対象チェック処理(KKPRC58502)
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT /*CR_T_TAIOKRK_DTL.SQL KK_SELECT_001*/
  TKDL.TAIO_KIROK_NO,
  TKDL.TOIAWASE_NAIYO,
  TKDL.ANS_NAIYO,
  TKDL.MEMO_NAIYO
FROM
  CR_T_TAIOKRK_DTL TKDL
WHERE
  TKDL.TAIO_KIROK_NO = ?
  AND TKDL.MK_FLG = '0'
ORDER BY TKDL.UPD_DTM DESC

#-