Clear Lots That Were Never Received, and Unused Dimensions

This Groovy script tidies the dimensions on a document's lines before the document is saved. It clears every dimension that the line's item does not use, and it clears any lot that the item has never had stock of in any warehouse.

Recommended pattern

This script is not tied to any customer's setup. It has no parameters: attach it and it works.


Class name	`EAClearLotIdsWithNoItemDimensionsQty`
Script	EAClearLotIdsWithNoItemDimensionsQty.groovy
Runs on	Supply chain documents that take stock out, such as a stock issue, a sales invoice or a stock transfer. Target action Pre Update Calculated Fields.
Uses	No parameters. Leave Parameter 1 empty.
Shipped as	Source to paste. It is not built into the system.

When to use it

Lines often arrive with dimensions that do not belong on them: a document is copied from another one, a line is imported from a spreadsheet, or the item was changed on a line after the lot had been typed. The save then stops because the lot is not found for the item, and the user has to find and clear the offending cell by hand.

This script does that cleaning for them. After it runs, a lot remains on a line only if the item really has stock records for that lot. The line is left with an empty lot, which the user, or a lot-picking flow that runs after this one, can then fill correctly.

Not for documents that bring new lots in

A stock receipt or a purchase invoice usually introduces a lot for the first time. That lot has no stock yet, so this script would clear it. Attach the script only to documents that take existing stock out.

How it works

It clears the dimensions the item does not use. For each line with an item:
- when the item has Has Lot off, the Lot ID is cleared together with the line's Expiry Date, Production Date and retest date;
- otherwise, when the item has Has Expiry off, only the Expiry Date is cleared;
- when the item has Has Serial off, the Serial Number is cleared;
- when the item has HasTwo Serials off, the Second Serial is cleared.
It checks the remaining lots in one go. Lines of lot-tracked items that still have a lot are collected, and the system's stock records are searched once for all their item and lot pairs together. The warehouse does not matter: the lot only has to exist for that item somewhere.
It clears the lots that are not found. A line whose item and lot pair has no stock record loses its lot and the three dates. The comparison ignores letter case and leading or trailing spaces, so test-a on the line matches a lot saved as TEST-A.

The script never refuses the save. It only clears fields.

Setting it up

Open Administration → Display Customization → Entity Flow and create a flow whose target type is the document to clean (for example Stock Issue).
In the Details grid, add a line with Target Action set to Pre Update Calculated Fields, and paste the whole script into its Groovy Script column. Leave Class Name empty: it is filled in when you save.
Leave all parameters empty, and save the flow.

The script also runs when a document is saved as a draft.

Example

Item RAW0033 is lot tracked and has stock in lot TEST-A. A stock issue has three lines:

Line	Item	Lot ID	Serial Number	After saving
1	`RAW0033`	`test-a`		Kept: it matches `TEST-A`.
2	`RAW0033`	`NEVER-RECEIVED`		Lot cleared: the item never had stock in it.
3	an item without lots or serials	`STRAY`	`SN1`	Lot and serial number cleared.

The script

groovy

package com.namasoft.modules.supplychain.domain.utils.plugnplay.groovy

import com.namasoft.common.Pair
import com.namasoft.common.utilities.ObjectChecker
import com.namasoft.infra.domainbase.datafields.BooleanDF
import com.namasoft.infra.domainbase.datafields.LongTextDF
import com.namasoft.infra.domainbase.datafields.UniqueIDDF
import com.namasoft.infra.domainbase.entity.base.EntityAction
import com.namasoft.infra.domainbase.persistence.repos.Persister
import com.namasoft.infra.domainbase.util.Result
import com.namasoft.modules.basic.domain.primitives.EntityTargetAction
import com.namasoft.modules.supplychain.domain.details.BasicSCDocumentLine
import com.namasoft.modules.supplychain.domain.entities.BasicSCDocument
import com.namasoft.modules.supplychain.domain.entities.InvItem
import com.namasoft.modules.supplychain.domain.primitives.LotId
import com.namasoft.modules.supplychain.domain.primitives.SerialNumber

public class EAClearLotIdsWithNoItemDimensionsQty implements EntityAction<BasicSCDocument> {

    @Override
    Result doAction(EntityTargetAction action, BasicSCDocument doc, LongTextDF... parameters) {
        Result result = Result.createAccumulatingResult();
        clearDimensionsDisabledOnItem(doc);
        Map<Pair<UUID, String>, List<BasicSCDocumentLine>> linesPerItemAndLot = collectLinesHavingLot(doc);
        if (ObjectChecker.isEmptyOrNull(linesPerItemAndLot))
            return result;
        Set<Pair<UUID, String>> knownItemAndLotPairs = selectExistingItemAndLotPairs(linesPerItemAndLot.keySet());
        for (Map.Entry<Pair<UUID, String>, List<BasicSCDocumentLine>> entry : linesPerItemAndLot.entrySet()) {
            if (knownItemAndLotPairs.contains(entry.getKey()))
                continue;
            for (BasicSCDocumentLine line : entry.getValue())
                clearLotAndItsDates(line);
        }
        return result;
    }

    private static void clearDimensionsDisabledOnItem(BasicSCDocument doc) {
        List<BasicSCDocumentLine> details = doc.getDetails();
        if (ObjectChecker.isEmptyOrNull(details))
            return;
        for (BasicSCDocumentLine line : details) {
            InvItem item = line.fetchItem();
            if (item == null)
                continue;
            if (BooleanDF.isFalse(item.getHasLot()))
                clearLotAndItsDates(line);
            else if (BooleanDF.isFalse(item.getHasExpiry()))
                line.setExpiryDate(null);
            if (BooleanDF.isFalse(item.getHasSerial()))
                line.getSpecificDimensions().setSerialNumber(SerialNumber.createEmpty());
            if (BooleanDF.isFalse(item.getHasTwoSerials()))
                line.getSpecificDimensions().setSecondSerial(SerialNumber.createEmpty());
        }
    }

    private static void clearLotAndItsDates(BasicSCDocumentLine line) {
        line.getSpecificDimensions().setLotId(LotId.createEmpty())
        line.setExpiryDate(null)
        line.setProductionDate(null)
        line.setRetestDate(null)
    }

    private static Map<Pair<UUID, String>, List<BasicSCDocumentLine>> collectLinesHavingLot(BasicSCDocument doc) {
        Map<Pair<UUID, String>, List<BasicSCDocumentLine>> linesPerItemAndLot = new LinkedHashMap<>();
        List<BasicSCDocumentLine> details = doc.getDetails();
        if (ObjectChecker.isEmptyOrNull(details))
            return linesPerItemAndLot;
        for (BasicSCDocumentLine line : details) {
            if (line.fetchItem() == null || BooleanDF.isFalse(line.fetchItem().getHasLot()))
                continue;
            if (ObjectChecker.isEmptyOrNull(line.fetchLotId()))
                continue;
            Pair<UUID, String> key = itemAndLotKey(line.fetchItem().getId(), line.fetchLotId());
            linesPerItemAndLot.computeIfAbsent(key, { k -> new ArrayList<BasicSCDocumentLine>() }).add(line);
        }
        return linesPerItemAndLot;
    }

    private static Set<Pair<UUID, String>> selectExistingItemAndLotPairs(Collection<Pair<UUID, String>> keys) {
        Map<String, Object> queryParameters = new HashMap<>();
        queryParameters.put("items", keys.collect { UniqueIDDF.fromUUID(it.getX()) }.unique());
        queryParameters.put("lots", keys.collect { it.getY() }.unique());
        List<Object[]> rows = Persister.prepareAndExecuteJDBCQuery(queryParameters,
                'select q.item_id, q.lotId from ItemDimensionsQty q where q.item_id in ({items}) and q.lotId in ({lots})');
        Set<Pair<UUID, String>> existing = new HashSet<>();
        for (Object[] row : rows)
            existing.add(itemAndLotKey(UniqueIDDF.fromObject(row[0]), row[1]));
        return existing;
    }

    private static Pair<UUID, String> itemAndLotKey(UniqueIDDF itemId, Object lotId) {
        UUID item = itemId == null ? null : itemId.getPrimitiveValue();
        return Pair.of(item, ObjectChecker.toStringOrEmpty(lotId).trim().toUpperCase());
    }

    @Override
    String describe() {
        return "Clears the dimensions that the item of the line does not use, then clears the lot of every document line whose " +
                "item + lot pair has no row in the item dimensions quantities table, so that a lot that was never received is not " +
                "carried on the document.\n" +
                "A dimension is cleared when the matching option is disabled on the item: the lot together with its expiry, production " +
                "and retest dates when the item has no lot, the expiry date when the item has no expiry, the serial when the item has " +
                "no serial, and the second serial when the item has no two serials.\n" +
                "For the lot check, lines are skipped when the item is empty, when the item is not lot tracked, or when the line has no lot.\n" +
                "All remaining lines are checked with a single query, and the lot is compared ignoring case and surrounding spaces.\n" +
                "Takes no parameters."
    }

    @Override
    List<String> columnNames() {
        return Arrays.asList("Not used - leave empty");
    }
}

EAGroovyAction: how Groovy scripts are attached to entity flows.
Understanding Inventory Items: the item options that switch each dimension on.

Clear Lots That Were Never Received, and Unused Dimensions ​

When to use it ​

How it works ​

Setting it up ​

Example ​

The script ​