Link Sales Return Lines to the Invoices They Came From

This Groovy script links every line of a sales return to the sales invoice lines it is returning, using an SQL query you write. When one return line holds more quantity than a single invoice line can take back, the script splits the line and spreads the quantity over several invoices.

Recommended pattern

This script is not tied to any customer's setup. You can paste it as it is and control it entirely through its parameters.


Class name	`EACollectInvoicesInReturn`
Script	EACollectInvoicesInReturn.groovy
Runs on	Sales Return, on Pre Update Calculated Fields.
Uses	Parameter 2 (required) and Parameter 3 (optional). Leave Parameter 1 empty.
Shipped as	Source to paste. It is not built into the system.

When to use it

A customer brings back goods without the invoice number. Perhaps they bought the same air conditioner three times over the summer, and now they return three units in one go. The return term is set to a Return Type of Partially, so every line must name the invoice it comes from in its Invoice column, and that column is required. Finding the right invoices by hand for every line is slow and error-prone.

This script does it for you. You describe, in SQL, which invoice lines a return line may come from. The script takes them in the order the query returns them, oldest first for example, and uses up the quantity that is still returnable on each one.

Take that example. The customer returns 3 units. The query finds two earlier invoices with 2 units each and nothing returned yet. The script keeps the first line at 2 units against the first invoice and adds a second line of 1 unit against the second invoice. Each line takes the Unit Price of its own invoice line, so the customer is credited exactly what they paid each time.

How it works

It skips lines that are already linked. A line whose Invoice column is filled is left as it is, so saving the return again does not redo the work.
It runs your query once per line. Inside the query, {currentLine.…} refers to the return line being handled and plain fields such as {customer.id} refer to the return's header. The query must return the invoice id in its first column and the invoice line id in its second.
It works out what is still returnable on each invoice line it gets back: the line's quantity minus its Return Qty. An invoice line with nothing left is skipped.
It links the line. When the returnable quantity covers the whole line, the line is linked to that invoice line and the script moves on to the next return line.
It splits the line when it has to. When the return line holds more than the invoice line can take, the line is cut down to the returnable quantity and linked, and a copy holding the rest is inserted right after it. That copy is then matched against the next invoice line from the query.
Each linked line takes its invoice line's details: the Invoice, the Copied From Doc and the Source Line Id, the whole price block (so the Unit Price and the discounts), and the Free Item flag. The quantity used is remembered, so two return lines of the same item never take back the same units twice in one save.
It copies extra fields, if you ask it to. When Parameter 3 holds a field map, it is applied from the invoice to the return for every line that gets linked.

A line the query cannot cover stays unlinked

If the query runs out of invoice lines before a return line's quantity is used up, the rest stays on a line with an empty Invoice column. On a return whose type is Partially, the save is then refused because that column is required. Widen the query, or lower the quantity.

Setting it up

Open Administration → Display Customization → Entity Flow and create a flow with Target Type set to Sales Return. Use Criteria or the book and term fields if only some returns should be handled.
In the Details grid, add a line with the action Pre Update Calculated Fields, and paste the whole script from the file above into Groovy Script. Leave Class Name empty: it is filled in when you save.
Fill in the parameters:

Parameter	Title shown after saving	What to enter
Parameter 1	Not used - leave empty	Nothing.
Parameter 2	Query to find invoices per line, example: …	A query returning the invoice id and the invoice line id, in that order and best first. See the example below.
Parameter 3	Extra fields map, example: currentLine.n1=currentLine.n2	Optional. A field map applied from the invoice (source) to the return (target) for each linked line, one `target=source` pair per line. See Field Values Calculator.

Save the flow. Class Name now shows com.namasoft.modules.supplychain.domain.utils.plugnplay.groovy.EACollectInvoicesInReturn, and each parameter shows its title.

Example

This is the query the script itself suggests as Parameter 2. It returns the committed invoices of the same customer that still hold returnable units of the same item and lot, oldest first:

sql

select top 15 s.id invoiceId, l.id lineId
from SalesInvoiceLine l inner join SalesInvoice s on s.id = l.salesInvoice_id
where s.commitedBefore = 1
  and l.quantityBaseValue - coalesce(l.returnedQty, 0) > 0
  and l.item_id = {currentLine.item.item}
  and l.lotid = {currentLine.specificDimensions.lotId}
  and l.customer_id = {customer.id}
order by s.valueDate, s.creationDate

On a return of 3 units of one item, where the customer's two oldest invoices each hold 2 units of it, the saved return has two lines: 2 units linked to the first invoice at its price, and 1 unit linked to the second invoice at its price. The Return Qty of both invoice lines goes up accordingly.

To keep a value from the invoice line on the return line as well, for example the invoice line's Number 2 in the return line's Number 1, put this in Parameter 3:

currentLine.n1=currentLine.n2

The script

groovy

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

import com.namasoft.common.utilities.ObjectChecker
import com.namasoft.infor.domainbase.util.actions.EAGenerateEntityFromEntityAction
import com.namasoft.infra.domainbase.datafields.DecimalDF
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.infra.domainbase.util.xml.XmlSerializer
import com.namasoft.modules.supplychain.domain.details.SalesInvoiceLine
import com.namasoft.modules.supplychain.domain.details.SalesReturnLine
import com.namasoft.modules.supplychain.domain.entities.SalesInvoice
import com.namasoft.modules.supplychain.domain.entities.SalesReturn

class EACollectInvoicesInReturn implements EntityAction<SalesReturn> {
    private Map<SalesInvoiceLine, DecimalDF> linesRemainingQty = new HashMap<>();
    private SalesReturn returnDoc;
    private LongTextDF copyExtraFieldsParam;

    @Override
    Result doAction(SalesReturn object, LongTextDF... parameters) {
        Result result = Result.createAccumulatingResult();
        this.returnDoc = object;
        this.copyExtraFieldsParam = parameters[2];
        List<SalesReturnLine> details = object.getDetails();

        for (int i = 0; i < details.size(); i++) {
            def detail = details.get(i);
            if (detail.getSourceInvoice() != null)
                continue;
            object.setCurrentLine(detail);
            def queryResult = Persister.prepareAndExecuteJDBCQuery(object, parameters[1].getPrimitiveValue());

            for (final def l in queryResult) {
                if (DecimalDF.isEmptyOrZero(detail.fetchPrimeQtyValue()))
                    break;
                SalesInvoice invoice = Persister.findByID(SalesInvoice.class, UniqueIDDF.fromObject(l[0]));
                SalesInvoiceLine invoiceLine = Persister.findByID(SalesInvoiceLine.class, UniqueIDDF.fromObject(l[1]));
                DecimalDF lineRemaining = linesRemainingQty.computeIfAbsent(invoiceLine, { it.fetchPrimeQtyValue().subtract(it.getReturnedQty()) });
                if (DecimalDF.isEmptyOrZero(lineRemaining))
                    continue;
                if (detail.fetchPrimeQtyValue().isGreaterThan(lineRemaining)) {
                    def clone = XmlSerializer.getInstance().clone(detail);
                    clone.updatePrimaryQty(clone.fetchPrimeQtyValue().subtract(lineRemaining));
                    clone.setId(UniqueIDDF.createRandomUniqueIDDF());
                    details.add(i + 1, clone);
                    consumeQty(invoice, invoiceLine, detail, lineRemaining);
                    i += 1;
                    detail = clone;
                } else {
                    consumeQty(invoice, invoiceLine, detail, detail.fetchPrimeQtyValue());
                    break;
                }
            }
        }
        return result;
    }

    @Override
    String describe() {
        return "Sales return: links every line that has no invoice yet to the sales invoice lines returned by the query in parameter 2. " +
                "The query runs once per line, can use {currentLine...} fields, and returns the invoice id in its first column and the invoice line id in its second. " +
                "The line takes the quantity still returnable on each invoice line in turn, with that line's price and free item flag: " +
                "when it has more quantity than one invoice line can take, the line is split and the rest goes to the next invoice line. " +
                "Parameter 3 is an optional field map copied from the invoice to the return for each linked line. Parameter 1 is not used.";
    }

    @Override
    List<String> columnNames() {
        return Arrays.asList("Not used - leave empty", "Query to find invoices per line, example: select top 15 s.id invoiceId,l.id lineId from SalesInvoiceLine l inner join SalesInvoice s on s.id = l.salesInvoice_id\n" +
                "where s.commitedBefore = 1 and l.quantityBaseValue - coalesce(l.returnedQty,0) >0 and l.item_id = {currentLine.item.item} and l.lotid = {currentLine.specificDimensions.lotId}  and l.customer_id = {customer.id} order by s.valueDate, s.creationDate ",
                "Extra fields map, example: currentLine.n1=currentLine.n2"
        );
    }

    void consumeQty(SalesInvoice invoice, SalesInvoiceLine invoiceLine, SalesReturnLine detail, DecimalDF consumedQty) {
        detail.updatePrimaryQty(consumedQty);
        detail.setOrginDoc(invoice.toGenericReference());
        detail.setSourceInvoice(invoice);
        detail.setSourceLineId(invoiceLine.getId());
        detail.setPrice(invoiceLine.getPrice().cloneData());
        detail.setFreeLine(invoiceLine.getFreeLine());
        linesRemainingQty.put(invoiceLine, linesRemainingQty.get(invoiceLine).subtract(consumedQty));
        if (ObjectChecker.isNotEmptyOrNull(copyExtraFieldsParam)) {
            returnDoc.setCurrentLine(detail);
            invoice.setCurrentLine(invoiceLine);
            EAGenerateEntityFromEntityAction.copyFieldsFromSourceToTarget(invoice, returnDoc, copyExtraFieldsParam.getPrimitiveValue());
        }
    }
}

EAGroovyAction: how Groovy scripts are attached to entity flows and task schedules.
Introduction to Entity Flows: what each action, such as Pre Update Calculated Fields, means.
Link Free Item Lines of a Sales Return to the Invoice: fills the source line of free lines.

Link Sales Return Lines to the Invoices They Came From ​

When to use it ​

How it works ​

Setting it up ​

Example ​

The script ​