View: dbo.Vendors_That_Require_Zero_Payment_View
View definition
-- Added to improve performance for the create zero liability process
CREATE VIEW dbo.Vendors_That_Require_Zero_Payment_View
AS
SELECT
v.client_KEY
, va.coupon_not_accepted_for_zero_payment
, va.coupon_required_if_no_payment_due
, c.date_opened
--- we only need the create_date from the recorded events IF our client date_opened is null; otherwise, just use that instead.
, COALESCE(c.date_opened, (
SELECT
re.create_date
FROM
dbo.Recorded_Event AS re
INNER JOIN dbo.Recorded_Event_Client_Recorded_Event_Object AS recreo ON re.recorded_event_KEY = recreo.recorded_event_KEY
INNER JOIN dbo.Recorded_Event_Object_N_Client AS reonc ON recreo.recorded_event_object_KEY = reonc.recorded_event_object_KEY
WHERE
reonc.client_KEY = c.client_KEY
AND re.event_class_type_KEY = 4
AND re.event_type_KEY = 1
)) AS create_date
, ta.ste_state_code_KEY
, tanva.tax_agent_KEY
, v.vendor_KEY
, va.vendor_agent_payment_schedule_KEY
, v.zero_liability_created_date
, ta.zero_payment_eft_accepted
FROM
dbo.Vendor AS v
INNER JOIN dbo.Vendor_Agent AS va ON va.vendor_KEY = v.vendor_KEY
INNER JOIN dbo.Client AS c ON c.client_KEY = v.client_KEY
INNER JOIN dbo.Client_Payroll_Information AS cpi ON cpi.client_KEY = v.client_KEY
LEFT JOIN dbo.Tax_Agent_N_Vendor_Agent AS tanva ON tanva.vendor_KEY = v.vendor_KEY
INNER JOIN dbo.Tax_Agent AS ta ON ta.tax_agent_KEY = tanva.tax_agent_KEY
WHERE
va.do_not_create_zero_liabilities = 0
AND va.coupon_required_if_no_payment_due = 1
AND c.client_type_KEY NOT IN (2, 3, 4)
AND c.record_status_KEY = 1
AND cpi.do_not_create_tax_liabilities = 0
AND v.vendor_KEY IN
(
SELECT
cti3.vendor_KEY
FROM
dbo.Client_Tax_Item AS cti3
INNER JOIN dbo.Work_Location_Tax_Jurisdiction AS wltj3 ON wltj3.tax_jurisdiction_KEY = cti3.tax_jurisdiction_KEY
UNION ALL
SELECT
cti1.vendor_KEY
FROM
dbo.Client_Tax_Item AS cti1
WHERE
cti1.has_nexus = 1
AND cti1.vendor_KEY IN (
SELECT
tanva1_a.vendor_KEY
FROM
dbo.Tax_Agent_N_Vendor_Agent AS tanva1_a
INNER JOIN dbo.Tax_Agent AS ta1_a ON ta1_a.tax_agent_KEY = tanva1_a.tax_agent_KEY
WHERE
ta1_a.system_provided_tax_agent_is_local = 1
)
UNION ALL
SELECT
cti2.vendor_KEY
FROM
dbo.Client_Tax_Item AS cti2
INNER JOIN dbo.Tax_Jurisdiction_State_Setting AS tjss2 ON tjss2.tax_jurisdiction_KEY = cti2.tax_jurisdiction_KEY
WHERE
tjss2.has_nexus = 1
);