How to default report data for logged in user ? 

This is applicble for reports having personal information for users. 

Report can be parameterized as below - 

 

:XDO_USER_NAME captures session username. 

 select username from per_users where upper(username) = upper(:xdo_user_name)

Execute BI Publisher Report from URL

Construct url using following variables in order to invoke a BIP report 

<InstanceDetails>/xmlpserver/<FolderPath>/<ReportName.xdo>?_xpt=0&_xmode=4&<Paramaters=ParamValue)

Example: 

InstanceDetails = https://fa-skp-test.fa.oraclecloud.com

FolderPath = /Custom/XXX

ReportName= XX Custom Report.xdo ( Replace spaces with '+') 

Paramaters = 

P_TYPE
S - Summary
D - Detailed 

P_BU
0 = AU Region
1 = NZ Region

 

URL to invoke and run this report would be - 

https://fa-skp-test.fa.oraclecloud.com/xmlpserver/Custom/XXX/XX+Custom+Report.xdo?_xpt=0&_xmode=4&P_TYPE=S&P_BU=0

SQL Script to find list of tables updated today

DECLARE 
    sql_st VARCHAR2(1000);
    no_of_records NUMBER;
BEGIN 
    dbms_output.put_line ('Table|No of Records Changed');
    dbms_output.put_line ('------|----------------------------------');
    FOR C1 in (SELECT table_name 
FROM all_tables a 
WHERE  table_name not lIKE '%$%' 
AND table_name not lIKE '%_A' 
AND exists (SELECT 1 
FROM all_tab_columns b 
WHERE a.table_name = b.table_name 
AND b.column_name = 'LAST_UPDATE_DATE'
)
order by table_name) 
    LOOP 
    no_of_records := 0;
    sql_st := 'Select count(1) from '|| c1.table_name ||' where last_update_date > TRUNC(SYSDATE)';
    BEGIN
            EXECUTE IMMEDIATE sql_st INTO no_of_records;
            dbms_output.put_line (c1.table_name||'|'||no_of_records);
    EXCEPTION 
        WHEN OTHERS THEN 
        dbms_output.put_line (c1.table_name ||'|'|| SQLERRM);
    END;
END Loop;
EXCEPTION 
WHEN OTHERS THEN 
    dbms_output.put_line ('Exception : '||SQLERRM);
END;

Script to allow user to view workflow submitted for all

 

SELECT Text from wf_resources  WHERE Name = 'WF_ADMIN_ROLE'

If above SQL returns * , you can see all workflow submitted by others in Workflow Admin > Status Monitor Area.

If this is restricted for only your user, use below update statement to allow for all. 

UPDATE wf_resources set text='*' where name='WF_ADMIN_ROLE'

Get Charge Account Description in Fusion

methodName
GET_CHARGE_ACCOUNT_DESC

packageName
POR_UTIL_PVT
GL_FLEXFIELDS_PKG

SELECT 

POR_UTIL_PVT.GET_CHARGE_ACCOUNT_DESC('GL','GL#',chart_of_accounts_id,CODE_COMBINATION_ID)

FROM  DUAL 

SQL to Get Departnment Hierarcy in Single line in Fusion

SELECT
h_child.organization_id
,LISTAGG(haou.name , '|') WITHIN GROUP (Order by distance) DeptHierarchy
FROM 
per_dept_tree_node_rf    pgtn_rf
,hr_all_organization_units haou
,hr_all_organization_units h_child
WHERE
pgtn_rf.ancestor_pk1_value = haou.organization_id
and pgtn_rf.pk1_value = h_child.organization_id  
and pgtn_rf.tree_code  ='XXXX_DEPT_STRUCTURE'
and NVL(:P_START_DATE,trunc(SYSDATE))  between haou.effective_start_date and haou.effective_end_date
and EXISTS(
SELECT 1
FROM
FND_TREE_VERSION ftv
WHERE
ftv.tree_code = pgtn_rf.tree_code
AND pgtn_rf.tree_version_id = ftv.tree_version_id
AND ftv.status = 'ACTIVE'
AND SYSDATE  between TRUNC(ftv.effective_start_date) AND TRUNC(ftv.effective_end_date)
)

No Data shown in Financial Tax Register Report after completion

Financial Tax register populate data on following tables. 

ZX_REP_CONTEXT_T
ZX_REP_TRX_DETAIL_T
ZX_REP_ACTG_EXT_T
ZX_REP_TRX_JX_EXT_T

Which can be referred using view ZX_REP_EXTRACT_V

Report can be run using parameter SUBMIT_PURGE, which is to delete data post completion. 

Purging Routine -  ZX_EXTRACT_PKG.purge(p_request_id)

This routine is defined to DELETE transaction for given REQUEST ID when profile  FND: Debug Log Enabled  is set No. 

If Post completion there is no data in above view/tables, please check if Program is subimtted with SUBMIT PURGE parameter ? 

To Validate if report is generating data or not, set the profile value to YES and run the program. This will not purge the tables. 

Refer metalink note for more detail on Financial tax Register - 

How to Use and Troubleshoot the Financial Tax Register in Release 12 E-Business Tax (Doc ID 1073468.1)

Shortcut to run BIP report in Fusion (Sandbox)

 Requirement 
How to access Oracle BIP Report from fusion without accessing Tools >  Reports and Analytics and ESS job. 

Solution
We can use sandbox to setup a shorcut in required screen in Oracle fusion. 

Create a sandbox using Structure option.

 

 

Select create page entry option. 

Select the group name where you want to keep this shortcut. 

EL expression can be used to control access for specifc user or role 

Select Link type = Dynamic URL and provide the report path and name ( as shown) in destination for web application. 

Save and publish the sandbox. 

Go to path and a shortcut will be shown on selected group. 

Different methods to access BIP Report in Oracle Fusion

Requirement 
What are different ways one can access BIP Reports 

Solution 
Reports can be accessed via - 

1) Reports and Analytics - Go to report path and run or schedule report 

2) Oracle Fusion Scheduled Process - Setup and ESS job for BIP report and access report data by running ESS job from Oracle fusion. 

3) Oracle Fusion (any screen) -  We can use sandbox to create new page entry and link this with report path. A BIP report can be linked using Static and Dynamic options. 

Static - This will be not instance dependent and report URL will be full link including instance details. 
Dynamic -  Suggested to use as this need only report path and name. Instance name will be automatically added based on used instance. 

Working with Bursting in Oracle Fusion

Requirement 
How to define delivery details for an BIP report at run time ? 

Solution
Similar to e-Business BIP reports are delivered to use bursting concept for run time delivery selection. 

Bursting SQL need to be defined at Data Model. 

Following are the options to run data model and bursting scope - 

• Data Model - Data model can be executed directly from data model by using view button.
  Bursting does not execute in this.
  
  
• Report (Online View) - Report can be run using Open option in Oracle analytics. Bursting  defined at associated Data model does not execute in this. 
  
  
• Report submitted as Job - Report can be run using (More > Schedule) option in Oracle analytics. Bursting  defined at associated Data model may/may not execute in this. In order to execute Report ned to be updated by selecting properties option and enable bursting need to be checked if bursting is defined at linked data model. 

 

• ESS Job - If ESS job is setup in Oracle fusion and linked with BIP report. Any bursting defined at associated data model will be executed when report run using ESS job.