aonestar.public

Parameters

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

Definition

DECLARE 
    tran_count int = 0;
    S record;
BEGIN
    post_date = coalesce(post_date, fn_system_date());
    FOR S IN (
        SELECT t.dept_id, t.post_type, t.tran_type, t.is_deposit, t.post_qty, t.post_count, t.post_amt, t.void_qty, t.void_count, t.void_amt
        FROM sp_get_transaction_summary(post_date) t 
    ) LOOP 
        tran_count = tran_count + s.post_count;
        INSERT INTO transaction_summary AS ts 
        (charge_date, dept_id, post_type, tran_type, is_deposit, post_qty, post_count, post_amount, void_qty, void_count, void_amount)
        VALUES ($1, s.dept_id, s.post_type, s.tran_type, s.is_deposit, s.post_qty, s.post_count, s.post_amt, s.void_qty, s.void_count, s.void_amt)
        ON CONFLICT (charge_date, dept_id)
        DO UPDATE 
           SET post_qty    = ts.post_qty + excluded.post_qty,
               post_count  = ts.post_count + excluded.post_count,
               post_amount = ts.post_amount + excluded.post_amount,
               void_qty    = ts.void_qty + excluded.void_qty,
               void_count  = ts.void_count + excluded.void_count,
               void_amount = ts.void_amount + excluded.void_amount;
    END LOOP;
    status = 'success';
    msg = tran_count::TEXT || ' transactions summarized';
END

sp_update_transaction_summary

Parameters

Definition