- Created by Jessica Real, last modified on 02-18-2020
The exported file from Digital Dispatcher is being imported in Energy Trac. There are two available format that can be set based on how it will be used for import.
Below is the file format and mapping that has been exported through Dispatch process.
| Format | Description |
|---|---|
| cus_no | TM Site Customer Number |
| cus_name | Customer Name |
| site_no | TM Site No |
| site_desc | Site Description |
| site_addr | Site Address |
| site_city | Site City |
site_state | Site State |
| site_zip | Site Zip |
| site_lat | Site Latitude |
| site_long | Site Longitude |
| bill_addr | Billing Address |
| bill_city | Billing City |
| bill_state | Billing State |
| bill_zip | Billing Zip |
| phone | Customer Phone No |
| prod_no | Site Product No |
| prod_desc | Site Product Description |
| order_no | Call Entry > Ticket Assigned |
| order_dt | Call Entry > Call In Date |
| req_dt | Call Entry > Required Date |
| order_qty | Call Entry > Calculated/Desired Quantity |
| order_prc | Call Entry > Price |
| order_cmt | Call Entry > Comments |
| fill_method | Site Fill Method |
| tax_juris | Site Tax State ID |
| order_canc | If Dispatched = N If Cancel Dispatch = Y |
| strRouteNumber | Site > Route |
| intRouteSequence | Site > Sequence |
| dblPercentLeft | Order > Percent Left |
| Format | Description | Mapping | Remarks |
|---|---|---|---|
AccountNumber | TM Site Customer # (Ship to) | ||
| Phone # | Customer's Phone # (Phone 1) | N/A | |
| LocationCode | TM Site # | ||
| BillingName | A/R Bill To Name | ||
| BillingAddress1 | A/R Bill To Address 1 | this is address 1 of customer in origin this will be blank if address 1 is empty | |
| BillingAddress2 | A/R Bill To Address 1 Second Line | this is address 2 of customer in origin this will be blank if address 2 is empty | |
| BillingCity | A/R Bill To City | ||
| BillingState | A/R Bill To State | ||
| BillingZip | A/R Bill To Zip | ||
| DeliveryName | A/R Ship To Name | ||
| DeliveryAddress1 | TM Site Address first line | ||
| DeliveryAddress2 | TM Site Address Second Line | ||
| DeliveryCity | TM Site City | ||
| DeliveryState | TM Site State | ||
| DeliveryZip | TM Site Zip | ||
| Latitude | TM Site Lat | ||
| Longitude | TM Site Long | ||
| MainPhoneNumber | A/R Bill to Phone | Displays Customer's Phone No. | |
| DeliveryPhoneNumber | A/R Ship to Phone | Displays Customer's Phone No. | |
| WorkPhoneNumber | A/R Ship to Phone 2 | Blank | |
| SpecialInstructions1 | Site > Details tab > Instruction | Displays Site Details > Instructions | |
| SpecialInstructions2 | This should be the next 50 characters of the TM instructions 1 field. | ||
| TankSchedule | TM Site Capacity - Max % Full | If 0 Default %, Default the value to 100% if is it not > 0. Formula: (vwitm_deflt_percnt - dblEstimatedPercentLeft)/100 * dblTotalCapacity select vwitm_deflt_percnt, * from vwitmmst select dblEstimatedPercentLeft,dblTotalCapacity, * from tblTMSite where intSiteID = <intSiteID> | |
Should display 0 in CSV file if Tank Schedule < 0 | dblEstimatedPercentLeft is getting the Site > Details value. | ||
| TankSize | TM Site Capacity | ||
| NumberOfTanks | TM Site # of Tank Devices | ||
| FillLocation | TM Site Description | ||
| kFactor | TM Site Burn Rate | ||
| Route | TM Site Route | ||
| DivisionName | Location Name | Should display Location Name | |
| Comment | TM Site Comment | Displays Site Details tab > Comments | |
| MapCoord | N/A | N/A | Blank |
| DDNext | TM Site Next DD | ||
| DateReq | TM Site Fill Date or TM Site Will Call Req Date | ||
| DateOrdered | Entry Date, or Automatic Generate Date | ||
| GalOrdered | TM Site Call Entry Gals Requested. Calculated/Desired Qty. 0 if Gallons not specified. | ||
| TaxCode | Origin: Sales Tax = checked Tax State + Tax Locale 1 + Tax Locale 2 i21: Displays Tax State ID | ||
| ProductCode | TM Site Item # | ||
| UnitPrice | Contract price. Otherwise send the regular price. | ||
| PrepayAmount | Not Used. | N/A | Blank |
| DelInst | TM Will Call Comments + TM Site Description + TM Site Instructions | http://jira.irelyserver.com/browse/TM-1751 | |
| DelType | TM Fill Method | ||
| CreditCode | TM Site Terms | ||
| LastDeliveryDate | TM Site Last Delivery | ||
| LastDeliveryVolume | TM Site Last Gallons | ||
| ARBalance | A/R Bill To Total Balance | Customer Balance select vwcus_balance, * from vwcusmst where vwcus_key = '<customer number>' The arBalance field should be left blank ("") if not used | |
| DiscountType | TM Site Terms | Default to "D" for now | |
| DiscountRate | A/R Terms Disc % | N/A | Blank |
| DiscountDays | A/R Terms Disc Days | N/A | Blank |
| DiscountTxt | A/R Terms Description | N/A | Blank |
| DiscountCode | N/A | N/A | Blank |
| PlanPrice | A/R Contract Price. Blank if contract is not available. | Should display the selected contract price only. If no contract is selected or no contract for customer, display blank. Displays 0. | |
| PlanRemainingGal | A/R Contract Balance | Blank or 0 | |
| OffPlanPrice | Regular price (or secondary plan price if applicable) | Displays Call Entry Price | |
| TicketNumber | N/A. Displays Ticket Assigned | Displays Ticket Assigned in TM | |
| priceGal2 | Regular price. Note: This will be overrode by the value in OffPlanPrice if OffPlanPrice is sent. | ||
| addCharge1 | N/A | N/A | Blank |
| addChargeLabel1 | N/A | N/A | Blank |
| addChargeTax1 | N/A | N/A | Blank |
| addCharge2 | N/A - Need method for additional fees. | N/A | Blank |
| addChargeLabel2 | N/A | N/A | Blank |
| addChargeTax2 | N/A | N/A | Blank |
| balanceReflects | Always Y or N? | N/A | Blank |
| po_number | TM Site Recurring PO | ||
| divisionNumber | TM Site Location | Should display Location No | |
| CustomerRequest | TM Site Will Call = Y | Displays N if Fill Method is not equal to WIll Call | |
| Sequence | N/A | **Displays intContractDetailId of tblCTContractDetail Displays Blank even TM Sequence field has value | |
| usePreset | Yes when Will Call Quantity Requested is entered. | Displays N if Fill Method is not equal to WIll Call | |
| deliveryGroup | TM Site Fill Group | N/A | Blank |
| serialNumber | TM Tank Serial # | ||
| alwaysDiscount | TBD | N/A | Blank |
| emailAddr | A/R Contact E-mail addresses That are setup with Invoices for eMail Distribution | N/A | Blank |
| TruckNumber | N/A | N/A | Blank |
| ContractNum | A/R Contract # | N/A | Blank |
| TankPctFull | TM Site Estimated % Full | N/A | Blank. No tracking on what was the %full during the last delivery |
| addCharge3+A86A78:A88A78:A9A78:A111 | additional charges for additives etc | N/A | Blank |
| addChargeLabel3 | Text to print on delivery Ticket | N/A | Blank |
| addChargeTax3 | tax on additional charge | N/A | Blank |
| addCharge4 | additional charges for additives etc | N/A | Blank |
| addChargeLabel4 | Text to print on delivery Ticket | N/A | Blank |
| addChargeTax4 | tax on additional charge | N/A | Blank |
| addCharge5 | additional charges for additives etc | N/A | Blank |
| addChargeLabel5 | Text to print on delivery Ticket | N/A | Blank |
| addChargeTax5 | tax on additional charge | N/A | Blank |
| addCharge6 | additional charges for additives etc | N/A | Blank |
| addChargeLabel6 | Text to print on delivery Ticket | N/A | Blank |
| addChargeTax6 | tax on additional charge | N/A | Blank |
| addCharge7 | additional charges for additives etc | N/A | Blank |
| addChargeLabel7 | Text to print on delivery Ticket | N/A | Blank |
| addChargeTax7 | tax on additional charge | N/A | Blank |
| addCharge8 | additional charges for additives etc | N/A | Blank |
| addChargeLabel8 | Text to print on delivery Ticket | N/A | Blank |
| addChargeTax8 | tax on additional charge | N/A | Blank |
| RunOutDDay | degree day estimate for tank runout / formerly called degreeDayRunout | N/A | Blank |
| BringTicketBack | Message for driver to bring delivery ticket back to office | If blank will default to 'N' | |
| ownedTank | Message for driver that tank is owned by customer | If blank will default to 'N' | |
| hwAcct | Flag to indicate that this is a hot water account | if blank will to default to 'N' | |
| PAR | Price above Rack | Blank | |
| PriceAdjustment | Price Adjustment Flag | IF blank will default to 'N; | |
| ScheduleSubType | type of automatic delivery, julian, day-of-week | if blank will not be displayed | |
| addChargeCode1-DO NOT USE | associated with add charge | N/A | Blank |
| addChargeCode2-DO NOT USE | associated with add charge | N/A | Blank |
| addChargeCode3 | associated with add charge | N/A | Blank |
| addChargeCode4 | associated with add charge | N/A | Blank |
| addChargeCode5 | associated with add charge | N/A | Blank |
| addChargeCode6 | associated with add charge | N/A | Blank |
| addChargeCode7 | associated with add charge | N/A | Blank |
| addChargeCode8 | associated with add charge | N/A | Blank |
| addCharge1Prefix-DO NOT USE | determines special add charge cases. Blank or no prefix indicates a flat fee. 'N' for Negative value to be subtracted 'P' apply value as percent of unit price * vol. 'U' apply value as unit charge per unit vol. Note: P and U type Add charges, can be entered as negative values to produce a credit on the invoice. | N/A | Blank |
| addCharge2Prefix-DO NOT USE | determines special add charge cases. Blank or no prefix indicates a flat fee. 'N' for Negative value to be subtracted 'P' apply value as percent of unit price * vol. 'U' apply value as unit charge per unit vol. Note: P and U type Add charges, can be entered as negative values to produce a credit on the invoice. | N/A | Blank |
| addCharge3Prefix | determines special add charge cases. Blank or no prefix indicates a flat fee. 'N' for Negative value to be subtracted 'P' apply value as percent of unit price * vol. 'U' apply value as unit charge per unit vol. Note: P and U type Add charges, can be entered as negative values to produce a credit on the invoice. | N/A | Blank |
| addCharge4Prefix | determines special add charge cases. Blank or no prefix indicates a flat fee. 'N' for Negative value to be subtracted 'P' apply value as percent of unit price * vol. 'U' apply value as unit charge per unit vol. Note: P and U type Add charges, can be entered as negative values to produce a credit on the invoice. | N/A | Blank |
| addCharge5Prefix | determines special add charge cases. Blank or no prefix indicates a flat fee. 'N' for Negative value to be subtracted 'P' apply value as percent of unit price * vol. 'U' apply value as unit charge per unit vol. Note: P and U type Add charges, can be entered as negative values to produce a credit on the invoice. | N/A | Blank |
| addCharge6Prefix | determines special add charge cases. Blank or no prefix indicates a flat fee. 'N' for Negative value to be subtracted 'P' apply value as percent of unit price * vol. 'U' apply value as unit charge per unit vol. Note: P and U type Add charges, can be entered as negative values to produce a credit on the invoice. | N/A | Blank |
| addCharge7Prefix | determines special add charge cases. Blank or no prefix indicates a flat fee. 'N' for Negative value to be subtracted 'P' apply value as percent of unit price * vol. 'U' apply value as unit charge per unit vol. Note: P and U type Add charges, can be entered as negative values to produce a credit on the invoice. | N/A | Blank |
| addCharge8Prefix | determines special add charge cases. Blank or no prefix indicates a flat fee. 'N' for Negative value to be subtracted 'P' apply value as percent of unit price * vol. 'U' apply value as unit charge per unit vol. Note: P and U type Add charges, can be entered as negative values to produce a credit on the invoice. | N/A |
- No labels
Overview
Content Tools