9 Replies Latest reply on May 6, 2014 1:47 PM by Francisco Amores

    Urgent!, MAP dimensions from FDM to HFM (Target)

    944384

      Hi Experts,

      I am on FDM Version 11.1.2.1, i have a quick question on FDM ADAPTOR user defined  dimension members, on mapping  to HFM target.

       

      there are couple of USER DEFINED members i am unsing, but those with Active are the members i am loading to HFM.

      if the SOURCE-TARGET mapping is someting like below

      FDM UD1 --> goes to HFM UD1

      FDM UD2 --> goes to HFM UD2

      FDM UD3 --> goes to HFM UD3

      FDM UD4 --> goes to HFM UD4

       

      Or it can be modified ti Custom SOurce Target MAP.

      FDM UD10 --> goes to HFM UD2

      FDM UD11 --> goes to HFM UD3

       

      when i double click User Dimension, i found a general oracle script point to

       

      Function UD10List()     'If  i modify this to UD10 and update as UD2, will it map the FDM UD10 to HFM UD2

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

      'Oracle® Hyperion Financial Data Quality Management Adapter for HFM

      '

      'Created By:     Oracle Corporation

      '

      'Purpose:   Get Custom10 Dimension Members 

      '             

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

       

       

      'Declare Local Variables

      Dim objR

       

       

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

      'Intialize function return COM object

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

      Set objR = CreateObject("UpsOBJDMw.objScriptReturn")

      Set UD10List = objR     'If  i modify this to UD10 and update as UD2, will it map the FDM UD10 to HFM UD2

      objR.blnSortList = RES.FDMTrue

       

       

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

      'Check the status of the connection to FM

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

      If API.IntBlockMgr.IntegrationMgr.PobjIntegrate.intResult Then

        'Execute method and assign Return value

        objR.varList = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fListCustom10(API.IntBlockMgr.IntegrationMgr.PblnListAllMembers) 'If  i modify this to UD10 and update as UD2, will it map the FDM UD10 to HFM UD2

        objR.lngListCount = UBound(objR.varList,2)

        objR.lngListDimCount = 2

        objR.lngListDimActive = 2

        objR.lngErrCode = 0

       

       

        'Destroy Objects

        If Not API.State.GblnBatchLoadActive Then

        API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fDisconnect

        Set API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon = Nothing

        End If

      Else

        objR.strErrDesc = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fLocalizeError(10055)

        objR.lngErrCode = 10055

        If Not API.State.GblnBatchLoadActive Then Set API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon = Nothing

      End If

      End Function

        • 1. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
          Francisco Amores

          You can configure the foreign name of custom dimensions to point to any target dimension.

          1 person found this helpful
          • 2. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
            944384

            Hi Francisco,

            Thanks for your reply, when you say foreign name, you mean the HFM custom dimension. can you be little in detail please.

             

            I am really sorry to trouble you again, can you please explain with below example.

             

            already i have a n FDM User Defined Dimesnsion (UD2)

            UD2 is already mapped to Custom 2 in HFM

             

            i made FDM UD2 as "Not Used".

             

            can you point me exactly, how can i direct UD12 (FDM Dimesion) to Custom 2 (HFM Dimension)

            • 3. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
              Francisco Amores

              Firstly, why would you like disabling Custom 2?

               

              The problem here is that even if you link UD12 to Custom2, connection to HFM will fail as it will check if UD2 in HFM is active.

               

              If you want to link UD12 to Custom2 you have to go to Workbench Client, right click on UD12, and set Foreign Name to "Custom2"

              1 person found this helpful
              • 4. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
                944384

                Yes, i am still using this Old Custom_2 to load into database for some calculations. but i dont want this to be loaded into HFM.

                In that case, when i double click on the workbench-->Dimension-->UD2 (Custom_2)-->R-Click, one window opened. i had written in that window as "Not Used".

                and i see the Foreignkey is Custom2 only, so if i change it to Custom 12. by any chance the custom order in the IMPORT screen will get change. or just only the Foreign Key.

                 

                and as per your reply, i Interchange the Foreignkey name for UD2 as Custom_12 & for UD12 as Custom_2. then will it work correctly?

                 

                Thanks for your answer.

                • 5. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
                  Francisco Amores

                  So what you want is to export Custom12 to HFM Custom2 instead of standard Custom2.

                   

                  - Why you need this?

                  - Wouldn't be better use Custom12 for your calculations and export Custom2

                   

                  If you want not to export Custom2 and export Custom12 you can adjust EXPORT Action Script from Workbench Client (Adapter > Actions)

                   

                  Regards

                  • 6. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
                    944384

                    Hi Francisco,

                    so whereever i find CUSTOM2/UD2 in the script. i have to update as CUSTOM12/UD12. Please correct me if i am wrong.

                    Inthis case, dont i need to change the FOREIGN KEY to CUSTOM2 for the UD12 dimension member.

                    Below is the script, hope this is the standard script provided by Oracle. PLease confirm me.

                    i am really sorry to bother you again & again. please confirm me in detail. as i am hitting very near to the project end date.

                     

                     

                     

                    **************************************************************************************************************************

                    Function Export(strFile, strLoc, strCat, strPer, strTCat, strTPer)

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

                    'Oracle® Hyperion Financial Data Quality Management Adapter for HFM

                    '

                    'Created By:     Oracle Corporation

                    'Date Created:   04/15/2008

                    '

                    'Purpose:   Generate a text file formated to Load

                    ' Hyperion Financial Management.    

                    '              

                    'Modified By: Oracle Corporation

                    'Date Modified: 02/04/2008

                    'Purpose: Includes paging for data sets larger than 100K records.

                    '

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

                     

                     

                    'Declare Local Variables

                    Dim objFileSys

                    Dim objFile

                    Dim objR

                    Dim rsExport

                    Dim rsCellText

                    Dim strDelim

                    Dim strLine

                    Dim strTargYear

                    Dim strPerDesc

                    Dim strCleanMemo

                    Dim blnLoadAsJournals

                    Dim dblValue

                     

                     

                    'Declare Logging Variables

                    Dim strIODesc

                    Dim strEventDesc

                    Dim strErrDesc

                     

                     

                    'Declare Logging

                    Dim TStart

                    TStart = Now()

                     

                     

                    'Intialize function return object

                    Set objR = CreateObject("UpsOBJDMw.objScriptReturn")

                    Set Export = ObjR

                     

                     

                    '*******************************************************************

                    'Initialize variables

                    '*******************************************************************

                    strDelim = ";"

                    strTargYear = API.POVMgr.fPeriodKey(CStr(strPer(0))).strTargetYear

                    API.IntBlockMgr.IntegrationMgr.PvarIntegrate2 = ""

                     

                     

                    '*******************************************************************

                    'Check for Period Range Processing

                    '*******************************************************************

                    Dim blnUseRange

                    Dim lngPerCount

                    Dim strStartPerKey

                    Dim strEndPerKey

                     

                     

                     

                     

                    If LCase(strPer(0)) <> LCase(strPer(1)) Then

                      blnUseRange = RES.FDMTrue

                    Else

                      blnUseRange = RES.FDMFalse

                    End If

                    strPerDesc = strPer(0) & " - " & strPer(1)

                     

                     

                    '*******************************************************************

                    'Get the DataValue and test to see if a Data must be loaded as a JV

                    '*******************************************************************

                    Dim strDataValue

                    Dim blnDataAsJournal

                    Dim lngSegKey

                    Dim lngPartKey

                    Dim lngCatKey

                     

                     

                    'Get the Category and Period Keys

                    lngCatKey = API.POVMgr.fCategoryKey(CStr(strCat))

                    strStartPerKey = API.POVMgr.fPeriodKey(CStr(strPer(0))).strDateKey

                    strEndPerKey = API.POVMgr.fPeriodKey(CStr(strPer(1))).strDateKey

                     

                     

                    'Get the the location DataValue and SegmentKey

                    With API.POVMgr.fCheckLocProf(CStr(strLoc))

                      lngSegKey = .lngSegKey

                      lngPartKey = .lngLocKey

                      strDataValue = .strDataValue

                    End With

                    'Check Journal Loading Options

                    If API.DataWindow.DBTools.fGlobalOptionGet(API.IntBlockMgr.IntegrationMgr.PstrSysKey & "JnlEnabled").Status Then

                      If InStr(1,strDataValue,";") > 0 Then

                      blnLoadAsJournals = RES.FDMTrue

                      Else

                      blnLoadAsJournals = RES.FDMFalse

                      End If

                    Else

                      blnLoadAsJournals = RES.FDMFalse

                    End If

                    'Evaluate the DataValue

                    If InStr(1,strDataValue,";") > 0 Then

                      strDataValue = API.DataWindow.Utilities.fParseString(CStr(strDataValue), 2, 1, ";")

                    End If

                    'Test the DataValue to see if the type triggers a journal load

                    Select Case LCase(strDataValue)

                      Case "<entity curr adjs>", "<parent curr adjs>", "[contribution adjs]", "[parent adjs]"

                      If blnLoadAsJournals = RES.FDMFalse Then

                      'DataValue Requires Journal Loading, Set to false and load as entity currency

                      blnDataAsJournal = RES.FDMFalse

                      strDataValue = "<Entity Currency>"

                      Else

                      blnDataAsJournal = RES.FDMTrue

                      End If

                      Case Else

                      blnDataAsJournal = RES.FDMFalse

                    End Select

                     

                     

                    'Check the Load as journal switch

                    If blnDataAsJournal = RES.FDMTrue Then

                     

                     

                      'Check the journal numbering switch

                      Dim blnJournalIDPerEntity

                      blnJournalIDPerEntity = API.DataWindow.DBTools.fGlobalOptionGet(API.IntBlockMgr.IntegrationMgr.PstrSysKey & "JnlIDPerEntity").Status

                     

                     

                      'Set the jounral ID based on the numbering type (Single ID or One Per Entity)

                      If blnJournalIDPerEntity = RES.FDMFalse Then

                      'Execute the single journal entry number update

                      fSetJournalNoAll strLoc, lngSegKey, lngPartKey, lngCatKey, strStartPerKey, strEndPerKey

                      Else

                      'Execute Journal Entry Number Per Entity

                      fSetJournalNoPerEntity strLoc, strCat, strPer, lngSegKey, lngPartKey, lngCatKey, strStartPerKey, strEndPerKey

                      End If

                     

                      'Exporting as a journal, just return true

                      objR.blnIsErr = RES.FDMFalse

                      objR.intResult = RES.FDMTrue

                      objR.strErrDesc = ""

                      objR.lngErrCode = 0

                      'Set the import action skip flag

                      API.IntBlockMgr.IntegrationMgr.PvarIntegrate2 = "skip"

                     

                     

                      'Create an empty file stating "Data Loaded as Journal"

                      Set objFileSys = CreateObject("Scripting.FileSystemObject")

                      Set objFile = objFileSys.CreateTextFile(strFile, RES.FDMTrue)

                      objFile.WriteLine(API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fLocalizeString("A_" & API.IntBlockMgr.IntegrationMgr.PstrSysKey & "_EX_MS_5"))

                      objFile.Close

                      Set objFile = Nothing

                      Set objFileSys = Nothing

                     

                      Exit Function

                    Else

                      'Make sure the import action skip flag is cleared

                      API.IntBlockMgr.IntegrationMgr.PvarIntegrate2 = ""

                    End If

                     

                     

                    '*******************************************************************

                    'Line Item Detail Processing

                    '*******************************************************************

                    Dim objIntersect

                    Dim blnLoadLineItems

                    Dim intLoadType

                    Dim blnAcctUsesLineItems

                    blnLoadLineItems = API.DataWindow.DBTools.fGlobalOptionGet(API.IntBlockMgr.IntegrationMgr.PstrSysKey & "EnableLDLoad").Status

                    intLoadType = Left(API.DataWindow.DBTools.fGlobalOptionGet(API.IntBlockMgr.IntegrationMgr.PstrSysKey & "LDLoadType").Value,1)

                    If blnLoadLineItems Then Set objIntersect = CreateObject("UpsOBJDMw.objIntersection")

                     

                     

                    '*******************************************************************

                    'DATA PROTECTION

                    '*******************************************************************

                    Dim blnProtection

                    Dim strProtection1

                    Dim strCriteria1

                    Dim NewRec

                    Dim strCurEntity

                    If Not blnUseRange Then

                      Set RES.PcolRecs = API.DataWindow.DBTools.fCreateRecCol()

                      blnProtection = API.DataWindow.DBTools.fGlobalOptionGet(API.IntBlockMgr.IntegrationMgr.PstrSysKey & "DataProtection").Status

                      strProtection1 = API.DataWindow.DBTools.fGlobalOptionGet(API.IntBlockMgr.IntegrationMgr.PstrSysKey & "ProtectionValue1").Value

                      strCriteria1 = API.DataWindow.DBTools.fGlobalOptionGet(API.IntBlockMgr.IntegrationMgr.PstrSysKey & "ProtectionCrit1").Value

                    Else

                      blnProtection = RES.FDMFalse

                    End If

                     

                     

                    '*******************************************************************

                    'Check FDM Dimension Attributes to get default properties

                    '*******************************************************************

                    'Declare Dimension status & Empty value variables

                    Dim strNoCategory

                    Dim strNoPeriod

                    Dim strNoAccount

                    Dim strNoEntity

                    Dim strNoICCoParty

                    Dim strNoUD1

                    Dim strNoUD2

                    Dim strNoUD3

                    Dim strNoUD4

                    Dim strNoView

                    Dim strDataView

                    Dim strNoDataValue

                    Dim strNoYear

                     

                     

                    With API.SQLMgr.fIntDimensionList(API.IntBlockMgr.IntegrationMgr.PstrSysKey, RES.FDMFalse, 99, "", RES.FDMTrue)

                        If Not .BOF And Not .EOF Then

                      Do While Not .EOF

                     

                     

                      'Set the dimension active state

                      Select Case LCase(.Fields("DimName").Value)

                      Case "category"

                      strNoCategory = .Fields("DimNoDataValue").Value

                      Case "period"

                      strNoPeriod = .Fields("DimNoDataValue").Value

                      Case "account"

                      strNoAccount = .Fields("DimNoDataValue").Value

                      Case "entity"

                      strNoEntity = .Fields("DimNoDataValue").Value

                      Case "icp"

                      strNoICCoParty = .Fields("DimNoDataValue").Value

                      Case "ud1"

                      strNoUD1 = .Fields("DimNoDataValue").Value

                      Case "ud2"

                      strNoUD2 = .Fields("DimNoDataValue").Value

                      Case "ud3"

                      strNoUD3 = .Fields("DimNoDataValue").Value

                      Case "ud4"

                      strNoUD4 = .Fields("DimNoDataValue").Value

                      Case "datavalue"

                      strNoDataValue = .Fields("DimNoDataValue").Value

                      Case "view"

                      strNoView = .Fields("DimNoDataValue").Value

                      End Select

                      .Movenext

                      Loop

                      'Set static defaults

                      strNoYear = "Missing"

                      'strNoView = "<Scenario View>"

                     

                     

                        Else

                            'Error in Integration Block, Dimension was not found

                      objR.strErrDesc = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fLocalizeError(10064)

                      objR.lngErrCode = 10064

                      objR.blnIsErr = RES.FDMTrue

                         Exit Function

                        End If

                        .Close

                    End With

                     

                     

                    '*******************************************************************

                    'Open the file system object

                    '*******************************************************************

                    Set objFileSys = CreateObject("Scripting.FileSystemObject")

                    Set objFile = objFileSys.CreateTextFile(strFile, RES.FDMTrue, API.DataWindow.Connection.PblnEncodingUnicode)

                     

                     

                    'Set the data FM tag

                    objFile.WriteLine("!Data")

                     

                     

                    'Declare paging variables

                    Dim lngTotalRC

                    Dim PageSize

                    Dim pc

                     

                     

                    'process 10K records at a time

                    PageSize = 100000

                     

                     

                    '*******************************************************************

                    'Get and process the recordset

                    '*******************************************************************

                    If blnUserange Then

                      'Get Crosstab for range output

                      Set rsExport = API.SQLMgr.fTrialBalanceConvertedCrossTab(CStr(strLoc), CStr(strCat), CStr(strPer(0)), CStr(strPer(1)), RES.FDMTrue, , CLng(PageSize), 1, lngTotalRC)

                     

                     

                      objFile.WriteLine("!Period=" & strTPer(0) & "..." & strTPer(1) & "")

                      objFile.WriteLine("!Column_Order = Scenario,Year,View,Entity,Value,Account,ICP,Custom1,Custom2,Custom3,Custom4")

                     

                     

                      'Find the Field number of the first amount

                      Dim blnOnUDField

                      Dim lngAmountFieldStart

                      blnOnUDField = RES.FDMFalse

                     

                      For lngPerCount = 0 To rsExport.Fields.Count - 1

                      'First amount already has delimiter

                      If LCase(Left(rsExport.Fields(lngPerCount).Name,2)) = "ud" Then

                      blnOnUDField = RES.FDMTrue

                      Else

                      'We are on the first field after the user defined values (First Amount)

                      If blnOnUDField = RES.FDMTrue Then

                      lngAmountFieldStart = lngPerCount

                      Exit For

                      End If

                      End If

                      Next

                    Else

                      'Check for Journal Loading

                      If blnLoadAsJournals = RES.FDMTrue Then

                      'Exclude FDM Journals from DAT file

                      Set rsExport = API.SQLMgr.fTrialBalanceConvertedFiltered(CStr(strLoc), CStr(strCat), CStr(strPer(0)), CStr(strPer(0)), 2, RES.FDMTrue, RES.FDMFalse, CLng(PageSize), 1, lngTotalRC)

                      Else

                      'Include both FDM Journals and standard data values

                      Set rsExport = API.SQLMgr.fTrialBalanceConverted(CStr(strLoc), CStr(strCat), CStr(strPer(0)), RES.FDMTrue, , CLng(PageSize), 1, lngTotalRC)

                      End If

                    End If

                     

                     

                    pc = lngTotalRC / PageSize

                    If (lngTotalRC Mod PageSize > 0) Then

                      pc = pc + 1

                    End If

                     

                     

                    If (pc = 0) Then

                      pc = 1

                    End If

                     

                     

                    For i = 1 To pc

                     

                     

                      If i <> 1 Then 'need to get the next/new page of data

                      If blnUserange Then

                      'Get Crosstab for range output

                      Set rsExport = API.SQLMgr.fTrialBalanceConvertedCrossTab(CStr(strLoc), CStr(strCat), CStr(strPer(0)), CStr(strPer(1)), RES.FDMTrue, , CLng(PageSize), i, lngTotalRC)

                     

                      'Find the Field number of the first amount

                      blnOnUDField = RES.FDMFalse

                     

                      For lngPerCount = 0 To rsExport.Fields.Count - 1

                      'First amount already has delimiter

                      If LCase(Left(rsExport.Fields(lngPerCount).Name,2)) = "ud" Then

                      blnOnUDField = RES.FDMTrue

                      Else

                      'We are on the first field after the user defined values (First Amount)

                      If blnOnUDField = RES.FDMTrue Then

                      lngAmountFieldStart = lngPerCount

                      Exit For

                      End If

                      End If

                      Next

                      Else

                      If blnLoadAsJournals = RES.FDMTrue Then

                      'Exclude FDM Journals from DAT file

                      Set rsExport = API.SQLMgr.fTrialBalanceConvertedFiltered(CStr(strLoc), CStr(strCat), CStr(strPer(0)), CStr(strPer(0)), 2, RES.FDMTrue, RES.FDMFalse, CLng(PageSize), CLng(i), lngTotalRC)

                      Else

                      Set rsExport = API.SQLMgr.fTrialBalanceConverted(CStr(strLoc), CStr(strCat), CStr(strPer(0)), RES.FDMTrue, , CLng(PageSize), i, lngTotalRC)

                      End If

                      End If

                      End If

                     

                      'Make sure the recordset returned records

                      If Not rsExport.BOF And Not rsExport.EOF Then

                      'Loop through the recorsd

                      Do While Not rsExport.EOF 

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

                      'LINE ITEM DETAIL

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

                      If blnLoadLineItems Then

                      'Check To see If account uses line items

                      If API.IntBlockMgr.IntegrationMgr.PobjIntegrate.intResult Then

                      'Check for line items

                      If API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fAccountUsesLineItems(rsExport.Fields("AccountX").Value) Then

                      blnAcctUsesLineItems = RES.FDMTrue

                      Else

                      blnAcctUsesLineItems = RES.FDMFalse

                      End If

                      End If

                      End If

                     

                      'Generate the HFM File Format

                      'Category

                      If Len(strTCat) > 0 Then

                      strLine = strTCat & strDelim

                      Else

                      strLine = strNoCategory & strDelim

                      End If

                      'Year

                      If Len(strTargYear) > 0 Then

                      strLine = strLine & strTargYear & strDelim

                      Else

                      strLine = strLine & strNoYear & strDelim

                      End If

                      'Period

                      If Not blnUseRange Then

                      If Len(strTPer(0)) > 0 Then

                      strLine = strLine & strTPer(0) & strDelim

                      Else

                      strLine = strLine & strNoPeriod & strDelim

                      End If

                      End If

                     

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

                      'LINE ITEM DETAIL

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

                      If blnLoadLineItems Then

                      'Check for line items

                      If Not blnAcctUsesLineItems Then

                      'NO LINE ITEM DETAIL - any View is OK

                      If Len(rsExport.Fields("DataView").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("DataView").Value & strDelim

                      strDataView = rsExport.Fields("DataView").Value

                      Else

                      strLine = strLine & strNoView & strDelim

                      strDataView = strNoView

                      End If

                      Else

                      'LINE ITEM DETAIL - must use scenario view

                      strLine = strLine & strNoView & strDelim

                      strDataView = strNoView

                      End If

                      Else

                      'View

                      If Len(rsExport.Fields("DataView").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("DataView").Value & strDelim

                      strDataView = rsExport.Fields("DataView").Value

                      Else

                      strLine = strLine & strNoView & strDelim

                      strDataView = strNoView

                      End If

                      End If

                     

                      'Entity

                      If Len(rsExport.Fields("EntityX").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("EntityX").Value & strDelim

                      Else

                      strLine = strLine & strNoEntity & strDelim

                      End If

                     

                      'DataValue

                      If Len(strDataValue) > 0 Then

                      strLine = strLine & strDataValue & strDelim

                      Else

                      strLine = strLine & strNoDataValue & strDelim

                      End If

                     

                      'Account

                      If Len(rsExport.Fields("AccountX").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("AccountX").Value & strDelim

                      Else

                      strLine = strLine & strNoAccount & strDelim

                      End If

                      'ICP

                      If Len(rsExport.Fields("ICPX").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("ICPX").Value & strDelim

                      Else

                      strLine = strLine & strNoICCoParty & strDelim

                      End If

                      'Custom1

                      If Len(rsExport.Fields("UD1X").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("UD1X").Value & strDelim

                      Else

                      strLine = strLine & strNoUD1 & strDelim

                      End If

                      'Custom2

                      If Len(rsExport.Fields("UD2X").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("UD2X").Value & strDelim

                      Else

                      strLine = strLine & strNoUD2 & strDelim

                      End If

                      'Custom3

                      If Len(rsExport.Fields("UD3X").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("UD3X").Value & strDelim

                      Else

                      strLine = strLine & strNoUD3 & strDelim

                      End If

                      'Custom4

                      If Len(rsExport.Fields("UD4X").Value) > 0 Then

                      strLine = strLine & rsExport.Fields("UD4X").Value & strDelim

                      Else

                      strLine = strLine & strNoUD4 & strDelim

                      End If

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

                      'Period Range

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

                      If blnUseRange Then

                      'Add all periods to the line

                             For lngPerCount = lngAmountFieldStart To rsExport.Fields.Count - 1

                      'Check whether ML Zero Loading is enabled

                      If API.DataWindow.DBTools.fGlobalOptionGet(API.IntBlockMgr.IntegrationMgr.PstrSysKey & "EnableMLZeros").Status Then

                      dblValue = CDbl(rsExport.Fields(lngPerCount).Value)

                      Else

                      If CDbl(rsExport.Fields(lngPerCount).Value) <> 0 Then

                      dblValue = CDbl(rsExport.Fields(lngPerCount).Value)

                      Else

                      dblValue = ""

                      End If

                      End If

                     

                                If lngPerCount = lngAmountFieldStart Then

                      'First amount already has delimiter

                                strLine = strLine & dblValue

                      Else

                      'Subsequent amounts must be prefixed with a delimiter

                      strLine = strLine & strDelim & dblValue

                      End If

                             Next

                     

                      'Write the line to the file

                      objFile.WriteLine(strLine)

                      Else

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

                      'LINE ITEM DETAIL

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

                      If blnLoadLineItems Then

                      'Check for line items

                      If Not blnAcctUsesLineItems Then

                      '*** NO LINE ITEMS - Use standard line ***

                      strLine = strLine & API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.SetAmountToHFMuserPref(rsExport.Fields("Amt").Value)

                      'Write the line to the file

                      objFile.WriteLine(strLine)

                      Else

                      'Call FM API to clear existing line items

                      'API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fClearLineItems CStr(rsExport.Fields("EntityX").Value), CStr(rsExport.Fields("AccountX").Value), CStr(rsExport.Fields("ICCoPartyX").Value), CStr(rsExport.Fields("UserDefined1X").Value), CStr(rsExport.Fields("UserDefined2X").Value), CStr(rsExport.Fields("UserDefined3X").Value), CStr(rsExport.Fields("UserDefined4X").Value), CStr(strScenario), CStr(strYear), CStr(strPeriod)

                     

                      Select Case intLoadType

                      Case 1

                      '*** SUMMARY LINE ITEMS USED  ***

                      objFile.WriteLine("!LINE_ITEM_DETAIL")

                      'Amount, Finish the line with normal lenght

                      strLine = strLine & """" & rsExport.Fields("AccountX").Value & """" & strDelim & API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.SetAmountToHFMuserPref(rsExport.Fields("Amt").Value)

                      'Write the line to the file

                      objFile.WriteLine(strLine)

                      objFile.WriteLine("!DATA")

                      Case 2

                      '*** DETAIL LINE ITEMS USED  ***

                      objFile.WriteLine("!LINE_ITEM_DETAIL")

                      'Initialize the intersection object

                      objIntersect.varDataView = rsExport.Fields("DataView").Value

                      objIntersect.varAccount = rsExport.Fields("AccountX").Value

                      objIntersect.varEntity = rsExport.Fields("EntityX").Value

                      objIntersect.varICP = rsExport.Fields("ICPX").Value

                      objIntersect.varUD1 = rsExport.Fields("UD1X").Value

                      objIntersect.varUD2 = rsExport.Fields("UD2X").Value

                      objIntersect.varUD3 = rsExport.Fields("UD3X").Value

                      objIntersect.varUD4 = rsExport.Fields("UD4X").Value

                     

                      'Get drill down for the current intersection

                      With API.SQLMgr.fTrialBalanceConvertedDrillDown(CStr(strLoc), CStr(strCat), CStr(strPer(0)), objIntersect)

                      If Not .BOF And Not .EOF Then

                      Do While Not .EOF

                      'Write the DETAIL line to the file

                      objFile.WriteLine(strLine & """" & .Fields("Account").Value & "-" & .Fields("Entity").Value & "-" & .Fields("Desc1").Value & """" & strDelim & API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.SetAmountToHFMuserPref(.Fields("Amt").Value))

                      .Movenext

                      Loop

                      Else

                      'Amount, Finish the line with normal lenght

                      strLine = strLine & """" & rsExport.Fields("AccountX").Value & """" & strDelim & API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.SetAmountToHFMuserPref(rsExport.Fields("Amt").Value)

                      'Write the line to the file

                      objFile.WriteLine(strLine)

                      End If

                      .Close

                      End With

                      objFile.WriteLine("!DATA")

                      End Select

                      End If

                      Else

                      'Line Item processing is not active

                      strLine = strLine & API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.SetAmountToHFMuserPref(rsExport.Fields("Amt").Value)

                      'Write the line to the file

                      objFile.WriteLine(strLine)

                      End If

                      End If

                     

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

                      'DATA PROTECTION - Store list of Entities that need data protection

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

                      If blnProtection Then

                      'Check for an Entity change

                      If LCase(strCurEntity) <> LCase(rsExport.Fields("EntityX").Value) Then

                     

                         'Create a single Record to be used for updates

                      Set NewRec = DW.DBTools.fCreateRec()

                        With NewRec

                            .varKey = rsExport.Fields("EntityX").Value

                            If Len(rsExport.Fields("EntityX").Value) > 0 Then

                            .colFlds.Add "Entity", rsExport.Fields("EntityX").Value

                            Else

                            .colFlds.Add "Entity", strNoEntity

                            End If

                            .colFlds.Add "Scenario", strTCat

                            .colFlds.Add "Year", strTargYear

                      .colFlds.Add "Period", strTPer(0)

                      .colFlds.Add "DataView", strDataView 'rsExport.Fields("DataView").Value

                      .colFlds.Add "Accounts", ""

                      .colFlds.Add "Operator", strCriteria1

                      .colFlds.Add "Criteria", strProtection1

                        End With

                        

                         'Append the record to the collection

                      RES.PcolRecs.Append NewRec

                     

                      'Update the current entity flag

                      strCurEntity = rsExport.Fields("EntityX").Value

                      End If

                      End If

                     

                      rsExport.Movenext

                      Loop

                     

                      'Close recordset

                      rsExport.Close

                      Set rsExport = Nothing

                     

                      'Export Complete

                      objR.blnIsErr = RES.FDMFalse

                      objR.intResult = RES.FDMTrue

                      objR.strErrDesc = ""

                      objR.lngErrCode = 0

                      Else

                      'No Data to Export

                      objR.blnIsErr = RES.FDMFalse

                      objR.intResult = RES.FDMFalse

                      objR.strErrDesc = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fLocalizeString("A_" & API.IntBlockMgr.IntegrationMgr.PstrSysKey & "_EX_MS_10")

                      objR.lngErrCode = 0

                     

                      'Close the object

                      rsExport.Close

                      End If

                    Next

                     

                     

                    'Close the objects

                    objFile.close

                    Set objFile = Nothing

                    Set objFileSys = Nothing

                     

                     

                    '*******************************************************************

                    'DATA PROTECTION - Extract Protected Data & Append to DAT file

                    '*******************************************************************

                    If blnProtection Then

                      Dim vaExtract

                      Dim objRec

                      For Each ObjRec In RES.PcolRecs

                      vaExtract = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fDBExtractArray(CStr(API.DataWindow.Connection.PstrDirOutbox),objRec.colFlds.Item("Entity").varValue, CStr(objRec.colFlds.Item("Scenario").varValue), CStr(objRec.colFlds.Item("Year").varValue), objRec.colFlds.Item("Period").varValue, objRec.colFlds.Item("DataView").varValue, , CStr(objRec.colFlds.Item("Operator").varValue), CStr(objRec.colFlds.Item("Criteria").varValue), RES.FDMFalse)

                      Set objHWReturn = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fDBAppendArrayToFile(CStr(strFile), vaExtract)

                      'Check the result

                      With ObjHWReturn

                      If .PblnIsErr Then

                      'Error during load

                      objR.intResult = RES.FDMFalse

                      objR.strErrDesc = .PstrErrDesc

                      objR.lngErrCode = .PlngErrCode

                      objR.varList = .PstrErrFile

                      objR.blnIsErr = RES.FDMTrue

                      'Destroy Collection

                      Set RES.PcolRecs = Nothing

                      Exit Function

                      End If

                      End With

                      Next

                      'Destroy Collection

                      Set RES.PcolRecs = Nothing

                    End If

                     

                     

                    'Set the logging strings

                    strIODesc = CStr(strFile)

                    strEventDesc = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fLocalizeString("A_" & API.IntBlockMgr.IntegrationMgr.PstrSysKey & "_EX_EM_5", CStr(blnLoadLineItems),CStr(blnProtection),CStr(strTCat),CStr(strPerDesc),CStr(strTargYear))

                    strErrDesc = CStr(objR.strErrDesc)

                     

                     

                    'Log Action in transaction log

                    API.DataWindow.DBTools.mLog 5005, API.DataWindow.Connection.PstrUserID, CDate(TStart), Now(), CStr(strIODesc), "OK", CStr(strEventDesc), CStr(strErrDesc)

                     

                     

                    'Destroy objects

                    Set objIntersect = Nothing

                    If Not API.State.GblnBatchLoadActive Then API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.fDisconnect

                    If Not API.State.GblnBatchLoadActive Then Set API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon = Nothing

                     

                     

                    End Function

                     

                     

                    Function fSetJournalNoAll(strLoc, lngSegKey, lngPartKey, lngCatKey, strStartPerKey, strEndPerKey)

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

                    'Hyperion System 9 FDM Integration Action

                    '

                    'Created By:     Hyperion Solutions

                    'Date Created:   05/05/2006

                    '

                    'Purpose:   Flag all lines for the specified POV as a single

                    ' journal entry number.    

                    '              

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

                      'Declare local variables

                      Dim strAutoJournalID

                      Dim strDML

                     

                     

                      'JA = Journal Auto (Used to allow different data values per entity)

                      strAutoJournalID = "JA"

                      strAutoJournalID = strAutoJournalID & DatePart("h",Now())

                      strAutoJournalID = strAutoJournalID & DatePart("n",Now())

                      strAutoJournalID = strAutoJournalID & DatePart("s",Now())

                     

                     

                        Select Case API.DataWindow.Connection.PlngProviderID

                            Case 1

                      'enmDBProviderType.dwSQLServer

                      'Update the current trial balance with an Automatic JV Number

                      strDML = "UPDATE tDataSeg" & CStr(lngSegKey) & " "

                      strDML = strDML & "Set JournalID = '" & strAutoJournalID & "', Desc2 = 'FDM Auto-JV Loc: " & strLoc  & " User: " & API.DataWindow.Connection.PstrUserID & "' "

                      strDML = strDML & "WHERE "

                      strDML = strDML & "(Len(JournalID) = 0 Or JournalID Is Null) "

                      strDML = strDML & "And (PeriodKey BETWEEN '" & CStr(strStartPerKey) & "' AND '" & CStr(strEndPerKey) & "') "

                      strDML = strDML & "And (CatKey = " & CStr(lngCatKey) & ") "

                      strDML = strDML & "And (PartitionKey = " & CStr(lngPartKey) & ")"

                      Case 10

                      'enmDBProviderType.dwOracle

                      'Update the current trial balance with an Automatic JV Number

                      strDML = "UPDATE tDataSeg" & CStr(lngSegKey) & " "

                      strDML = strDML & "Set JournalID = '" & strAutoJournalID & "', Desc2 = 'FDM Auto-JV: Loc: " & strLoc & " User: " & API.DataWindow.Connection.PstrUserID & "' "

                      strDML = strDML & "WHERE "

                      strDML = strDML & "(Length(JournalID) = 0 Or JournalID Is Null) "

                      strDML = strDML & " And (TO_CHAR(PeriodKey,'" & API.DataWindow.Connection.PstrQueryDatemask & "') Between '" & CStr(strStartPerKey) & "' And '" & CStr(strEndPerKey) & "') "

                      strDML = strDML & "And (CatKey = " & CStr(lngCatKey) & ") "

                      strDML = strDML & "And (PartitionKey = " & CStr(lngPartKey) & ")"

                      End Select

                     

                     

                      'Execute the update

                      fSetJournalNoAll = API.DataWindow.DataManipulation.fExecuteDML(CStr(strDML), RES.FDMTrue)

                     

                     

                    End Function

                     

                     

                    Function fSetJournalNoPerEntity(strLoc, strCat, strPer, lngSegKey, lngPartKey, lngCatKey, strStartPerKey, strEndPerKey)

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

                    'Hyperion System 9 FDM Integration Action

                    '

                    'Created By:     Hyperion Solutions

                    'Date Created:   05/05/2006

                    '

                    'Purpose:   Flag each entity for the specified POV with its own

                    ' journal entry number.    

                    '              

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

                      'Declare local variables

                      Dim strAutoJournalID

                      Dim strDML

                      Dim rsUpdate

                      Dim strLastEnt

                      Dim lngCount

                     

                     

                      'Default to true

                      fSetJournalNoPerEntity = RES.FDMTrue

                     

                     

                      'Set a Journal ID for each data value line

                      Set rsUpdate = API.SQLMgr.fTrialBalanceConvertedFiltered(CStr(strLoc), CStr(strCat), CStr(strPer(0)), CStr(strPer(1)), 2, RES.FDMTrue, RES.FDMFalse)

                     

                     

                      If Not rsUpdate.BOF And Not rsUpdate.EOF Then

                      strLastEnt = ""

                      Do While Not rsUpdate.EOF

                      'Check for new journal header

                      If LCase(strLastEnt) <> LCase(rsUpdate.Fields("EntityX").Value) Then

                      'Set the counter

                      lngCount = lngCount + 1

                     

                      'Set the last Entity

                      strLastEnt = rsUpdate.Fields("EntityX").Value

                     

                     

                      'Set the Journal ID for FLASHSALES entity

                      'JA = Journal Auto (Used to allow different data values per entity)

                      strAutoJournalID = "JA"

                      strAutoJournalID = strAutoJournalID & DatePart("h",Now())

                      strAutoJournalID = strAutoJournalID & DatePart("n",Now())

                      strAutoJournalID = strAutoJournalID & CStr(lngCount)

                     

                     

                         Select Case API.DataWindow.Connection.PlngProviderID

                             Case 1

                      'enmDBProviderType.dwSQLServer

                      'Update the current trial balance with an Automatic JV Number

                      strDML = "UPDATE tDataSeg" & CStr(lngSegKey) & " "

                      strDML = strDML & "Set JournalID = '" & strAutoJournalID & "', Desc2 = 'FDM Auto-JV: Entity: " & strLastEnt & " Loc: " & strLoc  & " User: " & API.DataWindow.Connection.PstrUserID & "' "

                      strDML = strDML & "WHERE "

                      strDML = strDML & "(Len(JournalID) = 0 Or JournalID Is Null) "

                      strDML = strDML & "And (PeriodKey BETWEEN '" & CStr(strStartPerKey) & "' AND '" & CStr(strEndPerKey) & "') "

                      strDML = strDML & "And (CatKey = " & CStr(lngCatKey) & ") "

                      strDML = strDML & "And (PartitionKey = " & CStr(lngPartKey) & ") "

                      strDML = strDML & "And (EntityX = '" & strLastEnt & "')"

                      Case 10

                      'enmDBProviderType.dwOracle

                      'Update the current trial balance with an Automatic JV Number

                      strDML = "UPDATE tDataSeg" & CStr(lngSegKey) & " "

                      strDML = strDML & "Set JournalID = '" & strAutoJournalID & "', Desc2 = 'FDM Auto-JV: Entity: " & strLastEnt & " Loc: " & strLoc  & " User: " & API.DataWindow.Connection.PstrUserID & "' "

                      strDML = strDML & "WHERE "

                      strDML = strDML & "(Length(JournalID) = 0 Or JournalID Is Null) "

                      strDML = strDML & " And (TO_CHAR(PeriodKey,'" & API.DataWindow.Connection.PstrQueryDatemask & "') Between '" & CStr(strStartPerKey) & "' And '" & CStr(strEndPerKey) & "') "

                      strDML = strDML & "And (CatKey = " & CStr(lngCatKey) & ") "

                      strDML = strDML & "And (PartitionKey = " & CStr(lngPartKey) & ") "

                      strDML = strDML & "And (UPPER(EntityX) = '" & UCase(strLastEnt) & "')"

                      End Select

                     

                     

                      'Execute the update

                      If API.DataWindow.DataManipulation.fExecuteDML(CStr(strDML), RES.FDMTrue) = RES.FDMFalse Then

                      fSetJournalNoPerEntity = RES.FDMFalse

                      End If

                     

                      End If

                      rsUpdate.movenext

                      Loop

                      rsUpdate.close

                      Set rsUpdate = Nothing

                      End If

                     

                     

                    End Function

                    • 7. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
                      Francisco Amores

                      I don't know your requirements but if you link Custom12 to HFM UD2 is because you want to create mappings with valid target values. For example you will be able to browser UD2 members from mappings of Custom12.

                       

                      Export script has code for different features such us line item details, period ranges...

                      For example the following code exports UD2X. If you don't want UD2 to be exported... then you can comment this code.

                       

                      You need to be very careful with what you are doing in order to avoid issues with drill-through from HFM to FDM.

                       

                          'Custom2

                        If Len(rsExport.Fields("UD2X").Value) > 0 Then

                        strLine = strLine & rsExport.Fields("UD2X").Value & strDelim

                        Else

                        strLine = strLine & strMissingValue & strDelim

                        End If

                       

                      Again, I suggest you to analyze your solution.

                      Which is the purpose of Custom 12 in FDM?

                      and Custom 2?

                      • 8. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
                        944384

                        Yes i am creating the seperate mapping for this.

                        But on this EXPORT script after commenting the UD2, dont i need to replace UD12 in the same place whereever i find UD2 in the present coding. below are some listed items i found UD2 in this EXPORT script.

                        so if i change replace it with UD12, then data export will be in the same format. (Only replace for UD2 data, would be UD12)

                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                        'Set the dimension active state

                          Case "ud12"

                          strNoUD12 = .Fields("DimNoDataValue").Value

                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

                          'LINE ITEM DETAIL

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

                          'Custom2

                          If Len(rsExport.Fields("UD12X").Value) > 0 Then

                          strLine = strLine & rsExport.Fields("UD12X").Value & strDelim

                          Else

                          strLine = strLine & strNoUD12 & strDelim

                          End If

                         

                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                          '*** DETAIL LINE ITEMS USED  ***

                          objFile.WriteLine("!LINE_ITEM_DETAIL")

                          'Initialize the intersection object

                          objIntersect.varUD2 = rsExport.Fields("UD2X").Value

                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                        • 9. Re: Urgent!, MAP dimensions from FDM to HFM (Target)
                          Francisco Amores

                          As long as UD12 is active it should be exported in the record set

                           

                          Best way if to test :-)