/*****************************************************************************
*
* Filename         - $Workfile:   carrier_lookup.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema: carrier_lookup table and associated items
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   carrier_lookup.sql  $  $Revision:   1.8  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/tables/carrier_lookup.svl  $
 *
 *    Rev 1.8   18 Jun 2001 11:10:28   tagtrak
 * OR:
 * CR: 15399
 * REV: 1.8
 * DESC: User Group Changes 2001
 *
 *    Rev 1.6   31 Jan 2000 15:08:30   tagtrak
 * OR:
 * CR: 14402
 * REV:
 * San Francisco Core Product Merge
 * Neil Hilier
 *
 *    Rev 1.5   16 Dec 1998 11:34:38   tagtrak
 * OR: 12352
 * CR: 12352
 * REV:
 * Store Gateway Airline Unique file in database, utilising the
 * carrier_lookup table, and provide an interface function to
 * allow the DCS gateway to access the list.
 * -------------
 * Andrew Walker
 * -------------
 * (branch ATS revision copied to trunk.)
 * Andrew Walker
 *
 *    Rev 1.4.1.0   19 Nov 1998 16:14:58   tagtrak
 * OR: 12352
 * CR: 12352
 * REV:
 * Store Gateway Airline Unique file in database, utilising the
 * carrier_lookup table, and provide an interface function to
 * allow the DCS gateway to access the list.
 * -------------
 * Andrew Walker
 *
 *    Rev 1.4   12 Oct 1998 16:43:10   tagtrak
 * OR: 12682 12683 12678 12654 12419 12265 12805 12285
 * CR: 12682 12683 12678 12654 12419 12265 12805
 * REV:
 * Vienna enhancements/fixes for database V0.4.1E
 * branch ATS revision moved to trunk.
 * Andrew Walker
 *
 *    Rev 1.3.1.0   01 Oct 1998 15:49:34   tagtrak
 * OR: 12682 12683 12678 12654 12419 12265 12285
 * CR: 12682 12683 12678 12654 12419 12265
 * REV:
 * Vienna enhancements for Database 0.4.1
 * Andrew Walker
 *
 *    Rev 1.3.1.0   01 Oct 1998 15:39:22   tagtrak
 *
 *    Rev 1.3   02 Apr 1998 19:42:36   tagtrak
 * OR:
 * CR:
 * REV:
 * Initial Issue
 * See label for concession
 *
 *    Rev 1.2   26 Feb 1998 13:54:52   tagtrak
 * OR:
 * CR:
 * REV:
 * Initial core product version
 *
 *    Rev 1.1.1.1   26 Feb 1998 11:07:32   tagtrak
 * OR:
 * CR:
 * REV:
 * Initial core product version
 *
 *    Rev 1.1.1.0   19 Jan 1998 14:36:06   tagtrak
 * Replaced unique indexes on alpha and bar code columns
 * with unique table constraints, to facilitate use
 * of foreign key constraints gaianst these columns.
 *
 *    Rev 1.1   15 Jan 1998 13:59:08   tagtrak
 * OR: OR11101, TN13, TN15
 * CR:
 * REV:
 * Phase 2C to Phase 3 upgrade, plus code review actions and resolving some echnical issues.
 *
 *
 *    Rev 1.0   22 Oct 1997 11:07:30   tagtrak
 * OR:
 * CR:
 * REV:
 * Initial check-in
*
****************************************************************************/

PROMPT Creating table CARRIER_LOOKUP

CREATE TABLE carrier_lookup (
   alpha_code                    CHAR (3)                             NOT NULL,
   bar_code                      CHAR (3),
   carrier                       VARCHAR2 (40)                        NOT NULL,
   last_user                     NUMBER (10)                          NOT NULL,
   tag_crew_bags                 CHAR (1)      DEFAULT 'Y'            NOT NULL,
   pax_cred                      VARCHAR2 (15) DEFAULT 'SEQUENCE_NO'  NOT NULL,
   pax_unique                    CHAR (1)      DEFAULT 'N'            NOT NULL,
   dcs_boarding                  CHAR (1)      DEFAULT 'Y'            NOT NULL,
   board_match_using             VARCHAR (12)  DEFAULT 'SEQUENCE_NO'  NOT NULL,
   precision_loading             CHAR (1)      DEFAULT 'N',
   selectee_pax                  CHAR (1)      DEFAULT 'N',
   handle_dupes                  CHAR (1)      DEFAULT 'N',
   stdby_pax_unique_key          VARCHAR(15)   DEFAULT 'UNKNOWN',
   not_chkdin_pax_unique_key     VARCHAR(15)   DEFAULT 'UNKNOWN',
   bpm_groups                    VARCHAR2(10),
   bpm_exceptions                VARCHAR2(10),
   row_create_time               date,
   row_date_time                 date,
   cuser                         varchar2(30),
   muser                         varchar2(30),
   mterm                         varchar2(64),
   source                        varchar2(8),
   to_be_archived                varchar2(1),
   appn_row_id                   number(18) not null,
   system_id                     number(4) default 0 not null
);

alter table carrier_lookup add ( constraint carrier_lookup_pk primary key (appn_row_id) using index tablespace &1._indexes);

ALTER TABLE carrier_lookup ADD ( CONSTRAINT carrier_lookup_uk1 UNIQUE (alpha_code) USING INDEX TABLESPACE &1._indexes);

ALTER TABLE carrier_lookup ADD ( CONSTRAINT carrier_lkp_fk_users foreign key (last_user) REFERENCES tagtrak_users( user_id));

ALTER TABLE carrier_lookup ADD ( CONSTRAINT carrier_lkp_fk_creds foreign key (pax_cred) REFERENCES credentials_lookup (pax_cred));

ALTER TABLE carrier_lookup ADD ( constraint carrier_lookup_chk_dcs_b CHECK (dcs_boarding IN ('Y', 'N')));

ALTER TABLE carrier_lookup ADD ( CONSTRAINT carrier_lookup_chk_match CHECK (board_match_using in ('SEQUENCE_NO', 'SEAT_NO')));

ALTER TABLE carrier_lookup ADD ( CONSTRAINT carrier_lookup_chk_sby_key CHECK (NVL(stdby_pax_unique_key,'UNKNOWN') IN ('SEQUENCE_NO', 'SECURITY_NO','UNKNOWN')));

ALTER TABLE carrier_lookup ADD ( CONSTRAINT carrier_lookup_not_chkdin_key CHECK (NVL(not_chkdin_pax_unique_key,'UNKNOWN') IN ('SEQUENCE_NO', 'SECURITY_NO','UNKNOWN')));

CREATE INDEX carrier_lookup_sk2 ON carrier_lookup(last_user) TABLESPACE &1._indexes;

CREATE INDEX carrier_lookup_sk3 ON carrier_lookup(pax_cred) TABLESPACE &1._indexes;

COMMENT ON TABLE CARRIER_LOOKUP IS 'This table contains details of the airline carriers used in the system';

COMMENT ON COLUMN CARRIER_LOOKUP.ALPHA_CODE IS  'The carrier code';
COMMENT ON COLUMN CARRIER_LOOKUP.BAR_CODE IS  'The bar code';
COMMENT ON COLUMN CARRIER_LOOKUP.BOARD_MATCH_USING IS  'Carrier description';
COMMENT ON COLUMN CARRIER_LOOKUP.CARRIER IS  'Carrier Description';
COMMENT ON COLUMN CARRIER_LOOKUP.DCS_BOARDING IS  'Flag to indicate whether the DCS supplies a boarding status for the passenger.';
COMMENT ON COLUMN CARRIER_LOOKUP.HANDLE_DUPES IS  'Flag to enable automatic handling of duplicates (DCS must guarantee unique tags).';
COMMENT ON COLUMN CARRIER_LOOKUP.LAST_USER IS  'User identifier of the last user to update this row.';
COMMENT ON COLUMN CARRIER_LOOKUP.PAX_CRED IS  'Passenger matching credentials. One of NAME, SEAT_NO, SECURITY_NO or SEQUENCE_NO.';
COMMENT ON COLUMN CARRIER_LOOKUP.PAX_UNIQUE IS  'Flag to indicate whether the carrier guarantees a unique sequence or security number for a passenger.';
COMMENT ON COLUMN CARRIER_LOOKUP.PRECISION_LOADING IS  'Flag to enable precision loading for this carrier.';
COMMENT ON COLUMN CARRIER_LOOKUP.SELECTEE_PAX IS  'Flag to indicate whether the DCS supplies the security status for passengers';
COMMENT ON COLUMN CARRIER_LOOKUP.STDBY_PAX_UNIQUE_KEY IS  'Flag to indicate whether the carrier guarantees a unique sequence or security number for a passenger';
COMMENT ON COLUMN CARRIER_LOOKUP.TAG_CREW_BAGS IS  'Flag to indicate crew bags are tagged';
COMMENT ON COLUMN CARRIER_LOOKUP.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN CARRIER_LOOKUP.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN CARRIER_LOOKUP.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN CARRIER_LOOKUP.CUSER IS 'The user who created the row';
COMMENT ON COLUMN CARRIER_LOOKUP.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN CARRIER_LOOKUP.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN CARRIER_LOOKUP.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN CARRIER_LOOKUP.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN CARRIER_LOOKUP.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
