/*****************************************************************************
*
* Filename         - $Workfile:   load_bag.sql  $
*
*                     Name              Date      Version     CR Number
*                     Yomi Kogbe        8/8/2				1.7					15666
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - ULTRATRAK(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, 1997
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE load_bag 

CREATE OR REPLACE PROCEDURE load_bag (
	tag2a           IN  CHAR,
	tag2b           IN  CHAR,
	tag3            IN  NUMBER,
	pid             IN  NUMBER,
	uldno           IN  CHAR,
	p_location_code IN  CHAR,
	p_scanner_id    IN  CHAR,
	fltid           IN  NUMBER,
	retval          OUT NUMBER
) 
AS 
  bsn number(6);
  depair      pax.departure_airport_code%type := null;
  -- v_status    bag.security_clearance_status%type;
BEGIN
   depair := get_offset.find_airport_code(fltid,  pid);
	/*
	 * - Initialise Variables
	 * - Call the add_bag_to_uld procedure
	 * - IF the bsn is greater than 0 then (bsn obtained from the procedure
	 *    call to add_bag
	 *   IF tag2b is not equal to '-' then
	 *     Update the bag table using the bar code representation
	 *   ELSE
	 *     Update the bag table using the alpha code representation
	 * - ELSE
	 *   Assign -1013 to the return value
	 */
	bsn    := 0;
	retval := 0;

/************************************************************************************/
/************************************************************************************/
/************************************************************************************/
/*** This section of code has been removed as this check should not be performed  ***/
/*** here.  Checks for validity to load should be performed before calling this   ***/
/*** procedure.  Most of the calls to this procedure assume that after the call   ***/
/*** the bag will be loaded - this check breaks that assumption and causes        ***/
/*** multiple bugs as a result. (UAS/FALCP/CA/02124)                              ***/
/************************************************************************************/
/************************************************************************************/
/************************************************************************************/
--
--  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
--   * CR 15666                                                                * 
--   * Find the security_clearance_status and if the bag is Not cleared then   * 
--   * return an error_code (-1068) to the calling unit to indicate that the   * 
--   * bag has Not been cleared                                                *
--   *                                                                         * 
--   * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
--  SELECT 
--		security_clearance_status
--  INTO 
--		v_status
--  FROM 
--		bag
--  WHERE 
--		pax_id    = pid and 
--		bag_tag3  = tag3 and 
--		bag_tag2b = tag2b;
--      
--  IF (v_status = 'N') THEN
--    retval := -1068;
--  END IF;
/************************************************************************************/
/************************************************************************************/
/************************************************************************************/
/*** End of removed section of code                                               ***/
/************************************************************************************/
/************************************************************************************/
/************************************************************************************/
  
  IF (retval = 0) THEN
      /* CRs 15561, 15605
   * If the bag has not already been loaded then lock the row to
   * prevent another load of the same bag by a different transaction.
   * If the bag has been loaded then terminate processing and return
   * to the calling unit with an already loaded error (-1011).
   */
  BEGIN
    SELECT
      0
    INTO
      retval
    FROM
      bag
    WHERE
      bag_tag3 = tag3
    AND
      (bag_tag2a = NVL(tag2a,'-')
      OR
      bag_tag2b = NVL(tag2b,'-'))
    AND
      pax_id = pid
    AND
      delete_marker = 0
    AND
      uld_no IS NULL
    AND
      loaded_status <> 'Y'
    FOR UPDATE OF
      uld_no
      ,loaded_status;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      retval := -1011;
      RETURN;
  END;
  END IF;

IF (retval = 0) THEN 
	add_bag_to_uld ( fltid, uldno, bsn);
 
	IF (bsn > 0) THEN 
		IF (tag2b != '-') THEN 
			/* use bar code representation */
			UPDATE
				bag
			SET 
				uld_no          = uldno,
				loaded_status   = 'Y',
				bag_sequence_no = bsn,
				time_loaded     = SYSDATE + get_offset.airport_code(depair),
				location_code   = p_location_code,
				scanner_id      = p_scanner_id,
				uld_flight_id   = fltid
			WHERE
				bag_tag3      	= tag3  AND
				bag_tag2b     	= tag2b AND
				pax_id        	= pid   AND
				delete_marker 	= 0;
		ELSE 
			/* use alpha */
			UPDATE
				bag
			SET 
				uld_no          = uldno,
				loaded_status   = 'Y',
				bag_sequence_no = bsn,
				time_loaded     = SYSDATE + get_offset.airport_code(depair),
				location_code   = p_location_code,
				scanner_id      = p_scanner_id,
				uld_flight_id   = fltid
			WHERE
				bag_tag3      	= tag3  AND
				bag_tag2a     	= tag2a AND
				pax_id        	= pid   AND
				delete_marker 	= 0;
		END IF;
	ELSE
		/* uld error */
		retval := -1013;
	END IF;
END IF;
END load_bag;
/
