#-----------------------------------------------------------
#--出荷指図（基本情報）(DK_T_SHKA_KIHON_INFO-DK_SELECT_001)
#--出荷指図（基本情報）テーブルより配送指示年月日が運用日付と同一且つ、出荷指図一時（基本情報）テーブルの配送番号と同一の情報を出荷ロット番号、サービス契約番号の昇順で取得する。
#-----------------------------------------------------------

--KEY:DK_SELECT_001

SELECT 
    T1.SHUKKA_SBT_CD, 
    T1.HAISO_SJI_YMD, 
    T1.SHUKKA_LOT_NO, 
    T1.PRC_GRP_CD, 
    T1.HISOS_PCD, 
    T1.HISOS_STATE_NM, 
    T1.HISOS_CITY_NM, 
    T1.HISOS_OAZTSU_NM, 
    T1.HISOS_AZCHO_NM, 
    T1.HISOS_BNCHIGO, 
    T1.HISOS_ADRTTM, 
    T1.HISOS_ADRRM, 
    T1.HISOS_NM, 
    T1.HISOS_TELNO, 
    T1.PCRS_CD, 
    T1.PPLAN_CD, 
    T1.KEISHA_NM, 
    T1.SYSID, 
    T1.SVC_KEI_NO, 
    T1.ID_SOKHOSHO_OUTPUT_UM_FLG, 
    T1.DATA_CNT, 
    T1.KOJI_COMP_CD, 
    T1.KOJI_RSV_YMD, 
    T1.KOJIAK_NO, 
    T1.DOJI_KOJIAK_NO, 
    T1.DDT_NO, 
    T1.OPMS_MSKM_FORM, 
    T1.IP_SBT, 
    T1.TV_SBT, 
    T1.AD_MI_FIX_FLG, 
    T1.STB_NON_MENU_FLG, 
    T1.HAISO_ARIV_SHITEI_TIME_CD,
    T1.SAIBAN_STA_TCASE_NO,
    KK0891.TK_HOSHIKI_PATTERN_CD
FROM DK_T_SHKA_KIHON_INFO T1
LEFT JOIN KK_T_SVC_KEI KK0081
    ON KK0081.SVC_KEI_NO = T1.SVC_KEI_NO
    AND KK0081.MK_FLG = '0'
LEFT JOIN KK_T_TK_HOSHIKI_KEI KK0891
    ON KK0891.TK_HOSHIKI_KEI_NO = KK0081.TK_HOSHIKI_KEI_NO
    AND KK0891.MK_FLG = '0'
WHERE T1.HAISO_SJI_YMD = ?
AND   EXISTS(SELECT 1
             FROM   DK_T_SHKA_KIHON_TMP T2
             WHERE  T1.HAISO_NO = T2.HAISO_NO
             AND    T2.MK_FLG = '0')
AND KK0081.GENE_ADD_DTM = 
    (SELECT MAX(KK0081_GENE.GENE_ADD_DTM) 
     FROM KK_T_SVC_KEI KK0081_GENE 
     WHERE KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO
     AND KK0081_GENE.MK_FLG = '0')
    AND T1.MK_FLG = '0' 
ORDER BY T1.SHUKKA_LOT_NO, T1.SVC_KEI_NO

#-

#-----------------------------------------------------------
#--出荷指図（基本情報）(DK_T_SHKA_KIHON_INFO-DK_SELECT_002)
#--出荷指図の基本情報と端末情報の通番の最大値を取得
#-----------------------------------------------------------

--KEY:DK_SELECT_002

SELECT DK0401.SHUKKA_LOT_NO,
       MAX(DK0401.SVC_KEI_NO) AS SVC_KEI_NO,
       DK0401.HAISO_NO,
       MAX(DK0401.HISOS_PCD) AS HISOS_PCD,
       MAX(DK0401.HISOS_STATE_NM) AS HISOS_STATE_NM,
       MAX(DK0401.HISOS_CITY_NM) AS HISOS_CITY_NM,
       MAX(DK0401.HISOS_OAZTSU_NM) AS HISOS_OAZTSU_NM,
       MAX(DK0401.HISOS_AZCHO_NM) AS HISOS_AZCHO_NM,
       MAX(DK0401.HISOS_BNCHIGO) AS HISOS_BNCHIGO,
       MAX(DK0401.HISOS_ADRTTM) AS HISOS_ADRTTM,
       MAX(DK0401.HISOS_ADRRM) AS HISOS_ADRRM,
       MAX(DK0401.HISOS_NM) AS HISOS_NM,
       MAX(DK0401.HISOS_TELNO) AS HISOS_TELNO,
       MAX(DK0401.KEISHA_NM) AS KEISHA_NM,
       MAX(DK0401.SYSID) AS SYSID,
       MAX(DK0401.KOJI_COMP_CD) AS KOJI_COMP_CD,
       MAX(DK0401.KOJI_RSV_YMD) AS KOJI_RSV_YMD,
       MAX(DK0401.KOJIAK_NO) AS KOJIAK_NO,
       MAX(DK0401.DOJI_KOJIAK_NO) AS DOJI_KOJIAK_NO,
       MAX(DK0401.DDT_NO) AS DDT_NO,
       MAX(DK0401.OPMS_MSKM_FORM) AS OPMS_MSKM_FORM,
       MAX(DK0401.TV_SBT) AS TV_SBT,
       MAX(DK0401.AD_MI_FIX_FLG) AS AD_MI_FIX_FLG,
       MAX(DK0401.STB_NON_MENU_FLG) AS STB_NON_MENU_FLG,
       MAX(DK0401.SHUKKA_SBT_CD) AS SHUKKA_SBT_CD,
       MAX(DK0401.HAISO_ARIV_SHITEI_TIME_CD) AS HAISO_ARIV_SHITEI_TIME_CD,
       MAX(DK0401.SAIBAN_STA_TCASE_NO) AS SAIBAN_STA_TCASE_NO,
       DK0011.HAISO_MOTO_SOKO_CD AS SOKO_CD,
       DK0411.SHELF_CD,
       DK0011.HAISO_SJI_YMD,
       MAX(DK0411.SHUKKA_LOT_KIKI_SEQ) AS SHUKKA_LOT_KIKI_SEQ
FROM   (
         SELECT DK0401.SHUKKA_LOT_NO AS SHUKKA_LOT_NO,
                MAX(DK0401.SVC_KEI_NO) AS SVC_KEI_NO,
                DK0401.HAISO_NO AS HAISO_NO,
                MAX(DK0401.HISOS_PCD) AS HISOS_PCD,
                MAX(DK0401.HISOS_STATE_NM) AS HISOS_STATE_NM,
                MAX(DK0401.HISOS_CITY_NM) AS HISOS_CITY_NM,
                MAX(DK0401.HISOS_OAZTSU_NM) AS HISOS_OAZTSU_NM,
                MAX(DK0401.HISOS_AZCHO_NM) AS HISOS_AZCHO_NM,
                MAX(DK0401.HISOS_BNCHIGO) AS HISOS_BNCHIGO,
                MAX(DK0401.HISOS_ADRTTM) AS HISOS_ADRTTM,
                MAX(DK0401.HISOS_ADRRM) AS HISOS_ADRRM,
                MAX(DK0401.HISOS_NM) AS HISOS_NM,
                MAX(DK0401.HISOS_TELNO) AS HISOS_TELNO,
                MAX(DK0401.KEISHA_NM) AS KEISHA_NM,
                MAX(DK0401.SYSID) AS SYSID,
                MAX(DK0401.KOJI_COMP_CD) AS KOJI_COMP_CD,
                MAX(DK0401.KOJI_RSV_YMD) AS KOJI_RSV_YMD,
                MAX(DK0401.KOJIAK_NO) AS KOJIAK_NO,
                MAX(DK0401.DOJI_KOJIAK_NO) AS DOJI_KOJIAK_NO,
                MAX(DK0401.DDT_NO) AS DDT_NO,
                MAX(DK0401.OPMS_MSKM_FORM) AS OPMS_MSKM_FORM,
                MAX(DK0401.TV_SBT) AS TV_SBT,
                MAX(DK0401.AD_MI_FIX_FLG) AS AD_MI_FIX_FLG,
                MAX(DK0401.STB_NON_MENU_FLG) AS STB_NON_MENU_FLG,
                MAX(DK0401.SHUKKA_SBT_CD) AS SHUKKA_SBT_CD,
                MAX(DK0401.HAISO_ARIV_SHITEI_TIME_CD) AS HAISO_ARIV_SHITEI_TIME_CD,
                MAX(DK0401.SAIBAN_STA_TCASE_NO) AS SAIBAN_STA_TCASE_NO
         FROM   DK_T_SHKA_KIHON_INFO DK0401
                INNER JOIN DK_T_SHKA_KIHON_TMP DK0431
                ON  DK0401.HAISO_NO = DK0431.HAISO_NO
                AND DK0431.MK_FLG = '0'
         WHERE  DK0401.HAISO_SJI_YMD = ?
         AND    DK0401.MK_FLG = '0'
         GROUP BY DK0401.SHUKKA_LOT_NO,DK0401.HAISO_NO
       ) DK0401
       INNER JOIN DK_T_HAISO DK0011
       ON  DK0011.HAISO_NO = DK0401.HAISO_NO
       AND DK0011.MK_FLG = '0'
       INNER JOIN DK_T_SHKA_TNMT_INFO DK0411
       ON  DK0411.SHUKKA_LOT_NO = DK0401.SHUKKA_LOT_NO
       AND DK0411.MK_FLG = '0'
GROUP BY DK0401.SHUKKA_LOT_NO,
         DK0401.HAISO_NO,
         DK0011.HAISO_MOTO_SOKO_CD,
         DK0411.SHELF_CD,
         DK0011.HAISO_SJI_YMD

#-

#-----------------------------------------------------------
#--出荷指図（基本情報）(DK_T_SHKA_KIHON_INFO-DK_UPDATE_001)
#--住所変更で移動する機器の端末情報の件数をデータ数に加算する
#-----------------------------------------------------------

--KEY:DK_UPDATE_001

UPDATE DK_T_SHKA_KIHON_INFO DK0401
SET DK0401.DATA_CNT = (SELECT COUNT(1)
                       FROM   DK_T_SHKA_TNMT_INFO DK0411
                       WHERE  DK0411.SHUKKA_LOT_NO = DK0401.SHUKKA_LOT_NO
                       AND    DK0411.SVC_KEI_NO = DK0401.SVC_KEI_NO), 
    DK0401.UPD_DTM = ?, 
    DK0401.UPD_OPEACNT = ?,
    DK0401.UPD_UNYO_YMD = 'UPD_UNYO',
    DK0401.UPD_TRN_ID = 'UPD_TRN'
WHERE DK0401.SHUKKA_LOT_NO = ?
AND   DK0401.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--出荷指図（基本情報）(DK_T_SHKA_KIHON_INFO-DK_SELECT_003)
#--出荷指図の基本情報が存在するかをチェック
#-----------------------------------------------------------

--KEY:DK_SELECT_003

SELECT COUNT(1) AS CNT
FROM   DK_T_SHKA_KIHON_INFO DK0401
WHERE  DK0401.SHUKKA_LOT_NO = ?
AND    DK0401.SVC_KEI_NO = ?

#-

#-----------------------------------------------------------
#--出荷指図（基本情報）(DK_T_SHKA_KIHON_INFO-DK_SELECT_004)
#--同じ出荷ロット番号のサービス契約番号を取得する
#-----------------------------------------------------------

--KEY:DK_SELECT_004

SELECT
SVC_KEI_NO
FROM DK_T_SHKA_KIHON_INFO
WHERE SHUKKA_LOT_NO = ?
AND MK_FLG = '0'

#-

#-----------------------------------------------------------
#--出荷指図（基本情報）(DK_T_SHKA_KIHON_INFO-DK_SELECT_005)
#--同一配送番号の出荷指図の基本情報が存在するかをチェック
#-----------------------------------------------------------

--KEY:DK_SELECT_005

SELECT
COUNT(1) AS CNT
FROM DK_T_SHKA_KIHON_INFO
WHERE HAISO_NO = ?
AND SHUKKA_LOT_NO <> ?
AND MK_FLG = '0'

#-

#-----------------------------------------------------------
#--出荷指図（基本情報）(DK_T_SHKA_KIHON_INFO-KK_SELECT_001)
#--【保存期間経過データ物理削除】
#--削除対象の出荷ロット番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
    DK0401.SHUKKA_LOT_NO
FROM
    DK_T_SHKA_KIHON_INFO DK0401
WHERE
        DK0401.SVC_KEI_NO = ?
    AND DK0401.MK_FLG = '0'

#-