UFN_PDACCOUNT_GETCUSTOMTABLESFORSEGMENT

create function dbo.UFN_PDACCOUNT_GETCUSTOMTABLESFORSEGMENT(@REVENUESPLITID uniqueidentifier, @REVENUETRANSACTIONTYPECODE tinyint, @APPLICATIONCODE tinyint, @PAYMENTMETHODCODE tinyint, @DESIGNATIONID uniqueidentifier)
returns @AdditionalCalculateUsingTables table (TableID uniqueidentifier, RowID uniqueidentifier, FunctionID int, FriendlyTableName varchar(255))
as
begin

with CTE (PDACCOUNTTABLESAVAILABLEFORSEGMENTID, FRIENDLYTABLENAME)
as (select distinct isnull(PDCOMPOSITESEGMENTTABLELIST.PDACCOUNTTABLESAVAILABLEFORSEGMENTID, PDACCOUNTSTRUCTURE.PDACCOUNTTABLESAVAILABLEFORSEGMENTID) as PDACCOUNTTABLESAVAILABLEFORSEGMENTID,
isnull(tables2.FRIENDLYTABLENAME,PDACCOUNTTABLESAVAILABLEFORSEGMENT.FRIENDLYTABLENAME) as FRIENDLYTABLENAME
from dbo.PDACCOUNTSTRUCTURE inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT on PDACCOUNTSTRUCTURE.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID 
left join dbo.PDCOMPOSITESEGMENTTABLELIST on PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID = PDCOMPOSITESEGMENTTABLELIST.PDCOMPOSITESEGMENTID 
left join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT tables2 on PDCOMPOSITESEGMENTTABLELIST.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = tables2.ID
where PDACCOUNTSTRUCTURE.SEGMENTTYPE = 3 or PDACCOUNTSTRUCTURE.SEGMENTTYPE = 4)

insert into @AdditionalCalculateUsingTables (TableID, RowID, FunctionID, FriendlyTableName)
--This entry is an example that will never be returned.  

--Union a select statement of this form for each table to be added to the "Calculate using" dropdown, incrementing FunctionID

--First column is the TableID, second column is the scalar function that returns the ID for a single row from the table.  

--See UFN_REVENUE_GENERATEGLACCOUNT_STANDARD2 for examples of the system standard segments and scalar functions.

select '00000000-0000-0000-0000-000000000000' as TableID, '00000000-0000-0000-0000-000000000000' as RowID, 100 as FunctionID, 'EXAMPLE' as FriendlyTableName
from CTE inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT on CTE.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID
where PDACCOUNTTABLESAVAILABLEFORSEGMENT.TableID = '00000000-0000-0000-0000-000000000000'

return
end