USP_SPONSORSHIP_BULKTRANSFERPROCESS

Back-end logic for the sponsorship bulk transfer business process.

Parameters

Parameter	Parameter Type	Mode
@SPONSORSHIPSELECTIONID	uniqueidentifier	IN
@SUCCESSTABLE	nvarchar(128)	IN
@EXCEPTIONTABLE	nvarchar(128)	IN
@SUCCESSCOUNT	int	INOUT
@EXCEPTIONCOUNT	int	INOUT
@SPONSORSHIPPROGRAMID	uniqueidentifier	IN
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN
@GENDERCODE	int	IN
@SPROPPAGERANGEID	uniqueidentifier	IN
@ISHIVPOSITIVECODE	int	IN
@HASCONDITIONCODE	int	IN
@ISORPHANEDCODE	int	IN
@SPROPPPROJECTCATEGORYCODEID	uniqueidentifier	IN
@SPONSORPREFERENCESCODE	tinyint	IN
@TRANSFERTYPE	tinyint	IN
@MATCHRULE	tinyint	IN
@SPONSORSHIPREASONID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_SPONSORSHIP_BULKTRANSFERPROCESS (
    @SPONSORSHIPSELECTIONID uniqueidentifier,
    @SUCCESSTABLE nvarchar(128),
    @EXCEPTIONTABLE nvarchar(128),
    @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT int = 0 output,
    @SPONSORSHIPPROGRAMID uniqueidentifier = null,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @GENDERCODE int = 0,
    @SPROPPAGERANGEID uniqueidentifier = null,
    @ISHIVPOSITIVECODE int = 0,
    @HASCONDITIONCODE int = 0,
    @ISORPHANEDCODE int = 0,
    @SPROPPPROJECTCATEGORYCODEID uniqueidentifier = null,
    @SPONSORPREFERENCESCODE tinyint = 0,
    @TRANSFERTYPE tinyint = 0,
    @MATCHRULE tinyint = 0,
    @SPONSORSHIPREASONID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    set nocount on;

    create table #TRANSFERS (
        FROMSPONSORSHIPID uniqueidentifier,
        NEWSPONSORSHIPPROGRAMID uniqueidentifier,
        NEWSPONSORSHIPLOCATIONID uniqueidentifier,
        NEWSPONSORSHIPOPPORTUNITYID uniqueidentifier,
        NEWCHILDGENDERCODE tinyint,
        NEWSPONSORSHIPOPPORTUNITYAGERANGEID uniqueidentifier,
        NEWISHIVPOSITIVECODE tinyint,
        NEWHASCONDITIONCODE tinyint,
        NEWISORPHANEDCODE tinyint,
        NEWSPROPPPROJECTCATEGORYCODEID uniqueidentifier);

    insert into #TRANSFERS (
        FROMSPONSORSHIPID,
        NEWSPONSORSHIPPROGRAMID,
        NEWSPONSORSHIPLOCATIONID,
        NEWCHILDGENDERCODE,
        NEWSPONSORSHIPOPPORTUNITYAGERANGEID,
        NEWISHIVPOSITIVECODE,
        NEWHASCONDITIONCODE,
        NEWISORPHANEDCODE,
        NEWSPROPPPROJECTCATEGORYCODEID
    )
    select
        SPONSORSHIP.ID,
        @SPONSORSHIPPROGRAMID,
        case when @SPONSORSHIPLOCATIONID is not null then
          case
            -- use preferred location if it is more specific than the transfer location

            when @SPONSORPREFERENCESCODE = 0 and
                 @SPONSORSHIPLOCATIONID <> SPONSORSHIP.SPONSORSHIPLOCATIONID and
                 PREFERREDLOCATION.HIERARCHYPATH.IsDescendantOf(TRANSFERLOCATION.HIERARCHYPATH) = 1 then PREFERREDLOCATION.ID
            else @SPONSORSHIPLOCATIONID
          end
        end,
        case @GENDERCODE when 0 then null else @GENDERCODE end,
        @SPROPPAGERANGEID,
        case @ISHIVPOSITIVECODE when 0 then null else @ISHIVPOSITIVECODE end,
        case @HASCONDITIONCODE when 0 then null else @HASCONDITIONCODE end,
        case @ISORPHANEDCODE when 0 then null else @ISORPHANEDCODE end,
        @SPROPPPROJECTCATEGORYCODEID
    from dbo.SPONSORSHIP
    inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SPONSORSHIPSELECTIONID) INCLUDESPONSORSHIPS on INCLUDESPONSORSHIPS.ID = SPONSORSHIP.ID
    left join dbo.SPONSORSHIPLOCATION as PREFERREDLOCATION on PREFERREDLOCATION.ID = SPONSORSHIP.SPONSORSHIPLOCATIONID
    left join dbo.SPONSORSHIPLOCATION as TRANSFERLOCATION on TRANSFERLOCATION.ID = @SPONSORSHIPLOCATIONID;

    exec dbo.USP_SPONSORSHIP_BULKTRANSFER
        @SUCCESSTABLE,
        @EXCEPTIONTABLE,
        @TRANSFERTYPE,
        @MATCHRULE,
        @SPONSORSHIPREASONID,
        0,
        null,
        1,
        @CHANGEAGENTID,
        @SUCCESSCOUNT output,
        @EXCEPTIONCOUNT output,
        @SPONSORPREFERENCESCODE,
    0
end