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.

 

SageCRM v7.1 – Consulta de seguimiento de oportunidades, con datos de zona, vendedor y empresa.

Dic
02

Llegado el momento de explotar la información creada en nuestro CRM se ponene a nuestra disposición varias alternativas. Una de ellas es crear una vista específica en SageCRM empleando el gestor de informes estándar y otras de ellas es Crystal Reports, posiblemente el mejor gestor de reporting de los últimos 15 años.

En el siguiente ejemplo mostramos una consulta de seguimiento de oportunidades mediante la cual tendremos la posibilidad de agrupar oportunidades por zona y vendedor, incluyendo información complementaria de la ficha de la empresa.

Código de la query SQL con todos sus campos:

SELECT c.*, o.*,op.*
FROM Opportunity o,OpportunityProgress op, Company c
where o.Oppo_OpportunityId = op.Oppo_OpportunityId
and C.comp_CompanyId = o.Oppo_PrimaryCompanyId

En esta consulta hemos mostrado todos los campos de las tablas relacionadas.

Código de la query SQL con una selección de campos, filtrada y clasificada:

SELECT c.comp_name, c.COMP_territory , c.comp_referencia, c.comp_type, c.comp_status, c.comp_source, t.terr_caption
, o.Oppo_Description, o.Oppo_Note, o.Oppo_CustomerRef, o.Oppo_Opened, o.Oppo_Closed, o.Oppo_Status, o.Oppo_Certainty, o.Oppo_Priority
, u.user_lastname, u.User_FirstName, op.Oppo_Status, op.Oppo_Stage, op.Oppo_Forecast, op.oppo_certainty, op.Oppo_ProgressNote, op.Oppo_Description, op.oppo_forecast
FROM Opportunity o,OpportunityProgress op, Company c, Territories t, Users u
where o.Oppo_OpportunityId = op.Oppo_OpportunityId
and C.comp_CompanyId = o.Oppo_PrimaryCompanyId
and t.Terr_TerritoryID = c.Comp_secterr
and u.User_UserId = op.Oppo_AssignedUserId
order by c.comp_name,o.Oppo_Description,o.Oppo_Opened

Análisis de la consulta:

El resultado es un informe de seguimiento de oportunidades en curso, perdidas o ganadas, con sus valores de previsión de venta y nota de seguimineto comercial, agrupadas por zona primero y después por vendedor y ordenada cronológicamente por fecha de apertura.

Recordemos que el seguimiento de las oportunidades nos muestra información de la evolución de las etapas que se han realizado a través del gestor de flujos de procesos.

Para más información y sugerencias, esperamos tus comentarios.