DynDeveloper.com

It turns out that when you delete data in Dynamics GP that is tied to Extender data, the Extender data does not automatically delete. In order to get it to delete, you need to link Extender to the underlying table. 

Not horrible, but you need to be aware of that in case you're reporting off the Extender data.

This was just a little bit tricky, I didn't think the table naming was all that obvious... so here's the process

User btamulis sent in this query and we thought it worth keeping. It provides a list of user/companies, and the roles that they're assigned to.

I see from Googling that you can change the due date for a receivables document by using the "Edit Transaction Information"  under the Sales tab.  However, it won't let you change the assigned terms and I'm wondering if there is an issue if I do it via SQL update?

I changed some due dates in a test company and then ran the aging update, and the due date seems to remain constant.  However,  I'd rather update the payment terms for the transactions so that when people look at the records the doc date and due date match what would be calculated based on the terms.

Is there any reason I should not update the terms along with the due date for receivables?

Necesito hacer  INSERT AND UPDATE en las tablas de EXTENDER desde codigo vba, pero me rechaza todos los intentos.

Existiran procedimientos almacenados nativos para estas operaciones? si los conocen les agradesco la información.

Saludos.

Editor 10/8

I need to do INSERT AND UPDATE in the EXTENDER tables from vba code, but it rejects all attempts.
Will there be native stored procedures for these operations? if you know them I like the information.

Okay - I would like a simple 'report' which shows one record for every GP user/Company combination - however I need to include a longer stringer of the first 7 or 8 Security Roles.

This SQL script has one row for every user/company/role.

use DYNAMICS
SELECT
['User Master'].USERID AS 'UserID'
,['User Master'].USERNAME AS 'Username'
----,['User Master'].USRCLASS AS 'UserClass'
----,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
,ISNULL(['Company Master'].INTERID, '') AS 'CompanyID'
,ISNULL(['Company Master'].CMPNYNAM, '') AS 'CompanyName'
,left(ISNULL(['Security Assignment User Role'].SECURITYROLEID,  ''), 11) AS 'SecurityRoleID'
,case when ['Security Assignment User Role'].SECURITYROLEID='POWERUSER' then 'Yes' else 'No' end as PowerUser
-----,left(ISNULL(['Security Roles Master'].SECURITYROLENAME, ''), 11) AS 'SecurityRoleName'
FROM
SY01400 AS ['User Master']
LEFT JOIN
SY40400 AS ['Class Master']
ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
SY60100 AS ['User-Company Access']
ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
SY10500 AS ['Security Assignment User Role']
ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
SY09100 AS ['Security Roles Master']
ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
SY01500 AS ['Company Master']
ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
GO

It returns something like this: (desired output would be 4 rows - one for each user/company with one additional column 'PrimaryUse')

I would like to return one row for every user/company with a single column that strings together 7 or 8 roles into a singular piece of data. Basically pivot the rows by concatenating the roles into a single string

Anybody do this before? 

I have a Payables Query:

SELECT P.VENDORID VendorID,
V.VENDNAME VendorName,
P.VCHRNMBR Voucher,
P.dateEntered EnterDate,
P.DOCDATE DocDate,
P.GPGLpostDate,
P.DUEDATE DueDate,
P.DOCNUMBR DocumentNumber,
Cast(P.DOCAMNT as decimal (10,2)) DocumentAmount,
Cast(P.CURTRXAM as decimal (10,2)) as Balance,
P.TRXDSCRN [Description]


----------\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
----------select * from PM30200
FROM (SELECT VENDORID, VCHRNMBR, DOCTYPE,
-----DATEDIFF(month, 'DEX_ROW_TS','DOCDATE') as Months,
CAST(DEX_ROW_TS as Date) as dateEntered,
CAST(DOCDATE as Date) DocDate, Cast(PSTGDATE as Date) SysPostDate,
CAST(PSTGDATE as Date) GPGLpostDate,
Cast(DUEDATE as Date) DueDate, 
DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
FROM PM20000 where DOCTYPE='1' and VOIDED='0' and DOCDATE > '2021-01-01'
UNION ALL
SELECT VENDORID, VCHRNMBR, DOCTYPE,
-----DATEDIFF(month, 'DEX_ROW_TS','DOCDATE') as Difference,
CAST(DEX_ROW_TS as Date) as dateEntered,
CAST(DOCDATE as Date) DocDate, Cast(PSTGDATE as Date) SysPostDate,
CAST(PSTGDATE as Date) GPDate,
CAST(DUEDATE as Date) DueDate, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED 
FROM PM30200 where DOCTYPE='1' and VOIDED='0' and DOCDATE > '2021-01-01') P


     INNER JOIN
PM00200 V
ON V.VENDORID = P.VENDORID

Which returns a dataset as follows:

My client is trying to identify Payable Vouchers entered into GP and backdated into a different month. The first record has a document date of September 24th but was actually entered (using the SQL date/timestamp) on Oct 2.....

I tried to construct a Month Date Diff  but was unsuccessful - I get a SQL error about converting 

How do I restrict my query to just documents where month entered <> document month?