Antiguedad de saldos de proveedores Consulta

[Former Member]

Buenos dias:

Alguien sabe como podria modificar la consulta de Antigüedad de saldos de proveedores? necesito aumentarles unos datos a la consulta, pero no tengo dicha consulta si alguien me la podria proporcionar se los agradeceria mucho

Saludos

Gary Navarro

[Humberto_Neira1 · Accepted solution]

Gary,

Para cualquier query que necesites conocer de B1, te recomiendo familiarizarte con la herramienta "SQL profiler".

Esa herramienta te permite ver las querys que son enviadas a la BD, y si la usas adecuadamente con lso botones STOP y PLAY puedes capturar las querys exactas que van a la BD cada vez que se hace algún click en algun boton de B1.

Es importante hacer ver, que también capturarás una cantidad importante de querys sin mayor sentido para nosotros, pero verás que no es tan dificil dar con la query correcta.

Esto te sirve para este caso, y para cualquier otro que necesites hacer.

saludos

[llopezf1920]

Estimados, espero les sirva

SELECT  T0."DocEntry",T0."DocNum",


 T4."SlpName", T0."CardCode", T0."CardName" AS "PROVEEDORES",T2."GroupName",  T0."FolioNum", T0."DocDate",  T0."DocDueDate",T0."TaxDate",  T0."Comments", T0."DocTotal" ,
(SELECT SUM (DISTINCT T1."DocTotal") FROM OPCH T1 INNER JOIN PCH1 T3 ON T1."DocEntry" = T3."DocEntry" WHERE T1."CardName" = T0."CardName" AND T1."DocEntry" = T0."DocEntry" AND T1."DocDueDate" >= CURRENT_DATE AND T1."CANCELED" = 'N' AND T3."TargetType" NOT IN (18,19) ) AS "No Vencido",


(SELECT SUM (DISTINCT T1."DocTotal") FROM OPCH T1 INNER JOIN PCH1 T3 ON T1."DocEntry" = T3."DocEntry" WHERE T1."CardName" = T0."CardName" AND T1."DocEntry" = T0."DocEntry" AND T1."DocDueDate" <CURRENT_DATE AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE)) <= 30 AND T1."CANCELED" = 'N' AND T3."TargetType" NOT IN (18,19) ) AS " 0      -      30 ",
(SELECT SUM (DISTINCT T1."DocTotal") FROM OPCH T1 INNER JOIN PCH1 T3 ON T1."DocEntry" = T3."DocEntry" WHERE T1."CardName" = T0."CardName" AND T1."DocEntry" = T0."DocEntry" AND T1."DocDueDate" < CURRENT_DATE AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) >=31  AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) <= 60 AND T1."CANCELED" = 'N' AND T3."TargetType" NOT IN (18,19) ) AS " 31      -      60 ",
(SELECT SUM (DISTINCT T1."DocTotal") FROM OPCH T1 INNER JOIN PCH1 T3 ON T1."DocEntry" = T3."DocEntry" WHERE T1."CardName" = T0."CardName" AND T1."DocEntry" = T0."DocEntry" AND T1."DocDueDate" < CURRENT_DATE AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) >=61  AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) <= 90 AND T1."CANCELED" = 'N' AND T3."TargetType" NOT IN (18,19) ) AS " 61      -      90 ",
(SELECT SUM (DISTINCT T1."DocTotal") FROM OPCH T1 INNER JOIN PCH1 T3 ON T1."DocEntry" = T3."DocEntry" WHERE T1."CardName" = T0."CardName" AND T1."DocEntry" = T0."DocEntry" AND T1."DocDueDate" < CURRENT_DATE AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) >=91  AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) <= 180 AND T1."CANCELED" = 'N' AND T3."TargetType" NOT IN (18,19) ) AS " 91      -      180 ",
(SELECT SUM (DISTINCT T1."DocTotal") FROM OPCH T1 INNER JOIN PCH1 T3 ON T1."DocEntry" = T3."DocEntry" WHERE T1."CardName" = T0."CardName" AND T1."DocEntry" = T0."DocEntry" AND T1."DocDueDate" < CURRENT_DATE AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) >=181 AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) <= 360  AND T1."CANCELED" = 'N' AND T3."TargetType" NOT IN (18,19) ) AS " 181      -      360 ",
(SELECT SUM (DISTINCT T1."DocTotal") FROM OPCH T1 INNER JOIN PCH1 T3 ON T1."DocEntry" = T3."DocEntry" WHERE T1."CardName" = T0."CardName" AND T1."DocEntry" = T0."DocEntry" AND T1."DocDueDate" < CURRENT_DATE AND ABS(DAYS_BETWEEN ( T1."DocDueDate", CURRENT_DATE )) >=361 AND T1."CANCELED" = 'N' AND T3."TargetType" NOT IN (18,19)) AS "       360+       ",
(SELECT SUM (DISTINCT T1."DocTotal") FROM OPCH T1 INNER JOIN PCH1 T3 ON T1."DocEntry" = T3."DocEntry" WHERE T1."CardName" = T0."CardName" AND T1."DocEntry" = T0."DocEntry" AND T1."DocDueDate" < CURRENT_DATE AND T1."CANCELED" = 'N'AND T3."TargetType" NOT IN (18,19)) AS "Vencido",


  T5."DocNum" AS "Nro. Int. Pago",T5."SumApplied" AS "ABONO",T6."BalDueCred" AS "SALDO"


FROM OPCH T0 
INNER JOIN PCH1 T3 ON T0."DocEntry" = T3."DocEntry"
INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode" 
INNER JOIN OCRG T2 ON T1."GroupCode" = T2."GroupCode"
INNER JOIN OSLP T4 ON T0."SlpCode" = T4."SlpCode"
LEFT JOIN VPM2 T5 ON T0."DocEntry" =  T5."DocEntry"
LEFT JOIN JDT1 T6 ON T0."DocEntry" =  T6."SourceID"  AND T0."DocNum" = T6."BaseRef" 


WHERE T0."CANCELED" = 'N'
AND  T3."TargetType" = -1
AND T6."SourceLine" = 1
AND T6."BalDueCred" <> 0


GROUP BY T0."CardName",T0."DocEntry",T0."DocNum",T0."CANCELED", T0."CardCode",T2."GroupName",  T0."FolioNum", T0."DocDate", T4."SlpName", 
T0."DocDueDate",T0."TaxDate",  T0."Comments", T0."DocTotal", T0."ReceiptNum",  T5."DocEntry", T5."SumApplied", T5."DocNum", T6."BalDueCred"
ORDER BY T0."CardName" ASC , T0."DocDueDate" ASC

[vidal2023]

Hola llopezf1920 , me ayudo mucho tu query 2 consultas , con referencia al filtro para colocar la fecha de corte cual seria no logro conseguirlo tal cual el reporte nativo, 2 como lograste ese query por algun analizador de consulta de hanna ? ??

[Former Member]

Estimado,

La consulta no se puede modificar ya que es un informe de sistema.

Att,

Manuel Lazcano

[Former Member]

Lo que deseo hacer es una consulta similar pero por el Query Manager, asi que si alguien sabe o tiene la consulta que saca el reporte de antiguedad de saldos de proveedor lo agradeceria

Saludos

Gary

[Former Member]

SELECT T0.[CardCode], T0.[CardName], T0.[Comments], T0.[DocTotal], T0.[TaxDate], cast(DATEDIFF(DAY, T0.[TaxDate], GETDATE()) as varchar) + ' Dias vencidos' FROM OPCH T0 where T0.[DocTotal] > T0.[PaidSum]

te puedo ayudar con eso es de proveedores pero igual solo busca las tablas con info del sistema y podes hacer algo similar