-- Name: sp_ORAhelparticle

-- Description:
--		Oracle specific help article

-- NOTE:
--		RMO flag tells helparticle to report extended
--		HREPL columns (which will be NULL for SQL publishers)


create procedure sys.sp_ORAhelparticle
(
    @pubid			int,
    @article        sysname,
    @returnfilter   bit,
    @publisher      sysname,
    @found          int OUTPUT,
    @rmo			bit = 0
)
AS
BEGIN
    DECLARE @retcode int

    SET NOCOUNT ON

    SELECT @found = 0

    -- Use explicit converts for constants/computed columns because the UI is
    -- very sensitive to data type.  Use BOL as a reference
	IF (@rmo = 0)
	BEGIN
		-- Normal case
	    SELECT  'article id'                    = iha.article_id,
	            'article name'                  = iha.name,
	            'base object'                   = CONVERT(nvarchar(257), QUOTENAME(msa.source_owner) + '.' + QUOTENAME(msa.source_object)),
	            'destination object'            = iha.dest_table,
	            'synchronization object'        = CONVERT(nvarchar(257), NULL),
	            -- Shiloh defined sp_helparticle's type to be a smallint (via the temp table)
	            -- To maintain UI backcompat, convert to smallint explicitly.
	            'type'                          = CONVERT(smallint, iha.type),
	            'status'                        = CONVERT(int, iha.status & ~32),
	            'filter'                        = CONVERT(nvarchar(257), NULL),
	            'description'                   = msa.description,
	            'insert_command'                = iha.ins_cmd,
	            'update_command'                = iha.upd_cmd,
	            'delete_command'                = iha.del_cmd,
	            'creation script path'          = iha.creation_script,
	            'vertical partition'            = CASE WHEN
	                                                    (
	                                                        SELECT count(*)
	                                                        FROM   IHpublishercolumns ihpc
	                                                        WHERE  ihpc.table_id = iha.table_id
	                                                    ) !=
	                                                    (
	                                                        select count(*)
	                                                        FROM    IHcolumns ihc
	                                                        WHERE   ihc.article_id = iha.article_id
	                                                    ) THEN CONVERT(bit, 1)
	                                                    ELSE CONVERT(bit, 0)
	                                              END,
	            'pre_creation_cmd'              = iha.pre_creation_cmd,
	            'filter_clause'                 = CASE
	                                                  WHEN @returnfilter = 1 THEN iha.filter_clause
	                                                  ELSE CONVERT(ntext, NULL)
	                                              END,
	            'schema_option'                 = iha.schema_option,
	            'dest_owner'                    = iha.dest_owner,
	            'source_owner'                  = msa.source_owner,
	            'unqua_source_object'           = msa.source_object,
	            'sync_object_owner'             = iha.article_view_owner,
	            'unqualified_sync_object'       = iha.article_view,
	            'filter_owner'                  = CONVERT(sysname, NULL),
	            'unqua_filter'                  = CONVERT(sysname, NULL),
	            'auto_identity_range'           = CONVERT(bit, 0),
	            'publisher_identity_range'      = CONVERT(bigint, NULL),
	            'identity_range'                = CONVERT(bigint, NULL),
	            'threshold'                     = CONVERT(bigint, NULL),
	            'identityrangemanagementoption' = CONVERT(int, 0),
	            'fire_triggers_on_snapshot'     = iha.fire_triggers_on_snapshot
	    FROM    IHpublications ihp,
	            IHarticles iha,
	            MSarticles msa
	    WHERE   ihp.pubid      = iha.publication_id
	      AND   ihp.pubid      = msa.publication_id
	      AND   iha.article_id = msa.article_id
	      AND   ihp.pubid      = @pubid
	      AND    ((@article = N'%') or (iha.name = @article))
	    ORDER BY iha.name
	END
	ELSE
	BEGIN
		-- RMO version
		CREATE TABLE #ORAtablespaceinfo
		(
			article_id int,
			tablespace sysname
		)

		-- Get tablespace information
		EXEC @retcode = sp_ORAGetTablespaceInfo @publisher = @publisher
		IF @retcode != 0 OR @@ERROR != 0
		BEGIN
			DROP TABLE #ORAtablespaceinfo
			RETURN (1)
		END

	    SELECT  'article id'                    = iha.article_id,
	            'article name'                  = iha.name,
	            'base object'                   = CONVERT(nvarchar(257), QUOTENAME(msa.source_owner) + '.' + QUOTENAME(msa.source_object)),
	            'destination object'            = iha.dest_table,
	            'synchronization object'        = CONVERT(nvarchar(257), NULL),
	            -- Shiloh defined sp_helparticle's type to be a smallint (via the temp table)
	            -- To maintain UI backcompat, convert to smallint explicitly.
	            'type'                          = CONVERT(smallint, iha.type),
	            'status'                        = CONVERT(int, iha.status & ~32),
	            'filter'                        = CONVERT(nvarchar(257), NULL),
	            'description'                   = msa.description,
	            'insert_command'                = iha.ins_cmd,
	            'update_command'                = iha.upd_cmd,
	            'delete_command'                = iha.del_cmd,
	            'creation script path'          = iha.creation_script,
	            'vertical partition'            = CASE WHEN
	                                                    (
	                                                        SELECT count(*)
	                                                        FROM   IHpublishercolumns ihpc
	                                                        WHERE  ihpc.table_id = iha.table_id
	                                                    ) !=
	                                                    (
	                                                        select count(*)
	                                                        FROM    IHcolumns ihc
	                                                        WHERE   ihc.article_id = iha.article_id
	                                                    ) THEN CONVERT(bit, 1)
	                                                    ELSE CONVERT(bit, 0)
	                                              END,
	            'pre_creation_cmd'              = iha.pre_creation_cmd,
	            'filter_clause'                 = CASE
	                                                  WHEN @returnfilter = 1 THEN iha.filter_clause
	                                                  ELSE CONVERT(ntext, NULL)
	                                              END,
	            'schema_option'                 = iha.schema_option,
	            'dest_owner'                    = iha.dest_owner,
	            'source_owner'                  = msa.source_owner,
	            'unqua_source_object'           = msa.source_object,
	            'sync_object_owner'             = iha.article_view_owner,
	            'unqualified_sync_object'       = iha.article_view,
	            'filter_owner'                  = CONVERT(sysname, NULL),
	            'unqua_filter'                  = CONVERT(sysname, NULL),
	            'auto_identity_range'           = CONVERT(bit, 0),
	            'publisher_identity_range'      = CONVERT(bigint, NULL),
	            'identity_range'                = CONVERT(bigint, NULL),
	            'threshold'                     = CONVERT(bigint, NULL),
	            'identityrangemanagementoption' = CONVERT(int, 0),
	            'fire_triggers_on_snapshot'     = iha.fire_triggers_on_snapshot,
	            'tablespace'					= otsi.tablespace,
	            'use_default_datatypes'			= iha.use_default_datatypes
	    FROM    IHpublications ihp INNER JOIN IHarticles iha ON ihp.pubid = iha.publication_id
	            INNER JOIN MSarticles msa ON ihp.pubid = msa.publication_id
	            LEFT OUTER JOIN #ORAtablespaceinfo otsi ON iha.article_id = otsi.article_id
	    WHERE   iha.article_id = msa.article_id
	      AND   ihp.pubid      = @pubid
	      AND    ((@article = N'%') or (iha.name = @article))
	    ORDER BY iha.name

	    DROP TABLE #ORAtablespaceinfo
	END
	
	IF @@rowcount > 0
	BEGIN
		SELECT @found = 1
	END

    RETURN (0)
END