aonestar.public

Parameters

Name	Type	Mode
date_ref	date	IN (DEFAULT NULL)
recalculate	boolean	IN (DEFAULT false)
yesterday_balance	t_money	OUT
today_received	t_money	OUT
today_canceled	t_money	OUT
today_clearing	t_money	OUT
today_no_show	t_money	OUT
today_refund	t_money	OUT
today_balance	t_money	OUT
outstanding	t_money	OUT

Definition

DECLARE system_date DATE = fn_system_date();
BEGIN
 	date_ref = COALESCE(date_ref, system_date);

IF (date_ref = system_date) OR recalculate THEN 
		with DPS as (
		    Select
		      sum(iif(deposit_date <= $1-1, amount, 0))    -- Received
			    - sum(iif(status = 'X' and cancel_date <= $1-1, amount, 0)) -- Cancelled
			    - sum(iif(status = 'C' and clearing_date <= $1-1, amount, 0))   -- Clearing
			    - sum(iif(status = 'X' and cancel_date > $1-1 and clearing_date <= $1-1, amount, 0)) -- Clearing and cancelled
			    - sum(iif(status = 'N' and noshow_date <= $1-1, amount, 0)) as yesterday_balance,
			  sum(iif(deposit_date = $1, amount, 0)) as received,
			  sum(iif(status = 'C' and clearing_date = $1, amount, 0)) as clearing,
			  sum(iif(status = 'X' and clearing_date = $1 and cancel_date <> $1, amount, 0)) as x_clearing,   
			  sum(iif(status = 'X' and cancel_date = $1, coalesce(clearing_amount, amount), 0)) as cancel,
			  sum(iif(status = 'N' and noshow_date = $1, coalesce(clearing_amount, amount), 0)) as noshow,
			  sum(iif(status = 'X' and cancel_date = $1, refund_amount, 0)) as refund_cancel,
			  sum(iif(status = 'N' and noshow_date = $1, refund_amount, 0)) as refund_noshow
			from deposit d
		    where (d.deposit_date <= $1)
		)
	    Select coalesce(dps.yesterday_balance, 0), coalesce(received, 0),
	        coalesce(dps.cancel, 0), coalesce(dps.noshow, 0), 
	        coalesce(dps.clearing, 0) + coalesce(dps.x_clearing, 0),
	        coalesce(dps.refund_cancel, 0) + coalesce(dps.refund_noshow, 0)
	    from DPS
	    INTO yesterday_balance, today_received, today_canceled, today_no_show, today_clearing, today_refund;
	
	    today_balance = today_received - today_clearing - today_canceled - today_no_show - today_refund;
	
	    outstanding = yesterday_balance + today_balance;
	ELSE
		SELECT deposit_yesterday_balance, deposit_received, deposit_canceled, deposit_no_show, deposit_clearing, deposit_refund, deposit_outstanding
		FROM outstanding os
		WHERE os.date_ref = $1
		INTO yesterday_balance, today_received, today_canceled, today_no_show, today_clearing, today_refund, outstanding;
		today_balance = today_received - today_clearing - today_canceled - today_no_show - today_refund;
	END IF;
END

sp_deposit_outstanding

Parameters

Definition