USP_DATAFORMTEMPLATE_VIEW_BILLINGTRANSACTIONDETAIL
The load procedure used by the view dataform template "Billing Transaction Detail View Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @FINANCIALTRANSACTIONID | uniqueidentifier | INOUT | FINANCIALTRANSACTIONID |
| @TYPECODE | tinyint | INOUT | TYPECODE |
| @TYPE | nvarchar(100) | INOUT | TYPE |
| @TRANSACTIONID | nvarchar(60) | INOUT | ID |
| @CHARGINGFOR | nvarchar(100) | INOUT | Charging for |
| @DATEACADEMICYEAR | nvarchar(100) | INOUT | Academic year |
| @DESCRIPTION | nvarchar(100) | INOUT | Description |
| @CREDITINGFOR | nvarchar(100) | INOUT | Crediting for |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BILLINGTRANSACTIONDETAIL
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@FINANCIALTRANSACTIONID uniqueidentifier = null output,
@TYPECODE tinyint = null output,
@TYPE nvarchar(100) = null output,
@TRANSACTIONID nvarchar(60) = null output,
@CHARGINGFOR nvarchar(100) = null output,
@DATEACADEMICYEAR nvarchar(100) = null output,
@DESCRIPTION nvarchar(100) = null output,
@CREDITINGFOR nvarchar(100) = null output
)
as
set nocount on;
set @DATALOADED = 0;
-- Open the symmetric key for decryption
exec dbo.USP_GET_KEY_ACCESS;
select @DATALOADED = 1,
@FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID,
@TYPECODE = FINANCIALTRANSACTION.TYPECODE,
@TYPE = FINANCIALTRANSACTION.TYPE,
@TRANSACTIONID = FINANCIALTRANSACTION.USERDEFINEDID,
@DATEACADEMICYEAR = case when ((FINANCIALTRANSACTION.TYPECODE = 104) and (not STUDENTPROGRESSION.ID is null)) then
(select ACADEMICYEARNAMECODE.DESCRIPTION
from dbo.ACADEMICYEAR
inner join dbo.ACADEMICYEARNAMECODE
on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
where ACADEMICYEAR.ID = dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOL.ID, STUDENTPROGRESSION.STARTDATE, STUDENTPROGRESSION.ENDDATE))
when ((FINANCIALTRANSACTION.TYPECODE = 106) and (not SP2.ID is null)) then
(select ACADEMICYEARNAMECODE.DESCRIPTION
from dbo.ACADEMICYEAR
inner join dbo.ACADEMICYEARNAMECODE
on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
where ACADEMICYEAR.ID = dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(S2.ID, SP2.STARTDATE, SP2.ENDDATE))
when (FINANCIALTRANSACTION.TYPECODE = 104 or FINANCIALTRANSACTION.TYPECODE = 106) then
dbo.UFN_BILLINGCYCLE_GETBILLINGCYCLEBYDATE(FINANCIALTRANSACTION.DATE)
else
''
end
+ char(13)
+ (case when (exists (select BILLINGITEM.ID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join CHARGELINEITEM
on FINANCIALTRANSACTIONLINEITEM.ID = CHARGELINEITEM.ID
left outer join dbo.BILLINGITEM
on CHARGELINEITEM.BILLINGITEMID = BILLINGITEM.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and
BILLINGITEM.PRICETYPECODE=1)) then
Coalesce(dbo.UFN_STUDENT_GETSCHOOLGRADELEVELBYDATE(FINANCIALTRANSACTION.DATE, FINANCIALTRANSACTION.CONSTITUENTID),'')
when (exists (select BILLINGITEM.ID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join RECEIVABLECREDITLINEITEM
on FINANCIALTRANSACTIONLINEITEM.ID = RECEIVABLECREDITLINEITEM.ID
left outer join dbo.BILLINGITEM
on RECEIVABLECREDITLINEITEM.BILLINGITEMID = BILLINGITEM.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and
BILLINGITEM.PRICETYPECODE=1)) then
Coalesce(dbo.UFN_STUDENT_GETSCHOOLGRADELEVELBYDATE(FINANCIALTRANSACTION.DATE, FINANCIALTRANSACTION.CONSTITUENTID),'')
when FINANCIALTRANSACTION.TYPECODE = 104 then
(case when not CONSTITUENTSCHOOL.ID is null then
CONSTITUENTSCHOOL.KEYNAME + ', ' + GRADELEVEL.DESCRIPTION
when not CONSTITUENTEDUCATIONALINSTITUTION.ID is null then
CONSTITUENTEDUCATIONALINSTITUTION.KEYNAME else ''
end)
when FINANCIALTRANSACTION.TYPECODE = 106 then
(case when not CS2.ID is null then
CS2.KEYNAME + ', ' + GL2.DESCRIPTION
when not CEI2.ID is null then
CEI2.KEYNAME else ''
end)
end)
from dbo.FINANCIALTRANSACTION
left outer join dbo.CHARGE -- Charge specific info
on FINANCIALTRANSACTION.ID = CHARGE.ID
left outer join dbo.STUDENTCHARGE -- Student charge specific info
on CHARGE.ID = STUDENTCHARGE.ID
inner join dbo.CONSTITUENT
on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
left outer join dbo.STUDENTPROGRESSION
on STUDENTCHARGE.STUDENTPROGRESSIONID=STUDENTPROGRESSION.ID
left outer join dbo.SCHOOLGRADELEVEL
on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
left outer join dbo.SCHOOL
on SCHOOLGRADELEVEL.SCHOOLID=SCHOOL.ID
left outer join dbo.CONSTITUENT CONSTITUENTSCHOOL
on CONSTITUENTSCHOOL.ID=SCHOOL.ID
left outer join dbo.GRADELEVEL
on SCHOOLGRADELEVEL.GRADELEVELID=GRADELEVEL.ID
left outer join dbo.EDUCATIONALHISTORY
on STUDENTCHARGE.EDUCATIONALHISTORYID=EDUCATIONALHISTORY.ID
left outer join CONSTITUENT CONSTITUENTEDUCATIONALINSTITUTION
on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID=CONSTITUENTEDUCATIONALINSTITUTION.ID
left outer join dbo.RECEIVABLECREDIT -- Credit specific info
on FINANCIALTRANSACTION.ID = RECEIVABLECREDIT.ID
left outer join dbo.STUDENTPROGRESSION SP2
on RECEIVABLECREDIT.STUDENTPROGRESSIONID=SP2.ID
left outer join dbo.SCHOOLGRADELEVEL SGL2
on SP2.SCHOOLGRADELEVELID = SGL2.ID
left outer join dbo.SCHOOL S2
on SGL2.SCHOOLID=S2.ID
left outer join dbo.CONSTITUENT CS2
on CS2.ID=S2.ID
left outer join dbo.GRADELEVEL GL2
on SGL2.GRADELEVELID=GL2.ID
left outer join dbo.EDUCATIONALHISTORY EH2
on RECEIVABLECREDIT.EDUCATIONALHISTORYID=EH2.ID
left outer join CONSTITUENT CEI2
on EH2.EDUCATIONALINSTITUTIONID=CEI2.ID
where FINANCIALTRANSACTION.ID = @ID
-- If we are dealing with a charge then we need to get all the billing items
if (@TYPECODE = 104)
begin
set @CHARGINGFOR =
(select stuff((select char(13) + BILLINGITEM.NAME
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join CHARGELINEITEM
on FINANCIALTRANSACTIONLINEITEM.ID = CHARGELINEITEM.ID
left outer join dbo.BILLINGITEM
on CHARGELINEITEM.BILLINGITEMID = BILLINGITEM.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, ''))
end
-- If we are dealing with a credit then we need to get all the billing items
if (@TYPECODE = 106)
begin
set @CREDITINGFOR =
(select stuff((select char(13) + BILLINGITEM.NAME
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join RECEIVABLECREDITLINEITEM
on FINANCIALTRANSACTIONLINEITEM.ID = RECEIVABLECREDITLINEITEM.ID
left outer join dbo.BILLINGITEM
on RECEIVABLECREDITLINEITEM.BILLINGITEMID = BILLINGITEM.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, ''))
end
-- put the descriptions together on separate lines
select @DESCRIPTION = dbo.UDA_BUILDLIST(FINANCIALTRANSACTIONLINEITEM.DESCRIPTION)
from dbo.FINANCIALTRANSACTIONLINEITEM
where (FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID) and
(len(FINANCIALTRANSACTIONLINEITEM.DESCRIPTION) > 0);
close symmetric key sym_BBInfinity;
return 0;