Invoice Report

Purpose

This is the primary invoice report.

Information Structure

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

  • The Header Record (TransHeader)
    • The [top-level] Line Items and Child Line Items[TransDetail].This is the “detail” level of the report.
    • The Company record (Account).
      • The company's Payment Terms (PaymentTerms).
      • The AP Contact (AccountContact).
    • The Primary Contact record (AccountContact).
    • The Salesperson for the Order(Employee).
    • The Employee who Entered the Order (Employee).
    • The Billing Address for the Order(Address).
    • The Division Name for the Order (EmployeeGroup).

Tables Accessed

Purpose Table Linkage Information Filter Information
Order Level TransHeader TransHeaderIDparam
Line Item Level TransDetail TransDetail.VariationID
TransVariation.ID
(TransDetail.ParentClassTypeID
10000 or ShowChildItemsTrue)
Customer Info Account Left Join Account.ID
TransHeader.AccountID
Contact Info AccountContact Left JoinAccountContact.ID
TransHeader.ContactID
Billing Address Address as
BillingAddress
Left Join BillingAddress.ID
TransHeader.InvoiceAddressID
Payment Terms PaymentTerms Left Join PaymentTerms.ID
Account.PaymentTermsID
Salesperson Employee as
Salesperson
Left Join Salesperson.ID
TransHeader.Salesperson1ID
Entered By Employee as
EnteredBy
Left Join EnteredBy.ID
TransHeader.EnteredByID
Division EmployeeGroup
as Division
Left Join Division.ID
TransHeader.DivisionID
Payments Part 1 Journal Left Join Journal.TransactionIDTransHeader.ID
Payments Part 2 Payments Left Join PaymentIDJournal.ID
AP Contact Info AccountContact as
APContact
Left Join Account._APContact.ID || || || Shipping
Address || Address as
ShippingAddress || Left Join TransHeader.ShipToAddressIDShippingAddress.ID || || Notes: TransDetail records includes both parent and child items.Parent Items are always shown.Child Items are only shown when the appropriate parameter is set.Top Level Line Items have a ParentClassTypeID 10000 (variations).Child Items have a ParentClassTypeID 10100 (line items).
Important Data Fields
||Item || Field || Notes || ||Order
Number ||TransHeader.OrderNumber || || ||Line Item Description ||TransDetail.Description || || ||Product ||TransDetail.GoodsItemCode || || ||Quantity ||TransDetail.Quantity || || ||Line Item Description ||TransDetail.HTMLShortFormat ||This must be formatted as HTML in Crystal Reports
and set to auto-expand || ||Line Item Subtotal ||TransDetail.SubTotalPrice or
TransDetail.MeandSonSubTotalPrice ||If suppressing child items, use the
MeAndSonSubtTotalPrice, otherwise
display the SubTotalPrice || ||Line Item Number ||TransDetail.LineItemNumber || || ||Order Created Date ||TransHeader.OrderDate || || ||Company Name ||Account.CompanyName || || ||Salesperson ||Employee.FirstName+“”+
Employee.LastName || || ||Payment Info || || || Group By
The following group by options should be supported:
||Description ||Level ||Optional ||Group ID field ||Group Display Field ||Notes || ||Order ||1 ||No ||TransHeader.ID ||TransHeader.OrderNumber || || Sort Options
The data within a group will always been sorted by the non-optional fields below and (if present) the user is given options to sort by the other fields.
||Description ||Sort Text ||Level ||Optional ||Notes || ||Order ||TransHeader.OrderNumber ||1 ||No || || ||Line Item ||TransDetail.LineItemNumber ||2 ||No || || There are no user defined sort of options in this report.
User 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 ||Default ||Data Filter ||Notes || ||Show Child Line Items || ||False ||(TransDetail.ParentClassTypeID
10400
or ShowChildItemsTrue) ||Used to include child line items. Top level items have variations (10400) as their parent items.
Note:If Child Line Items are not shown, the line item must show TransDetail.MeandSonsSubTotalPrice, otherwise it displays the TransDetail.SubTotalPrice || ||Show Line
Item Totals || ||True ||n/a ||Hide or Display the MeAndSonSubTotalPrice or the SubTotalPrice on the individual line item. || ||Show Unit Price || ||True ||n/a ||Hide or Display the unit price (MeAndSonSubTotalPrice/Quantity or SubTotalPrice/Quantity) for each line item. Note: You must check for Quantity0 and avoid division by zero. Also if Quantity0, display “n/a” as the unit price for that line. || ||Show
Payment
Terms || ||True ||n/a ||Hide of Display the Payment Terms at the bottom of the order|| ||Hide Line
Item
Description || ||True ||n/a ||Hide or Display the HTML Line item description || ||Show
Notes || ||True ||n/a ||Hide or Display the TransHeader.Notes at the bottom of the order|| ||Show
Entered By || ||True ||n/a ||Hide or Display the Entered By Information || ||Show Tax
Exemption
ID || ||True ||n/a ||Hide or Display the customers tax exemption information. Account.TaxNumber and Account.TaxNumberExpDate if not null. Note:if TaxNumber is null, do not display date either. || ||Show Store
Tax ID || ||True ||n/a ||Hide or Display the Store's Tax Exemption ID at the top of the order || ||Show
Payments || ||True||n/a || || ||Use Customers AP Contact || ||False ||n/a ||Instead of the Invoice Contact, show the Company's AP Contact for this invoice || ||Print Fold Lines || ||True || || || ||Print Payments || ||True || || || ||Show Customer Tax
ID as PST # || ||False || ||Hide or Display Account.Tax Number PST # as Label (Canadian only) || ||Show Discounts || ||True || || || ||Show Service Ticket Stub || ||True || || || ||Show Signature Block || ||True || || || ||Display Print As
For Product || ||False || ||Instead of TransDetail.
_ use Product.___
Print Images True If present, show thumbnails for each Line Item
Print Return Stub True Block of Text
Show Customer Tax ID True
Show Line Item Taxes False
Show Shipping Address False Displays “Ship To” Block with Address
Show Split Taxes False Separates PST/GST (Canadian Use)
Show Unit Price False

Summary Fields

There are no summary fields for this report.

Export

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

Test Cases