EOFY aged overdue invoice list
This report satisfies the French Commercial Code (Article D441-4) that requires this report for customers and suppliers. It applies to companies that must have their annual account certified by an account commissioner or auditor and for fiscal years from July 1, 2016.
The report must include the number and total amount, excluding taxes, for issued and received invoices that are past due and unpaid at the end of the fiscal year.
These amounts are broken down by late payment intervals, the percentage of late payments compared to the total amount of sales and purchases for the year.
You have the option to include detailed open item information on separate pages. See Detail pages for specific information regarding these report parameters.
Report data is stored in the BALAGEEOY table.
You can access the report from Reports > Prints/group > Financials > Legal reports.
Prerequisites
The HDU- Historical open item management activity code must be set to Yes.
Report criteria and control
This section describes how the parameters in the List of criteria behave.
Criteria | Comments |
Company | Legal companies allowed. |
Site | Option to filter open items and invoices by the company/site they are linked to.On a multi-site invoice, the header site alone is considered for all calculations.Printing data is restricted to the sites authorized in the CONSCPT function. |
Ctrl. account range — Block A | Option to filter open items and invoices by their control accounts. Selected open items display in block A, that details past due and unpaid invoices. If the corresponding invoices are not posted, they display in block B.You need to enter a control account or a control group in block A, not both. If there is no value in the control account range, it means there is no control account at all. |
Ctrl. account range — Block B | Option to filter open items and invoices by their control accounts. The selected open items and invoices only display in block B that details excluded open items (those not posted or disputed payables and receivables).Both parameters can be left empty. If there is no value in control account range, it means no control account at all. |
Range of business partners | Option to filter open items and invoices by the business partner they are linked to. The pay-by/pay-to business partner is not considered at all. |
No. of days 1–4 | Entered in increasing order (0, 30, 60, 90).Cannot be a negative value. |
FY end reference date | When a legal company is entered, the fiscal year end must: |
Sign (Local menu Debit, Credit) | Option to indicate how open item/invoice amounts must be displayed in the report. |
Not closed open item detail(A) | Option to display a detailed list of open items summarized in Block A: Number of related items and Total ex-tax amt. of related open items. |
Pur. & sales invoice detail(A) | Option to display a detailed list of invoices and journal entries summarized in Block A: Invoices issued/received in FY. |
Disputed open items detail(B) | Option to display a detailed list of excluded open items summarized in Block B: Open items excluded from (A) related to disputed or not posted payables and receivables. |
Multi-BP invoice detail | Option to display a list of multi-BP journal entries where there is more than one line with a business partner. |
Print selections (Local menu No, Yes) | No comments |
List of criteria
Parameter | Parameter title | Type |
|---|---|---|
societe | Company | |
sitedeb | Site range | |
coladeb | Ctrl. account range - Block A | SAC |
grpacol | Ctrl. account group - Block A | |
colbdeb | Ctrl. account range - Block B | SAC |
grpbcol | Ctrl. account group - Block B | |
tiersdeb | Range of business partners | |
borne1 | No. of days 1 | C |
borne2 | No. of days 2 | C |
borne3 | No. of days 3 | C |
borne4 | No. of days 4 | C |
datref | FY end reference date | D4 |
sens | Sign (Local menu Debit, Credit) | |
detlatpay | Not closed open item detail(A) (Local menu No, Yes) | |
dettotpursal | Pur. & sales invoice detail(A) (Local menu No, Yes) | |
detdoubtful | Disputed open items detail (B) (Local menu No, Yes) | |
detmultibp | Multi-BP invoice detail (Local menu No, Yes) | |
impselections | Print selections (Local menu No, Yes) |
Comments
The data is filtered by site according to the rights granted to the user on the function CONSCPT (Accounts).
Running the report
To meet the requirement, you need to run the report once for customers and once for suppliers. The report layout is the same for customers and suppliers.
Amounts are displayed in main general ledger currency and are tax excluded amounts.
A set of controls exclude certain invoice types or journal entries from the report:
- Invoices/journal entries where the open item type is not set to Invoice (TYPDUD field in the GACCENTRY/ GACCDUDATE/HISTODUD tables).
- Open items linked to journal entries whose document type is set with a Period different from Normal.
- Open items linked to journal entries whose category is different from Actual.
- Multi-BP invoices/journal entries (A multi-BP document is a journal entry where there is more than one line with a business partner.)
- Proforma invoices
Note: If the journal entry has been generated through the GESBIC, GESSIH, GESBIS, or GESPIH functions or directly entered in the GESGAS function (manual entry or import), and if the above criteria are met, it is included in the BALAGEEOY report.
Excluded tax calculation
Excluded tax amount calculation when the invoice is posted
The excluded tax amount is calculated based on data in the GACCENTRY table by considering:
- Records in the company's main general ledger
- Account amounts in the main general ledger currency
- Lines added where the accounts are subject to VAT management
Excluded tax amount when the invoice is not posted
When the invoice is generated in the Purchasing invoices (GESPIH) or Sales invoices (GESSIH) function, the excluded tax amount in the main general ledger currency already exists in the PINVOICE and SINVOICE tables from the Amount - tax (co) field (AMTNOTL).
When the invoice originates from the Supplier invoices (GESBIS) or Customer invoices (GESBIC) function, the excluded tax amount in the ledger currency is calculated using the tax excluded and tax included amounts in invoice currency that is available in Sale and Purchase invoices tables (AMTNOT and AMTATI fields in the SINVOICE and PINVOICE tables), as well as the tax included amount in ledger currency, that is available in the open item table (AMTLOC field in the GACCDUDATE table).
Description of the report
The first page of the report displays the selected report criteria. The second page displays the summary. Depending on parameter settings, additional pages display details for the related open items. See Detail pages for specific information.
Summary page
The Summary page of the report displays the following information divided into two blocks.
Block (A): Late payment ranges
Number of related open items / 0 day – Total ex-tax amt. of related open items / 0 day
The accounting date of the invoice is less than or equal to the fiscal year end date and it can also be less than or equal to the fiscal year start date.
The open item due date is greater than or equal to the year-end date and the open item has not been paid or the open item has been paid and the accounting date for the payment is greater than the fiscal year end date.
If the accounting date for the payment is less than the fiscal year end date, the open item is not considered in the Number of related open items, 0 day column.
Number of related open items / 1 to 30 days, 31 to 60, … Total (1 day and more) – Total ex-tax amt. of related open items /1 to 30 days, 31 to 60, … Total (1 day and more)
The accounting date of the invoice is less than or equal to the fiscal year end date and it can even be less than or equal to the fiscal year start date.
The open item due date is less than the fiscal year end date and the open item has not been paid or the open item has been paid and the accounting date for the payment is greater than the fiscal year end date.
In the Total ex-tax amt. of related open items line, it is the remaining open item amount that is considered and displayed.
For example, if you run the report for end date that is 31 December 2017, some invoices could have an accounting date in 2016. If they are still unpaid and past due, they appear here in these 2 elements. But of course, they are not considered in the invoices issued/received within the 2017 fiscal year.
Invoices issued/received in FY (based on selection parameters)
The accounting date is less than or equal to the fiscal year end date and is greater than or equal to the deduced fiscal year start date.
Block (B): Open items excluded from (A) related to disputed or not posted payables and receivables
Number of excluded open items – Total amt. of excluded open items
The control account for the invoice is consistent with block A, but the invoice is not posted
The control account for the invoice/open item is consistent with block B, and the invoice may or may not be posted.
The accounting date of the document is less than or equal to the fiscal year end date and is greater than or equal to the fiscal year start date.
Detail pages
Details are only included if the respective report parameters are set to Yes. Each section begins on a new page.
Not closed open item detail(A)
This option provides details on the Number of related open items and Total ex-tax amt. of related open items in the Summary page (Block A) of the report. The default value for this parameter is Yes. If you change this setting to No, these details are not included in the report.
The detailed list of open items is sorted according to these fields:
Business partner in ascending order
Accounting date beginning with earliest
Due date beginning with the most recent
Entry type and Numberin ascending order
For example, if the total for the 0 days interval is 7 with a total amount ex-tax of 7,875 EUR, the report includes a list of each open item and the amounts with and without tax. Each interval starts on a new page. If there are no open items in an interval, there is no corresponding page. The last page of interval details includes the overall totals from the Total 1 day and more field on the Summary page. So, if there are 7 in O days, 2 in 1 To 30 days, and 4 in 61 To 90 days, the 61 To 90 days detail page includes details for this interval plus the totals from the Total 1 day and more fields.
Pur. & sales invoice detail(A)
This option displays details for the corresponding invoices and journal entries by interval from the Invoices issued/received in FY on the Summary page (Block A) of the report. The default value for this parameter is No. Select Yes to include these details.
If there are several open items for the same document or invoice number, only one line displays for the corresponding tax amounts. For example, if there are two open items listed on an invoice, only the invoice totals display, not the amounts for each open item.
The detailed list of open items is sorted according to these fields:
Business partner in ascending order
Accounting date beginning with earliest
Entry type and Number in ascending order
Disputed open items detail(B)
This option displays details for the two fields in the Total amt. of exclude of open items on the Summary page (Block B) of the report. The default value for this parameter is Yes. If you change this setting to No, these details are not included in the report.
These details are broken down into to two parts: Excluded open items – Not posted and Excluded open items – Disputed payables and receivables. Each part displays subtotals for the number of open items part and the amounts with and without tax in that section. At the bottom of the page, totals for both parts display, which corresponds to the values in the Summary page (Block B) of the report: Number of excluded open itemsand Total amt. of excluded open items.
Part 1: Excluded open items – Not Posted
This section displays open items for invoices that are not posted and have a control account identified in one of these report parameters: 3. Ctrl. account range — Block A, 4. Ctrl. account group — Block A, 5. Ctrl. account range — Block B, or 6. Ctrl. account group — Block B.
Open items in Part 1 are sorted according to these fields:
Business partner in ascending order
Accounting date beginning with earliest
Entry type and Number in ascending order
Due date beginning with the most recent
Part 2: Excluded open items – Disputed payables and receivables
This section displays open items for invoices that are posted and have a control account identified in one of these report parameters: 5. Ctrl. account range — Block B or 6. Ctrl. account group — Block B.
Open items in Part 2 are sorted according to these fields:
Business partner in ascending order
Accounting date beginning with the earliest
Due date beginning with the most recent
Entry type and Number in ascending order
Multi-BP invoice detail
This parameter is not associated with any data from the Summary page of the report. The default value is No. Select Yes to include these details with the report provided they meet the criteria outlined below.
These details are limited to journal entries where there is more than one line with a business partner, since they are not considered in the Summary part.
The site displayed on this page corresponds to the site in the journal entry.
The invoices or journal entries on this page are sorted according to the following fields:
Accounting date beginning with the earliest
Entry type and Number in ascending order