How to add new attribute in Seeded Data Model - Simple Reports

Requirement 
Enhance a simple seeded report by adding additional attributes is very common. 

Solution1
We can enhance the seeded data set by modifying SQL statement using new attributes. 

Example
DataSet SQL -
SELECT
invoice_num
, invoice_id
, invoice_date
FROM
ap_invoices_all 

Need to display Business Unit Name. 

Modified Data Set SQL 

SELECT
invoice_num
, invoice_id
, invoice_date
,fbu.bu_name
FROM
ap_invoices_all apa
,fun_all_business_units_v fbu
WHERE
apa.org_id = fbu.bu_id

OR 

SELECT
invoice_num
, invoice_id
, invoice_date
,(SELECT bu_name from fun_all_business_units_v where bu_id = apa.org_id) bu_name
FROM
ap_invoices_all apa

Solution2
We can add a new data set for requested attributes and common attribute from existing data set. Then link the new data set using common attribute. 

DataSet1 SQL -
SELECT
invoice_num
, invoice_id
, invoice_date
,org_id
FROM
ap_invoices_all 

Need to display Business Unit Name. 

DataSet2 SQL 

SELECT
bu_id
,bu_name 
FROM
fun_all_business_units_v 

Linke DataSet 1 with DataSet2 using attribute Org_id (DS1) and Bu_id (DS2).

 

Profile Utilities:Diagnostics

 Utilities:Diagnostics to enable Access to  custom code under HELP > Diagnostics

JAR Files

Create JAR File 

jar cvf  <jar file name> <class name>....

Example 

jar cvf  TEST.jar A.class B.class

Execute Jar file 

java -cp ./<jar file name> <class name> <properties file>

Example 

java -cp ./SKPDebugREST.jar DebugREST DebugREST.properties

CURL

Open Command Prompt 

SKP>curl -help 

This shows curl utility is installed 

This shows utility is missing and need to install. 

Download and install - Win64OpenSSL-1_1_1i.exe

Oracle Fusion URLs Tips

Oracle Fusion Login URL 

https://<POD_NAME>.login.<DOMAIN>.oraclecloud.com

https://san-test.login.ap1.oraclecloud.com/

Oracle Fusion Application URL

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com

https://san-test.fa.ap1.oraclecloud.com/

Oracle Fusion UCM URL 

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com/cs

https://san-test.fa.ap1.oraclecloud.com/cs

Oracle Transnational BI Publisher  URL 

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com/analytics

https://san-test.fa.ap1.oraclecloud.com/analytics

Oracle Fusion BI Publisher Enterprise URL 

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com/xmlpserver

https://san-test.fa.ap1.oraclecloud.com/xmlpserver

Oracle Business Intelligence Cloud Connector Console  

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com/biacm

https://san-test.fa.ap1.oraclecloud.com/xmlpserver

How to backup and migrate OTBI objects in Fusion Cloud

Following  approaches one can use to promote OTBI objects from one instance to another. 

1) Manual :  Old school method. Manually follow same instruction as development. This is beneficial if change is simple only in layout or in one data set and you are unsure about other changes in development instance. 

2) Archieve/Unarchieve : 

On catalog screen we can see Archive and Unarchive options. 

Select the object and click archive for export OR

Click on More and select archive option. 

*.Catalog file will be created. 

Login to target instance and use unarchive option and select catalog file.

3) Upload/Download :

On catalog screen another option available is upload/download.

if download option is not available, open XMLPSERVER

<POD_URL>/xmlpserver

 Example :  https://san-test.fa.ap1.oraclecloud.com/xmlpserver

This will open BI Publisher Enterprise ( Old BI Publisher feature - Only BIP Data Model and Report are available). 

*.xdoz (Report)  & *.xdmz (Data Model) file will be downloaded. 

Oracle WFR scans comma as decimal in LineItem fields

This can not be resolved by using field properties ( Exclude Char) feature since Line Item is of Table type and there is no such properties.

Custom Sub Routine would be way forward, following is a sample -

Private Sub LineItems_ValidateTable(pTable As SCBCdrPROJLib.ISCBCdrTable, pWorkdoc As SCBCdrPROJLib.ISCBCdrWorkdoc, pValid As Boolean)
'======================================
'By Sandeep
'======================================
Dim i As Integer

For i = 0 To pWorkdoc.Fields("LineItems").Table(0).RowCount -1

'Replacing some known characters identified during debug
pWorkdoc.Fields("LineItems").Table(0).CellText("Unit Price", i)=Trim(Replace(CStr(pWorkdoc.Fields("LineItems").Table(0).CellText("Unit Price", i)),".",""))

pWorkdoc.Fields("LineItems").Table(0).CellText("Total", i)= Trim(Replace(CStr(pWorkdoc.Fields("LineItems").Table(0).CellText("Total", i)),".",""))

pWorkdoc.Fields("LineItems").Table(0).CellText("Total", i)= Trim(Replace(CStr(pWorkdoc.Fields("LineItems").Table(0).CellText("Total", i)),".",""))

Next

End Sub


Above is a sample code to replace any char and assign the replaced value back to line Items.
Based on business required we can change the values and assign the calculated values agaain.

If Total field has comma replaced by decimal,  form a logic to check length of post decimal string and decide if decimal is actual decimal or comma. Accordingly replace comma with "" and assign back. 

Oracle WFR : Basics

WFR - Oracle Web Center Form Recognition 

Open Project file (.sdp) in Form Recognition.

Definition Mode

• This will be used to define attributes properties 
• Select user exit and press F12 ( right click >> Show Scripts) to show available userExits 
• Select invoice Oracle F12 ( right click >> Show Scripts) to show sub routines. Select Objects and available related  procedure
• Make the required changes and Save project 
• After successful extraction ( from Run mode) user can  switch to Definition mode > invoice > fields to see each attribute extracted values 

Run Mode 

• Select a File 
• Select Run mode
• Click Extract next record 
• Extraction will be completed 
• Extraction results are displayed 
• Successfully extracted and passed validation will be ticked 
• Crossed attributes are failed some validations  

Verifier Mode ( Text)  

• This will be used to show extraction results for each attributes 
• Move over the mouse on red colored fields and it will show the failed validation 

Verifier Mode ( Design)  can be used to design the layout. 

** UserExits and SubRoutines are written in VB language 

Script to Change FND User Password from DB

DECLARE
  lv_user_name    VARCHAR2(100):= 'SANDEEP';
  lv_owner        VARCHAR2(10) := 'CUST';
  lv_password     VARCHAR2(20) := 'Oracle123';
 ln_user_id         NUMBER;
BEGIN
  /*Create FND User*/
   BEGIN
                          SELECT user_id
                          INTO ln_user_id
                          FROM fnd_user
                          WHERE user_name=lv_user_name;
    EXCEPTION
                        WHEN no_data_found THEN
                        dbms_output.put_line('Did not create user('||lv_user_name||') : '||sqlerrm);
    END;

    fnd_user_pkg.updateuser
                                    ( x_user_name                                                =>lv_user_name,
                                      x_owner                                                        =>lv_owner,
                                      x_unencrypted_password                             =>lv_password
                        );
 
    IF ln_user_id IS NOT NULL THEN
      dbms_output.put_line('Successfully Updated  user password  : '||lv_user_name);
      dbms_output.put_line('user id = '||ln_user_id);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error in  Create user :'||lv_user_name||'>>'||SQLERRM);
END;

Manage OAF Personalization from DB

Utility Name      JDR_UTILS 

Method Names  

• listcustomizations       : Parameter is Document Path ( Full path of Page, CO or VO)
• printdocument             : Parameter , Output of List Customization 
• deletedocument           : Parameter (P_document   => Output of List Customization ) 

Begin

jdr_utils.listcustomizations('/oracle/apps/per/selfservice/termination/webui/TerminationPG');

END;

Begin

jdr_utils.printdocument('/oracle/apps/per/selfservice/termination/webui/customizations/function/FUNCTIONNAME/TerminationPG');

END;

Begin

jdr_utils.deletedocument(p_document => '/oracle/apps/per/selfservice/termination/webui/customizations/function/FUNCTIONNAME/TerminationPG');

END;