Transcript Document
Session: How to Utilize the Open Database Architecture of CribMaster Presenter: Phil Stenger
Copyright 2007 – WinWare, Inc.
What Can I Do At The Database Level??
• Triggers and Stored Procedures • Schedule SQL Server Agent Jobs • Use SQL Mail To Send Dynamic E-mails • Use CribMaster SQL Rul Files
Copyright 2007 – WinWare, Inc.
Why Do We Need To Do This Functionality?
• CribMaster Currently Does Not Store Data The Way You Need to See It or Report On It.
• I Have Unique Processes and or Rules That CribMaster Currently Does Not Allow Through Normal Application Functionality.
• I Need To Update CribMaster Based on Data From Another System.
Copyright 2007 – WinWare, Inc.
Scheduling and Storing Data With SQL Server Jobs
• Issue – CribMaster Only Stores Dead Stock, Over Stocked and Crib Value Analyzer Reports Based on AltVendor.Cost But My Site Uses the CribMaster Average Price Calculation.
• Issue – CribMaster Rolls Up data For Analyzer Reports Weekly and Monthly But I Need Monthly Only Data Only For Some Reports.
• Resolution – Using SQL Server Scheduled Jobs Roll Up Additional Data In The CribStatistics Table.
Copyright 2007 – WinWare, Inc.
Example SQL Scripts
• CribStatistics Type 100 = Weekly and monthly value by price roll-up • CribStatistics Type 200 = Weekly and monthly Dead Stock by price roll-up (180 days) • CribStatistics Type 300 = Weekly and monthly Over Stock by price roll-up (12 Months) • CribStatistics Type 201 = Weekly and monthly Dead Stock by price roll-up (90 days) • CribStatistics Type 101 = Monthly only value by price roll-up • CribStatistics Type 202= Monthly only Dead Stock by price roll-up (180 days) • CribStatistics Type 301 = Monthly only Over Stock by price roll-up (12 Months)
Copyright 2007 – WinWare, Inc.
Example SQL Scripts Continued,
INSERT INTO CribStatistics (StatisticsType, Crib, BinCount, StatisticsDate, StatisticsValue) SELECT 100, STATION.Crib, COUNT(*), GetDate() , SUM(STATION.Quantity * INVENTRY.Price) FROM STATION INNER JOIN INVENTRY ON STATION.Item = INVENTRY.ItemNumber
GROUP BY STATION.Crib
_____________________________________________________
INSERT INTO CribStatistics (StatisticsType, Crib, BinCount, StatisticsDate, StatisticsValue, UDFStatisticsMonthDate) SELECT 101, STATION.Crib, COUNT(*), Null , SUM(STATION.Quantity * INVENTRY.Price) , GetDate() FROM STATION INNER JOIN INVENTRY ON STATION.Item = INVENTRY.ItemNumber
GROUP BY STATION.Crib
Copyright 2007 – WinWare, Inc.
Preventing Updates In CribMaster Using Triggers
• Issue – A Client wanted To prevent Users From Updating A Closed PO at the PODetail Level. I.E. Changing the Qty, Promised Or Required Dates… • Resolution – We Placed a Trigger On the PODetail Table Preventing Updates To Detail When a PO Is in Closed Status.
Copyright 2007 – WinWare, Inc.
The Trigger and Results From CribMaster
CREATE TRIGGER TRG_PODETAIL_PREVENTUPDATE ON PODETAIL FOR UPDATE AS IF EXISTS (select 1 from inserted a , PO b where a.TYPE = 0 and a.PONUMBER = b.PONUMBER
) and b.POSTATUSNO = 1 BEGIN ROLLBACK RAISERROR ('YOU CANNOT UPDATE A CLOSED PO',16,1) END
Copyright 2007 – WinWare, Inc.
Using A Trigger On One SQL Server Database to Update Another SQL Server Database??
• Issue – Work Orders Are Created In Another Application That Uses A SQL Server Database And I want To Issue To Those Work Orders (User Defined Field) In CribMaster.
• Resolution – Create A Trigger On the Work Order Table Of the Other Application To Update The CribMaster User1 Table
Copyright 2007 – WinWare, Inc.
An Example Of a Solution Using Oracle
• Issue The CribMaster Calculated Order Point and Order Qty Are Not suited To My Business • Resolution – Create a Trigger That Updates the CribMaster Over Ride Order Point and Over Ride Order Qty based on my Calculations
Copyright 2007 – WinWare, Inc.
An Example Of a Solution Using Oracle,
Don’t Try To Read This 8 pt Type.
Con’t
CREATE OR REPLACE TRIGGER TRG_STATION_CALCORDERQTYMIN BEFORE INSERT OR UPDATE ON STATION REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE hold_UDFVENDING CRIB.UDFVENDING%TYPE; BEGIN -- See if non-vending crib. All cribs assumed non-vending unless -- explicitly flagged as vending (CRIB.UDFVENDING='YES') BEGIN SELECT NVL(UPPER(UDFVENDING), 'NO') INTO hold_UDFVENDING FROM CRIB WHERE CRIB = :NEW.CRIB; EXCEPTION WHEN NO_DATA_FOUND THEN hold_UDFVENDING := 'NO'; END; IF hold_UDFVENDING <> 'YES' THEN -- Fire for non-vending cribs only IF NVL(:NEW.MONTHLYUSAGE, 0) >= 0 THEN :NEW.OVERRIDEORDERPOINT := CEIL(NVL(:NEW.MONTHLYUSAGE, 0) * 1.5); END IF;
I Needed A Filler Here
IF NVL(:NEW.BINQUANTITY, 0) >= 0 THEN :NEW.OVERRIDEORDERQUANTITY := NVL(:NEW.MONTHLYUSAGE, 0) * 3 - NVL(:NEW.BINQUANTITY, 0); IF (:NEW.OVERRIDEORDERQUANTITY < 0) THEN :NEW.OVERRIDEORDERQUANTITY := 0; END IF; END IF; END IF; EXCEPTION WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; / END;
Copyright 2007 – WinWare, Inc.
What Else Can I Do At The Database Level??
• Issue – I want To make Sure the Cost Field Is Always Populated When I Create A New Item • Resolution – Since CribMaster Has No Option To Enforce This Go To The Database And Set That Field To Not Allow Nulls » Note: All Items With A Supplier Must Have A Cost Before You Can Set the Field To No Nulls
Copyright 2007 – WinWare, Inc.
Dynamically Sending E-mail Using SQL Mail
• Issue – Client Wants To send an Email To Users or Supervisors That have Over Due Gauges Checked Out • Resolution – Use A Scheduled Stored Procedure Job With SQL Mail To Auto Generate and E mail The Notification
Copyright 2007 – WinWare, Inc.
Don’t I Need Exchange Server To Send SQL Mail??
The Answer Is No…..You use a POP3 Account and SMTP E-Mail For SQL Mail Advantages to using POP3 and SMTP for SQL MAIL and SQL Agent Mail • SMTP allows SQL Mail and SQL Agent mail to work with most firewall rules since SMTP protocols normally are not blocked.
• Using POP3 and SMTP for SQL Mail and SQL Agent mail also provides all the same functionality of using Exchange. • Therefore this means no SQL Server mail functionality will be lost using POP3 and SMTP as a mail solution for SQL Server. Note: Web Site On How To Set Up SQL Mail Using a POP Account http://www.databasejournal.com/features/mssql/article.php/3345091
Copyright 2007 – WinWare, Inc.
And The Results….
• This Stored Procedure Is Available Upon Request
Copyright 2007 – WinWare, Inc.
What Is an SQL Rul File?
• Staring With CribMaster 7.0 And Newer CribMaster Import Files Can Invoke SQL Scripts (Note: This Functionality Requires A Registry Update For Security reasons) Examples • rulSql - will execute the specified SQL immediately in the rul file header (any rul file) rulSql Delete From User1
Copyright 2007 – WinWare, Inc.
What Is an SQL Rul File, Con’t?
• rulSqlFile - will execute the specified SQL File immediately in the rul file header (any rul file) rulSqlFile .\SqlFileName.Sql
• rulSqlRules will execute the specified SQL statement for each row of data - Only E type ruls will execute
Copyright 2007 – WinWare, Inc.
An Example Of When You Can Use The
rulSqlRules
RulSQLRules?
< >>PODetail START Auto,E Update PODetail set UDFSalesOrder=%s Where OriginalSeqNo=%s,MyPONumber,CMSEQ Auto,E Update PODetail set Cost=%s Where OriginalSeqNo=%s, MyNewPrice,CMSEQ Auto,E Update PODetail set PROMISEDDATE={d '%s'} Where OriginalSeqNo=%s, MYEXPECTEDDATE,CMSEQ Copyright 2007 – WinWare, Inc. A Couple Of New Features: • rulSqlAtEnd - Delete From JUNK // SQL to Execute After all data is loaded You can have multiple of these- valid for any rul file • rulSqlFileAtEnd - .\SqlFileName.Sql // SQL File to Execute at the End - Only One File Allowed - The File Name Copyright 2007 – WinWare, Inc. • If The Trigger Is Not Complex and Can be Written By A Support Tech No Fee is Charged • Stored Procedures Are Generally More Complex And Might Invoke A Programming Fee We Aren’t Always looking For The Almighty Your Success is Also a Success For WinWare… Copyright 2007 – WinWare, Inc. • Give Me Examples Of What You Did At The Database Level And Why… • Do You Have An Issue With CribMaster Functionality That We Can Discuss? Copyright 2007 – WinWare, Inc.What Is an SQL Rul File, Con’t?
Does WinWare Charge For These Stored Procedures And Triggers
Open Discussion