› Resolved issues › Analytics for Kofax AP Agility › Database update - Analytics mismatches

Database update - Analytics mismatches

As a resolution for bug 1987961, you can ensure that existing invoices are displayed correctly in Analytics for Kofax AP Agility by performing a database update.

For the status to correctly reflect, perform a complete data deletion and then reload from the beginning. Also, it is best to perform this operation when no invoices are being processed.

In an on-premise environment run the following queries. In an OPMT or Azure environments ask your Kofax TotalAgility Cloud Administrator or Service provider representative to run the following queries: 

  1. /**** Query 1 - correct status in DOCUMENT_STATUS table where document is sent for ReScan.
	Please change the DB Schema names as per the target environment
	[live] - This is KTA main DB
	[apa_live] - This is Ap Agility DB
****/
-- Move documents in RescanRejectedDocs to state 11 (new)
-- Move documents in RescanRejectedDocs to state 11 (new)
UPDATE [apa_live].[DOCUMENT_STATUS] SET STATE_ID = 11 WHERE id IN
(
SELECT ds.id from [apa_live].[DOCUMENT_STATUS] ds
JOIN [live].[DocumentData] d ON d.Id = ds.DOC_ID
JOIN [live].[FolderData] f ON d.ParentId = f.InternalId
WHERE ds.STATE_ID = 8 AND CONVERT(nvarchar(100), f.id) IN(
select InstanceId from (
select Attributes.Attribute.query('.').value('.', 'varchar(max)') as InstanceId, a.OWNER_ID as JobId, 'A' as JobState
from (select CAST(var_value as xml) as x, OWNER_ID
from [live].[VARIABLE] where VAR_TYPE = 32759) a
cross apply x.nodes('/FolderVariableValue/InstanceId') Attributes (Attribute)
) FolderInstance where FolderInstance.InstanceId in
(
select CONVERT(nvarchar(100), f.id) as FolderId
from [apa_live].[DOCUMENT_STATUS] ds
JOIN [live].[DocumentData] d ON d.Id = ds.DOC_ID
JOIN [live].[FolderData] f ON d.ParentId = f.InternalId
WHERE ds.STATE_ID = 8
)
AND JobId IN (SELECT JOB_ID FROM [live].[JOB_HISTORY] WHERE PROCESS_NAME = 'RescanRejectedDocs')
)
)

    /**** Query 2 - correct status in INVOICE_HEADER table to match the state at IP side ****/

UPDATE h
SET h.CURRENT_STATE_MSG_ID = d.STATE_ID
FROM [apa_live].[INVOICE_HEADER] h
INNER JOIN [apa_live].[DOCUMENT_STATUS] d ON (h.INVOICE_ID = d.AP_INVOICE_ID)
WHERE h.CURRENT_STATE_CODE IN ('IP_VALIDATION', 'WAITING_FOR_REMATCH')

  2. /*****
	Queries to correct the DOCUMENT_STATUS, INVOICE_HEADER table status when the jobs are Suspended/Terminated/On-hold/Evaluation Failed.
	Please change the DB Schema names as per the target environment
	[live] - This is KTA main DB
	[apa_live] - This is Ap Agility DB
*****/
-- Add Canceled state to INVOICE_STATES table as it is not available at IP side.
INSERT INTO [apa_live].INVOICE_STATES (ID, STATE_NAME)
	SELECT 145, 'Canceled' WHERE NOT EXISTS (SELECT ID FROM [apa_live].INVOICE_STATES WHERE ID = 145)
-- Create TEMPORARY table to store the document ids whose corresponding jobs are Suspended/Terminated/On-hold/Evaluation Failed.
IF OBJECT_ID(N'tempdb..#T6061BB02F8F94B9A9810461D8B83B77E') IS NOT NULL
BEGIN
	DROP TABLE #T6061BB02F8F94B9A9810461D8B83B77E
END

CREATE TABLE #T6061BB02F8F94B9A9810461D8B83B77E(DOC_ID VARCHAR(50), JOB_STATUS int)

/*
	Insert document id, Job status to the temp table
*/
INSERT INTO #T6061BB02F8F94B9A9810461D8B83B77E
	SELECT DD.Id, JS.JOB_STATUS from 
	/* Query to get folder instance id from the folder variable xml */
	(select Attributes.Attribute.query('.').value('.', 'varchar(max)') as InstanceId, a.OWNER_ID
				from ( SELECT CAST(var_value as xml) as x, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE from (
					select var_value, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE	from [live].[VARIABLE]
					UNION
					select var_value, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE	from [live].[FINISHED_JOB_VARIABLE]) V  where V.VAR_TYPE = 32759) a
				cross apply x.nodes('/FolderVariableValue/InstanceId') Attributes (Attribute) ) as FV
	JOIN (
		--Suspended, On-hold, Evaluation failed jobs stay in JOB table
		SELECT JOB_ID, JOB_STATUS FROM [live].JOB WHERE JOB_STATUS IN (3,7,12)
		UNION
		--Terminated jobs will be moved to FINISHED_JOB table
		SELECT JOB_ID, JOB_STATUS FROM [live].FINISHED_JOB WHERE JOB_STATUS = 2) JS
		ON JS.JOB_ID = FV.OWNER_ID 
	JOIN [live].FolderData FD 
		ON CONVERT(NVARCHAR(50), FD.Id) = FV.InstanceId
	JOIN [live].DocumentData  DD
		ON FD.InternalId = DD.ParentId
	JOIN [apa_live].DOCUMENT_STATUS DS 
		ON DS.DOC_ID = CONVERT(NVARCHAR(50),DD.Id)

	UNION

	SELECT DV.InstanceId, JS.JOB_STATUS FROM
	/* Query to get document instance id from the document variable xml */
	(select Attributes.Attribute.query('.').value('.', 'varchar(max)') as InstanceId, a.OWNER_ID
				from ( SELECT CAST(var_value as xml) as x, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE	from (
					select var_value, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE	from [live].[VARIABLE]
					UNION
					select var_value, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE	from [live].[FINISHED_JOB_VARIABLE]) V WHERE V.VAR_TYPE = 32760) a
				cross apply x.nodes('/DocumentVariableValue/InstanceId') Attributes (Attribute) ) as DV
	JOIN (
		--Suspended, On-hold, Evaluation failed jobs stay in JOB table
		SELECT JOB_ID, JOB_STATUS FROM [live].JOB WHERE JOB_STATUS IN (3,7,12)
		UNION
		--Terminated jobs will be moved to FINISHED_JOB table
		SELECT JOB_ID, JOB_STATUS FROM [live].FINISHED_JOB WHERE JOB_STATUS = 2) JS
		ON JS.JOB_ID = DV.OWNER_ID 
	JOIN [apa_live].DOCUMENT_STATUS DS 
		ON DS.DOC_ID = DV.InstanceId

-- Set status as canceled and FINISH_DATE for Terminated jobs.
UPDATE [apa_live].DOCUMENT_STATUS SET
	STATE_ID = 145, FINISH_DATE = CURRENT_TIMESTAMP 
	WHERE DOC_ID IN 	
	(SELECT DOC_ID FROM #T6061BB02F8F94B9A9810461D8B83B77E WHERE JOB_STATUS = 2)

-- Set status as error for Suspended, On-hold and Evaluation Failed jobs.
UPDATE [apa_live].DOCUMENT_STATUS SET
	STATE_ID = 4 WHERE DOC_ID IN 	
	(SELECT DOC_ID FROM #T6061BB02F8F94B9A9810461D8B83B77E WHERE JOB_STATUS IN (3,7,12))

-- Set status as canceled and FINISH_DATE for Terminated jobs.
UPDATE [apa_live].INVOICE_HEADER SET INVOICE_STATUS = 'INVOICE_CANCELLED', CURRENT_STATE = 'Canceled', 
		CURRENT_STATE_CODE = 'INVOICE_STATE_CODE', CURRENT_STATE_MSG_ID = 145 
		WHERE APA_DOCUMENT_ID IN (SELECT DOC_ID FROM #T6061BB02F8F94B9A9810461D8B83B77E WHERE JOB_STATUS = 2)

-- Set status as error for Suspended, On-hold and Evaluation Failed jobs.
UPDATE [apa_live].INVOICE_HEADER SET INVOICE_STATUS = 'INVOICE_CANCELLED', CURRENT_STATE = 'Canceled', 
		CURRENT_STATE_CODE = 'INVOICE_STATE_CODE', CURRENT_STATE_MSG_ID = 4 
		WHERE APA_DOCUMENT_ID IN (SELECT DOC_ID FROM #T6061BB02F8F94B9A9810461D8B83B77E WHERE JOB_STATUS IN (3,7,12))

  3. /*****
	This query repairs the DOCUMENT_STATUS and INVOICE_HEADER table entries where the status is set to NULL.
	Please change the DB Schema names as per the target environment
	[live] - This is KTA main DB
	[apa_live] - This is Ap Agility DB
*****/

/*
	Create a temp table to hold the live job, its current activity and document id
*/
IF OBJECT_ID(N'tempdb..#T12345') IS NOT NULL
BEGIN
	DROP TABLE #T12345
END

CREATE TABLE #T12345 (JOB_ID BINARY(16), ACTIVITY_NAME VARCHAR(100), DOC_ID VARCHAR(50))

/*
	Capture live job, its current activity and document id.

*/
INSERT INTO #T12345 
	SELECT LA.JOB_ID, LA.NODE_NAME, ID [DOC_ID] FROM 
		[live].LIVE_ACTIVITY LA LEFT JOIN 
		(
			SELECT DD.Id, OWNER_ID FROM 
			(
				(select Attributes.Attribute.query('.').value('.', 'varchar(max)') as InstanceId, B.OWNER_ID
					from ( SELECT CAST(var_value as xml) as x, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE from (
						select var_value, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE	from [live].[VARIABLE]) A  where A.VAR_TYPE = 32759) B
					cross apply x.nodes('/FolderVariableValue/InstanceId') Attributes(Attribute)) AS FV
				JOIN [live].FolderData FD 
				ON CONVERT(NVARCHAR(50), FD.Id) = InstanceId
				JOIN [live].DocumentData  DD
				ON FD.InternalId = DD.ParentId
			)
			UNION
			SELECT DD.Id, OWNER_ID FROM 
			(
				(select Attributes.Attribute.query('.').value('.', 'varchar(max)') as InstanceId, B.OWNER_ID
					from ( SELECT CAST(var_value as xml) as x, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE	from (
						select var_value, OWNER_ID, VAR_ID, DISPLAY_NAME, VERSION, VAR_TYPE	from [live].[VARIABLE]) A WHERE A.VAR_TYPE = 32760) B
					cross apply x.nodes('/DocumentVariableValue/InstanceId') Attributes (Attribute)) AS DV
				JOIN [live].DOCUMENTDATA DD
				ON DV.INSTANCEID = CONVERT(NVARCHAR(50),DD.Id)
			)
		) AS U
	ON LA.JOB_ID = U.OWNER_ID
	WHERE LA.NODE_NAME IN ('Validation (IPA)', 'Line Pairing Correction (IPA)', 'Line Pairing Exception', 'Exception', 'Invoice Coding', 'Approval')
	AND LA.ACTIVITY_STATUS = 0

/*
	Update the document status with the correct status where the status is set to NULL
*/

UPDATE [apa_live].DOCUMENT_STATUS SET STATE_ID = CASE T.ACTIVITY_NAME
		WHEN 'Line Pairing Correction (IPA)' THEN 10
		WHEN 'Validation (IPA)' THEN 8
		WHEN 'Line Pairing Exception' THEN 5
		WHEN 'Exception' THEN 5
		WHEN 'Invoice Coding' THEN 3
		WHEN 'Approval' THEN 2
	END
	FROM 
		[apa_live].DOCUMENT_STATUS DS
		JOIN #T12345 T 
		ON DS.DOC_ID = T.DOC_ID
		WHERE DS.STATE_ID IS NULL AND DS.DELETED = 0 AND DS.FINISH_DATE IS NULL

/* 
	Update the invoice header table with correct status where the status is set to NULL
*/

UPDATE [apa_live].INVOICE_HEADER SET CURRENT_STATE_MSG_ID = CASE T.ACTIVITY_NAME
		WHEN 'Line Pairing Correction (IPA)' THEN 10
		WHEN 'Validation (IPA)' THEN 8
		WHEN 'Line Pairing Exception' THEN 310
		WHEN 'Exception' THEN 305
		WHEN 'Invoice Coding' THEN 303
		WHEN 'Approval' THEN 301
	END
	FROM 
		[apa_live].DOCUMENT_STATUS DS
		JOIN #T12345 T
		ON DS.DOC_ID = T.DOC_ID 
		JOIN [apa_live].INVOICE_HEADER IH
		ON DS.AP_INVOICE_ID = IH.INVOICE_ID
		WHERE DS.STATE_ID IS NULL AND DS.DELETED = 0 AND DS.FINISH_DATE IS NULL