Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * modify: Widana 01 Agt 2017
- * 1. Get Area Sales, insert into vAreaSales
- * 2. Get Price by selection (IF)
- * 3. support mengembalikan value nett price dari Zone 0 - Zone 9
- */
- CREATE OR REPLACE FUNCTION f_get_nett_price_by_partner(BIGINT, BIGINT)
- RETURNS NUMERIC AS
- $BODY$
- DECLARE
- pPartnerId ALIAS FOR $1;
- pProductCatalogId ALIAS FOR $2;
- vResult numeric(15,2);
- vAreaSales CHARACTER varying(10);
- vArea0 CHARACTER varying(10) := 'Zone 0';
- vArea1 CHARACTER varying(10) := 'Zone 1';
- vArea2 CHARACTER varying(10) := 'Zone 2';
- vArea3 CHARACTER varying(10) := 'Zone 3';
- vArea4 CHARACTER varying(10) := 'Zone 4';
- vArea5 CHARACTER varying(10) := 'Zone 5';
- vArea6 CHARACTER varying(10) := 'Zone 6';
- vArea7 CHARACTER varying(10) := 'Zone 7';
- vArea8 CHARACTER varying(10) := 'Zone 8';
- vArea9 CHARACTER varying(10) := 'Zone 9';
- BEGIN
- SELECT B.area_sales_code INTO vAreaSales
- FROM mlm_ds A
- JOIN m_area_sales B ON A.area_sales_id = B.area_sales_id
- WHERE A.partner_id = pPartnerId;
- SELECT B.area_sales_code INTO vAreaSales
- FROM mlm_ds A
- JOIN m_area_sales B ON A.area_sales_id = B.area_sales_id
- WHERE A.partner_id = pPartnerId;
- IF vAreaSales = vArea0 THEN
- SELECT nett_price0 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSEIF vAreaSales = vArea2 THEN
- SELECT nett_price2 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSEIF vAreaSales = vArea3 THEN
- SELECT nett_price3 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSEIF vAreaSales = vArea4 THEN
- SELECT nett_price4 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSEIF vAreaSales = vArea5 THEN
- SELECT nett_price5 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSEIF vAreaSales = vArea6 THEN
- SELECT nett_price6 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSEIF vAreaSales = vArea7 THEN
- SELECT nett_price7 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSEIF vAreaSales = vArea8 THEN
- SELECT nett_price8 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSEIF vAreaSales = vArea9 THEN
- SELECT nett_price9 INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- ELSE
- SELECT nett_price INTO vResult
- FROM product_catalog
- WHERE id = pProductCatalogId;
- END IF;
- RETURN vResult;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
Advertisement
Add Comment
Please, Sign In to add comment