HOW TO LINK COST CENTRE WITH GENERAL LEDGER REPORT

[karthick_s8]

Hi All,

here i have Posted my query,I want to link cost centre with this ...

anyone knows to correct this query

Declare @From as DATE

Declare @To as DATE

SET @From=(SELECT MAX(T0.DocDate) From ORCT T0)

SET @To=(SELECT MIN(T0.DocDate) From ORCT T0)

SELECT T7.Location,T5.ProfitCode,T5.Project,T0.TaxDate,T1.[CheckNum][CQ.NO],T6.[VoucherNum] [Vr NO],

T5.[ContraAct] ,T0.U_narration,

T3.Segment_1 [DR.A/c],T2.AcctName [CR. A/c], SUM(ISNULL(T5.[FCDebit],0))[DR AMT],

SUM(ISNULL(T5.[FCCredit],0))[CR AMT],

SUM(ISNULL(T5.[FCDebit],0))-

SUM(ISNULL(T5.[FCCredit],0))[BALANCE]

FROM ORCT T0 LEFT  JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum

LEFT JOIN RCT4 T2 ON T0.DocEntry = T2.DocNum LEFT JOIN OACT T3 ON T0.CashAcct=T3.AcctCode

LEFT OUTER JOIN OJDT T4 ON T0.[TransId] = T4.[TransId]

LEFT JOIN JDT1 T5 ON T5.[TransId] = T4.[TransId]

LEFT JOIN RCT3 T6 ON T0.DocEntry = T6.DocNum

LEFT OUTER JOIN OLCT T7 ON T5.Location=T7.Code

where T3.Segment_1='FC' AND @From >='20130601' and @To<='20150831'

GROUP BY T0.DocNum, T0.TaxDate,T1.[CheckNum],T6.[VoucherNum],

T5.[ContraAct] ,T0.U_narration, T2.AcctName,

T3.Segment_1, T0.CashSum ,T0.CreditSum, T0.TrsfrSum,T5.ProfitCode,T5.Project,T7.Location

Regards

Karthik

[karthick_s8]

Hi All,

I have an another issue...

In this Query I have Taken Cheque and Voucher No from Incoming Payments Table,but Requirment is To Map Outgoing and Incoming Payment...

Can anyone help for this?

[karthick_s8]

Hi Sampath,

Its Linking with only ocrcode2..what about Cost Centres,how to link everything??

[former_member205766]

Hi Karthick

Try with below join

Declare @From as DATE

Declare @To as DATE

SET @From=(SELECT MAX(T0.DocDate) From ORCT T0)

SET @To=(SELECT MIN(T0.DocDate) From ORCT T0)

SELECT T7.Location,T5.ProfitCode,T5.Project,T0.TaxDate,T1.[CheckNum][CQ.NO],T6.[VoucherNum] [Vr NO],

T5.[ContraAct] ,T0.U_narration,

T3.Segment_1 [DR.A/c],T2.AcctName [CR. A/c], SUM(ISNULL(T5.[FCDebit],0))[DR AMT],

SUM(ISNULL(T5.[FCCredit],0))[CR AMT],

SUM(ISNULL(T5.[FCDebit],0))-

SUM(ISNULL(T5.[FCCredit],0))[BALANCE]

FROM ORCT T0 LEFT  JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum

LEFT JOIN RCT4 T2 ON T0.DocEntry = T2.DocNum LEFT JOIN OACT T3 ON T0.CashAcct=T3.AcctCode

LEFT OUTER JOIN OJDT T4 ON T0.[TransId] = T4.[TransId]

LEFT JOIN JDT1 T5 ON T5.[TransId] = T4.[TransId]

LEFT JOIN RCT3 T6 ON T0.DocEntry = T6.DocNum

LEFT OUTER JOIN OLCT T7 ON T5.Location=T7.Code LEFT OUTER JOIN OPRC T8 ON T5.[OcrCode2]= T8.[PrcCode]

where T3.Segment_1='FC' AND @From >='20130601' and @To<='20150831'

GROUP BY T0.DocNum, T0.TaxDate,T1.[CheckNum],T6.[VoucherNum],

T5.[ContraAct] ,T0.U_narration, T2.AcctName,

T3.Segment_1, T0.CashSum ,T0.CreditSum, T0.TrsfrSum,T5.ProfitCode,T5.Project,T7.Location

With Regards

Balaji Sampath

[javier_facessantos]

Hello Karthick

You can try joining JDT1 table with OOCR table on JDT1.ProfitCode = OOCR.OcrCode

Regards