Other Snapshot Fields

Beyond addresses and BOM components, many other fields are copied from master records to transactions at creation time.

---

Price Snapshots

PRICE SNAPSHOT BEHAVIOR
═══════════════════════════════════════════════════════════════════════════════

Item Record                                Transaction Line
┌─────────────────────┐                   ┌─────────────────────┐
│ Item: Widget-A      │                   │ Sales Order Line    │
│ Base Price: $100    │ ── COPIED ───────▶│ Rate: $100          │
│                     │    AT CREATION    │ Amount: $1,000      │
└─────────────────────┘                   └─────────────────────┘
         │                                         │
         ▼                                         ▼
┌─────────────────────┐                   ┌─────────────────────┐
│ Price Updated:      │                   │ Rate unchanged:     │
│ Base Price: $120    │ ── NO SYNC ──X    │ Rate: $100          │
└─────────────────────┘                   └─────────────────────┘

Related Tables

Master Table	Transaction Field	Description
ItemPrice	TransactionLine.rate	Price at time of order
ItemPrice	TransactionLine.amount	Calculated from rate × quantity

Query: Compare Item Price vs Transaction Price

SELECT
    t.tranid,
    t.trandate,
    BUILTIN.DF(tl.item) AS item,
    tl.rate AS transaction_rate,
    ip.unitprice AS current_item_price,
    CASE
        WHEN tl.rate != ip.unitprice THEN 'PRICE CHANGED'
        ELSE 'Same'
    END AS status
FROM Transaction t
INNER JOIN TransactionLine tl ON tl.transaction = t.id
LEFT JOIN ItemPrice ip ON ip.item = tl.item AND ip.pricelevel = 1  -- Base price
WHERE t.type = 'SalesOrd'
  AND tl.mainline = 'F'
  AND tl.item IS NOT NULL
  AND tl.rate != ip.unitprice
ORDER BY t.trandate DESC

---

Exchange Rate Snapshots

EXCHANGE RATE SNAPSHOT
═══════════════════════════════════════════════════════════════════════════════

CurrencyRate Table                         Transaction
┌─────────────────────┐                   ┌─────────────────────┐
│ USD → EUR: 0.85     │                   │ Invoice (EUR)       │
│ Effective: Jan 1    │ ── RATE AT ─────▶│ Exchange Rate: 0.85 │
│                     │    TRAN DATE     │ TranDate: Jan 15    │
└─────────────────────┘                   └─────────────────────┘
         │                                         │
         ▼                                         ▼
┌─────────────────────┐                   ┌─────────────────────┐
│ Rate Updated:       │                   │ Rate unchanged:     │
│ USD → EUR: 0.90     │ ── NO SYNC ──X    │ Exchange Rate: 0.85 │
│ Effective: Feb 1    │                   │                     │
└─────────────────────┘                   └─────────────────────┘

Related Tables

Master Table	Transaction Field	Description
CurrencyRate	Transaction.exchangerate	Rate locked at transaction date

Query: Transaction Exchange Rates

SELECT
    t.tranid,
    t.trandate,
    BUILTIN.DF(t.currency) AS currency,
    t.exchangerate AS transaction_rate,
    cr.exchangerate AS current_rate,
    ROUND(((cr.exchangerate - t.exchangerate) / t.exchangerate) * 100, 2) AS rate_change_pct
FROM Transaction t
LEFT JOIN CurrencyRate cr
    ON cr.basecurrency = 1  -- Base currency (e.g., USD)
    AND cr.transactioncurrency = t.currency
    AND cr.effectivedate = (
        SELECT MAX(effectivedate)
        FROM CurrencyRate
        WHERE transactioncurrency = t.currency
          AND effectivedate <= SYSDATE
    )
WHERE t.type = 'CustInvc'
  AND t.currency != 1  -- Foreign currency only
  AND t.trandate >= ADD_MONTHS(SYSDATE, -6)
ORDER BY t.trandate DESC

---

Terms Snapshots

Customer payment terms are copied to transactions:

TERMS SNAPSHOT
═══════════════════════════════════════════════════════════════════════════════

Customer Record                            Transaction
┌─────────────────────┐                   ┌─────────────────────┐
│ Customer: Acme      │                   │ Invoice             │
│ Terms: Net 30       │ ── COPIED ───────▶│ Terms: Net 30       │
└─────────────────────┘                   └─────────────────────┘
         │
         ▼
┌─────────────────────┐                   ┌─────────────────────┐
│ Terms Updated:      │                   │ Terms unchanged:    │
│ Terms: Net 15       │ ── NO SYNC ──X    │ Terms: Net 30       │
└─────────────────────┘                   └─────────────────────┘

Query: Compare Customer Terms vs Transaction Terms

SELECT
    t.tranid,
    t.trandate,
    BUILTIN.DF(t.entity) AS customer,
    BUILTIN.DF(t.terms) AS transaction_terms,
    BUILTIN.DF(c.terms) AS current_customer_terms,
    CASE
        WHEN t.terms != c.terms THEN 'TERMS CHANGED'
        ELSE 'Same'
    END AS status
FROM Transaction t
INNER JOIN Customer c ON c.id = t.entity
WHERE t.type = 'CustInvc'
  AND t.terms != c.terms
ORDER BY t.trandate DESC

---

Item Default Fields

Many fields from the Item record are copied to transaction lines:

Item Field	Transaction Line Field	Description
salesdescription	description	Line item description
class	class	Classification
department	department	Department
location	location	Location
taxcode	taxcode	Tax code

Query: Items with Modified Descriptions

SELECT
    t.tranid,
    t.trandate,
    BUILTIN.DF(tl.item) AS item,
    tl.description AS line_description,
    i.salesdescription AS current_item_description,
    CASE
        WHEN tl.description != i.salesdescription THEN 'MODIFIED'
        ELSE 'Same'
    END AS status
FROM Transaction t
INNER JOIN TransactionLine tl ON tl.transaction = t.id
INNER JOIN Item i ON i.id = tl.item
WHERE t.type = 'SalesOrd'
  AND tl.mainline = 'F'
  AND tl.item IS NOT NULL
  AND tl.description != i.salesdescription
ORDER BY t.trandate DESC

---

Tax Snapshots

Tax codes and rates are determined at transaction time based on:

• Ship-to address jurisdiction
• Item tax settings
• Customer tax status

TAX SNAPSHOT
═══════════════════════════════════════════════════════════════════════════════

At Transaction Creation:
┌──────────────────────────────────────────────────────────────────────────┐
│                                                                          │
│  Ship To: New York, NY  ───┐                                             │
│                            │                                             │
│  Item Tax Settings ────────┼───▶ Tax Nexus Calculation ───▶ Tax Code    │
│                            │                                             │
│  Customer Tax Status ──────┘                                             │
│                                                                          │
└──────────────────────────────────────────────────────────────────────────┘

Result stored on TransactionLine.taxcode and Transaction tax fields.
Changes to tax settings don't affect existing transactions.

---

Complete Snapshot Field Reference

Category	Master Source	Transaction Snapshot	Notes
Address	CustomerAddressbook	TransactionShippingAddress	Becomes "Custom" if changed
Address	CustomerAddressbook	TransactionBillingAddress	Same behavior
BOM	AssemblyItemMember	TransactionLine	Copied at WO creation
BOM	BomRevisionComponentMember	TransactionLine	Advanced BOM
Price	ItemPrice	TransactionLine.rate	Price at order date
Exchange Rate	CurrencyRate	Transaction.exchangerate	Rate at transaction date
Terms	Customer.terms	Transaction.terms	Can be changed on transaction
Tax	Item/Customer/Nexus	TransactionLine.taxcode	Based on jurisdiction
Description	Item.salesdescription	TransactionLine.description	Editable
Class	Item.class	TransactionLine.class	Editable
Department	Item.department	TransactionLine.department	Editable
Location	Item.location	TransactionLine.location	Editable

---

Fields That DO Update

Not everything is snapshotted. Some fields reference the master and update:

Field	Behavior
Customer.companyname	Referenced, shows current name
Item.itemid	Referenced, shows current item code
Item.displayname	Referenced, shows current display name
Entity.email	Referenced, shows current email

Reference vs Snapshot

Use BUILTIN.DF() to get current referenced values. To get the original snapshotted value, you need to query the transaction-specific table.

---

Handling Snapshot Discrepancies

Report: All Changed Fields

-- Find transactions where key fields differ from current master
SELECT
    t.tranid,
    t.trandate,
    BUILTIN.DF(t.entity) AS customer,
    -- Address check
    CASE WHEN tsa.addr1 != ea.addr1 THEN 'Address' END AS addr_changed,
    -- Terms check
    CASE WHEN t.terms != c.terms THEN 'Terms' END AS terms_changed,
    -- Price check (simplified)
    CASE WHEN tl.rate != ip.unitprice THEN 'Price' END AS price_changed
FROM Transaction t
INNER JOIN Customer c ON c.id = t.entity
LEFT JOIN TransactionShippingAddress tsa ON tsa.nkey = t.shippingaddress
LEFT JOIN CustomerAddressbook cab ON cab.entity = c.id AND cab.defaultshipping = 'T'
LEFT JOIN EntityAddress ea ON ea.nkey = cab.addressbookaddress
LEFT JOIN TransactionLine tl ON tl.transaction = t.id AND tl.mainline = 'F'
LEFT JOIN ItemPrice ip ON ip.item = tl.item AND ip.pricelevel = 1
WHERE t.type = 'SalesOrd'
  AND (
      tsa.addr1 != ea.addr1
      OR t.terms != c.terms
      OR tl.rate != ip.unitprice
  )
ORDER BY t.trandate DESC

---

Quick Reference

Check for Price Changes

FROM TransactionLine tl
INNER JOIN ItemPrice ip ON ip.item = tl.item
WHERE tl.rate != ip.unitprice

Check for Terms Changes

FROM Transaction t
INNER JOIN Customer c ON c.id = t.entity
WHERE t.terms != c.terms

Check for Rate Changes

FROM Transaction t
INNER JOIN CurrencyRate cr ON cr.transactioncurrency = t.currency
WHERE t.exchangerate != cr.exchangerate

---

See Also

• Snapshot Tables Overview - Understanding the snapshot pattern
• Address Tables - Address snapshot details
• BOM Tables - BOM component snapshots