If exist......... else if NOT Exist SSIS procedure

CREATE PROCEDURE [dbo].[AddEventDetailsFromStaging]
@ExpenseId AS VARCHAR(50)='',
@Expense_Nature AS VARCHAR(100)='',
@Expense_Purpose AS VARCHAR(100)='',
@Activity_Type AS VARCHAR(100)='',
@Activity_State AS VARCHAR(100)='',
@Spend_Type AS VARCHAR(100)='',
@Attendee_FName AS VARCHAR(100)='',
@Attendee_MName AS VARCHAR(100)='',
@Attendee_LName AS VARCHAR(100)='',
@Customer_ID AS VARCHAR(50)='',
@AddressLine1 AS VARCHAR(200)='',
@LocationID AS VARCHAR(50)='',
@External_ExpenseID AS VARCHAR(50)='',
@Expense_Date AS DATETIME,
@Total_Attendees AS INT,
@Total_HCP_Attendees AS INT,
@Total_Amount AS DECIMAL(18,2)=0,
@Employee_Contact AS VARCHAR(100),
@Attendee_FullName AS VARCHAR(100)
AS
--BEGIN
  BEGIN TRAN
 DECLARE
  @intErrorCode INT,@Attendee_ID INT,@Event_ID INT,@StateId INT,@Event_TypeId INT,
  @EventTypeId INT,@COUNT INT,@NatureId INT,@PurposeId INT,@SpendItemId INT,
  @UPEvent_ID INT,@UPAttendee_ID INT,@Individualamount DECIMAL,
  @Event_Name_History  varchar(60),@Event_Type_Id_History INT,
  @Event_State_Id_History INT,@Event_Date_History VARCHAR(19),
  @Submitted_By_History  INT,@Employee_Contact_History VARCHAR(100),
  @Total_Attendees_History INT,@Total_HCP_Attendees_History INT,
  @Total_Amount_History INT,@User_Id INT,@History_id INT
 
 IF(@Activity_Type = 'EVENTEXP')
  SET @Activity_Type = 'Field Force T&E'

 SELECT @EventTypeId = Eventtype_Id FROM PMP_EventType WHERE UPPER([Type_Name]) = UPPER(@Activity_Type)
 SELECT @StateId = State_Id FROM PMP_StateMaster WHERE UPPER(State_initials) = UPPER(@Activity_State)
 IF @EventTypeId = ''
  SET @EventTypeId = NULL
 IF @StateId = ''
  SET @StateId = NULL
     SET @External_ExpenseID = 'MI-' + @External_ExpenseID
  SELECT @NatureId =  PaymentNature_Id FROM PMP_PaymentNature WHERE UPPER(Payment_Nature)=UPPER(@Expense_Nature)
  SELECT @PurposeId = PaymentPurpose_Id FROM PMP_PaymentPurpose WHERE UPPER(PaymentPurpose_Name)=UPPER(@Expense_Purpose)
  SELECT @SpendItemId = SpendItem_Id FROM  PMP_SpendItemMaster WHERE UPPER(SpendItem_Name) =UPPER(@Spend_Type)
  SELECT @User_Id = [User_Id] FROM PMP_UserInfo WHERE Username='MobileIntelligence' 
    
     -------Check Condition for Insert or Update record in Core Tables ----------------
    
     IF (EXISTS(SELECT EI.*, AI.Attendee_LName, AI.Attendee_FName, AI.Attendee_Id
     FROM PMP_Expenses AS EXPI INNER JOIN
     PMP_AttendeeInformation AS AI ON EXPI.Attendeeid = AI.Attendee_Id INNER JOIN
     PMP_EventInformation AS EI ON EXPI.Eventid = EI.Event_Id
     WHERE EI.Event_Type_Id=@EventTypeId
     AND EI.External_Activity_ID=@ExpenseId
     AND EI.Total_Attendees= @Total_Attendees
     AND EI.Total_HCP_Attendees=@Total_HCP_Attendees
     AND EI.Total_Amount=@Total_Amount
     AND EI.Event_Date = @Expense_Date
     AND EI.EmployeeContact=@Employee_Contact
     AND AI.Attendee_FName = @Attendee_FName
     AND AI.Attendee_LName = @Attendee_LName
     AND EXPI.SpendItemid=@SpendItemId
     AND EI.Event_Name=@External_ExpenseID
     AND CustomerID=@Customer_ID
     AND EI.Event_State_Id=@StateId ))
     BEGIN
     -------No Changes in Existing records of Core Tables
      SET @COUNT = 1
     END
 
     ELSE IF(NOT EXISTS(SELECT EI.*, AI.Attendee_LName, AI.Attendee_FName, AI.Attendee_Id
     FROM PMP_Expenses AS EXPI INNER JOIN
     PMP_AttendeeInformation AS AI ON EXPI.Attendeeid = AI.Attendee_Id INNER JOIN
     PMP_EventInformation AS EI ON EXPI.Eventid = EI.Event_Id
     WHERE EI.External_Activity_ID=@ExpenseId 
     --AND EI.Event_Type_Id=@EventTypeId
     AND AI.Attendee_FName = @Attendee_FName
     AND AI.Attendee_LName = @Attendee_LName
     AND EI.Event_Name=@External_ExpenseID))
      BEGIN 
        IF (@Customer_ID <> '')
   BEGIN
          INSERT INTO PMP_EventInformation
   (
     Event_Date,
     Individual_Amount,
     Total_Amount,
     Total_Attendees,
     Total_HCP_Attendees,
     Event_Type_Id,
     EmployeeContact,
     Event_State_Id,
     Request_Date,
     External_Activity_ID,
     Submitted_By,
     [Status],
     Event_Name
         )
     SELECT
     @Expense_Date,
     Individual_Amount,
     @Total_Amount,
     @Total_Attendees,
     @Total_HCP_Attendees,
     @EventTypeId,
     @Employee_Contact,
     @StateId,
     GETDATE(),
     @ExpenseId,
     @User_Id,
     'NEW',
     @External_ExpenseID
     FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
         END
        
      ELSE
        BEGIN
   INSERT INTO PMP_EventInformation_Error
    (
     Event_Date,
     Individual_Amount,
     Total_Amount,
     Total_Attendees,
     Total_HCP_Attendees,
     Event_Type_Id,
     EmployeeContact,
     Event_State_Id,
     Request_Date,
     External_Activity_ID,
     Submitted_By,
     [Status],
     Event_Name
          )
     SELECT
     @Expense_Date,
     Individual_Amount,
     @Total_Amount,
     @Total_Attendees,
     @Total_HCP_Attendees,
     @EventTypeId,
     @Employee_Contact,
     @StateId,
     GETDATE(),
     @ExpenseId,
     @User_Id,
     'NEW',
     @External_ExpenseID
     FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
        END  
     SELECT @Event_ID = @@IDENTITY
    -------------------------Insert Into AttendeeInformation---------
   IF (@Customer_ID <> '')
   BEGIN
    INSERT INTO PMP_AttendeeInformation
    (
       Attendee_FName,
       Attendee_MName,
       Attendee_LName,
       CustomerID
    )
    VALUES
    (
    @Attendee_FName,
    @Attendee_MName,
    @Attendee_LName,
    @Customer_ID
    )
  
   END
   ELSE
   BEGIN
    INSERT INTO PMP_AttendeeInformation_Error
    (
     Attendee_FName,
     Attendee_MName,
     Attendee_LName,
     CustomerID
    )
    VALUES
    (
    @Attendee_FName,
    @Attendee_MName,
    @Attendee_LName,
    @Customer_ID
    )
   END
  SELECT @Attendee_ID = @@IDENTITY
 
   -------------------------Insert Into PMP_Address-------------------------
 
  IF (@Customer_ID <> '' )
  BEGIN 
   INSERT INTO PMP_Address
    (
     Attendee_Id,
     AddressLine1,
     State_Id,
     Location_id
    )
    VALUES
    (
    @Attendee_ID,
    @AddressLine1,
    @StateId,
    @LocationID
    )
   END
    ELSE
     BEGIN 
     INSERT INTO PMP_Address_Error
    (
      Attendee_Id,
      AddressLine1,
      State_Id,
      Location_id
    )
    VALUES
    (
    @Attendee_ID,
    @AddressLine1,
    @StateId,
    @LocationID
    )
      END
    ------------------------------Insert Into PMP_EventAttendeeMapping--------
  
   IF (@Customer_ID <> '')
    BEGIN
    INSERT INTO PMP_EventAttendeeMapping
    (
     Event_Id,
     Attendee_Id
    )
    VALUES
    (
      @Event_ID,
      @Attendee_ID
    )
     END
     ELSE
      BEGIN
    INSERT INTO PMP_EventAttendeeMapping_Error
    (
     Event_Id,
     Attendee_Id
    )
    VALUES
    (
      @Event_ID,
      @Attendee_ID
    )
      END
 
  ---------------------Insert Into PaymentDetailsMapping------------
 
  IF @NatureId = ''
    SET @NatureId = NULL
     IF @PurposeId = ''
    SET @PurposeId = NULL
  IF @SpendItemId = ''
    SET @SpendItemId = NULL
 
  IF (@Customer_ID <> '')
   BEGIN
   INSERT INTO PMP_PaymentDetailsMapping
    (
     PaymentNature_Id,
     PaymentPurpose_Id,
     EventID,
     AttendeeId,
     SpenditemId,
     A_RoleID
    )
   VALUES
    (
     @NatureId,
     @PurposeId,
     @Event_ID,
     @Attendee_ID,
     @SpendItemId,
     5
    )
   END
   ELSE
    BEGIN
    INSERT INTO PMP_PaymentDetailsMapping_Error
    (
     PaymentNature_Id,
     PaymentPurpose_Id,
     EventID,
     AttendeeId,
     SpenditemId,
     A_RoleID
    )
   VALUES
    (
     @NatureId,
     @PurposeId,
     @Event_ID,
     @Attendee_ID,
     @SpendItemId,
     5
    )
    END
   -------------------------Insert Into PMP_Expenses---------
 
  IF (@Customer_ID <> '')
   BEGIN
   INSERT INTO PMP_Expenses
   (
     SpendItemid,
     Eventid,
     Attendeeid,
     Amount
   )
  
   SELECT
   @SpendItemId,
   @Event_ID,
   @Attendee_ID,
   Individual_Amount
   FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
  END
  ELSE
   BEGIN
     INSERT INTO PMP_Expenses_Error
   (
     SpendItemid,
     Eventid,
     Attendeeid,
     Amount
   )
  
    SELECT @SpendItemId,
    @Event_ID,
    @Attendee_ID,
    Individual_Amount FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
    END
        END
       
        ELSE
         BEGIN
         ------------------------------ Update Record ---------------------------------------
          ----------Find EventId and AttendeeId For Update Record----------
           SELECT @UPEvent_ID = EI.Event_Id,@UPAttendee_ID= AI.Attendee_Id FROM PMP_Expenses AS EXPI
     INNER JOIN PMP_AttendeeInformation AS AI ON EXPI.Attendeeid = AI.Attendee_Id
           INNER JOIN PMP_EventInformation AS EI ON EXPI.Eventid = EI.Event_Id
           WHERE EI.External_Activity_ID=@ExpenseId  
           --AND EI.Event_Type_Id=@EventTypeId
           AND AI.Attendee_FName = @Attendee_FName
           AND AI.Attendee_LName = @Attendee_LName
           AND EI.Event_Name=@External_ExpenseID
          ------------------------------------------------------------------
          ----Find Individual_Amount --------
         
           SELECT @Individualamount = Individual_Amount
           FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
          ------------------------------------
          
           --------------------Insert Into History Table---------------------------
         
            SELECT @History_ID = (CASE WHEN MAX(History_id) IS NULL THEN 1 ELSE MAX(History_id) + 1 END)    
      FROM PMP_Activity_History
  
   SELECT @Event_Name_History = Event_Name,
            @Event_Type_Id_History=Event_Type_Id,
            @Event_State_Id_History = Event_State_Id,
            @Event_Date_History = CONVERT(VARCHAR(19),Event_Date,120),
            @Employee_Contact_History = EmployeeContact,
      @Total_Attendees_History = Total_Attendees,
      @Total_HCP_Attendees_History = Total_HCP_Attendees,
      @Total_Amount_History = Total_Amount
      FROM PMP_EventInformation WHERE Event_Id = @UPEvent_ID
           
           IF(@Total_Amount_History <> @Total_Amount)
       BEGIN
       INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Total Amount',@Total_Amount_History,@Total_Amount)
       END
 
     IF(@Event_Type_Id_History <> @EventTypeId )
    BEGIN
       INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Event Type Id',@Event_Type_Id_History,@Activity_Type)
       END
      
         IF(@Event_State_Id_History <> @StateId)
   BEGIN
      INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Event State Id',@Event_State_Id_History,@StateId)
   END
      IF(@Total_HCP_Attendees_History <> @Total_HCP_Attendees)
      BEGIN
         INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Total HCP Attendees',@Total_HCP_Attendees_History,@Total_HCP_Attendees)
      END
 
            IF(@Total_Attendees_History <> @Total_Attendees)
      BEGIN
     INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Total Attendees',@Total_Attendees_History,@Total_Attendees)
      END
     
      IF(@Event_Date_History <> CONVERT(VARCHAR(19),@Expense_Date,120))
   BEGIN
        INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Event Date',@Event_Date_History,@Expense_Date)
      END
     
      IF(@Employee_Contact_History <> @Employee_Contact)
      BEGIN
       INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Employee Contact',@Employee_Contact_History,@Employee_Contact)
      END 
         
          ---------- Update PMP_EventInformation Table ------------
         
          UPDATE PMP_EventInformation SET  Event_Date = @Expense_Date, 
    Individual_Amount =  @Individualamount,Total_Amount = @Total_Amount,
    Total_Attendees = @Total_Attendees,Total_HCP_Attendees = @Total_HCP_Attendees,
    Event_Type_Id = @EventTypeId,EmployeeContact = @Employee_Contact,
    Event_State_Id =@StateId,Request_Date =GETDATE(),External_Activity_ID=@ExpenseId,  
    Submitted_By=@User_Id,[Status] = 'NEW',Event_Name = @External_ExpenseID  
    WHERE Event_Id = @UPEvent_ID  
   
       
        
  
  ---------------------------------------------------
         
          ---------- Update PMP_AttendeeInformation Table ------------
     UPDATE PMP_AttendeeInformation SET Attendee_FName = @Attendee_FName,
     Attendee_MName = @Attendee_MName,Attendee_LName = @Attendee_LName, 
     CustomerID = @Customer_ID  
     WHERE Attendee_Id = @UPAttendee_ID  
    
   ---------- Update PMP_Address Table ------------
  
    UPDATE  PMP_Address SET AddressLine1 = @AddressLine1,
    State_Id =  @StateId,Location_id = @LocationID  
    WHERE Attendee_Id = @UPAttendee_ID   
  
   ------------------------------------------------
         ---------- Update PMP_EventAttendeeMapping Table -
         
           UPDATE PMP_EventAttendeeMapping SET Event_Id = @UPEvent_ID,
   Attendee_Id =@UPAttendee_ID
   WHERE Attendee_Id = @UPAttendee_ID
  ------------------------------------------------
 
  ---------- Update PMP_PaymentDetailsMapping ----
            UPDATE PMP_PaymentDetailsMapping SET PaymentNature_Id = @NatureId,
   PaymentPurpose_Id = @PurposeId,EventID = @UPEvent_ID,
   AttendeeId = @UPAttendee_ID,SpenditemId = @SpendItemId,A_RoleID = 5
      WHERE AttendeeId = @UPAttendee_ID
  -----------------------------------------------
  ---------- Update PMP_Expenses ----
   UPDATE PMP_Expenses SET  SpendItemid = @SpendItemId,
   Eventid = @UPEvent_ID,Attendeeid = @UPAttendee_ID,
   Amount = @Individualamount   
   WHERE AttendeeId = @UPAttendee_ID  
    
  
         END
       
    SELECT @intErrorCode = @@ERROR
 IF (@intErrorCode <> 0) GOTO PROBLEM
 COMMIT TRAN
 ----------------------------------------
  PROBLEM:
 IF (@intErrorCode <> 0)
 BEGIN
     ---PRINT 'Unexpected error occurred!'
   ROLLBACK TRAN
    END