widana

f_get_nett_price_by_partner

Aug 1st, 2017
82
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. * modify: Widana 01 Agt 2017
  3. * 1. Get Area Sales, insert into vAreaSales
  4. * 2. Get Price by selection (IF)
  5. * 3. support mengembalikan value nett price dari Zone 0 - Zone 9
  6. */
  7.  
  8. CREATE OR REPLACE FUNCTION f_get_nett_price_by_partner(BIGINT, BIGINT)
  9.   RETURNS NUMERIC AS
  10. $BODY$
  11. DECLARE
  12.     pPartnerId          ALIAS FOR $1;
  13.     pProductCatalogId       ALIAS FOR $2;
  14.     vResult             numeric(15,2);
  15.     vAreaSales          CHARACTER varying(10);
  16.  
  17.     vArea0              CHARACTER varying(10) := 'Zone 0';
  18.     vArea1              CHARACTER varying(10) := 'Zone 1';
  19.     vArea2              CHARACTER varying(10) := 'Zone 2';
  20.     vArea3              CHARACTER varying(10) := 'Zone 3';
  21.     vArea4              CHARACTER varying(10) := 'Zone 4';
  22.     vArea5              CHARACTER varying(10) := 'Zone 5';
  23.     vArea6              CHARACTER varying(10) := 'Zone 6';
  24.     vArea7              CHARACTER varying(10) := 'Zone 7';
  25.     vArea8              CHARACTER varying(10) := 'Zone 8';
  26.     vArea9              CHARACTER varying(10) := 'Zone 9';
  27. BEGIN
  28.  
  29.     SELECT B.area_sales_code INTO vAreaSales
  30.     FROM mlm_ds A
  31.     JOIN m_area_sales B ON A.area_sales_id = B.area_sales_id
  32.     WHERE A.partner_id = pPartnerId;
  33.  
  34.     SELECT B.area_sales_code INTO vAreaSales
  35.     FROM mlm_ds A
  36.     JOIN m_area_sales B ON A.area_sales_id = B.area_sales_id
  37.     WHERE A.partner_id = pPartnerId;
  38.    
  39.     IF vAreaSales = vArea0 THEN
  40.         SELECT nett_price0 INTO vResult
  41.         FROM product_catalog
  42.         WHERE id = pProductCatalogId;
  43.  
  44.      ELSEIF vAreaSales = vArea2 THEN
  45.         SELECT nett_price2 INTO vResult
  46.         FROM product_catalog
  47.         WHERE id = pProductCatalogId;
  48.        
  49.     ELSEIF vAreaSales = vArea3 THEN
  50.         SELECT nett_price3 INTO vResult
  51.         FROM product_catalog
  52.         WHERE id = pProductCatalogId;
  53.        
  54.     ELSEIF vAreaSales = vArea4 THEN
  55.         SELECT nett_price4 INTO vResult
  56.         FROM product_catalog
  57.         WHERE id = pProductCatalogId;
  58.        
  59.     ELSEIF vAreaSales = vArea5 THEN
  60.         SELECT nett_price5 INTO vResult
  61.         FROM product_catalog
  62.         WHERE id = pProductCatalogId;
  63.        
  64.     ELSEIF vAreaSales = vArea6 THEN
  65.         SELECT nett_price6 INTO vResult
  66.         FROM product_catalog
  67.         WHERE id = pProductCatalogId;
  68.        
  69.     ELSEIF vAreaSales = vArea7 THEN
  70.         SELECT nett_price7 INTO vResult
  71.         FROM product_catalog
  72.         WHERE id = pProductCatalogId;
  73.        
  74.     ELSEIF vAreaSales = vArea8 THEN
  75.         SELECT nett_price8 INTO vResult
  76.         FROM product_catalog
  77.         WHERE id = pProductCatalogId;
  78.        
  79.     ELSEIF vAreaSales = vArea9 THEN
  80.         SELECT nett_price9 INTO vResult
  81.         FROM product_catalog
  82.         WHERE id = pProductCatalogId;
  83.        
  84.     ELSE
  85.         SELECT nett_price INTO vResult
  86.         FROM product_catalog
  87.         WHERE id = pProductCatalogId;
  88.     END IF;
  89.  
  90.     RETURN vResult;
  91. END;
  92. $BODY$
  93.   LANGUAGE plpgsql VOLATILE
  94.   COST 100;
Advertisement
Add Comment
Please, Sign In to add comment