Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
jivtesh 2/13/2013 3:27:07 AM

Creating Invoice with Manual Distributions

Hi Guys, 

Have been a subscriber for an year, now looking for your help. 

Using the code below to create an invoice with manual distributions, instead of default. Have the following 2 problems - 

1. Get the distribution account(s) are missing or invalid error. However, I can see the accounts in the distribution window. 

2. The tax is not being imported.  

 

Function CreateSOPHeader(ByRef eConnect As eConnectType, ByVal SOPHeader As SOPHeaderType, _
    ByRef sError As String, ByVal dSubTotal As Decimal, ByVal bPostImmediately As Boolean) As Boolean
        Try
            CreateSOPHeader = False
            Dim CustomerType As New SOPTransactionType
            Dim MySOPHeader As New taSopHdrIvcInsert
            'Insert our customer data from the winform into the MyCustomer Class object.
            With MySOPHeader
                .CUSTNMBR = Trim(SOPHeader.AccountNumber)
                .SOPTYPE = SOPHeader.TransactionType '1=Quote, 2=Order, 3=Invoice, 4=Return, 5=Backorder
                ' No need to specify .DOCID as it is automatically determined from .SOPTYPE. AM 2/11/2005
                If IsNothing(SOPHeader.DocIDString) Then
                    .DOCID = DocIDFromTypeArr(.SOPTYPE) ' Identifies source of record.
                Else
                    .DOCID = SOPHeader.DocIDString ' Identifies source of record.
                End If
                .SOPNUMBE = SOPHeader.InvoiceNumber
                If IsNothing(SOPHeader.DocDate) OrElse SOPHeader.DocDate = Date.MinValue Then
                    .DOCDATE = Today.Date
                Else
                    .DOCDATE = SOPHeader.DocDate
                End If
                If IsNothing(SOPHeader.DueDate) OrElse SOPHeader.DueDate = Date.MinValue Then
                    .DUEDATE = DateAdd(DateInterval.Day, 7, Today.Date)
                Else
                    .DUEDATE = SOPHeader.DueDate
                End If
                .CSTPONBR = SOPHeader.PONumber
                .BACHNUMB = SOPHeader.BatchNumber

                .REFRENCE = SOPHeader.Reference
                .COMMENT_1 = SOPHeader.CommentOnStatement_1
                .COMMENT_2 = SOPHeader.CommentOnStatement_2
                .COMMENT_3 = SOPHeader.CommentOnStatement_3
                .COMMENT_4 = SOPHeader.CommentOnStatement_4
                .SUBTOTAL = dSubTotal
                .DOCAMNT = dSubTotal
                .LOCNCODE = "BELM"
                .USRDEFND1 = SOPHeader.Reference
                .USER2ENT = SOPHeader.EnteredBy
                .CREATEDIST = 0
                If SOPHeader.ChkCredit Then
                    .CKCreditLimit = 1
                Else
                    .CKCreditLimit = 0
                End If
                If SOPHeader.ChkHold Then
                    .CKHOLD = 1
                Else
                    .CKHOLD = 0
                End If

                '1.16 Shipping Addresses
                .ShipToName = SOPHeader.AddressName
                .ADDRESS1 = SOPHeader.Address_Line1
                .ADDRESS2 = SOPHeader.Address_Line2
                .CITY = SOPHeader.City
            End With

            ' Only add a payment record if paying now.
            If Not IsNothing(SOPHeader.PaymentType) Then
                If SOPHeader.PaymentType > 0 Then
                    Dim MySOPPayment As New taCreateSopPaymentInsertRecord_ItemsTaCreateSopPaymentInsertRecord
                    MySOPPayment.CUSTNMBR = MySOPHeader.CUSTNMBR
                    MySOPPayment.SOPTYPE = MySOPHeader.SOPTYPE
                    MySOPPayment.SOPNUMBE = MySOPHeader.SOPNUMBE
                    MySOPPayment.PYMTTYPE = SOPHeader.PaymentType
                    If SOPHeader.PaymentType = enumPaymentTypes.CreditCard Then
                        MySOPPayment.CARDNAME = SOPHeader.PaymentCreditCardType.Trim ' VISA, BANKCARD, MASTERCARD
                        MySOPPayment.RCTNCCRD = SOPHeader.PaymentCreditCardNo.Trim
                        MySOPPayment.EXPNDATE = SOPHeader.PaymentCreditCardExpiry
                        '[1.13 
                        MySOPPayment.DOCNUMBR = Today.Date.ToString
                        '1.13]
                        ' Note: SJA cheque book ID is not allowed to be supplied for CC transactions 
                        ' - assume this is due to a/c being supplied when setting up CC's in GP.
                    Else
                        MySOPPayment.CHEKBKID = "BW TRADING" ' This is St Johns bank account, not the customers.
                        If SOPHeader.PaymentType = enumPaymentTypes.Cheque Then
                            MySOPPayment.CHEKNMBR = SOPHeader.PaymentChequeNo.Trim
                        End If
                    End If
                    MySOPPayment.DOCNUMBR = "PYMT-" & MySOPHeader.SOPNUMBE
                    ' Add the payment record to the object.
                    ReDim CustomerType.taCreateSopPaymentInsertRecord_Items(0)
                    CustomerType.taCreateSopPaymentInsertRecord_Items(0) = MySOPPayment
                End If
            End If

            CustomerType.taSopHdrIvcInsert = MySOPHeader
            ReDim eConnect.SOPTransactionType(0)
            eConnect.SOPTransactionType(0) = CustomerType

            If bPostImmediately Then
                If PostEconnectDocument(eConnect, sError) Then
                    CreateSOPHeader = True
                End If
            Else
                CreateSOPHeader = True
            End If
        Catch ex As Exception
            sError = ex.Message & vbCrLf & ex.StackTrace
        End Try
    End Function

    ' Handy ref: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/eConnect8.0_MSDN/soptransactiongeneralinformation.asp
    Function CreateSOPLines(ByVal SOPHeader As SOPHeaderType, ByVal SOPLine() As SOPLineType, _
    ByRef sError As String) As Boolean
        Dim eConnect As New eConnectType
        Try
            CreateSOPLines = False

            'Dim CustomerType As New SOPTransactionType
            Dim MyTaxLine As New taSopLineIvcTaxInsert_ItemsTaSopLineIvcTaxInsert
            Dim indx As Int16
            Dim dOrderTotal As Decimal = 0
            Dim dTax As Decimal
            Dim dTotalTax As Decimal = 0
            Dim dtSOPCustomer As DataTable = Nothing
            Dim dtItemAccts As DataTable = Nothing
            Dim dtCustAccts As DataTable = Nothing
            Dim SalesAccount As String = Nothing
            Dim SalesRTNAccount As String = Nothing
            Dim RECVAccount As String = Nothing
            Dim iDistCount As Integer = 0
            Dim oDistribution As New taSopDistribution_ItemsTaSopDistribution

            ' The "CreateSopHeader" function will return the "eConnect" object
            ' populated with the header information. This function can then add
            ' the line detail information before posting to the econnect interface.
            If Not CreateSOPHeader(eConnect, SOPHeader, sError, dOrderTotal, False) Then
                Exit Try
            End If

            ' Prepare the econnect line item array for the number of lines being inserted.
            ReDim eConnect.SOPTransactionType(0).taSopLineIvcInsert_Items(SOPLine.Length - 1)

            '[130107 - manual posting distribution !
            ' check if SOP processing is using Item or Customer ... if using customer we must build the distribution manually from accounts in the items view
            dtSOPCustomer = Check_SOP("SOPType", "", sError)
            
            For indx = 0 To SOPLine.Length - 1

                Dim MyInvoiceLine As New taSopLineIvcInsert_ItemsTaSopLineIvcInsert

                With MyInvoiceLine
                    .CUSTNMBR = Trim(SOPHeader.AccountNumber)
                    .SOPNUMBE = SOPHeader.InvoiceNumber
                    .SOPTYPE = SOPLine(indx).TransactionType '1=Quote, 2=Order, 3=Invoice, 4=Return, 5=Backorder
                    ' No need to specify .DOCID as it is automatically determined from .SOPTYPE. AM 2/11/2005
                    If IsNothing(SOPHeader.DocIDString) Then
                        .DOCID = DocIDFromTypeArr(.SOPTYPE) ' Identifies source of record.
                    Else
                        .DOCID = SOPHeader.DocIDString      ' Identifies source of record.
                    End If
                    .DOCDATE = eConnect.SOPTransactionType(0).taSopHdrIvcInsert.DOCDATE
                    .ITEMNMBR = Trim(SOPLine(indx).ItemNumber) ' Product code - relates to income account.
                    .UNITPRCE = SOPLine(indx).UnitPrice
                    .UNITCOST = SOPLine(indx).UnitCost
                    .QUANTITY = SOPLine(indx).Quantity

                    ' Dont use .TAXSCHID here - it will cause heaps of GST/BAS report grief!!!!!
                    If UCase((SOPLine(indx).TaxCode)) = "G" Then
                        .ITMTSHID = "GSTEX-GST"
                        '[1.11
                        ' dTax = .UNITPRCE * .QUANTITY * 0.1
                        dTax = .UNITPRCE * .QUANTITY * 0.1
                        dTax = Decimal.Round(dTax, 3)   ' Go to 3 decs them check if 3rd dec is 5
                        If Decimal.Remainder(dTax * 100, 1) = 0.5 Then
                            'dTax = Decimal.Round(dTax, 2) + 0.01
                            dTax = Decimal.Round(Decimal.Parse(dTax + 0.005), 2)
                        Else
                            If Decimal.Remainder(dTax * 100, 1) = -0.5 Then
                                'dTax = Decimal.Round(dTax, 2) - 0.01
                                dTax = Decimal.Round(Decimal.Parse(dTax - 0.005), 2)
                            Else
                                dTax = Decimal.Round(dTax, 2)
                            End If
                        End If
                        '1.11]
                        dTotalTax = dTotalTax + Decimal.Round(dTax, 2)
                    Else
                        .ITMTSHID = "GSTEX-FRE"
                    End If
                    '.TAXAMNT = Dont specify, as tax code will calculate this for us if we set 'CREATETAXES=1' in header.
                    .XTNDPRCE = .UNITPRCE * .QUANTITY
                    .ITEMDESC = Trim(SOPLine(indx).Description)
                    If Trim(SOPLine(indx).CommentOnInvoice_1).Length > 0 Then
                        .COMMENT_1 = Trim(SOPLine(indx).CommentOnInvoice_1)
                    Else
                        If Trim(SOPHeader.CommentOnStatement_1).Length > 0 Then
                            .COMMENT_1 = Trim(SOPHeader.CommentOnStatement_1)
                        Else
                            .COMMENT_1 = Trim(SOPLine(indx).Description)
                        End If
                    End If
                    .COMMENT_2 = Trim(SOPLine(indx).CommentOnInvoice_2)
                    .COMMENT_3 = Trim(SOPLine(indx).CommentOnInvoice_3)
                    .COMMENT_4 = Trim(SOPLine(indx).CommentOnInvoice_4)
                    .ReqShipDate = Today.Date ' Required field
                    .FUFILDAT = Today.Date  ' Required field
                    .ACTLSHIP = Today.Date  ' Required field
                    .UOFM = "EACH"
                    .CURNCYID = "Z-AUD"
                    .PRCLEVEL = SOPLine(indx).PriceScale
                    ' Make location for item same as header.
                    .LOCNCODE = eConnect.SOPTransactionType(0).taSopHdrIvcInsert.LOCNCODE

                    ' Returns will not post unless returned qty = total quantity
                    If .SOPTYPE = 4 Then .QTYRTRND = .QUANTITY

                    'Set line item sequence in increments of 16384
                    .LNITMSEQ = 16384 * (indx + 1)
                    '.LOCNCODE = "BELM"
                    dOrderTotal = dOrderTotal + (.UNITPRCE * .QUANTITY)
                End With

                'Do Manual Posting
                ' Add the line item to the econnect object...
                eConnect.SOPTransactionType(0).taSopLineIvcInsert_Items(indx) = MyInvoiceLine

                If dtSOPCustomer.Rows(0)(0).ToString = "1" Then
                    dtItemAccts = Check_SOP("ItemAccts", Trim(SOPLine(indx).ItemNumber), sError)
                    If dtItemAccts.Rows.Count > 0 Then
                        SalesAccount = dtItemAccts.Rows(0).Item(0).ToString
                        SalesRTNAccount = dtItemAccts.Rows(0).Item(1).ToString
                    End If

                    dtCustAccts = Check_SOP("CustAcct", Trim(SOPHeader.AccountNumber), sError)
                    If dtCustAccts.Rows.Count > 0 Then
                        RECVAccount = dtCustAccts.Rows(0).Item(0).ToString
                    End If

                    ReDim eConnect.SOPTransactionType(0).taSopDistribution_Items(SOPLine.Length + 1)

                    Select Case CType(MyInvoiceLine.SOPTYPE, enumSOPTransactionTypes)
                        Case enumSOPTransactionTypes.Invoice
                            ' manually set up distributions for each of the accounts present
                            ' need to find out what dist type, if they include tax or not, are they credit or debit (need an accountant for that!)



                            With oDistribution
                                .SOPTYPE = MyInvoiceLine.SOPTYPE
                                .SOPNUMBE = MyInvoiceLine.SOPNUMBE
                                .CUSTNMBR = MyInvoiceLine.CUSTNMBR
                                .SEQNUMBR = iDistCount
                                .DISTTYPE = enumSOPDistributionTypes.Sales
                                .ACTNUMST = SalesAccount
                                .CRDTAMNT = MyInvoiceLine.XTNDPRCE + dTax
                                .DistRef = "Sales"
                            End With
                            'eConnect.SOPDistributionsType(0).taSopDistribution_Items(iDistCount) = oDistribution
                            eConnect.SOPTransactionType(0).taSopDistribution_Items(iDistCount) = oDistribution
                            iDistCount += 1
                            oDistribution = Nothing
                            'If RECVAccount.ToString.Length > 0 Then
                            '    oDistribution = New taSopDistribution_ItemsTaSopDistribution
                            '    With oDistribution
                            '        .SOPTYPE = MyInvoiceLine.SOPTYPE
                            '        .SOPNUMBE = MyInvoiceLine.SOPNUMBE
                            '        .CUSTNMBR = MyInvoiceLine.CUSTNMBR
                            '        .SEQNUMBR = iDistCount
                            '        .DISTTYPE = enumSOPDistributionTypes.Receiving
                            '        .ACTNUMST = RECVAccount
                            '        .DEBITAMT = MyInvoiceLine.XTNDPRCE + dTax
                            '        .DistRef = "Receiving"
                            '    End With
                            '    eConnect.SOPTransactionType(0).taSopDistribution_Items(iDistCount) = oDistribution
                            '    iDistCount += 1
                            '    oDistribution = Nothing
                            'End If
                            ' do I need 'Sales Returns Account Number' ?
                        Case 2
                        Case 3

                    End Select

                End If

                MyInvoiceLine = Nothing
            Next

            With eConnect.SOPTransactionType(0).taSopHdrIvcInsert
                .CREATETAXES = 1
                .DEFTAXSCHDS = 1 ' Ensures that
                .TAXSCHID = "GSTEX-DEBT"
                '.SUBTOTAL = dOrderTotal
                '.DOCAMNT = dOrderTotal ' Same as subtotal when auto-calcing taxes, otherwise must include tax amount.
                .DEFPRICING = 1 ' Will auto calc subtotal from sop line amounts.
                '1.14 - use value passed in by user
                '.CKCreditLimit = 1 ' Dont post if credit limit exceeded.
                '.CKHOLD = 1 ' Dont post if account on hold.
            End With

            With eConnect.SOPTransactionType(0)
                ' If there is a payment record, then we must state how much we are paying.
                ' We will always pay the entire amount.
                If Not IsNothing(.taCreateSopPaymentInsertRecord_Items) Then
                    If .taCreateSopPaymentInsertRecord_Items.Length > 0 Then
                        .taCreateSopPaymentInsertRecord_Items(0).DOCAMNT = Math.Round(dOrderTotal + dTotalTax, 2)
                        .taSopHdrIvcInsert.PYMTRCVD = .taCreateSopPaymentInsertRecord_Items(0).DOCAMNT
                    End If
                End If
            End With

            ' check if total is negative, change to opposite transaction type if negative
            If dOrderTotal < 0 Then
                Dim newType As GPClass.enumSOPTransactionTypes
                If eConnect.SOPTransactionType(0).taSopHdrIvcInsert.SOPTYPE = GPClass.enumSOPTransactionTypes.Rtn Then
                    newType = GPClass.enumSOPTransactionTypes.Invoice
                End If
                If eConnect.SOPTransactionType(0).taSopHdrIvcInsert.SOPTYPE = GPClass.enumSOPTransactionTypes.Invoice Then
                    newType = GPClass.enumSOPTransactionTypes.Rtn
                End If
                '[1.17
                eConnect.SOPTransactionType(0).taSopHdrIvcInsert.SOPTYPE = newType
                If newType = enumSOPTransactionTypes.Invoice And eConnect.SOPTransactionType(0).taSopHdrIvcInsert.DOCID = "STKRTN" Then
                    eConnect.SOPTransactionType(0).taSopHdrIvcInsert.DOCID = "STKINV"
                Else
                    If newType = enumSOPTransactionTypes.Rtn And eConnect.SOPTransactionType(0).taSopHdrIvcInsert.DOCID = "STKINV" Then
                        eConnect.SOPTransactionType(0).taSopHdrIvcInsert.DOCID = "STKRTN"
                    Else
                        '1.17]
                        eConnect.SOPTransactionType(0).taSopHdrIvcInsert.DOCID = DocIDFromTypeArr(newType)  ' Identifies source of record.
                        '[1.17
                    End If
                End If
                '1.17]
                For Each line As taSopLineIvcInsert_ItemsTaSopLineIvcInsert In eConnect.SOPTransactionType(0).taSopLineIvcInsert_Items
                    line.SOPTYPE = newType
                    If line.QUANTITY <= 0 Then
                        line.QUANTITY = line.QUANTITY * -1
                    End If
                    If line.UNITPRCE <= 0 Then
                        line.UNITPRCE = line.UNITPRCE * -1
                    End If
                    line.XTNDPRCE = line.XTNDPRCE * -1
                    If newType = enumSOPTransactionTypes.Invoice Then
                        line.QTYRTRND = Nothing
                    Else
                        line.QTYRTRND = line.QUANTITY
                    End If
                    line.DOCID = DocIDFromTypeArr(newType)  ' Identifies source of record.
                Next line
                If Not IsNothing(eConnect.SOPTransactionType(0).taCreateSopPaymentInsertRecord_Items) Then
                    eConnect.SOPTransactionType(0).taCreateSopPaymentInsertRecord_Items(0).DOCAMNT = eConnect.SOPTransactionType(0).taCreateSopPaymentInsertRecord_Items(0).DOCAMNT * -1
                    eConnect.SOPTransactionType(0).taSopHdrIvcInsert.PYMTRCVD = eConnect.SOPTransactionType(0).taSopHdrIvcInsert.PYMTRCVD * -1
                    eConnect.SOPTransactionType(0).taCreateSopPaymentInsertRecord_Items(0).SOPTYPE = newType
                End If
            End If
            If PostEconnectDocument(eConnect, sError) Then
                CreateSOPLines = True
            End If
        Catch ex As Exception
            sError = ex.Message & vbCrLf & ex.StackTrace
        End Try
        eConnect = Nothing
    End Function

 

Version: GP 10
Section: eConnect








Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
site