aonestar.public

Parameters

Name	Type	Mode
date_ref	date	IN (DEFAULT NULL)
recalculate	boolean	IN (DEFAULT false)
yesterday_balance	t_money	OUT
post_summary	t_money	OUT
paid_summary	t_money	OUT
deposit	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
guest_folio_balance	t_money	OUT
deposit_folio_balance	t_money	OUT
cashsale_folio_balance	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 
        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)),
               deposit 
        FROM transactions t 
        WHERE t.charge_date = $1 AND active = TRUE 
        INTO post_summary, paid_summary;
        
        advance_post = (SELECT sum(acc_amount) 
                        FROM transactions t 
                        WHERE t.charge_date > $1 AND t.post_type = 'P' AND active = TRUE);
    
        SELECT sum(iif(fi.balance > 0, fi.balance, 0)), 
               sum(iif(fi.balance < 0, fi.balance, 0)) * -1,
               sum(iif(f.folio_type='R', fi.balance, 0)),
               sum(iif(f.folio_type='B', fi.balance, 0)),
               sum(iif(f.folio_type='C', fi.balance, 0))  
        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, guest_folio_balance, deposit_folio_balance, cashsale_folio_balance;
    
        yesterday_balance = coalesce(yesterday_balance, 0);
        post_summary = coalesce(post_summary, 0);
        paid_summary = coalesce(paid_summary, 0);
        advance_post = coalesce(advance_post, 0);
        debit_balance = coalesce(debit_balance, 0);
        credit_balance = coalesce(credit_balance, 0);
        net_different = post_summary - paid_summary;
        net_outstanding = debit_balance - credit_balance - advance_post;    
    ELSE
        SELECT os.yesterday_balance, 
               os.post_summary, 
               os.paid_summary, 
               os.net_different, 
               os.debit_balance, 
               os.credit_balance, 
               os.advance_post, 
               os.net_outstanding,
               os.guest_folio_balance,
               os.deposit_folio_balance, 
               os.cashsale_folio_balance          
        FROM outstanding os 
        WHERE os.date_ref = $1      
        INTO yesterday_balance, 
             post_summary, 
             paid_summary, 
             net_different, 
             debit_balance, 
             credit_balance, 
             advance_post, 
             net_outstanding,
             guest_folio_balance, 
             deposit_folio_balance, 
             cashsale_folio_balance;   
    END IF;
    
    post_summary := coalesce(post_summary, 0.0);
END

sp_get_outstanding

Parameters

Definition