IP Master | How to delete all invoices from a Scan Date defined

Please find below a SQL Script to cancel invoices or delete invoices from your BasWare Invoice Processing database:

------------------------------------------------------------------
--                      For Oracle Database                     --
------------------------------------------------------------------

-- Cancel invoices from a Scan Date defined
update docs
set status_index = '4'
where scan_date >= sysdate-90;
delete from flow_current where doc_id in (select d.doc_id from docs d where d.scan_date >= sysdate-90);
commit;

-- Cancel invoices from a Scan Date
update docs d set d.status_index ='4' where d.scan_date >= to_date('01/03/2020','DD/MM/YYYY');
delete from flow_current where doc_id in (select d.doc_id from docs d where d.scan_date >= to_date('01/03/2020','DD/MM/YYYY'));
commit;

-- Delete invoices from a Scan Date defined
delete from acc_data where doc_id in (select doc_id from docs where scan_date >= sysdate-90);
delete from flow_current where doc_id in (select doc_id from docs where scan_date >= sysdate-90);
delete from docs where scan_date >= sysdate-90;
commit;

-- Cancel invoices from a Scan Date
update docs d set d.status_index ='4' where d.scan_date >= to_date('01/03/2020','DD/MM/YYYY');
delete from flow_current where doc_id in (select d.doc_id from docs d where d.scan_date >= to_date('01/03/2020','DD/MM/YYYY'));
commit;

--------------------------------------------------------
--           Delete invoices from a Scan Date defined --
--------------------------------------------------------

delete from acc_data
where
  doc_id in (select doc_id from docs where comp_no not in (select comp_no from companies)
  and scan_date < to_date('01/01/2020','DD/MM/YYYY'));

delete from flow_current
where
  doc_id in (select doc_id from docs where comp_no not in (select comp_no from companies)
  and scan_date < to_date('01/01/2020','DD/MM/YYYY'));

delete from docs
where
  doc_id in (select doc_id from docs where comp_no not in (select comp_no from companies)
  and scan_date < to_date('01/01/2020','DD/MM/YYYY'));

-- Delete invoices from a Scan Date defined and COMP_NO is null
delete from acc_data
where
  doc_id in (select doc_id from docs where scan_date < to_date('01/01/2020','DD/MM/YYYY') and (comp_no is null or comp_no = ''))
  ;
  
delete from flow_current
where
  doc_id in (select doc_id from docs where scan_date < to_date('01/01/2020','DD/MM/YYYY') and (comp_no is null or comp_no = ''))
  ;

delete from docs
where
  doc_id in (select doc_id from docs where scan_date < to_date('01/01/2020','DD/MM/YYYY') and (comp_no is null or comp_no = ''))
  ;

-- Mise à jour des DOC_ID restants
update docs
set comp_no = 'ZZZZ'
where
  comp_no is null or comp_no = '';

select count(*) from docs where comp_no is null or comp_no = '';

commit;