aonestar.public

Parameters

Name	Type	Mode
total_amount	t_money	IN
itemizer_codes	text[]	IN
code	text	OUT
rate	t_percent	OUT
amount	numeric	OUT

Definition

WITH itm AS (
        SELECT code, name, rate, amount, is_service, is_tax, calc_on_baseprice
        FROM itemizer
        WHERE code = ANY($2)
    ), tax AS (
        SELECT code, name, rate, iif(rate IS NULL, amount, ($1-COALESCE(sum_amt,0)) * rate / (100.00+sum_rates)) AS amount
        FROM itm, LATERAL (SELECT sum(rate) sum_rates, sum(amount) sum_amt FROM itm WHERE is_tax) AS summ
        WHERE is_tax
    ), ser AS (
        SELECT code, name, rate, ($1-COALESCE(tx.tax_amt,0)) * rate / (100.00+sum_rates) AS amount
        FROM itm,
            LATERAL (SELECT sum(rate) sum_rates FROM itm WHERE is_service) AS summ,
            LATERAL (SELECT sum(amount) AS tax_amt FROM tax) AS tx
        WHERE is_service
    ), oth_amt AS (
        SELECT code, name, rate, amount FROM itm WHERE NOT(is_service or is_tax) AND rate IS null
    ), oth_on_top AS (--iif(calc_on_baseprice,sum_rates, sum_rates)
        SELECT code,
            --format('%s: %s x %s / %s', code, ($1 - service_amt - tax_amt), rate, 100.00+sum_rates) AS code,
            name, rate, ($1 - COALESCE(service_amt,0) - COALESCE(tax_amt,0)) * rate / (100.00 + sum_rates) as amount
        FROM itm,
            LATERAL (SELECT sum(rate) sum_rates FROM itm WHERE NOT(is_service or is_tax) AND rate IS NOT NULL AND NOT calc_on_baseprice) AS br,
            LATERAL (SELECT sum(amount) service_amt FROM ser) AS sv,
            LATERAL (SELECT sum(amount) tax_amt FROM tax) AS tx
        WHERE NOT(is_service or is_tax) AND rate IS NOT NULL AND NOT calc_on_baseprice
    ), oth_on_base AS (
        SELECT code,
            --format('%s: %s x %s / %s', code, ($1 - service_amt - tax_amt - sum_oth.amt - sum_on_top.amt), rate, 100.00) AS code,
            name, rate, ($1 - COALESCE(service_amt,0) - COALESCE(tax_amt,0) - COALESCE(sum_oth.amt,0) - COALESCE(sum_on_top.amt,0)) * rate / (100.00) as amount
        FROM itm,
            LATERAL (SELECT sum(rate) sum_rates FROM itm WHERE NOT(is_service or is_tax) AND calc_on_baseprice) AS br,
            LATERAL (SELECT sum(amount) amt FROM oth_amt) AS sum_oth,
            LATERAL (SELECT sum(amount) amt FROM oth_on_top) AS sum_on_top,
            LATERAL (SELECT sum(amount) service_amt FROM ser) AS sv,
            LATERAL (SELECT sum(amount) tax_amt FROM tax) AS tx
        WHERE NOT(is_service or is_tax) AND rate IS NOT NULL AND calc_on_baseprice
    ), items AS (
        SELECT * FROM oth_amt
        UNION ALL
        SELECT * FROM oth_on_base
        UNION ALL
        SELECT * FROM oth_on_top
        UNION ALL
        SELECT * FROM ser
        UNION ALL
        SELECT * FROM tax
    )
    SELECT 'BASE_PRICE' AS code, NULL::t_percent, ($1-sum(amount))::numeric(15,4) AS amount FROM items
    UNION ALL
    SELECT code, rate, amount::numeric(15,4)  FROM items

sp_get_itemizer_amounts

Parameters

Definition