Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION f_get_calculate_nett_sell_price_by_promo(CHARACTER VARYING, NUMERIC)
- RETURNS NUMERIC AS
- $BODY$
- DECLARE
- pPromoCode ALIAS FOR $1;
- pSellPrice 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(pSellPrice, 0);
- END IF;
- SELECT pSellPrice * ((100-COALESCE(vPromodisc1,0))/100) * ((100-COALESCE(vPromodisc2,0))/100) INTO vNettSellPrice;
- RETURN COALESCE(vNettSellPrice, 0);
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement