DynDeveloper.com

Here's the deal. I'm just tired of writing this script. I have to write it  several times a week.

I'll blog it here and be done with writing it. <smiles>

This is a script that will produce an insert statement for any table in SQL 2008 +

Sure, you can just right click on a table and get this... but my solution is ready to use and requires only minimal editing.

And (for all you OCDers like me) the commas line up.

Upon opening Dynamics GP, you receive this error "An error has occurred in the script on this page".

Hi , I have an application that generates a file in the eConnect xml format, they are invoices, and an application that passes the information to gp10.0 .

When an invoice has different amounts of xml 0.000000 in the header TAXAMNT node, or has distita amount of 0.000000 in the field line MRKDNAMT it raises error and not load the transaction in GP . For these cases you must manually edit the xml and so achievement that is loaded into GP .. you can correct this situation?

Hola, tengo una aplicación que me genera un xml en formato econnet, son facturas. y una aplicación que pasa la información a gp10.0.

ocurre que cuando una factura en xml tiene cantidad distinta de 0.000000 en el nodo TAXAMNT,  del header, o tiene cantidad distita de 0.000000 en el campo MRKDNAMT de line me genera error y no se carga la transacción en GP. Para estos casos debe editar manualmente el xml y asi logro que se cargue en GP.. 

es posible corregir esta situación?

Error: The purchase order line has been Received, Canceled, or Closed

I have been asked to generate a query that will allow for all PO's received but not invoiced. to be displayed.

I have most of the logic I think but am struggling to cut the code, could one of the experts give me a hand on this possibly? Or perhaps there is a better way to do this.

I believe the logic would be something like find POs in the POP30100 Purchase Order History table that donothave a matching record in the POP30300 Purchasing Receipt History table where POP30300.POPTYPE = 2 (Invoice) or 3 (Shipment/Invoice). You’d also need to look at the POP10300 Purchasing Receipt Work table too: find all POs in the POP30100 table that do not have a matching record in the POP10300 table where POP10300.POPTYPE = 2 or 3.

Likewise with the related work tables: find all POs in the POP10100 Purchase Order Work table that do not have a matching record in the POP10300 Purchasing Receipt Work table where POP10300.POPTYPE = 2 or 3.

Playing in SQL I get

SELECT  A.[PONUMBER] ,
        CASE A.[POSTATUS]
           WHEN 1 THEN 'NEW'
           WHEN 2 THEN 'RELEASED'
           WHEN 3 THEN 'CHANGE ORDER'
           WHEN 4 THEN 'RECEIVED'
           WHEN 5 THEN 'CLOSED'
           WHEN 6 THEN 'CANCELED'
         END AS POSTATUS ,
        CASE A.[POTYPE]
           WHEN 1 THEN 'STANDARD'
           WHEN 2 THEN 'DROP-SHIP'
           WHEN 3 THEN 'BLANKET'
           WHEN 4 THEN 'BLANKET DROP-SHIP '
        END AS POTYPE ,
        A.[DOCDATE] AS Date ,
        A.[PRMDATE] AS PromiseDate ,
        A.[REQDATE] AS RequestedDate ,
        A.[REMSUBTO] AS RemainingSubTotal ,
        A.[SUBTOTAL] AS SubTotal ,
        A.OREMSUBT AS OriginatingRemainingSubTotal ,
        A.ORSUBTOT AS OriginatingSubTotal ,
        A.[VENDORID] AS VendorID ,
        A.[VENDNAME] AS VendorName ,
        A.[DUEDATE] AS DueDate ,
        A.[CURNCYID] ,
        A.[BUYERID] ,
        CASE B.[POLNESTA]
           WHEN 1 THEN 'NEW'
           WHEN 2 THEN 'RELEASED'
           WHEN 3 THEN 'CHANGE ORDER'
           WHEN 4 THEN 'RECEIVED'
           WHEN 5 THEN 'CLOSED'
           WHEN 6 THEN 'CANCELED'
        END AS POLineStatus ,
        B.[ITEMNMBR] AS ItemNumber ,
        B.[ITEMDESC] AS ItemDescrption ,
        B.[VENDORID] AS LineVendorID ,
        B.[VNDITNUM] AS VendorItemNumber ,
        B.[LOCNCODE] ,
        B.[UOFM] ,
        B.[QTYORDER] AS QuantityOrdered ,
        B.[QTYCANCE] AS QuantityCancelled ,
        B.[UNITCOST] ,
        B.ORUNTCST AS OriginatingUnitCost ,
        B.[EXTDCOST] AS ExtendedCost ,
        B.OREXTCST AS OriginatingExtendedCost ,
        B.XCHGRATE AS ExchangeRate ,
        ISNULL(C.POPRCTNM, ' ') AS POPRCTNM ,
        ISNULL(C.QTYSHPPD, 0) AS QuantityShipped ,
        ISNULL(C.QTYINVCD, 0) AS QuantityInvoiced ,
        ISNULL(C.QTYREJ, 0) AS QuantityRejected ,
        ISNULL(C.QTYMATCH, 0) AS QuantityMatch ,
        ISNULL(C.QTYRESERVED, 0) AS QuantityReserved ,
        CASE C.POPTYPE
           WHEN 1 THEN 'SHIPMENT'
           WHEN 2 THEN 'INVOICE'
           WHEN 3 THEN 'SHIPMENT/INVOICE'
           ELSE ' '
        END AS DocumentType ,
        ISNULL(C.UOFM, ' ') AS UOFM,
        ISNULL(C.DATERECD ,'') AS DateReceived,
        ISNULL(C.PCHRPTCT, 0) AS PURCHASERECEIPTCOST ,
        ISNULL(D.ORUNTCST, 0) AS ORGRECUNITCOST ,
        ISNULL(D.OREXTCST, 0) AS ORGRECCOST
        FROM    dbo.POP10100 AS A
        INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
        LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER]
                                         AND B.[ORD] = C.[POLNENUM]
        LEFT OUTER JOIN dbo.POP30310 AS D ON C.[PONUMBER] = D.[PONUMBER]
                                         AND C.[RCPTLNNM] = D.[RCPTLNNM]
                                         AND C.POPRCTNM = D.POPRCTNM
        LEFT OUTER JOIN dbo.POP30300 AS E ON D.[POPRCTNM] = E.[POPRCTNM]

order by DATE DESC

I need to sort by status 2 and 3 I think to filter.

Como modifico la definición de un formato para que sea numérico y no Moneda. 

Como creo uno nuevo y que reportwriter lo reconosca, ya que hice uno y no logro utilizarlo.

favor ver imagen

As I change the definition of a format to be numerical and not Monetary. As I think ReportWriter new one and it reconnoitre , as I made one and I can not use it.   Please see picture

Hi:

I added a couple of custom fields (a textbox and a combobox) to the Purchase Requisitions Entry screen in GP 2015.  After running the dag.exe utilitity, generating the Application.Dynamics.ModifiedForms.dll and its xml file sucessfully, and importing it in my project, I am able to see the textbox in Intellisense, and use it, but I cannot see the combobox.  I tried adding a dropdown list, but it doesn't work either.  

Am I doing something wrong, or VST just doesn't support comboboxes and dropdown lists?

Hi,

I need an example code of how to set an SOP on hold.

I have a batch of sales that have bills and when I try to open them said that the document was historical. to consult with sql query invoices are all in SOP10100 And SOP10200.
Apparently during the posting process the connection to the server is interrupted, that damage the loye and then changed to a new batch bills, but you can not count and I need to post the batch.

anyone has any idea that can help me?

Performing the SQL queries that attached discover that the invoices are in the GL10000 tables, GL10001 but are not on the table GL20000, transactions in the SOP10100 table has the PSTGSTUS = 14 field and field TRXSORCE = SLSTE00010061. 
I have two questions:
1. There is some additional table where you should look.
2. I can delete the damaged invoices batch of GL10000 tables, GL10001; update PSTGSTUS = 0 field and field TRXSORCE = ''  and make a new posting?

Hello,

I am trying to update  statuses for old purchase order documents en masse.  The web service call works fine when cancelling PO lines which are New, Released, or Change Order.  However, when trying to push a status of Closed to PO lines with the status of Received, the call does not work.  eConnect does not throw any errors at this point, it is like the call is being ignored.  Below I have pasted the entire web method, and any help/insight is appreciated!

[WebMethod]
        public void changePOLineStatusSTG(string PONumber, string Vendor, string ItemNumber, int LineSeq, int POStatus)
        {
            eConnectType eC;
            POPTransactionType po = new POPTransactionType
            {
                taPoLine_Items = new[]
                {
                    new taPoLine_ItemsTaPoLine {
                    PONUMBER = PONumber,
                    POLNESTA = (short)POStatus,
                    POLNESTASpecified = true,
                    VENDORID = Vendor,
                    ITEMNMBR = ItemNumber,
                    ORD = LineSeq,
                    ORDSpecified =true,
                    UpdateIfExists = 1
                    }
                },

                taPoHdr = new taPoHdr
                {
                    PONUMBER = PONumber,
                    VENDORID = Vendor,
                    UpdateIfExists = 1
                }
            };

            eC = new eConnectType { POPTransactionType = new[] { po } };
            SendEconnect(eC, Settings.Default.CS_STG, TrxType.UPDATE);
        }