Query to get PPM Project Manager name in Oracle Fusion

 Hi,

In this post lets see how to fetch Project Manager name of a given project in Oracle Fusion

List of Tables used:

PJF_PROJECTS_ALL_VL

PJF_PROJECT_PARTIES

PJF_PROJ_ROLE_TYPES_TL

PER_PERSON_NAMES_F_V

Query:

Select

ppnfv.full_name as Project_manager

from

PJF_PROJECTS_ALL_VL ppav

PJF_PROJECT_PARTIES ppp

PJF_PROJ_ROLE_TYPES_TL pprtl

PER_PERSON_NAMES_F_V ppnfv

where 1=1

AND ppav.project_id = ppp.project_id

AND ppp.RESOURCE_SOURCE_ID=ppnfv1.person_id

AND ppnfv1.name_type='GLOBAL'

AND ppp.project_role_id = pprtl.project_role_id

AND pprtl.PROJECT_ROLE_NAME ='Project Manager'

Error while customizing reports in Oracle Fusion

 Hi

In this post lets see one of the common issue while Customizing standard reports in Oracle Fusion.

Issue: 

You don't have enough privilege to customize report. Please check the permission for the custom folders.

Solution:

Whenever this occurs , go to "custom folder" and check "permissions"

Select "Apply permissions to sub-folders" and "Apply permissions to items within folder" and "ok".

Query for AR Sales Representative Name in Oracle Fusion

 Hi,

This post is for fetching Sales person name in AR Transactions.

Query:

Select hp.Party_name

from

RA_CUSTOMER_TRX_ALL rcta,

JTF_RS_SALESREPS JRS,

HZ_PARTIES hp

where

rcta.PRIMARY_RESOURCE_SALESREP_ID=JRS.RESOURCE_SALESREP_ID 

and JRS.RESOURCE_ID=hp.PARTY_ID

Row Totals in OTBI Report

 Hello Everyone,

In this post let us see how to add row wise totals in OTBI reports.

Step1: Open an existing Analysis report and go to results page

Step2: Hover over a number / measure column/ any column where you can see this highlighted one

Step3: Right click on it, until you see this pop-up

Step4: Click on "Show Row Level Grand Total"

and select "After Values". By default, it will None.

Step4: Now check the output to see Grand Totals Row at the end.

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