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)

ASCII Delimited Format

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.

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)

ASCII Delimited Format

 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.

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            1^st Line of Vendor Address

address_2            char       80     80            2^nd Line of Vendor Address

address_3            char       80     80            3^rd 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           1^st Line of Vendor Address

address_2            char       80           2^nd Line of Vendor Address

address_3            char       80           3^rd 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&amp;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.