/*****************************************************************************
*
* Filename         - $Workfile:   is_bag_transfer.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - Gary Roberts
* Last Update      - CA03003
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - UAS/FALCP/CA/03003
*
* Contents         - Live Schema Function
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2008
*
*****************************************************************************/
PROMPT CREATE OR REPLACE FUNCTION is_bag_transfer

CREATE OR REPLACE FUNCTION is_bag_transfer
    (
     p_bag_tag IN bag.bag_tag%TYPE,
     p_flight_no IN flight.flight_no%TYPE,
     p_flight_date IN flight.flight_date%TYPE,
     p_user_id IN tagtrak_users.user_id%TYPE,
     p_transfer_bag OUT NUMBER
     )
     RETURN NUMBER
IS
    result_count NUMBER(5);
BEGIN
     /* Select config item from CARRIER_SITE_CONFIG */

    SELECT COUNT(1)
    INTO result_count
    FROM bag b, pax p, flight f, tagtrak_users tu, agents a
    WHERE substr(bag_tag,2,9) = substr(p_bag_tag,2,9)
    AND b.pax_id = p.pax_id
    AND p.flight_id = f.flight_id
    AND tu.user_id = p_user_id
    AND tu.agent_code = a.agent_code
    AND chk_ib_flt_if_reqd(substr(f.flight_no,1,3),p.pax_id,a.site_id, p_flight_no, p_flight_date) = 1;
    
    IF result_count > 0
    THEN
        p_transfer_bag := 1;
    ELSE
        p_transfer_bag := 0;
    END IF;
    
    RETURN 0;
END;
/

