USP_DATAFORMTEMPLATE_VIEW_RECURRINGGIFTACTIVITYDETAIL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RECURRINGGIFTACTIVITYDETAIL (
    @ID nvarchar(47),
    @DATALOADED bit = 0 output,
    @INSTALLMENTACTIVITY xml = null output,
    @AMENDMENTTYPECODE tinyint = null output,
    @PREVIOUSTRANSACTIONAMOUNT money = null output,
    @TRANSACTIONAMOUNT money = null output,
    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
    @PREVIOUSFREQUENCYCODE tinyint = null output,
    @FREQUENCYCODE tinyint = null output,
    @DESIGNATIONS xml = null output,
    @SOURCECODE nvarchar(50) = null output,
    @FINDERNUMBER nvarchar(19) = null output,
    @APPEAL nvarchar(100) = null output,
    @MAILING nvarchar(100) = null output,
    @CHANNEL nvarchar(100) = null output,
    @PREVIOUSPAYMENTMETHODCODE tinyint = null output,
    @PAYMENTMETHODCODE tinyint = null output,
    @PREVIOUSCREDITTYPE nvarchar(100) = null output,
    @CREDITTYPE nvarchar(100) = null output,
    @PREVIOUSCREDITCARDPARTIALNUMBER nvarchar(4) = null output,
    @CREDITCARDPARTIALNUMBER nvarchar(4) = null output,
    @PREVIOUSEXPIRESON dbo.UDT_FUZZYDATE = null output,
    @EXPIRESON dbo.UDT_FUZZYDATE = null output,
    @PREVIOUSFINANCIALINSTITUTION nvarchar(100) = null output,
    @FINANCIALINSTITUTION nvarchar(100) = null output,
    @PREVIOUSCONSTITUENTACCOUNTPARTIALNUMBER nvarchar(4) = null output,
    @CONSTITUENTACCOUNTPARTIALNUMBER nvarchar(4) = null output,
    @PREVIOUSREFERENCEDATE dbo.UDT_FUZZYDATE = null output,
    @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
    @PREVIOUSREFERENCENUMBER nvarchar(20) = null output,
    @REFERENCENUMBER nvarchar(20) = null output,
    @OLDCONSTITUENT nvarchar(154) = null output,
    @NEWCONSTITUENT nvarchar(154) = null output,
    @OLDCONSTITUENTLOOKUPID nvarchar(36) = null output,
    @NEWCONSTITUENTLOOKUPID nvarchar(36) = null output,
    @NEXTTRANSACTIONDATE date = null output,
    @PREVIOUSNEXTTRANSACTIONDATE date = null output,
    @STATUSCODE tinyint = null output,
    @PREVIOUSSTATUSCODE tinyint = null output,
    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
    @PREVIOUSOTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
    @PREVIOUSCARDHOLDERNAME nvarchar(255) = null output,
    @CARDHOLDERNAME nvarchar(255) = null output,
    @REVENUEDEVELOPMENTFUNCTION nvarchar(200) = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @DDISOURCECODEID uniqueidentifier = null output,
    @PREVIOUSDDISOURCECODEID uniqueidentifier = null output,
    @DDISOURCEDATE date = null output,
    @PREVIOUSDDISOURCEDATE date = null output,
    @SENDPMINSTRUCTION bit = null output,
    @PREVIOUSSENDPMINSTRUCTION bit = null output,
    @PMINSTRUCTIONTOSENDCODE tinyint = null output,
    @PREVIOUSPMINSTRUCTIONTOSENDCODE tinyint = null output,
    @PMINSTRUCTIONDATE_NEW date = null output,
    @PREVIOUSPMINSTRUCTIONDATE_NEW date = null output,
    @PMINSTRUCTIONDATE_CANCEL date = null output,
    @PREVIOUSPMINSTRUCTIONDATE_CANCEL date = null output,
    @PMINSTRUCTIONDATE_SETUP date = null output,
    @PREVIOUSPMINSTRUCTIONDATE_SETUP date = null output,
    @PMADVANCENOTICESENTDATE date = null output,
    @PREVIOUSPMADVANCENOTICESENTDATE date = null output,
    @SEPAMANDATEID uniqueidentifier = null output,
    @PREVIOUSSEPAMANDATEID uniqueidentifier = null output,
    @STANDINGORDERSETUP bit = null output,
    @PREVIOUSSTANDINGORDERSETUP bit = null output,
    @STANDINGORDERSETUPDATE date = null output,
    @PREVIOUSSTANDINGORDERSETUPDATE date = null output,
    @USESYSTEMGENERATEDREFERENCENUMBER bit = null output,
    @PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER bit = null output,
    @CONSTITUENTACCOUNTID uniqueidentifier = null output,
    @PREVIOUSCONSTITUENTACCOUNTID uniqueidentifier = null output
    )
as
set nocount on;
set @DATALOADED = 0;

declare @RECORDTYPE nvarchar(1) = substring(@ID,1,1);
declare @RECORDID uniqueidentifier = cast(substring(@ID,3,36) as uniqueidentifier);

--check on permission to view payment details
declare @USER_GRANTED_PAYMENTINFORMATION_VIEW bit
if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
   set @USER_GRANTED_PAYMENTINFORMATION_VIEW = 1;
else
   set @USER_GRANTED_PAYMENTINFORMATION_VIEW = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'bafca6f5-4cdb-4173-aece-113713d38bfb');


if @RECORDTYPE in('I','M')
begin
  if @RECORDTYPE = 'I'
  begin
    set @INSTALLMENTACTIVITY = dbo.UFN_RECURRINGGIFTINSTALLMENTACTIVITY_TOITEMLISTXML(@RECORDID);
    if @INSTALLMENTACTIVITY is not null
        set @DATALOADED = 1;

    --check for zero dollar installments
    if @DATALOADED = 0
    begin
        select @DATALOADED = 1
        from dbo.RECURRINGGIFTINSTALLMENT
        where ID = cast(substring(@ID,3,36) as uniqueidentifier)
        and AMOUNT = 0;

        set @INSTALLMENTACTIVITY = '<INSTALLMENTACTIVITY><ITEM><ACTIVITY>ZERODOLLAR</ACTIVITY></ITEM></INSTALLMENTACTIVITY>' 
    end
  end
  else
  begin
    set @INSTALLMENTACTIVITY =
      (select convert(date,substring(@ID,40,8),112) DATE,
              'Expected' ACTIVITY,
              0 AMOUNT,
              TRANSACTIONAMOUNT BALANCE,
              null DETAILS,
              TRANSACTIONCURRENCYID CURRENCYID,
              null DATEADDED,
              null ID,
              0 TYPECODE
       from dbo.FINANCIALTRANSACTION
       where ID = @RECORDID
       for xml path('ITEM'),type,elements,root('INSTALLMENTACTIVITY'),BINARY BASE64);

    if @INSTALLMENTACTIVITY is not null
        set @DATALOADED = 1;
  end
end
else
begin
        exec dbo.USP_GET_KEY_ACCESS;

        select @DATALOADED = 1,
            @AMENDMENTTYPECODE = a.AMENDMENTTYPECODE,
            @PREVIOUSTRANSACTIONAMOUNT = a.PREVIOUSTRANSACTIONAMOUNT,
            @TRANSACTIONAMOUNT = a.TRANSACTIONAMOUNT,
            @TRANSACTIONCURRENCYID = f.TRANSACTIONCURRENCYID,
            @PREVIOUSFREQUENCYCODE = a.PREVIOUSFREQUENCYCODE,
            @FREQUENCYCODE = a.FREQUENCYCODE,
            @DESIGNATIONS = (
                select T.c.value('(ID)[1]', 'uniqueidentifier') as [ID],
                    dbo.UFN_DESIGNATION_BUILDNAME(T.c.value('(ID)[1]', 'uniqueidentifier')) as [DESIGNATION],
                    T.c.value('(OLDAMOUNT)[1]', 'money') as [OLDAMOUNT],
                    T.c.value('(NEWAMOUNT)[1]', 'money') as [NEWAMOUNT],
                    T.c.value('(TRANSACTIONCURRENCYID)[1]', 'uniqueidentifier') as [TRANSACTIONCURRENCYID]
                from a.DESIGNATIONS.nodes('/DESIGNATIONS/ITEM') as T(c)
                for xml raw('ITEM'),
                    type,
                    elements,
                    root('DESIGNATIONS'),
                    binary BASE64
                ),
            @SOURCECODE = a.SOURCECODE,
            @FINDERNUMBER = nullif(a.FINDERNUMBER, 0),
            @APPEAL = APPEAL.name,
            @MAILING = MKTSEGMENTATION.name,
            @CHANNEL = CHANNELCODE.DESCRIPTION,
            @PREVIOUSPAYMENTMETHODCODE = case 
                                         when @USER_GRANTED_PAYMENTINFORMATION_VIEW = 0 then 99
                                         else a.PREVIOUSPAYMENTMETHODCODE
                                         end,
            @PAYMENTMETHODCODE = case 
                                 when @USER_GRANTED_PAYMENTINFORMATION_VIEW = 0 then 99
                                 else a.PAYMENTMETHODCODE
                                 end,
            @PREVIOUSCREDITTYPE = pct.DESCRIPTION,
            @CREDITTYPE = ct.DESCRIPTION,
            @PREVIOUSCREDITCARDPARTIALNUMBER = a.PREVIOUSCREDITCARDPARTIALNUMBER,
            @CREDITCARDPARTIALNUMBER = a.CREDITCARDPARTIALNUMBER,
            @PREVIOUSEXPIRESON = a.PREVIOUSEXPIRESON,
            @EXPIRESON = a.EXPIRESON,
            @PREVIOUSCARDHOLDERNAME = a.PREVIOUSCARDHOLDERNAME,
            @CARDHOLDERNAME = a.CARDHOLDERNAME,
            @PREVIOUSFINANCIALINSTITUTION = pfi.DESCRIPTION,
            @FINANCIALINSTITUTION = fi.DESCRIPTION,
            @PREVIOUSCONSTITUENTACCOUNTPARTIALNUMBER = right(convert(nvarchar(50), DecryptByKey(pca.ACCOUNTNUMBER)), 4),
            @CONSTITUENTACCOUNTPARTIALNUMBER = right(convert(nvarchar(50), DecryptByKey(ca.ACCOUNTNUMBER)), 4),
            @PREVIOUSREFERENCEDATE = a.PREVIOUSREFERENCEDATE,
            @REFERENCEDATE = a.REFERENCEDATE,
            @PREVIOUSREFERENCENUMBER = a.PREVIOUSREFERENCENUMBER,
            @REFERENCENUMBER = a.REFERENCENUMBER,
            @OLDCONSTITUENT = isnull(c2.NAME, 'Former gift owner'),
            @NEWCONSTITUENT = isnull(c1.NAME, 'Former gift owner'),
            @OLDCONSTITUENTLOOKUPID = c2.LOOKUPID,
            @NEWCONSTITUENTLOOKUPID = c1.LOOKUPID,
            @PREVIOUSNEXTTRANSACTIONDATE = a.PREVIOUSNEXTTRANSACTIONDATE,
            @NEXTTRANSACTIONDATE = a.NEXTTRANSACTIONDATE,
            @STATUSCODE = a.STATUSCODE,
            @PREVIOUSSTATUSCODE = a.PREVIOUSSTATUSCODE,
            @OTHERPAYMENTMETHODCODEID = a.OTHERPAYMENTMETHODCODEID,
            @PREVIOUSOTHERPAYMENTMETHODCODEID = a.PREVIOUSOTHERPAYMENTMETHODCODEID,
            @REVENUEDEVELOPMENTFUNCTION = rdfc.DESCRIPTION,
            @DDISOURCECODEID = a.DDISOURCECODEID,
            @PREVIOUSDDISOURCECODEID = a.PREVIOUSDDISOURCECODEID,
            @DDISOURCEDATE = a.DDISOURCEDATE,
            @PREVIOUSDDISOURCEDATE = a.PREVIOUSDDISOURCEDATE,
            @SENDPMINSTRUCTION = a.SENDPMINSTRUCTION,
            @PREVIOUSSENDPMINSTRUCTION = a.PREVIOUSSENDPMINSTRUCTION,
            @PMINSTRUCTIONTOSENDCODE = a.PMINSTRUCTIONTOSENDCODE,
            @PREVIOUSPMINSTRUCTIONTOSENDCODE = a.PREVIOUSPMINSTRUCTIONTOSENDCODE,
            @PMINSTRUCTIONDATE_NEW = a.PMINSTRUCTIONDATE_NEW,
            @PREVIOUSPMINSTRUCTIONDATE_NEW = a.PREVIOUSPMINSTRUCTIONDATE_NEW,
            @PMINSTRUCTIONDATE_CANCEL = a.PMINSTRUCTIONDATE_CANCEL,
            @PREVIOUSPMINSTRUCTIONDATE_CANCEL = a.PREVIOUSPMINSTRUCTIONDATE_CANCEL,
            @PMINSTRUCTIONDATE_SETUP = a.PMINSTRUCTIONDATE_SETUP,
            @PREVIOUSPMINSTRUCTIONDATE_SETUP = a.PREVIOUSPMINSTRUCTIONDATE_SETUP,
            @PMADVANCENOTICESENTDATE = a.PMADVANCENOTICESENTDATE,
            @PREVIOUSPMADVANCENOTICESENTDATE = a.PREVIOUSPMADVANCENOTICESENTDATE,
            @SEPAMANDATEID = a.SEPAMANDATEID,
            @PREVIOUSSEPAMANDATEID = a.PREVIOUSSEPAMANDATEID,
            @STANDINGORDERSETUP = a.STANDINGORDERSETUP,
            @PREVIOUSSTANDINGORDERSETUP = a.PREVIOUSSTANDINGORDERSETUP,
            @STANDINGORDERSETUPDATE = a.STANDINGORDERSETUPDATE,
            @PREVIOUSSTANDINGORDERSETUPDATE = a.PREVIOUSSTANDINGORDERSETUPDATE,
            @USESYSTEMGENERATEDREFERENCENUMBER = a.USESYSTEMGENERATEDREFERENCENUMBER,
            @PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER = a.PREVIOUSUSESYSTEMGENERATEDREFERENCENUMBER,
            @CONSTITUENTACCOUNTID = a.CONSTITUENTACCOUNTID,
            @PREVIOUSCONSTITUENTACCOUNTID = a.PREVIOUSCONSTITUENTACCOUNTID
        from dbo.RECURRINGGIFTAMENDMENT a
        left join dbo.APPEAL on APPEAL.ID = a.APPEALID
        left join dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = a.MAILINGID
        left join dbo.CHANNELCODE on CHANNELCODE.ID = a.CHANNELCODEID
        left join dbo.CREDITTYPECODE pct on pct.ID = a.PREVIOUSCREDITTYPECODEID
        left join dbo.CREDITTYPECODE ct on ct.ID = a.CREDITTYPECODEID
        left join dbo.CONSTITUENTACCOUNT pca on pca.ID = a.PREVIOUSCONSTITUENTACCOUNTID
        left join dbo.FINANCIALINSTITUTION pfi on pfi.ID = pca.FINANCIALINSTITUTIONID
        left join dbo.CONSTITUENTACCOUNT ca on ca.ID = a.CONSTITUENTACCOUNTID
        left join dbo.FINANCIALINSTITUTION fi on fi.ID = ca.FINANCIALINSTITUTIONID
        left join dbo.CONSTITUENT c1 on c1.ID = a.CONSTITUENTID
        left join dbo.CONSTITUENT c2 on c2.ID = a.PREVIOUSCONSTITUENTID
        left join dbo.REVENUEDEVELOPMENTFUNCTION rdf on rdf.ID = a.REVENUEDEVELOPMENTFUNCTIONID
        left join dbo.REVENUEDEVELOPMENTFUNCTIONCODE rdfc on rdfc.ID = rdf.REVENUEDEVELOPMENTFUNCTIONCODEID
        inner join dbo.FINANCIALTRANSACTION f on f.ID = a.FINANCIALTRANSACTIONID
        where a.ID = @RECORDID;

        --and a.AMENDMENTTYPECODE in(0,2,3);
        close symmetric key sym_BBInfinity;

end
return 0;