AP 暂挂过帐发票的删除(SQL)

纵横四海

现象：预付款发票有税码，但把税行删掉了，做费用发票与预付款核销时，暂挂。

解决方法
将有问题的发票彻底删掉重做。
一。用APPS将预付款发票的税码删除，重新审批。

二。对标准发票操作如下：
从FORM可知发票的INVOICE_ID='25494'，因已有部分行入帐

1 删除会计行
SELECT * FROM AP_AE_LINES_ALL AAL WHERE AAL.REFERENCE2='25494'
DELETE FROM AP_AE_LINES_ALL AAL WHERE AAL.REFERENCE2='25494'
DESC AP_AE_LINES_ALL AAL

2?删除会计行头
SELECT * FROM AP_AE_HEADERS_ALL AAH WHERE AAH.AE_HEADER_ID IN (36406,36407)
DESC AP_AE_HEADERS_ALL

3 删除发票事件表
SELECT * FROM AP_ACCOUNTING_EVENTS_ALL AEA WHERE AEA.SOURCE_ID='25494'
DESC AP_ACCOUNTING_EVENTS_ALL

4 删除发票分配行
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL APDA WHERE APDA.INVOICE_ID='25494'
DESC AP_INVOICE_DISTRIBUTIONS_ALL

5 删除发票头
SELECT * FROM AP_INVOICES_ALL AI WHERE AI.INVOICE_ID='25494'
DESC AP_INVOICES_ALL

6.删除发票暂挂表
SELECT * FROM AP_HOLDS_ALL AH WHERE AH.INVOICE_ID ='25494'
DESC AP_HOLDS_ALL

sunyukun8888

呵呵 我之前做过几个AP 删除的SQL 一直删除到 总帐日记账相关SQL分享给出来

--发票
create table bak.AP_INVOICES_ALL_110707 as
select * from AP_INVOICES_ALL aia
where aia.invoice_id in (90490,90333)
--发票行
create table bak.AP_INVOICE_LINES_110707 as
select * from AP_INVOICE_LINES_ALL ala
where ala.invoice_id in (90490,90333);
--分配
create table bak.Ap_Invoice_Dist_110707 as
select *
  from Ap_Invoice_Distributions_All aid
where aid.invoice_id = 90490;
--计划付款
create table bak.AP_PAYMENT_SCHEDULES_110707 as
select * from AP_PAYMENT_SCHEDULES_ALL p
where p.invoice_id in (90490,90333);
--暂挂
create table bak.AP_HOLDS_110707 as
select * from AP_HOLDS_ALL h
where h.invoice_id = 90490;
--付款行
create table bak.AP_INVOICE_PAYMENTS_110707 as
select * from AP_INVOICE_PAYMENTS_all aip
where aip.invoice_id = 90333;
--付款头
create table bak.AP_CHECKS_110707 as
select * from AP_CHECKS_ALL ac where ac.check_id = 64863;
--分录事件
create table bak.xla_trans_entities_110707 as
select *
  from xla.xla_transaction_entities xte
where xte.source_id_int_1 in (90490, 90333)
   and xte.security_id_int_1 = 81
   and application_id = 200;
insert into bak.xla_trans_entities_110707
select  * from xla.xla_transaction_entities xte
where xte.source_id_int_1 in (64863)
   and xte.security_id_int_1 = 81
   and application_id = 200;
   
--分录头
create table bak.xla_ae_headers_110707 as
select *
  from xla.xla_ae_headers xah
where xah.entity_id in (9556541, 9554363);
insert into bak.xla_ae_headers_110707
select * from xla.xla_ae_headers xah where xah.entity_id in (9554366);

--会计事件
create table bak.xla_events_110707 as
select *
  from xla_events xe
where xe.event_id in
       (select event_id
          from xla.xla_ae_headers xah
         where xah.entity_id in (9556541, 9554363));
insert into bak.xla_events_110707
select * from xla_events xe
where xe.event_id in
       (select event_id
          from bak.xla_ae_headers_110707 xah
         where xah.entity_id in (9554366));
--分录行
create table bak.xla_ae_lines_110707 as
select *
  from xla.xla_ae_lines xal
where xal.ae_header_id in (14101317, 14103708, 14299824);

insert into bak.xla_ae_lines_110707
select *
  from xla.xla_ae_lines xal
where xal.ae_header_id=14101322;

--日记账导入参考
create table bak.gl_import_references_110707 as
select *
  from gl.gl_import_references gr
where gr.gl_sl_link_id in
       (select gl_sl_link_id
          from xla.xla_ae_lines xal
         where xal.ae_header_id in (14101317, 14103708, 14299824));
insert into bak.gl_import_references_110707
select *
  from gl.gl_import_references gr
where gr.gl_sl_link_id in (25174221,25174222);  
--日记账头
create table bak.gl_je_headers_110707 as
select *
  from gl_je_headers gjh
where gjh.je_header_id in
       (select je_header_id
          from gl.gl_import_references gr
         where gr.gl_sl_link_id in
               (select gl_sl_link_id
                  from xla.xla_ae_lines xal
                 where xal.ae_header_id in (14101317, 14103708, 14299824)));
insert into bak.gl_je_headers_110707
select * from gl_je_headers gjh
where gjh.je_header_id =5553330;

--日记账行
create table bak.gl_je_lines_110707 as
select *
  from gl_je_lines gjl
where gjl.je_header_id in
       (select je_header_id
          from gl.gl_import_references gr
         where gr.gl_sl_link_id in
               (select gl_sl_link_id
                  from xla.xla_ae_lines xal
                 where xal.ae_header_id in (14101317, 14103708, 14299824)));
insert into bak.gl_je_lines_110707
select *
  from gl_je_lines gjl
where gjl.je_header_id=5553330;
--日记帐批   
create table bak.gl_je_batches_110707 as
select *
  from gl_je_batches gjb
where gjb.je_batch_id in
       (select je_batch_id
          from gl.gl_import_references gr
         where gr.gl_sl_link_id in
               (select gl_sl_link_id
                  from xla.xla_ae_lines xal
                 where xal.ae_header_id in (14101317, 14103708, 14299824)));
                 

--发票
delete from AP_INVOICES_ALL aia
where aia.invoice_id in (90490,90333)
--发票行
delete from AP_INVOICE_LINES_ALL ala
where ala.invoice_id in (90490,90333);
--分配
delete from Ap_Invoice_Distributions_All aid
where aid.invoice_id = 90490;
--计划付款
delete from AP_PAYMENT_SCHEDULES_ALL p
where p.invoice_id in (90490,90333);
--暂挂
delete from AP_HOLDS_ALL h
where h.invoice_id = 90490;
--付款行
delete from AP_INVOICE_PAYMENTS_all aip
where aip.invoice_id = 90333;
--付款头
delete from AP_CHECKS_ALL ac where ac.check_id = 64863;
--分录事件
delete from xla.xla_transaction_entities xte
where xte.source_id_int_1 in (90490, 90333)
   and xte.security_id_int_1 = 81
   and application_id = 200;
delete from xla.xla_transaction_entities xte
where xte.source_id_int_1 in (64863)
   and xte.security_id_int_1 = 81
   and application_id = 200;
   
--分录头
delete from xla.xla_ae_headers xah
where xah.entity_id in (9556541, 9554363);

delete from xla.xla_ae_headers xah where xah.entity_id in (9554366);
--会计事件
delete from xla_events xe
where xe.event_id in
       (select event_id
          from bak.xla_ae_headers_110707 xah
         where xah.entity_id in (9556541, 9554363));
delete from xla_events xe
where xe.event_id in
       (select event_id
          from bak.xla_ae_headers_110707 xah
         where xah.entity_id in (9554366));         
--分录行
delete from xla.xla_ae_lines xal
where xal.ae_header_id in (14101317, 14103708, 14299824);

delete from xla.xla_ae_lines xal
where xal.ae_header_id=14101322;
--日记账导入参考
delete from gl.gl_import_references gr
where gr.gl_sl_link_id in
       (select gl_sl_link_id
          from bak.xla_ae_lines_110707 xal
         where xal.ae_header_id in (14101317, 14103708, 14299824));
delete from gl.gl_import_references gr
where gr.gl_sl_link_id in (25174221,25174222);  
--日记账头
delete from gl_je_headers gjh
where gjh.je_header_id in
       (select je_header_id
          from bak.gl_import_references_110707 gr
         where gr.gl_sl_link_id in
               (select gl_sl_link_id
                  from bak.xla_ae_lines_110707 xal
                 where xal.ae_header_id in (14101317, 14103708, 14299824)));
delete from gl_je_headers gjh
where gjh.je_header_id =5553330;
--日记账行
delete from gl_je_lines gjl
where gjl.je_header_id in
       (select je_header_id
          from bak.gl_import_references_110707 gr
         where gr.gl_sl_link_id in
               (select gl_sl_link_id
                  from bak.xla_ae_lines_110707 xal
                 where xal.ae_header_id in (14101317, 14103708, 14299824)));
delete from gl_je_lines gjl
where gjl.je_header_id=5553330;

--日记帐批   
delete from gl_je_batches gjb where gjb.je_batch_id=5007897
select rowid,gjb.*
  from gl_je_batches gjb
where gjb.je_batch_id in
       (select je_batch_id
          from bak.gl_import_references_110707 gr
         where gr.gl_sl_link_id in
               (select gl_sl_link_id
                  from bak.xla_ae_lines_110707 xal
                 where xal.ae_header_id in (14101317, 14103708, 14299824)));
                 
                 
                 

龙泉怪味豆

学习了，删除的很完整啊