Useful RTF Functions

 Hello Everyone,

Following are the useful RTF functions:

Function Name	Example	Description
Substring	<?xdofx:substr(Field,Starting Position, length(Field))?>	Fetches part of a string from starting position to the length of the string
Sheet name for Excel output	<?spreadsheet-sheet-name:DesiredSheetName?>	Provides static name for the sheet in Excel output
For Loop	<?for-each:Groupname?>  <?end for-each?>	For loop in rtf
If loop	<?if:Condition?> Example: <?if:Field!=0?>  <?end if?>	If loop in rtf
Formatting Numbers	<?format-number:Field;'999G999D99'?>	1000 separator for numbers
Formatting Dates	<?format-date:Field;'Format'?> Example: <?format-date:Field;'XDODEFDATE'?>  -prints date in the format dd/mm/yyyy
Subtotals	<?sum(current-group()/Field)?>	Use this inside a loop to get the subtotal
Parent tag/ field from different group	<?ancestor-or-self::*/Field?>	To fetch parent tags or parameter or any other fields
Formatting Currency	<?format-currency:Field;//Currencycodefield?> Example: <?format-currency:Field;//'USD'?>
Page Break	<?split-by-page-break:?>
Value of	<xsl:value-of select=Field/>	Can be used with xsl if or choose
Choose	<xsl:choose> <xsl:when test="condition"> Field or data to be displayed when condition is satisfied </xsl:when> <xsl:otherwise> Field or data to be displayed when no conditions are satisfied </xsl:otherwise> </xsl:choose>	Same as switch case in C language

Checking query for OTBI / Analysis report

 Hi

Let us quickly check how to query for Subject area report or OTBI Or Analysis report.

After creating subject area report, please use following steps to find the query

Step 1: Go to your login in Reports and Analytics page

Step 2: Click on "Administration"

Step 3: Click on "Manage Sessions"

Step 4: In the Manage Sessions page, 

check records with your user login.

if you are finding multiple records, click on "Close All cursors". Now got to your subject area report and click on Results. Now refresh this Manage Sessions to see only active records.

Click on "View Log" 

For more confirmation i.e.,  whether your checking accurate report. Check columns of the subject area report in Statement Column

Step 5: Similar page appears after clicking on View Log.

Search for "With"

After searching for "With" clause

Step 5: From starting of "With" clause  to rownum<=75000 is the query of the Analysis.

Query for Purchase Order Line Attachment

 

Hi,

Please use the following query to fetch purchase order lines attachment

SELECT 

FDV.*

FROM 

FND_ATTACHED_DOCUMENTS FAD,

FND_DOCUMENTS_VL         FDV,

PO_LINES_ALL                 PLA,

PO_VERSIONS                                             PV

WHERE 

FAD.ENTITY_NAME =       'PO_LINES'

AND FAD.DOCUMENT_ID =       FDV.DOCUMENT_ID

AND PLA.PO_LINE_ID =       FAD.PK1_VALUE

AND FAD.CATEGORY_NAME           =                   'TO_SUPPLIER'

AND PK2_VALUE                                =                    PV.VERSION_ID

AND PLA.PO_HEADER_ID                =                    PV.PO_HEADER_ID

 AND NVL(PV.CO_NUM,0)                 =                    NVL(:PARAM_CO_NUM, 0)

How to fetch Attachment content in BI Report in Oracle Fusion

 Hello Everyone,

In this post let's us see how to fetch attachment content in BI Reports from UCM server

Let us consider fetching Attachment details of AP Invoices

Step1: In data model, Click on "New Data Set"

Select "SQL Query"

Step 2: Use the following query and click "ok"

select 

fdv.* 

from

fnd_attached_documents fad,

fnd_documents_vl       fdv,

AP_INVOICES_ALL        ap_invoices

where

             fad.document_id = fdv.document_id 

AND    fad.entity_name         = 'AP_INVOICES_ALL'

AND    fad.pk1_value                            = ap_invoices.invoice_id

The above query fetches attached document related details like id, version..

Step 3: Fetching document data from UCM server

Click on "New Data Set" and select "Content Server"

Step 4: Enter following data:

Name                : Specify data set name

Data Source     :  let it be "FA_UCM_PROVISIONED"

Parent Group   :  Choose the other data set group , for example I have choose G_1 which contains query from step 2

Document ID  :  Choose DM_VERSION_NUMBER

Content Type  : Text

Click "ok"

Step 5: This is how it looks

Goto results and check data, document content can be seen in the column "Document"

 

Multi Sheet Excel output using RTF Template

 Hello Everyone,

Today let us see how to place multi sheets in Excel.

In the rtf document, enter required data to be shown in the First Sheet

Now in the Microsoft word, goto Layout tab

Select Breaks 

Select Page

This will place your cursor in a new page which will be like a new sheet in the excel.

Enter Second sheet info now

Insert your xml and run it. Output Format: Excel

Checking output

Hope this works..

How to show Parameter along with its description but link data set with only parameter in Fusion

 Hi

In this post let us see how to link parameters and BIP report data set when we want to show both field and description in parameter but in data set only field is present.

Go to your datamodel and edit LOV

Sample Query: Select field||'-'||description, field from table1

This query shows the corresponding field along with description in parameter selection part but internally it returns field to the data set.

Example: P_Item: select item_number||'-'||item_description, item_number from items_table

How it is shown in parameters

Item Number : Item1-Test Item

Internally value passed will be "Item1"

Hope this helps...

Some steps that can be followed while creating or customizing a role in Fusion

 Hello Everyone,

Let us see some tips that can be useful while creating or customizing a role in Oracle Fusion.

1. Before creating a custom role, search if any seeded/ Oracle provided role is close to the one you require, if you find any such go with customizing i.e., copying of seeded role and work on it. If not starting creating a new role.

            Recommending to find a close role since there will be many data security policies associated with role and underlying privileges which will be difficult to find and add if working from scratch.

2. Role name can be changed at any point of time but role code cannot be changed once we save, so make sure of the role code before doing final submit.

3. Generally we don't need to touch data security policies unless the requirement is very specific. So make sure before deleting any data security policy.

4. Unnecessary Function Security Policies can be deleted.

5. In Function Security Policies, we can see few privileges are inherited other role. These privileges can be removed only if inherited roles are removed.

6. While working on Role hierarchy tab, make sure to not remove _obi roles if corresponding role is used since these might be related to running corresponding report outputs

7. Similarly do not delete any roles with _hcm and _crm if corresponding roles are used in Role hierarchy