OraFAQ Forum: Oracle Fusion Apps & E-Business Suite

Home » Applications » Oracle Fusion Apps & E-Business Suite » NVL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NVL [message #406779]

Fri, 05 June 2009 16:15

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

Report message to a moderator

Re: NVL [message #406782 is a reply to message #406779]

Fri, 05 June 2009 17:36

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

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

thanks

Report message to a moderator

Re: NVL [message #407614 is a reply to message #406782]

Wed, 10 June 2009 13:14

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

Report message to a moderator

Re: NVL [message #407616 is a reply to message #407614]

Wed, 10 June 2009 13:23

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

Report message to a moderator

Re: NVL [message #407959 is a reply to message #407616]

Fri, 12 June 2009 10:40

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 24 07:59:49 CST 2024