I was wondering if one of you knows a script or way to prevent FDM to load ICP details to target HFM accounts that are flagged 'N' to carry ICPs.
The source file from SAP is having accounts carrying ICP details while its target in HFM cannot. What would be a maintenance free way to prevent this?
Overwriting the ICP details for the source accounts is not an option from maintenance perspective as they can be changed, extended etc. I would rather have some lookup script before validation that overwrites the ICP details to [ICP NONE] in case the varvalues(14) is not allowed to carry ICP.
If anyone knows this script, I would highly appreciate it!
It seems that you are trying to prevent some account records to get loaded.
You can use mappings of those accounts for this purpose
You can map these accounts to 'Ignore'
so that at the time of validation they will be ignored and hence after export they will not be loaded to target application.
Try this out
Or else check whether you need a conditional script for this
Thanks for you reply. However, this feature is a bit too simple for the kind of problem I am having. ICP details export need to be excluded depening on the target account which it is heading to.
And the criteria for this exclusion would be the fact if in HFM, this account is allowed to carry ICP or not.
Since the accounts are not yet stable and subject to change, it would be nice to have some script that does the check each time the validation workflow is being performed in FDM.
Unfortunately FDM requires stable metadata and something that you can build an application around.
Since FDM and HFM are two seperate products they do not have any direct metadata sharing.
There is no way for FDM to know about HFM metadata components to do then be able to do certain this with values.
The best you can do is:
1. Create conditional mapping inside of FDM to map to ignore.... then when your accounts change inside of HFM .... go into FDM and update the mapping.
2. Create something in your HFM Rules file.
So it would be a very long conditional map then? There are 80 valid ICP entities in HFM. For each of their mapping in FDM I then would need to (e.g. ICP 090):
090 --> #script
script would be: IF (varvalues(14) = "110100" OR "110400" OR "1205000" etc. etc. Then Result = "IGNORE" Else varvalues(18) = "090"
For each varvalues(14) which cannot carry ICP, correct?
The number of invalid ICP varvalues would be like a couple of hundred, is that an issue? and performance-wise?
PS. Only thing I am still wondering is that if FDM is able to return export to HFM errors for invalid ICP exports, wouldnt this make it also possible to do some pre-check and with a before-export script put these ICP details on ignore?
PS2. Another on this, even though the export gives an error, all other items are being exported except these illegal ones, correct? If this errormessage could be blocked from the end-user view, maybe this would also be an option.
You CAN get this information from FDM out of HFM; however, it would not be out of the box functionality AND you may or may not want to go to the trouble to set it up / maintain it going forward.
From your FDM script, you could directly link with the HFM tables using ADO and you could retrieve the information you need from the proper tables.
The following code should get you a list of accounts that are NOT flagged for ICP by retrieving a recordset containing all Account names where isICPis set. You could then put this in an array and check the FDM account against your array.
Other options would be to alter the SQL so that you call and check the current FDM account against the HFM database directly. Might be a little easier coding; however, you'll have to do a lot more database calls which I wouldn't advise...
i.e. strSQL = "Select IsICP from <insert HFM App Name>_ACCOUNT_ITEM where Label = '" & <FDM Account Name variable here> & "'"
(NOTE : I'm assuming you're using SQL Server, if your using Oracle, you'd need to update the connection string according. If you are unsure of how to do this, I'd recommend : http://connectionstrings.com/ for more information)
' Connection string.
strCon = "Provider=sqloledb;Data Source=<insert HFM DB server here>;Initial Catalog=<insert HFM DB Name here>;User Id=<insert DB login here>;Password=<insert DB user Password here>"
' Create the required ADO objects.
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.recordset")
' Open the connection.
' Retrieve some records.
strSQL = "Select Label from <insert HFM App Name>_ACCOUNT_ITEM where IsICP = 1 "
rs.Open strSQL, conn
' Disconnect the recordset.
Set rs.ActiveConnection = Nothing
' Release the connection.
Set conn = Nothing
' Use the diconnected recordset here.
Do While Not rs.BOF and Not rs.EOF
' Do something with the current Account.
' Next record
' Release the recordset.
Set rs = Nothing
Not sure who you are disagreeing with on this one. In the method that I referred to, I am using an FDM API function to get the account information. I prefer the use of the API function because then I don't have to worry about the HFM schema changing (or updating when I point my FDM app to a new HFM app).
I'm guessing that you weren't disagreeing with me since we're essentially taking the same approach but from different angles.
One more note. By using the API, I don't need to put connection parameters (ie passwords) in the script.
I didn't even see your reply. I was replying to someone else who said you couldn't do it...... (I guess when you hit reply on someone's post, it doesn't indicate in your response who you replied to?? Crazy Oracle forum software. :) )
If there is an API available for this, I would also encourage that for the same reasons you mentioned. I would only hit the other database directly if there was no other choice. In regards to exposing username / password, I'd recommend establishing a read only account for just the access you need for your queries anyway... Other option would be to use a File DSN as you could bury the login / password in that as well..... Moot discussion though because correct answer is to use the API. :)
Cool. Yeah, it showed your response to JDeM but after my response. Weird. Have you ever tried cancelling out of a post and the website says that it is experience errors then you hit backa nd it's all good?