DynDeveloper.com

This stored proc will decrypt Dynamics GP passwords. Use this code to test. 

I am trying to locate the table that stores the information related to the ship to information on a line item.

When I click the arrow next to "Ship To Address ID" I get the following window:

Which table holds the information for this form?

good day!

I would like to know if you can guide me, how I can associate by the SOP master order number, the lines that have been transferred. Example, if you make a quote with 5 items, when transferring to order, the same when transferring only 3 item was transferred, and at the time of making invoice, only 1 item had quantity available.

It is more or less, a tracking of the items in each sales document.

Someone could help me, I would like to do that in sql.

Thank you

Client wants a sql report (Excel refreshable spreadsheet maybe?) to pull the GL account balances.  That part is easy

the hard part is - there are multiple companies - with different currency rates.

I know that the historical rates are in the GP tables.  Is it possible (without mega programming) to dump the GL account balances for these foreign currencies and get the local USD rate?  The balances in the SQL tables are in the foreign dollars.

Maybe something exists out there somewhere?

Hello,

 I wrote a SQL based integration for a client that takes customer and vendor invoices from a proprietary system, and inserts them into their MS Dynamics database. Overall the integration is working pretty well -- but some vendor invoices fail with error 4610 (Unable to calculate the discount and due dates) when calling "taPMTransactionInsert". I've looked at the data, and don't see any patterns (e.g., not always happening to a specific customer or vendor) and I really don't know much about dynamics in general.

 Can someone point me in the right direction to troubleshooting this problem? I can provide the procedure code in a private message if necessary, but the general flow I have is this...

1) begin transaction

2) Call taGetPMNextVoucherNumber and store next voucher number

3) Validate customer # I have exists in Dynamics (by querying GL00100)

4) Validate vendor # I have exists (by querying GL00100)

5) Call taPMTransactionInsert twice (once for the AP account and once for the customer account)

6) Each of the calls above are wrapped w/ try catches where I do error logging and a rollback on the transaction

Thanks,

David

eConnect Error Code 908 (Invalid Sales Account)

I am trying to override the default sales account when creating an invoice.  I am passing in the account index (@I_vSLSINDX varchar(75)) as a string.  I have tried setting it as a variable from my code and also hard coding the account index in my code and get the same error message either way.  I have verified that the sales account is coming from the customer and I can change the account on the customer card to the account I want to use and it defaults just fine but if I pass in the sales account index for that same account to the taSopLineIvcInsert eConnect stored procedure I get the 908 error code returned.  If I remove all references to the @I_vSLSINDX eConnect variable it works just fine and defaults the sales account from the customer card.  I even validate the SLSINDX in the SOP10200 table when I create an invoice through the GP UI and it is the same index that I am passing in so I don't think it has anything to do with the account index being used.

 Here is the SQL stored procedure I created and the code to call the stored procedure.

/****** Object:  StoredProcedure [dbo].[zCreateSubscriptionInvoiceLine]    Script Date: 4/24/2019 7:25:51 AM 

4/24/2019 Added Sales Index (@SLSINDX) to make sure the specific customer sales account number is used for distributions.
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[zCreateSubscriptionInvoiceLine]
(
@SOPTYPE tinyint 
,@SopNumber varchar(21)
,@CUSTNMBR varchar (15)
,@DOCDATE datetime
,@ITEMNMBR varchar(30)
,@UNITPRCE numeric(19,5)
,@XTNDPRCE numeric(19,5)
,@QUANTITY numeric(19,5)
,@ITEMDESC varchar(100)
,@NONINVEN smallint
,@LNITMSEQ int
,@DOCID varchar(15) 
,@SLSINDX varchar(75)
,@ErrorState int out
,@ErrString varchar(255) out
)

AS
BEGIN
       exec taSopLineIvcInsert @I_vSOPTYPE = @SOPTYPE
, @I_vSOPNUMBE = @SopNumber
, @I_vCUSTNMBR = @CUSTNMBR
, @I_vDOCDATE = @DOCDATE
, @I_vITEMNMBR = @ITEMNMBR
, @I_vUNITPRCE = @UNITPRCE
, @I_vXTNDPRCE = @XTNDPRCE
, @I_vQUANTITY = @QUANTITY
, @I_vITEMDESC = @ITEMDESC
, @I_vNONINVEN = @NONINVEN
, @I_vLNITMSEQ = @LNITMSEQ
, @I_vDOCID = @DOCID
, @I_vSLSINDX = @SLSINDX
, @O_iErrorState = @ErrorState out
, @oErrString = @ErrString out
END

/*
declare @SOPTYPE tinyint = 3
declare @SopNumber varchar(21) = 'SBINV0000000071'
declare @CUSTNMBR varchar (15) = 'SYNNEX'
declare @DOCDATE datetime = '4/24/2019'
declare @ITEMNMBR varchar(30) = 'WGM37913'
declare @UNITPRCE numeric(19,5) = 51.40000
declare @XTNDPRCE numeric(19,5) = 51.40000
declare @QUANTITY numeric(19,5) = 1.00000
declare @ITEMDESC varchar(100) = 'SB000015182 - 4/15/2019 - 5/14/2019 - 1'
declare @NONINVEN smallint = 0
declare @LNITMSEQ int = 16384
declare @DOCID varchar(15) = 'SBINV'
declare @SLSINDX varchar(75) = '11205'
declare @ErrorState int 
declare @ErrString varchar(255) 

exec zCreateSubscriptionInvoiceLine @SOPTYPE
,@SopNumber
,@CUSTNMBR
,@DOCDATE
,@ITEMNMBR
,@UNITPRCE
,@XTNDPRCE
,@QUANTITY
,@ITEMDESC
,@NONINVEN
,@LNITMSEQ
,@DOCID
,@SLSINDX
,@ErrorState out
,@ErrString out
select @ErrorState

*/