USP_DATAFORMTEMPLATE_EDIT_JOURNALENTRIES
The save procedure used by the edit dataform template "Financial Transaction Journal Entry Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @BATCHJOURNALENTRIES | xml | IN | Journal entries |
Definition
Copy
CREATE procedure [dbo].[USP_DATAFORMTEMPLATE_EDIT_JOURNALENTRIES] (
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@BATCHJOURNALENTRIES xml
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime;
declare @BATCHID uniqueidentifier;
set @CURRENTDATE = getdate();
declare @temp table (
JOURNALENTRYID uniqueidentifier NOT NULL,
AMOUNT money NULL,
CLASSCODE tinyint NULL,
COMMENT nvarchar(60) NULL,
JOURNALCODEID uniqueidentifier NULL,
PERCENTAGE numeric(20,4) NULL,
SEQUENCE int NOT NULL,
TRANSACTIONTYPECODE 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)
begin try
begin transaction
insert into @temp (
JOURNALENTRYID,
AMOUNT,
CLASSCODE,
COMMENT,
JOURNALCODEID,
PERCENTAGE,
SEQUENCE,
TRANSACTIONTYPECODE,
POSTDATE,
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(JOURNALENTRYID,'00000000-0000-0000-0000-000000000000')='00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE JOURNALENTRYID END JOURNALENTRYID,
CASE WHEN COALESCE(CREDITAMOUNT,0)<>0 THEN CREDITAMOUNT ELSE CASE WHEN coalesce(DEBITAMOUNT,0)<>0 THEN DEBITAMOUNT ELSE 0 END END,
CLASSCODE,
COMMENT,
JOURNALCODEID,
0,
SEQUENCE,
CASE WHEN COALESCE(CREDITAMOUNT,0)<>0 THEN 1 ELSE 0 END,
POSTDATE,
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,
ANNOTATIONS
from
dbo.[UFN_BATCHJOURNALENTRIES_FROMITEMLISTXML](@BATCHJOURNALENTRIES)
merge dbo.JOURNALENTRY as target
using (select
JOURNALENTRYID,
AMOUNT,
CLASSCODE,
COMMENT,
JOURNALCODEID,
PERCENTAGE,
T1.SEQUENCE,
TRANSACTIONTYPECODE,
POSTDATE,
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
from @temp as T1) as source (
JOURNALENTRYID,
AMOUNT,
CLASSCODE,
COMMENT,
JOURNALCODEID,
PERCENTAGE,
SEQUENCE,
TRANSACTIONTYPECODE,
POSTDATE,
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
)
on (target.ID=source.JOURNALENTRYID and target.FINANCIALTRANSACTIONLINEITEMID=@ID)
when not matched by source and target.FINANCIALTRANSACTIONLINEITEMID=@ID then
DELETE
when matched then
update set
TRANSACTIONAMOUNT=source.AMOUNT,
CLASSCODE=source.CLASSCODE,
COMMENT=source.COMMENT,
JOURNALCODEID=source.JOURNALCODEID,
PERCENTAGE=source.PERCENTAGE,
SEQUENCE=source.SEQUENCE,
TRANSACTIONTYPECODE=source.TRANSACTIONTYPECODE,
POSTDATE=source.POSTDATE,
CHANGEDBYID=@CHANGEAGENTID,
DATECHANGED=@CURRENTDATE
,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
when not matched then
insert (
ID,
FINANCIALTRANSACTIONLINEITEMID,
TRANSACTIONAMOUNT,
CLASSCODE,
COMMENT,
JOURNALCODEID,
PERCENTAGE,
SEQUENCE,
TRANSACTIONTYPECODE,
POSTDATE,
ADDEDBYID,
DATEADDED,
CHANGEDBYID,
DATECHANGED,
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)
values (
source.JOURNALENTRYID,
@ID,
source.AMOUNT,
source.CLASSCODE,
source.COMMENT,
source.JOURNALCODEID,
source.PERCENTAGE,
source.SEQUENCE,
source.TRANSACTIONTYPECODE,
source.POSTDATE,
@CHANGEAGENTID,
@CURRENTDATE,
@CHANGEAGENTID,
@CURRENTDATE,
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
);
--the following use of a temp table and broken out MERGE statement 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 @temp 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 WHERE ID=target.JOURNALENTRYID AND FINANCIALTRANSACTIONLINEITEMID=@ID)) then
delete
;
update dbo.JOURNALENTRYANNOTATION SET
JOURNALENTRYANNOTATION.ANNOTATIONCATEGORYCODE=T2.ANNOTATIONCATEGORYCODE,
JOURNALENTRYANNOTATION.REFERENCENAME=T2.REFERENCENAME,
JOURNALENTRYANNOTATION.SEQUENCE=T2.SEQUENCE,
JOURNALENTRYANNOTATION.VALIDATIONCODE=T2.VALIDATIONCODE,
JOURNALENTRYANNOTATION.[TEXT]=T2.[TEXT],
JOURNALENTRYANNOTATION.CHANGEDBYID=@CHANGEAGENTID,
JOURNALENTRYANNOTATION.DATECHANGED=@CURRENTDATE
from dbo.JOURNALENTRYANNOTATION inner join @temp2 T2 on JOURNALENTRYANNOTATION.ID=T2.ID
insert into dbo.JOURNALENTRYANNOTATION (
ID
,JOURNALENTRYID
,ANNOTATIONCATEGORYCODE
,REFERENCENAME
,SEQUENCE
,VALIDATIONCODE
,[TEXT]
,APPUSERID
,DATEADDED
,DATECHANGED
,ADDEDBYID
,CHANGEDBYID)
SELECT
T1.ID
,T1.JOURNALENTRYID
,T1.ANNOTATIONCATEGORYCODE
,T1.REFERENCENAME
,T1.SEQUENCE
,T1.VALIDATIONCODE
,T1.[TEXT]
,T1.APPUSERID
,@CURRENTDATE
,@CURRENTDATE
,@CHANGEAGENTID
,@CHANGEAGENTID
FROM
@temp2 T1
WHERE NOT EXISTS (SELECT 1 FROM dbo.JOURNALENTRYANNOTATION T2 WHERE T2.ID=T1.ID)
update JEB set
CHANGEDBYID = @CHANGEAGENTID
, DATECHANGED = @CURRENTDATE
from dbo.JOURNALENTRYBATCH JEB
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on JEB.ID = LI.FINANCIALTRANSACTIONID
where LI.ID = @ID;
commit transaction
end try
begin catch
rollback transaction
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0