3 Replies Latest reply on Nov 18, 2009 1:11 PM by user96869

    FDM EventScript not working with Batch Load

    user96869
    user96869 Nov 17, 2009 3:21 PM
      Hi,

      We built an EventScript in ImportAction in FDM that is working fine when we import file individually using the FDM Webclient.

      The script is validating Total # of records, total DT and Total Credit that we have in the file and compare with a control line that is included in the file Extract from SAP. That control line is always the last line in each eatract and it contains # of records, Total DT and Total Credit. If it doesn't reconcile it won't Import. In our script, it's looking for the files that are in the OpenBatch folder but when FDM laucnh the process it renames the folder and then load the extract.

      Our problem is we are using Batch Process to load our data in to HFM and that new script is not working with that process. We are implementing SAP on part of the business and SAP extract has that control line and managers want's FDM not to import the file it the control is not working.

      Anyone has a idea how to make that script working?
        • 1. Re: FDM EventScript not working with Batch Load
          668692
          668692 Nov 17, 2009 10:29 PM (in response to user96869)
          When you import the data via batch script then ImportAction script will not be executed so you need to
          copy all the "importaction" code to batchaction script . Since this script will be executed during the
          batch process .

          so you can write all your customizations as below


          Sub BatchAction(strEventName, objFile)

          If strEventName = "ImportAction" Then

          -- copy all the code from importaction script to here


          End If

          End Sub

          Please let me know whether the above solution work for you.
          1 person found this helpful
          • 2. Re: FDM EventScript not working with Batch Load
            682330
            682330 Nov 18, 2009 7:20 AM (in response to 668692)
            Hi,

            Do you still need to retain the Code in ImportAction Event as well?
            • 3. Re: FDM EventScript not working with Batch Load
              user96869
              user96869 Nov 18, 2009 1:11 PM (in response to user96869)
              Hi,

              It didn't work.

              Do I need to remove the path in my script? Because the Path was looking for files in the OpenBAtch folder.

              This is our script:

              Sub BatchAction(strEventName, objFile)
              '------------------------------------------------------------------
              'Hyperion FDM EVENT Script:
              '
              'Created By:      prakuma
              'Date Created:      2009-11-18-01:20:20
              '
              'Purpose:
              '
              '------------------------------------------------------------------

              If strEventName="ImportAction" Then
              strWorkTableName=API.State.GstrActiveWorkTableName
              'Declaration of variables
                        Dim objfilepath,objfolderpath,objFolder,objFiles,objFileSys,objFileName,dataArray,oTextStream,i
                        Dim totalNoOfRecords,PreviousPositiveValue,PreviousNegativeValue,totalRowsCount
                        Dim totalCalculatedPositiveAmount,totalCalculatedNegativeAmount

                        i = 0
                        totalCalculatedPositiveValue = "0"
                        totalCalculatedNegativeValue = "0"
                        totalNoOfRecords = 0
                        PreviousPositiveValue = 0
                        
                        'Creating file system object
                        Set objFileSys = CreateObject("Scripting.FileSystemObject")

                        'Specify the file path
                        objfolderpath = "E:\FDMShare\LCL_IFRS\Inbox\Batches\OpenBatch\"
                        
              'Get the folder contents
                        Set objFolder = objFileSys.GetFolder(objfolderpath)
              Set objFiles = objFolder.Files

              'Loop through the files
                        For Each folderIdx In objFiles
                        objfilepath = objfolderpath & folderIdx.Name

                        'Getting the file name
                        intPos = InStrRev(objfilepath,"\")
                        objFileName= Mid(objfilepath, intPos+1, Len(objfilepath))
                        
                        
                        'Checking the file is exists and starting of the file contains with "0L"
                        If objFileSys.FileExists(objfilepath) And (Left(objFileName,2)="0L") Then
                             Set objReadFile = objFileSys.OpenTextFile(objfilepath)
                             
                             Do Until objReadFile.AtEndOfStream
                                       sText = objReadFile.ReadLine
                                       sText = Split(sText,",")               
                                       i = i + 1 'Calculate the total no of lines [last line - 1]
                                       
                                       'msgbox i
                                       
                                       If UBound(sText) = 7 Then
                                                 If Not Right(sText(7),1) = "-" Then 'all negative value contains "-" as a last character
                                                      totalCalculatedPositiveValue = CDbl(totalCalculatedPositiveValue) + CDbl(sText(7))
                                                 Else
                                                      totalCalculatedNegativeValue = CDbl(totalCalculatedNegativeValue) + CDbl(sText(7))
                                                 End If
                                       Else
                                            i = i - 1 'Calculate the total no of lines [last line - 1]
                                            
                                            totalCalculatedNegativeValue = ( totalCalculatedNegativeValue * -1 ) & "-" ' to convert in the format of negative no like "1234-"
                                            
              If Trim(sText(2)) = Trim(totalCalculatedPositiveValue) And Trim(sText(3)) = Trim(totalCalculatedNegativeValue) And Trim(sText(1)) = Trim(i) Then

                                                 'msgbox "Valid Data"
                                            Else
                                            delSQL = "DELETE FROM " & strWorkTableName
              If Not API.DataWindow.DataManipulation.fexecuteDML(delsql) Then
              RES.plngactiontype = 2
              RES.pstractionvalue = "Import Action Error deleting data not matching pov"
              Exit Sub

              End If

              End If     
              End If
                                                           
                             Loop
                             objReadFile.Close
                        Else
                             'msgbox "Not Exists"
                        End If
                        Next

              End If



              End Sub

              Edited by: user10931567 on 18-Nov-2009 5:08 AM