Gen1 and Gen2 Categories
This section contains Web Reports Gen1 Categories and the equivalent Web Reports Gen2 categories. It is important to note the following:
- While converting the reports, DO NOT take the categories directly from Web Reports Gen1 and convert to equivalent categories in Web Reports Gen2 from the following table.
- The following table is provided as a reference to illustrate that Web Report Gen1 categories were more abstracted and encapsulated at a higher level, whereas Web Report Gen2 categories are not.
- Look at the mapping below and identify the Web Report Gen1 categories from the left and identify the Web Report Gen2 categories from the right (unique set) and then identify the join condition required to join them appropriately as per the report requirements. Also choose the bare minimum number of categories that are required for the report.
- In some cases, you may need to create multiple aliases of the same table in order to join with foreign keys for different logical entities.
For example: Allocation category has from_inventory_id and to_inventory_id as foreign keys but are pointing to the inventory table but they two represent two different logical entities i.e. ( from Inventory - is inbound inventory) and ( to inventory - is outbound inventory).
- In many cases, all the joins may not be required if you are not using data from the joined tables, so be aware of this, as this could provide better performance.
- Important: Web Reports Gen2 does create some of the obvious joins automatically. But you must go check the joins created and see if there are any other additional joins to be added or any redundant joins to be removed. Also avoid all duplicate joins.
- Also, anytime there is a change in a category or any options in a category (for
example, one suppresses duplicates using the check box) then Web Reports will try to
regenerate the joins again. It may seem frustrating, but this behavior is expected
as some settings have changed and the application is recreating joins to suit those
settings. It is important to double check again to see if joins have adjusted again
correctly. To avoid regeneration of joins, it is good practice to decide upfront
what categories and columns are required to minimize this.
Web Report Gen1 Category Equivalent Web Report Gen2 Categories and Related Joins Allocation - allocation
- allocation_type (inner)
- allocation_status (inner)
- allocation_run_hdr (left outer)
- uom (use alias to create two uom categories if you have to see alloc_uom and cartonize_uom) (left outer)
- lpn_type (left outer)
- task_creation (left outer)
AllocationFromContainer - container
- container_status ( inner)
- facility ( inner)
- company ( inner)
- lpn_type ( inner)
- pallet ( left outer)
- pallet_status ( left outer)
- audit_status ( left outer)
AllocationFromContainerLocation - location
- company ( for dedicated company ) (at location level) (inner)
- company ( destination company on location) ( left outer)
- Facility ( inner)
- Item_assignment_type ( inner)
- location_type ( inner)
- inventory_lock ( left outer)
- location_size_type ( left outer)
- mhe_system ( left outer)
- mhe_type ( left outer)
- item ( for item dedicated in a location) ( left outer)
AllocationFromContainerShipment - ib_shipment
- company (inner)
- facility (inner)
- ib_shipment_status (inner)
- ib_shipment_type (left outer)
- inventory_lock (left outer)
AllocationFromInventory - inventory
- inventory_attribute ( left outer)
- facility ( inner)
- inventory_status ( inner)
- batch_number ( left outer)
AllocationFromInventoryLocation - location
- Company ( for dedicated company at location level) (inner)
- Facility ( inner)
- Item_assignment_type ( inner)
- location_type ( inner)
- inventory_lock ( left outer)
- location_size_type ( left outer)
- mhe_system ( left outer)
- mhe_type ( left outer)
- item ( for item dedicated at location) ( left outer)
- task_zone ( left outer)
- replenishment_zone ( left outer)
AllocationMheSystem - mhe_system
- mhe_type ( inner )
- facility ( inner)
AllocationToContainer - container
- facility ( inner)
- company ( inner)
- lpn_type ( left outer)
- pallet ( left outer)
- pallet_status ( left outer)
- audit_status ( left outer)
AllocationToContainerLocation - location
- company (for dedicated company at location level) (left outer)
- facility (inner)
- Item_assignment_type (inner)
- location_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item (for item dedicated at location level) (left outer)
AllocationToInventory - Inventory
- inventory_attribute (left outer)
- facility (inner)
- inventory_status (inner)
- batch_number (left outer)
AllocationToInventoryLocation - location
- company ( for dedicated company at location level) (left outer)
- facility (inner)
- Item_assignment_type (inner)
- location_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( for item dedicated at location level) (left outer)
- task_zone (left outer)
- replenishment_zone (left outer)
Appointment - appointment
- dock_type (inner)
- facility (inner)
- appointment_status (inner)
- value_type (matching value type) (left outer)
- dock (left outer)
Batch Number - batch_number
- facility (inner)
- company (inner)
- inventory_lock (left outer)
BillingItem - billing_item
- company (inner)
- history_activity (left outer)
- column_name (activity group) (left outer)
Carrier - carrier
- company (inner)
- carrier_status (inner)
- std_carrier (left outer)
- carrier_type (left outer)
CarrierFacility - carrier_facility
- facility (inner)
- carrier_integration_type (left outer)
CarrierLpn - carrier_lpn
- container (inner)
- facility (inner)
- company (inner)
CCAdjustment Header Detail - cc_adjustment_hdr
- cc_adjustment_dtl (inner)
- inventory_attribute (left outer)
- cc_adjustment_status (inner)
- company (inner)
- facility (inner)
- cc_adjustment_warning (left outer)
- cc_warning (left outer)
Company - Company
Container - Container
- container_status (inner)
- facility (inner)
- company (inner)
- lpn_type (left outer)
- pallet (left outer)
- pallet_status (left outer)
- audit_status (left outer)
ContainerCurrLocation - Location
- Facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- company (dedicated company on location) (left outer)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( on dedicated item at location) (left outer)
ContainerLocks - container_lock_xref
- inventory_lock (inner)
- Container (inner)
- Inventory (inner)
- inventory_status (inner)
- inventory_attribute (left outer)
- item (inner join with inventory)
- facility ( inner)
- company ( inner)
- putaway_type (left outer with item)
ContainerPrevLocation - Location
- Facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- company (dedicated company on location) (left outer)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( on dedicated item at location) (left outer)
ContainerRcvdLoad - load
- company (inner)
- facility (inner)
- load_status (inner)
- carrier (left outer)
- carrier_type (left outer)
- trailer (left outer)
ContainerRcvdShipment - ib_shipment
- company (inner)
- facility (inner)
- ib_shipment_type (left outer)
- inventory_lock (left outer)
Dock - dock
- facility (inner)
- dock_type (inner)
- dock_status (inner)
- location (inner)
IBShipmentPrepackItemResidual - ib_shipment_item_residual
- facility (inner)
- company (inner)
IBShipmentSerialNbr - ib_shipment_srl_nbr
- company (inner)
- facility (inner)
InboundShipmentItem - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
InboundShipmentLoad - load
- company (inner)
- facility (inner)
- load_status (inner)
- carrier (left outer)
- carrier_type (left outer)
- trailer (left outer)
InboundShipmentVendorPerf - ib_shipment_vendor_perf
- vendor_perf_code (inner)
- ib_shipment (inner)
- company ( for vendor) (inner)
- uom (left outer)
Inventory - inventory
- facility (inner)
- inventory_status (inner)
- inventory_attribute (left outer)
- batch_number (left outer)
- allocation (left outer)
InventoryItem - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
InventoryItemLocation - inventory
- facility (inner)
- item (inner)
- putaway_type
- inventory_attribute (left outer)
- item_characterstics (inner)
- putaway_type (left outer)
- item_metrics (inner)
- item_struct (inner)
- item_hierarchy_defn (this will be one alias per hierarchy level) (left outer)
- item_group_defn (left outer)
- inventory_status (inner)
- batch_number (left outer)
- allocation (left outer)
- company (for item ,dedicated company on location and destination company on location) (left outer)
- location (full join)
- facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item (for dedicated item on location) (left outer)
- task_zone (left outer)
- replenishment_zone (left outer)
- property_prompt_mode (left outer)
InventoryLocation - location
- company ( more aliases may be required) (left outer)
- facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( for dedicated item on location) (left outer)
- task_zone (left outer)
- replenishment_zone (left outer)
Invoice - invoice
- invoice_dtl (inner)
- facility (inner)
- company (inner)
- invoice_status (inner)
- history_activity (left outer)
Item - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( multiple aliases may be required) ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
ItemFacilityMaterialHazardType - item_facility_material_hazard_type
- item_facility (inner)
- item (inner)
- facility (inner)
- material_hazard_type (inner)
ItemImage - item_image
- item (left outer)
ItemMaterialHazardType - item_material_hazard_type
- item (inner)
- material_hazard_type (inner)
ItemPrepack - item_pre_pack
- item (inner)
Location - location
- company ( for dedicated_company and destination company) (left outer)
- facility (inner)
- location_type (inner)
- item_assignment_type (inner)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- item ( for item dedication) (left outer)
- replenishment_zone (left outer)
- task_zone (left outer)
Order - order_dtl
- order_hdr (inner)
- inventory_attribute (left outer)
- company ( for destination company and company on Order itself) (inner)
- order_status (inner)
- order_type (inner)
- facility ( for destination facility, shipto facility and facility on the Order itself) ( inner for facility and for destionation and shipto it is left outer)
- lpn_type (left outer)
- ship_via (left outer)
- batch_number (left outer)
OrderCompany - Company
OrderDestinationCompany - Company
OrderFacility - Facility
OrderInstructionsDetail - order_instructions
- order_dtl (left outer)
- order_hdr (inner)
- order_instruction_type (inner)
- facility (inner)
- company (inner)
OrderInstructionsHeader - order_instructions
- order_hdr (left outer)
- order_instruction_type (inner)
- facility (inner)
- company (inner)
OrderItem - Item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
OrdersOnLoad - allocation
- order_dtl (inner)
- order_hdr (inner)
- order_status (inner)
- inventory ( from and to require different aliases and must be left outer joins)
- container (left outer)
- container_status (inner)
- ob_stop_dtl (left outer)
- ob_Stop (left outer)
- load (left outer)
- load_status (left outer)
- item (inner)
- inventory_attribute ( for to inventory) (left outer)
- batch_number ( for both from and to inventory) (left outer
- facility (inner)
OrdersOnLoadExtended - allocation
- order_dtl (inner)
- order_hdr (inner)
- order_status (inner)
- inventory ( from and to) (left outer)
- container (left outer)
- container_status (left outer)
- ob_stop_dtl (left outer)
- ob_Stop (left outer)
- load (left outer)
- load_status (left outer)
- item (inner)
- inventory_attribute ( for to inventory) (left outer)
- batch_number (left outer)
- facility (inner)
- order_type (inner)
- ship_via (left outer)
- facility ( shipto) (left outer)
- srl_nbr_inventory (on to_inventory) (left outer)
- serial_nbr (left outer)
- item_characterstics (inner)
OrderStageLocation - location
- company ( for dedicated company) (left outer)
- facility (inner)
- item_assignment_type (inner)
- location_type (inner)
- company ( for destination company) (left outer)
- inventory_lock (left outer)
- location_size_type (left outer)
- mhe_system (left outer)
- mhe_type (left outer)
- item ( for item dedication ) (left outer)
OutboundLoad - load
- company
- facility
- carrier
- carrier_type
- trailer
OutboundLoadCompany - company
OutboundLoadFacility - facility
OutboundStop - ob_stop_dtl
- ob_stop_hdr
- stop_status
- container
- facility
Pallet - pallet
- company
- facility
PalletHistory - pallet_history
- facility
- company
Parcel - parcel_manifest
- facility
- parcel_manifest_status
- parcel_shipment
ParcelLpn - parcel_shipment_dtl
- parcel_shipment
- parcel_manifest
- carrier_lpn
PurchaseOrder - purchase_order_dtl
- purchase_order_hdr
- facility
- company
- inventory_lock
- purchase_order_type
PurchaseOrderItem - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)
PutAwayPriority - putaway_priority
- facility (inner)
- location_type (inner)
- putaway_method
- putaway_search_mode
- location_size_type
- putaway_type
QCVerificationHistory - verification_result_dtl
- verification_result_hdr
- qc_status
- facility
- company
ReplenishmentZone - replenishment_zone
- facility
Route - route_dtl
- route_hdr
- facility
- company
SerialNbrRepository - serial_nbr
- Item
- company
- facility
SerialNbrInvetnory - serial_nbr_inventory
- serial_nbr
- inventory
ShipVia - ship_via
- company ( inner)
- std_carrier_service ( left Outer)
ShipViaDetail - ship_via_dtl
- ship_via (inner)
- std_carrier_accessorial (inner)
SimpleInventory - inventory
- facility (inner)
- inventory_status (inner)
- inventory_attribute (left outer)
- batch_number (left Outer Join)
Task - Task
- facility (inner)
- task_status (inner)
- task_type (inner)
TaskCurrLocation - location
- company ( for dedication company) ( inner join)
- facility ( inner)
- item_assignment_tpye ( inner)
- location_type (inner)
- company ( destination company) ( left outer join)
- inventory_lock ( left outer join)
- location_size_type ( left outer join)
- mhe_type ( left outer join)
- item ( for item dedication) ( left outer join)
TaskNextLocation - location
- company ( for dedication company) ( inner join)
- facility ( inner)
- item_assignment_tpye ( inner)
- location_type (inner)
- company (destination company) (left outer join)
- inventory_lock (left outer join)
- location_size_type (left outer join)
- mhe_type (left outer join)
- item (for item dedication) (left outer join)
Trailer - tailer
- location (left Outer join)
WMSActivityDtl - wms_activity_dtl
- wms_activity (inner)
- wms_activity_code (inner)
- facility (inner)
- company (inner)
WMSACtivityHdr - wms_activity
- wms_activity_status (inner)
- facility (inner)
- company (inner)
WorkOrder - work_order_hdr
- work_order_status (inner)
- work_order_type (inner)
- facility (inner)
- company (inner)
- work_order_kit (inner)
- item ( for work Order Kit) (inner)
- item ( for work Order Component)
- batch_number ( for work Order )( left outer)
- inventory_attribute ( for work Order Component) ( left Outer)
- inventory_attribute ( for work Order Kit) ( left Outer)
- work_order_component (inner)
- uom ( inner)
- location (for work order kit) ( left Outer)
WorkOrderComponentItem - item
- company (inner)
- putaway_type (left outer)
- item_characterstics (left outer)
- lpn_type (left outer)
- item_metrics (left outer)
- item_struct (left outer)
- item_hierarchy_defn ( one for each hierarchy level) (left outer)
- item_group_defn (left Outer)
- property_prompt_mode ( left outer)
- harmonized_tariff ( left outer)
- uom (left outer)