R OUTLET REKAP

/*
 * rekap stok
 * filter mandatory : session_id, tenant_id, ou_id, date_to
 */
CREATE OR REPLACE FUNCTION r_outlet_rekap_stok_akhir_vs_harga_beli(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;
    vRoundingMode           character varying(5);

    vYes                    character varying(1);
    vNo                     character varying(1);
    vNone                   character varying(4);

BEGIN

    vEmptyValue := ' ';
    vEmptyId := -99;
    vAllId := -99;
    vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);
    vYes := 'Y';
    vNo := 'N';
    vNone := 'None';
    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;
    DELETE FROM tt_input_data WHERE session_id = pSessionId;
    DELETE FROM tt_output_data WHERE session_id = pSessionId;

    /**
     * Adrian, Mar 27, 2017
     * Insert tr_saldo_stok untuk OU yang dipilih
     */
    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
        INNER JOIN m_warehouse_ou C ON C.warehouse_id = A.warehouse_id
        INNER JOIN m_ou_structure D ON D.ou_id = C.ou_id
    WHERE A.tenant_id = pTenantId AND
        D.ou_id = pOuId 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 27, 2017
     * Insert tr_saldo_stok untuk Outlet jika OU yang dipilih bukan Outlet
     */
    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
        INNER JOIN m_warehouse_ou C ON C.warehouse_id = A.warehouse_id
        INNER JOIN m_ou_structure D ON D.ou_id = C.ou_id
    WHERE A.tenant_id = pTenantId AND
        D.ou_bu_id = pOuId AND
        D.ou_id <> D.ou_bu_id 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 27, 2017
     * tax percentage gross sell price di-set selalu 0 untuk flg tax amount Y
     * Pada saat update:
     * - tax amount dihitung terpisah
     */
    INSERT INTO tr_sell_price
    (session_id, tenant_id, product_id, ou_id,
    curr_code, gross_sell_price, flg_tax_amount,
    tax_percentage, tax_id)
    SELECT pSessionId, A.tenant_id, A.product_id, B.ou_id,
        B.curr_code, COALESCE(f_get_gross_price_from_nett_amount(B.amount, B.qty, B.flg_tax_amount, 0, vRoundingMode, f_get_digit_decimal_doc_curr(-99, B.curr_code)), 0), B.flg_tax_amount,
        B.tax_percentage, B.tax_id
    FROM tr_saldo_stok A, pu_monthly_price_product B
    WHERE A.session_id = pSessionId AND
        A.tenant_id = B.tenant_id AND
        B.ou_id = vParentOuId AND
        A.product_id = B.product_id AND
        B.year_month_date = SUBSTRING(pDateTo, 1, 6) AND
        B.flg_tax_amount = vYes;

    INSERT INTO tr_sell_price
    (session_id, tenant_id, product_id, ou_id,
    curr_code, gross_sell_price, flg_tax_amount,
    tax_percentage, tax_id)
    SELECT pSessionId, A.tenant_id, A.product_id, B.ou_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, B.tax_id
    FROM tr_saldo_stok A, pu_monthly_price_product B
    WHERE A.session_id = pSessionId AND
        A.tenant_id = B.tenant_id AND
        B.ou_id = vParentOuId AND
        A.product_id = B.product_id AND
        B.year_month_date = SUBSTRING(pDateTo, 1, 6) AND
        B.flg_tax_amount = vNo;

    INSERT INTO tr_sell_price
    (session_id, tenant_id, product_id, ou_id,
        curr_code, gross_sell_price, flg_tax_amount,
        tax_percentage, tax_id)
    SELECT pSessionId, A.tenant_id, A.product_id, pOuId,
        f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), 0, 'Y',
        10.00, 1
    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 Z
    SET tax_amount = f_get_tax_amount(Z.tenant_id, A.qty * Z.gross_sell_price, Z.flg_tax_amount, Z.tax_percentage)
    FROM tr_saldo_stok A
    WHERE A.session_id = Z.session_id AND
        A.tenant_id = Z.tenant_id AND
        A.product_Id = Z.product_id;

    Open pRefHeader FOR
    SELECT B.ou_name AS outlet, fullName AS full_name, pDateTo AS date_to
    FROM t_user A
    INNER JOIN t_ou B ON A.tenant_id = B.tenant_id
    WHERE user_id = pUserId AND B.ou_id = pOuId;

    RETURN NEXT pRefHeader;

    /**
     * Adrian, Mar 27, 2017
     * Add tax_amount, flg_tax_amount, tax_id
     */
    INSERT INTO tt_input_data
    (session_id, id_data, data_group_1,
    data_group_2, data_group_3, data_measure,
    tax_amount, flg_tax_amount, tax_id,
    data_group_4, data_group_5)
    SELECT pSessionId, 'SALDO', B.curr_code,
        C.ctgr_product_name, D.sub_ctgr_product_name, SUM(A.qty * B.gross_sell_price),
        SUM(B.tax_amount), B.flg_tax_amount, B.tax_id,
        B.flg_tax_amount, B.tax_id
    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, B.flg_tax_amount, B.tax_id;

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

    /**
     * Adrian, Mar 27, 2017
     * update tax_name
     */
    UPDATE tt_output_data Z
    SET tax_name = A.tax_name
    FROM m_tax A
    WHERE A.tax_id = Z.tax_id;

    UPDATE tt_output_data Z
    SET tax_name = VNone
    WHERE Z.tax_id = vEmptyId;

    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,
        A.tax_amount AS saldo_tax_amount,
        CASE
            WHEN A.flg_tax_amount = vYes THEN 'Yes'
            ELSE 'No'
        END AS flg_tax_amount,
        A.tax_name
    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;
  /