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>
PostgreSQL :
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
