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

Comments

• Options

  netblazer Member Posts: 26

  2010-08-19

  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
  

  0