USP_REPORT_DEFAULTREFUNDITEMIZEDRECEIPT
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CREDITID | uniqueidentifier | IN |
Definition
Copy
create procedure dbo.USP_REPORT_DEFAULTREFUNDITEMIZEDRECEIPT
(
@CREDITID uniqueidentifier
)
as
set nocount on;
set transaction isolation level read uncommitted;
--The following are used for ORDERITEMTYPE and SUBORDERITEMTYPE
-- 0 - Ticket
-- 1 - Combination
-- 2 - Event Registration
-- 3 - Membership
-- 4 - Merchandise
-- 5 - Donation
-- 6 - Discounts
-- 7 - Fee
-- 8 - Tax
-- 9 - Security Deposit
-- 10 - Group Sales Refund (unearned revenue)
declare @CURRENTDATE date = getdate();
-- Tickets (not including combos)
select
0 ORDERITEMTYPE,
0 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
isnull([EVENT].ID, PROGRAM.ID) as ITEMCATEGORYID,
case
when [EVENT].ID is null then PROGRAM.NAME
else [EVENT].NAME + ' ' + cast([EVENT].STARTDATETIME as nvarchar)
end ITEMCATEGORYDESCRIPTION,
EXT.SALESORDERITEMIZEDITEMID ITEMID,
case
when cast(LI.QUANTITY as integer) = 1 then PRICETYPECODE.[DESCRIPTION]
else cast(cast(LI.QUANTITY as integer) as nvarchar) + ' ' + PRICETYPECODE.[DESCRIPTION]
end SUBORDERITEMDESCRIPTION,
(LI.QUANTITY * LI.UNITVALUE) ORDERITEMTOTAL,
isnull([EVENT].STARTDATETIME, @CURRENTDATE) as ORDERITEMSORTFIELD1,
isnull([EVENT].NAME, PROGRAM.NAME) as ORDERITEMSORTFIELD2,
PRICETYPECODE.[DESCRIPTION] SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
inner join dbo.PRICETYPECODE on PRICETYPECODE.ID = SALESORDERITEMTICKET.PRICETYPECODEID
inner join dbo.PROGRAM on PROGRAM.ID = SALESORDERITEMTICKET.PROGRAMID
left join dbo.[EVENT] on [EVENT].ID = SALESORDERITEMTICKET.EVENTID
left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID is null
union all
-- Ticket discounts (not including combos)
select
0 ORDERITEMTYPE,
5 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
--when discounts are itemized this will have to be DISCOUNT.ID
newid() ITEMCATEGORYID,
case
when [EVENT].ID is null then PROGRAM.NAME
else [EVENT].NAME + ' ' + cast([EVENT].STARTDATETIME as nvarchar)
end as ITEMCATEGORYDESCRIPTION,
EXT.SALESORDERITEMIZEDITEMID ITEMID,
--when discounts are itemized this will have to be DISCOUNT.NAME
' Discounts' SUBORDERITEMDESCRIPTION,
-EXT.DISCOUNTS ORDERITEMTOTAL,
isnull([EVENT].STARTDATETIME, @CURRENTDATE) as ORDERITEMSORTFIELD1,
isnull([EVENT].NAME, PROGRAM.NAME) as ORDERITEMSORTFIELD2,
PRICETYPECODE.[DESCRIPTION] SUBORDERITEMSORTFIELD1,
--when discounts are itemized this will have to be DISCOUNT.NAME
'Discounts' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID is null
and EXT.DISCOUNTS > 0
union all
-- Ticket fees (old refund style - fees are just a money field)
-- Doesn't include fees on combo tickets.
select
0 ORDERITEMTYPE,
6 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
--when fees are itemized this will have to be the FEE.ID
newid() ITEMCATEGORYID,
case
when [EVENT].ID is null then PROGRAM.NAME
else [EVENT].NAME + ' ' + cast([EVENT].STARTDATETIME as nvarchar)
end as ITEMCATEGORYDESCRIPTION,
null ITEMID,
--when fees are itemized this will have to be the FEE.NAME
' Fees' SUBORDERITEMDESCRIPTION,
EXT.FEES ORDERITEMTOTAL,
isnull([EVENT].STARTDATETIME, @CURRENTDATE) as ORDERITEMSORTFIELD1,
isnull([EVENT].NAME, PROGRAM.NAME) as ORDERITEMSORTFIELD2,
PRICETYPECODE.[DESCRIPTION] SUBORDERITEMSORTFIELD1,
--when fees are itemized this will have to be the FEE.NAME
'Fees' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID is null
and EXT.FEES > 0
union all
-- Ticket fees (new refund style - item-level fees are their own line item)
-- Itemized fee refunds have non-null SALESORDERITEMIZEDITEMID and apply to tickets.
-- Doesn't include fees on combo tickets.
select
0 ORDERITEMTYPE,
6 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
SALESORDERITEMFEE.FEEID ITEMCATEGORYID,
case
when [EVENT].ID is null then PROGRAM.NAME
else [EVENT].NAME + ' ' + cast([EVENT].STARTDATETIME as nvarchar)
end as ITEMCATEGORYDESCRIPTION,
SALESORDERITEMTICKETFEE.TICKETID ITEMID, -- Group fees with the tickets they apply to.
case when exists (
select 1
from dbo.FINANCIALTRANSACTIONLINEITEM REFUNDEDTICKET
inner join dbo.CREDITITEM_EXT REFUNDEDTICKET_EXT on REFUNDEDTICKET_EXT.ID = REFUNDEDTICKET.ID
where REFUNDEDTICKET_EXT.SALESORDERITEMIZEDITEMID = SALESORDERITEMTICKETFEE.TICKETID
and REFUNDEDTICKET.FINANCIALTRANSACTIONID = @CREDITID
) then ' ' else '' end + FEE.NAME SUBORDERITEMDESCRIPTION,
LI.BASEAMOUNT ORDERITEMTOTAL,
isnull([EVENT].STARTDATETIME, @CURRENTDATE) as ORDERITEMSORTFIELD1,
isnull([EVENT].NAME, PROGRAM.NAME) as ORDERITEMSORTFIELD2,
PRICETYPECODE.[DESCRIPTION] SUBORDERITEMSORTFIELD1,
FEE.NAME SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMFEE on SALESORDERITEMFEE.ID = EXT.SALESORDERITEMID
inner join dbo.SALESORDERITEMTICKETFEE on SALESORDERITEMTICKETFEE.ID = EXT.SALESORDERITEMIZEDITEMID
inner join dbo.FEE on FEE.ID = SALESORDERITEMFEE.FEEID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEMFEE.SALESORDERITEMID
inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID is null
and EXT.TYPECODE = 3
and EXT.SALESORDERITEMIZEDITEMID is not null
union all
-- Combination tickets
select
1 ORDERITEMTYPE,
1 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
isnull([EVENT].ID, PROGRAM.ID) ITEMCATEGORYID,
[COMBINATION].[NAME] ITEMCATEGORYDESCRIPTION,
EXT.SALESORDERITEMIZEDITEMID ITEMID,
coalesce
(
[EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')'
, PROGRAM.NAME
) SUBORDERITEMDESCRIPTION,
null ORDERITEMTOTAL,
0 ORDERITEMSORTFIELD1,
isnull([EVENT].NAME, PROGRAM.NAME) as ORDERITEMSORTFIELD2,
isnull([EVENT].NAME, PROGRAM.NAME) as SUBORDERITEMSORTFIELD1,
isnull([EVENT].NAME, PROGRAM.NAME) as SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
inner join dbo.COMBINATION on SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = COMBINATION.ID
inner join dbo.COMBINATIONPRICETYPE on COMBINATIONPRICETYPE.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID and COMBINATION.ID = COMBINATIONPRICETYPE.COMBINATIONID
inner join dbo.PROGRAM on PROGRAM.ID = SALESORDERITEMTICKET.PROGRAMID
left join dbo.[EVENT] on [EVENT].ID = SALESORDERITEMTICKET.EVENTID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and SALESORDERITEMTICKET.PRICETYPECODEID in (
select COMBINATIONPRICETYPE.PRICETYPECODEID
from dbo.COMBINATIONPRICETYPE
inner join dbo.SALESORDERITEMTICKETCOMBINATION SOITC on COMBINATIONPRICETYPE.COMBINATIONID = SOITC.COMBINATIONID
inner join dbo.SALESORDERITEMTICKET on COMBINATIONPRICETYPE.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID
where
SOITC.ID = SALESORDERITEMTICKET.ID and
SOITC.TICKETCOMBINATIONID = SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID
)
union all
-- Combination tickets
select
1 ORDERITEMTYPE,
2 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
PRICETYPECODE.ID ITEMCATEGORYID,
COMBINATION.NAME ITEMCATEGORYDESCRIPTION,
EXT.SALESORDERITEMIZEDITEMID ITEMID,
case
when cast(LI.QUANTITY as integer) = 1 then PRICETYPECODE.[DESCRIPTION]
else cast(cast(LI.QUANTITY as integer) as nvarchar) + ' ' + PRICETYPECODE.[DESCRIPTION]
end SUBORDERITEMDESCRIPTION,
LI.QUANTITY * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE(SALESORDERITEM.ID) ORDERITEMTOTAL,
1 ORDERITEMSORTFIELD1,
'1' ORDERITEMSORTFIELD2,
PRICETYPECODE.[DESCRIPTION] SUBORDERITEMSORTFIELD1,
isnull([EVENT].NAME, PROGRAM.NAME) as SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
inner join dbo.COMBINATION on SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = COMBINATION.ID
inner join dbo.COMBINATIONPRICETYPE on COMBINATIONPRICETYPE.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID and COMBINATION.ID = COMBINATIONPRICETYPE.COMBINATIONID
inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and SALESORDERITEM.QUANTITY > 0
and SALESORDERITEMTICKETCOMBINATION.PROGRAMGROUPID in (
select top 1 ID
from dbo.PROGRAMGROUP
where PROGRAMGROUP.COMBINATIONID = SALESORDERITEMTICKETCOMBINATION.COMBINATIONID
)
union all
-- Combo item fees (old-style refund - fees are a money field)
select
1 ORDERITEMTYPE,
2 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
--when fees are itemized this will have to be the FEE.ID
newid() ITEMCATEGORYID,
COMBINATION.NAME ITEMCATEGORYDESCRIPTION,
null ITEMID,
--when fees are itemized this will have to be the FEE.NAME
' Fees' SUBORDERITEMDESCRIPTION,
EXT.FEES ORDERITEMTOTAL,
1 ORDERITEMSORTFIELD1,
'1' ORDERITEMSORTFIELD2,
PRICETYPECODE.[DESCRIPTION] SUBORDERITEMSORTFIELD1,
--when fees are itemized this will have to be the FEE.NAME
'Fees' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
inner join dbo.COMBINATION on SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = COMBINATION.ID
inner join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and EXT.FEES > 0
union all
-- Combo item fees (new-style refund - fees are their own line item)
select
1 ORDERITEMTYPE,
2 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
FEE.ID ITEMCATEGORYID,
COMBINATION.NAME ITEMCATEGORYDESCRIPTION,
null ITEMID,
--when fees are itemized this will have to be the FEE.NAME
' ' + FEE.NAME SUBORDERITEMDESCRIPTION,
LI.BASEAMOUNT ORDERITEMTOTAL,
1 ORDERITEMSORTFIELD1,
'1' ORDERITEMSORTFIELD2,
PRICETYPECODE.[DESCRIPTION] SUBORDERITEMSORTFIELD1,
--when fees are itemized this will have to be the FEE.NAME
FEE.NAME SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMFEE on SALESORDERITEMFEE.ID = EXT.SALESORDERITEMID
inner join dbo.FEE on FEE.ID = SALESORDERITEMFEE.FEEID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
inner join dbo.COMBINATION on SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = COMBINATION.ID
inner join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and EXT.TYPECODE = 3
and EXT.SALESORDERITEMIZEDITEMID is not null
union all
-- Event registrations
select
2 ORDERITEMTYPE,
2 SUBORDERITEMTYPE,
LI.ID as ORDERITEMID,
REGISTRANT.ID ITEMCATEGORYID,
[EVENT].NAME + ' ' +
case
when [EVENT].STARTTIME = '' then
replace(convert(nvarchar, [EVENT].STARTDATE, 107), ',','')
else
cast([EVENT].STARTDATETIME as nvarchar)
end as ITEMCATEGORYDESCRIPTION,
null ITEMID,
'' SUBORDERITEMDESCRIPTION,
LI.UNITVALUE as ORDERITEMTOTAL,
[EVENT].STARTDATETIME ORDERITEMSORTFIELD1,
[EVENT].NAME ORDERITEMSORTFIELD2,
'' SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.CREDITITEMEVENTREGISTRATION on CREDITITEMEVENTREGISTRATION.ID = LI.ID
inner join dbo.REGISTRANT on CREDITITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
inner join dbo.[EVENT] on REGISTRANT.EVENTID = [EVENT].ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
union all
-- Memberships
select
3 ORDERITEMTYPE,
3 SUBORDERITEMTYPE,
LI.ID as ORDERITEMID,
MEMBERSHIPPROGRAM.ID ITEMCATEGORYID,
MEMBERSHIPPROGRAM.NAME ITEMCATEGORYDESCRIPTION,
CREDITITEMMEMBERSHIP.MEMBERSHIPID ITEMID,
case
when cast(LI.QUANTITY as integer) = 1 then MEMBERSHIPLEVEL.NAME
else cast(cast(LI.QUANTITY as integer) as nvarchar) + ' ' + MEMBERSHIPLEVEL.NAME
end SUBORDERITEMDESCRIPTION,
LI.UNITVALUE ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
MEMBERSHIPPROGRAM.NAME ORDERITEMSORTFIELD2,
cast(LI.ID as nvarchar(36)) as SUBORDERITEMSORTFIELD1,
MEMBERSHIPLEVEL.NAME SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.ID = LI.ID
inner join dbo.MEMBERSHIPPROGRAM on CREDITITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
inner join dbo.MEMBERSHIPLEVEL on CREDITITEMMEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
union all
-- Applied tickets to refunded membership
select
3 ORDERITEMTYPE,
5 SUBORDERITEMTYPE,
LI.ID as ORDERITEMID,
SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID ITEMCATEGORYID,
MEMBERSHIPPROGRAM.NAME ITEMCATEGORYDESCRIPTION,
SALESORDERITEMMEMBERSHIP.MEMBERSHIPID ITEMID,
' ' + SALESORDERITEMMEMBERSHIPITEMPROMOTION.PROMOTIONNAME SUBORDERITEMDESCRIPTION,
-SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
MEMBERSHIPPROGRAM.NAME ORDERITEMSORTFIELD2,
cast(LI.ID as nvarchar(36)) SUBORDERITEMSORTFIELD1,
SALESORDERITEMMEMBERSHIPITEMPROMOTION.PROMOTIONNAME SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = EXT.SALESORDERITEMID
inner join dbo.MEMBERSHIPPROGRAM on SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = EXT.SALESORDERITEMID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
union all
-- Merchandise
select distinct
4 ORDERITEMTYPE,
4 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
MPI.ID ITEMCATEGORYID,
MPI.BARCODE + ' ' + MPI.ITEMDETAILS ITEMCATEGORYDESCRIPTION,
EXT.SALESORDERITEMIZEDITEMID ITEMID,
case
when cast(LI.QUANTITY as integer) = 1 then MPI.ITEMDETAILS
else cast(cast(LI.QUANTITY as integer) as nvarchar) + ' ' + MPI.ITEMDETAILS
end SUBORDERITEMDESCRIPTION,
(LI.QUANTITY * LI.UNITVALUE) ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
MPI.ITEMDETAILS SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMMERCHANDISE SOIM on SOIM.ID = EXT.SALESORDERITEMID
inner join dbo.MERCHANDISEPRODUCTINSTANCE MPI on SOIM.MERCHANDISEPRODUCTINSTANCEID = MPI.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
union all
-- Merchandise discounts
select
4 ORDERITEMTYPE,
5 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
--when discounts are itemized this will have to be DISCOUNT.ID
newid() ITEMCATEGORYID,
MPI.BARCODE + ' ' + MPI.ITEMDETAILS ITEMCATEGORYDESCRIPTION,
EXT.SALESORDERITEMIZEDITEMID ITEMID,
--when discounts are itemized this will have to be DISCOUNT.NAME
' Discounts' SUBORDERITEMDESCRIPTION,
-EXT.DISCOUNTS ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
MPI.ITEMDETAILS SUBORDERITEMSORTFIELD1,
--when discounts are itemized this will have to be DISCOUNT.NAME
'Discounts' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = EXT.SALESORDERITEMID
inner join dbo.MERCHANDISEPRODUCTINSTANCE MPI on SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = MPI.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and EXT.DISCOUNTS > 0
union all
-- Donations
select distinct
5 ORDERITEMTYPE,
5 SUBORDERITEMTYPE,
LI.ID ORDERITEMID,
SOID.ID ITEMCATEGORYID,
SOI.[DESCRIPTION] ITEMCATEGORYDESCRIPTION,
null ITEMID,
cast(LI.UNITVALUE as nvarchar) as SUBORDERITEMDESCRIPTION,
LI.UNITVALUE as ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SOID.DESIGNATIONNAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEM SOI on SOI.ID = EXT.SALESORDERITEMID
inner join dbo.SALESORDERITEMDONATION SOID on SOID.ID = SOI.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
union all
-- Order discounts
select
6 ORDERITEMTYPE,
6 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
isnull(DISCOUNT.ID, SALESORDERADJUSTABLEDISCOUNT.ID) ITEMCATEGORYID,
'Discounts' ITEMCATEGORYDESCRIPTION,
null ITEMID,
SALESORDERITEMORDERDISCOUNT.DISCOUNTNAME SUBORDERITEMDESCRIPTION,
-SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SALESORDERITEMORDERDISCOUNT.DISCOUNTNAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
inner join dbo.SALESORDERITEMORDERDISCOUNT on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNT.ID
left join dbo.DISCOUNT on SALESORDERITEMORDERDISCOUNT.ISADJUSTABLEDISCOUNT = 0 and SALESORDERITEMORDERDISCOUNT.DISCOUNTID = DISCOUNT.ID
left join dbo.SALESORDERADJUSTABLEDISCOUNT on SALESORDERITEMORDERDISCOUNT.ISADJUSTABLEDISCOUNT = 1 and SALESORDERITEM.SALESORDERID = SALESORDERADJUSTABLEDISCOUNT.SALESORDERID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
and EXT.TYPECODE = 5
union all
-- Order fees
select
7 ORDERITEMTYPE,
7 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
FEE.ID ITEMCATEGORYID,
'Fees' ITEMCATEGORYDESCRIPTION,
null ITEMID,
FEE.NAME SUBORDERITEMDESCRIPTION,
LI.BASEAMOUNT ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
FEE.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
inner join dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
inner join dbo.FEE on SALESORDERITEMFEE.FEEID = FEE.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID and
SALESORDERITEMFEE.SALESORDERITEMID is null
union all
-- Taxes
select distinct
8 ORDERITEMTYPE,
8 SUBORDERITEMTYPE,
EXT.SALESORDERITEMID as ORDERITEMID,
TAX.ID ITEMCATEGORYID,
'Taxes' ITEMCATEGORYDESCRIPTION,
EXT.SALESORDERITEMIZEDITEMID ITEMID,
TAX.NAME SUBORDERITEMDESCRIPTION,
(LI.QUANTITY * LI.UNITVALUE) as ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
TAX.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.SALESORDERITEMTAX on SALESORDERITEMTAX.TAXITEMID = EXT.SALESORDERITEMID
inner join dbo.TAX on SALESORDERITEMTAX.TAXID = TAX.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID
union all
-- Security deposit
select distinct
9 ORDERITEMTYPE,
9 SUBORDERITEMTYPE,
@CREDITID as ORDERITEMID,
LI.ID as ITEMCATEGORYID,
'Security deposit' ITEMCATEGORYDESCRIPTION,
null ITEMID,
'Security deposit' SUBORDERITEMDESCRIPTION,
LI.UNITVALUE as ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
'' SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
where
LI.FINANCIALTRANSACTIONID = @CREDITID and
EXT.TYPECODE = 12 -- Security Deposit
union all
-- Unearned revenue
select distinct
10 ORDERITEMTYPE,
10 SUBORDERITEMTYPE,
@CREDITID as ORDERITEMID,
LI.ID ITEMCATEGORYID,
'Reservation for' ITEMCATEGORYDESCRIPTION,
null ITEMID,
DISPLAYNAME.NAME SUBORDERITEMDESCRIPTION,
LI.TRANSACTIONAMOUNT ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
'' SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.RESERVATION on EXT.SALESORDERID = RESERVATION.ID
inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) DISPLAYNAME
where
LI.FINANCIALTRANSACTIONID = @CREDITID and
EXT.TYPECODE = 255 -- Unearned revenue
order by ORDERITEMTYPE, ORDERITEMSORTFIELD1, ORDERITEMSORTFIELD2, SUBORDERITEMSORTFIELD1, ITEMID, SUBORDERITEMTYPE, SUBORDERITEMSORTFIELD2;
return 0;