Insert Records Sample

Sample Stored Procedure to Insert Records into ERP's EDI Tables:

^{CREATE PROCEDURE [dbo].[spEDIInsertRecords]}

^{@FileName varchar(500),}

^{@FilePath varchar(500),}

^{@OrderNumber varchar(50),}

^{@PONumber varchar(50),}

^{@PODate datetime,}

^{@ShipDate datetime,}

^{@CustomerShortName varchar(15),}

^{@DivisionCode varchar(3),}

^{@ItemNumber varchar(15),}

^{@Quantity int,}

^{@UnitPrice decimal(16,5)} 

^AS

^{DECLARE @FileID int}

^{DECLARE @POHeaderID int}

^{-- Create Temporary Table}

^{IF OBJECT_ID('tempdb..##tmpEDIFiles') IS NULL}

^BEGIN

^{CREATE TABLE ##tmpEDIFiles(FileID int, FileName varchar(500), FilePath varchar(500))}

^{SELECT * FROM ##tmpEDIFiles}

^END

^{-- Get the Latest File ID}

^{SELECT @FileID = FileID FROM EDI_File WHERE FileName = @FileName AND StatusID = 1}

^{IF @FileID IS NULL}

^BEGIN

^{INSERT INTO EDI_File (StatusID, FileTypeID, FileName, TestIndicator, SenderID, ReceiverID, ReceivedDateTime, ControlNumber)}

^{VALUES (1, 875, @FileName, 'P', 'ERPEDI', 'ERPEDI', getDate(), 1)}

^{SELECT @FileID = FileID FROM EDI_File WHERE FileName = @FileName AND StatusID = 1}

^{-- Store new file in Temporary Table}

^{INSERT INTO ##tmpEDIFiles VALUES(@FileID, @FileName, @FilePath)}

^END

^{-- See if the PO Header Already Exists}

^{SELECT @POHeaderID = POHeaderID FROM EDI_POHeader WHERE TransactionKey = @OrderNumber + '-' + @PONumber AND StatusID = 1}

^{IF @POHeaderID IS NULL}

^BEGIN

^{-- Create PO Header}

^{INSERT INTO EDI_POHeader (FileID, StatusID, TransactionKey, PONumber, PODate, ShipDate, CustomerShortName, DivisionCode)}

^{VALUES (@FileID, 1, @OrderNumber + '-' + @PONumber, @PONumber, @PODate, @ShipDate, @CustomerShortName, @DivisionCode)}

^{SELECT @POHeaderID = POHeaderID FROM EDI_POHeader WHERE TransactionKey = @OrderNumber + '-' + @PONumber AND StatusID = 1}

^END

^{-- Create PO Detail Line}

^{INSERT INTO EDI_PODetail (POHeaderID, ItemNumber, Quantity, UnitPrice)}

^{VALUES (@POHeaderID, @ItemNumber, @Quantity, @UnitPrice)}

^GO

ImportExportMap Records:
 

* SourceField values must match that of the incoming dataset. In this case, the XML file's field names.

* DestinationField values must match that of the Stored Procedure being called (see sample above).