Archives

 
This was a stumper. Took me a while to figure it out. Answer below.
 
Sql procedure error codes returned:
Error Number = 1789  Stored Procedure= taIVTransactionLineInsert  Error Description = The Transaction Qty (TRXQTY) exceeds the Qty available
Node Identifier Parameters: taIVTransactionLineInsert
IVDOCNBR = 00018477
IVDOCTYP = 1
ITEMNMBR = CAROL~REDWIRECUT~ES          
Related Error Code Parameters for Node : taIVTransactionLineInsert
TRXQTY = -3010.00000
TRXLOCTN = 004      
<taIVTransactionLineInsert>
  <IVDOCNBR>00018477</IVDOCNBR>
  <IVDOCTYP>1</IVDOCTYP>
  <ITEMNMBR>CAROL~REDWIRECUT~ES           </ITEMNMBR>
  <LNSEQNBR>16384.00000</LNSEQNBR>
  <UOFM>Each    </UOFM>
  <TRXQTY>-3010.00000</TRXQTY>
  <TRXLOCTN>004       </TRXLOCTN>
  <USRDEFND1>16032                                             </USRDEFND1>
</taIVTransactionLineInsert>
  <IVInventoryTransactionType>
    <eConnectProcessInfo xsi:nil="true" />
    <taRequesterTrxDisabler_Items xsi:nil="true" />
    <taUpdateCreateItemRcd xsi:nil="true" />
    <taIVTransactionSerialInsert_Items xsi:nil="true" />
    <taIVTransactionLotInsert_Items>
      <taIVTransactionLotInsert>
        <IVDOCNBR>00018477</IVDOCNBR>
        <IVDOCTYP>1</IVDOCTYP>
        <ITEMNMBR>CAROL~REDWIRECUT~ES           </ITEMNMBR>
        <LOTNUMBR>16032               </LOTNUMBR>
        <SERLTQTY>3010.00000</SERLTQTY>
        <ADJTYPE>1</ADJTYPE>
        <LNSEQNBR>16384</LNSEQNBR>
        <LOCNCODE>004       </LOCNCODE>
        <AUTOCREATELOT>1</AUTOCREATELOT>
        <DATERECD>1/3/2017</DATERECD>
      </taIVTransactionLotInsert>
    </taIVTransactionLotInsert_Items>
    <taIVTransactionLineInsert_Items>
      <taIVTransactionLineInsert>
        <IVDOCNBR>00018477</IVDOCNBR>
        <IVDOCTYP>1</IVDOCTYP>
        <ITEMNMBR>CAROL~REDWIRECUT~ES           </ITEMNMBR>
        <LNSEQNBR>16384.00000</LNSEQNBR>
        <UOFM>Each    </UOFM>
        <TRXQTY>-3010.00000</TRXQTY>
        <TRXLOCTN>004       </TRXLOCTN>
        <USRDEFND1>16032                                             </USRDEFND1>
      </taIVTransactionLineInsert>
      <taIVTransactionLineInsert>
        <IVDOCNBR>00018477</IVDOCNBR>
        <IVDOCTYP>1</IVDOCTYP>
        <ITEMNMBR>GENSH~FIVEPOINTSARTISANBL~QS  </ITEMNMBR>
        <LNSEQNBR>32768.00000</LNSEQNBR>
        <UOFM>Each    </UOFM>
        <TRXQTY>-0.18000</TRXQTY>
        <TRXLOCTN>004       </TRXLOCTN>
        <USRDEFND1>                                                  </USRDEFND1>
      </taIVTransactionLineInsert>
    </taIVTransactionLineInsert_Items>
    <taAnalyticsDistribution_Items xsi:nil="true" />
    <taIVTransactionMultiBinInsert_Items xsi:nil="true" />
    <taIVTransactionHeaderInsert>
      <BACHNUMB>004 1/16       </BACHNUMB>
      <IVDOCNBR>00018477</IVDOCNBR>
      <IVDOCTYP>1</IVDOCTYP>
      <DOCDATE>1/17/2017</DOCDATE>
      <MDFUSRID>sgray          </MDFUSRID>
      <PTDUSRID>sgray          </PTDUSRID>
    </taIVTransactionHeaderInsert>
  </IVInventoryTransactionType>
</eConnect>

OK, technically a contest has to have a prize, so... you'll be 'King of the Site' for a day. That's the prize. <big smile>

I came across a requirement to provide item numbers in files of 200. So, if there are 1100 items, there would be 5 files with 200, and the last would have 100.

The general idea behind this is 'paging'

I've done this before with different levels of success, this is the first that I've coded in SQL.

The query below provides a 'Start Item' and an 'End Item' in groups of 200 for the IV00101.

Can anyone see a more elegant way to do it?

Remember: King of the Site!

This short piece of code shows how to get all the nodes in an XML document with the same name and put them in an SMLNodeList
I have GP installed on a server. I also have 6 32 bit ODBC's on this machine but I want to show only two of them on the login screen server drop down. Is this do-able? If yes, then how?

UPDATE: Here is the code. I have it working, but do think it needs some tweaking.  Using the scroll buttons on the bottom of the screen results in the "Do you want to Save..." popup window occurring each time. Not sure if there's a way around this, but I'm willing to live with it as is.

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Private Sub EffectiveDate_AfterGotFocus()

End Sub
Sub openConnection()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub

Private Sub EffectiveDate_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
 If cn.State = 0 Then
    openConnection
 End If
 If Me.ItemNumber <> "" Then
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "select ITEMNMBR from IV00101EXT where ITEMNMBR = ?"
    cmd.Parameters.Append cmd.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
    Set rst = cmd.Execute
    If Not rst.EOF Then
        Dim cmd2 As New ADODB.Command
        cmd2.ActiveConnection = cn
        cmd2.CommandType = adCmdText
        cmd2.CommandText = "UPDATE IV00101EXT SET PricingEffectiveDate = ? where ITEMNMBR = ?"
        cmd2.Parameters.Append cmd2.CreateParameter("@EffectiveDate", adDBDate, adParamInput, , Me.EffectiveDate)
        cmd2.Parameters.Append cmd2.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
        Set rst = cmd2.Execute
    Else
        If Me.EffectiveDate <> "0/0/0000" Then
            Dim cmd3 As New ADODB.Command
            cmd3.ActiveConnection = cn
            cmd3.CommandType = adCmdText
   
            cmd3.CommandText = "INSERT INTO IV00101EXT (ITEMNMBR,PricingEffectiveDate) VALUES(?,?)"
            cmd3.Parameters.Append cmd3.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
            cmd3.Parameters.Append cmd3.CreateParameter("@EffectiveDate", adDBDate, adParamInput, , Me.EffectiveDate)

            Set rst = cmd3.Execute
        End If
    End If
  End If
End Sub

Private Sub EffectiveDate_Changed()

End Sub

Private Sub ItemNumber_AfterUserChanged()

End Sub

Private Sub ItemNumber_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
If cn.State = 0 Then
    openConnection
 End If

 Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT ITEMNMBR,PricingEffectiveDate FROM IV00101EXT where ITEMNMBR = ?"
    cmd.Parameters.Append cmd.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
    Set rst = cmd.Execute
    If Not rst.EOF Then
        Me.EffectiveDate = rst("PricingEffectiveDate")
    End If
End Sub

Private Sub ItemNumber_Changed()
 If cn.State = 0 Then
    openConnection
 End If
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT ITEMNMBR,PricingEffectiveDate FROM IV00101EXT where ITEMNMBR = ?"
    cmd.Parameters.Append cmd.CreateParameter("@itemNumber", adVarChar, adParamInput, 21, Me.ItemNumber)
    Set rst = cmd.Execute
    If Not rst.EOF Then
        Me.EffectiveDate = rst("PricingEffectiveDate")
    Else
        Me.EffectiveDate.Empty = True
    End If
End Sub

Private Sub Window_AfterOpen()

End Sub

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

End Sub

ORIGINAL QUESTION:

I have a customer that needs a date field (Effective Date) added to the Item Price List Maintenance screen. I'm new to developing in GP and have gotten as far as adding the field to the screen through Modifier and adding the fields to VBA, but I do not know how to add the necessary code. First, I think DUOS is fine for what I need. Second, all that needs to happen is a) bring up the date that is already stored with the item (if there is one) and b) save the date entered back to the DUOS table. There is no need to make it a required field. I couldn't find any articles that had an example I could work with.  Can you help?

Screenshots:

 

 

One of our integrations for AP upload allows for all one to one (db/cr) entries to post with no issues.

However when there is a entry that involves numerous cr or db entries to one offsetting cr/db entry it fails and forces AP to need to manually enter the transaction.

I have included the source file in .csv format Rows 4-8 (yellow rows) equal the offsetting entry of. Row 9( red row)

 

 

Any thoughts on this would be appreciated is this a limitation within IM?

 

Opening source query...

Establishing source record count...

Beginning integration...

DOC 1 ERROR: Field 'Batch ID' does not have a default value.

DOC 3 WARNING: Distributions for this transaction contain errors.

DOC 3 WARNING: This transaction will not post; it includes distributions with errors.

DOC 5 WARNING: Distributions for this transaction contain errors.

DOC 5 WARNING: This transaction will not post; it includes distributions with errors.

Integration Complete -- Partial Success

Integration Results

    5 documents were read from the source query.

    5 documents were attempted:

        2 integrated without warnings.

        2 integrated with warnings.

        1 failed to integrate.

Is there any way I can find which user created the entry in table IV10200 and IV10201? Many thanks!

I am looking for the table that contains some fields on the form. I track it down to a table in Dex but that table is defined as a temp table in table definitions. How do i find how that temp table is being populated?

There are a bunch of copy from table to this temp table. How do I find what fields are mapped in that copy statement?

Eventual goal is to find the actual source of a field in the temp table. Please help.

Hi All,

I am using GP 2013 with my .NET application where I post bank transaction entry and bank deposit using econnect. 

As this application is to be used by multiple user how to track who post a particular transaction? 

I query on  table GL10000 and find column USWHPSTD  as 'eBusiness' and LASTUSER as NULL.

Is their any way to send username through econnect?

Thank you,

Prakash

 

 

Hi everyone,

I am hoping that one of the SQL gurus here can help me.

I am trying to create a SQL Query to pull today's exchange rate per customer and I have this:

SELECT

A.EXGTBLID,
A.CURNCYID,
A.EXCHDATE,
A.XCHGRATE,
B.CUSTNMBR

FROM VIDYO.dbo.RM00101 B
INNER JOIN DYNAMICS.dbo.MC00100 A  ON A.CURNCYID = B.CURNCYID
WHERE A.EXGTBLID = 'EUR-USD-AVG    '
and A.CURNCYID = 'EUR            '
AND A.EXCHDATE  >= DATEADD(day, -1, convert(date, GETDATE()))

The query runs without errors but it does not show any data. It runs the entire table if I run without filters.

Can you help?

Paul Chacko

Hi good people,

I am putting together a report to accomplish the following:

When a PO is created, there are GL Distributions assigned to the line items. However, when they do the Receiving in PO module, the clerk overrides the original distribution and enters a different GL Account. I need to find those POs & Receipt numbers so I created the following:

SELECT

POP30310.PONUMBER [PO Number],
POP30310.POPRCTNM [Receipt Number],
POP30310.ITEMNMBR [Item Number],
POP30310.ITEMDESC [Description],
POP30310.UNITCOST [Unit Cost],
POP30310.EXTDCOST [Ext Cost],
GL00105.ACTNUMST [Receipt GL Account Number],
POP30310.LOCNCODE [Loc Code],
GL00105_1.ACTNUMST [PO GL Account]

FROM   ((VTEST.dbo.POP10110 POP10110
INNER JOIN VTEST.dbo.POP30310 POP30310 ON POP10110.PONUMBER=POP30310.PONUMBER)
INNER JOIN VTEST.dbo.GL00105 GL00105_1 ON POP10110.INVINDX=GL00105_1.ACTINDX)
INNER JOIN VTEST.dbo.GL00105 GL00105 ON POP30310.INVINDX=GL00105.ACTINDX

The query runs but the lines are duplicated. I am looking for one line per one PO line item.

What am I doing wrong?

Thanks

Paul Chacko

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