/*****************************************************************************
*
* Filename         - $Workfile:   mail_bpm4.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_bpm4.sql  $  $Revision:   1.12  $
*
* 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:   /pvcsarch/db_ora/db/source/live/procedures/mail_bpm4.sqv  $
* 
*    Rev 1.12   06 Jun 2000 09:48:14   tagtrak
*  
* 
*    Rev 1.11.1.0   07 Jan 2000 15:49:30   tagtrak
* OR:
* CR: 14744 14738
* REV:
* Changes made because OF firstly a Y2K issue with
* the mail_pack package and also mail_indiv records
* were not being deleted for record ULD position
* data.
* 
* Neil Bishop.
* 
*    Rev 1.11   02 Apr 1998 19:36:54   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.10   26 Feb 1998 10:14:16   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
* 
*    Rev 1.9   15 Jan 1998 12:13:28   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.8   24 Oct 1997 11:16:14   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT create or replace procedure mail_bpm4

create or replace procedure mail_bpm4 (
        fid    in out  number,
        uld    in out  char,
        btype  in      char,
        scanner_id in char,
        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_bsts     system_cfg.bpm_status%TYPE; 
        l_dsts     system_cfg.dcs_status%TYPE; 
        l_addr     mail_indiv.msg_address%TYPE; 
        l_mesg     mail_indiv.msg_text%TYPE; 

        /*
         * Lets generate a BPMID for the Record ULD Pos.
         * Neil Bishop.  06/01/2000.
         */
        generated_bpmid number;

begin

        /*
         * - Initialise Variables
         * - Select columns from the flight table where the flight_id is equal
         *   to the fid parameter.  If no data is found, then assign '-' to
         *   l_proc.
         * - IF l_proc = '-' (ie. no rows found) then
         *   Assign -1015 to the return value
         * - ELSE
         *   assign '-' to l_bsts
         *   select bpm_status and dcs_status from the system_cfg table where
         *    the process name is equal to the l_proc parameter and the unit_name
         *    is equal to the l_unit parameter and the dcsid is equal to the dcsid
         *    parameter and the subunit_name is equal to the l_sub parameter. If
         *    no data is found then assign '-' to l_bsts.
         *   IF l_bsts = '-' (ie. no data found from the select) then
         *     Assign -1015 to the return value
         *   ELSE
         *     Format the address and message string
         *     Call the store_mail procedure
         */

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

        /* this is a bodge be careful */                                                            

        begin
                select
                        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;

                if (l_proc = '-') then 
                        retval := -1015;
                else
                        l_bsts := '-';

                        begin
                                select
                                        dcs_status 
                                into
                                        l_dsts 
                                from
                                        system_cfg
                                where
                                        process_name =  l_proc  and
                                        unit_name    =  l_unit  and
                                        dcsid        =  l_dcsid and
                                        subunit_name =  l_sub;
                                
				get_bpm_status(l_proc, l_unit, l_dcsid, l_sub,
					       null, fid, btype, l_bsts);
                                
                                exception
                                        when NO_DATA_FOUND then
                                                l_bsts := '-';
                                end;

                                if (l_bsts = '-') then 
                                        retval := -1015;
                                else 
                                if (l_bsts = 'Y') then
                                        /*
                                         * Right, we want to generate BPM ID's, so firstly, get a
                                         * BPM ID.  This is different from other functions such
                                         * as load/offload bag, because this stored procedure
                                         * generates the BPM ID, and not the 'C' interface.
                                         * Neil Bishop.  06/01/2000.
                                         */
                                        generate_bpmid (generated_bpmid);

                                        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(generated_bpmid)
                                                ||','||uld||','||scanner_id;

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