AnsweredAssumed Answered

HELP- Parameter issue need to fix ASAP

Question asked by ma12 on Oct 2, 2013
Latest reply on Oct 10, 2013 by ma12
Hello,


We have a custom built crystal report created using SQL which is not working properly. This report needs to be fixed ASAP.

Initially when the reports was created the parameters where all single select. OLD query below for parameters. The requirements got changed and was asked to make "Project ID" multiselect and made the changes to the parameter for INV.ID highlighted in the NEW QUERY.

The issue is the Project ID is working fin but no other parameters are returning any value.

Appreciate all help.

Thanks,
MN

---------------------------------------------------------------------------------------------------------------------------
OLD QUERY

where
1=1 AND
({?param_inv_obs} = 0
OR INV.id IN (SELECT DISTINCT obsa.record_id
FROM prj_obs_associations obsa
INNER JOIN prj_obs_units_flat obsf ON obsa.unit_id = obsf.unit_id
INNER JOIN OBS_UNITS_FLAT_BY_MODE FLAT ON obsa.unit_id =FLAT.linked_unit_id
WHERE obsa.table_name = 'SRM_PROJECTS'
AND obsf.branch_unit_id = {?param_inv_obs}
AND ((nvl( '{?param_obs_mode}','OBS_UNIT_AND_CHILDREN') = 'OBS_UNIT_AND_CHILDREN') or (FLAT.unit_mode = '{?param_obs_mode}'))
)) AND

NVL(INV.MANAGER_ID,-1) = DECODE({?param_mgr},0,NVL(INV.MANAGER_ID,-1),{?param_mgr}) AND

(NVL('{?param_bus_spon}','XYZ') = 'XYZ' OR upper(OCP.si_bus_sponsor) = upper('{?param_bus_spon}') )AND
( NVL('{?param_itvp}','XYZ') = 'XYZ' OR upper(ocp.si_it_vp) = upper('{?param_itvp}')) AND

NVL(INV.ID,-1) = DECODE({?param_project},0,NVL(INV.id,-1),{?param_project}) AND
(NVL('{?param_port_man}','XYZ') = 'XYZ' OR upper(OCP.si_portfolio_mngr) = upper('{?param_port_man}')) AND
INV.IS_ACTIVE = {?param_active}


NEW QUERY

where
1=1 AND
({?param_inv_obs} = 0
OR INV.id IN (SELECT DISTINCT obsa.record_id
FROM prj_obs_associations obsa
INNER JOIN prj_obs_units_flat obsf ON obsa.unit_id = obsf.unit_id
INNER JOIN OBS_UNITS_FLAT_BY_MODE FLAT ON obsa.unit_id =FLAT.linked_unit_id
WHERE obsa.table_name = 'SRM_PROJECTS'
AND obsf.branch_unit_id = {?param_inv_obs}
AND ((nvl( '{?param_obs_mode}','OBS_UNIT_AND_CHILDREN') = 'OBS_UNIT_AND_CHILDREN') or (FLAT.unit_mode = '{?param_obs_mode}'))
)) AND

NVL(INV.MANAGER_ID,-1) = DECODE({?param_mgr},0,NVL(INV.MANAGER_ID,-1),{?param_mgr}) AND

(NVL('{?param_bus_spon}','XYZ') = 'XYZ' OR upper(OCP.si_bus_sponsor) = upper('{?param_bus_spon}') )AND
( NVL('{?param_itvp}','XYZ') = 'XYZ' OR upper(ocp.si_it_vp) = upper('{?param_itvp}')) AND

(INV.ID IN {?param_project}) AND
(NVL('{?param_port_man}','XYZ') = 'XYZ' OR upper(OCP.si_portfolio_mngr) = upper('{?param_port_man}'))

Outcomes