Clarity

In my filter I have a project ID parameter. When nothing selected I would like to pass all projects available into my nsql statement. Is there a way to do so?

right now my code reads as follow:

AND COSTPLAN.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

When nothing selected it's passing null and therefore it's messing my data result. Please help.

thanks,

-Georges

Hi Georges.

Try

AND ((COSTPLAN.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@) OR (@WHERE:PARAM:USER_DEF:integer:project_id@ IS NULL))

or...

AND COSTPLAN.OBJECT_ID=@NVL@(@WHERE:PARAM:USER_DEF:integer:project_id@)

Hi Robert,

This line of code will allow me to take the null value but won't fix my issue. It's adding all the values together for all the projects. So if I take this line out i get the same results. I want to be able to process each project at a time. My portlet is displaying the cost plan details for all the projects one calculated field is the actual and the planned cost. With ur suggestion it's adding the actuals and planned for all the projects. Without ur suggestion when no project is selected it's displaying 0.

thanks,

-Georges

Paste the nsql here so we can take a look at the query.

Sorry I have posted the wrong code previously, I deleted the other one and this is the right code where it has the " @WHERE:PARAM:USER_DEF:integer:project_id@.

here:

SELECT

       @select:dim:user_def:implied:project:rownum:rownum1@,

       @select:dim_prop:user_def:implied:project:project_id:project_id@,

       @select:dim_prop:user_def:implied:project:project_code:project_code@,

       @select:dim_prop:user_def:implied:project:project_name:project_name@,

       @select:dim_prop:user_def:implied:project:ChargeCode:ChargeCode@,

       @select:dim_prop:user_def:implied:project:ChargeCode_id:ChargeCode_id@,

       @select:dim_prop:user_def:implied:project:TransactionClass:TransactionClass@,

@select:dim_prop:user_def:implied:project:ic_stage_name:ic_stage_name@,

@select:dim_prop:user_def:implied:project:ic_stage_id:ic_stage_id@,

@select:dim_prop:user_def:implied:project:ic_tier_name:ic_tier_name@,

@select:dim_prop:user_def:implied:project:ic_tier_id:ic_tier_id@,

@select:dim_prop:user_def:implied:project:ic_status_id:ic_status_id@,

@select:dim_prop:user_def:implied:project:ic_it_enabled:ic_it_enabled@,

@select:dim_prop:user_def:implied:project:ic_dept_gov:ic_dept_gov@,

@select:dim_prop:user_def:implied:project:OBS_Name:OBS_Name@,

@select:dim_prop:user_def:implied:project:OBS_ID:OBS_ID@,

@select:dim_prop:user_def:implied:project:ProgramArchitecture:ProgramArchitecture@,

@select:dim_prop:user_def:implied:project:F1_Planned:F1_Planned@,

@select:dim_prop:user_def:implied:project:F2_Planned:F2_Planned@,

@select:dim_prop:user_def:implied:project:F3_Planned:F3_Planned@,

@select:dim_prop:user_def:implied:project:F4_Planned:F4_Planned@,

@select:dim_prop:user_def:implied:project:F5_Planned:F5_Planned@,

@select:dim_prop:user_def:implied:project:F1_Actual:F1_Actual@,

@select:dim_prop:user_def:implied:project:F2_Actual:F2_Actual@,

@select:dim_prop:user_def:implied:project:F3_Actual:F3_Actual@,

@select:dim_prop:user_def:implied:project:F4_Actual:F4_Actual@,

@select:dim_prop:user_def:implied:project:F5_Actual:F5_Actual@

From(

select INV.CODE project_code, 

  INV.NAME project_name, 

Proj.ic_it_enabled  ic_it_enabled ,

Proj.ic_dept_gov  ic_dept_gov,

(select obs_units.id from odf_ca_project proj

left join INV_PROJECTS on INV_PROJECTS.prid=proj.id

left join PAC_MNT_PROJECTS pac_obs on pac_obs.id=proj.id 

left join PRJ_OBS_UNITS obs_units on obs_units.unique_name=pac_obs.DEPARTCODE

LEFT JOIN (SELECT D.DEPARTCODE DEPARTCODE_lkp_code,D.DESCRIPTION DEPARTCODE_lkp_description FROM DEPARTMENTS D) DEPARTCODE_lkp ON DEPARTCODE_lkp.DEPARTCODE_lkp_code=pac_obs.DEPARTCODE

where proj.id = inv.id) OBS_ID,

(select name OBS_Name from odf_ca_project proj

left join INV_PROJECTS on INV_PROJECTS.prid=proj.id

left join PAC_MNT_PROJECTS pac_obs on pac_obs.id=proj.id 

left join PRJ_OBS_UNITS obs_units on obs_units.unique_name=pac_obs.DEPARTCODE

left join (SELECT D.DEPARTCODE DEPARTCODE_lkp_code,D.DESCRIPTION DEPARTCODE_lkp_description FROM DEPARTMENTS D) DEPARTCODE_lkp on DEPARTCODE_lkp.DEPARTCODE_lkp_code=pac_obs.DEPARTCODE

where proj.id = inv.id) OBS_Name

  , (select paa_name from odf_ca_project proj left join (select paa_obs.id PAA_ID, paa_obs.parent_id PAA_PARENT ,paa_obs.name PAA_NAME, paa_***.record_id PAA_PROJ_ID from PRJ_OBS_UNITS paa_obs,     PRJ_OBS_ASSOCIATIONS paa_***

WHERE paa_obs.ID=paa_***.unit_id AND paa_***.table_name = 'SRM_PROJECTS'

and paa_obs.type_id in (select id from PRJ_OBS_TYPES where unique_name = 'IC_PAA')) PAA_TBL on PAA_TBL.PAA_PROJ_ID=proj.id where inv.id=proj.id ) ProgramArchitecture,

  FP.TOTAL_COST,    

  PRC.PRNAME ChargeCode,

  PRC.PRID ChargeCode_id,

  TRAN.DESCRIPTION TransactionClass,

  FP_DET.TOTAL_COST TotalPlanned,

  F1.current_amount F1_Planned,

  F2.current_amount F2_Planned,

  F3.current_amount F3_Planned,

  F4.current_amount F4_Planned,

  F5.current_amount F5_Planned,

  A1.total F1_Actual,

  A2.total F2_Actual,

  A3.total F3_Actual,

  A4.total F4_Actual,

  A5.total F5_Actual,

  ProjStage.ic_stage_id,

  ProjStage.ic_stage_name,

  ProjTier.ic_tier_id ic_tier_id,

  ProjTier.ic_tier_name,

  ProjStatus.ic_status_id ic_status_id,

  ProjStatus.ic_status_name ic_status_name,

  inv.ID project_id

     FROM INV_INVESTMENTS INV 

INNER JOIN FIN_PLANS FP

  ON INV.ID = FP.OBJECT_ID 

            AND FP.OBJECT_CODE  ='project' 

            AND PLAN_TYPE_CODE  ='FORECAST' 

            AND IS_PLAN_OF_RECORD=1 

LEFT OUTER JOIN CMN_LOOKUPS_V LKUP

   ON FP.STATUS_CODE=LKUP.LOOKUP_CODE 

            AND LKUP.LOOKUP_TYPE  ='FIN_PLAN_STATUS' 

            AND LKUP.LANGUAGE_CODE= 'en'

LEFT OUTER JOIN FIN_COST_PLAN_DETAILS FP_DET

   ON FP.ID=FP_DET.PLAN_ID 

LEFT OUTER JOIN TRANSCLASS TRAN

   ON FP_DET.TRANSACTION_CLASS_ID = TRAN.ID 

LEFT OUTER JOIN PRCHARGECODE PRC

   ON FP_DET.CHARGE_CODE_ID=PRC.PRID

LEFT OUTER JOIN ODF_SSL_CST_DTL_COST TSL

   ON FP_DET.ID=TSL.PRJ_OBJECT_ID 

left join odf_ca_project proj on proj.id = inv.id

LEFT JOIN (SELECT * 

   from

  (SELECT ROUND(SUM(FORECAST.SLICE*(TO_DATE(FORECAST.FINISH_DATE) - TO_DATE(FORECAST.START_DATE))),2) CURRENT_AMOUNT, plan_det.charge_code_id, plan_det.transaction_class_id

    FROM ODF_SSL_CST_DTL_COST FORECAST,

    FIN_COST_PLAN_DETAILS PLAN_DET,

    FIN_PLANS COSTPLAN,

    PRCHARGECODE CHARGECODE

    WHERE

  COSTPLAN.ID=PLAN_DET.PLAN_ID

    AND FORECAST.PRJ_OBJECT_ID=PLAN_DET.ID

    AND CHARGECODE.PRID=PLAN_DET.CHARGE_CODE_ID

AND COSTPLAN.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

   --AND CHARGECODE.prExternalID LIKE 'S35_%'

    AND COSTPLAN.IS_PLAN_OF_RECORD=1

    AND COSTPLAN.PLAN_TYPE_CODE='FORECAST'

    AND FORECAST.START_DATE between (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 04

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)

    )

    and

    (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 03

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+1) group by plan_det.charge_code_id, plan_det.transaction_class_id ) A

  LEFT JOIN prchargecode pr ON  pr.prid = A.charge_code_id

  LEFT JOIN TRANSCLASS TRAN  ON A.transaction_class_id= tran.ID) F1 on F1.transaction_class_id = tran.id and F1.charge_code_id = prc.prid

  LEFT JOIN (SELECT 

w.charge_code charge_code,

w.transclass transclass,

sum(TOTALCOST) total

  FROM  PPA_WIP_VALUES WV,

        FIN_PLANS PLANS,          

        PPA_WIP W

  WHERE W.INVESTMENT_ID = PLANS.OBJECT_ID

  AND W.TRANSNO = WV.TRANSNO

  AND WV.CURRENCY_TYPE = 'HOME'

  AND PLANS.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

  AND W.STATUS = 0

  AND PLANS.IS_PLAN_OF_RECORD=1

  AND PLANS.PLAN_TYPE_CODE='FORECAST'

-- AND W.CHARGE_CODE LIKE 'S35_%'

  AND W.TRANSDATE BETWEEN (to_date((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)||'-04-1','YYYY-MM-DD') )

  AND (to_date((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+1||'-03-31','YYYY-MM-DD')) GROUP BY w.charge_code, w.transclass ORDER BY w.charge_code) A1

  on A1.charge_code = prc.prexternalid and A1.transclass = tran.transclass

  LEFT JOIN (SELECT *

   from

  (SELECT ROUND(SUM(FORECAST.SLICE*(TO_DATE(FORECAST.FINISH_DATE) - TO_DATE(FORECAST.START_DATE))),2) CURRENT_AMOUNT, plan_det.charge_code_id, plan_det.transaction_class_id

    FROM ODF_SSL_CST_DTL_COST FORECAST,

    FIN_COST_PLAN_DETAILS PLAN_DET,

    FIN_PLANS COSTPLAN,

    PRCHARGECODE CHARGECODE

    WHERE

  COSTPLAN.ID=PLAN_DET.PLAN_ID

    AND FORECAST.PRJ_OBJECT_ID=PLAN_DET.ID

    AND CHARGECODE.PRID=PLAN_DET.CHARGE_CODE_ID

    AND COSTPLAN.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

   --AND CHARGECODE.prExternalID LIKE 'S35_%'

    AND COSTPLAN.IS_PLAN_OF_RECORD=1

    AND COSTPLAN.PLAN_TYPE_CODE='FORECAST'

    AND FORECAST.START_DATE between (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 04

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@ + 1)

    )

    and

    (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 03

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+2) group by plan_det.charge_code_id, plan_det.transaction_class_id ) A

  LEFT JOIN prchargecode pr ON  pr.prid = A.charge_code_id

  LEFT JOIN TRANSCLASS TRAN  ON A.transaction_class_id= tran.ID) F2 ON F2.transaction_class_id = tran.ID AND F2.charge_code_id = prc.prid

   LEFT JOIN (SELECT 

w.charge_code charge_code,

w.transclass transclass,

sum(TOTALCOST) total

  FROM  PPA_WIP_VALUES WV,

        FIN_PLANS PLANS,          

        PPA_WIP W

  WHERE W.INVESTMENT_ID = PLANS.OBJECT_ID

  AND W.TRANSNO = WV.TRANSNO

  AND WV.CURRENCY_TYPE = 'HOME'

  AND PLANS.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

  AND W.STATUS = 0

  AND PLANS.IS_PLAN_OF_RECORD=1

  AND PLANS.PLAN_TYPE_CODE='FORECAST'

-- AND W.CHARGE_CODE LIKE 'S35_%'

  AND W.TRANSDATE BETWEEN (to_date((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@ +1)||'-04-1','YYYY-MM-DD') )

  AND (to_date(((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+1) + 1||'-03-31','YYYY-MM-DD')) GROUP BY w.charge_code, w.transclass ORDER BY w.charge_code) A2

  on A2.charge_code = prc.prexternalid and A2.transclass = tran.transclass

LEFT JOIN (SELECT *

   from

  (SELECT ROUND(SUM(FORECAST.SLICE*(TO_DATE(FORECAST.FINISH_DATE) - TO_DATE(FORECAST.START_DATE))),2) CURRENT_AMOUNT, plan_det.charge_code_id, plan_det.transaction_class_id

    FROM ODF_SSL_CST_DTL_COST FORECAST,

    FIN_COST_PLAN_DETAILS PLAN_DET,

    FIN_PLANS COSTPLAN,

    PRCHARGECODE CHARGECODE

    WHERE

  COSTPLAN.ID=PLAN_DET.PLAN_ID

    AND FORECAST.PRJ_OBJECT_ID=PLAN_DET.ID

    AND CHARGECODE.PRID=PLAN_DET.CHARGE_CODE_ID

    AND COSTPLAN.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

   --AND CHARGECODE.prExternalID LIKE 'S35_%'

    AND COSTPLAN.IS_PLAN_OF_RECORD=1

    AND COSTPLAN.PLAN_TYPE_CODE='FORECAST'

    AND FORECAST.START_DATE between (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 04

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@ + 2)

    )

    and

    (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 03

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+3) group by plan_det.charge_code_id, plan_det.transaction_class_id ) A

  LEFT JOIN prchargecode pr ON  pr.prid = A.charge_code_id

  LEFT JOIN TRANSCLASS TRAN  ON A.transaction_class_id= tran.ID) F3 on F3.transaction_class_id = tran.id and F3.charge_code_id = prc.prid  

  LEFT JOIN (SELECT 

w.charge_code charge_code,

w.transclass transclass,

sum(TOTALCOST) total

  FROM  PPA_WIP_VALUES WV,

        FIN_PLANS PLANS,          

        PPA_WIP W

  WHERE W.INVESTMENT_ID = PLANS.OBJECT_ID

  AND W.TRANSNO = WV.TRANSNO

  AND WV.CURRENCY_TYPE = 'HOME'

  AND PLANS.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

  AND W.STATUS = 0

  AND PLANS.IS_PLAN_OF_RECORD=1

  AND PLANS.PLAN_TYPE_CODE='FORECAST'

-- AND W.CHARGE_CODE LIKE 'S35_%'

  AND W.TRANSDATE BETWEEN (to_date((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@ +2)||'-04-1','YYYY-MM-DD') )

  AND (to_date(((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+1) + 2||'-03-31','YYYY-MM-DD')) GROUP BY w.charge_code, w.transclass ORDER BY w.charge_code) A3

  on A3.charge_code = prc.prexternalid and A3.transclass = tran.transclass

  LEFT JOIN (SELECT *

   from

  (SELECT ROUND(SUM(FORECAST.SLICE*(TO_DATE(FORECAST.FINISH_DATE) - TO_DATE(FORECAST.START_DATE))),2) CURRENT_AMOUNT, plan_det.charge_code_id, plan_det.transaction_class_id

    FROM ODF_SSL_CST_DTL_COST FORECAST,

    FIN_COST_PLAN_DETAILS PLAN_DET,

    FIN_PLANS COSTPLAN,

    PRCHARGECODE CHARGECODE

    WHERE

  COSTPLAN.ID=PLAN_DET.PLAN_ID

    AND FORECAST.PRJ_OBJECT_ID=PLAN_DET.ID

    AND CHARGECODE.PRID=PLAN_DET.CHARGE_CODE_ID

    AND COSTPLAN.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

   --AND CHARGECODE.prExternalID LIKE 'S35_%'

    AND COSTPLAN.IS_PLAN_OF_RECORD=1

    AND COSTPLAN.PLAN_TYPE_CODE='FORECAST'

    AND FORECAST.START_DATE between (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 04

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@ + 3)

    )

    and

    (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 03

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+4) group by plan_det.charge_code_id, plan_det.transaction_class_id ) A

  LEFT JOIN prchargecode pr ON  pr.prid = A.charge_code_id

  LEFT JOIN TRANSCLASS TRAN  ON A.transaction_class_id= tran.ID) F4 on F4.transaction_class_id = tran.id and F4.charge_code_id = prc.prid

   LEFT JOIN (SELECT 

w.charge_code charge_code,

w.transclass transclass,

sum(TOTALCOST) total

  FROM  PPA_WIP_VALUES WV,

        FIN_PLANS PLANS,          

        PPA_WIP W

  WHERE W.INVESTMENT_ID = PLANS.OBJECT_ID

  AND W.TRANSNO = WV.TRANSNO

  AND WV.CURRENCY_TYPE = 'HOME'

  AND PLANS.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

  AND W.STATUS = 0

  AND PLANS.IS_PLAN_OF_RECORD=1

  AND PLANS.PLAN_TYPE_CODE='FORECAST'

-- AND W.CHARGE_CODE LIKE 'S35_%'

  AND W.TRANSDATE BETWEEN (to_date((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@ +3)||'-04-1','YYYY-MM-DD') )

  AND (to_date(((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+1) + 3||'-03-31','YYYY-MM-DD')) GROUP BY w.charge_code, w.transclass ORDER BY w.charge_code) A4

  on A4.charge_code = prc.prexternalid and A4.transclass = tran.transclass

  LEFT JOIN (SELECT *

   from

  (SELECT ROUND(SUM(FORECAST.SLICE*(TO_DATE(FORECAST.FINISH_DATE) - TO_DATE(FORECAST.START_DATE))),2) CURRENT_AMOUNT, plan_det.charge_code_id, plan_det.transaction_class_id

    FROM ODF_SSL_CST_DTL_COST FORECAST,

    FIN_COST_PLAN_DETAILS PLAN_DET,

    FIN_PLANS COSTPLAN,

    PRCHARGECODE CHARGECODE

    WHERE

  COSTPLAN.ID=PLAN_DET.PLAN_ID

    AND FORECAST.PRJ_OBJECT_ID=PLAN_DET.ID

    AND CHARGECODE.PRID=PLAN_DET.CHARGE_CODE_ID

    AND COSTPLAN.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

   --AND CHARGECODE.prExternalID LIKE 'S35_%'

    AND COSTPLAN.IS_PLAN_OF_RECORD=1

    AND COSTPLAN.PLAN_TYPE_CODE='FORECAST'

    AND FORECAST.START_DATE between (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 04

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@ + 4)

    )

    and

    (    select max(start_date) from BIZ_COM_PERIODS

    WHERE to_char(start_date,'mm') = 03

    and to_char(start_date,'yyyy') = (@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+5) group by plan_det.charge_code_id, plan_det.transaction_class_id ) A

  LEFT JOIN prchargecode pr ON  pr.prid = A.charge_code_id

  LEFT JOIN TRANSCLASS TRAN  ON A.transaction_class_id= tran.ID) F5 ON F5.transaction_class_id = tran.ID AND F5.charge_code_id = prc.prid

   LEFT JOIN (SELECT 

w.charge_code charge_code,

w.transclass transclass,

sum(TOTALCOST) total

  FROM  PPA_WIP_VALUES WV,

        FIN_PLANS PLANS,          

        PPA_WIP W

  WHERE W.INVESTMENT_ID = PLANS.OBJECT_ID

  AND W.TRANSNO = WV.TRANSNO

  AND WV.CURRENCY_TYPE = 'HOME'

  AND PLANS.OBJECT_ID=@WHERE:PARAM:USER_DEF:integer:project_id@

  AND W.STATUS = 0

  AND PLANS.IS_PLAN_OF_RECORD=1

  AND PLANS.PLAN_TYPE_CODE='FORECAST'

-- AND W.CHARGE_CODE LIKE 'S35_%'

  AND W.TRANSDATE BETWEEN (to_date((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@ +4)||'-04-1','YYYY-MM-DD') )

  AND (to_date(((@WHERE:PARAM:USER_DEF:STRING:fiscal_year@)+1) + 4||'-03-31','YYYY-MM-DD')) GROUP BY w.charge_code, w.transclass ORDER BY w.charge_code) A5

  ON A5.charge_code = prc.prexternalid AND A5.transclass = tran.transclass

  LEFT JOIN

  (SELECT ic_stg_lkp.id ic_stage_id, ic_stg_lkp.name ic_stage_name, proj.id ProjID

  FROM ODF_CA_PROJECT proj

  LEFT JOIN cmn_lookups_v ic_stg_lkp     

  ON ic_stg_lkp.lookup_code=proj.ic_stage    

  AND ic_stg_lkp.lookup_type='IC_STAGE_LOOKUP'    

  AND ic_stg_lkp.language_code='en'   

  AND ic_stg_lkp.is_active=1) ProjStage ON inv.ID = ProjStage.ProjID

   LEFT JOIN

  (SELECT ic_tier_lkp.id ic_tier_id, ic_tier_lkp.name ic_tier_name, proj.id ProjID

  FROM ODF_CA_PROJECT proj

  LEFT JOIN cmn_lookups_v ic_tier_lkp

  ON ic_tier_lkp.LOOKUP_CODE=proj.ic_tier    

  AND ic_tier_lkp.lookup_type='IC_TIER_LOOKUP'    

  AND ic_tier_lkp.language_code='en'  

  AND ic_tier_lkp.is_active=1) ProjTier ON inv.ID = ProjTier.ProjID

   LEFT JOIN

  (SELECT ic_status_lkp.id ic_status_id, ic_status_lkp.name ic_status_name, proj.id ProjID

  FROM ODF_CA_PROJECT proj 

  LEFT JOIN cmn_lookups_v ic_status_lkp  

  ON ic_status_lkp.lookup_code=proj.ic_status

  AND ic_status_lkp.lookup_type='IC_STATUS_LOOKUP'

  AND ic_status_lkp.language_code='en'

  AND ic_status_lkp.is_active=1) ProjStatus ON inv.ID = ProjStatus.ProjID

,biz_com_periods biz_year

where 1=1

AND EXISTS (SELECT 1

FROM PRJ_OBS_UNITS UNITS WHERE TYPE_ID=(select id from PRJ_OBS_TYPES where unique_name='IC_OBS') AND ID=units.id

START WITH id = NVL(@WHERE:PARAM:USER_DEF:INTEGER:OBS_id@,(select id from PRJ_OBS_TYPES where unique_name='IC_OBS'))

CONNECT BY PRIOR id=parent_id)

and biz_year.p_year=@WHERE:PARAM:USER_DEF:STRING:fiscal_year@

  and  biz_year.period_type='ANNUALLY' and biz_year.is_active=1

AND    (@WHERE:PARAM:USER_DEF:integer:project_id@ is null or inv.id = @WHERE:PARAM:USER_DEF:integer:project_id@)

AND    (@WHERE:PARAM:USER_DEF:STRING:ic_it_enabled@ is null or PROJ.ic_it_enabled = @WHERE:PARAM:USER_DEF:STRING:ic_it_enabled@)

AND    (@WHERE:PARAM:USER_DEF:STRING:ic_dept_gov@ is null or PROJ.ic_dept_gov = @WHERE:PARAM:USER_DEF:STRING:ic_dept_gov@)

AND    (@WHERE:PARAM:USER_DEF:integer:ic_stage_id@ is null or PROJStage.ic_stage_id = @WHERE:PARAM:USER_DEF:integer:ic_stage_id@)

AND    (@WHERE:PARAM:USER_DEF:integer:ic_tier_id@ is null or PROJTier.ic_tier_id = @WHERE:PARAM:USER_DEF:integer:ic_tier_id@)

AND    (@WHERE:PARAM:USER_DEF:integer:ic_status_id@ is null or PROJStatus.ic_status_id = @WHERE:PARAM:USER_DEF:integer:ic_status_id@)

and (@WHERE:PARAM:USER_DEF:integer:obs_id@ is null or

(select obs_units.id from odf_ca_project proj

left join INV_PROJECTS on INV_PROJECTS.prid=proj.id

left join PAC_MNT_PROJECTS pac_obs on pac_obs.id=proj.id 

left join PRJ_OBS_UNITS obs_units on obs_units.unique_name=pac_obs.DEPARTCODE

LEFT JOIN (SELECT D.DEPARTCODE DEPARTCODE_lkp_code,D.DESCRIPTION DEPARTCODE_lkp_description FROM DEPARTMENTS D) DEPARTCODE_lkp ON DEPARTCODE_lkp.DEPARTCODE_lkp_code=pac_obs.DEPARTCODE

where proj.id = inv.id) = @WHERE:PARAM:USER_DEF:integer:obs_id@)

--inv.id =inv.id

AND @FILTER@

GROUP BY INV.CODE, 

  INV.NAME,  

  FP.TOTAL_COST,    

  PRC.PRNAME,

PRC.PRID,

  TRAN.DESCRIPTION,

  FP_DET.TOTAL_COST,

  F1.current_amount,

  F2.current_amount,

  F3.current_amount,

  F4.current_amount,

  F5.current_amount,

  A1.total,

  A2.total,

  A3.total,

  A4.total,

  A5.total,

  ProjStage.ic_stage_id,

  ProjStage.ic_stage_name,

  ProjTier.ic_tier_id,

  ProjTier.ic_tier_name,

  ProjStatus.ic_status_id,

  ProjStatus.ic_status_name,

  Proj.ic_it_enabled,

  Proj.ic_dept_gov,

  inv.ID

  order by prc.prname, tran.description

  )

does the nsql wrapped sql work correctly in your sql editor? i.e. return all projects

Yes it does. Except for the Planned and actual values it doesn't display the correct values (either 0, or sum of all) I must filter on a project to get the right values.

Actually my sql won't return all the projects I need the nsql parameter in order to achieve that. Right now in sql I'm hard coding the project ID value, which can be done once at a time. I need something like

AND COSTPLAN.OBJECT_ID=inv.id

but inv.id is an invalid identifier, and if i add the inv_investments table to my select statement it will cross join and it will mess my values.