Query to fetch COA Account Fields

 Hi All,

A short post to get COA Account Segment related fields.

select 

fvvv.value,

FVVV.DESCRIPTION,

fvvv.ENABLED_FLAG  Enabled,

fvvv.START_DATE_ACTIVE Start_Date,

fvvv.END_DATE_ACTIVE End_Date,

fvvv.Sort_Order,

fvvv.SUMMARY_FLAG Summary,

fvvv.FLEX_VALUE_ATTRIBUTE1 Allow_Posting,

fvvv.FLEX_VALUE_ATTRIBUTE2 Allow_Budgeting,

gl.Meaning Account_type

from 

fnd_vs_value_sets fvvs

,FND_VS_VALUES_VL fvvv

,gl_lookups gl

where 

fvvs.VALUE_SET_CODE=valueset_name --pass required valueset name

and fvvs.VALUE_SET_ID=fvvv.VALUE_SET_ID

and fvvv.FLEX_VALUE_ATTRIBUTE3 =gl.lookup_code

and gl.LOOKUP_TYPE='ACCOUNT TYPE'

Query to fetch AR Receipts Application reference — Oracle Fusion

 Select

acra.RECEIPT_NUMBER TRX_NUMBER,
HZ.PARTY_NAME CUSTOMER_NAME,
(Select listagg(apsa1.trx_number,’;’) within group (order by RECEIVABLE_APPLICATION_ID)
from 
ar_payment_schedules_all apsa1,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA

where
araa.APPLIED_PAYMENT_SCHEDULE_ID=apsa1.PAYMENT_SCHEDULE_ID
and araa.cash_receipt_id=acra.cash_receipt_id
and araa.display=’Y’
) Application_Reference

from
AR_CASH_RECEIPTS_ALL Acra,
AR_PAYMENT_SCHEDULES_ALL apsa ,
HZ_CUST_ACCOUNTS hca,
HZ_PARTIES HZ

where
acra.CASH_RECEIPT_ID = apsa.CASH_RECEIPT_ID(+)
and acra.PAY_FROM_CUSTOMER=hca.cust_account_id
and hca.party_id=hz.party_id

Query for PPM Project Status #PPM #Projects

 Hi,

In this post let's see how to fetch Project Status in Oracle Fusion.

Tables used:

PJF_PROJECTS_ALL_VL

PJF_PROJECT_STATUSES_TL

Query:

Select PPST.PROJECT_STATUS_NAME

from

PJF_PROJECTS_ALL_VL PPAV

PJF_PROJECT_STATUSES_TL PPST

where 1=1

AND PPST.LANGUAGE=USERENV('LANG')

AND PPA.PROJECT_STATUS_CODE=PPST.PROJECT_STATUS_CODE

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.