f_get_calculate_nett_sell_price_by_promo

CREATE OR REPLACE FUNCTION f_get_calculate_nett_sell_price_by_promo(CHARACTER VARYING, NUMERIC)
  RETURNS NUMERIC AS
$BODY$
DECLARE
    pPromoCode          ALIAS FOR $1;
    pGrossSellPrice     ALIAS FOR $2;

    vPromotype1         CHARACTER VARYING;
    vPromotype2         CHARACTER VARYING;
    vPromodisc1         numeric(15,2);
    vPromodisc2         numeric(15,2);
    vNettSellPrice  numeric(15,2) := 0;

    vPromoTypePct       CHARACTER VARYING := 'PROMO.DISC.PCT';
    vPromoTypeAmt       CHARACTER VARYING := 'PROMO.DISC.AMT';
    vPromoTypeVcr       CHARACTER VARYING := 'PROMO.VCR';
    vPromoTypeBigi      CHARACTER VARYING := 'PROMO.BIGI';

    vDiscountTypePct        CHARACTER VARYING := 'AMT';
    vDiscountTypeAmt        CHARACTER VARYING := 'PCT';
    vDiscountTypeSp     CHARACTER VARYING := 'SP';
    vEmpty          CHARACTER VARYING := '';

BEGIN

       -- untuk sementara query hanya support untuk discount dengan type Percent
       SELECT B.promo_type, B.disc_percent, C.promo_type, C.disc_percent INTO vPromotype1, vPromodisc1, vPromotype2, vPromodisc2
       FROM m_promo A
         LEFT JOIN m_promo_detail B ON A.promo_id = B.promo_id AND B.line_no = 1
         LEFT JOIN m_promo_detail C ON A.promo_id = C.promo_id AND C.line_no = 2
       WHERE A.promo_code = pPromoCode;

       -- jika barang memiliki promo_type BIGI atau normal price, maka yang dikembalikan adalah gross_sell_price
       IF vPromotype1 = vPromoTypeBigi OR pPromoCode IS NULL THEN
          RETURN COALESCE(pGrossSellPrice, 0);
       END IF;

       SELECT pGrossSellPrice * ((100-COALESCE(vPromodisc1,0))/100) * ((100-COALESCE(vPromodisc2,0))/100) INTO vNettSellPrice;

       RETURN COALESCE(vNettSellPrice, 0);

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;