|
|
This is an actual requirement that I got from a customer today, so I'm challenging you to see if you can figure out how to code it in SQL.
Give it a careful though before you read my answer.
Leave a comment if you have something that will work better, or is more elegant.
So, we start with this:
declare @Order table(RowID int identity,Invoice varchar(20), CustomerID varchar(20), CustomerEmail varchar(100), DocAmount numeric(19,2) )
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00100','001','Joe@Acompany.com',100)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00101','002','Sam@Bcompany.com',110)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00102','003','Bob@Ccompany.com',120)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00103','004','Tom@Dcompany.com',130)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00104','005','Flo@Fcompany.com',140)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00105','006','Tim@Gcompany.com',150)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00106','006','Tim2@Gcompany.com',160)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00107','008','Sal@Hcompany.com',170)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00108','008','Sal2@Hcompany.com',180)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00109','008','Sal3@Hcompany.com',190)
select * from @Order
This will give us an ORDER table that looks like the below. 10 lines, from 8 companies. The requirement is this:
Use the order table to update the email address field in the customer table. If the same customer has two email address, the newest one should be the main email address, the second oldest one should be the CC, and discard any older email addresses.
The final result should look like the second screen shot.
How would you code that?
|
|
|
|
TFS: The path xxx is already mapped in workspace xxx
Grrr.
I'm moving from one TFS server to another, and I'm having to remap quite a few projects. On each machine I run into the above error.
I found a fix today: |
|
|
|
Today we're talking about granting access to a table or view in SmartListBuilder.
If you look on the menu where this article is located, you'll see several other articles on security, and those are more complete than this one. Today we're solving on simple problem. |
|
|
|
Error 7115 The Price Method passed is Currency Amount (PRICMTHD = 1), you can not have a Rounding Amount > 0
Error 7116 The Price Method passed is Currency Amount (PRICMTHD=1) you can not have a (ROUNDTO <> 1)
I... don't exactly have a solution, but I have the piece of code that raises the error. This should help. |
|
|
|
SmartConnect: Failed to create record. Unable to call service: Not Found
This occurred during an invoice integration
Grrrr. How helpful is that?
Edit 4/18/2019 Found a second cause, please read all the way through |
|
|
|
| This is a short article that describes how to retrieve OLE notes in GP2015 and greater |
|
|
|
Error Number = 844
Stored Procedure= taPopRcptHdrInsert
Error Description = Tax detail amount does not match the header tax
amount Node Identifier Parameters: taPopRcptHdrInsert POPRCTNM =
RCT00000000003341 POPTYPE = 3
VNDDOCNM = RAJ00625668
receiptdate = 01/28/2019
BACHNUMB =BATCH01
VENDORID = CUST01
<taPopRcptHdrInsert>
<POPRCTNM>RCT00000000003341</POPRCTNM>
<POPTYPE>3</POPTYPE>
<VNDDOCNM>RAJ00625668 </VNDDOCNM>
<receiptdate>01/28/2019</receiptdate>
<BACHNUMB>BATCH01 </BACHNUMB>
<VENDORID>CUST01 </VENDORID>
<FRTAMNT>68.13000</FRTAMNT>
<TAXAMNT>3.41000</TAXAMNT>
<DISAVAMT>0</DISAVAMT>
<TAXSCHID>HST TAX </TAXSCHID>
<CREATEDIST>0</CREATEDIST>
<RATEEXPR>0</RATEEXPR>
<DYSTINCR>0</DYSTINCR>
<TRXDTDEF>0</TRXDTDEF>
<RTCLCMTD>0</RTCLCMTD>
</taPopRcptHdrInsert>
|
|
|
|
This ARTICLE describes a solution to an issue.
Sql procedure error codes returned:
Error Number = 996 Stored Procedure= taPopRcptHdrInsert Error Description = Vendor Document numbers (VNDDOCNM) for Shipment/Invoices (POPTYPE=3) must be unique Node Identifier Parameters: taPopRcptHdrInsert POPRCTNM = RCT00000000003355 POPTYPE = 3
VNDDOCNM = RAM00625668
receiptdate = 01/28/2019
BACHNUMB = BATCH01
VENDORID = VENDOR01
<taPopRcptHdrInsert>
<POPRCTNM>RCT00000000003355</POPRCTNM>
<POPTYPE>3</POPTYPE>
<VNDDOCNM>RAM00625668 </VNDDOCNM>
<receiptdate>01/28/2019</receiptdate>
<BACHNUMB>BATCH01 </BACHNUMB>
<VENDORID>VENDOR01 </VENDORID>
<FRTAMNT>68.13000</FRTAMNT>
<TAXAMNT>3.41000</TAXAMNT>
<DISAVAMT>0</DISAVAMT>
<TAXSCHID>HST TAX </TAXSCHID>
<RATEEXPR>0</RATEEXPR>
<DYSTINCR>0</DYSTINCR>
<TRXDTDEF>0</TRXDTDEF>
<RTCLCMTD>0</RTCLCMTD>
</taPopRcptHdrInsert>
|
|
|
|
Is there a way to create a notification or something that tells an ap user when a check batch is done building? Currently, they have to rely on the batch itself updating and some of our batches can take several minutes to build.
Just wondering if anyone has done anything like this before. |
|
|
|
I am currently using GP2016 and like to import GL Transactions with AA using econnect, VB or C#.
The examples I found ate for version 2010/2013. |
|
|
|
Good morning,
I have the follow situation Customer Invoices not showing all invoices in the All-In-One View Option I verify that all options has been marked and no filter has been setup. All invoices showing correctly in the Trial Balance and in the Customer Inquiry. I run check link and reindex script I don't work.
Any suggestion is welcome.
Thanks,
prcomputer
GP2016 version 16.00.0579
|
|
|
|
Good morning,
I have the follow situation i enter to Sales Transactions-Orders and select a Document No. and select the right arrow
to see the orders and invoices screen, the order is correct but show two invoices that not corresponding to this customer.
Any suggestion is welcome.
Thanks,
prcomputer
GP 2016 Version 16.00.0579
|
|
|
|
I'm writing a sales order import tool for GP 2018. I'm needing to know if the logic below is right, or if I'm missing something.
Import Logic
read headers
|
does customer exist?
NO YES
| |
| |
create new customer |
| |
insert records into |
RM00101, RM00102, |
RM00103 |
| |
check if order already exists
(export systems allow duplicates)
NO YES
| |
| exit
|
check for batch
or create new - lock batch
SOP00500
|
insert record into
SOP10100
SOP10101
SOP10102 - 2 entries, one with
account reference for Sales
and one with the reference for
accounts receivable
SOP10103
SOP10106
SOP10112
|
loop through line items
insert records into
SOP10200
SOP10105
|
update batch totals and unlock
SOP00500
|
|
|
|
This SQL Query:
Select CUSTNMBR as 'Customer', Convert(numeric(10,2),sum(ORTRXAMT)) as 'Revenue' as 'Revenue', Format(DOCDATE,'MMM-yy') as 'Period' FROM [SIZE].[dbo].[RM20101]
WHERE CUSTNMBR ='ACFLOR220' AND DOCDATE between '2/1/2019' and '2/28/2019' AND RMDTYPAL=1
GROUP BY CUSTNMBR, DOCDATE
Produces this:
Customer Revenue Period
ACFLOR220 337.25 Feb-19
ACFLOR220 310.65 Feb-19
But I want it to produce this:
ACFLOR220 647.90 Feb-19
What should I do? |
|
|
|
We are using a product that depends on the Batch Number to start with a certain character string. The Batch was created in error with the wrong character string. Batch Status is currently "110" which appears to be = Printing Computer Checks.
Question, through SQL can I safely rename the Batch or do I need to create another Batch.
Thanks!
|
|
|
|
Hey all,
I would like to run a SQL Job every day which runs a SQL command which deletes from a table where records are 60 days older than LSTORDDT. I also want to exclude certain CUSTNMBR fields in TEMPLATES (the same as in RM00101) that I have setup in a new table.
Any new CUSTNMBR added to the new table gets excluded also.
I have a Table called 'EXCLUDE' with one column called 'DebtorID' (char15) which is populated by 4 records.
The Table I wish to delete the records from is called 'TEMPLATES'
If I create a SQL job with this code:
DELETE FROM dbo.TEMPLATES WHERE LSTORDDT < DATEADD(d,-60,GETDATE()) AND NOT EXISTS ( SELECT NULL FROM dbo.EXCLUDE m WHERE m.DebtorID = CUSTNMBR )
GO
Is this correct for a SQL Job?
Thanks |
|
|
|
What table and column tells me that a customer is an EFT customer?
You have the Customer EFT Bank Maintenance screen, but not every customer is EFT, so what's the indicator/flag ??
|
|
|
|
I have a PO invoice with an amount variance on the invoice. When I manually enter the invoice, the amount calculates correctly. When pushing the transaction through econnect, the calculated variance amount is wrong.
In this XML, there is 1 line, PO'd at Unit Cost 90, Ext Cost 900. The invoice is at 80/800, and the variance is 1500 instead of 100. I can not seem to find where the calculation is going wrong...
<?xml version="1.0" encoding="utf-8"?>
<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<POPEnterMatchInvoiceType>
<eConnectProcessInfo xsi:nil="true"/>
<taRequesterTrxDisabler_Items xsi:nil="true"/>
<taUpdateCreateItemRcd xsi:nil="true"/>
<taUpdateCreateVendorRcd xsi:nil="true"/>
<taCreateVendorAddress_Items xsi:nil="true"/>
<taPopRcptLineTaxInsert_Items/>
<taPopDistribution_Items xsi:nil="true"/>
<taAnalyticsDistribution_Items xsi:nil="true"/>
<taPopEnterMatchLotInsert_Items xsi:nil="true"/>
<taPopEnterMatchSerialInsert_Items xsi:nil="true"/>
<taPopEnterMatchInvLine_Items>
<taPopEnterMatchInvLine>
<POPRCTNM>RCT1266</POPRCTNM>
<PONUMBER>PO2098</PONUMBER>
<QTYINVCD>10</QTYINVCD>
<ITEMNMBR>3-C2924A</ITEMNMBR>
<VENDORID>ADVANCED0001</VENDORID>
<UNITCOST>80.00</UNITCOST>
<EXTDCOST>800</EXTDCOST>
<POLNENUM>16384</POLNENUM>
</taPopEnterMatchInvLine>
</taPopEnterMatchInvLine_Items>
<taPopEnterMatchInvToShpMultiLine_Items>
<taPopEnterMatchInvToShpMultiLine>
<POPRCTNM>RCT1266</POPRCTNM>
<POPMtchShpRcpt>RCT1264</POPMtchShpRcpt>
<PONUMBER>PO2098</PONUMBER>
<QTYINVCD>10</QTYINVCD>
<VNDITNUM>3-C2924A</VNDITNUM>
<ShipRCPTLNNM>16384</ShipRCPTLNNM>
<VENDORID>ADVANCED0001</VENDORID>
<IVCLINNO>16384</IVCLINNO>
<VarianceAccount>000-4750-01</VarianceAccount>
<Revalue_Inventory>1</Revalue_Inventory>
</taPopEnterMatchInvToShpMultiLine>
</taPopEnterMatchInvToShpMultiLine_Items>
<taPopEnterMatchInvHdr>
<POPRCTNM>RCT1266</POPRCTNM>
<VNDDOCNM>INV1106</VNDDOCNM>
<receiptdate>3/12/19 12:00:00 AM</receiptdate>
<BACHNUMB>GLOBAL SEARCH</BACHNUMB>
<VENDORID>ADVANCED0001</VENDORID>
<SUBTOTAL>800</SUBTOTAL>
<PYMTRMID>Net 30</PYMTRMID>
<TAXSCHID>ALL DETAILS</TAXSCHID>
<USINGHEADERLEVELTAXES>1</USINGHEADERLEVELTAXES>
<CURNCYID>Z-US$</CURNCYID>
</taPopEnterMatchInvHdr>
<taMdaUpdate_Items xsi:nil="true"/>
</POPEnterMatchInvoiceType>
</eConnect>
|
|
|
|
I have a smartlist that I wish to see the underlying SQL that pulls back the data.
How does one do this? I have not had much luck.
I did look on the ASITAB20 table and it states the ASI_Table_Technical_Name is ASI_RM_Document_Explorer, but cannot find that table.
Any help would be greatly appreciate.
Will |
|
|
|
Dear Sirs
I have a situation creating an ODBC 32bit connection in SQL Server 2017 with sp1.
I'm using SQL Server Native Client 11 (11.00.6540) with diferent server names (Sql Server Name, IP, and localhost) and none of them works.
Firewall is off,
Have anyone has come up with issue that can help me.
I'm trying to connect Dynamics GP 2018 in Sql Server 2017.
I'll appreciate the help
Thank You
Richard Rivera |
|
|
|
I'm a .NET developer and am largely a novice with the GP table structures, so hopefully this makes sense..
All of our invoices are processed through the Bank Rec tables - specifically CM20202. Payments are batched into that table and then an external, third-party application reads and processes the records for payment. I have another application - an app that I'm writing - that needs to pull the record of some of those payments, tie them back to their corresponding Purchase Order, and send that information to another third-party site.
So as that third-party application processes transactions, I would like to read something from that CM20202 record - probably DOCNUMBR, since it appears to be unique - and work backwards. I believe the data I need for the Purchase Order lines is stored in POP10500, and I believe it contains the line items and receipts for each transaction processed against a PO.
What I need to know is this...is there a way to build a join back from CM20202 to POP10500?
If this doesn't make sense, ask a question and I'll try to clarify. I'm as much trying to understand how stuff works as solve my problem.
Thanks in advance!!
Joel |
|
|
|
Hi
I need to run a SQL script to identify users who have access to Fiscal Period Set Up [Tools | Set up | Company | Fiscal Periods] in GP 10. Can someone help with the script?
Thanks
Paul |
|
|
|
I am trying to export the GL accounts associated with the item classes so we can do a manual check in a spreadsheet to ensure they are setup correctly.
I found table IV40400, but cannot find the table to reference what GL accounts are setup against the item class.
Can anyone point me to that table?
Thank you,
Will |
|
|
|
| I have a client who is running GP10. I installed GP2018 on the workstations and since then, an error keeps appearing in GP10 saying "An error has occurred in the script on this page. Object doesn't support property or method "all" and points to a temp folder. I have cleared the temp folder and also installed OWC 2003 and removed Metrics and Outlook from the Home Page. How can this be resolved ? |
|
|
|
I have a client using the OLE Notes window to attached documents to PM Transactions. They are using GP2016. I believe that when they attach a Word document or PDF document it is stored somehow in the database on the more recent releases of GP. It seems like it stores this information in the CO00101 through CO00105 tables. I was able to locate these records and link back to the PM transaction records. GP somehow allows the user to open the attachments via the user interface from the Inquiry screens. Is there a way to open these attachments via a web browser via an SSRS report or through a URL?
Thanks,
John |
|
|
|
Have the following script in Great Plains 2015 that am trying to create a SmartList with & hitting "SQL script invalid". See the recommendations to enable debugging flags but don't want to take the chance of impacting anyone so close to month end. Anything in the script below happen to jump out as something you might have seen as an issue in GP 2015 ? The SQL runs well via SSMS. Thanks!
SELECT DB_NAME() AS [Company ID],
T1.[PONUMBER] as [PO Number],
CASE T1.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 'Cancelled'
END AS 'PO Status',
CASE T2.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 'Cancelled'
END AS 'PO Line Status',
T1.[USER2ENT] as [User to Enter],
T1.[DOCDATE] as [Document Date],
T1.[VENDORID] as [Vendor ID],
T1.[VENDNAME] as [Vendor Name],
T1.[PYMTRMID] as [Payment Terms ID],
isnull(T2.[JOBNUMBR], '') as [Job Number],
isnull(T2.[COSTCODE], '') as [Cost Code],
isnull(T2.[ITEMNMBR], '') as [Item Number],
isnull(T2.[ITEMDESC], '') as [Item Description],
isnull(T2.[VENDORID], '') [Vendor ID],
isnull(T2.[VNDITNUM], '') as [Vendor Item Number],
isnull(T2.[VNDITDSC], '') as [Vendor Item Description],
isnull(T2.[LOCNCODE], '') as [Location Code],
isnull(T2.[UOFM], '') as [U of M],
isnull(T2.[QTYORDER], 0) as [QTY Ordered],
isnull(T2.[QTYCANCE], 0) as [QTY Canceled],
isnull(T2.[UNITCOST], 0) as [Unit Cost],
isnull(T2.[EXTDCOST], 0) as [Extended Cost],
CONCAT (ACTNUMBR_1,ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4) as [InventoryIndex],
isnull(T3.[ACTDESCR], '') as [Account Description],
isnull(T2.[CURNCYID], '') as [Currency ID]
from POP10100 T1 with (nolock)
LEFT JOIN POP10110 T2 with (nolock)
on
T2.[PONUMBER] = T1.[PONUMBER] and T2.[POTYPE] = T1.[POTYPE]
LEFT JOIN GL00100 T3 with (nolock)
on
T3.[ACTINDX] = T2.[INVINDX]
where T1.[POSTATUS] in (1,2,3,4)
and T2.[POLNESTA] in (1,2,3) |
|
|
|
Error: You are not allowed to have negative actual costs
|
|