USP_DATALIST_BENEFITGLDISTRIBUTIONBACKOUT

A datalist of benefit Backout 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_BENEFITGLDISTRIBUTIONBACKOUT(@ID uniqueidentifier)
                as 
                    set nocount on;

                    declare @POSTSTATUS as bit = 1

                    if exists(select ID from REVENUEPOSTED where ID = @ID)
                        begin
                            set @POSTSTATUS = 0
                            if exists(select 1 from dbo.BENEFITGLDISTRIBUTION left join dbo.BENEFITADJUSTMENT on  BENEFITGLDISTRIBUTION.REVENUEID = BENEFITADJUSTMENT.REVENUEID and BENEFITGLDISTRIBUTION.BENEFITTYPECODE = BENEFITADJUSTMENT.BENEFITTYPECODE join dbo.GLTRANSACTION on  BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID where BENEFITGLDISTRIBUTION.BENEFITTYPECODE = 3 and GLTRANSACTION.POSTSTATUSCODE = 1 and BENEFITADJUSTMENT.ID is null and BENEFITGLDISTRIBUTION.REVENUEID = @ID)
                                set @POSTSTATUS = 1
                        end 
                    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 = 3
                        and ((GLTRANSACTION.POSTSTATUSCODE = @POSTSTATUS) or  exists(select * from BENEFITADJUSTMENT where REVENUEID = @ID ))
                    order by
                        BENEFITGLDISTRIBUTION.REFERENCE,BENEFITGLDISTRIBUTION.AMOUNT,BENEFITGLDISTRIBUTION.TRANSACTIONTYPE desc