/*****************************************************************************
*
* Filename         - $Workfile:   get_bpm_data4.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
*
*****************************************************************************/

PROMPT create or replace procedure get_bpm_data4

create or replace procedure get_bpm_data4 (
        fid            in out  number,
        codeshare_fid  in out  number,
        uld            in out  char,
        btype          in out  number,
        p_bpmid           in   number,
        retval            out  number,
        bsi               out  char,
        fltno          in out  char,
        fltdate           out  char,
        dest           in out  char,
        uldpos            out  char,
        uid            in out  number,
        stime             out  char,
        locn              out  char,
        proc           in out  char,
        unit           in out  char,
        p_dcsid        in out  char,
        subunit        in out  char,
        bpmver            out  char,
        scanner_id        out  char,
        bvis_subhead      out  bag.bvis_subheader%TYPE
) 

as 
        usts char(1);

begin

        /*
         * - Initialise Variables
         * - Select columns from the ulds table where the uld_no is equal to the
         *    uld parameter and the flight_id is equal to the fid parameter.
         * - IF the uld_loaded_status is != 'Y' then
         *   Assign -1018 to the return value
         * - Select columns from the flight table where the flight_id is equal to
         *    the fid parameter
         * - IF fltno = '-' (ie. nothing found) then
         *   Assign -1018 to the return value
         * - Select bpm_version from the system_cfg table where the process_name,
         *    unit_name, dcsid and subunit_name are equal to the relevant fields.
         */

        /* this is for BPM types 9 */
        retval := 0;
        bsi    := 'L';
        usts   := '-';
        fltno  := '-';

        /*
         * Added to prevent Null fetch errors when output vars are
         * left unassigned when the procedure returns.
         */
        fltdate :=  '';
        uldpos  :=  '';
        stime   :=  '';
        locn    :=  '';
        p_dcsid :=  '';
        bpmver  :=  '';
        dest    :=  '';
        bvis_subhead := NULL;
 
        begin
                select
                        uld_hold_no,
                        uld_loaded_status,
                        user_id,
                        location_code,
                        TO_CHAR(uld_load_time , 'DDMONYYYYHH24MISS')
                into
                        uldpos,
                        usts,
                        uid,
                        locn,
                        stime 
                from
                        ulds
                where
                        uld_no    =  uld and
                        flight_id =  fid; 

                exception
                        when NO_DATA_FOUND then
                                /* no op */
                                retval := -1018;
                                return;
        end;

        if (usts != 'Y') then 
                /* no op */
                retval := -1018;
                return;
        end if;

        begin
                select
                        get_dcs_flight_no( flight_no,dcs_flight_no),
                        flight_date,
                        process_name,
                        unit_name,
                        dcsid,
                        subunit_name,
                        bvis_subheader
                into
                        fltno,
                        fltdate,
                        proc,
                        unit,
                        p_dcsid,
                        subunit,
                        bvis_subhead
                from
                        flight
                where
                        flight_id =  codeshare_fid; 

                exception
                        when NO_DATA_FOUND then
                                /* no op */
                                retval := -1018;
                                return;

        end;

        begin
                select
                        bpm_version 
                into
                        bpmver 
                from
                        system_cfg
                where
                        process_name =  proc    and
                        unit_name    =  unit    and
                        dcsid        =  p_dcsid and
                        subunit_name =  subunit; 

                exception
                        when NO_DATA_FOUND then
                                /* no op */
                                retval := -1018;
                                return;
        end;

        BEGIN
              -- the scanner_id is the last substring (7th of 7)
              -- in msg_text for BPM type 9
              SELECT SUBSTR(msg_text,INSTR(msg_text,',',1,6) + 1
                     ,LENGTH(msg_text) - INSTR(msg_text,',',1,6)) SCANNER_ID
              INTO
                      scanner_id
              FROM
                      mail_indiv
              WHERE
                      -- the bpm_id is the fifth substring
                      p_bpmid = SUBSTR(msg_text,INSTR(msg_text,',',1,4) + 1
                     ,INSTR(msg_text,',',1,5) - INSTR(msg_text,',',1,4) - 1);

        EXCEPTION
              WHEN NO_DATA_FOUND THEN
              /* no op */
                retval := -1018;
                RETURN;

        END;

        begin
                dest := get_dest (fid);
        exception        
                when NO_DATA_FOUND then
                        dest := '';      
                when OTHERS then
                        dest := '';
        end;


end get_bpm_data4;
/
