create or replace procedure FixNotTransferredZpos is
begin
  for x in 
    (select * from zpos where zpos_internalsplit_zpos_id in
    (  select zpos_internalsplit_zpos_id from zpos 
       group by zpos_internalsplit_zpos_id
       having count(distinct zpos_zfac_id)+count(distinct zpos_yres_id)>1)
    and zpos_internalsplit_zpos_id=zpos_id)
  loop
    update zpos z2 set 
      z2.zpos_zwin_id= x.zpos_zwin_id, 
      z2.zpos_yres_id= x.zpos_yres_id, 
      z2.zpos_zfac_id= x.zpos_zfac_id,
      z2.zpos_xcms_id= x.zpos_xcms_id
    where 
      z2.zpos_internalsplit_zpos_id = x.zpos_internalsplit_zpos_id and
      ((x.zpos_yres_id is not null and (z2.zpos_yres_id is null or zpos_yres_id <> x.zpos_yres_id)) or
       (x.zpos_zfac_id is not null and (z2.zpos_zfac_id is null or zpos_zfac_id <> x.zpos_zfac_id)));  
    dbms_output.put_line(x.zpos_internalsplit_zpos_id);   
  end loop;
  
  dbms_output.put_line('zwin');   
  
  for y in 
    (select zpos.* from zpos where zpos_internalsplit_zpos_id in 
     (select zpos_internalsplit_zpos_id from zpos 
      group by zpos_internalsplit_zpos_id
      having count(distinct zpos_zwin_id)>1)
    and zpos_internalsplit_zpos_id=zpos_id)
  loop
    update zpos z2 set 
      z2.zpos_zwin_id= y.zpos_zwin_id, 
      z2.zpos_yres_id= y.zpos_yres_id, 
      z2.zpos_zfac_id= y.zpos_zfac_id,
      z2.zpos_xcms_id= y.zpos_xcms_id
    where 
      z2.zpos_internalsplit_zpos_id = y.zpos_internalsplit_zpos_id and
      z2.zpos_zwin_id <> y.zpos_zwin_id;
    dbms_output.put_line(y.zpos_internalsplit_zpos_id);   
  end loop;       
  
  /*
    VAT postings having ZPOS_TAXLINK_ID identical, but different ZPOS_INTERNALGROUP_IDs, updating ZPOS_INTERNALGROUP_ID with ID of the net posting
  */ 
  
  for w in 
    (select zpos.* from zpos where zpos_taxlink_id in 
     (select zpos_taxlink_id from zpos 
      group by zpos_taxlink_id
      having count(distinct nvl(zpos_internalgroup_id,0))>1)
    and zpos_cdt=1)
  loop
    update zpos z2 set 
      z2.zpos_zwin_id= w.zpos_zwin_id, 
      z2.zpos_yres_id= w.zpos_yres_id, 
      z2.zpos_zfac_id= w.zpos_zfac_id,
      z2.zpos_xcms_id= w.zpos_xcms_id,
      z2.zpos_internalgroup_id= w.zpos_internalgroup_id
    where 
      z2.zpos_taxlink_id = w.zpos_taxlink_id and
      nvl(z2.zpos_internalgroup_id,0) <> nvl(w.zpos_internalgroup_id,0);
      
    dbms_output.put_line(w.zpos_taxlink_id);   
  end loop;       

  
end fixNotTransferredZpos;
/ 
