SELECT
    KK0161_BASE.DEL_TRAN_SBT     AS DEL_TRAN_SBT,
    KK0161_BASE.DEL_TRGT_SBT     AS DEL_TRGT_SBT,
    KK0161_BASE.SVC_KEI_NO       AS SVC_KEI_NO,
    KK0161_BASE.SVKEI_GADTM      AS SVKEI_GADTM,
    KK0161_BASE.SVC_KEI_UCWK_NO  AS SVKEIUW_NO,
    KK0161_BASE.SVKEIUW_GADTM    AS SVKEIUW_GADTM,
    NULL                         AS OP_SVC_KEI_NO,
    NULL                         AS OPSVKEI_GADTM,
    NULL                         AS SYSID,
    KK0161_BASE.ISP_NINSHO_ID    AS ISP_NINSHO_ID,
    NULL                         AS MLAD,
    NULL                         AS MAILALIAS,
    NULL                         AS MLLIST_NM,
    KK0161_BASE.PPP_NINSHO_ID    AS PPP_NINSHO_ID,
    NULL                         AS URL,
    NULL                         AS FIXIPAD,
    NULL                         AS OABJ_TELNO,
    NULL                         AS TELNO_050,
    NULL                         AS EOID,
    NULL                         AS NKNAME,
    NULL                         AS AGING_NO,
    NULL                         AS WEB_ID,
    NULL                         AS POP_ID,
    KK0161_BASE.ADSL_NINSHO_ID   AS ADSL_NINSHO_ID,
    NULL                         AS SIP_USER_ID
  , KK0161_BASE.SVC_CD           AS SVC_CD
  , KK0161_BASE.PRC_GRP_CD       AS PRC_GRP_CD
FROM (
    SELECT
        KK0081.SVC_KEI_NO      AS SVC_KEI_NO,
        KK0081.GENE_ADD_DTM    AS SVKEI_GADTM,
        KK0161.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO,
        KK0161.GENE_ADD_DTM    AS SVKEIUW_GADTM,
        DECODE((
            SELECT
                COUNT(*) AS ODR_CNT
            FROM
                KK_T_ODR_SET KK1041
            WHERE
                KK1041.SVC_KEI_NO      = KK0161.SVC_KEI_NO
            AND KK1041.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
            AND KK1041.ORDER_SBT_CD    = '1'
            AND KK1041.SVC_ORDER_CD    = '06'
            AND KK1041.RCNT_YOKYU_SBT_CD IN ('02', '03', '04', '07', '10', '11', '14')
            AND KK1041.MK_FLG          = '0'
            AND KK1041.ADD_DTM IS NOT NULL
        ), 0, '2', '3')        AS DEL_TRAN_SBT,
        '01'                   AS DEL_TRGT_SBT,
        KK0171.ISP_NINSHO_ID   AS ISP_NINSHO_ID,
        NULL                   AS ADSL_NINSHO_ID,
        NULL                   AS PPP_NINSHO_ID
      , KK0081.SVC_CD          AS SVC_CD
      , KK0081.PRC_GRP_CD      AS PRC_GRP_CD
    FROM
        KK_T_SVC_KEI KK0081
    INNER JOIN KK_T_SVC_KEI_UCWK KK0161
        ON  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO
    INNER JOIN KK_T_SVKEIUW_EOH_NET KK0171
        ON  KK0171.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
        AND KK0171.GENE_ADD_DTM    = KK0161.GENE_ADD_DTM
    INNER JOIN KK_M_PRC_GRP KK0781
        ON  KK0781.PRC_GRP_CD = KK0081.PRC_GRP_CD
        AND KK0781.KAIHK_PSB_PRD IS NOT NULL
    WHERE
        KK0081.SVC_KEI_NO IS NOT NULL
    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.RSV_APLY_YMD <= ???1???
            AND    KK0081_GENE.RSV_APLY_CD   = '2'
            AND    KK0081_GENE.MK_FLG        = '0'
            GROUP BY KK0081_GENE.SVC_KEI_NO
        )
    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.RSV_APLY_YMD    <= ???2???
            AND    KK0161_GENE.RSV_APLY_CD      = '2'
            AND    KK0161_GENE.MK_FLG           = '0'
            GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO
        )
    AND (KK0781.PRC_GRP_CD, KK0781.RSV_APLY_YMD || KK0781.GENE_ADD_DTM) =
        (
            SELECT KK0781_GENE.PRC_GRP_CD, MAX(KK0781_GENE.RSV_APLY_YMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX
            FROM   KK_M_PRC_GRP KK0781_GENE
            WHERE  KK0781_GENE.PRC_GRP_CD    = KK0781.PRC_GRP_CD
            AND    KK0781_GENE.RSV_APLY_YMD <= ???3???
            AND    KK0781_GENE.MK_FLG        = '0'
            GROUP BY KK0781_GENE.PRC_GRP_CD
        )
    AND KK0081.SVC_CD                     = '01'
    AND KK0081.PRC_GRP_CD                 IN ('02', '03', '04')
    AND KK0081.SVCTK_BUT_DEL_TRN_JSSI_DTM IS NULL
    AND KK0081.MK_FLG                     = '0'
    AND (
           KK0161.PLAN_END_SBT_CD <> '1'
        OR KK0161.PLAN_END_SBT_CD IS NULL
    )
    AND KK0171.ISP_NINSHO_ID              IS NOT NULL
    AND (
          (
                KK0081.SVC_KEI_STAT               IN ('910')
            AND KK0081.SVC_ENDYMD                 IS NOT NULL
            AND TO_DATE(???4???) >= (TO_DATE(KK0081.SVC_ENDYMD) + KK0781.KAIHK_PSB_PRD)
          )
         OR 
          (
                KK0081.SVC_KEI_STAT               IN ('920')
            AND KK0081.SVC_CANCEL_YMD             IS NOT NULL
            AND TO_DATE(???5???) >= (TO_DATE(KK0081.SVC_CANCEL_YMD) + KK0781.KAIHK_PSB_PRD)
          )
    )
UNION ALL
    SELECT
        KK0081.SVC_KEI_NO      AS SVC_KEI_NO,
        KK0081.GENE_ADD_DTM    AS SVKEI_GADTM,
        KK0161.SVC_KEI_UCWK_NO AS SVC_KEI_UCWK_NO,
        KK0161.GENE_ADD_DTM    AS SVKEIUW_GADTM,
        DECODE((
            SELECT
                COUNT(*) AS ODR_CNT
            FROM
                KK_T_ODR_SET KK1041
            WHERE
                KK1041.SVC_KEI_NO      = KK0161.SVC_KEI_NO
            AND KK1041.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
            AND KK1041.ORDER_SBT_CD    = '1'
            AND KK1041.SVC_ORDER_CD    = '06'
            AND KK1041.RCNT_YOKYU_SBT_CD IN ('02', '03', '04', '07', '10', '11', '14')
            AND KK1041.MK_FLG          = '0'
            AND KK1041.ADD_DTM IS NOT NULL
        ), 0, '2', '3')        AS DEL_TRAN_SBT,
        '16'                   AS DEL_TRGT_SBT,
        NULL                   AS ISP_NINSHO_ID,
        KK0221.ADSL_NINSHO_ID  AS ADSL_NINSHO_ID,
        NULL                   AS PPP_NINSHO_ID
      , KK0081.SVC_CD          AS SVC_CD
      , KK0081.PRC_GRP_CD      AS PRC_GRP_CD
    FROM
        KK_T_SVC_KEI KK0081
    INNER JOIN KK_T_SVC_KEI_UCWK KK0161
        ON  KK0161.SVC_KEI_NO = KK0081.SVC_KEI_NO
    INNER JOIN KK_T_SVKEIUW_EOADSL KK0221
        ON  KK0221.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO
        AND KK0221.GENE_ADD_DTM    = KK0161.GENE_ADD_DTM
    INNER JOIN KK_M_PRC_GRP KK0781
        ON  KK0781.PRC_GRP_CD = KK0081.PRC_GRP_CD
        AND KK0781.KAIHK_PSB_PRD IS NOT NULL
    WHERE
        KK0081.SVC_KEI_NO IS NOT NULL
    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.RSV_APLY_YMD <= ???6???
            AND    KK0081_GENE.RSV_APLY_CD   = '2'
            AND    KK0081_GENE.MK_FLG        = '0'
            GROUP BY KK0081_GENE.SVC_KEI_NO
        )
    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.RSV_APLY_YMD    <= ???7???
            AND    KK0161_GENE.RSV_APLY_CD      = '2'
            AND    KK0161_GENE.MK_FLG           = '0'
            GROUP BY KK0161_GENE.SVC_KEI_UCWK_NO
        )
    AND (KK0781.PRC_GRP_CD, KK0781.RSV_APLY_YMD || KK0781.GENE_ADD_DTM) =
        (
            SELECT KK0781_GENE.PRC_GRP_CD, MAX(KK0781_GENE.RSV_APLY_YMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX
            FROM   KK_M_PRC_GRP KK0781_GENE
            WHERE  KK0781_GENE.PRC_GRP_CD    = KK0781.PRC_GRP_CD
            AND    KK0781_GENE.RSV_APLY_YMD <= ???8???
            AND    KK0781_GENE.MK_FLG        = '0'
            GROUP BY KK0781_GENE.PRC_GRP_CD
        )
    AND KK0081.SVC_CD                     = '01'
    AND KK0081.PRC_GRP_CD                 IN ('06')
    AND KK0081.SVCTK_BUT_DEL_TRN_JSSI_DTM IS NULL
    AND KK0081.MK_FLG                     = '0'
    AND KK0221.ADSL_NINSHO_ID             IS NOT NULL
    AND (
          (
                KK0081.SVC_KEI_STAT               IN ('910')
            AND KK0081.SVC_ENDYMD                 IS NOT NULL
            AND TO_DATE(???9???) >= (TO_DATE(KK0081.SVC_ENDYMD) + KK0781.KAIHK_PSB_PRD)
          )
         OR 
          (
                KK0081.SVC_KEI_STAT               IN ('920')
            AND KK0081.SVC_CANCEL_YMD             IS NOT NULL
            AND TO_DATE(???10???) >= (TO_DATE(KK0081.SVC_CANCEL_YMD) + KK0781.KAIHK_PSB_PRD)
          )
    )
UNION ALL
    SELECT
        KK0081.SVC_KEI_NO      AS SVC_KEI_NO,
        KK0081.GENE_ADD_DTM    AS SVKEI_GADTM,
        NULL                   AS SVC_KEI_UCWK_NO,
        NULL                   AS SVKEIUW_GADTM,
        DECODE((
            SELECT
                COUNT(*) AS ODR_CNT
            FROM
                KK_T_ODR_SET KK1041
            WHERE
                KK1041.SVC_KEI_NO      = KK0081.SVC_KEI_NO
            AND KK1041.ORDER_SBT_CD    = '2'
            AND KK1041.SVC_ORDER_CD    = '23'
            AND KK1041.MK_FLG          = '0'
            AND KK1041.ADD_DTM IS NOT NULL
        ), 0, '2', '3')        AS DEL_TRAN_SBT,
        '06'                   AS DEL_TRGT_SBT,
        NULL                   AS ISP_NINSHO_ID,
        NULL                   AS ADSL_NINSHO_ID,
        KK0101.PPP_NINSHO_ID   AS PPP_NINSHO_ID
      , KK0081.SVC_CD          AS SVC_CD
      , KK0081.PRC_GRP_CD      AS PRC_GRP_CD
    FROM
        KK_T_SVC_KEI KK0081
    INNER JOIN KK_T_SVC_KEI_EOH_TEL KK0101
        ON  KK0101.SVC_KEI_NO   = KK0081.SVC_KEI_NO
        AND KK0101.GENE_ADD_DTM = KK0081.GENE_ADD_DTM
    INNER JOIN KK_M_PRC_GRP KK0781
        ON  KK0781.PRC_GRP_CD = KK0081.PRC_GRP_CD
        AND KK0781.KAIHK_PSB_PRD IS NOT NULL
    WHERE
        KK0081.SVC_KEI_NO IS NOT NULL
    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.RSV_APLY_YMD <= ???11???
            AND    KK0081_GENE.RSV_APLY_CD   = '2'
            AND    KK0081_GENE.MK_FLG        = '0'
            GROUP BY KK0081_GENE.SVC_KEI_NO
        )
    AND (KK0781.PRC_GRP_CD, KK0781.RSV_APLY_YMD || KK0781.GENE_ADD_DTM) =
        (
            SELECT KK0781_GENE.PRC_GRP_CD, MAX(KK0781_GENE.RSV_APLY_YMD || KK0781_GENE.GENE_ADD_DTM) AS KK0781_MAX
            FROM   KK_M_PRC_GRP KK0781_GENE
            WHERE  KK0781_GENE.PRC_GRP_CD    = KK0781.PRC_GRP_CD
            AND    KK0781_GENE.RSV_APLY_YMD <= ???12???
            AND    KK0781_GENE.MK_FLG        = '0'
            GROUP BY KK0781_GENE.PRC_GRP_CD
        )
    AND KK0081.SVC_CD                     = '02'
    AND KK0081.SVCTK_BUT_DEL_TRN_JSSI_DTM IS NULL
    AND KK0081.MK_FLG                     = '0'
    AND KK0101.PPP_NINSHO_ID              IS NOT NULL
    AND (
          (
                KK0081.SVC_KEI_STAT               IN ('910')
            AND KK0081.SVC_ENDYMD                 IS NOT NULL
            AND TO_DATE(???13???) >= (TO_DATE(KK0081.SVC_ENDYMD) + KK0781.KAIHK_PSB_PRD)
          )
         OR 
          (
                KK0081.SVC_KEI_STAT               IN ('920')
            AND KK0081.SVC_CANCEL_YMD             IS NOT NULL
            AND TO_DATE(???14???) >= (TO_DATE(KK0081.SVC_CANCEL_YMD) + KK0781.KAIHK_PSB_PRD)
          )
    )
) KK0161_BASE