| Aim of this article | Explains how to configure the Channel Management module: create the validation and allocation logics and at least one Claim Type. |
|---|
| Related sections | Channel Management (Reference Manual) |
|---|
This logic reads data from the Excel sheet and validates the claim – i.e., checks if the claimed discount corresponds with the entries in the quotes. It can also compute values for columns that are empty in the Excel file. The logic is run when the Excel file is uploaded and is called once for each of the items.
To create a validation logic:
- Go to Administration > Calculation Logic > Generic Logic and create a new one.
- Set the logic's Nature to 'Claim'.
- Configure the logic's elements. The logic's content depends on the structure of the Excel file but it must at least:
- read the customer ID from the Claim header
- read the values of the current row
- validate that the current row contains the values necessary for calculation
- validate that the claimed amount is correct
The following two examples represent two elements of a validation logic
// Read the value of ProductID and the QuoteUniqueName column for the current row
def sku = api.getClaimContext().getRow().get("ProductID")
def quoteUN = api.getClaimContext().getRow().get("QuoteID")
def endCustomerId = api.getClaimContext().getRow().get("EndCustomerID")
// Read the customer ID from the Claim header
def customerId = api.currentItem("customerId")
def criticalErrors = []
if (!sku) {
criticalErrors.add("Empty ProductID")
}
if (!customerId) {
criticalErrors.add("Empty EndCustomerID")
}
if (!quoteUN) {
criticalErrors.add("Empty QuoteID")
}
api.global.invalid = !criticalErrors.isEmpty()
// Skips calculation of this row in case of errors
if (api.global.invalid) {
api.criticalAlert(String.join(", ", criticalErrors))
return null
}
api.global.maxDiscountPercent = 0
def p = api.getProduct(sku)
if (!p) {
api.criticalAlert("Non-existing product sku " + sku)
api.global.invalid = true
return null
}
// read the discount rules from the Quote and save the discount values as global variables
// we can fetch data with any api, by doing this, it will look into inputs/outputs JSON and just extract that subtree on "inputs.DiscountPercent.value" path
//(so it is super-efficient in this case, however, might be easier to just load the quote and process it as usual sometimes)
// we probably would want to restrict this to only valid and approved quotes
def qlist = api.find("Q",0,1,"id",["id","customerId","inputs.DiscountPercent.value","expiryDate"],
Filter.equal("uniqueName", quoteUN), Filter.equal("customerId", customerId))
def q = qlist ? qlist[0] : null
if (!q) {
api.addWarning("Non existing quote uniqueName " + quoteUN + " for customer " + customerId)
api.global.invalid = true
} else {
if (endCustomerId) { // claim back for end customer agreed sku specific discounts (using outputs rather than inputs since we need inherited customerId)
def lilist = api.find("QLI",0,1,"id",["outputs.DiscountPercent.result","outputs.Quantity.result"],
Filter.equal("clicId", q.id),Filter.equal("sku",sku),Filter.equal("outputs.EndCustomer.result", endCustomerId))
//Filter.custom(" and {clicId} in (select id from Quote where workflowStatus = 3 or workflowStatus = 4")) //approved=3,no_approval_required=4
def li = lilist ? lilist[0] : null
if (li) {
if (li["outputs.DiscountPercent.result"]) {
api.global.skuDiscountPercent = li["outputs.DiscountPercent.result"] as BigDecimal
}
if (li["outputs.Quantity.result"]) {
api.global.skuMaxQuantityForSkuDiscount = li["outputs.Quantity.result"] as BigDecimal
}
} else { // no sku specific discount, look for folder
def folderlist = api.find("QLI",0,1,"id",["inputs.DiscountPercent.value"],
Filter.equal("clicId", q.id),Filter.isNull("sku"),Filter.equal("inputs.Customer.value", endCustomerId))
def folder = folderlist ? folderlist[0] : null
if (folder["inputs.DiscountPercent.value"]) {
api.global.maxDiscountPercent = folder["inputs.DiscountPercent.value"] as BigDecimal
api.logInfo("folder",api.global)
}
}
}
if (!api.global.maxDiscountPercent) { // no endCustomer specific discount, use distributor one
api.global.maxDiscountPercent = q["inputs.DiscountPercent.value"] as BigDecimal
api.logInfo("q",api.global)
}
api.global.quoteExpiryDate = q["expiryDate"]
}
def sku = api.getClaimContext().getRow().get("ProductID")
def skuDiscount = api.getClaimContext().getRow().get("QuoteDiscountPerItemPercent")
def claimQuantity = api.getClaimContext().getRow().get("ClaimQuantity")
def totalClaimAmount = api.getClaimContext().getRow().get("TotalClaimAmount")
// we read the mapping here, but it is mapping for currently computed claim type, one must ensure that
// the queries against mapping have sense by themselves, i.e. ensure that sku is always set as attribute1 e.g.
def skuDiscountElement = api.getClaimContext().getAttributeName("QuoteDiscountPerItemPercent")
def skuElement = api.getClaimContext().getAttributeName("ProductID")
def claimQuantityElement = api.getClaimContext().getAttributeName("ClaimQuantity")
if (skuDiscount > api.global.maxDiscountPercent) { // it is above the limit without quantity
if (api.global.skuDiscountPercent) {
if (skuDiscount <= api.global.skuDiscountPercent) { // but it is within the limit
// we have just a single quote, so we just take the already computed skuMaxQuantityForSkuDiscount from global,
// then checking already APPROVED and VALIDATED items and checking the difference in quantity
def sumAlreadyClaimedItems = api.find("CLLI", 0, 1000, null, [claimQuantityElement], false,
Filter.equal("claimId", api.currentItem('id')),
Filter.equal(skuElement, sku),
Filter.greaterThan(skuDiscountElement, api.global.maxDiscountPercent)
/*in a real-life scenario, we'd probably want to filter only on validated items or only on approved claims
, Filter.in("validationStatus", "AUTO_VALIDATED", "VALIDATED"),
Filter.custom(" and {claimId} in (select id from Claim where workflowStatus = 3 or workflowStatus = 4"))*/ //approved=3,no_approval_required=4
).collect { it[claimQuantityElement] as BigDecimal }.sum()
api.logInfo("global",api.global)
api.logInfo("sumAlreadyClaimedItems", sumAlreadyClaimedItems)
if (api.global.skuMaxQuantityForSkuDiscount && api.global.skuMaxQuantityForSkuDiscount < (sumAlreadyClaimedItems + claimQuantity)) {
api.yellowAlert("ClaimQuantity exceeds max agreed quantity for such a high discount $skuDiscount. Remaining quantity to claim is ${api.global.skuMaxQuantityForSkuDiscount - sumAlreadyClaimedItems} but it was claimed $claimQuantity")
api.global.invalid = true
}
} else {
api.redAlert("QuoteDiscountPerItemPercent $skuDiscount is above the agreed level in linked quote for specific sku = '$sku'. Agreed value = '${api.global.skuDiscountPercent}'")
api.global.invalid = true
}
} else {
api.redAlert("QuoteDiscountPerItemPercent $skuDiscount is above the agreed level in linked quote " + api.global.maxDiscountPercent)
api.global.invalid = true
}
}
totalClaimAmount as BigDecimal
Defines the content of the Allocation Grid, which is a result matrix visualizing the agreed/bought ratio for all claim items. The user can clearly see what was the agreed quantity and how much has been actually bought.
Like the validation logic, allocation logic is a generic logic with its 'Nature' set to 'Claim'.
def matrix = api.newMatrix("Quote", "Quantity", "Claim")
// set column formats
matrix.setColumnFormat("Quote", FieldFormatType.TEXT)
matrix.setColumnFormat("Quantity", FieldFormatType.NUMERIC)
matrix.setColumnFormat("Claim", FieldFormatType.TEXT)
// allow users to filter values
matrix.setEnableClientFilter(false)
if (api.isSyntaxCheck()) {
return matrix
}
// we probably would want only approved (approved=3,no_approval_required=4) or submitted=1 items
def lilist = api.find("QLI",0,1000,null,["clicId","outputs.DiscountPercent.result","outputs.Quantity.result"],
Filter.equal("sku", api.getClaimContext().getRow().get("ProductID")),
Filter.equal("outputs.EndCustomer.result", api.getClaimContext().getRow().get("EndCustomerID")),
Filter.isNotNull("outputs.Quantity.result"))
// Filter.custom(' {clicId} in (select id from Quote where workflowStatus = 1 or workflowStatus = 3 or workflowStatus = 4 ) ' ))
if (lilist) {
def qmap = [:]
def clics = lilist*.clicId
if (clics) {
def qs = api.find("Q",0,1000,null,["id","uniqueName"],true,Filter.in("id",clics))
for (q in qs) {
qmap.put(q.id, q.uniqueName)
}
for (li in lilist) {
matrix.addRow([
Quote: qmap[li.clicId],
Quantity: li["outputs.Quantity.result"]
])
}
}
}
// this is read from current claim type, if the mapping is not stable enough, or if it differs,
// one may need to load such data differently, like loading all matching lines, and fetch the meta
// for such each line (meta is per claim and stored in CLLIAM)
def skuElement = api.getClaimContext().getAttributeName("ProductID")
def endCustomerElement = api.getClaimContext().getAttributeName("EndCustomerID")
def claimQuantityElement = api.getClaimContext().getAttributeName("ClaimQuantity")
// in real use case we might to fetch just item that are on submitted or approved claims
// and maybe only these that do not have the validationState in ("REJECTED", "AUTO_REJECTED")
def cllilist = api.find("CLLI",0,1000,"id",[claimQuantityElement,"claimId"],
Filter.equal(skuElement, api.getClaimContext().getRow().get("ProductID")),
Filter.equal(endCustomerElement, api.getClaimContext().getRow().get("EndCustomerID")),
Filter.isNotNull(claimQuantityElement))
//Filter.notIn("validationState","REJECTED","AUTO_REJECTED")
//Filter.custom(" {claimId} in (select id from Claims where workflowStatus = 1 or workflowStatus = 3 or workflowStatus = 4")
if (cllilist) {
def clmap = [:]
def cls = cllilist*.claimId
if (cls) {
def claims = api.find("CL",0,1000,null,["id","uniqueName"],true,Filter.in("id",cls))
for (claim in claims) {
clmap.put(claim.id, claim.uniqueName)
}
for (cli in cllilist) {
matrix.addRow([
Claim: clmap[cli.claimId],
Quantity: matrix.styledCell(cli[claimQuantityElement], "#ff0000", "transparent")
])
}
}
}
return matrix
Claim Type is a definition that instructs the Channel Management module how to read and process the uploaded Excel file. It maps data and sets the calculation logics for validation/calculation and for generating the Allocation Grid.
Claim sheets from different customers will typically have different structure and you will create a specific Claim Type for each case.
To create a new Claim Type:
- Go to Channel Management > Claim Types.
- Click 'Add'.
- Enter the required values to the columns:
- The Name and Label of the new Claim.
- Validation Logic – The associated validation (calculation) logic. The logic compares the data from the Excel with the actual Quotes.
- Sheet Name – The name of the sheet in the Excel file, from which data should be read.
- Validation State Element Name – The name of the element in the pricing logic that defines the names/values of the 'true' and 'false' validation states as returned by the logic for a particular item (e.g., 'auto rejected' and 'auto validated'). If not filled in, 'validationState' is entered automatically.
- Allocation Formula – The associated allocation logic defining the Allocation Grid.
- Click the Edit icon to display the mapping grid. It maps the columns of the uploaded Excel file to Claim attributes – enter the names of columns containing the SKU, customer ID, quantity, the total claimed amount and other columns. You can also specify the data format type for each column.
The attribute value is either calculated by the logic (the element's Name and the column name in the spreadsheet must match) or is taken from the spreadsheet. You can make any of the attributes editable by users by enabling or disabling the Can Edit checkbox.
When a claim is approved a Claim Record is automatically created. The Claim Record is created on the header level – one record that contains all validated items of the approved claim.
On approval, an event is generated that can be caught by integration systems and forwarded to other applications for processing.
Instead of uploading an Excel file, the transactional data can come from an integrated external system.
- Create a logic that will retrieve the transactional data - a generic logic of the Claim nature.
- This logic must be selected on the configuration page.
- Validation will be handled by the validation logic of the associated Claim Type. Sheet Name, attribute mapping is not taken into account.