MATERIALIZED VIEW: APPS.ENI_DBI_CO_ALL_MV

Source

select /* 12.0: bug#4526784 */  eco.change_notice
       , eco.organization_id    organization_id
       , to_char(eri.revised_item_id)  item_id
       , eco.change_order_type_id   change_order_type_id
       , nvl(eco.reason_code, '-1') reason_code
       , nvl(eco.priority_code, '-1') priority_code
       , eco.status_code                      status_type
       , trunc(eco.initiation_date)             creation_date
       , trunc(eco.implementation_date)       implementation_date
       , trunc(eco.cancellation_date)         cancellation_date
       , trunc(eco.approval_date)             approval_date
       , trunc(eco.need_by_date)                need_by_date
       , eco.assignee_id
       , eco.description
       , eco.change_id
       , 1                                    marker
       , count(*)                             cnt
         from ENG.ENG_ENGINEERING_CHANGES eco, ENG.ENG_REVISED_ITEMS eri
         where eco.organization_id = eri.organization_id
                    and eco.change_notice = eri.change_notice
                    and eco.change_mgmt_type_code = 'CHANGE_ORDER'
        group by eco.change_notice
        , eco.organization_id
        , to_char(eri.revised_item_id)
        , eco.change_order_type_id
        , nvl(eco.reason_code, '-1')
        , nvl(eco.priority_code, '-1')
        , eco.status_code
        , trunc(eco.initiation_date)
        , trunc(eco.implementation_date)
        , trunc(eco.cancellation_date)
        , trunc(eco.approval_date)
        , trunc(eco.need_by_date)
        , eco.assignee_id
        , eco.description
        , eco.change_id
union all
        select eco.change_notice
      , eco.organization_id                  organization_id
      , ecs.pk1_value                        item_id
      , eco.change_order_type_id             change_order_type_id
      , nvl(eco.reason_code,  '-1')   reason_code
      , nvl(eco.priority_code, '-1') priority_code
      , eco.status_code                      status_type
      , trunc(eco.initiation_date)             creation_date
      , trunc(eco.implementation_date)       implementation_date
      , trunc(eco.cancellation_date)         cancellation_date
      , trunc(eco.approval_date)             approval_date
      , trunc(eco.need_by_date)              need_by_date
      , eco.assignee_id
      , eco.description
      , eco.change_id
      , 2                                    marker
      , count(*)                             cnt
        from ENG.ENG_ENGINEERING_CHANGES eco,ENG.ENG_CHANGE_SUBJECTS ecs, ENI_DBI_CO_OBJIDS_MV edcom
        where eco.change_id = ecs.change_id
           and ecs.entity_name = edcom.obj_name
           and eco.organization_id = ecs.pk2_value
           and eco.change_mgmt_type_code = 'CHANGE_ORDER'
         group by eco.change_notice
        , eco.organization_id
        , ecs.pk1_value
        , eco.change_order_type_id
        , nvl(eco.reason_code, '-1')
        , nvl(eco.priority_code, '-1')
        , eco.status_code
        , trunc(eco.initiation_date)
        , trunc(eco.implementation_date)
        , trunc(eco.cancellation_date)
        , trunc(eco.approval_date)
        , trunc(eco.need_by_date)
        , eco.assignee_id
        , eco.description
        , eco.change_id