select h.msg_shipment_id msg_shipment_id,
       h.created,
       (select mp_name || '(' || h.marketplace_id || ')'
          from marketplaces
         where marketplace_id = h.marketplace_id) marketplace_info,
       h.marketplace_id marketplace_id,
       h.shipment_nr,
       h.shipment_date,
       h.saledoc_nr,
       h.saledoc_date,                          
       (select id || ', ' || fullname || ' (' || addressf_text || ')'
          from firms
         where id = h.shipper_id) shipper_info,
       (select id || ', ' || fullname || ' (' || addressf_text || ')'
          from firms
         where id = h.consignee_id) consignee_info,
       (select id || ', ' || fullname || ' (' || addressf_text || ')'
          from firms
         where id = h.customer_id) customer_info,
       (select id || ', ' || fullname || ' (' || addressf_text || ')'
          from firms
         where id = h.supplier_id) supplier_info,                           
       decode(h.status,
              0,              'удален',
              1,              'вчитан, готов к отправке получателю',
              2,              'отправлен получателю',
              3,              'доставлен',
              'статус не определен') status_info,
       h.status,
       h.created_user,
       h.unloaded,
       (select mp_name || '(' || h.destmarketplace_id || ')'
          from marketplaces
         where marketplace_id = h.destmarketplace_id) destmarketplace_info,
       h.shipment_type,                           
       nvl2(shnh.created,
            to_char(shnh.created, 'dd.mm.yyyy hh24:mi:ss'),
            'нет уведомления о приеме отправки') notice_created
  from msg_shipment_headers h
  left join etalonadm.msg_shipmentnotice_headers shnh on (shnh.shipment_nr =
                                               h.shipment_nr and
                                               shnh.marketplace_id =
                                               h.destmarketplace_id)
 where (upper(h.shipment_nr) like upper('00000005935') 
        or upper(h.saledoc_nr) like upper('00000005935')
       )
   and rownum <= 50
            

create index ETALONADM.IX_MSG_SH_HEADERS_SHIP_NR_UP
 on ETALONADM.MSG_SHIPMENT_HEADERS (upper(SHIPMENT_NR))
  tablespace ETALON
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );  
  
create index ETALONADM.IX_MSG_SH_HEADERS_SD_NR_UP
 on ETALONADM.MSG_SHIPMENT_HEADERS (upper(SALEDOC_NR))
  tablespace ETALON
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );