SELECT ID, salesperson, consultantid,
       (SELECT MAX (employee_hist_key)
          FROM edw.employee_vw
         WHERE operator_id = t.consultantid) sckey, activityid,
       (SELECT MAX (activityflag)
          FROM edw_stage.psm_activitylookup p
         WHERE p.activityid = t.activityid) aflag, activitydate,
       (SELECT MAX (date_key)
          FROM edw.dates
         WHERE dates = t.activitydate) dkey, DURATION, customer,
       (SELECT MAX (company_address_source_key)
          FROM edw.company_address_source_dim
         WHERE source_system = 'SFDC'
           AND UPPER (company_name) = t.customer) co_key,
       comments, deleteflag, lastupdated, opportunityidnumber
  FROM (SELECT ROW_NUMBER () OVER (PARTITION BY ID, consultantid, activityid, activitydate ORDER BY lastupdated DESC) distinct_ct,
               ID, salesperson, consultantid, activityid, activitydate,
               DURATION, customer, comments, deleteflag, lastupdated,
               opportunityidnumber
          FROM edw_stage.psm_activity psm_activity) t
 WHERE distinct_ct = 1 AND lastupdated > SYSDATE - 4

 Plan	Object_Name	Obj_type	Bytes	Cost	Cardinality	CPU_COST	IO_COST	ACCESS_PRED	FILTER_PRED
Select Statement			261905994	6893	692873	1126464456	6808		
  1.1 Sort (Aggregate)			60		1				
    2.1 View	EMPLOYEE_VW	VIEW	120	6	2	13312300	5		
      3.1 Sort (Group By)			788	6	2	13312300	5		
        4.1 Table Access (By Index Rowid)	EMPLOYEE_HIST	TABLE	788	5	2	92364	5		"EFF_END_DATE">SYSDATE@! OR "EFF_END_DATE" IS NULL
          5.1 Index (Range Scan)	EMP_HIST_OPERATOR_ID	INDEX		1	9	17836	1	"OPERATOR_ID"=:B1	
  1.2 Sort (Aggregate)			16		1				
    2.1 Table Access (Full)	PSM_ACTIVITYLOOKUP	TABLE	16	3	1	53417	3		"P"."ACTIVITYID"=:B1
  1.3 Sort (Aggregate)			13		1				
    2.1 Table Access (By Index Rowid)	DATES	TABLE	13	2	1	31562	2		
      3.1 Index (Range Scan)	DATES_IDX02	INDEX		1	1	16236	1	"DATES"=:B1	
  1.4 Sort (Aggregate)			34		1				
    2.1 Table Access (Full)	COMPANY_ADDRESS_SOURCE_DIM	TABLE	140930	8506	4145	696898590	8453		"SOURCE_SYSTEM"='SFDC' AND UPPER("COMPANY_NAME")=:B1
  1.5 View			261905994	6893	692873	1126464456	6808		"DISTINCT_CT"=1 AND "LASTUPDATED">SYSDATE@!-4
    2.1 Window (Sort Pushed Rank)			62358570	6893	692873	1126464456	6808		ROW_NUMBER() OVER ( PARTITION BY "ID","CONSULTANTID","ACTIVITYID","ACTIVITYDATE" ORDER BY INTERNAL_FUNCTION("LASTUPDATED") DESC )<=1
      3.1 Table Access (Full)	PSM_ACTIVITY	TABLE	62358570	1188	692873	280314622	1167		

(SELECT ROW_NUMBER () OVER (PARTITION BY ID, consultantid, activityid, activitydate ORDER BY lastupdated DESC) distinct_ct,
               ID, salesperson, consultantid, activityid, activitydate,
               DURATION, customer, comments, deleteflag, lastupdated,
               opportunityidnumber
          FROM edw_stage.psm_activity psm_activity) t
 WHERE distinct_ct = 1 AND lastupdated > SYSDATE - 4