Create a sale order in GP using econnect

Step 1 - create tables needed

CREATE TABLE [dbo].[ICB_SOPErrors](

[ErrorID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[Date] [datetime] NULL,

[SOPNumber] [nvarchar](50) NULL,

[EconnectErrorNumber] [nvarchar](5) NULL,

[EconnectErrorMessage] [nvarchar](300) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[ICB_SOPDetail](

[OrderID] [int] NULL,

[ItemNmbr] [nvarchar](31) NULL,

[UnitPrce] [numeric](19, 2) NULL,

[Xtndprce] [numeric](19, 2) NULL,

[Quantity] [numeric](19, 2) NULL,

[ItemDesc] [nvarchar](100) NULL,

[Qtyfulfi] [numeric](19, 2) NULL,

[UofM] [nvarchar](8) NULL,

[Qtyshrtopt] [int] NULL,

[Taxschid] [nvarchar](15) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[ICB_SOPHeader](

[SOPType] [int] NULL,

[DocID] [nvarchar](15) NULL,

[OrderID] [int] NULL,

[TaxSchid] [nvarchar](15) NULL,

[Shipmthd] [nvarchar](15) NULL,

[Locncode] [nvarchar](10) NULL,

[Docdate] [datetime] NULL,

[TaxAmnt] [numeric](19, 2) NULL,

[Freight] [numeric](19, 2) NULL,

[Miscamnt] [numeric](19, 2) NULL,

[Custnmbr] [nvarchar](15) NULL,

[Subtotal] [numeric](19, 2) NULL,

[Docamnt] [numeric](19, 2) NULL,

[Bachnumb] [nvarchar](15) NULL,

[Prbtadcd] [nvarchar](15) NULL,

[Prstadcd] [nvarchar](15) NULL,

[Ordrdate] [datetime] NULL,

[Createdist] [int] NULL,

[Prclevel] [nvarchar](50) NULL,

[CmmtText] [nvarchar](500) NULL

) ON [PRIMARY]

Step 2 - econnect stored proc

create Procedure [dbo].[spICB_GroupOrders] as

Begin

--Next line is standard syntax for a stored proc

Set nocount on

--start a cursor for header information

DECLARE SOPHeader Cursor

FOR

Select *

From ICB_SOPHeader

Open SOPHeader

Declare @soptype int, @docid nvarchar(15), @OrderID int, @taxschid nvarchar(15), @shipmthd nvarchar(15), @locncode nvarchar(10),

@docdate datetime, @ReqShipDate datetime, @taxamnt numeric(19,2), @freight numeric(19,2),@miscamnt numeric(19,2), @custnmbr nvarchar(15),

@subtotal numeric(19,2), @docamnt numeric(19,2), @bachnumb nvarchar(15),

@prbtadcd nvarchar(15), @prstadcd nvarchar(15), @ordrdate datetime, @createdist int, @prclevel nvarchar(50), @cmmttext nvarchar(500)

--Holding variables for default tax scheduleid and customer address tax scheduleid

DECLARE @TAXSCHID1 nvarchar(15)

DECLARE @TAXSCHID2 nvarchar(15)

--Declare variable for determining Great Plains sales order number

declare @SOPNumbe nvarchar(21)

--Declare variables required by the econnect stored procs

declare @O_iErrorState int, @oErrString varchar(255)

--Declare additional variables we will use to better track the econnect error messages

declare @ErrorState2 int, @ErrorState3 int, @ErrString2 varchar(255), @ErrString3 varchar(255), @ErrorItem Varchar(500)

--populate the cursor with the first record from the header

Fetch NEXT FROM SOPHeader

INTO

@soptype, @docid, @orderId, @taxschid, @shipmthd, @locncode, @docdate, @taxamnt, @freight, @miscamnt, @custnmbr,

@subtotal, @docamnt, @bachnumb, @prbtadcd, @prstadcd, @ordrdate, @createdist, @prclevel, @cmmttext

While (@@FETCH_STATUS =0)

BEGIN

--Begin a sql transaction so we can undo everything if it doesn't work

Begin transaction

-- The following Tax Schedule logic was added by M Plonowski on 10/11

--Get the current customer master tax scheduleid

select @TAXSCHID1 = TAXSCHID

FROM RM00101

WHERE CUSTNMBR = @custnmbr AND ADRSCODE = @prstadcd

--Get the current customer address tax scheduleid

select @TAXSCHID2 = TAXSCHID

FROM RM00102

WHERE CUSTNMBR = @custnmbr AND ADRSCODE = @prstadcd

IF @taxschid IN ('TX','CO')

Begin -- Update the customer's tax schedule to the appropriate state

IF NOT (@TAXSCHID1 IS NULL)

Begin -- Update Customer Master taxscheduleID

UPDATE RM00101

SET TAXSCHID = @taxschid

WHERE CUSTNMBR = @custnmbr AND ADRSCODE = @prstadcd

End -- Update Customer Master taxscheduleID

UPDATE RM00102

SET TAXSCHID = @taxschid

WHERE CUSTNMBR = @custnmbr AND ADRSCODE = @prstadcd

End -- Update the customer's tax schedule to the appropriate state

-- End of Tax Schedule logic added by M Plonowski on 10/11

--call the getsopnumber stored proc to get next sop number

select @O_iErrorState = 0

SET @ReqShipDate = DATEADD(d, 2, @DocDate)

if (@O_iErrorState = 0)

begin

exec taGetSOPNumber

@i_tSOPType = @soptype,

@I_cDocID = @docid,

@I_tInc_Dec = 1,

@O_vSOPNumber = @SOPNumbe output,

@O_iErrorState = @O_ierrorstate output

IF @O_iErrorState <> 0

Begin

Select @SOPNumbe

end

End

--we are now ready to begin the hardcore crunching

-- SOP Invoice Line

-- Begin SubCursor for Detail Info

DECLARE SOPDetail Cursor

FOR

Select *

From ICB_SOPDetail

Open SOPDetail

Declare @OrderID_Detail int, @ITEMNMBR nvarchar(31), @unitprce numeric(19,2), @xtndprce numeric(19,2), @quantity numeric (19,2), @itemdesc nvarchar(100),

@gpitemdesc nvarchar(100), @qtyfulfi numeric(19,2), @uofM nvarchar(8), @qtyshrtopt int, @taxschidDetail nvarchar(15)

Set @ErrorItem = ''

fetch NEXT FROM SOPDetail INTO

@OrderID_Detail , @ITEMNMBR, @unitprce, @xtndprce, @quantity, @itemdesc, @qtyfulfi, @uofm, @qtyshrtopt, @taxschidDetail

While (@@FETCH_STATUS =0)

Begin

select @O_iErrorState = 0

if (@O_iErrorState = 0)

begin

-- Added by Michael Plonowski on 9/17/2008 to replace Miva description

-- with the GP Item description

SET @gpitemdesc = (SELECT ITEMDESC FROM IV00101 WHERE ITEMNMBR = @itemnmbr)

-- This line ends the addition by M Plonowski see change in lines below

--Execute SOP Invoice Line

exec taSOPLineivcinsert

@I_vSOPTYPE = @soptype,

@I_vSOPnumbe = @SOPNumbe,

@I_vCustnmbr = @custnmbr,

@I_vDocDate = @DocDate,

@I_vLocncode = @Locncode,

@I_vITEMNMBR = @ITEMNMBR,

@I_vunitprce = @unitprce,

@I_vXtndprce= @xtndprce,

@I_vQuantity = @quantity,

@I_vprclevel = @prclevel,

@I_vItemDesc = @gpitemdesc, -- replaced @itemdesc with @gpitemdesc

@I_vReqShipDate = @ReqShipDate,

--@I_vqtyfulfi = @qtyfulfi,

@I_vqtyshrtopt = @qtyshrtopt,

@I_vTaxSchid = @taxschiddetail,

-- Override the default ShipTo address to calculate taxes based on actual ShipTo address

@I_vPRSTADCD = @prstadcd,

@oErrString = @oErrString output,

@O_iErrorState = @O_iErrorState output

IF @O_iErrorState <> 0

Begin

select @ErrorItem = @ErrorItem + RTRIM(@ITEMNMBR) + ' '

--SELECT 'Detail Error' As ErrTpe, @ITEMNMBR As ItemNum, @O_iErrorState AS ErrorState, @oErrString As ErrorStr

End

end

Fetch NEXT FROM SOPDetail INTO

@OrderID_Detail, @ITEMNMBR, @unitprce, @xtndprce, @quantity, @itemdesc, @qtyfulfi, @uofm, @qtyshrtopt, @taxschidDetail

--Next 3 lines are standard syntax to close cursor

END

CLOSE SOPDetail

DEALLOCATE SOPDetail

--now populate error handling variables used for SOP detail

select @ErrorState2 = @O_iErrorState

select @ErrString2 = @oErrString

--reset variable as we will reuse it for SOP Header

select @oErrString = ''

-- SOP Invoice Header

select @O_iErrorState = 0

if (@O_iErrorState = 0)

begin

--Execute SOP Invoice Header

exec taSOPHdrIVCInsert

@I_vSOPType = @soptype,

@I_vDocid = @docid,

@I_vSOPNumbe = @sopnumbe,

@I_vTaxSchid = @taxschid,

@I_vShipmthd = @shipmthd,

@I_vLocncode = @locncode,

@I_vDocDate = @Docdate,

@I_vCustnmbr = @custnmbr,

@I_vSUBTOTAL = @SUBTOTAL,

@I_vDocamnt = @docamnt,

@I_vBACHNUMB = @BACHNUMB,

@I_vReqShipDate = @ReqShipDate,

@I_vPrbtadcd = @prbtadcd,

@I_vPrstadcd = @prstadcd,

@I_vOrdrdate = @ordrdate,

@I_vCreateDist = @createdist,

@I_vCreateTaxes = 1,

@I_vDEFTAXSCHDS = 0,--/1,

@I_vPrcLevel = @Prclevel,

@oErrString = @oErrString output,

@O_iErrorState = @O_iErrorState output

end

--SELECT 'Header Error' As ErrTpe, @O_iErrorState AS ErrorState, @oErrString As ErrorStr

select @ErrorState3 = @O_iErrorState

select @ErrString3 = @oErrString

select @oErrString = ''

-- Reset the Customer's taxschedules to their beginning status

-- New Tax Schedule logic added by M Plonowski on 10/11

IF @taxschid IN ('TX','CO')

Begin -- Update the customer's tax schedule to the appropriate state

IF NOT (@TAXSCHID1 IS NULL)

Begin -- Update Customer Master taxscheduleID

UPDATE RM00101

SET TAXSCHID = @TAXSCHID1

WHERE CUSTNMBR = @custnmbr AND ADRSCODE = @prstadcd

End -- Update Customer Master taxscheduleID

UPDATE RM00102

SET TAXSCHID = @TAXSCHID2

WHERE CUSTNMBR = @custnmbr AND ADRSCODE = @prstadcd

End -- Update the customer's tax schedule to the appropriate state

-- End of Tax Schedule logic added by M Plonowski on 10/11

--Processing is done. Let's begin the error handling. This first set of code will determine

-- whether a transaction should be posted to Great Plains or not

if @O_iErrorState <> 0 or @ErrorState2 <>0 or @ErrorState3 <> 0

begin

rollback

end

else

Commit

if @O_iErrorState <> 0 or @ErrorState2 <>0 or @ErrorState3 <> 0

begin

Declare @errordesc nvarchar(100)

Set @errorDesc = (select errordesc from dynamics.dbo.taerrorcode where errorcode = @errorState3)

Insert into ICB_SOPErrors (Date, SOPNumber,EconnectErrorNumber,Econnecterrormessage) values (Getdate(),@sopnumbe, @errorstate3,@errordesc )

End

--Code is done - loop to the next record in the cursor

Fetch NEXT FROM SOPHeader

INTO

@soptype, @docid, @orderID, @taxschid, @shipmthd, @locncode, @docdate, @taxamnt, @freight, @miscamnt, @custnmbr,

@subtotal, @docamnt, @bachnumb, @prbtadcd, @prstadcd, @ordrdate, @createdist, @prclevel, @cmmttext

--Next 3 lines standard syntax to close SOPHeader cursor

End

CLOSE SOPHeader

DEALLOCATE SOPHeader

--Final end statement for the stored procedure

End