Lee Harper, Biosite Diagnostics, Inc.
Biosite Diagnostics, (http://www.Biosite.com) a biotech company located in San Diego, one of the fastest growing biotech centers in the U.S., has been using MANMAN Classic on the OpenVMS platform since 1992.
The chief complaint about the MANMAN system from users has been the lack of easy ad-hoc reporting, especially in the sales and marketing area.
As well as answering many of the daily ad-hoc needs for analysis in the cost accounting, accounting, and manufacturing areas, eNVy’s Data Warehouse solution has helped to solve one of the most nagging problems the Biosite I.S. dept. has had for years: How do we analyze "Direct" and "Distributor" sales together?.
Biosite receives sales invoice information from its primary Distributor (via e-mail), and also has direct sales information contained in the MANMAN DBMS database. An Import program (using Visual Basic and Interactive SQL) reads the Distributor’s data and imports it into a Customer/Invoice SQL Server database that was jointly designed by Biosite’s Sales and I.S. departments. Using a similar Import program, MANMAN data is taken from the eNVy MANMAN Data Warehouse (See diagrams and SQL SELECT statements below), and inserted into the same Customer/Invoice SQL Server database, so that now the Sales and Marketing departments can view reports on sales regardless of whether it was sold direct or through our distributor.
The eNVy MANMAN Data Warehouse is proving more and more valuable each day for Biosite, for analysis, reporting – even for such things as a Customer and Vendor List for our Year 2000 project.
Mapping of
Data Warehouse (MANDB120 OMAR database) Fields to CCT’s DISTRIBUTOR_DATA Table
Translation is done in two selections: one for invoice information, and one for
credit memo information.
Data for each selection comes from the same record, except where two records are shown.
| Dstbtr_data table column name |
Data type | Length | MANMAN DW table name | MANMAN DW field name | MANMAN data comments |
| Dstbtr_cust_number | Cust_number | 9 | N/A | ||
| Biosite_cust_number | Cust_number | 9 | MA_SHPREC | MA_SHPNO | |
| Dstbtr_cust_name | Cust_name | 50 | MA_SHPREC | MA_SHPNAME | |
| Dstbtr_cust_address | Address | 50 | MA_SHPREC | MA_SHPADDR2 + MA_SHPADDR3 |
|
| Dstbtr_cust_city | City | 16 | MA_SHPREC | MA_SHPCITY | |
| Dstbtr_cust_state | State | 2 | MA_SHPREC | MA_SHPSTATE | |
| Dstbtr_cust_zip | Zip | 10 | MA_SHPREC | MA_SHPZIP | |
| Invoice_number | Invoice_number | 7 | MA_INHREC MA_CMHREC |
1ST letter of MA_SHPNO + MA_INHNO 1ST letter of MA_SHPNO + MA_CMHNO |
Truncate |
| Invoice_date | Datetime | 8 | MA_INHREC MA_CMHREC |
MA_INHDAT MA_CMHDAT |
Integer data type Integer data type |
| Business_year_month | Char | 5 | Extract from INHDAT/CMHDAT |
||
| Territory_number | Char | 8 | N/A | ||
| Region | Char | 4 | MA_SHPREC | MA_SHPSA | Current sales agent region (not when it was invoiced) |
| Product_number | Product_number | 12 | MA_SODREC MA_CMDREC |
MA_PRODNO MA_PRODNO |
|
| Unit_of_measure | Char | 2 | N/A | ||
| Quantity_sold | Int | 4 | MA_INDREC MA_CMDREC |
MA_INDQTY IF MA_CMDNQTY – MA_CMDOQTY = 0 THEN MA_CMDNQTY ELSE MA_CMDNQTY – MA_CMDOQTY |
|
| Total_cost | Smallmoney | 4 | MA_INDREC
MA_CMDREC |
IF MA_INDADJ = -1 THEN MA_INDPRTABDISPCT*(1- MA_INDDISPCT) ELSE MA_INDADJ IF PRICE = 0 |
Total extended unit price.
PRICE = (IF CMDNADJ = -1 THEN MA_CMDPRTABDISPCT*(1- MA_CMDDISPCT) ELSE MA_CMDNADJ) – (IF MA_CMDOADJ = -1 THEN MA_CMDPRTABDISPCT*(1- MA_CMDDISPCT) ELSE MA_CMDOADJ) |
| National_account | Int | 4 | N/A | ||
| Cust_group | MA_SHPREC MA_BILREC |
If MA_SHPSIC<>null THEN MA_SHPSIC Else MA_BILCT |
Views for the Mapping of
Data Warehouse (MANDB120 OMAR database) Fields to CCT’s DISTRIBUTOR_DATA Table
Here are the two views that are needed, one for each of the translation passes.
SELECT [various fields] FROM ma_shprec, ma_inhrec, ma_sodrec, ma_indrec, ma_bilrec, ma_sohrec WHERE ma_indrec.ma_sohno = ma_sodrec.ma_sohno AND ma_indrec.ma_sodlin = ma_sodrec.ma_sodlin AND ma_indrec.ma_inhno = ma_inhrec.ma_inhno AND ma_inhrec.ma_sohno = ma_sohrec.ma_sohno AND ma_sohrec.ma_shpno = ma_shprec.ma_shpno AND ma_shprec.ma_bilno = ma_bilrec.ma_bilno
SELECT [various fields] FROM ma_shprec, ma_inhrec, ma_cmdrec, ma_cmhrec, ma_bilrec, ma_sohrec WHERE ma_cmhrec.ma_cmhno = ma_cmdrec.ma_cmhno AND ma_cmhrec.ma_inhno = ma_inhrec.ma_inhno AND ma_inhrec.ma_sohno = ma_sohrec.ma_sohno AND ma_sohrec.ma_shpno = ma_shprec.ma_shpno AND ma_shprec.ma_bilno = ma_bilrec.ma_bilno
eNVy Systems is the manufacturer of the eNVy Data Warehouse Plus Suite for ORACLE CODASYL DBMS, installed and operational at companies ranging from Fortune 100 to Fortune 1000, a proven technology for quick and reliable deployment of CA-MANMAN data to Microsoft SQL Server, Oracle 6/7/8, SYBASE, CA-Ingres and other RDBMS. eNVy also provides consultation ranging from system and data integration to CODASYL DBMS administration, de-corruption and general OpenVMS systems management.
© 1998, eNVy Systems. All rights reserved. All brand/trade and/or product names are the property of their respective owners.