UFN_CONTROLACCOUNT_GETDATAELEMENTS

Returns a table of accounting elements associated with the given control account.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@CONTROLACCOUNTID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_CONTROLACCOUNT_GETDATAELEMENTS(@CONTROLACCOUNTID uniqueidentifier)
returns @DATAELEMENTS table
  (ID uniqueidentifier,
  DATAELEMENTID uniqueidentifier,
  ACCOUNTSTRUCTUREID uniqueidentifier)
with execute as caller
as begin
  insert into @DATAELEMENTS(ID, DATAELEMENTID, ACCOUNTSTRUCTUREID)
  select unPvt.ID, unPvt.DATAELEMENTID, PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID
  from
  (select ID, 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, ACCOUNTTYPECODE
  from dbo.CONTROLACCOUNT
  where ID = @CONTROLACCOUNTID) p
  unpivot
  (DATAELEMENTID for dColumn in
      (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)) as unPvt
  inner join dbo.PDACCOUNTSEGMENTVALUE ON unPvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID    

  return;
end