-- Function: r_outlet_rekap_nilai_saldo_stok_akhir(character varying, bigint, bigint, character varying, bigint)

-- DROP FUNCTION r_outlet_rekap_nilai_saldo_stok_akhir(character varying, bigint, bigint, character varying, bigint);

CREATE OR REPLACE FUNCTION r_outlet_rekap_nilai_saldo_stok_akhir(character varying, bigint, bigint, character varying, 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;
    pUserId                 ALIAS FOR $5;

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

BEGIN

    vEmptyValue := ' ';
    vEmptyId := -99;
    vAllId := -99;
    vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);

    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;
    DELETE FROM tt_input_data WHERE session_id = pSessionId;
    DELETE FROM tt_output_data WHERE session_id = pSessionId;

    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
    WHERE A.tenant_id = pTenantId AND
        A.warehouse_id = vWarehouseId 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;

    INSERT INTO tr_sell_price
    (session_id, tenant_id, product_id, ou_id,
    curr_code, gross_sell_price, flg_tax_amount)
    SELECT pSessionId, A.tenant_id, A.product_id, B.ou_id,
        B.curr_code, B.gross_sell_price, B.flg_tax_amount
    FROM tr_saldo_stok A, m_sell_price_product B
    WHERE A.session_id = pSessionId AND
        A.tenant_id = B.tenant_id AND
        B.ou_id = pOuId AND
        A.product_id = B.product_id AND
        pDateTo BETWEEN B.date_from AND B.date_to;

    INSERT INTO tr_sell_price
    (session_id, tenant_id, product_id, ou_id,
    curr_code, gross_sell_price, flg_tax_amount)
    SELECT pSessionId, A.tenant_id, A.product_id, B.ou_id,
        B.curr_code, B.gross_sell_price, B.flg_tax_amount
    FROM tr_saldo_stok A, m_sell_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
        pDateTo BETWEEN B.date_from AND B.date_to 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);

    INSERT INTO tr_sell_price
    (session_id, tenant_id, product_id, ou_id,
        curr_code, gross_sell_price, flg_tax_amount)
    SELECT pSessionId, A.tenant_id, A.product_id, pOuId,
        f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), 0, 'Y'
    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);

    Open pRefHeader FOR
    SELECT fullName AS full_name, pDateTo AS date_to
    FROM t_user
    WHERE user_id = pUserId;

    RETURN NEXT pRefHeader;

    INSERT INTO tt_input_data
    (session_id, id_data, data_group_1,
    data_group_2, data_group_3, data_measure)
    SELECT pSessionId, 'SALDO', B.curr_code,
        C.ctgr_product_name, D.sub_ctgr_product_name, SUM(A.qty * B.gross_sell_price)
    FROM tr_saldo_stok A, tr_sell_price B, m_ctgr_product C, m_sub_ctgr_product D
    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
    GROUP BY B.curr_code, C.ctgr_product_name, D.sub_ctgr_product_name;

    SELECT f_agregate_percentage(pSessionId, 'SALDO', ';', 3, 1, 2) INTO vCount;

    Open pRefDetail FOR
    SELECT A.data_group_1 AS curr_code, A.data_group_2 AS ctgr_product_name,
        A.data_group_3 AS sub_ctgr_product_name, A.sum_measure AS saldo_amount, A.percentage_measure AS percentage
    FROM tt_output_data A
    WHERE A.session_id = pSessionId AND
        A.id_data = 'SALDO';

    RETURN NEXT pRefDetail;

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

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION r_outlet_rekap_nilai_saldo_stok_akhir(character varying, bigint, bigint, character varying, bigint)
  OWNER TO sts;
GRANT EXECUTE ON FUNCTION r_outlet_rekap_nilai_saldo_stok_akhir(character varying, bigint, bigint, character varying, bigint) TO sts;
GRANT EXECUTE ON FUNCTION r_outlet_rekap_nilai_saldo_stok_akhir(character varying, bigint, bigint, character varying, bigint) TO public;