OracleFree referencePDF, Excel, Word exportJD Edwards EnterpriseOneAll JD Edwards EnterpriseOne tools

JD Edwards AP and AR Ledger Tables: F0411, F0413, F0414, F03B11, F03B13, F03B14

This reference lists keys and working columns for the JD Edwards EnterpriseOne 9.2 payables and receivables tables: F0411 vouchers, F0413 and F0414 payments, F03B11 invoices, F03B13 and F03B14 receipts, the Z1 batch input tables and the who's who contact tables. Use it to write aging, payment and contact queries that join correctly.

Download:

Table	Name	Key columns	Important columns	Batch input (Z) table	Notes
F0411	Accounts Payable Ledger	RPDOC, RPDCT, RPKCO, RPSFX, RPSFXE	RPAN8 supplier, RPPYE payee, RPDIVJ invoice date, RPDGJ G/L date, RPDDJ net due, RPAG gross, RPAAP open, RPPST pay status, RPICU batch, RPPOST posted code, RPCRCD, RPVINV supplier invoice, RPPO/RPPDCT/RPPKCO purchase order	F0411Z1	One row per voucher pay item. Open payables = RPAAP not 0.
F0411	RPPST pay status (UDC 00/PS)		A approved for payment, H hold pending approval, P paid in full, R retainage, % withholding applies, # payment in process		Sites can add their own hold codes to 00/PS; check the list on your system.
F0411	Amounts and dates		RPAG, RPAAP stored with 2 implied decimals; RPDIVJ, RPDGJ, RPDDJ are Julian CYYDDD		Foreign amounts in RPACR and RPFAP use the currency in RPCRCD.
F0411Z1	Voucher Transactions - Batch Upload	VLEDUS, VLEDBT, VLEDTN, VLEDLN	VLKCO, VLDOC, VLDCT, VLSFX, VLAN8, VLPYE, EDI control columns VLEDSP processed flag, VLEDTC action	Itself	VL prefix. Processed into F0411 and F0911 by the batch voucher processor.
F0413	Accounts Payable - Matching Document (payment header)	RMPYID	RMDOCM payment number, RMDCTM matching doc type, RMPYE payee, RMGLBA bank account, RMDMTJ payment date, RMPAAP payment amount, RMVDGJ void date, RMICU batch, RMISTP post status		One row per payment. RMPYID is an internal ID from next numbers.
F0414	Accounts Payable Matching Document Detail (payment detail)	RNPYID, RNRC5	RNDOC, RNDCT, RNKCO, RNSFX, RNSFXE voucher being paid, RNPAAP amount paid, RNADSA discount taken, RNPOST		Join F0413 on PYID, then F0411 on DOC, DCT, KCO, SFX, SFXE.
F0401	Supplier Master	A6AN8	A6APC G/L offset, A6TRAP payment terms, A6PYIN payment instrument, A6HDPY hold payment code, A6CRRP currency		Supplier name and address come from F0101 and F0116.
F03B11	Customer Ledger (AR invoices)	RPDOC, RPDCT, RPKCO, RPSFX	RPAN8 customer, RPDIVJ invoice date, RPDGJ G/L date, RPDDJ net due, RPAG gross, RPAAP open, RPPST pay status, RPICU, RPPOST, RPCRCD, RPSDOC/RPSDCT sales order, RPVR01, RPPYID	F03B11Z1	Same RP prefix as F0411 but a different table. No SFXE in the AR key.
F03B11Z1	Batch Invoices	VJEDUS, VJEDBT, VJEDTN, VJEDLN	VJDOC, VJDCT, VJKCO, VJSFX, VJAN8, VJDGJ, VJDIVJ	Itself	VJ prefix. Processed into F03B11 and F0911 by the batch invoice processor.
F03B13	Receipts Header	RYPYID	RYCKNU receipt number, RYAN8, RYPYR payor, RYDMTJ receipt date, RYDGJ G/L date, RYCKAM receipt amount, RYAAP unapplied, RYPOST, RYICU batch, RYCRCD		One row per receipt. RYAAP shows the unapplied amount.
F03B14	Receipts Detail	RZPYID, RZRC5	RZDOC, RZDCT, RZKCO, RZSFX invoice paid, RZPAAP amount, RZADSA discount taken, write-off and chargeback amounts, RZDGJ		Join F03B13 on PYID, then F03B11 on DOC, DCT, KCO, SFX.
F03012	Customer Master by Line of Business	AIAN8, AICO	AIARC G/L offset, AITRAR payment terms, AIACL credit limit, AIHDAR hold invoices, AIRYIN payment instrument, AICRCD		Company 00000 holds the default record when line of business is off.
F0111	Address Book - Who's Who	WWAN8, WWIDLN	WWALPH name, WWMLNM mailing name, WWGNNM given, WWSRNM surname, WWATTL title, WWTYC type code		WWIDLN 0 is the entity itself; contacts start at 1. Basis of a supplier or customer contact list.
F0115	Address Book - Contact Phone Numbers	WPAN8, WPIDLN, WPCNLN, WPRCK7	WPPHTP phone type, WPAR1 prefix, WPPH1 number		Join F0111 on AN8 and IDLN.
F01151	Electronic Address	EAAN8, EAIDLN, EARCK7	EAETP address type (E email), EAEMAL address, EAECLASS category		Join F0111 on AN8 and IDLN for contact emails.

From the 9.2 table definitions and the Accounts Payable guide for pay status values. Vendor contact list query: F0401 joined to F0111 on AN8, then left join F0115 and F01151 on AN8 and IDLN. Amounts are stored without decimals; dates are Julian CYYDDD.

Need an aging or contact query for your system?

Tell us your database and companies and we will send a tested AP or AR query built on these tables.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How the tables fit

On the payables side, each voucher pay item is a row in F0411, keyed by document number, type, company, pay item and pay item extension. A payment is one F0413 header (RMPYID) with one F0414 line per voucher pay item paid. On the receivables side, each invoice pay item is a row in F03B11, keyed by document, type, company and pay item; a receipt is one F03B13 header (RYPYID) with F03B14 lines per invoice applied. Both ledgers use the RP prefix, so always qualify the table name in SQL. Imports go through F0411Z1 (VL prefix) and F03B11Z1 (VJ prefix).

Open items and pay status

The open amount is RPAAP in both F0411 and F03B11; a fully paid item has RPAAP 0 and, in A/P, pay status P. A/P pay status codes come from UDC 00/PS: A approved for payment, H hold pending approval, P paid in full, R retainage, % withholding applies and # payment in process. Hold codes beyond H are site specific. For aging, use RPDDJ (net due) against your as-of date, and remember that posted status RPPOST tells you whether the G/L entry exists, not whether the item is paid.

Open AP: F0411 where RPAAP <> 0
Open AR: F03B11 where RPAAP <> 0
Unapplied cash: F03B13 where RYAAP <> 0

Worked example: who paid this invoice?

A customer disputes invoice 50231 RI in company 00100, pay item 001. Find it in F03B11 with RPDOC = 50231, RPDCT = 'RI', RPKCO = '00100'. Then select F03B14 where RZDOC, RZDCT, RZKCO and RZSFX match; each row gives RZPYID and the amount applied, discount and write-off. Join RZPYID to F03B13.RYPYID for the receipt number (RYCKNU), date (RYDMTJ) and payor. The same pattern works for A/P: F0411 to F0414 on document keys, then F0413 for the payment number and bank account.

Gotchas

F0411 includes RPSFXE in its primary key; F03B11 does not. Divide amounts by 100 for two decimal currencies, and by the right power of ten for others. A supplier contact list needs F0111 lines with WWIDLN greater than 0 for people; line 0 is the company itself. Phones and emails are child rows keyed by AN8, IDLN and a line number, so a contact can have several. Voided payments keep their F0413 row with a void date in RMVDGJ, so filter them out of cash reports.

Frequently Asked Questions

What is the F0411 table in JDE?

F0411 is the Accounts Payable Ledger. Each row is a voucher pay item, keyed by RPDOC, RPDCT, RPKCO, RPSFX and RPSFXE. It holds supplier, payee, invoice, G/L and due dates, gross and open amounts, pay status and batch information. Payments against it are in F0413 and F0414.

What is the F03B11 table in JDE?

F03B11 is the Customer Ledger for accounts receivable invoices, keyed by RPDOC, RPDCT, RPKCO and RPSFX. It stores customer, invoice and due dates, gross and open amounts, pay status and the related sales order. Receipts applied to it are in F03B13 (header) and F03B14 (detail).

How do I build a vendor contact list in JDE 9.2?

Start from F0401 for suppliers, join F0101 for the name, then F0111 on AN8 for who's who lines (WWIDLN above 0 for contacts). Left join F0115 for phones and F01151 for email addresses on AN8 and IDLN. Filter EAETP = 'E' for email addresses.

Which tables hold JDE payments and receipts?

A/P payments: F0413 header keyed by RMPYID and F0414 detail keyed by RNPYID and RNRC5. A/R receipts: F03B13 header keyed by RYPYID and F03B14 detail keyed by RZPYID and RZRC5. Detail rows carry the voucher or invoice keys, so you can join back to F0411 or F03B11.

If your AP and AR teams want to ask 'what is open for this supplier' or 'who paid this invoice' in plain English, answered from F0411 and F03B11 on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools