on 02-19-2013 11:53 PM
Buenos dias a todos.
Me pidieron hacer los reportes de ventas y compras para una fecha determinada porque se ha detectado que en esa fecha el tipo de cambio estaba errado, he encontrado en el foro un query que me sirve aunque no me muestra toda la informacion que me piden, el query es (se agradece grandemente a quien apoyo para realizarlo):
SELECT T1.SeriesName 'Serie',
CASE T2.InvType
WHEN 13 THEN 'Factura'
WHEN 14 THEN 'N.Credito'
END 'Docto',
CASE T2.InvType
WHEN 13 THEN (SELECT A.DocNum FROM OINV A WHERE A.DocEntry=T2.DocEntry)
WHEN 14 THEN (SELECT A.DocNum FROM ORIN A WHERE A.DocEntry=T2.DocEntry)
END '#SAP',
CASE T2.InvType
WHEN 13 THEN (SELECT A.FolioNum FROM OINV A WHERE A.DocEntry=T2.DocEntry)
WHEN 14 THEN (SELECT A.FolioNum FROM ORIN A WHERE A.DocEntry=T2.DocEntry)
END'Folio',
T0.DocNum 'No. Pago',
T0.CardCode 'Código Cliente',
T0.CardName 'Nombre Cliente',
T0.CashSum 'Efectivo',
T0.CreditSum 'T. Credito',
T0.CheckSum 'Cheque',
T0.TrsfrSum 'Transf',
T0.DocTotal 'Total',
T0.DocDate 'Fecha'
FROM ORCT T0
INNER JOIN NNM1 T1 ON T0.Series = T1.Series
INNER JOIN RCT2 T2 ON T0.DocNum = T2.DocNum
WHERE T0.Canceled = 'N'
ORDER BY T0.DocNum
FOR BROWSE
Me piden el numero de la factura, el monto aplicado(pagado), si tiene nota de credito, el numero del asiento que se creo(por la factura y la nota de credito), el numero del pago recibido y el tipo de pago.
El query devuelve varios datos de los que necesito mas no por ejemplo las notas de credito, aunque hace referencia a ellas, luegome faltarian los asientos creados.
Les pido su ayuda por favor.
Gracias por el apoyo.
Saludos
Angel Uribe
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SELECT Distinct T1.DocDate 'F.Pago',T1.DocNum 'No.Pago',T9.SeriesName 'Giro',
Factura = Case
When T3.InvType = '13' Then T4.FolioNum
When T3.InvType = '14' Then T11.FolioNum
When T3.InvType = '203' Then T10.FolioNum
When T3.InvType = '30' Then T12.TransId
End,
'Fecha Fact' = Case
When T3.InvType = '13' Then T4.DocDate
When T3.InvType = '14' Then T11.DocDate
When T3.InvType = '203' Then T10.DocDate
When T3.InvType = '30' Then T12.RefDate
End,
T1.CardCode as "Codigo", T1.CardName as "Cliente", T2.SlpName,
Pago=case
When T1.CashSum > '0' Then 'Efectivo'
When T1.CheckSum > '0' Then ('Cheque:')+cast(T6.CheckNum as varchar) + (', Banco:' + T8.BankName)
When T1.CreditSum > '0' Then ('TC,' + T7.CrTypeName)
When T1.TrsfrSum > '0' Then 'TransFerencia'
End,
RefP=case
When T1.CashSum > '0' Then 'Efectivo'
When T1.CheckSum > '0' Then 'Cheque'
When T1.CreditSum > '0' Then 'TC'
When T1.TrsfrSum > '0' Then 'TransFerencia'
End,
Total = Case
When T3.InvType = '13' Then T3.SumApplied
When T3.InvType = '14' Then (T3.SumApplied) * -1
When T3.InvType = '203' Then T3.SumApplied
When T3.InvType = '30' Then T3.SumApplied
End,
T1.CashSum,T1.CheckSum,T1.CreditSum,T1.TrsfrSum,T1.Canceled,T1.DocEntry,T3.InvType,T1.transID
FROM dbo.OCRD T0
INNER JOIN dbo.ORCT T1 ON T0.CardCode = T1.CardCode
INNER JOIN dbo.RCT2 T3 ON T3.DocNum = T1.DocNum
INNER JOIN dbo.OSLP T2 ON T0.SlpCode = T2.SlpCode
left JOIN dbo.OINV T4 ON T4.DocEntry = T3.DocENtry
LEFT JOIN dbo.RCT3 T5 ON T1.DocEntry = T5.DOcNum
LEFT JOIN dbo.RCT1 T6 ON T1.DocEntry = T6.DocNum
LEFT JOIN dbo.OCRP T7 ON T5.CrTypeCode = T7.CrTypeCode
LEFT JOIN dbo.ODSC T8 ON T6.BankCode = T8.BankCode
INNER JOIN dbo.NNM1 T9 ON T1.Series = T9.Series
LEFT JOIN dbo.ODPI T10 ON T3.DocEntry = T10.DocEntry
LEFT JOIN dbo.ORIN T11 ON T3.DocENtry = T11.DocEntry
Inner Join OJDT T12 ON T3.DocTransId = T12.TransId
WHERE T1.Canceled = 'N' and T1.DocDate='2012/12/20' Order By T1.DocNum
Saludos.
Alessandro.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
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.