UFN_SALESORDER_GETAPPLICATIONSFORPAYMENT
Returns the applications for a sales order payment, pro-rated to the given amount.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESORDERID | uniqueidentifier | IN | |
| @AMOUNT | money | IN |
Definition
Copy
CREATE function dbo.UFN_SALESORDER_GETAPPLICATIONSFORPAYMENT
(
@SALESORDERID uniqueidentifier,
@AMOUNT money -- Not used
)
returns @PAYMENTSPLITS table
(
ID uniqueidentifier,
AMOUNT money,
APPLICATIONCODE tinyint,
TYPECODE tinyint,
PROGRAMID uniqueidentifier,
EVENTID uniqueidentifier,
FEEID uniqueidentifier,
TAXID uniqueidentifier,
SALESORDERITEMID uniqueidentifier,
RESOURCEID uniqueidentifier,
VOLUNTEERTYPEID uniqueidentifier,
EVENTLOCATIONID uniqueidentifier,
MERCHANDISEPRODUCTINSTANCEID uniqueidentifier
)
as
begin
insert into @PAYMENTSPLITS
select coalesce(SALESORDERITEMTICKET.EVENTID, SALESORDERITEMTICKET.PROGRAMID),
sum(SALESORDERITEM.TOTAL),
9,
5,
SALESORDERITEMTICKET.PROGRAMID,
SALESORDERITEMTICKET.EVENTID as EVENTID,
null as FEEID,
null as TAXID,
null as SALESORDERITEMID,
null as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEMTICKET
inner join dbo.SALESORDERITEM on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
where SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 0 -- Ticket
group by SALESORDERITEMTICKET.EVENTID, SALESORDERITEMTICKET.PROGRAMID
union all
select SALESORDERITEM.ID,
SALESORDERITEM.TOTAL as AMOUNT,
0,
0,
null as PROGRAMID,
null as EVENTID,
null as FEEID,
null as TAXID,
SALESORDERITEM.ID as SALESORDERITEMID,
null as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEM
where SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 2 -- Donation
union all
select SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
5,
2,
null as PROGRAMID,
null as EVENTID,
null as FEEID,
null as TAXID,
SALESORDERITEM.ID as SALESORDERITEMID,
null as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEM
where SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 1 -- Membership
union all
select SALESORDERITEMFEE.FEEID,
sum(SALESORDERITEM.TOTAL),
9,
6,
null,
null as EVENTID,
SALESORDERITEMFEE.FEEID,
null,
null as SALESORDERITEMID,
null as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEMFEE
inner join dbo.SALESORDERITEM on SALESORDERITEMFEE.ID = SALESORDERITEM.ID
where SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 3 -- Fee
group by SALESORDERITEMFEE.FEEID
union all
select SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
9,
7,
null,
null as EVENTID,
null,
(select top 1 SALESORDERITEMTAX.TAXID from SALESORDERITEMTAX
where SALESORDERITEMTAX.TAXITEMID = SALESORDERITEM.ID) as TAXID,
null as SALESORDERITEMID,
null as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEM
where SALESORDERITEM.SALESORDERID = @SALESORDERID
and SALESORDERITEM.TYPECODE = 4 -- Tax
union all
select SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
1,
1,
null,
null as EVENTID,
null,
null,
SALESORDERITEM.ID as SALESORDERITEMID,
null as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEM
where
SALESORDERITEM.SALESORDERID = @SALESORDERID
and SALESORDERITEM.TOTAL > 0
and SALESORDERITEM.TYPECODE = 6 -- Event Registration
union all
select SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
9, /* Application type: Ticket */
10, /* Type code: Supply/Equipment resource */
null,
null as EVENTID,
null,
null,
SALESORDERITEM.ID as SALESORDERITEMID,
ITINERARYRESOURCE.RESOURCEID as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMITINERARYRESOURCE
on SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.ITINERARYRESOURCE
on ITINERARYRESOURCE.ID = SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID
where SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 8 -- Itinerary Supply/Equipment Resource
union all
select SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
9, /* Application type: Ticket */
10, /* Type code: Supply/Equipment resource */
null,
null as EVENTID,
null,
null,
SALESORDERITEM.ID as SALESORDERITEMID,
ITINERARYITEMRESOURCE.RESOURCEID as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE
on SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.ITINERARYITEMRESOURCE
on ITINERARYITEMRESOURCE.ID = SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID
where SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 9 -- Itinerary Item Supply/Equipment Resource
union all
select SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
9, /* Application type: Ticket */
11, /* Type code: Staffing resource */
null,
null as EVENTID,
null,
null,
SALESORDERITEM.ID as SALESORDERITEMID,
null as RESOURCEID,
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE
on SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.ITINERARYSTAFFRESOURCE
on ITINERARYSTAFFRESOURCE.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID
where SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 10 -- Itinerary Staffing Resource
union all
select SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
9, /* Application type: Ticket */
11, /* Type code: Staffing resource */
null,
null as EVENTID,
null,
null,
SALESORDERITEM.ID as SALESORDERITEMID,
null as RESOURCEID,
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE
on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.ITINERARYITEMSTAFFRESOURCE
on ITINERARYITEMSTAFFRESOURCE.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID
where SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 11 -- Itinerary Item Staffing Resource
union all
select
SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
9, -- Application type: Ticket
14, -- Type code: Facility
null as PROGRAMID,
null as EVENTID,
null as FEEID,
null as TAXID,
SALESORDERITEM.ID as SALESORDERITEMID,
null as RESOURCEID,
null as VOLUNTEERTYPEID,
SALESORDERITEMFACILITY.EVENTLOCATIONID,
null as MERCHANDISEPRODUCTINSTANCEID
from
dbo.SALESORDERITEM
inner join
dbo.SALESORDERITEMFACILITY on SALESORDERITEMFACILITY.ID = SALESORDERITEM.ID
where
SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 7 -- Facility
union all
select
SALESORDERITEM.ID,
SALESORDERITEM.TOTAL,
16, -- Application type: Merchandise
16, -- Type code: Merchandise
null as PROGRAMID,
null as EVENTID,
null as FEEID,
null as TAXID,
SALESORDERITEM.ID as SALESORDERITEMID,
null as RESOURCEID,
null as VOLUNTEERTYPEID,
null as EVENTLOCATIONID,
SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID as MERCHANDISEPRODUCTINSTANCEID
from
dbo.SALESORDERITEM
inner join
dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
where
SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TYPECODE = 14 -- Merchandise
--Add additional sales order items here
return;
end