Oracle ERP R12. 刪除已存在資料Document Sequence

因為Oracle ERP 的Document Sequence一經設定就不予許刪除重指定,故若傳票編碼設定後就不能修改.但現實狀況裡,可能遇到期初的思慮不週導致後來需要變更Document sequence assign之狀況.

以下文章為顧問公司所提供之Document Sequence刪除的建議步驟:

※以下針對已Assignment Document Sequence做刪除，若僅define Document Sequence並未Assign給任何Category並需要刪除者，處理3. DROP DATABASE SEQUNCE 及4. DELETE　SEQUENCE DEFINE TABLE（第3步的DATABASE SEQUENCE應該還未建立）

Step 1. 資料備份

CREATE TABLE AP_DOC_SEQUENCE_AUDIT_BK AS

SELECT *

FROM AP_DOC_SEQUENCE_AUDIT;

CREATE TABLE AR_DOC_SEQUENCE_AUDIT_BK AS

SELECT *

FROM AR_DOC_SEQUENCE_AUDIT;

CREATE TABLE FND_DOC_SEQUENCE_AUDIT_BK AS

SELECT *

FROM FND_DOC_SEQUENCE_AUDIT;

CREATE TABLE GL_DOC_SEQUENCE_AUDIT_BK AS

SELECT *

FROM GL_DOC_SEQUENCE_AUDIT;

CREATE TABLE FND_DOC_SEQUENCE_ASSIGNS_BK AS

SELECT *

  FROM FND_DOC_SEQUENCE_ASSIGNMENTS;

CREATE TABLE FND_DOCUMENT_SEQUENCES_BK AS

SELECT *

FROM FND_DOCUMENT_SEQUENCES;

Step 2. 刪除Audit table

/*  若已經部份用舊編號,以新的sequence update回各table(ex: RA_CUSTOMER_TRX_ALL, AP_INVOICES_ALL...)  doc_sequence_id, doc_sequence_value 之後,

      要將audit table刪除,再補(insert into )新的 document_sequence_value, doc_sequence_id, doc_sequence_assignment_id 資料, 以避免相關的 "Audit Report xxxx" 標準報表錯誤.

*/

<AP Document Number 記錄檔 : 

  包括 AP 立帳, AP 付款>

DELETE 

FROM AP_DOC_SEQUENCE_AUDIT   adsa

WHERE 1=1

AND EXISTS

(

SELECT 'X'

  FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdca 

 WHERE 1=1

   AND fdca.set_of_books_id             =  :p_set_of_books_id -- ledger_id 

   AND fdca.application_id              =  nvl(:p_application_id , fdca.application_id)  -- 101: gl ; 200: ap ; 222: ar  ; 660 : om   

   AND trunc(fdca.start_date)          >=  trunc(:p_start_date)

   AND trunc(fdca.end_date)            <=  trunc(:p_end_date) 

   AND fdca.doc_sequence_id             =  adsa.doc_sequence_id

   AND fdca.doc_sequence_assignment_id  =  adsa.doc_sequence_assignment_id 

);

<AR Document Number 記錄檔 : 包括AR 立帳, AR收款, AR ADJUSTMENT>

DELETE 

FROM AR_DOC_SEQUENCE_AUDIT  adsa

WHERE 1=1

AND EXISTS

(

SELECT 'X'

  FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdca 

 WHERE 1=1

   AND fdca.set_of_books_id             =  :p_set_of_books_id   -- ledger_id 

   AND fdca.application_id              =  nvl(:p_application_id , fdca.application_id)  -- 101: gl ; 200: ap ; 222: ar  ; 660 : om   

   AND trunc(fdca.start_date)          >=  trunc(:p_start_date)

   AND trunc(fdca.end_date)            <=  trunc(:p_end_date) 

   AND fdca.doc_sequence_id             =  adsa.doc_sequence_id

   AND fdca.doc_sequence_assignment_id  =  adsa.doc_sequence_assignment_id 

);

<FND Document Number 記錄檔 : 

  包括 OM SALES ORDER NUMBERING , WSH 出貨DELIVERY NUMBERING>

**(若只要刪除 AR/AP 代傳票 以及 GL 傳票 序號, 此一 SCRIPT 不要執行!)**

DELETE 

FROM FND_DOC_SEQUENCE_AUDIT   fdsa

WHERE 1=1

AND EXISTS

(

SELECT 'X'

  FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdca 

 WHERE 1=1

   AND fdca.set_of_books_id             =  :p_set_of_books_id   -- ledger_id 

   AND fdca.application_id              =  nvl(:p_application_id , fdca.application_id)  -- 101: gl ; 200: ap ; 222: ar  ; 660 : om   

   AND trunc(fdca.start_date)          >=  trunc(:p_start_date)

   AND trunc(fdca.end_date)            <=  trunc(:p_end_date) 

   AND fdca.doc_sequence_id             =  fdsa.doc_sequence_id

   AND fdca.doc_sequence_assignment_id  =  fdsa.doc_sequence_assignment_id 

);

<GL Document Number 記錄檔 :  

  傳票序號>

DELETE 

FROM GL_DOC_SEQUENCE_AUDIT  gdsa

WHERE 1=1

AND EXISTS

(

SELECT 'X'

  FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdca 

 WHERE 1=1

   AND fdca.set_of_books_id             =  :p_set_of_books_id   -- ledger_id 

   AND fdca.application_id              =  nvl(:p_application_id , fdca.application_id)  -- 101: gl ; 200: ap ; 222: ar  ; 660 : om   

   AND trunc(fdca.start_date)          >=  trunc(:p_start_date)

   AND trunc(fdca.end_date)            <=  trunc(:p_end_date) 

   AND fdca.doc_sequence_id             =  gdsa.doc_sequence_id

   AND fdca.doc_sequence_assignment_id  =  gdsa.doc_sequence_assignment_id 

);

<DROP DATABASE SEQUNCE : 

  產生的SCRIPT , 再丟到 SQL*PLUS 執行 > 

SELECT 'DROP SEQUENCE APPLSYS.' ||  fds.DB_SEQUENCE_NAME || ';' DROP_SCRIPT, fds.*

  FROM FND_DOCUMENT_SEQUENCES   fds

 WHERE 1=1

   AND fds.application_id        =  nvl(:p_application_id , fds.application_id)  -- 101: gl ; 200: ap ; 222: ar  ; 660 : om  

   AND (

         upper(:p_only_arapgl)   =  'N'        OR

         ( upper(:p_only_arapgl) =  'Y'        OR

           fds.application_id   IN  ( 101, 200, 222 )   -- 101: gl ; 200: ap ; 222: ar 

         )

       ) 

   AND trunc(fds.start_date)    >=  trunc(:p_start_date)

   AND trunc(fds.end_date)      <=  trunc(:p_end_date) 

   AND fds.DB_SEQUENCE_NAME     is  NOT NULL

   AND EXISTS    ----指定帳本, 以避免將所有的 DB SEQUENCE都DROP 掉

       (

         SELECT 'X'

           FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdca 

          WHERE 1=1

            AND fdca.set_of_books_id             =  :p_set_of_books_id   -- ledger_id  

            AND fdca.doc_sequence_id             =  fds.doc_sequence_id 

       );

<DELETE SEQUENCE DEFINE TABLE : 

  先刪此一TABLE, 因為FND_DOC_SEQUENCE_ASSIGNMENTS TABLE 才能判別 帳本 (SET_OF_BOOKS_ID)>

DELETE 

  FROM FND_DOCUMENT_SEQUENCES fds

 WHERE 1=1   

   AND fds.application_id        =  nvl(:p_application_id , fds.application_id)  -- 101: gl ; 200: ap ; 222: ar  ; 660 : om  

   AND (

         upper(:p_only_arapgl)   =  'N'        OR

         ( upper(:p_only_arapgl) =  'Y'        OR

           fds.application_id   IN  ( 101, 200, 222 ) -- 101: gl ; 200: ap ; 222: ar 

         )

       ) 

   AND trunc(fds.start_date)    >=  trunc(:p_start_date)

   AND trunc(fds.end_date)      <=  trunc(:p_end_date) 

   AND fds.NAME                  =  nvl(:p_sequence_name, fds.NAME) 

   AND EXISTS    --指定帳本, 以避免將所有的 DB SEQUENCE都DROP 掉

       (

         SELECT 'X'

           FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdca 

          WHERE 1=1

            AND fdca.set_of_books_id             =  :p_set_of_books_id   -- ledger_id  

            AND fdca.doc_sequence_id             =  fds.doc_sequence_id 

       );

<DELETE SEQUENCE ASSIGNMENTS TABLE>  

DELETE

  FROM FND_DOC_SEQUENCE_ASSIGNMENTS fdca 

 WHERE 1=1 

   AND fdca.set_of_books_id             =  :p_set_of_books_id   -- ledger_id 

   AND fdca.application_id              =  nvl(:p_application_id , fdca.application_id)  -- 101: gl ; 200: ap ; 222: ar  ; 660 : om   

   AND trunc(fdca.start_date)          >=  trunc(:p_start_date)

   AND trunc(fdca.end_date)            <=  trunc(:p_end_date)  

   AND (

         upper(:p_only_arapgl)    =  'N'        OR

         ( upper(:p_only_arapgl)  =  'Y'        OR

           fdca.application_id   IN  ( 101, 200, 222 )   -- 101: gl ; 200: ap ; 222: ar 

         )

       )