View: dbo.GL_Transaction_Detail_View
View definition
-- SUMMARY
-- This view gathers ledger entries from all sources currently in the database. At the moment these sources are:
-- * Bank Transactions (balancing entries, check & deposit summary entries, payroll check details, distributions)
-- * Journal Entries
-- * AP transactions (payables, payments, payment applications)
-- * AR transactions (receivables, payments, payment applications)
-- As ledger entry sources are added in the future, this view will be updated to include them.
CREATE VIEW dbo.GL_Transaction_Detail_View
AS
select
auto_reversing
, bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, distribution_workpaper_reference
, first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, journal_entry_distribution_KEY
, journal_entry_KEY
, journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, schedule_m3_tax_code_attribute_KEY
, second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, vendor_KEY
, workpaper_reference
, null as gl_accounting_method_KEY
, payroll_check_payroll_item_distribution_KEY
, payroll_check_tax_item_distribution_KEY
, payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, sequence_number
, ledger_entry_type_KEY = 4 -- Bank Transaction Distribution Cr Dr
from
dbo.GL_Transaction_Detail__Bank_Transaction_Distribution__View
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, null as gl_accounting_method_KEY
, payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, sequence_number
, ledger_entry_type_KEY = 5 /* Payroll Check Payroll Item Expense Dr */
from dbo.GL_Transaction_Detail__Payroll_Check_Payroll_Item_Expense__View
where
-- This non-posting period restriction logically belongs within the indexed ledger entry view itself.
-- Unfortunately, an indexed view can''t contain a "LEFT JOIN" or a "NOT IN (subquery)" construct so the restriction
-- is placed here.
gl_period_KEY not in (select gl_period_KEY from dbo.Non_Posting_Payroll_Transaction_GL_Period)
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, null as gl_accounting_method_KEY
, payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, sequence_number
, ledger_entry_type_KEY = 6 /* Payroll Check Payroll Item Liability Cr */
from dbo.GL_Transaction_Detail__Payroll_Check_Payroll_Item_Liability__View
where
-- This non-posting period restriction logically belongs within the indexed ledger entry view itself.
-- Unfortunately, an indexed view can''t contain a "LEFT JOIN" or a "NOT IN (subquery)" construct so the restriction
-- is placed here.
gl_period_KEY not in (select gl_period_KEY from dbo.Non_Posting_Payroll_Transaction_GL_Period)
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, null as gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, sequence_number
, ledger_entry_type_KEY = 7 /* Payroll Check Tax Item Expense Dr */
from dbo.GL_Transaction_Detail__Payroll_Check_Tax_Item_Expense__View
where
-- This non-posting period restriction logically belongs within the indexed ledger entry view itself.
-- Unfortunately, an indexed view can''t contain a "LEFT JOIN" or a "NOT IN (subquery)" construct so the restriction
-- is placed here.
gl_period_KEY not in (select gl_period_KEY from dbo.Non_Posting_Payroll_Transaction_GL_Period)
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, null as gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, sequence_number
, ledger_entry_type_KEY = 8 /* Payroll Check Tax Item Liability Cr */
from dbo.GL_Transaction_Detail__Payroll_Check_Tax_Item_Liability__View
where
-- This non-posting period restriction logically belongs within the indexed ledger entry view itself.
-- Unfortunately, an indexed view can''t contain a "LEFT JOIN" or a "NOT IN (subquery)" construct so the restriction
-- is placed here.
gl_period_KEY not in (select gl_period_KEY from dbo.Non_Posting_Payroll_Transaction_GL_Period)
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, null as gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, sequence_number
, ledger_entry_type_KEY = 9 /* Payroll Check Worker Compensation Expense Dr */
from dbo.GL_Transaction_Detail__Payroll_Check_Worker_Compensation_Expense__View
where
-- This non-posting period restriction logically belongs within the indexed ledger entry view itself.
-- Unfortunately, an indexed view can''t contain a "LEFT JOIN" or a "NOT IN (subquery)" construct so the restriction
-- is placed here.
gl_period_KEY not in (select gl_period_KEY from dbo.Non_Posting_Payroll_Transaction_GL_Period)
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, null as gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, sequence_number
, ledger_entry_type_KEY = 10 /* Payroll Check Worker Compensation Liability Cr */
from dbo.GL_Transaction_Detail__Payroll_Check_Worker_Compensation_Liability__View
where
-- This non-posting period restriction logically belongs within the indexed ledger entry view itself.
-- Unfortunately, an indexed view can''t contain a "LEFT JOIN" or a "NOT IN (subquery)" construct so the restriction
-- is placed here.
gl_period_KEY not in (select gl_period_KEY from dbo.Non_Posting_Payroll_Transaction_GL_Period)
UNION ALL
select
auto_reversing
, bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, distribution_workpaper_reference
, first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, journal_entry_distribution_KEY
, journal_entry_KEY
, journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, schedule_m3_tax_code_attribute_KEY
, second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, vendor_KEY
, workpaper_reference
, null as gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, sequence_number
, ledger_entry_type_KEY = 1 /* Journal Entry Cr Dr */
from
dbo.GL_Transaction_Detail__Journal_Entry__View
UNION ALL
select
auto_reversing
, bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, distribution_workpaper_reference
, first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, journal_entry_distribution_KEY
, journal_entry_KEY
, journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, schedule_m3_tax_code_attribute_KEY
, second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, vendor_KEY
, workpaper_reference
, null as gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, 0 as sequence_number
, CASE
WHEN gl_transaction_type_KEY = 301 /* GLBalancingEntryType.CheckSummaryMemo */ THEN 2 /* LedgerEntryType.CheckSummaryMemoCr */
WHEN gl_transaction_type_KEY = 302 /* GLBalancingEntryType.DepositSummaryMemo */ THEN 27 /*LedgerEntryType.DepositSummaryMemoDr*/
END as ledger_entry_type_KEY
from
dbo.GL_Transaction_Detail__Summary_Memo__View
UNION ALL
select
auto_reversing
, bank_transaction_distribution_KEY
, bank_transaction_KEY
, checkbook_KEY
, client_KEY
, distribution_workpaper_reference
, first__w2_1099_box_type_KEY
, gl_account_KEY
, gl_balance_effect_KEY
, NULL AS gl_balancing_entry_KEY
, gl_period_KEY
, gl_transaction_amount
, gl_transaction_date
, gl_transaction_description
, gl_transaction_detail_description
, gl_transaction_KEY
, gl_transaction_reference_number
, gl_transaction_reference_number_sortable
, gl_transaction_source_expression_KEY
, gl_transaction_source_KEY
, gl_transaction_type_KEY
, is_activity_journal_entry
, journal_entry_distribution_KEY
, journal_entry_KEY
, journal_entry_misstatement_type_KEY
, journal_entry_type_KEY
, journal_KEY
, schedule_m3_tax_code_attribute_KEY
, second__w2_1099_box_type_KEY
, source_was_a_distribution
, transaction_status_KEY
, vendor_KEY
, workpaper_reference
, null as gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, 0 as sequence_number
, ledger_entry_type_KEY = 3 /* Bank Transaction Balancing Cr Dr */
from
dbo.GL_Transaction_Detail__Bank_Transaction__View
where
gl_balancing_entry_KEY IS NULL
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, null as bank_transaction_KEY
, null as checkbook_KEY
, j.client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, le.gl_account_KEY
, le.gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, le.gl_period_KEY
, le.amount as gl_transaction_amount
, le.application_date as gl_transaction_date
, le.description as gl_transaction_description
, le.description as gl_transaction_detail_description
, le.gl_transaction_KEY
, le.reference_number as gl_transaction_reference_number
, le.reference_number_sortable as gl_transaction_reference_number_sortable
, CAST(4 AS TINYINT) /*GLTransactionSourceExpression.PayablePaymentApplication*/ AS gl_transaction_source_expression_KEY
, 5 /*PayablePaymentApplication*/ as gl_transaction_source_KEY
, 401 /*PayablePaymentApplication*/ as gl_transaction_type_KEY
, cast(0 as bit) as is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, 101 /*Journal entry*/ as journal_entry_type_KEY
, le.journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, source_was_a_distribution
, 1 /*Live*/ as transaction_status_KEY
, pt.vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, le.gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, le.payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, le.payment__gl_transaction_KEY
, le.payable_payment_application_payable_distribution_KEY
, le.payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, 0 as sequence_number
, le.ledger_entry_type_KEY
from
dbo.GL_Transaction_Detail__Payable_Payment_Application__View as le
inner join dbo.Journal as j
on j.journal_KEY = le.journal_KEY
inner join dbo.Payable_Transaction as pt
on pt.payable_transaction_KEY = le.payable_transaction_KEY
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, null as bank_transaction_KEY
, null as checkbook_KEY
, j.client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, le.gl_account_KEY
, le.gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, le.gl_period_KEY
, le.amount as gl_transaction_amount
, le.transaction_date as gl_transaction_date
, v.vendor_name as gl_transaction_description
, isnull(le.description, v.vendor_name) as gl_transaction_detail_description
, le.gl_transaction_KEY
, le.reference_number as gl_transaction_reference_number
, le.reference_number_sortable as gl_transaction_reference_number_sortable
, CAST(5 AS TINYINT) /*GLTransactionSourceExpression.PayableTransaction*/ AS gl_transaction_source_expression_KEY
, 4 /*PayableTransaction*/ as gl_transaction_source_KEY
, le.payable_transaction_type_KEY as gl_transaction_type_KEY
, cast(0 as bit) as is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, 101 /*Journal entry*/ as journal_entry_type_KEY
, le.journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, source_was_a_distribution
, 1 /*Live*/ as transaction_status_KEY
, le.vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, le.gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, le.payable_transaction_KEY
, le.payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, null as customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, le.sequence_number
, le.ledger_entry_type_KEY
from
dbo.GL_Transaction_Detail__Payable_Transaction__View as le
inner join dbo.Journal as j
on j.journal_KEY = le.journal_KEY
inner join dbo.Vendor as v
on le.vendor_KEY = v.vendor_KEY
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, null as bank_transaction_KEY
, null as checkbook_KEY
, j.client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, le.gl_account_KEY
, le.gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, le.gl_period_KEY
, le.amount as gl_transaction_amount
, le.transaction_date as gl_transaction_date
, cst.customer_name as gl_transaction_description
, cst.customer_name as gl_transaction_detail_description
, le.gl_transaction_KEY
, le.reference_number as gl_transaction_reference_number
, le.reference_number_sortable as gl_transaction_reference_number_sortable
, CAST(3 AS TINYINT) /*GLTransactionSourceExpression.ARTransaction*/ AS gl_transaction_source_expression_KEY
, 6 /*ARTransaction*/ as gl_transaction_source_KEY
, le.ar_transaction_type_KEY as gl_transaction_type_KEY
, cast(0 as bit) as is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, 101 /*Journal entry*/ as journal_entry_type_KEY
, le.journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, le.source_was_a_distribution
, 1 /*Live*/ as transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, le.gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, le.customer_KEY
, le.ar_transaction_item_KEY
, le.ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, le.sequence_number
, le.ledger_entry_type_KEY
from
dbo.GL_Transaction_Detail__AR_Transaction__View as le
inner join dbo.Journal as j
on j.journal_KEY = le.journal_KEY
inner join dbo.Customer as cst
on le.customer_KEY = cst.customer_KEY
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, null as bank_transaction_KEY
, null as checkbook_KEY
, j.client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, le.gl_account_KEY
, le.gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, le.gl_period_KEY
, le.amount as gl_transaction_amount
, le.transaction_date as gl_transaction_date
, le.customer_name as gl_transaction_description
, le.customer_name as gl_transaction_detail_description
, le.gl_transaction_KEY
, le.reference_number as gl_transaction_reference_number
, le.reference_number_sortable as gl_transaction_reference_number_sortable
, CAST(2 AS TINYINT) /*GLTransactionSourceExpression.ARPayment*/ AS gl_transaction_source_expression_KEY
, 7 /*ARPayment*/ as gl_transaction_source_KEY
, le.ar_payment_type_KEY as gl_transaction_type_KEY
, cast(0 as bit) as is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, 101 /*Journal entry*/ as journal_entry_type_KEY
, le.journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, le.source_was_a_distribution
, 1 /*Live*/ as transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, le.gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, le.customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, 0 as sequence_number
, le.ledger_entry_type_KEY
from
dbo.GL_Transaction_Detail__AR_Payment__View as le
inner join dbo.Journal as j
on j.journal_KEY = le.journal_KEY
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, null as bank_transaction_KEY
, null as checkbook_KEY
, j.client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, le.gl_account_KEY
, le.gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, le.gl_period_KEY
, le.amount as gl_transaction_amount
, le.application_date as gl_transaction_date
, le.description as gl_transaction_description
, le.description as gl_transaction_detail_description
, le.gl_transaction_KEY
, le.reference_number as gl_transaction_reference_number
, le.reference_number_sortable as gl_transaction_reference_number_sortable
, CAST(1 AS TINYINT) /*GLTransactionSourceExpression.ARPaymentApplication*/ AS gl_transaction_source_expression_KEY
, 8 /*ARPaymentApplication*/ as gl_transaction_source_KEY
, 570 /*AR payment application*/ as gl_transaction_type_KEY
, cast(0 as bit) as is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, 101 /*Journal entry*/ as journal_entry_type_KEY
, le.journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, le.source_was_a_distribution
, 1 /*Live*/ as transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, le.gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, le.customer_KEY
, null as ar_transaction_item_KEY
, null as ar_transaction_item_gl_account_KEY
, null as ar_payment_application_distribution_KEY
, 0 as sequence_number
, le.ledger_entry_type_KEY
from
dbo.GL_Transaction_Detail__AR_Payment_Application__View as le
inner join dbo.Journal as j
on j.journal_KEY = le.journal_KEY
UNION ALL
select
null as auto_reversing
, null as bank_transaction_distribution_KEY
, null as bank_transaction_KEY
, null as checkbook_KEY
, j.client_KEY
, N'' COLLATE Latin1_General_CI_AS as distribution_workpaper_reference
, null as first__w2_1099_box_type_KEY
, le.gl_account_KEY
, le.gl_balance_effect_KEY
, null as gl_balancing_entry_KEY
, le.gl_period_KEY
, le.amount as gl_transaction_amount
, le.application_date as gl_transaction_date
, le.description as gl_transaction_description
, le.description as gl_transaction_detail_description
, le.gl_transaction_KEY
, le.reference_number as gl_transaction_reference_number
, le.reference_number_sortable as gl_transaction_reference_number_sortable
, CAST(1 AS TINYINT) /*GLTransactionSourceExpression.ARPaymentApplication*/ AS gl_transaction_source_expression_KEY
, 8 /*ARPaymentApplication*/ as gl_transaction_source_KEY
, 570 /*AR payment application*/ as gl_transaction_type_KEY
, cast(0 as bit) as is_activity_journal_entry
, null as journal_entry_distribution_KEY
, null as journal_entry_KEY
, null as journal_entry_misstatement_type_KEY
, 101 /*Journal entry*/ as journal_entry_type_KEY
, le.journal_KEY
, null as schedule_m3_tax_code_attribute_KEY
, null as second__w2_1099_box_type_KEY
, le.source_was_a_distribution
, 1 /*Live*/ as transaction_status_KEY
, null as vendor_KEY
, N'' COLLATE Latin1_General_CI_AS as workpaper_reference
, le.gl_accounting_method_KEY
, null as payroll_check_payroll_item_distribution_KEY
, null as payroll_check_tax_item_distribution_KEY
, null as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, null as payable_transaction_KEY
, null as payable_transaction_distribution_KEY
, null as payment__gl_transaction_KEY
, null as payable_payment_application_payable_distribution_KEY
, null as payable_payment_application_payment_distribution_KEY
, art.customer_KEY
, le.ar_transaction_item_KEY
, le.ar_transaction_item_gl_account_KEY
, le.ar_payment_application_distribution_KEY
, 0 as sequence_number
, ledger_entry_type_KEY = 26 /* AR Payment Application Distribution Cr Dr */
from
dbo.GL_Transaction_Detail__AR_Payment_Application_Distribution__View as le
inner join dbo.Journal as j
on j.journal_KEY = le.journal_KEY
inner join dbo.AR_Transaction_Item as arti
on le.ar_transaction_item_KEY = arti.ar_transaction_item_KEY
inner join dbo.AR_Transaction as art
on arti.gl_transaction_KEY = art.gl_transaction_KEY
;