X

The Visual Builder Cloud Service Blog

Oracle ERP Extension with Visual Builder Cloud Service

John Ceccarelli
Product Manager

In this tutorial we're going to create a an extension to Oracle ERP that serves as a staging area for invoices and allows you to apply discounts to the line items on the invoice. Once the discount has been approved, there will be an action to create the invoice in ERP. 

The app is based on Visual Builder Cloud Service business objects. Certain data, like customer and product info, is replicated from ERP into the VBCS business objects. In a real production setup, you would use Integration Cloud to schedule a data extract from ERP and into the VBCS on a regular basis.

Here's what the data model looks like:

Setting up the Project

  1. Import this file to your computer.
  2. In the VBCS home page, click Import > Import from File, and upload the ZIP there.

Adding the List of Invoices

  1. Drag a table component into main-start.
  2. In the Quickstarts tab of the Property Inspector, click Add Data.
  3. Select Business Objects > Invoice and click Next.
  4. Select the following fields and click Next:
    • BillToCustomerNumber
    • BusinessUnit
    • totalAmount
    • discount
    • discountedAmount
  5. Click Finish.
  6. Drag a Currency component onto the Discounted Amount column. The number changes from a regular number to a currency. Do the same for Total Amount.
  7. Drag a Percentage component onto the Discount column. Your finished table should look like this:

Adding the Create Page

By the end of this chapter, you will have:

  • Added a Create page to your list of invoices
  • Added an action chain on the Customer drop-down to populate the rest of the customer information
  • Hard-coded the default value of fields like Transaction Type and others.

Let's get started!

  1. Select the table in the Page Designer. Click the Quickstarts tab of the Property Inspector and click Add Create Page.
  2. Select Business Objects > Invoice and click Next.
  3. Select the following fields:
    • customer (automatically added)
    • BillToCustomer
    • BusinessUnit
    • InternalCurrencyCode
    • PaymentTerms
    • TransactionDate
    • TransactionSource
    • TransactionType
    • status
  4. Change the Button Label to Create.
  5. Click Finish.
  6. Command-click the Create button to open the Create page in the Page Designer.
  7. Delete the Status field.
  8. Reorder the remaining fields so they look like this:
  9. Select the containing form-component and in the Property Inspector set Max Columns to 2.
  10. Set the following components to be read-only by selecting them and clicking Readonly in the Property Inspector:
    • Bill To Customer
    • Business Unit
    • Internal Currency Code
    • Payment Terms
    • Transaction Source
    • Transaction Type
  11. Select the Customer drop-down. In the Property Inspector, click the Events tab. Then click New Event > Quick Start: "value"
  12. Drag a Call REST Endpoint into the action chain. Click Select Endpoint and choose Business Objects > Customer > GET /Customer/{Customer_Id}
  13. In the Property Inspector, click Input Parameters > Customer_Id.
  14. Map value to Customer_Id.
  15. Drag an Assign Variables action to the success path below the Call REST Endpoint action.
  16. Click Assign in the right panel to open the mapper dialog.
  17. Map the fields from the response body of the REST call to your invoice page variable as shown below:
    • businessUnit -> BusinessUnit
    • customerNumber -> BillToCustomerNumber
    • invoiceCurrencyCode -> InternalCurrencyCode
  18. Click Save and close the editor tab for the Action Chain.
  19. With main-create-invoice tab open in the Page Designer, click the Variables button in the sidebar.
  20. Expand {} invoice and set the default values for the following fields:
    • TransactionSource -> Manual
    • TransactionType -> Invoice
    • status -> Requested
    • PaymentTerms -> Net 15
  21. Use the Run button to preview the app and to create a new record.

Adding an Edit Page

Our create page just fills in the general overview information of the invoice. Now we will create an edit page that allows us to enter line items, set the discount, and recalculate the total based on the discount and line items.

  1. Go back to main-start and select the table in the Page Designer. In the Quickstarts tab of the Property Inspector, click Add Edit Page.
  2. Select Invoice for the Read Endpoint and click Next. Then select Invoice for the Update Endpoint and click Next.
  3. On the final page of the wizard, select the following fields for inclusion:
    • BillToCustomerNumber
    • BusinessUnit
    • InternalCurrencyCode
    • PaymentTerms
    • TransactionDate
    • TransactionSource
    • TransactionType
    • customer
    • discount
    • discountedAmount
    • totalAmount
  4. Rename the button label Edit. Click Finish.
  5. Open the Edit Page with Live Mode by selecting Live in the upper-right of the Page Designer, then selecting a row in the table and clicking the Edit Invoice button. This opens up the Edit page with the context of a row to edit. If no context is available, you'll get an error message in the Page Designer. Any time this happens, simply switch back to the main-start page, go into Live Mode, then open a record to edit. Don't forget to switch the Page Designer back to Design Mode once you've opened the Edit Page.
  6. Rearrange the fields so they look like this:
  7. Select the Form Layout (select anywhere around the fields in the Edit Page or open up the Structure View and click the Form Layout component there). In the Property Inspector, set Max Columns to 3 and Label Edge to top. 
  8. Set all of the fields except Customer, Transaction Date, and Discount to be read-only.
  9. Set up the Customer drop-down to populate the customer fields like you did in the Create page. (If you are short on time you can skip this part.) Select the Customer drop-down. In the Property Inspector, click the Events tab. Then click New Event > Quick Start: "value"
  10. Drag a Call REST Endpoint into the action chain. Click Select Endpoint and choose Business Objects > Customer > GET /Customer/{Customer_Id}
  11. In the Property Inspector, click Input Parameters > Customer_Id.
  12. Map value to Customer_Id.
  13. Drag an Assign Variables action to the success path below the Call REST Endpoint action.
  14. Map the fields from the response body of the REST call to your invoice page variable as shown below:
    • businessUnit -> BusinessUnit
    • customerNumber -> BillToCustomerNumber
    • invoiceCurrencyCode -> InternalCurrencyCode
  15. Click Save and close the editor tab for the Action Chain.
  16. Select Total Amount and in the Property Inspector set the converter to $ and the Currency Code to USD.
  17. Your page should now look like this:
  18. Now let's update Discounted Amount so that it changes when we update the discount. Select Discounted Amount and in the Property Inspector click the Data tab. Set the value to:
    {{ $variables.invoice && $variables.invoice.discount !== undefined ? $variables.invoice.totalAmount * (1-$variables.invoice.discount) : $variables.invoice.totalAmount }}
  19. The basic information is now set up. However, if you run the page and try to save it, you'll get an error. This is because the app is trying to send all of the parameters that it fetched in the GET back to the PATCH endpoint, including two read-only fields (discountedAmount and totalAmount). These fields are read-only because we set them in the data model to be calculated from formulas/aggregations. We need to create a new payload variable that will store all of the read-write properties from the page and use that. 
  20. Click the Variables tab in the left sidebar of the Page Designer. Click the Types tab > + Type > From Endpoint. Select Invoice > PATCH /Invoice/{Invoice_Id}. Click Next.
  21. Name the type updatePayload and add the following fields from the Request node (not the Response node):
    • BillToCustomerNumber
    • BusinessUnit
    • InternalCurrencyCode
    • PaymentTerms
    • TransactionDate
    • TransactionSource
    • TransactionType
    • discount
    • customer
    • status
  22. Click Finish.
  23. Click the Actions tab in the left sidebar of the Page Designer.
  24. Click the SaveInvoiceChain action chain.
  25. Click the Variables tab in the left sidebar of the Action Chain Editor. Click + Variable and add a variable called payload of type updatePayload.
  26. Click the Action Chain tab in the left sidebar of the Action Chain Editor. Drag an Assign Variables action directly below the Start action.
  27. Click Variables -> Assign in the right sidebar of the Action Chain Designer.
  28. Map Page > Invoice to Action Chain > payload.
  29. Click Save. 
  30. Scroll down and select the Call REST Endpoint action. In the right sidebar, click Parameters > body. Delete the existing mapping and map Action Chain > payload to Parameters > body.
  31. Click Save.

Adding the Line Item Child Table

Now it's time for adding the UI to manage the line items for the invoices. Note that in the data model, each line item has a mandatory reference field to its parent invoice. Also, each line item has a lineTotal field which we will calculate based on the unit price, quantity, and discount.

In this chapter you will:

  • Add a table to the Edit Page and bind it to ReceivablesLineInvoice
  • Set a filter on the table to only show line items for the current invoice
  • Add a column with a line total calculated on the fly as the discount changes
  • Add a Create page for the line item (in the interest of time we won't do an Edit page)
  • Default the invoice reference in the newly created line item to point to the current invoice

So let's get started!

  1. Open the Edit page in the Page Designer.
  2. Drag a Heading component between the Form Component and the Toolbar containing the Save and Cancel buttons.
  3. In the Property Inspector, set the Text to Line Items and the Level to H3.
  4. Drag a Table component below the Heading.
  5. In the Quickstarts tab of the Property Inspector, click Add Data.
  6. Select ReceivablesInvoiceLine in the wizard. (Hover over each node to see the full name of the business object.) Click Next.
  7. Add the following fields:
    • Description
    • Quantity
    • UnitSellingPrice
    • lineTotal
  8. Click Next.
  9. In the Define Query page, expand filterCriterion > criteria > item[0].
  10. Select attribute and type invoice in the Expression field. Make sure it's set to Static Content and not Expression.
  11. Select op and type $eq in the Expression field. 
  12. Map Page > InvoiceId to the value field of the filterCriterion.
  13. Click Finish.
  14. Notice the Unit Selling Price and Line Total were entered as numbers and not currency. Drag a Currency component into each of the columns to change them to display as currencies. 
  15. Now let's add another column which will automatically update based on the discount amount. Drag a Text component to the slot at the end of the table.
  16. In the Property Inspector with the Text component selected, change the value to 
    {{ ($variables.invoice.discount !== undefined) ? (($current.row.Quantity * $current.row.UnitSellingPrice) * (1 - $variables.invoice.discount)) : ($current.row.Quantity * $current.row.UnitSellingPrice) }}
  17. Now let's fix the column header. In the Page Designer, select the table. In the Property Inspector, click Data and change the cellTemplate to Discounted Total.
  18. Now let's add the create page for the invoice. In the Page Designer, select the table and go to the Quickstarts tab of the Property Inspector. Click Add Create Page.
  19. Leave ReceivablesInvoiceLine selected as the endpoint and click Next.
  20. Add the following fields:
    • Description
    • LineNumber
    • Quantity
    • TaxClassificationCode
    • UnitSellingPrice
    • invoice
    • lineTotal
    • product
  21. Change the Button Label to Create.
  22. Click Finish.
  23. Open the Create page by switching to Live Mode and clicking the Create button that you just added.
  24. Delete the Description, Tax Classification Code, and Invoice fields, as we will default these.
  25. Rearrange the fields so your form looks like this:
  26. Make Unit Selling Price and Line Total read-only.
  27. Now let's update the fields in the page based on which product is selected, like we did with the Customer field on the main Invoice pages. Select the Product field, go to the Events tab of the Property Inspector, and click + New Event > Quick Start: value.
  28. Drag a Call REST Endpoint under the Start node. Click Select Endpoint in the right sidebar and choose Product > GET /Product/{Product_Id}. Click Select. 
  29. In the right sidebar, click Input Parameters > Product_Id. Map Action Chain > value to Parameters > Product_Id. Click Save.
  30. Drag an Assign Variables action into the success path. In the right sidebar, click Variables > Assign. Map description and unitSellingPrice from the REST response body to receivablesInvoiceLine > Description and receivablesInvoiceLine > UnitSellingPrice.
  31. Click Save and close the Action Chain editor tab.
  32. Back in the Line Item Create Page, select the Variables tab on the left and select receivablesInvoiceLine > TaxClassificationCode. Set the default value to FR VAT STD DEF RATE AP.
  33. Switch back to the Page Designer. Select Unit Selling Price, and in the Property Inspector, switch the Converter to $ and set the Currency Code to USD. 
  34. Do the same for Line Total.
  35. With Line Total still selected, click the Data tab of the Property Inspector and change the Value field to:
    {{ ($variables.receivablesInvoiceLine.Quantity && $variables.receivablesInvoiceLine.UnitSellingPrice) ? $variables.receivablesInvoiceLine.Quantity * $variables.receivablesInvoiceLine.UnitSellingPrice : 0 }}
  36. Now we need to change the Save action chain to calculate the lineTotal before sending the payload to the PUT endpoint. Select the Actions tab on the left and click createReceivablesInvoiceLineChain.
  37. Drag an Assign Variables action below the Start action.
  38. In the right sidebar, click Variables > Assign. Select Page > receivablesInvoiceLine > lineTotal and change the value to:
    $page.variables.receivablesInvoiceLine.Quantity * $page.variables.receivablesInvoiceLine.UnitSellingPrice
    Make sure that it's still set as an Expression and doesn't change to Static Content.
  39. Click Save.
  40. One last step! We need to pass in the Invoice Number when we navigate to the Create Line Item page and default the invoice reference to this value. Go back to the main-create-invoice-line page and click the Variables tab on the left. Click + Variable and add a String called InvoiceId. In the Property Inspector, make the string as a required input parameter that's passed on the URL.
  41. Go back to the main-edit-invoice page. Click the Actions tab in the left of the page and click navigateToCreateReceivablesInvoiceLineChain. Select the Navigate action and in the Property Inspector, click InvoiceId.
  42. Map Page > InvoiceId to Parameters > InvoiceId. Click Save.
  43. Go back to the main-create-receivables-invoice-line page and click the Variables tab on the left. Expand receivablesInvoiceLine and select invoice. Set the Default Value to {{$page.variables.InvoiceId}}.

Adding a Custom Component

Now let's add a custom Oracle JET Web Component to display a timeline of invoices. 

  1. Download this component to your computer.
  2. Open main-start in the editor. In the list of components, scroll down to the bottom and click the + sign in the Custom category. Drag the ZIP file into the dialog and click Import. The component gets added to the Palette.
  3. Update your SDP definition to include the fields that the custom component requires. Go to the Variables tab for main-start, click Types, and click Edit from Endpoint for getallInvoiceResponse. Select the status and TransactionDate fields (and leave all of the previously selected fields selected) and click Finish.
  4. Drag the component onto the page.
  5. In the Property Inspector for the my-timeline component, enter {{ $variables.invoiceListSDP }} for the items property.

Building the ERP Integration

So we've got our staging area for our invoices and for calculating our discounts all ready. Now we need to create an integration into ERP so we can create the invoices in ERP cloud. We will create a service connection to the POST receivablesInvoice REST API then use a custom JS function to prepare the payload.

  1. In the main application navigator, click Service Connections.
  2. Click + Service Connection. If your VB instance already has a registered SaaS API Catalog, then use the Select from Catalog functionality and skip to step 5 below. If not, select Define by Specification.
  3. Enter the following information:
    • API Type: ADF Describe
    • Service Specification: Web Address -  https://ERP_Base_URL/fscmRestApi/resources/11.13.18.05/receivablesInvoices/describe
    • Service ID: invoices
    • Authentication: Basic
    • Username: CAROLINE.VALENCE (or some user that can call this service)
    • Password: your password
  4. Click Next.
  5. Select receivablesInvoices > POST /receivablesInvoices and click Finish.
  6. Open main-edit-invoice and click the JS tab in the left sidebar.
  7. Enter the following code in bold in the JavaScript.
      var PageModule = function PageModule() {};
    
      PageModule.prototype.getCreateInvoicePayload = function(invoice, invoiceLines) {
      var invoiceLineNumber = 1;
      return {
          BillToCustomerNumber : invoice.BillToCustomerNumber,
          BusinessUnit : invoice.BusinessUnit,
          InvoiceCurrencyCode : invoice.InternalCurrencyCode,
          PaymentTerms : invoice.PaymentTerms,
          TransactionDate : invoice.TransactionDate,
          TransactionSource : invoice.TransactionSource,
          TransactionType : invoice.TransactionType,
          receivablesInvoiceLines : invoiceLines.map(i => { return {
                  Description : i.Description,
                  LineNumber : invoiceLineNumber++,
                  Quantity : i.Quantity,
                  UnitSellingPrice : i.UnitSellingPrice * (1-invoice.discount),
                  TaxClassificationCode : i.TaxClassificationCode
              }})
          };
      }  
      
      return PageModule;
  8. Go back to the Page Designer tab and drag a new Button component next to the Save and Cancel buttons at the bottom of the page. Change the display text to Submit.
  9. Switch to the Events tab of the Property Inspector and click + New Event > Quick Start: 'ojAction'
  10. Drag a Call REST Endpoint action to the chain. Select Endpoint and select Business Objects > ReceivableInvoiceLine > GET /ReceivableInvoiceLine and click Select.
  11. With the Call REST Endpoint action selected, click Parameters > requestTransformOptions in the right sidebar.
  12. Expand Parameters > requestTransformOptions > filter > criteria > item. Set the following values:
    • attribute: invoice (Static Content)
    • operator: $eq (Static Content)
    • value: $page.variables.invoiceId (Expression)
  13. Drag a Call Module Function action to the success path of the Call REST Endpoint. Click Select Module Function, select the getCreateInvoicePayload function, and click Select.
  14. Click Input Parameters > Assign and map Page > invoice to Parameters > invoice and Action Chain > Results > callRestEndpoint1 > body > items to Parameters > invoiceLines. Click Save.
  15. Drag a Call REST Endpoint action below the Call Module Function action.
  16. Click Select Endpoint and select the POST /receivablesInvoices endpoint.
  17. Click Parameters > body and map the output of the getCreateInvoicePayload function to the body. Click Save.
  18. Drag a Fire Notification action to the success path of the Call REST Endpoint action.
  19. Set the Summary to "Invoice created!", Notification Type to confirmation, and Display Mode to transient.
  20. Drag a Fire Notification action to the failure path of the Call REST Endpoint action. Set the summary to "Something went wrong!" and leave the rest of the fields at their defaults.
  21. Drag a Navigate action below the success Fire Notification action. Click Select target > Root Page > shell. This will navigate back home.
  22. Test out your app. You did it!

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha