/**********************************************************************************************
*
*      This View is created to help show the reasons why a bags security staus has been set   *
*
**********************************************************************************************/
PROMPT Creating view security_screening_v2

CREATE OR REPLACE FORCE VIEW security_screening_v2  (
 FLIGHT_ID,
 FLIGHT_NO ,
 FLIGHT_DATE ,
 Pre_CLRSTP ,
 PreClr_FID ,   -- this should be excluded by an outer joni to flight 
												 --  but use of view does not warrant that
 ".V"   ,
 SC_SITE ,
 PAX_SURNAME,
 ".I"  ,
 BAG_TAG   ,
 EXCEP ,
 BSI ,
 NON_APR ,
 SCS
)
as
select f.flight_id, f.flight_no,f.flight_date,
    bl.function "Pre_CLRSTP",bl.flight_id,
    s1.airport_code ".V" ,
    ac.SECURITY_CLEARANCE||'     '  "SC_SITE",
    PAX_SURNAME, INBOUND_BOARDING_POINT ".I" ,
    b.bag_tag, BAG_EXCEPTION||' ' "EXCEP",
    BAG_SOURCE_INDICATOR||'  ' "BSI",
    NVL (s2.APPROVED_SECURITY_STATUS,'not set'  ) "NON_APR",
    security_clearance_status||'  '  "SCS"
from bag b, flight f, pax p, site_info s1 ,  airport_configuration ac,
   site_info s2, batch_log bl
where p.pax_id = b.pax_id
 and f.flight_id = p.flight_id
 and f.site_id = s1.site_id
 and bl.tag3(+) = b.bag_tag3
 and bl.tag2b(+) = b.bag_tag2b
 --   and bl.flight_id(+) = f.flight_id
 and s1.airport_code = ac.airport_code(+)
 and s2.airport_code(+) = p.INBOUND_BOARDING_POINT
 order by f.flight_id
/
