widana

f_get_saldo_awal_partner_credit_limit_usage

Aug 8th, 2017
90
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Function: f_get_saldo_awal_partner_credit_limit_usage(bigint, bigint, character varying)
  2.  
  3. -- DROP FUNCTION f_get_saldo_awal_partner_credit_limit_usage(bigint, bigint, character varying);
  4.  
  5. CREATE OR REPLACE FUNCTION f_get_saldo_awal_partner_credit_limit_usage(bigint, bigint, character varying)
  6.   RETURNS numeric AS
  7. $BODY$
  8. DECLARE
  9.  
  10.     pTenantId               alias for $1;
  11.     pPartnerId          alias for $2;
  12.     pYearMonth             alias for $3;
  13.  
  14.     vResult             numeric;
  15.     vSaldoAwal          numeric;
  16.     vBeginAmount            numeric;
  17.     vLogAmount          numeric;
  18.  
  19.     vBeginYearMonth         character varying(6);
  20.  
  21. BEGIN
  22. /**
  23. *Function ini mencari saldo awal di bulan tahun pencarian
  24. *jika tidak ketemu maka akan mengambil saldo awal bulan tahun sebelumnya dan ditambah dengan akumulasi amount sebelum tahun bulan pencarian
  25. *saldo awal yang dikembalikan bisa bernilai - ataupun +
  26. */
  27.     vResult := 0;
  28.     vSaldoAwal := 0;
  29.     vBeginAmount := 0;
  30.     vLogAmount := 0;
  31.  
  32.    
  33.     SELECT COALESCE(SUM(amount),0) INTO vSaldoAwal
  34.     FROM sl_log_partner_credit_limit_usage
  35.     WHERE tenant_id = pTenantId
  36.         AND partner_id = pPartnerId
  37.         AND record_type = 0
  38.         AND doc_id = -99
  39.         AND doc_type_id = -99
  40.         AND SUBSTR(doc_date,1,6) = pYearMonth;
  41.  
  42.     IF vSaldoAwal IS NOT NULL AND vSaldoAwal <> 0 THEN
  43.        
  44.         vResult = vSaldoAwal;
  45.  
  46.     ELSE
  47.    
  48.         SELECT COALESCE(MAX(SUBSTR(doc_date,1,6)), pYearMonth) INTO vBeginYearMonth
  49.         FROM sl_log_partner_credit_limit_usage
  50.         WHERE tenant_id = pTenantId
  51.             AND partner_id = pPartnerId
  52.             AND record_type = 0
  53.             AND doc_id = -99
  54.             AND doc_type_id = -99
  55.             AND SUBSTRING(doc_date, 1, 6) < pYearMonth;
  56.                
  57.    
  58.         SELECT COALESCE(SUM(amount),0) INTO vBeginAmount
  59.         FROM sl_log_partner_credit_limit_usage
  60.         WHERE tenant_id = pTenantId
  61.             AND partner_id = pPartnerId
  62.             AND record_type = 0
  63.             AND doc_id = -99
  64.             AND doc_type_id = -99
  65.             AND SUBSTR(doc_date,1,6) = vBeginYearMonth;
  66.  
  67.         SELECT COALESCE(SUM(amount * record_type),0) INTO vLogAmount
  68.         FROM sl_log_partner_credit_limit_usage
  69.         WHERE tenant_id = pTenantId
  70.             AND partner_id = pPartnerId
  71.             AND record_type <> 0
  72.             AND doc_id <> -99
  73.             AND doc_type_id <> -99
  74.             AND SUBSTR(doc_date,1,6) >= vBeginYearMonth
  75.             AND SUBSTR(doc_date,1,6) < pYearMonth;
  76.  
  77.         vResult := vBeginAmount + vLogAmount;
  78.  
  79.     END IF;
  80.  
  81.     return vResult;
  82. END;
  83. $BODY$
  84.   LANGUAGE plpgsql VOLATILE
  85.   COST 100;
  86. ALTER FUNCTION f_get_saldo_awal_partner_credit_limit_usage(bigint, bigint, character varying)
  87.   OWNER TO sts;
Advertisement
Add Comment
Please, Sign In to add comment