USP_REVENUE_TRANSACTIONAPPLICATIONHIERARCHY
Transaction types and applications in the system.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DATALOADED | bit | INOUT | |
| @TRANSACTIONTYPECODES | xml | INOUT |
Definition
Copy
CREATE procedure dbo.USP_REVENUE_TRANSACTIONAPPLICATIONHIERARCHY
(
@DATALOADED bit = 0 output,
@TRANSACTIONTYPECODES xml = null output
)
as
set nocount on;
set @DATALOADED = 1;
declare @ENTERPRISE bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046');
declare @FOUNDATIONS bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9d9e2fee-e2e5-46d1-b087-25c538bf3334');
declare @PROGRAMS bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('bb1c17bc-9e0b-4683-b490-ee40d511fa05');
declare @MEMBERSHIP bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('5e696818-62ee-4c90-8c28-b1f52d77176e') ;
if @MEMBERSHIP = 0
set @MEMBERSHIP = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('cdde0301-817a-4118-9c4e-ea4640fb0b1c');
declare @PROSPECT bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0a07aa22-ed88-42b7-8c55-d80df4f4cea2');
declare @BASICDEV bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('42c15648-749e-4859-a56d-3a6474814cc7');
declare @UNDERCONSTRUCTION bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9C06FD0D-0D04-11D2-BC7D-00E0290E1347');
declare @AUCTION bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0321D454-B28B-4FAB-BD58-F2ECC7050594');
select @TRANSACTIONTYPECODES =
(
select [ID], [PARENTID], [APPLICATION], [TRANSACTIONTYPE], [NAME], [SEQUENCE], [SELECTED]
from
(
--Auction donations
select '00000000-0000-0000-0000-000000000028' [ID], NULL [PARENTID], NULL [APPLICATION], 7 [TRANSACTIONTYPE], 'Auction donation' [NAME], 1 [SEQUENCE], 0 [SELECTED] where @AUCTION = 1
union all
select '00000000-0000-0000-0000-000000000029', '00000000-0000-0000-0000-000000000028', 0, 7, 'Donation', 1, 0 where @AUCTION = 1
--Grant awards
union all
select '00000000-0000-0000-0000-000000000025' [ID], NULL [PARENTID], NULL [APPLICATION], 6 [TRANSACTIONTYPE], 'Grant award' [NAME], 3 [SEQUENCE], 0 [SELECTED] where @ENTERPRISE = 1 or @FOUNDATIONS = 1
union all
select '00000000-0000-0000-0000-000000000026', '00000000-0000-0000-0000-000000000025', 8, 6, 'Grant award', 1, 0 where @ENTERPRISE = 1 or @FOUNDATIONS = 1
--Matching gift claims
union all
select '00000000-0000-0000-0000-000000000024' [ID], NULL [PARENTID], NULL [APPLICATION], 3 [TRANSACTIONTYPE], 'Matching gift claim' [NAME], 4 [SEQUENCE], 0 [SELECTED]
union all
select '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000024', 0, 3, 'Donation', 1, 0
--Orders
union all
select '00000000-0000-0000-0000-000000000002', NULL, NULL, 5, 'Order', 6, 0 where @PROGRAMS = 1
union all
select '00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000002', 0, 5, 'Donation', 1, 0 where @PROGRAMS = 1
union all
select '00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000002', 5, 5, 'Membership', 2, 0 where @PROGRAMS = 1 and (@MEMBERSHIP = 1 or @ENTERPRISE = 1)
union all
select '00000000-0000-0000-0000-000000000039', '00000000-0000-0000-0000-000000000002', 18, 5, 'Membership add-on', 3, 0 where @PROGRAMS = 1 and (@MEMBERSHIP = 1 or @ENTERPRISE = 1)
union all
select '00000000-0000-0000-0000-000000000040', '00000000-0000-0000-0000-000000000002', 1, 5, 'Event registration', 4, 0 where @PROGRAMS = 1 and (@BASICDEV = 1 or @ENTERPRISE = 1)
--Payments
union all
select '00000000-0000-0000-0000-000000000005', NULL, NULL, 0, 'Payment', 7, 0
union all
select '00000000-0000-0000-0000-000000000006', '00000000-0000-0000-0000-000000000005', 9, 0, 'Admission', 1, 0 where @PROGRAMS = 1
union all
select '00000000-0000-0000-0000-000000000027', '00000000-0000-0000-0000-000000000005', 12, 0, 'Auction purchase', 2, 0 where @AUCTION = 1
union all
select '00000000-0000-0000-0000-000000000007', '00000000-0000-0000-0000-000000000005', 0, 0, 'Donation', 3, 0
union all
select '00000000-0000-0000-0000-000000000008', '00000000-0000-0000-0000-000000000005', 1, 0, 'Event registration', 4, 0 where @BASICDEV = 1 or @ENTERPRISE = 1
union all
select '00000000-0000-0000-0000-000000000009', '00000000-0000-0000-0000-000000000005', 8, 0, 'Grant', 5, 0 where @ENTERPRISE = 1 or @FOUNDATIONS = 1
union all
select '00000000-0000-0000-0000-000000000010', '00000000-0000-0000-0000-000000000005', 7, 0, 'Matching gift', 6, 0
union all
select '00000000-0000-0000-0000-000000000011', '00000000-0000-0000-0000-000000000005', 5, 0, 'Membership', 7, 0 where @MEMBERSHIP = 1 or @ENTERPRISE = 1
union all
select '00000000-0000-0000-0000-000000000012', '00000000-0000-0000-0000-000000000005', 11, 0, 'Miscellaneous', 10, 0
union all
select '00000000-0000-0000-0000-000000000013', '00000000-0000-0000-0000-000000000005', 10, 0, 'Order', 11, 0 where @PROGRAMS = 1
union all
select '00000000-0000-0000-0000-000000000014', '00000000-0000-0000-0000-000000000005', 4, 0, 'Other', 12, 0
union all
select '00000000-0000-0000-0000-000000000015', '00000000-0000-0000-0000-000000000005', 6, 0, 'Planned gift', 13, 0 where @ENTERPRISE = 1 or @PROSPECT = 1
union all
select '00000000-0000-0000-0000-000000000016', '00000000-0000-0000-0000-000000000005', 2, 0, 'Pledge', 14, 0
union all
select '00000000-0000-0000-0000-000000000017', '00000000-0000-0000-0000-000000000005', 3, 0, 'Recurring gift', 15, 0
union all
select '00000000-0000-0000-0000-000000000032', '00000000-0000-0000-0000-000000000005', 13, 0, 'Donor challenge', 16, 0
union all
select '00000000-0000-0000-0000-000000000033', '00000000-0000-0000-0000-000000000005', 18, 0, 'Membership add-on', 8, 0 where @MEMBERSHIP = 1
union all
select '00000000-0000-0000-0000-000000000038', '00000000-0000-0000-0000-000000000005', 19, 0, 'Membership installment plan', 9, 0 where @ENTERPRISE = 1 and @MEMBERSHIP = 1
--Planned gifts
union all
select '00000000-0000-0000-0000-000000000018', NULL, NULL, 4, 'Planned gift', 8, 0 where @ENTERPRISE = 1 or @PROSPECT = 1
union all
select '00000000-0000-0000-0000-000000000019', '00000000-0000-0000-0000-000000000018', 0, 4, 'Donation', 1, 0 where @ENTERPRISE = 1 or @PROSPECT = 1
--Pledges
union all
select '00000000-0000-0000-0000-000000000020', NULL, NULL, 1, 'Pledge', 9, 0
union all
select '00000000-0000-0000-0000-000000000021', '00000000-0000-0000-0000-000000000020', 0, 1, 'Donation', 1, 0
--Recurring gifts
union all
select '00000000-0000-0000-0000-000000000022', NULL, NULL, 2, 'Recurring gift', 10, 0
union all
select '00000000-0000-0000-0000-000000000023', '00000000-0000-0000-0000-000000000022', 0, 2, 'Donation', 1, 0
union all
select '00000000-0000-0000-0000-000000000037', '00000000-0000-0000-0000-000000000022', 5, 2, 'Membership', 2, 0 where @MEMBERSHIP = 1
--Donor challenge claims
union all
select '00000000-0000-0000-0000-000000000030' [ID], NULL [PARENTID], NULL [APPLICATION], 8 [TRANSACTIONTYPE], 'Donor challenge claim' [NAME], 2 [SEQUENCE], 0 [SELECTED]
union all
select '00000000-0000-0000-0000-000000000031', '00000000-0000-0000-0000-000000000030', 0, 8, 'Donation', 1, 0
--Membership installment plans
union all
select '00000000-0000-0000-0000-000000000034' [ID], NULL [PARENTID], NULL [APPLICATION], 15 [TRANSACTIONTYPE], 'Membership installment plan' [NAME], 5 [SEQUENCE], 0 where @ENTERPRISE = 1 and @MEMBERSHIP = 1
union all
select '00000000-0000-0000-0000-000000000041', '00000000-0000-0000-0000-000000000034', 0, 15, 'Donation', 1, 0 where @ENTERPRISE = 1 and @MEMBERSHIP = 1
union all
select '00000000-0000-0000-0000-000000000035', '00000000-0000-0000-0000-000000000034', 5, 15, 'Membership', 2, 0 where @ENTERPRISE = 1 and @MEMBERSHIP = 1
union all
select '00000000-0000-0000-0000-000000000036', '00000000-0000-0000-0000-000000000034', 18, 15, 'Membership add-on', 3, 0 where @ENTERPRISE = 1 and @MEMBERSHIP = 1
) [HIERARCHY]
for xml raw('ITEM'),type,elements,root('TRANSACTIONTYPECODES'),BINARY BASE64
)
return 0;