USP_DATAFORMTEMPLATE_ADD_GIFTINKINDSALEBYTRANSACTION
The save procedure used by the add dataform template "Gift-in-Kind Sale By Transaction Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @REVENUEID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @SALEDATE | datetime | IN | Date of sale |
| @SALEAMOUNT | money | IN | Sale amount |
| @NUMBEROFUNITS | int | IN | Units sold |
| @SALEPOSTDATE | datetime | IN | GL post date |
| @SALEPOSTSTATUSCODE | tinyint | IN | GL post status |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Base currency ID |
| @EXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_GIFTINKINDSALEBYTRANSACTION
(
@ID uniqueidentifier = null output,
@REVENUEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@SALEDATE datetime,
@SALEAMOUNT money,
@NUMBEROFUNITS integer,
@SALEPOSTDATE datetime = null,
@SALEPOSTSTATUSCODE tinyint = 1,
@BASEEXCHANGERATEID uniqueidentifier = null,
@EXCHANGERATE decimal(20,8) = null,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
-- Check GL business rule for this account system and set to 'Do not post' if needed.
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
from dbo.FINANCIALTRANSACTION
where ID = @REVENUEID;
declare @ALLOWGLDISTRIBUTIONS bit;
set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
if @ALLOWGLDISTRIBUTIONS = 0
begin
set @SALEPOSTSTATUSCODE = 2 -- Do not post
set @SALEPOSTDATE = null
end
-- ****
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENTID @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @ID is null
set @ID = newid();
-- The ID for GIFTINKINDPAYMENTMETHODDETAIL is the same as REVENUEPAYMENTMETHOD's ID.
declare @GIFTINKINDPAYMENTMETHODDETAILID uniqueidentifier;
select top 1
@GIFTINKINDPAYMENTMETHODDETAILID = ID
from dbo.REVENUEPAYMENTMETHOD
where REVENUEID = @REVENUEID;
--Don't allow a sale before the revenue date.
if @SALEDATE < (select DATE from dbo.REVENUE where ID = @REVENUEID)
begin
raiserror('BBERR_SALEDATEBEFOREREVENUEDATE', 13, 1);
return 1;
end
--Don't allow a sale to post before the revenue date.
if @SALEPOSTDATE < (select DATE from dbo.REVENUE where ID = @REVENUEID)
begin
raiserror('BBERR_SALEPOSTDATEBEFOREREVENUEDATE', 13, 1);
return 1;
end
-- Raise error if the transaction is "do not post" but the stock sale isn't "do not post".
if @SALEPOSTSTATUSCODE <> 2 and (select top 1 DONOTPOST from dbo.REVENUE where ID = @REVENUEID) = 1
begin
raiserror('BBERR_TRANSACTIONDONOTPOSTSALEPOST', 13, 1);
return 1;
end
begin try
declare
@ORGANIZATIONSALEAMOUNT money,
@BASESALEAMOUNT money,
@BASECURRENCYID uniqueidentifier,
@ORGANIZATIONEXCHANGERATEID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier;
select
@TRANSACTIONCURRENCYID = GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONCURRENCYID,
@BASECURRENCYID = GIFTINKINDPAYMENTMETHODDETAIL.BASECURRENCYID
from
dbo.REVENUEPAYMENTMETHOD
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
where
REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID;
if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
begin
set @BASEEXCHANGERATEID = newid();
insert into dbo.CURRENCYEXCHANGERATE
(
ID,
FROMCURRENCYID,
TOCURRENCYID,
RATE,
ASOFDATE,
TYPECODE,
SOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@EXCHANGERATE,
@SALEDATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
exec dbo.USP_CURRENCY_GETCURRENCYVALUES
@SALEAMOUNT,
@SALEDATE,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@BASESALEAMOUNT output,
@ORGANIZATIONCURRENCYID output,
@ORGANIZATIONSALEAMOUNT output,
@ORGANIZATIONEXCHANGERATEID output,
1; --Get an organization currency and exchange rate on the first call
if isnull(@NUMBEROFUNITS, 0) <= 0
raiserror ('CK_GIFTINKINDSALE_NUMBEROFUNITSPOSITIVE', 16, 1);
if isnull(@NUMBEROFUNITS, 0) > dbo.UFN_GIFTINKINDPAYMENTMETHODDETAIL_REMAININGUNITS(@GIFTINKINDPAYMENTMETHODDETAILID,@ID)
raiserror ('CK_GIFTINKINDSALE_NUMBEROFUNITSVALID2', 16, 1);
if not (isnull(@SALEPOSTSTATUSCODE,1) = 2 or @SALEPOSTDATE is not null)
raiserror ('CK_GIFTINKINDSALE_POSTDATE_REQUIRED', 16, 1);
if not (isnull(@BASESALEAMOUNT, 0) >= 0)
raiserror ('CK_GIFTINKINDSALE_SALEAMOUNTPOSITIVE', 16, 1);
insert into dbo.FINANCIALTRANSACTION (
ID
,PARENTID
,DATE
,POSTSTATUSCODE
,POSTDATE
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,TRANSACTIONCURRENCYID
,BASEEXCHANGERATEID
,ORGEXCHANGERATEID
,PDACCOUNTSYSTEMID
,TYPECODE
,DESCRIPTION
,DELETEDON
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values (
@ID
,@REVENUEID
,@SALEDATE
,case isnull(@SALEPOSTSTATUSCODE, 1) when 0 then 2 when 1 then 1 when 2 then 3 end
,@SALEPOSTDATE
,@SALEAMOUNT
,@BASESALEAMOUNT
,@ORGANIZATIONSALEAMOUNT
,@TRANSACTIONCURRENCYID
,@BASEEXCHANGERATEID
,@ORGANIZATIONEXCHANGERATEID
,@PDACCOUNTSYSTEMID
,27
,''
,null
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
insert into dbo.GIFTINKINDSALE_EXT (
ID
,GIFTINKINDPAYMENTMETHODDETAILID
,SALEDATE
,NUMBEROFUNITS
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values (
@ID
,@GIFTINKINDPAYMENTMETHODDETAILID
,@SALEDATE
,@NUMBEROFUNITS
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
-- Add new stock detail GL distributions
if @SALEPOSTSTATUSCODE <> 2
exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE, @ID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;