Как объединить счета и полученные платежи в Quick Books?
Я подключился к QB через ODBC из MS Access и связался со следующим:
- QBReportAdminGroup_v_txn_invoice_hdr
- QBReportAdminGroup_v_txn_invoice_line
- QBReportAdminGroup_v_txn_received_payment_hdr
- QBReportAdminGroup_v_txn_received_payment_line
Я хочу создать запрос для объединения платежей и счетов, но не могу найти общий ключ между ними. Я что-то упускаю? LE (добавлены поля таблицы):
QBReportAdminGroup_v_txn_invoice_hdr
- cf_name_id
- cleared_type_h
- contact_id_h
- currency_id_h
- custom_style_id_h
- customer_id_h
- customer_message_id
- db_created_tms
- db_modified_tms
- Дата доставки
- doc_num_h
- due_date_h
- employee_id_h
- end_balance_amt
- exchange_rate_h
- Ожидаемая дата
- надувать
- is_active
- is_condensed
- is_deposited_h
- is_finance_charge
- is_home_currency_adjustment
- is_opening_balance
- is_paid_h
- is_purchase_h
- is_purchase_order_h
- is_received_h
- is_refunded
- is_sale_h
- is_shown_in_home_currency
- item_id_h
- last_auditable_action
- last_edit_by
- memo_h
- modtimestamp_tms
- Другой
- other_id_h
- payment_method_id_h
- po_num
- sales_order_num
- sales_rep_id
- sales_tax_code_id_h
- ship_date
- ship_to_id
- ship_to_name_id
- ship_via_id
- target_id_h
- tax_code_id_h
- terms_id
- trans_num
- transaction_date_h
- номер транзакции
- transaction_name_type_h
- transaction_view_type_h
- txn_ident
- vendor_id_h
- vendor_message
QBReportAdminGroup_v_txn_invoice_line
- cf_name_id
- cleared_type_h
- contact_id_h
- currency_id_h
- custom_style_id_h
- customer_id_h
- customer_message_id
- db_created_tms
- db_modified_tms
- Дата доставки
- doc_num_h
- due_date_h
- employee_id_h
- end_balance_amt
- exchange_rate_h
- Ожидаемая дата
- надувать
- is_active
- is_condensed
- is_deposited_h
- is_finance_charge
- is_home_currency_adjustment
- is_opening_balance
- is_paid_h
- is_purchase_h
- is_purchase_order_h
- is_received_h
- is_refunded
- is_sale_h
- is_shown_in_home_currency
- item_id_h
- last_auditable_action
- last_edit_by
- memo_h
- modtimestamp_tms
- Другой
- other_id_h
- payment_method_id_h
- po_num
- sales_order_num
- sales_rep_id
- sales_tax_code_id_h
- ship_date
- ship_to_id
- ship_to_name_id
- ship_via_id
- target_id_h
- tax_code_id_h
- terms_id
- trans_num
- transaction_date_h
- номер транзакции
- transaction_name_type_h
- transaction_view_type_h
- txn_ident
- vendor_id_h
- vendor_message
- cf_item_id
- account_id
- amount_amt
- bank_account_type
- bank_ref_name
- billed_date
- ID класса
- cleared_type
- cogs_amt
- contact_id
- currency_id
- Пользовательский ИД
- doc_num
- срок оплаты
- employee_id
- estimate_amt
- курс обмена
- foreign_amount_amt
- is_adjustment
- is_applied_discount
- is_arap
- is_based_on_time_activity
- is_begin_balance
- is_build
- is_deposited
- is_discount
- оценивается
- is_gain_loss_target
- is_groupend_row
- is_inactive
- is_invoiced
- is_item_from_estimate
- is_item_from_sales_order
- is_less_cashback
- is_manually_closed
- оплачивается
- is_purchase
- is_purchase_order
- is_receipt
- is_received
- is_sale
- is_sales_order
- is_source
- is_trans_tax
- is_uses_billed_date
- is_value_adjust
- item_id
- lineitem_name_type
- manufacturer_part_num
- markup_amt
- markup_amt_pct
- меморандум
- next_target_id
- nom_total_amt
- open_balance_amt
- open_qnty
- other_id
- Разное1
- Разное2
- payment_entry_type
- payment_method_id
- quantity_qnty
- reconcile_date
- reimbursed_type
- sales_order_qnty
- sales_tax_code_id
- service_date
- sibling_account_id
- site_id
- target_id
- tax_code_id
- taxable_discount_amt
- Дата сделки
- transaction_name_type
- transaction_view_type
- txnline_ident
- unit_id
- unit_price_amt
- unit_price_pct
- VENDOR_ID
QBReportAdminGroup_v_txn_received_payment_hdr
- cf_name_id
- cleared_type_h
- contact_id_h
- currency_id_h
- custom_style_id
- customer_id_h
- customer_message_id
- db_created_tms
- db_modified_tms
- Дата доставки
- doc_num_h
- due_date_h
- employee_id_h
- end_balance_amt
- exchange_rate_h
- Ожидаемая дата
- надувать
- is_active
- is_condensed
- is_deposited_h
- is_finance_charge
- is_home_currency_adjustment
- is_opening_balance
- is_paid_h
- is_purchase_h
- is_purchase_order_h
- is_received_h
- is_refunded
- is_sale_h
- is_shown_in_home_currency
- item_id_h
- last_auditable_action
- last_edit_by
- memo_h
- modtimestamp_tms
- Другой
- other_id_h
- payment_method_id_h
- po_num
- sales_order_num
- sales_rep_id
- sales_tax_code_id_h
- ship_date
- ship_to_id
- ship_to_name_id
- ship_via_id
- target_id_h
- tax_code_id_h
- terms_id
- trans_num
- transaction_date_h
- номер транзакции
- transaction_name_type_h
- transaction_view_type_h
- txn_ident
- vendor_id_h
- vendor_message
QBReportAdminGroup_v_txn_received_payment_line
- cf_name_id
- cleared_type_h
- contact_id_h
- currency_id_h
- custom_style_id
- customer_id_h
- customer_message_id
- db_created_tms
- db_modified_tms
- Дата доставки
- doc_num_h
- due_date_h
- employee_id_h
- end_balance_amt
- exchange_rate_h
- Ожидаемая дата
- надувать
- is_active
- is_condensed
- is_deposited_h
- is_finance_charge
- is_home_currency_adjustment
- is_opening_balance
- is_paid_h
- is_purchase_h
- is_purchase_order_h
- is_received_h
- is_refunded
- is_sale_h
- is_shown_in_home_currency
- item_id_h
- last_auditable_action
- last_edit_by
- memo_h
- modtimestamp_tms
- Другой
- other_id_h
- payment_method_id_h
- po_num
- sales_order_num
- sales_rep_id
- sales_tax_code_id_h
- ship_date
- ship_to_id
- ship_to_name_id
- ship_via_id
- target_id_h
- tax_code_id_h
- terms_id
- trans_num
- transaction_date_h
- номер транзакции
- transaction_name_type_h
- transaction_view_type_h
- txn_ident
- vendor_id_h
- vendor_message
- cf_item_id
- account_id
- amount_amt
- bank_account_type
- bank_ref_name
- billed_date
- ID класса
- cleared_type
- cogs_amt
- contact_id
- currency_id
- Пользовательский ИД
- doc_num
- срок оплаты
- employee_id
- estimate_amt
- курс обмена
- foreign_amount_amt
- is_adjustment
- is_applied_discount
- is_arap
- is_based_on_time_activity
- is_begin_balance
- is_build
- is_deposited
- is_discount
- оценивается
- is_gain_loss_target
- is_groupend_row
- is_inactive
- is_invoiced
- is_item_from_estimate
- is_item_from_sales_order
- is_less_cashback
- is_manually_closed
- оплачивается
- is_purchase
- is_purchase_order
- is_receipt
- is_received
- is_sale
- is_sales_order
- is_source
- is_trans_tax
- is_uses_billed_date
- is_value_adjust
- item_id
- lineitem_name_type
- manufacturer_part_num
- markup_amt
- markup_amt_pct
- меморандум
- next_target_id
- nom_total_amt
- open_balance_amt
- open_qnty
- other_id
- Разное1
- Разное2
- payment_entry_type
- payment_method_id
- quantity_qnty
- reconcile_date
- reimbursed_type
- sales_order_qnty
- sales_tax_code_id
- service_date
- sibling_account_id
- site_id
- target_id
- tax_code_id
- taxable_discount_amt
- Дата сделки
- transaction_name_type
- transaction_view_type
- unit_id
- unit_price_amt
- unit_price_pct
- VENDOR_ID
1 ответ
Вам следует использовать таблицу v_txn_link для связи платежей и счетов.
Это отношения
v_txn_link.transaction_1_id = v_txn_received_payment_line.transaction_id
v_txn_link.transaction_2_id = v_txn_invoice_line.transaction_id