ITAS to JDE Interface Process

The ITAS to JDE interface is run as a menu option from within an ITAS company. All invoices, credit & debit notes and journals in the company not yet transmitted to JDE will be processed.

Purchase invoices and reversals of PIs (i.e. DNs with an original reference of PI) will generate an accounts payable (A/P) interface file, sales invoices, debit notes and credit notes will generate an accounts receivable (A/R) interface file and journals will generate a general ledger (a ) interface file. The interface files will only be created if there is data to transmit.

Temporary Advices (TA) documents will only transmit the opening side of the transaction as the reversal is generated automatically in JDE.

There are several interface parameters and these are maintained in S01 (see screen snapshot below) Firstly to enable the interface, the ‘Operating interface’ option is set to ‘JDE Accounting’. The other parameters may be maintained by selecting View…JDE Interface parameters.

The interface file(s) are created in the Interface file folder (within the ITAS folder) as maintained in S01. The interface file is an ASCII text file named as Udddhhmmss.IJ2 where:

The extension IJ2 indicates ITAS to JDE stage 2 has been created successfully i.e. file exists completed for use. An extension of IJ1 indicates the interface procedure is still active or an interface procedure has crashed. The interface may not run while a file with extension IJ1 exists in the Interface path.

File Transmission

When the file(s) have been created they are sent to the JDE server using FTP, and the JDE side initiates using an FTP QUOTE command. The FTP parameters are maintained in S01. The FTP commands may be executed as a shelled script file or using system APIs. The script command is useful for problem solving as the script file is kept in the Interface path and may be examined and re-executed in command mode. The API method is advantageous in that success or failure can be communicated to the user whereas it cannot when using the shelled script file method.

Sample script file (Singapore Style) :

Open 111.222.333.444 itasjdelink ascii Put C:\Itas\Jde\U032141742.IJ2 jdeftplib/itasglgl.U032141742 QUOTE RCMD SBMJOB CMD(CALL PGM(J5599074) PARM(itasglsg jdeftplib U032141742 N)) JOBD(JYAPDCT1/ITASSGJOBD) USER(*JOBD) INLLIBL(*JOBD) OUTQ(*JOBD) quit

Sample script file (US Style) :

Open 111.222.333.444 itasjdelink ascii Put C:\Itas\Jde\U272093957.IJ2 uffrcv/stitasar.U032141742 QUOTE RCMD SBMJOB CMD(CALL PGM(J5599334) PARM(STITASAR UFFRCV U272093957)) JOBQ(BATCHCFG/INTERFACE7) INLLIBL(*JOBD) OUTQ(*JOBD) quit

Where:

A report of the interface and a script file (if used) will be stored in OPRINT, named as the interface file name.

The interface file itself will be stored in the interface path for as long as required.

The contents of the interface path may be viewed in the interface program by selecting Right Click…View interface files. The list may be reduced by entering part of file name or file extension or by file creation date. Double clicking the file name will open the file in Notepad

The JDE system IP address may be set to NO TRANSMIT in S01. In this case the interface file is created, but not transmitted to JDE. The is may be useful for testing purposes or if there is a network failure.

File Content

The key components of an accounting transaction in ITAS are:

These 6 items are on every record type.

The account code may be one of 3 account types:

The currency code is 2 or 3 characters e.g. USD

The amount is 20 characters using the EBCDIC signed negative overpunch format e.g. 21,345.70 is transmitted as 00000000000000213470 and

-21,345.70 is transmitted as 0000000000000021347}

The Document date is sent as CCYYMMDD e.g. 12th February 2005 is transmitted as 20050212.

The G/L date passed to JDE is the ITAS accounting period.

If the Document date is in the correct period then it's the document date, otherwise it's the 1st day of the accounting period. If it's ADJ, the G/L date is sent as the last day of the financial year.

The document reference is converted to an 8 digit document reference, whereby the document type is mapped to a 2 digit integer (see table below) and the first digit of the financial year is dropped e.g. PI0500223 becomes 00500223.

When recreating the ITAS document reference from the JDE document reference, the program will assumes the decade (1st digit of financial year) is the same as the current fin year, however it assumes the previous decade if:

Document type to integer mapping table

Number	Document type
00	PI
01	SI
02	CN
03	DN
04	CI
05	CP
06	DA
07	SA
08	JL
09	AC
10	JF
11	IJ
12	CM
13	TA
14	EJ
15	IT

ITAS Document type to DCT document type mapping table

ITAS document type	Document type DCT field
SI	YS
PI	YP
CN	YC
DN	YD
TA	YT
Others	YJ

The DCT field is YF for reversals of PIs (i.e. DNs with an original reference of PI)

Which interface and Record type

ITAS Document type	ITAS Account type	Interface type	Interface Record Type
PI, DN	Client	Accounts payable	04
PI, DN	Nominal, WIP	Accounts payable	09
SI, CN	Client	Accounts Receivable	03
SI, CN	Nominal, WIP	Accounts Receivable	09
Journals (JL, TA etc)	Nominal, Client, WIP	General ledger	09

Detail of Interface Records

Posn	Length	JDE field	Filled with
1	30	U0SSID Source system id	S01 maintained JDE source system ID base followed by A/R, A/P or G/L depending on the interface
31	4	U0RTYPRecord type	00
35	1	U0PROC Processed flag	Blank
36	30	U0EDTN Transaction no.	No. of records in the file
66	20	U0EDBT Batch no	S01 maintained batch number base for the interface followed by date and time in YYMMDDHHMMSS format
86	10	U0EDLN Line no.	0000000000
96	20	U0UDC1/2 Blank	Blank
116	8	U0DATE Creation date	Date in CCYYMMDD format
124	6	U0TIME Creation time	Time in HHMMSS format
130	4	U0SYSF System function	03 for A/R, 04 for A/P and 09 for G/L
134	10	U0#RCS No. of records in file	No. of records in the file

Posn	Length	JDE field	Filled with
1	30	U0SSID Source system id	S01 maintained JDE source system ID base followed by A/R
31	4	U0RTYP Record type	03
35	1	U0PROC Processed flag	Blank
36	30	U0EDTN Transaction no.	ITAS document reference
66	20	U0DBT Batch no	S01 maintained batch number base for accounts receivable followed by date and time in YYMMDDHHMMSS format
86	10	U0EDLN Line no.	Line no.
96	20	U0UDC1/2 Blank	Blank
116	3	U1EDTR Transaction type	I
*119	10	U1ANTP Trading partner source ID	S01 Maintained e.g. FERITASG
129	20	U1ANSS Source customer no.	ITAS Account reference
149	10	U1CO Company	S01 maintained JDE company code
159	20	U1ACR/U1AG Currency amount	Amount
179	8	U1DG G/L Date	ITAS accounting period as a date
187	8	U1D1 Invoice date	Document date
195	8	U1DD Due date	Value date
203	8	U1DDN Discount due date	Zeros
211	25	U1ADSC Discount available	Zeros
236	15	U1DOC Document no.	Document reference
251	5	U1DCT Document type	Mapped as per below table
256	5	U1SFX Pay item	Counter for client records for the document
*261	15	U1PO Number	Counter party reference from TRADE record
276	5	U1DCTO Order type	If number is non-blank then same as U1DCT otherwise blank
281	10	U1KCO Company	S01 maintained JDE company code
291	60	U1VR01 Reference	Vessel from TRADE record
*351	60	U1RMK Remark	List of contact numbers on the invoice. If Cotton then full 10 digits of 1st contract no
411	1	U1EDTC Transaction action	A
*412	41	U1VINV Invoice number	Client’s reference
*453	1	U1CRRM Mode of entry	D (domestic) if in company currency, otherwise F for foreign currency
454	5	T1CRCD Hook for correcting decimals	Currency code
*459	20	U1CRR Exchange rate	Blank
479	5	U1AC02 Category code 02	Blank
484	1		Blank
485	15	U1ATXA taxable  amount domestic	Vat T/O. Zero if vat rate is zero
500	2		Blank
502	15	U1STAM Tax domestic	Vat.
517	2		Blank
519	15	U1CTXA foreign taxable amount
524	2		Blank
536	15	U1CTAM Foreign tax amount
551	2		Blank
553	10	U1TXA1 Tax rate area	S01 maintained JDE tax explanation code followed by the document vat code
563	2		Blank
565	2	U1EXR1 Tax explanation code	S01 maintained JDE tax explanation code
567	1		Blank
568	3	U1PTC	If then first 3 of payment terms
571	4		Department Code

Posn	Length	JDE field	Filled with
1	30	U0SSID Source system id	S01 maintained JDE source system ID base followed by A/R
31	4	U0RTYP Record type	09
35	1	U0PROC Processed flag	Blank
36	30	U0EDTN Transaction no.	ITAS document reference
66	20	U0EDBT Batch no	S01 maintained batch number base for accounts receivable followed by date and time in YYMMDDHHMMSS format
86	10	U0EDLN Line no.	Line no.
96	20	U0UDC1/2 Blank	Blank
116	3	U3EDTR Transaction type	I
119	8	U3DG G/L date	ITAS accounting period as a date
127	10	U3CO Company	S01 maintained JDE company code
137	20	U3AA OR U3ACR  Amount	Amount
157	5	U3DCT Document type	Mapped as per below table
162	15	U3DOC Document no.	Document reference
177	50	U3EXA Explanation – alpha name	Client account title
227	40	T3ANI Account code broken out	ITAS account code
267	40	U3SBL Sub ledger	If N/L account set up as interco and the associated client’s EDI address is non blank – then associated client’s EDI address else blank IF then commodity code
307	5	U3SBLT Sub ledger type	If N/L account set up as interco and the associated client’s EDI address is non blank – then F else blank If then X
312	20	U3R2 Reference 2	Non Cotton 1st 6 of purchase contract no If Cotton then origin+destination. Origin=ctry/state of whse, Destination=Counterparty’s state for domestic and  ctry of the destination port on non-domestic
332	20	U3U Units	The units (MTs) on the WIP account control leg, blank on normal G/L legs
352	5	U3LT Ledger type	AA (domestic) if in company currency, AA for foreign currency as well
357	50	U3EXR Remark	Narrative
407	6	T3SUBS Hook for further breakdown of subs	Blank
413	1	U3CRRM Mode of entry	D (domestic) if in company currency, otherwise F for foreign currency
414	5	T3CRCD	Currency code
419	20	U3CRR Exchange rate	Blank
439	79	U3PO Purchase order no.	Counter party reference from TRADE record
518	47	T3JELN	Blank
565	50	U3AN8 Address no	Blank
615	29	UANI Build ANI from MCU, OBJ, SUB	Blank

Posn	Length	JDE field	Filled with
1	30	U0SSID Source system id	S01 maintained JDE source system ID base followed by A/P
31	4	U0RTYP Record type	04
35	1	U0PROC Processed flag	Blank
36	30	U0EDTN Transaction no.	ITAS document reference
66	20	U0EDBT Batch no	S01 maintained batch number base for accounts payable followed by date and time in YYMMDDHHMMSS format
86	10	U0EDLN Line no.	Line no.
96	20	U0UDC1/2 Blank	Blank
116	3	U2EDTR Transaction type	V
119	20	U2ANSS Source Vendor no	ITAS Account code
139	30	U2ANTP Trading partner source id	S01 Maintained e.g. FERITASG
169	15	U2DOC Document no.	ITAS document reference
184	10	U2CO Company	S01 maintained JDE company code
194	5	T2CRCD Currency code	Currency code
199	1	U2CRRM Mode of entry	D (domestic) if in company currency, otherwise F for foreign currency
200	8	U2DG G/l date	ITAS accounting period as a date
208	20	U2ACR/U2AG Currency amount	Amount (sign reversed)
228	8	U2D1 Voucher date	Document date
236	8	U2DD Net due date	Value date
244	5	U2DCT Document type	Mapped as per below table
249	5	U2SFX Pay item	Counter for client records for the document
254	35	U2VINV Vendor invoice no.	Client’s Reference The client’s reference will be prefixed with an R when a PI reversal (i.e. a DN with an original reference of PI)
289	15	U2PO Number	Counterparty reference from TRADE record
304	5	U2DCTO Order type	If number is non-blank then same as U2DCT otherwise blank
309	40	U2RMK Remark	List of contact numbers on the invoice
349	33	U2PST Pay Status	Blank
382	22	U2CRR Exchange rate	Blank
404	3	U2AC02 Category code 02	Blank
407	3
410	25	U2VR01 Reference	Vessel from TRADE record
435	2		Blank
437	15	U2ATXA Taxable amount (Domestic)	Vat T/O. Zero if vat rate is zero
452	1		Blank
453	15	U2STAM Tax (Domestic)	Vat T/O
468	2		Blank
470	15	U2CTXA Foreign taxable amount
485	2		Blank
487	15	U2CTAM Foreign tax amount
502	3		Blank
505	10	U2TXA1 Tax rate area	S01 maintained JDE tax explanation code followed by the document vat code
515	2		Blank
517	2	U2EXR1 Tax explanation code	S01 maintained JDE tax explanation code
519	8		Ship to AB
527	8		Approver
535	4		Department

Posn	Length	JDE field	Filled with
1	30	U0SSID Source system id	S01 maintained JDE source system ID base followed by A/P
31	4	U0RTYP Record type	09
35	1	U0PROC Processed flag	Blank
36	30	U0EDTN Transaction no.	ITAS document reference
66	20	U0EDBT Batch no	S01 maintained batch number base for accounts payable followed by date and time in YYMMDDHHMMSS format
86	10	U0EDLN Line no.	Line no.
96	20	U0UDC1/2 Blank	Blank
116	3	U3EDTR Transaction type	V
119	8	U3DG G/L date	ITAS accounting period as a date
127	10	U3CO Company	S01 maintained JDE company code
137	20	U3AA/U3ACR Amount	Amount
157	1	U3CRRM Mode of Entry	D (domestic) if in company currency, otherwise F for foreign currency
158	5	U3DCT Document type	Mapped as per below table
163	50	U3EXA Explanation – alpha name	Client account title
213	15	U3DOC Document No.	Document Reference
228	40	T3ANI Account code broken out	ITAS account code
268	40	U3SBL Sub ledger	If N/L account set up as interco and the associated client’s EDI address is non blank – then associated client’s EDI address else blank IF then commodity code
308	5	U3SBLT Sub ledger type	If N/L account set up as interco and the associated client’s EDI address is non blank – then F else blank If then X
313	15	U3PO/U3R1 No. / Reference 1	Blank
328	15	U3R2 Reference 2	1st 6 of purchase contract no
343	20	U3U Units	The units (MTs) on the WIP account control leg, blank on normal G/L legs
363	5	U3LT Ledger type	AA (domestic) if in company currency, AA for foreign currency as well
368	50	U3EXR Remark	Narrative
418	6	T3SUBS further breakdown of subs	Blank
424	5	T3CRCD Correct decimals	Currency code
429	20	U3CRR Exchange rate	Blank
449	79		Blank
528	37	T3JELN	Blank
565	50	U3AN8 Address no	Blank
615	29	UANI Combine cc.obj.sub	Blank

Posn	Length	JDE field	Filled with
1	30	U0SSID Source system id	S01 maintained JDE source system ID base followed by A/R
31	4	U0RTYP Record type	09
35	1	U0PROC Processed flag	Blank
36	30	U0EDTN Transaction no.	ITAS document reference
66	20	U0EDBT Batch no	S01 maintained batch number base for general ledger followed by date and time in YYMMDDHHMMSS format
86	10	U0EDLN Line no.	Line no.
96	20	U0UDC1/2 Blank	Blank
116	3	U3EDTR Transaction type	J
119	8	U3DG G/L date	ITAS accounting period as a date
127	10	U3CO Company	S01 maintained JDE company code
137	20	U3AA Amount	Amount
157	5	U3DCT Document type	Mapped as per below table
162	50	U3EXA Explanation	Same as U0EDBT; S01 maintained batch number base for accounts payable followed by date and time in YYMMDDHHMMSS format
212	40	U3ANI Account No.	ITAS account code
252	40	U3SBL Sub ledger	If N/L account set up as interco and the associated client’s EDI address is non blank – then associated client’s EDI address Else If USA then commodity code Else blank
292	5	U3SBLT Sub Ledger type	If N/L account set up as interco and the associated client’s EDI address is non blank – then F Else if then X Else blank
297	20	U3U Units	Currency code
317	5	U3LT Ledger type	AA (domestic) if in company currency, otherwise blank for foreign currency
322	50	U3EXR Explanation - Remark	Narrative
373	15	U3R2 Reference II	1st 6 of purchase contract no
387	1	U3EDTC Transaction action	A
388	15	U3PO Purchase order no.	Blank
403	1		Blank
404	1	U3RE Reversal flag	R if a TA
405	1		Blank
406	15	U3ACR currency amount foreign
421	2		Blank
423	1	U3CRRM Mode of entry
424	1		Blank
425	3	U3CRCD Currency code	Currency code
428	2		Blank
430	15	U3DOC	ITAS document reference

JDE to ITAS AR/AP Cash Interface

This is a tab delimited ASCII file with 2 record types:

All transactions with the same company code/document reference must appear together in the file and will be processed as a unit.

1	Record Type	H
2	Company code	JDE company code, S01 setting
3	Document reference	Unique Cash Document reference. Must be 8 characters See note 1 below
4	Interface Type	AR, AP or COST
5	Account	ITAS Client account code. Only used on AP and AR interface types
6	ITAS Invoice Reference	e.g. 00500001 (PI0500001 in ITAS) Only used on a cost interface type. In ITAS this is used to find the WIP accounts to post the amount to and the contract numbers to actualise the cost. The amount will be apportioned over the accounts on the invoice
7	Currency code	ISO code e,g, USD, GBP
8	Amount	For the accounts payable interface (The document reference starts with 04 i.e. a CP in ITAS) the sign will be reversed
9	Document date	YYYYMMDD
10	Value Date	YYYYMMDD
11	Narrative	maxium 30 charcaters
12	Expense Code	Used to override the default expense code e.g. PRE-FIN for AP/AR or BANK for a COST interface type. May be left blank in which case the nominated cash expense code will be used
13	Match flag	Only used on AP and AR interface types. Y Indicates this line to be matched.
14	Actualise flag	Only used on a cost interface type. Y indicates actualise the cost on the TRADE record
15	Cost type to actualise	e.g. BANK. Only used for a COST interface type when the actualise flag is set. If this field is blank and the actualise flag is set, the cost type to actualise will be derived from cost type entered in EXP for the expense code in field 12.

1	Record type	S
2	Company code	JDE company code, S01 setting
3	Document reference	Unique Document reference 8 chars e.g. e.g. 04500001
4	ITAS Invoice Reference to match	e.g. 00500001, see Note 1  (e.g. PI0500001 in ITAS)
5	Amount to match	For the accounts payable interface (The document reference starts with 04 i.e. a CP in ITAS) the sign will be reversed
6	Client account to match	Used in case the ITAS invoice reference is for more than one client

1	Record type	R
2	Company code	JDE company code, S01 setting
3	Document reference	Document reference being reversed.  8 chars e.g. e.g. 04500001 will reverse ITAS document reference CP0500001. The reversal document will have an EC document type generated in ITAS and will match with match flag 000.  Locates original document using SecRef (3Q13)

Notes

JDE to ITAS Client Balances Interface

This is an ASCII file of client summary data. There is one record per client/Currency code. The interface recognises this style of JDE to ITAS interface by the filename starting with CB, whereas the AP/AR cash interface files always start with AP or AR.

Field	Posn	Length	Extra information
Customer Code	1	10	Example: USABCMN.P
Credit Limit Amount	11	10	Number no decimal places or sign
Credit Limit Date	21	8	CCYYMMDD
Credit Limit Expiry Date	29	8	CCYYMMDD
A/R Balance Total	37	13	12 Numeric + trailing sign (2 decimal places implied)
Late Charge Balance	50	13	(example: -3,225.40 = 000000322540- )
A/R Current Total	63	13
A/R 1-30 Days Total	76	13
A/R 31-60 Days Total	89	13
A/R 61-90 Days Total	102	13
A/R 91-120 Days Total	115	13
A/R 121+ Days Total	128	13
Disputed Balance	141	13
Overdue Amount Days	154	17	16 Numeric + trailing sign (2 decimal places implied)
Currency Code	171	3	Example: USD
Constant "."	174	1	Character Period (.)

The data held on this file is secured on a dedicated table (chr34). Each receipt of this data overrides the previous entry.

The interface provides 2 reports utilising this data:

The JDE supplied account balances and account limits may be used in the INVOICING credit check if so nominated in S01.

Access after the interface

The import and export files may be viewed from the interface by doing right click…View Interface files.

After clicking GO, the files matching the selection will be displayed. Double clicking a filename will open it up in Notepad.

When the interface is complete the report is stored in OPRINT with the same name as the interface file name.

Part of the export process is to update 2 fields on the transaction:

This is used to ensure the transaction is not sent again (except on a resend) and provide visibility in ITAS as these columns can be added in SRC.