Assignment: fromDate & thruDate Analysis in HotWax Commerce OMS

Table of Contents

• Part 0: All Scenarios Overview
• Part 1: Step-by-Step Data Creation in Each Flow
• Part 2 (T-1): Entities with fromDate & thruDate — Sorted List
• Part 3 (T-1): How fromDate/thruDate Affects Datamodel & Business Processes
• Part 4 (T-2): User Actions That Trigger New Records & Code for Correct Data Fetching

Part 0: All Scenarios Overview

A. Order Scenarios

B. Return Scenarios

Part 1: Step-by-Step Data Creation

Flow 1: Online Delivery Order (Full Charges)

Each step below shows which entities get records created or updated.

Step 1: Order Import from Shopify

When the Import Orders job runs, the Shopify order is transformed via prepareTransformedShopifyOrderPayload.groovy and persisted:

[!IMPORTANT] Entities marked with ⭐ have fromDate & thruDate fields. These are the temporal entities where the "from/thru date pattern" applies.

Step 2: Order Approval

Step 3: Brokering / Inventory Allocation

Step 4: Store Fulfillment (Pack & Ship)

Step 5: Address Added Later (Unfulfilled scenario)

Flow 2: POS Completed Order

POS orders arrive as already-completed:

Flow 3: Return (Normal Return with Restock)

Flow 4: Exchange (e.g., POS Equal Exchange)

All return flow entities PLUS:

Part 2 (T-1): Entities with fromDate & thruDate

Sorted Master List

[!NOTE] PK = fromDate is part of the Primary Key (composite key pattern). Non-PK = fromDate is a regular field (not part of PK). Entities are grouped by domain and sorted alphabetically within each group.

Order Domain

Return Domain

Product Domain

Facility Domain

Party Domain

Shipment Domain

Security Domain

Other

Part 3 (T-1): Impact on Datamodel & Business Processes

Why fromDate & thruDate Exist — The Temporal Pattern

[!IMPORTANT] The fromDate/thruDate pattern implements temporal versioning in the data model. Instead of updating a record in-place (losing history), a new record is created with a new fromDate, and the old record's thruDate is set to "now". This preserves full audit history of every change.

Pattern Rules:

1. fromDate as PK → The entity supports multiple concurrent valid records for the same logical relationship, differentiated by time
2. thruDate = NULL → The record is currently active/valid
3. thruDate = <timestamp> → The record was expired/superseded at that time
4. New record → A new row with fromDate = now and thruDate = NULL replaces the old active record

How This Changes the Business Process

1. Order Identification Lookup

When the system needs to find an order by its Shopify ID:

• Without date filter: Could return expired/old mappings if a Shopify order ID was remapped
• With date filter: Returns only the current valid mapping

2. Product SKU Lookup (GoodIdentification)

When resolving a product from a SKU during order import:

• A product's SKU can change over time (new GoodIdentification row, old one expired)
• Must filter by date to get the current SKU→productId mapping

3. Customer Address Changes (PartyContactMech)

When a customer updates their address:

• Old PartyContactMech gets thruDate = now
• New PartyContactMech inserted with fromDate = now
• Ship-to resolution must use date filter to get the current address

4. Facility-Store Mapping (ProductStoreFacility)

When a facility is added/removed from a store:

• Old mapping gets expired
• During brokering, only currently-active facilities should be considered

5. Product Category Membership (ProductCategoryMember, ProdCatalogCategory)

Pre-order/backorder detection relies on category membership:

• A product can be moved in/out of pre-order category
• Must check current membership, not historical

Part 4 (T-2): User Actions & Correct Data Fetching

Critical Question: When does a user action create a NEW record in a temporal entity?

Code Patterns for Correct Date-Filtered Data Fetching

The codebase uses two primary patterns to ensure only current (non-expired) records are fetched:

Pattern 1: filterByDate() — Post-Query Filter

// Used AFTER fetching a list, filters in-memory
List results = ec.entity.find("EntityName")
    .condition([field: value])
    .list()
    .filterByDate("fromDate", "thruDate", ec.user.nowTimestamp)

Real examples from the codebase:

// 1. Looking up ReturnIdentification to find existing return
//    File: createShopifyInProgressReturn.groovy:42
List<Map> returnIdentifications = ec.entity.find("co.hotwax.order.return.ReturnIdentification")
    .condition([returnIdentificationTypeId: "SHOPIFY_RTN_ID", idValue: shopifyReturnId])
    .list().filterByDate("fromDate", "thruDate", nowTs)

// 2. Looking up GoodIdentification to resolve product from SKU
//    File: createShopifyInProgressReturn.groovy:172
List fallbackProductIds = ec.entity.find("org.apache.ofbiz.product.product.GoodIdentification")
    .condition([goodIdentificationTypeId: "SKU", idValue: sku])
    .list().filterByDate("fromDate", "thruDate", nowTs)

// 3. Checking ProdCatalogCategory for pre-order detection
//    File: prepareTransformedShopifyOrderPayload.groovy:640
def prodCatalogCategories = ec.entity.find("org.apache.ofbiz.product.catalog.ProdCatalogCategory")
    .condition("prodCatalogId", productCatalogId)
    .condition("prodCatalogCategoryTypeId", categoryTypeId)
    .useCache(true).list().filterByDate("fromDate", "thruDate", ec.user.nowTimestamp)

Pattern 2: conditionDate() — Query-Level Filter

// Used as part of the query builder, filters at DB level (more efficient)
List results = ec.entity.find("EntityName")
    .condition([field: value])
    .conditionDate("fromDate", "thruDate", null)  // null = now
    .list()

Real examples:

// 1. Finding product associations (marketing package components) during return restocking
//    File: createShopifyCompletedReturn.groovy:286
List<Map> productAssocs = ec.entity.find("org.apache.ofbiz.product.product.ProductAssocAndFrom")
    .condition([productId: productId, productTypeId: "MARKETING_PKG_PICK",
                productAssocTypeId: "PRODUCT_COMPONENT"])
    .conditionDate("fromDate", "thruDate", null)
    .selectField("productIdTo,quantity")
    .list()

// 2. Security group permission check during login
//    File: OfbizShiroRealm.groovy:191
.conditionDate("fromDate", "thruDate", eci.user.getNowTimestamp())
.disableAuthz().orderBy("-fromDate").list()

// 3. Product feature lookups in PDF templates
//    File: PdfTemplateData.xml:108
ec.entity.find("co.hotwax.product.feature.ProductFeatureAndAppl")
    .condition("productId", productId)
    .conditionDate("fromDate", "thruDate", ec.user.getNowTimestamp())
    .list()

What Goes Wrong Without Date Filtering?

[!CAUTION] If filterByDate / conditionDate is NOT used on temporal entities, the following bugs occur:

The Fix: Always Apply Date Filters

[!TIP] Rule of thumb: Any time you query an entity that has fromDate/thruDate and you want the current record, you MUST use one of:

• .list().filterByDate("fromDate", "thruDate", timestamp) — for in-memory post-filtering
• .conditionDate("fromDate", "thruDate", timestamp) — for DB-level filtering (preferred for large datasets)

Use null as the timestamp parameter to default to "now".

Summary: Entities Most Critical in Order/Return Flows

These are the entities with fromDate/thruDate that are actively queried with date filtering in the order import, return creation, and fulfillment code:

Key Takeaways

1. fromDate/thruDate is NOT just metadata — it is a core part of the Primary Key in most temporal entities, meaning the same logical relationship can have multiple rows differentiated only by time.

2. Every user action that "changes" a temporal relationship creates a new row (fromDate=now) and expires the old row (thruDate=now), rather than updating in place.

3. Data fetching code MUST always use filterByDate or conditionDate when querying temporal entities to avoid stale/expired data.

4. Two patterns exist: filterByDate (post-query, in-memory) and conditionDate (query-level, DB-side). The latter is more efficient for large datasets.

5. The most impactful temporal entities in the order/return flow are: GoodIdentification, ReturnIdentification, OrderIdentification, ProductAssoc, PartyContactMech, ProductStoreFacility, and ProdCatalogCategory.