1. Tables and Views
  2. PSC_INS_INSPECTION

PSC_INS_INSPECTION

This table contains inspection transaction, including inspection result and score.

Details

  • Schema: FUSION

  • Object owner: PSC_PER

  • Object type: TABLE

  • Tablespace: TRANSACTION_TABLES

Primary Key

Name Columns

PSC_INS_INSPECTION_PK

INSPECTION_KEY

Columns

Name Datatype Length Precision Not-null Comments Status
INSPECTION_KEY NUMBER 18 Yes This column is the unique identifier for an inspection. This random value is automatically generated.
INSPECTION_PUID VARCHAR2 480 Yes Alternate key used by CRM Import.
INSPECTION_ID VARCHAR2 50 Yes This column is used to indicate inspection id Active
PARCEL_ID NUMBER 18 This column is needed in Inspection Schedule to determine the District ID which in turn will determine which inspector can be assigned to the inspection task.
ADDRESS_ID NUMBER 18 This column is needed in Inspection Schedule to determine the District ID which in turn will determine which inspector can be assigned to the inspection task.
PHONE_COUNTRY_CODE VARCHAR2 10 Contact International country code for the phone number
PHONE_AREA_CODE VARCHAR2 10 Contact phone area code
PHONE_EXTENSION VARCHAR2 20 Contact additional phone number extension
CANCELLATION_REASON VARCHAR2 150 Reason for canceling inspection schedule
DESCRIPTION VARCHAR2 100 Yes This column is used to indicate description Active
INSPECTION_TYPE VARCHAR2 15 Yes This column is used to indicate inspection type Active
INSPECTION_STATUS VARCHAR2 15 Yes This column is used to indicate inspection status Active
BILLABLE_FLAG VARCHAR2 1 Yes This column is used to indicate billable flag Active
BILL_TYPE VARCHAR2 15 This column is used to indicate bill type Active
LNP_RECORD_KEY NUMBER 18 Yes This column is used to indicate lnp record key Active
LNP_RECORD_ID VARCHAR2 50 Yes This column is used to indicate lnp record id Active
RECORD_NUMBER VARCHAR2 64 Yes PuID for a Permit record. Used by CRM Import.
REQUESTED_DTTM TIMESTAMP This column is used to indicate requested dttm Obsolete
READY_BY_DTTM TIMESTAMP READY_BY_DTTM
PREFERRED_DATE TIMESTAMP PREFERRED_DATE
PREFERRED_TIME VARCHAR2 15 PREFERRED_TIME
INSPECTOR_ID NUMBER 18 This column is used to indicate inspector id Active
INSPECTOR_NAME VARCHAR2 360 This column is used to indicate inspector name Active
INSPECTOR_INITIALS VARCHAR2 6 This column is used to indicate inspector initials Active
SCHEDULED_START_DTTM TIMESTAMP This column is used to indicate scheduled start dttm Active
SCHEDULED_END_DTTM TIMESTAMP This column is used to indicate scheduled end dttm Active
ACTUAL_START_DTTM TIMESTAMP This column is used to indicate actual start dttm Active
ACTUAL_END_DTTM TIMESTAMP This column is used to indicate actual end dttm Active
ESTIMATED_DURATION_HRS NUMBER 8 This column is used to indicate estimated duration hrs Active
ACTUAL_DURATION_HRS NUMBER 8 This column is used to indicate actual duration hrs Active
ESTIMATED_TIME_ARRIVAL TIMESTAMP This column is used to indicate estimated time arrival Active
ACTL_TRAVEL_TIME_TO_HRS NUMBER 8 This column is used to indicate actl travel time to hrs Active
ACTL_TRAVEL_TIME_FROM_HRS NUMBER 8 This column is used to indicate actl travel time from hrs Active
INSPECTION_LOCATION VARCHAR2 100 This column is used to indicate inspection location Active
COUNTRY VARCHAR2 3 This column is used to indicate country Active
ADDRESS1 VARCHAR2 240 This column is used to indicate address1 Active
ADDRESS2 VARCHAR2 240 This column is used to indicate address2 Active
ADDRESS3 VARCHAR2 240 This column is used to indicate address3 Active
ADDRESS4 VARCHAR2 240 This column is used to indicate address4 Active
CITY VARCHAR2 60 This column is used to indicate city Active
POSTAL_CODE VARCHAR2 60 This column is used to indicate postal code Active
STATE VARCHAR2 60 This column is used to indicate state Active
PROVINCE VARCHAR2 60 This column is used to indicate province Active
COUNTY VARCHAR2 60 This column is used to indicate county Active
POSTAL_PLUS4_CODE VARCHAR2 10 This column is used to indicate postal plus4 code Active
LOCATION_LATITUDE NUMBER This column is used to indicate location latitude Active
LOCATION_LONGITUDE NUMBER This column is used to indicate location longitude Active
INSPECTION_DISTRICT_ID VARCHAR2 30 From Inspection address, determine Parcel ID that is used to derive the Inspection District ID Active
TRAVEL_TO_START_ODOMETER NUMBER 8 This column is used to indicate travel to start odometer Active
TRAVEL_TO_END_ODOMETER NUMBER 8 This column is used to indicate travel to end odometer Active
TRAVEL_TO_ACTUAL_DISTANCE NUMBER 8 This column is used to indicate travel to actual distance Active
TRAVEL_FROM_START_ODOMETER NUMBER 8 This column is used to indicate travel from start odometer Active
TRAVEL_FROM_END_ODOMETER NUMBER 8 This column is used to indicate travel from end odometer Active
TRAVEL_FROM_ACTUAL_DISTANCE NUMBER 8 This column is used to indicate travel from actual distance Active
DISTANCE_UOM VARCHAR2 5 This column is used to indicate distance uom Active
INSPECTION_RESULT VARCHAR2 25 This column is used to indicate inspection result Active
INSPECTION_RATING VARCHAR2 5 This column is used to indicate inspection rating Active
INSPECTION_SCORE NUMBER 5 This column is used to indicate inspection score Active
NUM_MAJOR_VIOLATIONS NUMBER 5 This column is used to indicate num major violations Active
HIDE_INSPECTOR_COMMENT VARCHAR2 1 This column is used to indicate hide inspector comment Active
CONTACT_NAME VARCHAR2 450 This column is used to indicate contact name Active
PHONE_NUMBER VARCHAR2 40 This column is used to indicate contact phone Active
CONTACT_EMAIL VARCHAR2 320 This column is used to indicate contact email Active
PREFERRED_CONTACT_METHOD VARCHAR2 30 This column is used to indicate preferred contact method Active
OWNER_SIGNATURE BLOB This column is used to indicate owner signature Active
CONTRACTOR_SIGNATURE BLOB This column is used to indicate contractor signature Active
INSPECTOR_SIGNATURE BLOB This column is used to indicate inspector signature Active
REINSPECTION_FLAG VARCHAR2 1 This column is used to indicate reinspection flag Active
FINAL_INSPECTION_FLAG VARCHAR2 1 This column is used to indicate final inspection flag Active
REQUIRED_FLAG VARCHAR2 1 Yes Indicates whether the inspection is required
OBJECT_VERSION_NUMBER NUMBER 9 Yes Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. Active
OWNER_NAME VARCHAR2 360 This column is used to indicate owner name Active
CONTRACTOR_NAME VARCHAR2 360 This column is used to indicate contractor name Active
OWNER_SIGNATURE_DTTM TIMESTAMP This column is used to indicate owner signature dttm Active
CONTRACTOR_SIGNATURE_DTTM TIMESTAMP This column is used to indicate contractor signature dttm Active
INSPECTOR_SIGNATURE_DTTM TIMESTAMP This column is used to indicate inspector signature dttm Active
SYSTEM_STATUS VARCHAR2 15 This column is used to indicate system status Active
SYSTEM_RESULT VARCHAR2 20 This column is used to indicate system result
INSPECTION_COMPLETE_DTTM TIMESTAMP This column is used to indicate inspection complete dttm Active
CREATED_BY VARCHAR2 64 Yes Who column: indicates the user who created the row. Active
CREATION_DATE TIMESTAMP Yes Who column: indicates the date and time of the creation of the row. Active
LAST_UPDATED_BY VARCHAR2 64 Yes Who column: indicates the user who last updated the row. Active
LAST_UPDATE_DATE TIMESTAMP Yes Who column: indicates the date and time of the last update of the row. Active
LAST_UPDATE_LOGIN VARCHAR2 32 Who column: indicates the session login associated to the user who last updated the row. Active
AGENCY_ID NUMBER 8 Yes This column is used to indicate agency id Active
ESTIMATED_TIME_ARRIVAL_END TIMESTAMP The estimated time to arrival end time. Active
COORDINATE_X NUMBER This column is used to indicate coordinate x.
COORDINATE_Y NUMBER This column is used to indicate coordinate y.
SRID NUMBER 10 This column is used to indicate srid.
SHAPE UDT Specifies coordinates of the location defined by the address. Special data type required to store.
REQUESTOR_COMMENT VARCHAR2 500 Comment enters by inspection requester for additional information about the inspection
PREFERRED_INSPECTOR_ID NUMBER 18 This column is used to indicate the preferred inspector id for the re-inspection.
REINSPECTION_ID_BY VARCHAR2 50 This column is used to indicate the inspection id for this inspection's re-inspection.
REINSPECTION_ID_OF VARCHAR2 50 This column is used to indicate the inspection id that failed resulting in the creation of this inspection.
MIGRATED_DATA_FLAG VARCHAR2 3 Status of CRM Data Import migration.
ASSESS_FEE_FLAG VARCHAR2 1 Yes This column is used to indicate the assess fee flag.
INSPECTION_SOURCE VARCHAR2 30 Indicates the source from which the inspection was created.
STREET_NUMBER VARCHAR2 20 This column indicates the street number for the parcel.
STREET_DIRECTION VARCHAR2 10 This column is used to indicate street direction.
STREET_NAME VARCHAR2 240 This column is used to indicate street name.
STREET_TYPE VARCHAR2 20 This column is used to indicate street type.
STREET_SUFFIX VARCHAR2 20 This column describes street name that represents the street..
UNIT_NUMBER VARCHAR2 30 This column is used to indicate unit number.
STREET_NAME_CONCAT VARCHAR2 350 A concatenation of parts of an address that contains street information.
TEXT_COUNTRY_CODE VARCHAR2 10 Contact text message country code
TEXT_AREA_CODE VARCHAR2 10 Contact text message area code
TEXT_NUMBER VARCHAR2 40 Contact text message number
HIDE_FROM_USER_FLAG VARCHAR2 1 Yes This column indicates whether the type is visible to the public user.
LAST_GIS_SYNC_DATETIME TIMESTAMP This column is used to indicate the date and time the inspection was synchronized by GIS.

Foreign Keys

Table Foreign Table Foreign Key Column
PSC_INS_INSPECTION psc_lnp_record LNP_RECORD_KEY
psc_ins_attachment psc_ins_inspection INSPECTION_KEY
psc_ins_inspection_chklist psc_ins_inspection INSPECTION_KEY
psc_ins_opa_checkpoint psc_ins_inspection INSPECTION_ID
psc_lnp_ins_re_xref psc_ins_inspection INSPECTION_KEY
psc_ins_comment psc_ins_inspection INSPECTION_KEY
psc_ins_related_inspection psc_ins_inspection INSPECTION_ID

Indexes

Index Uniqueness Tablespace Columns Status
PSC_INS_INSPECTION_FK1 Non Unique Default LNP_RECORD_KEY
PSC_INS_INSPECTION_N1 Non Unique Default SYSTEM_STATUS, INSPECTOR_ID, AGENCY_ID, SCHEDULED_START_DTTM, SCHEDULED_END_DTTM
PSC_INS_INSPECTION_PK Unique Default INSPECTION_KEY Active
PSC_INS_INSPECTION_U1 Unique Default INSPECTION_PUID
PSC_INS_INSPECTION_U2 Unique Default INSPECTION_ID