Validate Media Object files physically exist in JD Edwards EnterpriseOne

24 Aug 2012

Media Objects are method to store a file attachment to a record within the EnterpriseOne ERP system. The gist of the process is there is a table (F00165) that will store a pointer to a physical file location stored on a server. Occasionally these physical files will be deleted due to bugs within EnterpriseOne (ex: OLE’s being linked to more than 1 transaction, etc…) or by user error where the file is physically deleted from the media object server.

I created this powershell script to go through F00165 media object table and verify that the file still physically exists. If it does not it will write a record to a CSV file to be reviewed later. Please note this is performed on an Oracle database, but can be application to any database with minor modifications.



##################################################################
# Validate_Media_Object_Files.ps1
# Created: 12/20/2010
# Author: Me
# Summary: Fetch all file and OLE media object records from the F00165 table.
# Then verify the file exists in the location specified (should always be media object server.
##################################################################
#------------------------
# Inits
#------------------------
[Reflection.Assembly]::LoadFile("C:\ora10g\odp.net\bin\2.x\Oracle.DataAccess.dll")
$connectionString = "Data Source=XXXXX;User Id=XXXXXXXX;Password=tXXXXXXX;"
$connection = New-Object Oracle.DataAccess.Client.OracleConnection($connectionString)
$connection.Open()
$queryString = "SELECT GDOBNM, GDTXKY, GDMOSEQN, GDGTMOTYPE, GDGTFILENM, GDUSER, GDUPMJ, GDTDAY FROM F00165 WHERE GDGTMOTYPE IN (2, 5)"
$command = new-Object Oracle.DataAccess.Client.OracleCommand($queryString, $connection)
$mediaObjRS = $command.ExecuteReader()
$files = @() # Initialize array outside loop ...
$counter = 0
$filedate = get-date -format "M-d-yyyy"
$outputFilename = "MissingMediaAttach" + $filedate + "_PROD.csv"

#---------------------------------------
# Loop through recordset
#---------------------------------------
while ($mediaObjRS.read()) {

   # Assign variables from recordset.
   $object = $mediaObjRS.GetString(0)
   $objectKey = $mediaObjRS.GetString(1)
   $objectSeq = $mediaObjRS.GetDecimal(2)
   $objectType=$mediaObjRS.GetDecimal(3)
   $objectFileName = $mediaObjRS.GetString(4)
   $objectUser = $mediaObjRS.GetString(5)
   $objectDate=$mediaObjRS.GetDecimal(6)
   $objectTime=$mediaObjRS.GetDecimal(7)

        #-------------------------------------------------------
        # Check if file exists based on filetype.
        #-------------------------------------------------------
        if($objectType -eq 2){
                # Type 2 = OLE
                $fileLocation = "\\jdemed02\D$\E812\MEDIAOBJ\Oleque\" + $objectFileName
        }elseif($objectType -eq 5){
                # Type 5 = FILE
                $fileLocation = $objectFileName
        }

        # Skip test if media object points to network share
        if($fileLocation.substring(1,2) -ne ":\"){
                # Skip test if URL
                if(!$fileLocation.tolower().StartsWith("http")){
                        $fileExists = Test-Path $fileLocation

                        if(!$fileExists){
                                #Write to output file
                                $obj = new-object psobject -prop @{
                                           Object = $object
                                           FileName = $objectFileName
                                           Type = $objectType
                                           Seq = $objectSeq
                                           Key = $objectKey
                                           User = $objectUser
                                           Date = $objectDate
                                           Time = $objectTime
                                   }
                                   $files += $obj
                                   $counter = $counter + 1
                                   Write-Host "$objectKey $objectSeq $objectType $objectFileName"
                        }
                }
        }
}

$files | Export-Csv $outputFilename

$connection.Close()

Write-Host "Record Count: " $counter