USP_DATALIST_SALESORDERONLINESHIPPINGADDRESS
Gets possible shipping addresses for a constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @SALESORDERID | uniqueidentifier | IN | Sales Order ID |
| @GETALL | bit | IN | Get All |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_SALESORDERONLINESHIPPINGADDRESS
(
@CONSTITUENTID uniqueidentifier,
@SALESORDERID uniqueidentifier,
@GETALL bit = 0
)
as
set nocount on;
declare @ADDRESSID uniqueidentifier
if @GETALL = 1
begin
select top 1 @ADDRESSID = [ID]
from [dbo].[ADDRESS]
where [CONSTITUENTID] = @CONSTITUENTID
select
a.[ID],
c.[NAME],
c.[FIRSTNAME],
c.[KEYNAME],
a.[ADDRESSBLOCK],
a.[CITY],
a.[STATE],
a.[POSTCODE],
a.[FULLADDRESS],
a.[COUNTRY],
'',
''
from [dbo].[UFN_CONSTITUENT_GETADDRESSES](@ADDRESSID) a
inner join [dbo].[CONSTITUENT] c on c.ID = @CONSTITUENTID
where a.ID NOT IN (
select [ID]
from dbo.ADDRESS
where CONSTITUENTID = @CONSTITUENTID and ISCONFIDENTIAL = 1
)
end
else
begin
declare @PHONEID uniqueidentifier
declare @EMAILID uniqueidentifier
select
@ADDRESSID = [ADDRESSID],
@PHONEID = [PHONEID],
@EMAILID = [EMAILADDRESSID]
from [dbo].[SALESORDER]
where [ID] = @SALESORDERID
select
a.[ID],
c.[NAME],
c.[FIRSTNAME],
c.[KEYNAME],
a.[ADDRESSBLOCK],
a.[CITY],
a.[STATE],
a.[POSTCODE],
a.[FULLADDRESS],
a.[COUNTRY],
p.[NUMBER],
e.[EMAILADDRESS]
from [dbo].[UFN_CONSTITUENT_GETADDRESSES](@ADDRESSID) a
inner join [dbo].[CONSTITUENT] c on c.ID = @CONSTITUENTID
left join [dbo].[PHONE] p on p.ID = @PHONEID
left join [dbo].[EMAILADDRESS] e on e.ID = @EMAILID
where a.ID NOT IN (
select [ID]
from dbo.ADDRESS
where CONSTITUENTID = @CONSTITUENTID and ISCONFIDENTIAL = 1
)
end