/*****************************************************************************
*
* Filename         - $Workfile:   bag_manifest_totals.sql  $
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2003
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PACKAGE bag_manifest_totals

CREATE OR REPLACE
PACKAGE bag_manifest_totals AS
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                             *
  *  This package is used in conjunction with bag_manifest_totals_v1 (a view) and exists purely *
  *  as a method of providing counts of bags relating to a flight in view format.               *
  *  Each selected function returns a column in the view represented by the function name as    *
  *  follows:                                                                                   * 
  *                                                                                             *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

FUNCTION bags_total (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_accompanied (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER;
FUNCTION bags_unaccompanied (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 (bags_total, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_accompanied, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (bags_unaccompanied, WNDS, WNPS);

END bag_manifest_totals;
/
PROMPT CREATE OR REPLACE PACKAGE BODY bag_manifest_totals 

CREATE OR REPLACE
PACKAGE BODY bag_manifest_totals AS

FUNCTION bags_total (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
	v_bags_total NUMBER DEFAULT 0;
	BEGIN
		SELECT COUNT(*) INTO v_bags_total
		FROM load_v1
                WHERE flight_id = p_flight_id;
		RETURN NVL(v_bags_total,0);
	END bags_total;

FUNCTION bags_unaccompanied (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
	v_bags_unaccompanied NUMBER DEFAULT 0;
	BEGIN
		SELECT COUNT(*) INTO v_bags_unaccompanied
		FROM load_v1 
		WHERE bag_type = 'UNACCOMP'
		AND flight_id = p_flight_id;
		RETURN NVL(v_bags_unaccompanied,0);
	END bags_unaccompanied;

FUNCTION bags_accompanied (p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER IS
	v_bags_accompanied NUMBER DEFAULT 0;
	BEGIN
		SELECT bags_total(p_flight_id) - bags_unaccompanied(p_flight_id) INTO v_bags_accompanied from dual;
		RETURN NVL(v_bags_accompanied,0);
	END bags_accompanied;

END bag_manifest_totals;
/
