...
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.
...
/>
</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 |
|---|
...
------------------- ---------- ------ ------ ------ -----------------------------------
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
...
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_ |
...
cashier char 30 Cashier Name
...
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.
...
/>
</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
...
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 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
...
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 |
...
description char 28 Description of item
...
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.
...
</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 |
|---|
...
---------------- --------------------------------------- ------ -----------
Transaction Transaction Number 10
Line Number Transaction Line 10
Catalog/ISBN Catalog ISBN Number 15
...
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 |