cancel
Showing results for 
Search instead for 
Did you mean: 

General Ledger Query

thobenjo1
Explorer
0 Kudos
453

Hey Experts,

Can I get a query of gl month wise and where we can select project from and to and then gl account also. Thank you.

View Entire Topic
0 Kudos

Dear Prakash,

You can use the below query ad you can apply the filter in query manager window.

DECLARE @StartDate DATETIME, @endDate DATETIME, @dummy INTEGER

SELECT TOP 1 @dummy = T0.TransId FROM OJDT T0 WHERE T0.[RefDate] >='[%0]' AND T0.RefDate <='[%1]'

SELECT @StartDate ='[%0]', @endDate ='[%1]'

SELECT Distinct T0.TransId,T0.RefDate,

(CASE WHEN T0.TransType = '-2' THEN 'Opening Balance'

WHEN T0.TransType = '22' THEN 'Purchase Order'

WHEN T0.TransType = '23' THEN 'Sales Quotations'

WHEN T0.TransType = '17' THEN 'Sales Orders'

WHEN T0.TransType = '13' THEN 'AR Invoice'

WHEN T0.TransType = '14' THEN 'AR Credit Memo'

WHEN T0.TransType = '15' THEN 'Delivery'

WHEN T0.TransType = '16' THEN 'Return'

WHEN T0.TransType = '18' THEN 'AP Invoice'

WHEN T0.TransType = '19' THEN 'AP Credit Memo'

WHEN T0.TransType = '20' THEN 'Goods Receipt PO'

WHEN T0.TransType = '202' THEN 'Production Order'

WHEN T0.TransType = '21' THEN 'Goods Return'

WHEN T0.TransType = '24' THEN 'Incoming Payments'

WHEN T0.TransType = '30' THEN 'Journal Entry'

WHEN T0.TransType = '46' THEN 'Outgoing Payments'

WHEN T0.TransType = '58' THEN 'Stock Posting'

WHEN T0.TransType = '59' THEN 'Goods Receipt/Receipt from production'

WHEN T0.TransType = '60' THEN 'Goods Issue/Issue from Production'

WHEN T0.TransType = '67' THEN 'InventoryTransfer'

WHEN T0.TransType = '69' THEN 'Landed Costs'

WHEN T0.TransType = '162' THEN 'Inventory Revaluation'

WHEN T0.TransType = '140000009' THEN 'Outgoing Excise Invoice'

WHEN T0.TransType = '140000010' THEN 'Incoming Excise Invoice' ELSE 'NULLVALUE' END) AS 'Transaction Name',

case when T1.Account in (200001001,200002001,120000001)

then T4.CardCode else T1.Account end as 'GL CODE' ,

case when T1.Account in (200001001,200002001,120000001)

then T4.CardName else T3.AcctName end as 'GL Name' ,

T1.[Project] 'Project code',

(Select PRC.PrcName From OPRC PRC where PRC.PrcCode=T1.[ProfitCode])'Dimesion1',

(Select PRC.PrcName From OPRC PRC where PRC.PrcCode=T1.[OcrCode2])'Dimesion2',

(Select PRC.PrcName From OPRC PRC where PRC.PrcCode=T1.[OcrCode3])'Dimesion3',

(Select PRC.PrcName From OPRC PRC where PRC.PrcCode=T1.[OcrCode4])'Dimesion4',

(Select PRC.PrcName From OPRC PRC where PRC.PrcCode=T1.[OcrCode5])'Dimesion5',

T1.[Debit]'Debit',T1.[Credit]FROM OJDT T0

left JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]

left JOIN OACT T3 ON T1.[Account] = T3.[AcctCode]

left JOIN OCRD T4 ON T1.ShortName = T4.CardCode

Left Join oprj t5 on t1.project=t5.prjcode

left join JDT1 T6 on t1.ContraAct=T6.Account AND T1.TransId=T6.TransId

Left Join oprj T7 on T6.project=T7.prjcode

WHERE 0.[RefDate] >=@StartDate AND T0.[RefDate] <=@endDate

order by T0.[RefDate