USP_DATALIST_ACCOUNTINGELEMENTRELATIONSHIPGROUPBYACCOUNT

This list displays all the relationships with which this account is associated..

Parameters

Parameter	Parameter Type	Mode	Description
@ACCOUNTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_ACCOUNTINGELEMENTRELATIONSHIPGROUPBYACCOUNT(@ACCOUNTID uniqueidentifier)
as
    set nocount on;

    select AER.ID,
       AER.RELATIONSHIPID,
       AER.DESCRIPTION,
       ASR.DESCRIPTION as BASIS,
     AER.RESTRICTION,
     SELECTEDELEMENTS = case when len(substring((SELECT  DISTINCT ( ', ' + DE.SHORTDESCRIPTION )
                         FROM ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
                         inner join PDACCOUNTSEGMENTVALUE DE on DE.ID = AERD.BASEELEMENTID
                       WHERE AER.ID = AERD.ACCOUNTINGELEMENTRELATIONSHIPID
                         ORDER BY 
                            ', ' + DE.SHORTDESCRIPTION
                        FOR XML PATH( '' )),3,105)) > 100 then
            substring((SELECT  DISTINCT ( ', ' + DE.SHORTDESCRIPTION )
                         FROM ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
                         inner join PDACCOUNTSEGMENTVALUE DE on DE.ID = AERD.BASEELEMENTID
                       WHERE AER.ID = AERD.ACCOUNTINGELEMENTRELATIONSHIPID
                         ORDER BY 
                            ', ' + DE.SHORTDESCRIPTION
                        FOR XML PATH( '' )),3,100) + '...' else
            substring((SELECT  DISTINCT ( ', ' + DE.SHORTDESCRIPTION )
                         FROM ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD
                         inner join PDACCOUNTSEGMENTVALUE DE on DE.ID = AERD.BASEELEMENTID
                       WHERE AER.ID = AERD.ACCOUNTINGELEMENTRELATIONSHIPID
                         ORDER BY 
                            ', ' + DE.SHORTDESCRIPTION
                        FOR XML PATH( '' )),3,100) end
    from ACCOUNTINGELEMENTRELATIONSHIP AER 
    left join ACCOUNTINGELEMENTRELATIONSHIPDETAIL as AERD on AER.ID = AERD.ACCOUNTINGELEMENTRELATIONSHIPID
  left join dbo.PDACCOUNTSTRUCTURE ASR on ASR.ID = AER.BASEELEMENTACCOUNTSTRUCTUREID
  where AERD.ACCOUNTID = @ACCOUNTID
  group by AER.ID, AER.RELATIONSHIPID,AER.DESCRIPTION,ASR.DESCRIPTION,AER.RESTRICTION