AR Customer Import

Use to import customer information from a third-party source. Note: Customers are not AR customers until an account is assigned to them so this interface can be used for rental customers.

ASCII Delimited Format

customer_account	char	25	Required: student or customer id
student_account	char	25	optional: secondary id – advanced button
bad_check_number	char	25	optional: bad check ID number
salutation	char	10	optional: Mrs., Mr. etc.
first_name	char	50	optional: first name
last_name	char	50	Required: must at least send a last name
alias	char	80	optional: customer alias
email	char	128	optional: customer E-mail
create_date	datetime		optional: date student record was created
active_date	datetime		optional: date student was activated
comment_text	char	255	optional: can be seen advanced tab
type_code	char	2	not used – leave blank
card_code	char	2	not used – leave blank
member_amount	money		comes from PrismCore Memberships; leave blank
ship_address_descr	char	100	optional: students shipping address desc
ship_address	char	255	optional: students shipping address
ship_city	char	50	optional: students shipping city
ship_state	char	4	optional: students shipping state
ship_postal_code	char	20	optional: students shipping postal code
ship_country	char	24	optional: students shipping country
ship_phone1	char	16	optional: students shipping phone1
ship_phone2	char	16	optional: students shipping phone2
ship_phone3	char	16	optional: students shipping phone3
ship_ext1	char	6	optional: students shipping ext1
ship_ext2	char	6	optional: students shipping ext2
ship_ext3	char	6	optional: students shipping ext3
bill_address_descr	char	100	optional: students billing address desc
bill_address	char	255	optional: students billing address
bill_city	char	50	optional: students billing city
bill_state	char	4	optional: students billing state
bill_postal_code	char	20	optional: students billing postal code
bill_country	char	24	optional: students billing country
bill_phone1	char	16	optional: students billing phone1
bill_phone2	char	16	optional: students billing phone2
bill_phone3	char	16	optional: students billing phone3
bill_ext1	char	6	optional: students billing ext1
bill_ext2	char	6	optional: students billing ext2
bill_ext3	char	6	optional: students billing ext3
b_delete	int	1	Required: “0” = add; “1” = delete

Field Descriptions

Customer_ID	In the prism_trn is a SQL index key and is not used except as a unique identifier for the record.
customer_account	Is the customer ID. This ID is what is used to identify the customer in PrismCore. The interface will look for a match on this ID. If it finds one it will update otherwise it will build a new customer.
student_account	Is a secondary ID. This will be put in the advanced tab of the customer record and can be used to find the customer at the register. Note: there are no restrictions on this field. The interface will allow the same student_account to be assigned to multiple customer ID’s. This ID is also used in conjunction with the Enrollment Module.
bad_check_number	Is used to flag the customer as a bad check customer. The bad check account in this field will cause the bad check flag at the register.
Salutation	Will be loaded if present but is not required.
first_name	Will be loaded if present but is not required.
last_name	Is required for all customers.
Alias	Will be loaded if present but is not required.
Email	Is not validated and will be loaded if present but is not required.
create_date	Can be populated, if it is left blank will default to 1/1/1970.
active_date	Can be populated, if it is left blank will default to 1/1/1970.
comment_text	Populated the comment on the advanced tab if present but is not required.
card_code	Not used leave blank.
type_code	Not used leave blank.
member_amount	Not used leave blank.
Shipping address and billing address	Follow a couple of different rules. If the customer exists, and has both shipping and billing addresses, and the import file contains just the shipping address, another address record will be added to the customer, and the ‘Use shipping as billing’ option will be checked. If the existing addresses are not set to default the addresses from the file, they will be added as default to the customer. If the existing addresses are set as default and both shipping and billing addresses are in the file, they will update the existing customer address. If only the billing address is sent, the system will update the existing default billing address. In addition to that, elements of the address will not load unless there is something in the street address field. So for instance, if the store wants to load just the phone number, there would have to be something in the street address field in the file which could be “Phone”, otherwise the phone number in the file will not be loaded or updated.
ship_address_descr	Is the description of the customer’s shipping address it is not required.
ship_address	Is the street address for the customer’s shipping address it is not required.
ship_city	Is the city for the customer’s shipping address it is not required.
ship_state	Is the state for the customer’s shipping address it is not required.
ship_postal_code	Is the zip/postal code of the customer’s shipping address it is not required.
ship_country	Is the country for the customer’s shipping address it is not required.
ship_phone1	Is the first phone number of the customers shipping address it is not required.
ship_phone2	Is the second phone number of the customers shipping address it is not required.
ship_phone3	Is the third phone number of the customers shipping address it is not required.
ship_ext1	Is the extension for the first phone number of the customer’s shipping address it is not required.
ship_ext2	Is the extension for the second phone number of the customer’s shipping address it is not required.
ship_ext3	Is the extension for the third phone number of the customer’s shipping address it is not required.
bill_address_descr	Is the description of the customer’s billing address it is not required.
bill_address	Is the street address for the customer’s billing address it is not required.
bill_city	Is the city for the customer’s billing address it is not required.
bill_state	Is the state for the customer’s billing address it is not required.
bill_postal_code	Is the zip/postal code of the customer’s billing address it is not required.
bill_country	Is the country for the customer’s billing address it is not required.
bill_phone1	Is the first phone number of the customers billing address it is not required.
bill_phone2	Is the second phone number of the customers billing address it is not required.
bill_phone3	Is the third phone number of the customers billing address it is not required.
bill_ext1	Is the extension for the first phone number of the customer’s billing address it is not required.
bill_ext2	Is the extension for the second phone number of the customer’s billing address it is not required.
bill_ext3	Is the extension for the third phone number of the customer’s billing address it is not required.

PrismCore Import_AR_Customer Table Names

(Use this for interfaces that bypass WPConsole and write directly to prism_trn)

Column	Data Type	Length	Prec	Scale	Description
customer_id	int	4	10	0	Index Key
customer_account	char	25	25		student or customer id
student_account	char	25	25		secondary id – advanced button
bad_check_number	char	25	25		bad check ID number
salutation	char	10	10		Mrs., Mr. etc.
first_name	char	50	50		first name
last_name	char	50	50		must at least send a last name
alias	char	80	80		customer Alias
email	char	128	128		customer E-mail
create_date	datetime	8	23	3	date student record was created
active_date	datetime	8	23	3	date student was activated
comment_text	char	255	255		can be seen in the Advanced tab
card_code	char	2	2		not used – leave blank
type_code	char	2	2		not used – leave blank
member_amount	money	8	19	4	comes from PrismCore Memberships; leave blank
ship_address_descr	char	100	100		students shipping address desc
ship_address	char	255	255		students shipping address
ship_city	char	50	50		students shipping city
ship_state	char	4	4		students shipping state
ship_postal_code	char	20	20		students shipping postal code
ship_country	char	24	24		students shipping country
ship_phone1	char	16	16		students shipping phone1
ship_phone2	char	16	16		students shipping phone2
ship_phone3	char	16	16		students shipping phone3
ship_ext1	char	6	6		shipping ext1
ship_ext2	char	6	6		shipping ext2
ship_ext3	char	6	6		shipping ext3
bill_address_descr	char	100	100		students billing address desc
bill_address	char	255	255		students billing address
bill_city	char	50	50		students billing city
bill_state	char	4	4		students billing state
bill_postal_code	char	20	20		students billing postal code
bill_country	char	24	24		students billing country
bill_phone1	char	16	16		students billing phone1
bill_phone2	char	16	16		students billing phone2
bill_phone3	char	16	16		students billing phone3
bill_ext1	char	6	6		students billing ext1
bill_ext2	char	6	6		students billing ext2
bill_ext3	char	6	6		students billing ext3
b_delete	tinyint	1	3	0	“0” = add
tm_stamp	timestamp	8	8		SQL timestamp

ASCII Delimited Format with Birth Date

There is an alternate format that includes the customer birth date. This format requires a setting be changed in WPConsole to use the alternate file format. Please contact support to make this change before implementing.

Column	Data Type	Length	Description
customer_account	char	25	Required: student or customer id
student_account	char	25	optional: secondary id – advanced button
bad_check_number	char	25	optional: bad check ID number
salutation	char	10	optional: Mrs., Mr. etc.
first_name	char	50	optional: first name
last_name	char	50	Required: must at least send a last name
alias	char	80	optional: customer alias
email	char	128	optional: customer E-mail
create_date	datetime		optional: date student record was created
active_date	datetime		optional: date student was activated
comment_text	char	255	optional: can be seen advanced tab
type_code	char	2	not used – leave blank
card_code	char	2	not used – leave blank
member_amount	money		comes from PrismCore Memberships; leave blank
ship_address_descr	char	100	optional: students shipping address desc
ship_address	char	255	optional: students shipping address
ship_city	char	50	optional: students shipping city
ship_state	char	4	optional: students shipping state
ship_postal_code	char	20	optional: students shipping postal code
ship_country	char	24	optional: students shipping country
ship_phone1	char	16	optional: students shipping phone1
ship_phone2	char	16	optional: students shipping phone2
ship_phone3	char	16	optional: students shipping phone3
ship_ext1	char	6	optional: students shipping ext1
ship_ext2	char	6	optional: students shipping ext2
ship_ext3	char	6	optional: students shipping ext3
bill_address_descr	char	100	optional: students billing address desc
bill_address	char	255	optional: students billing address
bill_city	char	50	optional: students billing city
bill_state	char	4	optional: students billing state
bill_postal_code	char	20	optional: students billing postal code
bill_country	char	24	optional: students billing country
bill_phone1	char	16	optional: students billing phone1
bill_phone2	char	16	optional: students billing phone2
bill_phone3	char	16	optional: students billing phone3
bill_ext1	char	6	optional: students billing ext1
bill_ext2	char	6	optional: students billing ext2
bill_ext3	char	6	optional: students billing ext3
b_delete	int		Required: “0” = add; “1” = delete
birth_date	datetime		optional: Date of birth

XML Definition

The XML definition uses the same fields as the ASCII Formats. Note: some fields will be omitted if there is no corresponding data. All attributes are CDATA #IMPLIED.

<?xml version="1.0"?>

<trn_ar_customer_imports>

<trn_ar_customer_import

customer_account="MRBEATLE"

student_account="Beatles Account"

bad_check_number="BadCheck12345"

salutation="Sir"

first_name="Richard"

last_name="Starkey"

alias="Ringo"

email="ringostarr@nebook.com"

create_date="05/31/2007"

active_date="05/31/2007"

comment_text="Comment for Ringo Starr"

card_code=""

type_code="A"

member_amount=".0000"

ship_address_descr="Richard Starkey"

ship_address="101 Dingle Way"

ship_city="Liverpool"

ship_state="NE"

ship_postal_code="12345-6789"

ship_country="USA"

ship_phone1="111-222-3333"

ship_phone2="222-333-4444"

ship_phone3="333-444-5555"

ship_ext1="4444"

ship_ext2="5555"

ship_ext3="6666"

bill_address_descr="Richard Starkey"

bill_address="201 Main Street Road"

bill_city="Liverpool"

bill_state="NE"

bill_postal_code="12345-6789"

bill_country="USA"

bill_phone1="111-222-3333"

bill_phone2="222-333-4444"

bill_phone3="333-444-5555"

bill_ext1="4444"

bill_ext2="5555"

bill_ext3="5555"

b_delete="0"

/>

</trn_ar_customer_imports>

PRISM Import Format for AR Customer Import

Customer Record

Filename = Cxxxxxxx.yyy xxxxxxx = unique number, yyy=store number

Record Size: 269 bytes

Field Name	Size	Description
Customer Number	25	REQUIRED FIELD. This field can be alpha-numeric. If two customers have the same number, the last one read will over-write any previous.
Customer Name	30	Required FIELD See note regarding Parsing of this field below.
Customer Address #1	30	OPTIONAL FIELD
Customer Address #2	30	OPTIONAL FIELD
Customer Address #3	30	OPTIONAL FIELD
Customer City	15	OPTIONAL FIELD.
Customer State	3	OPTIONAL FIELD.
Customer Zip Code	10	OPTIONAL FIELD
Customer Country	10	10
Customer Phone #1	15	OPTIONAL FIELD.
Customer Phone #2	15	OPTIONAL FIELD.
Customer Phone #3	15	OPTIONAL FIELD.
Customer Create Date	8	OPTIONAL FIELD. Contains the date this customer was added. 'MM/DD/YY'
Add/Delete Flag	1	REQUIRED FIELD. 'A' for ADD, 'D' for DELETE.
REQUIRED FIELD. 'A' for ADD, 'D' for DELETE.	1	REQUIRED FIELD. 'A' for Active, 'B' for bad check.
Customer Bad Check Id	20	OPTIONAL FIELD. Contains a code used for tracking bad checks; could be drivers lic. ssn, etc.
Card Code	2	OPTIONAL FIELD. Lost card code used for validating swiped card at POS.
Membership Amount	8	OPTIONAL FIELD. Dollar amount sold for membership card discounts.
Line Feed	1	OPTIONAL FIELD. Use for record separators.

For PrismCore the Customer Name field is parsed in one of two ways. If there is a comma in the field the field is parsed as Last, First. If there is no comma in the field it is parsed as ‘first last’ with the space being the delimiter. If the names are going to include middle initials it is best to use the Last, First method.

AR Customer Export

Exports any A/R customers that have been added, deleted or updated.

ASCII Delimited Format

Column	Data Type	Length	Description
customer_account	char	25	student or customer id
student_account	char	25	secondary id – advanced button
bad_check_number	char	25	from advanced tab bad check ID number
salutation	char	10	customer salutation
first_name	char	50	customer first name
last_name	char	50	customer last name
alias	char	80	customer Alias
email	char	128	customer E-mail
create_date	datetime		date student record was created
active_date	datetime		date student was activated
comment_text	char	55	from the Advanced tab
card_code	char	2	not used – will be blank
type_code	char	2	not used – will be blank
member_amount	money		from advanced tab
ship_address_descr	char	100	customer Shipping address Description
ship_address	char	255	Street address for shipping address
ship_city	char	50	city for shipping address
ship_state	char	4	state for shipping address
ship_postal_code	char	20	zip/postal code for shipping address
ship_country	char	24	country for shipping address
ship_phone1	char	16	first phone for shipping address
ship_phone2	char	16	second phone for shipping address
ship_phone3	char	16	third phone for shipping address
ship_ext1	char	6	first extension for shipping address
ship_ext2	char	6	second extension for shipping address
ship_ext3	char	6	third extension for shipping address
bill_address_descr	char	100	customer billing address description
bill_address	char	255	street address for billing address
bill_city	char	50	city for billing address
bill_state	char	4	state for billing address
bill_postal_code	char	20	zip/postal code for billing address
bill_country	char	24	country for billing address
bill_phone1	char	16	first phone for billing address
bill_phone2	char	16	second phone for billing address
bill_phone3	char	16	third phone for billing address
bill_ext1	char	6	first extension for billing address
bill_ext2	char	6	second extension for billing address
bill_ext3	char https://prismrbs.wyzed.com/	6	third extension for billing address
b_delete	int		will be 0

PrismCore Export_AR_Customer Table Names

(Use this for interfaces that bypass WPConsole and go directly to prism_trn)

Column	Data Type	Length	Prec	Scale	Description
customer_id	int	4	10	0	Index Key
customer_account	char	25	25		student or customer id
student_account	char	25	25		secondary id – advanced button
bad_check_number	char	25	25		bad check ID number
salutation	char	10	10		Mrs., Mr. etc.
first_name	char	50	50		first name
last_name	char	50	50		last name
alias	char	80	80		customer alias
email	char	128	128		customer E-mail
customer E-mail	datetime	8	23	3	date student record was created
active_date	datetime	8	23	3	date student was activated
comment_text	char	255	255		comment from advanced tab
card_code	char	2	2		not used – leave blank
type_code	char	2	2		not used – leave blank
member_amount	money	8	19	4	from advanced tab
ship_address_descr	char	100	100		students shipping address desc
ship_address	char	255	255		students shipping address
ship_city	char	50	50		students shipping city
ship_state	char	4	4		students shipping state
ship_postal_code	char	20	20		students shipping postal code
ship_country	char	24	24		students shipping country
ship_phone1	char	16	16		students shipping phone1
ship_phone2	char	16	16		students shipping phone2
ship_phone3	char	16	16		students shipping phone3
ship_ext1	char	6	6		shipping ext1
ship_ext2	char	6	6		shipping ext2
ship_ext3	char	6	6		shipping ext3
bill_address_descr	char	100	100		students billing address desc
bill_address	char	255	255		students billing address
bill_city	char	50	50		students billing city
bill_state	char	4	4		students billing state
bill_postal_code	char	20	20		students billing postal code
bill_country	char	24	24		students billing country
bill_phone1	char	16	16		students billing phone1
bill_phone2	char	16	16		students billing phone2
bill_phone3	char	16	16		students billing phone3
bill_ext1	char	6	6		students billing ext1
bill_ext2	char	6	6		students billing ext2
bill_ext3	char	6	6		students billing ext3
b_delete	tinyint	1	3	0	“0” = add
tm_stamp	timestamp	8	8		SQL timestamp

XML Definition

The XML definition uses the same fields as the ASCII Formats. Note: some fields will be omitted if there is no corresponding data. All attributes are CDATA #IMPLIED.

<?xml version="1.0"?>

<trn_ar_customer_exports>

<trn_ar_customer_export

customer_account="MRBEATLE"

student_account="DrumYouAway1"

bad_check_number="BadCheck12345"

salutation="Sir"

first_name="Richard"

last_name="Starkey"

alias="Ringo"

email="ringostarr@nebook.com"

create_date="05/31/2007"

active_date="05/31/2007"

comment_text="Comment for DrumYouAway1"

card_code=""

type_code="A"

member_amount=".0000"

ship_address_descr="Richard Starkey"

ship_address="101 Dingle Way"

ship_city="Liverpool"

ship_state="NE"

ship_postal_code="12345-6789"

ship_country=""

ship_phone1="111-222-3333"

ship_phone2="222-333-4444"

ship_phone3="333-444-5555"

ship_ext1="4444"

ship_ext2="5555"

ship_ext3="6666"

bill_address_descr="Richard Starkey"

bill_address="201 Main Street Road"

bill_city="Liverpool"

bill_state="NE"

bill_postal_code="12345-6789"

bill_country=""

bill_phone1="111-222-3333"

bill_phone2="222-333-4444"

bill_phone3="333-444-5555"

bill_ext1="4444"

bill_ext2="5555"

bill_ext3="5555"

b_delete="0"

/>

</trn_ar_customer_exports>

PRISM Export Format for AR Customer Export

Customer Record

Filename = Cxxxxxxx.yyy xxxxxxx = unique number, yyy=store number

Record Size: 269 bytes

Field Name	Size	Description
Customer Number	25	Customer Code.
Customer Name	30	Customer Name.
Customer Address #1	30	Customer Address Line 1.
Customer Address #2	30	Customer Address Line 2.
Customer Address #3	30	Customer Address Line 3.
Customer City	15	Customer City.
Customer State	3	Customer State.
Customer Zip Code	10	Customer Zip.
Customer Country	10	Customer Country.
Customer Phone #1	15	Customer Phone 1.
Customer Phone #2	15	Customer Phone 2.
Customer Phone #3	15	Customer Phone 3.
Customer Create Date	8	Contains the date this customer was added. 'MM/DD/YY'
Add/Delete Flag	1	'A' for ADD, 'D' for DELETE.
Customer Type Code	1	'A' for Active, 'B' for bad check.
Customer Bad Check Id	20	Contains a code used for tracking bad checks; could be drivers lic., ssn, etc.
Card Code	2	Lost card code used for validating swiped card at POS.
Membership Amount	8	Dollar amount sold for membership card discounts.
Line Feed	1	Record separator.

Accounts Receivable Interfaces

AR Customer Import

ASCII Delimited Format

PrismCore Import_AR_Customer Table Names

ASCII Delimited Format with Birth Date

XML Definition

PRISM Import Format for AR Customer Import

AR Customer Export

ASCII Delimited Format

PrismCore Export_AR_Customer Table Names

XML Definition

PRISM Export Format for AR Customer Export