Asociado solicita la eliminación de su corte por algún error o bien cambiarlo de estatus. Cortes parciales o corte final están en un estatus incorrecto según las necesidades del asociado.
Query: Balance – Verificación Corte de Cajeros
DECLARE @Date datetime = ‘2024-07-16’ –Día de Operación
DECLARE @Cashier int = 9997 –Employee
DECLARE @Emp int = (Select employeeno from employee where employeeno = @Cashier or payrollid = @Cashier)
Select GETDATE() as ‘HORA ACTUAL’, ‘CIERRE DE TIENDA’, ProgStartTim, EOPStatus, AcctStatus, CurrentDate, CurRptDate, Progname,Lasttran,* FROM AcctPeriod order by getdate() desc
/* Cierre de caja general */
SELECT @@SERVERNAME AS ‘Server Name’
SELECT ‘CIERRE CAJA GENERAL’, * FROM STORE_FinalTenderTotals WHERE CAST(Date as date) = @Date
SELECT * FROM CashierBalances WHERE Date = @Date AND EmployeeNo = @Emp
SELECT TABadge, JobTitle, * FROM Employee WHERE EMPLOYEENO = @Emp OR PayrollId = @Emp
/* Encabezado del corte */
SELECT * FROM STORE_Withdrawals WHERE Cast(Date as date) = @Date and EmployeeNo = @Emp
SELECT @@SERVERNAME AS ‘Server Name’
BEGIN
SELECT TenderCode AS ‘Tipo de pago’, Cajas, Cortes–, convert(money,Cajas)-Isnull(convert(money,Cortes),0) ‘Diferencia’
FROM(
/* Información de cortes */
SELECT
CONCAT(DEN.TenderCode, ‘ – ‘, TN.[Description]) AS TenderCode,
FORMAT(SUM(WDE.TotalPrice), ‘C’, ‘en-us’) AS Amount,
‘Cortes’ AS Type
FROM STORE_Withdrawals AS WD
JOIN STORE_WithdrawalDenominations AS WDE ON WD.WithdrawalId = WDE.WithdrawalId
JOIN STORE_Denominations AS DEN ON WDE.DenominationId = DEN.DenominationId
JOIN TenderCntrl TN ON DEN.TenderCode = TN.TenderCode
WHERE WD.WithdrawalId IN (SELECT WithdrawalId FROM STORE_Withdrawals WHERE EmployeeNo = @Emp)
AND CAST(WD.Date as date) = @Date
GROUP BY DEN.TenderCode, TN.[Description]
UNION
/* Información de cajas */
SELECT
CONCAT(TTE.TenderCode, ‘ – ‘, TN.[Description]) AS TenderCode,
FORMAT(SUM(tte.TenderAmount), ‘C’, ‘en-us’) AS Amount,
‘Cajas’ AS Type
FROM TranTotal TT
JOIN TranTender TTE on TT.TranNumber = TTE.TranNumber and TT.TranDate = TTE.TranDate and TT.RegisterID = TTE.RegisterID
JOIN Employee AS EMP ON TT.EmployeeNo = EMP.EmployeeNo
JOIN TenderCntrl TN ON TTE.TenderCode = TN.TenderCode
WHERE TT.TranDate = @Date
AND TT.EmployeeNo = @Emp
AND TT.OperatMode = 0
AND TT.RecType = ‘N’
AND TT.TypeOfSale NOT IN (3,49,88)
AND TTE.TenderCode != 14
GROUP BY TTE.TenderCode, TN.[Description]
) M
PIVOT
(
MAX(Amount) FOR Type in (Cajas,Cortes)
) piv;
END
/* Detalle del corte final */
SELECT ‘DETALLE DE CORTE FINAL’, * FROM STORE_Withdrawaldenominations wd JOIN STORE_Withdrawals w ON wd.WithdrawalId = w.Withdrawalid
WHERE Cast(Date as date) = @Date and EmployeeNo = @Emp and w.DocumentType like ‘%final%’
/* Transacciones del cajero */
SELECT tt.posted, tt.Endtime, tt.RecType, tt.OperatMode, te.TranDate, tt.RegisterId, tt.TranNumber, tt.EmployeeNo, te.TenderCode, te.TenderAmount, SigSequence
FROM TranTotal tt LEFT JOIN TranTender te
ON te.TranDate = tt.TranDate and te.RegisterId = tt.RegisterId and te.TranNumber = tt.TranNumber
WHERE tt.TranDate = @Date and EmployeeNo = @Emp
ORDER BY EndTime
Procedemos a identificar el parcial/final que solicitan el cambio de estatus.
En este escenario lo vemos en borrador podemos cambiarlo a pending y que caja general lo apruebe.
Draft – Corte en borrador podemos borrar o colocar en pending (NO es visible desde SMS únicamente por BD)
Pending – Corte pendiente por aprobación, caja general lo puede revisar y aprobar o rechazar
Rejected – corte rechazado, caja general lo rechaza por algún error, asociado puede modificarlo y enviarlo nuevamente para aprobación (pending).
Approved – Corte aprobado por caja general se puede borrar/rechazar en caso que tenga algún error y el asociado lo solicite.
Para estos procesos ocupamos el dato del campo withdrawalid de la tabla STORE_Withdrawaldenominations y ejecutamos el update que necesitemos.
—Depurar/ ELIMINAR corte de cajero final o parcial
–Declare @wdr int = 50663 — colocar el dato que se encuentra en el campo de withdrawalid
–DELETE FROM STORE_Withdrawaldenominations where withdrawalid = @wdr
–DELETE FROM STORE_Withdrawals where withdrawalid = @wdr
—Actualizar corte de cajero a REJECTED
–update STORE_Withdrawals set status=’rejected’ where WithdrawalId = 363772
—Actualizar corte de cajero a PENDING
–update STORE_Withdrawals set status=’pending’ where WithdrawalId = 298795
—Actualizar corte de cajero a APROBADO
–update STORE_Withdrawals set status=’approved’ where WithdrawalId = 505137
