Tables where client invoice paid details are stored

Question

Hi, 
 Could someone provide me the AT Invoice Payment table name? We need this for an integration with a CRM. Specifically we need to know what are the outstanding balances owen by our clients. We made the same inquiry before but unfortunately the response did not help us solve the issue. (See below previous response) 
 Thank you 
 Offline Richard S. Ridings 2 months ago Depending on exactly what you are looking to pull in, the DataDict.pdf in the Customers sections shows the tCusTr and tCusTrDt tables that include the information you see in the Customer Aged Reports. 
 tCusTr is essentially the header info for an Invoice or Deposit. The tCusTrDt table is for any payment transactions that affect the Invoice or Deposit. 
 There is also the Miscellaneous section of the DataDict.pdf that shows the tRcptHdr table. This table helps with the batch printing and I believe some of the lookup programming for the Receipt module in Sage 50.

GwG · Answer

as mentioned by Richard they are stored in tcustr and tcustrdt tables. nTranstype =1 is a payment. Below is a general query to dump the customer transactions using MySQL workbench 
 SELECT sName, tcustr.dtDate, tcustrdt.dtdate, tcustr.sSource, tcustrdt.sSource, tcustr.dPreTaxAmt, tcustrdt.dAmount FROM tcustomr inner join tcustr on tcustomr.lid=tcustr.lcusid inner join tcustrdt on tcustr.lId = tcustrdt.lcustrid;

Sage 50 Canada

Tables where client invoice paid details are stored

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING