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.

AR Customer Balance Import

Imports credit limit changes, or opening balances.

ASCII Delimited Format

customer_acct	char	25	Required: student or customer id
agency_number	char	25	Required: must match account code from Acct Maint
current_balance	money		Will always use a “0” unless a custom interface sends the balance. not used.
credit_limit	money		credit limit for student this depends on b_set_credit_limit setting and the Set Account Limit in Account Maintainance. 0 is unlimited unless the limit is set then it is actually zero.
b_set_credit_limit	int		0 or N leaves the Set Limit unchecked. 1 or Y Checks the set limit for the account.
status OR status	char int		Required: "A"ctive, "I" = Inactive Required: "1" = active, "0" = inactive
start_date	datetime		“07/06/2006” no charges before this date
end_date	datetime		“08/26/2006” no charges after this date
po_number	char	25	Not used used to be voucher/po number
b_open_balance	int		Always use a “0” unless a custom interface wants to create an opening balance record
b_delete	int		Required: “0”= add, “1” = delete

If you want to stop a customer from charging either send the inactive flag or a new expiration date. Once the account limit is set by using a 1 in b_set_credit_limit it can only be changed back manually.

PrismCore Import_AR_Customer_Balance Table Names

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

Column	Data Type	Length	Prec	Scale	Description
customer_acct_id	int	4	10	0	Index Key
customer_acct	char	25	25		Customer ID
agency_number	char	25	25		Account Code
current_balance	money	8	19	4	Not used will be 0
credit_limit	money	8	19	4	Credit Limit
start_date	datetime	8	23	3	First date can charge
end_date	datetime	8	23	3	Last date can charge
b_set_credit_limit	tinyint	1	3	0	Credit Limit 0=unlimited 1=set limit
b_delete	tinyint	1	3	0	is always set to 0.
b_active	tinyint	1	3	0	1=active, 0=inactive
b_set_balance	tinyint	1	3	0	0=Leave unchecked, 1=Set Limit
tm_stamp	timestamp	8	8		SQL timestamp

XML Definition

All attributes are CDATA #IMPLIED.

Example:

<?xml version="1.0"?>

<trn_ar_cust_bal_imports>

<trn_ar_cust_bal_import

acct_num="C-17839"

code="BAD CHECKS "

curr_balance="150.0000"

cred_limit="12345.0000"

unlimited="1"

status="I"

start_date="07/04/2006"

exp_date="07/04/2008"

ponum="PONumber for C-17839"

b_open_balance="0"

b_delete="0"

/>

</trn_ar_cust_bal_imports>

PRISM Import Format for AR Customer Balance

Balance Record

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

Record Size: 122 bytes

Field Name	Size	Description
Customer Code	25	REQUIRED FIELD.
Account Code	25	REQUIRED FIELD.
Current Balance	8	COMPUTED IN-HOUSE FIELD. LEAVE BLANK
Credit Limit	8	OPTIONAL FIELD.
Limit Credit	1	OPTIONAL FIELD. This is a 0/1 field that indicates if the credit limit should be observed.
Add/Delete Flag	1	REQUIRED FIELD. 'A' for ADD, 'D' for DELETE.
Status	1	REQUIRED FIELD. 'A'=Active, 'I'=Inactive
Start Date	8	OPTIONAL FIELD. Start date of this account in the form 'MM/DD/YY'. If leaving blank, please include the slashes.
Expire Date	8	OPTIONAL FIELD. Expiration date of this account in the form 'MM/DD/YY'. If leaving blank, please include the slashes.
Last Payment Date	8	IN-HOUSE FIELD. LEAVE BLANK.
Payment Amount	8	IN-HOUSE FIELD. LEAVE BLANK.
Standing PO #	20	OPTIONAL FIELD. Requisition #
Line Feed	1	OPTIONAL FIELD. Use for record separators.

AR Customer Balance Export

Exports any credit limit or balance changes for a customer.

PrismCore Export_AR_Customer_Balance Table Names

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

Column	Data Type	Length	Prec	Scale	Description
customer_acct_id	int	4	10	0	Index Key
customer_acct	char	25	25		Customer ID
agency_number	char	25	25		Account Code
current_balance	money	8	19	4	Balance for this account
credit_limit	money	8	19	4	Credit limit for this account
start_date	datetime	8	23	3	First date can charge
end_date	datetime	8	23	3	Last date can charge
b_set_credit_limit	tinyint	1	3	0	1=Credit Limit set 0=Credit Limit Not set
b_delete	tinyint	1	3	0	Will be 0
b_active	tinyint	1	3	0	1=Active
tm_stamp	timestamp	8	8		SQL Timestamp

ASCII Delimited Format

customer_acct	char	25	Customer ID
agency_number	char	25	Account Code
current_balance	money		Balance for this account
credit_limit	money		Credit limit for this account
start_date	datetime		First date can charge
end_date	datetime		Last date can charge
b_set_credit_limit	int		1=Credit Limit set 0=Credit Limit
b_delete	int		Will be 0
b_active	int		1=Active

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.

Example:

<?xml version="1.0"?>

<trn_ar_cust_bal_exports>

<trn_ar_cust_bal_export

customer_acct="C-17839"

agency_number="BAD CHECKS"

current_balance="150.0000"

credit_limit=".0000"

start_date="01/01/1970"

end_date="01/01/1970"

b_set_credit_limit="0"

b_delete="0"

b_active="1"

/>

<trn_ar_cust_bal_export

customer_acct="C-17839"

agency_number="00000168"

current_balance="-1109.7300"

credit_limit="1175.5400"

start_date="01/01/2001"

end_date="03/11/2004"

b_set_credit_limit="1"

b_delete="0"

b_active="1"

/>

</trn_ar_cust_bal_exports>

PRISM Export Format for AR Customer Balance

Balance Record

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

Record Size: 122 bytes

Field Name	Size	Description
Customer Code	25	Customer Code.
Account Code	25	Account Code.
Current Balance	8	COMPUTED IN-HOUSE FIELD.
Credit Limit	8	Credit Limit.
Limit Credit	1	This is a 0/1 field that indicates if the credit limit should be observed.
Status	1	'1'=Active, '0'=Inactive
Start Date	8	Start date of this account in the form 'MM/DD/YY'.
Expire Date	8	Expiration date of this account in the form 'MM/DD/YY'.
Standing PO #	20	Requisition #
Add/Delete Flag	1	'0' for ADD/EDIT, '1' for DELETE
Last Payment Date	8	Last Payment Date.
Payment Amount	8	Payment Amount.
Line Feed	1	Used for record separators.

AR Agency Import

Imports Account information into Account Maintenance.

PrismCore Import_AR_Agency Table Names

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

Column	Data Type	Length	Prec	Scale	Description
agency_number	char	25	25		Account Code
agency_name	char	80	80		Account Description
address_1	char	80	80		Street Address
address_2	char	80	80		2^nd Line of Street Address
address_3	char	80	80		3^rd Line of Street Address
city	char	40	40		City for Account Address
state	char	4	4		State for Account Address
postal_code	char	20	20		Zip/Postal Code for Account Address
country	char	24	24		Country for Account Address
phone_1	char	16	16		1^st Phone for Account
phone_2	char	16	16		2^nd Phone for Account
phone_3	char	16	16		3^rd Phone for Account
agency_comment	varchar	255	255		Comment on Account
tax_exempt_id	char	25	25		Tax Exempt ID
agency_priority	int	4	10	0	Multi Account Priority number
tender_code	char	2	2		POS code of Tender linked to Account
discount_code	char	4	4		POS code of Discount linked to Account
change_limit	money	8	19	4	Maximum Amount of change permitted
dcc_array	varchar	300	300		6 Digit permissible DCC’s concatenate the DCC’s together
discount_array	varchar	150	150		6 Digit permissible DCC’s concatenate the DCC’s together
b_change_due	tinyint	1	3	0	0=No Change due 1=Permit Change Due
b_tax_exempt	tinyint	1	3	0	0= taxable; 1=tax exempt
b_open_drawer	tinyint	1	3	0	0=Do not open Drawer 1=Open Drawer
b_delete	tinyint	1	3	0	Required: 0= add, 1 = delete
b_debit	tinyint	1	3	0	Required: 0=debit, 1=credit, 2=Bad Check.
b_set_credit_limit	tinyint	1	3	0	0=Do not set limit 1=Set Credit Limit
f_print_endorse	tinyint	1	3	0	0
f_agency_type	tinyint	1	3	0	B=Bad Check, C=Campus, D=Catalogue Sales, F=Financial Aid, G=Gift Certificate, R=Revolving, S=Special Orders, X=Store Defined
f_agency_status	tinyint	1	3	0	Required: “0” = inactive, “1” = active
f_discount_type	tinyint	1	3	0	0=Off Retail 1=Above Cost
agency_id	int	4	10	0	Null
tm_stamp	timestamp	8	8		SQL timestamp

ASCII Delimited Format

Account_Type	char	1	Required: 'B' - Bad Check 'C' - Campus 'D' - Catalogue Sales 'F' - Financial Aid 'G' - Gift Certificate 'R' - Revolving 'S' - Special Orders 'X' - Store Defined
agency_number	char	25	Required: account code
agency_name	char	80	Account Description
address_1	char	80	Street Address
address_2	char	80	2^nd Line of Street Address
address_3	char	80	3^rd Line of Street Address
city	char	40	City for Account Address
state	char	4	State for Account Address
postal_code	char	20	Zip/Postal Code for Account Address
country	char	24	Country for Account Address
phone_1	char	16	1^st Phone for Account
phone_2	char	16	2^nd Phone for Account
phone_3	char	16	3^rd Phone for Account
tax_exempt_id	char	25	Tax Exempt ID
agency_comment	varchar	255	Comment on Account
tender_code	char	2	POS code of Tender linked to Account
discount_code	char	4	POS code of Discount linked to Account
agency_priority	int		Multi Account Priority number
change_limit	money		Maximum Amount of change permitted
b_change_due	int		0=No Change due 1=Permit Change Due
b_tax_exempt	int		0= taxable; 1=tax exempt
b_open_drawer	int		0=Do not open Drawer 1=Open Drawer
f_print_endorse	int		0
f_status	int		Required: 0=inactive, 1=active
b_debit	int		Required: 0=debit, 1=credit, 2=Bad Check
f_discount_type	int		0=Off Retail 1=Above Cost
b_set_credit_limit	int		0=Do not set limit 1=Set Credit Limit
dcc_array	varchar	300	6 Digit permissible DCC’s concatenate the DCC’s together maximum of 50 DCC’s
discount_array	varchar	150	6 Digit permissible DCC’s concatenate the DCC’s together
b_delete	tinyint	1	Required: “0”= add, “1” = delete

XML Definition

All attributes are CDATA #IMPLIED.

<?xml version="1.0"?>

<trn_ar_agency_imports>

<trn_ar_agency_import

acct_type="R"

acct_number="TestMeAcctAgency1"

acct_name="TestMeAcctAgency1 Name"

acct_addr1="5540 Shady Creek Ct"

acct_addr2="Apartment 16"

acct_addr3="PO Box 156"

city="Lincoln"

state="NE"

zip="68516"

country="USA"

phone1="402-423-6530"

phone2="402-333-7047"

phone3="402-697-0845"

acct_fed_id="0"

acct_cmt="Comment for TestMeAcctAgency1"

acct_tender_code="12"

acct_disc_code="0"

acct_priority="1"

change_limit=".0000"

fchange_due="0"

ftax_exempt="0"

fopen_drawer="0"

fprinter_endorse="0"

fstatus="1"

fdorc="2"

fdisc_type="0"

fset_cred_limit="0"

dcc_list="10 20 30 70"

dcc_disc="100100100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"

b_delete="0"

/>

</trn_ar_agency_imports>

PRISM Import Format for AR Agency Import

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

Record Size: 775 bytes

Field Name	Size	Description
Account Type	1	REQUIRED FIELD. 'B' - Bad Check 'C' - Campus 'D' - Catalogue Sales 'F' - Financial Aid 'G' - Gift Certificate 'R' - Revolving 'S' - Special Orders 'X' - Store Defined
Account Code	25	Required FIELD account code.
Account Name	30	Required FIELD Account Description.
Account Address #1	30	OPTIONAL FIELD Street Address.
Account Address #2	30	OPTIONAL FIELD 2^nd Line of Street Address.
Account Address #3	30	OPTIONAL FIELD 3^rd Line of Street Address.
Account City	15	OPTIONAL FIELD City for Account Address.
Account State	3	OPTIONAL FIELD State for Account Address.
Account Zip Code	10	OPTIONAL FIELD Zip/Postal Code for Account Address.
Account Country	10	OPTIONAL FIELD Country for Account Address.
Account Phone #1	15	OPTIONAL FIELD 1^st Phone for Account.
Account Phone #2	15	OPTIONAL FIELD 2^nd Phone for Account.
Account Phone #3	15	OPTIONAL FIELD 3^rd Phone for Account.
Account Tax Exempt	1	OPTIONAL FIELD 0= taxable; 1=tax exempt.
Account Comment	60	OPTIONAL FIELD Comment on Account.
Account Tender Code	2	OPTIONAL FIELD POS code of Tender linked to Account.
Change Due Flag	1	OPTIONAL FIELD 0=No Change due 1=Permit Change Due.
Change Limit	8	OPTIONAL FIELD Maximum Amount of change permitted.
Open Cash Drawer	1	OPTIONAL FIELD 0=Do not open Drawer 1=Open Drawer.
Printer Endorse	1	OPTIONAL FIELD. This field indicates to the POS system which tender endorsement to use: 0 = None 1 = Check 2 = Gift Certificate 3 = Travelers Check 4 = Credit Memo 7 = Full Sheet 9 = Half Sheet
Allowable DCC 6*50	300	OPTIONAL FIELD 6 Digit permissible DCC’s concatenate the DCC’s together maximum of 50 DCC’s
Discount % by DCC 3*50	150	OPTIONAL FIELD. This is a list of up to 50 discount percentages to automatically apply to the corresponding DCC's. These are stored with no decimal point. (xx.y = xxy).
Add/Delete Flag	1	REQUIRED FIELD. 'A' for ADD, 'D' for DELETE.
Account Status	1	REQUIRED FILED 0=inactive, 1=active.
Discount Code	3	OPTIONAL FIELD. This is the numeric discount code to use on the POS system for discounted DCC sales.
Account Federal ID	10	OPTIONAL FIELD. This is the federal tax id to be used if the account is tax exempt.
Debit or Credit	1	'D'ebit, 'C'redit, or 'B'ad check
Discount type	1	'0' = off retail, '1' = above cost
Credit Limit	1	'1' = Set account limit '0' = Unlimited
Priority	3	Numeric Multi-Account Priority Level 0 = do not select. Otherwise select in order of lowest to highest.
Line Feed	1	OPTIONAL FIELD. Use for record separators.

AR Agency Export

Exports any agencies that have been added, deleted or updated.

PrismCore Export_AR_Agency Table Names

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

Column	Data Type	Length	Prec	Scale	Description
agency_number	char	25	25		Account Code
agency_name	char	80	80		Account Description
address_1	char	80	80		Street Address
address_2	char	80	80		2nd Line of Street Address
address_3	char	80	80		3rd Line of Street Address
city	char	40	40		City for Account Address
state	char	4	4		State for Account Address
country	char	24	24		Country for Account Address
postal_code	char	20	20		Zip/Postal Code for Account Address
phone_1	char	16	16		1st Phone for Account
phone_2	char	16	16		2nd Phone for Account
phone_3	char	16	16		3rd Phone for Account
agency_comment	varchar	255	255		Comment on Account
tax_exempt_id	char	25	25		Tax Exempt ID
agency_priority	int	4	10	0	Multi Account Priority number
tender_code	char	2	2		POS code of Tender linked to Account
discount_code	char	4	4		POS code of Discount linked to Account
change_limit	money	8	19	4	Maximum Amount of change permitted
dcc_array	varchar	300	300		6 Digit permissible DCC’s concatenated together
discount_array	varchar	150	150		6 Digit permissible DCC’s concatenate the DCC’s together
b_change_due	tinyint	1	3	0	0=No Change due 1=Permit Change Due
b_tax_exempt	tinyint	1	3	0	0= taxable; 1=tax exempt
b_open_drawer	tinyint	1	3	0	0=Do not open Drawer 1=Open Drawer
b_delete	tinyint	1	3	0	0= add, 1 = delete
b_debit	tinyint	1	3	0	0=debit, 1=credit, 2=Bad Check
b_set_credit_limit	tinyint	1	3	0	0=Do not set limit 1=Set Credit Limit
f_print_endorse	tinyint	1	3	0	0
f_agency_type	tinyint	1	3	0	B=Bad Check, C=Campus, D=Catalogue Sales, F=Financial Aid, G=Gift Certificate, R=Revolving, S=Special Orders, X=Store Defined
f_agency_status	tinyint	1	3	0	0=inactive, 1=active
f_discount_type	tinyint	1	3	0	0=Off Retail 1=Above Cost
agency_id	int	4	10	0	AgencyID from Acct_Agency
tm_stamp	timestamp	8	8		SQL timestamp

ASCII Delimited Format

f_Agency_Type	char	1	'B' - Bad Check 'C' - Campus 'D' - Catalogue Sales 'F' - Financial Aid 'G' - Gift Certificate 'R' - Revolving 'S' - Special Orders 'X' - Store Defined
agency_number	char	25	Account Code
agency_name	char	80	Account Description
address_1	char	80	Street Address
address_2	char	80	2nd Line of Street Address
address_3	char	80	3rd Line of Street Address
city	char	40	City for Account Address
state	char	4	State for Account Address
country	char	24	Country for Account Address
postal_code	char	20	Zip/Postal Code for Account Address
phone_1	char	16	1st Phone for Account
phone_2	char	16	2nd Phone for Account
phone_3	char	16	3rd Phone for Account
b_tax_exempt	int	1	0=taxable; 1=tax exempt
agency_comment	varchar	255	Comment on Account
tender_code	char	2	POS code of Tender linked to Account
b_change_due	int	1	0=No Change due 1=Permit Change Due
change_limit	money	8	Maximum Amount of change permitted
b_open_drawer	int	1	0=Do not open Drawer 1=Open Drawer
f_print_endorse	int	1	0=No Endorse, 8=Prism POS Half Sheet
dcc_array	varchar	300	6 Digit permissible DCC’s concatenated together
discount_array	varchar	150	6 Digit permissible DCC’s concatenated together
b_delete	tinyint	1	0= add, 1 = delete
f_status	int	1	0=inactive, 1=active
discount_code	char	4	POS code of Discount linked to Account
tax_exempt_id	char	25	Tax Exempt ID
b_debit	int	1	0=debit, 1=credit, 2=Bad Check
f_discount_type	int	1	0=Off Retail 1=Above Cost
b_set_credit_limit	int	1	0=Do not set limit 1=Set Credit Limit
agency_priority	int	1	Multi Account Priority number

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_agency_exports>

<trn_ar_agency_export

f_agency_type="F"

agency_number="TestMeAcctAgencyCODEwes" agency_name="TestMeAcctAgencyNAMEwes"

address_1="5240 Shady Creek Ct."

address_2="Apartment 25"

address_3=""

city="Omaha"

state="NE"

country="US"

postal_code="68544"

phone_1="402-692-7777"

phone_2=""

phone_3=""

b_tax_exempt="0"

agency_comment="TestMeAcctAgencyCOMMENTwes"

tender_code="12"

b_change_due="0"

change_limit=".0000"

b_open_drawer="0"

f_print_endorse="0"

dcc_array="10 30" discount_array="000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"

b_delete="1"

f_agency_status="1"

discount_code="0"

tax_exempt_id=""

b_debit="C"

f_discount_type="0"

b_set_credit_limit="0"

agency_priority=""

/>

</trn_ar_agency_exports>

PRISM Export Format for AR Agency Export

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

Record Size: 775 bytes

Field Name	Size	Description
Account Type	1	Includes the following account types: 'B' - Bad Check 'C' - Campus 'D' - Catalogue Sales 'F' - Financial Aid 'G' - Gift Certificate 'R' - Revolving 'S' - Special Orders 'X' - Store Defined
Account Code	25	Account Code.
Account Name	30	Account Name.
Account Address #1	30	Account Address Line 1.
Account Address #2	30	Account Address Line 2.
Account Address #3	30	Account Address Line 3.
Account City	15	Account Code.
Account State	3	Account State.
Account Country	10	Account Country.
Account Zip Code	10	Account Zip Code.
Account Phone #1	15	Account Phone #1.
Account Phone #2	15	Account Phone #2.
Account Phone #3	15	Account Phone #3.
Account Tax Exempt	1	This is a 0/1 flag that indicates to the POS system that this account is tax exempt.
Account Comment	60	Comment.
Account Tender Code	2	This is the numeric tender code used in the POS system.
Change Limit	1	This is a 0/1 flag that indicates to the POS system if change is allowed.
Change Limit	8	This is a numeric field that indicates to the POS system the amount of change allowed.
Open Cash Drawer	1	This is a 0/1 flag that indicates to the POS system if the cash drawer should be opened for this tender.
Printer Endorse	1	This field indicates to the POS system which tender endorsement to use: 0 = None 1 = Check 2 = Gift Certificate 3 = Travelers Check 4 = Credit Memo 7 = Full Sheet 9 = Half Sheet
Allowable DCC 6*50	300	This is a list of up to 50 allowable DCC's for this account.
Discount % by DCC 3*50	150	This is a list of up to 50 discount percentages to automatically apply to the corresponding DCC's. These are stored with no decimal point. (xx.y = xxy).
Add/Delete Flag	1	'A' for ADD, 'D' for DELETE.
Account Status	1	This is a 0/1 flag used to indicate 0=Inactive or 1=Active
Discount Code	3	This is the numeric discount code to use on the POS system for discounted DCC sales.
Account Federal ID	10	This is the federal tax id to be used if the account is tax exempt.
Debit or Credit	1	'D'ebit, 'C'redit, or 'B'ad check
Discount type	1	'0' = off retail, '1' = above cost
Credit Limit	1	'1' = Set account limit '0' = Unlimited
priority	3	Numeric Multi-Account Priority Level 0 = do not select. Otherwise select in order of lowest to highest.
Line Feed	1	Used for record separators.

AR Receipt Export

Exports all mail order, and POS receipt totals that have an A/R invoice.

PrismCore Export_AR_Customer_Receipt Table Names

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

Column	Data Type	Length	Prec	Scale	Description
receipt_id	int	4	10	0	Index Key
store_id	int	4	10	0	POSID of Store
invoice_id	int	4	10	0	InvoiceID code
customer_acct	char	25	25		CustomerID
agency_number	char	25	25		Account Description
receipt_number	char	25	25		Receipt Number
cashier	char	30	30		Cashier Name
receipt_date	datetime	8	23	3	Date of Receipt
receipt_total	money	8	19	4	Total Amount of receipt
tax_total	money	8	19	4	Total Tax on receipt
tax_exempt_total	money	8	19	4	Tax Exempt amount on receipt
ship_total	money	8	19	4	Amount of Shipping on receipt
discount_total	money	8	19	4	Total Discount amount on receipt
tender_total	money	8	19	4	Amount tendered on receipt
status	tinyint	2	3	0	89 BUYBACK, 65 CANCELLED, 67 CHECK CASHING, 88 CLOSING (EX), 78 NO SALE, 73 PAY IN, 79 PAY OUT, 0 POST VOID/SUSPENDED, 86 POST VOIDED, 82 REFUND, 83 SALE or EXCHANGE, 66 SUSPENDED
tran_code	char	2	2		AS Account Sale, SA Sale, RT Refund, AR Account Refund
request_number	char	25	25		Requisition Number
tm_stamp	timestamp	8	8		SQL timestamp

ASCII Delimited Format

customer_acct	char	25	CustomerID
agency_number	char	25	Account Code
receipt_number	char	25	Receipt Number
cashier	char	30	Cashier Name
receipt_date	datetime		Date of Receipt
receipt_total	money		Total Amount of receipt
tax_total	money		Total Tax on receipt
tax_exempt_total	money		Tax Exempt amount on receipt
ship_total	money		Amount of Shipping on receipt
discount_total	money		Total Discount amount on receipt
tender_total	money		Amount tendered on receipt
status	int	2	89 BUYBACK, 65 CANCELLED, 67 CHECK CASHING, 88 CLOSING (EX), 78 NO SALE, 73 PAY IN, 79 PAY OUT, 0 POST VOID/SUSPENDED, 86 POST VOIDED, 82 REFUND, 83 SALE or EXCHANGE, 66 SUSPENDED
tran_code	char	2	AS Account Sale, SA Sale, RT Refund, AR Account Refund, PI PayIn, PO PayOut
request_number	char	25	Requisition Number
store_id	int		POSID of Store

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_rcpt_exports>

<trn_ar_rcpt_export

customer_acct="001"

agency_number="100"

receipt_number="RC-000047-5"

cashier="PrismCore Admin"

receipt_date="06/04/2007"

receipt_total="87.3000"

tax_total="4.3700"

tax_exempt_total=".0000"

ship_total=".0000"

discount_total=""

tender_total="91.6700"

status="83"

tran_code="AS"

request_number=""

store_id="2"

/>

</trn_ar_rcpt_exports>

PRISM Export Format for AR Receipt Export

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

Notes:

Although the receipt_number field is defined as having a maximum length of 25 characters in the ASCII formats listed above, the PRISM format for Receipt Number (Transaction Number) is only 10 characters max. The same applies for AR Receipt Detail Export, i.e., Transaction (Transaction Number).
Concerning the PRISM format, other fields may have a different maximum size as compared to their ASCII counterparts as well, including the cashier field, etc. Just be aware of this and exercise due caution when using the various formats.

Field	Description	Length	Condition
Customer	Customer Code	25
Account	Charge Account Code	25
Transaction Date	Transaction Date	8	MM/DD/YY
Receipt Number	Transaction Number	10
Cashier	Cashier Login Id	10
Merchandise	Merchandise Total	8	No Decimal
Non Merchandise	Non Merchandise Total	8	No Decimal
Item Discounts	Total Item Discounts	8	No Decimal
Transaction Disc	Total Transaction Discount	8	No Decimal
Total Taxes	Total Taxes Paid	8	No Decimal
Transaction Tot	Transaction Total (All Items Less Tax)	8	No Decimal
Tender Total	Total Amount Tendered	8	No Decimal
Change Total	Total Change	8	No Decimal
Transaction Time	Transaction Time (HHMM military format)	4	(HHMM)
Total Lines	Total Transaction Lines	4
Exchanged Amount	Merchandise Exchanged Amount	8	No Decimal
Tax Exempt Total	Tax Exempt Total	8	No Decimal
Trans Dsc Per	Transaction Discount percent	4	No Decimal
Item Discount	Item Discount amount	8	No Decimal
Payin Amount	Payin/Payout Amount	8	No Decimal
Charged Amount	Amount Charged to this Account	8	No Decimal
Tender Code #1	POS Tender Code #1	2
Tender Code #2	POS Tender Code #2	2
Tender Code #3	POS Tender Code #3	2
Tender Code #4	POS Tender Code #4	2
Tender Code #5	POS Tender Code #5	2
Tender Amt #1	Tender Amount #1	8	No Decimal
Tender Amt #2	Tender Amount #2	8	No Decimal
Tender Amt #3	Tender Amount #3	8	No Decimal
Tender Amt #4	Tender Amount #4	8	No Decimal
Tender Amt #5	Tender Amount #5	8	No Decimal
Change Due	Amount of Change Due	8	No Decimal
Store	POS Store Number	3
Register	POS Register Number	3
Trans Disc Code	POS Transaction Discount Code	3
Payin Code	POS Payin Code	3
Manual Tax	Manual Tax (1=yes,0=No)	1	(0 or 1)
Tax Exempt #	Tax Exempt Number	10
Type	Filler	1
Transaction Code	Type of Transaction	2	(SA = sale, RT = Return, PV = Post Void, PI = Payin, PO = Payout)
Apply Number	POS entered apply number for payment	20
Create Date	Date Transaction was created	8	MM/DD/YY
Items	Total Items on Transaction	4
Status	Status	1	(S = Sales, R = Returns, V = Post Void, I = Payin, O = Payout)
		311 Bytes

AR Receipt Detail Export

Exports all mail order and POS receipt details that have an A/R invoice.

PrismCore Export_AR_Customer_Receipt_Detail Table Names

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

Column	Data Type	Length	Prec	Scale	Description
receipt_dtl_id	int	4	10	0	Index Key
receipt_id	int	4	10	0	Null
invoice_detail_id	int	4	10	0	Link to Invoice Detail
store_id	int	4	10	0	POSID of Store
customer_acct	char	25	25		CustomerID
agency_number	char	25	25		Account Code
receipt_number	char	25	25		Receipt Number
receipt_date	datetime	8	23	3	Date of Receipt
cat_isbn	char	20	20		ISBN of Book
dcc_code	char	10	10		6 digit DCC code for item
description	char	128	128		Description of item
sku	int	4	10	0	SKU of item
quantity	int	4	10	0	Quantity of item
price	money	8	19	4	Price per unit of item
discount	money	8	19	4	Amount of discount for item
tax	money	8	19	4	Tax on item
tm_stamp	timestamp	8	8		SQL timestamp

ASCII Delimited Format

customer_acct	char	25	CustomerID
agency_number	char	25	Account Code
receipt_number	char	25	Receipt Number
receipt_date	datetime		Date of Receipt
cat_isbn	char	20	ISBN of Book
dcc_code	char	10	6 digit DCC code for item
description	char	28	Description of item
sku	int		SKU of item
quantity	int		Quantity of item
price	money		Price per unit of item
discount	money		Amount of discount for item
tax	money		Tax on item
store_id	int		POSID of Store

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_rcpt_dtl_exports>

<trn_ar_rcpt_dtl_export

customer_acct="001"

agency_number="100"

receipt_number="RC-000047-5"

receipt_date="06/04/2007"

cat_isbn="978-0-415-56426-7"

dcc_code="201010"

description="CAMPBELL / ESSENTIAL BIOLOGY WITH PHYSIOLOGY (W/CD )"

sku="10000045"

quantity="1"

price="59.0700"

discount=".0000"

tax="2.8200"

store_id="2"

/>

</trn_ar_rcpt_dtl_exports>

PRISM Format for AR Receipt Detail Export

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

Note: Although the receipt_number field is defined as having a maximum length of 25 characters in the ASCII formats listed above, the PRISM format for Transaction (Transaction Number) is only 10 characters max. The same applies for AR Receipt Export, i.e., Receipt Number (Transaction Number).

Field	Description	Length	Condition
Transaction	Transaction Number	10
Line Number	Transaction Line	10
Catalog/ISBN	Catalog ISBN Number	15
DCC Code	Item Department/Class/Category	6
Description	Item Description	25
Item SKU	Item SKU number	8
Quantity	Quantity Sold	6
Item Price	Item Price	8
Extended Price	Extended Price (Quantity * Item Price)	8
Item Discount	Item Discount Amount	8
Non Merch Amount	Non Merchandise Amount	8
Non Merch Code	POS Non Merchandise Code	3
Item Disc Code	POS Item Discount Code	3
Item Disc Rate	POS Item Discount Rate	8	No Decimal
Store	POS Store Number	3
Transaction Disc	Transaction Discount for this item	8	No Decimal
Customer	Customer Code	25
Account	Charge Account Code	25
Tax Amount	Tax Amount for this item	8	No Decimal
		195 Bytes

Page Comparison

Versions Compared

Old Version 1

New Version Current

Key

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

AR Customer Balance Import

ASCII Delimited Format

PrismCore Import_AR_Customer_Balance Table Names

XML Definition

PRISM Import Format for AR Customer Balance

AR Customer Balance Export

PrismCore Export_AR_Customer_Balance Table Names

ASCII Delimited Format

XML Definition

PRISM Export Format for AR Customer Balance

AR Agency Import

PrismCore Import_AR_Agency Table Names

ASCII Delimited Format

XML Definition

PRISM Import Format for AR Agency Import

AR Agency Export

PrismCore Export_AR_Agency Table Names

ASCII Delimited Format

XML Definition

PRISM Export Format for AR Agency Export

AR Receipt Export

PrismCore Export_AR_Customer_Receipt Table Names

ASCII Delimited Format

XML Definition

PRISM Export Format for AR Receipt Export

AR Receipt Detail Export

PrismCore Export_AR_Customer_Receipt_Detail Table Names

ASCII Delimited Format

XML Definition

PRISM Format for AR Receipt Detail Export