USP_DATAFORMTEMPLATE_EDITLOAD_MKTVENDORSERVICE

The load procedure used by the edit dataform template "Marketing Vendor Service Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SERVICETYPES	xml	INOUT	Available service types
@SERVICES	xml	INOUT	Available services
@VENDORSERVICETYPES	xml	INOUT	Service types provided
@VENDORSERVICES	xml	INOUT	Services provided
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTVENDORSERVICE]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SERVICETYPES xml = null output,
  @SERVICES xml = null output,
  @VENDORSERVICETYPES xml = null output,
  @VENDORSERVICES xml = null output,
  @TSLONG bigint = 0 output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @DATALOADED = 1,
    @TSLONG = [TSLONG]
  from dbo.[VENDOR]
  where [ID] = @ID;

  declare @SERVICETYPESTABLE table ([SERVICETYPECODE] tinyint not null);

  -- this is done this way so as to be extendable in the future, when vendors are 

  -- shared with other product lines

  -- logic like "if the product is BBDM, allow lists and creatives; if the product

  -- is PE, allow tickets, etc." would go here (and appropriate values would be

  -- added to the enumeration to represent those service types)


  insert into @SERVICETYPESTABLE values (1); -- creatives

  insert into @SERVICETYPESTABLE values (2); -- lists

  insert into @SERVICETYPESTABLE values (3); -- public media


  set @SERVICETYPES = (
    select 
      [SERVICETYPECODE]
    from @SERVICETYPESTABLE
    for xml raw('ITEM'), type, elements, root('SERVICETYPES'), binary base64);

  set @SERVICES = (
    select 
      [MKTSERVICE].[ID],
      [MKTSERVICECATEGORYCODE].[DESCRIPTION] as [CATEGORY],
      [MKTSERVICE].[NAME],
      [MKTSERVICE].[SERVICECATEGORYCODEID]
    from dbo.[MKTSERVICE] 
    inner join dbo.[MKTSERVICECATEGORYCODE] 
    on [MKTSERVICECATEGORYCODE].[ID] = [MKTSERVICE].[SERVICECATEGORYCODEID]
    order by [MKTSERVICECATEGORYCODE].[DESCRIPTION], [MKTSERVICE].[SERVICECATEGORYCODEID], [MKTSERVICE].[NAME]
    for xml raw('ITEM'), type, elements, root('SERVICES'), binary base64);

  set @VENDORSERVICETYPES = (
    select
      [ID],
      [SERVICETYPECODE],
      [SERVICETYPEEXISTS]
    from dbo.[UFN_MKTVENDOR_GETVENDORSERVICETYPES](@ID)
    for xml raw('ITEM'), type, elements, root('VENDORSERVICETYPES'), binary base64);

  set @VENDORSERVICES = (
    select
      [ID],
      [SERVICEID],
      [NAME],
      [CATEGORY],
      [SERVICECATEGORYCODEID]
    from dbo.[UFN_MKTVENDOR_GETVENDORSERVICES](@ID)
    order by [CATEGORY], [SERVICECATEGORYCODEID], [NAME]
    for xml raw('ITEM'), type, elements, root('VENDORSERVICES'), binary base64);

  return 0;