/*****************************************************************************
*
* Filename         - $Workfile:   get_bpm_data1.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_data1
                                           
CREATE OR REPLACE procedure get_bpm_data1 (
        flt_id       in out  number,
        pid          in out  number,
        tag2b        in out  char,
        tag3         in out  number,
        btype        in out  number,
        p_bpmid      in out  number,
        retval          out  number,
        bsi             out  char,
        fltno        in out  char,
        fltdate         out  char,
        uno             out  char,
        onwardxfer      out  number,
        cls             out  char,
        dest            out  char,
        bseq            out  number,
        atld            out  char,
        seatno          out  char,
        paxsts          out  char,
        seqno           out  char,
        secno           out  char,
        secsts          out  char,
        nosname         out  char,
        sname           out  char,
        fullinit        out  char,
        pnr             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,
        dcstagno        out  char,
        scanner_id      out  bag.scanner_id%TYPE,
        bvis_subhead    out  bag.bvis_subheader%TYPE

) as

        fid number(10);
        codeshare_fid number(10);
        bsts char(1);
        v_scanner_id bag.scanner_id%TYPE;
        v_flight_id  flight.flight_id%TYPE;
        v_flightid_count  number(3) := 0;

begin

        /*
         * - Initialise variables
         * - Select relevant columns from the bag table where the pax_id,
         *    bag_tag3 and bag_tag2b are the same as the parameters passed in
         *    and the delete_marker is 0.
         * - IF the bsts variable = '-' (ie. no rows found) then
         *   Assign -1002 to the return variable
         *   exit this program
         * - IF btype = 1 or 2 and bsts is not equal to 'Y' then
         *   Assign -1018 to the return variable
         *   exit this program
         * - IF btype = 3 and bsts is not equal to 'U' then
         *   Assign -1018 to the return variable
         *   exit this program
         * - Select the relevant details from the pax table where the pax_id is
         *    equal to the pid parameter (ignoring the delete_marker)
         * - IF flight_id > 0 (ie. a row has been found) then
         *   Select the dcs_flight_no and flight_date from the flight table
         *    where the flight_id is equal to the fid parameter.
         * - ELSE
         *   Assign -1010 to the return value
         * - IF fltno = '-' (ie. no rows found) then
         *   Assign -1018 to the return value
         * - Select user_id from the bpm_data table where the bpmid is equal to
         *    the p_bpmid parameter and pax_id is equal to 0.
         * - IF uid = 0 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 the
         *    relevant parameters.
         */


        /* this is for BPM types 1, 2 and 3 */
        uid    := 0;
        retval := 0;
        fltno  := '-';
        bsts   := '-';

        /*
         * Added to prevent Null fetch errors which occur when output
         * vars are left unassigned when the procedure returns.
         */

        bsi        :=  '';
        fltdate    :=  '';
        uno        :=  '';
        onwardxfer :=  0;
        cls        :=  '';
        dest       :=  '';
        bseq       :=  0;
        atld       :=  '';
        seatno     :=  '';
        paxsts     :=  '';
        seqno      :=  '';
        secno      :=  '';
        secsts     :=  '';
        nosname    :=  '';
        sname      :=  '';
        fullinit   :=  '';
        pnr        :=  '';
        stime      := '';
        locn       :=  '';
        p_dcsid    :=  '';
        bpmver     :=  '';
        dcstagno   := '';
        scanner_id := '';
        bvis_subhead := NULL;

        IF pid <> 0 THEN --  BPMs required for BagFlo when Unknown Tag where pid = 0

          begin
                select
                        b.uld_no,
                        b.bag_source_indicator,
                        b.bag_sequence_no,
                        b.bag_destination,
                        to_char(b.time_loaded, 'DDMONYYYYHH24MISS'),
                        b.authority_to_load_dcs,
                        b.loaded_status,
                        b.location_code,
                        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),
                        b.dcs_bag_tag,
                        b.bvis_subheader
                into
                        uno,
                        bsi,
                        bseq,
                        dest,
                        stime,
                        atld,
                        bsts,
                        locn,
                        proc,
                        unit,
                        p_dcsid,
                        subunit,
                        dcstagno,
                        bvis_subhead
                from
                        bag b, pax p, flight f
                where
                        b.pax_id        =  pid   and
				b.pax_id        =  p.pax_id and
				p.flight_id     =  f.flight_id and
                        b.bag_tag3      =  tag3  and
                        b.bag_tag2b     =  tag2b; 
                        --b.delete_marker check removed, CA2201

                exception
                        when NO_DATA_FOUND then
                                /* UNKNOWN_BAG */
                                retval := -1002;
                                return;

          end;

          -- If the scan was an error then the location and time of the
          -- scanning action will not be held on the bag table because
          -- these values will both be null

          IF btype BETWEEN '10' and '14' THEN
            BEGIN
              SELECT SUBSTR(bag_tag,1,6) LOCATION_CODE
                ,TO_CHAR(scan_time, 'DDMONYYYYHH24MISS') SCAN_TIME
              INTO
                locn
                ,stime
              FROM
                bpm_data
              WHERE
                bpmid  = p_bpmid and
                pax_id = 0;
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                /* no op */
                retval := -1018;
                RETURN;
            END;
          END IF;

        END IF;

        /* check not superceded by events */
        if ((btype = 1 or btype = 2) and bsts != 'Y') then
                /* no op */
                retval := -1018;
                return;
        end if;

        if (btype = 3 and bsts != 'U') then
                /* no op */
                retval := -1018;
                return;
        end if;

        IF pid <> 0 THEN

          begin
                select
                        flight_id,
                        booked_flight_id,
                        pax_class,
                        pax_seat_no,
                        pax_status,
                        pax_dcs_sequence_no,
                        pax_security_status,
                        pax_surname,
                        pax_full_inits,
                        pax_name_record,
                        pax_security_no,
                        to_char(no_same_surname),
                        onward_transfer
                into
                        fid,
                        codeshare_fid,
                        cls,
                        seatno,
                        paxsts,
                        seqno,
                        secsts,
                        sname,
                        fullinit,
                        pnr,
                        secno,
                        nosname,
                        onwardxfer
                from
                        pax p
                where
                        pax_id        = pid; 
                        --delete_marker check removed, CA2201          

                exception
                        when NO_DATA_FOUND then
                                /* UNKNOWN_PAX */
                                retval := -1010;
                                return;
          end;

          begin
                select
                        get_dcs_flight_no( flight_no,dcs_flight_no),
                        flight_date
                into
                        fltno,
                        fltdate
                from
                        flight
                where
                        flight_id = codeshare_fid;

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

        ELSIF pid = 0 AND btype BETWEEN '10' and '13' THEN

        --  BPMs required for BagFlo when Unknown Tag where pid = 0
        --  No information can be obtained from pax or bag without a pax_id (pid)

          BEGIN

                -- the flight_id is the 4th substring in msg_text
                SELECT
                  TO_NUMBER(SUBSTR(msg_text
                       ,INSTR(msg_text,',',1,3) + 1
                       ,INSTR(msg_text,',',1,4) - INSTR(msg_text,',',1,3) - 1)) FLIGHT_ID
                -- the bag tag is the penultimate (7th) substring in msg_text
                  ,SUBSTR(msg_text
                       ,INSTR(msg_text,',',1,6) + 1
                       ,INSTR(msg_text,',',1,7) - INSTR(msg_text,',',1,6) -1) DCS_BAG_TAG
                INTO
                        v_flight_id
                       ,dcstagno
                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);

                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 = v_flight_id;

                IF btype = 11 THEN
                        BEGIN
                               flt_id := NVL(flt_id,v_flight_id);
                               dest := get_dest (flt_id);
                        EXCEPTION
                               when NO_DATA_FOUND then
                               dest := '';
                        END;
                END IF;

          EXCEPTION
              WHEN NO_DATA_FOUND THEN
                fltno := NULL;
                fltdate := NULL;
                proc := 'DCS';
                unit := '1';
                p_dcsid := 'BAGFLO';
                subunit := 'SYM';
          END;

          BEGIN
                  SELECT
                          SUBSTR(bag_tag,1,6) LOCATION_CODE
                          ,TO_CHAR(scan_time, 'DDMONYYYYHH24MISS') SCAN_TIME
                  INTO
                          locn
                          ,stime
                  FROM
                          bpm_data
                  WHERE
                          bpmid  = p_bpmid and
                          pax_id = 0;

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

        END IF;

        begin
                select
                        user_id
                into
                        uid
                from
                        bpm_data
                where
                        bpmid  = p_bpmid and
                        pax_id = 0;

                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;

        /* CA199 CA309 */
        begin
                select
                        DISTINCT bag_source_indicator
                into
                        bsi
                from
                        bag
                where
                        bag_tag = dcstagno;

                exception
                        when NO_DATA_FOUND then
				bsi := 'L'; /* CA309: nothing we can do to find bsi if bag tag unknown
				              , default to most likely case of 'L' */
                        when TOO_MANY_ROWS then
				bsi := 'L'; /* CA309: nothing we can do to find bsi if not the same across
				               duplicate tags, default to most likely case of 'L' */
        end;

        BEGIN
              -- the scanner_id is the last substring (8th of 8)
              -- in msg_text for BPM types 1,2,3,10,11,12,13
              SELECT SUBSTR(msg_text,INSTR(msg_text,',',1,7) + 1
                     ,LENGTH(msg_text) - INSTR(msg_text,',',1,7)) 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 get_bpm_data1;
/


