/*                                                          */
/*   THIS FILE IS UNDER RCS - DO NOT MODIFY UNLESS YOU HAVE */
/*   CHECKED IT OUT USING THE COMMAND CHECKOUT.             */
/*                                                          */

/*    Revision history:                                     *
 *                                                          *
 *     $Log: ewdb_create_smmessage.sql,v $
 *     Revision 1.1  2001/04/06 19:07:43  davidk
 *     Initial revision
 *                  *
 ************************************************************/

/************************************************
************ SPECIAL FORMATTED COMMENT **********
EW API FORMATTED COMMENT 
TYPE FUNCTION_PROTOTYPE 

LIBRARY  EWDB_API_LIB 

SUB_LIBRARY STRONG_MOTION-INTERNAL

LANGUAGE SQL

LOCATION THIS_FILE 

PROCEDURE Create_SMMessage 
RETURN_PARAMETER 1 

PROTOTYPE_LOCATION 

RETURN_TYPE NUMBER 

RETURN_VALUE 0
RETURN_DESCRIPTION Success

RETURN_VALUE -1
RETURN_DESCRIPTION Unknown error

RETURN_VALUE -2
RETURN_DESCRIPTION Duplicate Key idSMMessage.  This indicates an
internal error, as the key should be uniquely generated by this
procedure.  If you receive this error, you should scratch your head
and contact your guardian angel.

RETURN_VALUE -3
RETURN_DESCRIPTION Invalid IN_idChan.  IN_idChan must contain
a valid idChan value that represents a primary key in the Chan table.
      
RETURN_VALUE -1X
RETURN_DESCRIPTION Error in Create_Core_idKey(), where X 
indicates error returned by that proc.

RETURN_VALUE -1XX
RETURN_DESCRIPTION The SMMessage record was successfully created, 
but IN_idEvent was specified as non-NULL so this procedure attempted 
to bind the new SMMessage record to the event specified by IN_idEvent,
using Create_Bind().  Create_Bind() returned error XX.  
*NOTE:  The newly create SMMessage record was subsequently deleted, since it could not
be bound to the specified event.


PARAMETER 1 
PARAM_NAME OUT_RetCode
PARAM_TYPE number 
PARAM_DESCRIPTION This parameter holds the return value for the procedure.


PARAMETER 2 
PARAM_NAME OUT_idSMMessage 
PARAM_TYPE number  
PARAM_DESCRIPTION The DB ID of the newly created SMMessage record.  This
number is only valid if OUT_RetCode = 0 upon completion of the procedure.

PARAMETER 3
PARAM_NAME IN_idChan 
PARAM_TYPE number 
PARAM_DESCRIPTION The idChan of the channel with which the new message
is associated.

PARAMETER 4
PARAM_NAME IN_tMotion
PARAM_TYPE number 
PARAM_DESCRIPTION Approximate time of the motions upon which the new 
message is based.  This is the primary time and should be the one
provided by the instrument/person/program that detected the motion.
More specific times for PGA(Peak Ground Acceleration),
PGV, and PGD may optionally be specified in the IN_tPGA, IN_tPGV, 
and IN_tPGD fields.  (seconds since 1970)

PARAMETER 5
PARAM_NAME IN_tLoad
PARAM_TYPE number 
PARAM_DESCRIPTION Time that the record was first loaded into and EW DBMS.
Caller must calculate this parameter.  (seconds since 1970)

PARAMETER 6
PARAM_NAME IN_tAlt
PARAM_TYPE number 
PARAM_DESCRIPTION Optional time that might specify a more accurate time
of motion than IN_tMotion.  IN_iAltCode specifies how this time was
derived.  (seconds since 1970)

PARAMETER 7
PARAM_NAME IN_iAltCode 
PARAM_TYPE number 
PARAM_DESCRIPTION A code to describe how IN_tAlt was derived.  This should
contain the numeric value of one of the SM_ALTCODE_* constants defined
in rw_strongmotionII.h.  (Should be set to SM_ALTCODE_NONE if IN_tAlt is
NULL).

PARAMETER 8
PARAM_NAME IN_tPGA
PARAM_TYPE number 
PARAM_DESCRIPTION Optional value that specifies the time of the Peak Ground
Acceleration(PGA) associated with this message.  (seconds since 1970)

PARAMETER 9
PARAM_NAME IN_tPGV
PARAM_TYPE number 
PARAM_DESCRIPTION Optional value that specifies the time of the Peak Ground
Velocity(PGV) associated with this message.  (seconds since 1970)

PARAMETER 10
PARAM_NAME IN_tPGA
PARAM_TYPE number 
PARAM_DESCRIPTION Optional value that specifies the time of the Peak Ground
Displacement(PGD) associated with this message.  (seconds since 1970)

PARAMETER 11
PARAM_NAME IN_idEvent
PARAM_TYPE number 
PARAM_DESCRIPTION Optional DB idEvent that identifies an Event with which
the new SMMessage should be associated.  Set to NULL if the message should
not be associated with an event.


DESCRIPTION This procedure creates a new strong motion message(SMMessage) 
record in the EW DBMS.  It returns the DB ID(idSMMessage) of the newly 
created record.  To put a complete strong motion message into an Earthworm 
DBMS, the caller should first call Create_SMMessage() to load the base 
message(complete with timestamps), and then call Create_SMMotion() for 
each acceleration, velocity, and displacement measurement in the message.

*************************************************
************************************************/

CREATE OR REPLACE PROCEDURE Create_SMMessage
(OUT_RetCode out number,
 OUT_idSMMessage out number,
 IN_idChan number,
 IN_tMotion number,
 IN_tLoad number,
 IN_tAlt number,
 IN_iAltCode number,
 IN_tPGA number,
 IN_tPGV number,
 IN_tPGD number,
 IN_idEvent number
)
as


Temp_idSMMessage  number;
Temp              number;
Temp_tPGA         number;
Temp_tPGV         number;
Temp_tPGD         number;
State             number;
Temp_idBind       number;

begin

  State       := 0;
  OUT_RetCode := -1;
  

  /**********************************/
  /* Get A New idSMMessage          */
  /**********************************/
  select SMMotionSeq.NEXTVAL into Temp_idSMMessage from sys.dual;
 
  Create_Core_idKey(Temp_idSMMessage);
  if Temp_idSMMessage <= 0 then
    OUT_RetCode := -10 + Temp_idSMMessage;
    return;
  end if;

  State := 1;

  if IN_tPGA = 0 then 
    Temp_tPGA := NULL;
  else
    Temp_tPGA := IN_tPGA;
  end if;

  if IN_tPGV = 0 then 
    Temp_tPGV := NULL;
  else
    Temp_tPGV := IN_tPGV;
  end if;

  if IN_tPGD = 0 then 
    Temp_tPGD := NULL;
  else
    Temp_tPGD := IN_tPGD;
  end if;

  State := 2;

  insert into SMMessage(idSMMessage,idChan,tMotion,tLoad,tAlternate,
                        iAltCode,tPGA,tPGV,tPGD)
                 values(Temp_idSMMessage,IN_idChan,IN_tMotion,IN_tLoad,IN_tAlt,
                        IN_iAltCode,Temp_tPGA, Temp_tPGV, Temp_tPGD);
  OUT_idSMMessage := Temp_idSMMessage;

  State := 3;

  if IN_idEvent IS NOT NULL AND IN_idEvent > 0 then
    Create_Bind(Temp_idBind, IN_idEvent, 'SMMessage', Temp_idSMMessage);
    if Temp_idBind <= 0 then
      OUT_RetCode := -100 + Temp_idBind;
      delete SMMessage where idSMMessage = Temp_idSMMessage;
      return;
    end if;
  end if;
      
  OUT_RetCode := 0;

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    OUT_RetCode := -2;  /* duplicate keyed SMMessage record exists */

  WHEN OTHERS THEN
    if SQLCODE = -2291 then
      /* foreign key error for idChan */
      OUT_RetCode := -3;  /* invalid idChan */
      insert into test values('Create_SMMessage_chan_fk()',State,IN_idChan);
    else
      OUT_RetCode := -1;
      Temp := SQLCODE;
      insert into test values('Create_SMMessage()',State,Temp);
    end if;

END;

