Oracle Fusion

Freeze headers issue in FRS Reports

Hi Everyone,

In this post let us see an issue encountered while freezing headers in FRS Reports in Oracle Fusion.

Issue: While freezing headers in FRS, we can see output in HTML size changes.

Sample screenshots of Issue:

Screenshot of the report output before freezing headers

Now let us use "Freeze Headers" option in the FRS report.

Freeze Grid Headers option will be present under "Grid Properties" as highlighted below. Check the option.

Now let us see the output of the report one more time

This is how report output changes with this option.

Steps to avoid this issue:

1. Go to File > Page Setup

Page Setup pop appears

Now go to "Workspace Size" tab

Check the option "Use a Custom Workspace Size" and make Width and height to 50

Click "ok"

2. your report will be in Grid1 , select Report1

After selecting Report1

Select the Grid and drag it to the required size by checking the output in between

Checking the output

Now you can see the difference, so expand Grid Vertically and Horizontally as you require.

Query for Inventory Cycle Count

Hi Everyone,

Following query lists out data for Inventory Cycle count data particularly "Approve Count Sequences" page.

Main Tables:

INV_CYCLE_COUNT_HEADERS

INV_CYCLE_COUNT_ITEMS

INV_CYCLE_COUNT_ENTRIES

Query:

select

CYCLE_COUNT_HEADER_NAME,

icce.COUNT_LIST_SEQUENCE Count_Sequence,

esi.ITEM_NUMBER Item,

esit.DESCRIPTION Item_Description,

fl.MEANING count_Sequence_status,

icce.COUNT_QUANTITY,

icce.COUNT_UOM,

icce.ADJUSTMENT_QUANTITY Primary_adjustment_quantity,

icce.REVISION,

icce.LOT_NUMBER,

icce.SERIAL_NUMBER,

icce.COUNTRY_OF_ORIGIN_CODE,

iac.ABC_CLASS_NAME,

icce.COUNT_DATE,

itl.SEGMENT1 locator_name,

icce.SUBINVENTORY,

icce.ITEM_UNIT_COST,

iop.ORGANIZATION_name,

ppat.name project_name,

ptv.TASK_NUMBER,

icce.COMMENTS

from

INV_CYCLE_COUNT_HEADERS icch,

INV_CYCLE_COUNT_ITEMS icci,

INV_ABC_CLASSES iac,

EGP_SYSTEM_ITEMS_B esi,

EGP_SYSTEM_ITEMS_TL esit,

INV_CYCLE_COUNT_ENTRIES icce,

inv_item_locations itl,

fnd_lookups fl,

PJF_PROJECTS_ALL_TL ppat,

PJF_TASKS_V ptv,

INV_ORGANIZATION_DEFINITIONS_V iop

where 1=1

and icch.CYCLE_COUNT_HEADER_ID=icci.CYCLE_COUNT_HEADER_ID

and icci.ABC_CLASS_ID=iac.ABC_CLASS_ID

and icci.INVENTORY_ITEM_ID=esi.INVENTORY_ITEM_ID

and icci.ORGANIZATION_ID=esi.ORGANIZATION_ID

and icci.INVENTORY_ITEM_ID=esit.INVENTORY_ITEM_ID

and icci.ORGANIZATION_ID=esit.ORGANIZATION_ID(+)

and esit.LANGUAGE=userenv('lang')

and icch.CYCLE_COUNT_HEADER_ID=icce.CYCLE_COUNT_HEADER_ID

and icci.INVENTORY_ITEM_ID=icce.INVENTORY_ITEM_ID

and icci.ORGANIZATION_ID=icce.ORGANIZATION_ID

and icce.LOCATOR_ID=itl.INVENTORY_LOCATION_ID

and icce.ENTRY_STATUS_CODE =fl.LOOKUP_CODE

and fl.LOOKUP_TYPE = UPPER('INV_CC_ENTRY_STATUSES')

and icch.ORGANIZATION_ID=iop.ORGANIZATION_ID

and icce.PROJECT_ID=ppat.PROJECT_ID(+)

and icce.TASK_ID=ptv.TASK_ID(+)

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

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..