#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-CH_SELECT_001)
#--回線場所住所補記・部屋番号を取得する。
#-----------------------------------------------------------

--KEY:CH_SELECT_001

SELECT KK0251.SKS_YO_KISN_PLACE_AD_RM_NO
FROM   KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN
      (SELECT KK0241.SVC_KEI_KAISEN_UCWK_NO
             ,KK0241.SVC_KEI_NO
        FROM  KK_T_KAISEN_TG_SVKEI KK0241
        WHERE KK0241.KAISEN_UCWK_USE_STAYMD <= ?
          AND KK0241.KAISEN_UCWK_USE_ENDYMD >= ?
          AND KK0241.MK_FLG = '0'
      ) KK0241
   ON   KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
INNER JOIN
       (SELECT KK0081.SVC_KEI_NO
              ,KK0081.TK_HOSHIKI_KEI_NO
        FROM   KK_T_SVC_KEI KK0081
        WHERE
              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') 
       ) KK0081
   ON    KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO
INNER JOIN 
        KK_T_TK_HOSHIKI_KEI KK0891
   ON   KK0891.TK_HOSHIKI_KEI_NO = KK0081.TK_HOSHIKI_KEI_NO
  AND   KK0891.MK_FLG = '0'
INNER JOIN
        KK_T_KAISEN_USE_KEI KK0881
   ON   KK0881.KAISEN_USE_KEI_NO = KK0891.KAISEN_USE_KEI_NO
  AND   (     KK0881.MANSION_ID = ?
          OR  KK0881.CATID = ?
          OR  KK0881.MSBSN_ANKEN_KNRI_NO = ?
          OR  KK0881.OWNR_KEI_NO =
             (SELECT  KK0871.OWNR_KEI_NO
              FROM    KK_T_OWNR_KEI KK0871
              WHERE   KK0871.PID = ?
              AND     KK0871.MK_FLG = '0')
        )
  AND   KK0881.MK_FLG = '0'
WHERE
     KK0251.GENE_ADD_DTM =
     ( SELECT
           MAX(KK0251_GENE.GENE_ADD_DTM)
       FROM
           KK_T_SVKEI_KAISEN_UW KK0251_GENE
       WHERE
           KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
       AND KK0251_GENE.MK_FLG = '0' )

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_001)
#--サービス契約番号に紐づくサービス契約回線内訳を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT /* KK_T_SVKEI_KAISEN_UW.SQL KK_SELECT_001 */
 UW_KAISEN.KAISEN_PLACE_PCD,
 UW_KAISEN.KAISEN_PLACE_STATE_NM,
 UW_KAISEN.KAISEN_PLACE_CITY_NM,
 UW_KAISEN.KAISEN_PLACE_OAZTSU_NM,
 UW_KAISEN.KAISEN_PLACE_AZCHO_NM,
 UW_KAISEN.KAISEN_PLACE_BNCHIGO,
 UW_KAISEN.KAISEN_PLACE_ADRTTM,
 UW_KAISEN.KAISEN_PLACE_ADRRM,
 UW_KAISEN.KISN_PLC_AD_MI_FIX_FLG,
 UW_KAISEN.MSKM_DTL_NO,
 UW_KAISEN.SVC_KEI_KAISEN_UCWK_NO
FROM
 KK_T_SVKEI_KAISEN_UW UW_KAISEN
INNER JOIN 
 KK_T_KAISEN_TG_SVKEI SVKEI_KAISEN
ON
 UW_KAISEN.SVC_KEI_KAISEN_UCWK_NO = SVKEI_KAISEN.SVC_KEI_KAISEN_UCWK_NO
WHERE
 SVKEI_KAISEN.SVC_KEI_NO = ?
AND SVKEI_KAISEN.KAISEN_UCWK_USE_STAYMD <= ?
AND (SVKEI_KAISEN.SVC_KEI_NO, SVKEI_KAISEN.KAISEN_UCWK_USE_ENDYMD) = 
        (SELECT SVKEI_KAISEN_01.SVC_KEI_NO, MAX(SVKEI_KAISEN_01.KAISEN_UCWK_USE_ENDYMD)
         FROM   KK_T_KAISEN_TG_SVKEI SVKEI_KAISEN_01
         WHERE  SVKEI_KAISEN_01.SVC_KEI_NO = SVKEI_KAISEN.SVC_KEI_NO
         AND    SVKEI_KAISEN_01.KAISEN_UCWK_USE_STAYMD <= ?
         AND    SVKEI_KAISEN_01.MK_FLG = '0'
         GROUP BY SVKEI_KAISEN_01.SVC_KEI_NO)
AND SVKEI_KAISEN.MK_FLG = '0'
AND UW_KAISEN.GENE_ADD_DTM =
         (SELECT MAX(UW_KAISEN_GENE.GENE_ADD_DTM) AS UW_KAISEN_MAX
          FROM  KK_T_SVKEI_KAISEN_UW UW_KAISEN_GENE
          WHERE  UW_KAISEN_GENE.SVC_KEI_KAISEN_UCWK_NO = UW_KAISEN.SVC_KEI_KAISEN_UCWK_NO
          AND    UW_KAISEN_GENE.MK_FLG = '0')
ORDER BY UW_KAISEN.SVC_KEI_KAISEN_UCWK_NO DESC

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_003)
#--サービス契約番号にひもづくデータを取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT
    KK0251.SVC_KEI_KAISEN_UCWK_NO,
    KK0251.GENE_ADD_DTM,
    KK0251.KAISEN_PLACE_PCD,
    KK0251.KAISEN_PLACE_STATE_NM,
    KK0251.KAISEN_PLACE_CITY_NM,
    KK0251.KAISEN_PLACE_OAZTSU_NM,
    KK0251.KAISEN_PLACE_AZCHO_NM,
    KK0251.KAISEN_PLACE_BNCHIGO,
    KK0251.KAISEN_PLACE_ADRTTM,
    KK0251.KAISEN_PLACE_ADRRM,
    KK0251.KAISEN_PLACE_TELNO,
    KK0251.KISN_PLC_AD_MI_FIX_FLG
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE
    KK0251.GENE_ADD_DTM =
         (SELECT
              MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
          FROM
              KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE
              KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
          AND KK0251_GENE.MK_FLG = '0')
AND KK0251.SVC_KEI_KAISEN_UCWK_NO in
         (SELECT
              KK0241_1.SVC_KEI_KAISEN_UCWK_NO
          FROM
              KK_T_KAISEN_TG_SVKEI KK0241_1
          INNER JOIN
                   (SELECT
                        KK0241_2.SVC_KEI_NO,COUNT(SVC_KEI_NO) AS KK0241_2_MAX
                    FROM
                        KK_T_KAISEN_TG_SVKEI KK0241_2
                    WHERE
                        KK0241_2.KAISEN_UCWK_USE_STAYMD =
                               (SELECT
                                    MAX(KK0241_STA.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAX
                                FROM
                                    KK_T_KAISEN_TG_SVKEI KK0241_STA
                                WHERE
                                    KK0241_STA.SVC_KEI_KAISEN_UCWK_NO = KK0241_2.SVC_KEI_KAISEN_UCWK_NO
                                AND KK0241_STA.SVC_KEI_NO = KK0241_2.SVC_KEI_NO
                                AND KK0241_STA.KAISEN_UCWK_USE_STAYMD <= ?
                                AND KK0241_STA.MK_FLG = '0')
                    GROUP BY KK0241_2.SVC_KEI_NO
                   )  KK0241_3
                   ON KK0241_3.SVC_KEI_NO = KK0241_1.SVC_KEI_NO
          WHERE
              KK0241_1.SVC_KEI_NO = ?
          AND (
               (KK0241_3.KK0241_2_MAX > 1 AND EXISTS
                     (SELECT
                          K2091.ADCHG_NO
                      FROM
                          KK_T_ADCHG K2091
                      WHERE
                          K2091.ITENS_SVKEI_KISUW_NO = KK0241_1.SVC_KEI_KAISEN_UCWK_NO
                      AND K2091.MK_FLG = '0')
               )
               OR
               (KK0241_3.KK0241_2_MAX = 1)
              )
          )

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_004)
#--サービス契約番号に紐づくサービス契約回線内訳情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT  KAISEN_UW.SVC_KEI_KAISEN_UCWK_NO,
        KAISEN_UW.KAISEN_PLACE_AD_CD,
        KAISEN_UW.KAISEN_PLACE_PCD,
        KAISEN_UW.KAISEN_PLACE_STATE_NM,
        KAISEN_UW.KAISEN_PLACE_CITY_NM,
        KAISEN_UW.KAISEN_PLACE_OAZTSU_NM,
        KAISEN_UW.KAISEN_PLACE_AZCHO_NM,
        KAISEN_UW.KAISEN_PLACE_BNCHIGO,
        KAISEN_UW.KAISEN_PLACE_ADRTTM,
        KAISEN_UW.KAISEN_PLACE_ADRRM,
        KAISEN_UW.LGTD,
        KAISEN_UW.LTTD,
        KAISEN_UW.MANSION_BUKKEN_NO,
        KAISEN_UW.SKS_YO_KISN_PLACE_AD_RM_NO,
        KAISEN_UW.KISN_PLC_AD_MI_FIX_FLG
FROM    KK_T_SVKEI_KAISEN_UW KAISEN_UW
INNER JOIN KK_T_KAISEN_TG_SVKEI KAISEN_TG_SVKEI
ON      KAISEN_UW.SVC_KEI_KAISEN_UCWK_NO = KAISEN_TG_SVKEI.SVC_KEI_KAISEN_UCWK_NO
WHERE   KAISEN_TG_SVKEI.SVC_KEI_NO = ?
AND     KAISEN_TG_SVKEI.MK_FLG = '0'
AND     KAISEN_UW.GENE_ADD_DTM = (SELECT MAX(KK_T_SVKEI_KAISEN_UW_RCNT.GENE_ADD_DTM) AS KK_T_SVKEI_KAISEN_UW_MAX
            FROM    KK_T_SVKEI_KAISEN_UW KK_T_SVKEI_KAISEN_UW_RCNT
            WHERE   KK_T_SVKEI_KAISEN_UW_RCNT.SVC_KEI_KAISEN_UCWK_NO = KAISEN_UW.SVC_KEI_KAISEN_UCWK_NO
            AND     KK_T_SVKEI_KAISEN_UW_RCNT.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_005)
#--KKPRC00904
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT
 KK0251.GENE_ADD_DTM,
 KK0251.SVC_KEI_KAISEN_UCWK_STAT,
 KK0251.PON_SBT_CD
FROM
 KK_T_SVKEI_KAISEN_UW KK0251
WHERE
 KK0251.SVC_KEI_KAISEN_UCWK_NO = ? AND
 KK0251.GENE_ADD_DTM = (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) KK0251_MAX
              FROM KK_T_SVKEI_KAISEN_UW KK0251_GENE
              WHERE KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
                    KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_006)
#--サービス締結登録
#--対象機器提供サービス契約更新
#-----------------------------------------------------------

--KEY:KK_SELECT_006

SELECT
  KK0251.SVC_KEI_KAISEN_UCWK_NO,
  KK0251.MANSION_BUKKEN_NO,
  KK0251.MSKM_DTL_NO
FROM
  KK_T_SVKEI_KAISEN_UW KK0251
  INNER JOIN
    KK_T_KAISEN_TG_SVKEI KK0241
  ON
    KK0241.SVC_KEI_NO = ?
    AND KK0241.KAISEN_UCWK_USE_STAYMD = 
    (SELECT
      MAX(KK0241_MAX.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAXYMD
    FROM
      KK_T_KAISEN_TG_SVKEI KK0241_MAX
    WHERE
      KK0241.SVC_KEI_NO = KK0241_MAX.SVC_KEI_NO AND
      KK0241_MAX.KAISEN_UCWK_USE_STAYMD <= ? AND
      KK0241_MAX.MK_FLG = '0') 
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
    AND KK0251.GENE_ADD_DTM =
    (SELECT
      MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
    FROM
      KK_T_SVKEI_KAISEN_UW KK0251_GENE
    WHERE
      KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
      AND KK0251_GENE.MK_FLG = '0')


#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_007)
#--KKPRC99999(TV共通)
#--KKPRC33703, KKPRC33704, KKPRC33701,
#--KKPRC09701
#--サービス契約回線内訳のカレントデータを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_007

SELECT
 KK0251.SVC_KEI_KAISEN_UCWK_NO,
 KK0251.GENE_ADD_DTM,
 KK0251.SVC_KEI_KAISEN_UCWK_STAT,
 KK0251.MSKM_DTL_NO,
 KK0251.KISN_PLC_AD_MI_FIX_FLG,
 KK0251.KISN_PLC_AD_MI_FIX_RLS_YMD,
 KK0251.KAISEN_PLC_KSH_AD_SAI_FLG,
 KK0251.CUST_SKK_AD_MI_FIX_FLG,
 KK0251.CUST_SOS_USE_UM,
 KK0251.KAISEN_CD,
 KK0251.MANSION_BUKKEN_NO,
 KK0251.KAISEN_PLACE_TELNO,
 KK0251.KAISEN_PLC_KANA,
 KK0251.KAISEN_PLACE_AD_CD,
 KK0251.KAISEN_PLACE_PCD,
 KK0251.KAISEN_PLACE_STATE_NM,
 KK0251.KAISEN_PLACE_CITY_NM,
 KK0251.KAISEN_PLACE_OAZTSU_NM,
 KK0251.KAISEN_PLACE_AZCHO_NM,
 KK0251.KAISEN_PLACE_BNCHIGO,
 KK0251.KAISEN_PLACE_ADRTTM,
 KK0251.KAISEN_PLACE_ADRRM,
 KK0251.SKS_YO_KISN_PLACE_AD_RM_NO,
 KK0251.KAISEN_PLACE_NO,
 KK0251.KISN_PLC_KKK_SEIRI_CHU_FLG,
 KK0251.KISN_PLC_HSK_CD_1,
 KK0251.KISN_PLC_HSK_CD_2,
 KK0251.KISN_PLC_HSK_MEMO,
 KK0251.USE_STAYMD,
 KK0251.AD_FORM_CD,
 KK0251.SHUNKO_RSV_YMD,
 KK0251.EPOWER_SODEN_RSV_YMD,
 KK0251.NYUKYO_RSV_YMD,
 KK0251.HIKIWATASHI_RSV_YMD,
 KK0251.HIKKOSHI_RSV_YMD,
 KK0251.SKS_SHS_AD_CHG_UM,
 KK0251.SKS_SHS_AD_CHG_KIBO_YMD,
 KK0251.KSHAD_CHG_UM,
 KK0251.KSHAD_CHG_KIBO_YMD,
 KK0251.XZAHYO,
 KK0251.YZAHYO,
 KK0251.LGTD,
 KK0251.LTTD,
 KK0251.ZAHYO_HOSEI_UM,
 KK0251.TV_KOBET_TAIO_CD,
 KK0251.TV_ANTENNA_UM,
 KK0251.DIRECTION_CD_1,
 KK0251.DIRECTION_CD_2,
 KK0251.KODATE_KCKU_FLR_CNT_CD,
 KK0251.SHUGJ_NYUKYO_FLR_CNT_CD,
 KK0251.PON_SBT_CD,
 KK0251.LOOPBACK_TEST_FIN_YMD,
 KK0251.KAISEN_TK_COMP_CD,
 KK0251.VDSL_USE_TEL_KISN_SBT_CD,
 KK0251.VDSL_USE_DKSN_SWCH_RSV_YMD,
 KK0251.VDSL_USE_TG_DBKSN_SBT_CD,
 KK0251.VDSL_USE_TG_TELNO,
 KK0251.TEL_BAS_HOST_ID,
 KK0251.TEL_VLAN_ID,
 KK0251.VLAN_ID_FIX_FLG,
 KK0251.TEL_VLAN_ID_TRKM_YMD,
 KK0251.IDO_DIV,
 KK0251.HIKKOSHI_YMD,
 KK0251.GAITO_AREA_SVC_STAYMD,
 KK0251.TEKKYO_KOJI_KIBO_YMD,
 KK0251.USE_ENDYMD,
 KK0251.KITEN_HDSHO_CD,
 KK0251.SHYAKK_CD,
 KK0251.SVC_CANCEL_YMD,
 KK0251.SVC_CANCEL_RSN_CD,
 KK0251.SVKEI_KISN_UW_CNCL_CL_YMD,
 KK0251.KEPCO_BUSIOFFICE_CD,
 KK0251.ADD_DTM,
 KK0251.ADD_OPEACNT,
 KK0251.KAISEN_PLACE_TOU_INFO,
 KK0251.ROZETT_STC_STAT_CD,
 KK0251.CONSNT_STC_NYO_CD,
 KK0251.TNMT_STC_FLR_CD,
 KK0251.KISN_TD_SETPLC_1_CD,
 KK0251.KISN_TD_SETPLC_2_CD,
 KK0251.KISN_TD_STC_KASHO_CD,
 KK0251.KISN_TD_SETPLC_1_OTR,
 KK0251.KISN_TD_SETPLC_2_OTR,
 KK0251.KISN_TD_STC_KASHO_OTR,
 KK0251.TANMT_STC_MEN_CD,
 KK0251.KAISEN_PLACE_RENBAN,
 KK0251.HGW_KKN_FFG
FROM
  KK_T_SVKEI_KAISEN_UW KK0251
WHERE
  KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
  AND KK0251.GENE_ADD_DTM =
    (SELECT
      MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
       KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
       KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
       AND  KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_008)
#--サービス契約回線内訳番号に紐づくサービス契約回線内訳データを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_008

SELECT 
  KK0251.SVC_KEI_KAISEN_UCWK_NO 
  ,KK0251.KAISEN_PLACE_TELNO 
  ,KK0251.KAISEN_PLACE_AD_CD 
  ,KK0251.KAISEN_PLACE_PCD 
  ,KK0251.KAISEN_PLACE_STATE_NM 
  ,KK0251.KAISEN_PLACE_CITY_NM 
  ,KK0251.KAISEN_PLACE_OAZTSU_NM 
  ,KK0251.KAISEN_PLACE_AZCHO_NM 
  ,KK0251.KAISEN_PLACE_BNCHIGO 
  ,KK0251.KAISEN_PLACE_ADRTTM 
  ,KK0251.KAISEN_PLACE_ADRRM 
  ,KK0251.KAISEN_PLACE_NO 
  ,KK0251.KISN_PLC_KKK_SEIRI_CHU_FLG 
  ,KK0251.KISN_PLC_HSK_CD_1 
  ,KK0251.KISN_PLC_HSK_CD_2 
  ,KK0251.KISN_PLC_HSK_MEMO 
  ,KK0251.KISN_PLC_AD_MI_FIX_FLG 
FROM  
  KK_T_SVKEI_KAISEN_UW KK0251 
WHERE 
  KK0251.SVC_KEI_KAISEN_UCWK_NO = 
    (SELECT KK0241.SVC_KEI_KAISEN_UCWK_NO 
     FROM KK_T_KAISEN_TG_SVKEI KK0241 
     WHERE KK0241.SVC_KEI_NO = ? 
     AND KK0241.KAISEN_UCWK_USE_STAYMD <= ? 
     AND KK0241.MK_FLG = '0') 
  AND KK0251.MK_FLG = '0' 
  AND KK0251.GENE_ADD_DTM = 
         (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX 
          FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE 
          WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO 
          AND    KK0251_GENE.MK_FLG = '0') 


#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_009)
#--使用終了年月日がnullの情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_009

SELECT
1
FROM
KK_T_SVKEI_KAISEN_UW SKU
WHERE
SKU.SVC_KEI_KAISEN_UCWK_NO = ?
AND SKU.USE_ENDYMD IS NULL
AND SKU.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_010)
#--使用開始年月日がnullの情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_010

SELECT
1
FROM
KK_T_SVKEI_KAISEN_UW SKU
WHERE
SKU.SVC_KEI_KAISEN_UCWK_NO = ?
AND SKU.USE_STAYMD IS NULL
AND SKU.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_012)
#--回復不可契約情報抽出のための、サービス契約番号に紐づくサービス契約回線内訳情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_012

SELECT   KK0251.SVC_KEI_KAISEN_UCWK_NO,
         KK0251.KAISEN_PLACE_NO,
         KK0251.KAISEN_PLACE_AD_CD,
         KK0251.KAISEN_PLACE_PCD,
         KK0251.KAISEN_PLACE_STATE_NM,
         KK0251.KAISEN_PLACE_CITY_NM,
         KK0251.KAISEN_PLACE_OAZTSU_NM,
         KK0251.KAISEN_PLACE_AZCHO_NM,
         KK0251.KAISEN_PLACE_BNCHIGO,
         KK0251.KAISEN_PLACE_ADRTTM,
         KK0251.KAISEN_PLACE_ADRRM,
         KK0251.KISN_PLC_AD_MI_FIX_FLG,
         KK0251.LGTD,
         KK0251.LTTD,
         KK0251.AD_FORM_CD,
         KK0251.SHUGJ_NYUKYO_FLR_CNT_CD,
         KK0251.KODATE_KCKU_FLR_CNT_CD,
         KK0251.DIRECTION_CD_1,
         KK0251.DIRECTION_CD_2
FROM     KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241
ON       KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
WHERE    KK0241.SVC_KEI_NO = ?
AND      KK0241.KAISEN_UCWK_USE_STAYMD <= ?
AND      KK0241.KAISEN_UCWK_USE_ENDYMD >= ?
AND      KK0241.MK_FLG = '0'
AND      KK0251.GENE_ADD_DTM =
         (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
          FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
          AND    KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_013)
#--該当のサービス契約回線内訳番号の回線場所番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_013

SELECT
    KAISEN_UW.KAISEN_PLACE_NO
FROM
    KK_T_SVKEI_KAISEN_UW KAISEN_UW
WHERE
    KAISEN_UW.SVC_KEI_KAISEN_UCWK_NO = ?
AND
    KAISEN_UW.GENE_ADD_DTM = (
        SELECT MAX(SVKEI_KAISEN_UW_RCNT.GENE_ADD_DTM) AS SVKEI_KAISEN_UW_MAX
        FROM  KK_T_SVKEI_KAISEN_UW SVKEI_KAISEN_UW_RCNT
        WHERE  SVKEI_KAISEN_UW_RCNT.SVC_KEI_KAISEN_UCWK_NO = KAISEN_UW.SVC_KEI_KAISEN_UCWK_NO
        AND    SVKEI_KAISEN_UW_RCNT.MK_FLG = '0'
    )

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_015)
#--KKPRC01701
#--サービス契約番号に紐づくサービス契約回線内訳情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_015

SELECT
    KK0251.KITEN_HDSHO_CD,
    KK0251.KAISEN_PLACE_STATE_NM,
    KK0251.KAISEN_PLACE_CITY_NM,
    KK0251.KAISEN_PLACE_OAZTSU_NM,
    KK0251.KAISEN_PLACE_AZCHO_NM,
    KK0251.KAISEN_PLACE_BNCHIGO,
    KK0251.KAISEN_PLACE_ADRTTM
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN
    KK_T_KAISEN_TG_SVKEI KK0241
ON
    KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
    KK0241.SVC_KEI_NO = ? AND
    KK0241.KAISEN_UCWK_USE_STAYMD = (SELECT
                                        MAX(KK0241_KUUS_MAX.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAX
                                    FROM
                                        KK_T_KAISEN_TG_SVKEI KK0241_KUUS_MAX
                                    WHERE
                                        KK0241_KUUS_MAX.SVC_KEI_NO = KK0241.SVC_KEI_NO AND
                                        KK0241_KUUS_MAX.KAISEN_UCWK_USE_STAYMD <= ? AND
                                        KK0241_KUUS_MAX.MK_FLG = '0')
WHERE
    KK0251.GENE_ADD_DTM = (SELECT
                               MAX(KK0251_MAX.GENE_ADD_DTM)
                             FROM
                               KK_T_SVKEI_KAISEN_UW KK0251_MAX
                            WHERE
                               KK0251_MAX.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
                               AND KK0251.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_016)
#--サービス契約回線内訳番号から、サービス契約番号．申込書番号を取得する。
#--KKPRC35201にて使用
#-----------------------------------------------------------

--KEY:KK_SELECT_016

SELECT 
    KK0081.MSKM_DTL_NO,
    KK0251.KISN_PLC_AD_MI_FIX_FLG,
    KK0251.KAISEN_PLACE_NO
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
    INNER JOIN 
        KK_T_KAISEN_TG_SVKEI KK0241
    ON
        KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
    INNER JOIN 
        KK_T_SVC_KEI KK0081
    ON
        KK0241.SVC_KEI_NO = KK0081.SVC_KEI_NO
WHERE    
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
    AND KK0241.KAISEN_UCWK_USE_STAYMD <= ?
    AND KK0241.KAISEN_UCWK_USE_ENDYMD >= ?
    AND KK0241.MK_FLG = '0'
    AND KK0251.GENE_ADD_DTM =
         (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
          FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
          AND    KK0251_GENE.MK_FLG = '0')
    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')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_017)
#--サービス契約開始対象抽出（KKPRC00901)
#--(3)サービス開始対象契約出力
#--同一回線内訳番号に紐づいているネットのサービス契約番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_017

SELECT
 KK0081.SVC_KEI_NO
FROM
 KK_T_SVKEI_KAISEN_UW KK0251
 INNER JOIN
  KK_T_KAISEN_TG_SVKEI KK0241
 ON
  KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
  ? BETWEEN KK0241.KAISEN_UCWK_USE_STAYMD AND KK0241.KAISEN_UCWK_USE_ENDYMD
 INNER JOIN
  KK_T_SVC_KEI KK0081
 ON
  KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO
  AND KK0081.SVC_KEI_STAT = '030'
  AND KK0081.SVC_CD = '01'
  AND KK0081.PRC_GRP_CD IN('02','03','04')
  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')
WHERE
 KK0251.SVC_KEI_KAISEN_UCWK_NO = ? AND
 KK0251.GENE_ADD_DTM = 
  (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
  FROM
   KK_T_SVKEI_KAISEN_UW KK0251_GENE
  WHERE
   KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
   AND KK0251.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_018)
#--契約情報ファイル作成のための、サービス契約に紐付くサービス契約回線内訳情報を抽出する。
#-----------------------------------------------------------

--KEY:KK_SELECT_018

SELECT
     KTSKU.SHYAKK_CD
    ,KTSKU.KITEN_HDSHO_CD
    ,KTSKU.KAISEN_PLACE_PCD
    ,KTSKU.KAISEN_PLACE_STATE_NM
    ,KTSKU.KAISEN_PLACE_CITY_NM
    ,KTSKU.KAISEN_PLACE_OAZTSU_NM
    ,KTSKU.KAISEN_PLACE_AZCHO_NM
    ,KTSKU.KAISEN_PLACE_BNCHIGO
    ,KTSKU.KAISEN_PLACE_ADRTTM
    ,KTSKU.KAISEN_PLACE_ADRRM
    ,KTSKU.AD_FORM_CD
    ,KTSKU.SHUGJ_NYUKYO_FLR_CNT_CD
    ,KTSKU.KODATE_KCKU_FLR_CNT_CD
FROM
    KK_T_KAISEN_TG_SVKEI KTKTS
INNER JOIN
    KK_T_SVKEI_KAISEN_UW KTSKU
ON
    KTKTS.SVC_KEI_KAISEN_UCWK_NO = KTSKU.SVC_KEI_KAISEN_UCWK_NO
WHERE
    KTKTS.SVC_KEI_NO = ?
AND
    KTKTS.MK_FLG = '0'
AND
    KTSKU.GENE_ADD_DTM = (
        SELECT
            MAX(KTSKU_GENE.GENE_ADD_DTM) AS KTSKU_MAX
        FROM
            KK_T_SVKEI_KAISEN_UW KTSKU_GENE
        WHERE
            KTSKU_GENE.SVC_KEI_KAISEN_UCWK_NO = KTSKU.SVC_KEI_KAISEN_UCWK_NO
        AND
            KTSKU_GENE.MK_FLG = '0'
    )

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_020)
#--サービス契約回線内訳番号を基にマンション物件番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_020

SELECT 
  KK0251.MANSION_BUKKEN_NO AS MANSION_BUKKEN_NO
FROM  
  KK_T_SVKEI_KAISEN_UW KK0251 
WHERE 
  KK0251.SVC_KEI_KAISEN_UCWK_NO = ? 
  AND KK0251.MK_FLG = '0' 
  AND KK0251.GENE_ADD_DTM = 
         (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX 
          FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE 
          WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO 
          AND    KK0251_GENE.MK_FLG = '0') 

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_021)
#--電話VLAN-ID取込年月日を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_021

SELECT 
    SKU.VDSL_USE_TG_TELNO AS VDSL_USE_TG_TELNO
FROM  
    KK_T_SVKEI_KAISEN_UW SKU 
WHERE 
    SKU.SVC_KEI_KAISEN_UCWK_NO = ? 
AND
    SKU.GENE_ADD_DTM = 
    (
        SELECT
            MAX(SKU_GENE.GENE_ADD_DTM) AS GENE_ADD_DTM_MAX 
        FROM
            KK_T_SVKEI_KAISEN_UW SKU_GENE
        WHERE
            SKU.SVC_KEI_KAISEN_UCWK_NO = SKU_GENE.SVC_KEI_KAISEN_UCWK_NO 
        AND
            SKU_GENE.MK_FLG = '0'
    ) 
AND
    SKU.MK_FLG = '0' 

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_022)
#--KKPRC00912
#--サービス提供中のサービス契約回線内訳に紐づく情報を抽出します。
#-----------------------------------------------------------

--KEY:KK_SELECT_022

SELECT
    KK0241.SVC_KEI_NO,
    KK2111.LAST_UPD_DTM
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN
    KK_T_KAISEN_TG_SVKEI KK0241
ON
    KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
    KK0241.MK_FLG = '0'
INNER JOIN
    KK_T_SVKEI_EXC_CTRL KK2111
ON
    KK2111.SVC_KEI_NO = KK0241.SVC_KEI_NO AND
    KK2111.MK_FLG = '0'
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ? AND
    KK0251.SVC_KEI_KAISEN_UCWK_STAT = '100' AND
    KK0251.GENE_ADD_DTM = 
    (SELECT
         MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
         KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
         KK0251_GENE.MK_FLG = '0') AND
    KK0251.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_023)
#--サービス契約回線内訳番号を基に最大世代のサービス契約回線内訳を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_023

SELECT
    KK0251.SVC_KEI_KAISEN_UCWK_NO AS SVC_KEI_KAISEN_UCWK_NO,
    KK0251.GENE_ADD_DTM AS GENE_ADD_DTM,
    KK0251.SVC_KEI_KAISEN_UCWK_STAT AS SVC_KEI_KAISEN_UCWK_STAT,
    KK0251.USE_STAYMD AS USE_STAYMD,
    KK0251.NYUKYO_RSV_YMD AS NYUKYO_RSV_YMD,
    KK0251.USE_ENDYMD AS USE_ENDYMD,
    KK0251.UPD_DTM AS UPD_DTM
FROM  
    KK_T_SVKEI_KAISEN_UW KK0251 
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND
    KK0251.GENE_ADD_DTM =
        (
            SELECT
                MAX(SKU.GENE_ADD_DTM) AS MAX_GENE_ADD_DTM
            FROM
                KK_T_SVKEI_KAISEN_UW SKU
            WHERE
                SKU.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
            AND
                SKU.MK_FLG = '0'
        )

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_024)
#--サービス契約番号に紐づく回線場所住所を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_024

SELECT
     SVKEI_KAISEN_UW.KAISEN_PLACE_PCD
    ,SVKEI_KAISEN_UW.KAISEN_PLACE_STATE_NM
    ,SVKEI_KAISEN_UW.KAISEN_PLACE_CITY_NM
    ,SVKEI_KAISEN_UW.KAISEN_PLACE_OAZTSU_NM
    ,SVKEI_KAISEN_UW.KAISEN_PLACE_AZCHO_NM
    ,SVKEI_KAISEN_UW.KAISEN_PLACE_BNCHIGO
    ,SVKEI_KAISEN_UW.KAISEN_PLACE_ADRTTM
    ,SVKEI_KAISEN_UW.KAISEN_PLACE_ADRRM
    ,SVKEI_KAISEN_UW.KAISEN_PLACE_NO
FROM
     KK_T_SVKEI_KAISEN_UW SVKEI_KAISEN_UW
    ,KK_T_KAISEN_TG_SVKEI KAISEN_TG_SVKEI
WHERE
    KAISEN_TG_SVKEI.SVC_KEI_NO = ?
AND
    KAISEN_TG_SVKEI.SVC_KEI_KAISEN_UCWK_NO = SVKEI_KAISEN_UW.SVC_KEI_KAISEN_UCWK_NO
AND
    SVKEI_KAISEN_UW.GENE_ADD_DTM =
        (SELECT MAX(SVKEI_KAISEN_UW_GENE.GENE_ADD_DTM) AS KK0081_MAX
         FROM   KK_T_SVKEI_KAISEN_UW SVKEI_KAISEN_UW_GENE
         WHERE  SVKEI_KAISEN_UW_GENE.SVC_KEI_KAISEN_UCWK_NO = SVKEI_KAISEN_UW.SVC_KEI_KAISEN_UCWK_NO
         AND    SVKEI_KAISEN_UW_GENE.MK_FLG = '0') 

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_025)
#--商品券発送指図ファイルに必要なデータの抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_025

SELECT 
    CUST.CUST_NM,
    CUST.SVC_KEI_NO,
    CUST.DCHSKMST_NO,
    CUST.DCHSKMST_TG_KEI_NO,
    CUST.GENE_ADD_DTM,
    CUST.SVC_STA_YMD,
    CUST.KAISEN_PLACE_PCD,
    CUST.KAISEN_PLACE_STATE_NM,
    CUST.KAISEN_PLACE_CITY_NM,
    CUST.KAISEN_PLACE_OAZTSU_NM,
    CUST.KAISEN_PLACE_AZCHO_NM,
    CUST.KAISEN_PLACE_BNCHIGO,
    CUST.KAISEN_PLACE_ADRTTM,
    CUST.KAISEN_PLACE_ADRRM,
    CUST.KAISEN_PLACE_TELNO,
    CUST.SVC_KEI_STAT,
    CUST.KEISHA_TELNO,
    CUST.SVC_CD
FROM (
(
SELECT 
    CK0011.CUST_NM,
    KK1401.SVC_KEI_NO,
    KK1401.DCHSKMST_NO,
    KK1401.DCHSKMST_TG_KEI_NO,
    KK1401.GENE_ADD_DTM,
    KK0081.SVC_STA_YMD,
    KK0251.KAISEN_PLACE_PCD,
    KK0251.KAISEN_PLACE_STATE_NM,
    KK0251.KAISEN_PLACE_CITY_NM,
    KK0251.KAISEN_PLACE_OAZTSU_NM,
    KK0251.KAISEN_PLACE_AZCHO_NM,
    KK0251.KAISEN_PLACE_BNCHIGO,
    KK0251.KAISEN_PLACE_ADRTTM,
    KK0251.KAISEN_PLACE_ADRRM,
    KK0251.KAISEN_PLACE_TELNO,
    KK0081.SVC_KEI_STAT,
    CK0011.KEISHA_TELNO,
    KK0081.SVC_CD,
    KK0081.PRC_GRP_CD
FROM 
    CK_T_CUST CK0011 ,
    KK_T_DCHSKMST_TG_KEI KK1401 ,
    KK_T_SVC_KEI         KK0081 ,
    KK_T_KAISEN_TG_SVKEI KK0241 ,
    KK_T_SVKEI_KAISEN_UW KK0251 
WHERE
    (KK1401.DCHSKMST_NO, KK1401.DCHSKMST_TG_KEI_NO, KK1401.GENE_ADD_DTM) =
    (SELECT KK1401_GENE.DCHSKMST_NO, KK1401_GENE.DCHSKMST_TG_KEI_NO, MAX(KK1401_GENE.GENE_ADD_DTM) AS KK1401_MAX
    FROM   KK_T_DCHSKMST_TG_KEI KK1401_GENE
    WHERE  KK1401_GENE.DCHSKMST_NO = KK1401.DCHSKMST_NO
    AND    KK1401_GENE.DCHSKMST_TG_KEI_NO = KK1401.DCHSKMST_TG_KEI_NO
    AND    KK1401_GENE.MK_FLG = '0'
    GROUP BY KK1401_GENE.DCHSKMST_NO, KK1401_GENE.DCHSKMST_TG_KEI_NO)
AND KK1401.DCHSKMST_NO = ? 
AND KK1401.TG_KEI_SKBT_CD = '01' 
AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) =
        (SELECT KK0081_GENE.SVC_KEI_NO, 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'
         GROUP BY KK0081_GENE.SVC_KEI_NO)
AND KK0081.SVC_CD != '05'
AND KK0081.SVC_KEI_NO = KK1401.SVC_KEI_NO
AND (KK0241.SVC_KEI_NO, KK0241.KAISEN_UCWK_USE_STAYMD) = 
    (SELECT KK0241_STA.SVC_KEI_NO, MAX(KK0241_STA.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAX
     FROM   KK_T_KAISEN_TG_SVKEI KK0241_STA
     WHERE  KK0241_STA.SVC_KEI_NO = KK0241.SVC_KEI_NO
     AND    KK0241_STA.KAISEN_UCWK_USE_STAYMD <= ?
     AND    KK0241_STA.MK_FLG = '0'
     GROUP BY KK0241_STA.SVC_KEI_NO) 
AND KK0241.SVC_KEI_NO = KK0081.SVC_KEI_NO 
AND (KK0251.SVC_KEI_KAISEN_UCWK_NO, KK0251.GENE_ADD_DTM) = 
    (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX 
     FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE 
     WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO 
     AND    KK0251_GENE.MK_FLG = '0'
     GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO) 
AND KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
AND KK0251.KISN_PLC_AD_MI_FIX_FLG = '0'
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) AS CK0011_MAX 
         FROM   CK_T_CUST CK0011_GENE 
         WHERE  CK0011_GENE.SYSID = CK0011.SYSID 
         AND    CK0011_GENE.RSV_APLY_YMD <= ? 
         AND    CK0011_GENE.RSV_APLY_CD = '2' 
         AND    CK0011_GENE.MK_FLG = '0'
         GROUP BY CK0011_GENE.SYSID) 
AND CK0011.SYSID = ? 
AND CK0011.CUST_STAT = '010' 
) UNION (
SELECT 
    CK0011.CUST_NM,
    KK1401.SVC_KEI_NO,
    KK1401.DCHSKMST_NO,
    KK1401.DCHSKMST_TG_KEI_NO,
    KK1401.GENE_ADD_DTM,
    KK0081.SVC_STA_YMD,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    KK0081.SVC_KEI_STAT,
    CK0011.KEISHA_TELNO,
    KK0081.SVC_CD,
    KK0081.PRC_GRP_CD
FROM
    CK_T_CUST CK0011 ,
    KK_T_DCHSKMST_TG_KEI KK1401 ,
    KK_T_SVC_KEI KK0081
WHERE 
    (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) AS CK0011_MAX 
         FROM   CK_T_CUST CK0011_GENE 
         WHERE  CK0011_GENE.SYSID = CK0011.SYSID 
         AND    CK0011_GENE.RSV_APLY_YMD <= ?
         AND    CK0011_GENE.RSV_APLY_CD = '2' 
         AND    CK0011_GENE.MK_FLG = '0'
         GROUP BY CK0011_GENE.SYSID) 
AND CK0011.SYSID = ?
AND CK0011.CUST_STAT = '010' 
AND (KK1401.DCHSKMST_NO, KK1401.DCHSKMST_TG_KEI_NO, KK1401.GENE_ADD_DTM) =
        (SELECT KK1401_GENE.DCHSKMST_NO, KK1401_GENE.DCHSKMST_TG_KEI_NO, MAX(KK1401_GENE.GENE_ADD_DTM) AS KK1401_MAX
         FROM   KK_T_DCHSKMST_TG_KEI KK1401_GENE
         WHERE  KK1401_GENE.DCHSKMST_NO = KK1401.DCHSKMST_NO
         AND    KK1401_GENE.DCHSKMST_TG_KEI_NO = KK1401.DCHSKMST_TG_KEI_NO
         AND    KK1401_GENE.MK_FLG = '0'
         GROUP BY KK1401_GENE.DCHSKMST_NO, KK1401_GENE.DCHSKMST_TG_KEI_NO)
AND KK1401.DCHSKMST_NO = ?
AND KK1401.TG_KEI_SKBT_CD = '01' 
AND (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) =
        (SELECT KK0081_GENE.SVC_KEI_NO, 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'
         GROUP BY KK0081_GENE.SVC_KEI_NO)
AND KK0081.SVC_CD = '05'
AND KK0081.SVC_KEI_NO = KK1401.SVC_KEI_NO
)
) CUST
ORDER BY 
    CUST.SVC_CD ASC,
    CUST.PRC_GRP_CD ASC

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_026)
#--サービス契約回線内訳番号と入居予定日を基に、サービス契約回線内訳を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_026

SELECT
    KK0251.SVC_KEI_KAISEN_UCWK_NO AS SVC_KEI_KAISEN_UCWK_NO,
    KK0251.GENE_ADD_DTM AS GENE_ADD_DTM,
    KK0251.SVC_KEI_KAISEN_UCWK_STAT AS SVC_KEI_KAISEN_UCWK_STAT,
    KK0251.USE_STAYMD AS USE_STAYMD,
    KK0251.NYUKYO_RSV_YMD AS NYUKYO_RSV_YMD,
    KK0251.UPD_DTM AS UPD_DTM
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND
    KK0251.NYUKYO_RSV_YMD = ?
AND
    KK0251.GENE_ADD_DTM =
        (
            SELECT
                MAX(SKU.GENE_ADD_DTM) AS MAX_GENE_ADD_DTM
            FROM
                KK_T_SVKEI_KAISEN_UW SKU
            WHERE
                SKU.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
            AND
                SKU.MK_FLG = '0'
        )

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_028)
#--KKPRC41001
#--マンション（子）情報のサービス契約回線内訳情報の取得
#-----------------------------------------------------------

--KEY:KK_SELECT_028

SELECT
 KK0251.SVC_KEI_KAISEN_UCWK_NO,
 KK0251.GENE_ADD_DTM,
 KK0251.MSKM_DTL_NO,
 KK0251.KITEN_HDSHO_CD,
 KK0251.SHYAKK_CD,
 KK2111.SVC_KEI_NO,
 KK2111.LAST_UPD_DTM
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN
    KK_T_KAISEN_TG_SVKEI KK0241
ON
    KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
    KK0241.MK_FLG = '0'
INNER JOIN
    KK_T_SVKEI_EXC_CTRL KK2111
ON
    KK2111.SVC_KEI_NO = KK0241.SVC_KEI_NO AND
    KK2111.MK_FLG = '0'
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ? AND
    KK0251.SVC_KEI_KAISEN_UCWK_STAT IN ('010','020','030','100','220','910') AND
    KK0251.GENE_ADD_DTM = 
    (SELECT
         MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
         KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
         KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_029)
#--加入御礼管理データ抽出
#--指定されたサービス契約番号のサービス契約回線内訳番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_029

SELECT      KK0251.SVC_KEI_KAISEN_UCWK_NO,
            KK0251.GENE_ADD_DTM
FROM        KK_T_KAISEN_TG_SVKEI KK0241
INNER JOIN (SELECT   KK0251.SVC_KEI_KAISEN_UCWK_NO,
                     KK0251.GENE_ADD_DTM,
                     MAX(KK0251.GENE_ADD_DTM) OVER (PARTITION BY KK0251.SVC_KEI_KAISEN_UCWK_NO) AS MAX_GENE_DTM
            FROM     KK_T_SVKEI_KAISEN_UW KK0251
            WHERE    KK0251.MK_FLG = '0'
           ) KK0251 ON
             KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO AND
             KK0251.GENE_ADD_DTM = KK0251.MAX_GENE_DTM
WHERE      KK0241.SVC_KEI_NO = ?
AND        ? BETWEEN KK0241.KAISEN_UCWK_USE_STAYMD AND KK0241.KAISEN_UCWK_USE_ENDYMD
AND        KK0241.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_030)
#--KKPRC35401にて使用
#--サービス契約内訳番号でサービス契約回線内訳TBLから回線場所郵便番号を取得します。
#-----------------------------------------------------------

--KEY:KK_SELECT_030

SELECT
    KK0251.KAISEN_PLACE_PCD
FROM
    KK_T_SVC_KEI_UCWK KK0161
    INNER JOIN
        KK_T_SVC_KEI KK0081
    ON
        KK0081.SVC_KEI_NO = KK0161.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_KAISEN_TG_SVKEI KK0241
    ON
        KK0241.SVC_KEI_NO = KK0081.SVC_KEI_NO 
    AND 
        KK0241.KAISEN_UCWK_USE_STAYMD =
                (SELECT MAX(KK0241_MAX.KAISEN_UCWK_USE_STAYMD)  AS KK0241_MAX
                        FROM 
                            KK_T_KAISEN_TG_SVKEI KK0241_MAX
                        WHERE 
                            KK0241.SVC_KEI_NO = KK0241_MAX.SVC_KEI_NO 
                        AND
                            KK0241_MAX.KAISEN_UCWK_USE_STAYMD <= ? 
                        AND
                            KK0241_MAX.MK_FLG = '0')
    INNER JOIN
        KK_T_SVKEI_KAISEN_UW KK0251
    ON 
        KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO 
    AND 
        KK0251.GENE_ADD_DTM = 
                (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
                        FROM 
                            KK_T_SVKEI_KAISEN_UW KK0251_GENE
                        WHERE 
                            KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
                        AND 
                            KK0251_GENE.MK_FLG = '0')
WHERE
    KK0161.SVC_KEI_UCWK_NO = ? 
AND 
    KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM =
            (SELECT
                MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX
                FROM
                    KK_T_SVC_KEI_UCWK KK0161_GENE
                WHERE
                    KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
                AND 
                    KK0161_GENE.RSV_APLY_YMD <= ?
                AND 
                    KK0161_GENE.RSV_APLY_CD = '2'
                AND 
                KK0161_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_031)
#--カレントレコードの世代登録年月日時分秒と関電営業所コードを抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_031

SELECT  KK0251.GENE_ADD_DTM,
        KK0251.KEPCO_BUSIOFFICE_CD
FROM   KK_T_SVKEI_KAISEN_UW KK0251
WHERE  KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND    KK0251.GENE_ADD_DTM = (
  SELECT  MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_GENE_MAX
  FROM    KK_T_SVKEI_KAISEN_UW KK0251_GENE
  WHERE   KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
  AND     KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_032)
#--サービス契約番号で有効な回線対象サービス契約
#--を取得し、回線対象サービス契約の情報を取得
#-----------------------------------------------------------

--KEY:KK_SELECT_032

SELECT
    KK0251.SVC_KEI_KAISEN_UCWK_NO
    ,KK0251.GENE_ADD_DTM
    ,KK0251.TEL_BAS_HOST_ID
    ,KK0251.TEL_VLAN_ID
    ,KK0251.VLAN_ID_FIX_FLG
    ,KK0251.TEL_VLAN_ID_TRKM_YMD
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
    INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241
    ON  KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
    AND KK0241.SVC_KEI_NO = ?
	AND KK0241.KAISEN_UCWK_USE_STAYMD <= ?
	AND (KK0241.SVC_KEI_NO, KK0241.KAISEN_UCWK_USE_ENDYMD) = 
	        (SELECT KK0241_01.SVC_KEI_NO, MAX(KK0241_01.KAISEN_UCWK_USE_ENDYMD)
	         FROM   KK_T_KAISEN_TG_SVKEI KK0241_01
	         WHERE  KK0241_01.SVC_KEI_NO = KK0241.SVC_KEI_NO
	         AND    KK0241_01.KAISEN_UCWK_USE_STAYMD <= ?
	         AND    KK0241_01.MK_FLG = '0'
	         GROUP BY KK0241_01.SVC_KEI_NO)
	AND  KK0241.MK_FLG = '0'
WHERE
    (KK0251.SVC_KEI_KAISEN_UCWK_NO, KK0251.GENE_ADD_DTM) =
        (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
         FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
         WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
         AND    KK0251_GENE.MK_FLG = '0'
         GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO)

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_034)
#--【保存期間経過データ物理削除】
#--サービス契約番号に関連する申込明細番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_034

SELECT
 UW_KAISEN.SVC_KEI_KAISEN_UCWK_NO,
 UW_KAISEN.MSKM_DTL_NO
FROM
 KK_T_SVKEI_KAISEN_UW UW_KAISEN
INNER JOIN 
 KK_T_KAISEN_TG_SVKEI SVKEI_KAISEN
ON
 UW_KAISEN.SVC_KEI_KAISEN_UCWK_NO = SVKEI_KAISEN.SVC_KEI_KAISEN_UCWK_NO
WHERE
 SVKEI_KAISEN.SVC_KEI_NO = ?
AND UW_KAISEN.MK_FLG    = '0'
AND SVKEI_KAISEN.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_035)
#--指定したサービス契約番号に紐付くサービス契約回線内訳情報を取得する。
#--回線対象サービス契約は、回線内訳使用開始年月日が運用日以前のMAXデータを対象とすることで一意とする。
#-----------------------------------------------------------

--KEY:KK_SELECT_035

SELECT
    KK0251.SVC_KEI_KAISEN_UCWK_NO,
    KK0251.GENE_ADD_DTM AS KK0251_GENE_ADD_DTM,
    KK0251.KAISEN_PLACE_TELNO,
    KK0251.UPD_UNYO_YMD,
    KK0241.UPD_UNYO_YMD AS KK0241_UPD_UNYO_YMD
FROM
    KK_T_KAISEN_TG_SVKEI KK0241
INNER JOIN
    KK_T_SVKEI_KAISEN_UW KK0251
ON
    KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
WHERE
    KK0251.GENE_ADD_DTM =
         (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
          FROM  KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
          AND    KK0251_GENE.MK_FLG = '0')
AND  KK0241.SVC_KEI_NO = ?
AND  KK0241.KAISEN_UCWK_USE_STAYMD <= ?
AND  KK0241.KAISEN_UCWK_USE_ENDYMD >= ?
AND  KK0241.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_UPDATE_001)
#--サービス契約回線内訳番号を条件に使用終了年月日を更新する。
#-----------------------------------------------------------

--KEY:KK_UPDATE_001

UPDATE
    KK_T_SVKEI_KAISEN_UW KK0251
SET
    KK0251.SVC_KEI_KAISEN_UCWK_STAT = '210',
    KK0251.USE_ENDYMD = ?,
    KK0251.IDO_DIV = '00020',
    KK0251.UPD_DTM = 'UPD_DTM',
    KK0251.UPD_UNYO_YMD = 'UPD_UNYO',
    KK0251.UPD_TRN_ID = 'UPD_TRN',
    KK0251.UPD_OPEACNT = 'UPD_OPE'
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND
    KK0251.GENE_ADD_DTM = ?
AND
    KK0251.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_UPDATE_002)
#--サービス契約回線内訳番号を条件に使用開始年月日を更新する。
#-----------------------------------------------------------

--KEY:KK_UPDATE_002

UPDATE
    KK_T_SVKEI_KAISEN_UW KK0251
SET
    KK0251.SVC_KEI_KAISEN_UCWK_STAT = '100',
    KK0251.USE_STAYMD = ?,
    KK0251.USE_ENDYMD = '20991231',
    KK0251.IDO_DIV = '00020',
    KK0251.UPD_DTM = 'UPD_DTM',
    KK0251.UPD_UNYO_YMD = 'UPD_UNYO',
    KK0251.UPD_TRN_ID = 'UPD_TRN',
    KK0251.UPD_OPEACNT = 'UPD_OPE'
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND
    KK0251.GENE_ADD_DTM = ?
AND
    KK0251.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_UPDATE_003)
#--郵便番号から関電営業所コードを取得してカレントを更新
#-----------------------------------------------------------

--KEY:KK_UPDATE_003

UPDATE KK_T_SVKEI_KAISEN_UW KK0251
SET    KK0251.KEPCO_BUSIOFFICE_CD = (
  SELECT  KK2321.KEPCO_BUSIOFFICE_CD AS KK2321_CD
  FROM    KK_M_PCDB_KPCOFC_KHO KK2321
  WHERE   KK2321.PCD = ?
  AND     KK2321.PCDB_KPCOFC_KHO_TSTAYMD <= ?
  AND     KK2321.PCDB_KPCOFC_KHO_TENDYMD > ?
  AND     KK2321.MK_FLG = '0'
  AND     ROWNUM <= 1 ),
       KK0251.UPD_DTM = ?,
       KK0251.UPD_OPEACNT = ?,
       KK0251.UPD_UNYO_YMD = ?,
       KK0251.UPD_TRN_ID = ?
WHERE  KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND    KK0251.GENE_ADD_DTM = ?

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-CR_SELECT_001)
#--サービス契約回線内訳番号から回線場所情報を取得
#--・ホットボイス抽出
#-----------------------------------------------------------

--KEY:CR_SELECT_001

SELECT
  SKU.KAISEN_PLACE_PCD AS KAISEN_PLACE_PCD,
  SKU.KAISEN_PLACE_STATE_NM AS KAISEN_PLACE_STATE_NM,
  SKU.KAISEN_PLACE_CITY_NM AS KAISEN_PLACE_CITY_NM
FROM
  KK_T_SVKEI_KAISEN_UW SKU
WHERE
   SKU.SVC_KEI_KAISEN_UCWK_NO = ?
   AND SKU.GENE_ADD_DTM =
       (
         SELECT 
           MAX(SKU_GENE.GENE_ADD_DTM) AS SKU_MAX
         FROM
           KK_T_SVKEI_KAISEN_UW SKU_GENE
         WHERE
           SKU_GENE.SVC_KEI_KAISEN_UCWK_NO = SKU.SVC_KEI_KAISEN_UCWK_NO
           AND SKU_GENE.MK_FLG = '0'
       )

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_INSERT_001)
#--KKPRC33704
#--カレントデータを元に履歴登録する。
#-----------------------------------------------------------

--KEY:KK_INSERT_001

INSERT INTO KK_T_SVKEI_KAISEN_UW 
(SVC_KEI_KAISEN_UCWK_NO
,GENE_ADD_DTM
,SVC_KEI_KAISEN_UCWK_STAT
,MSKM_DTL_NO
,KISN_PLC_AD_MI_FIX_FLG
,KISN_PLC_AD_MI_FIX_RLS_YMD
,KAISEN_PLC_KSH_AD_SAI_FLG
,CUST_SKK_AD_MI_FIX_FLG
,CUST_SOS_USE_UM
,KAISEN_CD
,MANSION_BUKKEN_NO
,KAISEN_PLACE_TELNO
,KAISEN_PLC_KANA
,KAISEN_PLACE_AD_CD
,KAISEN_PLACE_PCD
,KAISEN_PLACE_STATE_NM
,KAISEN_PLACE_CITY_NM
,KAISEN_PLACE_OAZTSU_NM
,KAISEN_PLACE_AZCHO_NM
,KAISEN_PLACE_BNCHIGO
,KAISEN_PLACE_ADRTTM
,KAISEN_PLACE_ADRRM
,KAISEN_PLACE_NO
,KISN_PLC_KKK_SEIRI_CHU_FLG
,KISN_PLC_HSK_CD_1
,KISN_PLC_HSK_CD_2
,KISN_PLC_HSK_MEMO
,KISN_PLC_AD_FIX_AF_CHGE_KH
,USE_STAYMD
,AD_FORM_CD
,SHUNKO_RSV_YMD
,EPOWER_SODEN_RSV_YMD
,NYUKYO_RSV_YMD
,HIKIWATASHI_RSV_YMD
,HIKKOSHI_RSV_YMD
,XZAHYO
,YZAHYO
,LGTD
,LTTD
,ZAHYO_HOSEI_UM
,TV_KOBET_TAIO_CD
,TV_ANTENNA_UM
,DIRECTION_CD_1
,DIRECTION_CD_2
,KODATE_KCKU_FLR_CNT_CD
,SHUGJ_NYUKYO_FLR_CNT_CD
,PON_SBT_CD
,LOOPBACK_TEST_FIN_YMD
,KAISEN_TK_COMP_CD
,VDSL_USE_TEL_KISN_SBT_CD
,VDSL_USE_DKSN_SWCH_RSV_YMD
,VDSL_USE_TG_DBKSN_SBT_CD
,VDSL_USE_TG_TELNO
,IDO_DIV
,HIKKOSHI_YMD
,GAITO_AREA_SVC_STAYMD
,TEKKYO_KOJI_KIBO_YMD
,USE_ENDYMD
,KITEN_HDSHO_CD
,SHYAKK_CD
,SVC_CANCEL_YMD
,SVC_CANCEL_RSN_CD
,SVKEI_KISN_UW_CNCL_CL_YMD
,KEPCO_BUSIOFFICE_CD
,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
) VALUES 
(?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,'',''
,'0','','','','','','')
#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-WC_SELECT_001)
#--郵便番号検索
#-----------------------------------------------------------

--KEY:WC_SELECT_001

SELECT KK0251.KAISEN_PLACE_PCD
FROM
KK_T_SVKEI_KAISEN_UW KK0251,
KK_T_KAISEN_TG_SVKEI KK0241
WHERE
    KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
AND KK0241.SVC_KEI_NO = ?
AND KK0241.MK_FLG = '0'
AND KK0251.MK_FLG = '0'
AND KK0251.GENE_ADD_DTM = 
(
    SELECT MAX(KK0251.GENE_ADD_DTM) 
    FROM 
    KK_T_SVKEI_KAISEN_UW KK0251,
    KK_T_KAISEN_TG_SVKEI KK0241
    WHERE
        KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
    AND KK0241.SVC_KEI_NO = ?
    AND KK0241.MK_FLG = '0'
    AND KK0251.MK_FLG = '0'
)

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_033)
#--KKPRC44501
#--場所番号と住所未確定フラグを取得
#-----------------------------------------------------------

--KEY:KK_SELECT_033

SELECT 
    KK0241.SVC_KEI_NO,
    KK0251.KAISEN_PLACE_NO,
    KK0251.KISN_PLC_AD_MI_FIX_FLG
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
    INNER JOIN 
        KK_T_KAISEN_TG_SVKEI KK0241
    ON
        KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
        AND KK0241.SVC_KEI_NO = ?
        AND KK0241.KAISEN_UCWK_USE_STAYMD <= ?
        AND KK0241.KAISEN_UCWK_USE_ENDYMD >= ? 
        AND KK0241.MK_FLG = '0'
WHERE    
        KK0251.GENE_ADD_DTM =
         (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
          FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
          AND    KK0251_GENE.MK_FLG = '0')

#-


#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_036)
#--【変更住所データ取得】
#--サービス契約番号、SYSYID、回線場所住所コードを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_036

SELECT
    KK0081.SVC_KEI_NO,
    KK0081.SYSID,
    KK0251.SVC_KEI_KAISEN_UCWK_NO,
    KK0251.KAISEN_PLACE_AD_CD,
    KK0251.KAISEN_PLACE_PCD,
    KK0251.KAISEN_PLACE_STATE_NM,
    KK0251.KAISEN_PLACE_CITY_NM,
    KK0251.KAISEN_PLACE_OAZTSU_NM,
    KK0251.KAISEN_PLACE_AZCHO_NM,
    KK0251.KAISEN_PLACE_BNCHIGO,
    KK2661.AD_CD,
    KK2661.PCD,
    KK2661.STATE_NM,
    KK2661.CITY_NM,
    KK2661.OAZTSU_NM,
    KK2661.AZCHO_NM,
    KK2661.AD_SHUSEI_CD,
    KK2661.NEW_AD_CD,
    KK2661.NEW_PCD,
    KK2661.NEW_STATE_NM,
    KK2661.NEW_CITY_NM,
    KK2661.NEW_OAZTSU_NM,
    KK2661.NEW_AZCHO_NM,
    KK2661.AD_CHG_SBT_CD
FROM
  KK_T_SVC_KEI KK0081
  INNER JOIN
    KK_T_KAISEN_TG_SVKEI KK0241
  ON
    KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO
    AND KK0241.KAISEN_UCWK_USE_STAYMD =
    (SELECT
      MAX(KK0241_MAX.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAXYMD
    FROM
      KK_T_KAISEN_TG_SVKEI KK0241_MAX
    WHERE
      KK0241.SVC_KEI_NO = KK0241_MAX.SVC_KEI_NO AND
      KK0241_MAX.MK_FLG = '0')
  INNER JOIN
    KK_T_SVKEI_KAISEN_UW KK0251
  ON
    KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
    AND KK0251.GENE_ADD_DTM =
    (SELECT
      MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
    FROM
      KK_T_SVKEI_KAISEN_UW KK0251_GENE
    WHERE
      KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
      AND KK0251_GENE.MK_FLG = '0')
 INNER JOIN
  KK_T_CHGTGAD_CD_WK KK2661
 ON
  KK0251.KAISEN_PLACE_AD_CD = KK2661.AD_CD
  AND KK2661.CHG_AD_TRN_STAT_CD = '0'
WHERE
  KK0251.KAISEN_PLACE_AD_CD = ?
  AND KK0081.GENE_ADD_DTM =
  (SELECT
    MAX(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_CD = '2'
    AND KK0081_GENE.MK_FLG = '0')
  AND KK0081.MK_FLG = '0'
  AND KK2661.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_037)
#--【廃止住所データ取得】
#--サービス契約番号、SYSYID、回線場所住所コードを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_037

SELECT
  KK0081.SVC_KEI_NO,
  KK0081.SYSID,
  KK0251.KAISEN_PLACE_AD_CD
FROM
  KK_T_SVC_KEI KK0081
  INNER JOIN
    KK_T_KAISEN_TG_SVKEI KK0241
  ON
    KK0081.SVC_KEI_NO = KK0241.SVC_KEI_NO
    AND KK0241.KAISEN_UCWK_USE_STAYMD =
    (SELECT
      MAX(KK0241_MAX.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAXYMD
    FROM
      KK_T_KAISEN_TG_SVKEI KK0241_MAX
    WHERE
      KK0241.SVC_KEI_NO = KK0241_MAX.SVC_KEI_NO AND
      KK0241_MAX.MK_FLG = '0')
  INNER JOIN
    KK_T_SVKEI_KAISEN_UW KK0251
  ON
    KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
    AND KK0251.GENE_ADD_DTM =
    (SELECT
      MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
    FROM
      KK_T_SVKEI_KAISEN_UW KK0251_GENE
    WHERE
      KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
      AND KK0251_GENE.MK_FLG = '0')
    AND KK0251.SVC_KEI_KAISEN_UCWK_STAT != '920'
    AND KK0251.MK_FLG = '0'
WHERE
  KK0251.KAISEN_PLACE_AD_CD = ?
  AND KK0081.GENE_ADD_DTM =
  (SELECT
    MAX(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_CD = '2'
    AND KK0081_GENE.MK_FLG = '0')
  AND KK0081.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_038)
#--地図情報差分抽出--サービス契約回線内訳
#--対象日付の登録・更新のあったサービス契約回線内訳を抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_038

SELECT
    KK0251.XZAHYO,
    KK0251.YZAHYO,
    KK0251.LGTD,
    KK0251.LTTD,
    KK0081.SYSID,
    KK0081.SVC_KEI_NO
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
    INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241
    ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
    AND KK0241.MK_FLG = '0'
    INNER JOIN KK_T_SVC_KEI KK0081
    ON KK0081.SVC_KEI_NO = KK0241.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 CK_T_CUST CK0011
    ON CK0011.SYSID = KK0081.SYSID
    AND CK0011.RSV_APLY_YMD || CK0011.GENE_ADD_DTM =
        (SELECT MAX(CK0011_GENE.RSV_APLY_YMD || CK0011_GENE.GENE_ADD_DTM) AS CK0011_MAX
         FROM   CK_T_CUST CK0011_GENE
         WHERE  CK0011_GENE.SYSID = CK0011.SYSID
         AND    CK0011_GENE.RSV_APLY_YMD <= ?
         AND    CK0011_GENE.RSV_APLY_CD = '2'
         AND    CK0011_GENE.MK_FLG = '0')
WHERE
    KK0251.GENE_ADD_DTM =
        (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
         FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
         WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
         AND    KK0251_GENE.MK_FLG = '0')
AND (KK0251.ADD_UNYO_YMD = ? OR KK0251.UPD_UNYO_YMD = ?)
AND KK0081.PRC_GRP_CD IN ('02', '03', '04', '10', '11', '12', '13')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_039)
#--運用日時点で有効なサービス契約回線内訳の入居予定日を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_039

SELECT
    KK0241.SVC_KEI_NO,
    KK0251.NYUKYO_RSV_YMD
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN 
    KK_T_KAISEN_TG_SVKEI KK0241
ON  KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
INNER JOIN 
    KK_T_SVC_KEI KK0081
ON  KK0241.SVC_KEI_NO = KK0081.SVC_KEI_NO
WHERE    
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND KK0241.KAISEN_UCWK_USE_STAYMD <= ?
AND KK0241.KAISEN_UCWK_USE_ENDYMD >= ?
AND KK0241.MK_FLG = '0'
AND KK0251.GENE_ADD_DTM =
     (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
      FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
      WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
      AND    KK0251_GENE.MK_FLG = '0')
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')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_040)
#--加入御礼管理データ抽出
#--サービス契約回線内訳番号に紐づく世代が一番新しいレコード順に抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_040

SELECT
   KK0251.KAISEN_PLACE_PCD
  ,KK0251.KAISEN_PLACE_STATE_NM
  ,KK0251.KAISEN_PLACE_CITY_NM
  ,KK0251.KAISEN_PLACE_OAZTSU_NM
  ,KK0251.KAISEN_PLACE_AZCHO_NM
  ,KK0251.KAISEN_PLACE_BNCHIGO
  ,KK0251.KAISEN_PLACE_ADRTTM
  ,KK0251.KAISEN_PLACE_ADRRM
  ,KK0251.KAISEN_PLACE_TELNO
  ,KK0251.KAISEN_CD
FROM  KK_T_SVKEI_KAISEN_UW KK0251
WHERE KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
ORDER BY KK0251.GENE_ADD_DTM DESC

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_041)
#--番ポなし番号事前通知データ抽出
#--サービス契約回線内訳番号に紐づく世代が一番新しいレコード順に抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_041

SELECT
   KK0251.KAISEN_PLACE_PCD
  ,KK0251.KAISEN_PLACE_STATE_NM
  ,KK0251.KAISEN_PLACE_CITY_NM
  ,KK0251.KAISEN_PLACE_OAZTSU_NM
  ,KK0251.KAISEN_PLACE_AZCHO_NM
  ,KK0251.KAISEN_PLACE_BNCHIGO
  ,KK0251.KAISEN_PLACE_ADRTTM
  ,KK0251.KAISEN_PLACE_ADRRM
  ,KK0251.KAISEN_PLACE_TELNO
  ,KK0251.KISN_PLC_AD_MI_FIX_FLG
FROM  KK_T_SVKEI_KAISEN_UW KK0251
WHERE KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
ORDER BY KK0251.GENE_ADD_DTM DESC

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_043)
#--番ポなし番号事前通知データ抽出
#--サービス契約番号に紐づくカレントレコードを取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_043

SELECT
   KK0251.KAISEN_PLACE_PCD
  ,KK0251.KAISEN_PLACE_STATE_NM
  ,KK0251.KAISEN_PLACE_CITY_NM
  ,KK0251.KAISEN_PLACE_OAZTSU_NM
  ,KK0251.KAISEN_PLACE_AZCHO_NM
  ,KK0251.KAISEN_PLACE_BNCHIGO
  ,KK0251.KAISEN_PLACE_ADRTTM
  ,KK0251.KAISEN_PLACE_ADRRM
  ,KK0251.KAISEN_PLACE_TELNO
  ,KK0251.KISN_PLC_AD_MI_FIX_FLG
FROM  KK_T_SVKEI_KAISEN_UW KK0251
  INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241
  ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
  AND KK0241.SVC_KEI_NO = ?
  AND KK0241.KAISEN_UCWK_USE_STAYMD <= ?
  AND KK0241.KAISEN_UCWK_USE_ENDYMD > ?
WHERE KK0251.GENE_ADD_DTM = (
  SELECT MAX(GENE_ADD_DTM)
  FROM   KK_T_SVKEI_KAISEN_UW KK0251_G
  WHERE  KK0251_G.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
  AND    KK0251_G.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_045)
#--KKPRC35401にて使用
#--サービス契約内訳番号でサービス契約回線内訳TBLから転居先回線場所郵便番号を取得します。
#-----------------------------------------------------------

--KEY:KK_SELECT_045

SELECT
    KK0251.KAISEN_PLACE_PCD
FROM
    KK_T_SVC_KEI_UCWK KK0161
    INNER JOIN
        KK_T_SVC_KEI KK0081
    ON
        KK0081.SVC_KEI_NO = KK0161.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_KAISEN_TG_SVKEI KK0241
    ON
        KK0241.SVC_KEI_NO = KK0081.SVC_KEI_NO 
    AND 
        KK0241.KAISEN_UCWK_USE_STAYMD =
                (SELECT MAX(KK0241_MAX.KAISEN_UCWK_USE_STAYMD)  AS KK0241_MAX
                        FROM 
                            KK_T_KAISEN_TG_SVKEI KK0241_MAX
                        WHERE 
                            KK0241.SVC_KEI_NO = KK0241_MAX.SVC_KEI_NO 
                        AND
                            KK0241_MAX.KAISEN_UCWK_USE_STAYMD = '20991231' 
                        AND
                            KK0241_MAX.MK_FLG = '0')
    INNER JOIN
        KK_T_SVKEI_KAISEN_UW KK0251
    ON 
        KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO 
    AND 
        KK0251.GENE_ADD_DTM = 
                (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
                        FROM 
                            KK_T_SVKEI_KAISEN_UW KK0251_GENE
                        WHERE 
                            KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
                        AND 
                            KK0251_GENE.MK_FLG = '0')
WHERE
    KK0161.SVC_KEI_UCWK_NO = ? 
AND 
    KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM =
            (SELECT
                MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) AS KK0161_MAX
                FROM
                    KK_T_SVC_KEI_UCWK KK0161_GENE
                WHERE
                    KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
                AND 
                    KK0161_GENE.RSV_APLY_YMD <= ?
                AND 
                    KK0161_GENE.RSV_APLY_CD = '2'
                AND 
                KK0161_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_046)
#--KKPRC00909
#--工事対象の回線より回線コードを取得
#-----------------------------------------------------------

--KEY:KK_SELECT_046

SELECT
    KK0251.SVC_KEI_KAISEN_UCWK_NO,
    KK0251.GENE_ADD_DTM,
    KK0251.SVC_KEI_KAISEN_UCWK_STAT,
    KK0251.KAISEN_CD,
    KK0251.TEL_VLAN_ID_TRKM_YMD,
    KK0251.KAISEN_TK_COMP_CD
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN
    KU_T_SVKEI_KOJIAK KU0081
ON
    KU0081.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
    KU0081.SVC_KEI_NO = ? AND
    KU0081.KOJIAK_NO = ? AND
    KU0081.MK_FLG = '0'
WHERE
    KK0251.GENE_ADD_DTM = (SELECT
                               MAX(KK0251_MAX.GENE_ADD_DTM)
                             FROM
                               KK_T_SVKEI_KAISEN_UW KK0251_MAX
                            WHERE
                               KK0251_MAX.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
                               AND KK0251_MAX.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_INSERT_002)
#-----------------------------------------------------------

--KEY:KK_INSERT_002

INSERT INTO KK_T_SVKEI_KAISEN_UW 
	(SVC_KEI_KAISEN_UCWK_NO,
	GENE_ADD_DTM,
	SVC_KEI_KAISEN_UCWK_STAT,
	MSKM_DTL_NO,
	KISN_PLC_AD_MI_FIX_FLG,
	KISN_PLC_AD_MI_FIX_RLS_YMD,
	KAISEN_PLC_KSH_AD_SAI_FLG,
	CUST_SKK_AD_MI_FIX_FLG,
	CUST_SOS_USE_UM,
	KAISEN_CD,
	MANSION_BUKKEN_NO,
	KAISEN_PLACE_TELNO,
	KAISEN_PLC_KANA,
	KAISEN_PLACE_AD_CD,
	KAISEN_PLACE_PCD,
	KAISEN_PLACE_STATE_NM,
	KAISEN_PLACE_CITY_NM,
	KAISEN_PLACE_OAZTSU_NM,
	KAISEN_PLACE_AZCHO_NM,
	KAISEN_PLACE_BNCHIGO,
	KAISEN_PLACE_ADRTTM,
	KAISEN_PLACE_ADRRM,
	SKS_YO_KISN_PLACE_AD_RM_NO,
	KAISEN_PLACE_NO,
	KISN_PLC_KKK_SEIRI_CHU_FLG,
	KISN_PLC_HSK_CD_1,
	KISN_PLC_HSK_CD_2,
	KISN_PLC_HSK_MEMO,
	USE_STAYMD,
	AD_FORM_CD,
	SHUNKO_RSV_YMD,
	EPOWER_SODEN_RSV_YMD,
	NYUKYO_RSV_YMD,
	HIKIWATASHI_RSV_YMD,
	HIKKOSHI_RSV_YMD,
	SKS_SHS_AD_CHG_UM,
	SKS_SHS_AD_CHG_KIBO_YMD,
	KSHAD_CHG_UM,
	KSHAD_CHG_KIBO_YMD,
	XZAHYO,
	YZAHYO,
	LGTD,
	LTTD,
	ZAHYO_HOSEI_UM,
	TV_KOBET_TAIO_CD,
	TV_ANTENNA_UM,
	DIRECTION_CD_1,
	DIRECTION_CD_2,
	KODATE_KCKU_FLR_CNT_CD,
	SHUGJ_NYUKYO_FLR_CNT_CD,
	PON_SBT_CD,
	LOOPBACK_TEST_FIN_YMD,
	KAISEN_TK_COMP_CD,
	VDSL_USE_TEL_KISN_SBT_CD,
	VDSL_USE_DKSN_SWCH_RSV_YMD,
	VDSL_USE_TG_DBKSN_SBT_CD,
	VDSL_USE_TG_TELNO,
	TEL_BAS_HOST_ID,
	TEL_VLAN_ID,
	VLAN_ID_FIX_FLG,
	TEL_VLAN_ID_TRKM_YMD,
	IDO_DIV,
	HIKKOSHI_YMD,
	GAITO_AREA_SVC_STAYMD,
	TEKKYO_KOJI_KIBO_YMD,
	USE_ENDYMD,
	KITEN_HDSHO_CD,
	SHYAKK_CD,
	SVC_CANCEL_YMD,
	SVC_CANCEL_RSN_CD,
	SVKEI_KISN_UW_CNCL_CL_YMD,
	KEPCO_BUSIOFFICE_CD,
	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,
	KAISEN_PLACE_TOU_INFO,
	ROZETT_STC_STAT_CD,
	CONSNT_STC_NYO_CD,
	TNMT_STC_FLR_CD,
	KISN_TD_SETPLC_1_CD,
	KISN_TD_SETPLC_2_CD,
	KISN_TD_STC_KASHO_CD,
	KISN_TD_SETPLC_1_OTR,
	KISN_TD_SETPLC_2_OTR,
	KISN_TD_STC_KASHO_OTR,
	TANMT_STC_MEN_CD,
	KAISEN_PLACE_RENBAN,
	HGW_KKN_FFG
	
) 
SELECT
	KK0251.SVC_KEI_KAISEN_UCWK_NO,
	?,
	KK0251.SVC_KEI_KAISEN_UCWK_STAT,
	KK0251.MSKM_DTL_NO,
	KK0251.KISN_PLC_AD_MI_FIX_FLG,
	KK0251.KISN_PLC_AD_MI_FIX_RLS_YMD,
	KK0251.KAISEN_PLC_KSH_AD_SAI_FLG,
	KK0251.CUST_SKK_AD_MI_FIX_FLG,
	KK0251.CUST_SOS_USE_UM,
	KK0251.KAISEN_CD,
	KK0251.MANSION_BUKKEN_NO,
	KK0251.KAISEN_PLACE_TELNO,
	KK0251.KAISEN_PLC_KANA,
	KK0251.KAISEN_PLACE_AD_CD,
	KK0251.KAISEN_PLACE_PCD,
	KK0251.KAISEN_PLACE_STATE_NM,
	KK0251.KAISEN_PLACE_CITY_NM,
	KK0251.KAISEN_PLACE_OAZTSU_NM,
	KK0251.KAISEN_PLACE_AZCHO_NM,
	KK0251.KAISEN_PLACE_BNCHIGO,
	KK0251.KAISEN_PLACE_ADRTTM,
	KK0251.KAISEN_PLACE_ADRRM,
	KK0251.SKS_YO_KISN_PLACE_AD_RM_NO,
	KK0251.KAISEN_PLACE_NO,
	KK0251.KISN_PLC_KKK_SEIRI_CHU_FLG,
	KK0251.KISN_PLC_HSK_CD_1,
	KK0251.KISN_PLC_HSK_CD_2,
	KK0251.KISN_PLC_HSK_MEMO,
	KK0251.USE_STAYMD,
	KK0251.AD_FORM_CD,
	KK0251.SHUNKO_RSV_YMD,
	KK0251.EPOWER_SODEN_RSV_YMD,
	KK0251.NYUKYO_RSV_YMD,
	KK0251.HIKIWATASHI_RSV_YMD,
	KK0251.HIKKOSHI_RSV_YMD,
	KK0251.SKS_SHS_AD_CHG_UM,
	KK0251.SKS_SHS_AD_CHG_KIBO_YMD,
	KK0251.KSHAD_CHG_UM,
	KK0251.KSHAD_CHG_KIBO_YMD,
	KK0251.XZAHYO,
	KK0251.YZAHYO,
	KK0251.LGTD,
	KK0251.LTTD,
	KK0251.ZAHYO_HOSEI_UM,
	KK0251.TV_KOBET_TAIO_CD,
	KK0251.TV_ANTENNA_UM,
	KK0251.DIRECTION_CD_1,
	KK0251.DIRECTION_CD_2,
	KK0251.KODATE_KCKU_FLR_CNT_CD,
	KK0251.SHUGJ_NYUKYO_FLR_CNT_CD,
	KK0251.PON_SBT_CD,
	KK0251.LOOPBACK_TEST_FIN_YMD,
	KK0251.KAISEN_TK_COMP_CD,
	KK0251.VDSL_USE_TEL_KISN_SBT_CD,
	KK0251.VDSL_USE_DKSN_SWCH_RSV_YMD,
	KK0251.VDSL_USE_TG_DBKSN_SBT_CD,
	KK0251.VDSL_USE_TG_TELNO,
	KK0251.TEL_BAS_HOST_ID,
	KK0251.TEL_VLAN_ID,
	KK0251.VLAN_ID_FIX_FLG,
	KK0251.TEL_VLAN_ID_TRKM_YMD,
	KK0251.IDO_DIV,
	KK0251.HIKKOSHI_YMD,
	KK0251.GAITO_AREA_SVC_STAYMD,
	KK0251.TEKKYO_KOJI_KIBO_YMD,
	KK0251.USE_ENDYMD,
	KK0251.KITEN_HDSHO_CD,
	KK0251.SHYAKK_CD,
	KK0251.SVC_CANCEL_YMD,
	KK0251.SVC_CANCEL_RSN_CD,
	KK0251.SVKEI_KISN_UW_CNCL_CL_YMD,
	KK0251.KEPCO_BUSIOFFICE_CD,
	KK0251.ADD_DTM,
	KK0251.ADD_OPEACNT,
	'UPD_DTM',
	'UPD_OPE',
	NULL,
	NULL,
	'0',
	KK0251.ADD_UNYO_YMD,
	KK0251.ADD_TRN_ID,
	'UPD_UNYO',
	'UPD_TRN',
	NULL,
	NULL,
	KK0251.KAISEN_PLACE_TOU_INFO,
	KK0251.ROZETT_STC_STAT_CD,
	KK0251.CONSNT_STC_NYO_CD,
	KK0251.TNMT_STC_FLR_CD,
	KK0251.KISN_TD_SETPLC_1_CD,
	KK0251.KISN_TD_SETPLC_2_CD,
	KK0251.KISN_TD_STC_KASHO_CD,
	KK0251.KISN_TD_SETPLC_1_OTR,
	KK0251.KISN_TD_SETPLC_2_OTR,
	KK0251.KISN_TD_STC_KASHO_OTR,
	KK0251.TANMT_STC_MEN_CD,
	KK0251.KAISEN_PLACE_RENBAN,
	KK0251.HGW_KKN_FFG
FROM KK_T_SVKEI_KAISEN_UW KK0251
WHERE KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND KK0251.GENE_ADD_DTM = (
    SELECT
        MAX(KK0251_GENE.GENE_ADD_DTM)
    FROM
        KK_T_SVKEI_KAISEN_UW KK0251_GENE
    WHERE
        KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
        AND KK0251_GENE.MK_FLG = '0')
AND KK0251.MK_FLG = '0'

#-

--KEY:KK_UPDATE_004

UPDATE
    KK_T_SVKEI_KAISEN_UW
SET
    KAISEN_PLACE_AD_CD = ? ,
    KAISEN_PLACE_PCD = ? ,
    KAISEN_PLACE_STATE_NM = ? ,
    KAISEN_PLACE_CITY_NM = ? ,
    KAISEN_PLACE_OAZTSU_NM = ? ,
    KAISEN_PLACE_AZCHO_NM = ?
WHERE
    SVC_KEI_KAISEN_UCWK_NO = ?
    AND GENE_ADD_DTM = ?

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_047)
#--KKPRC02202
#--サービス契約番号に紐付くサービス契約回線内訳番号を取得
#-----------------------------------------------------------

--KEY:KK_SELECT_047

SELECT 
    KK0251.SVC_KEI_KAISEN_UCWK_NO,
    KK0251.GENE_ADD_DTM
FROM 
KK_T_KAISEN_TG_SVKEI KK0241 
INNER JOIN 
    KK_T_SVKEI_KAISEN_UW KK0251 
ON 
     KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
     AND KK0251.SVC_KEI_KAISEN_UCWK_STAT NOT IN('910','920') 
     AND KK0251.GENE_ADD_DTM = 
     (SELECT 
         MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX 
     FROM 
         KK_T_SVKEI_KAISEN_UW KK0251_GENE 
     WHERE 
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO 
         AND KK0251_GENE.MK_FLG = '0') 
WHERE
    KK0241.SVC_KEI_NO = ? 
    AND KK0241.KAISEN_UCWK_USE_STAYMD = 
    (SELECT
       MAX(KK0241_MAX.KAISEN_UCWK_USE_STAYMD) AS KK0241_MAXYMD
     FROM
       KK_T_KAISEN_TG_SVKEI KK0241_MAX
     WHERE
       KK0241.SVC_KEI_NO = KK0241_MAX.SVC_KEI_NO AND
       KK0241_MAX.KAISEN_UCWK_USE_STAYMD <= ? AND
       KK0241_MAX.MK_FLG = '0') 
ORDER BY 
 KK0241.KAISEN_UCWK_USE_STAYMD 

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_048)
#--KKPRC51501_E-PON→GE-PON設備切替工事情報作成処理
#--KKPRC67101_ONU計画交換工事一括立ち上げ登録
#--サービス契約番号に紐付くサービス契約回線内訳番号を取得
#-----------------------------------------------------------

--KEY:KK_SELECT_048

SELECT KK0251.SVC_KEI_KAISEN_UCWK_NO
      ,KK0251.GENE_ADD_DTM
      ,KK0251.SVC_KEI_KAISEN_UCWK_STAT
      ,KK0251.MSKM_DTL_NO
      ,KK0251.KISN_PLC_AD_MI_FIX_FLG
      ,KK0251.KISN_PLC_AD_MI_FIX_RLS_YMD
      ,KK0251.KAISEN_PLC_KSH_AD_SAI_FLG
      ,KK0251.CUST_SOS_USE_UM
      ,KK0251.KAISEN_CD
      ,KK0251.MANSION_BUKKEN_NO
      ,KK0251.KAISEN_PLACE_TELNO
      ,KK0251.KAISEN_PLC_KANA
      ,KK0251.KAISEN_PLACE_AD_CD
      ,KK0251.KAISEN_PLACE_PCD
      ,KK0251.KAISEN_PLACE_STATE_NM
      ,KK0251.KAISEN_PLACE_CITY_NM
      ,KK0251.KAISEN_PLACE_OAZTSU_NM
      ,KK0251.KAISEN_PLACE_AZCHO_NM
      ,KK0251.KAISEN_PLACE_BNCHIGO
      ,KK0251.KAISEN_PLACE_ADRTTM
      ,KK0251.KAISEN_PLACE_ADRRM
      ,KK0251.KAISEN_PLACE_NO
      ,KK0251.KISN_PLC_KKK_SEIRI_CHU_FLG
      ,KK0251.KISN_PLC_HSK_CD_1
      ,KK0251.KISN_PLC_HSK_CD_2
      ,KK0251.KISN_PLC_HSK_MEMO
      ,NULL AS KISN_PLC_AD_FIX_FLG
      ,KK0251.USE_STAYMD
      ,KK0251.AD_FORM_CD
      ,KK0251.SHUNKO_RSV_YMD
      ,KK0251.EPOWER_SODEN_RSV_YMD
      ,KK0251.NYUKYO_RSV_YMD
      ,KK0251.HIKIWATASHI_RSV_YMD
      ,KK0251.HIKKOSHI_RSV_YMD
      ,KK0251.XZAHYO
      ,KK0251.YZAHYO
      ,KK0251.LGTD
      ,KK0251.LTTD
      ,KK0251.ZAHYO_HOSEI_UM
      ,KK0251.TV_KOBET_TAIO_CD
      ,KK0251.TV_ANTENNA_UM
      ,KK0251.DIRECTION_CD_1
      ,KK0251.DIRECTION_CD_2
      ,KK0251.KODATE_KCKU_FLR_CNT_CD
      ,KK0251.SHUGJ_NYUKYO_FLR_CNT_CD
      ,KK0251.PON_SBT_CD
      ,KK0251.LOOPBACK_TEST_FIN_YMD
      ,KK0251.KAISEN_TK_COMP_CD
      ,KK0251.VDSL_USE_TEL_KISN_SBT_CD
      ,KK0251.VDSL_USE_DKSN_SWCH_RSV_YMD
      ,KK0251.VDSL_USE_TG_DBKSN_SBT_CD
      ,KK0251.VDSL_USE_TG_TELNO
      ,KK0251.IDO_DIV
      ,KK0251.HIKKOSHI_YMD
      ,KK0251.GAITO_AREA_SVC_STAYMD
      ,KK0251.TEKKYO_KOJI_KIBO_YMD
      ,KK0251.USE_ENDYMD
      ,KK0251.KITEN_HDSHO_CD
      ,KK0251.SHYAKK_CD
      ,KK0251.SVC_CANCEL_YMD
      ,KK0251.SVC_CANCEL_RSN_CD
      ,KK0251.SVKEI_KISN_UW_CNCL_CL_YMD
      ,KK0251.KEPCO_BUSIOFFICE_CD
      ,KK0251.ADD_DTM
      ,KK0251.ADD_OPEACNT
      ,KK0251.UPD_DTM
      ,KK0251.UPD_OPEACNT
      ,KK0251.DEL_DTM
      ,KK0251.DEL_OPEACNT
      ,KK0251.MK_FLG
      ,KK0251.CUST_SKK_AD_MI_FIX_FLG
      ,KK0251.SKS_YO_KISN_PLACE_AD_RM_NO
      ,KK0251.TEL_BAS_HOST_ID
      ,KK0251.TEL_VLAN_ID
      ,KK0251.VLAN_ID_FIX_FLG
      ,KK0251.TEL_VLAN_ID_TRKM_YMD
      ,KK0251.SKS_SHS_AD_CHG_UM
      ,KK0251.SKS_SHS_AD_CHG_KIBO_YMD
      ,KK0251.KSHAD_CHG_UM
      ,KK0251.KSHAD_CHG_KIBO_YMD
      ,KK0251.HGW_KKN_FFG
FROM KK_T_KAISEN_TG_SVKEI KK0241
 INNER JOIN KK_T_SVKEI_KAISEN_UW KK0251
         ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
WHERE KK0241.SVC_KEI_NO = ?
  AND KK0241.KAISEN_UCWK_USE_STAYMD <= ?
  AND (KK0241.SVC_KEI_NO, KK0241.KAISEN_UCWK_USE_ENDYMD) = 
      (SELECT KK0241_MAX.SVC_KEI_NO, MAX(KK0241_MAX.KAISEN_UCWK_USE_ENDYMD) AS KAISEN_UCWK_USE_ENDYMD 
         FROM KK_T_KAISEN_TG_SVKEI KK0241_MAX 
        WHERE KK0241_MAX.SVC_KEI_NO = KK0241.SVC_KEI_NO 
          AND KK0241_MAX.KAISEN_UCWK_USE_STAYMD <= ?
          AND KK0241_MAX.MK_FLG = '0' 
        GROUP BY KK0241_MAX.SVC_KEI_NO) 
  AND (KK0251.SVC_KEI_KAISEN_UCWK_NO, KK0251.GENE_ADD_DTM) = 
         (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_GENE.GENE_ADD_DTM) 
          FROM KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
            AND KK0251_GENE.MK_FLG = '0'
            GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO)
ORDER BY
    KK0241.KAISEN_UCWK_USE_STAYMD

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_049)
#--KKPRC51601
#--サービス契約回線内訳のカレントデータを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_049

SELECT
    KK0251.KAISEN_PLACE_PCD,
    KK0251.KAISEN_PLACE_STATE_NM,
    KK0251.KAISEN_PLACE_CITY_NM,
    KK0251.KAISEN_PLACE_OAZTSU_NM,
    KK0251.KAISEN_PLACE_AZCHO_NM,
    KK0251.KAISEN_PLACE_BNCHIGO,
    KK0251.KAISEN_PLACE_ADRTTM,
    KK0251.KAISEN_PLACE_ADRRM
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ? 
AND KK0251.GENE_ADD_DTM =
    (SELECT
         MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
         KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
     AND KK0251_GENE.MK_FLG = '0')

#-

#------------------------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_050)
#--
#--SYSIDに紐付くモバイルのサービス契約回線内訳のカレントデータを取得する。
#------------------------------------------------------------------------

--KEY:KK_SELECT_050

SELECT 
    KK0251.SVC_KEI_KAISEN_UCWK_NO,
    KK0251.GENE_ADD_DTM,
    KK0251.SVC_KEI_KAISEN_UCWK_STAT,
    KK0251.MSKM_DTL_NO,
    KK0251.KISN_PLC_AD_MI_FIX_FLG,
    KK0251.KISN_PLC_AD_MI_FIX_RLS_YMD,
    KK0251.KAISEN_PLC_KSH_AD_SAI_FLG,
    KK0251.CUST_SKK_AD_MI_FIX_FLG,
    KK0251.CUST_SOS_USE_UM,
    KK0251.KAISEN_CD,
    KK0251.MANSION_BUKKEN_NO,
    KK0251.KAISEN_PLACE_TELNO,
    KK0251.KAISEN_PLC_KANA,
    KK0251.KAISEN_PLACE_AD_CD,
    KK0251.KAISEN_PLACE_PCD,
    KK0251.KAISEN_PLACE_STATE_NM,
    KK0251.KAISEN_PLACE_CITY_NM,
    KK0251.KAISEN_PLACE_OAZTSU_NM,
    KK0251.KAISEN_PLACE_AZCHO_NM,
    KK0251.KAISEN_PLACE_BNCHIGO,
    KK0251.KAISEN_PLACE_ADRTTM,
    KK0251.KAISEN_PLACE_ADRRM,
    KK0251.SKS_YO_KISN_PLACE_AD_RM_NO,
    KK0251.KAISEN_PLACE_NO,
    KK0251.KISN_PLC_KKK_SEIRI_CHU_FLG,
    KK0251.KISN_PLC_HSK_CD_1,
    KK0251.KISN_PLC_HSK_CD_2,
    KK0251.KISN_PLC_HSK_MEMO,
    KK0251.USE_STAYMD,
    KK0251.AD_FORM_CD,
    KK0251.SHUNKO_RSV_YMD,
    KK0251.EPOWER_SODEN_RSV_YMD,
    KK0251.NYUKYO_RSV_YMD,
    KK0251.HIKIWATASHI_RSV_YMD,
    KK0251.HIKKOSHI_RSV_YMD,
    KK0251.SKS_SHS_AD_CHG_UM,
    KK0251.SKS_SHS_AD_CHG_KIBO_YMD,
    KK0251.KSHAD_CHG_UM,
    KK0251.KSHAD_CHG_KIBO_YMD,
    KK0251.XZAHYO,
    KK0251.YZAHYO,
    KK0251.LGTD,
    KK0251.LTTD,
    KK0251.ZAHYO_HOSEI_UM,
    KK0251.TV_KOBET_TAIO_CD,
    KK0251.TV_ANTENNA_UM,
    KK0251.DIRECTION_CD_1,
    KK0251.DIRECTION_CD_2,
    KK0251.KODATE_KCKU_FLR_CNT_CD,
    KK0251.SHUGJ_NYUKYO_FLR_CNT_CD,
    KK0251.PON_SBT_CD,
    KK0251.LOOPBACK_TEST_FIN_YMD,
    KK0251.KAISEN_TK_COMP_CD,
    KK0251.VDSL_USE_TEL_KISN_SBT_CD,
    KK0251.VDSL_USE_DKSN_SWCH_RSV_YMD,
    KK0251.VDSL_USE_TG_DBKSN_SBT_CD,
    KK0251.VDSL_USE_TG_TELNO,
    KK0251.TEL_BAS_HOST_ID,
    KK0251.TEL_VLAN_ID,
    KK0251.VLAN_ID_FIX_FLG,
    KK0251.TEL_VLAN_ID_TRKM_YMD,
    KK0251.IDO_DIV,
    KK0251.HIKKOSHI_YMD,
    KK0251.GAITO_AREA_SVC_STAYMD,
    KK0251.TEKKYO_KOJI_KIBO_YMD,
    KK0251.USE_ENDYMD,
    KK0251.KITEN_HDSHO_CD,
    KK0251.SHYAKK_CD,
    KK0251.SVC_CANCEL_YMD,
    KK0251.SVC_CANCEL_RSN_CD,
    KK0251.SVKEI_KISN_UW_CNCL_CL_YMD,
    KK0251.KEPCO_BUSIOFFICE_CD,
    KK0251.ADD_DTM,
    KK0251.ADD_OPEACNT,
    KK0251.UPD_DTM,
    KK0251.UPD_OPEACNT,
    KK0251.DEL_DTM,
    KK0251.DEL_OPEACNT,
    KK0251.MK_FLG,
    KK0251.ADD_UNYO_YMD,
    KK0251.ADD_TRN_ID,
    KK0251.UPD_UNYO_YMD,
    KK0251.UPD_TRN_ID,
    KK0251.DEL_UNYO_YMD,
    KK0251.DEL_TRN_ID,
    KK0251.KAISEN_PLACE_TOU_INFO,
    KK0251.ROZETT_STC_STAT_CD,
    KK0251.CONSNT_STC_NYO_CD,
    KK0251.TNMT_STC_FLR_CD,
    KK0251.KISN_TD_SETPLC_1_CD,
    KK0251.KISN_TD_SETPLC_2_CD,
    KK0251.KISN_TD_STC_KASHO_CD,
    KK0251.KISN_TD_SETPLC_1_OTR,
    KK0251.KISN_TD_SETPLC_2_OTR,
    KK0251.KISN_TD_STC_KASHO_OTR,
    KK0251.TANMT_STC_MEN_CD,
    KK0251.KAISEN_PLACE_RENBAN,
    KK0251.HGW_KKN_FFG
FROM KK_T_SVC_KEI KK0081 
INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241 
        ON KK0241.SVC_KEI_NO = KK0081.SVC_KEI_NO 
INNER JOIN KK_T_SVKEI_KAISEN_UW KK0251
        ON KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
WHERE (KK0081.SVC_KEI_NO, KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) = 
      (SELECT KK0081_GENE.SVC_KEI_NO, MAX(KK0081_GENE.RSV_APLY_YMD || 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.RSV_APLY_YMD <= ?
       AND    KK0081_GENE.RSV_APLY_CD = '2' 
       AND    KK0081_GENE.MK_FLG = '0'
       GROUP BY KK0081_GENE.SVC_KEI_NO) 
AND (KK0241.SVC_KEI_NO, KK0241.KAISEN_UCWK_USE_ENDYMD) = 
    (SELECT KK0241_MAX.SVC_KEI_NO, MAX(KK0241_MAX.KAISEN_UCWK_USE_ENDYMD) AS KAISEN_UCWK_USE_ENDYMD 
       FROM KK_T_KAISEN_TG_SVKEI KK0241_MAX 
      WHERE KK0241_MAX.SVC_KEI_NO = KK0241.SVC_KEI_NO 
        AND KK0241_MAX.KAISEN_UCWK_USE_STAYMD <= ? 
        AND KK0241_MAX.MK_FLG = '0' 
      GROUP BY KK0241_MAX.SVC_KEI_NO) 
AND    KK0251.SVC_KEI_KAISEN_UCWK_STAT = '100' 
AND    KK0251.KAISEN_CD in('4001' , '4002' ,'4003')
AND   (KK0251.SVC_KEI_KAISEN_UCWK_NO, KK0251.GENE_ADD_DTM) =
      (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_GENE.GENE_ADD_DTM)
       FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
       WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
       AND    KK0251_GENE.MK_FLG = '0'
       GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO)
AND EXISTS (
            SELECT 1 
            FROM KK_T_SVC_KEI KK0081_01 
            INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241_01 
                    ON KK0241_01.SVC_KEI_NO = KK0081_01.SVC_KEI_NO 
            INNER JOIN KK_T_SVKEI_KAISEN_UW KK0251_01
                    ON KK0251_01.SVC_KEI_KAISEN_UCWK_NO = KK0241_01.SVC_KEI_KAISEN_UCWK_NO 
            WHERE  KK0081_01.SYSID = ? 
            AND    KK0251_01.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
            AND   (KK0081_01.SVC_KEI_NO, KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) = 
                  (SELECT KK0081_01_GENE.SVC_KEI_NO, MAX(KK0081_01_GENE.RSV_APLY_YMD || KK0081_01_GENE.GENE_ADD_DTM) 
                   FROM   KK_T_SVC_KEI KK0081_01_GENE 
                   WHERE  KK0081_01_GENE.SVC_KEI_NO = KK0081_01.SVC_KEI_NO 
                   AND    KK0081_01_GENE.RSV_APLY_YMD <= ?
                   AND    KK0081_01_GENE.RSV_APLY_CD = '2' 
                   AND    KK0081_01_GENE.MK_FLG = '0'
                   GROUP BY KK0081_01_GENE.SVC_KEI_NO) 
            AND    KK0241_01.KAISEN_UCWK_USE_STAYMD <= ?
            AND    (KK0241_01.SVC_KEI_NO, KK0241_01.KAISEN_UCWK_USE_ENDYMD) = 
                   (SELECT KK0241_MAX1.SVC_KEI_NO, MAX(KK0241_MAX1.KAISEN_UCWK_USE_ENDYMD) AS KAISEN_UCWK_USE_ENDYMD 
                      FROM KK_T_KAISEN_TG_SVKEI KK0241_MAX1 
                     WHERE KK0241_MAX1.SVC_KEI_NO = KK0241_01.SVC_KEI_NO 
                       AND KK0241_MAX1.KAISEN_UCWK_USE_STAYMD <= ?
                       AND KK0241_MAX1.MK_FLG = '0' 
                     GROUP BY KK0241_MAX1.SVC_KEI_NO) 
            AND    KK0251_01.SVC_KEI_KAISEN_UCWK_STAT = '100'
            AND   (KK0251_01.SVC_KEI_KAISEN_UCWK_NO, KK0251_01.GENE_ADD_DTM) =
                  (SELECT KK0251_01_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_01_GENE.GENE_ADD_DTM)
                   FROM   KK_T_SVKEI_KAISEN_UW KK0251_01_GENE
                   WHERE  KK0251_01_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251_01.SVC_KEI_KAISEN_UCWK_NO
                   AND    KK0251_01_GENE.MK_FLG = '0'
                   GROUP BY KK0251_01_GENE.SVC_KEI_KAISEN_UCWK_NO)
    ) 
ORDER BY KK0251.SVC_KEI_KAISEN_UCWK_NO ASC
        ,KK0081.SVC_KEI_NO ASC

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_051)
#--サービス契約番号に紐づくサービス契約回線内訳を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_051

SELECT     KK0241.SVC_KEI_KAISEN_UCWK_NO
           , KK0251.KAISEN_CD
FROM       KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN (SELECT     CASE
                         WHEN  KK0241_ADCHG.KAISEN_UCWK_USE_STAYMD IS NULL /* 住所変更先が存在しない場合 */
                           THEN  KK0241_MAIN.SVC_KEI_KAISEN_UCWK_NO        /* 住所変更前より設定 */
                           ELSE  KK0241_ADCHG.SVC_KEI_KAISEN_UCWK_NO       /* 住所変更先より設定 */
                       END AS SVC_KEI_KAISEN_UCWK_NO
            FROM      (SELECT   KK0241.SVC_KEI_NO,                         /* 住所変更前のデータ抽出 */
                                KK0241.SVC_KEI_KAISEN_UCWK_NO,
                                KK0241.KAISEN_UCWK_USE_STAYMD
                       FROM     KK_T_KAISEN_TG_SVKEI KK0241
                       WHERE    KK0241.SVC_KEI_NO = ?
                       AND      KK0241.KAISEN_UCWK_USE_STAYMD != '20991231'
                       AND      KK0241.KAISEN_UCWK_USE_ENDYMD = '20991231'
                       AND      KK0241.MK_FLG = '0') KK0241_MAIN
            LEFT JOIN (SELECT   KK0241.SVC_KEI_NO,                         /* 住所変更先のデータ抽出 */
                                KK0241.SVC_KEI_KAISEN_UCWK_NO,
                                KK0241.KAISEN_UCWK_USE_STAYMD
                       FROM     KK_T_KAISEN_TG_SVKEI KK0241
                       WHERE    KK0241.SVC_KEI_NO = ?
                       AND      KK0241.KAISEN_UCWK_USE_STAYMD = '20991231'
                       AND      KK0241.MK_FLG = '0' ) KK0241_ADCHG ON
                       KK0241_ADCHG.SVC_KEI_NO = KK0241_MAIN.SVC_KEI_NO) KK0241 ON
            KK0241.SVC_KEI_KAISEN_UCWK_NO =KK0251.SVC_KEI_KAISEN_UCWK_NO    /* 住所変更先を優先した回線内訳番号で結合 */
WHERE      (KK0251.SVC_KEI_KAISEN_UCWK_NO , KK0251.GENE_ADD_DTM) = 
           (SELECT   KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO , MAX(KK0251_GENE.GENE_ADD_DTM)
            FROM     KK_T_SVKEI_KAISEN_UW KK0251_GENE
            WHERE    KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
            AND      KK0251_GENE.MK_FLG = '0'
            GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO)

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_052)
#--KKPRC68101
#--サービス契約に紐付くサービス契約回線内訳の回線場所情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_052

SELECT
    KK0251.KAISEN_PLACE_PCD
   ,KK0251.KAISEN_PLACE_STATE_NM
   ,KK0251.KAISEN_PLACE_CITY_NM
   ,KK0251.KAISEN_PLACE_OAZTSU_NM
   ,KK0251.KAISEN_PLACE_AZCHO_NM
   ,KK0251.KAISEN_PLACE_BNCHIGO
   ,KK0251.KAISEN_PLACE_ADRTTM
   ,KK0251.KAISEN_PLACE_ADRRM
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN (
        SELECT
            KK0241_01.SVC_KEI_NO
           ,KK0241_01.SVC_KEI_KAISEN_UCWK_NO
        FROM
            KK_T_KAISEN_TG_SVKEI KK0241_01
        WHERE
            KK0241_01.KAISEN_UCWK_USE_STAYMD <= ?
        AND
            KK0241_01.KAISEN_UCWK_USE_ENDYMD >= ?
        AND
            KK0241_01.MK_FLG = '0'
        AND
            KK0241_01.SVC_KEI_NO = ?
           ) KK0241
        ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
WHERE
    (KK0251.SVC_KEI_KAISEN_UCWK_NO,KK0251.GENE_ADD_DTM) =
        (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO,MAX(KK0251_GENE.GENE_ADD_DTM)
           FROM KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
            AND KK0251_GENE.MK_FLG = '0'
       GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO)

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_053)
#--KKPRC01701
#--サービス契約番号に紐づくサービス契約回線内訳情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_053

SELECT
    KK0251.KITEN_HDSHO_CD,
    KK0251.KAISEN_PLACE_STATE_NM,
    KK0251.KAISEN_PLACE_CITY_NM,
    KK0251.KAISEN_PLACE_OAZTSU_NM,
    KK0251.KAISEN_PLACE_AZCHO_NM,
    KK0251.KAISEN_PLACE_BNCHIGO,
    KK0251.KAISEN_PLACE_ADRTTM
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
INNER JOIN
    KK_T_KAISEN_TG_SVKEI KK0241
ON
    KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
    KK0241.SVC_KEI_NO = ? AND
    KK0241.KAISEN_UCWK_USE_ENDYMD = (SELECT
                                        MAX(KK0241_KUUS_MAX.KAISEN_UCWK_USE_ENDYMD) AS KK0241_MAX
                                    FROM
                                        KK_T_KAISEN_TG_SVKEI KK0241_KUUS_MAX
                                    WHERE
                                        KK0241_KUUS_MAX.SVC_KEI_NO = KK0241.SVC_KEI_NO AND
                                        KK0241_KUUS_MAX.KAISEN_UCWK_USE_STAYMD <= ? AND
                                        KK0241_KUUS_MAX.MK_FLG = '0')
WHERE
    KK0251.GENE_ADD_DTM = (SELECT
                               MAX(KK0251_MAX.GENE_ADD_DTM)
                             FROM
                               KK_T_SVKEI_KAISEN_UW KK0251_MAX
                            WHERE
                               KK0251_MAX.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
                               AND KK0251.MK_FLG = '0')
ORDER BY
    KK0241.KAISEN_UCWK_USE_STAYMD ASC
#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_054)
#--KKPRC55302
#--サービス利用場所住所を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_054

SELECT
    KK0251.KAISEN_PLACE_STATE_NM || KK0251.KAISEN_PLACE_CITY_NM || KK0251.KAISEN_PLACE_OAZTSU_NM || KK0251.KAISEN_PLACE_AZCHO_NM || KK0251.KAISEN_PLACE_BNCHIGO || KK0251.KAISEN_PLACE_ADRTTM || KK0251.KAISEN_PLACE_ADRRM AS KAISEN_PLACE_ADR 
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ? 
AND KK0251.GENE_ADD_DTM =
    (SELECT
         MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
         KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
     AND KK0251_GENE.MK_FLG = '0')

#-


#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_055)
#--KKPRC32302
#--サービス利用場所郵便番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_055

SELECT
    KK0251.KAISEN_PLACE_PCD
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
    INNER JOIN KK_T_KAISEN_TG_SVKEI KK0241
        ON  KK0251.SVC_KEI_KAISEN_UCWK_NO = KK0241.SVC_KEI_KAISEN_UCWK_NO
WHERE
    KK0241.SVC_KEI_NO = ?
AND KK0241.KAISEN_UCWK_USE_STAYMD <= ?
AND KK0241.KAISEN_UCWK_USE_ENDYMD >= ?
AND KK0241.MK_FLG = '0'
AND KK0251.GENE_ADD_DTM =
         (SELECT
              MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
          FROM
              KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE
              KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
          AND KK0251_GENE.MK_FLG = '0'
         )

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_056)
#--KKPRC57401_コース変更一括登録
#--PON種別コードを取得
#-----------------------------------------------------------

--KEY:KK_SELECT_056

SELECT 
        KK0251.SVC_KEI_KAISEN_UCWK_NO,
        KK0251.PON_SBT_CD
FROM KK_T_KAISEN_TG_SVKEI KK0241
 INNER JOIN KK_T_SVKEI_KAISEN_UW KK0251
         ON KK0241.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
WHERE KK0241.SVC_KEI_NO = ?
  AND KK0241.KAISEN_UCWK_USE_STAYMD <= ?
  AND (KK0241.SVC_KEI_NO, KK0241.KAISEN_UCWK_USE_ENDYMD) = 
      (SELECT KK0241_MAX.SVC_KEI_NO, MAX(KK0241_MAX.KAISEN_UCWK_USE_ENDYMD) AS KAISEN_UCWK_USE_ENDYMD 
         FROM KK_T_KAISEN_TG_SVKEI KK0241_MAX 
        WHERE KK0241_MAX.SVC_KEI_NO = KK0241.SVC_KEI_NO 
          AND KK0241_MAX.KAISEN_UCWK_USE_STAYMD <= ?
          AND KK0241_MAX.MK_FLG = '0' 
        GROUP BY KK0241_MAX.SVC_KEI_NO) 
  AND (KK0251.SVC_KEI_KAISEN_UCWK_NO, KK0251.GENE_ADD_DTM) = 
         (SELECT KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_GENE.GENE_ADD_DTM) 
          FROM KK_T_SVKEI_KAISEN_UW KK0251_GENE
          WHERE KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
            AND KK0251_GENE.MK_FLG = '0'
            GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO)
ORDER BY
    KK0241.KAISEN_UCWK_USE_STAYMD

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_058)
#--KKPRC61203にて使用
#--住所変更情報取得
#-----------------------------------------------------------

--KEY:KK_SELECT_058

SELECT
    KK2091.ITENS_SVKEI_KISUW_NO 
FROM
    KK_T_ADCHG KK2091
INNER JOIN
    KK_T_SVKEI_KAISEN_UW KK0251
ON
    KK0251.SVC_KEI_KAISEN_UCWK_NO = KK2091.ITENS_SVKEI_KISUW_NO
AND
    KK0251.GENE_ADD_DTM =
      (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
       FROM   KK_T_SVKEI_KAISEN_UW KK0251_GENE
       WHERE  KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO 
       AND    KK0251_GENE.MK_FLG = '0') 
JOIN
    KK_T_MANSION_BUKKEN KK0861
ON
    KK0861.MANSION_BUKKEN_NO = KK0251.MANSION_BUKKEN_NO
WHERE
    KK2091.ITNM_SVKEI_KISUW_NO = ?
AND (KK0251.MANSION_BUKKEN_NO IS NULL
    OR KK0861.MANSION_BUKKEN_NO = '002')
AND KK2091.MK_FLG = '0'
AND KK0251.MK_FLG = '0'
AND KK0861.MK_FLG = '0'


#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_057)
#--KKPRC58001
#--機器流用元回線のPON種別コードを取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_057

SELECT
    KK0251.PON_SBT_CD
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND KK0251.GENE_ADD_DTM =
    (SELECT
         MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
         KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
     AND KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_059)
#--【内容】
#--   サービス契約回線内訳番号を元に、利用場所情報を取得。
#--【条件】
#--   サービス契約回線内訳番号（有効データ）
#--【抽出対象】
#--   サービス契約回線内訳番号
#-----------------------------------------------------------


--KEY:KK_SELECT_059

SELECT
    KK0251.KAISEN_PLACE_PCD
  , KK0251.KAISEN_PLACE_STATE_NM
  , KK0251.KAISEN_PLACE_CITY_NM
  , KK0251.KAISEN_PLACE_OAZTSU_NM
  , KK0251.KAISEN_PLACE_AZCHO_NM
  , KK0251.KAISEN_PLACE_BNCHIGO
  , KK0251.KAISEN_PLACE_ADRTTM
  , KK0251.KAISEN_PLACE_ADRRM
  , KK0251.KAISEN_PLACE_TELNO
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND KK0251.GENE_ADD_DTM = 
       (SELECT
          MAX(KK0251_GENE.GENE_ADD_DTM)
        FROM
          KK_T_SVKEI_KAISEN_UW KK0251_GENE
        WHERE
          KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
        AND KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_060)
#--KKPRC62801
#--移転先回線の入居予定日を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_060

SELECT
    KK0251.NYUKYO_RSV_YMD
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND KK0251.GENE_ADD_DTM =
    (SELECT
         MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
         KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
     AND KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--工事完了ワーク(KK_T_SVKEI_KAISEN_UW-KK_SELECT_061)
#--KKPRC65701_ローゼット工事完了反映
#--ローゼット設置結果の反映後の更新結果に差異があるか確認するため、現状のローゼット設置の状態を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_061

SELECT 
    KK0251.ROZETT_STC_STAT_CD,
    KK0251.CONSNT_STC_NYO_CD,
    KK0251.TNMT_STC_FLR_CD,
    KK0251.KISN_TD_SETPLC_1_CD,
    KK0251.KISN_TD_SETPLC_2_CD,
    KK0251.KISN_TD_STC_KASHO_CD,
    KK0251.KISN_TD_SETPLC_1_OTR,
    KK0251.KISN_TD_SETPLC_2_OTR,
    KK0251.KISN_TD_STC_KASHO_OTR,
    KK0251.TANMT_STC_MEN_CD,
	KK0251.GENE_ADD_DTM
FROM 
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND KK0251.MK_FLG = '0'
AND KK0251.GENE_ADD_DTM = 
       (SELECT
          MAX(KK0251_GENE.GENE_ADD_DTM)
        FROM
          KK_T_SVKEI_KAISEN_UW KK0251_GENE
        WHERE
          KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
        AND KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_062)
#--KKPRC65701
#--ローゼット化済のネット系撤去工事の回線情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_062

SELECT
    KK0251.KAISEN_PLACE_NO,
    KK0251.KAISEN_PLACE_STATE_NM,
    KK0251.KAISEN_PLACE_CITY_NM,
    KK0251.KAISEN_PLACE_OAZTSU_NM,
    KK0251.KAISEN_PLACE_AZCHO_NM,
    KK0251.KAISEN_PLACE_BNCHIGO,
    KK0251.KAISEN_PLACE_ADRTTM,
    KK0251.KAISEN_PLACE_ADRRM,
    KK0251.ROZETT_STC_STAT_CD
FROM 
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
    AND KK0251.MK_FLG = '0'
    AND KK0251.GENE_ADD_DTM = 
       (SELECT
          MAX(KK0251_GENE.GENE_ADD_DTM)
        FROM
          KK_T_SVKEI_KAISEN_UW KK0251_GENE
        WHERE
          KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
        AND KK0251_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KU_SELECT_001)
#--KUPRC00401
#--ローゼット設置情報を取得する。
#-----------------------------------------------------------

--KEY:KU_SELECT_001

SELECT
    KK0251.ROZETT_STC_STAT_CD,
    KK0251.CONSNT_STC_NYO_CD
FROM
    KK_T_SVKEI_KAISEN_UW KK0251
WHERE
    KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
AND (KK0251.SVC_KEI_KAISEN_UCWK_NO, KK0251.GENE_ADD_DTM) =
    (SELECT
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO, MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
         KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
         KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
     AND KK0251_GENE.MK_FLG = '0'
     GROUP BY KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO)

#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_063)
#--KKPRC65601_ローゼット設置状態一括取込
#--サービス契約回線内訳のカレントデータを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_063

SELECT
 KK0251.SVC_KEI_KAISEN_UCWK_NO,
 KK0251.GENE_ADD_DTM,
 KK0251.SVC_KEI_KAISEN_UCWK_STAT,
 KK0251.MSKM_DTL_NO,
 KK0251.KISN_PLC_AD_MI_FIX_FLG,
 KK0251.KISN_PLC_AD_MI_FIX_RLS_YMD,
 KK0251.KAISEN_PLC_KSH_AD_SAI_FLG,
 KK0251.CUST_SKK_AD_MI_FIX_FLG,
 KK0251.CUST_SOS_USE_UM,
 KK0251.KAISEN_CD,
 KK0251.MANSION_BUKKEN_NO,
 KK0251.KAISEN_PLACE_TELNO,
 KK0251.KAISEN_PLC_KANA,
 KK0251.KAISEN_PLACE_AD_CD,
 KK0251.KAISEN_PLACE_PCD,
 KK0251.KAISEN_PLACE_STATE_NM,
 KK0251.KAISEN_PLACE_CITY_NM,
 KK0251.KAISEN_PLACE_OAZTSU_NM,
 KK0251.KAISEN_PLACE_AZCHO_NM,
 KK0251.KAISEN_PLACE_BNCHIGO,
 KK0251.KAISEN_PLACE_ADRTTM,
 KK0251.KAISEN_PLACE_ADRRM,
 KK0251.SKS_YO_KISN_PLACE_AD_RM_NO,
 KK0251.KAISEN_PLACE_NO,
 KK0251.KISN_PLC_KKK_SEIRI_CHU_FLG,
 KK0251.KISN_PLC_HSK_CD_1,
 KK0251.KISN_PLC_HSK_CD_2,
 KK0251.KISN_PLC_HSK_MEMO,
 KK0251.USE_STAYMD,
 KK0251.AD_FORM_CD,
 KK0251.SHUNKO_RSV_YMD,
 KK0251.EPOWER_SODEN_RSV_YMD,
 KK0251.NYUKYO_RSV_YMD,
 KK0251.HIKIWATASHI_RSV_YMD,
 KK0251.HIKKOSHI_RSV_YMD,
 KK0251.SKS_SHS_AD_CHG_UM,
 KK0251.SKS_SHS_AD_CHG_KIBO_YMD,
 KK0251.KSHAD_CHG_UM,
 KK0251.KSHAD_CHG_KIBO_YMD,
 KK0251.XZAHYO,
 KK0251.YZAHYO,
 KK0251.LGTD,
 KK0251.LTTD,
 KK0251.ZAHYO_HOSEI_UM,
 KK0251.TV_KOBET_TAIO_CD,
 KK0251.TV_ANTENNA_UM,
 KK0251.DIRECTION_CD_1,
 KK0251.DIRECTION_CD_2,
 KK0251.KODATE_KCKU_FLR_CNT_CD,
 KK0251.SHUGJ_NYUKYO_FLR_CNT_CD,
 KK0251.PON_SBT_CD,
 KK0251.LOOPBACK_TEST_FIN_YMD,
 KK0251.KAISEN_TK_COMP_CD,
 KK0251.VDSL_USE_TEL_KISN_SBT_CD,
 KK0251.VDSL_USE_DKSN_SWCH_RSV_YMD,
 KK0251.VDSL_USE_TG_DBKSN_SBT_CD,
 KK0251.VDSL_USE_TG_TELNO,
 KK0251.TEL_BAS_HOST_ID,
 KK0251.TEL_VLAN_ID,
 KK0251.VLAN_ID_FIX_FLG,
 KK0251.TEL_VLAN_ID_TRKM_YMD,
 KK0251.IDO_DIV,
 KK0251.HIKKOSHI_YMD,
 KK0251.GAITO_AREA_SVC_STAYMD,
 KK0251.TEKKYO_KOJI_KIBO_YMD,
 KK0251.USE_ENDYMD,
 KK0251.KITEN_HDSHO_CD,
 KK0251.SHYAKK_CD,
 KK0251.SVC_CANCEL_YMD,
 KK0251.SVC_CANCEL_RSN_CD,
 KK0251.SVKEI_KISN_UW_CNCL_CL_YMD,
 KK0251.KEPCO_BUSIOFFICE_CD,
 KK0251.ADD_DTM,
 KK0251.ADD_OPEACNT,
 KK0251.KAISEN_PLACE_TOU_INFO,
 KK0251.ROZETT_STC_STAT_CD,
 KK0251.CONSNT_STC_NYO_CD,
 KK0251.TNMT_STC_FLR_CD,
 KK0251.KISN_TD_SETPLC_1_CD,
 KK0251.KISN_TD_SETPLC_2_CD,
 KK0251.KISN_TD_STC_KASHO_CD,
 KK0251.KISN_TD_SETPLC_1_OTR,
 KK0251.KISN_TD_SETPLC_2_OTR,
 KK0251.KISN_TD_STC_KASHO_OTR,
 KK0251.TANMT_STC_MEN_CD,
 KK0251.KAISEN_PLACE_RENBAN,
 KK0251.HGW_KKN_FFG
 
FROM
  KK_T_SVKEI_KAISEN_UW KK0251
WHERE
  KK0251.SVC_KEI_KAISEN_UCWK_NO = ?
  AND KK0251.GENE_ADD_DTM =
    (SELECT
      MAX(KK0251_GENE.GENE_ADD_DTM) AS KK0251_MAX
     FROM
       KK_T_SVKEI_KAISEN_UW KK0251_GENE
     WHERE
       KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO
       AND  KK0251_GENE.MK_FLG = '0')
       
#-

#-----------------------------------------------------------
#--サービス契約回線内訳(KK_T_SVKEI_KAISEN_UW-KK_SELECT_064)
#--KKPRC67605
#-----------------------------------------------------------

--KEY:KK_SELECT_064

SELECT
 KK0251.SVC_KEI_KAISEN_UCWK_STAT,
 KK0251.GENE_ADD_DTM
FROM
 KK_T_SVKEI_KAISEN_UW KK0251
WHERE
 KK0251.SVC_KEI_KAISEN_UCWK_NO = ? AND
 KK0251.GENE_ADD_DTM = (SELECT MAX(KK0251_GENE.GENE_ADD_DTM) KK0251_MAX
              FROM KK_T_SVKEI_KAISEN_UW KK0251_GENE
              WHERE KK0251_GENE.SVC_KEI_KAISEN_UCWK_NO = KK0251.SVC_KEI_KAISEN_UCWK_NO AND
                    KK0251_GENE.MK_FLG = '0')

#-