Skip to Content
avatar image
Former Member

Informe Nativo de Ventas de SAP vs Query

Buenos Días Consultores

La gerencia me solicito generar una consulta donde se vean los ingresos por Grupos de Articulos y Barcos( T0.[BPChCode] - Código Canal SN) realize el siguiente query :

SELECT 'FACTURA' AS 'OPER.SAP',T0.[DocDate], T0.[DocNum], T0.[NumAtCard], T0.[CardCode]+'('+T0.[CardName]+')' as 'Cliente', T4.[CardCode]+'('+T4.[CardName]+')' as 'Barco', T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal], T3.[ItmsGrpNam],T0.[DiscPrcnt],T1.[LineTotal]-ISNULL(( T1.[LineTotal]*(T0.[DiscPrcnt])/100),0) AS 'TOTAL'

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.BPChCode = T4.CardCode WHERE T0.[DocDate] >=[%0] and  T0.[DocDate] <=[%1]

UNION ALL

SELECT 'NOTA DE CRÉDITO' AS 'OPER.SAP',T0.[DocDate], T0.[DocNum], T0.[NumAtCard], T0.[CardCode]+'('+T0.[CardName]+')' as 'Cliente', T4.[CardCode]+'('+T4.[CardName]+')' as 'Barco', T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal]*-1, T3.[ItmsGrpNam],T0.[DiscPrcnt],T1.[LineTotal]-ISNULL(( T1.[LineTotal]*(T0.[DiscPrcnt])/100),0)*-1 AS 'TOTAL'

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.BPChCode = T4.CardCode WHERE T0.[DocDate] >=[%0] and  T0.[DocDate] <=[%1]

Interrogantes :

T1.[LineTotal]-ISNULL(( T1.[LineTotal]*(T0.[DiscPrcnt])/100),0) AS 'TOTAL' al momento de recuperar los valores tengo diferencia con el reporte nativo.

Ejemplo

Articulo XXX  Reporte Nativo (8.97) vs Query (8.98)

Cuando es nota de credito utilizo la misma formula T1.[LineTotal]-ISNULL(( T1.[LineTotal]*(T0.[DiscPrcnt])/100),0)*-1 AS 'TOTAL' solo que lo multiplico por -1, pero al recuperar el valor lo genera con valor positivo.

Agradeciendo por sus colaboraciones.

Saludos

Carlos Ortega

Query.jpg (412.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Feb 04, 2013 at 02:38 PM

    Hola.

    1. La diferencia podría ser por la precisión decimal.

    2. Para que quede el valor negativo para las NC intenta así: -(T1.[LineTotal]-ISNULL(( T1.[LineTotal]*(T0.[DiscPrcnt])/100),0))

    Saludos cordiales.

    Add comment
    10|10000 characters needed characters exceeded