/*****************************************************************************
*
* Filename         - $Workfile:   mail_pack.sql  $
*
*                     Name              Date      Version     CR Number
* 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 Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT create or replace package mail_pack

CREATE OR REPLACE package mail_pack
is

	/*
	 * - Declare Procedures and functions
	 */

	/*
	 * get_counts:- Return internal mail package counts - provided
	 * for diagnostic purposes only.
	 */
	procedure get_counts (
		p_indivmailtypetabr  out number,
		p_indivmailtypetabp  out number,
		p_globalmailtypetabp out number,
		p_globalmailtypetabr out number,
		p_indivmailtabr      out number,
		p_indivmailtabp      out number,
		p_globalmailtabr     out number,
		p_globalmailtabp     out number,
		p_indivmailcounttab  out number,
		p_globalmailcounttab out number,
		p_allmailcounttab    out number
	);

	/*
		 Returns a mail message to the calling process if available
	*/
	procedure mail_look (
		itype  in      char,    /* Type of message required '-' if all */
		imode  in      char,    /* P(eek) or R(ead) - Peek is non destructive */
		scope  in      char ,   /* G(lobal) or I(ndividual) */
		retval in out  number,  /* Either the 0 or a -ve Error Number */
		mid    in out  number,  /* The message id returned (if applicable) */
		mtype  in out  char,    /* The type of message returned */
		text   in out  varchar2
	); /* The text of the message returned */

	/*
		 Registers Interest in Mail for an Instance of a process
	*/
	procedure start_mail (
		proc char, /* Process Id of Instance */
		unit char  /* Unit Id of Instance */
	);

	/*
		 Cancels Interest in Mail for an Instance of a process
	*/
	procedure cancel_mail;

	/*
		 Stores a mail message
	*/
	procedure store_mail (
		addr     in  char,       /* Destination Address of Instance */
		itype    in  char,       /* Type of Message */
		text     in  varchar,    /* Text of Message */
		msgcount in  smallint,   /* Number of receipients of Message */
		scope    in  char,       /* G(lobal) or I(ndividual) */
		retval   out number      /* Either the message id or a -ve Error No */
	);

	/*
		Returns 1 if mail is available
	*/
	function poll_mail return number;

	/*
		Returns the latest message id of the request classifcation of message
	*/
	procedure get_mail_counts (
		scope in      char,        /* A(ll) or G(lobal) or I(ndividual) */
		mid   in out  number       /* Message ID */
	);

	/*
		Returns the counts of Individual, Global, and Broadcast messages since
		the last call.
	*/
	procedure get_new_mail_counts (
		icount in out integer, /* Individual Messages */
		gcount in out integer, /* Global Messages */
		acount in out integer  /* Broadcast Messages */
	);


	/*
		The following procedures are for use by the DCS gateway only
	*/
	/* Called shortly after gateway startup */
	PROCEDURE gw_mail_reset (p_proc IN CHAR, p_unit IN CHAR);

        /* Called before entering the mail loop */
	PROCEDURE gw_mail_batch_start;

        /* Retrieve a mail from the database */
	PROCEDURE gw_mail_peek (p_retval  OUT NUMBER,
				p_msgid   OUT NUMBER,
				p_msgtyp  OUT VARCHAR2,
				p_msgtxt  OUT VARCHAR2
				);

        /* Called in the event of a Put_Msg failure */
	PROCEDURE gw_mail_error (p_msgid IN NUMBER);

        /* Called in the event of a message translation failure */
        PROCEDURE gw_mail_reject (p_msgid IN NUMBER);

/*used to correct unprocessed mail_indiv entries for a flight if flight details have changed*/
PROCEDURE correct_mail_indiv_bpms(p_flight_id        IN flight.flight_id%TYPE,
                                  p_old_process_name IN flight.process_name%TYPE,
                                  p_old_unit_name    IN flight.unit_name%TYPE,
                                  p_new_process_name IN flight.process_name%TYPE,
                                  p_new_unit_name    IN flight.unit_name%TYPE,
                                  p_new_dcsid        IN flight.dcsid%TYPE,
                                  p_new_subunit_name IN flight.subunit_name%TYPE);

end mail_pack;
/

CREATE OR REPLACE package body mail_pack is

/* This package relies on the fact that an instance of a process stays
   connected to the database throughout its life.  It uses persistent PL/SQL
   variables to hold control information.  The values of these variables
   are lost when the instance disconnects from the database.  The specified
   behaviour of the routine at startup is to start at the first individual
   message available and the latest global and broadcast messages.

   Messages are returned based on their timestamp.  A message is assumed to be
   new if its timestamp is greater than or equal to the last message returned
   and it is not contained in one of the tables below.  A routine can
   either request messages by type or messages of any type.

   The following tables are used to ensure duplicate messages are not returned
   to the calling process. 4 tables are maintained.

   GlobalMailTypeTabP/R is a table of all mail types returned since the last
   start_mail was issued and the timestamp of the latest message of that
   type returned.  P for peeked records, R for read records.

   GlobalMailTabP/R is a table of latest global and broadcast messages returned
   to the instance.  If the last global mail request was for any type of
   message this table will hold messages of identical timestamps.  If the
   last global mail request was for a specific type of message this table
   will hold messages of that type with identical timestamps and messages
   of other types reflecting the timestamp of the last type specific or
   type any request. P for peeked records, R for read records.

   IndivMailTypeTabP/R and IndivMailTabP/R are the equivalents for Indivdual
   Mail requests.

*/

	type GlobalMailMsgRec is record (
		MailMsgId      mail_global.global_msg_id%type,
		MailMsgType    mail_global.msg_type%type,
		MailMsgTime    mail_global.msg_time%type
	 );

	type GlobalMailTabTyp is table OF GlobalMailMsgRec
		 index by binary_integer;

	GlobalMailTabP GlobalMailTabTyp;
	GlobalMailTabR GlobalMailTabTyp;

	type IndivMailMsgRec is record (
		MailMsgId     mail_indiv.indiv_msg_id%type,
		MailMsgType   mail_indiv.msg_type%type,
		MailMsgTime   mail_indiv.msg_time%type
	 );

	type IndivMailTabTyp is table of IndivMailMsgRec
		 index by binary_integer;

	IndivMailTabR IndivMailTabTyp;
	IndivMailTabP IndivMailTabTyp;

	type GlobalMailTypeRec is record (
		MailMsgType mail_global.msg_type%type,
		MailMsgTime mail_global.msg_time%type
	 );

	type GlobalMailTypeTabTyp is table of GlobalMailTypeRec
		 index by binary_integer;

	GlobalMailTypeTabR GlobalMailTypeTabTyp;
	GlobalMailTypeTabP GlobalMailTypeTabTyp;

	type IndivMailTypeRec is record (
		MailMsgType    mail_indiv.msg_type%type,
		MailMsgTime    mail_indiv.msg_time%type
	);

	type IndivMailTypeTabTyp is table of IndivMailTypeRec
		 index by binary_integer;

	IndivMailTypeTabR IndivMailTypeTabTyp;
	IndivMailTypeTabP IndivMailTypeTabTyp;


	/* The timestamp of the lastest message returned by an any type request */

	GlobalMailTimeP mail_global.msg_time%type := to_date('01/01/1901','DD/MM/YYYY');
	GlobalMailTimeR mail_global.msg_time%type := to_date('01/01/1901','DD/MM/YYYY');
	IndivMailTimeP  mail_indiv.msg_time%type  := to_date('01/01/1901','DD/MM/YYYY');
	IndivMailTimeR  mail_indiv.msg_time%type  := to_date('01/01/1901','DD/MM/YYYY');


	/* Tables for Message Counting
		 These tables contain the records of the latest messages returned that
		 have an identical timestamp.  They reflect the three classifcations of
		 message.
	*/

	IndivMailCountTab    IndivMailTabTyp;
	GlobalMailCountTab   GlobalMailTabTyp;
	AllMailCountTab      GlobalMailTabTyp;

	IndivMailCountTime  mail_indiv.msg_time%type  :=
		to_date('01/01/1901','DD/MM/YYYY');

	GlobalMailCountTime mail_global.msg_time%type :=
		to_date('01/01/1901','DD/MM/YYYY');

	AllMailCountTime mail_global.msg_time%type    :=
		to_date('01/01/1901','DD/MM/YYYY');

	allmail_address mail_global.msg_address%type := 'ALL';
	globalmail_address mail_global.msg_address%type;
	indivmail_address  mail_indiv.msg_address%type;

	/* Variables for DCS gateway functions
		-- Cursor for messages to be sent.
		-- This query only returns messages that:
		--   Are for our process
                --   Haven't already failed (send_status = 'E')
		--   Are not for interfaces that have messages outstanding
		--   Are not for interfaces that have failed.
	*/
	CURSOR l_mail_cursor IS SELECT * FROM mail_indiv
	   WHERE msg_address = indivmail_address AND
                 send_status IS NULL AND
	         address_stamp NOT IN (SELECT DISTINCT address_stamp FROM mail_indiv WHERE
	            msg_address = indivmail_address AND NVL(send_status,'N') = 'Y') AND
	         address_stamp NOT IN (SELECT DISTINCT subunit_name FROM system_cfg WHERE
	            PROCESS_name = 'DCS' and nvl(retry_status,'-1') != 0)
	   ORDER BY msg_time, indiv_msg_id;

	v_mail_cursor_open BOOLEAN := FALSE;



	/*
		 Returns the table index for the passed type if already stored or 0 if
		 not
	*/
	function get_globaltypeid (
		pmode char,
		ptype char
	)
		return number is

		counter number;
	begin

		/*
		 * - IF the pmode is equal to 'P' THEN
		 *   IF GlobalMailTypeTabP.Count is greater than 0 THEN
		 *     For counter from 1 to GlobalMailTypeTabp.last LOOP
		 *       IF ptype = GlobalMailTypeTabP(counter).MailMsgType THEN
		 *         Return the counter variable
		 *     END LOOP
		 *   Return 0
		 * - ELSE
		 *   IF GlobalMailTypeTabR.Count is greater than 0 THEN
		 *     For counter from 1 to GlobalMailTypeTabR.last LOOP
		 *       IF ptype = GlobalMailTypeTabR(counter).MailMsgType THEN
		 *         Return the counter variable
		 *     END LOOP
		 *   Return 0
		 */

		if pmode = 'P' then
			if GlobalMailTypeTabP.COUNT > 0 then
				for counter in 1..GlobalMailTypeTabP.LAST loop
					if ptype = GlobalMailTypeTabP(counter).MailMsgType then
						return counter;
					end if;
				end loop;
			end if;

			return 0;
		else
			if GlobalMailTypeTabR.COUNT > 0 then
				for counter in 1..GlobalMailTypeTabR.LAST loop
					if ptype = GlobalMailTypeTabR(counter).MailMsgType then
						return counter;
					end if;
				end loop;
			end if;

			return 0;
		end if;
	end;


	/*
		 Returns true if the message id passed has not already been sent
	*/
	function globalidnotsent (
		pmode char,
		id    number,
		ptype char,
		time  mail_global.msg_time%type
	)
		return boolean is

		counter number;
		typeid  number;

	begin

		/*
		 * - Get the typeid by calling the get_globaltypeid procedure
		 * - IF the typeid is equal to 0 THEN
		 *   Return True
		 * - IF the pmode is equal to 'P' THEN
		 *   IF time is less than GlobalMailTypeTabP(typeid).MailMsgTime THEN
		 *     Return False
		 *   IF GlobalMailTabP.count is greater than 0 THEN
		 *     FOR counter from 1 to GlobalMailTabP.last LOOP
		 *       IF GlobalMailTabP.exists(counter) THEN
		 *         IF id = GlobalMailTabP(counter).MailMsgID THEN
		 *           Return False
		 *     END LOOP
		 *   Return True
		 * - ELSE
	 	 *   IF time is less than GlobalMailTypeTabR(typeid).MailMsgTime THEN
		 *     Return False
		 *   IF GlobalMailTabR.count is greater than 0 THEN
		 *     FOR counter from 1 to GlobalMailTabR.last LOOP
		 *       IF GlobalMailTabR.exists(counter) THEN
		 *         IF id = GlobalMailTabR(counter).MailMsgID THEN
		 *           Return False
		 *     END LOOP
		 *   Return True
		 */

		typeid := get_globaltypeid (pmode, ptype);

		if typeid = 0 then
			return true;
		end if;

		if pmode = 'P' then
			if time < GlobalMailTypeTabP(typeid).MailMsgTime then
				return false;
			end if;

			if GlobalMailTabP.count > 0 then
				for counter in 1..GlobalMailTabP.last loop
					if GlobalMailTabP.exists(counter) then
						if id = GlobalMailTabP(counter).MailMsgID then
							return false;
						end if;
					end if;
				end loop;
			end if;

			return true;
		else
			if time < GlobalMailTypeTabR(typeid).MailMsgTime then
				return false;
			end if;

			if GlobalMailTabR.count > 0 then
				for counter in 1..GlobalMailTabR.last loop
					if GlobalMailTabR.exists(counter) then
						if id = GlobalMailTabR(counter).MailMsgID then
							return false;
						end if;
					end if;
				end loop;
			end if;

			return true;
		end if;
	end;

	/*
	 * Return internal mail package counts - provided for diagnostic
	 * purposes only.
	 */
	procedure get_counts (
		p_indivmailtypetabr  out number,
		p_indivmailtypetabp  out number,
		p_globalmailtypetabp out number,
		p_globalmailtypetabr out number,
		p_indivmailtabr      out number,
		p_indivmailtabp      out number,
		p_globalmailtabr     out number,
		p_globalmailtabp     out number,
		p_indivmailcounttab  out number,
		p_globalmailcounttab out number,
		p_allmailcounttab    out number
	)
	is

	begin

		/*
		 * - Initialise variables by copying variables
		 */

		p_indivmailtypetabr  := IndivMailTypeTabR.count;
		p_indivmailtypetabp  := IndivMailTypeTabP.count;
		p_globalmailtypetabp := GlobalMailTypeTabP.count;
		p_globalmailtypetabr := GlobalMailTypeTabR.count;
		p_indivmailtabr      := IndivMailTabR.count;
		p_indivmailtabp      := IndivMailTabP.count;
		p_globalmailtabr     := GlobalMailTabR.count;
		p_globalmailtabp     := GlobalMailTabP.count;
		p_indivmailcounttab  := IndivMailCountTab.count;
		p_globalmailcounttab := GlobalMailCountTab.count;
		p_allmailcounttab    := AllMailCountTab.count;
	end;


	/*
		 Stores a mail record in the global mail table after deleting old mail
		 records.
	*/
	procedure store_globalmail_record (
		pmode char,
		id    number,
		otype char,
		mtime mail_global.msg_time%type,
		itype char
	)

	is

		counter number;
		typid   number;
		recno   number;

	begin

		/*
		 * - IF pmode is equal to 'P' THEN
		 *   IF GlobalMailTabP.count is greater than 0 THEN
		 *     FOR counter from 1 to GlobalMailTabP.last LOOP
		 *       IF GlobalMailTabP.exists(counter) THEN
		 *         IF GlobalMailTabP(counter).MailMsgType is equal to otype or
		 *          itype is equal to '-' and GlobalMailTabP(counter).MailMsgTime
		 *          is less than mtime THEN
		 *           GlobalMailTabP.delete(counter)
		 *     END LOOP
		 *   Get the typid by calling the get_globaltypeid procedure
		 *   IF the typid is equal to 0 THEN
		 *     IF GlobalMailTypeTabP.count is equal to 0 THEN
		 *       Assign 1 to typid
		 *     ELSE
		 *       Assign GlobalMailTypeTabP.last + 1 to the typid variable
		 *     Assign mtime to the GlobalMailTypeTabP(typid).MailMsgTime
		 *     IF GlobalMailTabP.count is equal to 0 THEN
		 *       Assign 1 to recno
		 *     ELSE
		 *       Assign GlobalMailTabP.last + 1 to the recno variable
		 *     Initialise the MailMsgId, MailMsgType and MailMsgTime variables
		 * - ELSE
		 *   IF GlobalMailTabR.count is greater than 0 THEN
		 *     FOR counter from 1 to GlobalMailTabR.last LOOP
		 *       IF GlobalMailTabR.exists(counter) THEN
		 *         IF GlobalMailTabR(counter).MailMsgType is equal to otype or
		 *          itype is equal to '-' and GlobalMailTabR(counter).MailMsgTime
		 *          is less than mtime THEN
		 *           GlobalMailTabR.delete(counter)
		 *     END LOOP
		 *   Get the typid by calling the get_globaltypeid procedure
		 *   IF the typid is equal to 0 THEN
		 *     IF GlobalMailTypeTabR.count is equal to 0 THEN
		 *       Assign 1 to typid
		 *     ELSE
		 *       Assign GlobalMailTypeTabR.last + 1 to the typid variable
		 *     Assign mtime to the GlobalMailTypeTabR(typid).MailMsgTime
		 *     IF GlobalMailTabR.count is equal to 0 THEN
		 *       Assign 1 to recno
		 *     ELSE
		 *       Assign GlobalMailTabR.last + 1 to the recno variable
		 *     Initialise the MailMsgId, MailMsgType and MailMsgTime variables
		 */

		if pmode = 'P' then
			if GlobalMailTabP.count > 0 then
				for counter in 1..GlobalMailTabP.last loop
					if GlobalMailTabP.exists(counter) then
						if (GlobalMailTabP(counter).MailMsgType = otype or
								itype = '-')                                and
								GlobalMailTabP(counter).MailMsgTime < mtime then
							GlobalMailTabP.delete(counter);
						end if;
					end if;
				end loop;
			end if;

			typid := get_globaltypeid (pmode, otype);

			if typid = 0 then
				if GlobalMailTypeTabP.count = 0 then
					typid := 1;
				else
					typid := GlobalMailTypeTabP.last + 1;
				end if;

				GlobalMailTypeTabP(typid).MailMsgType := otype;
			end if;

			GlobalMailTypeTabP(typid).MailMsgTime := mtime;

			if GlobalMailTabP.count = 0 then
				recno := 1;
			else
				recno := GlobalMailTabP.last + 1;
			end if;

			GlobalMailTabP(recno).MailMsgId   := id;
			GlobalMailTabP(recno).MailMsgType := otype;
			GlobalMailTabP(recno).MailMsgTime := mtime;
		else
			if GlobalMailTabR.count > 0 then
				for counter in 1..GlobalMailTabR.last loop
					if GlobalMailTabR.exists(counter) then
						if (GlobalMailTabR(counter).MailMsgType = otype or
								itype = '-')                                and
								GlobalMailTabR(counter).MailMsgTime < mtime then
							GlobalMailTabR.delete(counter);
						end if;
					end if;
				end loop;
			end if;

			typid := get_globaltypeid (pmode, otype);

			if typid = 0 then
				if GlobalMailTypeTabR.count = 0 then
					typid := 1;
				else
					typid := GlobalMailTypeTabR.last + 1;
				end if;

				GlobalMailTypeTabR(typid).MailMsgType := otype;
			end if;

			GlobalMailTypeTabR(typid).MailMsgTime := mtime;

			if GlobalMailTabR.count = 0 then
				recno := 1;
			else
				recno := GlobalMailTabR.last + 1;
			end if;

			GlobalMailTabR(recno).MailMsgId   := id;
			GlobalMailTabR(recno).MailMsgType := otype;
			GlobalMailTabR(recno).MailMsgTime := mtime;
		end if;
	end;


	/*
		 Returns the table index for the passed type if already stored or 0 if
		 not
	*/
	function get_indivtypeid (
		pmode char,
		ptype char
	) return number is

		counter number;

	begin

		/*
		 * - IF pmode is equal to 'P' THEN
		 *   IF IndivMailTypeTabP.count is greater than 0 THEN
		 *     FOR counter from 1 to IndivMailTypeTabP.last LOOP
		 *       IF ptype is equal to IndivMailTypeTabP(counter).MailMsgType THEN
		 *         Return counter
		 *     END LOOP
		 *   Retrun 0
		 * - ELSE
		 *   IF IndivMailTypeTabR.count is greater than 0 THEN
		 *     FOR counter from 1 to IndivMailTypeTabR.last LOOP
		 *       IF ptype is equal to IndivMailTypeTabR(counter).MailMsgType THEN
		 *         Return counter
		 *     END LOOP
		 *   Retrun 0
		 */

		if pmode = 'P' then
			if IndivMailTypeTabP.count > 0 then
				for counter in 1..IndivMailTypeTabP.last loop
					if ptype = IndivMailTypeTabP(counter).MailMsgType then
						return counter;
					end if;
				end loop;
			end if;

			return 0;
		else
			if IndivMailTypeTabR.count > 0 then
				for counter in 1..IndivMailTypeTabR.last loop
					if ptype = IndivMailTypeTabR(counter).MailMsgType then
						return counter;
					end if;
				end loop;
			end if;

			return 0;
		end if;
	end;


	/*
		 Returns true if the message id passed has not already been sent
	*/
	function individnotsent (
		pmode char,
		id number,
		ptype char,
		time mail_indiv.msg_time%type
	) return boolean
	is
		counter number;
		typeid number;
	begin

		/*
		 * - Get the typeid by calling the get_indivtypeid function
		 * - IF typeid is equal to 0 THEN
		 *   Return true
		 * - IF pmode is equal to 'P' THEN
		 *   IF time is less than IndivMailTypeTabP(typeid).MailMsgTime THEN
		 *     Return False
		 *   IF IndivMailTabP.count is greater than 0 THEN
		 *     FOR counter from 1 to IndivMailTabP.last LOOP
		 *       IF IndivMailTabP.exists(counter) THEN
		 *         IF id is equal to IndivMailTabP(counter).MailMsgID THEN
		 *           Return False
		 *     END LOOP
		 *   Return True
		 * - ELSE
		 *   IF time is less than IndivMailTypeTabR(typeid).MailMsgTime THEN
		 *     Return False
		 *   IF IndivMailTabR.count is greater than 0 THEN
		 *     FOR counter from 1 to IndivMailTabR.last LOOP
		 *       IF IndivMailTabR.exists(counter) THEN
		 *         IF id is equal to IndivMailTabR(counter).MailMsgID THEN
		 *           Return False
		 *     END LOOP
		 *   Return True
		 */

		typeid := get_indivtypeid(pmode,ptype);

		if typeid = 0 then
			return true;
		end if;

		if pmode = 'P' then
			if time < IndivMailTypeTabP(typeid).MailMsgTime then
				return false;
			end if;

			if IndivMailTabP.count > 0 then
				for counter in 1..IndivMailTabP.last loop
					if IndivMailTabP.exists(counter) then
						if id = IndivMailTabP(counter).MailMsgID then
							return false;
						end if;
					end if;
				end loop;
			end if;

			return true;
		else
			if time < IndivMailTypeTabR(typeid).MailMsgTime then
				return false;
			end if;

			if IndivMailTabR.count > 0 then
				for counter in 1..IndivMailTabR.last loop
					if IndivMailTabR.exists(counter) then
						if id = IndivMailTabR(counter).MailMsgID then
							return false;
						end if;
					end if;
				end loop;
			end if;

			return true;
		end if;
	end;


	/*
		 Stores a mail record in the individual mail table after deleting old mail
		 records.
	*/
	procedure store_indivmail_record (
		pmode   char,
		id      number,
		otype   char,
		mtime   mail_indiv.msg_time%type,
		itype   char
	)
	is
		counter number;
		typid   number;
		recno   number;
	begin

		/*
		 * - IF pmode is equal to 'P' THEN
		 *   IF IndivMailTabP.count is greater than 0 THEN
		 *     FOR counter from 1 to IndivMailTabP.last LOOP
		 *       IF IndivMailTabP.exists(counter) THEN
		 *         IF (IndivMailTabP(counter).MailMsgType is equal to otype or
		 *          itype is equal to '-') and IndivMailTabP(counter).MailMsgTime
		 *          is less than mtime THEN
		 *           IndivMailTabP.delete(counter)
		 *     END LOOP
		 *   Get the type id by calling the get_indivtypeid function
		 *   IF the type id is equal to 0 THEN
		 *     IF IndivMailTypeTabP.count is equal to 0 THEN
		 *       Assign 1 to Type id
		 *     ELSE
		 *       Assign IndivMailTypeTabP.last + 1 to type id
		 *     Assign otype to IndivMailTypeTabP(typid).MailMsgTime
		 *     IF IndivMailTabP.count is equal to 0 THEN
		 *       Assign 1 to recno
		 *     ELSE
		 *       Assign IndivMailTabP.last + 1 to recno
		 *     Assign MailMsgId, MailMsg_type and MailMsgTime
		 * - ELSE
		 *   IF IndivMailTabR.count is greater than 0 THEN
		 *     FOR counter from 1 to IndivMailTabR.last LOOP
		 *       IF IndivMailTabR.exists(counter) THEN
		 *         IF (IndivMailTabR(counter).MailMsgType is equal to otype or
		 *          itype is equal to '-') and IndivMailTabR(counter).MailMsgTime
		 *          is less than mtime THEN
		 *           IndivMailTabR.delete(counter)
		 *     END LOOP
		 *   Get the type id by calling the get_indivtypeid function
		 *   IF the type id is equal to 0 THEN
		 *     IF IndivMailTypeTabR.count is equal to 0 THEN
		 *       Assign 1 to Type id
		 *     ELSE
		 *       Assign IndivMailTypeTabR.last + 1 to type id
		 *     Assign otype to IndivMailTypeTabR(typid).MailMsgTime
		 *   Assign mtime to IndivMailTypeTabR(typid).MailMsgTime
		 *   IF IndivMailTabR.count is equal to 0 THEN
		 *     Assign 1 to recno
		 *   ELSE
		 *     Assign IndivMailTabR.last + 1 to recno
		 *   Assign MailMsgId, MailMsg_type and MailMsgTime
		 */

		if pmode = 'P' then
			if IndivMailTabP.count > 0 then
				for counter in 1..IndivMailTabP.last loop
					if IndivMailTabP.exists(counter) then
						if (IndivMailTabP(counter).MailMsgType = otype or
								itype = '-')                               and
								IndivMailTabP(counter).MailMsgTime < mtime then
							IndivMailTabP.delete(counter);
						end if;
					end if;
				end loop;
			end if;

			typid := get_indivtypeid (pmode, otype);

			if typid = 0 then
				if IndivMailTypeTabP.count = 0 then
					typid := 1;
				else
					typid := IndivMailTypeTabP.last + 1;
				end if;

				IndivMailTypeTabP(typid).MailMsgType := otype;
			end if;

			IndivMailTypeTabP(typid).MailMsgTime := mtime;

			if IndivMailTabP.count = 0 then
				recno := 1;
			else
				recno := IndivMailTabP.last + 1;
			end if;

			IndivMailTabP(recno).MailMsgId   := id;
			IndivMailTabP(recno).MailMsgType := otype;
			IndivMailTabP(recno).MailMsgTime := mtime;
		else
			if IndivMailTabR.count > 0 then
				for counter in 1..IndivMailTabR.last loop
					if IndivMailTabR.exists(counter) then
						if (IndivMailTabR(counter).MailMsgType = otype or
								itype = '-')                               and
								IndivMailTabR(counter).MailMsgTime < mtime then
							IndivMailTabR.delete (counter);
						end if;
					end if;
				end loop;
			end if;

			typid := get_indivtypeid (pmode, otype);

			if typid = 0 then
				if IndivMailTypeTabR.count = 0 then
					typid := 1;
				else
					typid := IndivMailTypeTabR.last + 1;
				end if;

				IndivMailTypeTabR(typid).MailMsgType := otype;
			end if;

			IndivMailTypeTabR(typid).MailMsgTime := mtime;

			if IndivMailTabR.count = 0 then
				recno := 1;
			else
				recno := IndivMailTabR.last + 1;
			end if;

			IndivMailTabR(recno).MailMsgId   := id;
			IndivMailTabR(recno).MailMsgType := otype;
			IndivMailTabR(recno).MailMsgTime := mtime;
		end if;
	end;

	procedure mail_look (
		itype  in      char,      /* Type of message required '-' if all */
		imode  in      char,      /* P(eek) or R(ead) - Peek is non destructive */
		scope  in      char,      /* G(lobal) or I(ndividual) */
		retval in out  number,    /* Either the 0 or a -ve Error Number */
		mid    in out  number,    /* The message id returned (if applicable) */
		mtype  in out  char,      /* The type of message returned */
		text   in out  varchar2   /* The text of the message returned */
	)
	is

		cursor get_globalmail (
			ptyp mail_global.msg_type%type,
			ptim mail_global.msg_time%type
		)
		is

			select
				global_msg_id,
				msg_type,
				msg_time
			from
				mail_global
			where
				(ltrim(msg_address) = ltrim(globalmail_address)               or
				ltrim(msg_address)  = ltrim(allmail_address))                 and
				ltrim(rtrim(msg_type))  like ltrim(rtrim(decode(rtrim(ptyp),'-','%',ptyp))) and
				to_date(to_char(msg_time, 'DD-MON-RRRR'), 'DD-MON-RRRR') >=
				to_date(to_char(ptim, 'DD-MON-RRRR'), 'DD-MON-RRRR')
			order by
				msg_time;

		cursor get_indivmail (
			ptyp mail_indiv.msg_type%type,
			ptim mail_indiv.msg_time%type
		)
		is
			select
				indiv_msg_id,
				msg_type,
				msg_time
			from
				mail_indiv
			where
				ltrim(msg_address) = ltrim(indivmail_address)                and
				ltrim(rtrim(msg_type)) like ltrim(rtrim(decode(rtrim(ptyp),'-','%',ptyp))) and
				to_date(to_char(msg_time, 'DD-MON-RRRR'), 'DD-MON-RRRR') >=
				to_date(to_char(ptim, 'DD-MON-RRRR'), 'DD-MON-RRRR')
			order by
				msg_time;

		ac        number(6);
		last_id   number;
		mail_time mail_global.msg_time%type;
		type_id   number;

	begin

		/*
		 * - Initialise variables
		 * - IF globalmail_address is null THEN
		 *   Assign -1018 (start_mail not called) to the return value
		 * - ELSEIF imode is not equal to 'P' and imode is not equal to 'R' THEN
		 *   Assign -1016 (unknown imode) to the return value
		 * - ELSEIF scope is equal to 'G' and imode is equal to 'P' and 'R' THEN
		 *   Select the maximum global_msg_id from the mail_global table where
		 *    the msg_address, msg_typ and msg_time are the same
		 *   Select the global_msg_id, msg_type, msg_text, msg_time and
		 *    addressee_count from the mail_global table where the global_msg_id
		 *    is equal to the mid variable
		 *   Call the store_globalmail_record procedure
		 *   IF imode is equal to 'P' THEN
		 *     Assign the mail_time to the GlobalMailTimeP
		 *   ELSE
		 *     Assign the mail_time to the GlobalMailTimeR
		 *   IF itype is equal to mytpe or itype is equal to '-' THEN
		 *     IF imode is equal to 'R' THEN
		 *       IF ac is greater than 1 THEN
		 *         Update the mail_global table by decrementing the addressee_count
		 *          field where the global_msg_id is equal to mid.
		 *       ELSE
		 *         Delete from mail_global where the global_msg_id is equal to mid
		 *   ELSEIF itype is not equal to '-' THEN
		 *     Initialise mtype and text to ''.
		 * - ELSEIF scope is equal to 'G' THEN
		 *   IF itype is not equal to '-' THEN
		 *     Get the type_id by calling the get_globaltypeid
		 *     IF type_id is not equal to 0 THEN
		 *       IF imode is equal to 'P' THEN
		 *         Assign GlobalMailTypeTabP(type_id).MailMsgTime to mail_time
		 *       ELSE
		 *         Assign GlobalMailTypeTabR(type_id).MailMsgTime to mail_time
		 *     ELSE
		 *       IF imode is equal to 'P' THEN
		 *         Assign GlobalMailTimeP to mail_time
		 *       ELSE
		 *         Assign GlobalMailTimeR to mail_time
		 *   ELSE
		 *     IF imode is equal to 'P' THEN
		 *       Assign GlobalMailTimeP to mail_time
		 *     ELSE
		 *       Assign GlobalMailTimeR to mail_time
		 * - ELSE
		 *   IF imode is equal to 'P' THEN
		 *     Assign GlobalMailTimeP to mail_time
		 *   ELSE
		 *     Assign GlobalMailTimeR to mail_time
		 * - FOR mail_record in get_globalmail LOOP
		 *   IF globalidnotsent THEN
		 *     Assign mail_record.global_msg_id to mid
		 *     Exit
		 * - END LOOP
		 * - Select global_msg_id, msg_type, msg_text and addressee_count from
		 *    mail_global where the global_msg_id is equal to the mid
		 * - Call the store_globalmail_record procedure
		 * - IF itype is equal to '-' THEN
		 *   IF imode is equal to 'P' THEN
		 *     Assign mail_time to GlobalMailTimeP
		 *   ELSE
		 *     Assign mail_time to GlobalMailTimeR
		 * - IF imode is equal to 'R' THEN
		 *   IF ac is greater than 1 THEN
		 *     Update mail_global by decrementing the addressee_count where the
		 *      global_msg_id is equal to mid
		 *   ELSE
		 *     Delete from mail_global where global_msg_id is equal to mid
		 * - ELSEIF scope is equal to 'I' THEN
		 *   IF itype is not equal to '-' THEN
		 *     Get the type_id by calling the get_indivtypeid function
		 *     IF the type_id is not equal to 0 THEN
		 *       IF imode is equal to 'P' THEN
		 *         Assign IndivMailTypeTabP(type_id).MailMsgTime to mail_time
		 *       ELSE
		 *         Assign IndivMailTypeTabR(type_id).MailMsgTime to mail_time
		 *     ELSE
		 *       IF imode is equal to 'P' THEN
		 *         Assign IndivMailTimeP to mail_time
		 *       ELSE
		 *         Assign IndivMailTimeR to mail_time
		 *   ELSE
		 *     IF imode is equal to 'P' THEN
		 *       Assign IndivMailTimeP to mail_time
		 *     ELSE
		 *       Assign IndivMailTimeR to mail_time
		 *   FOR mail_record in get_indivmail LOOP
		 *     IF individnotsent THEN
		 *       Assign mail_record.indiv_msg_id to mid
		 *       Exit
		 *   END LOOP
		 *   Select indiv_msg_id, msg_type, msg_text, msg_time and addressee_count
		 *    from mail_indiv where indiv_msg_id is equal to mid
		 *   Call the store_indivmail_record procedure
		 *   IF itype is equal to '-' THEN
		 *     IF imode is equal to 'P' THEN
		 *       Assign mail_time to IndivMailTimeP
		 *     ELSE
		 *       Assign mail_time to IndivMailTimeR
		 *   IF imode is equal to 'R' THEN
		 *     IF ac is greater than 1 THEN
		 *       Update the mail_indiv table by decrementing the addressee_count
		 *        where the indiv_msg_id is equal to mid
		 *     ELSE
		 *       Delete from the mail_indiv table where the indiv_msg_id is equal
		 *        to the mid variable.
		 * - ELSE
		 *   Assign -1017 (Invalid Scope) to the return value
		 * - IF no data is found THEN
		 *   Assign '' to mtype and text
		 * - Assign GlobalMailTimeP and GlobalMailTimeR to the SYSDATE
		 */

		retval := 0;
		mid    := 0;

		if globalmail_address is null then
			retval := -1018; /* start_mail not called */
		elsif (imode != 'P' and imode != 'R') then
			retval := -1016;  /* unknown imode */
		elsif (scope = 'G' and
				( (GlobalMailTimeP = to_date('01/01/1901','DD/MM/YYYY') and
				imode = 'P')                                        or
				(GlobalMailTimeR = to_date('01/01/1901','DD/MM/YYYY')   and
				imode = 'R') ))                                     then
			/* First Time Called */

			select
				nvl(max(global_msg_id),0)
			into
				mid
			from
				mail_global
			where
				(msg_address = globalmail_address          or
				 msg_address = allmail_address)            and
				 msg_type like decode(itype,'-','%',itype) and
				 msg_time    = (
				select
					max(msg_time)
				from
					mail_global
				where
					(msg_address = globalmail_address or
					 msg_address = allmail_address) );

			select
				global_msg_id,
				msg_type,
				msg_text,
				msg_time,
				addressee_count
			into
				mid,
				mtype,
				text,
				mail_time,
				ac
			from
				mail_global
			where
				global_msg_id = mid;

			store_globalmail_record (imode, mid, mtype, mail_time, itype);

			if imode = 'P' then
				GlobalMailTimeP := mail_time;
			else
				GlobalMailTimeR := mail_time;
			end if;

			if itype = mtype or itype = '-' then
				if (imode = 'R') then
					if (ac > 1) then
						update
							mail_global
						set
							addressee_count = addressee_count - 1
						where
							global_msg_id = mid;
					else
						delete from
							mail_global
						where
							global_msg_id = mid;
					end if;
				end if;
			elsif itype != '-' then
				mtype := '';
				text  := '';
			end if;

		elsif (scope = 'G') then
			if itype != '-' then
				type_id := get_globaltypeid (imode, itype);

				if type_id != 0 then
					if imode = 'P' then
						mail_time := GlobalMailTypeTabP(type_id).MailMsgTime;
					else
						mail_time := GlobalMailTypeTabR(type_id).MailMsgTime;
					end if;
				else
					if imode = 'P' then
						mail_time := GlobalMailTimeP;
					else
						mail_time := GlobalMailTimeR;
					end if;
				end if;
			else
				if imode = 'P' then
					mail_time := GlobalMailTimeP;
				else
					mail_time := GlobalMailTimeR;
				end if;
			end if;

			for mail_record in get_globalmail (itype, mail_time) loop
				if globalidnotsent (
						imode,
						mail_record.global_msg_id,
						mail_record.msg_type,
						mail_record.msg_time
				) then
					mid := mail_record.global_msg_id;
					exit;
				end if;
			end loop;

			select
				global_msg_id,
				msg_type,
				msg_text,
				msg_time,
				addressee_count
			into
				mid,
				mtype,
				text,
				mail_time,
				ac
			from
				mail_global
			where
				global_msg_id = mid;

			store_globalmail_record (imode, mid, mtype, mail_time, itype);

			if itype = '-' then
				if imode = 'P' then
					GlobalMailTimeP := mail_time;
				else
					GlobalMailTimeR := mail_time;
				end if;
			end if;

			if (imode = 'R') then
				if (ac > 1) then
					update
						mail_global
					set
						addressee_count = addressee_count - 1
					where
						global_msg_id = mid;
				else
					delete from
						mail_global
					where
						global_msg_id = mid;
				end if;
			end if;

		/* Individual Mail - BPM Processing starts here.  */
		elsif (scope = 'I') then
			if itype != '-' then

				/*
				* Find out whether we have entries for this mail type
				* in our IndivMailTypeTabP or R lists and, if we have,
				* then get the index into the list of the most recent one.
				*/
				type_id := get_indivtypeid (imode, itype);

				if type_id != 0 then
					/*
					* We have an entry (or some entries). Save the
					* time of most recent entry.
					*/
					if imode = 'P' then
						mail_time := IndivMailTypeTabP(type_id).MailMsgTime;
					else
						mail_time := IndivMailTypeTabR(type_id).MailMsgTime;
					end if;

				else
					/*
					* We have no entries - initialise the time.
					*/
					if imode = 'P' then
						mail_time := IndivMailTimeP;
					else
						mail_time := IndivMailTimeR;
					end if;
				end if;
			else
				/*
				* Type was not passed in - so just initialise the time.
				*/
				if imode = 'P' then
					mail_time := IndivMailTimeP;
				else
					mail_time := IndivMailTimeR;
				end if;
			end if;

			/*
			* Search through the mail messages in mail_indiv, using cursor
			* get_indivmail, using function individnotsent to check whether
			* any messages found have been sent (i.e. exist in IndivMailTypeTabP
			* or R). If an unsent message is found then set mid to that message's
			* id and exit the loop.
			*/

 			for mail_record in get_indivmail (itype, mail_time) loop
				if individnotsent (
						imode,
						mail_record.indiv_msg_id,
						mail_record.msg_type,
						mail_record.msg_time
				) then
					mid := mail_record.indiv_msg_id;
					exit;
				end if;
			end loop;

			/* Read the rest of the message data from mail_indiv. */
			select
				indiv_msg_id,
				msg_type,
				msg_text,
				msg_time,
				addressee_count
			into
				mid,
				mtype,
				text,
				mail_time,
				ac
			from
				mail_indiv
			where
				indiv_msg_id = mid;

			/* Make an entry in IndivMailTypeTabP or R for the message to be sent. */
			store_indivmail_record (imode, mid, mtype, mail_time, itype);

			/*
			* Remember the message's msg_time for the next time through this
			* procedure.
			*/
			if itype = '-' then
				if imode = 'P' then
					IndivMailTimeP := mail_time;
				else
					IndivMailTimeR := mail_time;
				end if;
			end if;

			if (imode = 'R') then
				if (ac > 1) then
					update
						mail_indiv
					set
						addressee_count = addressee_count - 1
					where
						indiv_msg_id = mid;
				else
					delete from
						mail_indiv
					where
						indiv_msg_id = mid;
				end if;
			end if;
		else
			retval := -1017; /*Invalid Scope*/
		end if;

		exception when NO_DATA_FOUND then
			mtype := '';
			text := '';

		if ( (GlobalMailTimeP = to_date('01/01/1901','DD/MM/YYYY') and
				imode = 'P'))                                      then
			GlobalMailTimeP := SYSDATE;
		end if;

		if ( (GlobalMailTimeR = to_date('01/01/1901','DD/MM/YYYY') and
				imode = 'R'))                                      then
		GlobalMailTimeR := SYSDATE;
		end if;

	when others then
		raise;
	end mail_look;




	procedure start_mail (
		proc char,
		unit char
	)
	is

		retval number;
		mid    number;
		mtype  char(6);
		text   varchar2(100);

	begin

		/*
		 * - Initialise variables
		 * - Call the mail_look procedure
		 * -
		 */

		GlobalMailTypeTabP.delete;
		GlobalMailTabP.delete;
		IndivMailTypeTabP.delete;
		IndivMailTabP.delete;
		GlobalMailTypeTabR.delete;
		GlobalMailTabR.delete;
		IndivMailTypeTabR.delete;
		IndivMailTabR.delete;
		IndivMailTimeP     := to_date('01/01/1901','DD/MM/YYYY');
		IndivMailTimeR     := to_date('01/01/1901','DD/MM/YYYY');
		GlobalMailTimeP    := to_date('01/01/1901','DD/MM/YYYY');
		GlobalMailTimeP    := to_date('01/01/1901','DD/MM/YYYY');
		indivmail_address  := proc || unit;
		globalmail_address := proc || '0';
		dbms_alert.register(indivmail_address);
		dbms_alert.register(globalmail_address);
		dbms_alert.register(allmail_address);
		mail_look (
			'-',
			'P',
			'G',
			retval,
			mid,
			mtype,
			text);
			MAIL_LOOK (
			'-',
			'R',
			'G',
			retval,
			mid,
			mtype,
			text
		);
		end;


	procedure cancel_mail is
	begin

		/*
		 * - Call dbms_alert.remove and initialise globalmail_address to ''
		 */

		dbms_alert.remove(indivmail_address);
		dbms_alert.remove(globalmail_address);
		dbms_alert.remove(allmail_address);
		globalmail_address := '';
	end;



	procedure store_mail (
		addr     in char,
		itype    in char,
		text     in varchar,
		msgcount in smallint,
		scope    in char,
		retval   out number
	)
	is

	begin

		/*
		 * - Initialise retval
		 * - IF scope is equal to 'G' THEN
		 *   Insert a record into the mail_global table
		 *   Select the next ID number as the return number
		 * - ELSEIF scope is equal to 'I' THEN
		 *   Insert a record into the mail_indiv table
		 *   Select the next ID number as the return number
		 * - ELSE
		 *   Assign -1017 (incorrect scope) as the return value
		 */

		retval := 0;

		if (scope = 'G') then
			insert into
				mail_global (
					msg_address,
					msg_type,
					msg_text,
					msg_time,
					addressee_count
				) values (
					addr,
					itype,
					text,
					SYSDATE,
					msgcount
				);

			select
				gmsgid_gen.currval
			into
				retval
			from
				dual;

		elsif (scope = 'I') then
			insert into
				mail_indiv (
					msg_address,
					msg_type,
					msg_text,
					msg_time,
					addressee_count
				) values (
					addr,
					itype,
					text,
					SYSDATE,
					msgcount
				);

			select
				imsgid_gen.currval
			into
				retval
			from
				dual;

		else
			retval := -1017;         /* incorrect scope */
		end if;

/* CA 199 C.Penny: commit should occur at end of bag_log.pc if whole transaction not rolled back. */
  -- commit;

	end store_mail;




	function poll_mail return number
	is
		mailmsg    varchar2(2000);
		mailstatus integer;
	begin

		/*
		 * - Call the dbms_alert.waitone built in procedure
		 * - IF the signal is received (mailstatus = 0) THEN
		 *   Return 1
		 * - Call the dbms_alert.waitone built in procedure
		 * - IF the signal is received (mailstatus = 0) THEN
		 *   Return 1
		 * - Call the dbms_alert.waitone built in procedure
		 * - IF the signal is received (mailstatus = 0) THEN
		 *   Return 1
		 * - Return 0
		 */

		dbms_alert.waitone (
			indivmail_address,
			mailmsg,
			mailstatus,
			0
		);

		if mailstatus = 0 then /* Signal Received */
			return 1;
		end if;

		dbms_alert.waitone (
			globalmail_address,
			mailmsg,
			mailstatus,
			0);

		if mailstatus = 0 then /* Signal Received */
			return 1;
		end if;

		dbms_alert.waitone (
			allmail_address,
			mailmsg,
			mailstatus,
			0);

		if mailstatus = 0 then /* Signal Received */
			return 1;
		end if;

		return 0;

	end poll_mail;


	procedure get_mail_counts (
		scope in      char,
		mid   in out  number
	)
	is

	begin

		/*
		 * - Initialise mid to -1
		 * - IF scope is equal to 'I' THEN
		 *   Select the maximum indiv_msg_id from the mail_indiv table where
		 *    the msg_address is equal to the indivmail_address and the
		 *    msg_time is equal to the message time of the indivmail_address
		 *    message.
		 * - ELSEIF scope is equal to 'G' THEN
		 *   Select the maximum global_msg_id from the mail_global table where
		 *    the msg_address is equal to the globalmail_address and the
		 *    msg_time is equal to the message time of the globalmail_address
		 *    message.
		 * - ELSE
		 *   Select the maximum global_msg_id from the mail_global table where
		 *    the msg_address is equal to the allmail_address and the
		 *    msg_time is equal to the message time of the allmail_address
		 *    message.
		 */

		mid := -1;

		if (scope = 'I') then
			select
				nvl(max(indiv_msg_id),0)
			into
				mid
			from
				mail_indiv
			where
				msg_address = indivmail_address and
				msg_time    = (
					select
						max(msg_time)
					from
						mail_indiv
					where
						msg_address = indivmail_address
				);

		elsif (scope = 'G') then
			select
				nvl(max(global_msg_id),0)
			into
				mid
			from
				mail_global
			where
				msg_address = globalmail_address and
				msg_time    = (
					select
						max(msg_time)
					from
						mail_global
					where
						msg_address = globalmail_address
					);
		else
			select
				nvl(max(global_msg_id),0)
			into
				mid
			from
				mail_global
			where
				msg_address = allmail_address and
				msg_time    = (
					select
						max(msg_time)
					from
						mail_global
					where
						msg_address = allmail_address
					);
		end if;

	end get_mail_counts;



	procedure get_new_mail_counts (
		icount in out integer,
		gcount in out integer,
		acount in out integer
	)
	is

		cursor get_indivmail is
			select
				indiv_msg_id,
				msg_time
			from
				mail_indiv
			where
				msg_address = indivmail_address and
				msg_time   >= IndivMailCountTime
			order by
				msg_time;

		cursor get_globalmail is
			select
				global_msg_id,
				msg_time
			from
				mail_global
			where
				msg_address = globalmail_address and
				msg_time   >= GlobalMailCountTime
			order by
				msg_time;

		cursor get_allmail is
			select
				global_msg_id,
				msg_time
			from
				mail_global
			where
				msg_address = allmail_address and
				msg_time   >= AllMailCountTime
			order by
				msg_time;

		last_recno number;

		ITempMailCountTab IndivMailTabTyp;
		GTempMailCountTab GlobalMailTabTyp;




	function globalidcounted (
		id      number,
		MailTab GlobalMailTabTyp
	) return boolean is

		counter number;

	begin

		/*
		 * - IF MailTab.count is greater than 0 THEN
		 *   FOR counter from 1 to MailTab.last LOOP
		 *     IF MailTab.exists(counter) THEN
		 *       IF id is equal to MailTab(counter).MailMsgID THEN
		 *         Return True
		 *   END LOOP
		 * - Return False
		 */

		if MailTab.count > 0 then
			for counter in 1..MailTab.last loop
				if MailTab.exists(counter) then
					if id = MailTab(counter).MailMsgID then
						return true;
					end if;
				end if;
			end loop;
		end if;

		return false;
	end globalidcounted;




	function individcounted (id number)
		return boolean
	is
		counter number;
	begin

		/*
		 * - IF IndivMailCountTab.count is greater than 0 THEN
		 *   FOR counter from 1 to IndivMailCountTab.last LOOP
		 *     IF IndivMailCountTab.exists(counter) THEN
		 *       IF id is equal to IndivMailCountTab(counter).MailMsgID THEN
		 *         Return True
		 *   END LOOP
		 * - Return False
		 */

		if IndivMailCountTab.count > 0 then
			for counter in 1..IndivMailCountTab.last loop
				if IndivMailCountTab.exists(counter) then
					if id = IndivMailCountTab(counter).MailMsgID then
						return true;
					end if;
				end if;
			end loop;
		end if;

		return false;
	end individcounted;



	begin

		/*
		 * - Initialise Variables
		 * - FOR indiv_record is in the get_indivmail LOOP
		 *   IF not individcounted THEN
		 *     Increment icount
		 *     Assign indiv_record.msg_time to IndivMailCountTime
		 *   IF last record number is equal to 0 THEN
		 *     Assign 1 to the last record number
		 *     Assign Variables
		 *   ELSEIF ITempMailCountTab(last_recno).MailMsgTime is equal to
		 *    indiv_record.msg_time THEN
		 *     Increment the last record number
		 *     Assign Variables
		 *   ELSE
		 *     Assign 1 to the last record number
		 *     Assign variables
		 * - END LOOP
		 * - Assign ITempMailCountTab to IndivMailCountTab
		 * - Initialise global message counts to 0.
		 * - FOR global_record in get_globalmail LOOP
		 *   IF not globalidcounted THEN
		 *     Increment gcount
		 *     Assign global_record.msg_time to GlobalMailCountTime
		 *   IF last record number is equal to 0 THEN
		 *     Assign 1 to the last record number
		 *     Assign Variables
		 *   ELSEIF GTempMailCountTab(last_recno).MailMsgTime is equal to
		 *    global_record.msg_time THEN
		 *     Increment the last record number
		 *     Assign Variables
		 *   ELSE
		 *     Assign 1 to the last record number
		 *     Assign variables
		 * - END LOOP
		 * - Assign GTempMailCountTab to GlobalMailCountTab
		 * - Initialise global message counts to 0.
		 * - FOR global_record in get_allmail LOOP
		 *   IF not globalidcounted THEN
		 *     Increment acount
		 *     Assign global_record.msg_time to AllMailCountTime
		 *   IF last record number is equal to 0 THEN
		 *     Assign 1 to the last record number
		 *     Assign Variables
		 *   ELSEIF GTempMailCountTab(last_recno).MailMsgTime is equal to
		 *    global_record.msg_time THEN
		 *     Increment the last record number
		 *     Assign Variables
		 *   ELSE
		 *     Assign 1 to the last record number
		 *     Assign variables
		 * - END LOOP
		 * - Assign GTempMailCountTab to AllMailCountTab
		 */

		/* Count Individual Messages */
		icount     := 0;
		last_recno := 0;

		for indiv_record in get_indivmail loop
			if not individcounted(indiv_record.indiv_msg_id) then
				icount             := icount + 1;
				IndivMailCountTime := indiv_record.msg_time;
			end if;

			if last_recno = 0 then
				last_recno := 1;
				ITempMailCountTab(last_recno).MailMsgId   :=
						indiv_record.indiv_msg_id;

				ITempMailCountTab(last_recno).MailMsgTime :=
						indiv_record.msg_time;

			elsif ITempMailCountTab(last_recno).MailMsgTime =
					indiv_record.msg_time then
				last_recno := last_recno + 1;

				ITempMailCountTab(last_recno).MailMsgId :=
						indiv_record.indiv_msg_id;

				ITempMailCountTab(LAST_RECNO).MailMsgTime :=
						indiv_record.msg_time;

			else
				ITempMailCountTab.delete;
				last_recno := 1;

				ITempMailCountTab(last_recno).MailMsgId   :=
						indiv_record.indiv_msg_id;

				ITempMailCountTab(last_recno).MailMsgTime :=
						indiv_record.msg_time;
			end if;
		end loop;

		IndivMailCountTab := ITempMailCountTab;

		/* Count Global Messages */
		gcount     := 0;
		last_recno := 0;

		for global_record in get_globalmail loop
			if not globalidcounted(global_record.global_msg_id,
					GlobalMailCountTab) then
				gcount := gcount + 1;
				GlobalMailCountTime := global_record.msg_time;
			end if;

			if last_recno = 0 then
				last_recno := 1;

				GTempMailCountTab(last_recno).MailMsgId   :=
						global_record.global_msg_id;

				GTempMailCountTab(last_recno).MailMsgTime :=
						global_record.msg_time;

			elsif GTempMailCountTab(last_recno).MailMsgTime =
					global_record.msg_time then
				last_recno := last_recno + 1;

				GTempMailCountTab(last_recno).MailMsgId :=
						global_record.global_msg_id;

				GTempMailCountTab(last_recno).MailMsgTime :=
						global_record.msg_time;

			else
				GTempMailCountTab.delete;
				last_recno := 1;

				GTempMailCountTab(last_recno).MailMsgId :=
						global_record.global_msg_id;

				GTempMailCountTab(last_recno).MailMsgTime :=
						global_record.msg_time;

			end if;
		end loop;

		GlobalMailCountTab := GTempMailCountTab;
		GTempMailCountTab.delete;

		/* Count Broadcast Messages */
		acount     := 0;
		last_recno := 0;

		for global_record in get_allmail loop
			if not globalidcounted(global_record.global_msg_id,
					AllMailCountTab) then
				acount           := acount + 1;
				AllMailCountTime := global_record.msg_time;
			end if;

		if last_recno = 0 then
			last_recno                                := 1;
			GTempMailCountTab(last_recno).MailMsgId   :=
					global_record.global_msg_id;

			GTempMailCountTab(last_recno).MailMsgTime :=
			global_record.msg_time;

		elsif GTempMailCountTab(last_recno).MailMsgTime =
				global_record.msg_time then
			last_recno                                := last_recno + 1;
			GTempMailCountTab(last_recno).MailMsgId   :=
					global_record.global_msg_id;

			GTempMailCountTab(last_recno).MailMsgTime :=
					global_record.msg_time;

		else
			GTempMailCountTab.delete;
			last_recno                                := 1;
			GTempMailCountTab(LAST_RECNO).MailMsgId   :=
					global_record.global_msg_id;

			GTempMailCountTab(LAST_RECNO).MailMsgTime :=
					global_record.msg_time;

		end if;
	end loop;

	AllMailCountTab := GTempMailCountTab;

	end get_new_mail_counts;


	/******************************************************************************
	*
	* The following mail interface functions should only be called by the DCS
	* gateway.
	*
	******************************************************************************/

	/******************************************************************************
	*
	* PROCEDURE gw_mail_reset
	*
	* Description:
	*  This procedure will initialise all global variables and reset the state
	*  of each interface in the system_cfg and mail_indiv tables.
	*
	* Parameters:
	*   p_proc - Our process identifier
	*   p_unit - Our unit ID
	*
	******************************************************************************/

	PROCEDURE gw_mail_reset (p_proc IN CHAR, p_unit IN CHAR) IS
	BEGIN

	   --
	   -- Derive our individual mail address
	   --
	   indivmail_address := p_proc || p_unit;

	   --
	   -- Reset retry counters so we'll attempt to re-send data for
	   -- each DCS interface
	   --
	   UPDATE system_cfg SET retry_status = 0, msgs_this_batch = 0
	      WHERE process_name = 'DCS';

	   --
	   -- The following update performs a full-table scan.  It shouldn't
	   -- be a performance problem because it's only performed once on
	   -- gateway startup, but this depends on the size of the mail_indiv table.
	   --
	   UPDATE mail_indiv SET send_status = ''
	      WHERE NVL(send_status,'N') = 'Y';

	END gw_mail_reset;


	/******************************************************************************
	*
	* PROCEDURE gw_mail_batch_start
	*
	* Description:
	*  This procedure will open our mail cursor and set the state of the package
	*  ready for a batch to be send.  It should be called before the mail_peek proc.
	*
	* Parameters:
	*   None
	*
	******************************************************************************/

	PROCEDURE gw_mail_batch_start IS
	BEGIN
	   --
	   -- If our mail cursor is already open, close it before
	   -- continuing.
	   --
	   IF v_mail_cursor_open = TRUE THEN
	      CLOSE l_mail_cursor;
	      v_mail_cursor_open := FALSE;
	   END IF;

	   --
	   -- Set all our "rows sent this batch" counters to "0"
	   --
	   UPDATE system_cfg SET msgs_this_batch = 0
	      WHERE process_name = 'DCS';

	   --
	   -- Decrement counts for failed interfaces so we will retry at some point
	   --
	   UPDATE system_cfg SET retry_status = retry_status -1
	      WHERE process_name = 'DCS' AND NVL(retry_status,'0') > 0;

	   --
	   -- Open our mail cursor
	   --
	   OPEN l_mail_cursor;
	   v_mail_cursor_open := TRUE;

	END gw_mail_batch_start;


	/******************************************************************************
	*
	* PROCEDURE gw_mail_peek
	*
	* Description:
	*   This procedure scans the mail_indiv queue, looking for messages which can
	*   be sent.
	*
	*   It will return only a specific number of messages for each interface per
	*   batch, and it will not return messages for interfaces that have failed.
	*
	* Parameters:
	*   p_retval - Set to negative (error), 0 (no messages) or 1 (message found)
	*   p_msgid  - Set to the message ID to send (if found)
	*   p_msgtyp - Set to the message type (if found)
	*   p_msgtxt - Set to the message text (if found)
	*
	******************************************************************************/

	PROCEDURE gw_mail_peek (p_retval  OUT NUMBER,
	                        p_msgid   OUT NUMBER,
	                        p_msgtyp  OUT VARCHAR2,
	                        p_msgtxt  OUT VARCHAR2
	                       )
	IS

	   l_mail_message       mail_indiv%ROWTYPE;
	   l_retry_status       system_cfg.retry_status%TYPE;
	   l_msgs_this_batch    system_cfg.msgs_this_batch%TYPE;
	   l_max_msgs_per_batch NUMBER;

	BEGIN

	   --
	   -- Default is "no message found"
	   --
	   p_retval := 0;
	   p_msgid  := '';
	   p_msgtyp := '';
	   p_msgtxt := '';


	   -- Find the number of messages we are allowed to send in each batch
	   BEGIN
	      SELECT TO_NUMBER(db_value) into l_max_msgs_per_batch FROM db_config
	         WHERE db_item = 'GW_TX_MESSAGE_LIMIT';
	   EXCEPTION
	      WHEN OTHERS THEN
	         --
	         -- Use default value of 5
	         --
	         l_max_msgs_per_batch := 5;
	   END;

	   LOOP
	      FETCH l_mail_cursor INTO l_mail_message;
	      EXIT WHEN l_mail_cursor%NOTFOUND;

	      --
	      -- Find the status of this interface.
	      --
	      BEGIN
	         SELECT retry_status, msgs_this_batch INTO l_retry_status, l_msgs_this_batch FROM system_cfg
	            WHERE process_name = 'DCS' AND
	                  subunit_name = l_mail_message.address_stamp;

	         IF l_retry_status > 0 THEN
	            --
	            -- This interface has failed, ignore this message
	            --
	            dbms_output.put_line('Message '||l_mail_message.indiv_msg_id||
       	                          ' - Not sending because the '||l_mail_message.address_stamp||
       	                          ' interface has a retry count of '||l_retry_status);
	         ELSIF (l_msgs_this_batch > l_max_msgs_per_batch) THEN
	            --
  	            -- Sent too many messages this time round for this interface
	            --
	            dbms_output.put_line('Message '||l_mail_message.indiv_msg_id||
	                                 ' - Not sending because the '||l_mail_message.address_stamp||
	                                 ' interface has a message count of '||l_msgs_this_batch);
	         ELSE
	            --
	            -- This is a valid message
	            --
	            p_msgid  := l_mail_message.indiv_msg_id;
	            p_msgtyp := TRIM(l_mail_message.msg_type);
	            p_msgtxt := TRIM(l_mail_message.msg_text);
	            p_retval := 1;

	            UPDATE system_cfg SET msgs_this_batch = msgs_this_batch + 1
	               WHERE process_name = 'DCS' and SUBUNIT_NAME = l_mail_message.address_stamp;

	            UPDATE mail_indiv SET send_status = 'Y'
	               WHERE indiv_msg_id = l_mail_message.indiv_msg_id;

	            dbms_output.put_line('Message '||l_mail_message.indiv_msg_id||
	                                 ' - Sending to the '||l_mail_message.address_stamp||
	                                 ' interface.');

	            EXIT;
	         END IF;
	      EXCEPTION
	         WHEN OTHERS THEN
	            -- This is an error from an individual message or interface
	            -- Ignore it and process the next message.
	            p_retval := 0;
	            p_msgid  := '';
	            p_msgtyp := '';
	            p_msgtxt := '';
	      END;

	   END LOOP;

	EXCEPTION
	   WHEN OTHERS THEN
	      --
	      -- Should only get here if there are cursor errors
	      --
	      p_retval := -1;
	END gw_mail_peek;


	/******************************************************************************
	*
	* PROCEDURE gw_mail_error
	*
	* Description:
	*   This procedure is called if Put_Msg fails, and will mark the message
	*   in question as "failed".  It will set the retry counter for the interface
	*   to a value (read from the DB_CONF table) and mo more messages for that
	*   interface will be sent this batch.
	*
	* Parameters:
	*   p_msgid  - The ID of the message that failed.
	*
	******************************************************************************/

	PROCEDURE gw_mail_error (p_msgid IN NUMBER) IS

	   l_retry_value NUMBER;

	BEGIN

	   --
	   -- Mark the inidividual message as "not sent"
	   --
	   UPDATE mail_indiv SET send_status = ''
	      WHERE indiv_msg_id = p_msgid;

	   --
	   -- Mark the interface as down for a certain time
	   --
	   BEGIN
	      SELECT TO_NUMBER(db_value) into l_retry_value FROM db_config
	         WHERE db_item = 'GW_TX_RETRY_COUNT';
	   EXCEPTION
	      WHEN OTHERS THEN
	         --
	         -- Use default value of 6
	         --
	         l_retry_value := 6;
	   END;

	   UPDATE system_cfg SET retry_status = l_retry_value
	      WHERE process_name = 'DCS' AND
	            subunit_name = (select address_stamp from mail_indiv where indiv_msg_id = p_msgid);

	END gw_mail_error;

	/******************************************************************************
	*
	* PROCEDURE gw_mail_reject
	*
	* Description:
	*   This procedure is called if the mail cannot be converted into an outgoing
	*   message properly.  It will mark it as "failed", and will perform no further
	*   action on it.  It will not be returned again by the mail functions, and
	*   must be deleted or corrected manually by the user.
	*
	* Parameters:
	*   p_msgid  - The ID of the message that failed.
	*
	******************************************************************************/

	PROCEDURE gw_mail_reject (p_msgid IN NUMBER) IS

	   l_retry_value NUMBER;

	BEGIN

	   --
	   -- Mark the inidividual message as "not sent"
	   --
	   UPDATE mail_indiv SET send_status = 'E'
	      WHERE indiv_msg_id = p_msgid;

	END gw_mail_reject;

/*used to correct unprocessed mail_indiv entries for a flight if flight details have changed*/
PROCEDURE correct_mail_indiv_bpms(p_flight_id        IN flight.flight_id%TYPE,
                                  p_old_process_name IN flight.process_name%TYPE,
                                  p_old_unit_name    IN flight.unit_name%TYPE,
                                  p_new_process_name IN flight.process_name%TYPE,
                                  p_new_unit_name    IN flight.unit_name%TYPE,
                                  p_new_dcsid        IN flight.dcsid%TYPE,
                                  p_new_subunit_name IN flight.subunit_name%TYPE) IS

  CURSOR c_indivmail IS
    SELECT *
      FROM mail_indiv
     WHERE msg_type = 'BPM'
       AND trim(msg_address) =
           rtrim(p_old_process_name) || rtrim(p_old_unit_name)
       -------to skip bagflo records-------------------
       AND  trim(substr(msg_text,
                  instr(msg_text, ',', 1, 2) + 1,
                  instr(msg_text, ',', 1, 3) - instr(msg_text, ',', 1, 2) - 1))
            IN (1,2,3,4,5,6,7,8,9,14)
       ------------------------------------------------
       AND p_flight_id =
           trim(substr(msg_text,
                  instr(msg_text, ',', 1, 3) + 1,
                  instr(msg_text, ',', 1, 4) - instr(msg_text, ',', 1, 3) - 1));

  msg_text_part1 mail_indiv.msg_text%TYPE;
  msg_text_part2 mail_indiv.msg_text%TYPE;
BEGIN
  msg_text_part1 := rtrim(p_new_dcsid) || ',' ||
                    rtrim(valid_address_stamp(p_new_subunit_name));
  FOR r_indivmail IN c_indivmail LOOP

    msg_text_part2 := substr(r_indivmail.msg_text,
                             instr(r_indivmail.msg_text, ',', 1, 2));
    UPDATE mail_indiv
       SET msg_address = rtrim(p_new_process_name) || rtrim(p_new_unit_name),
           msg_text    = msg_text_part1 || msg_text_part2,
	   address_stamp = rtrim(valid_address_stamp(p_new_subunit_name))
     WHERE indiv_msg_id = r_indivmail.indiv_msg_id;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    add_app_msg.log_oracle_error('mail_pack.correct_mail_indiv',
                                 SQLERRM,
                                 SQLCODE);
    RAISE;
END correct_mail_indiv_bpms;

end mail_pack;
/

