ECC Oracle Asset Tracking, Oracle Asset Tracking: Assets

Description
Categories: Enterprise Command Center
Columns: Instance Rowid, Ecc Spec Id, Instance Id, Fa Asset Id, Fa Book Type Code, Move Txn Eligible, Major Category, Minor Category, Instance Number, Serial Number ...
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
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 CSI_ITEM_INSTANCES_DFV) 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