/*****************************************************************************
*
* Filename         - $Workfile:   pbc.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell  06/06/01    1.0       CR15443
*                     Martin Helliwell  09/04/02    1.1       CR15578
* 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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2001
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   pbc.sql  $  $Revision:   1.1  $
*
* 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:   /shared/pvcs/aisarch/db_ora/db/source/live/procedures/pbc.svl  $
*
*    Rev 1.1   12 Apr 2002 11:47:18   martin
* OR:
* CR: 15578
* REV: 1.1
* DESC: Fix pax_checked function
*
*    Rev 1.0   18 Jun 2001 14:22:28   tagtrak
* OR:
* CR: 15443
* REV: 1.0
* DESC: User Group Changes 2001
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PACKAGE pbc

CREATE OR REPLACE
PACKAGE pbc AS
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                             *
  *  This package is used in conjunction with pax_and_bag_count_v1 (a view) and exists purely   *
  *  as a method of providing counts of bags and passengers relating to a flight in view format.*
  *  Each selected function returns a column in the view represented by the function name as    *
        *  follows:                                                                                   *
  *                                                                                             *
        *               SQL> DESCRIBE pax_and_bag_count_v1                                                        *
        *               Name                            Null?    Type                                             *
        *               ------------------------------- -------- ----                                             *
        *               FLIGHT_ID                       NOT NULL NUMBER(10)                                       *
        *               PAX_CHECKED                              NUMBER                                           *
        *               PAX_BOARDED                              NUMBER                                           *
        *               PAX_NO_SHOW                              NUMBER                                           *
        *               BAGS_CHECKED                             NUMBER                                           *
        *               BAGS_LOADED                              NUMBER                                           *
        *               BAGS_SHORT                               NUMBER                                           *
        *               BAGS_REJECT                              NUMBER                                           *
        *               BAGS_NORMAL                              NUMBER                                           *
        *               BAGS_RUSH                                NUMBER                                           *
        *               BAGS_GATE                                NUMBER                                           *
        *               BAGS_CREW                                NUMBER                                           *
        *               BAGS_ALERTS                              NUMBER                                           *
        *               BAGS_STANDBY                             NUMBER                                           *
        *               BAGS_AIRCRAFT_SIDE                       NUMBER                                           *
        *               BAGS_LOCAL                               NUMBER                                           *
        *               BAGS_TRANSFER                            NUMBER                                           *
        *               BAGS_COMPANY_MAIL                        NUMBER                                           *
        *               BAGS_DIPLOMATIC                          NUMBER                                           *
  *                                                                                             *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

FUNCTION pax_checked (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION pax_boarded (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION pax_no_show (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_checked (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_loaded (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_short (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_normal (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_rush (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_gate (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_crew (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_local (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_transfer (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_aircraft_side (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_standby (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_alerts (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_crew_untagged (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_reject (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_company_mail (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_diplomatic (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;

 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                             *
  *  To execute an SQL SELECT that calls a stored function the RDBMS needs to verify the result.*
  *  A package body is invisible to SQL and the RDBMS is unable to verify that there are no     *
  *  harmful side effects from possible updates to the database.  The pragmas guarantee the     *
  *  purity level of the code and ensure that no tables are updated (WNDS) and no packaged      *
  *  variables are updated (WNPS) thus effectively denying all the functions any possibility of *
  *  updating the database to the satisfaction of the RDBMS.                                    *
  *                                                                                             *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

PRAGMA RESTRICT_REFERENCES (pax_checked, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (pax_boarded, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (pax_no_show, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_checked, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_loaded, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_short, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_normal, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_rush, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_gate, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_crew, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_local, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_transfer, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_aircraft_side, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_standby, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_alerts, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_crew_untagged, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_reject, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_company_mail, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_diplomatic, WNDS, WNPS);

END pbc;
/
PROMPT CREATE OR REPLACE PACKAGE BODY pbc 

CREATE OR REPLACE
PACKAGE BODY pbc AS

FUNCTION pax_checked (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_pax_checked NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_pax_checked
                FROM pax p, bag_exception_types t
                WHERE delete_marker = 0
								and p.exception_type_id = t.BAG_EXCEP_ID(+)
                AND pax_status in ('C','B')
								AND (t.INCLUDE_IN_MWS_PAX_LIST = 'Y'  OR p.exception_type_id is null)
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_pax_checked,0);
        END pax_checked;

FUNCTION pax_boarded (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_pax_boarded NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_pax_boarded
                FROM pax p, bag_exception_types t
                WHERE pax_status = 'B'
								and p.exception_type_id = t.BAG_EXCEP_ID(+)
								AND (t.INCLUDE_IN_MWS_PAX_LIST = 'Y'  OR p.exception_type_id is null)
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_pax_boarded,0);
        END pax_boarded;

FUNCTION pax_no_show (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_pax_no_show NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_pax_no_show
                FROM pax p, bag_exception_types t
                WHERE pax_status <> 'B'
								and p.exception_type_id = t.BAG_EXCEP_ID(+)
								AND (t.INCLUDE_IN_MWS_PAX_LIST = 'Y'  OR p.exception_type_id is null)
                AND delete_marker = 0
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_pax_no_show,0);
        END pax_no_show;

FUNCTION bags_checked (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_checked NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_checked
                FROM bag b
                ,pax p
                WHERE b.delete_marker = 0
                AND p.pax_status IN ('C','B')
                AND p.pax_sequence_no != '-1'    -- don't include unknown bags
                AND b.authority_to_load_dcs = 'Y'
                AND b.pax_id = p.pax_id
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_checked,0);
        END bags_checked;

FUNCTION bags_loaded (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_loaded NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_loaded
                FROM bag b
                ,pax p
                WHERE b.loaded_status = 'Y'
                AND b.pax_id = p.pax_id
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_loaded,0);
        END bags_loaded;

FUNCTION bags_short (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_short NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_short
                FROM bag b
                ,pax p
                WHERE b.delete_marker = 0
                AND p.pax_status IN ('C','B')
                AND b.authority_to_load_dcs = 'Y'
                AND b.loaded_status <> 'Y'
                AND b.pax_id = p.pax_id 
                AND p_flight_id = p.flight_id
                AND p.pax_sequence_no <> -1;
                RETURN NVL(v_bags_short,0);
        END bags_short;

FUNCTION bags_normal (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_normal NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_normal
                FROM bag b
                ,pax p
                WHERE b.delete_marker = 0
                AND p.pax_status IN ('C','B')
                AND b.authority_to_load_dcs = 'Y'
                AND b.bag_exception is null
                AND b.pax_id = p.pax_id 
                AND p_flight_id = p.flight_id
                AND p.pax_sequence_no <> -1;
                RETURN NVL(v_bags_normal,0);
        END bags_normal;

FUNCTION bags_rush (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_rush NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_rush
                FROM bag b
                ,pax p
                WHERE b.delete_marker = 0
                AND p.pax_status IN ('C','B')
                AND b.authority_to_load_dcs = 'Y'
                AND b.bag_exception = 'RUSH'
                AND b.pax_id = p.pax_id 
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_rush,0);
        END bags_rush;

FUNCTION bags_gate (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_gate NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_gate
                FROM bag b
                ,pax p
                WHERE b.delete_marker = 0
                AND p.pax_status IN ('C','B')
                AND b.authority_to_load_dcs = 'Y'
                AND b.bag_exception = 'GATE'
                AND b.pax_id = p.pax_id 
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_gate,0);
        END bags_gate;

FUNCTION bags_crew (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_crew NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_crew
                FROM bag b
                ,pax p
                WHERE b.delete_marker = 0
                AND p.pax_status IN ('C','B')
                AND b.authority_to_load_dcs = 'Y'
                AND b.bag_exception = 'CREW'
                AND b.pax_id = p.pax_id 
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_crew,0);
        END bags_crew;

FUNCTION bags_local (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_local NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_local
                FROM bag b
                ,pax p
                WHERE b.delete_marker = 0
                AND p.pax_status IN ('C','B')
                AND b.authority_to_load_dcs = 'Y'
                AND b.bag_exception IS NULL
                AND bag_source_indicator = 'L'
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_local,0);
        END bags_local;

FUNCTION bags_transfer (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_transfer NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_transfer
                FROM bag b
                ,pax p
                WHERE b.delete_marker = 0
                AND p.pax_status IN ('C','B')
                AND b.authority_to_load_dcs = 'Y'
                AND b.bag_exception IS NULL
                AND bag_source_indicator = 'T'
                AND b.pax_id = p.pax_id 
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_transfer,0);
        END bags_transfer;

FUNCTION bags_aircraft_side (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_aircraft_side NUMBER DEFAULT 0;
        BEGIN
                SELECT SUM(uld_no_of_bags) INTO v_bags_aircraft_side
                FROM ulds u
                WHERE uld_loaded_status = 'Y'
                AND p_flight_id = u.flight_id;
                RETURN NVL(v_bags_aircraft_side,0);
        END bags_aircraft_side;

FUNCTION bags_standby (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_standby NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_standby
                FROM bag b
                ,pax p
                ,flight f
                WHERE p.pax_status = 'S'
                AND b.pax_id = p.pax_id
                AND p.flight_id = f.flight_id 
                AND b.delete_marker = 0
                AND p_flight_id = f.flight_id;
                RETURN NVL(v_bags_standby,0);
        END bags_standby;

FUNCTION bags_alerts (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_alerts NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_alerts
                FROM pax p
                ,bag_alert ba
                ,bag b
                WHERE ba.alert_on_action IN ('UNLOAD    ', 'UNKNOWN   ')
                AND ba.pax_id =  b.pax_id
                AND ba.bag_tag = b.bag_tag
                AND p.pax_id = b.pax_id
                AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_alerts,0);
        END bags_alerts;

FUNCTION bags_crew_untagged (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_crew_untagged NUMBER DEFAULT 0;
        BEGIN
                SELECT SUM(p.no_of_bags) INTO v_bags_crew_untagged
                FROM pax p,
                     bag_exception_types bet
                WHERE p.exception_type_id = bet.bag_excep_id
                  AND p.pax_id NOT IN
                        (SELECT b.pax_id
                           FROM bag b,
                                pax p2,
                                bag_exception_types bet2
                          WHERE b.pax_id = p2.pax_id
                            AND p2.exception_type_id = bet2.bag_excep_id
                            AND bet2.bag_excep_code = 'CREW'
                            AND p2.flight_id = p_flight_id)
                  AND bet.bag_excep_code = 'CREW'
                  AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_crew_untagged,0);
        END bags_crew_untagged;

FUNCTION bags_reject (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_reject NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_reject
                FROM bag_trace
                WHERE error_id < 0
                AND p_flight_id = flight_id_scan;
                RETURN NVL(v_bags_reject,0);
        END bags_reject;

FUNCTION bags_company_mail (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_company_mail NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_company_mail
                FROM bag b,
                     pax p
                WHERE b.delete_marker = 0
                  AND p.pax_status IN ('C','B')
                  AND b.authority_to_load_dcs = 'Y'
                  AND b.bag_exception = 'MAIL'
                  AND b.pax_id = p.pax_id 
                  AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_company_mail,0);
        END bags_company_mail;

FUNCTION bags_diplomatic (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
        v_bags_diplomatic NUMBER DEFAULT 0;
        BEGIN
                SELECT COUNT(*) INTO v_bags_diplomatic
                FROM bag b,
                     pax p
                WHERE b.delete_marker = 0
                  AND p.pax_status IN ('C','B')
                  AND b.authority_to_load_dcs = 'Y'
                  AND b.bag_exception = 'DIPL'
                  AND b.pax_id = p.pax_id 
                  AND p_flight_id = p.flight_id;
                RETURN NVL(v_bags_diplomatic,0);
        END bags_diplomatic;

END pbc;
/
