Thanks for helping out Steve. Here is the code. It wasn't written by any of us, it is a stored procedure written by the software vendor. This is only the first SP that continues to call numerous other stored procedures. Plus, they don't comment their code very well. Good luck!
Again, there is something different with this SP. We have successfully used other SP's in our VB app, but this once produces no results.
-----------------------------------------
/*EXECUTE CERSP_ATT0_SELECT_GEN_IDS @pATXR_SOURCE_ID = NULL , @pATSY_ID = "ATN0" ,
@pATXR_DEST_ID = NULL */
create procedure SP_NOTE_UPDATE
/***********************************************************
******* Declare Arguments ******
***********************************************************/
@MEME_CK int = NULL ,
@GRGR_CK int = NULL ,
@NTNT_TEXT char(100) = NULL
as
/*********************************************************
****** Declare Note Stuff Vairables ******
*********************************************************/
declare @pATXR_SOURCE_ID datetime
declare @pATSY_ID char(004)
declare @pATXR_DEST_ID datetime
declare @pATXR_CREATE_DT datetime
declare @pATXR_LAST_UPD_DT datetime
declare @pATNT_SEQ_NO smallint
declare @pLOCK_TOKEN smallint
declare @pATXR_CREATE_USUS char(016)
declare @pATXR_LAST_UPD_USUS char(016)
declare @pATTB_ID char(004)
declare @pATTB_TYPE char(001)
declare @pATXR_DESC char(070)
declare @pATXR_COMPILED_KEY char(100)
declare @pATXR_LOCK_TOKEN smallint
declare @pATNT_TYPE char(004)
declare @pATXR_ATTACH_ID datetime
declare @pATNT_LOCK_TOKEN smallint
declare @p_Class char(30)
declare @pATND_TEXT char(100)
/*********************************************************
****** Declare Member Vairables ******
*********************************************************/
declare @SBSB_CK int
declare @MEME_SFX smallint
declare @MEME_REL char(1)
declare @MEME_ID_NAME char(06)
declare @MEME_LAST_NAME char(35)
declare @MEME_FIRST_NAME char(15)
declare @MEME_MI char(1)
declare @MEME_TITLE char(10)
declare @MEME_ORIG_EFF_DT datetime
declare @MEME_SSN char(009)
declare @MEME_SEX char(001)
declare @MEME_BIRTH_DT datetime
declare @MEME_WRK_PHONE char(020)
declare @MEME_WRK_PHONE_EXT char(004)
declare @MEME_MCTR_STS char(004)
declare @MEME_MCTR_LANG char(004)
declare @MEME_RECORD_NO char(011)
declare @MEME_LATE_ENR_IND char(001)
declare @MEME_MARITAL_STATUS char(001)
/***********************************************************
****** Begin Procedure Code ******
***********************************************************/
/***********************************************************
this sets the @pATSY_ID to ATN0, and calls a
procedure that will return the ATXR_SOURCE_ID and
the ATXR_DEST_ID (these are the two IDS that are
used to link the MEMBER table to a consoldation table
to the note
************************************************************/
select @pATSY_ID = "ATN0"
Begin
EXECUTE CERSP_ATT0_RETURN_GEN_IDS
@pATXR_SOURCE_ID = @pATXR_SOURCE_ID output,
@pATSY_ID = @pATSY_ID output,
@pATXR_DEST_ID = @pATXR_DEST_ID output
end
/***********************************************************
Move values into the fields used for the next stored
procedure
***********************************************************/
select @pATNT_SEQ_NO = 0
select @pLOCK_TOKEN = 1
select @pATTB_ID = "MEME"
select @pATTB_TYPE = "S"
select @pATXR_DESC = "NOTE"
select @pATXR_CREATE_DT = getdate()
select @pATXR_CREATE_USUS = "a0b412"
select @pATXR_LAST_UPD_DT = getdate()
select @pATXR_LAST_UPD_USUS = "a0b412"
select @pATXR_COMPILED_KEY = ""
select @pATXR_LOCK_TOKEN = 0
select @pATNT_TYPE = ""
select @pATXR_ATTACH_ID = NULL
select @pATNT_LOCK_TOKEN = 0
/***********************************************************
This inserts a record into the CER_ATXR_ATTACH_U
table, useing the ID generated in the previous stored
procedures
***********************************************************/
Begin
EXECUTE CERSP_ATNT_INSERT_VIEW
@pATXR_SOURCE_ID,
@pATSY_ID,
@pATXR_DEST_ID,
@pATNT_SEQ_NO,
@pATTB_ID,
@pATTB_TYPE,
@pATXR_DESC,
@pATXR_CREATE_DT ,
@pATXR_CREATE_USUS ,
@pATXR_LAST_UPD_DT ,
@pATXR_LAST_UPD_USUS,
@pATXR_COMPILED_KEY,
@pATXR_LOCK_TOKEN ,
@pATNT_TYPE,
@pATXR_ATTACH_ID ,
@pATNT_LOCK_TOKEN,
@pLOCK_TOKEN
END
/***********************************************************
I think this removes any notes that all ready exist (not
sure though)
***********************************************************/
Begin
EXECUTE CERSP_ATND_DELALL
@p_Class = "CER_APPREC_ATND",
@pLOCK_TOKEN = @pLOCK_TOKEN ,
@pATSY_ID = @pATSY_ID ,
@pATXR_DEST_ID= @pATXR_DEST_ID ,
@pATNT_SEQ_NO = @pATNT_SEQ_NO ,
@pATND_SEQ_NO = NULL
END
/***********************************************************
Here the text is set to what text was passed to the
procedure, and a row is inserted into the
CER_ATND_NOTE_C table (this is the table where the
text information is stored
***********************************************************/
Begin
SELECT @pATND_TEXT = @NTNT_TEXT
EXECUTE CERSP_ATND_INSERT
@p_Class="CER_APPREC_ATND",
@pLOCK_TOKEN = @pLOCK_TOKEN ,
@pATSY_ID = @pATSY_ID ,
@pATXR_DEST_ID = @pATXR_DEST_ID ,
@pATNT_SEQ_NO = @pATNT_SEQ_NO ,
@pATND_SEQ_NO = 0,
@pATND_TEXT = @pATND_TEXT,
@pATND_LOCK_TOKEN = 0
END
/***********************************************************
This I don't understand why it is done, but Facets
does it so I am too.
***********************************************************/
/*Begin
EXECUTE CERSP_ATSY_SEARCH_SORC_TBL
@pATTB_ID = "ATXR",
@pATTB_TYPE = @pATTB_TYPE,
@pATSY_LIST_TYPE = "%"
END*/
/***********************************************************
First I select the relevent row from the
CMC_MEME_MEMBER table, from there I run the Facets
stored procedure that will update the members
information so the new note is reflected
***********************************************************/
SELECT @SBSB_CK = SBSB_CK,
@MEME_SFX = MEME_SFX,
@MEME_REL = MEME_REL,
@MEME_ID_NAME = MEME_ID_NAME,
@MEME_LAST_NAME = MEME_LAST_NAME,
@MEME_FIRST_NAME = MEME_FIRST_NAME,
@MEME_MI = MEME_MID_INIT,
@MEME_TITLE = MEME_TITLE,
@MEME_ORIG_EFF_DT = MEME_ORIG_EFF_DT,
@MEME_SSN = MEME_SSN,
@MEME_SEX = MEME_SEX,
@MEME_BIRTH_DT = MEME_BIRTH_DT,
@MEME_WRK_PHONE = MEME_WRK_PHONE,
@MEME_WRK_PHONE_EXT = MEME_WRK_PHONE_EXT,
@MEME_MCTR_STS = MEME_MCTR_STS,
@MEME_MCTR_LANG = MEME_MCTR_LANG,
@MEME_RECORD_NO = MEME_RECORD_NO,
@MEME_LATE_ENR_IND = MEME_LATE_ENR_IND,
@MEME_MARITAL_STATUS = MEME_MARITAL_STATUS
FROM CMC_MEME_MEMBER MEME WHERE MEME_CK = @MEME_CK
BEGIN
EXECUTE CMCSP_MEME_UPDATE
@p_Class= "CMC_APPREC_MEME",
@pLOCK_TOKEN = 1,
@pMEME_CK = @MEME_CK ,
@pGRGR_CK = @GRGR_CK,
@pSBSB_CK = @SBSB_CK,
@pMEME_SFX = @MEME_SFX ,
@pMEME_REL = @MEME_REL,
@pMEME_ID_NAME = @MEME_ID_NAME ,
@pMEME_LAST_NAME = @MEME_LAST_NAME,
@pMEME_FIRST_NAME = @MEME_FIRST_NAME,
@pMEME_MID_INIT = @MEME_MI,
@pMEME_TITLE = @MEME_TITLE,
@pMEME_ORIG_EFF_DT = @MEME_ORIG_EFF_DT,
@pMEME_SSN = @MEME_SSN,
@pMEME_SEX = @MEME_SEX,
@pMEME_BIRTH_DT = @MEME_BIRTH_DT,
@pMEME_WRK_PHONE = @MEME_WRK_PHONE,
@pMEME_WRK_PHONE_EXT = @MEME_WRK_PHONE_EXT,
@pMEME_MCTR_STS = @MEME_MCTR_STS,
@pMEME_MCTR_LANG = @MEME_MCTR_LANG,
@pMEME_RECORD_NO = @MEME_RECORD_NO,
@pMEME_LATE_ENR_IND = @MEME_LATE_ENR_IND,
@pMEME_MARITAL_STATUS = @MEME_MARITAL_STATUS,
@pATXR_SOURCE_ID = @pATXR_SOURCE_ID ,
@pLOCK_TOKEN_IND = 1
END
