Laporan Kartu Stock

CREATE OR REPLACE FUNCTION rpt.r_report_kartu_stock(character varying, bigint, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              	REFCURSOR := 'refHeader';
    pRefDetail             	 	REFCURSOR := 'refDetail';

    pSessionId              	ALIAS FOR $1;
    pRecordOwnerId          	ALIAS FOR $2;
    pProductId		        	ALIAS FOR $3;
    pStartDate		            ALIAS FOR $4;
    pEndDate		            ALIAS FOR $5;

    vEmptyId					bigint := -99;
    vBeginningQty				bigint := 0;
    vEndQty						bigint := 0;
    vCalculateQty				bigint := 0;

    vEmpty                  	character varying := '';
    vNo                     	character varying := 'N';
    vYes                    	character varying := 'Y';

    vYearMonth					character varying := '';
    vFilerProduct				character varying := '';
    vStartYearMonth				character varying := '';
    vEndYearMonth				character varying := '';

	vCursorRow					tt_summary_trx_document%ROWTYPE;


BEGIN

	DELETE FROM tt_monthly_summary_qty WHERE session_id = pSessionId;
	DELETE FROM tt_summary_trx_document	WHERE session_id = pSessionId;

	SELECT substring(pStartDate, 1, 6) INTO vStartYearMonth;

	SELECT substring(pEndDate, 1, 6) INTO vEndYearMonth;

	IF pProductId <> vEmptyId THEN
		vFilerProduct := ' AND A.product_id =  '''|| pProductId ||'''';
	END IF;

	OPEN pRefHeader FOR
		SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, pStartDate AS date_from, pEndDate AS date_to,
				CASE WHEN pProductId <> vEmptyId THEN f_get_product_code(pProductId) ELSE 'All' END AS product_code,
				CASE WHEN pProductId <> vEmptyId THEN f_get_product_name(pProductId) ELSE 'All' END AS product_name;
	RETURN NEXT pRefHeader;

	-- Stock summary beginning qty per montlhy
	EXECUTE '
		INSERT INTO tt_monthly_summary_qty(
			session_id, year_month, record_owner_id, beginning_qty
		)
		SELECT $4, A.year_month, A.record_owner_id, COALESCE(SUM(A.qty), 0)
		FROM in_summary_monthly_qty A
		WHERE A.record_owner_id = $1
			AND A.year_month >= $2
			AND A.year_month <= $3
			' || vFilerProduct ||'
		GROUP BY A.year_month, A.record_owner_id'
	USING pRecordOwnerId, vStartYearMonth, vEndYearMonth, pSessionId;

	-- Ambil Semua dokumen transaksi yang ada dalam jangka periode
	EXECUTE '
		INSERT INTO tt_summary_trx_document (
			session_id, record_owner_id, doc_no, doc_date, doc_desc, qty
		)
		SELECT $4, A.record_owner_id, A.ref_doc_no, A.ref_doc_date, f_get_doc_desc(A.ref_doc_type_id), COALESCE(SUM(A.qty), 0)
		FROM in_log_product_balance_stock A
		WHERE A.record_owner_id = $1
			AND A.ref_doc_date >= $2
			AND A.ref_doc_date <= $3
			' || vFilerProduct ||'
		GROUP BY A.record_owner_id, A.ref_doc_no, A.ref_doc_date, A.ref_doc_type_id
		ORDER BY A.ref_doc_date ASC'
	USING pRecordOwnerId, pStartDate, pEndDate, pSessionId;

    FOR vCursorRow IN
    	SELECT * FROM tt_summary_trx_document WHERE session_id = pSessionId
    LOOP
    	SELECT substring(vCursorRow.doc_date, 1, 6) INTO vYearMonth;

    	SELECT beginning_qty INTO vBeginningQty FROM tt_monthly_summary_qty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId AND year_month = vYearMonth;

    	vCalculateQty := vBeginningQty+vCursorRow.qty;

		INSERT INTO tt_sumary_trx_log_product_balance(
			session_id, doc_no, doc_date, doc_desc, beginning_qty,
			trx_qty, end_qty
		) VALUES (pSessionId, vCursorRow.doc_no, vCursorRow.doc_date, vCursorRow.doc_desc, vBeginningQty,
			vCursorRow.qty, vCalculateQty);

    	UPDATE tt_monthly_summary_qty SET beginning_qty = vCalculateQty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId AND year_month = vYearMonth;
    END LOOP;

    OPEN pRefDetail FOR
    	SELECT *
    	FROM tt_sumary_trx_log_product_balance
    	WHERE session_id = pSessionId;
    RETURN NEXT pRefDetail;

	DELETE FROM tt_monthly_summary_qty WHERE session_id = pSessionId;
	DELETE FROM tt_summary_trx_document	WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, ptg, htg, pb, pj, batch
  COST 100;
  /