Script to change check numbers at the table level in Payables.
Payable Scenario -

Entering a "manually typed" check into the system as a manual type, the user did not realize that the wrong check number was
assigned Example: Check number 101 needs to be swapped with repay check #

Steps: 1) System Void Check #101
          2) Repay the invoice to a new check #
          3) Run Script
           4) Don't forget to manually void the replay check #  document as the script will change this to a void.

1) Get the check id for the checks to be updated
Select check_id, amount, bank_account_name
From ap_checks_all Where check_number = 'your check number';

2) Run this for each check you plan on updating.
Verify the correct check_id by using the amount and bank.

3) There is a unique constraint on check_stock_id and
check_number So you may need to move aside one of the check
records (to a dummy check number ie. 99999') before updating.

Update ap_checks_all set check_number = '99999'
where check_id = id for check 101 from above select;

Update ap_checks_all set check_number = '101'
where check_id = id for repay check # from above select;

Update ap_checks_all set check_number = 'Repay check #'
where check_id = id for original check 101 from 1st update;

Return to : Oracle Database, SQL, Application, Programming Tips