Customized Form and Security Access

On forms, dots (or periods) before and after the title header in a form inform you that this form has been customized.  This customized form will also require special permission for access.  If a user is having issues/errors for this customized form, check permissions.  Sometimes there will be strange error messages and not "access denied".

 

 

List All Current Companies

select interid,* from dynamics..sy01500

How to create a SmartList through SmartList Builder that will include the Status field values of the Requisition Management Audit History Document table


Step 1: Create a SQL View and pull this into SmartList Builder
Step 1A: Start the Support Administrator Console, Microsoft SQL Query Analyzer, or SQL Server Management Studio. To do this, use one of the following methods depending on the program that you are using.

Method 1: For SQL Server Desktop Engine
If you are using SQL Server Desktop Engine (also known as MSDE 2000), start the Support Administrator Console. To do this, click Start, point to All Programs, point to Microsoft Administrator Console, and then click Support Administrator Console.

Method 2: For SQL Server 2000
If you are using SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.

Method 3: For SQL Server 2005
If you are using SQL Server 2005, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

Method 4: For SQL Server 2008
If you are using SQL Server 2008, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

Step 1B: Run the following script against the Dynamics database:
CREATE VIEW Req_History_Doc
AS
SELECT RD.DocumentID Requisition_ID,
RL.ItemDescription Title,
RD.OriginatingName Created_by,
RD.CreatedDate Created_Date,
CASE RD.Status
WHEN 1 THEN 'Created'
WHEN 2 THEN 'Pending Final Approval'
WHEN 3 THEN 'Rejected'
WHEN 4 THEN 'Pending Transfer to PO'
WHEN 5 THEN 'Transferred to PO'
WHEN 6 THEN 'Voided'
END Status,
RD.LastToModifyName Modified_by,
RD.ModifiedDate Modified_Date,
RL.Item Item_Number,
RL.Vendor Vendor,
RL.PurchaseOrderDocument PO_Number,
RL.PurchaseAccount Account_Index,
GL.ACTNUMST Account,
RL.RequiredBy Required_by_Date,
RL.UnitPrice Price,
RL.Quantity QTY,
RL.UnitOfMeasure Unit,
RL.ExtendedPrice Extended_Price

FROM ReqMgmtAuditHistoryDocument RD
LEFT OUTER JOIN
ReqMgmtAuditHistoryLines RL
ON RL.DocumentID = RD.DocumentID
Join TWO..GL00105 GL on RL.PurchaseAccount = GL.Actindx

Step 2. Grant Security to the SQL View by doing the following:
a. Click Microsoft Dynamics GP, point to Tools, point to SmartList Builder, point to Security, and then click SQL Table Security.
b. Check the box for Dynamics under Databases, and then select the Views radio button. (Dynamics should be marked by default along with the Company database).
c. Scroll down and check the box for Req_History_Doc.
d. Click Ok.

Step 3. Create the SmartList in SmartList Builder.
a. Click Microsoft Dynamcis GP, point to Tools, point to SmartList Builder, and then click SmartList Builder.
b. Enter information in the following fields:
SmartList ID
SmartList Name
Item Name
Product: Microsoft Dynamics GP
Series: Purchasing
c. Click the plus sign (+) for Tables, and then click SQL Server Table.
d. In the Add SQL Table window click Dynamics, and then click the Views radio button.
e. Click Req_History_Doc (or whatever you named the View).
f. Click the plus sign (+) for Key Fields, and then click Field: Requisition_ID
g. Click Save.
h. On the SmartList Builder window select the fields you want on the SmartList, and then check the corresponding Default checkbox.
i. Click Save.

Step 4. Include the changes in SmartList.a. Click Microsoft Dynamics GP, and then click SmartList.
b. Click Ok to the following message:
SmartList Builder has detected changes to be made. Do you want to make these changes now?
*Note if you do not receive a message you could have SmartList already open. Close SmartList and re-launch.
c. In the SmartList Window expand Purchasing, expand the name of your SmartList. and then click the SmartList.

Dynamics system file definition and removing mods

Main Files:

DYNAMICS.EXE - main application.
DYNAMICS.SET - stores add-ons and file locations.

Development files:

DYNAMICS.VBA - stores modified VBA code.
FORMS.DIC - store form changes.

Save order line items on location change


Requirement:

When an order comes in, they are not sure where the order will be shipped from.   Currently, they have an 'ADMIN' site that they use when taking all the orders in.   Once the order is ready to be shipped, they will decide where the order should be shipped from based on quantity available and proximity to the customer site.   At this point, they have to change the site ID for each line on the sales order so that it will properly allocate the inventory at each site and pick up the correct GL accounts.

What they would like is the ability to change the Default Site ID on the header and then have a question pop up that will ask the user if they would like to roll down the change to all the existing line items.   When doing so, it should attempt to reallocate each line, just like if you were manually changing the sites on each line.

Resolution:

On the Sales Transaction Entry form, update the corresponding line items in table SOP10200 when the Default Site ID is changed.  I am also programmatically executing the save to save the header and refresh the form after the line items are saved to the DB.

======================================================================
Important before entering VBA code...

The window and fields must be added for modification
Under Tools, Customize: Add current window to Visual Basic and Add fields to Visual Basic.

Set required references (ADO objects, etc.)
Under Tools, References.
======================================================================

Code:

Dim cn As New ADODB.Connection
Private Sub DefaultSiteID_AfterUserChanged()
    Dim ret As Integer
    ret = MsgBox("Would like to roll down the change to all the existing line items?", vbYesNo, "Roll Down Changes")
   
    If ret = vbYes Then
        Dim rst As New ADODB.Recordset
        Dim strPaymentTerm As String
        Dim strDocNum As String
        Dim cmd As New ADODB.Command
   
        If cn.State = 0 Then openConnection
   
        cmd.ActiveConnection = cn
        cmd.CommandType = adCmdText
        cmd.CommandText = "UPDATE SOP10200 SET LOCNCODE = ? WHERE SOPNUMBE = '" & Me.DocumentNo & "'"
        cmd.Parameters.Append cmd.CreateParameter("@LOCNCODE", adVarChar, adParamInput, 11, Me.DefaultSiteID)
   
        Set rst = cmd.Execute
        strDocNum = Me.DocumentNo
        Me.Save.Value = 1
        Me.DocumentNo = strDocNum
        SendKeys ("{TAB}")
    End If
End Sub
Sub openConnection()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub



Allocation Process:

Two stored procedures were created.  One to update the line items for the sales order (sopUpdateWarehouse) and the other to update and recalculate the line items totals (dd_IVReallocate) as per the new warehouse/default site ID.

--sopUpdateWarehouse process explanation:

Input Parameters: @SOPTYPE, @SOPNUMBE, @LOCNCODE

A cursor is created to loop through a recordset of distinct items derived from sales items in SOP10100 (Sales Transaction Work) and SOP10200 (Sales Transaction Amounts Work) that does not equal the input parameter: warehouse/default site ID (@LOCNCODE).  Each item found is then updated in SOP10200 with the new warehouse/default site ID.

--dd_IVReallocate process explanation:

Input Parameters: @ITEMNMBR, @LOCNCODE

This stored procedure was created to update the total quantity in the IV00102 (Item Quantity Master) table to reflect the warehouse change.  Two quantity fields (QUANTITY and TRXQTY) are summed from SOP10200 (Sales Transaction Amounts Work) and IV10001 (Inventory Transaction Amounts Work) filtered by ITEMNMBR and LOCNCODE.  This summed value then updates the ATYALLOC column in IV00102 (Item Quantity Master) table.