Deleting Rows in ExcelToCI
By Chris Malek | Wed, Oct 13, 2010
I recently had a situation where I needed to remove a security role from a large amount of users. While I could have done this with SQL doing a delete something like this.
DELETE FROM PSROLEUSER WHERE ROLENAME = 'ROLE TO REMOVE' AND ROLEUSER = 'THE_USER';
This would not trigger the integration broker messages that publish out to all the subscribing databases. So I was going to use the ExcelTOCI to remove the role. I had actually never done a delete before with ExcelToCI and I realized that the delivered ExceltoCI macros are not coded for a row delete. However, the excelTOCI just submits SOAP messages that end up getting parsed by the SOAPtoCI application package which looks for a “CiNodeAction” attribute and it will process a row delete. You can see the code in the SOAPTOCI:SOAPTOCI application class.
You just have to modify the VBA Macros that actually create the SOAP XML that get submitted to the Weblib.
- While you are in your ExceltoCI template go to Tools | Macros | Visual Basic Editor
- Open the StagingAndSubmission Module
- In the WriteCollectionHeader function do the following
You will see a case statement “Select Case lScrollLevel”
Change the “Case 1” statement
sXmlLine = sIndent & "<" & sXmlTag & ">"
sXmlLine = sIndent & "<" & sXmlTag & " CINodeAction=""DELETE"">"
This worked for the USER_PROFILE component interface because the ROLEUSER table shows up at level1. If you need to delete a level 2 or 3 row then you need to basically make the same change on those Case statements.
This was a quick and dirty solution and you would probably want to make sure you did not give this modified macro to anyone because you could do some damage in the database.
Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.About Chris Work with Chris
PeopleSoft Simple Web Services (SWS)
Do you need a quick way to create web services?
Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.
Integration Broker - The Missing Manual
I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.