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.

More:

Capital Asset Modules