This util helps you query Pricefx tables based on the configurations and group the looked-up data.

Sample Use Case

Let’s use the quote context to simulate the situation. Given a table schema like this:

The Product Discounts table defines the discount value by the customer country and the discount type which is based on the product class from the Products master table.

And, we want to find ‘Product Discounts’ data for quoted products and customers in batches, e.g. line items [“MB-0001”, “MB-0002”] and customer “CD-00001”.

We need the following steps:

Find product class for quoted products, record the product class for the quoted products to be used to group the looked-up data.
Find discount type for the quoted product based on the product class, record the discount type for the quoted products to be used to group the looked-up data.
Find customer country for the quoted customer.
Find Product Discount for the customer country and discount type.
Group the specific Product Discount rows that match the quoted products. This is the most tricky part where you need to define which looked-up data belong to which product class, and then to which product they belong.

As you can see, for each linked table, e.g. Discount Type By Product Class, we need to repeat these steps:

Find data in that table.
Record data to be used in grouping.
Use the data to filter in the next dependent table.

The final result we need is a map of target table data that are grouped for each product, e.g.:

["MB-0001": [customerCountry: "Korea", discountType: "%", discountValue: "10"],
 "MB-0002": [customerCountry: "Korea", discountType: "abs", discountValue: "20"]]

This util provides a solution for that situation by defining the lookup configuration and data dependency between the tables.

So, you just need to:

Define a table for lookup and tables dependency configuration.
Use the util to find data for specific products, customers, line items, etc.

Factories

The lookup factory provides methods to define the lookup configuration for a specific table.

The filter factory provides methods to define the dependencies between tables.

Let’s see how we query data from the above tables for the specific customer and products.

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory
Script filterFactory = libs.DataLookupLib.ConfigurationBasedLookupFilterFactory

// Build DiscountTypeByProductClass lookup
lookupFactory.with {
    buildLookup("productDiscountType")
    lookupPriceParameter("DiscountTypeByProductClass")
    setLookupAttributes([productClass: "productClass",
                         discountType: "discountType"])
    setSortByAttributes("productClass,-discountType")
}
filterFactory.with {
    buildFilter("productClass")
    setProductValueSource("productClass")
    setGenericValue("*")
    build()
}

// Build ProductDiscounts lookup that depend on the DiscountTypeByProductClass lookup
lookupFactory.with {
    buildLookup("ProductDiscounts")
    lookupProductExtension("ProductDiscounts")
    setLookupAttributes([country      : "customerCountry",
                         discountType : "discountType",
                         discountValue: "discountValue"])
    setSortByAttributes("country, discountType, -discountValue")
}
filterFactory.with {
    buildFilter("discountType")
    // discountType column depend on the DiscountTypeByProductClass lookup
    setLookupValueSource("productDiscountType", "discountType") // discountType attribute of the DiscountTypeByProductClass lookup result
    build()

    buildFilter("customerCountry")
    setCustomerValueSource("country")
    build()
}

// Lookup discounts data for products
Map data = lookupFactory.productBasedLookup(["MB-0001",
                                             "MB-0002",
                                             "MB-0003",
                                             "MB-0004",
                                             "MB-0005"], "CD-00001")

Groups

The initial data sent to the lookup. They can be a list of products, a list of customers, or a list of any objects that the looked-up data should match.

Lookup Factory

The factory provides the methods to build up the lookup configuration.

Define Lookup

A lookup should be defined with a key so that its result can be cached and accessed later.

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    //other setting
}

Lookup Product Master

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupProductMaster()
    //other setting
}

Lookup Product Extension

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupProductExtension("ProductCosts")
    //other setting
}

Lookup Price Competitor

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupProductCompetitor()
    //other setting
}

Lookup Customer Master

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupCustomerMaster()
    //other setting
}

Lookup Customer Extension

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupCustomerExtension("CustomerCashDiscount")
    //other setting
}

Lookup Price Parameter Table

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupPriceParameter("DiscountTypeByProductClass")
    //other setting
}

Define Target Date and Valid to Date

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupPriceParameter("DiscountTypeByProductClass")
    setTargetDateAttribute("targetDate")
    setValidToDateAttribute("validToDate")
    //other setting
}

Target date attribute will be compared with the value from api.targetDate().
Valid to date attribute will be compared with the current date.

Column name and technical name are both valid to use.

Define sortBy Attributes

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupPriceParameter("DiscountTypeByProductClass")
    setSortByAttributes("productClass,-discountType")
    //other setting
}

Column name and technical name are both valid to use.

Define Returned Attributes and Mapping

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupPriceParameter("DiscountTypeByProductClass")
    setLookupAttributes([productClass: "productClass",
                         discountType: "discountType"])
    //other setting
}

Map of attributes should be returned and mapped from the lookup. e.g.:

[productClass: "attribute2",
 discountType: "attribute3"]

will have the result:

[productClass: "A",
 attribute2: "A"
 discountType: "%",
 attribute3: "%"]

Column name and technical name are both valid to use.

Result Grouping

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupPriceParameter("DiscountTypeByProductClass")
    setLookupAttributes([productClass: "productClass",
                         discountType: "discountType"])
    setResultGroupingProperties("productClass")
    //other setting
}

Define the attributes that should be used to group the looked-up data to the provided lookup data. If this is not provided, the generated filter will be used to group data.

Result Selection

Script lookupFactory = libs.DataLookupLib.ConfigurationBasedLookupFactory

lookupFactory.with {
    buildLookup("myLookup")
    lookupPriceParameter("DiscountTypeByProductClass")
    selectAllGroupResults()
    //other setting
}

By default, the first record of each group is returned. If you need to get all records per group, selectAllGroupResults should be used.

Filter Factory

The factory provides methods to build up the table dependencies and to filter data.

The below figure shows how each part of a filter reflects the configuration.

The value source and the value attribute define how the value gets to build the filter.

Filter on Table Column

filterFactory.with {
    buildFilter("CX_CustomerType", "Customer Type") // Build a filter name 'CX_CustomerType' on "Customer Type" column
    // other settings
    build()
}

Or

filterFactory.with {
    buildFilter("CX_CustomerType") // Build a filter name 'CX_CustomerType' 
    setProperty("Customer Type") //on "Customer Type" column
    // other settings
    build()
}

Filter by Product Master Attribute

filterFactory.with {
    buildFilter("PX_ProductCost")
    setProperty("Product Class") // Filter on "Product Class" column
    setProductValueSource("Product Class") //with value come from the "Product Class" from product master
    // other settings
    build()
}

Filter by Product Extension Table Attribute

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setProductExtensionValueSource("PX Name", "PX column")
    // other settings
    build()
}

Filter by Customer Master Attribute

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setCustomerValueSource("Customer attribute")
    // other settings
    build()
}

Filter by Customer Extension Table Attribute

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setCustomerExtensionValueSource("CX Name", "CX column")
    // other settings
    build()
}

Filter by Value

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setStaticValueSource("Value to filter")
    // other settings
    build()
}

Filter by Input Value

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setInputValueSource("Input name to get filter value")
    // other settings
    build()
}

Filter by api.global Value

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setGlobalValueSource("Global key to get filter value")
    // other settings
    build()
}

Filter by api.local Value

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setLocalValueSource("Local key to get filter value")
    // other settings
    build()
}

Filter by Value from Provided Lookup Data

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setValue("attribute name to get on group/item")
    // other settings
    build()
}

Table Dependencies

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setLookupValueSource("Dependent lookup Id", "Dependent lookup attribute")
    // other settings
    build()
}

Filter Operator

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setStaticValueSource("A")
    setOperator("NOT_EQUAL")
    // other settings
    build()
}

Generic Value

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setStaticValueSource("A")
    setGenericValue("*")
    // other settings
    build()
}

Filter Lookup Context

filterFactory.with {
    buildFilter("Filter name")
    setProperty("Filter on column")
    setLookupValueSource("Dependent lookup Id", "Dependent lookup attribute")
    setValueLookupContext([id: "Filter lookup context", cached: [:]])
    // other settings
    build()
}

Lookup Context

The lookup context provides interfaces to drive the lookup behavior.

The lookup context is an optional parameter; if it is not provided, an internal context is initialized.

Data Transformers

This provides the custom converters for looked-up data. There are 2 transformer types.

Group Entries Transformer

The default transformer provides a predefined implementation to select data for each group:

Sort by target date and data generalization (generic value vs. specific value).
Filter data and return based on configuration (all or first match).

When you want to use your own, you can define the context group entries transformer.

def contextBuilder = libs.DataLookupLib.ConfigurationBasedLookupContextFactory

contextBuilder.with {
    buildContext("ProductDiscountsLookupContext")
    //The final result contains only the last discountValue instead of the whole looked-up row, eg.[MB-0001: 10]
    setGroupEntriesTransformer {def group, List<Map> groupEntries, Map lookupContext ->
        return [(group): groupEntries.last().get("discountValue")]
    }
}

The group entries transformer should return the group key and the value for that group. So,

[(group): groupEntries.last().get("discountValue")] or

[group, groupEntries.last().get("discountValue")] should work.

Entry Transformer

When using the default group entries transformer, the whole looked-up row is returned.

If you want to override the behavior, e.g. select only one attribute, you can define the entry transformer.

def contextBuilder = libs.DataLookupLib.ConfigurationBasedLookupContextFactory

contextBuilder.with {
    buildContext("ProductDiscountsLookupContext")
    //The result contains only the discountValue, eg. [MB-0001: [10, 20]]
    setEntryTransformer {Map entry ->
        return entry.discountValue
    }
}

Data Ranking/Sorting

By default, the group entries are sorted by the target date and data generalization (generic value vs. specific value). But you can define your ranking strategy when using the default group entries transformer.

def contextBuilder = libs.DataLookupLib.ConfigurationBasedLookupContextFactory

contextBuilder.with {
    buildContext("ProductDiscountsLookupContext")
    setGroupEntriesRanking {def group, List<Map> groupEntries, Map lookupContext ->
        return groupEntries.sort { Map entry -> entry.discountType }
    }
}

In-memory Data Filtering

You can also filter the looked-up data when working with the default group entries transformer by defining the entry filter closure in the lookup context.

def contextBuilder = libs.DataLookupLib.ConfigurationBasedLookupContextFactory

contextBuilder.with {
    buildContext("ProductDiscountsLookupContext")
    setEntryFilter {Map entry ->
        return entry.discountValue > 10
    }
}

Individual Item Input

In some cases, api.input(inputName) does not give you the correct value of the item input that you want to use to filter data. Then you can define the group input provider to provide the input value.

def contextBuilder = libs.DataLookupLib.ConfigurationBasedLookupContextFactory

contextBuilder.with {
    buildContext("ProductDiscountsLookupContext")
    setGroupInputProvider { String inputName, def lineItem ->
        return lineItem.inputs
                       ?.find { Map input -> input.name == inputName }
                       ?.value
    }
}

Default Lookup Generic Value

The lookup generic value should be defined for each column. You can also define the default one in the lookup context that is used for all column filters.

def contextBuilder = libs.DataLookupLib.ConfigurationBasedLookupContextFactory

contextBuilder.with {
    buildContext("ProductDiscountsLookupContext")
    setDefaultLookupGenericValue ("*")
}

Lookup Debugging

All lookup configurations, filters, looked-up data, etc. are saved into the lookup context for debugging.

You can get that information by providing context to the lookup.

Configuration Based Lookup

Sample Use Case

Factories

Groups

Lookup Factory

Define Lookup

Lookup Product Master

Lookup Product Extension

Lookup Price Competitor

Lookup Customer Master

Lookup Customer Extension

Lookup Price Parameter Table

Define Target Date and Valid to Date

Define sortBy Attributes

Define Returned Attributes and Mapping

Result Grouping

Result Selection

Filter Factory

Filter on Table Column

Filter by Product Master Attribute

Filter by Product Extension Table Attribute

Filter by Customer Master Attribute

Filter by Customer Extension Table Attribute

Filter by Value

Filter by Input Value

Filter by api.global Value

Filter by api.local Value

Filter by Value from Provided Lookup Data

Table Dependencies

Filter Operator

Generic Value

Filter Lookup Context

Lookup Context

Data Transformers

Group Entries Transformer

Entry Transformer

Data Ranking/Sorting

In-memory Data Filtering

Individual Item Input

Default Lookup Generic Value

Lookup Debugging