UFN_POSTTOGLPROCESS_GETUNPOSTEDDEPOSITTRANSACTIONS

CREATE function dbo.UFN_POSTTOGLPROCESS_GETUNPOSTEDDEPOSITTRANSACTIONS()
returns
    @GLDISTRIBUTION table (
        DEPOSITID uniqueidentifier,
        RECORDID uniqueidentifier,
        GLTRANSACTIONID uniqueidentifier,
        TRANSACTIONTYPE nvarchar(100),
        ACCOUNTSTRING  nvarchar(255),
        PROJECT  nvarchar(100),
        AMOUNT  money,
        REFERENCE  nvarchar(255),
        ISREVERSAL  bit,
        ISADJUSTED bit
    )
as
begin
    insert into @GLDISTRIBUTION(
        DEPOSITID,
        RECORDID, 
        GLTRANSACTIONID,
        TRANSACTIONTYPE,
        ACCOUNTSTRING,
        PROJECT,
        AMOUNT,
        REFERENCE,
        ISREVERSAL,
        ISADJUSTED
    )
    select    
        BANKACCOUNTDEPOSITPAYMENT.DEPOSITID,
        DISTRIBUTION.REVENUEID as RECORDID,
        GLTRANSACTION.ID as GLTRANSACTIONID,
        GLTRANSACTION.TRANSACTIONTYPE,
        GLTRANSACTION.ACCOUNT,
        GLTRANSACTION.PROJECT,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.REFERENCE,
        0 as ISREVERSAL,
        DISTRIBUTION.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 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

            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
            union all
        select GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID,GIFTFEEGLDISTRIBUTION.REVENUEID, case when GIFTFEEADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED 
        from dbo.GIFTFEEGLDISTRIBUTION
        left join dbo.GIFTFEEADJUSTMENT on GIFTFEEGLDISTRIBUTION.REVENUEID = GIFTFEEADJUSTMENT.REVENUEID and GIFTFEEADJUSTMENT.POSTSTATUSCODE = 1                        
        )
        as DISTRIBUTION on GLTRANSACTION.ID = DISTRIBUTION.GLTRANSACTIONID
    left join
        dbo.REVENUE on DISTRIBUTION.REVENUEID = REVENUE.ID
    inner join 
        dbo.REVENUEPAYMENTMETHOD on DISTRIBUTION.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID 
    inner join
        dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID 
    inner join 
        dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
    left outer join
        dbo.ADJUSTMENT on ADJUSTMENT.REVENUEID = REVENUE.ID and ADJUSTMENT.POSTSTATUSCODE = 1
    left outer join
        dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
    left outer join
        dbo.BENEFITADJUSTMENT on BENEFITADJUSTMENT.REVENUEID = REVENUE.ID and BENEFITADJUSTMENT.POSTSTATUSCODE = 1
    left outer join
        dbo.AUCTIONPURCHASEADJUSTMENT on AUCTIONPURCHASEADJUSTMENT.REVENUEID = REVENUE.ID and AUCTIONPURCHASEADJUSTMENT.POSTSTATUSCODE = 1
    where
        (BANKACCOUNTTRANSACTION.POSTSTATUSCODE = 1 or (REVENUEPOSTED.ID is not null and ADJUSTMENT.POSTSTATUSCODE = 1) or (REVENUEPOSTED.ID is not null and BENEFITADJUSTMENT.POSTSTATUSCODE = 1) or (REVENUEPOSTED.ID is not null and AUCTIONPURCHASEADJUSTMENT.POSTSTATUSCODE = 1))
    and
        (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0,1,2,10))
    and
        (GLTRANSACTION.POSTSTATUSCODE = 1)

    union all

    select    
        BANKACCOUNTDEPOSITPAYMENT.DEPOSITID,
        REVENUE.ID as RECORDID,
        GL1.ID as GLTRANSACTIONID,
        GL1.TRANSACTIONTYPE,
        GL1.ACCOUNT,
        GL1.PROJECT,
        GL1.AMOUNT,
        left('Reversal - ' + GL1.REFERENCE, 255) as REFERENCE,
        1 as ISREVERSAL,
        0 as ISADJUSTED
    from
        dbo.GLTRANSACTION GL1
    inner join 
        dbo.GLTRANSACTION on GLTRANSACTION.ID = GL1.REVERSEDGLTRANSACTIONID
    inner join
        dbo.REVENUEGLDISTRIBUTION on REVENUEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
    inner join
        dbo.REVENUE on REVENUE.ID = REVENUEGLDISTRIBUTION.REVENUEID
    inner join 
        dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID 
    inner join
        dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID 
    inner join 
        dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
    where
        (BANKACCOUNTTRANSACTION.POSTSTATUSCODE != 2)
    and
        (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0,1,2,10))
    and
        (GL1.POSTSTATUSCODE = 1)

    union all

    select 
        BANKACCOUNTDEPOSITCORRECTION.DEPOSITID,
        BANKACCOUNTDEPOSITCORRECTION.ID as RECORDID,
        GLTRANSACTION.ID as GLTRANSACTIONID,
        GLTRANSACTION.TRANSACTIONTYPE,
        GLTRANSACTION.ACCOUNT,
        GLTRANSACTION.PROJECT,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.REFERENCE,
        0 as ISREVERSAL,
        0 as ISADJUSTED
    from
        dbo.BANKACCOUNTDEPOSITCORRECTION
    inner join
        dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION on BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BANKACCOUNTDEPOSITCORRECTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
    inner join
        dbo.GLTRANSACTION on GLTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.GLTRANSACTIONID
    inner join
        dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTION.DEPOSITID
    where
        GLTRANSACTION.POSTSTATUSCODE = 1
    and
        BANKACCOUNTTRANSACTION.POSTSTATUSCODE = 1

    -- 10/12/2009 Deposited refund payments

    union all
    select BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID, CREDIT.ID RECORDID, GL1.ID, GL1.TRANSACTIONTYPE, GL1.ACCOUNT, GL1.PROJECT, GL1.AMOUNT, GL1.REFERENCE, 0 as ISREVERSAL, 0 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
    inner join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on BANKACCOUNTDEPOSITCREDITPAYMENT.ID = CREDITPAYMENT.ID
    where CREDIT.TYPECODE = 0 
        and GL1.POSTSTATUSCODE = 1

  -- get reversals for benefits in deposit payments

  union all
    select    
        BANKACCOUNTDEPOSITPAYMENT.DEPOSITID,
        REVENUE.ID as RECORDID,
        GL1.ID as GLTRANSACTIONID,
        GL1.TRANSACTIONTYPE,
        GL1.ACCOUNT,
        GL1.PROJECT,
        GL1.AMOUNT,
        left('Reversal - ' + GL1.REFERENCE, 255) as REFERENCE,
        1 as ISREVERSAL,
        0 as ISADJUSTED
    from
        dbo.GLTRANSACTION GL1
    inner join 
        dbo.GLTRANSACTION on GLTRANSACTION.ID = GL1.REVERSEDGLTRANSACTIONID
    inner join
        dbo.BENEFITGLDISTRIBUTION on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
    inner join
        dbo.REVENUE on REVENUE.ID = BENEFITGLDISTRIBUTION.REVENUEID
    inner join 
        dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID 
    inner join
        dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID 
    inner join 
        dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
    where
        (BANKACCOUNTTRANSACTION.POSTSTATUSCODE != 2)
    and
        (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0,1,2,10))
    and
        (GL1.POSTSTATUSCODE = 1)  

    return;
end