Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Function: f_get_saldo_awal_partner_credit_limit_usage(bigint, bigint, character varying)
- -- DROP FUNCTION f_get_saldo_awal_partner_credit_limit_usage(bigint, bigint, character varying);
- CREATE OR REPLACE FUNCTION f_get_saldo_awal_partner_credit_limit_usage(bigint, bigint, character varying)
- RETURNS numeric AS
- $BODY$
- DECLARE
- pTenantId alias for $1;
- pPartnerId alias for $2;
- pYearMonth alias for $3;
- vResult numeric;
- vSaldoAwal numeric;
- vBeginAmount numeric;
- vLogAmount numeric;
- vBeginYearMonth character varying(6);
- BEGIN
- /**
- *Function ini mencari saldo awal di bulan tahun pencarian
- *jika tidak ketemu maka akan mengambil saldo awal bulan tahun sebelumnya dan ditambah dengan akumulasi amount sebelum tahun bulan pencarian
- *saldo awal yang dikembalikan bisa bernilai - ataupun +
- */
- vResult := 0;
- vSaldoAwal := 0;
- vBeginAmount := 0;
- vLogAmount := 0;
- SELECT COALESCE(SUM(amount),0) INTO vSaldoAwal
- FROM sl_log_partner_credit_limit_usage
- WHERE tenant_id = pTenantId
- AND partner_id = pPartnerId
- AND record_type = 0
- AND doc_id = -99
- AND doc_type_id = -99
- AND SUBSTR(doc_date,1,6) = pYearMonth;
- IF vSaldoAwal IS NOT NULL AND vSaldoAwal <> 0 THEN
- vResult = vSaldoAwal;
- ELSE
- SELECT COALESCE(MAX(SUBSTR(doc_date,1,6)), pYearMonth) INTO vBeginYearMonth
- FROM sl_log_partner_credit_limit_usage
- WHERE tenant_id = pTenantId
- AND partner_id = pPartnerId
- AND record_type = 0
- AND doc_id = -99
- AND doc_type_id = -99
- AND SUBSTRING(doc_date, 1, 6) < pYearMonth;
- SELECT COALESCE(SUM(amount),0) INTO vBeginAmount
- FROM sl_log_partner_credit_limit_usage
- WHERE tenant_id = pTenantId
- AND partner_id = pPartnerId
- AND record_type = 0
- AND doc_id = -99
- AND doc_type_id = -99
- AND SUBSTR(doc_date,1,6) = vBeginYearMonth;
- SELECT COALESCE(SUM(amount * record_type),0) INTO vLogAmount
- FROM sl_log_partner_credit_limit_usage
- WHERE tenant_id = pTenantId
- AND partner_id = pPartnerId
- AND record_type <> 0
- AND doc_id <> -99
- AND doc_type_id <> -99
- AND SUBSTR(doc_date,1,6) >= vBeginYearMonth
- AND SUBSTR(doc_date,1,6) < pYearMonth;
- vResult := vBeginAmount + vLogAmount;
- END IF;
- return vResult;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- ALTER FUNCTION f_get_saldo_awal_partner_credit_limit_usage(bigint, bigint, character varying)
- OWNER TO sts;
Advertisement
Add Comment
Please, Sign In to add comment