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

create or replace procedure get_bpm_data2 (
        fid     in out  number,
        p_bpmid in out  number,
        btype   in out  number,
        retval     out  number,
        bsi        out  char,
        fltno   in out  char,
        fltdate    out  char,
        dest    in 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,
        pid     in out  number,
        tag2b   in out  char,
        tag3    in out  number,
        scanner_id out  char,
        bvis_subhead out bag.bvis_subheader%TYPE
) 

as  
begin

        /*
         * - Assign Variables
         * - Select columns from the bpm_data table where the bpmid is equal
         *    to the p_bpmid parameter and the pax_id is equal to 0.
         * - IF uid = 0 (ie. no rows found) then
         *   Assign -1018 to the return value
         * - Select the relevant variables from the flight table, where the
         *    flight_id is equal to to fid parameter.
         * - IF fltno = '-' (ie. no flight number) then
         *   Assign -1018 to the return value
         * - Select the bpm_version from the system_cfg table where the process_name
         *    unit_name, dcsid and subunit_name are equal to their corresponding
         *    variables.
         */

        /* this is for BPM types 4, 5, 6, 7 and 8 */
        uid    := 0;
        retval := 0;
        bsi    := 'L';
        fltno  := '-';
 
        begin
                select
                        user_id,
                        TO_CHAR(scan_time , 'DDMONYYYYHH24MISS'),
                        SUBSTR(bag_tag,1,6) LOCATION_CODE
                into
                        uid,
                        stime,
                        locn
                from
                        bpm_data
                where
                        bpmid  =  p_bpmid and
                        pax_id = 0; 

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

        IF (btype != 6 AND btype != 8) THEN

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

                -- Get the sub-header information from the flight table
                begin
                   select bvis_subheader
                   into bvis_subhead
                   from flight
                   where flight_id = fid;

                exception
                   when NO_DATA_FOUND then
                      bvis_subhead := NULL;
                end;


        ELSIF (btype = 6 or btype = 8) THEN -- need to get bag destination for moving a bag

                begin
                        -- the pax_id is the last (9th) substring in msg_text for BPM types 6 and 8
                        select  TO_NUMBER(SUBSTR(msg_text,INSTR(msg_text,',',1,8) + 1
                               ,LENGTH(msg_text) - INSTR(msg_text,',',1,8))) PAXID
                        into
                                pid
                        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
                        select
                                bag_destination,
                                bvis_subheader
                        into
                                dest,
                                bvis_subhead
                        from
                                bag b
                        where
                                pax_id        =  pid   and
                                bag_tag3      =  tag3  and
                                bag_tag2b     =  tag2b and
                                delete_marker = 0; 
                exception
                        when NO_DATA_FOUND then
                                -- UNKNOWN_BAG
                                retval := -1002;
                                return;
                
                end;

        ELSE
	
                dest := '';
        
        END IF;

        begin
                select
                        get_dcs_flight_no( flight_no,dcs_flight_no),
                        flight_date,
                        process_name,
                        unit_name,
                        dcsid,
                        subunit_name 
                into
                        fltno,
                        fltdate,
                        proc,
                        unit,
                        p_dcsid,
                        subunit 
                from
                        flight
                where
                        flight_id =  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;

    IF btype != 4 THEN
        BEGIN

              -- the scanner_id is the 8th substring in msg_text
              SELECT SUBSTR(msg_text,INSTR(msg_text,',',1,7) + 1
                     ,INSTR(msg_text,',',1,8) - INSTR(msg_text,',',1,7) - 1) 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;
     END IF;

end get_bpm_data2;
/
