SQL Server Performance

SP Help

Discussion in 'Getting Started' started by Janette_I, Mar 10, 2008.

  1. Janette_I New Member

    Good Day,
    I have an inquiry regarding my stored procedure. It goes like this, before checking and updating of records
    happened in the front end (VB6) and as of today checking and updating of record is thru stored procedure
    my stored procedure will delete record per crew, per user in my temporary table then will check if record is still valid if not valid it will
    insert new record in my temporary table and will update affected record.
    It seems that my stored procedure cause slowness and blocking error. How can I eliminate this?
    thanks,
    Janette
  2. Akthar New Member

    maybe if you can have a look at profiler, to have some information on what's going on during execution.
  3. Adriaan New Member

    You need to post (some of) your code before we can begin to make any suggestions.
  4. satya Moderator

  5. Adriaan New Member

    Perhaps you are using SELECT INTO syntax like this ...
    SELECT col1, col2, col3
    INTO #Temp_Table
    FROM Permanent_Table
    This will often cause blocking. Rewrite as:

    CREATE TABLE #Temp_Table (col1 datatype ...........)
    INSERT INTO #TempTable
    SELECT col1, col2, col3
    FROM Permanent_Table
  6. FrankKalis Moderator

    Without seeing the actual code, it's like a shot in the dark to suggest anything.
  7. Janette_I New Member

    I try to add with (rowlock) in some of my update statement.
    here is the code...
    it's quite long [:)]
    CREATE PROCEDURE [dbo].[sp_DocumentValidity]
    (@SeqNo int,
    @ApplUserName varchar(10)
    )
    AS
    SET NOCOUNT ON
    --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    Begin
    declare @VessCode int
    declare @DepDate datetime
    declare @ConLen int
    declare @PosiCode int
    declare @ChecklistNo int
    declare @ChecklistPerVes int
    declare @VesCat varchar(2)
    declare @VesPool int
    declare @DocValPerPool int
    declare @DocValPerPrin int
    declare @DocValPerProcessing int
    declare @DocVal int
    declare @ValEXSO char(1)
    declare @VesPrin int
    declare @ExpectedSignOff datetime
    declare @ConUnit varchar(1)
    declare @PosRank varchar(1)
    declare @intTrainCenter int
    declare @VesFlag int
    declare @No varchar(20)
    declare @TranNo varchar(20)
    declare @expired varchar(1)
    declare @tranDateIssued datetime
    declare @tranDateValidity datetime
    declare @Destination varchar(100)
    declare @intMTC int
    declare @MTCExpired char(1)
    /*Processing Requirements Document Validity*/
    declare @PrreDocValPool int
    --declare @PrreDayMoValPool varchar(1)
    --declare @PrreExSoPool varchar(1)
    declare @PrreDocValPrin int
    --declare @PrreDayMoValPrin varchar(1)
    --declare @PrreExSoPrin varchar(1)
    declare @PrreDocVal int
    --declare @PrreDayMoVal varchar(1)
    --declare @PrreExSo varchar(1)
    declare @PrreDocVess int --- Per Vessel
    declare @gDocVal int
    declare @gDocMoVal varchar(1)
    declare @gDocExSo varchar(1)
    declare @gDocAddDepDocVal varchar(1)
    set @gDocVal=0
    declare @preDoc int
    declare @cntDoc int
    declare @QualDocVal datetime
    declare @preVal datetime
    declare @TranCnt int
    declare @intPOCE int
    declare @BotyPOCE int
    declare @BotyDocu int
    declare @BotyTrceCode int
    declare @CntBook int
    declare @BookValidPoce int
    declare @gNewDocVal datetime
    declare @intCountHDec int
    declare @intCountPEME int
    declare @HDecVal datetime
    declare @LicNeeded varchar(1)
    declare @intPoceCode int
    declare @LicNo varchar(35)
    declare @intCountPoce int
    declare @LicGOCVal datetime
    declare @intCertId int
    declare @VesClass int
    declare @CntPeme int
    declare @CntHede int
    /*End*/
    declare @OldFlag int
    declare @OldPrin int
    select @OldFlag = (select FLAG_CODE From VW_CREW_LAST_PRINFLAG where (Nunmber=@SeqNo))
    select @OldPrin = (select PRIN_CODE From VW_CREW_LAST_PRINFLAG where (Nunmber=@SeqNo))

    --- End Flag Med Dec
    select @VessCode =(Select Vess_Code from LU where Nunmber=@SeqNo)
    select @DepDate =(select departure_date from LU where (Nunmber=@SeqNo))
    select @ConLen =(select contract_length from LU where (Nunmber=@SeqNo))
    select @PosiCode =(select POSI_CODE from LU where (Nunmber=@SeqNo))
    select @ChecklistNo =(select prma_seqno from LU where (Nunmber=@SeqNo))
    select @ConUnit =(select LENGTH_UNIT from LU where (Nunmber=@SeqNo))
    select @PosRank =(SELECT dbo.POS.POSITION_RANKING FROM dbo.LU INNER JOIN dbo.POS ON dbo.LU.POSI_CODE = dbo.POS.POSI_CODE where (LU.Nunmber=@SeqNo))
    select @Destination=(select DESTINATION from LU where Nunmber=@SeqNo)

    if @PosRank='O'
    Begin
    set @intTrainCenter = 46
    End
    else
    Begin
    set @intTrainCenter = 145
    End
    select @ChecklistPerVes = (SELECT COUNT(*) AS Expr1 FROM dbo.ProMastVes WHERE (VESS_CODE = @VessCode))


    select @ChecklistNo=(SELECT top 1 PRMA_SEQNO FROM FN_PROMAST(@VessCode,@PosiCode,@ChecklistPerVes))


    --- Update Checklist No in LU
    update LU set prma_seqno = @ChecklistNo where (Nunmber=@SeqNo)

    set @DocVal=0
    select @VesCat =(select VECA_CODE from Vess where (vess_code=@VessCode))
    select @VesPool=(select PRPO_CODE from Vess where (vess_code =@VessCode))
    select @VesPrin=(select Prin_CODE from Vess where (vess_code=@VessCode))
    select @VesFlag=(select flag_code from Vess where (vess_code=@VessCode))

    select @DocValPerPool = (select count(*) as Count from AJ_DOCUMENTS_VALIDITY_POOL where (PRPO_CODE=@VesPool))

    select @DocValPerPrin =(select count(*) count from AJ_DOCUMENTS_VALIDITY_PRINCIPAL where (PRIN_CODE=@VesPrin))

    declare @DocValPPool int
    declare @DocValPPrin int
    select @DocValPPool = (select months from aj_documents_validity_pool where (prpo_code=@VesPool))
    select @DocValPPrin =(select months from AJ_DOCUMENTS_VALIDITY_PRINCIPAL where (prin_code=@VesPrin))

    if @DocValPerPool =1 and @DocValPerPrin = 1
    Begin
    if @DocValPPrin <> @DocValPPool
    Begin
    print 'error'
    Raiserror ('Doc Validity in Pool and Prin is not equal',16,1)
    RETURN 1
    End
    End
    Else
    if @DocValPerPool = 1
    Begin
    select @DocVal = isnull((select months from aj_documents_validity_pool where (prpo_code=@VesPool)),0)

    End
    Else
    if @DocValPerPrin = 1
    Begin
    select @DocVal = isnull((select months from AJ_DOCUMENTS_VALIDITY_PRINCIPAL where (prin_code=@VesPrin)),0)

    End

    if @DocVal <> 0
    Begin
    select @ExpectedSignOff = dbo.fn_GetExpectedSignoff (@Depdate, @ConUnit , @DocVal )

    End
    else
    Begin
    select @ExpectedSignOff =dbo.fn_GetExpectedSignoff (@Depdate,@ConUnit , @ConLen )
    set @ExpectedSignOff=convert(datetime,@ExpectedSignOff,103)

    End
    delete from temp_CannotPrint_Contract where (Nunmber=@SeqNo) and (Created_By =@ApplUserName)
    delete from temp_deficient_records where (Nunmber=@SeqNo)and (created_by=@ApplUserName)

    /*Declaration for detail vw_processing_req_details*/
    declare @PrreCode int
    declare @PrreDesc varchar(100)
    declare @YesNo varchar(1)
    declare @BookType int
    declare @BookDesc varchar(50)
    declare @TrceCode int
    declare @TrceAbbr varchar(30)
    declare @HedeCode int
    declare @HedeDesc varchar(50)
    declare @HereCode int
    declare @HereDesc varchar(100)
    declare @DocCode int
    declare @DocDesc varchar(100)
    declare @Noofdays int
    declare @TareCode int
    declare @TareDesc varchar(100)
    declare @MeexCode int
    declare @MeexDesc varchar(100)
    declare @TankerYesNo varchar(1)
    declare @BookWValidYesNo varchar(1)
    declare @CheckInNo varchar(1)
    declare @CheckInIssued varchar(1)
    declare @PireCode int
    declare @PireDesc varchar(100)
    declare @GocVal datetime
    declare @PemeVal datetime
    declare @HereVal datetime
    declare @CertVal datetime
    declare @strRevalidateMTC varchar(1)
    declare @gNewExpectedSignOff datetime
    declare @OtMedDesc varchar(50)
    declare @TankVal datetime
    declare @cntVesLimit int
    declare @CertIssued datetime
    /*End*/

    declare @BookVal datetime
    declare @SSSNo varchar(12)
    declare @TaxCode varchar(20)
    declare @TinNo varchar(20)
    declare @LicenseVal datetime
    set @bookval= ' '

    select @BookVal =(select validity_date from Books where (boty_code=2) and (Nunmber=@SeqNo))
    if @BookVal is null
    Begin
    INSERT INTO Temp_CannotPrint_Contract
    (Nunmber, DESCRIPTION, CREATED_BY)
    VALUES (@SeqNo,'not Encoded',@ApplUserName)
    End
    else
    Begin
    if @ExpectedSignOff < @BookVal and @BookVal is not null
    Begin

    update Books WITH (ROWLOCK) set expired_tag ='N' where (Nunmber=@SeqNo) and (boty_code=2)
    End
    Else
    Begin
    update Books WITH (ROWLOCK) set expired_tag ='Y' where (Nunmber=@SeqNo) and (boty_code=2)
    INSERT INTO Temp_CannotPrint_Contract
    (Nunmber, DESCRIPTION, CREATED_BY)
    VALUES (@SeqNo,'not Expired',@ApplUserName)
    End
    End

    if @VesCat <> 'II'
    Begin
    declare @intSRCNo varchar(35)
    declare @intSRCPos int
    select @intSRCNO = isnull((SELECT SRC FROM cw WITH (NOLOCK) WHERE Nunmber=@SeqNo),'0')
    if @intSRCNo = '0'
    Begin
    INSERT INTO Temp_CannotPrint_Contract
    (Nunmber,DESCRIPTION,CREATED_BY)
    VALUES(@SeqNo,' Not Encoded',@ApplUserName)
    End
    select @intSRCPos = isnull((select SRC_POSI_CODE from cw where (Nunmber=@SeqNo)),0)
    if @intSRCPos = 0
    Begin
    insert into temp_cannotprint_Contract
    (Nunmber,description,created_by)
    values(@SeqNo,'not Encoded',@ApplUserName)
    End
    End

    select @SSSNo=isnull((select SNo from cw where (Nunmber=@seqno)),'0')
    if @sssno = '0' or len(@sssno) <> 12
    Begin
    insert into temp_cannotprint_Contract
    (Nunmber,description,created_by)
    values(@SeqNo,'not encoded.',@ApplUserName)
    End
    if @VesCat = 'II'
    Begin

    select @TaxCode = isnull((select taxs_code from cw WITH (NOLOCK) where (Nunmber=@Seqno)),'0')
    select @TinNo=ISNULL((select tin from cw WITH(NOLOCK) where (Nunmber=@SeqNo)),'0')
    if @TaxCode ='0'
    Begin
    insert into temp_cannotprint_Contract
    (Nunmber,description,created_by)
    values(@SeqNo,'not Encoded.',@ApplUserName)
    End
    If @TinNo ='0'
    Begin
    insert into temp_cannotprint_Contract
    (Nunmber,description,created_by)
    values(@SeqNo,'Not Encoded.',@ApplUserName)
    End
    End


    declare cur_master_doc cursor
    for
    SELECT * From VW_PROCESSING_REQ_DETAILS WHERE
    (PRRE_CODE IN (SELECT PRRE_CODE From AJ_PROCESSING_MASTER WITH (NOLOCK)
    WHERE (PRMA_SEQNO = @ChecklistNo))) AND (NOT (NO_OF_DAYS IS NULL))
    OR
    (PRRE_CODE IN
    (SELECT PRRE_CODE
    FROM AJ_PROCESSING_MASTER
    WHERE (PRMA_SEQNO = @ChecklistNo))) AND (NOT (MEEX_CODE IS NULL))
    OR
    (PRRE_CODE IN
    (SELECT PRRE_CODE
    FROM AJ_PROCESSING_MASTER
    WHERE (PRMA_SEQNO = @ChecklistNo))) AND (NOT (DOCU_CODE IS NULL))
    OR
    (PRRE_CODE IN
    (SELECT PRRE_CODE
    FROM AJ_PROCESSING_MASTER
    WHERE (PRMA_SEQNO = @ChecklistNo))) AND (NOT (BOTY_CODE IS NULL))
    OR
    (PRRE_CODE IN
    (SELECT PRRE_CODE
    FROM AJ_PROCESSING_MASTER
    WHERE (PRMA_SEQNO = @ChecklistNo))) AND (NOT (HERE_CODE IS NULL))
    OR
    (PRRE_CODE IN
    (SELECT PRRE_CODE
    FROM AJ_PROCESSING_MASTER
    WHERE (PRMA_SEQNO = @ChecklistNo))) AND (NOT (HEDE_CODE IS NULL))
    ORDER BY PRRE_CODE
    open cur_master_doc
    fetch next from cur_master_doc into @PrreCode, @PrreDesc, @YesNo ,@BookType,@BookDesc,@TrceCode,@TrceAbbr,@HedeCode ,@HedeDesc,
    @HereCode ,@HereDesc, @DocCode , @DocDesc, @Noofdays, @TareCode, @TareDesc, @MeexCode, @MeexDesc ,
    @TankerYesNo, @BookWValidYesNo, @CheckInNo , @CheckInIssued , @PireCode , @PireDesc
    while @@fetch_status =0
    Begin
    select @No =null
    select @TranNo =null
    select @expired =null
    select @tranDateIssued = NULL
    select @tranDateValidity = NULL
    declare @certyear datetime
    declare @certid int
    declare @certtrcecode int
    declare @prredocvaldest varchar(100)
    declare @intCheckDest int
    declare @LicCap varchar(300)

    print ' *** Start ' +@Prredesc + ' ***'

    set @gDocMoVal=''
    set @gDocExSo =''
    set @gDocVal=0

    if @DocValPerPool =1 and @DocValPerPrin = 1
    Begin
    if @DocValPPrin <> @DocValPPool
    Begin
    Raiserror ( 'Document Validity Pool and Document Validity per Prin not equal',16,1)
    RETURN 1
    End
    End
    Else
    if @DocValPerPool = 1
    Begin
    select @DocVal = isnull((select months from aj_documents_validity_pool where (prpo_code=@VesPool)),0)
    End
    Else
    if @DocValPerPrin = 1
    Begin
    select @DocVal = isnull((select months from AJ_DOCUMENTS_VALIDITY_PRINCIPAL where (prin_code=@VesPrin)),0)
    End

    if @DocVal <> 0
    Begin
    select @ExpectedSignOff = dbo.fn_GetExpectedSignoff (@Depdate, @ConUnit , @DocVal )
    End
    else
    Begin
    select @ExpectedSignOff =dbo.fn_GetExpectedSignoff (@Depdate,@ConUnit , @ConLen )
    set @ExpectedSignOff=convert(datetime,@ExpectedSignOff,103)
    End
    --- End of Expected Signoff

    /*Check is there is a specific document validity per pool, principal and processing requirements*/
    select @PrreDocValPool = isnull((select Doc_validity from AJ_processing_Req_DocVal_Pool where (PRRE_CODE=@PrreCode) and (PRPO_CODE=@VesPool)),0)
    select @PrreDocValPrin =isnull((select Doc_validity from AJ_PROCESSING_REQ_DOCVAL_PRIN where (PRRE_CODE=@PrreCode) and (PRIN_CODE=@VesPrin)),0)
    select @PrreDocVal=isnull( (select Doc_validity from AJ_PROCESSING_REQ_DOCVAL where (PRRE_CODE=@PrreCode)),0)
    select @PrreDocVess=isnull((select doc_validity from aj_processing_req_docVal_vess where (prre_code=@prrecode)and (vess_code=@vesscode)),0)

    if @PrreDocValPool <> 0 and @PrreDocValPrin <> 0
    Begin
    if @PrreDocValPool <> @PrreDocValPrin
    Begin
    Raiserror ('Doc Validity in Processing Requirement per Pool and Processing Requirement per Prin is not equal',16,1)
    RETURN 1
    End
    End
    if @PrreDocValPool <> 0
    Begin
    set @gDocVal=@PrreDocValPool
    set @gDocMoVal=(select days_months from aj_processing_Req_DocVal_Pool where (PRRE_CODE=@PrreCode) and (PRPO_Code=@VesPool))
    set @gDocExSo=(select Val_ValDate_ExSo from aj_processing_req_docval_pool where (prre_code=@PrreCode) and (PRPO_Code = @VesPool))
    set @gDocAddDepDocVal = (select ADD_DOCVALDEPDATEVal from aj_processing_req_docval_pool where (prre_code=@prrecode) and (prpo_code=@vespool))
    End
    if @PrreDocValPrin <> 0
    Begin
    set @gDocVal=@PrreDocValPrin
    set @gDocMoVal=(select days_months from AJ_PROCESSING_REQ_DOCVAL_PRIN where (PRRE_CODE=@PrreCode) and (PRIN_CODE=@VesPrin))
    set @gDocExSo=(select Val_ValDate_ExSo from AJ_PROCESSING_REQ_DOCVAL_PRIN where (prre_code=@PrreCode) and (PRIN_CODE=@VesPrin))
    set @gDocAddDepDocVal =(select ADD_DOCVALDEPDATEVal from aj_processing_req_docval_prin where (prre_code=@prrecode)and (prin_code=@vesprin))
    End
    if @PrreDocVess <> 0
    Begin
    set @gDocVal=@PrreDocVess
    set @gDocMoVal=(select days_months from aj_processing_req_docval_vess where (prre_code=@prrecode)and (vess_code=@vesscode))
    set @gDocExSo=(select val_valdate_exso from aj_processing_req_docval_vess where (prre_code=@prrecode)and (vess_code=@vesscode))
    set @prredocvaldest=(select destination from aj_processing_req_docval_vess where vess_code=@vesscode)
    set @intCheckDest=(select patindex('%[@prredocvaldest]%',@destination)as test from LU where Nunmber=@seqno)
    set @gDocaddDepDocVal=(select ADD_DOCVALDEPDATEVal from aj_processing_req_docval_vess where (prre_code=@prrecode) and (vess_code=@vesscode))
    End
    if @PrreDocVal <> 0
    Begin
    set @gDocVal=@PrreDocVal
    set @gDocMoVal=(select days_months from AJ_PROCESSING_REQ_DOCVAL where (PRRE_CODE=@PrreCode))
    set @gDocExSo=(select Val_ValDate_ExSo from AJ_PROCESSING_REQ_DOCVAL where (prre_code=@PrreCode))
    set @gDocAddDepDocVal=(select ADD_DOCVALDEPDATEVal from aj_processing_req_docval where (prre_code=@prrecode))
    End
    set @BookVal = ' '
    if isnull(@BookType,0) <> 0
    Begin
    select @BotyPOCE=isnull((SELECT Books.POCE_CODE FROM Books WHERE (Books.BOTY_CODE = @booktype) AND (Books.Nunmber = @seqno)),0)
    select @BOTYDocu=isnull((select docu_code from Books where (Nunmber=@seqno) and (boty_code=@booktype)),0)
    select @BotytrceCode=isnull((select trce_code from Books where (boty_code=@booktype) and (Nunmber=@seqno)),0)
    select @BookVal=(select validity_date from Books where (Nunmber=@Seqno) AND (boty_code=@BookType))
    select @BookValidPoce=(SELECT COUNT(*) AS Expr1 FROM dbo.VW_BOOKS_W_POCE
    WHERE (Nunmber = @seqno) AND (POSI_CODE = @PosiCode)
    AND (POCE_CenDocPerPos = @botyPOCE) AND (TRCE_CODE = @BOtyTrceCode))
    select @CntBook = (select count(*) as [Count] from Books where Nunmber=@SeqNo and (BOTY_CODE=@BookType))
    if @gDocVal <> 0
    Begin

    if @BookWValidYesNo <>'Y'
    Begin
    if @botypoce <> 0
    begin
    if @BookValidPoce =1
    Begin
    if @gDocExSo ='Y'
    begin
    if @gDocAddDepDocVal ='Y'
    Begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    End
    if @gDocAddDepDocVal='N'
    Begin
    select @gNewDocVal = dbo.fn_GetExpectedSignOff(@BookVal,@gDocMoVal,@gDocVal)

    End

    if @ExpectedSignOff > @gNewDocVal
    Begin

    update Books WITH (ROWLOCK) set expired_tag='Y' where
    (Nunmber=@Seqno) and (boty_code=@booktype)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE ,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @ExpectedSignOff < @gNewDocVal
    Begin
    update Books WITH (ROWLOCK) set expired_tag='N' where
    (Nunmber=@Seqno) and (boty_code=@booktype)
    End

    end
    if @gDocExSo ='N'
    begin
    if @gDocAddDepDocVal ='Y'
    Begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    End
    if @gDocAddDepDocVal='N'
    Begin
    select @gNewDocVal = dbo.fn_GetExpectedSignOff(@BookVal,@gDocMoVal,@gDocVal)

    End

    if @gNewDocVal > @BookVal and @BookVal is not null
    Begin

    update Books WITH (ROWLOCK) set expired_tag='Y' where
    (Nunmber=@Seqno) and (boty_code=@booktype)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @gNewDocVal < @BookVal and @BookVal is not null
    Begin

    update Books WITH (ROWLOCK) set expired_tag='N' where
    (Nunmber=@Seqno) and (boty_code=@booktype)
    End
    if @BookVal =getdate()
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    end
    End
    if @BookValidPoce =0
    Begin

    update Books WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@SeqNo)
    and (boty_code=@BookType)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@expectedsignoff,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    end
    if @botypoce=0
    begin

    if @gDocExSo='Y'
    Begin
    if @gDocAddDepDocVal ='Y'
    Begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    End
    if @gDocAddDepDocVal='N'
    Begin
    select @gNewDocVal = dbo.fn_GetExpectedSignOff(@BookVal,@gDocMoVal,@gDocVal)

    End


    if @bookval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    if @gNewDocVal > @ExpectedSignOff and @BookVal is not null
    begin

    update Books set expired_tag='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@seqno) and (boty_code=@booktype)
    end
    else --- expired
    begin

    update Books WITH (ROWLOCK) set expired_tag='Y',updated_by=@applusername,update_date=getdate()
    where (Nunmber=-@seqno) and (boty_code=@booktype)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    End
    if @gDocExSo='N'
    begin

    if @gDocAddDepDocVal ='Y'
    Begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    End
    if @gDocAddDepDocVal='N'
    Begin
    select @gNewDocVal = dbo.fn_GetExpectedSignOff(@BookVal,@gDocMoVal,@gDocVal)

    End

    if @bookval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    if @bookval > @gNewDocVal and @bookval is not null
    begin

    update Books WITH (ROWLOCK) set expired_tag='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@seqno) and (boty_code=@booktype)
    end
    if @bookval < @gNewDocVal and @BookVal is not null
    begin

    update Books WITH (ROWLOCK) set expired_tag='Y',updated_by=@applusername,update_date=getdate()
    where (Nunmber=-@seqno) and (boty_code=@booktype)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end

    end
    end
    End
    if @BookWValidYesNo ='N'
    Begin

    if @CntBook > 0
    Begin

    update Books WITH (ROWLOCK) set expired_Tag='N' where (Nunmber=@Seqno) and (BOTY_CODE=@BookType)

    End
    if @CntBook =0
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    End
    End
    if @gDocVal = 0
    Begin

    if @BookWValidYesNo <>'Y' or len(@BookWValidYesNo)=0
    begin

    if @botypoce <>0
    Begin

    if @BookValidPoce =1
    begin

    if @ExpectedSignOff < @BookVal and @BookVal is not null
    Begin

    update Books WITH (ROWLOCK) set Expired_Tag ='N' where (Nunmber=@SeqNo) and (boty_code=@booktype)
    End
    if @bookval is null
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @ExpectedSignOff > @BookVal and @BookVal is not null
    Begin

    update Books WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@seqno) and (boty_code=@booktype)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    ENd
    end
    if @BookValidPoce =0
    begin

    update Books WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@seqno) and (boty_code=@booktype)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@expectedSignOff,isnull(@BookVal,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    End
    if @botypoce=0
    begin

    if @bookval is null
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@expectedsignoff,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @BookVal is not null and(@BookVal > @ExpectedSignOff)
    Begin

    update Books WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@seqno) and (boty_code=@booktype)
    End
    if @expectedsignoff > @bookval and @BookVal is not null
    Begin

    update Books WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@seqno) and (boty_code=@booktype)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@expectedsignoff,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    end
    end
    if @BookWValidYesNo ='N'
    begin

    if @CntBook > 0
    Begin

    update Books WITH (ROWLOCK) set expired_Tag='N' where (Nunmber=@Seqno) and (BOTY_CODE=@BookType)

    End
    if @CntBook =0
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@BookVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    end
    End
    select @No=(SELECT BOOK_NO FROM dbo.Books WHERE (Nunmber = @SeqNo) AND (BOTY_CODE = @BookType))
    select @tranNo = (SELECT TOP 1 LICENSE_NO FROM dbo.cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (BOTY_CODE = @BookType) AND (DATE_RECEIVED IS NULL))
    select @Expired = (SELECT EXPIRED_TAG FROM dbo.Books WHERE (Nunmber = @SeqNo) AND (BOTY_CODE = @BookType))
    select @tranDateIssued = (SELECT TOP 1 ISSUED_DATE FROM dbo.cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (BOTY_CODE = @BookType) AND (DATE_RECEIVED IS NULL))
    select @tranDateValidity = (SELECT TOP 1 VALIDITY_DATE FROM dbo.cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (BOTY_CODE = @BookType) AND (DATE_RECEIVED IS NULL))
    if (@No is null and @tranNo is not null) or (@expired = 'Y' and @tranno is not null)
    begin

    if @tranDateValidity < getdate()
    Begin
    insert into temp_deficient_records
    (Nunmber,deficient_records,exsignoff,docvalidity,departure_date,contract_duration,contract_unit,prre_code,create_date,created_by)
    values(@seqno,@prredesc ,@expectedsignoff,@tranDateValidity,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    update cw_transitional_certificates WITH (ROWLOCK) set EXPIRED='Y' where (Nunmber=@SeqNo)
    End

    if @tranDateValidity > getdate()
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set EXPIRED='N' where (Nunmber=@SeqNo)
    End
    if @tranNo is null
    Begin
    insert into temp_deficient_records
    (Nunmber,deficient_records,exsignoff,docvalidity,departure_date,contract_duration,contract_unit,PRRE_CODE,create_date,created_by)
    values(@seqno,@prredesc,@ExpectedSignOff,isnull(@trandatevalidity,getdate()),@depdate,@conlen,@ConUnit,@prrecode,getdate(),@applusername)

    End
    end
    End
    if isnull(@HedeCode,0) <> 0
    Begin

    if @VesCat <> 'II'
    Begin
    select @intCountHDec=(select count(*) as [count] from cw_healths where (Nunmber=@SeqNo) and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL))))
    select @intCountPEME=(select count(*) as [count]from cw_med_exams where (Nunmber=@SeqNo)and (meex_code=5))
    select @HDecVal = (select validity_date from cw_healths where (Nunmber=@SeqNo) and (hede_code=@hedecode))
    if @gDocVal <> 0
    Begin


    if @VesPrin = @OldPrin and @VesFlag = @OldFlag
    Begin
    if @gDocExSo ='Y'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@HDecVal,@gDocMoVal , @gDocVal )

    end

    if @gNewDocVal <= @ExpectedSignOff
    begin
    update cw_healths WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo) and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=5)
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=11)
    End
    select @cntpeme=(SELECT COUNT(*) AS Expr1 FROM dbo.TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND
    (DEFICIENT_RECORDS = 'note') AND (CREATED_BY =@ApplUserName))
    if @cntpeme = 1
    begin
    DELETE FROM TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND (DEFICIENT_RECORDS = 'note') AND (CREATED_BY = @ApplUserName)
    end
    end
    else
    Begin
    update cw_healths WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@SeqNo) and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=5)
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=11)
    select @OtMedDesc = (select meex_description from aj_med_exam_register where meex_code=11)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@OtMedDesc ,@gNewDocVal,@HdecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@HDecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @intcounthdec =0 and @intcountpeme = 0
    Begin


    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@HdecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    End

    if @gDocExSo='N'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal,@gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@HDecVal,@gDocMoVal,@gDocVal )

    end
    if @HDecVal is null
    begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@HdecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    if @HDecVal is not null
    if @gNewDocVal <= @HDecVal
    begin
    update cw_healths WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo) and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    update cw_med_exams set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=5)
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=11)
    End
    select @cntpeme=(SELECT COUNT(*) AS Expr1 FROM dbo.TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND
    (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY =@ApplUserName))
    if @cntpeme = 1
    begin
    DELETE FROM TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY = @ApplUserName)
    end


    end
    else
    Begin
    update cw_healths WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@SeqNo) and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=5)
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=11)
    End

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@HdecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)


    End

    if @intcounthdec =0 and @intcountpeme = 0
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@HDecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    end
    End
    if @VesPrin <> @OldPrin or @VesFlag <> @OldFlag
    Begin
    select @PemeVal = (select medval from cw_med_exams where (Nunmber=@seqno)and (meex_code=5))
    if @gDocExSo ='Y'
    Begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@PemeVal,@gDocMoVal , @gDocVal )

    end
    if @gNewDocVal < @ExpectedSignOff
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo)
    and (meex_code=5)
    update cw_healths WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo)
    and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=11)
    End
    select @cntpeme=(SELECT COUNT(*) AS Expr1 FROM dbo.TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND
    (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY =@ApplUserName))
    if @cntpeme = 1
    begin
    DELETE FROM TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY = @ApplUserName)
    end
    End
    else
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@seqno) and
    (meex_code=5)
    update cw_healths WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@SeqNo)
    and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=11)
    select @OtMedDesc = (select meex_description from aj_med_exam_register where (meex_code=11))
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@OtMedDesc ,@gNewDocVal,@PEMEVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End



    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@PEMEVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End

    End
    if @gDocExSo ='N'
    Begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@PemeVal,@gDocMoVal , @gDocVal )

    end
    if @PemeVal is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@pemeval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    if @gNewDocVal < @PemeVal and @PemeVal is not null
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo)
    and (meex_code=5)
    update cw_healths WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo)
    and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=11)
    End
    select @cntpeme=(SELECT COUNT(*) AS Expr1 FROM dbo.TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND
    (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY =@ApplUserName))
    if @cntpeme = 1
    begin
    DELETE FROM TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY = @ApplUserName)
    end


    End
    else
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@seqno) and
    (meex_code=5)
    update cw_healths WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@SeqNo)
    and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=11)
    select @OtMedDesc= (select meex_description from aj_med_exam_register where (meex_code=11))
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@OtMedDesc ,@gNewDocVal,@pemeval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End



    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@pemeval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    End
    End
    End
    if @gDocVal =0
    Begin
    if(@VesPrin = @OldPrin) and (@VesFlag = @OldFlag)
    begin

    if @HDecVal is null
    begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@expectedsignoff,@hDecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    if @ExpectedSignOff <= @HDecVal and @HDecVal is not null
    begin
    update cw_healths WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo) and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    update cw_med_exams set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=5)
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=11)
    End
    select @cntpeme=(SELECT COUNT(*) AS Expr1 FROM dbo.TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND
    (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY =@ApplUserName))
    if @cntpeme = 1
    begin
    DELETE FROM TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY = @ApplUserName)
    end

    end
    else
    Begin
    update cw_healths WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@SeqNo) and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    update cw_med_exams set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=5)
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=11)
    select @OtMedDesc = (select meex_description from aj_med_exam_register where (meex_code=11))
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@OtMedDesc ,@ExpectedSignOff,@HdecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    */
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@HdecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @intcounthdec =0 and @intcountpeme = 0
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@HdecVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    end
    if (@VesFlag<> @OldFlag) or (@VesPrin <> @OldPrin)
    Begin

    select @PemeVal =(select medval from cw_med_exams where (Nunmber=@seqno)and (meex_code=5))
    if @expectedsignoff < @pemeval and @PemeVal is not null
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo)
    and (meex_code=5)
    update cw_healths WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo)
    and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno)and (meex_code=11)
    End
    select @cntHede=(select count(*) as cnt from cw_healths where (Nunmber=@seqno)and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL))))


    if @cntHede =0
    begin

    select @hededesc=(SELECT AJ_PROCESSING_REQUIREMENTS.PRRE_DESC FROM AJ_PROCESSING_MASTER INNER JOIN AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE
    WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL))
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@hededesc,@ExpectedSignOff,@PEMEVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    select @cntpeme=(SELECT COUNT(*) AS Expr1 FROM dbo.TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND
    (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY =@ApplUserName))
    if @cntpeme = 1
    begin
    DELETE FROM TEMP_DEFICIENT_RECORDS
    WHERE (Nunmber = @Seqno) AND (DEFICIENT_RECORDS = 'Note') AND (CREATED_BY = @ApplUserName)
    end

    End
    else
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@seqno) and
    (meex_code=5)
    update cw_healths WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@SeqNo)
    and (hede_code=(SELECT AJ_PROCESSING_REQUIREMENTS.HEDE_CODE FROM AJ_PROCESSING_MASTER INNER JOIN
    AJ_PROCESSING_REQUIREMENTS ON AJ_PROCESSING_MASTER.PRRE_CODE = AJ_PROCESSING_REQUIREMENTS.PRRE_CODE WHERE (AJ_PROCESSING_MASTER.PRMA_SEQNO = @ChecklistNo) AND (AJ_PROCESSING_REQUIREMENTS.HEDE_CODE IS NOT NULL)))
    if @Vesprin =55
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno)and (meex_code=11)
    select @otmeddesc = (select meex_code from aj_med_exam_register where (meex_code=11))
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@PEMEVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@PEMEVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End

    End
    End
    End
    End
    if isnull(@HereCode,0) <> 0
    Begin
    select @hereval = (select validity_date from cw_vaccinations where (Nunmber=@seqno) and (here_code=@herecode))
    if @gDocVal <> 0
    Begin
    if @gDocExSo ='Y'
    begin
    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@hereval,@gDocMoVal , @gDocVal )
    end
    if @herecode=1 or @herecode=4 or @herecode= 5 --- Hepa A and B and AntiTetanus
    Begin
    select @hereval = (select date_taken from cw_vaccinations where (Nunmber=@seqno)and (here_code=@Herecode))
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo)and (here_code=@herecode)
    if @hereval is null
    begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@hereval,isnull(@hereval,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    End
    else
    if @herecode <> 1 or @herecode <> 4 or @herecode <> 5
    begin
    if @hereval is null
    Begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='Y'
    where (Nunmber=@SeqNo) and (here_code=@herecode)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,isnull(@hereval,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @gNewDocVal < @expectedsignoff and @HereVal is not null
    begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='N'
    where (Nunmber=@SeqNo) and (here_code=@herecode)

    end
    if @gNewDocVal > @expectedsignoff and @HereVal is not null
    begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='Y'
    where (Nunmber=@SeqNo) and (here_code=@herecode)


    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,isnull(@hereval,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    end
    end
    if @gDocExSo ='N'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@hereval,@gDocMoVal , @gDocVal )

    end
    if @herecode=1 or @herecode=4 or @herecode= 5
    Begin
    select @hereval = (select date_taken from cw_vaccinations where (Nunmber=@seqno)and (here_code=@Herecode))

    update cw_vaccinations WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@SeqNo)and (here_code=@herecode)
    if @hereval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,isnull(@hereval,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    End
    else
    if @herecode <> 1 or @herecode <> 4 or @herecode <> 5
    Begin
    if @hereval is null
    Begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='Y'
    where (Nunmber=@SeqNo) and (here_code=@herecode)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,isnull(@hereVal,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @hereval > @gNewDocVal and @hereval is not null
    begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='N'
    where (Nunmber=@SeqNo) and (here_code=@herecode)

    end
    if @hereval < @gNewDocVal and @hereval is not null
    begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='Y'
    where (Nunmber=@SeqNo) and (here_code=@herecode)


    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,isnull(@hereVal,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    end
    end
    End
    if @gDocVal =0
    Begin
    if @herecode=1 or @herecode=4 or @herecode= 5
    Begin
    select @hereval = (select date_taken from cw_vaccinations where (Nunmber=@seqno)and (here_code=@Herecode))

    update cw_vaccinations set expired_tag='N' where (Nunmber=@SeqNo)and (here_code=@herecode)
    if @hereval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,isnull(@hereval,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    End
    else
    if @herecode <> 1 or @herecode <> 4 or @herecode <> 5
    Begin
    if @hereval is null
    Begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='Y'
    where (Nunmber=@SeqNo) and (here_code=@herecode)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@expectedsignoff,isnull(@hereval,getdate()),@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End

    if @expectedsignoff < @hereval and @hereval is not null
    begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='N'
    where (Nunmber=@SeqNo) and (here_code=@herecode)

    end
    if @expectedsignoff > @hereval and @hereval is not null
    begin
    update cw_vaccinations WITH (ROWLOCK) set expired_tag='Y'
    where (Nunmber=@SeqNo) and (here_code=@herecode)


    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@expectedsignoff,@hereVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    End
    End
    End
    if isnull(@MeexCode,0) <> 0
    Begin

    select @pemeval = (select medval from cw_med_exams where (Nunmber=@SeqNo) and (meex_code=@meexcode))

    if @gDocVal <> 0
    Begin

    if @gDocExSo='Y'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )
    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@pemeval,@gDocMoVal , @gDocVal )
    end
    if @pemeval is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@pemeVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @gNewDocVal < @ExpectedSignOff and @pemeval is not null
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno) and (meex_code=@meexcode)
    End
    else
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@pemeval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno) and (meex_code=@meexcode)

    End
    end
    if @gDocExSo='N'
    begin
    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@PemeVal,@gDocMoVal , @gDocVal )

    end
    if @pemeval is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@pemeVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @gNewDocVal < @pemeval and @pemeval is not null
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno) and (meex_code=@meexcode)
    End
    if @gNewDocVal > @pemeval and @pemeval is not null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@PEMEVAL,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno) and (meex_code=@meexcode)

    End
    end
    End
    if @gDocval = 0
    Begin

    if @pemeval is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@expectedsignoff,@pemeVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @expectedsignoff < @pemeval and @pemeval is not null
    Begin
    update cw_med_exams WITH (ROWLOCK) set expired_tag='N' where (Nunmber=@Seqno) and (meex_code=@meexcode)
    End
    if @expectedsignoff > @pemeval and @pemeval is not null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@expectedsignoff,@pemeVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    update cw_med_exams WITH (ROWLOCK) set expired_tag='Y' where (Nunmber=@Seqno) and (meex_code=@meexcode)
    End
    End
    End
    if isnull(@TrceCode,0) <> 0 and isnull(@DocCode,0) <> 0
    Begin
    if @PosiCode =1400 or @posicode = 1460 or @posicode = 9610 or @posicode = 1390
    Begin
    if @doccode =3
    begin
    set @TrceCode=145
    end
    End
    if @PosRank ='R' or @PosRank ='r'
    Begin
    set @trcecode = 145
    End
    set @intPOCE=''
    select @cntDoc = (SELECT COUNT(AJ_CENTER_DOCUMENT_PER_POSITION.POCE_CODE) AS Expr1
    FROM AJ_CENTER_DOCUMENT_PER_POSITION INNER JOIN
    AJ_CENTER_DOCUMENT ON AJ_CENTER_DOCUMENT_PER_POSITION.CEDO_SEQ_NO = AJ_CENTER_DOCUMENT.CEDO_SEQ_NO
    WHERE (AJ_CENTER_DOCUMENT.DOCU_CODE = @doccode) AND (AJ_CENTER_DOCUMENT_PER_POSITION.POSI_CODE = @posicode))
    --(SELECT COUNT(*) AS [COUNT] FROM dbo.cw_QUALIFICATION_DOC WHERE (Nunmber = @Seqno) AND (DOCU_CODE = @DocCode) AND (TRCE_CODE = @TrceCode))
    select @QualDocVal =(select validity_date from cw_qualification_doc where (Nunmber=@SeqNo) and (docu_code=@DocCode) and (trce_code=@TrceCode))
    select @preDoc = (select pre_docu_code from aj_center_document where (trce_code=@trcecode) and (docu_code=@doccode) and (PRE_DOCU_CODE IS NOT NULL))
    declare @CenDocPerPos int
    set @CenDocPerPos=0
    declare cur_poce cursor
    for
    SELECT AJ_CENTER_DOCUMENT_PER_POSITION.POCE_CODE
    FROM AJ_CENTER_DOCUMENT_PER_POSITION INNER JOIN
    AJ_CENTER_DOCUMENT ON AJ_CENTER_DOCUMENT_PER_POSITION.CEDO_SEQ_NO = AJ_CENTER_DOCUMENT.CEDO_SEQ_NO
    WHERE(AJ_CENTER_DOCUMENT.DOCU_CODE = @doccode) AND (AJ_CENTER_DOCUMENT_PER_POSITION.POSI_CODE = @posicode) AND
    (AJ_CENTER_DOCUMENT.TRCE_CODE = @trcecode)
    open cur_poce
    fetch next from cur_poce into @intPOCE
    while @@fetch_status = 0
    Begin
    if @CenDocPerPos = 0
    Begin
    set @CenDocPerPos=(select count(*) as [Count] from vw_center_doc_per_position where (Nunmber=@seqno) and (docu_code=@doccode)and (posi_code=@posicode)and (trce_code=@trcecode) and (poce_cendocperpos=@intpoce) )
    end
    fetch next from cur_poce into @intPOCE
    End
    close cur_poce
    deallocate cur_poce
    if @TankerYesNo = 'Y'
    Begin
    select @VesClass = (select vetc_code from Vess where (vess_code=@vesscode))
    if @prrecode = 204
    begin
    if @posrank ='R' or @posicode=1400 or @posicode = 1460 or @posicode = 9610 or @posicode = 1390
    begin
    set @TrceCode = 145
    end
    end
    select @Trcecode = (SELECT TRCE_CODE FROM AJ_PROCESSING_REQUIREMENTS WHERE PRRE_CODE=@PRRECODE)
    select @tarecode = (SELECT TARE_CODE FROM cw_TANKER_DOCUMENT WHERE (Nunmber = @SeqNo) AND (DOCU_CODE = @DocCode) AND (TRCE_CODE = @TrceCode))
    select @cntVesLimit=(SELECT COUNT (*) AS [COUNT] FROM AJ_VESSEL_TYPE_LIMIT
    WHERE FLAG_CODE=@VesFlag AND (VETC_CODE=@vesclass) AND (DOCU_CODE=@doccode) AND (TRCE_CODE=@trcecode)and (Tare_code=@tarecode))
    if @cntVesLimit > 0
    Begin
    if @prrecode = 204 and @posrank ='R'
    begin
    select @TankVal = (select validity_date from cw_tanker_document where (Nunmber=@seqno) and (docu_code=@doccode)and (tare_code=@tarecode)and ( trce_code in(46,145)))
    End
    if @prrecode <> 204 and @posrank <> 'R'
    begin
    select @TankVal=(select validity_date from cw_tanker_document where (Nunmber=@seqno) and (docu_code=@doccode) and (tare_code=@tarecode)and (trce_code=@trcecode))
    end
    declare @cnt int
    set @cnt = 0
    PRINT @gDocVal
    if @gDocVal <> 0
    Begin
    if @gDocExSo ='Y'
    begin
    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )
    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@tankval,@gDocMoVal , @gDocVal )
    end
    if @tankval is null
    begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@tankval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    if @expectedsignoff < @gNewDocVal and @tankval is not null
    begin
    update cw_tanker_document WITH (ROWLOCK) set expired='N' ,valid_yesno='Y' where (Nunmber=@seqNo)and
    (trce_code=@trcecode)and (tare_code=@tarecode)
    end

    if @expectedsignoff > @gNewDocVal and @tankval is not null
    begin

    update cw_tanker_document WITH (ROWLOCK) set expired='Y', valid_yesno='N' where (Nunmber=@seqno)and
    (trce_code=@trcecode)and (tare_code=@tarecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@tankval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    set @cnt = @cnt + 1
    end
    if @gDocExSo ='N'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )
    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Tankval,@gDocMoVal , @gDocVal )
    end
    if @tankval is null
    begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@tankval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    if @gNewDocVal < @tankval and @tankval is not null
    begin

    update cw_tanker_document WITH (ROWLOCK) set expired='N' ,valid_yesno='Y' where (Nunmber=@seqNo)and
    (trce_code=@trcecode)and (tare_code=@tarecode)
    end

    if @gNewDocVal > @tankval and @tankval is not null
    begin

    update cw_tanker_document WITH (ROWLOCK) set expired='Y', valid_yesno='N' where (Nunmber=@seqno)and
    (trce_code=@trcecode)and (tare_code=@tarecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@tankval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    set @cnt = @cnt + 1
    end
    End
    if @gDocVal = 0
    Begin
    if @tankval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@expectedsignoff,@tankval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    if @expectedsignoff < @tankval and @tankval is not null
    begin

    update cw_tanker_document WITH (ROWLOCK) set expired='N' ,valid_yesno='Y' where (Nunmber=@seqNo)and
    (trce_code=@trcecode)and (tare_code=@tarecode)
    end

    if @expectedsignoff > @tankval and @tankval is not null
    begin

    update cw_tanker_document WITH (ROWLOCK) set expired='Y', valid_yesno='N' where (Nunmber=@seqno)and
    (trce_code=@trcecode)and (tare_code=@tarecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@expectedsignoff,@tankval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    set @cnt = @cnt + 1
    End
    End
    if @cntVesLimit =0
    Begin
    if @tankval is null
    begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@expectedsignoff,@tankval,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end


    End
    End

    else
    Begin

    if @cntDoc = 0
    Begin

    select @qualdocval = (select validity_date from cw_qualification_doc where (Nunmber=@seqno) and (docu_code=@doccode)and (trce_code=@trcecode))
    if @gDocVal <> 0
    begin
    )
    if @gDocExSo ='Y'
    Begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@qualDocVal,@gDocMoVal , @gDocVal )

    end
    if @gNewDocVal < @ExpectedSignOff
    Begin

    update cw_qualification_doc WITH (ROWLOCK) set expired='Y' where(Nunmber=@seqno)
    and (docu_code=@doccode) and (trce_code=@trcecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    else
    Begin

    update cw_qualification_doc WITH (ROWLOCK) set expired='N' where (Nunmber=@Seqno)
    and (docu_code=@doccode)and (trce_code=@trcecode)
    End
    End
    if @gDocExSo='N'
    Begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@QualDocVal,@gDocMoVal , @gDocVal )

    end
    if @gNewDocVal > @QualDocVal and @QualDocVal is not null
    Begin


    update cw_qualification_doc WITH (ROWLOCK) set expired='Y' where(Nunmber=@seqno)
    and (docu_code=@doccode) and (trce_code=@trcecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    else
    Begin

    update cw_qualification_doc WITH (ROWLOCK) set expired='N' where (Nunmber=@Seqno)
    and (docu_code=@doccode)and (trce_code=@trcecode)
    End
    End
    if @qualdocval is null
    begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    select @TranCnt = (select count(*) as [Count] from cw_transitional_certificates where (Nunmber=@SeqNo) and (poce_code is null) and (tare_code is null) and (docu_code = @preDoc) and (trce_code=@trcecode) and (date_received is null))
    if @TranCnt=0 and @preDoc is not null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @TranCnt > 0 and @preDoc is not null
    Begin
    if @predoc is null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc))
    if @preval < getdate()
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode)
    End
    end
    if @predoc is not null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc))
    if @preval < getdate()
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode) AND (POCE_CODE = @predoc)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode) AND (POCE_CODE = @predoc)
    End
    end
    end
    end
    end
    if @gDocVal =0
    begin
    if @qualdocval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    select @TranCnt = (select count(*) as [Count] from cw_transitional_certificates where (Nunmber=@SeqNo)and (poce_code is null) and (tare_code is null) and (docu_code = @preDoc) and (trce_code=@trcecode) and (date_received is null))

    if @TranCnt=0 and @preDoc is not null
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @TranCnt > 0 and @preDoc is not null
    Begin
    if @predoc is null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc))
    if @preval < getdate()
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc)
    End
    end
    if @predoc is not null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc))
    if @preval < getdate()
    Begin


    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc)
    End
    end
    End
    end
    if @ExpectedSignOff > @QualDocVal and @QualDocVal is not null
    Begin

    update cw_qualification_doc WITH (ROWLOCK) set expired='Y' where(Nunmber=@seqno)
    and (docu_code=@doccode) and (trce_code=@trcecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @ExpectedSignOff < @QualDocVal and @QualDocVal is not null
    Begin

    update cw_qualification_doc WITH (ROWLOCK) set expired='N' where (Nunmber=@Seqno)
    and (docu_code=@doccode)and (trce_code=@trcecode)
    End

    end
    End
    if @cntDoc <>0
    Begin

    select @QualDocVal =(select validity_date from vw_center_doc_per_position where (Nunmber=@SeqNo) and (docu_code=@DocCode) and (trce_code=@TrceCode)and (posi_code=@posicode))

    if @CenDocPerPos > 0
    begin

    if @qualdocval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    select @TranCnt = (select count(*) as [Count] from cw_transitional_certificates where (Nunmber=@SeqNo) and (poce_code is null) and (tare_code is null) and (docu_code = @preDoc) and (trce_code=@trcecode) and (date_received is null))
    if @TranCnt=0 and @preDoc is not null
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @TranCnt > 0 and @preDoc is not null
    Begin
    if @predoc is null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc))
    if @preval < getdate()
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc)
    End
    end
    if @predoc is not null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc))
    if @preval < getdate()
    Begin


    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode) AND (POCE_CODE = @predoc)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode) AND (POCE_CODE = @predoc)
    End
    end

    End
    end
    if @ExpectedSignOff > @QualDocVal and @QualDocVal is not null
    Begin

    update cw_qualification_doc_poce WITH (ROWLOCK) set expired='Y' where(Nunmber=@seqno)
    and (docu_code=@doccode) and (trce_code=@trcecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @ExpectedSignOff < @QualDocVal and @QualDocVal is not null
    Begin

    update cw_qualification_doc_poce WITH (ROWLOCK) set expired='N' where (Nunmber=@Seqno)
    and (docu_code=@doccode)and (trce_code=@trcecode)
    End

    end
    if @CenDocPerPos=0
    begin
    -
    Begin

    select @qualdocval = (select validity_date from cw_qualification_doc where (Nunmber=@seqno) and (docu_code=@doccode)and (trce_code=@trcecode))
    if @gDocVal <> 0
    begin

    if @gDocExSo ='Y'
    Begin
    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )
    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@qualDocVal,@gDocMoVal , @gDocVal )
    end
    if @gNewDocVal < @ExpectedSignOff
    Begin
    print @prredesc + ' expired'
    update cw_qualification_doc WITH (ROWLOCK) set expired='Y' where(Nunmber=@seqno)
    and (docu_code=@doccode) and (trce_code=@trcecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    else
    Begin

    update cw_qualification_doc WITH (ROWLOCK) set expired='N' where (Nunmber=@Seqno)
    and (docu_code=@doccode)and (trce_code=@trcecode)
    End
    End
    if @gDocExSo='N'
    Begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )
    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@QualDocVal,@gDocMoVal , @gDocVal )
    end
    if @gNewDocVal > @QualDocVal and @QualDocVal is not null
    Begin
    update cw_qualification_doc WITH (ROWLOCK) set expired='Y' where(Nunmber=@seqno)
    and (docu_code=@doccode) and (trce_code=@trcecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    else
    Begin

    update cw_qualification_doc WITH (ROWLOCK) set expired='N' where (Nunmber=@Seqno)
    and (docu_code=@doccode)and (trce_code=@trcecode)
    End
    End
    if @qualdocval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    select @TranCnt = (select count(*) as [Count] from cw_transitional_certificates where (Nunmber=@SeqNo) and (poce_code is null) and (tare_code is null) and (docu_code = @preDoc) and (trce_code=@trcecode) and (date_received is null))
    if @TranCnt=0 and @preDoc is not null
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @TranCnt > 0 and @preDoc is not null
    Begin
    if @predoc is null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc))
    if @preval < getdate()
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode)
    End
    end
    if @predoc is not null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc))
    if @preval < getdate()
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode) AND (POCE_CODE = @predoc)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @doccode) AND (POCE_CODE = @predoc)
    End
    end
    end
    end
    end
    if @gDocVal =0
    begin

    if @qualdocval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    --- to do transitional certificate
    select @TranCnt = (select count(*) as [Count] from cw_transitional_certificates where (Nunmber=@SeqNo)and (poce_code is null) and (tare_code is null) and (docu_code = @preDoc) and (trce_code=@trcecode) and (date_received is null))

    if @TranCnt=0 and @preDoc is not null
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @TranCnt > 0 and @preDoc is not null
    Begin
    if @predoc is null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc))
    if @preval < getdate()
    Begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber=@seqno) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc)
    End
    end
    if @predoc is not null
    begin

    select @preVal =(SELECT VALIDITY_DATE FROM cw_TRANSITIONAL_CERTIFICATES WHERE (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc))
    if @preval < getdate()
    Begin


    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@qualdocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    update cw_transitional_certificates WITH (ROWLOCK) set expired='Y' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc)
    End
    else
    Begin

    update cw_transitional_certificates WITH (ROWLOCK) set expired='N' where (Nunmber = @SeqNo) AND (DATE_RECEIVED IS NULL) AND (TRCE_CODE = @TrceCode) AND (DOCU_CODE = @preDoc) AND (POCE_CODE = @predoc)
    End
    end
    End
    end
    if @ExpectedSignOff > @QualDocVal and @QualDocVal is not null
    Begin
    update cw_qualification_doc WITH (ROWLOCK) set expired='Y' where(Nunmber=@seqno)
    and (docu_code=@doccode) and (trce_code=@trcecode)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@QualDocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @ExpectedSignOff < @QualDocVal and @QualDocVal is not null
    Begin

    update cw_qualification_doc WITH (ROWLOCK) set expired='N' where (Nunmber=@Seqno)
    and (docu_code=@doccode)and (trce_code=@trcecode)
    End

    end
    End
    end
    End
    End
    End
    if isnull(@TrceCode,0) <> 0 and isnull(@DocCode,0) =0
    Begin

    select @LicNeeded = (select license_needed from POS where (posi_code=@PosiCode))
    select @intPoceCode = (select poce_code from aj_licenses_earned where (Nunmber=@SeqNo) and (trce_code = @trcecode))
    select @LicNo = isnull((select license_no from aj_licenses_earned where (Nunmber=@SeqNo) and (trce_code = @trcecode)),'0')
    select @intCountPoce =(select count(*) from vw_license_w_poce where (Nunmber=@SeqNo) and (trce_code=@trcecode) and (posi_code=@posicode) and (poce_cendocperpos=@intPoceCode))
    select @LicGOCVal=(select id_validity_date from aj_licenses_earned where (Nunmber=@SeqNo) and (trce_code=@trcecode))

    if @LicNeeded ='Y'
    begin
    if @intCountPoce <> 0
    Begin

    if @gDocVal <> 0
    Begin

    if @prrecode = 8
    Begin

    if @gDocExSo ='Y'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@LicGOCVal,@gDocMoVal , @gDocVal )

    end
    if @gNewDocVal < @ExpectedSignOff and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='Y' where (trce_code=@trcecode)and (Nunmber=@seqno)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @gNewDocVal > @ExpectedSignOff and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='N' where (trce_code=@trcecode)and (Nunmber=@seqno)

    End
    if @LicGocVal is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    end
    if @gDocExSo ='N'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@LicGOCVal,@gDocMoVal , @gDocVal )

    end
    if @gNewDocVal > @LicGOCVal and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='Y' where (trce_code=@trcecode)and (Nunmber=@seqno)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @gNewDocVal < @LicGOCVal and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='N' where (trce_code=@trcecode)and (Nunmber=@seqno)

    End
    if @LicGOCVal is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    end
    End
    if @prrecode <> 8
    Begin

    if @LicNo <> '0'
    Begin

    update aj_licenses_earned WITH (ROWLOCK) set Expired_tag='N' where
    (trce_code=@trcecode) and (Nunmber=@SeqNo)
    End
    Else
    Begin

    insert into temp_cannotprint_Contract
    (Nunmber,description,created_by)
    values(@SeqNo,@Prredesc ,@ApplUserName)
    End
    End
    End
    if @gDocVal = 0
    Begin

    if @prrecode = 8
    Begin

    if @LicGOCVal < @ExpectedSignOff and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='Y' where (trce_code=@trcecode)and (Nunmber=@seqno)


    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    if @LicGOCVal > @ExpectedSignOff and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='N' where (trce_code=@trcecode)and (Nunmber=@seqno)

    End
    if @LicGOCVal is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpEctedSignOff,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    End
    if @prrecode <> 8
    Begin

    if @LicNo <> '0'
    Begin

    update aj_licenses_earned WITH (ROWLOCK) set Expired_tag='N' where
    (trce_code=@trcecode) and (Nunmber=@SeqNo)
    End
    Else
    Begin

    insert into temp_cannotprint_Contract
    (Nunmber,description,created_by)
    values(@SeqNo,@Prredesc,@ApplUserName)
    End
    End
    End
    End
    if @intCountPoce = 0
    begin

    if @prrecode <> 8
    Begin

    insert into temp_cannotprint_Contract
    (Nunmber,description,created_by)
    values(@SeqNo,@Prredesc ,@ApplUserName)

    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='Y' where
    (trce_code=@intTrainCenter) and (Nunmber=@SeqNo)
    End
    if @prrecode =8
    Begin
    print @prredesc
    if @gDocVal <> 0
    Begin

    if @gDocExSo ='Y'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@LicGocVal,@gDocMoVal , @gDocVal )
    end
    if @gNewDocVal < @ExpectedSignOff
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='Y' where (trce_code=@trcecode)and (Nunmber=@seqno)
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @gNewDocVal > @ExpectedSignOff
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='N' where (trce_code=@trcecode)and (Nunmber=@seqno)

    End
    if @LicGocVal is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    end
    if @gDocExSo ='N'
    begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@LicGOCVal,@gDocMoVal , @gDocVal )

    end
    if @gNewDocVal > @LicGOCVal and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='Y' where (trce_code=@trcecode)and (Nunmber=@seqno)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @gNewDocVal < @LicGOCVal and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='N' where (trce_code=@trcecode)and (Nunmber=@seqno)
    End
    if @LicGOCVal is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    end
    End
    if @gDocVal =0
    Begin
    if @LicGOCVal < @ExpectedSignOff and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='Y' where (trce_code=@trcecode)and (Nunmber=@seqno)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    if @LicGOCVal > @ExpectedSignOff and @LicGocVal is not null
    Begin
    update aj_licenses_earned WITH (ROWLOCK) set expired_tag='N' where (trce_code=@trcecode)and (Nunmber=@seqno)

    End
    if @LicGOCVal is null
    Begin
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@expectedSignOff,@LicGocVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    End
    End
    end
    End
    End
    if @LicNeeded ='N'
    Begin

    update aj_licenses_earned set Expired_tag='N' where
    (trce_code=@trcecode) and (Nunmber=@SeqNo)
    end

    if isnull(@PireCode,0) <> 0
    Begin
    Print 'remarks'
    End
    else --- others
    declare @cntCert int

    select @cntCert =isnull((SELECT DISTINCT COUNT(*) AS Expr2 FROM AJ_PROCESSING_REQ_DETAILS GROUP BY PRRE_CODE HAVING (PRRE_CODE IN (@prrecode))),0)
    if @cntCert > 0 --- Check certificates
    Begin
    --- check revalidated by mtc
    select @strRevalidateMTC=(select revalidate_mtc from vw_traincert_validated where (prma_seqno=@ChecklistNo) and (prre_code=@prrecode))
    select @intcertid = (SELECT CERT_ID FROM dbo.VW_TrainCert_Validated WHERE (PRMA_SEQNO = @ChecklistNo) AND (PRRE_CODE = @prrecode) AND (REVALIDATE_MTC = 'Y'))
    if @strrevalidatemtc='Y' and @PosRank ='O'
    begin

    select @certval =(select license_validity from aj_certificates_earned where (cert_id=@intCertId) and(trce_code=291) and (Nunmber=@Seqno))

    if @gDocVal <> 0
    begin

    if @gDocExSo='Y'
    Begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@CertVal,@gDocMoVal , @gDocVal )
    end
    if @certval is null
    begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='Y',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@CertVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    if @gNewDocVal < @ExpectedSignoff
    Begin



    update aj_certificates_earned WITH (ROWLOCK) set expired='Y',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@CertVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    else
    Begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='N',Updated_by=@ApplUsername,Update_date=getdate()

    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    End
    End
    if @gDocExSo ='N'
    Begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )
    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@CertVal,@gDocMoVal , @gDocVal )
    end
    if @certval is null
    begin
    update aj_certificates_earned WITH (ROWLOCK) set expired='Y',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@certVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    if @gNewDocVal > @certval and @CertVal is not null
    Begin


    update aj_certificates_earned WITH (ROWLOCK) set expired='Y',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@certVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    else
    Begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='N',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    End
    End
    end
    if @gDocVal=0
    begin

    if @certval is null
    begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='Y',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT, PRRE_CODE,CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@certVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    if @expectedsignoff > @certval and @Certval is not null
    Begin


    update aj_certificates_earned WITH (ROWLOCK) set expired='Y',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@ExpectedSignOff,@certVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    End
    else
    Begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='N',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)

    End
    end
    end
    if @strrevalidatemtc='Y' and @PosRank ='R'
    begin
    update aj_certificates_earned WITH (ROWLOCK) set expired='N',Updated_by=@ApplUsername,Update_date=getdate()
    where (Nunmber=@seqno) and (cert_id=@intcertid) and (trce_code=291)
    end
    --- check other certificates
    --if @prrecode = 14 --- BSEC
    Else
    Begin
    print @intCheckDest


    if @gDocVal <> 0
    Begin
    if @PrreDocVess <> 0
    Begin

    if @prredocvaldest is not null and @intCheckDest > 0
    Begin
    print @destination
    declare cur_cert cursor
    for
    SELECT TRCE_CODE, CERT_ID, LICENSE_DATE,LICENSE_VALIDITY
    FROM dbo.AJ_CERTIFICATES_EARNED
    WHERE(CERT_ID IN (SELECT CERT_ID AS Expr1
    FROM AJ_PROCESSING_REQ_DETAILS
    WHERE(PRRE_CODE IN(SELECT DISTINCT PRRE_CODE
    FROM dbo.AJ_PROCESSING_REQ_DOCVAL_VESS
    WHERE (VESS_CODE = @VessCode) AND (PRRE_CODE = @prrecode)
    AND (DESTINATION =@prredocvaldest))))) AND (Nunmber = @SeqNo)
    ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC
    End
    else
    Begin
    declare cur_cert cursor
    for
    SELECT TRCE_CODE, CERT_ID, LICENSE_DATE,LICENSE_VALIDITY
    FROM AJ_CERTIFICATES_EARNED
    WHERE (CERT_ID IN (SELECT CERT_ID
    FROM AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE IN(SELECT DISTINCT PRRE_CODE
    FROM AJ_PROCESSING_REQ_DOCVAL_VESS
    WHERE vess_code = @VessCode)AND (PRRE_CODE = @prrecode) ))) AND (Nunmber = @SeqNo)
    ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC
    End
    End
    if @PrreDocValPrin <> 0
    Begin

    declare cur_cert cursor
    for
    SELECT TRCE_CODE, CERT_ID, LICENSE_DATE,LICENSE_VALIDITY
    FROM AJ_CERTIFICATES_EARNED
    WHERE (CERT_ID IN (SELECT CERT_ID
    FROM AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE IN(SELECT DISTINCT PRRE_CODE
    FROM AJ_PROCESSING_REQ_DOCVAL_PRIN
    WHERE prin_code = @VesPrin)AND (PRRE_CODE = @prrecode) ))) AND (Nunmber = @SeqNo)
    ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC
    End
    if @PrreDocValPool <> 0
    Begin
    declare cur_cert cursor
    for
    SELECT TRCE_CODE, CERT_ID, LICENSE_DATE,LICENSE_VALIDITY
    FROM AJ_CERTIFICATES_EARNED
    WHERE (CERT_ID IN (SELECT CERT_ID
    FROM AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE IN(SELECT DISTINCT PRRE_CODE
    FROM AJ_PROCESSING_REQ_DOCVAL_POOL
    WHERE prpo_code = @VesPool)AND (PRRE_CODE = @prrecode) ))) AND (Nunmber = @SeqNo)
    ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC
    End
    if @PrreDocVal <> 0
    Begin
    declare cur_cert cursor
    for
    SELECT TRCE_CODE, CERT_ID, LICENSE_DATE,LICENSE_VALIDITY
    FROM AJ_CERTIFICATES_EARNED
    WHERE (CERT_ID IN (SELECT CERT_ID
    FROM AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE IN (SELECT PRRE_CODE
    FROM AJ_PROCESSING_REQ_DOCVAL
    WHERE (PRRE_CODE = @prrecode)))))AND (Nunmber = @SeqNo)
    ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC
    End
    open cur_cert
    fetch next from cur_cert into @certtrcecode,@certid,@certyear,@CertVal
    while @@fetch_status = 0
    Begin

    select @intMTC=(SELECT TOP 1 TRCE_CODE FROM dbo.AJ_CERTIFICATES_EARNED WHERE (CERT_ID IN (SELECT CERT_ID FROM dbo.AJ_PROCESSING_REQ_DETAILS WHERE (PRRE_CODE = @prrecode))) AND (Nunmber = @SeqNo) AND (TRCE_CODE = 291) ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC)
    select @LicCap=(select license_capacity from aj_certificates where (cert_iD=@CertID))
    select @CertIssued=@CertYear

    if @CertVal is null --@PrreDocVess <> 0 and @intCheckDest > 0
    Begin
    select @CertVal=isnull(@certyear,dbo.fn_GetExpectedSignoff (@certyear,@gDocMoVal,@gDocVal))
    End

    if @intMTC is null
    Begin

    select @certval =(SELECT TOP 1 LICENSE_DATE FROM dbo.AJ_CERTIFICATES_EARNED WHERE(CERT_ID IN (SELECT CERT_ID FROM dbo.AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE = @prrecode))) AND (Nunmber = @seqno) AND (TRCE_CODE <> 291)ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC)
    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@certval,@gDocMoVal , @gDocVal )
    end
    if @gNewDocVal < @ExpectedSignOff
    begin
    update aj_certificates_earned WITH (ROWLOCK) set expired ='Y',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo)and (cert_id=@certid)and (trce_code=@certtrcecode)
    IF NOT EXISTS( select Nunmber from TEMP_DEFICIENT_RECORDS
    where ( Nunmber = @SeqNo ) and (prre_code=@prrecode) and (deficient_records=@prredesc) )
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@certyear,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    if @gNewDocVal > @ExpectedSignOff
    begin

    update aj_certificates_earned WITH (ROWLOCK) set expired ='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo)and (cert_id=@certid)and (trce_code=@certtrcecode)
    end
    End

    if @intMTC is not null
    Begin
    if @gDocExSo='Y'
    Begin

    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )
    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@CertVal,@gDocMoVal , @gDocVal )
    end
    if @gNewDocVal < @ExpectedSignoff
    begin
    print @prredesc + ' is expired'
    update aj_certificates_earned WITH (ROWLOCK) set expired ='Y',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo)and (cert_id=@certid)and (trce_code=@certtrcecode)


    select @mtcExpired = (SELECT TOP 1 EXPIRED FROM dbo.AJ_CERTIFICATES_EARNED WHERE (CERT_ID IN (SELECT CERT_ID FROM dbo.AJ_PROCESSING_REQ_DETAILS WHERE (PRRE_CODE = @prrecode))) AND (Nunmber = @SeqNo) AND (TRCE_CODE = 291) ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC)
    if @mtcExpired ='Y'
    Begin

    select @certval =(SELECT TOP 1 LICENSE_DATE FROM dbo.AJ_CERTIFICATES_EARNED WHERE(CERT_ID IN (SELECT CERT_ID FROM dbo.AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE = @prrecode))) AND (Nunmber = @seqno) AND (TRCE_CODE <> 291)ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC)
    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@certVal,@gDocMoVal , @gDocVal )

    end


    if @gNewDocVal < @ExpectedSignOff
    begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='Y',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo) and (cert_id=@certid) and (trce_code=@certtrcecode)
    IF NOT EXISTS( select Nunmber from TEMP_DEFICIENT_RECORDS
    where ( Nunmber = @SeqNo ) and (prre_code=@prrecode) and (deficient_records=@prredesc ) )
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc ,@gNewDocVal,@certyear,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    if @gNewDocVal > @ExpectedSignOff

    begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo) and (cert_id=@certid) and (trce_code=@certtrcecode)
    end

    End
    if @mtcExpired='N'
    Begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo) and (cert_id=@certid) and (trce_code=@certtrcecode)
    End
    end
    if @gNewDocVal > @ExpectedSignOff
    begin
    update aj_certificates_earned WITH (ROWLOCK) set expired='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo) and (cert_id=@certid) and (trce_code=@certtrcecode)
    end

    End
    if @gDocExSo='N'
    Begin
    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )
    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@certVal,@gDocMoVal , @gDocVal )
    end
    if @gNewDocVal < @CertVal and @CertVal is not null
    Begin


    update aj_certificates_earned WITH (ROWLOCK) set expired='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo)and (cert_id=@certid) and (trce_code=@certtrcecode)
    End
    if @gNewDocVal > @CertVal and @CertVal is not null
    Begin


    select @mtcExpired = (SELECT TOP 1 EXPIRED FROM dbo.AJ_CERTIFICATES_EARNED WHERE (CERT_ID IN (SELECT CERT_ID FROM dbo.AJ_PROCESSING_REQ_DETAILS WHERE (PRRE_CODE = @prrecode))) AND (Nunmber = @SeqNo) AND (TRCE_CODE = 291) ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC)
    if @mtcExpired ='Y'
    Begin

    select @certval =(SELECT TOP 1 LICENSE_DATE FROM dbo.AJ_CERTIFICATES_EARNED WHERE(CERT_ID IN (SELECT CERT_ID FROM dbo.AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE = @prrecode))) AND (Nunmber = @seqno) AND (TRCE_CODE <> 291)ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC)
    if @gDocAddDepDocVal='Y'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@Depdate,@gDocMoVal , @gDocVal )

    end
    if @gDocAddDepDocVal='N'
    begin
    select @gNewDocVal =dbo.fn_GetExpectedSignoff (@CertVal,@gDocMoVal , @gDocVal )

    end
    print convert(varchar(10),@certval,103) + ' Latest doc not issued by MTC'

    if @gNewDocVal < @ExpectedSignOff
    begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='Y',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo) and (cert_id=@certid) and (trce_code=@certtrcecode)
    IF NOT EXISTS( select Nunmber from TEMP_DEFICIENT_RECORDS
    where ( Nunmber = @SeqNo ) and (prre_code=@prrecode) and (deficient_records=@prredesc + ' For Refresher Course') )
    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@gNewDocVal,@certyear,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)

    end
    if @gNewDocVal > @ExpectedSignOff

    begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo) and (cert_id=@certid) and (trce_code=@certtrcecode)
    end

    End
    if @mtcExpired='N'
    Begin

    update aj_certificates_earned WITH (ROWLOCK) set expired='N',updated_by=@applusername,update_date=getdate()
    where (Nunmber=@SeqNo) and (cert_id=@certid) and (trce_code=@certtrcecode)
    End
    End
    End
    End

    fetch next from cur_cert into @certtrcecode,@certid,@certyear,@CertVal
    End
    close cur_cert
    deallocate cur_cert


    End
    if @gDocVal =0
    Begin

    declare cur_certs cursor
    for
    select TRCE_CODE,CERT_ID,LICENSE_DATE,LICENSE_VALIDITY
    FROM AJ_CERTIFICATES_EARNED
    WHERE (CERT_ID IN (SELECT CERT_ID FROM AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE=@PRRECODE))) AND (Nunmber=@sEQnO)

    open cur_certs
    fetch next from cur_certs into @certtrcecode,@certid,@certyear,@CertVal

    while @@fetch_status = 0
    Begin
    select @intMTC=(SELECT TOP 1 TRCE_CODE FROM dbo.AJ_CERTIFICATES_EARNED WHERE (CERT_ID IN (SELECT CERT_ID FROM dbo.AJ_PROCESSING_REQ_DETAILS WHERE (PRRE_CODE = @prrecode))) AND (Nunmber = @SeqNo) AND (TRCE_CODE = 291) ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC)
    select @LicCap=(select license_capacity from aj_certificates where (cert_iD=@CertID))
    select @CertIssued=@CertYear

    if @intMTC is not null
    begin
    print @prredesc
    end
    if @intMTC is null
    begin

    select @certval=(SELECT TOP 1 LICENSE_DATE FROM dbo.AJ_CERTIFICATES_EARNED WHERE(CERT_ID IN (SELECT CERT_ID FROM dbo.AJ_PROCESSING_REQ_DETAILS
    WHERE (PRRE_CODE = @prrecode))) AND (Nunmber = @seqno) AND (TRCE_CODE <> 291)ORDER BY LICENSE_DATE DESC, CREATE_DATE DESC)
    if @certval is not null
    begin
    print @prredesc
    end
    if @certval is null
    begin

    INSERT INTO TEMP_DEFICIENT_RECORDS
    (Nunmber, DEFICIENT_RECORDS, EXSIGNOFF, DOCVALIDITY, DEPARTURE_DATE, CONTRACT_DURATION, CONTRACT_UNIT,PRRE_CODE, CREATE_DATE,CREATED_BY)
    VALUES (@SeqNo,@prredesc,@ExpectedSignOff,@CertVal,@DepDate,@ConLen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    end
    end
    fetch next from cur_certs into @certtrcecode,@certid,@certyear,@certval
    End
    close cur_certs
    deallocate cur_certs

    End
    End
    End
    if @prrecode = 27
    Begin
    declare @PDOSValdate as datetime
    declare @PdosMo int
    declare @strMoDay char(1)
    select @PdosMo =(select doc_validity from aj_processing_req_docval where (prre_code=@prrecode))
    select @strMoDay =(select days_months from aj_processing_req_docval where (prre_code=@prrecode))
    if @strMoDay ='M'
    Begin
    set @PDOSValdate=dateadd(Month,@PdosMo,(SELECT LAST_PDOS_DATE_ATTENDED FROM cw WHERE (Nunmber=@SeqNo)))
    End

    if @strModay ='D'
    Begin
    set @PDOSValdate=dateadd(Day,@PdosMo,(SELECT LAST_PDOS_DATE_ATTENDED FROM cw WHERE (Nunmber=@SeqNo)))
    End
    If year(@PDOSValdate) <= year(@Depdate)
    Begin
    insert into temp_deficient_records
    (Nunmber,deficient_records,exsignoff,docvalidity,departure_date,contract_duration,contract_unit,PRRE_CODE,create_date,created_by)
    values(@SeqNo,@prredesc,@ExpectedSignOff,@PdosValDate,@depDate,@Conlen,@ConUnit,@prrecode,getdate(),@ApplUserName)
    End
    End

    print ' *** End ' +@Prredesc + ' ***'
    fetch next from cur_master_doc into @PrreCode, @PrreDesc, @YesNo ,@BookType,@BookDesc,@TrceCode,@TrceAbbr,@HedeCode ,@HedeDesc,
    @HereCode ,@HereDesc, @DocCode , @DocDesc, @Noofdays, @TareCode, @TareDesc, @MeexCode, @MeexDesc ,
    @TankerYesNo, @BookWValidYesNo, @CheckInNo , @CheckInIssued , @PireCode , @PireDesc
    End
    close cur_master_doc
    deallocate cur_master_doc
    End
    GO

  8. Adriaan New Member

    Code like this ...
    select @VessCode =(Select Vess_Code from LU where Nunmber=@SeqNo)
    select @DepDate =(select departure_date from LU where (Nunmber=@SeqNo))
    select @ConLen =(select contract_length from LU where (Nunmber=@SeqNo))
    select @PosiCode =(select POSI_CODE from LU where (Nunmber=@SeqNo))
    select @ChecklistNo =(select prma_seqno from LU where (Nunmber=@SeqNo))
    select @ConUnit =(select LENGTH_UNIT from LU where (Nunmber=@SeqNo))
    ... is a bad omen. These six assignments can be done in one call, instead of six:
    select @VessCode = Vess_Code, @DepDate = departure_date, @ConLen = contract_length, @PosiCode = POSI_CODE, @ChecklistNo = prma_seqno, @ConUnit = LENGTH_UNIT
    from LU where (Nunmber=@SeqNo)
    There is at least one more instance of this type of code, but I haven't worked through the whole procedure. The code then opens a cursor, with many IF conditions for responding to specific values ...
    It looks as if the developer(s) did not know how to handle things in set-based logic. There may be a some items that cannot be handled that way, but a lot of them probably can, and they should help reducing the locking issues.
  9. Janette_I New Member

    thanks, I'll try to locate some of the codes like that and change it, what is set-based logic?
  10. Adriaan New Member

    Perhaps I should have said "set-based processing", but anyway ...
    The code that you have resembles the code like you would find in a Visual Basic procedure: you declare variables, you assign values to the variables, and you follow some logic to process the values from the variables one way or another.
    But in T-SQL you don't need to use variables to pass values from one table to another: you just use an insert query. And you don't need to process one set of values at a time: you can process complete rowsets. And you can do logical evaluations just by referring to the column names. And ...
    In all, for an optimum use of T-SQL you should take a slightly different approach than you would in a regular programming environment. You still have a lot of procedural elements to support complex processing, but the core operations should center on rowsets, IOW on queries.

Share This Page