Skip to Content
0
Former Member
Mar 05, 2009 at 04:08 AM

out standing MIS Report query problem.

120 Views

Hi,

We designed MIS Report for outstanding mis.here we are facing probleme.ex:AR invoice raised on 01 Jan 09 bill amount 1000 and for this client is received full amount against that bill on 04 march09.in this report we provided parameters for from date and to date. When user selected in parameter from date 010109 to 050309 it will show pending amount is 0.but users are asking if they select from date upto 030309 means it will show pending amount should be show 1000.in this query we are retrieving based on document status when open. How we can show report for this requirements. Please guide me.if we give docstatus='O' or docstatus= 'C' it's showing but pending amount =Bill amount - paid amount here it's showing 0 i think here also we need to pass incoming payment docdate.how wen do please guide me.

below this is the query.

set @FromYear=case when month(@ToDate)>=1 and month(@ToDate)<=3 then Year(@ToDate)-1 else Year(@ToDate) end

set @ToYear=case when month(@ToDate)>=1 and month(@ToDate)<=3 then Year(@ToDate) else Year(@ToDate)+1 end

--select @fromYear,@toyear,@todate,@ProjCode,@Cardcode,@VoucherType,@GroupName

select a.u_category,a.docdate,g.name,d.CardCode,d.cardname,e.GroupName,SUBSTRING(CONVERT(VARCHAR(11), a.docdate, 113), 4, 8)as [Month of invoiceraised],

b.seriesname,a.docnum,a.Project,0,a.doctotal,

paidamount= case when a.paidtodate is NULL then 0 else a.paidtodate end,

pendingamount= case when isnull(a.doctotal,0)-isnull(a.paidtodate,0) is null then 0 else isnull(a.doctotal,0)-isnull(a.paidtodate,0) end,

case when year(a.docdate) = @ToYear and month(a.docdate) = 3 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as March ,

case when year(a.docdate) = @ToYear and month(a.docdate) = 2 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as Feb,

case when year(a.docdate) = @ToYear and month(a.docdate) = 1 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as Jan,

case when year(a.docdate) = @FromYear and month(a.docdate) = 12 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as Dec,

case when year(a.docdate) = @FromYear and month(a.docdate) = 11 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as Nov,

case when year(a.docdate) = @FromYear and month(a.docdate) = 10 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as Oct,

case when year(a.docdate) = @FromYear and month(a.docdate) = 9 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as Sept,

case when year(a.docdate) = @FromYear and month(a.docdate) = 8 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as Aug,

case when year(a.docdate) = @FromYear and month(a.docdate) = 7 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as July,

case when year(a.docdate) = @FromYear and month(a.docdate) = 6 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as June,

case when year(a.docdate) = @FromYear and month(a.docdate) = 5 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as May,

case when year(a.docdate) = @FromYear and month(a.docdate) = 4 then isnull(a.doctotal,0)-isnull(a.paidtodate,0) else 0 end as April,

--case when a.docdate = (select f_refdate from ofpr where year(f_refdate)=year(a.refdate) then a.doctotal-c.paidtodate else 0 end as [Pre-Yr]

nodocsum=isnull(f.nodocsum,0),0 as PreviousFY

from oinv a

left join nnm1 b on a.series=b.series

inner join INV1 c ON a.DocEntry = c.DocEntry

inner join OCRD d ON d.CardCode=a.CardCode

left join ocpr g on g.cardcode=d.cardcode

Inner join OCRG e on e.GroupCode=d.GroupCode and e.grouptype='c'

LEFT JOIN ORCT f ON a.DocEntry = f.DocEntry

where

a.docstatus='O' and

case when a.doctotal-a.paidtodate is null then 0 else a.doctotal-a.paidtodate end >= 0

Reds,

Sampath kumar devunuri.