Skip to main content

Data warehouse field reference: Accounts Receivable

Maggie Shipley
Updated

Where values from the Accounts Receivable screens are stored in the external data warehouse. Use this reference when rebuilding AR reporting outside Aiwyn.

All mappings below are based on the internal UI-to-data-warehouse field mapping.

New Payment dialog — details

Screen: Accounts Receivable → Transactions → New Payment

Tables: ar_tran, payment, ar_tran_link

UI element Table Column Notes
Client ar_tran client_id  
Payment method payment payment_method Stored on the linked payment row
Bank payment firm_bank_account_id Join through payment.ar_tran_id
Reference payment pp_id Duplicate references receive a (n) suffix, so the stored value can differ from what was entered
Transaction date ar_tran transaction_date  
Deposit date payment posted_to_bank_dt, posted_to_bank Entering a deposit date stores the date at the start of the day and sets the posted flag
Note ar_tran notes  
Total payment amount ar_tran amount payment has no amount column; the value lives on ar_tran

New Payment dialog — allocation

UI element Table Column Notes
Allocatable transactions → Allocated amount ar_tran_link ar_tran_mod_id, ar_tran_base_id, alloc_amount Each selected row and amount becomes one allocation entry. ar_tran_mod_id is the new payment's ar_tran.id; ar_tran_base_id is the invoice being paid down.

Payment Plans grid

Screen: Accounts Receivable → Payment Plans

Tables: automatic_payment_plan, scheduled_payment, payment_method, ar_tran

UI element Table Column Notes
Client ID / Client automatic_payment_plan client_id Foreign key; the grid displays the joined client reference and name
Description automatic_payment_plan name AutoPay plans display “AutoPay up to $X” instead of the stored name
Created by and ADMIN pill automatic_payment_plan created_by_staff_id, created_by_contact_id  
Amount scheduled_payment amount Amount of the next scheduled payment, not the plan total; no plan-total column exists
Payment Method automatic_payment_plan → payment_method payment_method_id → type, card_type, last4 The displayed value is composed at render time. Recorded payments instead use payment.payment_method.
Start Date automatic_payment_plan created_at Used for AutoPay plans. A scheduled plan's start date comes from an underlying schedule that is not exposed.
Last Payment ar_tran transaction_date Date of the most recent successful payment on the plan
Next Payment — “Skipped” suffix scheduled_payment active The suffix appears when active is false
Updated By / Last Updated automatic_payment_plan last_updated_by_staff_id, last_updated_by_contact_id, updated_at  

Not in the external data warehouse

UI element Why it cannot be reproduced
Frequency The underlying schedule is not exposed
End Date The underlying schedule is not exposed
Next Payment date The next date comes from the underlying schedule, which is not exposed

Refund

Screen: Accounts Receivable → Payment History → row menu → Issue Refund

Tables: refund, ar_tran, ar_tran_link

UI element Table Column Notes
Refund amount refund → ar_tran ar_tran_id → amount, amount_in_currency A refund creates its own ar_tran row with type REFUND; the amount is stored there, not on refund

Tie a refund to the payment it reverses

Relationship Table Column
The refund ar_tran_link ar_tran_base_id
The original payment ar_tran_link ar_tran_mod_id

Reporting cautions

  • payment has no amount column. Read a payment value from ar_tran.amount.
  • payment.pp_id may not exactly match the reference entered by a user because duplicate values receive a (n) suffix.
  • The base and modifier roles in ar_tran_link invert between a payment allocation and a refund. For a payment allocation, the payment is ar_tran_mod_id and the invoice is ar_tran_base_id. For a refund, the refund is ar_tran_base_id and the original payment is ar_tran_mod_id.
  • Payment methods use different columns for plans and recorded payments. A plan joins to payment_method.type, card_type and last4; a recorded payment uses payment.payment_method.
  • A display value such as “Visa •••• 4242” is assembled from multiple columns and is not stored as a single warehouse value.
  • Frequency, End Date and Next Payment date cannot currently be reproduced from the external warehouse.

Related