USP_DATAFORMTEMPLATE_ADD_LEDGERACCOUNT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_LEDGERACCOUNT
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
    @DESCRIPTION nvarchar(400),
    @DATAELEMENTS xml = null,
  @PDACCOUNTSYSTEMID uniqueidentifier
)
as

set nocount on;

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

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

if @PDACCOUNTSYSTEMID is null
  set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

declare @CURRENTDATE datetime = (select getdate())

begin try

    /* Verify that the account is unique */
    if dbo.UFN_LEDGERACCOUNT_ACCOUNTISUNIQUE(@DATAELEMENTS,null) = 0
        raiserror('ERR_LEDGERACCOUNT_NONUNIQUEACCOUNTSTRING',13,1)

  declare @Segments table (DATAELEMENTID uniqueidentifier, SEGMENTSEQUENCE tinyint, SEGMENTCOLUMN tinyint, SHORTDESCRIPTION nvarchar(100))

  insert into @Segments (DATAELEMENTID, SEGMENTSEQUENCE, SEGMENTCOLUMN, SHORTDESCRIPTION)
  select tf1.DATAELEMENTID, PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE, NUMBERS.NUM, PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION
  from dbo.UFN_LEDGERACCOUNT_GETACCOUNTSEGMENTS_FROMITEMLISTXML(@DATAELEMENTS) tf1
  inner join dbo.PDACCOUNTSEGMENTVALUE on tf1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
  inner join dbo.PDACCOUNTSTRUCTURE ON PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
  right join dbo.NUMBERS on PDACCOUNTSTRUCTURE.SEGMENTCOLUMN = NUMBERS.NUM 
  where NUMBERS.NUM between 1 and 30

    insert into dbo.GLACCOUNT
        (ID, ACCOUNTDESCRIPTION, PDACCOUNTSYSTEMID, ADDEDBYID, CHANGEDBYID, DATEADDED, 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)
    select @ID, @DESCRIPTION, @PDACCOUNTSYSTEMID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
  cast([1] as uniqueidentifier), cast([2] as uniqueidentifier), cast([3] as uniqueidentifier), cast([4] as uniqueidentifier), cast([5] as uniqueidentifier), cast([6] as uniqueidentifier), cast([7] as uniqueidentifier), cast([8] as uniqueidentifier), cast([9] as uniqueidentifier), cast([10] as uniqueidentifier), 
  cast([11] as uniqueidentifier), cast([12] as uniqueidentifier), cast([13] as uniqueidentifier), cast([14] as uniqueidentifier), cast([15] as uniqueidentifier), cast([16] as uniqueidentifier), cast([17] as uniqueidentifier), cast([18] as uniqueidentifier), cast([19] as uniqueidentifier), cast([20] as uniqueidentifier), 
  cast([21] as uniqueidentifier), cast([22] as uniqueidentifier), cast([23] as uniqueidentifier), cast([24] as uniqueidentifier), cast([25] as uniqueidentifier), cast([26] as uniqueidentifier), cast([27] as uniqueidentifier), cast([28] as uniqueidentifier), cast([29] as uniqueidentifier), cast([30] as uniqueidentifier)
  from (
    select cast(DATAELEMENTID as nvarchar(36)) as Element, SEGMENTCOLUMN
    from @Segments) as S
    pivot
    (max(Element) for SEGMENTCOLUMN in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])) as P
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0