(Back to F-BP Business Partner Module SQL Posted to Wiki Listing - F-BP Business Partner Module SQL Posted to Wiki )
TYPE: Monthly Special
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in 2007A SP01 PL05. A "workstation" is where SQL is created which allows an End User the ability to do a large amount of work which is pretty repetitive. Often times the work is updating and/or reviewing a lot of records to see if the records have the appropriate data or are missing some kind of code, setting, etc. This SQL is a good example of "workstations" I have built in the past. Basically the Customer was about to go through a full tax audit by the states of Arizona, California, Georgia, and Illinois. They needed some special SQL to make sure they had the Federal Tax ID and Tax Exemption Certificates. Although this SQL only concentrates on these four states, you can change the SQL to include all states or even other specific states. Since this Customer had created tax codes with "EXMPT" at the end of each tax code as a naming standard, it was fairly easy to pull out only those Business Partners who are considered exempt from taxation. The Customer uses this SQL as part of their month-end process to make sure the Business Partners and tax requirements are being adhered to. There is a handy SQL command in the WHERE Clause called "LIKE" which displays only those Customers with "EXMPT" at the end of the tax code. This is only one way to use LIKE; there are other ways and you can find that information in the SAP B1 Query Generator "Conditions" or in other reference material. If you find this SQL helpful, then use “copy and paste” to place and save it using the Query Generator of your SAP B1 installation; change whatever pieces of the SQL you need, and hopefully it saves your company some processing time.
Here is a screen print of the SQL results:
Copy and Paste this SQL:
--F-BP Tax Code Workstation Ver 2 ZP 2010 02 06
--DESCRIPTION: Lists Business Partner Addresses with Associated Tax Codes and exemption information for AZ CA GA IL audits.
--Version 1 Zal Parchem 2009 12 24
--Version 2 Zal Parchem 2010 02 26 Added Additional Column forCounty
T1.Address AS 'Ship To Address Name',
WHEN T0.CardType = 'S' THEN 'Vendor'
WHEN T0.CardType = 'C' THEN 'Customer'
WHEN T0.CardType = 'L' THEN 'Lead'
END AS 'BP Class',
T0.LicTradNum AS 'Fed Tax ID',
T0.ExemptNo AS 'BP Tax Exmpt'
FROM OCRD T0
LEFT OUTER JOIN CRD1 T1
ON T0.CardCode = T1.CardCode
T1.TaxCode LIKE '%%EXMPT'
(T1.State = 'AZ'
OR T1.State = 'CA'
OR T1.State = 'GA'
OR T1.State = 'IL')
AND T1.AdresType = 'S'