Open Item Accounts Receivable Report
Executive Summary
Open Item Accounts Receivable Report produces an Excel workbook showing the open receivable activity for every client account on the weekly billing cycle you choose, for one billing cycle period. Finance staff start a report, wait while the system reads the open invoices, payments and adjustments for each funder's client accounts, and receive a workbook that breaks the activity down by weekday, with a sheet per funder and a department summary. Every report is kept in the list so the workbook can be downloaded again later.
Key Features
- Start a new report from a weekly billing cycle, year and billing cycle period
- Excel workbook with a Criteria sheet, one sheet per funder and a Departments sheet
- Invoice quantity and amount, debit memos, credit memos and payments summarised for each account by day of the week and for the whole week
- Review who ran each report, when it ran and how long it took
- Download the workbook of any earlier report from the grid
Typical Workflow
- Click Start A New Report.
- Choose the Billing Cycle, Year and Billing Cycle Period, then click Ok.
- Wait for the Starting Report progress panel to finish. The Excel file downloads automatically and "Exported Successfully" is shown.
- The new job appears at the top of the grid with its start, end and duration.
- Use the Download icon on the row at any time to save the workbook again.
Report Output
The report is an Excel workbook (.xlsx) generated when the report finishes. It is downloaded automatically and also stored on the job. The file name pattern is OpenItemAR_{cycle}_{year}_{period}_{date and time}.xlsx. Use the Download icon in the grid to retrieve it later; if a job has no stored file, the warning "No File Available" appears.
Report Contents
The workbook has these sheets, in this order.
| Content | How it is determined |
|---|---|
| Criteria sheet | Report name, the billing cycle, year, and the period number with its date range. |
| One sheet per funder | Named with the funder code. Created only when at least one of the funder's client accounts has open activity in the period. Funders are those assigned to the chosen billing cycle. |
| Client UID, Name, Contact Id, Name, Account ID | Client number, client last name and first name, billing contact number and name, and the client account number. Rows are sorted by client last name, then first name. Accounts with no open activity in the period are left out. |
| Mon to Sun and Week columns: IN Qty, IN Amt | Quantity and amount of invoices (document type group Invoice). |
| DM | Total of adjustments whose document type is a debit. |
| CM | Total of adjustments whose document type is a credit. |
| Payment | Total of transactions whose document type group is Payment. |
| Which day a transaction falls on | The transaction's document date, converted to the tenant time zone. The Week columns add up all seven days. |
| Which transactions are included | Transactions of the account and funder with the status open, dated from the first day to the last day of the chosen period. Empty cells are left blank rather than showing zero. |
| Departments sheet | One row per department and document type: department number and name, document type and name, number of transactions and the total amount, across all funders. |
Available Permissions
The following permissions are available to be assigned to AlayaCare Roles:
| Attribute | What It Controls |
|---|---|
| View | Open the page |
Main Grid
The grid lists the Open Item Accounts Receivable reports that have been run, newest first (14 rows per page).
| Column | Description |
|---|---|
| Added | Date and time the report was started, in the tenant time zone. |
| Added By | User who started the report. |
| Cycle | Billing cycle. |
| Year | Billing year. |
| Period | Billing cycle period number. |
| From | Period start date. |
| To | Period end date. |
| Start | When processing began. |
| End | When processing finished. Blank if the report did not finish. |
| Duration | Elapsed processing time. |
| Manage Records | Download icon that saves the Excel workbook. |
Actions
| Button | Description |
|---|---|
| Start A New Report | Opens the Start A New Report dialog. |
| Download (row icon) | Downloads the Excel workbook of that report, or shows "No File Available" when none is stored. |
Dialog Components
Start A New Report
Opens from Start A New Report. Choose all three values and click Ok.
| Field | Purpose |
|---|---|
| Select A Billing Cycle | Billing cycle for the report. Only billing cycles with a weekly frequency are listed. |
| Year | Billing year. Defaults to the current year. |
| Select A Billing Cycle Period | Period within the chosen cycle and year, shown as year, period number and date range, oldest first. The list fills in after a cycle and year are chosen. |
Dependencies
The following tables must be configured for lookups on this view to work:
| Table | Description |
|---|---|
| Billing Cycles | Weekly billing cycles offered in the dialog. Also covers its billing cycle periods: periods offered in the dialog and the dates of the report. |
| Departments | Department names shown on the Departments sheet. |
| Document Types | Classify each transaction as an invoice, payment, debit or credit adjustment and supply the document type names. |
| Funders | Funders on the chosen billing cycle, one sheet each. |
Processing Overviews
This section describes what happens after you confirm a processing action.
Start A New Report
- Creates the report in the grid with the billing cycle, year, period dates, the user and the start time.
- Finds every funder assigned to the chosen billing cycle.
- Starts the workbook and writes the Criteria sheet.
- For each funder, goes through the client accounts in last name, first name order. The progress panel shows the funder code being processed.
- For each account, loads the open transactions dated within the period. Accounts with none are left out.
- Adds each transaction to the account's weekday totals using its document date in local time and the document type: invoices go to IN Qty and IN Amt, credit adjustments to CM, debit adjustments to DM and payments to Payment. The transaction is also added to the department totals.
- Writes the funder's sheet when it has at least one account with activity, then writes the Departments sheet.
- Saves the workbook on the report, downloads it, and records the end time and duration.