You don't appear to need the row_number approach because all you derive from that view is the latest (maximum) date.
instead of this:
inner join allscripts_oracle_project_audit_view v on v.Project_Code = inv.code and v.COLUMN_NAME = 'is_active' and v.System_Value_After = 0 and v.date_changed
BETWEEN $P{start_date} AND $P{end_date}
try this
INNER JOIN (
SELECT
av.Project_Code
, MAX(av.date_changed) AS date_changed
FROM allscripts_oracle_project_audit_view av
WHERE av.COLUMN_NAME = 'is_active'
AND av.System_Value_After = 0
AND av.date_changed BETWEEN $P{start_date} AND $P{end_date}
GROUP BY
av.Project_Code
) v ON v.Project_Code = inv.code
and remove row_number() and related where
SELECT
o.oracle_project_id
, o.client_id
, o.mhs_client
, v.Date_Changed
, stg.name stage
FROM inv_investments inv
INNER JOIN inv_projects p ON inv.id = p.prid
AND p.is_template = 0
AND p.IS_PROGRAM = 0
INNER JOIN odf_ca_project o ON o.id = inv.id
INNER JOIN (
SELECT
av.Project_Code
, MAX(av.date_changed) AS date_changed
FROM allscripts_oracle_project_audit_view av
WHERE av.COLUMN_NAME = 'is_active'
AND av.System_Value_After = 0
AND av.date_changed BETWEEN $P{start_date} AND $P{end_date}
GROUP BY
av.Project_Code
) v ON v.Project_Code = inv.code
LEFT OUTER JOIN CMN_LOOKUPS_V stg ON inv.stage_code = stg.lookup_code
AND stg.lookup_type = 'INV_STAGE_TYPE'
AND stg.language_code = 'en'
AND stg.is_active = '1'
AND stg.parent_lookup_code = 'mhs_stage'
INNER JOIN prj_obs_associations o2 ON inv.ID = o2.RECORD_ID
AND o2.table_name = 'SRM_PROJECTS'
AND o2.unit_id IN (
(SELECT
n.child_obs_unit_id
FROM PRJ_OBS_UNITS o
, NBI_DIM_OBS_FLAT n
WHERE o.name IN ('Allscripts Services')
AND o.dept = 2
AND n.parent_obs_unit_id = o.id)
)
INNER JOIN (
SELECT
o4.oracle_project_id
FROM odf_ca_project o4
JOIN srm_projects p2 ON o4.id = p2.id
WHERE p2.is_program = 0
GROUP BY
o4.oracle_project_id
HAVING SUM(p2.is_active) = '0'
) o3 ON o3.oracle_project_id = o.oracle_project_id
WHERE o.oracle_project_id IS NOT NULL
=======================
tip:
Be VERY careful with how you define date ranges. Best practice is to avoid between for date ranges.
Much more reliable is >= plus <
e.g.
AND av.date_changed >= '2015-12-01' and av.date_changed < '2016-01-01'
For more on this topic please see: Beware of Between
the best practice with date and time ranges is to avoid BETWEEN and to always use the form: WHERE col >= '20120101' AND col < '20120201' This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan