on 07-17-2012 4:22 AM
Hi Experts,
I want the query for general Ledger
Same as Reports------> Financial-----> Accounting---------> General Ledger
I want the query to display the above report
Any Help Would Be Greatly Appreciated
Thanks
Shafi
Hi Shafi,
Try This
DECLARE @D1 DATETIME
DECLARE @D2 DATETIME
SET @D1= '2012-04-01 00:00:00.000'
SET @D2= '2012-04-01 00:00:00.000'
select
T0.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,
T0.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' , T1.[ContraAct] 'OffsetAcct',
Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ),
T1.Debit, T1.Credit
from OJDT T0
inner join JDT1 T1 on T0.TransId =T1.TransId
left outer Join OACT T2 ON T2.AcctCode=T1.ContraAct
left outer join OCRD t3 on t3.CardCode=T1.ContraAct
Where T0.RefDate>=@d1 and T0.RefDate<=@d2
order by T0.TransId ,t1.Line_ID
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for ur replies experts
with ur support im able to write this query
Select
T0
.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,
T0
.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' ,T1.shortName 'OffsetAcct'--, T1.[ContraAct] 'OffsetAcct',
,
Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ),
T1
.Debit, T1.Credit
from
OJDT T0
inner
join JDT1 T1 on T0.TransId =T1.TransId
left
outer Join OACT T2 ON T2.AcctCode=T1.ContraAct
left
outer join OCRD t3 on t3.CardCode=T1.ContraAct
Where
T0.RefDate>='04/01/2010' and T0.RefDate<='04/01/2010' and T1.[ContraAct]='1606'
order
by T0.TransId ,t1.Line_ID
im getting
T1.shortName 'OffsetAcct'
By using
T1.shortName 'OffsetAcct' im getting the code how to get the name for this code
I Want The Offset Acctname for this...
and also debit(LC),Credit(LC),Cummulative Balance
Hi.....
Try this then......
Select
T0
.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,
T0
.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' ,T1.shortName 'OffsetAcct'--, T1.[ContraAct] 'OffsetAcct',
,
Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ),
T1
.Debit, T1.Credit
from
OJDT T0
inner
join JDT1 T1 on T0.TransId =T1.TransId
left
outer Join OACT T2 ON T2.AcctCode=T1.ShortName
left
outer join OCRD t3 on t3.CardCode=T1.ContraAct
Where
T0.RefDate>='04/01/2010' and T0.RefDate<='04/01/2010' and T1.[ContraAct]='1606'
order
by T0.TransId ,t1.Line_ID
Regards,
Rahul
Hi Shafi,
DECLARE @D1 DATETIME
DECLARE @D2 DATETIME
SET @D1= '2012-04-01 00:00:00.000'
SET @D2= '2012-04-01 00:00:00.000'
select
T0.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,
T0.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' , T1.[ContraAct] 'OffsetAcct',
Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ),
T1.Debit, T1.Credit
from OJDT T0
inner join JDT1 T1 on T0.TransId =T1.TransId
left outer Join OACT T2 ON T2.AcctCode=T1.ContraAct
left outer join OCRD t3 on t3.CardCode=T1.ContraAct
Where T0.RefDate>=@d1 and T0.RefDate<=@d2 and (T1.[ContraAct]='311200006' or t1.ShortName='311200006')
order by T0.TransId ,t1.Line_ID
Regards
Kennedy
Hi Shafi,
You can try this query:
SELECT
tr4.RefDate[Posting Date], tr4.BaseRef[Doc. No.], tr4.transid[Trans. No.],
tr4.Memo[Remarks], tr1.ContraAct[Offset Acct], 'offset acctname'
= (select distinct acctname from OACT where AcctCode = tr1.contraact),
case when tr1.account <> tr1.shortname then tr1.ShortName else tr1.Account end [Acctcode/BP], case when
tr1.account = tr1.shortname then tr3.AcctName else tr5.CardName end[AcctName/BP Name], (tr1.debit), (tr1.credit),
(SELECT SUM(COALESCE(debit,0) - COALESCE(credit,0)) FROM jdt1 tr2
WHERE tr2.Account = tr1.account and tr2.transid <= tr1.transid and tr2.line_id <= tr1.line_id) AS 'Cumulative balance {LC}'
FROM jdt1 tr1 left join oact tr3 on tr3.acctcode = tr1.account inner join ojdt tr4 on tr4.transid = tr1.transid
left join OCRD tr5 on tr5.cardcode = tr1.shortname
where tr4.RefDate >= '01/01/2012' and tr4.RefDate <= '05/12/2012' and tr1.Account = '130000'
order by tr1.line_id
Just replace the condition value with [%0]', '[%1]' and '[%3]'.
But I suggest you to use store procedure to get your preferred report. The custom report can make the report parameter more flexible and also easy to create.
JM
I want the Project Column to be active on my General Ledger Report in SAP Business One.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Shafi,
Why don't you use general leddger report ?
If you want the same report with the GL report, , you can use G/L report
JM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Shafi,
You can use custom report if you want the same report with G/L.
E.g. crystal report or reporting service.
If you only use query that run in the query generator, I am afraid the same result will be happened i.e. run out of memory.
You can also use the selection criteria parameter to avoid the run out of memory message.
There are many fields in the selection criteria of the SAP B1 general ledger report. You can use expanded function too.
JM
Hi Shafi........
You may create such Report by using OJDT and JDT1 table very easily........
In case you can include transaction related tables with the Inner join of JDT1 also........
Please check this thread........
You find an awesome SQL Query report.......
Regards,
Rahul
Hi Experts,
I Tried This Query
select
T0.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,
T0
.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' from OJDT T0 inner join JDT1 T1 on T0.TransId =T1.TransId
Still I Want Some Fields Offset Acct,Offset AcctName,Debit(LC), Credit(LC), Cummulative Balance(LC)
Any Help Would Be Greatly Appreciated
Thanks
Shafi
Hi Shafi.........
Try this......
select
T0.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,
T0
.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' , T1.[ContraAct] 'OffsetAcct', T2.AcctName, T1.Debit, T1.Credit
from OJDT T0 inner join JDT1 T1 on T0.TransId =T1.TransId Inner Join OACT T2 ON T2.AcctCode=T1.Account
Where T0.RefDate>='[%0]' and T0.RefDate<='[%1]'
For Cumulative Balance you can manage in well manner in Crystal Report through Running Total...
Regards,
Rahul
Ok.......
Try this........
T0.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,
T0
.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' , T1.[ContraAct] 'OffsetAcct', T2.AcctName, T1.Debit, T1.Credit
from OJDT T0 inner join JDT1 T1 on T0.TransId =T1.TransId Inner Join OACT T2 ON T2.AcctCode=T1.ContraAct
Where T0.RefDate>='[%0]' and T0.RefDate<='[%1]'
In this Account name will bring name of Offset Account........
Regards,
Rahul
Hi Shafi,
Me and Rahul have proposed that it would be better to use custom report. However it seems that you need to check the query generator.
You may try this query:
SELECT tr1.account[Acct Code], tr3.AcctName[Acct Name], tr1.RefDate[Posting Date], tr1.BaseRef[Doc. No.], tr1.transid[Trans. No.],
tr1.linememo[Remarks], tr1.ContraAct[Offset Acct], (tr1.debit), (tr1.credit)
FROM jdt1 tr1 inner join oact tr3 on tr3.acctcode = tr1.account order by line_id
Here is the screen shot of result:
Good luck
JM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.