Home » Applications » Oracle Fusion Apps & E-Business Suite » NVL
NVL [message #406779] Fri, 05 June 2009 16:15 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
When I run the following query:


SELECT   gi.award_id award_id, pfs.project_id project_id, nvl(pfs.task_id,0)task_id,
                        SUM (NVL (pfs.total_funding_amount, 0)) funding
             FROM apps.gms_summary_project_fundings pfs JOIN apps.gms_installments gi
              ON gi.installment_id = pfs.installment_id
            WHERE pfs.installment_id IN (
                   SELECT MAX (g.installment_id)
                     FROM apps.gms_installments g JOIN apps.gms_awards_all ga
                      ON ga.award_id = g.award_id
                    WHERE g.installment_id =
                                pfs.installment_id)
                                and gi.award_id IN (15037,4531)
                                and pfs.project_id IN (23043,8749)
                               GROUP BY gi.award_id, pfs.project_id, pfs.task_id









The output is as follows:

AWARD_ID	PROJECT_ID	TASK_ID	       FUNDING
4,531.00	8,749.00	8,661.00	236,765.68
4,531.00	8,749.00	8,662.00	41,259.04
4,531.00	8,749.00	8,663.00	52,388.73
15,037.00	23,043.00	0       	275,256.00





The problem is in my report the funding column for the dollar amount $275,256.00 will not appear in my report it will just appear as null. All other funding amounts appear in the report, except the $275,256.00. Also pfs.task_id column description in pfs table can be null.

So how can I get the $275,256.00 funding amount to appear in my report?


Anne

Re: NVL [message #406782 is a reply to message #406779] Fri, 05 June 2009 17:36 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


I think your taking about oracle forms & reports; IF yes; Double check your report.

thanks
Re: NVL [message #407614 is a reply to message #406782] Wed, 10 June 2009 13:14 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
No it's a sql*plus script running through Oracle apps.


Anne
Re: NVL [message #407616 is a reply to message #407614] Wed, 10 June 2009 13:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Are you sure the query is being run from apps is same as the one you are running from sql*plus?
Cross check in the <PROD>_TOP.

Post both the outputs.

By
Vamsi
Re: NVL [message #407959 is a reply to message #407616] Fri, 12 June 2009 10:40 Go to previous message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Here is the query again, this time I added NVL to the sum amount.


SELECT   gi.award_id award_id, pfs.project_id project_id,
         NVL (pfs.task_id, 0) task_id,
         SUM (NVL (pfs.total_funding_amount, 0)) funding
    FROM apps.gms_summary_project_fundings pfs JOIN apps.gms_installments gi
 ON gi.installment_id = pfs.installment_id
   WHERE pfs.installment_id IN (SELECT MAX (g.installment_id)
                                  FROM apps.gms_installments g JOIN apps.gms_awards_all ga
                                       ON ga.award_id = g.award_id
                                 WHERE g.installment_id = pfs.installment_id)
pfs.task_id is null
                                 GROUP BY gi.award_id, pfs.project_id, pfs.task_id





Output:

AWARD_ID,PROJECT_ID,TASK_ID,FUNDING
1218,38153,0,0
9813,16195,0,0
9813,16196,0,0
9813,16197,0,0
10433,13751,0,0
[B]15037,23043,0,275256[/B]
190713,50627,0,0


The funding amount of 275256 appears when I run the query by itself.




This is how it appears in the report:


Total Funding
 blank



The report is running from our sub directories in which reside the concurrent programs and reports. $SAM_TOP/sql - for customized reports written in sql plus.



Anne
Previous Topic: FSG Content Set Display in Oracle Report Manager
Next Topic: Oracle Exchange Marketplace
Goto Forum:
  


Current Time: Sat Dec 10 05:02:17 CST 2016

Total time taken to generate the page: 0.25371 seconds