USP_DATALIST_BENEFITGLDISTRIBUTIONLIABILITY

A datalist of benefit liability GL distributions.
Parameters

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

Copy


CREATE procedure dbo.USP_DATALIST_BENEFITGLDISTRIBUTIONLIABILITY(@ID uniqueidentifier)
                as 
                    set nocount on;

                    declare @POSTSTATUS as bit = 1

                    if exists(select ID from  REVENUEPOSTED where ID = @ID)
                        set @POSTSTATUS = 0
                    select 
                        BENEFITGLDISTRIBUTION.REVENUEID as ID,
                        BENEFITGLDISTRIBUTION.ID as REVENUEGLDISTRIBUTIONID,
                        MAP.DESCRIPTION,
                        BENEFITGLDISTRIBUTION.TRANSACTIONTYPE,                        
                        BENEFITGLDISTRIBUTION.ACCOUNT,
                        BENEFITGLDISTRIBUTION.PROJECT,
                        BENEFITGLDISTRIBUTION.AMOUNT,
                        BENEFITGLDISTRIBUTION.REFERENCE,
                        BENEFITGLDISTRIBUTION.TRANSACTIONAMOUNT,
                        BENEFITGLDISTRIBUTION.AMOUNT,
                        BENEFITGLDISTRIBUTION.ORGANIZATIONAMOUNT,
                        BENEFITGLDISTRIBUTION.TRANSACTIONCURRENCYID,
                        BENEFITGLDISTRIBUTION.BASECURRENCYID
                    from 
                        dbo.BENEFITGLDISTRIBUTION
                        inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on BENEFITGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
                        inner join GLTRANSACTION on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
                    where 
                        BENEFITGLDISTRIBUTION.REVENUEID = @ID  
                        and BENEFITGLDISTRIBUTION.OUTDATED = 0
                        and BENEFITGLDISTRIBUTION.BENEFITTYPECODE = 2
                        and ((GLTRANSACTION.POSTSTATUSCODE = @POSTSTATUS) or  exists(select * from BENEFITADJUSTMENT where REVENUEID = @ID ))
                    order by
                        BENEFITGLDISTRIBUTION.REFERENCE,BENEFITGLDISTRIBUTION.AMOUNT,BENEFITGLDISTRIBUTION.TRANSACTIONTYPE desc