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 ; | |||