FAR Query and Base DB Loading and Unloading and Function Creation

--------------------------------------------------------------------
How to get table name and column name in base DB
------------------------------------------------------------------
 select * from systab where table_name='required Table Name'
SYSCOLUMNS
select tname from sys.SYSCOLUMNS where cname = 'columnname';
--------------------------------------------------------------
CREATE TABLE TEST_DBSchema.TEST_UTLZ(

   Name          varchar(50)    NULL IQ UNIQUE (255) ,

    OUR_TIME            datetime    NULL IQ UNIQUE (255) ,

    HW_Usage            numeric(18,0)  NULL IQ UNIQUE (255)

    )

 Environment

set temporary option escape_character = 'ON'

load table TEST_DBSchema.TEST_UTLZ(Name ',', OUR_TIME ',', HW_Usage '\X0A')from '/tmp/1.csv'

ESCAPES OFF QUOTES OFF

WITH CHECKPOINT ON;

--Trasfering file from to Target server.

scp /tmp/USER/1.csv user@190.167.75.12:/tmp

Change owner

chown dbview:oinstall 1.csv

chmod 777 1.csv

scp /tmp/Tahir/1.csv user@190.167.75.12:/tmp

Prompt Linux

source /opt/base/30/1.sh

dbisql -c "uid=DBA;pwd=dbapassword;eng=$ENG" -nogui Uti.sql

Linux:/tmp # vi Uti.sql

load table TEST_DBSchema.TEST_UTLZ(Name ',', OUR_TIME ',', HW_Usage '\X0A')from '/tmp/1.csv'

ESCAPES OFF QUOTES OFF

WITH CHECKPOINT ON;

Modifying CSV files using sed command

sed "1d" 1.csv > 2.csv

sed "1,2d" 1.csv > 2.csv
--------------------------------------------------------------------------------------------------------------------------
How to extract table from BASE DB to local disk
----------------------------------------------
Method
---------
hostname#:/dir1/dir2/dir3/dir4/dir5 # anyfile.sql
set temporary option temp_extract_name1 = '';
set temporary option temp_extract_append = off;
set temporary option Temp_Extract_Null_As_Empty = on;
set temporary option Temp_Extract_Column_Delimiter = '|';
set temporary option temp_extract_name1 = '/dirA/dirB/dirC/Table_Name_or_FileName.dat';
select * from tablename where age>=10 and age<50 and ROLNo='20';


HOW to create backup of a table in BASE DB
----------------------------------------------------------
select * into NEW_BACKUP_TABLE_1 from OLD_Table_WHOSE_BACKUP_YOUWANTTODO_1  

HOW TO GET TABLES NAME IN BASE DB
---------------------------------------------------------------
select 'select count(1) from '||case when creator=007 then 'PAK' when creator=006 then 'AJK' else 'ANYDBUSER' end||'.'||table_name||';'  
from   systable  where table_name like 'TABLENAME%'
and (upper(table_name) >='TABLENAMENO' and upper(table_name) <='TABLENAMENO') 
order by table_name; 
Get the table Names which Common Columns name such as Road_id  and road traffic.

Select table_name,column_name from  systabcol a

inner join systab  on  a.table_id=systab.table_id

where column_name ='col1' --such as  -- id number 

and exists (select 1 from systabcol b where a.table_id=b.table_id and b.column_name  ='col2')

HOW To Create Function in base  db
---------------------------------------------

create or replace  FUNCTION  FUN_NAME() returns int

begin

declare date_count int;

select datediff(dd,'18800202 08:00',getdate()) into date_count;

return date_count;

end

-----

create or replace  FUNCTION  Function_name(IN  give_time numeric(18))     

RETURNS numeric

BEGIN

     DECLARE con_time    numeric(18);

    set con_time =dateadd(ss,give_time+8*8600,'2017-1-1'); 

     return con_time;

end;

begin

declare date_count     int;

select datediff(dd,'20700101 08:00',getdate()) into date_count;

PRINT  date_count;   

end

 ---------storeprocedure
create or replace procedure PunjabUniversity.Own_Sp()
result("ID" numeric(3,0), "AT" varchar(20), "FIC" numeric(5,0))
on exception resume
begin
declare od char(10);
declare oq char(30000);
set od = cast(DATEDIFF( DAY, '1980/02/02', convert(char,getdate()-1,112)) as varchar(20)) ;
set oq='SELECT
ID,
AT,
SUM(A)/1024+ SUM(B)/1024  AS FIC FROM PunjabUnversity_TableNAME_'||od|| ' where ID=10,0000 group by ID,AT';
EXECUTE IMMEDIATE oq;
end

select * from PunjabUniversity.Own_Sp()
---------------------------------------------------------------------------------------------------------------------------ORACLEDBA-------------------------------

SELECT
  to_char(to_date(Dates), 'YYYYMM') as MM,
  last_day(Dates) as Month,
  a.Category,
  a.kpi,
  round(avg(a.value),2) as Value 
  FROM TDB_CITY_WISE a
  WHERE DATES > '21-OCT-2018'
  AND CITY='K'
  GROUP BY
  to_char(to_date(Dates), 'YYYYMM'),
  last_day(Dates),
  a.Category, a.kpi
 
alter session set "_ORACLE_SCRIPT"=true;
CREATE USER adhoc_user identified by orcl;
GRANT CONNECT TO adhoc_user;
GRANT DBA TO adhoc_user WITH ADMIN OPTION;

CREATE USER Tableau identified by orcl;
GRANT CONNECT TO Tableau;
GRANT DBA TO Tableau WITH ADMIN OPTION;


DROP USER SR;
CREATE USER SR identified by orcl;
GRANT CONNECT TO SR;
GRANT DBA TO SR WITH ADMIN OPTION;

SELECT * FROM "OT" where im_count=0

DELETE FROM "OT" where im_count=0

COMMIT 

---
CREATE USER username IDENTIFIED BY user1;


SELECT
  *
FROM
  DBA_SYS_PRIVS where GRANTEE= 'SYSASM' AND PRIVILEGE LIKE 'USER';
 
 
  DELETE FROM TDB WHERE PI <>'ATC'
  TRUNCATE TABLE TDB
  SELECT 
  FROM TDB where PI in ('PC','DC','CST');
 
        SELECT
        to_char(to_date(Dates), 'YYYYMM') as MM,
        SUM(val) as values
        FROM
        (
    SELECT to_char(to_date(Dates), 'YYYYMM'),
    SUM(CASE WHEN PI='CST' THEN  Value*10
             WHEN PI='DC' THEN  Value*45
             WHEN PI='PC' THEN  Value*45 END
    )/10,COUNT(DISTINCT Dates)
    as val
  FROM TDB where PI  in ('PC','DC','CST')
  GROUP BY to_char(to_date(Dates), 'YYYYMM')
  )as b
  GROUP BY
  to_char(to_date(Dates), 'YYYYMM');
 
  to_char(to_date(Dates), 'MonthYYYY') as Month,
 
  SELECT
  to_char(to_date(Dates), 'YYYYMM') as MM,
    last_day(Dates) as Month,
  to_number(to_char(to_date(Dates), 'w')) as Week,
  a.Category,
  a.PI,
  round(avg(a.value),2) as Value 
  FROM TDB a
  GROUP BY
  to_char(to_date(Dates), 'YYYYMM'),
  last_day(Dates),
  to_number(to_char(to_date(Dates), 'w')),a.Category, a.PI
  ORDER BY 1 DESC;
 
  INSERT INTO TDB VALUES
  ('27-OCT-18','Data','S1',0.81)
  COMMIT
  DELETE FROM TDB WHERE Dates= '27-OCT-18' AND Value='0.80';
 
 
 
 
SELECT
        to_char(to_date(Dates), 'YYYYMM') as MM,
        SUM(val) as values
        FROM
        (
    SELECT Dates,to_char(to_date(Dates), 'YYYYMM'),PI,
    SUM(Value),COUNT(DISTINCT Dates)as val
  FROM TDB where category='FFM'
  and to_char(to_date(Dates), 'YYYYMM')='201810'
  GROUP BY Dates,to_char(to_date(Dates), 'YYYYMM'),PI;
  )as
  GROUP BY
  to_char(to_date(Dates), 'YYYYMM');
 
  SELECT PI,count(dates) FROM TDB-- where category='FFM'
  GROUP BY PI
 
  DELETE FROM TDB where rowid < (SELECT MAX(rowid) FROM TDB WHERE Category=category)
 
  select * from MANUAL_TRANSACTION
 
  SELECT 
  to_char(to_date(Dates), 'YYYYMM'),
  sum(VALUE)/count(dates) FROM TDB
  where Dates between to_date('23-OCT-2018','dd-mon-yyyy') AND to_date('18-NOV-2018','dd-mon-yyyy')
    GROUP BY
  to_char(to_date(Dates), 'YYYYMM')
 
 
  SELECT
  Dates,
  to_char(to_date(Dates), 'YYYYMM') as MM,
    last_day(Dates) as Month,
  to_number(to_char(to_date(Dates), 'w')) as Week,
  a.Category,
  a.PI,
  round(avg(a.value),2) as Value 
  FROM TDB a
  GROUP BY
  Dates,
  to_char(to_date(Dates), 'YYYYMM'),
  last_day(Dates),
  to_number(to_char(to_date(Dates), 'w')),a.Category, a.PI
 
 
 
 
 
    SELECT
  to_char(to_date(Dates), 'YYYYMM') as MM,
    last_day(Dates) as Month,
  to_number(to_char(to_date(Dates), 'w')) as Week,
  a.Category,
  a.PI,
  round(avg(a.value),2) as Value 
  FROM TDB a
  GROUP BY
  Dates,
  to_char(to_date(Dates), 'YYYYMM'),
  last_day(Dates),
  to_number(to_char(to_date(Dates), 'w')),a.Category, a.PI
 
 
 
     SELECT
  to_char(to_date(Dates), 'YYYYMM') as MM,
  a.Category,PI,
  round(avg(a.value),2) as Value ,COUNT(DISTINCT DATES) , COUNT(DATES)
  FROM TDB a
  WHERE Category = 'Data'
  GROUP BY
  to_char(to_date(Dates), 'YYYYMM'),a.Category,PI
 
  SELECT DATES, SUM(VALUE) FROM TDB
  WHERE DATES > '21-OCT-2018'
  GROUP BY DATES
  ORDER BY 1
 
  SELECT  Dates,avg(VALUE) FROM
     TDB
  WHERE PI = 'S1'
  GROUP BY DATES
  ORDER BY DATES
 
  INSERT INTO TDB VALUES('27-OCT-18','Data','S1',0.75)
 
  DELETE FROM TDB
  WHERE DATEs= to_Date('28-OCT-2018','dd-mon-yyyy') AND PI='S1' AND CATEGORY='Data' and value=0.8
 
  DELETE FROM TDB
  SELECT * FROM TDB WHERE DATEs=to_Date('15-NOV-2018','dd-mon-yyyy') AND PI='S1' AND CATEGORY='Data'
 
  SELECT dates,count(dates) FROM TDB group by dates having count(dates)>1
  COMMIT;
 
 
  SELECT
  to_char(to_date(Dates), 'YYYYMM') as MM,
  SUM(
  CASE WHEN PI= 'ATC' THEN (value*10) ELSE 0 END
+CASE WHEN PI= 'PD' THEN (value*10)  ELSE 0 END
+CASE WHEN PI= 'S1' THEN (value*10) ELSE 0 END
  +CASE WHEN PI <>'Null' THEN (0.9*70) ELSE 0 END
)/10 as val
,
  round(avg(a.value),2) as Value
  FROM TDB a
  WHERE Category = 'Data'
  GROUP BY
  to_char(to_date(Dates), 'YYYYMM')
 
TRUNCATE TABLE TDB
  DELETE FROM TDB a WHERE ROWID<(SELECT MAX(ROWID) FROM TDB b WHERE A.Dates = b.Dates)
  CREATE TABLE TDB2 AS SELECT DISTINCT * FROM TDB
 
  INSERT INTO TDB SELECT  *FROM  TDB2
  WHERE DATES BETWEEN to_Date('22-OCT-2018','dd-mon-yyyy') and to_Date('19-NOV-2018','dd-mon-yyyy')
 
  SELECT PI,MAX(dates), COUNT(1) from TDB GROUP BY PI
 
  TRUNCATE TABLE TDB_CITY_WISE
  CREATE TABLE TDB_CITY_WISE AS
  SELECT * FROM TDB WHERE 1=2
  DELETE FROM TDB_CITY_WISE where value >1
  PI <> 'ATC'
  COMMIT
 
  SELECT PI,dates,city,value   FROM
  TDB_CITY_WISE  where value >1
  PI = 'ATC';
 
  SELECT PI,MAX(dates),MIN(dates), COUNT(1) from TDB_CITY_WISE
  GROUP BY PI
 
  CREATE TABLE bsarwar.TDB2 AS
  SELECT * FROM TDB2;
 
  SELECT  dates,COUNT(1) FROM  TDB_CITY_WISE where city <> 'K' AND PI = 'ATC'
    GROUP BY dates
  ORDER BY DATES

 
  DELETE FROM TDB_CITY_WISE a WHERE a.rowid < (SELECT MAX(b.ROWID) FROM TDB_CITY_WISE b
  WHERE a.dates= b.dates AND a.city=b.city and a.PI=b.PI and a.category=b.category
  )
  COMMIT
 
  INSERT INTO TDB_CITY_WISE
  (DAtes,Category,PI,value,city)
  VALUES
  ('25-OCT-18',    'Data'     ,'S1',          0.77,      'K')
  rollback
 
  DELETE FROM TDB_CITY_WISE
  WHERE PI='DC';
 
  SELECT DISTINCT CITY FROM TDB_CITY_WISE
  where
  CITY NOT IN
  (
  'Any City'         
  )
 
  dates = '15-NOV-2018'
 
  SELECT /*+ PARALLEL(4) */
  count(1) FROM adhoc_user.data_user_traffic_nov2018;
 
  ANALYZE TABLE adhoc_user.data_user_traffic_nov2018 COMPUTE STATISTICS;
 
 
  SELECT * FROM adhoc_user.data_user_traffic_nov2018;
 
  WHERE to_char(traffic_mb) LIKE '%E%';
 
 
 
  SELECT
  Dates,
  to_char(to_date(Dates), 'YYYYMM') as MM,
  last_day(Dates) as Month,
  to_number(to_char(to_date(Dates), 'w')) as Week,
  a.City,
  a.Category,
  a.PI,
  round(avg(a.value),2) as Value 
  FROM TDB_CITY_WISE a
  WHERE DATES > '21-OCT-2018'
  GROUP BY
  Dates,
  to_char(to_date(Dates), 'YYYYMM'),
  last_day(Dates),
  to_number(to_char(to_date(Dates), 'w')),
  a.City,
  a.Category, a.PI
 
 
 
 
 
 
 
 
 
  SELECT
  to_char(to_date(Dates), 'YYYYMM') as MM,
  last_day(Dates) as Month,
  a.Category,
  a.PI,
  round(avg(a.value),2) as Value 
  FROM TDB_CITY_WISE a
  WHERE DATES > '21-OCT-2018'
  AND CITY='K'
  GROUP BY
  to_char(to_date(Dates), 'YYYYMM'),
  last_day(Dates),
  a.Category, a.PI
 
alter session set "_ORACLE_SCRIPT"=true;
CREATE USER adhoc_user identified by orcl;
GRANT CONNECT TO adhoc_user;
GRANT DBA TO adhoc_user WITH ADMIN OPTION;

CREATE USER Tableau identified by orcl;
GRANT CONNECT TO Tableau;
GRANT DBA TO Tableau WITH ADMIN OPTION;


DROP USER SR;
CREATE USER SR identified by orcl;
GRANT CONNECT TO SR;
GRANT DBA TO SR WITH ADMIN OPTION;

SELECT * FROM "OT" where im_count=0

DELETE FROM "OT" where im_count=0

COMM

--------------------------------------------------------------------------------------------------------------------------------------------------------
Curson Definition in Oracle VS Python code independently written
-----------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE

CURSOR Data_curson IS select HOURR,count(1)   from  Your_Table_Name where HOURR between DATE '2013-05-21'  and DATE '2013-05-22'group by HOURR;

hour_name Your_Table_Name.HOURR%type;

h_count   integer;

BEGIN

OPEN Data_curson;

LOOP

FETCH Data_curson INTO hour_name,h_count ;

IF Data_curson%NOTFOUND

THEN

EXIT;

END IF;

Dbms_output.put_line('hour_name Fetched:'||hour_name);

Dbms_output.put_line('h_count Fetched:'||h_count);

END LOOP;

--Dbms_output.put_line('Total rows fetched is'||Data_curson%R0WCOUNT);

CLOSE Data_curson;

END;

--------------------------------------------------------------------------------------------------------------------

#-----------------------------------------------------------

#how to open a CSV file and read each row, as well as reference specific data on each row.       

#-------------------------------------------------------------       

#csv file is give below

# 0       1 2 3    index no

#1/2/2014,5,8,red

#1/3/2014,5,2,green

#1/4/2014,9,1,blue   

import csv

with open('D:\Test.csv') as csvfile:

    readCSV = csv.reader(csvfile, delimiter=',')

    dates = []

    colors = []

    for row in readCSV:

        color = row[3]

        date = row[0]

        dates.append(date)

        colors.append(color)

    print(dates[1]) #will print 1/2/2014

    print(colors[1]) #will print red

--------------------------------------------------------------------------------------------------------------

Oracle Fixed Assets Query

 ---------------------------------------------------------------------------------------------------------------------------
select
'Freehold Land own' Particulars,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Land%')+nvl(getRetirements_FAR_THK(&FromDate,'%Land%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Land%')+nvl(getRetirements_FAR_THK(&FromDate,'%Land%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Land%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Land%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Land%')+nvl(getRetirements_FAR_THK(&FromDate,'%Land%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%Land%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Land%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Land%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Land%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Land%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Land%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Leasehold Improvement',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Leasehold%')+nvl(getRetirements_FAR_THK(&FromDate,'%Leasehold%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Leasehold%')+nvl(getRetirements_FAR_THK(&FromDate,'%Leasehold%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Leasehold%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Leasehold%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Leasehold%')+nvl(getRetirements_FAR_THK(&FromDate,'%Leasehold%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%Leasehold%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Leasehold%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code='OPR FA TAN FH.Leasehold Improvement.NA.NA'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code='OPR FA TAN FH.Leasehold Improvement.NA.NA'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code='OPR FA TAN FH.Leasehold Improvement.NA.NA'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code='OPR FA TAN FH.Leasehold Improvement.NA.NA'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Computers & Software',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%COMPUTER%')+nvl(getRetirements_FAR_THK(&FromDate,'%COMPUTER%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%COMPUTER%')+nvl(getRetirements_FAR_THK(&FromDate,'%COMPUTER%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%COMPUTER%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%COMPUTER%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%COMPUTER%')+nvl(getRetirements_FAR_THK(&FromDate,'%COMPUTER%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%COMPUTER%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%COMPUTER%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like '%COMPUTER%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like '%COMPUTER%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like '%COMPUTER%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like '%COMPUTER%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Vehicles',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%VEHICLES%')+nvl(getRetirements_FAR_THK(&FromDate,'%VEHICLES%'),0) OpenBal,
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%VEHICLES%')+nvl(getRetirements_FAR_THK(&FromDate,'%VEHICLES%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%VEHICLES%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%VEHICLES%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%VEHICLES%')+nvl(getRetirements_FAR_THK(&FromDate,'%VEHICLES%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%VEHICLES%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%VEHICLES%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like '%VEHICLES%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like '%VEHICLES%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like '%VEHICLES%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like '%VEHICLES%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Infra-Network Batteries',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Network Batteries%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Network Batteries%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Network Batteries%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Network Batteries%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Network Batteries%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Network Batteries%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Network Batteries%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Network Batteries%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Network Batteries%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Network Batteries%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like 'OPR FA TAN FH.INFRASTRUCTURE.Infra-Network Batteries.NA'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like 'OPR FA TAN FH.INFRASTRUCTURE.Infra-Network Batteries.NA'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like 'OPR FA TAN FH.INFRASTRUCTURE.Infra-Network Batteries.NA'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like 'OPR FA TAN FH.INFRASTRUCTURE.Infra-Network Batteries.NA'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Infra-Telco Tools',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%INFRASTRUCTURE.Infra-Telco%')+nvl(getRetirements_FAR_THK(&FromDate,'%INFRASTRUCTURE.Infra-Telco%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%INFRASTRUCTURE.Infra-Telco%')+nvl(getRetirements_FAR_THK(&FromDate,'%INFRASTRUCTURE.Infra-Telco%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%INFRASTRUCTURE.Infra-Telco%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%INFRASTRUCTURE.Infra-Telco%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%INFRASTRUCTURE.Infra-Telco%')+nvl(getRetirements_FAR_THK(&FromDate,'%INFRASTRUCTURE.Infra-Telco%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%INFRASTRUCTURE.Infra-Telco%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%INFRASTRUCTURE.Infra-Telco%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%INFRASTRUCTURE.Infra-Telco%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%INFRASTRUCTURE.Infra-Telco%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%INFRASTRUCTURE.Infra-Telco%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%INFRASTRUCTURE.Infra-Telco%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Infra-Imported & Local Equipment Cost',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-%Equipment%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-%Equipment%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-%Equipment%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-%Equipment%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-%Equipment%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-%Equipment%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-%Equipment%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-%Equipment%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%Infra-%Equipment%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-%Equipment%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-%Equipment%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-%Equipment%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-%Equipment%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-%Equipment%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Infra-Services & Civil Works Cost',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-%vi% Cost%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-%vi% Cost%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-%vi% Cost%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-%vi% Cost%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-%vi% Cost%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-%vi% Cost%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-%vi% Cost%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-%vi% Cost%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%Infra-%vi% Cost%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-%vi% Cost%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-%vi% Cost%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-%vi% Cost%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-%vi% Cost%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-%vi% Cost%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Furniture',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%FURNITURE%')+nvl(getRetirements_FAR_THK(&FromDate,'%FURNITURE%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%FURNITURE%')+nvl(getRetirements_FAR_THK(&FromDate,'%FURNITURE%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%FURNITURE%'),0)  ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%FURNITURE%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%FURNITURE%')+nvl(getRetirements_FAR_THK(&FromDate,'%FURNITURE%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%FURNITURE%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%FURNITURE%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%FURNITURE%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%FURNITURE%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%FURNITURE%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%FURNITURE%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Office Equipment',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%OFFICE EQUIPMENT%')+nvl(getRetirements_FAR_THK(&FromDate,'%OFFICE EQUIPMENT%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%OFFICE EQUIPMENT%')+nvl(getRetirements_FAR_THK(&FromDate,'%OFFICE EQUIPMENT%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%OFFICE EQUIPMENT%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%OFFICE EQUIPMENT%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%OFFICE EQUIPMENT%')+nvl(getRetirements_FAR_THK(&FromDate,'%OFFICE EQUIPMENT%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%OFFICE EQUIPMENT%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%OFFICE EQUIPMENT%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%OFFICE EQUIPMENT%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%OFFICE EQUIPMENT%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
--varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%OFFICE EQUIPMENT%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%OFFICE EQUIPMENT%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Infra-Pre-operating Cost',OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Pre-operating Cost%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Pre-operating Cost%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,nvl(R.Retirments,0)Retirements_Adjustments,OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Pre-operating Cost%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Pre-operating Cost%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Pre-operating Cost%'),0) ACC_DeprnAS_At_30_June,nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Pre-operating Cost%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Pre-operating Cost%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Pre-operating Cost%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Pre-operating Cost%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Pre-operating Cost%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-Pre-operating Cost%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-Pre-operating Cost%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
--varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-Pre-operating Cost%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-Pre-operating Cost%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
union
select
'Infra-Financial Charges',
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Financial Charges%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Financial Charges%'),0) OpenBal,      --nvl(R.Retirments,0)
nvl(A.Additions,0) Addition,
nvl(R.Retirments,0)Retirements_Adjustments,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Financial Charges%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Financial Charges%'),0)+nvl(A.Additions,0)-nvl(R.Retirments,0) ClosingBal,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Financial Charges%'),0) ACC_DeprnAS_At_30_June,
nvl(C.DeprnAsNOV13,0)Deprn_As_NOV13,
nvl(RD.Retirments_Deprn,0) Retirements_Adjustments,
nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Financial Charges%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)   Accumu_DepreAT_30_11_13,
OpenBal_FAR_THK(add_months(&FromDate,0)-1,'%Infra-Financial Charges%')+nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Financial Charges%'),0)+nvl(A.Additions,0)-nvl(getRetirements_FAR_THK(&FromDate,'%Infra-Financial Charges%'),0) -nvl(R.Retirments,0)-(nvl(OpenBal_Deprn_FAR_THK_AT30JUN( add_months(&FromDate,0)-1,'%Infra-Financial Charges%'),0)+nvl(C.DeprnAsNOV13,0)-nvl(RD.Retirments_Deprn,0)) WDV_as_at_30_11_13
from
(select
sum(ab.cost) Additions
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-Financial Charges%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and(select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service between &FromDate and &ToDate)A,
(select
sum(re.cost_retired) Retirments
from
fa_retirements re
where
re.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-Financial Charges%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'6-DEC-13'--end subqurey inner
and re.date_retired  between &FromDate and &ToDate)R, --variablle '30-NOV-13'
 --varibale year wise 'JUN-13',JUN-12, JUN-11
(select sum(ds.ytd_deprn)DeprnAsNOV13
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-Financial Charges%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))----'6-DEC-13'--end subqurey inner
and dp.period_name=to_char(add_months(&Todate,12),'MON-YY'))C,--and dp.period_name='NOV-14'
(select
        sum(a.adjustment_amount) Retirments_Deprn
from
fa_adjustments a
where
a.asset_id in (select ab.asset_id from fa_books ab,fa_additions_b ad,fa_additions_tl adt,fa_transaction_headers th where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code  like '%Infra-Financial Charges%'
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null))--'06-DEC-13' end subqurey inner
and a.source_type_code = 'RETIREMENT'
and a.adjustment_type = 'RESERVE'
and a.period_counter_adjusted between (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&FromDate,12),'MON-YY'))) and (select  p.period_counter from fa_deprn_periods p where p.period_name in (to_char(add_months(&ToDate,12),'MON-YY')) ))RD
------------------------------------------------------------------------------------------------------------------------------------------
create or replace function OpenBal_FAR_THK(enddate date, catagory varchar2 ) return number is
  ResultOpenbal number;
begin
  select
sum(ab.cost)  into ResultOpenbal
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like  catagory            
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null)--'6-DEC-13'
and ab.date_placed_in_service<=enddate;
  return(ResultOpenbal);
end OpenBal_FAR_THK;
-----------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace function OpenBal_Deprn_FAR_THK_AT30JUN(enddate date, catagory varchar2) return number is
  OpenBalAt30JunResult number;
begin
select sum(DEP.Acc_Depreciation_at_30JUN_13)  into OpenBalAt30JunResult from(
select
CASE  WHEN ab.period_counter_life_complete is not null   then
(select sum(dd.deprn_reserve) from fa_deprn_detail dd where dd.asset_id=th.asset_id
 and dd.deprn_run_date in(select max(ds.deprn_run_date)
 from fa_deprn_summary ds where ds.asset_id=th.asset_id))
WHEN ab.period_counter_life_complete is  null then
(select sum(ds.deprn_reserve)
from
fa_deprn_summary ds,
fa_deprn_periods dp
where
ds.period_counter=dp.period_counter
and ds.asset_id=th.asset_id
and dp.period_name=to_char(add_months(enddate,0)-1,'MON-YY'))--JUN-13 PeriodName variable'to_char(add_months(&enddate,0)-1,'MON-YY')' will be passed from function to get the PeriodName, from FROMDate Parameter.
END Acc_Depreciation_at_30JUN_13
from
fa_books ab,
fa_additions_b ad,
fa_additions_tl adt,
fa_transaction_headers th
where
ab.asset_id=ad.asset_id
and th.asset_id=adt.asset_id
and th.asset_id=ad.asset_id
and ad.attribute_category_code like catagory
and th.transaction_type_code='ADDITION'
and ab.date_ineffective is null-- to get the present row
and ab.transaction_header_id_out is null-- to get assets which are not retirement yet
and th.date_effective between '15-SEP-05'and (select  to_char(max(dp.period_close_date),'DD-MON-YY') from fa_deprn_periods dp where
dp.period_close_date is not null) --NOV-14 --'5-AUG-13'--30-_june_06 that 1-JUL-06  to  30-JUN-13
and ab.date_placed_in_service<=enddate
)DEP;
--dbms_output.put_line(x);
return(OpenBalAt30JunResult);
end OpenBal_Deprn_FAR_THK_AT30JUN;

© 2014 Microsoft Terms Privacy & cookies Developers English (United States)
tahir khalid
Facebook
Facebook
Connect
Twitter
Twitter
Sign up
Already on Twitter?
Content from FacebookTwitter
Learn more|Turn off