SELECT
   '3'                     AS ORDER_SBT_CD
  ,'12'                    AS SVC_ORDER_CD
  ,'03'                    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
  ,KK0341.KKTK_SVC_KEI_NO  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
  ,NULL                    AS KK0161_GENE_ADD_DTM
  ,KK0341.GENE_ADD_DTM     AS KK0341_GENE_ADD_DTM
  ,NULL                    AS KK0351_GENE_ADD_DTM
  ,NULL                    AS KK0401_GENE_ADD_DTM
  ,'190'                   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_KKTK_SVC_KEI KK0341
      ON KK0081.SVC_KEI_NO = KK0341.SVC_KEI_NO
WHERE
       KK1091.PRG_DTM >= :OPE_DATE || '000000000'
   AND KK1091.PRG_DTM <= :OPE_DATE || '235959999'
   AND KK1091.PRG_STAT = '2204'
   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 = '01'
   AND KK0081.PRC_GRP_CD = '08'
   AND (KK0341.KKTK_SVC_KEI_NO, KK0341.RSV_APLY_YMD || KK0341.GENE_ADD_DTM) =
         (SELECT KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.RSV_APLY_YMD || KK0341_GENE.GENE_ADD_DTM) AS KK0341_MAX 
          FROM   KK_T_KKTK_SVC_KEI KK0341_GENE
          WHERE 
                 KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
             AND KK0341_GENE.ADD_DTM <= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') + NUMTODSINTERVAL(TO_NUMBER(:SEC_TO),'SECOND'), 'YYYYMMDDHH24MISSFF3')
             AND KK0341_GENE.RSV_APLY_YMD <= :OPE_DATE
             AND KK0341_GENE.RSV_APLY_CD = '2'
             AND KK0341_GENE.MK_FLG = '0'
          GROUP BY KK0341_GENE.KKTK_SVC_KEI_NO)
   AND KK0341.KKTK_SVC_CD = 'C016'
   AND KK0341.KKTK_SVC_KEI_STAT = '910'
   AND KK0341.KIKI_SEIZO_NO IS NOT NULL