Oracle Apps Fixed Asset Details.

Generally User want fixed asset and their depreciation details information.

select
FADT.category
,FADT.major_cat
,FADT.minor_cat
,FADT.asset_number
,FADT.description
,FADT.QUANTITY
,FADT.use_date
,FADT.DEPRECIATION_DATE
,FADT.method
,FADT.book_type_code
,FADT.LIFE_IN_MONTHS
,FADT.DEPRN_RESERVE
,FADT.YTD_DEPRN
,FADT.employee_number
,FADT.expence_account
,fadt.segment1 Comp
,fadt.segment2 dev
,fadt.segment3 city

,fadt.segment4 Loc
,fadt.loc_code
,fadt.segment5 Dept
,fadt.segment6 Macc
,fadt.segment7 Intercomp
,fadt.segment8 Future1
,fadt.segment9 Future2
,FADT.location
,FADT.department
,FADT.reference_num
,FADT.transaction_type
,FADT.period_effective
,FADT.period_entered
--,FADT.period_name
,FADT.cost
,FADT.original_cost
,FADT.cost_open_bal
,FADT.cost_addition
,FADT.cost_Transfer
,FADT.cost_Disposal
,(FADT.cost_open_bal + FADT.cost_addition + FADT.cost_Transfer - FADT.cost_Disposal) cost_close_bal
,FADT.DEP_OPEN_BAL
,FADT.Dep_Charge
,FADT.Dep_Transfer
,FADT.DEP_DISPOSAL
,(FADT.Dep_open_bal + NVL(FADT.Dep_Charge,0) + FADT.Dep_Transfer - NVL(FADT.Dep_Disposal,0)) Dep_close_bal
,((FADT.COST_OPEN_BAL + FADT.COST_ADDITION + FADT.COST_TRANSFER - FADT.COST_DISPOSAL)
-(FADT.Dep_open_bal + FADT.Dep_Charge + FADT.Dep_Transfer - FADT.Dep_Disposal)) Net_bal
from
(select
faa.ATTRIBUTE_CATEGORY_CODE category
,faa.ASSET_NUMBER asset_number
,faa.DESCRIPTION  description
,FAA.current_units QUANTITY
,fb.DATE_PLACED_IN_SERVICE  use_date
,fb.PRORATE_DATE DEPRECIATION_DATE
,fb.DEPRN_METHOD_CODE method
,fb.book_type_code
,fb.LIFE_IN_MONTHS LIFE_IN_MONTHS
--,fb.COST  cost
,ffic.current_cost cost
,fb.original_cost
,XXHFL_FA_OPB_FUNC(ftb.asset_id,:P_FROM_PERIOD,:P_TO_PERIOD) cost_open_bal 
,ffic.period_effective
,fc.segment1 major_cat
,fc.segment2 minor_cat
,(SELECT DEPRN_RESERVE FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=FAA.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND b.period_name= NVL(:P_TO_PERIOD,b.period_name))
  and rownum=1)DEPRN_RESERVE
,(SELECT YTD_DEPRN FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=Faa.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND b.period_name= NVL(:P_TO_PERIOD,b.period_name))
  and rownum=1)YTD_DEPRN
--,fdp.period_name
 ,fe.employee_number
 ,gcc.concatenated_segments expence_account
,gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,gcc.segment9
,fl.segment1||'-'||fl.segment2||'-'||fl.segment3||'-'||
fl.segment4||'-'||fl.segment5||'-'||fl.segment6||'-'||fl.segment7 Loc_code
,flv_loc.description location --> Location
,flv_dep.description department --> department 
,fth.transaction_header_id reference_num
,FFIC.TRANSACTION_TYPE
,ffic.period_entered
 ,(SELECT nvl(SUM(b.current_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
  and fb.transaction_header_id_in=b.transaction_header_id_in
  AND FB.TRANSACTION_HEADER_ID_IN=A.TRANSACTION_HEADER_ID
 --and a.period_effective = nvl(:P_FROM_PERIOD,a.period_effective)
 AND TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY') = NVL(:P_TO_PERIOD,TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY'))
  AND a.transaction_type = 'Addition' 
  ) COST_ADDITION
   ,(SELECT nvl(SUM(b.original_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
   and fb.transaction_header_id_in=b.transaction_header_id_in
  AND FB.TRANSACTION_HEADER_ID_IN=A.TRANSACTION_HEADER_ID
 and TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY') = NVL(:P_TO_PERIOD,TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY'))
  AND a.transaction_type = 'Full Retirement')cost_Disposal
  ,(SELECT nvl(SUM(b.current_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
   and fb.transaction_header_id_in=b.transaction_header_id_in
  AND FB.TRANSACTION_HEADER_ID_IN=A.TRANSACTION_HEADER_ID
  -- and a.period_effective = nvl(:P_FROM_PERIOD,a.period_effective)
  and TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY') = NVL(:P_TO_PERIOD,TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY'))
  AND A.TRANSACTION_TYPE = 'Transfer') COST_TRANSFER
,(SELECT (nvl(DEPRN_RESERVE,0)-nvl(DEPRN_AMOUNT,0)) FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=FAA.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND b.period_name= NVL(:P_TO_PERIOD,b.period_name)
  )  AND ROWNUM=1)DEP_OPEN_BAL
,(SELECT DEPRN_AMOUNT FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=FAA.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(B.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE A.ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND b.period_name= NVL(:P_TO_PERIOD,b.period_name))
  AND ROWNUM=1) DEP_CHARGE
 ,(SELECT NVL(SUM(A.YTD_DEPRN-A.DEPRN_RESERVE),0) FROM FA_DEPRN_DETAIL A,FA_TRANSACTION_HISTORY_TRX_V B
  WHERE A.ASSET_ID=FAA.ASSET_ID
  AND A.ASSET_ID=B.ASSET_ID
  And a.period_counter=b.period_counter
  AND B.TRANSACTION_TYPE='Full Retirement'
  AND a.PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND B.PERIOD_NAME= NVL(:P_TO_PERIOD,B.PERIOD_NAME)
  ) and rownum=1)Dep_Disposal
 /*(SELECT nvl(SUM(b.current_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
  and fb.transaction_header_id_in=b.transaction_header_id_in
  and fb.transaction_header_id_in=a.transaction_header_id
  AND A.PERIOD_EFFECTIVE = NVL(:P_FROM_PERIOD,A.PERIOD_EFFECTIVE)
  AND a.transaction_type = 'Full Retirement')Dep_Disposal*/
  ,(SELECT nvl(SUM(b.current_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
   and fb.transaction_header_id_in=b.transaction_header_id_in
  AND FB.TRANSACTION_HEADER_ID_IN=A.TRANSACTION_HEADER_ID
  and TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY') = NVL(:P_TO_PERIOD,TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY'))
  -- and a.period_effective = nvl(:P_FROM_PERIOD,a.period_effective) 
  AND a.transaction_type = 'Transfer')Dep_Transfer
from
fa_additions faa
,fa_books fb
,FA_CATEGORIES_B_KFV FC
-----,fa_deprn_detail fdd
--,fa_deprn_periods fdp
,FA_EMPLOYEES fe
 ,FA_DISTRIBUTION_HISTORY fdh
  ,fa_locations fl
 ,gl_code_combinations_kfv gcc
,FND_FLEX_VALUES_VL flv_loc
,fnd_flex_value_sets fls_loc
 ,FND_FLEX_VALUES_VL flv_dep
 ,fnd_flex_value_sets fls_dep
 ,FA_TRANSACTION_HISTORY_TRX_V fth
,FA_TRANSACTION_HISTORY_BOOKS_V ftb
,FA_FINANCIAL_INQUIRY_COST_V FFIC
----,FA_FINANCIAL_INQUIRY_DEPRN_V ffid
where 1=1
and faa.ASSET_ID=fb.ASSET_ID
AND faa.asset_category_id = fc.category_id
AND FB.DATE_INEFFECTIVE IS NULL
and fe.employee_id(+)= fdh.assigned_to
and fAA.ASSET_ID=fdh.ASSET_ID
AND fdh.date_ineffective IS NULL
AND fl.location_id = fdh.location_id
and fdh.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and gcc.segment4 = flv_loc.flex_value_meaning --> Join for location value set
and gcc.segment3 = flv_loc.parent_flex_value_low --> Join for region - dependent value set
and flv_loc.flex_value_set_id = fls_loc.flex_value_set_id
and fls_loc.flex_value_set_name = 'Hafil_Location'
and gcc.segment5 = flv_dep.flex_value_meaning --> Join for department value set
and flv_dep.flex_value_set_id = fls_dep.flex_value_set_id
and fls_dep.flex_value_set_name = 'Hafil_Department'
and faa.ASSET_ID=fth.ASSET_ID
and fb.transaction_header_id_in=fth.transaction_header_id
and faa.ASSET_ID=ftb.ASSET_ID
and ftb.ASSET_ID=fth.ASSET_ID
AND FB.TRANSACTION_HEADER_ID_IN=FTB.TRANSACTION_HEADER_ID_IN
AND FAA.ASSET_ID=FFIC.ASSET_ID
AND (SELECT DEPRN_AMOUNT FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=FAA.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND B.PERIOD_NAME= NVL(:P_TO_PERIOD,B.PERIOD_NAME))
  AND ROWNUM=1) is not null
AND FFIC.TRANSACTION_HEADER_ID_IN = (SELECT C.TRANSACTION_HEADER_ID FROM FA_TRANSACTION_HISTORY_TRX_V C
                                     WHERE C.TRANSACTION_TYPE='Addition'
                                     AND C.ASSET_ID=FB.ASSET_ID)
                                     --and c.TRANSACTION_HEADER_ID_IN=fb.TRANSACTION_HEADER_ID_IN)
and fb.book_type_code = nvl(:P_ASSET_BOOK,fb.book_type_code)
AND faa.ASSET_ID IN (SELECT A.ASSET_ID FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
                 WHERE A.PERIOD_COUNTER=B.PERIOD_COUNTER
                 AND TO_DATE(B.PERIOD_NAME,'Mon-YY') BETWEEN NVL(TO_DATE(:P_FROM_PERIOD,'Mon-YY'),TO_DATE(B.PERIOD_NAME,'Mon-YY'))
AND NVL(TO_DATE(:P_TO_PERIOD,'Mon-YY'),TO_DATE(B.PERIOD_NAME,'Mon-YY')))   
AND FAA.ASSET_NUMBER = nvl(:P_ASSET_NUM,faa.asset_number)
and  faa.ATTRIBUTE_CATEGORY_CODE = nvl(:P_CATEGORY,faa.ATTRIBUTE_CATEGORY_CODE)
and flv_loc.description = nvl(:P_LOCATION,flv_loc.description)
AND GCC.CONCATENATED_SEGMENTS =NVL(:P_EXPENCE_ACC,GCC.CONCATENATED_SEGMENTS)
UNION ALL
select
faa.ATTRIBUTE_CATEGORY_CODE category
,faa.ASSET_NUMBER asset_number
,faa.DESCRIPTION  description
,FAA.current_units QUANTITY
,fb.DATE_PLACED_IN_SERVICE  use_date
,fb.PRORATE_DATE DEPRECIATION_DATE
,fb.DEPRN_METHOD_CODE method
,fb.book_type_code
,fb.LIFE_IN_MONTHS LIFE_IN_MONTHS
--,fb.COST  cost
,ffic.current_cost cost
,fb.original_cost
,XXHFL_FA_OPB_FUNC(ftb.asset_id,:P_FROM_PERIOD,:P_TO_PERIOD)  cost_open_bal 
,ffic.period_effective
,fc.segment1 major_cat
,FC.SEGMENT2 MINOR_CAT
,(SELECT NVL(DEPRN_RESERVE,0) FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=FAA.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND b.period_name= NVL(:P_TO_PERIOD,b.period_name))
  AND ROWNUM=1)DEPRN_RESERVE
,(SELECT NVL(YTD_DEPRN,0) FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=Faa.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  --AND B.PERIOD_NAME= NVL(:P_TO_PERIOD,B.PERIOD_NAME)
  )
  and rownum=1)YTD_DEPRN
--,fdp.period_name
 ,fe.employee_number
 ,gcc.concatenated_segments expence_account
,gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,gcc.segment5,gcc.segment6,gcc.segment7,gcc.segment8,gcc.segment9
,fl.segment1||'-'||fl.segment2||'-'||fl.segment3||'-'||
fl.segment4||'-'||fl.segment5||'-'||fl.segment6||'-'||fl.segment7 Loc_code
,flv_loc.description location --> Location
,flv_dep.description department --> department 
,fth.transaction_header_id reference_num
,FFIC.TRANSACTION_TYPE
,FFIC.PERIOD_ENTERED
 ,/*(SELECT nvl(SUM(b.current_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
  and fb.transaction_header_id_in=b.transaction_header_id_in
  AND FB.TRANSACTION_HEADER_ID_IN=A.TRANSACTION_HEADER_ID
 --and a.period_effective = nvl(:P_FROM_PERIOD,a.period_effective)
  ---AND TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY') = NVL(:P_TO_PERIOD,TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY'))
  AND a.transaction_type = 'Addition' 
  )*/ 0 COST_ADDITION
 ,(SELECT nvl(SUM(b.original_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
   and fb.transaction_header_id_in=b.transaction_header_id_in
  AND FB.TRANSACTION_HEADER_ID_IN=A.TRANSACTION_HEADER_ID
 --and TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY') = NVL(:P_TO_PERIOD,TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY'))
  AND a.transaction_type = 'Full Retirement')cost_Disposal
  ,(SELECT nvl(SUM(b.current_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
  and fb.transaction_header_id_in=b.transaction_header_id_in
  AND FB.TRANSACTION_HEADER_ID_IN=A.TRANSACTION_HEADER_ID
  AND A.TRANSACTION_TYPE = 'Transfer') COST_TRANSFER
,(SELECT (NVL(DEPRN_RESERVE,0)-NVL(DEPRN_AMOUNT,0)) FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=FAA.ASSET_ID
  AND (PERIOD_COUNTER,distribution_id)=(SELECT MAX(A.PERIOD_COUNTER),max(distribution_id) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  --AND b.period_name= NVL(:P_TO_PERIOD,b.period_name)
  )  AND ROWNUM=1)DEP_OPEN_BAL
,/*(SELECT NVL(DEPRN_AMOUNT,0) FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=FAA.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND b.period_name= NVL(:P_TO_PERIOD,b.period_name))
  AND ROWNUM=1)*/ 0 DEP_CHARGE
 ,(SELECT NVL(SUM(A.YTD_DEPRN-A.DEPRN_RESERVE),0) FROM FA_DEPRN_DETAIL A,FA_TRANSACTION_HISTORY_TRX_V B
  WHERE A.ASSET_ID=FAA.ASSET_ID
  AND A.ASSET_ID=B.ASSET_ID
  And a.period_counter=b.period_counter
  AND B.TRANSACTION_TYPE='Full Retirement'
  AND a.PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  ---AND B.PERIOD_NAME= NVL(:P_TO_PERIOD,B.PERIOD_NAME)
  ) and rownum=1)Dep_Disposal
  ,(SELECT nvl(SUM(b.current_cost),0)
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = faa.asset_id
  and b.asset_id = faa.asset_id
  and a.asset_id = b.asset_id
  and fb.transaction_header_id_in=b.transaction_header_id_in
  AND FB.TRANSACTION_HEADER_ID_IN=A.TRANSACTION_HEADER_ID
 --- and TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY') = NVL(:P_TO_PERIOD,TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY'))
  -- and a.period_effective = nvl(:P_FROM_PERIOD,a.period_effective) 
  AND a.transaction_type = 'Transfer')Dep_Transfer
from
fa_additions faa
,fa_books fb
,FA_CATEGORIES_B_KFV FC
-----,fa_deprn_detail fdd
--,fa_deprn_periods fdp
,FA_EMPLOYEES fe
,FA_DISTRIBUTION_HISTORY fdh
 ,fa_locations fl
 ,gl_code_combinations_kfv gcc
,FND_FLEX_VALUES_VL flv_loc
,fnd_flex_value_sets fls_loc
 ,FND_FLEX_VALUES_VL flv_dep
 ,fnd_flex_value_sets fls_dep
 ,FA_TRANSACTION_HISTORY_TRX_V fth
,FA_TRANSACTION_HISTORY_BOOKS_V ftb
,FA_FINANCIAL_INQUIRY_COST_V FFIC
----,FA_FINANCIAL_INQUIRY_DEPRN_V ffid
where 1=1
and faa.ASSET_ID=fb.ASSET_ID
AND faa.asset_category_id = fc.category_id
AND FB.DATE_INEFFECTIVE IS NULL
and fe.employee_id(+)= fdh.assigned_to
and fAA.ASSET_ID=fdh.ASSET_ID
AND fdh.date_ineffective IS NULL
AND fl.location_id = fdh.location_id
and fdh.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and gcc.segment4 = flv_loc.flex_value_meaning --> Join for location value set
and gcc.segment3 = flv_loc.parent_flex_value_low --> Join for region - dependent value set
and flv_loc.flex_value_set_id = fls_loc.flex_value_set_id
and fls_loc.flex_value_set_name = 'Hafil_Location'
and gcc.segment5 = flv_dep.flex_value_meaning --> Join for department value set
and flv_dep.flex_value_set_id = fls_dep.flex_value_set_id
and fls_dep.flex_value_set_name = 'Hafil_Department'
and faa.ASSET_ID=fth.ASSET_ID
and fb.transaction_header_id_in=fth.transaction_header_id
and faa.ASSET_ID=ftb.ASSET_ID
and ftb.ASSET_ID=fth.ASSET_ID
AND FB.TRANSACTION_HEADER_ID_IN=FTB.TRANSACTION_HEADER_ID_IN
AND FAA.ASSET_ID=FFIC.ASSET_ID
AND FFIC.TRANSACTION_HEADER_ID_IN = (SELECT C.TRANSACTION_HEADER_ID FROM FA_TRANSACTION_HISTORY_TRX_V C
                                     WHERE C.TRANSACTION_TYPE='Addition'
                                     AND C.ASSET_ID=FB.ASSET_ID)
                                     --and c.TRANSACTION_HEADER_ID_IN=fb.TRANSACTION_HEADER_ID_IN)
and fb.book_type_code = nvl(:P_ASSET_BOOK,fb.book_type_code)
AND FAA.ASSET_ID IN (SELECT A.ASSET_ID FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
                 WHERE B.PERIOD_COUNTER = (SELECT MAX(C.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL C,FA_DEPRN_PERIODS D
                 WHERE TO_DATE(D.PERIOD_NAME,'Mon-YY') BETWEEN NVL(TO_DATE(:P_FROM_PERIOD,'Mon-YY'),TO_DATE(D.PERIOD_NAME,'Mon-YY'))
AND NVL(TO_DATE(:P_TO_PERIOD,'Mon-YY'),TO_DATE(D.PERIOD_NAME,'Mon-YY'))))  
AND FAA.ASSET_NUMBER = nvl(:P_ASSET_NUM,faa.asset_number)
AND (SELECT DEPRN_AMOUNT FROM FA_DEPRN_DETAIL
  WHERE ASSET_ID=FAA.ASSET_ID
  AND PERIOD_COUNTER=(SELECT MAX(A.PERIOD_COUNTER) FROM FA_DEPRN_DETAIL A,FA_DEPRN_PERIODS B
  WHERE ASSET_ID=FAA.ASSET_ID
  AND A.PERIOD_COUNTER = B.PERIOD_COUNTER
  AND B.PERIOD_NAME= NVL(:P_TO_PERIOD,B.PERIOD_NAME))
  AND ROWNUM=1) is null
and  faa.ATTRIBUTE_CATEGORY_CODE = nvl(:P_CATEGORY,faa.ATTRIBUTE_CATEGORY_CODE)
AND FLV_LOC.DESCRIPTION = NVL(:P_LOCATION,FLV_LOC.DESCRIPTION)
AND GCC.CONCATENATED_SEGMENTS =NVL(:P_EXPENCE_ACC,GCC.CONCATENATED_SEGMENTS)
)FADT;

CREATE OR REPLACE FUNCTION XX_FA_OPB_FUNC(p_asset_id number,p_period_date varchar2,p_to_date varchar2)
return number
is
L_COST NUMBER;
L_Addition NUMBER;
p_start_date date;
p_end_Date date;
begin
begin
select start_date,end_Date
into
p_start_date,p_end_Date
from FA_CALENDAR_PERIODS where period_name=to_char(to_date(p_period_date,'Mon-RR'),'Mon-RR');
exception
when  others then
null;
end;
L_COST:=0;
dbms_output.put_line('Date:'||p_period_date);
dbms_output.put_line('Date new:'||to_char(to_date(p_period_date,'Mon-RR'),'Mon-RR'));
dbms_output.put_line('L_COST:'||L_COST);
dbms_output.put_line('p_start_date:'||p_start_date);
dbms_output.put_line('p_end_Date:'||p_end_Date);
begin
select Fth.CURRENT_COST INTO L_COST
from FA_TRANSACTION_HISTORY_BOOKS_V FTH,FA_FINANCIAL_INQUIRY_COST_V  FFI,FA_CALENDAR_PERIODS fcp where fth.asset_id=p_asset_id
AND FTH.ASSET_ID=FFI.ASSET_ID
and fcp.period_name=ffi.period_effective
and fcp.start_Date<p_start_date
and fcp.end_Date<p_end_Date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;

SELECT NVL(SUM(B.CURRENT_COST),0)
  INTO L_Addition
  FROM FA_TRANSACTION_HISTORY_TRX_V a
 ,FA_TRANSACTION_HISTORY_BOOKS_V b
  WHERE a.asset_id = p_asset_id
  and b.asset_id = p_asset_id
  AND A.ASSET_ID = B.ASSET_ID 
  AND TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY') = NVL(p_to_date,TO_CHAR(TO_DATE(A.PERIOD_EFFECTIVE,'Mon-YY'),'Mon-YY'))
  AND A.TRANSACTION_TYPE = 'Addition'; 

IF L_ADDITION = 0 THEN
SELECT FTH.CURRENT_COST INTO L_COST
FROM FA_TRANSACTION_HISTORY_BOOKS_V FTH
WHERE FTH.ASSET_ID = P_ASSET_ID
AND TRANSACTION_TYPE = 'Addition'
AND ROWNUM=1;
END IF;
 
WHEN OTHERS THEN
NULL;
end;
RETURN L_C