Computer Associates's Report Template Builder - DataModelDictionary Document

Name	POINT_EM
Comment	Point emission 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
POLLUTANT_CODE	No	No		VARCHAR2(9)	NULL	Pollutant Code Code table - POLLUTANTS
EMISSION_RELEASE_POINT_ID	No	No		VARCHAR2(6)	NULL	Emission Release Point ID
START_DATE	No	No		NUMBER(8)	NULL	Start Date
END_DATE	No	No		NUMBER(8)	NULL	End Date
START_TIME	No	No		NUMBER(4)	NULL	Start time of the activity. Format: HHMM
END_TIME	No	No		NUMBER(4)	NULL	End time of the activity. Format: HHMM
EMISSION_NUMERIC_VALUE	No	No		NUMBER	NULL	Numeric value of emission. Unit for criteria emissions: annual = TON; average season day = LBS.
EMISSION_UNIT_NUMERATOR	No	No		VARCHAR2(10)	NULL	Unit of Measure
EMISSION_TYPE	No	No		VARCHAR2(2)	NULL	Code describing temporal designation of emissions reported, i.e., Entire Period, Average Weekday, etc. This code is extended beyond the standard types to include pollution limit amounts, and potential pollution amounts Code table - EMISSION_TYPES. See User's Guide instruction on how to designate time period & emission type for annual or average season day emissions.
EM_RELIABILITY_INDICATOR	No	No		VARCHAR2(5)	NULL	Emission reliability indicator
FACTOR_NUMERIC_VALUE	No	No		NUMBER	NULL	The numeric value of the emission factor.
FACTOR_UNIT_NUMERATOR	No	No		VARCHAR2(10)	NULL	Unit of Measure
FACTOR_UNIT_DENOMINATOR	No	No		VARCHAR2(10)	NULL	Unit of Measure
MATERIAL	No	No		NUMBER(4)	NULL	Material code for material processed. Code table - MATERIALS_PROCESSED
MATERIAL_IO	No	No		VARCHAR2(10)	NULL	A descriptor indicating whether material is used or produced. Code table - MATERIAL IO
EMISSION_CALC_METHOD_CODE	No	No		VARCHAR2(2)	NULL	Code description of method used to derive emissions. Code table - EMISSION_CALC_METHODS
EF_RELIABILITY_INDICATOR	No	No		VARCHAR2(5)	NULL	A reliability indicator code for emission factor rating.
RULE_EFFECTIVENESS	No	No		NUMBER	NULL	Measure of the percent effectiveness of the control strategy.
RULE_EFFECTIVENESS_METHOD	No	No		VARCHAR2(2)	NULL	The code identifying the rule effectiveness method. Code table - RULE_EFFECT_METHODS
HAP_EMISSIONS_PERFORMANCE_LEVL	No	No		VARCHAR2(2)	NULL	HAP emissions performance level
CONTROL_STATUS	No	No		VARCHAR2(12)	NULL	Indicates if reported emissions are controlled or uncontrolled. Valid values = CONTROLLED, UNCONTROLLED
EMISSION_DATA_LEVEL	No	No		VARCHAR2(10)	NULL	The level of site emissions disaggregation reported on the emission record. Valid values = SITE, UNIT, STACK, or PROCESS
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
EMISSION_TON_VALUE	No	No		NUMBER	NULL	Value of emissions in tons
EMISSION_TYPE_PERIOD	No	No		VARCHAR2(10)	NULL	Indicates period type of the emission type code
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
PE_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 "PROCESS_ID" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "POLLUTANT_CODE" 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 "START_DATE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "END_DATE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "START_TIME" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "END_TIME" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EMISSION_NUMERIC_VALUE" Column

Range Check	Null Check	Valid Table/Values
Greater than 0, PM checks	Mandatory	None

UDP(s) of "EMISSION_UNIT_NUMERATOR" Column

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

UDP(s) of "EMISSION_TYPE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EM_RELIABILITY_INDICATOR" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "FACTOR_NUMERIC_VALUE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "FACTOR_UNIT_NUMERATOR" Column

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

UDP(s) of "FACTOR_UNIT_DENOMINATOR" Column

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

UDP(s) of "MATERIAL" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "MATERIAL_IO" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EMISSION_CALC_METHOD_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EF_RELIABILITY_INDICATOR" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "RULE_EFFECTIVENESS" Column

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

UDP(s) of "RULE_EFFECTIVENESS_METHOD" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "HAP_EMISSIONS_PERFORMANCE_LEVL" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "CONTROL_STATUS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EMISSION_DATA_LEVEL" 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 "EMISSION_TON_VALUE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "EMISSION_TYPE_PERIOD" Column

Range Check	Null Check	Valid Table/Values
None	None	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 "PE_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_EM
XAK1POINT_EM
XIE1POINT_EM
XIF4POINT_EM
XIF5POINT_EM
XIF6POINT_EM
XIF7POINT_EM
XIF8POINT_EM

Member(s) of "XPKPOINT_EM" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1POINT_EM" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1POINT_EM" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

TRIBAL_CODE

Column

Name

STATE_AND_COUNTY_FIPS_CODE

Column

Name

EMISSION_UNIT_ID

Column

Name

PROCESS_ID

Column

Name

POLLUTANT_CODE

Column

Name

START_DATE

Column

Name

END_DATE

Member(s) of "XIF4POINT_EM" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF5POINT_EM" Index

Column

Name

ORGANIZATION_ID

Member(s) of "XIF6POINT_EM" Index

Column

Name

PE_PRIMARY_KEY

Member(s) of "XIF7POINT_EM" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF8POINT_EM" Index

Column

Name

TRANSFER_JOB_ID

Child Relationship

Child Table

Name

POINT_CE

Parent Relationship

Parent Table

Name

TRANSFER_FILE

Parent Table

Name

INVENTORY

Parent Table

Name

POINT_PE

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 ;

/