G/L Query

[nshah123]

Hi Experts,

I have created the following query in the query generator to build a custom general ledger:

SELECT T1.[TransType],

T1.[TransId],

T1.[Account],

T3.[AcctName],

T1.[RefDate],

T1.[ContraAct],

(case when t2.cardname IS NULL THEN t4.cardname else t2.cardname end) 'Name 1',

(case when t2.cardname IS NULL THEN t6.cardname else t2.cardname end) 'Name 2',

(case when t2.cardname IS NULL THEN t11.cardname else t2.cardname end) 'Name 3',

(case when t2.cardname IS NULL THEN t12.cardname else t2.cardname end) 'Name 4',

(case when t2.cardname IS NULL THEN t8.cardname else t2.cardname end) 'Name 5',

(case when t2.cardname IS NULL THEN t9.cardname else t2.cardname end) 'Name 6',

T2.[CardName],

T4.[CardName],   T6.[CardName],

T7.[Dscription], 

(case when T4.U_PVNUMBER IS NULL THEN T1.Ref2 else T4.U_PVNUMBER end) 'Reference',  

T1.[Debit],

T1.[Credit],

T5.[FreeTxt],

T8.[Comments],

T10.[Dscription],

T11.[Comments],

T11.[CounterRef],

T12.[Comments],

T12.[CounterRef],

T0.[Memo] FROM JDT1 T1  

INNER JOIN OACT T3 ON T1.Account = T3.AcctCode

LEFT OUTER JOIN OCRD T2 ON T2.CARDCODE = T1.ContraAct

LEFT OUTER JOIN OPCH T4 ON T1.TransId = T4.TransId

LEFT OUTER JOIN PCH1 T5 ON T4.DocEntry = T5.DocEntry

LEFT OUTER JOIN OINV T6 ON T1.TransId = T6.TransId

LEFT OUTER JOIN INV1 T7 ON T6.DocEntry = T7.DocEntry

LEFT OUTER JOIN ORIN T8 ON T1.TransId = T8.TransId

LEFT OUTER JOIN ORPC T9 ON T1.TransId = T9.TransId

LEFT OUTER JOIN RPC1 T10 ON T9.DocEntry = T10.DocEntry

LEFT OUTER JOIN ORCT T11 ON T1.TransId = T11.TransId

LEFT OUTER JOIN OVPM T12 ON T1.TransId = T12.TransId

LEFT OUTER JOIN OJDT T0 ON T1.TransId = T0.TransId WHERE T1.[RefDate] >='[%0]' and T1.[RefDate] <='[%1]'

However, I have the following issues:

1. On certain g/l accounts, where there are more than 1 line entries, the report displays the entry twice, but the debit/credit amount for both is the same

2. How do I show transactions before the specified period as "Opening Balance" i.e. if I run it from 01/01/14 to 31/05/14, i need to show opening balance as at 01/01/14 - similarly if i run the report from 01/02/14 to 31/05/2014, it should show the opening balance as at 01/02/14

3. How do I eliminate cancelled transactions from the report?

4. Sales credit notes appear as -Amount in the credit column, how can I show these as debit amounts? - the same for purchases credit notes?

[nshah123]

Just an update - the query line which are providing duplicate records is happening when I use

LEFT OUTER JOIN INV1 T7 ON T6.DocEntry = T7.DocEntry

and the field in the report from INV1 is T7.Dscription

This is happening for the journals where there are multiple line items...

Any help would be appreciated

[Former Member]

Well Why dont you try a sbuquery for that ?

I mean you can add a subquery or function like this:

Select

[Field A],

[Field B],

[Field C],

[SELECT [FIELD X] FROM O.... INNER JOIN I... WHERE ...],

OR

Select

[Field A],

[Field B],

[Field C],

Function_Name (Values),

so you can use Results from query in this two ways and find what you ned with out dulicity ...

let us know.

[nshah123]

Hi Christian

Thanks for that. I am trying to work that out and will let you know how it goes. In the meanwhile I have come up with another query (after creating views on my database) which produces the report as I want it to be. However I have only 2 challenges:

1. All the balances in my report match with the system balances except for my sales, VAT and debtors control accounts
2. My report shows transactions since the start of company activity. How can I  suppress transactions older than the current year and describe them as a Balance B/F?

Below is the query I have used:

SELECT T1.[TransType],

case T1.transtype

when '-2' then 'OBAL'

when '13' then 'INV'

when '14' then 'SCRN'

when '24' then 'RCPT'

when '18' then 'INV'

when '19' then 'CRN'

when '46' then 'PYMT'

else 'JNL'

end 'Type',

T1.[TransId],

T1.[Account],

T3.[AcctName],

T1.[RefDate],

T1.[ContraAct],

(case when t2.cardname IS NULL THEN t4.cardname else t2.cardname end) 'Name 1',

(case when t2.cardname IS NULL THEN t6.cardname else t2.cardname end) 'Name 2',

(case when t2.cardname IS NULL THEN t11.cardname else t2.cardname end) 'Name 3',

(case when t2.cardname IS NULL THEN t12.cardname else t2.cardname end) 'Name 4',

(case when t2.cardname IS NULL THEN t8.cardname else t2.cardname end) 'Name 5',

(case when t2.cardname IS NULL THEN t9.cardname else t2.cardname end) 'Name 6',

T2.[CardName],

T4.[CardName],   T6.[CardName],

T6.[DescofSales], 

(case when T4.U_PVNUMBER IS NULL THEN T1.Ref2 else T4.U_PVNUMBER end) 'Reference',  

T1.[Debit],

T1.[Credit],

T4.[DescOfGoods] 'PINV Desc',

T6.[DescofSales] 'SINV Desc',

T8.[Comments],

T10.[Dscription],

T11.[Comments],

T11.[CounterRef],

T12.[Comments],

T12.[CounterRef],

T13.[Dscription],

T8.[NumAtCard],

T0.[Memo]

FROM JDT1 T1   INNER JOIN OACT T3 ON T1.Account = T3.AcctCode

LEFT JOIN OCRD T2 ON T2.CARDCODE = T1.ContraAct

LEFT JOIN Purch_Desc3 T4 ON T1.TransId = T4.TransId

LEFT JOIN Sales_Desc5 T6 ON T1.TransId = T6.TransId

LEFT JOIN ORIN T8 ON T1.TransId = T8.TransId

LEFT JOIN ORPC T9 ON T1.TransId = T9.TransId

LEFT JOIN RPC1 T10 ON T9.DocEntry = T10.DocEntry

LEFT JOIN ORCT T11 ON T1.TransId = T11.TransId

LEFT JOIN OVPM T12 ON T1.TransId = T12.TransId

LEFT JOIN OJDT T0 ON T1.TransId = T0.TransId

LEFT JOIN RIN1 T13 ON T8.DocEntry = T13.DocEntry

WHERE  T1.[RefDate] <={?To}

order by T1.Refdate

[nshah123]

I have managed to sort out point number 1 by creating a seperate view which eliminates duplication of records which were causing the differences.

I now only need to know how I can group transactions prior to 01/01/current year to reflect as opening balances

Any help would be appreciated.

[kothandaraman_nagarajan]

Hi,

For third questions, please refer this thread:

http://scn.sap.com/message/15022979#15022979

Thanks & Regards,

Nagarajan

[nshah123]

Thank you for that,  but it seems to be eliminating more entries than necessart