I have requirement where i have a input form where it has a feild called maxOffer which is by default set to 10 in the template and each input form if we are saving it under the campaign will count as 1 offer in backend.
So the structure is like:
Campaign (properties: Max offer : 10 ) and if we create 2 offers by entering the form it will count as current offers -= 2
So we have a check condition in Offer input form where it will raise an error if we create more than 10 offers for a campaign since the max offer limit is only 10.
input form :
<check expr="nova_offer_validateActiveOfferCount( @IOPERATIONID ) != '0'"> <error>"The number of active offers exceeds the number provided on the Campaing Input Form. Please deactivate an existing offer before creating a new offer."</error> </check>
CREATE OR REPLACE FUNCTION nova_offer_validateActiveOfferCount(P_IOPERATIONID INTEGER) RETURNS VARCHAR AS $func$ DECLARE L_overActiveOfferLimit INTEGER := 0; L_errorCode VARCHAR(8) := '1'; BEGIN WITH active_offers AS ( SELECT COUNT(RNO.INOVAOFFERSID) cnt FROM RN_NOVA_OFFER RNO WHERE RNO.IOPERATIONID = P_IOPERATIONID AND RNO.IACTIVE = 1 ) SELECT case when no.IRSNMAXACTIVENOVAOFFERS( this will be default 10) < ao.cnt( lets say this as 2) then 1 else 0 end INTO L_overActiveOfferLimit FROM NMSOPERATION no, active_offers ao WHERE IOPERATIONID = P_IOPERATIONID; IF L_overActiveOfferLimit = 1 THEN L_errorCode := '0'; END IF; RETURN L_errorCode; EXCEPTION WHEN others THEN RAISE EXCEPTION 'nova_offer__validateActiveOfferCount ERROR: %', SQLERRM; END; $func$ language plpgsql;
The above is allowing us to create a offer and save but when we try to edit already created form it raise the error : "The number of active offers exceeds the number provided on the Campaing Input Form. Please deactivate an existing offer before creating a new offer." ( which is like 10 > 2 where it should not error out )
Can anyone suggest what is wrong here. I can't able to see what is the output of the postgrefunction inside the alert