Computer Associates's Report Template Builder - DataModelDictionary Document

Name	POINT_CE
Comment	Point Control Equipment

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
PRIMARY_PCT_CONTROL_EFFICIENCY	No	No		NUMBER	NULL	Primary PCT Control Efficiency
PCT_CAPTURE_EFFICIENCY	No	No		NUMBER	NULL	PCT Capture Efficiency
TOTAL_CAPTURE_CNTRL_EFFICIENCY	No	No		NUMBER	NULL	Total Capture control efficiency
PRIMARY_DEVICE_TYPE	No	No		VARCHAR2(3)	NULL	Code table - CONTROL_DEVICE_TYPES
SECONDARY_DEVICE_TYPE	No	No		VARCHAR2(3)	NULL	Code table - CONTROL_DEVICE_TYPES
CONTROL_SYSTEM_DESCRIPTION	No	No		VARCHAR2(40)	NULL	Description of control equipment chain.
THIRD_CONTROL_DEVICE_TYPE	No	No		VARCHAR2(3)	NULL	Code table - CONTROL_DEVICE_TYPES
FOURTH_CONTROL_DEVICE_TYPE	No	No		VARCHAR2(3)	NULL	Code table - CONTROL_DEVICE_TYPES
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
EM_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 "PRIMARY_PCT_CONTROL_EFFICIENCY" Column

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

UDP(s) of "PCT_CAPTURE_EFFICIENCY" Column

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

UDP(s) of "TOTAL_CAPTURE_CNTRL_EFFICIENCY" Column

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

UDP(s) of "PRIMARY_DEVICE_TYPE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "SECONDARY_DEVICE_TYPE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "CONTROL_SYSTEM_DESCRIPTION" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "THIRD_CONTROL_DEVICE_TYPE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "FOURTH_CONTROL_DEVICE_TYPE" 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 "EM_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_CE
XAK1POINT_CE
XIE1POINT_CE
XIF3POINT_CE
XIF4POINT_CE
XIF5POINT_CE
XIF6POINT_CE
XIF7POINT_CE

Member(s) of "XPKPOINT_CE" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1POINT_CE" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1POINT_CE" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

TRIBAL_CODE

Column

Name

EMISSION_UNIT_ID

Column

Name

PROCESS_ID

Column

Name

POLLUTANT_CODE

Member(s) of "XIF3POINT_CE" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF4POINT_CE" Index

Column

Name

ORGANIZATION_ID

Member(s) of "XIF5POINT_CE" Index

Column

Name

EM_PRIMARY_KEY

Member(s) of "XIF6POINT_CE" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF7POINT_CE" Index

Column

Name

TRANSFER_JOB_ID

Child Relationship

Parent Relationship

Parent Table

Name

TRANSFER_FILE

Parent Table

Name

INVENTORY

Parent Table

Name

POINT_EM

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 ;

/