ddtaPOPTransactionTypeWrapper

This article details a series of SQL scripts that will allow you to create Dynamics GP POs using the stored procedures that power eConnect. 

The steps involved:

  • Create staging tables that will hold the PO header and line data
  • Populate the tables
  • Call the ddtaPOPTransactionTypeWrapper stored procedure.
  • This proc callse two other procs: ddtaPOHdrWrapper and ddtaPOLineWrapper

All the calls are wrapped in error trapping, if any errors occur all the data is reversed and the error is reported.

Related Articles

... and you 'll find more on the eConnect Menu

First, let's create the tables

if exists (select 1 from INFORMATION_SCHEMA.TABLES where table_name = 'DDtaPOHdr' ) BEGIN
    drop table DDtaPOHdr
end
  
GO
  
CREATE TABLE DDtaPOHdr
(
RowID int NOT NULL IDENTITY (1, 1),
errorText varchar(max),
POTYPE smallint ,
PONUMBER char (17) ,
VENDORID char (15) ,
VENDNAME char (64) ,
DOCDATE datetime ,
BUYERID char (15) ,
ALLOWSOCMTS tinyint ,
TRDISAMT numeric (19,5) ,
FRTAMNT numeric (19,5) ,
MSCCHAMT numeric (19,5) ,
TAXAMNT numeric (19,5) ,
SUBTOTAL numeric (19,5) ,
CUSTNMBR char (15) ,
PRSTADCD char (15) ,
CMPNYNAM char (64) ,
CONTACT char (60) ,
ADDRESS1 char (60) ,
ADDRESS2 char (60) ,
ADDRESS3 char (60) ,
CITY char (35) ,
STATE char (29) ,
ZIPCODE char (10) ,
CCode char (6) ,
COUNTRY char (60) ,
PHONE1 char (21) ,
PHONE2 char (21) ,
PHONE3 char (21) ,
FAX char (21) ,
Print_Phone_NumberGB smallint ,
VADCDPAD char (15) ,
PURCHCMPNYNAM char (64) ,
PURCHCONTACT char (60) ,
PURCHADDRESS1 char (60) ,
PURCHADDRESS2 char (60) ,
PURCHADDRESS3 char (60) ,
PURCHCITY char (35) ,
PURCHSTATE char (29) ,
PURCHZIPCODE char (10) ,
PURCHCCode char (6) ,
PURCHCOUNTRY char (60) ,
PURCHPHONE1 char (21) ,
PURCHPHONE2 char (21) ,
PURCHPHONE3 char (21) ,
PURCHFAX char (21) ,
PRBTADCD char (15) ,
SHIPMTHD char (15) ,
PYMTRMID char (20) ,
DSCPCTAM numeric (19,2) ,
DSCDLRAM numeric (19,5) ,
DISAMTAV numeric (19,5) ,
DUEDATE datetime ,
DISCDATE datetime ,
TXRGNNUM char (25) ,
CONFIRM1 char (20) ,
COMMNTID char (15) ,
COMMENT_1 char (50) ,
COMMENT_2 char (50) ,
COMMENT_3 char (50) ,
COMMENT_4 char (50) ,
HOLD tinyint ,
TAXSCHID char (15) ,
Purchase_Freight_Taxable smallint ,
Purchase_Misc_Taxable smallint ,
FRTSCHID char (15) ,
MSCSCHID char (15) ,
FRTTXAMT numeric (19,5) ,
MSCTXAMT numeric (19,5) ,
BCKTXAMT numeric (19,5) ,
BackoutFreightTaxAmt numeric (19,5) ,
BackoutMiscTaxAmt numeric (19,5) ,
BackoutTradeDiscTax numeric (19,5) ,
USINGHEADERLEVELTAXES smallint ,
CURNCYID char (15) ,
XCHGRATE numeric (19,7) ,
RATETPID char (15) ,
EXPNDATE datetime ,
EXCHDATE datetime ,
EXGTBDSC char (30) ,
EXTBLSRC char (50) ,
RATEEXPR smallint ,
DYSTINCR smallint ,
RATEVARC numeric (19,7) ,
TRXDTDEF smallint ,
RTCLCMTD smallint ,
PRVDSLMT smallint ,
DATELMTS smallint ,
TIME1 datetime ,
USERID char (15) ,
POSTATUS smallint ,
CMMTTEXT varchar (500) ,
PRMDATE datetime ,
PRMSHPDTE datetime ,
REQDATE datetime ,
CONTENDDTE datetime ,
CNTRLBLKTBY smallint ,
REQTNDT datetime ,
UpdateIfExists smallint ,
NOTETEXT varchar (8000) ,
RequesterTrx smallint ,
USRDEFND1 char (50) ,
USRDEFND2 char (50) ,
USRDEFND3 char (50) ,
USRDEFND4 varchar (8000) ,
USRDEFND5 varchar (8000)
)  ON [PRIMARY]
GO
ALTER TABLE DDtaPOHdr ADD CONSTRAINT
    PK_taPOHdr PRIMARY KEY CLUSTERED
    (
    RowID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
if exists (select 1 from INFORMATION_SCHEMA.TABLES where table_name = 'DDtaPOLine' ) BEGIN
    drop table DDtaPOLine
end
  
GO
  
CREATE TABLE DDtaPOLine
(
RowID int NOT NULL IDENTITY (1, 1),
HeaderID int NOT NULL ,
POTYPE smallint ,
PONUMBER char (17) ,
DOCDATE datetime ,
VENDORID char (15) ,
LOCNCODE char (10) ,
VNDITNUM char (30) ,
ITEMNMBR char (30) ,
QUANTITY numeric (19,5) ,
QTYCANCE numeric (19,5) ,
FREEONBOARD smallint ,
REQSTDBY char (20) ,
COMMNTID char (15) ,
COMMENT_1 char (50) ,
COMMENT_2 char (50) ,
COMMENT_3 char (50) ,
COMMENT_4 char (50) ,
REQDATE datetime ,
RELEASEBYDATE datetime ,
PRMDATE datetime ,
PRMSHPDTE datetime ,
NONINVEN smallint ,
IVIVINDX int ,
InventoryAccount varchar (75) ,
ITEMDESC char (100) ,
UNITCOST numeric (19,5) ,
VNDITDSC char (100) ,
UOFM char (8) ,
Purchase_IV_Item_Taxable smallint ,
Purchase_Item_Tax_Schedu char (15) ,
Purchase_Site_Tax_Schedu char (15) ,
BSIVCTTL smallint ,
TAXAMNT numeric (19,5) ,
BCKTXAMT numeric (19,5) ,
Landed_Cost_Group_ID char (15) ,
PLNNDSPPLID smallint ,
SHIPMTHD char (15) ,
BackoutTradeDiscTax numeric (19,5) ,
POLNESTA smallint ,
CMMTTEXT varchar (500) ,
ORD int ,
CUSTNMBR char (15) ,
ADRSCODE char (15) ,
CMPNYNAM char (64) ,
CONTACT char (60) ,
ADDRESS1 char (60) ,
ADDRESS2 char (60) ,
ADDRESS3 char (60) ,
CITY char (35) ,
STATE char (29) ,
ZIPCODE char (10) ,
CCode char (6) ,
COUNTRY char (60) ,
PHONE1 char (21) ,
PHONE2 char (21) ,
PHONE3 char (21) ,
FAX char (21) ,
Print_Phone_NumberGB smallint ,
CURNCYID char (15) ,
ProjNum char (15) ,
CostCatID char (15) ,
LineNumber int ,
UpdateIfExists smallint ,
NOTETEXT varchar (8000) ,
RequesterTrx smallint ,
USRDEFND1 char (50) ,
USRDEFND2 char (50) ,
USRDEFND3 char (50) ,
USRDEFND4 varchar (8000) ,
USRDEFND5 varchar (8000)
)  ON [PRIMARY]
GO
ALTER TABLE DDtaPOLine ADD CONSTRAINT
    PK_taPOLine PRIMARY KEY CLUSTERED
    (
    RowID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Insert the data

--initialize
DECLARE @dt date = GETDATE()
DECLARE @HeaderID int
 
INSERT INTO DDtaPOHdr(POTYPE,VENDORID,DOCDATE)
    VALUES           (     1,'TEST'  , @dt  )
 
SELECT @HeaderID = SCOPE_IDENTITY()
 
 
INSERT INTO DDtaPOLine ( headerid , POTYPE, DOCDATE, VENDORID, LOCNCODE, ITEMNMBR          , QUANTITY, UOFM  , ORD  , LineNumber)
    VALUES             ( @headerid, 1     , @dt    , 'TEST'  , '001'   , 'TEST~STEVE~TEST' , 1500    , 'EACH', 16384, 1)
INSERT INTO DDtaPOLine ( headerid , POTYPE, DOCDATE, VENDORID, LOCNCODE, ITEMNMBR          , QUANTITY, UOFM  , ORD  , LineNumber)
    VALUES             ( @HeaderID, 1     , @dt    , 'TEST'  , '001'   , 'TEST~STEVE~TEST' , 1500    , 'EACH', 32768, 2)
 
-- DELETE DDtaPOLine DELETE DDtaPOHdr
DECLARE @PONumber VARCHAR(21)
 
EXEC fp_taPOPTransactionTypeWrapper @headerid, @PONumber OUT
SELECT @PONumber

Finally, here are the three stored procedures

IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = N'fp_taPOPTransactionTypeWrapper'
        AND type = 'P')
    DROP PROCEDURE fp_taPOPTransactionTypeWrapper
GO
  
CREATE PROCEDURE fp_taPOPTransactionTypeWrapper
 
--we expect there to be lines in DDtaPOHdr and DDtaPOLine
--the HeaderID in DDtaPOHdr links to the RowID in DDtaPOLine
--this proc will process one document and return any errors
 
@HeaderID INT,
@PONumber VARCHAR(21) OUT
  
AS
 
BEGIN TRY
    BEGIN TRAN
        -- be carefull not to exit this or the transaction will not get committed, you'll lock the table up
         
 
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
        SET NOCOUNT ON
 
        DECLARE @O_iErrorState int
        DECLARE @oErrString varchar(255)
        DECLARE @DOCID VARCHAR(21)
        DECLARE @LineID INT
        DECLARE @O_vPONUMBER VARCHAR(17)
 
 
        --we allow the sopnumbe to be null. if it is, we'll fill it in
        EXEC taGetPONextNumber
            @I_vInc_Dec = 0,                       -- tinyint
            @O_vPONUMBER = @O_vPONUMBER OUTPUT,    -- varchar(17)
            @O_iErrorState = @O_iErrorState OUTPUT -- int
 
 
        IF @O_iErrorState > 0 BEGIN
            SElect @oErrString = ErrorDesc
                FROM dynamics..taerrorcode ec
                WHERE ec.ErrorCode = @O_iErrorState
            PRINT 'fp_taPOPTransactionTypeWrapper An error occurred getting the next PO number'
            RETURN 1
        end
 
        SELECT @PONumber = @O_vPONUMBER
        UPDATE DDtaPOHdr SET PONUMBER = @O_vPONUMBER WHERE rowid = @HeaderID
        UPDATE DDtaPOLine SET PONUMBER = @O_vPONUMBER WHERE HeaderID = @HeaderID
 
 
        --=========================================================================================
        -- loop through the lines and send them
        --=========================================================================================
        DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
            select RowID
                from DDtaPOLine  t
                WHERE HeaderID = @HeaderID
           
        OPEN curName
        WHILE 1=1
        BEGIN
            FETCH NEXT FROM curName INTO @LineID
            if @@fetch_status <> 0 begin
                break
            END
             
            EXEC fp_taPOLineWrapper @LineID
            IF @O_iErrorState > 0 BEGIN
                SElect @oErrString = ec.ErrorDesc
                    FROM dynamics..taerrorcode ec
                    WHERE ec.ErrorCode = @O_iErrorState
                RETURN 1
            end
        END
        CLOSE curName
        DEALLOCATE curName
 
        --=========================================================================================
        -- send the header
        --=========================================================================================
        EXEC fp_tapohdrwrapper @HeaderID
        IF @O_iErrorState > 0 BEGIN
            PRINT 'fp_taPOPTransactionTypeWrapper fp_taSopHdrIvcInsertWrapper error'
            SElect @oErrString = ErrorDesc
                FROM dynamics..taerrorcode ec
                WHERE ec.ErrorCode = @O_iErrorState
            RETURN 1
        end
 
 
        DELETE DDtaPOHdr WHERE RowID = @HeaderID
        DELETE DDtaPOLine WHERE @HeaderID = @HeaderID
 
    COMMIT TRAN
END TRY
BEGIN CATCH
    --find out what went wrong
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
  
    --roll everything back
    ROLLBACK TRAN
END CATCH
 
GO
Grant EXEC on fp_taPOPTransactionTypeWrapper to public
 
--  sp_sps 'r'

 

IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'fp_taPOHdrWrapper'
       AND    type = 'P')
    DROP PROCEDURE fp_taPOHdrWrapper
GO
    
CREATE PROCEDURE fp_taPOHdrWrapper
    
-- fp_taPOHdrWrapper '1','PO0001','4/12/2027','ADVANCED0001'
   
@HeaderID int
   
AS
    
set transaction isolation level read uncommitted
 
DECLARE @RC int
DECLARE @I_vPOTYPE smallint
DECLARE @I_vPONUMBER char(17)
DECLARE @I_vVENDORID char(15)
DECLARE @I_vVENDNAME char(64)
DECLARE @I_vDOCDATE datetime
DECLARE @I_vBUYERID char(15)
DECLARE @I_vALLOWSOCMTS tinyint
DECLARE @I_vTRDISAMT numeric(19,5)
DECLARE @I_vFRTAMNT numeric(19,5)
DECLARE @I_vMSCCHAMT numeric(19,5)
DECLARE @I_vTAXAMNT numeric(19,5)
DECLARE @I_vSUBTOTAL numeric(19,5)
DECLARE @I_vCUSTNMBR char(15)
DECLARE @I_vPRSTADCD char(15)
DECLARE @I_vCMPNYNAM char(64)
DECLARE @I_vCONTACT char(60)
DECLARE @I_vADDRESS1 char(60)
DECLARE @I_vADDRESS2 char(60)
DECLARE @I_vADDRESS3 char(60)
DECLARE @I_vCITY char(35)
DECLARE @I_vSTATE char(29)
DECLARE @I_vZIPCODE char(10)
DECLARE @I_vCCode char(6)
DECLARE @I_vCOUNTRY char(60)
DECLARE @I_vPHONE1 char(21)
DECLARE @I_vPHONE2 char(21)
DECLARE @I_vPHONE3 char(21)
DECLARE @I_vFAX char(21)
DECLARE @I_vPrint_Phone_NumberGB smallint
DECLARE @I_vVADCDPAD char(15)
DECLARE @I_vPURCHCMPNYNAM char(64)
DECLARE @I_vPURCHCONTACT char(60)
DECLARE @I_vPURCHADDRESS1 char(60)
DECLARE @I_vPURCHADDRESS2 char(60)
DECLARE @I_vPURCHADDRESS3 char(60)
DECLARE @I_vPURCHCITY char(35)
DECLARE @I_vPURCHSTATE char(29)
DECLARE @I_vPURCHZIPCODE char(10)
DECLARE @I_vPURCHCCode char(6)
DECLARE @I_vPURCHCOUNTRY char(60)
DECLARE @I_vPURCHPHONE1 char(21)
DECLARE @I_vPURCHPHONE2 char(21)
DECLARE @I_vPURCHPHONE3 char(21)
DECLARE @I_vPURCHFAX char(21)
DECLARE @I_vPRBTADCD char(15)
DECLARE @I_vSHIPMTHD char(15)
DECLARE @I_vPYMTRMID char(20)
DECLARE @I_vDSCPCTAM numeric(19,2)
DECLARE @I_vDSCDLRAM numeric(19,5)
DECLARE @I_vDISAMTAV numeric(19,5)
DECLARE @I_vDUEDATE datetime
DECLARE @I_vDISCDATE datetime
DECLARE @I_vTXRGNNUM char(25)
DECLARE @I_vCONFIRM1 char(20)
DECLARE @I_vCOMMNTID char(15)
DECLARE @I_vCOMMENT_1 char(50)
DECLARE @I_vCOMMENT_2 char(50)
DECLARE @I_vCOMMENT_3 char(50)
DECLARE @I_vCOMMENT_4 char(50)
DECLARE @I_vHOLD tinyint
DECLARE @I_vTAXSCHID char(15)
DECLARE @I_vPurchase_Freight_Taxable smallint
DECLARE @I_vPurchase_Misc_Taxable smallint
DECLARE @I_vFRTSCHID char(15)
DECLARE @I_vMSCSCHID char(15)
DECLARE @I_vFRTTXAMT numeric(19,5)
DECLARE @I_vMSCTXAMT numeric(19,5)
DECLARE @I_vBCKTXAMT numeric(19,5)
DECLARE @I_vBackoutFreightTaxAmt numeric(19,5)
DECLARE @I_vBackoutMiscTaxAmt numeric(19,5)
DECLARE @I_vBackoutTradeDiscTax numeric(19,5)
DECLARE @I_vUSINGHEADERLEVELTAXES smallint
DECLARE @I_vCURNCYID char(15)
DECLARE @I_vXCHGRATE numeric(19,7)
DECLARE @I_vRATETPID char(15)
DECLARE @I_vEXPNDATE datetime
DECLARE @I_vEXCHDATE datetime
DECLARE @I_vEXGTBDSC char(30)
DECLARE @I_vEXTBLSRC char(50)
DECLARE @I_vRATEEXPR smallint
DECLARE @I_vDYSTINCR smallint
DECLARE @I_vRATEVARC numeric(19,7)
DECLARE @I_vTRXDTDEF smallint
DECLARE @I_vRTCLCMTD smallint
DECLARE @I_vPRVDSLMT smallint
DECLARE @I_vDATELMTS smallint
DECLARE @I_vTIME1 datetime
DECLARE @I_vUSERID char(15)
DECLARE @I_vPOSTATUS smallint
DECLARE @I_vCMMTTEXT varchar(500)
DECLARE @I_vPRMDATE datetime
DECLARE @I_vPRMSHPDTE datetime
DECLARE @I_vREQDATE datetime
DECLARE @I_vCONTENDDTE datetime
DECLARE @I_vCNTRLBLKTBY smallint
DECLARE @I_vREQTNDT datetime
DECLARE @I_vUpdateIfExists smallint
DECLARE @I_vNOTETEXT varchar(8000)
DECLARE @I_vRequesterTrx smallint
DECLARE @I_vUSRDEFND1 char(50)
DECLARE @I_vUSRDEFND2 char(50)
DECLARE @I_vUSRDEFND3 char(50)
DECLARE @I_vUSRDEFND4 varchar(8000)
DECLARE @I_vUSRDEFND5 varchar(8000)
DECLARE @O_iErrorState int
DECLARE @oErrString varchar(255)
DECLARE @ErrorDesc VARCHAR(MAX)
 
 
 
select
        @I_vPOTYPE                      = po.POTYPE
        ,@I_vPONUMBER                   = po.PONUMBER
        ,@I_vVENDORID                   = po.VENDORID
        ,@I_vVENDNAME                   = po.VENDNAME
        ,@I_vDOCDATE                    = po.DOCDATE
        ,@I_vBUYERID                    = ISNULL(po.BUYERID,'')
        ,@I_vALLOWSOCMTS                = po.ALLOWSOCMTS
        ,@I_vTRDISAMT                   = po.TRDISAMT
        ,@I_vFRTAMNT                    = isnull(po.FRTAMNT,0)
        ,@I_vMSCCHAMT                   = isnull(po.MSCCHAMT,0)
        ,@I_vTAXAMNT                    = po.TAXAMNT
        ,@I_vSUBTOTAL                   = po.SUBTOTAL
        ,@I_vCUSTNMBR                   = ISNULL(po.CUSTNMBR,'')
        ,@I_vPRSTADCD                   = po.PRSTADCD
        ,@I_vCMPNYNAM                   = po.CMPNYNAM
        ,@I_vCONTACT                    = po.CONTACT
        ,@I_vADDRESS1                   = po.ADDRESS1
        ,@I_vADDRESS2                   = po.ADDRESS2
        ,@I_vADDRESS3                   = po.ADDRESS3
        ,@I_vCITY                       = po.CITY
        ,@I_vSTATE                      = po.STATE
        ,@I_vZIPCODE                    = po.ZIPCODE
        ,@I_vCCode                      = po.CCode
        ,@I_vCOUNTRY                    = po.COUNTRY
        ,@I_vPHONE1                     = po.PHONE1
        ,@I_vPHONE2                     = po.PHONE2
        ,@I_vPHONE3                     = po.PHONE3
        ,@I_vFAX                        = po.FAX
        ,@I_vPrint_Phone_NumberGB       = po.Print_Phone_NumberGB
        ,@I_vVADCDPAD                   = po.VADCDPAD
        ,@I_vPURCHCMPNYNAM              = po.PURCHCMPNYNAM
        ,@I_vPURCHCONTACT               = po.PURCHCONTACT
        ,@I_vPURCHADDRESS1              = po.PURCHADDRESS1
        ,@I_vPURCHADDRESS2              = po.PURCHADDRESS2
        ,@I_vPURCHADDRESS3              = po.PURCHADDRESS3
        ,@I_vPURCHCITY                  = po.PURCHCITY
        ,@I_vPURCHSTATE                 = po.PURCHSTATE
        ,@I_vPURCHZIPCODE               = po.PURCHZIPCODE
        ,@I_vPURCHCCode                 = po.PURCHCCode
        ,@I_vPURCHCOUNTRY               = po.PURCHCOUNTRY
        ,@I_vPURCHPHONE1                = po.PURCHPHONE1
        ,@I_vPURCHPHONE2                = po.PURCHPHONE2
        ,@I_vPURCHPHONE3                = po.PURCHPHONE3
        ,@I_vPURCHFAX                   = po.PURCHFAX
        ,@I_vPRBTADCD                   = po.PRBTADCD
        ,@I_vSHIPMTHD                   = po.SHIPMTHD
        ,@I_vPYMTRMID                   = po.PYMTRMID
        ,@I_vDSCPCTAM                   = po.DSCPCTAM
        ,@I_vDSCDLRAM                   = po.DSCDLRAM
        ,@I_vDISAMTAV                   = po.DISAMTAV
        ,@I_vDUEDATE                    = po.DUEDATE
        ,@I_vDISCDATE                   = po.DISCDATE
        ,@I_vTXRGNNUM                   = isnull(po.TXRGNNUM ,'')
        ,@I_vCONFIRM1                   = isnull(po.CONFIRM1 ,'')
        ,@I_vCOMMNTID                   = isnull(po.COMMNTID ,'')
        ,@I_vCOMMENT_1                  = isnull(po.COMMENT_1,'')
        ,@I_vCOMMENT_2                  = isnull(po.COMMENT_2,'')
        ,@I_vCOMMENT_3                  = isnull(po.COMMENT_3,'')
        ,@I_vCOMMENT_4                  = isnull(po.COMMENT_4,'')
        ,@I_vHOLD                       = isnull(po.HOLD,'')
        ,@I_vTAXSCHID                   = po.TAXSCHID
        ,@I_vPurchase_Freight_Taxable   = po.Purchase_Freight_Taxable
        ,@I_vPurchase_Misc_Taxable      = po.Purchase_Misc_Taxable
        ,@I_vFRTSCHID                   = po.FRTSCHID
        ,@I_vMSCSCHID                   = po.MSCSCHID
        ,@I_vFRTTXAMT                   = po.FRTTXAMT
        ,@I_vMSCTXAMT                   = po.MSCTXAMT
        ,@I_vBCKTXAMT                   = po.BCKTXAMT
        ,@I_vBackoutFreightTaxAmt       = po.BackoutFreightTaxAmt
        ,@I_vBackoutMiscTaxAmt          = po.BackoutMiscTaxAmt
        ,@I_vBackoutTradeDiscTax        = po.BackoutTradeDiscTax
        ,@I_vUSINGHEADERLEVELTAXES      = po.USINGHEADERLEVELTAXES
        ,@I_vCURNCYID                   = po.CURNCYID
        ,@I_vXCHGRATE                   = ISNULL(po.XCHGRATE,0)
        ,@I_vRATETPID                   = po.RATETPID
        ,@I_vEXPNDATE                   = po.EXPNDATE
        ,@I_vEXCHDATE                   = po.EXCHDATE
        ,@I_vEXGTBDSC                   = po.EXGTBDSC
        ,@I_vEXTBLSRC                   = po.EXTBLSRC
        ,@I_vRATEEXPR                   = po.RATEEXPR
        ,@I_vDYSTINCR                   = po.DYSTINCR
        ,@I_vRATEVARC                   = po.RATEVARC
        ,@I_vTRXDTDEF                   = po.TRXDTDEF
        ,@I_vRTCLCMTD                   = po.RTCLCMTD
        ,@I_vPRVDSLMT                   = po.PRVDSLMT
        ,@I_vDATELMTS                   = po.DATELMTS
        ,@I_vTIME1                      = po.TIME1
        ,@I_vUSERID                     = po.USERID
        ,@I_vPOSTATUS                   = ISNULL(po.POSTATUS,1)
        ,@I_vCMMTTEXT                   = ISNULL(po.CMMTTEXT,'')
        ,@I_vPRMDATE                    = po.PRMDATE
        ,@I_vPRMSHPDTE                  = po.PRMSHPDTE
        ,@I_vREQDATE                    = po.REQDATE
        ,@I_vCONTENDDTE                 = isnull(po.CONTENDDTE,'1/1/1900')
        ,@I_vCNTRLBLKTBY                = isnull(po.CNTRLBLKTBY,'')
        ,@I_vREQTNDT                    = po.REQTNDT
        ,@I_vUpdateIfExists             = po.UpdateIfExists
        ,@I_vNOTETEXT                   = po.NOTETEXT
        ,@I_vRequesterTrx               = po.RequesterTrx
        ,@I_vUSRDEFND1                  = po.USRDEFND1
        ,@I_vUSRDEFND2                  = po.USRDEFND2
        ,@I_vUSRDEFND3                  = po.USRDEFND3
        ,@I_vUSRDEFND4                  = po.USRDEFND4
        ,@I_vUSRDEFND5                  = po.USRDEFND5
    FROM DDtaPOHdr po
    WHERE rowid = @HeaderID
 
 
 
EXECUTE @RC = [dbo].[taPoHdr]
   @I_vPOTYPE
  ,@I_vPONUMBER
  ,@I_vVENDORID
  ,@I_vVENDNAME
  ,@I_vDOCDATE
  ,@I_vBUYERID
  ,@I_vALLOWSOCMTS
  ,@I_vTRDISAMT
  ,@I_vFRTAMNT
  ,@I_vMSCCHAMT
  ,@I_vTAXAMNT
  ,@I_vSUBTOTAL
  ,@I_vCUSTNMBR
  ,@I_vPRSTADCD
  ,@I_vCMPNYNAM
  ,@I_vCONTACT
  ,@I_vADDRESS1
  ,@I_vADDRESS2
  ,@I_vADDRESS3
  ,@I_vCITY
  ,@I_vSTATE
  ,@I_vZIPCODE
  ,@I_vCCode
  ,@I_vCOUNTRY
  ,@I_vPHONE1
  ,@I_vPHONE2
  ,@I_vPHONE3
  ,@I_vFAX
  ,@I_vPrint_Phone_NumberGB
  ,@I_vVADCDPAD
  ,@I_vPURCHCMPNYNAM
  ,@I_vPURCHCONTACT
  ,@I_vPURCHADDRESS1
  ,@I_vPURCHADDRESS2
  ,@I_vPURCHADDRESS3
  ,@I_vPURCHCITY
  ,@I_vPURCHSTATE
  ,@I_vPURCHZIPCODE
  ,@I_vPURCHCCode
  ,@I_vPURCHCOUNTRY
  ,@I_vPURCHPHONE1
  ,@I_vPURCHPHONE2
  ,@I_vPURCHPHONE3
  ,@I_vPURCHFAX
  ,@I_vPRBTADCD
  ,@I_vSHIPMTHD
  ,@I_vPYMTRMID
  ,@I_vDSCPCTAM
  ,@I_vDSCDLRAM
  ,@I_vDISAMTAV
  ,@I_vDUEDATE
  ,@I_vDISCDATE
  ,@I_vTXRGNNUM
  ,@I_vCONFIRM1
  ,@I_vCOMMNTID
  ,@I_vCOMMENT_1
  ,@I_vCOMMENT_2
  ,@I_vCOMMENT_3
  ,@I_vCOMMENT_4
  ,@I_vHOLD
  ,@I_vTAXSCHID
  ,@I_vPurchase_Freight_Taxable
  ,@I_vPurchase_Misc_Taxable
  ,@I_vFRTSCHID
  ,@I_vMSCSCHID
  ,@I_vFRTTXAMT
  ,@I_vMSCTXAMT
  ,@I_vBCKTXAMT
  ,@I_vBackoutFreightTaxAmt
  ,@I_vBackoutMiscTaxAmt
  ,@I_vBackoutTradeDiscTax
  ,@I_vUSINGHEADERLEVELTAXES
  ,@I_vCURNCYID
  ,@I_vXCHGRATE
  ,@I_vRATETPID
  ,@I_vEXPNDATE
  ,@I_vEXCHDATE
  ,@I_vEXGTBDSC
  ,@I_vEXTBLSRC
  ,@I_vRATEEXPR
  ,@I_vDYSTINCR
  ,@I_vRATEVARC
  ,@I_vTRXDTDEF
  ,@I_vRTCLCMTD
  ,@I_vPRVDSLMT
  ,@I_vDATELMTS
  ,@I_vTIME1
  ,@I_vUSERID
  ,@I_vPOSTATUS
  ,@I_vCMMTTEXT
  ,@I_vPRMDATE
  ,@I_vPRMSHPDTE
  ,@I_vREQDATE
  ,@I_vCONTENDDTE
  ,@I_vCNTRLBLKTBY
  ,@I_vREQTNDT
  ,@I_vUpdateIfExists
  ,@I_vNOTETEXT
  ,@I_vRequesterTrx
  ,@I_vUSRDEFND1
  ,@I_vUSRDEFND2
  ,@I_vUSRDEFND3
  ,@I_vUSRDEFND4
  ,@I_vUSRDEFND5
  ,@O_iErrorState OUTPUT
  ,@oErrString OUTPUT
 
 
IF @O_iErrorState > 0 BEGIN
    PRINT 'fp_taPOHdrWrapper error'
    --SELECT       @I_vPOTYPE ,@I_vPONUMBER ,@I_vVENDORID ,@I_vDOCDATE ,@I_vBUYERID ,@I_vFRTAMNT ,@I_vMSCCHAMT ,@I_vCUSTNMBR ,@I_vTXRGNNUM ,@I_vCONFIRM1 ,@I_vCOMMNTID ,@I_vCOMMENT_1 ,@I_vCOMMENT_2 ,@I_vCOMMENT_3 ,@I_vCOMMENT_4 ,@I_vHOLD ,@I_vPOSTATUS ,@I_vCMMTTEXT ,@I_vCONTENDDTE ,@I_vCNTRLBLKTBY
 
    SELECT @ErrorDesc = 'po hdr error ' + CONVERT(VARCHAR(10),@O_iErrorState) + '  ' +  tec.ErrorDesc
        FROM dynamics..taErrorCode tec
        WHERE tec.ErrorCode = @O_iErrorState
     
    raiserror(@ErrorDesc, 16,2)
END ELSE BEGIN
    SELECT @ErrorDesc = ''
end
  -- fp_taPOLineWrapper 3
 
 
 
    
    
GO
    
grant exec on fp_taPOHdrWrapper to public

 

IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N'fp_taPOLineWrapper'
       AND    type = 'P')
    DROP PROCEDURE fp_taPOLineWrapper
GO
    
CREATE PROCEDURE fp_taPOLineWrapper
    
-- fp_taPOLineWrapper 3
   
@LineID int
   
AS
    
set transaction isolation level read uncommitted
   
 
DECLARE @RC int
DECLARE @I_vPOTYPE smallint
DECLARE @I_vPONUMBER char(17)
DECLARE @I_vDOCDATE datetime
DECLARE @I_vVENDORID char(15)
DECLARE @I_vLOCNCODE char(10)
DECLARE @I_vVNDITNUM char(30)
DECLARE @I_vITEMNMBR char(30)
DECLARE @I_vQUANTITY numeric(19,5)
DECLARE @I_vQTYCANCE numeric(19,5)
DECLARE @I_vFREEONBOARD smallint
DECLARE @I_vREQSTDBY char(20)
DECLARE @I_vCOMMNTID char(15)
DECLARE @I_vCOMMENT_1 char(50)
DECLARE @I_vCOMMENT_2 char(50)
DECLARE @I_vCOMMENT_3 char(50)
DECLARE @I_vCOMMENT_4 char(50)
DECLARE @I_vREQDATE datetime
DECLARE @I_vRELEASEBYDATE datetime
DECLARE @I_vPRMDATE datetime
DECLARE @I_vPRMSHPDTE datetime
DECLARE @I_vNONINVEN smallint
DECLARE @I_vIVIVINDX int
DECLARE @I_vInventoryAccount varchar(75)
DECLARE @I_vITEMDESC char(100)
DECLARE @I_vUNITCOST numeric(19,5)
DECLARE @I_vVNDITDSC char(100)
DECLARE @I_vUOFM char(8)
DECLARE @I_vPurchase_IV_Item_Taxable smallint
DECLARE @I_vPurchase_Item_Tax_Schedu char(15)
DECLARE @I_vPurchase_Site_Tax_Schedu char(15)
DECLARE @I_vBSIVCTTL smallint
DECLARE @I_vTAXAMNT numeric(19,5)
DECLARE @I_vBCKTXAMT numeric(19,5)
DECLARE @I_vLanded_Cost_Group_ID char(15)
DECLARE @I_vPLNNDSPPLID smallint
DECLARE @I_vSHIPMTHD char(15)
DECLARE @I_vBackoutTradeDiscTax numeric(19,5)
DECLARE @I_vPOLNESTA smallint
DECLARE @I_vCMMTTEXT varchar(500)
DECLARE @I_vORD int
DECLARE @I_vCUSTNMBR char(15)
DECLARE @I_vADRSCODE char(15)
DECLARE @I_vCMPNYNAM char(64)
DECLARE @I_vCONTACT char(60)
DECLARE @I_vADDRESS1 char(60)
DECLARE @I_vADDRESS2 char(60)
DECLARE @I_vADDRESS3 char(60)
DECLARE @I_vCITY char(35)
DECLARE @I_vSTATE char(29)
DECLARE @I_vZIPCODE char(10)
DECLARE @I_vCCode char(6)
DECLARE @I_vCOUNTRY char(60)
DECLARE @I_vPHONE1 char(21)
DECLARE @I_vPHONE2 char(21)
DECLARE @I_vPHONE3 char(21)
DECLARE @I_vFAX char(21)
DECLARE @I_vPrint_Phone_NumberGB smallint
DECLARE @I_vCURNCYID char(15)
DECLARE @I_vProjNum char(15)
DECLARE @I_vCostCatID char(15)
DECLARE @I_vLineNumber int
DECLARE @I_vUpdateIfExists smallint
DECLARE @I_vNOTETEXT varchar(8000)
DECLARE @I_vRequesterTrx smallint
DECLARE @I_vUSRDEFND1 char(50)
DECLARE @I_vUSRDEFND2 char(50)
DECLARE @I_vUSRDEFND3 char(50)
DECLARE @I_vUSRDEFND4 varchar(8000)
DECLARE @I_vUSRDEFND5 varchar(8000)
DECLARE @O_iErrorState int
DECLARE @oErrString varchar(255)
DECLARE @ErrorDesc VARCHAR(MAX)
 
SELECT
        @I_vPOTYPE                      = pol.POTYPE
        ,@I_vPONUMBER                   = pol.PONUMBER
        ,@I_vDOCDATE                    = pol.DOCDATE
        ,@I_vVENDORID                   = pol.VENDORID
        ,@I_vLOCNCODE                   = pol.LOCNCODE
        ,@I_vVNDITNUM                   = ISNULL(pol.VNDITNUM,'')
        ,@I_vITEMNMBR                   = pol.ITEMNMBR
        ,@I_vQUANTITY                   = pol.QUANTITY
        ,@I_vQTYCANCE                   = pol.QTYCANCE
        ,@I_vFREEONBOARD                = ISNULL(pol.FREEONBOARD,1)
        ,@I_vREQSTDBY                   = ISNULL(pol.REQSTDBY,'')
        ,@I_vCOMMNTID                   = ISNULL(pol.COMMNTID,'')
        ,@I_vCOMMENT_1                  = isnull(pol.COMMENT_1,'')
        ,@I_vCOMMENT_2                  = isnull(pol.COMMENT_2,'')
        ,@I_vCOMMENT_3                  = isnull(pol.COMMENT_3,'')
        ,@I_vCOMMENT_4                  = isnull(pol.COMMENT_4,'')
        ,@I_vREQDATE                    = isnull(pol.REQDATE      ,'1/1/1900')
        ,@I_vRELEASEBYDATE              = isnull(pol.RELEASEBYDATE,'1/1/1900')
        ,@I_vPRMDATE                    = isnull(pol.PRMDATE      ,'1/1/1900')
        ,@I_vPRMSHPDTE                  = isnull(pol.PRMSHPDTE    ,'1/1/1900')
        ,@I_vNONINVEN                   = isnull(pol.NONINVEN, 0)
        ,@I_vIVIVINDX                   = isnull(pol.IVIVINDX,0)
        ,@I_vInventoryAccount           = isnull(pol.InventoryAccount,'')
        ,@I_vITEMDESC                   = isnull(pol.ITEMDESC,'')
        ,@I_vUNITCOST                   = pol.UNITCOST
        ,@I_vVNDITDSC                   = isnull(pol.VNDITDSC,'')
        ,@I_vUOFM                       = isnull(pol.UOFM,'')
        ,@I_vPurchase_IV_Item_Taxable   = pol.Purchase_IV_Item_Taxable
        ,@I_vPurchase_Item_Tax_Schedu   = pol.Purchase_Item_Tax_Schedu
        ,@I_vPurchase_Site_Tax_Schedu   = pol.Purchase_Site_Tax_Schedu
        ,@I_vBSIVCTTL                   = pol.BSIVCTTL
        ,@I_vTAXAMNT                    = pol.TAXAMNT
        ,@I_vBCKTXAMT                   = pol.BCKTXAMT
        ,@I_vLanded_Cost_Group_ID       = pol.Landed_Cost_Group_ID
        ,@I_vPLNNDSPPLID                = pol.PLNNDSPPLID
        ,@I_vSHIPMTHD                   = pol.SHIPMTHD
        ,@I_vBackoutTradeDiscTax        = pol.BackoutTradeDiscTax
        ,@I_vPOLNESTA                   = ISNULL(pol.POLNESTA,1)
        ,@I_vCMMTTEXT                   = ISNULL(pol.CMMTTEXT,'')
        ,@I_vORD                        = pol.ORD
        ,@I_vCUSTNMBR                   = pol.CUSTNMBR
        ,@I_vADRSCODE                   = pol.ADRSCODE
        ,@I_vCMPNYNAM                   = pol.CMPNYNAM
        ,@I_vCONTACT                    = pol.CONTACT
        ,@I_vADDRESS1                   = pol.ADDRESS1
        ,@I_vADDRESS2                   = pol.ADDRESS2
        ,@I_vADDRESS3                   = pol.ADDRESS3
        ,@I_vCITY                       = pol.CITY
        ,@I_vSTATE                  = pol.STATE
        ,@I_vZIPCODE                    = pol.ZIPCODE
        ,@I_vCCode                  = pol.CCode
        ,@I_vCOUNTRY                    = pol.COUNTRY
        ,@I_vPHONE1                 = pol.PHONE1
        ,@I_vPHONE2                 = pol.PHONE2
        ,@I_vPHONE3                 = pol.PHONE3
        ,@I_vFAX                        = pol.FAX
        ,@I_vPrint_Phone_NumberGB       = pol.Print_Phone_NumberGB
        ,@I_vCURNCYID                   = ISNULL(pol.CURNCYID,'')
        ,@I_vProjNum                    = isnull(pol.ProjNum  ,'')
        ,@I_vCostCatID                  = isnull(pol.CostCatID,'')
        ,@I_vLineNumber             = pol.LineNumber
        ,@I_vUpdateIfExists         = pol.UpdateIfExists
        ,@I_vNOTETEXT                   = pol.NOTETEXT
        ,@I_vRequesterTrx               = pol.RequesterTrx
        ,@I_vUSRDEFND1              = pol.USRDEFND1
        ,@I_vUSRDEFND2              = pol.USRDEFND2
        ,@I_vUSRDEFND3              = pol.USRDEFND3
        ,@I_vUSRDEFND4              = pol.USRDEFND4
        ,@I_vUSRDEFND5              = pol.USRDEFND5
    FROM DDtaPOLine pol
    WHERE pol.RowID = @LineID
 
IF NOT EXISTS(SELECT 1 FROM iv00103 WHERE VENDORID = @I_vVENDORID AND VNDITNUM = @I_vITEMNMBR ) BEGIN
 
    INSERT INTO iv00103 (vendorid    , itmvndty, ITEMNMBR    , VNDITNUM)
        VALUES          (@I_vVENDORID, 1       , @I_vITEMNMBR, @I_vITEMNMBR)
    PRINT 'end insert'
end
-- fp_taPOLineWrapper 3
 
 
EXECUTE @RC = taPoLine
   @I_vPOTYPE
  ,@I_vPONUMBER
  ,@I_vDOCDATE
  ,@I_vVENDORID
  ,@I_vLOCNCODE
  ,@I_vVNDITNUM
  ,@I_vITEMNMBR
  ,@I_vQUANTITY
  ,@I_vQTYCANCE
  ,@I_vFREEONBOARD
  ,@I_vREQSTDBY
  ,@I_vCOMMNTID
  ,@I_vCOMMENT_1
  ,@I_vCOMMENT_2
  ,@I_vCOMMENT_3
  ,@I_vCOMMENT_4
  ,@I_vREQDATE
  ,@I_vRELEASEBYDATE
  ,@I_vPRMDATE
  ,@I_vPRMSHPDTE
  ,@I_vNONINVEN
  ,@I_vIVIVINDX
  ,@I_vInventoryAccount
  ,@I_vITEMDESC
  ,@I_vUNITCOST
  ,@I_vVNDITDSC
  ,@I_vUOFM
  ,@I_vPurchase_IV_Item_Taxable
  ,@I_vPurchase_Item_Tax_Schedu
  ,@I_vPurchase_Site_Tax_Schedu
  ,@I_vBSIVCTTL
  ,@I_vTAXAMNT
  ,@I_vBCKTXAMT
  ,@I_vLanded_Cost_Group_ID
  ,@I_vPLNNDSPPLID
  ,@I_vSHIPMTHD
  ,@I_vBackoutTradeDiscTax
  ,@I_vPOLNESTA
  ,@I_vCMMTTEXT
  ,@I_vORD
  ,@I_vCUSTNMBR
  ,@I_vADRSCODE
  ,@I_vCMPNYNAM
  ,@I_vCONTACT
  ,@I_vADDRESS1
  ,@I_vADDRESS2
  ,@I_vADDRESS3
  ,@I_vCITY
  ,@I_vSTATE
  ,@I_vZIPCODE
  ,@I_vCCode
  ,@I_vCOUNTRY
  ,@I_vPHONE1
  ,@I_vPHONE2
  ,@I_vPHONE3
  ,@I_vFAX
  ,@I_vPrint_Phone_NumberGB
  ,@I_vCURNCYID
  ,@I_vProjNum
  ,@I_vCostCatID
  ,@I_vLineNumber
  ,@I_vUpdateIfExists
  ,@I_vNOTETEXT
  ,@I_vRequesterTrx
  ,@I_vUSRDEFND1
  ,@I_vUSRDEFND2
  ,@I_vUSRDEFND3
  ,@I_vUSRDEFND4
  ,@I_vUSRDEFND5
  ,@O_iErrorState OUTPUT
  ,@oErrString OUTPUT
 
 
    
IF @O_iErrorState > 0 BEGIN
    SELECT @ErrorDesc = tec.ErrorDesc
        FROM dynamics..taErrorCode tec
        WHERE tec.ErrorCode = @O_iErrorState
     
    raiserror(@ErrorDesc, 16,2)
END ELSE BEGIN
    SELECT @ErrorDesc = ''
end
  -- fp_taPOLineWrapper 3
  
    
    
GO
    
grant exec on fp_taPOLineWrapper to public

 

 

 

 

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences