Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In

Archives

 

It's true. They do. 

Try this:

CREATE TABLE TrigTest (RowID INT)
INSERT INTO TrigTest (RowID) VALUES (1)
 
 
 
 
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N't_TrigTest'
       AND    type = 'TR')
    DROP TRIGGER t_TrigTest
GO
 
CREATE TRIGGER t_TrigTest ON TrigTest FOR update
 
AS
 
BEGIN
    PRINT 'trigger executing'
end
 
UPDATE TrigTest SET rowid = 2 WHERE rowid = 3

When you run the update statement at the bottom of the script, the trigger will fire. 

Want to know an easy way to stop this behavior?

 

 

This stored procedure wraps the eConnect taSopDeleteDocument stored procedure and makes it easier to call. 

I'm trying to create a view for SOP order lines. It needs to show all Item Numbers from SOP10200 in one column but another column that returns the kit item when it exists - i.e. shows the same Item Number if the component sequence is 0, but if the component sequence is greater than 0 (kit components), it shows the item number with the same line sequence and component sequence of 0. Does someone know how to do this? Any help would be appreciated. 

An example is the below where HDWR-DCD-00001 is a kit and WATCH is a regular sales inventory item.

SOPTYPE SOPNUMBE LNITMSEQ CMPNTSEQ  ITEMNMBR  Returned Item Number
 2 ORDM1010 16384  0 HDWR-DCD-0001  HDWR-DCD-0001
  2  ORDM1010  16384  16384 ITCT-CIR-CD85  HDWR-DCD-0001
  2  ORDM1010  16384   32768 RESR-TRR-68KM  HDWR-DCD-0001
  2  ORDM1010  16384   49152  RMTL-CAP-10MF  HDWR-DCD-0001
 2  ORDM1003  16384 0 WATCH WATCH

This is what I have so far. Instead of 'kit component' for ITEM NUMBER, I need it to return the actual kit item number like the above:

select 
B.BACHNUMB AS 'BATCH NUMBER',
B.DOCDATE AS 'ORDER DATE',
B.SHIPMTHD AS 'SHIPPING METHOD',
A.SOPTYPE as 'SALES ORDER TYPE',
B.DOCID AS 'DOCUMENT ID',
A.SOPNUMBE as 'SALES ORDER NUMBER',
B.CUSTNMBR AS 'CUSTOMER NUMBER',
B.CUSTNAME AS 'CUSTOMER NAME',
B.SHIPTONAME AS 'SHIP TO NAME',
A.ITEMNMBR as 'ITEM NUMBER SEARCH',
A.ITEMDESC AS 'ITEM DESCRIPTION',
(select CASE  
WHEN A.CMPNTSEQ > '0' THEN 'Kit Component'
ELSE A.ITEMNMBR
END as CMPNTSEQ) AS 'ITEM NUMBER',
A.UOFM AS 'UNIT OF MEASURE',
A.LOCNCODE AS 'SITE',
A.QUANTITY AS 'QTY ORDERED',
A.QTYREMAI AS 'QTY REMAINING',
A.ATYALLOC AS 'QTY ALLOCATED',
A.QTYTBAOR as 'QTY BACKORDERED',
A.QTYFULFI AS 'QTY FULFILLED',
A.UNITPRCE AS 'UNIT PRICE',
CASE C.ITEMTYPE 
WHEN 3 THEN 'Kit Header'
WHEN 1 THEN 'Sales Inventory'
ELSE 'Non-Inventory'
END 'ITEM TYPE',
A.LNITMSEQ AS 'LINE SEQ',
(select CASE  
WHEN A.CMPNTSEQ > '0' THEN 'Kit Component'
ELSE ''
END as CMPNTSEQ) AS 'KIT ITEM',
CASE D.DELETE1
WHEN 0 THEN 'Hold'
Else ''
END 'PROCESS HOLD',
(SELECT COUNT(*) FROM SOP10200 z WHERE z.SOPTYPE = a.SOPTYPE AND z.SOPNUMBE = a.SOPNUMBE) AS LineCount
FROM SOP10200 A
inner join SOP10100 B ON A.SOPTYPE = B.SOPTYPE AND A.SOPNUMBE = B.SOPNUMBE
inner join IV00101 C ON A.ITEMNMBR = C.ITEMNMBR
LEFT OUTER JOIN SOP10104 D ON A.SOPNUMBE = D.SOPNUMBE
where A.SOPTYPE = 2 and A.QTYREMAI > 0 AND B.VOIDSTTS = 0 

 

HITB report is erroring out after updating customer to Dynamics GP 18.3.

 

SSRS Report Error

An error has occurred during report processing: (rsProcessingAborted)

Query execution failed for dataset ‘HITB’. (rsErrorExecutingCommand)

Procedure or function seeHITB has too many arguments specified.

 

Previous Issue with HITB after GP update

This stored procedure needed to be modified last year after an update because checkboxes were added for Item Quantity and Amount in HITB report in GP Report Writer. The fix was adding QTYONHND int NOT NULL when @ temptable is created and when ivCreateHistoricalIVTrialBalance is called, the new parameter @I_fIncludeZeroQtyValue input parameter needed a value by either adding an input for users on the report itself or setting a default value of 1 (true).

Thank you in advance!

 

Scott

I'm trying to report the average qtysold from table IV10201 by item number for:

the previous 7 days (or week)

the previous 30 days (or month)

show the difference of the last 30 days to the 30 days prior to that

show the difference of last 30 days to one year ago.

since there is more than one row per date and per item, I think I need to sum the qty sold based on a date parameter but I'm having a hard time determining the right way to write this. Any suggestions? 

Hi All we have 35 live companies that each month some poor soul namely me needs to go though and close the periods, is there a Macro / vB code that any one knows of that can automate this process?

 

Thanks  

I'm trying to automate the aging process, using code in the knowledgebase article 2901.  Does anyone know the available parameters?  Some are obvious, but I want to make sure I understand them all.

I believe the first one is balance type so I assume 0 is for "All."  The second is starting customer ID, third is ending customer ID and the fourth is the aging date.  The fifth appears to be Aging Cycle, but what is the significance of the value 127?  I've seen other examples using 16, so I'm not sure.  The sixth value is apparently Age Finance Charges so I assume 0 would be a no value.  The seventh looks to have something to do with Custom Range, but I don't know the significance of the value.  Does 0 mean "All?"

I'm trying to simulate the vales as indicated in the GP screenshot below, so if anyone knows the values I should use for Statement Cycle and Custom Range I would appreciate the help.

DECLARE @O_iErrorState int, @I_dAgingDate datetime
select @I_dAgingDate = convert(varchar(10), GetDate(), 102)
EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate, 127, 0, 0, '', @O_iErrorState OUT
SELECT @O_iErrorState

 

image.png

I have an integration with SmartConnect that is creating an Equipment Log.  The map succeeds however there are no distribution entries created.  Any suggestions what to check?

I have looked at the node in SmartConnect and there are no Distribution maps I can add either.

Thanks

Hi there what'll be the impact if I update currcost directly in IV00101? There's a situation we need to update the current cost in the system. Can someone advise the correct way to do it? Thank you very much!
We have an extender window set up to link to RM Distributions.  I am able to report on the information but if a distribution is altered before posting then the extender tables have the original and new lines. How can I exclude the original (i.e. old lines) since extender doesn't seem to remove them even if the distribution is deleted and recreated?






Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
site