Edit a Financial Project Plan

In this use case, you'll edit a financial project plan using the Oracle Visual Builder Add-in for Excel tool. The Excel workbook is configured with the Financial Project Plans REST API using which it interacts with the application. You first configure the Excel workbook with the Oracle Visual Builder Add-in and download data, and then perform these tasks:

• Perform create, update, or delete operations on any business object
• Perform custom actions
• Perform bulk update of descriptive flex fields
• Configure List of Values

Configure Excel Workbook With Oracle Visual Builder Add-in and Download Data

Based on your requirements, you can configure the Excel worksheet with the Oracle Visual Builder Add-in. This use case covers a few configurations that you can do in the Excel workbook. You can also perform other configurations like removing a mandatory check on certain fields, displaying only the selected fields on the layout, and so on.

After you configure the Excel workbook, you can use it as is or publish it for data entry or to perform other operations such as invoking custom actions, updating descriptive flex fields, and so on.

1. Install the Oracle Visual Builder Add-in for Excel.
2. Open the Excel workbook with the Oracle Visual Builder Add-in installed.
3. In the Oracle Visual Builder tab of the Excel workbook, click Designer.
4. In the New Layout Setup dialog box, enter the service description of the Financial Project Plans REST API as follows, and click OK.

   https://<hostname>/fscmRestApi/resources/11.13.18.05/financialProjectPlans/describe

5. Log in to the application.

6. Select financialProjectPlans in the Choose a Business Object dialog box and click OK. Then, select Table Layout in the New Layout setup dialog box and click OK.

   A new table appears in your Excel workbook. Column headers are shown, with a placeholder data row below the headers. You'll also see the Layout Designer on the right.

7. Add a new worksheet, click Designer, select the ResourceAssignments business object and click OK. Then, select Table Layout in the New Layout setup dialog box and click OK

   A hierarchy of dependent layouts is created in the Excel Workbook as follows: financialProjectPlans > ResourceAssignments > PlanningAmounts > PlanningAmountDetails.

   Note:

   Repeat this step for the PlanningAmounts and PlanningAmountDetails business objects.
8. Assign a parent layout to each child layout.
   1. In the Layout Designer, click the Edit icon next to Parent Layout.
   2. Click Yes.

   Note:

   The add-in automatically detects the previous worksheet as its parent layout.
9. Optionally, you can add parent columns that are displayed along with the child columns.
   1. In the Layout Designer, click the + icon next to Parent Columns.
   2. Enter the column name in the Filter field on the Available Business Object Fields dialog box and click OK. For example, in the ResourceAssignments worksheet, enter ProjectNumber as the parent column so that you know for which project you are adding resource assignments.
10. Optionally, you can configure search options based on your requirements.
    1. In the Layout Designer, click Query.
    2. Click the Edit icon next to Search.
    3. Enter the field to query in the Filter field on the Available Business Object Fields dialog box. For example, enter ProjectNumber in the Filter field.
    4. Select a value from the comparator list, enter the search text, and click OK in the Search dialog box. For example, select startswith from the comparator list and enter DCM_Corp to search for projects with DCM_Corp as the starting text in the project number.

    The configured search options are used when you click Download Data in the Oracle Visual Builder tab.

11. After you configured the Excel workbook as per your requirements, click Redraw Layout to see your changes reflected in the worksheet.
12. Click Download Data.

13. Because you set up a search query to limit data downloads, you'll be prompted to enter a value for the query. Enter the search options and click OK.

    The data is downloaded into the various worksheets as per your configuration. You'll also see the Status on the right.

Perform Create, Update, or Delete Operations

You can perform create, update, or delete operations on any business object as supported by the underlying REST API.

1. Perform the following operations in the Resource Assignments worksheet:
   1. Create a resource assignment. Click Table Row Changes, select Insert Rows, and enter the values for the various parameters as appropriate.
   2. Update the planning dates in a resource assignment. Select the row and edit the planning dates as appropriate.
   3. Delete a resource assignment. Select the row, click Table Row Changes and select Mark for Delete.

   The status of the selected row is displayed in the Status section and in the Change column. That is, a create, update, or delete message appears in the Change column for each row where you made a change.

2. Click Upload Changes.

   When prompted with a message that the table has pending deletions, click Yes to confirm. After the upload completes, the table refreshes the Status section to describe the change for each modified row that was uploaded.

   Note:

   All data is validated before it is uploaded to the REST API. If you run into errors, you can always fix the errors and upload your changes again. If you have partial errors, for example, if create and update rows were successful, but delete rows failed, you'll only need to fix data for the failed delete row.

Perform Custom Actions

After invoking custom actions, you must refresh the layout manually by downloading the data.

1. In the Layout Designer, click Columns.
2. Click the Manage Columns icon.
3. In the Table Column Manager dialog box, click the Custom Actions tab, and select the fields that you want to enter during runtime. Select the Refresh Only Conversion Rates, Refresh Rates Period Forward, Adjustment Type, From Period, To Period, Adjustment Percentage, and Task Number check boxes, and click Done.
4. Click Redraw Layout.
5. Click Download Data.
6. Because you set up a search query to limit data downloads, you'll be prompted to enter a value for the query. Enter the search options and click OK.

   The data is downloaded into the worksheet. The workbook is configured for custom actions.

7. Invoke custom actions, as follows:
   1. Select the row, click Table Row Changes, and select Mark for Action.
   2. Select the Refresh Rates custom action in the Choose an action to perform on the selected rows dialog box and click OK.
   3. Enter the values in the editable columns as appropriate and click Upload Changes.

      Only the Refresh Rates custom action related columns are editable. After the upload completes, the status is displayed in the Status section.

Perform Bulk Update of Descriptive Flex Fields

You can update the descriptive flex fields in bulk.

1. In the Layout Designer, click Columns.
2. Click the Manage Columns icon.
3. In the Table Column Manager dialog box, select the Context Segment check box, and click Done.
4. Click Redraw Layout.
5. Click Download Data.
6. Because you set up a search query to limit data downloads, you'll be prompted to enter a value for the query. Enter the search options and click OK.

   The data is downloaded into the worksheets. You can view the descriptive flex fields related fields in the worksheet.

You can edit these descriptive flex fields or add context segments as appropriate. If you make any changes, you must click Upload Changes to push the changes to the application, and then click Download Data to download them in the worksheet.

Configure List of Values

You can associate a list of values with a business object field. Here's how you configure the list of values for Tasks using the projectTasksLOV resource.

1. In the Layout Designer, click the Edit icon next to ResourceAssigments under Business Object.
2. Click the Fields tab in the Business Object Editor dialog box.
3. Select TaskName and click Done..
4. Click the List of Values tab in the Business Object Field Editor dialog box. Add or edit the list of values for the business object field as appropriate.
   • Select the Enabled check box to enable the list of values functionality for the TaskName field.
   • Click the Edit icon next to Referenced Business Object and select projectTasksLOV. The referenced business object provides the display values for the corresponding identity values.
   • Click the Edit icon next to Identity Field and select TaskName. The identity field is used to look up the display values for the identity values in the current field.
   • Click the + icon next to Display Fields and add the TaskName and TaskNumber fields. These fields are populated from the referenced business object and are shown instead of the identity fields if the field is used in a layout.
   • Define filters using name-value query parameters to filter the list of values for these fields. Select q as the parameter name and ProjectID=<project_ID> as the parameter value.
5. Click Done.