SELECT
   '1'                     AS ORDER_SBT_CD
  ,'06'                    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
  ,KK0161.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
  ,'147'                   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
WHERE
       KK1091.PRG_DTM >= :OPE_DATE || '000000000'
   AND KK1091.PRG_DTM <= :OPE_DATE || '235959999'
   AND KK1091.PRG_STAT = '2001'
   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'
           )
        OR KK0081.PRC_GRP_CD = '06'
        )
   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_STAT = '210'
UNION ALL
SELECT
   '1'                     AS ORDER_SBT_CD
  ,'06'                    AS SVC_ORDER_CD
  ,'10'                    AS YOKYU_SBT_CD
  ,KK1091_A.PRG_STAT       AS PRG_STAT
  ,KK1091_A.PRG_DTM        AS PRG_DTM
  ,KK1091.SVC_KEI_NO       AS SVC_KEI_NO_SOD
  ,KK1091.SVC_KEI_NO       AS SVC_KEI_NO
  ,KK0161.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
  ,'147'                   AS YOBI
FROM
   KK_T_PRG KK1091_A
   INNER JOIN KK_T_SVC_KEI KK0081
     ON KK1091_A.SVC_KEI_NO = KK0081.SVC_KEI_NO
   INNER JOIN KK_T_SVC_KEI_UCWK KK0161_A
     ON KK0081.SVC_KEI_NO = KK0161_A.SVC_KEI_NO
   INNER JOIN KK_T_PRG KK1091
     ON KK0161_A.SVC_KEI_NO = KK1091.SVC_KEI_NO
   INNER JOIN KK_T_SVC_KEI_UCWK KK0161
     ON KK0081.SVC_KEI_NO = KK0161.SVC_KEI_NO
     AND KK1091.MSKM_DTL_NO = KK0161.MSKM_DTL_NO
WHERE
       KK1091_A.PRG_DTM >= :OPE_DATE || '000000000'
   AND KK1091_A.PRG_DTM <= :OPE_DATE || '235959999'
   AND KK1091_A.PRG_STAT = '2001'
   AND ( KK1091_A.IDO_DIV <> '00019' AND KK1091_A.IDO_DIV <> '00020')
   AND KK1091_A.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_A.ADD_DTM ,'YYYYMMDDHH24MISSFF3') - NUMTODSINTERVAL(TO_NUMBER(:SEC_FROM),'SECOND'), 'YYYYMMDDHH24MISSFF3')
             AND KK0081_GENE.ADD_DTM <= TO_CHAR(TO_TIMESTAMP(KK1091_A.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'
           )
        OR KK0081.PRC_GRP_CD = '06'
        )
   AND (KK0161_A.SVC_KEI_UCWK_NO, KK0161_A.RSV_APLY_YMD || KK0161_A.GENE_ADD_DTM) = 
         (SELECT KK0161_A_GENE.SVC_KEI_UCWK_NO, MAX(KK0161_A_GENE.RSV_APLY_YMD || KK0161_A_GENE.GENE_ADD_DTM) AS KK0161_A_MAX 
          FROM   KK_T_SVC_KEI_UCWK KK0161_A_GENE
          WHERE  KK0161_A_GENE.SVC_KEI_UCWK_NO = KK0161_A.SVC_KEI_UCWK_NO
            AND  KK0161_A_GENE.ADD_DTM >= TO_CHAR(TO_TIMESTAMP(KK1091_A.ADD_DTM ,'YYYYMMDDHH24MISSFF3') - NUMTODSINTERVAL(TO_NUMBER(:SEC_FROM),'SECOND'), 'YYYYMMDDHH24MISSFF3')
            AND  KK0161_A_GENE.ADD_DTM <= TO_CHAR(TO_TIMESTAMP(KK1091_A.ADD_DTM ,'YYYYMMDDHH24MISSFF3') + NUMTODSINTERVAL(TO_NUMBER(:SEC_TO),'SECOND'), 'YYYYMMDDHH24MISSFF3')
            AND  KK0161_A_GENE.RSV_APLY_YMD <= :OPE_DATE
            AND  KK0161_A_GENE.RSV_APLY_CD = '2'
            AND  KK0161_A_GENE.MK_FLG = '0'
          GROUP BY KK0161_A_GENE.SVC_KEI_UCWK_NO)
   AND KK0161_A.SVC_KEI_UCWK_STAT = '210'
   AND KK1091.PRG_STAT = '1421'
   AND (KK0161.SVC_KEI_UCWK_STAT = '020' OR KK0161.SVC_KEI_UCWK_STAT = '030')