Estado de Cuenta Detallado

[Former Member]

Buen dia Comunidad, es mi primer post y mi primer oportunidad con SAP BO, me han solicitado un Estado de Cuenta detallado que incluya la siguiente estructura:

ESTADO DE CUENTA DEL 01/01/2011 AL 12/01/2012

Nombre SSSSS Limite Credito: 100,000 Dias Credito 30

Fecha # Doc Bodega Envio Descripcion Medida Cantidad PrecioU TotalCredito Abonos Saldo

240111 Fac1021 2 1525 MateriaPrima Caja 20 500.00 10,000 10,000

290111 Rec 215 1,500 8,500

040211 Rec 246 3,000 5,500

120211 Rec 287 2,500 3,000

240211 Rec 314 2,000 1,000

De esto tengo ya ubicado todos los datos de las ventas de la siguiente forma:

SELECT T0.[DocDate], T1.[CardCode], T1.[CardName], T2.[PymntGroup], T1.[CreditLine], T3.[SeriesName], T0.[DocNum], T4.[ItemCode], T4.[Dscription], T4.[UseBaseUn], T4.[Quantity], T4.[Price], T4.[GTotal], T0.[DocTotal] FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OCTG T2 ON T1.GroupNum = T2.GroupNum INNER JOIN NNM1 T3 ON T0.Series = T3.Series INNER JOIN INV1 T4 ON T0.DocEntry = T4.DocEntry INNER JOIN OWHS T5 ON T4.WhsCode = T5.WhsCode

Con el tema de los pagos recibidos tengo ubicado lo siguiente

SELECT T0.[DocNum], T0.[DocDate], T0.[CounterRef], T0.[Comments],T0.[DocTotal], T1.[SumApplied], T2.[BaseRef], T2.[RefDate], T2.[Ref1], T2.[LocTotal] FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum INNER JOIN OJDT T2 ON T1.DocTransId = T2.TransId

La consulta es como vincular los pagos con sus respectivo # de factura, poniendo un escenario que para una factura de 10,000 le hayan realizado 4 pagos de diferentes montos y fechas y tendria un saldo de 1,000, pero no encuentro la forma de mostrar los # de factura y que se vayan descontando visualmente hasta llegar al saldo 1,000, ya que en la tabla ORCT no me referencia mi Numero de Factura (Docnum)

Espero haberme explicado bien, no haberme extendido mucho y agradezco toda la ayuda que me puedan brindar para que en breve pueda realizar mis aportes a la comunidad y seguir creciendo

Saludos cordiales,

[former_member243761]

Agradeceria, si alguien me puede por favor compartir el estado de cuenta en Crystal

erleyp(arroba)gmail.com

Gracias,

[Former Member]

Buenos Días

Eric

Por favor me puedes enviar el compilador a mi correo carbex0116 @ hotmail.com

Saludos y Gracias

[Former Member]

Que tal:

Claro te lo mando, saludos.

[Former Member]

Hola te dejo 2 reportes espero sean de ayuda,

Reporte de Saldos

SELECT T0.[CardCode] as 'Codigo',

T0.[CardName] as 'Nombre',

T0.[DocNum] as 'Factura',

'Plazo' + ' ' + cast(T1.instlmntid as varchar) + ' ' + 'de' + ' ' + CAST(t0.Installmnt as varchar) as 'Plazo',

cast(t0.[FolioPref] as varchar) + '-' + cast(T0.[FolioNum] as varchar) as 'Folio',

T0.docdate as 'Fecha Creación',

t1.[DueDate] as 'Fecha Vencimiento',

case when (datediff(day,T1.[DueDate],getdate())< 0) then 0 else datediff(day,T1.[DueDate],getdate()) end as 'Días de Atraso',

case when (datediff(day,T1.[DueDate],getdate())< 0) then datediff(day,T1.[DueDate],getdate())*-1 else 0 end as 'Días Para Pago',

T0.DocTotal as 'Importe Original',

(T1.[InsTotal] - T1.[VatSum]) as 'SubTotal',

t1.[VatSum] as 'IVA',

t1.[InsTotal] as 'Total',

t1.PaidToDate as 'Pagado',

t1.[InsTotal] - t1.paidtodate as 'Por Pagar',

Case t1.[Status] When 'O' Then 'Abierta' End as 'Status Doc'

FROM OPCH T0 inner join PCH6 t1 on t0.docentry = t1.docentry

WHERE T0.DocDate >= '[%0]' and t0.DocDate <= '[%1]' and t1.[Status] = 'O'

order by T1.instlmntid, t1.[VatSum], T0.docdate

Estado de Cuenta

DECLARE @BPCode AS VARCHAR(15)

DECLARE @FechaIn AS DATETIME

DECLARE @FechaFin AS DATETIME

DECLARE @SaldoInicial AS NUMERIC(19,6)

DECLARE @SaldoFinal AS NUMERIC(19,6)

DECLARE @SaldoVencido AS NUMERIC(19,6)

SELECT @BPCode = T0.CardCode, @FechaIn = T1.RefDate, @FechaFin = T2.RefDate FROM OCRD T0 , JDT1 T1, JDT1 T2 WHERE T0.CardCode ='[%0]' AND T1.RefDate ='[%1]' AND T2.RefDate ='[%2]'

SET @BPCode = '[%0]'

SET @FechaIn = CONVERT(DATETIME, '[%1]', 112)

SET @FechaFin = CONVERT(DATETIME, '[%2]', 112)

SELECT @SaldoInicial = ISNULL((SUM(T0.Debit) - SUM(T0.Credit)),0) FROM JDT1 T0 WHERE T0.ShortName = @BPCode AND T0.RefDate <= @FechaIn

SELECT @SaldoFinal = ISNULL((SUM(T0.Debit) - SUM(T0.Credit)),0) FROM JDT1 T0 WHERE T0.ShortName = @BPCode AND T0.RefDate <= @FechaFin

SELECT @SaldoVencido = ISNULL(SUM(ISNULL(T0.DocTotal,0)),0) FROM OINV T0

WHERE T0.CardCode = @BPCode AND NOT T0.DocEntry IN

(SELECT T1.DocEntry FROM RCT2 T1 INNER JOIN ORCT T2 ON T2.DocNum = T1.DocNum WHERE T2.CardCode = @BPCode)

AND T0.DocDueDate < GETDATE()

SELECT IDENTITY(INT,1,1) AS TID, * INTO #PreEdoCta1 FROM (

SELECT TOP 999999999 Tbl1.RefDate, CASE(Tbl1.Transtype)

WHEN(24) THEN

T6.DocNum

ELSE

Num_Factura

END AS Num_Factura, CASE(Tbl1.Transtype)

WHEN(24) THEN

T6.NumAtCard

ELSE

Ref_Factura

END AS Ref_Factura, Tbl1.Ref1 AS Documento_Destino, FolioFiscal, Cargo, Abono,

Vence, FormaPago,

CASE Tbl1.TransType

WHEN 13 THEN 1

WHEN 14 THEN 2

WHEN 24 THEN 5

END AS MV, Notas, Tbl1.TransID, Line_ID FROM

(

SELECT T0.RefDate, T0.TransId, T0.Line_ID,

CASE(T0.Transtype)

WHEN(14) THEN

T4.Num_Factura

WHEN(13) THEN

T0.Ref1

WHEN(24) THEN

NULL

END AS Num_Factura,

CASE(T0.Transtype)

WHEN(14) THEN

T4.NumAtCard

WHEN(13) THEN

T2.NumAtCard

WHEN(24) THEN

NULL

END AS Ref_Factura,

CASE(T0.Transtype)

WHEN(14) THEN

CAST(T3.FolioPref AS VARCHAR(100)) + '-' + CAST(T3.FolioNum AS VARCHAR(100))

WHEN(13) THEN

T2.NumAtCard

END AS FolioFiscal,

CASE(T0.Transtype)

WHEN(14) THEN

Credit * (-1)

ELSE

Debit

END AS Cargo, CASE(T0.Transtype)

WHEN(14) THEN

Debit * (-1)

WHEN(24) THEN

ISNULL(T1.SumApplied, T0.Credit)

ELSE

Credit

END AS Abono, CASE(T0.Transtype)

WHEN(14) THEN

T3.DocDueDate

WHEN(13) THEN

T2.DocDueDate

WHEN(24) THEN

T7.DocDueDate

END AS Vence, T0.Ref1, T0.TransType, T1.DocEntry AS FactDestinoPago,

CASE TransType

WHEN 13 THEN T2.Comments

WHEN 14 THEN T3.Comments

WHEN 24 THEN T7.Comments

WHEN 30 THEN T0.LineMemo

END AS Notas,

CASE WHEN(T7.CashSum > 0) THEN

'EFECTV'

WHEN(T7.CreditSum > 0) THEN

'TRCRED'

WHEN(T7.[CheckSum] > 0) THEN

'CHEQUE'

WHEN(T7.TrsfrSum > 0) THEN

'TRBANC'

END AS FormaPago

FROM JDT1 T0

LEFT JOIN (SELECT T0.TransId, T0.Line_ID, T1.DocNum, ISNULL(SumApplied, T0.Credit) AS SumApplied,

CASE WHEN(InvType = 13) THEN FactOrigen ELSE '' END AS DocEntry, InvType

FROM JDT1 T0 LEFT JOIN (SELECT Tx1.DocNum, Tx0.DocEntry FactOrigen, Tx0.InvType,

(CASE WHEN(Tx0.InvType IN (13,24,30,46)) THEN Tx0.SumApplied

ELSE (Tx0.SumApplied * (-1)) END * CASE WHEN((Tx1.DocTotal + (Tx1.NoDocSum * (-1))) = 0) THEN 1

ELSE(((Tx1.DocTotal*100)/(Tx1.DocTotal + (Tx1.NoDocSum * (-1))))/100) END )

AS SumApplied

FROM RCT2 Tx0 LEFT JOIN ORCT Tx1 ON

Tx0.DocNum = Tx1.DocNum WHERE Tx1.Canceled = 'N')

T1 ON T0.Ref1 = T1.DocNum WHERE T0.SHORTNAME = @BPCode AND T0.TransType = 24

UNION ALL

SELECT Ty3.TransId, Ty3.Line_ID, Ty2.DocNum, Ty1.DocTotal AS SumApplied, 0 AS DocEntry, 24 AS InvType FROM (

SELECT Ty0.DocNum, SUM(CASE WHEN(Ty0.InvType IN (13,24,30,46)) THEN Ty0.SumApplied

ELSE (Ty0.SumApplied * (-1)) END) AS Suma FROM RCT2 Ty0

GROUP BY Ty0.DocNum) Ty2 LEFT JOIN ORCT Ty1 ON Ty2.DocNum = Ty1.DocNum

LEFT JOIN JDT1 Ty3 ON Ty2.DocNum = Ty3.Ref1

WHERE Ty1.CardCode = @BPCode AND Ty2.Suma = 0 AND Ty3.SHORTNAME = @BPCode AND Ty3.TransType = 24) T1 ON T0.TransId = T1.TransId AND T0.Line_Id = T1.Line_Id

LEFT JOIN OINV T2 ON T0.TransId = T2.TransId

LEFT JOIN ORIN T3 ON T0.TransId = T3.TransId

LEFT JOIN (SELECT DISTINCT TD.BaseRef Num_Factura, TH.DocEntry, TF.NumAtCard FROM RIN1 TD RIGHT JOIN ORIN TH

ON TH.DocEntry = TD.DocEntry LEFT JOIN OINV TF ON TD.BaseEntry = TF.DocEntry

WHERE NOT TD.BaseRef IS NULL AND TD.BaseType = 13) T4 ON T3.DocEntry = T4.DocEntry

LEFT JOIN ORCT T7 ON T0.Ref1 = CAST(T7.DocNum AS VARCHAR(30)) AND T0.TransType = 24

WHERE T0.ShortName = @BPCode AND

T0.RefDate >= @FechaIn AND T0.RefDate <= @FechaFin

) Tbl1 LEFT JOIN OINV T6 ON Tbl1.FactDestinoPago = T6.DocEntry

ORDER BY Refdate, FolioFiscal, Num_Factura ASC

) TblEdoCta1

SELECT 0 AS TID, 0 as transid, NULL AS Fecha, NULL AS Num_Factura, 'Cliente: ' + T0.CardCode + ' - ' + T0.CardName + CHAR(10) + CHAR(13) + T0.Address AS Ref_Factura, NULL AS FolioFiscal,

'Saldo Actual: ' AS Documento_Destino, T0.Balance AS Cargo, NULL AS Abono, NULL AS Saldo, NULL AS Vence, NULL AS FormaPago, NULL AS MV,

'RFC: ' + T0.LicTradNum AS Referencia, 'CP: ' + T0.ZipCode AS C9, 'Tel: ' + T0.Phone1 AS C1, 'Contacto: ' + T0.CntctPrsn AS C2,

'Vendedor: ' + T1.SlpName AS C3, 'Límite de Crédito: ' + CAST(T0.CreditLine AS VARCHAR(30)) AS C4, 'Saldo Vencido: ' + CAST(@SaldoVencido AS VARCHAR(30)) + '' AS C5

FROM OCRD T0 LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

WHERE T0.CardType <> N'L' AND T0.CardCode >= '' + @BPCode + '' AND

T0.CardCode <= '' + @BPCode + ''

UNION ALL

SELECT 0 AS TID,0 as transid, NULL AS Fecha, NULL AS Num_Factura, NULL AS Ref_Factura, 'SALDO FINAL' AS FolioFiscal, NULL AS Documento_Destino,

NULL AS Cargo, NULL AS Abono, @SaldoFinal AS Saldo, NULL AS Vence, NULL AS FormaPago, NULL AS MV,

NULL AS Referencia, '' AS C9, '' AS C1, '' AS C2, '' AS C3, '' AS C4, '' AS C5

UNION ALL

SELECT 0 AS TID,0 as transid,NULL AS Fecha, NULL AS Num_Factura, 'Fecha de Análisis Inicial: ' + CAST(@FechaIn AS VARCHAR(100)) + '' AS Ref_Factura,

'Fecha de Análisis Final: ' + CAST(@FechaFin AS VARCHAR(100)) + '' AS FolioFiscal, NULL AS Documento_Destino, NULL AS Cargo, NULL AS Abono,

NULL AS Saldo, NULL AS Vence, NULL AS FormaPago, NULL AS MV, NULL AS Referencia, '' AS C9, '' AS C1, '' AS C2, '' AS C3, '' AS C4, '' AS C5

UNION ALL

SELECT 0 AS TID,0 as transid, NULL AS Fecha, NULL AS Num_Factura, NULL AS Ref_Factura, 'SALDO INICIAL' AS FolioFiscal, NULL AS Documento_Destino,

NULL AS Cargo, @SaldoInicial AS Abono, @SaldoInicial AS Saldo, NULL AS Vence, NULL AS FormaPago, NULL AS MV,

NULL AS Referencia, '' AS C9, '' AS C1, '' AS C2, '' AS C3, '' AS C4, '' AS C5

UNION ALL

(SELECT TID, transid, RefDate AS Fecha, Num_Factura, Ref_Factura, FolioFiscal, Documento_Destino, Cargo, Abono,

0 as 'Saldo' ,

Vence, FormaPago, MV, CASE MV

WHEN 1 THEN 'Fact. ' + CAST(Num_Factura AS VARCHAR(15))

WHEN 2 THEN 'NC. a Factura: ' + CAST(Num_Factura AS VARCHAR(15))

WHEN 5 THEN 'Pago a Factura :' + CAST(Num_Factura AS VARCHAR(15))

ELSE Notas

END AS Referencia, '' AS C9, '' AS C1, '' AS C2, '' AS C3, '' AS C4, '' AS C5 FROM #PreEdoCta1 a)

order by num_factura, mv asc

DROP TABLE #PreEdoCta1

Saludos cordiales,

[Former Member]

Gracias Erik agradezco tu ayuda con esos reportes seran de mucha utilidad, sin embargo solo te quiero molestar con algo adicional a ver si pudieses ayudarme ya que lo que necesito es algo distinto, te envio un correo para ver si puedes ayudarme.

Saludos

[mauricio_rodriguez4]

Excelente Query y de mucha utilidad.

Una sola duda, como manejas los pagos a cuenta?

[former_member188440]

Para empatar el numero de pago que corresponde a una factura, necesitas usar el campo receiptnum (si no me equivoco) de la oinv , pues en este está el numero de pago a que corresponde

tambien puedes basarte en el docnum o docentry de la rct1 , donde viene el numero de factura que estas pagando

[Former Member]

Gracias por ese dato, he estado trabajando con ese detalle pero no logro obtener los datos, puedo enviarte un correo para que puedas ver lo que necesito y orientarme por donde dirigirme.

Saludos

[Former Member]

Hola que tal claro espero tu correo.

Saludos cordiales,