Computer Associates's Report Template Builder - DataModelDictionary Document

Name	POINT_SI
Comment	Point site table

Column

Name	Is PK	Is FK	UDP	Datatype	Null Option	Comment
PRIMARY_KEY	Yes	No		NUMBER(20)	NOT NULL	Unique System Assigned ID
RECORD_TYPE	No	No		VARCHAR2(2)	NULL	A code that identifies the type of Record
STATE_FACILITY_IDENTIFIER	No	No		VARCHAR2(15)	NULL	Unique ID number used by a state/local/tribal agency to identify a facility.
STATE_AND_COUNTY_FIPS_CODE	No	No		VARCHAR2(5)	NULL	The FIPS code for the state and county. Code table - STATE_AND_COUNTY_FIPS_CODE. Also see notes for Tribal Code. If State and County FIPS code does not apply, enter value = 0000 (e.g., 5 zeroes).
FEDERAL_REGISTRY_IDENTIFIER	No	No		VARCHAR2(12)	NULL	The ID number assigned by the EPA Facility Registry System.
FACILITY_CATEGORY	No	No		VARCHAR2(2)	NULL	Indicates if HAP emitting facility is MAJOR or AREA. Code table - FACILITY_CATEGORY
ORIS_FACILITY_CODE	No	No		VARCHAR2(5)	NULL	Unique identifier for electric generating units. DOE code for electric generating units.
SIC_PRIMARY	No	No		VARCHAR2(10)	NULL	Standard Industrial Classification code system. Code table - SIC
NAICS_PRIMARY	No	No		VARCHAR2(6)	NULL	North American Industry Classification code. Code table - NAICS
FACILITY_NAME	No	No		VARCHAR2(80)	NULL	The name of the facility.
SITE_DESCRIPTION	No	No		VARCHAR2(40)	NULL	Comments/ description for this facility.
LOCATION_ADDRESS	No	No		VARCHAR2(50)	NULL	Physical location of the front door / main entrance of the facility site
CITY	No	No		VARCHAR2(60)	NULL	The name of the city.
STATE	No	No		VARCHAR2(2)	NULL	State abbreviation.
ZIPCODE	No	No		VARCHAR2(14)	NULL	The U.S. Postal Service zip code.
COUNTRY	No	No		VARCHAR2(5)	NULL	The country name.
NTI_SITE_ID	No	No		VARCHAR2(20)	NULL	ID for the facility in the 1996 NTI.
DUNN_BRADSTREET_NUMBER	No	No		VARCHAR2(9)	NULL	Dun & Bradstreet no. for the facility.
TRI_ID	No	No		VARCHAR2(20)	NULL	Toxic Release Inventory (TRI) ID for facility.
SUBMITTAL_FLAG	No	No		VARCHAR2(4)	NULL	Submittal status of record when providing data corrections to NEI. Add; Delete; Revise/ Del;or Revise/ Add See User's guide instruction. Enter applicable code - A; D; RD; or RA.
TRIBAL_CODE	No	No		VARCHAR2(3)	NULL	Codes that represent American Indian tribes and Alaskan Native entities Key field and Code table implementation. Code table - TRIBAL_CODES. If Tribal Code does not apply, enter value = 000 (e.g., 3 zeroes).
COUNTRY_CODE	No	No		VARCHAR2(10)	NULL	Country Code
STATE_FIPS	No	No		VARCHAR2(2)	NULL	FIPS code of the state
COUNTY_FIPS	No	No		VARCHAR2(3)	NULL	FIPS code of the county
DATA_SOURCE	No	No		VARCHAR2(10)	NULL	Data Source of the record
RECORD_NUMBER	No	No		NUMBER(20)	NULL	Unique System Assigned ID
CREATED_BY	No	No		VARCHAR2(20)	NULL	Created By
CREATED_DATE	No	No		DATE	NOT NULL	Created Date
MODIFIED_BY	No	No		VARCHAR2(20)	NULL	Modified By
MODIFIED_DATE	No	No		DATE	NOT NULL	Modified Date
LOG_ID	No	No		NUMBER(20)	NULL	Unique System Assigned ID
DISPOSITION_STATUS	No	No		VARCHAR2(1)	NULL	Status of the record
APPLY_STATUS	No	No		VARCHAR2(1)	NULL	Status of the record
SECTOR_CODE	No	Yes		VARCHAR2(2)	NULL	Sector Code
SOURCE_INVENTORY_ID	No	No		NUMBER(20)	NULL	Unique System Assigned ID
SOURCE_INVENTORY_PK	No	No		NUMBER(20)	NULL	Unique System Assigned ID
ORGANIZATION_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
TR_PRIMARY_KEY	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
INVENTORY_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
TRANSFER_JOB_ID	No	Yes		NUMBER(20)	NULL	Transfer job ID

UDP(s) of "PRIMARY_KEY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "RECORD_TYPE" Column

Range Check	Null Check	Valid Table/Values
None	None	Valid Table/List

UDP(s) of "STATE_FACILITY_IDENTIFIER" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	None

UDP(s) of "STATE_AND_COUNTY_FIPS_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	Valid Table/Values

UDP(s) of "FEDERAL_REGISTRY_IDENTIFIER" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "FACILITY_CATEGORY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "ORIS_FACILITY_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SIC_PRIMARY" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	Valid Table/List

UDP(s) of "NAICS_PRIMARY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "FACILITY_NAME" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SITE_DESCRIPTION" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "LOCATION_ADDRESS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "CITY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "STATE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "ZIPCODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "COUNTRY" Column

Range Check	Null Check	Valid Table/Values
None	None	Valid Table/List

UDP(s) of "NTI_SITE_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "DUNN_BRADSTREET_NUMBER" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "TRI_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SUBMITTAL_FLAG" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "TRIBAL_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "COUNTRY_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "STATE_FIPS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "COUNTY_FIPS" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	None

UDP(s) of "DATA_SOURCE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "RECORD_NUMBER" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "CREATED_BY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "CREATED_DATE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "MODIFIED_BY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "MODIFIED_DATE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "LOG_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "DISPOSITION_STATUS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "APPLY_STATUS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SECTOR_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SOURCE_INVENTORY_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SOURCE_INVENTORY_PK" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "ORGANIZATION_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "TR_PRIMARY_KEY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "INVENTORY_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "TRANSFER_JOB_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

Index

Name	Member
XPKPOINT_SI
XAK1POINT_SI
XIE1POINT_SI
XIF3POINT_SI
XIF4POINT_SI
XIF5POINT_SI
XIF6POINT_SI
XIF7POINT_SI

Member(s) of "XPKPOINT_SI" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1POINT_SI" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1POINT_SI" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

TRIBAL_CODE

Column

Name

STATE_FACILITY_IDENTIFIER

Member(s) of "XIF3POINT_SI" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF4POINT_SI" Index

Column

Name

ORGANIZATION_ID

Member(s) of "XIF5POINT_SI" Index

Column

Name

TR_PRIMARY_KEY

Member(s) of "XIF6POINT_SI" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF7POINT_SI" Index

Column

Name

TRANSFER_JOB_ID

Child Relationship

Child Table

Name

POINT_ER

Child Table

Name

POINT_EU

Parent Relationship

Parent Table

Name

TRANSFER_FILE

Parent Table

Name

INVENTORY

Parent Table

Name

Parent Table

Name

ORGANIZATION

Parent Table

Name

SECTOR

Script

Name

Code

BI_SPLIT

create or replace trigger  %TableOwner.%Substr(%TableName,1,15)_BI_Split
before insert on %TableOwner.%TableName for each row
declare new_id number;
begin

      select  %TableOwner.SEQ_%TableName.nextval into new_id from dual;
      :new.%PK() := new_id;
      :new.created_by := user;
      :new.created_date := sysdate;
      :new.modified_by := user;
      :new.modified_date :=sysdate;
      :new.state_fips := substr(:new.state_and_county_fips_code,1,2);
      :new.county_fips := substr(:new.state_and_county_fips_code,3);

end ;

/

create or replace trigger  %TableOwner.%Substr(%TableName,1,21)_BU
before update on %TableOwner.%TableName for each row
begin

      :new.modified_by := user;
      :new.modified_date :=sysdate;
end ;

/