Home » Applications » Oracle Fusion Apps & E-Business Suite » running a SQL*PLUS report through oracle apps. concurrent managers (merged 2) 11.5.10.2
running a SQL*PLUS report through oracle apps. concurrent managers (merged 2) 11.5.10.2 [message #418020] |
Wed, 12 August 2009 09:57 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
I Created a report in sql*plus from a view and running it through the concurrent managers. I'm using 4 paramaters, but I made the awards paramaters as a required parameter and the from and to creation dates are not required. However, I noticed when trying to run the report with just the awards numbers no data is fetched, but when i run the report with both awards and dates then the reports shows data. Is there a way to get this report to run with awards only and make dates as an option from the query below?
parameters#
##1 from award number
##2 to award number
##3 from creation date
##4 to creation date
*/
set termout off
set head off
set pages 0
set feedback off
set verify off
set define #
col sort_col noprint
SELECT 'Revenue for Date ##3 - ##4, Awards ##1 - ##2', 0 sort_col
FROM DUAL
UNION
SELECT 'AWARD NUMBER^EVENT DATE^DESCRIPTION^REVENUE AMOUNT^CREATION DATE',
1
FROM DUAL
UNION
SELECT project_number
|| '^'
|| event_date
|| '^'
|| description
|| '^'
|| revenue_amount
|| '^'
|| creation_date,
2
FROM apps.pa_events_v
WHERE project_number BETWEEN NVL ('##1', project_number)
AND NVL ('##2', project_number)
AND creation_date BETWEEN TO_DATE (SUBSTR ('##3', 1, 10), 'YYYY/MM/DD')
AND TO_DATE (SUBSTR ('##4', 1, 10), 'YYYY/MM/DD')
ORDER BY 2;
Thanks
Anne
|
|
|
Re: running a SQL*PLUS report through oracle apps. concurrent managers (merged 2) 11.5.10.2 [message #418029 is a reply to message #418020] |
Wed, 12 August 2009 10:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/45399.jpg) |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
Quote: | WHERE project_number BETWEEN NVL ('##1', project_number)
AND NVL ('##2', project_number)
| There is no need of nvl for awards, as you have made them mandatory.Quote: | AND creation_date BETWEEN TO_DATE (SUBSTR ('##3', 1, 10), 'YYYY/MM/DD')
AND TO_DATE (SUBSTR ('##4', 1, 10), 'YYYY/MM/DD')
| But you need it in case of creation_date.
By
Vamsi
|
|
|
|
|
ORA-01861:LITERAL DOES NOT MATCH FORMAT STRING [message #418248 is a reply to message #418020] |
Thu, 13 August 2009 12:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
Hi,
I get the ora-01861 error message when trying to use the format date:
AND NVL(creation_date,sysdate) BETWEEN NVL( TO_DATE (SUBSTR ('##3', 1, 10), 'YYYY/MM/DD'),'1990/01/01')
AND NVL(TO_DATE (SUBSTR ('##4', 1, 10), 'YYYY/MM/DD'),'2199/12/31')
I don't see the problem. Please advise.
Anne
|
|
|
|
Re: ORA-01861:LITERAL DOES NOT MATCH FORMAT STRING [message #418252 is a reply to message #418248] |
Thu, 13 August 2009 12:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/45399.jpg) |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
I've asked you to use the nvl same way as you have done it for project_number.Quote: | AND creation_date BETWEEN NVL( TO_DATE (SUBSTR ('##3', 1, 10), 'YYYY/MM/DD'),creation_date)
AND NVL(TO_DATE (SUBSTR ('##4', 1, 10), 'YYYY/MM/DD'),creation_date)
| Try this.
By
Vamsi
|
|
|
|
Goto Forum:
Current Time: Tue Sep 10 09:19:39 CDT 2024
|