ALTER PROCEDURE [dbo].[Uspschedulingstep04of05forcloseout] @tblFASTCloseOutResponse TVPFASTCLOSEOUTRESPONSE READONLY,
                                                           @tblSAStatuses           TVPSASTATUSES READONLY
AS
  BEGIN
      DECLARE @ShipperApptRequestID VARCHAR(12)
      DECLARE @ConsigneeApptID VARCHAR(12)
      DECLARE @FASTStatus VARCHAR(25)
      DECLARE @TrackingID VARCHAR(12)
      DECLARE @SchedulerCorpID VARCHAR(12)
      DECLARE @SchedulerID VARCHAR(12)
      DECLARE @SchedulerCRID VARCHAR(12)
      DECLARE @CreatorSchedulerCorpID VARCHAR(12)
      DECLARE @CreatorSchedulerID VARCHAR(12)
      DECLARE @CreatorSchedulerCRID VARCHAR(12)
      DECLARE @ShipperMultiStopID VARCHAR(12)
      DECLARE @ConsigneeMultiStopID VARCHAR(12)
      DECLARE @ShipperStopNumber VARCHAR(6)
      DECLARE @ApptType VARCHAR(50)
      DECLARE @PickUpOrDelivery VARCHAR(50)
      DECLARE @PalletPositionCount VARCHAR(2)
      DECLARE @ApptMethod VARCHAR(75)
      DECLARE @LocaleKey VARCHAR(10)
      DECLARE @ScheduledAppt DATETIME
      DECLARE @ActualTruckArrival DATETIME
      DECLARE @ActualUnloadStart DATETIME
      DECLARE @ActualUnloadEnd DATETIME
      DECLARE @ActualPalletCount INT
      DECLARE @ActualBedloadCount INT
      DECLARE @IsCreatedBySA BIT

      SET @ShipperApptRequestID = NULL
      SET @ConsigneeApptID = NULL
      SET @FASTStatus = NULL
      SET @TrackingID = NULL
      SET @SchedulerCorpID = NULL
      SET @SchedulerID = NULL
      SET @SchedulerCRID = NULL
      SET @CreatorSchedulerCorpID = NULL
      SET @CreatorSchedulerID = NULL
      SET @CreatorSchedulerCRID = NULL
      SET @ShipperMultiStopID = NULL
      SET @ConsigneeMultiStopID = NULL
      SET @ShipperStopNumber = NULL
      SET @ApptType = NULL
      SET @PickUpOrDelivery = NULL
      SET @PalletPositionCount = NULL
      SET @ApptMethod = NULL
      SET @LocaleKey = NULL
      SET @ScheduledAppt = NULL
      SET @ActualTruckArrival = NULL
      SET @ActualUnloadStart = NULL
      SET @ActualUnloadEnd = NULL
      SET @ActualPalletCount = NULL
      SET @ActualBedloadCount = NULL
      SET @IsCreatedBySA = NULL

      DECLARE @NoneStatus VARCHAR(25)
      DECLARE @TrackingStatus VARCHAR(25)
      DECLARE @FASTRejectedStatus VARCHAR(25)
      DECLARE @SARejectedStatus VARCHAR(25)
      DECLARE @FASTPendingStatus VARCHAR(25)

      SET @NoneStatus = NULL
      SET @TrackingStatus = NULL
      SET @FASTRejectedStatus = NULL
      SET @SARejectedStatus = NULL
      SET @FASTPendingStatus = NULL

      SELECT @NoneStatus = [None],
             @TrackingStatus = Tracking,
             @FASTRejectedStatus = FASTRejected,
             @SARejectedStatus = SARejected,
             @FASTPendingStatus = FASTPending
      FROM   @tblSAStatuses

      DECLARE @TriggeringField VARCHAR(3)

      SELECT @TriggeringField = dbo.Gettriggeringfield()

      SET NOCOUNT ON;

      DECLARE CloseOut_Cursor CURSOR FOR
        SELECT ShipperApptRequestID,
               ConsigneeApptID,
               FASTStatus,
               TrackingID,
               SchedulerCorpID,
               SchedulerID,
               SchedulerCRID,
               CreatorSchedulerCorpID,
               CreatorSchedulerID,
               CreatorSchedulerCRID,
               ShipperMultiStopID,
               ConsigneeMultiStopID,
               ShipperStopNumber,
               ApptType,
               PickUpOrDelivery,
               PalletPositionCount,
               ApptMethod,
               LocaleKey,
               ScheduledAppt,
               ActualTruckArrival,
               ActualUnloadStart,
               ActualUnloadEnd,
               ActualPalletCount,
               ActualBedloadCount
        FROM   @tblFASTCloseOutResponse

      OPEN CloseOut_Cursor

      FETCH NEXT FROM CloseOut_Cursor INTO @ShipperApptRequestID, @ConsigneeApptID, @FASTStatus, @TrackingID, @SchedulerCorpID, @SchedulerID, @SchedulerCRID, @CreatorSchedulerCorpID, @CreatorSchedulerID, @CreatorSchedulerCRID, @ShipperMultiStopID, @ConsigneeMultiStopID, @ShipperStopNumber, @ApptType, @PickUpOrDelivery, @PalletPositionCount, @ApptMethod, @LocaleKey, @ScheduledAppt, @ActualTruckArrival, @ActualUnloadStart, @ActualUnloadEnd, @ActualPalletCount, @ActualBedloadCount;

      WHILE @@FETCH_STATUS = 0
        BEGIN
            --For Tracking/ FAST Rejected/ SA Rejected Status of CloseOut
            IF ( @FASTStatus = @TrackingStatus
                  OR @FASTStatus = @FASTRejectedStatus
                  OR @FASTStatus = @SARejectedStatus )
              BEGIN
                  IF EXISTS(SELECT [Status]
                            FROM   tblCloseOutRequest
                            WHERE  ShipperApptID = @ShipperApptRequestID)
                    BEGIN
                        IF( @TrackingID IS NULL
                             OR @TrackingID = 'N/A' )
                          BEGIN
                              UPDATE tblCloseOutRequest
                              SET    [Status] = @FASTStatus
                              WHERE  ShipperApptID = @ShipperApptRequestID
                          END
                        ELSE
                          BEGIN
                              UPDATE tblCloseOutRequest
                              SET    [Status] = @FASTStatus,
                                     TrackingID = @TrackingID,
                                     TrackingOccurAt = Getdate()
                              WHERE  ShipperApptID = @ShipperApptRequestID
                          END
                    END
                  ELSE
                    BEGIN
                        IF( @TrackingID IS NULL
                             OR @TrackingID = 'N/A' )
                          BEGIN
                              INSERT INTO tblCloseOutRequest
                                          (ShipperApptID,
                                           [Status],
                                           RequestDateTime)
                              VALUES      ( @ShipperApptRequestID,
                                            @FASTStatus,
                                            Getdate() )
                          END
                        ELSE
                          BEGIN
                              INSERT INTO tblCloseOutRequest
                                          (ShipperApptID,
                                           TrackingID,
                                           [Status],
                                           RequestDateTime,
                                           TrackingOccurAt)
                              VALUES      ( @ShipperApptRequestID,
                                            @TrackingID,
                                            @FASTStatus,
                                            Getdate(),
                                            Getdate() )
                          END
                    END

                  DELETE FROM APPT_ERRORS
                  WHERE  AER_CONSIGNEE_ID = @ConsigneeApptID
              END
            --For Open/ Closed/ No Show/ UnScheduled Status of CloseOut
            ELSE
              BEGIN
                  SET @IsCreatedBySA = 'False'

                  --For OneTime/ MultiStop/ Recurring Instance
                  IF EXISTS(SELECT APT_APPT_STATUS
                            FROM   APPT_REGULAR_INFO
                            WHERE  APT_PK_SHIPPER_APPT_ID = @ShipperApptRequestID)
                    BEGIN
                        SET @IsCreatedBySA = 'True'

                        UPDATE HIST_APPT_REGULAR_INFO
                        SET    APT_FST_TRUCKARRIVAL_DATETIME = @ActualTruckArrival,
                               APT_FST_UNLOADSTART_DATETIME = @ActualUnloadStart,
                               APT_FST_UNLOADEND_DATETIME = @ActualUnloadEnd,
                               APT_APPT_STATUS = @FASTStatus,
                               ActualPalletCount = @ActualPalletCount,
                               ActualBedloadCount = @ActualBedloadCount,
                               APT_SA_UPDATE_DATETIME = Getdate()
                        WHERE  APT_CONSIGNEE_APPT_ID = @ConsigneeApptID

                        UPDATE APPT_REGULAR_INFO
                        SET    APT_FST_TRUCKARRIVAL_DATETIME = @ActualTruckArrival,
                               APT_FST_UNLOADSTART_DATETIME = @ActualUnloadStart,
                               APT_FST_UNLOADEND_DATETIME = @ActualUnloadEnd,
                               APT_APPT_STATUS = @FASTStatus,
                               ActualPalletCount = @ActualPalletCount,
                               ActualBedloadCount = @ActualBedloadCount,
                               APT_SA_UPDATE_DATETIME = Getdate()
                        WHERE  APT_CONSIGNEE_APPT_ID = @ConsigneeApptID
                    END

                  --For ShellContent
                  IF EXISTS(SELECT SHL_APPT_STATUS
                            FROM   APPT_SHELL_INFO
                            WHERE  SHL_PK_SHIPPER_APPT_ID = @ShipperApptRequestID)
                    BEGIN
                        SET @IsCreatedBySA = 'True'

                        UPDATE HIST_APPT_SHELL_INFO
                        SET    APT_FST_TRUCKARRIVAL_DATETIME = @ActualTruckArrival,
                               APT_FST_UNLOADSTART_DATETIME = @ActualUnloadStart,
                               APT_FST_UNLOADEND_DATETIME = @ActualUnloadEnd,
                               SHL_APPT_STATUS = @FASTStatus,
                               ActualPalletCount = @ActualPalletCount,
                               ActualBedloadCount = @ActualBedloadCount,
                               SHL_SA_UPDATE_DATETIME = Getdate()
                        WHERE  SHL_CONSIGNEE_APPT_ID = @ConsigneeApptID

                        UPDATE HIST_APPT_SHELL_INFO
                        SET    APT_FST_TRUCKARRIVAL_DATETIME = @ActualTruckArrival,
                               APT_FST_UNLOADSTART_DATETIME = @ActualUnloadStart,
                               APT_FST_UNLOADEND_DATETIME = @ActualUnloadEnd,
                               SHL_APPT_STATUS = @FASTStatus,
                               ActualPalletCount = @ActualPalletCount,
                               ActualBedloadCount = @ActualBedloadCount,
                               SHL_SA_UPDATE_DATETIME = Getdate()
                        WHERE  SHL_CONSIGNEE_APPT_ID = @ConsigneeApptID
                    END

                  --For Appointment's Content(s) & StandAloneContent(s)
                  IF ( @TriggeringField = 'PGI' )
                    BEGIN
                        --For Appointment's Content(s)
                        UPDATE MDB_CONTAINER_INFO
                        SET    CON_CONTAINER_GROUPING_ID = @FASTStatus,
                               CON_SA_UPDATE_DATETIME = Getdate()
                        WHERE  CON_CONSIGNEE_ID = @ConsigneeApptID
                               AND CON_IS_LINKED = 1

                        UPDATE HIST_APPT_CONTENT_CONTAINER_INFO
                        SET    CON_CONTAINER_GROUPING_ID = @FASTStatus,
                               CON_SA_UPDATE_DATETIME = Getdate()
                        WHERE  CON_CONSIGNEE_ID = @ConsigneeApptID
                               AND CON_IS_LINKED = 1

                        UPDATE APPT_CONTENT_CONTAINER_INFO
                        SET    CON_CONTAINER_GROUPING_ID = @FASTStatus,
                               CON_SA_UPDATE_DATETIME = Getdate()
                        WHERE  CON_CONSIGNEE_ID = @ConsigneeApptID
                               AND CON_IS_LINKED = 1
                    END
                  ELSE
                    BEGIN
                        --For Appointment's Content(s)
                        UPDATE MDB_CONTAINER_INFO
                        SET    CON_POSTAGE_GROUPING_ID = @FASTStatus,
                               CON_SA_UPDATE_DATETIME = Getdate()
                        WHERE  CON_CONSIGNEE_ID = @ConsigneeApptID
                               AND CON_IS_LINKED = 1

                        UPDATE HIST_APPT_CONTENT_CONTAINER_INFO
                        SET    CON_POSTAGE_GROUPING_ID = @FASTStatus,
                               CON_SA_UPDATE_DATETIME = Getdate()
                        WHERE  CON_CONSIGNEE_ID = @ConsigneeApptID
                               AND CON_IS_LINKED = 1

                        UPDATE APPT_CONTENT_CONTAINER_INFO
                        SET    CON_POSTAGE_GROUPING_ID = @FASTStatus,
                               CON_SA_UPDATE_DATETIME = Getdate()
                        WHERE  CON_CONSIGNEE_ID = @ConsigneeApptID
                               AND CON_IS_LINKED = 1
                    END

                  --For Not SA Created Appointment(s)
                  IF ( @IsCreatedBySA = 'False' )
                    BEGIN
                        IF EXISTS(SELECT [Status]
                                  FROM   Closeout_Info
                                  WHERE  ConsigneeID = @ConsigneeApptID)
                          BEGIN
                              UPDATE Closeout_Info
                              SET    ShipperApptID = @ShipperApptRequestID,
                                     ConsigneeID = @ConsigneeApptID,
                                     SchedulerCorpID = @SchedulerCorpID,
                                     SchedulerID = @SchedulerID,
                                     SchedulerCRID = @SchedulerCRID,
                                     CreatorSchedulerCorpID = @CreatorSchedulerCorpID,
                                     CreatorSchedulerID = @CreatorSchedulerID,
                                     CreatorSchedulerCRID = @CreatorSchedulerCRID,
                                     ShipperMultiStopID = @ShipperMultiStopID,
                                     ConsigneeMultiStopID = @ConsigneeMultiStopID,
                                     ShipperStopNumber = @ShipperStopNumber,
                                     ApptType = @ApptType,
                                     PickUpOrDelivery = @PickUpOrDelivery,
                                     PalletPositionCount = @PalletPositionCount,
                                     ApptMethod = @ApptMethod,
                                     FacilityNumber = @LocaleKey,
                                     AppointmentScheduled = @ScheduledAppt,
                                     TruckArrival = @ActualTruckArrival,
                                     UnloadStart = @ActualUnloadStart,
                                     UnloadEnd = @ActualUnloadEnd,
                                     [Status] = @FASTStatus,
                                     ActualPalletCount = @ActualPalletCount,
                                     ActualBedloadCount = @ActualBedloadCount,
                                     TrackingID = @TrackingID,
                                     RecordLastModifiedDate = Getdate()
                              WHERE  ConsigneeID = @ConsigneeApptID
                          END
                        ELSE
                          BEGIN
                              INSERT INTO Closeout_Info
                                          (ShipperApptID,
                                           ConsigneeID,
                                           SchedulerCorpID,
                                           SchedulerID,
                                           SchedulerCRID,
                                           CreatorSchedulerCorpID,
                                           CreatorSchedulerID,
                                           CreatorSchedulerCRID,
                                           ShipperMultiStopID,
                                           ConsigneeMultiStopID,
                                           ShipperStopNumber,
                                           ApptType,
                                           PickUpOrDelivery,
                                           PalletPositionCount,
                                           ApptMethod,
                                           FacilityNumber,
                                           AppointmentScheduled,
                                           TruckArrival,
                                           UnloadStart,
                                           UnloadEnd,
                                           [Status],
                                           ActualPalletCount,
                                           ActualBedloadCount,
                                           TrackingID,
                                           RecordCreatedDate)
                              VALUES      ( @ShipperApptRequestID,
                                            @ConsigneeApptID,
                                            @SchedulerCorpID,
                                            @SchedulerID,
                                            @SchedulerCRID,
                                            @CreatorSchedulerCorpID,
                                            @CreatorSchedulerID,
                                            @CreatorSchedulerCRID,
                                            @ShipperMultiStopID,
                                            @ConsigneeMultiStopID,
                                            @ShipperStopNumber,
                                            @ApptType,
                                            @PickUpOrDelivery,
                                            @PalletPositionCount,
                                            @ApptMethod,
                                            @LocaleKey,
                                            @ScheduledAppt,
                                            @ActualTruckArrival,
                                            @ActualUnloadStart,
                                            @ActualUnloadEnd,
                                            @FASTStatus,
                                            @ActualPalletCount,
                                            @ActualBedloadCount,
                                            @TrackingID,
                                            Getdate() )
                          END
                    END
              END

            FETCH NEXT FROM CloseOut_Cursor INTO @ShipperApptRequestID, @ConsigneeApptID, @FASTStatus, @TrackingID, @SchedulerCorpID, @SchedulerID, @SchedulerCRID, @CreatorSchedulerCorpID, @CreatorSchedulerID, @CreatorSchedulerCRID, @ShipperMultiStopID, @ConsigneeMultiStopID, @ShipperStopNumber, @ApptType, @PickUpOrDelivery, @PalletPositionCount, @ApptMethod, @LocaleKey, @ScheduledAppt, @ActualTruckArrival, @ActualUnloadStart, @ActualUnloadEnd, @ActualPalletCount, @ActualBedloadCount;
        END

      CLOSE CloseOut_Cursor;

      DEALLOCATE CloseOut_Cursor;
  END