Serial discrepancies
--Check all stock items, which are serialized Whse vs Serial Qty!
SELECT StockLink, StockCode, WHID, WHCode, SUM(SerialQty) as SerialQty, SUM(StockQty) as StockQty, diff = COALESCE(SUM(SerialQty),0) - COALESCE(SUM(StockQty),0) FROM
(select SNStockLink as StockLink, S.Code as StockCode, CurrentAccLink as WHID, W.Code as WHCode, count(*) as SerialQty, 0 as StockQty
from SerialMF
left join StkItem S ON SerialMF.SNStockLink = S.StockLink
left join WhseMst W ON CurrentAccLink = W.WhseLink where CurrentLoc IN(1,6,4) GROUP BY CurrentAccLink, W.Code, SNStockLink, S.Code
UNION ALL
SELECT P.AccountLink as StockLink, S.Code as StockCode, P.WarehouseID, W.Code, 0 as SerialQty,
--SUM(CASE WHEN Debit > 0 THEN Quantity ELSE -Quantity END) as SignedQty
SUM (CASE WHEN P.Id = 'JCI' THEN 0 --JCI invoices directly invoice out, so doesn't affect the available stock qty
WHEN P.Id = 'JCS' THEN 0 --JCS is always 0 anyway on the quantity column, so this shouldn't affect anything.
/*JCM -> standard rule applies on qty */
WHEN P.Id NOT IN ('JCI','JCS') AND Debit > 0 THEN Quantity
ELSE -Quantity END) as SignedQty
from PostST P
left join StkItem S ON P.AccountLink = S.StockLink
left join WhseMst W ON P.WarehouseID = W.WhseLink where S.SerialItem = 1 AND S.ServiceItem = 0
group by P.WarehouseID, W.Code, P.AccountLink, S.Code
) t
GROUP BY StockLink, WHID, WHCode, StockCode
HAVING SUM(SerialQty) <> SUM(StockQty)
ORDER BY StockCode
Stored Proc: WHT Serial (without PostST)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================
-- Author: Asamco B.V. - Alex
-- Create date: 2019-04-03
-- Description: Quick WH transfer for SN (no PostST entry!)
-- =================================================================
CREATE OR ALTER PROCEDURE _as_spSNWHT
-- Add the parameters for the stored procedure here
@SerialNumber varchar(50)
,@StockID int
,@FromWHID int
,@ToWHID int
,@TxDate datetime = NULL
AS
BEGIN
/*USAGE:
EXEC _as_spSNWHT 'SERIALNRHERE', 123,1,2
*/
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--fix tx date - no time or null:
SELECT @TxDate = Convert(date, COALESCE(@TxDate,GETDATE()))
--get Audit Number:
declare @AuditNo varchar(20) -- AuditNo
declare @IsBranch bit = 0 -- 1 if branch, otherwise 0
declare @TxBranchID int = 0 -- ID of the currenct branch
declare @AuditTemp float -- The temp value for the audit number.
EXEC @AuditTemp = _bspNextAuditNo;
select @AuditNo = CASE
WHEN @IsBranch = 1 THEN Cast(@TxBranchID as varchar(20)) + '.' + CAST(@AuditTemp as varchar) + '.0001'
ELSE CAST(@AuditTemp as varchar) + '.0001' END; --'GET NEXT AUDIT NO!!!!' --Check SP _bspNextAuditNo
DECLARE @SerialNumberID bigint
SELECT @SerialNumberID = COALESCE((SELECT SerialCounter FROM SerialMF S WHERE S.SerialNumber = @SerialNumber AND S.SNStockLink = @StockID),0)
DECLARE @TxRef varchar(100)
SELECT @TxRef = 'vIBT' + convert(varchar, getdate(), 120)
--post WH OUT:
EXECUTE [dbo].[_bspSNPostSerialNumber]
@SerialNumberID OUTPUT
,@SerialNumber = @SerialNumber
,@StockID = @StockID
,@TxType = 9
,@TxDate = @TxDate
,@TxReference = @TxRef
,@TxCodeID = 32 --hardcoded for ATT db.
,@ProjectID = 0
,@DrCrAccountID = 0
,@WarehouseID = @FromWHID
,@JobCodeID = 0
,@MFPID = 0
,@LotID = 0
,@AuditNumber = @AuditNo
,@TxReference2 = ''
,@MFPLineID = 0
,@iTxBranchID = 0
,@Quantity = 1
--reset:
--select @SerialNumberID = 0
--post WH in:
EXECUTE [dbo].[_bspSNPostSerialNumber]
@SerialNumberID OUTPUT
,@SerialNumber = @SerialNumber
,@StockID = @StockID
,@TxType = 10
,@TxDate = @TxDate
,@TxReference = @TxRef
,@TxCodeID = 32 --hardcoded for ATT db.
,@ProjectID = 0
,@DrCrAccountID = 0
,@WarehouseID = @ToWHID
,@JobCodeID = 0
,@MFPID = 0
,@LotID = 0
,@AuditNumber = @AuditNo
,@TxReference2 = ''
,@MFPLineID = 0
,@iTxBranchID = 0
,@Quantity = 1
END
GO