USP_DATAFORMTEMPLATE_VIEW_PROSPECTPROFILE_2
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @DATALOADED | bit | INOUT | |
| @ISPROSPECTPROFILE | bit | INOUT | |
| @ISORGANIZATION | bit | INOUT | |
| @ADDRESS | nvarchar(300) | INOUT | |
| @PHONENUMBER | nvarchar(100) | INOUT | |
| @PHONETYPE | nvarchar(100) | INOUT | |
| @EMAILADDRESS | UDT_EMAILADDRESS | INOUT | |
| @WEBADDRESS | UDT_WEBADDRESS | INOUT | |
| @BOARDMEMBERCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @RELATIONCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @STAFFCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @DONORCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @FUNDRAISERCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @PROSPECTCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @VOLUNTEERCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @COMMUNITYMEMBERCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @USERDEFINEDCONSTITUENCYTEXT | nvarchar(4000) | INOUT | |
| @ALUMNUSSTATUSTEXT | nvarchar(100) | INOUT | |
| @REGISTRANTSTATUSTEXT | nvarchar(100) | INOUT | |
| @VENDORSTATUSTEXT | nvarchar(100) | INOUT | |
| @RELATEDCONSTITUENT | nvarchar(700) | INOUT | |
| @RELATEDCONSTITUENTID | uniqueidentifier | INOUT | |
| @ISINACTIVE | bit | INOUT | |
| @DECEASEDDATE | UDT_FUZZYDATE | INOUT | |
| @PICTURE | varbinary | INOUT | |
| @STATUS | nvarchar(200) | INOUT | |
| @PROSPECTMANAGERFUNDRAISERID | uniqueidentifier | INOUT | |
| @PROSPECTMANAGER | nvarchar(700) | INOUT | |
| @PROSPECTSTATUS | nvarchar(100) | INOUT | |
| @SOLICITCODECOUNT | int | INOUT | |
| @ISGROUP | bit | INOUT | |
| @GROUPTYPE | nvarchar(300) | INOUT | |
| @GROUPMEMBERCOUNT | int | INOUT | |
| @GIVESANONYMOUSLY | bit | INOUT | |
| @PLANNEDGIVERCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @BANKCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @PATRONCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @PROSPECTMANAGERSTARTDATE | datetime | INOUT | |
| @GRANTORCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @SPONSORCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @ISCOMMITTEEMEMBER | bit | INOUT | |
| @COMMITTEEMEMBERCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @ISDECEASED | bit | INOUT | |
| @ISSPOUSEDECEASED | bit | INOUT | |
| @CONSTITUENTINACTIVITYREASON | nvarchar(63) | INOUT | |
| @DONOTMAIL | bit | INOUT | |
| @DONOTEMAIL | bit | INOUT | |
| @DONOTPHONE | bit | INOUT | |
| @PHONEISCONFIDENTIAL | bit | INOUT | |
| @ADDRESSISCONFIDENTIAL | bit | INOUT | |
| @ADDRESSID | uniqueidentifier | INOUT | |
| @PHONENUMBERID | uniqueidentifier | INOUT | |
| @EMAILADDRESSID | uniqueidentifier | INOUT | |
| @HOUSEHOLDTEXT | nvarchar(154) | INOUT | |
| @HOUSEHOLDID | uniqueidentifier | INOUT | |
| @LIFECYCLESTAGE | nvarchar(1000) | INOUT | |
| @LIFECYCLESTAGEASOF | datetime | INOUT | |
| @PLANNEDGIVERSTAGE | nvarchar(15) | INOUT | |
| @PLANNEDGIVERSTAGEASOF | datetime | INOUT | |
| @DONORSTATECODE | tinyint | INOUT | |
| @DONORSTATE | nvarchar(250) | INOUT | |
| @LASTREVENUEDATE | datetime | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @MEMBERCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @MAJORDONORCONSTITUENCYTEXT | nvarchar(100) | INOUT | |
| @SOCIALMEDIAACCOUNTS | xml | INOUT |
Definition
Copy
create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTPROFILE_2(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@ISPROSPECTPROFILE bit = null output,
@ISORGANIZATION bit = null output,
@ADDRESS nvarchar(300) = null output,
@PHONENUMBER nvarchar(100) = null output,
@PHONETYPE nvarchar(100) = null output,
@EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
@WEBADDRESS dbo.UDT_WEBADDRESS = null output,
@BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
@RELATIONCONSTITUENCYTEXT nvarchar(100) = null output,
@STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
@DONORCONSTITUENCYTEXT nvarchar(100) = null output,
@FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
@PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
@VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
@COMMUNITYMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
@USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
@ALUMNUSSTATUSTEXT nvarchar(100) = null output,
@REGISTRANTSTATUSTEXT nvarchar(100) = null output,
@VENDORSTATUSTEXT nvarchar(100) = null output,
@RELATEDCONSTITUENT nvarchar(700) = null output,
@RELATEDCONSTITUENTID uniqueidentifier = null output,
@ISINACTIVE bit = null output,
@DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
@PICTURE varbinary(max) = null output,
@STATUS nvarchar(200) = null output,
@PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null output,
@PROSPECTMANAGER nvarchar(700) = null output,
@PROSPECTSTATUS nvarchar(100) = null output,
@SOLICITCODECOUNT int = null output,
@ISGROUP bit = null output,
@GROUPTYPE nvarchar(300) = null output,
@GROUPMEMBERCOUNT integer = null output,
@GIVESANONYMOUSLY bit = null output,
@PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100) = null output,
@BANKCONSTITUENCYTEXT nvarchar(100) = null output,
@PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
@PROSPECTMANAGERSTARTDATE datetime = null output,
@GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
@SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
@ISCOMMITTEEMEMBER bit = null output,
@COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
@ISDECEASED bit = null output,
@ISSPOUSEDECEASED bit = null output,
@CONSTITUENTINACTIVITYREASON nvarchar(63) = null output,
@DONOTMAIL bit = null output,
@DONOTEMAIL bit = null output,
@DONOTPHONE bit = null output,
@PHONEISCONFIDENTIAL bit = null output,
@ADDRESSISCONFIDENTIAL bit = null output,
@ADDRESSID uniqueidentifier = null output,
@PHONENUMBERID uniqueidentifier = null output,
@EMAILADDRESSID uniqueidentifier = null output,
@HOUSEHOLDTEXT nvarchar(154) = null output,
@HOUSEHOLDID uniqueidentifier = null output,
@LIFECYCLESTAGE nvarchar(1000) = null output,
@LIFECYCLESTAGEASOF datetime = null output,
@PLANNEDGIVERSTAGE nvarchar(15) = null output,
@PLANNEDGIVERSTAGEASOF datetime = null output,
@DONORSTATECODE tinyint = null output,
@DONORSTATE nvarchar(250) = null output,
@LASTREVENUEDATE datetime = null output,
@CURRENTAPPUSERID uniqueidentifier = null,
@MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
@MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
@SOCIALMEDIAACCOUNTS xml = null output
) as
set nocount on;
set @DATALOADED = 0;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @CURRENTDATEEARLIESTTIME datetime;
set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
declare @PROSPECTID uniqueidentifier;
select @PROSPECTID = PROSPECTID from dbo.PROSPECTPLAN where ID = @ID;
set @ISCOMMITTEEMEMBER = dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@PROSPECTID);
select
@DATALOADED = 1,
@ISPROSPECTPROFILE = 1,
@ISORGANIZATION = C.ISORGANIZATION,
@ADDRESS = dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
@DONOTMAIL = A.DONOTMAIL,
@ADDRESSISCONFIDENTIAL = A.ISCONFIDENTIAL,
@ADDRESSID = A.ID,
@PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PH.COUNTRYID, PH.NUMBER),
@PHONETYPE = dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PH.PHONETYPECODEID),
@DONOTPHONE = PH.DONOTCALL,
@PHONEISCONFIDENTIAL = PH.ISCONFIDENTIAL,
@PHONENUMBERID = PH.ID,
@EMAILADDRESS = E.EMAILADDRESS,
@DONOTEMAIL = E.DONOTEMAIL,
@EMAILADDRESSID = E.ID,
@WEBADDRESS = C.WEBADDRESS,
@PICTURE = C.PICTURETHUMBNAIL,
@STATUS = dbo.UFN_PROSPECT_GETPROSPECTPLANSTATUSES(C.ID),
@RELATEDCONSTITUENT = case when C.ISORGANIZATION = 1 then PARENTORG_NF.NAME else SPOUSE_NF.NAME end,
@RELATEDCONSTITUENTID = case when C.ISORGANIZATION = 1 then ORGANIZATIONDATA.PARENTCORPID else RELATIONSHIP.RECIPROCALCONSTITUENTID end,
@PLANNEDGIVERCONSTITUENCYTEXT =case
when dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634') --Planned giver
else null end,
@BOARDMEMBERCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') --Board member
else null end,
@RELATIONCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISRELATION(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('8B6ECCFD-6829-4FBC-B092-58CA083F9322') --Relation only
else null end,
@STAFFCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISSTAFF(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467') --Staff
else null end,
@DONORCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISDONOR(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F') --Donor
else null end,
@FUNDRAISERCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISFUNDRAISER(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923') --Fundraiser
else null end,
@PROSPECTCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISPROSPECT(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8') --Major giving prospect
else null end,
@VOLUNTEERCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISVOLUNTEER(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D') --Volunteer
else null end,
@SPONSORCONSTITUENCYTEXT = case dbo.UFN_CONSTITUENT_SPONSORTYPECODE(@PROSPECTID)
when 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4D746A03-A0AB-45F3-A30B-1AD4F304E622') --Sponsor
when 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F89E03BC-E724-4e5d-943B-72D4D1E1E916') --Sponsorship recipient
when 2 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('908E521C-B0A5-4832-B664-7D7B079D77C2') --Sponsorship donor
else null end,
-- @COMMUNITYMEMBERCONSTITUENCYTEXT = case
-- when dbo.UFN_CONSTITUENT_ISCOMMUNITYMEMBER(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('55FE8E7C-2B68-44C8-B35C-818AD1944C03') --Community member
-- else null end,
@MEMBERCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISMEMBER(@PROSPECTID,@CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2d11326e-8f3b-4322-9797-57c1aacfa5df') --Member
else null end,
@ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@PROSPECTID),
@REGISTRANTSTATUSTEXT = case
when dbo.UFN_CONSTITUENT_ISREGISTRANT(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C') --Event registrant
else null end,
@VENDORSTATUSTEXT = case
when dbo.UFN_CONSTITUENT_ISVENDOR(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('5435C96D-8617-46C3-9A62-5AFF08451A53') --Vendor
else null end,
@USERDEFINEDCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCIES_USERDEFINED(C.ID),
@BANKCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISBANK(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594') --Bank
else null end,
@PATRONCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISPATRON(@PROSPECTID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5') --Patron
else null end,
@ISINACTIVE = C.ISINACTIVE,
@DECEASEDDATE = DECEASEDCONSTITUENT.DECEASEDDATE,
--If PROSPECTMANAGERENDDATE is a past date do not fetch prospect manager data
@PROSPECTMANAGERFUNDRAISERID = case when P.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else P.PROSPECTMANAGERFUNDRAISERID end,
@PROSPECTMANAGER = case when P.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else PROSPECTMANAGER_NF.NAME end,
@PROSPECTSTATUS = dbo.UFN_PROSPECTSTATUSCODE_GETDESCRIPTION(P.PROSPECTSTATUSCODEID),
@ISGROUP = C.ISGROUP,
@GIVESANONYMOUSLY = C.GIVESANONYMOUSLY,
@PROSPECTMANAGERSTARTDATE = case when P.PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then null else P.PROSPECTMANAGERSTARTDATE end,
@GRANTORCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISGRANTOR(@PROSPECTID) = 1 THEN dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D9982C99-15C1-4C90-873E-56FD4B164056') --Grantor
else null end,
@COMMITTEEMEMBERCONSTITUENCYTEXT = case when @ISCOMMITTEEMEMBER = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('093A3D4F-2974-447F-AD92-870EB4A04593') /* Committee member */
else null end,
@ISDECEASED = case when DECEASEDCONSTITUENT.ID is not null then 1 else 0 end,
@ISSPOUSEDECEASED = case when SPOUSEDECEASEDCONSTITUENT.ID is not null then 1 else 0 end
from
dbo.CONSTITUENT C
left outer join dbo.PROSPECT P on P.ID=C.ID
left outer join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = C.ID
left outer join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = C.ID and RELATIONSHIP.ISSPOUSE = 1
left outer join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = C.ID
left outer join dbo.ADDRESS A on A.CONSTITUENTID=C.ID and A.ISPRIMARY = 1
left outer join dbo.PHONE PH on PH.CONSTITUENTID=C.ID and PH.ISPRIMARY = 1
left outer join dbo.EMAILADDRESS E on E.CONSTITUENTID=C.ID and E.ISPRIMARY = 1
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) PROSPECTMANAGER_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) SPOUSE_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(ORGANIZATIONDATA.PARENTCORPID) PARENTORG_NF
left outer join dbo.DECEASEDCONSTITUENT SPOUSEDECEASEDCONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSEDECEASEDCONSTITUENT.ID
where
C.ID = @PROSPECTID;
--SOCIAL MEDIA ACCOUNTS
select @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@PROSPECTID);
--Solicit Codes
select @SOLICITCODECOUNT=count(ID)
from CONSTITUENTSOLICITCODE
where CONSTITUENTID=@PROSPECTID;
if @ISINACTIVE = 1
select @CONSTITUENTINACTIVITYREASON = dbo.UFN_CONSTITUENTINACTIVITYREASONCODE_TRANSLATE(CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID)
from dbo.CONSTITUENTINACTIVEDETAIL
where ID = @PROSPECTID
-- group fields
select
@GROUPTYPE = coalesce( (select GT.NAME from dbo.GROUPTYPE GT where GT.ID = G.GROUPTYPEID), 'Household')
from
dbo.GROUPDATA G
where
G.ID = @PROSPECTID;
set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
select
@GROUPMEMBERCOUNT = count(GM.ID)
from
dbo.GROUPMEMBER GM
left join
dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.GROUPID = @PROSPECTID and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE))
or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO >= @CURRENTDATE))
-- Household information
select top(1)
@HOUSEHOLDID = CONSTITUENTGROUP.ID,
@HOUSEHOLDTEXT = CONSTITUENTGROUP.NAME
from
dbo.GROUPMEMBER
inner join
dbo.CONSTITUENT as CONSTITUENTGROUP on GROUPMEMBER.GROUPID = CONSTITUENTGROUP.ID
inner join
dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
where
GROUPMEMBER.MEMBERID = @PROSPECTID
and
dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1
and
GROUPDATA.GROUPTYPECODE = 0;
select
@LIFECYCLESTAGE = LIFECYCLETYPE.NAME,
@LIFECYCLESTAGEASOF = REVENUELIFECYCLE.STARTDATE,
@DONORSTATECODE = REVENUELIFECYCLE.DONORSTATECODE,
@LASTREVENUEDATE = REVENUELIFECYCLE.LASTREVENUEDATE,
@DONORSTATE = REVENUELIFECYCLE.DONORSTATE
from
dbo.REVENUELIFECYCLE
inner join dbo. LIFECYCLETYPE on LIFECYCLETYPE.ID = REVENUELIFECYCLE.LIFECYCLETYPEID
where
REVENUELIFECYCLE.CONSTITUENTID = @PROSPECTID and
LIFECYCLETYPE.IMPORTANCE <> 0;
select
@PLANNEDGIVERSTAGE = LIFECYCLETYPE.NAME,
@PLANNEDGIVERSTAGEASOF = REVENUELIFECYCLE.STARTDATE
from
dbo.REVENUELIFECYCLE
inner join dbo. LIFECYCLETYPE on LIFECYCLETYPE.ID = REVENUELIFECYCLE.LIFECYCLETYPEID
where
REVENUELIFECYCLE.CONSTITUENTID = @PROSPECTID and
LIFECYCLETYPE.NAME = 'Planned giver';
set @MAJORDONORCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISMAJORDONOR(@PROSPECTID) = 1
then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('1A9BFE80-604D-4B5B-8065-E751DDF6EF39') --Major donor
else null
end;
return 0;