SELECT D.BOOK_TYPE_CODE,
a.asset_number,
a.description ASSET_DESC,
a.ASSET_TYPE,
fb.date_placed_in_service,
(SELECT PRORATE_CONVENTION_CODE
FROM FA_CONVENTION_TYPES
WHERE CONVENTION_TYPE_ID = fb.CONVENTION_TYPE_ID)
Proprate_Convention,
fc.segment1 Asset_Category_Major,
fc.segment2 Asset_Category_Minor,
a.TAG_NUMBER,
a.SERIAL_NUMBER,
a.MANUFACTURER_NAME,
a.MODEL_NUMBER Model,
a.ATTRIBUTE1 Item_Detail,
a.ATTRIBUTE2 PROJECT,
(SELECT SEGMENT1
FROM FA_ASSET_KEYWORDS
WHERE CODE_COMBINATION_ID = a.ASSET_KEY_CCID)
Asset_Key_Seg1,
fb.cost Asset_Cost,
(SELECT YTD_DEPRN
FROM FA_DEPRN_SUMMARY
WHERE asset_id = a.asset_id
AND DEPRN_SOURCE_CODE = 'DEPRN'
AND DEPRN_RUN_DATE =
(SELECT MAX (DEPRN_RUN_DATE)
FROM FA_DEPRN_SUMMARY
WHERE asset_id = a.asset_id
AND DEPRN_SOURCE_CODE = 'DEPRN'))
YTD_Depreciation,
(SELECT DEPRN_RESERVE
FROM FA_DEPRN_SUMMARY
WHERE asset_id = a.asset_id
AND DEPRN_SOURCE_CODE = 'DEPRN'
AND DEPRN_RUN_DATE =
(SELECT MAX (DEPRN_RUN_DATE)
FROM FA_DEPRN_SUMMARY
WHERE asset_id = a.asset_id
AND DEPRN_SOURCE_CODE = 'DEPRN'))
Depreciation_Reserve,
fb.cost
- (SELECT DEPRN_RESERVE
FROM FA_DEPRN_SUMMARY
WHERE asset_id = a.asset_id
AND DEPRN_SOURCE_CODE = 'DEPRN'
AND DEPRN_RUN_DATE =
(SELECT MAX (DEPRN_RUN_DATE)
FROM FA_DEPRN_SUMMARY
WHERE asset_id = a.asset_id
AND DEPRN_SOURCE_CODE = 'DEPRN'))
Net_Book_Value,
fai.invoice_number,
fai.INVOICE_LINE_DESCRIPTION,
fai.INVOICE_LINE_NUMBER,
fai.PAYABLES_COST INV_LINE_AMOUNT,
fai.VENDOR_NAME,
fai.VENDOR_NUMBER,
fai.PO_NUMBER,
fai.PROJECT_NUMBER,
fai.PROJECT_TASK_NUMBER,
fai.FEEDER_SYSTEM_NAME SOURCE,
a.CURRENT_UNITS,
(SELECT ( ppnf.first_name
|| ' '
|| ppnf.nam_information1
|| ' '
|| ppnf.nam_information2
|| ' '
|| ppnf.last_name)
EMP_NAME
FROM per_all_people_f b, per_person_names_f ppnf
WHERE d.assigned_to = b.person_id(+)
AND TRUNC (SYSDATE) BETWEEN TRUNC (b.effective_start_date)
AND NVL (b.effective_end_date,
SYSDATE)
AND TRUNC (SYSDATE) BETWEEN ppnf.effective_start_date
AND NVL (ppnf.effective_end_date,
SYSDATE)
AND b.person_id = ppnf.person_id
AND UPPER (ppnf.name_type) = 'GLOBAL')
Employee_Name,
(SELECT b.person_number
FROM per_all_people_f b, per_person_names_f ppnf
WHERE d.assigned_to = b.person_id(+)
AND TRUNC (SYSDATE) BETWEEN TRUNC (b.effective_start_date)
AND NVL (b.effective_end_date,
SYSDATE)
AND TRUNC (SYSDATE) BETWEEN ppnf.effective_start_date
AND NVL (ppnf.effective_end_date,
SYSDATE)
AND b.person_id = ppnf.person_id
AND UPPER (ppnf.name_type) = 'GLOBAL')
EMPLOYEE_NUM,
(SELECT GCC.SEGMENT1
|| '-'
|| GCC.SEGMENT2
|| '-'
|| GCC.SEGMENT3
|| '-'
|| GCC.SEGMENT4
|| '-'
|| GCC.SEGMENT5
|| '-'
|| GCC.SEGMENT6
FROM gl_code_combinations gcc
WHERE gcc.CODE_COMBINATION_ID = d.CODE_COMBINATION_ID)
Depreciation_Exp_Acct,
( l.SEGMENT1
|| '-'
|| l.SEGMENT2
|| '-'
|| l.SEGMENT3
|| '-'
|| l.segment4)
location
FROM fa_additions_vl a,
fa_locations l,
fa_categories_b c,
fa_asset_history h,
fa_distribution_history d,
fa_categories_vl fc,
fa_category_books cb,
fa_books fb,
fa_asset_invoices fai
WHERE a.asset_id = d.asset_id
AND d.location_id = l.location_id
AND h.asset_id = a.asset_id
AND h.category_id = c.category_id
AND c.category_id = fc.category_id
AND cb.category_id = fc.category_id
AND cb.book_type_code = d.book_type_code
AND d.date_ineffective IS NULL
AND a.asset_id = fb.asset_id
AND fb.period_counter_fully_retired IS NULL
AND NVL (fb.DATE_INEFFECTIVE, SYSDATE) >= SYSDATE
AND fai.asset_id = a.asset_id
AND d.BOOK_TYPE_CODE = NVL (:p_book_type, d.BOOK_TYPE_CODE)
AND a.ASSET_TYPE=NVL(:p_Asset_Type,a.ASSET_TYPE)
AND fc.segment1=NVL(:P_MAJOR_CATEGORY,fc.segment1)
-- AND a.ASSET_NUMBER = '10333'
ORDER BY a.asset_number