Blog

It is common to need to query invoices generated by employees. However, the relationship between the AP_INVOICES_ALL and PER_ALL_PEOPLE_F tables is not straightforward. These tables are connected through the HZ_PARTIES table. The relationship is shown below:

1. AP_INVOICES_ALL is linked to HZ_PARTIES via party_id.
2. HZ_PARTIES is linked to PER_ALL_PEOPLE_F via orig_system_reference.

Here’s a visualization of the SQL:

A sample SQL is shown below:

SELECT
  person.person_number,
  inv.invoice_num,
  inv.invoice_amount,
  ledg.name ledger_name,
  lin.description,
  lin.amount line_amount,
  dist.accounting_date,
  dist.period_name,
  gcc.segment1 || '.' || gcc.segment2 accounting_code
FROM
  AP_INVOICES_ALL inv,
  ap_invoice_lines_all lin,
  ap_invoice_distributions_all dist,
  gl_code_combinations gcc,
  hz_parties hp,
  gl_ledgers ledg,
  per_all_people_f person
WHERE
  lin.invoice_id = inv.invoice_id
  AND dist.invoice_id = lin.invoice_id
  AND dist.invoice_line_number = lin.line_number
  AND dist.dist_code_combination_id = gcc.code_combination_id
  AND inv.party_Id = hp.party_id
  AND hp.orig_system_reference = to_char (person.person_id)
  AND inv.set_of_books_id = ledg.ledger_id
  AND sysdate BETWEEN person.effective_Start_date AND person.effective_end_date