SELECT
   '2'                     AS ORDER_SBT_CD
  ,'21'                    AS SVC_ORDER_CD
  ,'04'                    AS YOKYU_SBT_CD
  ,KK1091.PRG_STAT         AS PRG_STAT
  ,KK1091.PRG_DTM          AS PRG_DTM
  ,KK1091.SVC_KEI_NO       AS SVC_KEI_NO_SOD
  ,KK1091.SVC_KEI_NO       AS SVC_KEI_NO
  ,NULL                    AS SVC_KEI_UCWK_NO
  ,NULL                    AS KKTK_SVC_KEI_NO
  ,NULL                    AS OP_SVC_KEI_NO
  ,NULL                    AS SBOP_SVC_KEI_NO
  ,KK1091.IDO_DIV          AS IDO_DIV
  ,KK1091.ADD_DTM          AS ADD_DTM
  ,KK1091.ADD_TRN_ID       AS ADD_TRN_ID
  ,KK0081.SVC_CD           AS SVC_CD
  ,KK0081.PRC_GRP_CD       AS PRC_GRP_CD
  ,KK0081.PCRS_CD          AS PCRS_CD
  ,KK0081.PPLAN_CD         AS PPLAN_CD
  ,KK1091.PRG_NO           AS PRG_NO
  ,KK0081.GENE_ADD_DTM     AS KK0081_GENE_ADD_DTM
  ,KK0161.GENE_ADD_DTM     AS KK0161_GENE_ADD_DTM
  ,NULL                    AS KK0341_GENE_ADD_DTM
  ,NULL                    AS KK0351_GENE_ADD_DTM
  ,NULL                    AS KK0401_GENE_ADD_DTM
  ,'204'                   AS YOBI
FROM
   KK_T_PRG KK1091
   INNER JOIN KK_T_SVC_KEI KK0081
      ON KK1091.SVC_KEI_NO = KK0081.SVC_KEI_NO
   INNER JOIN KK_T_SVC_KEI_UCWK KK0161
      ON KK0081.SVC_KEI_NO = KK0161.SVC_KEI_NO
   INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191
      ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO
         AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM
WHERE
       KK1091.PRG_DTM >= :OPE_DATE || '000000000'
   AND KK1091.PRG_DTM <= :OPE_DATE || '235959999'
   AND KK1091.PRG_STAT = '2402'
   AND ( KK1091.IDO_DIV <> '00019' AND KK1091.IDO_DIV <> '00020')
   AND KK1091.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.ADD_DTM >= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') - NUMTODSINTERVAL(TO_NUMBER(:SEC_FROM),'SECOND'), 'YYYYMMDDHH24MISSFF3')
            AND  KK0081_GENE.ADD_DTM <= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') + NUMTODSINTERVAL(TO_NUMBER(:SEC_TO),'SECOND'), 'YYYYMMDDHH24MISSFF3')
            AND  KK0081_GENE.RSV_APLY_YMD <= :OPE_DATE
            AND  KK0081_GENE.RSV_APLY_CD = '2'
            AND  KK0081_GENE.MK_FLG = '0'
          GROUP BY KK0081_GENE.SVC_KEI_NO
         )
   AND KK0081.SVC_CD = '02'
   AND (KK0161.SVC_KEI_UCWK_NO, KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) =
         (SELECT KK0161_GENE.SVC_KEI_UCWK_NO, 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.ADD_DTM >= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') - NUMTODSINTERVAL(TO_NUMBER(:SEC_FROM),'SECOND'), 'YYYYMMDDHH24MISSFF3')
             AND KK0161_GENE.ADD_DTM <= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') + NUMTODSINTERVAL(TO_NUMBER(:SEC_TO),'SECOND'), 'YYYYMMDDHH24MISSFF3')
             AND KK0161_GENE.RSV_APLY_YMD <= :OPE_DATE
             AND KK0161_GENE.RSV_APLY_CD = '2'
             AND KK0161_GENE.MK_FLG = '0'
          GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO)
   AND KK0191.DSL_PAUSE_TOKI_ADD_CD = '3'
   AND KK0191.DSL_PAUSE_TOKI_STA_YMD IS NOT NULL
   AND (KK0191.DSL_PAUSE_TOKI_END_YMD IS NOT NULL OR KK0191.DSL_PAUSE_TOKI_END_YMD = :OPE_DATE)


   AND EXISTS(
         SELECT 1
         FROM  KK_T_OP_SVC_KEI KK0351_SUB
         WHERE 
            KK0351_SUB.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
            AND (KK0351_SUB.OP_SVC_KEI_NO, KK0351_SUB.RSV_APLY_YMD || KK0351_SUB.GENE_ADD_DTM) =
                   (SELECT KK0351_SUB_GENE.OP_SVC_KEI_NO, MAX(KK0351_SUB_GENE.RSV_APLY_YMD || KK0351_SUB_GENE.GENE_ADD_DTM) AS KK0351_SUB_MAX
                    FROM   KK_T_OP_SVC_KEI KK0351_SUB_GENE
                    WHERE 
                          KK0351_SUB_GENE.OP_SVC_KEI_NO = KK0351_SUB.OP_SVC_KEI_NO
                      AND KK0351_SUB_GENE.ADD_DTM >= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') - NUMTODSINTERVAL(TO_NUMBER(:SEC_FROM),'SECOND'), 'YYYYMMDDHH24MISSFF3')
                      AND KK0351_SUB_GENE.ADD_DTM <= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') + NUMTODSINTERVAL(TO_NUMBER(:SEC_TO),'SECOND'), 'YYYYMMDDHH24MISSFF3')
                      AND KK0351_SUB_GENE.RSV_APLY_YMD <= :OPE_DATE
                      AND KK0351_SUB_GENE.RSV_APLY_CD = '2'
                      AND KK0351_SUB.MK_FLG = '0'
                    GROUP BY KK0351_SUB_GENE.OP_SVC_KEI_NO
                   )
            AND KK0351_SUB.OP_SVC_KEI_STAT IN('020','030','100')
            AND (KK0351_SUB.OP_SVC_CD = 'B022'
                 OR KK0351_SUB.OP_SVC_CD = 'B023'
                 OR KK0351_SUB.OP_SVC_CD = 'B024'
                 OR KK0351_SUB.OP_SVC_CD = 'B025'
                 OR KK0351_SUB.OP_SVC_CD = 'B026'
                 OR KK0351_SUB.OP_SVC_CD = 'B027'
                 OR KK0351_SUB.OP_SVC_CD = 'B071'
                )
       )