(Back to A-AD Administration Module SQL Posted to Wiki Listing - A-AD Administration Module SQL Posted to Wiki )
TYPE: Monthly Special
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in 2007A SP01 PL05. One of the biggest problems with taxation is when there is no default tax code entered in the process of inputting a new address on the Customer Address Tab on the Business Partner Master Data. You can use this SQL as an Alert Message to make sure an order does not get too far down the road to being an Invoice and catch it ahead of time. You will need to determine the frequency of the Alert Message - this Customer had it set to run hourly. The Alert Message lets someone in the Finance Department know that a Customer was created, but the person entering the Customer information forgot to enter an appropriate tax code in the address. If you have a large number of Customer addresses without a tax code right now, you could use this SQL to determine which Customers need to be re-visited for complete information, then set up an Alert Message after all Customers have been corrected to ensure the field is always populated when a new address is entered . Notice that some data fields in the SELECT section do not have an "AS" and the SQL displays what SAP B1 defaults to. You can use "copy and paste" to move this Alert Message into your system.
Here is a screen print of the SQL results:
Copy and Paste this SQL:
--A-AD Alert Mess Tax Code Missing from Address Ver 1 ZP 2010 04 09
--DESCRIPTION: SQL lists those Customer Addresses which have no Tax Code entered on a Ship To Address.
--USAGE: Alert Message Daily Frequency at 10AM
--Version 1 Zal Parchem 2010 04 09
T1.CardCode AS 'Cust Code',
T0.CardName AS 'Customer Name',
FROM OCRD T0
LEFT OUTER JOIN CRD1 T1
ON T0.CardCode = T1.CardCode
T1.TaxCode IS NULL
AND T1.AdresType = 'S'
AND T0.CardType = 'C'