USP_DATAFORMTEMPLATE_EDITLOAD_ORGANIZATIONCONSTITUENT_GIFTDATAENTRY

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@ORGANIZATIONNAME	nvarchar(100)	INOUT
@INDUSTRYCODEID	uniqueidentifier	INOUT
@NUMEMPLOYEES	int	INOUT
@NUMSUBSIDIARIES	int	INOUT
@PARENTCORPID	uniqueidentifier	INOUT
@PICTURE	varbinary	INOUT
@PICTURETHUMBNAIL	varbinary	INOUT
@PICTURECHANGED	bit	INOUT
@WEBADDRESS	UDT_WEBADDRESS	INOUT
@TSLONG	bigint	INOUT
@ISPRIMARY	bit	INOUT
@PRIMARYADDRESSID	uniqueidentifier	INOUT
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	INOUT
@ADDRESS_COUNTRYID	uniqueidentifier	INOUT
@ADDRESS_ADDRESSBLOCK	nvarchar(max)	INOUT
@ADDRESS_CITY	nvarchar(50)	INOUT
@ADDRESS_STATEID	uniqueidentifier	INOUT
@ADDRESS_POSTCODE	nvarchar(12)	INOUT
@ADDRESS_DONOTMAIL	bit	INOUT
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@PHONE_PHONETYPECODEID	uniqueidentifier	INOUT
@PHONE_NUMBER	nvarchar(100)	INOUT
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@PRIMARYEMAILADDRESSID	uniqueidentifier	INOUT
@PRIMARYPHONEID	uniqueidentifier	INOUT

Definition

Copy


create procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_ORGANIZATIONCONSTITUENT_GIFTDATAENTRY
(
    @ID uniqueidentifier,
        @DATALOADED bit = 0 output,
        @ORGANIZATIONNAME nvarchar(100) = null output, 
        @INDUSTRYCODEID uniqueidentifier = null output,        
        @NUMEMPLOYEES int = null output,
        @NUMSUBSIDIARIES int = null output,
        @PARENTCORPID uniqueidentifier = null output,
        @PICTURE varbinary(max) = null output,
        @PICTURETHUMBNAIL varbinary(max) = null output,
        @PICTURECHANGED bit = null output,
        @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
        @TSLONG bigint = 0 output,
        @ISPRIMARY bit = null output,
    @PRIMARYADDRESSID uniqueidentifier = null output,
    @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null output,
    @ADDRESS_COUNTRYID uniqueidentifier = null output,
    @ADDRESS_ADDRESSBLOCK nvarchar(max) = null output,  
    @ADDRESS_CITY nvarchar(50) = null output,
    @ADDRESS_STATEID uniqueidentifier = null output,
    @ADDRESS_POSTCODE nvarchar(12) = null output,  
    @ADDRESS_DONOTMAIL bit = null output,
    @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null output,
    @PHONE_PHONETYPECODEID uniqueidentifier = null output,  
    @PHONE_NUMBER nvarchar(100) = null output,
    @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @PRIMARYEMAILADDRESSID uniqueidentifier = null output,
    @PRIMARYPHONEID uniqueidentifier = null output
)
as

    set nocount on;

    -- moved original code in the USP bellow, which is shared with other areas other then organization edit (duplicate search resolution screen)
    /*
  exec dbo.USP_ORGANIZATION_EDITLOAD @ID, @DATALOADED output, @ORGANIZATIONNAME output, @INDUSTRYCODEID output, @NUMEMPLOYEES output, @NUMSUBSIDIARIES output, 
                                           @PARENTCORPID output, @PICTURE output, @PICTURETHUMBNAIL output, @PICTURECHANGED output, @WEBADDRESS output, @TSLONG output, @ISPRIMARY output;    
   */
  select   
     @DATALOADED = 1,  
     @ID = c.ID,  
     @ORGANIZATIONNAME = c.KEYNAMEPREFIX + case c.KEYNAMEPREFIX when '' then '' else '\' end + c.KEYNAME,  
     @INDUSTRYCODEID = oga.INDUSTRYCODEID,  
     @NUMEMPLOYEES = coalesce(oga.NUMEMPLOYEES,0),  
     @NUMSUBSIDIARIES = coalesce(oga.NUMSUBSIDIARIES,0),  
     @PARENTCORPID = oga.PARENTCORPID,  
     @PICTURETHUMBNAIL = c.PICTURETHUMBNAIL,  
     @WEBADDRESS = c.WEBADDRESS,  
     @TSLONG = c.TSLONG,  
     @ISPRIMARY = oga.ISPRIMARY,
     @PRIMARYADDRESSID = a.ID,
     @ADDRESS_ADDRESSTYPECODEID = a.ADDRESSTYPECODEID,
     @ADDRESS_COUNTRYID= a.COUNTRYID,
     @ADDRESS_ADDRESSBLOCK= a.ADDRESSBLOCK,
     @ADDRESS_CITY= a.CITY,
     @ADDRESS_STATEID= a.STATEID,
     @ADDRESS_POSTCODE= a.POSTCODE,
     @ADDRESS_DONOTMAIL= a.DONOTMAIL,
     @ADDRESS_DONOTMAILREASONCODEID= a.DONOTMAILREASONCODEID,
     @PRIMARYPHONEID= p.ID,
     @PHONE_PHONETYPECODEID= p.PHONETYPECODEID,
     @PHONE_NUMBER= p.NUMBER,
     @PRIMARYEMAILADDRESSID= ea.ID,
     @EMAILADDRESS_EMAILADDRESSTYPECODEID= ea.EMAILADDRESSTYPECODEID,
     @EMAILADDRESS_EMAILADDRESS= ea.EMAILADDRESS
   from   
      dbo.CONSTITUENT c 
      left join dbo.ORGANIZATIONDATA oga on oga.ID = c.ID  
      left join dbo.Address a on a.constituentID = c.ID and a.ISPRIMARY =1
      left join dbo.PHONE p on p.constituentID = c.ID and p.ISPRIMARY =1
      left join dbo.EMAILADDRESS ea on ea.constituentID = c.ID and ea.ISPRIMARY =1
   where   
      c.ISORGANIZATION = 1 and c.ID = @ID;  

   return 0;