USP_DATALIST_BENEFITGLDISTRIBUTION

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

                    --Post Status Code

                    -- 0: Do Not Post

                    -- 1: Not Posted

                    -- 2: Posted

                    -- 3: Posted (adjustment pending)

                    -- 4: Posted (adjustment set to do not post)        

                    declare @POSTSTATUSCODE tinyint = 0; 
                    declare @POSTSTATUS as bit = 1;
/*                    select top 1
                        @POSTSTATUSCODE =    case
                                                when REVENUE.DONOTPOST = 1                                                then 0
                                                when REVENUEPOSTED.ID is null                                            then 1
                                                when REVENUEPOSTED.ID is not null and ADJUSTMENT.ID is null                then 2
                                                when REVENUEPOSTED.ID is not null and ADJUSTMENT.POSTSTATUSCODE = 1        then 3
                                                when REVENUEPOSTED.ID is not null and ADJUSTMENT.POSTSTATUSCODE = 2        then 4
                                            end,
                        @POSTSTATUS = case when REVENUEPOSTED.ID is not null then 0 else 1 end
                    from dbo.REVENUE
                    left join dbo.ADJUSTMENT on REVENUE.ID = ADJUSTMENT.REVENUEID
                    left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                    where REVENUE.ID = @ID
                    order by ADJUSTMENT.DATEADDED desc;
*/


            select 
                FT.ID as ID,
                isnull(JEX.DISTRIBUTIONTABLEID, JEX.ID) as REVENUEGLDISTRIBUTIONID,
                MAP.DESCRIPTION,
                JE.TRANSACTIONTYPE,                        
                GL.ACCOUNTNUMBER as ACCOUNT,
                JEX.PROJECT,
                JE.BASEAMOUNT as AMOUNT,
                JE.COMMENT as REFERENCE,
                JE.TRANSACTIONAMOUNT,
                JE.BASEAMOUNT,
                JE.ORGAMOUNT as ORGANIZATIONAMOUNT,
                case FT.TRANSACTIONCURRENCYID 
                    when '00000000-0000-0000-0000-000000000000' then null
                    else FT.TRANSACTIONCURRENCYID end as TRANSACTIONCURRENCYID,
                case CURRENCYSET.BASECURRENCYID 
                    when '00000000-0000-0000-0000-000000000000' then null
                    else CURRENCYSET.BASECURRENCYID end as BASECURRENCYID

                from 
                    dbo.JOURNALENTRY_EXT JEX 
                    inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
                    inner join dbo.JOURNALENTRY JE on JEX.ID = JE.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID 
                    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
                    inner join dbo.PDACCOUNTSYSTEM on FT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID    
                    inner join dbo.GLACCOUNT GL on GL.ID = JE.GLACCOUNTID
                    left join dbo.ADJUSTMENT ADJ on LI.FINANCIALTRANSACTIONID = ADJ.REVENUEID 
                where 
                    not exists (select 1 from ADJUSTMENT where POSTSTATUSCODE = 2 and REVENUEID = @ID )
                    and
                    LI.FINANCIALTRANSACTIONID = @ID  
                    and JEX.BENEFITTYPECODE = 1    and ((JEX.OUTDATED = 0) or (JEX.OUTDATED = 1 and FT.TYPECODE = 5))                     
                    and 
                    (
                    (LI.POSTSTATUSCODE != 3) or  
                    exists(select * from BENEFITADJUSTMENT where REVENUEID = @ID )
                    )
                order by
                    REFERENCE,BASEAMOUNT,TRANSACTIONTYPE desc