Oracle Cloud ERP - Create an Integration user with adequate roles and Priviledges | Assign Required roles to an Integration User

Usecase : Here, we will create an Integration user and assign roles to Integration user in Oracle Fusion ERP cloud using security console.

Roles to add:

• Add Function Security Policy : 
• Name: Manage Webservices catalog Code: FND_MANAGE_CATALOG_SERVICE_PRIV : Privilege to Manage Web service Catalog.
• Add Role:
• Role1: Name: Integration Specialist Code:ORA_FND_INTEGRATION_SPECIALIST_JOB : it is a job role which inherits Oracle ERP Cloud roles but does not include any data access. Individual responsible for planning, coordinating, and supervising all activities related to the integration of enterprise information systems.
• Role 2: Name: SOA Operator Role , Code: SOAOperator: SOA application operator, for common operational tasks like start, stop  monitor, backups.
• Role3: Name: Attachments User , Code: AttachmentsUser: provides access to the Attachments Security which enables user to download log or output file using ERP Integration service.
• Role 4: Name: Employee , Code: ORA_PER_EMPLOYEE_ABSTRACT
• Role5: Name: SOA_ADMIN_ROLE, Code: SOA_ADMIN_ROLE : this is needed to access the SOA composer to register the CSF key.

Detailed steps with screenshots:

Login to ERP >> Home Navigation >> Tools >> Security console

Users >> Add User account

Provide user details >> Save and close

Roles >> Create Role

Provide Role name, code and Role category as " Common - Job Roles" >> Next

Add Function Security Policy >> 

Name: Manage WebService Catalog

Code: FND_MANAGE_CATALOG_SERVICE_PRIV

Add Role  >> Next

Role1: 

Name: Integration Specialist

Code: ORA_FND_INTEGRATION_SPECIALIST_JOB

Role 2:

Name: SOA Operator Role

Code: SOAOperator

Role 3 :

Name: Attachments User

Code: AttachmentsUser

Add User >>search username >> add user to role >> Save and Close

Users >> search the created user >> click kn the user >>Edit >> Add Role

Role 1:

Name: Employee

Code: ORA_PER_EMPLOYEE_ABSTRACT

Role 2: 

Name: SOA_ADMIN_ROLE

Code: SOA_ADMIN_ROLE

Save and close

Tools >> Scheduled Processes >> Scheduled New Process >> submit the job : Retrieve Latest LDAP Changes

Once the sync roles job succeeded, login the ERP with the created User and verify if scheduled processes and Reports and Analytics exist under Tools.

 

OIC - Design time audit logs | How to monitor design time audit logs

We can track or monitor the design time audits from monitoring dashboards. That means we can track who has activated, deactivated or locked the integrations etc. We can also download all the audit logs or report for the retention period.

Navigation:

OIC console >> Monitoring >> Integrations >> Dashboards >> Change the view to Design time Audit

All thr audit logs:

Left top corner download button to dwonalod all the rentention period logs.

We can also do the filter on the specifc integration audit changes.

ERP - Import AP Invoice using FBDI files from ERP scheduled Processes

In this post, I will show you how to import payable invoice(zip of invoice header and Line) from ERP scheduled Processes.

Highlevel steps:

1. Download the invoice file(.zip file which contains ApInvoicesInterface.csv and ApInvoiceLinesInterface.csv) from Oracle enterprise repository.
2. Upload the file to UCM and submit the process "Load Interface File for Import"
3. Onve the data loaded from the zip file  to the interface tables(AP_INVOICES_INTERFACE AND AP_INVOICE_LINES_INTERFACE), run the process "Import Payables Invoices" to load data to bases tables.

Follow the below detailed Steps:

Step1: Get the FBDI file from OER (Oracle Enterprise Repository)

https://docs.oracle.com/en/cloud/saas/financials/23a/oefbf/index.html

Download the XLSX template and  Generate the CSV files as .zip file.

Step2: Transfer the file to UCM and load the file to Interface tables:

Navigate to Tools >> Scheduled Processes

Step3: Run the payable import job to load the data from interface tables to Base tables.

Jobs got successfully completed and the invoice got generated. PAYABLE  import report reflecting the invoice created.

From Payable >> Invoice page  we can also check the created invoice:

Note :

We can also verify the interface table data by running below BI queries:

Select * from AP_INVOICES_INTERFACE order by creation_date desc

Select * from AP_INVOICE_LINES_INTERFACE order by creation_date desc

ERP - About Oracle Payable Invoice

An Invoice is an itemized list of goods shipped or service rendered, with an account of all costs. 

Oracle payables lets us to capture all the attributes of the real life invoice documents we receive from our suppliers. When we enter invoice in Payables , the invoice information is divided between the Invoice header and the Invoice Lines.

Invoice structures:

• Invoice Header: The invoice header defines the common information about the invoice: invoice number and date, supplier information, remittance information, and payment terms. Information specified at the invoice header level defaults down to the line level. We can override the header level information for individual lines, as required.
• Invoice Lines : The invoice lines define the details of the goods and services as well as the tax, freight, and miscellaneous charges invoiced by the supplier. There can be multiple invoice lines for each invoice header. The Lines tab of the Invoice Workbench captures all of the details for the invoice line necessary for accounting, as well as for cross-product integration with other Oracle E-Business Suite applications, such as Assets, Grants Accounting, Inventory, Projects, Purchasing, Property Manager, and Receivables.
• Item Lines: Item lines capture the details of the goods and services billed on your invoice.
• Freight and Miscellaneous Lines: Freight lines capture the details of your freight charges. Freight charges can be allocated to Item lines as required.

  Miscellaneous lines capture the details of other charges on your invoices such as installation or service. Like Freight lines, Miscellaneous lines can be allocated to Item lines.

• Tax lines : Payables integrates with Oracle E-Business Tax to automatically determine and calculate the applicable tax lines for your invoices. E-Business Tax uses your tax setup, plus fields on the invoice header and lines, known as tax drivers, to determine which taxes should be applied to the invoice, to calculate the tax using the appropriate tax rates, and to determine whether or not the tax is recoverable or non-recoverable. E-Business tax creates the necessary tax lines and distributions for the invoice and allocates tax. If your tax setup permits, you can update the tax lines or manually enter tax lines
• Invoice Types
• Standard
• Credit Memo
• Debit Memo
• Mixed
• Prepayment
• Expense Report
• Withholding Tax
• Retainage Release
• Distribution : Distribution details include invoice accounting details, the GL date, charge accounts, and project information. An invoice line can have one or more invoice distributions.

Reference:

https://docs.oracle.com/cd/E18727_01/doc.121/e12797/T295436T366808.htm

ERP - Fix for SQL query time out error in BI reports | How to increase SQL timeout for BI Reports?

The default limit set at BI server lebel for time out is 500 sec. How ever we can override this time out limit setting for individual reports at the data model level:

Solution steps:

Navigation >> Tools >> Reports & Analytics >> Btowse Catalog >> Open the data model from BI catalog >> Click on Properties tab:

Set the Query Time out to 14400 (4 hrs) - it will crease the time out for this specific report only.

Also we can enable SQL Pruning to "On" - SQL pruning enhances performances by fetching only the columns that are used in the report layout/template. Columns that are defined in the query but are not used in the report , are not fetched.