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;

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')           

   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')