USP_FINANCIALTRANSACTIOLINEITEM_EDIT_IMPLEMENTATION
Insert, update, and delete line items for a financial transaction
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @FINANCIALTRANSACTIONID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @LINEITEMS | xml | IN | |
| @UPDATEDISTRIBUTION | tinyint | IN | |
| @MULTICURRENCY | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_FINANCIALTRANSACTIOLINEITEM_EDIT_IMPLEMENTATION
(
@FINANCIALTRANSACTIONID uniqueidentifier
,@CHANGEAGENTID uniqueidentifier = null
,@LINEITEMS xml
,@UPDATEDISTRIBUTION tinyint
,@MULTICURRENCY tinyint = 0
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
select @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID from dbo.FINANCIALTRANSACTION where ID = @FINANCIALTRANSACTIONID;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin try
declare @temp table (
ID uniqueidentifier NOT NULL
,FINANCIALTRANSACTIONID uniqueidentifier NOT NULL
,AMOUNT money NOT NULL
,BASEAMOUNT money NOT NULL
,ORGAMOUNT money NOT NULL
,DESCRIPTION nvarchar(100) NULL
,SEQUENCE integer NOT NULL
,TYPECODE tinyint NULL
,DELETED bit NULL
,REVERSEDLINEITEMID uniqueidentifier NULL
,POSTDATE datetime NULL
,POSTSTATUSCODE tinyint NOT NULL
,GLENTRIES xml
);
insert into @temp (
ID
,FINANCIALTRANSACTIONID
,AMOUNT
,BASEAMOUNT
,ORGAMOUNT
,DESCRIPTION
,SEQUENCE
,TYPECODE
,DELETED
,REVERSEDLINEITEMID
,POSTDATE
,POSTSTATUSCODE
,GLENTRIES)
select
isnull(FTLI.ID, NEWID())
,@FINANCIALTRANSACTIONID
,FTLI.AMOUNT
,FTLI.BASEAMOUNT
,FTLI.ORGAMOUNT
,FTLI.DESCRIPTION
,FTLI.SEQUENCE
,isnull(FTLI.TYPECODE,0)
,DELETED
,REVERSEDLINEITEMID
,isnull(FTLI.POSTDATE, FT.POSTDATE) --TODO: Postdate and poststatus will need to be changed to the lineitems poststatus/postdate
,isnull(FTLI.POSTSTATUSCODE,FT.POSTSTATUSCODE) --TODO: Postdate and poststatus will need to be changed to the lineitems poststatus/postdate
,FTLI.GLENTRIES
from dbo.UFN_FINANCIALTRANSACTION_LINEITEMS_FROMITEMLISTXML(@LINEITEMS,@MULTICURRENCY) FTLI
inner join dbo.FINANCIALTRANSACTION FT on @FINANCIALTRANSACTIONID = FT.ID
--Cache current context information
declare @CONTEXTCACHE varbinary(128);
set @CONTEXTCACHE = CONTEXT_INFO();
set CONTEXT_INFO @CHANGEAGENTID;
merge dbo.FINANCIALTRANSACTIONLINEITEM as target
using (select
ID
,FINANCIALTRANSACTIONID
,AMOUNT
,BASEAMOUNT
,ORGAMOUNT
,DESCRIPTION
,SEQUENCE
,TYPECODE
,DELETED
,REVERSEDLINEITEMID
,POSTDATE
,POSTSTATUSCODE
from @temp) as source (
ID
,FINANCIALTRANSACTIONID
,AMOUNT
,BASEAMOUNT
,ORGAMOUNT
,DESCRIPTION
,SEQUENCE
,TYPECODE
,DELETED
,REVERSEDLINEITEMID
,POSTDATE
,POSTSTATUSCODE)
on (target.ID = source.ID and target.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID and target.VISIBLE = 1)
when not matched by source and target.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID and target.VISIBLE = 1 then DELETE
when matched then
update set
ID = source.ID
,FINANCIALTRANSACTIONID = source.FINANCIALTRANSACTIONID
,TRANSACTIONAMOUNT = source.AMOUNT
,BASEAMOUNT = source.BASEAMOUNT
,ORGAMOUNT = source.ORGAMOUNT
,DESCRIPTION = source.DESCRIPTION
,SEQUENCE = source.SEQUENCE
,TYPECODE = source.TYPECODE
,DELETEDON = case when isnull(source.DELETED,0)=0 then NULL else @CURRENTDATE end
,REVERSEDLINEITEMID = source.REVERSEDLINEITEMID
,POSTDATE = source.POSTDATE
,POSTSTATUSCODE = source.POSTSTATUSCODE
,CHANGEDBYID = @CHANGEAGENTID
,DATECHANGED = @CURRENTDATE
when not matched then
insert (
ID
,FINANCIALTRANSACTIONID
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,DESCRIPTION
,SEQUENCE
,TYPECODE
,DELETEDON
,REVERSEDLINEITEMID
,POSTDATE
,POSTSTATUSCODE
,ADDEDBYID
,DATEADDED
,CHANGEDBYID
,DATECHANGED)
values (
source.ID
,source.FINANCIALTRANSACTIONID
,source.AMOUNT
,source.BASEAMOUNT
,source.ORGAMOUNT
,source.DESCRIPTION
,source.SEQUENCE
,source.TYPECODE
,case when isnull(source.DELETED,0)=0 then NULL else @CURRENTDATE end
,source.REVERSEDLINEITEMID
,source.POSTDATE
,source.POSTSTATUSCODE
,@CHANGEAGENTID
,@CURRENTDATE
,@CHANGEAGENTID
,@CURRENTDATE);
-- Update JournalEntries
if @UPDATEDISTRIBUTION = 1
begin
declare @tempJE table (
FINANCIALTRANSACTIONLINEITEMID uniqueidentifier NOT NULL,
JOURNALENTRYID uniqueidentifier NOT NULL,
AMOUNT money NULL,
BASEAMOUNT money NULL,
ORGAMOUNT money NULL,
CLASSCODE tinyint NULL,
COMMENT nvarchar(60) NULL,
JOURNALCODEID uniqueidentifier NULL,
PERCENTAGE numeric(20,4) NULL,
SEQUENCE int NOT NULL,
TRANSACTIONTYPECODE tinyint NULL,
SUBLEDGERTYPECODE tinyint NULL,
POSTDATE date null,
DATAELEMENT1ID uniqueidentifier null,
DATAELEMENT2ID uniqueidentifier null,
DATAELEMENT3ID uniqueidentifier null,
DATAELEMENT4ID uniqueidentifier null,
DATAELEMENT5ID uniqueidentifier null,
DATAELEMENT6ID uniqueidentifier null,
DATAELEMENT7ID uniqueidentifier null,
DATAELEMENT8ID uniqueidentifier null,
DATAELEMENT9ID uniqueidentifier null,
DATAELEMENT10ID uniqueidentifier null,
DATAELEMENT11ID uniqueidentifier null,
DATAELEMENT12ID uniqueidentifier null,
DATAELEMENT13ID uniqueidentifier null,
DATAELEMENT14ID uniqueidentifier null,
DATAELEMENT15ID uniqueidentifier null,
DATAELEMENT16ID uniqueidentifier null,
DATAELEMENT17ID uniqueidentifier null,
DATAELEMENT18ID uniqueidentifier null,
DATAELEMENT19ID uniqueidentifier null,
DATAELEMENT20ID uniqueidentifier null,
DATAELEMENT21ID uniqueidentifier null,
DATAELEMENT22ID uniqueidentifier null,
DATAELEMENT23ID uniqueidentifier null,
DATAELEMENT24ID uniqueidentifier null,
DATAELEMENT25ID uniqueidentifier null,
DATAELEMENT26ID uniqueidentifier null,
DATAELEMENT27ID uniqueidentifier null,
DATAELEMENT28ID uniqueidentifier null,
DATAELEMENT29ID uniqueidentifier null,
DATAELEMENT30ID uniqueidentifier null,
ANNOTATIONXML xml null);
insert into @tempJE (
JOURNALENTRYID,
FINANCIALTRANSACTIONLINEITEMID,
AMOUNT,
ORGAMOUNT,
BASEAMOUNT,
CLASSCODE,
COMMENT,
JOURNALCODEID,
PERCENTAGE,
SEQUENCE,
POSTDATE,
TRANSACTIONTYPECODE,
SUBLEDGERTYPECODE,
DATAELEMENT1ID,
DATAELEMENT2ID,
DATAELEMENT3ID,
DATAELEMENT4ID,
DATAELEMENT5ID,
DATAELEMENT6ID,
DATAELEMENT7ID,
DATAELEMENT8ID,
DATAELEMENT9ID,
DATAELEMENT10ID,
DATAELEMENT11ID,
DATAELEMENT12ID,
DATAELEMENT13ID,
DATAELEMENT14ID,
DATAELEMENT15ID,
DATAELEMENT16ID,
DATAELEMENT17ID,
DATAELEMENT18ID,
DATAELEMENT19ID,
DATAELEMENT20ID,
DATAELEMENT21ID,
DATAELEMENT22ID,
DATAELEMENT23ID,
DATAELEMENT24ID,
DATAELEMENT25ID,
DATAELEMENT26ID,
DATAELEMENT27ID,
DATAELEMENT28ID,
DATAELEMENT29ID,
DATAELEMENT30ID,
ANNOTATIONXML)
select
CASE WHEN coalesce(GL.JOURNALENTRYID,'00000000-0000-0000-0000-000000000000')='00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE GL.JOURNALENTRYID END JOURNALENTRYID
,T.ID
,abs(GL.AMOUNT) -- Amount is always positive.
,abs(GL.BASEAMOUNT)
,abs(GL.ORGAMOUNT)
,GL.CLASSCODE
,GL.COMMENT
,GL.JOURNALCODEID
,GL.PERCENTAGE
,GL.SEQUENCE
,GL.POSTDATE
,case when GL.AMOUNT>=0 then GL.TRANSACTIONTYPECODE else (-GL.TRANSACTIONTYPECODE)+1 end -- Negative amounts get to switch their transaction type code
,GL.TRANSACTIONTYPECODE as SUBLEDGERTYPECODE
,GL.DATAELEMENT1ID
,GL.DATAELEMENT2ID
,GL.DATAELEMENT3ID
,GL.DATAELEMENT4ID
,GL.DATAELEMENT5ID
,GL.DATAELEMENT6ID
,GL.DATAELEMENT7ID
,GL.DATAELEMENT8ID
,GL.DATAELEMENT9ID
,GL.DATAELEMENT10ID
,GL.DATAELEMENT11ID
,GL.DATAELEMENT12ID
,GL.DATAELEMENT13ID
,GL.DATAELEMENT14ID
,GL.DATAELEMENT15ID
,GL.DATAELEMENT16ID
,GL.DATAELEMENT17ID
,GL.DATAELEMENT18ID
,GL.DATAELEMENT19ID
,GL.DATAELEMENT20ID
,GL.DATAELEMENT21ID
,GL.DATAELEMENT22ID
,GL.DATAELEMENT23ID
,GL.DATAELEMENT24ID
,GL.DATAELEMENT25ID
,GL.DATAELEMENT26ID
,GL.DATAELEMENT27ID
,GL.DATAELEMENT28ID
,GL.DATAELEMENT29ID
,GL.DATAELEMENT30ID
,GL.ANNOTATIONS
from
@temp as T
cross apply dbo.UFN_FINANCIALTRANSACTION_LINEITEM_GL_FROMITEMLISTXML_MC(T.GLENTRIES,@MULTICURRENCY,T.BASEAMOUNT,T.ORGAMOUNT) as GL
merge dbo.JOURNALENTRY as target
using (
select
GL.JOURNALENTRYID as ID
,GL.FINANCIALTRANSACTIONLINEITEMID
,GL.AMOUNT
,GL.BASEAMOUNT
,GL.ORGAMOUNT
,GL.CLASSCODE
,GL.COMMENT
,GL.JOURNALCODEID
,GL.PERCENTAGE
,GL.SEQUENCE
,GL.POSTDATE
,GL.TRANSACTIONTYPECODE
,GL.SUBLEDGERTYPECODE
,GL.DATAELEMENT1ID
,GL.DATAELEMENT2ID
,GL.DATAELEMENT3ID
,GL.DATAELEMENT4ID
,GL.DATAELEMENT5ID
,GL.DATAELEMENT6ID
,GL.DATAELEMENT7ID
,GL.DATAELEMENT8ID
,GL.DATAELEMENT9ID
,GL.DATAELEMENT10ID
,GL.DATAELEMENT11ID
,GL.DATAELEMENT12ID
,GL.DATAELEMENT13ID
,GL.DATAELEMENT14ID
,GL.DATAELEMENT15ID
,GL.DATAELEMENT16ID
,GL.DATAELEMENT17ID
,GL.DATAELEMENT18ID
,GL.DATAELEMENT19ID
,GL.DATAELEMENT20ID
,GL.DATAELEMENT21ID
,GL.DATAELEMENT22ID
,GL.DATAELEMENT23ID
,GL.DATAELEMENT24ID
,GL.DATAELEMENT25ID
,GL.DATAELEMENT26ID
,GL.DATAELEMENT27ID
,GL.DATAELEMENT28ID
,GL.DATAELEMENT29ID
,GL.DATAELEMENT30ID
from
@tempJE as GL
) as source
on ( target.ID = source.ID )
when not matched by source and target.FINANCIALTRANSACTIONLINEITEMID in (select ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID) then DELETE
when matched then
update set
TRANSACTIONAMOUNT = source.AMOUNT
,BASEAMOUNT = source.BASEAMOUNT
,ORGAMOUNT = source.ORGAMOUNT
,CLASSCODE = source.CLASSCODE
,COMMENT = source.COMMENT
,JOURNALCODEID = source.JOURNALCODEID
,PERCENTAGE = source.PERCENTAGE
,SEQUENCE = source.SEQUENCE
,POSTDATE = source.POSTDATE
,TRANSACTIONTYPECODE = source.TRANSACTIONTYPECODE
,SUBLEDGERTYPECODE = source.SUBLEDGERTYPECODE
,DATAELEMENT1ID = source.DATAELEMENT1ID
,DATAELEMENT2ID = source.DATAELEMENT2ID
,DATAELEMENT3ID = source.DATAELEMENT3ID
,DATAELEMENT4ID = source.DATAELEMENT4ID
,DATAELEMENT5ID = source.DATAELEMENT5ID
,DATAELEMENT6ID = source.DATAELEMENT6ID
,DATAELEMENT7ID = source.DATAELEMENT7ID
,DATAELEMENT8ID = source.DATAELEMENT8ID
,DATAELEMENT9ID = source.DATAELEMENT9ID
,DATAELEMENT10ID = source.DATAELEMENT10ID
,DATAELEMENT11ID = source.DATAELEMENT11ID
,DATAELEMENT12ID = source.DATAELEMENT12ID
,DATAELEMENT13ID = source.DATAELEMENT13ID
,DATAELEMENT14ID = source.DATAELEMENT14ID
,DATAELEMENT15ID = source.DATAELEMENT15ID
,DATAELEMENT16ID = source.DATAELEMENT16ID
,DATAELEMENT17ID = source.DATAELEMENT17ID
,DATAELEMENT18ID = source.DATAELEMENT18ID
,DATAELEMENT19ID = source.DATAELEMENT19ID
,DATAELEMENT20ID = source.DATAELEMENT20ID
,DATAELEMENT21ID = source.DATAELEMENT21ID
,DATAELEMENT22ID = source.DATAELEMENT22ID
,DATAELEMENT23ID = source.DATAELEMENT23ID
,DATAELEMENT24ID = source.DATAELEMENT24ID
,DATAELEMENT25ID = source.DATAELEMENT25ID
,DATAELEMENT26ID = source.DATAELEMENT26ID
,DATAELEMENT27ID = source.DATAELEMENT27ID
,DATAELEMENT28ID = source.DATAELEMENT28ID
,DATAELEMENT29ID = source.DATAELEMENT29ID
,DATAELEMENT30ID = source.DATAELEMENT30ID
,TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
-- Boilerplate
,CHANGEDBYID = @CHANGEAGENTID
,DATECHANGED = @CURRENTDATE
when not matched then
insert (
ID
,FINANCIALTRANSACTIONLINEITEMID
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,CLASSCODE
,COMMENT
,JOURNALCODEID
,PERCENTAGE
,SEQUENCE
,POSTDATE
,TRANSACTIONTYPECODE
,SUBLEDGERTYPECODE
,DATAELEMENT1ID
,DATAELEMENT2ID
,DATAELEMENT3ID
,DATAELEMENT4ID
,DATAELEMENT5ID
,DATAELEMENT6ID
,DATAELEMENT7ID
,DATAELEMENT8ID
,DATAELEMENT9ID
,DATAELEMENT10ID
,DATAELEMENT11ID
,DATAELEMENT12ID
,DATAELEMENT13ID
,DATAELEMENT14ID
,DATAELEMENT15ID
,DATAELEMENT16ID
,DATAELEMENT17ID
,DATAELEMENT18ID
,DATAELEMENT19ID
,DATAELEMENT20ID
,DATAELEMENT21ID
,DATAELEMENT22ID
,DATAELEMENT23ID
,DATAELEMENT24ID
,DATAELEMENT25ID
,DATAELEMENT26ID
,DATAELEMENT27ID
,DATAELEMENT28ID
,DATAELEMENT29ID
,DATAELEMENT30ID
,TRANSACTIONCURRENCYID
-- boilerplate
,ADDEDBYID
,DATEADDED
,CHANGEDBYID
,DATECHANGED
)
values (
COALESCE(source.ID, newid())
,source.FINANCIALTRANSACTIONLINEITEMID
,source.AMOUNT
,source.BASEAMOUNT
,source.ORGAMOUNT
,source.CLASSCODE
,source.COMMENT
,source.JOURNALCODEID
,source.PERCENTAGE
,source.SEQUENCE
,source.POSTDATE
,source.TRANSACTIONTYPECODE
,source.SUBLEDGERTYPECODE
,source.DATAELEMENT1ID
,source.DATAELEMENT2ID
,source.DATAELEMENT3ID
,source.DATAELEMENT4ID
,source.DATAELEMENT5ID
,source.DATAELEMENT6ID
,source.DATAELEMENT7ID
,source.DATAELEMENT8ID
,source.DATAELEMENT9ID
,source.DATAELEMENT10ID
,source.DATAELEMENT11ID
,source.DATAELEMENT12ID
,source.DATAELEMENT13ID
,source.DATAELEMENT14ID
,source.DATAELEMENT15ID
,source.DATAELEMENT16ID
,source.DATAELEMENT17ID
,source.DATAELEMENT18ID
,source.DATAELEMENT19ID
,source.DATAELEMENT20ID
,source.DATAELEMENT21ID
,source.DATAELEMENT22ID
,source.DATAELEMENT23ID
,source.DATAELEMENT24ID
,source.DATAELEMENT25ID
,source.DATAELEMENT26ID
,source.DATAELEMENT27ID
,source.DATAELEMENT28ID
,source.DATAELEMENT29ID
,source.DATAELEMENT30ID
,@TRANSACTIONCURRENCYID
-- boilerplate
,@CHANGEAGENTID
,@CURRENTDATE
,@CHANGEAGENTID
,@CURRENTDATE
);
--- Save annotations!
--the following use of a temp table is just a work-around for a bug in SQL Server 2008
--http://connect.microsoft.com/SQLServer/feedback/details/321459/mergesql-incorrectly-tries-to-insert-nulls
declare @temp2 table (
JOURNALENTRYID uniqueidentifier NOT NULL,
ID uniqueidentifier NOT NULL,
ANNOTATIONCATEGORYCODE tinyint NOT NULL,
REFERENCENAME nvarchar(255) NULL,
SEQUENCE int NOT NULL,
VALIDATIONCODE int NOT NULL,
[TEXT] nvarchar(4000) null,
APPUSERID uniqueidentifier null,
DATEADDED datetime not null)
insert into @temp2
(JOURNALENTRYID,ID,ANNOTATIONCATEGORYCODE,REFERENCENAME,SEQUENCE ,VALIDATIONCODE,[TEXT], APPUSERID, [DATEADDED])
select
T1.JOURNALENTRYID,
CASE WHEN coalesce(T2.ID,'00000000-0000-0000-0000-000000000000')='00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE T2.ID END ID,
T2.ANNOTATIONCATEGORYCODE,
T2.REFERENCENAME,
T2.SEQUENCE,
T2.VALIDATIONCODE,
T2.[TEXT],
T2.[APPUSERID],
T2.[DATEADDED]
from @tempJE T1 CROSS APPLY dbo.UFN_JOURNALENTRYANNOTATIONS_FROMITEMLISTXML(T1.ANNOTATIONXML) T2
merge dbo.JOURNALENTRYANNOTATION as target
using (select * from @temp2) as source
on (target.JOURNALENTRYID=source.JOURNALENTRYID AND target.ID=source.ID)
when not matched by source and
(EXISTS (
SELECT *
FROM dbo.JOURNALENTRY as JE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
WHERE JE.ID=target.JOURNALENTRYID AND LI.FINANCIALTRANSACTIONID=@FINANCIALTRANSACTIONID)
) then
delete
when matched then
update SET
ANNOTATIONCATEGORYCODE=source.ANNOTATIONCATEGORYCODE,
REFERENCENAME=source.REFERENCENAME,
SEQUENCE=source.SEQUENCE,
VALIDATIONCODE=source.VALIDATIONCODE,
[TEXT]=source.[TEXT],
CHANGEDBYID=@CHANGEAGENTID,
DATECHANGED=@CURRENTDATE
when not matched then
insert (
ID
,JOURNALENTRYID
,ANNOTATIONCATEGORYCODE
,REFERENCENAME
,SEQUENCE
,VALIDATIONCODE
,[TEXT]
,APPUSERID
,DATEADDED
,DATECHANGED
,ADDEDBYID
,CHANGEDBYID)
values (
isnull(source.ID,newid())
,source.JOURNALENTRYID
,source.ANNOTATIONCATEGORYCODE
,source.REFERENCENAME
,source.SEQUENCE
,source.VALIDATIONCODE
,source.[TEXT]
,source.APPUSERID
,@CURRENTDATE
,@CURRENTDATE
,@CHANGEAGENTID
,@CHANGEAGENTID
);
end;
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;