Automation PowerShell Script to invoke EPM Automate commands!

Lets have a detailed walkthrough of the PowerShell script detailing its purpose and functionality. The script is designed to perform several tasks: logging into the EPM, run the Data Management Integration rule, download the generated extract created by the integration rule, download the data file & then logout from the EPM instance.

Environment Setup

The script begins by setting up environment variables and directories.

$env:PATH += ";C:\oracle\EPM Automate\bin"

$pwds = "Password"

$logs = "C:\Users\Documents\logs"

$URL = https://planning-test-epm.oraclecloud.com

$datafiles = "C:\Oracle\EPM Automate\datafiles"

# Create logs directory if it doesn't exist

if (!(Test-Path -Path $logs)) {

    New-Item -Path $logs -ItemType Directory

}

 # Create datafiles directory if it doesn't exist

if (!(Test-Path -Path $datafiles)) {

    New-Item -Path $datafiles -ItemType Directory

}

Function to Run EPM Automate Commands

The script defines a function to run EPM Automate commands and capture their output.

function Run-EpmAutomate {

    param (

        [string]$command,

        [string]$outputLog

    )

    Start-Process -NoNewWindow -FilePath "epmautomate" -ArgumentList $command -RedirectStandardOutput $outputLog -Wait -PassThru | Out-Null

    Get-Content -Path $outputLog

}

Login to EPM Automate

The script logs into the EPM Automate service

$loginLog = "$logs\login.log"

Write-Output "Login Output:"

Run-EpmAutomate "login username@example.com $pwds $URL" $loginLog.

Get Substitution Variables

Retrieves the current month and year substitution variables.

# Get the Sub Var CurMth

$curMonthLog = "$logs\curmonth.log"

Write-Output "CurMth Output:"

Run-EpmAutomate "getSubstVar ALL name=CurMth" $curMonthLog

 

# Get the Sub Var CurYr

$curYearLog = "$logs\curyear.log"

Write-Output "CurYr Output:"

Run-EpmAutomate "getSubstVar ALL name=CurYr" $curYearLog

$curMonthLog and $curYearLog: These variables store the paths to the log files where the outputs of the commands will be saved.

Run-EpmAutomate "getSubstVar ALL name=CurMth" $curMonthLog: This command runs EPM Automate to get the substitution variable CurMth and saves the output to curmonth.log.

Run-EpmAutomate "getSubstVar ALL name=CurYr" $curYearLog: Similarly, this command gets the substitution variable CurYr and saves the output to curyear.log. 

Read and Output the Contents of the Logs for Debugging

# Read and debug the contents of the logs

Write-Output "Contents of $($curMonthLog):"

Get-Content -Path $curMonthLog

Write-Output "Contents of $($curYearLog):"

Get-Content -Path $curYearLog

These commands read the contents of the log files curmonth.log and curyear.log and output them for debugging purposes to verify that the correct data has been retrieved.

Extract the Current Month and Year from the Logs

# Extract the current month and year from the logs

$curMonth = (Get-Content -Path $curMonthLog | Select-String -Pattern "ALL\.CurMth=" | ForEach-Object { $_ -replace ".*ALL\.CurMth=", "" }).Trim()

$curYear = (Get-Content -Path $curYearLog | Select-String -Pattern "ALL\.CurYr=" | ForEach-Object { $_ -replace ".*ALL\.CurYr=", "" }).Trim()

  $curMonth: Reads the contents of curmonth.log, filters lines containing "ALL.CurMth=", removes everything up to "ALL.CurMth=" using a regular expression, and trims any whitespace.

  $curYear: Similarly, reads the contents of curyear.log, filters lines containing "ALL.CurYr=", removes everything up to "ALL.CurYr=", and trims any whitespace.

Output Extracted Values for Debugging

# Debug output to verify extraction

Write-Output "Extracted CurMth: $curMonth"

Write-Output "Extracted CurYr: $curYear"

 if (-not $curMonth) {

    Write-Error "Failed to extract current month."

    exit 1

}

 if (-not $curYear) {

    Write-Error "Failed to extract current year."

    exit 1

}

Outputs the extracted values of CurMth and CurYr to verify successful extraction.

If either variable is empty, it logs an error and exits the script.

Adjust the Year for Fiscal Year

# Adjust the year for fiscal year (FY24 = 2024 - 2000)

$curYearAdjusted = [int]$curYear.Substring(2, 2) + 2000 - 2000

# Format the adjusted year as a two-digit number

$curYearFormatted = $curYearAdjusted.ToString("00")

 Write-Output "Current Month: $curMonth"

Write-Output "Adjusted Year (FY24): $curYearFormatted"

$curYearAdjusted: Adjusts the extracted year to fiscal year format. For example, "24" becomes "2024".

$curYearFormatted: Formats the adjusted year as a two-digit number (e.g., "24").

Outputs the current month and adjusted year for verification.

Run Data Extraction Rule with the Adjusted Fiscal Year

# Run Data Extraction Rule with the adjusted fiscal year

$dataExtractionLog = "$logs\dataExtraction.log"

$periodName = "$curMonth-$curYearFormatted"

$integrationCommand = "runIntegration `"(DM12) ICPCMRPT to Snowflake Actual IC Calculations`" importMode=Replace exportMode=Replace periodName=`"{$periodName}`""

 Write-Output "Data Extraction Output:"

Run-EpmAutomate $integrationCommand $dataExtractionLog

• $dataExtractionLog: Path to the log file for the data extraction process.
• $periodName: Combines the current month and adjusted year into a period name string.
• $integrationCommand: Constructs the command to run the data extraction rule in EPM Automate with the specified period name and modes.
• Run-EpmAutomate $integrationCommand $dataExtractionLog: Executes the data extraction command and logs the output to dataExtraction.log.

This section of the script is responsible for retrieving the current month and year from Oracle EPM, adjusting the year to fit the fiscal year format, and running a data extraction rule with these parameters.

This part of the PowerShell script is responsible for verifying the existence of the output file from the data extraction process, downloading it, and checking for successful download. Here’s a step-by-step explanation:

List Files to Verify Existence

$listFilesLog = "$logs\listFiles.log"

Run-EpmAutomate "listfiles" $listFilesLog | Out-Null

• $listFilesLog: Path to the log file where the output of the listfiles command will be saved.
• Run-EpmAutomate "listfiles" $listFilesLog: Runs the EPM Automate command listfiles to list all available files in the EPM outbox and redirects the output to listFiles.log.

Read and Parse the Log Contents

# Read the contents of the listFiles.log for diagnostics

$listFilesContent = Get-Content -Path $listFilesLog

# Parse the list of files to find the file that matches the pattern

$filePattern = "outbox/PCM-SF-PCM Outbound Data_*"

$fileToDownload = $listFilesContent | Select-String -Pattern $filePattern | ForEach-Object { $_.Line.Trim() } | Sort-Object -Property { [int]($_ -replace '\D', '') } | Select-Object -Last 1

 $listFilesContent: Reads the contents of listFiles.log and stores it in a variable.

 $filePattern: Defines the pattern to match the desired file name.

 $fileToDownload:

• Filters the list of files to find lines matching the pattern outbox/PCM-SF-PCM Outbound Data_*.
• Trims each line to remove leading/trailing whitespace.
• Sorts the matched lines numerically based on any digits in the file names.
• Selects the latest file (the one with the highest number) for download.

Check and Prepare for File Download

if ($fileToDownload) {

    Write-Output "Latest file found: $fileToDownload"

     # Encode the file name for URL compatibility

    $encodedFileToDownload = $fileToDownload -replace ' ', '%20'

    Write-Output "Encoded file name for download: $encodedFileToDownload"

     # Specify the download path

    $downloadFilePath = Join-Path -Path $datafiles -ChildPath ($fileToDownload -replace 'outbox/', '' -replace ' ', '')

    $downloadLog = "$logs\downloadfile.log"

    Write-Output "Download File Output:"

 if ($fileToDownload): Checks if a matching file was found.

  Write-Output "Latest file found: $fileToDownload": Outputs the name of the latest file found.

  $encodedFileToDownload: Encodes the file name to replace spaces with %20 for URL compatibility.

  Write-Output "Encoded file name for download: $encodedFileToDownload": Outputs the encoded file name.

  $downloadFilePath: Specifies the local path where the file will be downloaded, removing the outbox/ prefix and any spaces.

  $downloadLog: Path to the log file where the download output will be saved.

  Write-Output "Download File Output:": Outputs a message indicating the start of the download process.

Run the Download Command

    # Run the download command with the correct arguments

    Run-EpmAutomate "downloadfile `"$fileToDownload`" `"$downloadFilePath`"" $downloadLog

    # Check the download log for success

    $downloadLogContent = Get-Content -Path $downloadLog

    Write-Output $downloadLogContent

    if ($downloadLogContent -match "completed successfully" -or $downloadLogContent -match "downloaded to") {

        if (Test-Path -Path $downloadFilePath) {

            Write-Output "File downloaded successfully to $downloadFilePath"

  Run-EpmAutomate "downloadfile "$fileToDownload" "$downloadFilePath"" $downloadLog: Executes the EPM Automate downloadfile command to download the specified file to the local path, logging the output to downloadfile.log.

  $downloadLogContent: Reads the contents of downloadfile.log to check for success.

  Write-Output $downloadLogContent: Outputs the contents of the download log for verification.

  if ($downloadLogContent -match "completed successfully" -or $downloadLogContent -match "downloaded to"): Checks the log content for success indicators.

  if (Test-Path -Path $downloadFilePath): Verifies that the file exists at the specified local path.

  Write-Output "File downloaded successfully to $downloadFilePath": Outputs a success message if the file was downloaded correctly.

# Logout

$logoutLog = "$logs\logout.log"

Write-Output "Logout Output:"

Run-EpmAutomate "logout" $logoutLog

Run-EpmAutomate "logout" $logoutLog: This command runs the logout command using the Run-EpmAutomate function defined earlier in the script. The output of the command is redirected to the logout.log file.

• logout: The EPM Automate command used to log out from the Oracle EPM environment.
• $logoutLog: The path to the log file where the output of the logout command will be saved. 

Hope this was useful, Happy days on the Cloud.