Extracting Notes and Attachments from Cells in Oracle Cloud EPM Using Groovy!

Lets explore on how to extract cell notes and attachments from Oracle Cloud EPM using Groovy scripting. 

This blog walks through retrieving Entity and Cost Center descendants, querying financial data, and identifying cells with notes or file attachments.

The script dynamically iterates through each combination, loads a data grid, and logs key findings after finding the cell Notes and Attachment if any. 

Suppression rules ensure efficient execution, while error handling prevents failures. Ideal for auditing financial data and ensuring compliance. 

CAUTION NOTE : Works well for a small set of data, not an ideal solution for large database. 

What The Script Does?

• Retrieves all descendant entities and cost centers.
• Queries financial data for each combination.
• Extracts notes and attachments from financial data.
• Logs important findings for review.
• Handles errors gracefully without stopping execution.

Retrieve All Entity Descendants & All Cost Center Descendants:

Finds all descendants of Total Entity using the IDescendants() function.

The method getEvaluatedMembers(expression, cube) dynamically retrieves members based on the cube’s structure.

Example output for Entity: If Total Entity has two main sub-entities

entityDescendants = ["Entity_A", "Entity_B", "Entity_C", ...]

Example output for Cost Center :

costCenterDescendants = ["CC_1001", "CC_1002", "CC_1003", ...]

Iterating Through Entities and Cost Centers

Loop Over Each Entity

Iterates over each entity retrieved earlier & Logs the entity being processed.

Loop Over Each Cost Center

Iterates through each cost center for the current entity. This results in Entity × Cost Center combinations being processed.

Querying the Data for the Current Entity and Cost Center

 setPov(...) → Defines static members for the query.

• Scenario: 'Forecast'
• Version: 'Working'
• Year: 'FY25'
• Other metadata selections like 'FY25', 'EIC_999', etc.

 addColumn(...) → Defines dynamic columns for the query.

• Includes entity and costCenter, allowing data retrieval per combination.

 addRow('ILvl0Descendants(Net Income)') → Retrieves all level-0 accounts under "Net Income".

 Suppression Settings → Ensure missing or invalid data doesn't break execution.

 Supported suppression methods - 

Processing the Retrieved Data

 The query results are stored in a grid object.

  dataCellIterator() iterates through all data cells retrieved.

  If a cell is not empty, it checks for:

• Cell Notes (comments left on a cell)
• Attachments (files uploaded in the form)

  If notes or attachments exist, they are logged for review.

 This prevents one bad entity or cost center from stopping the entire execution.

  If an error occurs, the script logs the issue and continues processing other members.

Confirms the  script has successfully iterated through all entity-cost center combinations.

The complete script is here. 

Logs from the rule here, 

The Rule logs the pov where the Cell Note and Attachment is found. 

Hope this was useful, happy days in the Cloud!!!