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
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 messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: running a SQL*PLUS report through oracle apps. concurrent managers (merged 2) 11.5.10.2 [message #418244 is a reply to message #418029] Thu, 13 August 2009 11:01 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Even with the changes I made it still does not work unless a date is added..then it will populate data.



WHERE project_number BETWEEN ('##1') AND ('##2') 
     AND creation_date BETWEEN TO_DATE (SUBSTR ('##3', 1, 10), 'YYYY/MM/DD')
                           AND TO_DATE (SUBSTR ('##4', 1, 10), 'YYYY/MM/DD')           




Anne
Re: running a SQL*PLUS report through oracle apps. concurrent managers (merged 2) 11.5.10.2 [message #418246 is a reply to message #418244] Thu, 13 August 2009 11:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
But you need it in case of creation_date.
I wanted you to put nvl for dates.

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 messageGo to next message
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 #418251 is a reply to message #418248] Thu, 13 August 2009 12:12 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Anne Simms wrote on Thu, 13 August 2009 13:07

[code]
( TO_DATE (SUBSTR ('##3', 1, 10), 'YYYY/MM/DD')



Substrincg of ##3,1,10 = ##3. there is no date format that will match this.
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 messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: ORA-01861:LITERAL DOES NOT MATCH FORMAT STRING [message #418255 is a reply to message #418252] Thu, 13 August 2009 12:32 Go to previous message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
OK, that worked.


Thank you very much!!!

I appreciate your promptness and paintence... Smile

Anne
Previous Topic: Formula Level
Next Topic: pmp course
Goto Forum:
  


Current Time: Wed Dec 07 18:30:57 CST 2016

Total time taken to generate the page: 0.19136 seconds