AP Check Import
· Allows third-party applications to set a comment on checks generated in the PrismCore A/P module.
· AP Check Import does not import new checks into PrismCore.
PrismCore Import_AP_Checks Table Names
(Use this for interfaces that bypass WPConsole and go directly to prism_trn)
Column Data Type Length Prec Scale Description
------------------- ---------- ------ ------ ------ -----------------------------------
check_number char 25 25 Check Number
comment char 80 80 Comment to add
tm_stamp timestamp 8 8 SQL timestamp
ASCII Delimited Format
check_number char 25 Check Number should match to existing check
comment char 80 Comment that will be added to the check
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_ap_check_imports>
<trn_ap_check_import
check_number="CK-00000100"
check_comment="Comment for CK-00000100"
/>
</trn_ap_check_imports>
PRISM Format for AP Check Import
Note: Be careful while using PRISM formats, because some fields most likely be cut off, thus you may not get the result you want. For example, Check Number generally may contain up to 25 characters, but in PRISM format it can only contain up to 10 characters.
Name Length Description
------ ------ ---------------------------------------------------
Check Number 10 Check Number
Comment 70 Comment
AP Check Export
Exports check information to a third-party application. Generated by either the auto export or using a configured AP Export in PrismCore Admin.
PrismCore Export_AP_Checks Table Names
(Use this for interfaces that bypass WPConsole and go directly to prism_trn)
Column Data Type Length Prec Scale Description
------------------- ---------- ------ ------ ------ -----------------------------------
check_number char 25 25 Check Number
vendor_code char 10 10 Vendor Code
check_date datetime 8 23 3 Check Date
check_amount money 8 19 4 Amount of check
discount_amount money 8 19 4 Discount Amount
credit_amount money 8 19 4 Credit Amount
store_id int 4 10 0 0
check_id int 4 10 0 Check Header ID
currency_code char 3 3 Null
exch_rate decimal 9 10 8 Null
tm_stamp datetime 8 23 3 SQL timestamp
ASCII Delimited Format
check_number char 25 Check Number
vendor_code char 10 Vendor Code
check_date datetime Date of Check
check_amount money Amount on Check
discount_amount money Discount on Check
credit_amount money Credit Amount on Check
store_id int 0
currency_code char Blank Field
exch_rate decimal Blank Field
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_ap_check_exports>
<trn_ap_check_export
check_number="CK-00000044"
vendor_code="CAN002"
check_date="05/03/2006"
check_amount="47.2600"
discount_amount=".0000"
credit_amount=".0000"
store_id="0"
currency_code=""
exch_rate=""
/>
trn_ap_check_exports>
PRISM Format for AP Check Export
· All Dates are in MM/DD/YY format - (03/05/95).
· All amounts have embedded decimal points
· but no leading zeros - ( 10.23).
· All fields have a tilde (~) between them
· All records end in a CR/LF.
Name Length Description
------ ------ ---------------------------------------------------
Record 1 'H' AP Check Record - Header
Check Number 10 Check Number
Vendor Code 8 Vendor Number
Check Date 8 Check Date 99/99/99
Amount 10 Check Amount
Discount 10 Discount Amount
Credit 10 Credit Amount
AP Vendor Export
Exports vendor information for vendors that are used in check, invoice, credit or discount exports.
PrismCore Export_AP_Vendor Table Names
(Use this for interfaces that bypass WPConsole and go directly to prism_trn)
Column Data Type Length Prec Scale Description
------------------- ---------- ------ ------ ------ -----------------------------------
vendor_code char 10 10 Vendor Code
vendor_alias char 10 10 Vendor Alias
vendor_name char 100 100 Vendor Name
address_1 char 80 80 1st Line of Vendor Address
address_2 char 80 80 2nd Line of Vendor Address
address_3 char 80 80 3rd Line of Vendor Address
city char 40 40 Vendor City
state char 4 4 Vendor State
country char 24 24 Vendor Country
postal_code char 20 20 Vendor Postal Code
phone char 16 16 Vendor Phone
telex char 16 16 Null
fed_tax_id char 25 25 Federal Tax ID
vendor_id int 4 10 ID from VendorMaster Table
currency_code char 3 3 USD or CAD
tm_stamp timestamp 8 8 SQL timestamp
ASCII Delimited Format
vendor_code char 10 PrismCore Vendor Code
vendor_alias char 10 Alias from Vendor Maintenance
vendor_name char 100 Vendor Name
address_1 char 80 1st Line of Vendor Address
address_2 char 80 2nd Line of Vendor Address
address_3 char 80 3rd Line of Vendor Address
city char 40 Vendor City
state char 4 Vendor State
country char 24 Vendor Country
postal_code char 20 Vendor Postal Code
phone char 16 Vendor Phone
telex char 16 Blank Field
fed_tax_id char 25 Federal Tax ID
currency_code char 3 USD or CAD
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_ap_vendor_exports>
<trn_ap_vendor_export
vendor_code="AND001"
vendor_alias=""
vendor_name="Andy Inc"
address_1="1 SOUTHWOOD DRIVE"
address_2=""
address_3=""
city="LINCOLN"
state="NE"
country="USA"
postal_code="68502"
phone="402-421-5000"
telex=""
fed_tax_id=""
currency_code="USD"
/>
</trn_ap_vendor_exports>
PRISM Format for AP Vendor Export
· All Dates are in MM/DD/YY format - (03/05/95).
· All amounts have embedded decimal points
· but no leading zeros - ( 10.23).
· All fields have a tilde (~) between them
· All records end in a CR/LF.
Name Length Description
------ ------ ---------------------------------------------------
Record 1 'V' AP Vendor Record - Header
Vendor Number 8 Vendor Number
Vendor Name 25 Vendor Name
Address 1 25 Address 1
Address 2 25 Address 2
City 15 City
State 2 State
Postal Code 10 Postal Code
Phone 14 Phone
Fed ID 11 Fed ID
Telex 15 Telex
Alias 10 Alias
AP Invoice Export
Exports invoice totals by GL code to third-party applications.
PrismCore Export_AP_Invoice Table Names
(Use this for interfaces that bypass WPConsole and go directly to prism_trn)
Column Data Type Length Prec Scale Description
------------------- ---------- ------ ------ ------ ---------------------------------
invoice_id int 4 10 0 Index Key
check_number char 25 25 Null
invoice_number char 25 25 Invoice Number
vendor_code char 25 25 Vendor Code
voucher_number char 25 25 Voucher Number
invoice_date datetime 8 23 3 Invoice Date
due_date datetime 8 23 3 Invoice Due Date
tot_invoice_amount_paid money 8 19 4 Total Paid on Invoice
tot_invoice_amount money 8 19 4 0
tot_invoice_cost money 8 19 4 Total Cost on Invoice
tot_invoice_retail money 8 19 4 0
tot_invoice_fees money 8 19 4 Total Fees from ICS Invoice
tot_invoice_credit money 8 19 4 Total Credit from ICS Invoice
tot_invoice_GST money 8 19 4 GST Tax Amount from ICS Invoice
tot_invoice_PST money 8 19 4 PST Tax Amount from ICS Invoice
tot_invoice_payshort money 8 19 4 Pay Short Amount on Invoice
tot_invoice_frt_payshort money 8 19 4 Pay Short from Freight Invoice
tot_invoice_discount money 8 19 4 Total Invoice Discount
tot_invoice_credit_taken money 8 19 4 0
tot_invoice_discount_taken money 8 19 4 Discount Issued on Check
tot_invoice_freight money 8 19 4 Total Freight on the ICS Invoice
b_freight tinyint 1 3 0 0=Regular Invoice,1=Special Inv.
currency_code char 3 3 USD or CAD
exch_rate decimal 9 10 8 Exchange rate
tm_stamp timestamp 8 8 SQL timestamp
ASCII Delimited Format
Line number int 4 Links to detail Line Number
Invoice number char 25 Invoice Number
Vendor code char 25 Vendor Code
Voucher number char 25 Voucher Number
Invoice date datetime 8 Invoice Date
Due date datetime 8 Invoice Due Date
Amount Paid money 8 Total Paid on Invoice
Amount money 8 Will be 0
Cost money 8 Total Cost on Invoice
Retail money 8 Will be 0
Fees Amount money 8 Total Fees from ICS Invoice
Credit Amount money 8 Total Credit from ICS Invoice
GST Amount money 8 GST Tax Amount from ICS Invoice
PST Amount money 8 PST Tax Amount from ICS Invoice
Pay Short Amount money 8 Pay Short Amount on Invoice
Freight Pay Short Amt money 8 Pay Short from Freight Invoice
Invoice Discount money 8 Total Invoice Discount
Credit Taken money 8 Will be 0
Invoice Disc Taken money 8 Discount Issued on Check
Invoice Freight money 8 Total Freight on the ICS Invoice
Special Invoice 1 0=Regular Invoice,1=Special Invoice.
Currency code char 3 USD or CAD
Exchange Rate decimal 9 Exchange rate used
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_ap_invoice_exports>
<trn_ap_invoice_export
invoice_id="513"
invoice_number="74487448"
vendor_code="MCG001"
voucher_number="VCH-00000665"
invoice_date="10/09/2006"
due_date="10/09/2006"
tot_invoice_amount_paid="1132.2500"
tot_invoice_amount=".0000"
tot_invoice_cost="1132.2500"
tot_invoice_retail=".0000"
tot_invoice_fees="1.5000"
tot_invoice_credit=".0000"
tot_invoice_GST=".5000"
tot_invoice_PST="1.0000"
tot_invoice_payshort="1.5000"
tot_invoice_frt_payshort=".0000"
tot_invoice_discount=".0000"
tot_invoice_credit_taken=".0000"
tot_invoice_discount_taken=".0000"
tot_invoice_freight="9.5000"
b_freight="0"
currency_code="USD"
exch_rate=""
/>
</trn_ap_invoice_exports>
PRISM Format for AP Invoice Export
· All Dates are in MM/DD/YY format - (03/05/95).
· All amounts have embedded decimal points
· but no leading zeros - ( 10.23).
· All fields have a tilde (~) between them, except there are two tildes (~~) between
· invoice amount and retail amount.
· This is similar to PRISM Format for AP Credit Export.
· All records end in a CR/LF.
Name Length Description
------ ------ ---------------------------------------------------
Record 1 'I' AP Invoice Record - Header
GL Account 25 GL Account
GL Description 30 GL Description
Invoice Number 15 Invoice Number
Invoice Date 8 Invoice Date 99/99/99
Due Date 8 Due Date 99/99/99
PO Number 10 PO Number (not required)
Amount 10 Invoice Amount
Retail 10 Retail Amount
Voucher 10 Voucher Number
Store ID 3 Store ID
Freight Flag 1 Freight Flag (‘Y’ or ‘N’)
AP Invoice Detail Export
Exports invoice detail totals by GL code to third-party applications.
PrismCore Export_AP_Invoice_Detail Table Names
(Use this for interfaces that bypass WPConsole and go directly to prism_trn)
Column Data Type Length Prec Scale Description
------------------- ---------- ------ ------ ------ -----------------------------------
invoice_dtl_id int 4 10 0 Index Key
invoice_id int 4 10 0 Invoice ID of header record
invoice_number char 25 25 Invoice Number
voucher_number char 25 25 Voucher Number
check_number char 25 25 Null
gl_code char 25 25 GL code for record
gl_description char 80 80 GL Description
invoice_amount money 8 19 4 Item Invoice Amount
invoice_retail money 8 19 4 Item Retail Amount
invoice_paid money 8 19 4 Item Paid Amount
discount_amount money 8 19 4 Item Discount Amount
discount_taken money 8 19 4 Item Discount Taken
payshort_amount money 8 19 4 Item Pay/Short Amount
store_id int 4 10 0 POSID
tm_stamp timestamp 8 8 SQL timestamp
ASCII Delimited Format
Detail Line Number int 3 Line number of Item on Invoice
Line Number int 3 Line Number of Header Record
Invoice Number char 25 Invoice Number
Voucher Number char 25 Voucher Number
GL Code char 25 GL Code for Item
GL Description char 80 Description of GL Code
Invoice Amount money 8 Amount on the Invoice
Invoice Retail money 8 Retail Amount
Invoice Paid money 8 Paid Amount
Discount Amount money 8 Discount Allowed
Discount Taken money 8 Discount Taken
Pay Short Amount money 8 Pay Short Amount
Store Id int 4 POSID for 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_ap_invoice_exports>
<trn_ap_invoice_export
invoice_dtl_id="984"
invoice_id="513"
invoice_number="74487448"
voucher_number="VCH-00000665"
gl_code="10200"
gl_description="FREIGHT"
invoice_amount="9.5000"
invoice_retail=".0000"
invoice_paid="9.5000"
discount_amount=".0000"
discount_taken=".0000"
payshort_amount=".0000"
store_id="1"
/>
</trn_ap_invoice_exports>
PRISM Format for AP Invoice Detail Export
There is no PRISM Format for AP Invoice Detail Export.
AP Credit Export
Exports credit totals by GL code to third-party applications.
PrismCore Export_AP_Credit Table Names
(Use this for interfaces that bypass WPConsole and go directly to prism_trn)
Column Data Type Length Prec Scale Description
------------------- ---------- ------ ------ ------ -----------------------------------
memo_id char 25 25 Index Key
memo_number char 25 25 Credit Memo Number
vendor_code char 10 10 Vendor Code
voucher_number char 25 25 Voucher Number
check_number char 25 25 Check Number
vend_check_number char 25 25 Null
credit_date datetime 8 23 3 Date/Time of Credit
credit_amount money 8 19 4 Amount of Credit
credit_retail money 8 19 4 Retail Amount
tot_credit_used money 8 19 4 Amount of Credit Used
tot_check_amount money 8 19 4 Total Amount on Check
tot_credit_GST money 8 19 4 Total GST tax Amount
tot_credit_PST money 8 19 4 Total PST tax Amount
currency_code char 3 3 USD or CAD
exch_rate decimal 9 10 8 Exchange Rate
tm_stamp timestamp 8 8 SQL timestamp
ASCII Delimited Format
Line number int 3 Links to detail Line Number
Credit Memo number char 25 credit memo number
Vendor code char 10 vendor code
Voucher number char 25 voucher number
Check number char 25 check number
Credit date datetime 8 credit memo date
Amount money 8 amount of credit
Retail money 8 retail amount
Credit Used money 8 amount of credit used
Check amount money 8 total amount on check
GST money 8 Total GST tax amount
PST money 8 Total PST tax amount
Currency code char 3 USD or CAD
Exchange Rate decimal 9 Exchange rate used
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_ap_credit_exports>
<trn_ap_credit_export
memo_id="449"
memo_number="72639425"
vendor_code="S&S001"
voucher_number="VCH-00001963"
check_number=""
credit_date="06/11/2003"
tot_credit_amount="84.0000"
tot_credit_retail=".0000"
tot_credit_used="23.5300"
tot_check_amount=".0000"
tot_credit_GST=".0000"
tot_credit_PST=".0000"
currency_code="USD"
exch_rate=""
/>
</trn_ap_credit_exports>
PRISM Format for AP Credit Export
· All Dates are in MM/DD/YY format - (03/05/95).
· All amounts have embedded decimal points but no leading zeros - ( 10.23).
· All fields have a tilde (~) between them, except there are two tildes (~~) between request number and credit amount
· This is similar to PRISM Format for AP Invoice Export.
· All records end in a CR/LF.
Name Length Description
------ ------ ---------------------------------------------------
Record 1 'C' AP Credit Record - Header
GL Account 25 GL Account
GL Description 30 GL Description
Memo Number 15 Credit Memo Number
Memo Date 8 Credit Memo Date 99/99/99
Request Number 10 Credit Request Number (not required)
Amount 10 Credit Amount
Retail 10 Retail Amount
Voucher 10 Voucher Number
Store ID 3 Store ID
AP Credit Detail Export
Exports credit detail totals by GL code to third-party applications.
PrismCore Export_AP_Credit_Detail Table Names
(Use this for interfaces that bypass WPConsole and go directly to prism_trn)
Column Data Type Length Prec Scale Description
------------------- ---------- ------ ------ ------ -----------------------------------
memo_dtl_id int 4 10 0 Index Key
memo_id int 4 10 0 Credit ID of header record
memo_number char 25 25 Credit Number
check_number char 25 25 Check Number
gl_code char 25 25 GL Code
gl_description char 80 80 GL Description
credit_amount money 8 19 4 Credit Amount
credit_retail money 8 19 4 Credit Retail Amount
credit_used money 8 19 4 Credit Used
store_id int 4 10 0 POSID
tm_stamp timestamp 8 8 SQL timestamp
ASCII Delimited Format
Detail Line Number int 3 Line number of Item on Credit
Line Number int 3 Line Number of Header Record
Memo Number char 25 Credit Number
GL Code char 25 GL Code for item
GL Description char 80 Description of GL code
Credit Amount money 8 Credit Amount
Credit Retail money 8 Credit Retail Amount
Credit Paid money 8 Credit Used
Store Id int 4 POSID for 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_ap_credit_exports>
<trn_ap_credit_export
memo_dtl_id="734"
memo_id="449"
memo_number="72639425"
gl_code="92500"
gl_description="Inventory"
credit_amount="84.0000"
credit_retail="140.0000"
credit_used="23.5300"
store_id="12"
/>
</trn_ap_credit_exports>
PRISM Format for AP Credit Detail Export
There is no PRISM Format for AP Credit Detail Export.
AP Discount Export
Exports discount totals by GL code to third-party applications
PrismCore Export_AP_Discount Table Names
(Use this for interfaces that bypass WPConsole and go directly to prism_trn)
Column Data Type Length Prec Scale Description
------------------- ---------- ------ ------ ------ -----------------------------------
invoice_number char 25 25
gl_code char 25 25
gl_description char 80 80
invoice_date datetime 8 23 3
due_date datetime 8 23 3
discount_amount money 8 19 4
store_id int 4 10 0
b_freight tinyint 1 3 0
currency_code char 3 3 USD or CAD
exch_rate decimal 9 10 8
tm_stamp timestamp 8 8 SQL timestamp
ASCII Formats
Not defined at this time, call R&D if needed.
XML Definition
Not defined at this time, call R&D if needed.
PRISM Format for AP Check Export
Not defined at this time, call R&D if needed.