The AP Aging Report lists open accounts payable (AP) transactions for each tenant. It contains the amount due, the due date, and the aging bucket for each transaction, along with vendor, location, and approval details.
Column reference
| Column | Type | Description |
|---|---|---|
| tenant_id | NUMBER(38,0) | R365 internal tenant identifier |
| transaction_id | VARCHAR | Transaction header identifier |
| transaction_type | NUMBER(38,0) | Numeric code for the transaction type: 1 is AP invoice, 2 is AP credit memo, and 3 is AP payment. Any other value, such as 7 for journal entry, is a non-AP transaction that posts to the AP GL account. |
| transaction_type_name | VARCHAR | Human-readable transaction type label |
| transaction_number | VARCHAR | Document or reference number |
| vendor_id | VARCHAR | Vendor identifier |
| vendor_name | VARCHAR | Vendor name |
| vendor_number | VARCHAR | Vendor number |
| vendor_group_id | VARCHAR | Vendor group identifier (no vendor group name available yet) |
| location_id | VARCHAR | Location identifier |
| location_name | VARCHAR | Name of the location |
| legal_entity_id | VARCHAR | Legal entity identifier |
| legal_entity_name | VARCHAR | Legal entity name |
| transaction_date | DATE | Document date. If the document date is blank, the GL posting date is used. |
| due_date | DATE | Transaction due date |
| gl_date | DATE | GL posting date |
| approval_status | NUMBER(38,0) | Approval status: 1 is unapproved and 2 is approved. The report includes both. Filter this column to select approved transactions, unapproved transactions, or both. |
| amount | NUMBER(38,4) | Transaction amount. Payments and credit memos appear as negative amounts, so summing this column gives the net open payable balance. |
| amount_remaining | NUMBER(38,4) | Remaining open balance on the transaction. Payments and credit memos appear as negative amounts, as in the amount column. |
| applied_amount | NUMBER(38,4) | Amount applied to this transaction to date. Payments and credit memos appear as negative amounts, as in the amount column. |
| is_short_pay | BOOLEAN | True if the transaction is flagged for short-pay |
| credit_expected | NUMBER(38,4) | Expected credit amount for short-pay transactions. The amount is not subtracted from the amount remaining. Apply it against the amount remaining as needed. |
| comment | VARCHAR | Header-level transaction comment |
| is_beginning_balance | BOOLEAN | True for beginning balance (go-live) AP documents. The report includes these documents. Filter this column to exclude them. |
| as_of_date | DATE | The date used to calculate aging for each tenant. This is the latest transaction date, limited to the latest date data was received, so a future-dated transaction cannot push the date ahead. If no transaction has a date, the latest data received date is used. The date can move back if the latest transaction is later corrected or deleted. |
| days_past_due | NUMBER(9,0) | Number of days between the aging date and the as-of date. The aging date is the due date, or the transaction date if there is no due date. The value is negative when the transaction is not yet due. The value is blank if both dates are blank. It is always blank for non-AP transactions (types other than 1, 2, and 3), which do not age. |
| aging_bucket | VARCHAR | Aging group based on days past due: Current, 1-30, 31-60, 61-90, or 91+. Always Current for non-AP transactions (types other than 1, 2, and 3). Blank, not 91+, when days past due is blank on an AP transaction. |