CREATE OR REPLACE FUNCTION r_report_daily_stock_mutation_with_amount(
    character varying,
    bigint,
    bigint,
    bigint,
    character varying,
    bigint,
    bigint,
    character varying,
    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;
	pPeriodFrom								ALIAS FOR $8;
	pPeriodTo								ALIAS FOR $9;
	pShowAllProduct							ALIAS FOR $10;

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

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

	vDocTypeSaldoAwal						bigint;

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

	vStartTrxDate							character varying;
	vMaxDateYearMonthCogs								character varying;


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';
	vAdminLedgerCodeCost					:= 'COST.FG';
	vStatusLedgerDone						:= '1';
	vLastYearMonthProcessed					:= '';
	vLastYearMonthProcessedForNilaiSatuan	:= '';
	vNextLastYearMonthProcessed				:= '';
	vDateMinusOneDay						:= '';
	vMaxDateYearMonthCogs								:= '';


	-- 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;

	SELECT MAX(date_year_month) FROM in_summary_monthly_cogs WHERE date_year_month <= LEFT(pPeriodTo,6) INTO vMaxDateYearMonthCogs;

	SELECT f_get_value_system_config_by_param_code(pTenantId, 'start.trx.date') INTO vStartTrxDate;

	-- 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(pPeriodFrom,6);

	SELECT COALESCE(MAX(date_year_month), '') INTO vLastYearMonthProcessedForNilaiSatuan
	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 = vAdminLedgerCodeCost
	AND A.status_ledger = vStatusLedgerDone
	AND A.date_year_month <= LEFT(pPeriodFrom,6);

	IF (vLastYearMonthProcessed = '') THEN
		vNextLastYearMonthProcessed = vStartTrxDate;
	ELSE
		-- vLastYearMonthProcessed + 1 month
		SELECT TO_CHAR(CONCAT(vLastYearMonthProcessed,'01')::date + INTERVAL '1 month', 'YYYYMM') INTO vNextLastYearMonthProcessed;
	END IF;

	raise notice '%',pPeriodFrom;
	raise notice '%',vLastYearMonthProcessed;

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



	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 harga pokok penjualan
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan, nilai_satuan)
	SELECT DISTINCT pSessionId, A.product_id,  B.product_code, B.product_name,
		'' AS brand, f_get_uom_name(B.base_uom_id) AS satuan, COALESCE(A.avg_price, 0) AS nilai_satuan
	FROM in_summary_monthly_cogs A
	INNER JOIN m_product B ON A.product_id = B.product_id
	WHERE A.tenant_id = pTenantId
	AND A.date_year_month = vMaxDateYearMonthCogs;

	-- insert qty_terima_barang supplier 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)
	AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
	GROUP BY A.product_id, A.base_uom_id;

	-- insert qty_terima_barang antar gudang 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_antar_gudang)
	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_antar_gudang
	FROM in_log_product_balance_stock A
	WHERE A.tenant_id = pTenantId
	AND A.warehouse_id = pWarehouseId
	AND A.doc_type_id IN (vDocTypeGoodsTransferIn)
	AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
	GROUP BY A.product_id, A.base_uom_id;

	-- insert qty_terima_barang antar gudang 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_antar_gudang)
	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_antar_gudang
	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 BETWEEN pPeriodFrom AND pPeriodTo
	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 BETWEEN pPeriodFrom AND pPeriodTo
	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 BETWEEN pPeriodFrom AND pPeriodTo
	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 BETWEEN pPeriodFrom AND pPeriodTo
	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 BETWEEN pPeriodFrom AND pPeriodTo
	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 BETWEEN pPeriodFrom AND pPeriodTo
	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 BETWEEN pPeriodFrom AND pPeriodTo
	GROUP BY A.product_id, A.base_uom_id;


	-- insert data yg belum ada di tt
	INSERT INTO tt_r_daily_stock_mutation(
	    session_id, product_id, product_code, product_name,
	    brand, satuan)
    SELECT pSessionId,A.product_id,A.product_code,A.product_name,
    '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan
	FROM m_product A
	WHERE A.active='Y' AND tenant_id =10 AND NOT EXISTS(
		SELECT 1 from tt_r_daily_stock_mutation B WHERE B.product_id = A.product_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, pPeriodFrom AS period_from, pPeriodTo AS period_to,
			CASE WHEN pShowAllProduct = vOnlyWithStockMovement THEN 'Only With Stock Movement' ELSE 'All' END AS showed_product;
	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(ABS(A.saldo_awal)) AS saldo_awal,SUM(ABS(A.saldo_awal))*SUM(A.nilai_satuan) AS nilai_saldo_awal,
				SUM(ABS(A.qty_terima_barang)) AS qty_terima_barang_supplier, SUM(ABS(A.qty_terima_barang))*SUM(A.nilai_satuan) AS nilai_terima_barang_supplier,
				SUM(ABS(A.qty_terima_barang_antar_gudang)) AS qty_terima_barang_antar_gudang, SUM(ABS(A.qty_terima_barang_antar_gudang))*SUM(A.nilai_satuan) AS nilai_terima_barang_antar_gudang,
				SUM(ABS(A.qty_jual)) AS qty_jual,  SUM(ABS(A.qty_jual))*SUM(A.nilai_satuan) AS nilai_jual,
				SUM(ABS(A.qty_retur_beli)) As qty_retur_beli, SUM(ABS(A.qty_retur_beli))*SUM(A.nilai_satuan) AS nilai_retur_beli,
				SUM(ABS(A.qty_retur_jual)) AS qty_retur_jual,  SUM(ABS(A.qty_retur_jual))*SUM(A.nilai_satuan) AS nilai_retur_jual,
				SUM(ABS(A.qty_keluar_barang)) AS qty_keluar_barang, SUM(ABS(A.qty_keluar_barang))*SUM(A.nilai_satuan) AS nilai_keluar_barang,
				SUM(A.qty_adj) AS qty_adj, SUM(A.qty_adj)*SUM(A.nilai_satuan) AS nilai_adj,
				(SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_terima_barang_antar_gudang) + 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,
				SUM(A.nilai_satuan) AS nilai_satuan,
				SUM(A.nilai_satuan) * (SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_terima_barang_antar_gudang) + SUM(A.qty_jual) + SUM(A.qty_retur_beli) +
					SUM(A.qty_retur_jual) + SUM(A.qty_keluar_barang) + SUM(A.qty_adj)) AS nilai_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_terima_barang_antar_gudang) != 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(ABS(A.saldo_awal)) AS saldo_awal,SUM(ABS(A.saldo_awal))*SUM(A.nilai_satuan) AS nilai_saldo_awal,
				SUM(ABS(A.qty_terima_barang)) AS qty_terima_barang_supplier, SUM(ABS(A.qty_terima_barang))*SUM(A.nilai_satuan) AS nilai_terima_barang_supplier,
				SUM(ABS(A.qty_terima_barang_antar_gudang)) AS qty_terima_barang_antar_gudang, SUM(ABS(A.qty_terima_barang_antar_gudang))*SUM(A.nilai_satuan) AS nilai_terima_barang_antar_gudang,
				SUM(ABS(A.qty_jual)) AS qty_jual,  SUM(ABS(A.qty_jual))*SUM(A.nilai_satuan) AS nilai_jual,
				SUM(ABS(A.qty_retur_beli)) As qty_retur_beli, SUM(ABS(A.qty_retur_beli))*SUM(A.nilai_satuan) AS nilai_retur_beli,
				SUM(ABS(A.qty_retur_jual)) AS qty_retur_jual,  SUM(ABS(A.qty_retur_jual))*SUM(A.nilai_satuan) AS nilai_retur_jual,
				SUM(ABS(A.qty_keluar_barang)) AS qty_keluar_barang, SUM(ABS(A.qty_keluar_barang))*SUM(A.nilai_satuan) AS nilai_keluar_barang,
				SUM(A.qty_adj) AS qty_adj, SUM(A.qty_adj)*SUM(A.nilai_satuan) AS nilai_adj,
				(SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_terima_barang_antar_gudang) + 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,
				SUM(A.nilai_satuan) AS nilai_satuan,
				SUM(A.nilai_satuan) * (SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_terima_barang_antar_gudang) + SUM(A.qty_jual) + SUM(A.qty_retur_beli) +
					SUM(A.qty_retur_jual) + SUM(A.qty_keluar_barang) + SUM(A.qty_adj)) AS nilai_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;
/