I hope someone can help out on this.
Let me start off by explaining a issue.
We are currently on SAP B1 SP:1 PL:29
I am working on a query that takes Invoices minus out and Credit Memo and produces a Net Value so a percent can be multiplied to produce a Royalty Amount that is due. I have created a User Defined Table to hold certain values. I have been able to get it to run for the most part but when ever I add the option for a user to input a Date Range I get multiple errors.
Here is the query.
SELECT x.SchoolCode, x.CardCode, x.DocNum, x.Categories, sum(x.Total) as 'Net Total', x.Royalty,
Sum(x.Total)*x.Royalty/100 as 'Royalty Due'
FROM (SELECT Categories = CASE(t5.U_Categories)
when '1' then 'Mens/Unisex T-Shirt'
when '2' then 'Mens/Unisex Fleece'
when '3' then 'Womens/Apparel'
When '4' then 'Mens/Unisex Performance Apparel'
when '5' then 'Mens/Unisex Adjustable Headwear'
when '6' then 'Mens/Unisex Asjustable Wool Blends Headwear'
when '7' then 'Mens/Unisex Structured Closed Back Headwear'
When '8' then 'Jerseys/Uniforms'
when '9' then 'Balls'
else 'No Category'
end,
T2.U_SchoolCode as SchoolCode, T2.CardCode as CardCode, T2.DocNum as DocNum, T6.[U_Royalty] as Royalty,
(SUM(T3.Quantity*T3.PriceAfVAT)-DiscSum) as Total
FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
LEFT OUTER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN INV1 T3
ON T2.DocEntry = T3.DocEntry LEFT OUTER JOIN OSLP T4 ON T0.slpcode = t4.slpcode INNER JOIN OITM T5 ON
T3.ItemCode = T5.ItemCode INNER JOIN [dbo].[@LIC_ROYALTY] T6 ON T2.U_SchoolCode = T6.Code
WHERE T0.CardType = 'C' and t1.adrestype = 'S' and and T6.[U_Agency] = '[%0]' and T2.DocDate >= '[%1]' and T2.DocDate <= '[%2]' and T5.U_Categories >= '1'
group by t5.U_Categories, t2.cardcode, T2.U_SchoolCode, T2.DocNum, t0.cardname, t1.state, t4.slpname
t2.discsum,T6.[U_Royalty]
UNION
SELECT Categories = CASE(t5.U_Categories)
when '1' then 'Mens/Unisex T-Shirt'
when '2' then 'Mens/Unisex Fleece'
when '3' then 'Womens/Apparel'
When '4' then 'Mens/Unisex Performance Apparel'
when '5' then 'Mens/Unisex Adjustable Headwear'
when '6' then 'Mens/Unisex Asjustable Wool Blends Headwear'
when '7' then 'Mens/Unisex Structured Closed Back Headwear'
When '8' then 'Jerseys/Uniforms'
when '9' then 'Balls'
else 'No Category'
end,
T2.U_SchoolCode as SchoolCode, T2.CardCode as CardCode, T2.DocNum as DocNum, T6.[U_Royalty] as Royalty,
-1(SUM(T3.QuantityT3.PriceAfVAT)-DiscSum) as Total
FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
LEFT OUTER JOIN ORIN T2 ON T0.CardCode = T2.CardCode INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry
LEFT OUTER JOIN OSLP T4 ON T0.slpcode = t4.slpcode INNER JOIN OITM T5 ON
T3.ItemCode = T5.ItemCode INNER JOIN [dbo].[@LIC_ROYALTY] T6 ON T2.U_SchoolCode = T6.Code
WHERE T0.CardType = 'C' and t1.adrestype = 'S' and T6.[U_Agency] = '[%0]' and T2.DocDate >= '[%1]' and T2.DocDate <= '[%2]' and T5.U_Categories >= '1'
GROUP BY t5.U_Categories, t2.cardcode, T2.U_SchoolCode, T2.DocNum, T6.[U_Royalty],T0.CardName, t1.state
t4.slpname, t2.discsum) x
group by x.Categories, x.SchoolCode, x.DocNum, x.CardCode, x.Royalty
order by x.SchoolCode
If you have any questions please fell free to ask or i you have a better way of producing the results PLEASE HELP.
Thank you in ADVANCE..
Craig Spehar