Useful PowerShell Script for Downloading Files from UCM Based on a CSV File

select DID, DDOCNAME, DDOCTITLE, DDOCAUTHOR 
from revisions 
where DDOCTITLE like '%your pattern or BICC job%'

<#
.SYNOPSIS
    Downloads documents from Oracle Fusion / WebCenter Content (UCM) using SOAP GET_FILE service.
    CSV columns expected: DID, DDDOCNAME, DDOCTITLE, DDOCAUTHOR
    Filename priority:
    1. DDOCTITLE (preferred - usually the meaningful title)
    2. DDDOCNAME
    3. dID.bin (fallback)
    Files are saved in the same folder as this .ps1 script.
.PARAMETER csvFile Path to your CSV file
.PARAMETER fusionUrl Base URL of Fusion environment[](https://yourpod-fa-ext.oraclecloud.com/)
.PARAMETER username Fusion / UCM username
.PARAMETER password Password
#>

param (
    [Parameter(Mandatory=$true)]
    [string]$csvFile,
    [Parameter(Mandatory=$true)]
    [string]$fusionUrl,
    [Parameter(Mandatory=$true)]
    [string]$username,
    [Parameter(Mandatory=$true)]
    [string]$password
)

# Force current directory to be the folder where this script is located
# This ensures files are saved next to the .ps1 file (most predictable)
$scriptDir = Split-Path -Parent $MyInvocation.MyCommand.Path
Set-Location -Path $scriptDir -ErrorAction Stop
Write-Output "Saving files to: $scriptDir"

# Normalize Fusion URL
if (-not $fusionUrl.EndsWith('/')) {
    $fusionUrl += '/'
}
$soapEndpoint = $fusionUrl + "idcws/GenericSoapPort"

# Load CSV
try {
    $documents = Import-Csv -Path $csvFile -ErrorAction Stop
} catch {
    Write-Error "Cannot read CSV file: $($_.Exception.Message)"
    exit 1
}

# Basic Authentication
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("$username:$password"))
$authHeader = "Basic $base64AuthInfo"

foreach ($doc in $documents) {
    $did = $doc.DID
    if ([string]::IsNullOrWhiteSpace($did)) {
        Write-Warning "Skipping row - missing DID"
        continue
    }

    # Get name sources safely (compatible with PowerShell 5.1)
    $title   = if ($doc.DDOCTITLE) { $doc.DDOCTITLE.Trim() } else { "" }
    $docname = if ($doc.DDDOCNAME) { $doc.DDDOCNAME.Trim() } else { "" }

    Write-Output "Processing dID: $did"
    Write-Output " Title (DDOCTITLE) : '$title'"
    Write-Output " DocName (DDDOCNAME): '$docname'"

    # -------------------------------------------------------------------------
    # SOAP Request
    # -------------------------------------------------------------------------
    $soapBody = @"
                <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">
                   <soapenv:Header/>
                   <soapenv:Body>
                      <ucm:GenericRequest webKey="cs">
                         <ucm:Service IdcService="GET_FILE">
                            <ucm:Document>
                               <ucm:Field name="dID">$did</ucm:Field>
                            </ucm:Document>
                         </ucm:Service>
                      </ucm:GenericRequest>
                   </soapenv:Body>
                </soapenv:Envelope>
                "@
                
    $headers = @{
        "Authorization" = $authHeader
        "Content-Type"   = "text/xml;charset=UTF-8"
    }

    try {
        $response = Invoke-WebRequest -Uri $soapEndpoint `
                                      -Method Post `
                                      -Headers $headers `
                                      -Body $soapBody `
                                      -ErrorAction Stop

        if ($response.StatusCode -ne 200) {
            throw "HTTP $($response.StatusCode) $($response.StatusDescription)"
        }

        $rawBytes = $response.Content

        # Extract multipart boundary
        $contentType = $response.Headers["Content-Type"]
        if ($contentType -notmatch 'boundary="?([^";]+)"?') {
            throw "No multipart boundary found in Content-Type"
        }
        $boundary = "--" + $matches[1]

        $encoding = [Text.Encoding]::GetEncoding('iso-8859-1')
        $rawString = $encoding.GetString($rawBytes)
        $parts = $rawString -split ([regex]::Escape($boundary))

        if ($parts.Count -lt 3) {
            throw "Unexpected MIME parts count: $($parts.Count)"
        }

        $filePart = $parts[2].Trim()
        $headerEndIndex = $filePart.IndexOf("`r`n`r`n")
        if ($headerEndIndex -eq -1) {
            throw "Cannot find end of MIME headers in file part"
        }

        $partHeaders = $filePart.Substring(0, $headerEndIndex)
        $binaryStart = $headerEndIndex + 4
        $binaryString = $filePart.Substring($binaryStart)
        $fileBytes = $encoding.GetBytes($binaryString)

        # Try to get filename from response headers (sometimes present)
        $responseFileName = $null
        if ($partHeaders -match '(?i)filename="?([^";\r\n]+)"?') {
            $responseFileName = $matches[1].Trim()
            Write-Output " Response provided filename: '$responseFileName'"
        }

        # Final filename decision
        $finalFileName = if ($responseFileName) {
            $responseFileName
        } elseif ($title) {
            $title
        } elseif ($docname) {
            $docname
        } else {
            "$did"
        }

        # Clean filename and ensure extension
        $finalFileName = $finalFileName -replace '[<>:"/\\|?*]', '_'
        if (-not $finalFileName.Contains('.')) {
            $finalFileName += ".pdf"   # ← Change to .pdf / .docx / .xlsx / .zip / .bin as appropriate
        }

        # Save file
        [IO.File]::WriteAllBytes($finalFileName, $fileBytes)
        Write-Output "Successfully saved: $finalFileName (size: $($fileBytes.Length) bytes)"
    }
    catch {
        Write-Error "Failed for dID $did → $($_.Exception.Message)"
    }
}

Write-Output "All downloads completed."
Write-Output "Files are saved in: $scriptDir"

.\Download-UCM-Files.ps1 -csvFile "C:\temp\my-documents.csv" `
                         -fusionUrl "https://yourpod-fa-ext.oraclecloud.com/" `
                         -username "your.user@company.com" `
                         -password "YourPassword123"