F03B13

-- ============================================================
-- Table  : F03B13 Receipts header: one row per customer receipt or draft with the check amount, unapplied balance, bank account, and G/L batch identity.
-- Purpose: Column-selected read of F03B13 — auto-generated from field metadata
-- Grain  : One row per RYPYID
-- 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.RYPYID AS "System-assigned payment ID — the key that receipt detail rows hang off",
  f.RYCKNU AS "Receipt or check number from the customer's remittance",
  f.RYAN8 AS "Customer address book number on the receipt",
  f.RYPYR AS "Payor address number when someone other than the customer paid",
  CASE WHEN f.RYDMTJ IS NULL OR f.RYDMTJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RYDMTJ AS INT) DIV 1000, 1, 1), CAST(f.RYDMTJ AS INT) % 1000 - 1) END AS "Receipt date — the date the payment was received",  -- CYYDDD Julian → DATE
  CASE WHEN f.RYDGJ IS NULL OR f.RYDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RYDGJ AS INT) DIV 1000, 1, 1), CAST(f.RYDGJ AS INT) % 1000 - 1) END AS "G/L date the receipt posts under",  -- CYYDDD Julian → DATE
  CASE WHEN f.RYVLDT IS NULL OR f.RYVLDT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RYVLDT AS INT) DIV 1000, 1, 1), CAST(f.RYVLDT AS INT) % 1000 - 1) END AS "Value/cleared date used for cash forecasting and bank reconciliation",  -- CYYDDD Julian → DATE
  f.RYPOST AS "G/L posted code; 'D' when the receipt batch has posted",
  f.RYCTRY AS "Century of the G/L date",
  f.RYFY AS "Fiscal year of the G/L date",
  f.RYPN AS "Fiscal period of the G/L date",
  f.RYCO AS "Company that received the cash",
  f.RYICUT AS "Batch type (RB receipts)",
  f.RYICU AS "Batch number the receipt was entered under",
  CASE WHEN f.RYDICJ IS NULL OR f.RYDICJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RYDICJ AS INT) DIV 1000, 1, 1), CAST(f.RYDICJ AS INT) % 1000 - 1) END AS "Batch date",  -- CYYDDD Julian → DATE
  f.RYCKAM / POWER(10, 2) AS "Total receipt amount in domestic currency",  -- implied decimals: 2 (verify in F9210)
  f.RYAAP / POWER(10, 2) AS "Unapplied portion of the receipt still open against the customer",  -- implied decimals: 2 (verify in F9210)
  f.RYBCRC AS "Base (domestic) currency of the company",
  f.RYCRRM AS "Currency mode — domestic or foreign entry",
  f.RYCRCD AS "Currency the receipt was tendered in",
  f.RYCRR AS "Exchange rate applied to the receipt; implied decimals vary by setup",
  CASE WHEN f.RYERDJ IS NULL OR f.RYERDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RYERDJ AS INT) DIV 1000, 1, 1), CAST(f.RYERDJ AS INT) % 1000 - 1) END AS "Date used to retrieve the exchange rate",  -- CYYDDD Julian → DATE
  f.RYFCAM / POWER(10, 2) AS "Receipt amount in the foreign currency",  -- implied decimals: 2 (verify in F9210)
  f.RYFAP / POWER(10, 2) AS "Unapplied amount in the foreign currency",  -- implied decimals: 2 (verify in F9210)
  f.RYGLBA AS "Short account ID of the bank account the cash hit",
  f.RYTYIN AS "Receipt type code distinguishing cash receipts from drafts and logged receipts",
  f.RYRYIN AS "Payment instrument the customer used (check, EFT, draft)",
  f.RYALPH AS "Customer name captured at entry, handy for quick lookups without joining F0101",
  CASE WHEN f.RYVDGJ IS NULL OR f.RYVDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RYVDGJ AS INT) DIV 1000, 1, 1), CAST(f.RYVDGJ AS INT) % 1000 - 1) END AS "G/L date of the void when the receipt was voided",  -- CYYDDD Julian → DATE
  f.RYVRE AS "Void reason code",
  f.RYNFVD AS "NSF/void code flagging bounced or reversed receipts",
  CASE WHEN f.RYDKC IS NULL OR f.RYDKC = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RYDKC AS INT) DIV 1000, 1, 1), CAST(f.RYDKC AS INT) % 1000 - 1) END AS "Date the check cleared the bank",  -- CYYDDD Julian → DATE
  f.RYDDST AS "Draft status when the receipt is a draft working through acceptance/collection"
FROM <catalog>.<schema_data>.f03b13 f
WHERE
  f.RYDGJ >= <DGJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.RYDGJ <= <DGJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.RYPYID;