SELECT
   '2'                     AS ORDER_SBT_CD
  ,'20'                    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
  ,KK1091.SVC_KEI_UCWK_NO  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
  ,'221'                   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 = 'B552'
   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_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 KK0161.SVC_KEI_UCWK_NO = KK1091.SVC_KEI_UCWK_NO
   AND (KK0161.SVC_KEI_UCWK_STAT = '020' OR KK0161.SVC_KEI_UCWK_STAT = '030'OR KK0161.SVC_KEI_UCWK_STAT = '100')
   AND KK0191.BMP_UM = '1'
UNION ALL
SELECT
   '2'                     AS ORDER_SBT_CD
  ,'21'                    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
  ,KK1091.SVC_KEI_UCWK_NO  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
  ,'221'                   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 = 'B552'
   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_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 KK0161.SVC_KEI_UCWK_NO = KK1091.SVC_KEI_UCWK_NO
   AND (KK0161.SVC_KEI_UCWK_STAT = '020' OR KK0161.SVC_KEI_UCWK_STAT = '030'OR KK0161.SVC_KEI_UCWK_STAT = '100')
   AND KK0191.BMP_UM = '1'
UNION ALL
SELECT
   '2'                     AS ORDER_SBT_CD
  ,'21'                    AS SVC_ORDER_CD
  ,'08'                    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
  ,KK1091.SVC_KEI_UCWK_NO  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
  ,'221'                   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 = 'B552'
   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_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 KK0161.SVC_KEI_UCWK_NO = KK1091.SVC_KEI_UCWK_NO
   AND (KK0161.SVC_KEI_UCWK_STAT = '020' OR KK0161.SVC_KEI_UCWK_STAT = '030'OR KK0161.SVC_KEI_UCWK_STAT = '100')
   AND KK0191.BMP_UM = '1'
UNION ALL
SELECT
   '2'                     AS ORDER_SBT_CD
  ,'21'                    AS SVC_ORDER_CD
  ,'02'                    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
  ,KK1091.SVC_KEI_UCWK_NO  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
  ,'221'                   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 = 'B552'
   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_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 KK0161.SVC_KEI_UCWK_NO = KK1091.SVC_KEI_UCWK_NO
   AND (KK0161.SVC_KEI_UCWK_STAT = '020' OR KK0161.SVC_KEI_UCWK_STAT = '030'OR KK0161.SVC_KEI_UCWK_STAT = '100')
   AND KK0191.BMP_UM = '1'
UNION ALL
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
  ,KK1091.SVC_KEI_UCWK_NO  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
  ,'221'                   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 = 'B552'
   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_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 KK0161.SVC_KEI_UCWK_NO = KK1091.SVC_KEI_UCWK_NO
   AND (KK0161.SVC_KEI_UCWK_STAT = '020' OR KK0161.SVC_KEI_UCWK_STAT = '030'OR KK0161.SVC_KEI_UCWK_STAT = '100')
   AND KK0191.BMP_UM = '1'
UNION ALL
SELECT
   '4'                     AS ORDER_SBT_CD
  ,'24'                    AS SVC_ORDER_CD
  ,'08'                    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
  ,KK1091.SVC_KEI_UCWK_NO  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
  ,'221'                   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 = 'B552'
   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_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 KK0161.SVC_KEI_UCWK_NO = KK1091.SVC_KEI_UCWK_NO
   AND (KK0161.SVC_KEI_UCWK_STAT = '020' OR KK0161.SVC_KEI_UCWK_STAT = '030'OR KK0161.SVC_KEI_UCWK_STAT = '100')
   AND KK0191.BMP_UM = '1'
UNION ALL
SELECT
   '4'                     AS ORDER_SBT_CD
  ,'24'                    AS SVC_ORDER_CD
  ,'02'                    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
  ,KK1091.SVC_KEI_UCWK_NO  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
  ,'221'                   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 = 'B552'
   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_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 KK0161.SVC_KEI_UCWK_NO = KK1091.SVC_KEI_UCWK_NO
   AND (KK0161.SVC_KEI_UCWK_STAT = '020' OR KK0161.SVC_KEI_UCWK_STAT = '030'OR KK0161.SVC_KEI_UCWK_STAT = '100')
   AND KK0191.BMP_UM = '1'