Problem in Sales Order Query

[Former Member]

Hi All,

I have problem....This is query which ic combination of Sales Order, Return, Delivery and AR Invoice.....Please tell me why this query is not working ......I want Document No, Quantity, Doc Date from Sales Order, Return, Delivery and AR Invoice and Tax bifergation also.....I per my view this query there should not be any problem but it is not working...please tell me where I am wrong....Just copy and paste this query and check in ur system.......

SELECT b.Doc_Num1 as 'SO. No.',
b.DocDate    as      'SO. Date', 
b.Doc_Num2   AS      'Del.Doc.No.',
b.DocDate    AS      'Delivery Date',
b.Doc_Num3   as      'A/R Invoice No.', 
b.DocDate    as 	'Inv. Date', 
b.Doc_Num4   AS 	'RETURN Doc.No.',
b.DocDate    as 	'Ret.Date',
b.CardName   as 	'Vendor Name',
b.NumAtCard  as 	'Bill No. & Date',
b.ItemCode   as 	'Item Code',
b.Dscription as 	'Dscription',
b.Quantity1  as 	'AR Invoice Quantity',
b.Quantity2  as 	'Sales Order Quantity',
b.Quantity3  as 	'Delivery Quantity',
b.Quantity4  as 	'Return Quantity',
b.LineTotal  as 	'Base Amt.(Rs.)',
b.WTSum	      AS 	'TDS (Rs.)', 
b.DocTotal    as	'Total (Rs.)',
sum(b.BED)    as 	     'Bed Value', 
sum(b.EDCS)   as	     'EDCS Value', 
sum(b.HECS)   as	     'HECS Value', 
sum(b.VAT) 	  as 		'VAT Value', 
sum(b.CST) 	  as		 'CST Value', 
sum(b.CVD) 	  as	 	'CVD Value', 
sum(b.SerTax)  as	 	'SERTAX Value', 
sum(b.CSonSerTax) as	 'CS Value', 
sum(b.HECS_ST)	 as	 'HESC Value'  
from (
select *,( a.Bed + a.EDCS + a.HECS + a.VAT + a.CST +a.CVD + a.SerTax + a.CSonSerTax  + a.HECS_ST ) as Filter from (SELECT distinct 
T0.DocNum,
T0.DocDate,
T2.DocNum,
T2.DocDate,
M.DocNum,
M.DocDate,
M.CardName,
M.NumAtCard,
L.ItemCode,
L.Dscription,
L.Quantity, 
T1.Quantity, 
T3.Quantity, 
S.DocNum, 
S.DocDate, 
X.Quantity, 
L.LineTotal,
M.DocEntry,
M.[DiscSum],
M.WTSum,
M.DocTotal,
M.WTSum, 
M.DocTotal,

[Former Member · Accepted solution]

Hi Sonal,

Try this,

SELECT  T0.DocNum as 'SO. No.',
T0.DocDate as 'SO. Date', 
T2.DocNum AS 'Del.Doc.No.',
T2.DocDate AS 'Delivery Date',
M.DocNum as 'A/R Invoice No.', 
M.DocDate as 'Inv. Date', 
T4.DocNum AS 'Return No.',
T4.DocDate AS 'Return Date',
M.CardName as 'Vendor Name',
M.NumAtCard as 'Bill No. & Date',
ISNULL(L.ItemCode,'Service Item') as 'Item Code',
L.Dscription,
L.Quantity,
(Select Sum(LineTotal) FROM INV1 L Where L.DocEntry=M.DocEntry) as 'Base Amt.(Rs.)',
(SELECT Sum(TaxSum) FROM INV4 where statype=-90 and DocEntry=M.DocEntry) as 'ED (Rs.)',
(SELECT Sum(TaxSum) FROM INV4 where statype=-60 and DocEntry=M.DocEntry) as 'EDCS (Rs.)',
(SELECT Sum(TaxSum) FROM INV4 where statype=7 and DocEntry=M.DocEntry) as 'HECS (Rs.)',
(SELECT Sum(TaxSum) FROM INV4 where statype=1 and DocEntry=M.DocEntry) as ' VAT (Rs.) ',
(SELECT Sum(TaxSum) FROM INV4 where statype=4 and DocEntry=M.DocEntry) as ' CST (Rs.) ',
(SELECT Sum(TaxSum) FROM INV4 where statype=10 and DocEntry=M.DocEntry) as ' CVD (Rs.) ',
(SELECT Sum(TaxSum) FROM INV4 where statype=5 and DocEntry=M.DocEntry) as ' Ser.Tax (Rs.) ',
(SELECT Sum(TaxSum) FROM INV4 where statype=6 and DocEntry=M.DocEntry) as 'CS on Ser.Tax (Rs.)',
(SELECT Sum(TaxSum) FROM INV4 where statype=8 and DocEntry=M.DocEntry) as 'HECS_ST (Rs.)',
(Select Sum(LineTotal) From INV3 Q Where Q.DocEntry=M.DocEntry) AS 'Freight (Rs.)', 
M.WTSum AS 'TDS (Rs.)', 
M.DocTotal as 'Total (Rs.)'
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN ODLN T2 ON T2.DocEntry = T1.TrgetEntry
INNER JOIN DLN1 T3 on T3.DocEntry = T2.Docentry
INNER JOIN OINV M ON M.DocEntry = T3.TrgetEntry
LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry 
LEFT JOIN ORDN T4 ON T4.DocEntry = L.TrgetEntry
LEFT JOIN RDN1 T5 ON T5.DocEntry = T4.DocEntry
LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum 
LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry
LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry
WHERE M.DocDate >= '[%0]' AND M.DocDate <= '[%1]'
GROUP BY 
T0.DocNum,T0.DocDate,T2.DocNum,T2.DocDate, M.DocNum,M.DocDate,T4.DocNum, T4.DocDate, M.CardName,M.NumAtCard,L.ItemCode,L.Dscription,L.Quantity,
M.DocEntry,M.[DiscSum],M.WTSum,M.DocTotal
ORDER BY
T0.DocNum,T0.DocDate,T2.DocNum,T2.DocDate, M.DocNum,M.DocDate,T4.DocNum,T4.DocDate, M.CardName,M.NumAtCard,L.ItemCode,L.Dscription,L.Quantity,
M.DocEntry,M.[DiscSum],M.WTSum,M.DocTotal

Regards,

Madhan.

[Former Member · Accepted solution]

Hi Madhan,

Thanks for the reply...Here is my query which is working fine........but I dont want repetitive records.........Check for records who has atleast two Item Nos....then tell where I am wrong.......

SELECT b.Doc_Num1 as 'SO. No.',
b.DocDate1   as      'SO. Date', 
b.Doc_Num2   AS      'Del.Doc.No.',
b.DocDate2    AS      'Delivery Date',
b.Doc_Num3   as      'A/R Invoice No.', 
b.DocDate3    as 	'Inv. Date', 
b.Doc_Num4   AS 	'RETURN Doc.No.',
b.DocDate4    as 	'Ret.Date',
b.CardName   as 	'Vendor Name',
b.NumAtCard  as 	'Bill No. & Date',
b.ItemCode   as 	'Item Code',
b.Dscription as 	'Dscription',
b.Quantity1  as 	'AR Invoice Quantity',
b.Quantity2  as 	'Sales Order Quantity',
b.Quantity3  as 	'Delivery Quantity',
b.Quantity4  as 	'Return Quantity',
b.LineTotal  as 	'Base Amt.(Rs.)',
b.WTSum	      AS 	'TDS (Rs.)', 
b.DocTotal    as	'Total (Rs.)',
sum(b.BED)    as 	     'Bed Value', 
sum(b.EDCS)   as	     'EDCS Value', 
sum(b.HECS)   as	     'HECS Value', 
sum(b.VAT) 	  as 		'VAT Value', 
sum(b.CST) 	  as		 'CST Value', 
sum(b.CVD) 	  as	 	'CVD Value', 
sum(b.SerTax)  as	 	'SERTAX Value', 
sum(b.CSonSerTax) as	 'CS Value', 
sum(b.HECS_ST)	 as	 'HESC Value'  
from (
select *,( a.Bed + a.EDCS + a.HECS + a.VAT + a.CST +a.CVD + a.SerTax + a.CSonSerTax  + a.HECS_ST ) as Filter from (SELECT distinct 
T0.DocNum as 'Doc_Num1',
T0.DocDate as 'DocDate1',
T2.DocNum as 'Doc_Num2',
T2.DocDate as 'DocDate2',
M.DocNum as 'Doc_Num3',
M.DocDate as 'DocDate3',
M.CardName,
M.NumAtCard,
L.ItemCode,
L.Dscription,
L.Quantity as  'Quantity1', 
T1.Quantity as 'Quantity2', 
T3.Quantity as 'Quantity3', 
S.DocNum 'Doc_Num4', 
S.DocDate as 'DocDate4', 
X.Quantity as 'Quantity4', 
L.LineTotal ,
M.DocEntry,
M.[DiscSum],
M.WTSum,
M.DocTotal,

[Former Member · Accepted solution]

case T.Statype 
when
-90 then sum (T.[TaxSum]) else 0 end) 'BED ',(case T.Statype when
-60 then sum (T.[TaxSum]) else 0 end) 'EDCS ',(case T.Statype when
7 then sum (T.[TaxSum]) else 0 end) 'HECS ',(case T.Statype when
1 then sum (T.[TaxSum]) else 0 end) 'VAT',(case T.Statype when
4  then sum (T.[TaxSum]) else 0 end) 'CST ', (case T.Statype when
10 then sum (T.[TaxSum]) else 0 end) 'CVD ', (case T.Statype when
5  then sum (T.[TaxSum]) else 0 end) 'SerTax', (case T.Statype when
6  then sum (T.[TaxSum]) else 0 end) 'CSonSerTax', (case T.Statype when  
 8 then sum (T.[TaxSum]) else 0 end) 'HECS_ST'


FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN ODLN T2 ON T2.DocEntry = T1.TrgetEntry
INNER JOIN DLN1 T3 on T3.DocEntry = T2.Docentry
INNER JOIN OINV M ON M.DocEntry = T3.TrgetEntry
INNER JOIN ORDN S ON S.CardCode = M.CardCode
LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry
LEFT OUTER JOIN RDN1 X on X.DocEntry=S.DocEntry
LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum 
LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry
LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry
WHERE M.DocNum ='[%0]'

[Former Member · Accepted solution]

group by
T.Statype, T0.DocNum,T0.DocDate,T2.DocNum,T2.DocDate, M.DocNum,M.DocDate,M.CardName,M.NumAtCard,L.ItemCode,L.Dscription,L.Quantity, T1.Quantity, T3.Quantity, S.DocNum, S.DocDate, X.Quantity, L.LineTotal, 
M.DocEntry,M.[DiscSum],M.WTSum,M.DocTotal	)a)b
where b.filter >=  0
group by
b.Doc_Num1,
b.DocDate1, 
b.Doc_Num2,
b.DocDate2,
b.Doc_Num3, 
b.DocDate3, 
b.Doc_Num4,
b.DocDate4,
b.CardName,
b.NumAtCard ,
b.ItemCode,
b.Dscription,
b.Quantity1,
b.Quantity2,
b.Quantity3,
b.Quantity4,
b.LineTotal  ,
b.WTSum, 
b.DocTotal

I put filter over there to delete repetitive records........

[Former Member · Accepted solution]

Hi Sonal,

Try this,

SELECT b.Doc_Num1 as 'SO. No.',
b.DocDate1   as      'SO. Date', 
b.Doc_Num2   AS      'Del.Doc.No.',
b.DocDate2    AS      'Delivery Date',
b.Doc_Num3   as      'A/R Invoice No.', 
b.DocDate3    as 	'Inv. Date', 
b.Doc_Num4   AS 	'RETURN Doc.No.',
b.DocDate4    as 	'Ret.Date',
b.CardName   as 	'Vendor Name',
b.NumAtCard  as 	'Bill No. & Date',
b.ItemCode   as 	'Item Code',
b.Dscription as 	'Dscription',
b.Quantity1  as 	'SO_Qty',
b.Quantity2  as 	'DLN_Qty',
b.Quantity3  as 	'A/R Inv_Qty',
b.Quantity4  as 	'Return_Qty',
b.LineTotal  as 	'Base Amt.(Rs.)',
b.WTSum	      AS 	'TDS (Rs.)', 
b.DocTotal    as	'Total (Rs.)',
sum(b.BED)    as 	     'Bed Value', 
sum(b.EDCS)   as	     'EDCS Value', 
sum(b.HECS)   as	     'HECS Value', 
sum(b.VAT) 	  as 		'VAT Value', 
sum(b.CST) 	  as		 'CST Value', 
sum(b.CVD) 	  as	 	'CVD Value', 
sum(b.SerTax)  as	 	'SERTAX Value', 
sum(b.CSonSerTax) as	 'CS Value', 
sum(b.HECS_ST)	 as	 'HESC Value'  
from (
select *,( a.Bed + a.EDCS + a.HECS + a.VAT + a.CST +a.CVD + a.SerTax + a.CSonSerTax  + a.HECS_ST ) as Filter from (SELECT distinct 
T0.DocNum as 'Doc_Num1',
T0.DocDate as 'DocDate1',
T2.DocNum as 'Doc_Num2',
T2.DocDate as 'DocDate2',
M.DocNum as 'Doc_Num3',
M.DocDate as 'DocDate3',
M.CardName,
M.NumAtCard,
L.ItemCode,
L.Dscription,
T1.Quantity as  'Quantity1', 
T3.Quantity as  'Quantity2', 
L.Quantity as  'Quantity3', 
X.Quantity as  'Quantity4',
S.DocNum 'Doc_Num4', 
S.DocDate as 'DocDate4', 
L.LineTotal ,
M.DocEntry,
M.[DiscSum],
M.WTSum,
M.DocTotal,

[Former Member · Accepted solution]

Last part of Query,

(case T.Statype 
when
-90 then sum (T.[TaxSum]) / 4 else 0 end) 'BED ',(case T.Statype when
-60 then sum (T.[TaxSum]) / 4 else 0 end) 'EDCS ',(case T.Statype when
7 then sum (T.[TaxSum]) / 4 else 0 end) 'HECS ',(case T.Statype when
1 then sum (T.[TaxSum])  / 4 else 0 end) 'VAT',(case T.Statype when
4  then sum (T.[TaxSum]) / 4  else 0 end) 'CST ', (case T.Statype when
10 then sum (T.[TaxSum]) / 4  else 0 end) 'CVD ', (case T.Statype when
5  then sum (T.[TaxSum]) / 4  else 0 end) 'SerTax', (case T.Statype when
6  then sum (T.[TaxSum]) / 4  else 0 end) 'CSonSerTax', (case T.Statype when  
 8 then sum (T.[TaxSum]) / 4 else 0 end) 'HECS_ST'
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN ODLN T2 ON T2.DocEntry = T1.TrgetEntry
INNER JOIN DLN1 T3 on T3.DocEntry = T2.Docentry
INNER JOIN OINV M ON M.DocEntry = T3.TrgetEntry
LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry
LEFT JOIN ORDN S ON S.DocEntry = L.TrgetEntry
LEFT JOIN RDN1 X ON X.DocEntry = S.DocEntry
LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum 
LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry
LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry
WHERE M.DocNum ='[%0]' 
group by
T.Statype, T0.DocNum,T0.DocDate,T2.DocNum,T2.DocDate, M.DocNum,M.DocDate,M.CardName,M.NumAtCard,L.ItemCode,L.Dscription,T1.Quantity,T3.Quantity,L.Quantity, X.Quantity, S.DocNum, S.DocDate, L.LineTotal, 
M.DocEntry,M.[DiscSum],M.WTSum,M.DocTotal	)a)b
where b.filter >=  0
group by
b.Doc_Num1,
b.DocDate1, 
b.Doc_Num2,
b.DocDate2,
b.Doc_Num3, 
b.DocDate3, 
b.Doc_Num4,
b.DocDate4,
b.CardName,
b.NumAtCard ,
b.ItemCode,
b.Dscription,
b.Quantity1,
b.Quantity2,
b.Quantity3,
b.Quantity4,
b.LineTotal,
b.WTSum, 
b.DocTotal

Close this Thread if issue solved

Regards,

Madhan.

[Former Member]

And another half part of the query

(case T.Statype 
when
-90 then sum (T.[TaxSum]) else 0 end) 'BED ',(case T.Statype when
-60 then sum (T.[TaxSum]) else 0 end) 'EDCS ',(case T.Statype when
7 then sum (T.[TaxSum]) else 0 end) 'HECS ',(case T.Statype when
1 then sum (T.[TaxSum]) else 0 end) 'VAT',(case T.Statype when
4  then sum (T.[TaxSum]) else 0 end) 'CST ', (case T.Statype when
10 then sum (T.[TaxSum]) else 0 end) 'CVD ', (case T.Statype when
5  then sum (T.[TaxSum]) else 0 end) 'SerTax', (case T.Statype when
6  then sum (T.[TaxSum]) else 0 end) 'CSonSerTax', (case T.Statype when  
 8 then sum (T.[TaxSum]) else 0 end) 'HECS_ST'


FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN ODLN T2 ON T2.DocEntry = T1.TrgetEntry
INNER JOIN DLN1 T3 on T3.DocEntry = T2.Docentry
INNER JOIN OINV M ON M.DocEntry = T3.TrgetEntry
INNER JOIN ORDN S ON S.CardCode = M.CardCode
LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry
LEFT OUTER JOIN RDN1 X on X.DocEntry=S.DocEntry
LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum 
LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry
LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry
WHERE M.DocNum ='[%0]'

[Former Member]

and last part of query

group by
T.Statype, T0.DocNum,T0.DocDate,T2.DocNum,T2.DocDate, M.DocNum,M.DocDate,M.CardName,M.NumAtCard,L.ItemCode,L.Dscription,L.Quantity, T1.Quantity, T3.Quantity, S.DocNum, S.DocDate, X.Quantity, L.LineTotal, 
M.DocEntry,M.[DiscSum],M.WTSum,M.DocTotal	)a)b
where b.filter >=  0
group by
b.Doc_Num1,
b.DocDate, 
b.Doc_Num2,
b.DocDate,
b.Doc_Num3, 
b.DocDate, 
b.Doc_Num4,
b.DocDate,
b.CardName,
b.NumAtCard ,
b.ItemCode,
b.Dscription,
b.Quantity1,
b.Quantity2,
b.Quantity3,
b.Quantity4,
b.LineTotal  ,
b.WTSum, 
b.DocTotal

Pls Send me solution

Thanks