6 Replies Latest reply on Oct 6, 2011 9:42 AM by 851939

    Rows not deleted after delete in BefExportToDat Script in FDM

    851939
      Hi

      I am using FDM ERPI to load data from Oracle eBS GL 1.5 to Oracle Hyperion Planning 11.1.2.1.

      I have to slit data coming from eBS to 2 Essbase applications, using a temporary table :
      Location1 (data to app1) : I have to load data on accounts listed in the temporary table.
      Location2 (data to app2) : I have to load data on accounts not listed in the temporary table.

      For that, I have chosen to use the BefExportToDat script on which I would like to delete from the tdatasegxx table the data I don't want to load into essbase.

      I do my test with the ERPIEBS location.

      The "Delete" sql generated is correct (I have displayed the generated sql and executed it via SQL Developer without problem).

      but when I execute the "Delete" sql through FDM, the result is "True", but rows have not been deleted from the table.

      You can read the script bellow.

      Any idea?

      Thanks in advance

      Fanny
      Sub BefExportToDat(strLoc, strCat, strPer, strTCat, strTPer, strFile)
      '------------------------------------------------------------------
      'Oracle Hyperion FDM EVENT Script:
      '
      'Created By:       fanny
      'Date Created:       2011-08-30 15:33:42
      '
      'Purpose:
      '
      '------------------------------------------------------------------
      
      Set objFileSys = CreateObject("Scripting.FileSystemObject")
      objfilepath = "D:\Oracle\FDMApplication\FDMTEST\Inbox\ERPIEBS\ComptesCA.txt"
      
      'Vérification de l'existence du fichier
      If objFileSys.FileExists(objfilepath) Then
      
           Set objReadFile = objFileSys.OpenTextFile(objfilepath)
      
           'Création de la table temporaire qui stockera les indicateurs de CA à prendre en compte
           strSQL = "CREATE TABLE TMP (Compte NVARCHAR2(75)) "
           API.DataWindow.DataManipulation.fexecuteDML(strSQL)
      
           'Alimentation de la table avec les lignes du fichier
           Do Until objReadFile.AtEndOfStream
                sText = objReadFile.ReadLine
                strSQL = "INSERT INTO TMP VALUES ('"&sText&"')"
                API.DataWindow.DataManipulation.fexecuteDML(strSQL)
           Loop
           objReadFile.Close
      
           'Récupération des paramètres du flux
           dtePerKey = RES.PdtePerKey 'Period Key
           strCatKey = RES.PlngCatKey 'Category Key
           strLocKey=RES.PlngLocKey 'Location Key
           strTableName = "tDataSeg" & API.DataWindow.Reports.PlngSegKey 'Location Segment Number (tDataSegxx)
           
           'S il s'agit du flux CA, on ne garde que les indicateurs listés dans la table
           If strLoc="ERPIEBS" Then
                strSQL = "DELETE FROM " & strTableName
                strSQL = strSQL & " WHERE PartitionKey='" & strLocKey & "'"
                strSQt = strSQL & " AND CatKey = '" & strCatKey & "'"
                strSQL = strSQL & " AND PeriodKey = '" & dtePerKey & "'"
                strSQL = strSQL & " AND ACCOUNTX NOT IN (SELECT Compte FROM TMP)"
                
                'Show user a message
                RES.PlngActionType = 2
                RES.PstrActionValue = strSQL
                
                API.DataWindow.DataManipulation.fExecuteDML(strSQL)
                
           'S il s'agit du flux CR, on supprime les indicateurs listés dans la table
           ElseIf strLoc="ERPIEBSCR" Then
                strSQL = "DELETE FROM " & strTableName
                strSQL = strSQL & " WHERE PartitionKey='" & strLocKey & "'"
                strSQL = strSQL & " AND CatKey = '" & strCatKey & "'"
                strSQL = strSQL & " AND PeriodKey = '" & dtePerKey & "'"
                strSQL = strSQL & " AND ACCOUNTX IN (SELECT Compte FROM TMP)"
                
                API.DataWindow.DataManipulation.fexecuteDML(strSQL)
      
           End If
           
           'Suppression de la table temporaire
           strSQL = "DROP TABLE TMP"
           'API.DataWindow.DataManipulation.fexecuteDML(strSQL)
      
      Else
           'Show user a message
           RES.PlngActionType = 2
           RES.PstrActionValue = "Impossible de trouver le fichier D:\Oracle\FDMApplication\FDMTEST\Inbox\ERPIEBS\ComptesCA.txt" 
      End If
      
      End Sub