/*****************************************************************************
*
* Filename         - $Workfile:   bmm_routes_triggers.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: triggers for bmm_routes table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/
CREATE OR REPLACE TRIGGER tiu_bmm_routes
BEFORE INSERT OR UPDATE ON bmm_routes
FOR EACH ROW
DECLARE

  l_count NUMBER;

BEGIN

  -- Look for parent configuration item
  SELECT COUNT(*)
  INTO   l_count
  FROM   carrier_site_config
  WHERE  site_id = :NEW.site_id
    AND  alpha_code = RPAD(:NEW.carrier, 3)
    AND  config_item = 'SEND_BMM';

  IF (NVL(l_count, 0) = 0) THEN

    -- Parent item does not exist
    RAISE_APPLICATION_ERROR(-20789, 'Parent carrier / site configuration item (SEND_BMM) does not exist');

  END IF;

  -- Set the Route ID
  IF (INSERTING) THEN
    SELECT bmm_route_id_seq.NEXTVAL
    INTO   :NEW.route_id
    FROM   DUAL;
  END IF;

END;
/

    


