#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-AC_SELECT_001)
#--【内容】
#--    ディレイド依頼を行った、
#--    登録オペレーションIDおよび更新オペレーションIDを取得する。
#--【検索条件】
#--    ディレイド処理依頼番号(DLYD_TRN_REQ_NO)
#-- 【結合テーブル】
#--    なし
#--【結合条件】
#--    なし
#--【抽出対象】
#--    一意
#--【世代管理】
#--    なし
#--【予約管理】
#--    なし
#--【無効フラグ考慮】
#--    あり
#-----------------------------------------------------------

--KEY:AC_SELECT_001

SELECT
     CC0111.DLYD_TRN_REQ_NO
    ,CC0111.ADD_OPEACNT
    ,CC0111.UPD_OPEACNT
FROM
    CC_T_DLYD_TRN_REQ CC0111
WHERE
    DLYD_TRN_REQ_NO = ?
#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-SELECT)
#-----------------------------------------------------------

--KEY:CC_SELECT_001
SELECT
 DLY.DLYD_TRN_REQ_NO AS DLYD_TRN_REQ_NO,
 DLY.DLYD_TRN_REQ_STAT AS DLYD_TRN_REQ_STAT,
 DLY.INPUT_EFILE_KANRI_NO AS INPUT_EFILE_KANRI_NO,
 DLY.INPUT_EFILE_GENE_ADD_DTM AS INPUT_EFILE_GENE_ADD_DTM,
 DLY.REQ_JOB_ID AS REQ_JOB_ID,
 DLY.SYS_CD AS SYS_CD,
 DLY.DLYD_TRN_REQ_SBT_CD AS DLYD_TRN_REQ_SBT_CD,
 DLY.DLYD_TRN_RSLT_CD AS DLYD_TRN_RSLT_CD,
 DLY.ERR_EFILE_KANRI_NO AS ERR_EFILE_KANRI_NO,
 DLY.ERR_EFILE_GENE_ADD_DTM AS ERR_EFILE_GENE_ADD_DTM,
 EFL.EFILE AS INPUT_EFILE,
 EFL.BUTSURI_FILE_NM AS INPUT_EFILE_BUTSURI_NM,
 EFL.FILE_SIZE AS INPUT_EFILE_SIZE
FROM CC_T_DLYD_TRN_REQ DLY
 LEFT OUTER JOIN
      CC_T_EFILE_KANRI EFL
 ON DLY.INPUT_EFILE_KANRI_NO     = EFL.EFILE_KANRI_NO
    AND DLY.INPUT_EFILE_GENE_ADD_DTM = EFL.GENE_ADD_DTM
WHERE DLY.REQ_JOB_ID = ?
  AND ( DLY.DLYD_TRN_JSSI_UNYO_DTM <= ?
        OR  DLY.DLYD_TRN_JSSI_UNYO_DTM = '00000000000000000' )
  AND DLY.DLYD_TRN_RSLT_CD IS NULL
  AND DLY.MK_FLG = '0'
ORDER BY DLY.DLYD_TRN_REQ_NO DESC
#-

--KEY:CC_SELECT_002
SELECT DLY.DLYD_TRN_REQ_NO
FROM CC_T_DLYD_TRN_REQ DLY
WHERE DLY.REQ_JOB_ID = ?
  AND ( DLY.DLYD_TRN_JSSI_UNYO_DTM <= ?
        OR  DLY.DLYD_TRN_JSSI_UNYO_DTM = '00000000000000000' )
  AND DLY.DLYD_TRN_REQ_STAT = '001'
  AND DLY.DLYD_TRN_RSLT_CD IS NULL
  AND DLY.MK_FLG = '0'
FOR UPDATE
#-

--KEY:CC_SELECT_003
SELECT
 DLY.DLYD_TRN_REQ_NO AS DLYD_TRN_REQ_NO,
 DLY.DLYD_TRN_REQ_STAT AS DLYD_TRN_REQ_STAT,
 DLY.INPUT_EFILE_KANRI_NO AS INPUT_EFILE_KANRI_NO,
 DLY.INPUT_EFILE_GENE_ADD_DTM AS INPUT_EFILE_GENE_ADD_DTM,
 DLY.REQ_JOB_ID AS REQ_JOB_ID,
 DLY.SYS_CD AS SYS_CD,
 DLY.DLYD_TRN_REQ_SBT_CD AS DLYD_TRN_REQ_SBT_CD,
 DLY.DLYD_TRN_RSLT_CD AS DLYD_TRN_RSLT_CD,
 DLY.ERR_EFILE_KANRI_NO AS ERR_EFILE_KANRI_NO,
 DLY.ERR_EFILE_GENE_ADD_DTM AS ERR_EFILE_GENE_ADD_DTM,
 EFL.EFILE AS INPUT_EFILE,
 EFL.BUTSURI_FILE_NM AS INPUT_EFILE_BUTSURI_NM,
 EFL.FILE_SIZE AS INPUT_EFILE_SIZE
FROM CC_T_DLYD_TRN_REQ DLY
 LEFT OUTER JOIN
      CC_T_EFILE_KANRI EFL
 ON DLY.INPUT_EFILE_KANRI_NO     = EFL.EFILE_KANRI_NO
    AND DLY.INPUT_EFILE_GENE_ADD_DTM = EFL.GENE_ADD_DTM
WHERE DLY.DLYD_TRN_REQ_NO IN (?)
ORDER BY DLY.DLYD_TRN_REQ_NO DESC
#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-AC_UPDATE_001)
#--【内容】
#--ディレイド処理依頼の状態を「処理中」から「処理済」に変更する。
#-----------------------------------------------------------

--KEY:AC_UPDATE_001

UPDATE CC_T_DLYD_TRN_REQ
SET
 DLYD_TRN_RSLT_CD = '1',
 DLYD_TRN_REQ_STAT = '002',
 UPD_DTM = ?,
 UPD_OPEACNT = ?,
 UPD_UNYO_YMD = 'UPD_UNYO',
 UPD_TRN_ID   = 'UPD_TRN'
WHERE REQ_JOB_ID = ?
AND DLYD_TRN_REQ_STAT = '004'

#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-UPDATE)
#-----------------------------------------------------------

--KEY:CC_UPDATE_001
UPDATE CC_T_DLYD_TRN_REQ
SET
 DLYD_TRN_RSLT_CD = ?,
 DLYD_TRN_REQ_STAT = ?,
 ERR_EFILE_KANRI_NO = ?,
 ERR_EFILE_GENE_ADD_DTM = ?,
 UPD_DTM = ?,
 UPD_OPEACNT = ?,
 UPD_UNYO_YMD = 'UPD_UNYO',
 UPD_TRN_ID   = 'UPD_TRN'
WHERE DLYD_TRN_REQ_NO = ?
#-

--KEY:CC_UPDATE_002
UPDATE CC_T_DLYD_TRN_REQ
SET
 DLYD_TRN_RSLT_CD = ?,
 DLYD_TRN_REQ_STAT = ?,
 UPD_DTM = ?,
 UPD_OPEACNT = ?,
 UPD_UNYO_YMD = 'UPD_UNYO',
 UPD_TRN_ID   = 'UPD_TRN'
WHERE DLYD_TRN_REQ_NO = ?
#-

--KEY:CC_UPDATE_003
UPDATE CC_T_DLYD_TRN_REQ
SET
 DLYD_TRN_REQ_STAT = '004',
 UPD_DTM = ?,
 UPD_OPEACNT = ?,
 UPD_UNYO_YMD = 'UPD_UNYO',
 UPD_TRN_ID   = 'UPD_TRN'
WHERE DLYD_TRN_REQ_NO = ?
#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-DK_SELECT_001)
#--ディレイド処理依頼番号を条件に電子ファイル管理情報を抽出する
#-----------------------------------------------------------

--KEY:DK_SELECT_001

SELECT
    DLY.DLYD_TRN_REQ_NO AS DLYD_TRN_REQ_NO,
    DLY.DLYD_TRN_REQ_STAT AS DLYD_TRN_REQ_STAT,
    DLY.INPUT_EFILE_KANRI_NO AS INPUT_EFILE_KANRI_NO,
    DLY.INPUT_EFILE_GENE_ADD_DTM AS INPUT_EFILE_GENE_ADD_DTM,
    DLY.REQ_JOB_ID AS REQ_JOB_ID,
    DLY.SYS_CD AS SYS_CD,
    DLY.DLYD_TRN_REQ_SBT_CD AS DLYD_TRN_REQ_SBT_CD,
    DLY.DLYD_TRN_RSLT_CD AS DLYD_TRN_RSLT_CD,
    DLY.ERR_EFILE_KANRI_NO AS ERR_EFILE_KANRI_NO,
    DLY.ERR_EFILE_GENE_ADD_DTM AS ERR_EFILE_GENE_ADD_DTM,
    EFL.EFILE AS INPUT_EFILE,
    EFL.BUTSURI_FILE_NM AS INPUT_EFILE_BUTSURI_NM,
    EFL.FILE_SIZE AS INPUT_EFILE_SIZE
FROM CC_T_DLYD_TRN_REQ DLY
     LEFT OUTER JOIN CC_T_EFILE_KANRI EFL
     ON  DLY.INPUT_EFILE_KANRI_NO     = EFL.EFILE_KANRI_NO
     AND DLY.INPUT_EFILE_GENE_ADD_DTM = EFL.GENE_ADD_DTM
     AND EFL.MK_FLG = '0'
WHERE DLY.DLYD_TRN_REQ_NO = ?
      AND DLY.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-ZM_DELETE_001)
#--PKが一致するレコードを削除する。
#-----------------------------------------------------------

--KEY:ZM_DELETE_001

DELETE FROM
    CC_T_DLYD_TRN_REQ DLYD
WHERE
    DLYD.DLYD_TRN_REQ_NO IN (?)

#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-KK_SELECT_001)
#--DLYD_TRN_REQ_STAT = '004' のレコードを検索する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001
SELECT DLY.DLYD_TRN_REQ_NO
FROM CC_T_DLYD_TRN_REQ DLY
WHERE DLY.REQ_JOB_ID = ?
  AND ( DLY.DLYD_TRN_JSSI_UNYO_DTM <= ?
        OR  DLY.DLYD_TRN_JSSI_UNYO_DTM = '00000000000000000' )
  AND DLY.DLYD_TRN_REQ_STAT = '004'
  AND DLY.DLYD_TRN_RSLT_CD IS NULL
  AND DLY.MK_FLG = '0'
ORDER BY DLY.DLYD_TRN_REQ_NO

#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-CH_SELECT_001)
#--mineo支払方法未登録ファイル抽出
#-----------------------------------------------------------

--KEY:CH_SELECT_001
SELECT 
    DLY.DLYD_TRN_REQ_STAT AS DLYD_TRN_REQ_STAT,
    DLY.DLYD_TRN_RSLT_CD AS DLYD_TRN_RSLT_CD,
    DLY.INPUT_EFILE_KANRI_NO,
    EFL.EFILE AS INPUT_EFILE,
    EFL.BUTSURI_FILE_NM AS INPUT_EFILE_BUTSURI_NM,
    EFL.FILE_SIZE AS INPUT_EFILE_SIZE
FROM CC_T_DLYD_TRN_REQ DLY
LEFT OUTER JOIN CC_T_EFILE_KANRI EFL
ON  DLY.INPUT_EFILE_KANRI_NO     = EFL.EFILE_KANRI_NO
AND DLY.INPUT_EFILE_GENE_ADD_DTM = EFL.GENE_ADD_DTM
AND EFL.MK_FLG = '0'
WHERE DLY.REQ_JOB_ID = ?
  AND ( DLY.INPUT_EFILE_GENE_ADD_DTM >= ?
        AND  DLY.INPUT_EFILE_GENE_ADD_DTM  <= ? )
  AND DLY.MK_FLG = '0'
ORDER BY DLY.DLYD_TRN_REQ_NO DESC

#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-AK_SELECT_001)
#--セット割補正　料金調整一括登録データ抽出
#-----------------------------------------------------------

--KEY:AK_SELECT_001
SELECT
     CC0111.DLYD_TRN_REQ_NO
    ,CC0111.DLYD_TRN_REQ_STAT
    ,CC0111.REQ_JOB_ID
    ,CC0111.DLYD_TRN_JSSI_UNYO_DTM
    ,CC0111.ADD_OPEACNT
    ,CC0111.UPD_OPEACNT
FROM
    CC_T_DLYD_TRN_REQ CC0111
WHERE
    REQ_JOB_ID = ?
AND DLYD_TRN_REQ_STAT in(?,?) 

#-

#-----------------------------------------------------------
#--ディレイド処理依頼(CC_T_DLYD_TRN_REQ-AC_SELECT_002)
#--期間で完了しているデータを抽出
#-----------------------------------------------------------

--KEY:AC_SELECT_002

SELECT
     CC0111.DLYD_TRN_REQ_NO
    ,CC0111.ADD_UNYO_YMD
    ,CC0111.UPD_OPEACNT
FROM
    CC_T_DLYD_TRN_REQ CC0111
WHERE
    CC0111.MK_FLG = '0'
AND CC0111.DLYD_TRN_REQ_STAT = '002'
AND CC0111.REQ_JOB_ID = ?
AND ( ? < CC0111.ADD_UNYO_YMD AND CC0111.ADD_UNYO_YMD <= ? )

#-