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;