Computer Associates's Report Template Builder - DataModelDictionary Document

Name	POINT_EP
Comment	Point emission process 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_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).
STATE_FACILITY_IDENTIFIER	No	No		VARCHAR2(15)	NULL	Unique ID number used by a state/local/tribal agency to identify a facility.
EMISSION_UNIT_ID	No	No		VARCHAR2(6)	NULL	Emission Unit ID
PROCESS_ID	No	No		VARCHAR2(6)	NULL	Process ID
SCC	No	No		VARCHAR2(10)	NULL	EPA Source Category Code for Area and Mobile Sources.
EMISSION_RELEASE_POINT_ID	No	No		VARCHAR2(6)	NULL	Emission Release Point ID
PROCESS_MACT_CODE	No	No		VARCHAR2(6)	NULL	Maximum Achievable Control Technology for HAP regulated sources. Code table - MACT_CATEGORY_CODE
EMISSION_PROCESS_DESCRIPTION	No	No		VARCHAR2(80)	NULL	A text description of the Emission Process.
WINTER_THROUGHPUT_PCT	No	No		NUMBER	NULL	The percentage a process operates during the seasonal months. Whole number between 0 to 100.
SPRING_THROUGHPUT_PCT	No	No		NUMBER	NULL	The percentage a process operates during the seasonal months. Whole number between 0 to 100.
SUMMER_THROUGHPUT_PCT	No	No		NUMBER	NULL	The percentage a process operates during the seasonal months. Whole number between 0 to 100.
FALL_THROUGHPUT_PCT	No	No		NUMBER	NULL	The percentage a process operates during the seasonal months. Whole number between 0 to 100.
ANNUAL_AVG_DAYS_PER_WEEK	No	No		NUMBER(1)	NULL	Average number of days per week an emission process is active within year.
ANNUAL_AVG_WEEKS_PER_YEAR	No	No		NUMBER(2)	NULL	Average number of weeks per year an emission process is active.
ANNUAL_AVG_HOURS_PER_DAY	No	No		NUMBER(2)	NULL	Average number of hours per day an emission process is active within year.
ANNUAL_AVG_HOURS_PER_YEAR	No	No		NUMBER(4)	NULL	Average number of hours per year an emission process is active.
HEAT_CONTENT	No	No		NUMBER	NULL	The heat content of a fuel in million BTU's per Ton of coal, 1000 Gals of oil, or million SCF gas.
SULFUR_CONTENT	No	No		NUMBER	NULL	The sulfur content of a fuel (mass percent).
ASH_CONTENT	No	No		NUMBER	NULL	The ash content of a fuel (mass percent).
PROCESS_MACT_COMPLIANCE_STATUS	No	No		VARCHAR2(6)	NULL	Major/Area classification and status under CAAA Sections 112&129. Code table - MACT COMPLIANCE STATUS
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
SOURCE_INVENTORY_ID	No	No		NUMBER(20)	NULL	Unique System Assigned ID
SOURCE_INVENTORY_PK	No	No		NUMBER(20)	NULL	Unique System Assigned ID
SECTOR_CODE	No	Yes		VARCHAR2(2)	NULL	Sector Code
ER_PRIMARY_KEY	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
EU_PRIMARY_KEY	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
INVENTORY_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
ORGANIZATION_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_AND_COUNTY_FIPS_CODE" Column

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

UDP(s) of "STATE_FACILITY_IDENTIFIER" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	None

UDP(s) of "EMISSION_UNIT_ID" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	None

UDP(s) of "PROCESS_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SCC" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EMISSION_RELEASE_POINT_ID" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	None

UDP(s) of "PROCESS_MACT_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EMISSION_PROCESS_DESCRIPTION" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "WINTER_THROUGHPUT_PCT" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 100	None	None

UDP(s) of "SPRING_THROUGHPUT_PCT" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 100	None	None

UDP(s) of "SUMMER_THROUGHPUT_PCT" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 100	None	None

UDP(s) of "FALL_THROUGHPUT_PCT" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 100	None	None

UDP(s) of "ANNUAL_AVG_DAYS_PER_WEEK" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 7	None	None

UDP(s) of "ANNUAL_AVG_WEEKS_PER_YEAR" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 52	None	None

UDP(s) of "ANNUAL_AVG_HOURS_PER_DAY" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 24	None	None

UDP(s) of "ANNUAL_AVG_HOURS_PER_YEAR" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 8760	None	None

UDP(s) of "HEAT_CONTENT" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SULFUR_CONTENT" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "ASH_CONTENT" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PROCESS_MACT_COMPLIANCE_STATUS" 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 "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 "SECTOR_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "ER_PRIMARY_KEY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EU_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 "ORGANIZATION_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_EP
XAK1POINT_EP
XIE1POINT_EP
XIF4POINT_EP
XIF5POINT_EP
XIF6POINT_EP
XIF7POINT_EP
XIF8POINT_EP
XIF9POINT_EP

Member(s) of "XPKPOINT_EP" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1POINT_EP" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1POINT_EP" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

TRIBAL_CODE

Column

Name

PROCESS_ID

Column

Name

SCC

Member(s) of "XIF4POINT_EP" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF5POINT_EP" Index

Column

Name

ER_PRIMARY_KEY

Member(s) of "XIF6POINT_EP" Index

Column

Name

EU_PRIMARY_KEY

Member(s) of "XIF7POINT_EP" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF8POINT_EP" Index

Column

Name

ORGANIZATION_ID

Member(s) of "XIF9POINT_EP" Index

Column

Name

TRANSFER_JOB_ID

Child Relationship

Child Table

Name

POINT_PE

Parent Relationship

Parent Table

Name

TRANSFER_FILE

Parent Table

Name

ORGANIZATION

Parent Table

Name

INVENTORY

Parent Table

Name

POINT_EU

Parent Table

Name

POINT_ER

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 ;

/