Purpose

The report is used for printing Historical Accounts Receivable reports. It is similar in look to the A/R Detail report, but the source of the A/Rs is the G/L records rather than the orders (since the orders may have changed). It also does not break the A/Rs into their corresponding Ages.

Information Structure

The report shows, in logical hierarchy, information from the following different sources and the table the information is contained within:

  • GL
    • Order (TransHeader)
      • Company (TransHeader)
        • A/P Contact Information (AccountContact)
        • Payment Terms (PaymentTerms)

      • Order Salesperson (Employee)
      • Order Division (EmployeeGroup)

Note that, unlike the A/R summary, the Contact Information displayed here is the Company's contact information and not the contact information for the particular order.

Tables Accessed

Purpose
Table
Linkage Information
Filter Information
GL Level
GL

Where GLAccountID = 14
Order Level
TransHeader
Left Join GL.TransactionID = TransHeader.ID
TransHeader.StatusID = 3
Company Info
Account
Left Join Account.ID = TransHeader.AccountID
((?Account_AccountID = -1) or (?Account_AccountID = TransHeader.AccountID))
Payment Terms
PaymentTerms
Left Join PaymentTerms.ID = Account.PaymentTermsID

Contact Info
AccountContact
Left Join AccountContact.ID = Account.AccountingContactID

Salesperson
Employee
Left Join Employee.ID = TransHeader.Salesperson1ID
{?Salesperson_EmployeeID} = -1 or {?Salesperson_EmployeeID} = TransHeader.SalespersonID
Division
EmployeeGroup
Left Join EmployeeGroup.ID = TransHeader.DivisionID
{?Division_DivisionID} = -1 or {?Division_DivisionID} = TransHeader.DivisionID

Parameters and Filter Options

The following options need to be implemented to filter the data and/or provide information to reconfigure the report display.

Description
Parameter Name
Type
Default
Data Filter
Notes
Report Date
?ReportDate_DateTime
DateTime
Today()
where GL.EntryDateTime <= ?ReportDate

Division
?Division_DivisionID
Integer
-1
((?Division_DivisionID = -1) or (?Division_DivisionID = TransHeader.DivisionID))
Use -1 for All
Salesperson
?Salesperson_EmployeeID
Integer
-1
((?Salesperson_EmployeeID = -1) or (?Salesperson_EmployeeID = TransHeader.Salesperson1ID))
Use -1 for All
Company
?Account_AccountID
Integer
-1
((?Account_AccountID = -1) or (?Account_AccountID = TransHeader.AccountID))
Use -1 for All
Report Title
?ReportTitle
String


used in header

If any of these filters are NOT set to -1, then the filter information should be displayed in the right header of the first page, as in:
    • Salesperson: Jim Smith
    • Division: North Plant
    • Company: Smith Refineries

If the filter is set to -1, do not show the filter line in the header.

Display and Group Fields


Group Header #1
Category
Item
Field
Notes
Group Header #1
Division Name
Division: Division.DivisionName

Group Header #2
Company
Account.CompanyName
A/P Contact
AccountContact.FirstName + " " + AccountContact.LastName

Credit Balance
Account.CreditBalance

Phone
AccountContact.PrimaryNumber

Payment Terms
PaymentTerms.TermsName

Detail
Order Number
TransHeader.OrderNumber

Sale Date
TransHeader.SaleDate
display in short date format (no time)
Age
Date() - cast(TransHeader.SaleDate as Date)
number of days since Sale
Description
TransHeader.Description

Current Order Total
TransHeader.TotalPrice

?ReportDate Balance Due
Sum(GL.Amount) where GL.EntryDateTime <= ?ReportDate

Group Footer #2
Total Due
Sum( Balance Due Column )

Group Footer #1
Total Due
Sum( Balance Due Column )


Group By

The following groups are inserted in the report design:
Description
Level
Optional
Group ID field
Group Display Field
Notes
Division
1
No
TransHeader.DivisionID
Division.DivisionName

Company
2
No
TransHeader.AccountID
Account.Companyname

Order
3
No
GL.TransactionID
TransHeader.OrderNumber


Sort Options

The data within a group will always be sorted by these fields:

Description
Sort Text
Level
Optional
Notes
Division
Division.DivisionName
1
No

Company Name
Account.CompanyName
2
No

Order ID
TransHeader.ID
3
No


There are no user sort options for this report.

Export

The report data should export into clean comma-delimited/Excel data.


Test Cases