USP_REVENUE_GETPAYMENTDETAILS

Returns payment details for a transaction.

Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@PAYMENTMETHODCODE	tinyint	INOUT
@CHECKDATE	UDT_FUZZYDATE	INOUT
@CHECKNUMBER	nvarchar(20)	INOUT
@REFERENCEDATE	UDT_FUZZYDATE	INOUT
@REFERENCENUMBER	nvarchar(20)	INOUT
@CARDHOLDERNAME	nvarchar(255)	INOUT
@CREDITCARDNUMBER	nvarchar(4)	INOUT
@CREDITTYPECODEID	uniqueidentifier	INOUT
@AUTHORIZATIONCODE	nvarchar(20)	INOUT
@EXPIRESON	UDT_FUZZYDATE	INOUT
@ISSUER	nvarchar(100)	INOUT
@NUMBEROFUNITS	decimal(20, 3)	INOUT
@SYMBOL	nvarchar(25)	INOUT
@MEDIANPRICE	decimal(19, 4)	INOUT
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	INOUT
@PROPERTYSUBTYPECODEID	uniqueidentifier	INOUT
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT
@DIRECTDEBITRESULTCODE	nvarchar(10)	INOUT
@LOWPRICE	decimal(19, 4)	INOUT
@HIGHPRICE	decimal(19, 4)	INOUT
@GIFTINKINDITEMNAME	nvarchar(100)	INOUT
@GIFTINKINDDISPOSITIONCODE	tinyint	INOUT
@GIFTINKINDNUMBEROFUNITS	int	INOUT
@GIFTINKINDFAIRMARKETVALUE	money	INOUT
@DIRECTDEBITISREJECTED	bit	INOUT
@HASCREDITCARDBEENCHARGED	bit	INOUT
@SALEAMOUNT	money	INOUT
@SALEDATE	datetime	INOUT
@BROKERFEE	money	INOUT
@SALEPOSTSTATUSCODE	tinyint	INOUT
@SALEPOSTDATE	datetime	INOUT
@SEPAMANDATEID	uniqueidentifier	INOUT

Definition

Copy



      CREATE procedure dbo.USP_REVENUE_GETPAYMENTDETAILS
      (
        @REVENUEID uniqueidentifier,
        @PAYMENTMETHODCODE tinyint = null output,
        @CHECKDATE dbo.UDT_FUZZYDATE = null output,
        @CHECKNUMBER nvarchar(20) = null output,
        @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
        @REFERENCENUMBER nvarchar(20) = null output,
        @CARDHOLDERNAME nvarchar(255) = null output,
        @CREDITCARDNUMBER nvarchar(4) = null output,
        @CREDITTYPECODEID uniqueidentifier = null output,
        @AUTHORIZATIONCODE nvarchar(20) = null output,
        @EXPIRESON dbo.UDT_FUZZYDATE = null output,
        @ISSUER nvarchar(100) = null output,
        @NUMBEROFUNITS decimal(20,3) = null output,
        @SYMBOL nvarchar(25) = null output,
        @MEDIANPRICE decimal(19,4) = null output,
        @GIFTINKINDSUBTYPECODEID uniqueidentifier = null output,
        @PROPERTYSUBTYPECODEID uniqueidentifier = null output,
        @CONSTITUENTACCOUNTID uniqueidentifier = null output,
        @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
        @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
        @LOWPRICE decimal(19,4) = null output,
        @HIGHPRICE decimal(19,4) = null output,
        @GIFTINKINDITEMNAME nvarchar(100) = null output,
        @GIFTINKINDDISPOSITIONCODE tinyint = null output,
        @GIFTINKINDNUMBEROFUNITS int = null output,
        @GIFTINKINDFAIRMARKETVALUE money = null output,
        @DIRECTDEBITISREJECTED bit = null output,
        @HASCREDITCARDBEENCHARGED bit = null output,
        @SALEAMOUNT money = null output,
        @SALEDATE datetime = null output,
        @BROKERFEE money = null output,
        @SALEPOSTSTATUSCODE tinyint = null output,
        @SALEPOSTDATE datetime = null output,
        @SEPAMANDATEID uniqueidentifier = null output
      )
      as
        set nocount on

        declare @PAYMENTMETHODID uniqueidentifier;
        select
          @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
          @PAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID
        from dbo.REVENUEPAYMENTMETHOD
        where REVENUEID = @REVENUEID;

        select
          @REFERENCENUMBER = '',
          @REFERENCEDATE = '00000000',
          @CHECKDATE = '00000000',
          @CHECKNUMBER = '',
          @CARDHOLDERNAME = '',
          @CREDITCARDNUMBER = '',
          @CREDITTYPECODEID = null,
          @AUTHORIZATIONCODE = '',
          @EXPIRESON = '00000000',
          @CONSTITUENTACCOUNTID = null,
          @ISSUER = '',
          @NUMBEROFUNITS = 0,
          @SYMBOL = '',
          @MEDIANPRICE = 0,
          @PROPERTYSUBTYPECODEID = null,
          @LOWPRICE = 0,
          @HIGHPRICE = 0,
          @GIFTINKINDITEMNAME = '',
          @GIFTINKINDDISPOSITIONCODE = 0,
          @GIFTINKINDNUMBEROFUNITS = 0,
          @GIFTINKINDFAIRMARKETVALUE = 0,
          @HASCREDITCARDBEENCHARGED = 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,
            @HASCREDITCARDBEENCHARGED = case when TRANSACTIONID is not null then 1 else 0 end
          from dbo.CREDITCARDPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;
        end

        if @PAYMENTMETHODCODE = 3 --Direct Debit

          select
            @REFERENCEDATE = DIRECTDEBITPAYMENTMETHODDETAIL.REFERENCEDATE,
            @REFERENCENUMBER = DIRECTDEBITPAYMENTMETHODDETAIL.REFERENCENUMBER,
            @CONSTITUENTACCOUNTID = DIRECTDEBITPAYMENTMETHODDETAIL.CONSTITUENTACCOUNTID,
            @DIRECTDEBITRESULTCODE = DIRECTDEBITPAYMENTMETHODDETAIL.DIRECTDEBITRESULTCODE,
            @DIRECTDEBITISREJECTED = DIRECTDEBITPAYMENTMETHODDETAIL.ISREJECTED,
            @SEPAMANDATEID = DIRECTDEBITPAYMENTMETHODDETAIL.SEPAMANDATEID
          from 
            dbo.DIRECTDEBITPAYMENTMETHODDETAIL 
          where 
            DIRECTDEBITPAYMENTMETHODDETAIL.ID = @PAYMENTMETHODID;

        if @PAYMENTMETHODCODE = 4 --Stock

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

        if @PAYMENTMETHODCODE = 5 --Property

          select top 1
            @PROPERTYSUBTYPECODEID = PROPERTYSUBTYPECODEID,
            @SALEAMOUNT = TRANSACTIONSALEAMOUNT,
            @SALEDATE = SALEDATE,
            @BROKERFEE = TRANSACTIONBROKERFEE,
            @SALEPOSTSTATUSCODE = 
              case 
                when (    select top 1
                      REVENUE.DONOTPOST
                    from
                      dbo.REVENUE
                      inner join dbo.REVENUEPAYMENTMETHOD on [REVENUEPAYMENTMETHOD].REVENUEID = REVENUE.ID
                    where
                      REVENUEPAYMENTMETHOD.ID = @PAYMENTMETHODID
                  ) = 1
                  then 2
                else
                  case PROPERTYDETAIL.[ISSOLD] when 1 then PROPERTYDETAIL.[SALEPOSTSTATUSCODE] else case when PROPERTYDETAIL.[SALEPOSTDATE] is null then 1 else  
                  PROPERTYDETAIL.[SALEPOSTSTATUSCODE] end end
              end,
            @SALEPOSTDATE = SALEPOSTDATE
          from dbo.PROPERTYDETAIL WHERE ID = @PAYMENTMETHODID;

        if @PAYMENTMETHODCODE = 6 --Gift in Kind

          select
            @GIFTINKINDSUBTYPECODEID = GIFTINKINDSUBTYPECODEID,
            @GIFTINKINDITEMNAME = ITEMNAME,
            @GIFTINKINDDISPOSITIONCODE = DISPOSITIONCODE,
            @GIFTINKINDNUMBEROFUNITS = NUMBEROFUNITS,
            @GIFTINKINDFAIRMARKETVALUE = TRANSACTIONFAIRMARKETVALUE
          from dbo.GIFTINKINDPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;

        if @PAYMENTMETHODCODE = 10 --Other

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

        if @PAYMENTMETHODCODE = 11 --Standing Order

          select
            @REFERENCEDATE = REFERENCEDATE,
            @REFERENCENUMBER = N'',
            @CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID
          from dbo.STANDINGORDERPAYMENTMETHODDETAIL where ID = @PAYMENTMETHODID;

        if @PAYMENTMETHODCODE = 101 -- PayPal

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

        if @PAYMENTMETHODCODE = 102 -- Venmo

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