Purchase Register

[Former Member]

Dear Experts

i am creating query on purchase register but when i am running the query i see document no. is appering more than one in few document.I am looking for base document amt .tax amt and document total only.I dont want by row wise report.

can somebody guide me

Thanks

Parikshit

SELECT T0.[DocDate], T0.[TaxDate], T0.[DocNum], T0.[DocType], T2.[GroupName], T0.[CardCode], T0.[CardName],

T0.[LicTradNum],T0.[NumAtCard], T1.[AddID], T0.[Comments], T3.[VatPrcnt], T0.[VatSum], T0.[DocTotal]

FROM [dbo].[OPCH] T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

INNER JOIN PCH1 T3 ON T0.DocEntry = T3.DocEntry WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <= [%1]

[Former Member · Accepted solution]

Hi,

Because you have T3.VatPrcnt in the query, it will create multiple lines if you have more than one line in the line detail. You may restrict only one type of T3.VatPrcnt such as T3.VatPrcnt = .14, that can ensure only one line for one document number.

Thanks,

Gordon

[Former Member · Accepted solution]

Thanks Gordon for your reply.

i have Replace T3.[VatPrcnt] the below error is appering.Somthing wrong i am doing.can you guide me

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '='. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Received Alerts' (OAIB) (s) could not be prepared.

Parikshit

[Former Member · Accepted solution]

You do not need replace it. Only need add T3.VatPrcnt = .14 after Where.

[Former Member · Accepted solution]

Thanks Gordon

I did as per your instruction ,now is coming "data not found"

Parikshit

[Former Member · Accepted solution]

I just show an example. You need to enter what your tax rates are.

[Former Member · Accepted solution]

Thanks Gordon

Even i put my local tax rate still in few document no. is appering more than one. and tax amount and document total

Regards

Parikshit

[Former Member]

Hi Parikshit....

Please try this Report and make some necessary changes as you want like CardGroup Name.....

SELECT T0.[DocNum], T0.[DocDate], T0.NumAtCard As 'Bill No.', 
T0.[CardName], T1.[Dscription], T1.[Quantity], T1.Price, 
T1.[LineTotal], T0.[DocTotal], T0.[DiscPrcnt], T1.[DiscPrcnt], T0.[Header], T0.[Footer],
(isnull((SELECT SUM((case when upper(t4.STACode) like 'BED%' then T4.TaxSum else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) ExciseDuty,
(isnull((SELECT SUM((case when upper(t4.STACode) like 'BED%' then T4.TaxRate else 0 end))
	FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum and T4.RelateType=1),0)) As 'BED Rate %',


(isnull((SELECT SUM((case when upper(t4.STACode) like 'eCess%' then T4.TaxSum else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) ECess,
(isnull((SELECT SUM((case when upper(t4.STACode) like 'eCess%' then T4.TaxRate else 0 end))
	FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum and T4.RelateType=1),0)) As 'EcessRate %',

(isnull((SELECT SUM((case when upper(t4.STACode) like 'HSC%' then T4.TaxSum else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) HEdCess, 
(isnull((SELECT SUM((case when upper(t4.STACode) like 'HSC%' then T4.TaxRate else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum and T4.RelateType=1),0)) HEdCessRate,

(isnull((SELECT SUM((case when upper(t4.STACode) like 'Service%' then T4.TaxSum else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) As 'Service', 
(isnull((SELECT SUM((case when upper(t4.STACode) like 'Service%' then T4.TaxRate else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum and T4.RelateType=1),0)) ServiceRate,

(isnull((SELECT SUM((case when upper(t4.STACode) like 'Cess_ST%' then T4.TaxSum else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) ECess_ST,
(isnull((SELECT SUM((case when upper(t4.STACode) like 'Cess_ST%' then T4.TaxRate else 0 end))
	FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum and T4.RelateType=1),0)) As 'Ecess_STRate %',

(isnull((SELECT SUM((case when upper(t4.STACode) like 'HSC_ST%' then T4.TaxSum else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) HEdCess, 
(isnull((SELECT SUM((case when upper(t4.STACode) like 'HSC_ST%' then T4.TaxRate else 0 end))
	 FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum and T4.RelateType=1),0)) HEdCessRate,


(isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' then T4.TaxSum else 0 end))
	FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) As 'VAT',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'VAT%' then T4.TaxRate else 0 end))
	FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) As 'VAT %',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'CST%' then T4.TaxSum else 0 end))
	FROM PDN4 T4 WHERE T4.DocEntry=T0.DocEntry and T1.LineNum = T4.LineNum),0)) As 'CST',
(isnull((SELECT SUM((case when upper(t4.STACode) like 'CST%' then T4.TaxRate else 0 end))
	FROM PDN4 T4 WHERE T4.DocEntry=T1.DocEntry and T1.LineNum = T4.LineNum and T4.RelateType=1),0)) As 'CST %',
	
	(isnull((SELECT SUM((case when upper(t5.ExpnsCode) =1 then T5.LineTotal else 0 end))
	 FROM PDN2 T5 WHERE T5.DocEntry=T0.DocEntry and T1.LineNum = T5.LineNum),0)) Packing,
	 (isnull((SELECT SUM((case when upper(t5.ExpnsCode) =2 then T5.LineTotal else 0 end))
	 FROM PDN2 T5 WHERE T5.DocEntry=T0.DocEntry and T1.LineNum = T5.LineNum),0)) Forwarding

FROM OPDN T0  
INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry  
WHERE (T0.[DocDate]>='[%0]' and T0.[DocDate]<='[%1]')

Regards,

Rahul

[Former Member]

thank you very i will do some modification in your query.