Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * 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;
- /**
- * Adrian, Mar 24, 2017
- * Insert tr_saldo_stok untuk OU
- */
- 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 24, 2017
- * Insert tr_saldo_stok untuk 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 24, 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, 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, 0, 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) AND
- B.flg_tax_amount = vYes;
- 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) AND
- B.flg_tax_amount = vNo;
- 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 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;
- 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(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;
- /
Add Comment
Please, Sign In to add comment