UFN_POSTTOGLPROCESS_GETUNPOSTEDGLTRANSACTIONS

            CREATE function dbo.UFN_POSTTOGLPROCESS_GETUNPOSTEDGLTRANSACTIONS()
            returns table
            as
            return
            (
                select
                    DISTRIBUTION.REVENUEID,
                    GLTRANSACTION.ID as GLTRANSACTIONID,
                    GLTRANSACTION.TRANSACTIONTYPE,
                    GLTRANSACTION.ACCOUNT as ACCOUNTSTRING,
                    GLTRANSACTION.PROJECT,
                    GLTRANSACTION.AMOUNT,
                    GLTRANSACTION.REFERENCE,
                    cast(0 as bit) as ISREVERSAL,
                    cast(DISTRIBUTION.ISADJUSTED as bit) as ISADJUSTED
                from
                    dbo.GLTRANSACTION 
                inner join (
                    select REVENUEGLDISTRIBUTION.GLTRANSACTIONID,REVENUEGLDISTRIBUTION.REVENUEID, case when ADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.REVENUEGLDISTRIBUTION 
                    left join dbo.ADJUSTMENT on REVENUEGLDISTRIBUTION.REVENUEID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
                        union all
                    select STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID,STOCKSALEGLDISTRIBUTION.REVENUEID, case when STOCKSALEADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.STOCKSALEGLDISTRIBUTION
                    left join dbo.STOCKSALEADJUSTMENT on STOCKSALEGLDISTRIBUTION.STOCKSALEID = STOCKSALEADJUSTMENT.STOCKSALEID and STOCKSALEADJUSTMENT.POSTSTATUSCODE = 1
                        union all
                    select GIFTINKINDSALEGLDISTRIBUTION.GLTRANSACTIONID,GIFTINKINDSALEGLDISTRIBUTION.REVENUEID, case when GIFTINKINDSALEADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.GIFTINKINDSALEGLDISTRIBUTION
                    left join dbo.GIFTINKINDSALEADJUSTMENT on GIFTINKINDSALEGLDISTRIBUTION.GIFTINKINDSALEID = GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID and GIFTINKINDSALEADJUSTMENT.POSTSTATUSCODE = 1
                        union all
                    select PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID,PROPERTYDETAILGLDISTRIBUTION.REVENUEID, case when PROPERTYDETAILADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.PROPERTYDETAILGLDISTRIBUTION
                    left join dbo.PROPERTYDETAILADJUSTMENT on PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE = 1
                        union all
                    select WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID,WRITEOFFGLDISTRIBUTION.REVENUEID, case when WRITEOFFADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.WRITEOFFGLDISTRIBUTION
                    left join dbo.WRITEOFFADJUSTMENT on WRITEOFFGLDISTRIBUTION.WRITEOFFID = WRITEOFFADJUSTMENT.WRITEOFFID and WRITEOFFADJUSTMENT.POSTSTATUSCODE = 1
                        union all
                    select distinct GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID,GIFTAIDGLDISTRIBUTION.REVENUEID, case when REVENUESPLITGIFTAIDREFUND.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.GIFTAIDGLDISTRIBUTION
                    left join dbo.REVENUESPLITGIFTAIDREFUND on GIFTAIDGLDISTRIBUTION.REVENUESPLITGIFTAIDID = REVENUESPLITGIFTAIDREFUND.REVENUESPLITID --and WRITEOFFADJUSTMENT.POSTSTATUSCODE = 1

                    -- 10/12/2009 Discounts

                        union all
                    select distinct CREDITGLDISTRIBUTION.GLTRANSACTIONID, CREDITGLDISTRIBUTION.REVENUEID, case when ADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.CREDITGLDISTRIBUTION
                    inner join dbo.CREDITITEM on CREDITGLDISTRIBUTION.CREDITITEMID = CREDITITEM.ID
                    inner join dbo.CREDIT on CREDITITEM.CREDITID = CREDIT.ID
                    left join dbo.ADJUSTMENT on CREDITGLDISTRIBUTION.REVENUEID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
                    where CREDIT.TYPECODE <> 0
                    -- 7/18/1010 Refunds to Discounts

                        union all
                    select distinct CREDITGLDISTRIBUTION.GLTRANSACTIONID, DISCOUNTCREDITGLDISTRIBUTION.REVENUEID, case when ADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.CREDITGLDISTRIBUTION
                    inner join dbo.CREDITITEM on CREDITGLDISTRIBUTION.DISCOUNTCREDITITEMID = CREDITITEM.ID
                    inner join dbo.CREDITGLDISTRIBUTION [DISCOUNTCREDITGLDISTRIBUTION] on CREDITGLDISTRIBUTION.DISCOUNTCREDITITEMID = DISCOUNTCREDITGLDISTRIBUTION.CREDITITEMID
                    inner join dbo.CREDIT on CREDITITEM.CREDITID = CREDIT.ID
                    left join dbo.ADJUSTMENT on CREDITGLDISTRIBUTION.REVENUEID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
                    where CREDIT.TYPECODE <> 0
                        union all
                    select PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID,PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID, case when PLANNEDGIFTPAYOUTADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION 
                    left join dbo.PLANNEDGIFTPAYOUTADJUSTMENT on PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID = PLANNEDGIFTPAYOUTADJUSTMENT.REVENUEID and PLANNEDGIFTPAYOUTADJUSTMENT.POSTSTATUSCODE = 1                        
                        union all
                    select BENEFITGLDISTRIBUTION.GLTRANSACTIONID,BENEFITGLDISTRIBUTION.REVENUEID, case when BENEFITADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.BENEFITGLDISTRIBUTION 
                    left join dbo.BENEFITADJUSTMENT on BENEFITGLDISTRIBUTION.REVENUEID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE = 1                        
                        union all
                    select AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID,AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID as REVENUEID, case when AUCTIONPURCHASEADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
                    from dbo.AUCTIONPURCHASEGLDISTRIBUTION
                    left join dbo.AUCTIONPURCHASEADJUSTMENT on AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = AUCTIONPURCHASEADJUSTMENT.REVENUEID and AUCTIONPURCHASEADJUSTMENT.POSTSTATUSCODE = 1
                    )
                    as DISTRIBUTION on GLTRANSACTION.ID = DISTRIBUTION.GLTRANSACTIONID
                left join
                    dbo.REVENUE on DISTRIBUTION.REVENUEID = REVENUE.ID
                where
                    GLTRANSACTION.POSTSTATUSCODE = 1
                and
                    REVENUE.DONOTPOST = 0

                union all

                select REVENUEGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.REVENUEGLDISTRIBUTION 
                inner join dbo.GLTRANSACTION GL1 on REVENUEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.REVENUE on REVENUEGLDISTRIBUTION.REVENUEID = REVENUE.ID
                where GL2.POSTSTATUSCODE = 1
                and (REVENUE.ID is null or REVENUE.DONOTPOST = 0)

                union all

                select STOCKSALEGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.STOCKSALEGLDISTRIBUTION
                inner join dbo.GLTRANSACTION GL1 on STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.STOCKSALE on STOCKSALEGLDISTRIBUTION.STOCKSALEID = STOCKSALE.ID
                where GL2.POSTSTATUSCODE = 1
                and (STOCKSALE.ID is null or STOCKSALE.SALEPOSTSTATUSCODE <> 2)

                union all

                select GIFTINKINDSALEGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.GIFTINKINDSALEGLDISTRIBUTION
                inner join dbo.GLTRANSACTION GL1 on GIFTINKINDSALEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.GIFTINKINDSALE on GIFTINKINDSALEGLDISTRIBUTION.GIFTINKINDSALEID = GIFTINKINDSALE.ID
                where GL2.POSTSTATUSCODE = 1
                and (GIFTINKINDSALE.ID is null or GIFTINKINDSALE.SALEPOSTSTATUSCODE <> 2)

                union all

                select PROPERTYDETAILGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.PROPERTYDETAILGLDISTRIBUTION
                inner join dbo.GLTRANSACTION GL1 on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.PROPERTYDETAIL on PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = PROPERTYDETAIL.ID
                where GL2.POSTSTATUSCODE = 1
                and (PROPERTYDETAIL.ID is null or PROPERTYDETAIL.SALEPOSTSTATUSCODE <> 2)

                union all

                select WRITEOFFGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.WRITEOFFGLDISTRIBUTION
                inner join dbo.GLTRANSACTION GL1 on WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.WRITEOFF on WRITEOFFGLDISTRIBUTION.WRITEOFFID = WRITEOFF.ID
                where GL2.POSTSTATUSCODE = 1
                and (WRITEOFF.ID is null or WRITEOFF.POSTSTATUSCODE <> 2)

                union all

                select GIFTAIDGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.GIFTAIDGLDISTRIBUTION
                inner join dbo.GLTRANSACTION GL1 on GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.REVENUESPLITGIFTAID on GIFTAIDGLDISTRIBUTION.REVENUESPLITGIFTAIDID = REVENUESPLITGIFTAID.ID
                left join dbo.REVENUE on GIFTAIDGLDISTRIBUTION.REVENUEID = REVENUE.ID
                where GL2.POSTSTATUSCODE = 1
                and (REVENUESPLITGIFTAID.ID is null or REVENUE.DONOTPOST = 0)

                -- 10/12/2009 Discounts reversals

                union all

                select CREDITGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.CREDITGLDISTRIBUTION
                inner join dbo.CREDITITEM on CREDITGLDISTRIBUTION.CREDITITEMID = CREDITITEM.ID
                inner join dbo.CREDIT on CREDITITEM.CREDITID = CREDIT.ID
                inner join dbo.GLTRANSACTION GL1 on CREDITGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.REVENUE on CREDITGLDISTRIBUTION.REVENUEID = REVENUE.ID
                where GL2.POSTSTATUSCODE = 1 and CREDIT.TYPECODE <> 0
                and (REVENUE.ID is null or REVENUE.DONOTPOST = 0)

                -- 10/12/2009 Refunds

                union all

                select CREDITPAYMENT.ID REVENUEID, GL1.ID, GL1.TRANSACTIONTYPE, GL1.ACCOUNT, GL1.PROJECT, GL1.AMOUNT, GL1.REFERENCE, cast(0 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.CREDITGLDISTRIBUTION
                inner join dbo.GLTRANSACTION GL1 on CREDITGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.CREDITPAYMENT on CREDITGLDISTRIBUTION.CREDITPAYMENTID = CREDITPAYMENT.ID
                inner join dbo.CREDIT on CREDITPAYMENT.CREDITID = CREDIT.ID
                where 
                    CREDIT.TYPECODE = 0 and
                    GL1.POSTSTATUSCODE = 1

                union all

                select PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION 
                inner join dbo.GLTRANSACTION GL1 on PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.REVENUE on PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID = REVENUE.ID
                where GL2.POSTSTATUSCODE = 1
                and (REVENUE.ID is null or REVENUE.DONOTPOST = 0)

                union all

                select BENEFITGLDISTRIBUTION.REVENUEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.BENEFITGLDISTRIBUTION 
                inner join dbo.GLTRANSACTION GL1 on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.REVENUE on BENEFITGLDISTRIBUTION.REVENUEID = REVENUE.ID
                where GL2.POSTSTATUSCODE = 1
                and (REVENUE.ID is null or REVENUE.DONOTPOST = 0)

                union all

                select AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID,GL2.ID,GL2.TRANSACTIONTYPE,GL2.ACCOUNT,GL2.PROJECT,GL2.AMOUNT,GL2.REFERENCE,cast(1 as bit) as ISREVERSAL, cast(0 as bit) as ISADJUSTED
                from dbo.AUCTIONPURCHASEGLDISTRIBUTION
                inner join dbo.GLTRANSACTION GL1 on AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
                inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
                left join dbo.REVENUE on AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = REVENUE.ID
                where GL2.POSTSTATUSCODE = 1
                and (REVENUE.ID is null or REVENUE.DONOTPOST = 0)
            )