--when importing SOP data, we most often will run the import data into two tables, ddtaSOPHdrIvcInsert and ddtaSOPLineIvcInsert
--these tables exactly mirror the parameters for the linked stored procedures/eConnect schema
--this code is designed to query those tables and run the data into SOP10200.
--this is not a full sop document, it is the line only. it could be used for updates
--ddtaSOPHdrIvcInsert links to ddtaSOPLineInsert on this field, so this gives us exactly one order
declare @HeaderID int = 1305369
DECLARE @I_vSOPTYPE int,
@I_vInc_Dec tinyint = 1,
@O_iErrorState INT = 0
DECLARE @O_vSOPNumber varchar(21) = ''
DECLARE @RC int
DECLARE @oErrString varchar(255)
declare @I_vDOCID varchar(15)
declare @I_vCUSTNMBR varchar(15)
declare @I_vDOCDATE date
declare @I_vLOCNCODE varchar(11)
declare @I_vITEMNMBR varchar(31)
declare @I_vAutoAssignBin int
declare @I_vQUANTITY numeric(19,5)
declare @I_vTAXSCHID varchar(11)
declare @I_vCITY varchar(35)
declare @I_vSTATE varchar(29)
declare @I_vZIPCODE varchar(10)
declare @I_vCOUNTRY varchar(60)
declare @I_vPHONE1 varchar(21)
declare @I_vSHIPMTHD varchar(11)
declare @I_vUOFM varchar(11)
declare @I_vDEFPRICING int
declare @I_vDEFEXTPRICE int
declare @I_vQtyShrtOpt smallint
--get data from our setup tables. This will vary from site to site
select
@I_vDOCID = h.DOCID,
@I_vSOPTYPE = h.SOPTYPE,
@I_vCUSTNMBR = h.CUSTNMBR
from DDtaSopHdrIvcInsert h
where rowid = 1305369
select
@I_vDOCDATE = l.DOCDATE,
@I_vLOCNCODE = l.LOCNCODE,
@I_vITEMNMBR = l.ITEMNMBR,
@I_vAutoAssignBin = l.AutoAssignBin,
@I_vQUANTITY = l.QUANTITY,
@I_vTAXSCHID = l.TAXSCHID,
@I_vCITY = l.CITY,
@I_vSTATE = l.state,
@I_vZIPCODE = l.ZIPCODE,
@I_vCOUNTRY = l.country,
@I_vPHONE1 = l.PHONE1,
@I_vSHIPMTHD = l.SHIPMTHD,
@I_vUOFM = l.UOFM,
@I_vDEFPRICING = l.DEFPRICING,
@I_vDEFEXTPRICE = l.DEFEXTPRICE,
@I_vQtyShrtOpt = l.QtyShrtOpt
from DDtaSopLineIvcInsert l
where l.HeaderID = @HeaderID
--this is demo code, we wanted to return only one line.
--best if you pass this in
and l.LineID = 1645483
--get the next available SOPNUMBE
exec sopGetIDNumber @i_vSOPTYPE,@I_vDOCID,@I_vInc_Dec ,@O_vSOPNumber OUT,@O_iErrorState OUT
--if we don't get a SOPNUMBE, whine and exit
if @O_iErrorState <> 0 begin
SELECT @O_vSOPNumber ,
@O_iErrorState
return
end
EXECUTE @RC = taSopLineIvcInsert
--note that we're providing the params 'by name'.
--by using this method, we can provide the ones that we want, and skip the rest
--we've provided all the parameters below, commented out, for future use
@I_vSOPTYPE = @I_vSOPTYPE,
@I_vSOPNUMBE = @O_vSOPNumber,
@I_vCUSTNMBR = @I_vCUSTNMBR,
@I_vDOCDATE = @I_vDOCDATE,
--@I_vUSERDATE
@I_vLOCNCODE = @I_vLOCNCODE,
@I_vITEMNMBR = @I_vITEMNMBR,
--@I_vAutoAssignBin
--@I_vUNITPRCE
--@I_vXTNDPRCE
@I_vQUANTITY = @I_vQUANTITY,
--@I_vMRKDNAMT
--@I_vMRKDNPCT
--@I_vCOMMNTID
--@I_vCOMMENT_1
--@I_vCOMMENT_2
--@I_vCOMMENT_3
--@I_vCOMMENT_4
--@I_vUNITCOST
--@I_vPRCLEVEL
--@I_vITEMDESC
--@I_vTAXAMNT
--@I_vQTYONHND
--@I_vQTYRTRND
--@I_vQTYINUSE
--@I_vQTYINSVC
--@I_vQTYDMGED
--@I_vNONINVEN
--@I_vLNITMSEQ
--@I_vDROPSHIP
--@I_vQTYTBAOR
@I_vDOCID = @I_vDOCID,
--@I_vSALSTERR
--@I_vSLPRSNID
--@I_vITMTSHID
--@I_vIVITMTXB
@I_vTAXSCHID = @I_vTAXSCHID,
--@I_vPRSTADCD
--@I_vShipToName
--@I_vCNTCPRSN
--@I_vADDRESS1
--@I_vADDRESS2
--@I_vADDRESS3
@I_vCITY = @I_vCITY,
@I_vSTATE = @I_vSTATE,
@I_vZIPCODE = @I_vZIPCODE,
@I_vCOUNTRY = @I_vCOUNTRY,
@I_vPHONE1 = @I_vPHONE1,
--@I_vPHONE2
--@I_vPHONE3
--@I_vFAXNUMBR
--@I_vPrint_Phone_NumberGB
--@I_vEXCEPTIONALDEMAND
--@I_vReqShipDate
--@I_vFUFILDAT
--@I_vACTLSHIP
@I_vSHIPMTHD = @I_vSHIPMTHD,
--@I_vINVINDX
--@I_vCSLSINDX
--@I_vSLSINDX
--@I_vMKDNINDX
--@I_vRTNSINDX
--@I_vINUSINDX
--@I_vINSRINDX
--@I_vDMGDINDX
--@I_vAUTOALLOCATESERIAL
--@I_vAUTOALLOCATELOT
--@I_vGPSFOINTEGRATIONID
--@I_vINTEGRATIONSOURCE
--@I_vINTEGRATIONID
--@I_vRequesterTrx
--@I_vQTYCANCE
--@I_vQTYFULFI
--@I_vALLOCATE
--@I_vUpdateIfExists
--@I_vRecreateDist
--@I_vQUOTEQTYTOINV
--@I_vTOTALQTY
--@I_vCMMTTEXT
--@I_vKitCompMan
@I_vDEFPRICING = @I_vDEFPRICING,
@I_vDEFEXTPRICE = @I_vDEFEXTPRICE,
--@I_vCURNCYID
@I_vUOFM = @I_vUOFM,
--@I_vIncludePromo
--@I_vCKCreditLimit
@I_vQtyShrtOpt = @I_vQtyShrtOpt,
--@I_vRECREATETAXES
--@I_vRECREATECOMM
--@I_vUSRDEFND1
--@I_vUSRDEFND2
--@I_vUSRDEFND3
--@I_vUSRDEFND4
--@I_vUSRDEFND5
@O_iErrorState = @O_iErrorState OUTPUT,
@oErrString = @oErrString OUTPUT
--if there is an error, @O_iErrorState will have the number and we'll look up the text for that error in DYNAMICS..taErrorCode
IF @O_iErrorState <> 0 BEGIN
SELECT @O_iErrorState, tec.ErrorDesc, @I_vITEMNMBR, @I_vLOCNCODE
FROM DYNAMICS..taErrorCode tec
WHERE tec.ErrorCode = @O_iErrorState
end
SELECT * FROM sop10200 WHERE SOPNUMBE = @O_vSOPNumber