Software Lab

Sage 100 – SQL Server 2008 – Procedimiento almacenado para consultar una factura

Sep
30

100% compatible con Sage 100 v15 SQL y Ms SQL Server 2008
80% compatible con Sage 100 v16+ SQL y Ms SQL Server 2008 R2, 2012

 

Si necesitamos consultar o imprimir un documento en el cual estén contemplados los datos de campos virtuales suplementarios de Sage 100, solo tenemos la opción de hacerlo mediante una lectura del driver ODBC.

Empleando el driver ODBC las consultas son muy lentas por lo que se recomienda realizar las consultas con queries SQL.

 

Si además por motivos de optimización de código queremos crear una vista  para la consulta tendremos un obstáculo: las vistas de SQL Server 2008 no admiten parámetros. Por lo tanto la solución que nos queda es crear un procedimiento almacenado y ejecutarlo pasando cada vez los parámetros necesarios, en este caso el número de documento a consultar.

En nuestro ejemplo tenemos una consulta que hace referencia a los campos fundamentales enlazados con todas las tablas que intervienen en una venta. Además el negocio emplea gamas (tallas y colores), código de barras de cruce de gamas y forma de pago múltiple.

Esta es nuestra query:

 

USE BIJOU
GO

DECLARE @DOPIECE varchar(10)
SET @DOPIECE = ‘T2141175’

SELECT
DL.DO_DOMAINE, DL.DO_TYPE, DL.DO_PIECE, DL.DO_DATE, DL.DL_Ligne, DL.DL_No, DL.AR_Ref, DL.DL_MontantHT
, DL.DL_MontantTTC, DL.DL_MvtStock, DL.DL_PrixUnitaire, DL.DL_Qte, DL.DL_Remise01REM_Valeur, DL.DL_PUBC
, DL.DL_PUTTC, DL.DL_Taxe1, DL.DL_TypeTaux1, DL.DL_Valorise

, DT.DO_Tiers
–NO EXISTEN EN SAGE100 SQL: ,FNT_TOTALTTC, FNT_NETAPAYER
, (SELECT SUM(F_DOCLIGNE.DL_MontantTTC)FROM F_DOCLIGNE WHERE F_DOCLIGNE.DO_DOMAINE=0 AND F_DOCLIGNE.DO_Type=6 AND F_DOCLIGNE.DO_Piece=@DOPIECE) AS TOTALCONIVA
, (SELECT SUM(F_DOCREGL.DR_Montant)FROM F_DOCREGL WHERE F_DOCREGL.DO_Type=6 AND F_DOCREGL.DO_Piece=@DOPIECE AND F_DOCREGL.DR_Regle=0) AS NETOAPAGAR

, DR.DR_EQUIL, DR.DR_DATE, DR.DR_MONTANT
–NO EXISTE EN SAGE100 SQL: , DR.INT_REGLEMENT
, PA.R_INTITULE

, CT.CT_Intitule, CT.CT_NUM

, RE.RE_NOM

, FA.FA_Intitule

, AN.AE_CODEBARRE

FROM
F_DOCLIGNE DL

INNER JOIN F_DOCENTETE DT ON
DL.DO_Domaine = DT.DO_DOMAINE
AND DL.DO_Piece = DT.DO_PIECE
AND DL.DO_TYPE = DT.DO_TYPE

INNER JOIN F_DOCREGL DR ON
DR.DO_DOMAINE = DL.DO_DOMAINE
AND DR.DO_PIECE = DL.DO_PIECE
AND DR.DO_TYPE = DL.DO_TYPE

INNER JOIN P_REGLEMENT PA ON
PA.cbIndice = DR.DR_TypeRegl

INNER JOIN F_COMPTET CT ON
CT.CT_NUM = DL.CT_Num

INNER JOIN F_REPRESENT RE ON
RE.RE_No = DL.RE_No

INNER JOIN F_ARTICLE AR ON
AR.AR_Ref = DL.AR_Ref

INNER JOIN F_FAMILLE FA ON
FA.FA_CodeFamille = AR.FA_CodeFamille

INNER JOIN F_ARTENUMREF AN ON
AN.AR_Ref = DL.AR_Ref
AND AN.AG_NO1 = DL.AG_NO1
AND AN.AG_NO2 = DL.AG_NO2

WHERE
DL.DO_Domaine = 0
AND DL.DO_Type = 6
AND DL.DO_Piece = @DOPIECE

ORDER BY
DL.DO_Piece, DL.DL_Ligne;
go

 

Nuestra query nos devolverá en un tiempo récord los registros del documento, 10 veces más rápido que ejecutando la misma consulta en ODBC.

Ahora nos enfrentamos a la integración de la query en un entorno codificado, es decir, en una aplicación o en un gestor documental. No podemos crear una vista cada vez que ejecutamos la consulta pues no resulta operativo ni eficiente al no poder pasarle parámetros, por consiguiente la solución que tenemos es la de utilizar un procedimiento almacenado de usuario.

 

SOLUCIÓN con STORED PROCEDURE para pasarle PARÁMETROS en tiempo de ejecución

La solución contiene 3 pasos:

1 –  Crear un procedimiento almacenado.

2 – Preparar el server SQL 2008 para servir datos desde stored procedures

3 – Ejecutar la consulta con parámetros dinámicos

 

1 Procedimiento almacenado:

USE BIJOU

GO

 

CREATE PROCEDURE AACAJATKT

@DOPIECE varchar(10)

AS
SELECT
DL.DO_DOMAINE, DL.DO_TYPE, DL.DO_PIECE, DL.DO_DATE, DL.DL_Ligne, DL.DL_No, DL.AR_Ref, DL.DL_MontantHT
, DL.DL_MontantTTC, DL.DL_MvtStock, DL.DL_PrixUnitaire, DL.DL_Qte, DL.DL_Remise01REM_Valeur, DL.DL_PUBC
, DL.DL_PUTTC, DL.DL_Taxe1, DL.DL_TypeTaux1, DL.DL_Valorise

, DT.DO_Tiers
, (SELECT SUM(F_DOCLIGNE.DL_MontantTTC)FROM F_DOCLIGNE WHERE F_DOCLIGNE.DO_DOMAINE=0 AND F_DOCLIGNE.DO_Type=6 AND F_DOCLIGNE.DO_Piece=@DOPIECE) AS TOTALCONIVA
, (SELECT SUM(F_DOCREGL.DR_Montant)FROM F_DOCREGL WHERE F_DOCREGL.DO_Type=6 AND F_DOCREGL.DO_Piece=@DOPIECE AND F_DOCREGL.DR_Regle=0) AS NETOAPAGAR

, DR.DR_EQUIL, DR.DR_DATE, DR.DR_MONTANT
, PA.R_INTITULE

, CT.CT_Intitule, CT.CT_NUM

, RE.RE_NOM

, FA.FA_Intitule

, AN.AE_CODEBARRE

FROM
F_DOCLIGNE DL

INNER JOIN F_DOCENTETE DT ON
DL.DO_Domaine = DT.DO_DOMAINE
AND DL.DO_Piece = DT.DO_PIECE
AND DL.DO_TYPE = DT.DO_TYPE

INNER JOIN F_DOCREGL DR ON
DR.DO_DOMAINE = DL.DO_DOMAINE
AND DR.DO_PIECE = DL.DO_PIECE
AND DR.DO_TYPE = DL.DO_TYPE

INNER JOIN P_REGLEMENT PA ON
PA.cbIndice = DR.DR_TypeRegl

INNER JOIN F_COMPTET CT ON
CT.CT_NUM = DL.CT_Num

INNER JOIN F_REPRESENT RE ON
RE.RE_No = DL.RE_No

INNER JOIN F_ARTICLE AR ON
AR.AR_Ref = DL.AR_Ref

INNER JOIN F_FAMILLE FA ON
FA.FA_CodeFamille = AR.FA_CodeFamille

INNER JOIN F_ARTENUMREF AN ON
AN.AR_Ref = DL.AR_Ref
AND AN.AG_NO1 = DL.AG_NO1
AND AN.AG_NO2 = DL.AG_NO2

WHERE
DL.DO_Domaine = 0
AND DL.DO_Type = 6
AND DL.DO_Piece = @DOPIECE

ORDER BY
DL.DO_Piece, DL.DL_Ligne;
go

 

2 Preparar SQL Server 2008 para acceso a datos de stored procedures

USE BIJOU
GO
EXEC sp_addlinkedserver @server = ‘MySqlServerName’, @srvproduct = »,
@provider = ‘SQLOLEDB’, @datasrc = @@servername;

sp_serveroption ‘MySqlServerName’, ‘DATA ACCESS’, TRUE;

GO

 

3 Sintaxis de la consulta del procedimiento almacenado con parámetros

 

USE BIJOU

GO

 

SELECT * FROM OPENQUERY(MySqlServerName,’EXEC BIJOU.dbo.AACAJATKT »T2141184»’);

GO

 

 

Según la aplicación que necesitemos aportar pueden existir variantes de esta solución:

A)  insertar la consulta del procedimiento almacenado en una tabla con SELECT INTO

B) Emplear una tabla temporal para la consulta y consultar todas sus líneas desde la aplicación

C) Exportar los datos de la consulta a un formato XML ó CSV

 

Comentarios y sugerencias, siempre bienvenidas.

Hasta pronto.