Home » Developer & Programmer » Reports & Discoverer » fiscal year report
fiscal year report [message #345841] Fri, 05 September 2008 01:01 Go to next message
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 Go to previous messageGo to next message
dude4084
Messages: 221
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 Go to previous messageGo to next message
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 #346267 is a reply to message #346230] Mon, 08 September 2008 00:42 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Your table structure?


Re: fiscal year report [message #346315 is a reply to message #346230] Mon, 08 September 2008 03:20 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Your email address is invalid.

Kindly rectify it.

Thanks

-dude
Re: fiscal year report [message #346330 is a reply to message #346315] Mon, 08 September 2008 04:22 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
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 #346461 is a reply to message #346330] Mon, 08 September 2008 11:15 Go to previous messageGo to next message
dude4084
Messages: 221
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 Go to previous messageGo to next message
dude4084
Messages: 221
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
  • Attachment: XXDEPSUMM.RDF
    (Size: 72.00KB, Downloaded 219 times)
Re: fiscal year report [message #347233 is a reply to message #345841] Thu, 11 September 2008 02:41 Go to previous messageGo to next message
dude4084
Messages: 221
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 Go to previous message
dude4084
Messages: 221
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 195 times)

[Updated on: Fri, 12 September 2008 11:38]

Report message to a moderator

Previous Topic: Reading material/Book
Next Topic: rep-1216 'F-2' has an illegal print condition
Goto Forum:
  


Current Time: Sat Dec 03 22:05:03 CST 2016

Total time taken to generate the page: 0.09968 seconds