USP_DATAFORMTEMPLATE_VIEW_PAYMENTAUCTIONPURCHASE

The load procedure used by the view dataform template "Payment: Auction Purchase 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.
@ITEMNAME	nvarchar(100)	INOUT	Item
@AUCTIONNAME	nvarchar(100)	INOUT	Auction
@PURCHASEAMOUNT	money	INOUT	Amount due
@PURCHASEDATE	date	INOUT	Purchase date
@DESIGNATION	nvarchar(3000)	INOUT	Designation
@DESCRIPTION	nvarchar(100)	INOUT	Description
@RECEIPTAMOUNT	money	INOUT	Receipt amount
@ITEMVALUE	money	INOUT	Item value
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PAYMENTAUCTIONPURCHASE
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ITEMNAME nvarchar(100) = null output,
                    @AUCTIONNAME nvarchar(100) = null output,
                    @PURCHASEAMOUNT money = null output,
                    @PURCHASEDATE date = null output,
                    @DESIGNATION nvarchar(3000) = null output,
                    @DESCRIPTION nvarchar(100) = null output,
                    @RECEIPTAMOUNT money = null output,
                    @ITEMVALUE money = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output
                )
                as
                    set nocount on;

                    -- be sure to set this, in case the select returns no rows

                    set @DATALOADED = 0;

                    declare @PACKAGEPURCHASEPRICE money;
                    select
                        @PACKAGEPURCHASEPRICE = TRANSACTIONPURCHASEPRICE from dbo.UFN_AUCTIONITEM_GETPURCHASEPRICES_INCURRENCY(@ID);

                    select 
                        @DATALOADED = 1,
                        @ITEMNAME = AUCTIONITEM.NAME,
                        @AUCTIONNAME = EVENT.NAME,
                        @PURCHASEAMOUNT = case
                                            when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEMRESERVATION.ID is not null then  --Reserved item

                                                AUCTIONITEMRESERVATION.PURCHASEAMOUNT
                                            when AUCTIONITEM.TYPECODE = 1 and AUCTIONITEMRESERVATION.ID is not null then  --Reserved package

                                                AUCTIONITEMRESERVATION.PURCHASEAMOUNT
                                            when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEMRESERVATION.ID is null then  --Purchased item

                                                [PURCHASESPLIT].TRANSACTIONAMOUNT
                                            when AUCTIONITEM.TYPECODE = 1 and AUCTIONITEMRESERVATION.ID is null then  --Purchased package

                                                @PACKAGEPURCHASEPRICE
                                            else
                                                0
                                          end,
                        @PURCHASEDATE = case
                                            when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEMRESERVATION.ID is not null then  --Reserved item

                                                AUCTIONITEMRESERVATION.PURCHASEDATE
                                            when AUCTIONITEM.TYPECODE = 1 and AUCTIONITEMRESERVATION.ID is not null then  --Reserved package

                                                AUCTIONITEMRESERVATION.PURCHASEDATE
                                            when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEMRESERVATION.ID is null then  --Purchased item

                                                REVENUE.DATE
                                            when AUCTIONITEM.TYPECODE = 1 and AUCTIONITEMRESERVATION.ID is null then  --Purchased package

                                                REVENUE.DATE
                                            else
                                                0
                                          end,
                        @DESIGNATION = case
                                            when AUCTIONITEM.TYPECODE = 0 then
                                                dbo.UFN_DESIGNATION_GETNAME([DONATIONSPLIT].DESIGNATIONID)
                                            else
                                                dbo.UFN_AUCTIONPACKAGE_GETDESIGNATIONLIST(AUCTIONITEM.ID)
                                       end,
                        @DESCRIPTION = AUCTIONITEM.NAME,
             @RECEIPTAMOUNT = case 
                                            when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEMRESERVATION.ID is not null and (AUCTIONITEMRESERVATION.PURCHASEAMOUNT - AUCTIONITEM.TRANSACTIONVALUE > 0) then 
                                                AUCTIONITEMRESERVATION.PURCHASEAMOUNT - AUCTIONITEM.TRANSACTIONVALUE 
                                            when AUCTIONITEM.TYPECODE = 1 and AUCTIONITEMRESERVATION.ID is not null and (AUCTIONITEMRESERVATION.PURCHASEAMOUNT - [AUCTIONITEMVALUES].TRANSACTIONVALUE > 0) then
                                                AUCTIONITEMRESERVATION.PURCHASEAMOUNT - [AUCTIONITEMVALUES].TRANSACTIONVALUE
                                            when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEMRESERVATION.ID is null and ([PURCHASESPLIT].TRANSACTIONAMOUNT - AUCTIONITEM.TRANSACTIONVALUE > 0) then
                                                [PURCHASESPLIT].TRANSACTIONAMOUNT - AUCTIONITEM.TRANSACTIONVALUE
                                            when AUCTIONITEM.TYPECODE = 1 and AUCTIONITEMRESERVATION.ID is null and (@PACKAGEPURCHASEPRICE - [AUCTIONITEMVALUES].TRANSACTIONVALUE > 0) then
                                                @PACKAGEPURCHASEPRICE - [AUCTIONITEMVALUES].TRANSACTIONVALUE
                                            else 0 
                                         end,
                        @ITEMVALUE = AUCTIONITEM.TRANSACTIONVALUE,
                        @TRANSACTIONCURRENCYID = coalesce(PURCHASESPLIT.TRANSACTIONCURRENCYID,AUCTIONITEM.TRANSACTIONCURRENCYID)
                    from 
                        dbo.AUCTIONITEM
                        left join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                        left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEM.ID = AUCTIONITEMRESERVATION.AUCTIONITEMID
                        left join dbo.EVENT on AUCTIONITEM.EVENTAUCTIONID = EVENT.ID
                        left join dbo.REVENUESPLIT [DONATIONSPLIT] on AUCTIONITEM.REVENUEAUCTIONDONATIONID = [DONATIONSPLIT].REVENUEID
                        left join dbo.REVENUESPLIT [PURCHASESPLIT] on AUCTIONITEMPURCHASE.PURCHASEID = [PURCHASESPLIT].ID
                        left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
                        left join dbo.REVENUE on REVENUE.ID = AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID
                        outer apply dbo.UFN_AUCTIONITEM_GETVALUES_INCURRENCY(AUCTIONITEM.ID) [AUCTIONITEMVALUES]
                    where AUCTIONITEM.ID = @ID

                    return 0;