#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_001)
#--指定された申込明細番号の、申込明細情報と申込情報を取得
#-----------------------------------------------------------

--KEY:KK_SELECT_001

SELECT
 MSKM_DTL.MSKMSHO_NO,
 MSKM_DTL.SYSID,
 MSKM.MSKM_SBT_CD
FROM
 KK_T_MSKM_DTL MSKM_DTL
INNER JOIN
 KK_T_MSKM MSKM
ON
 MSKM_DTL.MSKM_NO = MSKM.MSKM_NO
WHERE
 MSKM_DTL.MSKM_DTL_NO = ? 
AND MSKM_DTL.GENE_ADD_DTM = (
     SELECT MAX(MSKM_DTL_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
     FROM  KK_T_MSKM_DTL MSKM_DTL_GENE
     WHERE  MSKM_DTL_GENE.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
     AND    MSKM_DTL_GENE.MK_FLG = '0')
AND MSKM.GENE_ADD_DTM = (
     SELECT MAX(MSKM_GENE.GENE_ADD_DTM) AS MSKM_MAX
     FROM  KK_T_MSKM MSKM_GENE
     WHERE  MSKM_GENE.MSKM_NO = MSKM.MSKM_NO
     AND    MSKM_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_002)
#--・申込形態コードチェック情報を抽出する。
#--    ＜抽出条件＞
#--        １．申込明細.SYSIDが入力パラメータ.SYSIDと紐づくデータ
#--        ２．申込明細.申込形態コードが入力パラメータ.申込形態コードと紐づくデータ。
#-----------------------------------------------------------

--KEY:KK_SELECT_002

SELECT
  1
FROM
  KK_T_MSKM_DTL TMD
WHERE
 TMD.SYSID = ?
 AND TMD.MSKM_FORM_CD = ?
 AND TMD.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_003)
#--申込明細番号に紐づく申込明細情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_003

SELECT  MSKM_DTL.MSKM_NO,
        MSKM_DTL.MSKMSHO_NO,
        MSKM_DTL.MSKM_YMD,
        MSKM_DTL.MSKM_DTL_NO
FROM    KK_T_MSKM_DTL MSKM_DTL
WHERE   MSKM_DTL.MSKM_DTL_NO = ?
AND     MSKM_DTL.GENE_ADD_DTM = (SELECT MAX(KK_T_MSKM_DTL_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX
            FROM    KK_T_MSKM_DTL KK_T_MSKM_DTL_RCNT
            WHERE   KK_T_MSKM_DTL_RCNT.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
            AND     KK_T_MSKM_DTL_RCNT.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_004)
#--メール送信対象のデータを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_004

SELECT
 KK0021.MSKM_DTL_NO,
 KK0021.MSKM_NO,
 KK0021.MSKMSHO_NO,
 KK0021.KOJI_PRG_TCHI_MLAD,
 KK0021.KJSC_TCH_KTAI_MLAD,
 KK0081.SVC_KEI_NO,
 KK0081.SVC_KEI_STAT,
 KK0081.SVC_CD,
 KK0081.SKEKKA_CD,
 KU0151.KOJIAK_NO,
 KU0151.STEP_HROUTE_CHOSA_DSGN_CD,
 KU0151.STEP_CUST_TAKCHO_NTADJ_CD,
 KU0151.STEP_CUST_TAKCHO_CD,
 KU0151.STEP_VARI_KYOKA_SHINSEI_CD,
 KU0151.STEP_TAKCHO_SDAKS_RCP_CD,
 KU0151.STEP_HCABLE_LINE_KOJI_CD,
 KU0151.STEP_CUST_TAKNI_KOJI_CD,
 KU0151.STEP_USE_STA_CD,
 KU0151.TAKCHO_JSSI_FLG,
 KK1461.SVC_KEI_STAT SVC_KEI_STAT1,
 KK1461.SKEKKA_CD SKEKKA_CD1,
 KK1461.STEP_HROUTE_CHOSA_DSGN_CD STEP_HROUTE_CHOSA_DSGN_CD1,
 KK1461.STEP_CUST_TAKCHO_NTADJ_CD STEP_CUST_TAKCHO_NTADJ_CD1,
 KK1461.STEP_CUST_TAKCHO_CD STEP_CUST_TAKCHO_CD1,
 KK1461.STEP_VARI_KYOKA_SHINSEI_CD STEP_VARI_KYOKA_SHINSEI_CD1,
 KK1461.STEP_TAKCHO_SDAKS_RCP_CD STEP_TAKCHO_SDAKS_RCP_CD1,
 KK1461.STEP_HCABLE_LINE_KOJI_CD STEP_HCABLE_LINE_KOJI_CD1,
 KK1461.STEP_CUST_TAKNI_KOJI_CD STEP_CUST_TAKNI_KOJI_CD1,
 KK1461.STEP_USE_STA_CD STEP_USE_STA_CD1
FROM KK_T_MSKM_DTL KK0021
 INNER JOIN 
  KK_T_SVC_KEI KK0081
 ON
  KK0021.MSKM_DTL_NO = KK0081.MSKM_DTL_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') AND 
  KK0081.MK_FLG = '0'
 INNER JOIN
  KU_T_MSKM_DTL_KOJIAK KU0091
 ON
  KK0021.MSKM_DTL_NO = KU0091.MSKM_DTL_NO AND
  ? BETWEEN KU0091.MSKM_DTL_KOJIAK_TSTAYMD AND
  KU0091.MSKM_DTL_KOJIAK_TENDYMD AND
  KU0091.MK_FLG = '0'
 INNER JOIN
  KU_T_KOJI_STEP_PRG KU0151
 ON
  KU0091.KOJIAK_NO = KU0151.KOJIAK_NO AND
  KU0151.KOJI_STEP_PRG_YMD = ? AND
  KU0151.MK_FLG = '0'
 LEFT OUTER JOIN
  (SELECT
    KK1461.MSKM_DTL_NO,
    KK1461.KOJIAK_NO,
    KK1461.SVC_KEI_STAT SVC_KEI_STAT,
    KK1461.SKEKKA_CD SKEKKA_CD,
    KK1461.STEP_HROUTE_CHOSA_DSGN_CD,
    KK1461.STEP_CUST_TAKCHO_NTADJ_CD,
    KK1461.STEP_CUST_TAKCHO_CD,
    KK1461.STEP_VARI_KYOKA_SHINSEI_CD,
    KK1461.STEP_TAKCHO_SDAKS_RCP_CD,
    KK1461.STEP_HCABLE_LINE_KOJI_CD,
    KK1461.STEP_CUST_TAKNI_KOJI_CD,
    KK1461.STEP_USE_STA_CD,
    KK1461.MK_FLG 
   FROM KK_T_STPPRML_SND_JSK KK1461
     INNER JOIN
       KU_T_KOJI_STEP_PRG KU0151
     ON
       KK1461.KOJIAK_NO = KU0151.KOJIAK_NO AND
       KU0151.MK_FLG = '0'
   WHERE
       KK1461.STPPRML_SND_JSK_NO = (
         SELECT 
           MAX(KK1461_GENE.STPPRML_SND_JSK_NO)
         FROM
           KK_T_STPPRML_SND_JSK KK1461_GENE
         WHERE 
           KK1461_GENE.KOJIAK_NO = KK1461.KOJIAK_NO AND
           KK1461_GENE.MSKM_DTL_NO = KK1461.MSKM_DTL_NO AND
           KK1461_GENE.MK_FLG = '0')
   )  KK1461
 ON
  KK1461.KOJIAK_NO = KU0091.KOJIAK_NO AND
  KK1461.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND
  KK1461.MK_FLG = '0'
WHERE
  (KK0021.KOJI_PRG_TCHI_MLAD IS NOT NULL OR KK0021.KJSC_TCH_KTAI_MLAD IS NOT NULL) AND
  (KK0021.ML_SEND_JGI_FLG = '0' OR TRIM(KK0021.ML_SEND_JGI_FLG) IS NULL) AND
  KK0021.GENE_ADD_DTM = 
      (SELECT 
          MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX 
      FROM
          KK_T_MSKM_DTL KK0021_GENE 
      WHERE 
          KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND 
          KK0021_GENE.MK_FLG='0'
      )
  AND KK0021.MK_FLG='0'
ORDER BY KU0151.KOJIAK_NO,KK0021.MSKM_NO,KK0021.KOJI_PRG_TCHI_MLAD,KK0021.KJSC_TCH_KTAI_MLAD,KK0081.SVC_CD

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_005)
#--申込明細番号に紐づく申込明細情報、申込_代理店情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_005

SELECT 
  KTMD.MSKM_DTL_NO 
  ,KTMD.MSKM_NO 
  ,KTMD.MSKMSHO_NO 
  ,KTMA.AGNT_CD 
FROM  
  KK_T_MSKM_DTL KTMD 
INNER JOIN  
  KK_T_MSKM_AGNT KTMA 
ON  
  KTMD.MSKM_NO = KTMA.MSKM_NO 
WHERE 
  KTMD.MSKM_DTL_NO = ? 
  AND KTMD.MK_FLG = '0' 
  AND KTMA.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_006)
#--申込明細番号に紐づく申込明細情報を取得する。
#--KKPRC35201、KKPRC44501にて使用
#-----------------------------------------------------------

--KEY:KK_SELECT_006

SELECT
    KK0021.MSKM_NO,
    KK0021.MSKMSHO_NO
FROM
    KK_T_MSKM_DTL KK0021
WHERE
    KK0021.MSKM_DTL_NO = ?
    AND KK0021.GENE_ADD_DTM = 
        (
            SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX
            FROM    KK_T_MSKM_DTL KK0021_GENE
            WHERE   KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
            AND     KK0021_GENE.MK_FLG = '0'
        )

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_007)
#--申込明細の申込明細照査年月日時分秒が指定日から指定日まで、かつ、指定した申込形態コードのレコードの同時申込eo光申込書番号がNULL以外の場合は同時申込eo光申込書番号を、それ以外の場合は申込書番号を一意に検索する。
#-----------------------------------------------------------

--KEY:KK_SELECT_007
SELECT DISTINCT
   MSKMSHO_NO
FROM (
SELECT /*+ INDEX(MSKM_DTL KK_T_MSKM_DTL_IDX_06) */ 
    NVL(MSKM_DTL.DOJI_MSKM_EOH_MSKMSHO_NO, MSKM_DTL.MSKMSHO_NO) MSKMSHO_NO
FROM
    KK_T_MSKM_DTL MSKM_DTL
WHERE
    MSKM_DTL.MSKM_DTL_SHOSA_DTM > ? AND
    MSKM_DTL.MSKM_DTL_SHOSA_DTM <= ? AND
   MSKM_DTL.MSKM_FORM_CD = ? AND
    (MSKM_DTL.MSKMSHO_NO IS NOT NULL OR MSKM_DTL.DOJI_MSKM_EOH_MSKMSHO_NO IS NOT NULL) AND
    MSKM_DTL.GENE_ADD_DTM = (
        SELECT MAX(MSKM_DTL_GENE.GENE_ADD_DTM)
        FROM KK_T_MSKM_DTL MSKM_DTL_GENE
        WHERE MSKM_DTL_GENE.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
        AND MSKM_DTL_GENE.MK_FLG = '0')
    AND EXISTS ( 
      SELECT 1
         FROM KK_T_SVC_KEI KK0081
         WHERE KK0081.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
           AND KK0081.SVC_CD = '05'
           AND KK0081.MK_FLG = '0'
    )
UNION ALL
SELECT /*+ INDEX(MSKM_DTL KK_T_MSKM_DTL_IDX_06) */ 
    MSKM_DTL.MSKMSHO_NO
FROM
    KK_T_MSKM_DTL MSKM_DTL
WHERE  MSKM_DTL.MSKM_DTL_SHOSA_DTM > ? 
    AND MSKM_DTL.MSKM_DTL_SHOSA_DTM <= ? 
    AND MSKM_DTL.MSKM_FORM_CD = ? 
    AND MSKM_DTL.MSKMSHO_NO IS NOT NULL 
    AND MSKM_DTL.GENE_ADD_DTM = (
        SELECT MAX(MSKM_DTL_GENE.GENE_ADD_DTM)
        FROM KK_T_MSKM_DTL MSKM_DTL_GENE
        WHERE MSKM_DTL_GENE.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
        AND MSKM_DTL_GENE.MK_FLG = '0')
    AND EXISTS ( 
      SELECT 1
         FROM KK_T_SVC_KEI KK0081
         WHERE KK0081.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
           AND KK0081.SVC_CD <> '05'
           AND KK0081.MK_FLG = '0'
    )
)

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_008)
#--サービス契約のサービス開始日が指定日から指定日までのレコードの申込明細番号から、申込明細の同時申込eo光申込書番号がNULL以外の場合は同時申込eo光申込書番号を、それ以外の場合は申込書番号を一意に検索する。
#--MVNOサービスは対象外にする。
#-----------------------------------------------------------

--KEY:KK_SELECT_008

SELECT DISTINCT
    NVL(MSKM_DTL.DOJI_MSKM_EOH_MSKMSHO_NO, MSKM_DTL.MSKMSHO_NO)
FROM
    KK_T_MSKM_DTL MSKM_DTL
WHERE
    MSKM_DTL.MSKM_DTL_NO IN
    (
        SELECT
            SVC_KEI.MSKM_DTL_NO
        FROM
            KK_T_SVC_KEI SVC_KEI
        INNER JOIN KK_T_PRG PRG
            ON PRG.SVC_KEI_NO = SVC_KEI.SVC_KEI_NO AND
            PRG.MSKM_DTL_NO = SVC_KEI.MSKM_DTL_NO AND
            PRG.PRG_DTM > ? AND
            PRG.PRG_DTM <= ? AND
            PRG.PRG_STAT = '1000' AND
            PRG.MK_FLG = '0'
        WHERE
            SVC_KEI.SVC_CD != '51' AND
            SVC_KEI.MK_FLG = '0'
    ) AND
    (MSKM_DTL.MSKMSHO_NO IS NOT NULL OR MSKM_DTL.DOJI_MSKM_EOH_MSKMSHO_NO IS NOT NULL) AND
    MSKM_DTL.GENE_ADD_DTM = (
        SELECT MAX(MSKM_DTL_GENE.GENE_ADD_DTM)
        FROM KK_T_MSKM_DTL MSKM_DTL_GENE
        WHERE MSKM_DTL_GENE.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
        AND MSKM_DTL_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_009)
#--KKPRC05701
#--申込明細情報の取得
#-----------------------------------------------------------

--KEY:KK_SELECT_009

SELECT
 KK0021.KOJI_APO_RRKS_TELNO
FROM
 KK_T_MSKM_DTL KK0021
WHERE
 KK0021.MSKM_DTL_NO = ?
 AND KK0021.GENE_ADD_DTM = (
  SELECT
   MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX
  FROM
   KK_T_MSKM_DTL KK0021_GENE
  WHERE
   KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
   AND KK0021_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_010)
#--申込番号に紐づく申込明細番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_010

SELECT
    MSKM_DTL.MSKM_DTL_NO
FROM
    KK_T_MSKM_DTL MSKM_DTL
WHERE
    MSKM_DTL.MSKM_NO = ?
AND
    MSKM_DTL.GENE_ADD_DTM = 
        (
            SELECT MAX(KK_T_MSKM_DTL_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX
            FROM    KK_T_MSKM_DTL KK_T_MSKM_DTL_RCNT
            WHERE   KK_T_MSKM_DTL_RCNT.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
            AND     KK_T_MSKM_DTL_RCNT.MK_FLG = '0'
        )

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_011)
#--KKPRC01601
#--指定された申込明細番号と同じ申込番号を持つ申込明細番号の取得
#-----------------------------------------------------------

--KEY:KK_SELECT_011

SELECT
    KK0021.MSKM_DTL_NO
FROM
    KK_T_MSKM_DTL KK0021
WHERE
    KK0021.MSKM_NO = (SELECT
                           KK0021_A.MSKM_NO
                       FROM
                           KK_T_MSKM_DTL KK0021_A
                       WHERE
                           KK0021_A.MSKM_DTL_NO = ? AND
                           KK0021_A.GENE_ADD_DTM = (SELECT
                                                       MAX(KK0021_GENE.GENE_ADD_DTM) AS MAX
                                                   FROM
                                                       KK_T_MSKM_DTL KK0021_GENE
                                                   WHERE
                                                       KK0021_GENE.MSKM_DTL_NO = KK0021_A.MSKM_DTL_NO AND
                                                       KK0021_GENE.MK_FLG = '0') AND
                           KK0021_A.MK_FLG = '0'
                       ) AND
    KK0021.GENE_ADD_DTM = (SELECT
                              MAX(KK0021_GENE.GENE_ADD_DTM) AS MAX
                          FROM
                              KK_T_MSKM_DTL KK0021_GENE
                          WHERE
                              KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND
                              KK0021_GENE.MK_FLG = '0') AND
    KK0021.MK_FLG = '0' AND
    KK0021.MSKM_DTL_NO <> ?

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_012)
#--KKPRC37301  
#--申込明細情報取得
#-----------------------------------------------------------

--KEY:KK_SELECT_012

SELECT  
  KK0011.MSKM_SBT_CD,  
  KK0021.MSKM_FORM_CD  
 FROM   
  KK_T_MSKM_DTL KK0021  
  INNER JOIN  
   KK_T_MSKM KK0011  
 ON  
  KK0011.MSKM_NO = KK0021.MSKM_NO  
  AND  KK0011.GENE_ADD_DTM =  
      (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_MAX  
       FROM   KK_T_MSKM KK0011_GENE  
       WHERE  KK0011_GENE.MSKM_NO = KK0011.MSKM_NO   
       AND    KK0011_GENE.MK_FLG = '0')   
WHERE  
  KK0021.MSKM_DTL_NO = ?  
  AND  KK0021.GENE_ADD_DTM =  
      (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX  
       FROM   KK_T_MSKM_DTL KK0021_GENE  
       WHERE  KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO   
       AND    KK0021_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_013)
#--サービス契約番号に紐づく申込詳細の契約付加コードを取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_013

SELECT
    MSKM_DTL.KEI_HUKA_CD
FROM
    KK_T_MSKM_DTL MSKM_DTL
WHERE
    MSKM_DTL.MSKM_DTL_NO = ?
AND
    MSKM_DTL.GENE_ADD_DTM = (
        SELECT MAX(MSKM_DTL_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
        FROM
            KK_T_MSKM_DTL MSKM_DTL_GENE
        WHERE
            MSKM_DTL_GENE.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
        AND
            MSKM_DTL_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_014)
#--端末購入通知書データ抽出
#--申込明細番号から代理店端末販売担当者氏名を抽出
#-----------------------------------------------------------

--KEY:KK_SELECT_014

SELECT KK0061.TNMT_BUY_UK_TNTSHA_NM
FROM   KK_M_AGNT KK0061
WHERE  KK0061.AGNT_CD = (SELECT KK0071_.AGNT_CD
  FROM (SELECT KK0071.AGNT_CD
  FROM KK_T_MSKM_AGNT KK0071
    INNER JOIN (SELECT KK0021.MSKM_NO
      FROM   KK_T_MSKM_DTL KK0021
      WHERE  KK0021.MSKM_DTL_NO = ?
      AND    KK0021.GENE_ADD_DTM = (
        SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS GENE_MAX
        FROM   KK_T_MSKM_DTL KK0021_GENE
        WHERE  KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
        AND    KK0021.MK_FLG = '0')) KK0021_
    ON KK0021_.MSKM_NO = KK0071.MSKM_NO
  ORDER BY DSP_JUN ASC) KK0071_
  WHERE ROWNUM = 1)
AND    KK0061.RSV_APLY_YMD || KK0061.GENE_ADD_DTM = (
  SELECT MAX(KK0061_GENE.RSV_APLY_YMD || KK0061_GENE.GENE_ADD_DTM) AS GENE_MAX
  FROM   KK_M_AGNT KK0061_GENE
  WHERE  KK0061_GENE.RSV_APLY_YMD <= ?
  AND    ? BETWEEN KK0061_GENE.AGNT_TSTAYMD AND KK0061_GENE.AGNT_TENDYMD
  AND    KK0061_GENE.MK_FLG = '0')

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_015)
#--契約情報ファイル作成のための、申込明細番号に紐付く申込明細情報を抽出する。
#-----------------------------------------------------------

--KEY:KK_SELECT_015

SELECT
     KTMD.MSKMSHO_NO
    ,KTMD.MSKM_YMD
FROM
    KK_T_MSKM_DTL KTMD
WHERE
    KTMD.MSKM_DTL_NO = ?
AND
    KTMD.GENE_ADD_DTM = (
        SELECT
            MAX(KTMD_GENE.GENE_ADD_DTM) AS KTMD_MAX
        FROM
            KK_T_MSKM_DTL KTMD_GENE
        WHERE
            KTMD_GENE.MSKM_DTL_NO = KTMD.MSKM_DTL_NO
        AND
            KTMD_GENE.MK_FLG = '0'
    )

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_016)
#--キャンペーンに伴い、申込された申込年月日を取得するため、申込明細を取得する。なお、カレントを対象とする。
#-----------------------------------------------------------

--KEY:KK_SELECT_016

SELECT 
    KK0021.MSKM_YMD
FROM 
   KK_T_MSKM_DTL KK0021 
WHERE 
    (KK0021.MSKM_DTL_NO, KK0021.GENE_ADD_DTM) = 
        (SELECT KK0021_GENE.MSKM_DTL_NO, MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX
         FROM    KK_T_MSKM_DTL KK0021_GENE
         WHERE   KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
         AND     KK0021_GENE.MK_FLG = '0'
         GROUP BY KK0021_GENE.MSKM_DTL_NO)
AND KK0021.MSKM_DTL_NO = ?

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_017)
#--申込明細番号から申込書番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_017

SELECT
    KK0021.MSKMSHO_NO
FROM KK_T_MSKM_DTL KK0021
    WHERE KK0021.MSKM_DTL_NO = ?
    AND KK0021.GENE_ADD_DTM =
        (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) KK0021_MAX
         FROM KK_T_MSKM_DTL KK0021_GENE
         WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
         AND   KK0021_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_018)
#--申込番号から申込書番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_018

SELECT
    DTL.MSKMSHO_NO
FROM
    KK_T_MSKM_DTL DTL
WHERE
    DTL.MSKM_NO = ?
AND
    DTL.GENE_ADD_DTM = 
          (SELECT MAX(SUB.GENE_ADD_DTM) AS SUB_MAX 
         FROM KK_T_MSKM_DTL SUB
        WHERE DTL.MSKM_DTL_NO = SUB.MSKM_DTL_NO
          AND SUB.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_019)
#--申込書番号単位に「申込明細」を検索し、同じ申込書番号が使用されていないか確認する。
#-----------------------------------------------------------

--KEY:KK_SELECT_019

SELECT
   COUNT(*) AS CNT
FROM
   KK_T_MSKM_DTL KTMD
WHERE
   KTMD.MSKMSHO_NO = ?
AND
   KTMD.MK_FLG = '0'
AND
   KTMD.GENE_ADD_DTM = (
      SELECT
         MAX(KTMD_GENE.GENE_ADD_DTM) AS KTMD_MAX
      FROM
         KK_T_MSKM_DTL KTMD_GENE
      WHERE
         KTMD_GENE.MSKM_DTL_NO = KTMD.MSKM_DTL_NO
      AND
         KTMD_GENE.MK_FLG = '0'
   )

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_020)
#--申込明細番号に紐づく申込書番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_020

SELECT
 KTMSD.MSKMSHO_NO
FROM
 KK_T_MSKM_DTL KTMSD
WHERE
 KTMSD.MSKM_DTL_NO = ?
AND
 MK_FLG = '0'
AND
 KTMSD.GENE_ADD_DTM = 
 (SELECT
   MAX(KTMSD_GENE.GENE_ADD_DTM) AS KTMSD_MAX
  FROM
   KK_T_MSKM_DTL KTMSD_GENE
  WHERE
   KTMSD_GENE.MSKM_DTL_NO = KTMSD.MSKM_DTL_NO
  AND
   KTMSD_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_021)
#--申込明細番号に紐づく申込書番号、申込書明細番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_021

SELECT 
    MSKM_DTL.MSKM_NO,
    MSKM_DTL.MSKM_DTL_NO,
    MSKM_DTL.MSKMSHO_NO,
    MSKM_DTL.MSKMSHO_DTL_NO,
    MSKM_DTL.MSKM_DTL_STAT,
    MSKM_DTL.MSKM_DTL_CANCEL_RSN_CD
FROM
    KK_T_MSKM_DTL MSKM_DTL
WHERE
    MSKM_DTL.MSKM_DTL_NO = ?
AND
    MSKM_DTL.GENE_ADD_DTM = 
        (
            SELECT MAX(KK_T_MSKM_DTL_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX
            FROM    KK_T_MSKM_DTL KK_T_MSKM_DTL_RCNT
            WHERE   KK_T_MSKM_DTL_RCNT.MSKM_DTL_NO = MSKM_DTL.MSKM_DTL_NO
            AND     KK_T_MSKM_DTL_RCNT.MK_FLG = '0'
        )

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_022)
#--KKPRC00915
#--紹介者情報登録対象出力
#-----------------------------------------------------------

--KEY:KK_SELECT_022

SELECT   KK0021.MSKMSHO_DTL_NO,
         KK0021.MSKM_YMD
FROM    (SELECT   KK0021.MSKMSHO_DTL_NO,
                  KK0021.MSKM_YMD,
                  KK0021.GENE_ADD_DTM,
                  MAX(KK0021.GENE_ADD_DTM) OVER (PARTITION BY KK0021.MSKM_DTL_NO) AS MAX_GENE_DTM
         FROM     KK_T_MSKM_DTL KK0021
         WHERE    KK0021.MSKM_DTL_NO = ?
         AND      KK0021.MK_FLG = '0') KK0021
WHERE    KK0021.GENE_ADD_DTM = KK0021.MAX_GENE_DTM

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_023)
#--KKPRC00906
#--申込明細番号の世代MAX情報を取得
#-----------------------------------------------------------

--KEY:KK_SELECT_023

SELECT
    KK0021.MSKM_DTL_NO,
    KK0021.MSKM_YMD
FROM
    KK_T_MSKM_DTL KK0021
WHERE
    KK0021.MSKM_DTL_NO = ?
    AND KK0021.GENE_ADD_DTM = 
    (SELECT
        MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX
    FROM
        KK_T_MSKM_DTL KK0021_GENE
    WHERE
        KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
        AND KK0021_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_024)
#--KKPRC00902,KKPRC02202
#--申込明細番号より申込年月日取得
#-----------------------------------------------------------

--KEY:KK_SELECT_024

SELECT 
  KK0011.MSKM_NO,
  KK0011.MSKM_YMD,
  KK0021.MSKM_DTL_NO
 FROM   
  KK_T_MSKM_DTL KK0021
  INNER JOIN
   KK_T_MSKM KK0011
 ON
   KK0011.MSKM_NO = KK0021.MSKM_NO  
  AND  KK0011.GENE_ADD_DTM =  
      (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_MAX  
       FROM   KK_T_MSKM KK0011_GENE  
       WHERE  KK0011_GENE.MSKM_NO = KK0011.MSKM_NO   
       AND    KK0011_GENE.MK_FLG = '0')   
WHERE  
  KK0021.MSKM_DTL_NO = ?  
  AND  KK0021.GENE_ADD_DTM =  
      (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX  
       FROM   KK_T_MSKM_DTL KK0021_GENE  
       WHERE  KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO   
       AND    KK0021_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_025)
#--申込番号から住所変更時のネットコース変更の申込明細番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_025

SELECT
    DISTINCT KK0021.MSKM_DTL_NO
FROM
    KK_T_MSKM_DTL KK0021
INNER JOIN
    KK_T_IDO_RSV KK1681
ON  KK1681.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
AND KK1681.IDO_DIV = '00019'
AND KK1681.IDO_RSV_STAT_CD = '03'
AND RSV_APLY_YMD = '20991231'
INNER JOIN
    KK_T_SVC_KEI KK0081
ON  KK0081.SVC_KEI_NO= KK1681.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 GENE_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')
AND KK0081.SVC_CD = '01'
WHERE
    KK0021.GENE_ADD_DTM = (
     SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
     FROM   KK_T_MSKM_DTL KK0021_GENE
     WHERE  KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
     AND    KK0021_GENE.MK_FLG = '0')
AND KK0021.MSKM_NO = ?

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_026)
#--申込番号から住所変更時のネットコース変更の申込明細番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_026

SELECT
  KK0021.SYSID
 ,CK0011.EOID
FROM KK_T_MSKM_DTL KK0021
  LEFT JOIN (
    SELECT CK0011_P.SYSID,CK0011_P.EOID
    FROM   CK_T_CUST CK0011_P
    WHERE CK0011_P.MEMBER_SBT_CD IN ('00', '11', '21', '30')
    AND   CK0011_P.RSV_APLY_YMD || CK0011_P.GENE_ADD_DTM = (
      SELECT MAX(CK0011_G.RSV_APLY_YMD || CK0011_G.GENE_ADD_DTM)
      FROM   CK_T_CUST CK0011_G
      WHERE  CK0011_G.SYSID = CK0011_P.SYSID
      AND    CK0011_G.RSV_APLY_YMD <= ?
      AND    CK0011_G.RSV_APLY_CD = '2'
      AND    CK0011_G.MK_FLG = '0')) CK0011
  ON CK0011.SYSID = KK0021.SYSID
WHERE KK0021.MSKMSHO_NO = ?
AND   KK0021.GENE_ADD_DTM = (
  SELECT MAX(KK0021_G.GENE_ADD_DTM)
  FROM   KK_T_MSKM_DTL KK0021_G
  WHERE  KK0021_G.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
  AND    KK0021_G.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_027)
#--加入御礼書管理データ抽出
#--申込明細番号から出力対象の申込情報を検索する
#-----------------------------------------------------------

--KEY:KK_SELECT_027

SELECT      KK0011.MSKM_NO,
            KK0011.GENE_ADD_DTM AS KK0011_GENE_ADD_DTM,
            KK0011.MSKM_UK_DTM,
            KK0011.KEPCO_CUSTINFO_JUJU_DOI_UM,
            KK0011.MSKMSHO_ARIV_YMD,
            KK0021.MSKM_DTL_NO,
            KK0021.GENE_ADD_DTM AS KK0021_GENE_ADD_DTM,
            KK0021.MSKMSHO_DTL_NO,
            KK0021.MSKMSHO_NO,
            KK0021.MSKM_FORM_CD,
            KK0021.OP_SVC_HKTGI_UM,
            KK0021.DNSKH_DOI_UM
FROM        KK_T_MSKM KK0011
INNER JOIN (SELECT   KK0021.MSKM_NO,
                     KK0021.MSKM_DTL_NO,
                     KK0021.GENE_ADD_DTM,
                     KK0021.MSKMSHO_DTL_NO,
                     KK0021.MSKMSHO_NO,
                     KK0021.MSKM_FORM_CD,
                     KK0021.OP_SVC_HKTGI_UM,
                     MAX(KK0021.GENE_ADD_DTM) OVER (PARTITION BY KK0021.MSKM_DTL_NO) AS MAX_GENE_DTM,
                     KK0021.DNSKH_DOI_UM
            FROM     KK_T_MSKM_DTL KK0021
            WHERE    KK0021.MSKM_DTL_NO = ?
            AND      KK0021.MK_FLG = '0') KK0021 ON
            KK0021.MSKM_NO = KK0011.MSKM_NO AND
            KK0021.GENE_ADD_DTM = KK0021.MAX_GENE_DTM
WHERE      (KK0011.MSKM_NO , KK0011.GENE_ADD_DTM) = (SELECT   KK0011_G.MSKM_NO,
                                                              MAX(KK0011_G.GENE_ADD_DTM)
                                                     FROM     KK_T_MSKM KK0011_G
                                                     WHERE    KK0011_G.MSKM_NO = KK0011.MSKM_NO
                                                     AND      KK0011_G.MK_FLG = '0'
                                                     GROUP BY KK0011_G.MSKM_NO)

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_028)
#--番ポなし番号事前通知データ抽出
#--申込明細番号にひもづくデータ抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_028

SELECT
    KK0021.MSKMSHO_NO
   ,KK0021.MSKMSHO_DTL_NO
FROM KK_T_MSKM KK0011
  INNER JOIN KK_T_MSKM_DTL KK0021
  ON  KK0011.MSKM_NO = KK0021.MSKM_NO
WHERE KK0021.MSKM_DTL_NO = ?
AND   KK0011.GENE_ADD_DTM = (
  SELECT MAX(KK0011_G.GENE_ADD_DTM)
  FROM   KK_T_MSKM KK0011_G
  WHERE  KK0011_G.MSKM_NO = KK0011.MSKM_NO
  AND    KK0011_G.MK_FLG = '0')
AND KK0021.GENE_ADD_DTM = (
  SELECT MAX(KK0021_G.GENE_ADD_DTM)
  FROM    KK_T_MSKM_DTL KK0021_G
  WHERE   KK0021_G.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
  AND     KK0021_G.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_029)
#--番ポなし番号事前通知データ抽出
#--サービス契約番号が異動区分「住所変更・登録」中かチェック
#-----------------------------------------------------------

--KEY:KK_SELECT_029

SELECT
   KK2091.ITENS_SVKEI_KISUW_NO
  ,KK2101.ADCHG_NO
  ,KK2101.CHBF_SKBT_NO
  ,KK2101.CHAF_SKBT_NO
  ,KK2101.ADCHG_DTL_SBT_CD
FROM KK_T_ADCHG KK2091
  INNER JOIN KK_T_ADCHG_DTL KK2101
  ON  KK2101.ADCHG_NO = KK2091.ADCHG_NO
WHERE KK2091.ADCHG_STAT = '001'
AND   KK2101.CHAF_SKBT_NO = ?
AND   KK2091.MK_FLG = '0'
AND   KK2101.ADCHG_DTL_SBT_CD = '01'
AND   KK2101.MK_FLG = '0'

#-


#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_030)
#--サービス契約番号より新規追加の申込年月日を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_030

SELECT
    MS.MSKM_YMD
FROM
    KK_T_MSKM_DTL MS
INNER JOIN
    KK_T_SVC_KEI SK
ON
    SK.MSKM_DTL_NO = MS.MSKM_DTL_NO
WHERE
    SK.SVC_KEI_NO = ?
AND SK.RSV_APLY_YMD || SK.GENE_ADD_DTM =
   (SELECT
        MIN(SK_GENE.RSV_APLY_YMD || SK_GENE.GENE_ADD_DTM) AS SK_MIN
    FROM
        KK_T_SVC_KEI SK_GENE
    WHERE
        SK_GENE.SVC_KEI_NO = SK.SVC_KEI_NO
    AND SK_GENE.IDO_DIV IN('00001','00002')
    AND SK_GENE.RSV_APLY_YMD <= ?
    AND SK_GENE.RSV_APLY_CD = '2'
    AND SK_GENE.MK_FLG = '0')
AND
    MS.GENE_ADD_DTM =
   (SELECT
        MAX(MSKM_DTL_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
    FROM
        KK_T_MSKM_DTL MSKM_DTL_GENE
    WHERE
        MSKM_DTL_GENE.MSKM_DTL_NO = MS.MSKM_DTL_NO
    AND MSKM_DTL_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_031)
#--KKPRC02201
#--申込明細番号より住所変更明細情報取得
#-----------------------------------------------------------

--KEY:KK_SELECT_031

SELECT 
    KK2101.ADCHG_NO,
    KK2101.ADCHG_DTL_NO,
    KK2101.CHAF_SKBT_NO
FROM
    KK_T_MSKM_DTL KK0021
  INNER JOIN
    KK_T_ADCHG KK2091
  ON
    KK2091.MSKM_NO = KK0021.MSKM_NO  
  AND
    KK2091.MK_FLG = '0'
  INNER JOIN
    KK_T_ADCHG_DTL KK2101
  ON
    KK2101.ADCHG_NO = KK2091.ADCHG_NO 
  AND
    KK2101.ADCHG_DTL_SBT_CD = '08' 
  AND
    KK2101.MK_FLG = '0'
WHERE
    KK0021.MSKM_DTL_NO = ?
    AND KK0021.GENE_ADD_DTM = 
    (SELECT
        MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX
    FROM
        KK_T_MSKM_DTL KK0021_GENE
    WHERE
        KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
        AND KK0021_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_032)
#--KKPRC02201
#--サービス契約番号より最小の申込明細番号を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_032

SELECT
    KK0021.MSKM_YMD
FROM KK_T_SVC_KEI KK0081_MIN
    INNER JOIN KK_T_MSKM_DTL KK0021 ON KK0081_MIN.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
WHERE KK0081_MIN.SVC_KEI_STAT <> '920'
AND KK0081_MIN.SVC_KEI_NO  = ?
AND KK0081_MIN.RSV_APLY_YMD || KK0081_MIN.GENE_ADD_DTM =
    (SELECT MIN(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MIN
       FROM   KK_T_SVC_KEI KK0081_GENE
       WHERE  KK0081_GENE.SVC_KEI_NO = KK0081_MIN.SVC_KEI_NO
       AND    KK0081_GENE.RSV_APLY_YMD <= ?
       AND    KK0081_GENE.RSV_APLY_CD = '2'
       AND    KK0081_GENE.MK_FLG = '0')
AND KK0021.GENE_ADD_DTM = 
    (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS GENE_MAX
       FROM   KK_T_MSKM_DTL KK0021_GENE
       WHERE  KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
       AND    KK0021_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_033)
#--KKPRC07001
#--申込明細番号より申込明細情報を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_033

SELECT   KK0021.MSKMSHO_NO
FROM     KK_T_MSKM_DTL KK0021
WHERE    KK0021.MSKM_DTL_NO = ?
AND     (KK0021.MSKM_DTL_NO , KK0021.GENE_ADD_DTM) =
        (SELECT   KK0021_GENE.MSKM_DTL_NO , MAX(KK0021_GENE.GENE_ADD_DTM)
         FROM     KK_T_MSKM_DTL KK0021_GENE
         WHERE    KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
         AND      KK0021_GENE.MK_FLG = '0'
         GROUP BY KK0021_GENE.MSKM_DTL_NO)

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_034)
#--KKPRC02204
#--申込明細番号より工事完了かプラン変更(工事なし)の工事案件情報を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_034

SELECT 
    KK0021.MSKM_YMD,
    KU0011.KOJIAK_NO
FROM 
    KK_T_MSKM_DTL KK0021 
LEFT OUTER JOIN 
    KU_T_MSKM_DTL_KOJIAK KU0091
ON
    KU0091.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
    AND KU0091.MK_FLG = '0'
LEFT OUTER JOIN
    KU_T_KOJIAK KU0011
ON
    KU0011.KOJIAK_NO = KU0091.KOJIAK_NO
    AND (
            (
             (KU0011.KOJIAK_STAT = '200') OR (KU0011.KOJIAK_STAT = '120' AND KU0011.MANS_KOJIAK_STAT_CD = '200')
            )
             OR
             (KU0011.KOJIAK_STAT = '120' AND KU0011.KOJIAK_SBT_CD = '010')
        )
    AND KU0011.MK_FLG = '0'
WHERE 
    KK0021.MSKM_DTL_NO = ? AND
    KK0021.GENE_ADD_DTM = 
      (SELECT
         MAX(KK0021_GENE.GENE_ADD_DTM) AS MAX
       FROM
         KK_T_MSKM_DTL KK0021_GENE
       WHERE
         KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND
         KK0021_GENE.MK_FLG = '0' )

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_036)
#--申込明細番号に紐づく申込明細情報を取得する。
#--KKPRC10401にて使用
#-----------------------------------------------------------

--KEY:KK_SELECT_036

SELECT 
  /*KK_T_MSKM_DTL.SQL KK_SELECT_036*/
  KK0021.MSKM_DTL_NO,
  KK0021.GENE_ADD_DTM,
  KK0021.MSKM_NO,
  KK0021.MSKMSHO_NO,
  NVL(KK0021.KOJI_PRG_TCHI_MLAD, ' ') KOJI_PRG_TCHI_MLAD,
  NVL(KK0021.KJSC_TCH_KTAI_MLAD, ' ') KJSC_TCH_KTAI_MLAD, 
  NVL(KK0021.ML_SEND_JGI_FLG, ' ') ML_SEND_JGI_FLG,
  KK0021.MK_FLG,
  KK0021.MSKMSHO_DTL_NO
FROM 
  (
    SELECT
      KK0021_GENE.MSKM_DTL_NO,
      KK0021_GENE.GENE_ADD_DTM,
      KK0021_GENE.MSKM_NO,
      KK0021_GENE.MSKMSHO_NO,
      KK0021_GENE.KOJI_PRG_TCHI_MLAD,
      KK0021_GENE.KJSC_TCH_KTAI_MLAD,
      KK0021_GENE.ML_SEND_JGI_FLG,
      KK0021_GENE.MK_FLG,
      KK0021_GENE.MSKMSHO_DTL_NO,
      MAX(KK0021_GENE.GENE_ADD_DTM) OVER(PARTITION BY KK0021_GENE.MSKM_DTL_NO) GENE_ADD_DTM_MAX
    FROM
      KK_T_MSKM_DTL KK0021_GENE
    WHERE
      KK0021_GENE.MSKM_DTL_NO = ?
    AND
      KK0021_GENE.MK_FLG = '0'
  ) KK0021
WHERE
  KK0021.GENE_ADD_DTM_MAX = KK0021.GENE_ADD_DTM
  AND (KK0021.ML_SEND_JGI_FLG IS NULL OR KK0021.ML_SEND_JGI_FLG = '0')
  AND (KK0021.KOJI_PRG_TCHI_MLAD IS NOT NULL OR KK0021.KJSC_TCH_KTAI_MLAD IS NOT NULL)

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_UPDATE_001)
#--指定申込明細番号のカレントレコードの進捗メール最終送信年月日時分秒を更新する。
#-----------------------------------------------------------

--KEY:KK_UPDATE_001

UPDATE
    KK_T_MSKM_DTL KK0021
SET
    KK0021.PRG_ML_LAST_SEND_DTM = ?,
    KK0021.UPD_DTM = 'UPD_DTM',
    KK0021.UPD_UNYO_YMD = 'UPD_UNYO',
    KK0021.UPD_TRN_ID = 'UPD_TRN',
    KK0021.UPD_OPEACNT = 'UPD_OPE'
WHERE
    KK0021.MSKM_DTL_NO = ? AND
    KK0021.GENE_ADD_DTM = 
    (SELECT
        MAX(IN_KK0021.GENE_ADD_DTM) AS MAX_GENE_ADD_DTM
    FROM
        KK_T_MSKM_DTL IN_KK0021
    WHERE
        IN_KK0021.MSKM_DTL_NO = KK0021.MSKM_DTL_NO AND
        IN_KK0021.MK_FLG = '0'
    ) AND
    KK0021.MK_FLG = '0'

#-

#-------------------------FU--------------------------------

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-FU_SELECT_001)
#--申込明細テーブルより、サービス契約テーブル、紹介テーブルに紐づく、申込完了メールが未送信、かつキャンセルメールが未送信である「申込明細番号」を取得する。
#-----------------------------------------------------------

--KEY:FU_SELECT_001

SELECT
    KK0311.SHOKAISHA_NM,
    KK0311.SHOKAISHA_RRKS_MLAD,
    KK0311.INTR_CD,
    KK0311.HISHOKAI_NM,
    KK0311.HISHOKAI_RRKS_MLAD,
    KK0311.SHOKAISHA_AD_STATE_NM,
    KK0311.SHOKAISHA_AD_CITY_NM,
    KK0311.SHOKAISHA_AD_OAZTSU_NM,
    KK0311.SHOKAISHA_AD_AZCHO_NM,
    KK0311.SHOKAISHA_AD_BNCHIGO,
    KK0311.SHOKAISHA_ADRTTM,
    KK0311.SHOKAISHA_ADRRM,
    KK0311.SVC_KEI_NO AS INTR_SVC_KEI_NO,
    KK0021.MSKM_DTL_NO,
    KK0021.MSKMSHO_NO,
    KK0081.SVC_KEI_NO,
    KK0081.SVC_KEI_STAT,
    KK0081.SVC_CD
FROM
    KK_T_INTR KK0311
    INNER JOIN KK_T_SVC_KEI KK0081
    ON KK0311.INTR_CD = KK0081.INTR_CD
    AND KK0311.MSKM_ML_SEND_CD = '0'
    AND KK0311.CANCEL_ML_SEND_CD = '0'
    AND KK0311.MSKM_TCH_ML_SEND_DTM IS NULL
    AND KK0311.CANCEL_TCH_ML_SEND_DTM IS NULL
    AND KK0311.MK_FLG = '0'
    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.PRC_GRP_CD = '02'
    AND KK0081.MK_FLG = '0'
    INNER JOIN KK_T_SVC_KEI KK0081_01
    ON KK0081.SVC_KEI_NO = KK0081_01.SVC_KEI_NO
    AND (KK0081_01.SVC_KEI_NO,KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) =
            (
            SELECT
                KK0081_GENE_01.SVC_KEI_NO,MIN(KK0081_GENE_01.RSV_APLY_YMD || KK0081_GENE_01.GENE_ADD_DTM) AS KK0081_MIN
            FROM
                KK_T_SVC_KEI KK0081_GENE_01
            WHERE
                KK0081_GENE_01.SVC_KEI_NO = KK0081_01.SVC_KEI_NO
                AND KK0081_GENE_01.RSV_APLY_YMD <= ?
                AND KK0081_GENE_01.RSV_APLY_CD = '2'
                AND KK0081_GENE_01.MK_FLG = '0'
            GROUP BY KK0081_GENE_01.SVC_KEI_NO
            )
    AND KK0081_01.PRC_GRP_CD = '02'
    AND KK0081_01.MK_FLG = '0'
    INNER JOIN KK_T_MSKM_DTL KK0021
    ON KK0081_01.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
    AND (KK0021.MSKM_DTL_NO,KK0021.GENE_ADD_DTM) =
            (
            SELECT
                KK0021_GENE.MSKM_DTL_NO,MAX(KK0021_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
            FROM
                KK_T_MSKM_DTL KK0021_GENE
            WHERE
                KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
                AND KK0021_GENE.MK_FLG = '0'
            GROUP BY KK0021_GENE.MSKM_DTL_NO
            )
    AND KK0021.MK_FLG = '0'
ORDER BY KK0311.INTR_CD

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-FU_SELECT_002)
#--申込明細テーブルより、サービス契約テーブル、紹介テーブルに紐づく、キャンセルメールが送信である「申込明細番号」を取得する。
#-----------------------------------------------------------

--KEY:FU_SELECT_002

SELECT
    KK0311.SHOKAISHA_NM,
    KK0311.SHOKAISHA_RRKS_MLAD,
    KK0311.INTR_CD,
    KK0311.HISHOKAI_NM,
    KK0311.HISHOKAI_RRKS_MLAD,
    KK0311.SHOKAISHA_AD_STATE_NM,
    KK0311.SHOKAISHA_AD_CITY_NM,
    KK0311.SHOKAISHA_AD_OAZTSU_NM,
    KK0311.SHOKAISHA_AD_AZCHO_NM,
    KK0311.SHOKAISHA_AD_BNCHIGO,
    KK0311.SHOKAISHA_ADRTTM,
    KK0311.SHOKAISHA_ADRRM,
    KK0311.SVC_KEI_NO AS INTR_SVC_KEI_NO,
    KK0311.MSKM_TCH_ML_SEND_DTM,
    KK0021.MSKM_DTL_NO,
    KK0021.MSKMSHO_NO,
    KK0081.SVC_KEI_NO,
    KK0081.SVC_KEI_STAT,
    KK0081.SVC_CD
FROM
    KK_T_INTR KK0311
    INNER JOIN KK_T_SVC_KEI KK0081
    ON KK0311.INTR_CD = KK0081.INTR_CD
    AND KK0311.MSKM_ML_SEND_CD = '1'
    AND KK0311.CANCEL_ML_SEND_CD = '0'
    AND KK0311.CANCEL_TCH_ML_SEND_DTM IS NULL
    AND KK0311.MK_FLG = '0'
    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.PRC_GRP_CD = '02'
    AND KK0081.MK_FLG = '0'
    INNER JOIN KK_T_SVC_KEI KK0081_01
    ON KK0081.SVC_KEI_NO = KK0081_01.SVC_KEI_NO
    AND (KK0081_01.SVC_KEI_NO,KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) =
            (
            SELECT
                KK0081_GENE_01.SVC_KEI_NO,MIN(KK0081_GENE_01.RSV_APLY_YMD || KK0081_GENE_01.GENE_ADD_DTM) AS KK0081_MIN
            FROM
                KK_T_SVC_KEI KK0081_GENE_01
            WHERE
                KK0081_GENE_01.SVC_KEI_NO = KK0081_01.SVC_KEI_NO
                AND KK0081_GENE_01.RSV_APLY_YMD <= ?
                AND KK0081_GENE_01.RSV_APLY_CD = '2'
                AND KK0081_GENE_01.MK_FLG = '0'
            GROUP BY KK0081_GENE_01.SVC_KEI_NO
            )
    AND KK0081_01.PRC_GRP_CD = '02'
    AND KK0081_01.MK_FLG = '0'
    INNER JOIN KK_T_MSKM_DTL KK0021
    ON KK0081_01.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
    AND (KK0021.MSKM_DTL_NO,KK0021.GENE_ADD_DTM) =
            (
            SELECT
                KK0021_GENE.MSKM_DTL_NO,MAX(KK0021_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
            FROM
                KK_T_MSKM_DTL KK0021_GENE
            WHERE
                KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
                AND KK0021_GENE.MK_FLG = '0'
            GROUP BY KK0021_GENE.MSKM_DTL_NO
            )
    AND KK0021.MK_FLG = '0'
ORDER BY KK0311.INTR_CD


#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-FU_SELECT_003)
#--申込明細テーブルより、サービス契約テーブル、紹介テーブルに紐づく、メゾンタイプの完了通知メール送信対象を取得する。
#-----------------------------------------------------------

--KEY:FU_SELECT_003

SELECT
    KK0311.SHOKAISHA_NM,
    KK0311.SHOKAISHA_RRKS_MLAD,
    KK0311.INTR_CD,
    KK0311.HISHOKAI_NM,
    KK0311.HISHOKAI_RRKS_MLAD,
    KK0311.SHOKAISHA_AD_STATE_NM,
    KK0311.SHOKAISHA_AD_CITY_NM,
    KK0311.SHOKAISHA_AD_OAZTSU_NM,
    KK0311.SHOKAISHA_AD_AZCHO_NM,
    KK0311.SHOKAISHA_AD_BNCHIGO,
    KK0311.SHOKAISHA_ADRTTM,
    KK0311.SHOKAISHA_ADRRM,
    KK0311.SVC_KEI_NO AS INTR_SVC_KEI_NO,
    KK0021.MSKM_DTL_NO,
    KK0021.MSKMSHO_NO,
    KK0081.SVC_KEI_NO,
    KK0081.SVC_KEI_STAT,
    KK0081.SVC_CD
FROM
    KK_T_INTR KK0311
    INNER JOIN KK_T_SVC_KEI KK0081
    ON KK0311.INTR_CD = KK0081.INTR_CD
    AND KK0311.MSKM_ML_SEND_CD = '0'
    AND KK0311.CANCEL_ML_SEND_CD = '0'
    AND KK0311.MSKM_TCH_ML_SEND_DTM IS NULL
    AND KK0311.CANCEL_TCH_ML_SEND_DTM IS NULL
    AND KK0311.MK_FLG = '0'
    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.PRC_GRP_CD = '03'
    AND KK0081.MK_FLG = '0'
    INNER JOIN KK_T_SVC_KEI KK0081_01
    ON KK0081.SVC_KEI_NO = KK0081_01.SVC_KEI_NO
    AND (KK0081_01.SVC_KEI_NO,KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) =
            (
            SELECT
                KK0081_GENE_01.SVC_KEI_NO,MIN(KK0081_GENE_01.RSV_APLY_YMD || KK0081_GENE_01.GENE_ADD_DTM) AS KK0081_MIN
            FROM
                KK_T_SVC_KEI KK0081_GENE_01
            WHERE
                KK0081_GENE_01.SVC_KEI_NO = KK0081_01.SVC_KEI_NO
                AND KK0081_GENE_01.RSV_APLY_YMD <= ?
                AND KK0081_GENE_01.RSV_APLY_CD = '2'
                AND KK0081_GENE_01.MK_FLG = '0'
            GROUP BY KK0081_GENE_01.SVC_KEI_NO
            )
    AND KK0081_01.PRC_GRP_CD = '03'
    AND KK0081_01.MK_FLG = '0'
    INNER JOIN KK_T_MSKM_DTL KK0021
    ON KK0081_01.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
    AND (KK0021.MSKM_DTL_NO,KK0021.GENE_ADD_DTM) =
            (
            SELECT
                KK0021_GENE.MSKM_DTL_NO,MAX(KK0021_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
            FROM
                KK_T_MSKM_DTL KK0021_GENE
            WHERE
                KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
                AND KK0021_GENE.MK_FLG = '0'
            GROUP BY KK0021_GENE.MSKM_DTL_NO
            )
    AND KK0021.MK_FLG = '0'
ORDER BY KK0311.INTR_CD


#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-FU_SELECT_004)
#--申込明細テーブルより、サービス契約テーブル、紹介テーブルに紐づく、マンションタイプの完了通知メール送信対象を取得する。
#-----------------------------------------------------------

--KEY:FU_SELECT_004
SELECT
    KK0311.SHOKAISHA_NM,
    KK0311.SHOKAISHA_RRKS_MLAD,
    KK0311.INTR_CD,
    KK0311.HISHOKAI_NM,
    KK0311.HISHOKAI_RRKS_MLAD,
    KK0311.SHOKAISHA_AD_STATE_NM,
    KK0311.SHOKAISHA_AD_CITY_NM,
    KK0311.SHOKAISHA_AD_OAZTSU_NM,
    KK0311.SHOKAISHA_AD_AZCHO_NM,
    KK0311.SHOKAISHA_AD_BNCHIGO,
    KK0311.SHOKAISHA_ADRTTM,
    KK0311.SHOKAISHA_ADRRM,
    KK0311.SVC_KEI_NO AS INTR_SVC_KEI_NO,
    KK0021.MSKM_DTL_NO,
    KK0021.MSKMSHO_NO,
    KK0081.SVC_KEI_NO,
    KK0081.SVC_KEI_STAT,
    KK0081.SVC_CD
FROM
    KK_T_INTR KK0311
    INNER JOIN KK_T_SVC_KEI KK0081
    ON KK0311.INTR_CD = KK0081.INTR_CD
    AND KK0311.MSKM_ML_SEND_CD = '0'
    AND KK0311.CANCEL_ML_SEND_CD = '0'
    AND KK0311.MSKM_TCH_ML_SEND_DTM IS NULL
    AND KK0311.CANCEL_TCH_ML_SEND_DTM IS NULL
    AND KK0311.MK_FLG = '0'
    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.MK_FLG = '0'
    INNER JOIN KK_T_SVC_KEI KK0081_01
    ON KK0081.SVC_KEI_NO = KK0081_01.SVC_KEI_NO
    AND (KK0081_01.SVC_KEI_NO,KK0081_01.RSV_APLY_YMD || KK0081_01.GENE_ADD_DTM) =
            (
            SELECT
                KK0081_GENE_01.SVC_KEI_NO,MIN(KK0081_GENE_01.RSV_APLY_YMD || KK0081_GENE_01.GENE_ADD_DTM) AS KK0081_MIN
            FROM
                KK_T_SVC_KEI KK0081_GENE_01
            WHERE
                KK0081_GENE_01.SVC_KEI_NO = KK0081_01.SVC_KEI_NO
                AND KK0081_GENE_01.RSV_APLY_YMD <= ?
                AND KK0081_GENE_01.RSV_APLY_CD = '2'
                AND KK0081_GENE_01.MK_FLG = '0'
            GROUP BY KK0081_GENE_01.SVC_KEI_NO
            )
    AND KK0081_01.MK_FLG = '0'
    INNER JOIN KK_T_MSKM_DTL KK0021
    ON KK0081_01.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
    AND (KK0021.MSKM_DTL_NO,KK0021.GENE_ADD_DTM) =
            (
            SELECT
                KK0021_GENE.MSKM_DTL_NO,MAX(KK0021_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
            FROM
                KK_T_MSKM_DTL KK0021_GENE
            WHERE
                KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
                AND KK0021_GENE.MK_FLG = '0'
            GROUP BY KK0021_GENE.MSKM_DTL_NO
            )
    AND KK0021.MK_FLG = '0'
    AND (KK0081.PRC_GRP_CD = '04'
       OR (KK0081.PRC_GRP_CD = '10' AND KK0021.MSKMSHO_SBT_CD = '2'))
    AND (KK0081_01.PRC_GRP_CD = '04'
       OR (KK0081_01.PRC_GRP_CD = '10' AND KK0021.MSKMSHO_SBT_CD = '2'))
ORDER BY KK0311.INTR_CD

#-------------------------FU_END--------------------------------


#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KV_SELECT_001)
#--申込明細番号より申込年月日取得
#-----------------------------------------------------------

--KEY:KV_SELECT_001

SELECT 
  KK0011.MSKM_NO,
  KK0011.MSKM_YMD,
  KK0021.MSKM_DTL_NO
 FROM   
  KK_T_MSKM_DTL KK0021
  INNER JOIN
   KK_T_MSKM KK0011
 ON
   KK0011.MSKM_NO = KK0021.MSKM_NO  
  AND  KK0011.GENE_ADD_DTM =  
      (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_MAX  
       FROM   KK_T_MSKM KK0011_GENE  
       WHERE  KK0011_GENE.MSKM_NO = KK0011.MSKM_NO   
       AND    KK0011_GENE.MK_FLG = '0')   
WHERE  
  KK0021.MSKM_DTL_NO = ?  
  AND  KK0021.GENE_ADD_DTM =  
      (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX  
       FROM   KK_T_MSKM_DTL KK0021_GENE  
       WHERE  KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO   
       AND    KK0021_GENE.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KV_SELECT_001)
#--申込明細番号から申込形態を取得する
#-----------------------------------------------------------

--KEY:KV_SELECT_001

SELECT
    KK0021.MSKM_FORM_CD
FROM
    KK_T_MSKM_DTL KK0021
WHERE
    KK0021.MSKM_DTL_NO = ?
AND KK0021.GENE_ADD_DTM = (SELECT MAX(KK0021_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX
    FROM
        KK_T_MSKM_DTL KK0021_RCNT
    WHERE
        KK0021_RCNT.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
    AND KK0021_RCNT.MK_FLG = '0')

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KV_SELECT_002)
#--対象の申込明細番号の申込形態が「訪販」であることを確認する
#-----------------------------------------------------------

--KEY:KV_SELECT_002

SELECT
    KK0021.MSKM_DTL_NO
FROM
    KK_T_MSKM_DTL KK0021
WHERE
    KK0021.MSKM_DTL_NO = ?
AND KK0021.GENE_ADD_DTM = (SELECT MAX(KK0021_RCNT.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX
    FROM
        KK_T_MSKM_DTL KK0021_RCNT
    WHERE
        KK0021_RCNT.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
    AND KK0021_RCNT.MK_FLG = '0')
AND KK0021.MSKM_FORM_CD = '03'

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KV_SELECT_003)
#--割引サービス契約番号に紐付く申込明細の申込年月日を取得する
#-----------------------------------------------------------

--KEY:KV_SELECT_003

SELECT  KK0021.MSKM_YMD
FROM  ( SELECT  MSKM_DTL_NO 
        FROM    KK_T_WRIB_SVC_KEI KK0451
        WHERE   KK0451.WRIB_SVC_KEI_NO = ?
        AND     ( KK0451.WRIB_SVC_KEI_NO , KK0451.RSV_APLY_YMD || KK0451.GENE_ADD_DTM ) = 
                                                             ( SELECT  KK0451_GENE.WRIB_SVC_KEI_NO , MAX(KK0451_GENE.RSV_APLY_YMD || KK0451_GENE.GENE_ADD_DTM) AS KK0451_MAX
                                                               FROM    KK_T_WRIB_SVC_KEI KK0451_GENE
                                                               WHERE   KK0451_GENE.WRIB_SVC_KEI_NO = KK0451.WRIB_SVC_KEI_NO
                                                               AND     KK0451_GENE.RSV_APLY_YMD <= ?
                                                               AND     KK0451_GENE.RSV_APLY_CD = '2'
                                                               AND     KK0451_GENE.MK_FLG = '0'
                                                               GROUP BY KK0451_GENE.WRIB_SVC_KEI_NO )
      ) KK0451
INNER JOIN ( SELECT  KK0021.MSKM_DTL_NO,
                     KK0021.MSKM_YMD
             FROM    KK_T_MSKM_DTL KK0021
             WHERE   ( KK0021.MSKM_DTL_NO , KK0021.GENE_ADD_DTM ) = 
                                           ( SELECT  KK0021_GENE.MSKM_DTL_NO , MAX(KK0021_GENE.GENE_ADD_DTM) AS KK_T_MSKM_DTL_MAX
                                             FROM    KK_T_MSKM_DTL KK0021_GENE
                                             WHERE   KK0021.MSKM_DTL_NO = KK0021_GENE.MSKM_DTL_NO
                                             AND     KK0021_GENE.MK_FLG = '0'
                                             GROUP BY KK0021_GENE.MSKM_DTL_NO )
           ) KK0021 ON KK0021.MSKM_DTL_NO = KK0451.MSKM_DTL_NO

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KV_SELECT_004)
#--契約異動検証_機器出荷一時金生成チェック
#--(2)-(B)-(c)
#-----------------------------------------------------------

--KEY:KV_SELECT_004

SELECT
        KK0021.SYSID
        ,KK0021.MSKM_YMD
FROM
        KK_T_MSKM_DTL KK0021
WHERE
        KK0021.MSKM_DTL_NO = ?
AND     KK0021.GENE_ADD_DTM =
       (
        SELECT
               MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX
        FROM
               KK_T_MSKM_DTL KK0021_GENE
        WHERE
               KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
        AND    KK0021_GENE.MK_FLG = '0'
        AND    KK0021_GENE.MSKM_YMD <= ?
        GROUP BY KK0021_GENE.MSKM_DTL_NO
        )

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_035)
#--申込明細番号を元に申込明細に紐付く情報を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_035

SELECT
    KK0021.MSKM_NO,
    KK0021.MSKM_DTL_NO
FROM KK_T_MSKM_DTL KK0021
WHERE KK0021.GENE_ADD_DTM = (
    SELECT MAX(KK0021_GENE.GENE_ADD_DTM)
    FROM    KK_T_MSKM_DTL KK0021_GENE
    WHERE   KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
    AND     KK0021_GENE.MK_FLG = '0')
AND KK0021.MSKM_DTL_NO = ?

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_037)
#--KKPRC12607
#--申込番号とサービス契約番号（異動区分が住所変更）に紐付く申込明細番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_037

SELECT KK0021.MSKM_DTL_NO from KK_T_MSKM_DTL KK0021

INNER JOIN
    KK_T_SVC_KEI KK0081
ON  KK0081.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
AND KK0021.MSKM_NO = ?
AND KK0021.MK_FLG = '0'
AND KK0081.SVC_KEI_NO = ?
AND KK0081.IDO_DIV = '00019'
AND KK0081.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_038)
#--サービス契約番号から申込時の申込書番号を取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_038

SELECT KTMSD.MSKMSHO_NO
FROM KK_T_SVC_KEI KTSK
     INNER JOIN KK_T_MSKM_DTL KTMSD
     ON KTMSD.MSKM_DTL_NO = KTSK.MSKM_DTL_NO
WHERE KTSK.SVC_KEI_NO = ?
AND (KTSK.SVC_KEI_NO,KTSK.RSV_APLY_YMD || KTSK.GENE_ADD_DTM) =
    (SELECT KTSK_GENE.SVC_KEI_NO,MIN(KTSK_GENE.RSV_APLY_YMD || KTSK_GENE.GENE_ADD_DTM) AS KTSK_MAX
     FROM KK_T_SVC_KEI KTSK_GENE
     WHERE KTSK_GENE.SVC_KEI_NO = KTSK.SVC_KEI_NO
     AND KTSK_GENE.IDO_DIV IN('00001','00002')
     AND KTSK_GENE.RSV_APLY_YMD <= ?
     AND KTSK_GENE.RSV_APLY_CD = '2'
     AND KTSK_GENE.MK_FLG = '0'
     GROUP BY KTSK_GENE.SVC_KEI_NO)
AND (KTMSD.MSKM_DTL_NO,KTMSD.GENE_ADD_DTM) = 
    (SELECT KTMSD_GENE.MSKM_DTL_NO,MAX(KTMSD_GENE.GENE_ADD_DTM) AS KTMSD_MAX
     FROM KK_T_MSKM_DTL KTMSD_GENE
     WHERE KTMSD_GENE.MSKM_DTL_NO = KTMSD.MSKM_DTL_NO
     AND KTMSD_GENE.MK_FLG = '0'
     GROUP BY KTMSD_GENE.MSKM_DTL_NO)

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_039)
#--基準となる年月日時分秒以降の同時申込が行われた
#--mineo、電気の契約情報を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_039

SELECT
    KK0021.MSKM_DTL_NO
  , KK0021.MSKM_YMD
  , KK0021.DOJI_MSKM_EOH_MSKMSHO_NO
  , KK0081.SVC_KEI_NO
  , KK0081.PRC_GRP_CD
FROM
    (SELECT
         KK0021_A.MSKM_DTL_NO
       , KK0021_A.MSKM_YMD
       , KK0021_A.DOJI_MSKM_EOH_MSKMSHO_NO
     FROM
         KK_T_MSKM_DTL KK0021_A
     WHERE
         KK0021_A.DOJI_MSKM_EOH_MSKMSHO_NO IS NOT NULL
     AND
         KK0021_A.UPD_DTM >= TO_CHAR(TO_TIMESTAMP(?, 'YYYYMMDDHH24MISSFF3') - NUMTODSINTERVAL(TO_NUMBER(1), 'HOUR'), 'YYYYMMDDHH24MISSFF3')
     AND
         KK0021_A.GENE_ADD_DTM = (SELECT
                                      MIN(KK0021_MIN.GENE_ADD_DTM)
                                  FROM
                                      KK_T_MSKM_DTL KK0021_MIN
                                  WHERE
                                      KK0021_MIN.MSKM_DTL_NO = KK0021_A.MSKM_DTL_NO
                                  AND
                                      KK0021_MIN.MK_FLG = '0')) KK0021
INNER JOIN
    (SELECT
         KK0081_A.SVC_KEI_NO
       , KK0081_A.PRC_GRP_CD
       , KK0081_A.MSKM_DTL_NO
     FROM
         KK_T_SVC_KEI KK0081_A
     WHERE
         KK0081_A.RSV_APLY_YMD || KK0081_A.GENE_ADD_DTM = (SELECT
                                                               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_A.SVC_KEI_NO
                                                           AND
                                                               KK0081_GENE.RSV_APLY_YMD <= ?
                                                           AND
                                                               KK0081_GENE.RSV_APLY_CD = '2'
                                                           AND
                                                               KK0081_GENE.MK_FLG = '0')) KK0081
ON
    KK0021.MSKM_DTL_NO = KK0081.MSKM_DTL_NO

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_040)
#--申込書番号、代理店コードから、
#--同時申込が行われたeo光の契約情報を取得する
#-----------------------------------------------------------

--KEY:KK_SELECT_040

SELECT
    KK0071.MSKM_NO
  , KK0071.MSKM_AGNT_NO
  , KK0071.AGNT_CD
  , KK0021.MSKMSHO_NO
  , KK0021.MSKMSHO_SBT_CD
  , KK0021.MANSION_ID
  , CK0011.SYSID
  , NVL(CK0011.CUST_KANA, CK0011.CONT_CUST_KANA) CUST_KANA
  , NVL(CK0011.KEISHA_TELNO, CK0201.TELNO) KEISHA_TELNO
FROM
    KK_T_MSKM_DTL KK0021
INNER JOIN
    KK_T_MSKM_AGNT KK0071
ON
    KK0071.MSKM_NO = KK0021.MSKM_NO
AND
    KK0071.MSKM_AGNT_NO IN ('001', '003')
AND
    KK0071.MK_FLG = '0'
INNER JOIN
    CK_T_CUST CK0011
ON
    CK0011.SYSID = KK0021.SYSID
LEFT OUTER JOIN
    CK_T_RRKS CK0201
ON
    CK0201.SYSID = KK0021.SYSID
AND
    CK0201.RRK_WAY_CD = '003'
WHERE
    KK0021.MSKMSHO_NO = ?
AND
    KK0071.AGNT_CD IN (?)
AND
    KK0021.GENE_ADD_DTM = (SELECT
                               MIN(KK0021_MIN.GENE_ADD_DTM)
                           FROM
                               KK_T_MSKM_DTL KK0021_MIN
                           WHERE
                               KK0021_MIN.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
                           AND
                               KK0021_MIN.MK_FLG = '0')
AND
    CK0011.GENE_ADD_DTM = (SELECT
                               MIN(CK0011_MIN.GENE_ADD_DTM)
                           FROM
                               CK_T_CUST CK0011_MIN
                           WHERE
                               CK0011_MIN.SYSID = CK0011.SYSID
                           AND
                               CK0011_MIN.MK_FLG = '0') 
AND
    (CK0201.GENE_ADD_DTM IS NULL OR 
     CK0201.RSV_APLY_YMD || CK0201.GENE_ADD_DTM = (SELECT
                                                       MIN(CK0201_MIN.RSV_APLY_YMD || CK0201_MIN.GENE_ADD_DTM)
                                                   FROM
                                                       CK_T_RRKS CK0201_MIN
                                                   WHERE
                                                       CK0201_MIN.RRKS_NO = CK0201.RRKS_NO
                                                   AND
                                                       CK0201_MIN.RSV_APLY_YMD <= ?
                                                   AND
                                                       CK0201_MIN.RSV_APLY_CD = '2'
                                                   AND
                                                       CK0201_MIN.MK_FLG = '0'))

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_041)
#--KKPRC62801
#--指定された住所変更番号に紐付く申込明細情報を取得。
#-----------------------------------------------------------

--KEY:KK_SELECT_041

SELECT
    KK0021.MSKM_NO,
    KK0021.MSKM_DTL_NO
FROM
    KK_T_MSKM_DTL KK0021
INNER JOIN
    KK_T_ADCHG KK2091
ON
    KK2091.MSKM_NO = KK0021.MSKM_NO
INNER JOIN
    KK_T_SVC_KEI KK0081
ON
    KK0081.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
WHERE
    KK2091.ADCHG_NO = ?
AND KK0081.SVC_KEI_NO = ?
AND (KK0021.MSKM_DTL_NO, KK0021.GENE_ADD_DTM) = (
     SELECT KK0021_GENE.MSKM_DTL_NO, MAX(KK0021_GENE.GENE_ADD_DTM) AS MSKM_DTL_MAX
     FROM  KK_T_MSKM_DTL KK0021_GENE
     WHERE  KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
     AND    KK0021_GENE.MK_FLG = '0'
     GROUP BY KK0021_GENE.MSKM_DTL_NO)
AND KK2091.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_042)
#--KKPRC65901
#--書面申込以外かつ本申込かどうかを判定する
#-----------------------------------------------------------

--KEY:KK_SELECT_042

SELECT
    KK0021.MSKM_DTL_NO
FROM
    KK_T_MSKM_DTL KK0021
INNER JOIN KK_T_MSKM KK0011
    ON KK0011.MSKM_NO = KK0021.MSKM_NO
INNER JOIN KK_T_MSKM_DMEN_DTL KK2511
    ON KK2511.MSKMSHO_NO = KK0021.MSKMSHO_NO
WHERE
    KK0021.MSKM_DTL_NO = ?
AND KK0021.MSKM_FORM_CD <> '05'
AND (KK0021.MSKM_DTL_NO, KK0021.GENE_ADD_DTM) =
    (SELECT
         KK0021_GENE.MSKM_DTL_NO, MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX
     FROM
         KK_T_MSKM_DTL KK0021_GENE
     WHERE
         KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
     AND KK0021_GENE.MK_FLG = '0'
     GROUP BY KK0021_GENE.MSKM_DTL_NO)
AND KK0011.MSKM_STAT = '130'
AND (KK0011.MSKM_NO, KK0011.GENE_ADD_DTM) =
    (SELECT
         KK0011_GENE.MSKM_NO, MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_MAX
     FROM
         KK_T_MSKM KK0011_GENE
     WHERE
         KK0011_GENE.MSKM_NO = KK0011.MSKM_NO
     AND KK0011_GENE.MK_FLG = '0'
     GROUP BY KK0011_GENE.MSKM_NO)
AND KK2511.MSKM_DMEN_SBT_CD = '2'
AND KK2511.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_044)
#--KKPRC68201
#--入力したサービス契約番号の最古世代の申込明細番号に紐づいたカレントの申込・申込明細スキーマを取得する。
#-----------------------------------------------------------

--KEY:KK_SELECT_044

SELECT
    KK0021.MSKM_DTL_NO,
    KK0021.GENE_ADD_DTM,
    KK0021.MSKM_DTL_STAT,
    KK0021.MSKM_NO,
    KK0021.MSKMSHO_NO,
    KK0021.MSKMSHO_DTL_NO,
    KK0021.MSKM_YMD,
    KK0021.SYSID,
    KK0021.WEB_UK_NO,
    KK0021.MSKM_FORM_CD,
    KK0021.MSKM_DTL_SBT_CD,
    KK0021.RRKS_KTAI_TELNO,
    KK0021.RRKS_OFFC_NM,
    KK0021.DT_RRKS_TELNO,
    KK0021.NEW_MSKM_SBT_CD,
    KK0021.MSKM_OPTNTY_CD,
    KK0021.TAKCHO_KIBO_APO_KIGEN_YMD,
    KK0021.KOJI_APO_RRKS_SHITEI_CD,
    KK0021.KOJI_APO_RRKS_TELNO,
    KK0021.RRK_JIKO_ADD_DTM,
    KK0021.MSKM_DTL_SHOSA_DTM,
    KK0021.MSKM_DTL_SHOSA_CL_YMD,
    KK0021.KZKWRK_REQYMD,
    KK0021.MSKM_DTL_CANCEL_YMD,
    KK0021.MSKM_DTL_CANCEL_RSN_CD,
    KK0021.MSKM_DTL_CANCEL_RSN_MEMO,
    KK0021.MSKM_DTL_CANCEL_CL_YMD,
    KK0021.MSKM_RRKS_MLAD,
    KK0021.KOJI_PRG_TCHI_MLAD,
    KK0021.RRK_JIKO,
    KK0021.RCNT_SHOKAI_YMD,
    KK0021.WEB_SHOKAI_CNT,
    KK0021.PC_SHOKAI_CNT,
    KK0021.KTAI_SHOKAI_CNT,
    KK0021.KJSC_TCH_KTAI_MLAD,
    KK0021.KJSC_TCH_PC_MLAD_CHG_YMD,
    KK0021.KJSC_TCH_KTAI_MLAD_CHG_YMD,
    KK0021.PRG_ML_LAST_SEND_DTM,
    KK0021.ML_SEND_JGI_FLG,
    KK0021.ML_SEND_JGI_RSN_MEMO,
    KK0021.BMP_MSKM_RRKS_TLN,
    KK0021.OP_SVC_HKTGI_UM,
    KK0021.DSL_ATICLE_SOHU_KIBO_UM,
    KK0021.RRK_WAY_HOKI,
    KK0021.NEWCONST_BUKKEN_CD,
    KK0021.HUKKAT_ANKEN_FLG,
    KK0021.SAME_EQUIP_RE_MSKM_CD,
    KK0021.HNIN_CFM_ATICLE_SBT_CD,
    KK0021.HNIN_CFM_ATICLE_NM,
    KK0021.MSKMSHO_SBT_CD,
    KK0021.KEI_HUKA_CD,
    KK0021.MAP_TEMP_UM,
    KK0021.KCN_STB_MSKM_CNT,
    KK0021.KCN_STB_KIND_CD,
    KK0021.MANSSBSYS_RNKI_YO_KIJIRAN,
    KK0021.MANSION_ID,
    KK0021.CATID,
    KK0021.MKM_UK_SBT_CD,
    KK0021.MSKMSHO_TRAT_IF_TNTSHA_NM,
    KK0021.MSKMSHO_TRAT_IF_TELNO,
    KK0021.WRIB_AUTO_APLY_TG_GAI_FLG,
    KK0021.ADD_DTM,
    KK0021.ADD_OPEACNT,
    KK0021.UPD_DTM,
    KK0021.UPD_OPEACNT,
    KK0021.DEL_DTM,
    KK0021.DEL_OPEACNT,
    KK0021.MK_FLG,
    KK0021.ADD_UNYO_YMD,
    KK0021.ADD_TRN_ID,
    KK0021.UPD_UNYO_YMD,
    KK0021.UPD_TRN_ID,
    KK0021.DEL_UNYO_YMD,
    KK0021.DEL_TRN_ID,
    KK0021.TAKINORT_KKN_KIBO_UM,
    KK0021.DOJI_MSKM_EOH_MSKMSHO_NO,
    KK0021.FAMILY_PACK_DSL_KIBO_UM,
    KK0021.VIRUSBUSTER_DSL_KIBO_UM,
    KK0021.YUGAI_WEB_FLT_DSL_KIBO_UM,
    KK0021.TSYRN_TAKCHO_YH,
    KK0021.TSYRN_KJ_RRKS_NM,
    KK0021.TSYRN_KJ_RRKS_TLN,
    KK0021.TSYRN_TAKCHO_JI_RRKS_NM,
    KK0021.TSYRN_TAKCHO_JI_RRKS_TLN,
    KK0021.TSYRN_RRK_KIBO_DATE,
    KK0021.TSYRN_MAP_UM,
    KK0021.TSYRN_KZN_KEI_INF,
    KK0021.TSYRN_HBI_KAISHOU_YMD,
    KK0021.EQUIP_RYUYO_UM,
    KK0021.RYUYO_MOTO_KJAK_NO,
    KK0021.SBRY_KJ_FIN_KIBOYMD,
    KK0021.NOW_USE_SK_KEI_KISN_DIV,
    KK0021.KIKI_SETPLC_CHGUM,
    KK0021.DOJITU_JSSI_KBUM,
    KK0021.EARLY_OPEN_EARLY_RRK_KBUM,
    KK0021.DNSKH_DOI_UM,
    KK0021.TAKCHO_APO_KADEN_KIBO_UM,
    KK0021.HKTGI_OPSVKEI_LIST
FROM KK_T_MSKM_DTL KK0021
INNER JOIN
    (SELECT
        KK0081_WK.MSKM_DTL_NO
    FROM KK_T_SVC_KEI KK0081_WK
    WHERE KK0081_WK.SVC_KEI_NO = ?
    AND KK0081_WK.RSV_APLY_YMD || KK0081_WK.GENE_ADD_DTM = (
        SELECT
              MIN(
                KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM
            ) AS KK0081_MIN
        FROM
            KK_T_SVC_KEI KK0081_GENE
        WHERE
            KK0081_GENE.SVC_KEI_NO = KK0081_WK.SVC_KEI_NO
            AND KK0081_GENE.RSV_APLY_YMD <= ?
            AND KK0081_GENE.RSV_APLY_CD = '2'
            AND KK0081_GENE.MK_FLG = '0'
        )
     AND KK0081_WK.MK_FLG = '0'
    ) KK0081
ON KK0081.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
INNER JOIN KK_T_MSKM KK0011
ON KK0021.MSKM_NO = KK0011.MSKM_NO
WHERE  KK0021.GENE_ADD_DTM =
       (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS GENE_MAX
        FROM KK_T_MSKM_DTL KK0021_GENE
        WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
        AND KK0021_GENE.MK_FLG = '0')
AND    KK0011.GENE_ADD_DTM =
       (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MAX
        FROM   KK_T_MSKM KK0011_GENE
        WHERE  KK0011_GENE.MSKM_NO = KK0011.MSKM_NO
        AND    KK0011_GENE.MK_FLG = '0')
AND KK0011.MK_FLG = '0'
AND KK0021.MK_FLG = '0'

#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-CN_SELECT_001)
#--CNPRC03301_コンテンツ利用規約自動同意登録
#--入力したサービス契約番号の最古世代の申込明細番号に紐づいたカレントの申込・申込明細スキーマを取得する。
#-----------------------------------------------------------

--KEY:CN_SELECT_001

SELECT
    KK0021.MSKM_DTL_NO
    ,KK0021.MSKM_NO
    ,KK0011.MSKM_YMD
    ,KK0021.MKM_UK_SBT_CD
FROM KK_T_MSKM_DTL KK0021
INNER JOIN
    (SELECT
        KK0081_WK.MSKM_DTL_NO
    FROM KK_T_SVC_KEI KK0081_WK
    WHERE KK0081_WK.SVC_KEI_NO = ?
    AND KK0081_WK.RSV_APLY_YMD || KK0081_WK.GENE_ADD_DTM = (
        SELECT
              MIN(
                KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM
            ) AS KK0081_MIN
        FROM
            KK_T_SVC_KEI KK0081_GENE
        WHERE
            KK0081_GENE.SVC_KEI_NO = KK0081_WK.SVC_KEI_NO
            AND KK0081_GENE.RSV_APLY_YMD <= ?
            AND KK0081_GENE.RSV_APLY_CD = '2'
            AND KK0081_GENE.MK_FLG = '0'
        )
     AND KK0081_WK.MK_FLG = '0'
    ) KK0081
ON KK0081.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
INNER JOIN KK_T_MSKM KK0011
ON KK0021.MSKM_NO = KK0011.MSKM_NO
WHERE  KK0021.GENE_ADD_DTM =
       (SELECT MAX(KK0021_GENE.GENE_ADD_DTM) AS GENE_MAX
        FROM KK_T_MSKM_DTL KK0021_GENE
        WHERE KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
        AND KK0021_GENE.MK_FLG = '0')
AND    KK0011.GENE_ADD_DTM =
       (SELECT MAX(KK0011_GENE.GENE_ADD_DTM) AS KK0011_GENE_MAX
        FROM   KK_T_MSKM KK0011_GENE
        WHERE  KK0011_GENE.MSKM_NO = KK0011.MSKM_NO
        AND    KK0011_GENE.MK_FLG = '0')
AND KK0011.MSKM_YMD >= ?
AND KK0021.MKM_UK_SBT_CD IN ('0','2','4','5','K','L','M','N','O','P','Q','R','S')
AND KK0011.MK_FLG = '0'
AND KK0021.MK_FLG = '0'


#-

#-----------------------------------------------------------
#--申込明細(KK_T_MSKM_DTL-KK_SELECT_045)
#--KKPRC68101
#--WAO注文連携抑止フラグがOFFである申込明細情報を抽出する
#-----------------------------------------------------------

--KEY:KK_SELECT_045

SELECT
    KK0021.MSKM_DTL_NO
FROM
    KK_T_MSKM_DTL KK0021
WHERE
    KK0021.MSKM_DTL_NO = ?
AND (KK0021.WAO_CHUMN_RNKI_YOKSI_FLG IS NULL OR KK0021.WAO_CHUMN_RNKI_YOKSI_FLG <> '1')
AND (KK0021.MSKM_DTL_NO, KK0021.GENE_ADD_DTM) =
    (SELECT
         KK0021_GENE.MSKM_DTL_NO, MAX(KK0021_GENE.GENE_ADD_DTM) AS KK0021_MAX
     FROM
         KK_T_MSKM_DTL KK0021_GENE
     WHERE
         KK0021_GENE.MSKM_DTL_NO = KK0021.MSKM_DTL_NO
     AND KK0021_GENE.MK_FLG = '0'
     GROUP BY KK0021_GENE.MSKM_DTL_NO)

#-