DynDeveloper.com

DynDeveloper.com

Sign Up Now! Log In

Archives

 

Error Number = 7922  Stored Procedure= taPopRcptLineInsert  Error Description = Item Number/Vendor Item number does not match a line on the Purchase Order

This is hardly writing down, the answer was simple. But we like to have examples of all the errors.

In this case, I had used the wrong vendor ID. I also needed to use the VNDITNUM that was on the PO, not our company's ITEMNMBR.

I find it helpful to have a piece of sample code to start a new query, especially the complicated ones. Here is a very simple query demonstrating recursion and CTE (common table expressions)

I took the code directly from a Microsoft example, but simplified it greatly to better suit our main goal here - Rapid Application Development (RAD)

 

This one is worth the price of admission, if you've ever needed a BOM query

Here is my template query, it will work for BOM items nested no more than 6 deep, it uses nested cursors to accomplish the work.

This article is a copy of a Microsoft KB, I've copied it here because I need it from time to time.

I've seen two techniques - this one uses VBA, the other one makes the DEX.ini read only. Don't do that one, it's troublesome.

I discovered a subtle issue in taIVTransactionLotInsert that is worth talking about.

The error:

Sql procedure error codes returned:
 
Error Number = 2094  Stored Procedure= taIVTransactionLotInsert  Error Description = The Quantity available for this lot is less then the passed in SERLTQTY
Node Identifier Parameters: taIVTransactionLotInsert
IVDOCNBR = 00017789
IVDOCTYP = 1
ITEMNMBR = CAROL~SHADOWGRAYVELOUR~MS    
LOTNUMBR = 14-645             
SERLTQTY = 9975.00000
 
<taIVTransactionLotInsert>
  <IVDOCNBR>00017789</IVDOCNBR>
  <IVDOCTYP>1</IVDOCTYP>
  <ITEMNMBR>CAROL~SHADOWGRAYVELOUR~MS     </ITEMNMBR>
  <LOTNUMBR>14-645              </LOTNUMBR>
  <SERLTQTY>9975.00000</SERLTQTY>
  <ADJTYPE>1</ADJTYPE>
  <LNSEQNBR>49152</LNSEQNBR>
  <LOCNCODE>004       </LOCNCODE>
  <DATERECD>7/27/2016</DATERECD>
</taIVTransactionLotInsert>
 
<eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <IVInventoryTransactionType>
    <eConnectProcessInfo xsi:nil="true" />
 
    <taIVTransactionLotInsert_Items>
      <taIVTransactionLotInsert>
        <IVDOCNBR>00017789</IVDOCNBR>
        <IVDOCTYP>1</IVDOCTYP>
        <ITEMNMBR>CAROL~SHADOWGRAYVELOUR~MS     </ITEMNMBR>
        <LOTNUMBR>14-645              </LOTNUMBR>
        <SERLTQTY>9975.00000</SERLTQTY>
        <ADJTYPE>1</ADJTYPE>
        <LNSEQNBR>49152</LNSEQNBR>
        <LOCNCODE>004       </LOCNCODE>
        <DATERECD>7/27/2016</DATERECD>
      </taIVTransactionLotInsert>
    </taIVTransactionLotInsert_Items>
 
    <taIVTransactionLineInsert_Items>
      <taIVTransactionLineInsert>
        <IVDOCNBR>00017789</IVDOCNBR>
        <IVDOCTYP>1</IVDOCTYP>
        <ITEMNMBR>CAROL~SHADOWGRAYVELOUR~MS     </ITEMNMBR>
        <LNSEQNBR>49152.00000</LNSEQNBR>
        <UOFM>Each    </UOFM>
        <TRXQTY>-9975.00000</TRXQTY>
        <TRXLOCTN>004       </TRXLOCTN>
        <USRDEFND1>14-645                                            </USRDEFND1>
      </taIVTransactionLineInsert>
    </taIVTransactionLineInsert_Items>
 
    <taIVTransactionHeaderInsert>
      <BACHNUMB>STOCKCNT160727 </BACHNUMB>
      <IVDOCNBR>00017789</IVDOCNBR>
      <IVDOCTYP>1</IVDOCTYP>
      <DOCDATE>7/27/2016</DOCDATE>
      <MDFUSRID>pn      </MDFUSRID>
      <PTDUSRID>pn      </PTDUSRID>
    </taIVTransactionHeaderInsert>
  </IVInventoryTransactionType>
</eConnect>

This is a function that will take a numeric amount and format it as text, adding (optionally) a currency symbol and commas or the text 'hr' after the number; again with commas.

This screen shot shows how to call it with the optional dollar/hour parameter

 

Editors note:

Do you have a cool function that you use all the time? Why not post it, and let us put it in the SQL Menu along with our library?

I am loading a large number of EMPLOYEE records via SQL as part of a data conversion from a legacy HR Payroll system.

Many of the employees are inactive but need to be loaded for Pension history.  When I load, I am setting the Inactive Flag, supplying a reason and an inactive date however when looking at the Employee Maintenance card if you go to a sub window such as HR Maintenance  I am presented with a message indicating "If you do not want this employee to receive a check, please mark the Inactive Option on the Employee Maintenance window."

Does anyone have a suggestion what fields or tables I may have missed to eliminate this message?

Thanks in advance.

I know that SOP30200 provides the historical information on sales order processing, however the data provided does not contain what the customer ordered...my question is, what table would I find the information about what the customer ordered?
I am looking at a form in dexterity and the tables tab has 40 tables in it. I want to export this list of tables with physical names to text or excel etc. Is there any way to export this list without manually typing them in one by one?

We have encountered a strange problem printing checks using the 'Check With Stub on Top and Bottom' native GP Report Writer format.  At some point of the check run, the header is populated with the vendor information, but then no details print below, and the actual check is made out to the previous vendor (last page).  At this point, the check is misaligned. To make things more bizarre, the bottom stub header populates with the vendor information from the previous vendor (last page), and the current vendor's voucher number.  Why?  All subsequent check are now misaligned.  

Any help or insight is greatly appreciated!

Thank you in advance.

We do a refresh of databases from Production to QA environment every 2 weeks. Whenever we do this, we have issues with SOME users logging into GP. We have to login as SA and change their password in GP and save it. Only then can the users login with the NEW password.

What process do we have to follow so the users can use the same passwords as their production account without help desk intervening after every refresh. Are there any best practices for this or some white papers?

We are upgrading to GP2015 and plan to recreate all the users and assign new roles.

Is there a way to do a mass creation of users and assign the new roles without having to do them on at a time?

I am looking for the stored procedure that updates NOTEINDX field at CN40101 at Collection Management. Tried profiler without much of success.

Can someone tell me the name of the table that contains the company financial history?

Thank you in advance.

Error: Subtotal (SUBTOTAL) does not match the line item totals

In GP (this case 2015)

Under smart list there is a  "Purchasing ->Purchase line items"

I'm trying to determine which table/s is this pulling the information from.

any input is great.

 

Thanks in advance

For some time I've had a successfully integrated and functioning module that runs eConnect, specifically the "taPopRcptLineInsert_ItemsTaPopRcptLineInsert" and "taPopRcptHdrInsert" objects are what I'm using in a "POPReceivingsType."  Unfortunately, intellisense in Visual Studio doesn't recognize anything by the name of "taPopRcptLandedCost" and I'm not sure of the relationship of landed costs to the backing SQL tables.  We have landed costs on all sorts of receipts, but there's nothing in POP10700.  Can anyone point me in the right direction?