How to make Offer Code mandatory and unique

While working on a requirement on Adobe Campaign Classic, I got a requirement to make the offer code mandatory and unique. Initially it seemed to be an easy requirement as I can just create a unique key on the offer code field.
But later realized that, every offer has another version of the same stored in the same schema nms:offer for the live environment. Adobe automatically creates the internal name as ogininalName_liveRcp while deploying the offer to the live environment (don't know how that is done yet, may be a post for future :)). But for other fields, it is copied as is and so is the offer code too. So unique key is not option.
Then thought, if I add another field in the nms:offer schema for the environment and make the key with offer code + the environment. But same deployment problem, how do I set the env variable as part of deployment?
So decided to go with the toughest but cleanest solution, SOAP call from input form. Here are the steps for the same. You can follow almost any sequence while building as long as all the changes are made before testing.

Step 1: Update Input Form
Update the nms:offer input form to add the soap call inside the <leave> tag before </form> tag. Out of the box, offer has the leave tag, but for other schemas, you may need to add it.
<leave>
 <if expr="!@isModel">
      <soapCall name="CustomValidation" noRefresh="true" service="nms:offer">
        <param exprIn="/" type="DOMElement"/>
        <param type="boolean" xpathOut="/tmp/@customValidationResult"/>
      </soapCall>
    </if>
  </leave>
Explanation: The input parameter to the soap call, actually sends the whole <ctx> tag of the object creation form as an DOMElement to the method declared. The return is a boolean value. I don't need it, but it seems that you have to save it somewhere. So I gave an attribute name inside /tmp node.

Step 2: Declare the function in schema
Extend the nms:offer schema as cus:offer as below. Removing some of the srcSchema attributes to make it smaller
<srcSchema extendedSchema="nms:offer" name="offer" namespace="cus" >
  <element label="Marketing offers" labelSingular="Marketing offer name="offer">
    <attribute label="Offer code" length="64" name="code" type="string" default="'OFR'+LPad(ToString(CounterValue('CusOfferCode')),6,'0')"/>
  </element>

  <methods>
    <!--Custom Validation for Offer-->
    <method library="cus:offer.js" name="CustomValidation" static="true">
      <help>Validates the fields of an offer</help>
      <parameters>
        <param desc="Inbound offer context" inout="in" name="ctx" type="DOMElement"/>
        <param desc="Validation Status" inout="out" name="isValidOffer" type="boolean"/>
      </parameters>
    </method>
  </methods>
</srcSchema>

Explanation: The Blue highlighted part for the code element for Offer Code is bonus to make the offer with a default value as "OFR000001" and so on. To make it working you have to create a sequence with the name "CustomOfferCode". I was using SQL Server, so ran the below SQL
Insert into XtkNewId values ('CusOfferCode',1)

Step 3: Create the JS function
Create a js file cus:offer.js with below code. You need to remove the logs which I added for debugging

loadLibrary("/nl/core/shared/nl.js");

NL.require('/nms/campaign.js');

function nms_offer_CustomValidation(ctx)

{

logInfo("Parameter:" + ctx.toXMLString());

var eOffer = ctx.offer;

var offerId = parseInt(eOffer.@["id"]);

var offerCode =eOffer.@["code"];

if(offerCode.trim() == "")

{

logError("Your offer code can't be blank");

return false;

}

var isModel = NL.XTK.parseBoolean(eOffer.@["isModel"]);

if(isModel)

return true;

var isNewOffer = false;

if(ctx.tmp != undefined && ctx.tmp.@newOffer == "1")

{

isNewOffer = true;

}

logInfo("Offer Code:" + offerCode);

return validateOfferCode(offerId, offerCode, isNewOffer);

}

//Checks if an offer already exists with the same code in design environment

function validateOfferCode(offerId, code, isNewOffer)

{

var query = xtk.queryDef.create(

<queryDef schema="nms:offer" operation="select">

<select>

<node expr="@id" />

<node expr="@label" label="Offer Label"/>

<node expr="[category/@label]" label="Category Label" />

</select>

<where>

<condition boolOperator="AND" expr="[category/env/@live] = 0"/>

<condition boolOperator="AND" expr="@isModel = 0"/>

<condition expr={"@code = '" + code + "'"}/>

</where>

</queryDef>)

var offers = query.ExecuteQuery()

for each(var offer in offers.offer)

{

var id = parseInt(offer.@id);

//var isModel = NL.XTK.parseBoolean(offer.@isModel);

var label = NL.XTK.toString(offer.@label);

if(id == offerId)

{

//Same offer, so ignore

continue;

}

else

{

var category = NL.XTK.toString(offer.category.@label);

var errorMessage = "An offer(Label:" + label + " - in Category:" + category + ") with the same code already exists"

logError(errorMessage);

return false;

}

}

return true;

}

And That's it. Your offer is ready with mandatory and unique offer code.

New offer Creation:

Edit offer error, if the offer code already exists

Thanks for reading through it.. Hope this helps!! Please feel free to provide your valuable comments.