OA - Learning

2023-08-16T21:43:00.000+10:00

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

2023-02-01T23:31:00.003+11:00

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

2023-02-01T23:20:00.002+11:00

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

2023-01-22T13:50:00.002+11:00

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

2023-01-22T13:43:00.003+11:00

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

2023-01-22T13:29:00.005+11:00

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

2023-01-15T00:11:00.002+11:00

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)

2023-01-09T23:35:00.005+11:00

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

2023-01-09T23:22:00.001+11:00

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

2023-01-08T22:18:00.000+11:00

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.

How to use Fast Formula in HCM Extract

2023-01-08T16:39:00.003+11:00

Requirement
Can we use Fast Formula in HCM Extrat to derive any Database calculation ?

Solution
Oralce provides rule type attribute in HCM extract. We can use this type to use fast formula driven attribute.

In order to access Fast formula in Extract attributes, use type = "Extract Rule"

If using GET_VALUE_SET method to cal value set ( only TABLE type value set are supported). Please ensure you pass value to ID field as well.

How to schedule a report with timestamp in filename

2022-12-31T14:12:00.005+11:00

Requirement
Schedule a report to deliver output file with timestamp in filename so it can be differentiated

Solution
Dynamic filename can be achieved using 2 ways -

1) User delivery option manually in schedule

In this method in delivery tab, select the delivery method and use below syntex in filename - BIPReport_%y%m%d%H24%M%S.txt

2) Define bursting and use SQL method of appending timestamp in filename parameter -

'BIPReport_'||to_char(SYSDATE, 'YYYYMMDDH24MISS'||'.txt' PARAMETER5

How to default Date value in parameter

2022-12-31T13:45:00.002+11:00

Requirement

Default dynamic date parameter values

Solution

Use following predefined function to default date para,eters -

{$SYSDATE()$} – current date

{$FIRST_DAY_OF_MONTH()$} – first day of the current month

{$LAST_DAY_OF_MONTH()$} – last day of the current month

{$FIRST_DAY_OF_YEAR)$} – first day of the current year

{$LAST_DAY_OF_YEAR)$} – last day of the current year

*Dates are system date of the server on which BI Publisher is running.

Example

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

2022-12-31T13:34:00.000+11:00

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

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

2022-12-31T00:13:00.000+11:00

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

JAR Files

2021-02-10T22:16:00.003+11:00

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

2021-02-10T22:07:00.001+11:00

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

2020-06-25T00:13:00.001+10:00

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

2020-06-25T00:03:00.001+10:00

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.

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

2020-04-24T22:51:00.001+10:00

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

2020-04-24T22:40:00.000+10:00

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

2020-04-24T22:22:00.002+10:00

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

2020-04-24T11:58:00.004+10:00

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;

Unix Command to send Email

2020-04-07T22:41:00.001+10:00

uuencode <ATTACHMENT_FILE> <ATTACHMENT_FILE> | mailx -s "Subject" <TO_EMAIL> -c <CC_EMAIL>

mutt -s "SUBJECT" <TO_EMAIL> -b <CC_EMAIL> < <ATTACHMENT_FILE>

Sample Code
-------------------------------------------------------------------------------------
CONTENT="$XXX_TOP/bin/sample.txt"
SUBJECT="Email TEST"
EMAIL_ADDRESS="Sandeep19rm@gmail.com"
CC_ADDRESS="Sandeep19rm@gmail.com"
ATTACHMENT="-a $FILE_HEADER -a $FILE_DETAIL"

mutt -s "$SUBJECT" $EMAIL_ADDRESS -b $CC_EMAIL < $CONTENT $ATTACHMENT

-------------------------------------------------------------------------------------

(echo "Hi,

Please find the attached log file for review.

Thanks & Regards

OA-Learning";

uuencode test.zip test.zip) | mailx -s "TEST Result" 'Sandeep19rm@gmail.com'

-------------------------------------------------------------------------------------

HCM Extract Migration

2020-04-07T22:25:00.001+10:00

HCM Extract provides export and import options. For any new development work, migration approach should be export/import.

Export Extract Definition
My Workforce >> Data Exchange >> Manage Extract/ Extract Definition >> Search Extract and Click export icon ( 3rd one having up arrow key symbol)

This will download the XML version of Extract definition.

Import Extract Definition

My Workforce >> Data Exchange >> Manage Extract/ Extract Definition >> Click on Import Icon ( opposite of export and available at top left corner , beside +)

It will prompt to Give Extract Name and file Import.

Limitation - Extract Name must be unique during Import. This is why this method doesn't really helpful if change is in existing extract.

It also doesn't allow to delete extract if it did ran in past. So you can't delete and reuse same name.

For Operational Support, all changes to existing extract need to be done manually.