This seems to do the SQL job:


With Recursive
RecurseCGS as
(
Select ID from CGS_Structure
where ID = :ID_RootCGS
Union all
Select Child.ID from CGS_Structure Child, RecurseCGS Parent
where Child.Parent_ID = Parent.id
)
Select * from
(
select p.ID, p.BIZTYPE_DESCRIPTION, p.CostingType_Description, p.Description, p.CGS_Description, p.Date_Closing, p.TOTAL_REVENUE, p.EXCHANGE_RATE, p.CGSSHARE_PCT, p.CGS_REVENUE
from V_BD_Biz_Prim p, RecurseCGS
where ((p.BIZTYPE_ID = :BizType_ID and p.CGS_ID = RecurseCGS.ID and p.Date_Closing between :Date_Begin and :Date_End)
and
(
p.TOTAL_REVENUE is null or p.TOTAL_REVENUE = 0
or p.EXCHANGE_RATE is null or p.EXCHANGE_RATE = 0
or p.CGSSHARE_PCT is null or p.CGSSHARE_PCT = 0
))
or
(p.BIZTYPE_ID = :BizType_ID and p.CGS_ID = RecurseCGS.ID and p.Date_Closing is null)
)
order by CGS_Description, Date_Closing desc, ID