ECC Oracle Asset Tracking, Oracle Asset Tracking: Instances

➜ Back to Library
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 ...
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 
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(