MATERIALIZED VIEW: APPS.BIM_I_OBJ_NAME_MV

Source

SELECT  /* Dummy Comment */ 1 umark,
       source_code_id,
       campaign_name name,
       language,
       object_id object_id,
       object_type object_type,
       l.meaning object_type_mean,
       null activity_id ,
       null child_object_usage
   FROM  bim_i_source_codes b,ams_campaigns_all_tl a,ams_lookups l
   WHERE b.object_id = a.campaign_id
   AND   b.object_type in ('CAMP', 'RCAM')
   AND   b.child_object_id = 0
   AND   b.object_type = l.lookup_code
   AND   l.lookup_type = 'AMS_SYS_ARC_QUALIFIER'
UNION ALL
select 2 umark,
       source_code_id,
       event_header_name name,
       language,
       object_id object_id,
       object_type object_type,
       l.meaning object_type_mean,
       null activity_id ,
       null child_object_usage
  from bim_i_source_codes b, ams_event_headers_all_tl a,ams_lookups l
 where b.object_id = a.event_header_id
   AND b.object_type = 'EVEH'
   AND b.child_object_id = 0
   AND   b.object_type = l.lookup_code
   AND   l.lookup_type = 'AMS_SYS_ARC_QUALIFIER'
UNION ALL
select
       3 umark,
       source_code_id,
       schedule_name name,
       language,
       child_object_id object_id,
       child_object_type object_type,
       l.meaning object_type_mean,
       b.activity_id activity_id ,
       b.child_object_usage child_object_usage
  from bim_i_source_codes b, ams_campaign_schedules_tl a,ams_lookups l
 where b.child_object_id = a.schedule_id
   and b.child_object_type = 'CSCH'
   AND   b.child_object_type = l.lookup_code
   AND   l.lookup_type = 'AMS_SYS_ARC_QUALIFIER'
UNION ALL
select 4 umark,
       source_code_id,
       event_offer_name name,
       language,
       child_object_id object_id,
       child_object_type object_type,
       l.meaning object_type_mean,
       null activity_id ,
       null child_object_usage
  from bim_i_source_codes b, ams_event_offers_all_tl a,ams_lookups l
 where b.child_object_id = a.event_offer_id
   AND b.child_object_type = 'EVEO'
   AND   b.child_object_type = l.lookup_code
   AND   l.lookup_type = 'AMS_SYS_ARC_QUALIFIER'
UNION ALL
select 5 umark,
       source_code_id,
       event_offer_name name,
       language,
       object_id object_id,
       object_type object_type,
       l.meaning object_type_mean,
       null activity_id ,
       null child_object_usage
  from bim_i_source_codes b, ams_event_offers_all_tl a,ams_lookups l
 where b.object_id = a.event_offer_id
   and b.object_type = 'EONE'
   and b.child_object_id = 0
   AND   b.object_type = l.lookup_code
   AND   l.lookup_type = 'AMS_SYS_ARC_QUALIFIER'
UNION ALL
select
    6 umark,-1 source_code_id, meaning name,language,null object_id,NULL object_type,null object_type_mean,
       null activity_id ,
       null child_object_usage
  from fnd_lookup_values
 where lookup_type = 'BIM_GEN_LOOKUP'
   and lookup_code = 'UNA'