ECC Oracle Asset Tracking, Oracle Asset Tracking: Instances
Description
Categories: Enterprise Command Center
Columns: Instance Rowid, Ecc Spec Id, Instance Number, Instance Id, Serial Number, Instance Quantity, Item Name, Inventory Item Id, Pa Txn Source, Operating Unit Id ...
Columns: Instance Rowid, Ecc Spec Id, Instance Number, Instance Id, Serial Number, Instance Quantity, Item Name, Inventory Item Id, Pa Txn Source, Operating Unit Id ...
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
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, 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, 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, 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, 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 CSI_ITEM_INSTANCES_DFV) 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 AND NOT EXISTS (select 'Y' from csi_i_assets cia where cia.instance_id = e.instance_id and cia.active_end_date IS NULL AND cia.fa_asset_id IS NOT NULL) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Sold From Org. |
|
LOV |