query saldos por mes proveedores sap B1

[Former Member]

Buenas tardes  estoy  buscando  un  query  que  me  de  los  saldos por  mes  de  proveedores  en sap B1 si  alguien  tiene  uno les  agradezco  la  ayuda

[agustin_marcoscividanes]

Hola

Si lo obtienes con el balance de sumas y saldos por meses y para socios de negocio ¿no obtienes esa información?

Un saludo

Agustín Marcos Cividanes

[former_member227744]

Jeny:

Acá te dejo algo que puede servir.

--------------------------------------------------------------------------------------------------------------------------------

SELECT Distinct T2.CARDCODE, T2.CardName,

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150101' AND '20150131'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150101' AND '20150131'),0)) 'Enero',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150201' AND '20150228'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150201' AND '20150228'),0)) 'Febrero',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150301' AND '20150331'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150301' AND '20150331'),0)) 'Marzo',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150401' AND '20150430'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150401' AND '20150430'),0)) 'Abril',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150501' AND '20150531'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150501' AND '20150531'),0)) 'Mayo',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150601' AND '20150630'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150601' AND '20150630'),0)) 'Junio',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150701' AND '20150731'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150701' AND '20150731'),0)) 'Julio',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150801' AND '20150831'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150801' AND '20150831'),0)) 'Agosto',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20150901' AND '20150930'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20150901' AND '20150930'),0)) 'Septiembre',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20151001' AND '20151031'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20151001' AND '20151031'),0)) 'Octubre',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20151101' AND '20151130'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20151101' AND '20151130'),0)) 'Noviembre',

(ISNULL((SELECT SUM(T.DocTOTAL-T.VatSum-T.TotalExpns) FROM OPCH T WHERE T.CardCode = T2.CardCode AND T.DocDate BETWEEN '20151201' AND '20151231'),0) - ISNULL((SELECT SUM(Y.DocTOTAL-Y.VatSum-Y.TotalExpns) FROM ORPC Y WHERE Y.CardCode = T2.CardCode AND Y.DocDate BETWEEN '20151201' AND '20151231'),0)) 'Diciembre'

FROM OPCH T0 left outer join ORPC T3 on T0.DocDate=T3.DocDate

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

WHERE T0.DocDate BETWEEN '20150101' AND '20151231'

AND T0.CANCELED ='N' and T0.DocEntry NOT IN (Select BaseEntry FROM RPC1 WHERE BaseType != -1) group by T2.CardName,T2.CardCode

----------------------------------------------------------------------------------------------------------------------------------

Saludos,

Manuel Díaz G.