UFN_PDACCOUNT_GETCUSTOMTABLESFORSEGMENT_REVENUEBATCH

Allows additional tables to be added to the 'Calculate using' dropdown for account structure and processed in batch.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@REVENUESPLITID	uniqueidentifier	IN
@REVENUETRANSACTIONTYPECODE	tinyint	IN
@REVENUESPLITTYPECODE	tinyint	IN
@APPLICATIONCODE	tinyint	IN
@PAYMENTMETHODCODE	tinyint	IN
@DESIGNATIONID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN

Definition

Copy



            create function dbo.UFN_PDACCOUNT_GETCUSTOMTABLESFORSEGMENT_REVENUEBATCH
            (
                @REVENUEID uniqueidentifier, 
                @REVENUESPLITID uniqueidentifier, 
                @REVENUETRANSACTIONTYPECODE tinyint, 
                @REVENUESPLITTYPECODE tinyint, 
                @APPLICATIONCODE tinyint, 
                @PAYMENTMETHODCODE tinyint, 
                @DESIGNATIONID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            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