/*****************************************************************************
*
* Filename         - $Workfile:   maintain_codeshare.sql  $
*
*                     Name              Date      Version     CR Number 15824
*                      Yomi Kogge       24/03/02  1.0
* 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: codeshare_config table and associated items
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PACKAGE maintain_codeshare

CREATE OR REPLACE PACKAGE maintain_codeshare AS
  PROCEDURE add_codeshare_rule
  (
     OPS_FLTNO     IN VARCHAR2
   , CS_FLTNO      IN VARCHAR2
   , STARTDATE     IN VARCHAR2
   , ENDDATE       IN VARCHAR2
   , FREQUENCY     IN VARCHAR2
   , WEEK_DAY      IN VARCHAR2
   , ACTION        IN VARCHAR2
   , AGENTCODE     IN VARCHAR2
   , RETVAL        OUT NUMBER
  );
  PROCEDURE update_codeshare;
  PROCEDURE update_codeshare_for_day(p_day IN NUMBER);
END  maintain_codeshare;
/
PROMPT CREATE OR REPLACE PACKAGE BODY MAINTAIN_CODESHARE

CREATE OR REPLACE PACKAGE BODY MAINTAIN_CODESHARE AS
 PROCEDURE add_codeshare_rule
  (
     OPS_FLTNO     IN VARCHAR2
   , CS_FLTNO      IN VARCHAR2
   , STARTDATE     IN VARCHAR2
   , ENDDATE       IN VARCHAR2
   , FREQUENCY     IN VARCHAR2
   , WEEK_DAY      IN VARCHAR2
   , ACTION        IN VARCHAR2
   , AGENTCODE     IN VARCHAR2
   , RETVAL        OUT NUMBER
  ) IS
    limit          exception;
    counter        number(10) := 0;
    new_year_start varchar2(5);
    new_year_end   varchar2(5);
 BEGIN
    retval := 0;
    new_year_start := to_char(sysdate, 'yyyy');
    new_year_end   := to_char(sysdate, 'yyyy');

    IF (trunc(to_date(startdate, 'DDMON')) < trunc(sysdate)) THEN
       new_year_start := to_char(sysdate, 'yyyy') + 1;
    END IF;

    IF (trunc(to_date(enddate, 'DDMON')) < trunc(sysdate)) THEN
       new_year_end   := to_char(sysdate, 'yyyy') + 1;
    END IF;
    
    IF (action = 'ADD') THEN
       SELECT count(CS_FLIGHTNO)
         INTO counter
         from codeshare_config
        where OP_FLIGHTNO = OPS_FLTNO;
      IF ( counter < 5) THEN
        insert into codeshare_config (op_flightno,cs_flightno,start_date,end_date,schedule,weekday,agent_code) values
        (
           ops_fltno
          ,cs_fltno
          ,to_date(startdate||new_year_start, 'DDMONYYYY')
          ,to_date(enddate||new_year_end,   'DDMONYYYY')
          ,frequency
          ,week_day
          ,agentcode
        );
      ELSE
        RAISE limit;
      END IF;
    ELSIF (action = 'UPDATE') THEN
        update codeshare_config
           set start_date  =  to_date(STARTDATE||new_year_start, 'DDMONYYYY'),
               end_date    =  to_date(ENDDATE||new_year_end,'DDMONYYYY'),
               schedule    =  FREQUENCY,
               weekday     =  WEEK_DAY
         where OP_FLIGHTNO =  OPS_FLTNO
           and CS_FLIGHTNO =  CS_FLTNO;
    ELSIF (action = 'DELETE') THEN
        delete from codeshare_config
        where  OP_FLIGHTNO = OPS_FLTNO
          and  CS_FLIGHTNO = CS_FLTNO;
    END IF;
 commit;
 EXCEPTION
    when limit then
       retval := 5;
    when others then
      retval := 1;
 END add_codeshare_rule;

 PROCEDURE update_codeshare IS
 cursor get_codeshare_rule is
 select * from codeshare_config;
 BEGIN
   FOR get_codeshare_rule_rec in get_codeshare_rule LOOP
      IF (trunc(get_codeshare_rule_rec.start_date) <= trunc(sysdate)) THEN
         IF (trunc(get_codeshare_rule_rec.end_date) >= trunc(sysdate)) THEN
            IF (get_codeshare_rule_rec.schedule = 'DAILY') THEN
                  insert into code_share (direction,op_carrier,op_fltno,op_date,
                                          cs_carrier,cs_fltno,cs_date,timestamp,airport_code)
                  values(
                   'O'
                  ,rtrim(substr(get_codeshare_rule_rec.OP_FLIGHTNO,1,3))
                  ,rtrim(substr(get_codeshare_rule_rec.OP_FLIGHTNO,4))
                  ,sysdate
                  ,rtrim(substr(get_codeshare_rule_rec.cs_flightno,1,3))
                  ,rtrim(substr(get_codeshare_rule_rec.cs_flightno,4))
                  ,sysdate
                  ,sysdate
                  ,multi_site.get_agent_loc(get_codeshare_rule_rec.agent_code)
                  );
            ELSIF (get_codeshare_rule_rec.schedule = 'WEEKLY') THEN
                IF(get_codeshare_rule_rec.weekday  = to_char(sysdate, 'DY')) THEN
                  insert into code_share (direction,op_carrier,op_fltno,op_date,
                                          cs_carrier,cs_fltno,cs_date,timestamp,airport_code)
                  values(
                   'O'
                  ,rtrim(substr(get_codeshare_rule_rec.OP_FLIGHTNO,1,3))
                  ,rtrim(substr(get_codeshare_rule_rec.OP_FLIGHTNO,4))
                  ,sysdate
                  ,rtrim(substr(get_codeshare_rule_rec.cs_flightno,1,3))
                  ,rtrim(substr(get_codeshare_rule_rec.cs_flightno,4))
                  ,sysdate
                  ,sysdate
                  ,multi_site.get_agent_loc(get_codeshare_rule_rec.agent_code)
                  );
                END IF;
            END IF;
         END IF;
      END IF;
   END LOOP;
   COMMIT;
END update_codeshare;

PROCEDURE update_codeshare_for_day(p_day IN NUMBER) IS
 cursor get_codeshare_rule is
 select * from codeshare_config;
 BEGIN
   FOR get_codeshare_rule_rec in get_codeshare_rule LOOP
      IF (trunc(get_codeshare_rule_rec.start_date) <= trunc(sysdate+p_day)) THEN
         IF (trunc(get_codeshare_rule_rec.end_date) >= trunc(sysdate+p_day)) THEN
            IF (get_codeshare_rule_rec.schedule = 'DAILY') THEN
                  insert into code_share (direction,op_carrier,op_fltno,op_date,
                                          cs_carrier,cs_fltno,cs_date,timestamp,airport_code)
                  values(
                   'O'
                  ,rtrim(substr(get_codeshare_rule_rec.OP_FLIGHTNO,1,3))
                  ,rtrim(substr(get_codeshare_rule_rec.OP_FLIGHTNO,4))
                  ,sysdate+p_day
                  ,rtrim(substr(get_codeshare_rule_rec.cs_flightno,1,3))
                  ,rtrim(substr(get_codeshare_rule_rec.cs_flightno,4))
                  ,sysdate+p_day
                  ,sysdate
                  ,multi_site.get_agent_loc(get_codeshare_rule_rec.agent_code)
                  );
            ELSIF (get_codeshare_rule_rec.schedule = 'WEEKLY') THEN
                IF(get_codeshare_rule_rec.weekday  = to_char(sysdate+p_day, 'DY')) THEN
                  insert into code_share (direction,op_carrier,op_fltno,op_date,
                                          cs_carrier,cs_fltno,cs_date,timestamp,airport_code)
                  values(
                   'O'
                  ,rtrim(substr(get_codeshare_rule_rec.OP_FLIGHTNO,1,3))
                  ,rtrim(substr(get_codeshare_rule_rec.OP_FLIGHTNO,4))
                  ,sysdate+p_day
                  ,rtrim(substr(get_codeshare_rule_rec.cs_flightno,1,3))
                  ,rtrim(substr(get_codeshare_rule_rec.cs_flightno,4))
                  ,sysdate+p_day
                  ,sysdate
                  ,multi_site.get_agent_loc(get_codeshare_rule_rec.agent_code)
                  );
                END IF;
            END IF;
         END IF;
      END IF;
   END LOOP;
   COMMIT;
END update_codeshare_for_day;

END MAINTAIN_CODESHARE;
/
show errors



