f_get_catalog_price_by_partner

/*
* modify: Widana 01 Agt 2017
* 1. Get Area Sales, insert into vAreaSales
* 2. Get Price by selection (IF)
* 3. support mengembalikan value catalog price dari Zone 0 - Zone 9
*/

CREATE OR REPLACE FUNCTION f_get_catalog_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;

    IF vAreaSales = vArea0 THEN
        SELECT catalog_price0 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea1 THEN
        SELECT current_price INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea2 THEN
        SELECT catalog_price2 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea3 THEN
        SELECT catalog_price3 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea4 THEN
        SELECT catalog_price4 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea5 THEN
        SELECT catalog_price5 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea6 THEN
        SELECT catalog_price6 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea7 THEN
        SELECT catalog_price7 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea8 THEN
        SELECT catalog_price8 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    IF vAreaSales = vArea9 THEN
        SELECT catalog_price9 INTO vResult
        FROM product_catalog
        WHERE id = pProductCatalogId;
    END IF;

    RETURN vResult;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;