1. Oracle Warehouse Management Cloud Web Reports Gen2 Report Creation

1Web Report Gen2

Web Report Gen2

Web Report Gen2 has been created to provide more flexibility and better performance by exposing most of the WMS and WFM categories (ie. tables) at a fine-grained level. The earlier Gen1 combined many tables together with inbuilt joins and exposed them as higher level categories at the expense of some flexibility and some performance.

Gen2 avoids prebuilt joins such that the resulting database queries will perform better. In order to help you perform joins, please follow the guidelines provided below.

The capabilities of WebReports itself have remained the same in terms of end user formatting and layout capabilities and the type of reports.

The following are some guidelines that one must follow for creating joins.
  1. There There is a company parameter that controls the option to create aliases for categories. This is controlled through the parameter WR2_CATEGORY_ALIAS_MODE.

    The values are as follows:

    The default empty string mode for WR2_CATEGORY_ALIAS_MODE:

    • allows aliases to be used
    • provides join guidance by disabling unjoinable categories
    • but restricts editing of categories after report creation

    NON_ALIAS_MODE:

    • prevents aliases from being used
    • provides join guidance by disabling unjoinable categories
    • allows editing of categories after report creation

    ALIAS_MODE:

    • allows aliases to be used
    • does not provide any join guidance
    • allows editing of categories after report creation
  2. Every category in the table, see the complete list in Appendix 1 that exposes contains an identity key (id) that uniquely identifies a row in the table. This is not visible when seeing the columns on that table while building the report.
  3. Some of the categories will hold foreign keys to other tables. Foreign keys are just columns with names ending in _id per our convention.
    For example: The category Container, has the following:
    • Id – identity column for Entity Container
    • FACILITY_ID – Foreign key to entity Facility’s id
    • COMPANY_ID – Foreign key to entity Company’s id

    Refer to Appendix 2 for a list of entities and foreign keys these entities refer to.

  4. Some of these keys will not show up during the report layout creation. But all them will show up when one is defining the joins.
  5. The user doesn’t have to know which category can be joined to which other categories and how. WebReports handles that complexity and only allows you to join categories that are “joinable”.
  6. Some of the categories are “look up” tables that define a list of predefined values. For example: status. Most of the categories that track status will have corresponding status lookup table named as <category_name>_status. Following are the status categories:
    • allocation_status
    • appointment_status
    • asset_status
    • audit_status
    • bridge_status
    • carrier_status
    • container_status
    • container_vas_status
    • dock_status
    • ib_shipment_status
    • inventory_status
    • invoice_status
    • load_status
    • order_dtl_status
    • order_status
    • pallet_status
    • parcel_manifest_status
    • purchase_order_status
    • qc_status
    • record_status
    • route_hdr_status
    • run_status
    • stage_status
    • stop_status
    • task_status
    • vas_execution_status
    • wave_mhe_status
    • wave_template_status
    • wms_activity_status
    • wms_activity_track_status
  7. Among the categories exposed, the following relationships are important to keep in mind while performing joins. The following entities will typically get used very often in reports:
    • Inventory
    • Container
    • Location
    • Item
    • Facility
    • Company
    • Allocation
    • Task
    • Load
    • Stop
    • StopDtl

Inventory

Inventory is a record representing actual inventory stored in a location, either in part or in full. It has an Item with quantity and attributes and comes into existence when inventory is received or created. Inventory points to an item.

Inventory is always inside a container or a location (active). A container or Location can hold multiple inventory records. Inventory can be inbound (received, QC, etc.), on-hand (located) or outbound (allocated, packed, in packing, loaded etc.) Inventory holds the address of where it is, a container (Inbound or Outbound) or a location.

Container

Container can be of type Inbound (I) or Outbound (O).

A Container has a field cur_location_id that holds the address of where it is i.e. the location, unless it’s on a pallet, in which case the pallet will have the location.

Allocation

Allocation(s) – are reservation(s) of Inventory (inbound or on-hand) for a need specified by an Order(s). Allocations get created when Wave (Picking or Replenishment) is run. When Allocations are created by wave. they usually have the reference to the Wave Number on them.

Allocations also get created when one does cross docking or distribution while receiving.

Allocations can also get created when Orders are allocated directly using Direct Allocation or Pick and Allocate transactions.

Allocations can be grouped together to form a task.

An allocation record associates an order detail (order line) record with specific inbound inventory (from_inventory_id) which is used to fulfill a portion of the order detail and also has a reference to the outbound inventory (to_inventory_id) into which it is (or will be) packed.

There are certain entities which come from outside the WMS like PO, Inbound Shipment and Order. These entities have header/Detail type of structure.

For example:

One Order can have many details which is basically a set of items that the Order is for with ordered or requested quantity and other attributes if any:
  1. When creating a report in Web Reports choose the categories and then subsequently define the sorting and filtering. Form the basic layout. Then finish the report. This is when the Web Reports joins are automatically created. These joins are usually created automatically based on the categories chosen and the foreign keys present on them. Please navigate to the Advanced Join section and review the joins to make sure they are the desired joins created. Otherwise you can redo the joins or modify them appropriately to suit your needs.
  2. In Web Report Gen2 you do have option to create aliases for the categories. So there could be sometimes need to use the same category twice in joins with different aliases. Such joins can be performed as well. You might run into this with the allocation entity which refers to the inventory table via two separate foreign keys from_inventory_id and to_inventory_id. To make this easier to user we have exposed the inventory table, not just as the inventory category but also as from_inventory and to_inventory. The allocation category is joinable only with from_inventory and to_inventory and not with the regular inventory category (which exists for joining with other categories).
  3. Always recommended to filter the main driving categories by facility and company appropriately for which the report is being built. Note that regardless of this WebReports will automatically filter by the user’s eligible facilities and companies.