ECC Oracle Asset Tracking, Oracle Asset Tracking: Instances
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: oat-assets
Query Procedure: CSE_ECC_UTIL_PVT.GET_ECC_ASSET_DATA_LOAD_INFO
Security Procedure: cse_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Dataset Key: oat-assets
Query Procedure: CSE_ECC_UTIL_PVT.GET_ECC_ASSET_DATA_LOAD_INFO
Security Procedure: cse_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Oracle Asset Tracking, Oracle Asset Tracking: Instances and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( WITH eccinst AS ( select * FROM ((SELECT /*+ leading( cei cii) index(cii CSI_ITEM_INSTANCES_U01) no_merge(cei) */ cii.rowid instance_rowid, cei.*, hl.address1 || ',' || hl.city || ',' || hl.county || ',' || hl.state || ',' || hl.postal_code || ',' || hl.country address , hl.address1, hl.country, hl.city, hl.postal_code, hl.state, hl.county, hl.province, 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, DECODE(cei.issue_field_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_to_proj_field_alert, DECODE(cei.issue_proj_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_to_proj_alert, DECODE(cei.create_so_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') create_so_alert, (select segment1 from pa_projects_all where project_id = cei.projectid) project_number, DECODE(NVL(to_char(cei.projectid), 'No'), 'No' , 'ecc_blank' , 'Create_Activity_48_blue') proj_cap_db, NVL(( select 'Yes' from csi_item_instances cii1 where cii1.instance_id = cei.instance_id AND cii1.operational_status_code IN ('INSTALLED', 'OUT_OF_SERVICE')), 'No') put_in_service_eligible, flvl.meaning location_type, flva.meaning accounting_class, flvi.meaning instance_usage, flvo.meaning operational_status, ood1.organization_code org_code, ood1.chart_of_accounts_id chart_of_accounts_id, hou_org.name ORGANIZATION_NAME, hou_ou.name OPERATING_UNIT , cii.active_end_date active_end_date, cii.instance_status_id instance_status_id from cse_ecc_instances_v cei, org_organization_definitions ood1 , hz_party_sites hps, hz_locations hl, csi_item_instances cii , fnd_lookup_values flvl,fnd_lookup_values flva, fnd_lookup_values flvi, fnd_lookup_values flvo, hr_all_organization_units_tl hou_org, hr_all_organization_units_tl hou_ou where cii.instance_id = cei.instance_id AND cei.language in ( 'US' ) AND hps.party_site_id = cei.location_id AND hps.location_id = hl.location_id AND flvl.lookup_code = cii.location_type_code and flvl.lookup_type = 'CSI_INST_LOCATION_SOURCE_CODE' AND flva.lookup_code = cii.accounting_class_code and flva.lookup_type = 'CSI_ACCOUNTING_CLASS_CODE' AND flvi.lookup_code = cii.instance_usage_code and flvi.lookup_type = 'CSI_INSTANCE_USAGE_CODE' AND flvo.lookup_code = cii.operational_status_code and flvo.lookup_type = 'CSI_OPERATIONAL_STATUS_CODE' and flvl.language = flvi.language and flvo.language = flvi.language and flvo.language = flva.language and cei.language = flva.language AND hou_org.organization_id = cii.last_vld_organization_id AND hou_ou.organization_id = ood1.operating_unit and ood1.organization_id = cii.last_vld_organization_id AND hou_org.language = hou_ou.language and hou_org.language = flva.language AND cei.location_type_code = 'HZ_PARTY_SITES') UNION (SELECT /*+ leading( cei cii) index(cii CSI_ITEM_INSTANCES_U01) no_merge(cei) */ cii.rowid instance_rowid, cei.*, hl.address1 || ',' || hl.city || ',' || hl.county || ',' || hl.state || ',' || hl.postal_code || ',' || hl.country address , hl.address1, hl.country, hl.city, hl.postal_code, hl.state, hl.county, hl.province, 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, DECODE(cei.issue_field_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_to_proj_field_alert, DECODE(cei.issue_proj_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_to_proj_alert, DECODE(cei.create_so_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') create_so_alert, (select segment1 from pa_projects_all where project_id = cei.projectid) project_number, DECODE(NVL(to_char(cei.projectid), 'No'), 'No' , 'ecc_blank' , 'Create_Activity_48_blue') proj_cap_db, NVL(( select 'Yes' from csi_item_instances cii1 where cii1.instance_id = cei.instance_id AND cii1.operational_status_code IN ('INSTALLED', 'OUT_OF_SERVICE')), 'No') put_in_service_eligible, flvl.meaning location_type, flva.meaning accounting_class, flvi.meaning instance_usage, flvo.meaning operational_status, ood1.organization_code org_code, ood1.chart_of_accounts_id chart_of_accounts_id, hou_org.name ORGANIZATION_NAME, hou_ou.name OPERATING_UNIT , cii.active_end_date active_end_date, cii.instance_status_id instance_status_id from cse_ecc_instances_v cei, org_organization_definitions ood1 , hz_locations hl, csi_item_instances cii, fnd_lookup_values flvl,fnd_lookup_values flva, fnd_lookup_values flvi, fnd_lookup_values flvo, hr_all_organization_units_tl hou_org, hr_all_organization_units_tl hou_ou where cii.instance_id = cei.instance_id AND cei.language in ( 'US' ) AND cei.location_id = hl.location_id AND flvl.lookup_code = cii.location_type_code and flvl.lookup_type = 'CSI_INST_LOCATION_SOURCE_CODE' AND flva.lookup_code = cii.accounting_class_code and flva.lookup_type = 'CSI_ACCOUNTING_CLASS_CODE' AND flvi.lookup_code = cii.instance_usage_code and flvi.lookup_type = 'CSI_INSTANCE_USAGE_CODE' AND flvo.lookup_code = cii.operational_status_code and flvo.lookup_type = 'CSI_OPERATIONAL_STATUS_CODE' and flvl.language = flvi.language and flvo.language = flvi.language and flvo.language = flva.language and cei.language = flva.language AND hou_org.organization_id = cii.last_vld_organization_id AND hou_ou.organization_id = ood1.operating_unit and ood1.organization_id = cii.last_vld_organization_id AND hou_org.language = hou_ou.language and hou_org.language = flva.language AND cei.location_type_code = 'HZ_LOCATIONS') UNION (SELECT /*+ leading( cei cii) index(cii CSI_ITEM_INSTANCES_U01) no_merge(cei) */ cii.rowid instance_rowid, cei.*, hl.address_line_1 || ',' || hl.town_or_city || ',' || hl.region_2 || ',' || hl.postal_code || ',' || hl.country address , hl.address_line_1, hl.country, hl.town_or_city, hl.postal_code, hl.region_2, null, null, 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, DECODE(cei.issue_field_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_to_proj_field_alert, DECODE(cei.issue_proj_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_to_proj_alert, DECODE(cei.create_so_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') create_so_alert, (select segment1 from pa_projects_all where project_id = cei.projectid) project_number, DECODE(NVL(to_char(cei.projectid), 'No'), 'No' , 'ecc_blank' , 'Create_Activity_48_blue') proj_cap_db, NVL(( select 'Yes' from csi_item_instances cii1 where cii1.instance_id = cei.instance_id AND cii1.operational_status_code IN ('INSTALLED', 'OUT_OF_SERVICE')), 'No') put_in_service_eligible, flvl.meaning location_type, flva.meaning accounting_class, flvi.meaning instance_usage, flvo.meaning operational_status, ood1.organization_code org_code, ood1.chart_of_accounts_id chart_of_accounts_id, hou_org.name ORGANIZATION_NAME, hou_ou.name OPERATING_UNIT , cii.active_end_date active_end_date, cii.instance_status_id instance_status_id from cse_ecc_instances_v cei, org_organization_definitions ood1 , hr_locations hl, csi_item_instances cii, fnd_lookup_values flvl,fnd_lookup_values flva, fnd_lookup_values flvi, fnd_lookup_values flvo, hr_all_organization_units_tl hou_org, hr_all_organization_units_tl hou_ou where cii.instance_id = cei.instance_id AND cei.language in ( 'US' ) AND cei.location_id = hl.location_id AND flvl.lookup_code = cii.location_type_code and flvl.lookup_type = 'CSI_INST_LOCATION_SOURCE_CODE' AND flva.lookup_code = cii.accounting_class_code and flva.lookup_type = 'CSI_ACCOUNTING_CLASS_CODE' AND flvi.lookup_code = cii.instance_usage_code and flvi.lookup_type = 'CSI_INSTANCE_USAGE_CODE' AND flvo.lookup_code = cii.operational_status_code and flvo.lookup_type = 'CSI_OPERATIONAL_STATUS_CODE' and flvl.language = flvi.language and flvo.language = flvi.language and flvo.language = flva.language and cei.language = flva.language AND hou_org.organization_id = cii.last_vld_organization_id AND hou_ou.organization_id = ood1.operating_unit and ood1.organization_id = cii.last_vld_organization_id AND hou_org.language = hou_ou.language and hou_org.language = flva.language AND cei.location_type_code = 'INTERNAL_SITE') UNION (SELECT /*+ leading( cei cii) index(cii CSI_ITEM_INSTANCES_U01) no_merge(cei) */ cii.rowid instance_rowid, cei.*, null, null, null, null, null, null, null, null , 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, DECODE(cei.issue_field_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_to_proj_field_alert, DECODE(cei.issue_proj_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') issue_to_proj_alert, DECODE(cei.create_so_eligible, 'Yes' , 'Create_Activity_48_blue' , 'No' , 'ecc_blank') create_so_alert, (select segment1 from pa_projects_all where project_id = cei.projectid) project_number, DECODE(NVL(to_char(cei.projectid), 'No'), 'No' , 'ecc_blank' , 'Create_Activity_48_blue') proj_cap_db, NVL(( select 'Yes' from csi_item_instances cii1 where cii1.instance_id = cei.instance_id AND cii1.operational_status_code IN ('INSTALLED', 'OUT_OF_SERVICE')), 'No') put_in_service_eligible, flvl.meaning location_type, flva.meaning accounting_class, flvi.meaning instance_usage, flvo.meaning operational_status, ood1.organization_code org_code, ood1.chart_of_accounts_id chart_of_accounts_id, hou_org.name ORGANIZATION_NAME, hou_ou.name OPERATING_UNIT , cii.active_end_date active_end_date, cii.instance_status_id instance_status_id from cse_ecc_instances_v cei, csi_item_instances cii, org_organization_definitions ood1 , fnd_lookup_values flvl,fnd_lookup_values flva, fnd_lookup_values flvi, fnd_lookup_values flvo, hr_all_organization_units_tl hou_org, hr_all_organization_units_tl hou_ou where cii.instance_id = cei.instance_id AND cei.language in ( 'US' ) AND flvl.lookup_code = cii.location_type_code and flvl.lookup_type = 'CSI_INST_LOCATION_SOURCE_CODE' AND flva.lookup_code = cii.accounting_class_code and flva.lookup_type = 'CSI_ACCOUNTING_CLASS_CODE' AND flvi.lookup_code = cii.instance_usage_code and flvi.lookup_type = 'CSI_INSTANCE_USAGE_CODE' AND flvo.lookup_code = cii.operational_status_code and flvo.lookup_type = 'CSI_OPERATIONAL_STATUS_CODE' and flvl.language = flvi.language and flvo.language = flvi.language and flvo.language = flva.language and cei.language = flva.language AND hou_org.organization_id = cii.last_vld_organization_id AND hou_ou.organization_id = ood1.operating_unit and ood1.organization_id = cii.last_vld_organization_id AND hou_org.language = hou_ou.language and hou_org.language = flva.language AND cei.location_type_code IN ( 'INVENTORY','IN_TRANSIT','PO','WIP','VENDOR_SITE','PROJECT'))) pivot ( max(location_type) as location_type , max(accounting_class) as accounting_class, max(item_description) as item_description, max(OPERATING_UNIT) as OPERATING_UNIT, max(ORGANIZATION_NAME) as ORGANIZATION_NAME, max(instance_usage) as instance_usage, max(operational_status) as operational_status for LANGUAGE in ('US' "US"))) , dfv AS (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 )) SELECT e.*, ext_attr.* , d.* , coa.operating_unit_id sold_from_org_id FROM eccinst e, dfv d, ( 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, CSI_I_ORG_ASSIGNMENTS coa WHERE e.instance_rowid = d."'INSTANCE_ROW_ID'"(+) and ext_attr.ext_instance_id(+) = e.instance_id and coa.instance_id(+) = e.instance_id and coa.relationship_type_code(+) = 'SOLD_FROM' and NVL(e.active_end_date, sysdate) >= sysdate and e.instance_status_id <> 1 ) x where 2=2 |