#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_SELECT_001)
#--KKPRC02201
#--工事取消した情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
KK1481.KOJIAK_NO,
KK1481.KOJIAK_STP_CANCEL_PRC_UM,
KK1481.KOJIAK_STP_UK_YMD
FROM
KK_T_KJ_CL_WK KK1481
INNER JOIN
KU_T_MSKM_DTL_KOJIAK KU0091
ON
KK1481.KOJIAK_NO = KU0091.KOJIAK_NO
AND KU0091.MK_FLG = '0'
WHERE
KK1481.SVC_KEI_NO = ? AND
KK1481.KOJIAK_NO = ? AND
KK1481.RNKI_YMD || KK1481.RNKI_SEQ =
         (SELECT MAX(KK1481_GENE.RNKI_YMD || KK1481_GENE.RNKI_SEQ ) AS KK1481_MAX
          FROM   KK_T_KJ_CL_WK KK1481_GENE 
          WHERE  KK1481_GENE.SVC_KEI_NO = KK1481.SVC_KEI_NO
          AND    KK1481_GENE.SVC_KEI_KAISEN_UCWK_NO = KK1481.SVC_KEI_KAISEN_UCWK_NO
          AND    KK1481_GENE.KOJIAK_SBT_CD IN('001','003','009','013')
          AND    KK1481_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_SELECT_002)
#--KKPRC38701
#--工事案件種別コードが
#--"eo光電話宅内工事"
#--又は"移設"であるデータを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
 KK1481.SVC_KEI_NO, 
 KK1481.RNKI_YMD, 
 KK1481.KOJIAK_SBT_CD, 
 KK1481.KOJIAK_NO,
 KK0081.GENE_ADD_DTM,
 KK2111.LAST_UPD_DTM
FROM
 KK_T_KJ_CL_WK KK1481
    INNER JOIN
        KK_T_SVC_KEI KK0081
    ON
        KK1481.SVC_KEI_NO = KK0081.SVC_KEI_NO
        AND KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM =
    (SELECT
        MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX
    FROM
        KK_T_SVC_KEI KK0081_GENE
    WHERE
        KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO
        AND KK0081_GENE.RSV_APLY_YMD <= ?
        AND KK0081_GENE.RSV_APLY_CD = '2'
        AND KK0081_GENE.MK_FLG = '0')
   INNER JOIN
      KK_T_SVKEI_EXC_CTRL KK2111
   ON
      KK2111.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
      KK2111.MK_FLG = '0'
WHERE
 KK1481.KOJIAK_SBT_CD IN ('006' , '007') AND 
 KK1481.RNKI_YMD || KK1481.RNKI_SEQ =
         (SELECT MAX(KK1481_GENE.RNKI_YMD || KK1481_GENE.RNKI_SEQ ) AS KK1481_MAX
          FROM   KK_T_KJ_CL_WK KK1481_GENE 
          WHERE  KK1481_GENE.SVC_KEI_NO = KK1481.SVC_KEI_NO
          AND    KK1481_GENE.SVC_KEI_KAISEN_UCWK_NO = KK1481.SVC_KEI_KAISEN_UCWK_NO
          AND    KK1481_GENE.MK_FLG = '0')
ORDER BY 
 KK1481.SVC_KEI_NO, 
 KK1481.KOJIAK_NO, 
 KK1481.RNKI_YMD

#-

#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_SELECT_003)
#--KKPRC35401
#--サービス解約理由コードが工事起因によるキャンセル（03）の場合
#--仮鍵開け受け付年月日を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
    KU0031.KOJIAK_NO,
    KU0031.KARI_KEY_OPEN_UK_YMD
FROM
    KK_T_KJ_CL_WK KK1481 
INNER JOIN KU_T_KJAK_EOH_TV KU0031 
    ON  KU0031.KOJIAK_NO = KK1481.KOJIAK_NO 
    AND KU0031.MK_FLG = '0'
WHERE
    KK1481.SVC_KEI_NO = ? AND
    KK1481.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_SELECT_004)
#--KKPRC33705
#--撤去解約に伴う工事取消情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
    KK1481.SVC_KEI_NO,
    KK1481.KOJIAK_SBT_CD
FROM
    KK_T_KJ_CL_WK KK1481 
WHERE
    KK1481.KOJIAK_SBT_CD IN ('002','004')
    AND KK1481.SYRZM_FLG = '0'
    AND KK1481.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_SELECT_005)
#--KKPRC40102
#--工事取消ワークから対象工事案件データ抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT
    KK1481.SVC_KEI_NO,
    KK1481.KOJIAK_NO,
    KK1481.SVC_KEI_KAISEN_UCWK_NO,
    KU0011.AD_CHG_NEWESTA_KJAK_NO,
    KU0011.AD_CHG_TEKKYO_KJAK_NO
FROM
    KK_T_KJ_CL_WK KK1481 
INNER JOIN KU_T_KOJIAK KU0011 
    ON  KU0011.KOJIAK_NO = KK1481.KOJIAK_NO 
    AND KU0011.KOJI_UK_OPTNTY_IDO_DIV = '00067' 
    AND KU0011.MK_FLG = '0'
WHERE
    KK1481.KOJIAK_SBT_CD = '003'
    AND KK1481.SYRZM_FLG = '0'
    AND KK1481.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_SELECT_006)
#--KKPRC5204
#--工事取消ワークから対象工事案件データ抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_006

SELECT
	KK1481.SVC_KEI_NO,
    KK1481.KOJIAK_NO,
    KK1481.SVC_KEI_KAISEN_UCWK_NO
FROM
    KK_T_KJ_CL_WK KK1481 
WHERE
    KK1481.KOJIAK_SBT_CD = '014'
    AND KK1481.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_DELETE_001 )
#--工事取消ワーク情報を削除します。
#--KKPRC33701にて使用
#-----------------------------------------------------------

--KEY:KK_DELETE_001 

DELETE FROM KK_T_KJ_CL_WK

#-


#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_SELECT_007)
#--eoテレビ変更の工事案件抽出(日中バッチ)
#-----------------------------------------------------------

--KEY:KK_SELECT_007

SELECT
    KK1481.SVC_KEI_NO,
    KK1481.KOJIAK_NO,
    KK1481.KOJIAK_STP_ADD_YMD,
    KK1481.KOJIAK_STP_CANCEL_PRC_UM,
    KU0091.MSKM_DTL_NO,
    '' AS IDO_RSV_NO,
    '1' AS TABLE_NO,
    '' AS RSV_APLY_YMD,
    '' AS SVC_KEI_UCWK_NO,
    KU0011.KOJI_UK_OPTNTY_IDO_DIV
FROM
    KK_T_KJ_CL_WK KK1481
INNER JOIN
    KU_T_KOJIAK KU0011
ON
    KU0011.KOJIAK_NO = KK1481.KOJIAK_NO
AND
    KU0011.KOJI_UK_OPTNTY_IDO_DIV  = '00018'
AND
    KU0011.MK_FLG = '0'
INNER JOIN
    KU_T_MSKM_DTL_KOJIAK KU0091
ON
    KU0091.KOJIAK_NO = KU0011.KOJIAK_NO 
AND
    KU0091.MK_FLG = '0'
WHERE
    KK1481.RNKI_YMD || KK1481.RNKI_SEQ =
         (SELECT MAX(KK1481_GENE.RNKI_YMD || KK1481_GENE.RNKI_SEQ ) AS KK1481_MAX
              FROM   KK_T_KJ_CL_WK KK1481_GENE 
              WHERE  KK1481_GENE.SVC_KEI_NO = KK1481.SVC_KEI_NO
              AND    KK1481_GENE.SVC_KEI_KAISEN_UCWK_NO = KK1481.SVC_KEI_KAISEN_UCWK_NO
              AND    KK1481_GENE.KOJIAK_SBT_CD IN('001','009','013')
              AND    KK1481_GENE.MK_FLG = '0')
AND
    KK1481.SYRZM_FLG = '0'
UNION
SELECT
    KK1481.SVC_KEI_NO,
    KK1481.KOJIAK_NO,
    KK1481.KOJIAK_STP_ADD_YMD,
    KK1481.KOJIAK_STP_CANCEL_PRC_UM,
    KU0091.MSKM_DTL_NO,
    '' AS IDO_RSV_NO,
    '1' AS TABLE_NO,
    '' AS RSV_APLY_YMD,
    '' AS SVC_KEI_UCWK_NO,
    KU0011.KOJI_UK_OPTNTY_IDO_DIV
FROM
    KK_T_KJ_CL_WK KK1481
INNER JOIN
    KU_T_KOJIAK KU0011
ON
    KU0011.KOJIAK_NO = KK1481.KOJIAK_NO
AND
    KU0011.KOJI_UK_OPTNTY_IDO_DIV  = '00009'
AND
    KU0011.MK_FLG = '0'
INNER JOIN
    KU_T_MSKM_DTL_KOJIAK KU0091
ON
    KU0091.KOJIAK_NO = KU0011.KOJIAK_NO 
AND
    KU0091.MK_FLG = '0'
INNER JOIN
    KK_T_IDO_RSV KK1681
ON
    KK1681.MSKM_DTL_NO = KU0091.MSKM_DTL_NO
AND
    KK1681.SVC_KEI_NO = KK1481.SVC_KEI_NO
AND
    KK1681.IDO_DIV = '00009'
AND
    KK1681.NEW_PCRS_CD IN('A38','A39','A24','A64')
AND
    KK1681.OLD_PCRS_CD IN('A38','A39','A24','A64')
AND
    KK1681.MK_FLG = '0'
WHERE
    KK1481.RNKI_YMD || KK1481.RNKI_SEQ =
         (SELECT MAX(KK1481_GENE.RNKI_YMD || KK1481_GENE.RNKI_SEQ ) AS KK1481_MAX
              FROM   KK_T_KJ_CL_WK KK1481_GENE 
              WHERE  KK1481_GENE.SVC_KEI_NO = KK1481.SVC_KEI_NO
              AND    KK1481_GENE.SVC_KEI_KAISEN_UCWK_NO = KK1481.SVC_KEI_KAISEN_UCWK_NO
              AND    KK1481_GENE.KOJIAK_SBT_CD IN('001','009','013')
              AND    KK1481_GENE.MK_FLG = '0')
AND
    KK1481.SYRZM_FLG = '0'
ORDER BY SVC_KEI_NO,KOJIAK_NO,TABLE_NO

#-

#-----------------------------------------------------------
#--工事取消ワーク(KK_T_KJ_CL_WK-KK_SELECT_008)
#--KKPRC33706
#--処理済フラグが未済みのレコードのPKを取得し、同時に排他ロックを実施する。
#-----------------------------------------------------------

--KEY:KK_SELECT_008

SELECT
    KK1481.SVC_KEI_NO,
    KK1481.SVC_KEI_KAISEN_UCWK_NO,
    KK1481.RNKI_YMD,
    KK1481.RNKI_SEQ
FROM
    KK_T_KJ_CL_WK KK1481
WHERE
    KK1481.SYRZM_FLG = '0'
    AND KK1481.MK_FLG = '0'
FOR UPDATE

#-