/*------------------------------------------------------------
-- 代理店コードから、
-- 基準となる年月日時分秒以降のeo光の申込データを取得する
-- 申込＿代理店スキーマのアンロード
------------------------------------------------------------*/
SELECT
    KK0071_1.MSKM_NO
  , KK0071_2.AGNT_CD
  , KK0071_1.MSKM_AGNT_NO
  , KK0021.MSKM_DTL_NO
  , KK0021.MSKM_YMD
  , KK0021.MSKMSHO_NO
  , KK0021.MSKMSHO_SBT_CD
  , KK0021.MANSION_ID
  , KK0081.SVC_KEI_NO
  , KK0081.PRC_GRP_CD
  , CK0011.SYSID
  , CK0011.CUST_KANA
  , CK0011.KEISHA_TELNO
FROM
    (SELECT
         KK0071_A.MSKM_NO
       , MIN(KK0071_A.MSKM_AGNT_NO) MSKM_AGNT_NO
     FROM
         KK_T_MSKM_AGNT KK0071_A
     WHERE
         KK0071_A.AGNT_CD IN (?_TOKUT_AGNT_CD)
     AND
         KK0071_A.UPD_DTM >= TO_CHAR(TO_TIMESTAMP('?_ZNKI_TRAN_RUN_DTM', 'YYYYMMDDHH24MISSFF3') - NUMTODSINTERVAL(TO_NUMBER(1), 'HOUR'), 'YYYYMMDDHH24MISSFF3')
     AND
         KK0071_A.MSKM_AGNT_NO IN ('001', '003')
     AND
         KK0071_A.MK_FLG = '0'
     GROUP BY
         MSKM_NO) KK0071_1
INNER JOIN
    (SELECT
         KK0071_B.AGNT_CD
       , KK0071_B.MSKM_NO
       , KK0071_B.MSKM_AGNT_NO
     FROM
         KK_T_MSKM_AGNT KK0071_B) KK0071_2
ON
    KK0071_1.MSKM_NO = KK0071_2.MSKM_NO
AND
    KK0071_1.MSKM_AGNT_NO = KK0071_2.MSKM_AGNT_NO
INNER JOIN
    (SELECT
         KK0021_A.MSKM_DTL_NO
       , KK0021_A.MSKM_YMD
       , KK0021_A.MSKMSHO_NO
       , KK0021_A.MSKMSHO_SBT_CD
       , KK0021_A.MANSION_ID
       , KK0021_A.MSKM_NO
       , KK0021_A.SYSID
     FROM
         KK_T_MSKM_DTL KK0021_A
     WHERE
         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')
     AND
         KK0021_A.DOJI_MSKM_EOH_MSKMSHO_NO IS NULL
    ) KK0021
ON
    KK0071_1.MSKM_NO = KK0021.MSKM_NO
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 <= '?_RSLT_OPEDATE'
                                                           AND
                                                               KK0081_GENE.RSV_APLY_CD = '2'
                                                           AND
                                                               KK0081_GENE.MK_FLG = '0')) KK0081
ON
    KK0021.MSKM_DTL_NO = KK0081.MSKM_DTL_NO
INNER JOIN
    (SELECT
         CK0011_A.SYSID
       , NVL(CK0011_A.CUST_KANA, CK0011_A.CONT_CUST_KANA) CUST_KANA
       , NVL(CK0011_A.KEISHA_TELNO, CK0201.TELNO) KEISHA_TELNO
     FROM
         CK_T_CUST CK0011_A
     LEFT OUTER JOIN
         CK_T_RRKS CK0201
     ON
         CK0201.SYSID = CK0011_A.SYSID
     AND
         CK0201.RRK_WAY_CD = '003'
     AND
         CK0201.RRKS_BUNRUI_CD = '1'
     WHERE
         CK0011_A.GENE_ADD_DTM = (SELECT
                                      MIN(CK0011_MIN.GENE_ADD_DTM)
                                  FROM
                                      CK_T_CUST CK0011_MIN
                                  WHERE
                                      CK0011_MIN.SYSID = CK0011_A.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 <= '?_RSLT_OPEDATE'
                                                        AND
                                                          CK0201_MIN.RSV_APLY_CD = '2'
                                                        AND
                                                          CK0201_MIN.MK_FLG = '0'))
    ) CK0011
ON
    CK0011.SYSID = KK0021.SYSID