Pago Proveedores

[Former Member]

Estimados Expertos,

he realizado un query  para obtener un informe de  facturas de proveedores a pago y las notas de crédito asociadas, sin embargo solo puedo obtener una nota de crédito por factura y a veces tengo mas asociadas a una factura. La consecuencia de esto es el  campo PaidToDate pero si lo saco me arroja notas de credito saldadas anteriormente.

Favor su ayuda...  dejo el query utilizado actualmente. gracias

SELECT 

T1.LicTradNum as 'RUT',

T0.CardName AS 'Nombre',

T0.DocTotal*-1 as 'Total',

t0.FolioNum as 'N°Factura o NC',

T0.DocDueDate AS 'Fecha de vencimiento',

T0.DocNum AS 'REF'

FROM OPCH T0

inner join OCRD T1 on T1.cardCode = T0.Cardcode

inner join OACT T3 on T1.DebPayAcct = T3.AcctCode

WHERE DATEDIFF (DD, T0.DocDueDate, GETDATE( )) BETWEEN -60 AND 365

AND T0.DocStatus = 'O'

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

union

SELECT 

T1.LicTradNum as 'RUT',

T4.CardName AS 'Nombre',

T0.DocTotal as 'Total',

T0.FolioNum ,

T4.DocDueDate ,

T0.DocNum

FROM ORPC T0 -- tabla de notas de credito

inner join OCRD T1 on T1.cardCode = T0.Cardcode

inner join OACT T3 on T1.DebPayAcct = T3.AcctCode

inner join OPCH T4 on

t4.PaidToDate = t0.PaidToDate  and t4.CardCode = t0.CardCode

WHERE t4.CEECFlag = 'Y'

AND T4.DocStatus = 'O'

ORDER BY T1.LicTradNum

[rjovel · Accepted solution]

intenta con este

select
								T1.cardcode 'Codigo',
								T1.cardname 'Nombre',
								case
								when  T0.transtype=13 then
								isnull((select TOP 1  numatcard from oinv with(nolock) where docnum=t0.Ref1 ),'')
								when  T0.transtype=14 then
								isnull((select TOP 1 numatcard from orin with(nolock) where docnum=t0.Ref1 ),'')
								else t0.Ref1 end as
								'Documento / OC',
								t0.Ref1 as 'Referencia',
								CONVERT(VARCHAR(10), t0.RefDate, 103) 'Fecha Docto' ,
								CONVERT(VARCHAR(10), t0.duedate, 103) 'Vence Docto'
								,Monto=T0.Debit
								,Saldo=T0.BALDUEDEB- T0.BalDueCred
		,isnull(CASE

                                       when (DATEDIFF(dd,t0.RefDate,current_timestamp))+1 <= 0

                                       then

                                       case

                                       when credit <> 0 then isnull(-credit,0)

                                       else  isnull(BALDUEDEB- T0.BalDueCred,0)

                                       end

                                       end,0) "No Vencido",

ISNULL( case when ((datediff(dd,t0.RefDate,current_timestamp))+1 >= 1

                                       and (datediff(dd,t0.RefDate,current_timestamp))+1< 30)

                                       then

                                       case

                                       when credit <> 0 then isnull(-credit,0)

                                       else isnull(BALDUEDEB- T0.BalDueCred,0)

                                       end

                                       end,0)"30 Dias",

                      ISNULL( case when ((datediff(dd,t0.RefDate,current_timestamp))+1 >= 31

                                       and (datediff(dd,t0.RefDate,current_timestamp))+1< 61)

                                       then

                                       case

                                       when credit <> 0 then isnull(-credit,0)

                                       else isnull(BALDUEDEB- T0.BalDueCred,0)

                                       end

                                       end,0)"31 to 60 Dias",

                       isnull(case when ((datediff(dd,t0.RefDate,current_timestamp))+1 >= 61

                                       and (datediff(dd,t0.RefDate,current_timestamp))+1< 91)

                                       then

                                       case

                                       when credit <> 0 then isnull(-credit,0)

                                       else isnull(BALDUEDEB- T0.BalDueCred,0)

                                       end

                                       end,0) "61 to 90 Dias",

                       isnull(case when ((datediff(dd,t0.RefDate,current_timestamp))+1 >= 91

                                       and (datediff(dd,t0.RefDate,current_timestamp))+1< 121)

                                       then

                                       case

                                       when credit <> 0 then isnull(-credit,0)

                                       else isnull(BALDUEDEB- T0.BalDueCred,0)

                                       end

                                       end,0) "91 to 120 Dias",                                      

                       isnull(CASE

                                       when (DATEDIFF(dd,t0.RefDate,current_timestamp))+1 >= 121

                                       then

                                       case

                                       when credit= 0 then isnull(BALDUEDEB- T0.BalDueCred,0)

                                       when debit= 0 then isnull(BALDUEDEB- T0.BalDueCred,0)

                                       end

                                       end,0) "121+ Dias"

                                     ,T0.LineMemo as 'Observaciones'

									,t1.SlpCode
									,T2.SlpName
				from   dbo.JDT1 T0 with(nolock)
					INNER JOIN
					dbo.OCRD T1  with(nolock)
					ON T0.shortname = T1.cardcode
					inner join
					OSLP T2 WITH(NOLOCK)
					ON T1.SlpCode=T2.SlpCode
where					T0.BALDUEDEB != T0.BALDUECRED
						and T1.cardname='[%0]'

order by t0.ShortName,T0.TransId

[Former Member · Accepted solution]

Gracias !!! Esta bueno, es Otra forma de Verlo.   Sin  embargo siguen sin aparecer las notas de credito asociadas

[rjovel · Accepted solution]

Cristobal, si no aparecen es porque ya las reconciliaron con alguna factura o pago efectuado, no debería tomarlas en cuenta nuevamente.

no se si me explico? la nota de crédito solo debe usarla una vez.

[Former Member · Accepted solution]

mm no sé!?   agradezco tu pronta respuesta, pero necesito que en el informe me aparezcan las notas de crédito asociadas a la factura a pagar.

[rjovel · Accepted solution]

quitele esta linea

T0.BALDUEDEB != T0.BALDUECRED

[Former Member · Accepted solution]

Estimado Roger, hice lo que me dijiste y me muestra todas las notas de crédito d emitidas por ese proveedor, incluso las facturas ya pagadas...

[Former Member · Accepted solution]

la idea es que me muestre solo las notas de crédito de las facturas pendientes de pago

[rjovel · Accepted solution]

ok el punto es que si no aparecen es porque ya la usaron para conciliar algún pago pendiente que tenían con el proveedor, si no la han usado aparecerá.

[Former Member · Accepted solution]

revisando el mapa de relaciones no aparecen pagos asociados.

[rjovel · Accepted solution]

no revises el mapa de relaciones de la nota de crédito, contablemente es desde el pago efectuado, revisa uno por uno hasta donde encuentres uno que tenga seleccionada factura  y nota de crédito, entonces me entenderás.