USP_PROCESSORTRANSACTIONSETTLEMENTIMPORT_PROCESSTRANSACTIONS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PROCESSORTRANSACTIONSETTLEMENTIMPORTID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @INITIALAUTOSETTLEMENT | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_PROCESSORTRANSACTIONSETTLEMENTIMPORT_PROCESSTRANSACTIONS
(
@PROCESSORTRANSACTIONSETTLEMENTIMPORTID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@INITIALAUTOSETTLEMENT bit = 0
)
as
begin
declare
@HASAUTHCODE bit,
@TOTALIMPORTTRANSACTIONS int,
@ISDUPLICATEFILEUPLOAD bit,
@CURRENTDATE datetime,
@STATUSCODE tinyint = 0,
@APPUSERISSYSADMIN bit = 0,
@IMPORTTYPECODE bit = 0;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @FEATUREID uniqueidentifier = '9151decb-5734-4fd9-8884-7d18aba1e2ed';
select
@HASAUTHCODE = HASAUTHCODE,
@TOTALIMPORTTRANSACTIONS = TOTALIMPORTTRANSACTIONS,
@ISDUPLICATEFILEUPLOAD = ISDUPLICATEFILEUPLOAD,
@IMPORTTYPECODE = IMPORTTYPECODE
from dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORT
where ID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID;
declare @SITESFORUSER table (SITEID uniqueidentifier);
insert into @SITESFORUSER
select * from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@FEATUREID,2);
declare @ISSYSTEMUSER bit;
select @ISSYSTEMUSER = ISSYSTEM,
@APPUSERISSYSADMIN = ISSYSADMIN
from dbo.APPUSER (nolock) where ID = @CURRENTAPPUSERID;
create table #SETTLEDTRANSACTIONS (
IMPORTTRANSACTIONID uniqueidentifier,
DBTRANSACTIONID uniqueidentifier
);
--attempt this on the transaction ID first
if @APPUSERISSYSADMIN = 1 or @ISSYSTEMUSER = 1
begin
insert into #SETTLEDTRANSACTIONS
select
ITR.ID,
PMD.ID
from dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW ITR
inner join dbo.CREDITCARDPAYMENTMETHODDETAIL PMD on ITR.TRANSACTIONID = PMD.TRANSACTIONID
inner join dbo.REVENUEPAYMENTMETHOD RPM on RPM.ID = PMD.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = RPM.REVENUEID
where PMD.SETTLEMENTTYPECODE = 0
and ITR.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and ISREFUND = 0
end
else
begin
insert into #SETTLEDTRANSACTIONS
select
ITR.ID,
PMD.ID
from dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW ITR
inner join dbo.CREDITCARDPAYMENTMETHODDETAIL PMD on ITR.TRANSACTIONID = PMD.TRANSACTIONID
inner join dbo.REVENUEPAYMENTMETHOD RPM on RPM.ID = PMD.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = RPM.REVENUEID
where PMD.SETTLEMENTTYPECODE = 0
and ITR.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and ISREFUND = 0
and (
( --there's no revenue
select
count(ID)
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FT.ID
) = 0
or
(--Check site security
select
count(*)
from dbo.UFN_SITEID_MAPFROM_REVENUEID(FT.ID) as SITE
where (
@APPUSERISSYSADMIN = 1 or
exists (
select 1
from @SITESFORUSER
where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)
)
)
) > 0);
end
-- Refunds do not generate revenue, and therefore don't generate credit card
-- transaction history. To properly settle them, we'll have to just remove any
-- refund transactions that match system refunds.
if @APPUSERISSYSADMIN = 1 or @ISSYSTEMUSER = 1
begin
insert into #SETTLEDTRANSACTIONS
select
IMPORTEDTRANSACTIONS.ID,
CREDITPAYMENT.ID
from dbo.CREDITPAYMENT
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = CREDITPAYMENT.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
inner join dbo.CREDITCARDPAYMENTMETHODDETAIL
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.CREDITCARDPARTIALNUMBER = CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, CREDITPAYMENT.[DATEADDED])
and -IMPORTEDTRANSACTIONS.AMOUNT = CREDITPAYMENT.AMOUNT
where IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and CREDITPAYMENT.SETTLEMENTTYPECODE = 0
and IMPORTEDTRANSACTIONS.ISREFUND = 1
and CREDITPAYMENT.REVENUESPLITID is null
end
else
begin
insert into #SETTLEDTRANSACTIONS
select
IMPORTEDTRANSACTIONS.ID,
CREDITPAYMENT.ID
from dbo.CREDITPAYMENT
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = CREDITPAYMENT.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
inner join dbo.CREDITCARDPAYMENTMETHODDETAIL
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.CREDITCARDPARTIALNUMBER = CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, CREDITPAYMENT.[DATEADDED])
and -IMPORTEDTRANSACTIONS.AMOUNT = CREDITPAYMENT.AMOUNT
where IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and CREDITPAYMENT.SETTLEMENTTYPECODE = 0
and IMPORTEDTRANSACTIONS.ISREFUND = 1
and CREDITPAYMENT.REVENUESPLITID is null
and (
( --there's no revenue
select
count(ID)
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
) = 0
or
(--Check site security
select
count(*)
from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) as SITE
where (
@APPUSERISSYSADMIN = 1 or
exists (
select 1
from @SITESFORUSER
where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)
)
)
) > 0);
end
if @APPUSERISSYSADMIN = 1 or @ISSYSTEMUSER = 1
begin
insert into #SETTLEDTRANSACTIONS
select
IMPORTEDTRANSACTIONS.ID,
CREDITPAYMENT.ID
from dbo.CREDITPAYMENT
inner join dbo.REVENUESPLIT
on CREDITPAYMENT.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
inner join dbo.CREDITCARDPAYMENTMETHODDETAIL
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.CREDITCARDPARTIALNUMBER = CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, CREDITPAYMENT.[DATEADDED])
and -IMPORTEDTRANSACTIONS.AMOUNT = CREDITPAYMENT.AMOUNT
where IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and CREDITPAYMENT.SETTLEMENTTYPECODE = 0
and IMPORTEDTRANSACTIONS.ISREFUND = 1
end
else
begin
insert into #SETTLEDTRANSACTIONS
select
IMPORTEDTRANSACTIONS.ID,
CREDITPAYMENT.ID
from dbo.CREDITPAYMENT
inner join dbo.REVENUESPLIT
on CREDITPAYMENT.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
inner join dbo.CREDITCARDPAYMENTMETHODDETAIL
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.CREDITCARDPARTIALNUMBER = CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, CREDITPAYMENT.[DATEADDED])
and -IMPORTEDTRANSACTIONS.AMOUNT = CREDITPAYMENT.AMOUNT
where IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and CREDITPAYMENT.SETTLEMENTTYPECODE = 0
and IMPORTEDTRANSACTIONS.ISREFUND = 1
and (
( --there's no revenue
select
count(ID)
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
) = 0
or
(--Check site security
select
count(*)
from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) as SITE
where (
@APPUSERISSYSADMIN = 1 or
exists (
select 1
from @SITESFORUSER
where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)
)
)
) > 0);
end
if @HASAUTHCODE = 1
begin
-- We can match on auth code and date here, which is very clean and guaranteed unique in live data
if @APPUSERISSYSADMIN = 1 or @ISSYSTEMUSER = 1
begin
insert into #SETTLEDTRANSACTIONS
select
IMPORTEDTRANSACTIONS.ID,
CREDITCARDPAYMENTMETHODDETAIL.ID
from dbo.CREDITCARDPAYMENTMETHODDETAIL
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.AUTHORIZATIONCODE = CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, FINANCIALTRANSACTION.[DATE])
where CREDITCARDPAYMENTMETHODDETAIL.SETTLEMENTTYPECODE = 0
and not exists (select 1 from #SETTLEDTRANSACTIONS ST where ST.IMPORTTRANSACTIONID = IMPORTEDTRANSACTIONS.ID and ST.DBTRANSACTIONID = CREDITCARDPAYMENTMETHODDETAIL.ID)
and IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and IMPORTEDTRANSACTIONS.ISREFUND = 0
and (IMPORTEDTRANSACTIONS.TRANSACTIONID is null or CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID is null) --if matched, ignore. If different, ignore.s
end
else
begin
insert into #SETTLEDTRANSACTIONS
select
IMPORTEDTRANSACTIONS.ID,
CREDITCARDPAYMENTMETHODDETAIL.ID
from dbo.CREDITCARDPAYMENTMETHODDETAIL
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.AUTHORIZATIONCODE = CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, FINANCIALTRANSACTION.[DATE])
where CREDITCARDPAYMENTMETHODDETAIL.SETTLEMENTTYPECODE = 0
and not exists (select 1 from #SETTLEDTRANSACTIONS ST where ST.IMPORTTRANSACTIONID = IMPORTEDTRANSACTIONS.ID and ST.DBTRANSACTIONID = CREDITCARDPAYMENTMETHODDETAIL.ID)
and IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and IMPORTEDTRANSACTIONS.ISREFUND = 0
and (IMPORTEDTRANSACTIONS.TRANSACTIONID is null or CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID is null) --if matched, ignore. If different, ignore.
and (
( --there's no revenue
select
count(ID)
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
) = 0
or
(--Check site security
select
count(*)
from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) as SITE
where (
@APPUSERISSYSADMIN = 1 or
exists (
select 1
from @SITESFORUSER
where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)
)
)
) > 0);
end
end
else
begin
-- Here we have to match on CC#, date, and amount, which can be duplicated
if @APPUSERISSYSADMIN = 1 or @ISSYSTEMUSER = 1
begin
insert into #SETTLEDTRANSACTIONS
select
IMPORTEDTRANSACTIONS.ID,
CREDITCARDPAYMENTMETHODDETAIL.ID
from dbo.CREDITCARDPAYMENTMETHODDETAIL
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.CREDITCARDPARTIALNUMBER = CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, FINANCIALTRANSACTION.[DATE])
and IMPORTEDTRANSACTIONS.AMOUNT = FINANCIALTRANSACTION.ORGAMOUNT
where CREDITCARDPAYMENTMETHODDETAIL.SETTLEMENTTYPECODE = 0
and not exists (select 1 from #SETTLEDTRANSACTIONS ST where ST.IMPORTTRANSACTIONID = IMPORTEDTRANSACTIONS.ID and ST.DBTRANSACTIONID = CREDITCARDPAYMENTMETHODDETAIL.ID)
and IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and IMPORTEDTRANSACTIONS.ISREFUND = 0
and (IMPORTEDTRANSACTIONS.TRANSACTIONID is null or CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID is null) --if matched, ignore. If different, ignore.
end
else
begin
insert into #SETTLEDTRANSACTIONS
select
IMPORTEDTRANSACTIONS.ID,
CREDITCARDPAYMENTMETHODDETAIL.ID
from dbo.CREDITCARDPAYMENTMETHODDETAIL
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.CREDITCARDPARTIALNUMBER = CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, FINANCIALTRANSACTION.[DATE])
and IMPORTEDTRANSACTIONS.AMOUNT = FINANCIALTRANSACTION.ORGAMOUNT
where CREDITCARDPAYMENTMETHODDETAIL.SETTLEMENTTYPECODE = 0
and not exists (select 1 from #SETTLEDTRANSACTIONS ST where ST.IMPORTTRANSACTIONID = IMPORTEDTRANSACTIONS.ID and ST.DBTRANSACTIONID = CREDITCARDPAYMENTMETHODDETAIL.ID)
and IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and IMPORTEDTRANSACTIONS.ISREFUND = 0
and (IMPORTEDTRANSACTIONS.TRANSACTIONID is null or CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID is null) --if matched, ignore. If different, ignore.
and (
( --there's no revenue
select
count(ID)
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
) = 0
or
(--Check site security
select
count(*)
from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) as SITE
where (
@APPUSERISSYSADMIN = 1 or
exists (
select 1
from @SITESFORUSER
where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)
)
)
) > 0);
end
end
-- After the initial pass, we have to ensure that no 1 processor transaction settled multiple db transactions
-- This should not have a huge impact on performance since duplicate transactions should be very minimal.
-- They would really only occur if the same amount was paid on the same day with the same partial CC number
-- in the case of a missing authorization code or a refund.
declare @DUPLICATEDTRANSACTIONS table(DUPLICATETRANSACTIONID uniqueidentifier, DUPLICATEDBTRANSACTIONID uniqueidentifier);
declare DUPLICATED_TRANSACTIONS cursor local fast_forward read_only for
select
IMPORTTRANSACTIONID,
DBTRANSACTIONID
from #SETTLEDTRANSACTIONS S1
where (
select COUNT(S2.IMPORTTRANSACTIONID)
from #SETTLEDTRANSACTIONS S2
where S2.IMPORTTRANSACTIONID = S1.IMPORTTRANSACTIONID
or S2.DBTRANSACTIONID = S1.DBTRANSACTIONID
) > 1
declare @DUPLICATETRANSACTIONID uniqueidentifier, @DBTRANSACTIONID uniqueidentifier;
open DUPLICATED_TRANSACTIONS
fetch next from DUPLICATED_TRANSACTIONS into @DUPLICATETRANSACTIONID, @DBTRANSACTIONID
while @@fetch_status = 0
begin
if not exists(
select
1
from @DUPLICATEDTRANSACTIONS
where DUPLICATEDBTRANSACTIONID = @DBTRANSACTIONID
) and not exists (
select
1
from @DUPLICATEDTRANSACTIONS
where DUPLICATETRANSACTIONID = @DUPLICATETRANSACTIONID
) insert into @DUPLICATEDTRANSACTIONS values (@DUPLICATETRANSACTIONID, @DBTRANSACTIONID)
fetch next from DUPLICATED_TRANSACTIONS into @DUPLICATETRANSACTIONID, @DBTRANSACTIONID
end
close DUPLICATED_TRANSACTIONS
deallocate DUPLICATED_TRANSACTIONS
if exists(select 1 from @DUPLICATEDTRANSACTIONS)
-- we have duplicates
begin
delete from #SETTLEDTRANSACTIONS
where exists(
select
1
from @DUPLICATEDTRANSACTIONS
where DUPLICATETRANSACTIONID = IMPORTTRANSACTIONID
and DUPLICATEDBTRANSACTIONID = DBTRANSACTIONID
)
end
declare @TOTALSETTLEDTRANSACTIONS int = (select count(*) from #SETTLEDTRANSACTIONS);
declare @PERCENTMATCHED decimal;
if @TOTALIMPORTTRANSACTIONS > 0
set @PERCENTMATCHED = convert(decimal, @TOTALSETTLEDTRANSACTIONS)/convert(decimal, @TOTALIMPORTTRANSACTIONS);
else
-- This would only happen if they uploaded a blank file, in which case 0/0 should be considered a 100% match
set @PERCENTMATCHED = 1
-- Threshold subject to change
if @PERCENTMATCHED < .25 and @ISDUPLICATEFILEUPLOAD = 0 and @IMPORTTYPECODE = 0
begin
if @HASAUTHCODE = 1
begin
-- Wrong auth code or date column
set @STATUSCODE = 2;
end
else
begin
-- Do some checking to see if we can narrow down the possible problem fields
declare @TOTALMATCHES int;
-- Start with amount field since that's the most likely error case (gross vs net)
-- Do the same query without the amount field incorporated
if @APPUSERISSYSADMIN = 1 or @ISSYSTEMUSER = 1
begin
select
@TOTALMATCHES = count(*)
from dbo.CREDITCARDPAYMENTMETHODDETAIL
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.CREDITCARDPARTIALNUMBER = CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, FINANCIALTRANSACTION.[DATE])
where CREDITCARDPAYMENTMETHODDETAIL.SETTLEMENTTYPECODE = 0
and IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and ISREFUND = 0
end
else
begin
select
@TOTALMATCHES = count(*)
from dbo.CREDITCARDPAYMENTMETHODDETAIL
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
on IMPORTEDTRANSACTIONS.CREDITCARDPARTIALNUMBER = CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
and IMPORTEDTRANSACTIONS.TRANSACTIONDATE = convert(date, FINANCIALTRANSACTION.[DATE])
where CREDITCARDPAYMENTMETHODDETAIL.SETTLEMENTTYPECODE = 0
and IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and ISREFUND = 0
and (
( --there's no revenue
select
count(ID)
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
) = 0
or
(--Check site security
select
count(*)
from dbo.UFN_SITEID_MAPFROM_REVENUEID(FINANCIALTRANSACTION.ID) as SITE
where (
@APPUSERISSYSADMIN = 1 or
exists (
select 1
from @SITESFORUSER
where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)
)
)
) > 0);
end
if @TOTALMATCHES > @TOTALSETTLEDTRANSACTIONS
begin
-- Wrong amount column
set @STATUSCODE = 3;
end
else
begin
-- Wrong card number or date column
set @STATUSCODE = 4;
end
end
end
if @STATUSCODE = 0
begin
update PMD set
SETTLEMENTDATE = @CURRENTDATE,
SETTLEMENTTYPECODE = 2,
FEE = IR.FEE,
NETAMOUNT = IR.NETAMOUNT
from
#SETTLEDTRANSACTIONS S
inner join dbo.CREDITCARDPAYMENTMETHODDETAIL PMD on PMD.ID = S.DBTRANSACTIONID
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IR on IR.ID = S.IMPORTTRANSACTIONID
update dbo.CREDITPAYMENT set
SETTLEMENTDATE = @CURRENTDATE,
SETTLEMENTTYPECODE = 2
where exists (
select
1
from #SETTLEDTRANSACTIONS
where DBTRANSACTIONID = ID
);
update IR set
STATUSCODE = 1,
DATABASETRANSACTIONID = S.DBTRANSACTIONID,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from
#SETTLEDTRANSACTIONS S
inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IR on IR.ID = S.IMPORTTRANSACTIONID
insert into dbo.UNSETTLEDCREDITCARDTRANSACTION
(
ID,
TRANSACTIONDATE,
CARDTYPE,
CARDHOLDERNAME,
CREDITCARDPARTIALNUMBER,
AUTHORIZATIONCODE,
AMOUNT,
PROCESSORTRANSACTIONSETTLEMENTIMPORTID,
VENDORID,
CURRENCYID,
PROCESSORTRANSACTIONSETTLEMENTIMPORTROWID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
TRANSACTIONDATE,
CARDTYPE,
CARDHOLDERNAME,
CREDITCARDPARTIALNUMBER,
AUTHORIZATIONCODE,
AMOUNT,
@PROCESSORTRANSACTIONSETTLEMENTIMPORTID,
VENDORID,
CURRENCYID,
IMPORTEDTRANSACTIONS.ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW IMPORTEDTRANSACTIONS
where not exists (
select
1
from #SETTLEDTRANSACTIONS
where IMPORTTRANSACTIONID = IMPORTEDTRANSACTIONS.ID
)
and IMPORTEDTRANSACTIONS.PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
and IMPORTEDTRANSACTIONS.STATUSCODE = 0; --Do not include fraud fees
set @STATUSCODE = 1;
end
else
begin
delete from dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW
where PROCESSORTRANSACTIONSETTLEMENTIMPORTID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID
end
-- Clear temp tables
drop table #SETTLEDTRANSACTIONS;
update dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORT set
STATUSCODE = @STATUSCODE
where ID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID;
end