Copy Account Annotations using a calc script 2


We did see how to copy cell comments using a business rule in the previous post. If you didn’t read that post, please do so.

Prerequisites

  • LCM Export XML file
  • LCM encrypted password

The account annotation export XML format is shown below.

Account Annotations LCM export XML

You can run an LCM to get this export file.

LCM

Follow the steps from the previous step to get the LCM encrypted password.

How it works

You can add descriptions (annotations) for accounts if the form allows.

Account Annotations XML is different from Cell Text; it is not stored by plan types.

LCM XML

It is also not stored against other user-defined dimensions. The following dimensions take part in Account Annotations.

  • Scenario
  • Version
  • Entity
  • Account

What we are going to do in the groovy script is to replicate an entire node of information (accountAnnotation) and replace them with values provided by the user.

Here is what the Calc Manager rule looks like.

When called, the above rule executes CopyAccAnnotation.groovy rule, which provides 4 parameters for the rule.

  • Copy From Scenario
  • Copy To Scenario
  • Copy From Version
  • Copy To Version

You can perform a check for Entity if needed.

Groovy to perform the copy

/* Import groovy XML driver */
import groovy.xml.*
now = new Date()
/* parameters */
lcmFolderName= """Account Annotation ${now.format("yyyyMMdd-HHmmss.SSS")}"""
lcmRootFolder = "E:\\Account Annotation Copy"
adminUser="admin"
adminUserEncPass="{LCM}akbvG/gihyDq8JhxFw9jQ42Up2ljcq58VPm0owapuh/H8bt1+RNlV8INFFKvkA6g"
/* Extract existing cell texts */
Process expProc=Runtime.getRuntime().exec("cmd /c E:\\Oracle\\Middleware\\user_projects\\epmsystem1\\bin\\Utility.bat \"$lcmRootFolder\\Export.xml\" -b \"$lcmRootFolder\\$lcmFolderName\"")
expProc.waitFor()
/* add new node for the copy */
accAnnotXML = new XmlParser().parse(new File("$lcmRootFolder\\$lcmFolderName\\HP-Vision\\resource\\Relational Data\\Account Annotations.xml").toURI().toString())
/* find all XML nodes with scenario and version*/
accNode = accAnnotXML.accountAnnotation.findAll { it.scenarioName.text() == fromScenario && it.versionName.text() == fromVersion }
accNode.each{
    def clonedaccNode = new XmlParser().parseText( XmlUtil.serialize( it ) )  
     clonedaccNode.'scenarioName'[0].value = toScenario
     clonedaccNode.'versionName'[0].value = toVersion
     accAnnotXML.children().add( 0, clonedaccNode )
}
/* write the updated XML file */
accAnnotUpdatedXML= new File("$lcmRootFolder\\$lcmFolderName\\HP-Vision\\resource\\Relational Data\\Account Annotations.xml")
accAnnotUpdatedXML.withWriter('UTF-8') { writer ->
    writer.write( XmlUtil.serialize (accAnnotXML))
}
/* add user name and encrypted password to exported folder's import.xml */
importXML = new XmlParser().parse(new File("$lcmRootFolder\\$lcmFolderName\\Import.xml").toURI().toString())
/* find the XML node with empty password */
userNode = importXML.User.find{
 it.@password.equals('')
}
/*add the username and password */
userNode.replaceNode { node ->
    User(name: adminUser , password: adminUserEncPass)
}
/* write the updated XML file */
importUpdatedXML= new File("$lcmRootFolder\\$lcmFolderName\\Import.xml")
importUpdatedXML.withWriter('UTF-8') { writer ->
    writer.write( XmlUtil.serialize (importXML))
}
/* import XML process */
Process impProc=Runtime.getRuntime().exec("cmd /c E:\\Oracle\\Middleware\\user_projects\\epmsystem1\\bin\\Utility.bat \"$lcmRootFolder\\$lcmFolderName\\Import.xml\"")
impProc.waitFor()

Line 7 to 10 is where we store the parameters for Groovy; line 7 is the name of the folder, which gets created after a successful LCM execution.

When the groovy rule gets executed, it performs an LCM of the existing Account Annotations into a folder with a timestamp – Line 13.

Once the XML file extraction gets completed, it then looks for all the nodes that match the Scenario and Version supplied by the user – Line 21.

It is now time to replace and add new nodes; for each node (the one with “Plan” and “Working” version), a new node gets created. scenarioName and versionName get replaced with the user-supplied ones (toScenario, and toVersion). Once replaced, the node then gets added as the first child under accountAnnotations node. All these are happening in lines 23 to 28.

That’s it, and it is now time to write the newly created XML and import it into Planning.

Existing Account Annotations.xml gets replaced with this newly created XML file – Line 31 -34.

Import the newly minted XML file with the LCM encrypted password.

Lines 37-53 updates the Import.xml file with the user name and encrypted password. All done, the next obvious step is the import – line 56.


Leave a comment

Your email address will not be published.

2 thoughts on “Copy Account Annotations using a calc script

  • Peter John Nitschke

    Hey,

    Nice work as always! Do you think you’ll hit any security issues running that? I’ve done the same thing using the MAXL CDF (out to shell to run the LCM utility) but this is much more elegant.

    Also – did you check it without the password? Sometimes I found it wasn’t required…

    • ckattookaran Post author

      Thanks, Pete.
      I’m using a Shared Services Admin account, so that is taken care of.
      The password is not needed from the second time onwards. However, here since we are downloading a new LCM every time we need to add the encrypted password.