Customer Invoice/Credit Memo Upload(CSV) format
For information on procedures and formatting rules that apply to all KFS batch uploads on the administration menu, see Batch Upload Basics.
Data Dictionary Rules
1. The first row of the file MUST be the header fields as noted in the sample file. The first row is ignored when the file is uploaded into the system. If the header row is not present, the upload will produce errors. All actual data must be defined in row 2 and onward.
2. Columns 1 – 20 (A – T) are for document header information. The remaining columns are for document detail accounting lines.
3. A Customer Invoice or Customer Credit Memo must contain between 1 and n (n >=1) accounting detail lines. Each document is represented by 1 to n lines in the CSV file. In order to group these lines so the upload program can determine all lines belong to one document, a chargeGroupKey field has been created. This field is the first column in the file. This alpha numeric value can be used to group all detail lines belonging to a single Customer Invoice or Credit Memo document. This value must be unique from other grouping keys used by the other documents in one CSV file.
4. Values in columns 21 – 34 (U - AH) are used from all lines in a chargeGroupKey set for creating each invoice detail accounting line, including the first line where the document level information is also used.
5. customerNumber is a unique ID generated by KFS for a customer. It will take precedence by the customer invoice/credit memo batch job to be used to identify a customer. If the external department provides only customerNumberExternalID and customerSourceCode, but no KFS customerNumber in the csv file, then KFS will use the customerNumberExternalID and customerSourceCode to identify the customer. If the external department provides customerNumber as well as the combination of customerNumberExternalID and customerSourceCode, then the customerNumber will be used as the identifier. If the customerNumber is provided in the csv file, but does not exist in KFS, then that particular record will error out.
6. customerNumberExternalID is a unique ID generated by the source system for a customer and must be included in the CSV file in combination with CustomerSourceCode if the KFS customerNumber is not provided.
7. customerSourceCode: Sample values for this are “CTRL” and “HRBN”.Example: For a Customer Invoice document with three accounting detail lines, the same chargeGroupKey value must be used on all three lines and it must be unique from all other values in the CSV file. Columns 1 – 20 (A – T) define document header level information such as the explanation, customer number and type, etc. Since a single customer invoice/credit memo document can contain multiple detail lines, document header field values (i.e. columns 1 - 20) do not have to be repeated in subsequent lines. However, it is recommended to do so for consistency.
Customer Invoice/Credit Memo Upload(CSV) format
|
Column |
Name |
Type |
Length |
Reqd? |
Special Formatting |
Comments |
|
A |
chargeGroupKey |
varchar2 |
10 |
Y |
|
|
|
B |
Explanation |
varchar2 |
40 |
Y |
|
“Header” level information to display only on Statement description |
|
C |
billByChartOfAccountCode |
varchar2 |
2 |
Y |
|
|
|
D |
billedByOrganizationCode |
varchar2 |
Y |
|
|
|
|
E |
organizationInvoiceNumber |
varchar2 |
9 |
N |
|
Invoice number from source system |
|
F |
invoiceDueDate |
date |
10 |
N |
mm/dd/yyyy |
|
|
G |
customerNumber |
varchar2 |
9 |
Y* |
|
Please refer to Data Dictionary Rules above |
|
H |
customerNumberExternalID |
varchar2 |
9 |
Y* |
Please refer to Data Dictionary Rules above | |
|
J |
customerPurchaseOrderNumber |
varchar2 |
25 |
N |
|
Please refer to Data Dictionary Rules above |
|
K |
customerPurchaseOrderDate |
date |
10 |
N |
mm/dd/yyyy |
|
|
L |
invoiceHeaderText |
varchar2 |
120 |
Y |
|
|
|
M |
invoiceAttentionLineText |
varchar2 |
80 |
N |
|
|
|
N |
printInvoiceIndicator |
varchar2 |
1 |
Y |
|
List of all the options for the ‘Print Invoice Indicator’ in the Customer invoice document.Send to BILL Queue- B Do Not Print- NSend to PROC Queue-Q Send to USER Queue-U |
|
O |
customerBillToAddressIdentifier |
number |
7 |
Y |
|
Auto populate the Customer Primary address by default |
|
P |
customerShipToAddressIdentifier |
number |
7 |
N |
|
Leave all fields blank by default |
|
Q |
customerInvoiceDocumentNumber |
varchar2 |
14 |
Y |
|
MUST be specified only for Credit Memo against a KFS customer invoice document |
|
R |
noteText |
varchar2 |
800 |
N |
|
Fill in if a note needs to be added to the document. One note per line in a chargeGroupKey set. |
|
S |
batch-mimeTypeCode |
varchar2 |
40 |
N |
|
Fill in if a specific mimeTypeCode needs to be associated with an attachment file for this document. Note the file must exist on the server in the “staging” directory |
|
T |
batch-fileName |
varchar2 |
250 |
N |
|
Fill in if a file needs to be attached to the resulting document. One attachment file per line in a chargeGroupKey set. Note: The file must exist on the server in the “staging” directory |
|
U |
dtl-chartOfAccountsCode |
varchar2 |
2 |
Y |
|
|
|
V |
dtl-accountNumber |
varchar2 |
32 |
Y |
|
|
|
W |
dtl-subAccountNumber |
varchar2 |
5 |
N |
|
|
|
X |
dtl-financialObjectCode |
varchar2 |
4 |
Y |
|
|
|
Y |
dtl-financialSubObjectCode |
varchar2 |
6 |
N |
|
|
|
Z |
dtl-projectCode |
varchar2 |
10 |
N |
|
|
|
AA |
dtl-organizationReferenceId |
varchar2 |
6 |
N |
||
|
AB |
dtl-invoiceItemCode |
varchar2 |
Yes |
|||
|
AC |
dtl-invoiceItemQuantity |
number |
(11,2) |
Y |
||
|
AD |
dtl-invoiceItemDescription |
varchar2 |
400 |
Y |
400 |
|
|
AE |
dtl-invoiceItemServiceDate |
varchar2 |
10 |
N |
mm/dd/yyyy |
|
|
AF |
dlt-invoiceItemUnitOfMeasureCode |
varchar2 |
4 |
N |
|
|
|
AG |
dtl-invoiceItemUnitPrice |
number |
(19,4) |
Y |
|
MUST be negative if the upload is a Customer Credit Memo |
|
AH |
dtl-taxableIndicator |
varchar2 |
1 |
Y |
*Indicates a required field.
Capital Asset Modules