R OUT:LET SALDO STOK AKHIR VS HARGA BELI

/*
 * rekap stok
 * filter mandatory : session_id, tenant_id, ou_id, date_to
 * filter optional : category product, sub category product, product
 * jika filter optional diisi -99 artinya data digunakan semua
 */
CREATE OR REPLACE FUNCTION r_outlet_saldo_stok_akhir_vs_harga_beli(character varying, bigint, bigint, character varying, bigint, bigint, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
	pRefHeader			REFCURSOR := 'refHeader';
	pRefDetail			REFCURSOR := 'refDetail';
	pSessionId				ALIAS FOR $1;
	pTenantId				ALIAS FOR $2;
	pOuId					ALIAS FOR $3;
	pDateTo					ALIAS FOR $4;
	pCtgrProductId			ALIAS FOR $5;
	pSubCtgrProductId		ALIAS FOR $6;
	pUserId					ALIAS FOR $7;

	vEmptyValue				character varying(1);
	vEmptyId				bigint;
	vAllId					bigint;
	vWarehouseId			bigint;
	vParentOuId				bigint;
	vRoundingMode			character varying(5);

	vYes					character varying(1);
	vNo						character varying(1);
	vNone					character varying(4);

BEGIN

	vEmptyValue := ' ';
	vEmptyId := -99;
	vAllId := -99;
	vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);
	vYes := 'Y';
	vNo := 'N';
	vNone := 'None';
	SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingMode;

	/* SELECT warehouse_id INTO vWarehouseId
	FROM i_outlet
	WHERE tenant_id = pTenantId AND ou_id = pOuId; */

	DELETE FROM tr_saldo_stok WHERE session_id = pSessionId;
	DELETE FROM tr_sell_price WHERE session_id = pSessionId;

	/**
	 * Adrian, Mar 24, 2017
	 * Insert tr_saldo_stok untuk OU yang dipilih
	 */
	INSERT INTO tr_saldo_stok
	(session_id, tenant_id, product_id, base_uom_id, qty,
	product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
	SELECT pSessionId, A.tenant_id, A.product_id, A.base_uom_id, SUM(A.qty),
		B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id
	FROM in_product_balance_stock A
		INNER JOIN m_product B ON A.product_id = B.product_id
		INNER JOIN m_warehouse_ou C ON C.warehouse_id = A.warehouse_id
		INNER JOIN m_ou_structure D ON D.ou_id = C.ou_id
	WHERE A.tenant_id = pTenantId AND
		D.ou_id = pOuId AND
		A.qty <> 0
	GROUP BY A.tenant_id, A.product_id, A.base_uom_id,
		B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id;

	/**
	 * Adrian, Mar 24, 2017
	 * Insert tr_saldo_stok untuk Outlet jika OU yang dipilih bukan Outlet
	 */
	INSERT INTO tr_saldo_stok
	(session_id, tenant_id, product_id, base_uom_id, qty,
	product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
	SELECT pSessionId, A.tenant_id, A.product_id, A.base_uom_id, SUM(A.qty),
		B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id
	FROM in_product_balance_stock A
		INNER JOIN m_product B ON A.product_id = B.product_id
		INNER JOIN m_warehouse_ou C ON C.warehouse_id = A.warehouse_id
		INNER JOIN m_ou_structure D ON D.ou_id = C.ou_id
	WHERE A.tenant_id = pTenantId AND
		D.ou_bu_id = pOuId AND
		D.ou_id <> D.ou_bu_id AND
		A.qty <> 0
	GROUP BY A.tenant_id, A.product_id, A.base_uom_id,
		B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id;

	/**
	 * Adrian, Mar 24, 2017
	 * tax percentage gross sell price di-set selalu 0 untuk flg tax amount Y
	 * Pada saat update:
	 * - tax amount dihitung terpisah
	 */
	INSERT INTO tr_sell_price
	(session_id, tenant_id, ou_id, product_id,
	curr_code, gross_sell_price, flg_tax_amount,
	tax_percentage, tax_id)
	SELECT pSessionId, A.tenant_id, B.ou_id, A.product_id,
		B.curr_code, COALESCE(f_get_gross_price_from_nett_amount(B.amount, B.qty, B.flg_tax_amount, 0, vRoundingMode, f_get_digit_decimal_doc_curr(-99, B.curr_code)), 0), B.flg_tax_amount,
		B.tax_percentage, B.tax_id
	FROM tr_saldo_stok A, pu_monthly_price_product B
	WHERE A.session_id = pSessionId AND
		A.tenant_id = B.tenant_id AND
		A.product_id = B.product_id AND
		B.ou_id = vParentOuId AND
		B.year_month_date = SUBSTRING(pDateTo, 1, 6) AND
		B.flg_tax_amount = vYes;

	INSERT INTO tr_sell_price
	(session_id, tenant_id, ou_id, product_id,
	curr_code, gross_sell_price, flg_tax_amount,
	tax_percentage, tax_id)
	SELECT pSessionId, A.tenant_id, B.ou_id, A.product_id,
		B.curr_code, COALESCE(f_get_gross_price_from_nett_amount(B.amount, B.qty, B.flg_tax_amount, B.tax_percentage, vRoundingMode, f_get_digit_decimal_doc_curr(-99, B.curr_code)), 0), B.flg_tax_amount,
		B.tax_percentage, B.tax_id
	FROM tr_saldo_stok A, pu_monthly_price_product B
	WHERE A.session_id = pSessionId AND
		A.tenant_id = B.tenant_id AND
		A.product_id = B.product_id AND
		B.ou_id = vParentOuId AND
		B.year_month_date = SUBSTRING(pDateTo, 1, 6) AND
		B.flg_tax_amount = vNo;

	INSERT INTO tr_sell_price
	(session_id, tenant_id, ou_id, product_id,
		curr_code, gross_sell_price, flg_tax_amount,
		tax_percentage, tax_id)
	SELECT pSessionId, A.tenant_id, pOuId, A.product_id,
		f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), 0, 'Y',
		10.00, 1
	FROM tr_saldo_stok A
	WHERE A.session_id = pSessionId AND
		NOT EXISTS (SELECT 1 FROM tr_sell_price C
					WHERE A.session_id = C.session_id AND
						A.tenant_id = C.tenant_id AND
						A.product_Id = C.product_id);

	UPDATE tr_sell_price Z
	SET tax_amount = f_get_tax_amount(Z.tenant_id, A.qty * Z.gross_sell_price, Z.flg_tax_amount, Z.tax_percentage)
	FROM tr_saldo_stok A
	WHERE A.session_id = Z.session_id AND
		A.tenant_id = Z.tenant_id AND
		A.product_Id = Z.product_id;

	/**
	 * Adrian, Mar 27, 2017
	 * update tax_name
	 */
	UPDATE tr_sell_price Z
	SET tax_name = A.tax_name
	FROM m_tax A
	WHERE A.tax_id = Z.tax_id;

	UPDATE tr_sell_price Z
	SET tax_name = VNone
	WHERE Z.tax_id = vEmptyId;

	IF pCtgrProductId <> vAllId THEN

		DELETE FROM tr_saldo_stok WHERE session_id = pSessionId AND ctgr_product_id <> pCtgrProductId;

	END IF;

	IF pSubCtgrProductId <> vAllId THEN

		DELETE FROM tr_saldo_stok WHERE session_id = pSessionId AND sub_ctgr_product_id <> pSubCtgrProductId;

	END IF;

	Open pRefHeader FOR
	SELECT B.ou_name AS outlet, A.fullname AS full_name, pDateTo AS date_to, pCtgrProductId AS ctgr_product_id, pSubCtgrProductId AS sub_ctgr_product_id
	FROM t_user A
	INNER JOIN t_ou B ON A.tenant_id = B.tenant_id
	WHERE A.user_id = pUserId AND B.ou_id = pOuId;

	RETURN NEXT pRefHeader;

	/**
	 * Adrian, Mar 27, 2017
	 * Add saldo_tax_amount, flg_tax_amount, tax_name
	 */
	Open pRefDetail FOR
	SELECT B.curr_code, C.ctgr_product_name AS ctgr_product_name, D.sub_ctgr_product_name AS sub_ctgr_product_name,
		A.product_code AS product_code, A.product_name AS product_name, E.uom_code AS uom_code,
		SUM(A.qty) AS qty, B.gross_sell_price AS gross_sell_price, SUM(A.qty * B.gross_sell_price) AS saldo_amount,
		SUM(B.tax_amount) AS saldo_tax_amount,
		CASE
			WHEN B.flg_tax_amount = vYes THEN 'Yes'
			ELSE 'No'
		END AS flg_tax_amount,
		B.tax_name
	FROM tr_saldo_stok A, tr_sell_price B, m_ctgr_product C, m_sub_ctgr_product D, m_uom E
	WHERE A.session_id = pSessionId AND
		A.session_id = B.session_id AND
		A.tenant_id = B.tenant_id AND
		A.product_id = B.product_id AND
		A.ctgr_product_id = C.ctgr_product_id AND
		A.sub_ctgr_product_id = D.sub_ctgr_product_id AND
		A.base_uom_id = E.uom_id
	GROUP BY B.curr_code, C.ctgr_product_name, D.sub_ctgr_product_name, A.product_code, A.product_name, E.uom_code, B.gross_sell_price, B.flg_tax_amount, B.tax_name
	ORDER BY B.curr_code, C.ctgr_product_name, D.sub_ctgr_product_name, A.product_code;

	RETURN NEXT pRefDetail;

	DELETE FROM tr_saldo_stok WHERE session_id = pSessionId;
	DELETE FROM tr_sell_price WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /