USP_POSTTOGLPROCESS_UPDATEREVENUEWITHPOSTDETAIL

            CREATE proc dbo.USP_POSTTOGLPROCESS_UPDATEREVENUEWITHPOSTDETAIL(@TEMPTABLE nvarchar(255), @POSTINGOPTION tinyint = 0, @CHANGEAGENTID uniqueidentifier)
            as
                set nocount on;


                /* Insert into the Revenue record posted GL Account and Amount */
                declare @SQL nvarchar(4000);
                begin transaction UPDATEREVENUE;
                begin try
                    set @SQL = 'insert into dbo.REVENUEGLDISTRIBUTION (REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, DEBITACCOUNT, CREDITACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                    select REVENUE.ID, D.GLPAYMENTMETHODREVENUETYPEMAPPINGID, D.ACCOUNTSTRING, C.ACCOUNTSTRING, D.PROJECT, D.REFERENCE, D.AMOUNT, @DYNCHANGEAGENTID, @DYNCHANGEAGENTID, getdate(), getdate()
                                        from dbo.REVENUE 
                                        inner join ' + @TEMPTABLE + ' as C on REVENUE.ID = C.REVENUEID and UPPER(C.DEBITCREDIT) = ''C''
                                        inner join ' + @TEMPTABLE + ' as D on REVENUE.ID = D.REVENUEID and UPPER(D.DEBITCREDIT) = ''D''
                                        where not exists(select ID from dbo.REVENUEGLDISTRIBUTION where REVENUEGLDISTRIBUTION.REVENUEID = REVENUE.ID) 
                                                and    C.ISREVERSAL <> ''1'' and D.ISREVERSAL <> ''1'' 
                                                and C.WRITEOFFID is null and D.WRITEOFFID is null
                                                and    C.ISSOLDPROPERTYORSTOCKORGIFTINKIND = ''0'' and D.ISSOLDPROPERTYORSTOCKORGIFTINKIND = ''0''
                                                and C.GROUPID = D.GROUPID 
                                                and C.GLPAYMENTMETHODREVENUETYPEMAPPINGID = D.GLPAYMENTMETHODREVENUETYPEMAPPINGID';

                    exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;

                    set @SQL = 'insert into dbo.STOCKSALEGLDISTRIBUTION (STOCKDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, DEBITACCOUNT, CREDITACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                    select REVENUE.ID, D.GLPAYMENTMETHODREVENUETYPEMAPPINGID, D.ACCOUNTSTRING, C.ACCOUNTSTRING, D.PROJECT, D.REFERENCE, D.AMOUNT, @DYNCHANGEAGENTID, @DYNCHANGEAGENTID, getdate(), getdate()
                                        from dbo.REVENUE 
                                        inner join ' + @TEMPTABLE + ' as C on REVENUE.ID = C.REVENUEID and UPPER(C.DEBITCREDIT) = ''C''
                                        inner join ' + @TEMPTABLE + ' as D on REVENUE.ID = D.REVENUEID and UPPER(D.DEBITCREDIT) = ''D''
                                        inner join dbo.STOCKDETAIL on REVENUE.ID = STOCKDETAIL.ID
                                        where not exists(select ID from dbo.STOCKSALEGLDISTRIBUTION where STOCKSALEGLDISTRIBUTION.STOCKDETAILID = STOCKDETAIL.ID) 
                                                and    C.ISREVERSAL <> ''1'' and D.ISREVERSAL <> ''1'' 
                                                and C.WRITEOFFID is null and D.WRITEOFFID is null
                                                and    C.ISSOLDPROPERTYORSTOCKORGIFTINKIND = ''1'' and D.ISSOLDPROPERTYORSTOCKORGIFTINKIND = ''1''
                                                and C.GROUPID = D.GROUPID
                                                and C.GLPAYMENTMETHODREVENUETYPEMAPPINGID = D.GLPAYMENTMETHODREVENUETYPEMAPPINGID';

                    exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;

                    set @SQL = 'insert into dbo.PROPERTYDETAILGLDISTRIBUTION (PROPERTYDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, DEBITACCOUNT, CREDITACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                    select REVENUE.ID, D.GLPAYMENTMETHODREVENUETYPEMAPPINGID, D.ACCOUNTSTRING, C.ACCOUNTSTRING, D.PROJECT, D.REFERENCE, D.AMOUNT, @DYNCHANGEAGENTID, @DYNCHANGEAGENTID, getdate(), getdate()
                                        from dbo.REVENUE 
                                        inner join ' + @TEMPTABLE + ' as C on REVENUE.ID = C.REVENUEID and UPPER(C.DEBITCREDIT) = ''C''
                                        inner join ' + @TEMPTABLE + ' as D on REVENUE.ID = D.REVENUEID and UPPER(D.DEBITCREDIT) = ''D''
                                        inner join dbo.PROPERTYDETAIL on REVENUE.ID = PROPERTYDETAIL.ID
                                        where not exists(select ID from dbo.PROPERTYDETAILGLDISTRIBUTION where PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = PROPERTYDETAIL.ID) 
                                                and    C.ISREVERSAL <> ''1'' and D.ISREVERSAL <> ''1'' 
                                                and C.WRITEOFFID is null and D.WRITEOFFID is null
                                                and    C.ISSOLDPROPERTYORSTOCKORGIFTINKIND = ''1'' and D.ISSOLDPROPERTYORSTOCKORGIFTINKIND = ''1''
                                                and C.GROUPID = D.GROUPID
                                                and C.GLPAYMENTMETHODREVENUETYPEMAPPINGID = D.GLPAYMENTMETHODREVENUETYPEMAPPINGID';

                    exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;

                    set @SQL = 'insert into dbo.GIFTINKINDSALEGLDISTRIBUTION (GIFTINKINDPAYMENTMETHODDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, DEBITACCOUNT, CREDITACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                    select REVENUE.ID, D.GLPAYMENTMETHODREVENUETYPEMAPPINGID, D.ACCOUNTSTRING, C.ACCOUNTSTRING, D.PROJECT, D.REFERENCE, D.AMOUNT, @DYNCHANGEAGENTID, @DYNCHANGEAGENTID, getdate(), getdate()
                                        from dbo.REVENUE 
                                        inner join ' + @TEMPTABLE + ' as C on REVENUE.ID = C.REVENUEID and UPPER(C.DEBITCREDIT) = ''C''
                                        inner join ' + @TEMPTABLE + ' as D on REVENUE.ID = D.REVENUEID and UPPER(D.DEBITCREDIT) = ''D''
                                        inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on REVENUE.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
                                        where not exists(select ID from dbo.GIFTINKINDSALEGLDISTRIBUTION where GIFTINKINDSALEGLDISTRIBUTION.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDPAYMENTMETHODDETAIL.ID) 
                                                and    C.ISREVERSAL <> ''1'' and D.ISREVERSAL <> ''1'' 
                                                and C.WRITEOFFID is null and D.WRITEOFFID is null
                                                and    C.ISSOLDPROPERTYORSTOCKORGIFTINKIND = ''1'' and D.ISSOLDPROPERTYORSTOCKORGIFTINKIND = ''1''
                                                and C.GROUPID = D.GROUPID
                                                and C.GLPAYMENTMETHODREVENUETYPEMAPPINGID = D.GLPAYMENTMETHODREVENUETYPEMAPPINGID';

                    exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;

                    /* Insert into the WriteOff record posted GL Account and Amount */
                    set @SQL = 'insert into dbo.WRITEOFFGLDISTRIBUTION (WRITEOFFID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, DEBITACCOUNT, CREDITACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                    select WRITEOFF.ID, D.GLPAYMENTMETHODREVENUETYPEMAPPINGID, D.ACCOUNTSTRING, C.ACCOUNTSTRING, D.PROJECT, D.REFERENCE, D.AMOUNT, @DYNCHANGEAGENTID, @DYNCHANGEAGENTID, getdate(), getdate()
                                        from dbo.WRITEOFF
                                        inner join ' + @TEMPTABLE + ' as C on WRITEOFF.ID = C.WRITEOFFID and UPPER(C.DEBITCREDIT) = ''C''
                                        inner join ' + @TEMPTABLE + ' as D on WRITEOFF.ID = D.WRITEOFFID and UPPER(D.DEBITCREDIT) = ''D''
                                        where not exists(select ID from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFGLDISTRIBUTION.WRITEOFFID = WRITEOFF.ID) 
                                                and C.REVENUESPLITID = D.REVENUESPLITID
                                                and C.GROUPID = D.GROUPID
                                                and C.GLPAYMENTMETHODREVENUETYPEMAPPINGID = D.GLPAYMENTMETHODREVENUETYPEMAPPINGID';
                    exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
                end try
                begin catch
                    if @@TRANCOUNT > 0
                        rollback transaction UPDATEREVENUE;

                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                if @@TRANCOUNT > 0
                    commit transaction UPDATEREVENUE;

                return 0;