View: dbo.Elf_File_View
View definition
`CREATE VIEW dbo.Elf_File_View AS SELECT ef.elf_file_KEY AS elf_file_KEY , ef.client_KEY AS client_KEY , ef.period_end_date AS period_end_date , ef.payment_date AS payment_date , COALESCE(ef.form_data_xml, N'') AS form_data_xml -- We WANT the MS SQL Bug on the line above where COALESCE() fails to -- report NON-NULL because the underlying TABLE is NULL, at least until we -- make bigger changes we will want the ENTITY to report NULL-permitted. , ef.tax_form_KEY AS tax_form_KEY , ef.tax_form_KEY AS tax_form_used_by_elf_file_KEY , CONVERT(INT, CONVERT(DECIMAL, tf.ste_state_code_KEY)) AS state_KEY -- table doesn't allow null so don't worry about it here. , LatestTransaction.elf_transmission_status_KEY AS elf_transmission_status_KEY --set to the latest transmission status, possible NULL if not yet transmitted. needs to be named this for the filter to work properly , ef.file_name AS file_name , ef.filing_number AS filing_number , ef.federal_tin AS federal_tin , ef.account_number AS account_number , ef.is_corrected as is_corrected FROM dbo.Elf_File AS ef INNER JOIN dbo.Tax_Form AS tf ON tf.tax_form_KEY = ef.tax_form_KEY LEFT JOIN ( SELECT et_candidate.elf_file_KEY, et_candidate.elf_transmission_status_KEY FROM dbo.Elf_Transmission AS et_candidate WHERE NOT EXISTS( SELECT 1 FROM dbo.Elf_Transmission AS et_better WHERE et_better.elf_file_KEY = et_candidate.elf_file_KEY AND et_better.elf_transmission_KEY > et_candidate.elf_transmission_KEY ) ) AS LatestTransaction ON LatestTransaction.elf_file_KEY = ef.elf_file_KEY;`