White Paper

Utilizing Event Triggers For Managing Information Changes.

Tom Brown, Biosite Diagnostics, Inc.

Biosite Diagnostics uses the eNVy Warehouse Suite data warehouse extensively as an integral source for MANMAN information. Biosites information needs span from general reporting, Excel spreadsheets to complex OLAP datamarts and general content via Microsoft Active Server Pages.  Our initial implementation configuration included a reporting system which satisfies various levels of information reporting across MANMAN/Manufacturing, MANMAN/Omar and MANMAN/Tracker.

As we mature with the data warehouse and Microsoft SQL and ASP technology we find our information environment to be rich in opportunity. One such opportunity entailed our need to audit transactional records (in near real-time) for accuracy. The MANMAN system, although robust, is unable to capture certain business events with notification.

Problem/Solution.

When a part or product is set up in MANMAN, accounting needs to be notified so that they can review specific fields to ensure the recording of the transaction is correct and MANMAN reporting is consistent. Many times, transactions are entered with bad costs or no costs at all, wrong primary account numbers and wrong GL product types. To ensure these transactions where entered correctly, we implemented an event notification system for these records. Today, Biosite's transaction accuracy has significantly improved and our business processes are more robust. 

We added triggers to the Manufacturing PRTREC table and OMAR MA_PRODREC table, upon the insertion of a new record accounting users are notified via e-mail, that the part or product was added.

Code.

MANMAN/Manufacturing PRTREC TABLE
CREATE trigger t_insert_part on prtrec 
FOR INSERT
AS 
DECLARE   @part_number varchar(18),@part_desc varchar(30),@sub_string varchar(100) 
SELECT  @part_number =  RTRIM(prtno),@part_desc = RTRIM(prtdesc) 
FROM INSERTED
 
SET @sub_string='A new Manufacturing part has been added: '+@part_number + " " + @part_desc
execute master.dbo.xp_sendmail @recipients='user1@anycompany.com;user2@anycompany.com',
        @copy_recipients='user3@anycompany',
        @subject='New Manufacturing Part',
        @message=@sub_string,
        @attach_results='false',
        @width=1250,
        @no_output='true'
 
MANMAN/OMAR MA_PRODREC TABLE
CREATE trigger t_insert_product on ma_prodrec FOR INSERT
AS
DECLARE  @prod_number varchar(18) , @prod_desc varchar(30) ,@sub_string varchar(100)
SELECT  @prod_number =  RTRIM(ma_prodno), @prod_desc = RTRIM(ma_proddesc)
FROM INSERTED
SET @sub_string='A new OMAR product has been added: '+@prod_number + " " + @prod_desc
execute master.dbo.xp_sendmail @recipients='user1@anycompany.com;user2@anycompany.com',
        @copy_recipients='user3@anycompany',
        @subject='New OMAR Product',
        @message=@sub_string,
        @attach_results='false',
        @width=1250,
        @no_output='true'
 

Summary.

The data warehouse platform provides a rich information base and an infrastructure for building flexible reporting, and event driven applications. Biosite has the power to generate event notifications in virtually any record in MANMAN using business rules which meet our requirements. There are plans to implement trigger events for transactions to notify key users for Manman/Manufacturing shortages, and other inventory transactions. 

Tom Brown is a Programmer/Analyst with Information Services at Biosite Diagnostics, Inc. .

The eNVy Systems Warehouse Suite has made it easier for us to bring MANMAN to the desktop for our users worldwide. eNVy Systems Warehouse Suite is available on OpenVMS for applications executing Oracle Codasyl DBMS. For information: http://envysys.com or voice: (408) 363-8896.