#!/bin/bash
##############################################################
# システム名    ：eo顧客基幹システム
#
# サブシステム名：物流管理
#
# シェル名      ：残置の返品機器ツール
#
# 機能          ：残置にした機器の機器提供サービス契約を解約にし、返品機器を作成する。
#
# コマンド形式  ：EO50D0X10J0.sh
#
# 実行環境      ：.../app/shl
#
# 終了ステータス：ゼロ以外異常
#
# 特記事項      ：特になし
#
# 変更履歴      ：変更日      変更者       障害／仕様変更No.
# v54.00.00       2021/07/27  FJ)寺川      ANK-4069-00-00 バッチ・DBサーバーリプレース対応
#
##############################################################

##############################################################
# 環境変数定義
##############################################################

#
#---ツール格納場所
#
G_TOOLSH=.

#
#---環境変数定義Shell呼び出し
#
. EOK010110J0.sh

#
#---ＪＯＢ名
#
JOBNAME=`basename $0 | sed -e "s/\.sh//g"`

#
#---サービス（業務プロセス）
#
SVC=$JOBNAME


#
#---プロセス番号
#
PID=$$

#
#---運用日
#
OPD=$1
#---運用日の指定がない場合、運用日付を取得する
if [ "${OPD}" = "" ]; then
   . EOK010310J0.sh "ODATE"
   OPD="${RSLT_OPEDATE}"
fi


ORA_USER=${ORACLEUSER}
ORA_PASS=${ORACLEPASS}
ORA_SID=${SID_S}

#
#---業務で必要な変数定義
#--- 現在のシステム日時を取得
TODAY=`date +%Y%m%d%H%M%S`

##############################################################
# 業務処理
##############################################################
sh ${G_TOOLSH}/EOK010210J0.sh CS00025I $JOBNAME

sh ${G_TOOLSH}/EOK010210J0.sh CS00027I $JOBNAME

#sqlplus -s $ORA_USER/$ORA_PASS@$ORA_SID << EOF
sqlplus -s $ORA_USER/$ORA_PASS@$ORA_SID <<EOF
SET SERVEROUT ON
VARIABLE code NUMBER
DECLARE

    PROC_EXCEPTION      EXCEPTION;

    ERROR_CODE          NUMBER(5);
    ERROR_MESSAGE       VARCHAR2(2048);
    REC_CNT             NUMBER(10) := 0;
    TARGET_CNT          NUMBER(10) := 0;
    DK301_INS_CNT       NUMBER(10) := 0;
    DK301_UPD_CNT       NUMBER(10) := 0;
    KK341_INS_CNT       NUMBER(10) := 0;

    W_HMPIN_KIKI_NO     DK_T_HMPIN_KIKI.HMPIN_KIKI_NO%TYPE;
    W_DATA_COUNT        NUMBER(10) := 0;

BEGIN
    :code := '0';

    FOR val_rec IN (

                     SELECT DISTINCT 
                      KK0341.TAKNKIKI_MODEL_CD,KK0341
                     .KIKI_SEIZO_NO
                     ,KK0341.KKTK_SVC_KEI_STAT
                     ,KK0341.KKTK_SVC_KEI_NO
                     ,KK0341.KIKI_CHG_NO
                     ,KK0341.SVC_KEI_KAISEN_UCWK_NO
                     ,KK0341.KKTK_SBT_CD
                     ,KK2101.ADCHG_NO
                     ,KK0341.SVC_STA_YMD
                     ,CASE KK0341.IDO_DIV
                      WHEN '00009' THEN '5'
                      WHEN '00018' THEN '5'
                      WHEN '00019' THEN '5'
                      WHEN '00026' THEN '5'
                      WHEN '00028' THEN '5'
                      WHEN '00049' THEN '5'
                      WHEN '00027' THEN '4'
                      ELSE '2'
                      END AS HMPIN_DIV
                     ,KU0011.KOJIAK_NO
                     ,NVL(KU0011.KOJIAK_JSSI_YMD,'20991231') AS KOJIAK_JSSI_YMD
                     ,(
                          SELECT SUBSTR(MIN(SVC_CD || SVC_KEI_NO),3,12) AS SVC_KEI_NO_MIN
                          FROM KU_T_SVKEI_KOJIAK
                          WHERE KOJIAK_NO = KU0011.KOJIAK_NO
                      ) AS SVC_KEI_NO
                     ,CK0011.CUST_NM
                     ,CK0011.KEISHA_AD_CD
                     ,CK0011.KEISHA_PCD
                     ,CK0011.KEISHA_STATE_NM
                     ,CK0011.KEISHA_CITY_NM
                     ,CK0011.KEISHA_OAZTSU_NM
                     ,CK0011.KEISHA_AZCHO_NM
                     ,CK0011.KEISHA_BNCHIGO
                     ,CK0011.KEISHA_ADRTTM
                     ,CK0011.KEISHA_ADRRM
                     ,CK0011.KEISHA_TELNO
                    FROM KK_T_ADCHM_RYKK_TPPV KK2021
                    LEFT JOIN KK_T_ADCHG_DTL KK2101 ON KK2101.ADCHG_DTL_SBT_CD = '03' AND KK2101.CHG_TG_KEI_NO = KK2021.KKTK_SVC_KEI_NO AND KK2101.MK_FLG = '0'
                    LEFT JOIN KK_T_ADCHG_DTL KK2101_1 ON KK2101_1.ADCHG_DTL_SBT_CD = '06' AND KK2101_1.ADCHG_NO = KK2101.ADCHG_NO AND KK2101.MK_FLG = '0'
                    LEFT JOIN KK_T_ADCHG KK2091 ON KK2091.ADCHG_NO = KK2101.ADCHG_NO AND KK2091.MK_FLG = '0'
                    LEFT JOIN KK_T_MSKM_DTL T5 ON KK2091.MSKM_NO = T5.MSKM_NO AND T5.MK_FLG = '0'
                    LEFT JOIN KK_T_ADCHM_TPPV KK2011 ON T5.MSKMSHO_NO = KK2011.MSKMSHO_NO AND KK2011.ADCHM_TPPV_NO = KK2021.ADCHM_TPPV_NO AND KK2011.MK_FLG = '0'
                    LEFT JOIN KU_T_KOJIAK KU0011 ON KU0011.KOJIAK_NO = KK2101_1.CHBF_SKBT_NO AND KU0011.MK_FLG = '0'
                    INNER JOIN KK_T_KKTK_SVC_KEI KK0341
                    ON KK0341.KKTK_SVC_KEI_NO = KK2021.KKTK_SVC_KEI_NO
                    AND KK0341.KIKI_CHG_NO = KK2101.CHBF_SKBT_NO
                    AND (KK0341.KKTK_SVC_KEI_NO,KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = (
                         SELECT B1.KKTK_SVC_KEI_NO,MAX(B1.RSV_APLY_YMD || B1.GENE_ADD_DTM)
                         FROM KK_T_KKTK_SVC_KEI B1
                         WHERE B1.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
                         AND B1.KIKI_CHG_NO = KK0341.KIKI_CHG_NO
                         AND B1.MK_FLG = '0'
                         GROUP BY B1.KKTK_SVC_KEI_NO
                    )
                    INNER JOIN CK_T_CUST CK0011
                    ON CK0011.SYSID = KK0341.SYSID
                    AND (CK0011.SYSID,CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM) = (
                         SELECT CK0011_GENE.SYSID,MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM)
                         FROM   CK_T_CUST CK0011_GENE
                         WHERE  CK0011_GENE.SYSID = CK0011.SYSID
                         AND    CK0011_GENE.MK_FLG = '0'
                         GROUP BY CK0011_GENE.SYSID )
                    WHERE KK2021.TKKIKI_TEKKYO_SBT_CD = '2' 
                    AND KK0341.KKTK_SBT_CD = '02'
                    AND NOT EXISTS (
                        SELECT 1 
                        FROM DK_T_HMPIN_KIKI DK0301
                        WHERE DK0301.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
                        AND   DK0301.KIKI_CHG_NO = KK0341.KIKI_CHG_NO
                        AND   DK0301.MK_FLG = '0'
                    )
                    AND EXISTS (
                        SELECT 1 FROM KK_T_KAISEN_TG_SVKEI C
                        WHERE  C.SVC_KEI_KAISEN_UCWK_NO = KK0341.SVC_KEI_KAISEN_UCWK_NO
                        AND C.KAISEN_UCWK_USE_ENDYMD <= '${OPD}'
                        AND C.MK_FLG = '0'
                    )
                    ORDER BY KK0341.TAKNKIKI_MODEL_CD,KK0341.KIKI_SEIZO_NO

                    )

    LOOP
        REC_CNT := REC_CNT + 1;

        -- 撤去工事案件実績年月日が運用日以上なら処理対象外
        IF val_rec.KOJIAK_JSSI_YMD > '${OPD}' THEN
            CONTINUE;
        END IF;

        TARGET_CNT := TARGET_CNT + 1;

        -- 返品機器の更新
        BEGIN
            -- 型番/製番でデータを検索
            --   機器提供サービス契約番号/機器変更番号/読み込み親サービス契約番号がNullのデータ
            SELECT DK0301.HMPIN_KIKI_NO
            INTO   W_HMPIN_KIKI_NO
            FROM   DK_T_HMPIN_KIKI DK0301
            WHERE  DK0301.HMPIN_KIKI_STAT = '002'
            AND    DK0301.TAKNKIKI_MODEL_CD = val_rec.TAKNKIKI_MODEL_CD
            AND    DK0301.KIKI_SEIZO_NO = val_rec.KIKI_SEIZO_NO
            AND    (DK0301.KKTK_SVC_KEI_NO IS NULL OR DK0301.KIKI_CHG_NO IS NULL OR DK0301.LOAD_OYA_SVKEI_NO IS NULL)
            AND    DK0301.MK_FLG = '0';

            -- データ更新
            UPDATE DK_T_HMPIN_KIKI DK0301
            SET
                HMPIN_UK_YMD = val_rec.KOJIAK_JSSI_YMD,
                KKTK_SVC_KEI_NO = val_rec.KKTK_SVC_KEI_NO,
                KIKI_CHG_NO = val_rec.KIKI_CHG_NO,
                LOAD_OYA_SVKEI_NO = val_rec.SVC_KEI_NO,
                TK_SBT_CD = val_rec.KKTK_SBT_CD,
                HMPIN_DIV = val_rec.HMPIN_DIV,
                UPD_DTM = TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3'),
                UPD_OPEACNT = '962305',
                UPD_UNYO_YMD = '${OPD}',
                UPD_TRN_ID = '${HOSTNAME}_${JOBNAME}_${PID}'
            WHERE DK0301.HMPIN_KIKI_NO = W_HMPIN_KIKI_NO;
            DK301_UPD_CNT := DK301_UPD_CNT + 1;

        EXCEPTION
                -- データがない場合は返品機器を作成する。
                WHEN NO_DATA_FOUND THEN
                    INSERT INTO DK_T_HMPIN_KIKI (
                    HMPIN_KIKI_NO,
                    HMPIN_KIKI_STAT,
                    HMPIN_UK_YMD,
                    HMPIN_DIV,
                    KKTK_SVC_KEI_NO,
                    KIKI_CHG_NO,
                    TAKNKIKI_MODEL_CD,
                    KIKI_SEIZO_NO,
                    HMPINSHA_NM,
                    HMPINSHA_AD_CD,
                    HMPINSHA_PCD,
                    HMPINSHA_STATE_NM,
                    HMPINSHA_CITY_NM,
                    HMPINSHA_OAZTSU_NM,
                    HMPINSHA_AZCHO_NM,
                    HMPINSHA_BNCHIGO,
                    HMPINSHA_ADRTTM,
                    HMPINSHA_ADRRM,
                    HMPINSHA_TELNO,
                    LOAD_OYA_SVKEI_NO,
                    ADD_OPTNTY_KEI_IDO_DIV,
                    TK_SBT_CD,
                    ADD_DTM,
                    ADD_OPEACNT,
                    UPD_DTM,
                    UPD_OPEACNT,
                    MK_FLG,
                    ADD_UNYO_YMD,
                    ADD_TRN_ID,
                    UPD_UNYO_YMD,
                    UPD_TRN_ID
                    ) SELECT
                        'R' || TO_CHAR(SYSDATE,'YY') || LPAD(SEQ_HMPIN_KIKI_NO.NEXTVAL,7,'0'),
                        '001',
                        val_rec.KOJIAK_JSSI_YMD,
                        '2',
                        KK0341.KKTK_SVC_KEI_NO,
                        KK0341.KIKI_CHG_NO,
                        KK0341.TAKNKIKI_MODEL_CD,
                        KK0341.KIKI_SEIZO_NO,
                        val_rec.CUST_NM,
                        val_rec.KEISHA_AD_CD,
                        val_rec.KEISHA_PCD,
                        val_rec.KEISHA_STATE_NM,
                        val_rec.KEISHA_CITY_NM,
                        val_rec.KEISHA_OAZTSU_NM,
                        val_rec.KEISHA_AZCHO_NM,
                        val_rec.KEISHA_BNCHIGO,
                        val_rec.KEISHA_ADRTTM,
                        val_rec.KEISHA_ADRRM,
                        val_rec.KEISHA_TELNO,
                        val_rec.SVC_KEI_NO,
                        '00005',
                        KK0341.KKTK_SBT_CD,
                        TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3'),
                        '962305',
                        TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3'),
                        '962305',
                        '0',
                        '${OPD}',
                        '${HOSTNAME}_${JOBNAME}_${PID}',
                        '${OPD}',
                        '${HOSTNAME}_${JOBNAME}_${PID}'
                    FROM KK_T_KKTK_SVC_KEI KK0341
                    WHERE KK0341.KKTK_SVC_KEI_NO = val_rec.KKTK_SVC_KEI_NO
                    AND KK0341.KIKI_CHG_NO = val_rec.KIKI_CHG_NO
                    AND (KK0341.KKTK_SVC_KEI_NO,KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = 
                           (SELECT KK0341_1.KKTK_SVC_KEI_NO,MAX(KK0341_1.RSV_APLY_YMD || KK0341_1.GENE_ADD_DTM)
                            FROM KK_T_KKTK_SVC_KEI KK0341_1
                            WHERE KK0341_1.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
                              AND KK0341_1.KIKI_CHG_NO = KK0341.KIKI_CHG_NO
                              AND KK0341_1.MK_FLG = '0'
                            GROUP BY KK0341_1.KKTK_SVC_KEI_NO)
                    AND NOT EXISTS
                        (SELECT 1
                         FROM DK_T_HMPIN_KIKI DK0301_1
                         WHERE DK0301_1.HMPIN_KIKI_STAT = '001'
                         AND DK0301_1.MK_FLG = '0'
                         AND DK0301_1.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
                         AND DK0301_1.KIKI_CHG_NO = KK0341.KIKI_CHG_NO);
                    DK301_INS_CNT := DK301_INS_CNT + 1;

                WHEN OTHERS THEN
                        ERROR_CODE := sqlcode;
                        ERROR_MESSAGE := sqlerrm;
                        dbms_output.put_line('エラーコード:' || ERROR_CODE);
                        dbms_output.put_line('エラーメッセージ:' || ERROR_MESSAGE);
                        dbms_output.put_line('型番:' || val_rec.TAKNKIKI_MODEL_CD);
                        dbms_output.put_line('製造番号:' || val_rec.KIKI_SEIZO_NO);
                        :code := 127;
        END;

        -- 機器提供サービス契約に解約レコードを作成する。
        BEGIN
            INSERT INTO KK_T_KKTK_SVC_KEI
            (KKTK_SVC_KEI_NO, GENE_ADD_DTM, KKTK_SVC_KEI_STAT, KKTK_SVC_CD, 
                PCRS_CD, PPLAN_CD, TK_HOSHIKI_KEI_NO, KKTK_SBT_CD, HAMBAI_SBT_CD, 
                SVC_USE_STA_KIBO_YMD, RSV_TSTA_KIBO_YMD, KIBO_MAKER_CD, 
                KIKI_SHITEI_SBT_CD, TAKNKIKI_SBT_CD, TAKNKIKI_MODEL_CD, KIKI_SEIZO_NO, 
                HUZOKUHIN_SBT_CD, HUZOKUHIN_MODEL_CD, TAKNKIKI_SETHIN_MODEL_CD, 
                KIKI_CHG_NO, KIKI_CHG_RSN_CD, TSUSHIN_KIKI_SET_CD, HDD_CAPA_CD, 
                KIKI_STC_SAKI_PLACE_NO, OYA_KEI_SKBT_CD, SVC_KEI_NO, SVC_KEI_UCWK_NO, 
                SVC_KEI_KAISEN_UCWK_NO, OP_SVC_KEI_NO, SYSID, MSKM_DTL_NO, 
                LINK_STB_FLG, KIKI_HKAT_SHITEI_SOKO_CD, KIKI_HKAT_SHITEI_SKDN_CD, 
                KIKI_STI_JI_KRIPLACE_SKCD, KIKI_STI_JI_KOCOMP_CD, 
                KIKI_STI_JI_KOCOMP_SLF_CD, KIKI_STI_JI_YTKSKOF_CD, 
                KIKI_STI_JI_YTKSKOF_SLF_CD, KKTK_SVC_KEI_HKHASYMD, KIKI_SORYO_UM, 
                KIKI_SORYO_SAKSEI_YMD, KIKI_SOHUS_NM, KIKI_SOHUS_KANA, 
                KIKI_SOHUS_AD_CD, KIKI_SOHUS_PCD, KIKI_SOHUS_STATE_NM, 
                KIKI_SOHUS_CITY_NM, KIKI_SOHUS_OAZTSU_NM, KIKI_SOHUS_AZCHO_NM, 
                KIKI_SOHUS_BNCHIGO, KIKI_SOHUS_ADRTTM, KIKI_SOHUS_ADRRM, 
                KIKI_SHS_AD_MAN_INPUT_FLG, KIKI_SOHUS_TELNO, MANSION_BUKKEN_NO, 
                KIKI_SOHUS_KSH_AD_SAI_FLG, KIKI_SHS_KBT_SHITEI_FLG, KIKI_SHS_HSK_CD_1, 
                KIKI_SHS_HSK_CD_2, KIKI_SHS_HSK_MEMO, KIKI_STC_SAKI_NM, 
                KIKI_STC_SAKI_KANA, KIKI_STC_SAKI_AD_CD, KIKI_STC_SAKI_PCD, 
                KIKI_STC_SAKI_STATE_NM, KIKI_STC_SAKI_CITY_NM, KIKI_STC_SAKI_OAZTSU_NM, 
                KIKI_STC_SAKI_AZCHO_NM, KIKI_STC_SAKI_BNCHIGO, KIKI_STC_SAKI_ADRTTM, 
                KIKI_STC_SAKI_ADRRM, KIKI_STC_SK_KSH_AD_SAI_FLG, KIKI_STC_SK_TELNO, 
                KIKI_STS_KKK_SEIRI_CHU_FLG, AD_MI_FIX_FLG, AUTO_ADD_CD, 
                AD_MI_FIX_RLS_YMD, CHRG_STA_YMD_HOSEI_UM, KIKI_STS_HSK_CD_1, 
                KIKI_STS_HSK_CD_2, KIKI_STS_HSK_MEMO, KKTK_SVC_KEI_KZKWRK_REQYMD, 
                SHOSA_YMD, SHOSA_CL_YMD, HAISO_DIV, HAISO_KIGEN_YMD, 
                HAISO_ARIV_SHITEI_YMD, FTRIAL_KANYU_YMD, FTRIAL_PRD_ENDYMD, 
                HONKANYU_YMD, HONKANYU_IKO_KIGEN_YMD, KEI_CNC_YMD, 
                JCCC_KANYU_BUNSHO_YMD, HOSHO_CD, KKTK_SVKEI_HOKI, HOSHO_STAYMD, 
                HOSHO_END_YMD, RSV_APLY_YMD, RSV_CL_YMD, RSV_APLY_CD, KIKI_CHG_YMD, 
                PLAN_STAYMD, PLAN_ENDYMD, PLAN_CHRG_STAYMD, PLAN_CHRG_ENDYMD, 
                PLAN_END_SBT_CD, SVC_CANCEL_YMD, SVC_CANCEL_RSN_CD, SVC_STA_KISAN_YMD, 
                SVC_STA_YMD, SVC_CHRG_STAYMD, SVC_STP_YMD, SVCTK_CHU_USE_FAIL_SBT_CD, 
                SVC_STP_RSN_CD, SVC_STP_RLS_YMD, SVC_STP_RLS_RSN_CD, PAUSE_STP_CD, 
                SVC_PAUSE_YMD, SVC_PAUSE_RSN_CD, SVC_PAUSE_RSN_MEMO, SVC_PAUSE_RLS_YMD, 
                SVC_PAUSE_RLS_RSN_CD, SVC_PAUSE_RLS_RSN_MEMO, SVC_ENDYMD, 
                SVC_CHRG_ENDYMD, SVC_DSL_YMD, SVC_DLRE_CD, SVC_DLRE_MEMO, ZANCHI_FLG, 
                SVC_DSL_TTDKI_FIN_FLG, KAIHK_YMD, SVC_CANCEL_CL_YMD, SVC_DSL_CL_YMD, 
                SKEKKA_SEND_CD, SVC_PAUSE_CHRG_STA_YMD, PNLTY_HASSEI_CD, 
                KIKI_NINSHO_ID, KIKI_NINSHO_ID_PWD, IDO_DIV, KKST_JSEKI_UK_YMD, 
                EO_TV_KKST_SNN_STAT_CD, KKST_SNN_YMD, TAKNKIKI_IDO_CD, 
                CAS_CARD_USE_KYODAK_YMD, KIKI_HUKA_INFO_CD, SHOSA_DSL_FIN_CD, 
                ROUTER_DSL_RSV_TRN_STAT_CD, ROUTER_DSL_RSV_TRN_RSLT_CD, HAISO_WAY_CD, 
                KIKI_ITENS_MV_JSSIS_SKCD, KAISHU_KIKI_UM, HAISO_REQ_SHITEI_YMD, 
                SHKA_FIN_JI_SYRZM_FLG, SVC_STA_JI_HIS_JOKYO_SKCD, ADD_DTM, ADD_OPEACNT, 
                UPD_DTM, UPD_OPEACNT, DEL_DTM, DEL_OPEACNT, MK_FLG, ADD_UNYO_YMD, 
                ADD_TRN_ID, UPD_UNYO_YMD, UPD_TRN_ID, DEL_UNYO_YMD, DEL_TRN_ID)
            SELECT KKTK_SVC_KEI_NO, TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3'), '910', KKTK_SVC_CD, 
                PCRS_CD, PPLAN_CD, TK_HOSHIKI_KEI_NO, KKTK_SBT_CD, HAMBAI_SBT_CD, 
                SVC_USE_STA_KIBO_YMD, RSV_TSTA_KIBO_YMD, KIBO_MAKER_CD, 
                KIKI_SHITEI_SBT_CD, TAKNKIKI_SBT_CD, TAKNKIKI_MODEL_CD, KIKI_SEIZO_NO, 
                HUZOKUHIN_SBT_CD, HUZOKUHIN_MODEL_CD, TAKNKIKI_SETHIN_MODEL_CD, 
                KIKI_CHG_NO, KIKI_CHG_RSN_CD, TSUSHIN_KIKI_SET_CD, HDD_CAPA_CD, 
                KIKI_STC_SAKI_PLACE_NO, OYA_KEI_SKBT_CD, SVC_KEI_NO, SVC_KEI_UCWK_NO, 
                SVC_KEI_KAISEN_UCWK_NO, OP_SVC_KEI_NO, SYSID, MSKM_DTL_NO, 
                LINK_STB_FLG, KIKI_HKAT_SHITEI_SOKO_CD, KIKI_HKAT_SHITEI_SKDN_CD, 
                KIKI_STI_JI_KRIPLACE_SKCD, KIKI_STI_JI_KOCOMP_CD, 
                KIKI_STI_JI_KOCOMP_SLF_CD, KIKI_STI_JI_YTKSKOF_CD, 
                KIKI_STI_JI_YTKSKOF_SLF_CD, KKTK_SVC_KEI_HKHASYMD, KIKI_SORYO_UM, 
                KIKI_SORYO_SAKSEI_YMD, KIKI_SOHUS_NM, KIKI_SOHUS_KANA, 
                KIKI_SOHUS_AD_CD, KIKI_SOHUS_PCD, KIKI_SOHUS_STATE_NM, 
                KIKI_SOHUS_CITY_NM, KIKI_SOHUS_OAZTSU_NM, KIKI_SOHUS_AZCHO_NM, 
                KIKI_SOHUS_BNCHIGO, KIKI_SOHUS_ADRTTM, KIKI_SOHUS_ADRRM, 
                KIKI_SHS_AD_MAN_INPUT_FLG, KIKI_SOHUS_TELNO, MANSION_BUKKEN_NO, 
                KIKI_SOHUS_KSH_AD_SAI_FLG, KIKI_SHS_KBT_SHITEI_FLG, KIKI_SHS_HSK_CD_1, 
                KIKI_SHS_HSK_CD_2, KIKI_SHS_HSK_MEMO, KIKI_STC_SAKI_NM, 
                KIKI_STC_SAKI_KANA, KIKI_STC_SAKI_AD_CD, KIKI_STC_SAKI_PCD, 
                KIKI_STC_SAKI_STATE_NM, KIKI_STC_SAKI_CITY_NM, KIKI_STC_SAKI_OAZTSU_NM, 
                KIKI_STC_SAKI_AZCHO_NM, KIKI_STC_SAKI_BNCHIGO, KIKI_STC_SAKI_ADRTTM, 
                KIKI_STC_SAKI_ADRRM, KIKI_STC_SK_KSH_AD_SAI_FLG, KIKI_STC_SK_TELNO, 
                KIKI_STS_KKK_SEIRI_CHU_FLG, AD_MI_FIX_FLG, AUTO_ADD_CD, 
                AD_MI_FIX_RLS_YMD, CHRG_STA_YMD_HOSEI_UM, KIKI_STS_HSK_CD_1, 
                KIKI_STS_HSK_CD_2, KIKI_STS_HSK_MEMO, KKTK_SVC_KEI_KZKWRK_REQYMD, 
                SHOSA_YMD, SHOSA_CL_YMD, HAISO_DIV, HAISO_KIGEN_YMD, 
                HAISO_ARIV_SHITEI_YMD, FTRIAL_KANYU_YMD, FTRIAL_PRD_ENDYMD, 
                HONKANYU_YMD, HONKANYU_IKO_KIGEN_YMD, KEI_CNC_YMD, 
                JCCC_KANYU_BUNSHO_YMD, HOSHO_CD, KKTK_SVKEI_HOKI, HOSHO_STAYMD, 
                HOSHO_END_YMD, RSV_APLY_YMD, RSV_CL_YMD, RSV_APLY_CD, KIKI_CHG_YMD, 
                             -- プラン終了日                            プラン課金終了日
                PLAN_STAYMD, val_rec.KOJIAK_JSSI_YMD, PLAN_CHRG_STAYMD, TO_CHAR(TO_DATE(val_rec.KOJIAK_JSSI_YMD)-1,'YYYYMMDD'), 
    
                PLAN_END_SBT_CD, SVC_CANCEL_YMD, SVC_CANCEL_RSN_CD, SVC_STA_KISAN_YMD, 
                SVC_STA_YMD, SVC_CHRG_STAYMD, SVC_STP_YMD, SVCTK_CHU_USE_FAIL_SBT_CD, 
                SVC_STP_RSN_CD, SVC_STP_RLS_YMD, SVC_STP_RLS_RSN_CD, PAUSE_STP_CD, 
                SVC_PAUSE_YMD, SVC_PAUSE_RSN_CD, SVC_PAUSE_RSN_MEMO, SVC_PAUSE_RLS_YMD, 
                                                               -- サービス終了日
                SVC_PAUSE_RLS_RSN_CD, SVC_PAUSE_RLS_RSN_MEMO, val_rec.KOJIAK_JSSI_YMD, 
    
                -- サービス課金終了日                                サービス解約年月日
                TO_CHAR(TO_DATE(val_rec.KOJIAK_JSSI_YMD)-1,'YYYYMMDD'), val_rec.KOJIAK_JSSI_YMD, '03', SVC_DLRE_MEMO, ZANCHI_FLG, 
    
                SVC_DSL_TTDKI_FIN_FLG, KAIHK_YMD, SVC_CANCEL_CL_YMD, SVC_DSL_CL_YMD, 
                SKEKKA_SEND_CD, SVC_PAUSE_CHRG_STA_YMD, PNLTY_HASSEI_CD, 
                KIKI_NINSHO_ID, KIKI_NINSHO_ID_PWD, '00005', KKST_JSEKI_UK_YMD, 
                EO_TV_KKST_SNN_STAT_CD, KKST_SNN_YMD, TAKNKIKI_IDO_CD, 
                CAS_CARD_USE_KYODAK_YMD, KIKI_HUKA_INFO_CD, SHOSA_DSL_FIN_CD, 
                ROUTER_DSL_RSV_TRN_STAT_CD, ROUTER_DSL_RSV_TRN_RSLT_CD, HAISO_WAY_CD, 
                KIKI_ITENS_MV_JSSIS_SKCD, KAISHU_KIKI_UM, HAISO_REQ_SHITEI_YMD, 
                SHKA_FIN_JI_SYRZM_FLG, SVC_STA_JI_HIS_JOKYO_SKCD, TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3'),'962305', 
                TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3'), '962305', NULL, NULL, '0', '${OPD}', 
                '${HOSTNAME}_${JOBNAME}_${PID}', '${OPD}', '${HOSTNAME}_${JOBNAME}_${PID}', NULL, NULL
            FROM KK_T_KKTK_SVC_KEI KK0341
            WHERE KK0341.KKTK_SVC_KEI_NO = val_rec.KKTK_SVC_KEI_NO
            AND KK0341.KIKI_CHG_NO = val_rec.KIKI_CHG_NO
            AND (KK0341.KKTK_SVC_KEI_NO,KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) = 
                   (SELECT KK0341_1.KKTK_SVC_KEI_NO,MAX(KK0341_1.RSV_APLY_YMD || KK0341_1.GENE_ADD_DTM)
                    FROM KK_T_KKTK_SVC_KEI KK0341_1
                    WHERE KK0341_1.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
                      AND KK0341_1.KIKI_CHG_NO = KK0341.KIKI_CHG_NO
                      AND KK0341_1.MK_FLG = '0'
                    GROUP BY KK0341_1.KKTK_SVC_KEI_NO);
             KK341_INS_CNT := KK341_INS_CNT + 1;
    
        EXCEPTION
                    WHEN OTHERS THEN
                        ERROR_CODE := sqlcode;
                        ERROR_MESSAGE := sqlerrm;
                        dbms_output.put_line('エラーコード:' || ERROR_CODE);
                        dbms_output.put_line('エラーメッセージ:' || ERROR_MESSAGE);
                        dbms_output.put_line('型番:' || val_rec.TAKNKIKI_MODEL_CD);
                        dbms_output.put_line('製造番号:' || val_rec.KIKI_SEIZO_NO);
        END;

    END LOOP;

    dbms_output.put_line('■■■処理結果■■■');
    dbms_output.put_line('処理件数:' || REC_CNT);
    dbms_output.put_line('対象件数:' || TARGET_CNT);
    dbms_output.put_line('返品機器登録数:' || DK301_INS_CNT);
    dbms_output.put_line('返品機器更新数:' || DK301_UPD_CNT);
    dbms_output.put_line('機器提供サービス契約登録数:' || KK341_INS_CNT);

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ERROR_CODE := sqlcode;
        ERROR_MESSAGE := sqlerrm;
        dbms_output.put_line('エラーコード:' || ERROR_CODE);
        dbms_output.put_line('エラーメッセージ:' || ERROR_MESSAGE);
        :code := 127;
        ROLLBACK;
END;
/
exit :code
EOF

rc=$?

if [ $rc -ne 0 ]
then
    sh ${G_TOOLSH}/EOK010210J0.sh CS00002E $JOBNAME    ---残置の返品機器ツール。処理を終了します。
    sh ${G_TOOLSH}/EOK010210J0.sh CS00028I $JOBNAME
    sh ${G_TOOLSH}/EOK010210J0.sh CS00026I $JOBNAME
    exit 127
fi


#
#--- ジョブの終了メッセージを出力する
#
sh ${G_TOOLSH}/EOK010210J0.sh CS00028I $JOBNAME
sh ${G_TOOLSH}/EOK010210J0.sh CS00026I $JOBNAME


exit $rc