Forum Stats

  • 3,780,523 Users
  • 2,254,406 Discussions
  • 7,879,373 Comments

Discussions

HFM_LoadDimTables

786797
786797 Member Posts: 83
edited Jun 6, 2014 11:17PM in Financial Consolidation

Hi,

i would like to retrieve the IsICP attribute from the accounts of HFM in FDM.

The HFM_LoadDimTables does the job for the account and the description, but i have not been able to modify it or add a parameter to get the isICP. A column already exist in the FDM table but the code is very advanced and beyond my coding skills...

below the part that i think need to be modified. Any help appreciated

'Declare local variables

Dim objBP

Dim lngCount

Dim i

Dim varList

Dim rsAppend

Dim strMessage

'Intiialize the block processor (Pass Active API & Script object References)

Set objBP = CreateObject("upsWBlockProcessorDM.clsBlockProcessor")

objBP.Initialize API, SCRIPTENG

'Call the Connect Action for the active integration block

Set API.IntBlockMgr.IntegrationMgr.PobjIntegrate = objBP.ActConnect("GetDimensionMembers")

'Check the connection and start checking intersections

If API.IntBlockMgr.IntegrationMgr.PobjIntegrate.intResult Then

  'Delete existing values

  If API.DataWindow.DataManipulation.fExecuteDML("DELETE FROM tDimAccount", True ) Then

  'Open the Target account table for append

  Set rsAppend = API.DataWindow.DataAccess.farsTableAppend("tDimAccount")

  'Execute method and assign Return value

  varList = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fListAccounts(False)

  For lngCount = LBound(varList,2) To UBound(varList,2)

  rsAppend.addnew

  rsAppend.Fields("TargAcctKey").Value = varList(0,lngCount)

  If InStr(1,varList(1,lngCount),"[") > 1 Then

  rsAppend.Fields("TargAcctDesc").Value = Trim(Left(Left(varList(1,lngCount),InStr(1,varList(1,lngCount),"[")-1),50))

  If InStr(InStr(1,varList(1,lngCount),"["),varList(1,lngCount),",") > 0 Then

  rsAppend.Fields("TargAcctType").Value = Mid(varList(1,lngCount),InStr(1,varList(1,lngCount),"[")+1,InStr(InStr(1,varList(1,lngCount),"["),varList(1,lngCount),",")-InStr(1,varList(1,lngCount),"[")-1)

  Else

  rsAppend.Fields("TargAcctType").Value = Mid(varList(1,lngCount),InStr(1,varList(1,lngCount),"[")+1,InStr(InStr(1,varList(1,lngCount),"["),varList(1,lngCount),"]")-InStr(1,varList(1,lngCount),"[")-1)

  End If

  Else

  rsAppend.Fields("TargAcctDesc").Value = Trim(Left(varList(1,lngCount),50))

  rsAppend.Fields("TargAcctType").Value = "No Type"

  End If

  rsAppend.Fields("TargAcctIndex").Value = lngCount '

  rsAppend.Fields("TargAcctGroup").Value = "Account"

  rsAppend.Update

  Next

  rsAppend.Close

  Set rsAppend = Nothing

  End If

Tagged:

Answers

  • KKT
    KKT Member Posts: 1,128

    Try this :-

    Below solution from this forum

    https://community.oracle.com/thread/2207553?tstart=0

    As i have used the same.

    --------------------------------------------------------------------------------------------

    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.

         conn.Open strCon

        

         ' 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.

         conn.Close

         Set conn = Nothing

        

         ' Use the diconnected recordset here.

    rs.MoveFirst

    Do While Not rs.BOF and Not rs.EOF

    ' Do something with the current Account.

    ' Next record

    rs.movenext

    Loop

         ' Release the recordset.

         rs.Close

         Set rs = Nothing

    Let me know if that works or else we will suggest some different solution as well.

    Thanks,

    ~KKT~

  • 786797
    786797 Member Posts: 83

    Hi KKT,

    i was trying to avoid doing that as i already have 2 connections in my import script.

    now in the discussion you pointed me to, there is an interesting info.

    TonyScalese mentions the following parameter: fisaccounticp

    i cannot find the adaptor type library anywhere so i am unsure how to use this. if anyone has used that before, it would be great if you could share the condition for example..

    if .... fisaccounticp then

    do something

    end if

    thanks

This discussion has been closed.