Developer ToolsFree generatorPDF, Excel, Word exportJD Edwards EnterpriseOneAll JD Edwards EnterpriseOne tools

JDE Received Not Vouchered Report SQL (F43121, F4311, F4301)

This generator writes SQL that lists JD Edwards EnterpriseOne receipts still waiting for a voucher, from F43121 receipt records joined to the PO detail F4311, PO header F4301 and supplier F0101. It adds supplier totals so AP and the controller can reconcile the received not vouchered account at month end.

Fill in

Business data schema

Order company (KCOO)

Branch/plant (MCU)

Receipt date from (Julian CYYDDD)

Receipt date to (Julian CYYDDD)

Supplier address number (0 = all)

Generated code

Download:

Open receipts by PO line

-- F43121 match type PRMATC = '1' are receipt records; PRAOPN is the amount not yet vouchered.
-- Amounts and PRLNID are stored without decimals: divide amounts by 100 for a 2-decimal
-- currency and line numbers by 1000.
SELECT r.PRAN8                 AS supplier,
       s.ABALPH                AS supplier_name,
       r.PRDOCO                AS po_number,
       r.PRDCTO                AS po_type,
       r.PRKCOO                AS po_company,
       r.PRLNID / 1000         AS po_line,
       d.PDLITM                AS item,
       d.PDDSC1                AS description,
       LTRIM(RTRIM(r.PRMCU))   AS branch_plant,
       h.PHTRDJ                AS order_date_julian,
       r.PRRCDJ                AS receipt_date_julian,
       r.PRUREC                AS units_received,
       r.PRAREC / 100          AS amount_received,
       r.PRAOPN / 100          AS amount_open,
       r.PRGLC                 AS gl_class
FROM   CRPDTA.F43121 r
JOIN   CRPDTA.F4311 d
       ON  d.PDDOCO = r.PRDOCO AND d.PDDCTO = r.PRDCTO
       AND d.PDKCOO = r.PRKCOO AND d.PDSFXO = r.PRSFXO
       AND d.PDLNID = r.PRLNID
JOIN   CRPDTA.F4301 h
       ON  h.PHDOCO = r.PRDOCO AND h.PHDCTO = r.PRDCTO
       AND h.PHKCOO = r.PRKCOO AND h.PHSFXO = r.PRSFXO
LEFT JOIN CRPDTA.F0101 s
       ON  s.ABAN8 = r.PRAN8
WHERE  r.PRMATC = '1'
  AND  r.PRAOPN <> 0
  AND  r.PRKCOO = '00200'
  AND  LTRIM(RTRIM(r.PRMCU)) = '30'
  AND  r.PRRCDJ BETWEEN 126001 AND 126295
  AND  (0 = 0 OR r.PRAN8 = 0)
ORDER  BY r.PRAN8, r.PRDOCO, r.PRLNID;

Totals by supplier

SELECT r.PRAN8              AS supplier,
       MAX(s.ABALPH)        AS supplier_name,
       COUNT(*)             AS open_receipt_lines,
       SUM(r.PRAOPN) / 100  AS amount_open
FROM   CRPDTA.F43121 r
LEFT JOIN CRPDTA.F0101 s ON s.ABAN8 = r.PRAN8
WHERE  r.PRMATC = '1'
  AND  r.PRAOPN <> 0
  AND  r.PRKCOO = '00200'
  AND  LTRIM(RTRIM(r.PRMCU)) = '30'
  AND  r.PRRCDJ BETWEEN 126001 AND 126295
  AND  (0 = 0 OR r.PRAN8 = 0)
GROUP  BY r.PRAN8
ORDER  BY SUM(r.PRAOPN) DESC;

-- Reconcile: the grand total should agree with the G/L balance of the
-- received not vouchered account set by AAI 4320 for these G/L classes,
-- allowing for timing and any manual entries to that account.

This approximates the standard received-not-vouchered reporting, it is not Oracle's report. PRAOPN is the open amount today, so a receipt date filter does not give a true as-of balance: vouchers matched after the period end have already reduced it. Verify the PRMATC values and currency decimals in your data dictionary, and add PRCRCD/foreign amounts if you receive in more than one currency.

Get an RNV reconciliation set up

Share your AAI 4320 setup and a month's numbers and we will build a repeatable RNV reconciliation query for your companies.

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

Was this tool useful?

Where this lives in EnterpriseOne

When you receive a PO line with P4312 (PO Receipts), JD Edwards writes a receipt record to F43121, the Purchase Order Receiver file, with match type PRMATC = 1, and posts a journal entry that debits inventory or expense and credits the received not vouchered account from AAI 4320. When AP matches a voucher to the receipt in P4314 (Voucher Match), a match type 2 record is written and the receipt's open amount PRAOPN and open units fall. So the open receipt rows are your RNV detail. F4311 is the PO detail table (PDDOCO, PDDCTO, PDKCOO, PDSFXO, PDLNID key) and F4301 is the PO header (PHDOCO, PHDCTO, PHKCOO, PHSFXO).

How to use it

Set the schema, the order company, branch/plant, a receipt date range in Julian and optionally one supplier. The first query lists every receipt line with an open amount, showing PO number, line, item, description, order date, receipt date, units and amounts. The second totals by supplier and sorts the largest first, which is usually where the investigation starts. Amounts are divided by 100 for a two decimal currency; change the divisor if your currency differs.

Branch/plant is a right-justified 12 character field, hence the trim
Line numbers have 3 implied decimals: 1000 is line 1.000
Join on all five key fields, including the order suffix, or rows will multiply

How to read the result

Old receipts with open amounts usually mean one of three things: the supplier has not invoiced, AP entered the invoice without matching it to the receipt (a standard voucher instead of a match), or the receipt was over-received or priced wrongly. Supplier totals that are negative point to vouchers that exceeded the receipt. Compare the grand total with the G/L balance of the 4320 account; differences come from manual journals to that account, receipts in other currencies, or vouchers matched after the period you are closing.

Version differences and gotchas

PRAOPN reflects the current state, not a historical balance, so if you run this after period end the vouchers entered since then have already reduced it. For a true as-of figure, add back match type 2 rows with a G/L date after your cutoff. Landed cost and voucher matches create further F43121 rows with other match types, so filter on match type rather than counting rows. In multi-currency setups, report the domestic and foreign amount columns separately instead of adding them together.

Frequently Asked Questions

Which table stores PO receipts in JD Edwards?

F43121, the Purchase Order Receiver file. Each receipt writes a row with match type 1, and each voucher match against it writes a row with match type 2. The open amount and open quantity on the receipt row show what is still to be vouchered. F4311 holds the PO lines and F4301 the PO headers.

What is the F4311 table in JDE?

F4311 is Purchase Order Detail, one row per PO line, keyed by order number PDDOCO, order type PDDCTO, order company PDKCOO, suffix PDSFXO and line number PDLNID. It holds the item, quantities, unit cost, line status (PDLTTR, PDNXTR) and dates. F4301 is the matching PO header with supplier and order date.

Which AAI is used for received not vouchered?

AAI 4320 defines the received not vouchered account, credited at receipt and debited when the voucher is matched. AAI 4310 defines the inventory or expense account debited at receipt. Both are resolved by company and G/L class code, so check them in P40950 for the G/L classes on your PO lines.

Why does my RNV query not match the G/L?

Typical causes are manual journal entries to the RNV account, receipts in foreign currency, vouchers entered without a receipt match, and timing: the query shows today's open amounts while the G/L balance is as of a period. Run both on the same date and reconcile the remaining items one by one.

If you want AP and the controller to ask JD Edwards what is received but not vouchered in plain English on your own servers, we can set that up over these same tables.

Talk to an Expert Explore More Free Tools