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.