CREATE OR REPLACE FUNCTION r_report_daily_stock_mutation(
    character varying,
    bigint,
    bigint,
    bigint,
    character varying,
    bigint,
    bigint,
    character varying,
    character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
	pRefHeader								REFCURSOR := 'refHeader';
	pRefDetail								REFCURSOR := 'refDetail';
	pSessionId								ALIAS FOR $1;
	pTenantId								ALIAS FOR $2;
	pUserId									ALIAS FOR $3;
	pRoleId									ALIAS FOR $4;
	pDatetime								ALIAS FOR $5;
	pOuId									ALIAS FOR $6;
	pWarehouseId							ALIAS FOR $7;
	pDate									ALIAS FOR $8;
	pShowAllProduct							ALIAS FOR $9;

	vEmptyId								bigint;
	vOuCodeName								character varying(100);
	vWarehouseCodeName						character varying(100);
	vOnlyWithStockMovement					character varying(50);

	vNol									bigint;
	vDocTypeReceiveGoods					bigint;
	vDocTypeGoodsTransferIn					bigint;
	vDocTypeGoodsTransferInReceipt				bigint;
	vDocTypeDeliveryOrder					bigint;
	vDocTypeDeliveryOrderReceipt			bigint;
	vDocTypePointOfSales					bigint;
	vDocTypePointOfSalesVoid				bigint;
	vDocTypeClaimNote						bigint;
	vDocTypeReturnNote						bigint;
	vDocTypeGoodsTransferOut				bigint;
	vDocTypeAdjusmentStockQty				bigint;
	vDocTypeAdjusmentStockQtyAmount			bigint;

	vDocTypeSaldoAwal						bigint;

	vAdminLedgerCodeInvQty					character varying(5);
	vStatusLedgerDone						character varying(5);
	vLastYearMonthProcessed					character varying(30);
	vNextLastYearMonthProcessed				character varying(30);
	vDateMinusOneDay						character varying(30);


BEGIN

	vEmptyId								:= -99;
	vOuCodeName								:= 'ALL';
	vWarehouseCodeName						:= 'ALL';
	vOnlyWithStockMovement					:= 'ONLYWITHSTOCKMOVEMENT';
	vNol									:= 0;

	vDocTypeReceiveGoods					:= 111;
	vDocTypeGoodsTransferIn					:= 535;
	vDocTypeDeliveryOrder					:= 311;
	vDocTypeDeliveryOrderReceipt			:= 526;
	vDocTypePointOfSales					:= 401;
	vDocTypePointOfSalesVoid				:= 405;
	vDocTypeClaimNote						:= 511;
	vDocTypeReturnNote						:= 502;
	vDocTypeGoodsTransferOut				:= 533;
	vDocTypeAdjusmentStockQty				:= 521;
	vDocTypeAdjusmentStockQtyAmount			:= 522;
	vDocTypeGoodsTransferInReceipt			:= 536;

	vDocTypeSaldoAwal						:= -99;

	vAdminLedgerCodeInvQty					:= 'INV';
	vStatusLedgerDone						:= '1';
	vLastYearMonthProcessed					:= '';
	vNextLastYearMonthProcessed				:= '';
	vDateMinusOneDay						:= '';

	-- filter ou id
	IF(pOuId <> vEmptyId) THEN
		SELECT ou_code||' - '||ou_name INTO vOuCodeName
		FROM t_ou
		WHERE ou_id = pOuId;
	END IF;

	-- filter warehouse id
	IF(pWarehouseId <> vEmptyId) THEN
		SELECT warehouse_code||' - '||warehouse_name INTO vWarehouseCodeName
		FROM m_warehouse
		WHERE warehouse_id = pWarehouseId;
	END IF;

	-- get last year month processed
	SELECT COALESCE(MAX(date_year_month), '') INTO vLastYearMonthProcessed
	FROM m_admin_process_ledger A, m_ou_structure B
	WHERE A.tenant_id = pTenantId
	AND B.ou_id = pOuId
	AND B.ou_bu_id = A.ou_id
	AND A.ledger_code = vAdminLedgerCodeInvQty
	AND A.status_ledger = vStatusLedgerDone
	AND A.date_year_month < LEFT(pDate,6);

	-- vLastYearMonthProcessed + 1 month
	SELECT TO_CHAR(CONCAT(vLastYearMonthProcessed,'01')::date + INTERVAL '1 month', 'YYYYMM') INTO vNextLastYearMonthProcessed;

	-- H-1 pDate
	SELECT TO_CHAR(pDate::date - INTERVAL '1 day', 'YYYYMMDD') INTO vDateMinusOneDay; --202002



	DELETE FROM tt_r_daily_stock_mutation WHERE session_id = pSessionId;


	-- insert saldo_awal into table temp tt_r_daily_stock_mutation from in_summary_monthly_qty
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, saldo_awal)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, COALESCE(SUM(A.qty), 0) AS saldo_awal
	FROM in_summary_monthly_qty A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.date_year_month = vNextLastYearMonthProcessed
	AND A.doc_type_id =  vDocTypeSaldoAwal
	GROUP BY A.product_id, A.base_uom_id;

	-- insert saldo_awal into table temp tt_r_daily_stock_mutation from in_log_product_balance_stock
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, saldo_awal)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, COALESCE(SUM(A.qty), 0) AS saldo_awal
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND SUBSTRING(A.doc_date, 1, 6) > vLastYearMonthProcessed
	AND A.doc_date <= vDateMinusOneDay
	GROUP BY A.product_id, A.base_uom_id;



	-- insert qty_terima_barang table tt_r_daily_stock_mutation
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, qty_terima_barang)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_terima_barang
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id IN (vDocTypeReceiveGoods, vDocTypeGoodsTransferIn)
	AND A.doc_date = pDate
	GROUP BY A.product_id, A.base_uom_id;

	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, qty_terima_barang)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_terima_barang
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id IN (vDocTypeGoodsTransferInReceipt)
	AND A.qty > 0
	AND A.doc_date = pDate
	GROUP BY A.product_id, A.base_uom_id;

	-- insert qty_jual table tt_r_daily_stock_mutation
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, qty_jual)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_jual
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id IN (vDocTypeDeliveryOrder, vDocTypeDeliveryOrderReceipt, vDocTypePointOfSales, vDocTypePointOfSalesVoid)
	AND A.doc_date = pDate
	GROUP BY A.product_id, A.base_uom_id;

	-- insert qty_retur_beli table tt_r_daily_stock_mutation
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, qty_retur_beli)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_retur_beli
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id = vDocTypeClaimNote
	AND A.doc_date = pDate
	GROUP BY A.product_id, A.base_uom_id;

	-- insert qty_retur_jual table tt_r_daily_stock_mutation
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, qty_retur_jual)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_retur_jual
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id = vDocTypeReturnNote
	AND A.doc_date = pDate
	GROUP BY A.product_id, A.base_uom_id;

	-- insert qty_keluar_barang table tt_r_daily_stock_mutation
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, qty_keluar_barang)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_keluar_barang
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id = vDocTypeGoodsTransferOut
	AND A.doc_date = pDate
	GROUP BY A.product_id, A.base_uom_id;

	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, qty_keluar_barang)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_keluar_barang
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id = vDocTypeGoodsTransferInReceipt
	AND A.qty < 0
	AND A.doc_date = pDate
	GROUP BY A.product_id, A.base_uom_id;

	-- insert qty_adj table tt_r_daily_stock_mutation
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, qty_adj)
	SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
		'' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_adj
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id IN (vDocTypeAdjusmentStockQty, vDocTypeAdjusmentStockQtyAmount)
	AND A.doc_date = pDate
	GROUP BY A.product_id, A.base_uom_id;


	OPEN pRefHeader FOR
		SELECT CASE WHEN pOuId = vEmptyId THEN -99 ELSE pOuId END AS ou_id, vOuCodeName AS ou_code_name,
			CASE WHEN pWarehouseId = vEmptyId THEN -99 ELSE pWarehouseId END AS warehouse_id, vWarehouseCodeName AS warehouse_code_name,
			pDatetime AS datetime, f_get_username(pUserId) AS username, pDate AS date;
	RETURN NEXT pRefHeader;


	IF(pShowAllProduct = vOnlyWithStockMovement) THEN
		OPEN pRefDetail FOR
			SELECT A.product_code, A.product_name,
				CONCAT(f_get_brand_code(B.brand_id), ' - ', f_get_brand_name(B.brand_id)) AS brand,
				A.satuan,
				SUM(A.saldo_awal) AS saldo_awal, SUM(A.qty_terima_barang) AS qty_terima_barang,
				SUM(A.qty_jual) AS qty_jual, SUM(A.qty_retur_beli) As qty_retur_beli,
				SUM(A.qty_retur_jual) AS qty_retur_jual, SUM(A.qty_keluar_barang) AS qty_keluar_barang, SUM(A.qty_adj) AS qty_adj,
				(SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_jual) + SUM(A.qty_retur_beli) +
					SUM(A.qty_retur_jual) + SUM(A.qty_keluar_barang) + SUM(A.qty_adj)) AS saldo_akhir
			FROM tt_r_daily_stock_mutation A
				INNER JOIN m_product B ON A.product_id = B.product_id
			GROUP BY A.product_code, A.product_name, B.brand_id, A.satuan
			HAVING SUM(A.qty_terima_barang) != 0 OR SUM(A.qty_jual) != 0 OR SUM(A.qty_retur_beli) != 0
				OR SUM(A.qty_retur_jual) != 0 OR SUM(A.qty_keluar_barang) != 0 OR SUM(A.qty_adj) != 0
			ORDER BY A.product_name;
		RETURN NEXT pRefDetail;
	ELSE
		OPEN pRefDetail FOR
			SELECT A.product_code, A.product_name,
				CONCAT(f_get_brand_code(B.brand_id), ' - ', f_get_brand_name(B.brand_id)) AS brand,
				A.satuan,
				SUM(A.saldo_awal) AS saldo_awal, SUM(A.qty_terima_barang) AS qty_terima_barang,
				SUM(A.qty_jual) AS qty_jual, SUM(A.qty_retur_beli) As qty_retur_beli,
				SUM(A.qty_retur_jual) AS qty_retur_jual, SUM(A.qty_keluar_barang) AS qty_keluar_barang, SUM(A.qty_adj) AS qty_adj,
				(SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_jual) + SUM(A.qty_retur_beli) +
					SUM(A.qty_retur_jual) + SUM(A.qty_keluar_barang) + SUM(A.qty_adj)) AS saldo_akhir
			FROM tt_r_daily_stock_mutation A
				INNER JOIN m_product B ON A.product_id = B.product_id
			GROUP BY A.product_code, A.product_name, B.brand_id, A.satuan
			ORDER BY A.product_name;
		RETURN NEXT pRefDetail;
	END IF;


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