Procedimento Clientes com Títulos em atraso

[former_member235051]

Bom dia tenho, um procedimento na minha base, onde faço uma chamada através de uma consulta formatada, para um bloqueio de clientes com títulos em atraso… sendo que ao longo do uso notamos que esse procedimento está trazendo o bloqueio de clientes com títulos em atraso sem está devendo…

se por acaso o cliente já deveu algum titulo, mesmo que já esteja quitado ele sempre traz como bloqueio para liberação… poderiam me ajudar no que está errado… , abaixo segue o código:

USE [SBO_EXOMED]
GO
/****** Object:  StoredProcedure [dbo].[SP_FL_ATRASO_CLIENTE]    Script Date: 10/10/2016 11:32:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_FL_ATRASO_CLIENTE]
	  @Cliente  NVARCHAR(15) = NULL
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @_Qtd INTEGER;
  DECLARE @_MSN NVARCHAR(100);
  
  SELECT @_MSN = 'FALSE'
  
  SELECT @_Qtd = COUNT(*) FROM (
  SELECT T1.[TransId], T1.[TransRowId],   MAX(T0.[ShortName]) ShortName, MAX(T0.[TransType]) TransType,
		   MAX(T0.[CreatedBy]) CreatedBy, MAX(T0.[BaseRef]) BaseRef,     MAX(T0.[SourceLine]) SourceLine,
		   MAX(T0.[RefDate])   RefDate,   MAX(T0.[DueDate]) DueDate,     MAX(T0.[TaxDate]) TaxDate,
		   MAX(T0.[BalDueCred]) + SUM(T1.[ReconSum]) SOMA_1,             MAX(T0.[BalFcCred]) + SUM(T1.[ReconSumFC]) SOMA_2, 
		   MAX(T0.[BalScCred])  + SUM(T1.[ReconSumSC])SOMA_3,            MAX(T0.[LineMemo]) LineMemo, 
		   MAX(T3.[FolioPref]) FolioPref, MAX(T3.[FolioNum]) FolioNum,   MAX(T0.[Indicator]) Indicator, 
		   MAX(T0.[Account])   Account
  FROM  [dbo].[JDT1] T0  INNER  JOIN [dbo].[ITR1] T1 
		  ON  T1.[TransId] = T0.[TransId]  AND  T1.[TransRowId] = T0.[Line_ID] 
		  INNER  JOIN [dbo].[OITR] T2  
		  ON  T2.[ReconNum] = T1.[ReconNum] 
		  INNER  JOIN [dbo].[OJDT] T3 
		  ON  T3.[TransId] = T0.[TransId]
		  LEFT OUTER  JOIN [dbo].[OINV] T4
		  ON  T3.[TransType] = 13  AND  T4.[DocEntry] = T3.[CreatedBy] 
		  --LEFT OUTER  JOIN [dbo].[ORIN] T5 
		  --ON  T3.[TransType] = 14  AND  T5.[DocEntry] = T3.[CreatedBy]
		  LEFT OUTER  JOIN [dbo].[ODPI] T6 
		  ON  T3.[TransType] = 203  AND  T6.[DocEntry] = T3.[CreatedBy]   
		  LEFT OUTER  JOIN [dbo].[OCIN] T7  
		  ON  T3.[TransType] = 132  AND  T7.[DocEntry] = T3.[CreatedBy]
		  LEFT OUTER  JOIN [dbo].[OCSI] T8  
		  ON  T3.[TransType] = 165  AND  T8.[DocEntry] = T3.[CreatedBy]
		  LEFT OUTER  JOIN [dbo].[OCSV] T9  
		  ON  T3.[TransType] = 166  AND  T9.[DocEntry] = T3.[CreatedBy]
  WHERE T0.[DueDate]   < CAST(GETDATE() AS Date)
    AND T2.[ReconDate]  > GETDATE()
    AND T1.[IsCredit]   = ('C')
    AND T0.[ShortName]  = @Cliente		 -- PARÂMETRO: CÓDIGO DO LIENTE 
	AND T0.[BalDueDeb] <>0  -- PARA TRATAR SÓ PARCELAS EM ABERTO
  GROUP BY T1.[TransId], T1.[TransRowId] 
  HAVING MAX(T0.[BalFcCred])  <> - SUM(T1.ReconSumFC)  
     OR  MAX(T0.[BalDueCred]) <> - SUM(T1.ReconSum) ) XX
  
  IF @_Qtd > 0
    SELECT @_MSN = 'TRUE'

  ELSE  
    BEGIN
    SELECT @_Qtd = COUNT(*) FROM (
    SELECT T1.[TransId], T1.[TransRowId], MAX(T0.[ShortName]) ShortName, MAX(T0.[TransType]) TransType,
         MAX(T0.[CreatedBy]) CreatedBy, MAX(T0.[BaseRef]) BaseRef,     MAX(T0.[SourceLine]) SourceLine, 
         MAX(T0.[RefDate]) RefDate,     MAX(T0.[DueDate]) DueDate,     MAX(T0.[TaxDate]) TaxDate, 
       - MAX(T0.[BalDueDeb])- SUM(T1.[ReconSum])   SOMA_1,           - MAX(T0.[BalFcDeb]) - SUM(T1.[ReconSumFC]) SOMA_2, 
       - MAX(T0.[BalScDeb]) - SUM(T1.[ReconSumSC]) SOMA_3,             MAX(T0.[LineMemo]) LineMemo, 
         MAX(T3.[FolioPref]) FolioPref, MAX(T3.[FolioNum]) FolioNum,   MAX(T0.[Indicator])Indicator, 
         MAX(T0.[Account]) Account
    FROM  [dbo].[JDT1] T0  INNER  JOIN [dbo].[ITR1] T1  
		  ON T1.[TransId] = T0.[TransId] AND  T1.[TransRowId] = T0.[Line_ID]
		  INNER JOIN [dbo].[OITR] T2  
		  ON T2.[ReconNum] = T1.[ReconNum]
		  INNER JOIN [dbo].[OJDT] T3
		  ON T3.[TransId] = T0.[TransId]
		  LEFT OUTER JOIN [dbo].[OINV] T4
		  ON T3.[TransType] = 13 AND  T4.[DocEntry] = T3.[CreatedBy]
		  --LEFT OUTER JOIN [dbo].[ORIN] T5
		  --ON T3.[TransType] = 14 AND  T5.[DocEntry] = T3.[CreatedBy]
		  LEFT OUTER JOIN [dbo].[ODPI] T6 
		  ON T3.[TransType] = 203 AND T6.[DocEntry] = T3.[CreatedBy]
		  LEFT OUTER JOIN [dbo].[OCIN] T7
		  ON T3.[TransType] = 132 AND T7.[DocEntry] = T3.[CreatedBy]
		  LEFT OUTER JOIN [dbo].[OCSI] T8
		  ON T3.[TransType] = 165 AND T8.[DocEntry] = T3.[CreatedBy]
		  LEFT OUTER  JOIN [dbo].[OCSV] T9
		  ON T3.[TransType] = 166 AND T9.[DocEntry] = T3.[CreatedBy]
    WHERE T0.[DueDate]  < CAST(GETDATE() AS Date)
      AND T2.[ReconDate] > GETDATE()
      AND T1.[IsCredit]  = ('D')
      AND T0.[ShortName] = @Cliente		 -- PARÂMETRO: CÓDIGO DO LIENTE   
	  AND T0.[BalDueDeb] <>0 -- PARA TRATAR SÓ PARCELAS EM ABERTO
	GROUP BY T1.[TransId], T4.SERIAL, T1.[TransRowId] 
	HAVING MAX(T0.[BalFcDeb]) <> - SUM(T1.ReconSumFC) 
	   OR  MAX(T0.[BalDueDeb])<> - SUM(T1.ReconSum) ) XX
    
    IF @_Qtd > 0
      SELECT @_MSN = 'TRUE'
    
    ELSE
    BEGIN
      SELECT @_Qtd = COUNT(*) FROM (
      SELECT T0.[TransId], T0.[Line_ID],      MAX(T0.[ShortName]) ShortName, MAX(T0.[TransType])  TransType, 
			   MAX(T0.[CreatedBy]) CreatedBy, MAX(T0.[BaseRef]) BaseRef,     MAX(T0.[SourceLine]) SourceLine, 
               MAX(T0.[RefDate])   RefDate,   MAX(T0.[DueDate]) DueDate,     MAX(T0.[TaxDate]) TaxDate, 
			   MAX(T0.[BalDueCred]) - MAX(T0.[BalDueDeb]) SOMA_1,            MAX(T0.[BalFcCred]) - MAX(T0.[BalFcDeb]) SOMA_2, 
			   MAX(T0.[BalScCred])  - MAX(T0.[BalScDeb])  SOMA_3,            MAX(T0.[LineMemo]) LineMemo, MAX(T1.[FolioPref]) FolioPref, 
			   MAX(T1.[FolioNum]) FolioNum,   MAX(T0.[Indicator]) Indicator, MAX(T0.[Account]) Account
	  FROM  [dbo].[JDT1] T0  INNER  JOIN [dbo].[OJDT] T1
			  ON T1.[TransId] = T0.[TransId]
			  LEFT OUTER JOIN [dbo].[OINV] T2
			  ON T1.[TransType] = 13 AND  T2.[DocEntry] = T1.[CreatedBy]
			  --LEFT OUTER JOIN [dbo].[ORIN] T3
			  --ON T1.[TransType] = 14 AND T3.[DocEntry] = T1.[CreatedBy]
			  LEFT OUTER JOIN [dbo].[ODPI] T4
			  ON T1.[TransType] = 203 AND T4.[DocEntry] = T1.[CreatedBy]
			  LEFT OUTER JOIN [dbo].[OCIN] T5
			  ON T1.[TransType] = 132 AND T5.[DocEntry] = T1.[CreatedBy]
			  LEFT OUTER  JOIN [dbo].[OCSI] T6
			  ON T1.[TransType] = 165 AND T6.[DocEntry] = T1.[CreatedBy]
			  LEFT OUTER  JOIN [dbo].[OCSV] T7
			  ON T1.[TransType] = 166 AND T7.[DocEntry] = T1.[CreatedBy]
	  WHERE T0.[DueDate] < CAST(GETDATE() AS Date)
	    AND NOT EXISTS (SELECT U0.[TransId], U0.[TransRowId] 
						  FROM  [dbo].[ITR1] U0  INNER  JOIN [dbo].[OITR] U1
								ON  U0.[ReconNum] = U1.[ReconNum] 
						  WHERE T0.[TransId] = U0.[TransId]  
							AND T0.[Line_ID] = U0.[TransRowId]
							AND U1.[ReconDate] > GETDATE()
						  GROUP BY U0.[TransId], U0.[TransRowId])
		  AND  T0.[ShortName] = @Cliente  -- PARÂMETRO: CÓDIGO DO LIENTE 
		  AND T0.[BalDueDeb] <>0  -- PARA TRATAR SÓ PARCELAS EM ABERTO  
	  GROUP BY T0.[TransId], T2.SERIAL, T0.[Line_ID] 
	  HAVING MAX(T0.[BalFcCred])  <> MAX(T0.BalFcDeb) 
	     OR  MAX(T0.[BalDueCred]) <> MAX(T0.BalDueDeb) ) XX
      
      IF @_Qtd > 0
        SELECT @_MSN = 'TRUE'

    END
    
  END
  
  SELECT @_MSN
    
END

[Former Member]

Salvo engando isso entra em modelos de autorização.

A consulta é gerada pelo sistema, a medida que você responde alguns itens como tipo do documento, valor, relação e etc. Na versão 9.1 tínhamos um item chamado condições de pagamento e um campo atraso médio. Se o atraso for de 10 dias, resolvo. Mas, além disso, o gerente toma conhecimento e realiza o acerto comercial.

Talvez, você precise revisar a parametrização realizada.