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
Add comment