cancel
Showing results for 
Search instead for 
Did you mean: 

Query For General Ledger

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

KennedyT21
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

HI Experts

I have attached Both screen shots of Standard Report and also customised Report

In my customised report im getting wrong information from Acctname onwards

Any Suggestions

Thanks

Shafi

KennedyT21
Active Contributor
0 Kudos

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

former_member186095
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Thanks for ur replies Experts . I Got The Solution

Answers (2)

Answers (2)

wale_adegbola
Participant
0 Kudos

I want the Project Column to be active on my General Ledger Report in SAP Business One.

former_member186095
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Thanks For ur reply Jimmy

When im trying to open the same report Its giving me an error as "Run Out Of Memory" .

Thats y I want to write the Query and want to do some tuning to the query

Can Any One Suggest Me The Solution

Thanks

Shafi

former_member186095
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Thanks For Ur Suggestion Jimmy

Can u provide me a query for that So That I Can Run The Crystal Report

Thanks

Shafi

Former Member
0 Kudos

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.......

http://wiki.sdn.sap.com/wiki/display/B1/SAP+B1+SQL+B-FN+General+Ledger+Report+with+Account+Code+and+...

Regards,

Rahul

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Thanks For ur reply Rahul

I Used the Query Suggested by u .But Offset acct, offset actname,debit,credit.. is different when i compare to Standard Report

Im getting different values in offset acct,offset actname..... when i compare to standard report

Thanks

Shafi

Former Member
0 Kudos

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

former_member186095
Active Contributor
0 Kudos

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