SELECT
   '1'                     AS ORDER_SBT_CD
  ,'08'                    AS SVC_ORDER_CD
  ,'10'                    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
  ,KK0351.OP_SVC_KEI_NO    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
  ,NULL                    AS KK0341_GENE_ADD_DTM
  ,KK0351.GENE_ADD_DTM     AS KK0351_GENE_ADD_DTM
  ,NULL                    AS KK0401_GENE_ADD_DTM
  ,'163'                   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_OP_SVC_KEI KK0351
      ON KK0081.SVC_KEI_NO = KK0351.SVC_KEI_NO
   INNER JOIN KK_T_OPSVKEI_ISP KK0361
      ON KK0351.OP_SVC_KEI_NO = KK0361.OP_SVC_KEI_NO
         AND KK0351.GENE_ADD_DTM = KK0361.GENE_ADD_DTM
WHERE
       KK1091.PRG_DTM >= :OPE_DATE || '000000000'
   AND KK1091.PRG_DTM <= :OPE_DATE || '235959999'
   AND KK1091.PRG_STAT = '2201'
   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 = '02'
        OR KK0081.PRC_GRP_CD = '03'
        OR (
            KK0081.PRC_GRP_CD = '04' 
            AND KK0081.PCRS_CD <> 'A47' 
            AND KK0081.PCRS_CD <> 'A48' 
            AND KK0081.PCRS_CD <> 'A49' 
            AND KK0081.PCRS_CD <> 'A67'
            AND KK0081.PCRS_CD <> 'A68'
            AND KK0081.PCRS_CD <> 'A78'
           )
        )
   AND (KK0351.OP_SVC_KEI_NO, KK0351.RSV_APLY_YMD || KK0351.GENE_ADD_DTM) =
         (SELECT KK0351_GENE.OP_SVC_KEI_NO, MAX(KK0351_GENE.RSV_APLY_YMD || KK0351_GENE.GENE_ADD_DTM) AS KK0351_MAX 
          FROM   KK_T_OP_SVC_KEI KK0351_GENE
          WHERE 
                 KK0351_GENE.OP_SVC_KEI_NO = KK0351.OP_SVC_KEI_NO
             AND KK0351_GENE.ADD_DTM >= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') - NUMTODSINTERVAL(TO_NUMBER(:SEC_FROM),'SECOND'), 'YYYYMMDDHH24MISSFF3')
             AND KK0351_GENE.ADD_DTM <= TO_CHAR(TO_TIMESTAMP(KK1091.ADD_DTM ,'YYYYMMDDHH24MISSFF3') + NUMTODSINTERVAL(TO_NUMBER(:SEC_TO),'SECOND'), 'YYYYMMDDHH24MISSFF3')
             AND KK0351_GENE.RSV_APLY_YMD <= :OPE_DATE
             AND KK0351_GENE.RSV_APLY_CD = '2'
             AND KK0351_GENE.MK_FLG = '0'
          GROUP BY KK0351_GENE.OP_SVC_KEI_NO)
   AND KK0351.OP_SVC_CD = 'B020'
   AND KK0351.OP_SVC_KEI_STAT = '220'