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 |
Dynamics GP >