Work Order 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].

      • The estimated part usage (TransPart) links for each line item.
        • The part that is estimated to be used (Part)
        • The station (Station) this part is used in.

    • The shipments assigned to this Order (Shipment)
      • SUBREPORT: The shipment details for each shipment (Function ShipmentItemsTable(ShipmentID int) )
        • The line items shipped (TransDetail) in that detail.

    • The Company record (Account).
    • The Primary Contact record (AccountContact).
    • The Salesperson for the Order(Employee).
    • The Employee who Entered the Order (Employee).
    • The Division Name for the Order (EmployeeGroup).

Tables Accessed

Purpose
Table
Linkage Information
Filter Information
OrderLevel
TransHeader

TransHeaderID=param
LineItemLevel
TransDetail
TransDetail.TransHeaderID = TransHeader.ID

ShippingAddress
Address
OrderLevel.ShippingAddressID = ShippingAddress.ID

ShipToCompany
Account
OrderLevel.ShippingCompanyID = ShipToCompany.ID

ShippingContact
AccountContact
OrderLevel.ShippingContactID = ShippingContact.ID

CustomerInfo
Account
Left Join Account.ID = TransHeader.AccountID

ContactInfo
AccountContact
Left JoinAccountContact.ID = TransHeader.ContactID

Salesperson
Employee as Salesperson
Left Join Salesperson.ID = TransHeader.Salesperson1ID

EnteredBy
Employee as EnteredBy
Left Join EnteredBy.ID = TransHeader.EnteredByID

Division
EmployeeGroup as Division
Left Join Division.ID = TransHeader.DivisionID

WorkOrderLinks.jpg

Shipment SubReport

Purpose
Table
Linkage Information
Filter Information
Shipment Detail
database View "ShipmentDetail"

ShipmentDetail.TransheaderID
=Pm-?Transheader.ID
Shipments
Shipments
ShipmentDetail.TransHeaderID = Shipments.TransHeaderID

TransDetail
TransDetail
ShipmentDetail.LineItemID --> TransDetail.ID

Account
Account
Shipments.AccountID --> Account.ID

AccountContact
AccountContact
Shipments.ContactID --> AccountContact.ID

ShippingMethod
ShippingMethod
Shipments.CarrierID --> ShippingMethod.ID

AddressLink
AddressLink
Shipments.ShipToAddressLinkID --> AddressLink.ID

Address
Address
AddressLink.AddressID --> Address.ID


ShipmentSubLink.jpg

Parts List SubReport

Alias
Table
Link or Filter
Part_Link
TransPart
Part_Link.TransheaderID = Pm-?TransDetail.ID
Part
Part
Part.ID = Part_Link.PartID
GoodsItemPartLink
GoodsItemPartLink
GoodsItemPartLink.ID = TransPart.PartLinkID
PartsStation
Station
PartsStation.ID = Part.StationID



Order
Number
TransHeader.OrderNumber

Line Item Description
TransDetail.Description

Product
TransDetail.GoodsItemCode

Quantity
TransDetail.Quantity

Line Item Description
TransDetail.HTMLLongFormat
This must be formatted as HTML in Crystal Reports and set to auto-expand
Line Item Subtotal
TransDetail.SubTotalPrice
Suppressed based on options
Line Item Number
TransDetail.LineItemNumber

Order Created Date
TransHeader.OrderDate

Company Name
Account.CompanyName

Salesperson
Employee.FirstName + " " + Employee.LastName


Group By

Main Report:

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

Order
2
No
OrderLevel.ID
OrderLevel.OrderNumber

LineItem
3
No
LineItemLevel.ID as formatted by @LineItemSort


PartsList Subreport:

grouped by Station.StationName

ShipmentView subreport

grouped first by Shipments.ShipmentNumber and second by TransDetail.LineItemNumber

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

Line Item Station
TransDetail.LineItemNumber
3
No
This Sort would only seem to apply to the subreport; therefore delete.
Part Type
Part.PartType
4
No
Not previously done in the subreport! delete?????
Part Name
Part.ItemName
5
No
Not previously done in the subreport! delete?????


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 Line Item Totals

True
n/a
Hide or Display the SubTotalPrice on the individual line item.
Show Service Ticket Stub

True


Print Images

True

If present, show thumbnails for each Line Item
Show Parts Information

True
If true, shows the parts subreport

Show Shipping Information

True
If true, shows the shipment subreport



Summary Fields

There are no summary fields for this report.

Export

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

Test Cases