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