8 Replies Latest reply: Apr 2, 2012 11:06 PM by KostasN. RSS

    Default Consolidation / Default Translation

    andriy golovchenko
      Hello,

      I would appreciate if someone could post the script/text of the default Sub Consolidate()/Translate() procedure which system runs during default Consolidation and Translation.

      Thanks
        • 1. Re: Default Consolidation / Default Translation
          Sofia
          Sub Consolidate() 'Regla de consolidación si, en los setting esta marcado que usa la regla utiliza la siguiente regla para consolidar

          Dim MyDataUnit
          Dim lNumItems
          Dim dPCon

          Set MyDataUnit = HS.OpenDataUnit("")

          dPCon = HS.Node.PCon("")


          lNumItems = MyDataUnit.GetNumItems


          for i = 0 to lNumItems-1


          ' Get the next item from the dataunit
          call MyDataUnit.GetItem(i, strAccount, strICP, strCustom1, strCustom2, strCustom3, strCustom4, dData)

          ' See if this is a consolidatable account
          If HS.Account.IsConsolidated(strAccount) Then


          ' Proportionalize this account
          call HS.Con("",dPCon,"Proporción")


          ' see if we should eliminate this account
          call Eliminate(strAccount, strICP)


          End If


          next

          End sub 'Fin regla Consolidación

          '***************************************************
          ' Tema: Comienza el proceso de Eliminacion segun metodo de consolidacion
          '***************************************************


          Sub Eliminate (strAccount, strICP) 'intercompany elimination rules

          Dim CanEliminate
          Dim strPlug
          Dim dPCon



          CanEliminate = TRUE
          NegatePlug = FALSE


          If (StrComp(strICP, "[ICP None]", vbTextCompare) = 0) Then
          CanEliminate = FALSE
          ElseIf (HS.Account.IsICP(strAccount) = FALSE) Then
          CanEliminate = FALSE
          ELSEIF (HS.Node.ISDESCENDANT(HS.PARENT.Member,strICP,"") = FALSE) THEN
          CanEliminate = FALSE
          'ElseIf (HS.PARENT.ISDESCENDANT(strICP,"") = FALSE) Then
          'CanEliminate = FALSE
          Else
          strPlug = HS.Account.PlugAcct(strAccount)


          'HS.Parent.IsDescendant("I#[ICP Top]", "")
          If (strPlug = "") Then CanEliminate = FALSE
          End If


          If CanEliminate Then


          dPCon = HS.Node.PCon("")


          call HS.Con("V#[Elimination]",-1*dPCon,"Eliminacion interco")


          call HS.Con("V#[Elimination].A#" & strPlug,dPCon,"Eliminacion interco")


          End If







          End Sub 'Fin regla Eliminación



          Sub Translate ()

          'Esta rutina se ejecuta cuando se realiza la conversion de monedas desde la aplicacion de HFM. Es utilizada
          'cuando se ejecutan calculos de conversion no estandars.

                    POVEntity= hs.entity.member
               tmpAverageRate = HS.GetRate("A#TC_PROMEDIO")
                    tmpClosingRate = HS.GetRate("A#TC_CIERRE")
                    tmpClosingRateLY = HS.GetRate("A#TC_CIERRE.Y#PRIOR.P#last")
                    AnoCognos = "2050"
               tmpTCEntity = Hs.GetRate("A#"& left ("TC_" & POVEntity,20))


          call WriteToFile(tmpTCEntity)

          '---------------------------------------------------

                    'Todas las variaciones del patrimonio se convierten a tipo de cambio de cierre del mes de la transaccion

               '---------------------------------------------------
               AccList= Hs.Account.List("","CuentasPatrimonio")
               
               For each Acc in AccList

                    HS.TransPeriodic "A#"& Acc&".C4#Variacion" ,"A#"& Acc&".C4#Variacion" ,tmpClosingRate, ""
               Next


          '---------------------------------------------------

                    'Todas las variaciones del patrimonio se convierten a tipo de cambio de cierre del mes de la transaccion

               '---------------------------------------------------
               AccList= Hs.Account.List("","CuentasResultado")
               
               For each Acc in AccList

                    HS.TransPeriodic "A#"& Acc ,"A#"& Acc ,tmpAverageRate, ""
               Next


               

          if hs.year.member > AnoCognos then


               AccList= Hs.Account.List("","CuentasBalance")
               For each Acc in AccList
                    
                    HS.Trans "A#"& Acc&".C4#CTA" ,"A#"& Acc&".C4#Apertura" ,tmpClosingRate, tmpClosingRateLY
               Next
               



          end if
          '---------------------------------------------------

               
               
               if hs.year.member =< AnoCognos then

                    AccList= Hs.Account.List("","CuentasPatrimonio")
                         For each Acc in AccList
                              
                              HS.Trans "A#"& Acc&".C4#Cierre","A#"& Acc&".C4#Cierre",tmpTCEntity, ""
                         Next
               

               end if
               





          End Sub ' Fin Reglas de Conversión de Moneda



          hope it helps out. If not http://www.oracle.com/technetwork/middleware/financial-management/overview/index.html. , in this addresss you could found a complete application to see hoy an aplication is done

          good luck
          • 2. Re: Default Consolidation / Default Translation
            andriy golovchenko
            Muchas gracias!
            • 3. Re: Default Consolidation / Default Translation
              Sofia
              No es nada

              saludos
              • 4. Re: Default Consolidation / Default Translation
                KostasN.
                Hi All,

                Just a clarification. Default consolidation refers to the process that runs when you set ConsolidationRules=N in the application settings. There is no such thing however regarding translations. The default translation runs always, whether you implement translation rules or not. The Translate() routine runs after default translation, so that you can override translations which have already happened.

                --Kostas                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                • 5. Re: Default Consolidation / Default Translation
                  andriy golovchenko
                  Hi Kostas, thanks. Yes that is known. I think I now know how the default/behind-the-scene text of the script should look like for Consolidation and Translation which i'll post a bit later (if one has to use customized Consolidation/Translation - this will not differ from the default behavior).

                  The reason I asked the initial question was related to having all data translated to the application default currency irrespectively of whether the default translation occurred or not. But this seems to be a little challenging task (as you cannot control the calls of Sub Translate() routine whenever you want - unless you are forcing a parent to be of different currency to implicitly kick off the default translation).

                  I also liked the posting by Chris Barbieri which explains much in this respect - HFM Currency Translation Throughout Hierarchy

                  Edited by: Andriy on Apr 2, 2012 9:53 AM
                  • 6. Re: Default Consolidation / Default Translation
                    KostasN.
                    Think of it this way. HFM is economical on which calculations will do and which it won't. Calculations that have already been done (when executing "Consolidate") or that need not be done (like translations when both entity and its parent have the same default currency) or are out of the consolidation path will not be executed during consolidation.

                    On the other hand, HFM will never "volunteer" to perform any kind of calculation. It always needs a user event to activate them, like initiating calculate, translate or consolidate. And it needs also the user to specify the scope of the requested calculation (select the scenario, entity, year, period, value combination). In the case you describe you are out of the scope of the consolidation path. Therefore, for your purpose you have to specify the scope, like providing with a grid with all entities and the application currency selected for the Value dimension, as Chris describes in the thread you mention, on which you will select all the entities. Then you have to also provide with the event to initiate the process (execute the translate command). If you think a little about it you wouldn't want HFM to execute processes which are not needed, not to fill-up the database with calculated results that are not needed either. Therefore, you (the user) have to be very specific of what you want HFM to do.

                    --Kostas                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                    • 7. Re: Default Consolidation / Default Translation
                      andriy golovchenko
                      I do think of it in the way of efficiency too and for purely "consolidation" purpose this will work, however, in terms of default application currency, I also think there should be an out-of-the-box switch allowing an HFM admin user to choose whether to use the current default translation or slightly enhance it with translation of all (or some) entities having non-default currencies to the default "one". The reason for this obvious, if a multinational organization with finance departments in different countries uses HFM - then HFM must provide this option by default (in order for Head Office and Geographic Regions to be able to use the same application for consolidation and reporting). In other case, there are unnecessary overheads related to introducing/supporting alternative hierarchies or customizing a default routine (using non-generic translation approach) in order to make it work in a "default" way for multinational companies.

                      Technically, yes it is effective to use web-grid for ad-hoc translations, however, browsing every month (in some case more often) through various hierarchies trying to figure out which ones might be necessary to translate to the main reporting currency is a manual non-value added step, which I believe the system should provide an option to do automatically. And depending on hierarchy, this might involve hundreds of clicks every month.

                      Hopefully, this will be heard by the Oracle's EPMA Customer Advisory Board(CAB) :)
                      • 8. Re: Default Consolidation / Default Translation
                        KostasN.
                        Just a small note: using a web-grid for ad hoc translations is not so horrible an overhead like you describe, since HFM provides you with all the tools to streamline the whole user process, including the task lists by means of which your user need not be "browsing", "trying to figure out" what to do, but rather be directed by HFM on his actions depending on the reporting period.