on 11-11-2015 3:41 AM
Buenas noches
Escribo para pedirle si me pueden orientar. Deseo hacer un query que me permita sacar el saldo de los socios de negocio y los clasifique en grupo de acuerdo al tiempo de vencimiento del documento. Mi problema radica en que no logro hacer la conexión correcta entre OINV, pagos y ORIN para que el saldo sea
Saldo SC = OINV.DocTotal-.PaidToDate+ORIN.DocTotal > 0 or OINV.DocTotalFC-.PaidFC+ORIN.DocTotal > 0)
Este es el FROM que estoy usando tomando de referencia el ticket , sin embargo mi query no me trae ningun resultado
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE
INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM
INNER JOIN OSLP T3 on T0.SlpCode = T3.SlpCode
INNER JOIN INV1 T4 ON T0.DocEntry = T4.DocEntry
LEFT OUTER JOIN RIN1 T5 ON T4.DocEntry = T5.BaseEntry AND T4.LineNum = T5.BaseLine
INNER JOIN ORIN T6 ON T4.DocEntry = T5.DocEntry
WHERE (T0.DocTotal-T0.PaidToDate+t6.DocTotal > 0 or T0.DocTotalFC-T0.PaidFC+T6.DocTotal > 0)
Mucho les agradeceré sus comentarios.
Buenas tardes
Favor revise estos post y vea cual query se ajusta a su requerimiento
Antigüedad de saldos de slientes | SCN
Antiguedad de Saldos en Query Manager | SCN
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Felipe
Buenos dias
Muchas gracias por las referencias. Estoy leyendo ambos tópicos y veo que los pagos se manejan usando tablas de diario. Hare más pruebas el lunes. Y veré si ya logro que sea por fin que para todos mis socios facturas mas pagado a la fecha menos nota de credito es decir, ver solo facturas no pagadas y no canceladas.
Hola Karla.
A primera instancia puedo mencionarte que el último join es incorrecto, debería de ser INNER JOIN ORIN T6 ON T5.DocEntry = T6.DocEntry y tu lo tienes T4.DocEntry = T5.DocEntry.
Una duda... ¿No te sirve la antigüedad de saldos de clientes?
Suerte.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Gerardo
Muchas gracias por la orientación. Ya he aplicado tu consejo a mi FROM y el query ya corre, aunque al validar los resultados con mi usuario el resultado no es el esperado ya que antes de aplicar el nuevo form obtenia facturas correctas e incorrectas y ahora ninguna pertenece al grupo buscado.Sigo investigando si mi error ahora esta en si incluye los pagos aplicados o en la operacion matemática de saldos
SELECT
T0.CardCode AS 'Master',
T0.CardName AS 'Empresa',
T3.SlpName'Vendedor',
T2.PYMNTGROUP 'DiasCred',
'TotalDoc'= (case when T0.DocCur='USD' then (T0.DocTotalFC) else (T0.DocTotal) end) , T0.DocNum AS 'fOLIO' ,
T0.DOCDATE as 'CreaDO',
GETDATE() as FechaRepo,
T0.NUMATCARD as Ref2,
T0.DOCDUEDATE as 'Fech/VenceCRED',
DATEDIFF(day, t0.docdate, getdate()) as Días,
'Saldo Pendiente'= (case when T0.DocCur='USD' then (T0.DocTotalFC-T0.PaidFC+T6.DocTotalFC) else (T0.DocTotal-T0.PaidToDate+T6.DocTotal) end) ,
T0.DocCur as Moneda ,
'Corriente' = (case when (T0.Doccur='MXP' and getdate() <= t0.DocDate) then (T0.DocTotal-T0.PaidToDate+T6.DocTotal) else (case when getdate() <= t0.DocDate and T0.Doccur='USD' then (T0.DocTotalFC-T0.PaidFC+T6.DocTotalFC) else 0 end) end) ,
' 0-60 OB/RENOV' = (case when (T0.Doccur='MXP' and DATEDIFF(day, t0.docdate, getdate())BETWEEN 0 and 60) then (T0.DocTotal-T0.PaidToDate + T6.DocTotal) else (case when DATEDIFF(day, t0.docdate, getdate())BETWEEN 0 AND 60 and T0.Doccur='USD' then (T0.DocTotalFC-T0.PaidFC+T6.DocTotalFC) else 0 end) end) ,
' 61-105 OB/RENOV' = (case when (T0.DocCur='MXP' and DATEDIFF(day, t0.docdate, getdate())>=61 and DATEDIFF(day, t0.docdate, getdate())<=105) then (T0.DocTotal-T0.PaidToDate+T6.DocTotal) else (case when DATEDIFF(day, t0.docdate, getdate())>=61 and DATEDIFF(day, t0.docdate , getdate())<=105 and T0.DocCur='USD' then (T0.DocTotalFC-T0.PaidFC+T6.DocTotalFC)else 0 end) end) ,
'106-150 CARTERA ' = (case when (T0.DocCur='MXP' and DATEDIFF(day, t0.docdate, getdate())>=106 and DATEDIFF(day, t0.docdate, getdate())<=150) then (T0.DocTotal-T0.PaidToDate+T6.DocTotal) else (case when DATEDIFF(day, t0.docdate, getdate())>=106 and DATEDIFF(day, t0.docdate, getdate())<=150 and t0.DocCur='USD' then (T0.DocTotalFC-T0.PaidFC+T6.DocTotalFC) else 0 end) end),
'151+ COBRANZA' = (case when (T0.DocCur='MXP' and DATEDIFF(day, t0.docdate, getdate())>=151) then (T0.DocTotal-T0.PaidToDate+T6.DocTotal) else (case when (T0.DocCur='USD' and DATEDIFF(day, t0.docdate, getdate())>=151) then (T0.DocTotalFC-T0.PaidFC+T6.DocTotalFC) else 0 end) end),
T0.U_AREA_RESP as "DUEÑO"
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE
INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM
INNER JOIN OSLP T3 on T0.SlpCode = T3.SlpCode
INNER JOIN INV1 T4 ON T0.DocEntry = T4.DocEntry
LEFT OUTER JOIN RIN1 T5 ON T4.DocEntry = T5.BaseEntry AND T4.LineNum = T5.BaseLine
INNER JOIN ORIN T6 ON T4.DocEntry = T5.DocEntry
WHERE (T0.DocTotal-T0.PaidToDate+t6.DocTotal > 0 or T0.DocTotalFC-T0.PaidFC+T6.DocTotal > 0)
Hola, Karla.
El where lo tienes asi:
WHERE (T0.DocTotal-T0.PaidToDate+t6.DocTotal
> 0 or T0.DocTotalFC-T0.PaidFC+T6.DocTotal > 0)
Intenta cambiarlo para tenerlo así:
WHERE (T0.DocTotal-T0.PaidToDate+t6.DocTotal > 0
Or T0.DocTotalFC-T0.PaidFC+T6.DocTotal > 0)
Y el JOIN de ORIN igual intenta cambiarlo a LEFT JOIN.
Saludos y suerte,
JC.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
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.