TIMSS 6 & VeriSign

TIMSS 6 integrates credit card processing with VeriSign, a big improvement over PC Charge. I've had no issues with it failing since we upgraded to TIMSS 6. A helpful documents can be found on VeriSign's website. Here is a good one.

Once you have created your account with VeriSign, you can login to your account and run reports as to what's been charged if you do have any mis-matches/errors at

https://payments.verisign.com/manager

VeriSign gives you 2 accounts, a Test and a Production one. You can control which once TIMSS is charging to in the ..\\Services\TimssInterfaceServer\TimssInterfaceConfig.xml file. Your TTST should be "test-payflow.verisign.com", whereas TPRO should be "payflow.verisign.com". Since this is stored in a config file and not the database, when you refresh the TTST DB, you don't have to worry about it pointing to the wrong server. However, if you create a new TIMSS instance from TPRO, you will need to point it to the correct server in the copied config file.

Testing can be done with the following card numbers, which will only work on the test server:

Issuer	Test Number
American Express	378282246310005
American Express	371449635398431
Amex Corporate	378734493671000
Australian BankCard	5610591081018250
Diners Club	30569309025904
Diners Club	38520000023237
Discover	6011111111111117
Discover	6011000990139424
JCB	3530111333300000
JCB	3566002020360505
MasterCard	5555555555554444
MasterCard	5105105105105100
Visa	4111111111111111
Visa	4012888888881881
Visa	4222222222222 Note: Even though this number has a different character count than the other test numbers, it is the correct and functional number.

Settlement is done nightly by VeriSign depending upon who your processor is.

Processor	Settlement Time
American Express Phoenix	7 PM Pacific Time
FDMS Nashville	9 PM Pacific Time
FDMS South	4 PM Pacific Time
Global Payments-Central	3 PM Pacific Time
Nova	7 PM Pacific Time
Paymentech New Hampshire	4 PM Pacific Time
First Data TeleCheck	6 PM Pacific Time
Vital	8 PM Pacific Time

To have your charges posted to the bank, you need to run the CCP610 process. This is a process that you will most likely want to schedule to run nightly according to when your procesor settles with VeriSign. If you run CCP610 after your bank settles, then you won't see the charges until the next time you processor settles (i.e. the next day).

When charging a card, Verisign puts an Authorization on the card for the funds, but the funds aren't actually charged until settlement is run. This allows you to do a Reversal the same day and they charge won't appear on the cardholder's statement. On the reports from the VeriSign Manager, you will see the Authorization for the charge, but you won't see the actual debit until the settlement.

The same is true for credit card refunds. You will see them in TIMSS as unprocessed until the CCP610 is run. Thus you can reverse them the same day.

Applies to: TIMSS6

TIMSS 6: FAR TXN_TYPE_CODEs

When a transaction is inserted into the FAR_TXN table it will have a TXN_TYPE_CODE. Some of the codes are the same in TIMSS 5 & TIMSS 6, but some new ones have been added.

Here is what they mean in TIMSS 6:

1 - Receipt: a payment
2 - Voucher: a refund (credit card or check)
3 - Transfer: funds moved from one order to another
4 - Sale: prodcut price that a customer is billed
5 - Writeoff: left over amount you aren't asking the customer to pay
6 - Adjustment: Adjustment/Cancelation fees with real dollar amounts
7 - Revenue Recognition: generated by the FAR670 process
8 - Memo: $0 but notes adjustments
9 - Deferred Receipt : future payments
0 - “Non-Receivable”
J - Journal Entry: done on FAR003J-Advanced Adjustments

Note that these are characters not numbers because of the J. If you are using this column in queries and are comparing it with numbers it will work, until you have a J somewhere in the column. Then you will get an error when comparing a number to a character.

Applies to: TIMSS6

Alternate Characters

In TIMSS 5 & 6, you can use special/alternate characters (i.e. á, â, ã, ä, å, or æ) in customer names. You may just copy and paste them in from other applications, or they can be entered by holding down the ALT key and entering the corresponding code found in the Alternate Characters & TIMSS document on the TIMSSCare website.

If you use a special character for a customer name, make sure you create an alias for the customer without the special character.

For example, if a name was “Káighn Company” you would create an Alias where the “á” was a normal “a”: “Kaighn Company”. This way when searching for the company, one would not have to enter the special character.

These characters can be used in product descriptions as well (at least in TIMSS 6).

So the next question is how do I find these special characters to make sure that the name has an alias? Well I created a function on my TIMSS 6 SQL Server:

CREATE FUNCTION DBO.HASALTCHAR(@SEARCH_STRING AS VARCHAR(80))
RETURNS INTEGER AS
BEGIN
DECLARE @POSITION INTEGER, @STRING VARCHAR(80), @TRUEFALSE INTEGER
SET @POSITION = 1
SET @STRING = @SEARCH_STRING
SET @TRUEFALSE=0
WHILE @POSITION <= DATALENGTH(@STRING)
BEGIN
IF (ASCII(SUBSTRING(@STRING, @POSITION, 1))>127)
BEGIN
SET @TRUEFALSE=1
END
SET @POSITION = @POSITION + 1
END
RETURN @TRUEFALSE
END

And I can use it to find names that have alternate characters:

select c.master_customer_id, c.label_name, a.search_name, a.alias_code
from customer c
left outer join cus_alias a on c.master_customer_id=a.master_customer_id
where dbo.HasAltChar(c.label_name)=1
order by c.master_customer_id


Applies to: TIMSS5 & TIMSS6

SOUNDEX & De-duping

SOUNDEX is a phonetic coding used to group similar English names. It was developed in the early 1900s and used with Census data to group people with similar last names. Because the Census data was hand written and sometimes given by neighbors and those who couldn't spell, it was difficult to find people based purely on the spelling of a last name.

A SOUNDEX code consists of a letter and 3 numbers. The letter is the first letter of the name. The numbers represent letters that are pronounced similarly. Vowels and the letters H, W, and Y are excluded, and double letters are only coded once. The numbers are as follows:

1- B, F, P, V
2- C, G, J, K, Q, S, X, Z
3- D, T
4- L
5- M, N
6- R

If you run out of letters before getting 3 numbers, the rest are filled in with zeros.

Some examples are:

• Washington-W252
• Kaighn- K250
• Lee- L000
• Hall- H400
• Smith- S530
• Smyth- S530

There is a SOUNDEX function in both Oracle and SQL Server, or you can even go here to get the SOUNDEX of a name.

Since it is based on English pronunciation, it will work best with "English" names. And because nicknames are often substituted for first names that start with different letters (Bob & Robert), SOUNDEX should not be used on first names because the first character is so important in the SOUNDEX of name.

How is SOUNDEX connected to TIMSS? The de-duping process (CUS590) uses SOUNDEX to get groups of customers who have similar names. Then other comparisons are made against the customers in that group (same state, same gender, same SSN, etc) to see if they are a potential match. SOUNDEX is not for determining duplicates, it is only to put those with similarly spelled names together so that they can be further compared.

When running a process to find duplicates, we could just compare everyone to everyone. However, for most customers this is a waste of time since they are obviously not duplicates (i.e. Smith;John & Doe;John). To save effort, we can group similar customers first before doing comparisons.

These groups can be based on any characteristic of the customer. Last name is the obvious choice because everyone has one and it rarely has variations or changes (the spelling of my last name has never changed; a woman's usually only does related to marriage). Street addresses & email addresses do change (I've had 6 mailing addresses and used 7 email addresses in the last 15 years), and other characteristics like gender or SSN cast either to broad a net or not narrow enough (SSN would be a match, but I know I don't have it for all my customers and if it did match it's more likely a typo than a duplicate).

If we create groups for out comparisons based on last names being exactly the same, then we miss the most obvious reason duplicates are created: typos. When a name is entered a vowel is omitted or letters that sound alike are used (Smith & Smyth, or for me Kaighn & Kaign).

So if we group customers with similar last names first before comparing, we save ourselves a lot of effort by not comparing customers that obviously aren't duplicates, and we don't loose customers where the last names aren't an exact match.

Thus SOUNDEX is first used to generate groups of customers with similar names and then comparisons are done on those smaller groups. Putting 2 customers in the same group does not mean they are duplicates, it just means that they are similar enough to do comparisons on to see if they are duplicates based upon their name, address, gender, SSN, etc.

Below are some useful websites when you find people with similar last names:

http://www.census.gov/genealogy/www/namesearch.html - If you have 2 spellings of a name and are unsure which is correct you can search the 1990 Census for last names see which name is more common.

http://www.census.gov/genealogy/names/names_files.html - List all last names by frequency. If you have a very large customer database, you may want to de-dup a particular last name (i.e. SMITH, JOHNSON, WILLIAMS, JONES, BROWN, DAVIS, MILLER). Since the SOUNDEX would be the same for all these customers, that would be your group.

http://www.anywho.com/ - Look up people based upon address, or even do a reverse lookup on a phone number.

Applies to: TIMSS5 & TIMSS6

TIMSS 6: Facility Products

TIMSS 6 allows you to sell meeting rooms in Facilities. We do this for our conference rooms that are available for members & non-members to rent, at different pricing of course.

You could just set these up as miscellaneous products, but using the Facilities Product, you get a couple extra attributes.

First, you can check the Internal Scheduling box and TIMSS will keep track of events in the room (facility products sold) in TIMSS, and you can view this similarly to an Outlook calendar.

Second, when you setup the Facility, you can set the configuration and corresponding capacity for each room. When placing the order, you can select the date and configuration of the room.


Third, when you create the Facility Product, you can choose from 3 types:

1. Room Reservation- this is the product for the actual room and allows you to associate it with a particular room from the facility
2. Fees- for general fees not associated with a room
3. Food Services- for food services (similar to Fees)

I only setup one extra Fee facility product since we have many things that we sell (food service, projectors, etc.), but we rarely sell them. I've allowed the person entering the order to set the price on the order and told them to put in the "Product Name" on the ORD001 screen what extra was purchased.

You can also do requirements (i.e. Projectors, Microphones, Food). On Options tab of ORD001, you can pick from requirements you've setup for the product on the product's requirements tab. The requirements need to be associated with a room to show up on the Requirements tab of the Schedule.

Applies to: TIMSS6

CognosScript Editor: Sending Personalized Email with a PDF Attachement

We needed to send a roster and/or invoice to our customers using email. Using the CognosScript Editor I created a process that

1. reads in customer information from a file
2. runs a roster/invoice for that customer
3. saves the output from Impromptu as a PDF file
4. composes a customized email
5. sends the email with the PDF attachment

First you will need a comma delimited text file with all the fields you will need (put in Excel, save as text file):

00031616,"HomeBanc Foundation, Inc.",Ms. Albertelli,kaigr@cof.org,$400.00
00001385,Calhoun County Community Foundation,Ms. anks,kaigr@cof.org,"$1,410.00"
00003788,"Community Foundation of Broward, Inc.",Ms. Bartram,kaigr@cof.org,"$4,630.00"
00002651,Alabama Civil Justice Foundation,Ms. McInnish,kaigr@cof.org,$400.00

Next you will need an Impromptu report that generates your roster/invoice for each customer. The customer ID will be a prompt that is passed by the script.

Then we can use the CognosScript Editor (Start, Programs, Cognos BI, Tools, CognosScript Editor) to create the script.

Sub main()

Dim impapp As object
Dim imprep As Object
Dim objPDFPub As Object
Dim objImpSP As Object
Dim member as String
Dim member_name as String
Dim contact_name as String
Dim contact_sal as String
Dim email_address as String
Dim message_text as String
Dim order_total as String
Dim objOutlook as Object
Dim objOutlookMsg as Object

Set impapp = CreateObject("Impromptu.Application")
Set objOutlook = CreateObject("Outlook.Application")
impapp.Visible 1
impapp.OpenCatalog "X:\TIMSS_AI\Catalogs\Custom_Membership_Reports.cat","User",,"username","password"

Open "C:\1st_Reminder\1st_Reminder.txt" For Input As #1

message_text= "If you intend to renew, but prefer to pay at a later date, please inform us “
message_text= message_text + “of this by April 1, as this information is vital to our budgeting “
message_text= message_text + “process. Please accept our apologies if your dues have already been
message_text= message_text + “mailed. " +chr(13)+chr(13)

Do While Not Eof(1)
Input #1, member, member_name, contact_sal, email_address, order_total
Set imprep = impapp.OpenReport("C:\1st_Reminder\membership_invoice_customer.imr", member)
Set objPDFPub = imprep.PublishPDF
objPDFPub.Publish "C:\1st_Reminder\"+member+"_Invoice.pdf"
imprep.CloseReport
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.To=email_address
.Subject="Council on Foundations - Membership Renewal"
.Body="Dear "+contact_sal+", "+chr(13)+chr(13)+ "We are writing to remind you that your 2004 membership dues of "+order_total+" will be due on April 1, 2004. " + message_text
.Attachments.Add("C:\1st_Reminder\"+member+"_Invoice.pdf")
.Send
End With
Set objOutlookMsg=Nothing

Loop
Close #1

impapp.Quit

End Sub

Things to watch out for:

1. Email is From the user that is logged in on the workstation’s default account
2. Because of Security settings in Outlook 2003, you will need to confirm the sending of each email
3. Style of email (i.e. font) is that user’s default style
4. Attachments can take up a lot of space. If you go over your mailbox limit, process will not send messages (I delete from Sent folder as they go out).
5. Make sure Impromptu report has output for all customers. If not, they will get sent a blank attachment.

Other Tips:

1. Can save as Excel
   ImpRep.ExportExcel “E:\Testcases\EdwinCruize\MyAttempts.xls”
2. Print a report
   ImpRep.Print [START, END, COPIES]
3. See Document #113754 in Cognos Knowledge Base on sending an email attachment and other documents on Macros and the Script editor.
4. .MAC file is the script and will launch CongonsScript Editor, .MCX is the compiled script and will run when you double click on it.

Applies to: TIMSS5 & TIMSS6