V_CUSTOMFORM_REVIEWPAYMENTS
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | int | ||
| FirstName | nvarchar(50) | yes | |
| LastName | nvarchar(100) | yes | |
| PaymentAmount | money | yes | |
| CreditCardType | nvarchar(100) | yes | |
| CardHolderName | nvarchar(255) | yes | |
| CreditCardNumber | nvarchar(4) | yes | |
| PaymentMethod | nvarchar(100) | yes | |
| PaymentDate | datetime | yes | |
| ReferenceNumber | nvarchar(100) | yes | |
| Title | nvarchar(200) | ||
| ClientSitesID | int | yes | |
| EntryID | uniqueidentifier | ||
| PaymentReviewed | tinyint |
Definition
Copy
CREATE view dbo.V_CUSTOMFORM_REVIEWPAYMENTS as
select
formentry.ID as ID,
FirstName = CASE formdatasource.DataSource
WHEN '132E3702-0207-4d32-AA6F-7B265B1DC7D0' THEN CUSTOMFORMDASHBOARDFORMENTRY.STUDENTFIRSTNAME --Online Admissions
WHEN '1927B45A-F7F8-44d7-BF45-66899B8C7D43' THEN CUSTOMFORMDASHBOARDFORMENTRY.STUDENTFIRSTNAME --Online Reenrollment
ELSE clientuser.FirstName
END,
LastName = CASE formdatasource.DataSource
WHEN '132E3702-0207-4d32-AA6F-7B265B1DC7D0' THEN CUSTOMFORMDASHBOARDFORMENTRY.STUDENTLASTNAME --Online Admissions
WHEN '1927B45A-F7F8-44d7-BF45-66899B8C7D43' THEN CUSTOMFORMDASHBOARDFORMENTRY.STUDENTLASTNAME --Online Reenrollment
ELSE clientuser.LastName
END,
case formentry.PaymentMethod when 'Waive' then 0 else formentry.PaymentAmount end as PaymentAmount,
formentry.CreditCardType,
formentry.CardHolderName,
right(formentry.CreditCardNumber,4) as CreditCardNumber,
formentry.PaymentMethod,
case formentry.PaymentDate when CAST('0001-01-01 00:00:00.0000000 +00:00' as datetimeoffset) then null else CAST(formentry.PaymentDate as datetime) end as PaymentDate,
formentry.ReferenceNumber,
form.Title,
formentry.ClientSitesID,
formentry.EntryID,
formentry.PaymentReviewed
from
dbo.CustomFormEntry formentry
inner join dbo.CustomFormEntryAttribute statusAttribute ON (formentry.EntryID = statusAttribute.EntryID) and (formentry.EntryState = statusAttribute.AttributeType)
inner join dbo.CustomFormElement form ON formentry.FormGuid = form.Guid
inner join dbo.CustomFormDataSource formdatasource ON form.Guid = formdatasource.FormGuid
inner join dbo.CustomFormSettings formsettings ON formentry.FormGuid = formsettings.FormGUID
left outer join dbo.ClientUsers clientuser ON formentry.ClientUserID = clientuser.ID
left outer join dbo.CUSTOMFORMDASHBOARDFORMENTRY ON CUSTOMFORMDASHBOARDFORMENTRY.CUSTOMFORMENTRYID = formentry.EntryID
where (formentry.PaymentMethod <> 'None') and (formentry.EntryState = 2)