CREATE OR REPLACE PROCEDURE internal.ptransferparentconsenthomelearning(IN par_dataimportid BIGINT, IN par_validateonly NUMERIC DEFAULT 0,
     IN par_debugmode INTEGER DEFAULT 0, IN par_logresult NUMERIC DEFAULT 0, INOUT return_code int DEFAULT 0,
     INOUT p_refcur refcursor DEFAULT NULL)
AS 
$BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_SourceDataImportID BIGINT DEFAULT (SELECT
        sourcedataimportid
        FROM internal.dataimport
        WHERE dataimportid = par_DataImportID);
    var_SourceDataDate TIMESTAMP WITHOUT TIME ZONE;
    var_SourceDataDateS VARCHAR(20);
    var_dbname VARCHAR(128) DEFAULT current_database();
    var_LogDescription VARCHAR(255);
    var_LogData VARCHAR(2048);
    var_RowCount INTEGER;
    var_LogLevel CHAR(1);
    var_Sql TEXT;
    var_SysStartTime TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT timezone('UTC', CURRENT_TIMESTAMP(6));
    var_LogText VARCHAR(8000);
    var_LastUpdate TIMESTAMP WITHOUT TIME ZONE DEFAULT clock_timestamp();
    var_Batch INTEGER DEFAULT COALESCE((SELECT
        MAX(batch)
        FROM dbo.sourceparent), 0);
    pdataimportlogtext$par_LogText VARCHAR;
    pdataimportlogtext VARCHAR;
    -- pdataimportlogtext$refcur_1 refcursor;
    -- pdataimportlogtext$refcur_2 refcursor;
    -- pdataimportlogtext$refcur_3 refcursor;
    -- pdataimportlogtext$refcur_4 refcursor;
    -- pdataimportlogtext$refcur_5 refcursor;
    -- pdataimportlogtext$refcur_6 refcursor;
    -- pdataimportlogtext$refcur_7 refcursor;
    -- pdataimportlogtext$refcur_8 refcursor;
BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON
    */
RAISE NOTICE 'par_DataImportID: %', par_DataImportID;       
    IF par_DataImportID IS NULL THEN
RAISE NOTICE 'par_LogResult: %', par_LogResult;           
        IF par_LogResult IS NULL OR var_LogLevel = 1 THEN
RAISE NOTICE 'SELECT logtext';                   
            OPEN p_refcur FOR
            SELECT
                COALESCE(var_LogText, '') AS logtext;
        END IF;
        -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %.', '50000', 16, 0, '@DataImportID must be supplied.' USING ERRCODE = '50000';
        return_code := 1;
        RETURN;
    END IF;
    -- CALL ClickCOMMON.dbo.pResetSequence(var_dbname, 'dbo', 'Person', 'SourceParent');
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
    SELECT
    		@SourceDataDate		= DataContractDataDate,
    		@SourceDataDateS	= FORMAT(@SourceDataDate, 'yyyy-MM-dd HH:mm:ss')
    	FROM staging.DataImportDataContract
    	WHERE DataImportID = @SourceDataImportID
    */

    SELECT
        DataContractDataDate,
        TO_CHAR(var_SourceDataDate::DATE, 'yyyy-MM-dd HH:mm:ss')
    INTO
        var_SourceDataDate,
        var_SourceDataDateS
    FROM dbo.dataimportdatacontract_staging
    WHERE DataImportID = var_SourceDataImportID;

RAISE NOTICE 'var_SourceDataDate: %', var_SourceDataDate;           
RAISE NOTICE 'var_SourceDataDateS: %', var_SourceDataDateS;           
RAISE NOTICE 'var_SourceDataImportID: %', var_SourceDataImportID;           

    BEGIN
RAISE NOTICE 'var_TranCountOnEntry: %', var_TranCountOnEntry;               
        IF var_TranCountOnEntry = 0 THEN
            -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %.', '50000', 0, 0, 'BEGIN TRAN' USING ERRCODE = '50000';
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN
            */
        END IF;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('SourceDataImportID=%I64d, @SourceDataDate=%s', @SourceDataImportID, @SourceDataDateS);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
RAISE NOTICE 'CALL internal.pdataimportloginsert';

        CREATE TEMPORARY TABLE t$data
        (rownumber BIGINT NOT NULL,
            submissiondate TIMESTAMP WITHOUT TIME ZONE NOT NULL,
            parentcellnumberreceived VARCHAR(20) NULL,
            parentconfirminterest NUMERIC(1, 0) NULL,
            parentcellnumberpreferredquestion NUMERIC(1, 0) NULL,
            parentcellnumber VARCHAR(20) NULL,
            parentconsent NUMERIC(1, 0) NULL,
            parentemail VARCHAR(100) NULL,
            parentfirstname VARCHAR(50) NULL,
            parentsurname VARCHAR(50) NULL,
            /* */
            parentcellnumberreceivedvalid NUMERIC(1, 0) NULL,
            parentcellnumbervalid NUMERIC(1, 0) NULL,
            personid INTEGER NULL,
            personidnew NUMERIC(1, 0) NOT NULL DEFAULT (0),
            personmatchcount INTEGER NULL,
            updated VARCHAR(200) NOT NULL DEFAULT (''));
RAISE NOTICE 'CREATE TEMPORARY TABLE t$data';               

        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers., 9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        INSERT #Data
        		(RowNumber, SubmissionDate, ParentCellNumberReceived, ParentConfirmInterest, ParentCellNumberPreferredQuestion, ParentCellNumber, ParentConsent, ParentEmail,
        		ParentFirstName, ParentSurname,
        		ParentCellNumberReceivedValid, ParentCellNumberValid)
        		SELECT
        			D.RowNumber,
        			D.SubmissionUploadDate,
        			CASE
        				WHEN D.ParentCellNumberReceived IS NULL THEN
        					CASE WHEN ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumber        ) = 1 THEN ClickCOMMON.dbo.fFormatCellNumber(D.ParentCellNumber        ) ELSE D.ParentCellNumber END
        				ELSE
        					CASE WHEN ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumberReceived) = 1 THEN ClickCOMMON.dbo.fFormatCellNumber(D.ParentCellNumberReceived) ELSE D.ParentCellNumberReceived END
        			END,
        			CASE WHEN D.ParentConfirmInterest = 'want_info' THEN 1 ELSE 0 END ParentConfirmInterest,
        			CASE WHEN D.ParentCellNumberPreferredQuestion = 'False' THEN 1 ELSE 0 END,
        			CASE
        				WHEN D.ParentCellNumberReceived IS NULL THEN NULL
        				WHEN ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumber) = 1 THEN ClickCOMMON.dbo.fFormatCellNumber(D.ParentCellNumber)
        				ELSE D.ParentCellNumber
        			END,
        			CASE WHEN D.ParentConsent = 'Consent_given' THEN 1 ELSE 0 END ParentConsent,
        			LOWER(D.ParentEmail),
        			TRIM(D.ParentFirstName),
        			TRIM(D.ParentSurname),
        			CASE
        				WHEN D.ParentCellNumberReceived IS NULL THEN ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumber)
        				ELSE ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumberReceived)
        			END,
        			CASE
        				WHEN D.ParentCellNumberReceived IS NULL THEN NULL
        				WHEN D.ParentCellNumber IS NULL THEN NULL
        				ELSE ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumber)
        			END
        		FROM ClickSTAGING.dbo.ParentInfoConsentHomeLearning D
        		WHERE D.DataImportID = @SourceDataImportID
        			AND D.SubmissionIsIncomplete = 'False'
        		ORDER BY D.RowNumber
        */
        INSERT INTO t$data
                (RowNumber, SubmissionDate, ParentCellNumberReceived, ParentConfirmInterest, ParentCellNumberPreferredQuestion, 
                    ParentCellNumber, ParentConsent, ParentEmail,
                    ParentFirstName, ParentSurname,
                    ParentCellNumberReceivedValid, ParentCellNumberValid)
                SELECT
                    D.RowNumber,
                    D.SubmissionUploadDate,
                    --CASE
                    --    WHEN D.ParentCellNumberReceived IS NULL THEN
                    --        CASE WHEN ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumber        ) = 1 THEN ClickCOMMON.dbo.fFormatCellNumber(D.ParentCellNumber        ) ELSE D.ParentCellNumber END
                    --    ELSE
                    --        CASE WHEN ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumberReceived) = 1 THEN ClickCOMMON.dbo.fFormatCellNumber(D.ParentCellNumberReceived) ELSE D.ParentCellNumberReceived END
                    --END,
                    D.ParentCellNumber,
                    CASE WHEN D.ParentConfirmInterest = 'want_info' THEN 1 ELSE 0 END ParentConfirmInterest,
                    CASE WHEN D.ParentCellNumberPreferredQuestion = 'False' THEN 1 ELSE 0 END,
                    --CASE
                    --    WHEN D.ParentCellNumberReceived IS NULL THEN NULL
                    --    WHEN ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumber) = 1 THEN ClickCOMMON.dbo.fFormatCellNumber(D.ParentCellNumber)
                    --    ELSE D.ParentCellNumber
                    --END,
                    D.ParentCellNumberReceived,
                    CASE WHEN D.ParentConsent = 'Consent_given' THEN 1 ELSE 0 END ParentConsent,
                    LOWER(D.ParentEmail),
                    TRIM(D.ParentFirstName),
                    TRIM(D.ParentSurname),
                    --CASE
                    --    WHEN D.ParentCellNumberReceived IS NULL THEN ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumber)
                    --    ELSE ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumberReceived)
                    --END,
                    D.ParentCellNumberReceived,
                    --CASE
                    --    WHEN D.ParentCellNumberReceived IS NULL THEN NULL
                    --    WHEN D.ParentCellNumber IS NULL THEN NULL
                    --    ELSE ClickCOMMON.dbo.fIsCellNumber(D.ParentCellNumber)
                    --END
                    D.ParentCellNumberReceived
                FROM dbo.parentinfoconsenthomelearning_staging D
                WHERE D.DataImportID = var_SourceDataImportID
                    AND D.SubmissionIsIncomplete::text = 'False'
                ORDER BY D.RowNumber;
RAISE NOTICE 'INSERT INTO t$data';               

        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Complete Data Rows: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        var_RowCount := (SELECT
            COUNT(*)
            FROM t$data
            WHERE parentcellnumberreceivedvalid = 0);
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Invalid ParentCellNumberReceived Rows: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        DELETE FROM t$data
            WHERE parentcellnumberreceivedvalid = 0;
        DELETE FROM t$data AS D
        USING (SELECT
            d.parentcellnumberreceived, MAX(d.submissiondate) AS maxsubmissiondate
            FROM t$data AS d
            GROUP BY d.parentcellnumberreceived
            HAVING COUNT(*) > 1) AS x
            WHERE LOWER(d.parentcellnumberreceived) = LOWER(x.parentcellnumberreceived) AND d.submissiondate <> x.maxsubmissiondate;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Duplicates removed: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        var_RowCount := (SELECT
            COUNT(*)
            FROM t$data
            WHERE parentcellnumbervalid = 0);
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Invalid ParentCellNumber Rows: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /*
        [7927 - Severity CRITICAL - PostgreSQL doesn't support OUTER joins for self-reference to the label from UPDATE / DELETE in the FROM clause and without PK on table-for-update. Perform a manual conversion.]
        UPDATE D SET
        			PersonID			= CASE WHEN X.MatchCount <= 1 THEN X.PersonID ELSE NULL END,
        			PersonMatchCount	= COALESCE(X.MatchCount,0)
        		FROM #Data D
        		LEFT JOIN
        		(
        			SELECT D.RowNumber, COUNT(DISTINCT PC.PersonID) MatchCount, MIN(PC.PersonID) PersonID
        			FROM #Data D
        			JOIN PersonHasContact PC ON PC.ContactTypeID = 1 AND D.ParentCellNumberReceived = PC.[Value]
        			JOIN PersonRoleHasPerson PRP ON PC.PersonID = PRP.PersonID AND PRP.PersonRoleID = 14
        			GROUP BY D.RowNumber
        		) X ON D.RowNumber = X.RowNumber;
        */
        var_RowCount := (SELECT
            COUNT(*)
            FROM t$data
            WHERE personmatchcount > 1);
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Ignoring multiple matches: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        var_RowCount := (SELECT
            COUNT(*)
            FROM t$data
            WHERE personmatchcount = 0);
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('No matches: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        var_RowCount := (SELECT
            COUNT(*)
            FROM t$data
            WHERE personmatchcount = 1);
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Matches: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        DELETE FROM t$data AS D
            WHERE d.personmatchcount > 1;
        /* Existing Parent Update FirstName/Lastname */
        
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE D SET Updated = Updated + ',Update Names'
        		FROM Person P
        		JOIN #Data D ON P.PersonID = D.PersonID
        		WHERE  ((P.FirstName IS NULL AND D.ParentFirstName IS NOT NULL) OR (P.FirstName <> D.ParentFirstName))
        			OR ((P.LastName  IS NULL AND D.ParentSurname   IS NOT NULL) OR (P.LastName  <> D.ParentSurname))
        */
        
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE P SET
        			FirstName	= D.ParentFirstName,
        			LastName	= D.ParentSurname
        		FROM Person P
        		JOIN #Data D ON P.PersonID = D.PersonID
        		WHERE  ((P.FirstName IS NULL AND D.ParentFirstName IS NOT NULL) OR (P.FirstName <> D.ParentFirstName))
        			OR ((P.LastName  IS NULL AND D.ParentSurname   IS NOT NULL) OR (P.LastName  <> D.ParentSurname))
        */
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Existing Parent FirstName/LastName update where different: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* Existing Parent Update Email */
        
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE D SET Updated = Updated + ',Update Email'
        		FROM #Data D
        		JOIN PersonHasContact PC ON D.PersonID = PC.PersonID AND PC.ContactTypeID = 4 AND PC.[Order] = 1
        		WHERE D.ParentEmail IS NOT NULL AND LEN(D.ParentEmail) >= 5 AND D.ParentEmail <> PC.[Value]
        */
        
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE PC SET Value = D.ParentEmail, Consent = COALESCE(D.ParentConsent,0)
        		FROM #Data D
        		JOIN PersonHasContact PC ON D.PersonID = PC.PersonID AND PC.ContactTypeID = 4 AND PC.[Order] = 1
        		WHERE D.ParentEmail IS NOT NULL AND LEN(D.ParentEmail) >= 5 AND D.ParentEmail <> PC.[Value]
        */
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Existing Parent Email update: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /*
        [7927 - Severity CRITICAL - PostgreSQL doesn't support OUTER joins for self-reference to the label from UPDATE / DELETE in the FROM clause and without PK on table-for-update. Perform a manual conversion.]
        UPDATE D SET Updated = Updated + ',Insert Email'
        		FROM #Data D
        		JOIN Person P ON D.PersonID = P.PersonID
        		LEFT JOIN PersonHasContact PC ON D.PersonID = PC.PersonID AND PC.ContactTypeID = 4 AND PC.[Order] = 1
        		WHERE D.ParentEmail IS NOT NULL AND LEN(D.ParentEmail) >= 5
        			AND PC.PersonID IS NULL;
        */
        INSERT INTO dbo.personhascontact (personid, contacttypeid, "Order", value, consent)
        SELECT DISTINCT
            d.personid, 4, 1, d.parentemail, COALESCE(d.parentconsent, 0)
            FROM t$data AS d
            JOIN dbo.person AS p
                ON d.personid = p.personid
            LEFT OUTER JOIN dbo.personhascontact AS pc
                ON d.personid = pc.personid AND pc.contacttypeid = 4 AND pc."Order" = 1
            WHERE d.parentemail IS NOT NULL AND LENGTH(d.parentemail) >= 5 AND pc.personid IS NULL;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Existing Parent Email insert: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* Existing Parent Update preferred cell number */
        CREATE TEMPORARY TABLE t$preferred
        AS
        SELECT
            d.personid, MAX(d.parentcellnumber) AS parentcellnumber, MAX(CAST (d.parentconsent AS INTEGER)) AS parentconsent
            FROM t$data AS d
            JOIN dbo.person AS p
                ON d.personid = p.personid
            LEFT OUTER JOIN dbo.personhascontact AS pc
                ON p.personid = pc.personid AND pc.contacttypeid = 1 AND pc."Order" = 1
            WHERE d.parentcellnumberpreferredquestion = 1 AND d.parentcellnumbervalid = 1 AND (LOWER(d.parentcellnumber) <> LOWER(d.parentcellnumberreceived)) AND ((pc.personid IS NULL) OR (pc.personid IS NOT NULL AND LOWER(d.parentcellnumber) <> LOWER(pc.value)))
            GROUP BY d.personid;
        DELETE FROM dbo.personhascontact AS P
        USING t$preferred AS p, dbo.personhascontact AS pc
            WHERE p.personid = pc.personid AND pc.contacttypeid = 1 AND LOWER(p.parentcellnumber) = LOWER(pc.value);
        UPDATE dbo.personhascontact AS p
        SET "Order" = x.neworder
        FROM dbo.personhascontact AS pc
        JOIN (SELECT
            pc.personid, pc.contacttypeid, pc."Order", ROW_NUMBER() OVER (PARTITION BY pc.personid ORDER BY pc."Order") + 1 AS neworder
            FROM t$preferred AS p
            JOIN dbo.personhascontact AS pc
                ON p.personid = pc.personid AND pc.contacttypeid = 1) AS x
            ON pc_dml.personid = x.personid AND pc_dml.contacttypeid = x.contacttypeid AND pc_dml."Order" = x."Order";
        INSERT INTO dbo.personhascontact (personid, contacttypeid, "Order", value, consent)
        SELECT
            p.personid, 1, 1, p.parentcellnumber, p.parentconsent
            FROM t$preferred AS p;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Existing Parent Preferred Cell: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$data AS d
        SET updated = updated || ',Update Preferred Cell'
        FROM t$preferred AS p
            WHERE d.personid = p.personid;
        DROP TABLE t$preferred;
        /* Existing Parent update consent */
        UPDATE t$data AS d
        SET updated = updated || ',Update Consent'
        FROM t$data AS d
        JOIN (SELECT DISTINCT
            pc.personid
            FROM t$data AS d
            JOIN dbo.personhascontact AS pc
                ON d.personid = pc.personid
            WHERE (d.parentconsent <> pc.consent) OR (pc.consent IS NULL)) AS x
            ON d.personid = x.personid;
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE PC SET Consent = D.ParentConsent
        		FROM #Data D
        		JOIN PersonHasContact PC ON D.PersonID = PC.PersonID
        		WHERE (D.ParentConsent <> PC.Consent) OR (PC.Consent IS NULL)
        */
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Existing Parent Consent: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* Update PersonHasDataSource */
        UPDATE dbo.personhasdatasource AS d
        SET latestdataimportid = par_DataImportID, updatedtime = var_SourceDataDate
        FROM t$data AS d, dbo.personhasdatasource AS pds
            WHERE d.personid = pds.personid AND pds.datasourcetypeid = 3;
        INSERT INTO dbo.personhasdatasource (personid, datasourcetypeid, priority, latestdataimportid, updatedtime)
        SELECT DISTINCT
            d.personid, dst.datasourcetypeid, dst.priority, par_DataImportID, var_SourceDataDate
            FROM t$data AS d
            JOIN dbo.person AS p
                ON d.personid = p.personid
            JOIN dbo.datasourcetype AS dst
                ON dst.datasourcetypeid = 3
            LEFT OUTER JOIN dbo.personhasdatasource AS pds
                ON d.personid = pds.personid AND pds.datasourcetypeid = 3
            WHERE pds.personid IS NULL;
        /* Update DataImportAudit */
        INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
        SELECT
            par_DataImportID, 'U', 'Person', d.personid, SUBSTR(d.updated, 2, 200)
            FROM t$data AS d
            WHERE d.personid IS NOT NULL AND LOWER(d.updated) <> LOWER('');
        /* Insert Parents */
        UPDATE t$data
        SET personidnew = 1
            WHERE personid IS NULL AND parentconsent = 1;
        UPDATE t$data
        SET personid = nextval('dbo.personsequence')
            WHERE personidnew = 1;
        INSERT INTO dbo.person (personid, firstname, lastname)
        SELECT
            d.personid, d.parentfirstname, d.parentsurname
            FROM t$data AS d
            WHERE d.personidnew = 1;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Parent Person inserted: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        INSERT INTO internal.dataimportaudit (dataimportid, action, item, itemid, detail)
        SELECT
            par_DataImportID, 'C', 'Person', d.personid, d.parentfirstname || ' ' || d.parentsurname
            FROM t$data AS d
            WHERE d.personidnew = 1;
        /* Insert PersonRoleHasPerson */
        INSERT INTO dbo.personrolehasperson (personroleid, personid)
        SELECT DISTINCT
            14, /* Parent */ d.personid
            FROM t$data AS d
            WHERE d.personidnew = 1;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('PersonRoleHasPerson inserted: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* Insert contact details */
        CREATE TEMPORARY TABLE t$personhascontact
        AS
        SELECT
            *
            FROM (SELECT
                d.personid, 1 AS contacttypeid, 1 AS "Order", d.parentcellnumber AS value, d.parentconsent AS consent
                FROM t$data AS d
                WHERE d.personidnew = 1 AND d.parentcellnumbervalid = 1 AND d.parentcellnumberpreferredquestion = 1
            UNION
            SELECT
                d.personid, 1 AS contacttypeid,
                CASE
                    WHEN d.parentcellnumbervalid = 1 AND d.parentcellnumberpreferredquestion = 1 THEN 2
                    ELSE 1
                END AS "Order", d.parentcellnumberreceived, d.parentconsent
                FROM t$data AS d
                WHERE d.personidnew = 1 AND d.parentcellnumberreceivedvalid = 1
            UNION
            SELECT
                d.personid, 4 AS contacttypeid, 1 AS "Order", d.parentemail, d.parentconsent
                FROM t$data AS d
                WHERE d.personidnew = 1 AND LENGTH(d.parentemail) >= 5) AS x
            ORDER BY personid NULLS FIRST, contacttypeid NULLS FIRST, "Order" NULLS FIRST;
        CREATE INDEX ix_t$personhascontact_ux ON t$personhascontact
            (personid, contacttypeid, "Order");
        INSERT INTO dbo.personhascontact (personid, contacttypeid, "Order", value, consent)
        SELECT
            s.personid, s.contacttypeid, s."Order", s.parentcellnumber, s.parentconsent
            FROM t$personhascontact AS s
            LEFT OUTER JOIN dbo.personhascontact AS t
                ON t.personid = s.personid AND t.contacttypeid = s.contacttypeid AND t."Order" = s."Order"
            WHERE t.personid IS NULL;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('PersonHasContact inserted: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* Update PersonHasDataSource */
        INSERT INTO dbo.personhasdatasource (personid, datasourcetypeid, priority, latestdataimportid, updatedtime)
        SELECT
            d.personid, dst.datasourcetypeid, dst.priority, par_DataImportID, var_SourceDataDate
            FROM t$data AS d
            JOIN dbo.datasourcetype AS dst
                ON dst.datasourcetypeid = 3
            WHERE d.personidnew = 1;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('PersonHasDataSource inserted: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* SourceParent */
        CREATE TEMPORARY TABLE t$sourceparentmapping
        AS
        SELECT DISTINCT
            *
            FROM (SELECT
                sp.sourceparentid, d.rownumber
                FROM dbo.sourceparent AS sp
                JOIN t$data AS d
                    ON sp.parentpersonid = d.personid
            UNION
            SELECT
                sp.sourceparentid, d.rownumber
                FROM t$data AS d
                LEFT OUTER JOIN dbo.sourceparent AS sp
                    ON (LOWER(sp.parentcell1) = LOWER(d.parentcellnumberreceived) OR LOWER(sp.parentcell2) = LOWER(d.parentcellnumberreceived))
                WHERE (d.personidnew = 1 OR d.personid IS NULL)) AS x;
        UPDATE dbo.sourceparent AS m
        SET parentfirstname =
        CASE
            WHEN d.parentfirstname IS NOT NULL AND LOWER(d.parentfirstname) <> LOWER('') THEN d.parentfirstname
            ELSE sp.parentfirstname
        END, parentsurname =
        CASE
            WHEN d.parentsurname IS NOT NULL AND LOWER(d.parentsurname) <> LOWER('') THEN d.parentsurname
            ELSE sp.parentsurname
        END, parentcell1 =
        CASE
            WHEN d.parentcellnumberpreferredquestion = 1 THEN d.parentcellnumber
            ELSE d.parentcellnumberreceived
        END, parentcell2 =
        CASE
            WHEN d.parentcellnumberpreferredquestion = 1 THEN sp.parentcell1
            ELSE sp.parentcell2
        END, parentconsent = d.parentconsent, parentemail =
        CASE
            WHEN d.parentemail IS NOT NULL AND LENGTH(d.parentemail) >= 5 THEN d.parentemail
            ELSE sp.parentemail
        END, parentpersonid =
        CASE
            WHEN sp.parentpersonid IS NOT NULL THEN d.personid
            ELSE sp.parentpersonid
        END, sourcedataimportid = var_SourceDataImportID, sourcerownumber = d.rownumber, sourcedatadate = var_SourceDataDate, lastupdate = var_LastUpdate
        FROM dbo.sourceparent AS sp
        JOIN t$data AS d
            ON m.rownumber = d.rownumber
            WHERE m.sourceparentid = sp.sourceparentid;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('SourceParent updated: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        INSERT INTO dbo.sourceparent (parentfirstname, parentsurname, parenttype, parentcell1, parentcell2, parentconsent, parentemail, emiscode, learnerfirstname, learnersurname, sourcedataimportid, sourcerownumber, sourcedatadate, lastupdate, parentpersonid, batch)
        SELECT
            COALESCE(d.parentfirstname, ''), COALESCE(d.parentsurname, ''), '',
            CASE
                WHEN d.parentcellnumberpreferredquestion = 1 THEN d.parentcellnumber
                ELSE d.parentcellnumberreceived
            END,
            CASE
                WHEN d.parentcellnumberpreferredquestion = 1 THEN d.parentcellnumberreceived
                ELSE NULL
            END, d.parentconsent, d.parentemail, '', '', '', var_SourceDataImportID, d.rownumber, var_SourceDataDate, var_LastUpdate, d.personid, var_Batch
            FROM t$sourceparentmapping AS m
            JOIN t$data AS d
                ON m.rownumber = d.rownumber
            WHERE m.sourceparentid IS NULL;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('SourceParent insert: %d', @RowCount);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF var_TranCountOnEntry = 0 THEN
            -- RAISE 'Error %, severity %, state % was -- RAISEd. Message: %.', '50000', 0, 0, 'COMMIT TRAN' USING ERRCODE = '50000';
            COMMIT;
        END IF;
        -- CALL internal.pdataimportlogtext(par_DataImportID := par_DataImportID, par_LogResult := 0, par_Select := 1, p_refcur => pdataimportlogtext$refcur_1, p_refcur_2 => pdataimportlogtext$refcur_2, p_refcur_3 => pdataimportlogtext$refcur_3, p_refcur_4 => pdataimportlogtext$refcur_4, p_refcur_5 => pdataimportlogtext$refcur_5, p_refcur_6 => pdataimportlogtext$refcur_6, p_refcur_7 => pdataimportlogtext$refcur_7, p_refcur_8 => pdataimportlogtext$refcur_8);
        -- select * from internal.pdataimportlogtext(par_DataImportID, pdataimportlogtext, 0, 1);

        -- CLOSE pdataimportlogtext$refcur_1;
        -- CLOSE pdataimportlogtext$refcur_2;
        -- CLOSE pdataimportlogtext$refcur_3;
        -- CLOSE pdataimportlogtext$refcur_4;
        -- CLOSE pdataimportlogtext$refcur_5;
        -- CLOSE pdataimportlogtext$refcur_6;
        -- CLOSE pdataimportlogtext$refcur_7;
        -- CLOSE pdataimportlogtext$refcur_8;
        EXCEPTION
            WHEN OTHERS THEN
                -- CALL internal.pdataimportlogtext(par_DataImportID := par_DataImportID, par_LogResult := 0, par_Select := 1, par_LogText => pdataimportlogtext$par_LogText, p_refcur => pdataimportlogtext$refcur_1, p_refcur_2 => pdataimportlogtext$refcur_2, p_refcur_3 => pdataimportlogtext$refcur_3, p_refcur_4 => pdataimportlogtext$refcur_4, p_refcur_5 => pdataimportlogtext$refcur_5, p_refcur_6 => pdataimportlogtext$refcur_6, p_refcur_7 => pdataimportlogtext$refcur_7, p_refcur_8 => pdataimportlogtext$refcur_8);
                -- select * from internal.pdataimportlogtext(par_DataImportID, pdataimportlogtext, 0, 1);

                -- CLOSE pdataimportlogtext$refcur_1;
                -- CLOSE pdataimportlogtext$refcur_2;
                -- CLOSE pdataimportlogtext$refcur_3;
                -- CLOSE pdataimportlogtext$refcur_4;
                -- CLOSE pdataimportlogtext$refcur_5;
                -- CLOSE pdataimportlogtext$refcur_6;
                -- CLOSE pdataimportlogtext$refcur_7;
                -- CLOSE pdataimportlogtext$refcur_8;
                /*
                [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
                IF @TranCountOnEntry = 0 AND @@TRANCOUNT > 0
                		BEGIN
                			-- RAISERROR('ROLLBACK TRAN (CATCH)', 0, 0) WITH NOWAIT;
                			ROLLBACK TRAN;
                		END;
                */
                -- RAISE;
    END;
    /*
    
    DROP TABLE IF EXISTS t$data;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$preferred;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$personhascontact;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$sourceparentmapping;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$
LANGUAGE plpgsql;