View: dbo.Payroll_Bank_Transaction_Full_View
View definition
CREATE VIEW dbo.Payroll_Bank_Transaction_Full_View
AS
SELECT
bank_transaction_KEY = BT.bank_transaction_KEY
, transaction_date = BT.transaction_date
, bank_transaction_type_KEY = BT.bank_transaction_type_KEY
, checkbook_KEY = BT.checkbook_KEY
, transaction_status_KEY = BT.transaction_status_KEY
, amount = BT.amount
, reference_number = BT.reference_number
, memo = BT.memo
, description = BT.description
, is_handwritten_check = BT.is_handwritten_check
, export_journal_entry_to_csa_status_KEY = BT.export_journal_entry_to_csa_status_KEY
, funding_processed_status_KEY = BT.funding_processed_status_KEY
, employee_KEY = PBT.employee_KEY
, pay_frequency_KEY = PBT.pay_frequency_KEY
, period_begin_date = PBT.period_begin_date
, period_end_date = PBT.period_end_date
, weeks_worked = PBT.weeks_worked
, ste_location_code = PBT.ste_location_code
, do_not_create_check_from_enter_batch_checks = PBT.do_not_create_check_from_enter_batch_checks
, direct_deposit_amount = COALESCE(DDBTA.direct_deposit_amount, 0.00)
, origin__accounting_application_KEY = BT.origin__accounting_application_KEY
, funding_processed_status_filter_type_KEY = (
CASE
WHEN BT.funding_processed_status_KEY IN (1, 2) THEN 2 -- Pending
WHEN BT.funding_processed_status_KEY = 3 THEN 3 -- Funded
WHEN BT.funding_processed_status_KEY = 4 THEN 4 -- Not Applicable
ELSE 10 -- Invalid funding_processed_status_KEY
END
)
FROM
dbo.Bank_Transaction BT
INNER JOIN
dbo.Payroll_Bank_Transaction PBT ON PBT.bank_transaction_KEY = BT.bank_transaction_KEY
LEFT OUTER JOIN
(SELECT
bank_transaction_KEY
,direct_deposit_amount = SUM(amount)
FROM
dbo.Direct_Deposit_Bank_Transaction_Allocation
GROUP BY
bank_transaction_KEY) DDBTA ON DDBTA.bank_transaction_KEY = BT.bank_transaction_KEY
WHERE bank_transaction_type_KEY = 5