F03B11

-- ============================================================
-- Table  : F03B11 Customer ledger: one row per invoice pay item carrying gross, open, discount, and tax amounts plus the dates and status codes that drive A/R aging and collections.
-- Purpose: Column-selected read of F03B11 — auto-generated from field metadata
-- Grain  : One row per RPDOC + RPDCT + RPKCO + RPSFX
-- 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.RPDOC AS "Invoice document number",
  f.RPDCT AS "Invoice document type (RI standard invoice, RM credit memo, RU unapplied cash, etc.)",
  f.RPKCO AS "Document company that owns the invoice number sequence",
  f.RPSFX AS "Pay item — one invoice can split into multiple pay items with their own terms",
  f.RPAN8 AS "Customer address book number",
  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 invoice in a fiscal period",  -- CYYDDD Julian → DATE
  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 "Invoice date printed on the document; aging can run on this or the G/L date",  -- CYYDDD Julian → DATE
  f.RPICUT AS "Batch type of the batch that created the row (IB invoices, G journal entries)",
  f.RPICU AS "Batch number the invoice was entered under",
  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 receivable belongs to",
  f.RPPYR AS "Payor address number expected to pay the invoice",
  f.RPPOST AS "G/L posted code; 'D' means the invoice has posted to F0911/F0902",
  f.RPPST AS "Pay status (A approved, P paid, H held); the core open/closed indicator",
  f.RPAG / POWER(10, 2) AS "Gross invoice amount in domestic currency",  -- implied decimals: 2 (verify in F9210)
  f.RPAAP / POWER(10, 2) AS "Open amount still unpaid — the column A/R aging sums",  -- implied decimals: 2 (verify in F9210)
  f.RPADSC / POWER(10, 2) AS "Discount offered to the customer if paid by the discount due date",  -- implied decimals: 2 (verify in F9210)
  f.RPADSA / POWER(10, 2) AS "Discount actually taken when the invoice was paid",  -- implied decimals: 2 (verify in F9210)
  f.RPATXA / POWER(10, 2) AS "Taxable portion of the invoice amount",  -- implied decimals: 2 (verify in F9210)
  f.RPSTAM / POWER(10, 2) AS "Tax amount on the invoice",  -- implied decimals: 2 (verify in F9210)
  f.RPBCRC AS "Base (domestic) currency of the company",
  f.RPCRRM AS "Currency mode — whether the invoice was entered in domestic or foreign currency",
  f.RPCRCD AS "Transaction currency of the invoice",
  f.RPCRR AS "Exchange rate used to convert foreign to domestic amounts; implied decimals vary by setup",
  f.RPACR / POWER(10, 2) AS "Gross invoice 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.RPTXA1 AS "Tax rate/area applied to the invoice",
  TRIM(f.RPMCU) AS "Business unit for the receivable, used in AAI account resolution",
  f.RPPTC AS "Payment terms code that set the due and discount dates",
  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 used for aging buckets",  -- 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
  f.RPRSCO AS "Write-off reason code when part of the balance was written off",
  f.RPODOC AS "Original document number when this row derives from another document (e.g. chargeback source)",
  f.RPODCT AS "Original document type paired with the original document number",
  f.RPVINV AS "Customer's reference/invoice number as supplied on the remittance",
  f.RPDCTO AS "Sales order type when the invoice came from sales update",
  f.RPSDOC AS "Sales order number that generated the invoice via R42800",
  f.RPSDCT AS "Document type of the originating sales order",
  f.RPVOD AS "Void flag — voided invoices remain as rows, so filter them out of aging"
  -- … plus 1 more columns — full list in the Fields section above
FROM <catalog>.<schema_data>.f03b11 f
ORDER BY f.RPDOC;