
-- Start of DDL script for ID_AIR_A
-- Generated 5-Jul-07  1:08:03 pm
-- from elite-KE004T3:1

-- Table ID_AIR_A

CREATE TABLE id_air_a
 (
  aira_key                   NUMBER NOT NULL,
  aira_0                     VARCHAR2(240),
  aira_1                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     81920
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_A

ALTER TABLE id_air_a
 ADD CONSTRAINT air_a_fkey FOREIGN KEY (aira_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_A

-- Start of DDL script for ID_AIR_AMADEUS
-- Generated 5-Jul-07  1:08:10 pm
-- from elite-KE004T3:1

-- Table ID_AIR_AMADEUS

CREATE TABLE id_air_amadeus
 (
  air_key                    NUMBER NOT NULL,
  air_reference              VARCHAR2(240),
  air_0                      VARCHAR2(240),
  air_1                      VARCHAR2(240),
  air_2                      VARCHAR2(240),
  air_3                      VARCHAR2(240),
  air_4                      VARCHAR2(240),
  air_5                      VARCHAR2(240),
  air_6                      VARCHAR2(240),
  air_7                      VARCHAR2(240),
  air_8                      VARCHAR2(240),
  air_9                      VARCHAR2(240),
  air_10                     VARCHAR2(240),
  air_11                     VARCHAR2(240),
  air_created_on             DATE
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     286720
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_AMADEUS

ALTER TABLE id_air_amadeus
 ADD CONSTRAINT air_amadeus_pkey PRIMARY KEY (air_key)
 USING INDEX
  PCTFREE    10
  INITRANS   2
  MAXTRANS   255
  TABLESPACE travel_space
 STORAGE (
   INITIAL     81920
   NEXT
   PCTINCREASE
   MINEXTENTS  1
   MAXEXTENTS  2147483645
   )
/


-- End of DDL script for ID_AIR_AMADEUS

-- Start of DDL script for ID_AIR_B
-- Generated 5-Jul-07  1:08:17 pm
-- from elite-KE004T3:1

-- Table ID_AIR_B

CREATE TABLE id_air_b
 (
  airb_key                   NUMBER,
  airb_0                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     81920
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_B

ALTER TABLE id_air_b
 ADD CONSTRAINT air_b_fkey FOREIGN KEY (airb_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_B

-- Start of DDL script for ID_AIR_C
-- Generated 5-Jul-07  1:08:23 pm
-- from elite-KE004T3:1

-- Table ID_AIR_C

CREATE TABLE id_air_c
 (
  airc_key                   NUMBER NOT NULL,
  airc_0                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     163840
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_C

ALTER TABLE id_air_c
 ADD CONSTRAINT air_c_fkey FOREIGN KEY (airc_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_C

-- Start of DDL script for ID_AIR_D
-- Generated 5-Jul-07  1:08:28 pm
-- from elite-KE004T3:1

-- Table ID_AIR_D

CREATE TABLE id_air_d
 (
  aird_key                   NUMBER NOT NULL,
  aird_0                     VARCHAR2(240),
  aird_1                     VARCHAR2(240),
  aird_2                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     163840
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_D

ALTER TABLE id_air_d
 ADD CONSTRAINT air_d_fkey FOREIGN KEY (aird_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_D

-- Start of DDL script for ID_AIR_E
-- Generated 5-Jul-07  1:08:33 pm
-- from elite-KE004T3:1

-- Table ID_AIR_E

CREATE TABLE id_air_e
 (
  aire_key                   NUMBER NOT NULL,
  aire_0                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     81920
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_E

ALTER TABLE id_air_e
 ADD CONSTRAINT air_e_fkey FOREIGN KEY (aire_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- Triggers for ID_AIR_E

CREATE OR REPLACE TRIGGER id_amadeus_interface_load_lf
AFTER INSERT
ON id_air_e
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
 reference_number	NUMBER;
 tmp_serial		    NUMBER;
 sector_1		    VARCHAR2(5);
 sector_2		    VARCHAR2(5);
 sector_3		    VARCHAR2(5);
 sector_4		    VARCHAR2(5);
 sector_5		    VARCHAR2(5);
 dest_code		    VARCHAR2(5);
 ticket_position	NUMBER;
 tkt_number		    VARCHAR2(20);
 tkt_number1        VARCHAR2(20);
 tkt_tvl_date       DATE;
 tkt_arr_date       DATE;
 fbasis             VARCHAR2(10);
 fbasis1            VARCHAR2(10);
 fbasis2            VARCHAR2(10);
 fbasis3            VARCHAR2(10);
 fbasis4            VARCHAR2(10);
 fbasis5            VARCHAR2(10);
 fbasis6            VARCHAR2(10);
 fbasis7            VARCHAR2(10);
 fbasis8            VARCHAR2(10);
 fserial            NUMBER;
 conj_number        NUMBER;
 no_of_conj_tkts    NUMBER;
 company_code		VARCHAR2(10) := '1';
 tkt_type		    VARCHAR2(2);
 tkt_location		VARCHAR2(10);
 tkt_date		    DATE;
 tkt_airline		VARCHAR2(10);
 tkt_air_release	VARCHAR2(10);
 tkt_agent		    VARCHAR2(10);
 tkt_pax            VARCHAR2(75);
 pax_age            VARCHAR2(10) := 'AD';
 tkt_card_find      VARCHAR2(2);
 tkt_card_type		VARCHAR2(10);
 tkt_card_number	VARCHAR2(50);
 tkt_card_amount	NUMBER;
 tkt_class		    VARCHAR2(10);
 tkt_class1		    VARCHAR2(10);
 tmp_find		    VARCHAR2(1);
 tkt_comm_slb1		NUMBER := 0;
 tkt_comm_slb2		NUMBER := 0;
 tkt_comm_slb3		NUMBER := 0;
 tkt_comm_slb4		NUMBER := 0;
 tkt_comm_slb5		NUMBER := 0;
 tkt_comm_per1		NUMBER := 0;
 tkt_comm_per2		NUMBER := 0;
 tkt_comm_per3		NUMBER := 0;
 tkt_comm_per4		NUMBER := 0;
 tkt_comm_per5		NUMBER := 0;
 tkt_comm_amt1		NUMBER := 0;
 tkt_comm_amt2		NUMBER := 0;
 tkt_comm_amt3		NUMBER := 0;
 tkt_comm_amt4		NUMBER := 0;
 tkt_comm_amt5		NUMBER := 0;
 tmp_class          VARCHAR2(10);

 cash_amount		NUMBER;
 sett_mode		    VARCHAR2(1);
 sell_fare		    NUMBER;
 pub_fare           NUMBER;
 v_pub_fare         NUMBER;
 tax_d              NUMBER;
 tax_1              NUMBER;
 tax_2              NUMBER;
 comm_per           NUMBER;
 book_clerk         VARCHAR2(10);
 conj_first_sec     NUMBER;
 conj_last_sec      NUMBER;
 ex_error_text      VARCHAR2(1200);
 err_reason         VARCHAR2(100);
 tvl_date           DATE;
 sale_exec          VARCHAR2(10);
 lpo                VARCHAR2(15);
 cust_code          VARCHAR2(10);
 addi_selling       NUMBER := 0;
 addi_collection    NUMBER := 0;
 cust_amt           NUMBER := 0;
 disc_amt           NUMBER := 0;
 orc_amt            NUMBER := 0;
 spl_disc           NUMBER := 0;
 ex_rate            NUMBER := 1;
 v_ex_Rate          NUMBER := 1;
 tkt_currency       VARCHAR2(10);
 v_tkt_currency     VARCHAR2(10);
 base_currency      VARCHAR2(10);
 exch_rate          NUMBER := 1;
 exch_rate_1          NUMBER := 1;
 sales_tax_1        NUMBER := 0;

 tax1   varchar2(10);
 tax2   varchar2(10);
 tax3   varchar2(10);
 tax4   varchar2(10);
 tax5   varchar2(10);
 tax6   varchar2(10);
 tax7   varchar2(10);
 tax8   varchar2(10);
 tax9   varchar2(10);
 tax10  varchar2(10);
 tax11  varchar2(10);
 tax12  varchar2(10);
 tax13  varchar2(10);
 tax14  varchar2(10);
 m_tax  varchar2(10);
 taxa1  number := 0;
 taxa2  number := 0;
 taxa3  number := 0;
 taxa4  number := 0;
 taxa5  number := 0;
 taxa6  number := 0;
 taxa7  number := 0;
 taxa8  number := 0;
 taxa9  number := 0;
 taxa10 number := 0;
 taxa11 number := 0;
 taxa12 number := 0;
 taxa13 number := 0;
 taxa14 number := 0;

 tax_code   varchar2(10);
 tax_amount number:=0;
 CURSOR ama_a_airline_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(aira_1, 4, 3))) air_code
  FROM id_air_a
  WHERE aira_key = :NEW.aire_key;

 CURSOR ama_d_date_cursor IS
  SELECT TO_DATE(LTRIM(RTRIM(aird_2)), 'YYMMDD' ) sale_date
  FROM id_air_d
  WHERE aird_key = :NEW.aire_key;

 CURSOR ama_i_pax_cursor(p_tkt_serial IN NUMBER) IS
  SELECT LTRIM(RTRIM(SUBSTR(airi_1, 3) )) pax_name
  FROM id_air_i
  WHERE airi_key = :NEW.aire_key
    AND airi_serial = p_tkt_serial;

 CURSOR ama_h_sector_cursor(p_from_serial NUMBER, p_to_serial NUMBER) IS
  SELECT DISTINCT airh_serial, LTRIM(RTRIM(SUBSTR(airh_1,5,3))) f_sector,
    LTRIM(RTRIM(airh_3)) l_sector, LTRIM(RTRIM(SUBSTR(airh_5,12,2))) booking_class
  FROM id_air_h
  WHERE airh_key = :NEW.aire_key
    AND airh_serial BETWEEN p_from_serial AND p_to_serial
  ORDER BY airh_serial;

 CURSOR ticket_sectors IS
  SELECT airh_key,airh_serial sec_serial, LTRIM(RTRIM(SUBSTR(airh_1,5,3))) sec_src_sector,
        LTRIM(RTRIM(airh_3)) sec_dest_sector, LTRIM(RTRIM(SUBSTR(airh_5,12,2))) sec_class,
        LTRIM(RTRIM(SUBSTR(airh_5,1,2))) sec_airline, LTRIM(RTRIM(SUBSTR(airh_5,16,9))) dept_date,
        LTRIM(RTRIM(SUBSTR(airh_5,31,5)))||LTRIM(RTRIM(SUBSTR(airh_5,26,4))) arrv_date,
        LTRIM(RTRIM(SUBSTR(airh_13,1,2))) sec_baggage, LTRIM(RTRIM(SUBSTR(airh_5,7,4))) sec_flight
  FROM id_air_h
  WHERE airh_key = :NEW.aire_key
  ORDER BY airh_serial;

 CURSOR fare_basis_cursor IS
  SELECT substr(airm_0,3,10), substr(airm_1,1,10), substr(airm_2,1,10), substr(airm_3,1,10),
         substr(airm_4,1,10), substr(airm_5,1,10), substr(airm_6,1,10), substr(airm_7,1,10)
  FROM  id_air_m
  WHERE airm_key = :NEW.aire_key
  AND   substr(airm_0,1,2) ='M-';

 CURSOR air_h_tvl_date_cursor IS
  SELECT TO_DATE(LTRIM(RTRIM(substr(airh_5,16,5))), 'DDMON' ) tvl_date
  FROM id_air_h
  WHERE airh_key = :NEW.aire_key
    AND airh_serial = 1;

 CURSOR ama_k_fare_cursor IS
  SELECT DISTINCT DECODE(LTRIM(RTRIM(airk_1)), NULL, LTRIM(RTRIM(SUBSTR(airk_0, 7) )),
             LTRIM(RTRIM(SUBSTR(airk_1,4) )) ) pub_fare, NVL(LTRIM(RTRIM(airk_13)),1) ex_rate,
    DECODE(LTRIM(RTRIM(airk_1)), NULL, LTRIM(RTRIM(SUBSTR(airk_0,4,3))),
              LTRIM(RTRIM(SUBSTR(airk_1,1,3))) ) currency
  FROM id_air_k
  WHERE airk_key = :NEW.aire_key
    AND SUBSTR(airk_0,1,2) = 'K-' ;

 CURSOR ama_net_fare_cursor IS
  SELECT DISTINCT DECODE(LTRIM(RTRIM(airk_1)), NULL, LTRIM(RTRIM(SUBSTR(airk_0, 8) )),
             LTRIM(RTRIM(SUBSTR(airk_1,4) )) ) pub_fare, NVL(LTRIM(RTRIM(airk_14)),1) ex_rate,
    DECODE(LTRIM(RTRIM(airk_1)), NULL, LTRIM(RTRIM(SUBSTR(airk_0,5,3))),
              LTRIM(RTRIM(SUBSTR(airk_1,1,3))) ) currency
  FROM id_air_k
  WHERE airk_key = :NEW.aire_key
    AND SUBSTR(airk_0,1,3) = 'KN-' ;

 CURSOR ama_k_tax_cursor IS
  SELECT DISTINCT NVL(SUBSTR(airk_1,5, INSTR(SUBSTR(airk_1,5),' ')), 0) d_tax,
     NVL(SUBSTR(airk_2,5, INSTR(SUBSTR(airk_2,5),' ')), 0) tax_1,
    (NVL(SUBSTR(airk_3,5, INSTR(SUBSTR(airk_3,5),' ')), 0) + NVL(SUBSTR(airk_4,5, INSTR(SUBSTR(airk_4,5),' ')), 0)  +
     NVL(SUBSTR(airk_5,5, INSTR(SUBSTR(airk_5,5),' ')), 0) + NVL(SUBSTR(airk_6,5, INSTR(SUBSTR(airk_6,5),' ')), 0)  +
     NVL(SUBSTR(airk_7,5, INSTR(SUBSTR(airk_7,5),' ')), 0) + NVL(SUBSTR(airk_8,5, INSTR(SUBSTR(airk_8,5),' ')), 0)  +
     NVL(SUBSTR(airk_9,5, INSTR(SUBSTR(airk_9,5),' ')), 0) + NVL(SUBSTR(airk_10,5, INSTR(SUBSTR(airk_10,5),' ')), 0)   +
     NVL(SUBSTR(airk_11,5, INSTR(SUBSTR(airk_11,5),' ')), 0) + NVL(SUBSTR(airk_12,5, INSTR(SUBSTR(airk_12,5),' ')), 0) +
     NVL(SUBSTR(airk_13,5, INSTR(SUBSTR(airk_13,5),' ')), 0) + NVL(SUBSTR(airk_14,5, INSTR(SUBSTR(airk_14,5),' ')), 0) +
     NVL(SUBSTR(airk_15,5, INSTR(SUBSTR(airk_15,5),' ')), 0) + NVL(SUBSTR(airk_16,5, INSTR(SUBSTR(airk_16,5),' ')), 0) +
     NVL(SUBSTR(airk_17,5, INSTR(SUBSTR(airk_17,5),' ')), 0) + NVL(SUBSTR(airk_18,5, INSTR(SUBSTR(airk_18,5),' ')), 0) +
     NVL(SUBSTR(airk_19,5, INSTR(SUBSTR(airk_19,5),' ')), 0) + NVL(SUBSTR(airk_20,5, INSTR(SUBSTR(airk_20,5),' ')), 0) ) tax_2,
     SUBSTR(airk_1,14,2) tax1, NVL(SUBSTR(airk_1,5, INSTR(SUBSTR(airk_1,5),' ')), 0) taxa1,
     SUBSTR(airk_2,14,2) tax2, NVL(SUBSTR(airk_2,5, INSTR(SUBSTR(airk_2,5),' ')), 0) taxa2,
     SUBSTR(airk_3,14,2) tax3, NVL(SUBSTR(airk_3,5, INSTR(SUBSTR(airk_3,5),' ')), 0) taxa3,
     SUBSTR(airk_4,14,2) tax4, NVL(SUBSTR(airk_4,5, INSTR(SUBSTR(airk_4,5),' ')), 0) taxa4,
     SUBSTR(airk_5,14,2) tax5, NVL(SUBSTR(airk_5,5, INSTR(SUBSTR(airk_5,5),' ')), 0) taxa5,
     SUBSTR(airk_6,14,2) tax6, NVL(SUBSTR(airk_6,5, INSTR(SUBSTR(airk_6,5),' ')), 0) taxa6,
     SUBSTR(airk_7,14,2) tax7, NVL(SUBSTR(airk_7,5, INSTR(SUBSTR(airk_7,5),' ')), 0) taxa7,
     SUBSTR(airk_8,14,2) tax8, NVL(SUBSTR(airk_8,5, INSTR(SUBSTR(airk_8,5),' ')), 0) taxa8,
     SUBSTR(airk_9,14,2) tax9, NVL(SUBSTR(airk_9,5, INSTR(SUBSTR(airk_9,5),' ')), 0) taxa9,
     SUBSTR(airk_10,14,2) tax10, NVL(SUBSTR(airk_10,5, INSTR(SUBSTR(airk_10,5),' ')), 0) taxa10,
     SUBSTR(airk_11,14,2) tax11,  NVL(SUBSTR(airk_11,5, INSTR(SUBSTR(airk_11,5),' ')), 0) taxa11,
     SUBSTR(airk_12,14,2) tax12,  NVL(SUBSTR(airk_12,5, INSTR(SUBSTR(airk_12,5),' ')), 0) taxa12,
     SUBSTR(airk_13,14,2) tax13,  NVL(SUBSTR(airk_13,5, INSTR(SUBSTR(airk_13,5),' ')), 0) taxa13
  FROM id_air_k
  WHERE airk_key = :NEW.aire_key
    AND (SUBSTR(airk_0,1,2) = 'KF' OR SUBSTR(airk_0,1,3) = 'KNT');

 CURSOR ama_t_number_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(airt_0, 8, 10))) tkt_no,
     DECODE(NVL(SUBSTR(airt_0,18,1),'*'), '-', LTRIM(RTRIM(SUBSTR(airt_0, 19, 2))), NULL) conj_no, airt_serial
  FROM id_air_t
  WHERE airt_key = :NEW.aire_key;

 CURSOR ama_fm_comm_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(airf_0, 6))) comm_per
  FROM id_air_f
  WHERE airf_key = :NEW.aire_key
    AND SUBSTR(airf_0,1,2) = 'FM';

 CURSOR ama_fm_comm_cursor1 IS
  SELECT LTRIM(RTRIM(SUBSTR(airf_0, 7))) comm_per
  FROM id_air_f
  WHERE airf_key = :NEW.aire_key
    AND SUBSTR(airf_0,1,2) = 'FM';

 CURSOR ama_c_clerk_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(airc_0, 22,2))) book_clerk
  FROM id_air_c
  WHERE airc_key = :NEW.aire_key;

 CURSOR ama_fp_pay_type_cursor IS
  SELECT  INSTR(airf_0,'CC') , SUBSTR(airf_0, INSTR(airf_0,'CC')+2 , 2 ),
   SUBSTR(airf_0, INSTR(airf_0,'CC')+4,INSTR( SUBSTR(airf_0, INSTR(airf_0,'CC')+4),'/' ) -1 )
 FROM id_air_f
  WHERE airf_key = :NEW.aire_key
    AND SUBSTR(airf_0,1,2) = 'FP';

 CURSOR reference_cursor IS
  SELECT travel_sequence.NEXTVAL
  FROM DUAL;

 CURSOR stock_cursor IS
  SELECT stock_type, stock_agent, stock_location
  FROM id_ticket_stock_master
  WHERE stock_company = company_code
    AND stock_airline = tkt_airline
    AND stock_ticket  = tkt_number
    AND stock_status  = 'N';

 CURSOR bsp_stock_cursor IS
  SELECT stock_type, stock_agent, stock_location, stock_airline
  FROM id_ticket_stock_master
  WHERE stock_company = company_code
    AND stock_airline = (SELECT ticket_bsp_airline FROM id_ticket_control
                         WHERE ticket_control_company = company_code)
    AND stock_ticket  = tkt_number
    AND stock_status  = 'N';

 CURSOR commission_cursor IS
  SELECT comm_slab_1, comm_slab_2, comm_slab_3, comm_slab_4, comm_slab_5
  FROM id_ticket_commission_master
  WHERE comm_company = company_code
    AND comm_airline = tkt_airline
    AND comm_ticket_type = tkt_type
    AND comm_effective_date <= tkt_date
  ORDER BY comm_effective_date DESC;

 CURSOR ticket_check_cursor IS
  SELECT 'V'
  FROM id_ticket_details
  WHERE ticket_airline = tkt_airline
    AND ticket_number   = tkt_number;

 CURSOR air_r_customer_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(airr_0,8,10))) cust_code
  FROM id_air_r
  WHERE airr_key = :NEW.aire_key
    AND SUBSTR(airr_0,4,4) = 'CUST';

 CURSOR air_r_lpo_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(airr_0,7,10))) lpo
  FROM id_air_r
  WHERE airr_key = :NEW.aire_key
    AND SUBSTR(airr_0,4,3) = 'LPO';

 CURSOR air_r_sell_fare_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(airr_0,8))) sell_fare
  FROM id_air_r
  WHERE airr_key = :NEW.aire_key
    AND SUBSTR(airr_0,4,4) = 'SELL';

 CURSOR air_r_coll_fare_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(airr_0,8))) coll_fare
  FROM id_air_r
  WHERE airr_key = :NEW.aire_key
    AND SUBSTR(airr_0,4,4) = 'COLL';

 CURSOR air_r_sales_exec_cursor IS
  SELECT LTRIM(RTRIM(SUBSTR(airr_0,8,10))) sales_exec
  FROM id_air_r
  WHERE airr_key = :NEW.aire_key
    AND SUBSTR(airr_0,4,4) = 'EXEC';

 CURSOR customer_check_cursor IS
  SELECT tar_code
  FROM id_travel_ar_master
  WHERE tar_company = company_code
    AND tar_code    = cust_code;

 CURSOR base_currency_cursor IS
  SELECT setup_base_currency
  FROM   id_company_setup
  WHERE  setup_company = company_code;

 CURSOR exchange_rate_cursor IS
  SELECT NVL(dcurr_customer_rate,1)
  FROM  id_daily_currency_master
  WHERE dcurr_company = company_code
    AND dcurr_code    = tkt_currency
    AND dcurr_date   <= tkt_date
 ORDER BY dcurr_date DESC;

 CURSOR usd_rate_cursor IS
  SELECT NVL(dcurr_customer_rate,1)
  FROM  id_daily_currency_master
  WHERE dcurr_company = company_code
    AND dcurr_code    = 'USD'
    AND dcurr_date    <= tkt_date
 ORDER BY dcurr_date DESC;

CURSOR  class_cursor IS
 SELECT class_code
 FROM   id_class_master
 WHERE  class_code = ltrim(rtrim(tkt_class));

CURSOR  tax_cursor(tt_code varchar2) IS
  SELECT tax_code
  FROM   id_tax_master
  WHERE  tax_company = company_code
  AND  tax_code    = tt_code;

BEGIN
  err_reason := 'Before ama_a_airline_cursor';
  OPEN ama_a_airline_cursor;
  FETCH ama_a_airline_cursor INTO tkt_airline;
  CLOSE ama_a_airline_cursor;

  tkt_air_release := tkt_airline;
  err_reason := 'Before ama_d_date_cursor';
  OPEN ama_d_date_cursor;
  FETCH ama_d_date_cursor INTO tkt_date;
  CLOSE ama_d_date_cursor;
  err_reason := 'Before air_h_tvl_date_cursor';
  OPEN air_h_tvl_date_cursor;
  FETCH air_h_tvl_date_cursor INTO tvl_date;
  CLOSE air_h_tvl_date_cursor;
  err_reason := 'Before ama_c_clerk_cursor';
  OPEN ama_c_clerk_cursor;
  FETCH ama_c_clerk_cursor INTO book_clerk;
  CLOSE ama_c_clerk_cursor;
  err_reason := 'Before ama_h_sector_cursor';
  tmp_serial := 1;
  FOR sec_idx IN ama_h_sector_cursor(1, 4) LOOP

    IF tmp_serial = 1 THEN
      sector_1 := sec_idx.f_sector;
      sector_2 := sec_idx.l_sector;
      dest_code := sec_idx.f_sector;
      tkt_class := sec_idx.booking_class;
    END IF;
    IF tmp_serial = 2 THEN
      sector_2 := sec_idx.f_sector;
      sector_3 := sec_idx.l_sector;
      dest_code := sec_idx.f_sector;
    END IF;
    IF tmp_serial = 3 THEN
      sector_3 := sec_idx.f_sector;
      sector_4 := sec_idx.l_sector;
      dest_code := sec_idx.f_sector;
    END IF;
    IF tmp_serial = 4 THEN
      sector_4 := sec_idx.f_sector;
      sector_5 := sec_idx.l_sector;
      dest_code := sec_idx.f_sector;
    END IF;
    IF tmp_serial < 4 THEN
      tmp_serial := tmp_serial + 1;
    ELSE
      EXIT;
    END IF;
  END LOOP;  /* -- Sector Taking Loop.. . */
  err_reason := 'Before ama_k_fare_cursor';

  -- if class not found in class master, insert thru interface
  OPEN  class_cursor;
  FETCH class_cursor INTO tmp_class;
  IF class_cursor%NOTFOUND then
     insert into id_class_master(class_code, class_description, class_created_by, class_created_on)
     values (ltrim(rtrim(tkt_class)),tkt_class||' Class','AMADEUS',SYSDATE);
  END IF;
  CLOSE class_cursor;

  OPEN  ama_k_fare_cursor;
  FETCH ama_k_fare_cursor INTO pub_fare, ex_rate, tkt_currency;
  CLOSE ama_k_fare_cursor;

  OPEN  ama_net_fare_cursor;
  FETCH ama_net_fare_cursor INTO v_pub_fare, v_ex_rate, v_tkt_currency;
  close ama_net_fare_cursor;

  if nvl(v_pub_fare,0) > 0 then
   pub_fare := nvl(v_pub_fare,0);
  end if;

  if v_ex_rate is not null then
   ex_Rate := v_ex_Rate;
  end if;

  if v_tkt_currency is not null then
   tkt_currency := v_tkt_currency;
  end if;

  OPEN  base_currency_cursor;
  FETCH base_currency_cursor INTO base_currency;
  CLOSE base_currency_cursor;

  OPEN  exchange_rate_cursor;
  FETCH exchange_rate_cursor INTO exch_rate;
  CLOSE exchange_rate_cursor;

  OPEN  usd_rate_cursor;
  FETCH usd_rate_cursor INTO exch_rate_1;
  CLOSE usd_rate_cursor;

  IF LTRIM(RTRIM(tkt_currency)) IS NULL THEN
    tkt_currency := base_currency;
    ex_rate      := 1;
  ELSIF tkt_currency = base_currency then
    ex_rate      := 1;
  end if;
  IF tkt_currency != base_currency THEN
     ex_rate := exch_rate;
  END IF;
  IF (ex_rate IS NULL OR ex_rate <= 0) AND tkt_currency = base_currency THEN
     ex_rate := 1;
  ELSIF (ex_rate IS NULL OR ex_rate <= 0) AND tkt_currency != base_currency THEN
     ex_rate := exch_rate;
  END IF;
  IF TKT_CURRENCY != BASE_CURRENCY THEN
    taxa14 := 3;
    tax14 := 'IPP';
  ELSE
    taxa14 := 3 * EXCH_RATE_1;
    tax14 := 'IPP';
  END IF;

  err_reason := 'Before ama_k_tax_cursor';
/* OPEN  ama_k_tax_cursor;
  FETCH ama_k_tax_cursor INTO tax_d, tax_1, tax_2;
  CLOSE ama_k_tax_cursor;*/

  OPEN  ama_k_tax_cursor;
  FETCH ama_k_tax_cursor INTO tax_d, tax_1, tax_2, tax1, taxa1, tax2, taxa2, tax3, taxa3, tax4, taxa4,
  tax5, taxa5, tax6, taxa6, tax7, taxa7, tax8, taxa8, tax9, taxa9, tax10, taxa10, tax11, taxa11,
  tax12, taxa12, tax13, taxa13;
  CLOSE ama_k_tax_cursor;


/*  commented bcs fare and tax will be in same currency, if its different this needs to be changed
  tax_d := ROUND(tax_d / ex_rate, 2);
  tax_1 := ROUND(tax_1 / ex_rate, 2);
  tax_2 := ROUND(tax_2 / ex_rate, 2);*/


  sale_exec := NULL; lpo := NULL; cust_code := NULL; addi_selling := 0; addi_collection := 0; disc_amt := 0;
  err_reason := 'Before air_r_sales_exec_cursor';
  OPEN  air_r_sales_exec_cursor;
  FETCH air_r_sales_exec_cursor INTO sale_exec;
  CLOSE air_r_sales_exec_cursor;

  if sale_exec is null then
     sale_exec := book_clerk;
  end if;

   err_reason := 'Before air_r_customer_cursor';
  OPEN  air_r_customer_cursor;
  FETCH air_r_customer_cursor INTO cust_code;
  CLOSE air_r_customer_cursor;
  err_reason := 'Before air_r_sell_fare_cursor';
  OPEN  air_r_sell_fare_cursor;
  FETCH air_r_sell_fare_cursor INTO addi_selling;
  CLOSE air_r_sell_fare_cursor;
  err_reason := 'Before air_r_coll_fare_cursor';
  OPEN  air_r_coll_fare_cursor;
  FETCH air_r_coll_fare_cursor INTO addi_collection;
  CLOSE air_r_coll_fare_cursor;

  IF NVL(addi_selling,0) != 0 THEN
    sell_fare := NVL(addi_selling,0);
  ELSE
    sell_fare := pub_fare;
  END IF;
  IF cust_code IS NOT NULL THEN
    OPEN customer_check_cursor;
    FETCH customer_check_cursor INTO cust_code;
    IF customer_check_cursor%NOTFOUND THEN
      cust_code := NULL;
    END IF;
    CLOSE customer_check_cursor;
  END IF;

  err_reason := 'Before ama_fp_pay_type_cursor';
  OPEN ama_fp_pay_type_cursor;
  FETCH ama_fp_pay_type_cursor INTO tkt_card_find, tkt_card_type, tkt_card_number;
  CLOSE ama_fp_pay_type_cursor;
  IF tkt_card_find = 0 THEN
    tkt_card_type := NULL;
    tkt_card_number := NULL;
  END IF;
  err_reason := 'After ama_fp_pay_type_cursor';
  if tkt_card_find > 0 then

    tkt_card_amount := NVL(sell_fare, 0) + NVL(tax_d, 0) + NVL(tax_1, 0) + NVL(tax_2, 0);
    tax14 := null;
    taxa14 := 0;
    cust_amt        := 0;
    cash_amount     := 0;
    sett_mode       := '3';


  ELSIF cust_code IS NULL THEN
    IF NVL(addi_collection ,0) != 0 THEN
      cash_amount := addi_collection;
      disc_amt    := NVL(addi_selling, sell_fare) + NVL(tax_d, 0) + NVL(tax_1, 0) + NVL(tax_2, 0) + nvl(taxa14,0) - addi_collection;
    ELSE
      cash_amount := NVL(sell_fare, 0) + NVL(tax_d, 0) + NVL(tax_1, 0) + NVL(tax_2, 0)  + nvl(taxa14,0);
    END IF;
    tkt_CarD_amount := 0;
    cust_amt        := 0;
    sett_mode   := '1';
  ELSE
    IF NVL(addi_collection ,0) != 0 THEN
      cust_amt    := addi_collection;
      disc_amt    := NVL(addi_selling, sell_fare) + NVL(tax_d, 0) + NVL(tax_1, 0) + NVL(tax_2, 0) + nvl(taxa14,0) - addi_collection;
    ELSE
      cust_amt    := NVL(sell_fare, 0) + NVL(tax_d, 0) + NVL(tax_1, 0) + NVL(tax_2, 0) + nvl(taxa14,0);
    END IF;
    sett_mode   := '2';
    tkt_CarD_amount := 0;
    cash_amount := 0;
  END IF;
  IF sell_fare < pub_fare THEN
    orc_amt := pub_fare - sell_fare;
    IF disc_amt < 0 THEN
      spl_disc := orc_amt + disc_Amt;
    ELSE
      spl_disc := orc_amt;
    END IF;
  ELSE
    orc_amt := 0; spl_disc := 0;
  END IF;

  err_reason := 'Before air_r_lpo_cursor';
  OPEN air_r_lpo_cursor;
  FETCH air_r_lpo_cursor INTO lpo;
  CLOSE air_r_lpo_cursor;


  BEGIN
    err_reason := 'Before ama_fm_comm_cursor';
    OPEN ama_fm_comm_cursor;
    FETCH ama_fm_comm_cursor INTO comm_per;
    CLOSE ama_fm_comm_cursor;
    EXCEPTION WHEN OTHERS THEN
      comm_per := 0;
  END;
  IF NVL(comm_per,0) = 0 THEN
    BEGIN
      err_reason := 'Before ama_fm_comm_cursor1';
      OPEN ama_fm_comm_cursor1;
      FETCH ama_fm_comm_cursor1 INTO comm_per;
      CLOSE ama_fm_comm_cursor1;
      EXCEPTION WHEN OTHERS THEN
        comm_per := 0;
    END;
  END IF;
  err_reason := 'Before commission_cursor';
  OPEN commission_cursor;
  FETCH commission_cursor INTO tkt_comm_slb1, tkt_comm_slb2, tkt_comm_slb3, tkt_comm_slb4,
       tkt_comm_slb5;
  IF commission_cursor%FOUND THEN
    IF NVL(comm_per, tkt_comm_slb1) = tkt_comm_slb1 THEN
        tkt_comm_per1 := NVL(comm_per, tkt_comm_slb1);
        tkt_comm_amt1 := NVL(tkt_comm_per1 * pub_fare /100, 0);
    ELSIF NVL(comm_per, tkt_comm_slb2) = tkt_comm_slb2 THEN
      tkt_comm_per2 := NVL(comm_per, tkt_comm_slb2);
      tkt_comm_amt2 := NVL(tkt_comm_per2 * pub_fare /100, 0);
    ELSIF NVL(comm_per, tkt_comm_slb3) = tkt_comm_slb3 THEN
      tkt_comm_per3 := NVL(comm_per, tkt_comm_slb3);
      tkt_comm_amt3 := NVL(tkt_comm_per3 * pub_fare /100, 0);
    ELSIF NVL(comm_per, tkt_comm_slb4) = tkt_comm_slb4 THEN
      tkt_comm_per4 := NVL(comm_per, tkt_comm_slb4);
      tkt_comm_amt4 := NVL(tkt_comm_per4 * pub_fare /100, 0);
    ELSIF NVL(comm_per, tkt_comm_slb5) = tkt_comm_slb5 THEN
      tkt_comm_per5 := NVL(comm_per, tkt_comm_slb5);
      tkt_comm_amt5 := NVL(tkt_comm_per5 * pub_fare /100, 0);
    ELSE
      tkt_comm_per1 := NVL(comm_per, 0);
      tkt_comm_amt1 := NVL(tkt_comm_per1 * pub_fare /100, 0);
    END IF;
  ELSE    -- Commission Cursor Checking Else.. .
    tkt_comm_per1 := NVL(comm_per,0);
    tkt_comm_amt1 := NVL(tkt_comm_per1 * pub_fare /100, 0);
  END IF;
  CLOSE commission_cursor;
  /* -- Loop From Ticket Table For Multi Tkts Also.. .*/
  FOR tidx IN ama_t_number_cursor LOOP
    tkt_number  := tidx.tkt_no;
    conj_number := tidx.conj_no;
    err_reason := 'Before stock_cursor';
    OPEN stock_cursor;
    FETCH stock_cursor INTO tkt_type, tkt_agent, tkt_location;
    IF stock_cursor%NOTFOUND THEN
      CLOSE stock_cursor;
      OPEN bsp_stock_cursor;
      FETCH bsp_stock_cursor INTO tkt_type, tkt_agent, tkt_location, tkt_airline;
      IF bsp_stock_cursor%NOTFOUND THEN
        CLOSE bsp_stock_cursor;
        err_reason := 'Ticket Not Found In Stock';
      ELSE
        CLOSE bsp_stock_cursor;
      END IF;
    ELSE
      CLOSE stock_cursor;
    END IF; /* -- Stock Checking Over.. .*/
    IF err_reason IS NULL THEN
      err_reason := 'Before ama_i_pax_cursor';
    END IF;
    OPEN  ama_i_pax_cursor(tidx.airt_serial);
    FETCH ama_i_pax_cursor INTO tkt_pax;
    CLOSE ama_i_pax_cursor;

    -- To Pick Pax Category
    IF instr(tkt_pax,'CHD') > 0 then
       pax_age := 'CH';
    ELSIF instr(tkt_pax,'INF') > 0 then
       pax_age := 'IN';
    END IF;

    OPEN ticket_check_cursor;
    FETCH ticket_check_cursor INTO tmp_find;
    IF ticket_check_cursor%NOTFOUND THEN
      OPEN reference_cursor;
      FETCH reference_cursor INTO reference_number;
      CLOSE reference_cursor;
      INSERT INTO id_ticket_details(ticket_company, ticket_type,
        ticket_airline, ticket_airline_release, ticket_agent, ticket_location, ticket_number,
        ticket_status, ticket_reference, ticket_date, ticket_class, ticket_age,
        ticket_pax_name, ticket_sector_1, ticket_sector_2, ticket_sector_3, ticket_sector_4,
        ticket_sector_5, ticket_final_dest, ticket_card_type, ticket_card_number,
        ticket_card_amount, ticket_local_inter, ticket_sales_type, ticket_mode, ticket_bsp_code,
        ticket_executive, ticket_gross_fare, ticket_market_fare, ticket_destination_tax,
        ticket_other_tax_1, ticket_other_tax_2, ticket_sales_tax_1,ticket_net_fare, ticket_orc_total,
        ticket_booking_clerk, ticket_settlement_status, ticket_tour_status,
        ticket_commission_1, ticket_commission_on_1, ticket_commission_amount_1,
        ticket_commission_2, ticket_commission_on_2, ticket_commission_amount_2,
        ticket_commission_3, ticket_commission_on_3, ticket_commission_amount_3,
        ticket_commission_4, ticket_commission_on_4, ticket_commission_amount_4,
        ticket_commission_5, ticket_commission_on_5, ticket_commission_amount_5,
        ticket_cash, ticket_reference_amount_1, ticket_exchange_docno, ticket_lpo,
        ticket_created_on, ticket_created_by, ticket_orc_amount ,
        ticket_special_discount, ticket_flex_v_3, ticket_total_commission, ticket_new_sale_date,
        ticket_department, ticket_discount_on_1, ticket_cash_discount, ticket_customer_1,
        ticket_currency, ticket_ex_rate)
      VALUES( company_code, tkt_type, tkt_airline, tkt_air_release, tkt_agent, tkt_location, SUBSTR(tkt_number,1,10),
        'S', reference_number, tkt_date, ltrim(rtrim(tkt_class)), pax_age,
        tkt_pax, sector_1, sector_2, sector_3, sector_4,
        sector_5, dest_code, tkt_card_type, tkt_card_number,
        NVL(tkt_card_amount,0), 'I', 'NORMAL', NVL(sett_mode,'7'), NULL,
        NVL(sale_exec, 'ID'), sell_fare, pub_fare, tax_d + tax_1 + tax_2 + taxa14,
        0, 0, 0,0, 0,  NVL(book_clerk, 'ID'), 'E', 'N',
        tkt_comm_per1, decode(sell_fare,0, pub_fare,sell_fare), tkt_comm_amt1,
        tkt_comm_per2, decode(sell_fare,0, pub_fare,sell_fare), tkt_comm_amt2,
        tkt_comm_per3, decode(sell_fare,0, pub_fare,sell_fare), tkt_comm_amt3,
        tkt_comm_per4, decode(sell_fare,0, pub_fare,sell_fare), tkt_comm_amt4,
        tkt_comm_per5, decode(sell_fare,0, pub_fare,sell_fare), tkt_comm_amt5,
        NVL(cash_amount,0), NVL(cust_amt,0), NULL, lpo,
        SYSDATE, 'INTERFACE', orc_amt,
        spl_disc, 'N', (tkt_comm_amt1 + tkt_comm_amt2 + tkt_comm_amt3 + tkt_comm_amt4 + tkt_comm_amt5), tvl_date,
        tkt_location, DECODE(sell_fare,0, pub_fare,sell_fare), disc_amt, cust_code ,
        tkt_currency, ex_rate);
      -- Updating Stock Master.. .
         -- Insert into to tax tables
        FOR i in 1..14 LOOP
            if i = 1 then
              tax_code := tax1; tax_amount := taxa1;
            elsif i = 2 then
              tax_code := tax2; tax_amount := taxa2;
            elsif i = 3 then
              tax_code := tax3; tax_amount := taxa3;
            elsif i = 4 then
              tax_code := tax4; tax_amount := taxa4;
            elsif i = 5 then
              tax_code := tax5; tax_amount := taxa5;
            elsif i = 6 then
              tax_code := tax6; tax_amount := taxa6;
            elsif i = 7 then
              tax_code := tax7; tax_amount := taxa7;
            elsif i = 8 then
              tax_code := tax8; tax_amount := taxa8;
            elsif i = 9 then
              tax_code := tax9; tax_amount := taxa9;
            elsif i = 10 then
              tax_code := tax10; tax_amount := taxa10;
            elsif i = 11 then
              tax_code := tax11; tax_amount := taxa11;
            elsif i = 12 then
              tax_code := tax12; tax_amount := taxa12;
            elsif i = 13 then
              tax_code := tax13; tax_amount := taxa13;
            elsif i = 14 then
              tax_code := tax14; tax_amount := taxa14;
            end if;
            if tax_code is not null then
                   OPEN  tax_cursor(tax_code);
                   FETCH tax_cursor into m_tax;
                   IF tax_cursor%NOTFOUND THEN -- IF not found in tax master
                         INSERT INTO id_tax_master(tax_company, tax_code, tax_description, tax_percentage, tax_minimum_amount,
                                           tax_from_amount, tax_to_amount)
                            VALUES(company_code, tax_code, tax_code, 0, 0 ,0, 0);
                    END IF;
                   CLOSE tax_cursor;

                insert into id_ticket_taxes(tt_company, tt_ticket_reference, tt_ticket_number, tt_tax_code, tt_tax_amount)
                values(company_code, reference_number, SUBSTR(tkt_number,1,10),tax_code, tax_amount);
            end if;
        END LOOP;


      UPDATE id_ticket_stock_master
      SET stock_issued = tkt_date,
          stock_status = 'S'
      WHERE stock_company = company_code
        AND stock_airline = tkt_airline
        AND stock_ticket  = SUBSTR(tkt_number,1,10)
        AND stock_status  = 'N';
      IF conj_number IS NOT NULL THEN
        conj_last_sec := 4;
        IF SUBSTR(tkt_number, 9, 2) > conj_number THEN
          no_of_conj_tkts := ( 100 + conj_number ) - SUBSTR(tkt_number, 9, 2);
        ELSE
          no_of_conj_tkts := conj_number - SUBSTR(tkt_number, 9, 2);
        END IF;
        FOR con_idx IN 1 .. no_of_conj_tkts LOOP
          sector_1 := NULL;
          sector_2 := NULL;
          sector_3 := NULL;
          sector_4 := NULL;
          sector_5 := NULL;
          conj_first_sec := conj_last_sec + 1;
          conj_last_sec  := conj_first_sec + 3;
          tmp_serial     := 1;
          FOR sec_idx IN ama_h_sector_cursor(conj_first_sec, conj_last_sec) LOOP
            IF tmp_serial = 1 THEN
              sector_1 := sec_idx.f_sector;
              sector_2 := sec_idx.l_sector;
            END IF;
            IF tmp_serial = 2 THEN
              sector_2 := sec_idx.f_sector;
              sector_3 := sec_idx.l_sector;
            END IF;
            IF tmp_serial = 3 THEN
              sector_3 := sec_idx.f_sector;
              sector_4 := sec_idx.l_sector;
            END IF;
            IF tmp_serial = 4 THEN
              sector_4 := sec_idx.f_sector;
              sector_5 := sec_idx.l_sector;
            END IF;
            tmp_serial := tmp_serial + 1;
          END LOOP;  -- Sector Cursor.. .
          tkt_number := tkt_number + 1;
          err_reason := 'Before INSERT INTO Conjunction';
          INSERT INTO id_ticket_conjunction(conj_company, conj_type, conj_airline,
            conj_location, conj_ticket, conj_serial, conj_parent, conj_xo, conj_refund_status,
            conj_sector_1, conj_sector_2, conj_sector_3, conj_sector_4, conj_sector_5)
          VALUES(company_code, tkt_type, tkt_airline,
            tkt_location, tkt_number, con_idx -1, reference_number, NULL, 'N',
            NVL(sector_1, 'NBO'), sector_2, sector_3, sector_4, sector_5);
          -- Updating Stock Master.. .
          UPDATE id_ticket_stock_master
          SET stock_issued = NVL(tkt_date,SYSDATE),
              stock_status = 'S'
          WHERE stock_company = company_code
            AND stock_airline = tkt_airline
            AND stock_ticket  = tkt_number
            AND stock_status  = 'N';
        END LOOP;  -- Conjunction Loading Loop Over...
      END IF;  -- Conjuntion Exist Checking Over...

      -- Loading Sector Details
      FOR sec_idx1 in ticket_sectors
      LOOP
                tkt_number1 := tkt_number;
                OPEN   fare_basis_cursor;
                FETCH  fare_basis_cursor into fbasis1,fbasis2,fbasis3,fbasis4,fbasis5,fbasis6,fbasis7,fbasis8;
                CLOSE  fare_basis_cursor;

                if sec_idx1.sec_serial = 1 then
                   fbasis := fbasis1;
                elsif sec_idx1.sec_serial = 2 then
                   fbasis := fbasis2;
                elsif sec_idx1.sec_serial = 3 then
                   fbasis := fbasis3;
                elsif sec_idx1.sec_serial = 4 then
                   fbasis := fbasis4;
                elsif sec_idx1.sec_serial = 5 then
                   fbasis := fbasis5;
                elsif sec_idx1.sec_serial = 6 then
                   fbasis := fbasis6;
                elsif sec_idx1.sec_serial = 7 then
                   fbasis := fbasis7;
                else
                   fbasis := fbasis8;
                end if;

                IF sec_idx1.dept_date is not null THEN
                    tkt_tvl_date := to_date(sec_idx1.dept_date,'ddmonhh24mi');
                ELSE
                    tkt_tvl_date := to_date(null);
                END IF;

                IF sec_idx1.arrv_date is not null THEN
                    tkt_arr_date := to_date(sec_idx1.arrv_date,'ddmonhh24mi');
                ELSE
                    tkt_arr_date := to_date(null);
                END IF;

                INSERT INTO id_ticket_sectors(sector_company, sector_reference, sector_ticket, sector_serial,
                            sector_from_sector, sector_to_sector, sector_class, sector_flying_airline,
                            sector_travel_date, sector_baggage_allowed, sector_refund_status, sector_fare_basis,
                            sector_flight_no, sector_valid_from, sector_valid_to)
                VALUES(company_code, reference_number, tkt_number1, sec_idx1.sec_serial, sec_idx1.sec_src_sector,
                            sec_idx1.sec_dest_sector,sec_idx1.sec_class, sec_idx1.sec_airline,
                            tkt_tvl_date, 20,'S', fbasis, sec_idx1.sec_flight,
                            tkt_tvl_date, tkt_arr_date);
      END LOOP;

    END IF; -- Ticket Check Cursor Over..
    CLOSE ticket_check_cursor;
  END LOOP; /* Tkt Table Loop Over.. .*/
  EXCEPTION WHEN OTHERS THEN
   ex_error_text := SUBSTR(SQLERRM,1,1200);
   INSERT INTO id_temp_parameters(p_company, p_key, p_user, p_v_1,
     p_v_2, p_v_3, p_v_4,
     p_v_5, p_v_6, p_v_7,
     p_v_8, p_v_9, p_v_10,
     p_v_11, p_v_12, p_d_1)
    VALUES(company_code, NVL(tkt_number,-999999), 'AMADEUS', SUBSTR(ex_error_text,1,50),
     SUBSTR(ex_error_text,51,100),SUBSTR(ex_error_text,101,150),SUBSTR(ex_error_text,151,200),
     SUBSTR(ex_error_text,201,250),SUBSTR(ex_error_text,251,300),SUBSTR(ex_error_text,301,350),
     SUBSTR(ex_error_text,351,400),SUBSTR(ex_error_text,401,450),SUBSTR(ex_error_text,451,500),
     SUBSTR(ex_error_text,501,50), SUBSTR(err_reason,1,50), SYSDATE);
END;
/

-- End of DDL script for ID_AIR_E

-- Start of DDL script for ID_AIR_F
-- Generated 5-Jul-07  1:08:38 pm
-- from elite-KE004T3:1

-- Table ID_AIR_F

CREATE TABLE id_air_f
 (
  airf_key                   NUMBER NOT NULL,
  airf_serial                NUMBER NOT NULL,
  airf_0                     VARCHAR2(240),
  airf_1                     VARCHAR2(240),
  airf_2                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     286720
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_F

ALTER TABLE id_air_f
 ADD CONSTRAINT air_f_fkey FOREIGN KEY (airf_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_F

-- Start of DDL script for ID_AIR_G
-- Generated 5-Jul-07  1:08:42 pm
-- from elite-KE004T3:1

-- Table ID_AIR_G

CREATE TABLE id_air_g
 (
  airg_key                   NUMBER NOT NULL,
  airg_0                     VARCHAR2(240),
  airg_1                     VARCHAR2(240),
  airg_2                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     40960
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_G

ALTER TABLE id_air_g
 ADD CONSTRAINT air_g_fkey FOREIGN KEY (airg_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_G

-- Start of DDL script for ID_AIR_H
-- Generated 5-Jul-07  1:08:47 pm
-- from elite-KE004T3:1

-- Table ID_AIR_H

CREATE TABLE id_air_h
 (
  airh_key                   NUMBER NOT NULL,
  airh_serial                NUMBER NOT NULL,
  airh_0                     VARCHAR2(240),
  airh_1                     VARCHAR2(240),
  airh_2                     VARCHAR2(240),
  airh_3                     VARCHAR2(240),
  airh_4                     VARCHAR2(240),
  airh_5                     VARCHAR2(240),
  airh_6                     VARCHAR2(240),
  airh_7                     VARCHAR2(240),
  airh_8                     VARCHAR2(240),
  airh_9                     VARCHAR2(240),
  airh_10                    VARCHAR2(240),
  airh_11                    VARCHAR2(240),
  airh_12                    VARCHAR2(240),
  airh_13                    VARCHAR2(240),
  airh_14                    VARCHAR2(240),
  airh_15                    VARCHAR2(240),
  airh_16                    VARCHAR2(240),
  airh_17                    VARCHAR2(240),
  airh_18                    VARCHAR2(240),
  airh_19                    VARCHAR2(240),
  airh_20                    VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     286720
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_H

ALTER TABLE id_air_h
 ADD CONSTRAINT air_h_fkey FOREIGN KEY (airh_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_H

-- Start of DDL script for ID_AIR_I
-- Generated 5-Jul-07  1:08:52 pm
-- from elite-KE004T3:1

-- Table ID_AIR_I

CREATE TABLE id_air_i
 (
  airi_key                   NUMBER NOT NULL,
  airi_serial                NUMBER NOT NULL,
  airi_0                     VARCHAR2(240),
  airi_1                     VARCHAR2(240),
  airi_2                     VARCHAR2(240),
  airi_3                     VARCHAR2(240),
  airi_4                     VARCHAR2(240),
  airi_5                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     696320
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_I

ALTER TABLE id_air_i
 ADD CONSTRAINT air_i_fkey FOREIGN KEY (airi_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_I

-- Start of DDL script for ID_AIR_K
-- Generated 5-Jul-07  1:08:57 pm
-- from elite-KE004T3:1

-- Table ID_AIR_K

CREATE TABLE id_air_k
 (
  airk_key                   NUMBER NOT NULL,
  airk_serial                NUMBER NOT NULL,
  airk_0                     VARCHAR2(240),
  airk_1                     VARCHAR2(240),
  airk_2                     VARCHAR2(240),
  airk_3                     VARCHAR2(240),
  airk_4                     VARCHAR2(240),
  airk_5                     VARCHAR2(240),
  airk_6                     VARCHAR2(240),
  airk_7                     VARCHAR2(240),
  airk_8                     VARCHAR2(240),
  airk_9                     VARCHAR2(240),
  airk_10                    VARCHAR2(240),
  airk_11                    VARCHAR2(240),
  airk_12                    VARCHAR2(240),
  airk_13                    VARCHAR2(240),
  airk_14                    VARCHAR2(240),
  airk_15                    VARCHAR2(240),
  airk_16                    VARCHAR2(240),
  airk_17                    VARCHAR2(240),
  airk_18                    VARCHAR2(240),
  airk_19                    VARCHAR2(240),
  airk_20                    VARCHAR2(240),
  airk_21                    VARCHAR2(240),
  airk_22                    VARCHAR2(240),
  airk_23                    VARCHAR2(240),
  airk_24                    VARCHAR2(240),
  airk_25                    VARCHAR2(240),
  airk_26                    VARCHAR2(240),
  airk_27                    VARCHAR2(240),
  airk_28                    VARCHAR2(240),
  airk_29                    VARCHAR2(240),
  airk_30                    VARCHAR2(240),
  airk_31                    VARCHAR2(240),
  airk_32                    VARCHAR2(240),
  airk_33                    VARCHAR2(240),
  airk_34                    VARCHAR2(240),
  airk_35                    VARCHAR2(240),
  airk_36                    VARCHAR2(240),
  airk_37                    VARCHAR2(240),
  airk_38                    VARCHAR2(240),
  airk_39                    VARCHAR2(240),
  airk_40                    VARCHAR2(240),
  airk_41                    VARCHAR2(240),
  airk_42                    VARCHAR2(240),
  airk_43                    VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     163840
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_K

ALTER TABLE id_air_k
 ADD CONSTRAINT air_k_fkey FOREIGN KEY (airk_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_K

-- Start of DDL script for ID_AIR_L
-- Generated 5-Jul-07  1:09:03 pm
-- from elite-KE004T3:1

-- Table ID_AIR_L

CREATE TABLE id_air_l
 (
  airl_key                   NUMBER NOT NULL,
  airl_serial                NUMBER NOT NULL,
  airl_0                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     81920
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_L

ALTER TABLE id_air_l
 ADD CONSTRAINT air_l_fkey FOREIGN KEY (airl_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_L

-- Start of DDL script for ID_AIR_M
-- Generated 5-Jul-07  1:09:08 pm
-- from elite-KE004T3:1

-- Table ID_AIR_M

CREATE TABLE id_air_m
 (
  airm_key                   NUMBER NOT NULL,
  airm_serial                NUMBER NOT NULL,
  airm_0                     VARCHAR2(240),
  airm_1                     VARCHAR2(240),
  airm_2                     VARCHAR2(240),
  airm_3                     VARCHAR2(240),
  airm_4                     VARCHAR2(240),
  airm_5                     VARCHAR2(240),
  airm_6                     VARCHAR2(240),
  airm_7                     VARCHAR2(240),
  airm_8                     VARCHAR2(240),
  airm_9                     VARCHAR2(240),
  airm_10                    VARCHAR2(240),
  airm_11                    VARCHAR2(240),
  airm_12                    VARCHAR2(240),
  airm_13                    VARCHAR2(240),
  airm_14                    VARCHAR2(240),
  airm_15                    VARCHAR2(240),
  airm_16                    VARCHAR2(240),
  airm_17                    VARCHAR2(240),
  airm_18                    VARCHAR2(240),
  airm_19                    VARCHAR2(240),
  airm_20                    VARCHAR2(240),
  airm_21                    VARCHAR2(240),
  airm_22                    VARCHAR2(240),
  airm_23                    VARCHAR2(240),
  airm_24                    VARCHAR2(240),
  airm_25                    VARCHAR2(240),
  airm_26                    VARCHAR2(240),
  airm_27                    VARCHAR2(240),
  airm_28                    VARCHAR2(240),
  airm_29                    VARCHAR2(240),
  airm_30                    VARCHAR2(240),
  airm_31                    VARCHAR2(240),
  airm_32                    VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     696320
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_M

ALTER TABLE id_air_m
 ADD CONSTRAINT air_m_fkey FOREIGN KEY (airm_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_M

-- Start of DDL script for ID_AIR_O
-- Generated 5-Jul-07  1:09:12 pm
-- from elite-KE004T3:1

-- Table ID_AIR_O

CREATE TABLE id_air_o
 (
  airo_key                   NUMBER NOT NULL,
  airo_serial                NUMBER NOT NULL,
  airo_0                     VARCHAR2(240),
  airo_1                     VARCHAR2(240),
  airo_2                     VARCHAR2(240),
  airo_3                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     81920
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_O

ALTER TABLE id_air_o
 ADD CONSTRAINT air_o_fkey FOREIGN KEY (airo_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_O

-- Start of DDL script for ID_AIR_Q
-- Generated 5-Jul-07  1:09:17 pm
-- from elite-KE004T3:1

-- Table ID_AIR_Q

CREATE TABLE id_air_q
 (
  airq_key                   NUMBER NOT NULL,
  airq_serial                NUMBER NOT NULL,
  airq_0                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     163840
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_Q

ALTER TABLE id_air_q
 ADD CONSTRAINT air_q_fkey FOREIGN KEY (airq_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_Q

-- Start of DDL script for ID_AIR_R
-- Generated 5-Jul-07  1:09:21 pm
-- from elite-KE004T3:1

-- Table ID_AIR_R

CREATE TABLE id_air_r
 (
  airr_key                   NUMBER NOT NULL,
  airr_serial                NUMBER NOT NULL,
  airr_0                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     163840
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- End of DDL script for ID_AIR_R

-- Start of DDL script for ID_AIR_S
-- Generated 5-Jul-07  1:09:28 pm
-- from elite-KE004T3:1

-- Table ID_AIR_S

CREATE TABLE id_air_s
 (
  airs_key                   NUMBER NOT NULL,
  airs_serial                NUMBER NOT NULL,
  airs_0                     VARCHAR2(240),
  airs_1                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     163840
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_S

ALTER TABLE id_air_s
 ADD CONSTRAINT air_s_fkey FOREIGN KEY (airs_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_S

-- Start of DDL script for ID_AIR_T
-- Generated 5-Jul-07  1:09:33 pm
-- from elite-KE004T3:1

-- Table ID_AIR_T

CREATE TABLE id_air_t
 (
  airt_key                   NUMBER NOT NULL,
  airt_serial                NUMBER NOT NULL,
  airt_0                     VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     163840
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_T

ALTER TABLE id_air_t
 ADD CONSTRAINT air_t_fkey FOREIGN KEY (airt_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_T

-- Start of DDL script for ID_AIR_U
-- Generated 5-Jul-07  1:09:38 pm
-- from elite-KE004T3:1

-- Table ID_AIR_U

CREATE TABLE id_air_u
 (
  airu_key                   NUMBER NOT NULL,
  airu_serial                NUMBER NOT NULL,
  airu_0                     VARCHAR2(240),
  airu_1                     VARCHAR2(240),
  airu_2                     VARCHAR2(240),
  airu_3                     VARCHAR2(240),
  airu_4                     VARCHAR2(240),
  airu_5                     VARCHAR2(240),
  airu_6                     VARCHAR2(240),
  airu_7                     VARCHAR2(240),
  airu_8                     VARCHAR2(240),
  airu_9                     VARCHAR2(240),
  airu_10                    VARCHAR2(240),
  airu_11                    VARCHAR2(240),
  airu_12                    VARCHAR2(240),
  airu_13                    VARCHAR2(240),
  airu_14                    VARCHAR2(240),
  airu_15                    VARCHAR2(240),
  airu_16                    VARCHAR2(240),
  airu_17                    VARCHAR2(240),
  airu_18                    VARCHAR2(240),
  airu_19                    VARCHAR2(240),
  airu_20                    VARCHAR2(240)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     65536
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- End of DDL script for ID_AIR_U

-- Start of DDL script for ID_AIR_V
-- Generated 5-Jul-07  1:09:42 pm
-- from elite-KE004T3:1

-- Table ID_AIR_V

CREATE TABLE id_air_v
 (
  airv_key                   NUMBER NOT NULL,
  airv_serial                NUMBER NOT NULL,
  airv_0                     VARCHAR2(4000)
 )
 PCTFREE    10
 PCTUSED    40
 INITRANS   1
 MAXTRANS   255
 TABLESPACE travel_space
 STORAGE   (
      INITIAL     40960
      NEXT
      PCTINCREASE
      MINEXTENTS  1
      MAXEXTENTS  2147483645
   )
/

-- Constraints for ID_AIR_V

ALTER TABLE id_air_v
 ADD CONSTRAINT air_v_fkey FOREIGN KEY (airv_key)
      REFERENCES ID_AIR_AMADEUS(air_key)
/


-- End of DDL script for ID_AIR_V
