ECC Oracle Asset Tracking item instances

Description
Categories: Draft
Listing of all assets processed via the asset tracking inlcuding install base reference details. This query is based on the Enterprise command center dashboard query and allows full export to Excel from EBS rather than ECC
WITH eccinst AS ( select * FROM  ((SELECT /*+ leading ( cei.cii ) cardinality ( cei.cii 10 )  */ 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,  CSE_ECC_UTIL_PVT.issue_to_field_eligible(cii.instance_id) issue_field_eligible,  CSE_ECC_UTIL_PVT.issue_to_proj_eligible(cii.instance_id) issue_proj_eligible,  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 ) cardinality ( cei.cii 10 )  */ 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,  CSE_ECC_UTIL_PVT.issue_to_field_eligible(cii.instance_id) issue_field_eligible,  CSE_ECC_UTIL_PVT.issue_to_proj_eligible(cii.instance_id) issue_proj_eligible,  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 ) cardinality ( cei.cii 10 )  */ 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,  CSE_ECC_UTIL_PVT.issue_to_field_eligible(cii.instance_id) issue_field_eligible,  CSE_ECC_UTIL_PVT.issue_to_proj_eligible(cii.instance_id) issue_proj_eligible,  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 ) cardinality ( cei.cii 10 )  */ cii.rowid instance_rowid, cei.*, null,  null, null, null, null, null, null, null ,  CSE_ECC_UTIL_PVT.issue_to_field_eligible(cii.instance_id) issue_field_eligible,  CSE_ECC_UTIL_PVT.issue_to_proj_eligible(cii.instance_id) issue_proj_eligible,  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 'INSTANCE_ROW_ID','INSTANCE_CONTEXT_VALUE','INSTANCE_LAST_MAINTENANCE_DONE_BY_NAME_','INSTANCE_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT_VALUE,LAST_MAINTENANCE_DONE_BY_NAME_,CONCATENATED_SEGMENTS from CSI_ITEM_INSTANCES_DFV)   SELECT e.*,
	d.* ,
    coa.operating_unit_id sold_from_org_id  FROM  eccinst e,
    dfv d,
    CSI_I_ORG_ASSIGNMENTS coa    WHERE e.instance_rowid = d."'INSTANCE_ROW_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)