Skip to Content
0
Jul 30, 2008 at 03:07 PM

Change in purchase journal register query -Urgent !....

27 Views

hi all,

Below is query for purchase journal register,

we requires change in query in bold letters as below.

we like to fetch account code,account name

from goods receipt PO journal entry debit side.

SELECT M.DocNum AS 'AP Inv. #', M.DocDate as 'Date', M.CardName as 'Vendor Name',M.NumAtCard as 'Bill No. & Dt.',

(SELECT T2.Account FROM [dbo].[OPCH] T0 INNER JOIN [dbo].[OJDT] T1 ON T0.TransId = T1.TransId INNER JOIN [dbo].[JDT1] T2 ON T1.TransId = T2.TransId

WHERE T0.DocNum =M.DocNum AND

(T2.Debit)=(Select Sum(LineTotal) FROM PCH1 L Where L.DocEntry=M.DocEntry)-M.[DiscSum]) AS 'A/C Code',

(SELECT T3.AcctName FROM [dbo].[OPCH] T0 INNER JOIN [dbo].[OJDT] T1 ON T0.TransId = T1.TransId INNER JOIN [dbo].[JDT1] T2 ON T1.TransId = T2.TransId

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

WHERE T0.DocNum =M.DocNum AND

(T2.Debit)=(Select Sum(LineTotal) FROM PCH1 L Where L.DocEntry=M.DocEntry)-M.[DiscSum]) AS 'A/C Name',(Select Sum(LineTotal) FROM PCH1 L Where L.DocEntry=M.DocEntry) as 'Base Amt.(Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=-90 and DocEntry=M.DocEntry) as 'ED (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=-60 and DocEntry=M.DocEntry) as 'EDCS (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=7 and DocEntry=M.DocEntry) as 'HECS (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=1 and DocEntry=M.DocEntry) as ' VAT (Rs.) ',

(SELECT Sum(TaxSum) FROM PCH4 where statype=4 and DocEntry=M.DocEntry) as ' CST (Rs.) ',

(SELECT Sum(TaxSum) FROM PCH4 where statype=10 and DocEntry=M.DocEntry) as ' CVD (Rs.) ',

(SELECT Sum(TaxSum) FROM PCH4 where statype=5 and DocEntry=M.DocEntry) as ' Ser.Tax (Rs.) ',

(SELECT Sum(TaxSum) FROM PCH4 where statype=6 and DocEntry=M.DocEntry) as 'CS on Ser.Tax (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=8 and DocEntry=M.DocEntry) as 'HECS_ST (Rs.)',

(Select Sum(LineTotal) From PCH3 Q Where Q.DocEntry=M.DocEntry) AS 'Freight (Rs.)',

M.WTSum AS 'TDS (Rs.)',

M.DocTotal as 'Total (Rs.)'

FROM OPCH M LEFT OUTER JOIN PCH1 L on L.DocEntry=M.DocEntry

LEFT OUTER JOIN PCH4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum

LEFT OUTER JOIN PCH5 J ON M.DocEntry = J.AbsEntry

LEFT OUTER JOIN PCH3 Q ON M.DocEntry = Q.DocEntry

WHERE (M.DocDate >= '01-jul-08' AND M.DocDate <= '30-jul-08')

GROUP BY

M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.[DiscSum],M.WTSum,M.DocTotal

ORDER BY

M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.[DiscSum],M.WTSum,M.DocTotal

Any one can give clues/ideas how to modify the query/with

modified query.

Expecting your valuable reply.

Jeyakanthan