SalesPad Support Hours

8am-5pm EST Monday-Thursday

8am-4pm EST Friday

Start a conversation

Importing Credit Card Transactions Using SQL Statements

Important Notice

After October 29, 2019, SalesPad will no longer be supporting CardControl. Additionally, the application will cease to be a PA-DSS validated solution as of this date, and therefore CardControl customers would no longer be PCI compliant. 

Instead, SalesPad Desktop now offers built-in credit card processing via Nodus PayFabric. If you have questions or want more information on our credit card processing services, please contact your sales rep.


Overview

This document addresses the case where external transactions need to be imported into CardControl 3.0 tables. This document gives an overview of the spcc.CustomerCreditCard and spcc.CreditCardTransaction tables, and provides example SQL statements that can be used for importing transactions.

Tables
spcc.CustomerCreditCard:
  • CC_ID:
  • Customer_Num:
  • CC_Number_Masked:

Int32(ID):

String: String:

Auto-generated Credit Card ID. Unique customer ID string.

Masked credit number, of the form XXXX-XXXX-XXXX-1234.

- CC_Type:

String:

Full name of the credit card type (VISA, MASTERCARD,

   

DISCOVER, AMEX, etc.).

- CC_Number_Secure:

String:

Encrypted credit card number or token ID.

- CC_Number_Secure_Salt:

String:

Secure Salt (This will be NULL).

- CC_Expiration_Date:

DateTime:

Expiration date of the credit card (MM/YY).

- Cardholder_Name:

String:

Name of the cardholder as displayed on card.

- Cardholder_Street_Address:

String:

Street name and number (123 Example Street) specified in the

- Cardholder_City:

String:

card billing address.

City specified in the card billing address.

- Cardholder_State:

String:

2-letter specified in the card billing address.

- Cardholder_Zip:

String:

Zip code specified in the card billing address.

- Cardholder_Country:

String:

Cardholder country specified in the card billing address.

- Cardholder_Country_Code:

String:

Cardholder country code specified in the card billing address

- Cardholder_Phones:

String:

Whitespace delimited list of card holder phone numbers,

   

maximum amount 3.

- CC_Description:

String:

Description of the credit card.

- Created_On:

DateTime:

Date and time of when the card was created.

- Created_By:

String:

Username to identify who created the card.

- Changed_On:

DateTime:

Date and time of when the credit card information was changed.

- Changed_By:

String:

Username of who made the most recent change.

- Dtstamp:

String:

SQL-generated timestamp.

- IsDefault:

Boolean:

Determines if this is the default credit card for a customer

- Type:

Enum(int):

Determines if the credit card is a token or not (0 for CreditCard

1 for Token).

  • TokenStorage: Int32: Processor ID, available in spcc.CreditCardProcessor.
  • Card_Type: Enum(int): Enum representing the card type (MasterCard, Visa, etc.).

Possible values:

0: Unknown

1: NotSet

2: AM

3: DI

4: MC

5: VI

spcc.CreditCardTransaction:

- CC_Transaction_ID:

Int32(ID):

Auto-generated transaction ID.

- CC_Processor_ID:

Int32:

Processor ID associated with the transaction.

- CC_Processor_Name:

String:

Name of the processor.

- CC_Transaction_Type:

Enum (int):

Type of transaction

Possible values:

0: None

1: Authorization

2: Charge

3: Capture

4: Void

5: Credit

  • CC_Transaction_Time:
  • CC_Cardholder_Name:
  • CC_Type:

DateTime: String: String:

Date and time of when transaction occurred. Cardholder's name as displayed on the card.

Full name of the credit card(VISA, MASTERCARD, DISCOVER,

   

AMEX, etc.).

- CC_Number_Masked:

String:

Masked credit number, of the form XXXX-XXXX-XXXX-1234.

- CC_Expiration_Date:

DateTime:

Date and time of the expiration date.

- CC_Bank_Phone:

String:

Phone number of associated bank.

  • CC_Transaction_Status: Enum(int): Designates status of transaction.

Possible values:

0: New

1: Pending

2: Success

3: Fail

- Document_XML:

String:

XML serialized SalesDocument object.

- Document_Number:

String:

Document ID.

- Document_Type:

Enum(int):

Designates the type of document.

Possible values:

0: Unknown

1: Quote

2: Order

3: Invoice

4: Return

5: Backorder

6: Fulfillment

- Document_Group:

String:

ID that multiple related documents can belong to. Also known as a Master Number in Microsoft Dynamics GP and Master_Num for the SalesDocument object.

- Document_Date:

DateTime:

Date and time of document creation.

- Customer_Num:

String:

Unique string identifier of the customer.

- Customer_Name:

String:

Customer name.

- PO_Num:

String:

Purchase order associated with the document.

- Comments:

String:

User comments about the transaction.

- Document_Total

Decimal:

Total amount associated with this document.

- Currency_ID

String:

ID of the currency in use for this document.

- Freight:

Decimal:

This document's freight amount.

- Tax:

Decimal:

This document's tax amount.

- Discount:

Decimal:

The discount amount.

- Amount:

Decimal:

The total amount associated with this document.

- Auth_Code:

String:

Unique identifier for the current transaction. If this transaction is

   

new, this field is automatically generated by the processor when

   

the transaction is completed.

- Processor_Results:

String:

Processor response details.

- Created_On:

DateTime:

Date and time of when the transaction was created.

- Created_By:

String:

User who created the transaction.

- Changed_On:

DateTime:

Date and time of when the credit card transaction information

- Changed_By:

String:

changed.

User who changed the transaction most recently.

- Dtstamp:

String:

SQL-generated timestamp. (Ex: 0x00000000000058B0).

  • Original_Transaction_Info: String: XML serialized CreditCardTransaction for the previous

associated transaction.

  • Document_Source: Enum(int): Designates the source of this request.

Possible values:

0: Unknown

1: SOPEntry

2: CashReceipt

3: WebApi

  • Tax_Exempt_1:
  • Tax_Exempt_2:

String: String:

Tax-exemption reason.

Additional Tax-exemption reason.

- Shipping_Method:

String:

The shipping method with the associated products.

- ISOCurrencyID:

String:

The ISO standardized currency ID for the current transaction

- Ship_To_Contact:

String:

amounts.

The name of the customer with the associated shipping address.

- Ship_To_Address_1:

String:

The first line of the shipping address.

- Ship_To_Address_2:

String:

The second line of the shipping address.

- Ship_To_Address_3:

String:

The third line of the shipping address.

- Ship_To_City:

String:

The city specified in the shipping address.

- Ship_To_City:

String:

The city specified in the shipping address.

- Ship_To_State:

String:

The state specified in the shipping address.

- Ship_To_Zip:

String:

The zip code specified in the shipping address.

- Ship_To_Country:

String:

The country specified in the shipping address.

- Ship_To_Country_Code:

String:

The country code specified in the shipping address.

- Ship_To_Phone:

String:

The phone number specified in the shipping address.

- Ship_To_Fax:

String:

The fax number specified in the shipping address.

- Ship_To_Email:

String:

The email address specified in the shipping address.

- Bill_To_Contact:

String:

The name of the customer associated with the billing address.

- Bill_To_Address_1:

String:

The first line of the billing address.

- Bill_To_Address_2:

String:

The second line of the billing address.

- Bill_To_Address_3:

String:

The third line of the billing address.

- Bill _To_City:

String:

The city specified in the billing address.

- Bill _To_State:

String:

The state specified in the billing address.

- Bill _To_Zip:

String:

The zip code specified in the billing address.

- Bill_To_Country:

String:

The country specified in the billing address.

- Bill_To_Country_Code:

String:

The country code specified in the billing address.

- Bill_To_Phone:

String:

The phone number specified in the billing address.

- Bill_To_Fax:

String:

The fax number specified in the billing address.

- Bill_To_Email:

String:

The email address specified in the billing address.

  • Ship_To_Addr_Code:
  • Bill_To_Addr_Code:

String: String:

The address code specified in the shipping address. The address code specified in the billing address.

- Auth_Code2:

String:

Secondary identifier for the current transaction set by the

   

payment processor.

- CC_ID:

Int32:

ID automatically generated when a card is saved (or -1 if it's a

- Mapped_CC_Type:

String:

new card). Unused (NULL).

- Card_Type:

Enum(int):

Enum representing the card type (MasterCard, Visa, etc.).

Possible values:

0: Unknown

1: NotSet

2: AM

3: DI

4: MC

5: VI

- Machine_Name:

String:

String identifying the name of the machine that created the transaction.

- Session_Id:

Int32:

Session ID identifying the session in which the transaction was

   

performed.

Example SQL Statements
spcc.CustomerCreditCard

Note that in the following query, many fields are missing. This statement is structured to contain only the essential information required to import a credit card. Any additional field information can be safely omitted, but including more information is preferred. Refer to the above table information as needed.

This example demonstrates how a user can import a token from an external system. Users wishing to import CreditCard data are encouraged to do so using the CardControl SDK.

spcc.CreditCardTransaction

Note that in the following query, many fields are missing. This statement is structured to contain only the essential information required to import a credit card transaction. . Any additional field information can be safely omitted, but including more information is preferred. Refer to the above table information as needed.

Choose files or drag and drop files
Was this article helpful?
Yes
No
  1. SalesPad Support

  2. Posted
  3. Updated

Comments