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  |
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 #269752 is a reply to message #269709] |
Mon, 24 September 2007 07:21  |
skooman
Messages: 913
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 |
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 |
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 |
skooman
Messages: 913
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  ):
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 |
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 #271679 is a reply to message #270485] |
Tue, 02 October 2007 08:28 |
skooman
Messages: 913
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 |
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
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 |
skooman
Messages: 913
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 |
|
|
|
Goto Forum:
Current Time: Thu Jul 04 12:13:03 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
