Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
Hosea 3/13/2013 9:02:21 AM

SQL - Script to show tax detail history

I received a requirement today to write a SQL View for a smartlist that would show tax detail history and the line items. We wanted to use TX30000 for the base table so that we'd be sure that we weren't missing any lines. But TX30000 has one line for each tax detail in the order and we really wanted one line for the order, so in the view you'll see that the first line in the where clause is a sub query that returns one line for the order.

Then we join in the SOP30300 for the item info and the SOP10105 tax table for tax detail info, per the customer's request.

I always put a select statement below the 'create view' statement to be able to easily test the code.
Version: All
Section: SQL Scripts

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables