USP_DATAFORMTEMPLATE_EDITLOAD_FAFNFGCAMPAIGNLEVELHIERARCHY

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@TSLONG	bigint	INOUT
@DATALOADED	bit	INOUT
@LEVELHIERARCHY	xml	INOUT
@ROOTLEVELID	uniqueidentifier	INOUT

Definition

Copy


          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_FAFNFGCAMPAIGNLEVELHIERARCHY(
            @ID uniqueidentifier,
            @TSLONG bigint = 0 output,
            @DATALOADED bit = 0 output,
            @LEVELHIERARCHY xml = null output,
            @ROOTLEVELID uniqueidentifier = null output
          ) as begin
            set nocount on;                    

            select
              @LEVELHIERARCHY = (
                select 
                  FAFNFGCAMPAIGNLEVEL.ID, 
                  (select PARENTLEVEL.ID from dbo.FAFNFGCAMPAIGNLEVEL AS PARENTLEVEL where FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetAncestor(1) = PARENTLEVEL.HIERARCHYPATH ) as PARENTID,
                  FAFNFGCAMPAIGNLEVEL.NAME, 
                  row_number() over (partition by FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetAncestor(1) order by HIERARCHYPATH) as SEQUENCE
                from
                dbo.FAFNFGCAMPAIGNLEVEL
                where FAFNFGCAMPAIGNLEVEL.NFGCAMPAIGNID = @ID
                order by FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH
                for xml raw('ITEM'),type,elements,root('LEVELHIERARCHY'),BINARY BASE64
               ),
               @DATALOADED = 1;
            select @ROOTLEVELID = FAFNFGCAMPAIGNLEVEL.ID from dbo.FAFNFGCAMPAIGNLEVEL where FAFNFGCAMPAIGNLEVEL.NFGCAMPAIGNID = @ID  and FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetLevel() = 1 
            return 0;
            end