/*****************************************************************************
*
* Filename         - $Workfile : security_certificate_v1.sql $
*
* Project Name     - ULTRATRAK(Oracle Database)
*
* Source Code
* Identifier       - FAS/FALCP
*
* Description      - BARNZ Hold Baggage Screening and Reports
*
* Language         - Oracle PL/SQL
*
* File History     - For the development history run the Show History report in PVCS
*
* Coptright Ultra Electronics Airport Systems 2005
*
*****************************************************************************/

PROMPT CREATE OR REPLACE VIEW security_certificate_v1

CREATE OR REPLACE FORCE VIEW security_certificate_v1 (
   flight_id
 , flight_no
 , flight_date
 , bag_tag
 , bag_tag1
 , bag_tag2a
 , bag_tag2b
 , bag_tag3
 , bag_destination
 , bag_exception
 , bag_security
 , pax_id
 , pax_surname
 , pax_initials
 , sc_surname
 , sc_firstname
 , sc_loginname
 , sc_date_time
 , sc_location
)
AS
SELECT  f.flight_id
      , f.flight_no
      , f.flight_date
      , b.bag_tag
      , b.bag_tag1
      , b.bag_tag2a
      , b.bag_tag2b
      , TO_CHAR (b.bag_tag3)
      , b.bag_destination
      , b.bag_exception
      , b.security_clearance_status
      , p.pax_id
      , p.pax_surname
      , p.pax_initials
      , t.surname
      , t.firstname
      , t.loginname
      , b.sc_time
      , b.sc_location
FROM    tagtrak_users t
      , bag           b
      , pax           p
      , flight        f
WHERE   f.flight_id  = p.flight_id
AND     p.pax_id     = b.pax_id
AND     b.sc_user_id = t.user_id (+);

sho err
