Current Position:Home > Cash Book Report

Cash Book Report

Update:10-11Source: network consolidation
Advertisement
Hi Guys,
I am Looking for a Cash Book Report from sap b1.
I have tried using the General Ledger Report  but i need it as Column wise. and i have searched for ' 'XL-CashBook.ixr' but i didnt find this file.
If i have 3 Accounts i need it to display it as in columns for those 3 accounts for the given period.
Pls anybody give me some solution  to get the report.
Regards,
Vamsi

The Best Answer

Advertisement
Hi ,
try below query:
SELECT
DocNum,
STUFF((SELECT ', ' + CAST(DocEntry AS VARCHAR(MAX)) AS text()
FROM RCT2 b
WHERE a.DocNum = b.DocNum
FOR XML PATH('')), 1, 2, '') AS DocEntry
INTO #Invoices
FROM RCT2 a
GROUP BY DocNum
ORDER BY DocNum
SELECT T0.TransId, T0.Account, T0.Debit As Credit, T0.Credit As Debit, T0.ShortName, T0.ContraAct, T5.CardCode As Code, T5.CardName As Name, T0.TaxDate, T0.DueDate, 'Ch.No. ' + Cast(T4.CheckNum As VARchar) + ' Rec. No. ' + Cast(T4.RcptNum AS VARCHAR) + ' ' + IsNull('Invoice No. ' + T3.DocEntry, '') + ' ' + IsNull('Clear Date ' + Convert(VARCHAR, T2.MthDate, 103), '') + IsNull(T0.U_Narration, '') AS U_Narration,
T0.TransType, T0.BaseRef
INTO #Ledger1
FROM JDT1 T0
INNER JOIN OACT T1 ON T0.Account = T1.AcctCode
INNER JOIN JDT1 T2 ON T0.TransId = T2.TransId AND T2.Account = @CashAccount
LEFT JOIN OCHH T4 ON T0.TransId = T4.TransNum AND T0.Ref3Line = T4.CheckNum AND T0.Credit = T4.CheckSum
LEFT JOIN OCRD T5 ON T4.CardCode = T5.CardCode
LEFT JOIN #Invoices T3 ON T4.RcptNum = T3.DocNum
WHERE T0.ContraAct = @CashAccount
AND (T0.ShortName NOT LIKE 'S%' AND T0.ShortName NOT LIKE 'C%')
AND T0.TaxDate BETWEEN @Start AND @Finish
AND T0.TransType = 25
UNION ALL
SELECT T0.TransId, T0.Account, T0.Debit As Credit, T0.Credit As Debit, T0.ShortName, T0.ContraAct, T1.AcctCode As Code, T1.AcctName As Name, T0.TaxDate, T0.DueDate, T0.U_Narration, T0.TransType, T0.BaseRef
FROM JDT1 T0
INNER JOIN OACT T1 ON T0.Account = T1.AcctCode
WHERE T0.ContraAct = @CashAccount
AND (T0.ShortName NOT LIKE 'S%' AND T0.ShortName NOT LIKE 'C%')
AND T0.TaxDate BETWEEN @Start AND @Finish
AND T0.TransType 25
UNION
ALL
SELECT T0.TransId, T0.Account, T0.Debit As Credit, T0.Credit As Debit, T0.ShortName, T0.ContraAct, T1.CardCode As Code, T1.CardName As Name, T0.TaxDate, T0.DueDate, T0.U_Narration, T0.TransType, T0.BaseRef
FROM JDT1 T0
INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
WHERE T0.ContraAct = @CashAccount
AND (T0.ShortName LIKE 'S%' OR T0.ShortName LIKE 'C%')
AND T0.TaxDate BETWEEN @Start AND @Finish
SELECT T0.Account, Sum(T0.Debit) As SumDebit, Sum(T0.Credit) As SumCredit
INTO #OpeningBalances
FROM JDT1 T0
WHERE T0.TaxDate BETWEEN @YearStart AND DateAdd(dd, -1, @Start)
AND T0.Account = @CashAccount
GROUP BY T0.Account
SELECT 0 As TCode, 0 As TransId, T0.Account, T1.AcctName, T1.GroupMask, T0.SumDebit - T0.SumCredit As Debit, 0 As Credit, 'Dr.' As BalType, '' As Code, '' As Name, '' As TaxDate, '' As DueDate, '' As U_Narration, T1.AcctCode As Code1, T1.AcctName As Name1, 0 As TransType, '' As BaseRef
FROM #OpeningBalances T0
INNER JOIN OACT T1 ON T0.Account = T1.AcctCode
WHERE T1.GroupMask = 1
Or T1.GroupMask = 5
UNION
ALL
SELECT 0 As TCode, 0 As TransId, T0.Account, T1.AcctName, T1.GroupMask, 0 As Debit, T0.SumCredit - T0.SumDebit As Credit, 'Cr.' As BalType, '' As Code, '' As Name, '' As TaxDate, '' As DueDate, '' As U_Narration, T1.AcctCode As Code1, T1.AcctName As Name1, 0 As TransType, '' As BaseRef
FROM #OpeningBalances T0
INNER JOIN OACT T1 ON T0.Account = T1.AcctCode
WHERE T1.GroupMask = 2
Or T1.GroupMask = 4
UNION
ALL
SELECT 1 As TCode, T0.TransId, T0.Account, T1.AcctName, T1.GroupMask, T0.Debit, T0.Credit, '' As BalType, T0.Code, T0.Name, T0.TaxDate, T0.DueDate, T0.U_Narration, T1.AcctCode As Code1, T1.AcctName As Name1, T0.TransType, T0.BaseRef
FROM #Ledger1 T0
INNER JOIN OACT T1 ON T0.Account = T1.AcctCode
Thanks,
Neetu
  • Cash Flow report in Report Painter Update:10-11

    Hi, I am trying to make a cash flow report in Report Painter. Can any one help me with steps or config material so that i can prepare and tally the cash flow report being generated in Report Painter. Also there are report line items where by the figu

  • URGENT..How to Run Cash Flow Report. Update:10-11

    How do I run the cashflow report. I opened the cash flow report by going in t.code S_ALR_87012272. I entered the co. code and the g/l accounts, and the fiscal year and executed the report. But it opened a screen with no records found, although the g/

  • Urgent please help: what is the table name for cash management reports? Update:10-11

    I am wondering what the table name for cash management reports is. I am trying to figure out then if I have any missing. Please help.I checked MSEG table. But, I didn't find any field for date. Can you tell me field name. Regards SivaRead other 5 ans

  • How to prepare consolidated petty cash journal report? Update:10-11

    Dear All, How to prepare cash journal consolidated  report for  Check Receipt and cash receipt and cash payment expenses do through cash, we need cash journal report, that report including (Check Receipt, Cash Receipt and Cash outgoing payment). Tell

  • Cash Flow Report Update:10-11

    What are the different ways or steps  to study SAP Standard Cash Flow statement? Actually My client want to check Working Capital Report Can I compare it with Cash Flow or not?Hi, There is no Working capital report in SAP B1. Use standard cash flow r

  • Cash Requirement Report Update:10-11

    Hello, I need to query on some invoices that has the same APPROVAL as the Cash Requirement Report. Where does the approval on the cash requirement come from? Is it a field in a certain table or does it come from a stored procedure? I am going through

  • Cash Advance report Update:10-11

    Dear All, Please provide me with the standard  cash advance report for Travel management. Regards, RaziqAslam-o-Alikum, Hi Raziq, If you want to check cash advance in travel module then you can use following reports. Advance amounts are included with

  • Cash Book Report Update:10-11

    Hi Guys, I am Looking for a Cash Book Report from sap b1. I have tried using the General Ledger Report  but i need it as Column wise. and i have searched for ' 'XL-CashBook.ixr' but i didnt find this file. If i have 3 Accounts i need it to display it

  • Cash receipt report Update:10-11

    Hello, Is there any standard SAP Cash Receipt report? I tried by did not find any. Thank you! JanDear Jan, There is standard cash received report in SAP. But u can get incoming paymens as well as outgoing payment reports from S_ALR_87012309. If busin

  • Cash Forecasting Report Update:10-11

    Hello, Can anyone explain the term cash forecasting report ? Typically, what infocubes feed cash forecasting report ? Please let me know. Thank you. Edited by: Seetha Manikam on Sep 8, 2008 10:12 AMAs the name itself implies ,Cash forecasting report