-----------------------------------------------------------------------------------
--
--  Name:    PollEnd
--  Purpose: PollEnd request signifies that the change entries identified with the current
--           interval have been successfully entered into the store and forward database
--           and can be deleted from the article log tables.
--  Input:
--           argLSN         IN RAW(10)      LSN from distributor that was associated
--                                          with this poll interval
--  Output:
--  Notes:   This request causes those entries of the article log tables represented in the
--           Poll Table and having the current pollid to be deleted from both their article log
--           tables and from the Poll Table. The last request value is updated to reflect a
--           PollEnd request.
--
-----------------------------------------------------------------------------------
PROCEDURE PollEnd
(
    argLSN      IN RAW
)
AS
    SQLCommand      VARCHAR2(500);
    LogTable        VARCHAR2(255);
    CurrentPollID   NUMBER;
    TableIDs        number_tab;
    InstanceIDs     number_tab;
    IDCount         BINARY_INTEGER;
    PublisherLSN    RAW(10);

BEGIN
    -- Put the published tableIDs in a PL/SQL table of IDs
    HREPL.GetTableIDs(TableIDs, InstanceIDs);

    -- Get the current Poll ID
    SELECT Publisher_CurrentPollid INTO CurrentPollID FROM HREPL_Publisher;

    IDCount := TableIDs.COUNT;
    -- For each table represented in the ID list
    FOR id_ind IN 1 .. IDCount
    LOOP

        LogTable := REPLACE( REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(TableIDs(id_ind))),
                                                         MatchStringY, TO_CHAR(InstanceIDs(id_ind)));

        BEGIN
            -- Generate command to delete from the article log those entries appearing in the
            -- Poll Table with the current PollID
            SQLCommand := 'DELETE FROM ' || LogTable || ' l ' ||
                              'WHERE EXISTS (SELECT p.POLL_POLLID FROM HREPL_POLL p ' ||
                              '              WHERE CHARTOROWID(l.ROWID) = p.Poll_ROWID '  ||
                              '              AND p.Poll_PollID = :Pollid)';

            HREPL.ExecuteCommandForPollID(SQLCommand, CurrentPollID);

        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    FOR POLLID IN (SELECT CurrentPollid FROM DUAL)
    LOOP
        -- Delete from HREPL_Event those entries appearing in the Poll Table
        -- with the current PollID.
        DELETE FROM HREPL_Event e
        WHERE EXISTS (SELECT p.POLL_POLLID FROM HREPL_POLL p
                      WHERE CHARTOROWID(e.ROWID) = p.Poll_ROWID
                      AND p.Poll_PollID = POLLID.CurrentPollID);

        -- Delete entries from the Poll Table having the current Pollid
        DELETE FROM HREPL_Poll
        WHERE Poll_PollID = POLLID.CurrentPollID;
    END LOOP;

    -- Drop all views associated with articles that are marked as UnPublishPending.
    -- Note:  We cannot perform these drops in UnPublish table, since UnPublish
    --        table can execute concurrently with PollBegin and the querying
    --        of published tables by the log reader.  PollEnd, however, executes
    --        synchronously with respect to these activities, so can be used
    --        to cleanup log tables and views that are no longer needed.
    HREPL.CleanupLogsandViews;

    -- Mark the last request as PollEnd, and update the Publisher LSN
    -- to reflect the LSN committed at the publisher.
    UPDATE  HREPL_Publisher
    SET Publisher_PollInProcess = NoPollInProcess,
        Publisher_LSN = argLSN;

    -- Commit transaction
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;

END PollEnd;