aonestar.public

Parameters

Name	Type	Mode
date_ref	date	IN (DEFAULT fn_system_date())
yesterday_balance	t_money	OUT
post_summary	t_money	OUT
paid_summary	t_money	OUT
net_different	t_money	OUT
debit_balance	t_money	OUT
credit_balance	t_money	OUT
advance_post	t_money	OUT
net_outstanding	t_money	OUT

Definition

BEGIN
	yesterday_balance = (SELECT os.net_outstanding FROM outstanding os WHERE os.date_ref = $1-1);

SELECT sum(iif(t.post_type = 'P', iif(t.tran_type = 'D', t.amount, -t.amount), 0)), 
		   sum(iif(t.post_type = 'S', iif(t.tran_type = 'C', t.amount, -t.amount), 0))
	FROM transactions t 
	WHERE t.charge_date = $1 AND active = TRUE 
	INTO post_summary, paid_summary;
	
	advance_post = (SELECT sum(iif(t.tran_type = 'D', t.amount, -t.amount)) FROM transactions t WHERE t.charge_date > $1 AND active = TRUE);

SELECT sum(iif(fi.balance > 0, fi.balance, 0)), 
		   sum(iif(fi.balance < 0, fi.balance, 0)) * -1
	FROM folio_items fi LEFT JOIN folio f ON f.id = fi.folio_id 
	WHERE f.status = 'A' AND NOT fi.closed
	INTO debit_balance, credit_balance;

post_summary = coalesce(post_summary, 0);
	paid_summary = coalesce(post_summary, 0);
	advance_post = coalesce(post_summary, 0);
	debit_balance = coalesce(post_summary, 0);
	credit_balance = coalesce(credit_balance, 0);
	net_different = post_summary - paid_summary;
	net_outstanding = debit_balance - credit_balance - advance_post;	
END

sp_calc_outstanding

Parameters

Definition