删除 AP 发票相关脚本
/* Formatted on 2018/3/15 10:33:14 (QP5 v5.256.13226.35538) */
--发票
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_linesxal
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_linesxal
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_linesxal
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)));
