1 / 21

Session: How to Utilize the Open Database Architecture of CribMaster

Session: How to Utilize the Open Database Architecture of CribMaster. Presenter:. Phil Stenger. Triggers and Stored Procedures Schedule SQL Server Agent Jobs Use SQL Mail To Send Dynamic E-mails Use CribMaster SQL Rul Files. What Can I Do At The Database Level??.

kaia
Download Presentation

Session: How to Utilize the Open Database Architecture of CribMaster

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session:How to Utilize the Open Database Architecture of CribMaster Presenter: Phil Stenger

  2. Triggers and Stored Procedures Schedule SQL Server Agent Jobs Use SQL Mail To Send Dynamic E-mails Use CribMaster SQL Rul Files What Can I Do At The Database Level??

  3. 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. Why Do We Need To Do This Functionality?

  4. 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. Scheduling and Storing Data With SQL Server Jobs

  5. 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) Example SQL Scripts

  6. 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 Example SQL Scripts Continued, _____________________________________________________

  7. 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. Preventing Updates In CribMaster Using Triggers

  8. 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 The Trigger and Results From CribMaster

  9. 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 Using A Trigger On One SQL Server Database to Update Another SQL Server Database??

  10. 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 An Example Of a Solution Using Oracle

  11. 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; 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; / An Example Of a Solution Using Oracle, Con’t Don’t Try To Read This 8 pt Type. I Needed A Filler Here

  12. 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 What Else Can I Do At The Database Level??

  13. 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 Dynamically Sending E-mail Using SQL Mail

  14. 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 Don’t I Need Exchange Server To Send SQL Mail??

  15. This Stored Procedure Is Available Upon Request And The Results….

  16. 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 What Is an SQL Rul File?

  17. 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 What Is an SQL Rul File, Con’t?

  18. rulSqlRules <<YourFileName.csv >>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 An Example Of When You Can Use The RulSQLRules?

  19. 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 What Is an SQL Rul File, Con’t?

  20. 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… Does WinWare Charge For These Stored Procedures And Triggers

  21. 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? Open Discussion

More Related