InvoicePrice = min(ListPrice * (1 – Discount), QuotePrice)

Code

def products = ["10000001", "10000002"]
def targetDate = api.targetDate()
def customerClass = "A"
def customerId = "CID-0001"

def qapi = api.queryApi()

def t1 = qapi.tables().conditionRecords("Pricelist")
def t2 = qapi.tables().conditionRecords("Discounts")
def t3 = qapi.tables().conditionRecords("QuotePrice")

return qapi.source(t1, [t1.sku, t1.conditionValue().as("ListPrice")], t1.sku.in(products))
        .leftOuterJoin(t2, { cols -> [
                t2.conditionValue().as("Discount"),
                cols.ListPrice.multiply(qapi.exprs().real(1).minus(t2.conditionValue())).as("CustomerPrice")
        ]}, { cols ->
            qapi.exprs().and(
                    t2.key1().equal(cols.sku),
                    t2.key2().equal(customerClass),
                    t2.validFrom().lessOrEqual(targetDate),
                    t2.validTo().greaterOrEqual(targetDate))
        })
        .leftOuterJoin(t3, { cols -> [
                t3.conditionValue().as("QuotePrice"),
                qapi.exprs().nullIf(t3.conditionValue(), cols.CustomerPrice).as("InvoicePrice")
        ] },
                { cols ->
                    qapi.exprs().and(
                            t3.key1().equal(cols.sku),
                            t3.key2().equal(customerId),
                            t3.validFrom().lessOrEqual(targetDate),
                            t3.validTo().greaterOrEqual(targetDate))
                }
        )
        .stream { it.collect { it } }

Result

sku	ListPrice	Discount	CustomerPrice
10000001	57
10000001	21
10000001	41
10000001	98
10000001	57
10000001	62
10000001	54
10000002	6	0.09	5.46
10000002	30	0.09	27.3
10000002	26	0.09	23.66
10000002	33	0.09	30.03
10000002	6	0.09	5.46
10000002	77	0.09	70.07
10000002	83	0.09	75.53

Configuration Engineer Knowledge Base

Waterfall Calculation

Code

Result

See Also