Oracle Fusion SaaS BIP Report: Combine SQL reports with user's manually uploaded files

Business Need

There are cases when Oracle Fusion SaaS user would like to invoke a BIP report, pass it user file as a parameter and use the data from the uploaded file together with regular SQL queries.
Example: perform validations for Excel list of items.

Solution Overview

Create a CSV file.
Utilize standard File Export/Import screen for CSV file upload.
Create a SQL based report data model.
Utilize report parameter SQL to show the user files he uploaded.
Fetch UCM file information from REVISIONS table and use UCM connection.
Use Group Links to define parsing processing sequence
Parse CSV file by using SQL connect by technique
Use the parsed data in SQL as any other table

Detailed Solution Explanation

Create a CSV file bu using Excel, make sure that the file extension is saved with .txt extension. This is required for UCM connection parsing.
Upload the file to UCM by using standard screen, select account relevant for the file content:
Create a data model for BIP Report
Define a LOV for fetching file names uploaded by current user:

The SQL shows files uploaded by current Fusion user, recently uploaded at the top. Came be extended with any requested logic.
```
select ddoctitle, did 
from revisions 
where ddocauthor = fnd_global.user_name and upper(ddoctype) = 'APPLICATION' 
order by dcreatedate desc
```

Create 3 data sets: G_REVS – based on the selected file, G_UCM – fetch UCM file content by document Id, G_PARSER – parse the UCM file by using SQL.
G_REVS and G_PARSER must be connected by Group Links:

G_REVS query:

select did, ddocname from revisions where did = :p_file_id

G_UCM definition:

G_PARSER query:

with rws as (select :DOCUMENT str from dual) 
select REGEXP_SUBSTR(column_value, '[^,]+', 1, 1) AS item,        
       REGEXP_SUBSTR(column_value, '[^,]+', 1, 2) AS qty,        
       REGEXP_SUBSTR(column_value, '[^,]+', 1, 3) AS desc 
from  (select trim(regexp_substr(str, '[^' || chr(10) || ']+', 1,level)) column_value       
       from rws       
       connect by level <= length(str) - length(replace(str, chr(10))) + 1      
       ) file_data

Group Link definition:

file_data can be now combined with any other table/view in SQL…
Create report layout as any other report
Limitations: the uploaded file size must be under 32k

There is also an option to use XML representation of Excel as Excel 2004 XML.
In this case the parser SQL is much simpler:

select q.* 
from
 XMLTABLE('//*:Worksheet[@*:Name="Sheet1"]/*:Table/*:Row'
      PASSING XMLTYPE(:DOCUMENT)
      COLUMNS 
                                 "ITEM" VARCHAR2(100) PATH '*:Cell[1]/*:Data',
                                 "QTY" VARCHAR2(100) PATH '*:Cell[2]/*:Data',
                                 "DESCR" VARCHAR2(100) PATH '*:Cell[3]/*:Data'
     ) q

Oracle Fusion SaaS BIP Report: Combine SQL reports with user’s manually uploaded files

Business Need

Solution Overview

Detailed Solution Explanation