FA Retirement Accounting Entry in Oracle apps R12

 

             Retirement:  

             The following the accounting entry generated at FA Module Level and code combination                                                                   derived from which setup is mentioned below.

                  Accumulated Depreciation……...Dr.                     (Dep Charged till Date) (Asset Category)

                  Proceeds of Sales clearing a/c… ..Dr.                     (Asset Sold for)  (Book Controls)

                  Cost of removal A/c ………………Dr                                                 (Book Controls)

                  Net Book Value Retired …………..Dr                                                   (Book Controls)

                                          To Asset Cost………………..Cr               (Total Asset Cost)  (Asset category)

                                          To Gain/Loss a/c…………….Cr               (Profit or Loss A/c)

                                           To Proceed for Sales ………….Cr                               (Book Controls)

                                           To Cost of Removal Clearing A/c….Cr                       (Book Controls)

 

 

                Create AR Transaction.

 

                             Receivables  A/c………..Dr

                                         To Proceed to Sale Clearing A/c………….Cr

 

                 Create AP Invoice

 

                               Cost of Removal Clearing A/c………………….Dr

                                             To AP liability A/c…………………………………Cr

Project Module Concurrent Program handy

 The following concurrent Program should be run post entering Miscellaneous Cost in Project Module

The following is the process to transfer the Inventory Misc (Move Order Transactions) to Project Module 

Process of transfer P2P Cycle or Manual AP invoice to Project Module

The below is the mentioned Process once all the expenditure are transfer to Project Module.

Period Close Dependencies Modules in EBS R12

                                       Period Close Dependencies Modules

The following is the Module wise sequence for the period closure in EBS R12.

1. Order Management

2. Cash management

3. Payables 

4. Receivables

5. Purchasing

6. Inventory/ Cost management

7. Projects

8. Asset 

9. General Ledger. 

You have close Order Management module before you close Inventory or Receivable module because the data get inserted to respective modules once the transactions is processed. You have to close Oracle Payables before you close Oracle Purchasing to account for purchasing accruals at period end. You also need to close Oracle Payables before you close Oracle Inventory and Oracle Assets. You required to closed the payables transaction and transfer all the FA transaction through mass addition Process. Once all the payables transaction are closed for the particular period we require to run PRC Interface supplier cost to transfer the Project Costing data to project Module. You actually have to close Oracle Cash Management before you close Oracle Receivables, as bank reconciliation in Cash Management will create miscellaneous receipts in Oracle Receivables. Finally, General Ledger Module will be closed only after all the subledger modules are closed.

SQL Queries used for Data Import from Oracle Receivables

Query to exact  AR Invoice Data.

SELECT  distinct hou.NAME OU_NAME,--HOU.organization_id,

(select DISTINCT NAME from ra_batch_sources_all 

        where BATCH_SOURCE_ID=ra.BATCH_SOURCE_ID

        AND org_id=ra.ORG_ID)SOURCE,

        (select TYPE from ra_cust_trx_types_all where cust_trx_type_id=ra.cust_trx_type_id and rownum=1)CLASS,

        (select name from ra_cust_trx_types_all where cust_trx_type_id=ra.cust_trx_type_id and rownum=1)INVOICE_TYPE,

        hp.party_name BILL_TO_CUSTOMER_NAME,

        hc.ACCOUNT_NUMBER BILL_TO_ACCOUNT_NUMBER,

        hps_bill.PARTY_SITE_NAME BILL_TO_PARTY_SITE_NAME,

        hps_bill.PARTY_SITE_NUMBER BILL_TO_PARTY_SITE_NUMBER,

        hp_ship.party_name SHIP_TO_CUSTOMER_NAME,

        hc_ship.ACCOUNT_NUMBER SHIP_TO_ACCOUNT_NUMBER,

        hps_ship.PARTY_SITE_NAME SHIP_TO_PARTY_SITE_NAME,

        hps_ship.PARTY_SITE_NUMBER SHIP_TO_PARTY_SITE_NUMBER,

        ra.TRX_NUMBER,

        ra.TRX_DATE,

        rct.GL_DATE,

        ra.INVOICE_CURRENCY_CODE,

        ra.EXCHANGE_RATE_TYPE,

        ra.EXCHANGE_DATE,

        ra.EXCHANGE_RATE,

        (SELECT UPPER(NAME)

           FROM ra_terms_tl

          WHERE TERM_ID = ra.TERM_ID)PAYMENT_TERM,

          'OPEN INVOICE DATA' DESCRIPTION,

          '1' QUANTITY_INVOICED,

--        rl.UNIT_SELLING_PRICE,

--        rl.DESCRIPTION,

--        rl.QUANTITY_INVOICED,

--        rl.UNIT_SELLING_PRICE,

        aps.AMOUNT_DUE_REMAINING AMOUNT,

       (select concatenated_segments from gl_code_combinations_kfv where code_combination_id=rct.code_combination_id)REVENUE_ACCOUNT_CODE,

       (select gcc.concatenated_segments

from gl_code_combinations_kfv gcc,ra_cust_trx_line_gl_dist_all rctg 

where gcc.code_combination_id=rctg.code_combination_id

AND rctg.CUSTOMER_TRX_ID =ra.CUSTOMER_TRX_ID AND ACCOUNT_CLASS='REC')RECEIVABLE_ACCOUNT_CODE

  FROM ra_customer_trx_all       ra,

       ra_customer_trx_lines_all rl,

       ar_payment_schedules_all  aps,

       ra_cust_trx_types_all     rt,

       hz_cust_accounts          hc,

       hz_parties                hp,

       hz_cust_acct_sites_all    hcasa_bill,

       hz_cust_site_uses_all     hcsua_bill,

       hz_party_sites            hps_bill,

       hz_cust_accounts          hc_ship,

       hz_parties                hp_ship,

       hz_cust_acct_sites_all    hcasa_ship,

       hz_cust_site_uses_all     hcsua_ship,

       hz_party_sites            hps_ship,

       ra_cust_trx_line_gl_dist_all rct,

       hr_operating_units hou

 WHERE ra.customer_trx_id           = rl.customer_trx_id

   AND ra.customer_trx_id           = aps.customer_trx_id

   AND ra.org_id                    = aps.org_id

   AND ra.org_id                    = hou.ORGANIZATION_ID

   AND hou.ORGANIZATION_ID in ('6845','6846','6843','6844','7463','8047')

   AND rct.customer_trx_id          = aps.customer_trx_id

   AND rct.customer_trx_id          = ra.customer_trx_id

   AND rct.customer_trx_id          = rl.customer_trx_id

   AND rct.customer_trx_line_id     = rl.customer_trx_line_id

   AND ra.complete_flag             = 'Y'

   AND rl.line_type                 IN ('FREIGHT', 'LINE')

   AND ra.cust_trx_type_id          = rt.cust_trx_type_id

   ---------BILL_TO---------

   AND ra.bill_to_customer_id       = hc.cust_account_id

   AND hc.status                    = 'A'

   AND hcasa_bill.org_id in ('6845','6846','6843','6844','7463','8047')

   AND hp.party_id                  = hc.party_id

   AND hcasa_bill.cust_account_id   = ra.bill_to_customer_id

   AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id

   AND hcsua_bill.site_use_code     = 'BILL_TO'

   AND hcsua_bill.site_use_id       = ra.bill_to_site_use_id

   AND hps_bill.party_site_id       = hcasa_bill.party_site_id

   AND hcasa_bill.status            = 'A'

   AND hcsua_bill.status            = 'A'

   ---------SHIP_TO

   AND ra.SHIP_TO_CUSTOMER_ID       = hc_ship.cust_account_id

   AND hc_ship.status               = 'A'

   AND hcasa_ship.org_id in ('6845','6846','6843','6844','7463','8047')

   AND hp_ship.party_id             = hc_ship.party_id

   AND hcasa_ship.cust_account_id   = ra.SHIP_TO_CUSTOMER_ID

   AND hcasa_ship.cust_acct_site_id = hcsua_ship.cust_acct_site_id

   AND hcsua_ship.site_use_code     = 'SHIP_TO'

   AND hcsua_ship.site_use_id       = ra.SHIP_TO_SITE_USE_ID

   AND hps_ship.party_site_id       = hcasa_ship.party_site_id

   AND hcasa_ship.status            = 'A'

   AND hcsua_ship.status            = 'A'

   AND aps.amount_due_remaining     <> 0

   AND aps.status                   = 'OP'

   AND rct.CREATION_DATE between '01-JAN-18' and '30-SEP-2021'

   AND aps.AMOUNT_DUE_REMAINING<>0

--   AND ra.trx_number='F64TX17180000360'

=======================================================================================================================

Query to exact the Open AR Receipt Query

SELECT distinct 

cust_acct.account_number,

hp.PARTY_NAME account_name,

party_site.PARTY_SITE_NUMBER,

acr.RECEIPT_NUMBER,

acr.RECEIPT_DATE,

ABS(ps.AMOUNT_DUE_REMAINING)AMOUNT_DUE_REMAINING ,

acr.AMOUNT,

null Bank_Charges,

ps.GL_DATE,

(select distinct NAME from ar_receipt_methods where RECEIPT_METHOD_ID=acr.RECEIPT_METHOD_ID)RECEIPT_METHOD,

(select distinct NAME from hr_operating_units where ORGANIZATION_ID=acr.ORG_ID)OPERATING_UNIT,

acr.CURRENCY_CODE,

DECODE (acr.TYPE,

'CASH', 'Standard',

'MISC', 'Miscellaneous',

acr.TYPE

)Type,

NULL ACTIVITY,

acr.EXCHANGE_RATE_TYPE,

acr.EXCHANGE_RATE,

acr.EXCHANGE_DATE

  FROM ar_payment_schedules_all ps

     , ar_receivable_applications_all arr

     , hz_cust_accounts_all cust_acct

     , hz_cust_acct_sites_all acct_site

     , hz_party_sites party_site

     , hz_parties hp

     , hz_locations loc

     , hz_cust_site_uses_all site_uses

     , ar_cash_receipts_all acr

     , ar_cash_receipt_history_all crh

     , gl_code_combinations cc

WHERE  ps.customer_id = cust_acct.cust_account_id

   AND ps.customer_id = cust_acct.cust_account_id

   AND acct_site.party_site_id = party_site.party_site_id

   AND loc.location_id = party_site.location_id

   AND ps.cash_receipt_id = acr.cash_receipt_id

   AND acr.cash_receipt_id = crh.cash_receipt_id

   AND crh.account_code_combination_id = cc.code_combination_id

   AND ps.CLASS = 'PMT'

   AND ps.cash_receipt_id = arr.cash_receipt_id

   AND arr.STATUS IN ('UNAPP','UNID')

   AND ps.status = 'OP'

   AND site_uses.site_use_code = 'BILL_TO'

   AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id

   AND NVL (site_uses.status, 'A') = 'A'

   AND cust_acct.cust_account_id = acct_site.cust_account_id

   AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id

   AND party_site.party_id=hp.party_id

   AND ps.customer_id = acct_site.cust_account_id

   AND ps.customer_site_use_id = site_uses.site_use_id

   AND ps.ORG_ID in ('6845','6846','6843','6844','7463','8047')

Query to Exact the Customer Data 

SELECT DISTINCT hcsu.PRIMARY_SALESREP_ID,hp.party_name,hca.ACCOUNT_NUMBER,hps.PARTY_SITE_NUMBER,

hcas.org_id Customer_org_id,jrs.SALESREP_ID,jrs.org_id--INTO V_SALESPERSON

                    FROM hz_parties hp

                       , hz_party_sites hps

                       , hz_cust_accounts hca

                       , hz_cust_acct_sites_all hcas

                       , hz_cust_site_uses_all hcsu

                       , hz_locations hl 

                       , JTF_RS_SALESREPS jrs

                   WHERE hca.party_id = hp.party_id

                     AND hp.party_id = hps.party_id

                     AND hca.cust_account_id = hcas.cust_account_id

                     AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

                     AND hps.party_site_id = hcas.party_site_id

                     AND hps.location_id = hl.location_id

                     AND jrs.SALESREP_ID=hcsu.PRIMARY_SALESREP_ID

--                     AND hps.party_site_number ='777034'--i.bill_to_party_site_number

                     AND hcsu.site_use_code = 'BILL_TO'

--                     AND hca.account_number ='417543'--i.bill_to_account_number

                     AND hcas.org_id in  ('8248','8249','8250','8251')

                     AND hca.STATUS='A'

                     AND hcas.org_id<>jrs.org_id

                     AND hcsu.PRIMARY_SALESREP_ID>0 

----------------------------------------------------------------------------------------

Other Query to exact Customer Details

SELECT hp.party_name, hca.cust_account_id, hcas.org_id, hp.party_id,

       hcas.cust_acct_site_id, hps.location_id, hl.address1, hl.address2,

       hl.address3, hl.address4, hl.city, hl.postal_code, hl.state, hp.PARTY_NUMBER, hps.PARTY_SITE_NUMBER, hca.ACCOUNT_NUMBER, hcsu.LOCATION

  FROM hz_parties hp,

       hz_party_sites hps,

       hz_cust_accounts hca,

       hz_cust_acct_sites_all hcas,

      hz_cust_site_uses_all hcsu, 

       hz_locations hl

WHERE hp.party_id = hca.party_id

   AND hca.party_id = hps.party_id

   AND hps.party_site_id = hcas.party_site_id

   AND hca.cust_account_id = hcas.cust_account_id

   AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

   AND hcsu.site_use_code = 'BILL_TO'

   AND hl.location_id = hps.location_id

   AND hcas.status = 'A'

   AND hcsu.status = 'A'

   AND hp.party_name='ABC TANNERS'

  and hcas.org_id='121'

 --- AND hl.location_id = 89091

  ---AND hca.cust_account_id = 965678

Customer Master fetch Query

SELECT hp.party_name, hca.cust_account_id, hcas.org_id,

(Select NAME from HR_operating_units where ORGANIZATION_ID=hcas.org_id) OPERATINGUNIT,

hp.party_id,

       hcas.cust_acct_site_id, hps.location_id, hl.address1, hl.address2,

       hl.address3, hl.address4, hl.city, hl.postal_code, hl.state, hp.PARTY_NUMBER, hps.PARTY_SITE_NUMBER, hca.ACCOUNT_NUMBER, hcsu.LOCATION

  FROM hz_parties hp,

       hz_party_sites hps,

       hz_cust_accounts hca,

       hz_cust_acct_sites_all hcas,

      hz_cust_site_uses_all hcsu, 

       hz_locations hl

WHERE hp.party_id = hca.party_id

   AND hca.party_id = hps.party_id

   AND hps.party_site_id = hcas.party_site_id

   AND hca.cust_account_id = hcas.cust_account_id

   AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

   AND hcsu.site_use_code = 'BILL_TO'

   AND hl.location_id = hps.location_id

   AND hcas.status = 'A'

   AND hcsu.status = 'A'

 --  AND hp.party_name='ABC TANNERS'

  and hcas.org_id in  ('8248','8249','8250','8251')

 --- AND hl.location_id = 89091

  ---AND hca.cust_account_id = 965678

How to diagnose the performance issue in oracle apps R12.

1.  Trace - We run the trace based on particular activity. We require to generate trace based on particular activity like concurrent report, or form which is leading to time consumption. Trace can be run at the below level

- Trace can be run at respective Form Level

- Trace need to be enabled at Concurrent Define window.

- Trace fetching based on Request ID

Once trace file is generated. Reveiw the trace file to find out at which level there performance issue for a Particular SQL ID as is mentioned below.
                            ·         Parsing
                            ·         Fetching
                            ·         Execution

2. AWR Report: AWR Report will be run every 1 hour as scheduled by DBA team. For this report we will be able to find out Top 10 SQL ID which is causing performance issue. Based on SQL ID, we will generated

3. SQLT Report:  For particular SQL ID we have to generate SQLT Report.  Which will help us to identify the number indexes created on particular table or Query which is causing for SQL Execution issue.

Oracle Enterprise Management (OEM)

From Oracle Enterprise Management (OEM) window we will be able to find the load on the server. As per the graph below, Blue indicates high load on the server.

`

Blocking Queries:

select to_char(a.LOGON_TIME,'DDMonYY HH24:MM'),a.sid, a.serial#,c.NAME, a.STATUS,a.ACTION from V$SESSION a ,DBA_BLOCKERS b,AUDIT_ACTIONS c

where a.sid = b.holding_session and a.command = c.action;

select /*+ rule */ 'SID '||a.sid||' is blocking the sessions '||b.sid from v$lock a, v$lock b where a.block=1 and b.request >0;

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine

                 FROM  v$locked_object a, v$session b,  dba_objects c

                 WHERE b.SID = a.session_id AND a.object_id = c.object_id  AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

  

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,b.osuser, b.machine FROM  v$locked_object a, v$session b,  dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine FROM  v$locked_object a, v$session b,  dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id  AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

SELECT session_id  FROM DBA_LOCKS WHERE BLOCKING_OTHERS = 'Blocking';

select * from DBA_WAITERS;

select * from dba_blockers;

select * from v$lock where block=1;

SELECT DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid sess,inst_id,id1, id2,lmode,request, TYPE

FROM gv$LOCK  WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$LOCK WHERE request > 0)

ORDER BY id1, request;

SELECT 'alter system kill session ''' || s.sid || ',' || s.SERIAL# || ','||'@'||''|| i.instance_number || ''';' a,'ps -ef |grep LOCAL=NO|grep ' || p.SPID SPID,

'kill -9 ' || p.SPID,s.status FROM gv$session s, gv$process p,gv$instance i WHERE ( (p.addr(+) = s.paddr) AND (p.inst_id(+) = s.inst_id)) AND s.sid = &sid;

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE

|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '

|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) OBJ_ID:' ||L1.ID1||' OBJ_NAME:'||O.OBJECT_NAME

AS BLOCKING_STATUS

FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2,DBA_OBJECTS O

WHERE S1.SID=L1.SID AND S2.SID=L2.SID

AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID

AND L1.ID1=OBJECT_ID

AND L1.ID1=O.OBJECT_ID

AND L1.BLOCK > 0 AND L2.REQUEST > 0

AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

    SELECT

   l1.sid || ' is blocking ' || l2.sid blocking_sessions

FROM

   gv$lock l1, gv$lock l2

WHERE

   l1.block = 1 AND

   l2.request > 0 AND

   l1.id1 = l2.id1 AND

   l1.id2 = l2.id2;

Report Used to reconcile FA module to GL

Report in FA Module.

1. Cost Summary Report
2. Journal Reserve Report.

Report in GL Module.

1. Journal Extended Report

Incase of data migaration point to be remebered for Fixed Asset Data Upload

Note: Any value entered in Accumulated depreciation Field system will calcualted depreciation for Current Period only. If no value entered in depreciation Field system will calculate depreciation from beginning of the asset.

Case Study 1: Create a new asset, With Accumulated Depreciation(let's say X)  and YTD Depreciation.    

Run Depreciation to Calculate current Method Depreciation. In Accumulated Deprecation , system will calculated current method Depreciation
and add to accumulated deprecation as entered while creating asset(X Value + current Period Depreciation)
while running create Accounting. System will Generated accounting
Entry for Asset addition and Current month Depreciation but no accounting entry will be created for accumulated Depreciation entered while Creating Asset..
If Accumulated Depreciation is entered, System will calculated Depreciation for current Period only.

Illustration:

Asset Cost: 10000
Accumulated Depreciation: 500
Date Place in Service : 01-JAN-2013
Depreciation Method Life: 40 Yrs.   (480 Months)
After Running Depreciation for MAR-16
Deprecation calculate for the period= Asset Cost/ Life in months=  10000/480= 20.83
Now
Accumulated Depreciation will be 500+ 20.83=   520.83.

Accounting entry will be generated for Asset Addition as below

Asset Cost A/c............Dr   10000
      To Asset Clearing A/c.......................Cr   10000

Accounting entry will be generated for Depreciation as below

Depreciation A/c..............Dr   20.83
                   To Accumulated Depreciation A/c....20.83
Note: No Accounting will be generated for Accumulated Deprecation entered before running Depreciation.

Case Study 2: Create a new asset, With Accumulated Depreciation as null and YTD Depreciation as null and when you save record. System will create YTD Deprecation and
accumulated Depreciation till Previous Period.
Run Depreciation to Calculate current Method Depreciation.   
while running create Accounting. System will Generated accounting
Entry for Asset addition and Current month Depreciation but no accounting entry will be created for accumulated Depreciation.
If Accumulated Depreciation is entered, System will calculated Depreciation for currrent Period only.

Illustration:

Asset Cost: 10000
Accumulated Depreciation:  NULL
YTD Depreciation: NULL
Date Place in Service : 01-JAN-2013
Depreciation Method Life: 40 Yrs.
after Running Depreciation for MAR-16
Deprecation calculate from Date place in Service: 20.83 (Dep per Month)* 39(Till Current Month)= 812.5
Now
Accumulated Depreciation will be = 812.5
YTD Depreciation will be=812.5.


Accounting entry will be generated for Asset Addition as below

Asset Cost A/c............Dr   10000
      To Asset Clearing A/c.......................Cr   10000

Accounting entry will be generated for Depreciation as below

Depreciation A/c..............Dr   812.5
                   To Accumulated Depreciation A/c....812.5
Note: Depreciation Accounting will be generated from Date place in service to till Date

Not able to update the TAX Calendar in TDS Regime. Error Appears - "FRM-40654 : Record has been updated. Requery block to see change"

UPDATE jai_regimes
      SET creation_date = TRUNC(creation_date) ,
             last_update_date = TRUNC(last_update_date),
             effective_from = TRUNC(effective_from)
WHERE REGIME_NAME = 'TDS'
/
COMMIT;

Error while Incomplete AR Invoice, APP-AR-11526: ENCOUNTERED AN ERROR IN JAI_AR_TAX_DEP_PKG. dETERMINE_MANUAL_ar_tax

Download & Apply the following Patch: 26410637:R12.JAI.B and Test it.

How to set the customer number in Oracle apps / Customer Number Sequence updation in AR

Note: Refer Doc ID 265315.1)

Switch to the Application Developer responsibility,
In the Navigator window select Application, then Database, and finally Sequence.

To automatically assign a unique number to every new customer, check the Automatic Customer Numbering box. Do not check this box if you want to manually assign customer numbers. Oracle Applications system administrator or developer can specify the initial number to be used for Automatic Customer Numbering.

Switch to the Application Developer responsibility.

In the Navigator window select Application, then Database, and finally Sequence.

In the Name field of the Sequences window, query for HZ_ACCOUNT_NUM_S.

In the Start Value field of the Sequences window, enter the initial number to be used for Automatic Customer Numbering.

You were previously entering customer numbers manually. Now you want to generate the number automatically but you want to specify the value the sequence should start from. How can this be done? The database sequence that handles the creation of customer numbers is defined in HZ_ACCOUNT_NUM_S.

Care should be taken to ensure that you do not define a number which will cause duplicate customer numbers to exist in your system. you can run the following to identify the highest number currently in use within your system: Select max(account_number) from hz_cust_accounts_all; Then using the number returned above, define the sequence to start with a higher value. If for example the above returns 20000, then you can do the following to ensure the sequence will not create overlapping account numbers:

DROP SEQUENCE AR.HZ_ACCOUNT_NUM_S;

CREATE SEQUENCE AR.HZ_ACCOUNT_NUM_S START WITH 20000 INCREMENT BY 1 MAXVALUE 999999 CACHE 20;