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 ); | |||