View: dbo.Shortfall_Transaction_View
View definition
CREATE VIEW dbo.Shortfall_Transaction_View
AS
SELECT
shortfall_transaction_KEY = ST.shortfall_transaction_KEY
, shortfall_transaction_type_KEY = ST.shortfall_transaction_type_KEY
, employee_KEY = ST.employee_KEY
, transaction_date = ST.transaction_date
, amount = ST.amount
, shortfall__bank_transaction_displayable_status_KEY = (
CASE
WHEN BTV.bank_transaction_KEY IS NULL THEN 1
ELSE BTV.bank_transaction_displayable_status_KEY
END
)
FROM
dbo.Shortfall_Transaction ST
left join
dbo.Shortfall_Transaction_N_Payroll_Check_Tax_Item ST_PCTI
ON ST_PCTI.shortfall_transaction_KEY = ST.shortfall_transaction_KEY
left join
dbo.Shortfall_Transaction_N_Payroll_Check_Payroll_Item ST_PCPI
ON ST_PCPI.shortfall_transaction_KEY = ST.shortfall_transaction_KEY
left join
dbo.Payroll_Check_Tax_Item PCTI
ON PCTI.payroll_check_tax_item_KEY = ST_PCTI.payroll_check_tax_item_KEY
left join
dbo.Payroll_Check_Payroll_Item PCPI
ON PCPI.payroll_check_payroll_item_KEY = ST_PCPI.payroll_check_payroll_item_KEY
left join
dbo.Bank_Transaction_View BTV
ON (BTV.bank_transaction_KEY = PCTI.bank_transaction_KEY
OR
BTV.bank_transaction_KEY = PCPI.bank_transaction_KEY)