Computer Associates's Report Template Builder - DataModelDictionary Document

Name	ONROAD_EM
Comment	Onroad Mobile 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_PROCESS_DESCRIPTION	No	No		VARCHAR2(80)	NULL	A text description of the Emission Process.
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
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
ORGANIZATION_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
INVENTORY_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
SOURCE_INVENTORY_ID	No	No		NUMBER(20)	NULL	Unique System Assigned ID
SOURCE_INVENTORY_PK	No	No		NUMBER(20)	NULL	Unique System Assigned ID
PE_PRIMARY_KEY	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 "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_PROCESS_DESCRIPTION" 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 "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 "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 "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 "PE_PRIMARY_KEY" 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
XPKONROAD_EM
XAK1ONROAD_EM
XIE1ONROAD_EM
XIF4ONROAD_EM
XIF5ONROAD_EM
XIF6ONROAD_EM
XIF7ONROAD_EM
XIF8ONROAD_EM

Member(s) of "XPKONROAD_EM" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1ONROAD_EM" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1ONROAD_EM" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

TRIBAL_CODE

Column

Name

SCC

Column

Name

POLLUTANT_CODE

Column

Name

START_DATE

Column

Name

END_DATE

Member(s) of "XIF4ONROAD_EM" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF5ONROAD_EM" Index

Column

Name

ORGANIZATION_ID

Member(s) of "XIF6ONROAD_EM" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF7ONROAD_EM" Index

Column

Name

PE_PRIMARY_KEY

Member(s) of "XIF8ONROAD_EM" Index

Column

Name

TRANSFER_JOB_ID

Child Relationship

Parent Relationship

Parent Table

Name

TRANSFER_FILE

Parent Table

Name

ONROAD_PE

Parent Table

Name

INVENTORY

Parent Table

Name

ORGANIZATION

Parent Table

Name

SECTOR

Script

Name

Code

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 ;

/

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 ;

/