Can't figure out the taxes in MS Dynamics Nav 2009 SP1

netblazer

I've been tasked with writting a few reports / aspx pages and I need to figure out the taxes to apply depending on the cust. #.

I'already asked 2 Nav dev and they couldn't explain it to me (not their area I guess).

99.999% of our clients are in Canada so that means either none, only 1 tax applied or 2 taxes, and the 2nd one can be applied on top of the first one but it's not automatic.

Anybody already has a query written for this or a roadmap I could follow?

TIA.

netblazer

After much deliberation here's a start (works for me, but hopefully it'll help someone else)

SELECT * FROM (
SELECT 
		  C.No_
		--, C.[Tax Area Code]
		--, C.[Tax Liable]
		, TJC.[Print Description]
		, TJC.[Print Order]
		, TJC.[Calculate Tax on Tax]
		, TD.[Effective Date]
		, TD.[Tax Below Maximum]
		, TAL.[Tax Jurisdiction Code]
		, ROW_NUMBER() OVER (PARTITION BY TAL.[Tax Jurisdiction Code] ORDER BY TD.[Effective Date] DESC) AS TieBreaker
FROM	dbo.[Pneus Supérieurs Inc_$Customer] C 
		INNER JOIN dbo.[Pneus Supérieurs Inc_$Tax Area Line] TAL
			ON C.[Tax Area Code] = TAL.[Tax Area]
		INNER JOIN dbo.[Pneus Supérieurs Inc_$Tax Jurisdiction] TJC
			ON TAL.[Tax Jurisdiction Code] = TJC.Code
		INNER JOIN dbo.[Pneus Supérieurs Inc_$Tax Detail] TD
			ON TJC.Code = TD.[Tax Jurisdiction Code]
WHERE	C.No_= 'DUBUC' 
		AND C.[Tax Liable] = 1
		--we had an input error in the system and we had multiple correct values for taxes strictly based on the joins, hopefully you don't need this one
		AND [Tax Below Maximum] > 0 
		AND [Effective Date] <= GETDATE()
) dtTx 
WHERE dtTx.TieBreaker = 1