Problema con Query

SAP Business One
1

Que tal mi nombre es alejandro y tengo un problema con un query dentro de SAP
me manda el siguiente error
1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘/%0’. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘/%1’. 3). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement ‘’ (SWEI) (s) could not be prepared.

El query es el siguente

DECLARE @FechaInicial AS DATETIME 
DECLARE @FechaFinal AS DATETIME 

SET @FechaInicial = [%0]
SET @FechaFinal = [%1]


select T0.docdate, 'FC' as  'Tipo Doc',t0.docnum,T0.TransId Asiento, t1.AcctCode, 
(select X2.AcctName from oact X2 where t1.AcctCode = X2.AcctCode ),
t1.CogsAcct, 
(select X2.AcctName from oact X2 where t1.cogsacct = X2.AcctCode ), 
t1.itemcode, t1.Dscription, t1.Quantity ,
Case 
	when T0.DocCur in ('USD', 'EUR') 
	then (T1.LineTotal * T0.DocRate)
	else t1.LineTotal end as 'Total MXP', 
t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
from oinv T0
inner join inv1 T1 on T0.docentry = t1.DocEntry
left join OACT T2 on t1.AcctCode = t2.AcctCode


where  T0.[DocDate] >= @FechaInicial and   T0.[DocDate] <= @FechaFinal

y no se como correr el query correctamente dentro de SAP

de ante mano gracias por el apoyo

2

Hola @aramirez revisa si te sirve de esta forma:

Select T0.docdate, 'FC' as 'Tipo Doc', t0.docnum, T0.TransId Asiento, t1.AcctCode,
	   Case When t1.AcctCode = T2.AcctCode Then T2.AcctName End [Cuenta Ventas], T1.CogsAcct, 
	   Case When t1.cogsacct = T2.AcctCode Then T2.AcctName End [Nombre Cuenta Costo],
	   t1.itemcode, t1.Dscription, t1.Quantity ,
	   Case when T0.DocCur in ('USD', 'EUR') then (T1.LineTotal * T0.DocRate) else t1.LineTotal end as 'Total MXP',
	   t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
		From OINV T0 Inner Join 
		 INV1 T1 on T0.DocEntry =  T1.DocEntry Inner Join 
		 OACT T2 on T1.AcctCode = T2.AcctCode
Where T0.DocDate >= [%0] and T0.DocDate <= [%1]

Nos comentas si te fue de ayuda o no para buscar otra solución.

Saludos

1 me gusta
3

Si muchísimas Gracias por le apoyo ya solo tengo que termina todo el query, ese era una parte pero me daba ese error y no podía continuar, le muestro el query de como queda al final

Gracias

4

Hola @aramirez excelente, espero tus noticias siempre :slight_smile:

Saludos

5

te mando el query Terminado, Saludos y ojala les puedas servir

Select T0.docdate, 'FC' as 'Tipo Doc', t0.docnum, T0.TransId Asiento, t1.AcctCode,
	   Case When t1.AcctCode = T2.AcctCode Then T2.AcctName End [Cuenta Ventas], T1.CogsAcct, 
	   --Case When t1.cogsacct = T2.AcctCode Then T2.AcctName End [Nombre Cuenta Costo],
	   t1.itemcode Codigo, t1.Dscription, t1.Quantity ,
	   Case when T0.DocCur in ('USD', 'EUR') then (T1.LineTotal * T0.DocRate) else t1.LineTotal end as 'Total MXP',
	   t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
		From OINV T0 Inner Join 
		 INV1 T1 on T0.DocEntry =  T1.DocEntry Inner Join 
		 OACT T2 on T1.AcctCode = T2.AcctCode
Where T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.CANCELED ='N'

union all

Select T0.docdate, 'NCC' as 'Tipo Doc', t0.docnum, T0.TransId Asiento, t1.AcctCode,
	   Case When t1.AcctCode = T2.AcctCode Then T2.AcctName End [Cuenta Ventas], T1.CogsAcct, 
	   --Case When t1.cogsacct = T2.AcctCode Then T2.AcctName End [Nombre Cuenta Costo],
	   t1.itemcode Codigo, t1.Dscription, t1.Quantity ,
	   Case when T0.DocCur in ('USD', 'EUR') then ((T1.LineTotal * T0.DocRate)*-1) else t1.LineTotal end as 'Total MXP',
	   t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
		From ORIN T0 Inner Join 
		 RIN1 T1 on T0.DocEntry =  T1.DocEntry Inner Join 
		 OACT T2 on T1.AcctCode = T2.AcctCode
Where T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.CANCELED ='N'

union all

Select T0.docdate, 'NCC' as 'Tipo Doc', t0.docnum, T0.TransId Asiento, t1.AcctCode,
	   Case When t1.AcctCode = T2.AcctCode Then T2.AcctName End [Cuenta Ventas], T1.CogsAcct, 
	   --Case When t1.cogsacct = T2.AcctCode Then T2.AcctName End [Nombre Cuenta Costo],
	   t1.itemcode Codigo, t1.Dscription, t1.Quantity ,
	   Case when T0.DocCur in ('USD', 'EUR') then ((T1.LineTotal * T0.DocRate)*-1) else t1.LineTotal end as 'Total MXP',
	   t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
		From ORIN T0 Inner Join 
		 RIN1 T1 on T0.DocEntry =  T1.DocEntry Inner Join 
		 OACT T2 on T1.AcctCode = T2.AcctCode
Where T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.CANCELED ='N'

Union all

Select T0.docdate, 'EM' as 'Tipo Doc', t0.docnum, T0.TransId Asiento, t1.AcctCode,
	   Case When t1.AcctCode = T2.AcctCode Then T2.AcctName End [Cuenta Ventas], T1.CogsAcct, 
	   --Case When t1.cogsacct = T2.AcctCode Then T2.AcctName End [Nombre Cuenta Costo],
	   t1.itemcode Codigo, t1.Dscription, t1.Quantity ,
	   Case when T0.DocCur in ('USD', 'EUR') then ((T1.LineTotal * T0.DocRate)*-1) else t1.LineTotal end as 'Total MXP',
	   t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
		From OPDN T0 Inner Join 
		 PDN1 T1 on T0.DocEntry =  T1.DocEntry Inner Join 
		 OACT T2 on T1.AcctCode = T2.AcctCode
Where T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.CANCELED ='N'

union all

Select T0.docdate, 'FP' as 'Tipo Doc', t0.docnum, T0.TransId Asiento, t1.AcctCode,
	   Case When t1.AcctCode = T2.AcctCode Then T2.AcctName End [Cuenta Ventas], T1.CogsAcct, 
	   --Case When t1.cogsacct = T2.AcctCode Then T2.AcctName End [Nombre Cuenta Costo],
	   t1.itemcode Codigo, t1.Dscription, t1.Quantity ,
	   Case when T0.DocCur in ('USD', 'EUR') then ((T1.LineTotal * T0.DocRate)*-1) else t1.LineTotal end as 'Total MXP',
	   t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
		From OPCH T0 Inner Join 
		 PCH1 T1 on T0.DocEntry =  T1.DocEntry Inner Join 
		 OACT T2 on T1.AcctCode = T2.AcctCode
Where T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.CANCELED ='N'

union all

Select T0.docdate, 'NCP' as 'Tipo Doc', t0.docnum, T0.TransId Asiento, t1.AcctCode,
	   Case When t1.AcctCode = T2.AcctCode Then T2.AcctName End [Cuenta Ventas], T1.CogsAcct, 
	   --Case When t1.cogsacct = T2.AcctCode Then T2.AcctName End [Nombre Cuenta Costo],
	   t1.itemcode Codigo, t1.Dscription, t1.Quantity ,
	   Case when T0.DocCur in ('USD', 'EUR') then ((T1.LineTotal * T0.DocRate)*-1) else t1.LineTotal end as 'Total MXP',
	   t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
		From ORPC T0 Inner Join 
		 RPC1 T1 on T0.DocEntry =  T1.DocEntry Inner Join 
		 OACT T2 on T1.AcctCode = T2.AcctCode
Where T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.CANCELED ='N'

union all

Select T0.docdate, 'SM' as 'Tipo Doc', t0.docnum, T0.TransId Asiento, t1.AcctCode,
	   Case When t1.AcctCode = T2.AcctCode Then T2.AcctName End [Cuenta Ventas], T1.CogsAcct, 
	   --Case When t1.cogsacct = T2.AcctCode Then T2.AcctName End [Nombre Cuenta Costo],
	   t1.itemcode Codigo, t1.Dscription, t1.Quantity ,
	   Case when T0.DocCur in ('USD', 'EUR') then ((T1.LineTotal * T0.DocRate)*-1) else t1.LineTotal end as 'Total MXP',
	   t1.OcrCode Finca, t1.OcrCode2 'U. de Negocio', t1.OcrCode3 Fase
		From OIGE T0 Inner Join 
		 IGE1 T1 on T0.DocEntry =  T1.DocEntry Inner Join 
		 OACT T2 on T1.AcctCode = T2.AcctCode
Where T0.DocDate >= [%0] and T0.DocDate <= [%1] and T0.CANCELED ='N'


order by t0.docdate
6

Hola que tal , podrías indicar cual es la funcionalidad del Query visualizo que es de ventas por las tablas

7

Hola @aramirez excelente que lograste terminar tu reporte, te recomendó marcar una solución así ya pueden cerrar el hilo.

Saludos

8

Este reporte te arroja todos los movimientos que tengan que ver con Artículos ligado con los asientos y las cuentas a las que están haciendo referencia, muestra los números de documentos, los tipos de documentos, el numero de asiento y el numero de transacción, dando el total en piezas y el total en cantidad en moneda local, ligado a la contabilidad de centros de costos que a los que va cada movimiento

Espero les pueda servir

saludos

9

Este tema se cerró automáticamente 7 días después del último post. No se permiten nuevas respuestas.