aonestar.public

Parameters

Name	Type	Mode
tran_date	date	IN (DEFAULT fn_system_date())
dept_id	integer	OUT
post_type	character	OUT
tran_type	character	OUT
is_deposit	boolean	OUT
post_qty	integer	OUT
post_count	integer	OUT
post_amt	t_money	OUT
void_qty	integer	OUT
void_count	integer	OUT
void_amt	t_money	OUT

Definition

--DECLARE SYSTEM_DATE date;
--BEGIN  
    --SYSTEM_DATE := fn_system_date();
    --REPORT_DATE := COALESCE(REPORT_DATE, SYSTEM_DATE);

    --RETURN QUERY
    SELECT t.dept_id, 
        --IIF(separate_deposit and t.post_flag = 'D', 'Z', t.post_type) as post_type, 
        t.post_type, 
        t.tran_type, 
        coalesce(t.post_flag,'') = 'D' as is_deposit,
        sum(t.qty) AS post_qty, 
        count(*) AS post_count, 
        sum(CASE
                WHEN (t.post_type = 'P' AND t.tran_type = 'C') THEN t.total_amount * -1
                WHEN (t.post_type = 'S' AND t.tran_type = 'D') THEN t.total_amount * -1
                ELSE t.total_amount
            END) AS post_amt, 
        sum(iif(t.voided, t.qty, 0)) AS void_qty, 
        sum(iif(t.voided, 1, 0)) AS void_count, 
        sum(CASE
                WHEN (t.voided = false) THEN 0
                WHEN (t.post_type = 'P' AND t.tran_type = 'C') THEN t.total_amount * -1
                WHEN (t.post_type = 'S' AND t.tran_type = 'D') THEN t.total_amount * -1
                ELSE t.total_amount
            END) AS void_amt
    FROM transactions t 
    WHERE t.charge_date = coalesce($1, fn_system_date()) AND t.transfer_out = false 
    GROUP BY 1,2,3,4
--END

sp_get_transaction_summary

Parameters

Definition