drop trigger T_ChangeApplicantStatus
/
CREATE  TRIGGER T_ChangeApplicantStatus ON applicant
FOR  UPDATE
AS
declare @OldStatus   varchar2 (10) ,@NewStatus   varchar2 (10),@ApplicantID varchar2 (10),@RequisitionId varchar2 (10)
declare @ChangedDate date , @NextStatus varchar2 (10), @ChangedBy varchar2 (10), @Changecomments varchar2 (150), @ActionType varchar2 (1)
BEGIN
	select @OldStatus = status_id from deleted
	select @NewStatus = status_id from inserted
	IF @OldStatus Is Null 
		Set @OldStatus = ''
	IF @NewStatus Is Null 
		Set @NewStatus = ''
	IF @OldStatus <> @NewStatus
	Begin
		Select @ApplicantID = applicant_id ,@RequisitionId = requisition_id , @ChangedDate = changed_date,@ActionType = action_type ,
			@NextStatus = next_status_id , @ChangedBy = changed_by , @Changecomments = change_comments
		from Inserted
		If Not ( @ActionType is Null )
		  Begin
			If @ActionType = 'R' Or @ActionType = 'D'
				Begin
				Select @RequisitionId = requisition_id from deleted
				End
			Insert Into applicanct_status_history (applicant_id , status_id , requisition_id , changed_date , next_status_id  , changed_by , comments , action_type)
			Values (@ApplicantID,@NewStatus,@RequisitionId, @ChangedDate  , @NextStatus , @ChangedBy , @Changecomments , @ActionType)
		 End 
	End
END
/
drop trigger close_suc_plan
/
CREATE TRIGGER close_suc_plan ON employee
FOR  UPDATE
AS
declare @Closed_Auto   varchar2 (1)
declare @Emp_code      varchar2 (10)
declare @PositionBefore  varchar2 (10)
declare @PositionAfter  varchar2 (10)
BEGIN
	Select  @PositionBefore = position_id from deleted  
	Select  @Emp_code = employee, @PositionAfter = position_id from inserted 
	IF @PositionAfter <> @PositionBefore 
	BEGIN
		Select  @Closed_Auto =value_1  from system_setup where original_parm = 'CLOSESUCCESSTIONPLANAUTO'
		IF @Closed_Auto = 'Y'  
			UPDATE CD_succession_plan  SET suc_status = 'C'  , closed_date = getdate()
			WHERE ( CD_succession_plan.employee = @Emp_code ) AND  
				( CD_succession_plan.position_id = @PositionAfter ) AND  
				( CD_succession_plan.active = 'A' ) and (CD_succession_plan.suc_status <> 'C')
	END
END
/
drop trigger cd_training_close
/
CREATE TRIGGER cd_training_close ON CD_suc_cources
FOR UPDATE
AS
Declare @closed_flag    	varchar2 (1)
Declare @closed_flag_after    	varchar2 (1)
Declare @update_data    	varchar2 (1)
Declare @training_status 	varchar2 (1)
Declare @Serial               	Int
Declare @plan_id            	varchar2 (10)
Declare @Emp_code      		varchar2 (10)
Declare @usercatalog_flag  	varchar2 (1)
Declare @activity_id  		varchar2 (10)
Declare @cat_description  	varchar2 (100)
Declare @cat_description_e  	varchar2 (100)
Declare @actual_date  		date
Declare @evaluation     	decimal(13,8)
Declare @evaluation_date  	date
Declare @organization_id  	varchar2 (10)
Declare @active  		varchar2 (1)
Declare @comment  		varchar2 (100)
Declare @EmpCount  		Int

BEGIN
Select @closed_flag = training_status from deleted
Select @closed_flag_after = training_status from inserted
IF @closed_flag_after <> @closed_flag and @closed_flag_after = 'C'
BEGIN

Select @update_data  = value_1 from system_setup where original_parm = 'UPDATEEMPLOYEEDATA'
Select @activity_id = activity_id From inserted
Select @plan_id = suc_plan_id From inserted
Select @Emp_code = employee From cd_succession_plan where suc_plan_id = @plan_id
IF @update_data = 'Y'-- and @closed_flag = 'C'
BEGIN
Set @EmpCount = NVL((Select NVL(count(employee),0) from prs_emp_train Where tr_activity_id = @activity_id and employee = @Emp_code) , 0 ) 
IF  @EmpCount = 0
BEGIN
Select @training_status = training_status From inserted
Select @Serial = NVL(max(serial),0) + 1 from prs_emp_train where employee =  @Emp_code
Select @usercatalog_flag = usercatalog_flag From inserted
Select @cat_description = cat_description From inserted
Select @cat_description_e = cat_description_e From inserted
Select @actual_date = actual_date From inserted
Select @evaluation = evaluation From inserted
Select @evaluation_date = evaluation_date From inserted
Select @organization_id = organization_id From inserted
Select @active = active From inserted
Select @comment = comment From inserted

INSERT INTO prs_emp_train( employee,serial,	usercatalog_flag, tr_activity_id, cat_description,
			       cat_description_e,finish_date,evaluation,
			       evaluation_date,organization_id ,active,notes )
Values                  (@Emp_code, @Serial, @usercatalog_flag  , @activity_id , @cat_description, @cat_description_e , @actual_date ,
		@evaluation , @evaluation_date,@organization_id , @active , @comment	)    
END
END
END
END
/
drop trigger cd_suc_plan_close
/
CREATE trigger cd_suc_plan_close ON prs_emp_train
FOR INSERT,UPDATE
AS
Declare @Emp_code      varchar2 (10)
Declare @activity_id  varchar2 (10)
BEGIN
Select @Emp_code  = employee from inserted
select @activity_id = tr_activity_id from inserted
update CD_suc_cources
set training_status = 'C'
where ( activity_id = @activity_id )  and ( training_status <> 'C') And 
	suc_plan_id in (select suc_plan_id from cd_succession_plan
			where employee = @Emp_code and suc_status <> 'C') 
END
/
drop trigger cd_educ_close
/
CREATE TRIGGER cd_educ_close ON CD_suc_educ
FOR UPDATE
AS
Declare @closed_flag    varchar2 (1)
Declare @closed_flag_after    varchar2 (1)
Declare @update_data    varchar2 (1)
Declare @Serial               Int
Declare @plan_id            varchar2 (10)
Declare @Emp_code      varchar2 (10)
Declare @cert_degree_id  varchar2 (10)
Declare @speciality  varchar2 (10)
Declare @graduation_date  date
Declare @university  varchar2 (10)
Declare @active  varchar2 (1)
Declare @comment  varchar2 (100)
Declare @EmpCount  Int
Declare @organization_id varchar2 (10)
Declare @faculty_id varchar2 (10)
Declare @faculty_dep varchar2 (10)
BEGIN

  Select @closed_flag = NVL(cert_status, 'O') from deleted
  Select @closed_flag_after = NVL(cert_status,'O') from inserted
  
  IF @closed_flag_after <> @closed_flag and @closed_flag_after = 'C'
     BEGIN
       Select @update_data  = value_1 from system_setup where original_parm = 'UPDATEEMPLOYEEDATA'
       Select @cert_degree_id = scientific_degree From inserted
       Select @plan_id = suc_plan_id From inserted
       Select @Emp_code = employee From cd_succession_plan where suc_plan_id = @plan_id
       IF @update_data = 'Y'
         BEGIN
           Set @EmpCount = NVL((Select NVL(count(employee),0) from prs_emp_educ_his Where scientific_degree = @cert_degree_id And employee = @Emp_code),0)
           IF  @EmpCount = 0
             BEGIN
               Select @Serial = NVL(max(educ_serial),0) + 1 from prs_emp_educ_his where employee =  @Emp_code
               Select @speciality = speciality From inserted
               Select @graduation_date = graduation_date From inserted
               Select @university = university From inserted
               --Select @actual_date = actual_date From inserted
               --Select @evaluation = evaluation From inserted
               --Select @evaluation_date = evaluation_date From inserted
               Select @organization_id = organization_id From inserted
               Select @active 		= active From inserted
               Select @comment 	= comment From inserted
               Select @faculty_id 	= faculty_id From inserted
               Select @faculty_dep 	= faculty_dep From inserted

               INSERT INTO prs_emp_educ_his( employee,educ_serial,	scientific_degree, speciality,graduation_date,university,
			       faculty_id,faculty_dep,educ_notes ,active,organization_id)
               Values                  (@Emp_code, @Serial,  @cert_degree_id  , @speciality , @graduation_date, @university , @faculty_id ,
		   @faculty_dep , @comment,@active ,@organization_id	)    
             END
         END
   END
END
/
drop trigger cd_suc_plan_educ_close
/
CREATE trigger cd_suc_plan_educ_close ON prs_emp_educ_his
FOR INSERT,UPDATE
AS
Declare @Emp_code      varchar2 (10)
Declare @scientific_degree  varchar2 (10)

BEGIN
Select @Emp_code  = employee from inserted
select @scientific_degree = scientific_degree from inserted
update CD_suc_educ set cert_status = 'C' where scientific_degree = @scientific_degree and ( cert_status <> 'C')  and 
	suc_plan_id in ( select suc_plan_id from cd_succession_plan where employee = @Emp_code and suc_status <> 'C' )
END
/