Computer Associates's Report Template Builder - DataModelDictionary Document

Name	POINT_EU
Comment	Point emission unit 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
ORIS_BOILER_ID	No	No		VARCHAR2(6)	NULL	EPA code for electric generating units.
SIC_UNIT_LEVEL	No	No		VARCHAR2(10)	NULL	Standard Industrial Classification code system. Code table - SIC
NAICS_UNIT_LEVEL	No	No		VARCHAR2(6)	NULL	North American Industry Classification code. Code table - NAICS
DESIGN_CAPACITY	No	No		NUMBER	NULL	Numeric value of average operational capacity for an Emission Unit. Report design capacity value if unit is a boiler or turbine.
DESIGN_CAPACITY_UNIT_NUMERATOR	No	No		VARCHAR2(10)	NULL	Unit of Measure
DESIGN_CAPACITY_UNIT_DENOMNTOR	No	No		VARCHAR2(10)	NULL	Unit of Measure
MAX_NAMEPLATE_CAPACITY	No	No		NUMBER	NULL	Electric Generator's rated design capacity at 100% (max) operation. Report value in unit of measure MW.
EMISSION_UNIT_DESCRIPTION	No	No		VARCHAR2(80)	NULL	Emission unit description
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
ORGANIZATION_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
SI_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_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 "ORIS_BOILER_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SIC_UNIT_LEVEL" Column

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

UDP(s) of "NAICS_UNIT_LEVEL" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "DESIGN_CAPACITY" Column

Range Check	Null Check	Valid Table/Values
Greater than 0	None	None

UDP(s) of "DESIGN_CAPACITY_UNIT_NUMERATOR" Column

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

UDP(s) of "DESIGN_CAPACITY_UNIT_DENOMNTOR" Column

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

UDP(s) of "MAX_NAMEPLATE_CAPACITY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EMISSION_UNIT_DESCRIPTION" 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 "ORGANIZATION_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SI_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_EU
XAK1POINT_EU
XIE1POINT_EU
XIF3POINT_EU
XIF4POINT_EU
XIF5POINT_EU
XIF6POINT_EU
XIF7POINT_EU

Member(s) of "XPKPOINT_EU" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1POINT_EU" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1POINT_EU" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

TRIBAL_CODE

Column

Name

STATE_FACILITY_IDENTIFIER

Column

Name

EMISSION_UNIT_ID

Member(s) of "XIF3POINT_EU" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF4POINT_EU" Index

Column

Name

ORGANIZATION_ID

Member(s) of "XIF5POINT_EU" Index

Column

Name

SI_PRIMARY_KEY

Member(s) of "XIF6POINT_EU" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF7POINT_EU" Index

Column

Name

TRANSFER_JOB_ID

Child Relationship

Child Table

Name

POINT_EP

Parent Relationship

Parent Table

Name

TRANSFER_FILE

Parent Table

Name

INVENTORY

Parent Table

Name

POINT_SI

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 ;

/