Reconciliación de Lotes (Batch) de Ventas (SOP) Parte II

Microsoft Dynamics GP
En la parte I llegué hasta el script de SQL que actualizaba únicamente los lotes inconsistentes de ventas. Quedó pendiente convertirlo en un stored procedure:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'gpspSOPBatchesReconcile') BEGIN
	DROP PROCEDURE [dbo].[gpspSOPBatchesReconcile]
END
GO
-- =============================================
-- Description:	Reconcile SOP Batches
-- =============================================
CREATE PROCEDURE [dbo].[gpspSOPBatchesReconcile]
AS
BEGIN
	SET NOCOUNT ON;

	WITH T AS (
		SELECT
			H.[BACHNUMB],
			ISNULL(COUNT(H.[SOPNUMBE]), 0) 'REALNUMOFTRX',
			ISNULL(SUM(H.[DOCAMNT]), 0) 'REALBCHTOTAL'
		FROM
			[dbo].[SOP10100] H
		GROUP BY
			H.[BACHNUMB]
	)
	UPDATE C
	SET
		C.[NUMOFTRX] = T.[REALNUMOFTRX],
		C.[BCHTOTAL] = T.[REALBCHTOTAL]
	FROM
		[dbo].[SY00500] C
	JOIN T ON T.[BACHNUMB] = C.[BACHNUMB]
	WHERE
		C.[SERIES] = 3 AND
		C.[BCHSOURC] = 'Sales Entry' AND
		(C.[NUMOFTRX] - T.[REALNUMOFTRX] <> 0 OR
		C.[BCHTOTAL] - T.[REALBCHTOTAL] <> 0);

	RETURN @@ROWCOUNT;
END
GO
GRANT EXECUTE ON [dbo].[gpspSOPBatchesReconcile] TO [DYNGRP]
GO

En resumen, si el stored procedure existe lo elimina antes de crearlo y luego de creado le asigna permiso de ejecución a DYNGRP. Antes de ejecutar este stored procedure se debe estar ubicado en la base de datos de la compañía correcta. Recuerden seguir las mejores prácticas y siempre hacer backup de la base de datos antes de cualquier cambio!!!

Si llegan a ejecutar el stored procedure verán que devuelve el número de transacciones que actualiza. La idea es que luego del lado de la aplicación tomemos ese valor. Para probar el stored procedure:


DECLARE @Status INT
EXEC @Status = [dbo].[gpspSOPBatchesReconcile]
SELECT @Status 'Status'

O mejor para evitar inyección de código:


DECLARE @Status INT
EXECUTE sp_executesql N'EXEC @Status = [dbo].[gpspSOPBatchesReconcile]', N'@Status INT OUTPUT', @Status = @Status OUTPUT
SELECT @Status 'Status'

En mi caso hago las pruebas en la base de datos Fabrikam, Inc. (TWO). Si no tienen lotes inconsistentes pueden crear inconsistencias manualmente de la siguiente forma:


UPDATE SY00500 SET NUMOFTRX = 0 WHERE BACHNUMB = 'SOP ORDERS'
UPDATE SY00500 SET BCHTOTAL = 0 WHERE BACHNUMB = 'SOP ORDERS'

En Fabrikam, Inc. de forma predeterminada existe un lote de ventas llamado SOP ORDERS y volvemos el número total de transacciones en 0 con el primer UPDATE. Con el segundo volvemos el monto total del lote en 0. Pueden ejecutar solo uno o los dos para crear las inconsistencias y ejecutar el stored procedure para probar. También pueden ejecutarlo sobre otros lotes y hacer las pruebas.

Con esto la parte de SQL está finalizada. Ahora entramos con el código de Dexterity. Cada stored procedure de SQL debe tener su contraparte equivalente del lado de Dexterity llamado Prototype Procedure y debe llamarse de la misma forma que se definió en SQL. Debe contener la definición de los parámetros que recibe el stored procedure y el valor que devuelve. Por el momento, este stored procedure no recibe parámetros. Más adelante lo estaré modificando para crear una nueva versión que recibe parámetros.

Lo ideal de este desarrollo es que quede completamente integrado con el proceso de conciliación de Microsoft Dynamics GP. ¿Qué significa esto? Que luego de ejecutar el botón “Procesar” se ejecuta el código original de los programadores de Microsoft, luego de esto debo ejecutar el stored procedure que he creado y posterior a esto se ejecuta el reporte de status de la conciliación. El detalle es que las correcciones que ejecutemos en el update no será reflejados en el reporte de conciliación. Investigando un poco, ví que la ejecución del proceso de conciliación ejecuta el reporte desde una tabla que llena en la ejecución llamada SOP_Error_Log_TEMP (SOP50700). Los primeros 7 SEQNUMBR de la tabla representan el encabezado del reporte. Como tengo la información de los lotes que se van a actualizar (script de la parte I de este artículo) puedo llenar la tabla y entonces puedo completar el reporte.

En realidad para llegar a solución tuve que hacer unas cuantas pruebas. La primera prueba que hice fue con Trigger de Foco sobre el botón de Procesar. Esta sería la forma de pensar obvia y lo lógico sería ejecutar el código después de la ejecución de código original. ¿Cuál fue el resultado? El código de conciliación del script de SQL se ejecutaba antes que el código de conciliación de Microsoft. En condiciones en que la cual la base de datos sólo tiene inconsistencias que deben ser corregidas por el script de SQL o sólo por el código de Microsoft no hay problemas, pero si se combinan los dos sí, debido a que el código de Microsoft debe ejecutarse primero. ¿Por qué? Primero debe ser conciliado cada documento, linea de detalle con encabezado, que es la conciliación que realiza el código de Microsoft y luego puede ser ejecutado la conciliación de SQL, de lo contrario terminamos generando más inconsistencias. Es extraño cómo es programado esta opción de conciliación por parte de los programadores de Microsoft, pero me dí cuenta que el programa tiene el evento que inicia la impresión en el Post de la la ventana, por lo que la solución fue colocar un Trigger de Foco en el Post de la ventana antes del código original.

Adicionalmente modifiqué el código del script de SQL para modificar el contenido de la tabla de impresión, de modo de reflejar los cambios realizados por la actualización. Ahora el script de SQL recibe como parámetro de ID del usuario que está ejecutando la conciliación. El nuevo script queda de la siguiente forma:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS(SELECT * FROM [sys].[procedures] WHERE [name] = 'gpspSOPBatchesReconcile') BEGIN
	DROP PROCEDURE [dbo].[gpspSOPBatchesReconcile];
END
GO
-- =============================================
-- Description:	Reconcile SOP Batches
-- =============================================
CREATE PROCEDURE [dbo].[gpspSOPBatchesReconcile]
	@USERID CHAR(15) = ''
AS
BEGIN
	DECLARE @LINE INT
	DECLARE @RANGE CHAR(133) = 'Document Number:                                                                                                                     '

	SET NOCOUNT ON;
	
	SELECT @LINE = COUNT(*) FROM [dbo].[SOP50700] R WHERE R.[USERID] = @USERID
	IF @LINE < 7
	BEGIN
		SET @LINE = 7
	END

	INSERT INTO [dbo].[SOP50700]
	(
		[FIELD12],
		[SEQNUMBR],
		[USERID],
		[Process_ID]
	)
	(
	SELECT
		LEFT(B.[BACHNUMB]+'                              ',30)+'Lote: '+IIF(B.[NUMOFTRX] - T.[REALNUMOFTRX] <> 0,'  Corregido el número de transacciones de '+LTRIM(RTRIM(STR(B.[NUMOFTRX])))+' a '+LTRIM(RTRIM(STR(T.[REALNUMOFTRX]))), ' ')+IIF(B.[BCHTOTAL] - T.[REALBCHTOTAL] <> 0,' Corregido el monto del lote de '+LTRIM(RTRIM(STR(B.[BCHTOTAL])))+' a '+LTRIM(RTRIM(STR(T.[REALBCHTOTAL]))), ' ') 'FIELD12',
		ROW_NUMBER() OVER (ORDER BY B.[BACHNUMB]) + @LINE 'SEQNUMBR',
		@USERID 'USERID',
		0 'Process_ID'
	FROM
		[dbo].[SY00500] B
	JOIN
		(SELECT
			H.[BACHNUMB],
			ISNULL(COUNT(H.[SOPNUMBE]), 0) 'REALNUMOFTRX',
			ISNULL(SUM(H.[DOCAMNT]), 0) 'REALBCHTOTAL'
		FROM
			[dbo].[SOP10100] H
		GROUP BY
			H.[BACHNUMB]) T ON
		T.[BACHNUMB] = B.[BACHNUMB]
	WHERE
		B.[SERIES] = 3 AND
		B.[BCHSOURC] = 'Sales Entry' AND
		(B.[NUMOFTRX] - T.[REALNUMOFTRX] <> 0 OR
		B.[BCHTOTAL] - T.[REALBCHTOTAL] <> 0)
	);

	WITH T AS (
		SELECT
			H.[BACHNUMB],
			ISNULL(COUNT(H.[SOPNUMBE]), 0) 'REALNUMOFTRX',
			ISNULL(SUM(H.[DOCAMNT]), 0) 'REALBCHTOTAL'
		FROM
			[dbo].[SOP10100] H
		GROUP BY
			H.[BACHNUMB]
	)
	UPDATE C
	SET
		C.[NUMOFTRX] = T.[REALNUMOFTRX],
		C.[BCHTOTAL] = T.[REALBCHTOTAL]
	FROM
		[dbo].[SY00500] C
	JOIN T ON T.[BACHNUMB] = C.[BACHNUMB]
	WHERE
		C.[SERIES] = 3 AND
		C.[BCHSOURC] = 'Sales Entry' AND
		(C.[NUMOFTRX] - T.[REALNUMOFTRX] <> 0 OR
		C.[BCHTOTAL] - T.[REALBCHTOTAL] <> 0);

	RETURN @@ROWCOUNT;
END
GO
GRANT EXECUTE ON [dbo].[gpspSOPBatchesReconcile] TO [DYNGRP];
GO

El código de Dexterity Prototype Procedure para el script de SQL gpspSOPBatchesReconcile de debe llamar de la misma forma gpspSOPBatchesReconcile y lo asigné a la serie Sales:


sproc returns long lStatus;
in string UserId;

local long lSleep;

try
	call sproc "gpspSOPBatchesReconcile",lStatus,UserId;
catch [EXCEPTION_CLASS_DB]
	lSleep=Timer_Sleep(200);
	restart try;
catch [EXCEPTION_CLASS_DB_CHG_TRAN_LVL]
	lSleep=Timer_Sleep(200);
	restart try;
catch [EXCEPTION_CLASS_DB_DEADLOCK]
	lSleep=Timer_Sleep(200);
	restart try;
catch [EXCEPTION_CLASS_DB_TRAN_IMP_ROLLBACK]
	lSleep=Timer_Sleep(200);
	restart try;
else
	exit try;
end try;

Para que Microsoft Dynamics GP encuentre los stored procedures en las series sin problemas creo que siguiente script de direccionamiento llamado SQLScriptPath (este nombre no puede ser cambiado) en la serie System:


pragma(disable warning LiteralStringUsed);
pragma(disable warning UnusedVariable);

in integer product_ID;
in integer core_ID;
in integer procedure_ID;
out string data_path;

data_path='Intercompany ID' of globals+"/dbo/";

pragma(enable warning LiteralStringUsed);

Creo que script que ejecuta el trigger llamado SOPBatchesReconcile en la serie de Sales:


local long lStatus;

if 'Include GB' of window SOP_Reconcile of form SOP_Reconcile=0 then
	call gpspSOPBatchesReconcile,
		lStatus,
		'User ID' of globals;
end if;

Y por último, en el script de Startup de la serie de System agrego el siguiente código:


pragma(disable warning LiteralStringUsed);

local integer iStatus;

iStatus=Trigger_RegisterFocus(anonymous(window SOP_Reconcile of form SOP_Reconcile),TRIGGER_FOCUS_POST,TRIGGER_BEFORE_ORIGINAL,script SOPBatchesReconcile);
if iStatus<>SY_NOERR then
	error "Error en registro de trigger.";
end if;

pragma(enable warning LiteralStringUsed);

Con estos cambios ahora podemos probar la aplicación en modo test o creando el chunk. Estas pruebas las he ejecutado en la versión 2016 de Microsoft Dynamics GP, pero debe ser válido para las versiones anteriores. Cualquier duda que tengan pueden contactarme para preguntarme.

Leave a Reply