oracle with

WITH person AS
 (SELECT ppf.person_id, ppf.last_name
    FROM per_people_f ppf
   WHERE 1 = 1
     AND trunc(SYSDATE) BETWEEN ppf.effective_start_date AND
         ppf.effective_end_date)
SELECT temp.rn,
       temp.fsys_sq,
       temp.fcust_name_cn,
       temp.cust_grade_by,
       temp.fproject_name,
       temp.fproject_remark,
       temp.pick_person_name,
       temp.fflow_type_code,
       temp.fproject_no,
       temp.last_service_person,
       temp.log_date,
       temp.last_service_type,
       temp.last_service_content,
       temp.last_service_date,
       temp.data_source,
       temp.forh_resp_person_id,
       decode(temp.reg_flag, 1, 'Y', NULL) reg_flag,
       temp.province,
       temp.city,
       temp.pool_category,
       temp.ly_sale_amt,
       temp.ty_sale_amt,
       temp.brand,
       temp.sq
  FROM (SELECT rownum rn, b.*
          FROM (SELECT a.*,
                       row_number() over(PARTITION BY fsys_sq ORDER BY last_service_date DESC) sq
                  FROM (SELECT pr.fsys_sq,
                               pr.fcust_name_cn,
                               pr.cust_grade_by,
                               pr.fproject_name,
                               pr.fproject_remark,
                               pick.last_name pick_person_name,
                               pr.fflow_type_code,
                               pr.fproject_no,
                               last_service.last_name last_service_person,
                               to_char(cwl.log_date, 'YYYY/MM/DD') log_date,
                               clv.meaning last_service_type,
                               cwl.log_name last_service_content,
                               cwl.log_date last_service_date,
                               s.meaning data_source,
                               pr.forh_resp_person_id,
                               nvl(ssr.req_flag, 2) reg_flag,
                               pf.cust_province province,
                               pf.cust_city city,
                               pool_category,
                               round(pf.ly_sale_amt, 0) ly_sale_amt,
                               round(pf.ty_sale_amt, 0) ty_sale_amt,
                               pf.brand
                          FROM person last_service,
                               person pick,
                               cop_lookup_values clv,
                               cop_work_log cwl,
                               cop_tse_project_v pr,
                               cop_lookup_values s,
                               (SELECT ssr.cust_party_id,
                                       1 req_flag,
                                       row_number() over(PARTITION BY ssr.cust_party_id ORDER BY 1) rn
                                  FROM secom_service_registers ssr
                                 WHERE ssr.user_status = 'OPEN'
                                   AND nvl(ssr.end_date_active, SYSDATE + 1) >
                                       SYSDATE
                                   AND ssr.sales_resp_id = 1) ssr,
                               secom_cust_performance pf
                         WHERE 1 = 1
                           AND last_service.person_id(+) = cwl.log_owner
                           AND pick.person_id(+) = pr.forh_resp_person_id
                           AND cwl.project_no(+) = pr.fproject_no
                           AND pr.fcust_party_id = pf.party_id(+)
                           AND ssr.rn(+) = 1
                           AND ssr.cust_party_id(+) = pr.fcust_party_id
                           AND s.lookup_code(+) = pr.data_source
                           AND clv.lookup_type(+) = 'WORK_LOG_TYPE'
                           AND clv.lookup_code(+) = cwl.log_type
                           AND pr.fstep_name NOT IN (9)
                           AND 1 = 1) a
                         /*  ORDER BY a.reg_flag ASC,  a.fsys_sq DESC*/
                         ORDER BY ) b
         WHERE b.sq = 1) temp
 WHERE temp.rn > 0
   AND temp.rn <= 100