Home » Developer & Programmer » Reports & Discoverer » fiscal year report
| fiscal year report [message #345841] |
Fri, 05 September 2008 01:01  |
radhavijay
Messages: 29
Registered: July 2008
|
Junior Member |
|
|
hi all,
how to develop fiscal year report in fixed assets.
i mean my companies fiscal yr is 01-jul-08 to 30-jun-09.
i must develop report in fixed assets module.
my report o/p is
asset name cost purchase date jul08 aug08 sep08 oct08 nov08 dec08 jan09 feb09 mar09 apr09 may09 jun09
this is my report output the jul----jun are dynamic values.
parameters are from date and to date
when i give dates as parameters i must cost values in respective months for whole yr.
for eg
01-aug-08 to 30-jul-09 are parametres then
asset name cost jul08 aug08 sep08 oct08-------jun09
asdasd 1000 20 300 500 110
imust get o/p like this.
im new to fixed assets reports and new to work this type of fisacl year reports please help me how to do tht the july07 months are dynamic.im not getting how to do them dynamic.
thanks in advance
radha
|
|
|
|
| Re: fiscal year report [message #346178 is a reply to message #345841] |
Sun, 07 September 2008 01:17  |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
I am trying to guide you but your question is still not clear.
For example
| Quote: |
01-aug-08 to 30-jul-09 are parametres then
asset name cost jul08 aug08 sep08 oct08-------jun09
asdasd 1000 20 300 500 110
|
If input parameter starts from 1-aug-08 then how come july08 data is appearing above.
Similarly, what do you mean by July 07 months are dynamic.
| Quote: |
im new to fixed assets reports and new to work this type of fisacl year reports please help me how to do tht the july07 months are dynamic.im not getting how to do them dynamic.
|
Any how the basic idea is
Select assetname, to_chart(assetdate, 'MMYYYY'), sum(value)
from yourtable
where assetdate is between begin_date and end_date
group by assetname, to_chart(assetdate, 'MMYYYY');
and use matrix type of report.
good luck
-Dude
|
|
|
|
| Re: fiscal year report [message #346230 is a reply to message #346178] |
Sun, 07 September 2008 19:28 |
radhavijay
Messages: 29
Registered: July 2008
|
Junior Member |
|
|
thanks for guiding me.
i will expalin u clearly.
our company fiscal year is jul-jun
if we give parameters as 01-jul-08 to 30-jun-09
then
report output must be in this way
asset name cost jul08 aug08--------jun09
wqe 100 20 30 50
like this the cost of particular month must dispaly.
if we give parameters as 01-aug-08 to 01-aug-09
then jul08 must display the previous value and from august the particular values must display
cost is depreciation cost .
i must get the depriciation cost for particular months.
suppose if we buy a computer then its cost was 1000$ then after depriciation it was reduced to 950$
for jul it was depriciated 50$,aug-10$,sep-30$ like this must get values.
if thre was no depriciation value then 0.00 must print.
im telling dynamic because
if i run this yr then
i must get report as
asset name cost jul08 aug08 ------jan09
if i run next year the same report
thn report o/p must be
assetname cost jul09 aug09 sep09----jan10
like this.
please help me
my mail id is radhaa05@hotmail.com.
its urgent for me.
hope u help me.
thanks in advance
radha
|
|
|
|
|
|
|
|
|
|
| Re: fiscal year report [message #346461 is a reply to message #346330] |
Mon, 08 September 2008 11:15 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
again table structure is required. (not the table names)
Give sample data too.
Your report then can be made!!
| radhavijay wrote on Mon, 08 September 2008 14:22 | sorry mail id is
radhaa_05@hotmail.com
im getting values from different tables.
basic tables of fixed assets
fa_calendar_periods,
fa_additions_b,
fa_books,
fa_deprn_detail
thanks in advance
radha
|
|
|
|
|
| Re: fiscal year report [message #346550 is a reply to message #345841] |
Mon, 08 September 2008 18:51 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
i can guess that what is your required report.
I have attached an image showing your expected desired report.
Kindly provide table structure and data soon so that i can guide you.
-Dude
|
|
|
|
| Re: fiscal year report [message #346566 is a reply to message #346550] |
Mon, 08 September 2008 22:26 |
radhavijay
Messages: 29
Registered: July 2008
|
Junior Member |
|
|
table structure means i didtnt get u.
which table structure u want.
give me your mail id i will send u my query and other details and one question in which table org id will be in fixed assets.
thanks in advance
radha
|
|
|
|
| Re: fiscal year report [message #346567 is a reply to message #346566] |
Mon, 08 September 2008 22:31 |
radhavijay
Messages: 29
Registered: July 2008
|
Junior Member |
|
|
my report o/p is
asset name purchase date cost accum deprn jul08 aug08 sep08 oct08 nov08 dec08 jan09 feb09 mar09 apr09 may09 jun09 ytd deprn total deprn net book value
group by assets(like machinery,office renovation,office eqipment) and its types
office equipments have computers,air conditioners etc.
like this for every assets there will be some types by this i must group.
and deprn amount must display in jul08 aug08----jun09
send me your mail id i will send u details.
thanks in advance,
radha
|
|
|
|
| Re: fiscal year report [message #347229 is a reply to message #346567] |
Thu, 11 September 2008 02:22 |
radhavijay
Messages: 29
Registered: July 2008
|
Junior Member |
|
|
hello dude,
please reply me.
im sending u the query.
SELECT DISTINCT FAAT.DESCRIPTION AS "ASSET NAME",
FACB.SEGMENT1 AS "ATTRIBUTE GROUP",
FACB.SEGMENT2 AS "ATTRIBUTE TYPE",
FAB.DATE_PLACED_IN_SERVICE AS "PURCHASE DATE",
(FAB.LIFE_IN_MONTHS/12) AS "LIFE IN YEARS",
FAB.ORIGINAL_COST AS "COST",
FADD.DEPRN_RESERVE AS "ACCUM DEPRN",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=1 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "jul",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=2 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "aug" ,
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=3 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "sep",
i uploaded the rdf file.
please send me your mail id please
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=4 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "oct",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=5 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "nov",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=6 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "dec",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=7 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "jan",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=8 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "feb",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=9 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "mar",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=10 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "apr",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=11 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "may",
(SELECT PERIOD_NAME FROM FA_CALENDAR_PERIODS WHERE PERIOD_NUM=12 AND START_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE AND CALENDAR_TYPE='FA Cal') as "jun",
FADD.YTD_DEPRN AS "YTD DEPRN",
(FADD.YTD_DEPRN+FADD.DEPRN_RESERVE) AS " TOTAL DEPRN",
FAR.NBV_RETIRED AS "NBV"
FROM FA_ADDITIONS_TL FAAT,
FA_ADDITIONS_B FAAB,
FA_BOOKS FAB,
FA_RETIREMENTS FAR,
FA_DEPRN_DETAIL FADD,
FA_CATEGORIES_B FACB,
FA_CALENDAR_PERIODS FACP
WHERE FAAT.ASSET_ID=FAB.ASSET_ID AND
FAAT.ASSET_ID=FAR.ASSET_ID AND
FAAB.ASSET_ID=FAAT.ASSET_ID AND
FAAT.ASSET_ID=FADD.ASSET_ID AND
FAAB.ASSET_CATEGORY_ID=FACB.CATEGORY_ID AND
FAR.BOOK_TYPE_CODE='TWCT BOOK'
--FADP.PERIOD_OPEN_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE
this is the query i wrote please help me
|
|
|
|
| Re: fiscal year report [message #347233 is a reply to message #345841] |
Thu, 11 September 2008 02:41 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
Hi
I still need your table structure.
Table structure means columns present in the table and their data types.
Moreover, the query you have written is somewhat difficult to understand by me.
I have written email to you two days before but you have not replied yet.
Anyhow, i am sending you email again. Kindly respond.
-Dude
|
|
|
|
| Re: fiscal year report [message #347636 is a reply to message #345841] |
Fri, 12 September 2008 08:11 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
Hi
I have thought table structure my self and inserted dummy data.
So here is SQL code for table and data.
create table asset (
catdid varchar2(2),
assetid varchar2(2) constraint pk_asset_assetid primary key,
assetname varchar2(20),
purchdate date,
cost number(7)
);
create table depr
(assetid varchar2(2) CONSTRAINT fk_asset_depr_asset_id references asset(assetid),
deprdate date,
amt number(7)
);
insert into asset values (1,1, 'Computer-1', to_date('31-10-2006', 'dd-mm-yyyy'), 20000);
insert into asset values (1,2, 'Machinery-1', to_date('31-08-2005', 'dd-mm-yyyy'), 300000);
insert into asset values (2,3, 'Car-1', to_date('31-12-2007', 'dd-mm-yyyy'), 500000);
insert into depr values (1, to_date( '1-11-2006', 'dd-mm-yyyy'), 100);
insert into depr values (1, to_date( '1-12-2006', 'dd-mm-yyyy'), 100);
insert into depr values (1, to_date( '1-1-2007', 'dd-mm-yyyy'), 100);
insert into depr values (1, to_date( '1-2-2007', 'dd-mm-yyyy'), 100);
insert into depr values (1, to_date( '1-3-2007', 'dd-mm-yyyy'), 100);
insert into depr values (1, to_date( '1-4-2007', 'dd-mm-yyyy'), 100);
insert into depr values (1, to_date( '1-5-2007', 'dd-mm-yyyy'), 100);
insert into depr values (1, to_date( '1-6-2007', 'dd-mm-yyyy'), 100);
insert into depr values (1, to_date( '1-7-2007', 'dd-mm-yyyy'), 50);
insert into depr values (1, to_date( '1-8-2007', 'dd-mm-yyyy'), 50);
insert into depr values (1, to_date( '1-9-2007', 'dd-mm-yyyy'), 50);
insert into depr values (1, to_date( '1-10-2007', 'dd-mm-yyyy'), 50);
insert into depr values (1, to_date( '1-11-2007', 'dd-mm-yyyy'), 50);
insert into depr values (1, to_date( '1-12-2007', 'dd-mm-yyyy'), 50);
insert into depr values (1, to_date( '1-1-2008', 'dd-mm-yyyy'), 30);
insert into depr values (1, to_date( '1-2-2008', 'dd-mm-yyyy'), 30);
insert into depr values (1, to_date( '1-3-2008', 'dd-mm-yyyy'), 30);
insert into depr values (1, to_date( '1-4-2008', 'dd-mm-yyyy'), 30);
insert into depr values (1, to_date( '1-5-2008', 'dd-mm-yyyy'), 30);
insert into depr values (1, to_date( '1-6-2008', 'dd-mm-yyyy'), 30);
insert into depr values (2, to_date( '1-9-2005', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-10-2005', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-11-2005', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-12-2005', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-1-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-2-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-3-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-4-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-5-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-6-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-7-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-8-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-9-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-10-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-11-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-12-2006', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-1-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-2-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-3-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-4-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-5-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-6-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-7-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-8-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-9-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-10-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-11-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-12-2007', 'dd-mm-yyyy'), 1000);
insert into depr values (2, to_date( '1-1-2008', 'dd-mm-yyyy'), 900);
insert into depr values (2, to_date( '1-2-2008', 'dd-mm-yyyy'), 900);
insert into depr values (2, to_date( '1-3-2008', 'dd-mm-yyyy'), 900);
insert into depr values (2, to_date( '1-4-2008', 'dd-mm-yyyy'), 900);
insert into depr values (2, to_date( '1-5-2008', 'dd-mm-yyyy'), 900);
insert into depr values (2, to_date( '1-6-2008', 'dd-mm-yyyy'), 900);
insert into depr values (3, to_date( '1-1-2008', 'dd-mm-yyyy'), 3000);
insert into depr values (3, to_date( '1-2-2008', 'dd-mm-yyyy'), 3000);
insert into depr values (3, to_date( '1-3-2008', 'dd-mm-yyyy'), 3000);
insert into depr values (3, to_date( '1-4-2008', 'dd-mm-yyyy'), 3000);
insert into depr values (3, to_date( '1-5-2008', 'dd-mm-yyyy'), 3000);
insert into depr values (3, to_date( '1-6-2008', 'dd-mm-yyyy'), 3000);
And run the attached RDF.
Work still left to do:
Defining begining and ending date parameters.
I hope if the attached report do not fullfill your requirment then it will give you idea at least.
Good Luck
and remember the forum is still open for discussion!
-Dude
Just forgot to paste the following function. (this function is used to calculate the accumalted depreciation from date of purchase upto begining date (excluding begining dat))
CREATE OR REPLACE FUNCTION ACCDEP
(pin_assetid IN char,
pin_beg_date IN date) RETURN number IS
mdate date := pin_beg_date;
massetid varchar2(2) := pin_assetid;
mans number;
BEGIN
Select nvl(sum(amt),0)
into mans
from depr
where deprdate < mdate
and assetid=massetid;
RETURN mans;
END;
/
-
Attachment: depr.RDF
(Size: 72.00KB, Downloaded 1178 times)
[Updated on: Fri, 12 September 2008 11:38] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed May 01 13:16:48 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
