MES_ERP_Cipher_Diff.sql

select rownum as "№",  cipher_list.*
  from (select to_number(m.material_id) as "Код материала",
               nvl(m.tns, m.proportional_tns) as "Тонны",
               dv.code as "Шифр MES",
               (select max(aw.no) keep(dense_rank last order by po.prod_oper_time) 
                  from rs_product@erp        p,
                       rs_prod_oper@erp      po,
                       rs_transform_oper@erp tro,
                       acc_ware@erp          aw
                 where p.rs_product_guid = c.document_guid
                   and po.rs_product_id = p.rs_product_id
                   and tro.rs_transform_oper_id = po.rs_transform_oper_id
                   and tro.type_sid in (54700001) -- сдача
                   and aw.acc_ware_id = po.oper_acc_ware_id
                   and po.parent_rs_prod_oper_id is null
                   and not exists (select 1
                                     from rs_prod_oper@erp po1
                                    where po1.rs_prod_oper_id = po.parent_rs_prod_oper_id
                                  )
               ) as "Шифр ERP",
               to_char(crs.shift_date, 'dd.mm.yyyy') as "Дата сортамента",
               nvl(ws.no, '') as "РО"
          from material            m,   -- Result Material
               material_desc       md,
               description_type    dt,  -- Cipher 
               description_value   dv,  -- Cipher Value
               document_line       cdl, -- Claim Lines
               documents           c,   -- Claim
               document_type       ct,  -- Claim Type
               document_route_step crs, -- Claim Route Steps
               operation           o,   -- Claim Operation
               document_line       wsdl,-- Wagon Specification Lines
               documents           ws,  -- Wagon Specification
               document_type       wst  -- Wagon Specification Type
         where m.material_id = md.material_id
           and md.description_type_id = dt.description_type_id
           and dt.code = 160 -- Cipher Code
           and md.description_value_id = dv.description_value_id
           and cdl.material_id = m.material_id
           and cdl.status = 'Created'
           and c.document_id = cdl.document_id
           and ct.document_type_id = c.document_type_id
           and ct.code = 22 -- Claim Code
           and c.document_id = crs.document_id
           and crs.shift_date between
               to_date('01.04.2013') and -- Begin Date
               to_date('02.04.2013')     -- End Date
           and crs.operation_id = o.operation_id
           and o.type = 'Document'
           and o.code = 642 -- Finish Production Code
           and wsdl.material_id = m.material_id
           and ws.document_id(+) = wsdl.document_id
           and ws.document_type_id = wst.document_type_id
           and wst.code = 7 -- Wagon Specification Code
       ) cipher_list
 where cipher_list."Шифр MES" != cipher_list."Шифр ERP"