Skip to Content
0
Former Member
Apr 20, 2009 at 12:59 PM

Query Help

19 Views

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