USP_DATALIST_ACCOUNTINGELEMENTRELATIONSHIPGROUPBYELEMENT

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

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

Copy


CREATE procedure dbo.USP_DATALIST_ACCOUNTINGELEMENTRELATIONSHIPGROUPBYELEMENT(
  @ACCOUNTINGELEMENTID 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 (BASEELEMENTID = @ACCOUNTINGELEMENTID) or 
        (DATAELEMENT1ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT2ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT3ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT4ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT5ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT6ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT7ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT8ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT9ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT10ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT11ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT12ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT13ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT14ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT15ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT16ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT17ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT18ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT19ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT20ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT21ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT22ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT23ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT24ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT25ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT26ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT27ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT28ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT29ID = @ACCOUNTINGELEMENTID) or
        (DATAELEMENT30ID = @ACCOUNTINGELEMENTID)
      group by AER.ID, AER.RELATIONSHIPID,AER.DESCRIPTION,ASR.DESCRIPTION,AER.RESTRICTION