ECC Oracle Asset Tracking, Oracle Asset Tracking: Instances

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