Computer Associates's Report Template Builder - DataModelDictionary Document

Name	AREA_EM
Comment	Area/NonRoad Mobile/Fires 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).
SCC	No	No		VARCHAR2(10)	NULL	EPA Source Category Code for Area and Mobile Sources.
POLLUTANT_CODE	No	No		VARCHAR2(9)	NULL	Pollutant Code Code table - POLLUTANTS
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	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).
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
RULE_PENETRATION	No	No		NUMBER	NULL	Percent of source category emissions affected by the applied rule
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
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
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
TRANSFER_JOB_ID	No	Yes		NUMBER(20)	NULL	Transfer job ID
ORGANIZATION_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
INVENTORY_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
PE_PRIMARY_KEY	No	Yes		NUMBER(20)	NULL	Unique System Assigned 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 "SCC" 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 "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	None

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 "RULE_PENETRATION" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 100	Mandatory	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 "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 "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 "TRANSFER_JOB_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 "INVENTORY_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

Index

Name	Member
XPKAREA_EM
XAK1AREA_EM
XIE1AREA_EM
XIF2AREA_EM
XIF6AREA_EM
XIF7AREA_EM
XIF8AREA_EM
XIF9AREA_EM

Member(s) of "XPKAREA_EM" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1AREA_EM" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1AREA_EM" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

SCC

Column

Name

TRIBAL_CODE

Column

Name

POLLUTANT_CODE

Column

Name

END_DATE

Column

Name

START_DATE

Member(s) of "XIF2AREA_EM" Index

Column

Name

TRANSFER_JOB_ID

Member(s) of "XIF6AREA_EM" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF7AREA_EM" Index

Column

Name

ORGANIZATION_ID

Member(s) of "XIF8AREA_EM" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF9AREA_EM" Index

Column

Name

PE_PRIMARY_KEY

Child Relationship

Child Table

Name

AREA_CE

Parent Relationship

Parent Table

Name

AREA_PE

Parent Table

Name

INVENTORY

Parent Table

Name

ORGANIZATION

Parent Table

Name

SECTOR

Parent Table

Name

TRANSFER_FILE

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 ;

/