Home » Developer & Programmer » Reports & Discoverer » HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL
HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #268382] Tue, 18 September 2007 06:41 Go to next message
fusion007
Messages: 11
Registered: September 2007
Junior Member
Hi...
I'm trying to do which in theory is a basic report to list fixed assets with the cost, opening depreciation, YTD depreciation, Accumulated depreciation and the NBV.
There doesn't appear to be a table that holds the opening depreciation. The opening depreciation is in effect the closing period 12 depreciation of the previous year. I can get the openimg dep figure by using a calculation Acc depreciation - YTD depreciation.

The problem I am having is that for some of the assets there isn't any current year depreciation but will have depreciation for the previous year closing period (period 12) which should the opening dep for the current year being reported on. Discoverer does not list this asset on the report as the main period parameter is say 05-2008.

I'm using an embedded sql within the main sql but I'm still not getting the results. I beleive there is a problem with the joins but I'm not sure where.

Can someone please help? Thanks

Here is the code that I am trying in the custom folder which is not working: (Period counter 24096 is period name 12-2007)

select apps.fabg_depreciations.asset_id Asset_Number 
,apps.fabg_depreciations.ytd_deprn YTD_Deprn 
,apps.fabg_depreciations.deprn_amount Deprn_Amount 
,apps.fabg_depreciations.accumulated_deprn Accumulated_Deprn 
,apps.fabg_depreciations.reval_reserve Reval_reserve 
,apps.fabg_acct_prds.period_name Period 
,apps.fabg_asset_bks.original_cost Original_Cost 
,apps.fabg_asset_bks.cost Cost 
,apps.fabg_assets.asset_category_id Category_ID 
,XXX.accumulated_deprn Opening_Dep 
,apps.fabg_asset_cats.description Description 
from apps.fabg_depreciations 
, apps.fabg_acct_prds 
, apps.fabg_asset_bks 
, apps.fabg_assets 
, apps.fabg_asset_cats 
,(select fabg_depreciations.asset_id, fabg_depreciations.accumulated_deprn 
from apps.fabg_depreciations where fabg_depreciations.period_counter = 24096) XXX 
where apps.fabg_depreciations.book_type_code= 'ASSETS CCA' 
and apps.fabg_depreciations.period_counter =apps.fabg_acct_prds.period_counter 
and apps.fabg_asset_bks.asset_id=apps.fabg_depreciations.asset_id(+) 
and apps.fabg_assets.asset_id=apps.fabg_asset_bks.asset_id 
and apps.fabg_assets.asset_category_id=fabg_asset_cats.asset_category_id 
and apps.fabg_assets.asset_id = XXX.asset_id(+) 

[Updated on: Tue, 18 September 2007 08:27] by Moderator

Report message to a moderator

Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #268682 is a reply to message #268382] Wed, 19 September 2007 06:19 Go to previous messageGo to next message
fusion007
Messages: 11
Registered: September 2007
Junior Member
Hi...can someone please help with this. Thanks
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #269588 is a reply to message #268682] Sun, 23 September 2007 12:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
In order for anyone to provide proper assistance, you need to read and follow the posting guidelines in the OraFAQ Forum Guide, which is located at the top of the list of posts, highlighted in yellow. You need to post create table and insert statements for sample data and the results that you want, based on that data, and your Oracle version.
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #269709 is a reply to message #269588] Mon, 24 September 2007 04:17 Go to previous messageGo to next message
fusion007
Messages: 11
Registered: September 2007
Junior Member
Sorry, Looks like I may have joined the wrong forum. I'm using Oracle Discoverer and Toad to create reports from existing tables using basic sql.
I thought perhaps someone could see from the above sql code where I was going wrong with the where conditions.
Thanks
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #269752 is a reply to message #269709] Mon, 24 September 2007 07:21 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Actually, in a case of EBS/apps (like this), I think that create table and insert scripts are less needed. The entire datamodel can be found on Metalink.

Two things I noticed in your statement:

You're outer joining on fabg_depreciations, but have a where clause on that table (book_type_code = 'ASSETS CCA') and a equi-join from fabg_depreciations to fabg_depreciations, thus "destroying" the outer join. So, this should be
WHERE  depr.book_type_code(+) = 'ASSETS CCA'
AND    depr.period_counter(+) = accp.period_counter


(see below for a rewrite of you statement using aliases like depr, such I use above; those long tablenames makes it hard to read I think).

Second thing is that I wonder what this xxx inline select is doing. What are you trying to accomplish?

One last question: you state that you have to look up the last depreciation value in the previous year to know the starting value for this year. I would have expected to see some lag function for this, but it's not there. Is this column depr.ytd_deprn doing this for you? And can it be empty? If so, what do you do?

Rewrite of the statement, using aliases:

SELECT depr.asset_id          asset_number
      ,depr.ytd_deprn         ytd_deprn
      ,depr.deprn_amount      deprn_amount
      ,depr.accumulated_deprn accumulated_deprn
      ,depr.reval_reserve     reval_reserve
      ,accp.period_name       period
      ,assb.original_cost     original_cost
      ,assb.cost              cost
      ,asst.asset_category_id category_id
      ,xxx.accumulated_deprn  opening_dep
      ,assc.description       description
FROM   apps.fabg_depreciations depr
      ,apps.fabg_acct_prds accp
      ,apps.fabg_asset_bks assb
      ,apps.fabg_assets asst
      ,apps.fabg_asset_cats assc
      ,(SELECT depr2.asset_id
              ,depr2.accumulated_deprn
        FROM   apps.fabg_depreciations deprdepr2
        WHERE  depr2.period_counter = 24096) xxx
WHERE  depr.book_type_code(+) = 'ASSETS CCA'
AND    depr.period_counter(+) = accp.period_counter
AND    assb.asset_id = depr.asset_id(+)
AND    asst.asset_id = assb.asset_id
AND    asst.asset_category_id = assc.asset_category_id
AND    asst.asset_id = xxx.asset_id(+)
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #269776 is a reply to message #269752] Mon, 24 September 2007 08:52 Go to previous messageGo to next message
fusion007
Messages: 11
Registered: September 2007
Junior Member
Hi...Thanks for taking the time to reply.

To answer your questions:

The XXX inline select is to pick up the accumulated depreciation figure for the last period in the previous year. This figure is the opening depreciation figure for the current year.

You mention about some lag function to pick up this value but I do not know how to create this function. Therefore, I am using the the accumulated depr figure from the previous year to accomplish this.
This figure can be potentially be empty for any new assets so in this case I would expect the opening_depr to be zero.

I tried the statement you kindly did but I still don't get any data returned when I run it for any assets that had the accumulated depreciation figure for the previous year but has not had any depreciation for the current period/year that I am running the report for.

I hope this makes sense.

Any other suggestions?

Thanks
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #269777 is a reply to message #269752] Mon, 24 September 2007 09:00 Go to previous messageGo to next message
fusion007
Messages: 11
Registered: September 2007
Junior Member
Sorry forgot to mention that when I tried the statement I got an error message "a table may be outer joined at most to one other table"

AND    depr.period_counter(+) = accp.period_counter
AND    assb.asset_id = depr.asset_id(+)


So I tried removing the outer join to both of them one at a time but it did not make a difference to the data being returned.

Thanks
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #269965 is a reply to message #269777] Tue, 25 September 2007 04:47 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I've tried to re-construct what you're doing on our test environment, but it seems that you have some setup we don't. I could have a look next week at a customer's site to check if they have this setup. But for now it's hard to check your statement since I don't have the data for it.

Getting back to the outer join, what you are doing in your original statement is like this (inventing an ERD format here Wink):

assc
|
asst - xxx(+)
|
assb
|
depr(+)
|
accp


So, you have an outerjoin on depr, but then an equijoin on accp, thus eliminating the outerjoin. I guess what you mean is:

assc
|
asst - xxx(+)
|
assb
|
depr(+)
|
accp(+)


I did mix up the (+) signs in my example, due to the lack of test data, sorry about that. But try to fix your statement to have the outerjoin "streched" to accp.

Something about your model I don't get: you join assets to books to depreciation to periods. But then you join that XXX (selecting from depreciation again) directly to assets, why is that? I'm not really familiar with OFA, but what is the meaning of books in this model?

Also, I noticed you are using views (fabg, don't know what that stands for) instead of base tables, why is that? Most of them are just 1:1 views on the base tables, so I would suggest to make it easier to comprehend and control by using the base tables. Especially that FABG_ASSETS is a weird view, I would investigate that if I were you and verify if you really need it.

[Updated on: Tue, 25 September 2007 04:49]

Report message to a moderator

Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #270251 is a reply to message #269965] Wed, 26 September 2007 05:42 Go to previous messageGo to next message
fusion007
Messages: 11
Registered: September 2007
Junior Member
Hi...Thanks for the reply. I have recreated the code using the base tables which will hopefully make it easier to follow

select fdd.asset_id Asset_Number 
,fdd.ytd_deprn YTD_Deprn 
,fdd.deprn_amount Deprn_Amount 
,fdd.deprn_reserve Accumulated_Deprn 
,fdd.reval_reserve Reval_reserve 
,fdp.period_name Period 
,fdd.cost Cost 
,faa.asset_category_id Category_ID 
,XXX.deprn_reserve Opening_Dep 
,fct.description Description 
from fa_deprn_detail     fdd 
, fa_deprn_periods         fdp 
, fa_books                      fab 
, fa_additions_b             faa 
, fa_categories_tl           fct 
,(select fdd2.asset_id, fdd2.deprn_reserve, fdd2.period_counter from fa_deprn_detail     fdd2   where  fdd2.period_counter = 24096 ) XXX 
where fdd.book_type_code= 'ASSETS CCA' 
and fab.asset_id=faa.asset_id 
and faa.asset_id = XXX.asset_id(+)
and faa.asset_id=fdd.asset_id
and fdp.period_counter=fdd.period_counter(+)
and faa.asset_category_id=fct.category_id
and fdd.period_counter=XXX.period_counter
and fdd.asset_id= 100175
and fdp.period_name = '05-2008'


You will see from the conditions in the code that I have selected Asset ID 100175 and period 05-2008 to test the code.

This asset id 100175 has depreciation in 2007 (period counter 24096) but has had no depreciation in 2008.
So if I run the report for day period 05-2008 I would still expect to see the 'opening depreciation' for that asset, hence, the inline view. But I do not get any data for that asset.

With regards to book type, we have 2 types 'Assets CCA' and 'Historical CCA'. I only want to report on 'Assets CCA', hence the condition.

Any ideas? Thanks
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #270484 is a reply to message #270251] Thu, 27 September 2007 03:15 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Do you get any data if you execute the XXX inline view only? (for that asset_id, so add a where clause on that)
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #270485 is a reply to message #270484] Thu, 27 September 2007 03:18 Go to previous messageGo to next message
fusion007
Messages: 11
Registered: September 2007
Junior Member
Hi..yes if I execute just the XXX line I do get data for that asset. But I can not add a where asset_id = clause as the majority of assets will have the dep figure for that period and I'm reporting on just that one asset.
I'm just using that asset id 100175 to test.

Thanks
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #271679 is a reply to message #270485] Tue, 02 October 2007 08:28 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Ok, got it. But it's logical that you don't see the data for that asset, since you're joining on periods, whereas you should outerjoin on periods. Did you try to work out the statement according to the 'ERD' I provided earlier on?

(this week I'm going to a customer's site, I'll have a try on their environment to see what the statement does there).
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #271694 is a reply to message #271679] Tue, 02 October 2007 09:39 Go to previous messageGo to next message
fusion007
Messages: 11
Registered: September 2007
Junior Member
Hi...Thanks for the reply. I've tried this where condition with the outer join on the period counter but still no joy.


where fdd.book_type_code= 'ASSETS CCA' 
and fab.asset_id=faa.asset_id 
and faa.asset_id = XXX.asset_id
and faa.asset_id=fdd.asset_id
and fdp.period_counter=fdd.period_counter(+)
and faa.asset_category_id=fct.category_id
and fdd.period_counter=XXX.period_counter(+)
and fdd.asset_id= 100175
and fdp.period_name = '05-2008'


I've had to remove the outer join on the
faa.asset_id = XXX.asset_id
as it will not allow me to have more than one outer join on one table.

Thanks again.
Re: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL [message #271997 is a reply to message #271694] Wed, 03 October 2007 10:50 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Okay, I've found it I think.

It all comes down to this table fa_deprn_detail. For each asset, firstly there is 1 row created with deprn_source_code = 'B' (like base) and an amount "to depreciate". Once you run some depreciation process for a certain period, that inserts a new row for the same asset with code = 'D'.

So, in you case there is no row for asset_id 100175 and period_name 05-2008 in fa_deprn_detail. But yet, you want to show data for that combination. In your setup that is not possible.
It requires some more fancy logic like:
- get all assets (that's in fa_additions)
- get their depreciation data for 05 2008 (that is in fa_deprn_detail and fa_periods), if any
- if there isn't any depreciation for the whole of 2008 (that's a dirty detail you mentioned!), than get the last value from 2007 (so, go into fa_deprn_details again).

I'm not sure about all this, but this is what I've figured out based on metalink and the data at the customer's site. Hope it helps!
Hope to find time tomorrow to write an example, maybe you can try it yourself?

PS books is never used in the statement, so it's easier to remove it from the select.

[Updated on: Wed, 03 October 2007 11:01]

Report message to a moderator

Previous Topic: string convertion
Next Topic: I need help to create calendar style report
Goto Forum:
  


Current Time: Sun Dec 04 22:54:15 CST 2016

Total time taken to generate the page: 0.24706 seconds