Deltek Expense supports the ability to import batch expenses, or "Credit Card Feeds." This is very useful if your company distributes company credit cards to your employees and you receive one or more statements detailing the expenses charged to that card. You can import the credit card statements as often as necessary - monthly, weekly, even daily. Your employees can select these imported expenses on expense reports where they can be detailed and accounted for.
Use the Batch Types screen to set up configuration information regarding batch expenses. This includes identifying default information, setting up starting and ending position information from file layouts, selecting a bill currency, determining file filters, and setting up ghost card information. Information set up in the screen will be used when importing credit card files. For more information, please see the documentation for the Batch Types screen.
The file that you receive from the credit card company has a distinct layout that identifies header and detail transaction information. Currently, the only credit card format that is accepted by Deltek Expense is from American Express. It is called the KR-1025 file.
The Amex KR-1025 Record 0 Mapping details header information for each file. The layout of the file contains the following information:
Field - This column displays the name of the header field.
Picture - This column displays the size of the picture
Location - This column details the location of the Field in the file.
Notes - This column displays any notes regarding the Field
|
Field |
Picture |
Location |
Length |
Notes |
|
Record Type |
X(01) |
1 |
1 |
0 = File Header |
|
Filler |
X(01) |
2 |
1 |
|
|
Business Unit Number |
X(09) |
3-11 |
9 |
|
|
Filler |
X(01) |
12 |
1 |
|
|
Recipient Number |
X(09) |
13-21 |
9 |
|
|
Filler |
X(01) |
22 |
1 |
|
|
Report Prefix |
X(02) |
23-24 |
2 |
|
|
Report Number |
X(05) |
25-29 |
5 |
|
|
Report Version |
X(03) |
30-32 |
3 |
|
|
Filler |
X(01) |
33 |
1 |
|
|
Report Version Date (CCYYMMDD) |
X(08) |
34-41 |
8 |
|
|
Filler |
X(01) |
42 |
1 |
|
|
Report Create Date (CCYYMMDD) |
X(08) |
43-50 |
8 |
|
|
Filler |
X(31) |
51-81 |
31 |
|
|
File Version Number |
X(03) |
82-84 |
3 |
|
|
Filler |
X(1862) |
85-1946 |
1862 |
|
The Amex KR-1025 Record 1 Common file layout identifies detail information for each expense. Each detail row's layout is the same with the following exceptions:
(*) - Redefine Area - The group of characters starting with 614 and going through 819 can be used for different things depending on the MIS industry code. For example, the fields may be used for one thing for Car Rental expenses and another thing for Lodging expenses. See details regarding this section in the table below.
|
Field |
Picture |
Location |
Length |
Notes |
|
Record Type |
X(01) |
1 |
1 |
1 = Detail Billing Data |
|
Requesting Control Account Number |
X(19) |
2-20 |
19 |
The account number requesting the report. |
|
Requesting Control Account Name |
X(35) |
21-55 |
35 |
The name associated with the account requesting the report. |
|
Billing Basic Control Acct Number |
X(19) |
56-74 |
19 |
Billed BCA for the billed account number. |
|
Billing Basic Control Account Name |
X(35) |
75-109 |
35 |
The name associated with the billed BCA for billed account number. |
|
FIPS Code |
X(04) |
110-113 |
4 |
Internal US Federal Agency grouping, |
|
Billing Account Number |
X(19) |
114-132 |
19 |
Billed Account Number - Can be CM or a Diverted Account. |
|
Cardmember Embossed Name |
X(26) |
133-158 |
26 |
Name of the card member incurring the charge. |
|
Employee ID |
X(10) |
159-168 |
10 |
A textual field assigned by the user. |
|
Cost Center |
X(10) |
169-178 |
10 |
A textual field assigned by the user. |
|
Universal Number |
X(25) |
179-203 |
25 |
A textual field assigned by the user. |
|
Social Security Number |
X(10) |
204-213 |
10 |
A textual field assigned by the user. |
|
Language Preference Code |
X(01) |
214 |
1 |
Space - US |
|
Corporate Identifier Number |
X(19) |
215-233 |
19 |
A number used to represent a corporate client. |
|
Supplier Reference Number |
X(11) |
234-244 |
11 |
Provided by the service establishment at the point of sale. |
|
Sign Indicator (+ or -) |
X(01) |
245 |
1 |
Indicates where amounts are positive or negative. Valid values are + and -. |
|
Billed Amount |
Decimal (15,0) |
246-260 |
15 |
The amount of the billed charge. |
|
Billed Tax Amount |
Decimal (15,0) |
261-275 |
15 |
The amount of the billed tax. |
|
Billed Currency Code |
X(03) |
276-278 |
3 |
The ISO code for the billed currency. |
|
Billed Decimal Place Number |
X(01) |
279 |
1 |
The number of decimal places needed to display currency. |
|
Local Charge Amount |
Decimal (15,0) |
280-294 |
15 |
The amount of the charge in local currency |
|
Local Tax Amount |
Decimal (15,0) |
295-309 |
15 |
The amount of the tax in local currency. |
|
Local Currency Code |
X(03) |
310-312 |
3 |
The ISO code for the local currency. |
|
Local Decimal Place Number |
X(01) |
313 |
1 |
The number of decimal places needed to display currency. |
|
Currency Exchange Rate |
Decimal (15,8) |
314-328 |
15 |
The exchange rate from the local currency to US Dollars. |
|
Transaction Type Code |
X(02) |
329-330 |
2 |
The type of charge or credit for a transaction. |
|
Financial Category Code |
X(01) |
331 |
1 |
Indicates financial category of transaction |
|
Account Type |
X(05) |
332-336 |
5 |
The type of account for this card member (CM, BTA, BTBA, ACB, FCB, CRCB) |
|
Originating Basic Control Account Number |
X(19) |
337-355 |
19 |
This will be the originating BCA for the originating CM account number if this is a diverted transaction. |
|
Originating CM Account Number |
X(19) |
356-374 |
19 |
This will be the originating CM account number for the actual charge if this is a diverted transaction. |
|
Transaction Number |
X(15) |
375-389 |
15 |
Sequence Number (375-381) |
|
Charge Date (CCYYMMDD format) |
X(08) |
390-397 |
8 |
Actual date on which the transaction was incurred. |
|
Business Process Date (CCYYMMDD format) |
X(08) |
398-405 |
8 |
The date on which the transaction was processed by American express. |
|
Bill Date (CCYYMMDD format) |
X(08) |
406-413 |
8 |
Billing Date or Cycle Cut Date. |
|
CM Reference Number |
X(17) |
414-430 |
17 |
The field that the CM at the point of sale. |
|
MIS Industry Code |
X(02) |
431-432 |
2 |
This is the MIS industry code for the transaction: |
|
ROC ID |
X(13) |
433-445 |
13 |
Uniquely identifies a record of charge. |
|
Charge Description Line 1 |
X(42) |
446-487 |
42 |
First line of description for the record of charge. |
|
Charge Description Line 2 |
X(42) |
488-529 |
42 |
Second line of description for the record of charge. |
|
Charge Description Line 3 |
X(42) |
530-571 |
42 |
Third line of description for the record of charge. |
|
Charge Description Line 4 |
X(42) |
572-613 |
42 |
Fourth line of description for the record of charge. |
|
Redefine Area (*) |
X(206) |
614-819 |
206 |
This area is redefined based on the MIS industry code. See Details on other sheets. |
|
SE Number |
X(10) |
820-829 |
10 |
The Establishment Services account number for the SE where the charge was incurred. |
|
SE Chain Code |
X(10) |
830-839 |
10 |
The parent company code for the Service Establishment where the charge was incurred. |
|
SE Name 1 |
X(40) |
840-879 |
40 |
The first SE name field. |
|
SE Name 2 |
X(40) |
880-919 |
40 |
Second SE name field. |
|
SE Street 1 |
X(40) |
920-959 |
40 |
First SE address line. |
|
SE Street 2 |
X(40) |
960-999 |
40 |
Second SE address line. |
|
SE City |
X(40) |
1000-1039 |
40 |
SE city name. |
|
SE State |
X(06) |
1040-1045 |
6 |
SE state code. |
|
SE Zip Code |
X(15) |
1046-1060 |
15 |
SE ZIP code. |
|
SE Country Name |
X(35) |
1061-1095 |
35 |
SE country name. |
|
SE Country Code |
X(03) |
1096-1098 |
3 |
The ISO country code. |
|
SE Corporate Status Code |
X(01) |
1099 |
1 |
Identifies the corporate status. |
|
SE Purchasing Card Code |
X(02) |
1100-1101 |
2 |
Defines the different Purchasing Card/Non-Purchasing Card SEs. |
|
SE Purchasing Card Owner Type Code |
X(02) |
1102-1103 |
2 |
Defines the minority status of the SE's owner. |
|
SIC Code |
X(04) |
1104-1107 |
4 |
The Standard Industry Code. |
|
SE Sales Tax Collected Flag |
X(01) |
1108 |
1 |
Indicates whether sales tax has been collected. |
|
Ship To Postal Code |
X(15) |
1109-1123 |
15 |
Postal code shipped to. |
|
Ship To City |
X(30) |
1124-1153 |
30 |
City name shipped to. |
|
Ship To Country * |
X(30) |
1154-1183 |
30 |
Country name shipped to. |
|
Ship To State |
X(06) |
1184-1189 |
6 |
State name shipped to. |
|
DDA Number |
X(22) |
1190-1121 |
22 |
Funds Access checking account number. |
|
Bank Routing Number |
X(04) |
1212-1215 |
4 |
Routing number for the bank. |
|
US CM ABA Number |
X(04) |
1216-1219 |
4 |
Number that identifies the domestic bank used. |
|
Spool Number * |
X(22) |
1220-1241 |
22 |
Internal number assigned by AMEX that identifies the order in which the transaction was spooled. |
|
Funds Access Log Date |
X(08) |
1242-1249 |
8 |
Date on which the transaction was logged by AMEX. |
|
Funds Access Log Time |
X(08) |
1250-1257 |
8 |
Time the transaction was logged by AMEX. |
|
Machine Number |
X(08) |
1258-1265 |
8 |
Unique number that identifies the ATM from which a transaction originated. |
|
Terminal Location |
X(40) |
1266-1305 |
40 |
Location of the ATM. |
|
Network Route and Transit Number |
X(10) |
1306-1315 |
10 |
|
|
Terminal Route and Transit Number |
X(10) |
1316-1325 |
10 |
|
|
Cash Batch Number |
X(06) |
1326-1331 |
6 |
Internal number assigned to identify type of transaction for cash transactions. |
|
MIS Sub-Industry Code |
X(03) |
1332-1334 |
3 |
11,51 - General |
|
VAT Tax Percent |
Decimal (5,3) |
1335-1339 |
5 |
Percentage of VAT tax collected. |
|
Capture Center Code |
X(1) |
1340 |
1 |
Need Codes. |
|
Mercator Key |
X(21) |
1341-1361 |
21 |
For Internal AMEX CPCIS use only. |
|
Transaction Fee Indicator |
X(3) |
1362-1364 |
3 |
001= Paper Tix 002=Refund 003=Exchange 004=E-Tix 005=Interactive Tix 006=Reconciliation Fee |
|
Filler |
X(582) |
1365-1946 |
582 |
Spaces |
The Redefine Area of the AMEX KF1025 file is used to compile specific information based on the type of expense. The purpose of the fields will vary depending on the expense. The Redefine Area begins at location 614 and ends at location 819. The layout of each field is detailed below.
The Amex KR1025 Record 1 Car Rental Mapping file layout identifies detail information for fields 614 through 819 as they relate to car rental expenses. The file contains the field name, picture information (which describes how long the field is and what type of field it is), the location of the field in the file, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Car Rental Date (CCYYMMDD format) |
X(08) |
614-621 |
8 |
Date on which the car was first rented. |
|
Car Rental Location |
X(30) |
622-651 |
30 |
Location (city/state) where the car was rented. |
|
Car Return Date (CCYYMMDD format) |
X(08) |
652-659 |
8 |
Date on which the car was returned. |
|
Car Return Location |
X(30) |
660-689 |
30 |
Location (city/state) where the car was returned. |
|
Car Renter Name |
X(35) |
690-724 |
35 |
Name of the individual renting the car. |
|
Car Rental Days |
Decimal (3,0) |
725-727 |
3 |
Number of days for which the car was rented. |
|
Car Rental Agreement Number |
X(13) |
728-740 |
13 |
Number identifying the car rental agreement. |
|
Filler |
X(79) |
741-819 |
79 |
Spaces |
The Amex KR1025 Record 1 Hotel Mapping file layout identifies detail information for fields 614 through 819 as they relate to hotel expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Hotel Description of Charge |
X(23) |
614-636 |
23 |
Description of hotel charge. |
|
Hotel Guest Name* |
X(35) |
637-671 |
35 |
Name of the guest staying at hotel. |
|
Hotel Arrival Date (CCYYMMDD format) |
X(08) |
672-679 |
8 |
Date checked into hotel. |
|
Hotel Depart Date (CCYYMMDD format) |
X(08) |
680-687 |
8 |
Date checked out of hotel. |
|
Hotel Stay Duration |
Decimal (3,0) |
688-690 |
3 |
Number of room nights at hotel (# of rooms * # of nights). |
|
Hotel Room Rate |
X(15) |
691-705 |
15 |
Room rate submitted by hotel for the stay. This field will be numeric if a numeric value is available. |
|
Hotel City |
X(30) |
706-735 |
30 |
Name of the hotel city. |
|
Hotel State |
X(06) |
736-741 |
6 |
The code for the hotel state |
|
Hotel Folio Number |
X(10) |
742-751 |
10 |
Internal Hotel Reference Number. |
|
Filler |
X(68) |
752-819 |
68 |
Spaces |
The Amex KR1025 Record 1 Telephone Mapping file layout identifies detail information for fields 614 through 819 as they relate to telephone expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Tele From City |
X(30) |
614-643 |
30 |
Name of the city from which call is made. |
|
Tele From State |
X(06) |
644-649 |
6 |
Code for the state from which call is made. |
|
Tele To Number |
X(15) |
650-664 |
15 |
The phone number placed. |
|
Tele Reference # |
X(08) |
665-672 |
8 |
The reference number for the phone call placed. |
|
Tele Time of Call |
X(04) |
673-676 |
4 |
Time of day the call was placed. |
|
Tele Date of Call (CCYYMMDD format) |
X(08) |
677-684 |
8 |
The CCYY of the date the call was placed. |
|
Tele Call Length |
X(04) |
685-688 |
4 |
The MMDD of the date the call was placed. |
|
Tele Time of Call (HHMM)* |
X(05) |
689-693 |
5 |
The length of the call placed (hhmm). |
|
Tele Length of Call (HHMMSS) * |
X(08) |
694-701 |
8 |
|
|
Filler |
X(118) |
702-819 |
118 |
Spaces |
The Amex KR1025 Record 1 Airline Mapping file layout identifies detail information for fields 614 through 819 as they relate to airline expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Air Departure Date (CCYYMMDD format) |
X(08) |
614-621 |
8 |
Date of departure on the first leg of the trip. |
|
Air Return Date |
|
652-659 |
8 |
Date of return on the first leg of the trip. |
|
Air Routing |
X(27) |
622-648 |
27 |
IATA travel point code for each leg of the trip (up to 9). |
|
Air Class of Service |
X(08) |
649-656 |
8 |
The code for the class of service for each leg of the trip (up to 8). |
|
Air Carrier Code |
X(16) |
657-672 |
16 |
The code for the carrier for each leg of the trip (up to 8). |
|
Air Ticket Issuer |
X(37) |
673-709 |
37 |
Name of the airline or travel agency issuing the ticket. |
|
Air Issuer City |
X(30) |
710-739 |
30 |
City name of the airline or travel agency issuing the ticket. |
|
Air Issuer State |
X(06) |
740-745 |
6 |
State code of the airline or travel agency issuing the ticket. |
|
Air Passenger Name |
X(35) |
746-780 |
35 |
Name of the traveler using the ticket. |
|
Air Agency Number |
X(08) |
781-788 |
8 |
The number of the airline or travel agency issuing the ticket. |
|
Air Ticket Number |
X(13) |
789-801 |
13 |
Ticket number. |
|
Air Routing additional details* |
X(18) |
802-819 |
18 |
|
The Amex KR1025 Record 1 Retail Mapping file layout identifies detail information for fields 614 through 819 as they relate to retail expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Department Name |
X(28) |
614-641 |
28 |
Name of Department where purchase was made. |
|
Bill Number |
X(10) |
642-651 |
10 |
Reference number provided by establishment. |
|
Cashier Terminal ID* |
X(10) |
652-661 |
10 |
ID number for cashier terminal where sale took place. |
|
Time of Purchase* |
X(05) |
662-666 |
5 |
HHMMS that purchase was made. |
|
Description Line 1 |
X(20) |
667-686 |
20 |
Description of transaction submitted by establishment. |
|
Description Line 2 |
X(20) |
687-706 |
20 |
Description of transaction submitted by establishment. |
|
Description Line 3 |
X(20) |
707-726 |
20 |
Description of transaction submitted by establishment. |
|
Description Line 4* |
X(20) |
727-746 |
20 |
Description of transaction submitted by establishment. |
|
Description Line 5* |
X(20) |
747-766 |
20 |
Description of transaction submitted by establishment. |
|
Description Line 6* |
X(20) |
767-786 |
20 |
Description of transaction submitted by establishment. |
|
Filler |
X(33) |
787-819 |
33 |
Spaces |
The Amex KR1025 Record 1 Restaurant Mapping file layout identifies detail information for fields 614 through 819 as they relate to restaurant expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Bill Number |
X(10) |
614-623 |
10 |
Reference number provided by establishment. |
|
Charge Description |
X(20) |
624-643 |
20 |
Description of transaction submitted by establishment. |
|
Charge Amount |
X(12) |
644-655 |
12 |
Amount of charge. |
|
Tip |
X(12) |
656-667 |
12 |
Amount of tip. |
|
Filler |
X(152) |
668-819 |
152 |
Spaces |
The Amex KR1025 Record 1 Rail Mapping file layout identifies detail information for fields 614 through 819 as they relate to rail expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Departure Location |
X(20) |
614-633 |
20 |
Departure City Code. |
|
Departure Date (CCYYMMDD format) |
X(08) |
614-621 |
8 |
Date of departure. |
|
Return Date |
652-659 |
8 |
Date of return. |
|
|
1st Leg Destination |
X(20) |
634-653 |
20 |
First Leg City Code. |
|
2nd Leg Destination |
X(20) |
654-673 |
20 |
Second Leg City Code. |
|
3rd Leg Destination |
X(20) |
674-693 |
20 |
Third Leg City Code. |
|
General Description Line 1 |
X(40) |
694-733 |
40 |
Description of transaction submitted by establishment. |
|
General Description Line 2 |
X(40) |
734-773 |
40 |
Description of transaction submitted by establishment. |
|
General Description Line 3* |
X(40) |
774-813 |
40 |
Description of transaction submitted by establishment. |
|
Filler |
X(06) |
814-819 |
6 |
Spaces |
The Amex KR1025 Record 1 Mail Order Mapping file layout identifies detail information for fields 614 through 819 as they relate to mail order expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Goods Description* |
X(30) |
614-643 |
30 |
Description of transaction submitted by establishment. |
|
Bill Number* |
X(09) |
644-652 |
9 |
Reference number provided by establishment. |
|
Extra Description Line 1* |
X(20) |
653-672 |
20 |
Description of transaction submitted by establishment. |
|
Extra Description Line 2* |
X(20) |
673-692 |
20 |
Description of transaction submitted by establishment. |
|
Extra Description Line 3* |
X(20) |
693-712 |
20 |
Description of transaction submitted by establishment. |
|
Extra Description Line 4* |
X(20) |
713-732 |
20 |
Description of transaction submitted by establishment. |
|
Extra Description Line 5* |
X(20) |
733-752 |
20 |
Description of transaction submitted by establishment. |
|
Handling Charge* |
X(06) |
753-758 |
6 |
Amount of shipping and handling charges. |
|
Filler |
X(61) |
759-819 |
61 |
Spaces |
The Amex KR1025 Record 1 Insurance Mapping file layout identifies detail information for fields 614 through 819 as they relate to insurance expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Premium Identification* |
X(07) |
614-621 |
7 |
|
|
Policy Name* |
X(20) |
622-642 |
20 |
|
|
Policy Number* |
X(12) |
643-655 |
12 |
|
|
Additional Policy Number* |
X(20) |
656-676 |
20 |
|
|
Policy Period* |
X(20) |
677-697 |
20 |
|
|
Stamp Duty* |
X(10) |
698-708 |
10 |
|
|
Commission* |
X(10) |
709-719 |
10 |
|
|
Filler |
X(100) |
720-819 |
100 |
|
The Amex KR1025 Record 1 Oil Mapping file layout identifies detail information for fields 614 through 819 as they relate to oil expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Purchase Description* |
X(28) |
614-641 |
28 |
Description of transaction submitted by establishment. |
|
Bill Number* |
X(10) |
642-651 |
10 |
Reference number provided by establishment. |
|
Filler |
X(168) |
652-819 |
168 |
Spaces |
The Amex KR1025 Record 1 Event Mapping file layout identifies detail information for fields 614 through 819 as they relate to event expenses. The file contains the field name, the picture location, the character location of the field, the length of each field, and any notes regarding the field.
|
Field |
Picture |
Location |
Length |
Notes |
|
Event Name* |
X(30) |
614-643 |
30 |
Name of event for which tickets were purchased. |
|
Event Date* |
X(8) |
644-651 |
8 |
Date of event. |
|
Ticket Price* |
X(10) |
652-661 |
10 |
Price per ticket for event. |
|
Number of Tickets* |
X(3) |
662-664 |
3 |
Number of tickets purchased. |
|
Bill Number* |
X(10) |
665-674 |
10 |
Reference number provided by establishment. |
|
Filler |
X(145) |
675-819 |
145 |
Spaces |
Once the file has been obtained from American Express, you can import it into Deltek Expense using the Maintain Expenses screen.
Use this screen to import expenses from a credit card feed, select existing records to maintain, and manually enter expenses. You can also use this screen to post these transactions so the employee can select and expense them on an expense report. You have the ability to both import expenses from a feed and manually enter them in the same batch. For more information, please see the documentation for the Maintain Expenses screen.
Once posted, the transactions entered in the Maintain Expenses screen are available for you to select as expenses on an expense report.
The list of Outstanding Expenses will be the first screen displayed once you select the Category on an expense report. The list will contain all outstanding expenses for the Category selected plus any outstanding expenses that have a Category of "Other." After selecting the outstanding expense, you will be taken through the wizard. Any fields that are required for the Expense Type selected that were not provided in the Maintain Expenses screen will be blank and you will be required to make a selection before continuing with the expense. Some fields will already be filled out you will not be able to change them. Once selected, that expense is removed from the list of Outstanding Expenses.