F470461

-- ============================================================
-- Table  : F470461 The additional-header companion to F47046: one row per outbound EDI invoice (810) carrying the invoice-level totals rolled up from the selected detail lines, the tax authorities and rates behind them, and the payment terms — none of which the base header holds.
-- Purpose: Column-selected read of F470461 — auto-generated from field metadata
-- Grain  : One row per S3EKCO + S3EDOC + S3EDCT
-- 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.S3EKCO AS "Key company that scopes the EDI document number; part of the staging primary key shared with F47046.",
  f.S3EDOC AS "EDI document number of the invoice this additional header belongs to; the 1:1 join back to F47046.",
  f.S3EDCT AS "EDI document type qualifier; pairs with document number and key company to complete the staging key.",
  f.S3EDLN / POWER(10, 3) AS "EDI line number; carried for structural consistency with the detail tables and normally zero on a header row. Three implied decimals.",  -- implied decimals: 3 (verify in F9210)
  f.S3DOC AS "A/R invoice number the totals belong to; the table's second index and the practical bridge to F03B11 when reconciling sent invoices against the ledger.",
  f.S3DCT AS "A/R invoice document type; joins to F03B11 DCT.",
  f.S3KCO AS "Document company of the A/R invoice key; joins to F03B11 KCO.",
  f.S3EDTY AS "Record type qualifier telling the translator which segment group of the 810 this row feeds.",
  f.S3EDSQ AS "Record sequence within the record type; orders repeated additional-header rows when the translator expects more than one.",
  f.S3EDSP AS "Processed flag: set to Y once the invoice has been flagged as sent. Filter N for the outbound backlog; Y rows are what the purge removes.",
  f.S3EDBT AS "Batch number grouping the invoices extracted in the same Invoice Print run.",
  f.S3DOCO AS "JDE sales order number the invoice bills; blank on rows created by the World A/R-side path, which has no order lineage.",
  f.S3DCTO AS "JDE order type of the related order (SO and related types).",
  f.S3SFXO AS "Order suffix; distinguishes multiple documents against the same order number.",
  TRIM(f.S3MCU) AS "Branch/plant on the order; right-justified 12-character business unit.",
  f.S3AN8 AS "Address book number the invoice is billed to.",
  f.S3AG / POWER(10, 2) AS "Gross invoice amount — the total of the detail records the extraction selected, and the figure to tie against the F03B11 gross amount.",  -- implied decimals: 2 (verify in F9210)
  f.S3AAP / POWER(10, 2) AS "Open amount on the invoice at extraction time.",  -- implied decimals: 2 (verify in F9210)
  f.S3ADSC / POWER(10, 2) AS "Discount available if the customer pays inside the terms discount window.",  -- implied decimals: 2 (verify in F9210)
  f.S3DSCT / POWER(10, 2) AS "Discount amount actually applied to the invoice.",  -- implied decimals: 2 (verify in F9210)
  f.S3ATXA / POWER(10, 2) AS "Taxable portion of the invoice total.",  -- implied decimals: 2 (verify in F9210)
  f.S3ATXN / POWER(10, 2) AS "Non-taxable portion of the invoice total; taxable plus non-taxable plus tax should reconcile to gross.",  -- implied decimals: 2 (verify in F9210)
  f.S3STAM / POWER(10, 2) AS "Total tax amount on the invoice.",  -- implied decimals: 2 (verify in F9210)
  f.S3TA1 AS "First tax authority (an address book number). The authority columns repeat five times, TA1 through TA5; only the first is cataloged here.",
  f.S3TXR1 / POWER(10, 3) AS "Tax rate applied by the first authority; three implied decimals. Repeats as TXR1 through TXR5 alongside the authority columns.",  -- implied decimals: 3 (verify in F9210)
  f.S3TAXT / POWER(10, 2) AS "Tax amount for the first authority. Note the alias: the first slot of the repeating group is TAXT, not TAX1 — the remaining slots are TAX2 through TAX5, and mis-specifying this column is a common mapping error.",  -- implied decimals: 2 (verify in F9210)
  f.S3PTD AS "Printed description of the payment terms applied to the invoice.",
  f.S3DCP AS "Discount percentage offered by the payment terms.",
  f.S3DCD AS "Number of days the discount stays available from the invoice date.",
  f.S3NDTP AS "Net days to pay under the payment terms — the basis for the terms side of DSO analysis.",
  CASE WHEN f.S3IVD IS NULL OR f.S3IVD = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.S3IVD AS INT) DIV 1000, 1, 1), CAST(f.S3IVD AS INT) % 1000 - 1) END AS "Invoice date (Julian).",  -- CYYDDD Julian → DATE
  CASE WHEN f.S3DDJ IS NULL OR f.S3DDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.S3DDJ AS INT) DIV 1000, 1, 1), CAST(f.S3DDJ AS INT) % 1000 - 1) END AS "Net due date of the invoice (Julian).",  -- CYYDDD Julian → DATE
  CASE WHEN f.S3DSVJ IS NULL OR f.S3DSVJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.S3DSVJ AS INT) DIV 1000, 1, 1), CAST(f.S3DSVJ AS INT) % 1000 - 1) END AS "Service or tax date driving which tax rates applied (Julian)."  -- CYYDDD Julian → DATE
FROM <catalog>.<schema_data>.f470461 f
WHERE
  f.S3IVD >= <IVD_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.S3IVD <= <IVD_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.S3EKCO;