Home > The Back Office > CustomProjects > 3023_TRACSAGRISIntegration

3023_TRACSAGRISIntegration

Overview

THIS PROJECT IS IN PROGRESS.  THIS IS NOT A FINAL DOCUMENT, IT IS A WORKING DOCUMENT (PLEASE EXCUSE ALL TYPO'S AND FORMATTING).  WE USE THIS TOOL TO DOCUMENT PROJECTS, THIS PAGE WILL REMAIN WHEN THE PROJECT IS COMPLETE.  

 

PLEASE NOTE:  SINCE THIS APP IS IN BETA THE EXPORT FILE CREATED CONTAINS THE FOLLOWING WARNING:

"BETA PROJECT..do not use for production until project is approved.  (Once approved this line will be removed) "

 

The "3023_TRACSAGRISIntegration" is a custom application developed by The Back Office.  This app was developed at the request and direction of the customer listed below.

 

Cooperative Producers, Inc.

P.O. Box 1008

Hastings, NE 68902

308-380-1391

   

Project Contacts

Gary Brandt gbrandt@cpicoop.com
Dave Nall dnall@cpicoop.com
Bill Tiernan bill_tiernan@genpt.com
Bill Frommelt​ billf@accountinglink.net
Jackie Luety​ jackiel@accountinglink.net
Travis Jerke  travis.jerke@culturatech.com
Russ Shirley   

 

THIS IS A CUSTOM APP AND IS NOT SUPPORTED BY NAPA TRACS OR THE BACK OFFICE OR AS PART OF ANY SUPPORT PLAN.  IT IS THE CUSTOMERS SOLE RESPONSIBILITY TO PROOF THIS APP TO INSURE  IT IS WORKING TO THEIR SATISFACTION.

System Requirements

 

Settings

Edit section

This app allows the user to change settings to allow for changes in processes over time.  The follow settings are available by clicking on the "Edit Settings" link on the bottom of the screen.

Setting Comment
EXPORTPATH  
DELIMITER comma

File Layouts

Parameters

  • User interface will allow an unlimited date range to be selected for export
  • AGRIS will recognize and prevent any duplicate activity from being imported
  • Only Invoiced repair orders will be exported from NAPA TRACS
  • ​Detailed Item number information that corresponds to AGRIS Item numbers will be extracted
  • No Accounts Payable or Purchase Order transactions will be extracted from NAPA TRACS
  • No Payments will be extracted from NAPA TRACS
  • AGRIS will assume responsibility for verifying accuracy of figures transmitted by interface

TransactionList

  • The Transaction list file will be generated by this app for use in the 'AGRIS' system.

  • The file will be comma delimited (this can be changed in setup) 

  • The Transaction list file will populate fields in the ACRI0 and ACRI1 fields as described below. No information will transfer for the ACRI2 row type as these are comments only

  • Detail line item fields in ACRI0 will equal header item totals

  • An INI setting for the shop supplies and discounts part number will be created and a shop supplies line item will be added

    • Shop Supplies: 43409  ACRI0_ShopSupplyItemCode

    • Discounts: DISC   ACRI0_DiscountItemCode

      • When looking into the details it seems that when the discount is applied at the RO level the line item amounts are adjusted to reflect the discount.  This means we will not need to add a line item for the total discount amount.

  • Designation of CPI internal vehicle account for identification of calculations for fields AH6, AI6, Y7 and Z7, subject to Use Tax

    • "CPI" at beginning of Customer field in Edit Vehicle (see attached screenshot CPI internal vehicle ID in TRACS)

    • Number in Fleet Number field in Edit Vehicle (see attached screenshot CPI internal vehicle ID in TRACS)

    • The Customer is a member of the Customer Group (in Edit Customer) called "CPI Vehicles" (see attached screenshot CPI internal vehicle ID in TRACS 2)

      • We only need ONE way to identify internal use Invoices.  This method seems to be the most reliable.  It also would allow for an adjustment in TRACS and to reexport information.

      • 'InternalCustomerGroup' will be used to identify the "Internal Invoices".  Any Invoice that is assigned to a customer in this group will have the Sales Tax amount allocated as use tax.  All others will be allocated as sales tax.

  • A non-tax item will be treated as a 0% tax rate and $0 tax amount

  • Header rows may be included (Type column should be left blank on header row)

  • ALL RO's invoiced will be exported.  If field designated in TRACS for NameID is blank value will be supplied from ini. 

  • Note on Sales Tax Allocations:

    • Since we are allocating total sales tax collected in TRACS to State VS City AND also then down to the line item we will run into rounding issues.  To deal with this we are going to calc the State Sales Tax/Use Tax allocation for ACRI0 and plug the difference to City Sales tax/Use Tax.  For ACRI1 we will keep track of how much as been allocated and on the last item we are allocating to we will plug the remainder.

 

Revision Requests

11/13/2014: Requested changes listed below and highlighted in tables

  • ACRI0 layout, NameID and ShipToNameID fields: zero fill on left to 10 spaces
  • ACRI0 layout, TermsCode field: leave blank (null)
  • ACRI1 layout, QtyUOMCode and PRCUOMCode fields: leave blank (null)
  • Add ACRR0 to export (see ACRR0 table)

 

ACRI0

Cell Heading TRACS Field Default Comment Field Length
A6 Type n/a ACRI0
  • ACRI0 (hard coded)
5
B6 InvoiceLocationCode n/a INI=?
  • INI setting: ACRI0_InvoiceLocationCode
3
C6 InvoiceNumber n/a Blank
  • Leave blank (will be populated by AGRIS)
6
D6 NameID [CUSTOMER].[MEMBERSHIPNUM] INI=?
  • AGRIS customer number is located in Edit Customer>General tab>Membership Number field
    (see attached screenshot AGRIS Customer ID location in TRACS)
  • INI setting: ACRI0_NameID  (used if not supplied in TRACS)
  • CHANGE: Pad Left with zeroes to fill to 10 spaces
10
E6 DocumentType n/a INI=1
  • INI setting: ACRI0_DocumentType
1
F6 InvoiceDate [ROH].[INVOICEDATE] n/a
  • Date RO was Invoiced in NAPA TRACS
  • YYMMDD format
6
G6 LedgerDate [ROH].[INVOICEDATE] n/a
  • Date RO was Invoiced in NAPA TRACS
  • YYMMDD format
6
H6 DueDate n/a Blank
  • Leave blank (will be populated by AGRIS)
6
I6 ShipDate [ROH].[INVOICEDATE] n/a
  • Date RO was Invoiced in NAPA TRACS
  • YYMMDD format
6
J6 DiscountDate n/a Blank
  • Leave blank (will be populated by AGRIS)
6
K6 InvoiceAmount [ROH].[TOTAL] 0
  • Total of  invoiced RO, including sales tax
  • Round to 2 decimal places
13
L6 StateSalesTaxAmount Calc/Allocation 0
  • Total Sales Tax calculated in NAPA TRACS, allocated based on INI setting.
  • INI Setting: ACRI0_StateSalesTaxRate (ex .06)
10
M6 DiscountAmount n/a Blank
  • Leave blank (will be populated by AGRIS)
10
N6 InvoiceType n/a INI=TX
  • INI Setting: ACRI0_InvoiceType
2
O6 NameIDType n/a INI=C
  • INI Setting: ACRI0_NameIDType
1
P6 StateCounty n/a INI=?
  • INI Setting: ACRI0_StateCounty
5
Q6 TIK n/a INI=TRX
  • INI Setting: ACRI0_TIK
  • Ticket Creator
3
R6 EQP n/a INI=?
  • INI Setting: ACRI0_EQP
  • Equip/Facility​
3
S6 DEP n/a INI=?
  • INI Setting: ACRI0_DEP
  • Department
3
T6 ORG n/a INI=?
  • INI Setting: ACRI0_ORG
  • Origin location
3
U6 DST n/a INI=?
  • INI Setting: ACRI0_DST
  • Destination location
3
V6 OrderNumber [ROH].[ROID] n/a
  • Invoice number from NAPA TRACS
15
W6 LocationInfo n/a Blank
  • Blank (May be used for PO# if needed in future)
10
X6 SplitInfo n/a Blank
  • Blank (May be used for PO# if needed in future)
10
Y6 AvailableField1 n/a Blank
  • Blank (May be used for PO# if needed in future)
15
Z6 AvailableField2 n/a Blank
  • Blank (May be used for PO# if needed in future)
15
AA6 InvoiceDescription n/a Blank
  • Blank
25
AB6 TermsCode n/a INI=01
  • INI Setting: ACRI0_TermsCode
  • CHANGE: Blank
2
AC6 ShipToNameID [CUSTOMER].[MEMBERSHIPNUM] INI=?

Same as D6.

  • AGRIS customer number is located in Edit Customer>General tab>Membership Number field
    (see attached screenshot AGRIS Customer ID location in TRACS)
  • INI setting: ACRI0_NameID  (used if not supplied in TRACS)
  • CHANGE: Pad Left with zeroes to fill to 10 spaces
10
AD6 ShipperNameID n/a Blank
  • Blank
10
AE6 AgentBrokerID n/a Blank
  • Blank
10
AF6 FirstInvSet n/a Blank
  • Blank
6
AG6 CitySalesTaxAmount Calc/Allocation 0
  • Total Sales Tax calculated in NAPA TRACS, allocated based on INI setting.
  • INI Setting: ACRI0_CitySalesTaxRate (ex .06)
  • Round to 2 decimal places
10
AH6 StateUseTaxAmount Calc/Allocation 0
  • Total Sales Tax calculated in NAPA TRACS, allocated based on INI setting for internal vehicles). 
  • INI Setting: ACRI0_StateUseTaxRate (ex .06)
  • Round to 2 decimal places
10
AI6 CityUseTaxAmount Calc/Allocation 0
  • Total Sales Tax calculated in NAPA TRACS, allocated based on INI setting for internal vehicles). 
  • INI Setting: ACRI0_CityUseTaxRate (ex .06)
  • Round to 2 decimal places
10

ACRI1

Cell Heading TRACS Field Default Comment Field Length
A7       Type n/a ACRI1
  • ACRI1 (hard coded)
5
B7 UpdateType n/a INI=I
  • INI setting: ACRI1_UpdateType
1
C7 ItemLocationCode n/a INI=?
  • INI setting: ACRI1_ItemLocationCode
3
D7 ItemNumber [ITEM].[LOCATION] Blank
  • AGRIS Item number located in Edit Item>Location field
  • (See attached screenshot AGRIS item number in TRACS)
  • If LaborItem INI Setting ACRI1_LaborItemNumber
15
E7 DetailType n/a Ini=C
  • INI setting: ACRI1_DetailType
1
F7 Quantity [ROITEM].[QTY] 0 3 decimal places 12
G7 UnitPrice [ROITEM].[PRICE] 0 3 decimal places 12
H7 TotalPrice [ROITEM].[TOTAL] 0 2 decimal places 12
I7 DiscountRate n/a INI=0
  • INI setting: ACRI1_DiscountRate
8
J7 StateSalesTaxRate n/a INI=?
  • INI setting: ACRI0_StateSalesTaxRate
  • 4 decimal places
8
K7 OriginalQuantity [ROITEM].[QTY] 0
  • 3 decimal places
  • equal to value in F7
12
L7 UnitCost [ROITEM].[COST] 0
  • 5 decimal places
13
M7 BLReference   Blank
  • Blank
12
N7 ItemDescription1   Blank
  • Blank
25
O7 ExecID n/a Blank
  • Blank
6
P7 ActivityLocationCode n/a INI=?
  • INI setting: ACRI1_ActivityLocationCode
3
Q7 QtyUOMCode n/a INI=EACH
  • INI setting: ACRI1_QtyUOMCode
  • CHANGE: Blank
4
R7 PRCUOMCode n/a INI=EACH
  • INI setting: ACRI1_PRCUOMCode
  • CHANGE: Blank
4
S7 CitySalesTaxRate n/a INI=?
  • INI setting: ACRI0_CitySalesTaxRate
  • 4 decimal places
8
T7 StateUseTaxRate n/a INI=?
  • INI setting: ACRI0_StateUseTaxRate
  • (same as J7)- I built setting to allow them to be set different than sales tax
  • 4 decimal places
8
U7 CityUseTaxRate n/a INI=?
  • INI setting: ACRI0_CityUseTaxRate
  • (same as S7) - I built setting to allow them to be set different than sales tax
  • 4 decimal places
8
V7 BlankField1 n/a Blank
  • Blank
7
W7    ​ StateSalesTaxAmount     CalcAllocation             0
  • Prorated based on J7 Rate and total tax, by line item, customer invoice. 
  • The StateSalesTaxAmount in ACRI0 L6 will be allocated to ACRI1 lines marked taxable.
  • 2 decimal places
  • For NON CPI Internal Invoices Only..else Use Tax
10
X7     CitySalesTaxAmount     CalcAllocation      0
  • Prorated based on S7 Rate and total tax, by line item, customer invoice. 
  • The CitySalesTaxAmount in ACRI0 AG6 will be allocated to ACRI1 lines marked taxable.
  • 2 decimal places
  • For NON CPI Internal Invoices Only..else Use Tax
10
Y7 StateUseTaxAmount CalcAllocation    0
  • Prorated based on T7 Rate and total tax, by line item, customer invoice. 
  • The CitySalesTaxAmount in ACRI0 AH6 will be allocated to ACRI1 lines marked taxable.
  • 2 decimal places
  • For CPI Internal Invoices Only..else Sales Tax
10
Z7 CityUseTaxAmount CalcAllocation  0
  • Prorated based on U7 Rate and total tax, by line item, customer invoice. 
  • The CitySalesTaxAmount in ACRI0 AI6 will be allocated to ACRI1 lines marked taxable.
  • 2 decimal places
  • For CPI Internal Invoices Only..else Sales Tax
10
AA7 BlankField2 n/a Blank
  • Blank
1
AB7 LineItemNumber n/a Calc
  • 5 digit alphanumeric item number
  • First two characters identify line
  • Last 3 characters = 001
5
AC7 OrderLocationCode n/a Blank
  • Blank
3
AD7 OrderNumber n/a Blank
  • Blank
8
AE7 ProductCategory n/a Blank
  • Blank
2
AF7 IncludedInBlend n/a Blank
  • Blank
1
AG7 BlankField3 n/a Blank
  • Blank
1
AH7 StorageFacility n/a Blank
  • Blank
1
AI7 StorageBin n/a Blank
  • Blank
3
AJ7 ContractLocation n/a Blank
  • Blank
3
AK7 ContractNumber n/a Blank
  • Blank
9
AL7 GrainApplication n/a Blank
  • Blank
1
AM7 ItemDescription2 [ROITEM].[COST] Blank
  • Item Description from NAPA TRACS
50
AN7 GRNApplyNameID n/a Blank
  • Blank
1

 

ACRR0 (waiting on TRACS Enterprise to look into)

 

​NOTES:

  • No headings; to follow ACRI1 for each RO, if possible
  • Need to link RO and Payment
    • Option 1: Enter RO number in Note field [ARH].[NOTE] of payment posting and match to OrderNumber [ROH].[ROID]
      (ACRI0 field V=ACRR0 field AA)
    • Option 2: Enter a payment Ref. ID in the WO Reference field BEFORE changing the status to Invoice. 
      • This will require posting the payment first to obtain the Ref. ID [ARH].[ARID].
      • The WO Reference field is located on the Detail tab>Additional Information [ROH].[ROREF].

 

 

Cell Heading TRACS field Default Comment Field Length
A8 Type n/a ACRR0 ACRR0 (Hard coded) 5
B8 ApplyReceiveOnAcct n/a INI=1 ACRR0_ApplyReceiveOnAccount 1
C8 BankCode n/a INI=? INI setting: ACRR0_BankCode 2
D8 ReceiptLocation n/a INI=?

INI setting: ACRI0_InvoiceLocationCode

3
E8 ReceiptNumber n/a Blank Blank 6
F8 PaymentDate n/a Blank Blank 6
G8 AppliedDate [ARH].[TRXDATE] n/a
  • Date RO was Invoiced in NAPA TRACS
  • YYMMDD format
6
H8 NameID [CUSTOMER].[MEMBERSHIPNUM] n/a
  • AGRIS customer number is located in Edit Customer>General tab>Membership Number field
    (see attached screenshot AGRIS Customer ID location in TRACS)
  • INI setting: ACRI0_NameID  (used if not supplied in TRACS)
  • CHANGE: Pad Left with zeroes to fill to 10 spaces
10
I8 CashSource n/a INI=I INI setting: ACRRO_CashSource 1
J8 PaymentAmount [ARH].[TRXAMT] 0
  • Total of  invoiced RO, including sales tax
  • Round to 2 decimal places
13
K8 DiscountAppliled n/a Blank Blank 10
L8 InvoiceLocation n/a INI=?

INI setting: ACRI0_InvoiceLocationCode

3
M8 InvoiceLinePricing n/a Blank Blank 11
N8 CheckNumber [ARH].[CHECKNBR] INI=? Check number if possible, else ? 9
O8 PostmarkDate n/a Blank Blank 6
P8 PrepaymentType n/a Blank Blank 1
Q8 PrepaymentReference n/a Blank Blank 15
R8 ProtectedPrice n/a Blank Blank 13
S8 PrepaymentUnitAmount n/a Blank Blank 13
T8 PrepaymentQuantity n/a Blank Blank 13
U8 QuantityUOMCode n/a Blank Blank 4
V8 PriceCostUOMCode n/a Blank Blank 4
W8 SalesTax1 n/a Blank Blank 13
X8 SalesTax2 n/a Blank Blank 13
Y8 SalesTax3 n/a Blank Blank 13
Z8 SalesTax4 n/a Blank Blank 13
AA8 OrderNumber [ARH].[NOTE] n/a Match to Invoice number from NAPA TRACS  [ROH].[ROID] 15
Remaining fields left blank  

Downloads

Version Date Install File Name Comment
1.0.13 11/21/2014 3023_TRACSAGRISIntegrationV01.00.13
  • Adjustments based on customer request
    • Added left pad to 10 spaces on membership ID
    • Changed TermsCode, QtyUOMCodeno, and PRCUOMCode to be blank INI settings
1.0.12 08/06/2014 3023_TRACSAGRISIntegrationV01.00.12
  • Adjustments based on customer testing
1.0.11 07/22/2014 3023_TRACSAGRISIntegrationV01.00.11
  • Fix: Installer was not putting icon on desktop
  • Fix: Added "_log" to the name of the log file.
  • Fix: Resolved issue where sales and use tax amounts were not being allocated if No "InternalCustomerGroup' was assigned
  • Fix: Resolved rounding issues with sales and use tax allocation.
  • Fix: Added function for 'ItemDescription2" to remove any occurrence of the delimiter in the content of field before exporting to avoid corupting the file.
  • Fix: Adjusted length settings for status menu so "help" would be visible.
1.0.10 07/15/2014 3023_TRACSAGRISIntegrationV01.00.10
  • Internal release version for testing.  Project is ready for customer once approved by QA.
1.0.8 06/27/2014 3023_TRACSAGRISIntegrationV01.00.08
  • Internal release version for testing default file layout an validation settings.

Install Instructions

  1. Click and download the "Install File Name" listed above to download the install
  2. Click and Run the install file once downloaded.  Accept the license agreement and click next through all of the installers prompts.

How to Use

Setup

1. Double click on the icon for TRACS AGRIS Integration.

 

icon.PNG

2. The following window will appear.

 

app.PNG

3. Click on Edit Settings. The following window will appear.

 

edit settings window1.PNG

For each location, you will need to set up the associated settings. The list below includes all of the required Settings and the default values. 

  

Setting Value
DELIMITER ,
EXPORTPATH Include a \ at the end of the path
ACRI0_ShopSupplyItemCode 43409
ACRI0_DiscountItemCode DISC
InternalCustomerGroup CPI VEHICLES
ACRI0_InvoiceLocationCode  
ACRI0_NameID  
ACRI0_DocumentType 1
ACRI0_StateSalesTaxRate  
ACRI0_InvoiceType TX
ACRI0_NameIDType C
ACRI0_StateCounty PPPPP
ACRI0_TIK TRX
ACRI0_EXP Leave blank
ACRI0_DEP Leave blank
ACRI0_ORG Leave blank
ACRI0_DST Leave blank
ACRI0_TermsCode 01
ACRI0_CItySalesTaxRate  
ACRI0_StateUseTaxRate  
ACRI0_CityUseTaxRate  
ACRI1_UpdateType 1
ACRI1_ItemLocationCode  
ACRI1_LaborItemNumber 38
ACRI1_DetailType C
ACRI1_DiscountRate 0
ACRI1_ActivityLocationCode  
ACRI1_QtyUOMCode EACH
ACRI1_PRCUOMCode EACH

 

4. Enter the Values for the Settings which are unique to each location or the integration itself. Double click into the right column and enter the associated value. A definition of each Setting as well as any field restrictions are listed in the File Layout tables above.

5. Click Save to save the settings. The Settings will only need to be setup once. Thereafter, you may edit as needed.

6. Select the desired date range and click Run.

app.PNG

7. Two files will be exported to the path designated in the Settings file. One is an information log, the other is the actual extract which will then need to be imported into AGRIS. Each is date and time stamped. An image of two sample files is shown below.​    ​    

 

log.PNG    extract file.PNG

Last modified

Tags

Classifications

This page has no classifications.