REPORT BALANCE STOCK VS MONTHLY PRICE

/*
 * 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);

BEGIN

    vEmptyValue := ' ';
    vEmptyId := -99;
    vAllId := -99;
    vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);
    vYes := 'Y';
    vNo := 'N';
    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;

    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;

    /* Adrian, Mar 24, 2017
     * tax percentage untuk gross sell price di-set selalu 0
     * Pada saat update:
     * - tax amount dihitung terpisah
     * - untuk flg tax amount Y, maka gross sell price dikurangi dengan tax amount
     */
    INSERT INTO tr_sell_price
    (session_id, tenant_id, ou_id, product_id,
    curr_code, gross_sell_price, flg_tax_amount,
    tax_percentage)
    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
    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);

    INSERT INTO tr_sell_price
    (session_id, tenant_id, ou_id, product_id,
        curr_code, gross_sell_price, flg_tax_amount,
        tax_percentage)
    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
    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
    SET tax_amount = f_get_tax_amount(tenant_id, gross_sell_price, flg_tax_amount, tax_percentage);

    UPDATE tr_sell_price
    SET gross_sell_price = gross_sell_price - tax_amount
    WHERE flg_tax_amount = vYes;

    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;

    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(A.qty * B.tax_amount) AS saldo_tax_amount
    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
    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;
  /