Cory Flowers, Quest Guest Blogger | Blog content sourced from Ps_App_Development

I was working on the eSupplier portal and had a request for an enhancement to the Bidder Approval process. Before a bidder can access the eSupplier Portal, they must first be approved by an Approver in the Procurement Department. Once this happens, a user is granted roles to allow the user access to the eSupplier Portal. I thought that adding a SQLExec statement in the correct location would insert the role and everything would be great. Well, maybe not.

As it turns out, a bidder when registering can add multiple users to the bidder profile. And there is the issue. SQLExec only updates one row: the first row returned. With the following code, I was able to update all of the users once the Approver from the Procurement Department clicked the Approval push button.

/* Adding Custom Role for bidder access */

 

Local SQL &SQL;

Local string &OPRID;

Local string &BIDDER_ID;

 

/* &RoleGrntOprid is the variable used during the processing of the program*/

 

&OPRID = &RoleGrntOprid;

 

/* Select Bidder ID of the Users we want to Insert the Role for*/

 

SQLExec(“Select BIDDER_ID FROM PSOPRALIAS WHERE OPRID = :1”, &OPRID, &BIDDER_ID);

 

/* Select Operator Id’s associated with the Bidder ID*/

 

H &SQL = CreateSQL(“Select OPRID FROM PSOPRALIAS WHERE BIDDER_ID = :1”, &BIDDER_ID);

 

/*While I have Operator Id’s associated with the Bidder Id Insert the row*/

 

While &SQL.Fetch(&OPRID)

 

SQLExec(“INSERT INTO PSROLEUSER (ROLEUSER, ROLENAME, DYNAMIC_SW) VALUES(:1, ‘BIDDER_ACCESS’, ‘Y’)”, &OPRID);

 

End-While;

This is how I solved the issue of inserting roles for multiple users associated with a Bidder Id.