estou com uma procedure , que analise se o cliente do pedido esta em atraso, caso sim ele vai para uma autorização... o grande problema é que , mesmo que o cliente esteja sem nenhum titulo em atraso, mas já houve alguma vez registro de que ele atrasou ele traz... gostaria de consertar para ele ir para autorização só e somente só ele estiver em atraso mesmo...
USE [SBO_EXOMED] GO /****** Object: StoredProcedure [dbo].[SP_FL_ATRASO_CLIENTE] Script Date: 18/10/2016 14:15:40 ******/ 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