Archive Advance Ship Notices w/ Customer Number & Actual Ship Date

You can run the Stored Procedure (see below) to Archive your ASNs based on a Customer Number and the Actual Ship Date.

Executing

Navigate: Company Database → Programmability → EDI_Archive_ASNs_CUSTNMBR_ACTLSHIP

Right Click: Execute Stored Procedure

Parameters

CUSTNBMR: Dynamics GP Customer Number

Date Format: 2016-05-01 00:00:00.000

EDI_Archive_ASNs_CUSTNMBR_ACTLSHIP

SQL Script

You can run the below script to create the Stored Procedure if you do not have it

Note: Replace [TWO] with your Dynamics GP Company database.

USE [TWO]
GO

/****** Object:  StoredProcedure [dbo].[EDI_Archive_ASNs_CUSTNMBR_ACTLSHIP]    Script Date: 5/5/2016 4:38:15 PM ******/

/*****   date format is   CCYY-MM-DD HH:MM:SS.SSS    example:  2016-05-01 00:00:00.000  *****/
/*****   CUSTNBMR is the GP Customer Number  *****/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[EDI_Archive_ASNs_CUSTNMBR_ACTLSHIP]
	@ACTDATE datetime,
	@CUSTNMBR char(40)

AS

INSERT INTO ESI30300 (SEQNUMBR,MSTRNUMB,ESICARTN,ESICPCID,ITEMNMBR,LNITMSEQ,ESIPKQTY,QTYFULFI,QTYREMAI,UOFM,ITEMSHWT,ESISCODE,ESISHPID)  
SELECT SEQNUMBR, MSTRNUMB,ESICARTN,ESICPCID,ITEMNMBR,LNITMSEQ,ESIPKQTY,QTYFULFI,QTYREMAI,UOFM,ITEMSHWT,ESISCODE,ESISHPID FROM ESI10300 WHERE ESISHPID IN 
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)

INSERT INTO ESI30200 (SEQNUMBR,MSTRNUMB,ESICARTN,ESIPKCID,ESIPALNM,ESIPALID,ESIUCCNM,ESIUCCPN,ITEMSHWT,ESISHPID) SELECT SEQNUMBR,MSTRNUMB,ESICARTN,ESIPKCID,ESIPALNM,ESIPALID,ESIUCCNM,ESIUCCPN,ITEMSHWT,ESISHPID FROM ESI10200 WHERE ESISHPID IN 
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)

INSERT INTO ESI30100 (CUSTNMBR,SEQNUMBR,MSTRNUMB,SOPNUMBE,SOPTYPE,ESITCTNS,ESITOTVOL,ITEMSHWT,ESISCODE,ESILBLDT,ESIADOPT,ESISHPID,LOCNCODE,PRSTADCD,PRBTADCD) SELECT CUSTNMBR,SEQNUMBR,MSTRNUMB,SOPNUMBE,SOPTYPE,ESITCTNS,ESITOTVOL,ITEMSHWT,ESISCODE,ESILBLDT,ESIADOPT,ESISHPID,LOCNCODE,PRSTADCD,PRBTADCD FROM ESI10100 WHERE ESISHPID IN 
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)



INSERT INTO ESI30001 (CUSTNMBR,ESISHPID,vs_HDR_Reference_1,vs_HDR_Reference_2,vs_HDR_Reference_3,vs_HDR_Reference_4,vs_HDR_Reference_5,vs_HDR_UDEF_1,vs_HDR_UDEF_2,vs_HDR_UDEF_3,vs_HDR_UDEF_4,vs_HDR_UDEF_5,vs_HDR_REF_Qual_1,vs_HDR_REF_Qual_2,vs_HDR_REF_Qual_3,vs_HDR_REF_Qual_4,vs_HDR_REF_Qual_5,vs_Integer_1,vs_Integer_2,vs_Integer_3,vs_Integer_4,vs_Integer_5,vs_Extra_1,vs_Extra_2,vs_Extra_3,vs_Extra_4,vs_Extra_5) SELECT CUSTNMBR,ESISHPID,vs_HDR_Reference_1,vs_HDR_Reference_2,vs_HDR_Reference_3,vs_HDR_Reference_4,vs_HDR_Reference_5,vs_HDR_UDEF_1,vs_HDR_UDEF_2,vs_HDR_UDEF_3,vs_HDR_UDEF_4,vs_HDR_UDEF_5,vs_HDR_REF_Qual_1,vs_HDR_REF_Qual_2,vs_HDR_REF_Qual_3,vs_HDR_REF_Qual_4,vs_HDR_REF_Qual_5,vs_Integer_1,vs_Integer_2,vs_Integer_3,vs_Integer_4,vs_Integer_5,vs_Extra_1,vs_Extra_2,vs_Extra_3,vs_Extra_4,vs_Extra_5 FROM ESI10001 WHERE ESISHPID IN
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)


INSERT INTO ESI30000 (ESISHPID,ESI856NM,CUSTNMBR,ACTLSHIP,ESIARRIV,ESIBOLNM,ESIPRONM,ESITRAIL,ESITCTNS,ESITOTVOL,ITEMSHWT,ESISCODE,ESIASNDT,LOCNCODE,PRSTADCD,PRBTADCD,SHIPMTHD) SELECT ESISHPID,ESI856NM,CUSTNMBR,ACTLSHIP,ESIARRIV,ESIBOLNM,ESIPRONM,ESITRAIL,ESITCTNS,ESITOTVOL,ITEMSHWT,ESISCODE,ESIASNDT,LOCNCODE,PRSTADCD,PRBTADCD,SHIPMTHD FROM ESI10000 WHERE ESISHPID IN 
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)




DELETE ESI10300 WHERE ESISHPID IN 
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)


DELETE ESI10200 WHERE ESISHPID IN 
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)

DELETE ESI10100 WHERE ESISHPID IN 
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)


DELETE ESI10001 WHERE ESISHPID IN 
(SELECT ESISHPID FROM ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR)

DELETE ESI10000 WHERE ACTLSHIP < @ACTDATE and CUSTNMBR = @CUSTNMBR


GO

WARNING

Scripts are provided as-is, and are considered run-at-your-own-risk.

Always consult your System Admin before running

rev 12/23/22