ECC Oracle Asset Tracking, Oracle Asset Tracking: Assets

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: oat-capitalized-assets
Query Procedure: CSE_ECC_ASSET_UTIL_PVT.GET_ECC_ASSET_DATA_LOAD_INFO
Security Procedure: cse_ecc_datasecurity_pkg_pub.GetFilterAttributeValues

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
x.*
from
(
select  e.*, ext_attr.* , dfv.* from (select * from (select cii.rowid instance_rowid , cea.*, fab.asset_number, fat.description   asset_description,
hou_org.name organization_name,  (select name from pa_projects_all where project_id = fma.project_id) project_name,   fma.date_placed_in_service,    fab.asset_type  asset_type, CASE
      WHEN (cea.DEPRN_RESERVE = bk.COST)
      THEN 0
      ELSE (cea.deprn_amount - ( NVL (cea.deprn_adjustment_amount, 0)- NVL (cea.bonus_deprn_adjustment_amount, 0))- NVL (cea.bonus_deprn_amount, 0))
    END AS depreciation_amount ,  NVL(gl_mc_currency_pkg.CurrRound(cea.deprn_reserve, cea.CURRENCY_CODE),0) accumulated_deprn ,  (select period_name from fa_deprn_periods where book_type_code = cea.fa_book_type_code and period_close_date is null and rownum = 1) period_name, NVL((select 'Yes' from dual where (sysdate between nvl(cii.active_start_date,sysdate) and nvl(cii.active_end_date,sysdate))  and cii.instance_status_id <> 1 ), 'No' ) active_item_instance,  NVL((select 'Yes' from dual where exists(select 1 from csi_i_assets cia where cia.instance_id = cii.instance_id  and (sysdate between nvl(cia.active_start_date,sysdate) and nvl(cia.active_end_date,sysdate)))), 'No' )  active_asset,  (select batch_name from fa_mass_ext_retirements where asset_id = cea.fa_asset_id and review_status = 'POST' and rownum = 1) batch_name,  (select full_name from per_all_people_f ppf, FA_DISTRIBUTION_HISTORY fdh where fdh.assigned_to = ppf.person_id and fdh.asset_id = cea.fa_asset_id and rownum = 1) employee_name,  (select organization_code from mtl_parameters where organization_id = cea.organization_id) org_code,  (select cia1.fa_location_id from csi_i_assets cia1 where cia1.instance_id = cii.instance_id and rownum = 1) asset_location_id,  cii.operational_status_code operational_status_code ,  cii.instance_usage_code instance_usage_code,  ( select operating_unit from org_organization_definitions where organization_id = cea.organization_id ) operating_unit_id ,  ( select operating_unit_id from CSI_I_ORG_ASSIGNMENTS where instance_id = cea.instance_id ) sold_from_org_id ,  NVL((select 'Yes' from dual where exists (select 1 from fa_retirements fr where cea.fa_asset_id = fr.asset_id and fr.status <> 'PROCESSED' )), 'No' ) calc_gain_loss_flag,  NVL((select 'Yes' from dual where cii.operational_status_code IN ( 'INSTALLED' , 'OUT_OF_SERVICE' ) ), 'No' ) uninstall_eligible,  NVL((select 'Yes' from dual where cii.location_type_code <> 'INVENTORY' ), 'No' ) deploy_retire_eligible,  NVL((select 'Yes' from dual where cii.operational_status_code IN ( 'NOT_USED' ,'INSTALLED' , 'OUT_OF_SERVICE' ) ), 'No' ) deploy_move_eligible,  NVL((select 'Yes' from dual where cii.instance_usage_code IN ( 'NOT_USED' ,'INSTALLED' , 'IN_PROCESS' ) ), 'No' ) proj_transfer_eligible, 'Create_Activity_48_blue' update_costs , 'Create_Activity_48_blue' update_asset , 'Create_Activity_48_blue' update_employee ,  substr(cea.physical_location, instr(cea.physical_location,',',1,1)+1 , instr(cea.physical_location, ',' ,1,2) - instr(cea.physical_location,',' ,1,1)-1) city,  substr(cea.physical_location, instr(cea.physical_location,',',1,2)+1 , instr(cea.physical_location, ',' ,1,3) - instr(cea.physical_location,',' ,1,2)-1) county,  substr(cea.physical_location, instr(cea.physical_location,',',1,3)+1 , instr(cea.physical_location, ',' ,1,4) - instr(cea.physical_location,',' ,1,3)-1) state,  substr(cea.physical_location, instr(cea.physical_location,',' ,1,5)+1 ) country,  DECODE(NVL((select 'Yes' from dual where  cii.location_type_code = 'INVENTORY' ), 'No' ) , 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_from_inventory,  NVL((select 'Yes' from dual where  cii.location_type_code = 'INVENTORY' ), 'No' ) issue_from_inventory_flag ,  DECODE(NVL((select 'Yes' from dual where  cii.location_type_code <> 'INVENTORY' ), 'No' ) , 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') receipt_from_field,  NVL((select 'Yes' from dual where  cii.location_type_code <> 'INVENTORY' ), 'No' ) receipt_from_field_flag,  DECODE(NVL( (select 'Yes' from dual where exists (select 1 from mtl_system_items msi, mtl_parameters mp  where msi.organization_id = mp.organization_id  and msi.organization_id = cea.organization_id and msi.inventory_item_id = cea.inventory_item_id and NVL(msi.eam_item_type, 0) <> 0   and mp.eam_enabled_flag = 'Y' )), 'No' ), 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') asset_maintenance,  NVL( (select 'Yes' from dual where exists (select 1 from mtl_system_items msi, mtl_parameters mp  where msi.organization_id = mp.organization_id  and msi.organization_id = cea.organization_id and msi.inventory_item_id = cea.inventory_item_id and NVL(msi.eam_item_type, 0) <> 0   and mp.eam_enabled_flag = 'Y' )), 'No' ) eam_flag  from CSE_ECC_ASSETS_TMP cea, fa_additions_b fab, fa_additions_tl fat, fa_mass_additions fma, fa_books bk, csi_item_instances cii, hr_all_organization_units_tl hou_org  where fma.asset_number = fab.asset_number and cea.fa_asset_id = fat.asset_id and fat.asset_id = fab.asset_id and fat.language = cea.language  AND hou_org.organization_id = cea.organization_id  AND hou_org.language = cea.language  AND bk.book_type_code = cea.fa_book_type_code  AND bk.asset_id = fab.asset_id  AND bk.date_ineffective IS NULL  AND BK.TRANSACTION_HEADER_ID_OUT IS NULL  AND cii.instance_id = cea.instance_id  AND cea.language in ( 'US' ))   pivot ( max(location_type_code) as location_type_code ,
                      max(accounting_class_code) as accounting_class_code, max(organization_name) as organization_name,
					  max(asset_description) as asset_description   for LANGUAGE in ('US' "US"))) e  ,   (select "ROW_ID" "'INSTANCE_ROW_ID'","CONTEXT_VALUE" "'INSTANCE_CONTEXT_VALUE'","LAST_MAINTENANCE_DONE_BY_NAME_" "'INSTANCE_LAST_MAINTENANCE_DONE_BY_NAME_'","CONCATENATED_SEGMENTS" "'INSTANCE_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",CONTEXT "CONTEXT_VALUE",ATTRIBUTE16 "LAST_MAINTENANCE_DONE_BY_NAME_",ATTRIBUTE16 "CONCATENATED_SEGMENTS" from CSI_ITEM_INSTANCES )) dfv , ( select c1.instance_id ext_instance_id, (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10000 ) COMMS_10000 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10001 ) COMMS_10001 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10060 ) AHL_MFG_DATE_10060 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10061 ) AHL_TEMP_SERIAL_NUM_10061 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10164 ) ACTIVATION_DIAL_TONE_CO_10164 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10165 ) ADDRESS_LINE1_10165 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10166 ) ADDRESS_LINE2_10166 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10167 ) AHL_TEMP_SERIAL_NUM_10167 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10168 ) ASSIGNED_SP_ID_10168 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10169 ) AUDIT_TYPE_10169 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10170 ) SELECT_CALL_FORWARDING_10170 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 10171 ) SFM_RES_BASIC_PHONE_10171 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 11185 ) ITIL_ENVIRONMENT_11185 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 11186 ) ITIL_IMPACT_11186 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 13183 ) ITIL_ENVIRONMENT_13183 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 13184 ) ITIL_USER_COUNT_13184 , (select c2.attribute_value a from csi_iea_values c2 where c2.instance_id = c1.instance_id and c2.attribute_id = 13185 ) ITIL_IMPACT_13185  from csi_iea_values c1) ext_attr   WHERE e.instance_rowid = dfv."'INSTANCE_ROW_ID'"(+)  and ext_attr.ext_instance_id(+) = e.instance_id 
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.operating_unit_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV
Sold From Org.
x.sold_from_org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV