Procedimento de Bloqueio atraso

Oct 18, 2016 at 05:32 PM
Valdixson Barnabé dos Santos
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
Find Questions & Answers

Procedimento de Bloqueio atraso

0 Answers

Find Questions & Answers
