on 05-19-2014 7:56 PM
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
Hola, Cristobal
Espero te funcione este query
SELECT DISTINCT
T1.LicTradNum as 'RUT',
T0.CardName AS 'Nombre',
T0.DocTotal as 'Total Factura',
T0.FolioNum as 'N°Factura',
ISNULL(T5.DocTotal,0) as 'Total NC',
T5.FolioNum as 'N° 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
INNER JOIN PCH1 T4 ON T0.DocEntry = T4.DocEntry
LEFT JOIN ORPC T5 ON T4.TrgetEntry = T5.DocEntry
WHERE DATEDIFF (DD, T0.DocDueDate, GETDATE( )) BETWEEN -60 AND 365
AND T0.DocStatus = 'O'
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
ya entendí, lo que tu quieres es ver que documentos están relacionados a la factura, es como ver contra que se reconcilio una factura? confirmame si entendí bien....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.