CREATE OR REPLACE PACKAGE email_pkg AS

/**********************************************************************
 *
 * PROCEDURE save_site_config_dtls
 *
 * Description:
 *  create a record in the SITE_CONFIG table if the configuration item does not
 *  exist for the SITE_ID provided. If a matching entry already exists then it
 *  will get updated with new values for DB_VALUE and DESCRIPTION. If the
 *  provided DB_VALUE of the configuration item is null and a matching record
 *  exists, then it will be deleted.
 **********************************************************************/

PROCEDURE save_site_config_dtls(
             p_site_id           IN site_config.site_id%TYPE
            ,p_db_item           IN site_config.db_item%TYPE
            ,p_db_value          IN site_config.db_value%TYPE
            ,p_description       IN site_config.description%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE upd_emailable_report
 *
 * Description:
 *  accept REPORT_ID of the report to be updated and the new value of the
 *  EMAILABLE attribute and update the REPORTS table accordingly.
 **********************************************************************/

PROCEDURE upd_emailable_report(
             p_report_id         IN  reports.report_id%TYPE
            ,p_emailable_value   IN  reports.emailable%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE upd_agent_email_account
 *
 * Description:
 *  For updating the EMAIL_ACCOUNT_ID field in the AGENTS table
 **********************************************************************/

PROCEDURE upd_agent_email_account(
             p_agent_code        IN  agents.agent_code%TYPE
            ,p_email_account_id  IN  email_accounts.email_account_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE upd_email_account
 *
 * Description:
 *  update the matching record in the EMAIL_ACCOUNTS table
 *  return -20784, 'No matching record found' if no matching record exists
 ***********************************************************************/

PROCEDURE upd_email_account(
             p_email_account_id  IN  email_accounts.email_account_id%TYPE
            ,p_email_account     IN  email_accounts.email_account%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE add_email_account
 *
 * Description:
 *  insert a new record in the EMAIL_ACCOUNTS table and return the
 *  EMAIL_ACCOUNT_ID of the new record as an output parameter. If
 *  the EMAIL_ACCOUNT being inserted is a duplicate, then return
 *  -20028, 'record is the same as an existing one, update it'
 ***********************************************************************/

PROCEDURE add_email_account(
             p_email_account     IN  email_accounts.email_account%TYPE
            ,p_email_account_id  OUT email_accounts.email_account_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE del_email_account
 *
 * Description:
 *  accept EMAIL_ACCOUNT_ID of the record to be deleted and delete the
 *  matching record in the EMAIL_ACCOUNTS table.
 *  return -20784, 'No matching record found' if no matching record exists.
 ***********************************************************************/

PROCEDURE del_email_account(
             p_email_account_id  IN  email_accounts.email_account_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE upd_email_address
 *
 * Description:
 *  update the matching record in the EMAIL_ADDRESSES table.
 *  return -20784, 'No matching record found' if no matching
 *  record exists.
 ***********************************************************************/

PROCEDURE upd_email_address(
             p_email_address_id  IN  email_addresses.email_address_id%TYPE
            ,p_agent_code        IN  email_addresses.agent_code%TYPE
            ,p_email_address     IN  email_addresses.email_address%TYPE
            ,p_sita_address      IN  email_addresses.SITA_ADDRESS%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE add_email_address
 *
 * Description:
 *  insert a new record in the EMAIL_ADDRESSES table and return the
 *  EMAIL_ADDRESS_ID of the new record as an output parameter. If
 *  the record being inserted is a duplicate, then return
 *  -20028, 'record is the same as an existing one, update it'
 ***********************************************************************/

PROCEDURE add_email_address(
             p_agent_code        IN  email_addresses.agent_code%TYPE
            ,p_email_address     IN  email_addresses.email_address%TYPE
            ,p_sita_address      IN  email_addresses.sita_address%TYPE
            ,p_email_address_id  OUT email_addresses.email_address_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE del_email_address
 *
 * Description:
 *  delete the matching record in the EMAIL_ADDRESSES table.
 *  return -20784, 'No matching record found' if
 *  no matching record exists
 ***********************************************************************/

PROCEDURE del_email_address(
             p_email_address_id  IN email_addresses.email_address_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            );

/**********************************************************************
 *
 * PROCEDURE add_email_trace
 *
 * Description:
 *  used by MWS to create a log in EMAIL_TRACE table for each email that is sent.
 *  p_transmission_date should be a string in format 'DD-MM-YYYY HH24.MI.SS'which
 *  will be converted into a date data type before inserting a record.
 *  Appropriate errors will be raised if location, agent or report codes
 *  passed as input are invalid (20782, -20781 and -20785 respectively).
 *  The procedure will attempt to derive FLIGHT_ID if only FLIGHT_NO and
 *  FLIGHT_DATE are provided and vice versa by querying the FLIGHT table.
 *  If it fails to find a matching flight then the table will be populated using
 *  the partial flight details provided but additional details will not
 *  be worked out.If the value of the transmission failure parameter is undefined
 *  or other than Y or N (yes/no) then it will default to N.
 ***********************************************************************/

PROCEDURE add_email_trace(
             p_report_id                      IN   email_trace.report_id%TYPE
            ,p_sender_user_id                 IN   email_trace.sender_user_id%TYPE
            ,p_agent_code                     IN   email_trace.agent_code%TYPE
            ,p_recipient_email_address        IN   email_trace.recipient_email_address%TYPE
            ,p_transmission_date              IN   VARCHAR2
            ,p_report_format                  IN   email_trace.report_format%TYPE
            ,p_location_code                  IN   email_trace.location_code%TYPE
            ,p_flight_id                      IN   email_trace.flight_id%TYPE
            ,p_flight_no                      IN   email_trace.flight_no%TYPE
            ,p_flight_date                    IN   email_trace.flight_date%TYPE
            ,p_transmission_failure           IN   email_trace.transmission_failure%TYPE
            ,p_error_message                  OUT  error.error_description%TYPE
            ,p_error_number                   OUT  error.error_id%TYPE
            );
END email_pkg;
/
CREATE OR REPLACE PACKAGE BODY email_pkg AS

process   VARCHAR2(30)  DEFAULT NULL;

FUNCTION get_error_description (errid in error.error_id%TYPE)
return varchar2
IS
 errd error.error_description%TYPE ;
BEGIN

  if errid = 0 then
     return ' ';
  end if;

 select error_description
    into errd
    from error
    where error_id = errid;
  return   errd;

  EXCEPTION
    WHEN OTHERS THEN
     errd := 'Unknown Database Error '||errid ;
  return   errd;
END;


PROCEDURE save_site_config_dtls(
             p_site_id           IN site_config.site_id%TYPE
            ,p_db_item           IN site_config.db_item%TYPE
            ,p_db_value          IN site_config.db_value%TYPE
            ,p_description       IN site_config.description%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
dummy NUMBER;
BEGIN
   process := 'save_site_config_dtls : ';
   p_error_number  := pkg_constants.success;
   BEGIN
   -- Test to see if the location_code exists on the database
     SELECT 1
       INTO dummy
       FROM site_config
      WHERE db_item = p_db_item
        AND site_id = p_site_id;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
     BEGIN
       IF p_db_value IS NOT NULL THEN
          INSERT INTO site_config (site_id, db_item, db_value, description)
               VALUES (p_site_id,p_db_item,p_db_value,p_description);
       ELSE
          p_error_number  := pkg_constants.error_invalid_config_value;
          p_error_message := get_error_description(p_error_number);
       END IF;
       RETURN;
     END;
   END;

   IF p_db_value IS NOT NULL THEN
      UPDATE site_config
         SET  db_value    = p_db_value
             ,description = p_description
       WHERE  db_item = p_db_item
         AND  site_id = p_site_id;
   ELSE
      DELETE FROM site_config
            WHERE db_item = p_db_item
              AND site_id = p_site_id;
   END IF;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END save_site_config_dtls;

PROCEDURE upd_emailable_report(
             p_report_id         IN  reports.report_id%TYPE
            ,p_emailable_value   IN  reports.emailable%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
BEGIN
   process := 'upd_emailable_report : ';
   p_error_number  := pkg_constants.success;

    UPDATE  reports
       SET  emailable    = p_emailable_value
     WHERE  report_id    = p_report_id;

    IF SQL%ROWCOUNT = 0 THEN
       p_error_number  := pkg_constants.error_invalid_report;
       p_error_message := get_error_description(p_error_number);
    END IF;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END upd_emailable_report;

PROCEDURE upd_agent_email_account(
             p_agent_code        IN  agents.agent_code%TYPE
            ,p_email_account_id  IN  email_accounts.email_account_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
PARENT_NOT_FOUND   EXCEPTION;
PRAGMA EXCEPTION_INIT(PARENT_NOT_FOUND,-02291);
BEGIN
    process := 'upd_agent_email_account : ';
    p_error_number  := pkg_constants.success;

    UPDATE  agents
       SET  email_account_id = p_email_account_id
     WHERE  agent_code       = p_agent_code;

    IF SQL%ROWCOUNT = 0 THEN
      p_error_number  := pkg_constants.error_invalid_agent;
      p_error_message := get_error_description(p_error_number);
      RETURN;
    END IF;
EXCEPTION
WHEN PARENT_NOT_FOUND THEN
   p_error_number  := pkg_constants.error_invalid_email_acct_id;
   p_error_message := get_error_description(p_error_number);
   RETURN;
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END upd_agent_email_account;

PROCEDURE upd_email_account(
             p_email_account_id  IN  email_accounts.email_account_id%TYPE
            ,p_email_account     IN  email_accounts.email_account%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
BEGIN
    process := 'upd_email_account : ';
    p_error_number  := pkg_constants.success;

    UPDATE  email_accounts
       SET  email_account    = p_email_account
     WHERE  email_account_id = p_email_account_id;

    IF SQL%ROWCOUNT = 0 THEN
       p_error_number  := pkg_constants.error_record_not_found;
       p_error_message := get_error_description(p_error_number);
    END IF;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END upd_email_account;

PROCEDURE add_email_account(
             p_email_account     IN  email_accounts.email_account%TYPE
            ,p_email_account_id  OUT email_accounts.email_account_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
BEGIN
    process := 'add_email_account : ';
    p_error_number  := pkg_constants.success;

    INSERT INTO  email_accounts (email_account)
         VALUES  (p_email_account) RETURNING email_account_id INTO p_email_account_id;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
   p_error_number  := pkg_constants.error_duplicate_entry;
   p_error_message := get_error_description(p_error_number);
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END add_email_account;

PROCEDURE del_email_account(
             p_email_account_id  IN  email_accounts.email_account_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
BEGIN
    process := 'del_email_account : ';
    p_error_number  := pkg_constants.success;

    DELETE FROM   email_accounts WHERE email_account_id = p_email_account_id;

    IF SQL%ROWCOUNT = 0 THEN
       p_error_number  := pkg_constants.error_record_not_found;
       p_error_message := get_error_description(p_error_number);
    END IF;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END del_email_account;

PROCEDURE upd_email_address(
             p_email_address_id  IN  email_addresses.email_address_id%TYPE
            ,p_agent_code        IN  email_addresses.agent_code%TYPE
            ,p_email_address     IN  email_addresses.email_address%TYPE
            ,p_sita_address      IN  email_addresses.SITA_ADDRESS%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
BEGIN
    process := 'upd_email_address : ';
    p_error_number  := pkg_constants.success;

    UPDATE  email_addresses
       SET  agent_code    = p_agent_code
           ,email_address = p_email_address
           ,sita_address  = NVL(p_sita_address,sita_address)
     WHERE  email_address_id = p_email_address_id;

    IF SQL%ROWCOUNT = 0 THEN
       p_error_number  := pkg_constants.error_record_not_found;
       p_error_message := get_error_description(p_error_number);
    END IF;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END upd_email_address;


PROCEDURE add_email_address(
             p_agent_code        IN  email_addresses.agent_code%TYPE
            ,p_email_address     IN  email_addresses.email_address%TYPE
            ,p_sita_address      IN  email_addresses.SITA_ADDRESS%TYPE
            ,p_email_address_id  OUT email_addresses.email_address_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
dummy NUMBER;
BEGIN
   process := 'add_email_address : ';
   p_error_number  := pkg_constants.success;

   BEGIN
   -- Test to see if the agent_code exists on the database
     SELECT 1
       INTO dummy
       FROM agents
      WHERE agent_code = p_agent_code;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
         p_error_number  := pkg_constants.error_invalid_agent;
         p_error_message := get_error_description(p_error_number);
         RETURN;
   END;

   INSERT INTO  email_addresses (agent_code,email_address,sita_address)
        VALUES  (p_agent_code,p_email_address,NVL(p_sita_address,'N'))
     RETURNING email_address_id INTO p_email_address_id;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
   p_error_number  := pkg_constants.error_duplicate_entry;
   p_error_message := get_error_description(p_error_number);
WHEN OTHERS THEN

   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END add_email_address;

PROCEDURE del_email_address(
             p_email_address_id  IN email_addresses.email_address_id%TYPE
            ,p_error_message     OUT error.error_description%TYPE
            ,p_error_number      OUT error.error_id%TYPE
            )
IS
BEGIN
    process := 'del_email_address : ';
    p_error_number  := pkg_constants.success;

    DELETE FROM   email_addresses WHERE email_address_id = p_email_address_id;

    IF SQL%ROWCOUNT = 0 THEN
       p_error_number  := pkg_constants.error_record_not_found;
       p_error_message := get_error_description(p_error_number);
    END IF;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END del_email_address;


PROCEDURE add_email_trace(
             p_report_id                      IN   email_trace.report_id%TYPE
            ,p_sender_user_id                 IN   email_trace.sender_user_id%TYPE
            ,p_agent_code                     IN   email_trace.agent_code%TYPE
            ,p_recipient_email_address        IN   email_trace.recipient_email_address%TYPE
            ,p_transmission_date              IN   VARCHAR2
            ,p_report_format                  IN   email_trace.report_format%TYPE
            ,p_location_code                  IN   email_trace.location_code%TYPE
            ,p_flight_id                      IN   email_trace.flight_id%TYPE
            ,p_flight_no                      IN   email_trace.flight_no%TYPE
            ,p_flight_date                    IN   email_trace.flight_date%TYPE
            ,p_transmission_failure           IN   email_trace.transmission_failure%TYPE
            ,p_error_message                  OUT  error.error_description%TYPE
            ,p_error_number                   OUT  error.error_id%TYPE
            )IS
   PRAGMA        AUTONOMOUS_TRANSACTION;
   dummy         PLS_INTEGER;
   l_flight_id   email_trace.flight_id%TYPE;
   l_flight_no   email_trace.flight_no%TYPE;
   l_flight_date email_trace.flight_date%TYPE;
   l_site_id     site_info.site_id%TYPE;
BEGIN
   process := 'add_email_trace : ';
   p_error_number  := pkg_constants.success;
   BEGIN
   -- Test to see if the location_code exists on the database
     SELECT 1
       INTO dummy
       FROM location
      WHERE location_code = p_location_code;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
         p_error_number  := pkg_constants.error_invalid_location;
         p_error_message := get_error_description(p_error_number);
         RETURN;
   END;
   BEGIN
   -- Test to see if the agent_code exists on the database
     SELECT site_id
       INTO l_site_id
       FROM agents
      WHERE agent_code = p_agent_code;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
         p_error_number  := pkg_constants.error_invalid_agent;
         p_error_message := get_error_description(p_error_number);
         RETURN;
   END;
   BEGIN
   -- Test to see if the report exists on the database
     SELECT 1
       INTO dummy
       FROM reports
      WHERE report_id = p_report_id;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
         p_error_number  := pkg_constants.error_invalid_report;
         p_error_message := get_error_description(p_error_number);
         RETURN;
   END;
   --attempt to derive flight_id if only flight_no and flight_date are provided
   --and vice-versa by querying flight table. If a matching flight is not found
   --then the the table will be populated using the partial flight details provided
   --and additional details will not be worked-out.
   l_flight_id   := p_flight_id;
   l_flight_no   := p_flight_no;
   l_flight_date := p_flight_date;

     IF    p_flight_id IS NOT NULL
       AND (p_flight_no IS NULL OR p_flight_date IS NULL)
     THEN
       BEGIN
         SELECT flight_no,
               flight_date
          INTO l_flight_no,
               l_flight_date
          FROM flight
         WHERE flight_id = p_flight_id;
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
           NULL;
       END;
     ELSIF  p_flight_id IS NULL
       AND (p_flight_no IS NOT NULL AND  p_flight_date IS NOT NULL)
     THEN
       BEGIN
         SELECT flight_id
          INTO l_flight_id
          FROM flight_outbound_v1
         WHERE flight_no = p_flight_no
           AND flight_date = p_flight_date
           AND site_id = l_site_id;
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
         NULL;
       END;
     END IF;

   INSERT INTO email_trace (report_id,sender_user_id,agent_code
                            ,recipient_email_address,transmission_date
                            ,report_format,location_code,flight_id,flight_no
                            ,flight_date,transmission_failure
                            )
        VALUES             (p_report_id,p_sender_user_id,p_agent_code
                            ,p_recipient_email_address
                            ,TO_DATE(p_transmission_date,'DD-MM-YYYY HH24.MI.SS')
                            ,p_report_format,p_location_code,l_flight_id
                            ,l_flight_no,l_flight_date
                            ,DECODE(UPPER(p_transmission_failure)
                                    ,'Y','Y'
                                    ,'N','N'
                                    ,'N'
                                   )
                            );
   COMMIT;
EXCEPTION
WHEN OTHERS THEN
   add_app_msg.log_oracle_error(process,SQLERRM,SQLCODE);
   p_error_number  := pkg_constants.error_unhandled_exception;
   p_error_message := get_error_description(p_error_number);
END add_email_trace;

END email_pkg;
/
