/****** Object: StoredProcedure [dbo].[BI_AutoDayBatch_byUser] Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[BI_AutoDayBatch_byUser] @xBatch varchar(50),@xTag varchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create new batches if they do not already exist
Insert into SY00500 (GLPOSTDT,BCHSOURC,BACHNUMB,SERIES,MKDTOPST,NUMOFTRX,BACHFREQ,BCHCOMNT,USERID,BCHTOTAL,ORIGIN)
SELECT B.DOCDATE, B.Expr1, B.BNum, B.Expr2, B.Expr3, B.Expr4, B.Expr5, B.Expr6, B.Expr7, B.Expr8, B.Expr9
FROM (SELECT DISTINCT
DOCDATE, 'Sales Entry' AS Expr1,left(CONVERT(char(10), DOCDATE, 126) + @xtag,15) AS BNum, 3 AS Expr2, 0 AS Expr3, 0 AS Expr4, 1 AS Expr5, 'Move-' + BACHNUMB + ' ' + @xTag AS Expr6,
'DYNSA' AS Expr7, 0 AS Expr8, 1 AS Expr9
FROM SOP10100
WHERE (BACHNUMB = @xBatch)) AS B LEFT OUTER JOIN
SY00500 ON B.BNum = SY00500.BACHNUMB
WHERE (SY00500.BACHNUMB IS NULL)
-- Update all transactions in batch
Update sop10100
Set BACHNUMB = left(CONVERT(char(10), docdate,126)+@xTag,15)
where BACHNUMB = @xBatch and USER2ENT = @xTag
END
GO
/****** Object: StoredProcedure [dbo].[BI_AutoMonthBatch] Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Creates batches every month in the format BATCHNAME_YYMM
-- =============================================
CREATE PROCEDURE [dbo].[BI_AutoMonthBatch] @xBatch varchar(50),@xTag varchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create new batches if they do not already exist
Insert into SY00500 (GLPOSTDT,BCHSOURC,BACHNUMB,SERIES,MKDTOPST,NUMOFTRX,BACHFREQ,BCHCOMNT,USERID,BCHTOTAL,ORIGIN)
SELECT B.DOCDATE, B.Expr1, B.BNum, B.Expr2, B.Expr3, B.Expr4, B.Expr5, B.Expr6, B.Expr7, B.Expr8, B.Expr9
FROM (SELECT DISTINCT
DOCDATE, 'Sales Entry' AS Expr1,left('CREDIT_DEC_' + right(year(docdate),2) + right('00'+month(docdate),2),15) AS BNum, 3 AS Expr2, 0 AS Expr3, 0 AS Expr4, 1 AS Expr5, 'Move-' + BACHNUMB + ' ' + @xTag AS Expr6,
'DYNSA' AS Expr7, 0 AS Expr8, 1 AS Expr9
FROM SOP10100
WHERE (BACHNUMB = @xBatch)) AS B LEFT OUTER JOIN
SY00500 ON B.BNum = SY00500.BACHNUMB
WHERE (SY00500.BACHNUMB IS NULL)
-- Update all transactions in batch
Update sop10100
Set BACHNUMB = left('CREDIT_DEC_' + right(year(docdate),2) + right('00'+month(docdate),2),15)
where BACHNUMB = @xBatch and USER2ENT = @xTag
END
GO
/****** Object: StoredProcedure [dbo].[BI_BuildBatches] Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Schedule this SP to run periodically to generate batches
-- =============================================
CREATE PROCEDURE [dbo].[BI_BuildBatches]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Get list of current batches and users
--Cycle through each record and generate batch, assign all records, then reconcile batch
DECLARE @SOPCursor CURSOR;
DECLARE @MyField varchar(30);
BEGIN
SET @SOPCursor = CURSOR FOR
select distinct USER2ENT from sop10100
where SOPTYPE = 3 and BACHNUMB = 'INVOICED'
OPEN @SOPCursor
FETCH NEXT FROM @SOPCursor
INTO @MyField
WHILE @@FETCH_STATUS = 0
BEGIN
--begin algorithm
IF @MyField > '0' BEGIN
exec BI_AutoDayBatch_byUser 'INVOICED', @MyField
exec BI_SOP_Batch_Reconcile 'Sales Entry',0
END
--end algorithm
FETCH NEXT FROM @SOPCursor
INTO @MyField
END;
CLOSE @SOPCursor ;
DEALLOCATE @SOPCursor;
END;
END
GO
/****** Object: StoredProcedure [dbo].[BI_BuildCDBatches] Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Schedule this SP to run periodically to generate batches
-- =============================================
CREATE PROCEDURE [dbo].[BI_BuildCDBatches]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Get list of current batches and users
--Cycle through each record and generate batch, assign all records, then reconcile batch
DECLARE @SOPCursor CURSOR;
DECLARE @MyField varchar(30) ;
BEGIN
SET @SOPCursor = CURSOR FOR
select distinct USER2ENT from sop10100
where SOPTYPE = 3 and BACHNUMB = 'CREDIT DECLINED'
OPEN @SOPCursor
FETCH NEXT FROM @SOPCursor
INTO @MyField
WHILE @@FETCH_STATUS = 0
BEGIN
--begin algorithm
IF @MyField > '0' BEGIN
exec BI_AutoMonthBatch 'CREDIT DECLINED',@MyField
exec BI_SOP_Batch_Reconcile 'Sales Entry',0
END
--end algorithm
FETCH NEXT FROM @SOPCursor
INTO @MyField
END;
CLOSE @SOPCursor ;
DEALLOCATE @SOPCursor;
END;
END
GO
/****** Object: StoredProcedure [dbo].[BI_SOP_Batch_Reconcile] Script Date: 01/11/2017 12:19:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- create the store procedure
CREATE PROCEDURE [dbo].[BI_SOP_Batch_Reconcile]
@BCHSOURC varchar(15),
@REMOVE int --0 = Don't Delete Empty Batches, 1 = DO Delete Empty Batches
AS
/*
********************************************** STORED PROCEDURE HEADER ********************************************************
Comments: This stored procedure will reconcile SOP batches; add missing batch headers, recalculate batch totals, and
remove any empty batches.
REVISION HISTORY:
EXEC [dbo].[BI_SOP_Batch_Reconcile] 'Sales Entry', 1
*******************************************END OF STORED PROCEDURE HEADER ********************************************************
*/
set nocount on
/* Update the totals for the batch only for sales batches */
IF (@BCHSOURC <> 'Sales Entry')
RETURN (0)
--DECLARE THE VARIABLES
DECLARE @BACHNUMB varchar(15),
@I_sCompanyID smallint,
@O_mNoteIndex numeric(19,5),
@O_iErrorState int,
@I_vCHEKBKID char(15),
@CURNCYID char(15)
--Get the CompanyID
select @I_sCompanyID = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()
--Get the Checkbook ID
select @I_vCHEKBKID = CHEKBKID
from SOP40100
--Get the Functional Currency
select @CURNCYID = FUNLCURR
from MC40000
--DECLARE THE CURSOR
DECLARE curReconcileSOPBatches cursor fast_forward for
-- Reconcile existing batches
select BACHNUMB
from SY00500
where BCHSOURC = @BCHSOURC
union all
--Insert any missing SOP Batches
select distinct BACHNUMB
from SOP10100
where BACHNUMB not in (select BACHNUMB from SY00500 where BCHSOURC = @BCHSOURC)
and BACHNUMB <> ''
--OPEN THE CURSOR
OPEN curReconcileSOPBatches
FETCH next from curReconcileSOPBatches
into @BACHNUMB
WHILE (@@FETCH_STATUS = 0)
BEGIN
print(@BACHNUMB)
IF NOT EXISTS
(
select BACHNUMB
from SY00500
where BACHNUMB = @BACHNUMB
and BCHSOURC = @BCHSOURC
)
BEGIN
--Get and increment the next note index
exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output
INSERT into SY00500
(
GLPOSTDT,BCHSOURC,BACHNUMB,SERIES,BACHFREQ,USERID,CHEKBKID,MODIFDT,CREATDDT,NOTEINDX,CURNCYID,ORIGIN
)
select
CONVERT(varchar(10), GetDate(), 101), -- GLPOSTDT
@BCHSOURC, -- BCHSOURC
@BACHNUMB, -- BACHNUMB
3, -- SERIES
1, -- BACHFREQ
'sa', -- USERID
@I_vCHEKBKID, -- CHEKBKID
CONVERT(varchar(10), GetDate(), 101), -- MODIFDT
CONVERT(varchar(10), GetDate(), 101), -- CREATDDT
@O_mNoteIndex, -- NOTEINDX
@CURNCYID, -- CURNCYID
1 --ORIGIN
END
--EXECUTE THE LOGIC
DECLARE @NUMOFTRX integer
DECLARE @BCHTOTAL numeric(19,5)
-- Get the number of transactions
SELECT @NUMOFTRX = COUNT(s.SOPNUMBE)
FROM SOP10100 s
INNER JOIN SY00500 b ON
s.BACHNUMB = b.BACHNUMB AND
b.BCHSOURC = @BCHSOURC
WHERE s.BACHNUMB = @BACHNUMB
-- Get the batch amount
SELECT @BCHTOTAL = SUM(s.DOCAMNT)
FROM SOP10100 s
INNER JOIN SY00500 b ON
s.BACHNUMB = b.BACHNUMB AND
b.BCHSOURC = @BCHSOURC
WHERE s.BACHNUMB = @BACHNUMB
-- Remove any nulls
SELECT @BCHTOTAL = COALESCE(@BCHTOTAL, 0)
/* See if the quantity is 0. */
IF @NUMOFTRX = 0
AND @REMOVE = 1
BEGIN
-- Remove the batch if it does not have any transactions
DELETE SY00500
WHERE BACHNUMB = @BACHNUMB AND
BCHSOURC = @BCHSOURC
END
ELSE
BEGIN
-- Update the batch
UPDATE SY00500
SET BCHTOTAL = @BCHTOTAL,
NUMOFTRX = @NUMOFTRX
WHERE BACHNUMB = @BACHNUMB AND
BCHSOURC = @BCHSOURC
END
--GET THE NEXT RECORD
FETCH next from curReconcileSOPBatches
into @BACHNUMB
END
--CLOSE AND DEALLOCATE THE CURSOR
CLOSE curReconcileSOPBatches
DEALLOCATE curReconcileSOPBatches
GO
