|
riquigley
Starting Member
1 Post |
 Posted - 2012-06-23 : 16:10:45
|
Hi All. I'm a PHP, Java, ActionScript developer, but have somehow been tasked with injecting data into a MS GreatPlains database . I have been able to inject new customers and new customer addresses with no issue. But now that I have to create a new invoice, I'm having trouble.My understanding is that I have to generate an invoice in three parts. Part One is to create a line item tax header. Part 2 is to create a line item header. Part 3 is to create an invoice header.I'm pretty certain that I have all of my necessary fields set correctly, but I'm getting an error from GreatPlains telling me that there is at least one NULL value when I try to inject the Line Item Header (Part 2).I suspect that I might have to execute all three stored procs with one query, but I can't get the syntax right. Can someone please look over this and tell me what I'm doing wrong. For brevity, I have omitted a lot of the field declarations (as that there are hundreds). But if necessary, I can't post the queries in full.Here's to hoping all the time I spent answering ActionScript questions has built up some karma!----------------------------------------- The Tax Header Stored Proc --DECLARE @RC int DECLARE @I_vSOPTYPE smallint DECLARE @I_vTAXTYPE smallint DECLARE @I_vSOPNUMBE char(21) DECLARE @I_vCUSTNMBR char(15) DECLARE @I_vLNITMSEQ int DECLARE @I_vSALESAMT numeric(19,5) DECLARE @I_vFRTTXAMT numeric(19,5) DECLARE @I_vMSCTXAMT numeric(19,5) … snip...DECLARE @O_iErrorState int DECLARE @oErrString varchar(255) -- TODO: Set parameter values here.SET @I_vSOPTYPE = 3 SET @I_vTAXTYPE = 0 SET @I_vSOPNUMBE = 'WEB10000095' SET @I_vCUSTNMBR = 'WEB10000056' SET @I_vLNITMSEQ = 0 SET @I_vSALESAMT = 234 SET @I_vFRTTXAMT = 0 SET @I_vMSCTXAMT = 0 SET @I_vFREIGHT = 0 SET @I_vMISCAMNT = 0 … snip ...SET @O_iErrorState = 0 SET @oErrString = '' EXECUTE @RC = [TWO].[dbo].[taSopLineIvcTaxInsert] @I_vSOPTYPE ,@I_vTAXTYPE ,@I_vSOPNUMBE ,@I_vCUSTNMBR ,@I_vLNITMSEQ ,@I_vSALESAMT ,@I_vFRTTXAMT ,@I_vMSCTXAMT ,@I_vFREIGHT ,@I_vMISCAMNT ,… snip… @O_iErrorState OUTPUT ,@oErrString OUTPUTGO------------------------------------ The Line Item Header Stored Proc --DECLARE @RC int DECLARE @I_vSOPTYPE smallint DECLARE @I_vSOPNUMBE char(21) DECLARE @I_vCUSTNMBR char(15) DECLARE @I_vDOCDATE datetime DECLARE @I_vUSERDATE datetime DECLARE @I_vLOCNCODE char(10) DECLARE @I_vITEMNMBR char(30) DECLARE @I_vAutoAssignBin smallint DECLARE @I_vUNITPRCE numeric(19,5) …snip...DECLARE @O_iErrorState int DECLARE @oErrString varchar(255) -- TODO: Set parameter values here.SET @I_vSOPTYPE = 3 SET @I_vSOPNUMBE = 'WEB10000095' SET @I_vCUSTNMBR = 'WEB10000056' SET @I_vDOCDATE = 1340481126 SET @I_vUSERDATE = 0 SET @I_vLOCNCODE = 0 SET @I_vITEMNMBR = '858409003043' SET @I_vAutoAssignBin = 0 SET @I_vUNITPRCE = 39 … snip ...SET @O_iErrorState = 0 SET @oErrString = '' EXECUTE @RC = [TWO].[dbo].[taSopLineIvcInsert] @I_vSOPTYPE ,@I_vSOPNUMBE ,@I_vCUSTNMBR ,@I_vDOCDATE ,@I_vUSERDATE ,@I_vLOCNCODE ,@I_vITEMNMBR ,@I_vAutoAssignBin ,@I_vUNITPRCE ,… snip … @O_iErrorState OUTPUT ,@oErrString OUTPUT SELECT @O_iErrorState SELECT @oErrStringGO--------------------------------------- The Invoice Header Stored Proc --DECLARE @RC int DECLARE @I_vSOPTYPE smallint DECLARE @I_vDOCID char(15) DECLARE @I_vSOPNUMBE char(21) DECLARE @I_vORIGNUMB char(21) DECLARE @I_vORIGTYPE smallint DECLARE @I_vTAXSCHID char(15) DECLARE @I_vFRTSCHID char(15) DECLARE @I_vMSCSCHID char(15) DECLARE @I_vSHIPMTHD char(15) DECLARE @I_vTAXAMNT numeric(19,5) … snip ...DECLARE @O_iErrorState int DECLARE @oErrString varchar(255) -- TODO: Set parameter values here. SET @I_vSOPTYPE = 3 SET @I_vDOCID = 'WEB' SET @I_vSOPNUMBE = 'WEB10000095' SET @I_vORIGNUMB = '0' SET @I_vORIGTYPE = 0 SET @I_vTAXSCHID = '' SET @I_vFRTSCHID = '' SET @I_vMSCSCHID = '' SET @I_vSHIPMTHD = '' SET @I_vTAXAMNT = 0 … snip ...SET @O_iErrorState = 0 SET @oErrString = '' EXECUTE @RC = [TWO].[dbo].[taSopHdrIvcInsert] @I_vSOPTYPE ,@I_vDOCID ,@I_vSOPNUMBE ,@I_vORIGNUMB ,@I_vORIGTYPE ,@I_vTAXSCHID ,@I_vFRTSCHID ,@I_vMSCSCHID ,@I_vSHIPMTHD ,@I_vTAXAMNT ,… snip ...@O_iErrorState OUTPUT ,@oErrString OUTPUT SELECT @O_iErrorState SELECT @oErrString SELECT * FROM DYNAMICS.dbo.taErrorCode WHERE taErrorCode.ErrorCode=@O_iErrorStateAs a side note, you might be wondering why we aren't using a web service, or MS's eConnect system that's built into Great Plains. All I can say is that using those methods would require some help on the part of the Great Plains hosting service. And after 6 weeks of runarounds, and just 2 days before site launch, this is what they've given us. But it's up to me to get the SQL query right. So… ugh.Thanks all, for any help you might can provide.
|
|
Basic SQL Query