VWEASUserQuotaUsage.SQL: EAS usage view

Author: Robert Lawson
Environment: Windows Server, Microsoft SQL Server, EAS
Description: This view simplifies access to EAS sytem tables and provides key to inhouse Onecard system and match to Active Directory sAMAccount name. Used in conjunction with vbscripts to report over-quota and weekly usage. See EASUsageAudit.vbs and EASNearQuotaEmail.vbs .
Code:

/*
File   : VWEASUserQuotaUsage.SQL
Update : 08-July-2006/Robert Lawson
Purpose: Usage and quota for EAS users (only users with usage)
Notes  :
  - Size and Quota are expressed in MB, Quota = -1, is unlimited
  - Works with VWEASUserTopMsgs

SELECT * FROM VWEASUserQuotaUsage
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VWEASUserQuotaUsage]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VWEASUserQuotaUsage]

CREATE VIEW dbo.VWEASUserQuotaUsage AS
SELECT
US.USERID,
RTRIM(US.USERNAME) AS UserName,
US.EASQUOTAOPTION,
QuotaSize = CASE WHEN
  US.EASQUOTAOPTION = 1 THEN GP.EASQUOTA
  ELSE US.EASQUOTA
END,
GP.GROUPNAME,
QuotaType = CASE WHEN
  US.EASQUOTAOPTION = 1 THEN 'Group'
  ELSE 'User'
END,
RTRIM(RIGHT(NT.NTACCOUNT,(LEN(NT.NTACCOUNT) - CHARINDEX('\',NT.NTACCOUNT)))) AS sAMAccountName,
RTRIM(RIGHT(NT.NTACCOUNT,(LEN(NT.NTACCOUNT) - CHARINDEX('\',NT.NTACCOUNT)))) + '@uni.edu' AS Email,
COUNT(*) AS MsgCount,
SUM(CAST(PR.MSGSIZE AS BIGINT))/1048576 AS MsgSize
FROM  PROFILE PR, REFER RF, GROUPS GP, NTACCOUNT NT, USERS US
WHERE
US.EASSTATUS = 0 AND
(US.GROUPID > 0 AND US.GROUPID <> 2) AND
NT.USERID = US.USERID AND
GP.GROUPID = US.GROUPID AND
RF.USERID = US.USERID AND
RF.FOLDERID > 0 AND
RF.FOLDERID = (SELECT MAX(R2.FOLDERID) FROM REFER R2 WHERE
                R2.USERID = RF.USERID AND R2.MSGID = RF.MSGID AND R2.FOLDERID > 0) AND
PR.MSGID = RF.MSGID
GROUP BY US.USERID, US.USERNAME, US.EASQUOTAOPTION, GP.EASQUOTA, US.EASQUOTA, NT.NTACCOUNT, GP.GROUPNAME

VWEASUserTopMsgs.sql: EAS top user attachments

Author: Robert Lawson
Environment: Windows Server, Microsoft SQL Server, EAS
Description: This view simplifies access to EAS sytem tables and list of largest email attachments. Used in conjunction with vbscripts to report over-quota and weekly usage. See EASUsageAudit.vbs .
Code:

/*
File   : VWEASUserTopMsgs.sql
Update : 08-July-2006/Robert Lawson
Purpose: Get biggest emails for a given user
Notes  :
  - Size is in MB to be consistent with Quota and VWEASUserQuotaUsage
  - Works with VWEASUserQuotaUsage (gets the user information)

SELECT TOP 20 * FROM VWEASUserTopMsgs WHERE USERID = 9 ORDER BY MsgSize DESC
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VWEASUserTopMsgs]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VWEASUserTopMsgs]

CREATE VIEW dbo.VWEASUserTopMsgs AS
SELECT
RF.MSGID,
RF.USERID,
FL.FOLDERNAME,
PR.SUBJECT,
PR.FROMFLD,
PR.MSGDATE,
PR.MSGSIZE/1048576 MsgSize
FROM FOLDER FL, PROFILE PR, REFER RF
WHERE
RF.FOLDERID > 0 AND
RF.FOLDERID = (SELECT MAX(R2.FOLDERID) FROM REFER R2 WHERE
                R2.USERID = RF.USERID AND R2.MSGID = RF.MSGID AND R2.FOLDERID > 0) AND
PR.MSGID = RF.MSGID AND
FL.USERID = RF.USERID AND
FL.FOLDERID = RF.FOLDERID 

upd_indx.sql: update table indexes

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This is sample table index script used first in test instance, then in production, from JIT ERP system. This script matched indexes to data use by company.
Code Listing:

/* program: upd_indx.sql
   date   : 08/13/96
   author : Robert Lawson
   purpose: Mass update of indexes
*/
set termout on
set echo on
-- set heading off
-- set linesize 132
-- set feedback off
-- set pagesize 60
/*
-- /appl/jit/src/CUST302/ddl/table_q/alt_quote_model_dtl.sql
-- redoes this index with line_no
DROP INDEX QUOTE_MODEL_DETAIL_INDX1;
CREATE INDEX QUOTE_MODEL_DETAIL_INDX1
 ON QUOTE_MODEL_DETAIL (QUOTE_NO, OEDETL_LINE_NO, plant)
 tablespace JITINDX
 storage ( initial     5M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );
*/
-- new index
DROP INDEX QUOTE_MODEL_DETAIL_IDX3;
CREATE INDEX QUOTE_MODEL_DETAIL_IDX3
 ON QUOTE_MODEL_DETAIL (parent_no, plant)
 tablespace JITINDX
 storage ( initial      3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_b/alt_bom_log.sql (new)
-- only index! bmli
DROP INDEX BOM_LOG_IDX1;
CREATE INDEX BOM_LOG_IDX1
 ON BILL_OF_MATERIAL_LOG (parent_no, parent_plant)
 tablespace JITINDX
 storage ( initial      3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_b/alt_blownup_bill.sql (new)
-- only index!
DROP INDEX BLOWNUP_BILL_IDX1;
CREATE INDEX BLOWNUP_BILL_IDX1
 ON BLOWNUP_BILL (plant , order_no, component_no)
 tablespace JITINDX
 storage ( initial        3M   next       1M
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_line_comm.sql (new)
-- only index!
DROP INDEX PO_LINE_COMM_IDX1;
CREATE INDEX PO_LINE_COMM_IDX1
 ON PO_LINE_COMM (plant , po_no, line_no, comment_seq)
 tablespace JITINDX
 storage ( initial       1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_blanket.sql (new)
-- added line to index
DROP INDEX PO_BLANKET_IDX;
CREATE INDEX PO_BLANKET_IDX
 ON PO_BLANKET (plant , po_no, line_no)
 tablespace JITINDX
 storage ( initial       1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_o/alter_oesummary.sql
-- added index for customer
DROP INDEX OESUMMARY_IDX1;
CREATE INDEX OESUMMARY_IDX1
 ON OESUMMARY (cust_no, order_no, order_suffix)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_o/alt_oe_entry_register.sql (new)
-- added index for customer
DROP INDEX OE_ENTRY_REG_IDX1;
CREATE INDEX OE_ENTRY_REG_IDX1
 ON OE_ENTRY_REGISTER (order_no, order_suffix, plant)
 tablespace JITINDX
 storage ( initial        1M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_r/alt_requisition.sql (new)
-- added index for vendor
DROP INDEX REQUISITION_IDX4;
CREATE INDEX REQUISITION_IDX4
 ON REQUISITION (plant, vendno, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for vendor
DROP INDEX REQUISITION_IDX5;
CREATE INDEX REQUISITION_IDX5
 ON REQUISITION (plant, requested_by, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for buyer
DROP INDEX REQUISITION_IDX6;
CREATE INDEX REQUISITION_IDX6
 ON REQUISITION (plant, buyer_code, req_no, req_line_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_r/alt_req_header.sql (new)

-- added index for requestor
DROP INDEX REQ_HEADER_IDX2;
CREATE INDEX REQ_HEADER_IDX2
 ON REQ_HEADER (plant, requested_by, req_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_p/alt_po_header.sql (new)
-- added index for vendor
DROP INDEX PO_HEADER_IDX2;
CREATE INDEX PO_HEADER_IDX2
 ON PO_HEADER (plant, vendno, req_no)
 tablespace JITINDX
 storage ( initial       2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index for requestor
DROP INDEX PO_HEADER_IDX3;
CREATE INDEX PO_HEADER_IDX3
 ON PO_HEADER (plant, buyer_code, req_no)
 tablespace JITINDX
 storage ( initial      2M   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_c/alt_custcntrcts.sql (new)
-- added customer
DROP INDEX CUSTCNTRCTS_IDX1;
CREATE INDEX CUSTCNTRCTS_IDX1
 ON CUSTCNTRCTS (cust_no, item_no, plant)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- /appl/jit/src/CUST302/ddl/table_a/alt_ar_cia_invno.sql (new)
-- added index
DROP INDEX AR_CIA_INVNO_IDX1;
CREATE INDEX AR_CIA_INVNO_IDX1
 ON AR_CIA_INVNO (org_unit_id, order_no)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

-- added index
DROP INDEX AR_CIA_INVNO_IDX2;
CREATE INDEX AR_CIA_INVNO_IDX2
 ON AR_CIA_INVNO (org_unit_id, cia_invno)
 tablespace JITINDX
 storage ( initial      500K   next       500K
           minextents     1 maxextents   121
           pctincrease    0 );

bkd_tab.sql: create table script

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: This is sample table creation script used first in test instance, then in production, from JIT ERP system.
Code Listing:

rem $Author: roblaw $
rem $Locker:  $
rem $Date: 1996/07/07 17:38:06 $
rem $Source: /appl/jit/RCS/ddl/table_b/cre_booking_detail.sql
rem $Revision: 1.1 $           [bkd_tab.sql]
/*
===============================================================
Table
===============================================================
-schedule_no v2(6), hold_cd v2(1)

*/

drop table booking_detail;
CREATE TABLE booking_detail
(
plant            VARCHAR2(2),
order_no         VARCHAR2(10),
order_suffix     NUMERIC(2),
line_no          NUMERIC(3),
sys_date  DATE,
fiscal           VARCHAR2(4), -- YYMM
oracle_user_id   VARCHAR2(30),
reason           VARCHAR2(3), -- INS, DEL, UPD
item_no          VARCHAR2(50),
cust_no          VARCHAR2(10),
ext_price_d      NUMERIC(15,5), -- net dollar change
order_qty_d      NUMERIC(15,5),
unit_price_d     NUMERIC(15,5),
unit_cost_d      NUMERIC(15,5),
freight_d        NUMERIC(15,5),
tax_d            NUMERIC(15,5),
finish_1_o       VARCHAR2(5),
finish_1_n       VARCHAR2(5),
strap_1_o        VARCHAR2(5),
strap_1_n        VARCHAR2(5),
fabric_1_o       VARCHAR2(5),
fabric_1_n       VARCHAR2(5),
fabric_2_o       VARCHAR2(5),
fabric_2_n       VARCHAR2(5),
cush_style_o     VARCHAR2(5),
cush_style_n     VARCHAR2(5),
ship_zip_code_o  VARCHAR2(12),
ship_zip_code_n  VARCHAR2(12),
not_after_date_o DATE,
not_after_date_n DATE,

)
 tablespace JITDATA
 storage ( initial    1m   next        100k
           minextents     1 maxextents   121
           pctincrease    0 );
/*
===============================================================
Index
===============================================================
*/
DROP INDEX booking_detail _idx1;
CREATE INDEX booking_detail _idx1
 ON booking_detail (ORDER_NO, ORDER_SUFFIX, plant)
 tablespace JITINDX
 storage ( initial      500K   next       50K
           minextents     1 maxextents   121
           pctincrease    0 );

DROP INDEX booking_detail _idx2;
CREATE  INDEX booking_detail _idx2
 ON BACKLOG_DEMAND (ITEM_NO, PLANT)
 tablespace JITINDX
 storage ( initial      500K   next       50K
           minextents     1 maxextents   121
           pctincrease    0 );

DROP INDEX booking_detail _idx3;
CREATE  INDEX booking_detail _idx2
 ON BACKLOG_DEMAND (cust_no)
 tablespace JITINDX
 storage ( initial      500K   next       50K
           minextents     1 maxextents   121
           pctincrease    0 );

/*
===============================================================
Security
===============================================================
*/
drop public synonym booking_detail;
create public synonym booking_detail for JIT. booking_detail;

grant all on booking_detail to public;
-- grant all on booking_detail to jituser;
-- grant select on booking_detail to readjit;
/*
===============================================================
Comments
===============================================================
*/
COMMNET ON TABLE booking_detail IS
' The booking detail captures all significant changes to booked
orders.  The user id, date and reason for change are captured;
along with quantity, price and cost.  Other succulent nuggets are
captured to help explain many of our corporate mystries.
 The booking_detail is populated from these triggers:
 oedetl_trg, oehead_trg and oesummary_trg.'

COMMENT ON COLUMN booking_detail.plant IS
'oedetl.plant.  If plant code change, then there will be a
separate entry for each plant.';
COMMENT ON COLUMN booking_detailorder_no IS
'oedetl.order_no.';
COMMENT ON COLUMN booking_detail.order_suffix IS
'oedetl.order_suffix.';
COMMENT ON COLUMN booking_detail.line_no IS
'oedetl.line_no. 0=if header change, oehead or oesummary.';
COMMENT ON COLUMN booking_detail.sys_date IS
'Date change was made.';
COMMENT ON COLUMN booking_detail.fiscal IS
'Fiscal period change was made.';
COMMENT ON COLUMN booking_detail.oracle_user_id IS
'User who made change.';
COMMENT ON COLUMN booking_detail.reason IS
'Reason change happened: INSert, DELete, UPDate.';
COMMENT ON COLUMN booking_detail.item_no IS
'oedetl.item_no.';
COMMENT ON COLUMN booking_detail.cust_no IS
'oehead.cust_no.';
COMMENT ON COLUMN booking_detail.ext_price_d IS
'Net dollar change = order_qty_d * unit_price_d.';
COMMENT ON COLUMN booking_detail.order_qty_d IS
'Net quantity change = new.order_qty - old.order_qty.';
COMMENT ON COLUMN booking_detail.unit_price_d IS
'Net unit price change = new.unit_price - old.unit_price';
COMMENT ON COLUMN booking_detail.freight_d IS
'Net freight dollar change.  oesummary';
COMMENT ON COLUMN booking_detail.tax_d IS
'Net tax dollar change.  oesummary.';
COMMENT ON COLUMN booking_detail.finish_1_o IS
'Old oedetl.finish_1 value. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.finish_1_n IS
'New oedetl.finish_1 value. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.strap_1_o IS
'Old value of oedetl.strap_1.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.strap_1_n IS
'New value of oedetl.strap_1. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.fabric_1_o IS
'Old value of oedetl.fatric_1.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.fabric_1_n IS
'New value of oedetl.fabric_1. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.fabric_2_o IS
'Old value of oedetl.fabric_2.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.fabric_2_n IS
'New value of oedetl.fabric_2. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.cush_style_o IS
'Old value of oedetl.cush_style. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.cush_style_n IS
'New value of oedetl.cush_style. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.ship_zip_code_o IS
'Old value of oehead.ship_zip_code.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.ship_zip_code_n IS
'New value of oehead.ship_zip_code. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.not_after_date_o IS
'Old value of oehead.not_after_date.  NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.not_after_date_n IS
'New value of oehead.not_after_date. NULL if reason is DELete.';

db_prof.sql: report database triggers

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: Report database triggers from JIT ERP system.
Code Listing:

/*
program: db_prof.sql
purpose: profile of database
date   : 06-30-96 (a blue moon today)
author : robert lawson
changes:

*/
set wrap off
set linesize 300
set pagesize 50000
set longc 10000
set echo on
set termout on
-- set pause on

/*
-- Triggers
- all_triggers: TRIGGER_BODY, TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE
- all_trigger_cols:
*/
SELECT
-- substr(ao.owner,1,12) owner,
substr(ao.object_name,1,30) trigger_name,
ao.created,
ao.status,
substr(ag.table_name,1,20) table_name
FROM all_triggers ag, all_objects ao
WHERE
ao.object_type = 'TRIGGER' and
ao.owner <> 'SYS' and
ag.trigger_name = ao.object_name
ORDER BY 1;

/*
- Procedures, not in packages (odd balls)

*/
SELECT
substr(ao.owner,1,12) owner,
substr(ao.object_name,1,30) name,
ao.created,
ao.status
FROM all_objects ao
WHERE
ao.object_type = 'PROCEDURE'
-- ao.owner <> 'SYS'
ORDER BY 1, 2;

tab_abh.sql: alter table and comments

Author: Robert Lawson
Environment: Oracle database, HPUX
Description: Sample SQL script to modify database table and to add database documentation. This script would be used on test instance, then ultimately be used to make changes in production.
Code Listing:

rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1996/06/26 00:00:47 $
rem $Source: /appl/jit/RCS/ddl/table_b/alt_backlog_header.sql [bld_alt.sql]
rem $Revision: 1.1 $
set echo on
/*
06/25/96 roblaw schedule_build_header.release_flag to see if in process
*/
alter table backlog_header add (
release_flag    varchar2(1),
GROSS_ORD_VALUE NUMBER(13,2)
);

COMMENT ON COLUMN BACKLOG_HEADER.RELEASE_FLAG IS
'Is SO scheduled and released to production?
 null = not scheduled and thus not released
 N    = scheduled, but not released
 Y    = scheduled and released';

/*
01/02/97 roblaw: new columns for final assembly scheduling
*/
alter table backlog_header add (
week_before    NUMERIC(2),
week_order     NUMERIC(3,1),
item_excep     VARCHAR2(1),
finish_qty     NUMERIC(15,5),
finish_multi   VARCHAR2(5),
finish_excep   VARCHAR2(1),
strap_qty      NUMERIC(15,5),
strap_multi    VARCHAR2(5),
strap_excep    VARCHAR2(1),
fabric_qty     NUMERIC(15,5),
fabric_multi   VARCHAR2(5),
fabric_excep   VARCHAR2(1));

COMMENT ON COLUMN BACKLOG_HEADER.week_before IS
'Week number using not_before_date, sunday logic same as week_num.';

COMMENT ON COLUMN BACKLOG_HEADER.week_order IS
'Number of weeks old from order_date, value range 0-100.';

COMMENT ON COLUMN BACKLOG_HEADER.item_excep IS
'Value based on sum of backlog_detail.item_excep
  = * if any value is *
  = null, if all values are null';

COMMENT ON COLUMN BACKLOG_HEADER.finish_qty IS
'Sum of backlog_detail.finish_qty';

COMMENT ON COLUMN BACKLOG_HEADER.finish_multi IS
'Value is set based on all SO line items, where backlog_detail.finish_1 has
  = null, if all values are null
  = color, if only one color
  = MULTI, if more than one color';

COMMENT ON COLUMN BACKLOG_HEADER.finish_excep IS
'Value based on sum of backlog_detail.finish_excep
  = * if any value is *
  = null, if all values are null';

COMMENT ON COLUMN BACKLOG_HEADER.strap_qty IS
'Sum of backlog_detail.strap_qty';

COMMENT ON COLUMN BACKLOG_HEADER.strap_multi IS
'Value is set based on all SO line items, where backlog_detail.strap_1
 and backlog_detail.strap_2 has
  = null, if all values are null
  = color, if only one color
  = MULTI, if more than one color';

COMMENT ON COLUMN BACKLOG_HEADER.strap_excep IS
'Value based on sum of backlog_detail.strap_excep
  = * if any value is *
  = null, if all values are null';

COMMENT ON COLUMN BACKLOG_HEADER.fabric_qty IS
'sum of backlog_detail.fabric_qty';

COMMENT ON COLUMN BACKLOG_HEADER.fabric_multi IS
'Value is set based on all SO line items, where backlog_detail.fabric_1
 and backlog_detail.fabric_2 has
  = null, if all values are null
  = color, if only one color
  = MULTI, if more than one color';

COMMENT ON COLUMN BACKLOG_HEADER.fabric_excep IS
'Value based on sum of backlog_detail.fabric_excep
  = * if any value is *
  = null, if all values are null';