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.

PL/SQL - Large objects

VARCHAR2 can store max 4000 bytes data then what next? LOB's or Large object comes into to overcome the Varchar2 limit.

LOB's:

• LOB's or large objects help to store large data.
• It can also store unstructured data such as video, audio, photo images, etc within the database.

LOB's are catwgorized into 2 types:

• Intetnal LOB's
• External LOB's

Internal LOB's:

Internal LOBs are stored withon the database as columns in a table and participate the transaction mode of the server.

• ClOB:  A character LOB, used to store single byte character data. Max size: 8TB to 128 TB.
• BLOB: A binary LOB, used to store binary  raw data. Max size: 8 TB to 128 TB.
• NCLOB: a LOB used to store multi byte character data. Max size: 8TB to 128 TB.

External LOB's:

External LOB's are stored outside of the database as operating system files, only a reference to the actual OS file is stored in the db. External LOBs do not participate in transactions.

BFILE:

BFILE refers to Binary file. The BFILE LOB holds reference to large binary data stored as physical files in the OS outside the db.

Max size: 4 GB

Max size of file name: 255 characters.

Max size of a directory name: 30 characters.

LOB's Restrictions:

• LOBs cant be a part of a clustered table.
• LOBs cant be used in the following part of a SQL statement.
• GROUP BY
• ORDER BY
• SELECT DISTINCT
• JOINS
• AGGREGATE functions
• LOBs cant be analyzed using the Analyze cmd 
• LOBs cant be included in a partitioned index organized table
• LOBs cant be used in VARRAYs.

LOB Locator:

• Every LOB is associated with a lob locator.
• A lob locator is a pointet to the actual location of the LOB value.
• The locator associated with internal LOBs is called a LOB locator while the locator associated with external LOBs os called a BFILE locator.
• When storing data in a LOB column, we are also storing a LOB locator with it.
• This LOB locator is what is returned to us when we select the LOB column.
• The actual LOB valur can then be retrieved using this locator.

Character Large Object (CLOB):

Create a table job_resumes

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

resume clob

);

INSERT INTO JOB_RESUMES values(1, 'Sri' 'Das', ' resume details....');

We can use varius SQL functions on CLOBs. We should use normal functions only on relatively small CLOBs, upto about 100K in size. When we are querying larger LOBs, we sould use the DBMS_LOB package functions for better performances:

Select substr(reume, 1, 30) from job_resumes;

Select DBMS_LOB.substr(reume, 1, 30) from job_resumes;

Select length(resume), dbms_lob.getlength(resume) from job_reumes;

Binary Large Object(BLOB):

• CLOBs can store only character data but BLOBs can store any type of data.
• They are used to hold non-traditional data such as videos, voice, images, pdf, documents etc.
• Even we can insert character data into a BLOB , oracle will store it but it will be in raw data. That means we cant use SQL functions like substr to handle BLOB data.

Create a table job_resumes1

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

resume blob

);

Storing images/videos in BLOBs:

Create a table job_resumes2

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

Profile_picture blob

);

CREATE DIRECTORY MYIMAGES AS 'C:\MYIMAGES';

DECLARE

SRC BFILE := BFILENAME('MYIMAGES','SRI.JPG,');

DEST BLOB;

BEGIN

INSERT INTO JOB_RESUMES2 VALUES(1, 'SRI' ,'M', EMPTY_BLOB()) RETURNING PROFILE_PICTURE INTO DEST;

Dbms_lob.open(src,dbms_lob.lob_readonly);

Dbms_lob.loadfromfile(dest,src,dbms_lob.getlength(src));

Dbms_lob.close(src);

Commit;

End;

/

Binary File(BFILE):

BFile refers to Binary file. This BFILE LOB holds references to large binary data stored as physical files in the OS outside db. They do not participate in the transactions.

Create a table job_resumes3

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

Profile_picture BFILE

);

INSERT INTO JOB_RESUMES values(1, 'Sri' 'Das', BFILENAME('MYIMAGES','SRI.JPG'));