Fusion FA Asset Register Report : SQL

SELECT 
 FA.ASSET_NUMBER
,FAT.DESCRIPTION
,FB.ORIGINAL_COST
,FB.DATE_PLACED_IN_SERVICE
,FA.ATTRIBUTE_CATEGORY_CODE
,FCB.SEGMENT1 MAJOR_CATEGORY
,FCB.SEGMENT2 MINOR_CATEGORY
,NVL(FDS.DEPRN_RESERVE,0) DEPRN_RESERVE
,NVL(FDS.YTD_DEPRN,0) YTD_DEPRN
,(select FL.SEGMENT1 ||' ,'|| FL.SEGMENT2 ||' ,'|| FL.SEGMENT3 from fa_locations FL where FL.location_id = FDH.LOCATION_ID) location
,(SELECT FULL_NAME FROM PER_PERSON_NAMES_F WHERE PERSON_ID= FDH.ASSIGNED_TO AND NAME_TYPE ='GLOBAL') EMP_NAME
, (SELECT TO_CHAR(TRUNC(METH.LIFE_IN_MONTHS/12) +
             (MOD(METH.LIFE_IN_MONTHS,12)/100), '9000D00')
                                LIFE_IN_YR_MONTHS
FROM FA_METHODS METH
WHERE 1=1
AND METH.METHOD_ID = FB.METHOD_ID
AND ROWNUM =1
   ) LIFE_IN_YR_MONTHS
,1/(SELECT (METH.LIFE_IN_MONTHS/12) LIFE_IN_YR
FROM FA_METHODS METH
WHERE 1=1
AND METH.METHOD_ID = FB.METHOD_ID
AND ROWNUM =1
   ) DEPRN_RATIO
--,FCATB.ASSET_COST_ACCOUNT_CCID COST_ACCT_CCID
--,FCATB.RESERVE_ACCOUNT_CCID ACCUMULATED_DEPRN_CCID
--,FDH.CODE_COMBINATION_ID EXPENSE_CCID
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FCATB.ASSET_COST_ACCOUNT_CCID ) COST_ACCT
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FCATB.RESERVE_ACCOUNT_CCID    ) ACCDEP_ACCT
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID       ) EXP_ACCT
--,FDS_P.DEPRN_RESERVE DEPRN_RESERVE_PREVIOUS
--,FDS_P.DEPRN_AMOUNT
--,FDH.ASSIGNED_TO
--,FDH.LOCATION_ID
--,(SELECT PERIOD_NAME FROM FA_DEPRN_PERIODS WHERE PERIOD_COUNTER = FDP_C.PERIOD_COUNTER and BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1') ) CURRENT_PERIOD
--,FDP_C.PERIOD_COUNTER CURRENT_PERIOD_COUNTER
--,FDP_P.PERIOD_COUNTER PREVIOUS_PERIOD_COUNTER
--,(SELECT PERIOD_NAME FROM FA_DEPRN_PERIODS WHERE PERIOD_COUNTER = FDP_P.PERIOD_COUNTER and BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1')) PREVIOUS_PERIOD
--ROUND(SYSDATE - FB.DATE_PLACED_IN_SERVICE,0) DAYS_IN_SERVICE
FROM
  FA_ADDITIONS_B FA
, FA_ADDITIONS_TL FAT
, FA_BOOKS FB
, FA_CATEGORIES_B FCB
, FA_CATEGORY_BOOKS FCATB
, FA_DEPRN_SUMMARY FDS
--, FA_DEPRN_SUMMARY FDS_P
, FA_DISTRIBUTION_HISTORY FDH
, (SELECT MAX(PERIOD_COUNTER) AS PERIOD_COUNTER FROM FA_DEPRN_PERIODS WHERE PERIOD_CLOSE_DATE IS NULL AND BOOK_TYPE_CODE =  NVL(:P_BOOK_TYPE_CODE, '-1') ) FDP_C
--, (SELECT MAX(PERIOD_COUNTER) AS PERIOD_COUNTER FROM FA_DEPRN_PERIODS WHERE PERIOD_CLOSE_DATE IS NOT NULL AND BOOK_TYPE_CODE =  NVL(:P_BOOK_TYPE_CODE, '-1') ) FDP_P
, FA_BOOK_CONTROLS FBC
WHERE 1=1
AND FAT.ASSET_ID = FA.ASSET_ID
AND ( userenv('LANG') ) = FAT.language
------
AND FA.ASSET_ID = FB.ASSET_ID 
AND FCB.CATEGORY_ID = FA.ASSET_CATEGORY_ID
AND FCATB.CATEGORY_ID = FCB.CATEGORY_ID
AND FB.BOOK_TYPE_CODE = FCATB.BOOK_TYPE_CODE
AND FB.DATE_INEFFECTIVE IS NULL
AND FDS.ASSET_ID(+) = FA.ASSET_ID
AND FDS.BOOK_TYPE_CODE(+) = FB.BOOK_TYPE_CODE
AND FDS.PERIOD_COUNTER(+) = FDP_C.PERIOD_COUNTER
--AND FDS_P.ASSET_ID = FA.ASSET_ID
--AND FDS_P.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
--AND FDS_P.PERIOD_COUNTER = FDP_P.PERIOD_COUNTER
AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
AND FDH.ASSET_ID = FA.ASSET_ID
AND FDH.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FBC.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FBC.SET_OF_BOOKS_ID = :P_LEDGER_ID
--AND NVL(FDH.ASSIGNED_TO,-1) = NVL(:P_EMPLOYEE_ID,NVL(FDH.ASSIGNED_TO,-1))
and (NVL(FDH.ASSIGNED_TO,-1) IN (:P_EMPLOYEE_ID) OR LEAST(:P_EMPLOYEE_ID) IS NULL)
and (FCB.SEGMENT1 IN (:P_MAJOR_CATEGORY_NAME) OR LEAST(:P_MAJOR_CATEGORY_NAME) IS NULL)
and (FCB.SEGMENT2 IN (:P_MINOR_CATEGORY_NAME) OR LEAST(:P_MINOR_CATEGORY_NAME) IS NULL)
AND NVL(FDH.LOCATION_ID,-1) BETWEEN NVL(:P_FROM_LOCATION_ID, NVL(FDH.LOCATION_ID,-1)) AND NVL(:P_TO_LOCATION_ID, NVL(FDH.LOCATION_ID,-1))
AND FA.ASSET_NUMBER BETWEEN NVL(:P_FROM_ASSET_NO, FA.ASSET_NUMBER) AND NVL(:P_TO_ASSET_NO, NVL(:P_FROM_ASSET_NO, FA.ASSET_NUMBER))
AND FB.BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1')
AND EXISTS (select 'x' from FA_CATEGORY_BOOKS where book_type_code = :p_book_type_code and category_id = fcb.category_id)