F0411

-- ============================================================
-- Table  : F0411 Accounts payable ledger: one row per voucher pay item with gross, open, discount, and tax amounts, due dates, pay status, and the PO keys behind three-way match.
-- Purpose: Column-selected read of F0411 — auto-generated from field metadata
-- Grain  : One row per RPKCO + RPDOC + RPDCT + RPSFX + RPSFXE
-- Notes  : Auto-generated skeleton. Julian dates (CYYDDD) are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210); padded business-unit/UDC keys are TRIMmed. Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  f.RPKCO AS "Document company that owns the voucher number sequence",
  f.RPDOC AS "Voucher document number",
  f.RPDCT AS "Voucher document type (PV standard voucher, PD debit memo, PL logged, etc.)",
  f.RPSFX AS "Pay item — vouchers split into pay items with independent terms and status",
  f.RPSFXE AS "Pay item extension used by prepayments and multi-tax-rate splits",
  f.RPAN8 AS "Supplier address book number",
  f.RPPYE AS "Payee address number actually receiving payment, when it differs from the supplier",
  CASE WHEN f.RPDIVJ IS NULL OR f.RPDIVJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RPDIVJ AS INT) DIV 1000, 1, 1), CAST(f.RPDIVJ AS INT) % 1000 - 1) END AS "Supplier's invoice date",  -- CYYDDD Julian → DATE
  CASE WHEN f.RPDDJ IS NULL OR f.RPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RPDDJ AS INT) DIV 1000, 1, 1), CAST(f.RPDDJ AS INT) % 1000 - 1) END AS "Net due date that drives the payment schedule and A/P aging",  -- CYYDDD Julian → DATE
  CASE WHEN f.RPDDNJ IS NULL OR f.RPDDNJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RPDDNJ AS INT) DIV 1000, 1, 1), CAST(f.RPDDNJ AS INT) % 1000 - 1) END AS "Discount due date — pay by this date to earn the discount",  -- CYYDDD Julian → DATE
  CASE WHEN f.RPDGJ IS NULL OR f.RPDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RPDGJ AS INT) DIV 1000, 1, 1), CAST(f.RPDGJ AS INT) % 1000 - 1) END AS "G/L date that places the voucher in a fiscal period",  -- CYYDDD Julian → DATE
  f.RPFY AS "Fiscal year of the G/L date",
  f.RPPN AS "Fiscal period of the G/L date",
  f.RPCO AS "Company the payable belongs to",
  f.RPICU AS "Batch number the voucher was entered under",
  f.RPICUT AS "Batch type (V vouchers, W manual w/match)",
  f.RPPST AS "Pay status (A approved, P paid, H held) — the open/closed indicator for A/P",
  f.RPAG / POWER(10, 2) AS "Gross voucher amount in domestic currency",  -- implied decimals: 2 (verify in F9210)
  f.RPAAP / POWER(10, 2) AS "Open amount still unpaid — the column A/P aging and cash forecasts sum",  -- implied decimals: 2 (verify in F9210)
  f.RPADSC / POWER(10, 2) AS "Discount available if paid by the discount due date",  -- implied decimals: 2 (verify in F9210)
  f.RPADSA / POWER(10, 2) AS "Discount actually taken at payment",  -- implied decimals: 2 (verify in F9210)
  f.RPATXA / POWER(10, 2) AS "Taxable portion of the voucher",  -- implied decimals: 2 (verify in F9210)
  f.RPSTAM / POWER(10, 2) AS "Tax amount on the voucher",  -- implied decimals: 2 (verify in F9210)
  f.RPTXA1 AS "Tax rate/area applied",
  f.RPCRRM AS "Currency mode — domestic or foreign entry",
  f.RPCRCD AS "Transaction currency of the voucher",
  f.RPCRR AS "Exchange rate used to convert foreign to domestic; implied decimals vary by setup",
  f.RPACR / POWER(10, 2) AS "Gross amount in the transaction (foreign) currency",  -- implied decimals: 2 (verify in F9210)
  f.RPFAP / POWER(10, 2) AS "Open amount in the transaction (foreign) currency",  -- implied decimals: 2 (verify in F9210)
  f.RPPOST AS "G/L posted code; 'D' when posted to F0911/F0902",
  TRIM(f.RPMCU) AS "Business unit on the voucher, used in AAI account resolution",
  f.RPPTC AS "Payment terms code that set due and discount dates",
  f.RPVOD AS "Void flag — voided vouchers remain as rows, so filter them out of open A/P",
  f.RPVINV AS "Supplier's invoice number — the duplicate-invoice check key",
  f.RPPKCO AS "Document company of the matched purchase order",
  TRIM(f.RPPO) AS "Purchase order number the voucher was matched to (three-way match link) — a character copy of the numeric PO key: CAST(NULLIF(TRIM(RPPO), '') AS BIGINT) before comparing to F4311.PDDOCO (the NULLIF guards blank values from failing the cast)",
  f.RPPDCT AS "Document type of the matched purchase order",
  f.RPLNID / POWER(10, 3) AS "PO line number the voucher pay item was matched against",  -- implied decimals: 3 (verify in F9210)
  f.RPSFXO AS "Order suffix of the matched PO",
  f.RPPYIN AS "Payment instrument for the payment (check, EFT, draft)"
  -- … plus 1 more column — full list in the Fields section above
FROM <catalog>.<schema_data>.f0411 f
WHERE
  f.RPKCO = '<KCO>'
  -- AND f.RPDOC = <DOC>
  -- AND f.RPDCT = '<DCT>'
  -- AND f.RPDGJ >= <DGJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.RPDGJ <= <DGJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.RPKCO;