SP Help

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

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

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

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

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

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.

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

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.