UFN_PREPOST_DATA
Retrieve the pre-post GL transaction data.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(36) | IN |
Definition
Copy
CREATE function [dbo].[UFN_PREPOST_DATA](
@ID as nvarchar(36)
)
returns
@PrePostData_Table
table(
ID uniqueidentifier,
REVENUEID nvarchar(100),
DEPOSITCORRECTIONID nvarchar(150),
ADJUSTMENTID nvarchar(100),
ACCOUNT nvarchar(100),
LOOKUPID nvarchar(100),
POSTDATE datetime,
TRANSACTIONTYPECODE tinyint,
AMOUNT money,
JOURNAL nvarchar(50),
REFERENCE nvarchar(max),
DEPOSITID nvarchar(36),
DEPOSITLINKID nvarchar(150),
DEPOSITNUMBER integer,
PERIODID nvarchar(36),
SUMMARYDATE datetime,
GROUPBY nvarchar(50),
ISEXCEPTION bit,
EXCEPTIONREASON nvarchar(max),
TRANSACTIONAMOUNT money,
ORGANIZATIONAMOUNT money,
TRANSACTIONISOCURRENCYCODE nvarchar(3),
TRANSACTIONCURRENCYSYMBOL nvarchar(5),
TRANSACTIONCURRENCYDECIMALDIGITS tinyint,
TRANSACTIONCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint,
REVENUEIDGUID uniqueidentifier,
ADJUSTMENTIDGUID uniqueidentifier,
CREDITID nvarchar(100)
)
as
begin
declare @PostOptions tinyint -- 0=All, 1=Selected, 2=Adjustments/Reversals only, 3=None
declare @RevenueMethod tinyint
declare @CashMethod tinyint
declare @ARMethod tinyint
declare @RevaluationGainLossMethod tinyint
declare @SummarizeBy tinyint -- 0=Date / 1=Period
declare @BasicGL bit;
declare @DepostiPostingOption tinyint; --0=All, 1=Selected, 2=None, 3=Specific bank accounts, 4=Specific deposit templates
declare @AdjustmentPostingOption tinyint; --0=All, 1=Selected, 2=None
declare @RequireDeposit as bit
declare @PostDateFilter date;
declare @PostDateUpToCode tinyint; --0 - Today, 1 - Yesterday, 2 - End of last week, 3 - End of this week, 4 - End of last period, 5 - End of this period, 6 - Specific date
declare @OWNERID uniqueidentifier;
declare @BUSINESSPROCESSCATALOGID uniqueidentifier;
select @OWNERID = OWNERID,
@BUSINESSPROCESSCATALOGID = BUSINESSPROCESSCATALOGID
from dbo.BUSINESSPROCESSINSTANCE
where BUSINESSPROCESSPARAMETERSETID = convert(uniqueidentifier, @ID);
declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);
declare @APPUSER_IN_NONRACROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@OWNERID);
declare @AllowedSites table (SITEID uniqueidentifier, SITESECURITYMODE int) --For checking site security
set @RevenueMethod = 0
set @CashMethod = 1
set @ARMethod = 1
set @RevaluationGainLossMethod = 1
set @SummarizeBy = 0
--select @BasicGL = CASE WHEN ID is null THEN 0 ELSE 1 END
--from dbo.INSTALLEDPRODUCTLIST where ID = '0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7';
select @BasicGL = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7');
declare @DEFAULTGLACCOUNTID uniqueidentifier;
select
@PostOptions=POSTINGOPTIONCODE,
@DepostiPostingOption = DEPOSITPOSTINGOPTIONCODE,
@AdjustmentPostingOption = ADJUSTMENTPOSTINGOPTIONCODE,
@RevenueMethod=REVENUEPOSTMETHODCODE,
@CashMethod=CASHPOSTMETHODCODE,
@ARMethod=ARPOSTMETHODCODE,
@SummarizeBy=SUMMARIZECODE,
@RevaluationGainLossMethod = REVALUATIONGAINLOSSPOSTMETHODCODE,
@RequireDeposit = isnull(PDACCOUNTSYSTEM.REQUIREDPOSIT, 0),
@DEFAULTGLACCOUNTID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID
,@PostDateUpToCode = T1.POSTDATEUPTOCODE
,@PostDateFilter = T1.SPECIFICPOSTDATEUPTO
from
POSTTOGLPROCESS as T1
inner join POSTTOGLPROCESSDETAIL as T2 on T1.ID = T2.ID
left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = T1.PDACCOUNTSYSTEMID
where
T1.ID = @ID
select @PostDateFilter = case @PostDateUpToCode
when 0 then cast(getdate() as date)
when 1 then cast(DATEADD("D", -1, getdate()) as date)
when 2 then cast(DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 5) as date)
when 3 then cast(DATEADD(wk, DATEDIFF(wk, 5, GETDATE()), 5) as date)
when 4 then cast(DATEADD("D", -1, P.STARTDATE) as date)
when 5 then cast(P.ENDDATE as date)
when 6 then @PostDateFilter end
from dbo.GLFISCALPERIOD P
where P.STARTDATE <= cast(GETDATE() as date) and cast(GETDATE() as date) <= P.ENDDATE;
declare @ORGAMOUNTORIGINCODE tinyint;
declare @ORGCURRENCYID uniqueidentifier;
select top 1 @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
set @ORGCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @Temp table (ID uniqueidentifier);
--kwb This needed to be moved outside of the revenue transaction section because it is also used by deposits
if @ISADMIN != 1 and (@PostOptions != 3 or (@DepostiPostingOption != 2 and @BasicGL = 1)) --Only needed if not an administrator
insert into @AllowedSites (SITEID, SITESECURITYMODE)
select SITEID, SITESECURITYMODE
from
dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BUSINESSPROCESS as SECURITYVIEW
left join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = SECURITYVIEW.APPUSERID and SITEPERMISSION.SYSTEMROLEID = SECURITYVIEW.SYSTEMROLEID and (SECURITYVIEW.SITESECURITYMODE = 2 or SECURITYVIEW.SITESECURITYMODE = 3)
where
SECURITYVIEW.APPUSERID = @OWNERID and
SECURITYVIEW.BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSCATALOGID and
SECURITYVIEW.GRANTORDENY=1
if @PostOptions != 3 or (@DepostiPostingOption != 2 and @BasicGL = 1)
begin
declare @UNPOSTEDDEPOSITTRANSACTIONS table (RecordID uniqueidentifier, GLTransactionID uniqueidentifier primary key, DepositID uniqueidentifier, IsReversal bit, Reference nvarchar(255))
insert into @UNPOSTEDDEPOSITTRANSACTIONS (RecordID, GLTransactionID, DepositID, IsReversal, Reference)
select RecordID, GLTransactionID, DepositID, IsReversal, Reference
from dbo.UFN_POSTTOGLPROCESS_GETUNPOSTEDDEPOSITTRANSACTIONS_2(@ID)
declare @GRANTEDCONSTITIDSFORBUSINESSPROCESS table (ID uniqueidentifier)
insert into @GRANTEDCONSTITIDSFORBUSINESSPROCESS (ID)
select ID
from dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BUSINESSPROCESSCATALOGID)
end
-- Insert all revenue transactions.
if @PostOptions != 3
begin
declare @UNPOSTEDGLTRANSACTIONS table (RevenueID uniqueidentifier, GLTransactionID uniqueidentifier primary key, Account nvarchar(101), IsReversal bit, IsAdjusted bit)
insert into @UNPOSTEDGLTRANSACTIONS (RevenueID, GLTransactionID, Account, IsReversal, IsAdjusted)
select RevenueID, GLTransactionID, Account, IsReversal, IsAdjusted
from dbo.UFN_POSTTOGLPROCESS_GETUNPOSTEDGLTRANSACTIONS_2(@ID)
declare @UNLINKED table (GLTransactionID uniqueidentifier primary key)
insert into @UNLINKED (GLTransactionID)
select GLTransactionID
from dbo.UFN_REVENUE_PAYMENT_UNLINKED()
insert @PrePostData_Table
Select t1.ID,
'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),f1.REVENUEID) as REVENUEID, null as DEPOSITCORRECTIONID, null as ADJUSTMENTID,
isnull(f1.ACCOUNT, JOURNALENTRY_EXT.ACCOUNT) as ACCOUNT, isnull(FT.CALCULATEDUSERDEFINEDID,'') as LOOKUPID,
LI.POSTDATE, --kwb bug 224302 Don't case out post date for reversals based on summary option; reversals are always posted in detail
t1.TRANSACTIONTYPECODE, t1.BASEAMOUNT, JOURNALENTRY_EXT.JOURNAL, left(CASE WHEN LI.TYPECODE = 1 then 'Reversal - ' else '' END + t1.COMMENT, 255),
Cast(t4.DEPOSITID as nvarchar(36)) DEPOSITID, null as DEPOSITLINKID, null as DEPOSITNUMBER, Cast(t5.ID as nvarchar(36)) PERIODID,
case dbo.UFN_POSTSUMMARYTYPE(f1.ISREVERSAL, t1.TRANSACTIONTYPECODE, coalesce(t3.REVENUETRANSACTIONTYPECODE, case JOURNALENTRY_EXT.TABLENAMECODE when 10 then PROPERTYREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE else null end, 255), coalesce(t3.APPLICATIONCODE, case JOURNALENTRY_EXT.TABLENAMECODE when 10 then PROPERTYREVENUETYPEMAPPING.APPLICATIONCODE else null end, 255), coalesce(t3.PAYMENTMETHODCODE, case JOURNALENTRY_EXT.TABLENAMECODE when 10 then PROPERTYREVENUETYPEMAPPING.PAYMENTMETHODCODE else null end, 255))
when 1 then
case @RevenueMethod when 0 then cast(LI.POSTDATE as date) else case @SummarizeBy when 0 then cast(LI.POSTDATE as date) else cast(t5.ENDDATE as date) end end
when 2 then
case @CashMethod when 0 then cast(LI.POSTDATE as date) else case @SummarizeBy when 0 then cast(LI.POSTDATE as date) else cast(t5.ENDDATE as date) end end
when 3 then
case @ARMethod when 0 then cast(LI.POSTDATE as date) else case @SummarizeBy when 0 then cast(LI.POSTDATE as date) else cast(t5.ENDDATE as date) end end
when 4 then
case JOURNALENTRY_EXT.TABLENAMECODE
when 1 then cast(LI.POSTDATE as date)
when 9 then cast(LI.POSTDATE as date)
when 12 then cast(LI.POSTDATE as date)
when 13 then cast(LI.POSTDATE as date)
when 11 then cast(LI.POSTDATE as date)
when 8 then cast(LI.POSTDATE as date)
when 6 then cast(LI.POSTDATE as date)
else case @RevaluationGainLossMethod when 0 then cast(LI.POSTDATE as date) else case @SummarizeBy when 0 then cast(LI.POSTDATE as date) else cast(t5.ENDDATE as date) end end
end
else
cast(LI.POSTDATE as date)
end SUMMARYDATE,
case dbo.UFN_POSTSUMMARYTYPE(f1.ISREVERSAL, t1.TRANSACTIONTYPECODE, coalesce(t3.REVENUETRANSACTIONTYPECODE, case JOURNALENTRY_EXT.TABLENAMECODE when 10 then PROPERTYREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE else null end, 255), coalesce(t3.APPLICATIONCODE, case JOURNALENTRY_EXT.TABLENAMECODE when 10 then PROPERTYREVENUETYPEMAPPING.APPLICATIONCODE else null end, 255), coalesce(t3.PAYMENTMETHODCODE, case JOURNALENTRY_EXT.TABLENAMECODE when 10 then PROPERTYREVENUETYPEMAPPING.PAYMENTMETHODCODE else null end, 255))
when 1 then (case @RevenueMethod when 0 then '0_' + cast(f1.REVENUEID as nvarchar(36)) when 2 then '1_' + cast(t3.APPLICATIONCODE as nvarchar(36)) when 3 then '1_' + isnull(Cast(t4.DEPOSITID as nvarchar(36)), '') else '1' end)
when 2 then (case @CashMethod when 0 then '0_' + cast(f1.REVENUEID as nvarchar(36)) when 1 then '2_' + isnull(Cast(t4.DEPOSITID as nvarchar(36)), '') else '2' end)
when 3 then (case @ARMethod when 0 then '0_' + cast(f1.REVENUEID as nvarchar(36)) else '3' end)
when 4 then case JOURNALENTRY_EXT.TABLENAMECODE
when 1 then '0_' + cast(f1.REVENUEID as nvarchar(36)) -- These should only be planned gifts
when 9 then '0_' + cast(f1.REVENUEID as nvarchar(36)) --PlannedGiftGLDistribution
when 12 then '0_' + cast(f1.REVENUEID as nvarchar(36)) --WriteOffGLDistribution
when 13 then '0_' + cast(f1.REVENUEID as nvarchar(36)) --GiftInKindGLDistribution
when 11 then '0_' + cast(f1.REVENUEID as nvarchar(36)) --StockSaleGLDistribution
when 8 then '0_' + cast(f1.REVENUEID as nvarchar(36)) --GiftFeeGLDistribution
when 6 then case --CreditGLDistribution
when JOURNALENTRY_EXT.CREDITPAYMENTID is null then case --Discount
when t1.TRANSACTIONTYPECODE = 0 then case @ARMethod
when 0 then '0_' + cast(f1.REVENUEID as nvarchar(36))
else '3'
end
else case @RevenueMethod
when 0 then '0_' + cast(f1.REVENUEID as nvarchar(36))
when 2 then '1_' + cast(t3.APPLICATIONCODE as nvarchar(36))
when 3 then '1_' + isnull(Cast(t4.DEPOSITID as nvarchar(36)), '')
else '1'
end
end
else case --Refund
when t1.TRANSACTIONTYPECODE = 0 then case @RevenueMethod
when 0 then '0_' + CAST(isnull(JOURNALENTRY_EXT.DISTRIBUTIONTABLEID, JOURNALENTRY_EXT.ID) as nvarchar(36))
else '1'
end
else case @CashMethod
when 0 then '0_' + CAST(isnull(JOURNALENTRY_EXT.DISTRIBUTIONTABLEID, JOURNALENTRY_EXT.ID) as nvarchar(36))
else '2'
end
end
end
else case @RevaluationGainLossMethod
when 0 then '0_' + cast(f1.REVENUEID as nvarchar(36))
else '4'
end
end
else '0' + CAST(FT.ID as nvarchar(36))
end + '_' +
cast(case @SummarizeBy when 0 then cast(LI.POSTDATE as date) else cast(t5.ENDDATE as date) end as nvarchar(10)) GROUPBY,
t5.CLOSED as ISEXCEPTION,
CASE WHEN t5.CLOSED = 1 THEN 'Post date must be in an open fiscal period' ELSE NULL END as EXCEPTIONREASON,
t1.TRANSACTIONAMOUNT,
t1.ORGAMOUNT as ORGANIZATIONAMOUNT,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE,
f1.REVENUEID, null,
'http://www.blackbaud.com/CREDITID?CREDITID=' + CONVERT(nvarchar(36),CREDITPAYMENT.CREDITID) as CREDITID
from dbo.JOURNALENTRY as t1
inner join dbo.JOURNALENTRY_EXT on t1.ID = JOURNALENTRY_EXT.ID
inner join @UNPOSTEDGLTRANSACTIONS as f1 On t1.ID = f1.GLTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = t1.FINANCIALTRANSACTIONLINEITEMID
inner join dbo.GLFISCALPERIOD t5 On cast(LI.POSTDATE as date) between t5.STARTDATE and t5.ENDDATE
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
left join dbo.FINANCIALTRANSACTION PARENT on PARENT.ID = FT.PARENTID and FT.TYPECODE = 26
left join dbo.CURRENCY on CURRENCY.ID = t1.TRANSACTIONCURRENCYID
left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as t3 on JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID = t3.ID and JOURNALENTRY_EXT.TABLENAMECODE = 1 and t3.ID not in (select ID from GLPAYMENTMETHODREVENUETYPEMAPPING where REVENUETRANSACTIONTYPECODE = 4 and APPLICATIONCODE = 0)
left outer join dbo.BANKACCOUNTDEPOSITPAYMENT t4 on f1.REVENUEID = t4.ID
left outer join dbo.REVENUEPAYMENTMETHOD on f1.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
left outer join dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING PROPERTYREVENUETYPEMAPPING on JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID = PROPERTYREVENUETYPEMAPPING.ID and JOURNALENTRY_EXT.TABLENAMECODE = 10
left outer join dbo.CREDITPAYMENT on CREDITPAYMENT.ID = f1.REVENUEID
where
(
(@PostOptions != 2)
or
(
(f1.ISADJUSTED = 1 or f1.ISREVERSAL = 1)
and
(JOURNALENTRY_EXT.TABLENAMECODE != 14 or (JOURNALENTRY_EXT.TABLENAMECODE = 14 and LI.POSTSTATUSCODE = 2))
)
)
and (@RequireDeposit = 0 or t1.ID not in (Select GLTRANSACTIONID from @UNLINKED))
and JOURNALENTRY_EXT.TABLENAMECODE != 5
and ((@BasicGL = 0) or (@RequireDeposit = 0 and T4.DEPOSITID is null) or (not (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0,1,2)
or (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10
and OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID not in
(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD)
)
) --or GIFTAIDGLDISTRIBUTION.ID is not null --below still sucks, look at again
or (JOURNALENTRY_EXT.TABLENAMECODE = 7 AND JOURNALENTRY_EXT.ID not in (select GLTRANSACTIONID from @UNPOSTEDDEPOSITTRANSACTIONS))
or (f1.GLTRANSACTIONID in (select gl1.ID from dbo.JOURNALENTRY_EXT gl1 inner join dbo.JOURNALENTRY_EXT gl2 on gl1.REVERSEDGLTRANSACTIONID=gl2.ID where gl2.TABLENAMECODE = 7))
or (FT.TYPECODE in (1,15,20))
)
or (FT.DELETEDON is not null)
or (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE is null))
and(@ISADMIN = 1 or f1.REVENUEID is null
or(
(@APPUSER_IN_NONRACROLE = 1
or dbo.UFN_CONSTITID_MAPFROM_REVENUETRANSACTIONID(f1.REVENUEID) IN (
select ID
from @GRANTEDCONSTITIDSFORBUSINESSPROCESS
)
)
and exists (select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(f1.REVENUEID) REVSITES inner join @AllowedSites t2
on (REVSITES.SITEID = t2.SITEID and (t2.SITESECURITYMODE = 2 or t2.SITESECURITYMODE = 3))
or t2.SITESECURITYMODE = 0
or (t2.SITESECURITYMODE = 1 and REVSITES.SITEID is null)
)
)
)
and ((@PostOptions != 4)
or (@PostOptions = 4 and isnull(PARENT.TYPECODE, FT.TYPECODE) in (select FILTER.TYPECODE from dbo.POSTTOGLPROCESSTRANSACTIONTYPEFILTER FILTER where FILTER.POSTTOGLPROCESSID = @ID)))
option (recompile);
insert @PrePostData_Table
Select t1.ID,
'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),f1.REVENUEID) as REVENUEID,
null as DEPOSITCORRECTIONID,
null as ADJUSTMENTID,
isnull(f1.ACCOUNT, JOURNALENTRY_EXT.ACCOUNT) as ACCOUNT,
isnull(FT.CALCULATEDUSERDEFINEDID,'') as LOOKUPID,
LI.POSTDATE,
t1.TRANSACTIONTYPECODE,
t1.BASEAMOUNT as AMOUNT,
JOURNALENTRY_EXT.JOURNAL,
left(CASE WHEN LI.TYPECODE = 1 then 'Reversal - ' else '' END + t1.COMMENT, 255),
null as DEPOSITID,
null as DEPOSITLINKID,
null as DEPOSITNUMBER,
Cast(t5.ID as nvarchar(36)) PERIODID,
case @RevenueMethod when 0 then cast(LI.POSTDATE as date) else case @SummarizeBy when 0 then cast(LI.POSTDATE as date) else cast(t5.ENDDATE as date) end end SUMMARYDATE,
case @RevenueMethod when 0 then '0_' + cast(f1.REVENUEID as nvarchar(36)) when 2 then '1_' + cast(f1.REVENUEID as nvarchar(36)) when 3 then '1_' + isnull(Cast(BADP.DEPOSITID as nvarchar(36)), '') else '1' end
+ '_' +
cast(case @SummarizeBy when 0 then cast(LI.POSTDATE as date) else cast(t5.ENDDATE as date) end as nvarchar(10)) GROUPBY,
0 as ISEXCEPTION, '' as EXCEPTIONREASON,
t1.TRANSACTIONAMOUNT,
t1.ORGAMOUNT as ORGANIZATIONAMOUNT,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE,
f1.REVENUEID, null, null
from dbo.JOURNALENTRY as t1
inner join dbo.JOURNALENTRY_EXT on T1.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 5 and isnull(JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID, '00000000-0000-0000-0000-000000000000') not in (select ID from GLPAYMENTMETHODREVENUETYPEMAPPING where REVENUETRANSACTIONTYPECODE = 4 and APPLICATIONCODE = 0)
inner join @UNPOSTEDGLTRANSACTIONS as f1 On t1.ID = f1.GLTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = t1.FINANCIALTRANSACTIONLINEITEMID
inner join dbo.GLFISCALPERIOD t5 On LI.POSTDATE between t5.STARTDATE and t5.ENDDATE
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
left join dbo.FINANCIALTRANSACTION PARENT on PARENT.ID = FT.PARENTID and FT.TYPECODE = 26
left join dbo.CURRENCY on CURRENCY.ID = t1.TRANSACTIONCURRENCYID
left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on FT.ID = BADP.ID
left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as t3 on JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID = t3.ID
left outer join dbo.REVENUEPAYMENTMETHOD on f1.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
left outer join dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
where ((@PostOptions != 2) or (f1.ISADJUSTED = 1 or f1.ISREVERSAL = 1)) and (t1.ID not in (Select GLTRANSACTIONID from @UNLINKED) or @RequireDeposit = 0)
and ((@BasicGL = 0) or (not (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0,1,2)
or (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10
and OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID not in
(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD)
)
)
or @RequireDeposit = 0)
or (REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE is null)
)
and ((@PostOptions != 4)
or (@PostOptions = 4 and isnull(PARENT.TYPECODE, FT.TYPECODE) in (select FILTER.TYPECODE from dbo.POSTTOGLPROCESSTRANSACTIONTYPEFILTER FILTER where FILTER.POSTTOGLPROCESSID = @ID)))
option (recompile);
-- Find all non-depositable records that don't have an exchange rate when they should.
update
@PrePostData_Table
set
ISEXCEPTION = 1,
EXCEPTIONREASON = case when (JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
and ((@ORGAMOUNTORIGINCODE = 0 and V.BASECURRENCYID <> @ORGCURRENCYID and nullif(JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID,'00000000-0000-0000-0000-000000000000') is null)
or (@ORGAMOUNTORIGINCODE = 1 and JOURNALENTRY.TRANSACTIONCURRENCYID <> @ORGCURRENCYID and nullif(JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID,'00000000-0000-0000-0000-000000000000') is null))
then 'Base and organization exchange rates do not exist for this distribution.'
else
case when (JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
then 'Base exchange rate does not exist for this distribution.'
else 'Organization exchange rate does not exist for this distribution.'
end
end
from
@PrePostData_Table as PREPOSTDATA
inner join dbo.JOURNALENTRY on PREPOSTDATA.ID = JOURNALENTRY.ID
inner join dbo.JOURNALENTRY_EXT ON JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
where
JOURNALENTRY.TRANSACTIONCURRENCYID is not null
and JOURNALENTRY.TRANSACTIONAMOUNT <> 0
and
(
(JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
or
(
JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null
and
(
(
@ORGAMOUNTORIGINCODE = 0
and V.BASECURRENCYID <> @ORGCURRENCYID
)
or
(
@ORGAMOUNTORIGINCODE = 1
and JOURNALENTRY.TRANSACTIONCURRENCYID <> @ORGCURRENCYID
)
)
)
)
end
if @DepostiPostingOption != 2 and @BasicGL = 1
begin
--insert any records that are linked to a deposit
insert into @PrePostData_Table
select tf.GLTRANSACTIONID,
CASE WHEN FINANCIALTRANSACTION.ID is not null then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), tf.RECORDID) ELSE NULL END as REVENUEID,
CASE WHEN BANKACCOUNTDEPOSITCORRECTION.ID is not null then 'http://www.blackbaud.com/DEPOSITCORRECTIONID?DEPOSITCORRECTIONID=' + CONVERT(nvarchar(36), tf.RECORDID) ELSE NULL END as DEPOSITCORRECTIONID,
null as ADJUSTMENTID,
isnull(case
when T.GLACCOUNTID is null
then T_EXT.ACCOUNT
else
case when T_EXT.ACCOUNT != GLACCOUNT.ACCOUNTNUMBER
and len(T_EXT.ACCOUNT) > 0
then T_EXT.ACCOUNT
else
GLACCOUNT.ACCOUNTNUMBER
end
end,'') as ACCOUNT
,case when FINANCIALTRANSACTION.ID is not null then isnull(FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID,'')
else case when BANKACCOUNTDEPOSITCORRECTION.ID is not null then convert(nvarchar(36),BAT.TRANSACTIONNUMBER) + ' - ' + BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPE
else case when CREDIT.ID is not null then CREDIT.TYPE + ' - ' + convert(nvarchar(36), SALESORDER.SEQUENCEID)
else '' end
end
end as [LOOKUPID],
case when Tf.ISREVERSAL = 1
then
case dbo.UFN_REVERSALPOSTSUMMARYTYPE(T.ID)
when 1 then (case @RevenueMethod when 0 then BAT.POSTDATE else case @SummarizeBy when 0 then cast(BAT.POSTDATE as date) else cast(GLFISCALPERIOD.ENDDATE as date) end end)
when 2 then (case @CashMethod when 0 then BAT.POSTDATE else case @SummarizeBy when 0 then cast(BAT.POSTDATE as date) else cast(GLFISCALPERIOD.ENDDATE as date) end end)
when 3 then (case @ARMethod when 0 then BAT.POSTDATE else case @SummarizeBy when 0 then cast(BAT.POSTDATE as date) else cast(GLFISCALPERIOD.ENDDATE as date) end end)
else BAT.POSTDATE
end
else
BAT.POSTDATE
end as POSTDATE,
T.TRANSACTIONTYPECODE, T.BASEAMOUNT, T_EXT.JOURNAL
,left(CASE WHEN LI.TYPECODE = 1 then 'Reversal - ' else '' END + tf.REFERENCE, 255) REFERENCE,
Cast(tf.DEPOSITID as nvarchar(36)) as DEPOSITID,
case @CashMethod when 2 then 'http://www.blackbaud.com/DEPOSITLINKID?DEPOSITLINKID='+CONVERT(nvarchar(36),tf.DEPOSITID) else null end as DEPOSITLINKID,
BAT.TRANSACTIONNUMBER as DEPOSITNUMBER,
Cast(GLFISCALPERIOD.ID as nvarchar(36)) PERIODID,
case @CashMethod when 0 then cast(isnull(BAT.POSTDATE, T.POSTDATE) as date) else case @SummarizeBy when 0 then cast(isnull(BAT.POSTDATE, T.POSTDATE) as date) else cast(GLFISCALPERIOD.ENDDATE as date) end end SUMMARYDATE,
case when FINANCIALTRANSACTION.ID is not null then
case dbo.UFN_POSTSUMMARYTYPE(tf.ISREVERSAL, T.TRANSACTIONTYPECODE, t3.REVENUETRANSACTIONTYPECODE, t3.APPLICATIONCODE, t3.PAYMENTMETHODCODE)
when 1 then (case @RevenueMethod when 0 then '0_' + cast(FINANCIALTRANSACTION.ID as nvarchar(36)) when 2 then '1_' + cast(t3.APPLICATIONCODE as nvarchar(36)) when 3 then '1_' + isnull(Cast(t4.DEPOSITID as nvarchar(36)), '') else '1' end)
when 2 then (case @CashMethod when 0 then '0_' + cast(FINANCIALTRANSACTION.ID as nvarchar(36)) when 2 then '2_' + Cast(t4.DEPOSITID as nvarchar(36)) else '2' end)
when 3 then (case @ARMethod when 0 then '0_' + cast(FINANCIALTRANSACTION.ID as nvarchar(36)) else '3' end)
else '0_0' + CAST(FT.ID as nvarchar(36)) end + '_' +
cast(case @SummarizeBy when 0 then cast(isnull(BAT.POSTDATE, T.POSTDATE) as date) else cast(GLFISCALPERIOD.ENDDATE as date) end as nvarchar(10))
else case when CREDIT.ID is not null then
case when T_EXT.TABLENAMECODE = 6 and T_EXT.CREDITPAYMENTID is null then
case when T.TRANSACTIONTYPECODE = 1 then (case @ARMethod when 0 then '0_' + cast(tf.RECORDID as nvarchar(36)) else '3_' end)
else (case @RevenueMethod when 0 then '0_' + cast(tf.RECORDID as nvarchar(36)) when 2 then '1_' + cast(t3.APPLICATIONCODE as nvarchar(36)) when 3 then '1_' + isnull(Cast(t4.DEPOSITID as nvarchar(36)), '') else '1_' end) end
else case T.TRANSACTIONTYPECODE when 0 then case @RevenueMethod when 0 then '0_' + CAST(CREDIT.ID as nvarchar(36)) when 2 then '1_' else '1_' end
else case @CashMethod when 0 then '0_' + CAST(CREDIT.ID as nvarchar(36)) when 2 then '2_' + CAST(tf.DEPOSITID as nvarchar(36)) else '2_' end end end
+ cast(case @SummarizeBy when 0 then cast(isnull(T.POSTDATE, T.POSTDATE) as date) else cast(GLFISCALPERIOD.ENDDATE as date) end as nvarchar(10))
else --DEPOSIT CORRECTION
case FT.TYPECODE
when 24 then --SHORT
case T.TRANSACTIONTYPECODE
when 0 then --DEBIT
case @RevenueMethod when 0 then '0_' + cast(BANKACCOUNTDEPOSITCORRECTION.ID as nvarchar(36)) when 3 then '1_' + Cast(BAT.ID as nvarchar(36)) + '_' else '1_' end
else --CREDIT
case @CashMethod when 0 then '0_' + cast(BANKACCOUNTDEPOSITCORRECTION.ID as nvarchar(36)) when 2 then '2_' + Cast(BAT.ID as nvarchar(36)) + '_' else (CASE WHEN T.TRANSACTIONTYPECODE = 0 THEN '2_' ELSE '2_' END) end
end
when 25 then --OVER
case T.TRANSACTIONTYPECODE
when 0 then --DEBIT
case @CashMethod when 0 then '0_' + cast(BANKACCOUNTDEPOSITCORRECTION.ID as nvarchar(36)) when 2 then '2_' + Cast(BAT.ID as nvarchar(36)) + '_' else CASE WHEN T.TRANSACTIONTYPECODE = 0 THEN '2_' ELSE '2_' END end
else --CREDIT
case @RevenueMethod when 0 then '0_' + cast(BANKACCOUNTDEPOSITCORRECTION.ID as nvarchar(36)) when 3 then '1_' + Cast(BAT.ID as nvarchar(36)) + '_' else '1_' end
end
else
case T.TRANSACTIONTYPECODE
when 0 then --DEBIT
case @RevenueMethod when 0 then '0_' when 3 then '1_' + Cast(BAT.ID as nvarchar(36)) + '_' else '1_' end
else --CREDIT
case @CashMethod when 0 then '0_' when 2 then '2_' + Cast(BAT.ID as nvarchar(36)) + '_' else (CASE WHEN T.TRANSACTIONTYPECODE = 0 THEN '2_' ELSE '2_' END) end
end
end
+ cast(case @SummarizeBy when 0 then cast(isnull(BAT.POSTDATE, LI.POSTDATE) as date) else cast(GLFISCALPERIOD.ENDDATE as date) end as nvarchar(10)) end end
GROUPBY,
CASE WHEN GLFISCALPERIOD.CLOSED = 1 THEN 1 ELSE CASE WHEN BA.STATUSCODE = 0 THEN 1 ELSE 0 END END as ISEXCEPTION,
CASE WHEN GLFISCALPERIOD.CLOSED = 1 THEN 'Post date must be in an open fiscal period' ELSE CASE WHEN BA.STATUSCODE = 0 THEN 'Payments linked to deposits associated with closed bank accounts cannot be posted.' ELSE null END END as EXCEPTIONREASON,
T.TRANSACTIONAMOUNT TRANSACTIONAMOUNT,
T.ORGAMOUNT ORGANIZATIONAMOUNT,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE,
tf.RECORDID, null,
'http://www.blackbaud.com/CREDITID?CREDITID=' + CONVERT(nvarchar(36), CREDIT.ID) as CREDITID
from @UNPOSTEDDEPOSITTRANSACTIONS as tf
inner join dbo.JOURNALENTRY T on T.ID = tf.GLTRANSACTIONID
inner join dbo.JOURNALENTRY_EXT T_EXT on T_EXT.ID = T.ID
inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = tf.DEPOSITID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = T.FINANCIALTRANSACTIONLINEITEMID
inner join dbo.GLFISCALPERIOD On isnull(BAT.POSTDATE, LI.POSTDATE) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
left join dbo.CURRENCY on CURRENCY.ID = T.TRANSACTIONCURRENCYID
left outer join dbo.GLACCOUNT on T.GLACCOUNTID = GLACCOUNT.ID
left outer join dbo.FINANCIALTRANSACTION on tf.RECORDID = FINANCIALTRANSACTION.ID
left outer join dbo.BANKACCOUNTDEPOSITCORRECTION on tf.RECORDID = BANKACCOUNTDEPOSITCORRECTION.ID
left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as t3 on T_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID = t3.ID and T_EXT.TABLENAMECODE = 1
left outer join dbo.BANKACCOUNTDEPOSITPAYMENT t4 on TF.RECORDID = t4.ID
left outer join dbo.CREDITPAYMENT CP on CP.ID = T_EXT.CREDITPAYMENTID
left outer join dbo.CREDIT on CREDIT.ID = tf.RECORDID or CP.CREDITID = CREDIT.ID --Yes it's a view, but too hard to put the base tables here
left outer join dbo.SALESORDER on SALESORDER.ID = CREDIT.SALESORDERID
where @DepostiPostingOption < 2
or (@DepostiPostingOption = 3 and BA.ID in (select F.BANKACCOUNTID from dbo.POSTTOGLPROCESSBANKACCOUNTFILTER F where F.POSTTOGLPROCESSID = @ID))
or (@DepostiPostingOption = 4 and BAT.ID in (select D.ID from dbo.POSTTOGLPROCESSDEPOSITTEMPLATESFILTER F inner join dbo.BANKACCOUNTDEPOSIT D on D.SALESDEPOSITTEMPLATEID = f.SALESDEPOSITTEMPLATEID where F.POSTTOGLPROCESSID = @ID))
option (recompile);
insert into @Temp
select t1.DEPOSITID
from @PrePostData_Table t1
inner join dbo.JOURNALENTRY on t1.ID = JOURNALENTRY.ID
inner join dbo.JOURNALENTRY_EXT ON JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
inner join dbo.BANKACCOUNTDEPOSIT D on D.ID = t1.DEPOSITID
inner join dbo.BANKACCOUNTTRANSACTION_EXT BAT on BAT.ID = D.ID
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
where JOURNALENTRY.TRANSACTIONCURRENCYID is not null and t1.ISEXCEPTION = 0
and ((D.TRANSACTIONCURRENCYID <> BA.TRANSACTIONCURRENCYID and D.TRANSACTIONEXCHANGERATEID is null)
or (JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
or (@ORGAMOUNTORIGINCODE = 1 and JOURNALENTRY.TRANSACTIONCURRENCYID <> @ORGCURRENCYID and JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null)
or (@ORGAMOUNTORIGINCODE = 0 and V.BASECURRENCYID <> @ORGCURRENCYID and JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null));
update @PrePostData_Table set
ISEXCEPTION = 1
,EXCEPTIONREASON = CASE WHEN JOURNALENTRY.TRANSACTIONCURRENCYID is null or (isnull(JOURNALENTRY.BASEAMOUNT, 0) > 0 and isnull(JOURNALENTRY.ORGAMOUNT, 0) > 0)
THEN 'Distribution cannot post due to a related distribution with exceptions.'
ELSE CASE WHEN BANKACCOUNTTRANSACTION.ID is not null and BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID <> BANKACCOUNT.TRANSACTIONCURRENCYID and BANKACCOUNTDEPOSIT.TRANSACTIONEXCHANGERATEID is null
THEN 'Exchange rate from the payment currency to the bank account currency does not exist for this transaction.'
ELSE CASE WHEN (JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
and ((@ORGAMOUNTORIGINCODE = 0 and V.BASECURRENCYID <> @ORGCURRENCYID and JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null)
or (@ORGAMOUNTORIGINCODE = 1 and JOURNALENTRY.TRANSACTIONCURRENCYID <> @ORGCURRENCYID and JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null))
THEN 'Base and organization exchange rates do not exist for this distribution.'
ELSE CASE WHEN (JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
THEN 'Base exchange rate does not exist for this distribution.'
ELSE 'Organization exchange rate does not exist for this distribution.' END
END
END
END
from @PrePostData_Table T
inner join dbo.JOURNALENTRY on T.ID = JOURNALENTRY.ID
inner join dbo.JOURNALENTRY_EXT on T.ID = JOURNALENTRY_EXT.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSIT.ID = T.DEPOSITID
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSIT.ID
inner join dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION.BANKACCOUNTID
where T.ISEXCEPTION = 0
and T.DEPOSITID in (select ID from @Temp)
--This section replaces the deletion below for performance
--Again, get rid of the scalar function and make the deletion a simpler, two step process
if @ISADMIN = 0
begin
declare @DepositTable table (DEPOSITID uniqueidentifier)
insert into @DepositTable (DEPOSITID)
select T.DEPOSITID
from @PrePostData_Table T
left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = T.REVENUEIDGUID
where (T.REVENUEIDGUID is null
or(
(@APPUSER_IN_NONRACROLE = 1
or FINANCIALTRANSACTION.CONSTITUENTID IN (
select ID
from @GRANTEDCONSTITIDSFORBUSINESSPROCESS
)
)
and exists (select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(T.REVENUEIDGUID) REVSITES inner join @AllowedSites t2
on (REVSITES.SITEID = t2.SITEID and (t2.SITESECURITYMODE = 2 or t2.SITESECURITYMODE = 3))
or t2.SITESECURITYMODE = 0
or (t2.SITESECURITYMODE = 1 and REVSITES.SITEID is null)
))) option (recompile);
delete from @PrePostData_Table
where DEPOSITID is not null
and DEPOSITID not in (select t1.DEPOSITID from @DepositTable t1)
end
end
if @AdjustmentPostingOption != 2 and @BasicGL = 1
begin
--insert any bank account adjustments
insert into @PrePostData_Table
select tf.GLTRANSACTIONID,
null as REVENUEID, null as DEPOSITCORRECTIONID,
'http://www.blackbaud.com/ADJUSTMENTID?ADJUSTMENTID=' + CONVERT(nvarchar(36), tf.ADJUSTMENTID) as ADJUSTMENTID,
isnull(GLACCOUNT.ACCOUNTNUMBER, JOURNALENTRY_EXT.ACCOUNT),
'' as LOOKUPID,
LI.POSTDATE
, T.TRANSACTIONTYPECODE, T.BASEAMOUNT, JOURNALENTRY_EXT.JOURNAL, T.COMMENT,
null as DEPOSITID, null as DEPOSITLINKID, null as DEPOSITNUMBER, Cast(GLFISCALPERIOD.ID as nvarchar(36)) PERIODID
, case @SummarizeBy when 0 then cast(isnull(LI.POSTDATE, T.POSTDATE) as date) else cast(GLFISCALPERIOD.ENDDATE as date) end,
case @CashMethod when 0 then '0_' + cast(BAT.ID as nvarchar(36)) else CASE WHEN T.TRANSACTIONTYPECODE = 0 THEN '2_' ELSE '1_' END end
+ cast(case @SummarizeBy when 0 then cast(isnull(LI.POSTDATE, T.POSTDATE) as date) else cast(GLFISCALPERIOD.ENDDATE as date) end as nvarchar(10)) GROUPBY,
CASE WHEN GLFISCALPERIOD.CLOSED = 1 THEN 1 ELSE CASE WHEN BA.STATUSCODE = 0 THEN 1 ELSE 0 END END as ISEXCEPTION,
CASE WHEN GLFISCALPERIOD.CLOSED = 1 THEN 'Post date must be in an open fiscal period' ELSE CASE WHEN BA.STATUSCODE = 0 THEN 'Payments linked to deposits associated with closed bank accounts cannot be posted.' ELSE null END END as EXCEPTIONREASON,
T.TRANSACTIONAMOUNT,
T.ORGAMOUNT,
CURRENCY.ISO4217,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.DECIMALDIGITS,
CURRENCY.SYMBOLDISPLAYSETTINGCODE,
tf.ADJUSTMENTID, tf.ADJUSTMENTID, null
from dbo.UFN_POSTTOGLPROCESS_GETUNPOSTEDADJUSTMENTTRANSACTIONS_2(@ID) as tf
inner join dbo.JOURNALENTRY T on T.ID = tf.GLTRANSACTIONID
inner join dbo.JOURNALENTRY_EXT on T.ID = JOURNALENTRY_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = T.FINANCIALTRANSACTIONLINEITEMID
inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = tf.ADJUSTMENTID
inner join dbo.GLFISCALPERIOD on LI.POSTDATE between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
left join dbo.CURRENCY on CURRENCY.ID = T.TRANSACTIONCURRENCYID
left join dbo.GLACCOUNT on T.GLACCOUNTID = GLACCOUNT.ID
delete from @Temp;
insert into @Temp
select t1.ADJUSTMENTIDGUID
from @PrePostData_Table t1
inner join dbo.JOURNALENTRY on t1.ID = JOURNALENTRY.ID
inner join dbo.JOURNALENTRY_EXT ON JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
where JOURNALENTRY.TRANSACTIONCURRENCYID is not null and t1.ADJUSTMENTID is not null and t1.ISEXCEPTION = 0
and ((JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
or (@ORGAMOUNTORIGINCODE = 1 and JOURNALENTRY.TRANSACTIONCURRENCYID <> @ORGCURRENCYID and JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null)
or (@ORGAMOUNTORIGINCODE = 0 and V.BASECURRENCYID <> @ORGCURRENCYID and JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null));
update @PrePostData_Table set
ISEXCEPTION = 1
,EXCEPTIONREASON = CASE WHEN JOURNALENTRY.TRANSACTIONCURRENCYID is null or (isnull(JOURNALENTRY.BASEAMOUNT, 0) > 0 and isnull(JOURNALENTRY.ORGAMOUNT, 0) > 0)
THEN 'Distribution cannot post due to a related distribution with exceptions.'
ELSE case when (JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
and ((@ORGAMOUNTORIGINCODE = 0 and V.BASECURRENCYID <> @ORGCURRENCYID and JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null)
or (@ORGAMOUNTORIGINCODE = 1 and JOURNALENTRY.TRANSACTIONCURRENCYID <> @ORGCURRENCYID and JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID is null))
then 'Base and organization exchange rates do not exist for this distribution.'
else
case when (JOURNALENTRY.TRANSACTIONCURRENCYID <> V.BASECURRENCYID and JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID is null)
then 'Base exchange rate does not exist for this distribution.'
else 'Organization exchange rate does not exist for this distribution.'
end
end
end
from @PrePostData_Table T
inner join dbo.JOURNALENTRY on T.ID = JOURNALENTRY.ID
inner join dbo.JOURNALENTRY_EXT on T.ID = JOURNALENTRY_EXT.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
where T.ISEXCEPTION = 0
and T.ADJUSTMENTIDGUID in (select ID from @Temp)
end
if @DEFAULTGLACCOUNTID is not null
update @PrePostData_Table set
ISEXCEPTION = 1
,EXCEPTIONREASON = CASE
WHEN tf.REVENUEIDGUID = tf2.REVENUEIDGUID
then 'The account assigned to unmapped transactions is included in this GL distribution.'
else 'Transaction is in a deposit that cannot be posted because the GL distribution for one of its payments includes the account assigned to unmapped transactions.'
end
from @PrePostData_Table tf
inner join (select tf2.REVENUEIDGUID, tf2.DEPOSITID
from @PrePostData_Table as tf2
inner join dbo.JOURNALENTRY T on T.ID = tf2.ID
where tf2.ISEXCEPTION = 0 and T.GLACCOUNTID = @DEFAULTGLACCOUNTID) tf2 on tf.REVENUEIDGUID = tf2.REVENUEIDGUID or tf.DEPOSITID = tf2.DEPOSITID
where tf.ISEXCEPTION = 0
delete from @PrePostData_Table
where cast(POSTDATE as date) > @PostDateFilter;
return
end