USP_REPORT_1099MISCACTIVITY
Returns the 1099 activity for payables vendors.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @VENDORID | uniqueidentifier | IN | |
| @VENDORQUERYID | uniqueidentifier | IN | |
| @INCLUDENON1099VENDORS | bit | IN | |
| @INCLUDEACTIVITYBELOW1099MIN | bit | IN | |
| @REPORTUSERID | nvarchar(128) | IN | |
| @ALTREPORTUSERID | nvarchar(128) | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_1099MISCACTIVITY
(
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@VENDORID uniqueidentifier = null,
@VENDORQUERYID uniqueidentifier = null,
@INCLUDENON1099VENDORS bit = null,
@INCLUDEACTIVITYBELOW1099MIN bit = null,
@REPORTUSERID nvarchar(128) = null,
@ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
set nocount on;
declare @1099DISTRIBUTION table (
CONSTITUENTID nvarchar(200)
,LOOKUPID nvarchar(100)
,NAME nvarchar(100)
,BOX1 money
,BOX2 money
,BOX3 money
,BOX4 money
,BOX5 money
,BOX6 money
,BOX7 money
,BOX8 money
,BOX10 money
,BOX13 money
,BOX14 money
,BOX15a money
,BOX15b money
,BOX16 money
,BOX18STATE nvarchar(5)
);
insert into @1099DISTRIBUTION
exec dbo.USP_REPORT_1099MISCACTIVITY_DIVIDED @STARTDATE, @ENDDATE, @VENDORID, @VENDORQUERYID, @INCLUDENON1099VENDORS, @REPORTUSERID, @ALTREPORTUSERID
if @INCLUDEACTIVITYBELOW1099MIN = 1
begin
select D.CONSTITUENTID
,D.LOOKUPID
,D.NAME
,D.BOX1
,D.BOX2
,D.BOX3
,D.BOX4
,D.BOX5
,D.BOX6
,D.BOX7
,D.BOX8
,D.BOX10
,D.BOX13
,D.BOX14
,D.BOX15a
,D.BOX15b
,D.BOX16
,D.BOX18STATE
,CASE WHEN D.BOX18STATE is null THEN 0 ELSE D.BOX1 + D.BOX2 + D.BOX3 + D.BOX5 + D.BOX6 + D.BOX7 + D.BOX8 + D.BOX10 + D.BOX13 + D.BOX14 + D.BOX15a + D.BOX15b END [BOX18]
from @1099DISTRIBUTION D
end
else
begin
select D.CONSTITUENTID
,D.LOOKUPID
,D.NAME
,case when (select SUM(D1.BOX1) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='1') THEN D.BOX1 ELSE 0 END [BOX1]
,case when (select SUM(D1.BOX2) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='2') THEN D.BOX2 ELSE 0 END [BOX2]
,case when (select SUM(D1.BOX3) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='3') THEN D.BOX3 ELSE 0 END [BOX3]
,case when (select SUM(D1.BOX4) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='4') THEN D.BOX4 ELSE 0 END [BOX4]
,case when (select SUM(D1.BOX5) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='5') THEN D.BOX5 ELSE 0 END [BOX5]
,case when (select SUM(D1.BOX6) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='6') THEN D.BOX6 ELSE 0 END [BOX6]
,case when (select SUM(D1.BOX7) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='7') THEN D.BOX7 ELSE 0 END [BOX7]
,case when (select SUM(D1.BOX8) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='8') THEN D.BOX8 ELSE 0 END [BOX8]
,case when (select SUM(D1.BOX10) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='10') THEN D.BOX10 ELSE 0 END [BOX10]
,case when (select SUM(D1.BOX13) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='13') THEN D.BOX13 ELSE 0 END [BOX13]
,case when (select SUM(D1.BOX14) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='14') THEN D.BOX14 ELSE 0 END [BOX14]
,case when (select SUM(D1.BOX15a) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='15a') THEN D.BOX15a ELSE 0 END [BOX15a]
,case when (select SUM(D1.BOX15b) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='15b') THEN D.BOX15b ELSE 0 END [BOX15b]
,case when (select SUM(D1.BOX16) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='16') THEN D.BOX16 ELSE 0 END [BOX16]
,D.BOX18STATE
,CASE WHEN D.BOX18STATE is null THEN 0 ELSE
case when (select SUM(D1.BOX1) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='1') THEN D.BOX1 ELSE 0 END
+ case when (select SUM(D1.BOX2) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='2') THEN D.BOX2 ELSE 0 END
+ case when (select SUM(D1.BOX3) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='3') THEN D.BOX3 ELSE 0 END
+ case when (select SUM(D1.BOX5) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='5') THEN D.BOX5 ELSE 0 END
+ case when (select SUM(D1.BOX6) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='6') THEN D.BOX6 ELSE 0 END
+ case when (select SUM(D1.BOX7) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='7') THEN D.BOX7 ELSE 0 END
+ case when (select SUM(D1.BOX8) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='8') THEN D.BOX8 ELSE 0 END
+ case when (select SUM(D1.BOX10) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='10') THEN D.BOX10 ELSE 0 END
+ case when (select SUM(D1.BOX13) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='13') THEN D.BOX13 ELSE 0 END
+ case when (select SUM(D1.BOX14) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='14') THEN D.BOX14 ELSE 0 END
+ case when (select SUM(D1.BOX15a) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='15a') THEN D.BOX15a ELSE 0 END
+ case when (select SUM(D1.BOX15b) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='15b') THEN D.BOX15b ELSE 0 END
END [BOX18]
from @1099DISTRIBUTION D
group by D.CONSTITUENTID, D.LOOKUPID, D.NAME, D.BOX18STATE, D.BOX1, D.BOX2, D.BOX3, D.BOX4, D.BOX5, D.BOX6, D.BOX7, D.BOX8, D.BOX10, D.BOX13, D.BOX14, D.BOX15a, D.BOX15b, D.BOX16
having ((select SUM(D1.BOX1) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='1') and D.BOX1 > 0)
or ((select SUM(D1.BOX2) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='2') and D.BOX2 > 0)
or ((select SUM(D1.BOX3) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='3') and D.BOX3 > 0)
or ((select SUM(D1.BOX4) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='4') and D.BOX4 > 0)
or ((select SUM(D1.BOX5) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='5') and D.BOX5 > 0)
or ((select SUM(D1.BOX6) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='6') and D.BOX6 > 0)
or ((select SUM(D1.BOX7) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='7') and D.BOX7 > 0)
or ((select SUM(D1.BOX8) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='8') and D.BOX8 > 0)
or ((select SUM(D1.BOX10) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='10') and D.BOX10 > 0)
or ((select SUM(D1.BOX13) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='13') and D.BOX13 > 0)
or ((select SUM(D1.BOX14) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='14') and D.BOX14 > 0)
or ((select SUM(D1.BOX15a) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='15a') and D.BOX15a > 0)
or ((select SUM(D1.BOX15b) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='15b') and D.BOX15b > 0)
or ((select SUM(D1.BOX16) from @1099DISTRIBUTION D1 where D1.CONSTITUENTID = D.CONSTITUENTID group by D1.CONSTITUENTID)>=(select MINIMUMAMOUNT from dbo.FINANCIALTRANSACTION1099BOXNUMBER where BOXNUMBER='16') and D.BOX16 > 0)
end