aonestar.public

Parameters

Name	Type	Mode
adate	date	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
status	text	OUT
msg	text	OUT

Definition

DECLARE
    rec_count int = 0;
BEGIN
    adate = COALESCE(adate, fn_system_date());

-- Replace existing data for the date (idempotent)
    DELETE FROM itemizer_summary WHERE charge_date = adate;

-- Sum itemizer amounts from JSONB column per itemizer code + rate combination
    -- itemizers array element: {"code": "...", "rate": ..., "amount": ...}
    -- amount in JSONB is per-unit; multiply by qty for the transaction total
    INSERT INTO itemizer_summary (charge_date, itemizer_code, rate, total_amount, transaction_count)
    SELECT
        adate,
        (item->>'code')::text                                   AS itemizer_code,
        (item->>'rate')::numeric                                AS rate,
        SUM(
            (item->>'amount')::numeric
            * t.qty
            * CASE t.tran_type WHEN 'C' THEN -1 ELSE 1 END
        )                                                       AS total_amount,
        COUNT(*)                                                AS transaction_count
    FROM transactions t
    CROSS JOIN LATERAL jsonb_array_elements(t.itemizers) AS item
    WHERE t.charge_date    = adate
      AND t.itemizers      IS NOT NULL
      AND t.voided         = false
      AND t.transfer_out   = false
    GROUP BY (item->>'code'), (item->>'rate')::numeric
    ON CONFLICT ON CONSTRAINT unq_itemizer_summary DO NOTHING;

GET DIAGNOSTICS rec_count = ROW_COUNT;
    status = 'success';
    msg    = rec_count::text || ' itemizer records generated';
END

sp_generate_itemizer_summary

Parameters

Definition