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.




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