Good evening guys.
I need help. For you to understand, I'm trying to run a system procedure that asks me for some variables that I can pass them in a fixed way, but one is date. in case the date needs to be of the current day, as I want to schedule it to run daily.
Manually reporting works, I tried to use getdate()
as the value for the @date
parameter, but it did not work.
What I need is to pass the current date (date of the day) to the parameter @date
.
If anyone can help, I appreciate it.
Using SQL Server 2008 Express.
Procedure code :
GO
ALTER PROCEDURE [dbo].[sp_CloseTerminal] (@StoreCode char(6), @Terminal char(3), @Period char(2), @Operator char(4), @Date datetime, @SaveBalance bit)
AS
BEGIN
SET NOCOUNT ON
DECLARE @intSuspended smallint, @intSelling smallint, @nBalance numeric(14, 2)
SELECT @intSuspended = COUNT(*) FROM LOJA_VENDA WHERE CODIGO_FILIAL = @StoreCode AND TERMINAL = @Terminal AND DATA_VENDA = @Date AND LANCAMENTO_CAIXA IS NULL
IF @intSuspended > 0
BEGIN
SELECT @intSuspended AS SuspendedSales, @intSelling as Selling, CONVERT(bit, 0) AS TerminalClosed
RETURN
END
SELECT @intSelling = COUNT(*) FROM LOJA_VENDA (NOLOCK) V INNER JOIN LOJA_VENDA_PGTO (NOLOCK) PG ON V.CODIGO_FILIAL = PG.CODIGO_FILIAL AND V.TERMINAL = PG.TERMINAL AND V.LANCAMENTO_CAIXA = PG.LANCAMENTO_CAIXA WHERE V.CODIGO_FILIAL = @STORECODE AND V.TERMINAL = @TERMINAL AND DATA_VENDA = @Date AND VENDA_FINALIZADA = 0
IF @intSelling > 0
BEGIN
SELECT @intSuspended AS SuspendedSales, @intSelling as Selling, CONVERT(bit, 0) AS TerminalClosed
RETURN
END
SELECT @nBalance = 0
IF @SaveBalance = 1
BEGIN
SELECT
@nBalance = ISNULL(SUM(ENTRADA_CAIXA), 0) + ISNULL(SUM(-SAIDA_CAIXA), 0)
FROM
LOJA_CAIXA_LANCAMENTOS A
LEFT JOIN LOJA_PGTO_CLIENTE B on A.CODIGO_FILIAL = B.CODIGO_FILIAL AND A.TERMINAL = B.TERMINAL AND A.LANCAMENTO_CAIXA = B.LANCAMENTO_CAIXA
WHERE
/* A.TIPO_LANCAMENTO_CAIXA NOT IN ('99') AND */ ISNULL(B.TIPO_PGTO, 'D') = 'D'
AND A.CODIGO_FILIAL = @StoreCode AND A.TERMINAL = @Terminal AND A.DATA = @Date -- AND A.PERIODO_FECHAMENTO = @Period
SELECT
@nBalance = @nBalance + ISNULL(SUM(VALOR), 0)
FROM
LOJA_VENDA_PGTO A
INNER JOIN LOJA_VENDA_PARCELAS B ON A.CODIGO_FILIAL = B.CODIGO_FILIAL AND A.TERMINAL = B.TERMINAL AND A.LANCAMENTO_CAIXA = B.LANCAMENTO_CAIXA
WHERE
TIPO_PGTO = 'D'/*IN ('D', 'C', 'P')*/ AND A.CODIGO_FILIAL = @StoreCode AND A.TERMINAL = @Terminal AND A.DATA = @Date -- AND A.PERIODO_FECHAMENTO = @Period
END
INSERT INTO LOJA_CAIXA_LANCAMENTOS
(CODIGO_FILIAL, TERMINAL, LANCAMENTO_CAIXA, TIPO_LANCAMENTO_CAIXA, HISTORICO, ENTRADA_CAIXA, SAIDA_CAIXA,
DATA, DIGITACAO, CAIXA_VENDEDOR, PERIODO_FECHAMENTO)
VALUES
(@StoreCode, @Terminal, dbo.fn_GetNextPaymentSequence(@StoreCode, @Terminal, @Date), '99', 'Fechamento do terminal', 0, @nBalance,
@Date, GETDATE(), @Operator, @Period)
UPDATE LOJA_TERMINAIS SET STATUS = 0, DATA_ULTIMO_STATUS = @Date WHERE CODIGO_FILIAL = @StoreCode AND TERMINAL = @Terminal
SELECT @intSuspended AS SuspendedSales, @intSelling as Selling, CONVERT(bit, 1) AS TerminalClosed
SET NOCOUNT OFF
END