BEGIN;
SELECT mlm_r_report_summary_mutasi_saldo('SESSION_DATA', 11, 14, '201707',333, 12);
FETCH ALL IN "refHeader";
ROLLBACK;

CREATE OR REPLACE FUNCTION mlm_r_report_summary_mutasi_saldo(character varying, bigint, bigint, character varying, bigint, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
	pSessionId			ALIAS FOR $1;
	pTenantId			ALIAS FOR $2;
	pOuId				ALIAS FOR $3;
	pPeriod				ALIAS FOR $4;
	pUserId				ALIAS FOR $5;
	pRoleId				ALIAS FOR $6;
	
	pRefHeader			REFCURSOR := 'refHeader';
	
	vDocTypeDpBalance	bigint := 252;
	vDocTypeCnArBonus	bigint := 255;
	vTglAwalBulan		character varying(8);
	vEmptyId		bigint := -99;
	vPartnerRank		character varying := 'PARTNERRANK';
	vYes			character varying := 'Y';
	vGroupCustomer		character varying := 'C';
	vCustomerDs		character varying := 'CUSTOMER DS';

BEGIN
	
	DELETE FROM tt_mlm_r_report_mutasi_saldo WHERE session_id = pSessionId;

	/*
	* insert ke table temporary untuk credit amount dan debt amount panggil function f_get_saldo_awal_partner_credit_limit_usage untuk 
	* menentukan nilai nya.
	*/
	INSERT INTO tt_mlm_r_report_mutasi_saldo(session_id, partner_id, order_type, credit_amount, debt_amount, remark)
	SELECT pSessionId, a.partner_id, 'Saldo Akhir', 
		CASE WHEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) > 0 THEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) ELSE 0 END AS credit_amount,
		CASE WHEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) < 0 THEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) * -1 ELSE 0 END AS debt_amount,
		'Saldo Akhir'
	FROM m_partner a 
	INNER JOIN  m_partner_cp n ON  a.partner_id = n.partner_id 
	INNER JOIN m_ctgr_partner b ON b.tenant_id = a.tenant_id AND b.ctgr_partner_id = a.ctgr_partner_id  
	INNER JOIN  mlm_ds  h ON a.partner_id = h.partner_id  
	INNER JOIN  mlm_member  i ON h.member_id = i.member_id  
	WHERE a.tenant_id = pTenantId;

	/*
	* tambahkan credit amount dan debt amount dari dokumen DNAP.
	*/
	WITH TEMP AS (
		SELECT B.mlm_r_report_mutasi_saldo_id, A.partner_id, B.order_type, SUM(CASE WHEN A.record_type = 1 THEN A.amount ELSE 0 END) AS credit_amount, SUM(CASE WHEN A.record_type = -1 THEN A.amount ELSE 0 END) AS debt_amount
		FROM sl_log_partner_credit_limit_usage A, tt_mlm_r_report_mutasi_saldo B
		WHERE A.tenant_id = pTenantId
			AND B.session_id = pSessionId
			AND A.partner_id = B.partner_id
			AND SUBSTRING(A.doc_date, 1, 6) = pPeriod
			AND A.amount <> 0
			AND A.record_type <> 0
		GROUP BY A.partner_id, B.order_type, B.mlm_r_report_mutasi_saldo_id)
	UPDATE tt_mlm_r_report_mutasi_saldo Z
		SET credit_amount = Z.credit_amount+A.credit_amount, debt_amount = Z.debt_amount+A.debt_amount
	FROM TEMP A
	WHERE Z.session_id = pSessionId
		AND A.mlm_r_report_mutasi_saldo_id = Z.mlm_r_report_mutasi_saldo_id
		AND A.partner_id = Z.partner_id
		AND A.order_type = Z.order_type;

	OPEN pRefHeader FOR
		SELECT B.partner_code, B.partner_name,
			A.credit_amount, A.debt_amount, (A.debt_amount-A.credit_amount) AS total_saldo_akhir,
			CASE WHEN C.active = vYes THEN 'Aktif' ELSE 'Tidak Aktif' END AS status
		FROM tt_mlm_r_report_mutasi_saldo A
		INNER JOIN m_partner B ON A.partner_id = B.partner_id
		INNER JOIN mlm_ds C ON B.partner_id = C.partner_id
		WHERE session_id = pSessionId
		ORDER BY B.partner_code ASC;
	RETURN NEXT pRefHeader;
	
	DELETE FROM tt_mlm_r_report_mutasi_saldo WHERE session_id = pSessionId;
	
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;