#-----------------------------------------------------------
#--資料送付(CR_T_ATICLE_SOHU-CR_UPDATE_001)
#--問合せ情報一括削除_資料送付論理削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_001

UPDATE CR_T_ATICLE_SOHU
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_ATICLE_SOHU-CR_UPDATE_002)
#--資料送付論理削除
#--・未加入問合せ情報削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_002

UPDATE CR_T_ATICLE_SOHU
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_ATICLE_SOHU-CR_UPDATE_003)
#--資料送付論理削除
#--・問合せ情報一括削除
#-----------------------------------------------------------

--KEY:CR_UPDATE_003

UPDATE CR_T_ATICLE_SOHU
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_ATICLE_SOHU-CR_SELECT_001)
#--変更申込書類送付情報取得
#-----------------------------------------------------------

--KEY:CR_SELECT_001

SELECT
  TKRK.SVC_KEI_NO,
  TKRK.TAIO_KIROK_NO,
  SOHU.ATICLE_SOHU_NO,
  SKRK.ATICLE_SOHU_KIROK_NO,
  ATCL.ATICLE_NM,
  SKRK.SOHU_CNT,
  (
    SELECT
      CDNM.CD_DIV_NM
    FROM
      ZM_M_CD_NM_KANRI CDNM
    WHERE
      CDNM.CD_SBT_CD = 'CD00304'
      AND CDNM.CD_DIV = SKRK.ATICLE_SOHU_SBT_CD
      AND CDNM.CD_TSTAYMD <= ?
      AND CDNM.CD_TENDYMD >= ?
      AND CDNM.MK_FLG = '0'
  ) ATICLE_SOHU_SBT_NM,
  SOHU.SOHUS_NM,
  SOHU.SOHUS_KANA_NM,
  SOHU.SOHUS_PCD,
  SOHU.SOHUS_STATE_NM,
  SOHU.SOHUS_CITY_NM,
  SOHU.SOHUS_OAZTSU_NM,
  SOHU.SOHUS_AZCHO_NM,
  SOHU.SOHUS_BNCHIGO,
  SOHU.SOHUS_ADRTTM,
  SOHU.SOHUS_ADRRM,
  SOHU.SOHUS_TELNO,
  SKRK.ATICLE_SOHU_KRK_SAKSEI_YMD
FROM
  CR_T_TAIO_KIROK TKRK,
  CR_T_QUE_BOX QBOX,
  CR_T_ATICLE_SOHU SOHU,
  CR_T_ATICLE_SOHU_KRK SKRK,
  CR_M_ATICLE ATCL
WHERE
  TKRK.SVC_KEI_NO IS NOT NULL
  AND TKRK.TOIAWASE_STAT_CD = '102'
  AND TKRK.MK_FLG = '0'
  AND QBOX.QUE_BOX_NO = TKRK.QUE_BOX_NO
  AND QBOX.QUE_TYPE_CD = '1403'
  AND QBOX.MK_FLG = '0'
  AND SOHU.TAIO_KIROK_NO = TKRK.TAIO_KIROK_NO
  AND SOHU.MK_FLG = '0'
  AND SKRK.TAIO_KIROK_NO = SOHU.TAIO_KIROK_NO
  AND SKRK.ATICLE_SOHU_NO = SOHU.ATICLE_SOHU_NO
  AND SKRK.ATICLE_SOHU_KIROK_STAT = '001'
  AND SKRK.MK_FLG = '0'
  AND ATCL.ATICLE_NO = SKRK.ATICLE_NO
  AND ATCL.ATICLE_OPUT_SBT_CD = '2'
  AND ATCL.MK_FLG = '0'
ORDER BY
  SKRK.TAIO_KIROK_NO,
  SKRK.ATICLE_SOHU_NO,
  SKRK.ATICLE_SOHU_KIROK_NO

#-