/*****************************************************************************
*
* Filename         - $Workfile:   ulds_card_details.sql  $
*
*                     Name              Date      Version     CR Number
* Author           -  Chris Reason      20/05/03
* Last Update      -  See revision history below.
*
* Project Name     - FALCP.
*
* Source Code
* Identifier       - N/A.
*
* Design Spec
* Section          - FAS/BBTRK/SS/222.
*
* Contents         - Live Schema: Creates the ulds_card_details table.
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2003
*
*****************************************************************************/

PROMPT Creating table ULD_CARD_DETAILS

create table ULD_CARD_DETAILS(
   TEMPLATE_NAME                 varchar2(30) NOT NULL,
   AGENT                         varchar2(30) NOT NULL,
   CARD_ID                       number(2,0) NOT NULL,
   ULD_CATEGORY                  varchar2(5),
   ULD_TYPE                      varchar2(1),
   DEST1                         varchar2(3),
   DEST2                         varchar2(3),
   DEST3                         varchar2(3),
   DEST4                         varchar2(3),
   DEST5                         varchar2(3),
   DEST6                         VARCHAR2(3),
   CLASS1                        varchar2(10),
   CLASS2                        varchar2(10),
   CLASS3                        varchar2(10),
   CLASS4                        varchar2(10),
   CLASS5                        varchar2(10),
   CLASS6                        VARCHAR2(10),
   QUANTITY                      number(2,0),
   LOADED_AT                     varchar2(3),
   TRANSFER_AT                   varchar2(3),
   ONWARD_FLIGHT_NO1             varchar2(8),
   ONWARD_FLIGHT_DATE1           varchar2(5),
   ONWARD_FLIGHT_NO2             varchar2(8),
   ONWARD_FLIGHT_DATE2           varchar2(5),
   ONWARD_FLIGHT_NO3             varchar2(8),
   ONWARD_FLIGHT_DATE3           varchar2(5),
   ONWARD_FLIGHT_NO4             varchar2(8),
   ONWARD_FLIGHT_DATE4           varchar2(5),
   ONWARD_FLIGHT_NO5             varchar2(8),
   ONWARD_FLIGHT_DATE5           varchar2(5),
   ONWARD_FLIGHT_NO6             varchar2(8),
   ONWARD_FLIGHT_DATE6           varchar2(5),
   CONTENTS_REMARKS              varchar2(30),
   REMARKS                       varchar2(30),
   DATE_OFFSET_1                 NUMBER(3),
   DATE_OFFSET_2                 NUMBER(3),
   DATE_OFFSET_3                 NUMBER(3),
   DATE_OFFSET_4                 NUMBER(3),
   DATE_OFFSET_5                 NUMBER(3),
   DATE_OFFSET_6                 NUMBER(3),
   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 uld_card_details add ( constraint uld_card_details_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table uld_card_details add constraint uld_card_details_uk1 unique (template_name,agent,card_id) using index tablespace &1._indexes;

CREATE index uld_card_details_sk1 ON uld_card_details( template_name, agent, card_id, loaded_at, transfer_at) TABLESPACE &1._indexes;

COMMENT ON TABLE ULD_CARD_DETAILS IS 'This table contains ULD card details';

COMMENT ON COLUMN ULD_CARD_DETAILS.AGENT IS  'Agent code';
COMMENT ON COLUMN ULD_CARD_DETAILS.CARD_ID IS  'Card identifier';
COMMENT ON COLUMN ULD_CARD_DETAILS.CLASS1 IS  'Class associated with the first onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.CLASS2 IS  'Class associated with the second onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.CLASS3 IS  'Class associated with the third onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.CLASS4 IS  'Class associated with the fourth onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.CLASS5 IS  'Class associated with the fifth onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.CLASS6 IS  'Class associated with the sixth onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.CONTENTS_REMARKS IS  'Contents remarks';
COMMENT ON COLUMN ULD_CARD_DETAILS.DEST1 IS  'Destination for the first flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DEST2 IS  'Destination for the second flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DEST3 IS  'Destination for the third flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DEST4 IS  'Destination for the fourth flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DEST5 IS  'Destination for the fifth flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DEST6 IS  'Destination for the sixth flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.LOADED_AT IS  'Airport the ULD is to be loaded at';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_DATE1 IS  'Date of the first onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_DATE2 IS  'Date of the second onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_DATE3 IS  'Date of the third onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_DATE4 IS  'Date of the fourth onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_DATE5 IS  'Date of the fifth onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_DATE6 IS  'Date of the sixth onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_NO1 IS  'First onward flight number ';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_NO2 IS  'Second onward flight number';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_NO3 IS  'Third onward flight number';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_NO4 IS  'Fourth onward flight number';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_NO5 IS  'Fifth onward flight number';
COMMENT ON COLUMN ULD_CARD_DETAILS.ONWARD_FLIGHT_NO6 IS  'Sixth onward flight number';
COMMENT ON COLUMN ULD_CARD_DETAILS.QUANTITY IS  'Quantity';
COMMENT ON COLUMN ULD_CARD_DETAILS.REMARKS IS  'Remarks';
COMMENT ON COLUMN ULD_CARD_DETAILS.DATE_OFFSET_1 IS 'Date offset of the first onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DATE_OFFSET_2 IS 'Date offset of the second onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DATE_OFFSET_3 IS 'Date offset of the third onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DATE_OFFSET_4 IS 'Date offset of the fourth onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DATE_OFFSET_5 IS 'Date offset of the five onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.DATE_OFFSET_6 IS 'Date offset of the six onward flight';
COMMENT ON COLUMN ULD_CARD_DETAILS.TEMPLATE_NAME IS  'Name of the template';
COMMENT ON COLUMN ULD_CARD_DETAILS.TRANSFER_AT IS  'Airport the ULD will be transferred at';
COMMENT ON COLUMN ULD_CARD_DETAILS.ULD_CATEGORY IS  'Category of the ULD';
COMMENT ON COLUMN ULD_CARD_DETAILS.ULD_TYPE IS  'Type of the ULD';
COMMENT ON COLUMN ULD_CARD_DETAILS.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN ULD_CARD_DETAILS.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN ULD_CARD_DETAILS.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN ULD_CARD_DETAILS.CUSER IS 'The user who created the row';
COMMENT ON COLUMN ULD_CARD_DETAILS.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN ULD_CARD_DETAILS.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN ULD_CARD_DETAILS.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN ULD_CARD_DETAILS.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN ULD_CARD_DETAILS.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';
