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;
    skip_count  int = 0;
    warn_msg    text;
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 department + itemizer code + rate
    -- 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, dept_id, itemizer_code, rate, total_amount, transaction_count)
	SELECT
	    adate,
		t.dept_id,
		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
		AND t.dept_id IS NOT NULL   -- dept_id is part of the key; see skip warning below
	GROUP BY 1,2,3,4
    ON CONFLICT ON CONSTRAINT unq_itemizer_summary DO NOTHING;

GET DIAGNOSTICS rec_count = ROW_COUNT;

-- A posted transaction without a department is a data defect. Skip it rather than
    -- failing the end-of-day run, but make the skip loud and traceable.
    SELECT COUNT(*) INTO skip_count
    FROM transactions t
    WHERE t.charge_date = adate
      AND t.post_type = 'P'
      AND t.itemizers IS NOT NULL
      AND t.active
      AND t.dept_id IS NULL;

status = 'success';
    msg    = rec_count::text || ' itemizer records generated';

IF skip_count > 0 THEN
        warn_msg = format('itemizer_summary %s: skipped %s transaction(s) with dept_id IS NULL',
                          adate, skip_count);
        RAISE WARNING '%', warn_msg;
        IF to_regprocedure('public.sp_log_warning(text,text,boolean,boolean)') IS NOT NULL THEN
            PERFORM public.sp_log_warning('ITEMIZER_SUMMARY_NO_DEPT', warn_msg, true, false);
        END IF;
        msg = msg || format(' (warning: %s transaction(s) skipped, dept_id IS NULL)', skip_count);
    END IF;
END

sp_generate_itemizer_summary

Parameters

Definition