USP_DATAFORMTEMPLATE_ADD_PAYMENTBYTRANSACTION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PAYMENTBYTRANSACTION
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier,
  @TRANSACTIONID uniqueidentifier,
  @AMOUNT money = 0,
  @RECEIPTAMOUNT money = 0,
  @SPLITS xml = null,
  @REVENUESTREAMS xml = null,
  @SOURCECODE nvarchar(50) = null,
  @APPEALID uniqueidentifier = null,
  @BENEFITS xml = null,
  @BENEFITSWAIVED bit = 0,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @UNAPPLIEDMATCHINGGIFTSPLITS xml = null,
  @UNAPPLIEDMATCHINGGIFTAMOUNT money = 0,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
  @REFERENCE nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;

declare @REVENUEID uniqueidentifier;
declare @DATE datetime;
declare @PAYMENTMETHODCODE tinyint;
declare @CHECKDATE dbo.UDT_FUZZYDATE;
declare @CHECKNUMBER nvarchar(20);
declare @REFERENCEDATE dbo.UDT_FUZZYDATE;
declare @REFERENCENUMBER nvarchar(20);
declare @CARDHOLDERNAME nvarchar(255);
declare @CREDITCARDNUMBER nvarchar(4);
declare @CREDITTYPECODEID uniqueidentifier;
declare @AUTHORIZATIONCODE nvarchar(20);
declare @EXPIRESON dbo.UDT_FUZZYDATE;
declare @ISSUER nvarchar(100);
declare @NUMBEROFUNITS decimal(20,3);
declare @SYMBOL nvarchar(25);
declare @MEDIANPRICE decimal(19,4);
declare @GIFTINKINDSUBTYPECODEID uniqueidentifier;
declare @PROPERTYSUBTYPECODEID uniqueidentifier;
declare @CONSTITUENTACCOUNTID uniqueidentifier;
declare @POSTSTATUSCODE tinyint;
declare @POSTDATE datetime;
declare @FINDERNUMBER bigint;
declare @GIVENANONYMOUSLY bit;
declare @DONOTRECEIPT bit;
declare @DONOTACKNOWLEDGE bit;
declare @LOWPRICE decimal(19,4);
declare @HIGHPRICE decimal(19,4);

if @CHANGEAGENTID is null
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();

begin try
  declare @CONSTITUENTID uniqueidentifier;
  declare @PAYMENTMETHODID uniqueidentifier;

  --Set default payment values

  select top 1 
    @REVENUEID = REVENUE.ID,
    @CONSTITUENTID = REVENUE.CONSTITUENTID,
    @DATE = REVENUE.DATE,
    @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
    @PAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID,
    @POSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end,
    @POSTDATE = REVENUE.POSTDATE,
    @DONOTRECEIPT = REVENUE.DONOTRECEIPT,
    @GIVENANONYMOUSLY = REVENUE.GIVENANONYMOUSLY,
    @DONOTACKNOWLEDGE = REVENUE.DONOTACKNOWLEDGE
  from dbo.REVENUE 
  inner join dbo.REVENUEPAYMENTMETHOD
    on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
  left outer join REVENUEPOSTED
    on REVENUE.ID = REVENUEPOSTED.ID
  where REVENUE.ID = @TRANSACTIONID;

  select  @REFERENCENUMBER = '',
      @REFERENCEDATE = '00000000',
      @CHECKDATE = '00000000',
      @CHECKNUMBER = '',
      @CARDHOLDERNAME = '',
      @CREDITCARDNUMBER = '',
      @CREDITTYPECODEID = null,
      @AUTHORIZATIONCODE = '',
      @EXPIRESON = '00000000',
      @CONSTITUENTACCOUNTID = null,
      @ISSUER = '',
      @NUMBEROFUNITS = 0,
      @SYMBOL = '',
      @MEDIANPRICE = 0,
      @PROPERTYSUBTYPECODEID = null,
      @OTHERPAYMENTMETHODCODEID = null,
      @LOWPRICE = 0,
      @HIGHPRICE = 0

  if @PAYMENTMETHODCODE = 0 --Cash

    select 
      @REFERENCENUMBER = REFERENCENUMBER,
      @REFERENCEDATE = REFERENCEDATE
    from dbo.CASHPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;

  if @PAYMENTMETHODCODE = 1 --Check

    select @CHECKDATE = CHECKDATE,
         @CHECKNUMBER = CHECKNUMBER
    from dbo.CHECKPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;

  if @PAYMENTMETHODCODE = 2 --Credit Card

  begin
    select 
      @CARDHOLDERNAME = CARDHOLDERNAME,
      @CREDITCARDNUMBER = CREDITCARDPARTIALNUMBER,
      @CREDITTYPECODEID = CREDITTYPECODEID,
      @AUTHORIZATIONCODE = AUTHORIZATIONCODE,
      @EXPIRESON = EXPIRESON
    from dbo.CREDITCARDPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;
  end

  if @PAYMENTMETHODCODE = 3 --Direct Debit

    select
      @REFERENCEDATE = REFERENCEDATE,
      @REFERENCENUMBER = REFERENCENUMBER,
      @CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID
    from dbo.DIRECTDEBITPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;

  if @PAYMENTMETHODCODE = 4 --Stock

    select 
      @ISSUER = ISSUER,
      @NUMBEROFUNITS = NUMBEROFUNITS,
      @SYMBOL = SYMBOL,
      @MEDIANPRICE = MEDIANPRICE,
      @LOWPRICE = LOWPRICE,
      @HIGHPRICE = HIGHPRICE
    from dbo.STOCKDETAIL where ID = @PAYMENTMETHODID;

  if @PAYMENTMETHODCODE = 5 --Property

    select 
      @PROPERTYSUBTYPECODEID = PROPERTYSUBTYPECODEID 
    from dbo.PROPERTYDETAIL WHERE ID = @PAYMENTMETHODID;

  if @PAYMENTMETHODCODE = 6 --Gift in Kind

    select
      @GIFTINKINDSUBTYPECODEID = GIFTINKINDSUBTYPECODEID
    from dbo.GIFTINKINDPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;


  if @PAYMENTMETHODCODE = 10 --Other

    select 
      @REFERENCENUMBER = REFERENCENUMBER,
      @REFERENCEDATE = REFERENCEDATE,
      @OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODEID
    from dbo.OTHERPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;

  if @CONSTITUENTID is null
    raiserror('Unable to locate associated transaction.', 13, 1);

  set @REVENUEID = null;
  set @ID = @TRANSACTIONID;

  exec dbo.USP_PAYMENT_ADD @REVENUEID output, @CHANGEAGENTID, @CURRENTDATE, @CONSTITUENTID, @ID,
              @DATE, @AMOUNT, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER,
              @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER,
              @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER,
              @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @GIFTINKINDSUBTYPECODEID,
              @PROPERTYSUBTYPECODEID, @RECEIPTAMOUNT, @CONSTITUENTACCOUNTID, @SPLITS,
              @REVENUESTREAMS, @POSTSTATUSCODE, @POSTDATE, @FINDERNUMBER,
              @SOURCECODE, @APPEALID, @BENEFITS, @BENEFITSWAIVED, @GIVENANONYMOUSLY, 
              @MAILINGID, @CHANNELCODEID, @DONOTACKNOWLEDGE,@DONOTRECEIPT,@UNAPPLIEDMATCHINGGIFTSPLITS,
              '', @OTHERPAYMENTMETHODCODEID, @REFERENCE, @CATEGORYCODEID, null, @LOWPRICE, @HIGHPRICE, @CURRENTAPPUSERID
end try

begin catch
  exec dbo.USP_RAISE_ERROR;

  return 1;
end catch

return 0;