Home » Developer & Programmer » Reports & Discoverer » Unable to create formula Column for the select (Oracle Report 10 G)
Unable to create formula Column for the select [message #604129] Tue, 24 December 2013 06:43 Go to next message
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;
Re: Unable to create formula Column for the select [message #604155 is a reply to message #604129] Wed, 25 December 2013 05:03 Go to previous messageGo to next message
Littlefoot
Messages: 21647
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

... it ends with error

Which error?
Re: Unable to create formula Column for the select [message #604157 is a reply to message #604155] Wed, 25 December 2013 05:52 Go to previous messageGo to next message
faisalhaleem
Messages: 4
Registered: December 2013
Junior Member
Actually this and condition below does not work
and to_char(CONVERSION_DATE,'DD-MON-YYYY')=to_char(to_date(:END_DATE,'yyyy-mm-dd')-1,'DD-MON-YYYY')

if I change this condition and take sysdate instead of :END_DATE then it works but when I keep it as the parameter it does not work.

I want to get the conversion rate based on the date passed from the parameter.
Re: Unable to create formula Column for the select [message #604158 is a reply to message #604157] Wed, 25 December 2013 06:26 Go to previous messageGo to next message
Littlefoot
Messages: 21647
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that END_DATE datatype causes problems. Check which one is it - CHARACTER (as your code suggests, as you are TO_DATE it) or DATE. Then, without performing any calculations, display END_DATE value in the report so that you'd see in which format it comes into the report. Then you'd know which format mask to apply.

Although I thought that DATE parameters are easier to handle, from my own experience I *know* that CHARACTERs are to be used. Therefore, try to pass a CHARACTER value for END_DATE from your application to this report, pay attention to its format mask and apply the same in report. Hopefully, you'll make it work.
Re: Unable to create formula Column for the select [message #604206 is a reply to message #604158] Thu, 26 December 2013 05:06 Go to previous message
faisalhaleem
Messages: 4
Registered: December 2013
Junior Member
Thanks Littlefoot the idea worked when I printed the END_DATE and saw what mask it was converted the conversion date to same format.
Thanks a ton
Previous Topic: Barcode Print In Oracle Reports
Next Topic: Bar Graph - Grouped/Stacked
Goto Forum:
  


Current Time: Sun Jan 17 03:57:08 CST 2021