PROMPT CREATE OR REPLACE PROCEDURE validate_uld_posn

CREATE OR REPLACE PROCEDURE validate_uld_posn
(p_fltid     IN     flight.flight_id%TYPE,
 p_uldno     IN     ulds.uld_no%TYPE,
 p_posn      IN     ulds.uld_hold_no%TYPE,
 p_user_id   IN     tagtrak_users.user_id%TYPE,
 p_retval       OUT number) AS

   l_agent_code    tagtrak_users.agent_code%TYPE;
   l_aircraft_type flight_info.aircraft_type%TYPE;
   l_carrier       flight.carrier_alpha_code%TYPE;
   l_count         NUMBER(6);
   l_double        NUMBER(1) := 0;
   l_flight_date   flight.flight_date%TYPE;
   l_flight_no     flight.flight_no%TYPE;
   l_fullvalid     NUMBER(6);
   l_on_off        VARCHAR2(1);
   l_site_id       site_info.site_id%TYPE;

BEGIN

   p_retval := 0;

   /** Go and get the carrier using the flight_id **/
   SELECT carrier_alpha_code, 
          flight_no, 
          flight_date, 
          site_id
   INTO l_carrier, 
        l_flight_no, 
        l_flight_date, 
        l_site_id
   FROM flight
   WHERE flight_id = p_fltid;

   /** Get the agent_code for the user **/
   SELECT agent_code
   INTO l_agent_code
   FROM tagtrak_users
   WHERE user_id = p_user_id;

   /** See if the position is a double one **/
   IF instr(p_posn, 'R', 1, 1) = 0
   THEN
      /** the position does not end with an R so now see if it ends with L **/

      IF instr(p_posn, 'L', 1, 1) = 0
      THEN
         l_double := 1;
      END IF;
   END IF;

   /** See if the airline has the ULD Position Validation facility switched on or off **/
   BEGIN
     SELECT config_value
     INTO   l_on_off
     FROM carrier_site_config cs,
          agents              ag
     WHERE cs.alpha_code = l_carrier
     AND   ag.agent_code = l_agent_code
     AND   cs.site_id    = ag.site_id
     AND   cs.config_item = 'ULD_VALIDATION';
   EXCEPTION
     -- CA00666 By default switch off the ULD Position Validation facility
     WHEN OTHERS THEN
       l_on_off := 'N';
   END;

   IF l_on_off = 'Y'
   THEN
      /** Check the ULD size and see if the position entered matches up.     **/
      /** If the ULD starts with 'DLF', 'DQF', 'ALE' or 'AQF' it is a double **/
      /** sized ULD and requires a two positions, otherwise it is single     **/
      /** sized an only needs one position.                                  **/

      IF instr (p_uldno, 'DLF', 1, 1) = 1
      OR instr (p_uldno, 'DQF', 1, 1) = 1
      OR instr (p_uldno, 'ALE', 1, 1) = 1
      OR instr (p_uldno, 'AQF', 1, 1) = 1
      THEN
         /** The ULD is double sized **/
         IF l_double != 1
         THEN
            /** The position specified is only a single one, so return an error **/
            p_retval := -1215;
            RETURN;
         END IF;
      ELSE
         /** The ULD is single sized **/
         IF l_double = 1
         THEN
            /** The position specified is a double one, so set the return status   **/
            /** to indicate this. Validation of the positions will continue though **/
            /** as the user can choose to override the warning message.            **/
            p_retval := -1214;
         END IF;
      END IF;

      /** ULD Position Validation is switched on so lets go and validate **/
      IF l_double = 1
      THEN
         /** Need to check the double number plus the individual 'L' and 'R' as well **/
         SELECT count(*)
         INTO l_count
         FROM ulds
         WHERE flight_id = p_fltid
         AND   trim(uld_hold_no) in (trim(p_posn), trim(p_posn)||'L', trim(p_posn)||'R');
      ELSE
         /** Single position, but still need to check the double type of posn **/
         SELECT count(*)
         INTO l_count
         FROM ulds
         WHERE flight_id = p_fltid
         AND   trim(uld_hold_no) in (trim(p_posn), substr(trim(p_posn), 1, length(trim(p_posn))-1));
      END IF;

      IF l_count > 0
      THEN
         /** 'Position already used' **/
         p_retval := -1211; 
         RETURN;
      END IF;

      /** Position has not already been used, so now we can validate it against a hold layout **/

      /** Need to check if user wishes the 'Full Validation Not Performed' message. **/
      SELECT count(*)
      INTO l_fullvalid
      FROM user_role_priv     urp,
           app_priv           ap,
           tagtrak_users_role tur
      WHERE ap.app_priv_description = 'SCAN - Full Validation'
      AND   tur.user_id             = p_user_id
      AND   urp.user_role_id        = tur.user_role_id
      AND   ap.app_priv_id          = urp.app_priv_id;

      /** Get the aircraft subtype from flight_info **/
      BEGIN
         SELECT fi.aircraft_type
         INTO l_aircraft_type
         FROM flight_info fi,
              site_info   si
         WHERE fi.flight_no    = l_flight_no
         AND   fi.flight_date  = l_flight_date
         AND   fi.airport_code = si.airport_code
         AND   si.site_id      = l_site_id;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         /** No aircraft type so we cannot validate against a hold layout **/
         IF l_fullvalid > 0
         THEN
            /** Full Validation message needed**/
            p_retval := -1212;
         ELSE
            /** No Full Validation message **/
            p_retval := 0;
         END IF;

         RETURN;
      END;

      /** We have a aircraft subtype, so see if we have a hold configuration **/

      SELECT count(*)
      INTO l_count
      FROM hold_config
      WHERE agent_code         = l_agent_code
      AND   carrier_alpha_code = l_carrier
      AND   aircraft_subtype    = l_aircraft_type;
 
      IF l_count = 0
      THEN
         /** No configuration found, try to see if there is a wild card one **/
         l_aircraft_type := substr(l_aircraft_type, 1, 2)||'*';
         SELECT count(*)
         INTO l_count
         FROM hold_config
         WHERE agent_code         = l_agent_code
         AND   carrier_alpha_code = l_carrier
         AND   aircraft_subtype   = l_aircraft_type;

         IF l_count = 0
         THEN
            /** No configuartion found so cannot do any further validation **/
            /** Set return value according to whether the user wants the Full Validation message displayed or not **/
            IF l_fullvalid > 0
            THEN
               /** Full Validation message needed**/
               p_retval := -1212;
            ELSE
               /** No Full Validation message **/
               p_retval := 0;
            END IF;

            RETURN;
         END IF;
      END IF;

      /** Configuration found, so now make sure position is valid **/
      IF l_double = 1
      THEN
         /** For double positions need to check both L and R **/

         SELECT count(*)
         INTO l_count
         FROM hold_config
         WHERE (agent_code         = l_agent_code
         AND    carrier_alpha_code = l_carrier
         AND    aircraft_subtype   = l_aircraft_type
         AND    trim(position)     = trim(p_posn)||'L')
         OR    (agent_code         = l_agent_code
         AND    carrier_alpha_code = l_carrier
         AND    aircraft_subtype   = l_aircraft_type
         AND    trim(position)     = trim(p_posn)||'R');

         IF l_count < 2
         THEN
            /** Both positions not found so position is invalid **/
            p_retval := -1213;
         END IF;
      ELSE
         SELECT count(*)
         INTO l_count
         FROM hold_config
         WHERE agent_code         = l_agent_code
         AND   carrier_alpha_code = l_carrier
         AND   aircraft_subtype   = l_aircraft_type
         AND   trim(position)     = trim(p_posn);

         IF l_count = 0
         THEN
            p_retval := -1213;
         END IF; 
      END IF;
   END IF;
EXCEPTION
WHEN others
THEN
   p_retval := SQLCODE;
END;
/
