USP_FINANCIALTRANSACTION_ADD

Adds a row in to the Financial Transaction Table
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@USERDEFINEDID	nvarchar(60)	IN
@TYPECODE	tinyint	IN
@AMOUNT	money	IN
@DATE	datetime	IN
@POSTDATE	datetime	IN
@POSTSTATUSCODE	tinyint	IN
@DESCRIPTION	nvarchar(100)	IN
@LINEITEMS	xml	IN
@PARENTID	uniqueidentifier	IN
@PDACCOUNTSYSTEMID	uniqueidentifier	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN
@BASEAMOUNT	money	IN
@ORGAMOUNT	money	IN
Definition

Copy


CREATE procedure dbo.[USP_FINANCIALTRANSACTION_ADD]
(
  @ID uniqueidentifier = null output
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@CONSTITUENTID uniqueidentifier = null
  ,@USERDEFINEDID nvarchar(60) = ''
  ,@TYPECODE tinyint = 1
  ,@AMOUNT money = 0
  ,@DATE datetime = null
  ,@POSTDATE datetime = null
  ,@POSTSTATUSCODE tinyint = 1
  ,@DESCRIPTION nvarchar(100) = ''
  ,@LINEITEMS xml = null
  ,@PARENTID uniqueidentifier = null
  ,@PDACCOUNTSYSTEMID uniqueidentifier = null
  ,@TRANSACTIONCURRENCYID uniqueidentifier = null
  ,@BASEAMOUNT money = null
  ,@ORGAMOUNT money = null
) 
as

set nocount on;

declare @MULTICURRENCYLINEITEMS tinyint;

if @ID is null
set @ID = newid()

if @CHANGEAGENTID is null  
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

if @PDACCOUNTSYSTEMID is NULL 
  select @PDACCOUNTSYSTEMID = ID from dbo.PDACCOUNTSYSTEM where ISDEFAULT = 1;

if @TRANSACTIONCURRENCYID is NULL
begin
  select @TRANSACTIONCURRENCYID = BASECURRENCYID from 
    dbo.CURRENCYSET as CS
    inner join dbo.PDACCOUNTSYSTEM as PD on PD.CURRENCYSETID = CS.ID
  where
    PD.ID = @PDACCOUNTSYSTEMID;

  if @BASEAMOUNT is NULL and @ORGAMOUNT is NULL 
  begin
    set @BASEAMOUNT = @AMOUNT;
    set @ORGAMOUNT = @AMOUNT;
  end
  else
    raiserror ('ERR_FINANCIALTRANSACTION_BASEANDORGAMOUNT_REQUIRE_TRANSACTIONCURRENCY',16,1);    
  set @MULTICURRENCYLINEITEMS  = 0
end;
else 
 set @MULTICURRENCYLINEITEMS = 1

begin try
-- handle inserting the data

insert into dbo.FINANCIALTRANSACTION 
(
  ID
  ,CONSTITUENTID
  ,USERDEFINEDID
  ,TYPECODE
  ,TRANSACTIONAMOUNT
  ,BASEAMOUNT
  ,ORGAMOUNT
  ,DATE
  ,DESCRIPTION
  ,PARENTID
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  ,POSTDATE
  ,POSTSTATUSCODE
  ,PDACCOUNTSYSTEMID
  ,TRANSACTIONCURRENCYID  
)
values 
(
  @ID
  ,@CONSTITUENTID
  ,@USERDEFINEDID
  ,@TYPECODE
  ,@AMOUNT
  ,@BASEAMOUNT
  ,@ORGAMOUNT
  ,@DATE
  ,@DESCRIPTION
  ,@PARENTID
  ,@CHANGEAGENTID 
  ,@CHANGEAGENTID
  ,@CURRENTDATE
  ,@CURRENTDATE
  ,@POSTDATE
  ,@POSTSTATUSCODE
  ,@PDACCOUNTSYSTEMID
  ,@TRANSACTIONCURRENCYID
)
    declare @ITEMSTABLE as table (
        ID uniqueidentifier
        ,FINANCIALTRANSACTIONID uniqueidentifier
        ,AMOUNT money
    ,BASEAMOUNT money
    ,ORGAMOUNT money
        ,[DESCRIPTION] nvarchar(100)
        ,SEQUENCE int
    ,POSTDATE date
    ,POSTSTATUSCODE tinyint
        ,GLENTRIES xml
    );

    --TODO: Postdate and poststatus will need to be changed to the lineitems poststatus/postdate

    insert into @ITEMSTABLE 
        select 
            ID
            ,@ID 
            ,AMOUNT
      ,BASEAMOUNT
      ,ORGAMOUNT
            ,[DESCRIPTION]
            ,SEQUENCE
      ,@POSTDATE
      ,@POSTSTATUSCODE
            ,GLENTRIES
        from dbo.UFN_FINANCIALTRANSACTION_LINEITEMS_FROMITEMLISTXML(@LINEITEMS,@MULTICURRENCYLINEITEMS);

    if @LINEITEMS is null or not exists (select * from @ITEMSTABLE)
    begin
        declare @LINEITEMID uniqueidentifier;
        set @LINEITEMID = null;

        -- insert line item information with no GL distribution

        exec dbo.USP_FINANCIALTRANSACTIONLINEITEM_ADD @LINEITEMID, @CHANGEAGENTID, @ID, @AMOUNT, 1, @DESCRIPTION, 1, @POSTDATE, @POSTSTATUSCODE, @BASEAMOUNT, @ORGAMOUNT;
    end
    else
    begin            
        update @ITEMSTABLE    
            set ID =  NEWID() 
        where ID = '00000000-0000-0000-0000-000000000000' or ID is null

        insert into dbo.FINANCIALTRANSACTIONLINEITEM
            (
        ID
        ,FINANCIALTRANSACTIONID
        ,TRANSACTIONAMOUNT
        ,BASEAMOUNT
        ,ORGAMOUNT
        ,DESCRIPTION
        ,SEQUENCE
        ,POSTDATE
        ,POSTSTATUSCODE
            -- boilerplate

            , ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select
            ID
            ,@ID FINANCIALTRANSACTIONID
            ,AMOUNT
      ,BASEAMOUNT
      ,ORGAMOUNT
            ,DESCRIPTION
            ,SEQUENCE
      ,POSTDATE
      ,POSTSTATUSCODE
            -- boilerplate

            ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from
            @ITEMSTABLE        
        ;

        insert into dbo.JOURNALENTRY
            (
                ID, TRANSACTIONAMOUNT, BASEAMOUNT, ORGAMOUNT, COMMENT, TRANSACTIONTYPECODE, GL.SEQUENCE
                ,CLASSCODE, POSTDATE, FINANCIALTRANSACTIONLINEITEMID
                ,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 
                -- boilerplate

                , ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
        select -- let's do credits and debits for GLENTRIES in lineitems

                newid(), GL.AMOUNT, GL.BASEAMOUNT, GL.ORGAMOUNT, COMMENT, TRANSACTIONTYPECODE, GL.SEQUENCE
                ,CLASSCODE, GL.POSTDATE, ID FINANCIALTRANSACTIONLINEITEMID
                ,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 
                -- boilerplate

                ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from
            @ITEMSTABLE
        cross apply dbo.UFN_FINANCIALTRANSACTION_LINEITEM_GL_FROMITEMLISTXML_MC(GLENTRIES,@MULTICURRENCYLINEITEMS,@BASEAMOUNT,@ORGAMOUNT) as GL

    end
end try

begin catch
  exec dbo.USP_RAISE_ERROR
  return 1
end catch

return 0