View: dbo.Client_View | |||
View definition | |||
CREATE VIEW dbo.Client_View AS SELECT C.client_KEY , C.client_id , C.client_id_sortable , C.description , C.client_class_KEY , CC.description AS client_class__description , C.name_1 , C.name_2 , C.federal_tin , C.individual_first_name , C.individual_last_name , C.spouse_first_name , C.spouse_last_name , C.record_status_KEY , RS.description AS record_status__description , C.industry_code , C.fiscal_year_end_month , C.date_opened , C.date_left , C.contact_KEY , CT.primary__contact_phone_type_KEY AS contact__primary__contact_phone_type_KEY , CT.primary__contact_address_type_KEY AS contact__primary__contact_address_type_KEY , CT.mailing__contact_address_type_KEY AS contact__mailing__contact_address_type_KEY , CT.contact_type_KEY AS contact__contact_type_KEY , C.client_inactive_reason_KEY , C.inactive_explanation , C.client_type_KEY , C.data_origin_KEY , C.import_source_KEY , C.allow_all_firm_staff_access , C.csa_client_had_tbs_or_gl_module_marked -- These CPI columns exist to support filter formulas where the filter type is a grandchild of client. -- Inspired by: Payroll Bank Transactions, Vendor Bank Transactions, and Payable Transactions. , CPI.primary_processor__staff_KEY AS client_payroll_information__primary_processor__staff_KEY , CPI.backup_processor__staff_KEY AS client_payroll_information__backup_processor__staff_KEY , CPI.reviewer__staff_KEY AS client_payroll_information__reviewer__staff_KEY , F.logo__report_image_KEY , CASE WHEN C.date_left IS NULL OR C.date_left > GETDATE() THEN 1 ELSE 2 END AS client__active_status_KEY -- Column added to match with the Active_Status View for Status Filter , C.send_w2s_to_ultra_tax , C.client_version , C.client_force_new_version_toggle , ISNULL( -- This cannot actually be NULL, BUT SQL SERVER is dumb as rocks. CASE WHEN EXISTS( SELECT 1 FROM dbo.Checkbook AS cb WHERE cb.client_KEY = C.client_KEY AND cb.yodlee_financial_account_link_status_KEY IN ( 3 /* Linked */ , 7 /* ActionRequired */ , 8 /* UpdatePending */ , 9 /* UpdatePeriodExpired */ ) ) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END, CAST(0 AS BIT)) AS has_yodlee_user_account , C.is_checked_out FROM dbo.Client AS C LEFT OUTER JOIN dbo.Client_Class AS CC ON CC.client_class_KEY = C.client_class_KEY LEFT OUTER JOIN dbo.Record_Status AS RS ON RS.record_status_KEY = C.record_status_KEY LEFT OUTER JOIN dbo.Contact AS CT ON CT.contact_KEY = C.contact_KEY LEFT OUTER JOIN dbo.Client_Payroll_Information AS CPI ON CPI.client_KEY = C.client_KEY CROSS JOIN dbo.Firm AS F ; -- CREATE VIEW must be in a distinct batch from other statements. | |||