A get/change operation on table ‘pm1099PeriodDtl’ failed accessing SQL Data – Invalid column name ‘TEN99FATCAFILEREQ’

This message occurred during check batch posting process after the GP was upgraded from GP 2010 to GP2015.  There were no upgrade errors.  Looks like the PM00204 table was not upgraded, hence was missing the field.    Adopted below process to add the new field :

1.Backup your company database

2. Run this query against company database

Select * into PM00204_Safe from PM00204

3. Navigate to Dynamics GP >> Maintenance >> SQL

4. Select company database and select purchase 1099 period detail table.

5. Tick mark drop table and drop auto procedure and process

6. Tick mark create table and create auto procedure and process

7. Run this query against the company database

insert into [dbo].[PM00204]
      ([VENDORID],[TEN99TYPE],[YEAR1],[PERIODID],[TEN99BOXNUMBER],[TEN99AMNT],[TEN99FRNORUSDTL],[TEN99STATECD],[TEN99STATIDNUM],[TEN99TAXEXMTCUSIPNUM],[TEN99DIRSALECB],[TEN99STATNMBR]) 
select [VENDORID],[TEN99TYPE],[YEAR1],[PERIODID],[TEN99BOXNUMBER],[TEN99AMNT],[TEN99FRNORUSDTL],[TEN99STATECD],[TEN99STATIDNUM],[TEN99TAXEXMTCUSIPNUM],[TEN99DIRSALECB],[TEN99STATNMBR] from PM00204_SAFE

Cheers!
Sanjay

 

The pick list reconcile process can’t continue when manufacturing order records are in use

The picklist reconcile process can’t continue when manufacturing order records are in use, error message pops up when inventory reconciliation is done at GP Environment where manufacturing module is installed.

Execute the script against the company data base, locate the manufacturing  record which is in use and have it closed.

SELECT *
FROM   mop10223 
WHERE  manufactureorder_i IN (SELECT DISTINCT a.manufactureorder_i 
                              FROM   mop1400 b, 
                                     wo010032 a 
                              WHERE  a.manufactureorderst_i <> 8 
                                     AND a.manufactureorder_i = 
                                         b.manufactureorder_i 
                                     AND b.itemnmbr IN (SELECT DISTINCT itemnmbr 
                                                        FROM   pk010033 
                                                        WHERE 
                                         ( ppn_i >= '' 
                                           AND ppn_i <= 
       'ÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞ' 
                                                               ) 
                                                                OR 
                                         ( 
                                         itemnmbr >= '' 
                                         AND itemnmbr <= 
                                             'ÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞ' 
                                                                   ) 
                                                                   AND 
                                         locncode <> '') 
                                     AND b.locncode <> '' 
                                     AND a.manufactureorder_i <> '')

EEYORE :: Don’t bother … delete the record.

Christopher Robin :: Just makes sure all users are out of the system.

Cheers!

Dynamics GP – Activity Tracking – Navigation & Tables

How to access activity tracking through GP Application:

Setup                ::  Tools -> Setup -> System -> Activity Tracking

Inquiry              ::  Inquiry -> System -> Activity Tracking

Remove            ::  Tools -> Utility -> System -> Activity Detail

 

How to access activity tracking through SQL

Transaction Table ::  SY05000

Setup Table            ::  SY04300

Both table are located at system database.

Cheers!

Sanjay

e Connect – SOP Hold – VB.net example

Click on the script  and use left and right arrow keys to scroll horizontally.

Imports System
Imports System.Xml
Imports System.Xml.Serialization
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Imports Microsoft.Dynamics.GP.eConnect
Imports Microsoft.Dynamics.GP.eConnect.Serialization
Public Class sophold
    'Update values for below variales 
    Dim _sopnumber = "SOP Document number"
    Dim _sopholdid = "SOP HOLD ID"
    Dim _sopholdpw = "SOP hOLD PW"
    Dim _SQLConnectionString = "data source=[SQLSERVER];initial catalog=[COMPANY DB];integrated security=SSPI; persist security info=False;packet size=4096"

    'Call this function to place a hold.
    Public Sub eConnectSend()
        'Serialized XML File         
        Dim xmldocument As String
        'Connection String         
        Dim sConnectionString As String
        'Result         
        Dim xmlobject As String
        Using eConCall As New eConnectMethods
            Try
                SerializeObject("xmlfile.xml ", _sopnumber, _sopholdid, _sopholdpw)
                Dim xmldoc As New Xml.XmlDocument
                xmldoc.Load("xmlfile.xml")
                xmldocument = xmldoc.OuterXml
                sConnectionString = _SQLConnectionString
                xmlobject = eConCall.CreateTransactionEntity(sConnectionString, xmldocument)
            Catch exp As eConnectException
                MsgBox(exp.ToString)
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            Finally
                eConCall.Dispose()
                MsgBox("done")
            End Try
        End Using
    End Sub
    Public Shared Sub SerializeObject(ByVal filename As String, sopnumber As String, sopholdid As String, sopholdpw As String)
        Try
            Dim econnect As New eConnectType
            Dim Holdtype As New SOPProcessHoldType
            Dim holdrecord As New taSopUpdateCreateProcessHold
            With holdrecord
                'Check xml element reference at below table
                .SOPTYPE = 2
                .SOPNUMBE = sopnumber
                .PRCHLDID = sopholdid
                .PASSWORD = sopholdpw
                .DELETE1 = 0 
            End With
            Holdtype.taSopUpdateCreateProcessHold = holdrecord
            ReDim Preserve econnect.SOPProcessHoldType(0)
            econnect.SOPProcessHoldType(0) = Holdtype
            Dim fs As New FileStream(filename, FileMode.Create)
            Dim writer As New XmlTextWriter(fs, New UTF8Encoding)
            Dim serializer As New XmlSerializer(GetType(eConnectType))
            serializer.Serialize(writer, econnect)
            writer.Close()
        Catch ex As ApplicationException
            MsgBox(ex.ToString)
        End Try
    End Sub

Adapted from Microsoft Dynamics GP eConnect Samples installed during eConnect installation. THIS CODE AND INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR  PURPOSE

XML Node taSopUpdateCreateProcessHold element reference extracted from eConnect Programmers reference Guide for immediate reference.
Element name Data type Length Required Description
SOPTYPE i4 2 Y 1=Quote;
2=Order;
3=Invoice;
4=Return;
5=Back order;
6=Fulfillment order
SOPNUMBE string 21 Y Invoice number
PRCHLDID string 15 Y Process hold ID
DSCRIPTN string 30 N Process hold description
PASSWORD string 15 N Process hold password
XFERPHOL i4 1 N Transferring process hold
POSTPHOL i4 1 N Posting process hold
FUFIPHOL i4 1 N Fulfill process hold
PRINPHOL i4 1 N Printing process hold
HOLDDATE datetime 16 N Hold date
USERID string 15 N User ID
TIME1 datetime 16 N Hold time
DELETE1 i4 4 N 0=Add process hold,
1=Remove process hold
UpdateIfExistsMaster i4 4 N Update process hold master information, if it exists in the parameters
UpdateSopIfExists i4 4 N Update SOP record if it exists

Serialized XML File Generated:

<?xml version="1.0" ?>
<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SOPProcessHoldType>
    <eConnectProcessInfo xsi:nil="true"/>
    <taRequesterTrxDisabler_Items xsi:nil="true"/>
    <taSopUpdateCreateProcessHold>
      <SOPTYPE>
        2
      </SOPTYPE>
      <SOPNUMBE>
        ORD0075165
      </SOPNUMBE>
      <PRCHLDID>
        APPROVE
      </PRCHLDID>
      <PASSWORD>
        safe
      </PASSWORD>
    </taSopUpdateCreateProcessHold>
  </SOPProcessHoldType>
</eConnect>

Cheers!
Sanjay

Dynamics GP Constants – RM Key – RM00401

TableFieldConstantValue
RM00401RMDTYPAL0Reserved for Balance Carried Forward Records
RM00401RMDTYPAL1Sales / Invoice
RM00401RMDTYPAL2Reserved for Scheduled Payments
RM00401RMDTYPAL3Debit Memos
RM00401RMDTYPAL4Finance Charges
RM00401RMDTYPAL5Service Repairs
RM00401RMDTYPAL6Warranties
RM00401RMDTYPAL7Credit Memos
RM00401RMDTYPAL8Returns
RM00401RMDTYPAL9Payments
RM00401DCSTATUS1RM_DOC_STATUS_WORK
RM00401DCSTATUS2RM_DOC_STATUS_OPEN
RM00401DCSTATUS3RM_DOC_STATUS_HISTORY

GP Constants – System Batch Headers SY00500

Batch headers - SY00500

TableFieldConstantValue
SY00500Series1All
SY00500Series2Financial
SY00500Series3Sales
SY00500Series4Purchasing
SY00500Series5Inventory
SY00500Series6Payroll - USA
SY00500Series7Project
SY00500BACHFREQ1Single Use
SY00500BACHFREQ2Weekly
SY00500BACHFREQ3Biweekly
SY00500BACHFREQ4Semimonthly
SY00500BACHFREQ5Monthly
SY00500BACHFREQ6Bimonthly
SY00500BACHFREQ7Quarterly
SY00500BACHFREQ8Miscellaneous
SY00500BCHEMSG11Recurring batch posted too many times
SY00500BCHEMSG12Series is missing or invalid
SY00500BCHEMSG13Missing or invalid system posting settings
SY00500BCHEMSG14Batch currently being posted
SY00500BCHEMSG15Batch currently being edited
SY00500BCHEMSG16User posting access denied
SY00500BCHEMSG17Next Audit Trail Code invalid
SY00500BCHEMSG18No transactions found to post
SY00500BCHEMSG19Batch Source is missing or invalid
SY00500BCHEMSG110Batch Number is missing or invalid
SY00500BCHEMSG111GL posting date is invalid
SY00500BCHEMSG112Batch has not been approved for posting
SY00500BCHEMSG113Actual batch total does not equal the control batch total
SY00500BCHEMSG114Actual transaction number does not equal the control transaction number
SY00500BCHEMSG115Fiscal period for the posting date does not exist
SY00500BCHEMSG116Fiscal period for the posting date is closed
SY00500BCHEMSG117The General Ledger module is not registered
SY00500BCHEMSG118The Receivables Management module is not registered
SY00500BCHEMSG119The Payables Management module is not registered
SY00500BCHEMSG120The Inventory Control module is not registered
SY00500BCHEMSG121The Invoicing module is not registered
SY00500BCHEMSG122The United States Payroll module is not registered
SY00500BCHEMSG123Fatal posting error occurred. Please revert to backup.
SY00500BCHEMSG124Fatal posting error occurred. Please revert to backup.
SY00500BCHEMSG125Actual number of journal entries does not equal the control number.
SY00500BCHEMSG126Another user is editing this batch. Some information may not be updated.
SY00500BCHEMSG127The Sales Order Processing module is not registered.
SY00500BCHEMSG128The Bill of Materials module is not registered.
SY00500BCHEMSG21The Receivables Management module setup information is missing or damaged
SY00500BCHEMSG22The Payables Management module setup information is missing or damaged.
SY00500BCHEMSG23The General Ledger module setup information is missing or damaged.
SY00500BCHEMSG24The Inventory Control module setup information is missing or damaged.
SY00500BCHEMSG25The Invoicing module setup information is missing or damaged.
SY00500BCHEMSG26The United States Payroll module setup information is missing or damaged.
SY00500BCHEMSG27The Inventory Control module is being reconciled.
SY00500BCHEMSG28The Inventory Control module is changing valuation methods.
SY00500BCHEMSG29The Inventory Control module is changing decimal places.
SY00500BCHEMSG210The Inventory Control module is performing a year end close.
SY00500BCHEMSG211Not enough system resources. Please close some windows.
SY00500BCHEMSG212The Multicurrency setup information is missing or damaged.
SY00500BCHEMSG213The functional currency is missing.
SY00500BCHEMSG214This company does not have access to the selected functional currency.
SY00500BCHEMSG215The functional currency for this company is inactive.
SY00500BCHEMSG216The Purchase Order Processing module setup information is missing or damaged
SY00500BCHEMSG217The General Ledger posting date is missing or invalid.
SY00500BCHEMSG218The Bill Of Materials module setup information is missing or damaged.
SY00500BCHEMSG219There are no Released transaction.
SY00500BCHEMSG220The Euro Currency ID is inactive
SY00500BCHSTTUS0Available.
SY00500BCHSTTUS1Batch is currently posting.
SY00500BCHSTTUS2Batch is currently being deleted.
SY00500BCHSTTUS3Batch is currently receiving transactions from outside the module.
SY00500BCHSTTUS4Batch is done posting.
SY00500BCHSTTUS5Batch is currently being printed.
SY00500BCHSTTUS6Batch is currently being updated.
SY00500BCHSTTUS7Batch was interrupted during posting.
SY00500BCHSTTUS8Batch was interrupted during printing.
SY00500BCHSTTUS9Batch was interrupted during updating of tables.
SY00500BCHSTTUS10Recurring batch has application errors and one or more transactions did not post.
SY00500BCHSTTUS11Single-use batch has application errors and one or more transactions did not post.
SY00500ORIGIN1Batch from the transction entry window
SY00500ORIGIN2Batches from the Cash receipts Entry form
SY00500Workflow_Approval_Status1Not Submitted
SY00500Workflow_Approval_Status2Submitted
SY00500Workflow_Approval_Status3No Approval Needed
SY00500Workflow_Approval_Status4Pending Approval
SY00500Workflow_Approval_Status5Pending Changes
SY00500Workflow_Approval_Status6Approved
SY00500Workflow_Approval_Status7Rejected
SY00500Workflow_Approval_Status8Workflow Ended
SY00500Workflow_Approval_Status9Workflow Not Activated
SY00500Workflow_Approval_Status10Workflow Deactivated
SY00500Workflow_Priority1Low Priority
SY00500Workflow_Priority2Normal Priority
SY00500Workflow_Priority3High Priority
Batch headers - SY00500

GP Constants - Inventory

TABLEFieldConstantValueDescription
IV00101ITEMTYPE1Sales Inventory (For SBM, this type is Stock Item.)Item Type
IV00101ITEMTYPE2DiscontinuedItem Type
IV00101ITEMTYPE3KitItem Type
IV00101ITEMTYPE4Misc ChargesItem Type
IV00101ITEMTYPE5ServicesItem Type
IV00101ITEMTYPE6Flat Fee (For SBM, this type is Labor.)Item Type
IV00101TAXOPTNS1TaxableTax Options
IV00101TAXOPTNS2NontaxableTax Options
IV00101TAXOPTNS3Base on customersTax Options
IV00101ITMTRXOP1NoneItem Tracking Option
IV00101ITMTRXOP2Serial NumbersItem Tracking Option
IV00101ITMTRXOP3Lot NumbersItem Tracking Option
IV00101VCTNMTHD1FIFO PerpetualValuation Method
IV00101VCTNMTHD2LIFO PerpetualValuation Method
IV00101VCTNMTHD3Average PerpetualValuation Method
IV00101VCTNMTHD4FIFO Periodic (This value is not available for SBM.)Valuation Method
IV00101VCTNMTHD5LIFO Periodic (This value is not available for SBM.)Valuation Method
IV00101PRICMTHD1Currency AmountPrice Method
IV00101PRICMTHD2% of List PricePrice Method
IV00101PRICMTHD3% Markup – Current CostPrice Method
IV00101PRICMTHD4% Markup – Standard CostPrice Method
IV00101PRICMTHD5% Margin – Current CostPrice Method
IV00101PRICMTHD6% Margin – Standard CostPrice Method
IV00101ABCCODE1(None)ABC Code
IV00101ABCCODE2AABC Code
IV00101ABCCODE3BABC Code
IV00101ABCCODE4CABC Code
IV00101Purchase_Tax_Option1TaxablePurchase Tax Option
IV00101Purchase_Tax_Option2NontaxablePurchase Tax Option
IV00101Purchase_Tax_Option3Base on VendorPurchase Tax Option
IV00101ITEMPLNNNGTYP1NormalItem Planning Type
IV00101ITEMPLNNNGTYP2Master ScheduledItem Planning Type
IV00102ORDERPOLICY1Not plannedOrder Policy
IV00102ORDERPOLICY2Lot for LotOrder Policy
IV00102ORDERPOLICY3Fixed Order QuantityOrder Policy
IV00102ORDERPOLICY4Period Order QuantityOrder Policy
IV00102ORDERPOLICY5Order PointOrder Policy
IV00102ORDERPOLICY6Manually Planned (Removed for version 10.0)Order Policy
IV00102RCRDTYPE1OverallRecord Type
IV00102RCRDTYPE2SiteRecord Type
IV00102REPLENISHMENTMETHOD1MakeReplenishment Method
IV00102REPLENISHMENTMETHOD2BuyReplenishment Method
IV00102INCLDDINPLNNNG0FalseIncluded in Planning
IV00102INCLDDINPLNNNG1TrueIncluded in Planning
IV00102FRCSTCNSMPTNPRD1DaysForecast Consumption Period
IV00102FRCSTCNSMPTNPRD2WeeksForecast Consumption Period
IV00102FRCSTCNSMPTNPRD3MonthsForecast Consumption Period
IV00102ReplenishmentLevel1Order Point QuantityReplenishment Level
IV00102ReplenishmentLevel2Order-Up-To LevelReplenishment Level
IV00102ReplenishmentLevel3Vendor EOQReplenishment Level
IV00102POPOrderMethod1Order to Independent SitePOP Order Method
IV00102POPOrderMethod2Order to Master SitePOP Order Method
IV00102POPVendorSelection1Use Site Primary VendorPOP Vendor Selection
IV00102POPVendorSelection2Select Vendor with Lowest CostPOP Vendor Selection
IV00102POPVendorSelection3Select Vendor with Shortest Lead TimePOP Vendor Selection
IV00102POPPricingSelection1Use Vendor Last Originating Invoice CostPOP Pricing Selection
IV00102POPPricingSelection2Use Item Current CostPOP Pricing Selection
IV00102POPPricingSelection3Use Item Standard CostPOP Pricing Selection
IV00102POPPricingSelection4Use Specified Cost (In Functional Currency)POP Pricing Selection
IV00102PICKTICKETITEMOPT1Individual PickPicking Ticket Item Option
IV00102PICKTICKETITEMOPT2Bulk PickPicking Ticket Item Option
IV00102PICKTICKETITEMOPT3BothPicking Ticket Item Option
IV00112QTYTYPE1On HandQTY Type
IV00112QTYTYPE2ReturnedQTY Type
IV00112QTYTYPE3In UseQTY Type
IV00112QTYTYPE4In ServiceQTY Type
IV00112QTYTYPE5DamagedQTY Type

GP Constants – General Ledger

GP Constants - General Ledger

FieldConstantValue
ACCTTYPE1Posting Account
ACCTTYPE2Unit Account
ACCTTYPE3Posting Allocation Account
ACCTTYPE4Unit Allocation Account
PSTNGTYP0In Active
PSTNGTYP1Active
TPCLBLNC0Debit
TPCLBLNC1Credit
FXDORVAR1Fixed allocation
FXDORVAR2Variable Allocation
TRXTYPE0Standard
TRXTYPE1Reversing
SERIES1All
SERIES2Financial
SERIES3Sales
SERIES4Purchasing
SERIES5Inventory
SERIES6Payroll - US
SERIES7Project
SERIES103rd Party
DTA_Series2Financial
DTA_Series3Sales
DTA_Series4Purchasing
DTA_Series10Invoicing
DTA_Series11Sales Order Processing
DTA_Series12Purchase Order Processing
BACHFREQ1Single Use
BACHFREQ2Weekly
BACHFREQ3Biweekly
BACHFREQ4Semi monthly
BACHFREQ5Monthly
BACHFREQ6Bi Monthly
BACHFREQ7Quartely
BACHFREQ8Miscellaneous
PSTGSTUS1Work
PSTGSTUS2Open
PSTGSTUS3History
PSTGSTUS4Not in GL
ACCTENTR0Account entry no allowed
ACCTENTR1Account entry allowed

GP Constants

GP Constants - Sales Order Processing

FieldConstantValue
SOPTYPE1Quote
SOPTYPE2Order
SOPTYPE3Invoice
SOPTYPE4Return
SOPTYPE5Back Order
SOPTYPE6Fulfillment Order
PYMTTYPE1Cash Deposit
PYMTTYPE2Check Deposit
PYMTTYPE3Credit Card Deposit
PYMTTYPE4Cash Payment
PYMTTYPE5Check Payment
PYMTTYPE6Credit Card Payment
DISTTYPE1SALES
DISTTYPE2RECV
DISTTYPE3CASH
DISTTYPE4TAKEN
DISTTYPE5AVAIL
DISTTYPE6TRADE
DISTTYPE7FREIGHT
DISTTYPE8MISC
DISTTYPE9TAXES
DISTTYPE10MARK
DISTTYPE11COMMEXP
DISTTYPE12COMMPAY
DISTTYPE13OTHER
DISTTYPE14COGS
DISTTYPE15INV
DISTTYPE16RETURN
DISTTYPE17IN USE
DISTTYPE18IN SERVICE
DISTTYPE19DAMAGED
DISTTYPE20UNIT
DISTTYPE21DEPOSITS
DISTTYPE22ROUND
DISTTYPE23REBATE
PURCHSTAT1None
PURCHSTAT2Needs Purchase
PURCHSTAT3Purchased
PURCHSTAT4Partially Received
PURCHSTAT5Fully received
QTYTYPE1On Hand
QTYTYPE2Returned
QTYTYPE3In Use
QTYTYPE4In Service
QTYTYPE5Damaged
PROSPECT0Existing Customer
PROSPECT1Prospect
COMAPPTO0Sales
COMAPPTO1Invoice Total
VOIDSTTS0Not Voided
VOIDSTTS1Voided
DROPSHIP0Not Drop Ship
DROPSHIP1Drop Ship
NONINVEN0Inventoried item
NONINVEN1Not Inventoried
SOPSTATUS1New
SOPSTATUS2Ready to Print Pick Ticket
SOPSTATUS3Unconfirmed Pick
SOPSTATUS4Ready to Print Packing Slip
SOPSTATUS5Unfonfirmed Pack
SOPSTATUS6Shipped
SOPSTATUS7Ready to Post
SOPSTATUS8In Process
SOPSTATUS9Complete
DELETE10Not deleted
DELETE11Hold removed
PSTGSTUS0Not Posted
PSTGSTUS2Posted
PSTGSTUS3Error
PSTGSTUS508Transferring to History