Computer Associates's Report Template Builder - DataModelDictionary Document

Name	ACTIVITY_PERMIT
Comment	Activity permit 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
EMISSION_RELEASE_POINT_ID	No	No		VARCHAR2(6)	NULL	Emission Release Point ID
SCC	No	No		VARCHAR2(10)	NULL	EPA Source Category Code for Area and Mobile Sources.
START_DATE	No	No		NUMBER(8)	NULL	Start Date
END_DATE	No	No		NUMBER(8)	NULL	End Date
PERMIT_NUMBER	No	No		VARCHAR2(20)	NULL	Permit Number
PERMITTED_ENTITY	No	No		VARCHAR2(20)	NULL	Permitted entity
PERMIT_FEE	No	No		NUMBER	NULL	Permit Fee
AUTHORIZED_BY	No	No		VARCHAR2(70)	NULL	The complete name of the person, including first name, middle name or initial, and surname.
AUTHORIZED_PHONE	No	No		VARCHAR2(10)	NULL	The phone number for the person.
LOCATION_ADDRESS	No	No		VARCHAR2(50)	NULL	Physical location of the front door / main entrance of the facility site
CITY	No	No		VARCHAR2(60)	NULL	The name of the city.
STATE	No	No		VARCHAR2(2)	NULL	State abbreviation.
ZIP_CODE	No	No		VARCHAR2(14)	NULL	The U.S. Postal Service zip code.
PERMIT_PERSON_NAME	No	No		VARCHAR2(70)	NULL	The complete name of the person, including first name, middle name or initial, and surname.
PERMIT_PHONE_NUMBER	No	No		VARCHAR2(10)	NULL	The phone number for the person.
PERMIT_FAX_NUMBER	No	No		VARCHAR2(10)	NULL	The phone number for the person.
PERMIT_ELECTRONIC_ADDRESS	No	No		VARCHAR2(100)	NULL	A resource address usually consisting of the access protocol, the domain name, and optionally, the path to a file or location. Report Email.
PERMIT_CONDITIONS	No	No		VARCHAR2(4000)	NULL	Permit Conditions
PERMIT_RESTRICTIONS	No	No		VARCHAR2(4000)	NULL	Permit Restrictions
PERMIT_LIMITATIONS	No	No		VARCHAR2(4000)	NULL	Permit Limitations
ISSUING_ENTITY	No	No		VARCHAR2(80)	NULL	Issuing entity
ISSUE_DATE	No	No		DATE	NULL	Issue Date
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).
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.
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
TRANSFER_JOB_ID	No	Yes		NUMBER(20)	NULL	Transfer job ID
RECORD_NUMBER	No	No		NUMBER(20)	NULL	Unique System Assigned ID
CREATED_BY	No	No		VARCHAR2(20)	NULL	Created by User ID
CREATED_DATE	No	No		DATE	NULL	Created Date
MODIFIED_DATE	No	No		DATE	NULL	Modified date
MODIFIED_BY	No	No		VARCHAR2(20)	NULL	Modified by User ID
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
FK_TABLE_NAME	No	No		VARCHAR2(32)	NULL	Foreign Key Table Name
FK_PRIMARY_KEY	No	No		NUMBER(20)	NULL	Unique System Assigned ID
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
INVENTORY_ID	No	Yes		NUMBER(20)	NULL	Unique System Assigned ID
ORGANIZATION_ID	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 "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 "EMISSION_RELEASE_POINT_ID" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	None

UDP(s) of "SCC" 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 "PERMIT_NUMBER" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PERMITTED_ENTITY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PERMIT_FEE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "AUTHORIZED_BY" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	None

UDP(s) of "AUTHORIZED_PHONE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "LOCATION_ADDRESS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "CITY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "STATE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "ZIP_CODE" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PERMIT_PERSON_NAME" Column

Range Check	Null Check	Valid Table/Values
None	Mandatory	None

UDP(s) of "PERMIT_PHONE_NUMBER" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PERMIT_FAX_NUMBER" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PERMIT_ELECTRONIC_ADDRESS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PERMIT_CONDITIONS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PERMIT_RESTRICTIONS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "PERMIT_LIMITATIONS" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "ISSUING_ENTITY" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "ISSUE_DATE" 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 "SUBMITTAL_FLAG" 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 "TRANSFER_JOB_ID" 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_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 "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 "FK_TABLE_NAME" Column

Range Check	Null Check	Valid Table/Values
None	None	None

UDP(s) of "FK_PRIMARY_KEY" 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 "INVENTORY_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

Index

Name	Member
XPKACTIVITY_PERMIT
XAK1ACTIVITY_PERMIT
XIE1ACTIVITY_PERMIT
XIE2ACTIVITY_PERMIT
XIE3ACTIVITY_PERMIT
XIF1ACTIVITY_PERMIT
XIF3ACTIVITY_PERMIT
XIF4ACTIVITY_PERMIT
XIF5ACTIVITY_PERMIT

Member(s) of "XPKACTIVITY_PERMIT" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1ACTIVITY_PERMIT" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1ACTIVITY_PERMIT" Index

Column

Name

STATE_AND_COUNTY_FIPS_CODE

Column

Name

TRIBAL_CODE

Member(s) of "XIE2ACTIVITY_PERMIT" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

TRIBAL_CODE

Member(s) of "XIE3ACTIVITY_PERMIT" Index

Column

Name

FK_TABLE_NAME

Column

Name

FK_PRIMARY_KEY

Member(s) of "XIF1ACTIVITY_PERMIT" Index

Column

Name

TRANSFER_JOB_ID

Member(s) of "XIF3ACTIVITY_PERMIT" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF4ACTIVITY_PERMIT" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF5ACTIVITY_PERMIT" Index

Column

Name

ORGANIZATION_ID

Child Relationship

Parent Relationship

Parent Table

Name

ORGANIZATION

Parent Table

Name

INVENTORY

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 ;

/