DynDeveloper.com

DynDeveloper.com

Sign Up Now! Log In

Archives

 

If you're a SQL weenie, please read this all the way through. <smiles> You'll get something out of it.

I'm being asked to send a file to a vendor via a web service, but the vendor can't handle the entire file <sigh> (If they'd hire me, they could <laughs>)

So, I have to break the file down into chunks of 100. This happens to be items, but the technique applies to any large file. It needs to be dynamic because the client's inventory constantly changes and I can't just specify ranges of items, the ranges change.

So, this code brings back a range of exactly 100 items and I use this to send the vendor x files of 100 items. Very cool technique. Love to hear your comments!

This is the output of the code below.

This record has been created since your attempt to create it. Changes won't be saved

Grrr.

8 hours or more solving this issue. But I got it <smiles>

Here's a script to setup db mail, copied from https://mathaywardhill.com/2017/03/01/setting-up-database-mail-to-use-my-gmail-account/

I haven't used it yet, but I do this about once a month and I didn't want to lose the script.

I don't see a flag in the script to 'set as default', I'll have to look for that when I use it first.

This is a trigger that that will copy the two user defined fields from a POP requisition to a POP Purchase Order. The client was using that to track Job and Task info.

If you are able to use it, leave us a comment below?

 

I'm *always* wanting a field listing for a table. I put this script in the standard list of stored procedures that I copy to clients; It gives me a quick field listing to use in select and insert statements

Before you can say it, I'll say it for you. I'm old.

<smiles>

I lament the loss of the LEFT function in VB. SUBSTRING is not the same, because it errors if you specify a length longer then the string.

Oh, I know you can code around that with LENGTH. And, I know that LEFT is still available, but you have to fully reference Microsoft.VisualBasic.Left() in order to use it.

That that makes me old, cranky, and lazy. <huge smile>

My answer is this: I include the code below in all my projects as part of the library that I import. It's my helper class for strings.

This class makes LEFT available for string objects, use it like this:

Dim str As String = "12345"
Debug.WriteLine(str.Left(33))
Debug.WriteLine(str.Left(3))

and you'll get these results:

12345
123

If you're not familiar with extension classes, they work on the object type specified in the first parameter. So, in our code below, the first parameter ('theString') is a STRING type so this object extends the STRING class, and only uses one parameter (LENGTH)

 

 

The two errors below are similar and have the same root, so I'll blog them together:

 

Sql procedure error codes returned:
Error Number = 320  Stored Procedure= taPMTransactionInsert  Error Description = The Cash Amount Checkbook ID (CAMCBKID) does not exist in Checkbook Master Table - CM00100
Node Identifier Parameters: taPMTransactionInsert
BACHNUMB = EXPNS 0317    
VCHNUMWK = 00000000000000183
VENDORID = REIPAP1       
DOCNUMBR = 356963             
DOCTYPE = 1
DOCDATE = 3/21/2017
Related Error Code Parameters for Node : taPMTransactionInsert
CAMCBKID = BOP           
Error Number = 324  Stored Procedure= taPMTransactionInsert  Error Description = The Checkbook id (CHEKBKID) does not exist in the Checkbook Master Table - CM00100
Node Identifier Parameters: taPMTransactionInsert
BACHNUMB = EXPNS 0317    
VCHNUMWK = 00000000000000183
VENDORID = REIPAP1       
DOCNUMBR = 356963             
DOCTYPE = 1
DOCDATE = 3/21/2017
Related Error Code Parameters for Node : taPMTransactionInsert
CHEKBKID = BOP           
<taPMTransactionInsert>
  <BACHNUMB>EXPNS 0317     </BACHNUMB>
  <VCHNUMWK>00000000000000183</VCHNUMWK>
  <VENDORID>REIPAP1        </VENDORID>
  <DOCNUMBR>356963              </DOCNUMBR>
  <DOCTYPE>1</DOCTYPE>
  <DOCAMNT>170.37000</DOCAMNT>
  <DOCDATE>3/21/2017</DOCDATE>
  <PRCHAMNT>170.37000</PRCHAMNT>
  <CASHAMNT>170.37000</CASHAMNT>
  <CAMCBKID>BOP            </CAMCBKID>
  <CDOCNMBR>100019              </CDOCNMBR>
  <CAMTDATE>3/21/2017</CAMTDATE>
  <CAMPMTNM>1A31350984164AA4B663</CAMPMTNM>
  <CHEKBKID>BOP            </CHEKBKID>
  <TRXDSCRN>Reinert Paper &amp; Chemical(REIPA</TRXDSCRN>
  <CREATEDIST>0</CREATEDIST>
  <USRDEFND1>ZTEST                                             </USRDEFND1>
</taPMTransactionInsert>
<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <PMTransactionType>
    <taPMDistribution_Items>
      <taPMDistribution>
        <DOCTYPE>1</DOCTYPE>
        <VCHRNMBR>00000000000000183</VCHRNMBR>
        <VENDORID>REIPAP1        </VENDORID>
        <DISTTYPE>2</DISTTYPE>
        <ACTNUMST>101-1090-00-0000-</ACTNUMST>
        <DEBITAMT>170.37000</DEBITAMT>
      </taPMDistribution>
      <taPMDistribution>
        <DOCTYPE>1</DOCTYPE>
        <VCHRNMBR>00000000000000183</VCHRNMBR>
        <VENDORID>REIPAP1        </VENDORID>
        <DISTTYPE>6</DISTTYPE>
        <ACTNUMST>101-3400-00-0000-</ACTNUMST>
        <CRDTAMNT>170.37000</CRDTAMNT>
      </taPMDistribution>
    </taPMDistribution_Items>
    <taAnalyticsDistribution_Items xsi:nil="true" />
    <taPMTransactionInsert>
      <BACHNUMB>EXPNS 0317     </BACHNUMB>
      <VCHNUMWK>00000000000000183</VCHNUMWK>
      <VENDORID>REIPAP1        </VENDORID>
      <DOCNUMBR>356963              </DOCNUMBR>
      <DOCTYPE>1</DOCTYPE>
      <DOCAMNT>170.37000</DOCAMNT>
      <DOCDATE>3/21/2017</DOCDATE>
      <PRCHAMNT>170.37000</PRCHAMNT>
      <CASHAMNT>170.37000</CASHAMNT>
      <CAMCBKID>BOP            </CAMCBKID>
      <CDOCNMBR>100019              </CDOCNMBR>
      <CAMTDATE>3/21/2017</CAMTDATE>
      <CAMPMTNM>1A31350984164AA4B663</CAMPMTNM>
      <CHEKBKID>BOP            </CHEKBKID>
      <TRXDSCRN>Chemical(REIPA</TRXDSCRN>
      <CREATEDIST>0</CREATEDIST>
      <USRDEFND1>ZTEST                                             </USRDEFND1>
    </taPMTransactionInsert>
    <taMdaUpdate_Items xsi:nil="true" />
  </PMTransactionType>
</eConnect>
This request operation sent to net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/EntityOperations did not receive a reply within the configured timeout (00:01:00).  The time allotted to this operation may have been a portion of a longer timeout.  This may be because the service is still processing the operation or because the service was unable to send a reply message.  Please consider increasing the operation timeout (by casting the channel/proxy to IContextChannel and setting the OperationTimeout property) and ensure that the service is able to connect to the client.
The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state.

I do several eConnect integrations a week. On most jobs, I do it all end to end, but on this job eConnect had already been installed...

This article contains the XML for a valid PMManualPayment.

If you're in GP 2013 R2, you'll want to read this, too

https://dyndeveloper.com/ArticleView.aspx?ArticleID=555

 

Hi,

I need to grant access to the Letter Writing Assistant (under vendors) in Great Plains 2013. Could someone please tell me where is this set up and what Task should I select under Set Up?

Thanks a lot

Paul Chacko

Hello -

 

Does anybody in this group know of where we can find a comprehensive guide to GP users, their tasks and security roles?   It would be nice to have it in one place and I don't know if such a manual exists.

 

Thank you,

Karen Koehle

Good evening,

   I have GP 2016 version 16.00.0404 and when I try to enter a transaction in Transactions/Transactions Entry after enter the last field an error occur showing

the following message:

Unhandled Script Exception Script Commit of form [1081] not found

EXCEPTION_CLASS_SCRIPT_MISSING

Any suggestion is welcome,

Thanks a lot.

Hello

 

I am looking for security rol table to recover user profile please help

Can anybody help me whit an POP Receiving, im sending:

      <taPopRcptLineInsert>
        <POPTYPE>1</POPTYPE>
        <POPRCTNM>RCT1218</POPRCTNM>
        <PONUMBER>PO2080</PONUMBER>
        <ITEMNMBR>PLLBP</ITEMNMBR>
        <VENDORID>ACETRAVE0001</VENDORID>
        <VNDITNUM>PLLBP</VNDITNUM>
        <QTYSHPPD>1210.53</QTYSHPPD>
        <AUTOCOST>1</AUTOCOST>
      </taPopRcptLineInsert>

 

In dynamics the Receiving is created but, the QTYSHPPD says 121,053.00, why? 

Saludos a Todos. estoy realizando una tabla dinámica en Excel que me muestre las ventar por periodo y por año segun la  consulta que agrego al final. 

como verán estoy utilizando la tabla RM00104 . La pregunta es: el campo SMRYSALS contiene el monto de ventas con impuesto o sin impuesto?

Greetings to all. I am doing a dynamic table in Excel that shows me the sales by period and by year according to the query that I add at the end. As you will see I am using the RM00104 table. The question is: does the SMRYSALS field contain the amount of sales with tax or no tax?

select 'TIENDA 1' "Centro de Ventas" , YEAR1, PERIODID,
CASE PERIODID WHEN 1 THEN 'ENE' WHEN 2 THEN 'FEB'  WHEN 3 THEN 'MAR'  WHEN 4 THEN 'ABR'
 WHEN 5 THEN 'MAY' WHEN 6 THEN 'JUN'  WHEN 7 THEN 'JUL'  WHEN 8 THEN 'AGO'
 WHEN 9 THEN 'SEP' WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DIC'
 END Mes,
  sum(SMRYSALS - SMRYDISC - SMRYRTRN) "Venta Neta"
 from RM00104 WHERE HISTTYPE = 0 and CUSTNMBR like 'CL9%' AND YEAR1 > 2010
  group by YEAR1, PERIODID
 UNION ALL
 select 'TIENDA 2' "Centro de Ventas",    YEAR1, PERIODID,  
 CASE PERIODID WHEN 1 THEN 'ENE' WHEN 2 THEN 'FEB'  WHEN 3 THEN 'MAR'  WHEN 4 THEN 'ABR'
 WHEN 5 THEN 'MAY' WHEN 6 THEN 'JUN'  WHEN 7 THEN 'JUL'  WHEN 8 THEN 'AGO'
 WHEN 9 THEN 'SEP' WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DIC'
 END Mes,
 sum(SMRYSALS - SMRYDISC - SMRYRTRN) "Venta Neta"
 from RM00104 WHERE HISTTYPE = 0 and CUSTNMBR like 'CL1%' AND YEAR1 > 2010
  group by YEAR1, PERIODID
 UNION ALL
 select 'TIENDA 3' "Centro de Ventas" ,  YEAR1, PERIODID,  
 CASE PERIODID WHEN 1 THEN 'ENE' WHEN 2 THEN 'FEB'  WHEN 3 THEN 'MAR'  WHEN 4 THEN 'ABR'
 WHEN 5 THEN 'MAY' WHEN 6 THEN 'JUN'  WHEN 7 THEN 'JUL'  WHEN 8 THEN 'AGO'
 WHEN 9 THEN 'SEP' WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DIC'
 END Mes,
 sum(SMRYSALS - SMRYDISC - SMRYRTRN) "Venta Neta"
 from RM00104 WHERE HISTTYPE = 0 and CUSTNMBR like 'CL5%' AND YEAR1 > 2010
 group by YEAR1, PERIODID
 order by 2, 3

 

Hi

In setting up new company's I import the GL Ledger we are using; however is there a way at SQL level or another way to tick all the currency's against those Account Codes.

We have several Currencies and by default we allow access to all. Anyone got a SQL script that we insert the records to MC00200 or another way to do it?

Many thanks

Good morning,

     After run Build Batch the Check File Report show me a critical error "Tax records is does not exist for the employee", I verify in the Card/Payroll/State Tax

and all employees have the "State" field filled with "PR", in Tools/Setup/Payroll Tax the "PR" state tax exist with the last payroll taxes tables updates.

Any suggestion is welcome.

Thanks,

 

I have the last version GP 2016 16.00.0579

 

I am attempting to push an invoice to Great Plains using GP Web Services.  I am coming across the following error.

Validation Errors:

- A Purchase Order Line does not exist for the PurchaseOrderKey, ItemKey, VendorItemNumber, VendorKey and PurchaseOrderLineKey.

- VendorKey does not match the Purchase Order.

The data from the PO seems to match 1:1 to the invoice being passed.

Any ideas?

Thanks much,

Falk0r

Hi, I have econnect working with creating a invoice from a sales order transaction. The Invoice doc number is being created using the GetNextSopNumber() from econnect also. I am running into problems when I am trying to create an invoice from a SO with lots. Is there some documentation on how to create the invoice by using the SO lots that were already allocated?

 

How do you unmark all "Include in Lookup" values on the GP Account Maintenance screen from the screen itself?  Is there a keyboard shortcut or right-click?

 

 

 

Good morning,

  I want to print tax detail, by example I have two taxes "State Tax" and "Municipal Tax" in the totals invoices and I need to print in 2 separate lines.

Actually GP sum the taxes in only one line called "Tax"

I have GP2016 version 16.00.0579

Any suggestion is welcome.

Thanks

 

I am trying to write a report fro PA module; it is looking at Cost Categories and the PAProfit and PAbillings fields. However the user wants to see the financial information in the currency of the Project so if the entity I am reporting on has functional currency USD but the project is in GBP then the fields I need to report on are PAMCPAProfitAmt etc,....

So the logic I am looking for is

If PA01201.Currnidx not = to MC40000.funcridx

then PA01301.PA[PAMCBProfitAmt]

else PA01301.[PAProfitAmt]

 

I have all the joins looking at the tables; in reality what I am looking for is case statement that references fields and depending on the output selects fields rather than values!

I hope that this makes sense if not tell me and I will try and clarify.

 

Many thanks

hi all,

I am working up a Historical Aged Trial balance report, and I would like to see this restricted by GL posting date, and not document date.

Can someone tell me how gl30000 might link to this query?  Although, upon further inspection, I might be able to join it to the PA receipts (PA31101)?

 

thanks,

geo

 

 

SELECT VM.VENDORID AS Vendor_ID,

VM.VENDNAME AS Vendor_Name,

VM.VNDCLSID AS Vendor_Class,

VM.PYMTRMID AS Vendor_Terms,

VM.CRLMTDLR AS Credit_Limit,

SUM(CASE

WHEN P.DOCTYPE < 4 THEN P.CURTRXAM ELSE P.CURTRXAM * -1

END) AS Unapplied_Amount,

SUM(CASE

WHEN DATEDIFF(d, P.DUEDATE, getdate()) < 31 AND P.DOCTYPE < 4 THEN P.CURTRXAM

WHEN DATEDIFF(d, P.DOCDATE, getdate()) < 31 AND P.DOCTYPE > 3 THEN P.CURTRXAM * -1 ELSE 0

END) AS [Current],

SUM(CASE

WHEN DATEDIFF(d, P.DUEDATE, getdate()) BETWEEN 31 AND 60 AND P.DOCTYPE < 4 THEN P.CURTRXAM

WHEN DATEDIFF(d, P.DOCDATE, getdate()) BETWEEN 31 AND 60 AND P.DOCTYPE > 3 THEN P.CURTRXAM * -1 ELSE 0

END) AS [31_to_60_Days],

SUM(CASE

WHEN DATEDIFF(d, P.DUEDATE, getdate()) BETWEEN 61 AND 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM

WHEN DATEDIFF(d, P.DOCDATE, getdate()) BETWEEN 61 AND 90 AND P.DOCTYPE > 3 THEN P.CURTRXAM * -1 ELSE 0

END) AS [61_to_90_Days],

SUM(CASE

WHEN DATEDIFF(d, P.DUEDATE, getdate()) > 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM

WHEN DATEDIFF(d, P.DOCDATE, getdate()) > 90 AND P.DOCTYPE > 3 THEN P.CURTRXAM * -1 ELSE 0

END) AS [91_and_Over],

P.APLYWITH,

P.DOCDATE,

P.DOCNUMBR,

dbo.PA10601.PAPROJNUMBER,

dbo.PA10601.PABase_Unit_Cost,

dbo.POP10110.XCHGRATE,

dbo.POP10110.CURNCYID,

dbo.PA10601.PApurordnum

FROM dbo.PA10600

INNER JOIN

dbo.PA10601

ON dbo.PA10600.PApurordnum = dbo.PA10601.PApurordnum

INNER JOIN

dbo.POP10110

ON dbo.PA10601.PApurordnum = dbo.POP10110.PONUMBER

RIGHT OUTER JOIN

dbo.PM00200 AS VM

INNER JOIN

dbo.PM20000 AS P

ON P.VENDORID = VM.VENDORID

ON dbo.PA10600.PApurordnum = P.PORDNMBR

WHERE (P.CURTRXAM <> 0) AND (P.VOIDED = 0)

GROUP BY VM.VENDORID, VM.VENDNAME, VM.PYMTRMID, VM.VNDCLSID, VM.CRLMTDLR, P.APLYWITH,

P.DOCDATE, P.DOCNUMBR, dbo.PA10601.PAPROJNUMBER, dbo.PA10601.PABase_Unit_Cost,

dbo.POP10110.XCHGRATE, dbo.POP10110.CURNCYID, dbo.PA10601.PApurordnum

HAVING (VM.VENDORID between @I_sStartVendorID and @I_sEndVendorID)

AND (P.DOCDATE < @I_dAgingDate)

AND (PA10601.PAPROJNUMBER BETWEEN @I_sProjectStart AND @I_sProjectEnd)

AND (P.DOCNUMBR BETWEEN @I_sStartDocumentNumber AND @I_sEndDocumentNumber);

END

I need to know which security role (besides Poweruser, of course) would allow access to the following:

GP   Financial > Routines > Electronic Reconcile

 

I've tried some hit-or-miss scenarios, but wondering if anybody there knows what will give this to the user.  She already has Accounting Manager, AP and AR Clerk, Bookkeeper, Customer Service Rep, FA Manager, Order Processor and Purchasing Manager.

Thank you.

 

 

I have this error - I need to see the resolution you have in this system for this error

In your experience, how big can your GP company database get before you start getting worried?  I am trying to determine whether I should start looking into database archiving.  To give you an example, our main GP company database is now about 250 GB with about 1.5 years worth of data.  Any feedback is greatly appreciated.

Thank you,