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