刪除 AP 發票相關指令碼

weixin_33816946發表於2018-03-15

/* 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)));

相關文章