FA Local Book Converted to USD Report
SELECT AL1.Book_Name,
AL1.Asset_Number,
AL1.Asset_Description,
AL1.Asset_Category,
AL1.Asset_Subcategory,
AL1.Tag_Number,
AL1.Asset_Comments_Attr2,
AL1.Asset_Key,
to_char(AL1.Placed_In_Service,'yyyy-mm-dd') Placed_In_Service,
to_char(AL1.Creation_Date_Addn,'yyyy-mm-dd') Creation_Date_Addn,
AL1.Country,
AL1.State,
AL1.City,
AL1.Building,
AL1.Room,
AL1.Cost_Acct,
AL1.Reserve_Acct,
AL1.Exp_Glbl_Acct,
AL1.Exp_Dept,
AL1.Deprn_Method,
AL1.Life_Months,
AL1.Remaining_Life_Months,
AL1.Depreciate,
AL1.Original_Cost,
AL1.Current_Cost,
AL1.Ytd_Deprn_Amt,
AL1.Life_Deprn_Amount,
AL1.Net_Book_Value,
ROUND(AL1.Original_Cost * AL2.USD_Rate, 2) Org_Cost_USD,
ROUND(AL1.Current_Cost * AL2.USD_Rate, 2) Curr_Cost_USD,
ROUND(AL1.Ytd_Deprn_Amt * AL2.USD_Rate, 2) YTD_Deprn_USD,
ROUND(AL1.Life_Deprn_Amount * AL2.USD_Rate, 2) Life_Deprn_USD,
ROUND(AL1.Net_Book_Value * AL2.USD_Rate, 2) NBV_USD,
AL1.Deprn_Amount,
ROUND(AL1.Deprn_Amount * AL2.USD_Rate, 2) Deprn_Amt_USD_Calc
FROM (SELECT FAB.BOOK_TYPE_CODE Book_Name,
FABC.BOOK_CLASS Book_Class,
FADB.ASSET_NUMBER Asset_Number,
FADT.DESCRIPTION Asset_Description,
SUBSTR(FADB.ATTRIBUTE_CATEGORY_CODE,1,(INSTR(FADB.ATTRIBUTE_CATEGORY_CODE,'.') - 1))
Asset_Category,
SUBSTR(FADB.ATTRIBUTE_CATEGORY_CODE,(INSTR(FADB.ATTRIBUTE_CATEGORY_CODE,'.') + 1),
LENGTH(FADB.ATTRIBUTE_CATEGORY_CODE)) Asset_Subcategory,
FADB.ATTRIBUTE_CATEGORY_CODE Category_Subcategory,
FADB.TAG_NUMBER Tag_Number,
FASK.SEGMENT1 Asset_Key,
FADB2.ASSET_NUMBER Parent_Asset_Number,
FAB.DATE_PLACED_IN_SERVICE Placed_In_Service,
GLP1.PERIOD_NAME Addn_Period_MON_YY,
ADNP.ADDN_FA_PERIOD Addn_FA_Period,
FADB.CREATION_DATE Creation_Date_Addn,
FADB.LAST_UPDATE_DATE Last_Updt_Addn,
FAB.LAST_UPDATE_DATE Last_Updt_Books,
FNDU.USERNAME Created_By_ID,
--FNDU.DESCRIPTION Created_By_Name,
FADB.ATTRIBUTE2 Asset_Comments_Attr2,
FADB.ATTRIBUTE3 Orig_Asset_Num_Attr3,
FADB.ATTRIBUTE4 Orig_Service_Dt_Attr4,
FADB.ATTRIBUTE5 Impairment_Amt_Attr5,
FADB.ATTRIBUTE6 Custom_Declaration_Attr6,
FADB.ATTRIBUTE7 Held_For_Sale,
FADB.ATTRIBUTE8 Orig_Cost_Attr8,
FADB.ATTRIBUTE9 Child_Extend_Life,
FADB.ATTRIBUTE10 UOP_Indicator,
FADB.ATTRIBUTE11 EAM_Asset_Number,
FADB.SERIAL_NUMBER Serial_Number,
FADB.MANUFACTURER_NAME Manufacturer_Name,
FADB.MODEL_NUMBER Model_Number,
FADB.OWNED_LEASED Owned_Leased,
FADB.NEW_USED New_Used,
FAL.SEGMENT1 Country,
FAL.SEGMENT2 State,
FAL.SEGMENT3 City,
FAL.SEGMENT4 Building,
FAL.SEGMENT5 Room,
FAB.DEPRN_START_DATE Deprn_Start_Date,
--FAB.DEPRN_METHOD_CODE Deprn_Method,
(SELECT fm.method_code from fa_methods fm
where fm.METHOD_ID = fab.METHOD_ID) Deprn_Method,
--FAB.LIFE_IN_MONTHS Life_Months,
(SELECT life_in_months from fa_methods fm
where fm.METHOD_ID = fab.METHOD_ID ) Life_Months,
/* CASE WHEN (MONTHS_BETWEEN(CASE WHEN TO_CHAR(FCP.START_DATE,'YYYY') < 2000
THEN add_months(ADD_MONTHS(TO_DATE (FCP.PERIOD_NAME, 'MON-YY'),FAB.LIFE_IN_MONTHS), -(12*100))
ELSE ADD_MONTHS(TO_DATE (FCP.PERIOD_NAME, 'MON-YY'),FAB.LIFE_IN_MONTHS)
END,
TO_DATE(FDP.PERIOD_NAME,'MON-YY')
) - 1) > 0
THEN MONTHS_BETWEEN(CASE WHEN TO_CHAR(FCP.START_DATE,'YYYY') < 2000
THEN add_months(ADD_MONTHS(TO_DATE (FCP.PERIOD_NAME, 'MON-YY'),FAB.LIFE_IN_MONTHS), -(12*100))
ELSE ADD_MONTHS(TO_DATE (FCP.PERIOD_NAME, 'MON-YY'),FAB.LIFE_IN_MONTHS)
END,
TO_DATE(FDP.PERIOD_NAME,'MON-YY')
) - 1
ELSE 0 END Remaining_Life_Months, */
(SELECT life_in_months from fa_methods fm
where fm.METHOD_ID = fab.METHOD_ID) - (fdp.period_counter - fdp2.period_counter) Remaining_Life_Months,
--FAB.RETIREMENT_PENDING_FLAG Retirement_Pending_Flag,
CASE WHEN NVL(FAB.PERIOD_COUNTER_FULLY_RETIRED,0) = 0
THEN 'N' ELSE 'Y' END Retired_Y_N,
GLCC.SEGMENT1 Exp_Comp,
GLCC.SEGMENT2 Exp_Glbl_Acct,
GLCC.SEGMENT3 Exp_Lcl_Acct,
GLCC.SEGMENT4 Exp_Dept,
GLCC.SEGMENT5 Exp_Intco,
GLCC.SEGMENT6 Exp_Project,
GLCC.SEGMENT7 Exp_BU,
GLCC.SEGMENT8 Exp_Future,
GLC2.SEGMENT2 Cost_Acct,
GLC2.SEGMENT3 Cost_Acct_Local,
GLC3.SEGMENT2 Reserve_Acct,
GLC3.SEGMENT3 Reserve_Acct_Local,
GLC4.SEGMENT2 Clearing_Acct,
GLC4.SEGMENT3 Clear_Acct_Local,
GLCC.SEGMENT1 || '.' || GLCC.SEGMENT4 Company_Dept,
(FAB.ORIGINAL_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Original_Cost,
(FAB.COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Current_Cost,
CASE WHEN FDP.FISCAL_YEAR = FDP2.FISCAL_YEAR
THEN (FDS.YTD_DEPRN / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED)
ELSE 0 END Ytd_Deprn_Amt,
(FDS.DEPRN_RESERVE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Life_Deprn_Amount,
(FDSC.DEPRN_AMOUNT / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Deprn_Amount,
(FDS.IMPAIRMENT_RESERVE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Impairment_Reserve,
((FAB.COST - FDS.DEPRN_RESERVE - FDS.IMPAIRMENT_RESERVE)
/ FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Net_Book_Value ,
(FAB.ADJUSTED_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Adjusted_Cost,
(FAB.SALVAGE_VALUE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Salvage_Value,
FAB.PERCENT_SALVAGE_VALUE Percent_Salvage,
(FAB.RECOVERABLE_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Recoverable_Cost,
(FAB.ADJUSTED_RECOVERABLE_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED) Adj_Recoverable_Cost,
--
CASE WHEN FABC.BOOK_CLASS = 'TAX' or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%')
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND((FAB.ORIGINAL_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND((FAMB.ORIGINAL_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND((FAB.ORIGINAL_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Original_Cost_USD,
--
CASE WHEN FABC.BOOK_CLASS = 'TAX' or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%')
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND((FAB.COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND((FAMB.COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND((FAB.COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Current_Cost_USD,
--
CASE WHEN FDP.FISCAL_YEAR = FDP2.FISCAL_YEAR AND (FABC.BOOK_CLASS ='TAX'
or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%'))
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND((FDS.YTD_DEPRN / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND((MCDT.YTD_DEPRN / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND((FDS.YTD_DEPRN / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Ytd_Deprn_USD ,
--
CASE WHEN FABC.BOOK_CLASS = 'TAX' or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%')
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND((FDS.DEPRN_RESERVE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND((MCDT.DEPRN_RESERVE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND((FDS.DEPRN_RESERVE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Life_Deprn_USD,
--
CASE WHEN FABC.BOOK_CLASS = 'TAX' or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%')
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND((FDSC.DEPRN_AMOUNT / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND((MDSC.DEPRN_AMOUNT / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND((FDSC.DEPRN_AMOUNT / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Deprn_Amt_USD,
--
CASE WHEN FABC.BOOK_CLASS = 'TAX' or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%')
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND((FDS.IMPAIRMENT_RESERVE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND((MCDT.IMPAIRMENT_RESERVE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND((FDS.IMPAIRMENT_RESERVE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Impairmt_Resv_USD,
--
CASE WHEN FABC.BOOK_CLASS = 'TAX' or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%')
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND(((FAB.COST - FDS.DEPRN_RESERVE - FDS.IMPAIRMENT_RESERVE)
/ FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND(((FAMB.COST - MCDT.DEPRN_RESERVE - MCDT.IMPAIRMENT_RESERVE)
/ FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND(((FAB.COST - FDS.DEPRN_RESERVE - FDS.IMPAIRMENT_RESERVE)
/ FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Net_Book_Value_USD,
--
CASE WHEN FABC.BOOK_CLASS = 'TAX' or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%')
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND((FAB.ADJUSTED_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND((FAMB.ADJUSTED_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND((FAB.ADJUSTED_COST / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Adjusted_Cost_USD,
--
CASE WHEN FABC.BOOK_CLASS = 'TAX' or (FABC.BOOK_CLASS = 'TAX' and FABC.BOOK_TYPE_CODE LIKE '%FA ENP%')
THEN CASE WHEN LGR.CURRENCY_CODE = 'USD'
THEN ROUND((FAB.SALVAGE_VALUE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
WHEN SUBSTR(FAB.BOOK_TYPE_CODE,1,3) NOT IN('612','613','624')
THEN ROUND((FAMB.SALVAGE_VALUE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED),2)
ELSE ROUND((FAB.SALVAGE_VALUE / FADB.CURRENT_UNITS * FADH.UNITS_ASSIGNED * JRT.CONVERSION_RATE),2)
END
ELSE NULL
END Salvage_Value_USD,
--
FAB.RATE_ADJUSTMENT_FACTOR Rate_Adj_Factor,
--
CASE WHEN FAB.RATE_ADJUSTMENT_FACTOR < 1
THEN 'Y'
ELSE 'N'
END Amort_Adj,
--
--FAB.PRORATE_CONVENTION_CODE Prorate_Conv,
FAB.PRORATE_DATE Prorate_Date,
--FAB.COST_CHANGE_FLAG Cost_Change_Flag,
--FAB.ADJUSTMENT_REQUIRED_STATUS Adj_Required_Status,
FAB.CAPITALIZE_FLAG Capitalize,
FAB.DEPRECIATE_FLAG Depreciate,
FAB.UNIT_OF_MEASURE Unit_Of_Measure,
FAB.ALLOWED_DEPRN_LIMIT Allowed_Deprn_Limit_Pct,
FAB.ALLOWED_DEPRN_LIMIT_AMOUNT Allowed_Deprn_Limit_Amt,
FADB.ASSET_TYPE Asset_Type,
FADB.CURRENT_UNITS Total_Units,
FADH.UNITS_ASSIGNED Units_Assigned,
/*PAPF.FULL_NAME Assigned_To_Name,
PAPF.EMPLOYEE_NUMBER Assigned_To_Emp_No,*/
FAB.ASSET_ID Asset_Id,
FADH.CODE_COMBINATION_ID Code_Combination_Id,
TO_CHAR(FAB.DATE_PLACED_IN_SERVICE,'MON-YYYY') Depn_Start_Month,
FABC.LAST_PERIOD_COUNTER Last_Period_Counter,
FADB.ASSET_CATEGORY_ID Asset_Category_Id,
FADH.DISTRIBUTION_ID Distribution_Id,
FAB.TRANSACTION_HEADER_ID_IN Transaction_Hdr_Id_In,
TRUNC(SYSDATE) Trunc_Sysdate,
FADB.PARENT_ASSET_ID Parent_Asset_Id
FROM FA_ADDITIONS_B FADB,
FA_ADDITIONS_B FADB2,
FA_ADDITIONS_TL FADT,
FA_BOOKS FAB,
FA_BOOK_CONTROLS FABC,
GL_LEDGERS LGR,
FA_ASSET_KEYWORDS FASK,
FA_DISTRIBUTION_HISTORY FADH,
--FA_DISTRIBUTION_ACCOUNTS FADA, --FA_DISTRIBUTION_ACCOUNTS not there in cloud
FA_LOCATIONS FAL,
GL_CODE_COMBINATIONS GLCC,
GL_CODE_COMBINATIONS GLC2,
GL_CODE_COMBINATIONS GLC3,
GL_CODE_COMBINATIONS GLC4,
GL_PERIODS GLP1,
per_USERs FNDU,
/*APPS.PER_ALL_PEOPLE_F PAPF,*/
FA_DEPRN_SUMMARY FDS,
FA_DEPRN_PERIODS FDP,
FA_DEPRN_PERIODS FDP2,
FA_CALENDAR_PERIODS FCP,
--
( select asset_id ASSET_ID,
book_type_code BOOK_TYPE_CODE,
ytd_deprn YTD_DEPRN,
deprn_reserve DEPRN_RESERVE,
impairment_reserve IMPAIRMENT_RESERVE
FROM FA_MC_DEPRN_SUMMARY MCDS
WHERE PERIOD_COUNTER = (SELECT MAX(PERIOD_COUNTER)
FROM FA_MC_DEPRN_SUMMARY MMCD
WHERE MMCD.ASSET_ID = MCDS.ASSET_ID
AND MMCD.BOOK_TYPE_CODE = MCDS.BOOK_TYPE_CODE)
) MCDT,
--
( select ldep.asset_id ASSET_ID,
ldep.book_type_code BOOK_TYPE_CODE,
ldep.deprn_amount DEPRN_AMOUNT
FROM FA_DEPRN_SUMMARY LDEP
WHERE LDEP.PERIOD_COUNTER = (SELECT MAX(PERIOD_COUNTER)
FROM FA_DEPRN_PERIODS LDRN
WHERE LDRN.BOOK_TYPE_CODE = LDEP.BOOK_TYPE_CODE
AND LDRN.DEPRN_RUN = 'Y')
) FDSC,
--
( select mdep.asset_id ASSET_ID,
mdep.book_type_code BOOK_TYPE_CODE,
mdep.deprn_amount DEPRN_AMOUNT
FROM FA_MC_DEPRN_SUMMARY MDEP
WHERE MDEP.PERIOD_COUNTER = (SELECT MAX(PERIOD_COUNTER)
FROM FA_DEPRN_PERIODS MDRN
WHERE MDRN.BOOK_TYPE_CODE = MDEP.BOOK_TYPE_CODE
AND MDRN.DEPRN_RUN = 'Y')
) MDSC,
--
( select mbks.asset_id ASSET_ID,
mbks.book_type_code BOOK_TYPE_CODE,
mbks.cost COST,
mbks.original_cost ORIGINAL_COST,
mbks.adjusted_cost ADJUSTED_COST,
mbks.salvage_value SALVAGE_VALUE
FROM FA_MC_BOOKS MBKS
WHERE MBKS.DATE_INEFFECTIVE IS NULL
) FAMB,
--
( select RAT.CONVERSION_RATE
FROM GL_DAILY_RATES RAT
WHERE RAT.CONVERSION_TYPE = 'Corporate'
and RAT.TO_CURRENCY = 'USD'
and RAT.FROM_CURRENCY = 'JPY'
and RAT.CONVERSION_DATE = TRUNC(SYSDATE)
) JRT,
--
( select bks.asset_id ASSET_ID,
bks.book_type_code BOOK_TYPE_CODE,
dpr.period_name ADDN_FA_PERIOD
from fa_books bks,
fa_deprn_periods dpr
where bks.book_type_code = dpr.book_type_code
and bks.date_effective between dpr.period_open_date and nvl(dpr.period_close_date,sysdate+1)
and bks.transaction_header_id_in = (select min(bks2.transaction_header_id_in)
from fa_books bks2
where bks2.book_type_code = bks.book_type_code
and bks2.asset_id = bks.asset_id)
) ADNP
--
WHERE rownum<=20
and FADB.ASSET_ID = FAB.ASSET_ID
AND FADB.PARENT_ASSET_ID = FADB2.ASSET_ID(+)
AND FAB.ASSET_ID = FADT.ASSET_ID
AND FADT.LANGUAGE = 'US'
AND FAB.BOOK_TYPE_CODE = FABC.BOOK_TYPE_CODE
AND FADB.ASSET_KEY_CCID = FASK.CODE_COMBINATION_ID(+)
AND FAB.ASSET_ID = FADH.ASSET_ID
AND FADH.LOCATION_ID = FAL.LOCATION_ID
--AND FADA.DEPRN_EXPENSE_ACCOUNT_CCID = GLCC.CODE_COMBINATION_ID
AND TRUNC(FADB.CREATION_DATE) BETWEEN GLP1.START_DATE AND GLP1.END_DATE
AND GLP1.PERIOD_SET_NAME = '4-4-5'
AND FADB.CREATED_BY = FNDU.USERNAME
/*AND FADH.ASSIGNED_TO = PAPF.PERSON_ID(+)*/
AND FDP.PERIOD_COUNTER = FABC.LAST_PERIOD_COUNTER
AND FDP.BOOK_TYPE_CODE = FABC.BOOK_TYPE_CODE
AND FDP.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FADB.ASSET_ID = FDS.ASSET_ID
AND FDS.PERIOD_COUNTER = (SELECT MAX(PERIOD_COUNTER)
FROM FA_DEPRN_SUMMARY FDS2
WHERE FADB.ASSET_ID = FDS2.ASSET_ID
AND FDP.BOOK_TYPE_CODE = FDS2.BOOK_TYPE_CODE)
AND FAB.DATE_PLACED_IN_SERVICE BETWEEN FCP.START_DATE(+) AND FCP.END_DATE(+)
/*AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE(+)) AND TRUNC(PAPF.EFFECTIVE_END_DATE(+))*/
AND FDP2.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FDP2.PERIOD_COUNTER = FDS.PERIOD_COUNTER
AND FAB.DATE_INEFFECTIVE IS NULL
AND FABC.DATE_INEFFECTIVE IS NULL
AND FADH.DATE_INEFFECTIVE IS NULL
--AND FADA.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
--AND FADA.DISTRIBUTION_ID = FADH.DISTRIBUTION_ID
--AND GLC2.CODE_COMBINATION_ID = FADA.ASSET_COST_ACCOUNT_CCID
-- AND GLC3.CODE_COMBINATION_ID = FADA.DEPRN_RESERVE_ACCOUNT_CCID
--AND GLC4.CODE_COMBINATION_ID = FADA.ASSET_CLEARING_ACCOUNT_CCID
AND MCDT.ASSET_ID(+) = FAB.ASSET_ID
AND MCDT.BOOK_TYPE_CODE(+) = FAB.BOOK_TYPE_CODE
AND FDSC.ASSET_ID(+) = FAB.ASSET_ID
AND FDSC.BOOK_TYPE_CODE(+) = FAB.BOOK_TYPE_CODE
AND MDSC.ASSET_ID(+) = FAB.ASSET_ID
AND MDSC.BOOK_TYPE_CODE(+) = FAB.BOOK_TYPE_CODE
AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND FAMB.BOOK_TYPE_CODE(+) = FAB.BOOK_TYPE_CODE
AND FAMB.ASSET_ID(+) = FAB.ASSET_ID
AND LGR.LEDGER_ID = FABC.SET_OF_BOOKS_ID
AND ADNP.ASSET_ID(+) = FAB.ASSET_ID
AND ADNP.BOOK_TYPE_CODE(+) = FAB.BOOK_TYPE_CODE) AL1,
(SELECT bk.asset_id Asset_Id,
bk.book_type_code Book_Name,
bk.cost Original_Cost,
mc.cost Orig_Cost_USD,
case when bk.cost = 0
then 0
else round((mc.cost / bk.cost),10)
end USD_Rate
from fa_books bk,
fa_mc_books mc
where bk.asset_id = mc.asset_id
and bk.book_type_code = mc.book_type_code
and bk.transaction_header_id_out is null
and mc.transaction_header_id_out is null) AL2
WHERE (AL1.Asset_Id = AL2.Asset_Id(+))
--AND (AL1.Book_Name) IN nvl(:P_BOOK_NAME,AL1.Book_Name)
AND (AL1.Book_Name IN (:P_BOOK_NAME)
or decode (:P_BOOK_NAME,null,1,2)=1)
AND (NOT AL2.Book_Name(+) LIKE '%ENP%')
ORDER BY al1.Asset_Number
No comments:
Post a Comment