CREATE PROCEDURE mstCmb(@MstType varchar(20),@Asondate varchar(20),@Astodate varchar(20))  AS
declare

@crsYRCD char(1),
@crsTRCD char(4),
@crsTRNO char(8),
@crsSLNO numeric(4),

@crsYRCD1 char(1),
@crsTRCD1 char(4),
@crsTRNO1 char(9),
@crsSLNO1 numeric(4),

@crsPAIDAMT numeric(13,2),
@crsPAIDAMTF numeric(14,3),
@crsPDCAMT numeric(13,2),
@crsPDCAMTF numeric(14,3)


select a.grpcd,a.accode,yrcd,trcd,trno,slno,trdt,duedate,refno,refdt,
     desc1 as descr,b.dramt,b.cramt,fdamt=case when b.dramt>0 then amtf else CONVERT(NUMERIC(13,2),'0') end,
     fcamt=case when b.cramt>0 then amtf else CONVERT(NUMERIC(13,2),'0') end,jbcode='',
     pdcamt=CONVERT(numeric(13,2),'0'),
     pdcamtf=CONVERT(numeric(14,3),'0'),
     paidamt=CONVERT(numeric(13,2),'0'),
     paidamtf=CONVERT(numeric(14,3),'0')
into #due
from accmst a, opblsub b where  a.accode=b.accode and rtrim(crgrpcode)=@MstType
union all
(select a.grpcd,a.accode,yrcd,trcd,trno,slno,trdt,duedate,CHQNO=CASE WHEN ISNULL(CHQNO,'')='' THEN REFNO ELSE CHQNO END,chqdt=CASE WHEN ISNULL(CHQNO,'')='' THEN REFDT ELSE CHQDT END,
     substring(descr,1,30) as descr,b.dramt,b.cramt,b.fdamt,b.fcamt,jbcode=isnull(jbcode,''),
     pdcamt=CONVERT(numeric(13,2),'0'),
     pdcamtf=CONVERT(numeric(14,3),'0'),
     paidamt=CONVERT(numeric(13,2),'0'),
     paidamtf=CONVERT(numeric(14,3),'0')
   from accmst a, mledger b (INDEX = MLE3)  where a.accode=b.accode and trcd<>'OPEN' and  isnull(mautag,'')=''   and trdt<=@Asondate  and rtrim(crgrpcode)=@MstType)
union all
(select a.grpcd,a.accode,yrcd,trcd,trno,slno,trdt,chqdt as duedate,chqno as refno,
   chqdt as refdt,substring(descr,1,30) as descr,
   dramt=case when trcd='PYPD' then (case when refdt is null or  refdt>@asondate then 0 else b.dramt end) else 0 end,
   cramt=case when trcd='RCPD' then (case when refdt is null or  refdt>@asondate then 0 else b.cramt end) else 0 end,
   fdamt=case when trcd='PYPD' then (case when  refdt is null or refdt>@asondate then 0 else b.fdamt end) else 0 end,
   fcamt=case when trcd='RCPD' then (case when  refdt is null or refdt>@asondate then 0 else b.fcamt end) else 0 end,jbcode='',
   pdcamt=case when  refdt is null or refdt>@asondate then b.dramt+b.cramt else CONVERT(numeric(13,2),'0')  end,
   pdcamtf=case when  refdt is null or refdt>@asondate then b.fdamt+b.fCamt else CONVERT(numeric(14,3),'0') end,
   paidamt =CONVERT(numeric(13,2),'0'),
   paidamtf=CONVERT(numeric(14,3),'0')
  from pdc b,accmst a  where  rtrim(crgrpcode)=@MstType and a.accode=b.accode
and (trdt<=@asondate or (refdt is not null and  refdt<=@asondate)) and RETDT is null AND ISNULL(MATCHED,0)=0)  


CREATE INDEX TMPCRS ON #DUE (yrcd, trcd, trno, slno)

/*SELECT * INTO #TE101001 FROM #DUE*/



   
  declare #DUE_cursor  cursor for

     select b.yrcd,b.trcd,b.trno,b.slno,b.yrcd1,b.trcd1,b.trno1,b.slno1,
     adjamt=(case when (b.TRCD='RCPD' OR b.TRCD='PYPD') AND (b.cleardt is null or b.CLEARDT>@astodate) then  CONVERT(numeric(13,2),'0')  else b.adjamt end),
     adjamtf=(case when (b.TRCD='RCPD' OR b.TRCD='PYPD') AND (b.cleardt is null or b.CLEARDT>@astodate) then CONVERT(numeric(14,3),'0')  else b.adjamtf end),

     pdcamt=(case when (b.TRCD='RCPD' OR b.TRCD='PYPD') AND  (b.cleardt is null or b.CLEARDT>@astodate) then b.adjamt else CONVERT(numeric(13,2),'0')  end),
     pdcamtf=(case when (b.TRCD='RCPD' OR b.TRCD='PYPD') AND (b.cleardt is null or b.CLEARDT>@astodate) then  b.adjamtf else CONVERT(numeric(14,3),'0')  end)
     from accmst a, due b where  a.accode=b.accode and b.trcd1<>''  and a.crgrpcode=@MstType
    and isnull(trdt1,@Astodate)<=@Astodate and (trdt<=@Astodate or (charindex(b.trcd,'RCPD;PYPD')>0 and b.cleardt is not null and b.cleardt<=@Astodate)) 

     open #DUE_cursor	 
     FETCH NEXT FROM #DUE_cursor INTO @crsYRCD,@crsTRCD,@crsTRNO,@crsSLNO,@crsYRCD1,@crsTRCD1,@crsTRNO1,@crsSLNO1,@crsPAIDAMT,@crsPAIDAMTF,@crsPDCAMT,@crsPDCAMTF

     WHILE  (@@fetch_status <> -1 and @@fetch_status <> -2 )
        begin
	        UPDATE #DUE set 
	          paidamt=paidamt + @crsPAIDAMT,
	          paidamtf=paidamtF+ @crsPAIDAMTF,
	          pdcamt=pdcamt +((CASE WHEN yrcd='A' and trcd=@crsTRCD and trno=@crsTRNO and slno=@crsSLNO THEN -1 ELSE 1 END )* @crsPDCAMT),
	          pdcamtf=pdcamtf +((CASE WHEN yrcd='A' and trcd=@crsTRCD and trno=@crsTRNO and slno=@crsSLNO THEN -1 ELSE 1 END )* @crsPDCAMTF)
	          WHERE (yrcd='A' and trcd=@crsTRCD and trno=@crsTRNO and slno=@crsSLNO) OR (yrcd='A' and trcd=@crsTRCD1 and trno=@crsTRNO1 and slno=@crsSLNO1)  

        FETCH NEXT FROM #DUE_cursor INTO @crsYRCD,@crsTRCD,@crsTRNO,@crsSLNO,@crsYRCD1,@crsTRCD1,@crsTRNO1,@crsSLNO1,@crsPAIDAMT,@crsPAIDAMTF,@crsPDCAMT,@crsPDCAMTF
     end
     close #DUE_cursor  
     DEALLOCATE #due_cursor

     select substring(A.GRPCD,1,3) as Grp,GrpN=( case substring(A.GRPCD,1,3)  when 'SDD' then 'Receivables' else 'Payables' end ), A.GRPCD,GRPNAME=(SELECT TOP 1 GPSNM FROM ACGROUP WHERE GRPCD=A.GRPCD)
        ,a.accode,a.name,a.curcode,a.tel,a.fax,cperson,trcd,trno,slno,trdt,duedate,refno,refdt,descr,
        invamt=(case when substring(a.grpcd,1,3)='SDD' then b.dramt else b.cramt end),
        invamtf=(case when substring(a.grpcd,1,3)='SDD' then b.fdamt else b.fcamt end), 
        paidamt=(case when substring(a.grpcd,1,3)='SDD' and b.dramt> 0 then b.paidamt
           when substring(a.grpcd,1,3)='SDD' and b.cramt> 0 then b.cramt-b.paidamt

           when substring(a.grpcd,1,3)='SDC' and b.cramt> 0 then b.paidamt
           when substring(a.grpcd,1,3)='SDC' and b.dramt> 0 then b.dramt-b.paidamt else convert(numeric(13,2),'0') end),
        paidamtf=(case when substring(a.grpcd,1,3)='SDD' and b.fdamt> 0 then b.paidamtf
           when substring(a.grpcd,1,3)='SDD' and b.fcamt> 0 then b.fcamt-b.paidamtf
           when substring(a.grpcd,1,3)='SDC' and b.fcamt> 0 then b.paidamtf
           when substring(a.grpcd,1,3)='SDC' and b.fdamt> 0 then b.fdamt-b.paidamtf else convert(numeric(14,3),'0') end),
        pdcamt,pdcamtf,a.crlimit,a.add1,a.tel as telx,a.fax as faxx,a.slman,a.slloc,(select itdesc from bcgmast where ittag = 'S' and itcode = a.slman) as slmanname,jbcode,
        (select itdesc from bcgmast where ittag = 'L' and itcode = a.slloc) as sllocname ,A.crgrpcode into #due1010
   FROM accmst a,#DUE b WHERE a.accode=b.accode and
     (b.DRAMT+b.CRAMT<> b.PAIDAMT or b.pdcamt>0)  


select *  from   #due1010  where (invamtf-paidamtf)<>0 




 drop table #due1010
 DROP TABLE #DUE
GO