/*****************************************************************************
*
* Filename         - $Workfile:   mail_bpm2.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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   mail_bpm2.sql  $  $Revision:   1.10  $
*
* 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/tagtarch/db_ora/db/source/live/procedures/mail_bpm2.sqv  $
* 
*    Rev 1.10   02 Apr 1998 19:36:48   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.9   26 Feb 1998 10:14:10   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
* 
*    Rev 1.8   15 Jan 1998 12:13:24   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.7   24 Oct 1997 11:15:34   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure mail_bpm2

create or replace procedure mail_bpm2 (
        fid    in out  number,
        tuld   in out  char,
        fuld   in out  char,
        btype  in out  char,
        bpmid  in out  number,
        scanner_id in  char,
        pax_id     in  number,
        retval in out  number
) 
as

        l_proc  system_cfg.process_name%TYPE; 
        l_unit  system_cfg.unit_name%TYPE; 
        l_dcsid system_cfg.dcsid%TYPE; 
        l_sub   system_cfg.subunit_name%TYPE; 
        l_addr  mail_indiv.msg_address%TYPE; 
        l_mesg  mail_indiv.msg_text%TYPE; 

begin

        /*
         * - Initialise Variables
         * - Select columns from the flight table where the flight_id is equal
         *   to the fid parameter.  If no data is found, the assign '-' to l_proc.
         * - IF l_proc = '-' (ie. no rows found) then
         *   Assign -1015 as the return value
         * - ELSE
         *   Build the address and message string
         *   Call the store_mail procedure
         */

        retval := 0;
        l_proc   := '-';

        /*
         * Added Rtrim to retain originally intended 'bodge' behaviour.
         */
        begin
                select
                        rtrim(process_name),
                        unit_name,
                        dcsid,
                        subunit_name 
                into
                        l_proc,
                        l_unit,
                        l_dcsid,
                        l_sub 
                from
                        flight
                where
                        flight_id = fid;

                exception
                        when NO_DATA_FOUND then
                                l_proc := '-';
                end;

                /* will only be run if the BPM status == "Y" */                                                                                                                                 
                if (l_proc = '-') then 
                        retval := -1015;
                else
                        l_addr := RTRIM(l_proc)||RTRIM(l_unit);

                        l_mesg := RTRIM(l_dcsid) ||','|| RTRIM(valid_address_stamp(l_sub)) ||','|| btype ||','
                                || to_char(fid)  ||','|| to_char(bpmid) ||','
                                || RTRIM(fuld)   ||','|| RTRIM(tuld) ||','|| RTRIM(scanner_id) || ',' || RTRIM(pax_id);

                        mail_pack.store_mail (
                                l_addr,
                                'BPM',
                                l_mesg,
                                1,
                                'I',
                                retval
                        );
                end if;

end mail_bpm2;
/
