| Unable to create formula Column for the select [message #604129] |
Tue, 24 December 2013 06:43  |
 |
faisalhaleem
Messages: 4
Registered: December 2013
|
Junior Member |
|
|
Hi All,
I have a select statement which runs fine in report builder but when I register the report in Oracle apps and run it then it ends with error can any one help me. The select is as below.
select round( (select CONVERSION_RATE
from gl_daily_rates
where FROM_CURRENCY=fc.CURRENCY_CODE
and TO_CURRENCY='USD'
and to_char(CONVERSION_DATE,'DD-MON-YYYY')=to_char(to_date(:END_DATE,'yyyy-mm-dd')-1,'DD-MON-YYYY'))*
sum(round(xxhr_sa_payroll_all.calculate_Provision(pap.BUSINESS_GROUP_ID,paa.assignment_id)
*GET_LEAVE_BALANCE_YTD (pap.BUSINESS_GROUP_ID, paa.assignment_id,nvl((select max(ASSIGNMENT_ACTION_ID) from PAY_ASSIGNMENT_ACTIONS where ASSIGNMENT_ID=paa.ASSIGNMENT_ID ),0),pap.PERSON_ID,paa.PAYROLL_ID,to_date (:START_DATE,'RRRR/MM/DD hh24:mi:ss'), to_date(:END_DATE,'RRRR/MM/DD hh24:mi:ss'),'Annual Leave Accrual Plan'),2)) ,2)SUM_USD
from per_all_people_f pap, per_all_assignments_f paa, FND_CURRENCIES_vl fc
where 1=1
and paa.PERSON_ID=pap.PERSON_ID
and paa.EFFECTIVE_END_DATE >sysdate
and pap.EFFECTIVE_END_DATE >sysdate
and paa.LOCATION_ID =:LOCATION_ID
and paa.business_group_id=:P_BUSINESS_GROUP_ID
and pap.PER_INFORMATION_CATEGORY=fc.ISSUING_TERRITORY_CODE
group by fc.CURRENCY_CODE;
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
