USP_DATAFORMTEMPLATE_EDITLOAD_GRANTAWARD_3

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_GRANTAWARD_3 (
  @ID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@TSLONG bigint = 0 output
  ,@GRANTNAME nvarchar(150) = null output
  ,@GRANTORNAME nvarchar(154) = null output
  ,@DATE datetime = null output
  ,@AMOUNT money = null output
  ,@POSTSTATUSCODE tinyint = null output
  ,@POSTDATE datetime = null output
  ,@SPLITS xml = null output
  ,@FREQUENCYCODE tinyint = null output
  ,@NUMBEROFINSTALLMENTS int = null output
  ,@NEXTTRANSACTIONDATE datetime = null output
  ,@AMOUNTPAID money = null output
  ,@INSTALLMENTS xml = null output
  ,@DONOTACKNOWLEDGE bit = null output
  ,@MAINSINGLEDESIGNATIONID uniqueidentifier = null output
  ,@LASTPAYMENTSEQUENCE int = null output
  ,@CATEGORYCODEID uniqueidentifier = null output
  ,@MAXSOLICITORSPLITAMOUNTS xml = null output
  ,@ISREIMBURSABLE bit = null output
  ,@GRANTAWARDON tinyint = null output
  ,@OPPORTUNITYID uniqueidentifier = null output
  ,@PROSPECTNAME nvarchar(154) = null output
  ,@CONSTITUENTID uniqueidentifier = null output
  ,@BASECURRENCYID uniqueidentifier = null output
  ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
  ,@BASEEXCHANGERATEID uniqueidentifier = null output
  ,@EXCHANGERATE decimal(20, 8) = null output
  ,@HADSPOTRATE bit = null output
  ,@RATECHANGED bit = null output
  ,@BASEDECIMALDIGITS tinyint = null output
  ,@BASEROUNDINGTYPECODE tinyint = null output
  ,@TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output
  ,@HASPOSTEDPAYMENTS bit = null output
  ,@HASPOSTEDWRITEOFFS bit = null output
  ,@HASPAYMENTS bit = null output
  ,@ALLOWGLDISTRIBUTIONS bit = null output
  ,@USERGRANTEDSPOTRATE bit = null output
  ,@ADJPAYMENT_DATE datetime = null output
  ,@ADJPAYMENT_POSTDATE datetime = null output
  ,@ADJPAYMENT_REASONCODEID uniqueidentifier = null output
  ,@ADJPAYMENT_DETAILS nvarchar(255) = null output
  ,@HASRECOGNITIONCREDIT bit = null output
  ,@UPDATERECOGNITIONOPTION tinyint = null output
  )
as
set nocount on;
set @DATALOADED = 0
set @TSLONG = 0

-- Check GL business rule for this account system and set to 'Do not post' if needed.

-- ****

declare @PDACCOUNTSYSTEMID uniqueidentifier;

select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
from dbo.PDACCOUNTSYSTEMFORREVENUE
where ID = @ID;

set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

-- ****                

declare @LASTINSTALLMENT uniqueidentifier;

select @DATALOADED = DATALOADED
  ,@GRANTNAME = GRANTNAME
  ,@GRANTORNAME = GRANTORNAME
  ,@DATE = [DATE]
  ,@AMOUNT = AMOUNT
  ,@POSTSTATUSCODE = POSTSTATUSCODE
  ,@POSTDATE = POSTDATE
  ,@FREQUENCYCODE = FREQUENCYCODE
  ,@NUMBEROFINSTALLMENTS = NUMBEROFINSTALLMENTS
  ,@NEXTTRANSACTIONDATE = NEXTTRANSACTIONDATE
  ,@TSLONG = TSLONG
  ,@DONOTACKNOWLEDGE = DONOTACKNOWLEDGE
  ,@CATEGORYCODEID = CATEGORYCODEID
  ,@MAXSOLICITORSPLITAMOUNTS = MAXSOLICITORSPLITAMOUNTS
  ,@ISREIMBURSABLE = ISREIMBURSABLE
  ,@GRANTAWARDON = GRANTAWARDON
  ,@OPPORTUNITYID = OPPORTUNITYID
  ,@PROSPECTNAME = PROSPECTNAME
  ,@CONSTITUENTID = CONSTITUENTID
  ,@BASECURRENCYID = BASECURRENCYID
  ,@TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
  ,@BASEEXCHANGERATEID = BASEEXCHANGERATEID
  ,@EXCHANGERATE = EXCHANGERATE
  ,@HADSPOTRATE = HADSPOTRATE
  ,@RATECHANGED = 0
  ,@BASEDECIMALDIGITS = BASEDECIMALDIGITS
  ,@BASEROUNDINGTYPECODE = BASEROUNDINGTYPECODE
  ,@TRANSACTIONCURRENCYDESCRIPTION = TRANSACTIONCURRENCYDESCRIPTION
from (
  select 1 as DATALOADED
    ,GRANTS.TITLE as GRANTNAME
    ,dbo.UFN_CONSTITUENT_BUILDNAME(GRANTS.GRANTORID) as GRANTORNAME
    ,REVENUE.[DATE] as [DATE]
    ,REVENUE.TRANSACTIONAMOUNT as AMOUNT
    ,case 
      when REVENUE.DONOTPOST = 1
        then 2
      when REVENUEPOSTED.ID is not null
        then 0
      else 1
      end as POSTSTATUSCODE
    ,REVENUE.POSTDATE as POSTDATE
    ,REVENUESCHEDULE.FREQUENCYCODE as FREQUENCYCODE
    ,REVENUESCHEDULE.NUMBEROFINSTALLMENTS as NUMBEROFINSTALLMENTS
    ,coalesce(REVENUESCHEDULE.NEXTTRANSACTIONDATE, REVENUESCHEDULE.STARTDATE) as NEXTTRANSACTIONDATE
    ,REVENUE.TSLONG as TSLONG
    ,DONOTACKNOWLEDGE
    ,(
      select top 1 GLREVENUECATEGORYMAPPINGID
      from dbo.REVENUECATEGORY
      inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID
      where REVENUEID = REVENUE.ID
      ) as CATEGORYCODEID
    ,dbo.UFN_REVENUEDETAIL_GETMAXSOLICITORSPLITAMOUNTS_TOITEMLISTXML(@ID) as MAXSOLICITORSPLITAMOUNTS
    ,ISREIMBURSABLE as ISREIMBURSABLE
    ,1 as GRANTAWARDON
    ,-- Grant program

    null as OPPORTUNITYID
    ,null as PROSPECTNAME
    ,GRANTS.GRANTORID as CONSTITUENTID
    ,REVENUE.BASECURRENCYID
    ,REVENUE.TRANSACTIONCURRENCYID
    ,REVENUE.BASEEXCHANGERATEID
    ,CURRENCYEXCHANGERATE.RATE as EXCHANGERATE
    ,case 
      when CURRENCYEXCHANGERATE.TYPECODE = 2
        then 1
      else 0
      end as HADSPOTRATE
    ,CURRENCY.DECIMALDIGITS as BASEDECIMALDIGITS
    ,CURRENCY.ROUNDINGTYPECODE as BASEROUNDINGTYPECODE
    ,dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCYDESCRIPTION
  from dbo.REVENUE
  left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
  inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
  inner join dbo.REVENUEFUNDINGREQUEST on REVENUE.ID = REVENUEFUNDINGREQUEST.ID
  inner join dbo.FUNDINGREQUEST on REVENUEFUNDINGREQUEST.FUNDINGREQUESTID = FUNDINGREQUEST.ID
  inner join dbo.GRANTS on FUNDINGREQUEST.GRANTSID = GRANTS.ID
  inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.BASECURRENCYID
  left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
  where REVENUE.ID = @ID
    and REVENUE.TRANSACTIONTYPECODE = 6

  union all

  select 1 as DATALOADED
    ,null as GRANTNAME
    ,null as GRANTORNAME
    ,REVENUE.[DATE] as [DATE]
    ,REVENUE.TRANSACTIONAMOUNT as AMOUNT
    ,case 
      when REVENUE.DONOTPOST = 1
        then 2
      when REVENUEPOSTED.ID is not null
        then 0
      else 1
      end as POSTSTATUSCODE
    ,REVENUE.POSTDATE as POSTDATE
    ,REVENUESCHEDULE.FREQUENCYCODE as FREQUENCYCODE
    ,REVENUESCHEDULE.NUMBEROFINSTALLMENTS as NUMBEROFINSTALLMENTS
    ,coalesce(REVENUESCHEDULE.NEXTTRANSACTIONDATE, REVENUESCHEDULE.STARTDATE) as NEXTTRANSACTIONDATE
    ,REVENUE.TSLONG as TSLONG
    ,DONOTACKNOWLEDGE
    ,(
      select top 1 GLREVENUECATEGORYMAPPINGID
      from dbo.REVENUECATEGORY
      inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID
      where REVENUEID = REVENUE.ID
      ) as CATEGORYCODEID
    ,dbo.UFN_REVENUEDETAIL_GETMAXSOLICITORSPLITAMOUNTS_TOITEMLISTXML(@ID) as MAXSOLICITORSPLITAMOUNTS
    ,ISREIMBURSABLE as ISREIMBURSABLE
    ,2 as GRANTAWARDON
    ,-- Opportunity

    OPPORTUNITY.ID as OPPORTUNITYID
    ,dbo.UFN_CONSTITUENT_BUILDNAME(PROSPECTPLAN.PROSPECTID) as PROSPECTNAME
    ,PROSPECTPLAN.PROSPECTID as CONSTITUENTID
    ,REVENUE.BASECURRENCYID
    ,REVENUE.TRANSACTIONCURRENCYID
    ,REVENUE.BASEEXCHANGERATEID
    ,CURRENCYEXCHANGERATE.RATE as EXCHANGERATE
    ,case 
      when CURRENCYEXCHANGERATE.TYPECODE = 2
        then 1
      else 0
      end as HADSPOTRATE
    ,CURRENCY.DECIMALDIGITS as BASEDECIMALDIGITS
    ,CURRENCY.ROUNDINGTYPECODE as BASEROUNDINGTYPECODE
    ,dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCYDESCRIPTION
  from dbo.REVENUE
  left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
  inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
  inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
  inner join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID
  inner join dbo.OPPORTUNITY on REVENUEOPPORTUNITY.OPPORTUNITYID = OPPORTUNITY.ID
  inner join dbo.PROSPECTPLAN on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
  inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.BASECURRENCYID
  left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
  where REVENUE.ID = @ID
    and REVENUE.TRANSACTIONTYPECODE = 6
  ) as TEMPTABLE;

if @DATALOADED = 1
begin
  select top 1 @ADJPAYMENT_DATE = ADJ.[DATE]
    ,@ADJPAYMENT_POSTDATE = ADJ.POSTDATE
    ,@ADJPAYMENT_DETAILS = ADJ.REASON
    ,@ADJPAYMENT_REASONCODEID = ADJ.REASONCODEID
  from (
    select A.[DATE]
      ,A.POSTDATE
      ,A.REASON
      ,A.REASONCODEID
    from dbo.ADJUSTMENT A
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTL on A.ID = FTL.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    inner join dbo.INSTALLMENTPAYMENT I on FTL.ID = I.PAYMENTID
    where I.PLEDGEID = @ID
      and A.POSTSTATUSCODE = 1
      and FTL.DELETEDON is null

    union all

    select A.[DATE]
      ,A.POSTDATE
      ,A.REASON
      ,A.REASONCODEID
    from dbo.WRITEOFFADJUSTMENT A
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTL on A.ID = FTL.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    inner join dbo.FINANCIALTRANSACTION FT on FTL.FINANCIALTRANSACTIONID = FT.ID
    where FT.PARENTID = @ID
      and A.POSTSTATUSCODE = 1
      and FTL.DELETEDON is null
    ) ADJ
  order by ADJ.[DATE] desc

  set @AMOUNTPAID = @AMOUNT - dbo.UFN_PLEDGE_GETBALANCE(@ID);
  set @LASTINSTALLMENT = dbo.UFN_INSTALLMENT_GETLASTINSTALLMENTPAID(@ID);

  if @LASTINSTALLMENT is null
  begin
    set @LASTPAYMENTSEQUENCE = 0;
  end
  else
  begin
    set @LASTPAYMENTSEQUENCE = (
        select SEQUENCE
        from dbo.INSTALLMENT
        where ID = @LASTINSTALLMENT
        );
  end

  --set @INSTALLMENTS = dbo.UFN_INSTALLMENT_GETINSTALLMENTS_TOITEMLISTXML(@ID);

  -- set @NUMBEROFINSTALLMENTS to the number of unpaid installments

  select @LASTPAYMENTSEQUENCE = COALESCE(MAX(INSTALLMENT.SEQUENCE), 0)
  ,@NUMBEROFINSTALLMENTS = COUNT(INSTALLMENT.ID)
  from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2(@ID) INSTALLMENT
  where INSTALLMENT.BALANCE > 0;

  set @SPLITS = (
      select SPLITS.TRANSACTIONAMOUNT AMOUNT
        ,SPLITS.APPLICATIONCODE
        ,SPLITS.CATEGORYCODEID
        ,SPLITS.DESIGNATIONID
        ,SPLITS.ID
        ,SPLITS.TYPECODE
        ,SPLITS.TRANSACTIONCURRENCYID
      from dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLITS
      inner join dbo.DESIGNATION on DESIGNATION.ID = SPLITS.DESIGNATIONID
      order by DESIGNATION.VANITYNAME
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('SPLITS')
        ,binary BASE64
      );
  set @INSTALLMENTS = (
      select I.ID
        ,I.[DATE]
        ,I.TRANSACTIONAMOUNT AMOUNT
        ,(I.TRANSACTIONAMOUNT - (I.AMOUNTPAID + I.AMOUNTWRITTENOFF)) as BALANCE
        ,(I.AMOUNTPAID + I.AMOUNTWRITTENOFF) as APPLIED
        ,I.SEQUENCE
        ,I.PAYMENTCOUNT
        ,I.WRITEOFFCOUNT
        ,(
          select SPLITINFO.ID
            ,SPLITINFO.DESIGNATIONID
            ,SPLITINFO.TRANSACTIONAMOUNT AMOUNT
            ,(SPLITINFO.AMOUNTPAID + SPLITINFO.AMOUNTWRITTENOFF) APPLIED
            ,SPLITINFO.TRANSACTIONCURRENCYID
            ,SPLITINFO.REVENUESPLITID
            ,SPLITINFO.PAYMENTCOUNT
            ,SPLITINFO.WRITEOFFCOUNT
          from dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITINFO_BULK() SPLITINFO
          left join dbo.DESIGNATION on DESIGNATION.ID = SPLITINFO.DESIGNATIONID
          where SPLITINFO.INSTALLMENTID = I.ID
          order by DESIGNATION.VANITYNAME
          for xml raw('ITEM')
            ,type
            ,elements
            ,binary BASE64
          ) as INSTALLMENTSPLITS
        ,I.BASECURRENCYID
        ,I.ORGANIZATIONAMOUNT
        ,I.ORGANIZATIONEXCHANGERATEID
        ,I.TRANSACTIONCURRENCYID
        ,I.BASEEXCHANGERATEID
      from dbo.UFN_INSTALLMENT_GETINFO_BULK() I
      where I.REVENUEID = @ID
      order by I.[DATE]
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('INSTALLMENTS')
        ,binary BASE64
 );
end

set @HASPAYMENTS = dbo.UFN_PLEDGE_PAYMENTSEXIST(@ID);

if @HASPAYMENTS = 0
  set @HASPOSTEDPAYMENTS = 0 --if there are no payments, then there can't be posted payments

else
  set @HASPOSTEDPAYMENTS = dbo.UFN_REVENUE_HASPOSTEDPAYMENTS(@ID)

set @HASPOSTEDWRITEOFFS = dbo.UFN_REVENUE_HASPOSTEDWRITEOFFS(@ID);
--replace with commented code for PBI 102747

set @USERGRANTEDSPOTRATE = 1;

if exists (
    select 1
    from dbo.REVENUERECOGNITION
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUERECOGNITION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
    )
    set @HASRECOGNITIONCREDIT = 1
else
    set @HASRECOGNITIONCREDIT = 0

set @UPDATERECOGNITIONOPTION = 0

/*set @USERGRANTEDSPOTRATE = case 
                            when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or 
                                dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '911f104d-ba5f-4469-b0ae-184c879aea99') = 1 
                                    then 1
                                    else 0
                            end;*/
return 0;