Перенос данных для товародвижения без блокировки : Супермаг Плюс (Супермаг 2000)

--очищаем таблицы себестоимости
truncate table supermag.ffbadlinks;
truncate table supermag.ffdebuglog;
truncate table supermag.ffdocuments;
truncate table supermag.fffinbases;
truncate table supermag.ffinvalidoutin;
truncate table supermag.ffmapinin;
truncate table supermag.ffmapoutin;
truncate table supermag.ffmaprep;
truncate table supermag.ffmaprep_;
truncate table supermag.ffpartnerhist;
truncate table supermag.ffpayorders;
truncate table supermag.ffpayordersvat;
truncate table supermag.ffproddocuments;
truncate table supermag.ffprodexpspec;
truncate table supermag.ffprodoutspec;
truncate table supermag.ffprodrep;
truncate table supermag.ffremains;
truncate table supermag.ffremains_;
truncate table supermag.ffremincome;
truncate table supermag.ffremincome_;
truncate table supermag.ffremthreshold;
truncate table supermag.ffsalereturns;
truncate table supermag.ffspec;
truncate table supermag.ffspecscale;
truncate table supermag.ffstorehist;
truncate table supermag.ffworkdata;
truncate table supermag.fsremains;
delete from  supermag.sscalclog;
delete from  supermag.sstransfers;

alter session set skip_unusable_indexes = true;

--отключаем индексы
ALTER INDEX SUPERMAG.FFDOCUMENTS_PK UNUSABLE;
ALTER INDEX SUPERMAG.FFCDOCUMENTS_PHYSPK UNUSABLE;
ALTER INDEX SUPERMAG.FFDOCUMENTS_SALEDATEIDX UNUSABLE;
ALTER INDEX SUPERMAG.FFDOCUMENTS_INCOMEDATEIDX UNUSABLE;
ALTER INDEX SUPERMAG.FFDOCUMENTS_CREATEDAT UNUSABLE;

--пихаем все документы в аналитическую таблицу ffdocuments
truncate table supermag.ffdocuments;
insert into supermag.ffdocuments
select rownum,t.* from
(select 
       
       d.doctype,
       d.id,
       d.createdat,
       d.opcode,
       d.locationfrom,
       d.locationto,
       decode(d.doctype,'WI',i.paycash,'WO',o.paycash,1) paycash,
       decode(d.doctype,'WI',i.goodsowner,0) goodsowner,
       d.clientindex,
       d.currencytype,
       d.userop,
       decode(d.doctype,'WI',i.supplierdoc) supplierdoc,
       decode(d.doctype,'WI',i.supplierinvoice) supplierinvoice,
       decode(d.doctype,'WI',i.supplinvoicecreate) supplinvoicecreate,
       decode(d.doctype,'WI',i.supplinvoiceaccept) supplinvoiceaccept,
       0.00000
from 
supermag.smdocuments d,
supermag.smwaybillsin i,
supermag.smwaybillsout o
where 
d.docstate>=3
and d.doctype=i.doctype(+)
and d.id=i.id(+)
and d.doctype=o.doctype(+)
and d.id=o.id(+)
and d.doctype in ('WI','WO','CS','CR')
and d.createdat<=SYSDATE-1
order by d.doctype, d.id
) t;
commit;
--включаем индексы
ALTER INDEX SUPERMAG.FFDOCUMENTS_PK REBUILD;
ALTER INDEX SUPERMAG.FFCDOCUMENTS_PHYSPK REBUILD;
ALTER INDEX SUPERMAG.FFDOCUMENTS_SALEDATEIDX REBUILD;
ALTER INDEX SUPERMAG.FFDOCUMENTS_INCOMEDATEIDX REBUILD;
ALTER INDEX SUPERMAG.FFDOCUMENTS_CREATEDAT REBUILD;

--пихаем все заказы в аналитическую таблицу ffpayorders
truncate table supermag.ffpayorders;
insert into supermag.ffpayorders
select 
      d.doctype,
      d.id,
      d.createdat,
      d.opcode,
      d.userop,
      d.clientindex,
      d.location,
      d.currencytype,
      d.currencyrate,
      0,
      d.totalsum,
      d.totalsumcur,
      d.isroubles,
      p.orderid,
      p.ourselfclient,
      p.paydate,
      p.iscash
from 
     supermag.smdocuments d,
     supermag.smpayorders p
     
where 
      d.doctype in ('EO','RO')
  and d.docstate>=3   
  and d.doctype=p.doctype(+)
  and d.id=p.id(+)
  and d.createdat<=SYSDATE-1  
order by d.createdat;
commit;

ALTER INDEX SUPERMAG.FFCSPEC_PK UNUSABLE;
ALTER INDEX SUPERMAG.FFSPEC_CAUSEIDX UNUSABLE;
ALTER INDEX SUPERMAG.FFSPEC_ART UNUSABLE;

truncate table supermag.ffspec;
--пихаем все  спецификации в аналитическую таблицу ffspec
insert into supermag.ffspec
select 
       d.ndoc,
       s.specitem,
       s.article,
       s.quantity,
       s.expquantity,
       s.itemprice,
       s.totalprice,
       s.causeid,
       s.causetype,
       s.causespecitem,
       null,
       d.doctype,
       d.createdat,
       d.opcode,
       s.totalpricenotax,
       0,
       0,
       nvl(s.itempricecur,0),
       nvl(s.totalpricecur,0)
from 
     supermag.ffdocuments d,
     supermag.smspec s
where 
      d.doctype=s.doctype
  and d.id=s.docid
commit;

--включаем индексы
ALTER INDEX SUPERMAG.FFCSPEC_PK REBUILD;
ALTER INDEX SUPERMAG.FFSPEC_CAUSEIDX REBUILD;
ALTER INDEX SUPERMAG.FFSPEC_ART REBUILD;

alter session set skip_unusable_indexes = false;

insert into supermag.sstransfers t (t.logid,t.enddocdate,t.lastchangedate,t.starttime,t.endtime) values (1000,sysdate-1,sysdate-1,sysdate-1,sysdate-1);
commit;

DELETE      sslocks
      WHERE objtype = 'DB';
COMMIT ;