R47041

-- ============================================================
-- Program: R47041 Inbound EDI Voucher Edit/Create — the batch processor that turns supplier invoices (X12 810) staged in the EDI invoice tables into A/P voucher and journal entry batch records for voucher batch processing
-- Purpose: Inbound EDI Voucher Edit/Create — the batch processor that turns supplier invoices (X12 810) staged in the EDI invoice tables into A/P voucher and journal entry batch records for voucher batch processing — auto-generated boilerplate from program-table-map
-- Grain  : F0411 × F0411, F0911 — 1:N joins yield one row per detail line
-- Tables : F0411, F0411, F0911
-- Notes  : Auto-generated skeleton. Julian dates are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210). 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.RPADSC / POWER(10, 2) AS "Discount available if paid by the discount due date",  -- implied decimals: 2 (verify in F9210)
  CASE WHEN f0.GLDGJ IS NULL OR f0.GLDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f0.GLDGJ AS INT) DIV 1000, 1, 1), CAST(f0.GLDGJ AS INT) % 1000 - 1) END AS "G/L date driving the fiscal period the line posts to.",  -- CYYDDD Julian → DATE
  f0.GLJELN AS "Line number within the journal entry.",
  f0.GLEXTL AS "Line extension code completing the unique key.",
  f0.GLLT AS "Ledger type the line belongs to (AA actual, CA foreign currency, etc.)."
FROM <catalog>.<schema_data>.f0411 f
LEFT JOIN <catalog>.<schema_data>.f0411 f
  ON f.RPKCO = f.RPKCO
 AND f.RPDOC = f.RPDOC
 AND f.RPDCT = f.RPDCT
 AND f.RPSFX = f.RPSFX
 AND f.RPSFXE = f.RPSFXE
LEFT JOIN <catalog>.<schema_data>.f0911 f0
  ON f0.GLDCT = f.RPDCT
 AND f0.GLDOC = f.RPDOC
 AND f0.GLKCO = f.RPKCO
ORDER BY f.RPKCO;