'CNV' ) ) as sum, ( select sum( a.debtdet_amt ) from stdebtdet a where a.coop_id = $P{as_coopid} and store_id = $P{as_storeid} and a.debtdet_date >= $P{adtm_startdate} and a.debtdet_date <= $P{adtm_enddate} and a.debtdet_status = '1' and a.debtincdectype_code = '001' and a.debtdettype_code not like 'R%' ) as suminc, ( select sum( a.debtdet_amt ) from stdebtdet a where a.coop_id = $P{as_coopid} and store_id = $P{as_storeid} and a.debtdet_date >= $P{adtm_startdate} and a.debtdet_date <= $P{adtm_enddate} and a.debtdet_status = '1' and a.debtincdectype_code = '002' and a.debtdettype_code not like 'R%' ) as sumdec, ( select sum ( a.debtbalance_amt ) from stdebtdet a where a.debt_no between $P{as_sdebtno} and $P{as_edebtno} and a.debttype_code like $P{as_debttypecode} and a.coop_id = $P{as_coopid} and store_id = $P{as_storeid} and a.seq_no in ( select max( z.seq_no ) from stdebtdet z where z.debtdet_date <= $P{adtm_enddate} and a.coop_id = z.company_id and a.store_id = z.store_id and a.debttype_code =$P{as_debttypecode} and a.debt_no = z.debt_no and z.debtdet_status = '1' and z.refdoc_no <> 'CNV' ) ) as sumend FROM "STDEBTDET", "STDEBTMASTER", "CMCOOPMASTER" WHERE ( "STDEBTDET"."DEBT_NO" = "STDEBTMASTER"."DEBT_NO" ) and ( "STDEBTDET"."COOP_ID" = "STDEBTMASTER"."COOP_ID" ) and ( "STDEBTDET"."DEBTTYPE_CODE" = "STDEBTMASTER"."DEBTTYPE_CODE" ) and ( "STDEBTMASTER"."STORE_ID" = "STDEBTDET"."STORE_ID" ) and ( ( "STDEBTDET"."COOP_ID" = $P{as_coopid}) AND ( "STDEBTDET"."STORE_ID" = $P{as_storeid} ) AND ( "STDEBTDET"."DEBTDET_DATE" between $P{adtm_startdate} and $P{adtm_enddate} ) AND ( trim("STDEBTDET"."DEBTDETTYPE_CODE") = $P{as_debttypecode} ) AND ( "STDEBTDET"."DEBT_NO" between $P{as_sdebtno} and $P{as_edebtno} ) AND ( trim("STDEBTMASTER"."DEBTFROMTYPE_CODE") like $P{as_debtfromtypecode} ) )]]>