We use WideOrbit for traffic management at work (advertising traffic, not cars). And we use Experian for credit checking. (Here's an example of why) We wanted to send our payment info from our clients to update Experian on a monthly basis. Experian has a pretty flexible file format but since WO uses SQL Server on the back end, I have a lot of flexibility there and decided just to use Experian's standard file format. I had to go to SQL because there was no way to add address and phone data (an Experian requirement) to the aging report.
Essentially I use a view to summarize the aging information from individual advertisers and split the info out into Current, 30, 60 and 90+ day buckets. Then I have a SQL script that can be run by an Admin from within WideOrbit. The SQL Script pulls everything together in Experian's format. WideOrbit can then be used to create an export file to send to Experian via email.
This process was quickly cobbled together so there are a couple of caveats:
1) Aging buckets are true 30 days, not broadcast or calendar period. We use a broadcast calendar so the export buckets don't completely match AR buckets but our terms are 30 days not 1 broadcast month ,so the data is accurate. The WideOrbit AR is just a bit more generous since it uses broadcast month (Don't change this guys, we like it the way it is!)
2) The export file is pulling from the invoices table to get the aging data. Payments not applied to invoices aren't included in the file. For us this is not an issue since anything not applied to an invoice is a pre-payment and pre-payments aren't and indicator of credit, they're an indicator of lack of credit. ;) We deal with this limitation with procedures and review of aging because I don't have time to rewrite the SQL code right now.
3) The export only pulls the status as of NOW. There is no going back to last week or yesterday.
4) WideOrbit doesn't expose an Advertiser/Agency id. It's in SQL as a GUID but that id is waaaay too long for Experian and Experian requires an id. Since we're not creating advertisers every second, we decided to use the date and time that the advertiser/agency was created as our id. The numbers are all run together but it's unique enough for us, for now. Your mileage may vary.
5) I'm not a DBA, I'm a CPA. Use this at your own risk. There is nothing proprietary about the code, the concept or the file format, so use it however you would like.
So here's the code.
A) Paste this into SQL Query analyzer and run it to create the view (you can't do this via the SQL window in WideOrbit, it only supports the Select statement).
CREATE view TTN_AR_Experian as SELECT LEFT(WO_AGENCIES.AGENCY_NAME, 40) AS Customer_Name, LEFT(WO_ADDRESSES.ADDRESS_LINE1, 30) AS Customer_Address, LEFT(WO_ADDRESSES.ADDRESS_CITY, 28) AS Customer_City, LEFT(WO_ADDRESSES.ADDRESS_STATE, 2) AS Customer_State, LEFT(WO_ADDRESSES.ADDRESS_ZIPCODE, 5) AS Customer_Zip, max(rtrim(cast(right(year(wo_agencies.create_date),2) as char)) +rtrim(cast(right(month(wo_agencies.create_date),2) as char)) +rtrim(cast(right(day(wo_agencies.create_date),2) as char))+ +rtrim(cast(right(datepart(hour,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(minute,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(second,wo_agencies.create_date),2) as char))) AS AR_Number, LEFT(WO_AGENCIES.MAIN_PHONE_NUMBER, 13) AS Customer_Phone, MIN(year(invoice_date)) AS Date_Acct_Opened, RIGHT('0' + cast(MAX(month(invoice_date)) AS varchar), 2) + cast(RIGHT(MAX(year(invoice_date)), 2) AS varchar) AS Date_of_Last_Acct_Activity, 'Net 30' AS Terms, SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS Total_Account_Balance, SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS Amount_Current, 0 AS [Amount_1-30], 0 AS [Amount_31-60], 0 AS [Amount_61-90], 0 AS [Amount_91+], ' ' AS Comment_Code, WO_STATIONS.STATION_CALL_LETTERS AS StationFROM WO_AGENCY_BILLING_ADDRESSES INNER JOIN WO_AGENCIES ON WO_AGENCY_BILLING_ADDRESSES.AGENCY_ID = WO_AGENCIES.AGENCY_ID INNER JOIN WO_ADDRESSES ON WO_AGENCY_BILLING_ADDRESSES.BILLING_ADDRESS_ID = WO_ADDRESSES.ADDRESS_ID INNER JOIN WO_INVOICES INNER JOIN WO_ORDER_BILL_PLANS ON WO_INVOICES.ORDER_BILL_PLAN_ID = WO_ORDER_BILL_PLANS.ORDER_BILL_PLAN_ID INNER JOIN WO_ORDERS ON WO_ORDER_BILL_PLANS.ORDER_ID = WO_ORDERS.ORDER_ID ON WO_AGENCIES.AGENCY_ID = WO_ORDERS.AGENCY_ID INNER JOIN WO_STATIONS ON WO_ORDERS.STATION_ID = WO_STATIONS.STATION_IDWHERE invoice_date >= getdate() - 30GROUP BY Agency_Name, Address_Line1, Address_City, Address_State, Address_ZipCode, Main_Phone_Number, WO_STATIONS.STATION_CALL_LETTERSUNION ALLSELECT LEFT(WO_AGENCIES.AGENCY_NAME, 40) AS Customer_Name, LEFT(WO_ADDRESSES.ADDRESS_LINE1, 30) AS Customer_Address, LEFT(WO_ADDRESSES.ADDRESS_CITY, 28) AS Customer_City, LEFT(WO_ADDRESSES.ADDRESS_STATE, 2) AS Customer_State, LEFT(WO_ADDRESSES.ADDRESS_ZIPCODE, 5) AS Customer_Zip, max(rtrim(cast(right(year(wo_agencies.create_date),2) as char)) +rtrim(cast(right(month(wo_agencies.create_date),2) as char)) +rtrim(cast(right(day(wo_agencies.create_date),2) as char))+ +rtrim(cast(right(datepart(hour,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(minute,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(second,wo_agencies.create_date),2) as char))) AS AR_Number, LEFT(WO_AGENCIES.MAIN_PHONE_NUMBER, 13) AS Customer_Phone, MIN(year(invoice_date)) AS Date_Acct_Opened, RIGHT('0' + cast(MAX(month(invoice_date)) AS varchar), 2) + cast(RIGHT(MAX(year(invoice_date)), 2) AS varchar) AS Date_of_Last_Acct_Activity, 'Net 30' AS Terms, SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS Total_Account_Balance, 0 AS Amount_Current, SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS [Amount_1-30], 0 AS [Amount_31-60], 0 AS [Amount_61-90], 0 AS [Amount_91+], ' ' AS Comment_Code, WO_STATIONS.STATION_CALL_LETTERS AS StationFROM WO_AGENCY_BILLING_ADDRESSES INNER JOIN WO_AGENCIES ON WO_AGENCY_BILLING_ADDRESSES.AGENCY_ID = WO_AGENCIES.AGENCY_ID INNER JOIN WO_ADDRESSES ON WO_AGENCY_BILLING_ADDRESSES.BILLING_ADDRESS_ID = WO_ADDRESSES.ADDRESS_ID INNER JOIN WO_INVOICES INNER JOIN WO_ORDER_BILL_PLANS ON WO_INVOICES.ORDER_BILL_PLAN_ID = WO_ORDER_BILL_PLANS.ORDER_BILL_PLAN_ID INNER JOIN WO_ORDERS ON WO_ORDER_BILL_PLANS.ORDER_ID = WO_ORDERS.ORDER_ID ON WO_AGENCIES.AGENCY_ID = WO_ORDERS.AGENCY_ID INNER JOIN WO_STATIONS ON WO_ORDERS.STATION_ID = WO_STATIONS.STATION_IDWHERE invoice_date <>= getdate() - 60GROUP BY Agency_Name, Address_Line1, Address_City, Address_State, Address_ZipCode, Main_Phone_Number, WO_STATIONS.STATION_CALL_LETTERSUNION ALLSELECT LEFT(WO_AGENCIES.AGENCY_NAME, 40) AS Customer_Name, LEFT(WO_ADDRESSES.ADDRESS_LINE1, 30) AS Customer_Address, LEFT(WO_ADDRESSES.ADDRESS_CITY, 28) AS Customer_City, LEFT(WO_ADDRESSES.ADDRESS_STATE, 2) AS Customer_State, LEFT(WO_ADDRESSES.ADDRESS_ZIPCODE, 5) AS Customer_Zip, max(rtrim(cast(right(year(wo_agencies.create_date),2) as char)) +rtrim(cast(right(month(wo_agencies.create_date),2) as char)) +rtrim(cast(right(day(wo_agencies.create_date),2) as char))+ +rtrim(cast(right(datepart(hour,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(minute,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(second,wo_agencies.create_date),2) as char))) AS AR_Number, LEFT(WO_AGENCIES.MAIN_PHONE_NUMBER, 13) AS Customer_Phone, MIN(year(invoice_date)) AS Date_Acct_Opened, RIGHT('0' + cast(MAX(month(invoice_date)) AS varchar), 2) + cast(RIGHT(MAX(year(invoice_date)), 2) AS varchar) AS Date_of_Last_Acct_Activity, 'Net 30' AS Terms, SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS Total_Account_Balance, 0 AS Amount_Current, 0 AS [Amount_1-30], SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS [Amount_31-60], 0 AS [Amount_61-90], 0 AS [Amount_91+], ' ' AS Comment_Code, WO_STATIONS.STATION_CALL_LETTERS AS StationFROM WO_AGENCY_BILLING_ADDRESSES INNER JOIN WO_AGENCIES ON WO_AGENCY_BILLING_ADDRESSES.AGENCY_ID = WO_AGENCIES.AGENCY_ID INNER JOIN WO_ADDRESSES ON WO_AGENCY_BILLING_ADDRESSES.BILLING_ADDRESS_ID = WO_ADDRESSES.ADDRESS_ID INNER JOIN WO_INVOICES INNER JOIN WO_ORDER_BILL_PLANS ON WO_INVOICES.ORDER_BILL_PLAN_ID = WO_ORDER_BILL_PLANS.ORDER_BILL_PLAN_ID INNER JOIN WO_ORDERS ON WO_ORDER_BILL_PLANS.ORDER_ID = WO_ORDERS.ORDER_ID ON WO_AGENCIES.AGENCY_ID = WO_ORDERS.AGENCY_ID INNER JOIN WO_STATIONS ON WO_ORDERS.STATION_ID = WO_STATIONS.STATION_IDWHERE invoice_date <>= getdate() - 90GROUP BY Agency_Name, Address_Line1, Address_City, Address_State, Address_ZipCode, Main_Phone_Number, WO_STATIONS.STATION_CALL_LETTERSUNION ALLSELECT LEFT(WO_AGENCIES.AGENCY_NAME, 40) AS Customer_Name, LEFT(WO_ADDRESSES.ADDRESS_LINE1, 30) AS Customer_Address, LEFT(WO_ADDRESSES.ADDRESS_CITY, 28) AS Customer_City, LEFT(WO_ADDRESSES.ADDRESS_STATE, 2) AS Customer_State, LEFT(WO_ADDRESSES.ADDRESS_ZIPCODE, 5) AS Customer_Zip, max(rtrim(cast(right(year(wo_agencies.create_date),2) as char)) +rtrim(cast(right(month(wo_agencies.create_date),2) as char)) +rtrim(cast(right(day(wo_agencies.create_date),2) as char))+ +rtrim(cast(right(datepart(hour,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(minute,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(second,wo_agencies.create_date),2) as char))) AS AR_Number, LEFT(WO_AGENCIES.MAIN_PHONE_NUMBER, 13) AS Customer_Phone, MIN(year(invoice_date)) AS Date_Acct_Opened, RIGHT('0' + cast(MAX(month(invoice_date)) AS varchar), 2) + cast(RIGHT(MAX(year(invoice_date)), 2) AS varchar) AS Date_of_Last_Acct_Activity, 'Net 30' AS Terms, SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS Total_Account_Balance, 0 AS Amount_Current, 0 AS [Amount_1-30], 0 AS [Amount_31-60], SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS [Amount_61-90], 0 AS [Amount_91+], ' ' AS Comment_Code, WO_STATIONS.STATION_CALL_LETTERS AS StationFROM WO_AGENCY_BILLING_ADDRESSES INNER JOIN WO_AGENCIES ON WO_AGENCY_BILLING_ADDRESSES.AGENCY_ID = WO_AGENCIES.AGENCY_ID INNER JOIN WO_ADDRESSES ON WO_AGENCY_BILLING_ADDRESSES.BILLING_ADDRESS_ID = WO_ADDRESSES.ADDRESS_ID INNER JOIN WO_INVOICES INNER JOIN WO_ORDER_BILL_PLANS ON WO_INVOICES.ORDER_BILL_PLAN_ID = WO_ORDER_BILL_PLANS.ORDER_BILL_PLAN_ID INNER JOIN WO_ORDERS ON WO_ORDER_BILL_PLANS.ORDER_ID = WO_ORDERS.ORDER_ID ON WO_AGENCIES.AGENCY_ID = WO_ORDERS.AGENCY_ID INNER JOIN WO_STATIONS ON WO_ORDERS.STATION_ID = WO_STATIONS.STATION_IDWHERE invoice_date <>= getdate() - 120GROUP BY Agency_Name, Address_Line1, Address_City, Address_State, Address_ZipCode, Main_Phone_Number, WO_STATIONS.STATION_CALL_LETTERSUNION ALLSELECT LEFT(WO_AGENCIES.AGENCY_NAME, 40) AS Customer_Name, LEFT(WO_ADDRESSES.ADDRESS_LINE1, 30) AS Customer_Address, LEFT(WO_ADDRESSES.ADDRESS_CITY, 28) AS Customer_City, LEFT(WO_ADDRESSES.ADDRESS_STATE, 2) AS Customer_State, LEFT(WO_ADDRESSES.ADDRESS_ZIPCODE, 5) AS Customer_Zip, max(rtrim(cast(right(year(wo_agencies.create_date),2) as char)) +rtrim(cast(right(month(wo_agencies.create_date),2) as char)) +rtrim(cast(right(day(wo_agencies.create_date),2) as char))+ +rtrim(cast(right(datepart(hour,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(minute,wo_agencies.create_date),2) as char)) +rtrim(cast(right(datepart(second,wo_agencies.create_date),2) as char))) AS AR_Number, LEFT(WO_AGENCIES.MAIN_PHONE_NUMBER, 13) AS Customer_Phone, MIN(year(invoice_date)) AS Date_Acct_Opened, RIGHT('0' + cast(MAX(month(invoice_date)) AS varchar), 2) + cast(RIGHT(MAX(year(invoice_date)), 2) AS varchar) AS Date_of_Last_Acct_Activity, 'Net 30' AS Terms, SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS Total_Account_Balance, 0 AS Amount_Current, 0 AS [Amount_1-30], 0 AS [Amount_31-60], 0 AS [Amount_61-90], SUM(WO_INVOICES.OUTSTANDING_AMOUNT) AS [Amount_91+], ' ' AS Comment_Code, WO_STATIONS.STATION_CALL_LETTERS AS StationFROM WO_AGENCY_BILLING_ADDRESSES INNER JOIN WO_AGENCIES ON WO_AGENCY_BILLING_ADDRESSES.AGENCY_ID = WO_AGENCIES.AGENCY_ID INNER JOIN WO_ADDRESSES ON WO_AGENCY_BILLING_ADDRESSES.BILLING_ADDRESS_ID = WO_ADDRESSES.ADDRESS_ID INNER JOIN WO_INVOICES INNER JOIN WO_ORDER_BILL_PLANS ON WO_INVOICES.ORDER_BILL_PLAN_ID = WO_ORDER_BILL_PLANS.ORDER_BILL_PLAN_ID INNER JOIN WO_ORDERS ON WO_ORDER_BILL_PLANS.ORDER_ID = WO_ORDERS.ORDER_ID ON WO_AGENCIES.AGENCY_ID = WO_ORDERS.AGENCY_ID INNER JOIN WO_STATIONS ON WO_ORDERS.STATION_ID = WO_STATIONS.STATION_IDWHERE invoice_date <= getdate() - 120GROUP BY Agency_Name, Address_Line1, Address_City, Address_State, Address_ZipCode, Main_Phone_Number, WO_STATIONS.STATION_CALL_LETTERS
B) Put this code in the SQL Window in WideOrbit and execute the code. This will setup the export file data.
Select Customer_Name,Customer_Address,Customer_City,Customer_State,
Customer_Zip, right(AR_Number,12) as Ar_Number,Customer_Phone,min(Date_Acct_Opened) as Date_Acct_Opened,max(Date_Of_Last_Acct_Activity)as Date_Of_Last_Acct_Activity, Terms,Right('000000000' + cast(cast(Sum(Total_Account_Balance)as integer)as varchar),9) as Total_Account_Balance, Right('000000000' + cast(cast(Sum(Amount_Current)as integer)as varchar),9) as Amount_Current,Right('000000000' + cast(cast(Sum([Amount_1-30])as integer)as varchar),9) as [Amount_1-30],Right('000000000' + cast(cast(Sum([Amount_31-60])as integer)as varchar),9) as [Amount_31-60],Right('000000000' + cast(cast(Sum([Amount_61-90])as integer)as varchar),9) as [Amount_61-90],Right('000000000' + cast(cast(Sum([Amount_91+]) as integer)as varchar),9)as [Amount_91+],Comment_Code, Station from ttn_AR_experianGroup by
Customer_Name,Customer_Address,Customer_City,Customer_State, Customer_Zip,AR_Number,Customer_Phone,Terms,Comment_Code,Station
C) Finally, Click the Export Data button (fourth button from the left) and save the data as a text file. This file can now be emailed to Experian.
That's it. Credit reporting in 3 easy steps. Well, not easy for me, but easy for you.
No comments:
Post a Comment