Database

Written by Peter on September 26, 2012

DIY - SQL Server upkeep

How’s your SQL Server running?  Backups taking too much space?  How is Infor helping you with this?  Your channel partner?  Where can you get help?

SQLSaturdayI went to a free seminar last Saturday by the folks at SQLSaturday and learned a lot!  I am sure there is one in your area soon.  Look for the next one here:  http://www.sqlsaturday.com.

Just a few tidbits I learned from some SQL MVPs:

Hopefully, some of this information will help you next time someone screams “Visual is SLOW!!”

Challenge Completed


Peter Boers is our special guest author at visualnuggets.com and is an active participant in the VISUAL community.  Peter has recently taken on the responsibility for maintaining the web site for the newly formed Visual Enterprise Global User Group (VEGUG).

Connect with him online at LinkedIn 

 

Written by Richard on June 13, 2012

How to be notified of VISUAL patch releases

Always the last to know?

Have you ever found yourself spending hours racking your brain trying to determine whether an issue you found in VISUAL is bug-related or simply a lack of knowledge of the module.  Only to find out that INFOR has already acknowledged it as a "bug" and has already fixed it in a patch released months ago

Finding this out is both relieving and frustrating.  Relieved to know it's not YOU, but frustrating to know that you wasted your time on an known issue which has been reported and fixed long ago.

So in order to keep on top of things, INFOR offers you to be notified by email when patches are released.  And in these releases you'll find not only the latest patches but a pdf detailing what was updated and fixed.

It's easy to sign up:

  1. Log into InforXtreme Support site.  You'll need to be on maintenance, of course.
        Infor VISUAL ERP Express - InforXtreme Support 
  2. Enter the Solution # of the patch release for the VISUAL application and version your interested in.  Refer the Nuggets to refer to where I list the popular patches by solution #.
    In this example I'm using Solution #955942 VISUAL 7 patch release.
        Infor VISUAL ERP Express - InforXtreme Support   
  3. When the following window appears, click on the SIGN UP link located to the right of the solution (as shown).
         Infor VISUAL ERP Express - InforXtreme Support Sign Up     
  4. Now enter your email and click on the SIGN UP button.
         Infor VISUAL ERP Express - InforXtreme Support - Enter email     

And Bob's your uncle (what does that mean anyway? and who's Bob?)

 

Written by Richard on June 6, 2012

Reengineering the outside service dispatch browse window

The Outside Service Dispatch Entry window is one module I don't spend too much time in, but when I do, I always find myself scrolling (more like scrambling) through the different windows and screens - trying to get answers for any one of the following questions (if not more).

  • Where is it?
  • Who has it? What else do they have?
  • When can we expect it
  • What else do we have out for that job?
  • Where did we dispatch that part out to?
  • and the list goes on...

So I decided to do a little "retooling" of the dispatch BROWSE window and have it display the information that tends to be asked over and over and over again. 

So I now have a browse window that allows me to search dispatches at the order line level (details) as oppose to only the header information.  This enables me to now search by part id, work order, back order qty, dates and so on.

Click on the following screenshot to view a "stripped down" version of the my modified browse window

Infor ERP VISUAL - Ourside Service Dispatch Browse window

NOW the browse window allows me to filter as needed and drill down to the individual dispatches.  I actually have ALL the information I need straight from the browse window (just like a report). On top of all that, because I'm using the browse window, I can export my filtered results to excel for further analysis and reporting.

All this within the VISUAL interface.

So if you find yourself in this position when ask about your dispatched outside services...

What?

Try this:

  1. Create a SQL VIEW named zview_DISPATCH_BROWSE using the query in this download.
     
  2. Find and open your VMBROWSE.ini file (should be located in you local VISUAL directory).
     
  3. Replace the entire [Dispatch] section with the following:

    [Dispatch]
    version=26
    table=SERVICE_DISPATCH,zview_DISPATCH_BROWSE,VENDOR
    title=Service Dispatches
    key1=SERVICE_DISPATCH.ID
    sort=SERVICE_DISPATCH.ID
    order=1
    where=SERVICE_DISPATCH.VENDOR_ID=VENDOR.ID AND SERVICE_DISPATCH.ID=zview_DISPATCH_BROWSE.DISPATCH_ID(+)
    searchfirst=1
    autorefresh=0
    decode=SERVICE_DISPATCH.MARKED_FOR_PURGE,Boolean,PURCHASE_ORDER.BACK_ORDER,Boolean,PURCHASE_ORDER.EXCH_RATE_FIXED,Boolean,PURCHASE_ORDER.MARKED_FOR_PURGE,Boolean,PURCHASE_ORDER.POSTING_CANDIDATE,Boolean,VENDOR.REPORT_1099_MISC,Boolean,PURCHASE_ORDER.STATUS,Status,VENDOR.MATCH_TYPE,MatchType,VENDOR.TERMS_DISC_TYPE,TermsType,VENDOR.TERMS_NET_TYPE,TermsType
    autobrowse=1

    colA=SERVICE_DISPATCH.ID,Dispatch ID,1,1.08,5,0,
    colB=zview_DISPATCH_BROWSE.WORKORDER_BASE_ID,Base ID,2,1.05,5,0,
    colC=SERVICE_DISPATCH.PURC_ORDER_ID,PO,3,1.12,5,0,
    colD=VENDOR.NAME,Vendor,4,2.98,5,0,
    colE=VENDOR.ID,Vendor ID,5,1.10,5,0,
    colF=zview_DISPATCH_BROWSE.STATUS,PO Status,6,0.90,5,0,
    colG=zview_DISPATCH_BROWSE.PART_ID,Part ID,7,1.07,5,0,
    colH=zview_DISPATCH_BROWSE.USER_ORDER_QTY,Qty Ordered,8,1.13,3,0,FORMAT_Decimal
    colI=zview_DISPATCH_BROWSE.TOTAL_USR_RECD_QTY,Qty Received,9,1.18,3,0,FORMAT_Decimal
    colJ=zview_DISPATCH_BROWSE.LN_BACKORDER_QTY,Backorder Qty,10,1.32,3,0,FORMAT_Decimal
    colK=zview_DISPATCH_BROWSE.LN_DESIRED_RECV_DATE,Desired Recv Date,11,1.90,5,0,
    colL=zview_DISPATCH_BROWSE.LN_PROMISE_DATE,Promise Date,12,1.87,5,0,


    If you don't want to replace the entire section, then just be sure to modify the lines HIGHLIGHTED in YELLOW


You should be good to go

Written by Richard on May 25, 2012

Calling a stored procedure from a macro

Whenever possible, I like to use stored procedures in macros when the process involves some sort of database update or manipulation.  This way it limits access to the code and reduces the chance of someone "accidentally" changing it within the macro dialog window.  Yes - they can still throw a wrench into the macros script  - but at least I can contain it.

Calling a stored procedure is relatively easy to do. 

Here's a walk through...

Keeping it simple, the following script will create a stored procedure (named SP_UpdateUDF) to update the USER_10 field for a part in the PART table.  The stored procedure has ONE parameter which is the part id to update.  The script also grants access to PUBLIC to give everyone permission to execute the store procedure.

CREATE proc dbo.SP_UpdateUDF
@I_PartID VARCHAR(30)
AS
SET NOCOUNT ON
--For synchronization machine id placement
UPDATE PART SET USER_10='COMPLETE' WHERE  ID = @I_PartID
SET NOCOUNT OFF
GO
GRANT EXECUTE ON dbo.SP_UpdateUDF TO PUBLIC
GO

 
Now here's the VISUAL macro to call the stored procedure:

Const adParamInput = &H0001
Const adVarChar = 200

Dim strServerName
Dim strDatabase
Dim strUserName
Dim strPassword
 
strServerName="E N T E R    N A M E    O F    D A T A B A S E    S E R V E R"
strDatabase="E N T E R    N A M E    O F    D A T A B A S E"
strUserName="E N T E R    U S E R N A M E"
strPassword="E N T E R    P A S S W O R D"

strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"

Set Conn = CreateObject("ADODB.Connection")
conn.open strcnn

Set Cmd = CreateObject("ADODB.Command")
Cmd.ActiveConnection = conn

' Set up the parameter for our Stored Procedure *** watch the line wrap ***
Cmd.Parameters.Append Cmd.CreateParameter("I_PartID", adVarChar, adParamInput , 30, "E N T E R    Y O U R    P A R T    I D")
Cmd.CommandText = "SP_UpdateUDF"
Cmd.Execute

 

Written by Richard on January 14, 2012

Considering SSRS? The first step is the hardest

If you're like me, I'm always a bit apprehensive when venturing into projects involving new platforms or applications.  And that's because I know there's going to be a learning curve - with my luck it'll be a steep one! 

ChallengeAcceptedThat's not to say that I'm not up for the challenge.  But when there's so many projects and with so little time  - you want to make the most of your valuable time.  For me, when I take on something new, I want to make sure it's worth the time and effort.

Learning SSRS (SQL Server Reporting Services) was definitely time well spent.  And I now use it as my primary reporting application.

I'm sure you know all the benefits of SSRS.  If you don't, just google it.  And in case you don't know - SSRS is free if you own SQL Server (2005 or greater).  There's no shortage of resources on the internet that can get you started.

Here are 2 posts in particular to help you with your 1st step:

How to Install SQL Server 2008 Reporting Services
This post shows you (with screenshots) how to quickly get SSRS up and running in your environment.  It also has additional links to guide through additional settings.

SQL Server Reporting Services 2008 Tutorial in 5 Minutes or "How to send my boss a report from a view quickly"
Once you have SSRS up and running, refer to this post to see how easy it is to create a report and distribute it.

 

Written by Richard on September 9, 2010

Searching all the tables that reference a particular data field (column name)

Here's one for your bag of tricks ... and we can all thank Dave Giusto (once again).

Use the following script (for SQL Server..sorry) when you what to find out all the places where a piece a data may be stored in the visual tables. 

To use the script:

  1. Download the SQL server script Tables & Views Search Script
     
  2. Open SQL Server Management Studio
     
  3. Select the visual database and click on the New Query
     
  4. Paste the script in the query window
     
  5. Modify the script to search for the data(column) you want. To do this, set the @col_name variable to the name of the column. You can use the '%' as a wildcard..

    For example, to search for CARTON:
    set @col_name ='%carton%'
     
  6. Next, indicate whether you want the script to search TABLES OR search VIEWS (yes... views too!). To do this, set the @structure variable to either 'U' for TABLES or 'V' for VIEWS.

    For example, to search in tables:
    set @structure = 'U'
     
  7. Execute the script.
     

Now, the resulting list may not be 100% complete due to varying naming convention of the column name but the script does provide you with a good starting point


Screen shot of the script

Sqlscript


Screen shot of the results:
Sqlscriptresults


Dave currently works out of the New York office of Synergy Resources and can be reached there or via the Visual Manufacturing International User Group (VMIUG) under the handle dave_giusto.

 

Written by Richard on July 22, 2009

Where is this inventory supply coming from?

Last week in Where is this demand coming from? I wrote about the different areas of Visual where demand can be placed on parts.  This time I'm writing about how supply is created. 

The following areas makeup the supply for a part (apart from quantities on hand):

1. Purchase Orders
2. Work Orders (and co-products)
3. Inter-branch Transfers
4. Planned Orders (and co-products)

The Material Planning Window is a great source to get both demand and supply details for a given part.  All supply-related information can be found to the right of the "Projected Qty" column.

Below is a single SQL query that returns a detail list of all existing supply sources for a part (similar to what you would see in the Material Planning Window).  The query uses the UNION operator which combines the results from several queries to generate one recordset.  When using the UNION operator, you must ensure that each SELECT statement has the same structure (same # of columns, same data type, same column order).

You can Download the query here.


REMEMBER
to change [ENTER PART ID] to your part ID in the query.

SELECT POL.PART_ID,
'PO' AS Supply_Type,
POL.PURC_ORDER_ID AS Order_ID,
CONVERT(varchar(5),ISNULL(PDL.DEL_SCHED_LINE_NO,POL.LINE_NO)) AS Lot_ID,
'0' AS Split_ID,
'' AS Sub_ID,
ISNULL(PDL.DEL_SCHED_LINE_NO,0) AS Del_Sched_Line_No,
COALESCE(PDL.WAREHOUSE_ID,POL.WAREHOUSE_ID,P.WAREHOUSE_ID) AS Supply_Warehouse,
CONVERT(CHAR(9),COALESCE(PDL.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,P.DESIRED_RECV_DATE),101) AS Date_Due,
(ISNULL(PDL.ORDER_QTY,POL.ORDER_QTY)) AS Total_Qty_Due,
(ISNULL(PDL.RECEIVED_QTY,POL.TOTAL_RECEIVED_QTY)) AS Qty_Received,
P.STATUS
FROM (dbo.PURC_ORDER_LINE POL LEFT JOIN dbo.PURC_LINE_DEL PDL ON (POL.LINE_NO = PDL.PURC_ORDER_LINE_NO) AND (POL.PURC_ORDER_ID = PDL.PURC_ORDER_ID)) INNER JOIN dbo.PURCHASE_ORDER P ON POL.PURC_ORDER_ID = P.ID
WHERE (((POL.PART_ID) ='[ENTER PART ID]') AND ((P.STATUS)<>'X' And (P.STATUS)<>'C') AND ((POL.ORDER_QTY)>POL.TOTAL_RECEIVED_QTY) AND ((POL.LINE_STATUS)='A'))
UNION ALL
SELECT W.PART_ID,
'WO' AS Supply_Type,
W.BASE_ID AS Order_ID,
W.LOT_ID,
W.SPLIT_ID,
W.SUB_ID,
0 Del_Sched_Line_No,
W.WAREHOUSE_ID AS Supply_Warehouse,
CONVERT(CHAR(9),ISNULL(W.DESIRED_WANT_DATE,'12/31/2099'),101) AS Date_Due,
W.DESIRED_QTY AS Total_Qty_Due,
W.RECEIVED_QTY AS Qty_Received,
W.STATUS
FROM dbo.WORK_ORDER W
WHERE (((W.PART_ID) ='[ENTER PART ID]') AND ((W.STATUS)<>'X') And ((W.STATUS)<>'C') AND ((W.DESIRED_QTY)>W.RECEIVED_QTY) AND ((W.TYPE)='W') AND ((W.SUB_ID)='0') AND ((W.WBS_PROJECT)='N'))
UNION ALL
SELECT CP.PART_ID,
'CP' AS Supply_Type,
W.BASE_ID AS Order_ID,
W.LOT_ID,
W.SPLIT_ID,
W.SUB_ID,
0 Del_Sched_Line_No,
ISNULL(CP.WAREHOUSE_ID,W.WAREHOUSE_ID) AS Supply_Warehouse,
CONVERT(CHAR(9),ISNULL(W.DESIRED_WANT_DATE,'12/31/2099'),101) AS Date_Due,
CP.DESIRED_QTY AS Total_Qty_Due,
CP.RECEIVED_QTY AS Qty_Received,
W.STATUS
FROM dbo.CO_PRODUCT CP INNER JOIN dbo.WORK_ORDER W ON CP.WORKORDER_TYPE = W.TYPE AND CP.WORKORDER_BASE_ID = W.BASE_ID AND CP.WORKORDER_LOT_ID = W.LOT_ID AND CP.WORKORDER_SPLIT_ID = W.SPLIT_ID AND CP.WORKORDER_SUB_ID = W.SUB_ID
WHERE (((CP.PART_ID) ='[ENTER PART ID]') AND ((W.STATUS)<>'X') And ((W.STATUS)<>'C') AND ((CP.DESIRED_QTY)>CP.RECEIVED_QTY) AND ((CP.WORKORDER_TYPE)='W') AND ((W.WBS_PROJECT)='N')  AND ((CP.LINE_STATUS)='A'))
UNION ALL
SELECT IBL.PART_ID,
'IBT' AS Supply_Type,
IBL.IBT_ID AS Order_ID,
CONVERT(varchar(5),IBL.LINE_NO) AS Lot_ID,
'' AS Split_ID,
'' AS Sub_ID,
0 AS Del_Line_No,
IB.TO_WHSE_ID AS Supply_Warehouse,
CONVERT(CHAR(9),ISNULL(IB.DESIRED_SHIP_DATE,'12/31/2099'),101) AS Date_Due,
IBL.ORDER_QTY AS Total_Qty_Due,
IBL.RECEIVED_QTY AS Qty_Received,
IB.STATUS
FROM dbo.IBT_LINE IBL INNER JOIN dbo.IBT IB ON IBL.IBT_ID = IB.ID
WHERE (((IBL.PART_ID) ='[ENTER PART ID]') AND ((IBL.ORDER_QTY)>IBL.RECEIVED_QTY) AND ((IB.STATUS)<>'X' And (IB.STATUS)<>'C') AND ((IBL.LINE_STATUS)='A'))
UNION ALL
SELECT PL.PART_ID,
'PL' AS Supply_Type,
PL.PART_ID AS Order_ID,
CONVERT(varchar(5),PL.SEQ_NO) AS Lot_ID,
'' AS Split_ID,
'' AS Sub_ID,
0 AS Del_Line_No,
PL.WAREHOUSE_ID AS Supply_Warehouse,
CONVERT(CHAR(9),ISNULL(PL.WANT_DATE,'12/31/2099'),101) AS Date_Due,
PL.ORDER_QTY AS Total_Qty_Due,
0 AS Qty_Received,
'' AS Status
FROM dbo.PLANNED_ORDER PL
WHERE (((PL.PART_ID)='[ENTER PART ID]'))
UNION ALL
SELECT CP.PART_ID,
'PLCP' AS Supply_Type,
CP.PART_ID AS Order_ID,
CONVERT(varchar(5),PL.SEQ_NO) AS Lot_ID,
'' AS Split_ID,
'' AS Sub_ID,
0 AS Del_Line_No,
ISNULL(CP.WAREHOUSE_ID,PL.WAREHOUSE_ID) AS Supply_Warehouse,
CONVERT(CHAR(9),ISNULL(PL.WANT_DATE,'12/31/2099'),101) AS Date_Due,
CP.DESIRED_QTY AS Total_Qty_Due,
0 AS Qty_Received,
'' AS Status
FROM dbo.CO_PRODUCT CP INNER JOIN dbo.PART P ON CP.WORKORDER_BASE_ID = P.ID AND CP.WORKORDER_LOT_ID = P.ENGINEERING_MSTR INNER JOIN dbo.PLANNED_ORDER PL ON CP.WORKORDER_BASE_ID = PL.PART_ID
WHERE (((CP.PART_ID)='[ENTER PART ID]'))
ORDER BY 1, 9
Written by Richard on July 17, 2009

Where is this inventory demand coming from?

In Visual, the demand placed on parts come from the following 4 areas:

1. Customer Orders
2. Work Orders
3. Inter-branch Transfers
4. Planned Work Orders

You can easily get demand information by using in the Material Planning Window.  All the demand for a part will be listed to the left of the "Projected Qty" column.

If you are wanting to "re-create" this information (or some variation of it) using SQL you would need to query all 4 possible demand areas and combine them using the UNION operator.  When using the UNION operator, you must ensure that each SELECT statement has the same structure (same # of columns, same data type, same column order).

Below is an example of what that query may look like (it's not as complicated as it looks!).  You can download this query here.


REMEMBER
to change [ENTER PART ID] to your part ID in the query.

SELECT COL.PART_ID,
'CO' AS Demand_Type,
COL.CUST_ORDER_ID AS Order_ID,
CONVERT(varchar(5),ISNULL(CDL.DEL_SCHED_LINE_NO,COL.LINE_NO)) AS Lot_ID,
'0' AS Split_ID,
'' AS Sub_ID,
ISNULL(CDL.DEL_SCHED_LINE_NO,0) AS Del_Line_No,
COALESCE(CDL.WAREHOUSE_ID,COL.WAREHOUSE_ID,C.WAREHOUSE_ID) AS Demand_Warehouse,
CONVERT(CHAR(9),COALESCE(CDL.DESIRED_SHIP_DATE,COL.DESIRED_SHIP_DATE,C.DESIRED_SHIP_DATE),101) AS Date_Required,
(ISNULL(CDL.ORDER_QTY,COL.ORDER_QTY)) AS Total_Qty_Required,
(ISNULL(CDL.SHIPPED_QTY,COL.TOTAL_SHIPPED_QTY)) AS Qty_Issued,
C.STATUS
FROM (dbo.CUST_ORDER_LINE COL LEFT JOIN dbo.CUST_LINE_DEL CDL ON (COL.LINE_NO = CDL.CUST_ORDER_LINE_NO) AND (COL.CUST_ORDER_ID = CDL.CUST_ORDER_ID)) INNER JOIN dbo.CUSTOMER_ORDER C ON COL.CUST_ORDER_ID = C.ID
WHERE (((COL.PART_ID) ='[ENTER PART ID]') AND ((C.STATUS)<>'X' And (C.STATUS)<>'C') AND ((COL.ORDER_QTY)>COL.TOTAL_SHIPPED_QTY) AND ((COL.LINE_STATUS)='A'))
UNION ALL
SELECT R.PART_ID,
'WO' AS Demand_Type,
R.WORKORDER_BASE_ID AS Order_ID,
R.WORKORDER_LOT_ID AS Lot_ID,
R.WORKORDER_SPLIT_ID AS Split_ID,
R.WORKORDER_SUB_ID AS Sub_ID,
0 AS Del_Line_No,
R.WAREHOUSE_ID AS Demand_Warehouse,
CONVERT(CHAR(9),ISNULL(R.REQUIRED_DATE,'12/31/2099'),101) AS Date_Required,
(R.CALC_QTY) AS Total_Qty_Required,
(R.ISSUED_QTY) AS Qty_Issued,
R.STATUS
FROM dbo.REQUIREMENT R
WHERE (((R.PART_ID) ='[ENTER PART ID]') AND ((R.CALC_QTY)>[ISSUED_QTY]) AND ((R.SUBORD_WO_SUB_ID) Is Null) AND ((R.STATUS)<>'X' And (R.STATUS)<>'C') AND ((R.WORKORDER_TYPE)='W'))
UNION ALL
SELECT IBL.PART_ID,
'IBT' AS Demand_Type,
IBL.IBT_ID AS Order_ID,
CONVERT(varchar(5),IBL.LINE_NO) AS Lot_ID,
'' AS Split_ID,
'' AS Sub_ID,
0 AS Del_Line_No,
IB.FROM_WHSE_ID AS Demand_Warehouse,
CONVERT(CHAR(9),ISNULL(IB.DESIRED_SHIP_DATE,'12/31/2099'),101) AS Date_Required,
IBL.ORDER_QTY AS Total_Qty_Required,
IBL.SHIPPED_QTY AS Qty_Issued,
IB.STATUS
FROM dbo.IBT_LINE IBL INNER JOIN dbo.IBT IB ON IBL.IBT_ID = IB.ID
WHERE (((IBL.PART_ID) ='[ENTER PART ID]') AND ((IBL.ORDER_QTY)>IBL.SHIPPED_QTY) AND ((IB.STATUS)<>'X' And (IB.STATUS)<>'C') AND ((IBL.LINE_STATUS)='A'))
UNION ALL
SELECT PL.REQUIRED_PART_ID,
'PL' AS Demand_Type,
PL.PARENT_PART_ID AS Order_ID,
CONVERT(varchar(5),PL.PARENT_SEQ_NO) AS Lot_ID,
'' AS Split_ID,
'' AS Sub_ID,
PL.REQ_NO AS Del_Line_No,
PL.WAREHOUSE_ID AS Demand_Warehouse,
CONVERT(CHAR(9),ISNULL(PL.REQUIRED_DATE,'12/31/2099'),101) AS Date_Required,
PL.REQUIRED_QTY AS Total_Qty_Required,
0 AS Qty_Issued,
'' AS Status
FROM dbo.PLANNED_MATL_REQ PL
WHERE (((PL.REQUIRED_PART_ID)='[ENTER PART ID]'))
ORDER BY 1, 9;

Written by Richard on May 17, 2009

WHO *modified* this part and WHEN did they do it?

As I wrote in WHO *created* this part and WHEN did they do it?, Visual does not have a built in mechanism to determine when a new part is entered into the system neither does it capture who did it.  As you probably figured out, Visual also doesn't record who and when a user last modified a part.  One way to resolve this issue is through the use of a SQL TRIGGER.

We first need to create 2 additional fields to the PART table that will capture the time and user when parts are modified.  You can manually add these 2 fields or you can run the following script:


ALTER TABLE

PART
ADD
zMODIFIED_USER_ID VARCHAR(30) DEFAULT NULL,
zMODIFIED_DATE DATETIME DEFAULT NULL;


The next step is to create an UPDATE Trigger that will *fire* every time a part is modified, capturing the time and the user who did it.  Now because the Trigger will be executed on every instance that the PART table is modified, we need to pay extra attention because the PART table contains summary fields which do get modified/updated from other sources of Visual and are not necessarily a result from a user modifying the part through Part Maintenance.  Therefore, the Trigger will need to specify when to capture the time and user when a part gets modified.

For simplicity sake, my Trigger with capture the time and user whenever a parts' description, product code and/or the commodity code are changed


CREATE TRIGGER PART_MODIFIED ON PART FOR UPDATE AS
SET NOCOUNT ON
BEGIN
  UPDATE PART SET zMODIFIED_USER_ID=SYSTEM_USER, zMODIFIED_DATE=getdate()
  FROM inserted i, deleted d
  WHERE (ISNULL(i.DESCRIPTION,'')<>ISNULL(d.DESCRIPTION,'') or ISNULL(i.PRODUCT_CODE,'')<>ISNULL(d.PRODUCT_CODE,'') or ISNULL(i.COMMODITY_CODE,'')<>ISNULL(d.COMMODITY_CODE,'')) and i.ROWID=d.ROWID
END


ALWAYS TEST ON A COPY OF THE DATABASE BEFORE IMPLEMENTING ON PRODUCTION DATABASE


Written by Richard on May 7, 2009

Trigger to Not Allow Negative Inventory

As I mentioned in Issue Negative flag: A bit misleading, there are 2 scenarios where quantities will be allowed to go negative

  1. Parts that are set to "Auto-Issue", and
  2. where the transaction quantity is less than or equal to TOTAL WAREHOUSE QUANTITY AVAILABLE of the part (visual does not apply the rule to individual warehouse locations)

To REALLY prevent quantities from falling below zero, you will need to create a database trigger that will prevent ANY transactions that would result in negative inventory in a part location

Below is a SQL server Trigger that will do just that.


ALWAYS TEST ON A COPY OF THE DATABASE BEFORE IMPLEMENTING ON PRODUCTION DATABASE


-- SQL Server trigger
CREATE TRIGGER PART_LOCATION_NEGATIVEQTY ON PART_LOCATION FOR UPDATE AS
SET NOCOUNT ON
DECLARE
@nRcd INT,
@N_PART_ID       VARCHAR(30),
@N_WAREHOUSE_ID  VARCHAR(15),
@N_LOCATION_ID   VARCHAR(15),
@N_QTY DEC(16,4),
@ErrorMessage VARCHAR(200)
BEGIN
  DECLARE PART_LOC_UPD CURSOR LOCAL FOR
  SELECT ISNULL(I.QTY, 0), I.PART_ID, I.WAREHOUSE_ID, I.LOCATION_ID FROM INSERTED I, DELETED D WHERE I.ROWID = D.ROWID
  OPEN PART_LOC_UPD
  FETCH PART_LOC_UPD INTO
   @N_QTY, @N_PART_ID, @N_WAREHOUSE_ID, @N_LOCATION_ID
  IF @N_QTY < 0
  BEGIN
    SET @ErrorMessage = 'Trigger:PART_LOCATION_NEGATIVEQTY - The transaction would leave you with a negative quantity on hand for PART ID ' + @N_PART_ID + ' in ' + @N_WAREHOUSE_ID + '/' + @N_LOCATION_ID
    RAISERROR(@ErrorMessage, 16, -1, 0)  
  END
  DEALLOCATE PART_LOCATION_UPD
  IF @N_QTY<0 ROLLBACK TRANSACTION
END

WHO IS VISUALNUGGETS?

Richard Ferlatte
Results Driven VISUAL Specialist

VISUAL WEBINARS - Updated!

Check out our 50+ Training Webinars focusing on the various VISUAL modules and concepts. There's sure to be one to interest you!
24 Manufacturing Webinars
15 Quality Webinars
13 Financial Webinars

VISUAL EXTENSIONS

Extend the functionality of your VISUAL ERP system with these proven and supported VISUAL apps
Go Paperless App
Integration of the Go Paperless App solution and VISUAL results in Paperless ERP that provides a way for VISUAL users to increase efficiencies and thereby, the cash conversion cycle.
Inventory Management Tool
Provides you the ultimate freedom to quickly filter data, drill-down and track inventory/work in process discrepancies, easily reconcile actual inventory/wip to financial records for current or past periods.
eBoard – Financial Dashboard
A powerful Financial Dashboard allowing you to access live Financial data, drill down to supporting details and export the financial statements to Microsoft Excel in one click.
VE Mobile
The premier mobile extension to the VISUAL, adding flexibility and productivity improvements through the introduction of mobile scanning technology to the warehouse.
Logicity™ Automatic Invoicing
Automatically generate e-mailed or printed invoices out of VISUAL
EDI Integrator
A powerful alternative to the VMDI interface for EDI transactions (inbound and outbound).
Web Store Integration
e-Commerce solution integrating the web to your VISUAL system.