Fecha en que se Pago una Factura

[Former Member]

Buenas Noches Colegas

Espero me puedan ayudar . Deseo generar un query que muestre la fecha en que fue pagada una factura.
Saludos

[former_member203638 · Accepted solution]

Hola Ruben,utiliza esta, y la adecuas a lo que vallas a usar. (Para factura de Cliente)

Si fuera de Proveedor tienes que usar tabla de OPCH y OVPM para Pagos Efectuados.

SELECT DISTINCT
T3.Slpname AS 'Vendedor',
T1.DocNum AS 'Número Pago',
T0.DocNum AS 'Número SAP',
cast(T0.FolioPref as varchar)+cast(T0.FolioNum as varchar) as 'No.Factura',
T0.CardName AS 'Nombre de deudor/acreedor', 
T1.DocDate AS 'Fecha Pago',
T0.TaxDate AS 'F.Emision Factura', 
T0.DocDueDate AS 'F.Vcto Factura.', 
DATEDIFF(day, DATEADD(day, 60,T0.TaxDate), T1.DocDate) AS Numdias,
T4.SumApplied AS 'Monto Pagado',
T0.DocTotal AS 'Total factura', 
(T0.DocTotal-T0.VatSum) AS 'Neto Factura'
FROM  
[dbo].[OINV] T0 
INNER JOIN [dbo].[RCT2] T4 ON T4.DocEntry = T0.DocEntry
INNER JOIN [dbo].[ORCT] T1 ON T4.DocNum = T1.DocNum 
INNER JOIN [dbo].[OSLP] T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN [dbo].[INV1] T7 ON T0.DocEntry = T7.DocENtry
WHERE ( T1.DocDate >='[%0]' and T1.DocDate <='[%1]' )

Saludos.

Alessandro.

[Former Member · Accepted solution]

Saludos Alessandro

me sirvió mucho tu consulta. oye crees me puedas ayudar con esta misma consulta pero los documentos que son en usd y peso

saludos y gracias de antemano

[former_member227744 · Accepted solution]

Alessandro:

Muy buena consulta.

Puedes incorporar a esta consulta los pagos a cuenta? (los no asociados a código SN).

Saludos,

Manuel

[former_member203638 · Accepted solution]

Hola Cesar, Intensa así:

SELECT DISTINCT
T3.Slpname AS 'Vendedor',  
T1.DocNum AS 'Número Pago',
T0.DocNum AS 'Número SAP',
cast(T0.FolioPref as varchar)+cast(T0.FolioNum as varchar) as 'No.Factura',
T0.CardName AS 'Nombre de deudor/acreedor',
T1.DocDate AS 'Fecha Pago',
T0.TaxDate AS 'F.Emision Factura',
T0.DocDueDate AS 'F.Vcto Factura',
DATEDIFF(day, DATEADD(day, 60,T0.TaxDate), T1.DocDate) AS Numdias,
Case When T0.DocCur='USD' Then T4.[AppliedFC] Else T4.SumApplied End AS 'Monto Pagado',
CASE When T0.DocCur='USD' Then T0.DocTotalFC Else T0.DocTotal End AS 'Total factura',
Case When T0.DocCur='USD' Then (T0.DocTotal-T0.VatSumFC) Else (T0.DocTotal-T0.VatSum) End AS 'Neto Factura',
T0.DocCur as 'Moneda'
FROM
[dbo].[OINV] T0
INNER JOIN [dbo].[RCT2] T4 ON T4.DocEntry = T0.DocEntry
INNER JOIN [dbo].[ORCT] T1 ON T4.DocNum = T1.DocNum
INNER JOIN [dbo].[OSLP] T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN [dbo].[INV1] T7 ON T0.DocEntry = T7.DocENtry
WHERE ( T1.DocDate >='[%0]' and T1.DocDate <='[%1]' )

Saludos.

[Former Member · Accepted solution]

Excelente!!

muchas gracias Alessandro si me sirvió.

[former_member203638 · Accepted solution]

Hola Manuel, intenta lo siguiente:

SELECT DISTINCT
T3.Slpname AS 'Vendedor',  
T1.DocNum AS 'Número Pago',
T0.DocNum AS 'Número SAP',
cast(T0.FolioPref as varchar)+cast(T0.FolioNum as varchar) as 'No.Factura',
T0.CardName AS 'Nombre de deudor/acreedor',
T1.DocDate AS 'Fecha Pago',
T0.TaxDate AS 'F.Emision Factura',
T0.DocDueDate AS 'F.Vcto Factura',
DATEDIFF(day, DATEADD(day, 60,T0.TaxDate), T1.DocDate) AS Numdias,
Case When T0.DocCur='USD' Then T4.[AppliedFC] Else T4.SumApplied End AS 'Monto Pagado',
CASE When T0.DocCur='USD' Then T0.DocTotalFC Else T0.DocTotal End AS 'Total factura',
Case When T0.DocCur='USD' Then (T0.DocTotal-T0.VatSumFC) Else (T0.DocTotal-T0.VatSum) End AS 'Neto Factura',
CASE wHEN (T1.PayNoDoc='y' and T0.DocCur='USD') Then T1.NoDocSumFC Else T1.NoDocSum end As 'Pago a Cuenta',
T0.DocCur as 'Moneda'
FROM
[dbo].[OINV] T0
INNER JOIN [dbo].[RCT2] T4 ON T4.DocEntry = T0.DocEntry
FULL JOIN [dbo].[ORCT] T1 ON T4.DocNum = T1.DocNum
LEFT JOIN [dbo].[OSLP] T3 ON T0.SlpCode = T3.SlpCode
LEFT JOIN [dbo].[INV1] T7 ON T0.DocEntry = T7.DocENtry
WHERE ( T1.DocDate >='[%0]' and T1.DocDate <='[%1]' )

Saludos.

[former_member227744 · Accepted solution]

Alessandro:

Genial, Muchas gracias !!!!

Saludos,

Manuel

[Former Member]

Excelente,  Muchas Gracias Alessandro.

Saludos