Follow this method and you can create PM Transactions to the taxing authorities to remit sales tax, no re-keying required.
Recently I responded to a post on the GP Community Forum from a user wanting to tie a vendor to the sales tax details so that he could automatically create a payables document similar to what is done when you pay something with a credit card.
I thought that was a really good idea and set out to find a way to do it. You need Integration Manager to accomplish it, but it’s easy to set up.
You must assign a general ledger account to each Tax Detail you use to calculate tax. The GL account must be unique per tax authority payee. For example, if I pay the city of Dallas for one tax detail, and pay the county tax assessor for another, you must have two different accounts. This is needed because you are going to assign a vendor to the GL account assigned to the Tax Detail. Each account will represent a single tax collector.
You do not need a unique account for each Tax Detail, just a unique account for each vendor.
For each liability account to a Tax Detail, record the Vendor ID in one of the User Defined fields on the Account Maintenance window. I used UserDefined1 in my example.
You are going to create a SQL statement that matches the account on the Tax Details card with the Account on the Account Maintenance screen. You are also going to match UserDefined1 with the Vendor Master. This will marry a vendor to each tax detail. Now, include whatever tables are needed to retrieve the sales tax amount in whatever period you are working with and use the Integration Manager (or eConnect, etc) to create a payables transaction for the resulting amount.
I used the SQL statement below to retrieve the sales tax detail amount on historical SOP Invoices. I hard coded the date range to select documents with an invoice date between the first day of the previous month and the last day of the previous month, you wouldn’t want to do that. I just put it in here to show how it is done. Voided documents were excluded:
/* This query returns the sales tax amount on historical SOP Invoices with an invoice
date between the first day of the previous month and the last date of the
previous month. Voided documents are excluded.
It uses the following tables:
SOP30200 Sales Transaction History
SOP10105 Sales Taxes Work and History
GL00100 Account Master
GL00105 Account Index Master
PM00200 Vendor Master
TX00201 Sales/Purchases Tax Master
WHEN 1 THEN 'Quote'
WHEN 2 THEN 'Order'
WHEN 3 THEN 'Fulfillment Order'
WHEN 4 THEN 'Invoice'
WHEN 5 THEN 'Return'
END AS Doc_Type
, SOP10105.SOPNUMBE AS SOP_Number
, SOP30200.DOCDATE AS Invoice_Date
, SOP30200.CUSTNMBR AS Customer_ID
, SOP30200.CUSTNAME AS Customer_Name
, GL00100.USERDEF1 AS User_Defined1
, PM00200.VENDNAME AS Vendor_Name
, GL00105.ACTNUMST AS GL_Account_Number
, SOP10105.TAXDTLID AS Tax_Detail_ID
, SOP10105.STAXAMNT AS Sales_Tax_Amt
, SOP10105.FRTTXAMT AS Tax_on_Freight
, SOP10105.MSCTXAMT AS Tax_on_Misc
, DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) as FirstDayPrevMo
, DATEADD (d,-DATEPART(d,GETDATE()),GETDATE()) as LastDayPrevMo
SOP10105 INNER JOIN
SOP30200 ON SOP10105.SOPTYPE = SOP30200.SOPTYPE
AND SOP10105.SOPNUMBE = SOP30200.SOPNUMBE
INNER JOIN GL00100 ON SOP10105.ACTINDX = GL00100.ACTINDX INNER JOIN
TX00201 ON SOP10105.TAXDTLID = TX00201.TAXDTLID INNER JOIN
PM00200 ON GL00100.USERDEF1 = PM00200.VENDORID INNER JOIN
GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
(SOP30200.SOPTYPE in (3,4)) and VOIDSTTS = 0
Until next post!