Fixing Duplicate Student and Organisation IDs

Like this:

-- ************************************************************************************
-- CSUPGRD-727 Michael Nitschke 31/05/2011
-- Correct situation where Students and Organisations have duplicate IDs.
-- This has occurred where users have overwritten the 'NEW' value when adding an Org.
-- ************************************************************************************

BEGIN
    -- Build working tables for this process.

    -- This one stores all the Records that will potentially be updated.
    -- OCCURSCOUNT1 stores rows of data to be updated
    -- OCCURSCOUNT2 stores rows of data to be updated *after* the fix has been run. Should be 0.
    FOR i IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'PS_AMS_DUPID_RECNM') LOOP
        EXECUTE IMMEDIATE 'DROP TABLE PS_AMS_DUPID_RECNM';
    END LOOP;
    EXECUTE IMMEDIATE '
        CREATE TABLE PS_AMS_DUPID_RECNM (
            DESCR VARCHAR2(100) NOT NULL,
            RECNAME VARCHAR2(15) NOT NULL,
               FIELDNAME VARCHAR2(18) NOT NULL,
               OCCURSCOUNT1 SMALLINT NOT NULL,
            OCCURSCOUNT2 SMALLINT NOT NULL)
         TABLESPACE SAAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80'
         ;

    -- This one stores the old EXT_ORG_ID and the new EXT_ORG_ID for each Organisation that is having its ID updated.
    -- May be useful if something unexpected happens.
    FOR i IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'PS_AMS_DUPID_OLDNW') LOOP
        EXECUTE IMMEDIATE 'DROP TABLE PS_AMS_DUPID_OLDNW';
    END LOOP;
    EXECUTE IMMEDIATE '
        CREATE TABLE PS_AMS_DUPID_OLDNW (
            EXT_ORG_ID_OLD VARCHAR2(11) NOT NULL,
            EXT_ORG_ID VARCHAR2(11) NOT NULL)
        TABLESPACE SAAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80'
        ;

END;
/

-- This procedure takes a given Record and finds all Records that use it as a prompt, where that prompt field is not EXT_ORG_ID (as record would be picked up in base select already).
-- Note that this function is recursive. Use the maxDepth value to set how far down the tree to go.
-- Too high a value and you will run out of cursors.
CREATE OR REPLACE PROCEDURE FindWhereRecUsedAsPrompt(varRecname VARCHAR2, currentDepth integer) IS

    CURSOR cRecPrompts IS
        SELECT 'PROMPT' X, A.RECNAME AS RECNAME, A.FIELDNAME AS FIELDNAME
        FROM
          PSRECFIELD A
        , PSRECDEFN B
        , PSDBFIELD C
        WHERE A.FIELDNAME <> 'EXT_ORG_ID'
        AND A.EDITTABLE = varRecname
        AND B.RECNAME = A.RECNAME
        AND B.RECTYPE = 0  -- SQL Table
        AND C.FIELDNAME = A.FIELDNAME
        AND C.FIELDTYPE = 0; -- Character

    depth integer;
    maxDepth integer;

BEGIN

    maxDepth:= 1; -- Set how deep you want to traverse down the prompt tables
    depth:= currentDepth;

    FOR recP in cRecPrompts
    LOOP
        EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_RECNM (DESCR, RECNAME, FIELDNAME, OCCURSCOUNT1, OCCURSCOUNT2) VALUES (''' || recP.x || '(depth: ' || depth || ', parent:' || varRecname || ')'' , ''' || recP.RECNAME || ''', ''' || recP.FIELDNAME || ''' ,0 ,0)';
        IF depth < maxDepth THEN
            FindWhereRecUsedAsPrompt(recP.RECNAME, depth+1);
        END IF;
    END LOOP;
    depth:= depth-1;

END FindWhereRecUsedAsPrompt;
/

-- This procedure counts number of rows in affected tables.
-- It uses the temp table PS_AMS_DUPID_RECNM
-- OCCURSCOUNT1 stores the number of rows beofore the fix/update is applied.
-- OCCURSCOUNT2 stores the number of rows after the fix/update has been applied (should = 0).
CREATE OR REPLACE PROCEDURE analyseRowCount(fldUpdate VARCHAR2) IS

    CURSOR cTableList IS
        SELECT DISTINCT RECNAME, FIELDNAME, OCCURSCOUNT1 FROM PS_AMS_DUPID_RECNM
        ;

    sqlStatement VARCHAR2(300);
    rowCount INTEGER;

BEGIN
    FOR rec in cTableList
    LOOP
        -- Two steps for legibility's sake.
        BEGIN
            If substr(rec.RECNAME, 1, 2) = 'PS'
            THEN
                sqlStatement:= 'SELECT COUNT(*) FROM ' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN (SELECT SUBSTR(EXT_ORG_ID,1,7) FROM PS_EXT_ORG_TBL B WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN (SELECT A.EMPLID FROM PS_PERSONAL_DATA A WHERE A.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))';
--                dbms_output.put_line('SELECT COUNT(*) FROM ' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN ');
            ELSE
                sqlStatement:= 'SELECT COUNT(*) FROM PS_' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN (SELECT SUBSTR(EXT_ORG_ID,1,7) FROM PS_EXT_ORG_TBL B WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN (SELECT A.EMPLID FROM PS_PERSONAL_DATA A WHERE A.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))';
--                dbms_output.put_line('SELECT COUNT(*) FROM PS_' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN ');
            END IF;

            EXECUTE IMMEDIATE sqlStatement INTO rowCount;
            sqlStatement:= 'UPDATE PS_AMS_DUPID_RECNM SET ' || fldUpdate || ' = ' || rowCount || ' WHERE RECNAME = ''' || rec.RECNAME || ''' AND FIELDNAME = ''' || rec.FIELDNAME || '''';
            EXECUTE IMMEDIATE sqlStatement;

        EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line('Error at analyseRowCount(): ' || sqlstatement);
        END;

    END LOOP;
END analyseRowCount;
/


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


-- << MAIN PROGRAM. >>
DECLARE

    -- Duplicate IDs.
    CURSOR cDuplicateIDs IS
        SELECT
          EXT_ORG_ID
        FROM PS_EXT_ORG_TBL A
        WHERE A.EXT_ORG_ID IN
            (SELECT B.EXT_ORG_ID
            FROM PS_EXT_ORG_TBL B
            WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN
                (SELECT C.EMPLID
                FROM PS_PERSONAL_DATA C
                WHERE C.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))
        --and rownum < 4 -- << LOOK!!! used for building.
        ;

    -- Base tables.
    -- Tables that have EXT_ORG_ID field.
    CURSOR cBaseTables IS
        SELECT
          'Base' X
        , A.RECNAME RECNAME
        , A.FIELDNAME FIELDNAME
        FROM
          PSRECFIELD A
        , PSRECDEFN B
        WHERE A.FIELDNAME = 'EXT_ORG_ID'
        AND B.RECNAME = A.RECNAME
        AND B.RECTYPE = 0
        AND B.RECNAME NOT LIKE '%TMP'
        AND NOT EXISTS -- Not used as an audit record.
            (SELECT 1
            FROM PSRECDEFN
            WHERE AUDITRECNAME = RECNAME)
        ;

    CURSOR cTableList IS
        SELECT DISTINCT RECNAME, FIELDNAME, OCCURSCOUNT1 FROM PS_AMS_DUPID_RECNM
        ;

    sqlStatement VARCHAR2(300);
    rowCount INTEGER;
    newExtOrgId PS_EXT_ORG_TBL.EXT_ORG_ID%type;
    duplicateID INTEGER;

BEGIN

    -- Loop through the base tables that contain EXT_ORG_ID.
    FOR rec in cBaseTables
    LOOP

        -- Insert into working table a list of both the base tables and any tables that use those base tables as a prompt.
        -- These are the list of tables that potentially need to be updated.
        EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_RECNM (DESCR, RECNAME, FIELDNAME, OCCURSCOUNT1, OCCURSCOUNT2) VALUES (''' || rec.x || ''', ''' || rec.RECNAME || ''', ''' || rec.FIELDNAME || ''',0 ,0)';
        FindWhereRecUsedAsPrompt(rec.RECNAME, 1);

    END LOOP;

    -- Now we have a list of tables built we'll analyse how many rows we expect to update.
    -- PS_AMS_DUPID_RECNM.OCCURSCOUNT1 stores the number of rows that will be updated.
    analyseRowCount('OCCURSCOUNT1');

    -- Update the duplicate IDs (this is the really exciting part).
    For rec in cDuplicateIDs
    LOOP

        -- Create a new ID.
        duplicateID := 1;
        WHILE duplicateID <> 0
        LOOP
            SELECT EMPLID_LAST_EMPL + 1 INTO newExtOrgId FROM PS_INSTALLATION;
            EXECUTE IMMEDIATE 'SELECT count(*) FROM PS_PERSONAL_DATA WHERE EMPLID = ''' || newExtOrgId || '''' INTO duplicateID;
            IF duplicateID = 0
            THEN
                EXECUTE IMMEDIATE 'SELECT count(*) FROM PS_EXT_ORG_TBL WHERE EXT_ORG_ID = ''' || newExtOrgId || '''' INTO duplicateID;
                --dbms_output.put_line('duplicateID= ' || duplicateID || ', newExtOrgId= ' || newExtOrgId);
            END IF;
        END LOOP;
        UPDATE PS_INSTALLATION SET EMPLID_LAST_EMPL = newExtOrgId;

        -- Update the old value to the new value in every record that has at least one affected row.
        FOR recUpd in cTableList
        LOOP
            If recUpd.OCCURSCOUNT1 > 0 THEN

                If substr(recUpd.RECNAME, 1, 2) = 'PS'
                THEN
                    sqlStatement:= 'UPDATE ' || recUpd.RECNAME || ' SET ' || recUpd.FIELDNAME || ' = ''' || newExtOrgId || ''' WHERE ' || recUpd.FIELDNAME || ' = ''' || rec.EXT_ORG_ID || '''';
                ELSE
                    sqlStatement:= 'UPDATE PS_' || recUpd.RECNAME || ' SET ' || recUpd.FIELDNAME || ' = ''' || newExtOrgId || ''' WHERE ' || recUpd.FIELDNAME || ' = ''' || rec.EXT_ORG_ID || '''';
                END IF;

                BEGIN
                    EXECUTE IMMEDIATE sqlStatement;
                    --dbms_output.put_line(sqlStatement);
                EXCEPTION
                    WHEN OTHERS THEN
                    dbms_output.put_line('Error at (main program): ' || sqlstatement);
                END;

            END IF;
        END LOOP;

        -- Record the old and new values.
        EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_OLDNW (EXT_ORG_ID_OLD, EXT_ORG_ID) VALUES (''' || rec.EXT_ORG_ID || ''', ''' || newExtOrgId || ''')';

    END LOOP;

    -- Analyse the number of rows again. We expect 0.
    -- OCCURSCOUNT2 stores the number of rows post update/fix. Should be 0.
    analyseRowCount('OCCURSCOUNT2');

END;
/

-- Show the analysis/working tables.
select * from PS_AMS_DUPID_OLDNW;
SELECT * FROM PS_AMS_DUPID_RECNM where occurscount1 > 0;

0 comments: