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

/*    Revision history:                                     *
 *                                                          *
 *     $Log: ewdb_create_smmotion.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_SMMotion
RETURN_PARAMETER 1 

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_idSMMessage.  IN_idSMMessage must contain
a valid idSMMessage value that represents a primary key in the SMMessage table.
      
RETURN_VALUE -4
RETURN_DESCRIPTION Unknown NO_DATA_FOUND error.  Time to revisit 
the guardian angel.

RETURN_VALUE -1X
RETURN_DESCRIPTION Error in Create_Core_idKey(), where X 
indicates error returned by that proc.


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_idSMMotion 
PARAM_TYPE number  
PARAM_DESCRIPTION The DB ID of the newly created SMMotion record.  This
number is only valid if OUT_RetCode = 0 upon completion of the procedure.

PARAMETER 3
PARAM_NAME IN_idSMMessage 
PARAM_TYPE number 
PARAM_DESCRIPTION The idSMMessage  of the strong motion message with 
which the new motion is associated.

PARAMETER 4
PARAM_NAME IN_iMotionType
PARAM_TYPE number 
PARAM_DESCRIPTION This specifies the type or category of motion.  It should
be one of the P3DB_SMMOTION_TYPE_* constants defined in ewdb_ora_api.h, 
such as P3DB_SMMOTION_TYPE_ACC 

PARAMETER 5
PARAM_NAME IN_dPeriod
PARAM_TYPE number 
PARAM_DESCRIPTION Either the period associated with
a spectral response measurment or a code (-1) indicating that the measurement
is of a peak value, such as PGA.

PARAMETER 6
PARAM_NAME IN_dMeasurement
PARAM_TYPE number 
PARAM_DESCRIPTION The measurment value of the motion.  Measurements should
be in terms of centimeters and seconds, so cm/s/s for acceleration, 
cm/s for velocity, and cm for displacement.  The caller is responsible for 
conversion of data from it's native format to the EWDB format of centimeters
and seconds.  Other known formats(not support by EWDB) are meters, g's,
and digital counts.

DESCRIPTION This procedure creates a new strong motion measurement(SMMotion) 
record in the EW DBMS.  It returns the DB ID(idSMMotion) 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_SMMotion
(
 OUT_RetCode out number,
 OUT_idSMMotion out number,
 IN_idSMMessage number,
 IN_iMotionType number,
 IN_dPeriod number,
 IN_dMeasurement number
)
as


Temp_idSMMotion number;
Temp_idChan     number;
Temp            number;
State           number;

begin

  State := 0;
  /**********************************/
  /* Get A New idSMMotion           */
  /**********************************/
  select SMMotionSeq.NEXTVAL into Temp_idSMMotion from sys.dual;
 
  State := 1;

  Create_Core_idKey(Temp_idSMMotion);
  if Temp_idSMMotion <= 0 then
    OUT_RetCode := -10 + Temp_idSMMotion;
    return;
  end if;

  State := 2;

  select idChan into Temp_idChan from SMMessage 
   where idSMMessage = IN_idSMMessage;

  State := 3;

  insert into SMMotion(idSMMotion,idSMMessage,iMotionType,
                       dPeriod,idChan,dMeasurement)
                values(Temp_idSMMotion,IN_idSMMessage,IN_iMotionType,
                       IN_dPeriod,Temp_idChan,IN_dMeasurement);

  OUT_idSMMotion := Temp_idSMMotion;
  OUT_RetCode    := 0;

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

  WHEN NO_DATA_FOUND THEN
    if State = 2 then
      /* IN_idSMMessage must be invalid, because no matching record was
         found. */
      OUT_RetCode := -3;
    else
      insert into test values('Create_SMMotion_NDF()',State,IN_idSMMessage);
      OUT_RetCode := -4;
    end if;

  WHEN OTHERS THEN
    Temp := SQLCODE;
    insert into test values('Create_SMMotion()',State,Temp);
    insert into test values('Create_SMMotion(2)',Temp_idChan,IN_idSMMessage);
    OUT_idSMMotion := -1;
end;

