Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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;
Add Comment
Please, Sign In to add comment