Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
KennedyT21
Active Contributor
0 Kudos
1,105

Query Report for Cash Report

/*****************************

*******************************
* Code formatted by Kennedy
* Time: 20/08/2013 6:52:09 PM
************************************************************/

/* SELECT t1.taxdate, t2.Acctname FROM jdt1 t1
  INNER JOIN oact t2 ON t1.Account=t2.AcctCode AND t2.Finanse='y' */

DECLARE @D1  AS DATETIME
DECLARE @d2  AS DATETIME
DECLARE @D3  AS NVARCHAR(100)
 
SET @D1 = /* t1.taxdate */ '[%0]'
SET @d2 =/* t1.taxdate */ '[%1]'
SET @D3 = /* t2.Acctname */ '[%2]'

DECLARE @PrCl   dec(19 ,6)
DECLARE @debit     dec(19 ,6)
DECLARE @credit     dec(19 ,6)
DECLARE @account    CHAR(20)
DECLARE @Pracc  CHAR(20)


CREATE TABLE #TempTab
(
    Account   CHAR(200)
   ,NAME  CHAR(100)
   ,Date    DATETIME
   ,Opening    dec(19 ,6)
   ,Debit    dec(19 ,6)
   ,Credit    dec(19 ,6)
   ,Closing    dec(19 ,6)
)
INSERT INTO #TempTab
  (
    Account
   ,NAME
   ,Date
   ,Opening
   ,Debit
   ,Credit
   ,Closing
  )
SELECT T1.[Account]
      ,t2.AcctName
      ,T1.[RefDate] Mo
      ,0
      ,SUM(T1.[Debit])De
      ,SUM(T1.[Credit])Cr
      ,0
FROM   OJDT T0
       INNER JOIN JDT1 T1
            ON  T0.TransId = T1.TransId
       INNER JOIN oact t2
            ON  t1.Account = t2.AcctCode
                AND t2.Finanse = 'y'
GROUP BY
       T1.[Account]
      ,T1.[RefDate]
      ,t2.AcctName
ORDER BY
       T1.[Account]
      ,T1.[RefDate]

DECLARE cu     CURSOR 
FOR
    SELECT Account
          ,Debit
          ,Credit 
    FROM  #TempTab
           FOR

UPDATE
SET    @PrAcc = ''

OPEN cu
FETCH NEXT FROM cu INTO @Account,@Debit,@Credit
WHILE @@FETCH_STATUS=0
BEGIN
    IF @account!=@PrAcc
        SET @PrCl = 0
   
    UPDATE #TempTab
    SET    opening = @PrCl
          ,closing = @PrCl-@Credit+@Debit
    WHERE  CURRENT OF cu
   
    SET @PrCl = @PrCl-@Credit+@Debit
    SET @PrAcc = @Account
    FETCH NEXT FROM cu INTO @Account,@debit,@credit
END
DEALLOCATE cu
SELECT c.Account AS Accountcode
      ,c.NAME AS AcctName
      ,c.Date AS [Date]
      ,c.Opening AS Opening
      ,c.Debit AS Debit
      ,c.Credit AS Credit
      ,c.Closing AS Closing
FROM   #TempTab c
WHERE  (date BETWEEN @D1 AND @d2)
       AND NAME LIKE @D3

DROP TABLE #TempTab

Hope Helpful

Labels in this area