aonestar.public

Parameters

Name	Type	Mode
report_date	date	IN (DEFAULT NULL)
id	integer	OUT
code	text	OUT
name	text	OUT
group_id	integer	OUT
group_name	text	OUT
post_type	character	OUT
day_post_qty	integer	OUT
day_post_amt	t_money	OUT
day_void_qty	integer	OUT
day_void_amt	t_money	OUT
day_net_qty	integer	OUT
day_net_amt	t_money	OUT
mtd_post_qty	integer	OUT
mtd_post_amt	t_money	OUT
mtd_void_qty	integer	OUT
mtd_void_amt	t_money	OUT
mtd_net_qty	integer	OUT
mtd_net_amt	t_money	OUT
ytd_post_qty	integer	OUT
ytd_post_amt	t_money	OUT
ytd_void_qty	integer	OUT
ytd_void_amt	t_money	OUT
ytd_net_qty	integer	OUT
ytd_net_amt	t_money	OUT

Definition

DECLARE SYSTEM_DATE date = fn_system_date();
BEGIN
    REPORT_DATE := COALESCE(REPORT_DATE, SYSTEM_DATE);
    IF REPORT_DATE > SYSTEM_DATE then 
       return;
    END IF;

    RETURN QUERY
    WITH summ as (
        SELECT $1 as charge_date, t.dept_id, t.tran_type, t.post_type, 
               t.post_qty, t.post_amt, t.void_qty, t.void_amt,
               true as is_today, true as is_mtd, true as is_ytd 
        FROM sp_get_transaction_summary($1) t 
        WHERE $1 = SYSTEM_DATE
        UNION ALL
        SELECT ts.charge_date, ts.dept_id, ts.tran_type,  ts.post_type,  
               ts.post_qty, ts.post_amount, ts.void_qty, ts.void_amount,
               (charge_date = $1) as is_today, 
               (charge_date between date_trunc('month', $1) and $1) as is_mtd, 
               (charge_date between date_trunc('year', $1) and $1) as is_ytd
                 
        FROM transaction_summary ts 
        WHERE ts.charge_date between date_trunc('year', $1) AND $1 and ts.charge_date <> fn_system_date()
    ), dataset as (
        SELECT s.dept_id, s.tran_type, s.post_type, 
                SUM(IIF(is_today, post_qty, 0))::int     as day_post_qty, 
                SUM(IIF(is_today, post_amt, 0))::t_money as day_post_amt, 
                SUM(IIF(is_today, void_qty, 0))::int     as day_void_qty, 
                SUM(IIF(is_today, void_amt, 0))::t_money as day_void_amt, 
                SUM(IIF(is_mtd  , post_qty, 0))::int     as mtd_post_qty, 
                SUM(IIF(is_mtd  , post_amt, 0))::t_money as mtd_post_amt, 
                SUM(IIF(is_mtd  , void_qty, 0))::int     as mtd_void_qty, 
                SUM(IIF(is_mtd  , void_amt, 0))::t_money as mtd_void_amt,
                SUM(IIF(is_ytd  , post_qty, 0))::int     as ytd_post_qty, 
                SUM(IIF(is_ytd  , post_amt, 0))::t_money as ytd_post_amt, 
                SUM(IIF(is_ytd  , void_qty, 0))::int     as ytd_void_qty, 
                SUM(IIF(is_ytd  , void_amt, 0))::t_money as ytd_void_amt
        FROM SUMM S
        GROUP BY 1,2,3
    )
    SELECT 
        d.id,
        d.code::text,
        d.name,
        d.group_id,
        dg.name AS group_name, 
        ds.post_type,
        ds.day_post_qty,
        ds.day_post_amt,
        ds.day_void_qty,
        ds.day_void_amt,
        ds.day_post_qty - ds.day_void_qty AS day_net_qty,
        (ds.day_post_amt - ds.day_void_amt)::t_money AS day_net_amt,
        ds.mtd_post_qty,
        ds.mtd_post_amt,
        ds.mtd_void_qty,
        ds.mtd_void_amt,
        ds.mtd_post_qty - ds.mtd_void_qty AS mtd_net_qty,
        (ds.mtd_post_amt - ds.mtd_void_amt)::t_money AS mtd_net_amt,
        ds.ytd_post_qty,
        ds.ytd_post_amt,
        ds.ytd_void_qty,
        ds.ytd_void_amt,
        ds.ytd_post_qty - ds.ytd_void_qty AS ytd_net_qty,
        (ds.ytd_post_amt - ds.ytd_void_amt)::t_money AS ytd_net_amt
    FROM 
        DataSet ds
        LEFT JOIN department d on ds.dept_id = d.id
        LEFT JOIN department_group dg ON dg.id = d.group_id 
    WHERE ds.ytd_post_qty > 0
    ORDER BY post_type, dg.seq, code;        
END

sp_trial_balance

Parameters

Definition