VIEW: APPS.FA_ADDITIONS_ASSET_V

Source

View Text - Preformatted

SELECT ad.rowid row_id , ad.asset_id , ad.asset_number , ad.asset_key_ccid , ad.current_units , ad.asset_type , ad.tag_number , ad.description , ad.asset_category_id , ad.parent_asset_id , ad.manufacturer_name , ad.serial_number , ad.model_number , ad.property_type_code , ad.property_1245_1250_code , ad.in_use_flag , ad.owned_leased , ad.new_used , ad.unit_adjustment_flag , ad.add_cost_je_flag , ad.attribute1 , ad.attribute2 , ad.attribute3 , ad.attribute4 , ad.attribute5 , ad.attribute6 , ad.attribute7 , ad.attribute8 , ad.attribute9 , ad.attribute10 , ad.attribute11 , ad.attribute12 , ad.attribute13 , ad.attribute14 , ad.attribute15 , ad.attribute16 , ad.attribute17 , ad.attribute18 , ad.attribute19 , ad.attribute20 , ad.attribute21 , ad.attribute22 , ad.attribute23 , ad.attribute24 , ad.attribute25 , ad.attribute26 , ad.attribute27 , ad.attribute28 , ad.attribute29 , ad.attribute30 , ad.attribute_category_code , ad.context , ad.lease_id , ad.last_update_date , ad.last_updated_by , ad.created_by , ad.creation_date , ad.last_update_login , pad.asset_number parent_asset , pad.description parent_description , pv.vendor_name lessor , lease.lease_number , lease.description lease_desc, lease.lessor_id , lease.attribute1 ls_attribute1 , lease.attribute2 ls_attribute2 , lease.attribute3 ls_attribute3 , lease.attribute4 ls_attribute4 , lease.attribute5 ls_attribute5 , lease.attribute6 ls_attribute6 , lease.attribute7 ls_attribute7 , lease.attribute8 ls_attribute8 , lease.attribute9 ls_attribute9 , lease.attribute10 ls_attribute10 , lease.attribute11 ls_attribute11 , lease.attribute12 ls_attribute12 , lease.attribute13 ls_attribute13 , lease.attribute14 ls_attribute14 , lease.attribute15 ls_attribute15 , lease.attribute_category_code ls_attribute_category_code , ca.category_type , ca.capitalize_flag , bk.date_placed_in_service , bk.cost , bk.book_type_code , bk.group_asset_id, ga.concatenated_segments group_asset from fa_additions ad, fa_additions pad, fa_leases lease, po_vendors pv, fa_categories ca, fa_books bk, fa_book_controls bc, fa_group_assets ga WHERE ad.parent_asset_id = pad.asset_id ( + ) and ad.lease_id = lease.lease_id ( + ) and pv.vendor_id ( + ) = lease.lessor_id and ad.asset_category_id = ca.category_id and ad.asset_id = bk.asset_id and bk.date_ineffective is null and bc.book_type_code = bk.book_type_code and bc.book_class = 'CORPORATE' and ad.asset_type != 'EXPENSED' and bk.group_asset_id = ga.group_asset_id (+) -- Bug 714445 and exists (select 1 from fa_books fabk, fa_distribution_history dh, fa_additions fad where dh.asset_id = fad.asset_id and dh.book_type_code = bk.book_type_code and dh.date_ineffective is null and fabk.asset_id = fad.asset_id and fad.asset_id = bk.asset_id and fabk.book_type_code = bk.book_type_code and fabk.date_ineffective is null and fabk.period_counter_fully_retired is null and not exists (select 1 from fa_retirements faret where faret.asset_id = fad.asset_id and faret.book_type_code = fabk.book_type_code and faret.status in ( 'PENDING', 'REINSTATE','PARTIAL')))

View Text - HTML Formatted

SELECT AD.ROWID ROW_ID
, AD.ASSET_ID
, AD.ASSET_NUMBER
, AD.ASSET_KEY_CCID
, AD.CURRENT_UNITS
, AD.ASSET_TYPE
, AD.TAG_NUMBER
, AD.DESCRIPTION
, AD.ASSET_CATEGORY_ID
, AD.PARENT_ASSET_ID
, AD.MANUFACTURER_NAME
, AD.SERIAL_NUMBER
, AD.MODEL_NUMBER
, AD.PROPERTY_TYPE_CODE
, AD.PROPERTY_1245_1250_CODE
, AD.IN_USE_FLAG
, AD.OWNED_LEASED
, AD.NEW_USED
, AD.UNIT_ADJUSTMENT_FLAG
, AD.ADD_COST_JE_FLAG
, AD.ATTRIBUTE1
, AD.ATTRIBUTE2
, AD.ATTRIBUTE3
, AD.ATTRIBUTE4
, AD.ATTRIBUTE5
, AD.ATTRIBUTE6
, AD.ATTRIBUTE7
, AD.ATTRIBUTE8
, AD.ATTRIBUTE9
, AD.ATTRIBUTE10
, AD.ATTRIBUTE11
, AD.ATTRIBUTE12
, AD.ATTRIBUTE13
, AD.ATTRIBUTE14
, AD.ATTRIBUTE15
, AD.ATTRIBUTE16
, AD.ATTRIBUTE17
, AD.ATTRIBUTE18
, AD.ATTRIBUTE19
, AD.ATTRIBUTE20
, AD.ATTRIBUTE21
, AD.ATTRIBUTE22
, AD.ATTRIBUTE23
, AD.ATTRIBUTE24
, AD.ATTRIBUTE25
, AD.ATTRIBUTE26
, AD.ATTRIBUTE27
, AD.ATTRIBUTE28
, AD.ATTRIBUTE29
, AD.ATTRIBUTE30
, AD.ATTRIBUTE_CATEGORY_CODE
, AD.CONTEXT
, AD.LEASE_ID
, AD.LAST_UPDATE_DATE
, AD.LAST_UPDATED_BY
, AD.CREATED_BY
, AD.CREATION_DATE
, AD.LAST_UPDATE_LOGIN
, PAD.ASSET_NUMBER PARENT_ASSET
, PAD.DESCRIPTION PARENT_DESCRIPTION
, PV.VENDOR_NAME LESSOR
, LEASE.LEASE_NUMBER
, LEASE.DESCRIPTION LEASE_DESC
, LEASE.LESSOR_ID
, LEASE.ATTRIBUTE1 LS_ATTRIBUTE1
, LEASE.ATTRIBUTE2 LS_ATTRIBUTE2
, LEASE.ATTRIBUTE3 LS_ATTRIBUTE3
, LEASE.ATTRIBUTE4 LS_ATTRIBUTE4
, LEASE.ATTRIBUTE5 LS_ATTRIBUTE5
, LEASE.ATTRIBUTE6 LS_ATTRIBUTE6
, LEASE.ATTRIBUTE7 LS_ATTRIBUTE7
, LEASE.ATTRIBUTE8 LS_ATTRIBUTE8
, LEASE.ATTRIBUTE9 LS_ATTRIBUTE9
, LEASE.ATTRIBUTE10 LS_ATTRIBUTE10
, LEASE.ATTRIBUTE11 LS_ATTRIBUTE11
, LEASE.ATTRIBUTE12 LS_ATTRIBUTE12
, LEASE.ATTRIBUTE13 LS_ATTRIBUTE13
, LEASE.ATTRIBUTE14 LS_ATTRIBUTE14
, LEASE.ATTRIBUTE15 LS_ATTRIBUTE15
, LEASE.ATTRIBUTE_CATEGORY_CODE LS_ATTRIBUTE_CATEGORY_CODE
, CA.CATEGORY_TYPE
, CA.CAPITALIZE_FLAG
, BK.DATE_PLACED_IN_SERVICE
, BK.COST
, BK.BOOK_TYPE_CODE
, BK.GROUP_ASSET_ID
, GA.CONCATENATED_SEGMENTS GROUP_ASSET
FROM FA_ADDITIONS AD
, FA_ADDITIONS PAD
, FA_LEASES LEASE
, PO_VENDORS PV
, FA_CATEGORIES CA
, FA_BOOKS BK
, FA_BOOK_CONTROLS BC
, FA_GROUP_ASSETS GA
WHERE AD.PARENT_ASSET_ID = PAD.ASSET_ID ( + )
AND AD.LEASE_ID = LEASE.LEASE_ID ( + )
AND PV.VENDOR_ID ( + ) = LEASE.LESSOR_ID
AND AD.ASSET_CATEGORY_ID = CA.CATEGORY_ID
AND AD.ASSET_ID = BK.ASSET_ID
AND BK.DATE_INEFFECTIVE IS NULL
AND BC.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND BC.BOOK_CLASS = 'CORPORATE'
AND AD.ASSET_TYPE != 'EXPENSED'
AND BK.GROUP_ASSET_ID = GA.GROUP_ASSET_ID (+) -- BUG 714445
AND EXISTS (SELECT 1
FROM FA_BOOKS FABK
, FA_DISTRIBUTION_HISTORY DH
, FA_ADDITIONS FAD
WHERE DH.ASSET_ID = FAD.ASSET_ID
AND DH.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND DH.DATE_INEFFECTIVE IS NULL
AND FABK.ASSET_ID = FAD.ASSET_ID
AND FAD.ASSET_ID = BK.ASSET_ID
AND FABK.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND FABK.DATE_INEFFECTIVE IS NULL
AND FABK.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND NOT EXISTS (SELECT 1
FROM FA_RETIREMENTS FARET
WHERE FARET.ASSET_ID = FAD.ASSET_ID
AND FARET.BOOK_TYPE_CODE = FABK.BOOK_TYPE_CODE
AND FARET.STATUS IN ( 'PENDING'
, 'REINSTATE'
, 'PARTIAL')))