1 Reply Latest reply on Nov 18, 2015 6:40 PM by SH_INT

    FDM TB tolerance limit

    2717029

      Hello Experts,

       

      We are using FDM 11.1.2.3.500 and wrote a custom script as below to set import tolerance limit of 250 <entity currency>.

      Based on this script ,if source trail balance is out of balance by more than 250 then FDM import fails and working as expected but now we got an additional requirement of loading head count into hfm using fdm for couple of entites.

      As headcount will not have Dr=Cr, their source trial balance is becoming out balance by more than set tolerance limit i.e250.

      is there a way where we can skip this headcount a/c from considering into tolerance limit while importing the file?

       

      Sub AftValidate(strLoc, strCat, strPer, lngProcState)
      '------------------------------------------------------------------
      'Oracle Hyperion FDM EVENT Script:
      '
      '
      'Purpose: Trial balance validation script
      '
      '------------------------------------------------------------------

      Set objxx = CreateObject ("scripting.filesystemObject").opentextfile("D:\Oracle\FDMdata\ArcherFDM\fdm.log",8,True)
      objxx.writeline ("hhahhahah") 
      Dim strSQL
      Dim strBaseSQL
      Dim rs
      Dim lngCheckAccountAmount
      Dim strCheckAccount
      Dim intTolerance
      Dim strMessage

      '*******************************************
      'This script is valid for Actual Scenario
      '******************************************
      If strCat = "ACT" Then

      If 1 = 1 Then
      objxx.writeline ("entered first loop")
          'The name of the logic account whose value to check
          strCheckAccount = "CHECKACCOUNT"
         
          'The tolerance for the check account
          intTolerance = 250
         
          'This is the message the user will see
         
         
          '======================================================================================
          ' YOU DO NOT NEED TO CHANGE ANYTHING AFTER THIS LINE
          '======================================================================================
         
          'Build up a basic SQL string
          strBaseSQL = "SELECT sum(AMOUNT)as AMOUNT FROM TDATASEG@SEGNO@ WHERE PERIODKEY = TO_DATE('@PERIOD@','mm/dd/yyyy')"
          objxx.writeline (strBaseSQL)
          strBaseSQL = strBaseSQL & "AND PARTITIONKEY = @PARTKEY@ AND CATKEY = @CATKEY@ "
         
          'Substitute the relevant keys for this POV
          strBaseSQL = Replace(strBaseSQL, "@PERIOD@", RES.PdtePerKey)
          strBaseSQL = Replace(strBaseSQL, "@PARTKEY@", RES.PlngLocKey)
          strBaseSQL = Replace(strBaseSQL, "@SEGNO@", RES.PlngSegKey)
          strBaseSQL = Replace(strBaseSQL, "@CATKEY@", RES.PlngCatKey)

          'Replace account
           strSQL = Replace(strBaseSQL, "@ACC@", strCheckAccount)
           objxx.writeline (strSQL)
          'Create a recordset
          Set rs = DW.DataAccess.farsSnap(strSQL)
           objxx.writeline ("query executed")
         
          'Get the value of the account
          lngCheckAccountAmount = 0
           objxx.writeline (Abs(CDbl(lngCheckAccountAmount)))
          If Not rs.BOF And Not rs.EOF Then
              lngCheckAccountAmount = rs.Fields("Amount").Value
          End If
         
          Set rs = Nothing
         
          'Here's the check. It must be within tolerance.
          objxx.writeline (Abs(CDbl(lngCheckAccountAmount)))
          objxx.writeline (intTolerance)
             If Abs(CDbl(lngCheckAccountAmount)) > intTolerance Then
                     
                        objxx.writeline ("entered loop")
                        API.MaintenanceMgr.mProcessLog API.POVMgr.PPOVLocation, API.POVMgr.PPOVCategory, API.POVMgr.PPOVPeriod, 2, False, "AftValidate error", Now()

              'Error so update the process state
              'API.MaintenanceMgr.mProcessLog strLoc, strCat, strPer, 1, False, "Import Errors", Now()
             ' API.MaintenanceMgr.mProcessLog RES.PstrLoc, RES.PstrCat, RES.PstrPer, 1, False, "Import Errors", Now()
              ' Last event - Cancel is not enabled so ignore it.
              'RES.PcolScriptInfo.Item("ScriptInfo").ColFlds.item("Cancel").varValue = RES.FDMTrue
              ' Set up a message for the user. 2 = new window, 6 = InfoBar
              RES.PlngActionType = 2
              ' Message for the user
              strMessage = "Validation error: will not proceed to next step because SET TOLERANCE is [" & intTolerance & "]and TB IMBALANCE is currently ["&Abs(CDbl(lngCheckAccountAmount))&"]"
              RES.PstrActionValue = strMessage


          End If
         
      End If
      End If

      End Sub