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, 
		item.rate,
	    SUM(item.amount * t.qty * CASE t.tran_type WHEN 'C' THEN -1 ELSE 1 END)::t_money,
	    COUNT(t.id)
	FROM transactions t,
		LATERAL jsonb_to_recordset(t.itemizers) AS item(code text, rate t_money, amount numeric)
	WHERE t.charge_date = adate 
		AND t.post_type = 'P' 
		AND t.itemizers IS NOT NULL 
		AND item.amount IS NOT NULL
		AND t.active 
	GROUP BY 1,2,3
    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