USP_BALANCINGENTRY_ADDFROMXML

CREATE procedure dbo.USP_BALANCINGENTRY_ADDFROMXML(
@BALANCINGENTRYID uniqueidentifier,
@BALANCINGENTRYTYPECODE tinyint,
@XML xml,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
as
begin
    if @CHANGEAGENTID is null
    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

if @CHANGEDATE is null
    set @CHANGEDATE = getdate()

-- build a temporary table containing the values from the XML

declare @TempTbl table (
   [DATAELEMENTID] uniqueidentifier,
   SEGMENTCOLUMN tinyint)


if @BALANCINGENTRYTYPECODE = 1 
  insert into @TempTbl(DATAELEMENTID, SEGMENTCOLUMN)
  select f1.DATAELEMENTID, NUMBERS.NUM
  from dbo.UFN_BALANCINGENTRY_GETELEMENTSDEBITFROM_FROMITEMLISTXML(@XML) f1 
  inner join dbo.PDACCOUNTSEGMENTVALUE on f1.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

if @BALANCINGENTRYTYPECODE = 2 
  insert into @TempTbl(DATAELEMENTID, SEGMENTCOLUMN)
  select f1.DATAELEMENTID, NUMBERS.NUM
  from dbo.UFN_BALANCINGENTRY_GETELEMENTSCREDITFROM_FROMITEMLISTXML(@XML) f1 
  inner join dbo.PDACCOUNTSEGMENTVALUE on f1.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

if @BALANCINGENTRYTYPECODE = 3 
  insert into @TempTbl(DATAELEMENTID, SEGMENTCOLUMN)
  select f1.DATAELEMENTID, NUMBERS.NUM
  from dbo.UFN_BALANCINGENTRY_GETELEMENTSDEBITTO_FROMITEMLISTXML(@XML) f1 
  inner join dbo.PDACCOUNTSEGMENTVALUE on f1.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

if @BALANCINGENTRYTYPECODE = 4
  insert into @TempTbl(DATAELEMENTID, SEGMENTCOLUMN)
  select f1.DATAELEMENTID, NUMBERS.NUM
  from dbo.UFN_BALANCINGENTRY_GETELEMENTSCREDITTO_FROMITEMLISTXML(@XML) f1 
  inner join dbo.PDACCOUNTSEGMENTVALUE on f1.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;

if @@Error <> 0
    return 1;

merge dbo.BALANCINGENTRYELEMENT as Target
using (select @BALANCINGENTRYID as BALANCINGENTRYID, @BALANCINGENTRYTYPECODE as BALANCINGENTRYTYPECODE,
    cast([1] as uniqueidentifier) as Col1, cast([2] as uniqueidentifier) as Col2, cast([3] as uniqueidentifier) as Col3, cast([4] as uniqueidentifier) as Col4, cast([5] as uniqueidentifier) as Col5, cast([6] as uniqueidentifier) as Col6, cast([7] as uniqueidentifier) as Col7, cast([8] as uniqueidentifier) as Col8, cast([9] as uniqueidentifier) as Col9, cast([10] as uniqueidentifier) as Col10, 
    cast([11] as uniqueidentifier) as Col11, cast([12] as uniqueidentifier) as Col12, cast([13] as uniqueidentifier) as Col13, cast([14] as uniqueidentifier) as Col14, cast([15] as uniqueidentifier) as Col15, cast([16] as uniqueidentifier) as Col16, cast([17] as uniqueidentifier) as Col17, cast([18] as uniqueidentifier) as Col18, cast([19] as uniqueidentifier) as Col19, cast([20] as uniqueidentifier) as Col20, 
    cast([21] as uniqueidentifier) as Col21, cast([22] as uniqueidentifier) as Col22, cast([23] as uniqueidentifier) as Col23, cast([24] as uniqueidentifier) as Col24, cast([25] as uniqueidentifier) as Col25, cast([26] as uniqueidentifier) as Col26, cast([27] as uniqueidentifier) as Col27, cast([28] as uniqueidentifier) as Col28, cast([29] as uniqueidentifier) as Col29, cast([30] as uniqueidentifier) as Col30 
    from (
      select cast(DATAELEMENTID as nvarchar(36)) as Element, SEGMENTCOLUMN
      from @TempTbl) 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
 ) as Source
on Target.BALANCINGENTRYTYPECODE = Source.BALANCINGENTRYTYPECODE and Target.BALANCINGENTRYID = Source.BALANCINGENTRYID
when matched then
  update set Target.DATAELEMENT1ID = Source.Col1,
  Target.DATAELEMENT2ID = Source.Col2,
  Target.DATAELEMENT3ID = Source.Col3,
  Target.DATAELEMENT4ID = Source.Col4,
  Target.DATAELEMENT5ID = Source.Col5,
  Target.DATAELEMENT6ID = Source.Col6,
  Target.DATAELEMENT7ID = Source.Col7,
  Target.DATAELEMENT8ID = Source.Col8,
  Target.DATAELEMENT9ID = Source.Col9,
  Target.DATAELEMENT10ID = Source.Col10,
  Target.DATAELEMENT11ID = Source.Col11,
  Target.DATAELEMENT12ID = Source.Col12,
  Target.DATAELEMENT13ID = Source.Col13,
  Target.DATAELEMENT14ID = Source.Col14,
  Target.DATAELEMENT15ID = Source.Col15,
  Target.DATAELEMENT16ID = Source.Col16,
  Target.DATAELEMENT17ID = Source.Col17,
  Target.DATAELEMENT18ID = Source.Col18,
  Target.DATAELEMENT19ID = Source.Col19,
  Target.DATAELEMENT20ID = Source.Col20,
  Target.DATAELEMENT21ID = Source.Col21,
  Target.DATAELEMENT22ID = Source.Col22,
  Target.DATAELEMENT23ID = Source.Col23,
  Target.DATAELEMENT24ID = Source.Col24,
  Target.DATAELEMENT25ID = Source.Col25,
  Target.DATAELEMENT26ID = Source.Col26,
  Target.DATAELEMENT27ID = Source.Col27,
  Target.DATAELEMENT28ID = Source.Col28,
  Target.DATAELEMENT29ID = Source.Col29,
  Target.DATAELEMENT30ID = Source.Col30,
  Target.CHANGEDBYID = @CHANGEAGENTID,
  Target.DATECHANGED = @CHANGEDATE
when not matched by Target then
  insert (ID, BALANCINGENTRYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BALANCINGENTRYTYPECODE,
          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 (newid(), @BALANCINGENTRYID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @BALANCINGENTRYTYPECODE,
          Source.Col1, Source.Col2, Source.Col3, Source.Col4, Source.Col5, Source.Col6, Source.Col7, Source.Col8, Source.Col9, Source.Col10, 
          Source.Col11, Source.Col12, Source.Col13, Source.Col14, Source.Col15, Source.Col16, Source.Col17, Source.Col18, Source.Col19, Source.Col20, 
          Source.Col21, Source.Col22, Source.Col23, Source.Col24, Source.Col25, Source.Col26, Source.Col27, Source.Col28, Source.Col29, Source.Col30);

if @@Error <> 0
    return 2;

return 0;

end