0 then deptitem_amt else 0.00 end as dept_amt, case when e.sign_flag < 0 then deptitem_amt else 0.00 end as with_amt , 0 as ERI_amt, 0 as ERO_amt from dpdeptstatement a, dpdepttype c, dpdeptmaster d , dpucfdeptitemtype e, mbmembmaster m, mbucfmembgroup mg where a.entry_date between $P{adtm_sdate} and $P{adtm_edate} and a.deptitemtype_code = e.deptitemtype_code and a.coop_id = e.coop_id and c.depttype_code = d.depttype_code and c.coop_id = d.coop_id and a.deptaccount_no = d.deptaccount_no and a.coop_id = c.coop_id and d.coop_id = m.coop_id and d.member_no = m.member_no and m.coop_id = mg.coop_id and m.membgroup_code = mg.membgroup_code and a.item_status = 1 and a.deptitemtype_code not in ('ERO', 'ERI') union all select d.deptaccount_no as deptaccount_no , d.deptaccount_name as deptaccount_name, m.membgroup_code as membgroup_code, mg.membgroup_desc as membgroup_desc, 0 as prncbal , c.depttype_desc as depttype_desc , c.depttype_code as depttype_code, 0 as dept_amt, 0 as with_amt, case when e.sign_flag > 0 && e.deptitemtype_code='ERI' then deptitem_amt else 0.00 end as ERI_amt, case when e.sign_flag < 0 && e.deptitemtype_code='ERO' then deptitem_amt else 0.00 end as ERO_amt from dpdeptstatement a, dpdepttype c, dpdeptmaster d , dpucfdeptitemtype e, mbmembmaster m, mbucfmembgroup mg where a.entry_date between $P{adtm_sdate} and $P{adtm_edate} and a.deptitemtype_code = e.deptitemtype_code and a.coop_id = e.coop_id and c.depttype_code = d.depttype_code and c.coop_id = d.coop_id and a.deptaccount_no = d.deptaccount_no and a.coop_id = d.coop_id and d.coop_id = m.coop_id and d.member_no = m.member_no and m.coop_id = mg.coop_id and m.membgroup_code = mg.membgroup_code and ( a.item_status = -1 or a.deptitemtype_code in ('ERO', 'ERI') ) ) temp group by temp.depttype_code, temp.depttype_desc, temp.membgroup_code, temp.membgroup_desc, temp.deptaccount_no, temp.deptaccount_name order by temp.depttype_code, temp.membgroup_code, temp.deptaccount_no]]>