Computer Associates's Report Template Builder - DataModelDictionary Document

Name	POINT_PE
Comment	Point emission period 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
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
ACTUAL_THROUGHPUT	No	No		NUMBER	NULL	Numeric value of process activity. Report actual throughput for the emission period designated, e.g., Start Date, End Date.
THROUGHPUT_UNIT_NUMERATOR	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
PERIOD_DAYS_PER_WEEK	No	No		NUMBER(1)	NULL	Average number of days per week an emission process is active within year.
PERIOD_WEEKS_PER_PERIOD	No	No		NUMBER(2)	NULL	Average number of weeks per year an emission process is active.
PERIOD_HOURS_PER_DAY	No	No		NUMBER(2)	NULL	Average number of hours per day an emission process is active within year.
PERIOD_HOURS_PER_PERIOD	No	No		NUMBER(4)	NULL	Average number of hours per year an emission process is active.
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
EP_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 "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 "ACTUAL_THROUGHPUT" Column

Range Check	Null Check	Valid Table/Values
Greater than 0	None	None

UDP(s) of "THROUGHPUT_UNIT_NUMERATOR" 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 "PERIOD_DAYS_PER_WEEK" Column

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

UDP(s) of "PERIOD_WEEKS_PER_PERIOD" Column

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

UDP(s) of "PERIOD_HOURS_PER_DAY" Column

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

UDP(s) of "PERIOD_HOURS_PER_PERIOD" Column

Range Check	Null Check	Valid Table/Values
Between 0 and 8760	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 "EP_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_PE
XAK1POINT_PE
XIE1POINT_PE
XIF4POINT_PE
XIF5POINT_PE
XIF6POINT_PE
XIF7POINT_PE
XIF8POINT_PE

Member(s) of "XPKPOINT_PE" Index

Column

Name

PRIMARY_KEY

Member(s) of "XAK1POINT_PE" Index

Column

Name

TRANSFER_JOB_ID

Column

Name

RECORD_NUMBER

Member(s) of "XIE1POINT_PE" Index

Column

Name

STATE_FIPS

Column

Name

COUNTY_FIPS

Column

Name

TRIBAL_CODE

Column

Name

STATE_FACILITY_IDENTIFIER

Column

Name

EMISSION_UNIT_ID

Column

Name

PROCESS_ID

Column

Name

START_DATE

Column

Name

END_DATE

Member(s) of "XIF4POINT_PE" Index

Column

Name

SECTOR_CODE

Member(s) of "XIF5POINT_PE" Index

Column

Name

ORGANIZATION_ID

Member(s) of "XIF6POINT_PE" Index

Column

Name

EP_PRIMARY_KEY

Member(s) of "XIF7POINT_PE" Index

Column

Name

INVENTORY_ID

Member(s) of "XIF8POINT_PE" Index

Column

Name

TRANSFER_JOB_ID

Child Relationship

Child Table

Name

POINT_EM

Parent Relationship

Parent Table

Name

TRANSFER_FILE

Parent Table

Name

INVENTORY

Parent Table

Name

POINT_EP

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 ;

/