/*****************************************************************************
*
* Filename         - $Workfile:   mail_bpm1.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_bpm1.sql  $  $Revision:   1.9  $
*
* 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_bpm1.sqv  $
* 
*    Rev 1.9   02 Apr 1998 19:36:46   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.8   15 Jan 1998 12:13:22   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:10   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure mail_bpm1 

CREATE OR REPLACE procedure mail_bpm1 (
        fid       in out  number,
        pid       in out  number,
        tag       in out  char,
        btype     in      char,
        bpmid     in out  number,
        p_proc    in      char,
        p_unit    in      char,
        p_dcsid   in      char,
        p_subunit in      char,
        p_scanner_id in   char,
        retval    out     number
) as

        l_pxid    bag.pax_id%TYPE;
        l_proc    system_cfg.process_name%TYPE;
        l_unit    system_cfg.unit_name%TYPE;
        l_dcsid   system_cfg.dcsid%TYPE;
        l_subunit system_cfg.subunit_name%TYPE;
        l_addr    mail_indiv.msg_address%TYPE;
        l_mesg    mail_indiv.msg_text%TYPE;

begin

        /*
         * - Initialise variable
         * - IF p_proc = '-' (ie. a NULL value) then
         *   Select various columns from the bag table where the pax_id is
         *    equal to the pid parameter and the bag_tag is equal to the tag
         *    parameter.  If no data is found, then assign '-' to l_proc.
         * - ELSE
         *   Assign variables
         * - IF l_proc = '-' (ie. a NULL value) then
         *   Assign -1015 to the return value
         * - ELSE
         *   Build the address and message
         *   Call the store_mail procedure
         */

        /* This procedure should only be called if the BPM status == "Y" */

        retval := 0;

        if (p_proc = '-') then
                begin
                /*
                 * Used Rtrim to retain originally intended 'cast' behaviour.
                 */

                /*if the process_name is not DCS in bag table then
                  get details from flight table. This is beacuse flight
                  may have been created via MWS or FIDS and bags manually
                  added before a BSM was received for the flight. This BSM
                  would have updated the flight details in flight table and
                  now BPMs can be sent thru Gateway. It is a requirement to
                  send BPMs for all bags regardless of whether they were
                  created manually or via BSM if a BSM has been received for
                  the flight.decode has been put to ensure that the query
                  works exactly as it worked before for bags created via BSMs*/

                select
                        decode(rtrim(b.process_name),'DCS',b.process_name,f.process_name),
                        decode(rtrim(b.process_name),'DCS',b.unit_name,f.unit_name),
                        decode(rtrim(b.process_name),'DCS',b.dcsid,f.dcsid),
                        decode(rtrim(b.process_name),'DCS',b.subunit_name,f.subunit_name),
                        to_char(b.pax_id)
                into
                        l_proc,
                        l_unit,
                        l_dcsid,
                        l_subunit,
                        l_pxid
                from
                        bag b,pax p,flight f
                where
                        b.pax_id  = pid and
                        b.bag_tag = tag and
                        p.pax_id  = b.pax_id and
                        f.flight_id = p.flight_id;
                exception
                        when NO_DATA_FOUND then
                                l_proc := '-';
                end;
        else
                l_pxid    := to_char(pid);
                l_proc    := rtrim(p_proc);
                l_unit    := p_unit;
                l_subunit := p_subunit;
                l_dcsid   := p_dcsid;
        end if;

        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_subunit))||','||btype||','
                        ||TO_CHAR(fid)||','||TO_CHAR(bpmid)||','||RTRIM(l_pxid)
                        ||','||tag||','||RTRIM(p_scanner_id);

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

        end if;

end mail_bpm1;
/


