|
|
https://www.red-gate.com/simple-talk/sql/t-sql-programming/tsql-regular-expression-workbench/#fourth
I find myself continually doing a 'we're not worthy' bow to the people at www.red-gate.com. Below is their Regex.Replace function for SQL, it works GREAT.
I copy it here because it's easier to find when I'm in a hurry... I use this site all day long in production coding.
RedGate, you rock. |
|
|
|
|
|
What? What's that? Why do I care?
Give me 60 seconds, I'll answer all that |
|
|
|
| Deferred Revenue script for RM, using the Period Days method |
|
|
|
Warning - this is a very powerful and dangerous script
AND IT IS UNTESTED IN A LIVE ENVIRONMENT
I'm just posting it here as a shell for future use.
USE AT YOUR OWN RISK.
BACKUP YOUR DATA.
That being said, this script is designed to look at all the unapplied payments in your environment and insert apply records.
There are three scripts. The first backs up RM20101. The second restores it. The third is the script that does the work. |
|
|
|
Hi All,
I am using econnect with Post Master to create Bank Transaction entry in my .Net web project. I find when I generate Trial balance report from GP client it shows user name as "SA", (my post master is configured with SA credentials ) might be this is the reason for user name as "SA".
Is their any way to show actual user name(Who initiate bank transaction) on the reports?
I am passing user name in ecconect xml with "USERID".
Thank You
Prakash |
|
|
|
Hi!
I have a small problem retreiving a Sales_Transaction in Dynamics GP. If I use the current user (my user, without impersonation) it is working. But if I do an impersonation with my user, it is not working...
Want to know if someone could help me with this!
Have a nice day!!
-Patrick.
Here is my C# code below, and after I put the impersonate class :
***********************************************************************************************************************************
public taSopHdrIvcInsert GetSalesTransaction(string transactionNumber)
{
taSopHdrIvcInsert returnValue = null;
eConnectMethods eConnectObject;
eConnectOut myRequest;
RQeConnectOutType[] eConnectOutType;
eConnectType eConnectDoc;
System.IO.MemoryStream memStream;
System.Xml.Serialization.XmlSerializer serializer;
System.Xml.XmlDocument myDoc;
string salesOrderEntityString;
System.IO.StringReader requestReader;
System.Xml.XmlTextReader xmlTextReader;
string salesorderXml;
System.IO.StringReader salesorderReader;
System.Xml.Serialization.XmlSerializer deSerializer;
try
{
using (Impersonator imp = new Impersonator("MyUserName", "MyDomain", "MyPassword"))
{
//set the requester to retreive a sales order
myRequest = new eConnectOut();
myRequest.DOCTYPE = "Sales_Transaction";
myRequest.OUTPUTTYPE = 1;
myRequest.INDEX1FROM = transactionNumber;
myRequest.INDEX1TO = transactionNumber;
myRequest.FORLIST = 1;
//myRequest.WhereClause = "CUSTNMBR = '" + customerNumber + "'";
//create the eConnect requester XML document object
eConnectOutType = new RQeConnectOutType[1] { new RQeConnectOutType() };
eConnectOutType[0].eConnectOut = myRequest;
eConnectDoc = new eConnectType();
eConnectDoc.RQeConnectOutType = eConnectOutType;
//serialize the object to produce an XML document
memStream = new System.IO.MemoryStream();
serializer = new System.Xml.Serialization.XmlSerializer(typeof(eConnectType));
serializer.Serialize(memStream, eConnectDoc);
memStream.Position = 0;
myDoc = new System.Xml.XmlDocument();
myDoc.Load(memStream);
//get the sales order entity
eConnectObject = new eConnectMethods();
salesOrderEntityString = eConnectObject.GetEntity(_connectionString, myDoc.OuterXml);
//load the xml document string into a StringReader
requestReader = new System.IO.StringReader(salesOrderEntityString);
//use the StringReader to populate an XML text reader
xmlTextReader = new System.Xml.XmlTextReader(requestReader);
//use the XML text reader to find the sales order XML in the eConnect Requester response document
//the eConnect_Out_Setup table shows that the sales order XML data will be enclosed in <SO_Trans> tags
xmlTextReader.ReadToFollowing("SO_Trans");
salesorderXml = xmlTextReader.ReadInnerXml();
salesorderXml = string.Concat("<?xml version=\"1.0\"?><taSopHdrIvcInsert>", salesorderXml, "</taSopHdrIvcInsert>");
salesorderReader = new System.IO.StringReader(salesorderXml);
//deserialize the XML node from the StringReader
deSerializer = new System.Xml.Serialization.XmlSerializer(typeof(taSopHdrIvcInsert));
//cast the deserialized object to a taSopHdrIvcInsert serialization object
returnValue = (taSopHdrIvcInsert)deSerializer.Deserialize(salesorderReader);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error:eConnectEnerkemGP.GetSalesTransaction()", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return returnValue;
}
***********************************************************************************************************************************
namespace MyNameSpace
{
#region Using directives.
// ----------------------------------------------------------------------
using System;
using System.Security.Principal;
using System.Runtime.InteropServices;
using System.ComponentModel;
// ----------------------------------------------------------------------
#endregion
/////////////////////////////////////////////////////////////////////////
/// <summary>
/// Impersonation of a user. Allows to execute code under another
/// user context.
/// Please note that the account that instantiates the Impersonator class
/// needs to have the 'Act as part of operating system' privilege set.
/// </summary>
/// <remarks>
/// This class is based on the information in the Microsoft knowledge base
/// article http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306158
///
/// Encapsulate an instance into a using-directive like e.g.:
///
/// ...
/// using ( new Impersonator( "myUsername", "myDomainname", "myPassword" ) )
/// {
/// ...
/// [code that executes under the new context]
/// ...
/// }
/// ...
///
/// Please contact the author Uwe Keim (mailto:uwe.keim@zeta-software.de)
/// for questions regarding this class.
/// </remarks>
public class Impersonator :
IDisposable
{
#region Public methods.
// ------------------------------------------------------------------
/// <summary>
/// Constructor. Starts the impersonation with the given credentials.
/// Please note that the account that instantiates the Impersonator class
/// needs to have the 'Act as part of operating system' privilege set.
/// </summary>
/// <param name="userName">The name of the user to act as.</param>
/// <param name="domainName">The domain name of the user to act as.</param>
/// <param name="password">The password of the user to act as.</param>
public Impersonator(
string userName,
string domainName,
string password)
{
ImpersonateValidUser(userName, domainName, password);
}
// ------------------------------------------------------------------
#endregion
#region IDisposable member.
// ------------------------------------------------------------------
public void Dispose()
{
UndoImpersonation();
}
// ------------------------------------------------------------------
#endregion
#region P/Invoke.
// ------------------------------------------------------------------
[DllImport("advapi32.dll", SetLastError = true)]
private static extern int LogonUser(
string lpszUserName,
string lpszDomain,
string lpszPassword,
int dwLogonType,
int dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern int DuplicateToken(
IntPtr hToken,
int impersonationLevel,
ref IntPtr hNewToken);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern bool RevertToSelf();
[DllImport("kernel32.dll", CharSet = CharSet.Auto)]
private static extern bool CloseHandle(
IntPtr handle);
private const int LOGON32_LOGON_INTERACTIVE = 2;
private const int LOGON32_PROVIDER_DEFAULT = 0;
// ------------------------------------------------------------------
#endregion
#region Private member.
// ------------------------------------------------------------------
/// <summary>
/// Does the actual impersonation.
/// </summary>
/// <param name="userName">The name of the user to act as.</param>
/// <param name="domainName">The domain name of the user to act as.</param>
/// <param name="password">The password of the user to act as.</param>
private void ImpersonateValidUser(
string userName,
string domain,
string password)
{
WindowsIdentity tempWindowsIdentity = null;
IntPtr token = IntPtr.Zero;
IntPtr tokenDuplicate = IntPtr.Zero;
try
{
if (RevertToSelf())
{
if (LogonUser(
userName,
domain,
password,
LOGON32_LOGON_INTERACTIVE,
LOGON32_PROVIDER_DEFAULT,
ref token) != 0)
{
if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
{
tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
impersonationContext = tempWindowsIdentity.Impersonate();
}
else
{
throw new Win32Exception(Marshal.GetLastWin32Error());
}
}
else
{
throw new Win32Exception(Marshal.GetLastWin32Error());
}
}
else
{
throw new Win32Exception(Marshal.GetLastWin32Error());
}
}
finally
{
if (token != IntPtr.Zero)
{
CloseHandle(token);
}
if (tokenDuplicate != IntPtr.Zero)
{
CloseHandle(tokenDuplicate);
}
}
}
/// <summary>
/// Reverts the impersonation.
/// </summary>
private void UndoImpersonation()
{
if (impersonationContext != null)
{
impersonationContext.Undo();
}
}
private WindowsImpersonationContext impersonationContext = null;
// ------------------------------------------------------------------
#endregion
}
/////////////////////////////////////////////////////////////////////////
} |
|
|
|
Hi All--
I am fairly new to GP 2016 and my company is experiencing and issue with Vendors being created incorrectly. I have not found a field inside the application of the "Created By" or similar field that houses a User ID, does anyone know how I can create a report that shows when a by whom each vendor was created?
Thanks |
|
|
|
First of all, is anybody out there familiar with creating RTV's in Field Service ? This seems to be a pretty unfamiliar module in my world.
We do not want to tie customer RMA's to Vendor RTV's. Customers return products to us and we credit them with an RMA.
Damaged or out-of-date products that we want to return to vendors (from various customers or from our own shelves) will utilize the RTV's.
Does an RTV generate an AP credit when it's shipped to the vendor?
Can you capture Lot number and expiration dates on each item on the RTV?
|
|
|
|
Does anyone know the correct way to update a Vendor's class via GP Web Services?
Sometimes, we need to remove the Class from a Vendor. This process works perfectly in GP (just remove the class from the input field and save the changes), however we need this same functionality in a web application that's using Dynamics GP Web Services. Using eConnect is not preferable but could be an option if the given solution would work.
I have tried a few variations of things like...
- vendor.ClassKey.Id = "";
- vendor.ClassKey.Id = " ";
- vendor.ClassKey.Id = " ";
- vendor.ClassKey.Id = null;
- vendor.ClassKey = null;
- vendor.ClassKey.Id = new VendorClassKey { Id = null };
I have also tried similar things with eConnect and that doesn't seem to work either. All of these attempts have resulted in the Vendor Class simply not being updated.
What am I doing wrong? |
|
|
|
Good afternoon,
the following information pertains to this question
SSRS version 2012
Dynamics GP version 2013 R2 ver 20.00.2130 (R2)
I've built SSRS invoices and they have been working well. For some unknown reason, we cannot print a particular SSRS invoice from a remote desktop. It renders fine in the web browser; but, when you go click on the printer icon, you get the following error:
Window title is "Print Error"
An error occurred during printing. (0x800C0007)
This only occurs if you attempt to print the invoice from one of the 7 remote desktop servers we connect to regardless of the login. If I log directly onto the SQL Server and print the invoice, it prints fine. Other SSRS invoice formats print fine from the remote desktop servers.
Let me know if there is additional information I can supply.
Thanks
Joe |
|
|
|
Error: At least one input variable contains a null value in taCreateItemVendors stored procedure
|
|
|
|
Good Afternoon,
I recently came across a scenario which was slightly different than usual, (from what ive seen). The user is creating a Purchase Order which is Drop Shipped. They are then going to the "Transactions > Purchasing > Enter / Match Invoices" and are able to auto invoice the Purchase order without receiving the invoice.
I am familiar with the idea of a Shipment Invoice, but I believe that is done at another screen within great plains.
So my question is, what would be the correct eConnect schema to create this Invoice/Receipt in the designated area of "Transactions > Purchasing > Enter / Match Invoices". Also, what is the difference between sending an invoice to this location and creating a Shipment Invoice in the "Receivings Transaction Entry" window.
|
|
|
|
This weekend we started seeing an unhandled object exception message when exiting GP. I don't see that it is causing any issues other that showing on exit.
I have attached a screen shot of what we are seeing. Any idea on where to start investigating?
Unhandled Object Exception
The parameter is incorrect
EXCEPTION_CLASS_OBJECT_EXCEPTION
ExceptionSubClass:-2147024809
|
|
|
|
I am working on a .NET MVC web application that utilizes Dynamics GP Web Services to connect to the GP back-end. I am currently having an issue when editing Vendor Notes via GP Web Services. Specifically, any variation of the newline character ('\r\n', '\r' or '\n') will output a line feed character ('\n'). For whatever reason, GP 2010 doesn't seem to like the line feed character as it won't produce a link break when viewing the note in GP.
If I set a Vendor's Note property to "test\rtest", it will show up in GP as "testtest" - without a link break. This means that when Users edit and save a Note, the formatting is completely lost. We use Notes quite heavily and this is becoming problematic. In SalesPad (a third party tool which has the ability to edit Vendor Notes), the notes save their formatting correctly - so it must be possible.
The article here seems to illustrate my problem. I have tried all the suggestions mentioned in the article. The comments seem to suggest that this is a bug in GP 2010 Web Services.
Does anyone know if there is actually a way to properly format Vendor Notes via GP Web Services so they show up with line breaks in GP?
Thanks |
|
|
|
We have two customers we are trying to make inactive.
There are no pending orders for them.
When I try to check the Inactive box on the Customer Card, I get this pop-up message: "You cannot inactivate a customer record with unposted or posted transactions."
Thoughts on what transactions to look for?
|
|
|
|
Good morning,
I
made several modifications to the invoices, orders and quotes using the GP
Report Writer when I try to send emails using E-mail buttom in PDF format GP
show me that is necessary assign a word template to the report, this work fine
but the problem is that the word template do not have the modifications already
made in report writer to this documents.
Any suggestion
is welcome,
Mr. García
I have GP 2016
version16.00.0579 (R2) |
|
|
|
Hi All--
I have a serious issue in our production environment and don't have a clue where to begin.
We printed a check on 8/14/17 for a vendor. See attached remit and check stub. However, this check does not show up in the vendor maintenance records for this vendor and when you find it in the checkbook register and drill down, the next screen is blank. On top of that, none of the invoices listed as paid on the remit are showing in the vendor record at all. See screen shots below.
Also when wetested to see if wecould re-enter one of the invoices through our PO middleware, we received an error stating that number already existed in GP. So with that said these documents seem to be in GP somewhere but not pulling all the way through the table to the vendor record.
Per AP, these invoices were entered in June, pulled into a payment batch on 7/26/17 and a check was printed on 8/14/17. So sometime between 8/14 and now, the invoices have disappeared from the vendor record and the check never showed up there.
Why has our check information disappeared?
|
|
|
|
Good morning,
I have items
that I do not need that exist in the inventory card but when i transfer sales order to purchase orders i don’t want that bring the last vendor
that I buyed. (I don't understand
this because this items does not exist in the inventory card)
I need the
vendor in blank and the red flags must be appear (NO VENDOR) so that then
the user will enter the vendor in the “PO” tab. I have Dynamics GP 2016 version 16.00.0579 (R2)
Any suggestion
is welcome,
Thanks a lot,
Mr. García |
|
|
|
In the Dynamics GP Table Reference - I am trying to look at field descriptions, but they show as abbreviated and ending in (Join Now).
I have joined DynDeveloper.com and am in my 30 day trial - therefore my account is active. The (more) beside each description is not a link and does not expand the description, and (Join Now) is irrelevant - as I have joined. What am I doing wrong?
Is there a way to export this content or to better view descriptions?
|
|
|
|
Hi everyone,
There is a report in GP SmartList under Purchasing [Receivings Transactions] based on the SQL View called 'ReceivingsTransactions'
What I am trying to do is add the PONUMBER field to this report by editing the view. However, the POP10300 does not seem to have a PONUMBER field in there. I could connect POP10310 but then I get multiple lines for the same receipt.
Any thoughts as to how I can accomplish this task?
Thanks
Paul Chacko
[Note: I fail to understand why PONUMBER is not in the Receiving Header but on the Line Items table - annoyed!] |
|
|
|
I'm looking for documentation on how to map the AAG tables together.
AAG30000
AAG30001
AAG30002
AAG30003 |
|