USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPPROGRAMDUESBASEDRULES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPPROGRAMDUESBASEDRULES
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @DUESTREATEDASCONTRIBUTION bit = null output,
  @DUESONEPAYMENTEACHTERM bit = null output,
  @DUESMULTIPLEPAYMENTSEACHTERM bit = null output,
  @DESIGNATIONSFORCONTRIBUTEDPORTION xml = null output,
  @MEMBERSHIPLEVELDESIGNATIONS xml = null output,
  @PROGRAMTYPECODE tinyint = null output,
  @DISCOUNTSFORPROGRAM xml = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @DEDUCTIBILITYCODE tinyint = null output,
  @DUESINSTALLMENTPOSTSTATUSCODE tinyint = null output
)
as

  set nocount on;

  -- be sure to set these, in case the select returns no rows

  set @DATALOADED = 0;
  set @TSLONG = 0;
  set @BASECURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

  -- populate the output parameters, which correspond to fields on the form.  Note that

  -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

  -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency

  -- can be considered.


  select
    @DATALOADED = 1,
    @TSLONG = TSLONG,
    @DUESTREATEDASCONTRIBUTION = DUESTREATEDASCONTRIBUTION,
    @DUESONEPAYMENTEACHTERM = ONEPAYMENTEACHTERM,
    @DUESMULTIPLEPAYMENTSEACHTERM = MULTIPLEPAYMENTSEACHTERM,
    @PROGRAMTYPECODE = PROGRAMTYPECODE,
    @DEDUCTIBILITYCODE = DEDUCTIBILITYCODE,
    @DUESINSTALLMENTPOSTSTATUSCODE = INSTALLMENTPOSTSTATUSCODE
  from dbo.MEMBERSHIPPROGRAM
  where ID = @ID;

  set @DESIGNATIONSFORCONTRIBUTEDPORTION = (
    select
      ID,
      DESIGNATIONID,
      [PERCENT]
    from dbo.MEMBERSHIPPROGRAMDESIGNATION
    where MEMBERSHIPPROGRAMID = @ID
    for xml raw ('ITEM'), type, elements, root('DESIGNATIONSFORCONTRIBUTEDPORTION'), BINARY BASE64);

  set @MEMBERSHIPLEVELDESIGNATIONS = (
    select
      MEMBERSHIPLEVELDESIGNATION.ID,
      MEMBERSHIPLEVEL.ID as LEVELID,
      MEMBERSHIPLEVELDESIGNATION.DESIGNATIONID,
      MEMBERSHIPLEVELDESIGNATION.[PERCENT],
      0 as USERMODIFIED
    from dbo.MEMBERSHIPLEVEL
      left outer join dbo.MEMBERSHIPLEVELDESIGNATION on MEMBERSHIPLEVEL.ID = MEMBERSHIPLEVELDESIGNATION.MEMBERSHIPLEVELID
    where
      MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID
    for xml raw ('ITEM'), type, elements, root('MEMBERSHIPLEVELDESIGNATIONS'), BINARY BASE64);

  set @DISCOUNTSFORPROGRAM = (
    select
      MEMBERSHIPPROMO.ID,
      MEMBERSHIPPROMOAVAILABILITY.ID as MEMBERSHIPPROMOAVAILABILITYID,
      case when MEMBERSHIPPROMOAVAILABILITY.ID is not NULL then 1 else 0 end as APPLY,
      MEMBERSHIPPROMO.NAME,
      MEMBERSHIPPROMO.DISCOUNTCALCULATIONTYPECODE,
      MEMBERSHIPPROMO.EXTENSIONCALCULATIONTYPECODE,
      MEMBERSHIPPROMO.PROMOTIONTYPECODE,
      MEMBERSHIPPROMO.AMOUNT,
      MEMBERSHIPPROMO.[PERCENT],
      MEMBERSHIPPROMO.EXTENSIONVALUE,
      MEMBERSHIPPROMO.BASECURRENCYID,
      MEMBERSHIPPROMO.FORMATTEDVALUE
    from dbo.MEMBERSHIPPROMO
      left outer join dbo.MEMBERSHIPPROMOAVAILABILITY on MEMBERSHIPPROMO.ID = MEMBERSHIPPROMOAVAILABILITY.MEMBERSHIPPROMOID and MEMBERSHIPPROMOAVAILABILITY.MEMBERSHIPPROGRAMID = @ID
    where
      (
        @PROGRAMTYPECODE = 0 or
        MEMBERSHIPPROMO.PROMOTIONTYPECODE = 0
      )
      and MEMBERSHIPPROMO.APPLICATIONTYPECODE = 0
      and (MEMBERSHIPPROMO.ISACTIVE = 1 or MEMBERSHIPPROMOAVAILABILITY.ID is not null)
    for xml raw('ITEM'),type,elements,root('DISCOUNTSFORPROGRAM'),BINARY BASE64
  );

  return 0;