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
I am doing something similar to the above script. And have created a batch record in SY00500 that looks like the GP created batch records from receivables batches entry, but my added batch does not display on the batch lookup screen in GP. Is there one part of the code above that makes the a created batch visible in the batch lookup screen? Thanks.
ReplyDeleteThe last part from SOP_Batch_Reconcile. That should fix any issues with the batch and make it visible.
DeleteThis comment has been removed by the author.
ReplyDelete