pears.status [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.status ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Description =====
Multi-purpose table holding Person, Vacancy and Progress states.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **type** | char(1) | NOT NULL |  |  |
| **status** | char(4) | NOT NULL |  |  |
| rejectstatus | char(1) | NULL |  |  |
| isfixed | smallint | NULL |  |  |
| name | char(40) | NOT NULL |  |  |
| final | smallint | NULL | 0 |  |
| help | char(40) | NOT NULL |  |  |
| sortorder | integer | NULL |  |  |
| gridsortorder | integer | NULL |  |  |
| PublishToWeb | smallint | NULL | 0 | 1 to show records with this status on web |
| AllowSetOnWeb | smallint | NULL | 0 | 1 to allow this status to be set on web |
| ContactEventClass | char(2) | NULL |  |  |
| BackgroundColour | integer | NULL | 16777215 |  |
| FontColour | integer | NULL | 0 |  |
| IsInterview | smallint | NULL | 0 | 1 for progress interviews |
| OverrideFormColour | tinyint | NULL | 0 |  |

===== Primary Key =====
  * type, status

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| contactclass | ContactEventClass | [[database:tables:pears_contactclass|pears.contactclass (classcode)]] |  |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_personstatusdivision|pears.PersonStatusDivision]] | status | Status, Type | status, type |
| [[database:tables:pears_progressstatusdivision|pears.ProgressStatusDivision]] | status | Status, Type | status, type |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| status_sortorder | Index | sortorder |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| WPK_status_STATUS | after | insert,delete,update order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."status"
-- Table comment: Multi-purpose table holding Person, Vacancy and Progress states.
-- Statement count: 9

CREATE TABLE "pears"."status" (
    "type"                           char(1) NOT NULL
   ,"status"                         char(4) NOT NULL
   ,"rejectstatus"                   char(1) NULL
   ,"isfixed"                        smallint NULL
   ,"name"                           char(40) NOT NULL
   ,"final"                          smallint NULL DEFAULT 0
   ,"help"                           char(40) NOT NULL
   ,"sortorder"                      integer NULL
   ,"gridsortorder"                  integer NULL
   ,"PublishToWeb"                   smallint NULL DEFAULT 0
   ,"AllowSetOnWeb"                  smallint NULL DEFAULT 0
   ,"ContactEventClass"              char(2) NULL
   ,"BackgroundColour"               integer NULL DEFAULT 16777215
   ,"FontColour"                     integer NULL DEFAULT 0
   ,"IsInterview"                    smallint NULL DEFAULT 0
   ,"OverrideFormColour"             tinyint NULL DEFAULT 0
   ,PRIMARY KEY ("type" ASC,"status" ASC) 
   ,CONSTRAINT "StatusLengthCheck" check("length"("Status") <= if "Type" = 'R' then 4 else 1 endif)
)
go


COMMENT ON COLUMN "pears"."status"."PublishToWeb" IS 
	'1 to show records with this status on web'
go


COMMENT ON COLUMN "pears"."status"."AllowSetOnWeb" IS 
	'1 to allow this status to be set on web'
go


COMMENT ON COLUMN "pears"."status"."IsInterview" IS 
	'1 for progress interviews'
go


COMMENT ON TABLE "pears"."status" IS 
	'Multi-purpose table holding Person, Vacancy and Progress states.'
go


ALTER TABLE "pears"."status"
    ADD FOREIGN KEY "contactclass" ("ContactEventClass" ASC)
    REFERENCES "pears"."contactclass" ("classcode")
go


CREATE INDEX "status_sortorder" ON "pears"."status"
    ( "sortorder" )
go


create trigger "WPK_status_STATUS" after insert,delete,update order 1 on
"pears"."status"
for each statement
begin
  call "WPKTrackChange"('P','STATUS')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."status"."WPK_status_STATUS" IS 
{create trigger WPK_status_STATUS 
 after insert,delete,update order 1 on
status
for each statement
begin
  call WPKTrackChange('P','STATUS')
end
}
go
</code>