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;

Always Return one Row in Oracle SQL

SELECT Min(FIELD) FIELD
FROM SOME_TABLE
WHERE FIELD = 'X'

XML Publisher and Rowset Class

Example Class for an Application Package. Takes a Rowset and passes to the given Publisher Report and Template.

/********************************************************************************
SAMS_BRD220X Michael Nitschke 12/05/2011 XML Publisher
Common Class for XML Publisher

Users can use the method CreateXMLFilesFromRS to create only XML and XSD file,
very useful for XMLPublisher Template creation.

The PublishXMLReportFromRS method will create the XML Files and publish the given 
report.
********************************************************************************/
import PSXP_XMLGEN:*;
import PSXP_RPTDEFNMANAGER:*;

class XMLPublisher
   method XMLPublisher();
   method PublishXMLReportFromRS();
   method CreateXMLFilesFromRS();
   
   property Rowset rsData;
   property string sFileNameXSD;
   property string sFileNameXML;
   property string sRptDefn;
   property string sReportTemplate;
   property number processInstance;
   property string xmlFileName;
end-class;

method XMLPublisher
   /* Constructor. */
end-method;


/* Takes the rsData rowset property and uses it to publish a report, using other property values. */
method PublishXMLReportFromRS
   
   %This.CreateXMLFilesFromRS();
   
   /* Generate the report. */
   Local any &oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn); /* report definition name */
   &oRptDefn.Get();
   &oRptDefn.SetRuntimeDataXMLFile(%This.xmlFileName); /* Connect report and data. */
   &oRptDefn.ProcessReport(&sReportTemplate, %Language_User, %Date, ""); /* report's template ID */
   &oRptDefn.OutDestination = %FilePath;
   
   /* Publish to Report Manager. */
   &oRptDefn.Publish("", "", "", &processInstance);
   
end-method;

method CreateXMLFilesFromRS
   
   /* Create XSD file. */
   Local PSXP_XMLGEN:RowSetDS &oXML_GENERATOR = create psxp_xmlgen:RowSetDS();
   Local string &my_schema = &oXML_GENERATOR.getXSDSchema(&rsData);
   Local File &XSD_File = GetFile(&sFileNameXSD, "W");
   &XSD_File.WriteLine(&my_schema);
   &XSD_File.Close();
   
   /* Create XML file. */
   Local string &my_xml = &oXML_GENERATOR.getXMLData(&rsData, "");
   Local File &XML_File = GetFile(&sFileNameXML, "W");
   &XML_File.WriteLine(&my_xml);
   %This.xmlFileName = &XML_File.Name;
   &XML_File.Close();
   
end-method;

Creating Section Breaks in XML Publisher/BI Publisher

Rather than using for-each:GROUP_NAME you use for-each@section:GROUP_NAME, every time xmlp hits a new member of GROUP_NAME the page numbering and header is reset.

(from www.orafaq.com forums)

Setting Up BI Publisher/XML Publisher

This can be fun. I had a lot of trouble with an error message stating I couldn't install the plug-in while Word was running. Shut down process after process to no avail.

Solution:
1. Download to your desktop the install file from Main Menu > Reporting Tools > XML Publisher > Setup > Design Helper.
2. Restart the machine in Safe Mode and install.

I also encountered further problems when trying to use some of the features such as the Chart object. Reinstall BI Publisher by navigating to: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\DotNetInstallFiles\setup.exe and following the prompts (next, ok, next, yes etc.)

XML / BI Publisher Example, using XML File as the Data Source

Note that using a Data Source of "XMLDoc" or "RowSet" has been deprecated. If you are going to create a new XML Publisher Report you are going to have to use a data source of "XML File" or "PS Query".

This is probably a good way of going about it anyway because you need to create the XML and XSD files before you can create the template anyway (unless you want to make them by hand).

Following is a simple example that could use some flourish, but illustrates the method clearly.



/* Super simple example of creating a XML Publisher report based on RowSet data.
   Note that producing reports from RowSet data or XMLDoc data has been deprecated.
   This example uses a Data Source of 'XML File'. */

import PSXP_XMLGEN:*;
import PSXP_RPTDEFNMANAGER:*;


/* Create Rowset(s) data for the report, however you need. */
Local Rowset &rsData = CreateRowset(Record.AMS_SOA_PROG_VW);
&rsData.Fill("WHERE EMPLID LIKE '3064%'");


Local string &sFileNameXSD = "Rowset.xsd";
Local string &sFileNameXML = "Rowset.xml";
Local string &sRptDefn = "AMS_SOA_XML";
Local string &sReportTemplate = "AMS_SOA_XML_1";

/* Create XSD file. */
Local PSXP_XMLGEN:RowSetDS &oXML_GENERATOR = create psxp_xmlgen:RowSetDS();
Local string &my_schema = &oXML_GENERATOR.getXSDSchema(&rsData);
Local File &XSD_File = GetFile(&sFileNameXSD, "W");
&XSD_File.WriteLine(&my_schema);
&XSD_File.Close();

/* Create XML file. */
Local string &my_xml = &oXML_GENERATOR.getXMLData(&rsData, "");
Local File &XML_File = GetFile(&sFileNameXML, "W");
&XML_File.WriteLine(&my_xml);
Local string &xmlFileName = &XML_File.Name; /* File.Name not available after File.Close() */
&XML_File.Close();


/* Generate the report. */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn); /* report definition name */
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&xmlFileName); /* Connect report and data. */
&oRptDefn.ProcessReport(&sRptTemplate, %Language_User, %Date, ""); /* report's template ID */
&oRptDefn.OutDestination = %FilePath;

/* Publish to Report Manager. */
&oRptDefn.Publish("", "", "", AMS_SOA_AET.PROCESS_INSTANCE);